select
    CLI.AGN_IN_CODIGO as id_erp,
    CLI.AGN_ST_NOME as razao_social,
    CLI.AGN_CH_TIPOPESSOAFJ as tipo,
    CLI.AGN_ST_FANTASIA as nome_fantasia,
    CLI.AGN_ST_CGC as cnpj,
    CLI.AGN_ST_INSCRESTADUAL as inscricao_estadual,
    CLI.AGN_ST_SUFRAMA as suframa,
    CLI.AGN_ST_LOGRADOURO || ' ' || AGN_ST_NUMERO as rua,
    CLI.AGN_ST_COMPLEMENTO as complemento,
    CLI.AGN_ST_CEP as cep,
    CLI.AGN_ST_BAIRRO as bairro,
    CLI.AGN_ST_MUNICIPIO as cidade,
    CLI.UF_ST_SIGLA as estado,
    to_char(OBS.OBSAGN_BL_OBSERVACAO) as observacao,
    CLI.AGN_ST_EMAIL as emails,
   (select (listagg (TEL.TEA_ST_TELEFONE, ',') within group (order by TEL.TEA_ST_TELEFONE)) as tester from MGGLO.GLO_TEL_AGENTES TEL where TEL.AGN_IN_CODIGO = CLI.AGN_IN_CODIGO)  as telefone,
    TELL.PAG_ST_CELULAR as celular,
    TELL.PAG_ST_TELCOMERCIAL as tel,
    (select (listagg(CONT.PAG_ST_NOME, ',') within group (order by CONT.PAG_ST_NOME)) as teste from MGGLO.GLO_PESSOA_AGENTES CONT where CONT.AGN_IN_CODIGO = CLI.AGN_IN_CODIGO) as contato,
  --  as nome_excecao_fiscal,
    ID.CAT_IN_REDUZIDO as segmento_id,
    ID.AGN_CH_STATUS as ativo,
    CLI.AGN_DT_ULTIMAATUCAD as ultima_alteracao
from MGGLO.GLO_AGENTES CLI
  --  left join MGGLO.GLO_TEL_AGENTES TEL on TEL.AGN_IN_CODIGO = CLI.AGN_IN_CODIGO
   -- left join MGGLO.GLO_PESSOA_AGENTES CONT on CONT.AGN_IN_CODIGO = CLI.AGN_IN_CODIGO
    left join MGGLO.GLO_AGENTES_ID ID ON ID.AGN_IN_CODIGO = CLI.AGN_IN_CODIGO
    left join MGGLO.GLO_OBSERVACAOAGENTE OBS on OBS.AGN_IN_CODIGO = CLI.AGN_IN_CODIGO and OBS.TOA_ST_CODIGO = '7'
    left join MGGLO.GLO_PESSOA_AGENTES TELL on TELL.AGN_IN_CODIGO = CLI.AGN_IN_CODIGO
where
    ID.AGN_TAU_ST_CODIGO = 'C'
    and ID.CAT_IN_REDUZIDO = '1'
    --and CLI.AGN_IN_CODIGO in ('232', '1098')
	--and CLI.AGN_ST_CGC = '10.827.393/0001-65'
group by
    CLI.AGN_IN_CODIGO,
    CLI.AGN_ST_NOME,
    CLI.AGN_CH_TIPOPESSOAFJ,
    CLI.AGN_ST_FANTASIA,
    CLI.AGN_ST_CGC,
    CLI.AGN_ST_INSCRESTADUAL,
    CLI.AGN_ST_SUFRAMA,
    CLI.AGN_ST_LOGRADOURO || ' ' || AGN_ST_NUMERO,
    CLI.AGN_ST_COMPLEMENTO,
    CLI.AGN_ST_CEP,
    CLI.AGN_ST_BAIRRO,
    CLI.AGN_ST_MUNICIPIO,
    CLI.UF_ST_SIGLA,
    to_char(OBS.OBSAGN_BL_OBSERVACAO),
    CLI.AGN_ST_EMAIL,
    TELL.PAG_ST_CELULAR,
    TELL.PAG_ST_TELCOMERCIAL,
    ID.CAT_IN_REDUZIDO,
    ID.AGN_CH_STATUS,
    CLI.AGN_DT_ULTIMAATUCAD