
CREATE OR REPLACE PACKAGE Resumen IS

   PROCEDURE SacaMontoEnt (Compania     IN     VARCHAR2,
                           Bodega       IN     VARCHAR2,
                           Articulo     IN     VARCHAR2,
                           Inicio       IN     DATE,
                           Fin          IN     DATE,
                           Mon_Compras  IN OUT NUMBER,
                           Mon_DevClien IN OUT NUMBER,
                           Mon_AjtPos   IN OUT NUMBER,
                           Mon_OtrasEnt IN OUT NUMBER
                          );
   --
   PROCEDURE SacaMontoSal (Compania     IN     VARCHAR2,
                           Bodega       IN     VARCHAR2,
                           Articulo     IN     VARCHAR2,
                           Inicio       IN     DATE,
                           Fin          IN     DATE,
                           Mon_Ventas   IN OUT NUMBER,
                           Mon_DevProv  IN OUT NUMBER,
                           Mon_AjtNeg   IN OUT NUMBER,
                           Mon_OtrasSal IN OUT NUMBER
                          );
   --
   FUNCTION SacaMontoInicial (Compania  IN  VARCHAR2,
                              Bodega    IN  VARCHAR2,
                              Articulo  IN  VARCHAR2,
                              Inicio    IN  DATE,
                              Fin       IN  DATE
                              ) RETURN NUMBER;

END Resumen;
/

CREATE OR REPLACE PACKAGE BODY Resumen IS

   PROCEDURE SacaMontoEnt (Compania     IN     VARCHAR2,
                           Bodega       IN     VARCHAR2,
                           Articulo     IN     VARCHAR2,
                           Inicio       IN     DATE,
                           Fin          IN     DATE,
                           Mon_Compras  IN OUT NUMBER,
                           Mon_DevClien IN OUT NUMBER,
                           Mon_AjtPos   IN OUT NUMBER,
                           Mon_OtrasEnt IN OUT NUMBER
                           ) IS
      CURSOR MontoEntradas IS
         SELECT NVL(SUM(B.Can_Articulo*B.Cos_Unidad), 0) Monto, C.Clase_Movto
         FROM Inv_EncabMovimiento                     A,
              Inv_DetMovimiento                       B,
              Inv_ConcepMovto                         C
         WHERE A.Cod_Cia      = Compania                     AND
               B.Cod_Cia      = Compania                     AND
               C.Cod_Cia      = Compania                     AND
               A.Tip_Movto    = 'E'                          AND
               B.Tip_Movto    = 'E'                          AND
               C.Tip_Movto    = 'E'                          AND
               B.Cod_Bodega   = Bodega                       AND
               B.Cod_Articulo = Articulo                     AND
               B.Num_Docto    = A.Num_Docto                  AND
               A.Fec_Movto    between Inicio and Fin         AND
               A.Cod_movto    = C.Cod_movto                  AND
               A.Estado       = 'D'
         GROUP BY C.Clase_Movto;
   BEGIN
      Mon_Compras  := 0;
      Mon_DevClien := 0;
      Mon_AjtPos   := 0;
      Mon_OtrasEnt := 0;
      FOR Reg_MontoEntradas IN MontoEntradas LOOP
         IF    Reg_MontoEntradas.Clase_Movto = 'EC' THEN
            Mon_Compras  := Reg_MontoEntradas.Monto;
         ELSIF Reg_MontoEntradas.Clase_Movto = 'ED' THEN
            Mon_DevClien := Reg_MontoEntradas.Monto;
         ELSIF Reg_MontoEntradas.Clase_Movto = 'EA' THEN
            Mon_AjtPos   := Reg_MontoEntradas.Monto;
         ELSE
            Mon_OtrasEnt := Mon_OtrasEnt + Reg_MontoEntradas.Monto;
         END IF;
      END LOOP;
   END SacaMontoEnt;

   PROCEDURE SacaMontoSal (Compania     IN     VARCHAR2,
                           Bodega       IN     VARCHAR2,
                           Articulo     IN     VARCHAR2,
                           Inicio       IN     DATE,
                           Fin          IN     DATE,
                           Mon_Ventas   IN OUT NUMBER,
                           Mon_DevProv  IN OUT NUMBER,
                           Mon_AjtNeg   IN OUT NUMBER,
                           Mon_OtrasSal IN OUT NUMBER
                          ) IS
      CURSOR MontoSalidas IS
         SELECT NVL(SUM(B.Can_Articulo*B.Cos_Unidad), 0) Monto, C.Clase_Movto
         FROM Inv_EncabMovimiento                     A,
              Inv_DetMovimiento                       B,
              Inv_ConcepMovto                         C
         WHERE A.Cod_Cia      = Compania                     AND
               B.Cod_Cia      = Compania                     AND
               C.Cod_Cia      = Compania                     AND
               B.Num_Docto    = A.Num_Docto                  AND
               A.Tip_Movto    = 'S'                          AND
               B.Tip_Movto    = 'S'                          AND
               C.Tip_Movto    = 'S'                          AND
               B.Cod_Bodega   = Bodega                       AND
               B.Cod_Articulo = Articulo                     AND
               A.Fec_Movto    between Inicio and Fin         AND
               A.Cod_movto    = C.Cod_movto                  AND
               A.Estado       = 'D'
         GROUP BY C.Clase_Movto;
   BEGIN
      Mon_Ventas   := 0;
      Mon_DevProv  := 0;
      Mon_AjtNeg   := 0;
      Mon_OtrasSal := 0;
      FOR Reg_MontoSalidas IN MontoSalidas LOOP
         IF Reg_MontoSalidas.Clase_Movto = 'SV' THEN
            Mon_Ventas  := Reg_MontoSalidas.Monto;
         ELSIF Reg_MontoSalidas.Clase_Movto = 'SD' THEN
            Mon_DevProv := Reg_MontoSalidas.Monto;
         ELSIF Reg_MontoSalidas.Clase_Movto = 'SA' THEN
            Mon_AjtNeg  := Reg_MontoSalidas.Monto;
         ELSE
            Mon_OtrasSal := Mon_OtrasSal + Reg_MontoSalidas.Monto;
         END IF;
      END LOOP;
   END  SacaMontoSal;

   FUNCTION SacaMontoInicial (Compania     IN     VARCHAR2,
                              Bodega       IN     VARCHAR2,
                              Articulo     IN     VARCHAR2,
                              Inicio       IN     DATE,
                              Fin          IN     DATE
                              ) RETURN NUMBER IS
      Mon_Entradas   NUMBER;
      Mon_Salidas    NUMBER;
   BEGIN
      Mon_Entradas := 0;
      Mon_Salidas  := 0;
      -- Entradas
      BEGIN
         SELECT SUM(DECODE(B.Tip_Movto,'E',B.Can_Articulo*B.Cos_Unidad,0)),
                SUM(DECODE(B.Tip_Movto,'S',B.Can_Articulo*B.Cos_Unidad,0))
            INTO Mon_Entradas, Mon_Salidas
         FROM Inv_EncabMovimiento               A,
              Inv_DetMovimiento                 B
         WHERE A.Cod_Cia      = Compania               AND
               B.Cod_Cia      = Compania               AND
               B.Cod_Bodega   = Bodega                 AND
               B.Cod_Articulo = Articulo               AND
               A.Tip_Movto    = B.Tip_movto            AND
               A.Num_Docto    = B.Num_Docto            AND
               A.Estado       = 'D'                    AND
               A.Fec_Movto    < Inicio;
      END;
      RETURN(nvl(Mon_Entradas,0) - nvl(Mon_Salidas,0));
   END SacaMontoInicial;
END Resumen;
/

DROP PUBLIC SYNONYM ResumenMovtos;
CREATE PUBLIC SYNONYM ResumenMovtos FOR Resumen;
GRANT EXECUTE ON RESUMEN TO PUBLIC;
