create or replace PROCEDURE PROT_EXPORTA_CLIENTE
IS
V_CDFILIAL VARCHAR2(4);
V_CDEXFILI VARCHAR2(20);
V_EMPRESA  VARCHAR2(2);
V_CPASS    VARCHAR2(5);
V_NOPC     VARCHAR2(1);
V_TIPO     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_COPTSIM  VARCHAR2(1);
V_COPTSN   VARCHAR2(1);
V_CEMPAUX  VARCHAR2(2);
V_CFILAUX  VARCHAR2(2);
V_CBLOQ    VARCHAR2(1);
V_CDCLIENTE VARCHAR2(21);
V_ERRO VARCHAR2(500);

CURSOR C_CLIENTE IS
SELECT SUBSTR(LTRIM(C.NMRAZSOCCLIE), 1, 39) CNOME, 
       C.IDTPIJURCLIE CPFPJ, 
       SUBSTR(LTRIM(C.NMFANTCLIE), 1, 9) CNOMEFAN,
       SUBSTR(LTRIM(E.DSENDECLIE), 1, 40) CEND,
       LTRIM(E.SGESTADO) CUF, 
       M.CDMUNICIBGE CCODMUN, 
       SUBSTR(LTRIM(M.NMMUNICIPIO), 1, 25) CMUNIC, 
       SUBSTR(LTRIM(E.NMBAIRCLIE), 1, 30) CBAIRRO, 
       NVL(SUBSTR(REGEXP_REPLACE(E.NRCEPCLIE,'\D'), 1, 8),99999999) CCEP,
       'XXX' CDDD,
       SUBSTR(NVL(E.NRTELECLIE,'999999999'), 1, 15) CFONE, 
       SUBSTR(P.CDPAISEXPFIS, 1, 3) CPAIS, 
       SUBSTR(LTRIM(E.NMCONTCLIE), 1, 15) CCONTATO,
       CASE WHEN C.IDTPIJURCLIE = 'F' THEN SUBSTR(LPAD(REGEXP_REPLACE(C.NRINSJURCLIE,'\D'),20,0),10)
            WHEN C.IDTPIJURCLIE = 'J' THEN SUBSTR(LPAD(REGEXP_REPLACE(C.NRINSJURCLIE,'\D'),20,0),7)
          ELSE SUBSTR(LPAD(REGEXP_REPLACE(C.NRINSJURCLIE,'\D'),20,0),7) END CCPFCNPJ,       
       SUBSTR(LTRIM(E.DSEMAILCLIE), 1, 30)CEMAIL, 
       CASE WHEN IDTPIJURCLIE = 'J' THEN 'CI' ELSE 'PF' END AS CTIPPES,
       CASE WHEN IDCLIEATIV = 'N' THEN '1' ELSE '2' END AS CBLOQ,
       CASE WHEN C.DTEXPXMLPROT IS NULL THEN '3'
            WHEN ((C.DTEXPXMLPROT < C.DTULTATUCLIE) OR (C.DTEXPXMLPROT < E.DTULTATUECLI)) THEN '4'
          END AS NOPC, 
       C.CDCLIENTE,
        '00' AS CDFIL_PROTHEUS,
        FIL.CDEMPRE_PROTHEUS
  FROM CLIENTE C
 INNER JOIN ENDECLIE E
    ON (C.CDCLIENTE = E.CDCLIENTE)
   AND (E.IDTPENDECLIE = 'P')
 INNER JOIN MUNICIPIO M
    ON (E.CDPAIS = M.CDPAIS)
   AND (E.SGESTADO = M.SGESTADO)
   AND (E.CDMUNICIPIO = M.CDMUNICIPIO)
 INNER JOIN PAIS P
    ON (P.CDPAIS = E.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 ((C.DTEXPXMLPROT IS NULL) OR (C.DTEXPXMLPROT < C.DTULTATUCLIE) OR (C.DTEXPXMLPROT < E.DTULTATUECLI));

BEGIN
   V_CPASS   := 'SPORT';
   V_CCODIGO := ' ';
   V_CLOJA   := '01';
   V_CTIPO    := 'L';
   V_COPTSIM := '2';
   V_COPTSN  := '2';
   
   DELETE FROM USR_PROT_CLIENTE;
   COMMIT;

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

   DELETE USR_PROT_INTEGRACAO
    WHERE CDLOG = 'EnviarCliente'
      AND DTINCLUSAO BETWEEN (SYSDATE - 30) AND (SYSDATE - 15);
   COMMIT;

   OPEN C_CLIENTE;
   LOOP
      FETCH C_CLIENTE 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_CEMAIL, V_CTIPPES, 
                           V_CBLOQ, V_NOPC, V_CDCLIENTE, V_CDEXFILI, V_CEMPAUX
                           ;
      EXIT WHEN C_CLIENTE%NOTFOUND;

         INSERT INTO USR_PROT_CLIENTE(CPASS, NOPC, CCODIGO, CLOJA, CNOME, CPFPJ, CNOMEFAN, CEND, CUF, CCODMUN, CMUNIC, CBAIRRO, CCEP,
                                      CDDD, CFONE, CPAIS, CCONTATO, CCPFCNPJ, CEMAIL, CTIPPES, CTIPO, COPTSIM, COPTSN, CEMPAUX, CFILAUX,
                                      CBLOQ, CDCLIENTE)
         VALUES(V_CPASS, V_NOPC, V_CCODIGO, V_CLOJA, 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_CEMAIL, V_CTIPPES, V_CTIPO, V_COPTSIM, V_COPTSN, V_CEMPAUX, V_CDEXFILI,
                V_CBLOQ, V_CDCLIENTE);
   END LOOP;
   CLOSE C_CLIENTE;

   COMMIT;

END;
/

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