AR receipts query in oracle apps

AR receipts query in oracle apps

In this post , We will discuss about Oracle receivables receipts sql queries. I will share my sql queries which helps to extract the AR receipts query in oracle apps. This sql query will help to fetch the most of the important information for the Customer receipt in oracle apps.  Here below is the complete AR receipts query in oracle apps

Tables Uses in AR receipts query in oracle apps

ar_payment_schedules_all

ar_customers

HZ_CUST_SITE_USES_ALL

HZ_CUST_ACCT_SITES_ALL

HZ_PARTY_SITES

ar_cash_receipts_all

Complete Example of AR receipts query 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,

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