[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select security_group_key
into secgrpkey
from fnd_security_groups
where security_group_id = x_secgrp_id;
select application_short_name
into appsname
from fnd_application
where application_id = x_resp_appl_id;
select responsibility_key
into respkey
from fnd_responsibility
where application_id = x_resp_appl_id
and responsibility_id = x_resp_id;
/* like inline inside a SQL select statement */
function Role_Name_from_Resp_No_Exc(
x_resp_id in number,
x_resp_appl_id in number,
x_secgrp_id in number)
return varchar2 is
begin
return fnd_user_resp_groups_api.Role_Name_from_Resp(
x_resp_id,
x_resp_appl_id,
x_secgrp_id);
select null
into dummy
from wf_all_user_roles
where user_name = username
and role_name = rolename
and rownum = 1;
select null
into dummy
from wf_all_user_roles
where user_name = username
and role_name = rolename
and direct_flag = 'D'
and rownum = 1;
select null
into dummy
from wf_all_user_roles
where user_name = username
and role_name = rolename
and direct_flag = 'I'
and rownum = 1;
select user_name
into username
from fnd_user
where user_id = assignment_exists.user_id;
select 'Y'
into x_status
from dual
where exists
(select null
from fnd_user u,
fnd_user_resp_groups urg,
fnd_responsibility r
where u.user_id = p_user_id
and sysdate between u.start_date and nvl(u.end_date, sysdate)
and urg.user_id = u.user_id
and urg.responsibility_application_id = p_resp_appl_id
and urg.responsibility_id = p_responsibility_id
and urg.security_group_id in (-1, p_security_group_id)
/*NOT NEEDED: and sysdate between urg.start_date and nvl(urg.end_date,sysdate)*/
and r.application_id = urg.responsibility_application_id
and r.responsibility_id = urg.responsibility_id
and sysdate between r.start_date and nvl(r.end_date, sysdate));
select start_date,
end_date
from wf_all_user_role_assignments --BUG5467610
where user_name = user
and role_name = role
and rownum = 1
for update of start_date nowait;
select user_name
into username
from fnd_user
where user_id = x_user_id;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
procedure Insert_Assignment(
user_id in number,
responsibility_id in number,
responsibility_application_id in number,
security_group_id in number,
start_date in date,
end_date in date,
description in varchar2)
is
sgid number;
select user_name
into username
from fnd_user
where user_id = Insert_assignment.user_id;
select application_short_name
into appsname
from fnd_application
where application_id = responsibility_application_id;
/* we can't insert a duplicate. */
result := assignment_check(username, rolename, 'D');
fnd_message.set_name('FND', 'FND_CANT_INSERT_USER_ROLE');
'FND_USER_RESP_GROUPS_API.Insert_Assignment');
p_lastUpdatedBy => fnd_global.user_id,
p_lastUpdateLogin => 0,
p_creationDate => sysdate, /*Bug3626390 sysdate*/
p_lastUpdatedate=> sysdate,
p_assignmentReason=>description);
p_lastUpdatedBy => fnd_global.user_id,
p_lastUpdateLogin => 0,
p_creationDate => sysdate, /*Bug3626390 sysdate*/
p_lastUpdatedate=> sysdate,
p_assignmentReason=>description);
Insert_Assignment.user_id||':'||
Insert_Assignment.responsibility_id,
null, null);
p_value => Insert_Assignment.user_id,
p_parameterlist => l_parameters);
p_value => Insert_Assignment.responsibility_id,
p_parameterlist => l_parameters);
p_value => Insert_Assignment.responsibility_application_id,
p_parameterlist => l_parameters);
wf_event.raise(p_event_name => 'oracle.apps.fnd.user.role.insert',
p_event_key => to_char(Insert_Assignment.user_id)||':'||to_char(Insert_Assignment.responsibility_id)||':'||appsname||':'||to_char(Insert_Assignment.responsibility_application_id),
p_event_data => NULL,
p_parameters => l_parameters,
p_send_date => Sysdate);
Generic_Error('FND_USER_RESP_GROUPS_API.INSERT_ASSIGNMENT',
sqlcode, sqlerrm);
end Insert_Assignment;
procedure Update_Assignment(
user_id in number,
responsibility_id in number,
responsibility_application_id in number,
security_group_id in number,
start_date in date,
end_date in date,
description in varchar2,
update_who_columns in varchar2 default null
/* 'N' = leave old who vals. 'Y' (default) = update who to current*/)
is
sgid number;
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
l_update_who boolean; --Bug5467610
select user_name
into username
from fnd_user
where user_id = Update_assignment.user_id;
select application_short_name
into appsname
from fnd_application
where application_id = responsibility_application_id;
/* Check whether there already is a direct row to update; if not, */
/* update it which is not allowed. */
result := assignment_check(username, rolename,'D');
fnd_message.set_name('FND', 'FND_CANT_UPDATE_USER_ROLE');
'FND_USER_RESP_GROUPS_API.Update_Assignment');
/* If we passed the flag saying to update the who columns */
/* then we set the last_update who columns to current user/date. */
if (update_who_columns = 'Y') then
l_last_updated_by := fnd_global.user_id;
l_last_update_login := 0;
l_last_update_date := sysdate;
l_update_who := TRUE; -- Bug5467610 update who columns.
p_lastUpdatedate=> l_last_update_date,
p_lastUpdatedBy => l_last_updated_by,
p_lastUpdateLogin => l_last_update_login,
p_assignmentReason=>description,
p_updatewho => l_update_who); -- Bug5467610
p_lastUpdatedate=> l_last_update_date,
p_lastUpdatedBy => l_last_updated_by,
p_lastUpdateLogin => l_last_update_login,
p_assignmentReason=>description,
p_updatewho => l_update_who); -- Bug5467610
Update_Assignment.user_id||':'||
Update_Assignment.responsibility_id,
null, null);
p_value => Update_Assignment.user_id,
p_parameterlist => l_parameters);
p_value => Update_Assignment.responsibility_id,
p_parameterlist => l_parameters);
p_value => Update_Assignment.responsibility_application_id,
p_parameterlist => l_parameters);
wf_event.raise(p_event_name => 'oracle.apps.fnd.user.role.update',
p_event_key => to_char(Update_Assignment.user_id)||':'||to_char(Update_Assignment.responsibility_id)||':'||appsname||':'||to_char(Update_Assignment.responsibility_application_id),
p_event_data => NULL,
p_parameters => l_parameters,
p_send_date => Sysdate);
Generic_Error('FND_USER_RESP_GROUPS_API.UPDATE_ASSIGNMENT',
sqlcode, sqlerrm);
end Update_Assignment;
X_LAST_UPDATE_DATE in DATE default sysdate) is
u_id number;
select user_id into u_id
from fnd_user
where user_name = X_USER_NAME;
select application_id into app_id
from fnd_application
where application_short_name = X_APP_SHORT_NAME;
select responsibility_id into resp_id
from fnd_responsibility
where responsibility_key = X_RESP_KEY
and application_id = app_id;
select security_group_id into sgroup_id
from fnd_security_groups
where security_group_key = X_SECURITY_GROUP;
select decode(X_END_DATE,
fnd_load_util.null_value, null,
null, X_END_DATE,
X_END_DATE)
into l_end_date
from dual;
p_lastUpdatedate=> x_last_update_date,
p_lastUpdatedBy => l_owner,
p_lastUpdateLogin => 0,
p_assignmentReason=>X_DESCRIPTION);
p_lastUpdatedBy => l_owner,
p_lastUpdateLogin => 0,
p_creationDate => sysdate, /*Bug3626390 sysdate*/
p_lastUpdatedate=> x_last_update_date,
p_assignmentReason=>X_DESCRIPTION);
update_who_columns in varchar2 default null
/* 'N' = leave old who vals. 'Y' (default) = update who to current*/)
is
sgid number;
Fnd_User_Resp_Groups_Api.Update_Assignment(
Upload_Assignment.user_id,
Upload_Assignment.responsibility_id,
Upload_Assignment.responsibility_application_id,
Upload_Assignment.sgid,
Upload_Assignment.start_date,
Upload_Assignment.end_date,
Upload_Assignment.description,
update_who_columns);
Fnd_User_Resp_Groups_Api.Insert_Assignment(
Upload_Assignment.user_id,
Upload_Assignment.responsibility_id,
Upload_Assignment.responsibility_application_id,
Upload_Assignment.sgid,
Upload_Assignment.start_date,
Upload_Assignment.end_date,
Upload_Assignment.description);
my_lastupdatedate date;
my_lastupdatedby number;
my_lastupdatelogin number;
select responsibility_key
into l_respkey
from fnd_responsibility
where responsibility_id = respid
and application_id = appid;
select security_group_key
into l_secgrpkey
from fnd_security_groups
where security_group_id = secgrpid;
select application_short_name
into applsname
from fnd_application
where application_id = appid;
select responsibility_name, description
into resp_name, descr
from fnd_responsibility_tl
where responsibility_id = respid
and application_id = appid
and language = (select language_code
from fnd_languages
where installed_flag = 'B');
/* Don't need the name for STANDARD, so skip the select */
if (secgrpid <> 0) then
begin
select security_group_name
into secgrp_name
from fnd_security_groups_tl
where security_group_id = secgrpid
and language = (select language_code
from fnd_languages
where installed_flag = 'B');
select start_date, end_date,
created_by, creation_date,
last_updated_by, last_update_date, last_update_login
into my_start, my_exp, my_createdby, my_creationdate,
my_lastupdatedby, my_lastupdatedate, my_lastupdatelogin
from fnd_responsibility
where responsibility_id = respid
and application_id = appid;
wf_event.AddParameterToList('LAST_UPDATED_BY',my_lastupdatedby,wf_parameters);
wf_event.AddParameterToList('LAST_UPDATE_DATE',
to_char(my_lastupdatedate,WF_CORE.canonical_date_mask),wf_parameters);
wf_event.AddParameterToList('LAST_UPDATE_LOGIN',
my_lastupdatelogin,wf_parameters);
wf_event.AddParameterToList('LAST_UPDATED_BY',my_lastupdatedby,wf_parameters);
wf_event.AddParameterToList('LAST_UPDATE_DATE',
to_char(my_lastupdatedate,WF_CORE.canonical_date_mask), wf_parameters);
wf_event.AddParameterToList('LAST_UPDATE_LOGIN',
my_lastupdatelogin, wf_parameters);
select security_group_id,
security_group_key
from fnd_security_groups;
select application_id,
responsibility_id,
responsibility_key,
start_date,
end_date
from fnd_responsibility;
select application_id,
responsibility_id,
responsibility_key,
start_date,
end_date
from fnd_responsibility;
select fu.user_name,
secgrp.security_group_key,
app.application_short_name,
resp.responsibility_key,
resp.start_date resp_start_date,
resp.end_date resp_end_date,
furgo.user_id,
furgo.responsibility_id,
furgo.responsibility_application_id,
furgo.start_date,
furgo.end_date,
furgo.security_group_id,
furgo.created_by,
furgo.creation_date,
furgo.last_updated_by,
furgo.last_update_date,
furgo.last_update_login
from fnd_user_resp_groups_old furgo,
fnd_user fu,
fnd_application app,
fnd_responsibility resp,
fnd_security_groups secgrp
where furgo.user_id = fu.user_id
and furgo.responsibility_id = resp.responsibility_id
and furgo.responsibility_application_id
= resp.application_id
and furgo.responsibility_application_id
= app.application_id
and furgo.security_group_id = secgrp.security_group_id;
select name
into dummy
from wf_local_roles partition (FND_RESP)
where name = rolename
and rownum = 1;
select role_name
into dummy
from wf_all_user_roles waur
where waur.role_name = rolename
and waur.user_name = rowrec.user_name
and ( (waur.start_date = rowrec.start_date)
OR((waur.start_date is NULL) AND (rowrec.start_date is NULL)))
and ( (waur.expiration_date = rowrec.end_date)
OR((waur.expiration_date is NULL) AND(rowrec.end_date is NULL)));
'USER_ROLE FOUND. Not inserting.');
'USER_ROLE Not FOUND. Need to insert');
p_lastUpdatedBy => fnd_global.user_id,
p_lastUpdateLogin => 0,
p_creationDate => sysdate,
p_lastUpdatedate=> sysdate);
select security_group_id,
security_group_key
from fnd_security_groups;
select null
into dummy
from wf_local_roles
where name = rolename
and partition_id = 2
and rownum = 1;