Monday, March 19, 2018

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
;

Thursday, March 15, 2018

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).