Wednesday 19 September 2018

Inactive Customer Open Balances


select * from apps.ap_expense_report_headers_all where report_header_id = '';

select * from hz_cust_accounts where account_number like '07724.0M978%';

select hca.account_number
,count(rct.trx_number)
,substr(to_char(rct.trx_Date,'DD/MM/YYYY'),4,2)
,sum(rgl.amount)
,sum(amount_due_remaining) OPEN_BL
,sum(rgl.amount)-sum(amount_due_remaining) CLOSED_BL
,rct.invoice_currency_code ,rct.org_id
 from apps.ra_customer_trx_all rct,
 apps.hz_cust_accounts hca ,
 apps.RA_CUST_TRX_LINE_GL_DIST_ALL rgl
 ,apps.ar_payment_schedules_all aps
where rgl.customer_trx_id=rct.customer_trx_id
and hca.cust_account_id=rct.bill_to_customer_id
and account_class='REC'
and latest_rec_flag='Y'
and aps.customer_trx_id=rct.customer_trx_id
and rct.org_id=82
and hca.status='I'
--and to_char(rct.trx_Date,'DD/MM/YYYY') between '01-DEC-2014' and '31-DEC-2014'
and rct.trx_Date between '01-JAN-2016' and '01-SEP-2016'
--and bill_to_customer_id=143685
group by hca.account_number,substr(to_char(rct.trx_Date,'DD/MM/YYYY'),4,2),rct.invoice_currency_code,rct.org_id;

No comments:

Post a Comment