/*****************************************************************************
*
* Filename         - $Workfile:   user_group_triggers.sql  $
*
*                     Name              Date      Version     CR Number
*                     Martin Helliwell 03/09/01     1.7       15500
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          -
*
* Contents         -  Live Schema: triggers for user_group table
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   user_group_triggers.sql  $  $Revision:   1.7  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   /pvcsarch/db_ora/db/source/live/triggers/user_group_triggers.svl  $
*
*    Rev 1.7   04 Sep 2001 19:29:00   tagtrak
* OR:
* CR: 15500
* REV: 1.7
* DESC: update user_role_priv
*
*    Rev 1.6   21 Jun 2001 15:04:52   tagtrak
* OR:
* CR:
* Re-baseline version 4.0.0 to include patches.
*
*    Rev 1.5   08 Apr 1999 15:24:42   tagtrak
* OR:
* CR: 13677
* REV:
* Changes as a result of System Testing.
*
* Neil Bishop.
* (branch build revision copied to trunk.)
* Neil Bishop
*
*    Rev 1.4.1.1   05 Mar 1999 11:37:50   tagtrak
* OR:
* CR: 13677
* REV:
* Changes as a result of System Testing.
*
* Neil Bishop.
*
*    Rev 1.3.1.0   19 Nov 1998 16:12:56   tagtrak
* OR: 12412 12200
* CR: 12412
* REV:
* Extent user_group table and associated views, procedures
* and functions, to handle more than 50 privilege codes.
* This was extended to 62, to allow upper and lower case alphabet
* plus 10 digits.
* -------------
* Andrew Walker
*
*    Rev 1.3   02 Apr 1998 19:46:38   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
*
*    Rev 1.2   26 Feb 1998 13:51:48   tagtrak
* OR:
* CR:
* REV:
* Initial core product version
*
*    Rev 1.1.1.1   26 Feb 1998 11:43:04   tagtrak
* OR:
* CR:
* REV:
* Initial core product version
*
*    Rev 1.1.1.0   29 Jan 1998 13:34:02   tagtrak
* Updated triggers to handle new field agent_code and
* it's inclusion as part of the PK.
*
*    Rev 1.1   15 Jan 1998 13:39:20   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical issues.
*
*
*    Rev 1.0   22 Oct 1997 16:38:48   tagtrak
* OR:
* CR:
* REV:
* Initial check-in
*
****************************************************************************/

-- File tia_user_group.sql inserted into this file - Dave Banks - FALCP/CA/1771 - 8th March 2006

create or replace trigger td_user_group
before delete on user_group
for each row
begin

        /*
         * Before Each delete on the user_group table :
         * - Insert a record into the log_table.
         */

        insert into log_table
                (user_id, reason, log_time)
        values
                ( :old.last_user,
                'Group ' || :old.group_name || ' Agent ' || :old.agent_code || ' deleted',
                SYSDATE );
end;
/

create or replace trigger ti_user_group
before insert on user_group
for each row
declare

  tmp_msg varchar(100);

begin

  :new.airport_code := multi_site.get_agent_loc(:new.agent_code);
  
  BEGIN
    
    SELECT user_role_id
    INTO :NEW.user_role_id
    FROM user_role
    WHERE user_role_name = RTRIM(:new.group_name)
    AND agent_code = :new.agent_code;
      
  EXCEPTION WHEN no_data_found THEN
    
    --user_role doesn't exist yet, create it.
    INSERT INTO user_role
    (user_role_name, agent_code)
    VALUES
    (RTRIM(:new.group_name), :new.agent_code)
    RETURNING user_role_id INTO :NEW.user_role_id;
      
  END;


        /*
         * Before each insert on the table user_group :
         * - Assign the Correct Privileges
         * - Insert a record into the log_table.
         */

        /*
         * Changed the code in this trigger below to include an nvl
         * (null value) trap for each privilege.  The MWS was reporting
         * an error when trying to copy a user group.  If a group
         * contained a blank value, this was not seen as a NULL value,
         * so the check_priv function was called with a blank value
         * being passed in, which returned ORA-20023 invalid privilege code
         * and caused the MWS to get its knickers in a twist.
         * Neil Bishop.  04/03/99.
         */

        tmp_msg := '';

        check_priv(:new.priv1);
        if (nvl(:new.priv1, '') != '' ) then
                tmp_msg := tmp_msg || :new.priv1;
        end if;
        if (nvl(:new.priv2, '') != '' ) then
                check_priv(:new.priv2);
                tmp_msg := tmp_msg || :new.priv2;
        end if;
        if (nvl(:new.priv3, '') != '' ) then
                check_priv(:new.priv3);
                tmp_msg := tmp_msg || :new.priv3;
        end if;
        if (nvl(:new.priv4, '') != '' ) then
                check_priv(:new.priv4);
                tmp_msg := tmp_msg || :new.priv4;
        end if;
        if (nvl(:new.priv5, '') != '' ) then
                check_priv(:new.priv5);
                tmp_msg := tmp_msg || :new.priv5;
        end if;
        if (nvl(:new.priv6, '') != '' ) then
                check_priv(:new.priv6);
                tmp_msg := tmp_msg || :new.priv6;
        end if;
        if (nvl(:new.priv7, '') != '' ) then
                check_priv(:new.priv7);
                tmp_msg := tmp_msg || :new.priv7;
        end if;
        if (nvl(:new.priv8, '') != '' ) then
                check_priv(:new.priv8);
                tmp_msg := tmp_msg || :new.priv8;
        end if;
        if (nvl(:new.priv9, '') != '' ) then
                check_priv(:new.priv9);
                tmp_msg := tmp_msg || :new.priv9;
        end if;
        if (nvl(:new.priv10, '') != '' ) then
                check_priv(:new.priv10);
                tmp_msg := tmp_msg || :new.priv10;
        end if;
        if (nvl(:new.priv11, '') != '' ) then
                check_priv(:new.priv11);
                tmp_msg := tmp_msg || :new.priv11;
        end if;
        if (nvl(:new.priv12, '') != '' ) then
                check_priv(:new.priv12);
                tmp_msg := tmp_msg || :new.priv12;
        end if;
        if (nvl(:new.priv13, '') != '' ) then
                check_priv(:new.priv13);
                tmp_msg := tmp_msg || :new.priv13;
        end if;
        if (nvl(:new.priv14, '') != '' ) then
                check_priv(:new.priv14);
                tmp_msg := tmp_msg || :new.priv14;
        end if;
        if (nvl(:new.priv15, '') != '' ) then
                check_priv(:new.priv15);
                tmp_msg := tmp_msg || :new.priv15;
        end if;
        if (nvl(:new.priv16, '') != '' ) then
                check_priv(:new.priv16);
                tmp_msg := tmp_msg || :new.priv16;
        end if;
        if (nvl(:new.priv17, '') != '' ) then
                check_priv(:new.priv17);
                tmp_msg := tmp_msg || :new.priv17;
        end if;
        if (nvl(:new.priv18, '') != '' ) then
                check_priv(:new.priv18);
                tmp_msg := tmp_msg || :new.priv18;
        end if;
        if (nvl(:new.priv19, '') != '' ) then
                check_priv(:new.priv19);
                tmp_msg := tmp_msg || :new.priv19;
        end if;
        if (nvl(:new.priv20, '') != '' ) then
                check_priv(:new.priv20);
                tmp_msg := tmp_msg || :new.priv20;
        end if;
        if (nvl(:new.priv21, '') != '' ) then
                check_priv(:new.priv21);
                tmp_msg := tmp_msg || :new.priv21;
        end if;
        if (nvl(:new.priv22, '') != '' ) then
                check_priv(:new.priv22);
                tmp_msg := tmp_msg || :new.priv22;
        end if;
        if (nvl(:new.priv23, '') != '' ) then
                check_priv(:new.priv23);
                tmp_msg := tmp_msg || :new.priv23;
        end if;
        if (nvl(:new.priv24, '') != '' ) then
                check_priv(:new.priv24);
                tmp_msg := tmp_msg || :new.priv24;
        end if;
        if (nvl(:new.priv25, '') != '' ) then
                check_priv(:new.priv25);
                tmp_msg := tmp_msg || :new.priv25;
        end if;
        if (nvl(:new.priv26, '') != '' ) then
                check_priv(:new.priv26);
                tmp_msg := tmp_msg || :new.priv26;
        end if;
        if (nvl(:new.priv27, '') != '' ) then
                check_priv(:new.priv27);
                tmp_msg := tmp_msg || :new.priv27;
        end if;
        if (nvl(:new.priv28, '') != '' ) then
                check_priv(:new.priv28);
                tmp_msg := tmp_msg || :new.priv28;
        end if;
        if (nvl(:new.priv29, '') != '' ) then
                check_priv(:new.priv29);
                tmp_msg := tmp_msg || :new.priv29;
        end if;
        if (nvl(:new.priv30, '') != '' ) then
                check_priv(:new.priv30);
                tmp_msg := tmp_msg || :new.priv30;
        end if;
        if (nvl(:new.priv31, '') != '' ) then
                check_priv(:new.priv31);
                tmp_msg := tmp_msg || :new.priv31;
        end if;
        if (nvl(:new.priv32, '') != '' ) then
                check_priv(:new.priv32);
                tmp_msg := tmp_msg || :new.priv32;
        end if;
        if (nvl(:new.priv33, '') != '' ) then
                check_priv(:new.priv33);
                tmp_msg := tmp_msg || :new.priv33;
        end if;
        if (nvl(:new.priv34, '') != '' ) then
                check_priv(:new.priv34);
                tmp_msg := tmp_msg || :new.priv34;
        end if;
        if (nvl(:new.priv35, '') != '' ) then
                check_priv(:new.priv35);
                tmp_msg := tmp_msg || :new.priv35;
        end if;
        if (nvl(:new.priv36, '') != '' ) then
                check_priv(:new.priv36);
                tmp_msg := tmp_msg || :new.priv36;
        end if;
        if (nvl(:new.priv37, '') != '' ) then
                check_priv(:new.priv37);
                tmp_msg := tmp_msg || :new.priv37;
        end if;
        if (nvl(:new.priv38, '') != '' ) then
                check_priv(:new.priv38);
                tmp_msg := tmp_msg || :new.priv38;
        end if;
        if (nvl(:new.priv39, '') != '' ) then
                check_priv(:new.priv39);
                tmp_msg := tmp_msg || :new.priv39;
        end if;
        if (nvl(:new.priv40, '') != '' ) then
                check_priv(:new.priv40);
                tmp_msg := tmp_msg || :new.priv40;
        end if;
        if (nvl(:new.priv41, '') != '' ) then
                check_priv(:new.priv41);
                tmp_msg := tmp_msg || :new.priv41;
        end if;
        if (nvl(:new.priv42, '') != '' ) then
                check_priv(:new.priv42);
                tmp_msg := tmp_msg || :new.priv42;
        end if;
        if (nvl(:new.priv43, '') != '' ) then
                check_priv(:new.priv43);
                tmp_msg := tmp_msg || :new.priv43;
        end if;
        if (nvl(:new.priv44, '') != '' ) then
                check_priv(:new.priv44);
                tmp_msg := tmp_msg || :new.priv44;
        end if;
        if (nvl(:new.priv45, '') != '' ) then
                check_priv(:new.priv45);
                tmp_msg := tmp_msg || :new.priv45;
        end if;
        if (nvl(:new.priv46, '') != '' ) then
                check_priv(:new.priv46);
                tmp_msg := tmp_msg || :new.priv46;
        end if;
        if (nvl(:new.priv47, '') != '' ) then
                check_priv(:new.priv47);
                tmp_msg := tmp_msg || :new.priv47;
        end if;
        if (nvl(:new.priv48, '') != '' ) then
                check_priv(:new.priv48);
                tmp_msg := tmp_msg || :new.priv48;
        end if;
        if (nvl(:new.priv49, '') != '' ) then
                check_priv(:new.priv49);
                tmp_msg := tmp_msg || :new.priv49;
        end if;

        if (nvl(:new.priv50, '') != '' ) then
                check_priv(:new.priv50);
                tmp_msg := tmp_msg || :new.priv50;
        end if;

        if (nvl(:new.priv51, '') != '' ) then
                check_priv(:new.priv51);
                tmp_msg := tmp_msg || :new.priv51;
        end if;

        if (nvl(:new.priv52, '') != '' ) then
                check_priv(:new.priv52);
                tmp_msg := tmp_msg || :new.priv52;
        end if;

        if (nvl(:new.priv53, '') != '' ) then
                check_priv(:new.priv53);
                tmp_msg := tmp_msg || :new.priv53;
        end if;

        if (nvl(:new.priv54, '') != '' ) then
                check_priv(:new.priv54);
                tmp_msg := tmp_msg || :new.priv54;
        end if;

        if (nvl(:new.priv55, '') != '' ) then
                check_priv(:new.priv55);
                tmp_msg := tmp_msg || :new.priv55;
        end if;

        if (nvl(:new.priv56, '') != '' ) then
                check_priv(:new.priv56);
                tmp_msg := tmp_msg || :new.priv56;
        end if;

        if (nvl(:new.priv57, '') != '' ) then
                check_priv(:new.priv57);
                tmp_msg := tmp_msg || :new.priv57;
        end if;

        if (nvl(:new.priv58, '') != '' ) then
                check_priv(:new.priv58);
                tmp_msg := tmp_msg || :new.priv58;
        end if;

        if (nvl(:new.priv59, '') != '' ) then
                check_priv(:new.priv59);
                tmp_msg := tmp_msg || :new.priv59;
        end if;

        if (nvl(:new.priv60, '') != '' ) then
                check_priv(:new.priv60);
                tmp_msg := tmp_msg || :new.priv60;
        end if;

        if (nvl(:new.priv61, '') != '' ) then
                check_priv(:new.priv61);
                tmp_msg := tmp_msg || :new.priv61;
        end if;

        if (nvl(:new.priv62, '') != '' ) then
                check_priv(:new.priv62);
                tmp_msg := tmp_msg || :new.priv62;
        end if;

        if (nvl(:new.priv63, '') != '' ) then
                check_priv(:new.priv63);
                tmp_msg := tmp_msg || :new.priv63;
        end if;

        if (nvl(:new.priv64, '') != '' ) then
                check_priv(:new.priv64);
                tmp_msg := tmp_msg || :new.priv64;
        end if;

        if (nvl(:new.priv65, '') != '' ) then
                check_priv(:new.priv65);
                tmp_msg := tmp_msg || :new.priv65;
        end if;

        if (nvl(:new.priv66, '') != '' ) then
                check_priv(:new.priv66);
                tmp_msg := tmp_msg || :new.priv66;
        end if;

        if (nvl(:new.priv67, '') != '' ) then
                check_priv(:new.priv67);
                tmp_msg := tmp_msg || :new.priv67;
        end if;

        if (nvl(:new.priv68, '') != '' ) then
                check_priv(:new.priv68);
                tmp_msg := tmp_msg || :new.priv68;
        end if;

        if (nvl(:new.priv69, '') != '' ) then
                check_priv(:new.priv69);
                tmp_msg := tmp_msg || :new.priv69;
        end if;

        if (nvl(:new.priv70, '') != '' ) then
                check_priv(:new.priv70);
                tmp_msg := tmp_msg || :new.priv70;
        end if;

        if (nvl(:new.priv71, '') != '' ) then
                check_priv(:new.priv71);
                tmp_msg := tmp_msg || :new.priv71;
        end if;

        if (nvl(:new.priv72, '') != '' ) then
                check_priv(:new.priv72);
                tmp_msg := tmp_msg || :new.priv72;
        end if;

        if (nvl(:new.priv73, '') != '' ) then
                check_priv(:new.priv73);
                tmp_msg := tmp_msg || :new.priv73;
        end if;

        if (nvl(:new.priv74, '') != '' ) then
                check_priv(:new.priv74);
                tmp_msg := tmp_msg || :new.priv74;
        end if;

        if (nvl(:new.priv75, '') != '' ) then
                check_priv(:new.priv75);
                tmp_msg := tmp_msg || :new.priv75;
        end if;

        if (nvl(:new.priv76, '') != '' ) then
                check_priv(:new.priv76);
                tmp_msg := tmp_msg || :new.priv76;
        end if;

        if (nvl(:new.priv77, '') != '' ) then
                check_priv(:new.priv77);
                tmp_msg := tmp_msg || :new.priv77;
        end if;

        if (nvl(:new.priv78, '') != '' ) then
                check_priv(:new.priv78);
                tmp_msg := tmp_msg || :new.priv78;
        end if;

        if (nvl(:new.priv79, '') != '' ) then
                check_priv(:new.priv79);
                tmp_msg := tmp_msg || :new.priv79;
        end if;

        if (nvl(:new.priv80, '') != '' ) then
                check_priv(:new.priv80);
                tmp_msg := tmp_msg || :new.priv80;
        end if;

        if (nvl(:new.priv81, '') != '' ) then
                check_priv(:new.priv81);
                tmp_msg := tmp_msg || :new.priv81;
        end if;

        if (nvl(:new.priv82, '') != '' ) then
                check_priv(:new.priv82);
                tmp_msg := tmp_msg || :new.priv82;
        end if;

        if (nvl(:new.priv83, '') != '' ) then
                check_priv(:new.priv83);
                tmp_msg := tmp_msg || :new.priv83;
        end if;

        if (nvl(:new.priv84, '') != '' ) then
                check_priv(:new.priv84);
                tmp_msg := tmp_msg || :new.priv84;
        end if;

        if (nvl(:new.priv85, '') != '' ) then
                check_priv(:new.priv85);
                tmp_msg := tmp_msg || :new.priv85;
        end if;

        if (nvl(:new.priv86, '') != '' ) then
                check_priv(:new.priv86);
                tmp_msg := tmp_msg || :new.priv86;
        end if;

        if (nvl(:new.priv87, '') != '' ) then
                check_priv(:new.priv87);
                tmp_msg := tmp_msg || :new.priv87;
        end if;

        if (nvl(:new.priv88, '') != '' ) then
                check_priv(:new.priv88);
                tmp_msg := tmp_msg || :new.priv88;
        end if;

        if (nvl(:new.priv89, '') != '' ) then
                check_priv(:new.priv89);
                tmp_msg := tmp_msg || :new.priv89;
        end if;

        if (nvl(:new.priv90, '') != '' ) then
                check_priv(:new.priv90);
                tmp_msg := tmp_msg || :new.priv90;
        end if;

        if (nvl(:new.priv91, '') != '' ) then
                check_priv(:new.priv91);
                tmp_msg := tmp_msg || :new.priv91;
        end if;

        if (nvl(:new.priv92, '') != '' ) then
                check_priv(:new.priv92);
                tmp_msg := tmp_msg || :new.priv92;
        end if;

        if (nvl(:new.priv93, '') != '' ) then
                check_priv(:new.priv93);
                tmp_msg := tmp_msg || :new.priv93;
        end if;

        if (nvl(:new.priv94, '') != '' ) then
                check_priv(:new.priv94);
                tmp_msg := tmp_msg || :new.priv94;
        end if;

        if (nvl(:new.priv95, '') != '' ) then
                check_priv(:new.priv95);
                tmp_msg := tmp_msg || :new.priv95;
        end if;

        if (nvl(:new.priv96, '') != '' ) then
                check_priv(:new.priv96);
                tmp_msg := tmp_msg || :new.priv96;
        end if;

        if (nvl(:new.priv97, '') != '' ) then
                check_priv(:new.priv97);
                tmp_msg := tmp_msg || :new.priv97;
        end if;

        if (nvl(:new.priv98, '') != '' ) then
                check_priv(:new.priv98);
                tmp_msg := tmp_msg || :new.priv98;
        end if;

        if (nvl(:new.priv99, '') != '' ) then
                check_priv(:new.priv99);
                tmp_msg := tmp_msg || :new.priv99;
        end if;

        if (nvl(:new.priv100, '') != '' ) then
                check_priv(:new.priv100);
                tmp_msg := tmp_msg || :new.priv100;
        end if;


        insert into log_table
                ( user_id, reason, log_time )
        values
                ( :new.last_user,
                'Group ' || :new.group_name || ' Agent ' || :new.agent_code ||
                ' added, with priv ' || tmp_msg,
                SYSDATE );
end;
/


create or replace trigger tu_user_group
before update on user_group
for each row
declare

  tmp_msg varchar(100);

begin

        /*
         * Before each Update on the user_group table :
         * - Assign the Correct Privileges
         * - Insert a record into the log_table.
         */

        /*
         * Changed the code in this trigger below to include an nvl
         * (null value) trap for each privilege.  The MWS was reporting
         * an error when trying to copy a user group.  If a group
         * contained a blank value, this was not seen as a NULL value,
         * so the check_priv function was called with a blank value
         * being passed in, which returned ORA-20023 invalid privilege code
         * and caused the MWS to get its knickers in a twist.
         * Neil Bishop.  04/03/99.
         */

        tmp_msg := '';

        check_priv(:new.priv1);
        if (nvl(:new.priv1, '') != '' ) then
                tmp_msg := tmp_msg || :new.priv1;
        end if;
        if (nvl(:new.priv2, '') != '' ) then
                check_priv(:new.priv2);
                tmp_msg := tmp_msg || :new.priv2;
        end if;
        if (nvl(:new.priv3, '') != '' ) then
                check_priv(:new.priv3);
                tmp_msg := tmp_msg || :new.priv3;
        end if;
        if (nvl(:new.priv4, '') != '' ) then
                check_priv(:new.priv4);
                tmp_msg := tmp_msg || :new.priv4;
        end if;
        if (nvl(:new.priv5, '') != '' ) then
                check_priv(:new.priv5);
                tmp_msg := tmp_msg || :new.priv5;
        end if;
        if (nvl(:new.priv6, '') != '' ) then
                check_priv(:new.priv6);
                tmp_msg := tmp_msg || :new.priv6;
        end if;
        if (nvl(:new.priv7, '') != '' ) then
                check_priv(:new.priv7);
                tmp_msg := tmp_msg || :new.priv7;
        end if;
        if (nvl(:new.priv8, '') != '' ) then
                check_priv(:new.priv8);
                tmp_msg := tmp_msg || :new.priv8;
        end if;
        if (nvl(:new.priv9, '') != '' ) then
                check_priv(:new.priv9);
                tmp_msg := tmp_msg || :new.priv9;
        end if;
        if (nvl(:new.priv10, '') != '' ) then
                check_priv(:new.priv10);
                tmp_msg := tmp_msg || :new.priv10;
        end if;
        if (nvl(:new.priv11, '') != '' ) then
                check_priv(:new.priv11);
                tmp_msg := tmp_msg || :new.priv11;
        end if;
        if (nvl(:new.priv12, '') != '' ) then
                check_priv(:new.priv12);
                tmp_msg := tmp_msg || :new.priv12;
        end if;
        if (nvl(:new.priv13, '') != '' ) then
                check_priv(:new.priv13);
                tmp_msg := tmp_msg || :new.priv13;
        end if;
        if (nvl(:new.priv14, '') != '' ) then
                check_priv(:new.priv14);
                tmp_msg := tmp_msg || :new.priv14;
        end if;
        if (nvl(:new.priv15, '') != '' ) then
                check_priv(:new.priv15);
                tmp_msg := tmp_msg || :new.priv15;
        end if;
        if (nvl(:new.priv16, '') != '' ) then
                check_priv(:new.priv16);
                tmp_msg := tmp_msg || :new.priv16;
        end if;
        if (nvl(:new.priv17, '') != '' ) then
                check_priv(:new.priv17);
                tmp_msg := tmp_msg || :new.priv17;
        end if;
        if (nvl(:new.priv18, '') != '' ) then
                check_priv(:new.priv18);
                tmp_msg := tmp_msg || :new.priv18;
        end if;
        if (nvl(:new.priv19, '') != '' ) then
                check_priv(:new.priv19);
                tmp_msg := tmp_msg || :new.priv19;
        end if;
        if (nvl(:new.priv20, '') != '' ) then
                check_priv(:new.priv20);
                tmp_msg := tmp_msg || :new.priv20;
        end if;
        if (nvl(:new.priv21, '') != '' ) then
                check_priv(:new.priv21);
                tmp_msg := tmp_msg || :new.priv21;
        end if;
        if (nvl(:new.priv22, '') != '' ) then
                check_priv(:new.priv22);
                tmp_msg := tmp_msg || :new.priv22;
        end if;
        if (nvl(:new.priv23, '') != '' ) then
                check_priv(:new.priv23);
                tmp_msg := tmp_msg || :new.priv23;
        end if;
        if (nvl(:new.priv24, '') != '' ) then
                check_priv(:new.priv24);
                tmp_msg := tmp_msg || :new.priv24;
        end if;
        if (nvl(:new.priv25, '') != '' ) then
                check_priv(:new.priv25);
                tmp_msg := tmp_msg || :new.priv25;
        end if;
        if (nvl(:new.priv26, '') != '' ) then
                check_priv(:new.priv26);
                tmp_msg := tmp_msg || :new.priv26;
        end if;
        if (nvl(:new.priv27, '') != '' ) then
                check_priv(:new.priv27);
                tmp_msg := tmp_msg || :new.priv27;
        end if;
        if (nvl(:new.priv28, '') != '' ) then
                check_priv(:new.priv28);
                tmp_msg := tmp_msg || :new.priv28;
        end if;
        if (nvl(:new.priv29, '') != '' ) then
                check_priv(:new.priv29);
                tmp_msg := tmp_msg || :new.priv29;
        end if;
        if (nvl(:new.priv30, '') != '' ) then
                check_priv(:new.priv30);
                tmp_msg := tmp_msg || :new.priv30;
        end if;
        if (nvl(:new.priv31, '') != '' ) then
                check_priv(:new.priv31);
                tmp_msg := tmp_msg || :new.priv31;
        end if;
        if (nvl(:new.priv32, '') != '' ) then
                check_priv(:new.priv32);
                tmp_msg := tmp_msg || :new.priv32;
        end if;
        if (nvl(:new.priv33, '') != '' ) then
                check_priv(:new.priv33);
                tmp_msg := tmp_msg || :new.priv33;
        end if;
        if (nvl(:new.priv34, '') != '' ) then
                check_priv(:new.priv34);
                tmp_msg := tmp_msg || :new.priv34;
        end if;
        if (nvl(:new.priv35, '') != '' ) then
                check_priv(:new.priv35);
                tmp_msg := tmp_msg || :new.priv35;
        end if;
        if (nvl(:new.priv36, '') != '' ) then
                check_priv(:new.priv36);
                tmp_msg := tmp_msg || :new.priv36;
        end if;
        if (nvl(:new.priv37, '') != '' ) then
                check_priv(:new.priv37);
                tmp_msg := tmp_msg || :new.priv37;
        end if;
        if (nvl(:new.priv38, '') != '' ) then
                check_priv(:new.priv38);
                tmp_msg := tmp_msg || :new.priv38;
        end if;
        if (nvl(:new.priv39, '') != '' ) then
                check_priv(:new.priv39);
                tmp_msg := tmp_msg || :new.priv39;
        end if;
        if (nvl(:new.priv40, '') != '' ) then
                check_priv(:new.priv40);
                tmp_msg := tmp_msg || :new.priv40;
        end if;
        if (nvl(:new.priv41, '') != '' ) then
                check_priv(:new.priv41);
                tmp_msg := tmp_msg || :new.priv41;
        end if;
        if (nvl(:new.priv42, '') != '' ) then
                check_priv(:new.priv42);
                tmp_msg := tmp_msg || :new.priv42;
        end if;
        if (nvl(:new.priv43, '') != '' ) then
                check_priv(:new.priv43);
                tmp_msg := tmp_msg || :new.priv43;
        end if;
        if (nvl(:new.priv44, '') != '' ) then
                check_priv(:new.priv44);
                tmp_msg := tmp_msg || :new.priv44;
        end if;
        if (nvl(:new.priv45, '') != '' ) then
                check_priv(:new.priv45);
                tmp_msg := tmp_msg || :new.priv45;
        end if;
        if (nvl(:new.priv46, '') != '' ) then
                check_priv(:new.priv46);
                tmp_msg := tmp_msg || :new.priv46;
        end if;
        if (nvl(:new.priv47, '') != '' ) then
                check_priv(:new.priv47);
                tmp_msg := tmp_msg || :new.priv47;
        end if;
        if (nvl(:new.priv48, '') != '' ) then
                check_priv(:new.priv48);
                tmp_msg := tmp_msg || :new.priv48;
        end if;
        if (nvl(:new.priv49, '') != '' ) then
                check_priv(:new.priv49);
                tmp_msg := tmp_msg || :new.priv49;
        end if;

        if (nvl(:new.priv50, '') != '' ) then
                check_priv(:new.priv50);
                tmp_msg := tmp_msg || :new.priv50;
        end if;

        if (nvl(:new.priv51, '') != '' ) then
                check_priv(:new.priv51);
                tmp_msg := tmp_msg || :new.priv51;
        end if;

        if (nvl(:new.priv52, '') != '' ) then
                check_priv(:new.priv52);
                tmp_msg := tmp_msg || :new.priv52;
        end if;

        if (nvl(:new.priv53, '') != '' ) then
                check_priv(:new.priv53);
                tmp_msg := tmp_msg || :new.priv53;
        end if;

        if (nvl(:new.priv54, '') != '' ) then
                check_priv(:new.priv54);
                tmp_msg := tmp_msg || :new.priv54;
        end if;

        if (nvl(:new.priv55, '') != '' ) then
                check_priv(:new.priv55);
                tmp_msg := tmp_msg || :new.priv55;
        end if;

        if (nvl(:new.priv56, '') != '' ) then
                check_priv(:new.priv56);
                tmp_msg := tmp_msg || :new.priv56;
        end if;

        if (nvl(:new.priv57, '') != '' ) then
                check_priv(:new.priv57);
                tmp_msg := tmp_msg || :new.priv57;
        end if;

        if (nvl(:new.priv58, '') != '' ) then
                check_priv(:new.priv58);
                tmp_msg := tmp_msg || :new.priv58;
        end if;

        if (nvl(:new.priv59, '') != '' ) then
                check_priv(:new.priv59);
                tmp_msg := tmp_msg || :new.priv59;
        end if;

        if (nvl(:new.priv60, '') != '' ) then
                check_priv(:new.priv60);
                tmp_msg := tmp_msg || :new.priv60;
        end if;

        if (nvl(:new.priv61, '') != '' ) then
                check_priv(:new.priv61);
                tmp_msg := tmp_msg || :new.priv61;
        end if;

        if (nvl(:new.priv62, '') != '' ) then
                check_priv(:new.priv62);
                tmp_msg := tmp_msg || :new.priv62;
        end if;

        if (nvl(:new.priv63, '') != '' ) then
                check_priv(:new.priv63);
                tmp_msg := tmp_msg || :new.priv63;
        end if;

        if (nvl(:new.priv64, '') != '' ) then
                check_priv(:new.priv64);
                tmp_msg := tmp_msg || :new.priv64;
        end if;

        if (nvl(:new.priv65, '') != '' ) then
                check_priv(:new.priv65);
                tmp_msg := tmp_msg || :new.priv65;
        end if;

        if (nvl(:new.priv66, '') != '' ) then
                check_priv(:new.priv66);
                tmp_msg := tmp_msg || :new.priv66;
        end if;

        if (nvl(:new.priv67, '') != '' ) then
                check_priv(:new.priv67);
                tmp_msg := tmp_msg || :new.priv67;
        end if;

        if (nvl(:new.priv68, '') != '' ) then
                check_priv(:new.priv68);
                tmp_msg := tmp_msg || :new.priv68;
        end if;

        if (nvl(:new.priv69, '') != '' ) then
                check_priv(:new.priv69);
                tmp_msg := tmp_msg || :new.priv69;
        end if;

        if (nvl(:new.priv70, '') != '' ) then
                check_priv(:new.priv70);
                tmp_msg := tmp_msg || :new.priv70;
        end if;

        if (nvl(:new.priv71, '') != '' ) then
                check_priv(:new.priv71);
                tmp_msg := tmp_msg || :new.priv71;
        end if;

        if (nvl(:new.priv72, '') != '' ) then
                check_priv(:new.priv72);
                tmp_msg := tmp_msg || :new.priv72;
        end if;

        if (nvl(:new.priv73, '') != '' ) then
                check_priv(:new.priv73);
                tmp_msg := tmp_msg || :new.priv73;
        end if;

        if (nvl(:new.priv74, '') != '' ) then
                check_priv(:new.priv74);
                tmp_msg := tmp_msg || :new.priv74;
        end if;

        if (nvl(:new.priv75, '') != '' ) then
                check_priv(:new.priv75);
                tmp_msg := tmp_msg || :new.priv75;
        end if;

        if (nvl(:new.priv76, '') != '' ) then
                check_priv(:new.priv76);
                tmp_msg := tmp_msg || :new.priv76;
        end if;

        if (nvl(:new.priv77, '') != '' ) then
                check_priv(:new.priv77);
                tmp_msg := tmp_msg || :new.priv77;
        end if;

        if (nvl(:new.priv78, '') != '' ) then
                check_priv(:new.priv78);
                tmp_msg := tmp_msg || :new.priv78;
        end if;

        if (nvl(:new.priv79, '') != '' ) then
                check_priv(:new.priv79);
                tmp_msg := tmp_msg || :new.priv79;
        end if;

        if (nvl(:new.priv80, '') != '' ) then
                check_priv(:new.priv80);
                tmp_msg := tmp_msg || :new.priv80;
        end if;

        if (nvl(:new.priv81, '') != '' ) then
                check_priv(:new.priv81);
                tmp_msg := tmp_msg || :new.priv81;
        end if;

        if (nvl(:new.priv82, '') != '' ) then
                check_priv(:new.priv82);
                tmp_msg := tmp_msg || :new.priv82;
        end if;

        if (nvl(:new.priv83, '') != '' ) then
                check_priv(:new.priv83);
                tmp_msg := tmp_msg || :new.priv83;
        end if;

        if (nvl(:new.priv84, '') != '' ) then
                check_priv(:new.priv84);
                tmp_msg := tmp_msg || :new.priv84;
        end if;

        if (nvl(:new.priv85, '') != '' ) then
                check_priv(:new.priv85);
                tmp_msg := tmp_msg || :new.priv85;
        end if;

        if (nvl(:new.priv86, '') != '' ) then
                check_priv(:new.priv86);
                tmp_msg := tmp_msg || :new.priv86;
        end if;

        if (nvl(:new.priv87, '') != '' ) then
                check_priv(:new.priv87);
                tmp_msg := tmp_msg || :new.priv87;
        end if;

        if (nvl(:new.priv88, '') != '' ) then
                check_priv(:new.priv88);
                tmp_msg := tmp_msg || :new.priv88;
        end if;

        if (nvl(:new.priv89, '') != '' ) then
                check_priv(:new.priv89);
                tmp_msg := tmp_msg || :new.priv89;
        end if;

        if (nvl(:new.priv90, '') != '' ) then
                check_priv(:new.priv90);
                tmp_msg := tmp_msg || :new.priv90;
        end if;

        if (nvl(:new.priv91, '') != '' ) then
                check_priv(:new.priv91);
                tmp_msg := tmp_msg || :new.priv91;
        end if;

        if (nvl(:new.priv92, '') != '' ) then
                check_priv(:new.priv92);
                tmp_msg := tmp_msg || :new.priv92;
        end if;

        if (nvl(:new.priv93, '') != '' ) then
                check_priv(:new.priv93);
                tmp_msg := tmp_msg || :new.priv93;
        end if;

        if (nvl(:new.priv94, '') != '' ) then
                check_priv(:new.priv94);
                tmp_msg := tmp_msg || :new.priv94;
        end if;

        if (nvl(:new.priv95, '') != '' ) then
                check_priv(:new.priv95);
                tmp_msg := tmp_msg || :new.priv95;
        end if;

        if (nvl(:new.priv96, '') != '' ) then
                check_priv(:new.priv96);
                tmp_msg := tmp_msg || :new.priv96;
        end if;

        if (nvl(:new.priv97, '') != '' ) then
                check_priv(:new.priv97);
                tmp_msg := tmp_msg || :new.priv97;
        end if;

        if (nvl(:new.priv98, '') != '' ) then
                check_priv(:new.priv98);
                tmp_msg := tmp_msg || :new.priv98;
        end if;

        if (nvl(:new.priv99, '') != '' ) then
                check_priv(:new.priv99);
                tmp_msg := tmp_msg || :new.priv99;
        end if;

        if (nvl(:new.priv100, '') != '' ) then
                check_priv(:new.priv100);
                tmp_msg := tmp_msg || :new.priv100;
        end if;

        insert into log_table
                (user_id, reason, log_time)
        values
                ( :new.last_user,
                'Group ' || :new.group_name || ' Agent ' || :new.agent_code ||
                ' changed, with priv ' || tmp_msg,
                SYSDATE );
end;
/


CREATE OR REPLACE TRIGGER tu2_user_group
AFTER UPDATE ON user_group
FOR EACH ROW
DECLARE

  scanner_privileges      VARCHAR2(100);
  all_scanner_privileges  VARCHAR2(100);
  
  CURSOR cursor_scanner_privileges IS
  SELECT app_priv_code 
  FROM app_priv 
  WHERE app_priv_code IS NOT NULL;

BEGIN

/*
 * Concatenate all the scanner privileges into a string
 */

  FOR scanner_privilege IN cursor_scanner_privileges LOOP
    all_scanner_privileges := CONCAT(all_scanner_privileges,scanner_privilege.app_priv_code);
  END LOOP;

/*
 * Concatenate the scanner privileges for a user group into a string
 */

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

/*
 * Revoke scanner privileges from a role where they are absent from the equivalent user_group
 * Compare each app_priv_code to see if it is absent from the string of scanner_privileges
 */

  DELETE FROM user_role_priv
  WHERE (user_role_id, app_priv_id) IN (SELECT user_role_id, app_priv_id
                                        FROM user_role, app_priv
                                        WHERE user_role_name = RTRIM(:new.group_name)
  AND agent_code = :new.agent_code
  AND INSTR(NVL(scanner_privileges,'?'), app_priv_code) = 0
  AND app_priv_code IS NOT NULL);

/*
 * Grant scanner privileges to a role where they are present in the equivalent user_group
 * and if they do not already exist
 * Compare each app_priv_code to see if it is present in the string of scanner_privileges
 */

  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(:new.group_name)
   AND agent_code = :new.agent_code
   AND INSTR(NVL(scanner_privileges,'?'), app_priv_code) <> 0
   AND app_priv_code IS NOT NULL
   AND (user_role_id, app_priv_id) NOT IN (SELECT user_role_id, app_priv_id
                                           FROM user_role_priv));
                                           
END;
/


CREATE OR REPLACE TRIGGER ti2_user_group
AFTER INSERT ON user_group
FOR EACH ROW
DECLARE

  i                     BINARY_INTEGER DEFAULT 1;       -- loop counter
  scanner_priv          app_priv.app_priv_code%TYPE;
  scanner_privileges    VARCHAR2(100);
  all_scanner_privs     VARCHAR2(100) DEFAULT NULL;

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

BEGIN

/*
 * Create a role in user_role the group_name in user_group
 */

  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;

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

  /*
   * Handle scanner_privileges 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(scanner_privileges),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(:new.group_name)
     AND user_role.agent_code = :new.agent_code
     AND app_priv_code = SUBSTR(scanner_privileges,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;
/

