Lessons Learned from Migrating to the 6.5.1 NoetixViews in an Oracle Applications 12.2 Environment

I received a copy of the 6.5.1 NoetixViews back in late March of this year. We needed to be on a version of the NoetixViews which will be certified with 12.2.5.

Right now we have completed our 2nd round of UAT and various things are still popping up.

Is there a big lesson learned from this migration?

We have lots of customizations (close to 200) with a lot of custom objects referenced. What I noticed is that my custom scripts (xu2 and xu5) that referenced custom objects needed to be properly referenced in the template tables (where applicable).

My convention up until the 6.5.1 NoetixViews with 12.2.5 Oracle Apps was to create a synonym in APPS and reference the object in n_view_table_templates through this synonym.  With 6.5.1, I just confirmed that the custom schema source was properly registered as an Oracle Application and then referenced the table directly from its schema.

This was my greatest pain point.

The different objects that are now associated with an Oracle Apps table (even a custom table owned by a registered schema) have ancillary objects as part of the edition based redefinition construct which is implemented with Oracle Applications 12.2.

Specifically, each table will have an edition based redefintion view owned in its schema and an APPS owned synonym.

Once the views are instantiated in the 12.2 EBS environment, they will then point at the edition based redefinition view associated with the table. This is true of both standard Oracle Apps tables and custom tables in registered Oracle Applications schemas.

With these new objects, the NVA will expect the Noetix admin database account to have grant select with grant option for all of the custom objects referenced in n_view_templates that are associated with the custom tables.


Last year I performed a 6.0.1 to 6.4.1 NoetixViews migration and this year I performed a 6.4.1 to a 6.5.1 NoetixView migration.  Because we are also upgrading Oracle Apps from 12.1.3 to 12.2.5 in this migration, personally, this was more work (and more risk).

Digression: Oracle Apps Descriptive Flexfield Query with Assigned Flexfield Value Sets

Much has happened since my last entry.
-completed a 6.0.1 to 6.4.1 Noetix View migration
-now have global views for everything in our Oracle Apps stack
-in UAT for a 6.4.1 to 6.5.1 Noetix View migration (migrating from 12.1.3 to 12.2.5 Oracle Apps concurrently)
-purchased Noetix Workbench

Many lessons have been learned.  Many tickets submitted to Magnitude. With the tickets to Magnitude, I had an opportunity to work with various levels of support (front line staff who were very helpful to senior engineers).

I will have to write about these details at some point.

Today, I have a reporting requirement pertaining to descriptive flexfields where the compiled usage is a date with a certain mask (no value set assigned though). 

Usually, I have created xu5 scripts to cast these dates as dates (though it is important to know the mask used).

Anyways, I did not have a DFF query with flex value sets associated, nor could I find one so here is my ANSI special query (I practice ANSI standard for practice and fun):

SELECT fcu.*,
  fcv.*,
 ffvs.*
FROM applsys.fnd_descr_flex_contexts fcv
JOIN applsys.fnd_application_tl A
ON fcv.application_id = A.application_id
AND A.language        = 'US'
JOIN applsys.fnd_descr_flex_column_usages fcu
ON fcv.application_id                 = fcu.application_id
AND fcv.descriptive_flexfield_name    = fcu.descriptive_flexfield_name
AND fcv.descriptive_flex_context_code = fcu.descriptive_flex_context_code
JOIN applsys.fnd_descr_flex_col_usage_tl fcut
ON fcu.application_id                 = fcut.application_id
AND fcu.descriptive_flexfield_name    = fcut.descriptive_flexfield_name
AND fcu.descriptive_flex_context_code = fcut.descriptive_flex_context_code
AND fcu.application_column_name       = fcut.application_column_name
AND fcut.language                     = 'US'
LEFT OUTER JOIN applsys.fnd_flex_value_sets ffvs
ON ffvs.flex_value_set_id = fcu.flex_value_set_id
WHERE A.application_name LIKE :application_name
  ||'%'
AND fcu.descriptive_flexfield_name LIKE '%'
  || :dff_name_eg_OE_LINE_ATTRIBUTES
  || '%'
AND fcut.form_left_prompt LIKE '%'
  || :dff_form_left_prompt
  ||'%'
ORDER BY fcut.descriptive_flexfield_name,
  fcut.application_column_name ;