R12: How To Avoid and Fix Corruption in Data Transfer from SLA - Negative Ledger_ID, Not Reached GL, Duplicate in GL (Doc ID 883557.1)
=========================================================================
Applies to:
Oracle Receivables - Version 12.0.0 to 12.0.6 [Release 12.0]
Oracle Purchasing - Version 12.0.0 and later
Oracle Cost Management - Version 12.0.0 and later
Oracle Payables - Version 12.0.0 to 12.0.6 [Release 12.0]
Oracle General Ledger - Version 12.0 and later
Information in this document applies to any platform.
Goal
Release 12 introduced SLA - an architectural layer between subledger and GL. Hence, typically, once data has been accounted properly, data transfer happens from subledger to GL through SLA.
During the transfer, some data corruption issues have been reported on the SLA to GL bridge. These issues may be categorized into below categories :
1. Data with Negative Ledger_id in GL tables.
2. Data Marked as transferred in SLA but not reached GL.
3. Data Marked as Not transferred in SLA, but reached GL.
4. Multiple posting issues.
5. In GL Deleted from SLA (It is NOT applicable for 11i data)
6. Journal Import failing with EP01 error while running Transfer to Journal Entries Program
7. 11i accounted transferred to GL in R12 missing gl sl link id.
The problems can be visible in GL (for example missing journals) but also in subledgers (for example Accrual Balance mismatch between Accrual Reconciliation Report and GL).
Fix
A) To identify and confirm if any such data corruption exists and which category you fall into, please run the SLA GL diagnostics script (see NOTE 878891.1).
This diagnostics can be obtained after applying the following patches.
for 12.0.x versions: Patch 8513940:R12.XLA.A
for 12.1.x versions: Patch 8765953:R12.XLA.B
Navigation:
Log into Oracle Diagnostics tools
Select link against Subledger Accounting
Select XLA GL Diagnostics under GL_DIAGNOSTICS link
GL diagnostics has to be run for all the ledgers associated to a primary ledger. The below query would help to identify the ledgers for which the diagnostics has to be run:
SELECT gled.ledger_id as LEDGER_ID
FROM gl_ledger_relationships glr, gl_ledgers gled
WHERE glr.primary_ledger_id = &p_ledger_id -- Put primary ledger id here
AND glr.application_id = 101
AND ((glr.target_ledger_category_code IN ('SECONDARY','ALC')
AND glr.relationship_type_code = 'SUBLEDGER')
OR (glr.target_ledger_category_code IN ('PRIMARY')
AND glr.relationship_type_code = 'NONE'))
AND glr.target_ledger_id = gled.ledger_id
AND Nvl(gled.complete_flag,'Y') = 'Y'
GROUP BY gled.ledger_id;
The diagnostic will prompt for following parameters:
Ledger name (One ledger at a time)
Responsibility name (subledger responsibility)
Start Date and End Date
Source (Journal Entry Source Name)
Application_Id
To get the Application id and corresponding je_source_name, run the query below :
select application_id "Application id" ,je_source_name "Je Source"
from xla_subledgers;
Please ensure the following before the script is run
Program 'Gather Schema Statistics' is run to avoid any performance issues.
The script is run for the Primary Ledger only.
B) Check that you have installed the following XLA patch to fix the Root Cause:
for 12.0.x versions: Patch 8691650:R12.XLA.A
Fixed file versions:
xlaaptrn.pkb - 120.61.12000000.25
This can be replaced with advantage by patch 9742747:R12.XLA.A: Create Accounting Performance: Consolidated Patch (May 2010)
for 12.1.1 versions: Patch 8691650:R12.XLA.B (fix included in R12.1.2)
Fixed file versions:
$XLA_TOP
patch/115/sql/xlapurge.pkb - 120.0.12010000.2
patch/115/sql/xlapurge.pkh - 120.0.12010000.2
patch/115/sql/xlaaptrn.pkb - 120.63.12010000.17
patch/115/import/US/xla120cp.ldt - 120.131.12010000.19
Important: Please refer to Document 833986.1. That note has details of patch applications of dependent subledger applications, like Payables (AP), Receivables (AR), Costing (CST/BOM), Projects(PA), Assets (FA) and Globalization (JG). Please apply the necessary patches depending upon which all modules of Oracle Applications you use.
C) In case the diagnostic output identifies any data corruption, please raise a Service Request with Oracle Support, providing the output of the above diagnostics, so that we can provide the Datafix action plan.
*** Customers are requested not to apply any fix without recommendation by Oracle Support. Each fix has a packaged series of steps which can be specific to every customer environment. Applying the fix without executing other related steps may cause damage to existing data. ***
-----------------------------
1.Data with Negative Ledger_id in GL tables.
1.1) Confirm that the recommended patches/file versions are installed.
Run steps 1.2 to 1.3 by providing only the primary ledger_id for which GL diagnostic returned corruption.
1.2) Apply Negative_ledger_id_dfix.txt
1.3) Apply IN_SLA_NOT_IN_GL_dfix.txt
1.4) Run Transfer to GL. It may be possible that the fix IN_SLA_NOT_IN_GL_dfix.txt did not return any rows. Because of this, step 1.4 would not transfer any new data either. In this case, it could be inferred that a corresponding line already exists in GL with a positive ledger id. This can also be verified using the query provided in step 1.7
1.4.1)While running Transfer to Journal Entries Program if journal Imports throws EP01 error, Please go to Step 6 and follow the steps and then resume back to this point and continue with below steps.
1.5) Rebuild Trial balance if subledger is Payables using Note 553484.1
1.6) Run GL diagnostic and log a bug if it still shows corruption.
1.7) After deleting negative ledger id data, to find out GL batches in which this data has been reposted with positive ledger data ,run below query. Note that there is no duplication, negative ledger id batches were deleted and reposted to GL with correct positive ledger id batches.
Select distinct je_batch_id, name
from gl_je_batches where je_batch_id in
(select je_batch_id from gl_import_references
where (gl_sl_link_id, gl_sl_link_table) in
(select gl_sl_link_id,gl_sl_link_table
from gl_invalid_jrnls_&P_BUG_NUMBER));
----------------------------
2. Data marked as transferred in SLA but not reached GL.
2.1) Confirm that the recommended patches/file versions are installed.
Run step 2.2 by providing only the Primary ledger_id
2.2) Apply IN_SLA_NOT_IN_GL_dfix.txt
2.3) Run Transfer to GL.
2.3.1)While running Transfer to Journal Entries Program if journal Imports throws EPO1 error, Please go to Step 6 a and follow the steps and then resume back to this point and continue with below steps
2.4) Rebuild Trial balance if subledger is Payables using Note 553484.1
2.5) Run GL Diagnostic again and log a bug if GL diagnostic still shows corruption.
2.6) To find the GL Batch for which data was originally stuck in SLA tables and is now posted after application of datafix and running Transfer to GL, run below query
select je_batch_id,name from gl_je_batches
where group_id in
(select distinct group_id from xla_ae_headers
where (ae_header_id, ledger_id, entity_id) in
(select ae_header_id, ledger_id, entity_id
from xla_not_in_gl_&P_BUG_NUMBER));
-----------------------------
3. Data marked as not transferred in SLA but reached GL.
For cases where only the gl transfer flag is incorrectly set in SLA tables to untransferred but data has reached GL, proceed with below steps.
3.1) Confirm that the recommended patches/file versions are installed before application of the following datafix.
Run step 3.2 only for Primary Ledger_id
3.2) Apply IN_GL_NOT_IN_SLA_dfix.txt
3.3) Rebuild Trial balance if subledger is Payables using Note 553484.1
3.4) Run GL Diagnostics and Log a bug if GL diagnostics still shows corruption.
-----------------------------
4. Multiple Posting Issues
For cases where data from SLA tables has been posted multiple times in GL, follow below steps.
4.1)Confirm that the recommended patches/file versions are installed before application of datafix;
4.2) Apply Dual_posting_dfix.txt
Run this datafix by providing Primary_ledger_id only. After running this datafix, please open the file provided in the P_File_name parameter and reverse all batches manually displayed under the following prompts
"Reverse following batches in GL" and "Reverse following batches in GL for the secondary ledgers created at Journal Level"
Batches are combinations of duplicate data and correct data (which was posted only once). Datafix asks to reverse all batches in which duplication has been posted and transfer to GL is run to retransfer data from SLA that was reversed in GL as part of datafix.
Do not proceed to 4.3 unless all the batches are reversed.
4.3) Apply IN_SLA_NOT_IN_GL_dfix.txt
4.4) Run Transfer to GL.
4.4.1)While running Transfer to Journal Entries Program if journal Imports throws EPO1 error, Please go to Step 6 and follow the steps and then resume back to this point and continue with below steps.
4.5) Rebuild Trial balance if subledger is Payables using Note 553484.1
4.6) Run GL Diagnostics and log a bug if GL diagnostics still shows corruption.
4.7) All duplicate batches would be reversed. To find out new batches in GL in which data has been reposted, run below query:
SELECT DISTINCT gjb.je_batch_id,
gjb.name ,
gjh.ledger_id
FROM gl_je_batches gjb ,
gl_je_headers gjh
WHERE gjb.je_batch_id IN
(SELECT je_batch_id FROM gl_import_references
WHERE (gl_sl_link_id,gl_sl_link_table) IN
(SELECT gl_sl_link_id,gl_sl_link_table
FROM xla_dual_&P_BUG_NUMBER) )
AND gjb.je_batch_id=gjh.je_batch_id
AND nvl(gjh.accrual_rev_je_header_id, 0) = 0;
---------------------------
5. In GL Deleted from SLA (It is NOT applicable for 11i data)
For Cases where Data deleted from SLA tables, but exists in GL Tables.
5.1) Confirm that the recommended patches/file versions are installed before application of the following datafix.
5.2)Apply generic_deleted_from_sla_Part1.sql script.
Run this datafix by providing below parameters
p_file_Name ->Output File Name, some name in order to identify customer which file is generated.
p_ledger id ->Primary ledger for which corruption is observed
p_application id ->Application ID (e.g 200-> Payables, 222-> Receivables etc)
p_je_source_name ->"Payables" or "Receivables" etc
If user is not aware then use below query
select application_id "Application id" ,je_source_name "Je Source"
from xla_subledgers;
p_start date/p_end date ->Date range within which accounting/gl date of corrupted data lies
After running this datafix, please open the file provided in the P_File_name parameter and reverse all batches manually displayed under the prompts - "Reverse following batches in GL" and "Reverse following batches in GL for the secondary ledgers created at Journal Level"
NOTE: To be able to reverse, one has to open the relevant periods and reverse the batches in the same periods because re-transfer the data to GL will be done in the same period.
If the user disagrees to open the period, then we may have to provide a datafix to sweep the transactions to an open period. The users will have to reverse these batches to this new period only (Please accept this option only in the worst case scenario).
5.3) After reversing the batches, Please run the script generic_deleted_from_sla_Part2.sql .
5.4) Run Transfer Journal Entries to GL to re-transfer the data back to GL.
5.4.1) While running Transfer to Journal Entries Program if Journal Imports throws EP01 error, Please refer Step 6 and then resume back to this point and continue with below action points. 5.5) Run GL Diagnostics and log a bug if GL diagnostics still shows corruption.
NOTE: The above fix will impact only data which ever transferred from SLA, if the data deleted is from other sources (eg Manuals, Other Sub ledgers) the above fix doesn't have any impact.
------------------------------
6. Journal Import failing with EP01 error while running Transfer to Journal Entries Program
6.1)Are the users willing to open the relevant periods listed in Journal Import with EP01 error.If yes, please Open period and run transfer to GL.If you ran Transfer to GL as part of data fix mentioned in this note, navigate back to original data fix and resume from where you left.
6.2)If users are not willing to open the period ,then run sweep data fix point .This would move the Accounting Date of data in SLA tables to that of latest open period.Note that users are recommended to open period over this option of running sweep because this would modify dates in SLA/GL but in sub ledger still old accounting date would be stamped.So users need to take care of this during reconciliation or followup with respective sub ledgers
NOTE: While running the data fix, please ensure proper parameter values are passed, when it comes to Dates, it should be in proper formats.
Supply the following when prompted:
new_period_name : New Period Name for which customer needs to sweep(It should be in proper format)
ledger_id : Primary Ledger Id
application_id : Application ID (e.g 200-> Payables, 222-> Receivables etc)
To get the Application id and corresponding je_source_name, run the query below :
select application_id "Application id" ,je_source_name "Je Source"
from xla_subledgers;
6.3) GL transfer is not picking data and ledger is secondary ledger and customer has disabled secondary ledger meaning relationship_enabled_flag in gl ledger is 'N'.Because of this GL Transfer doesnt pick secondary ledger data.Customer need following datafix in this case.
UPDATE XLA_AE_HEADERS XAH
SET
XAH.GL_TRANSFER_STATUS_CODE = 'NT'
WHERE
XAH.APPLICATION_ID = :p_application_id
AND XAH.LEDGER_ID = :p_ledger_id -- ledger id of secondary ledger which is disabled
AND XAH.GL_TRANSFER_STATUS_CODE = 'N'
AND XAH.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
AND XAH.ACCOUNTING_DATE BETWEEN TO_DATE(':p_start_date','DD-MM-YYYY') AND TO_DATE(':p_end_date','DD-MM-YYYY')
AND EXISTS (SELECT 1
FROM XLA_EVENTS XE
WHERE XE.APPLICATION_ID = :p_application_id
AND XE.EVENT_ID = XAH.EVENT_ID
AND XE.EVENT_ID = XAH.EVENT_ID
AND XE.EVENT_STATUS_CODE = 'P'
AND XE.PROCESS_STATUS_CODE = 'P');
Note : The same datafix can be used for primary book too, where you have old data not getting picked up. In that case you will have to use primary ledger id and date range of old periods as ledger_id and accounting date range respecitvely.
Important note : After updating the records to NT such records would not go to GL and customers would need to take care of those records while doing the reconciliation from their side.
------------------------
7. 11i accounted transferred to GL in R12 missing gl sl link id.
7.1)For users who have patch 8691650 or equivalent/higher version of xlaaptrn.pkb,follow gdf 13783741:R12.XLA.A/ 13783741:R12.XLA.B
7.2)For customers who do not have patch 8691650 or have lower version please apply datafix.Point to 11i missing old datafix here.(File Name: 11iMissingGLSLLINKID_R12.txt)
If while applying any of the datafixes, the following error is occurs:
- ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
add the below line after the 'begin' statement of CALL GL API TO DELETE THE BATCHES:
dbms_output.enable(null);
Do you have the script IN_SLA_NOT_IN_GL_dfix ?
ReplyDeleteyou can send to my email, iddimimi@gmail.com
ReplyDelete