Account Payables differences in EBS R11i and EBS R12

Please find below the major differences in 11i and R12 Account Payables

Type Description Available in R12 Available in 11i

Supplier	Supplier Details as in HTML Form	Yes	No
Supplier	Enable with MOAC Access	Yes	No
Supplier	Supplier as a TCA Party	Yes	No
Supplier	Supplier Setup Definition in AP .	No	Yes
Supplier	Usage of Supplier Site as a TCA Party Site for each distinct Address	Yes	No
Supplier	Supplier Contact copy – Each Supplier Site	No	Yes

Type Description Available in R12 Available in 11i

Payment	Payment Process Profile	Yes	No
Payment	Operating Unit at Header	Yes	No
Payment	Payment manager	Yes	Payment Batch

Type Description Available in R12 Available in 11i

Payable Option	Accounting,Method,Transfer to GL	No	Yes
Payable system set up	Enablement of Supplier numbering	Yes	At financial option

Type Description Available in R12 Available in 11i

Invoice	Operating Unit at Header	Yes	No
Invoice	Invoice Lines as a new additional line added in Invoice screen.	Yes	No
Invoice	All distribution option	Yes	No
Invoice	DFF at Header	Yes	No
Invoice	DFF at Distribution	Yes	At Invoice Header
Invoice	Allocate freight and special charges are define to the lines on the invoice	Yes	No
Invoice	Allocation of freight and special charges are define at the distribution level only	No	Yes
Invoice	Match Action come to Header	Header level	Near to Match
Invoice	Tax Details Option	Yes	No
Invoice	Tax Calculation Option	Yes	No
Invoice	Create Accounting	Draft,Final,Final Post	only
Invoice	Multi Organization Access Control (MOAC)	Yes	No
Invoice	Sub ledger Accounting (SLA)	Yes	No

Oracle apps R12 Query to get the Credit Memo Balance Due

The below given query provides the SQL Data extract for Credit Memo Balance Due.

SELECT HZ_ACCT.ACCOUNT_NUMBER, NVL (SUM (AR_PAY_SCH.AMOUNT_DUE_REMAINING), 0) CM_BALANCE_DUE
FROM
HZ_CUST_ACCOUNTS_ALL HZ_ACCT,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_CUST_SITE_USES_ALL HCSU,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS LOC,
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUST_TRX_LINE_GL_DIST_ALL RA_CUS_TRX_GL_DIST,
AR_PAYMENT_SCHEDULES_ALL AR_PAY_SCH,
GL_CODE_COMBINATIONS CC
WHERE 1=1
AND AR_PAY_SCH.CUSTOMER_ID = HZ_ACCT.CUST_ACCOUNT_ID
AND AR_PAY_SCH.CUST_TRX_TYPE_ID = RCTA.CUST_TRX_TYPE_ID
AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND LOC.LOCATION_ID = HPS.LOCATION_ID
AND HZ_ACCT.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
AND HCASA.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND AR_PAY_SCH.CUSTOMER_ID = HCASA.CUST_ACCOUNT_ID
AND AR_PAY_SCH.CUSTOMER_SITE_USE_ID = HCSU.SITE_USE_ID
AND RCTA.CUSTOMER_TRX_ID = AR_PAY_SCH.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID = RA_CUS_TRX_GL_DIST.CUSTOMER_TRX_ID
AND RA_CUS_TRX_GL_DIST.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID
AND RA_CUS_TRX_GL_DIST.ACCOUNT_CLASS = 'REV'
AND NVL (HCSU.STATUS, 'A') = 'A' 
AND AR_PAY_SCH.CLASS = 'CM'
AND AR_PAY_SCH.status = 'OP'
AND HCSU.SITE_USE_CODE = 'BILL_TO'
AND TRUNC (AR_PAY_SCH.GL_DATE) <= :P_ENTER_DATE
AND AR_PAY_SCH.TRX_DATE <= :P_ENTER_DATE
AND HZ_ACCT.ACCOUNT_NUMBER = :P_ENTER_ACCT_NUMBER
GROUP BY HZ_ACCT.ACCOUNT_NUMBER,AR_PAY_SCH.AMOUNT_DUE_REMAINING

Oracle EBS R12 SQL to get Open Invoices Customer Transaction Balances

The blog provides the Oracle EBS R12 SQL to check Customer Transaction Balances. The Oracle EBS R12 Account Receivable AR Invoices with Outstanding Customer Transaction Balances, AR Invoices which Over Due date are retrieved for AR Reconciliation

The below given SQL Query provides the data extract to check Customer Transaction Balances

SELECT HZ_ACCT.ACCOUNT_NUMBER, NVL (SUM (AR_PAY_SCH.AMOUNT_DUE_REMAINING), 0) INVOICE_BALANCE
FROM
HZ_CUST_ACCOUNTS_ALL HZ_ACCT,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_CUST_SITE_USES_ALL HCSU,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS LOC,
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUST_TRX_LINE_GL_DIST_ALL RA_CUS_TRX_GL_DIST,
AR_PAYMENT_SCHEDULES_ALL AR_PAY_SCH,
GL_CODE_COMBINATIONS CC
WHERE 1=1
AND AR_PAY_SCH.CUSTOMER_ID = HZ_ACCT.CUST_ACCOUNT_ID
AND AR_PAY_SCH.CUST_TRX_TYPE_ID = RCTA.CUST_TRX_TYPE_ID
AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND LOC.LOCATION_ID = HPS.LOCATION_ID
AND HZ_ACCT.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
AND HCASA.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND AR_PAY_SCH.CUSTOMER_ID = HCASA.CUST_ACCOUNT_ID
AND AR_PAY_SCH.CUSTOMER_SITE_USE_ID = HCSU.SITE_USE_ID
AND RCTA.CUSTOMER_TRX_ID = AR_PAY_SCH.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID = RA_CUS_TRX_GL_DIST.CUSTOMER_TRX_ID
AND RA_CUS_TRX_GL_DIST.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID
AND RA_CUS_TRX_GL_DIST.ACCOUNT_CLASS = 'REV'
AND NVL (HCSU.STATUS, 'A') = 'A'
AND AR_PAY_SCH.CLASS NOT IN ('CM', 'PMT')
AND HCSU.SITE_USE_CODE = 'BILL_TO'
AND TRUNC (AR_PAY_SCH.GL_DATE) <= :P_ENTER_DATE
AND AR_PAY_SCH.TRX_DATE <= :P_ENTER_DATE
AND HZ_ACCT.ACCOUNT_NUMBER = :P_ENTER_ACCT_NUMBER
GROUP BY HZ_ACCT.ACCOUNT_NUMBER,AR_PAY_SCH.AMOUNT_DUE_REMAINING

Oracle EBS R12 AR SQL to get Open Invoices with Customer payment status is Open

Hi All,

The below given SQL Query for Oracle Apps Receivables (AR) provides the main columns from the table – RA_CUSTOMER_TRX_ALL and AR_PAYMENT_SCHEDULES_ALL to get the details for the Open Invoices for a particular Customer ( enter TRX_NUMBER) or for all Customers.

SELECT
RA_CUST_TRX.STATUS_TRX,
RA_CUST_TRX.TRX_NUMBER,
RA_CUST_TRX.TRX_DATE,
RA_CUST_TRX.BILLING_DATE,
RA_CUST_TRX.BATCH_ID,
RA_CUST_TRX.BATCH_SOURCE_ID,
RA_TERMS.NAME || '-' || RA_TERMS.DESCRIPTION   TERM_NAME,
RA_CUST_TRX.INVOICE_CURRENCY_CODE TRX_CURR,
APS.ORG_ID,
APS.CUSTOMER_ID,
APS.CUSTOMER_TRX_ID,
--APS.TRX_NUMBER,
--APS.TRX_DATE,
APS.GL_DATE,
APS.CLASS,
APS.GL_DATE_CLOSED,
APS.STATUS PAYMENT_STATUS,
APS.INVOICE_CURRENCY_CODE,
APS.EXCHANGE_DATE,
APS.EXCHANGE_RATE_TYPE,
APS.EXCHANGE_RATE,
APS.DUE_DATE,
APS.AMOUNT_DUE_ORIGINAL,
APS.AMOUNT_DUE_REMAINING,
APS.AMOUNT_LINE_ITEMS_REMAINING,
APS.AMOUNT_APPLIED,
APS.NUMBER_OF_DUE_DATES
FROM
HZ_CUST_ACCOUNTS HZ_ACCT,
HZ_PARTIES HP,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_CUST_SITE_USES_ALL HCSUA,
HZ_PARTY_SITES HPS_BILL,
RA_CUST_TRX_LINE_GL_DIST_ALL RCT,
RA_TERMS RA_TERMS,
RA_CUSTOMER_TRX_ALL RA_CUST_TRX,
RA_CUSTOMER_TRX_LINES_ALL RA_TRX_LINES,
RA_CUST_TRX_TYPES_ALL RT,
AR_PAYMENT_SCHEDULES_ALL APS
WHERE 1 = 1
AND RA_CUST_TRX.CUSTOMER_TRX_ID = RA_TRX_LINES.CUSTOMER_TRX_ID
AND RA_CUST_TRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND RA_CUST_TRX.CUST_TRX_TYPE_ID = RT.CUST_TRX_TYPE_ID
AND RA_CUST_TRX.BILL_TO_CUSTOMER_ID = HZ_ACCT.CUST_ACCOUNT_ID
AND RA_CUST_TRX.ORG_ID = APS.ORG_ID
AND RA_CUST_TRX.TERM_ID = RA_TERMS.TERM_ID
AND RCT.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND RCT.CUSTOMER_TRX_ID = RA_CUST_TRX.CUSTOMER_TRX_ID
AND RCT.CUSTOMER_TRX_ID = RA_TRX_LINES.CUSTOMER_TRX_ID
AND RCT.CUSTOMER_TRX_LINE_ID = RA_TRX_LINES.CUSTOMER_TRX_LINE_ID
AND HP.PARTY_ID = HZ_ACCT.PARTY_ID
AND HCASA.CUST_ACCOUNT_ID = RA_CUST_TRX.BILL_TO_CUSTOMER_ID
AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
AND HCSUA.SITE_USE_ID = RA_CUST_TRX.BILL_TO_SITE_USE_ID
AND HPS_BILL.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
AND APS.AMOUNT_DUE_REMAINING <> 0
AND APS.STATUS = 'OP'
AND HCSUA.SITE_USE_CODE = 'BILL_TO'
AND HZ_ACCT.STATUS = 'A'
AND HCASA.STATUS = 'A'
AND HCSUA.STATUS = 'A'
AND RA_CUST_TRX.COMPLETE_FLAG = 'Y'
AND RA_TRX_LINES.LINE_TYPE IN ('FREIGHT', 'LINE')
--AND HZ_ACCT.ACCOUNT_NUMBER = :P_ENTER_CUSTOMER_NUMBER
ORDER BY RA_CUST_TRX.TRX_NUMBER DESC,
RA_CUST_TRX.TRX_DATE DESC

Oracle EBS R12 AR transaction processing

The below given are the common AR setup to be done in Oracle Apps – Oracle Receivables for the transaction processing.

Oracle Receivable Setup Description	Required ?
Define Sets Of Books	Yes
Use The Account Generator	Yes
Define System Item Flexfields Structure	Yes
Define Organizations	Yes
Define Territory Flexfield	No
Define Sales Tax Location Flexfields structure	Yes
Setup Flexible Address Formats	No
Maintain Countries & Territories	No
Define the Transaction Flexfield Structure	No
Define AutoCash Rule Sets	No
Define QuickCodes	No
Define AutoInvoice Line Ordering Rules	No
Define AutoInvoice Grouping Rules	No
Define System Options	Yes
Define Payment Terms	Yes
Define Accounting Rules	No
Maintain Accounting Periods	Yes
Define AutoAccounting	Yes
SetUpCash Basis Accounting	No
Define Transaction Types	Yes
Define Transaction Sources	Yes
Define Collectors	Yes
Define Adjustments Approval Limits	Yes
Define remittance Banks	Yes
Define distribution sets	No
Define receivable activities	Yes
Define receipts classes	Yes
Define Payment Methods	Yes
Define Receipts Sources	Yes
Define aging buckets	No
Define Statement cycles	No
Define Statement messages	No
Define dunning letters	No
Define dunning letters Sets	No
Define Territories	No
Define Sales persons	Yes
Define Profile options	Yes
Define tax codes and rate	Yes
Define customer profile classes	Yes
Define Customer	Yes
Define remit to address	Yes
Define customer relationship	No
Define Customer banks	No
Define Lock Boxes	No
Define the transmission formats	No
Define receipt programmes	No
Define UOM Classes	No
Define UOM	Yes
Define Standard memo lines	No
Define Item tax rate exceptions	No
Define tax exemptions	No
Define document sequences	No

Oracle EBS R12 SQL to get General Ledger Posting for AR Invoices

The blog provides the Oracle EBS R12 SQL to get AR Invoice posted to GL. If the Account Receivables Transactions are not posted to General ledger , then it leads to the Reconciliation Issues during the Month End Period Closure.

Verify General Ledger Posting for AR Invoices

SELECT
RA_CUST_TRX.STATUS_TRX,
GL.SHORT_NAME,
RA_CUST_TRX.INVOICE_CURRENCY_CODE TRX_CURR,
RA_CUST_TRX.TRX_NUMBER,
RA_CUST_TRX.TRX_DATE,
RA_CUST_TRX.BILLING_DATE,
GJH.NAME GL_JE_HEADER_NAME,
GJH.DESCRIPTION GL_HDR_DESC,
GJH.JE_BATCH_ID,
GJL.JE_LINE_NUM,
RA_CUST_TRX.CUST_TRX_TYPE_ID,
RA_CUST_TRX.BILL_TO_CUSTOMER_ID,
RA_CUST_TRX.BATCH_ID,
RA_CUST_TRX.BATCH_SOURCE_ID,
RA_CUST_TRX.TERM_ID,
RA_CUST_TRX_DIST.AMOUNT AR_INV_DIST_AMOUNT,
GJL.CODE_COMBINATION_ID GL_JE_LINE_CCID,
RA_CUST_TRX_DIST.CODE_COMBINATION_ID AR_INV_DIST_CCID,
GCC.SEGMENT1
|| '.'
|| GCC.SEGMENT2
|| '.'
|| GCC.SEGMENT3
|| '.'
|| GCC.SEGMENT4
|| '.'
|| GCC.SEGMENT5
|| '.'
|| GCC.SEGMENT6
|| '.'
|| GCC.SEGMENT7
|| '.'
|| GCC.SEGMENT8
AR_INV_DIST_CODE_COMBINATION
FROM AR.RA_CUSTOMER_TRX_ALL RA_CUST_TRX,
AR.RA_CUST_TRX_LINE_GL_DIST_ALL RA_CUST_TRX_DIST,
XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_AE_HEADERS XAH,
XLA.XLA_AE_LINES XAL,
XLA.XLA_DISTRIBUTION_LINKS XDL,
GL.GL_JE_HEADERS GJH,
GL.GL_JE_LINES GJL,
GL.GL_LEDGERS GL,
GL.GL_CODE_COMBINATIONS GCC,
GL.GL_IMPORT_REFERENCES GL_IMP_REF
WHERE 1 = 1
AND XTE.ENTITY_CODE = 'TRANSACTIONS'
AND XAL.ACCOUNTING_CLASS_CODE = 'REVENUE'
AND RA_CUST_TRX.CUSTOMER_TRX_ID = RA_CUST_TRX_DIST.CUSTOMER_TRX_ID
AND XTE.SOURCE_ID_INT_1 = RA_CUST_TRX.CUSTOMER_TRX_ID
AND GJH.JE_HEADER_ID = GL_IMP_REF.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GL_IMP_REF.JE_LINE_NUM
AND GCC.CODE_COMBINATION_ID = RA_CUST_TRX_DIST.CODE_COMBINATION_ID
AND GL.CHART_OF_ACCOUNTS_ID = GCC.CHART_OF_ACCOUNTS_ID
AND XTE.LEDGER_ID = GL.LEDGER_ID
AND XTE.APPLICATION_ID = 222
AND XAH.ENTITY_ID = XTE.ENTITY_ID
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = RA_CUST_TRX_DIST.CUST_TRX_LINE_GL_DIST_ID
AND XDL.APPLICATION_ID = 222
AND GL_IMP_REF.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
-- AND RA_CUST_TRX.TRX_NUMBER = :P_ENTER_TRX_NUMBER
-- AND TRX.CUSTOMER_TRX_ID = :P_ENTER_CUSTOMER_TRX_ID
-- AND TRX.BATCH_SOURCE_ID = :P_BATCH_SOURCE_ID

Oracle EBS R12 SQL get AP cancelled invoices with distribution lines

The blog provides the Oracle EBS R12 SQL get AP cancelled invoices with distribution lines

SELECT AIA.ORG_ID,AIA.INVOICE_ID, AIA.INVOICE_AMOUNT, AIA.GL_DATE, AIA.INVOICE_DATE, NVL(SUM(AIDA.AMOUNT),0) AMOUNT
FROM AP_INVOICES_ALL AIA,AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE 1=1
--AND AIA.org_id = <ORG_ID_VALUE>
AND AIA.INVOICE_ID = AIDA.INVOICE_ID
AND AIDA.LINE_TYPE_LOOKUP_CODE NOT IN ('PREPAY')
AND AIA.CANCELLED_DATE IS NOT NULL
AND ROWNUM <100
GROUP BY AIA.ORG_ID,AIA.INVOICE_ID, AIA.INVOICE_AMOUNT ,AIA.GL_DATE, AIA.INVOICE_DATE
HAVING (AIA.INVOICE_AMOUNT <> NVL(SUM(AIDA.AMOUNT),0))
ORDER BY AIA.INVOICE_ID ASC

SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE  AIDA.INVOICE_ID = <INVOICE_ID>

Oracle EBS R12 SQL to get AP invoices posted to GL

The blog provides the Oracle EBS R12 SQL to get AP invoices posted to General Ledger (GL). The Account Payables (AP) Invoices should be validated and Posted for AP reconciliation during month end period closure. Few are the validations given below which are being used to ensure that the Invoice has been accounted with approved Status and Posting Status.

Oracle EBS R12 AP invoice Status

There are 4 invoice status as given below
‘S’ – Selected
‘P’ – Partial
‘N’ – Unposted
‘Y’ – Posted

Check and ensure that the Invoice Id entry should be in the table – AP_INVOICE_DISTRIBUTIONS_ALL
Check and ensure that the Invoice Id entry should be in the table – AP_SELF_ASSESSED_TAX_DIST_ALL
Check if DIST VAR hold is placed on this invoice. DIST VAR Hold could be placed when no distributions exist and the invoice status should be NEEDS REAPPROVAL
Check and ensure that there is NO Hold applied on the Invoice
Check if the Invoice is NOT Cancelled
Check Table – AP_INVOICE_DISTRIBUTIONS_ALL for the Invoice ID match_status_flag IS NOT NULL and count should be 0

SQL to get AP Invoices Validated but Not Posted to General Ledger (GL)

SELECT INVOICE_NUM
FROM AP_INVOICES_ALL
WHERE AP_INVOICES_PKG.GET_APPROVAL_STATUS
(INVOICE_ID,
INVOICE_AMOUNT,
PAYMENT_STATUS_FLAG,
INVOICE_TYPE_LOOKUP_CODE
) ='APPROVED'
AND AP_INVOICES_PKG.GET_POSTING_STATUS(INVOICE_ID)='N

SQL Query to get AP Invoices Validated & Posted to General Ledger (GL)

SELECT INVOICE_NUM
FROM AP_INVOICES_ALL
WHERE AP_INVOICES_PKG.GET_APPROVAL_STATUS
(INVOICE_ID,
INVOICE_AMOUNT,
PAYMENT_STATUS_FLAG,
INVOICE_TYPE_LOOKUP_CODE
) ='APPROVED'
AND AP_INVOICES_PKG.GET_POSTING_STATUS(INVOICE_ID)='P'

Oracle EBS R12 SQL to get AP Invoice Status

The blog provides the Oracle EBS R12 SQL to get AP Invoice Status. The AP Invoices are required to be validated , processed and closed.

The below given SQL Query can be used to retrieve AP Invoice data for different Invoice Status – ‘ APPROVED ‘, ‘ UNAPPROVED ‘, ‘ CANCELLED ‘ in the Account Payables.

SELECT DISTINCT
AIA.INVOICE_ID           " Invoice Id",
AIA.INVOICE_NUM          " Invoice Number" ,
AIA.INVOICE_DATE         " Invoice Date",
AIA.CREATION_DATE        " Invoice Creation Date",
AIA.INVOICE_AMOUNT       " Invoice Amount" ,
ALC4.LOOKUP_TYPE         " Invoice Lookup Type",
ALC4.LOOKUP_CODE         " Invoice Lookup Code",
AIA.WFAPPROVAL_STATUS    " Approval Status ",
AIA.GL_DATE              " GL Date ",
AIA.ORG_ID               " ORG ID ",
AIA.SOURCE               " Invoice Source"
FROM
AP_INVOICES_ALL AIA,
AP_LOOKUP_CODES ALC4
WHERE
ALC4.LOOKUP_TYPE = ('NLS TRANSLATION')
AND ALC4.LOOKUP_CODE IN('APPROVED','UNAPPROVED','CANCELLED')
AND AIA.INVOICE_ID IN (478094)
ORDER BY AIA.CREATION_DATE DESC

Oracle EBS R12 Account Receivables (AR) Receipt Creation

The blog provides the steps to Create Oracle EBS R12 Account Receivables (AR) Receipt.

Oracle EBS R12 Account Receivables (AR) Receipt Types

In Oracle Receivables, Receipts can be created of 2 types:

Standard Receipts: Standard Receipts includes Cash or Check received from the Customer in the exchange of goods or services provided. These are termed as ‘Cash Receipts’ also.
Miscellaneous Receipts: Miscellaneous Receipts includes the revenue earned from the interest, refunds, investments or any other nonstandard item.

Points to Consider for Oracle EBS R12 AR Receipt Creation

Receipts can be applied to the transactions in the Open or Future Accounting Periods.
Charge back and adjustments can be created against the receipts
Receipts can be applied to Invoices, Debit Memos, Credit Memos, on-account credits and chargebacks.
Receipts can be applied partially or fully to a single Debit item or to several Debit items.
Receipts can be applied to other open receipts

Oracle EBS R12 AR Receipt Status

The Oracle EBS R12 provides the different Account Receivables (AR) Receipt status to retrieve the current state of the created AR Receipts and its reconciliation.

The Oracle Apps AR Receipt Status can be referred here Oracle Apps R12 AR Receipt Status

Oracle EBS R12 Journal Import Validation Process

The Oracle Apps Journal Import validates the accounting data received from other systems in the GL_INTERFACE Table. The list of journal error lines will be published in the Journal Import Execution Report.

Batch Level Validation:

Journal Import validates the below attributes so that the batch name available for processing should not already exist for the same Ledger Id Period in the Oracle Apps General Ledger application for the particular Period

Ledger Id
Period Name
Batch Name

Journal Level Validation:

Journal Import validates the below attributes so that the journals available for processing should have the required accounting data

Ledger Id
Period Name
Journal Source Name
Journal Category Name
Currency Code
Encumbrance type ID
Actual Flag
User Conversion Type
Budget Version Id
Accounting Date

Journal Entry Line Level Validation:

Journal Import validates the below attributes so that the journals available for processing should have the required accounting data

Account Validation ( Code Combination) :

Code Combinations will be considered valid and journal import will be processed successfully if the below validations exists:

If detail posting to segment combinations are allowed
If code combinations are enabled for the specified accounting date
If code combinations does not include summary accounts

Effective Date Validation:

Journal Import validates if the Accounting Date is a valid business day and thus General Ledger considers it as the transaction’s effective date. Below are the Effective Date Rule defined in oracle apps for General Ledger Accounting Date:

Fail: Journal Import will Reject the transactions
Leave Alone: Journal Import will consider transactions regardless of the Accounting Date
Roll Date: Journal Import will roll the Accounting Date to the nearest valid business day

Descriptive Flexfield Validation:

Journal Import validates the descriptive flexfield segments depending on the particular descriptive flexfield.

Journals – Journal Entry Line Descriptive Flexfield
- The descriptive flexfield global segments have valid values

Journals – Captured Information Descriptive Flexfield
- The descriptive flexfield context dependent segments , descriptive flexfield context and descriptive flexfield context dependent segments have valid values
Value Added Tax Descriptive Flexfield
- The descriptive flexfield context is set to Yes or No and descriptive flexfield context dependent segments have valid values

USSGL Transaction Code:

In case of Public Sector General Ledger, Journal Import validates the USSGL Transaction Code

Oracle EBS R12 General ledger interface tables

The Oracle apps General Ledger Interface Table – GL_INTERFACE is the entry point for all journals accounting data which have been imported from other systems. The Journal Import Program validates the accounting data received in the GL_INTERFACE Table and imports the available data into Journal entries in the Oracle Apps GL Application.

The Journal Import Program uses the GL_INTERFACE Table Column data to store the accounting data into the Journal defined categories.

Below given are few of the main columns for which Journal Import uses during processing

Column Name	Column Description
STATUS	Value could be ‘P’ – Posted , ‘U’ – Unposted or ERROR Number when returns Error while processing
LEDGER_ID	Ledger ID FROM gl_ledgers
USER_JE_SOURCE_NAME	Provide the Source for which Journal have been received ( Few Sources could be – REMEASUREMENT, Receivables, Global Intercompany, Cost Management, TRANSLATION, Spreadsheet, Payables, Global Intercompany RVRSE )
USER_JE_CATEGORY_NAME	Provide the Category for which Journal have been received ( Few categories could be – Travel , Inventory, Purchase Invoices, Reverse, AX Receivables, Payroll, Receiving, REMEASUREMENT, Statisticals, Sales Invoices )
ACCOUNTING_DATE	Date when journal entries have been accounted in Oracle Apps GL Application
CURRENCY_CODE	Journal Currency
ACCOUNTED_DR	Accounted Debit Amount for journal
ACCOUNTED_CR	Accounted Credit Amount for journal
JE_BATCH_ID	Journal Batch ID
GROUP_ID	Journal Group ID

Interface Table Column Name	Null?	Date Type
STATUS	NOT NULL	VARCHAR2 (50)
LEDGER_ID	NOT NULL	NUMBER (15)
USER_JE_SOURCE_NAME	NOT NULL	VARCHAR2 (25)
USER_JE_CATEGORY_NAME	NOT NULL	VARCHAR2 (25)
ACCOUNTING_DATE	NOT NULL	DATE
CURRENCY_CODE	NOT NULL	VARCHAR2 (15)
DATE_CREATED	NOT NULL	DATE
CREATED_BY	NOT NULL	NUMBER (15)
ACTUAL_FLAG	NOT NULL	VARCHAR2 (1)
ENCUMBRANCE_TYPE_ID		NUMBER
BUDGET_VERSION_ID		NUMBER
CURRENCY_ CONVERSION_DATE		DATE
USER_CURRENCY_ CONVERSION_TYPE		VARCHAR2 (30)
CURRENCY_ CONVERSION_RATE		NUMBER
SEGMENT1 through SEGMENT30		VARCHAR (25)
ENTERED_DR		NUMBER
ENTERED_CR		NUMBER
ACCOUNTED_DR		NUMBER
ACCOUNTED_CR		NUMBER
TRANSACTION_DATE		DATE
REFERENCE1		VARCHAR2 (100)
REFERENCE2		VARCHAR2 (240)
REFERENCE3		VARCHAR2 (100)
REFERENCE4		VARCHAR2 (100)
REFERENCE5		VARCHAR2 (240)
REFERENCE6 through REFERENCE9		VARCHAR2 (100)
REFERENCE10		VARCHAR2 (240)
REFERENCE11 through REFERENCE20		VARCHAR2 (100)
REFERENCE21 through REFERENCE30		VARCHAR2 (240)
GROUP_ID		NUMBER (15)
JE_BATCH_ID		NUMBER (15)
PERIOD_NAME		VARCHAR2 (15)
JE_HEADER_ID		NUMBER (15)
JE_LINE_NUM		NUMBER (15)
CHART_OF_ACCOUNTS_ID		NUMBER (15)
FUNCTIONAL_ CURRENCY_CODE		VARCHAR2 (15)
CODE_COMBINATION_ID		NUMBER (15)
DATE_CREATED_IN_GL		DATE
WARNING_CODE		VARCHAR2 (4)
STATUS_DESCRIPTION		VARCHAR2 (240)
DESCR_FLEX_ERROR_ MESSAGE		VARCHAR2 (240)
STAT_AMOUNT		NUMBER
REQUEST_ID		NUMBER (15)
SUBLEDGER_DOC_ SEQUENCE_ID		NUMBER
SUBLEDGER_DOC_ SEQUENCE_VALUE		NUMBER
USSGL_TRANSACTION_CODE		VARCHAR2 (30)
ATTRIBUTE1 through ATTRIBUTE20		VARCHAR2 (150)
CONTEXT , CONTEXT2, CONTEXT3		VARCHAR2 (150)
INVOICE_DATE		DATE
INVOICE_AMOUNT		NUMBER
INVOICE_IDENTIFIER		VARCHAR2 (20)
TAX_CODE		VARCHAR2 (15)

oracleappshelp.com

Category: Oracle Apps

Account Payables differences in EBS R11i and EBS R12

Oracle apps R12 Query to get the Credit Memo Balance Due

Oracle EBS R12 SQL to get Open Invoices Customer Transaction Balances

Oracle EBS R12 AR SQL to get Open Invoices with Customer payment status is Open

Oracle EBS R12 AR transaction processing

Oracle EBS R12 SQL to get General Ledger Posting for AR Invoices

Oracle EBS R12 SQL get AP cancelled invoices with distribution lines

Oracle EBS R12 SQL to get AP invoices posted to GL

Oracle EBS R12 SQL to get AP Invoice Status

Oracle EBS R12 Account Receivables (AR) Receipt Creation

Oracle EBS R12 Journal Import Validation Process

Batch Level Validation:

Journal Level Validation:

Journal Entry Line Level Validation:

Descriptive Flexfield Validation:

USSGL Transaction Code:

Oracle EBS R12 General ledger interface tables