Query to examine security manager settings of Noetix Query Users whom are authenticated EBS users accounts (type A)

I wanted to be cognizant of how the security manager setting are set-up in the Noetix schema so I created this query to compare and analyze EBS user accounts (type A).

Here is a query showing EBS users security manager settings:

select
    fu.user_name,
    u.user_name user_id,
    am.attribute_code,
    am.module_code,
    ua.*
from
    n_sm_user_attributes ua join n_sm_attribute_modules am on ua.attribute_module_id = am.attribute_module_id
    join n_security_mgr_users u on ua.sm_user_id = u.sm_user_id and ua.sm_user_id = u.sm_user_id
    join apps.fnd_user fu on to_number(ltrim(u.user_name,'#')) = fu.user_id
where
    1=1
and
    fu.user_name = :b_user_name
and
    u.user_type = 'A'
    ORDER BY 1, 3, 4
    ;

Having this query facilitates viewing/comparing accounts with similar profiles for OBIEE account management.

Report fails due to a missing A$ column

The Noetix Views communicates to all of us the indices to driving tables associated with a view through the A$ columns.

We have trained our end users not to use these columns, right?  In any event, things happen and some users with creating reports privileges use these columns in the report.

Now time elapses and Oracle Applications gets updated (in our case, from 12.1.3 to 12.2.5).  To make things even more interesting, Noetix Views gets updated from version 6.0.1. to 6.4.1 to 6.5.1 in the course of 2 years.

Once these changes were implemented, some of the A$ columns no longer exists (views change, Oracle Apps changes) and the reports break.

What can be done?

1. We report in Discoverer (and we are transitioning now to OBIEE). Did you back-up the EUL or RPD from the older version? Did you save the makeview%.sql files from the installation home?  You can "resurrect" the old environment in a development environment with these backups.

2. You can create a temporary hookscript to recreate the missing columns and regenerate the RPD or EUL.

Eventually, I had better success creating a temporary hookscript to recreate the missing columns.  This was quicker and more productive.

6.5.1 NVA Generation Slow in a 12.2.5 Oracle Applications Planning Instance (ASCP), Needing Fixed Object and Data Dictionary Stats Gathered

We have our 12.2.5 Oracle Applications ERP instances and then we have our 12.2.5 Oracle Applications ASCP, the planning module, instances. 

With the 6.4.1 NVA regeneration process associated with our planning module, my NVA regeneration process (stage 2 through 4) would usually take about 15 to 20 minutes.

With 6.5.1 NVA, my regenerations were taking about 1 hour.

While watching my output from SQL*Plus (used for my NVA regeneration process), I see this text, "Default role prefixes to Oracle user names", which is from xgenall.sql script. It stays at this output line for about 1/2 hour or so.

When I look at the most recent records in v$sql, I see that w_process_user_flag.sql is being ran.


Thinking that there might be a locking issue (seen this before), I queried for the existing locked objects:

SELECT
    a.os_user_name,
    a.oracle_username,
    a.object_id,
    c.object_name,
    c.object_type
FROM
    v$locked_object a
    JOIN
        dba_objects c
    ON
        a.object_id = c.object_id;

After reviewing the lack of locks associated with the NVA as a possible source of impediments, I raised a ticket with Magnitude Software.


Their proposal was to execute running statistics for a special group of objects. Specifically, they suggested to have statistics gathered for fixed objects and data dictionary objects:



exec dbms_stats.gather_fixed_objects_stats();
exec dbms_stats.gather_dictionary_stats;



After this was executed by our dba, the performance reverted back to 15 to 20 minutes to refresh.


From a dba perspective, these objects just are not gathered very often (comes down to need). With us migrating to 12.2.5 Oracle Applications in July of 2016, many things change in the database so you would anticipate that these object could benefit from a gathering of statistics.

Monitoring Edition Based Redefinition in a 12.2.5 Oracle Applications Environment

We migrated from a NoetixViews 6.4.1 to a NoetixViews 6.5.1 environment concurrently with our Oracle Applications 12.1.3 to 12.2.5 migration.  There were many, many changes that had to be made with customizations (referencing of objects in custom NoetixViews) 

Also, the 12.2.5 environment relies on a edition based redefinition model for code moves/patching which is a dramatic departure from previous approaches.  The edition based redefinition approach allows for online patching which is in contrast to the outage dependent NVA regeneration process which Magnitude advocates be ran in the runtime edition of the database.  

One (of many approaches) to be aware of the patching process is to monitor editions associated with the database instance.

This query will provide a hierarchical perspective of the editions associated with a database instance with the last level being the current edition (aka runtime edition):

SELECT level,

de.edition_name,

de.parent_edition_name,

de.usable

FROM dba_editions de

START WITH de.edition_name = ‘ORA$BASE’

CONNECT BY PRIOR de.edition_name = de.parent_edition_name

ORDER BY de.edition_name

Here is an example of this query being ran using apps in a development instance.

apps@dev>SELECT level,

2 de.edition_name,

3 de.parent_edition_name,

4 de.usable

5 FROM dba_editions de

6 START WITH de.edition_name = ‘ORA$BASE’

7 CONNECT BY PRIOR de.edition_name = de.parent_edition_name

8 ORDER BY de.edition_name

9 /

LEVEL     EDITION_NAME     PARENT_EDITION_NAME     USABLE

—————————————————————————————————

1               ORA$BASE                                                             YES

2               V_20160703_2120   ORA$BASE                             YES

3               V_20160725_2132   V_20160703_2120                   YES

Level 3 displays to the current runtime edition.

Applies To:   Patching and code move process

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 ;