pvivltab then pvivlven else pvivltab end)*pviqtdeatecar as pvivltab
,(case when pvivlven>pvivltab then 0 else pvivltab-pvivlven end)*pviqtdeatecar as vldesc
,coalesce(pvivlipi,0)+coalesce(pvivlst,0)+pvivltotal as pvivltotal
,coalesce(pvivlst,0) as pvivlst
,coalesce(pvivlipi,0) as pvivlipi
,coalesce(pvvlfrete,0) as pvvlfrete
,coalesce(pvobsped,'') as pvobsped
,estaliqipi as aliqipi
,pvivlipi
,pvivlst
,r.pardes as rcades
,case when opnftopnfcod='CF' then gtecfaliqicms else case when c.parcontricms='S' then gtaliqicms else gtncontribaliqicms end end as aliqicms
,pvivlven*(case when opnftopnfcod='CF' then gtecfaliqicms else case when c.parcontricms='S' then gtaliqicms else gtncontribaliqicms end end)/100 as vlicms
,cstpiscofins_aliq_pis as aliqpis
,pvivlven*coalesce(cstpiscofins_aliq_pis,0)/100 as vlpis
,cstpiscofins_aliq_cofins as aliqcofins
,pvivlven*coalesce(cstpiscofins_aliq_cofins,0)/100 as vlcofins
from pv
join cli on pvcliparcod=cliparcod
join par c on cliparcod=c.parcod
join plpg on pvplpgcod=plpgcod
join tc on pvtccod=tccod
join loc on cliloccod = loccod
join par r on pvrcaparcod = r.parcod
join par e on pvempparcod = e.parcod
join mun emp_mun on e.parmuncod=emp_mun.muncod
join mun cli_mun on c.parmuncod=cli_mun.muncod
join pvi on pvcod=pvipvcod
join pro on pviprocod=procod
join est on estprocod = procod and estempparcod = pvipvempparcod
join emp on empparcod=pvipvempparcod
join opnf on pvopnfcod = opnfcod
left join regtrib on empregtribcod=regtribcod
--trib por benef
left join tribbenef on regtribcod=tribbenefregtribcod
and tribbenefuforig=(select munufsigla from par join mun on parmuncod=muncod where parcod=pvempparcod)
and tribbenefufdest=(select lpuf from lp where lpcod=f_livro_do_cliente(pvcliparcod,pvempparcod))
and tribbenefbenefcod=clibenefcod
and tribbenefprocod=procod
--trib por produto
left join tribpro on regtribcod=tribproregtribcod
and tribprouforig=(select munufsigla from par join mun on parmuncod=muncod where parcod=pvempparcod)
and tribproufdest=(select lpuf from lp where lpcod=f_livro_do_cliente(pvcliparcod,pvempparcod))
and tribproprocod=procod
--trib por ncm
left join trib on regtribcod=tribregtribcod
and tribuforig=(select munufsigla from par join mun on parmuncod=muncod where parcod=pvempparcod)
and tribufdest=(select lpuf from lp where lpcod=f_livro_do_cliente(pvcliparcod,pvempparcod))
and tribncm=proncm
-- se utilizar GT de OPNF
--left join gt on tribgtcod=gtcod
--da prioridade para gt por opnf, depois por trib produto e por fim trib ncm
left join gt on gtcod=
case
when opnfgt='S' and coalesce(tribbenefuforig,tribprouforig,tribuforig)=coalesce(tribbenefufdest,tribproufdest,tribufdest) then opnfgtcode
when opnfgt='S' and coalesce(tribbenefuforig,tribprouforig,tribuforig)<>coalesce(tribbenefufdest,tribproufdest,tribufdest) then opnfgtcodi
else coalesce(tribbenefgtcod,tribprogtcod,tribgtcod) end
left join beneficios on ben_id = gt_ben_id
--verificar PIS/COFINS
left join cstpiscofins on regtribcod=cstpiscofins_regtribcod and estcstpiscofinscod=cstpiscofins_cod
--REGRA DIFAL
left join tribdifal on tribdifaluf=(select lpuf from lp where lpcod=f_livro_do_cliente(pvcliparcod,pvempparcod))
where pviqtdeatecar>0
and pvcod=$P{P_PVCOD}
order by prodes]]>