[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(1) into cnt
from USER_VIEWS
where VIEW_NAME = 'WF_FND_USR_ROLES';
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);
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
and UR.PARTITION_ID = c_partID;
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');
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;
select R.NAME,
substrb(R.DISPLAY_NAME,1,360),
substrb(R.DESCRIPTION,1,1000),
R.NOTIFICATION_PREFERENCE,
R.LANGUAGE,
R.TERRITORY,
wf_core.nls_date_format, -- <7578908> new NLS parameters
R.LANGUAGE, -- default nls_date_language
wf_core.nls_calendar ,
wf_core.nls_numeric_characters,
wf_core.nls_sort,
wf_core.nls_currency, --
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;
select R.NAME,
substrb(R.DISPLAY_NAME,1,360),
substrb(R.DESCRIPTION,1,1000),
R.NOTIFICATION_PREFERENCE,
R.LANGUAGE,
R.TERRITORY,
wf_core.nls_date_format, -- <7578908> new NLS parameters
R.LANGUAGE, -- default nls_date_language
wf_core.nls_calendar ,
wf_core.nls_numeric_characters,
wf_core.nls_sort,
wf_core.nls_currency, --
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;
select R.NAME,
substrb(R.DISPLAY_NAME,1,360),
substrb(R.DESCRIPTION,1,1000),
R.NOTIFICATION_PREFERENCE,
R.LANGUAGE,
R.TERRITORY,
wf_core.nls_date_format, -- <7578908> new NLS parameters
R.LANGUAGE, -- default nls_date_language
wf_core.nls_calendar ,
wf_core.nls_numeric_characters,
wf_core.nls_sort,
wf_core.nls_currency, --
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;
select R.NAME,
substrb(R.DISPLAY_NAME,1,360),
substrb(R.DESCRIPTION,1,1000),
R.NOTIFICATION_PREFERENCE,
R.LANGUAGE,
R.TERRITORY,
wf_core.nls_date_format, -- <7578908> new NLS parameters
R.LANGUAGE, -- default nls_date_language
wf_core.nls_calendar ,
wf_core.nls_numeric_characters,
wf_core.nls_sort,
wf_core.nls_currency, --
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;
role_info_tbl(1).last_update_date := to_date(null);
role_info_tbl(1).last_updated_by := to_number(null);
role_info_tbl(1).last_update_login := to_number(null);
select nvl(INSTALLED_FLAG, 'N')
into GetRoleInfoMail.installed_flag
from WF_LANGUAGES
where NLS_LANGUAGE = GetRoleInfoMail.language;
select nvl(INSTALLED_FLAG, 'N')
into GetRoleInfoMail2.p_installed_flag
from WF_LANGUAGES
where NLS_LANGUAGE = GetRoleInfoMail2.p_nlsLanguage;
select partition_id, orig_system
into GetRolePartitionInfo.partition_id, GetRolePartitionInfo.orig_system
from wf_local_roles
where name = GetRolePartitionInfo.role
and nvl(expiration_date, sysdate+1) > sysdate
and rownum = 1;
select partition_id, orig_system
into GetRolePartitionInfo.partition_id, GetRolePartitionInfo.orig_system
from wf_local_roles
where name = GetRolePartitionInfo.role
and orig_system =l_orig_system
and orig_system_id = l_orig_system_id
and nvl(expiration_date, sysdate+1) > sysdate
and rownum = 1;
select orig_system, display_name
into GetRolePartitionInfo.orig_system,
GetRolePartitionInfo.display_name
from wf_directory_partitions_vl
where GetRolePartitionInfo.partition_id <> 1
and partition_id = GetRolePartitionInfo.partition_id
or GetRolePartitionInfo.partition_id = 1
and orig_system = GetRolePartitionInfo.orig_system;
select partition_id, orig_system
into GetRolePartitionInfo.partition_id, GetRolePartitionInfo.orig_system
from wf_roles
where name = GetRolePartitionInfo.role
and rownum = 1;
select partition_id, orig_system
into GetRolePartitionInfo.partition_id, GetRolePartitionInfo.orig_system
from wf_roles
where name = GetRolePartitionInfo.role
and orig_system =l_orig_system
and orig_system_id = l_orig_system_id
and rownum = 1;
select orig_system, display_name
into GetRolePartitionInfo.orig_system,
GetRolePartitionInfo.display_name
from wf_directory_partitions_vl
where GetRolePartitionInfo.partition_id <> 1
and partition_id = GetRolePartitionInfo.partition_id
or GetRolePartitionInfo.partition_id = 1
and orig_system = GetRolePartitionInfo.orig_system;
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)
);
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)
);
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.ROLE_ORIG_SYSTEM = l_rorigSys
and UR.ROLE_ORIG_SYSTEM_ID = l_rorigSysID
and UR.PARTITION_ID = l_partID);
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
and UR.PARTITION_ID = l_partID);
select 1
into dummy
from SYS.DUAL
where exists
(select null
from wf_users
where name = username
and PARTITION_ID <> 9
and status = 'ACTIVE');
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');
select 1
into dummy
from SYS.DUAL
where exists
(select null
from WF_ROLES
where name = p_rolename
and status = 'ACTIVE');
select 1
into dummy
from SYS.DUAL
where exists
(select null
from WF_ROLES
where orig_system = substr(p_rolename, 1, colon-1)
and orig_system_id = substr(p_rolename, colon+1)
and name = p_rolename
and status = 'ACTIVE');
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;
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;
select substrb(display_name,1,360)
from wf_roles
where name = l_name
and PARTITION_ID not in (9,8,7,6,4);
select substrb(display_name,1,360)
from wf_roles
where orig_system = l_origSys
and orig_system_id = l_origSysID
and name = l_name;
select substrb(nvl(wrt.display_name,wr.display_name),1,360)
from wf_local_roles wr, wf_local_roles_tl wrt
where wr.name = l_name
and wr.orig_system = wrt.orig_system (+)
and wr.orig_system_id = wrt.orig_system_id (+)
and wr.name = wrt.name (+)
and wr.partition_id = wrt.partition_id (+)
and wrt.language (+) = userenv('LANG')
and wr.partition_id not in (9,8,7,6,4,3);
select substrb(nvl(wrt.display_name,wr.display_name),1,360)
from wf_local_roles wr, wf_local_roles_tl wrt
where wr.orig_system = l_origSys
and wr.orig_system_id = l_origSysID
and wr.name = l_name
and wr.orig_system = wrt.orig_system (+)
and wr.orig_system_id = wrt.orig_system_id (+)
and wr.name = wrt.name (+)
and wr.partition_id = wrt.partition_id (+)
and wrt.language (+) = userenv('LANG')
AND wr.partition_id <> 3;
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);
select nls_territory, nls_language into nterr, nlang
from WF_LANGUAGES
where code = userenv('LANG');
select count(*)
into l_count
from WF_LOCAL_ROLES
where NAME = CreateUser.name
and PARTITION_ID = l_partitionID
and ORIG_SYSTEM = l_origSys
and ORIG_SYSTEM_ID <> CreateUser.orig_system_id;
l_lastupdby := nvl(CreateUser.last_updated_by, WFA_SEC.USER_ID);
l_lastupddt := nvl(CreateUser.last_update_date, SYSDATE);
l_lastupdlog:= nvl(CreateUser.last_update_login, WFA_SEC.LOGIN_ID);
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,
l_ntfPref,
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,
l_lastupddt,
l_lastupdby,
l_creatdt,
l_creatby,
l_lastupdlog );
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,
l_lastupddt,
l_lastupdby,
l_creatdt,
l_creatby,
l_lastupdlog);
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);
last_update_date=>CreateUser.last_update_date,
last_updated_by=>CreateUser.last_updated_by,
last_update_login=>CreateUser.last_update_login,
created_by=>CreateUser.created_by,
creation_date=>CreateUser.creation_date);
select count(1) into d1
from wf_users u
where u.name = CreateAdHocUser.name
or u.display_name = CreateAdHocUser.display_name;
select to_char(WF_ADHOC_ROLE_S.NEXTVAL)
into role_id
from SYS.DUAL;
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);
select nls_territory, nls_language into nterr, nlang
from WF_LANGUAGES
where code = userenv('LANG');
select ORIG_SYSTEM_ID
bulk collect into l_origSysIDTAB
from WF_LOCAL_ROLES
where NAME = CreateRole.role_name
and PARTITION_ID = l_partitionID
and ORIG_SYSTEM = CreateRole.orig_system
and ORIG_SYSTEM_ID <> CreateRole.orig_system_id
and rownum < 2;
l_lastupdby := nvl(CreateRole.last_updated_by, WFA_SEC.USER_ID);
l_lastupddt := nvl(CreateRole.last_update_date, SYSDATE);
l_lastupdlog:= nvl(CreateRole.last_update_login, WFA_SEC.LOGIN_ID);
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,
l_ntfPref,
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,
l_lastupddt,
l_lastupdby,
l_creatdt,
l_creatby,
l_lastupdlog );
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,
l_lastupddt,
l_lastupdby,
l_creatdt,
l_creatby,
l_lastupdlog );
select count(1) into d1
from wf_roles
where name = CreateAdHocRole.role_name
or display_name = CreateAdHocRole.role_display_name;
select to_char(WF_ADHOC_ROLE_S.NEXTVAL)
into role_id
from SYS.DUAL;
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,
assignment_reason in varchar2,
eventParams in wf_parameter_list_t )
is
l_count PLS_INTEGER;
SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID,
START_DATE, EXPIRATION_DATE,PARTITION_ID
INTO l_uorigSys, l_uorigSysID,
l_userStartDate, l_userExpDate, l_upartID
FROM WF_LOCAL_ROLES
WHERE NAME = CreateUserRole.USER_NAME
AND ROWNUM < 2;
SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID,
START_DATE, EXPIRATION_DATE, PARTITION_ID
INTO l_rorigSys, l_rorigSysID,
l_roleStartDate, l_roleExpDate,l_rpartID
FROM WF_LOCAL_ROLES
WHERE NAME = CreateUserRole.ROLE_NAME
AND ROWNUM < 2;
SELECT start_date, expiration_date
into l_userStartDate, l_userExpDate
FROM WF_LOCAL_ROLES
WHERE NAME = CreateUserRole.user_name
AND ORIG_SYSTEM = l_uorigSys
AND ORIG_SYSTEM_ID = l_uorigSysID
AND PARTITION_ID = l_upartID;
SELECT start_date, expiration_date
INTO l_roleStartDate, l_roleExpDate
FROM WF_LOCAL_ROLES
WHERE NAME = CreateUserRole.role_name
AND ORIG_SYSTEM = l_rorigSys
AND ORIG_SYSTEM_ID = l_rorigSysID
AND PARTITION_ID = l_rpartID;
l_lastupdby := nvl(CreateUserRole.last_updated_by, WFA_SEC.USER_ID);
l_lastupddt := nvl(CreateUserRole.last_update_date, SYSDATE);
l_lastupdlog:= nvl(CreateUserRole.last_update_login, WFA_SEC.LOGIN_ID);
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,
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,
ASSIGNMENT_REASON
)
values
(
CreateUserRole.user_name,
CreateUserRole.role_name,
l_uorigSys,
l_uorigSysID,
l_rorigSys,
l_rorigSysID,
trunc(CreateUserRole.start_date),
trunc(CreateUserRole.end_date),
l_userStartDate,
l_userExpDate,
l_roleStartDate,
l_roleExpDate,
l_effStartDate,
l_effEndDate,
l_rpartID,
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,
l_lastupddt,
l_lastupdby,
l_creatdt,
l_creatby,
l_lastupdlog,
CreateUserRole.assignment_reason
) returning rowid into l_affectedRow;
WF_EVENT.AddParameterToList('LAST_UPDATED_BY',
to_char(CreateUserRole.last_updated_by,
WF_CORE.canonical_number_mask), l_params);
WF_EVENT.AddParameterToList('LAST_UPDATE_DATE',
to_char(CreateUserRole.last_update_date,
WF_CORE.canonical_date_mask), l_params);
WF_EVENT.AddParameterToList('LAST_UPDATE_LOGIN',
to_char(CreateUserRole.last_update_login,
WF_CORE.canonical_number_mask), l_params);
last_updated_by in number,
last_update_date in date,
last_update_login in number,
created_by in number,
creation_date in date,
assignment_type in varchar2,
parent_orig_system in varchar2,
parent_orig_system_id in number,
owner_tag in varchar2,
assignment_reason in varchar2,
updateWho in BOOLEAN,
eventParams in wf_parameter_list_t) is
l_uorigSys VARCHAR2(30) := UPPER(user_orig_system);
l_UpdateWho BOOLEAN := nvl(updateWho,TRUE);
SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID
INTO l_uorigSys, l_uorigSysID
FROM WF_LOCAL_ROLES
WHERE NAME = SetUserRoleAttr.user_name
and rownum < 2;
SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID, PARTITION_ID
INTO l_rorigSys, l_rorigSysID,l_partitionID
FROM WF_LOCAL_ROLES
WHERE NAME = SetUserRoleAttr.role_name
AND rownum < 2;
select START_DATE, END_DATE
into l_oldStartDate, l_oldEndDate
from (select START_DATE, END_DATE
from WF_USER_ROLE_ASSIGNMENTS
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
and PARTITION_ID = l_partitionID
order by relationship_id)
where rownum = 1;
l_lastupdby := nvl(setuserroleattr.last_updated_by, WFA_SEC.USER_ID);
l_lastupddt := nvl(setuserroleattr.last_update_date, SYSDATE);
l_lastupdlog:= nvl(setuserroleattr.last_update_login, WFA_SEC.LOGIN_ID);
if (OverWrite and l_updateWho) then
update WF_LOCAL_USER_ROLES
set EXPIRATION_DATE = SetUserRoleAttr.end_date,
START_DATE = SetUserRoleAttr.start_date,
LAST_UPDATED_BY = l_lastupdby,
LAST_UPDATE_DATE = l_lastupddt,
LAST_UPDATE_LOGIN = l_lastupdlog,
-- <7298384> never update CREATION_DATE, CREATED_BY on update dml
-- CREATION_DATE = nvl(SetUserRoleAttr.creation_date,
-- CREATION_DATE),
-- CREATED_BY = nvl(SetUserRoleAttr.created_by, CREATED_BY), --
PARENT_ORIG_SYSTEM = l_porigSys,
PARENT_ORIG_SYSTEM_ID = l_porigSysID,
ASSIGNMENT_REASON = SetUserRoleAttr.assignment_reason
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
and PARTITION_ID = l_partitionID
returning rowid into l_affectedRow;
elsif (OverWrite) then --donot Update WHO Columns
update WF_LOCAL_USER_ROLES
set EXPIRATION_DATE = SetUserRoleAttr.end_date,
START_DATE = SetUserRoleAttr.start_date,
PARENT_ORIG_SYSTEM = l_porigSys,
PARENT_ORIG_SYSTEM_ID = l_porigSysID,
ASSIGNMENT_REASON = SetUserRoleAttr.assignment_reason
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
and PARTITION_ID = l_partitionID
returning rowid into l_affectedRow;
elsif (l_updateWho) then -- Update WHO columns
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 = l_lastupdby,
LAST_UPDATE_DATE = l_lastupddt,
LAST_UPDATE_LOGIN = l_lastupdlog,
-- <7298384> never update CREATION_DATE, CREATED_BY on update dml
-- CREATED_BY = nvl(SetUserRoleAttr.created_by,
-- created_by),
-- CREATION_DATE = nvl(SetUserRoleAttr.creation_date,
-- creation_date), --
ASSIGNMENT_REASON = nvl(SetUserRoleAttr.assignment_reason,
ASSIGNMENT_REASON)
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
and PARTITION_ID = l_partitionID
returning rowid into l_affectedRow;
else --Donot Update Who columns
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),
ASSIGNMENT_REASON = nvl(SetUserRoleAttr.assignment_reason,
ASSIGNMENT_REASON)
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
and PARTITION_ID = l_partitionID
returning rowid into l_affectedRow;
WF_EVENT.AddParameterToList('LAST_UPDATED_BY',
to_char(SetUserRoleAttr.last_updated_by,
WF_CORE.canonical_number_mask), l_params);
WF_EVENT.AddParameterToList('LAST_UPDATE_DATE',
to_char(SetUserRoleAttr.last_update_date,
WF_CORE.canonical_date_mask), l_params);
WF_EVENT.AddParameterToList('LAST_UPDATE_LOGIN',
to_char(SetUserRoleAttr.last_update_login,
WF_CORE.canonical_number_mask), l_params);
if (l_updateWho) then
WF_EVENT.AddParameterToList('UPDATE_WHO','TRUE',l_params);
event.Event_Name := 'oracle.apps.fnd.wf.ds.userRole.updated';
WF_EVENT.Raise(p_event_name=>'oracle.apps.fnd.wf.ds.userRole.updated',
p_event_key=> l_uOrigSys||':'||
to_char(l_uOrigSysId)||'|'||
l_rOrigSys||':'||
to_char(l_rOrigSysId)||'|'||
to_char(SYSDATE, 'J:SSSSS'),
p_parameters=>l_params);
update WF_LOCAL_USER_ROLES
set EXPIRATION_DATE = l_expdate,
EFFECTIVE_END_DATE = l_expdate,
LAST_UPDATED_BY = l_lastupdby,
LAST_UPDATE_LOGIN = l_lastupdlog,
LAST_UPDATE_DATE = l_lastupddt
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
and PARTITION_ID = l_partitionID;
update WF_USER_ROLE_ASSIGNMENTS
set END_DATE = l_expdate,
EFFECTIVE_END_DATE = l_expdate,
LAST_UPDATED_BY = l_lastupdby,
LAST_UPDATE_LOGIN = l_lastupdlog,
LAST_UPDATE_DATE = l_lastupddt
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;
update WF_LOCAL_USER_ROLES
set EXPIRATION_DATE = l_expdate,
EFFECTIVE_END_DATE = l_expdate,
LAST_UPDATED_BY = l_lastupdby,
LAST_UPDATE_LOGIN = l_lastupdlog,
LAST_UPDATE_DATE = l_lastupddt
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
and PARTITION_ID = l_partitionID;
update WF_USER_ROLE_ASSIGNMENTS
set END_DATE = l_expdate,
EFFECTIVE_END_DATE = l_expdate,
LAST_UPDATED_BY = l_lastupdby,
LAST_UPDATE_LOGIN = l_lastupdlog,
LAST_UPDATE_DATE = l_lastupddt
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;
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;
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;
last_updated_by=>WFA_SEC.user_id,
last_update_date=>sysdate,
last_update_login=>WFA_SEC.login_id);
last_updated_by in number,
last_update_date in date,
last_update_login in number,
created_by in number,
creation_date in date,
eventParams in wf_parameter_list_t)
is
l_expiration DATE;
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
AND PARTITION_ID = l_partitionID;
l_lastupddt := nvl(SetUserAttr.last_update_date, SYSDATE);
l_lastupdby := nvl(SetUserAttr.last_updated_by, WFA_SEC.USER_ID);
l_lastupdlog := nvl(SetUserAttr.last_update_login, WFA_SEC.LOGIN_ID);
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,
-- <7298384> always keep CREATED_BY and CREATION_DATE in update
-- CREATED_BY = nvl(SetUserAttr.created_by, created_by),
-- CREATION_DATE = nvl(SetUserAttr.creation_date,
-- creation_date), --
LAST_UPDATED_BY = l_lastupdby,
LAST_UPDATE_DATE = l_lastupddt,
LAST_UPDATE_LOGIN = l_lastupdlog
where NAME = user_name
and ORIG_SYSTEM = SetUserAttr.orig_system
and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
and PARTITION_ID = l_partitionID
and USER_FLAG = 'Y';
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,
-- <7298384> always keep CREATED_BY and CREATION_DATE in update
-- CREATED_BY = nvl(SetUserAttr.created_by, created_by),
-- CREATION_DATE = nvl(SetUserAttr.creation_date,
-- creation_date), --
LAST_UPDATED_BY = l_lastupdby,
LAST_UPDATE_DATE = l_lastupddt,
LAST_UPDATE_LOGIN = l_lastupdlog
where NAME = user_name
and ORIG_SYSTEM = SetUserAttr.orig_system
and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
and PARTITION_ID = l_partitionID
and USER_FLAG = 'Y';
update WF_LOCAL_ROLES_TL
set DISPLAY_NAME = nvl(SetUserAttr.display_name, DISPLAY_NAME),
DESCRIPTION = SetUserAttr.description,
-- <7298384> always keep CREATED_BY and CREATION_DATE in update
-- CREATED_BY = nvl(SetUserAttr.created_by, created_by),
-- CREATION_DATE = nvl(SetUserAttr.creation_date, creation_date), --
LAST_UPDATED_BY = l_lastupdby,
LAST_UPDATE_DATE = l_lastupddt,
LAST_UPDATE_LOGIN = l_lastupdlog
where NAME = SetUserAttr.user_name
and ORIG_SYSTEM = SetUserAttr.orig_system
and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
and LANGUAGE = userenv('LANG')
and PARTITION_ID = l_partitionID;
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 wu.name,
nvl(SetUserAttr.display_name, wu.DISPLAY_NAME) ,
SetUserAttr.description,
wu.ORIG_SYSTEM,
wu.ORIG_SYSTEM_ID ,
wu.PARTITION_ID,
userenv('LANG'),
nvl(SetUserAttr.owner_tag, wu.OWNER_TAG),
l_creatby,
l_creatdt,
l_lastupdby,
l_lastupddt,
l_lastupdlog
from WF_LOCAL_ROLES wu
where wu.NAME = SetUserAttr.user_name
and wu.ORIG_SYSTEM = SetUserAttr.orig_system
and wu.ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
and wu.PARTITION_ID = l_partitionID
and wu.USER_FLAG = 'Y');
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)
-- <7298384> no overwrite mode, so keeping previous standard WHO column values
-- CREATED_BY = nvl(SetUserAttr.created_by, CREATED_BY),
-- CREATION_DATE = nvl(SetUserAttr.creation_date, CREATION_DATE),
-- LAST_UPDATED_BY = l_lastupdby,
-- LAST_UPDATE_DATE = l_lastupddt,
-- LAST_UPDATE_LOGIN = l_lastupdlog
--
where NAME = user_name
and ORIG_SYSTEM = SetUserAttr.orig_system
and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
and PARTITION_ID = l_partitionID
and USER_FLAG = 'Y';
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)
-- <7298384> no overwrite mode, so keeping previous values
-- CREATED_BY = nvl(SetUserAttr.created_by, CREATED_BY),
-- CREATION_DATE = nvl(SetUserAttr.creation_date, CREATION_DATE),
-- LAST_UPDATED_BY = l_lastupdby,
-- LAST_UPDATE_DATE = l_lastupddt,
-- LAST_UPDATE_LOGIN = l_lastupdlog --
where NAME = user_name
and ORIG_SYSTEM = SetUserAttr.orig_system
and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
and PARTITION_ID = l_partitionID
and USER_FLAG = 'Y';
update WF_LOCAL_ROLES_TL
set DISPLAY_NAME = nvl(SetUserAttr.display_name, DISPLAY_NAME),
DESCRIPTION = SetUserAttr.description
-- <7298384> no overwrite mode, so keeping previous values
-- CREATED_BY = nvl(SetUserAttr.created_by, CREATED_BY),
-- CREATION_DATE = nvl(SetUserAttr.creation_date, CREATION_DATE),
-- LAST_UPDATED_BY = l_lastupdby,
-- LAST_UPDATE_DATE = l_lastupddt,
-- LAST_UPDATE_LOGIN = l_lastupdlog --
where NAME = SetUserAttr.user_name
and ORIG_SYSTEM = SetUserAttr.orig_system
and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
and LANGUAGE = userenv('LANG')
and PARTITION_ID = l_partitionID;
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),
l_creatby,
l_creatdt,
l_lastupdby,
l_lastupddt,
l_lastupdlog
from WF_LOCAL_ROLES
where NAME = SetUserAttr.user_name
and ORIG_SYSTEM = SetUserAttr.orig_system
and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
and PARTITION_ID = l_partitionID
and USER_FLAG = 'Y');
WF_EVENT.AddParameterToList('LAST_UPDATED_BY',
to_char(SetUserAttr.last_updated_by,
WF_CORE.canonical_number_mask), 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 ,
WF_CORE.canonical_number_mask), l_params);
WF_EVENT.Raise(p_event_name=>'oracle.apps.fnd.wf.ds.user.updated',
p_event_key=>user_name, p_parameters=>l_params);
last_updated_by in number,
last_update_date in date,
last_update_login in number,
created_by in number,
creation_date in date,
eventParams in wf_parameter_list_t)
is
l_expiration DATE;
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
AND PARTITION_ID = l_partitionID;
l_lastupdby := nvl(SetRoleAttr.last_updated_by, WFA_SEC.USER_ID);
l_lastupddt := nvl(SetRoleAttr.last_update_date, SYSDATE);
l_lastupdlog:= nvl(SetRoleAttr.last_update_login, WFA_SEC.LOGIN_ID);
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 = l_lastupdby,
LAST_UPDATE_DATE = l_lastupddt,
LAST_UPDATE_LOGIN = l_lastupdlog
-- <7298384> always keep CREATED_BY and CREATION_DATE in update
-- CREATED_BY = nvl(SetRoleAttr.created_by,
-- created_by),
-- CREATION_DATE = nvl(SetRoleAttr.creation_date,
-- creation_date) --
where NAME = role_name
and ORIG_SYSTEM = SetRoleAttr.orig_system
and ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
and PARTITION_ID = l_partitionID;
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 = l_lastupdby,
LAST_UPDATE_DATE = l_lastupddt,
LAST_UPDATE_LOGIN = l_lastupdlog
-- <7298384> always keep CREATED_BY and CREATION_DATE in update
-- CREATED_BY = nvl(SetRoleAttr.created_by,
-- created_by),
-- CREATION_DATE = nvl(SetRoleAttr.creation_date,
-- creation_date) --
where NAME = role_name
and ORIG_SYSTEM = SetRoleAttr.orig_system
and ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
and PARTITION_ID = l_partitionID;
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 = l_lastupdby,
LAST_UPDATE_DATE = l_lastupddt,
LAST_UPDATE_LOGIN = l_lastupdlog
-- <7298384> always keep CREATED_BY and CREATION_DATE in update
-- CREATED_BY = nvl(SetRoleAttr.created_by,
-- created_by),
-- CREATION_DATE = nvl(SetRoleAttr.creation_date,
-- creation_date) --
where NAME = role_name
and ORIG_SYSTEM = SetRoleAttr.orig_system
and ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
and PARTITION_ID = l_partitionID;
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),
l_creatby,
l_creatdt,
l_lastupdby,
l_lastupddt,
l_lastupdlog
from WF_LOCAL_ROLES
where NAME = role_name
and ORIG_SYSTEM = SetRoleAttr.orig_system
and ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
and PARTITION_ID = l_partitionID);
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)
-- <7298384> no overwrite mode, so keeping previous values
-- LAST_UPDATED_BY = l_lastupdby,
-- LAST_UPDATE_DATE = l_lastupddt,
-- LAST_UPDATE_LOGIN = l_lastupdlog,
-- CREATED_BY = nvl(SetRoleAttr.created_by,
-- created_by),
-- CREATION_DATE = nvl(SetRoleAttr.creation_date,
-- creation_date) --
where NAME = role_name
and ORIG_SYSTEM = SetRoleAttr.orig_system
and ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
and PARTITION_ID = l_partitionID;
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)
-- <7298384> no overwrite mode, so keeping previous values
-- LAST_UPDATED_BY = l_lastupdby,
-- LAST_UPDATE_DATE = l_lastupddt,
-- LAST_UPDATE_LOGIN = l_lastupdlog,
-- CREATED_BY = nvl(SetRoleAttr.created_by,
-- created_by),
-- CREATION_DATE = nvl(SetRoleAttr.creation_date,
-- creation_date) --
where NAME = role_name
and ORIG_SYSTEM = SetRoleAttr.orig_system
and ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
and PARTITION_ID = l_partitionID;
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)
-- <7298384> no overwrite mode, so keeping previous values
-- LAST_UPDATED_BY = l_lastupdby,
-- LAST_UPDATE_DATE = l_lastupddt,
-- LAST_UPDATE_LOGIN = l_lastupdlog,
-- CREATED_BY = nvl(SetRoleAttr.created_by,
-- created_by),
-- CREATION_DATE = nvl(SetRoleAttr.creation_date,
-- creation_date) --
where NAME = role_name
and ORIG_SYSTEM = SetRoleAttr.orig_system
and ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
and PARTITION_ID = l_partitionID;
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),
l_creatby,
l_creatdt,
l_lastupdby,
l_lastupddt,
l_lastupdlog
from WF_LOCAL_ROLES
where NAME = SetRoleAttr.role_name
and ORIG_SYSTEM = SetRoleAttr.orig_system
and ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
and PARTITION_ID = l_partitionID);
WF_EVENT.AddParameterToList('LAST_UPDATED_BY',
to_char(SetRoleAttr.last_updated_by ,
WF_CORE.canonical_number_mask), 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 ,
WF_CORE.canonical_number_mask), l_params);
WF_EVENT.Raise(p_event_name=>'oracle.apps.fnd.wf.ds.role.updated',
p_event_key=>role_name, p_parameters=>l_params);
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
and UR.PARTITION_ID = 0;
delete from WF_USER_ROLE_ASSIGNMENTS URA
where URA.ROLE_NAME = RemoveUsersFromAdHocRole.role_name
and URA.ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
and URA.ROLE_ORIG_SYSTEM_ID = 0
and URA.PARTITION_ID = 0;
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
and UR.PARTITION_ID = 0;
delete from WF_USER_ROLE_ASSIGNMENTS URA
where URA.USER_NAME = user
and URA.ROLE_NAME = RemoveUsersFromAdHocRole.role_name
and URA.ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
and URA.ROLE_ORIG_SYSTEM_ID = 0
and URA.PARTITION_ID = 0;
wfa_sec.DS_Update_Local_Role(l_oldname,l_newname);
p_last_update_date in DATE,
p_last_updated_by in NUMBER,
p_last_update_login in NUMBER
-- <6817561>
, p_overWriteUserRoles in boolean
--
) is
l_overWriteUserRoles varchar2(2) := 'N';
Update WF_USER_ROLE_ASSIGNMENTS
Set USER_ORIG_SYSTEM = p_new_user_origSystem,
USER_ORIG_SYSTEM_ID = p_new_user_origSystemID,
ROLE_ORIG_SYSTEM = p_new_user_origSystem,
ROLE_ORIG_SYSTEM_ID = p_new_user_origSystemID,
-- <6817561>
LAST_UPDATE_DATE = decode(l_overWriteUserRoles,'Y', nvl(p_last_update_date, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
LAST_UPDATED_BY = decode(l_overWriteUserRoles,'Y', nvl(p_last_updated_by, LAST_UPDATED_BY), LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = decode(l_overWriteUserRoles,'Y', nvl(p_last_update_login, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
--
Where USER_ORIG_SYSTEM = p_old_user_origSystem
And USER_ORIG_SYSTEM_ID = p_old_user_origSystemID
And ROLE_ORIG_SYSTEM = p_old_user_origSystem
And ROLE_ORIG_SYSTEM_ID = p_old_user_origSystemID
And USER_NAME = p_user_name;
Delete from WF_USER_ROLE_ASSIGNMENTS
Where USER_ORIG_SYSTEM = p_old_user_origSystem
And USER_ORIG_SYSTEM_ID = p_old_user_origSystemID
And ROLE_ORIG_SYSTEM = p_old_user_origSystem
And ROLE_ORIG_SYSTEM_ID = p_old_user_origSystemID
And USER_NAME = p_user_name;
Update WF_LOCAL_USER_ROLES
Set USER_ORIG_SYSTEM = p_new_user_origSystem,
USER_ORIG_SYSTEM_ID = p_new_user_origSystemID,
ROLE_ORIG_SYSTEM = p_new_user_origSystem,
ROLE_ORIG_SYSTEM_ID = p_new_user_origSystemID,
-- <6817561>
LAST_UPDATE_DATE = decode(l_overWriteUserRoles,'Y', nvl(p_last_update_date, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
LAST_UPDATED_BY = decode(l_overWriteUserRoles,'Y', nvl(p_last_updated_by, LAST_UPDATED_BY), LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = decode(l_overWriteUserRoles,'Y', nvl(p_last_update_login, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
--
Where USER_ORIG_SYSTEM = p_old_user_origSystem
And USER_ORIG_SYSTEM_ID = p_old_user_origSystemID
And ROLE_ORIG_SYSTEM = p_old_user_origSystem
And ROLE_ORIG_SYSTEM_ID = p_old_user_origSystemID
And USER_NAME = p_user_name;
Delete from WF_LOCAL_USER_ROLES
Where USER_ORIG_SYSTEM = p_old_user_origSystem
And USER_ORIG_SYSTEM_ID = p_old_user_origSystemID
And ROLE_ORIG_SYSTEM = p_old_user_origSystem
And ROLE_ORIG_SYSTEM_ID = p_old_user_origSystemID
And USER_NAME = p_user_name;
Update WF_LOCAL_USER_ROLES
Set USER_ORIG_SYSTEM = p_new_user_origSystem,
USER_ORIG_SYSTEM_ID = p_new_user_origSystemID,
-- <6817561>
LAST_UPDATE_DATE = decode(l_overWriteUserRoles,'Y', nvl(p_last_update_date, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
LAST_UPDATED_BY = decode(l_overWriteUserRoles,'Y', nvl(p_last_updated_by, LAST_UPDATED_BY), LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = decode(l_overWriteUserRoles,'Y', nvl(p_last_update_login, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
--
Where USER_ORIG_SYSTEM = p_old_user_origSystem
And USER_ORIG_SYSTEM_ID = p_old_user_origSystemID
And USER_NAME = p_user_name;
Update WF_USER_ROLE_ASSIGNMENTS
Set USER_ORIG_SYSTEM = p_new_user_origSystem,
USER_ORIG_SYSTEM_ID = p_new_user_origSystemID,
-- <6817561>
LAST_UPDATE_DATE = decode(l_overWriteUserRoles,'Y', nvl(p_last_update_date, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
LAST_UPDATED_BY = decode(l_overWriteUserRoles,'Y', nvl(p_last_updated_by, LAST_UPDATED_BY), LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = decode(l_overWriteUserRoles,'Y', nvl(p_last_update_login, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
--
Where USER_ORIG_SYSTEM = p_old_user_origSystem
And USER_ORIG_SYSTEM_ID = p_old_user_origSystemID
And USER_NAME = p_user_name;
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;
SELECT Partition_ID, orig_system
INTO g_localPartitionID, g_localPartitionName
FROM WF_DIRECTORY_PARTITIONS
WHERE ORIG_SYSTEM = 'WF_LOCAL_ROLES';
select WR.NAME,
WR.DISPLAY,
WR.DESCRIPTION,
WR.NOTIFICATION_PREFERENCE,
WR.LANGUAGE,
WR.TERRITORY,
WR.FAX,
WR.STATUS,
WR.EXPIRATION_DATE,
WR.ORIG_SYSTEM,
WR.ORIG_SYSTEM_ID
into ROLE,
DISPLAY_NAME,
DESCRIPTION,
NOTIFICATION_PREFERENCE,
LANGUAGE,
TERRITORY,
FAX,
STATUS,
EXPIRATION_DATE,
ORIG_SYSTEM,
ORIG_SYSTEM_ID
from (select R.NAME,
substrb(R.DISPLAY_NAME,1,360) DISPLAY,
substrb(R.DESCRIPTION,1,1000) DESCRIPTION,
R.NOTIFICATION_PREFERENCE,
R.LANGUAGE,
R.TERRITORY,
R.FAX,
R.STATUS,
R.EXPIRATION_DATE,
R.ORIG_SYSTEM,
R.ORIG_SYSTEM_ID,
decode (R.STATUS, 'ACTIVE', 1, 2) ACTIVE_ORDER,
decode (R.ORIG_SYSTEM, 'PER', 1, 'FND_USR', 2, 3) ORIG_SYS_ORDER
from WF_ROLES R
where UPPER(R.EMAIL_ADDRESS) = UPPER(l_email)
order by ACTIVE_ORDER asc, ORIG_SYS_ORDER asc, START_DATE asc) WR
where ROWNUM < 2;
select count(1)
into mls_enabled
from wf_directory_partitions
where orig_system = l_origSys
and ROLE_TL_VIEW is not NULL ;
/* --Update the user/roles
UPDATE WF_LOCAL_USER_ROLES
SET USER_NAME = l_newName
WHERE USER_NAME = l_oldName;
UPDATE WF_USER_ROLE_ASSIGNMENTS
SET USER_NAME = l_newName
WHERE USER_NAME = l_oldName;*/ --these updates are now made inline
procedure DeleteRole ( p_name in varchar2,
p_origSystem in varchar2,
p_origSystemID in number)
is
TYPE numTab is table of number index by binary_integer;
select 1 into l_count
from SYS.DUAL
where exists (select null from wf_roles
where name=p_name
);
'WF_DIRECTORY.deleteRole',
'Role is still active. Make sure it is end dated ');
WF_CORE.Context('WF_DIRECTORY', 'deleteRole', p_name);
delete from wf_local_roles
where name=p_name
and orig_system=p_origSystem
and orig_system_id=p_origSystemId
and partition_id =l_partitionID
returning user_flag into l_flag;
DeleteUserRole(p_username=>p_name,
p_userorigSystem=>p_origSystem,
p_userorigSystemID=>p_origSystemID);
select relationship_id bulk collect into l_relIDTab
from wf_role_hierarchies where sub_name=p_name
or super_name = p_name;
DeleteUserRole(p_rolename=>p_name,
p_roleorigSystem=>p_origSystem,
p_roleorigSystemID=>p_origSystemID);
WF_CORE.Context('WF_DIRECTORY', 'deleteRole', p_name);
procedure DeleteUserRole ( p_username in varchar2,
p_rolename in varchar2,
p_userOrigSystem in varchar2,
p_userOrigSystemID in number,
p_roleOrigSystem in varchar2,
p_roleOrigSystemID in number)
is
l_count pls_integer;
WF_CORE.Context('WF_DIRECTORY', 'DeleteUserRole', p_username,p_rolename);
select 1 into l_count
from SYS.DUAL
where exists (select null from wf_local_roles
where name=p_rolename)
and ( exists (select null from wf_user_roles
where role_name=p_rolename
)
or exists (select null from wf_user_role_assignments_v
where role_name=p_rolename
)
or exists (select null from wf_role_hierarchies
where (super_name=p_rolename
or sub_name=p_rolename)
and enabled_flag='Y'
));
'WF_DIRECTORY.DeleteUserRole',
'User/Role assignment is still active. Make sure it is end dated ' ||
'and removed from any hierarchies');
WF_CORE.Context('WF_DIRECTORY', 'DeleteUserRole', p_rolename);
delete from wf_user_role_assignments
where role_name=p_rolename;
delete from wf_local_user_roles
where role_name=p_rolename
and role_orig_system=p_roleorigSystem
and role_orig_system_id=p_roleorigSystemID
and partition_id = l_partitionID;
select 1 into l_count
from SYS.DUAL
where exists (select null from wf_local_roles
where name=p_username)
and ( exists (select null from wf_user_roles
where user_name=p_username
)
or exists (select null from wf_user_role_assignments_v
where user_name=p_username
));
'WF_DIRECTORY.DeleteUserRole',
'User/Role assignment is still active. Make sure it is end dated ' ||
'and removed from any hierarchies');
WF_CORE.Context('WF_DIRECTORY', 'DeleteUserRole', p_username);
delete from wf_user_role_assignments
where user_name=p_username;
delete from wf_local_user_roles
where user_name=p_username
and user_orig_system=p_userorigSystem
and user_orig_system_id=p_userorigSystemID;
select 1 into l_count
from SYS.DUAL
where exists (select null from wf_local_roles
where name=p_rolename or name=p_username)
and ( exists (select null from wf_user_roles
where role_name=p_rolename
and user_name=p_username
)
or exists (select null from wf_user_role_assignments_v
where role_name=p_rolename
and user_name=p_username
)
or exists (select null from wf_role_hierarchies
where (super_name=p_rolename
or sub_name=p_rolename)
and enabled_flag='Y'
));
'WF_DIRECTORY.DeleteUserRole',
'User/Role assignment is still active. Make sure it is end dated ' ||
'and removed from any hierarchies');
WF_CORE.Context('WF_DIRECTORY', 'DeleteUserRole', p_username,p_rolename);
delete from wf_user_role_assignments
where role_name=p_rolename
and user_name=p_username;
delete from wf_local_user_roles
where role_name=p_rolename
and user_name=p_username
and role_orig_system=p_roleorigSystem
and role_orig_system_id=p_roleorigSystemID
and user_orig_system=p_userOrigSystem
and user_orig_system_id=p_userOrigSystemID
and partition_id = l_partitionID;
WF_CORE.Context('WF_DIRECTORY', 'DeleteUserRole', p_username,p_rolename);