Noetix Customisations (Per Month)

We are in the final stages of completing our R11 to R12 migration project and I thought it would be useful (because someone asked) to talk about the Customisations we have made to NoetixViews during the course of the project.

These are changes that you could submit to Noetix to be covered by a Noetix Customisation Maintenance (NCM) agreement (i.e. standard changes to views/ columns).

Changes Per Month

The chart shows that we have had a peak of over 400 changes in the month before our "go-live" (which was January 2012). This number of changes is likely to be either a new view (or a couple of new views!) or some mass omitting of columns (for example those whose descriptions start with "OBSOLETE" at R12!).

It's worth noting that by definition a row that's created is also updated so there is some "doubling up" in the numbers.

For those of you interested in the RAW data here is is (via Google Docs).

Here are our top customisations per view;

The views I've highlighted in red are completely new views we had to create to fill a gap. The INV_X_Dispensing view is a new view we created because of our customised dispensing module, all the other views we have created are reporting against standard Oracle Functionality (for example the RA_Customers_All view is simply the RA_Customers view without filtering out customers).

Hopefully I'll be able to publish all our customisations at some later date.

Oracle R12: Reporting Issues With Subledger Accounting

On 1st January this year we successfully migrated our existing R11 Oracle e-Business Suite installation to R12 and went live. The huge bulk of our reports had been upgraded to deal with the changes (largely now using Noetix Views) and with only about 100 reports remaining we were expecting to have completed the migration of existing reports and the creation of any new reports by the end of June 2012 (I'm writing this at the end of the May).

One of the process changes that was made as part of the R12 upgrade project was to enable some rules in Subledger Accounting. These are fairly simple rules and it probably helps to give an example so here goes;

A purchase order is raised for Item 7426-4 and then journalled into the GL (via the SLA). The original GL code defined with the purchase order line is;

2.001.9999.0000.0000.0000

It's not really important what the segments mean, but suffice to say when the final transaction is posted to the GL the account code has changed (by an SLA rule) to;

2.000.11111.0000.FOIL.0000

Looking at the lines in the GL (in Oracle) we see;

GL Journal Lines Showing the New Account Segments

From a Finance perspective this is good news as it dramatically reduces the chance of something being miss-coded by ignoring the codes the user has entered and replacing them with some pre-defined values.

However from a reporting standpoint this is a complete and total nightmare.

Why? Because the GL account code changes are not rolled back into the values defined for the Purchase Order. This means that if we are using the GL Account code in order to break down spending, which we are, then the only "true" value is in the GL.

On it's own this wouldn't be an issue except when you combine it with two others;

1. Subledger records in XLA_Distribution_Links only allow a GL Line to be linked to a *single* external entity. Thus if, for example, you want GL Lines with their Purchase Orders on a report (for example a Department Spend Report) then you need to look both at XLA_Distribution_Links that are linked directly to purchase orders, then those that are linked to invoices (which in turn are linked to purchase orders), then those lines which are linked to inventory transactions and then linked to purchase orders, etc. In short a relatively simple query against PO.PO_DISTRIBUTIONS_ALL becomes a complete nightmare of multiple UNIONS
2. The relationship between GL_JE_Lines and PO_Distributions_All isn't one-to-one. The tax line of distributions are merged into a single GL Line which means in XLA_Distribution_Links that single GL line is linked to every line on the purchase order.

Does anyone have a solution to this problem?!

Noetix: Extracting All Changes To a NoetixView To A Single File

NOTE: Last update, adding in some more information to the report, new version 0.9.2. It's now a requirement that you use this script to Omit Columns (makes the SQL much more readable, and reduces the amount of code that needs to be updated when Noetix update their processes - as they did at 6.0.2).

The purpose of this script is to combine all the changes made by a specific group of users to a single NoetixView template (in the tables N_View_Column_Templates, N_View_Table_Templates, and N_View_Where_Templates) into a single file split into four sections according to the template below;

-- NOETIX VIEW UPDATE TEMPLATE V0.9.1
-- NOTE: All changes to a single Noetix view should be included in one file (base view changes
--   should be in a separate file)
--
@utlspon

-- Section 1: Removing existing columns, queries, etc use a single update where possible

-- Section 2: Table (and Where clause) additions. Group by the change, not the type (i.e. add the
--   first table, then add the where clauses for the first table, then the second table, then the
--   where clauses for the second table, etc)

-- Section 3: Column Additions (in alphabetical order of Column_Label)

-- Section 4: Updates

COMMIT;

@utlspoff
 
The script is available here (via Google Docs), because it's quite long I've not copied/ pasted it into this blog.

Just to give you some idea we have approximately 3,000 files, the aim of using this script is to reduce this to a more manageable number (in the ten's).

The newly announced NoetixViews Workbench will (hopefully) further reduce the need for these legacy files.