SELECT
   C.CDCLIENTE,
   'C' AS IDTIPOCLIFORN,
   0 AS CODCLI,
   USR_DSAPELIDO AS APELIDO,
   '1.02.01.01.1.01.390' AS ATIVO,
   NMBAIRCLIE AS BAIRRO,
   'N' AS BLOQUEADO,
   NRCEPCLIE AS CEP,
   '0.000' AS CFOP,
   DECODE(IDTPIJURCLIE, 'O', 'NI'||NRINSJURCLIE, USR_PICTPESS(IDTPIJURCLIE, NRINSJURCLIE)) AS CGC,
   NMMUNICIPIO AS CIDADE,
   0 AS CLI_CODGC,
   TRUNC(SYSDATE) AS CLI_DTCADASTRO,
   DECODE(IDTPIJURCLIE, 'O', 'NI', IDTPIJURCLIE) AS CLI_FISJUR,
   '0' AS CLI_PARAMETRO,
   0 AS CODCLI_PAI,
   E.SGESTADO AS CODEST,
   0 AS CODPA,
   0 AS CODTRA,
   52 AS CODUSU,
   0 AS CODVEN,
   '0' AS NUMBAN,
   CDBANFLUX AS CLI_BFCR,
   '0' AS CLI_SENHA,
   (SELECT MIN(NMINTECLIE) FROM INTECLIE I WHERE I.CDCLIENTE = C.CDCLIENTE) AS CONT1,
   (SELECT MAX(NMINTECLIE) FROM INTECLIE I WHERE I.CDCLIENTE = C.CDCLIENTE) AS CONT2,
   (SELECT MAX(REPLACE(SUBSTR(R.CDBANCO,1,3)||'/'||SUBSTR(R.CDAGENCIA,1,4)||'/'||SUBSTR(R.CDCONTCORR,1,6),'//',''))
    FROM BANCOPREF B, CONTCORR R
    WHERE C.CDCLIENTE = B.CDCLIENTE
      AND B.CDREDUCONT = R.CDREDUCONT) AS CONTABANCO,
   NMRAZSOCCLIE DESCLI,
   DSENDECLIE AS ENDERECO,
   NRFAXCLIE AS FAX,
   NRTELECLIE AS FONE,
   'N' AS GERALOTE,
   '' AS CLI_INSMUNIC,
   NRINSESTCLIE AS INS,
   0 AS LIMITECRED,
   '2.01.02.01.1.01.030' AS PASSIVO,
   0 AS RES_CODFIS,
   1 AS RES_CODPLA,
   0 AS RES_CODUNI,
   0 AS CLI_DEPENDENTES,
   1 AS CLI_CODEMP,
   DSEMAILCLIE AS EMAIL,
   0 CLI_CODCPG,
   USR_CODCPG AS CLI_CODCPGCR,
   0 AS CLI_RESTORNO,
   CDTPCTRECEB AS CLI_CODFOR,
   'NORMAL' AS CLI_CATEGORIA,
   '*' AS CLI_POSICAOCOBRANCA,
   0 AS CLI_JUROMORA,
   'N' AS CLI_CALCULACF,
   0 AS CLI_INST01,
   0 AS CLI_INST02
FROM CLIENTE C, ENDECLIE E, MUNICIPIO M, FORMPGTO P
WHERE C.CDCLIENTE = E.CDCLIENTE
  AND E.IDTPENDECLIE = 'P'
  AND E.CDPAIS = M.CDPAIS
  AND E.SGESTADO = M.SGESTADO
  AND E.CDMUNICIPIO = M.CDMUNICIPIO
  AND USR_DSAPELIDO IS NOT NULL
  AND USR_DTEXPOCLIE IS NULL
  AND C.CDFORMPGTO = P.CDFORMPGTO(+)
UNION ALL
SELECT
   F.CDFORNECED,
   'F' AS IDTIPOCLIFORN,
   0 AS CODCLI,
   USR_DSAPELIDO AS APELIDO,
   '1.02.01.01.1.01.390' AS ATIVO,
   NMBAIRFORN AS BAIRRO,
   'N' AS BLOQUEADO,
   NRCEPFORN AS CEP,
   '0.000' AS CFOP,
   DECODE(IDTPIJURFORN, 'O', 'NI'||NRINSJURFORN, USR_PICTPESS(IDTPIJURFORN, NRINSJURFORN)) AS CGC,
   NMMUNICIPIO AS CIDADE,
   0 AS CLI_CODGC,
   TRUNC(SYSDATE) AS CLI_DTCADASTRO,
   DECODE(IDTPIJURFORN, 'O', 'NI', IDTPIJURFORN) AS CLI_FISJUR,
   '0' AS CLI_PARAMETRO,
   0 AS CODCLI_PAI,
   E.SGESTADO AS CODEST,
   0 AS CODPA,
   0 AS CODTRA,
   52 AS CODUSU,
   0 AS CODVEN,
   CDBANFLUX AS NUMBAN,
   '0' AS CLI_BFCR,
   CDSENHAFOR AS CLI_SENHA,
   (SELECT MIN(NMINTERFORN) FROM INTEFORN I WHERE I.CDFORNECED = F.CDFORNECED) AS CONT1,
   (SELECT MAX(NMINTERFORN) FROM INTEFORN I WHERE I.CDFORNECED = F.CDFORNECED) AS CONT2,
   (SELECT MAX(REPLACE(SUBSTR(R.CDBANCFORN,1,3)||'/'||SUBSTR(R.CDAGENFORN,1,4)||'/'||SUBSTR(R.CDCCORFORN,1,6),'//',''))
    FROM CCORFORN R
    WHERE R.CDFORNECED = F.CDFORNECED) AS CONTABANCO,
   NMRAZSOCFORN DESCLI,
   DSENDEFORN AS ENDERECO,
   NRFAXFORN AS FAX,
   NRTELEFORN AS FONE,
   'N' AS GERALOTE,
   NRINSMUNFORN AS CLI_INSMUNIC,
   NRINSESTFORN AS INS,
   0 AS LIMITECRED,
   '2.01.02.01.1.01.030' AS PASSIVO,
   0 AS RES_CODFIS,
   1 AS RES_CODPLA,
   0 AS RES_CODUNI,
   0 AS CLI_DEPENDENTES,
   1 AS CLI_CODEMP,
   DSEMAILFORN AS EMAIL,
   USR_CODCPG AS CLI_CODCPG,
   0 AS CLI_CODCPGCR,
   F.VRPEDESCFORN AS CLI_RESTORNO,
   CDTIPCOB AS CLI_CODFOR,
   'NORMAL' AS CLI_CATEGORIA,
   '*' AS CLI_POSICAOCOBRANCA,
   0 AS CLI_JUROMORA,
   'N' AS CLI_CALCULACF,
   0 AS CLI_INST01,
   0 AS CLI_INST02
FROM FORNECEDOR F, ENDEFORN E, MUNICIPIO M, FORMPGTO P
WHERE F.CDFORNECED = E.CDFORNECED
  AND E.IDTPENDEFORN = 'P'
  AND E.CDPAIS = M.CDPAIS
  AND E.SGESTADO = M.SGESTADO
  AND E.CDMUNICIPIO = M.CDMUNICIPIO
  AND USR_DSAPELIDO IS NOT NULL
  AND USR_DTEXPOFORN IS NULL
  AND F.CDCLIENTE IS NULL
  AND F.CDFORMPGTO = P.CDFORMPGTO(+)
ORDER BY 1