Organization Refresh - Noetix View

Back in the olden days of the NoetixViews, one would need to run NoetixView regeneration to refresh the organizations that are identified in  `N_APPLICATION_ORG_MAPPINGS` which is used in many ways throughout the NoetixViews environment and is a key component of the the security construct.  In the 6.5 version of NoetixViews, the staff at Magnitude were prescient and added the "Refresh Org" feature.

What is it?

Simple script invocation which updates the `N_APPLICATION_ORG_MAPPINGS` table.


How does one do this?

Just review the notes in the NoetixView Administrator Guide (e.g. for 6.5.1, it is page 192 in the section, Running Organization Refresh Using Script.


-Open the Noetix Script home (install file where all the installation scripts reside)

-Start a SQL*Plus session

-Execute instorgr.sql

-As a check, one can perform a query of the `N_APPLICATION_ORG_MAPPINGS` table (before and after).

Another Discoverer Digression: Changing the Ownership of a Discoverer Scheduled Workbook to Change a Scheduled Report Attribute

The principle of this approach is to identify a scheduled workbook, change the ownership of the scheduled workbook and then modify the schedule.  Typically, the need for this scenario is most common for an employ whom has quit and the schedule is no longer desired.

1.       Get existing workbook and scheduled workbook details

Log in as eul5_us@db:

SELECT doc_owner.user_name,

  doc_owner.eu_id,

  docs.doc_eu_id,

  docs.doc_id,

  docs.doc_name,

  docs.doc_developer_key,

  docs.doc_updated_date,

  docs.doc_batch

FROM eul5_us.eul5_documents docs,

  (SELECT fu.user_name,

    euluser.eu_id

  FROM eul5_us.eul5_eul_users euluser,

    applsys.fnd_user fu

  WHERE euluser.eu_username = '#'

    || TO_CHAR(fu.user_id)

  AND fu.user_name =      nvl(:user_name,fu.user_name)

  ) doc_owner

WHERE docs.doc_eu_id = doc_owner.eu_id

AND docs.doc_name LIKE '%'||:doc_name ||'%'

ORDER BY  docs.doc_name, docs.doc_updated_date;

-the column, doc_batch is a Boolean identifying whether the workbook is a scheduled workbook or not.

2.       Be aware of the access privileges of the reports which reside in eul5_access_privs

3.       Update the workbook and the scheduled workbook

 update eul5_us.eul5_documents

set doc_eu_id = :l_doc_eu_id_mine

where

doc_id = :doc_id

4.       One should tackle one scheduled report at a time.  Modify the subquery to be specific associated with the report you need/want to modify.

Here is a permutation of the subquery (predicate the same):

SELECT

    ebt.bqt_id,

    ebt.bqt_bq_id,

    ebt.bqt_brr_id,

    ebt.bqt_table_name,

    ebt.bqt_element_state,

    ebr.brr_id,

    ebr.brr_br_id,

    ebr.brr_run_number,

    ebr.brr_state,

    br.br_name,

    br.br_workbook_name,

    br.br_eu_id,

    br.br_rfu_id,

    br.br_created_by,

    ebr.brr_br_id

FROM

    eul5_us.eul5_bq_tables ebt

    JOIN eul5_us.eul5_br_runs ebr ON ebt.bqt_brr_id = ebr.brr_id

    JOIN eul5_us.eul5_batch_reports br ON ebr.brr_br_id  = br.br_id

    WHERE

    1 = 1

    AND   (ebt.bqt_table_name LIKE :b_schedule_schema_table_name || 'R%'

    OR br.br_workbook_name LIKE :b_workbook_name || '%')

     ORDER BY ebr.brr_run_number;

Update the batch scheduled report ownership

UPDATE eul5_batch_reports br

    SET

        br.br_eu_id =:l_doc_eu_id_mine_308244

WHERE

    br.br_id IN (

        SELECT DISTINCT

            ebr.brr_br_id

        FROM

            eul5_us.eul5_bq_tables ebt

            JOIN eul5_us.eul5_br_runs ebr ON ebt.bqt_brr_id = ebr.brr_id

            JOIN eul5_us.eul5_batch_reports br ON ebr.brr_br_id = br.br_id

        WHERE

            1 = 1

            AND   (

                ebt.bqt_table_name LIKE :b_schedule_schema_table_name

                || 'R%'

                OR    br.br_workbook_name LIKE :b_workbook_name

                || '%'

            )

    );


Make all the changes to the scheduled workbook (e.g. delete).

5.      Change back the ownership of the non-scheduled workbook

select

update eul5_us.eul5_documents

set doc_eu_id = :l_doc_eu_id  

where

doc_id = :doc_id

;

Preserving Custom View Joins after the Noetix EUL Generator Has Executed

When I migrated to the 6.4.1 Noetix Views, I noticed that my custom view joins get blown away after a Noetix EUL regeneration.

What follows are my cursory annotations associated with how to re-create these joins via a script.

Please do not laugh that someone is using Oracle Discoverer in 2018.

Many salient aspects of custom Discoverer joins can be queried as follow:

        SELECT

            ekc.key_id,

            eexp.exp_id,

            to_number(regexp_substr(eexp.exp_formula1,'\[\d,(\d+)\]',1,2,NULL,1) ) pk_key_id,

            to_number(regexp_substr(eexp.exp_formula1,'\[\d,(\d+)\]',1,3,NULL,1) ) fk_key_id

        FROM

            eul5_key_cons ekc

            JOIN eul5_expressions eexp ON ekc.key_id = eexp.jp_key_id

                                          AND eexp.exp_type = 'JP'

        WHERE

            1 = 1

            AND   ekc.key_type = 'FK'

            AND   ekc.key_developer_key = 'ZARCS_CS_COMMISSION_RATES_JOIN_TO_ARX0_TRANSACTION_LINE_DTLS';

I just make sure I use the developer key to find them. 

In re-creating a Discoverer join, I just go to an instance where these joins still exist.

The steps for single column, rowid, driven joins is pretty straightforward. These notes are not comprehensive and are oriented towards this scenario which is prevalent with the Noetix Views.

Join Steps

-----------

1. create an eul5_key_cons record. This contains the naming the folder being joined and the nature of join.

2. create an eul5_expressions record. This has vital information about the columns being joined.

3. create two records in the table, eul5_exp_deps, associated with identifying primary and foreign key identification for the join. 

For existing records, I just navigate to a pre-eul generated environment and query the key_developer_key like above to get the eul5_key_cons record. With SQL Developer, one can easily export the details of the record as an insert statement.  This is what I did and then I modified some of the information (like the key_id). I thought of doing a "create table as a select statement", but I am biased against creating additional objects in the database for this purpose (it would probably make things simpler).

Here is an example of an insert statement for a join:

INSERT INTO eul5_key_cons (

    key_id,

    key_type,

    key_name,

    key_developer_key,

    key_description,

    key_ext_key,

    key_obj_id,

    uk_primary,

    fk_key_id_remote,

    fk_obj_id_remote,

    fk_one_to_one,

    fk_mstr_no_detail,

    fk_dtl_no_master,

    fk_mandatory,

    key_user_prop2,

    key_user_prop1,

    key_element_state,

    key_created_by,

    key_created_date,

    key_updated_by,

    key_updated_date,

    notm

) VALUES (

    eul5_id_seq.nextval,  --key_id

    'FK',  --key_type

    'Z$ARCS_CS_Commission_Rates join to ARX0_Transaction_Line_Dtls',  --key_name

    'ZARCS_CS_COMMISSION_RATES_JOIN_TO_ARX0_TRANSACTION_LINE_DTLS',  --key_developer_key

    'Z$ARCS_CS_Commission_Rates join to ARX0_Transaction_Line_Dtls',  --key_description

    NULL,  --key_ext_key

    130292,  --key_obj_id

    NULL,  --uk_primary

    NULL,  --fk_key_id_remote

    135346,  --fk_obj_id_remote

    0,  --fk_one_to_one

    0,  --fk_mstr_no_detail

    1,  --fk_dtl_no_master

    0,  --fk_mandatory

    NULL,  --key_user_prop2

    NULL,  --key_user_prop1

    0,  --key_element_state

    'EUL5_US',  --key_created_by

    TO_DATE('20-APR-17','DD-MON-RR'),  --key_created_date

    'EUL5_US',  --key_updated_by

    TO_DATE('20-APR-17','DD-MON-RR'),  --key_updated_date

    0  --notm

);  --VALUES

Next, I obtained information for eul5_expressions record.

DECLARE

    v_eul5_key_cons_key_id   eul5_us.eul5_key_cons.key_id%TYPE;

BEGIN

    SELECT

        ekc.key_id

    INTO

        v_eul5_key_cons_key_id

    FROM

        eul5_key_cons ekc

    WHERE

        1 = 1

        AND   ekc.key_type = 'FK'

        AND   ekc.key_developer_key = 'ZARCS_CS_COMMISSION_RATES_JOIN_TO_ARX0_TRANSACTION_LINE_DTLS';

    INSERT INTO eul5_expressions (

        exp_id,

        exp_type,

        exp_name,

        exp_developer_key,

        exp_description,

        exp_formula1,

        exp_data_type,

        exp_sequence,

        it_dom_id,

        it_obj_id,

        it_doc_id,

        it_format_mask,

        it_max_data_width,

        it_max_disp_width,

        it_alignment,

        it_word_wrap,

        it_disp_null_val,

        it_fun_id,

        it_heading,

        it_hidden,

        it_placement,

        it_user_def_fmt,

        it_case_storage,

        it_case_display,

        it_ext_column,

        it_descriptor_id,

        ci_it_id,

        ci_runtime_item,

        par_multiple_vals,

        co_nullable,

        p_case_sensitive,

        jp_key_id,

        fil_obj_id,

        fil_doc_id,

        fil_runtime_filter,

        fil_app_type,

        fil_ext_filter,

        par_optional,

        par_lov_desc_ids,

        exp_user_prop2,

        exp_user_prop1,

        exp_element_state,

        exp_created_by,

        exp_created_date,

        exp_updated_by,

        exp_updated_date

    ) VALUES (

        eul5_id_seq.NEXTVAL,

        'JP',

        'Join Predicate',

        'JOIN_PREDICATE',

        NULL,

        '[1,81]([6,135432],[6,130303])',

        10,

        1,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        1,

        v_eul5_key_cons_key_id,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        NULL,

        0,

        'EUL5_US',

        TO_DATE('20-APR-17','DD-MON-RR'),

        'EUL5_US',

        TO_DATE('20-APR-17','DD-MON-RR')

    );

The exp_formula1 value keeps track of the columns being joined.  With an EUL Generation, these column values are preserved during a regeneration.

For the eul5_exp_deps records, I just create quick annonymous block a preceding select into statement which grabs crucial information to populate in this table.

-- 'ZARCS_CS_COMMISSION_RATES_JOIN_TO_ARX0_TRANSACTION_LINE_DTLS'

    BEGIN

        v_eul5_exp_deps_ed_id1 := eul5_id_seq.nextval;

        v_eul5_exp_deps_ed_id2 := eul5_id_seq.nextval;

        SELECT

            ekc.key_id,

            eexp.exp_id,

            to_number(regexp_substr(eexp.exp_formula1,'\[\d,(\d+)\]',1,2,NULL,1) ) pk_key_id,

            to_number(regexp_substr(eexp.exp_formula1,'\[\d,(\d+)\]',1,3,NULL,1) ) fk_key_id

        INTO

            v_eul5_key_cons_key_id,v_eul5_expressions_jp_exp_id,v_eul5_exp_exp_pk,v_eul5_exp_exp_fk

        FROM

            eul5_key_cons ekc

            JOIN eul5_expressions eexp ON ekc.key_id = eexp.jp_key_id

                                          AND eexp.exp_type = 'JP'

        WHERE

            1 = 1

            AND   ekc.key_type = 'FK'

            AND   ekc.key_developer_key = 'ZARCS_CS_COMMISSION_RATES_JOIN_TO_ARX0_TRANSACTION_LINE_DTLS';

        dbms_output.put_line('v_eul5_exp_exp_pk => ' || v_eul5_exp_exp_pk);

        dbms_output.put_line('v_eul5_exp_exp_fk => ' || v_eul5_exp_exp_fk);

        dbms_output.put_line('v_eul5_expressions_jp_exp_id => ' || v_eul5_expressions_jp_exp_id);

        v_eul5_exp_deps_ed_id1 := eul5_id_seq.nextval;

        v_eul5_exp_deps_ed_id2 := eul5_id_seq.nextval;

        INSERT INTO eul5_exp_deps (

            ed_id,

            ed_type,

            pd_p_id,

            ped_exp_id,

            pfd_fun_id,

            psd_sq_id,

            cd_exp_id,

            cfd_fun_id,

            cid_exp_id,

            ed_element_state,

            ed_created_by,

            ed_created_date,

            ed_updated_by,

            ed_updated_date,

            notm

        ) VALUES (

            v_eul5_exp_deps_ed_id1,

            'PED',

            v_eul5_expressions_jp_exp_id,

            v_eul5_exp_exp_pk,

            NULL,

            NULL,

            NULL,

            NULL,

            NULL,

            0,

            'EUL5_US',

            v_date,

            'EUL5_US',

            v_date,

            0

        );

        INSERT INTO eul5_exp_deps (

            ed_id,

            ed_type,

            pd_p_id,

            ped_exp_id,

            pfd_fun_id,

            psd_sq_id,

            cd_exp_id,

            cfd_fun_id,

            cid_exp_id,

            ed_element_state,

            ed_created_by,

            ed_created_date,

            ed_updated_by,

            ed_updated_date,

            notm

        ) VALUES (

            v_eul5_exp_deps_ed_id2,

            'PED',

            v_eul5_expressions_jp_exp_id,

            v_eul5_exp_exp_fk,

            NULL,

            NULL,

            NULL,

            NULL,

            NULL,

            0,

            'EUL5_US',

            v_date,

            'EUL5_US',

            v_date,

            0

        );

    EXCEPTION

        WHEN OTHERS THEN

            v_error_code := sqlcode;

            v_error_message := sqlerrm;

            dbms_output.put_line(TO_CHAR(v_error_code)

            || '- '

            || v_error_message);

    END;

----------------------------------------------------------

My error handling is not very remarkable (I will probably make this better at some point).

Discoverer Digression - Quickly Querying Columns Used in a Discoverer Worksheet

I suppose for many people out there, you probably are in awe that people are still running Discoverer in 2018.

Anyways, there is not a simple way to identify columns used in a Discoverer report.  There is a built in function, eul5_get_item_name, but it is a function and not easy to query against.

For migration planning and for the purposes of addressing risk to changes (e.g. we modify the compiled DFFs for an Oracle Applications table and we need to know what is affect and have the workbooks changed).

Here is my simple approach.

1. Create a table from the qpp stats table with the column information included.


CREATE TABLE eul5_us.eul5_document_columns
    AS
        ( SELECT
            qs_doc_owner,
            qs_doc_name,
            qs_doc_details,
            eul5_us.eul5_get_object_name(eul5_us.eul5_get_item_name(qs_id,'I') column_list
          FROM
            eul5_us.eul5_qpp_stats
          WHERE
            qs_created_date >= SYSDATE - 365
        );


2. I did not add indices to this table, but I suppose I could (this is left as an excercise for you).

3. Start querying the table.  I performed these steps in a non-production environment.  This is a snapshot and this type of inquiry probably is needed periodically until a complete migration has been done.

Here is an example of a DFF column which is changing and I need to know what reports were ran in the last year which use the columns being affected:



SELECT
    dc.*
FROM
    eul5_us.eul5_document_columns dc
WHERE
    column_list LIKE '%VEND$Authority_Number%'
    OR   column_list LIKE '%VEND$ATTRIBUTE_CATEGORY%';

Using vpd security, need a number of Oracle Responsibilities to Write OBIEE Analysis

Since we have vpd security set-up for the NoetixViews, I want to assign myself the appropriate responsibilities to access the appropriate subject areas I need. Next, I execute the Noetix Security Update concurrent program.

Eventually, I will create a FNDLOADER script with all the responsibilities I need in cloned environments, but in the interim; I created an anonymous block to cycle through a set of Oracle responsibilities (minus the responsibilities which I have).



DECLARE
    v_fnd_user_name            fnd_user.user_name%TYPE;
    v_error_code               NUMBER(38);
    v_error_message            VARCHAR2(200);
BEGIN

    v_fnd_user_name := 'EBS_USERNAME';
    FOR usr IN (
        WITH diff AS (
            SELECT
                a.application_short_name,
                r.responsibility_key
            FROM
                fnd_application a
                JOIN fnd_responsibility r ON a.application_id = r.application_id
            WHERE
                    1 = 1
                AND
                    r.responsibility_key LIKE '%DESIRED_PATTERN' --escape '\'
            MINUS
            SELECT
                a.application_short_name,
                r.responsibility_key
            FROM
                fnd_user u
                JOIN fnd_user_resp_groups g ON u.user_id = g.user_id
                JOIN fnd_application a ON g.responsibility_application_id = a.application_id
                JOIN fnd_responsibility r ON a.application_id = r.application_id
            WHERE
                    g.responsibility_id = r.responsibility_id
                AND
                    u.user_name = v_fnd_user_name
        ) SELECT
            diff.application_short_name,
            diff.responsibility_key
        FROM
            diff
    ) LOOP
        BEGIN
            dbms_output.put_line('username => '
             || v_fnd_user_name
             || ','
             || 'resp_app  => '
             || usr.application_short_name
             || ','
             || 'resp_key  => '
             || usr.responsibility_key
             || ','
             || 'security_group => STANDARD');

            fnd_user_pkg.addresp(
                username         => v_fnd_user_name,
                resp_app         => usr.application_short_name,
                resp_key         => usr.responsibility_key,
                security_group   => 'STANDARD',
                description      => NULL,
                start_date       => SYSDATE,
                end_date         => SYSDATE + 100
            );

        EXCEPTION
            WHEN OTHERS THEN
                v_error_code := sqlcode;
                v_error_message := sqlerrm;
                dbms_output.put_line(TO_CHAR(v_error_code) || '- ' || v_error_message);
        END;
    END LOOP;
END;
/

Performance Tuning OBIEE Analysis Pointing at NoetixViews Part 1

One can look at the SQL hitting the Oracle EBS database which the NoetixView's connection pool is pointing at with a query such as this:



SELECT
*
FROM
v$sql
WHERE
parsing_schema_name IN (
'XXNOETIX'
)
AND
module = 'nqsserver@server_name (TNS V1-V3)'
ORDER BY last_active_time DESC;

 I was looking for an analysis I was working on that was querying the qa_collection_plan_rslts view.  With a little perusing of my result set, I identified the query I want to performance tune:

WITH
SAWITH0 AS (select distinct T3343494.Collection_Elem_Result_Value as c1,
T3343494.Collection_Element_Name as c2,
T3343494.Collection_Plan_Active_Flag as c3,
T3343494.Collection_Plan_Name as c4,
T3343494.Job_Name as c5,
T3343494.Organization_Name as c6,
T3343494.Result_Entered_By_User_Name as c7,
T3343494.Result_Entry_Date as c8,
T3343494.Result_Last_Update_Date as c9
from
XXNOETIX.QA_COLLECTION_PLAN_RSLTS T3343494
where ( T3343494.Collection_Element_Name = 'Comments' and T3343494.Organization_Name = 'org_name' and T3343494.Job_Name = 'job_name' and T3343494.Collection_Plan_Name like 'CW%' ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6, D1.c7 as c7, D1.c8 as c8, D1.c9 as D1.c9 as c13 from ( select 0 as c1,
D1.c1 as c2,
D1.c2 as c3,
D1.c3 as c4,
D1.c4 as c5,
D1.c5 as c6,
D1.c6 as c7,
D1.c7 as c8,
D1.c8 as c9,
D1.c9 as c10
from
SAWITH0 D1
where rownum <= 100001

;

It is interesting to see the common table expression that OBIEE creates based on the analysis.

The next steps I usually take is to look at the refresh frequency of the statistics of the tables and start looking at the explain plan.

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 ;

Noetix 6.0.1 to 6.4.1 Noetix Views Migration ~ Running a Seeded Regeneration

I have been busy working on my Noetix 6.0.1 to 6.4.1 Noetix Views migration and I am supposed to crank through this project pretty quickly. I have been setting up my test environment.  My approach to setting-up my 6.4.1 Noetix View schema is as follows:

• Run a 6.4.1 Noetix Views regeneration “out of the box”on an existing Noetix schema (6.0.1 to be exact).
• Run a 6.4.1 Noetix Views regeneration with all my xu2 hook scripts.
• Run a 6.4.1 Noetix Views regeneration with all my xu5 hook scripts.

With the “out of the box” regeneration, I noticed that popview.sql and ycrcal.sql appear to have bugs (Magnitude supplied me with a corrected popview.sql script).

Here is the error for the ycrcal.sql:

6 insert into noetix_calendar
7 ( effective_date,
8 month,
9 day,
10 year,
11 week_of_year,
12 quarter,
13 julian_day,
14 month_of_year,
15 week_of_month,
16 day_of_month,
17 day_of_week )
18 values
19 ( l_effective_date,
20 TRIM(to_char(l_effective_date,'MONTH')),
21 TRIM(to_char(l_effective_date,'DAY')),
22 to_number(to_char(l_effective_date,'YYYY')),
23 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'WW')),
24 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'Q')),
25 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'J')),
26 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'MM')),
27 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'W')),
28 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'DD')),
29 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'D')));
30 l_effective_date := l_effective_date + 1;
31
32 exit when l_effective_date = to_date('01-JAN-2100','DD-MON-YYYY') ;
33 end loop;
34 commit;
35 end;
36 /
declare
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 6

This install script is taking l_effective_date, which is a date and casting it as date (I have not seen that done successfully nor is this a great idea).

Here is a reproduction of that error in a more trivial example:

NOETIX_SYS@erp>select to_date(sysdate,'dd-mon-yyyy')
2 from
3 dual;
select to_date(sysdate,'dd-mon-yyyy')
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

When I correct this casting of a date as a date issue, the script looks like this:

6 insert into noetix_calendar
7 ( effective_date,
8 month,
9 day,
10 year,
11 week_of_year,
12 quarter,
13 julian_day,
14 month_of_year,
15 week_of_month,
16 day_of_month,
17 day_of_week )
18 values
19 (l_effective_date,
20 to_char(l_effective_date,'MONTH'),
21 to_char(l_effective_date,'DAY'),
22 to_number(to_char(l_effective_date,'YYYY')),
23 to_number(to_char(l_effective_date,'WW')),
24 to_number(to_char(l_effective_date,'Q')),
25 to_number(to_char(l_effective_date,'J')),
26 to_number(to_char(l_effective_date,'MM')),
27 to_number(to_char(l_effective_date,'W')),
28 to_number(to_char(l_effective_date,'DD')),
29 to_number(to_char(l_effective_date,'D')));

Presently, I am waiting for Magnitude to acknowledge the problem and fix it.

Capturing Errors in XU2 Scripts Early in Stage 4 of a View Regeneration Using MS PowerShell

--This is a re-post

I posted on this previously and documented a MS DOS command to find errors in my XU2 scripts (here).The xu2 scripts are invoked during stage 4 of the Noetix View Administrator's regeneration process.  This is right after SQL Loader is finished loading seeded template table records. The errors that are thrown during this part of the regeneration are typically associated with constraint errors.  If you examine the constraints that are associated with a given Noetix template table, one can see that they are quite robustly written to help make sure that you do not add a record in an erroneous way.

One can typically examine the root cause of these errors if one looks at this query:

SELECT *
  FROM DBA_CONS_COLUMNS
 WHERE 1          = 1
   AND OWNER      = 'NOETIX_SYS'
   AND TABLE_NAME = 'N_VIEW_COLUMN_TEMPLATES';


Today I wanted to document another way to find these errors.  More than a year ago, I noticed that Microsoft had an updated shell, called PowerShell, and so I placed it on my laptop and did not do anything with it. I noticed that it was quite different from MS Dos and I looked at it for a little bit and talked to some peers.  My peers heard of it, but thought that it really had gained little traction in terms of use by administrators and other IT staff. For one reason or another, I stumbled on the blog, http://blogs.technet.com/b/heyscriptingguy/, and it really made me reconsider using this shell environment in lieu of MS DOS where practical.

Anyway, the two main commands one needs to know in this shell are as follows:

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Get-Alias  MS_DOS_Command

Notes on this:  This cmdlet just helps you translate your MS DOS command to the PowerShell cmdlet.

Here is an example:

PS C:\WINDOWS\system32\windowspowershell\v1.0> Get-Alias cd

This returns:

CommandType     Name                                                                                           Definition                                                                                    

-----------     ----                                                                                           ----------                                                                                    

Alias           cd                                                                                             Set-Location         

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The other command that is necessary is the following:

Get-Help cmdlet

Today, I will go through how one can invoke a command (well, actually a cmdlet) to find Oracle thrown errors that occur while the Noetix View Administrator is in stage 4 of a regeneration.


First, a little background information on PowerShell.

It is rather nice because it is object oriented and its piping is similar to Unix/Linux.  Specifically, the piping can return objects and then the next cmdlet (that is what they call their commands in this new shell) manipulates it, one object at a time through the pipe kind of like an embedded function.  This is very dissimilar to MS DOS where it completes the first command and then pushes the result set to the next command.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Anyways, I use this shell as my main shell that I use for various tasks.  Here is my cmdlet to watch for errors being thrown right before GUI prompt for the APPS password (which is right after the wnoetxu2.sql script has run):

Get-ChildItem -Path "\\cmntx02\NoetixViews 6.0.1 - ERP\Installs\NOETIX_SYS_erpdev" -Filter "*xu2.lst" | Select-String -pattern "ORA-"

Explanation: 

  

  The Get-ChildItem cmdlet gets the items in one or more specified locations. In this context it obtains all the xu2.lst spooled files and pipes the results to the Select-String cmdlet which finds matches to the pattern, "ORA-".

I invoke this script on my laptop and it peruses the files in the path I have identified.  I suppose you might take a look at this script and think; I will never type a path that long!  Well, if you are not aware, you can drag a file from MS explorer to your shell environment and Windows will “type it in” for you.