CREATE OR REPLACE FORCE VIEW ENDERECO_PARTICIPANTE_SPED (INSCRICAO, PAIS, ESTADO, MUNICIPIO, ENDERECO, NUMERO,
                                                         COMPLEMENTO, BAIRRO,PARTICIPANTE)
AS
   SELECT INSCRICAO, PAIS, ESTADO, MUNICIPIO, ENDERECO, NUMERO, COMPLEMENTO, BAIRRO,
          LISTAGG(PARTICIPANTE, '_') WITHIN GROUP (ORDER BY PARTICIPANTE) PARTICIPANTE
     FROM (SELECT 'C'||CL.CDCLIENTE AS PARTICIPANTE, CL.NRINSJURCLIE AS INSCRICAO, EC.CDPAIS AS PAIS, EC.SGESTADO AS ESTADO, EC.CDMUNICIPIO AS MUNICIPIO,
                  NVL(EC.DSENDECLIESEP,EC.DSENDECLIE) AS ENDERECO, EC.NRENDECLIESEP AS NUMERO,
                  EC.DSCOMPENDCLI AS COMPLEMENTO, EC.NMBAIRCLIE AS BAIRRO
             FROM CLIENTE CL, ENDECLIE EC
            WHERE CL.CDCLIENTE = EC.CDCLIENTE
              AND CL.IDENDEMISCLI = EC.IDTPENDECLIE
            UNION ALL
           SELECT 'F'||FO.CDFORNECED AS PARTICIPANTE, FO.NRINSJURFORN AS INSCRICAO, EF.CDPAIS AS PAIS, EF.SGESTADO AS ESTADO, EF.CDMUNICIPIO AS MUNICIPIO,
                  NVL(EF.DSENDEFORNSEP,EF.DSENDEFORN) AS ENDERECO, EF.NRENDEFORNSEP AS NUMERO,
                  EF.DSCOMPENDFOR AS COMPLEMENTO, EF.NMBAIRFORN AS BAIRRO
             FROM FORNECEDOR FO, ENDEFORN EF
            WHERE FO.CDFORNECED = EF.CDFORNECED
              AND FO.IDENDEMISFOR = EF.IDTPENDEFORN
            UNION ALL
           SELECT 'L'||FI.CDFILIAL AS PARTICIPANTE, FI.NRINSJURFILI, DECODE(FI.IDENDEMISFIL,'I',I.CDPAIS,EF.CDPAIS) AS CDPAIS,
                  DECODE(FI.IDENDEMISFIL,'I',I.SGESTADO,EF.SGESTADO) AS ESTADO,
                  DECODE(FI.IDENDEMISFIL,'I',I.CDMUNICIPIO,EF.CDMUNICIPIO) AS MUNICIPIO,
                  DECODE(FI.IDENDEMISFIL,'I',NVL(I.DSENDEIESTSEP,I.DSENDEIEST),NVL(EF.DSENDEFILISEP,EF.DSENDEFILI)) AS ENDERECO,
                  DECODE(FI.IDENDEMISFIL,'I',I.NRENDEIESTSEP,EF.NRENDEFILISEP) AS NUMERO,
                  DECODE(FI.IDENDEMISFIL,'I',I.DSCOMPENDIES,EF.DSCOMPENDFIL) AS COMPLEMENTO,
                  DECODE(FI.IDENDEMISFIL,'I',I.NMBAIRIEST,EF.NMBAIRFILI) AS BAIRRO
             FROM FILIAL FI, ENDEFILI EF, INSCRESTAD I
            WHERE EF.CDFILIAL        = FI.CDFILIAL
              AND ((EF.IDTPENDEFILI  = NVL(FI.IDENDEMISFIL,'P')) OR (FI.IDENDEMISFIL = 'I' AND EF.IDTPENDEFILI = 'P'))
              AND FI.CDINSCESTA      = I.CDINSCESTA
              AND FI.CDEMPRESA       = I.CDEMPRESA)
    GROUP BY INSCRICAO, PAIS, ESTADO, MUNICIPIO, ENDERECO, NUMERO, COMPLEMENTO, BAIRRO
/

--VERSAO--
BEGIN ATUVEROBJ('ENDERECO_PARTICIPANTE_SPED','0.00.000'); END;
/