rem *********************************************************************
rem *                                                                   *
rem *             Copyright (c) Ultra Electronics Limited 2007.         *
rem *                           All rights reserved.                    *
rem *                                                                   *
rem *********************************************************************
rem *                                                                   *
rem * Filename:             user_maintenance.sql                        *
rem * Module Name:          Database                                    *
rem * Subsystem Name:       UltraTrak                                   *
rem * Originating Author:   Rob Cooke                                   *
rem * Design Reference:     UAS/FALCP/CA/02214                          *
rem *                                                                   *
rem *********************************************************************
rem *                                                                   *
rem * Description:                                                      *
rem *        Package of functions to handle creating and maintaining    *
rem *        users.                                                     *
rem *                                                                   *
rem *********************************************************************/

CREATE OR REPLACE PACKAGE USER_MAINTENANCE AS

FUNCTION jam_privilege(p_app_priv_code IN app_priv.app_priv_code%TYPE)
RETURN app_priv.app_priv_code%TYPE;

PRAGMA RESTRICT_REFERENCES (jam_privilege, WNDS, WNPS);

--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
PROCEDURE add_user_group(p_last_user     IN     user_group.last_user%TYPE,
                         p_group_name    IN     user_group.group_name%TYPE,
                         p_agent_code    IN     user_group.agent_code%TYPE);
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
PROCEDURE update_role(p_user_role_name     IN user_role.user_role_name%TYPE,
                      p_agent_code         IN agents.agent_code%TYPE,
                      p_granted_privileges IN privs_list,
                      p_revoked_privileges IN privs_list,
                      p_user_id            IN tagtrak_users.user_id%TYPE);
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------

    -- Check to see whether a user has the specified privilege
    --
    -- Arguments:
    -- 	p_user_id	- The user's ID.
    -- 	p_priv		- The privilege to check.
    --
    -- Returns:
    --  true		- The user has the privilege
    --  false		- The user does not have the privilege
    --
    function User_Has_Privilege(p_user_id in tagtrak_users.user_id%type,
				p_priv    in app_priv.app_priv_name%type)
				    return boolean;


    -- Check to see whether a user has the specified privilege
    --
    -- Arguments:
    -- 	p_user_name	- The user's login name.
    -- 	p_priv		- The privilege to check.
    --
    -- Returns:
    --  true		- The user has the privilege
    --  false		- The user does not have the privilege
    --
    function User_Has_Privilege(p_user_name in tagtrak_users.loginname%type,
				p_priv      in app_priv.app_priv_name%type)
				    return boolean;

END user_maintenance;
/
CREATE OR REPLACE PACKAGE BODY USER_MAINTENANCE AS

  integrity_constraint    EXCEPTION;
  PRAGMA EXCEPTION_INIT(integrity_constraint, -2291);

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

FUNCTION scanner_privilege(p_app_priv_id IN app_priv.app_priv_id%TYPE)
RETURN BOOLEAN IS

  n         NUMBER;

BEGIN

  SELECT 1 INTO n
  FROM  app_priv
  WHERE app_priv_id = p_app_priv_id
  AND app_priv_code IS NOT NULL;

  RETURN TRUE;

EXCEPTION WHEN no_data_found THEN

  RETURN FALSE;

END scanner_privilege;

--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
--Returns the privilege code if the privilege does not exist on app_priv. Othwise returns null.
FUNCTION jam_privilege(p_app_priv_code IN app_priv.app_priv_code%TYPE)
RETURN app_priv.app_priv_code%TYPE IS

  n         NUMBER;

BEGIN

  SELECT 1 INTO n
  FROM app_priv
  WHERE app_priv_code = p_app_priv_code;

  RETURN NULL;

EXCEPTION WHEN no_data_found THEN

  RETURN p_app_priv_code;

END jam_privilege;

--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
--Returns the list of privileges in a particular user_group record that don't have a matching
--parent record on app_priv.
FUNCTION jam_privileges (p_group_name IN user_group.group_name%TYPE,
                         p_agent_code IN user_group.agent_code%TYPE)
RETURN VARCHAR2 IS

  v_jam_privileges VARCHAR2(100);

BEGIN

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

  RETURN v_jam_privileges;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;

END jam_privileges;

--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
--Updates the user_group record. Gets the list of privs from user_role_priv, add any privs that
--are protected by jam_privileges. If your list of privs is empty, delete the user_group record.
--Otherwise do an insert
PROCEDURE update_user_group (p_user_role_name IN user_role.user_role_name%TYPE,
                             p_agent_code     IN agents.agent_code%TYPE,
                             p_user_id        IN tagtrak_users.user_id%TYPE) AS

  SUBTYPE app_priv_code    IS app_priv.app_priv_code%TYPE;
  TYPE app_priv_code_table IS TABLE OF app_priv_code INDEX BY BINARY_INTEGER;

  l_app_priv_code   app_priv_code_table;
  l_jam_privileges  VARCHAR2(100);
  l_row             BINARY_INTEGER := 0;

  CURSOR c_app_priv_code (cp_user_role_name  user_role.user_role_name%TYPE,
                          cp_agent_code      agents.agent_code%TYPE) IS
  SELECT ap.app_priv_code
  FROM app_priv ap,
       user_role_priv urp,
       user_role ur
  WHERE trim(ur.user_role_name) = trim(cp_user_role_name)
  AND ur.agent_code = cp_agent_code
  AND ap.app_priv_id = urp.app_priv_id
  AND ur.user_role_id = urp.user_role_id
  AND ap.app_priv_code IS NOT NULL
  ORDER BY ap.app_priv_code;

BEGIN


--deb('update_user_group -  Group: ' || p_user_role_name || ', Agent: ' || p_agent_code);
  --First get all the scanner privs that are currently in user_role_priv
  --and append them to the t_app_priv_code binary integer table
  FOR rec IN c_app_priv_code(p_user_role_name,
                             p_agent_code) LOOP

    l_row := l_row + 1;
    l_app_priv_code(l_row) := rec.app_priv_code;

  END LOOP;

  --Get the list of privileges currently on user_group that don't have matching entries on
  --app_priv
  l_jam_privileges := jam_privileges(p_user_role_name, p_agent_code);
  --Append the list into binary integer table
  FOR j IN 1 .. NVL(LENGTH(l_jam_privileges),0) LOOP
    l_app_priv_code(l_row + j) := SUBSTR(l_jam_privileges,j,1);
  END LOOP;

  --Make sure the binary integer table has 100 rows (1 for each priv column) by adding blank
  --rows as necessary.
  FOR k IN (l_app_priv_code.count + 1)..100 LOOP

    l_app_priv_code(k) := null;

  END LOOP;

  IF l_app_priv_code(1) IS NULL THEN
    /*This bit of code won't ever run at the moment since jam privileges will always save a value.
      However I'm leaving it in since jam privileges needs to be changed.*/

    --No scanner privs exist, delete the user_group record.
    DELETE FROM user_group
    WHERE trim(p_user_role_name) = trim(group_name)
    AND p_agent_code = agent_code;

  ELSE

    --Update record with new values
    UPDATE user_group
    SET priv1 = l_app_priv_code(1),
        priv2 = l_app_priv_code(2),
        priv3 = l_app_priv_code(3),
        priv4 = l_app_priv_code(4),
        priv5 = l_app_priv_code(5),
        priv6 = l_app_priv_code(6),
        priv7 = l_app_priv_code(7),
        priv8 = l_app_priv_code(8),
        priv9 = l_app_priv_code(9),
        priv10 = l_app_priv_code(10),
        priv11 = l_app_priv_code(11),
        priv12 = l_app_priv_code(12),
        priv13 = l_app_priv_code(13),
        priv14 = l_app_priv_code(14),
        priv15 = l_app_priv_code(15),
        priv16 = l_app_priv_code(16),
        priv17 = l_app_priv_code(17),
        priv18 = l_app_priv_code(18),
        priv19 = l_app_priv_code(19),
        priv20 = l_app_priv_code(20),
        priv21 = l_app_priv_code(21),
        priv22 = l_app_priv_code(22),
        priv23 = l_app_priv_code(23),
        priv24 = l_app_priv_code(24),
        priv25 = l_app_priv_code(25),
        priv26 = l_app_priv_code(26),
        priv27 = l_app_priv_code(27),
        priv28 = l_app_priv_code(28),
        priv29 = l_app_priv_code(29),
        priv30 = l_app_priv_code(30),
        priv31 = l_app_priv_code(31),
        priv32 = l_app_priv_code(32),
        priv33 = l_app_priv_code(33),
        priv34 = l_app_priv_code(34),
        priv35 = l_app_priv_code(35),
        priv36 = l_app_priv_code(36),
        priv37 = l_app_priv_code(37),
        priv38 = l_app_priv_code(38),
        priv39 = l_app_priv_code(39),
        priv40 = l_app_priv_code(40),
        priv41 = l_app_priv_code(41),
        priv42 = l_app_priv_code(42),
        priv43 = l_app_priv_code(43),
        priv44 = l_app_priv_code(44),
        priv45 = l_app_priv_code(45),
        priv46 = l_app_priv_code(46),
        priv47 = l_app_priv_code(47),
        priv48 = l_app_priv_code(48),
        priv49 = l_app_priv_code(49),
        priv50 = l_app_priv_code(50),
        priv51 = l_app_priv_code(51),
        priv52 = l_app_priv_code(52),
        priv53 = l_app_priv_code(53),
        priv54 = l_app_priv_code(54),
        priv55 = l_app_priv_code(55),
        priv56 = l_app_priv_code(56),
        priv57 = l_app_priv_code(57),
        priv58 = l_app_priv_code(58),
        priv59 = l_app_priv_code(59),
        priv60 = l_app_priv_code(60),
        priv61 = l_app_priv_code(61),
        priv62 = l_app_priv_code(62),
        priv63 = l_app_priv_code(63),
        priv64 = l_app_priv_code(64),
        priv65 = l_app_priv_code(65),
        priv66 = l_app_priv_code(66),
        priv67 = l_app_priv_code(67),
        priv68 = l_app_priv_code(68),
        priv69 = l_app_priv_code(69),
        priv70 = l_app_priv_code(70),
        priv71 = l_app_priv_code(71),
        priv72 = l_app_priv_code(72),
        priv73 = l_app_priv_code(73),
        priv74 = l_app_priv_code(74),
        priv75 = l_app_priv_code(75),
        priv76 = l_app_priv_code(76),
        priv77 = l_app_priv_code(77),
        priv78 = l_app_priv_code(78),
        priv79 = l_app_priv_code(79),
        priv80 = l_app_priv_code(80),
        priv81 = l_app_priv_code(81),
        priv82 = l_app_priv_code(82),
        priv83 = l_app_priv_code(83),
        priv84 = l_app_priv_code(84),
        priv85 = l_app_priv_code(85),
        priv86 = l_app_priv_code(86),
        priv87 = l_app_priv_code(87),
        priv88 = l_app_priv_code(88),
        priv89 = l_app_priv_code(89),
        priv90 = l_app_priv_code(90),
        priv91 = l_app_priv_code(91),
        priv92 = l_app_priv_code(92),
        priv93 = l_app_priv_code(93),
        priv94 = l_app_priv_code(94),
        priv95 = l_app_priv_code(95),
        priv96 = l_app_priv_code(96),
        priv97 = l_app_priv_code(97),
        priv98 = l_app_priv_code(98),
        priv99 = l_app_priv_code(99),
        priv100 = l_app_priv_code(100),
        last_user = p_user_id
    WHERE trim(p_user_role_name) = trim(group_name)
    AND p_agent_code = agent_code;

    IF SQL%NOTFOUND THEN
    --No record exists to update so lets insert a new one.

      INSERT INTO user_group
      (group_name,          last_user,           agent_code,          airport_code,
       priv1,               priv2,               priv3,               priv4,
       priv5,               priv6,               priv7,               priv8,
       priv9,               priv10,              priv11,              priv12,
       priv13,              priv14,              priv15,              priv16,
       priv17,              priv18,              priv19,              priv20,
       priv21,              priv22,              priv23,              priv24,
       priv25,              priv26,              priv27,              priv28,
       priv29,              priv30,              priv31,              priv32,
       priv33,              priv34,              priv35,              priv36,
       priv37,              priv38,              priv39,              priv40,
       priv41,              priv42,              priv43,              priv44,
       priv45,              priv46,              priv47,              priv48,
       priv49,              priv50,              priv51,              priv52,
       priv53,              priv54,              priv55,              priv56,
       priv57,              priv58,              priv59,              priv60,
       priv61,              priv62,              priv63,              priv64,
       priv65,              priv66,              priv67,              priv68,
       priv69,              priv70,              priv71,              priv72,
       priv73,              priv74,              priv75,              priv76,
       priv77,              priv78,              priv79,              priv80,
       priv81,              priv82,              priv83,              priv84,
       priv85,              priv86,              priv87,              priv88,
       priv89,              priv90,              priv91,              priv92,
       priv93,              priv94,              priv95,              priv96,
       priv97,              priv98,              priv99,              priv100)
      VALUES
      (p_user_role_name,    p_user_id,           p_agent_code,        multi_site.get_agent_loc(p_agent_code),
       l_app_priv_code(1),  l_app_priv_code(2),  l_app_priv_code(3),  l_app_priv_code(4),
       l_app_priv_code(5),  l_app_priv_code(6),  l_app_priv_code(7),  l_app_priv_code(8),
       l_app_priv_code(9),  l_app_priv_code(10), l_app_priv_code(11), l_app_priv_code(12),
       l_app_priv_code(13), l_app_priv_code(14), l_app_priv_code(15), l_app_priv_code(16),
       l_app_priv_code(17), l_app_priv_code(18), l_app_priv_code(19), l_app_priv_code(20),
       l_app_priv_code(21), l_app_priv_code(22), l_app_priv_code(23), l_app_priv_code(24),
       l_app_priv_code(25), l_app_priv_code(26), l_app_priv_code(27), l_app_priv_code(28),
       l_app_priv_code(29), l_app_priv_code(30), l_app_priv_code(31), l_app_priv_code(32),
       l_app_priv_code(33), l_app_priv_code(34), l_app_priv_code(35), l_app_priv_code(36),
       l_app_priv_code(37), l_app_priv_code(38), l_app_priv_code(39), l_app_priv_code(40),
       l_app_priv_code(41), l_app_priv_code(42), l_app_priv_code(43), l_app_priv_code(44),
       l_app_priv_code(45), l_app_priv_code(46), l_app_priv_code(47), l_app_priv_code(48),
       l_app_priv_code(49), l_app_priv_code(50), l_app_priv_code(51), l_app_priv_code(52),
       l_app_priv_code(53), l_app_priv_code(54), l_app_priv_code(55), l_app_priv_code(56),
       l_app_priv_code(57), l_app_priv_code(58), l_app_priv_code(59), l_app_priv_code(60),
       l_app_priv_code(61), l_app_priv_code(62), l_app_priv_code(63), l_app_priv_code(64),
       l_app_priv_code(65), l_app_priv_code(66), l_app_priv_code(67), l_app_priv_code(68),
       l_app_priv_code(69), l_app_priv_code(70), l_app_priv_code(71), l_app_priv_code(72),
       l_app_priv_code(73), l_app_priv_code(74), l_app_priv_code(75), l_app_priv_code(76),
       l_app_priv_code(77), l_app_priv_code(78), l_app_priv_code(79), l_app_priv_code(80),
       l_app_priv_code(81), l_app_priv_code(82), l_app_priv_code(83), l_app_priv_code(84),
       l_app_priv_code(85), l_app_priv_code(86), l_app_priv_code(87), l_app_priv_code(88),
       l_app_priv_code(89), l_app_priv_code(90), l_app_priv_code(91), l_app_priv_code(92),
       l_app_priv_code(93), l_app_priv_code(94), l_app_priv_code(95), l_app_priv_code(96),
       l_app_priv_code(97), l_app_priv_code(98), l_app_priv_code(99), l_app_priv_code(100));

    END IF;

  END IF;

EXCEPTION
  WHEN OTHERS THEN
    RAISE;

END update_user_group;

--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 *                                 *
 * PURPOSE                         *
 *                                 *
 * Create a new tagtrak user group                         *
 *                                 *
 * PROCESS                           METHOD     TYPE       *
 * Create a user group with UserGroup Setup (j) and manager (m) privs    Query      Local      *
 *                                 *
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
PROCEDURE add_user_group(p_last_user   IN user_group.last_user%TYPE,
                         p_group_name  IN user_group.group_name%TYPE,
                         p_agent_code  IN user_group.agent_code%TYPE) AS

BEGIN

  INSERT INTO user_role(user_role_name, agent_code)
  VALUES (p_group_name, p_agent_code);

  INSERT INTO user_group
  (group_name, priv1, last_user, agent_code)
  VALUES
  (p_group_name, 'm', p_last_user, p_agent_code);

  COMMIT;


EXCEPTION
  WHEN integrity_constraint THEN
    ROLLBACK;
    raise_application_error(-20000, 'add_user_group : invalid agent_code for '||p_agent_code);

  WHEN DUP_VAL_ON_INDEX THEN
    ROLLBACK;
    raise_application_error(-20027, SQLERRM);

END add_user_group;

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

PROCEDURE grant_privs(p_user_role_id       IN     user_role.user_role_id%TYPE,
                      p_granted_privileges IN     privs_list,
                      p_update_user_group  IN OUT BOOLEAN) IS
BEGIN

  FOR i IN 1 .. p_granted_privileges.COUNT LOOP

    INSERT INTO user_role_priv (user_role_id, app_priv_id)
    VALUES (p_user_role_id, p_granted_privileges(i));

    --Check if we've got any scanner privs and need to update the user_group table.
    IF NOT p_update_user_group
    AND scanner_privilege(p_granted_privileges(i)) THEN
      p_update_user_group := TRUE;
    END IF;

  END LOOP;

END grant_privs;

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

PROCEDURE revoke_privs(p_user_role_id       IN     user_role.user_role_id%TYPE,
                       p_revoked_privileges IN     privs_list,
                       p_update_user_group  IN OUT BOOLEAN) IS

BEGIN

  FOR i IN 1 .. p_revoked_privileges.COUNT LOOP

    --Revoke privilege
    DELETE FROM user_role_priv
    WHERE user_role_id = p_user_role_id
    AND app_priv_id = p_revoked_privileges(i);

    --Check if we've got any scanner privs and need to update the user_group table.
    IF NOT p_update_user_group
    AND scanner_privilege(p_revoked_privileges(i)) THEN
      p_update_user_group := TRUE;
    END IF;

  END LOOP;

END revoke_privs;

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

PROCEDURE update_role(p_user_role_name     IN user_role.user_role_name%TYPE,
                      p_agent_code         IN agents.agent_code%TYPE,
                      p_granted_privileges IN privs_list,
                      p_revoked_privileges IN privs_list,
                      p_user_id            IN tagtrak_users.user_id%TYPE) AS

  l_update_user_group  BOOLEAN := FALSE;
  l_user_role_id       user_role.user_role_id%TYPE;
  l_process            VARCHAR2(100);

BEGIN

  --Get the user_role_id we need.
  SELECT user_role_id INTO l_user_role_id
  FROM user_role
  WHERE trim(user_role_name) = trim(p_user_role_name)
  AND trim(agent_code) = trim(p_agent_code);

  --Do we need to grant any privs?
  IF p_granted_privileges IS NOT NULL THEN

    l_process := 'insert into user_role_priv : ';
    grant_privs(l_user_role_id,
                p_granted_privileges,
                l_update_user_group);

  END IF;

  --Now check if there are any privileges that need to be removed
  IF p_revoked_privileges IS NOT NULL THEN

    l_process := 'delete from user_role_priv : ';
    revoke_privs(l_user_role_id,
                 p_revoked_privileges,
                 l_update_user_group);

  END IF;

  IF l_update_user_group THEN

    l_process := 'update user group : ';
    update_user_group(p_user_role_name,
                      p_agent_code,
                      p_user_id);

  END IF;

  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    raise_application_error(-20000, l_process || SQLERRM);

END update_role;


function User_Has_Privilege
		(p_user_id in tagtrak_users.user_id%type,
		 p_priv    in app_priv.app_priv_name%type) return boolean
is
    l_junk number;
begin

    select 1 into l_junk
	from user_role_priv up, user_role ur, tagtrak_users tu, app_priv ap
	where up.app_priv_id = ap.app_priv_id and
	      ur.user_role_id = up.user_role_id and
	      ur.user_role_name = trim(tu.group_name) and
	      tu.user_id = p_user_id and
	      ap.app_priv_name = p_priv;

    return true;

exception

    when NO_DATA_FOUND then
    	return false;

end User_Has_Privilege;


function User_Has_Privilege
		(p_user_name in tagtrak_users.loginname%type,
		 p_priv      in app_priv.app_priv_name%type) return boolean
is
    l_user_id tagtrak_users.user_id%type;
begin

    select user_id into l_user_id from tagtrak_users
    	where loginname = p_user_name;

    return User_Has_Privilege(l_user_id, p_priv);

end User_Has_Privilege;


--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
END USER_MAINTENANCE;
/
