create or replace PACKAGE PACKAGE_APUR_CONSUMO_EST AS
   /* Criacao de um tipo que armazenara as informacoes de posicao inicial, entradas, posicao final */
   TYPE TAB_APUR_CONSUMO IS RECORD(CDFILIAL     VARCHAR2(4),
                                   NMFILIAL     VARCHAR2(50),
                                   NRREFESTI    FLOAT,
                                   NRREFEFET    FLOAT,
                                   DIASUTEIS    FLOAT,
                                   DSGRUPOPROD  VARCHAR2(50),
                                   CDARVPROD    VARCHAR2(10),
                                   CDPRODUTO    VARCHAR2(10),
                                   NMPRODUTO    VARCHAR2(50),
                                   SGUNIDADE    VARCHAR2(10),
                                   QTESTINI     FLOAT,
                                   VRCUSTUNIT   FLOAT,
                                   QTENTRADA    FLOAT,
                                   QTDEVOLUCAO  FLOAT,
                                   VRCUSDEVOL   FLOAT,
                                   QTTRANSF     FLOAT,
                                   VRCUSTRANSF  FLOAT,
                                   VRCUSCMPLIQ  FLOAT,
                                   VRCUSCMPBRUT FLOAT,
                                   QTESTFIN     FLOAT,
                                   VRCUSTMEDEST FLOAT);

   -- Tipo que sera retornado pela funcao GET_APUR_CONSUMO
   TYPE TYPE_APUR_CONSUMO_EST IS TABLE OF TAB_APUR_CONSUMO;

   --Retorna data da posicao de estoque
   FUNCTION RETORNA_DTPOSESTCON(P_PERIODO IN VARCHAR, P_FILIAL IN VARCHAR, P_CDPRODESTO IN VARCHAR) RETURN DATE;

   -- Retorna os valores da apuracao consumo.
   FUNCTION GET_APUR_CONSUMO(P_FILIAL VARCHAR2, P_DATAINI VARCHAR2, P_DATAFINAL VARCHAR2, P_PRODINI VARCHAR2, P_PRODFIM VARCHAR2) RETURN TYPE_APUR_CONSUMO_EST PIPELINED;

END PACKAGE_APUR_CONSUMO_EST;
/

create or replace PACKAGE BODY PACKAGE_APUR_CONSUMO_EST
AS
   FUNCTION RETORNA_DTPOSESTCON(P_PERIODO IN VARCHAR, P_FILIAL IN VARCHAR, P_CDPRODESTO IN VARCHAR) RETURN DATE
   IS
   V_DTPOSESTCON DATE;
   BEGIN
     SELECT MAX(POSESTCONS.DTPOSESTCON)
       INTO V_DTPOSESTCON
       FROM POSESTCONS
      WHERE (POSESTCONS.DTPOSESTCON <= P_PERIODO)
        AND POSESTCONS.CDFILIAL = P_FILIAL
        AND POSESTCONS.CDPRODUTO = P_CDPRODESTO;

      RETURN V_DTPOSESTCON;

   END RETORNA_DTPOSESTCON;

   FUNCTION GET_APUR_CONSUMO(P_FILIAL VARCHAR2, P_DATAINI VARCHAR2, P_DATAFINAL VARCHAR2, P_PRODINI VARCHAR2, P_PRODFIM VARCHAR2) RETURN TYPE_APUR_CONSUMO_EST PIPELINED
   IS

   TAB_APUR             TAB_APUR_CONSUMO;
   V_CDTPOCUSTO_PADRAO  NUMBER;
   V_DTPOSESTCON        DATE;
   V_CUSTO_UNIT_EST_FIN FLOAT  := 0;
   V_CUSTO_UNIT_EST_INI FLOAT  := 0;
   V_QT_ESTOQUE_INICIAL FLOAT  := 0;
   V_VR_ESTOQUE_INICIAL FLOAT  := 0;
   V_QT_ESTOQUE_FINAL   FLOAT  := 0;
   V_VR_ESTOQUE_FINAL   FLOAT  := 0;
   V_QT_ENTRADAS_NF     FLOAT  := 0;
   V_QT_DEVOL_NF        FLOAT  := 0;
   V_VRCUSDEVOL         FLOAT  := 0;
   V_QT_TRANSF_NF       FLOAT  := 0;
   V_VRCUSTRANSF        FLOAT  := 0;
   V_VRCUSCMPLIQ        FLOAT  := 0;
   V_VRCUSCMPBRUT       FLOAT  := 0;
   V_TIPO_LANC          NUMBER ;
   V_CDFILIAL           VARCHAR2(04);
   V_NMFILIAL           VARCHAR2(50);
   V_DTPROCESSA         DATE;
   V_NRDIASUTEIS        FLOAT;
   V_QTCOMESTICAR       FLOAT;
   V_QTCOMEFETCAR       FLOAT;
   V_CDPRODMOVI         VARCHAR2(10);
   V_CDARVPROD          VARCHAR2(10);
   V_CDPRODUTO          VARCHAR2(10);
   V_NMPRODUTO          VARCHAR2(50);
   V_SGUNIDADE          VARCHAR2(10);
   V_DSGRUPO            VARCHAR2(50);

   ---------------------------------------
   --CURSOR DE PRODUTO
   ---------------------------------------
   CURSOR CUR_PRODUTO IS
   SELECT DISTINCT IT.CDPRODMOVI, P.CDARVPROD, P.CDPRODUTO, P.NMPRODUTO, P.SGUNIDADE, G.NMPRODUTO AS DSGRUPO
     FROM ITLANCTOEST IT, PRODUTO P, PRODUTO G
    WHERE IT.CDFILIMOVI = P_FILIAL
      AND IT.CDPRODUTO = P.CDPRODUTO
      AND G.CDPRODUTO = SUBSTR(IT.CDPRODUTO,0,3)
      AND P.CDPRODUTO = P.CDPRODESTO
      AND P.CDARVPROD BETWEEN P_PRODINI AND P_PRODFIM
      AND IT.DTLANCMOVI BETWEEN P_DATAINI AND P_DATAFINAL
    ORDER BY 5,3,1;

   BEGIN
      BEGIN
         SELECT DECODE(P.CDTIPCUSTEST,NULL,'03',P.CDTIPCUSTEST), F.CDFILIAL, F.NMFILIAL, P.DTPROCESSA, RETORNA_DIA_UTIL(F.CDFILIAL, P_DATAINI, P_DATAFINAL) NRDIASUTEIS,
                (SELECT SUM(QTCOMESTICAR)
                   FROM CARDADIA
                  WHERE DTCARDAPIO BETWEEN P_DATAINI AND P_DATAFINAL
                    AND CDFILIAL = F.CDFILIAL) AS QTCOMESTICAR,
                (SELECT SUM(QTCOMEFETCAR)
                   FROM CARDADIA
                  WHERE DTCARDAPIO BETWEEN P_DATAINI AND P_DATAFINAL
                    AND CDFILIAL = F.CDFILIAL) AS QTCOMEFETCAR
           INTO V_CDTPOCUSTO_PADRAO, V_CDFILIAL, V_NMFILIAL, V_DTPROCESSA, V_NRDIASUTEIS, V_QTCOMESTICAR, V_QTCOMEFETCAR
           FROM PARAMFILIAL P, FILIAL F
          WHERE P.CDFILIAL = P_FILIAL
            AND P.CDFILIAL = F.CDFILIAL;
      EXCEPTION
         WHEN OTHERS THEN
         V_CDTPOCUSTO_PADRAO := '03';
      END;

      OPEN CUR_PRODUTO;
      LOOP
       FETCH CUR_PRODUTO INTO V_CDPRODMOVI, V_CDPRODUTO, V_CDARVPROD, V_NMPRODUTO, V_SGUNIDADE, V_DSGRUPO;
       EXIT WHEN CUR_PRODUTO%NOTFOUND;

         ----------------------------CONSULTA ESTOQUE INICIAL--------------------------
         V_DTPOSESTCON := TO_CHAR(RETORNA_DTPOSESTCON(TO_DATE(P_DATAINI), P_FILIAL, V_CDPRODMOVI),'DD/MM/YYYY');
         V_CUSTO_UNIT_EST_INI := CUSTO_PRODUTO(P_FILIAL, V_CDPRODMOVI, V_CDTPOCUSTO_PADRAO, V_DTPOSESTCON, TRUNC(SYSDATE));

         BEGIN
            SELECT SUM(P.QTESTDIACON), SUM(P.QTESTDIACON * V_CUSTO_UNIT_EST_INI)
              INTO V_QT_ESTOQUE_INICIAL, V_VR_ESTOQUE_INICIAL
              FROM POSESTCONS P
             WHERE P.CDFILIAL    = P_FILIAL
               AND P.CDPRODUTO   = V_CDPRODMOVI
               AND P.QTESTDIACON <> 0
               AND P.DTPOSESTCON = V_DTPOSESTCON;
         EXCEPTION
            WHEN OTHERS THEN
            V_QT_ESTOQUE_INICIAL := 0;
            V_VR_ESTOQUE_INICIAL := 0;
         END;

         ----------------------------CONSULTA ESTOQUE FINAL----------------------------
         V_DTPOSESTCON := TO_CHAR(RETORNA_DTPOSESTCON(TO_DATE(P_DATAFINAL)+1, P_FILIAL, V_CDPRODMOVI),'DD/MM/YYYY');
         V_CUSTO_UNIT_EST_FIN := CUSTO_PRODUTO(P_FILIAL, V_CDPRODMOVI, V_CDTPOCUSTO_PADRAO, V_DTPOSESTCON, TRUNC(SYSDATE));

         BEGIN
            SELECT SUM(P.QTESTDIACON), SUM(P.QTESTDIACON * V_CUSTO_UNIT_EST_FIN)
              INTO V_QT_ESTOQUE_FINAL, V_VR_ESTOQUE_FINAL
              FROM POSESTCONS P
             WHERE P.CDFILIAL    = P_FILIAL
               AND P.CDPRODUTO   = V_CDPRODMOVI
               AND P.QTESTDIACON <> 0
               AND P.DTPOSESTCON = V_DTPOSESTCON;
         EXCEPTION
            WHEN OTHERS THEN
            V_QT_ESTOQUE_FINAL := 0;
            V_VR_ESTOQUE_FINAL := 0;
         END;

         -------------------------------CONSULTA ENTRADAS_NF---------------------------
         V_TIPO_LANC := 2;
         BEGIN
            SELECT  --ITLANCTOEST.IDEVOLESTOQ = 'S' /*SOMENTE O LANCAMENTOS QUE EVOLUI ESTOQUE*/
                   SUM(DECODE(ITLANCTOEST.IDEVOLESTOQ,'S',(QTLANCTOEST),0)),
                   SUM(DECODE(ITLANCTOEST.IDEVOLESTOQ,'S',(QTLANCTOEST),0) * ITLANCTOEST.VRLANCTOEST), --CUSTO DE COMPRA LIQUIDO * QUANTIDADE DE LANCADA
                   SUM(DECODE(ITLANCTOEST.IDEVOLESTOQ,'S',(QTLANCTOEST),0) * ITLANCTOEST.VRLANCTOBRUT) --CUSTO DE COMPRA BRUTO * QUANTIDADE DE LANCADA
                   --CUSTO_PRODUTO(P_FILIAL, V_CDPRODMOVI, 08, V_DTPOSESTCON, TRUNC(SYSDATE)), --CUSTO DE COMPRA LIQUIDO DA EPOCA 08
                   --CUSTO_PRODUTO(P_FILIAL, V_CDPRODMOVI, 09, V_DTPOSESTCON, TRUNC(SYSDATE)) --CUSTO DE COMPRA BRUTO DA EPOCA 09
              INTO V_QT_ENTRADAS_NF, V_VRCUSCMPLIQ, V_VRCUSCMPBRUT
              FROM ITLANCTOEST
             WHERE ITLANCTOEST.DTLANCMOVI BETWEEN  P_DATAINI AND P_DATAFINAL
               AND ITLANCTOEST.CDFILIMOVI = P_FILIAL
               AND ITLANCTOEST.CDPRODMOVI = V_CDPRODMOVI
               AND ITLANCTOEST.IDTIPOMOVI = V_TIPO_LANC;
         EXCEPTION
            WHEN OTHERS THEN
            V_QT_ENTRADAS_NF := 0;
            V_VRCUSCMPLIQ    := 0;
            V_VRCUSCMPBRUT   := 0;
         END;

         -------------------------------CONSULTA DEVOLUCOES----------------------------
         V_TIPO_LANC := 5;
         BEGIN
            SELECT --ITLANCTOEST.IDEVOLESTOQ = 'S' /*SOMENTE O LANCAMENTOS QUE EVOLUI ESTOQUE*/
                   SUM(DECODE(ITLANCTOEST.IDEVOLESTOQ,'S',(QTLANCTOEST),0)),
                   SUM(DECODE(ITLANCTOEST.IDEVOLESTOQ,'S',(QTLANCTOEST),0) * CUSTO_PRODUTO(P_FILIAL, V_CDPRODMOVI, 04, ITLANCTOEST.DTLANCMOVI, TRUNC(SYSDATE)))
                   --CUSTO_PRODUTO(P_FILIAL, V_CDPRODMOVI, 09, V_DTPOSESTCON, TRUNC(SYSDATE)) --DTLANCMOVI
              INTO V_QT_DEVOL_NF, V_VRCUSDEVOL
              FROM ITLANCTOEST
             WHERE ITLANCTOEST.DTLANCMOVI BETWEEN P_DATAINI AND P_DATAFINAL
               AND ITLANCTOEST.CDFILIMOVI = P_FILIAL
               AND ITLANCTOEST.CDPRODMOVI = V_CDPRODMOVI
               AND ITLANCTOEST.IDTIPOMOVI = V_TIPO_LANC;
         EXCEPTION
            WHEN OTHERS THEN
            V_QT_DEVOL_NF := 0;
            V_VRCUSDEVOL  := 0;
         END;

         -------------------------------CONSULTA SAIDAS------------------------
         V_TIPO_LANC := 4;
         BEGIN
            SELECT  --ITLANCTOEST.IDEVOLESTOQ = 'S' /*SOMENTE O LANCAMENTOS QUE EVOLUI ESTOQUE*/
                   NVL(SUM(DECODE(ITLANCTOEST.IDEVOLESTOQ,'S',(QTLANCTOEST),0)),0),
                   SUM(DECODE(ITLANCTOEST.IDEVOLESTOQ,'S',(QTLANCTOEST),0) * CUSTO_PRODUTO(P_FILIAL, V_CDPRODMOVI, 04, ITLANCTOEST.DTLANCMOVI, TRUNC(SYSDATE)))
              INTO V_QT_TRANSF_NF, V_VRCUSTRANSF
              FROM ITLANCTOEST
             WHERE ITLANCTOEST.DTLANCMOVI BETWEEN  P_DATAINI AND P_DATAFINAL
               AND ITLANCTOEST.CDFILIMOVI = P_FILIAL
               AND ITLANCTOEST.CDPRODMOVI = V_CDPRODMOVI
               AND ITLANCTOEST.IDTIPOMOVI = V_TIPO_LANC
               AND ITLANCTOEST.IDENTRSAIDOP||ITLANCTOEST.CDTIPOOPER IN (SELECT IDENTRSAIDOP||CDTIPOOPER
                                                                          FROM TIPOOPERAC
                                                                         WHERE IDORIGDESTOP = 'L'
                                                                           AND IDENTRSAIDOP = 'S');
         EXCEPTION
            WHEN OTHERS THEN
            V_QT_TRANSF_NF := 0;
            V_VRCUSTRANSF  := 0;
         END;

         TAB_APUR.CDFILIAL     := V_CDFILIAL;
         TAB_APUR.NMFILIAL     := V_NMFILIAL;
         TAB_APUR.NRREFESTI    := V_QTCOMESTICAR;
         TAB_APUR.NRREFEFET    := V_QTCOMEFETCAR;
         TAB_APUR.DIASUTEIS    := V_NRDIASUTEIS;
         TAB_APUR.DSGRUPOPROD  := V_DSGRUPO;
         TAB_APUR.CDARVPROD    := V_CDARVPROD;
         TAB_APUR.CDPRODUTO    := V_CDPRODMOVI;
         TAB_APUR.NMPRODUTO    := V_NMPRODUTO;
         TAB_APUR.SGUNIDADE    := V_SGUNIDADE;
         TAB_APUR.QTESTINI     := V_QT_ESTOQUE_INICIAL;
         TAB_APUR.VRCUSTUNIT   := V_CUSTO_UNIT_EST_INI;
         TAB_APUR.QTENTRADA    := V_QT_ENTRADAS_NF;
         TAB_APUR.QTDEVOLUCAO  := V_QT_DEVOL_NF;
         TAB_APUR.VRCUSDEVOL   := NVL(V_VRCUSDEVOL,0);
         TAB_APUR.QTTRANSF     := V_QT_TRANSF_NF;
         TAB_APUR.VRCUSTRANSF  := NVL(V_VRCUSTRANSF,0);
         TAB_APUR.VRCUSCMPLIQ  := NVL(V_VRCUSCMPLIQ,0);
         TAB_APUR.VRCUSCMPBRUT := NVL(V_VRCUSCMPBRUT,0);
         TAB_APUR.QTESTFIN     := V_QT_ESTOQUE_FINAL;
         TAB_APUR.VRCUSTMEDEST := V_CUSTO_UNIT_EST_FIN;

         PIPE ROW (TAB_APUR);

      END LOOP;
      CLOSE CUR_PRODUTO;

      RETURN;

   END GET_APUR_CONSUMO;

END PACKAGE_APUR_CONSUMO_EST;

/