[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ITEM_TYPE, ITEM_KEY
from WF_ITEMS
where OWNER_ROLE = l_oldname;
select ITEM_TYPE, ITEM_KEY
from WF_ITEMS
where OWNER_ROLE = l_oldname
and END_DATE IS NULL;
select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY
from WF_ITEM_ACTIVITY_STATUSES
where ASSIGNED_USER = l_oldname;
select WIAS.ITEM_TYPE, WIAS.ITEM_KEY, WIAS.PROCESS_ACTIVITY
from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_ITEMS WI
where WIAS.ITEM_TYPE = wi.item_type
and WIAS.ITEM_KEY = wi.item_key
and WI.END_DATE IS NULL
and WIAS.ASSIGNED_USER = l_oldname;
select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY
from WF_ITEM_ACTIVITY_STATUSES_H
where ASSIGNED_USER = l_oldname;
select WIASH.ITEM_TYPE, WIASH.ITEM_KEY, WIASH.PROCESS_ACTIVITY
from WF_ITEM_ACTIVITY_STATUSES_H WIASH, WF_ITEMS WI
where WIASH.ITEM_TYPE = wi.item_type
and WIASH.ITEM_KEY = wi.item_key
and WI.END_DATE IS NULL
and WIASH.ASSIGNED_USER = l_oldname;
select NOTIFICATION_ID
from WF_NOTIFICATIONS
where RECIPIENT_ROLE = l_oldname
or ORIGINAL_RECIPIENT = l_oldname
or more_info_role = l_oldname
or from_role = l_oldname
or responder = l_oldname;
select NOTIFICATION_ID
from WF_NOTIFICATIONS WN
where (WN.RECIPIENT_ROLE = l_oldname
or WN.ORIGINAL_RECIPIENT = l_oldname
or WN.MORE_INFO_ROLE = l_oldname
or WN.FROM_ROLE = l_oldname
or WN.RESPONDER = l_oldname)
and (exists (select '1'
from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_ITEMS WI
where WIAS.NOTIFICATION_ID = WN.NOTIFICATION_ID
and WIAS.NOTIFICATION_ID is not null
and WIAS.ITEM_TYPE = WI.ITEM_TYPE
and WIAS.ITEM_KEY = WI.ITEM_KEY
and WI.END_DATE IS NULL)
or exists (select '1'
from WF_ITEM_ACTIVITY_STATUSES_H WIASH, WF_ITEMS WI
where WIASH.NOTIFICATION_ID = WN.NOTIFICATION_ID
and WIASH.NOTIFICATION_ID is not null
and WIASH.ITEM_TYPE = WI.ITEM_TYPE
and WIASH.ITEM_KEY = WI.ITEM_KEY
and WI.END_DATE IS NULL));
select PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION,
INSTANCE_LABEL, INSTANCE_ID
from WF_PROCESS_ACTIVITIES
where PERFORM_ROLE = l_oldname;
select RULE_ID
from WF_ROUTING_RULES
where ROLE = l_oldname
or ACTION_ARGUMENT = l_oldname;
select ra.ROWID
from WF_ROUTING_RULE_ATTRIBUTES ra
where ra.TEXT_VALUE = l_oldname
and exists
(select null
from wf_message_attributes ma
where ma.name=ra.name
and ma.type='ROLE');
select rowid
from wf_comments
where from_role = l_oldname
or to_role = l_oldname
or proxy_role = l_oldname;
select WC.ROWID
from WF_COMMENTS WC
where (WC.FROM_ROLE = l_oldname
or WC.TO_ROLE = l_oldname
or WC.PROXY_ROLE = l_oldname)
and (exists (select '1'
from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_ITEMS WI
where WIAS.NOTIFICATION_ID = WC.NOTIFICATION_ID
and WIAS.NOTIFICATION_ID is not null
and WIAS.ITEM_TYPE = WI.ITEM_TYPE
and WIAS.ITEM_KEY = WI.ITEM_KEY
and WI.END_DATE IS NULL)
or exists (select '1'
from WF_ITEM_ACTIVITY_STATUSES_H WIASH, WF_ITEMS WI
where WIASH.NOTIFICATION_ID = WC.NOTIFICATION_ID
and WIASH.NOTIFICATION_ID is not null
and WIASH.ITEM_TYPE = WI.ITEM_TYPE
and WIASH.ITEM_KEY = WI.ITEM_KEY
and WI.END_DATE IS NULL));
select wiav.rowid
from wf_item_attribute_values wiav, wf_item_attributes wia
where wia.type = 'ROLE'
and wia.item_type = wiav.item_type
and wia.name = wiav.name
and wiav.text_value = l_oldname;
select wiav.rowid
from wf_item_attribute_values wiav, wf_item_attributes wia
where wia.type = 'ROLE'
and wia.item_type = wiav.item_type
and wia.name = wiav.name
and wiav.text_value = l_oldname
and exists (select '1'
from WF_ITEMS WI
where WI.ITEM_TYPE = WIAV.ITEM_TYPE
and WI.ITEM_KEY = WIAV.ITEM_KEY
and WI.END_DATE IS NULL);
select fg.rowid
from FND_GRANTS fg
where fg.GRANTEE_TYPE='USER'
and fg.GRANTEE_ORIG_SYSTEM in ('FND_USR', 'PER')
and fg.PROGRAM_NAME = 'WORKFLOW_UI'
and fg.PARAMETER1=PropagateChangedName.OldName;
update wf_items
set owner_role = l_newname
where item_type = itemTbl(i).item_type
and item_key = itemTbl(i).item_key;
itemTbl.delete;
update WF_ITEM_ACTIVITY_STATUSES
set ASSIGNED_USER = l_newname
where ITEM_TYPE = itemActTbl(i).item_type
and ITEM_KEY = itemActTbl(i).item_key
and PROCESS_ACTIVITY = itemActTbl(i).process_activity;
itemActTbl.delete;
update WF_ITEM_ACTIVITY_STATUSES_H
set ASSIGNED_USER = l_newname
where ITEM_TYPE = itemActTbl(i).item_type
and ITEM_KEY = itemActTbl(i).item_key
and PROCESS_ACTIVITY = itemActTbl(i).process_activity;
itemActTbl.delete;
update WF_NOTIFICATIONS
set RECIPIENT_ROLE = decode(RECIPIENT_ROLE, l_oldname, l_newname, RECIPIENT_ROLE),
ORIGINAL_RECIPIENT = decode(ORIGINAL_RECIPIENT, l_oldname, l_newname, ORIGINAL_RECIPIENT),
FROM_ROLE = decode(FROM_ROLE, l_oldname, l_newname, FROM_ROLE),
RESPONDER = decode(RESPONDER, l_oldname, l_newname, RESPONDER),
MORE_INFO_ROLE = decode(MORE_INFO_ROLE, l_oldname, l_newname, MORE_INFO_ROLE)
where NOTIFICATION_ID = numTbl(i);
numTbl.delete;
update WF_COMMENTS
set FROM_ROLE = decode(FROM_ROLE, l_oldname, l_newname, FROM_ROLE),
FROM_USER = decode(FROM_ROLE, l_oldname, l_roleInfoTAB(1).display_name, FROM_USER),
TO_ROLE = decode(TO_ROLE, l_oldname, l_newname, TO_ROLE),
TO_USER = decode(TO_ROLE, l_oldname, l_roleInfoTAB(1).display_name, TO_USER),
PROXY_ROLE = decode(PROXY_ROLE, l_oldname, l_newname, PROXY_ROLE)
where rowid = rowIdTbl(i);
rowIdTbl.delete;
update WF_ITEM_ATTRIBUTE_VALUES
set TEXT_VALUE = l_newname
where rowid = rowIdTbl(i);
rowIdTbl.delete;
update wf_items
set owner_role = l_newname
where item_type = itemTbl(i).item_type
and item_key = itemTbl(i).item_key;
itemTbl.delete;
update WF_ITEM_ACTIVITY_STATUSES
set ASSIGNED_USER = l_newname
where ITEM_TYPE = itemActTbl(i).item_type
and ITEM_KEY = itemActTbl(i).item_key
and PROCESS_ACTIVITY = itemActTbl(i).process_activity;
itemActTbl.delete;
update WF_ITEM_ACTIVITY_STATUSES_H
set ASSIGNED_USER = l_newname
where ITEM_TYPE = itemActTbl(i).item_type
and ITEM_KEY = itemActTbl(i).item_key
and PROCESS_ACTIVITY = itemActTbl(i).process_activity;
itemActTbl.delete;
update WF_NOTIFICATIONS
set RECIPIENT_ROLE = decode(RECIPIENT_ROLE, l_oldname, l_newname, RECIPIENT_ROLE),
ORIGINAL_RECIPIENT = decode(ORIGINAL_RECIPIENT, l_oldname, l_newname, ORIGINAL_RECIPIENT),
FROM_ROLE = decode(FROM_ROLE, l_oldname, l_newname, FROM_ROLE),
RESPONDER = decode(RESPONDER, l_oldname, l_newname, RESPONDER),
MORE_INFO_ROLE = decode(MORE_INFO_ROLE, l_oldname, l_newname, MORE_INFO_ROLE)
where NOTIFICATION_ID = numTbl(i);
numTbl.delete;
update WF_COMMENTS
set FROM_ROLE = decode(FROM_ROLE, l_oldname, l_newname, FROM_ROLE),
FROM_USER = decode(FROM_ROLE, l_oldname, l_roleInfoTAB(1).display_name, FROM_USER),
TO_ROLE = decode(TO_ROLE, l_oldname, l_newname, TO_ROLE),
TO_USER = decode(TO_ROLE, l_oldname, l_roleInfoTAB(1).display_name, TO_USER),
PROXY_ROLE = decode(PROXY_ROLE, l_oldname, l_newname, PROXY_ROLE)
where rowid = rowIdTbl(i);
rowIdTbl.delete;
update WF_ITEM_ATTRIBUTE_VALUES
set TEXT_VALUE = l_newname
where rowid = rowIdTbl(i);
rowIdTbl.delete;
update WF_PROCESS_ACTIVITIES
set PERFORM_ROLE = l_newname
where PROCESS_ITEM_TYPE = procActTbl(i).process_item_type
and PROCESS_NAME = procActTbl(i).process_name
and PROCESS_VERSION = procActTbl(i).process_version
and INSTANCE_LABEL = procActTbl(i).instance_label
and INSTANCE_ID = procActTbl(i).instance_id;
procActTbl.delete;
update WF_ROUTING_RULES
set ROLE = l_newname
where RULE_ID = numTbl(i)
and ROLE = l_oldname;
update WF_ROUTING_RULES
set ACTION_ARGUMENT = l_newname
where RULE_ID = numTbl(i)
and ACTION_ARGUMENT = l_oldname;
numTbl.delete;
update wf_routing_rule_attributes
set text_value = l_newname
where rowid = rowIdTbl(i);
rowIdTbl.delete;
UPDATE fnd_grants fg
SET fg.parameter1=PropagateChangedName.NewName
WHERE fg.ROWID=rowIdTbl(i);
rowIdTbl.DELETE;
'Updated Records: WF_ITEMS:'||l_items||', WF_IAS:'||l_ias||', WF_IASH:'
||l_iash||', WF_NTFS:'||l_ntfs||', WF_COMMENTS:'||l_coms);
'Updated Records: WF_PROC_ACTS:'||l_pas||', WF_ROUTING_RULES:'||l_rr
||', WF_ROUTING_RULE_ATTRS:' ||l_rra||', WF_ITEM_ATTR_VALUES:'||l_ra
||', WORKLIST_ACCESS:'||l_wa);
select ROWID, start_date, end_date, user_start_date, user_end_date,
role_start_date, role_end_date,
assigning_role_start_date, assigning_role_end_date, null, null
from WF_USER_ROLE_ASSIGNMENTS WURA
where (WURA.USER_NAME = p_username or p_username is null)
and (WURA.ROLE_NAME = p_rolename or p_rolename is null)
and (WURA.EFFECTIVE_START_DATE is null
or WURA.EFFECTIVE_END_DATE is null);
update WF_USER_ROLE_ASSIGNMENTS WURA
set WURA.EFFECTIVE_START_DATE=l_e_start_dateTab(j),
WURA.EFFECTIVE_END_DATE =l_e_end_dateTab(j)
where WURA.ROWID=l_rowidTab(j);
select lur.rowid,
ura.effective_start_date,ura.effective_end_date
from wf_local_user_roles lur,
(select user_name, role_name, min(effective_start_date) effective_start_date,
max (effective_end_date) effective_end_date
from wf_user_role_assignments group by user_name, role_name) ura
where ura.user_name = lur.user_name
and ura.role_name = lur.role_name
and (URA.USER_NAME=p_username or p_username is null)
and (URA.ROLE_NAME=p_rolename or p_rolename is null)
and (ura.effective_start_date <> lur.effective_start_date or
ura.effective_end_date <> lur.effective_end_date);
update WF_LOCAL_USER_ROLES
set EFFECTIVE_START_DATE=l_URAEffectiveStartDate(i),
EFFECTIVE_END_DATE=l_URAEffectiveEndDate(i)
where ROWID = l_LURTab(i);
p_UpdateWho in BOOLEAN,
p_parallel_processes in number) is
ColumnsMissing EXCEPTION;
l_updateDateTAB dateTAB;
l_updatedByTAB numTAB;
l_updateLoginTAB numTAB;
select user_name, role_name, -1, start_date, expiration_date,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, user_start_date, role_start_date,
user_end_date, role_end_date, partition_id,
effective_start_date, effective_end_date, user_orig_system,
user_orig_system_id, role_orig_system, role_orig_system_id,
parent_orig_system, parent_orig_system_id, owner_tag
from wf_local_user_roles wur
where (p_username IS NULL OR wur.user_name=p_username)
and (p_rolename IS NULL OR wur.role_name=p_rolename)
and not exists (select null
from wf_user_role_assignments wura
where wura.user_name = wur.user_name
and wura.role_name = wur.role_name
and (p_username is null OR wura.user_name = p_username)
and (p_rolename is null or wura.role_name = p_rolename)
);
select wu.rowid, wu.orig_system old_orig_system,
wu.orig_system_id old_orig_system_id,
decode(nvl(fu.employee_id, -1),-1,'FND_USR','PER') new_orig_system,
nvl(fu.employee_id, fu.user_id)
from wf_local_roles partition (FND_USR) wu,
fnd_user fu
where wu.name = fu.user_name
and (p_username is null or wu.name = p_username)
and (wu.orig_system <> decode(nvl(fu.employee_id, -1),-1,'FND_USR','PER')
or wu.orig_system_id <> nvl(fu.employee_id, fu.user_id)
);
select wu.orig_system, wu.orig_system_id,
wur.role_orig_system, wur.role_orig_system_id,
wur.partition_id, wur.rowid
from wf_local_user_roles wur,
wf_local_roles partition (FND_USR) wu
where (p_username is null or wu.name = p_username )
and wu.name = wur.user_name
and wur.user_orig_system in ('FND_USR','PER')
and (wur.user_orig_system <> wu.orig_system
or wur.user_orig_system_id <> wu.orig_system_id
--check for role_orig_system in case of self-reference
or (wur.partition_id=1
and (wur.role_orig_system <> wu.orig_system
or wur.role_orig_system_id <> wu.orig_system_id)
)
);
select wura.rowid, wur.rowid, wu.start_date, wu.expiration_date,
wu.orig_system, wu.orig_system_id
from wf_local_user_roles partition (FND_USR) wur,
wf_local_roles partition (FND_USR) wu,
wf_user_role_assignments partition (FND_USR) wura
--Equi-joins to select the proper relationships between the tables
where (p_username is null OR wura.user_name = p_username)
and wura.partition_id = wu.partition_id
and wura.partition_id = wu.partition_id
and wur.user_name = wu.name
and wur.role_name = wu.name
and wura.assigning_role = wu.name
and wura.user_name = wu.name
and wura.role_name = wu.name
--Criteria to select records that need to be corrected, beginning with
--broad checks (if effective dates are null, no reason to check further)
--and working down to more specific checks between the orig_system/id
and ((wur.effective_start_date is null or
wur.effective_end_date is null or
wura.effective_start_date is null or
wura.effective_end_date is null)
or ((wur.user_orig_system <> wu.orig_system) or
(wur.user_orig_system_id <> wu.orig_system_id) or
(wur.role_orig_system <> wu.orig_system) or
(wur.role_orig_system_id <> wu.orig_system_id))
or (wura.user_orig_system is null or wura.role_orig_system is null or
wura.user_orig_system_id is null or
wura.user_orig_system_id is null)
or (wura.user_orig_system <> wu.orig_system)
or (wura.user_orig_system_id <> wu.orig_system_id)
or (wura.role_orig_system <> wu.orig_system)
or (wura.role_orig_system_id <> wu.orig_system_id)
or (wu.start_date is null and
(wur.start_date is not null or
wur.user_start_date is not null or
wur.role_start_date is not null or
wur.effective_start_date <> to_date(1,'J')))
or (wu.start_date is not null and
(wur.start_date is null or wur.user_start_date is null or
wur.role_start_date is null or wur.start_date <> wu.start_date or
wur.user_start_date <> wu.start_date or
wur.role_start_date <> wu.start_date or
wur.effective_start_date <> wu.start_date))
or (wu.expiration_date is null and
(wur.expiration_date is not null or
wur.user_end_date is not null or wur.role_end_date is not null or
wur.effective_end_date <> to_date('9999/01/01','YYYY/MM/DD')))
or (wu.expiration_date is not null and
(wur.expiration_date is null or wur.user_end_date is null or
wur.role_end_date is null or
wur.expiration_date <> wu.expiration_date or
wur.user_end_date <> wu.expiration_date or
wur.role_end_date <> wu.expiration_date or
wur.effective_end_date <> wu.expiration_date)));
select rowid, wura_id,wur_id,role_name,user_name,
assigning_role, start_date, end_Date,role_start_date,
role_end_date, user_start_date,user_end_date,
role_orig_system,role_orig_system_id,
user_orig_system, user_orig_system_id,
assigning_role_start_date, assigning_role_end_date,
effective_start_date, effective_end_date,
relationship_id
from wf_ur_validate_stg
where (p_username is null OR user_name = p_username)
and (p_rolename is null OR role_name = p_rolename)
order by ROLE_NAME, USER_NAME;
select rowid
from wf_local_user_roles
where (p_username IS NULL OR user_name= p_username )
AND (p_rolename IS NULL OR role_name = p_rolename)
AND ( not exists (select null from wf_local_roles
WHERE name= user_name
AND (p_username IS NULL OR name= p_username)
)
or not EXISTS (select null from wf_local_roles
WHERE NAME = role_name
AND (p_rolename IS NULL OR name= p_rolename)
)
);
select rowid
from wf_user_role_assignments
where (p_username IS NULL OR user_name = p_username )
and (p_rolename IS NULL OR role_name = p_rolename )
-- Either user name or role name NOT in wf_local_roles
and (user_name not in (select name from wf_local_roles
WHERE (p_username is null or name = p_username)
)
-- Check RoleName
or role_name not in (select name from wf_local_roles
WHERE (p_rolename IS NULL OR NAME = p_rolename)
)
);
DELETE from WF_LOCAL_USER_ROLES
WHERE rowid = l_rowIDTAB(i);
l_rowIDTAB.DELETE;
DELETE from WF_USER_ROLE_ASSIGNMENTS
WHERE rowid = l_rowIDTAB(i);
l_rowIDTAB.DELETE;
l_userSrcTAB.DELETE;
l_createdByTAB, l_createDateTAB, l_updatedByTAB, l_updateDateTAB,
l_updateLoginTAB, l_userStartSrcTAB, l_roleStartSrcTAB,
l_userEndSrcTAB, l_roleEndSrcTAB, l_partTAB, l_effStartSrcTAB,
l_effEndSrcTAB, l_userOrigSrcTAB, l_userOrigIDSrcTAB,
l_roleOrigSrcTAB, l_roleOrigIDSrcTAB, l_parentOrigTAB,
l_parentOrigIDTAB, l_ownerTAGS
limit l_maxRows;
insert into WF_USER_ROLE_ASSIGNMENTS (USER_NAME,
ROLE_NAME, RELATIONSHIP_ID, ASSIGNING_ROLE, START_DATE,
END_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, USER_START_DATE,
ROLE_START_DATE, ASSIGNING_ROLE_START_DATE, USER_END_DATE,
ROLE_END_DATE, ASSIGNING_ROLE_END_DATE, PARTITION_ID,
EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, USER_ORIG_SYSTEM,
USER_ORIG_SYSTEM_ID, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID,
PARENT_ORIG_SYSTEM, PARENT_ORIG_SYSTEM_ID, OWNER_TAG)
values (l_userSrcTAB(i), l_roleSrcTAB(i), l_relIDTAB(i),
l_roleSrcTAB(i), l_startSrcTAB(i), l_endSrcTAB(i),
l_createdByTAB(i), l_createDateTAB(i), l_updatedByTAB(i),
l_updateDateTAB(i), l_updateLoginTAB(i), l_userStartSrcTAB(i),
l_roleStartSrcTAB(i), l_roleStartSrcTAB(i), l_userEndSrcTAB(i),
l_roleEndSrcTAB(i), l_roleEndSrcTAB(i), l_partTAB(i),
l_effStartSrcTAB(i), l_effEndSrcTAB(i), l_userOrigSrcTAB(i),
l_userOrigIDSrcTAB(i), l_roleOrigSrcTAB(i),
l_roleOrigIDSrcTAB(i), l_parentOrigTAB(i),
l_parentOrigIDTAB(i), l_ownerTAGS(i));
l_rowIDTAB.DELETE;
UPDATE WF_LOCAL_ROLES
SET orig_system = l_userOrigDestTAB(i),
orig_system_id = l_userOrigIDDestTAB(i)
WHERE rowid = l_rowIDTAB(i);
delete from wf_local_roles
where rowid = l_rowIDTAB(l_eIndex);
l_rowIDTAB.DELETE;
UPDATE WF_LOCAL_USER_ROLES
SET user_orig_system = l_userOrigSrcTAB(i),
user_orig_system_id = l_userOrigIDSrcTAB(i),
role_orig_system = l_roleOrigSrcTAB(i),
role_orig_system_id = l_roleOrigIDSrcTAB(i)
WHERE rowid = l_rowIDTAB(i);
delete from wf_local_user_roles
where rowid = l_rowIDTAB(l_eIndex);
if (p_UpdateWho is not null and p_UpdateWho) then
forall tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST save exceptions
update WF_USER_ROLE_ASSIGNMENTS
set ROLE_START_DATE = l_StartSrcTAB(tabIndex),
ROLE_END_DATE = l_EndSrcTAB(tabIndex),
USER_START_DATE = l_StartSrcTAB(tabIndex),
USER_END_DATE = l_EndSrcTAB(tabIndex),
START_DATE = l_StartSrcTAB(tabIndex),
END_DATE = l_EndSrcTAB(tabIndex),
EFFECTIVE_START_DATE = nvl(l_StartSrcTAB(tabIndex),
to_date(1,'J')),
EFFECTIVE_END_DATE = nvl(l_EndSrcTAB(tabIndex),
to_date('9999/01/01', 'YYYY/MM/DD')),
ASSIGNING_ROLE_START_DATE = l_StartSrcTAB(tabIndex),
ASSIGNING_ROLE_END_DATE = l_EndSrcTAB(tabIndex),
USER_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
ROLE_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
USER_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex),
ROLE_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex),
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where rowid = l_rowIDTAB(tabIndex);
update WF_USER_ROLE_ASSIGNMENTS
set ROLE_START_DATE = l_StartSrcTAB(tabIndex),
ROLE_END_DATE = l_EndSrcTAB(tabIndex),
USER_START_DATE = l_StartSrcTAB(tabIndex),
USER_END_DATE = l_EndSrcTAB(tabIndex),
START_DATE = l_StartSrcTAB(tabIndex),
END_DATE = l_EndSrcTAB(tabIndex),
EFFECTIVE_START_DATE = nvl(l_StartSrcTAB(tabIndex),
to_date(1,'J')),
EFFECTIVE_END_DATE = nvl(l_EndSrcTAB(tabIndex),
to_date('9999/01/01', 'YYYY/MM/DD')),
ASSIGNING_ROLE_START_DATE = l_StartSrcTAB(tabIndex),
ASSIGNING_ROLE_END_DATE = l_EndSrcTAB(tabIndex),
USER_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
ROLE_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
USER_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex),
ROLE_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex)
where rowid = l_rowIDTAB(tabIndex);
delete from wf_user_role_assignments
where rowid = l_rowIDTAB(l_eIndex);
if (p_UpdateWho is not null and p_UpdateWho) then
forall tabIndex in l_rowIDSrcTAB.FIRST..l_rowIDSrcTAB.LAST save exceptions
update wf_local_user_roles partition (FND_USR)
set ROLE_START_DATE = l_StartSrcTAB(tabIndex),
ROLE_END_DATE = l_EndSrcTAB(tabIndex),
USER_START_DATE = l_StartSrcTAB(tabIndex),
USER_END_DATE = l_EndSrcTAB(tabIndex),
START_DATE = l_StartSrcTAB(tabIndex),
EXPIRATION_DATE = l_EndSrcTAB(tabIndex),
EFFECTIVE_START_DATE = nvl(l_StartSrcTAB(tabIndex),
to_date(1,'J')),
EFFECTIVE_END_DATE = nvl(l_EndSrcTAB(tabIndex),
to_date('9999/01/01', 'YYYY/MM/DD')),
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where rowid = l_rowIDSrcTAB(tabIndex);
update wf_local_user_roles partition (FND_USR)
set ROLE_START_DATE = l_StartSrcTAB(tabIndex),
ROLE_END_DATE = l_EndSrcTAB(tabIndex),
USER_START_DATE = l_StartSrcTAB(tabIndex),
USER_END_DATE = l_EndSrcTAB(tabIndex),
START_DATE = l_StartSrcTAB(tabIndex),
EXPIRATION_DATE = l_EndSrcTAB(tabIndex),
EFFECTIVE_START_DATE = nvl(l_StartSrcTAB(tabIndex),
to_date(1,'J')),
EFFECTIVE_END_DATE = nvl(l_EndSrcTAB(tabIndex),
to_date('9999/01/01', 'YYYY/MM/DD'))
where rowid = l_rowIDSrcTAB(tabIndex);
l_rowIDTAB.DELETE;
l_rowIDTAB.delete;
l_rowIDSrcTAB.delete;
l_startSrcTAB.delete;
l_endSrcTAB.delete;
l_userOrigSrcTAB.delete;
l_userOrigIDSrcTAB.delete;
select min(to_number(value))
into l_defaultParProc
from v$parameter
where name in ('parallel_max_servers','cpu_count');
'INSERT /*+ append parallel(WF_UR_VALIDATE_STG,'|| l_parallelProc ||') */
INTO WF_UR_VALIDATE_STG (WURA_ID, WUR_ID , ROLE_NAME , USER_NAME ,
ASSIGNING_ROLE , START_DATE , END_DATE , ROLE_START_DATE, ROLE_END_DATE
, USER_START_DATE , USER_END_DATE , ROLE_ORIG_SYSTEM ,
ROLE_ORIG_SYSTEM_ID , USER_ORIG_SYSTEM , USER_ORIG_SYSTEM_ID ,
ASSIGNING_ROLE_START_DATE , ASSIGNING_ROLE_END_DATE ,
EFFECTIVE_START_DATE , EFFECTIVE_END_DATE , RELATIONSHIP_ID )
SELECT /*+ ordered parallel(WURA,'|| l_parallelProc ||') parallel(WR,'|| l_parallelProc ||
') parallel (wu,'|| l_parallelProc ||')
parallel (WAR,'|| l_parallelProc ||') parallel(WUR,'|| l_parallelProc ||') */
WURA.ROWID, WUR.ROWID, WURA.ROLE_NAME, WURA.USER_NAME,
WURA.ASSIGNING_ROLE,
DECODE(WURA.USER_NAME, WURA.ROLE_NAME, WU.START_DATE,
WURA.START_DATE) START_DATE,
DECODE(WURA.USER_NAME, WURA.ROLE_NAME, WU.EXPIRATION_DATE,
WURA.END_DATE) END_DATE,
WR.START_DATE, WR.EXPIRATION_DATE, WU.START_DATE,
WU.EXPIRATION_DATE, WR.ORIG_SYSTEM, WR.ORIG_SYSTEM_ID,
WU.ORIG_SYSTEM, WU.ORIG_SYSTEM_ID, WAR.START_DATE,
WAR.EXPIRATION_DATE,
GREATEST(NVL(WURA.START_DATE, TO_DATE(1,''J'')),
NVL(WURA.USER_START_DATE, TO_DATE(1,''J'')),
NVL(WURA.ROLE_START_DATE, TO_DATE(1,''J'')),
NVL(WURA.ASSIGNING_ROLE_START_DATE,
TO_DATE(1,''J''))) EFFECTIVE_START_DATE,
LEAST(NVL(WURA.END_DATE, TO_DATE(''9999/01/01'', ''YYYY/MM/DD'')),
NVL(WURA.USER_END_DATE, TO_DATE(''9999/01/01'', ''YYYY/MM/DD'')),
NVL(WURA.ROLE_END_DATE, TO_DATE(''9999/01/01'', ''YYYY/MM/DD'')),
NVL(WURA.ASSIGNING_ROLE_END_DATE,
TO_DATE(''9999/01/01'', ''YYYY/MM/DD''))) EFFECTIVE_END_DATE,
WURA.RELATIONSHIP_ID
FROM
WF_USER_ROLE_ASSIGNMENTS WURA,
WF_LOCAL_USER_ROLES WUR ,
WF_LOCAL_ROLES WAR,
WF_LOCAL_ROLES WU,
WF_LOCAL_ROLES WR
WHERE WURA.PARTITION_ID = WAR.PARTITION_ID
AND WURA.ASSIGNING_ROLE=WAR.NAME
AND WURA.USER_NAME= WUR.USER_NAME
AND WURA.ROLE_NAME=WUR.ROLE_NAME
AND WUR.USER_NAME = WU.NAME
AND WUR.USER_ORIG_SYSTEM=WU.ORIG_SYSTEM
AND WUR.USER_ORIG_SYSTEM_ID= WU.ORIG_SYSTEM_ID
AND WUR.ROLE_NAME = WR.NAME
AND WUR.ROLE_ORIG_SYSTEM= WR.ORIG_SYSTEM
AND WUR.ROLE_ORIG_SYSTEM_ID= WR.ORIG_SYSTEM_ID
AND WUR.PARTITION_ID = WR.PARTITION_ID
AND WUR.PARTITION_ID <> 1
AND WAR.PARTITION_ID <> 1
AND ( ( WUR.EFFECTIVE_START_DATE IS NULL or
WUR.EFFECTIVE_END_DATE IS NULL or
WURA.EFFECTIVE_START_DATE IS NULL or
WURA.EFFECTIVE_END_DATE IS NULL )
OR ( WURA.EFFECTIVE_START_DATE <> GREATEST(NVL(WURA.START_DATE,
TO_DATE(1,''J'')), NVL(WURA.USER_START_DATE, TO_DATE(1,''J'')), NVL(
WURA.ROLE_START_DATE, TO_DATE(1,''J'')), NVL(
WURA.ASSIGNING_ROLE_START_DATE, TO_DATE(1,''J''))) )
OR ( WURA.EFFECTIVE_END_DATE <> LEAST(NVL(WURA.END_DATE, TO_DATE(
''9999/01/01'', ''YYYY/MM/DD'')), NVL(WURA.USER_END_DATE, TO_DATE(
''9999/01/01'', ''YYYY/MM/DD'')) , NVL(WURA.ROLE_END_DATE, TO_DATE(
''9999/01/01'', ''YYYY/MM/DD'')), NVL(WURA.ASSIGNING_ROLE_END_DATE,
TO_DATE(''9999/01/01'', ''YYYY/MM/DD''))))
OR (WURA.USER_NAME = WURA.ROLE_NAME and
(nvl(wura.start_date, to_date(1,''J'')) <>
nvl(wu.start_date, to_date(1,''J'')) or
nvl(wura.end_date, to_date(''9999/01/01'', ''YYYY/MM/DD'')) <>
nvl(wu.expiration_date, to_date(''9999/01/01'', ''YYYY/MM/DD''))))
OR ( ( WUR.ASSIGNMENT_TYPE IS NULL )
OR WUR.ASSIGNMENT_TYPE NOT IN (''D'', ''I'', ''B'') )
OR ( WURA.USER_ORIG_SYSTEM IS NULL
OR WURA.ROLE_ORIG_SYSTEM IS NULL
OR WURA.USER_ORIG_SYSTEM_ID IS NULL
OR WURA.ROLE_ORIG_SYSTEM_ID IS NULL )
OR ( WURA.USER_ORIG_SYSTEM <> WU.ORIG_SYSTEM
OR WURA.USER_ORIG_SYSTEM_ID <> WU.ORIG_SYSTEM_ID
OR WURA.ROLE_ORIG_SYSTEM <> WR.ORIG_SYSTEM
OR WURA.ROLE_ORIG_SYSTEM_ID <> WR.ORIG_SYSTEM_ID )
OR ( ( WU.START_DATE IS NULL
AND ( WUR.USER_START_DATE IS NOT NULL
OR WURA.USER_START_DATE IS NOT NULL ) )
OR ( WU.START_DATE IS NOT NULL
AND ( WUR.USER_START_DATE IS NULL
OR WUR.USER_START_DATE <> WU.START_DATE
OR WURA.USER_START_DATE IS NULL
OR WURA.USER_START_DATE <> WU.START_DATE ) )
OR ( WU.EXPIRATION_DATE IS NULL
AND ( WUR.USER_END_DATE IS NOT NULL
OR WURA.USER_END_DATE IS NOT NULL ) )
OR ( WU.EXPIRATION_DATE IS NOT NULL
AND ( WUR.USER_END_DATE IS NULL
OR WUR.USER_END_DATE <> WU.EXPIRATION_DATE
OR WURA.USER_END_DATE IS NULL
OR WURA.USER_END_DATE <> WU.EXPIRATION_DATE ) ) )
OR ( ( WR.START_DATE IS NULL
AND ( WUR.ROLE_START_DATE IS NOT NULL
OR WURA.ROLE_START_DATE IS NOT NULL ) )
OR ( WR.START_DATE IS NOT NULL
AND ( WUR.ROLE_START_DATE IS NULL
OR WUR.ROLE_START_DATE <> WR.START_DATE
OR WURA.ROLE_START_DATE IS NULL
OR WURA.ROLE_START_DATE <> WR.START_DATE ) )
OR ( WR.EXPIRATION_DATE IS NULL
AND ( WUR.ROLE_END_DATE IS NOT NULL
OR WURA.ROLE_END_DATE IS NOT NULL ) )
OR ( WR.EXPIRATION_DATE IS NOT NULL
AND ( WUR.ROLE_END_DATE IS NULL
OR WUR.ROLE_END_DATE <> WR.EXPIRATION_DATE
OR WURA.ROLE_END_DATE IS NULL
OR WURA.ROLE_END_DATE <> WR.EXPIRATION_DATE ) ) )
OR ( ( WAR.START_DATE IS NULL
AND WURA.ASSIGNING_ROLE_START_DATE IS NOT NULL )
OR ( WAR.START_DATE IS NOT NULL
AND ( WURA.ASSIGNING_ROLE_START_DATE IS NULL
OR WURA.ASSIGNING_ROLE_START_DATE <> WAR.START_DATE ) )
OR ( WAR.EXPIRATION_DATE IS NULL
AND WURA.ASSIGNING_ROLE_END_DATE IS NOT NULL )
OR ( WAR.EXPIRATION_DATE IS NOT NULL
AND ( WURA.ASSIGNING_ROLE_END_DATE IS NULL
OR WURA.ASSIGNING_ROLE_END_DATE <> WAR.EXPIRATION_DATE ) ) ) )' ;
if (p_UpdateWho is not null and p_UpdateWho) then
forall tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST
update WF_USER_ROLE_ASSIGNMENTS
set ROLE_START_DATE = l_roleStartSrcTAB(tabIndex),
ROLE_END_DATE = l_roleEndSrcTAB(tabIndex),
USER_START_DATE = l_userStartSrcTAB(tabIndex),
USER_END_DATE = l_userEndSrcTAB(tabIndex),
START_DATE = l_startSrcTAB(tabIndex),
END_DATE = l_endSRcTAB(tabIndex),
EFFECTIVE_START_DATE = l_effStartSrcTAB(tabIndex),
EFFECTIVE_END_DATE = l_effEndSrcTAB(tabIndex),
ASSIGNING_ROLE_START_DATE = l_asgStartSrcTAB(tabIndex),
ASSIGNING_ROLE_END_DATE = l_asgEndSrcTAB(tabIndex),
USER_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
ROLE_ORIG_SYSTEM=l_roleOrigSrcTAB(tabIndex),
USER_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex),
ROLE_ORIG_SYSTEM_ID=l_roleOrigIDSrcTAB(tabIndex),
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where rowid = l_rowIDTAB(tabIndex);
update WF_USER_ROLE_ASSIGNMENTS
set ROLE_START_DATE = l_roleStartSrcTAB(tabIndex),
ROLE_END_DATE = l_roleEndSrcTAB(tabIndex),
USER_START_DATE = l_userStartSrcTAB(tabIndex),
USER_END_DATE = l_userEndSrcTAB(tabIndex),
START_DATE = l_startSrcTAB(tabIndex),
END_DATE = l_endSRcTAB(tabIndex),
EFFECTIVE_START_DATE = l_effStartSrcTAB(tabIndex),
EFFECTIVE_END_DATE = l_effEndSrcTAB(tabIndex),
ASSIGNING_ROLE_START_DATE = l_asgStartSrcTAB(tabIndex),
ASSIGNING_ROLE_END_DATE = l_asgEndSrcTAB(tabIndex),
USER_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
ROLE_ORIG_SYSTEM=l_roleOrigSrcTAB(tabIndex),
USER_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex),
ROLE_ORIG_SYSTEM_ID=l_roleOrigIDSrcTAB(tabIndex)
where rowid = l_rowIDTAB(tabIndex);
if (p_UpdateWho is not null and p_UpdateWho) then
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),
START_DATE = l_startDestTAB(destTabIndex),
EXPIRATION_DATE = l_endDestTAB(destTabIndex),
EFFECTIVE_START_DATE = l_effStartDestTAB(destTabIndex),
EFFECTIVE_END_DATE = l_effEndDestTAB(destTabIndex),
ASSIGNMENT_TYPE = l_assignTAB(destTabIndex),
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.Login_Id,
LAST_UPDATE_DATE = SYSDATE
WHERE rowid = l_rowIDDestTAB(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),
START_DATE = l_startDestTAB(destTabIndex),
EXPIRATION_DATE = l_endDestTAB(destTabIndex),
EFFECTIVE_START_DATE = l_effStartDestTAB(destTabIndex),
EFFECTIVE_END_DATE = l_effEndDestTAB(destTabIndex),
ASSIGNMENT_TYPE = l_assignTAB(destTabIndex)
WHERE rowid = l_rowIDDestTAB(destTabIndex);
l_roleStartDestTAB.DELETE;
l_roleEndDestTAB.DELETE;
l_userStartDestTAB.DELETE;
l_userEndDestTAB.DELETE;
l_effStartDestTAB.DELETE;
l_effEndDestTAB.DELETE;
l_assignTAB.DELETE;
l_startDestTAB.DELETE;
l_endDestTAB.DELETE;
l_roleDestTAB.DELETE;
l_userDestTAB.DELETE;
l_userOrigDestTAB.DELETE;
l_userOrigIDDestTAB.DELETE;
l_roleOrigDestTAB.DELETE;
l_roleOrigIDDestTAB.DELETE;
l_rowIDDestTAB.DELETE;
else --Record is already in the summary table so update effective dates
if l_effStartSrcTAB(tabIndex) < l_effStartDestTAB(sumTabIndex) then
l_effStartDestTAB(sumTabIndex) := l_effStartSrcTAB(tabIndex);
l_rowIDTAB.DELETE;
if (p_UpdateWho is not null and p_UpdateWho) then
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 = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.Login_Id,
LAST_UPDATE_DATE = SYSDATE
WHERE rowid = l_rowIDDestTAB(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)
WHERE rowid = l_rowIDDestTAB(destTabIndex);
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from FND_GRANTS fg
where fg.GRANTEE_TYPE='USER'
and fg.GRANTEE_ORIG_SYSTEM in ('FND_USR', 'PER')
and fg.PROGRAM_NAME = 'WORKFLOW_UI'
and fg.PARAMETER1=p_name;
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_ITEMS
where OWNER_ROLE = p_name;
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_ITEMS
where OWNER_ROLE = p_name
and END_DATE is null;
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_ITEM_ACTIVITY_STATUSES WIAS
where ASSIGNED_USER = p_name;
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_ITEM_ACTIVITY_STATUSES WIAS
where exists (select '1'
from WF_ITEMS WI
where WI.ITEM_TYPE=WIAS.ITEM_TYPE and
WI.ITEM_KEY=WIAS.ITEM_KEY and
WI.END_DATE is null) and
ASSIGNED_USER = p_name;
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_ITEM_ACTIVITY_STATUSES_H WIASH
where ASSIGNED_USER = p_name;
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_ITEM_ACTIVITY_STATUSES_H WIASH
where exists (select '1'
from WF_ITEMS WI
where WI.ITEM_TYPE=WIASH.ITEM_TYPE and
WI.ITEM_KEY=WIASH.ITEM_KEY and
WI.END_DATE is null) and
ASSIGNED_USER = p_name;
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_NOTIFICATIONS WN
where RECIPIENT_ROLE = p_name
or ORIGINAL_RECIPIENT = p_name
or more_info_role = p_name
or from_role = p_name
or responder = p_name;
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_NOTIFICATIONS WN
where (exists (select '1'
from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_ITEMS WI
where WIAS.NOTIFICATION_ID = WN.NOTIFICATION_ID
and WIAS.ITEM_TYPE = WI.ITEM_TYPE
and WIAS.ITEM_KEY = WI.ITEM_KEY
and WI.END_DATE IS NULL)
or exists (select '1'
from WF_ITEM_ACTIVITY_STATUSES_H WIASH, WF_ITEMS WI
where WIASH.NOTIFICATION_ID = WN.NOTIFICATION_ID
and WIASH.ITEM_TYPE = WI.ITEM_TYPE
and WIASH.ITEM_KEY = WI.ITEM_KEY
and WI.END_DATE IS NULL))
and (RECIPIENT_ROLE = p_name
or ORIGINAL_RECIPIENT = p_name
or more_info_role = p_name
or from_role = p_name
or responder = p_name);
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_PROCESS_ACTIVITIES
where PERFORM_ROLE = p_name;
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_ROUTING_RULES
where ROLE = p_name or ACTION_ARGUMENT = p_name;
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_ROUTING_RULES
where (ROLE = p_name or ACTION_ARGUMENT = p_name)
and BEGIN_DATE<=SYSDATE
and (END_DATE is null or END_DATE>=SYSDATE);
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_ROUTING_RULE_ATTRIBUTES ra, WF_ROUTING_RULES wrr
where wrr.RULE_ID=ra.RULE_ID
and ra.TEXT_VALUE = p_name
and exists
(select '1'
from WF_MESSAGE_ATTRIBUTES ma
where ma.NAME=ra.NAME
and ma.TYPE='ROLE');
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_ROUTING_RULE_ATTRIBUTES ra, WF_ROUTING_RULES wrr
where wrr.BEGIN_DATE<=SYSDATE
and (wrr.END_DATE is null or wrr.END_DATE>=SYSDATE)
and wrr.RULE_ID=ra.RULE_ID
and ra.TEXT_VALUE = p_name
and exists
(select '1'
from WF_MESSAGE_ATTRIBUTES ma
where ma.NAME=ra.NAME
and ma.TYPE='ROLE');
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_COMMENTS WC
where (WC.FROM_ROLE = p_name
or WC.TO_ROLE = p_name
or WC.PROXY_ROLE = p_name)
and (exists (select '1'
from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_ITEMS WI
where WIAS.NOTIFICATION_ID = WC.NOTIFICATION_ID
and WIAS.ITEM_TYPE = WI.ITEM_TYPE
and WIAS.ITEM_KEY = WI.ITEM_KEY
and WI.END_DATE IS NULL)
or exists (select '1'
from WF_ITEM_ACTIVITY_STATUSES_H WIASH, WF_ITEMS WI
where WIASH.NOTIFICATION_ID = WC.NOTIFICATION_ID
and WIASH.ITEM_TYPE = WI.ITEM_TYPE
and WIASH.ITEM_KEY = WI.ITEM_KEY
and WI.END_DATE IS NULL));
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_COMMENTS WC
where WC.FROM_ROLE = p_name
or WC.TO_ROLE = p_name
or WC.PROXY_ROLE = p_name;
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_ITEM_ATTRIBUTE_VALUES WIAV, WF_ITEM_ATTRIBUTES WIA
where WIA.type = 'ROLE'
and WIA.ITEM_TYPE = WIAV.ITEM_TYPE
and WIA.NAME = WIAV.NAME
and WIAV.TEXT_VALUE = p_name;
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_ITEM_ATTRIBUTE_VALUES WIAV, WF_ITEM_ATTRIBUTES WIA
where WIA.type = 'ROLE'
and WIA.ITEM_TYPE = WIAV.ITEM_TYPE
and WIA.NAME = WIAV.NAME
and WIAV.TEXT_VALUE = p_name
and exists (select '1'
from WF_ITEMS WI
where WI.ITEM_TYPE=WIAV.ITEM_TYPE
and WI.ITEM_KEY=WIAV.ITEM_KEY
and WI.END_DATE is null);
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_LOCAL_ROLES
WHERE NAME = p_name
AND PARTITION_ID = l_partitionID
AND ORIG_SYSTEM = l_roleInfoTAB(1).ORIG_SYSTEM
AND ORIG_SYSTEM_ID = l_roleInfoTAB(1).ORIG_SYSTEM_ID;
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_LOCAL_USER_ROLES
WHERE (ROLE_NAME = p_name
AND ROLE_ORIG_SYSTEM = l_roleInfoTAB(1).ORIG_SYSTEM
AND ROLE_ORIG_SYSTEM_ID = l_roleInfoTAB(1).ORIG_SYSTEM_ID)
OR
(USER_NAME = p_name
AND USER_ORIG_SYSTEM = l_roleInfoTAB(1).ORIG_SYSTEM
AND USER_ORIG_SYSTEM_ID = l_roleInfoTAB(1).ORIG_SYSTEM_ID);
select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
from WF_USER_ROLE_ASSIGNMENTS
WHERE (ROLE_NAME = p_name
AND ROLE_ORIG_SYSTEM = l_roleInfoTAB(1).ORIG_SYSTEM
AND ROLE_ORIG_SYSTEM_ID = l_roleInfoTAB(1).ORIG_SYSTEM_ID)
OR
(USER_NAME = p_name
AND USER_ORIG_SYSTEM = l_roleInfoTAB(1).ORIG_SYSTEM
AND USER_ORIG_SYSTEM_ID = l_roleInfoTAB(1).ORIG_SYSTEM_ID);
select sum (a.detail) into l_wfcount_tab.REC_CNT
from (select nvl(count(1), 0) as detail
from WF_ROLE_HIERARCHIES
WHERE SUPER_NAME = p_name
AND SUPERIOR_PARTITION_ID = l_partitionID
union
select nvl(count(1), 0) as detail
from WF_ROLE_HIERARCHIES
WHERE SUB_NAME = p_name
AND PARTITION_ID = l_partitionID) a;