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


Define Period Type and Accounting Calendar in Oracle EBS R12

The below steps need to be performed for setting up Period Type in Oracle EBS /Apps R12

Why we need Adjustment Period ?

Adjustment Period is used to make adjustment to manage the accounting entries for the reconciliation of Financial Periods. The Adjustment Period is not visible in General Ledger.

Calendar Types in Oracle EBS R12 

General Ledger uses the year type to assign a year in the accounting period . In Oracle EBS R12 Calendar can be defined in 2 ways

  • Fiscal: year in which an accounting period begins which means the Financial Year is considered as (01-Jul-20 to 30-Jun-21)
  • Calendar : year in which an accounting period ends which means the Financial Year is considered as (01-Jan-20 to 31-Dec-20)
Define Oracle Apps R12 Period Types
  • Navigate to General Ledger > Setup > Financials > Calendars > Types
  • Enter the Period Type (‘OAH Period’)
  • Enter Period Per Year (13) includes 1 Adjustment Period
  • Select Year Type as ‘Fiscal’

Define Accounting Calendar in Oracle EBS R12

Define Oracle Apps R12 Accounting Calendar

Define the 12 Periods from JAN-DEC for the specific accounting period

  • Enter the Calendar Name (‘OAH Calendar’)
  • Enter the Calendar Description
  • Enter the Period Prefix for the Accounting Period like JAN for Month January
  • Enter the Period Type defined in the Period Type Setup (‘OAH Period’)
  • Enter the Period Year in which Accounting Period to be defined (2021)
  • Enter Total Number of Quarters in an Accounting Period
  • Enter From Date ( ’01-JAN-2020′)
  • Enter To Date ( ’31-JAN-2020′)

Define the Adjustment Period as 13 Period for the specific accounting period

  • Enter the Period Prefix for the Accounting Period like ADJ for Adjustment Period
  • Enter the Period Type defined in the Period Type Setup (‘OAH Period’)
  • Enter the Period Year in which Accounting Period to be defined (2021)
  • Enter Total Number of Quarters in an Accounting Period
  • Enter From Date ( ’31-DEC-2020′)
  • Enter To Date ( ’31-DEC-2020′)

Calendar Validation 

Once the defined periods are saved in Calendar, System popups the Calendar Validation option to validate calendar period data. Select Current as the option

Oracle Apps R12 Calendar Validation

The validations are performed to validate the entered Calendar Period data

  • Validates Periods overlap
  • Validates if Periods have date gaps
  • Validates Adjustment period is checked or not
  • Validates if Period numbers are greater than the maximum period number for current period type
  • Validates if Periods are not in sequential order by date
  • Validates if Quarters are missing
  • Validates if Quarters are not in sequential order by period
  • Validates if Period numbers are missing
  • Validates if Period’s start or end dates are more than one year before or after its fiscal year