[Home] [Help]
2325: l_lastupdlog:= nvl(CreateUserRole.last_update_login, WFA_SEC.LOGIN_ID);
2326:
2327: -- Insert
2328: begin
2329: insert into WF_LOCAL_USER_ROLES
2330: ( USER_NAME,
2331: ROLE_NAME,
2332: USER_ORIG_SYSTEM,
2333: USER_ORIG_SYSTEM_ID,
2612: --changed.
2613: begin
2614: select START_DATE, EXPIRATION_DATE
2615: into l_oldStartDate, l_oldEndDate
2616: from WF_LOCAL_USER_ROLES
2617: where USER_NAME = SetUserRoleAttr.user_name
2618: and ROLE_NAME = SetUserRoleAttr.role_name
2619: and USER_ORIG_SYSTEM = l_uorigSys
2620: and USER_ORIG_SYSTEM_ID = l_uorigSysID
2632: l_lastupddt := nvl(setuserroleattr.last_update_date, SYSDATE);
2633: l_lastupdlog:= nvl(setuserroleattr.last_update_login, WFA_SEC.LOGIN_ID);
2634:
2635: if (OverWrite and l_updateWho) then
2636: update WF_LOCAL_USER_ROLES
2637: set EXPIRATION_DATE = SetUserRoleAttr.end_date,
2638: START_DATE = SetUserRoleAttr.start_date,
2639: LAST_UPDATED_BY = l_lastupdby,
2640: LAST_UPDATE_DATE = l_lastupddt,
2655: and PARTITION_ID = l_partitionID
2656: returning rowid into l_affectedRow;
2657:
2658: elsif (OverWrite) then --donot Update WHO Columns
2659: update WF_LOCAL_USER_ROLES
2660: set EXPIRATION_DATE = SetUserRoleAttr.end_date,
2661: START_DATE = SetUserRoleAttr.start_date,
2662: PARENT_ORIG_SYSTEM = l_porigSys,
2663: PARENT_ORIG_SYSTEM_ID = l_porigSysID,
2671: and PARTITION_ID = l_partitionID
2672: returning rowid into l_affectedRow;
2673:
2674: elsif (l_updateWho) then -- Update WHO columns
2675: update WF_LOCAL_USER_ROLES
2676: set EXPIRATION_DATE = nvl(SetUserRoleAttr.end_date, EXPIRATION_DATE),
2677: START_DATE = nvl(SetUserRoleAttr.start_date, START_DATE),
2678: PARENT_ORIG_SYSTEM = nvl(SetUserRoleAttr.parent_orig_system,
2679: l_porigSys),
2700: returning rowid into l_affectedRow;
2701:
2702:
2703: else --Donot Update Who columns
2704: update WF_LOCAL_USER_ROLES
2705: set EXPIRATION_DATE = nvl(SetUserRoleAttr.end_date, EXPIRATION_DATE),
2706: START_DATE = nvl(SetUserRoleAttr.start_date, START_DATE),
2707: PARENT_ORIG_SYSTEM = nvl(SetUserRoleAttr.parent_orig_system,
2708: l_porigSys),
2897: AssignPartition( RemoveUserRole.role_orig_system,l_partitionId,l_partitionName);
2898:
2899: if (user_orig_system is null or user_orig_system_id is null) then
2900: -- Expire user
2901: update WF_LOCAL_USER_ROLES
2902: set EXPIRATION_DATE = l_expdate,
2903: EFFECTIVE_END_DATE = l_expdate,
2904: LAST_UPDATED_BY = l_lastupdby,
2905: LAST_UPDATE_LOGIN = l_lastupdlog,
2923:
2924: else
2925:
2926: -- Expire user with orig system and orig system id
2927: update WF_LOCAL_USER_ROLES
2928: set EXPIRATION_DATE = l_expdate,
2929: EFFECTIVE_END_DATE = l_expdate,
2930: LAST_UPDATED_BY = l_lastupdby,
2931: LAST_UPDATE_LOGIN = l_lastupdlog,
3075:
3076: --
3077: -- Commenting out this check and catching a dup user/role exception
3078: -- select count(1) into d1
3079: -- from WF_LOCAL_USER_ROLES
3080: -- where USER_NAME = role_users(userIND)
3081: -- and ROLE_NAME = AddUsersToAdHocRole2.role_name
3082: -- and ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
3083: -- and ROLE_ORIG_SYSTEM_ID = 0;
4204: begin
4205: if (role_users is null) then
4206: -- Delete all users
4207: begin
4208: delete from WF_LOCAL_USER_ROLES UR
4209: where UR.ROLE_NAME = RemoveUsersFromAdHocRole.role_name
4210: and UR.ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
4211: and UR.ROLE_ORIG_SYSTEM_ID = 0
4212: and UR.PARTITION_ID = 0;
4236: user := substr(rest, 1, c1-1);
4237: end if;
4238:
4239: -- Delete
4240: delete from WF_LOCAL_USER_ROLES UR
4241: where UR.USER_NAME = user
4242: and UR.ROLE_NAME = RemoveUsersFromAdHocRole.role_name
4243: and UR.ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
4244: and UR.ROLE_ORIG_SYSTEM_ID = 0
4360: end if; --
4361:
4362: begin
4363: --First Update the user self-reference.
4364: Update WF_LOCAL_USER_ROLES
4365: Set USER_ORIG_SYSTEM = p_new_user_origSystem,
4366: USER_ORIG_SYSTEM_ID = p_new_user_origSystemID,
4367: ROLE_ORIG_SYSTEM = p_new_user_origSystem,
4368: ROLE_ORIG_SYSTEM_ID = p_new_user_origSystemID,
4394: And USER_NAME = p_user_name;
4395: exception
4396: when DUP_VAL_ON_INDEX then
4397: --This is an old reference that can be deleted
4398: Delete from WF_LOCAL_USER_ROLES
4399: Where USER_ORIG_SYSTEM = p_old_user_origSystem
4400: And USER_ORIG_SYSTEM_ID = p_old_user_origSystemID
4401: And ROLE_ORIG_SYSTEM = p_old_user_origSystem
4402: And ROLE_ORIG_SYSTEM_ID = p_old_user_origSystemID
4411: end;
4412:
4413: --Now update all other role references (self-reference is already updated so
4414: --it will not be effected by these updates)
4415: Update WF_LOCAL_USER_ROLES
4416: Set USER_ORIG_SYSTEM = p_new_user_origSystem,
4417: USER_ORIG_SYSTEM_ID = p_new_user_origSystemID,
4418: -- <6817561>
4419: LAST_UPDATE_DATE = decode(l_overWriteUserRoles,'Y', nvl(p_last_update_date, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
4779: l_newName := p_event.getValueForParameter('USER_NAME');
4780: l_oldName := p_event.getValueForParameter('OLD_USER_NAME');
4781:
4782: /* --Update the user/roles
4783: UPDATE WF_LOCAL_USER_ROLES
4784: SET USER_NAME = l_newName
4785: WHERE USER_NAME = l_oldName;
4786:
4787: --Update the user/role assignments
4979:
4980: delete from wf_user_role_assignments
4981: where role_name=p_rolename;
4982:
4983: delete from wf_local_user_roles
4984: where role_name=p_rolename
4985: and role_orig_system=p_roleorigSystem
4986: and role_orig_system_id=p_roleorigSystemID
4987: and partition_id = l_partitionID;
5022:
5023: delete from wf_user_role_assignments
5024: where user_name=p_username;
5025:
5026: delete from wf_local_user_roles
5027: where user_name=p_username
5028: and user_orig_system=p_userorigSystem
5029: and user_orig_system_id=p_userorigSystemID;
5030:
5075: delete from wf_user_role_assignments
5076: where role_name=p_rolename
5077: and user_name=p_username;
5078:
5079: delete from wf_local_user_roles
5080: where role_name=p_rolename
5081: and user_name=p_username
5082: and role_orig_system=p_roleorigSystem
5083: and role_orig_system_id=p_roleorigSystemID