with params as (
	select
		90 as dias
	from
		dual
), clientes as (
	select
		to_char(pc.codcli) as codcli,
		to_char(pc.codpraca) as codpraca,
		to_char(pp.numregiao) as numregiao,
		pp.tipopraca,
		case ferramentas.verificar_fj(pc.codcli) when 'PESSOA FISICA' then 'F' else 'J' end as tipofj,
		nvl(pc.orgaopub, 'N') as orgaopub,
		pc.classemc,
		pc.classevenda,
		pc.classecomercial,
		nvl(pc.orgaopubmunicipal, 'N') as orgaopubmunicipal,
		pc.codfilialnf,
		'1' as codfilial,
		nvl(pc.orgaopubfederal, 'N') as orgaopubfederal,
		pc.ieent,
		nvl(pc.consumidorfinal, 'N') as consumidorfinal,
		pp.cepinicial,
		pp.cepfinal,
		nvl(pc.utilizacalculostmt, 'N') as utilizacalculostmt,
		coalesce(pc.calculast, pa.calculast, 'N') as calculast,
		nvl(pc.clientefontest, 'N') as clientefontest,
		nvl(pc.usaivafontediferenciado, 'N') as usaivafontediferenciado,
		nvl(pc.riolog, 'N') as riolog,
		to_char(pc.dtnasc) as dtnasc,
		to_char(pc.ivafonte) as ivafonte,
		pc.isentoipi,
		pc.tipofj as tipocli,
		pc.estent,
		pc.cepent,
		nvl(pc.cozinhaindustrial, 'N') as cozinhaindustrial,
		nvl(pc.simplesnacional, 'N') as simplesnacional,
		pc.sulframa,
		to_char(pc.codcidade) as codcidade,
		to_char(pc.dtvencsuframa) as dtvencsuframa,
		pc.microempresa,
		to_char(pc.codatv1) as codatv1,
		to_char(pa.percdesc) as percdesc,
		pc.tipoempresa,
		pc.usadescontoicms,
		to_char(pc.codusur1) as codusur1,
        count(*) over () as total_rows,
        row_number() over (order by dbms_random.value) as rn
	from
		pcclient pc
	left join pcpraca pp on
		pp.codpraca = pc.codpraca
	left join pcativi pa on
		pa.codativ = pc.codatv1
	where
		exists (select 1 from pcpedc ped where pc.codcli = ped.codcli and ped.data >= sysdate - (select dias from params))
)
select * from (
	select
		'CODCLI' as codcli,
		'CODPRACA' as codpraca,
		'NUMREGIAO' as numregiao,
		'TIPOPRACA' as tipopraca,
		'TIPOFJ' as tipofj,
		'ORGAOPUB' as orgaopub,
		'CLASSEMC' as classemc,
		'CLASSEVENDA' as classevenda,
		'CLASSECOMERCIAL' as classecomercial,
		'ORGAOPUBMUNICIPAL' as orgaopubmunicipal,
		'CODFILIALNF' as codfilialnf,
		'CODFILIAL' as codfilial,
		'ORGAOPUBFEDERAL' as orgaopubfederal,
		'IEENT' as ieent,
		'CONSUMIDORFINAL' as consumidorfinal,
		'CEPINICIAL' as cepinicial,
		'CEPFINAL' as cepfinal,
		'UTILIZACALCULOSTMT' as utilizacalculostmt,
		'CALCULAST' as calculast,
		'CLIENTEFONTEST' as clientefontest,
		'USAIVAFONTEDIFERENCIADO' as usaivafontediferenciado,
		'RIOLOG' as riolog,
		'DTNASC' as dtnasc,
		'IVAFONTE' as ivafonte,
		'ISENTOIPI' as isentoipi,
		'TIPOCLI' as tipocli,
		'ESTENT' as estent,
		'CEPENT' as cepent,
		'COZINHAINDUSTRIAL' as cozinhaindustrial,
		'SIMPLESNACIONAL' as simplesnacional,
		'SULFRAMA' as sulframa,
		'CODCIDADE' as codcidade,
		'DTVENCSUFRAMA' as dtvencsuframa,
		'MICROEMPRESA' as microempresa,
		'CODATV1' as codatv1,
		'PERCDESC' as percdesc,
		'TIPOEMPRESA' as tipoempresa,
		'USADESCONTOICMS' as usadescontoicms,
		'CODUSUR1' as codusur1,
		'FILIAL_PEDIDOS' as filial_pedidos,
		'QTD_PEDIDOS' as qtd_pedidos,
		'TOTAL_PEDIDOS' as total_pedidos,
		to_clob('ITEMS_PEDIDOS') as items_pedidos,
		'PLANOS_DE_PAGAMENTO' as planos_de_pagamento,
		0 as ordem
	from dual
	union all
	select
		ca.codcli,
		ca.codpraca,
		ca.numregiao,
		ca.tipopraca,
		ca.tipofj,
		ca.orgaopub,
		ca.classemc,
		ca.classevenda,
		ca.classecomercial,
		ca.orgaopubmunicipal,
		ca.codfilialnf,
		ca.codfilial,
		ca.orgaopubfederal,
		ca.ieent,
		ca.consumidorfinal,
		ca.cepinicial,
		ca.cepfinal,
		ca.utilizacalculostmt,
		ca.calculast,
		ca.clientefontest,
		ca.usaivafontediferenciado,
		ca.riolog,
		ca.dtnasc,
		ca.ivafonte,
		ca.isentoipi,
		ca.tipocli,
		ca.estent,
		ca.cepent,
		ca.cozinhaindustrial,
		ca.simplesnacional,
		ca.sulframa,
		ca.codcidade,
		ca.dtvencsuframa,
		ca.microempresa,
		ca.codatv1,
		ca.percdesc,
		ca.tipoempresa,
		ca.usadescontoicms,
		ca.codusur1,
		(
			select max(CGC)
			  from pcpedc ped
			  inner join PCFILIAL f on ped.CODFILIAL = f.CODIGO
			 where ca.codcli = ped.codcli
			   and ped.data >= sysdate - (select dias from params)
		) as filial_pedidos,
		(
			select to_char(count(1))
			  from pcpedc ped
			 where ca.codcli = ped.codcli
			   and ped.data >= sysdate - (select dias from params)
		) as qtd_pedidos,
		(
			select to_char(sum(ped.vltotal))
			  from pcpedc ped
			 where ca.codcli = ped.codcli
			   and ped.data >= sysdate - (select dias from params)
		) as total_pedidos,
		(
	        select xmlelement("root",xmlagg(xmlelement("codprod", codprod))).getClobVal() as items_pedidos
			from (
			    select distinct pei.codprod, pei.codcli
			    from pcpedi pei
			    where data >= sysdate - (select dias from params)
			) item
	    	where ca.codcli = item.codcli
		) as items_pedidos,
		(select to_char(count(1)) from pcplpag ) as planos_de_pagamento,
		1 as ordem
	from
		clientes ca
)
order by
	ordem