CREATE TABLE wh_goods
   (dtsave                         DATE,
   id_sys_operations              NUMBER,
   id_goods                       NUMBER,
   nvalue                         NUMBER NOT NULL,
   nsum                           NUMBER(10,2),
   id_doc                         NUMBER NOT NULL,
   id_discountsreasons            NUMBER)
 PCTFREE     10
 PCTUSED     40
 INITRANS    1
 MAXTRANS    255
 TABLESPACE  system
 STORAGE   (
   INITIAL     458752
   MINEXTENTS  1
   MAXEXTENTS  2147483645
 )
 NOCACHE
 MONITORING
/

-- Indexes for WH_GOODS

CREATE INDEX i_wh_goods ON wh_goods
 (
   id_goods                        ASC,
   dtsave                          ASC
 )
 PCTFREE     10
 INITRANS    2
 MAXTRANS    255
 TABLESPACE  system
 STORAGE   (
   INITIAL     65536
   MINEXTENTS  1
   MAXEXTENTS  2147483645
 )
/

-- Foreign Key
ALTER TABLE wh_goods
ADD CONSTRAINT fk_wh_goods_goods FOREIGN KEY (id_goods)
REFERENCES goods (id) ON DELETE CASCADE
/

CREATE OR REPLACE
FUNCTION get_goods_rest (pid_goods IN NUMBER, pdt IN DATE)
  RETURN NUMBER
IS
  aresult   NUMBER;
BEGIN
  SELECT NVL (SUM (nvalue), 0)
    INTO aresult
    FROM wh_goods
   WHERE id_goods = pid_goods AND dtsave <= pdt;
  RETURN aresult;
EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
     RETURN 0;
END;
/

SELECT g.id_goods, SUM(g.nvalue), SUM(g.nvalue * g.nsum)
FROM wh_goods g
WHERE g.dtsave <= pdt
GROUP BY g.id_goods