select /*+ RULE */  r.datacredito as datacredito,
r.datavenda as datacompra,
cast(ceil (sum(1 / decode (r.totalparcela , 0, 1, cast(r.totalparcela as real)))) as int) as qtde,
max(r.datacredito) as maiordatacredito,
sum(r.valorbruto) as valorbruto,
sum(r.valorliquido) as valorliquido,
sum(cast(r.valorbruto as int) - cast(r.valorliquido as int)) as valorcomissao,
0 as valorsaque
from (select cv.datacv as datavenda, cv.datacredito as datacredito, decode (cv.tipotransacao , 'F', 'A', 'D') as tipoproduto, cv.captura as captura, cv.codlojasitef as codlojasitef, cv.datatrnsitef as datatrnsitef, 0 as totalparcela, nvl (cv.tipocaptura, '8') as tipocaptura, cast(cv.valorbruto as int) as valorbruto, cast(cv.valorliquido as int) as valorliquido from extratoredecarddebrv rv, extratoredecarddebcv cv where rv.numpdv = cv.numpdv and rv.datarv = cv.datarv and rv.numrv = cv.numrv
union all
select cv.datacv as datavenda, rv.datacredito as datacredito, 'C' as tipoproduto, cv.captura as captura, cv.codlojasitef as codlojasitef, cv.datatrnsitef as datatrnsitef, 0 as totalparcela, nvl (cv.tipocaptura, '8') as tipocaptura, cast(cv.valorcv as int) as valorbruto, cast(cv.valorcv as int) - cast(cv.valordesconto as int) as valorliquido from extratoredecardcredrv rv, extratoredecardcredcv cv where rv.numpdv = cv.numpdv and rv.datarv = cv.datarv and rv.numrv = cv.numrv and not exists (select 1 from extratoredecardfindesagparc where cv.numpdv = numpdv and cv.numrv = numrv and cv.datarv = datarv and cv.numcv = numcvorig and cv.datacv = datacvorig )
union all
select cv.datacv as datavenda, rv.datacredito as datacredito, 'C' as tipoproduto, cv.captura as captura, cv.codlojasitef as codlojasitef, cv.datatrnsitef as datatrnsitef, 0 as totalparcela, nvl (cv.tipocaptura, '8') as tipocaptura, cast(cv.valorcv as int) as valorbruto, cast(cast(cv.valorcv as int) - cast(cv.valordesconto as int) as int) - cast(d.valorajuste as int) as valorliquido from extratoredecardcredrv rv, extratoredecardfindesagparc d, extratoredecardcredcv cv where rv.numpdv = cv.numpdv and rv.datarv = cv.datarv and rv.numrv = cv.numrv and cv.numpdv = d.numpdv and cv.numrv = d.numrv and cv.datarv = d.datarv and cv.numcv = d.numcvorig and cv.datacv = d.datacvorig and d.tipo_lancam = '1'
union all
select cv.datacv as datavenda, rv.datacredito as datacredito, 'C' as tipoproduto, cv.captura as captura, cv.codlojasitef as codlojasitef, cv.datatrnsitef as datatrnsitef, 0 as totalparcela, nvl (cv.tipocaptura, '8') as tipocaptura, cast(cv.valorcv as int) as valorbruto, cast(cast(cv.valorcv as int) - cast(cv.valordesconto as int) as int) - cast(d.valorajuste as int) as valorliquido from extratoredecardcredrv rv, extratoredecardfindesagparc d, extratoredecardcredcv cv where rv.numpdv = cv.numpdv and rv.datarv = cv.datarv and rv.numrv = cv.numrv and cv.numpdv = d.numpdv and cv.numrv = d.numrv and cv.datarv = d.datarv and cv.numcv = d.numcvorig and cv.datacv = d.datacvorig and d.tipo_lancam = '0' and not exists (select 1 from extratoredecardfindesagparc where cv.numpdv = numpdv and cv.numrv = numrv and cv.datarv = datarv and cv.numcv = numcvorig and cv.datacv = datacvorig and tipo_lancam = '1')
union all
select cv.datacv as datavenda, p.datacredito1 as datacredito, 'C' as tipoproduto, cv.captura as captura, cv.codlojasitef as codlojasitef, cv.datatrnsitef as datatrnsitef, cast(cv.numparcelas as int) as totalparcela, cv.tipocaptura, decode (p.numparcela1 , '01', cast(cv.valor_bruto_primeira_parcela as int), cast(cv.valor_bruto_demais_parcelas as int)) as valorbruto, decode (p.numparcela1 , '01', cast(cv.valor_liquido_primeira_parcela as int), cast(cv.valor_liquido_demais_parcelas as int)) as valorliquido from extratoredecardcredrvparcsj rv, extratoredecardcredparcsj p, extratoredecardcredcvparcsj cv where cv.numpdv = p.numpdv and cv.numrv = p.numrv and cv.datarv = p.datarv and cast(cv.numparcelas as int) >= cast(p.numparcela1 as int) and rv.numpdv = cv.numpdv and rv.datarv = cv.datarv and rv.numrv = cv.numrv and not exists (select 1 from extratoredecardfindesagparc where cv.numpdv = numpdv and cv.numrv = numrv and cv.datarv = datarv and cv.numcv = numcvorig and cv.datacv = datacvorig and numparcela = p.numparcela1)
union all
select cv.datacv as datavenda, p.datacredito1 as datacredito, 'C' as tipoproduto, cv.captura as captura, cv.codlojasitef as codlojasitef, cv.datatrnsitef as datatrnsitef, cast(cv.numparcelas as int) as totalparcela, cv.tipocaptura, decode (p.numparcela1 , '01', cast(cv.valor_bruto_primeira_parcela as int), cast(cv.valor_bruto_demais_parcelas as int)) as valorbruto, decode (p.numparcela1 , '01', cast(cast(cv.valor_liquido_primeira_parcela as int) as int) - cast(d.valorajuste as int), cast(cast(cv.valor_liquido_demais_parcelas as int) as int) - cast(d.valorajuste as int)) as valorliquido from (SELECT numparcela, numpdv, numrv, datarv, numcvorig, datacvorig, tipo_lancam, sum(cast(valorajuste as int)) as valorajuste from extratoredecardfindesagparc group by numparcela, numpdv,numrv, datarv, numcvorig, datacvorig, tipo_lancam) d, extratoredecardcredrvparcsj rv, extratoredecardcredparcsj p, extratoredecardcredcvparcsj cv where cv.numpdv = d.numpdv and cv.numrv = d.numrv and cv.datarv = d.datarv and cv.numcv = d.numcvorig and cv.datacv = d.datacvorig and d.numparcela = p.numparcela1 and cv.numpdv = p.numpdv and cv.numrv = p.numrv and cv.datarv = p.datarv and cast(cv.numparcelas as int) >= cast(p.numparcela1 as int) and rv.numpdv = cv.numpdv and rv.datarv = cv.datarv and rv.numrv = cv.numrv and d.tipo_lancam = '1'
union all
select cv.datacv as datavenda, p.datacredito1 as datacredito, 'C' as tipoproduto, cv.captura as captura, cv.codlojasitef as codlojasitef, cv.datatrnsitef as datatrnsitef, cast(cv.numparcelas as int) as totalparcela, cv.tipocaptura, decode (p.numparcela1 , '01', cast(cv.valor_bruto_primeira_parcela as int), cast(cv.valor_bruto_demais_parcelas as int)) as valorbruto, decode (p.numparcela1 , '01', cast(cast(cv.valor_liquido_primeira_parcela as int) as int) - cast(d.valorajuste as int), cast(cast(cv.valor_liquido_demais_parcelas as int) as int) - cast(d.valorajuste as int)) as valorliquido from (SELECT numparcela, numpdv, numrv, datarv, numcvorig, datacvorig, tipo_lancam, sum(cast(valorajuste as int)) as valorajuste from extratoredecardfindesagparc group by numparcela, numpdv,numrv, datarv, numcvorig, datacvorig, tipo_lancam) d, extratoredecardcredrvparcsj rv, extratoredecardcredparcsj p, extratoredecardcredcvparcsj cv where cv.numpdv = d.numpdv and cv.numrv = d.numrv and cv.datarv = d.datarv and cv.numcv = d.numcvorig and cv.datacv = d.datacvorig and d.numparcela = p.numparcela1 and cv.numpdv = p.numpdv and cv.numrv = p.numrv and cv.datarv = p.datarv and cast(cv.numparcelas as int) >= cast(p.numparcela1 as int) and rv.numpdv = cv.numpdv and rv.datarv = cv.datarv and rv.numrv = cv.numrv and d.tipo_lancam = '0' and not exists (select 1 from extratoredecardfindesagparc where cv.numpdv = numpdv and cv.numrv = numrv and cv.datarv = datarv and cv.numcv = numcvorig and cv.datacv = datacvorig and tipo_lancam = '1' and numparcela = p.numparcela1)
union all
select cv.datacv as datavenda, p.datacredito1 as datacredito, 'C' as tipoproduto, cv.captura as captura, cv.codlojasitef as codlojasitef, cv.datatrnsitef as datatrnsitef, cast(cv.numparcelas as int) as totalparcela, cv.tipocaptura, decode (p.numparcela1 , '01', cast(cv.valor_bruto_primeira_parcela as int), cast(cv.valor_bruto_demais_parcelas as int)) as valorbruto, decode (p.numparcela1 , '01', cast(cv.valor_liquido_primeira_parcela as int), cast(cv.valor_liquido_demais_parcelas as int)) as valorliquido from extratoredecardcredrvparciata rv, extratoredecardcredparciata p, extratoredecardcredcvparciata cv where cv.numpdv = p.numpdv and cv.numrv = p.numrv and cv.datarv = p.datarv and cast(cv.numparcelas as int) >= cast(p.numparcela1 as int) and rv.numpdv = cv.numpdv and rv.datarv = cv.datarv and rv.numrv = cv.numrv and not exists (select 1 from extratoredecardfindesagparc where cv.numpdv = numpdv and cv.numrv = numrv and cv.datarv = datarv and cv.numcv = numcvorig and cv.datacv = datacvorig and numparcela = p.numparcela1)
union all
select cv.datacv as datavenda, p.datacredito1 as datacredito, 'C' as tipoproduto, cv.captura as captura, cv.codlojasitef as codlojasitef, cv.datatrnsitef as datatrnsitef, cast(cv.numparcelas as int) as totalparcela, cv.tipocaptura, decode (p.numparcela1 , '01', cast(cv.valor_bruto_primeira_parcela as int), cast(cv.valor_bruto_demais_parcelas as int)) as valorbruto, decode (p.numparcela1 , '01', cast(cast(cv.valor_liquido_primeira_parcela as int) as int) - cast(d.valorajuste as int), cast(cast(cv.valor_liquido_demais_parcelas as int) as int) - cast(d.valorajuste as int)) as valorliquido from (SELECT numparcela, numpdv, numrv, datarv, numcvorig, datacvorig, tipo_lancam, sum(cast(valorajuste as int)) as valorajuste from extratoredecardfindesagparc group by numparcela, numpdv,numrv, datarv, numcvorig, datacvorig, tipo_lancam) d, extratoredecardcredrvparciata rv, extratoredecardcredparciata p, extratoredecardcredcvparciata cv where cv.numpdv = d.numpdv and cv.numrv = d.numrv and cv.datarv = d.datarv and cv.numcv = d.numcvorig and cv.datacv = d.datacvorig and d.numparcela = p.numparcela1 and cv.numpdv = p.numpdv and cv.numrv = p.numrv and cv.datarv = p.datarv and cast(cv.numparcelas as int) >= cast(p.numparcela1 as int) and rv.numpdv = cv.numpdv and rv.datarv = cv.datarv and rv.numrv = cv.numrv and d.tipo_lancam = '1'
union all
select cv.datacv as datavenda, p.datacredito1 as datacredito, 'C' as tipoproduto, cv.captura as captura, cv.codlojasitef as codlojasitef, cv.datatrnsitef as datatrnsitef, cast(cv.numparcelas as int) as totalparcela, cv.tipocaptura, decode (p.numparcela1 , '01', cast(cv.valor_bruto_primeira_parcela as int), cast(cv.valor_bruto_demais_parcelas as int)) as valorbruto, decode (p.numparcela1 , '01', cast(cast(cv.valor_liquido_primeira_parcela as int) as int) - cast(d.valorajuste as int), cast(cast(cv.valor_liquido_demais_parcelas as int) as int) - cast(d.valorajuste as int)) as valorliquido from (SELECT numparcela, numpdv, numrv, datarv, numcvorig, datacvorig, tipo_lancam, sum(cast(valorajuste as int)) as valorajuste from extratoredecardfindesagparc group by numparcela, numpdv,numrv, datarv, numcvorig, datacvorig, tipo_lancam) d, extratoredecardcredrvparciata rv, extratoredecardcredparciata p, extratoredecardcredcvparciata cv where cv.numpdv = d.numpdv and cv.numrv = d.numrv and cv.datarv = d.datarv and cv.numcv = d.numcvorig and cv.datacv = d.datacvorig and d.numparcela = p.numparcela1 and cv.numpdv = p.numpdv and cv.numrv = p.numrv and cv.datarv = p.datarv and cast(cv.numparcelas as int) >= cast(p.numparcela1 as int) and rv.numpdv = cv.numpdv and rv.datarv = cv.datarv and rv.numrv = cv.numrv and d.tipo_lancam = '0' and not exists (select 1 from extratoredecardfindesagparc where cv.numpdv = numpdv and cv.numrv = numrv and cv.datarv = datarv and cv.numcv = numcvorig and cv.datacv = datacvorig and tipo_lancam = '1' and numparcela = p.numparcela1)) r where r.datacredito between '20230531' and '20230531' and r.tipoproduto in ('C') and r.codlojasitef in (select j.codlojasitef from defgrupo d, loja j where d.codlojasitef = j.codlojasitef and d.cod_grupo  in ('DRUGSTOR')) group by r.datacredito, r.datavenda order by r.datacredito, r.datavenda