A guide to Inventory Period Closing activities and Inventory-SLA-GL Reconciliation

This white paper aims at providing troubleshooting guide for the Inventory period closing activities and Gl reconciliation issues which is faced after the Inventory period is closed.

Intention of the white paper is to provide the effective guidelines and some handful scripts which can be used in checking various aspects of period closing as well as Reconciliation between sub-ledger and General Ledger (GL).

Following Pre-checks are important before attempting to reconcile the value at Sub-ledger for a given account code combination with the value of that account code combination reflecting in GL.

The scripts mentioned are not applicable for Periodic Costing System (PAC).

This is applicable for R12 using Standard and/or Average Costing.

Suitable for Oracle seeded Account Derivation Rules (ADR).

NOTE : The script contained in the attachment section is being updated periodically. Please download it from Metalink every time you need to run it in order to have the latest version.

SUMMARY

 1. All Material/WIP transactions should be costed.

This is the primary step in the process of Inventory Period close and ultimately in Reconciliation. If there are any errors in the respective tables or Cost worker errors those need to be resolved. To check for these errors, diagnostics scripts are available,

Please refer to the  Note 603657.1 -Error/Uncosted/Pending Material Transactions

2. Create Accounting-Cost Management request.

After checking all the transactions for the concerned Inventory period are costed, Create Accounting-Cost Management concurrent request should be run to transfer all the transactions to GL.

This concurrent request should get successfully completed.

For request parameters and the explanations for each of the parameters and the associated script :

Please refer to the Note 755943.1 -Transfer Inventory Transaction Into General Ledger through Subledger Accounting SLA Flow

 3. Inventory Period should be closed.

Once Create Accounting-Cost Management concurrent request is completed successfully, Inventory period should be closed. While closing the Inventory Period, a report will get triggered called as "Period Close Reconciliation Report".

This concurrent program and report is used to create summarized transaction records. It displays the differences between accounted value and inventory in the Discrepancy column.

Following are key columns in Period Close Reconciliation Report:

Accounted Value: The valuation when calculated using previous adjusted summarization data + distribution information from MTA (Mtl_transaction_accounts).

On-hand Value: The valuation when calculated using current values from MOQ - quantities and costs from MMT. (Mtl_material_transactions).

Discrepancy: The difference between Account Value and On-hand Value.

Once the Period Close Reconciliation report completes normal, please check the report values and especially Discrepancy column. If there is a difference, this is the first indicator of mismatch in the Sub-ledger and Gl. In R12, you can run the Period Close reconciliation report for the closed period as well.

For more discussions on the Period Close Reconciliation Report,

Please refer to the  Note 295182.1 -Period Close Reconciliation Report (CSTRPCRE)

 4. Backdated Transactions.

Timely closing of Inventory period will prevent the backdated transactions which will have its impact on Inventory valuation at the sub-ledger level as well as in the Gl. This is the primary factor which creates a difference in Sub-ledger and Gl.

Please refer to the Note 1578694.1 -Identifying Inventory Backdated Transactions.  

 5. Mismatch like MMT-MOQD and MMT-CQL.

There is mismatch in quantities in different sub-ledger tables like Mtl_material_transactions (MMT) and Mtl_on_hand_quantities (MOQ). This can also lead to sub-ledger level difference and discrepancy in Period close reconciliation report.

For MMT-MOQ mismatch and the resolution thereof

Please refer to the Note  279205.1 -Find Mismatch Between MTL_MATERIAL_TRANSACTIONS (MMT) and MTL_ONHAND_QUANTITIES_DETAIL

 In average costing Environment, there can be MMT-CQL mismatch,  

Please refer to the Note  378348.1 which provides txn.sql to identify MMT-CQL mismatch.

 6. No negative ledger Id's exists at the legal entity level.

This is the next level check in the SLA. If there are negative ledger id’s for the gl batches then these needs to be rectified and resolved. There is a diagnostics and Data fix which is available to resolve.

Please refer to the Note 883557.1 -How To Avoid and Fix Corruption in Data Transfer from SLA - Negative Ledger_ID, Not Reached GL, Duplicate in GL

 7. Sub-ledger Period Close Exception Report.

From Cost Management SLA Responsibility, Sub-ledger period close exception report states the entities/events which are errored while transferring to Gl. This report is very handy so to know the first level check whether there are any transactions not transferred to Gl.

8. From General Ledger(GL) responsibility, Account Analysis Sub-Ledger report 180 Characters report will be useful to get the data for the account code combination.

9. Recon_diag.sql

After completing the above pre-checks, If there is difference in the GL and Inventory, final step is to start the reconciliation process.

Recon_diag.sql is attatched.

In Recon_diag.sql there are 5 input values which need to be provided.

1)     Org code= The inventory organization for which reconciliation activity is going on.

2)      Reference account = the account code combination in question for which Gl and sub-ledger values are not matching.

3)      from date = The inventory period, or date in which value mismatch is there.

4)     to date= The inventory period, or date in which value mismatch is there.

5)     Ledger_id = Enter the Ledger_id of the default ledger.  This can be obtained from the gl_ledgers  table.

(For 3 & 4 , pl run the script on monthly basis. Ideally if you are reconciling the April-2012 period, then condition would be
from date=01-apr-2012 to date=30-apr-2012)

After running the Recon_diag.sql two text files will be created in users default locations.
File names will be SLA_GL_RECON.TXT and INV_SLA_RECON.TXT

 The output will gather data from the Sub-ledger tables like Mtl_transaction_accounts and Wip_transaction_accounts in INV_SLA_RECON.TXT.

Data from SLA and GL tables like Xla_transaction_entities, Xla_Ae_Headers and Xla_ae_lines   and Gl_je_lines, GL_Je_batches, Gl_import_references is gathered inSLA_GL_RECON.TXT.