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;
/