select org_id,
customer_trx_id,
sum(tax) tax,
sum(rev) rev,
sum(rec) rec,
sum(tax)+sum(rev) acutal_rec
from ( select org_id,
customer_trx_id,
case when account_class=’TAX’ then sum(amount) else 0 end tax,
case when account_class=’REV’ then sum(amount) else 0 end rev,
case when account_class=’REC’ then sum(amount) else 0 end rec,
from apps.ra_cust_trx_line_gl_dist_all x
where creation_date >=’01-May-21′
group by org_id,customer_trx_id,account_class)
group by org_id,customer_trx_id
having sum(tax)+sum(rev)<>sum(rec)
Recent Posts
