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;

  • September 20, 2018 | 15 views
  • Comments