[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_delete BOOLEAN;
g_updateOnly BOOLEAN;
g_overWrite_UserRoles BOOLEAN; -- <6817561> this exposes a switch to update
g_lastUpdateDate DATE;
g_lastUpdatedBy NUMBER;
g_lastUpdateLogin NUMBER;
g_delete := FALSE;
g_updateOnly := FALSE;
g_lastUpdateDate := sysdate;
g_lastUpdatedBy := WFA_SEC.user_id;
g_lastUpdateLogin := WFA_SEC.login_id;
elsif (upper(p_attributes(i).getName()) = 'DELETE') then
if (upper(p_attributes(i).getValue()) = 'TRUE') then
g_delete := TRUE;
elsif (upper(p_attributes(i).getName()) = 'UPDATEONLY') then
if (upper(p_attributes(i).getValue()) = 'TRUE') then
g_updateOnly := TRUE;
elsif (upper(p_attributes(i).getName()) = 'LAST_UPDATED_BY') then
g_lastUpdatedBy := to_number(p_attributes(i).getValue());
elsif (upper(p_attributes(i).getName()) = 'LAST_UPDATE_DATE') then
g_lastUpdateDate := to_date(p_attributes(i).getValue(),
WF_CORE.canonical_date_mask);
elsif (upper(p_attributes(i).getName()) = 'LAST_UPDATE_LOGIN') then
g_lastUpdateLogin := to_number(p_attributes(i).getValue());
g_delete := FALSE;
if ((g_delete) and (g_expDate is NULL)) then
--If delete=true then we will set inactive immediately. However if
--there is an expiration date, that will override the delete.
g_expDate := sysdate;
g_updateOnly := TRUE;
** update_entmgr -
**
** This routine encapsulates the bit that keeps entmgr in the loop
*/
PROCEDURE update_entmgr(p_entity_type in varchar2,
p_entity_key_value in varchar2,
p_attributes in wf_parameter_list_t,
p_source in varchar2) is
i number;
l_modulePkg varchar2(240) := g_modulePkg||'.update_entmgr';
'Begin update_entmgr('|| p_entity_type||', '||
p_entity_key_value||', '||
'p_attributes (wf_parameter_list_t)'||', '||
p_source||')');
'End update_entmgr('||p_entity_type||', '||
p_entity_key_value||', '||
'p_attributes(wf_parameter_list_t)'||', '||
p_source||')');
SELECT di.OWNER,
di.index_name,
di.table_name,
decode(di.uniqueness, 'UNIQUE', ' UNIQUE ', ' ')
FROM dba_indexes di
WHERE di.table_owner = tableOwner
AND di.owner = indexOwner
AND di.table_name = tableName;
SELECT dip.tablespace_name,
dip.ini_trans,
dip.max_trans,
dip.initial_extent,
dip.next_extent,
dip.min_extent,
dip.max_extent,
dip.pct_increase,
dip.pct_free,
dip.freelists,
dip.freelist_groups,
decode (dip.compression, 'ENABLED', 'COMPRESS', null) compression
FROM dba_ind_partitions dip
WHERE dip.index_owner = indexOwner
AND dip.index_name = indexName
AND dip.partition_name = 'WF_LOCAL_ROLES';
SELECT column_name, column_position
FROM dba_ind_columns
WHERE index_owner = indexOwner
AND index_name = indexName
AND table_name = tableName
ORDER BY column_position;
select COLUMN_EXPRESSION
into l_ColumnExpr
from dba_ind_expressions
where INDEX_NAME = l_indexes(i)
and INDEX_OWNER = l_owners(i)
and COLUMN_POSITION = b.column_position;
** This routine dynamically builds a column and select list based on a
** comparison of the stage table and the seeding view.
*/
function BuildQuery (p_orig_system in VARCHAR2,
p_stage_table in VARCHAR2,
p_seed_view in VARCHAR2,
p_columnList in out NOCOPY VARCHAR2,
p_selectList in out NOCOPY VARCHAR2) return BOOLEAN is
l_seedCursor NUMBER;
p_selectList := ' ';
l_sql := 'select * from '||p_stage_table||' where rownum < 2';
l_sql := 'select * from '||p_seed_view||' where rownum < 2';
select PARTITION_ID, ORIG_SYSTEM
into l_partitionID, l_partitionName
from WF_DIRECTORY_PARTITIONS
where ORIG_SYSTEM = upper(p_orig_system);
'Populating the select list...');
p_selectList := (p_selectList||''''||g_BaseLanguage||''''||', ');
p_selectList := (p_selectList||
'nvl(LANGUAGE, '''||g_BaseLanguage||'''), ');
p_selectList := (p_selectList||'ROLE_ORIG_SYSTEM, ');
p_selectList := (p_selectList||'ORIG_SYSTEM, ');
p_selectList := (p_selectList||
'nvl(PARENT_ORIG_SYSTEM, ROLE_ORIG_SYSTEM), ');
p_selectList := (p_selectList||
'nvl(PARENT_ORIG_SYSTEM, ORIG_SYSTEM), ');
p_selectList := (p_selectList||'ROLE_ORIG_SYSTEM_ID, ');
p_selectList := (p_selectList||'ORIG_SYSTEM_ID, ');
p_selectList := (p_selectList||
'nvl(PARENT_ORIG_SYSTEM_ID, ROLE_ORIG_SYSTEM_ID), ');
p_selectList := (p_selectList||
'nvl(PARENT_ORIG_SYSTEM_ID, ORIG_SYSTEM_ID), ');
p_selectList := (p_selectList||''''||g_BaseTerritory||''''||', ');
p_selectList := (p_selectList||
'nvl(TERRITORY, '''||g_BaseTerritory||'''), ');
p_selectList := (p_selectList||'USER_FLAG, ');
p_selectList := (p_selectList||'''N'', ');
p_selectList := (p_selectList||''''||to_char(l_partitionID)||''', ');
p_selectList := (p_selectList||to_char(FND_GLOBAL.user_id)||', ');
elsif ((l_stageTableDesc(stageIND).COL_NAME = 'LAST_UPDATED_BY') and
(NOT l_colExists)) then
p_selectList := (p_selectList||to_char(FND_GLOBAL.user_id)||', ');
elsif ((l_stageTableDesc(stageIND).COL_NAME = 'LAST_UPDATE_LOGIN') and
(NOT l_colExists)) then
p_selectList := (p_selectList||to_char(FND_GLOBAL.login_id)||', ');
elsif ((l_stageTableDesc(stageIND).COL_NAME = 'LAST_UPDATE_DATE') and
(NOT l_colExists)) then
p_selectList := (p_selectList||'sysdate, ');
p_selectList := (p_selectList||'sysdate, ');
p_selectList := (p_selectList||'-1, ');
p_selectList := (p_selectList||'ROLE_NAME, ');
p_selectList := (p_selectList||'EXPIRATION_DATE, ');
p_selectList := (p_selectList||'NULL, ');
p_selectList := (p_selectList||l_colName||', ');
p_selectList := rtrim(p_selectList, ', ');
'p_select list is: '||p_selectList);
select user_name from fnd_user
where employee_id = p_orig_system_id;
select user_name from fnd_user
where customer_id = p_orig_system_id;
select user_name from fnd_user
where user_id = p_orig_system_id;
wf_local_synch.update_entmgr('USER',
myuser.user_name,
p_attributes,
p_orig_system);
wf_local_synch.update_entmgr('USER',
myuser.user_name,
p_attributes,
p_orig_system);
wf_local_synch.update_entmgr('USER',
myuser.user_name,
p_attributes,
p_orig_system);
UPDATE WF_LOCAL_ROLES
SET NAME = g_name
-- <6817561>
, LAST_UPDATE_DATE = decode(l_overWrite, 'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
LAST_UPDATED_BY = decode(l_overWrite, 'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = decode(l_overWrite, 'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
-- , CREATED_BY = decode(l_overWrite, 'Y', nvl(g_createdBy, CREATED_BY), CREATED_BY)
-- , CREATION_DATE = decode(l_overWrite, 'Y', nvl(g_creationDate, CREATION_DATE), CREATION_DATE)
--
WHERE NAME = g_oldName
AND PARTITION_ID = l_partitionID;
UPDATE WF_LOCAL_ROLES
SET NAME = g_name
-- <6817561>
, LAST_UPDATE_DATE = decode(l_overWrite, 'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
LAST_UPDATED_BY = decode(l_overWrite, 'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = decode(l_overWrite, 'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
-- , CREATED_BY = decode(l_overWrite, 'Y', nvl(g_createdBy, CREATED_BY), CREATED_BY)
-- , CREATION_DATE = decode(l_overWrite, 'Y', nvl(g_creationDate, CREATION_DATE), CREATION_DATE)
--
WHERE NAME = g_oldName
AND PARTITION_ID = l_partitionID
AND ORIG_SYSTEM = p_orig_system
AND ORIG_SYSTEM_ID = p_orig_system_id;
UPDATE WF_LOCAL_USER_ROLES
SET USER_NAME = g_name,
ROLE_NAME = g_name
-- <6817561>
, LAST_UPDATE_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
LAST_UPDATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
-- , CREATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_createdBy, CREATED_BY), CREATED_BY)
-- , CREATION_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_creationDate, CREATION_DATE), CREATION_DATE)
--
WHERE USER_NAME = g_oldName
AND ROLE_NAME = g_oldName
AND PARTITION_ID = l_partitionID;
UPDATE WF_LOCAL_USER_ROLES
SET USER_NAME = g_Name
-- <6817561>
, LAST_UPDATE_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
LAST_UPDATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
-- , CREATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_createdBy, CREATED_BY), CREATED_BY)
-- , CREATION_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_creationDate, CREATION_DATE), CREATION_DATE)
--
WHERE USER_NAME = g_oldName;
UPDATE WF_USER_ROLE_ASSIGNMENTS
SET USER_NAME=g_name,
ROLE_NAME=g_name,
ASSIGNING_ROLE=g_name
-- <6817561>
, LAST_UPDATE_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
LAST_UPDATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
-- , CREATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_createdBy, CREATED_BY), CREATED_BY)
-- , CREATION_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_creationDate, CREATION_DATE), CREATION_DATE)
--
WHERE ASSIGNING_ROLE=g_oldName
AND USER_NAME=g_oldName
AND RELATIONSHIP_ID=-1
AND PARTITION_ID=l_partitionId;
UPDATE WF_USER_ROLE_ASSIGNMENTS
SET USER_NAME = g_Name
-- <6817561>
, LAST_UPDATE_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
LAST_UPDATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
-- , CREATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_createdBy, CREATED_BY), CREATED_BY)
-- , CREATION_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_creationDate, CREATION_DATE), CREATION_DATE)
--
WHERE USER_NAME = g_oldName;
DELETE from WF_LOCAL_ROLES PARTITION (FND_USR) WR
WHERE WR.ORIG_SYSTEM = 'PER'
AND WR.NAME = g_name
AND WR.ORIG_SYSTEM_ID <> g_employeeID
Returning WR.ORIG_SYSTEM_ID , last_update_date, last_update_login, last_updated_by
into l_oldOrigSystemID, l_oldLastUpdDate, l_oldLastUpdLogin, l_oldLastUpdBy -- <6817561>
;
Select WR.EMAIL_ADDRESS, WR.DISPLAY_NAME, WR.DESCRIPTION,
WR.STATUS
into g_emailAddress, g_displayName, g_description, g_status
From WF_LOCAL_ROLES PARTITION (PER_ROLE) WR
Where WR.ORIG_SYSTEM = 'PER_ROLE'
And WR.ORIG_SYSTEM_ID = g_employeeID;
last_updated_by=>g_lastUpdatedBy,
last_update_login=>g_lastUpdateLogin,
last_update_date=>g_lastUpdateDate,
eventParams=>g_attributes);
if NOT (g_delete) then --No reason to create a deleted user.
l_status := nvl(g_status,'ACTIVE');
l_auxLastUpdDate := g_lastUpdateDate;
l_auxLastUpdLogin := g_lastUpdateLogin;
l_auxLastUpdBy:= g_lastUpdatedBy;
select decode(l_overWrite, 'Y', nvl(g_expDate, p_expiration_date), WR.EXPIRATION_DATE),
nvl(p_start_date, WR.START_DATE)
into p_aux_exp_date, p_aux_start_date
from WF_LOCAL_ROLES PARTITION (PER_ROLE) WR
where WR.ORIG_SYSTEM_ID = g_employeeID;
last_updated_by=>l_auxLastUpdBy,
last_update_login=>l_auxLastUpdLogin,
creation_date=>g_creationDate,
last_update_date=>l_auxLastUpdDate);
Delete from WF_LOCAL_ROLES PARTITION (FND_USR) WR
Where WR.ORIG_SYSTEM = p_orig_system
And WR.ORIG_SYSTEM_ID = p_orig_system_id
returning last_update_date, last_update_login, last_updated_by
into l_oldLastUpdDate, l_oldLastUpdLogin, l_oldLastUpdBy -- <6817561>
;
g_origSystemID, g_lastUpdateDate,
g_lastUpdatedBy, g_lastUpdateLogin
, g_overWrite_UserRoles -- <6817561>
);
update wf_local_roles
set last_update_date = l_oldLastUpdDate,
last_updated_by = l_oldLastUpdBy,
last_update_login = l_oldLastUpdLogin
where name = g_name
and orig_system = g_origSystem
and orig_system_id = g_origSystemID;
elsif (g_overWrite_UserRoles) then -- <6817561> needed to update std WHO columns
update wf_local_user_roles
set last_update_date = nvl(g_lastUpdateDate,last_update_date),
last_updated_by = nvl(g_lastUpdatedBy, last_updated_by),
last_update_login = nvl(g_lastUpdateLogin, last_update_login)
where user_name = g_name
and user_orig_system=g_origSystem
and user_orig_system_id= g_origSystemID;
update wf_user_role_assignments
set last_update_date = nvl(g_lastUpdateDate,last_update_date),
last_updated_by = nvl(g_lastUpdatedBy, last_updated_by),
last_update_login = nvl(g_lastUpdateLogin, last_update_login)
where user_name = g_name
and user_orig_system=g_origSystem
and user_orig_system_id= g_origSystemID; --
/* WF_DIRECTORY.ReassignUserRoles was updated to handle the
self-references
begin
WF_DIRECTORY.SetUserRoleAttr(user_name=>g_name,
role_name=>g_name,
start_date=>to_date(NULL),
end_date=>sysdate,
user_orig_system=>'PER',
user_orig_system_id=>l_oldOrigSystemID,
role_orig_system=>'PER',
role_orig_system_id=>l_oldOrigSystemID,
OverWrite=>FALSE,
last_updated_by=>g_lastUpdatedBy,
last_update_login=>g_lastUpdateLogin,
last_update_date=>g_lastUpdateDate);
g_origSystemID, g_lastUpdateDate,
g_lastUpdatedBy, g_lastUpdateLogin
, g_overWrite_UserRoles -- <6817561>
);
Delete from WF_LOCAL_ROLES PARTITION (FND_USR) WR
Where WR.ORIG_SYSTEM = 'PER'
And WR.NAME = g_name
Returning WR.ORIG_SYSTEM_ID, last_update_date, last_update_login, last_updated_by
into g_employeeID, l_oldLastUpdDate, l_oldLastUpdLogin, l_oldLastUpdBy; -- <6817561>
l_auxLastUpdDate := g_lastUpdateDate;
l_auxLastUpdLogin := g_lastUpdateLogin;
l_auxLastUpdBy:= g_lastUpdatedBy;
last_updated_by=>l_auxLastUpdBy,
last_update_login=>l_auxLastUpdLogin,
creation_date=>g_creationDate,
last_update_date=>l_auxLastUpdDate);
/* WF_DIRECTORY.ReassignUserRoles was updated to handle the
self-references
begin
WF_DIRECTORY.SetUserRoleAttr(user_name=>g_name,
role_name=>g_name,
end_date=>sysdate,
user_orig_system=>'PER',
user_orig_system_id=>g_employeeID,
role_orig_system=>'PER',
role_orig_system_id=>g_employeeID,
OverWrite=>FALSE,
last_updated_by=>g_lastUpdatedBy,
last_update_login=>g_lastUpdateLogin,
last_update_date=>g_lastUpdateDate);
g_lastUpdateDate, g_lastUpdatedBy,
g_lastUpdateLogin
, g_overWrite_UserRoles -- <6817561>
);
last_updated_by=>g_lastUpdatedBy,
last_update_login=>g_lastUpdateLogin,
last_update_date=>g_lastUpdateDate,
eventParams=>g_attributes);
if NOT (g_delete) then
l_status := nvl(g_status,'ACTIVE');
last_updated_by=>g_lastUpdatedBy,
last_update_login=>g_lastUpdateLogin,
creation_date=>g_creationDate,
last_update_date=>g_lastUpdateDate );
update wf_local_user_roles
set last_update_date = nvl(g_lastUpdateDate,last_update_date),
last_updated_by = nvl(g_lastUpdatedBy, last_updated_by),
last_update_login = nvl(g_lastUpdateLogin, last_update_login)
where user_name = g_name
and user_orig_system=g_origSystem
and user_orig_system_id= g_origSystemID;
update wf_user_role_assignments
set last_update_date = nvl(g_lastUpdateDate,last_update_date),
last_updated_by = nvl(g_lastUpdatedBy, last_updated_by),
last_update_login = nvl(g_lastUpdateLogin, last_update_login)
where user_name = g_name
and user_orig_system=g_origSystem
and user_orig_system_id= g_origSystemID;
last_updated_by=>g_lastUpdatedBy,
last_update_login=>g_lastUpdateLogin,
last_update_date=>g_lastUpdateDate,
eventParams=>g_attributes );
if NOT (g_delete) then
l_status := nvl(g_status,'ACTIVE');
last_updated_by=>g_lastUpdatedBy,
last_update_login=>g_lastUpdateLogin,
creation_date=>g_creationDate,
last_update_date=>g_lastUpdateDate );
if NOT (g_delete) then
--Bug 3064439
propagate_role(p_orig_system, p_orig_system_id, p_attributes,
p_start_date, p_expiration_date);
SELECT WR.NAME, WR.NOTIFICATION_PREFERENCE, WR.LANGUAGE, WR.TERRITORY,
WR.FAX, WR.START_DATE, WR.EXPIRATION_DATE
FROM WF_LOCAL_ROLES PARTITION (FND_USR) WR
WHERE ORIG_SYSTEM = c_orig_system
AND ORIG_SYSTEM_ID = c_orig_system_id;
UPDATE WF_LOCAL_ROLES
SET NAME = g_name
WHERE NAME = g_oldName
AND PARTITION_ID = l_partitionID
AND ORIG_SYSTEM = p_orig_system
AND ORIG_SYSTEM_ID = p_orig_system_id;
UPDATE WF_LOCAL_USER_ROLES
SET ROLE_NAME = g_name
-- <6817561>
, LAST_UPDATE_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
LAST_UPDATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
--
WHERE ROLE_NAME = g_oldName
AND ROLE_ORIG_SYSTEM = p_orig_system
AND ROLE_ORIG_SYSTEM_ID = p_orig_system_id
AND PARTITION_ID = l_partitionID;
UPDATE WF_USER_ROLE_ASSIGNMENTS
SET ASSIGNING_ROLE = g_name
-- <6817561>
, LAST_UPDATE_DATE = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
LAST_UPDATED_BY = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = decode(l_overWrite_UserRoles,'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
--
WHERE ASSIGNING_ROLE = g_oldName
AND PARTITION_ID = l_partitionID;
UPDATE WF_ROLE_HIERARCHIES
SET SUB_NAME = g_name
WHERE SUB_NAME = g_oldName
AND PARTITION_ID = l_partitionID;
UPDATE WF_ROLE_HIERARCHIES
SET SUPER_NAME = g_name
WHERE SUPER_NAME = g_oldName
AND SUPERIOR_PARTITION_ID = l_partitionID;
UPDATE WF_USER_ROLE_ASSIGNMENTS
SET ROLE_NAME = g_name
WHERE ROLE_NAME = g_oldName;
UPDATE WF_LOCAL_USER_ROLES
SET
LAST_UPDATE_DATE = nvl(g_lastUpdateDate, LAST_UPDATE_DATE),
LAST_UPDATED_BY = nvl(g_lastUpdatedBy, LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN)
WHERE ROLE_NAME = g_name
AND ROLE_ORIG_SYSTEM = p_orig_system
AND ROLE_ORIG_SYSTEM_ID = p_orig_system_id
AND PARTITION_ID = l_partitionID;
UPDATE WF_USER_ROLE_ASSIGNMENTS
SET
LAST_UPDATE_DATE = nvl(g_lastUpdateDate, LAST_UPDATE_DATE),
LAST_UPDATED_BY = nvl(g_lastUpdatedBy, LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN)
WHERE ROLE_NAME = g_name
AND ROLE_ORIG_SYSTEM = p_orig_system
AND ROLE_ORIG_SYSTEM_ID = p_orig_system_id
AND PARTITION_ID = l_partitionID;
update WF_LOCAL_ROLES
set ORIG_SYSTEM_ID=g_employeeID
where ORIG_SYSTEM_ID=p_orig_system_id
and ORIG_SYSTEM=p_orig_system;
update WF_LOCAL_USER_ROLES
set ROLE_ORIG_SYSTEM_ID = g_employeeID,
USER_ORIG_SYSTEM_ID = g_employeeID,
LAST_UPDATE_DATE = decode(l_overWrite,'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
LAST_UPDATED_BY = decode(l_overWrite,'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = decode(l_overWrite,'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
where USER_ORIG_SYSTEM_ID = p_orig_system_id
and USER_ORIG_SYSTEM = p_orig_system
and ROLE_ORIG_SYSTEM_ID = p_orig_system_id
and ROLE_ORIG_SYSTEM = p_orig_system;
update WF_USER_ROLE_ASSIGNMENTS
set ROLE_ORIG_SYSTEM_ID = g_employeeID,
USER_ORIG_SYSTEM_ID = g_employeeID,
LAST_UPDATE_DATE = decode(l_overWrite,'Y', nvl(g_lastUpdateDate, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
LAST_UPDATED_BY = decode(l_overWrite,'Y', nvl(g_lastUpdatedBy, LAST_UPDATED_BY), LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = decode(l_overWrite,'Y', nvl(g_lastUpdateLogin, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
where USER_ORIG_SYSTEM_ID = p_orig_system_id
and USER_ORIG_SYSTEM = p_orig_system
and ROLE_ORIG_SYSTEM_ID = p_orig_system_id
and ROLE_ORIG_SYSTEM = p_orig_system;
last_updated_by=>g_lastUpdatedBy,
last_update_login=>g_lastUpdateLogin,
last_update_date=>g_lastUpdateDate );
g_updateOnly := TRUE;
if (g_updateOnly) then
begin
WF_DIRECTORY.SetRoleAttr( role_name=>g_name,
orig_system=>g_origSystem,
orig_system_id=>g_origSystemID,
display_name=>g_displayName,
description=>g_description,
notification_preference=>g_notificationPref,
language=>g_language,
territory=>g_territory,
email_address=>g_emailAddress,
fax=>g_fax,
expiration_date=>g_expDate,
status=>g_status,
overWrite=>g_overWrite,
start_date=>p_start_date,
parent_orig_system=>g_parentOrigSys,
parent_orig_system_id=>g_parentOrigSysID,
owner_tag=>g_ownerTag,
last_updated_by=>g_lastUpdatedBy,
last_update_login=>g_lastUpdateLogin,
last_update_date=>g_lastUpdateDate );
g_updateOnly := FALSE;
if ((NOT g_delete) AND (NOT g_updateOnly)) then
begin
l_status := nvl(g_status,'ACTIVE');
last_updated_by=>g_lastUpdatedBy,
last_update_login=>g_lastUpdateLogin,
creation_date=>g_creationDate,
last_update_date=>g_lastUpdateDate );
last_updated_by=>g_lastUpdatedBy,
last_update_login=>g_lastUpdateLogin,
last_update_date=>g_lastUpdateDate,
eventParams=>g_attributes );
SELECT name INTO l_roleName
FROM wf_roles
WHERE orig_system = p_role_orig_system
AND orig_system_id = p_role_orig_system_id;
SELECT name INTO l_userName
FROM wf_roles
WHERE orig_system = p_user_orig_system
AND orig_system_id = p_user_orig_system_id;
SELECT user_name, employee_id, 'PER'
INTO l_userName, l_origSystemID, l_origSystem
FROM FND_USER
WHERE USER_ID = p_user_orig_system_id;
p_lastUpdatedBy in number,
p_lastUpdateLogin in number,
p_creationDate in date,
p_lastUpdateDate in date,
p_assignmentReason in varchar2,
p_UpdateWho in boolean,
p_attributes in WF_PARAMETER_LIST_T)
is
l_uorigSys VARCHAR2(30);
last_updated_by=>p_lastUpdatedBy,
last_update_login=>p_lastUpdateLogin,
last_update_date=>p_lastUpdateDate,
assignment_reason=>p_assignmentReason,
updateWho=>p_UpdateWho,
eventParams=>p_attributes);
last_updated_by=>p_lastUpdatedBy,
last_update_login=>p_lastUpdateLogin,
creation_date=>p_creationDate,
last_update_date=>p_lastUpdateDate,
assignment_reason=>p_assignmentReason,
eventParams=>p_attributes );
SELECT INDEX_NAME
FROM DBA_INDEXES
WHERE TABLE_NAME = tabName
AND TABLE_OWNER = tabOwner;
l_selectList VARCHAR2(1000);
p_selectList=>l_selectList)) then
-- l_selectList is controlled by us
-- g_parallel must not be varchar
-- p_roletlview came from wf_directory_partitions
-- BINDVAR_SCAN_IGNORE[4]
if g_logging='LOGGING' then
l_sql := 'insert ';
l_sql := 'insert /*+ append parallel(T, '||to_char(g_parallel)||') */';
l_columnList||') select /*+ parallel(R, '||
to_char(g_parallel)||') */ '||l_selectList||
' from '||p_roletlview;
SELECT INDEX_NAME
FROM DBA_INDEXES
WHERE TABLE_NAME = tabName
AND TABLE_OWNER = tabOwner;
l_selectList VARCHAR2(1000);
SELECT trim(role_view) , trim(role_tl_view)
INTO l_roleView ,l_role_tl_view
FROM wf_directory_partitions
WHERE partition_id = l_partitionID;
p_selectList=>l_selectList)) then
-- g_parallel must not be varchar2
-- wf_schema came from wf_resources
-- l_selectList is controlled by us
-- l_partitionName came from wf_directory_partitions
-- BINDVAR_SCAN_IGNORE[5]
if g_logging='LOGGING' then
l_sql := 'insert ';
l_sql := 'insert /*+ append parallel(T, '||to_char(g_parallel)||') */';
'('||l_columnList||') select /*+ parallel(R, '||
to_char(g_parallel)||') */ '||l_selectList ||' from '||
nvl(l_roleView, 'WF_'||l_partitionName||'_ROLES R ' );
SELECT INDEX_NAME
FROM DBA_INDEXES
WHERE TABLE_NAME = tabName
AND TABLE_OWNER = tabOwner;
l_selectList VARCHAR2(1000);
SELECT trim(user_role_view)
INTO l_userRoleView
FROM wf_directory_partitions
WHERE partition_id = l_partitionID;
p_selectList=>l_selectList)) then
-- g_parallel must be number
-- wf_schema came from wf_resources
-- l_columnList came from DBMS_SQL.describe_columns()
-- l_selectList controlled by us
-- l_userRoleView came from wf_directory_partitions
-- l_partitionName came from wf_directory_partitions
-- BINDVAR_SCAN_IGNORE[5]
if g_logging='LOGGING' then
l_sql:='insert ';
l_sql := 'insert /*+ append parallel(T, '||to_char(g_parallel)||') */';
'( '||l_columnList||' ) select /*+ parallel(R, '||
to_char(g_parallel)||') */ ' ||l_selectList||' from '||
nvl(l_userRoleView, 'WF_'||l_partitionName||'_UR R ' );
l_sql := 'insert ';
l_sql := 'insert /*+ append parallel(T, '||to_char(g_parallel)||') */';
'CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, ';
'select /*+ parallel(R, '||
''''||to_char(g_parallel)||''') */ USER_NAME, ROLE_NAME, ';
'LAST_UPDATED_BY,LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, USER_START_DATE, '||
'ROLE_START_DATE, USER_END_DATE, '||
'ROLE_END_DATE, ''D'', '||
''''||to_char(l_partitionID)||''', ASSIGNMENT_REASON from '||
'WF_UR_ASSIGNMENTS_STAGE R';
select distinct lookup_code name
from fnd_lookup_values
where lookup_type = 'FND_WF_ORIG_SYSTEMS'
and language = 'US';
select min(to_number(value))
into g_parallel
from v$parameter
where name in ('parallel_max_servers','cpu_count');
select NLS_LANGUAGE, NLS_TERRITORY
into g_BaseLanguage, g_BaseTerritory
from FND_LANGUAGES
where INSTALLED_FLAG = 'B';
g_trustedRoles.DELETE;
** DeleteCache -
**
** Removes a role from the cache of newly created roles.
** IN
** p_role_name VARCHAR2
*/
PROCEDURE DeleteCache (p_role_name in VARCHAR2)
is
roleIND PLS_INTEGER;
g_trustedRoles.DELETE(roleIND);
p_UpdateWho in BOOLEAN,
p_parallel_processes in number) is
begin
WF_MAINTENANCE.ValidateUserRoles(p_BatchSize,
p_username,
p_rolename,
p_check_dangling,
p_check_missing_ura,
p_UpdateWho,
p_parallel_processes);
p_UpdateWho in varchar2,
p_parallel_processes in number) is
l_checkDangling BOOLEAN;
l_UpdateWho BOOLEAN;
if(nvl(p_UpdateWho,'N')='Y') then
l_UpdateWho := TRUE;
l_UpdateWho := FALSE;
l_checkMissingURA,l_UpdateWho, p_parallel_processes);
select name, orig_system, orig_system_id
from wf_local_roles
where orig_system in ('FND_USR', 'PER')
and notification_preference = 'DISABLED'
and nvl(EXPIRATION_DATE, sysdate+1) > sysdate;
select name, orig_system, orig_system_id
from wf_local_roles
where orig_system not in ('FND_USR', 'PER')
and notification_preference = 'DISABLED'
and nvl(EXPIRATION_DATE, sysdate+1) > sysdate;