1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| with cte_base as
(
select BRA_CODE, TRA_DATE, TRA_SEQ1
from TRANSACT
where EXPL_CODE = 368
and DOC_ALP = '31'
and TRA_DATE >= date '2017-02-09'
)
select trs.BRA_CODE, trs.TRA_DATE, trs.TRA_SEQ1
, round(sum(case trs.EXPL_CODE when 2049 then 1 else 0 end * trs.TRA_AMT), 2) as tva_collect
, round(sum(case trs.EXPL_CODE when 2049 then 0 else 0.19 end * trs.TRA_AMT), 2) as tva_reelle
from TRANSACT trs
where exists (select null
from cte_base cbs
where cbs.BRA_CODE = trs.BRA_CODE
and cbs.TRA_DATE = trs.TRA_DATE
and cbs.TRA_SEQ1 = trs.TRA_SEQ1)
where trs.EXPL_CODE in (2049, 2124, 2127)
and trs.DEB_CRE_IND = 2
group by trs.BRA_CODE, trs.TRA_DATE, trs.TRA_SEQ1
having round(sum(case trs.EXPL_CODE when 2049 then 1 else 0 end * trs.TRA_AMT), 2)
<> round(sum(case trs.EXPL_CODE when 2049 then 0 else 0.19 end * trs.TRA_AMT), 2); |
Partager