[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ROLE_VIEW
INTO l_viewName
FROM WF_DIRECTORY_PARTITIONS
WHERE ORIG_SYSTEM = UPPER(p_origSystem)
AND PARTITION_ID <> 0 --
AND (ROLE_VIEW is NULL
or ROLE_VIEW <> 'NOBS');
select 'NOBS'
into l_viewName
from dual
where EXISTS (select NULL
from WF_ROLE_HIERARCHIES
where PARTITION_ID = l_partitionID
or SUPERIOR_PARTITION_ID = l_partitionID);
SELECT RELATIONSHIP_ID
BULK COLLECT INTO l_relIDTAB
FROM WF_USER_ROLE_ASSIGNMENTS
WHERE ROLE_NAME = p_RoleName
AND USER_NAME = p_UserName
AND trunc(sysdate) BETWEEN
trunc(EFFECTIVE_START_DATE)
AND trunc(EFFECTIVE_END_DATE);
l_LastUpdatedBy NUMBER := WFA_SEC.USER_ID;
l_LastUpdateDate DATE;
l_LastUpdateLogin NUMBER := WFA_SEC.LOGIN_ID;
UpdateWho BOOLEAN := TRUE;
l_LastUpdatedBy := to_number(p_event.getValueForParameter(
'LAST_UPDATED_BY'), WF_CORE.canonical_number_mask);
l_LastUpdateDate := to_date(p_event.getValueForParameter(
'LAST_UPDATE_DATE'),
WF_CORE.canonical_date_mask);
l_LastUpdateLogin := to_number(p_event.getValueForParameter(
'LAST_UPDATE_LOGIN'), WF_CORE.canonical_number_mask);
if (p_event.getValueForParameter('UPDATE_WHO') ='TRUE') then
UpdateWho:= TRUE;
UpdateWho:= FALSE;
UPDATE WF_LOCAL_USER_ROLES
SET ASSIGNMENT_TYPE = l_assignmentType
WHERE ROWID = l_rowid;
SELECT START_DATE, EXPIRATION_DATE
INTO l_UserStartDate, l_UserEndDate
FROM WF_LOCAL_ROLES
WHERE NAME = l_UserName
AND PARTITION_ID = l_partitionID
and rownum<2;
SELECT START_DATE, EXPIRATION_DATE
INTO l_UserStartDate, l_UserEndDate
FROM WF_LOCAL_ROLES
WHERE NAME = l_UserName
AND ORIG_SYSTEM= l_UserOrigSystem
AND ORIG_SYSTEM_ID = l_UserOrigSystemID
AND PARTITION_ID = l_partitionID
AND rownum<2;
SELECT START_DATE, EXPIRATION_DATE
INTO l_RoleStartDate, l_RoleEndDate
FROM WF_LOCAL_ROLES
WHERE NAME = l_RoleName
AND PARTITION_ID = l_partitionID
AND rownum<2;
SELECT START_DATE, EXPIRATION_DATE
INTO l_RoleStartDate, l_RoleEndDate
FROM WF_LOCAL_ROLES
WHERE NAME = l_RoleName
AND ORIG_SYSTEM= l_RoleOrigSystem
AND ORIG_SYSTEM_ID = l_RoleOrigSystemID
AND PARTITION_ID = l_partitionID
AND rownum<2;
SELECT ROWID, ROLE_START_DATE, ROLE_END_DATE,EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE
BULK COLLECT INTO l_RowIDTAB, l_roleStartTAB,l_roleEndTAB,
l_effStartTAB,l_effEndTAB
FROM WF_USER_ROLE_ASSIGNMENTS
WHERE USER_NAME = l_UserName
AND ASSIGNING_ROLE = l_RoleName;
UPDATE WF_USER_ROLE_ASSIGNMENTS
SET ASSIGNMENT_REASON = l_assignmentReason
WHERE USER_NAME = l_UserName
AND ASSIGNING_ROLE = l_RoleName
AND RELATIONSHIP_ID = -1;
if OverWrite and UpdateWho then
--allow update of creation_date and created_by
forall tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST
update WF_USER_ROLE_ASSIGNMENTS
set START_DATE = l_StartDate,
END_DATE = l_EndDate,
LAST_UPDATED_BY = nvl(l_LastUpdatedBy,WFA_SEC.USER_ID),
LAST_UPDATE_DATE = nvl(l_LastUpdateDate,SYSDATE),
LAST_UPDATE_LOGIN = nvl(l_LastUpdateLogin,WFA_SEC.LOGIN_ID),
CREATION_DATE = nvl(l_CreationDate,CREATION_DATE),
CREATED_BY = nvl(l_CreatedBy, CREATED_BY),
EFFECTIVE_START_DATE = l_effStartTAB(tabIndex),
EFFECTIVE_END_DATE = l_effEndTAB(tabIndex)
where rowid = l_rowIDTAB(tabIndex);
elsif UpdateWho then
forall tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST
update WF_USER_ROLE_ASSIGNMENTS
set START_DATE = l_StartDate,
END_DATE = l_EndDate,
LAST_UPDATED_BY = nvl(l_LastUpdatedBy,WFA_SEC.USER_ID),
LAST_UPDATE_DATE = nvl(l_LastUpdateDate,SYSDATE),
LAST_UPDATE_LOGIN = nvl(l_LastUpdateLogin,WFA_SEC.LOGIN_ID),
EFFECTIVE_START_DATE = l_effStartTAB(tabIndex),
EFFECTIVE_END_DATE = l_effEndTAB(tabIndex)
where rowid = l_rowIDTAB(tabIndex);
else -- Donot update WHO columns
forall tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST
update WF_USER_ROLE_ASSIGNMENTS
set START_DATE = l_StartDate,
END_DATE = l_EndDate,
EFFECTIVE_START_DATE = l_effStartTAB(tabIndex),
EFFECTIVE_END_DATE = l_effEndTAB(tabIndex)
where rowid = l_rowIDTAB(tabIndex);
'. Updated existing assignments.');
INSERT INTO WF_USER_ROLE_ASSIGNMENTS
( USER_NAME,
ROLE_NAME,
RELATIONSHIP_ID,
ASSIGNING_ROLE,
START_DATE,
END_DATE,
ROLE_START_DATE,
ROLE_END_DATE,
USER_START_DATE,
USER_END_DATE,
ASSIGNING_ROLE_START_DATE,
ASSIGNING_ROLE_END_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PARTITION_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
USER_ORIG_SYSTEM,
USER_ORIG_SYSTEM_ID,
ROLE_ORIG_SYSTEM,
ROLE_ORIG_SYSTEM_ID,
ASSIGNMENT_REASON)
values
( l_UserName,
l_RoleName,
-1,
l_RoleName,
l_StartDate,
l_EndDate,
l_RoleStartDate,
l_RoleEndDate,
l_UserStartDate,
l_UserEndDate,
l_RoleStartDate,
l_RoleEndDate,
nvl(l_CreatedBy,WFA_SEC.User_ID),
nvl(l_CreationDate,SYSDATE),
nvl(l_LastUpdatedBy, WFA_SEC.User_ID),
nvl(l_LastUpdateDate,SYSDATE),
nvl(l_LastUpdateLogin,WFA_SEC.LOGIN_ID),
l_partitionID,
l_effStartDate,
l_effEndDate,
l_UserOrigSystem,
l_UserOrigSystemId,
l_RoleOrigSystem,
l_RoleOrigSystemId,
l_assignmentReason);
'There are no superiors, updates are limited to this '||
'user/role relationship.');
SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID,
START_DATE, EXPIRATION_DATE
INTO l_roleOrigSystem,l_roleOrigSystemID,
l_SupStartDate, l_SupEndDate
FROM WF_LOCAL_ROLES
WHERE NAME=l_superiors(l_count).SUPER_NAME
AND rownum<2;
INSERT INTO WF_USER_ROLE_ASSIGNMENTS
( USER_NAME,
ROLE_NAME,
RELATIONSHIP_ID,
ASSIGNING_ROLE,
START_DATE,
END_DATE,
USER_START_DATE,
USER_END_DATE,
ROLE_START_DATE,
ROLE_END_DATE,
ASSIGNING_ROLE_START_DATE,
ASSIGNING_ROLE_END_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PARTITION_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE ,
USER_ORIG_SYSTEM,
USER_ORIG_SYSTEM_ID,
ROLE_ORIG_SYSTEM,
ROLE_ORIG_SYSTEM_ID
)
values
( l_UserName,
l_superiors(l_count).SUPER_NAME,
l_superiors(l_count).RELATIONSHIP_ID,
l_RoleName,
l_StartDate,
l_EndDate,
l_UserStartDate,
l_UserEndDate,
l_SupStartDate,
l_SupEndDate,
l_RoleStartDate,
l_RoleEndDate,
nvl(l_CreatedBy,WFA_SEC.User_ID),
nvl(l_CreationDate,SYSDATE),
nvl(l_LastUpdatedBy, WFA_SEC.User_ID),
nvl(l_LastUpdateDate,SYSDATE),
nvl(l_LastUpdateLogin,WFA_SEC.LOGIN_ID),
l_partitionID,
l_EffStartDate,
l_EffEndDate,
l_UserOrigSystem,
l_UserOrigSystemID,
l_RoleOrigSystem,
l_RoleOrigSystemID
);
if (OverWrite and UpdateWho) then
--allow update of creation_date and created_by
UPDATE WF_USER_ROLE_ASSIGNMENTS
SET END_DATE = l_EndDate,
START_DATE = l_StartDate,
USER_START_DATE = l_UserStartDate,
USER_END_DATE = l_UserEndDate,
ROLE_START_DATE = l_SupStartDate,
ROLE_END_DATE = l_SupEndDate,
ASSIGNING_ROLE_START_DATE = l_RoleStartDate,
ASSIGNING_ROLE_END_DATE = l_RoleEndDate,
EFFECTIVE_START_DATE = l_EffStartDate,
EFFECTIVE_END_DATE = l_EffEndDate,
LAST_UPDATED_BY = nvl(l_LastUpdatedBy, WFA_SEC.User_ID),
LAST_UPDATE_DATE = nvl(l_LastUpdateDate,SYSDATE),
LAST_UPDATE_LOGIN = nvl(l_LastUpdateLogin,WFA_SEC.LOGIN_ID),
CREATED_BY = nvl(l_CreatedBy,CREATED_BY),
CREATION_DATE = nvl(l_CreationDate, CREATION_DATE)
WHERE RELATIONSHIP_ID = l_superiors(l_count).RELATIONSHIP_ID
AND USER_NAME = l_UserName
AND ROLE_NAME = l_superiors(l_count).SUPER_NAME
AND ASSIGNING_ROLE = l_RoleName;
elsif UpdateWho then
UPDATE WF_USER_ROLE_ASSIGNMENTS
SET END_DATE = l_EndDate,
START_DATE = l_StartDate,
USER_START_DATE = l_UserStartDate,
USER_END_DATE = l_UserEndDate,
ROLE_START_DATE = l_SupStartDate,
ROLE_END_DATE = l_SupEndDate,
ASSIGNING_ROLE_START_DATE = l_RoleStartDate,
ASSIGNING_ROLE_END_DATE = l_RoleEndDate,
EFFECTIVE_START_DATE = l_EffStartDate,
EFFECTIVE_END_DATE = l_EffEndDate,
LAST_UPDATED_BY = nvl(l_LastUpdatedBy, WFA_SEC.User_ID),
LAST_UPDATE_DATE = nvl(l_LastUpdateDate,SYSDATE),
LAST_UPDATE_LOGIN = nvl(l_LastUpdateLogin,WFA_SEC.LOGIN_ID)
WHERE RELATIONSHIP_ID = l_superiors(l_count).RELATIONSHIP_ID
AND USER_NAME = l_UserName
AND ROLE_NAME = l_superiors(l_count).SUPER_NAME
AND ASSIGNING_ROLE = l_RoleName;
UPDATE WF_USER_ROLE_ASSIGNMENTS
SET END_DATE = l_EndDate,
START_DATE = l_StartDate,
USER_START_DATE = l_UserStartDate,
USER_END_DATE = l_UserEndDate,
ROLE_START_DATE = l_SupStartDate,
ROLE_END_DATE = l_SupEndDate,
ASSIGNING_ROLE_START_DATE = l_RoleStartDate,
ASSIGNING_ROLE_END_DATE = l_RoleEndDate,
EFFECTIVE_START_DATE = l_EffStartDate,
EFFECTIVE_END_DATE = l_EffEndDate
WHERE RELATIONSHIP_ID = l_superiors(l_count).RELATIONSHIP_ID
AND USER_NAME = l_UserName
AND ROLE_NAME = l_superiors(l_count).SUPER_NAME
AND ASSIGNING_ROLE = l_RoleName;
last_updated_by=>l_LastUpdatedBy,
last_update_date=>l_LastUpdateDate,
last_update_login=>l_LastUpdateLogin,
assignment_type=>'I');
last_updated_by=>l_LastUpdatedBy,
last_update_date=>l_LastUpdateDate,
last_update_login=>l_LastUpdateLogin,
assignment_type=>'X',
updateWho=>UpdateWho);
select WRH1.RELATIONSHIP_ID REL_ID
from WF_ROLE_HIERARCHIES WRH1
connect by prior SUPER_NAME = SUB_NAME
start with SUB_NAME = p_subName
minus
select WRH2.RELATIONSHIP_ID REL_ID
from WF_ROLE_HIERARCHIES WRH2
where ENABLED_FLAG = 'Y'
connect by prior SUPER_NAME = SUB_NAME
and prior ENABLED_FLAG = 'Y'
start with SUB_NAME = p_subName;
select SUPER_NAME, RELATIONSHIP_ID
from WF_ROLE_HIERARCHIES
where ENABLED_FLAG = 'Y'
connect by prior SUPER_NAME = SUB_NAME
and prior ENABLED_FLAG = 'Y'
start with SUB_NAME = p_subName;
select SUPER_NAME, SUB_NAME, ENABLED_FLAG
into l_superName, l_subName, l_enabledFlag
from WF_ROLE_HIERARCHIES
where RELATIONSHIP_ID = p_relationship_id;
update WF_ROLE_HIERARCHIES
set PROPAGATE_DATE = p_propagateDate
where RELATIONSHIP_ID = p_relationship_id
and SUPER_NAME = l_superName
and SUB_NAME = l_subName;
select SUB_NAME
bulk collect into l_assignTAB
from WF_ROLE_HIERARCHIES
connect by prior SUB_NAME = SUPER_NAME
start with SUPER_NAME = l_superName;
l_relIDTAB.DELETE; --Truncate the PL/SQL Table.
delete from WF_USER_ROLE_ASSIGNMENTS
where RELATIONSHIP_ID = l_relIDTAB(hitIND)
and ASSIGNING_ROLE = l_assignTAB(subIND)
returning USER_NAME, ROLE_NAME
bulk collect into l_userTAB, l_roleTAB;
select min(effective_start_Date),max(effective_end_date)
into l_effStartDate, l_effEndDate
from wf_user_role_assignments
where user_name= l_userTAB(userIND)
and role_name = l_roleTAB(userIND);
delete from
WF_LOCAL_USER_ROLES
where USER_NAME = l_userTAB(userIND)
and ROLE_NAME = l_roleTAB(userIND);
update WF_LOCAL_USER_ROLES
set ASSIGNMENT_TYPE = l_assignmentType,
EFFECTIVE_START_DATE= l_effStartDate,
EFFECTIVE_END_DATE = l_effEndDate,
LAST_UPDATED_BY = WFA_SEC.user_id,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = WFA_SEC.login_id
where USER_NAME = l_userTAB(userIND)
and ROLE_NAME = l_roleTAB(userIND);
commit; --Commiting this batch of updates.
select ORIG_SYSTEM, ORIG_SYSTEM_ID, PARTITION_ID,
START_DATE, EXPIRATION_DATE
into l_rorigSysTAB(roleIND), l_rorigSysIDTAB(roleIND),
l_rpartIDTAB(roleIND), l_rStartDateTAB(roleIND),
l_rEndDateTAB(roleIND)
from WF_LOCAL_ROLES
where NAME = l_roleTAB(roleIND);
select USER_NAME, ASSIGNING_ROLE, START_DATE, END_DATE,
USER_START_DATE, USER_END_DATE,
ASSIGNING_ROLE_START_DATE, ASSIGNING_ROLE_END_DATE,
PARTITION_ID, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID
bulk collect into l_userTAB, l_assignTAB, l_startDateTAB, l_endDateTAB,
l_uStartDateTAB, l_uEndDateTAB, l_aStartDateTAB,
l_aEndDateTAB, l_apartIDTAB, l_uorigSysTAB, l_uorigSysIDTAB
from WF_USER_ROLE_ASSIGNMENTS
where ROLE_NAME = l_subName;
insert into WF_USER_ROLE_ASSIGNMENTS (USER_NAME,
ROLE_NAME,
RELATIONSHIP_ID,
ASSIGNING_ROLE,
START_DATE,
END_DATE,
USER_START_DATE,
USER_END_DATE,
ROLE_START_DATE,
ROLE_END_DATE,
ASSIGNING_ROLE_START_DATE,
ASSIGNING_ROLE_END_DATE,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
USER_ORIG_SYSTEM,
USER_ORIG_SYSTEM_ID,
ROLE_ORIG_SYSTEM,
ROLE_ORIG_SYSTEM_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PARTITION_ID) values
(
l_userTAB(userIND),
l_roleTAB(roleIND),
l_relIDTAB(roleIND),
l_assignTAB(userIND),
trunc(l_startDateTAB(userIND)),
trunc(l_endDateTAB(userIND)),
trunc(l_uStartDateTAB(userIND)),
trunc(l_uEndDateTAB(userIND)),
trunc(l_rStartDateTAB(roleIND)),
trunc(l_rEndDateTAB(roleIND)),
trunc(l_aStartDateTAB(userIND)),
trunc(l_aEndDateTAB(userIND)),
l_effStartDate,
l_effEndDate,
l_uorigSysTAB(userIND),
l_uorigSysIDTAB(userIND),
l_rorigSysTAB(roleIND),
l_rorigSysIDTAB(roleIND),
WFA_SEC.user_id,
sysdate,
WFA_SEC.user_id,
sysdate,
WFA_SEC.login_id,
l_apartIDTAB(userIND));
update WF_USER_ROLE_ASSIGNMENTS
set START_DATE = trunc(l_startDateTAB(userIND)),
END_DATE = trunc(l_endDateTAB(userIND)),
USER_START_DATE = trunc(l_uStartDateTAB(userIND)),
USER_END_DATE = trunc(l_uEndDateTAB(userIND)),
ROLE_START_DATE = trunc(l_rStartDateTAB(roleIND)),
ROLE_END_DATE = trunc(l_rEndDateTAB(roleIND)),
ASSIGNING_ROLE_START_DATE = trunc(l_aStartDateTAB(userIND)),
ASSIGNING_ROLE_END_DATE = trunc(l_aEndDateTAB(userIND)),
EFFECTIVE_START_DATE = l_effStartDate,
EFFECTIVE_END_DATE = l_effEndDate,
LAST_UPDATED_BY = WFA_SEC.user_id,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = WFA_SEC.login_id
where USER_NAME = l_userTAB(userIND)
and ROLE_NAME = l_roleTAB(roleIND)
and RELATIONSHIP_ID = l_relIDTAB(roleIND)
and ASSIGNING_ROLE = l_assignTAB(userIND);
select USER_NAME, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID,
OWNER_TAG, USER_START_DATE, USER_END_DATE, START_DATE,
EXPIRATION_DATE
bulk collect into l_userTAB, l_uorigSysTAB, l_uorigSysIDTAB,
l_ownerTAB, l_uStartDateTAB, l_uEndDateTAB,
l_startDateTAB, l_endDateTAB
from WF_LOCAL_USER_ROLES
where ROLE_NAME = l_subName;
select min(effective_start_date),
max(effective_end_date)
into l_effStartDate, l_effEndDate
from wf_user_role_Assignments
where user_name=l_userTAB(userIND)
and role_name = l_roleTAB(roleIND);
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,
USER_START_DATE,
USER_END_DATE,
ROLE_START_DATE,
ROLE_END_DATE,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
SECURITY_GROUP_ID,
PARTITION_ID,
OWNER_TAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ASSIGNMENT_TYPE) values
(l_userTAB(userIND),
l_roleTAB(roleIND),
l_uorigSysTAB(userIND),
l_uorigSysIDTAB(userIND),
l_rorigSysTAB(roleIND),
l_rorigSysIDTAB(roleIND),
l_startDateTAB(userIND),
l_endDateTAB(userIND),
trunc(l_uStartDateTAB(userIND)),
trunc(l_uEndDateTAB(userIND)),
trunc(l_rStartDateTAB(roleIND)),
trunc(l_rEndDateTAB(roleIND)),
l_effStartDate,
l_effEndDate,
NULL,
l_rpartIDTAB(roleIND),
l_ownerTAB(userIND),
WFA_SEC.user_id,
sysdate,
WFA_SEC.user_id,
sysdate,
WFA_SEC.login_id,
l_AssignmentType);
SELECT min(start_date)
INTO l_startDateTAB(userIND)
FROM WF_USER_ROLE_ASSIGNMENTS_V
WHERE USER_NAME = l_userTAB(userIND)
AND ROLE_NAME = l_roleTAB(roleIND);
SELECT max(end_date)
INTO l_endDateTAB(userIND)
FROM WF_USER_ROLE_ASSIGNMENTS_V
WHERE USER_NAME = l_userTAB(userIND)
AND ROLE_NAME = l_roleTAB(roleIND);
update WF_LOCAL_USER_ROLES
set START_DATE = trunc(l_startDateTAB(userIND)),
EXPIRATION_DATE = trunc(l_endDateTAB(userIND)),
USER_START_DATE = trunc(l_uStartDateTAB(userIND)),
USER_END_DATE = trunc(l_uEndDateTAB(userIND)),
ROLE_START_DATE = trunc(l_rStartDateTAB(roleIND)),
ROLE_END_DATE = trunc(l_rEndDateTAB(roleIND)),
EFFECTIVE_START_DATE = l_effStartDate,
EFFECTIVE_END_DATE = l_effEndDate,
LAST_UPDATED_BY = WFA_SEC.user_id,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = WFA_SEC.login_id,
ASSIGNMENT_TYPE = l_AssignmentType
where USER_NAME = l_userTAB(userIND)
and ROLE_NAME = l_roleTAB(roleIND)
and USER_ORIG_SYSTEM = l_uorigSysTAB(userIND)
and USER_ORIG_SYSTEM_ID = l_uorigSysIDTAB(userIND)
and ROLE_ORIG_SYSTEM = l_rorigSysTAB(roleIND)
and ROLE_ORIG_SYSTEM_ID = l_rorigSysIDTAB(roleIND);
SELECT relationship_id
BULK COLLECT INTO relIDTAB
FROM WF_ROLE_HIERARCHIES
WHERE (PROPAGATE_DATE is NULL or
((PROPAGATE_DATE is NOT NULL) and
(PROPAGATE_DATE < LAST_UPDATE_DATE)));
l_LastUpdatedBy NUMBER ;
l_LastUpdateDate DATE;
l_LastUpdateLogin NUMBER ;
select ROWID, ROLE_NAME, USER_NAME,ASSIGNING_ROLE, START_DATE, END_DATE,
ROLE_START_DATE, ROLE_END_DATE,USER_START_DATE, USER_END_DATE,
ASSIGNING_ROLE_START_DATE, ASSIGNING_ROLE_END_DATE, EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
from WF_USER_ROLE_ASSIGNMENTS
where (ROLE_NAME = c_roleName or ASSIGNING_ROLE = c_roleName)
and USER_NAME=c_userName;
select ROWID, ROLE_NAME, USER_NAME,ASSIGNING_ROLE, START_DATE, END_DATE,
ROLE_START_DATE, ROLE_END_DATE,USER_START_DATE, USER_END_DATE,
ASSIGNING_ROLE_START_DATE, ASSIGNING_ROLE_END_DATE, EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE ,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
from WF_USER_ROLE_ASSIGNMENTS
where USER_NAME=c_userName;
select ROWID, ROLE_NAME, USER_NAME,ASSIGNING_ROLE, START_DATE, END_DATE,
ROLE_START_DATE, ROLE_END_DATE,USER_START_DATE, USER_END_DATE,
ASSIGNING_ROLE_START_DATE, ASSIGNING_ROLE_END_DATE, EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
from WF_USER_ROLE_ASSIGNMENTS
where ROLE_NAME = c_roleName
or ASSIGNING_ROLE = c_roleName;
l_LastUpdatedBy := to_number(p_event.getValueForParameter(
'LAST_UPDATED_BY'), WF_CORE.canonical_number_mask);
l_LastUpdateDate := nvl(to_date(p_event.getValueForParameter(
'LAST_UPDATE_DATE'),
WF_CORE.canonical_date_mask),SYSDATE);
l_LastUpdateLogin := to_number(p_event.getValueForParameter(
'LAST_UPDATE_LOGIN'), WF_CORE.canonical_number_mask);
l_lastUpdLogTAB(tabIndex):=nvl(l_lastUpdateLogin,WFA_SEC.Login_ID);
l_lastUpdByTAB(tabIndex):=nvl(l_lastUpdatedBy, WFA_SEC.User_ID);
l_lastUpdDtTAB(tabIndex):=nvl(l_lastUpdateDate,SYSDATE);
update WF_USER_ROLE_ASSIGNMENTS
set ROLE_START_DATE = l_roleStartTAB(tabIndex),
ROLE_END_DATE = l_roleEndTAB(tabIndex),
USER_START_DATE = l_userStartTAB(tabIndex),
USER_END_DATE = l_userEndTAB(tabIndex),
EFFECTIVE_START_DATE = l_effStartTAB(tabIndex),
EFFECTIVE_END_DATE = l_effEndTAB(tabIndex),
START_DATE=l_startTAB(tabIndex),
END_DATE=l_endTAB(tabIndex),
ASSIGNING_ROLE_START_DATE = l_asgStartTAB(tabIndex),
ASSIGNING_ROLE_END_DATE = l_asgEndTAB(tabIndex),
LAST_UPDATED_BY = l_lastUpdByTAB(tabIndex),
LAST_UPDATE_DATE = l_lastUpdDtTAB(tabIndex),
LAST_UPDATE_LOGIN = l_lastUpdLogTAB(tabIndex)
where rowid = l_rowIDTAB(tabIndex);
l_LastUpdatedBy NUMBER;
l_LastUpdateDate DATE;
l_LastUpdateLogin NUMBER;
select /*+ use_concat */ ROWID, ROLE_NAME, USER_NAME, ROLE_START_DATE,
ROLE_END_DATE, USER_START_DATE, USER_END_DATE, EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE, START_DATE, END_DATE, RELATIONSHIP_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY
from WF_USER_ROLE_ASSIGNMENTS
where USER_NAME=c_userName
and ROLE_NAME in (select ROLE_NAME
from WF_USER_ROLE_ASSIGNMENTS
where ASSIGNING_ROLE = c_roleName
and USER_NAME = c_userName)
order by ROLE_NAME, USER_NAME;
select ROWID, ROLE_NAME, USER_NAME, ROLE_START_DATE,
ROLE_END_DATE, USER_START_DATE, USER_END_DATE, EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE, START_DATE, END_DATE, RELATIONSHIP_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE, CREATED_BY
from WF_USER_ROLE_ASSIGNMENTS
where USER_NAME=c_userName
order by ROLE_NAME, USER_NAME;
select ura.ROWID, ura.ROLE_NAME, ura.USER_NAME, ura.ROLE_START_DATE,
ura.ROLE_END_DATE, ura.USER_START_DATE, ura.USER_END_DATE, ura.EFFECTIVE_START_DATE,
ura.EFFECTIVE_END_DATE, ura.START_DATE, ura.END_DATE, ura.RELATIONSHIP_ID,
ura.LAST_UPDATE_DATE, ura.LAST_UPDATED_BY, ura.LAST_UPDATE_LOGIN,
ura.CREATION_DATE, ura.CREATED_BY
from WF_USER_ROLE_ASSIGNMENTS ura,
WF_USER_ROLE_ASSIGNMENTS ura2
where ura2.ASSIGNING_ROLE= c_roleName
and ura2.ROLE_NAME= ura.ROLE_NAME
and ura2.USER_NAME = ura.USER_NAME
order by ura.ROLE_NAME, ura.USER_NAME;
/* l_LastUpdatedBy := to_number(p_event.getValueForParameter(
'LAST_UPDATED_BY'));
l_LastUpdateDate := nvl(to_date(p_event.getValueForParameter(
'LAST_UPDATE_DATE'),
WF_CORE.canonical_date_mask),SYSDATE);
l_LastUpdateLogin := to_number(p_event.getValueForParameter(
'LAST_UPDATE_LOGIN'));
UPDATE WF_LOCAL_USER_ROLES
SET ASSIGNMENT_TYPE = l_assignmentType
WHERE ROWID = l_rowid;
-- before inserting, check whether the summarytable has
-- grown too large
if sumTabIndex >= g_maxRows then
--limit reached for summary table, so perform
--the bulk update and clear off the table.
--We need to perform the bulk update here in addition to
--bulk update after exit from the loop, so that clearing
--the summary table will not lose user/role effective date
--information when duplicate user/role
--combinations are spread across multiple groups
if (OverWrite) then
--allow update of created_by and creation_date
forall destTabIndex in l_roleDestTab.FIRST..l_roleDestTab.LAST
UPDATE WF_LOCAL_USER_ROLES wur
SET
ROLE_START_DATE = l_roleStartDestTAB(destTabIndex),
ROLE_END_DATE = l_roleEndDestTAB(destTabIndex),
USER_START_DATE = l_userStartDestTAB(destTabIndex),
USER_END_DATE = l_userEndDestTAB(destTabIndex),
EFFECTIVE_START_DATE = l_effStartDestTAB(destTabIndex),
EFFECTIVE_END_DATE = l_effEndDestTAB(destTabIndex),
START_DATE = l_startDestTAB(destTabIndex),
EXPIRATION_DATE = l_endDestTAB(destTabIndex),
ASSIGNMENT_TYPE = l_assignTAB(destTabIndex),
LAST_UPDATED_BY = l_lastUpdByDestTAB(destTabIndex),
LAST_UPDATE_LOGIN = l_lastUpdLogDestTAB(destTabIndex),
CREATION_DATE = nvl(l_creatDtSrcTAB(destTabIndex),CREATION_DATE),
CREATED_BY= nvl(l_creatBySrcTAB(destTabIndex), CREATED_BY),
LAST_UPDATE_DATE = l_lastUpdDtDestTAB(destTabIndex)
WHERE ROLE_NAME = l_roleDestTAB(destTabIndex)
AND USER_NAME = l_userDestTAB(destTabIndex);
UPDATE WF_LOCAL_USER_ROLES wur
SET
ROLE_START_DATE = l_roleStartDestTAB(destTabIndex),
ROLE_END_DATE = l_roleEndDestTAB(destTabIndex),
USER_START_DATE = l_userStartDestTAB(destTabIndex),
USER_END_DATE = l_userEndDestTAB(destTabIndex),
EFFECTIVE_START_DATE = l_effStartDestTAB(destTabIndex),
EFFECTIVE_END_DATE = l_effEndDestTAB(destTabIndex),
START_DATE = l_startDestTAB(destTabIndex),
EXPIRATION_DATE = l_endDestTAB(destTabIndex),
ASSIGNMENT_TYPE = l_assignTAB(destTabIndex),
LAST_UPDATED_BY = l_lastUpdByDestTAB(destTabIndex),
LAST_UPDATE_LOGIN = l_lastUpdLogDestTAB(destTabIndex),
LAST_UPDATE_DATE = l_lastUpdDtDestTAB(destTabIndex)
WHERE ROLE_NAME = l_roleDestTAB(destTabIndex)
AND USER_NAME = l_userDestTAB(destTabIndex);
l_roleStartDestTAB.DELETE;
l_roleEndDestTAB.DELETE;
l_userStartDestTAB.DELETE;
l_userEndDestTAB.DELETE;
l_effStartDestTAB.DELETE;
l_effEndDestTAB.DELETE;
l_assignTAB.DELETE;
l_roleDestTAB.DELETE;
l_userDestTAB.DELETE;
l_startDestTAB.DELETE;
l_endDestTAB.DELETE;
l_lastUpdDtDestTAB.DELETE;
l_lastUpdByDestTAB.DELETE;
l_lastUpdLogDestTAB.DELETE;
l_creatDtDestTAB.DELETE;
l_creatByDestTAB.DELETE;
--now perform the insert
sumTabIndex := sumTabIndex + 1;
--update effective_dates
if l_effStartSrcTAB(tabIndex) <
l_effStartDestTAB(sumTabIndex) then
l_effStartDestTAB(sumTabIndex):= l_effStartSrcTAB(tabIndex);
--update the assignment_Type to Both
if (((l_AssignTAB(sumTabIndex) = 'D') and
(l_relIDTAB(tabIndex) <> -1)) or
((l_AssignTAB(sumTabIndex) = 'I') and
(l_relIDTAB(tabIndex) = -1))) then
l_AssignTAB(sumTabIndex) := 'B';
UPDATE WF_LOCAL_USER_ROLES wur
SET
ROLE_START_DATE = l_roleStartDestTAB(tabIndex),
ROLE_END_DATE = l_roleEndDestTAB(tabIndex),
USER_START_DATE = l_userStartDestTAB(tabIndex),
USER_END_DATE = l_userEndDestTAB(tabIndex),
EFFECTIVE_START_DATE = l_effStartDestTAB(tabIndex),
EFFECTIVE_END_DATE = l_effEndDestTAB(tabIndex),
START_DATE = l_startDestTAB(tabIndex),
EXPIRATION_DATE = l_endDestTAB(tabIndex),
ASSIGNMENT_TYPE = l_assignTAB(tabIndex),
LAST_UPDATED_BY = l_lastUpdByDestTAB(tabIndex),
LAST_UPDATE_LOGIN = l_lastUpdLogDestTAB(tabIndex),
LAST_UPDATE_DATE = l_lastUpdDtDestTAB(tabIndex),
CREATION_DATE = nvl(l_creatDtSrcTAB(tabIndex),CREATION_DATE),
CREATED_BY= nvl(l_creatBySrcTAB(tabIndex), CREATED_BY)
WHERE ROLE_NAME = l_roleDestTAB(tabIndex)
AND USER_NAME = l_userDestTAB(tabIndex);
UPDATE WF_LOCAL_USER_ROLES wur
SET
ROLE_START_DATE = l_roleStartDestTAB(tabIndex),
ROLE_END_DATE = l_roleEndDestTAB(tabIndex),
USER_START_DATE = l_userStartDestTAB(tabIndex),
USER_END_DATE = l_userEndDestTAB(tabIndex),
EFFECTIVE_START_DATE = l_effStartDestTAB(tabIndex),
EFFECTIVE_END_DATE = l_effEndDestTAB(tabIndex),
START_DATE = l_startDestTAB(tabIndex),
EXPIRATION_DATE = l_endDestTAB(tabIndex),
ASSIGNMENT_TYPE = l_assignTAB(tabIndex),
LAST_UPDATED_BY = l_lastUpdByDestTAB(tabIndex),
LAST_UPDATE_LOGIN = l_lastUpdLogDestTAB(tabIndex),
LAST_UPDATE_DATE = l_lastUpdDtDestTAB(tabIndex)
WHERE ROLE_NAME = l_roleDestTAB(tabIndex)
AND USER_NAME = l_userDestTAB(tabIndex);
SELECT RELATIONSHIP_ID, SUB_NAME, SUPER_NAME,
ENABLED_FLAG
BULK COLLECT INTO p_superiors
FROM WF_ROLE_HIERARCHIES
WHERE ENABLED_FLAG = 'Y'
CONNECT BY PRIOR SUPER_NAME = SUB_NAME
AND ENABLED_FLAG = 'Y'
START WITH SUB_NAME = upper(p_name);
SELECT RELATIONSHIP_ID, SUB_NAME, SUPER_NAME,
ENABLED_FLAG
BULK COLLECT INTO p_subordinates
FROM WF_ROLE_HIERARCHIES
WHERE ENABLED_FLAG = 'Y'
CONNECT BY PRIOR SUB_NAME = SUPER_NAME
AND ENABLED_FLAG = 'Y'
START WITH SUPER_NAME = upper(p_name);
SELECT RELATIONSHIP_ID, SUB_NAME, SUPER_NAME,
ENABLED_FLAG
BULK COLLECT INTO relIDTab, subTab, superTab, enabledTab
FROM WF_ROLE_HIERARCHIES
WHERE ENABLED_FLAG = 'Y'
CONNECT BY PRIOR SUPER_NAME = SUB_NAME
AND PRIOR ENABLED_FLAG = 'Y'
START WITH SUB_NAME = upper(p_name);
SELECT RELATIONSHIP_ID, SUB_NAME, SUPER_NAME,
ENABLED_FLAG
BULK COLLECT INTO relIDTab, subTab, superTab, enabledTab
FROM WF_ROLE_HIERARCHIES
WHERE ENABLED_FLAG = 'Y'
CONNECT BY PRIOR SUB_NAME = SUPER_NAME
AND PRIOR ENABLED_FLAG = 'Y'
START WITH SUPER_NAME = upper(p_name);
SELECT RELATIONSHIP_ID, SUB_NAME, SUPER_NAME,
ENABLED_FLAG
BULK COLLECT INTO relIDTab, subTab, superTab, enabledTab
FROM WF_ROLE_HIERARCHIES
CONNECT BY PRIOR SUPER_NAME = SUB_NAME
START WITH SUB_NAME = upper(p_name);
SELECT RELATIONSHIP_ID, SUB_NAME, SUPER_NAME,
ENABLED_FLAG
BULK COLLECT INTO relIDTab, subTab, superTab, enabledTab
FROM WF_ROLE_HIERARCHIES
CONNECT BY PRIOR SUB_NAME = SUPER_NAME
START WITH SUPER_NAME = upper(p_name);
SELECT RELATIONSHIP_ID, SUB_NAME, SUPER_NAME,
ENABLED_FLAG
BULK COLLECT INTO p_superiors
FROM WF_ROLE_HIERARCHIES
CONNECT BY PRIOR SUPER_NAME = SUB_NAME
START WITH SUB_NAME = upper(p_name);
SELECT RELATIONSHIP_ID, SUB_NAME, SUPER_NAME,
ENABLED_FLAG
BULK COLLECT INTO p_subordinates
FROM WF_ROLE_HIERARCHIES
CONNECT BY PRIOR SUB_NAME = SUPER_NAME
START WITH SUPER_NAME = upper(p_name);
l_updateTime DATE;
SELECT ORIG_SYSTEM, PARTITION_ID
INTO l_sub_origSys, l_partitionID
FROM WF_LOCAL_ROLES
WHERE NAME = p_sub_name;
SELECT ORIG_SYSTEM, PARTITION_ID
INTO l_super_origSys, l_superPartitionID
FROM WF_LOCAL_ROLES
WHERE NAME = p_super_name;
select WF_ROLE_HIERARCHIES_S.NEXTVAL
into l_RelationshipID
from dual;
g_modulePkg||'.AddRelationship.Insert',
'Inserting record');
insert into WF_ROLE_HIERARCHIES
(RELATIONSHIP_ID,
SUB_NAME,
SUPER_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ENABLED_FLAG,
SECURITY_GROUP_ID,
PARTITION_ID,
SUPERIOR_PARTITION_ID)
values
( l_RelationshipID,
p_sub_name,
p_super_name,
WFA_SEC.USER_ID,
sysdate,
WFA_SEC.USER_ID,
sysdate,
WFA_SEC.LOGIN_ID,
l_enabled,
WFA_SEC.SECURITY_GROUP_ID,
l_partitionID,
l_superPartitionID);
'.AddRelationship.Insert.DUP_VAL_ON_INDEX',
'Updating expired relationship');
update WF_ROLE_HIERARCHIES set
LAST_UPDATED_BY = WFA_SEC.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN= WFA_SEC.USER_ID,
ENABLED_FLAG = 'Y',
SECURITY_GROUP_ID = WFA_SEC.SECURITY_GROUP_ID
where SUB_NAME = p_sub_name
and SUPER_NAME = p_super_name
and ENABLED_FLAG = 'N'
returning RELATIONSHIP_ID into l_relationshipID;
'.AddRelationship.Insert.DUP_VAL_ON_INDEX.NoUpdate',
'Active relationship exists, raising WFDS_DUP_HIERARCHY');
WF_LOCAL_SYNCH.DeleteCache(p_sub_name);
WF_LOCAL_SYNCH.DeleteCache(p_super_name);
g_modulePkg||'.ExpireRelationship.Update',
'Updating record');
update WF_ROLE_HIERARCHIES
set LAST_UPDATED_BY = WFA_SEC.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN= WFA_SEC.USER_ID,
ENABLED_FLAG = 'N'
where SUB_NAME = p_sub_name
and SUPER_NAME = p_super_name
and ENABLED_FLAG = 'Y'
returning RELATIONSHIP_ID into l_relationshipID;
'.ExpireRelationship.Update.NoActiveRelationship',
'No Active relationship exists, '||
'raising WFDS_NO_HIERARCHY');
p_eventName=>'oracle.apps.fnd.wf.ds.roleHierarchy.relationshipUpdated',
p_relationshipID=>l_RelationshipID,
p_superName=>p_super_name,
p_subName=>p_sub_name,
p_defer=>p_defer_mode);
l_updateTime DATE;
select to_date(text, WF_CORE.canonical_date_mask)
into l_UpdateTime
from WF_RESOURCES
where TYPE='WFTKN'
and NAME = 'WFDS_HIERARCHY_UPD'
and LANGUAGE = 'US';
if ((p_timeStamp is NOT NULL) and (l_updateTime is NOT NULL) and
(p_timeStamp = l_updateTime) and g_trustTimeStamp = l_updateTime) then
return TRUE;
update WF_RESOURCES
set text = to_char(g_trustTimeStamp, WF_CORE.canonical_date_mask)
where name = 'WFDS_HIERARCHY_UPD';
insert into WF_RESOURCES (TYPE,
NAME,
LANGUAGE,
SOURCE_LANG,
ID,
TEXT,
PROTECT_LEVEL,
CUSTOM_LEVEL) values
('WFTKN',
'WFDS_HIERARCHY_UPD',
'US',
'US',
0,
to_char(g_trustTimeStamp,
WF_CORE.canonical_date_mask),
0,
0);
select enabled_flag,sub_name,super_name
into l_enabled, l_subName , l_superName
from wf_role_hierarchies
where relationship_id=p_relationshipID;
Delete from WF_ROLE_HIERARCHIES
where relationship_id=p_relationshipID;