Query to get unpaid ar invoices in oracle apps

Query to get unpaid ar invoices in oracle apps

In this post , We will be discuss about SQL query to find the Unpaid ar invoices in oracle apps. In Ar , We said Un Paid Ar Invoices to Un-applied Ar Invoices because once we receive the Payment from Customers then We need to apply again the AR Invoice to make it Close other wise this remain Open or Unpaid AR invoice in oracle apps. Here below I will share the complete sql Query to get unpaid ar invoices in oracle apps

Detail SQL Query to get unpaid ar invoices in oracle apps

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