create or replace 
PROCEDURE EXPORTA_LISTA_PRECIOS(P_CDFILIAL  IN VARCHAR2,
                                P_DTINICIAL IN DATE,
                                P_DTFINAL   IN DATE,
                                P_TCDFILIAL IN CHAR)
IS
   V_CDCHAVE    VARCHAR2(60);
   V_VERFILIAL  INTEGER;
   V_FILIAL     VARCHAR2(4);
   V_VDLISTAPR  BOOLEAN;
   V_SEQUENCIAL INTEGER;
   V_VALIDA     VARCHAR2(60);
   V_MENSSITE   VARCHAR2(50);
   V_MENSPROV   VARCHAR2(50);
   V_MENSPROD   VARCHAR2(50);
   BOSITESAP    BOOLEAN;
   BOPROVSAP    BOOLEAN;
   BOPRODSAP    BOOLEAN;
   V_FILATUA    VARCHAR2(4);
   V_NRCOLATUA  VARCHAR2(10);
   
   PROCEDURE VALIDA(P_CHAVE IN VARCHAR2, P_VALIDA IN VARCHAR2, P_BOSITESAP IN BOOLEAN, 
                    P_BOPROVSAP IN BOOLEAN, P_BOPRODSAP IN BOOLEAN, P_SEQUENCIAL IN INTEGER, 
                    P_MENSSITE IN VARCHAR2, P_MENSPROV IN VARCHAR2, P_MENSPROD IN VARCHAR2,
                    P_BORETORNO IN OUT BOOLEAN)
   IS
      BORETORNO BOOLEAN;
   BEGIN
      IF (P_VALIDA = P_CHAVE) AND NOT(P_BORETORNO) THEN
         UPDATE COLETAPR
            SET USR_DTEXPORTA = NULL
          WHERE CDFILIAL    = SUBSTR(P_VALIDA, 1, 4)
            AND NRCOLEPRECO = SUBSTR(P_VALIDA, 5, 10);
      ELSE
         IF ((TRIM(P_VALIDA) IS NOT NULL) AND (P_BORETORNO)) THEN
            UPDATE COLETAPR
               SET USR_DTEXPORTA = SYSDATE
             WHERE CDFILIAL    = SUBSTR(P_VALIDA, 1, 4)
               AND NRCOLEPRECO = SUBSTR(P_VALIDA, 5, 10);
         END IF;
      END IF;
      
      IF (P_BOSITESAP) OR (P_BOPROVSAP) OR (P_BOPRODSAP) THEN
         P_BORETORNO := FALSE;
         
         IF SQL%ROWCOUNT > 0 THEN
            ROLLBACK;
         END IF;
      ELSE
         P_BORETORNO := TRUE;
      END IF;
      
      IF (P_BOSITESAP) THEN
         INSERT INTO USR_SAP_LOG(IDINTERFACE, IDSEQLOG, IDPROCESSO, DTLOG, STATUS, OBSSTATUS)
         VALUES(P_SEQUENCIAL, 'I', 6, SYSDATE, 1, 'Site SAP no registrado para el site TecFood '|| P_MENSSITE);
      END IF;
      
      IF (P_BOPROVSAP) THEN
         INSERT INTO USR_SAP_LOG(IDINTERFACE, IDSEQLOG, IDPROCESSO, DTLOG, STATUS, OBSSTATUS)
         VALUES(P_SEQUENCIAL, 'I', 6, SYSDATE, 1, 'Proveedor '||P_MENSPROV||' no posee c'||CHR(243)||'digo SAP');
      END IF;
      
      IF (P_BOPRODSAP) THEN
         INSERT INTO USR_SAP_LOG(IDINTERFACE, IDSEQLOG, IDPROCESSO, DTLOG, STATUS, OBSSTATUS)
         VALUES(P_SEQUENCIAL, 'I', 6, SYSDATE, 1, 'Producto '||P_MENSPROD||' no posee c'||CHR(243)||'digo SAP');
      END IF;
   END;
   
BEGIN
   V_VERFILIAL := LENGTH(REGEXP_REPLACE(TRIM(P_CDFILIAL), '\d'));
   V_VDLISTAPR := TRUE;
   V_VALIDA    := '   ';
   
   IF ((V_VERFILIAL > 2) OR (P_TCDFILIAL = 'T')) THEN     
      FOR C_LISTAPRECO IN
          (SELECT I.CDFILIAL, I.NRCOLEPRECO, I.CDMARCA, I.CDREGIAO, F.NRINSJURFORN NIF_PROVEE, P.CDPRODUTO,
                  I.VRPRECUNIITM, I.DTINIVALITM, I.DTFINVALITM, M.CDPRODSAP, F.CDEXFORNECED CDPROVEESAP,
                  PF.CDEXFILIAL CDSITESAP, P.SGUNIDADE UOMSAP, P.NMPRODUTO, F.CDFORNECED
             FROM ITEMCOLE I, PRODUTO P, MARCPROD M, FORNECEDOR F, PARAMFILIAL PF, COLETAPR C
            WHERE I.CDPRODUTO   = P.CDPRODUTO
              AND M.CDPRODUTO   = P.CDPRODUTO
              AND I.CDFORNECED  = F.CDFORNECED
              AND I.CDFILIAL    = PF.CDFILIAL
              AND C.CDFILIAL    = I.CDFILIAL
              AND C.NRCOLEPRECO = I.NRCOLEPRECO
              AND I.IDITMLISPREC = 'S'
              AND ((INSTR(P_CDFILIAL, '_'||I.CDFILIAL||'_') > 0) OR (P_TCDFILIAL = 'T'))
              AND I.DTINIVALITM BETWEEN P_DTINICIAL AND P_DTFINAL
              AND C.USR_DTEXPORTA IS NULL
            ORDER BY C.CDFILIAL, C.NRCOLEPRECO)
      LOOP
         V_CDCHAVE := C_LISTAPRECO.CDFILIAL||C_LISTAPRECO.NRCOLEPRECO||C_LISTAPRECO.CDMARCA||C_LISTAPRECO.CDREGIAO||C_LISTAPRECO.CDFORNECED||C_LISTAPRECO.CDPRODUTO;
         BOSITESAP := FALSE;
         BOPROVSAP := FALSE;
         BOPRODSAP := FALSE;
         
         SELECT USR_SAP_IDINTERFACE.NEXTVAL
           INTO V_SEQUENCIAL
           FROM DUAL;
         
         IF ((TRIM(V_VALIDA) IS NOT NULL) AND (SUBSTR(V_CDCHAVE, 1, 14) <> V_VALIDA)) THEN
            V_VDLISTAPR := TRUE;
            COMMIT;
         END IF;
         
         IF (C_LISTAPRECO.CDSITESAP IS NULL) THEN
            BOSITESAP  := TRUE;
            V_MENSSITE := C_LISTAPRECO.CDFILIAL;
         END IF;
         
         IF (C_LISTAPRECO.CDPROVEESAP IS NULL) THEN
            BOPROVSAP  := TRUE;
            V_MENSPROV := C_LISTAPRECO.NIF_PROVEE;
         END IF;
         
         IF (C_LISTAPRECO.CDPRODSAP IS NULL) THEN
            BOPRODSAP  := TRUE;
            V_MENSPROD := C_LISTAPRECO.NMPRODUTO;
         END IF;
         
         VALIDA(V_CDCHAVE, V_VALIDA, BOSITESAP, BOPROVSAP, BOPRODSAP, V_SEQUENCIAL, V_MENSSITE, V_MENSPROV, V_MENSPROD,
                V_VDLISTAPR);
         
         IF (V_VDLISTAPR) THEN   
            INSERT INTO USR_SAP_LISTPRECO(CDCHAVE, CDSITESAP, CDPRODSAP, UOMSAP, NIF_PROVEE, 
                                          CDPROVEESAP, VRUNITARIO, DTINIVIG, DTFINVIG,
                                          IDINTERFACE, TPINTERFACE, DTINTERFACEORI, DTINTERFACEDEST, STATUS, APPORIGEM, APPDESTINO)
            VALUES(V_CDCHAVE, C_LISTAPRECO.CDSITESAP, C_LISTAPRECO.CDPRODSAP, C_LISTAPRECO.UOMSAP, C_LISTAPRECO.NIF_PROVEE,
                   C_LISTAPRECO.CDPROVEESAP, C_LISTAPRECO.VRPRECUNIITM, C_LISTAPRECO.DTINIVALITM, C_LISTAPRECO.DTFINVALITM,
                   V_SEQUENCIAL, 'I', SYSDATE, NULL, 1, 1, 2);
                   
            IF V_VALIDA <> SUBSTR(V_CDCHAVE, 1, 14) THEN
               V_VALIDA := SUBSTR(V_CDCHAVE, 1, 14);
            END IF;
            V_FILATUA   := SUBSTR(V_VALIDA, 1, 4);
            V_NRCOLATUA := SUBSTR(V_VALIDA, 5, 10);
         END IF;
      END LOOP;
   ELSE
      V_FILIAL := REGEXP_REPLACE(P_CDFILIAL, '\D');
      
      FOR C_LISTAPRECO IN
          (SELECT I.CDFILIAL, I.NRCOLEPRECO, I.CDMARCA, I.CDREGIAO, F.NRINSJURFORN NIF_PROVEE, P.CDPRODUTO,
                  I.VRPRECUNIITM, I.DTINIVALITM, I.DTFINVALITM, M.CDPRODSAP, F.CDEXFORNECED CDPROVEESAP,
                  PF.CDEXFILIAL CDSITESAP, P.SGUNIDADE UOMSAP, P.NMPRODUTO, F.CDFORNECED
             FROM ITEMCOLE I, PRODUTO P, MARCPROD M, FORNECEDOR F, PARAMFILIAL PF, COLETAPR C
            WHERE I.CDPRODUTO   = P.CDPRODUTO
              AND M.CDPRODUTO   = P.CDPRODUTO
              AND I.CDFORNECED  = F.CDFORNECED
              AND I.CDFILIAL    = PF.CDFILIAL
              AND C.CDFILIAL    = I.CDFILIAL
              AND C.NRCOLEPRECO = I.NRCOLEPRECO
              AND I.CDFILIAL    = V_FILIAL
              AND I.IDITMLISPREC = 'S'
              AND I.DTINIVALITM BETWEEN P_DTINICIAL AND P_DTFINAL
              AND C.USR_DTEXPORTA IS NULL
            ORDER BY C.CDFILIAL, C.NRCOLEPRECO)
      LOOP
         V_CDCHAVE := C_LISTAPRECO.CDFILIAL||C_LISTAPRECO.NRCOLEPRECO||C_LISTAPRECO.CDMARCA||C_LISTAPRECO.CDREGIAO||C_LISTAPRECO.CDFORNECED||C_LISTAPRECO.CDPRODUTO;
         BOSITESAP := FALSE;
         BOPROVSAP := FALSE;
         BOPRODSAP := FALSE;
         
         SELECT USR_SAP_IDINTERFACE.NEXTVAL
           INTO V_SEQUENCIAL
           FROM DUAL;
         
         IF ((TRIM(V_VALIDA) IS NOT NULL) AND (SUBSTR(V_CDCHAVE, 1, 14) <> V_VALIDA)) THEN
            V_VDLISTAPR := TRUE;
            COMMIT;
         END IF;
         
         IF (C_LISTAPRECO.CDSITESAP IS NULL) THEN
            BOSITESAP  := TRUE;
            V_MENSSITE := C_LISTAPRECO.CDFILIAL;
         END IF;
         
         IF (C_LISTAPRECO.CDPROVEESAP IS NULL) THEN
            BOPROVSAP  := TRUE;
            V_MENSPROV := C_LISTAPRECO.NIF_PROVEE;
         END IF;
         
         IF (C_LISTAPRECO.CDPRODSAP IS NULL) THEN
            BOPRODSAP  := TRUE;
            V_MENSPROD := C_LISTAPRECO.NMPRODUTO;
         END IF;
         
         VALIDA(V_CDCHAVE, V_VALIDA, BOSITESAP, BOPROVSAP, BOPRODSAP, V_SEQUENCIAL, V_MENSSITE, V_MENSPROV, V_MENSPROD,
                V_VDLISTAPR);
         
         IF (V_VDLISTAPR) THEN   
            INSERT INTO USR_SAP_LISTPRECO(CDCHAVE, CDSITESAP, CDPRODSAP, UOMSAP, NIF_PROVEE, 
                                          CDPROVEESAP, VRUNITARIO, DTINIVIG, DTFINVIG,
                                          IDINTERFACE, TPINTERFACE, DTINTERFACEORI, DTINTERFACEDEST, STATUS, APPORIGEM, APPDESTINO)
            VALUES(V_CDCHAVE, C_LISTAPRECO.CDSITESAP, C_LISTAPRECO.CDPRODSAP, C_LISTAPRECO.UOMSAP, C_LISTAPRECO.NIF_PROVEE,
                   C_LISTAPRECO.CDPROVEESAP, C_LISTAPRECO.VRPRECUNIITM, C_LISTAPRECO.DTINIVALITM, C_LISTAPRECO.DTFINVALITM,
                   V_SEQUENCIAL, 'I', SYSDATE, NULL, 1, 1, 2);
                   
            IF V_VALIDA <> SUBSTR(V_CDCHAVE, 1, 14) THEN
               V_VALIDA := SUBSTR(V_CDCHAVE, 1, 14);
            END IF;
            V_FILATUA   := SUBSTR(V_VALIDA, 1, 4);
            V_NRCOLATUA := SUBSTR(V_VALIDA, 5, 10);
         END IF;
      END LOOP;
   END IF;
   
   UPDATE COLETAPR
      SET USR_DTEXPORTA = SYSDATE
    WHERE CDFILIAL    = V_FILATUA
      AND NRCOLEPRECO = V_NRCOLATUA;
   
   COMMIT;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('EXPORTA_LISTA_PRECIOS','5.45.XXX'); END;
/