Filtros: ' ||'[Periodo de cadastro cliente: '||case when $P{P_DTCAD}='S' then 'Qualquer data' else to_char(cast($P{P_DTCADDE} as date),'dd/mm/yyyy')||' a '||to_char(cast($P{P_DTCADATE} as date),'dd/mm/yyyy') end||']' ||'[Periodo de compra: '||case when $P{P_DTCOMP}='S' then 'Qualquer data' else to_char(cast($P{P_DTCOMPDE} as date),'dd/mm/yyyy')||' a '||to_char(cast($P{P_DTCOMPATE} as date),'dd/mm/yyyy') end||']' ||case when $P{P_RCA}>0 then '[RCA/VENDEDOR: '||(select pardes from par where parcod=$P{P_RCA})||']' else '' end ||case when $P{P_FORN}>0 then '[FORN/FORNECEDOR: '||(select pardes from par where parcod=$P{P_FORN})||']' else '' end ||case when $P{P_PRO}>0 then '[PRO/PRODUTO: '||(select prodes from pro where procod=$P{P_PRO})||']' else '' end ||case when $P{P_RCA}>0 then '[RCA/VENDEDOR: '||(select pardes from par where parcod=$P{P_RCA})||']' else '' end ||case when $P{P_LOC}>0 then '[Localidade: '||(select locdes from loc where loccod=$P{P_LOC})||']' else '' end ||case when $P{P_UF}>'0' then '[UF: '||coalesce((select distinct munufsigla from mun where munufsigla=$P{P_UF}), 'TODOS')||']' else '' end ||case when $P{P_MUN}>0 then '[Município: '||(select mundes from mun where muncod=$P{P_MUN})||']' else '' end ||case when $P{P_ROTA}>0 then '[Rota: '||(select rotades from rota where rotacod=$P{P_ROTA})||']' else '' end ||case when $P{P_RA}>0 then '[Ramo de Atividade: '||(select rades from ra where racod=$P{P_RA})||']' else '' end ||case when $P{P_AV}>0 then '[Área de Venda: '||(select av_descricao from area_venda where av_codigo=$P{P_AV})||']' else '' end ||case when $P{P_GRUPOCLI}>0 then ', [Grupo.Cli: '||coalesce((select cg_descricao from cliente_grupo where cg_codigo=$P{P_GRUPOCLI}),'TODOS')||']' else '' end ||case when $P{P_CLIBLOQ}>0 then case when $P{P_CLIBLOQ}=1 then ' Ativos' when $P{P_CLIBLOQ}=2 then ' Inativos' else '' end else '' end as filtro, /***************************************** */ rcapar.parcod as gruporcacod, rcapar.pardes as gruporcades, rcapar.parfan as gruporcafan, case when $P{P_AGRUP}=1 then 'Geral' when $P{P_AGRUP}=2 then cast(munufsigla||'-'||cliloc.locdes as varchar) when $P{P_AGRUP}=3 then cast(munufsigla||'-'||mundes as varchar) when $P{P_AGRUP}=4 then case when (cli_ate_seg='S' and cli_ate_ter='S' and cli_ate_qua='S' and cli_ate_qui='S' and cli_ate_sex='S') then '0-DIARIO' when cli_ate_seg='S' then '2-SEGUNDA' when cli_ate_ter='S' then '3-TERCA' when cli_ate_qua='S' then '4-QUARTA' when cli_ate_qui='S' then '5-QUINTA' when cli_ate_sex='S' then '6-SEXTA' when cli_ate_sab='S' then '7-SABADO' else 'NÃO DEFINIDO' end else 'Geral' end as subgrupodes, clipar.parcod as clicod, clipar.pardes||'-'||clipar.parcnpjcpf as clides, clipar.parfan as clifan, clipar.parrua as cliend, clipar.parbai as clibai, clipar.parcep as clicep, clipar.paremail as cliemail, cliloc.locdes as clilocdes, climun.mundes as climundes, climun.munufsigla as cliuf, case when clibloq='S' then 'Sim' else 'Não' end as clibloq, case when clipar.pardddfone<'0' or clipar.pardddfone is null then clipar.parfone else '('||clipar.pardddfone||') '||clipar.parfone end as clifone, clicontato, clilim, (select tctdoc from clitc join tc on tccod=clitctccod where clitccliparcod=clipar.parcod order by clitcseq desc limit 1)||' - '||cliplpg.plpgdes as clipgto, clipar.paremail from cli join par clipar on cliparcod = clipar.parcod join par rcapar on rcapar.parcod =clircaparcod join loc cliloc on cliloccod = cliloc.loccod join rota clirota on locrotacod = clirota.rotacod join mun climun on clipar.parmuncod = climun.muncod join plpg cliplpg on cli_plano_pagamento = cliplpg.plpgcod JOIN cliplpg cliplano ON cliplano.cliplpgcliparcod = cliparcod left join ( select fat_nf_dtemi, fat_forn_cod, fat_par_cod, fat_pro_cod from sd.vw_fat) compra on clipar.parcod = compra.fat_par_cod where 1=1 and case when $P{P_DTCAD}='S' then 1=1 else DATE(cli_dt_cad) between cast($P{P_DTCADDE} as date) and cast($P{P_DTCADATE} as date) end and case when $P{P_DTCOMP}='S' then 1=1 else DATE(compra.fat_nf_dtemi) between DATE($P{P_DTCOMPDE}) and DATE($P{P_DTCOMPATE}) end and case when $P{P_FORN}=0 then 1=1 else compra.fat_forn_cod=$P{P_FORN} end and case when $P{P_PRO}=0 then 1=1 else compra.fat_pro_cod=$P{P_PRO} end and case when $P{P_RCA}=0 then 1=1 else rcapar.parcod=$P{P_RCA} end and case when $P{P_MUN}=0 then 1=1 else climun.muncod=$P{P_MUN} end and case when $P{P_UF}='0' then 1=1 else climun.munufsigla=$P{P_UF} end and case when $P{P_ROTA}=0 then 1=1 else clirota.rotacod=$P{P_ROTA} end and case when $P{P_LOC}=0 then 1=1 else cliloc.loccod=$P{P_LOC} end and case when $P{P_RA}=0 then 1=1 else cliracod=$P{P_RA} end and case when $P{P_AV}=0 then 1=1 else cli_av_codigo=$P{P_AV} end and case when $P{P_GRUPOCLI}=0 then 1=1 else cli_cg_codigo=$P{P_GRUPOCLI} end group by clipar.parcod,rcapar.parcod, clipar.pardes,climun.munufsigla,climun.munufsigla,cliloc.locdes,climun.mundes,cli.cli_ate_seg,cli.cli_ate_ter,cli.cli_ate_qua ,cli.cli_ate_qui,cli.cli_ate_sex,cli.cli_ate_sab,cli.clibloq,clicontato,cli.clilim,cliplpg.plpgdes union select /***************************************** CABECALHO/RODAPE */ 'Lista de Clientes' as relatorio, to_char(now(),'dd/mm/yyyy HH24:MI')||' ('||coalesce((select usulogin from usu where usufunparcod=$P{P_USU}),'...')||')' as dtimpr, 'th_lista_cliente v25.04.07 - www.brsistemas.com.br' as versao, 'Filtros: ' ||'[Periodo de cadastro cliente: '||case when $P{P_DTCAD}='S' then 'Qualquer data' else to_char(cast($P{P_DTCADDE} as date),'dd/mm/yyyy')||' a '||to_char(cast($P{P_DTCADATE} as date),'dd/mm/yyyy') end||']' ||'[Periodo de compra: '||case when $P{P_DTCOMP}='S' then 'Qualquer data' else to_char(cast($P{P_DTCOMPDE} as date),'dd/mm/yyyy')||' a '||to_char(cast($P{P_DTCOMPATE} as date),'dd/mm/yyyy') end||']' ||case when $P{P_RCA}>0 then '[RCA/VENDEDOR: '||(select pardes from par where parcod=$P{P_RCA})||']' else '' end ||case when $P{P_FORN}>0 then '[FORN/FORNECEDOR: '||(select pardes from par where parcod=$P{P_FORN})||']' else '' end ||case when $P{P_PRO}>0 then '[PRO/PRODUTO: '||(select prodes from pro where procod=$P{P_PRO})||']' else '' end ||case when $P{P_RCA}>0 then '[RCA/VENDEDOR: '||(select pardes from par where parcod=$P{P_RCA})||']' else '' end ||case when $P{P_LOC}>0 then '[Localidade: '||(select locdes from loc where loccod=$P{P_LOC})||']' else '' end ||case when $P{P_UF}>'0' then '[UF: '||coalesce((select distinct munufsigla from mun where munufsigla=$P{P_UF}), 'TODOS')||']' else '' end ||case when $P{P_MUN}>0 then '[Município: '||(select mundes from mun where muncod=$P{P_MUN})||']' else '' end ||case when $P{P_ROTA}>0 then '[Rota: '||(select rotades from rota where rotacod=$P{P_ROTA})||']' else '' end ||case when $P{P_RA}>0 then '[Ramo de Atividade: '||(select rades from ra where racod=$P{P_RA})||']' else '' end ||case when $P{P_AV}>0 then '[Área de Venda: '||(select av_descricao from area_venda where av_codigo=$P{P_AV})||']' else '' end ||case when $P{P_GRUPOCLI}>0 then ', [Grupo.Cli: '||coalesce((select cg_descricao from cliente_grupo where cg_codigo=$P{P_GRUPOCLI}),'TODOS')||']' else '' end ||case when $P{P_CLIBLOQ}>0 then case when $P{P_CLIBLOQ}=1 then ' Ativos' when $P{P_CLIBLOQ}=2 then ' Inativos' else '' end else '' end as filtro, /***************************************** */ rcapar.parcod as gruporcacod, rcapar.pardes as gruporcades, rcapar.parfan as gruporcafan, case when $P{P_AGRUP}=1 then 'Geral' when $P{P_AGRUP}=2 then cast(munufsigla||'-'||cliloc.locdes as varchar) when $P{P_AGRUP}=3 then cast(munufsigla||'-'||mundes as varchar) when $P{P_AGRUP}=4 then case when (cli_ate_seg='S' and cli_ate_ter='S' and cli_ate_qua='S' and cli_ate_qui='S' and cli_ate_sex='S') then '0-DIARIO' when cli_ate_seg='S' then '2-SEGUNDA' when cli_ate_ter='S' then '3-TERCA' when cli_ate_qua='S' then '4-QUARTA' when cli_ate_qui='S' then '5-QUINTA' when cli_ate_sex='S' then '6-SEXTA' when cli_ate_sab='S' then '7-SABADO' else 'NÃO DEFINIDO' end else 'Geral' end as subgrupodes, clipar.parcod as clicod, clipar.pardes||'-'||clipar.parcnpjcpf as clides, clipar.parfan as clifan, clipar.parrua as cliend, clipar.parbai as clibai, clipar.parcep as clicep, clipar.paremail as cliemail, cliloc.locdes as clilocdes, climun.mundes as climundes, climun.munufsigla as cliuf, case when clibloq='S' then 'Sim' else 'Não' end as clibloq, case when clipar.pardddfone<'0' or clipar.pardddfone is null then clipar.parfone else '('||clipar.pardddfone||') '||clipar.parfone end as clifone, clicontato, clilim, (select tctdoc from clitc join tc on tccod=clitctccod where clitccliparcod=clipar.parcod order by clitcseq desc limit 1)||' - '||cliplpg.plpgdes as clipgto, clipar.paremail from cli join par clipar on cliparcod = clipar.parcod join loc cliloc on cliloccod = cliloc.loccod join rota clirota on locrotacod = clirota.rotacod join mun climun on parmuncod = climun.muncod join plpg cliplpg on cli_plano_pagamento = cliplpg.plpgcod JOIN cliplpg cliplano ON cliplano.cliplpgcliparcod = cliparcod left join clirca on clirca.clircacliparcod = clipar.parcod LEFT JOIN par rcapar ON rcapar.parcod = clirca.clircarcaparcod left join ( select distinct fat_nf_dtemi, fat_forn_cod, fat_par_cod, fat_pro_cod from sd.vw_fat) compra on clipar.parcod = compra.fat_par_cod WHERE 1=1 and case when $P{P_DTCAD}='S' then 1=1 else DATE(cli_dt_cad) between cast($P{P_DTCADDE} as date) and cast($P{P_DTCADATE} as date) end and case when $P{P_DTCOMP}='S' then 1=1 else DATE(compra.fat_nf_dtemi) between DATE($P{P_DTCOMPDE}) and DATE($P{P_DTCOMPATE}) end and case when $P{P_FORN}=0 then 1=1 else compra.fat_forn_cod=$P{P_FORN} end and case when $P{P_PRO}=0 then 1=1 else compra.fat_pro_cod=$P{P_PRO} end and case when $P{P_RCA}=0 then 1=1 else rcapar.parcod=$P{P_RCA} end and case when $P{P_MUN}=0 then 1=1 else climun.muncod=$P{P_MUN} end and case when $P{P_UF}='0' then 1=1 else climun.munufsigla=$P{P_UF} end and case when $P{P_ROTA}=0 then 1=1 else clirota.rotacod=$P{P_ROTA} end and case when $P{P_LOC}=0 then 1=1 else cliloc.loccod=$P{P_LOC} end and case when $P{P_RA}=0 then 1=1 else cliracod=$P{P_RA} end and case when $P{P_AV}=0 then 1=1 else cli_av_codigo=$P{P_AV} end and case when $P{P_GRUPOCLI}=0 then 1=1 else cli_cg_codigo=$P{P_GRUPOCLI} end group by clipar.parcod, clipar.pardes,rcapar.parcod,climun.munufsigla,climun.munufsigla,cliloc.locdes,climun.mundes,cli.cli_ate_seg,cli.cli_ate_ter,cli.cli_ate_qua ,cli.cli_ate_qui,cli.cli_ate_sex,cli.cli_ate_sab,cli.clibloq,clicontato,cli.clilim,cliplpg.plpgdes order by gruporcacod]]>