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.