Query to find open receipts in oracle apps

Query to find open receipts in oracle apps

In this post , we will be discuss about Query to find open receipts in oracle apps. Open Receipts in oracle apps are those Customer Payments which are still not applied to the customer Invoices. Once the Receipts will be fully applied to the customer invoice then the receipt status will show 'CLOSED'. One receipt can apply to multiple Customer AR Transactions/AR Invoices. Here below is the complete SQL query to find open receipts in oracle apps.

 

 

6 Important Tables to Find Open receipts in Oracle Apps

 1.AR_PAYMENT_SCHEDULES_ALL

2.AR_CSUTOEMRS

3.HZ_CUST_SITE_USES_ALL

4.HZ_CUST_ACCT_SITES_ALL

5.HZ_PARTY_SITES

6.AR_CASH_RECEIPTS_ALL

 

Detail SQL Query to find open receipts in oracle apps

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,

A6.RECEIPT_NUMBER,

A6.RECEIPT_DATE,

A6.COMMENTS,

A1.AMOUNT_APPLIED,

A1.EXCHANGE_RATE,

A1.EXCHANGE_DATE,

A1.EXCHANGE_RATE_TYPE

A2.CUSTOMER_NAME,

A5.PARTY_SITE_NUMBER,

A3.SITE_USE_CODE

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_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

AND A1.CUSTOMER_ID=A2.CUSTOMER_ID

AND A1.CASH_RECEIPT_ID=A6.CASH_RECEIPT_ID

and A1.CLASS='PMT'

AND A1.STATUS='OP'