create or replace PROCEDURE PROT_EXPORTA_POS_ESTOQUE
IS
V_CPASS      VARCHAR2(05) := 'SPORT' ;
V_NOPC       VARCHAR2(01);
V_CDFILIAL   VARCHAR2(04);
V_CEMPAUX    VARCHAR2(02);
V_CFILAUX    VARCHAR2(02);
V_D3_EMISSAO VARCHAR2(08); 
V_D3_COD     VARCHAR2(15); 
V_D3_UM      VARCHAR2(02); 
V_D3_LOCAL   VARCHAR2(02);
V_NRLANCESTQ VARCHAR2(10);
V_D3_QUANT   FLOAT; 
V_ERRO       VARCHAR2(500);
V_CDFILIMOVI VARCHAR2(04);
V_CDFILIAL_TEK VARCHAR2(02);

CURSOR C_MOVESTOQ IS 
SELECT TO_CHAR(LANCTOESTOQ.DTLANCESTQ,'YYYYMMDD') D3_EMISSAO,
       CDPRODMOVI AS D3_COD,
       SUBSTR(PRODUTO.SGUNIDADE,0,2) AS D3_UM,
       QTLANCTOEST AS D3_QUANT,
       NVL(ITLANCTOEST.CDALMOXARIFE, ' ') D3_LOCAL,
       LANCTOESTOQ.NRLANCESTQ,
       LANCTOESTOQ.CDFILIMOVI,
       (CASE WHEN ITLANCTOEST.DTEXPXMLPROT IS NULL THEN '3'
             WHEN ITLANCTOEST.DTEXPXMLPROT < ITLANCTOEST.DTULTATU THEN '4'
            END) AS NOPC, LANCTOESTOQ.CDFILIAL, FIL.CDFILIAL_TEK, FIL.CDFIL_PROTHEUS, FIL.CDEMPRE_PROTHEUS
  FROM LANCTOESTOQ 
 INNER JOIN ITLANCTOEST 
    ON LANCTOESTOQ.CDFILIAL = ITLANCTOEST.CDFILIAL
   AND LANCTOESTOQ.NRLANCESTQ = ITLANCTOEST.NRLANCESTQ
   AND ITLANCTOEST.IDTIPOMOVI = '4'--SOMENTE RETIRADAS
 INNER JOIN PRODUTO  
    ON ITLANCTOEST.CDPRODMOVI = PRODUTO.CDPRODUTO
  LEFT JOIN (SELECT FILIAL.CDFILIAL AS CDFILIAL_TEK, EMPRESA.CDEMPRESA AS CDEMPR_TEK, EMPRESA.NMEMPRESA AS NMEMPR_TEK, NVL(PARAMFILIAL.CDALMOXARIFE,' ') AS CDALMOXARIFE_TEK, NVL(PARAMFILIAL.CDLOCALESTOQ, ' ') AS CDLOCALESTOQ_TEK,
                    PARAMEMPRESA.CDEXPORTEMPR AS CDEMPRE_PROTHEUS, PARAMEMPRESA.CDEMPINTEGRACAO AS CDFIL_PROTHEUS, EMPRESA.NRINSJUREMPR, 'FIL' CDKEY
               FROM PARAMEMPRESA
              INNER JOIN EMPRESA
                 ON EMPRESA.CDEMPRESA = PARAMEMPRESA.CDEMPRESA
              INNER JOIN FILIAL
                 ON FILIAL.CDEMPRESA = PARAMEMPRESA.CDEMPRESA
              INNER JOIN PARAMFILIAL
                 ON FILIAL.CDFILIAL = PARAMFILIAL.CDFILIAL
              ORDER BY CDFILIAL_TEK, CDEMPR_TEK) FIL
    ON LANCTOESTOQ.CDFILIMOVI = FIL.CDFILIAL_TEK
  
  /*(SELECT F.CDFILIAL, P.CDEXFILIAL, F.CDEMPRESA, 'FIL' CDKEY
               FROM FILIAL F, PARAMFILIAL P
              WHERE F.CDFILIAL = P.CDFILIAL
                AND F.CDEMPRESA <> '03'
                AND F.CDFILIAL = '0001'
                AND CDEXFILIAL IS NOT NULL) FIL
   ON FIL.CDKEY = 'FIL' */   
 WHERE LANCTOESTOQ.IDTPLANCTO = 'S'
   AND ((ITLANCTOEST.DTEXPXMLPROT IS NULL) OR (ITLANCTOEST.DTEXPXMLPROT < ITLANCTOEST.DTULTATU));
   
BEGIN

   DELETE FROM USR_PROT_LANCTOESTOQ;
   COMMIT;

   DELETE FROM USR_PROT_ITLANCTOEST;
   COMMIT;

   DELETE USR_PROT_INTEGRACAO
    WHERE CDLOG = 'EnviarEstoque';
   COMMIT;

      OPEN C_MOVESTOQ;
      LOOP
         FETCH C_MOVESTOQ INTO V_D3_EMISSAO, V_D3_COD, V_D3_UM, V_D3_QUANT, V_D3_LOCAL, V_NRLANCESTQ, V_CDFILIMOVI, V_NOPC, V_CDFILIAL, V_CDFILIAL_TEK, V_CFILAUX, V_CEMPAUX;
         EXIT WHEN C_MOVESTOQ%NOTFOUND;

            INSERT INTO USR_PROT_LANCTOESTOQ(CPASS, NOPC, D3_TM, D3_EMISSAO, D3_DOC, CEMPAUX, CFILAUX, CDFILIAL, NRLANCESTQ)

            VALUES(V_CPASS, V_NOPC, NULL, V_D3_EMISSAO, NULL, V_CEMPAUX, V_CFILAUX, V_CDFILIAL, V_NRLANCESTQ);

            INSERT INTO USR_PROT_ITLANCTOEST(D3_COD, D3_UM, D3_QUANT, D3_LOCAL, CDFILIAL, NRLANCESTQ)

            VALUES(V_D3_COD, V_D3_UM, V_D3_QUANT, V_D3_LOCAL, V_CDFILIAL, V_NRLANCESTQ);

            UPDATE ITLANCTOEST
               SET DTEXPXMLPROT = SYSDATE
             WHERE CDPRODMOVI = V_D3_COD
               AND NRLANCESTQ = V_NRLANCESTQ
               AND CDFILIAL   = V_CDFILIAL
               AND CDFILIMOVI = V_CDFILIMOVI;

      END LOOP;
      CLOSE C_MOVESTOQ;

      COMMIT;


END;
/

-- VERSAO --
BEGIN ATUVEROBJ('PROT_EXPORTA_POS_ESTOQUE','6.25.XXX'); END;
/