Query to find unapplied receipts

Query to find unapplied receipts

In this post , We will discuss about unapplied receipts in oracle apps. We got receipt from Customer in Oracle receivables against the Invoices which we sent to the customers and when we enter the receipt of the customer in receivables then we need to apply that receipt against the invoices so that outstanding of the customers should be nullify and then receipt got closed. oracle ar unapplied receipts query helps to find those receipts which is still left to apply against the customer invoices and are still showing open for apply against the customer invoices. Here below is the complete sql query of oracle ar unapplied receipts.

Tables uses in uery to find unapplied receipts

ar_payment_schedules_all

ar_customers

HZ_CUST_SITE_USES_ALL

HZ_CUST_ACCT_SITES_ALL

HZ_PARTY_SITES A5

ar_cash_receipts_all

Example of oracle ar unapplied receipts query.

select A1.AMOUNT_DUE_ORIGINAL RECEIPT_AMOUNT,

A1.AMOUNT_DUE_REMAINING UNAPPLIED_AMOUNT,

A1.DUE_DATE,DECODE(A1.STATUS,'OP','OPEN','CL','CLOSED') RECEIPT_STATUS,

A1.INVOICE_CURRENCY_CODE,

A2.CUSTOMER_NAME,

A5.PARTY_SITE_NUMBER,

A3.SITE_USE_CODE,

A6.RECEIPT_NUMBER,

A6.RECEIPT_DATE,

A6.COMMENTS,

A1.AMOUNT_APPLIED,

A1.EXCHANGE_RATE,

A1.EXCHANGE_DATE,

A1.EXCHANGE_RATE_TYPE

 from ar_payment_schedules_all A1,ar_customers a2,

 HZ_CUST_SITE_USES_ALL A3,HZ_CUST_ACCT_SITES_ALL A4,HZ_PARTY_SITES A5,ar_cash_receipts_all A6

where A1.org_id=:P_ORG_ID

AND A1.CUSTOMER_ID=A2.CUSTOMER_ID

AND A1.CASH_RECEIPT_ID=A6.CASH_RECEIPT_ID

and A1.CLASS='PMT'

AND A1.STATUS='OP'

AND  A1.CUSTOMER_SITE_USE_ID=A3.SITE_USE_ID

AND A3.CUST_ACCT_SITE_ID=A4.CUST_ACCT_SITE_ID

AND A4.PARTY_SITE_ID=A5.PARTY_SITE_ID