Be aware of R12 Data Model changes to properly assess R12 upgrade rework

Posted on 13-May-11. Filed under: R12 | Tags: , , , , , |

Any entity upgrading to R12 should sufficiently understand the data model changes between 11i and R12.  Based on an entities’ dependence on the existing 11i data models that change, there can be a significant level of rework required to ensure that existing custom RDF/BI Pubisher Reports, Discoverer Reports, outbound interfaces, custom views, custom SQL & PL/SQL, etc. will function properly in the R12 environment.  Analysis of custom objects relative to the data model changes can’t just be limited to whether objects will compile or run in R12, but should also include analysis of whether the proper data is returned from both upgraded 11i transactions and new transactions generated in R12.  To properly plan and allocate resources to an R12 upgrade project, the impact of these changes to an entity should be thoroughly analyzed.

For example, in my years working in the Federal Financials space, I’ve seen a fairly heavy reliance on the gl_je_lines.referenceX (journal import references) columns for creating custom reports that provide subsidiary information (e.g. PO Number, Vendor, etc.) and gl information together.  I’ve seen reports/interfaces often built from gl_je_lines up to best ensure the detail reports balance with trial balance amounts.  In R12, the gl_je_lines.referenceX columns are no longer populated or are populated in a different manner.  Thus any custom report, interface, etc. that leverages these fields will likely have to be reworked (or overall approach addressed) to continue to meet end-user requirements.  The R12 GL -> Subsidiary drilldown path now runs through gl_import_references and a new suite of XLA tables as shown in this diagram.

An additional problem that confronts Federal Financials customers with this change is that the R12 Historical SLA Upgrade process does not upgrade 11i Accounting Events that are created from USSGL Transaction Codes (e.g. Budgetary Entries) into the new XLA tables.  Commercial R12 upgrade customers could likely just throw out the old model, assuming they upgrade all their old data necessary into the new XLA model, and re-build reports entirely off the XLA model.  However, to get a proper one-stop-shop blend of 4801 Obligation data that spans 11i and R12 for example,  Federal Financial customers have to blend queries utilizing both data-models.  This required blend can then have a detrimental performance impact on the underlying queries, so then it may also be likely that additional performance tuning may be required with the R12 rework effort.

Another R12 data model change that could impact a decent number of custom reports and potentially other objects deals with the new ap_invoice_lines architecture.  Any custom objects leveraging joins based on ap_invoice_distributions_all.distribution_line_number will certainly have to be reworked due to the change shown in this example to avoid cartesion products and/or errors with subqueries returning multiple rows.

There are numerous data-model changes with R12.  Oracle has recently provided the new EBS Data Model Comparison Report tool which is a good tool, but it is definitely going to take time to properly identify the impacts for each entity.  Other data model change areas I’ve been running into deal with include: 1) the new IBY Payments architecture (vendor payment and bank account information) , 2) Migration of vendor information to the TCA architecture (po.po_vendors, po.po_vendor_sites_all tables no longer exist, but there are apps views of po_vendors and po_vendor_sites_all that could be utilized), along with other miscellaneous changes.

I wanted to get the word out to help prevent underestimating the level of effort required for reworking custom objects with the R12 upgrade.  Hope it helps.


Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

2 Responses to “Be aware of R12 Data Model changes to properly assess R12 upgrade rework”

RSS Feed for Oracle Federal Applications Comments RSS Feed


if you please i want to know which tables or views will be instead of PO_VENDORS and PO_VENDOR_SITES_ALL

Where's The Comment Form?

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: