Oracle Receivables R12 AR Receipts SQL Queries

23 Jan 2021 oracleappsadmin

The blog provides the commonly used SQL Queries to reconcile the Oracle Receivables R12 AR Receipts to get accounted AR Receipts with GL Code Combinations, AR Payments received through checks , AR Receipts processing details, etc.

Oracle AR Receipts Tables

The below given are the Oracle AR Receipts tables in Oracle Receivables R12

Oracle AR R12 Receipts TablesAR Receipt Table Description
AR_CASH_RECEIPTS_ALLThe Receipt table stores receipt entry per record
AR_CASH_RECEIPT_HISTORY_ALLThe Receipt table stores the AR Receipt History and AR Receipt status updates
AR_MISC_CASH_DISTRIBUTIONS_ALLThe table stores the accounting entries for miscellaneous cash applications
AR_DISTRIBUTIONS_ALLThe table stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments, credit memo applications, cash receipt applications, and bills receivable transactions
AR_RECEIVABLE_APPLICATIONS_ALLThe table stores the accounting entries for cash and credit memo applications

Oracle Receivables R12 SQL Query to retrieve AR Receipts accounted with GL Code Combinations

The Oracle Receivables R12 SQL Query uses table mappings for AR_DISTRIBUTIONS_ALL , GL_CODE_COMBINATIONS, AR_CASH_RECEIPT_HISTORY_ALL and AR_CASH_RECEIPT_ALL to retrieve AR Receipts with GL Code Combinations from GL Table

SELECT AMOUNT_DR, AMOUNT_CR, ACCTD_AMOUNT_DR, ACCTD_AMOUNT_CR,
GCC.SEGMENT1
|| '.'
|| GCC.SEGMENT2
|| '.'
|| GCC.SEGMENT3
|| '.'
|| GCC.SEGMENT4
|| '.'
|| GCC.SEGMENT5 ACCOUNT
FROM AR.AR_DISTRIBUTIONS_ALL AD,
     GL.GL_CODE_COMBINATIONS GCC
WHERE 1=1
AND EXISTS (
SELECT 'T'
FROM AR.AR_CASH_RECEIPT_HISTORY_ALL A, 
     AR.AR_CASH_RECEIPTS_ALL B
WHERE A.CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
AND SOURCE_ID = CASH_RECEIPT_HISTORY_ID
AND B.ORG_ID = '&ORG_ID'
AND B.ORG_ID = A.ORG_ID
AND B.RECEIPT_NUMBER LIKE '%&RECEIPT_NUMBER%')
AND AD.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

Oracle Receivables R12 SQL Query to retrieve AR Receipts Payment Schedule Details

The Oracle Receivables R12 SQL Query uses table mappings for AR_PAYMENT_SCHEDULES_ALL , AR_CUSTOMERS, AR_CASH_RECEIPT_ALL, HZ_CUST_SITE_USES_ALL, HZ_CUST_ACCT_SITES_ALL, HZ_PARTY_SITES to retrieve AR Receipts Payment Schedule details based on ORG_ID input 

SELECT 	AR_PAY_SCH_ALL_TBL.AMOUNT_DUE_ORIGINAL RECEIPT_AMOUNT,
		AR_PAY_SCH_ALL_TBL.AMOUNT_DUE_REMAINING UNAPPLIED_AMOUNT,
		AR_PAY_SCH_ALL_TBL.DUE_DATE,DECODE(AR_PAY_SCH_ALL_TBL.STATUS,'OP','OPEN','CL','CLOSED') RECEIPT_STATUS,
		AR_PAY_SCH_ALL_TBL.INVOICE_CURRENCY_CODE,
		AR_CUSTOMERS_TBL.CUSTOMER_NAME,
		HZ_PARTY_SITES_TBL.PARTY_SITE_NUMBER,
		HZ_CUST_SITE_USES_ALL_TBL.SITE_USE_CODE,
		AR_CASH_RECEIPTS_ALL_TBL.RECEIPT_NUMBER,
		AR_CASH_RECEIPTS_ALL_TBL.RECEIPT_DATE,
		AR_CASH_RECEIPTS_ALL_TBL.COMMENTS,
		AR_PAY_SCH_ALL_TBL.AMOUNT_APPLIED,
		AR_PAY_SCH_ALL_TBL.EXCHANGE_RATE,
		AR_PAY_SCH_ALL_TBL.EXCHANGE_DATE,
		AR_PAY_SCH_ALL_TBL.EXCHANGE_RATE_TYPE
 FROM 	AR_PAYMENT_SCHEDULES_ALL AR_PAY_SCH_ALL_TBL,
		AR_CUSTOMERS AR_CUSTOMERS_TBL,
		HZ_CUST_SITE_USES_ALL HZ_CUST_SITE_USES_ALL_TBL,
		HZ_CUST_ACCT_SITES_ALL HZ_CUST_ACCT_SITES_ALL_TBL,
		HZ_PARTY_SITES HZ_PARTY_SITES_TBL,
		AR_CASH_RECEIPTS_ALL AR_CASH_RECEIPTS_ALL_TBL
WHERE 	AR_PAY_SCH_ALL_TBL.ORG_ID=:P_ORG_ID
AND		AR_PAY_SCH_ALL_TBL.CUSTOMER_ID=AR_CUSTOMERS_TBL.CUSTOMER_ID
AND		AR_PAY_SCH_ALL_TBL.CASH_RECEIPT_ID=AR_CASH_RECEIPTS_ALL_TBL.CASH_RECEIPT_ID
AND		AR_PAY_SCH_ALL_TBL.CLASS='PMT'
AND 	AR_PAY_SCH_ALL_TBL.CUSTOMER_SITE_USE_ID=HZ_CUST_SITE_USES_ALL_TBL.SITE_USE_ID
AND 	HZ_CUST_SITE_USES_ALL_TBL.CUST_ACCT_SITE_ID=HZ_CUST_ACCT_SITES_ALL_TBL.CUST_ACCT_SITE_ID
AND 	HZ_CUST_ACCT_SITES_ALL_TBL.PARTY_SITE_ID=HZ_PARTY_SITES_TBL.PARTY_SITE_ID

Oracle Receivables R12 SQL Query to retrieve AR Receipts Processing Details

SELECT ACRA.REQUEST_ID,ACRA.RECEIPT_NUMBER,
       ACRA.AMOUNT,  ACRA.RECEIPT_DATE, ACRA.DEPOSIT_DATE, ACRA.*  
FROM AR_CASH_RECEIPTS_ALL   ACRA
WHERE 1=1
AND RECEIPT_NUMBER IN (<AR_RECEIPT_NUMBER>)

Oracle Receivables R12 SQL Query to retrieve AR Receipts Accounted

SELECT 	ACR.RECEIPT_NUMBER,
		ADA.AMOUNT_DR, 
		ADA.AMOUNT_CR,
		ADA.ACCTD_AMOUNT_DR,
		ADA.ACCTD_AMOUNT_CR,
		ADA.SOURCE_TABLE, 
		MCD.SET_OF_BOOKS_ID
FROM   	AR.AR_DISTRIBUTIONS_ALL ADA,
		AR.AR_MISC_CASH_DISTRIBUTIONS_ALL MCD,
		AR.AR_CASH_RECEIPTS_ALL ACR
WHERE 1=1
AND 	ADA.SOURCE_ID = MCD.MISC_CASH_DISTRIBUTION_ID
AND 	MCD.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
AND ACR.RECEIPT_NUMBER =<AR_RECEIPT_NUMBER>
AND MCD.SET_OF_BOOKS_ID = <SOB_ID>

Oracle Receivables R12 SQL Query to retrieve AR Payments Received Through Checks

SELECT APIA.CHECK_NUMBER,  APIA.STATUS, APIA.* 
   FROM AR_PAYMENTS_INTERFACE_ALL  APIA
   WHERE 1=1
   AND APIA.STATUS ='AR_PLB_GL_PERIOD_CLOSED'
   AND CHECK_NUMBER IN (<CHECK_NUMBER>)
   ORDER BY CREATION_DATE DESC

SELECT * FROM AR_PAYMENTS_INTERFACE 
WHERE  1=1
AND STATUS = 'AR_PLB_GL_PERIOD_CLOSED'
AND CHECK_NUMBER = <CHECK_NUMBER>