eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ZPB_USER_UPDATE dependencies on ZPB_ACCOUNT_STATES

Line 15: n_status zpb_account_states.account_status%type;

11: t_subid1 bism_subjects.subject_id%type;
12: t_subjecttype1 bism_subjects.subject_type%type;
13: t_subid2 bism_subjects.subject_id%type;
14: t_subjecttype2 bism_subjects.subject_type%type;
15: n_status zpb_account_states.account_status%type;
16: t_objectid bism_objects.object_id%type :='31';
17: n_comboexists number :=0;
18: n_namelength number :=64;
19: n_epbproductid number :=210;

Line 41: FROM zpb_account_states z,

37:
38: CURSOR expired_user_resp_csr
39: IS
40: SELECT z.user_id, z.resp_id
41: FROM zpb_account_states z,
42: fnd_user u
43: WHERE z.user_id = u.user_id
44: AND (u.end_date IS NOT NULL AND u.end_date < SYSDATE)
45: AND z.business_area_id = p_business_area_id

Line 48: FROM zpb_account_states z,

44: AND (u.end_date IS NOT NULL AND u.end_date < SYSDATE)
45: AND z.business_area_id = p_business_area_id
46: UNION
47: SELECT /*+ LEADING (r) */ z.user_id, z.resp_id
48: FROM zpb_account_states z,
49: fnd_user_resp_groups_all u,
50: fnd_responsibility r
51: WHERE z.user_id = u.user_id
52: AND z.resp_id = u.responsibility_id

Line 60: FROM zpb_account_states z,

56: AND z.business_area_id = p_business_area_id
57: AND r.application_id = n_epbproductid
58: UNION
59: SELECT z.user_id, z.resp_id
60: FROM zpb_account_states z,
61: fnd_responsibility u
62: WHERE z.resp_id = u.responsibility_id
63: AND (u.end_date IS NOT NULL AND u.end_date < SYSDATE)
64: AND u.responsibility_key <> 'ZPB_MANAGER_RESP'

Line 71: FROM zpb_account_states z,

67:
68: CURSOR new_user_resp_csr
69: IS
70: SELECT z.user_id, z.resp_id
71: FROM zpb_account_states z,
72: fnd_user u
73: WHERE z.user_id = u.user_id
74: AND (u.end_date IS NULL OR u.end_date >= SYSDATE)
75: AND z.business_area_id = p_business_area_id

Line 78: FROM zpb_account_states z,

74: AND (u.end_date IS NULL OR u.end_date >= SYSDATE)
75: AND z.business_area_id = p_business_area_id
76: UNION
77: SELECT /*+ LEADING (z) */ z.user_id, z.resp_id
78: FROM zpb_account_states z,
79: fnd_user_resp_groups_all u
80: WHERE z.user_id = u.user_id
81: AND (u.end_date IS NULL OR u.end_date >= SYSDATE)
82: AND z.business_area_id = p_business_area_id

Line 86: FROM zpb_account_states z,

82: AND z.business_area_id = p_business_area_id
83: AND responsibility_application_id = n_epbproductid
84: UNION
85: SELECT z.user_id, z.resp_id
86: FROM zpb_account_states z,
87: fnd_responsibility u
88: WHERE z.resp_id = u.responsibility_id
89: AND (u.end_date IS NULL OR u.end_date >= SYSDATE)
90: AND z.business_area_id = p_business_area_id

Line 101: FROM zpb_account_states

97: WHERE a.responsibility_id = b.responsibility_id
98: AND b.application_id = n_epbproductid
99: MINUS
100: SELECT user_id, resp_id
101: FROM zpb_account_states
102: WHERE business_area_id = p_business_area_id;
103:
104: cursor grantedroles is
105: select /*+ LEADING (c) */

Line 129: a.user_id not in (select user_id from zpb_account_states ast

125: b1.subject_name = a.user_name and
126: b1.subject_type = 'u' and
127: b2.subject_name = c.responsibility_key and
128: b2.subject_type = 'g' and
129: a.user_id not in (select user_id from zpb_account_states ast
130: where b1.subject_id = ast.subject_id and
131: b2.subject_id = ast.group_id and
132: ast.business_Area_id = p_business_area_id);
133:

Line 151: -- Cursor is LEADING because very few rows in zpb_account_states

147: select responsibility_key
148: from fnd_responsibility
149: where application_id = n_epbproductid);
150: --
151: -- Cursor is LEADING because very few rows in zpb_account_states
152: -- should returned at all
153: --
154: cursor reinstated is
155: select /*+LEADING (x) */ y.subject_name

Line 156: from zpb_account_states x,

152: -- should returned at all
153: --
154: cursor reinstated is
155: select /*+LEADING (x) */ y.subject_name
156: from zpb_account_states x,
157: bism_subjects y,
158: fnd_user a,
159: fnd_user_resp_groups b,
160: fnd_responsibility c

Line 171: -- replace from bism_groups with from zpb_account_states

167: and c.application_id=n_epbproductid
168: and (a.end_date is null or a.end_date > SYSDATE)
169: and (b.end_date is NULL or b.end_date > SYSDATE);
170:
171: -- replace from bism_groups with from zpb_account_states
172: -- where user_id = u.subject_id
173: -- and group_id = g.subject_id
174: -- and user_id <> group_id
175: cursor revokedroles is

Line 177: from zpb_account_states s,

173: -- and group_id = g.subject_id
174: -- and user_id <> group_id
175: cursor revokedroles is
176: select u.subject_name uname, g.subject_name gname
177: from zpb_account_states s,
178: bism_subjects u,
179: bism_subjects g
180: where s.account_status <> HIDE_ACCOUNT
181: and u.subject_id = s.subject_id

Line 196: zpb_account_states s, bism_subjects u, bism_subjects g

192: -- roles resinstated for an active user.
193: cursor reinstatedroles is
194: select /*+LEADING (s) */ a.user_name uname, c.responsibility_key gname
195: from fnd_user a, fnd_user_resp_groups b, fnd_responsibility c,
196: zpb_account_states s, bism_subjects u, bism_subjects g
197: where a.user_id = b.user_id
198: and (a.end_date is NULL or a.end_date > SYSDATE)
199: and (b.end_date is NULL or b.end_date > SYSDATE)
200: and b.responsibility_id=c.responsibility_id

Line 313: UPDATE zpb_account_states

309: end loop;
310:
311: FOR new_user_resp_rec IN new_user_resp_csr LOOP
312:
313: UPDATE zpb_account_states
314: SET account_status = 10,
315: last_updated_by = fnd_global.user_id,
316: last_update_date = SYSDATE,
317: last_update_login = fnd_global.login_id,

Line 346: INSERT INTO zpb_account_states

342: WHERE a.subject_name = b.responsibility_key
343: AND b.responsibility_id = brand_new_user_resp_rec.resp_id
344: AND a.subject_type = 'g';
345:
346: INSERT INTO zpb_account_states
347: (subject_id,
348: group_id,
349: business_area_id,
350: user_id,

Line 379: UPDATE zpb_account_states

375: END LOOP;
376:
377: FOR expired_user_resp_rec IN expired_user_resp_csr LOOP
378:
379: UPDATE zpb_account_states
380: SET account_status = -10,
381: last_updated_by = fnd_global.user_id,
382: last_update_date = SYSDATE,
383: last_update_login = fnd_global.login_id,

Line 421: update zpb_account_states

417: t_subname:=eachgrant.resp_sub_name;
418:
419:
420: -- could be an existing entry
421: update zpb_account_states
422: set account_status = ADD_ROLE,
423: LAST_UPDATED_BY = fnd_global.USER_ID,
424: LAST_UPDATE_DATE = SYSDATE,
425: LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,

Line 433: delete zpb_account_states

429: and business_area_id = p_business_area_id;
430:
431: if SQL%NOTFOUND then
432: -- delete any obsolete entries
433: delete zpb_account_states
434: where user_id = eachgrant.user_id
435: and resp_id = eachgrant.responsibility_id
436: and business_area_id = p_business_area_id;
437: if SQL%NOTFOUND then

Line 449: insert into zpb_account_states

445: eachgrant.grantee||' - '|| eachgrant.granted_role || ' will be overwritten');
446: end if;
447: end if;
448:
449: insert into zpb_account_states
450: (subject_id,
451: group_id,
452: business_area_id,
453: user_id,

Line 513: -- from zpb_account_states

509: --remove the following code to ensure hidden accounts
510: --do not get reset to expired, bug 2968955
511: -- n_status := EXP_USER;
512: -- for user_acc_stat in (select account_status
513: -- from zpb_account_states
514: -- where subject_id = t_subid1
515: -- and business_area_id = p_business_area_id) loop
516: -- if user_acc_stat.account_status = HIDE_ACCOUNT then
517: -- n_status := HIDE_ACCOUNT;

Line 525: update zpb_account_states

521:
522: -- Do not turn off read access until the user has been re-assigned or deleted.
523: -- mark all existing user accounts as expired
524: --if n_status <> HIDE_ACCOUNT then
525: update zpb_account_states
526: set account_status = EXP_USER,
527: LAST_UPDATED_BY = fnd_global.USER_ID,
528: LAST_UPDATE_DATE = SYSDATE,
529: LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,

Line 552: update zpb_account_states

548: from bism_subjects
549: where subject_name = eachreinstated.subject_name;
550:
551: -- mark all accounts as new
552: update zpb_account_states
553: set account_status = NEW_USER,
554: assignee = null,
555: LAST_UPDATED_BY = fnd_global.USER_ID,
556: LAST_UPDATE_DATE = SYSDATE,

Line 585: update zpb_account_states

581: -- where user_id = t_subid1
582: -- and group_id = t_subid2;
583:
584: -- update the user state table to indicate removed role
585: update zpb_account_states
586: set account_status = RMV_ROLE,
587: LAST_UPDATED_BY = fnd_global.USER_ID,
588: LAST_UPDATE_DATE = SYSDATE,
589: LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,

Line 613: update zpb_account_states

609: from bism_subjects
610: where subject_name = eachreinstatedrole.gname;
611:
612: -- mark all accounts as new
613: update zpb_account_states
614: set account_status = NEW_USER,
615: assignee = null,
616: LAST_UPDATED_BY = fnd_global.USER_ID,
617: LAST_UPDATE_DATE = SYSDATE,

Line 631: update zpb_account_states

627: end if;
628: end loop;
629:
630: -- set status of new Schema Admininstrator accounts to Current
631: update zpb_account_states
632: set account_status = CURRENT_USER
633: where (account_status = ADD_ROLE
634: or account_status = NEW_USER)
635: and business_area_id = p_business_area_id

Line 666: from zpb_account_states

662: -- temp: todo
663: --
664: select subject_id, group_id
665: into l_subject_id, l_group_id
666: from zpb_account_states
667: where user_id = p_user_id
668: and resp_id = p_resp_id
669: and business_area_id = p_business_area_id;
670:

Line 702: FROM zpb_account_states z,

698:
699: CURSOR expired_sec_user_resp_csr
700: IS
701: SELECT z.user_id, z.resp_id
702: FROM zpb_account_states z,
703: fnd_user u
704: WHERE z.user_id = u.user_id
705: AND (u.end_date IS NOT NULL AND u.end_date < SYSDATE)
706: AND z.business_area_id = p_business_area_id

Line 709: FROM zpb_account_states z,

705: AND (u.end_date IS NOT NULL AND u.end_date < SYSDATE)
706: AND z.business_area_id = p_business_area_id
707: UNION
708: SELECT /*+ LEADING (r) */ z.user_id, z.resp_id
709: FROM zpb_account_states z,
710: fnd_user_resp_groups_all u,
711: fnd_responsibility r
712: WHERE z.user_id = u.user_id
713: AND z.resp_id = u.responsibility_id

Line 721: FROM zpb_account_states z,

717: AND z.business_area_id = p_business_area_id
718: AND responsibility_application_id = n_epbproductid
719: UNION
720: SELECT z.user_id, z.resp_id
721: FROM zpb_account_states z,
722: fnd_responsibility u
723: WHERE z.resp_id = u.responsibility_id
724: AND (u.end_date IS NOT NULL AND u.end_date < SYSDATE)
725: AND u.responsibility_key = 'ZPB_MANAGER_RESP'

Line 733: FROM zpb_account_states a,

729: -- Fix for Bug: 5620740
730: CURSOR new_sec_user_resp_csr
731: IS
732: SELECT a.user_id, a.resp_id
733: FROM zpb_account_states a,
734: fnd_user b,
735: fnd_responsibility c,
736: fnd_user_resp_groups d,
737: zpb_busarea_users e

Line 799: update ZPB_ACCOUNT_STATES A

795: regardless of whether the responsibility is currently valid or not.
796: The replaced statement will set the account_status to CURRENT_USER only if
797: the responsibility is valid (i.e not end-dated with end_date < sysdate).
798:
799: update ZPB_ACCOUNT_STATES A
800: set ACCOUNT_STATUS = CURRENT_USER
801: where A.BUSINESS_AREA_ID = p_business_area_id
802: and A.USER_ID in
803: (select B.USER_ID

Line 812: update ZPB_ACCOUNT_STATES A

808: from FND_RESPONSIBILITY C
809: where C.APPLICATION_ID = 210
810: and C.RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP');
811:
812: update ZPB_ACCOUNT_STATES A
813: set ACCOUNT_STATUS = CURRENT_USER
814: where A.BUSINESS_AREA_ID = p_business_area_id
815: and exists (select B.USER_ID
816: from ZPB_BUSAREA_USERS B

Line 835: update ZPB_ACCOUNT_STATES A

831: -- Added the following update statement for Bug: 5077013
832: -- This statement will set the account_status to RMV_ROLE (-10) if the
833: -- responsibility is end-dated with end_date < sysdate.
834:
835: update ZPB_ACCOUNT_STATES A
836: set ACCOUNT_STATUS = RMV_ROLE
837: where A.BUSINESS_AREA_ID = p_business_area_id
838: and (A.RESP_ID in (select C.RESPONSIBILITY_ID
839: from FND_RESPONSIBILITY C, fnd_user_resp_groups_all d

Line 851: update ZPB_ACCOUNT_STATES A

847: from fnd_user fu
848: where nvl(fu.end_date,sysdate) < sysdate
849: and A.user_id = fu.user_id));
850:
851: update ZPB_ACCOUNT_STATES A
852: set A.ACCOUNT_STATUS = ADD_ROLE
853: where A.BUSINESS_AREA_ID = p_business_area_id
854: and A.ACCOUNT_STATUS = RMV_ROLE
855: and (A.RESP_ID in (select C.RESPONSIBILITY_ID

Line 869: UPDATE zpb_account_states

865: ----------------------------------------------------------------------------------*/
866:
867: FOR new_sec_user_resp_rec IN new_sec_user_resp_csr LOOP
868: -- Fix for Bug: 5620740
869: UPDATE zpb_account_states
870: SET account_status = 0,
871: last_updated_by = fnd_global.user_id,
872: last_update_date = SYSDATE,
873: last_update_login = fnd_global.login_id,

Line 884: UPDATE zpb_account_states

880: END LOOP;
881:
882: FOR expired_sec_user_resp_rec IN expired_sec_user_resp_csr LOOP
883:
884: UPDATE zpb_account_states
885: SET account_status = -10,
886: last_updated_by = fnd_global.user_id,
887: last_update_date = SYSDATE,
888: last_update_login = fnd_global.login_id,