[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select /*+ LEADING (c) */ distinct(a.user_name) name, a.user_id
from fnd_user a, fnd_user_resp_groups b, fnd_responsibility c
where a.user_id=b.user_id
and b.responsibility_id=c.responsibility_id
and c.application_id=n_epbproductid;
select responsibility_key role
from fnd_responsibility
where application_id = n_epbproductid;
SELECT z.user_id, z.resp_id
FROM zpb_account_states z,
fnd_user u
WHERE z.user_id = u.user_id
AND (u.end_date IS NOT NULL AND u.end_date < SYSDATE)
AND z.business_area_id = p_business_area_id
UNION
SELECT /*+ LEADING (r) */ z.user_id, z.resp_id
FROM zpb_account_states z,
fnd_user_resp_groups_all u,
fnd_responsibility r
WHERE z.user_id = u.user_id
AND z.resp_id = u.responsibility_id
AND (u.end_date IS NOT NULL AND u.end_date < SYSDATE)
AND r.responsibility_id = u.responsibility_id
AND r.responsibility_key <> 'ZPB_MANAGER_RESP'
AND z.business_area_id = p_business_area_id
AND r.application_id = n_epbproductid
UNION
SELECT z.user_id, z.resp_id
FROM zpb_account_states z,
fnd_responsibility u
WHERE z.resp_id = u.responsibility_id
AND (u.end_date IS NOT NULL AND u.end_date < SYSDATE)
AND u.responsibility_key <> 'ZPB_MANAGER_RESP'
AND z.business_area_id = p_business_area_id
AND u.application_id = n_epbproductid;
SELECT z.user_id, z.resp_id
FROM zpb_account_states z,
fnd_user u
WHERE z.user_id = u.user_id
AND (u.end_date IS NULL OR u.end_date >= SYSDATE)
AND z.business_area_id = p_business_area_id
UNION
SELECT /*+ LEADING (z) */ z.user_id, z.resp_id
FROM zpb_account_states z,
fnd_user_resp_groups_all u
WHERE z.user_id = u.user_id
AND (u.end_date IS NULL OR u.end_date >= SYSDATE)
AND z.business_area_id = p_business_area_id
AND responsibility_application_id = n_epbproductid
UNION
SELECT z.user_id, z.resp_id
FROM zpb_account_states z,
fnd_responsibility u
WHERE z.resp_id = u.responsibility_id
AND (u.end_date IS NULL OR u.end_date >= SYSDATE)
AND z.business_area_id = p_business_area_id
AND u.application_id = n_epbproductid;
SELECT /*+ LEADING (b) */ a.user_id, a.responsibility_id resp_id
FROM fnd_user_resp_groups a, fnd_responsibility b
WHERE a.responsibility_id = b.responsibility_id
AND b.application_id = n_epbproductid
MINUS
SELECT user_id, resp_id
FROM zpb_account_states
WHERE business_area_id = p_business_area_id;
select /*+ LEADING (c) */
a.user_name grantee,
a.user_id,
c.responsibility_key granted_role,
c.responsibility_id,
b.creation_date,
b1.subject_id user_sub_id,
b1.subject_type user_sub_type,
b2.subject_id resp_sub_id,
b2.subject_type resp_sub_type,
b2.subject_name resp_sub_name
from fnd_user a,
fnd_user_resp_groups b,
fnd_responsibility c,
bism_subjects b1,
bism_subjects b2
where a.user_id = b.user_id and
(c.end_date is NULL or c.end_date > SYSDATE) and
b.responsibility_id=c.responsibility_id and
c.application_id=n_epbproductid and
b1.subject_name = a.user_name and
b1.subject_type = 'u' and
b2.subject_name = c.responsibility_key and
b2.subject_type = 'g' and
a.user_id not in (select user_id from zpb_account_states ast
where b1.subject_id = ast.subject_id and
b2.subject_id = ast.group_id and
ast.business_Area_id = p_business_area_id);
cursor deleted is
select subject_name from bism_subjects
where subject_name <> BIBEANS and subject_name <> ZPBUSER
minus
(select /*+ LEADING (c) */ distinct(a.user_name)
from fnd_user a,
fnd_user_resp_groups b,
fnd_responsibility c
where a.user_id=b.user_id
and (a.end_date is NULL or a.end_date >= SYSDATE)
and b.responsibility_id=c.responsibility_id
and c.application_id=n_epbproductid
union
select responsibility_key
from fnd_responsibility
where application_id = n_epbproductid);
select /*+LEADING (x) */ y.subject_name
from zpb_account_states x,
bism_subjects y,
fnd_user a,
fnd_user_resp_groups b,
fnd_responsibility c
where x.subject_id = y.subject_id
and x.business_area_id = p_business_area_id
and x.account_status in (EXP_USER, HIDE_ACCOUNT)
and x.user_id = a.user_id
and a.user_id=b.user_id
and b.responsibility_id=c.responsibility_id
and c.application_id=n_epbproductid
and (a.end_date is null or a.end_date > SYSDATE)
and (b.end_date is NULL or b.end_date > SYSDATE);
select u.subject_name uname, g.subject_name gname
from zpb_account_states s,
bism_subjects u,
bism_subjects g
where s.account_status <> HIDE_ACCOUNT
and u.subject_id = s.subject_id
and g.subject_id = s.group_id
and s.business_area_id = p_business_area_id
minus
(select /*+ LEADING (c) */ a.user_name, c.responsibility_key
from fnd_user a, fnd_user_resp_groups b, fnd_responsibility c
where a.user_id = b.user_id
and (b.end_date is NULL or b.end_date > SYSDATE)
and b.responsibility_id=c.responsibility_id
and c.application_id=n_epbproductid);
select /*+LEADING (s) */ a.user_name uname, c.responsibility_key gname
from fnd_user a, fnd_user_resp_groups b, fnd_responsibility c,
zpb_account_states s, bism_subjects u, bism_subjects g
where a.user_id = b.user_id
and (a.end_date is NULL or a.end_date > SYSDATE)
and (b.end_date is NULL or b.end_date > SYSDATE)
and b.responsibility_id=c.responsibility_id
and c.application_id=n_epbproductid
and u.subject_id = s.subject_id
and g.subject_id = s.group_id
and s.business_area_id = p_business_area_id
and s.account_status in (EXP_USER, HIDE_ACCOUNT)
and a.user_id = s.user_id
and c.responsibility_key = g.subject_name;
select SUBJECT_NAME,SUBJECT_TYPE into t_subname,t_subjecttype from bism_subjects where subject_name = each.name;
insert into bism_subjects (subject_id, subject_name, subject_type) values (t_newguid,each.name,'u');
insert into bism_groups (user_id, group_id) values(t_newguid,t_newguid);
select SUBJECT_NAME,SUBJECT_TYPE
into t_subname,t_subjecttype
from bism_subjects
where subject_name = eachgroup.role;
insert into bism_subjects (subject_id, subject_name, subject_type) values(t_newguid,eachgroup.role,'g');
insert into bism_permissions(subject_id, object_id, privilege) values(t_newguid, t_objectid, n_writepermission);
UPDATE zpb_account_states
SET account_status = 10,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
account_status_update_date = SYSDATE
WHERE business_area_id = p_business_area_id
AND user_id = new_user_resp_rec.user_id
AND resp_id = new_user_resp_rec.resp_id
-- Fix for Bug:5579658
-- AND account_status NOT IN (-100,0);
SELECT subject_id
INTO l_subj_user_id
FROM bism_subjects a,
fnd_user b
WHERE a.subject_name = b.user_name
AND b.user_id = brand_new_user_resp_rec.user_id
AND a.subject_type = 'u';
SELECT subject_id
INTO l_subj_resp_id
FROM bism_subjects a,
fnd_responsibility b
WHERE a.subject_name = b.responsibility_key
AND b.responsibility_id = brand_new_user_resp_rec.resp_id
AND a.subject_type = 'g';
INSERT INTO zpb_account_states
(subject_id,
group_id,
business_area_id,
user_id,
resp_id,
assignee,
account_status,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
account_status_update_date)
VALUES
(l_subj_user_id,
l_subj_resp_id,
p_business_area_id,
brand_new_user_resp_rec.user_id,
brand_new_user_resp_rec.resp_id,
null,
ADD_ROLE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
SYSDATE);
UPDATE zpb_account_states
SET account_status = -10,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
account_status_update_date = SYSDATE
WHERE business_area_id = p_business_area_id
AND user_id = expired_user_resp_rec.user_id
AND resp_id = expired_user_resp_rec.resp_id
AND account_status <> -100;
update zpb_account_states
set account_status = ADD_ROLE,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,
ACCOUNT_STATUS_UPDATE_DATE = SYSDATE
where subject_id = t_subid1
and group_id = t_subid2
and business_area_id = p_business_area_id;
delete zpb_account_states
where user_id = eachgrant.user_id
and resp_id = eachgrant.responsibility_id
and business_area_id = p_business_area_id;
insert into zpb_account_states
(subject_id,
group_id,
business_area_id,
user_id,
resp_id,
assignee,
account_status,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ACCOUNT_STATUS_UPDATE_DATE)
values(t_subid1,
t_subid2,
p_business_area_id,
eachgrant.user_id,
eachgrant.responsibility_id,
null,
ADD_ROLE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID,
SYSDATE);
for eachdeleted in deleted loop
select subject_id into t_subid1
from bism_subjects
where subject_name = eachdeleted.subject_name;
update zpb_account_states
set account_status = EXP_USER,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,
ACCOUNT_STATUS_UPDATE_DATE = SYSDATE
-- Commented out for Bug: 5007124
-- HAS_READ_ACCESS = 0
where subject_id = t_subid1
and business_area_id = p_business_area_id
and account_status <> HIDE_ACCOUNT;
'Deleted '||eachdeleted.subject_name);
select subject_id into t_subid1
from bism_subjects
where subject_name = eachreinstated.subject_name;
update zpb_account_states
set account_status = NEW_USER,
assignee = null,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,
ACCOUNT_STATUS_UPDATE_DATE = SYSDATE
where subject_id = t_subid1
and account_status <> CURRENT_USER
and business_area_id = p_business_area_id;
select subject_id into t_subid1
from bism_subjects
where subject_name = eachrevoked.uname;
select subject_id into t_subid2
from bism_subjects
where subject_name = eachrevoked.gname;
update zpb_account_states
set account_status = RMV_ROLE,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,
ACCOUNT_STATUS_UPDATE_DATE = SYSDATE
where subject_id = t_subid1
and group_id = t_subid2
and business_area_id = p_business_area_id
and not(account_status = EXP_USER or account_status = HIDE_ACCOUNT);
'Deleted relationship between user '||eachrevoked.uname||' and group '||eachrevoked.gname);
select subject_id into t_subid1
from bism_subjects
where subject_name = eachreinstatedrole.uname;
select subject_id into t_subid2
from bism_subjects
where subject_name = eachreinstatedrole.gname;
update zpb_account_states
set account_status = NEW_USER,
assignee = null,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,
ACCOUNT_STATUS_UPDATE_DATE = SYSDATE
where subject_id = t_subid1
and group_id = t_subid2
and business_area_id = p_business_area_id;
update zpb_account_states
set account_status = CURRENT_USER
where (account_status = ADD_ROLE
or account_status = NEW_USER)
and business_area_id = p_business_area_id
and resp_id = (
select unique(responsibility_id)
from fnd_responsibility
where responsibility_key = SCHEMA_ADMIN);
update_admin_entries(p_business_area_id);
select subject_id, group_id
into l_subject_id, l_group_id
from zpb_account_states
where user_id = p_user_id
and resp_id = p_resp_id
and business_area_id = p_business_area_id;
delete from bism_groups
where user_id = l_subject_id
and user_id <> group_id;
insert into bism_groups
(user_id, group_id)
values(l_subject_id, l_group_id);
SELECT z.user_id, z.resp_id
FROM zpb_account_states z,
fnd_user u
WHERE z.user_id = u.user_id
AND (u.end_date IS NOT NULL AND u.end_date < SYSDATE)
AND z.business_area_id = p_business_area_id
UNION
SELECT /*+ LEADING (r) */ z.user_id, z.resp_id
FROM zpb_account_states z,
fnd_user_resp_groups_all u,
fnd_responsibility r
WHERE z.user_id = u.user_id
AND z.resp_id = u.responsibility_id
AND z.resp_id = r.responsibility_id
AND (u.end_date IS NOT NULL AND u.end_date < SYSDATE)
AND r.responsibility_key = 'ZPB_MANAGER_RESP'
AND z.business_area_id = p_business_area_id
AND responsibility_application_id = n_epbproductid
UNION
SELECT z.user_id, z.resp_id
FROM zpb_account_states z,
fnd_responsibility u
WHERE z.resp_id = u.responsibility_id
AND (u.end_date IS NOT NULL AND u.end_date < SYSDATE)
AND u.responsibility_key = 'ZPB_MANAGER_RESP'
AND z.business_area_id = p_business_area_id
AND u.application_id = n_epbproductid;
SELECT a.user_id, a.resp_id
FROM zpb_account_states a,
fnd_user b,
fnd_responsibility c,
fnd_user_resp_groups d,
zpb_busarea_users e
WHERE a.user_id = b.user_id
AND a.resp_id = c.responsibility_id
AND a.resp_id = d.responsibility_id
AND a.user_id = d.user_id
AND b.user_id = d.user_id
AND c.responsibility_id = d.responsibility_id
and a.user_id = e.user_id
and b.user_id = e.user_id
and d.user_id = e.user_id
and a.business_area_id = e.business_area_id
AND (b.end_date IS NULL OR b.end_date >= SYSDATE)
AND (c.end_date IS NULL OR c.end_date >= SYSDATE)
AND (d.end_date IS NULL OR d.end_date >= SYSDATE)
AND a.business_area_id = p_business_area_id
AND d.responsibility_application_id = n_epbproductid
AND c.responsibility_key = 'ZPB_MANAGER_RESP';
insert into ZPB_USERS
(BUSINESS_AREA_ID,
USER_ID,
LAST_BUSAREA_LOGIN,
SHADOW_ID,
PERSONAL_AW,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
select /*+ LEADING (c) */
p_business_area_id,
A.USER_ID,
'N',
A.USER_ID,
'ZPB'||A.USER_ID||'A'||p_business_area_id,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
sysdate,
FND_GLOBAL.USER_ID
from ZPB_BUSAREA_USERS A,
FND_USER_RESP_GROUPS B,
FND_RESPONSIBILITY C
where A.USER_ID = B.USER_ID
and B.RESPONSIBILITY_APPLICATION_ID = 210
and B.RESPONSIBILITY_ID = C.RESPONSIBILITY_ID
and C.APPLICATION_ID = 210
and C.RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP'
and A.BUSINESS_AREA_ID = p_business_area_id
and A.USER_ID not in
(select distinct D.USER_ID
from ZPB_USERS D
where D.BUSINESS_AREA_ID = p_business_area_id);
Replaced this update statement with the following for Bug: 5077013.
This statement blindly updates the account_status to CURRENT_USER (0)
regardless of whether the responsibility is currently valid or not.
The replaced statement will set the account_status to CURRENT_USER only if
the responsibility is valid (i.e not end-dated with end_date < sysdate).
update ZPB_ACCOUNT_STATES A
set ACCOUNT_STATUS = CURRENT_USER
where A.BUSINESS_AREA_ID = p_business_area_id
and A.USER_ID in
(select B.USER_ID
from ZPB_BUSAREA_USERS B
where B.BUSINESS_AREA_ID = p_business_area_id)
and A.RESP_ID =
(select C.RESPONSIBILITY_ID
from FND_RESPONSIBILITY C
where C.APPLICATION_ID = 210
and C.RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP');
update ZPB_ACCOUNT_STATES A
set ACCOUNT_STATUS = CURRENT_USER
where A.BUSINESS_AREA_ID = p_business_area_id
and exists (select B.USER_ID
from ZPB_BUSAREA_USERS B
where B.BUSINESS_AREA_ID = p_business_area_id
and b.USER_ID = A.USER_ID)
and (A.RESP_ID = (select C.RESPONSIBILITY_ID
from FND_RESPONSIBILITY C, fnd_user_resp_groups_all d
where C.APPLICATION_ID = 210
and C.RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP'
and c.responsibility_id = d.responsibility_id
and d.user_id = a.user_id
and (d.end_date is NULL or d.end_date >= sysdate))
and exists (select user_id
from fnd_user fu
where nvl(fu.end_date,sysdate) >= sysdate
and A.user_id = fu.user_id));
update ZPB_ACCOUNT_STATES A
set ACCOUNT_STATUS = RMV_ROLE
where A.BUSINESS_AREA_ID = p_business_area_id
and (A.RESP_ID in (select C.RESPONSIBILITY_ID
from FND_RESPONSIBILITY C, fnd_user_resp_groups_all d
where C.APPLICATION_ID = 210
and c.responsibility_id = d.responsibility_id
and C.RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP'
and d.user_id = a.user_id
and d.end_date is NOT NULL
and d.end_date < sysdate)
or A.USER_ID = (select user_id
from fnd_user fu
where nvl(fu.end_date,sysdate) < sysdate
and A.user_id = fu.user_id));
update ZPB_ACCOUNT_STATES A
set A.ACCOUNT_STATUS = ADD_ROLE
where A.BUSINESS_AREA_ID = p_business_area_id
and A.ACCOUNT_STATUS = RMV_ROLE
and (A.RESP_ID in (select C.RESPONSIBILITY_ID
from FND_RESPONSIBILITY C, fnd_user_resp_groups_all d
where C.APPLICATION_ID = 210
and C.RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP'
and c.responsibility_id = d.responsibility_id
and (d.end_date IS NULL or d.end_date >= sysdate))
and A.USER_ID = (select user_id
from fnd_user fu
where nvl(fu.end_date, sysdate) >= sysdate
and A.user_id = fu.user_id));
UPDATE zpb_account_states
SET account_status = 0,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
account_status_update_date = SYSDATE
WHERE business_area_id = p_business_area_id
AND user_id = new_sec_user_resp_rec.user_id
AND resp_id = new_sec_user_resp_rec.resp_id
AND account_status = 10;
UPDATE zpb_account_states
SET account_status = -10,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
account_status_update_date = SYSDATE
WHERE business_area_id = p_business_area_id
AND user_id = expired_sec_user_resp_rec.user_id
AND resp_id = expired_sec_user_resp_rec.resp_id
AND account_status <> -100;
procedure update_admin_entries (p_business_area_id in number)
is
begin
delete from zpb_busarea_users
where user_id = (
select user_id
from zpb_busarea_users
where business_area_id = p_business_area_id
intersect
select /*+ LEADING (c) */ distinct(a.user_id)
from fnd_user a,fnd_user_resp_groups b,fnd_responsibility c
where a.user_id=b.user_id
and b.responsibility_id=c.responsibility_id
and c.responsibility_key = 'ZPB_MANAGER_RESP'
and ((a.end_date < SYSDATE) or
(b.end_date < SYSDATE)))
and business_area_id = p_business_area_id;
end update_admin_entries;
end ZPB_USER_UPDATE;