General Ledger ( GL) differences in EBS 11i and EBS R12

The blog provides the major changes in General Ledger ( GL) differences in 11i and R12

General Ledger EBS R11i vs EBS R12

Ledger: The ledger is a major concept change in Release 12 which replaces the 11i concept of a set of books. the Ledger represents an accounting representation for one or more legal entities or for a business need such as consolidation or management reporting. The new change provides the flexibility for the defining the model in a easy and efficient way for the setup of the legal entities and accounting representations in Release 12. It provides the shared service center and single instance initiatives where all legal entities of an enterprise are accounted for in a single instance, and data, setup, and processing must be effectively secured but also possibly shared.

While a set of books in 11I is defined by 3Cs

Chart of accounts
Functional currency
Accounting calendar

R12 has been added with 4th C as Accounting Method

Chart of accounts
Functional currency
Accounting calendar
Accounting Method

Accounting Setup Manager:

Accounting Setup Manager is a new feature that streamlines the setup and implementation of Oracle Financial Applications and provides the following common setup components:

Legal Entities
Ledgers, primary and secondary
Operating Units, which are assigned to primary ledgers
Reporting Currencies
Subledger Accounting Options. Creation of Accounting Methods sub-module level and associating it to the ledger where the accounting is stored.
Intercompany Accounts and Balancing Rules
Accounting and Reporting Sequencing

Definition Access Set: Definition Access Sets are an optional security feature that allows to create a secure shared General Ledger definitions . Definition Access Sets allow to assign a user or group of users access to specific definitions like change in Setup , Submission of Reports and View of Reports .Few of them are listed below which are commonly used:

MassAllocations,
Recurring Journal Formulas,
Financial Statement Generator (FSG) components

Subledger Accounting (SLA): SLA concept is introduced in R12 , which is a Rule-based accounting engine, toolset & repository which is supporting most of Oracle business Suite modules. SLA acts as an intermediate step between subledger products and the Oracle General Ledger. Journal entries are created in Subledger Accounting and then transferred to Oracle General Ledger . Every single subledger transaction ( like AP, AR, FA , PA) that requires accounting is represented by a complete and balanced subledger journal entry stored in a common data model.

Commonly used features of SLA in R12:

Journal Entry Setup and sequencing
Subledger Accounting Definition for each sub – module
Multiple Accounting Representation
Multi-period Accounting
Feature for providing Draft and online accounting
Replacement for disabled accounts
Category based Accounting
Accrual Reversal Accounting
Provides Accounted and Gain/Loss Amount calculations
Application Accounting Definition Loader
Enhanced Reporting Currency Functionality

AutoReversal Criteria Setup: Oracle Apps R12 allows to setup the AutoReversal Criteria Sets which can be shared across ledgers to reverse journals across multiple ledgers , enhanced by integrated Web-based Spreadsheet Interface.

Automatic Posting: Oracle Apps R12 allows to have the common AutoPost Criteria sets across multiple ledgers that share the same chart of accounts and calendar and use the AutoPost Criteria sets to post journals across multiple ledgers simultaneously.

Creation of foreign currency recurring journals: 11i provides the support for creating / defining the recurring journals in the functional currency or STAT Currency where as in Oracle Apps R12 , user can create recurring journals using foreign currencies.

Currency Translation of Multiple Ledgers : Oracle Apps R12 provides the flexibility to run the Translation program for multiple ledgers simultaneously

Financial Reporting for Multiple Ledgers: Oracle Apps R12 provides the flexibility to run Financial Statement Generator (FSG) reports for multiple ledgers simultaneously which helps in managing multiple ledgers when you need to run a balance sheet or income statement report for all of your ledgers at the same time

Cross-Ledger and Foreign Currency Allocations : Oracle Apps R12 allows to allocate financial data from one or more ledgers to a different target ledger and enables to perform cross-ledger allocations, which is useful for purposes such as allocating corporate or regional expenses to local subsidiaries when each entity has its own ledger

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