CREATE OR REPLACE PROCEDURE USR_EXP_INVENTESTOQ(P_CDFILIAL IN VARCHAR2,
                                                P_DATAINI IN DATE,
                                                P_DATAFIN IN DATE)
IS
   V_CDARVPRO  VARCHAR2(20);
   V_QTLANCTO  FLOAT;
   V_VRLANCTO  FLOAT;
   V_CDEMPRES  VARCHAR2(2);
   V_NRINSJUR  VARCHAR2(20);
   V_SEQUENCI  INTEGER;
   V_GRUPPROT  VARCHAR2(6);
   V_NRMOVEST  VARCHAR2(20);
   V_IDCREDDEB VARCHAR2(01);
   V_CONTADOR  INTEGER;

   V_QTPOSICAO     FLOAT;
   V_QTLANCMOVIEST_ENTR FLOAT;
   V_QTLANCMOVIEST_SAID FLOAT;
   V_QTDEAJUSTADA  FLOAT;

   V_CUSTTOTENTR   FLOAT;

   V_DTINICIAL     DATE;
   V_DTINIPROD     DATE;
   V_CDALMOXARIFE  VARCHAR2(6);
   V_NRLOTEESTQ    VARCHAR2(20);
   V_CDPRODMOVI    VARCHAR2(10);
BEGIN

   FOR C_FILIAL IN (SELECT F.CDFILIAL, A.CDALMOXARIFE, NVL(P.CDTIPCUSTEST, 3) CDTIPCUSTEST,
                           DECODE(PG.IDUTILDTPROC, 'S', NVL(P.DTPROCESSA, SYSDATE), SYSDATE) DTPROCESSA
                      FROM (SELECT CDFILIAL
                              FROM FILIAL
                             WHERE ((INSTR(P_CDFILIAL, '_'||CDFILIAL||'_')> 0) OR (P_CDFILIAL = 'T'))) F, ALMOXARIFADO A, PARAMFILIAL P, PARAMGERAL PG
                     WHERE F.CDFILIAL = A.CDFILIAL
                       AND P.CDFILIAL = F.CDFILIAL
                       AND (((P.IDUTILALMOX = 'S') AND (A.CDALMOXARIFE <> ' ')) OR (P.IDUTILALMOX = 'N'))
                       AND PG.CDPARGERAL = '0001'
                     ORDER BY F.CDFILIAL, A.CDALMOXARIFE)

   LOOP

      V_CDALMOXARIFE := 'X';
      V_NRLOTEESTQ   := 'X';
      V_CDPRODMOVI   := 'X';
      V_DTINICIAL    := P_DATAINI;
      V_DTINIPROD    := P_DATAINI;

      FOR C_MOVIEST IN (SELECT A.CDFILIMOVI, A.CDPRODMOVI, A.CDALMOXARIFE, A.NRLOTEESTQ, A.DTLANCESTQ, I.QTLANCTOEST AS QTAJUSTE,
                               A.CDFILIMOVI||A.CDPRODMOVI||TO_CHAR(A.DTLANCESTQ, 'YYYYMMDD') CDUNICO
                          FROM ITLANCTOEST I,
                               (SELECT L.CDFILIAL, L.CDFILIMOVI, I.CDPRODMOVI, I.CDALMOXARIFE, I.NRLOTEESTQ, L.DTLANCESTQ,
                                       MAX(I.NRLANCESTQ||I.NRSEQUITEM) AS NRLANCESTQ
                                  FROM LANCTOESTOQ L, ITLANCTOEST I, FILIAL F, PARAMFILIAL P, PARAMGERAL PG
                                 WHERE (L.CDFILIAL      = I.CDFILIAL)
                                   AND (L.NRLANCESTQ    = I.NRLANCESTQ)
                                   AND (PG.CDPARGERAL = '0001')
                                   AND (L.IDTPLANCTO  = '1')
                                   AND (L.CDFILIMOVI  = C_FILIAL.CDFILIAL)
                                   AND (L.CDFILIMOVI  = F.CDFILIAL)
                                   AND (F.CDFILIAL    = P.CDFILIAL)
                                   AND (L.DTLANCESTQ BETWEEN P_DATAINI AND P_DATAFIN)
                                   AND (L.DTLANCESTQ < DECODE(PG.IDUTILDTPROC, 'S', NVL(P.DTPROCESSA, TO_DATE(SYSDATE)), TO_DATE(SYSDATE)))
                                   AND (I.USR_DTEXPORTA IS NULL)
                                   AND (I.IDEVOLESTOQ = 'S')
                                   AND (I.IDTIPOMOVI = '1')
                                   AND (I.CDALMOXARIFE = C_FILIAL.CDALMOXARIFE)
                                   AND (I.CDLOCALESTOQ = ' ')
                                   AND (I.IDEVOLESTOQ = 'S')
                                 GROUP BY L.CDFILIAL, L.CDFILIMOVI, I.CDPRODMOVI, I.CDALMOXARIFE, I.NRLOTEESTQ, L.DTLANCESTQ) A
                         WHERE I.CDFILIAL = A.CDFILIAL
                           AND I.NRLANCESTQ = SUBSTR(A.NRLANCESTQ,1,10)
                           AND I.NRSEQUITEM = SUBSTR(A.NRLANCESTQ,11,4)
                         ORDER BY A.CDALMOXARIFE, A.CDPRODMOVI, A.NRLOTEESTQ, A.DTLANCESTQ)

      LOOP
         V_QTPOSICAO          := 0;
         V_QTLANCMOVIEST_ENTR := 0;
         V_QTLANCMOVIEST_SAID := 0;

         V_QTDEAJUSTADA       := 0;
         V_CUSTTOTENTR        := 0;
         V_QTLANCTO           := 0;
         V_VRLANCTO           := 0;

         IF ((V_CDALMOXARIFE <> C_MOVIEST.CDALMOXARIFE) OR (V_NRLOTEESTQ <> C_MOVIEST.NRLOTEESTQ) OR (V_CDPRODMOVI <> C_MOVIEST.CDPRODMOVI)) THEN
            V_DTINICIAL := P_DATAINI;
         ELSE
            V_DTINICIAL := V_DTINIPROD + 1;
         END IF;

         -- Posicao de estoque
         BEGIN
            SELECT MAX(QTESTOQDIA)
              INTO V_QTPOSICAO
              FROM POSESTDIA A
             WHERE CDFILIAL      = C_MOVIEST.CDFILIMOVI
               AND CDALMOXARIFE  = C_MOVIEST.CDALMOXARIFE
               AND CDLOCALESTOQ  = ' '
               AND NRLOTEESTQ    = C_MOVIEST.NRLOTEESTQ
               AND CDPRODUTO     = C_MOVIEST.CDPRODMOVI
               AND DTPOSIESTQ    = (SELECT MAX(P.DTPOSIESTQ)
                                      FROM POSESTDIA P
                                     WHERE P.CDFILIAL     = A.CDFILIAL
                                       AND P.CDPRODUTO    = A.CDPRODUTO
                                       AND P.CDALMOXARIFE = A.CDALMOXARIFE
                                       AND P.CDLOCALESTOQ = A.CDLOCALESTOQ
                                       AND P.NRLOTEESTQ   = A.NRLOTEESTQ
                                       AND P.NRSUBLOTE    = A.NRSUBLOTE
                                       AND P.DTPOSIESTQ   <= V_DTINICIAL);
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_QTPOSICAO := 0;
         END;

         -- Total Entradas
         BEGIN
            SELECT SUM(QTLANCTOEST)
              INTO V_QTLANCMOVIEST_ENTR
              FROM ITLANCTOEST R
             WHERE CDFILIMOVI    = C_MOVIEST.CDFILIMOVI
               AND CDPRODMOVI    = C_MOVIEST.CDPRODMOVI
               AND CDALMOXARIFE  = C_MOVIEST.CDALMOXARIFE
               AND CDLOCALESTOQ  = ' '
               AND NRLOTEESTQ    = C_MOVIEST.NRLOTEESTQ
               AND IDTIPOMOVI = '2'
               AND DTLANCMOVI BETWEEN V_DTINICIAL AND C_MOVIEST.DTLANCESTQ
               AND IDEVOLESTOQ = 'S';
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_QTLANCMOVIEST_ENTR := 0;
         END;

         -- Total Saidas
         BEGIN
            SELECT SUM(QTLANCTOEST)
              INTO V_QTLANCMOVIEST_SAID
              FROM ITLANCTOEST R
             WHERE CDFILIMOVI    = C_MOVIEST.CDFILIMOVI
               AND CDPRODMOVI    = C_MOVIEST.CDPRODMOVI
               AND CDALMOXARIFE  = C_MOVIEST.CDALMOXARIFE
               AND CDLOCALESTOQ  = ' '
               AND NRLOTEESTQ    = C_MOVIEST.NRLOTEESTQ
               AND IDTIPOMOVI IN ('3','4','5')
               AND DTLANCMOVI BETWEEN V_DTINICIAL AND C_MOVIEST.DTLANCESTQ
               AND IDEVOLESTOQ = 'S';
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_QTLANCMOVIEST_SAID := 0;
         END;

         V_QTDEAJUSTADA := ROUND(NVL(C_MOVIEST.QTAJUSTE,0) - (NVL(V_QTPOSICAO,0) + NVL(V_QTLANCMOVIEST_ENTR,0) - NVL(V_QTLANCMOVIEST_SAID,0)),3);

         V_CUSTTOTENTR := CUSTO_PRODUTO_LCEST(C_MOVIEST.CDFILIMOVI, C_MOVIEST.CDPRODMOVI, C_FILIAL.CDTIPCUSTEST, C_MOVIEST.DTLANCESTQ, C_FILIAL.DTPROCESSA,
                                             C_MOVIEST.CDALMOXARIFE, ' ', C_MOVIEST.NRLOTEESTQ, ' ');


         SELECT P.CDARVPROD
           INTO V_CDARVPRO
           FROM PRODUTO P
          WHERE P.CDPRODUTO = C_MOVIEST.CDPRODMOVI;

         BEGIN
            SELECT U.USR_CDTPPRODUTO
              INTO V_GRUPPROT
              FROM PRODUTO P, USR_PRODTPPRO U
             WHERE P.CDPRODUTO = U.USR_CDPRODTPPRO
               AND INSTR(V_CDARVPRO,P.CDARVPROD) = 1;
         EXCEPTION
            WHEN OTHERS THEN
               V_GRUPPROT := NULL;
         END;

         IF (V_GRUPPROT IS NOT NULL) THEN
            IF NVL(V_QTDEAJUSTADA,0) < 0 THEN
               V_QTLANCTO  := ABS(V_QTDEAJUSTADA);
               V_VRLANCTO  := ROUND(ABS(V_QTDEAJUSTADA * V_CUSTTOTENTR), 5);
               V_IDCREDDEB := 'D';
            ELSE
               V_QTLANCTO  := V_QTDEAJUSTADA;
               V_VRLANCTO  := ROUND(V_QTDEAJUSTADA * V_CUSTTOTENTR, 5);
               V_IDCREDDEB := 'C';
            END IF;

            SELECT COUNT(1)
              INTO V_CONTADOR
              FROM USR_PROT_MOVIESTOQ U
             WHERE CDFILIAL        = C_MOVIEST.CDFILIMOVI
               AND CDALMOXARIFADO  = C_MOVIEST.CDALMOXARIFE
               AND CDPRODUTO       = V_CDARVPRO
               AND DTLANCESTQ      = C_MOVIEST.DTLANCESTQ
               AND IDINTERFACE     = '1'
               AND IDOPERACAO      = '2'
               AND IDCREDDEB       = V_IDCREDDEB
               AND SEQUENCIAL = (SELECT MAX(SEQUENCIAL)
                                   FROM USR_PROT_MOVIESTOQ
                                  WHERE CDFILIAL       = U.CDFILIAL
                                    AND CDALMOXARIFADO = U.CDALMOXARIFADO
                                    AND CDPRODUTO      = U.CDPRODUTO
                                    AND DTLANCESTQ     = U.DTLANCESTQ
                                    AND IDCREDDEB      = U.IDCREDDEB
                                    AND IDOPERACAO     = '2');

            IF (V_CONTADOR = 0) THEN
               SELECT CDEMPRESA, NRINSJURFILI
                 INTO V_CDEMPRES, V_NRINSJUR
                 FROM FILIAL
                WHERE CDFILIAL = C_MOVIEST.CDFILIMOVI;

               SELECT USR_PROT_MOVIEST.NEXTVAL
                 INTO V_SEQUENCI
                 FROM DUAL;

               INSERT INTO USR_PROT_MOVIESTOQ(SEQUENCIAL, CDFILIAL, CDEMPRESA, CDPRODUTO, IDOPERACAO, CDALMOXARIFADO,
                                              DTLANCESTQ, QTLANCTOEST, VRLANCTOEST, IDCREDDEB, GRUPO_PROT, CNPJ_EMP_MAE,
                                              STATUS, LOG_ERRO, IDINTERFACE, DTGRAVACAO, HRGRAVACAO, DTIMPORTACAO, HRIMPORTACAO,
                                              CDUNICO)
               VALUES(V_SEQUENCI, C_MOVIEST.CDFILIMOVI, V_CDEMPRES, V_CDARVPRO, '2', C_MOVIEST.CDALMOXARIFE,
                      C_MOVIEST.DTLANCESTQ, ABS(V_QTLANCTO), ABS(NVL(V_VRLANCTO, 0)), V_IDCREDDEB, V_GRUPPROT, V_NRINSJUR,
                      'A', NULL, '1', SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI'), NULL, NULL,
                      C_MOVIEST.CDUNICO);
            ELSE
               UPDATE USR_PROT_MOVIESTOQ
                  SET QTLANCTOEST = (QTLANCTOEST+ ABS(V_QTLANCTO)),
                      VRLANCTOEST = (VRLANCTOEST+ABS(NVL(V_VRLANCTO, 0))),
                      IDCREDDEB   = V_IDCREDDEB,
                      DTGRAVACAO  = SYSDATE,
                      HRGRAVACAO  = TO_CHAR(SYSDATE, 'HH24:MI'),
                      DTIMPORTACAO = NULL,
                      HRIMPORTACAO = NULL,
                      LOG_ERRO     = NULL
                WHERE SEQUENCIAL = (SELECT MAX(SEQUENCIAL)
                                      FROM USR_PROT_MOVIESTOQ
                                     WHERE CDFILIAL   = C_MOVIEST.CDFILIMOVI
                                       AND CDPRODUTO  = V_CDARVPRO
                                       AND DTLANCESTQ = C_MOVIEST.DTLANCESTQ
                                       AND IDINTERFACE = '1'
                                       AND IDOPERACAO = '2');
            END IF;

            FOR C_ATUAL_ITLANCTO IN
              (SELECT I.CDFILIAL, I.NRLANCESTQ, I.NRSEQUITEM
                 FROM LANCTOESTOQ L, ITLANCTOEST I, FILIAL F, PARAMFILIAL P, PARAMGERAL PG
                WHERE (L.CDFILIAL      = I.CDFILIAL)
                  AND (L.NRLANCESTQ    = I.NRLANCESTQ)
                  AND (PG.CDPARGERAL = '0001')
                  AND (L.CDFILIMOVI = C_MOVIEST.CDFILIMOVI)
                  AND (L.IDTPLANCTO  = '1')
                  AND (L.CDFILIMOVI  = F.CDFILIAL)
                  AND (F.CDFILIAL    = P.CDFILIAL)
                  AND (L.DTLANCESTQ BETWEEN V_DTINICIAL AND P_DATAFIN)
                  AND (L.DTLANCESTQ < DECODE(PG.IDUTILDTPROC, 'S', NVL(P.DTPROCESSA, TO_DATE(SYSDATE)), TO_DATE(SYSDATE)))
                  AND (I.CDPRODMOVI = C_MOVIEST.CDPRODMOVI)
                  AND (I.CDALMOXARIFE = C_MOVIEST.CDALMOXARIFE)
                  AND (I.IDEVOLESTOQ = 'S'))
            LOOP
               UPDATE ITLANCTOEST
                  SET USR_DTEXPORTA = SYSDATE
                WHERE CDFILIAL   = C_ATUAL_ITLANCTO.CDFILIAL
                  AND NRLANCESTQ = C_ATUAL_ITLANCTO.NRLANCESTQ
                  AND NRSEQUITEM = C_ATUAL_ITLANCTO.NRSEQUITEM;
            END LOOP;
         END IF;

         V_DTINIPROD := C_MOVIEST.DTLANCESTQ;

         V_CDALMOXARIFE := C_MOVIEST.CDALMOXARIFE;
         V_NRLOTEESTQ   := C_MOVIEST.NRLOTEESTQ; 
         V_CDPRODMOVI   := C_MOVIEST.CDPRODMOVI;
      END LOOP;
      COMMIT;
   END LOOP;
   COMMIT;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('USR_EXP_INVENTESTOQ','6.05.XXX'); END;
/
