R12 AP-GL Reconciliation

How To Reconcile AP-GL
Month End reconciliation between GL and AP is highly recommended. If AP is interfaced to GL, verifying the balance between the two applications is usually done through comparing account balances of the liability (A/P) account.

To reconcile your accounts payable activity for April, make the following calculation:
"Accounts Payable Trial Balance" as of March 31 +
"Posted Invoice Register" for the period between April 1 and April 30 -
"Posted Payment Register" for the period between April 1 and April 30 =
"Accounts Payable Trial Balance" as of April 30

> If total is not matching you will need to find out the root casue of difference:
Eg. Invalidated Invoice for the period
> For the PTD activity check the "period Close Exception" if there are any invoices and payments which are not transferred to GL.
> If the current period does not reconcile, please complete the reconciliation process for all prior periodsfrom the most recent to the earliest until you get to one that reconciles.

Common Reasons for balance mismatch between AP & GL1) Manual journal entries in the general ledger that involve an AP liability account will cause the AP Trialbalance not to reconcile to the GL. These entries are not included in the AP subledger so they will not be reflected on the AP Trial Balance Report.
Run the "GL Account Analysis" report for the liability account and for the date range in question. Look for transactions with a source other than Payables. This can quickly pinpoint any transactions incorrectly charged to the account.

2) You performed a datafix in the past where you used the undo accounting script and swept a transaction forward from a closed period to reaccount it, this will cause an imbalance between AP and GL. The imbalancewill be corrected in the period in which you made a GL adjustment to account for the fix.

3) Any correction you make during the journal import process will result in the line being changed in the general ledger, but not in AP

4) If you have deleted any AP batches or lines from AP batches out of the GL Interface, this will cause AP and GL to be out of balance

5) If the AP batch is still in the GL Interface, it will not be reflected in the GL reports and this will cause a difference between AP and GL.

6) Any AP batches that are unposted in GL will cause a difference between AP and GL

Issue in R12: Accounts Payable Trial Balance Report not showing the latest Data

 How to Rebuild Trial Balance Data (repopulate XLA_TRIAL_BALANCES)
-----------------------------------------------------------------
1. Navigate: Payables Responsibility > Setup > Accounting Setups > Subledger Accounting Setups > Open Account Balances Listing Definitions
2. Query up your Report Definition: select Defined By = Accounting Flexfield or Segment (whichever applies in your case), click “Go”
3. Click on Update icon in the "Actions" column – the definition's update page opens.
4. "Touch" the definition by simply clicking "Apply" button (without making any change).
This should start the “Open Account Balances Data Manager” concurrent processes

11i to R12 Table Changes

Suppliers:
New R12 tables  -> Old 11i Tables

AP_SUPPLIERS - replaces PO_VENDORS

AP_SUPPLIER_SITES_ALL- replaces PO_VENDOR_SITES_ALL

Additional supplier related tables in IBY (Payments) and HZ (TCA):

IBY_EXTERNAL_PAYEES_ALL - stores Payee(supplier) information.

HZ_PARTIES - Party data for the suppliers.

HZ_PARTY_SITES - Party site data for the supplier sites.

Invoices:

Additional table in R12: AP_INVOICE_LINES_ALL

Allocations - AP_CHRG_ALLOCATIONS_ALL is obsolete in R12

Taxes:

Functionality provided by E-Business Tax

New tables in R12

ZX_LINES - Detailed Tax lines for the invoice (trx_id = invoice_id)

ZX_LINES_SUMMARY - Summary tax lines for the invoice (trx_id = invoice_id)

ZX_REC_NREC_DIST  - Tax distributions for the invoice (trx_id = invoice_id)

ZX_LINES_DET_FACTORS - Tax determination factors for the invoice (trx_id = invoice_id)

Payments:

Functionality moved to central Payments (IBY)

New IBY tables in R12:

IBY_PAY_SERVICE_REQUESTS  - Payment Process Request information

Accounting:

Functionality moved to SubLedger Accounting (SLA)

New R12 tables:

XLA_EVENTS -> replaces AP_ACOCUNTING_EVENTS_ALL 

XLA_AE_HEADERS -> replaces AP_AE_HEADERS_ALL

XLA_AE_LINES-> replaces AP_AE_LINES_ALL

XLA_DISTRIBUTION_LINKS

Trial Balance:

New R12 Table

XLA_TRIAL_BALANCES

AP_LIABILITY_BALANCE-> not used in new R12 transactions

AP_TRIAL_BALANCE -> not used in new R12 transactions

Bank Accounts:

Functionality moved to Cash Management.

CE_BANK_ACCOUNTS -> replaces AP_BANK_ACCOUNTS_ALL

CE_BANK_ACCT_USES_ALL  -> replaces AP_BANK_ACCOUNT_USES_ALL

CE_PAYMENT_DOCUMENTS -> AP_CHECK_STOCKS_ALL

Oracle EBS: Linking GL Lines to AP Invoice Distributions in R12

Oracle EBS: Linking GL Lines to AP Invoice Distributions in R12

R11 to R12 Comparision

This blog post gives some guidance as to how to link a GL Journal line in Oracle R12 back to its associated AP Invoice Distribution.

NOTE: That this applies ONLY to R12 data - you will not be able to link old (pre-migration) data using this method you will need to have one linking rule for old records and one for new. Just another reason to do a reimplementation!

As always the easiest way to look at this is with a picture;

The image is click-able for a larger view.

The linking goes as follows;

GL_JE_Lines > XLA_AE_Lines > XLA_Distribution_Links > AP_Invoice_Distributions_All

In our Oracle implementation this didn't end up with a 1:1 relationship (which we had in R11) mainly due to the changes in the way AP invoices are stored (R11: Invoice + Distributions, R12: Invoice + Invoice Lines + Distributions).

Looking at a specific example from our R12 test system for journal “4673291 Purchase Invoices GBP”. This journal contains 5 GL records;

Looking at records in XLA_AE_Lines we also have 5 records;

But when we move on to the next table (XLA_DISTRIBTUION_LINKS);

Technology Changes in R12 from 11i

 Technologies Used:
 OA Framework (Replacing Oracle Forms)
 BI Publisher (XML publisher) (Replacing Oracle Reports and Discoverer)
 BEPL (Replacing Workflow Builder)
 Java, Java Scripting (Replaces PL/SQL)

Existing objects in Oracle Forms, Reports, Discoverer, Workflow Builder and PLSQL are
working as it is but all the new development is done in OA Framework, BI Publisher,
BEPL, Java and Java Scripting etc.

Multi Org Access Control (MOAC) is introduced.

From Release 12 the Multi-Org Access Control feature, will enable users to
access data in one or more Operating Units from a single responsibility. This
feature allows users in a shared services environment to access/transact data
within several operating units from a single responsibility at the same time
restricting access to other users through a security policy. This allows flexibility
and convenience for shared service users. At same time you can prevent access
to data from users who are not authorized to access this information. Before
Release 12, users were limited to access information only from within the context
of their own operating unit from a single responsibility.

Virtual Private Database (VPD) feature introduced will replace usage of
CLIENT_INFO(Org Context) function in Multi-Org Architecture. All calls made to
previous org context function will not work any longer. All such views/synonyms
will need to be accessed only through security profile.

For eg. previously following script could be run for accessing information in
po_headers view.

begin
dbms_application_info.set_client_info('&org_id');
end;
/
select * from ap_invoices;
/
But in R12 the above will not work unless we request access through Security
Policy in the database. MO_GLOBAL package can be used to obtain this access.
This is true with Workflow packages.
Also custom forms and reports need to be initialized to secure access through
security profile.

In your design you need to consider future possibility of the customizations and
extensions being used in a shared services environment. This will mean
additional task for eg. addition of Operating Unit filed to your LOVs or joining your
queries explicitly with org_id when tables or views have Operating Unit sensitive
data.

Subledger accounting concept

1. Set Of Books is replaced by the term Ledger. The column set_of_books_id
has been replaced with ledger_id in Gl Tables

2. Subledger Accounting (SLA) options has been introduced and accounting
entries can be viewed at SLA menu

3. Accounting entries can be created manually at SLA level and can be imported
to GL by doing the journal import

4. As a part of Subledger Accounting, new SLA tables XLA_AE_HEADERS and
XLA_AE_LINES have been introduced.


5. PO details are not available in reference columns(reference_1,reference_2...)
in GL_JE_LINES and GL_BC_PACKETS. Hence accounting entries in these
tables can be retrieved only using SLA tables and GL_IMPORT_REFERENCES
6. Receiving Subledger is not directly linked to GL_JE_LINES,instead it is linked
through the subledger tables XLA_AE_HEADERS and XLA_AE_LINES.
XLA_AE_LINES is mapped to GL_IMPORT_REFERENCES with the
gl_sl_link_id and gl_sl_link_table columns. The linking columns between
GL_IMPORT_REFERENCES and GL_JE_LINES are je_header_id and
je_line_num


Let us take the example of Encumbrance Entries. Below are the differences in
Behavior between 11i and 12i:

1. In 11i, Encumbrance entries created in GL_BC_PACKETS will be directly
moved to GL_JE_LINES by running the CJE program, whereas in 12i,
Encumbrance entries gets generated in a new table PO_BC_DISTRIBUTIONS in
addition to GL_BC_PACKETS. These entries will be moved to GL by running the
program "Transfer Journal Entries to GL" instead of CJE program.

2. In 11i, it is not possible to view the Encumbrance entries in Purchasing
module, where as in 12i, the entries can be viewed from SLA menu in
Purchasing

3. In 11i, a Credit against the Reserve For Encumbrance a/c (RFE) can be seen
only at the time of posting the Encumbrance Debit entry in GL, But in 12i, RFE
a/c gets credited along with the PO Reservation ( budget a/c debit entry) and
these entries can be viewed from SLA menu in Purchasing

Steps to achieve multi-org functionality in R12 for custom tables used in 11i

R12 Multi Org Design For Custom Tables.
Steps: 
1. Create a Table in Custom Schema for multi org data.
Ex: CREATE TABLE XX_TABLENAME_ALL 
( 
num NUMBER, 
name VARCHAR2 (100), 
org_id NUMBER 
); 
Insert some data with different org_id’s

2. Give the Grants to Apps Schema for above created table
GRANT ALL ON XX.XX_TABLENAME_ALL TO APPS 

3. Connect to APPS Schema 
Create a synonym named XX_TABLENAME_ALL in APPS schema, referring to XX.XX_TABLENAME_ALL. 
CREATE OR REPLACE SYNONYM APPS.XX_TABLENAME_ALL FOR PO.XX_TABLENAME_ALL;

Create another synonym named XX_TABLENAME in APPS, referring to XX.XX_TABLENAME_ALL.
CREATE OR REPLACE SYNONYM APPS.XX_TABLENAME FOR PO.XX_TABLENAME_ALL;

4. Now we can apply Row Level security to XX_TABLENAME, using function MO_GLOBAL.ORG_SECURITY.

Oracle RLS will dynamically append WHERE CLAUSE similar to below
SELECT * FROM XX_TABLENAME WHERE EXISTS (SELECT 1 FROM mo_glob_org_access_tmp oa WHERE oa.organization_id = org_id) 

For Applying RLS we can add policy for XX_TABLENAME synonym by using DBMS_RLS.ADD_POLICY API.
Example for add Policy with ADD_POLICY API
BEGIN
dbms_rls.add_policy ('apps', -- Schema Name
'XX_TABLENAME', -- synonym name
'ORG_SEC',-- use policy_name 'ORG_SEC' –standard policy
'apps', --function_schem
'MO_GLOBAL.ORG_SECURITY',--Standard MO VPD policy fun
'SELECT, INSERT, UPDATE, DELETE', -- statement_type
TRUE, -- update_check
TRUE -- Policy enable
);
END;

After creating policy for the synonym we can get data only for which org_id initialized in the back end by using 
MO_GLOBAL.SET_POLICY_CONTEXT('S',’Org_id’);
Example for Testing:
Run the query
SELECT * FROM XX_TABLENAME
(The query returns no data)
Initialize the org_id
BEGIN
MO_GLOBAL.SET_POLICY_CONTEXT('S',12);
END;

Rerun the query
You will get the data for org_id 12 only.