Query to get AR Customer Receipt data in Oracle Apps R12

The blog provides the SQL Query to get Customer specific receipt details in Oracle Receivables R12

Query to get AR Customer Receipt data in Oracle Apps R12

SELECT 	ACRA.CASH_RECEIPT_ID,
DECODE (	 ACRA.TYPE,
			 'CASH', 'CASH RECEIPT RECEIPT',
			 'MISC', 'MISCELLANEOUS',
			 ACRA.TYPE
		) RECEIPT_TYPE,
		ACRA.CURRENCY_CODE,
		ACRA.DOC_SEQUENCE_VALUE RECEIPT_NUMBER,
		ACRA.RECEIPT_NUMBER REFERENCE_NUMBER,
		TRUNC (ACRA.RECEIPT_DATE) RECEIPT_DATE, 
		HP.PARTY_NAME RECEIVED_FROM,
		ACRA.MISC_PAYMENT_SOURCE,
		HCA.ACCOUNT_NUMBER CUSTOMER_NO,
		NVL (ACRA.AMOUNT, 0) ENTERED_AMOUNT,
		NVL (ACRA.AMOUNT, 0) * NVL (ACRA.EXCHANGE_RATE, 1) FUNCTIONAL_AMOUNT,
		ARM.NAME PAYMENT_METHOD,
		ABAA.BANK_ACCOUNT_NUM BANK_ACC_NUM,
		ABB.BANK_NAME,
		ABB.BANK_BRANCH_NAME,
		ACRA.COMMENTS DESCRIPTION
FROM 	AR_CASH_RECEIPTS_ALL ACRA,
		AR_RECEIPT_METHODS ARM,
		AP_BANK_ACCOUNTS_ALL ABAA,
		AP_BANK_BRANCHES ABB,
		HZ_CUST_ACCOUNTS HCA,
		HZ_PARTIES HP
WHERE 	ACRA.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID(+)
AND 	ACRA.ORG_ID = ABAA.ORG_ID(+)
AND 	HCA.PARTY_ID = HP.PARTY_ID(+)
AND 	ACRA.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
AND 	ACRA.REMITTANCE_BANK_ACCOUNT_ID = ABAA.BANK_ACCOUNT_ID
AND 	ABAA.BANK_BRANCH_ID = ABB.BANK_BRANCH_ID
AND 	HCA.CUST_ACCOUNT_ID = '<CUTOMER_ACCOUNT_ID>'
ORDER BY TRUNC (ACRA.RECEIPT_DATE), ACRA.DOC_SEQUENCE_VALUE;

Oracle AR SQL Query for Uncleared Receipt Amount Total

The blog provides the Oracle AR SQL Query for retrieving Uncleared Receipt Amount Total in Oracle apps R12

SQL Query to get Uncleared Receipt Amount Total

SELECT NVL(SUM(AR_Pay_Sch_tbl.AMOUNT_DUE_REMAINING), 0) 		unClearedReceiptTotalAmt
  FROM HZ_CUST_Agcc_tblOUNTS_ALL           			HZ_CUST_Agcc_tblT_ALL_TBL,
       AR_PAYMENT_SCHEDULES_ALL       				AR_Pay_Sch_tbl,
       AR_RECEIVABLE_APPLICATIONS_ALL 				AR_Rec_Appl_tbl,
       HZ_HZ_CUST_Agcc_tblT_ALL_TBL_SITES_ALL       HZ_CUST_AGgcc_tbl_TBLT_SITES,
       HZ_HZ_PARTY_SITES_TBLS                 		HZ_PARTY_SITES_TBL,
       HZ_Hz_loc_tblATIONS                   		Hz_loc_tbl,
       HZ_CUST_HZ_CUST_SITE_USES_TBL_ALL          	HZ_CUST_SITE_USES_TBL,
       AR_CASH_RECEIPT_HISTORY_ALL    				AR_CASH_RECPT_HIST_TBL,
       AR_CASH_RECEIPTS_ALL           				AR_CASH_RCPT_ALL_TBL,
       GL_CODE_COMBINATIONS           				gcc_tbl
 WHERE 1=1
   AND AR_Pay_Sch_tbl.CUSTOMER_ID = HZ_CUST_Agcc_tblT_ALL_TBL.CUST_Agcc_tblOUNT_ID
   AND AR_Pay_Sch_tbl.CUSTOMER_ID = HZ_CUST_Agcc_tblT_ALL_TBL.CUST_Agcc_tblOUNT_ID
   AND HZ_CUST_AGgcc_tbl_TBLT_SITES.HZ_PARTY_SITES_TBL_ID = HZ_PARTY_SITES_TBL.HZ_PARTY_SITES_TBL_ID
   AND Hz_loc_tbl.Hz_loc_tblATION_ID = HZ_PARTY_SITES_TBL.Hz_loc_tblATION_ID
   AND AR_Pay_Sch_tbl.CASH_RECEIPT_ID = AR_CASH_RCPT_ALL_TBL.CASH_RECEIPT_ID
   AND AR_CASH_RCPT_ALL_TBL.CASH_RECEIPT_ID = AR_CASH_RECPT_HIST_TBL.CASH_RECEIPT_ID
   AND AR_CASH_RECPT_HIST_TBL.Agcc_tblOUNT_CODE_COMBINATION_ID = gcc_tbl.CODE_COMBINATION_ID
   AND AR_Pay_Sch_tbl.CASH_RECEIPT_ID = AR_Rec_Appl_tbl.CASH_RECEIPT_ID
   AND HZ_CUST_SITE_USES_TBL.HZ_CUST_Agcc_tblT_ALL_TBL_SITE_ID = HZ_CUST_AGgcc_tbl_TBLT_SITES.HZ_CUST_Agcc_tblT_ALL_TBL_SITE_ID
   AND HZ_CUST_Agcc_tblT_ALL_TBL.CUST_Agcc_tblOUNT_ID = HZ_CUST_AGgcc_tbl_TBLT_SITES.CUST_Agcc_tblOUNT_ID
   AND HZ_CUST_AGgcc_tbl_TBLT_SITES.HZ_CUST_Agcc_tblT_ALL_TBL_SITE_ID = HZ_CUST_SITE_USES_TBL.HZ_CUST_Agcc_tblT_ALL_TBL_SITE_ID
   AND AR_Pay_Sch_tbl.CUSTOMER_ID = HZ_CUST_AGgcc_tbl_TBLT_SITES.CUST_Agcc_tblOUNT_ID
   AND AR_Pay_Sch_tbl.CUSTOMER_SITE_USE_ID = HZ_CUST_SITE_USES_TBL.SITE_USE_ID
   AND AR_Pay_Sch_tbl.CASH_RECEIPT_ID = AR_CASH_RECPT_HIST_TBL.CASH_RECEIPT_ID
   AND NVL(HZ_CUST_SITE_USES_TBL.STATUS, 'A') = 'A'
   AND AR_Pay_Sch_tbl.CLASS = 'PMT'
   AND AR_Rec_Appl_tbl.STATUS = 'UNAPP'
   AND AR_Pay_Sch_tbl.STATUS = 'OP'
   AND HZ_CUST_SITE_USES_TBL.SITE_USE_CODE = 'BILL_TO'
   AND HZ_CUST_Agcc_tblT_ALL_TBL.Agcc_tblOUNT_NUMBER = '<ENTER AGCC TBLOUNT NUMBER>'
   AND AR_Pay_Sch_tbl.TRX_DATE <= '<ENTER_TRX_DATE>'
   AND TRUNC(AR_Pay_Sch_tbl.GL_DATE) <= '<ENTER_GL_DATE>'
   AND AR_CASH_RECPT_HIST_TBL.STATUS NOT IN ('CLEARED') HAVING
 NVL(SUM(AR_Rec_Appl_tbl.AMOUNT_APPLIED), 0) > 0

AR Receipt Source creation in Oracle Receivables R12

The blog provides the steps to create Oracle Receivables R12 AR Receipt Batch Sources. The Receipt batch sources provides the default value for the Receipt Class, Receipt Methods and Remittance Bank Account fields that are added as part of Receipt Batch. Oracle Receivables allows to accept the default value or can be created as per user requirement. 

Receipt Batch sources can use manual or automatic batch numbering as defined in the setup

When receipt batch source is selected for Receipt Creation, Oracle Receivables automatically uses the Cash, Unapplied, Unidentified, On-Account, and Earned and Unearned Discount account to the Receipt Payment Method.

Prerequisites for Receipt Batch Source

The below setup should be available before you define a Receipt Batch Source

  • Define Banks
  • Define Receipt Classes
  • Define Receipt Methods

How to Create a Receipt Source in Oracle Receivables R12 ?

Perform the below steps to define a Receipt Source in Oracle AR R12

  • Navigate to AR -> setup -> Receipt -> Receipt source
  • Select an OU (Operating Unit)
  • Enter the Receipt Class
  • Enter the Receipt Method (Optional)
  • Enter Bank Account
  • Manual Batch Numbering ( Select if Receipt Batch Numbers to be entered manually)
  • Automatic Batch Numbering ( Select if Receipt Batch Numbers are entered by System)
  • Enter Start Date ( Date from when Receipt Source becomes Active)
  • Enter End Date ( Date when Receipt Source becomes Inactive)

Oracle Receivables SQL to get AR Receipt Sources Data Extract

SELECT HOU.NAME OPERATING_UNIT 
      ,AR_BATCH_SOURC_AR_LOOKUPS_TBLL_TBL.NAME   
      ,AR_BATCH_SOURC_AR_LOOKUPS_TBLL_TBL.DESCRIPTION
      ,AR_LOOKUPS_TBL.MEANING RECEIPT_SOURCE_TYPE  
      ,AR_RECEIPT_CLS_TBL.NAME RECEIPT_CLASS  
      ,AR_RECEIPT_MTHDS_TBL.NAME PAYMENT_METHOD 
      ,CE_BANK_ACCTS_TBL.BANK_ACCOUNT_NAME BANK_ACCOUNT  
      ,AR_LOOKUPS_TBL1.MEANING BATCH_NUMBERING 
      ,AR_BATCH_SOURC_AR_LOOKUPS_TBLL_TBL.LAST_BATCH_NUM LAST_NUMBER 
      ,AR_BATCH_SOURC_AR_LOOKUPS_TBLL_TBL.START_DATE_ACTIVE EFFECTIVE_START_DATE 
      ,AR_BATCH_SOURC_AR_LOOKUPS_TBLL_TBL.END_DATE_ACTIVE EFFECTIVE_END_DATE 
  FROM AR_BATCH_SOURCES_AR_LOOKUPS_TBLL AR_BATCH_SOURC_AR_LOOKUPS_TBLL_TBL   
      ,HR_OPERATING_UNITS HOU  
      ,AR_LOOKUPS AR_LOOKUPS_TBL   
      ,AR_RECEIPT_CLASSES AR_RECEIPT_CLS_TBL  
      ,AR_RECEIPT_METHODS AR_RECEIPT_MTHDS_TBL  
      ,AR_LOOKUPS AR_LOOKUPS_TBL1  
      ,CE_BANK_ACCOUNTS CE_BANK_ACCTS_TBL
 WHERE 1=1 
   AND AR_BATCH_SOURC_AR_LOOKUPS_TBLL_TBL.ORG_ID=HOU.ORGANIZATION_ID 
   AND HOU.ORGANIZATION_ID=NVL(:P_ORG_ID,HOU.ORGANIZATION_ID)  
   AND AR_LOOKUPS_TBL.LOOKUP_CODE(+)=AR_BATCH_SOURC_AR_LOOKUPS_TBLL_TBL.TYPE 
   AND AR_LOOKUPS_TBL.LOOKUP_TYPE(+)='BATCH_TYPE'  
   AND AR_RECEIPT_CLS_TBL.RECEIPT_CLASS_ID(+)=AR_BATCH_SOURC_AR_LOOKUPS_TBLL_TBL.DEFAULT_RECEIPT_CLASS_ID   
   AND AR_RECEIPT_MTHDS_TBL.RECEIPT_METHOD_ID(+)=AR_BATCH_SOURC_AR_LOOKUPS_TBLL_TBL.DEFAULT_RECEIPT_METHOD_ID 
   AND AR_LOOKUPS_TBL1.LOOKUP_CODE(+)=AR_BATCH_SOURC_AR_LOOKUPS_TBLL_TBL.AUTO_BATCH_NUMBERING
   AND AR_LOOKUPS_TBL1.LOOKUP_TYPE(+)='BATCH_NUMBERING'
   AND CE_BANK_ACCTS_TBL.BANK_ACCOUNT_ID(+)=AR_BATCH_SOURC_AR_LOOKUPS_TBLL_TBL.REMIT_BANK_ACCT_USE_ID   ;

AR Receipt Class and Receipt Method in Oracle Receivables R12

The blog provides the steps to “How to Setup AR Receipt Classes in Oracle Receivables R12 ?”. The Receipt Classes in Oracle Receivables R12 helps in Receipt processing steps ( Receipt Confirmation, Receipt Remittance and Receipt Reconciliation.  The Receipt Method assigned to Receipt Class helps in determining the accounting for receipts in Oracle Receivables. Each Create Receipt Class should have the following defined

  • Receipt Creation Method ( Manual / Automatic)
  • Receipt Remittance Method ( Standard, Factoring, Standard and Factoring, No Remittance)
  • Receipt Payment Method

AR Receipt Classes Setup in Oracle Receivables R12

Perform the below steps on how to setup AR Receipt Classes in Oracle Apps AR R12

  • Navigate to Receipt Classes
  • Enter Receipt Name
Oracle Receivables Receipt Classes
  • Notes Receivable Receipt Class ( Select the Checkbox if Receipt to handle future payments through Check or Promissory Note Document are considered in this option. A Note could have the status ( Open, Return, Delinquent, Repurchase, Exchange)
  • Enter Creation Method ( Manual / Automatic)
  • Require Confirmation : Select Checkbox if allows automatic receipt creation to use the Receipt Class
  • Enter Remittance Method: Selection of Require Confirmation for automatic receipts, make Remittance Method as mandatory. The Remittance Methods can be classified as given below
  • Standard : Remittance account used for automatic receipts with the Receipt Class
  • Factoring : Factoring account used for automatic receipts with the Receipt Class
  • Standard and Factoring : Allows to select Remittance Account or Factoring Account regardless of the batch remittance method
  • No Remittance : If do not require receipts assigned to the receipt class to be remitted

Enter Clearance Method: The Receipts created using a payment method assigned to the receipt class rquires to be reconciled before posting them to Cash Account in the General Ledger. The Clearance Method can be classified as given below

  • Directly : Select the option if the receipts do not need to be remitted to the bank and subsequently cleared
  • By Automatic Clearing : Select the option if the receipts to be cleared using the Automatic Clearing program
  • By Matching : Select the option if the receipts to be cleared manual in Order Cash Management

Remittance Bank Accounts

Click “Bank Accounts” on Receipt Sources Windows to assign the Remittance Bank Account . Provide the below given details

S. NoGL Field NameGL Field DescriptionUser Action
1Operating UnitOperating UnitSelect OU (Operating Unit)
2Bank NameBank NameSelect Bank Name
3Account NameAccount NameSelect the Account Name
4Effective DatesEffective DateEnter the Effective Date
S. NoGL Field NameGL Field DescriptionUser Action
1CashCash Accounting FlexfieldEnter Cash Accounting segments
2BankBank Accounting FlexfieldEnter Bank Accounting segments
3Unapplied ReceiptsUnapplied Receipts Accounting FlexfieldEnter Unapplied Receipts Accounting segments
4Unidentified ReceiptsUnidentified Receipts Accounting FlexfieldEnter Unidentified Receipts Accounting segments
5On Account ReceiptsOn Account Receipts Accounting FlexfieldEnter On-Account Accounting segments
6Unearned DiscountsUnearned Discounts fieldSelect Unearned Discount Value
7Earned DiscountsEarned Discounts fieldSelect Earned Discount Value

Oracle Receivables AR Receipt Method Setup

Oracle Receivables R12 uses Payment method to determine the Customer’s Remittance Bank Account details . Oracle Receivables allows to assign multiple remittance banks to each payment method considering one bank account as the primary bank account for each currency. Payments methods can be assigned to Receipt Sources to account the manual receipts and Autolockbox in Oracle receivables.

Automatic Receipts are required to be associated with Payment Methods. The below given are the Receipt Rule to be defined for payment methods when used with automatic reeipts

  • One Per Customer : Allows to create 1 payment per person
  • One per Customer and Due Date : Allows to create 1 payment per person and Due Date. Multiple payments can be processed based on Due Date
  • One Per Site : Allows to create 1 payment per site
  • One Per Invoice : Allows to create 1 payment per invoice
  • One Per Site and Due Date : Allows to create 1 payment per site and Due Date. Multiple payments can be processed based on Due Date

Perform the below steps to setup Receipt Methods in Oracle Apps R12

  • Navigate to Receipt Classes Window
  • Enter the Receipt Class
  • Enter the New Payment Method ( shows as Printed Name field on Receipt Classes Window)
  • Receipt Maturity Date Rule ( Enter Maturity Date for Automatic Receipts where Due Date is used for payment processing)
  • Automatic Print Program ( Automatic Receipts uses receivables standard receipt print program to format the payment output)
  • Enter Effective Dates

AR Receipt API methods in Oracle Receivables R12

The blog provides the AR Receipt API methods and its usage in Oracle Receivables R12.  The Oracle Receivables used AR Receipt API for creating and updating receipts received for AR Receipts Forms and from AR Lockboxes

What functionality is provided by AR Receipt API ?

The Oracle Receivables (AR) Receipt API helps in providing the below given functionality

  • AR Receipt API allows to create Cash Receipts
  • AR Receipt API allows to create Cash Receipts and applying to the Debit item
  • AR Receipt API allows on -account application
  • AR Receipt API allows to Unapply the on-account application
  • AR Receipt API allows to Unapply Receipt to a particular AR transaction
  • AR Receipt API allows to Reverse the Receipt
  • AR Receipt API allows to perform Receipt Write -Off
  • AR Receipt API allows to create Miscellaneous Receipt
  • AR Receipt API allows to create Receipt-to-Receipt application
  • AR Receipt API allows to create Cash Receipts and on-account application

AR RECEIPT API – AR_RECEIPT_API_PUB

The AR Receipt API AR_RECEIPT_API_PUB provides the below given API call

AR Receipt API RoutineDescription
AR_RECEIPT_API_PUB.Create_CashCreates a single Cash Receipt
AR_RECEIPT_API_PUB.ApplyApplies the Cash Receipt to a particular Debit Item
AR_RECEIPT_API_PUB.Create_and_applyCreates the Cash Receipt and applies to a particular Debit Item
AR_RECEIPT_API_PUB.Unapply unapply a particular Debit Item against the Cash Receipt
AR_RECEIPT_API_PUB.Apply_on_accountCreates a on-account application for a Cash Receipt
AR_RECEIPT_API_PUB.Unapply_on_accountunapplies on-account application for a Cash Receipt
AR_RECEIPT_API_PUB.ReverseReverse the Cash Receipt
AR_RECEIPT_API_PUB.activity_applicationApplies Receipt to an activity such as Receipt Write -Off
AR_RECEIPT_API_PUB.activity_unapplicationUnapplies Receipt from an activity such as Receipt Write -Off
AR_RECEIPT_API_PUB.Create_miscCreates a Miscellaneous Receipt
AR_RECEIPT_API_PUB.apply_other_accountApplies Receipt to other account such as Claim Investigation
AR_RECEIPT_API_PUB.unapply_other_accountUnapplies Receipt from other account such as Claim Investigation
AR_RECEIPT_API_PUB.apply_open_receiptCreates a receipt-to-receipt application (payment netting)
AR_RECEIPT_API_PUB.unapply_open_receiptunapplies a receipt-to-receipt application (payment netting)
AR_RECEIPT_API_PUB.Create_apply_on_accCreates a cash receipt and an on-account application

Integration with Oracle Payments

The Receipt API uses the Oracle Payment Integration for the below given API calls

AR Receipt API Routine Integrates with Oracle Payments ?
AR_RECEIPT_API_PUB.CREATE_CASHNo
AR_RECEIPT_API_PUB.CREATE_AND_APPLYYes
AR_RECEIPT_API_PUB.CREATE_MISCNo
AR_RECEIPT_API_PUB.CREATE_APPLY_ON_ACCYes


Configure AR Lookups in Oracle Receivables R12

The blog provides the list of AR Lookups that need to be defined in the Oracle Receivables R12.  New lookups can be created or predefined lookups can be updated in the Oracle Receivables R12. The Oracle Receivables R12 AR lookups are maintained in the FND_LOOKUP_VALUES Table where the LOOKUP_TYPE and MEANING Column values should be unique.

The below given are the commonly used Oracle Receivables lookups in R12. 

  • Customer Lookups
  • Customer Profile Lookups
  • Transaction Lookups
  • Collections Lookups
  • Receipt Lookups

Customer Lookups: The below given are the Customer Lookups defined in the Oracle Receivables R12

Lookup Type Lookup Code
Address Categories ADDRESS_CATEGORY
Customer Address Business Purpose SITE_USE_CODE
Customer CategoriesCUTOMER_CATEGORY
Customer ClassCUSTOMER_CLASS
Demand Class DEMAND_CLASS
FOBFOB
Customer Contact Job TitleRESPONSIBILTY
Party Fiscal Classification for LeasingAR_TAX_PARTY_FISCAL_CLASS
Contact Title at Customer SitesCONTACT_TITLE
Customer Communication Type COMMUNICATION_TYPE
Customer RelationshipRELATIONSHIP_TYPE
SIC Code TypeSIC_CODE_TYPE
State Codes STATE

Customer Profile Lookups: The below given are the Customer Profile Lookups defined in the Oracle Receivables R12

Lookup Type Lookup Code
Account Status ACCOUNT_STATUS
Customer Credit RatingCREDIT_RATING
Customer Credit RiskCREDIT_RISK

Transaction Lookups: The below given are the Transaction Lookups defined in the Oracle Receivables R12

Lookup Type Lookup Code
Adjustment ReasonADJUSTMENT_REASON
Approval TypeAPPROVAL_TYPE
Batch StatusBATCH_STATUS
Canadian ProvincesAR_CANADIAN_PROVINCE
Commitment DescriptionCOMMITMENT_DESCRIPTION
Credit Memo ReasonCREDIT_MEMO_REASON
Credit Memo StatusCREDIT_MEMO_REQUEST_STATUS
Invoice ReasonINVOICING_REASON
Location Identifier ARTAXVDR_LOC_QUALIFIER
Magnetic Format Codes MAGNETIC_FORMAT_CODE
Product Fiscal ClassificationAR_TAX_PRODUCT_FISCAL_CLASS
Special InstructionsSPECIAL_INSTRUCTIONS

Tax Classification
AR_TAX_CLASSIFICATION
Tax Exemption ReasonTAX_REASON
Tax Rate Exception reasonTAX_EXCEPTION_REASON
Tax TypeTAX_TYPE
Transaction Fiscal ClassificationAR_TAX_TRX_BUSINESS_CATEGORY
Message Type STANDARD_MSG_TYPES
Standard Text TypeSTANDARD_TEXT

Collections Lookups: The below given are the Collections Lookups defined in the Oracle Receivables R12

Lookup Type Lookup Code
Special Bucket type AGING_BUCKET_LINE_TYPE

Receipt Lookups: The below given are the Receipt Lookups defined in the Oracle Receivables R12

Lookup TypeLookup Code
Credit Card Error NotesAR_CC_ERROR_HANDLING
Matching Method ARLPLB_MATCHING_OPTION
Match on Corresponding DateARLPLB_USE_MATCHING_DATE
Payment TypeCASH_RECEIPT_TYPE
Receipt Reversal ReasonCKAJST_REASON
Mandatory field for Message DictionaryMANDATORY_FIELD_PROMPT
Reverse Payment ReasonREVERSE_PAYMENT_REASON


Oracle Receivables R12 AR Receipts SQL Queries

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>

Auto Accounting Rules in Oracle Account Receivables (AR)

The blog discuss about the Accounting Rule Configuration in Oracle Account Receivables R12.  Accounting Rule in Oracle Receivables allows to create Invoice Revenue Recognition schedule for the invoices to be processed.  Accounting rule determines number of periods and revenue percentage to be recorded for each accounting period. 

Accounting rules can be configured for the AutoInvoices ( Import Transactions into Receivables) and manually created Invoices in Oracle Receivables R12. Oracle Receivables R12 provides the Revenue Recognition Program which processed all the eligible invoices and generated distribution for the period for which accounting rule is configured. The accounting period with status Open, Future and Not Open are considered for revenue recognition accounting distribution.  Invoice with period as Closed creates distribution in the next open, Future, Not Open period

Define Period Type and Accounting Calendar in Oracle EBS R12

Define Accounting Rules in Oracle Receivables

Perform the below given steps for creating receivables accounting rule

Oracle Receivables Accounting Rule
  • Navigate to Accounting Rule Window
  • Enter the Accounting Rule Name
  • Enter the Accounting Rule Type from the below given selections
 Accounting Rule Type Name Accounting Rule Type Description
 Fixed Scheduleallows to calculate the prorate revenue recognition evenly whenever the accounting rule is being applied on invoice lines for the specific accounting period
 Variable Scheduleallows to calculate the revenue recognition over number of periods and assign the accounting rule with the values passed through AutoInvoices and manually entered invoices
 Daily Revenue Rate , All Periodsallows to calculate the daily revenue rate with the précised revenue amount for full and partial period in the schedule
 Daily Revenue Rate , Partial Periods allows to calculate the daily revenue rate with the précised revenue amount for partial period in the schedule
Daily Revenue Rate for Periods
  • Enter the Accounting Period and select the Defined Period Type.
  • Enter Number of Periods if the Accounting Rule is Fixed Schedule
  • Deferred Revenue Checkbox (Select this option if revenue is to be deferred to unearned revenue account)
  • Enter % of revenue for Accounting Period ( For Fixed Schedule it should be 100% , For Variable Schedule is not required to be defined)
  • If Accounting Rule Type is Fixed Schedule and selected Specific Dates , then enter dates for revenue recognition in accounting period

Deferred Accounting Rule in Oracle EBS R12

Deferred Accounting is required when specific transactions need to be accounted for Unearned Revenue Account. The Deferred accounting Rule can be enabled in the Invoice and Accounting Rule Windows by selecting the Deferred Revenue Checkbox.

The Revenue Recognition Program creates a single distribution per line for Unearned Revenue Account posting using the Revenue Accounting Management (RAM). The Deferred Accounting Rule can be applicable to invoices with the Invoicing Rule as ‘Bill In Advance’ .

If the Deferred Accounting Rule is applicable for single accounting period, then it revenue is recognized in the period for which Revenue Accounting Management (RAM) is specified

If the Deferred Accounting Rule is applicable for multiple accounting period, then it revenue is recognized as per the schedule accounting rule and start date is considered as GL Date retrieved from RAM Wizard. If the GL Date is specified for a Closed Period, then revenue is recognized in the next Open Accounting Period


Oracle EBS R12 SQL to get AR and AGIS Invoices

The blog provides Oracle EBS R12 SQL to get AR and AGIS Invoices with consolidated and non consolidated batches based on the AGIS Batch Source Name , Org_id and Flex Value Set Name defined

SELECT INTEF.ORG_ID,
INTEF.INTERFACE_LINE_ID,
INTEF.INTERFACE_LINE_ATTRIBUTE1 BATCH_ID,
INTEF.INTERFACE_LINE_ATTRIBUTE2 TRX_ID,
INTEF.INTERFACE_LINE_ATTRIBUTE3 LINE_ID,
INTEF.INTERFACE_LINE_ATTRIBUTE4 BATCH_NUMBER,
HEADERS.ATTRIBUTE1  ,   --trx_number in case of non-consolidated batches
FDL.ATTRIBUTE1, -- trx_number Null in case of AGIS manual batches and will have data in case of consolidated batches
BATCH.BATCH_NUMBER,
BATCH.BATCH_DATE,
LINE1.FLEX_VALUE TRADING_PARTNER_BC,
LINE2.FLEX_VALUE TRANSACTION_BC,
HOU1.NAME TRADING_ORG,
HOU2.NAME TRANSACTION_ORG,
HEADERS.BATCH_ID,
INTEF.CUST_TRX_TYPE_ID,
FTR.TRANSACTION_ORG_ID,
FTR.TP_ORG_ID,
FTR.TRANSACTION_LE_PARTY_ID,
FTR.TP_LE_PARTY_ID,
HEADERS.TRX_ID
FROM RA_INTERFACE_LINES INTEF,
FUN_TRX_BATCHES BATCH,
FUN_TRX_HEADERS HEADERS,
FUN_TRX_LINES FTL,
FUN_DIST_LINES FDL,
FUN_CUSTOMER_MAPS FCM,
FUN_TRADE_RELATIONS FTR,
FND_FLEX_VALUES LINE1,
FND_FLEX_VALUES LINE2,
FND_FLEX_VALUE_SETS HDR1,
FND_FLEX_VALUE_SETS HDR2,
HR_OPERATING_UNITS HOU1,
HR_OPERATING_UNITS HOU2
WHERE     1 = 1
AND INTEF.BATCH_SOURCE_NAME = :<ENTER_RBATCH_SOURCE_NAME>
AND INTEF.INTERFACE_LINE_ATTRIBUTE4 = BATCH.BATCH_NUMBER
AND INTEF.INTERFACE_LINE_ATTRIBUTE2 = HEADERS.TRX_ID
AND INTEF.ORG_ID = :<ENTER_-ORG_ID>
AND FCM.CUST_ACCOUNT_ID = INTEF.ORIG_SYSTEM_BILL_CUSTOMER_ID
AND HOU1.ORGANIZATION_ID = FTR.TRANSACTION_ORG_ID
AND HOU2.ORGANIZATION_ID = FTR.TP_ORG_ID
AND FTR.RELATION_ID = FCM.RELATION_ID
AND HDR1.FLEX_VALUE_SET_NAME = '<enter_flex_value_set_name>'
AND HDR1.FLEX_VALUE_SET_ID = LINE1.FLEX_VALUE_SET_ID
AND LINE1.ATTRIBUTE9 = HOU1.NAME
AND HDR2.FLEX_VALUE_SET_NAME = '<enter_flex_value_set_name>'
AND HDR2.FLEX_VALUE_SET_ID = LINE2.FLEX_VALUE_SET_ID
AND LINE2.ATTRIBUTE9 = HOU2.NAME
AND SYSDATE BETWEEN NVL (LINE1.START_DATE_ACTIVE, SYSDATE - 1)
AND NVL (LINE1.END_DATE_ACTIVE, SYSDATE)
AND SYSDATE BETWEEN NVL (LINE2.START_DATE_ACTIVE, SYSDATE - 1)
AND NVL (LINE2.END_DATE_ACTIVE, SYSDATE)
AND LINE1.ENABLED_FLAG = 'Y'
AND LINE2.ENABLED_FLAG = 'Y'
AND FTR.TP_ORG_ID = INTEF.ORG_ID
AND BATCH.BATCH_ID = HEADERS.BATCH_ID
AND HEADERS.TRX_ID = FTL.TRX_ID
AND FTL.LINE_ID = FDL.LINE_ID
AND FDL.PARTY_TYPE_FLAG = 'I'
AND FDL.DIST_TYPE_FLAG = 'L'
AND FDL.AUTO_GENERATE_FLAG = 'N'
AND INTEF.INTERFACE_LINE_CONTEXT = 'INTERNAL_ALLOCATIONS'

Oracle EBS R12 AR Receipt Class and Receipt Method SQL

The blog provides Oracle EBS R12 SQL to get AR Receipt Class and Receipt Method SQL Please find below the SQL Query to retrieve the Receipt Class and Receipt Method setup in the system

Navigation in front End: Receivables Manager -> Setup -> Receipts -> Receipt Classes

Query to verify the setup from backend

SELECT CLASS.NAME CLASS_NAME,
CLASS.CREATION_METHOD_CODE,
CLASS.REMIT_METHOD_CODE,
METHOD.NAME METHOD_NAME,
METHOD.ATTRIBUTE_CATEGORY,
METHOD.ATTRIBUTE1,
METHOD.ATTRIBUTE6,
METHOD.ATTRIBUTE7,
METHOD.ATTRIBUTE9,
METHOD.ATTRIBUTE10,
METHOD.ATTRIBUTE11,
BNKACCT.BANK_ACCOUNT_NAME,
BNKACCT.CURRENCY_CODE,
BNKBRCH.BANK_NAME,
BNKBRCH.BANK_BRANCH_NAME,
ACCT.ATTRIBUTE_CATEGORY ACCT_ATTRIBUTE_CATEGORY,
ACCT.ATTRIBUTE1 ACCT_ATTRIBUTE1,
ACCT.ATTRIBUTE2 ACCT_ATTRIBUTE2,
GCC1.SEGMENT1||’-‘||GCC1.SEGMENT2||’-‘||GCC1.SEGMENT3||’-‘||GCC1.SEGMENT4||’-‘||GCC1.SEGMENT5||’-‘||GCC1.SEGMENT6||’-‘||GCC1.SEGMENT7||’-‘||GCC1.SEGMENT8 AS CASH_ACCOUNT,
GCC2.SEGMENT1||’-‘||GCC2.SEGMENT2||’-‘||GCC2.SEGMENT3||’-‘||GCC2.SEGMENT4||’-‘||GCC2.SEGMENT5||’-‘||GCC2.SEGMENT6||’-‘||GCC2.SEGMENT7||’-‘||GCC2.SEGMENT8 AS REMITTANCE_ACCOUNT,
GCC3.SEGMENT1||’-‘||GCC3.SEGMENT2||’-‘||GCC3.SEGMENT3||’-‘||GCC3.SEGMENT4||’-‘||GCC3.SEGMENT5||’-‘||GCC3.SEGMENT6||’-‘||GCC3.SEGMENT7||’-‘||GCC3.SEGMENT8 AS RECEIPT_CLEARING_ACCOUNT,
GCC4.SEGMENT1||’-‘||GCC4.SEGMENT2||’-‘||GCC4.SEGMENT3||’-‘||GCC4.SEGMENT4||’-‘||GCC4.SEGMENT5||’-‘||GCC4.SEGMENT6||’-‘||GCC4.SEGMENT7||’-‘||GCC4.SEGMENT8 AS ON_ACCOUNT,
GCC5.SEGMENT1||’-‘||GCC5.SEGMENT2||’-‘||GCC5.SEGMENT3||’-‘||GCC5.SEGMENT4||’-‘||GCC5.SEGMENT5||’-‘||GCC5.SEGMENT6||’-‘||GCC5.SEGMENT7||’-‘||GCC5.SEGMENT8 AS UNAPPLIED_ACCOUNT,
GCC6.SEGMENT1||’-‘||GCC6.SEGMENT2||’-‘||GCC6.SEGMENT3||’-‘||GCC6.SEGMENT4||’-‘||GCC6.SEGMENT5||’-‘||GCC6.SEGMENT6||’-‘||GCC6.SEGMENT7||’-‘||GCC6.SEGMENT8 AS UNIDENTIFIED_ACCOUNT,
GCC7.SEGMENT1||’-‘||GCC7.SEGMENT2||’-‘||GCC7.SEGMENT3||’-‘||GCC7.SEGMENT4||’-‘||GCC7.SEGMENT5||’-‘||GCC7.SEGMENT6||’-‘||GCC7.SEGMENT7||’-‘||GCC7.SEGMENT8 AS BANK_CHARGES_ACCOUNT
FROM AR_RECEIPT_METHODS             METHOD,
AR_RECEIPT_CLASSES             CLASS,
AR_RECEIPT_METHOD_ACCOUNTS_ALL ACCT,
CE_BANK_ACCT_USES_ALL          BNKUSE,
CE_BANK_ACCOUNTS               BNKACCT,
CE_BANK_BRANCHES_V             BNKBRCH,
GL_CODE_COMBINATIONS           GCC1,
GL_CODE_COMBINATIONS           GCC2,
GL_CODE_COMBINATIONS           GCC3,
GL_CODE_COMBINATIONS           GCC4,
GL_CODE_COMBINATIONS           GCC5,
GL_CODE_COMBINATIONS           GCC6,
GL_CODE_COMBINATIONS           GCC7
WHERE METHOD.RECEIPT_CLASS_ID = CLASS.RECEIPT_CLASS_ID
AND ACCT.RECEIPT_METHOD_ID(+) = METHOD.RECEIPT_METHOD_ID
AND ACCT.REMIT_BANK_ACCT_USE_ID  = BNKUSE.BANK_ACCT_USE_ID(+)
AND BNKUSE.BANK_ACCOUNT_ID = BNKACCT.BANK_ACCOUNT_ID(+)
AND BNKBRCH.BANK_PARTY_ID(+) = BNKACCT.BANK_ID
AND BNKBRCH.BRANCH_PARTY_ID(+) = BNKACCT.BANK_BRANCH_ID
AND GCC1.CODE_COMBINATION_ID(+) = ACCT.CASH_CCID
AND GCC2.CODE_COMBINATION_ID(+) = ACCT.REMITTANCE_CCID
AND GCC3.CODE_COMBINATION_ID(+) = ACCT.RECEIPT_CLEARING_CCID
AND GCC4.CODE_COMBINATION_ID(+)= ACCT.ON_ACCOUNT_CCID
AND GCC5.CODE_COMBINATION_ID(+)= ACCT.UNAPPLIED_CCID
AND GCC6.CODE_COMBINATION_ID(+)= ACCT.UNIDENTIFIED_CCID
AND GCC7.CODE_COMBINATION_ID(+)= ACCT.BANK_CHARGES_CCID
and method.name in (‘<ENTER_METHOD_NAME>)