Oracle R12 SLA – Upgrade Opportunities and Challenges!

INTRODUCTION

With the Release 12 of Oracle Applications, Subledger Accounting (SLA) has been introduced. This is a Rule-based accounting engine, toolset & repository supporting Oracle E-Business Suite modules (also called as subledgers). The main idea behind this introduction is to have an option of allowing multiple accounting representations for a single business event, resolving conflicts between corporate and local fiscal accounting requirements.

Also, this introduces a common data model and user interface (UI) across subledgers, replaces various disparate 11i setups, providing single source of truth for financial and management analysis.

Due to SLA, there are some changes in the functionalities of General Ledger. There are also certain setups that need to be done in General Ledger for SLA.

OVERVIEW

For understanding the Impact of SLA on General Ledger, let’s delve into three main components:

Drilldown – This will deal with the change in the Drilldown data, which appears now. Also, the setups, which decide what data, can be viewed and how data can be secured.

Journal Import – This will deal with the transfer of data from Subledgers to General Ledger. This will also explain the various setups that are done for the Ledger which govern the import for various applications.

Technical Reference – This will give the technical references, for journal import and drilldown functionalities, which will help in understanding the architecture and will also help in troubleshooting.

Part I: Impact of SLA on Drilldown in General Ledger

Drilldown functionality enables users to navigate from a Journal in General Ledger to the Subledger Journal Entry in SLA and from there to the transaction which has resulted into that journal.

To enable Journal drilldown from General Ledger, the “Import Journal References” option should be enabled for the Source.

This can be checked under General Ledger Responsibility at Setup -> Journal -> Sources -> Define. Only when this is enabled, the reference data is populated by SLA engine.

The Drilldown in Release 12 is different from the prior releases of the Application. R12 drilldown is Web based and not in the Forms.

Navigation to perform Drilldown is the same as in Release 11i:

Journals -> Enter -> Review Journal, Select the journal line and click on “Line Drilldown”

Drilldown can also be performed from:

·         Tools -> Drilldown

As mentioned above, Drilldown opens the Web Page instead of Form.

The SLA Page for Drilldown shows the following:

·         GL Journal Line information

·         Subledger Journal Line information

There are two Options available on this SLA page:

·         View Transaction

·         View Journal Entry

  

‘View Transaction’ will take us to the Subledger Transaction form. E.g. for the current journal line, since the Source was Payables, it opens Invoice Workbench.

‘View Journal Entry’ will take to the Subledger Journal Entry details

View Journal Entry has three parts to it.

-Transaction Information

-Additional Information (this is hidden by default)

-Line Details

Apart from this there are some extra details shown at the top of the page, which is the header level information for the Subledger Transaction.

Some of the important fields in header part of the Subledger Transaction Journal are:

·         Ledger Name

·         Journal Entry Status

·         Balance Type

·         GL Date

This is the ‘Transaction Information’ section of Subledger Journal Entry page.

The fields here will change based on the transaction and the subledger in which the transaction was entered.

Since the example transaction is entered in Payables, it is showing the details of the invoice and the supplier.

This is the ‘Additional Information’ section of Subledger Transaction Entry page.
This section shows the details of

·         Event

·         General Ledger

·         Sequences

·         Application Accounting Definition

·         Note that Event and Application Accounting Definition are important from SLA side. However, from GL perspective the relevant details are Period, Transfer to GL Date and Transfer to GL status.

This is the last section, ‘Lines’ of the Subledger Journal Entry page.
The important details in this section are:

·         Code Combination

·         Currency

·         Entered Debits and Credits

·         Accounted Debits and Credits

·         Select the link ‘Show’ on any of the lines to view additional details for the transaction line

This page gives various details for transaction line from the subledgers side.

Thus, the Drilldown from General Ledger, takes us to the Subledger Journal Entry which shows the details of the Subledger journal. From here we can further drilldown to the Subledger Transaction.

Profile Options related to Drilldown:

SLA: Enable Subledger Transaction Security in GL
This profile option controls whether the drilldown to SLA will enforce the transaction security of the application owning the Transaction. For example, if one drills down to Payables, he will only be able to see Journals for transactions belonging to operating units to which he has access (based on your MO: Security Profile.) Thus, drilldown can be restricted to the Operating Units to which the responsibility has access. In case the GL responsibility does not have access to a particular Operating Unit, the Drilldown will not show any data.

Part II: Impact of SLA on Journal Import in General Ledger

The way Journal Import is triggered from the subledgers has undergone a major change in Release 12.

Execution of Journal Import from the Subledgers depends on the setting of the Profile option “SLA: Disable Journal Import”.

The profile option “SLA: Disable Journal Import” can be set only at the Site level and hence will be applicable to all the subledgers/modules. The default value for this profile option is ‘No’ and this should NEVER be changed, unless suggested by Support or Development. Setting this profile option to ‘Yes’ breaks the link between SLA and GL, so GL no longer knows which journals came from SLA. This may cause problems with inquiry and drilldown on these journals in the future. This option is set to ‘Yes’ only in cases where GL is on a separate instance than SLA.

The SLA: Disable Journal Import profile option is hidden and disabled in the latest code

SLA: Disable Journal Import set to No.

Value ‘No’ for this profile option indicates that Journal Import will ALWAYS accompany the transfer of data from Subledger. Another main implication of this value is that, on failure of Journal Import, the data will be rolled back to SLA tables and hence we will not have any data in GL_INTERFACE. Note that for this setting of the profile option, General Ledger will have very little part to play, as any error in Journal Import will take the data back to SLA tables and GL_INTERFACE will not have any data in it.

This will also mean that “Journal -> Import -> Run” and “Journal -> Import -> Correct” forms will not be used for the Subledger data as GL_INTERFACE will never have any data for them. However, these forms will continue to be used for the data, which is being transferred from some other sources such as Legacy or some user defined sources for which data is populated in GL_INTERFACE table, and then Import is run in General Ledger.

Whenever data is transferred from the subledgers, the request doing so, will also trigger the Journal Import program. The name of the program to transfer data to GL, will change as per the subledger; we have explained the behavior below with respect to the Payables module. In Payables, there are three options for transferring data to GL:

1. When Online Accounting is done for Payables transactions, there are three options available (Refer Screenshot below):

I.    Draft

II.    Final

III.    Final Post

For any value of the profile option, Draft and Final options work in the same way i.e. draft mode creates the SLA journals in Draft mode which can be modified afterwards; Final mode creates SLA journals and the transaction cannot be modified after this. The SLA journal can then be transferred to General Ledger. Transfer of the journals created in Final Mode is done using ‘Transfer to General Ledger’ concurrent request.

However, “Final Post” works differently for the two values of the Profile option. If profile option is set to No, then “Final Post” creates the SLA journals in Final mode, transfers the data to GL_INTERFACE, runs Journal Import and triggers the General Ledger Posting program too.

1.    When Accounting is done from Submit request form i.e. by running the request “Create Accounting”, it gives the following options:

2.    Mode: Draft or Final
b. Transfer to General Ledger
c. Post in General Ledger

If “Transfer to General Ledger” is selected as Yes, then the data is transferred to GL_INTERFACE and immediately after that Journal Import request is also triggered. In case of any errors, the data is rolled back to SLA tables.

3. If Accounting is done in Final Mode and we are transferring the data by running the request “Transfer Journal Entries to GL” request, it gives the option of “Post in General Ledger” which refers to the Posting program in General Ledger. Thus, there is no option for Journal Import as Journal Import will always be triggered by the Transfer program.

From points 1, 2 and 3, it is evident that Journal Import has become an inbuilt part of the Transfer Journal Entries program of SLA in Release 12. Thus, when the profile option “SLA: Disable Journal Import” is set to “No”, GL_INTERFACE table will never have data for Subledgers.

”SLA: Disable Journal Import” set to Yes

This setting for the profile option indicates that the transferring of data from Subledgers will take place in the same manner as it happens in Release 11i. However, changing the value of the Profile option to Yes is strictly not supported and should NEVER be used as this may lead to issues related to the linking between SLA and GL journals. For this value of profile, the Transfer to GL programs of the subledgers, populates the data in GL_INTERFACE and then Import needs to be run manually from GL. In case the subledger is providing the option of running the journal import too along with the transfer to GL, then the request will fire the Journal Import also. However, in case of a Journal Import error, the data will remain in GL_INTERFACE table.

Setups related to Journal Import:

(1) Journal Import can be run in Summary or Detail mode.

This can be set for each application, as per the requirement. By default, this is set to “Summarize by GL Period” but can be changed as per the requirement. The setup can be changed from:

·         General Ledger responsibility

·         Financials -> Accounting Setup Manager -> Accounting Setups

·         Query for the Ledger being used

·         Update Accounting Options

·         Select update for “Subledger Accounting Options”

·         “Update Accounting Options” for the Application

·         Here there is an option “General Ledger Journal Entry Summarization”, which can be set to following values:

i. Summarize by GL Date (Journal Import will create summary journals based on Accounting Date)

ii. No Summarization (Journal Import will create journals in Detail mode)

iii. Summarize by GL Period (Journal Import will create summary journals based on Period to which the Accounting Date falls) Thus the option “General Ledger Journal Entry Summarization” will determine if the Import for the application is being done in Detail or Summary mode.

(2) Prior to Release 12, it was needed that the Journal Import should be run in the same language as the language, which was used to transfer data to GL_INTERFACE. Though this is not much relevant now (the transfer and journal import are triggered together); still Journal Import can be made independent of the language. This can be achieved by running journal import using the Source and Category Key. Generally, the columns user_je_source_name and user_je_category_name in gl_interface, correspond to the columns GL_JE_SOURCES.user_je_source_name and GL_JE_CATEGORIES.user_je_category_name respectively. These values change for the different language and thus making journal import language-dependent. However, while defining the sources (General Ledger Responsibility: Setup -> Journal -> Source), if the option “Import Using Key” is selected, then import refers to GL_JE_SOURCES.je_source_key and GL_JE_CATEGORIES.je_category_key. These being the Key values are common across languages and hence journal import becomes language independent.

(3) While running Journal Import from General Ledger responsibility, the LOV of Ledger provided there is taken from the ledgers in table GL_INTERFACE and the Data Access Set attached to the responsibility has either Full or Read/Write access to the Ledger.

Journal -> Import -> Run

In case a different Interface table is being used, the LOV will check for the data in GL_INTERFACE_CONTROL instead of GL_INTERFACE.

(4) Data Access Sets do apply to Journal Import too. To be able to import data, one should have either Full or Read/Write Access to the ledger and all the Balancing Segment Values for which data is populated in GL_INTERFACE. In case one does not have access to the Ledger at all, the LOV for Ledger will not appear in Journal -> Import -> Run screen and hence he will not be able to submit Journal Import.

If there are multiple balancing segments in the data in GL_INTERFACE, and the responsibility has access to a few and not to others, then you will be able to run Journal Import but it will complete in Warning and data will not be imported. Error shown in the Journal Import Execution report would be:
– EM29: You do not have access to this ledger and account combination.

Part III: Technical Reference

Drilldown from General Ledger takes us to the SLA Journal details. The technical flow for this is as follows:

Drilldown is performed from Journal lines in General Ledger. This data is stored in the table GL_JE_LINES.
The link between SLA data and data in GL is through the table GL_IMPORT_REFERENCES.

Note that data is populated in GL_IMPORT_REFERENCES only if “Import References” option is selected in the Journal source definition.
The data can be mapped from GL_JE_LINES to GL_IMPORT_REFERENCES table using the columns je_header_id and je_lines_num.

The SLA Journal line data is stored in the table XLA_AE_LINES.
The header details are stored in XLA_AE_HEADERS.
The data in GL_IMPORT_REFERENCES can be mapped to XLA_AE_LINES using the columns gl_sl_link_id and gl_sl_link_table.

Data can be transferred from SLA to GL in either Summary or Detail mode. This option is defined for the Event Class, which is attached to the Journal Line Type. Journal Line Type is then attached to the Journal Line Definition, which is finally attached to the Subledger Accounting Method. Please note that if the transfer is done in Summary mode, then the Reference columns will not be populated in either GL_IMPORT_REFERENCES or GL_JE_LINES.

Data from SLA, which comes to GL_INTERFACE, can then be imported in either Summary or Detail Mode. This option is set while defining the Ledger in the Accounting Setup Manager.
Please note that irrespective of the Mode of Transfer (Detail or Summary), Drilldown will work from GL to all the Subledgers that are using the SLA Engine. The subledger reference information required to drilldown is taken from the SLA tables XLA_AE_HEADERS and XLA_AE_LINES, and not in GL_JE_LINES or GL_IMPORT_REFERENCES.
Since Patch 7512923:R12.XLA.A (for R12.0.x), the reference columns REFERENCE5 to REFERENCE10 are populated with entity_id, event_id, ae_header_id, ae_line_num, accounted_cr and accounted_dr.
You need to apply this patch if you want to see Line References on Account Inquiry -> Journal Detail.
To identify the mode of Transfer:

Column gl_transfer_mode_code in XLA_AE_LINES indicate the transfer mode from SLA to GL. For this column, value ‘S’ indicates that the transfer is done in Summary mode while ‘D’ indicates that the transfer is in Detail mode.

For the Journal Import mode, the values for specific applications is stored in the table XLA_LEDGER_OPTIONS. This table stores the setup for various applications for the Ledgers defined. The column indicating the Journal Import mode, is transfer_to_gl_mode_code. The values are:

P: Summarized by Period
A : Summarized by Accounting Date
D : No Summarization i.e. Detail mode

Challenges

R12.0.5 and R12.0.6 Regression

A regression was introduced in R12.0.5 and R12.0.6. As a result of this regression, any customer who has upgraded may not be able to run the SLA hot patch, later. Apply the pre-install critical Patch 8234812 to correct the problem.

SLA Hot Patch

Cannot run the SLA HOT Patch after upgrading from 11i to R12.0 or R12.1 because the LAST_UPDATED_BY field in the GL_PERIOD_STATUSES table has a -601 or -602. This is related to Bug 11829821. This patch is an upgrade patch and is only applicable to users who have not already upgraded from 11i to R12.0 or R12.1. A data fix is required to correct the data in the GL_PERIOD_STATUSES table. Please log a service request with Oracle Support to request the data fix.

Receivables 
Customers get an error while running the SLA Hot patch. The adpatch log shows that ar120girpu.sql failed. Customers who encounter this issue should log a service request with the Receivables team and request a datafix. Ref. Bug 9071674.

Project Accounting

PA customers get ORA-20500: There are periods pending upgrade for Application ID 8721 even though the customer has not enabled Enhanced Period Processing (EPP). The problem is that the SLA post upgrade cannot be run when attempting to run the SLA Hot patch. Customers who encounter this issue, should log a service request with the Project Costing team to request a datafix. Ref. Bug 8582427

TROUBLESHOOTING THE SLA PRE-UPGRADE PROGRAM

How to troubleshoot issues with the SLA PRE-UPGRADE PROGRAM?

First, run the following SQL script against the gl_period_statuses table to show the Apps ID, Ledger, start date, and end date.This information will help you determine what periods the SLA Pre-Upgrade program has selected for upgrading.

select application_id, set_of_books_id,
min(start_date), max(end_date)
from gl_period_statuses
where migration_status_code = ‘U’
and adjustment_period_flag = ‘N’
group by application_id, set_of_books_id;

SAMPLE OUTPUT
|—————————————————————————|
| APP ID | BOOKS | MIN(START_DATE) |MAX(END_DATE) | Legend for App ID:
| 101 | 1 | 01-APR-10 | 28-FEB-11 | – Fixed Assets
| 200 | 1 | 01-APR-10 | 28-FEB-11 | – Payables
| 201 | 1 | 01-APR-10 | 28-FEB-11 | – Purchasing
| 222 | 1 | 01-APR-10 | 28-FEB-11 | – Receivables
| 275 | 1 | 01-APR-10 | 31-MAR-11 | – Project Accounting
| 401 | 1 | 01-APR-10 | 31-MAR-17 | – Inventory/Costing
|—————————————————————————|

How does the SLA Pre-Upgrade program determine what is the START DATE and END DATE?

In our example above, the SLA Pre-upgrade picked 01-APR-2010 as its start date when the client expected
01-SEP-2010, which is 6 months prior to 28-FEB-2011 (which was when the upgrade was run).

START DATE 
The SLA pre-upgrade program selects the first date of the current fiscal year as its START DATE.
If there are not enough periods to make up 6 months, then the system will look at the periods in the prior fiscal year.
In the above example, the client’s fiscal period began on 01-APR-2010, so the system selected that date.
If the client’s fiscal period began on 01-JAN-2011 and the SLA Pre-upgrade was run on 28-FEB-2011, then the program would have chosen 01-SEP-2011 as its start date.

END DATE

The END DATE is the last open period. If you have opened the periods for some products and not others, then the end date would differ for each product. In the SAMPLE OUTPUT (10a), the products for Application IDs 401 and 275 had open periods through 31-MAR-2017 and 31-MAR-2011, respectively. The SLA Pre-Upgrade select all open periods in case there are transactions created for future periods.

Does the SLA Pre-upgrade program perform the upgrade?

The R12 SLA Pre-Upgrade Program DOES NOT perform the actual upgrade. This program writes out to the GL_PERIOD_STATUSES table. It is the subledger (i.e., AP, AR, FA, Costing etc.,) upgrade programs that actually perform the upgrade by reading the accounting information in the subledger (FA, AP, AR etc.,) tables and writing the corresponding records to the SLA tables for future reference.

Note: The upgrade program works differently for AP. The AP upgrade considers all records in the ap_accounting_events_all, ap_ae_headers_all, and ap_ae_lines_all tables. See question 6a above.

R12 SLA POST-UPGRADE

IMPORTANT: Before running the SLA Hot Patch (aka SLA Post Upgrade patch), please apply the one-off patches documented in Doc 1460065.1.
These patches address many of the performance issues reported by customers who were attempting to run the SLA Hot Patch.

How to upgrade the accounting transactions after I have completed the R12 Upgrade.

To upgrade accounting transactions after you have completed the R12 Upgrade, you can perform the SLA Post-Upgrade Process. In this process, you will be able to specify a subset of the accounting and tax data to upgrade.

See Appendix G of the Oracle Applications Upgrade Guide for more information on this process. See Ques 2 for the link to the documentation that is specific to the release you have.

How to upgrade historical data after upgrading to R12?

You can run the SLA hot patch during uptime to upgrade historical data, if you only did a partial or minimal upgrade during downtime.

OR

You can run the “Upgrade Historical Subledger Transaction Accounting Program”, which was first introduced in R12.1.2 (Doc 973404.1). Refer to Document 1376752.1 for more information.

OR

(This option is only available for Payables) Enable and use the SLA: On-the-fly Upgrade of Historical Subledger Accounting Distributions profile option, see Doc Id 1938253.1 for more details. This option is a good option when you have a few old transactions that are causing current accounting to error and you do not need/want ALL transactions going back to the date of the problem old transactions upgraded.

How to run the SLA Hot Patch or SLA post-upgrade process?

Before running any SLA post-upgrade process (i.e., SLA Hot Patch), you must enter the initial date to be used to determine the initial period to be upgraded. This date is entered in the SLA: Initial Date for Historical Upgrade profile option. This profile option must be populated in order to run the process.
Run the process as follows:

For R12.0.x/R12.1.x

Run AutoPatch with options=hotpatch.

Specify $XLA_TOP/patch/115/driver/xla5584908.drv when prompted for the unified driver

For R12.2.x

adop phase=apply options=nocopyportion patchtop=$XLA_TOP/patch/115 patches=driver:xla5584908.drv hotpatch=yes

Note: The SLA Hot Patch can be run multiple times, each time by specifying a date that covers a range that has not been upgraded.

For example, if your current fiscal year starts on 1-Jan-2008 and the current date is 1-Oct-2008, and you did not run the pre-upgrade process, then financial data for all your periods from Jan-08 to latest future open period will be upgraded during Downtime upgrade.

In the diagram above, data from Jan 2008 to current has been upgraded. To upgrade data from Jan 2004 to current, then do the following:

·         Change the SLA: Initial Date for Historical Upgrade profile to 01-Jul-2007

·         b. Then run the Hot Patch

·         c. Repeat the above steps for 6-month increments until all the data from Jan 2004 has been upgraded. The 6-month increment is not a rule because it really depends of the amount of history that is available to be upgraded.

·         Note: Running the Hot Patch could take several minutes or several hours to complete depending on the date range you specify and the amount of data to be upgraded.

·         Should you get the following message when you attempt to run the hotpatch, you can continue:

·         “This Patch seems to have been applied already. Continue Anyway?”

Restrictions when running the SLA Hot Patch?

Yes, there are some restrictions. They are as follows:

(a) You cannot run the SLA Hot Patch for a period that is included in the range already upgraded.

For example, in the diagram shown below, where it is assumed that the data for Jan 2008 up to the current accounting period has been upgraded, you cannot enter a date of Sep 2008 to rerun the upgrade.

(b) You cannot run the SLA Hot Patch if any period is pending upgrade, even if it only affects one of many applications. See Ques 4 above for an explanation on the different status codes.

(c) You cannot run the Hot Patch for one ledger or one application (i.e., AP or AR).

(d) You cannot run the Hot Patch for open transactions only. The upgrade looks at the start and end period, and it upgrades ALL transactions within the date range. The GL Headers that are created for these transactions are tagged as Upgraded and so are the periods.

Pre-cautionary steps should be taken “BEFORE” performing the R12 SLA Upgrade?

(a) It is good practice to transfer or post all 11i data from your respective subledgers (i.e., AP, AR, FA, etc.,) to the General Ledger before running the Downtime upgrade.

(b) You can post data after going live; however, you should avoid, if possible, having 11i accounted data that has not been transferred prior to upgrading. If for some reason this situation cannot be avoided, please refer to Doc Id 1092913.1.

Note: Please review the Upgrade Advisor for more pre-upgrade steps and patches. To find the Upgrade Advisor applicable to your upgrade, please click on the E-Business Suite link in the Lifecycle Advisor Doc Id 250.2

Verification from an SLA Upgrade standpoint after the downtime R12 upgrade and/or SLA Hot Patch is run?

·         Verify that there are no periods pending upgrade in the GL_PERIOD_STATUSES table, after the R12 upgrade or SLA Hot patch is run. See Note 747216.1R12 SLA Upgrade: Check that the SLA Pre-Upgrade Completed Successfully.

·         Compare the AP Trial Balance outputs from 11i to that in R12. You only need to check this after the downtime R12 upgrade and NOT after any SLA Hot Patch execution. Technical tips can be found in Note: 605707.1SLA: Troubleshooting the AP to GL Reconciliation, see Questions QR1 through QR12.

·         Note: Please review the Upgrade Advisor for more post-upgrade steps and patches. To find the Upgrade Advisor applicable to your upgrade, please click on the E-Business Suite link in the Lifecycle Advisor Doc Id 250.2

Precautionary steps should be performed “BEFORE” running the SLA Hot Patch?

·         Run this SQL script:
select * from gl_period_statuses where migration_status_code=’P’

this script should return zero rows, only then should you consider running the SLA Hot Patch.

·         Take a backup of the xla_upgrade_dates table before running the SLA Hot Patch

·         Set a realistic date for the SLA: Initial Date for Historical Upgrade profile. Do not set a date like 1-Jan 1900 or 1-Jan-1000.