REM dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
REM dbdrv: checkfile:~PROD:~PATH:~FILE
/*=======================================================================+
 |  Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
 |                            All rights reserved.                       |
 +=======================================================================+
 | FILENAME
 |   wfdirb.sql
 | DESCRIPTION
 |   WF_DIRECTORY package body
 | NOTES
 |
 | MODIFICATION LOG:
 |   01/2002 JWSMITH BUG 2001012 - Increase name, user, l_newname,  
 |           l_oldname,email_address to varchar2(320)
 |   10/2002 CTILLEY BUG 2630801 - Pass ValidateUserRole=>False when calling
 |           CreateUserRole from AddUsersToAdHocRole
 |   03/2003 hgandiko BUG 2779747 - Removed the check on '#','/' during the
 |				    creation of user,role. Also added check
 |				    on MAILHTM2 for validation on 
 |				    notification preference.
 *=======================================================================*/

set verify off
whenever sqlerror exit failure rollback;
whenever oserror exit failure rollback;

create or replace package body Wf_Directory as
/* $Header: wfdirb.pls 26.74 2005/03/03 21:32:50 rwunderl ship $ */

--
-- Private variables and APIs
--
hasBulkSyncView boolean;
g_origSystem    varchar2(30);
g_partitionID   number;
g_localPartitionID number; 
g_partitionName varchar2(30);
g_localPartitionName varchar2(30);

--
-- MinDate (PRIVATE)
--   Return the earliest of the two dates
-- IN
--   date1
--   date2
-- OUT
-- RETURN
--   date
--
function MinDate(date1 in date,
                 date2 in date)
return date
is
begin
  if (date2 is not null) then
    --
    -- when date2 is non-null, so we need to check
    -- both if date1 is null and which is ealier.
    --
    if (date1 is null or date2 < date1) then
      return(date2);
    end if;
  end if;

  -- Note that date1 could be null
  return date1;
end MinDate;

--
-- IsBulkSync (Private)
--   Return true if bulk sync view exists
--
-- RETURN
--   boolean
--
function IsBulkSync
return boolean
is
  cnt number;
begin
  if (hasBulkSyncView is null) then

    select count(1) into cnt
      from USER_VIEWS
     where VIEW_NAME = 'WF_FND_USR_ROLES';

    if (cnt = 0) then
      hasBulkSyncView := false;
    else
      hasBulkSyncView := true;
    end if;
  end if;

  return hasBulkSyncView;
end IsBulkSync;


--
-- String_To_UserTable (PRIVATE)
--   Converts a comma/space delimited string of users into a UserTable
-- IN
--   P_UserList  VARCHAR2
-- OUT
-- RETURN
--   P_UserTable WF_DIRECTORY.UserTable
--
procedure String_To_UserTable (p_UserList  in VARCHAR2, 
                               p_UserTable out NOCOPY WF_DIRECTORY.UserTable) 
is

  c1          pls_integer;
  u1          pls_integer := 0;
  l_userList  varchar2(32000);

begin
  if (p_UserList is not NULL) then
    --
    -- Substring and insert users into UserTable
    --
    l_userList := ltrim(p_UserList);
    <<UserLoop>>
    loop
      c1 := instr(l_userList, ',');
      if (c1 = 0) then
         c1 := instr(l_userList, ' ');
        if (c1 = 0) then
          p_UserTable(u1) := l_userList;
          exit;
        else
          p_UserTable(u1) := substr(l_userList, 1, c1-1);
        end if;
      else
        p_UserTable(u1) := substr(l_userList, 1, c1-1);
      end if;
      u1 := u1 + 1;
      l_userList := ltrim(substr(l_userList, c1+1));
    end loop UserLoop;
  end if;
end String_To_UserTable;


--
-- CompositeName (PRIVATE)
--   Extracts the origSystem/origSystemID from a composite name
-- IN
--   p_CompositeName  VARCHAR2
-- OUT
--   p_CompositeName  VARCHAR2
--   p_origSystem     VARCHAR2
--   p_origSystemID   NUMBER
-- RETURN
--   boolean
--     TRUE - if name is composite
--     FALSE - if name is not composite
--
function CompositeName (p_CompositeName IN VARCHAR2,
                        p_OrigSystem OUT NOCOPY VARCHAR2,
                        p_OrigSystemID OUT NOCOPY NUMBER) return boolean is

  invalidNumConv EXCEPTION;
  pragma exception_init(invalidNumConv, -6502);
  colon NUMBER;
  
  begin
    colon := instr(p_CompositeName,':');
    if (colon <> 0) then
      p_origSystemID := to_number(substrb(p_CompositeName, colon+1));
      p_origSystem := substrb(p_CompositeName, 1, colon-1);
      return TRUE;
    else
      return FALSE;
    end if;
  exception
    when invalidNumConv then
      return FALSE;
    
    when others then
      wf_core.context('Wf_Directory','CompositeName',p_CompositeName);
      raise;
  end;
  
--
-- End Private API section
--

--
-- GETROLEUSERS
--   list of users who perform role
-- IN
--   role
-- OUT
--   table of users that perform the role
--
procedure GetRoleUsers(
  role in varchar2,
  users out NOCOPY Wf_Directory.UserTable)
  
is
  l_origSystem VARCHAR2(30);
  l_origSystemID NUMBER;
  
  cursor c(c_rolename varchar2) is
    select UR.USER_NAME
    from WF_USER_ROLES UR
    where UR.ROLE_NAME = c_rolename
      and UR.PARTITION_ID not in (9,8,7,6,4);

  cursor corig(c_rolename varchar2, c_origSys  varchar2,
               c_origSysID number) is
    select UR.USER_NAME
    from WF_USER_ROLES UR
    where UR.ROLE_ORIG_SYSTEM = c_origSys
    and UR.ROLE_ORIG_SYSTEM_ID = c_origSysID
    and UR.ROLE_NAME = c_rolename;

begin
  if (compositeName(role, l_origSystem, l_origSystemID)) then
    open corig(role, l_origSystem, l_origSystemID);
    fetch corig bulk collect into users;
    close corig;
  else
    open c(role);
    fetch c bulk collect into users;
    close c;
  end if;
exception
  when others then
    if c%ISOPEN then
      close c;
    elsif corig%ISOPEN then
      close corig;
    end if;
    wf_core.context('Wf_Directory','GetRoleUsers',Role);
    raise;
end GetRoleUsers;

--
-- GETUSERRELATION
--   list of users associated with a user
-- IN
--   base user
--   relationship
-- OUT
--   table of related users
-- NOTES
--   currently unimplemented!
--   different relations may be supported by different directory services,
--   so the implementation of this procedure is expected to vary.
--   Example relationships are 'MANAGER', 'REPORT', 'HR_REP'
--
procedure GetUserRelation(
  base_user in varchar2,
  relation in varchar2,
  users out NOCOPY Wf_Directory.UserTable)
is
begin
  null;
exception
  when others then
        wf_core.context('Wf_Directory','GetUserRelation',base_user,relation);
        raise;
end GetUserRelation;

--
-- GETUSERROLES
--   list of roles performed by user
-- IN
--   user
-- OUT
--   table of roles performed by the user
--
procedure GetUserRoles(
  user in varchar2,
  roles out NOCOPY Wf_Directory.RoleTable)
is
  l_origSystem VARCHAR2(30);
  l_origSystemID NUMBER;
  
  cursor c(c_username varchar2) is
    select UR.ROLE_NAME
    from   WF_USER_ROLES UR
    where UR.USER_NAME = c_username
      and UR.USER_ORIG_SYSTEM not in ('HZ_PARTY','CUST_CONT');

  cursor corig(c_username varchar2,
               c_origSystem varchar2,
               c_origSystemID number) is
    select UR.ROLE_NAME
    from WF_USER_ROLES UR
    where UR.USER_ORIG_SYSTEM = c_origSystem
    and UR.USER_ORIG_SYSTEM_ID = c_origSystemID
    and UR.USER_NAME = c_username;

begin
  if (CompositeName(user, l_origSystem, l_origSystemID)) then
    open corig(user, l_origSystem, l_origSystemID);
    fetch corig bulk collect into roles;
    close corig;
  else
    open c(user);
    fetch c bulk collect into roles;
    close c;
  end if;
exception
  when others then
    if (c%ISOPEN) then
      close c;
    elsif (corig%ISOPEN) then
      close corig;
    end if;
     
    wf_core.context('Wf_Directory','GetUserRoles',User);
    raise;
end GetUserRoles;

--
-- GETROLEINFO
--   information about a role
-- IN
--  role
-- OUT
--   display_name
--   email_address
--   notification_preference
--   language
--   territory
--
procedure GetRoleInfo(
  role in varchar2,
  display_name out NOCOPY varchar2,
  email_address out NOCOPY varchar2,
  notification_preference out NOCOPY varchar2,
  language out NOCOPY varchar2,
  territory out NOCOPY varchar2)
is
  role_info_tbl wf_directory.wf_local_roles_tbl_type;
begin
  Wf_Directory.GetRoleInfo2(role, role_info_tbl);

  display_name            := role_info_tbl(1).display_name;
  email_address           := role_info_tbl(1).email_address;
  notification_preference := role_info_tbl(1).notification_preference;
  language                := role_info_tbl(1).language;
  territory               := role_info_tbl(1).territory;

exception
  when others then
    wf_core.context('Wf_Directory','GetRoleInfo',Role);
    raise;
end GetRoleInfo;

--
-- GETROLEINFO2
--   information about a role
-- IN
--  role
-- OUT
--   role_info_tbl
--
procedure GetRoleInfo2(
  role in varchar2,
  role_info_tbl out NOCOPY wf_directory.wf_local_roles_tbl_type)
is
  l_origSystem VARCHAR2(30);
  l_origSystemID NUMBER;
  l_isComposite BOOLEAN;

begin
  --Check for composite name.
  l_isComposite := CompositeName(role, l_origSystem, l_origSystemID);

  if (l_isComposite) then
    /*
    ** First try to get the role information from the new 
    ** wfa_sec.get_role_info function.  This function looks at each component 
    ** of the wf_roles view and attempts to get the information from there.  
    ** If it does not find the role then use the old method of looking at the 
    ** view.
    */

    --Get all info including expiration_date , fax and 
    --status for the role.  Call get_role_info2 
    wfa_sec.get_role_info2(role,     
                           role_info_tbl(1).name,
                           role_info_tbl(1).display_name,
                           role_info_tbl(1).description,
                           role_info_tbl(1).email_address,
                           role_info_tbl(1).notification_preference,
                           role_info_tbl(1).language,
                           role_info_tbl(1).territory,
                           role_info_tbl(1).orig_system,
                           role_info_tbl(1).orig_system_id,
                           role_info_tbl(1).fax,
                           role_info_tbl(1).status,
                           role_info_tbl(1).expiration_date);
      
  end if;  
  if (role_info_tbl.COUNT = 0 or role_info_tbl(1).display_name is NULL) then

    if NOT (l_isComposite) then
      -- try to select from all the ORIG_SYSTEMs that have no colon.
      -- we query FNDRESPXXX also since we cannot list all of them here.
      -- it is safer to use 'not in', in case of custom additions.
      select R.NAME,
             substrb(R.DISPLAY_NAME,1,360),
             substrb(R.DESCRIPTION,1,1000),
             R.NOTIFICATION_PREFERENCE,
             R.LANGUAGE,
             R.TERRITORY,
             substrb(R.EMAIL_ADDRESS,1,320),
             R.FAX,
             R.STATUS,
             R.EXPIRATION_DATE,
             R.ORIG_SYSTEM,
             R.ORIG_SYSTEM_ID,
             R.PARENT_ORIG_SYSTEM,
             R.PARENT_ORIG_SYSTEM_ID,
             R.OWNER_TAG,
             R.LAST_UPDATE_DATE,
             R.LAST_UPDATED_BY,
             R.CREATION_DATE,
             R.CREATED_BY,
             R.LAST_UPDATE_LOGIN
      into   role_info_tbl(1)
      from   WF_LOCAL_ROLES R
      where  R.NAME = GetRoleInfo2.role
      and    R.PARTITION_ID not in (9,8,7,6,4)
      and    nvl(R.EXPIRATION_DATE, sysdate+1) > sysdate
      and    rownum = 1;

       if (role_info_tbl(1).orig_system in ('FND_USR', 'PER')) then
       -- Until FND completes and releases 2660873 this code will call
       -- WF_PREF to dynamically populate the notification_preference,
       -- language, and territory.
         role_info_tbl(1).notification_preference := 
           nvl(WF_PREF.get_pref(role_info_tbl(1).name, 'MAILTYPE'), 
              role_info_tbl(1).notification_preference);
 
         role_info_tbl(1).language :=
           nvl(WF_PREF.get_pref(role_info_tbl(1).name, 'LANGUAGE'),
               role_info_tbl(1).language);
 
         role_info_tbl(1).territory :=
           nvl(WF_PREF.get_pref(role_info_tbl(1).name, 'TERRITORY'),
               role_info_tbl(1).territory);
 
       end if; 
    else
      select R.NAME,
             substrb(R.DISPLAY_NAME,1,360),
             substrb(R.DESCRIPTION,1,1000),
             R.NOTIFICATION_PREFERENCE,
             R.LANGUAGE,
             R.TERRITORY,
             substrb(R.EMAIL_ADDRESS,1,320),
             R.FAX,
             R.STATUS,
             R.EXPIRATION_DATE,
             R.ORIG_SYSTEM,
             R.ORIG_SYSTEM_ID,
             R.PARENT_ORIG_SYSTEM,
             R.PARENT_ORIG_SYSTEM_ID,
             R.OWNER_TAG,
             R.LAST_UPDATE_DATE,
             R.LAST_UPDATED_BY,
             R.CREATION_DATE,
             R.CREATED_BY,
             R.LAST_UPDATE_LOGIN
      into   role_info_tbl(1)
      from   WF_LOCAL_ROLES R
      where  R.ORIG_SYSTEM = l_origSystem
      and    R.ORIG_SYSTEM_ID = l_origSystemID
      and    R.NAME = GetRoleInfo2.role
      and    nvl(R.EXPIRATION_DATE, sysdate+1) > sysdate
      and    rownum = 1;
    end if;

  else
    role_info_tbl(1).name := role;
  end if;

exception
  when no_data_found then
  --If the role is not found in the local tables, we will check the view to make
  --sure we continue to support standalone which has not denormalized wfds.
  begin
    if NOT (l_isComposite) then
      select R.NAME,
             substrb(R.DISPLAY_NAME,1,360),
             substrb(R.DESCRIPTION,1,1000),
             R.NOTIFICATION_PREFERENCE,
             R.LANGUAGE,
             R.TERRITORY,
             substrb(R.EMAIL_ADDRESS,1,320),
             R.FAX,
             R.STATUS,
             R.EXPIRATION_DATE,
             R.ORIG_SYSTEM,
             R.ORIG_SYSTEM_ID,
             NULL,
             to_number(NULL),
             NULL,
             to_date(NULL),
             to_number(NULL),
             to_date(NULL),
             to_number(NULL),
             to_number(NULL)
      into   role_info_tbl(1)
      from   WF_ROLES R
      where  R.NAME = GetRoleInfo2.role
      and    R.PARTITION_ID not in (9,8,7,6,4)
      and    rownum = 1;
      else   
        select R.NAME,
               substrb(R.DISPLAY_NAME,1,360),
               substrb(R.DESCRIPTION,1,1000),
               R.NOTIFICATION_PREFERENCE,
               R.LANGUAGE,
               R.TERRITORY,
               substrb(R.EMAIL_ADDRESS,1,320),
               R.FAX,
               R.STATUS,
               R.EXPIRATION_DATE,
               R.ORIG_SYSTEM,
               R.ORIG_SYSTEM_ID,
               NULL,
               to_number(NULL),
               NULL,
               to_date(NULL),
               to_number(NULL),
               to_date(NULL),
               to_number(NULL),
               to_number(NULL)
       into   role_info_tbl(1)
       from   WF_ROLES R
       where  R.ORIG_SYSTEM = l_origSystem
       and    R.ORIG_SYSTEM_ID = l_origSystemID
       and    R.NAME = GetRoleInfo2.role
       and    rownum = 1;
      end if;
    exception
      when NO_DATA_FOUND then
        role_info_tbl(1).name := '';
        role_info_tbl(1).display_name := '';
        role_info_tbl(1).description := '';
        role_info_tbl(1).notification_preference := '';
        role_info_tbl(1).language := '';
        role_info_tbl(1).territory := '';
        role_info_tbl(1).email_address := '';
        role_info_tbl(1).fax := '';
        role_info_tbl(1).status := '';
        role_info_tbl(1).expiration_date := to_date(null);
        role_info_tbl(1).orig_system := '';
        role_info_tbl(1).orig_system_id := to_number(null);
        role_info_tbl(1).parent_orig_system := '';
        role_info_tbl(1).parent_orig_system_id := to_number(null);
        role_info_tbl(1).owner_tag := null;
        role_info_tbl(1).last_update_date := to_date(null);
        role_info_tbl(1).last_updated_by := to_number(null);
        role_info_tbl(1).creation_date := to_date(null);
        role_info_tbl(1).last_update_login := to_number(null);
    end;
  
  when others then
    wf_core.context('Wf_Directory','GetRoleInfo2',Role);
    raise;
end GetRoleInfo2;

--
-- GETROLEINFOMAIL
--   All information about a role for mailer
-- IN
--  role
-- OUT
--   display_name
--   email_address
--   notification_preference
--   language
--   territory
--   orig_system
--   orig_system_id
--   installed_flag - Y when a language is installed in WF_LANGUAGES,
--                    N otherwise.
--
procedure GetRoleInfoMail(
  role in varchar2,
  display_name out NOCOPY varchar2,
  email_address out NOCOPY varchar2,
  notification_preference out NOCOPY varchar2,
  language out NOCOPY varchar2,
  territory out NOCOPY varchar2,
  orig_system out NOCOPY varchar2,
  orig_system_id out NOCOPY number,
  installed_flag out NOCOPY varchar2)
is
  role_info_tbl wf_directory.wf_local_roles_tbl_type;
begin

  Wf_Directory.GetRoleInfo2(role, role_info_tbl);

  display_name            := role_info_tbl(1).display_name;
  email_address           := role_info_tbl(1).email_address;
  notification_preference := role_info_tbl(1).notification_preference;
  language                := role_info_tbl(1).language;
  territory               := role_info_tbl(1).territory;
  orig_system             := role_info_tbl(1).orig_system;
  orig_system_id          := role_info_tbl(1).orig_system_id;

  begin
    select nvl(INSTALLED_FLAG, 'N')
      into GetRoleInfoMail.installed_flag
      from WF_LANGUAGES
     where NLS_LANGUAGE = GetRoleInfoMail.language;
  exception
    when NO_DATA_FOUND then
      installed_flag := 'N';
  end;

exception
  when others then
    wf_core.context('Wf_Directory','GetRoleInfoMail',Role);
    raise;
end GetRoleInfoMail;

--
-- GETROLENTFPREF 
--   Obtains the notification preference for a given role
-- IN 
--  role 
-- OUT 
--  notification_preference
--
function GetRoleNtfPref(
  role in varchar2) return varchar2
is
  role_info_tbl wf_directory.wf_local_roles_tbl_type;
  notification_preference varchar2(8);
begin

  Wf_Directory.GetRoleInfo2(role, role_info_tbl);
  notification_preference := role_info_tbl(1).notification_preference;
  
  return notification_preference;
exception
  when others then
    wf_core.context('Wf_Directory','GetRoleNotePref',Role);
    raise;
end GetRoleNtfPref;

--
-- GETROLEORIGSYSINFO
--   orig system information about a role
-- IN
--  role
-- OUT
--   orig_system
--   orig_system_id
--
procedure GetRoleOrigSysInfo(
  role in varchar2,
  orig_system out NOCOPY varchar2,
  orig_system_id out NOCOPY number
)
is
  role_info_tbl wf_directory.wf_local_roles_tbl_type;
begin
  Wf_Directory.GetRoleInfo2(role, role_info_tbl);

  orig_system    := role_info_tbl(1).orig_system;
  orig_system_id := role_info_tbl(1).orig_system_id;

exception
  when others then
    wf_core.context('Wf_Directory','GetRoleOrigSysInfo',Role);
    raise;
end GetRoleOrigSysInfo;

--
-- ISPERFORMER
--   test if user performs role
--
function IsPerformer(
    user in varchar2,
    role in varchar2) return boolean
is
  userComposite boolean;
  roleComposite boolean;
  l_uorigSys varchar2(30);
  l_uorigSysID number;
  l_rorigSys varchar(30);
  l_rorigSysID number;
  
  dummy pls_integer;

begin
  userComposite := CompositeName(user, l_uorigSys, l_uorigSysID);
  roleComposite := CompositeName(role, l_rorigSys, l_rorigSysID);
  
  if NOT (roleComposite) then
    if NOT (userComposite) then
      select 1
      into dummy
      from SYS.DUAL
      where exists
        (select null
        from WF_USER_ROLES UR
        where UR.USER_NAME = IsPerformer.user
        and UR.USER_ORIG_SYSTEM not in ('HZ_PARTY')
        and UR.ROLE_NAME = IsPerformer.role
        and UR.PARTITION_ID not in (9,8,7,6,4)
        );
    else
      select 1
      into dummy
      from SYS.DUAL
      where exists
        (select null
        from WF_USER_ROLES UR
        where UR.USER_ORIG_SYSTEM = l_uOrigSys
        and UR.USER_ORIG_SYSTEM_ID = l_uOrigSysID
        and UR.USER_NAME = IsPerformer.user
        and UR.ROLE_NAME = IsPerformer.role
        and UR.PARTITION_ID not in (9,8,7,6,4)
        );
    end if;
  else
    if NOT (userComposite) then
      select 1
      into dummy
      from SYS.DUAL
      where exists
        (select null
        from WF_USER_ROLES UR
        where UR.USER_NAME = IsPerformer.user
        and UR.USER_ORIG_SYSTEM not in ('HZ_PARTY')
        and UR.ROLE_ORIG_SYSTEM = l_rorigSys
        and UR.ROLE_ORIG_SYSTEM_ID = l_rorigSysID
        and UR.ROLE_NAME = IsPerformer.role);
    else
      select 1
      into dummy
      from SYS.DUAL
      where exists
        (select null
        from WF_USER_ROLES UR
        where UR.USER_ORIG_SYSTEM = l_uOrigSys
        and UR.USER_ORIG_SYSTEM_ID = l_rOrigSysID
        and UR.USER_NAME = IsPerformer.user
        and UR.ROLE_ORIG_SYSTEM = l_rOrigSys
        and UR.ROLE_ORIG_SYSTEM_ID = l_rOrigSysID
        and UR.ROLE_NAME = IsPerformer.role);
    end if;
  end if;
  return TRUE;
exception
  when no_data_found then
    return FALSE;
  when others then
    wf_core.context('Wf_Directory','IsPerformer',User,Role);
    raise;
end IsPerformer;

--
-- CURRENTUSER
--   user name for current db session
-- NOTES
--   unimplemented!  This needs more thought.
--
function CurrentUser return varchar2
is
begin
  return NULL;
exception
  when others then
    wf_core.context('Wf_Directory','CurrentUser');
    raise;
end CurrentUser;

--
-- USERACTIVE
--   determine if a user is currently active
-- IN
--   username
-- RETURN:
--   True  - If user is Active
--   False - If User is NOT Active
--
function UserActive(
  username in varchar2)
return boolean
is
  colon pls_integer;
  dummy pls_integer;
begin
  colon := instr(username, ':');
  if (colon = 0) then
    select 1
    into dummy
    from SYS.DUAL
    where exists
      (select null
      from wf_users
      where name = username
      and PARTITION_ID <> 9
      and status = 'ACTIVE');
  else
    select 1
    into dummy
    from SYS.DUAL
    where exists
      (select null
      from wf_users
      where orig_system = substr(username, 1, colon-1)
      and orig_system_id = substr(username, colon+1)
      and name = username
      and status = 'ACTIVE');
  end if;

  return TRUE;
exception
  when no_data_found then
    return FALSE;
  when others then
    wf_core.context('Wf_Directory','UserActive',Username);
    raise;
end UserActive;

--
-- GETUSERNAME
--   returns the Workflow username given the originating system info
-- IN
--   orig_system     - Code identifying the original table
--   orig_system_id  - Id of the row in original table
-- OUT
--   user_name       - Workflow user_name
--   display_name    - Users display name
--
procedure GetUserName(p_orig_system    in  varchar2,
                      p_orig_system_id in  varchar2,
                      p_name           out NOCOPY varchar2,
                      p_display_name   out NOCOPY varchar2)
is
  cursor c_user is
    select name,
           substrb(display_name,1,360)
           p_display_name
    from   wf_users
    where  orig_system     = p_orig_system
    and    orig_system_id  = p_orig_system_id
    order by status, start_date;   

begin
    open  c_user;
    fetch c_user into p_name, p_display_name;
    close c_user;
exception
  when others then
    wf_core.context('Wf_Directory','GetUserName', p_orig_system, 
                    p_orig_system_id);
    raise;
end GetuserName;

--
-- GETROLENAME
--   returns the Workflow rolename given the originating system info
-- IN
--   orig_system     - Code identifying the original table
--   orig_system_id  - Id of the row in original table
-- OUT
--   name            - Workflow role name
--   display_name    - role display name
--
procedure GetRoleName(p_orig_system    in  varchar2,
                      p_orig_system_id in  varchar2,
                      p_name           out NOCOPY varchar2,
                      p_display_name   out NOCOPY varchar2)
is
  cursor c_role is
    select name,
           substrb(display_name,1,360)
           p_display_name
    from   wf_roles
    where  orig_system     = p_orig_system
    and    orig_system_id  = p_orig_system_id
    order by status, start_date;
begin
    open  c_role;
    fetch c_role into p_name,p_display_name;
    close c_role;
exception
  when others then
    wf_core.context('Wf_Directory','GetRoleName',p_orig_system,p_orig_system);
    raise;
end GetRoleName;

--
-- GetRoleDisplayName
--   Return display name of role
-- IN
--   p_role_name - internal name of role
-- RETURNS
--   role display name
--
-- NOTE
--   Cannot implement using GetRoleInfo/GetRoleInfo2, because of the
-- pragma WNPS.
--
function GetRoleDisplayName (
  p_role_name in varchar2)
return varchar2
is
  colon pls_integer;

  cursor c_role is
    select substrb(display_name,1,360)
    from wf_roles
    where name = p_role_name
    and   PARTITION_ID not in (9,8,7,6,4);

  cursor corig_role is
    select substrb(display_name,1,360)
    from wf_roles
    where orig_system = substr(p_role_name, 1, colon-1)
    and orig_system_id = substr(p_role_name, colon+1)
    and name = p_role_name;

  l_display_name wf_roles.display_name%TYPE;
begin
  colon := instr(p_role_name, ':');
  if (colon = 0) then
    open c_role;
    fetch c_role into l_display_name;
    close c_role;
  else
    open corig_role;
    fetch corig_role into l_display_name;
    close corig_role;
  end if;

  return l_display_name;
end GetRoleDisplayName;

--
-- SetAdHocUserStatus
--   Update status for user
-- IN
--   user_name        -
--   status           - status could be 'ACTIVE' or 'INACTIVE'
-- OUT
--
procedure SetAdHocUserStatus(user_name      in varchar2,
                             status         in varchar2)
is
begin
  --
  -- Update Status
  --
  SetUserAttr(user_name=>SetAdHocUserStatus.user_name,
              orig_system=>'WF_LOCAL_USERS',
              orig_system_id=>0,
              display_name=>NULL,
              notification_preference=>NULL,
              language=>NULL,
              territory=>NULL,
              email_address=>NULL,
              fax=>NULL,
              expiration_date=>NULL,
              status=>SetAdHocUserStatus.status);

exception
  when others then
    wf_core.context('Wf_Directory', 'SetAdHocUserStatus', user_name, status);
    raise;
end SetAdHocUserStatus;

--
-- SetAdHocRoleStatus
--   Update status for role
-- IN
--   role_name        -
--   status           - status could be 'ACTIVE' or 'INACTIVE'
-- OUT
--
procedure SetAdHocRoleStatus(role_name      in varchar2,
                        status         in varchar2)
is
begin
  --
  -- Update Status
  --
  SetRoleAttr(role_name=>SetAdHocRoleStatus.role_name,
              orig_system=>'WF_LOCAL_ROLES',
              orig_system_id=>0,
              display_name=>NULL,
              notification_preference=>NULL,
              language=>NULL,
              territory=>NULL,
              email_address=>NULL,
              fax=>NULL,
              expiration_date=>NULL,
              status=>SetAdHocRoleStatus.status);
              
exception
  when others then
    wf_core.context('Wf_Directory', 'SetAdHocRoleStatus', role_name, status);
    raise;
end SetAdHocRoleStatus;


--
-- CreateUser (PRIVATE)
--   Create a User
-- IN
--   name          - User Name
--   display_name  - User display name
--   description   -
--   notification_preference -
--   language      -
--   territory     -
--   email_address -
--   fax           -
--   status        -
--   expiration_date - NULL expiration date means no expiration
--   orig_system
--   orig_system_id
--   parent_orig_system
--   parent_orig_system_id
--   owner_tag -
--   last_update_date -
--   last_updated_by - 
--   creation_date -
--   created_by -
--   last_update_login
-- OUT
--

procedure CreateUser( name                    in  varchar2,
                      display_name            in  varchar2,
                      orig_system             in  varchar2,
                      orig_system_id          in  number,
                      language                in  varchar2,
                      territory               in  varchar2,
                      description             in  varchar2,
                      notification_preference in  varchar2,
                      email_address           in  varchar2,
                      fax                     in  varchar2,
                      status                  in  varchar2,
                      expiration_date         in  date,
                      start_date              in  date,
                      parent_orig_system      in  varchar2,
                      parent_orig_system_id   in  number,
                      owner_tag               in  varchar2,
                      last_update_date        in  date,
                      last_updated_by         in  number,
                      creation_date           in  date,
                      created_by              in  number,
                      last_update_login       in  number) 
  is
    nlang   varchar2(30);
    nterr   varchar2(30);
    l_partitionID number;
    l_partitionName varchar2(30);
    l_origSys VARCHAR2(30);
    
  begin

    -- [Name Validation]
    -- If concat name is passed, check to make sure it is valid.
    --
    if ( instr(name, ':') > 0 ) then
      if ( (orig_system||':'||orig_system_id ) <> name) then
        WF_CORE.Token('NAME', name);
        WF_CORE.Token('ORIG_SYSTEM', orig_system);
        WF_CORE.Token('ORIG_SYS_ID', orig_system_id);
        WF_CORE.Raise('WF_INVAL_CONCAT_NAME');
  
      end if;
  
    end if;
  
    --
    -- Make sure no '#' or '/' exist in name.
    --
    /* Bug 2779747
    if ( (instr(name, '/') > 0) or (instr(name, '#') > 0) ) then
      WF_CORE.Token('ROLENAME', name);
      WF_CORE.Raise('WF_INVALID_ROLE');
    */

    --
    -- Make sure the length of the name is  <= 320
    --
    if ( lengthb(name) > 320 ) then
      WF_CORE.Token('NAME', name);
      WF_CORE.Token('LENGTH', 320);
      WF_CORE.Raise('WF_ROLENAME_TOO_LONG');

    end if;

    --
    -- [Status Validation]
    --
    if ( CreateUser.status not in ('ACTIVE', 'TMPLEAVE', 'EXTLEAVE',
                                   'INACTIVE') ) then

      WF_CORE.Token('STATUS', CreateUser.status);
      WF_CORE.Raise('WF_INVALID_ROLE_STATUS');
  
    end if;

    --
    -- [Notification_Preference Validation]
    --
    -- Bug 2779747
    if ( CreateUser.notification_preference not in ('MAILHTML','MAILHTM2',
         'MAILATTH', 'SUMMARY', 'SUMHTML', 'QUERY', 'MAILTEXT','DISABLED') ) then
      WF_CORE.Token('NTF_PREF', CreateUser.notification_preference);
      WF_CORE.Raise('WF_INVALID_NTF_PREF');
  
    end if;

    --
    -- Resolve Territory and Language
    --
    if (language is null or territory is null) then
      begin
        select nls_territory, nls_language into nterr, nlang
          from WF_LANGUAGES
         where code = userenv('LANG');
      exception
        when NO_DATA_FOUND then
          wf_core.raise('WF_NO_LANG_TERR');
      end;
  
    end if;
 
    l_origSys := UPPER(CreateUser.orig_system);
    --
    -- Set the partition for the orig_system
    --
      AssignPartition(CreateUser.orig_system,
                      l_partitionID, l_partitionName); 
 
    --
    -- Insert WF_LOCAL_ROLES with USER_FLAG = 'Y' 
    --
      insert into WF_LOCAL_ROLES
         (name,
          display_name,
          description,
          notification_preference,
          language,
          territory,
          email_address,
          fax,
          status,
          expiration_date,
          orig_system,
          orig_system_id,
          start_date,
          user_flag,
          partition_id,
          parent_orig_system,
          parent_orig_system_id,
          owner_tag,
          last_update_date,
          last_updated_by,
          creation_date,
          created_by,
          last_update_login)
        values
         ( nvl(CreateUser.name, l_origSys || ':' ||
                                      CreateUser.orig_system_id),
           nvl(CreateUser.display_name, (nvl(CreateUser.name, 
                                             l_origSys || ':' ||
                                             CreateUser.orig_system_id))),
           CreateUser.description,
           nvl(CreateUser.notification_preference, 'MAILHTML'),
           nvl(CreateUser.language, nlang),
           nvl(CreateUser.territory, nterr),
           CreateUser.email_address,
           CreateUser.fax,
           CreateUser.status,
           CreateUser.expiration_date,
           l_origSys,
           CreateUser.orig_system_id,
           CreateUser.start_date,
           'Y',
           l_partitionID,
           nvl(CreateUser.parent_orig_system, CreateUser.orig_system),
           nvl(CreateUser.parent_orig_system_id, CreateUser.orig_system_id),
           CreateUser.owner_tag,
           CreateUser.last_update_date,
           CreateUser.last_updated_by,
           CreateUser.creation_date,
           CreateUser.created_by,
           CreateUser.last_update_login );

     --If MLS language support is enabled for this orig_system
     --then sync the data to _TL table aswell.
     if ((WF_DIRECTORY.IsMLSEnabled(l_origSys) = TRUE) AND 
         (userenv('LANG') <> 'US')) then
      insert into WF_LOCAL_ROLES_TL
         ( NAME,
           ORIG_SYSTEM,
           ORIG_SYSTEM_ID,
           PARTITION_ID,
           LANGUAGE,
           DISPLAY_NAME,
           DESCRIPTION,
           OWNER_TAG,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY,
           CREATION_DATE,
           CREATED_BY,
           LAST_UPDATE_LOGIN)
        values
          ( nvl(CreateUser.name, l_origSys || ':' || CreateUser.orig_system_id),
            l_origSys,
            CreateUser.orig_system_id,
            l_partitionID,
            userenv('LANG'),
            nvl(CreateUser.display_name, (nvl(CreateUser.name,
                                              l_origSys || ':' ||
                                              CreateUser.orig_system_id))),
            CreateUser.description,
            CreateUser.owner_tag,
            CreateUser.last_update_date,
            CreateUser.last_updated_by,
            CreateUser.creation_date,
            CreateUser.created_by,
            CreateUser.last_update_login);
    end if;
            

 
    --All Users belong to their own user/role relationship.
    begin
      CreateUserRole(user_name=>CreateUser.name,
                     role_name=>CreateUser.name,
                     user_orig_system=>l_origSys,
                     user_orig_system_id=>CreateUser.orig_system_id,
                     role_orig_system=>l_origSys,
                     role_orig_system_id=>CreateUser.orig_system_id,       
                     start_date=>CreateUser.start_date,
                     end_date=>CreateUser.expiration_date,
                     validateUserRole=>FALSE,
                     parent_orig_system=>CreateUser.parent_orig_system,
                     parent_orig_system_id=>CreateUser.parent_orig_system_id,
                     owner_tag=>CreateUser.Owner_Tag,
                     last_update_date=>CreateUser.last_update_date,
                     last_updated_by=>CreateUser.last_updated_by,
                     creation_date=>CreateUser.creation_date,
                     created_by=>CreateUser.created_by,
                     last_update_login=>CreateUser.last_update_login);

    exception
      when OTHERS then
        if (WF_CORE.error_name = 'WF_DUP_USER_ROLE') then
          SetUserRoleAttr(user_name=>CreateUser.name,
                          role_name=>CreateUser.name,
                          user_orig_system=>l_origSys,
                          user_orig_system_id=>CreateUser.orig_system_id,
                          role_orig_system=>l_origSys,
                          role_orig_system_id=>CreateUser.orig_system_id,
                          start_date=>CreateUser.start_date,
                          end_date=>CreateUser.expiration_date,
                          overWrite=>TRUE,
                          parent_orig_system=>CreateUser.parent_orig_system,
                          parent_orig_system_id=>
                                               CreateUser.parent_orig_system_id,
                          owner_tag=>CreateUser.owner_tag,
                          last_update_date=>CreateUser.last_update_date,
                          last_updated_by=>CreateUser.last_updated_by,
                          last_update_login=>CreateUser.last_update_login);

        else
          raise;

        end if;
    end;
exception
  when DUP_VAL_ON_INDEX then
    WF_CORE.Token('DISPNAME', CreateUser.display_name);
    WF_CORE.Token('USERNAME', nvl(CreateUser.name,
                                        l_origSys || ':' ||
                                        CreateUser.orig_system_id));
    WF_CORE.Raise('WF_DUP_USER');
    
  when others then
    wf_core.context('Wf_Directory', 'CreateUser', CreateUser.Name, 
                    l_origSys, CreateUser.orig_system_id );
    raise;
end CreateUser;


--
-- CreateAdHocUser
--   Create an ad hoc user given a user name, display name, etc.
-- IN
--   name          - User name
--   display_name  - User display name
--   description   -
--   notification_preference -
--   language      -
--   territory     -
--   email_address -
--   fax           -
--   status        -
--   expiration_date - NULL expiration date means no expiration
-- OUT
--
procedure CreateAdHocUser(name                in out NOCOPY varchar2,
                      display_name            in out NOCOPY varchar2,
                      language                in  varchar2,
                      territory               in  varchar2,
                      description             in  varchar2,
                      notification_preference in  varchar2,
                      email_address           in  varchar2,
                      fax                     in  varchar2,
                      status                  in  varchar2,
                      expiration_date         in  date,
                      parent_orig_system      in  varchar2,
                      parent_orig_system_id   in  number)
is
  role_id pls_integer;
  d1      pls_integer;

begin
  --
  -- Check if user name and display name exists in wf_users
  --
  if (name is not null or display_name is not null) then

    /* GK: The display name does not have to be unique
    
    select count(1) into d1
      from wf_users u
      where u.name = CreateAdHocUser.name
         or u.display_name = CreateAdHocUser.display_name;
    if (d1 > 0) then
      wf_core.token('USERNAME', CreateAdHocUser.name);
      wf_core.token('DISPNAME', CreateAdHocUser.display_name);
      wf_core.raise('WF_DUP_USER');
    end if;

    */
  
    NULL;

  else
  --
  -- Create user name if not exist
  --
    begin
      select to_char(WF_ADHOC_ROLE_S.NEXTVAL)
      into role_id
      from SYS.DUAL;
    exception
      when others then
        raise;
    end;

    CreateAdHocUser.name := '~WF_ADHOC-' || role_id;
    CreateAdHocUser.display_name := CreateAdHocUser.name;
  end if;

  CreateUser(CreateAdHocUser.name,
             CreateAdHocUser.display_name,
             'WF_LOCAL_USERS',
             0,
             CreateAdHocUser.language,
             CreateAdHocUser.territory,
             CreateAdHocUser.description,
             CreateAdHocUser.notification_preference,
             CreateAdHocUser.email_address,
             CreateAdHocUser.fax,
             CreateAdHocUser.status,
             CreateAdHocUser.expiration_date,
             CreateAdhocUser.parent_orig_system,
             CreateAdhocUser.parent_orig_system_id);


exception
  when others then
    wf_core.context('Wf_Directory', 'CreateAdHocUser');
    raise;
end CreateAdHocUser;


--
-- CreateRole (PRIVATE)
--   Create a role given a specific name
-- IN
--   role_name          -
--   role_display_name  -
--   role_description   -
--   notification_preference -
--   language           -
--   territory          -
--   email_address      -
--   fax                -
--   status             -
--   start_date         - defaults to sysdate
--   expiration_date   - Null means no expiration date
-- OUT
--
procedure CreateRole( role_name               in  varchar2,
                      role_display_name       in  varchar2,
                      orig_system             in  varchar2,
                      orig_system_id          in  number,
                      language                in  varchar2,
                      territory               in  varchar2,
                      role_description        in  varchar2,
                      notification_preference in  varchar2,
                      email_address           in  varchar2,
                      fax                     in  varchar2,
                      status                  in  varchar2,
                      expiration_date         in  date,
                      start_date              in  date,
                      parent_orig_system      in  varchar2,
                      parent_orig_system_id   in  number,
                      owner_tag               in  varchar2,
                      last_update_date        in  date,
                      last_updated_by         in  number,
                      creation_date           in  date,
                      created_by              in  number,
                      last_update_login       in  number )

is
  nlang         varchar2(30);
  nterr         varchar2(30);
  l_partitionID NUMBER;
  l_partitionName VARCHAR2(30);
  l_origSys     VARCHAR2(30);

  
begin

  -- These validations are also performed in CreateUser.  Should the 
  -- validations become resource intensive such as accessing DB, we
  -- might want to have a private variable to indicate if this is 
  -- being called by CreateUser.

  -- [Name Validation]
  -- If concat role_name is passed, check to make sure it is valid.
  --
  if ( instr(role_name, ':') > 0 ) then
    if ( (orig_system||':'||orig_system_id ) <> role_name) then
      WF_CORE.Token('NAME', role_name);
      WF_CORE.Token('ORIG_SYSTEM', orig_system);
      WF_CORE.Token('ORIG_SYS_ID', orig_system_id);
      WF_CORE.Raise('WF_INVAL_CONCAT_NAME');

    end if;

  end if;

  --
  -- Make sure no '#' or '/' exist in role_name.
  --
  /* Bug 2779747
  if ( (instr(role_name, '/') > 0) or (instr(role_name, '#') > 0) ) then
    WF_CORE.Token('ROLENAME', role_name);
    WF_CORE.Raise('WF_INVALID_ROLE');
  */

  --
  -- Make sure the length of the role_name is  <= 320
  --
  if ( lengthb(role_name) > 320 ) then
    WF_CORE.Token('NAME', role_name);
    WF_CORE.Token('LENGTH', 320);
    WF_CORE.Raise('WF_ROLENAME_TOO_LONG');

  end if;

  --
  -- [Status Validation]
  --
  if ( CreateRole.status not in ('ACTIVE', 'TMPLEAVE', 'EXTLEAVE',
                                 'INACTIVE') ) then

    WF_CORE.Token('STATUS', CreateRole.status);
    WF_CORE.Raise('WF_INVALID_ROLE_STATUS');

  end if;

  --
  -- [Notification_Preference Validation]
  --
  -- Bug 2779747
  if ( CreateRole.notification_preference not in ('MAILHTML','MAILHTM2',
       'MAILATTH', 'SUMMARY', 'SUMHTML', 'QUERY', 'MAILTEXT','DISABLED') ) then
    WF_CORE.Token('NTF_PREF', CreateRole.notification_preference);
    WF_CORE.Raise('WF_INVALID_NTF_PREF');

  end if;

  --
  -- Resolve Territory and Language
  --
    if (language is null or territory is null) then
      begin
        select nls_territory, nls_language into nterr, nlang
        from   WF_LANGUAGES
        where  code = userenv('LANG');

      exception
        when NO_DATA_FOUND then
          wf_core.raise('WF_NO_LANG_TERR');
      end;

    else
      nlang := CreateRole.language;
      nterr := CreateRole.territory;

    end if;

  l_origSys := UPPER(CreateRole.orig_system);
  
  --
  -- Check the partition.
  --
   AssignPartition(l_origSys,
                   l_PartitionID, l_PartitionName);


  --                                                                        
  -- Insert WF_LOCAL_ROLES with USER_FLAG = 'N'                             
  --                                                                        

   insert into WF_LOCAL_ROLES
      (name,
       display_name,
       description,
       notification_preference,
       language,
       territory,
       email_address,
       fax,
       status,
       expiration_date,
       orig_system,
       orig_system_id,
       start_date,
       user_flag,
       partition_id,
       parent_orig_system,
       parent_orig_system_id,
       owner_tag,
       last_update_date,
       last_updated_by,
       creation_date,
       created_by,
       last_update_login)
     values
      (nvl(CreateRole.role_name, l_origSys || ':' ||
                 CreateRole.orig_system_id),
       nvl(CreateRole.role_display_name, (nvl(CreateRole.role_name,
                                              l_origSys || ':' ||
                                              CreateRole.orig_system_id))),
       CreateRole.role_description,                                         
       nvl(CreateRole.notification_preference, 'MAILHTML'), 
       nvl(CreateRole.language, nlang),                                
       nvl(CreateRole.territory, nterr),                               
       CreateRole.email_address,                                       
       CreateRole.fax,                                                 
       CreateRole.status,                                              
       CreateRole.expiration_date,                                     
       l_origSys,                                         
       CreateRole.orig_system_id,                                      
       CreateRole.start_date,
       'N',                                                            
       l_PartitionID,
       CreateRole.parent_orig_system,
       CreateRole.parent_orig_system_id,
       CreateRole.owner_tag,
       CreateRole.last_update_date,
       CreateRole.last_updated_by,
       CreateRole.creation_date,
       CreateRole.created_by,
       CreateRole.last_update_login );                       

   if ((WF_DIRECTORY.IsMLSEnabled(l_origSys) = TRUE ) AND (userenv('LANG') <> 'US')) then
    --If the orig_system is MLS enabled then sync the dat into
    --the _TL table aswell.
    insert into WF_LOCAL_ROLES_TL
       ( NAME,
         ORIG_SYSTEM,
         ORIG_SYSTEM_ID,
         PARTITION_ID,
         LANGUAGE,
         DISPLAY_NAME,
         DESCRIPTION,
         OWNER_TAG,
         LAST_UPDATE_DATE,
         LAST_UPDATED_BY,
         CREATION_DATE,
         CREATED_BY,
         LAST_UPDATE_LOGIN)
      values
       (nvl(CreateRole.role_name, l_origSys || ':' ||
                 CreateRole.orig_system_id),
          l_origSys,
          CreateRole.orig_system_id,
          l_partitionID,
          userenv('LANG'),
          nvl(CreateRole.role_display_name, (nvl(CreateRole.role_name,
                                                 l_origSys || ':' ||
                                                 CreateRole.orig_system_id))),
          CreateRole.role_description,
          CreateRole.owner_tag,
          CreateRole.last_update_date,
          CreateRole.last_updated_by,
          CreateRole.creation_date,
          CreateRole.created_by,
          CreateRole.last_update_login);
    end if;                                                                                 

exception
  when DUP_VAL_ON_INDEX then
    WF_CORE.Token('DISPNAME', CreateRole.role_display_name);
    WF_CORE.Token('ROLENAME', nvl(CreateRole.role_name,
                                        l_origSys || ':' ||
                                        CreateRole.orig_system_id));
    WF_CORE.Raise('WF_DUP_ROLE');
    
  when others then
    wf_core.context('Wf_Directory', 'CreateRole', CreateRole.role_Name,
                    l_origSys, CreateRole.orig_system_id);
    raise;
end CreateRole;


--
-- CreateAdHocRole
--   Create an ad hoc role given a specific name
-- IN
--   role_name          -
--   role_display_name  -
--   role_description   -
--   notification_preference -
--   role_users         - Comma or space delimited list
--   language           -
--   territory          -
--   email_address      -
--   fax                -
--   status             -
--   expiration_date   - Null means no expiration date
-- OUT
--
procedure CreateAdHocRole(role_name               in out NOCOPY varchar2,
                          role_display_name       in out NOCOPY varchar2,
                          language                in            varchar2,
                          territory               in            varchar2,
                          role_description        in            varchar2,
                          notification_preference in            varchar2,
                          role_users              in            varchar2,
                          email_address           in            varchar2,
                          fax                     in            varchar2,
                          status                  in            varchar2,
                          expiration_date         in            date,
                          parent_orig_system      in            varchar2,
                          parent_orig_system_id   in            number,
                          owner_tag               in            varchar2)
is
  l_users WF_DIRECTORY.UserTable;

begin
  --Convert the string to a proper user table.
  if (role_users is NOT NULL) then
    WF_DIRECTORY.string_to_userTable(role_users, l_users);
  end if;

  --Pass the call over to the superceding procedure CreateAdHocRole2
  WF_DIRECTORY.CreateAdHocRole2(role_name=>CreateAdhocRole.role_name,
               role_display_name=>CreateAdhocRole.role_display_name,
               language=>CreateAdhocRole.language,
               territory=>CreateAdhocRole.Territory,
               role_description=>CreateAdhocRole.role_description,
               notification_preference=>CreateAdhocRole.notification_preference,
               role_users=>l_users,
               email_address=>CreateAdhocRole.email_address,
               fax=>CreateAdhocRole.fax,
               status=>CreateAdhocRole.status,
               expiration_date=>CreateAdhocRole.expiration_date,
               parent_orig_system=>CreateAdhocRole.parent_orig_system,
               parent_orig_system_id=>CreateAdhocRole.parent_orig_system_id,
               owner_tag=>CreateAdhocRole.owner_tag);
exception
  when others then
    wf_core.context('Wf_Directory', 'CreateAdHocRole');
    raise;
end CreateAdHocRole;

--
-- CreateAdHocRole2
--   Create an ad hoc role given a specific name
-- IN
--   role_name               -
--   role_display_name       -
--   role_description        -
--   notification_preference -
--   role_users              - WF_DIRECTORY.UserTable
--   language                -
--   territory               -
--   email_address           -
--   fax                     -
--   status                  -
--   expiration_date         - Null means no expiration date
-- OUT
--
procedure CreateAdHocRole2(role_name               in out NOCOPY varchar2,
                           role_display_name       in out NOCOPY varchar2,
                           language                in            varchar2,
                           territory               in            varchar2,
                           role_description        in            varchar2,
                           notification_preference in            varchar2,
                           role_users              in WF_DIRECTORY.UserTable,
                           email_address           in            varchar2,
                           fax                     in            varchar2,
                           status                  in            varchar2,
                           expiration_date         in            date,
                           parent_orig_system      in            varchar2,
                           parent_orig_system_id   in            number,
                           owner_tag               in            varchar2)
is
  role_id pls_integer;
  name    varchar2(320);
  d1      pls_integer;

begin
  --
  -- Check if role name exists in wf_roles
  --
  if (role_name is not null or role_display_name is not null) then
    /* GK: The display name does not have to be unique

    select count(1) into d1
      from wf_roles
      where name = CreateAdHocRole.role_name
         or display_name = CreateAdHocRole.role_display_name;
    if (d1 > 0) then
      wf_core.token('ROLENAME', CreateAdHocRole.role_name);
      wf_core.token('DISPNAME', CreateAdHocRole.role_display_name);
      wf_core.raise('WF_DUP_ROLE');
    end if;

    */

    NULL;

  else
  --
  -- Create role name if not exist
  --
    begin
      select to_char(WF_ADHOC_ROLE_S.NEXTVAL)
      into role_id
      from SYS.DUAL;
    exception
      when others then
        raise;
    end;

    role_name := '~WF_ADHOC-' || role_id;
    role_display_name := role_name;
  end if;

    CreateRole( CreateAdHocRole2.role_name,
                CreateAdHocRole2.role_display_name,
                'WF_LOCAL_ROLES',
                0,
                CreateAdHocRole2.language,
                CreateAdHocRole2.territory,
                CreateAdHocRole2.role_description,
                CreateAdHocRole2.notification_preference,
                CreateAdHocRole2.email_address,
                CreateAdHocRole2.fax,
                CreateAdHocRole2.status,
                CreateAdHocRole2.expiration_date,
                CreateAdHocRole2.parent_orig_system,
                CreateAdHocRole2.parent_orig_system_id );


  --
  -- Add Role Users
  --
  if (role_users.COUNT > 0) then
    AddUsersToAdHocRole2(CreateAdHocRole2.role_name, 
                         CreateAdHocRole2.role_users);
  end if;


exception
  when others then
    wf_core.context('Wf_Directory', 'CreateAdHocRole2');
    raise;
end CreateAdHocRole2;

--
-- CreateUserRole (PRIVATE)
--   Create a user to role relationship.
-- IN
--   user_name -
--   role_name -
--   start_date -
--   expiration_date -
--   user_orig_system -
--   user_orig_system_id -
--   role_orig_system -
--   role_orig_system_id -
--   validateUserRole -
--   start_date  -
--   end_date -
--   created_by -
--   creation_date  -
--   last_updated_by  -
--   last_update_date -
--   last_update_login -
--   assignment_type -
--   assignment_type -
--   parent_orig_system -,
--   parent_orig_system_id -
--   owner_tag -
--   last_update_date -
--   last_updated_by -
--   creation_date -
--   created_by -
--   last_update_login  -
--
procedure CreateUserRole ( user_name             in  varchar2,
                           role_name             in  varchar2,
                           user_orig_system      in  varchar2,
                           user_orig_system_id   in  number,
                           role_orig_system      in  varchar2,
                           role_orig_system_id   in  number,
                           validateUserRole      in  boolean,
                           start_date            in  date,
                           end_date              in  date,
                           created_by            in  number,
                           creation_date         in  date,
                           last_updated_by       in  number,
                           last_update_date      in  date,
                           last_update_login     in  number,
                           assignment_type       in  varchar2,
                           parent_orig_system    in  varchar2,
                           parent_orig_system_id in  number,
                           owner_tag             in  varchar2 )

is
  l_count  PLS_INTEGER;
  l_partitionID number;
  l_partitionName varchar2(30);
  l_uorigSys VARCHAR2(30);
  l_uorigSysID NUMBER;
  l_rorigSys VARCHAR2(30);
  l_rorigSysID NUMBER;
  l_validateUserRole BOOLEAN;
  l_params  WF_PARAMETER_LIST_T;
  l_affectedRow rowid;

begin
  if ((user_orig_system is NULL) or (user_orig_system_id is NULL) or
      (role_orig_system is NULL) or (role_orig_system_id is NULL)) then
    --We need to validate by USER_NAME and ROLE_NAME to retreive the origSys
    --info.

    --Checking the user.
    begin
      SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID
      INTO   l_uorigSys, l_uorigSysID
      FROM   WF_LOCAL_ROLES
      WHERE  NAME = CreateUserRole.USER_NAME
      AND    ROWNUM < 2;

    exception
      when NO_DATA_FOUND then
        WF_CORE.Token('NAME', CreateUserRole.user_name);
        WF_CORE.Token('ORIG_SYSTEM', 'NULL');
        WF_CORE.Token('ORIG_SYSTEM_ID', 'NULL');
        WF_CORE.Raise('WF_NO_USER');
    end;

    --Checking the Role.
    begin
      SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID
      INTO   l_rorigSys, l_rorigSysID
      FROM   WF_LOCAL_ROLES
      WHERE  NAME = CreateUserRole.ROLE_NAME
      AND    ROWNUM < 2;

    exception
      when NO_DATA_FOUND then
        WF_CORE.Token('NAME', CreateUserRole.role_name);
        WF_CORE.Token('ORIG_SYSTEM', 'NULL');
        WF_CORE.Token('ORIG_SYSTEM_ID', 'NULL');
        WF_CORE.Raise('WF_NO_ROLE');
    end;
    --We have already validated the roles, don't need to do it again.
    l_validateUserRole := FALSE;

  else 
    l_validateUserRole := validateUserRole;
    l_uorigSys   := UPPER(CreateUserRole.user_orig_system);
    l_uorigSysID := CreateUserRole.user_orig_system_id;
    l_rorigSys   := UPPER(CreateUserRole.role_orig_system);
    l_rorigSysID := CreateUserRole.role_orig_system_id;
   
  end if;
  --
  -- Confirm that the User and Role actually exist.
  --
  -- Removed the requirement for the user_name to be an actual user per Kevin and 
  -- Mark for JTF team.
  if (l_validateUserRole) then
    SELECT count(*) into l_count
    FROM   WF_LOCAL_ROLES
    WHERE  ORIG_SYSTEM = l_uorigSys
    AND    ORIG_SYSTEM_ID = l_uorigSysID
    AND    NAME = CreateUserRole.user_name;

    if (l_count < 1) then
      WF_CORE.Token('NAME', CreateUserRole.user_name);
      WF_CORE.Token('ORIG_SYSTEM', l_uorigSys);
      WF_CORE.Token('ORIG_SYSTEM_ID', l_uorigSysID);
      WF_CORE.Raise('WF_NO_USER');

    end if;

    SELECT count(*) into l_count
    FROM   WF_LOCAL_ROLES
    WHERE  ORIG_SYSTEM = l_rorigSys
    AND    ORIG_SYSTEM_ID = l_rorigSysID
    AND    NAME = CreateUserRole.role_name;

    if (l_count < 1) then
      WF_CORE.Token('NAME', CreateUserRole.role_name);
      WF_CORE.Token('ORIG_SYSTEM', l_rorigSys);
      WF_CORE.Token('ORIG_SYSTEM_ID', l_rorigSysID);
      WF_CORE.Raise('WF_NO_ROLE');

    end if;

  end if;

    --
    -- Set the partition for the orig_system of the role_name
    --
      AssignPartition(l_rorigSys,
                      l_partitionID, l_partitionName); 
                      
    -- Insert
    begin
      insert into WF_LOCAL_USER_ROLES
                  ( USER_NAME,
                    ROLE_NAME,
                    USER_ORIG_SYSTEM,
                    USER_ORIG_SYSTEM_ID,
                    ROLE_ORIG_SYSTEM,
                    ROLE_ORIG_SYSTEM_ID,
                    START_DATE,
                    EXPIRATION_DATE,
                    PARTITION_ID,
                    PARENT_ORIG_SYSTEM,
                    PARENT_ORIG_SYSTEM_ID,
                    ASSIGNMENT_TYPE,
                    OWNER_TAG,
                    LAST_UPDATE_DATE,
                    LAST_UPDATED_BY,
                    CREATION_DATE,
                    CREATED_BY,
                    LAST_UPDATE_LOGIN
                  )
               values 
                  (
                    CreateUserRole.user_name,
                    CreateUserRole.role_name,
                    l_uorigSys,
                    l_uorigSysID,
                    l_rorigSys,
                    l_rorigSysID,
                    trunc(CreateUserRole.start_date),
                    trunc(CreateUserRole.end_date),
                    l_partitionID,
                    nvl(CreateUserRole.parent_orig_system,
                        CreateUserRole.role_orig_system),
                    nvl(CreateUserRole.parent_orig_system_id,
                        CreateUserRole.role_orig_system_id),
                    CreateUserRole.assignment_type,
                    CreateUserRole.owner_tag,
                     nvl(CreateUserRole.last_update_date,SYSDATE),
                    CreateUserRole.last_updated_by,
                    nvl(CreateUserRole.creation_date,SYSDATE), 
                    CreateUserRole.created_by,
                    CreateUserRole.last_update_login
                   ) returning rowid into l_affectedRow;

    --We were able to insert the record, so we will raise the created event
    --Build parameter list.
    WF_EVENT.AddParameterToList('ROWID', ROWIDTOCHAR(l_affectedRow), l_params);
    WF_EVENT.AddParameterToList('USER_NAME', CreateUserRole.user_name, l_params);
    WF_EVENT.AddParameterToList('ROLE_NAME', CreateUserRole.role_name, l_params);
    WF_EVENT.AddParameterToList('USER_ORIG_SYSTEM', l_uorigSys, l_params);
    WF_EVENT.AddParameterToList('USER_ORIG_SYSTEM_ID', l_uorigSysID,  l_params);
    WF_EVENT.AddParameterToList('ROLE_ORIG_SYSTEM', l_rorigSys, l_params);
    WF_EVENT.AddParameterToList('ROLE_ORIG_SYSTEM_ID', l_rorigSysID, l_params);
    WF_EVENT.AddParameterToList('START_DATE', 
                                to_char(trunc(CreateUserRole.start_date),
                                        WF_CORE.Canonical_Date_Mask), 
                                l_params);
    WF_EVENT.AddParameterToList('END_DATE', 
                                to_char(trunc(CreateUserRole.end_date),
                                        WF_CORE.Canonical_Date_Mask),
                                l_params);

    WF_EVENT.AddParameterToList('CREATED_BY',
                                to_char(CreateUserRole.created_by), l_params);
   
  WF_EVENT.AddParameterToList('CREATION_DATE', 
                               to_char(nvl(CreateUserRole.creation_date,SYSDATE), 
                                        WF_CORE.canonical_date_mask), l_params);

    WF_EVENT.AddParameterToList('LAST_UPDATED_BY', 
                                to_char(CreateUserRole.last_updated_by),
                                l_params);
     WF_EVENT.AddParameterToList('LAST_UPDATE_DATE', 
                                to_char(nvl(CreateUserRole.last_update_date, SYSDATE),
                                        WF_CORE.canonical_date_mask), l_params);
    WF_EVENT.AddParameterToList('LAST_UPDATE_LOGIN', 
                                to_char(CreateUserRole.last_update_login),
                                l_params);
    WF_EVENT.AddParameterToList('ASSIGNMENT_TYPE', 
                                CreateUserRole.assignment_type, l_params);
    WF_EVENT.AddParameterToList('PARENT_ORIG_SYSTEM', 
                                CreateUserRole.parent_orig_system, l_params);
    WF_EVENT.AddParameterToList('PARENT_ORIG_SYSTEM_ID', 
                                to_char(CreateUserRole.parent_orig_system_id),
                                l_params);
    WF_EVENT.AddParameterToList('PARTITION_ID', to_char(l_partitionID), 
                                l_params);

    WF_EVENT.Raise(p_event_name=>'oracle.apps.fnd.wf.ds.userRole.created',
                   p_event_key=>user_name||':'||role_name,
                   p_parameters=>l_params);

    exception
      when DUP_VAL_ON_INDEX then
        WF_CORE.Raise('WF_DUP_USER_ROLE');
          
      when OTHERS then 
        raise;

    end;



exception
  when others then
    wf_core.context('Wf_Directory', 'CreateUserRole',
        user_name, role_name, l_uorigSys, 
        to_char(nvl(user_orig_system_id, l_uorigSysID)),
        l_rorigSys, to_char(nvl(role_orig_system_id, l_rorigSysID)));

    raise;

end CreateUserRole;


--
-- SetUserRoleAttr (PRIVATE)
--   Update a user to role relationship.
-- IN
--   user_name -
--   role_name -
--   start_date -
--   expiration_date -
--   user_orig_system -
--   user_orig_system_id -
--   role_orig_system -
--   role_orig_system_id -
--   OverWrite -
--   last_updated_by -
--   last_update_date -
--   last_update_login -
--   assignment_type  -
--   parent_orig_system - 
--   parent_orig_system_id 
--   owner_tag
--   last_update_date -
--   last_updated_by -
--   creation_date -
--   created_by -
--   last_update_login  -
--
procedure SetUserRoleAttr ( user_name             in varchar2,
                            role_name             in varchar2,
                            start_date            in date,
                            end_date              in date,
                            user_orig_system      in varchar2,
                            user_orig_system_id   in number,
                            role_orig_system      in varchar2,
                            role_orig_system_id   in number,
                            OverWrite             in boolean,
                            last_updated_by       in number,
                            last_update_date      in date,
                            last_update_login     in number,
                            assignment_type       in varchar2,
                            parent_orig_system    in varchar2,
                            parent_orig_system_id in number,
                            owner_tag             in varchar2) is

  l_uorigSys    VARCHAR2(30) := UPPER(user_orig_system);
  l_uorigSysID  NUMBER       := UPPER(user_orig_system_id);
  l_rorigSys    VARCHAR2(30) := UPPER(role_orig_system);
  l_rorigSysID  NUMBER       := UPPER(role_orig_system_id);
  l_porigSys    VARCHAR2(30) := UPPER(nvl(parent_orig_system, 
                                          role_orig_system));
  l_porigSysID  NUMBER       := UPPER(nvl(parent_orig_system_id,
                                          role_orig_system_id));
  l_params      WF_PARAMETER_LIST_T;
  l_affectedRow rowid;

begin
  --<rwunderl:2823630> Lookup origSys info if not provided.
  if ((SetUserRoleAttr.user_orig_system is NULL) or
      (SetUserRoleAttr.user_orig_system_id is NULL)) then
    --Checking the user.
    begin
      SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID
      INTO   l_uorigSys, l_uorigSysID
      FROM   WF_LOCAL_ROLES
      WHERE  NAME = SetUserRoleAttr.user_name
      and    rownum < 2;

    exception
      when NO_DATA_FOUND then
        WF_CORE.Token('NAME', SetUserRoleAttr.user_name);
        WF_CORE.Token('ORIG_SYSTEM', 'NULL');
        WF_CORE.Token('ORIG_SYSTEM_ID', 'NULL');
        WF_CORE.Raise('WF_NO_USER');
    end;
  end if;


  if ((SetUserRoleAttr.role_orig_system is NULL) or
      (SetUserRoleAttr.role_orig_system_id is NULL)) then
    --Checking the role.
    begin
      SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID
      INTO   l_rorigSys, l_rorigSysID
      FROM   WF_LOCAL_ROLES
      WHERE  NAME = SetUserRoleAttr.role_name
      AND    rownum < 2 ;

    exception
      when NO_DATA_FOUND then
        WF_CORE.Token('NAME', SetUserRoleAttr.role_name);
        WF_CORE.Token('ORIG_SYSTEM', 'NULL');
        WF_CORE.Token('ORIG_SYSTEM_ID', 'NULL');
        WF_CORE.Raise('WF_NO_ROLE');
    end;
  end if;
  
  if (OverWrite) then
    update WF_LOCAL_USER_ROLES
    set EXPIRATION_DATE       = SetUserRoleAttr.end_date,
        START_DATE            = SetUserRoleAttr.start_date,
        LAST_UPDATED_BY       = SetUserRoleAttr.last_updated_by,
        LAST_UPDATE_DATE      = nvl(SetUserRoleAttr.last_update_date,SYSDATE),
        LAST_UPDATE_LOGIN     = SetUserRoleAttr.last_update_login,
        PARENT_ORIG_SYSTEM    = l_porigSys,
        PARENT_ORIG_SYSTEM_ID = l_porigSysID
    where  USER_NAME          = SetUserRoleAttr.user_name
    and    ROLE_NAME          = SetUserRoleAttr.role_name
    and    USER_ORIG_SYSTEM    = l_uorigSys
    and    USER_ORIG_SYSTEM_ID = l_uorigSysID
    and    ROLE_ORIG_SYSTEM    = l_rorigSys
    and    ROLE_ORIG_SYSTEM_ID = l_rorigSysID 
    returning rowid into l_affectedRow;

  else            
    update WF_LOCAL_USER_ROLES 
          set    EXPIRATION_DATE = nvl(SetUserRoleAttr.end_date,
                                       EXPIRATION_DATE),
                 START_DATE = nvl(SetUserRoleAttr.start_date, START_DATE),
                 PARENT_ORIG_SYSTEM = nvl(SetUserRoleAttr.parent_orig_system,
                                          l_porigSys),
                 PARENT_ORIG_SYSTEM_ID = nvl(
                                          SetUserRoleAttr.parent_orig_system_id,
                                           l_porigSysID),
                 LAST_UPDATED_BY     = SetUserRoleAttr.last_updated_by,
                 LAST_UPDATE_DATE    = nvl(SetUserRoleAttr.last_update_date,SYSDATE),
                 LAST_UPDATE_LOGIN   = SetUserRoleAttr.last_update_login
          where  USER_NAME        = SetUserRoleAttr.user_name
          and    ROLE_NAME        = SetUserRoleAttr.role_name
          and    USER_ORIG_SYSTEM    = l_uorigSys
          and    USER_ORIG_SYSTEM_ID = l_uorigSysID
          and    ROLE_ORIG_SYSTEM    = l_rorigSys
          and    ROLE_ORIG_SYSTEM_ID = l_rorigSysID
          returning rowid into l_affectedRow;

  end if;
      
  if (sql%ROWCOUNT = 0) then
    WF_CORE.Raise('WF_INVAL_USER_ROLE');
    
  end if;
    --We were able to update an existing record, so we will raise the 
    --updated event

    --Build parameter list.
    WF_EVENT.AddParameterToList('ROWID', ROWIDTOCHAR(l_affectedRow), l_params);
    WF_EVENT.AddParameterToList('USER_NAME', SetUserRoleAttr.user_name, 
                                l_params);
    WF_EVENT.AddParameterToList('ROLE_NAME', SetUserRoleAttr.role_name, 
                                l_params);
    WF_EVENT.AddParameterToList('USER_ORIG_SYSTEM', l_uorigSys, l_params);
    WF_EVENT.AddParameterToList('USER_ORIG_SYSTEM_ID', to_char(l_uorigSysID),
                                 l_params);
    WF_EVENT.AddParameterToList('ROLE_ORIG_SYSTEM', l_rorigSys, l_params);
    WF_EVENT.AddParameterToList('ROLE_ORIG_SYSTEM_ID', to_char(l_porigSysID),
                                 l_params);
    WF_EVENT.AddParameterToList('PARENT_ORIG_SYSTEM', l_porigSys, l_params);
    WF_EVENT.AddParameterToList('PARENT_ORIG_SYSTEM_ID', to_char(l_rorigSysID),
                                l_params);
    WF_EVENT.AddParameterToList('START_DATE', 
                                to_char(trunc(SetUserRoleAttr.start_date),
                                        WF_CORE.canonical_date_mask), 
                                l_params);
    WF_EVENT.AddParameterToList('END_DATE', 
                                to_char(trunc(SetUserRoleAttr.end_date),
                                        WF_CORE.canonical_date_mask),
                                l_params);

    WF_EVENT.AddParameterToList('LAST_UPDATED_BY', 
                                to_char(SetUserRoleAttr.last_updated_by),
                                l_params);

 WF_EVENT.AddParameterToList('LAST_UPDATE_DATE', 
                                to_char(nvl(SetUserRoleAttr.last_update_date,SYSDATE),
                                        WF_CORE.canonical_date_mask), l_params);
    WF_EVENT.AddParameterToList('LAST_UPDATE_LOGIN', 
                                to_char(SetUserRoleAttr.last_update_login),
                                l_params);
    WF_EVENT.AddParameterToList('ASSIGNMENT_TYPE', 
                                SetUserRoleAttr.assignment_type, l_params);

    WF_EVENT.Raise(p_event_name=>'oracle.apps.fnd.wf.ds.userRole.updated',
                   p_event_key=>user_name||':'||role_name,
                   p_parameters=>l_params);

  
end;
          
          
--
-- RemoveUserRole (PRIVATE)
--   Remove a user from a role.
-- IN
--   user_name -
--   role_name -
--   user_orig_system -
--   user_orig_system_id -
--   role_orig_system -
--   role_orig_system_id -
--
procedure RemoveUserRole(user_name           in varchar2,
                         role_name           in varchar2,
                         user_orig_system    in varchar2,
                         user_orig_system_id in number,
                         role_orig_system    in varchar2,
                         role_orig_system_id in number)
is
begin

  if (user_orig_system is null or user_orig_system_id is null) then
    -- Expire user
    update WF_LOCAL_USER_ROLES
    set    EXPIRATION_DATE     =  sysdate
    where  USER_NAME           =  RemoveUserRole.user_name          
    and    ROLE_NAME           =  RemoveUserRole.role_name          
    and    ROLE_ORIG_SYSTEM    =  RemoveUserRole.role_orig_system   
    and    ROLE_ORIG_SYSTEM_ID =  RemoveUserRole.role_orig_system_id;
  else
    -- Expire user with orig system and orig system id
    update WF_LOCAL_USER_ROLES
    set    EXPIRATION_DATE     =  sysdate
    where  USER_NAME           =  RemoveUserRole.user_name          
    and    ROLE_NAME           =  RemoveUserRole.role_name          
    and    USER_ORIG_SYSTEM    =  RemoveUserRole.user_orig_system   
    and    USER_ORIG_SYSTEM_ID =  RemoveUserRole.user_orig_system_id
    and    ROLE_ORIG_SYSTEM    =  RemoveUserRole.role_orig_system   
    and    ROLE_ORIG_SYSTEM_ID =  RemoveUserRole.role_orig_system_id;
  end if;

  -- DL: did not trap the WF_INVALID_USER error here.
  -- It should be fine if someone want to remove a user again from the
  -- same role.  Plus it should be a user/role not exist, not an invalid
  -- user.

exception
  when others then
    wf_core.context('Wf_Directory', 'RemoveUserRole',
        user_name, role_name, user_orig_system, to_char(user_orig_system_id),
        role_orig_system, to_char(role_orig_system_id));

    raise;
end RemoveUserRole;

--
-- AddUsersToAdHocRole (Deprecated)
--   Use AddUsersToAdHocRole2
-- IN
--   role_name     - AdHoc role name
--   role_users    - Space or comma delimited list of apps-based users
--                      or adhoc users
-- OUT
--
procedure AddUsersToAdHocRole(role_name         in varchar2,
                              role_users        in  varchar2)
is
  l_users WF_DIRECTORY.UserTable;

begin

  if (role_users is NOT NULL) then
    String_To_UserTable (p_UserList=>AddUsersToAdHocRole.role_users, 
                         p_UserTable=>l_users);
                     
    AddUsersToAdHocRole2(role_name=>AddUsersToAdHocRole.role_name,
                         role_users=>l_users);
  end if;

exception
  when others then
    wf_core.context('Wf_Directory', 'AddUsersToAdHocRole',
        role_name, '"'||role_users||'"');
    raise;
end AddUsersToAdHocRole;

--
-- AddUsersToAdHocRole2
--   Add users to an existing ad hoc role
-- IN
--   role_name     - AdHoc role name
--   role_users    - Space or comma delimited list of apps-based users
--                      or adhoc users
-- OUT
--
procedure AddUsersToAdHocRole2(role_name         in varchar2,
                               role_users        in WF_DIRECTORY.UserTable) is

  d1               pls_integer;
  colon            pls_integer;
  userIND          number;
  l_orig_system    varchar2(30) := NULL;
  l_orig_system_id number       := NULL;

begin
  -- Validate Role
  if (wfa_sec.DS_Count_Local_Role(AddUsersToAdHocRole2.role_name) <= 0) then
    wf_core.token('ROLENAME', AddUsersToAdHocRole2.role_name);
    wf_core.raise('WF_INVALID_ROLE');
  end if;

  if (role_users.COUNT > 0) then
    for userIND in role_users.FIRST..role_users.LAST loop
      if (role_users(userIND) is NOT NULL) then
        -- Validation
        -- 1379875: (Performance) added support for orig_system, orig_system_id
        -- composite name.
        -- Changed Validation and duplicate checking to limit selects against
        -- wf_users.
        -- Used a sub-block to use exception handling instead of single count 
        -- into.


         begin
           colon := instr(role_users(userIND), ':');
           if (colon = 0) then
             --Bug 2465881
              --To eliminate error: Exact fetch returning more than
              --requested number of rows.
            SELECT  ORIG_SYSTEM, ORIG_SYSTEM_ID
            INTO    l_orig_system, l_orig_system_id
            FROM    WF_USERS
            WHERE   NAME = role_users(userIND)
            AND     partition_id <> 9
            AND     rownum < 2;        
          else
            -- Bug 2465881
            SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID
            INTO   l_orig_system, l_orig_system_id
            FROM   WF_USERS
            WHERE  ORIG_SYSTEM = substr(role_users(userIND), 1, colon-1)
            AND    ORIG_SYSTEM_ID = substr(role_users(userIND), colon+1)
            AND    rownum < 2;
          end if;
        exception
          when NO_DATA_FOUND then
            wf_core.token('USERNAME', role_users(userIND));
            wf_core.raise('WF_INVALID_USER');

          when others then
            wf_core.context('Wf_Directory', 'AddUsersToAdHocRole2', role_name);
            raise;
        end;

        -- Check Duplicate
        -- for local table, check user name and role name are sufficient
        -- there will not be index on orig system and orig system id
        -- orig systems and orig system ids are identical among the users
        -- from WF_LOCAL_ROLES.
        --
      
        select count(1) into d1
        from WF_LOCAL_USER_ROLES
        where USER_NAME = role_users(userIND)
        and ROLE_NAME = AddUsersToAdHocRole2.role_name
        and ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
        and ROLE_ORIG_SYSTEM_ID = 0;

        if (d1 > 0) then
          wf_core.token('USERNAME', role_users(userIND));
          wf_core.token('DISPNAME', '');
          wf_core.raise('WF_DUP_USER');
        end if;

        CreateUserRole(user_name=>role_users(userIND),
                       role_name=>AddUsersToAdHocRole2.role_name,
                       user_orig_system=>l_orig_system,
                       user_orig_system_id=>l_orig_system_id,
                       role_orig_system=>'WF_LOCAL_ROLES',
                       role_orig_system_id=>0,
                       start_date=>sysdate,
                       end_date=>to_date(NULL),
                       validateUserRole=>FALSE,
                       created_by=>WFA_SEC.user_id,
                       creation_date=>sysdate,
                       last_updated_by=>WFA_SEC.user_id,
                       last_update_date=>sysdate,
                       last_update_login=>WFA_SEC.login_id);
      end if;
    end loop;
  end if;

exception
  when others then
    wf_core.context('Wf_Directory', 'AddUsersToAdHocRole2',
        role_name);
    raise;
end AddUsersToAdHocRole2;


--
-- SetUserAttr (PRIVATE)
--   Update additional attributes for users
-- IN
--   user_name        - user name
--   orig_system      -
--   orig_system_id   -
--   display_name  - 
--   notification_preference -
--   language      -
--   territory     -
--   email_address -
--   fax           -
--   expiration_date  - New expiration date
--   status           - status could be 'ACTIVE' or 'INACTIVE'
--   start_date -
--   OverWrite -
--   parent_orig_system -
--   parent_orig_system_id -
--   owner_tag -
--   last_updated_by -
--   last_update_date -
--   last_update_login -
-- OUT
--
procedure SetUserAttr(user_name               in  varchar2,
                      orig_system             in  varchar2,
                      orig_system_id          in  number,
                      display_name            in  varchar2,
                      description             in  varchar2,
                      notification_preference in  varchar2,
                      language                in  varchar2,
                      territory               in  varchar2,
                      email_address           in  varchar2,
                      fax                     in  varchar2,
                      expiration_date         in  date,
                      status                  in  varchar2,
                      start_date              in  date,
                      OverWrite               in  boolean,
                      Parent_Orig_System      in  varchar2,
                      Parent_orig_system_id   in  number,
                      owner_tag               in  varchar2,
                      last_updated_by         in  number,
                      last_update_date        in  date,
                      last_update_login       in  number)
is
  CURSOR user_roles is
    SELECT ROLE_NAME, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID, START_DATE,
           EXPIRATION_DATE
    FROM   WF_LOCAL_USER_ROLES
    WHERE  USER_ORIG_SYSTEM     = SetUserAttr.orig_system
    AND    USER_ORIG_SYSTEM_ID  = SetUserAttr.orig_system_id
    AND    USER_NAME            = SetUserAttr.user_name
    AND    USER_NAME            <>  ROLE_NAME
    AND    USER_ORIG_SYSTEM     <> ROLE_ORIG_SYSTEM
    AND    USER_ORIG_SYSTEM_ID  <> ROLE_ORIG_SYSTEM_ID;

  l_expiration DATE;
  l_params WF_PARAMETER_LIST_T;
  l_oldStartDate DATE;
  l_oldEndDate   DATE;
  
begin
  --We first need to capture the current start/end date in case they are 
  --changed.
  begin
    SELECT START_DATE, EXPIRATION_DATE
    INTO   l_oldStartDate, l_oldEndDate
    FROM   WF_LOCAL_ROLES
    WHERE  NAME = SetUserAttr.user_name
    AND    ORIG_SYSTEM = SetUserAttr.orig_system
    AND    ORIG_SYSTEM_ID = SetUserAttr.orig_system_id;
  exception
    when NO_DATA_FOUND then
      wf_core.token('USERNAME', user_name);
      wf_core.raise('WF_INVALID_USER');
  end;

    --
    -- Update WF_LOCAL_ROLES where user_flag = 'Y'
    --
  if (OverWrite) then

   if ((userenv('LANG') = 'US') OR 
       (isMLSEnabled(SetUserAttr.orig_system) = FALSE)) then
    update WF_LOCAL_ROLES
       set NOTIFICATION_PREFERENCE = nvl(SetUserAttr.notification_preference,
                                         NOTIFICATION_PREFERENCE),
           LANGUAGE                = nvl(SetUserAttr.language, LANGUAGE),
           TERRITORY               = nvl(SetUserAttr.territory, TERRITORY),
           EMAIL_ADDRESS           = SetUserAttr.email_address,
           FAX                     = SetUserAttr.fax,
           DISPLAY_NAME            = nvl(SetUserAttr.display_name,
                                         DISPLAY_NAME),
           DESCRIPTION             = SetUserAttr.description,
           EXPIRATION_DATE         = SetUserAttr.expiration_date,
           STATUS                  = nvl(SetUserAttr.status, STATUS),
           START_DATE              = SetUserAttr.start_date,
           PARENT_ORIG_SYSTEM      = SetUserAttr.parent_orig_system,
           PARENT_ORIG_SYSTEM_ID   = SetUserAttr.parent_orig_system_id,
           OWNER_TAG               = SetUserAttr.owner_tag,
           LAST_UPDATED_BY         = SetUserAttr.last_updated_by,
           LAST_UPDATE_DATE        = SetUserAttr.last_update_date,
           LAST_UPDATE_LOGIN       = SetUserAttr.last_update_login
     where NAME           = user_name
       and ORIG_SYSTEM    = SetUserAttr.orig_system
       and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
       and USER_FLAG      = 'Y';

   else

    update WF_LOCAL_ROLES
       set NOTIFICATION_PREFERENCE = nvl(SetUserAttr.notification_preference,
                                         NOTIFICATION_PREFERENCE),
           LANGUAGE                = nvl(SetUserAttr.language, LANGUAGE),
           TERRITORY               = nvl(SetUserAttr.territory, TERRITORY),
           EMAIL_ADDRESS           = SetUserAttr.email_address,
           FAX                     = SetUserAttr.fax,
           EXPIRATION_DATE         = SetUserAttr.expiration_date,
           STATUS                  = nvl(SetUserAttr.status, STATUS),
           START_DATE              = SetUserAttr.start_date,
           PARENT_ORIG_SYSTEM      = SetUserAttr.parent_orig_system,
           PARENT_ORIG_SYSTEM_ID   = SetUserAttr.parent_orig_system_id,
           LAST_UPDATED_BY         = nvl(SetUserAttr.last_updated_by,
                                         WFA_SEC.user_id),
           LAST_UPDATE_DATE        = nvl(SetUserAttr.last_update_date, 
                                         trunc(sysdate)),
           LAST_UPDATE_LOGIN       = nvl(SetUserAttr.last_update_login,
                                         WFA_SEC.login_id)
     where NAME           = user_name
       and ORIG_SYSTEM    = SetUserAttr.orig_system
       and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
       and USER_FLAG      = 'Y';


   --Update the _TL table
   update WF_LOCAL_ROLES_TL
      set DISPLAY_NAME   = nvl(SetUserAttr.display_name, DISPLAY_NAME),
          DESCRIPTION    = SetUserAttr.description,
          LAST_UPDATED_BY         = nvl(SetUserAttr.last_updated_by,
                                        WFA_SEC.user_id),
          LAST_UPDATE_DATE        = nvl(SetUserAttr.last_update_date, 
                                        trunc(sysdate)),
          LAST_UPDATE_LOGIN       = nvl(SetUserAttr.last_update_login,
                                        WFA_SEC.login_id)
   where NAME           = SetUserAttr.user_name
      and ORIG_SYSTEM    = SetUserAttr.orig_system
      and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
      and LANGUAGE       = userenv('LANG');

   if (sql%rowcount = 0) then
     --No record exist for this lang in _TL table 
     --so insert a record 
     insert into  WF_LOCAL_ROLES_TL (NAME,
                                     DISPLAY_NAME,
                                     DESCRIPTION,
                                     ORIG_SYSTEM,
                                     ORIG_SYSTEM_ID,
                                     PARTITION_ID,
                                     LANGUAGE,
                                     OWNER_TAG,
                                     CREATED_BY,
                                     CREATION_DATE,
                                     LAST_UPDATED_BY,
                                     LAST_UPDATE_DATE,
                                     LAST_UPDATE_LOGIN )
     (select name, 
             nvl(SetUserAttr.display_name,DISPLAY_NAME) ,
             SetUserAttr.description, 
             ORIG_SYSTEM, 
             ORIG_SYSTEM_ID ,
             PARTITION_ID,
             userenv('LANG'), 
             nvl(SetUserAttr.owner_tag, OWNER_TAG),
             nvl(SetUserAttr.last_updated_by, WFA_SEC.user_id),
             trunc(sysdate),
             nvl(SetUserAttr.last_updated_by, WFA_SEC.user_id),
             nvl(SetUserAttr.last_update_date, trunc(sysdate)),
             nvl(SetUserAttr.last_update_login, WFA_SEC.login_id)
      from WF_LOCAL_ROLES      
      where NAME           = user_name
      and ORIG_SYSTEM      = SetUserAttr.orig_system
      and ORIG_SYSTEM_ID   = SetUserAttr.orig_system_id
      and USER_FLAG        = 'Y');

      --Now update the table for relevant data 
        
   end if;
   end if;
  else
   if ((userenv('LANG') = 'US') OR 
       (isMLSEnabled(SetUserAttr.orig_system) = FALSE)) then
    update WF_LOCAL_ROLES
       set NOTIFICATION_PREFERENCE = nvl(SetUserAttr.notification_preference, 
                                         NOTIFICATION_PREFERENCE),
           LANGUAGE        = nvl(SetUserAttr.language, LANGUAGE),
           TERRITORY       = nvl(SetUserAttr.territory, TERRITORY),
           EMAIL_ADDRESS   = nvl(SetUserAttr.email_address, EMAIL_ADDRESS),
           FAX             = nvl(SetUserAttr.fax, FAX),
           DISPLAY_NAME    = nvl(SetUserAttr.display_name, DISPLAY_NAME),
           DESCRIPTION     = nvl(SetUserAttr.description, DESCRIPTION),
           EXPIRATION_DATE = nvl(SetUserAttr.expiration_date, EXPIRATION_DATE),
           STATUS          = nvl(SetUserAttr.status, STATUS),
           START_DATE      = nvl(SetUserAttr.start_date, START_DATE),
           PARENT_ORIG_SYSTEM = nvl(SetUserAttr.parent_orig_system, 
                                    PARENT_ORIG_SYSTEM),
           PARENT_ORIG_SYSTEM_ID = nvl(SetUserAttr.parent_orig_system_id,
                                       PARENT_ORIG_SYSTEM_ID),
           OWNER_TAG = nvl(SetUserAttr.owner_tag, OWNER_TAG),
           LAST_UPDATED_BY = nvl(SetUserAttr.last_updated_by, 
                                 WFA_SEC.user_id),
           LAST_UPDATE_DATE = nvl(SetUserAttr.last_update_date, trunc(sysdate)),
           LAST_UPDATE_LOGIN = nvl(SetUserAttr.last_update_login, 
                                   WFA_SEC.login_id)
     where NAME = user_name
       and ORIG_SYSTEM = SetUserAttr.orig_system
       and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
       and USER_FLAG = 'Y';
   else
    update WF_LOCAL_ROLES
       set NOTIFICATION_PREFERENCE = nvl(SetUserAttr.notification_preference,
                                         NOTIFICATION_PREFERENCE),
           LANGUAGE        = nvl(SetUserAttr.language, LANGUAGE),
           TERRITORY       = nvl(SetUserAttr.territory, TERRITORY),
           EMAIL_ADDRESS   = nvl(SetUserAttr.email_address, EMAIL_ADDRESS),
           FAX             = nvl(SetUserAttr.fax, FAX),
           EXPIRATION_DATE = nvl(SetUserAttr.expiration_date, EXPIRATION_DATE),
           STATUS          = nvl(SetUserAttr.status, STATUS),
           START_DATE      = nvl(SetUserAttr.start_date, START_DATE),
           PARENT_ORIG_SYSTEM = nvl(SetUserAttr.parent_orig_system, 
                                    PARENT_ORIG_SYSTEM),
           PARENT_ORIG_SYSTEM_ID = nvl(SetUserAttr.parent_orig_system_id,
                                       PARENT_ORIG_SYSTEM_ID),
           OWNER_TAG = nvl(SetUserAttr.owner_tag, OWNER_TAG),
           LAST_UPDATED_BY = nvl(SetUserAttr.last_updated_by, 
                                 WFA_SEC.user_id),
           LAST_UPDATE_DATE = nvl(SetUserAttr.last_update_date, trunc(sysdate)),
           LAST_UPDATE_LOGIN = nvl(SetUserAttr.last_update_login, 
                                   WFA_SEC.login_id)
     where NAME = user_name
       and ORIG_SYSTEM = SetUserAttr.orig_system
       and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
       and USER_FLAG = 'Y';

   --then lets update the _TL table
   update WF_LOCAL_ROLES_TL
      set DISPLAY_NAME   = nvl(SetUserAttr.display_name, DISPLAY_NAME),
          DESCRIPTION    = SetUserAttr.description,
          LAST_UPDATED_BY         = nvl(SetUserAttr.last_updated_by,
                                        WFA_SEC.user_id),
          LAST_UPDATE_DATE        = nvl(SetUserAttr.last_update_date, 
                                        trunc(sysdate)),
          LAST_UPDATE_LOGIN       = nvl(SetUserAttr.last_update_login,
                                        WFA_SEC.login_id)
    where NAME           = SetUserAttr.user_name
      and ORIG_SYSTEM    = SetUserAttr.orig_system
      and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
      and LANGUAGE       = userenv('LANG');

     if (sql%rowcount = 0) then
       --No record exist for this lang in _TL table
       --so insert a record
       --The issue is that if the passed values or param 
       --are null we would be inserting 'US' language
       --display name and description

       insert into  WF_LOCAL_ROLES_TL (NAME,
                                       DISPLAY_NAME,
                                       DESCRIPTION,
                                       ORIG_SYSTEM,
                                       ORIG_SYSTEM_ID,
                                       PARTITION_ID,
                                       LANGUAGE,
                                       OWNER_TAG,
                                       CREATED_BY,
                                       CREATION_DATE,
                                       LAST_UPDATED_BY,
                                       LAST_UPDATE_DATE,
                                       LAST_UPDATE_LOGIN )
       (select name , 
               nvl(SetUserAttr.display_name,DISPLAY_NAME) ,
               SetUserAttr.description,
               ORIG_SYSTEM, 
               ORIG_SYSTEM_ID,
               PARTITION_ID,
               userenv('LANG'), 
               nvl(SetUserAttr.owner_tag, OWNER_TAG),
               nvl(SetUserAttr.last_updated_by, WFA_SEC.user_id),
               trunc(sysdate),
               nvl(SetUserAttr.last_updated_by, WFA_SEC.user_id),
               nvl(SetUserAttr.last_update_date, trunc(sysdate)),
               nvl(SetUserAttr.last_update_login, WFA_SEC.login_id)
        from WF_LOCAL_ROLES
        where NAME           = SetUserAttr.user_name
        and ORIG_SYSTEM      = SetUserAttr.orig_system
        and ORIG_SYSTEM_ID   = SetUserAttr.orig_system_id
        and USER_FLAG        = 'Y');

        --Now update the table for relevant data

     end if; 

   end if;
  end if;
  if (sql%rowcount = 0) then
      wf_core.token('USERNAME', user_name);
      wf_core.raise('WF_INVALID_USER');
  end if;

    --We were able to update the record, so we will raise the updated event
    --Build parameter list.
    WF_EVENT.AddParameterToList('USER_NAME', SetUserAttr.user_name, l_params);
    WF_EVENT.AddParameterToList('ORIG_SYSTEM', SetUserAttr.orig_system,
                                l_params);
    WF_EVENT.AddParameterToList('ORIG_SYSTEM_ID',
                                to_char(SetUserAttr.orig_system_id), l_params);
    WF_EVENT.AddParameterToList('DISPLAY_NAME', SetUserAttr.display_name,
                                l_params);
    WF_EVENT.AddParameterToList('DESCRIPTION', SetUserAttr.description,
                                l_params);
    WF_EVENT.AddParameterToList('NOTIFICATION_PREFERENCE', 
                                SetUserAttr.notification_preference, l_params);
    WF_EVENT.AddParameterToList('LANGUAGE', SetUserAttr.language,  l_params);
    WF_EVENT.AddParameterToList('TERRITORY', SetUserAttr.territory, l_params);
    WF_EVENT.AddParameterToList('EMAIL_ADDRESS', SetUserAttr.email_address,
                                l_params);
    WF_EVENT.AddParameterToList('FAX', SetUserAttr.fax, l_params);
    WF_EVENT.AddParameterToList('EXPIRATION_DATE', 
                                to_char(trunc(SetUserAttr.expiration_date),
                                        WF_CORE.Canonical_Date_Mask),
                                l_params);
    WF_EVENT.AddParameterToList('STATUS', SetUserAttr.status, l_params);
    WF_EVENT.AddParameterToList('START_DATE', 
                                to_char(trunc(SetUserAttr.start_date),
                                        WF_CORE.Canonical_Date_Mask), 
                                        l_params);
    if ((((l_oldStartDate is NOT NULL) and 
         (SetUserAttr.start_date is NOT NULL)) and
         (trunc(l_oldStartDate) <> trunc(SetUserAttr.start_date))) or
        ((l_oldStartDate is NULL and SetUserAttr.start_date is NOT NULL) or
         (SetUserAttr.start_date is NULL and l_oldStartDate is NOT NULL))) then

      WF_EVENT.AddParameterToList('OLD_START_DATE', 
                                  to_char(trunc(l_oldStartDate),
                                          WF_CORE.Canonical_Date_Mask), 
                                          l_params);
    else
      WF_EVENT.AddParameterToList('OLD_START_DATE', '*UNDEFINED*', 
                                          l_params);
    end if;

    if ((((l_oldEndDate is NOT NULL) and 
         (SetUserAttr.expiration_date is NOT NULL)) and
         (trunc(l_oldEndDate) <> trunc(SetUserAttr.expiration_date))) or
        ((l_oldStartDate is NULL and SetUserAttr.expiration_date is NOT NULL) 
        or (SetUserAttr.expiration_date is NULL 
          and l_oldEndDate is NOT NULL))) then

      WF_EVENT.AddParameterToList('OLD_END_DATE', 
                                  to_char(trunc(l_oldEndDate),
                                          WF_CORE.Canonical_Date_Mask),
                                  l_params);
    else
      WF_EVENT.AddParameterToList('OLD_END_DATE', '*UNDEFINED*', 
                                          l_params);
    end if;

    WF_EVENT.AddParameterToList('PARENT_ORIG_SYSTEM', 
                                SetUserAttr.parent_orig_system, l_params);
    WF_EVENT.AddParameterToList('PARENT_ORIG_SYSTEM_ID', 
                                to_char(SetUserAttr.parent_orig_system_id), 
                                l_params);
    WF_EVENT.AddParameterToList('OWNER_TAG', SetUserAttr.owner_tag, l_params);
    WF_EVENT.AddParameterToList('LAST_UPDATED_BY', 
                                to_char(SetUserAttr.last_updated_by),
                                l_params);
    WF_EVENT.AddParameterToList('LAST_UPDATE_DATE', 
                                to_char(SetUserAttr.last_update_date, 
                                        WF_CORE.canonical_date_mask), l_params);
    WF_EVENT.AddParameterToList('LAST_UPDATE_LOGIN', 
                                to_char(SetUserAttr.last_update_login),
                                l_params);

    WF_EVENT.Raise(p_event_name=>'oracle.apps.fnd.wf.ds.user.updated',
                   p_event_key=>user_name, p_parameters=>l_params);

exception
  when others then
    wf_core.context('Wf_Directory', 'SetUserAttr', user_name, display_name);
    raise;
end SetUserAttr;

--
-- SetRoleAttr (PRIVATE)
--   Update additional attributes for roles
-- IN
--   role_name        - role name
--   orig_system      -
--   orig_system_id   -
--   display_name  - 
--   notification_preference -
--   language      -
--   territory     -
--   email_address -
--   fax           -
--   expiration_date  - New expiration date
--   status           - status could be 'ACTIVE' or 'INACTIVE'
-- OUT
--
procedure SetRoleAttr(role_name               in  varchar2,
                      orig_system             in  varchar2,
                      orig_system_id          in  number,
                      display_name            in  varchar2,
                      description             in  varchar2,
                      notification_preference in  varchar2,
                      language                in  varchar2,
                      territory               in  varchar2,
                      email_address           in  varchar2,
                      fax                     in  varchar2,
                      expiration_date         in  date,
                      status                  in  varchar2,
                      start_date              in  date,
                      OverWrite               in  boolean,
                      Parent_Orig_System      in  varchar2,
                      Parent_Orig_System_ID   in  number,
                      owner_tag               in  varchar2,
                      last_updated_by         in  number,
                      last_update_date        in  date,
                      last_update_login       in  number)
is
  CURSOR user_roles is
    SELECT USER_NAME, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID, START_DATE,
           EXPIRATION_DATE
    FROM   WF_LOCAL_USER_ROLES
    WHERE  ROLE_ORIG_SYSTEM = SetRoleAttr.orig_system
    AND    ROLE_ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
    AND    ROLE_NAME = SetRoleAttr.role_name;

  l_expiration DATE;
  l_params WF_PARAMETER_LIST_T;
  l_oldStartDate DATE;
  l_oldEndDate   DATE;

begin
  --We first need to capture the current start/end date in case they are 
  --changed.
  begin
    SELECT START_DATE, EXPIRATION_DATE
    INTO   l_oldStartDate, l_oldEndDate
    FROM   WF_LOCAL_ROLES
    WHERE  NAME = SetRoleAttr.role_name
    AND    ORIG_SYSTEM = SetRoleAttr.orig_system
    AND    ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id;
  exception
    when NO_DATA_FOUND then
      WF_CORE.Token('ROLENAME', role_name);
      WF_CORE.Raise('WF_INVALID_ROLE');
  end;
  --
  -- Update WF_LOCAL_ROLES
  --
  if (OverWrite) then
    --Update the description field and display name field
    --in the base table only if the session language is 'US'
    --Else update theses values for the _TL table and keep the 
    --base table values same  
    if ((userenv('LANG') = 'US') OR 
        (isMLSEnabled(SetRoleAttr.orig_system) = FALSE)) then
      update WF_LOCAL_ROLES
      set    NOTIFICATION_PREFERENCE = nvl(SetRoleAttr.notification_preference,
                                           NOTIFICATION_PREFERENCE),
             LANGUAGE                = nvl(SetRoleAttr.language, LANGUAGE),
             TERRITORY               = nvl(SetRoleAttr.territory, TERRITORY),
             EMAIL_ADDRESS           = SetRoleAttr.email_address,
             FAX                     = SetRoleAttr.fax,
             DISPLAY_NAME            = nvl(SetRoleAttr.display_name,
                                         DISPLAY_NAME),
             DESCRIPTION             = SetRoleAttr.description,
             EXPIRATION_DATE         = SetRoleAttr.expiration_date,
             STATUS                  = nvl(SetRoleAttr.status, STATUS),
             START_DATE              = SetRoleAttr.start_date,
             PARENT_ORIG_SYSTEM      = SetRoleAttr.parent_orig_system,
             PARENT_ORIG_SYSTEM_ID   = SetRoleAttr.parent_orig_system_id,
             OWNER_TAG               = nvl(SetRoleAttr.owner_tag,
                                           OWNER_TAG),
             LAST_UPDATED_BY         = nvl(SetRoleAttr.last_updated_by,
                                           WFA_SEC.user_id),
             LAST_UPDATE_DATE        = sysdate,
             LAST_UPDATE_LOGIN       = nvl(SetRoleAttr.last_update_login,
                                           WFA_SEC.login_id)
      where  NAME           = role_name
        and  ORIG_SYSTEM    = SetRoleAttr.orig_system
        and  ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id;

      --Bug 3490260
      --lets keep the code here rather than end for better understanding 
      --If the role information was not updated, we need to raise an 
      --invalid role error so the caller can call the CreateRole api.
      if (sql%rowcount = 0) then
        WF_CORE.Token('ROLENAME', role_name);
        WF_CORE.Raise('WF_INVALID_ROLE');
      end if;

    else
      update WF_LOCAL_ROLES
      set    NOTIFICATION_PREFERENCE = nvl(SetRoleAttr.notification_preference,
                                             NOTIFICATION_PREFERENCE),
             LANGUAGE                = nvl(SetRoleAttr.language, LANGUAGE),
             TERRITORY               = nvl(SetRoleAttr.territory, TERRITORY),
             EMAIL_ADDRESS           = SetRoleAttr.email_address,
             FAX                     = SetRoleAttr.fax,
             EXPIRATION_DATE         = SetRoleAttr.expiration_date,
             STATUS                  = nvl(SetRoleAttr.status, STATUS),
             START_DATE              = SetRoleAttr.start_date,
             PARENT_ORIG_SYSTEM      = SetRoleAttr.parent_orig_system,
             PARENT_ORIG_SYSTEM_ID   = SetRoleAttr.parent_orig_system_id,
             OWNER_TAG               = nvl(SetRoleAttr.owner_tag,
                                           OWNER_TAG),
             LAST_UPDATED_BY         = nvl(SetRoleAttr.last_updated_by,
                                           WFA_SEC.user_id),
             LAST_UPDATE_DATE        = sysdate,
             LAST_UPDATE_LOGIN       = nvl(SetRoleAttr.last_update_login,
                                           WFA_SEC.login_id)
      where  NAME           = role_name
        and  ORIG_SYSTEM    = SetRoleAttr.orig_system
        and  ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id;
        
      --If the base role information was not updated, we need to raise an 
      --invalid role error so the caller can call the CreateRole api.
      if (sql%rowcount = 0) then
        WF_CORE.Token('ROLENAME', role_name);
        WF_CORE.Raise('WF_INVALID_ROLE');
      end if;
      
      --Update the _TL table for the display_name and 
      --description
      update WF_LOCAL_ROLES_TL
      set    DISPLAY_NAME            = nvl(SetRoleAttr.display_name,
                                           DISPLAY_NAME),
             DESCRIPTION             = SetRoleAttr.description,
             OWNER_TAG               = nvl(SetRoleAttr.owner_tag,
                                           OWNER_TAG),
             LAST_UPDATED_BY         = nvl(SetRoleAttr.last_updated_by,
                                           WFA_SEC.user_id),
             LAST_UPDATE_DATE        = sysdate,
             LAST_UPDATE_LOGIN       = nvl(SetRoleAttr.last_update_login,
                                           WFA_SEC.login_id)
      where  NAME           = role_name
      and  ORIG_SYSTEM    = SetRoleAttr.orig_system
      and  ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id;

      if (sql%rowcount = 0) then
        --No record exist for this lang in _TL table
        --so insert a record
        --The issue is that if the passed values or param 
        --are null we would be inserting 'US' language
        --display name as its non-nullable was max we can do is to 
        --add a 'NON_TRANSLATED' string or something to recognize.

        insert into  WF_LOCAL_ROLES_TL (NAME,
                                       DISPLAY_NAME,
                                       DESCRIPTION,
                                       ORIG_SYSTEM,
                                       ORIG_SYSTEM_ID,
                                       PARTITION_ID,
                                       LANGUAGE,
                                       OWNER_TAG,
                                       CREATED_BY,
                                       CREATION_DATE,
                                       LAST_UPDATED_BY,
                                       LAST_UPDATE_DATE,
                                       LAST_UPDATE_LOGIN )
        (select name, 
             nvl(SetRoleAttr.display_name, DISPLAY_NAME) ,
             SetRoleAttr.description, 
             ORIG_SYSTEM, 
             ORIG_SYSTEM_ID ,
             PARTITION_ID,
             userenv('LANG'), 
             nvl(SetRoleAttr.owner_tag, OWNER_TAG),
             nvl(SetRoleAttr.last_updated_by, WFA_SEC.user_id),
             trunc(sysdate),
             nvl(SetRoleAttr.last_updated_by, WFA_SEC.user_id),
             nvl(SetRoleAttr.last_update_date, trunc(sysdate)),
             nvl(SetRoleAttr.last_update_login, WFA_SEC.login_id)
         from WF_LOCAL_ROLES
         where  NAME           = role_name
         and  ORIG_SYSTEM      = SetRoleAttr.orig_system
         and  ORIG_SYSTEM_ID   = SetRoleAttr.orig_system_id);


        --Now update the table for relevant data

     end if;

    end if;
  else  --(NOT overWrite)
    if ((userenv('LANG') = 'US') OR 
        (wf_directory.isMLSEnabled(SetRoleAttr.orig_system)  = FALSE)) then
    update WF_LOCAL_ROLES
      set NOTIFICATION_PREFERENCE = nvl(SetRoleAttr.notification_preference, 
                                        NOTIFICATION_PREFERENCE),
           LANGUAGE        = nvl(SetRoleAttr.language, LANGUAGE),
           TERRITORY       = nvl(SetRoleAttr.territory, TERRITORY),
           EMAIL_ADDRESS   = nvl(SetRoleAttr.email_address, EMAIL_ADDRESS),
           FAX             = nvl(SetRoleAttr.fax, FAX),
           DISPLAY_NAME    = nvl(SetRoleAttr.display_name, DISPLAY_NAME),
           DESCRIPTION     = nvl(SetRoleAttr.description, DESCRIPTION),
           EXPIRATION_DATE = nvl(SetRoleAttr.expiration_date, EXPIRATION_DATE),
           STATUS          = nvl(SetRoleAttr.status, STATUS),
           START_DATE      = nvl(SetRoleAttr.start_date, START_DATE),
           PARENT_ORIG_SYSTEM = nvl(SetRoleAttr.parent_orig_system,
                                    PARENT_ORIG_SYSTEM),
           PARENT_ORIG_SYSTEM_ID = nvl(SetRoleAttr.parent_orig_system_id,
                                       PARENT_ORIG_SYSTEM_ID),
             OWNER_TAG               = nvl(SetRoleAttr.owner_tag,
                                           OWNER_TAG),
             LAST_UPDATED_BY         = nvl(SetRoleAttr.last_updated_by,
                                           WFA_SEC.user_id),
             LAST_UPDATE_DATE        = sysdate,
             LAST_UPDATE_LOGIN       = nvl(SetRoleAttr.last_update_login,
                                           WFA_SEC.login_id)
     where NAME = role_name
     and   ORIG_SYSTEM = SetRoleAttr.orig_system
     and   ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id;

     --Bug 3490260
     --lets keep the code here rather than end for better understanding
     --If the role information was not updated, we need to raise an 
     --invalid role error so the caller can call the CreateRole api.
     if (sql%rowcount = 0) then
       WF_CORE.Token('ROLENAME', role_name);
       WF_CORE.Raise('WF_INVALID_ROLE');
     end if;

    else
      update WF_LOCAL_ROLES
      set NOTIFICATION_PREFERENCE = nvl(SetRoleAttr.notification_preference,
                                        NOTIFICATION_PREFERENCE),
           LANGUAGE        = nvl(SetRoleAttr.language, LANGUAGE),
           TERRITORY       = nvl(SetRoleAttr.territory, TERRITORY),
           EMAIL_ADDRESS   = nvl(SetRoleAttr.email_address, EMAIL_ADDRESS),
           FAX             = nvl(SetRoleAttr.fax, FAX),
           EXPIRATION_DATE = nvl(SetRoleAttr.expiration_date, EXPIRATION_DATE),
           STATUS          = nvl(SetRoleAttr.status, STATUS),
           START_DATE      = nvl(SetRoleAttr.start_date, START_DATE),
           PARENT_ORIG_SYSTEM    = nvl(SetRoleAttr.parent_orig_system,
                                       PARENT_ORIG_SYSTEM),
           PARENT_ORIG_SYSTEM_ID = nvl(SetRoleAttr.parent_orig_system_id,
                                       PARENT_ORIG_SYSTEM_ID),
           OWNER_TAG             = nvl(SetRoleAttr.owner_tag,
                                       OWNER_TAG),
           LAST_UPDATED_BY       = nvl(SetRoleAttr.last_updated_by,
                                       WFA_SEC.user_id),
           LAST_UPDATE_DATE      = sysdate,
           LAST_UPDATE_LOGIN     = nvl(SetRoleAttr.last_update_login,
                                       WFA_SEC.login_id)
     where NAME = role_name
     and   ORIG_SYSTEM = SetRoleAttr.orig_system
     and   ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id;

     --If the base role information was not updated, we need to raise an 
     --invalid role error so the caller can call the CreateRole api.
     if (sql%rowcount = 0) then
       WF_CORE.Token('ROLENAME', role_name);
       WF_CORE.Raise('WF_INVALID_ROLE');
     end if;
     
     --Update the _TL table for the display_name and
     --description
     update  WF_LOCAL_ROLES_TL
      set    DISPLAY_NAME            = nvl(SetRoleAttr.display_name,
                                         DISPLAY_NAME),
             DESCRIPTION             = nvl(SetRoleAttr.description,DESCRIPTION),
             OWNER_TAG               = nvl(SetRoleAttr.owner_tag,
                                           OWNER_TAG),
             LAST_UPDATED_BY         = nvl(SetRoleAttr.last_updated_by,
                                           WFA_SEC.user_id),
             LAST_UPDATE_DATE        = sysdate,
             LAST_UPDATE_LOGIN       = nvl(SetRoleAttr.last_update_login,
                                           WFA_SEC.login_id)
      where  NAME           = role_name
      and    ORIG_SYSTEM    = SetRoleAttr.orig_system
      and    ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id;

      if (sql%rowcount = 0) then
        --No record exist for this lang in _TL table
        --so insert a record
        --The issue is that if the passed values or param
        --are null we would be inserting 'US' language
        --display name as its non-nullable

        insert into  WF_LOCAL_ROLES_TL (NAME,
                                       DISPLAY_NAME,
                                       DESCRIPTION,
                                       ORIG_SYSTEM,
                                       ORIG_SYSTEM_ID,
                                       PARTITION_ID,
                                       LANGUAGE,
                                       OWNER_TAG,
                                       CREATED_BY,
                                       CREATION_DATE,
                                       LAST_UPDATED_BY,
                                       LAST_UPDATE_DATE,
                                       LAST_UPDATE_LOGIN )
        (select name, 
             nvl(SetRoleAttr.display_name, DISPLAY_NAME),
             SetRoleAttr.description, 
             ORIG_SYSTEM, 
             ORIG_SYSTEM_ID ,
             PARTITION_ID,
             userenv('LANG'), 
             nvl(SetRoleAttr.owner_tag, OWNER_TAG),
             nvl(SetRoleAttr.last_updated_by, WFA_SEC.user_id),
             trunc(sysdate),
             nvl(SetRoleAttr.last_updated_by, WFA_SEC.user_id),
             nvl(SetRoleAttr.last_update_date, trunc(sysdate)),
             nvl(SetRoleAttr.last_update_login, WFA_SEC.login_id)
         from WF_LOCAL_ROLES
         where  NAME           = SetRoleAttr.role_name
         and  ORIG_SYSTEM      = SetRoleAttr.orig_system
         and  ORIG_SYSTEM_ID   = SetRoleAttr.orig_system_id);

     end if; 


    end if;
  end if;

    --We were able to update the record, so we will raise the updated event
    --Build parameter list.
    WF_EVENT.AddParameterToList('ROLE_NAME', SetRoleAttr.role_name, l_params);
    WF_EVENT.AddParameterToList('ORIG_SYSTEM', SetRoleAttr.orig_system,
                                l_params);
    WF_EVENT.AddParameterToList('ORIG_SYSTEM_ID',
                                to_char(SetRoleAttr.orig_system_id), l_params);
    WF_EVENT.AddParameterToList('DISPLAY_NAME', SetRoleAttr.display_name,
                                l_params);
    WF_EVENT.AddParameterToList('DESCRIPTION', SetRoleAttr.description,
                                l_params);
    WF_EVENT.AddParameterToList('NOTIFICATION_PREFERENCE', 
                                SetRoleAttr.notification_preference, l_params);
    WF_EVENT.AddParameterToList('LANGUAGE', SetRoleAttr.language,  l_params);
    WF_EVENT.AddParameterToList('TERRITORY', SetRoleAttr.territory, l_params);
    WF_EVENT.AddParameterToList('EMAIL_ADDRESS', SetRoleAttr.email_address,
                                l_params);
    WF_EVENT.AddParameterToList('FAX', SetRoleAttr.fax, l_params);
    WF_EVENT.AddParameterToList('EXPIRATION_DATE', 
                                to_char(trunc(SetRoleAttr.expiration_date),
                                        WF_CORE.Canonical_Date_Mask),
                                l_params);
    WF_EVENT.AddParameterToList('STATUS', SetRoleAttr.status, l_params);
    WF_EVENT.AddParameterToList('START_DATE', 
                                to_char(trunc(SetRoleAttr.start_date),
                                        WF_CORE.Canonical_Date_Mask), 
                                        l_params);
    if ((((l_oldStartDate is NOT NULL) and 
         (SetRoleAttr.start_date is NOT NULL)) and
         (trunc(l_oldStartDate) <> trunc(SetRoleAttr.start_date))) or
        ((l_oldStartDate is NULL and SetRoleAttr.start_date is NOT NULL) or
         (SetRoleAttr.start_date is NULL and l_oldStartDate is NOT NULL))) then

      WF_EVENT.AddParameterToList('OLD_START_DATE', 
                                  to_char(trunc(l_oldStartDate),
                                          WF_CORE.Canonical_Date_Mask), 
                                          l_params);
    else
      WF_EVENT.AddParameterToList('OLD_START_DATE', '*UNDEFINED*', 
                                          l_params);
    end if;

    if ((((l_oldEndDate is NOT NULL) and 
         (SetRoleAttr.expiration_date is NOT NULL)) and
         (trunc(l_oldEndDate) <> trunc(SetRoleAttr.expiration_date))) or
        ((l_oldStartDate is NULL and SetRoleAttr.expiration_date is NOT NULL) 
        or (SetRoleAttr.expiration_date is NULL 
          and l_oldEndDate is NOT NULL))) then

      WF_EVENT.AddParameterToList('OLD_END_DATE', 
                                  to_char(trunc(l_oldEndDate),
                                          WF_CORE.Canonical_Date_Mask),
                                  l_params);
    else
      WF_EVENT.AddParameterToList('OLD_END_DATE', '*UNDEFINED*',
                                  l_params);
    end if;

    WF_EVENT.AddParameterToList('PARENT_ORIG_SYSTEM', 
                                SetRoleAttr.parent_orig_system, l_params);
    WF_EVENT.AddParameterToList('PARENT_ORIG_SYSTEM_ID', 
                                to_char(SetRoleAttr.parent_orig_system_id), 
                                l_params);
    WF_EVENT.AddParameterToList('OWNER_TAG', SetRoleAttr.owner_tag, l_params);
    WF_EVENT.AddParameterToList('LAST_UPDATED_BY', 
                                to_char(SetRoleAttr.last_updated_by),
                                l_params);
    WF_EVENT.AddParameterToList('LAST_UPDATE_DATE', 
                                to_char(SetRoleAttr.last_update_date, 
                                        WF_CORE.canonical_date_mask), l_params);
    WF_EVENT.AddParameterToList('LAST_UPDATE_LOGIN', 
                                to_char(SetRoleAttr.last_update_login),
                                l_params);

    WF_EVENT.Raise(p_event_name=>'oracle.apps.fnd.wf.ds.role.updated',
                   p_event_key=>role_name, p_parameters=>l_params);

exception
  when others then
    wf_core.context('Wf_Directory', 'SetRoleAttr', SetRoleAttr.role_name,
                    SetRoleAttr.display_name);
    raise;
end SetRoleAttr;

--
-- SetAdHocUserExpiration
--   Update expiration date for ad hoc users
-- IN
--   user_name        - Ad hoc user name
--   expiration_date  - New expiration date
-- OUT
--
procedure SetAdHocUserExpiration(user_name      in varchar2,
                      expiration_date           in date)
is
begin
  --
  -- Update Expiration Date
  --
  SetUserAttr(user_name=>SetAdHocUserExpiration.user_name,
              orig_system=>'WF_LOCAL_USERS',
              orig_system_id=>0,
              display_name=>NULL,
              notification_preference=>NULL,
              language=>NULL,
              territory=>NULL,
              email_address=>NULL,
              fax=>NULL,
              expiration_date=>SetAdHocUserExpiration.expiration_date,
              status=>NULL);

exception
  when others then
    wf_core.context('Wf_Directory', 'SetAdHocUserExpiration', user_name, expiration_date);
    raise;
end SetAdHocUserExpiration;

--
-- SetAdHocRoleExpiration
--   Update expiration date for ad hoc roles, user roles
-- IN
--   role_name        - Ad hoc role name
--   expiration_date  - New expiration date
-- OUT
--
procedure SetAdHocRoleExpiration(role_name      in varchar2,
                      expiration_date           in date)
is
begin
  --
  -- Update Expiration Date
  --
  SetRoleAttr(role_name=>SetAdHocRoleExpiration.role_name,
              orig_system=>'WF_LOCAL_ROLES',
              orig_system_id=>0,
              display_name=>NULL,
              notification_preference=>NULL,
              language=>NULL,
              territory=>NULL,
              email_address=>NULL,
              fax=>NULL,
              expiration_date=>SetAdHocRoleExpiration.expiration_date,
              status=>NULL);
              
exception
  when others then
    wf_core.context('Wf_Directory', 'SetAdHocRoleExpiration', role_name, 
                    expiration_date);
    raise;
end SetAdHocRoleExpiration;

--
-- SetAdHocUserAttr
--   Update additional attributes for ad hoc users
-- IN
--   user_name        - Ad hoc user name
--   display_name  -
--   notification_preference -
--   language      -
--   territory     -
--   email_address -
--   fax           -
-- OUT
--
procedure SetAdHocUserAttr(user_name          in  varchar2,
                      display_name            in  varchar2,
                      notification_preference in  varchar2,
                      language                in  varchar2,
                      territory               in  varchar2,
                      email_address           in  varchar2,
                      fax                     in  varchar2,
                      parent_orig_system      in  varchar2,
                      parent_orig_system_id   in  number,
                      owner_tag               in  varchar2)
is
begin
  --
  -- Update the user.
  --
  SetUserAttr(user_name=>SetAdHocUserAttr.user_name,
              orig_system=>'WF_LOCAL_USERS',
              orig_system_id=>0,
              display_name=>SetAdHocUserAttr.display_name,
              notification_preference=>SetAdHocUserAttr.notification_preference,
              language=>SetAdHocUserAttr.language,
              territory=>SetAdHocUserAttr.territory,
              email_address=>SetAdHocUserAttr.email_address,
              fax=>SetAdHocUserAttr.fax,
              expiration_date=>NULL,
              status=>NULL,
              parent_orig_system=>SetAdHocUserAttr.parent_orig_system,
              parent_orig_system_id=>SetAdHocUserAttr.parent_orig_system_id,
              owner_tag=>SetAdhocUserAttr.owner_tag);
  
exception
  when others then
    wf_core.context('Wf_Directory', 'SetAdHocUserAttr', user_name, 
                    display_name);
    raise;
end SetAdHocUserAttr;

--
-- SetAdHocRoleAttr
--   Update additional attributes for ad hoc roles, user roles
-- IN
--   role_name        - Ad hoc role name
--   display_name  -
--   notification_preference -
--   language      -
--   territory     -
--   email_address -
--   fax           -
-- OUT
--
procedure SetAdHocRoleAttr(role_name          in  varchar2,
                      display_name            in  varchar2,
                      notification_preference in  varchar2,
                      language                in  varchar2,
                      territory               in  varchar2,
                      email_address           in  varchar2,
                      fax                     in  varchar2,
                      parent_orig_system      in  varchar2,
                      parent_orig_system_id   in  number,
                      owner_tag               in  varchar2)
is
begin
  --
  -- Update the role
  --
  SetRoleAttr(role_name=>SetAdHocRoleAttr.role_name,
              orig_system=>'WF_LOCAL_ROLES',
              orig_system_id=>0,
              display_name=>SetAdHocRoleAttr.display_name,
              notification_preference=>SetAdHocRoleAttr.notification_preference,
              language=>SetAdHocRoleAttr.language,
              territory=>SetAdHocRoleAttr.territory,
              email_address=>SetAdHocRoleAttr.email_address,
              fax=>fax,
              expiration_date=>NULL,
              status=>NULL,
              parent_orig_system=>SetAdHocRoleAttr.parent_orig_system,
              parent_orig_system_id=>SetAdHocRoleAttr.parent_orig_system_id,
              owner_tag=>SetAdHocRoleAttr.owner_tag);

exception
  when others then
    wf_core.context('Wf_Directory', 'SetAdHocRoleAttr', role_name, 
                    display_name);
    raise;
end SetAdHocRoleAttr;

--
-- RemoveUsersFromAdHocRole
--   Remove users from an existing ad hoc role
-- IN
--   role_name     -
--   role_users    -
-- OUT
--
procedure RemoveUsersFromAdHocRole(role_name in varchar2,
                      role_users             in varchar2)
is
  user varchar2(320);
  rest varchar2(2000);
  c1   pls_integer;
begin
  if (role_users is null) then
    -- Delete all users
    begin
      delete from WF_LOCAL_USER_ROLES UR
       where UR.ROLE_NAME = RemoveUsersFromAdHocRole.role_name
         and UR.ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
         and UR.ROLE_ORIG_SYSTEM_ID = 0;
    end;
  else
    --
    -- Delete Users
    --
    rest := ltrim(role_users);
    loop
      c1 := instr(rest, ',');
      if (c1 = 0) then
         c1 := instr(rest, ' ');
        if (c1 = 0) then
          user := rest;
        else
          user := substr(rest, 1, c1-1);
        end if;
      else
        user := substr(rest, 1, c1-1);
      end if;

      -- Delete
      delete from WF_LOCAL_USER_ROLES UR
       where UR.USER_NAME = user
         and UR.ROLE_NAME = RemoveUsersFromAdHocRole.role_name
         and UR.ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
         and UR.ROLE_ORIG_SYSTEM_ID = 0;
      if (sql%rowcount = 0) then
        wf_core.token('USERNAME', user);
        wf_core.raise('WF_INVALID_USER');
      end if;
      exit when (c1 = 0);

      rest := ltrim(substr(rest, c1+1));
    end loop;
  end if;

exception
  when others then
    wf_core.context('Wf_Directory', 'RemoveUsersFromAdHocRole',
        role_name, '"'||role_users||'"');
    raise;
end RemoveUsersFromAdHocRole;


--
-- ChangeLocalUserName
--  Change a User's Name in the WF_LOCAL_ROLES table.
-- IN
--  OldName
--  NewName
--  Propagate - call WF_MAINTENANCE.PropagateChangedName
-- OUT
--

function ChangeLocalUserName (OldName in varchar2,
                              NewName in varchar2,
                              Propagate in boolean)
return boolean

is
NumRows pls_integer;
l_oldname varchar2(320);
l_newname varchar2(320);

begin
  l_newname := substrb(NewName,1,320);
  l_oldname := substrb(OldName,1,320);

  NumRows := wfa_sec.DS_Count_Local_Role(l_oldname);

  if (NumRows = 1) then
    wfa_sec.DS_Update_Local_Role(l_oldname,l_newname);
    commit;

    if (Propagate) then
	WF_MAINTENANCE.PropagateChangedName(l_oldname, l_newname);
    end if;

    return TRUE;

  else

    return FALSE;

  end if;

exception
 when others then
 WF_CORE.Context('WF_DIRECTORY', 'ChangeLocalUserName', OldName, NewName);
 raise;

end ChangeLocalUserName;

--
-- ReassignUserRoles
--   Reassigns user/roles when the user information changes.
-- IN
--   p_user_name
--   p_old_user_origSystem
--   p_old_user_origSystemID
--   p_new_user_origSystem
--   p_new_user_origSystemID
--
-- OUT
--
procedure ReassignUserRoles (p_user_name             in VARCHAR2,
                             p_old_user_origSystem   in VARCHAR2,
                             p_old_user_origSystemID in VARCHAR2,
                             p_new_user_origSystem   in VARCHAR2,
                             p_new_user_origSystemID in VARCHAR2) is
                              
BEGIN

  Update WF_LOCAL_USER_ROLES
  Set    USER_ORIG_SYSTEM = p_new_user_origSystem,
         USER_ORIG_SYSTEM_ID = p_new_user_origSystemID
  Where  USER_ORIG_SYSTEM = p_old_user_origSystem
  And    USER_ORIG_SYSTEM_ID = p_old_user_origSystemID
  And    USER_NAME = p_user_name
  And    nvl(EXPIRATION_DATE, sysdate+1) > sysdate;
  
END;


--
-- AssignPartition (PRIVATE)
--
-- IN
--  p_orig_system (VARCHAR2)
-- 
-- RETURNS
--  Partition ID (NUMBER)
--
-- COMMENTS
--  This api will check to see the partition for the p_orig_system exists.
--  if it does not exist, it will be added to p_table_name.  In either case
--  the Partition_ID will be returned for the calling api to properly populate
--  that column on insert/update.
--
procedure AssignPartition (p_orig_system   in  varchar2,
                           p_partitionID   out NOCOPY number,
                           p_partitionName out NOCOPY varchar2) is

begin
  begin
    --Check for existing partition.
      if ((g_origSystem <> UPPER(p_orig_system))) or (g_origSystem is NULL) then

      --Orig_systems such as FND_RESP have the application id concatenated
      --which makes the responsibilities for various applications fall under
      --different orig_systems.  However all responsibilities need to go into
      --the same partition, so we handle that here.  Any other systems that
      --we need to bulksynch who add to the orig system will need to be here 
      --as well.
        if ((substr(UPPER(p_orig_system), 1, 8) = 'FND_RESP') and
            ((length(p_orig_system) = 8) or --In case we just get 'FND_RESP'
            (substr(p_orig_system, 9, 9) between '0' and '9'))) then
          g_origSystem := 'FND_RESP';

        else 
          g_origSystem := UPPER(p_orig_system);

        end if;
        /* We will place PER in FND_USR */
           
        SELECT Partition_ID, orig_system
        INTO   g_partitionID, g_partitionName
        FROM   WF_DIRECTORY_PARTITIONS
        WHERE  ORIG_SYSTEM = DECODE(g_origSystem, 'PER', 'FND_USR',
                                    g_origSystem)
        AND    PARTITION_ID IS NOT NULL;

    end if;

  exception
    when NO_DATA_FOUND then
        --If the partition does not exist, we will put this into the 
        --WF_LOCAL partition
        if (g_localPartitionID is NULL) then
          begin
            SELECT Partition_ID, orig_system
            INTO   g_localPartitionID, g_localPartitionName
            FROM   WF_DIRECTORY_PARTITIONS
            WHERE  ORIG_SYSTEM = 'WF_LOCAL_ROLES';
  
         exception
           when NO_DATA_FOUND then
             g_localPartitionID := 0;
             g_localPartitionName := 'WF_LOCAL_ROLES';

         end;
       end if;

       g_partitionID := g_localPartitionID;    
       g_partitionName := g_localPartitionName;

  end;
  p_partitionID := g_partitionID;
  p_partitionName := g_partitionName;

exception
  when OTHERS then
    WF_CORE.Context('WF_DIRECTORY', 'AssignPartition', p_orig_system);
    raise;
end;

-- Bug 3090738
-- GetInfoFromMail
--
-- IN
--   email address
-- OUT
--   User attributes as in WF_ROLES view
--
-- This API queries wf_roles view for information of the user when
-- the e-mail address is given.
procedure GetInfoFromMail(mailid            in         varchar2,
                          role              out NOCOPY varchar2,
                          display_name      out NOCOPY varchar2,
                          description       out NOCOPY varchar2,
                          notification_preference out NOCOPY varchar2,
                          language          out NOCOPY varchar2,
                          territory         out NOCOPY varchar2,
                          fax               out NOCOPY varchar2,
                          expiration_date   out NOCOPY date,
                          status            out NOCOPY varchar2,
                          orig_system       out NOCOPY varchar2,
                          orig_system_id    out NOCOPY number)
is
  l_email    varchar2(2000);
  l_start    pls_integer;
  l_end      pls_integer;
begin
  -- strip off the unwanted info from email. Emails from the mailer
  -- could be of the form "First Last"<First.Last@example.com>
  l_start := instr(mailid, '<', 1, 1);
  if (l_start > 0) then
     l_end := instr(mailid, '>', l_start);
     l_email := substr(mailid, l_start+1, l_end-l_start-1);
  else
     l_email := mailid;
  end if;

  -- lets find any active user with this e-mail id if not we will
  -- check for inactive user
  begin
     select R.NAME,
            substrb(R.DISPLAY_NAME,1,360),
            substrb(R.DESCRIPTION,1,1000),
            R.NOTIFICATION_PREFERENCE,
            R.LANGUAGE,
            R.TERRITORY,
            R.FAX,
            R.STATUS,
            R.EXPIRATION_DATE,
            R.ORIG_SYSTEM,
            R.ORIG_SYSTEM_ID
     into   ROLE,
            DISPLAY_NAME,
            DESCRIPTION,
            NOTIFICATION_PREFERENCE,
            LANGUAGE,
            TERRITORY,
            FAX,
            STATUS,
            EXPIRATION_DATE,
            ORIG_SYSTEM,
            ORIG_SYSTEM_ID
     from   WF_ROLES R
     where  UPPER(email_address) = UPPER(l_email)
     and    STATUS = 'ACTIVE';
  exception
     when no_data_found then
        -- Give preference to active user
        select R.NAME,
               substrb(R.DISPLAY_NAME,1,360),
               substrb(R.DESCRIPTION,1,1000),
               R.NOTIFICATION_PREFERENCE,
               R.LANGUAGE,
               R.TERRITORY,
               R.FAX,
               R.STATUS,
               R.EXPIRATION_DATE,
               R.ORIG_SYSTEM,
               R.ORIG_SYSTEM_ID
        into   ROLE,
               DISPLAY_NAME,
               DESCRIPTION,
               NOTIFICATION_PREFERENCE,
               LANGUAGE,
               TERRITORY,
               FAX,
               STATUS,
               EXPIRATION_DATE,
               ORIG_SYSTEM,
               ORIG_SYSTEM_ID
        from   WF_ROLES R
        where  UPPER(email_address) = UPPER(l_email);
  end;
exception
   when others then
      ROLE := '';
      DISPLAY_NAME := '';
      DESCRIPTION := '';
      NOTIFICATION_PREFERENCE := '';
      LANGUAGE := '';
      TERRITORY := '';
      FAX := '';
      STATUS := '';
      EXPIRATION_DATE := to_date(null);
      ORIG_SYSTEM := '';
      ORIG_SYSTEM_ID := to_number(null);
end GetInfoFromMail;

function IsMLSEnabled(p_orig_system  in   varchar2)
return boolean
is
mls_enabled   number;
l_origSys varchar2(30);
begin
  l_origSys := UPPER(p_orig_system);
  if ((substr(l_origSys, 1, 8) = 'FND_RESP') and
      ((length(l_origSys) = 8) or --In case we just get 'FND_RESP'
       (substr(l_origSys, 9, 9) between '0' and '9'))) then
    l_origSys := 'FND_RESP';

  end if;

  --We can use the global variable set in wf_local.syncroles
  --but for standalone so as not to introduce dependency on WF_LOCAL
  --package we query from wf_directory_partitions directly.
  select  count(1) 
  into    mls_enabled 
  from    wf_directory_partitions
  where   orig_system = l_origSys
  and     ROLE_TL_VIEW is not NULL ;
  if (mls_enabled = 1) then
     return TRUE;
  end if;
  --else case return false 
  return FALSE; 
end IsMLSEnabled;

-- Change_Name_References_RF (PRIVATE)
--
-- IN
--  p_sub_guid (RAW)
--  p_event    (WF_EVENT_T)
--
-- RETURNS
--  varchar2
--
-- COMMENTS
--  This api is a rule function to be called by BES.  It is primarily used for
--  a user name change to update all the fk references.  The subscription is 
--  set as deferred to offline the updates to return control back to the user
--  more quickly.
--
function Change_Name_References_RF( p_sub_guid  in            RAW,
                                    p_event     in out NOCOPY WF_EVENT_T ) 
                          return VARCHAR2 is
    l_newName        VARCHAR2(360);
    l_oldName        VARCHAR2(360);
    
  begin
    l_newName := p_event.getValueForParameter('USER_NAME');
    l_oldName := p_event.getValueForParameter('OLD_USER_NAME');
    
    --Update the user/roles
    UPDATE  WF_LOCAL_USER_ROLES
    SET     USER_NAME = l_newName
    WHERE   USER_NAME = l_oldName;
    
    --Update the user/role assignments
    UPDATE  WF_USER_ROLE_ASSIGNMENTS
    SET     USER_NAME = l_newName
    WHERE   USER_NAME = l_oldName;

    --Call WF_MAINTENANCE to update all the other fk references.
    WF_MAINTENANCE.PropagateChangedName(OLDNAME=>l_oldName, NEWNAME=>l_newName);
    return 'SUCCESS';
exception
  when OTHERS then
    return 'ERROR';
    
end Change_Name_References_RF;

end Wf_Directory;
/
--show errors package body wf_directory;
commit;
exit;