create or replace PROCEDURE PROT_EXPORTA_FORNECEDOR
IS
V_CDFILIAL VARCHAR2(4);
V_CDEXFILI VARCHAR2(20);
V_EMPRESA  VARCHAR2(2);
V_CPASS    VARCHAR2(5);
V_NOPC     VARCHAR2(1);
V_CCODIGO  VARCHAR2(6);
V_CLOJA    VARCHAR2(2);
V_CNOME    VARCHAR2(40);
V_CPFPJ    VARCHAR2(1);
V_CNOMEFAN VARCHAR2(9);
V_CTIPO    VARCHAR2(1);
V_CEND     VARCHAR2(40);
V_CUF      VARCHAR2(2);
V_CCODMUN  VARCHAR2(7);
V_CMUNIC   VARCHAR2(25);
V_CBAIRRO  VARCHAR2(30);
V_CCEP     VARCHAR2(8);
V_CDDD     VARCHAR2(3);
V_CFONE    VARCHAR2(15);
V_CPAIS    VARCHAR2(3);
V_CCONTATO VARCHAR2(15);
V_CCPFCNPJ VARCHAR2(14);
V_CINSCEST VARCHAR2(18);
V_CINSCMUN VARCHAR2(18);
V_CEMAIL   VARCHAR2(30);
V_CTIPPES  VARCHAR2(2);
V_COPTSN   VARCHAR2(1);
V_CDFORNECED VARCHAR2(21);
V_ERRO VARCHAR2(500);
   
CURSOR C_FILIAL IS
SELECT F.CDFILIAL, P.CDEXFILIAL, F.CDEMPRESA
  FROM FILIAL F, PARAMFILIAL P
 WHERE F.CDFILIAL = P.CDFILIAL
   AND F.CDEMPRESA <> '03'
   AND CDEXFILIAL IS NOT NULL;
   
CURSOR C_FORNECEDOR IS
SELECT DISTINCT SUBSTR(LTRIM(F.NMRAZSOCFORN), 1, 40) CNOME,
       CASE WHEN (F.IDTPIJURFORN NOT IN('F','J')) THEN 'X' ELSE F.IDTPIJURFORN END AS CPFPJ,
       SUBSTR(LTRIM(F.NMFANTFORN), 1, 9) CNOMEFAN,
       SUBSTR(LTRIM(E.DSENDEFORN), 1, 40) CEND,
       LTRIM(E.SGESTADO) CUF,
       M.CDMUNICIBGE CCODMUN,
       SUBSTR(LTRIM(M.NMMUNICIPIO), 1, 25) CMUNIC,
       LTRIM(SUBSTR(E.NMBAIRFORN,0,20)) CBAIRRO,
       NVL(SUBSTR(REGEXP_REPLACE(E.NRCEPFORN,'\D'), 1, 8),99999999) CCEP,
       NVL(E.NRDDDTELEFORN,'99') CDDD,
       SUBSTR(NVL(E.NRTELEFORN,'999999999'), 1, 15) CFONE,
       SUBSTR(A.CDPAISEXPFIS, 1, 3) CPAIS,
       SUBSTR(LTRIM(E.NMCONTFORN), 1, 15) CCONTATO,
       CASE WHEN F.IDTPIJURFORN = 'F' THEN SUBSTR(LPAD(REGEXP_REPLACE(F.NRINSJURFORN,'\D'),20,0),10)
            WHEN F.IDTPIJURFORN = 'J' THEN SUBSTR(LPAD(REGEXP_REPLACE(F.NRINSJURFORN,'\D'),20,0),7)
          ELSE SUBSTR(LPAD(REGEXP_REPLACE(F.NRINSJURFORN,'\D'),20,0),7) END CCPFCNPJ,
       SUBSTR(REGEXP_REPLACE(F.NRINSESTFORN,'\D'), 1, 18) CINSCEST,
       SUBSTR(REGEXP_REPLACE(F.NRINSMUNFORN,'\D'), 1, 18) CINSCMUN,
       SUBSTR(LTRIM(E.DSEMAILFORN), 1, 30) CEMAIL,
       CASE WHEN F.IDTPIJURFORN IN ('F') THEN 'PF' WHEN F.IDTPIJURFORN IN ('J','I') THEN 'CI' ELSE 'OS' END
       CTIPPES,
       DECODE(P.IDSIMPLESNACIONAL, 'S', '1', '2') COPTSN,
       CASE WHEN F.DTEXPXMLPROT IS NULL
            THEN '3'
            WHEN ((F.DTEXPXMLPROT <= F.DTULTATUFORN) OR (F.DTEXPXMLPROT < E.DTULTATUENFO))
            THEN '4'
        END AS NOPC,
        F.CDFORNECED,
        '00' AS CDFIL_PROTHEUS,
        FIL.CDEMPRE_PROTHEUS
 FROM FORNECEDOR F
INNER JOIN ENDEFORN E
   ON F.CDFORNECED = E.CDFORNECED
 JOIN MUNICIPIO M
   ON E.CDPAIS = M.CDPAIS
  AND E.SGESTADO = M.SGESTADO
  AND E.CDMUNICIPIO = M.CDMUNICIPIO
 JOIN PARAMFORNE P
   ON F.CDFORNECED = P.CDFORNECED
 LEFT JOIN TIPOFORN T
   ON F.CDTIPOFORN = T.CDTIPOFORN
 LEFT JOIN PAIS A
   ON E.CDPAIS = A.CDPAIS
 LEFT JOIN (SELECT PARAMEMPRESA.CDEXPORTEMPR AS CDEMPRE_PROTHEUS, 'FIL' CDKEY
               FROM PARAMEMPRESA
              INNER JOIN EMPRESA
                 ON EMPRESA.CDEMPRESA = PARAMEMPRESA.CDEMPRESA
              GROUP BY PARAMEMPRESA.CDEXPORTEMPR
              ORDER BY CDEMPRE_PROTHEUS) FIL
   ON FIL.CDKEY = 'FIL'
WHERE (E.IDTPENDEFORN = 'P')
  AND ((F.DTEXPXMLPROT IS NULL) OR (F.DTEXPXMLPROT < F.DTULTATUFORN) OR (F.DTEXPXMLPROT < E.DTULTATUENFO))
ORDER BY CDFORNECED;

BEGIN
   V_CPASS   := 'SPORT';
   V_CCODIGO := ' ';
   V_CLOJA   := '01';

   DELETE FROM USR_PROT_FORNECEDOR;
   COMMIT;

   DELETE USR_PROT_INTEGRACAO
    WHERE CDLOG = 'EnviarFornecedor'
      AND OBSLOG = NULL;

   COMMIT;

   OPEN C_FORNECEDOR;
   LOOP
      FETCH C_FORNECEDOR INTO V_CNOME, V_CPFPJ, V_CNOMEFAN, V_CEND,
                              V_CUF, V_CCODMUN, V_CMUNIC, V_CBAIRRO, V_CCEP,
                              V_CDDD, V_CFONE, V_CPAIS, V_CCONTATO, V_CCPFCNPJ,
                              V_CINSCEST, V_CINSCMUN, V_CEMAIL, V_CTIPPES, V_COPTSN,
                              V_NOPC, V_CDFORNECED, V_CDEXFILI, V_EMPRESA;
      EXIT WHEN C_FORNECEDOR%NOTFOUND;

         IF V_CPFPJ = 'X' THEN
            V_CTIPO := 'L';
         ELSE
            V_CTIPO := V_CPFPJ;
         END IF;

         INSERT INTO USR_PROT_FORNECEDOR(CPASS, NOPC, CCODIGO, CLOJA, CNOME, CPFPJ,
                                         CNOMEFAN, CTIPO, CEND, CUF, CCODMUN, CMUNIC,
                                         CBAIRRO, CCEP, CDDD, CFONE, CPAIS, CCONTATO,
                                         CCPFCNPJ, CINSCEST, CINSCMUN, CEMAIL, CTIPPES,
                                         COPTSN, CEMPAUX, CFILAUX, CDFORNECED)
         VALUES(V_CPASS, V_NOPC, V_CCODIGO, V_CLOJA, V_CNOME, V_CPFPJ,
                V_CNOMEFAN, V_CTIPO, V_CEND, V_CUF, V_CCODMUN, V_CMUNIC,
                V_CBAIRRO, V_CCEP, V_CDDD, V_CFONE, V_CPAIS, V_CCONTATO,
                V_CCPFCNPJ, V_CINSCEST, V_CINSCMUN, V_CEMAIL, V_CTIPPES,
                V_COPTSN, V_EMPRESA, V_CDEXFILI, V_CDFORNECED);


      COMMIT;

   END LOOP;
   CLOSE C_FORNECEDOR;

   COMMIT;

END;
/

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