create or replace
PROCEDURE USR_EXP_MOVRETI(P_CDFILIAL IN VARCHAR2,
                          P_DATAINI IN DATE,
                          P_DATAFIN IN DATE)
IS
   V_CDFILIAL VARCHAR2(4);
   V_CDPRODUT VARCHAR2(10);
   V_CDARVPRO VARCHAR2(20);
   V_CDALMOXA VARCHAR2(6);
   V_DTLANCTO DATE;
   V_QTLANCTO FLOAT;
   V_VRLANCTO FLOAT;
   V_CDEMPRES VARCHAR2(2);
   V_NRLOTEESTQ VARCHAR2(20);
   V_NRINSJUR VARCHAR2(20);
   V_SEQUENCI INTEGER;
   V_GRUPPROT VARCHAR2(6);
   V_CDUNICO  VARCHAR2(22);
   V_CDTIPCUSTEST VARCHAR2(02);
   V_DTPROCESSA DATE;
   V_CONTADOR INTEGER;

   V_CUSTTOTENTR   FLOAT;
   V_CDTIPORETI    VARCHAR2(02);

   CURSOR C_MOVIEST(C_CDFILIAL IN VARCHAR2, C_DATAINI IN DATE, C_DATAFIN IN DATE) IS
      SELECT L.CDFILIMOVI, I.CDPRODMOVI, I.CDALMOXARIFE, I.NRLOTEESTQ, L.DTLANCESTQ, SUM(I.QTLANCTOEST), ROUND(SUM(NVL(I.VRTOTLANCTO, (I.QTLANCTOEST * NVL(I.VRUNILANCTO, 0)))),2),
             I.CDFILIMOVI||I.CDPRODMOVI||TO_CHAR(L.DTLANCESTQ, 'YYYYMMDD') CDUNICO,
             NVL(P.CDTIPCUSTEST, 3) CDTIPCUSTEST, DECODE(PG.IDUTILDTPROC, 'S', NVL(P.DTPROCESSA, SYSDATE), SYSDATE) DTPROCESSA,
             I.CDTIPORETI
        FROM LANCTOESTOQ L, ITLANCTOEST I, FILIAL F, PARAMFILIAL P, PARAMGERAL PG
       WHERE (L.CDFILIAL   = I.CDFILIAL)
         AND (L.NRLANCESTQ = I.NRLANCESTQ)
         AND (L.CDFILIMOVI = F.CDFILIAL)
         AND (F.CDFILIAL   = P.CDFILIAL)
         AND (PG.CDPARGERAL = '0001')
         AND (L.IDTPLANCTO = '3')
         AND (L.DTLANCESTQ BETWEEN C_DATAINI AND C_DATAFIN)
         AND (L.DTLANCESTQ <= DECODE(PG.IDUTILDTPROC,'S', NVL(P.DTPROCESSA, TO_DATE(SYSDATE)), TO_DATE(SYSDATE)))
         AND ((INSTR(C_CDFILIAL, '_'||L.CDFILIMOVI||'_')> 0) OR (C_CDFILIAL = 'T'))
         AND (I.USR_DTEXPORTA IS NULL)
         AND (I.IDEVOLESTOQ = 'S')
       GROUP BY L.CDFILIMOVI, I.CDPRODMOVI, I.CDALMOXARIFE, I.NRLOTEESTQ, L.DTLANCESTQ, P.DTPROCESSA,
             I.CDFILIMOVI||I.CDPRODMOVI||TO_CHAR(L.DTLANCESTQ, 'YYYYMMDD'),
             NVL(P.CDTIPCUSTEST, 3), DECODE(PG.IDUTILDTPROC, 'S', NVL(P.DTPROCESSA, SYSDATE), SYSDATE),
             I.CDTIPORETI;
BEGIN
   V_CUSTTOTENTR := 0;

   OPEN C_MOVIEST(P_CDFILIAL, P_DATAINI, P_DATAFIN);
   LOOP
      FETCH C_MOVIEST INTO V_CDFILIAL, V_CDPRODUT, V_CDALMOXA, V_NRLOTEESTQ, V_DTLANCTO, V_QTLANCTO, V_VRLANCTO,
                           V_CDUNICO, V_CDTIPCUSTEST, V_DTPROCESSA, V_CDTIPORETI;
      EXIT WHEN C_MOVIEST%NOTFOUND;

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

      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
         SELECT COUNT(1)
           INTO V_CONTADOR
           FROM USR_PROT_MOVIESTOQ U
          WHERE CDFILIAL    = V_CDFILIAL
            AND CDALMOXARIFADO  = V_CDALMOXA
            AND CDPRODUTO   = V_CDARVPRO
            AND DTLANCESTQ  = V_DTLANCTO
            AND IDINTERFACE = '1'
            AND IDOPERACAO = '1'
            AND CDTIPORETI = V_CDTIPORETI
            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 IDINTERFACE    = U.IDINTERFACE
                                 AND IDOPERACAO     = U.IDOPERACAO
                                 AND CDTIPORETI     = U.CDTIPORETI);

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

            SELECT USR_PROT_MOVIEST.NEXTVAL
              INTO V_SEQUENCI
              FROM DUAL;

           V_CUSTTOTENTR := CUSTO_PRODUTO_LCEST(V_CDFILIAL, V_CDPRODUT, V_CDTIPCUSTEST, V_DTLANCTO, V_DTPROCESSA,
                                                V_CDALMOXA, ' ', V_NRLOTEESTQ, ' ');


            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, CDTIPORETI)
            VALUES(V_SEQUENCI, V_CDFILIAL, V_CDEMPRES, V_CDARVPRO, '1', V_CDALMOXA,
                   V_DTLANCTO, ROUND(V_QTLANCTO,3), ROUND(V_QTLANCTO * V_CUSTTOTENTR,5), NULL, V_GRUPPROT, V_NRINSJUR,
                   'A', NULL, '1', SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI'),
                   NULL, NULL, V_CDUNICO, V_CDTIPORETI);
         ELSE
            UPDATE USR_PROT_MOVIESTOQ
               SET QTLANCTOEST  = (QTLANCTOEST+V_QTLANCTO),
                   VRLANCTOEST  = (VRLANCTOEST+V_VRLANCTO),
                   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   = V_CDFILIAL
                                    AND CDALMOXARIFADO  = V_CDALMOXA
                                    AND CDPRODUTO  = V_CDARVPRO
                                    AND DTLANCESTQ = V_DTLANCTO
                                    AND IDINTERFACE = '1'
                                    AND IDOPERACAO = '1'
                                    AND CDTIPORETI = V_CDTIPORETI);
         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 (L.CDFILIMOVI = F.CDFILIAL)
               AND (F.CDFILIAL   = P.CDFILIAL)
               AND (PG.CDPARGERAL = '0001')
               AND (L.IDTPLANCTO = '3')
               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 (L.CDFILIMOVI = V_CDFILIAL)
               AND (I.CDPRODMOVI = V_CDPRODUT)
               AND (I.CDALMOXARIFE = V_CDALMOXA)
               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;
   END LOOP;

   COMMIT;
END;
/

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