/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
 *                                                                             *
 * First enable a combination of user/role/privilege to allow the set up of    *
 * other users, roles and privileges.                                          *
 *                                                                             *
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

    /* 
        Create a role of 'super' for agent 'ULTRA' 
    */

    INSERT INTO user_role (
          user_role_id
        , user_role_name
        , agent_code
    ) (
        SELECT NULL, 'super', 'ULTRA'
        FROM   dual
        WHERE NOT EXISTS (SELECT user_role_name, agent_code FROM user_role WHERE user_role_name = 'super' AND agent_code = 'ULTRA')
    );

    /* 
    Give the role of 'super' to tagtrak_users of agent_code 'ULTRA' and group_name 'super' 
    */

    INSERT INTO tagtrak_users_role (user_id,user_role_id) 
    	                            SELECT user_id, user_role_id
                                	FROM tagtrak_users tu, user_role ur
    	                            WHERE ur.agent_code = tu.agent_code
    	                            AND user_role_name = RTRIM(group_name)
    	                            AND ur.agent_code = 'ULTRA'
    	                            AND user_role_name = 'super'
    	                            AND (user_id, user_role_id) NOT IN (SELECT user_id, user_role_id FROM tagtrak_users_role);

    /* 
        Give access to the 'MWS - User Privileges' screen to the roles belonging to agent_code 'ULTRA'
    */

    INSERT INTO user_role_priv (user_role_id,app_priv_id)
                                SELECT user_role_id, app_priv_id
                                FROM user_role, app_priv
                                WHERE user_role_name <> 'ttadmin'
                                AND agent_code = 'ULTRA'
                                AND app_priv_description = 'MWS - User Privileges'
                                AND (user_role_id, app_priv_id) NOT IN (SELECT user_role_id, app_priv_id FROM user_role_priv);

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
 *                                                                             *
 * This script allocates scanner privileges to user roles based on the scanner *
 * privileges allocated to the corresponding user_group. A user_group          *
 * corresponds to a user_role where user_role.user_role_name =                 *
 * RTRIM(user_group.group_name) and user_role.agent_code =                     *
 * user_group.agent.code.  Scanner privileges are privileges where             *
 * app_priv.app_priv_code is a non-null value which corresponds to             *
 * user_group.priv[nn] where [nn] is a number between 1 and 100.                *
 *                                                                             *
 * NOTES                                                                       *
 *                                                                             *
 * This part of the script is rerunnable and will not duplicate data           *
 * This script must run AFTER load_user_role.sql                               *
 *                                                                             *
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

    DECLARE
        i                                   BINARY_INTEGER DEFAULT 1;    -- loop counter
        scanner_priv                        app_priv.app_priv_code%TYPE;
        v_group_name                        user_group.group_name%TYPE;
        v_agent_code                        user_group.agent_code%TYPE;
        v_scanner_privs                     VARCHAR2(100);
        all_scanner_privs                   VARCHAR2(100) DEFAULT NULL;
        row_count                           PLS_INTEGER DEFAULT 0;
        priv_count                          PLS_INTEGER DEFAULT 0;

        /* 
            Cursor for all scanner privileges
        */

        CURSOR scanner_privs IS
        SELECT    
            app_priv_code
        FROM
            app_priv
        WHERE
            app_priv_code IS NOT NULL
        ORDER BY
            app_priv_code;

        /* 
            Cursor for all user groups and their scanner privileges in a concatenated string.
            If a privilege character is not found in the scanner privilege string then 
            select null else select the character.
        */

        CURSOR user_group_scanner_privs IS 
        SELECT 
             group_name,
             agent_code,
             DECODE(INSTR(all_scanner_privs,priv1),0,NULL,priv1) || DECODE(INSTR(all_scanner_privs,priv2),0,NULL,priv2)
          || DECODE(INSTR(all_scanner_privs,priv3),0,NULL,priv3) || DECODE(INSTR(all_scanner_privs,priv4),0,NULL,priv4)
          || DECODE(INSTR(all_scanner_privs,priv5),0,NULL,priv5) || DECODE(INSTR(all_scanner_privs,priv6),0,NULL,priv6)
          || DECODE(INSTR(all_scanner_privs,priv7),0,NULL,priv7) || DECODE(INSTR(all_scanner_privs,priv8),0,NULL,priv8)
          || DECODE(INSTR(all_scanner_privs,priv9),0,NULL,priv9) || DECODE(INSTR(all_scanner_privs,priv10),0,NULL,priv10)
          || DECODE(INSTR(all_scanner_privs,priv11),0,NULL,priv11) || DECODE(INSTR(all_scanner_privs,priv12),0,NULL,priv12)
          || DECODE(INSTR(all_scanner_privs,priv13),0,NULL,priv13) || DECODE(INSTR(all_scanner_privs,priv14),0,NULL,priv14)
          || DECODE(INSTR(all_scanner_privs,priv15),0,NULL,priv15) || DECODE(INSTR(all_scanner_privs,priv16),0,NULL,priv16)
          || DECODE(INSTR(all_scanner_privs,priv17),0,NULL,priv17) || DECODE(INSTR(all_scanner_privs,priv18),0,NULL,priv18)
          || DECODE(INSTR(all_scanner_privs,priv19),0,NULL,priv19) || DECODE(INSTR(all_scanner_privs,priv20),0,NULL,priv20)
          || DECODE(INSTR(all_scanner_privs,priv21),0,NULL,priv21) || DECODE(INSTR(all_scanner_privs,priv22),0,NULL,priv22)
          || DECODE(INSTR(all_scanner_privs,priv23),0,NULL,priv23) || DECODE(INSTR(all_scanner_privs,priv24),0,NULL,priv24)
          || DECODE(INSTR(all_scanner_privs,priv25),0,NULL,priv25) || DECODE(INSTR(all_scanner_privs,priv26),0,NULL,priv26)
          || DECODE(INSTR(all_scanner_privs,priv27),0,NULL,priv27) || DECODE(INSTR(all_scanner_privs,priv28),0,NULL,priv28)
          || DECODE(INSTR(all_scanner_privs,priv29),0,NULL,priv29) || DECODE(INSTR(all_scanner_privs,priv30),0,NULL,priv30)
          || DECODE(INSTR(all_scanner_privs,priv31),0,NULL,priv31) || DECODE(INSTR(all_scanner_privs,priv32),0,NULL,priv32)
          || DECODE(INSTR(all_scanner_privs,priv33),0,NULL,priv33) || DECODE(INSTR(all_scanner_privs,priv34),0,NULL,priv34)
          || DECODE(INSTR(all_scanner_privs,priv35),0,NULL,priv35) || DECODE(INSTR(all_scanner_privs,priv36),0,NULL,priv36)
          || DECODE(INSTR(all_scanner_privs,priv37),0,NULL,priv37) || DECODE(INSTR(all_scanner_privs,priv38),0,NULL,priv38)
          || DECODE(INSTR(all_scanner_privs,priv39),0,NULL,priv39) || DECODE(INSTR(all_scanner_privs,priv40),0,NULL,priv40)
          || DECODE(INSTR(all_scanner_privs,priv41),0,NULL,priv41) || DECODE(INSTR(all_scanner_privs,priv42),0,NULL,priv42)
          || DECODE(INSTR(all_scanner_privs,priv43),0,NULL,priv43) || DECODE(INSTR(all_scanner_privs,priv44),0,NULL,priv44)
          || DECODE(INSTR(all_scanner_privs,priv45),0,NULL,priv45) || DECODE(INSTR(all_scanner_privs,priv46),0,NULL,priv46)
          || DECODE(INSTR(all_scanner_privs,priv47),0,NULL,priv47) || DECODE(INSTR(all_scanner_privs,priv48),0,NULL,priv48)
          || DECODE(INSTR(all_scanner_privs,priv49),0,NULL,priv49) || DECODE(INSTR(all_scanner_privs,priv50),0,NULL,priv50)
          || DECODE(INSTR(all_scanner_privs,priv51),0,NULL,priv51) || DECODE(INSTR(all_scanner_privs,priv52),0,NULL,priv52)
          || DECODE(INSTR(all_scanner_privs,priv53),0,NULL,priv53) || DECODE(INSTR(all_scanner_privs,priv54),0,NULL,priv54)
          || DECODE(INSTR(all_scanner_privs,priv55),0,NULL,priv55) || DECODE(INSTR(all_scanner_privs,priv56),0,NULL,priv56)
          || DECODE(INSTR(all_scanner_privs,priv57),0,NULL,priv57) || DECODE(INSTR(all_scanner_privs,priv58),0,NULL,priv58)
          || DECODE(INSTR(all_scanner_privs,priv59),0,NULL,priv59) || DECODE(INSTR(all_scanner_privs,priv60),0,NULL,priv60)
          || DECODE(INSTR(all_scanner_privs,priv61),0,NULL,priv61) || DECODE(INSTR(all_scanner_privs,priv62),0,NULL,priv62) 
          || DECODE(INSTR(all_scanner_privs,priv63),0,NULL,priv63)
          || DECODE(INSTR(all_scanner_privs,priv64),0,NULL,priv64)
          || DECODE(INSTR(all_scanner_privs,priv65),0,NULL,priv65)
          || DECODE(INSTR(all_scanner_privs,priv66),0,NULL,priv66)
          || DECODE(INSTR(all_scanner_privs,priv67),0,NULL,priv67)
          || DECODE(INSTR(all_scanner_privs,priv68),0,NULL,priv68)
          || DECODE(INSTR(all_scanner_privs,priv69),0,NULL,priv69)
          || DECODE(INSTR(all_scanner_privs,priv70),0,NULL,priv70)
          || DECODE(INSTR(all_scanner_privs,priv71),0,NULL,priv71)
          || DECODE(INSTR(all_scanner_privs,priv72),0,NULL,priv72)
          || DECODE(INSTR(all_scanner_privs,priv73),0,NULL,priv73)
          || DECODE(INSTR(all_scanner_privs,priv74),0,NULL,priv74)
          || DECODE(INSTR(all_scanner_privs,priv75),0,NULL,priv75)
          || DECODE(INSTR(all_scanner_privs,priv76),0,NULL,priv76)
          || DECODE(INSTR(all_scanner_privs,priv77),0,NULL,priv77)
          || DECODE(INSTR(all_scanner_privs,priv78),0,NULL,priv78)
          || DECODE(INSTR(all_scanner_privs,priv79),0,NULL,priv79)
          || DECODE(INSTR(all_scanner_privs,priv80),0,NULL,priv80)
          || DECODE(INSTR(all_scanner_privs,priv81),0,NULL,priv81)
          || DECODE(INSTR(all_scanner_privs,priv82),0,NULL,priv82)
          || DECODE(INSTR(all_scanner_privs,priv83),0,NULL,priv83)
          || DECODE(INSTR(all_scanner_privs,priv84),0,NULL,priv84)
          || DECODE(INSTR(all_scanner_privs,priv85),0,NULL,priv85)
          || DECODE(INSTR(all_scanner_privs,priv86),0,NULL,priv86)
          || DECODE(INSTR(all_scanner_privs,priv87),0,NULL,priv87)
          || DECODE(INSTR(all_scanner_privs,priv88),0,NULL,priv88)
          || DECODE(INSTR(all_scanner_privs,priv89),0,NULL,priv89)
          || DECODE(INSTR(all_scanner_privs,priv90),0,NULL,priv90)
          || DECODE(INSTR(all_scanner_privs,priv91),0,NULL,priv91)
          || DECODE(INSTR(all_scanner_privs,priv92),0,NULL,priv92)
          || DECODE(INSTR(all_scanner_privs,priv93),0,NULL,priv93)
          || DECODE(INSTR(all_scanner_privs,priv94),0,NULL,priv94)
          || DECODE(INSTR(all_scanner_privs,priv95),0,NULL,priv95)
          || DECODE(INSTR(all_scanner_privs,priv96),0,NULL,priv96)
          || DECODE(INSTR(all_scanner_privs,priv97),0,NULL,priv97)
          || DECODE(INSTR(all_scanner_privs,priv98),0,NULL,priv98)
          || DECODE(INSTR(all_scanner_privs,priv99),0,NULL,priv99)
          || DECODE(INSTR(all_scanner_privs,priv100),0,NULL,priv100) SCANNER_PRIVS
        FROM
            user_group;

    BEGIN

        OPEN scanner_privs;

        /* 
            Create a concatenated character string of all scanner privileges 
        */

        LOOP
            FETCH scanner_privs
            INTO scanner_priv;
            EXIT WHEN scanner_privs%NOTFOUND;

            all_scanner_privs := CONCAT(all_scanner_privs,scanner_priv);
        END LOOP;

        CLOSE scanner_privs;
 
        OPEN user_group_scanner_privs;

        /* 
            Extract a string of scanner privileges belonging to a user_group and grant them to the corresponding user_role.
        */

        LOOP

            FETCH 
                user_group_scanner_privs
            INTO
                v_group_name,
                v_agent_code,
                v_scanner_privs;
 
            EXIT WHEN user_group_scanner_privs%NOTFOUND;
   
            /* 
                Handle v_scanner_privs as a character array dimensioned by its length 
                varying from 0 (i.e. no scanner privileges) to 36 (i.e. all the scanner privileges).
            */

            FOR i IN 1 .. NVL(LENGTH(v_scanner_privs),0) LOOP

            /* 
                Allocate the app_priv_id for the scanner privilege to the user_role_id corresponding to the 
                group_name / agent_code combination.  If the privilege is already allocated then it will remain so. 
            */

                INSERT INTO user_role_priv (user_role_id,app_priv_id)
                                            SELECT 
                                                user_role_id,
                                                app_priv_id
                                            FROM
                                                user_role,
                                                app_priv
                                            WHERE
                                                user_role_name = RTRIM(v_group_name)
                                            AND
                                                user_role.agent_code = v_agent_code
                                            AND
                                                app_priv_code = SUBSTR(v_scanner_privs,i,1)
                                            AND
                                                (user_role_id, app_priv_id)
                                            NOT IN (SELECT
                                                        user_role_id,
                                                        app_priv_id
                                                    FROM
                                                        user_role_priv
                                                   );

            END LOOP;

        END LOOP;

        CLOSE user_group_scanner_privs;

    END;
/

