Oracle ar invoice balance query

Oracle ar invoice balance query

In this post , We will be discuss about SQL query to find  ar invoices Balance in oracle apps.This will help to share the complete detail of AR Invoices with the Open Balances as on date. Please find below  the complete sql Query of Oracle ar invoice balance query.

 

Detail Oracle ar invoice balance query

select aps.*

FROM ra_customer_trx_all rcta,

ra_customer_trx_lines_all rl,

ar_payment_schedules_all aps,

ra_cust_trx_types_all rt,

hz_cust_accounts hc,

hz_parties hp,

hz_cust_acct_sites_all hcasa_bill,

hz_cust_site_uses_all hcsua_bill,

hz_party_sites hps_bill,
 
ra_cust_trx_line_gl_dist_all rct

 

WHERE rcta.cust_trx_type_id = rt.cust_trx_type_id

AND rcta.bill_to_customer_id = hc.cust_account_id

AND hc.status = 'A'

AND hp.party_id = hc.party_id

AND rcta.customer_trx_id = rl.customer_trx_id

AND rcta.customer_trx_id = aps.customer_trx_id

AND rcta.org_id = aps.org_id

AND rct.customer_trx_id = aps.customer_trx_id

AND rct.customer_trx_id = rcta.customer_trx_id

AND rct.customer_trx_id = rl.customer_trx_id

AND rct.customer_trx_line_id = rl.customer_trx_line_id

AND rcta.complete_flag = 'Y'

AND hcasa_bill.status = 'A'

AND hcsua_bill.status = 'A'

AND aps.amount_due_remaining <> 0

AND aps.status = 'OP'

AND rl.line_type IN ('FREIGHT', 'LINE')

AND hcasa_bill.cust_account_id = rcta.bill_to_customer_id

AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id

AND hcsua_bill.site_use_code = 'BILL_TO'

AND hcsua_bill.site_use_id = rcta.bill_to_site_use_id

AND hps_bill.party_site_id = hcasa_bill.party_site_id