1 PACKAGE BODY ZPB_USER_UPDATE AS
2 /* $Header: zpbusersynch.plb 120.20 2007/12/04 16:20:41 mbhat ship $ */
3 procedure synch_users(p_business_area_id NUMBER) as
4
5 b_userexists boolean :=true;
6 b_groupexists boolean :=true;
7 b_writetolog boolean :=false;
8 t_subjecttype bism_subjects.subject_type%type;
9 t_subname bism_subjects.subject_name%type;
10 t_newguid bism_objects.object_id%type := null;
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;
20 n_writepermission number :=20;
21
22 l_subj_user_id bism_subjects.subject_id%type;
23 l_subj_resp_id bism_subjects.subject_id%type;
24 l_user_exists VARCHAR2(1);
25
26 cursor usernames is
27 select /*+ LEADING (c) */ distinct(a.user_name) name, a.user_id
28 from fnd_user a, fnd_user_resp_groups b, fnd_responsibility c
29 where a.user_id=b.user_id
30 and b.responsibility_id=c.responsibility_id
31 and c.application_id=n_epbproductid;
32
33 cursor groups is
34 select responsibility_key role
35 from fnd_responsibility
36 where application_id = n_epbproductid;
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
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
53 AND (u.end_date IS NOT NULL AND u.end_date < SYSDATE)
54 AND r.responsibility_id = u.responsibility_id
55 AND r.responsibility_key <> 'ZPB_MANAGER_RESP'
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'
65 AND z.business_area_id = p_business_area_id
66 AND u.application_id = n_epbproductid;
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
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
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
91 AND u.application_id = n_epbproductid;
92
93 CURSOR brand_new_user_resp_csr
94 IS
95 SELECT /*+ LEADING (b) */ a.user_id, a.responsibility_id resp_id
96 FROM fnd_user_resp_groups a, fnd_responsibility b
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) */
106 a.user_name grantee,
107 a.user_id,
108 c.responsibility_key granted_role,
109 c.responsibility_id,
110 b.creation_date,
111 b1.subject_id user_sub_id,
112 b1.subject_type user_sub_type,
113 b2.subject_id resp_sub_id,
114 b2.subject_type resp_sub_type,
115 b2.subject_name resp_sub_name
116 from fnd_user a,
117 fnd_user_resp_groups b,
118 fnd_responsibility c,
119 bism_subjects b1,
120 bism_subjects b2
121 where a.user_id = b.user_id and
122 (c.end_date is NULL or c.end_date > SYSDATE) and
123 b.responsibility_id=c.responsibility_id and
124 c.application_id=n_epbproductid and
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
134 cursor deleted is
135 select subject_name from bism_subjects
136 where subject_name <> BIBEANS and subject_name <> ZPBUSER
137 minus
138 (select /*+ LEADING (c) */ distinct(a.user_name)
139 from fnd_user a,
140 fnd_user_resp_groups b,
141 fnd_responsibility c
142 where a.user_id=b.user_id
143 and (a.end_date is NULL or a.end_date >= SYSDATE)
144 and b.responsibility_id=c.responsibility_id
145 and c.application_id=n_epbproductid
146 union
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
156 from zpb_account_states x,
157 bism_subjects y,
158 fnd_user a,
159 fnd_user_resp_groups b,
160 fnd_responsibility c
161 where x.subject_id = y.subject_id
162 and x.business_area_id = p_business_area_id
163 and x.account_status in (EXP_USER, HIDE_ACCOUNT)
164 and x.user_id = a.user_id
165 and a.user_id=b.user_id
166 and b.responsibility_id=c.responsibility_id
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
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
182 and g.subject_id = s.group_id
183 and s.business_area_id = p_business_area_id
184 minus
185 (select /*+ LEADING (c) */ a.user_name, c.responsibility_key
186 from fnd_user a, fnd_user_resp_groups b, fnd_responsibility c
187 where a.user_id = b.user_id
188 and (b.end_date is NULL or b.end_date > SYSDATE)
189 and b.responsibility_id=c.responsibility_id
190 and c.application_id=n_epbproductid);
191
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
201 and c.application_id=n_epbproductid
202 and u.subject_id = s.subject_id
203 and g.subject_id = s.group_id
204 and s.business_area_id = p_business_area_id
205 and s.account_status in (EXP_USER, HIDE_ACCOUNT)
206 and a.user_id = s.user_id
207 and c.responsibility_key = g.subject_name;
208
209 begin
210 -- check logging requirement for this module
211 b_writetolog := (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL);
212 --FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME);
213
214 -- loop for adding users to the Catalog from the fnd_user table
215 for each in usernames loop
216 if length(each.name) <= n_namelength then
217 begin
218 b_userexists := true;
219 -- checking user in the Catalog
220 select SUBJECT_NAME,SUBJECT_TYPE into t_subname,t_subjecttype from bism_subjects where subject_name = each.name;
221 -- the following condition should not happen but putting in an additional check
222 if t_subname is null then
223 b_userexists := false;
224 end if;
225
226 exception
227 when no_data_found then
228 b_userexists := false;
229 end;
230
231 if b_userexists = false then
232 t_newguid := bism_utils.get_guid;
233 insert into bism_subjects (subject_id, subject_name, subject_type) values (t_newguid,each.name,'u');
234 insert into bism_groups (user_id, group_id) values(t_newguid,t_newguid);
235
236 if (b_writetolog) then
237 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME,
238 'User with name '|| each.name||' has been added successfully');
239 end if;
240 else
241 if t_subjecttype = 'g' then
242 if (b_writetolog) then
243 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME,
244 'A group already exists with name '|| each.name);
245 end if;
246 else
247 if (b_writetolog) then
248 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME,
249 'User '|| each.name ||' already exists');
250 end if;
251 end if;
252 end if;
253 end if;
254 end loop;
255
256 -- loop for adding groups to the Catalog from the RDBMS
257
258 for eachgroup in groups loop
259 if length(eachgroup.role)>n_namelength then
260 if (b_writetolog) then
261 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME,
262 eachgroup.role||' can not be added to the Catalog because it has more than n_namelength chars');
263 end if;
264 else
265 begin
266 b_groupexists := true;
267
268 -- checking group in the Catalog
269 select SUBJECT_NAME,SUBJECT_TYPE
270 into t_subname,t_subjecttype
271 from bism_subjects
272 where subject_name = eachgroup.role;
273
274 -- the following condition should not happen but putting in an additional check
275 if t_subname is null then
276 b_groupexists := false;
277 end if;
278
279 exception
280 when no_data_found then
281 b_groupexists := false;
282 end;
283
284 if b_groupexists = false then
285 t_newguid := bism_utils.get_guid;
286 insert into bism_subjects (subject_id, subject_name, subject_type) values(t_newguid,eachgroup.role,'g');
287
288 -- give user some default privileges
289 insert into bism_permissions(subject_id, object_id, privilege) values(t_newguid, t_objectid, n_writepermission);
290
291 if (b_writetolog) then
292 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME,
293 'Group with name '|| eachgroup.role||' has been added successfully');
294 end if;
295 else
296 if t_subjecttype = 'u' then
297 if (b_writetolog) then
298 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME,
299 'A user already exists with name '|| eachgroup.role);
300 end if;
301 else
302 if (b_writetolog) then
303 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME,
304 'Group '|| eachgroup.role ||' already exists');
305 end if;
306 end if;
307 end if;
308 end if;
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,
318 account_status_update_date = SYSDATE
319 WHERE business_area_id = p_business_area_id
320 AND user_id = new_user_resp_rec.user_id
321 AND resp_id = new_user_resp_rec.resp_id
322 -- Fix for Bug:5579658
323 -- AND account_status NOT IN (-100,0);
324 AND account_status <> 0;
325
326 END LOOP;
327
328 FOR brand_new_user_resp_rec IN brand_new_user_resp_csr LOOP
329
330 SELECT subject_id
331 INTO l_subj_user_id
332 FROM bism_subjects a,
333 fnd_user b
334 WHERE a.subject_name = b.user_name
335 AND b.user_id = brand_new_user_resp_rec.user_id
336 AND a.subject_type = 'u';
337
338 SELECT subject_id
339 INTO l_subj_resp_id
340 FROM bism_subjects a,
341 fnd_responsibility b
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,
351 resp_id,
352 assignee,
353 account_status,
354 created_by,
355 creation_date,
356 last_updated_by,
360 VALUES
357 last_update_date,
358 last_update_login,
359 account_status_update_date)
361 (l_subj_user_id,
362 l_subj_resp_id,
363 p_business_area_id,
364 brand_new_user_resp_rec.user_id,
365 brand_new_user_resp_rec.resp_id,
366 null,
367 ADD_ROLE,
368 fnd_global.user_id,
369 SYSDATE,
370 fnd_global.user_id,
371 SYSDATE,
372 fnd_global.login_id,
373 SYSDATE);
374
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,
384 account_status_update_date = SYSDATE
385 WHERE business_area_id = p_business_area_id
386 AND user_id = expired_user_resp_rec.user_id
387 AND resp_id = expired_user_resp_rec.resp_id
388 AND account_status <> -100;
389
390 END LOOP;
391 /*----------------------------------------------------------------------------------------------
392 -- Commented out for Bug: 5077013
393 -- loop for adding users to groups within the Catalog from the RDBMS
394 for eachgrant in grantedroles loop
395 begin
396 t_subid1:=eachgrant.user_sub_id;
397 t_subid2:=eachgrant.resp_sub_id;
398 t_subjecttype1:=eachgrant.user_sub_type;
399 t_subjecttype2:=eachgrant.resp_sub_type;
400
401 -- only users can belong to groups
402 if t_subjecttype1 <> 'u' then
403 if (b_writetolog) then
404 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME,
405 eachgrant.grantee||' is not a user. It can not be added to a group');
406 end if;
407 elsif t_subjecttype2 <> 'g' then
408 if (b_writetolog) then
409 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME,
410 eachgrant.granted_role||' is not a group. Users can not be added to it');
411 end if;
412 else
413 if t_subid1 is not null and t_subid2 is not null then
414 --insert into bism_groups (user_id, group_id) values(t_subid1,t_subid2);
415
416 -- update user state to indicate a new role has been added
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,
426 ACCOUNT_STATUS_UPDATE_DATE = SYSDATE
427 where subject_id = t_subid1
428 and group_id = t_subid2
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
438 if (b_writetolog) then
439 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME, 'A new user ' ||eachgrant.grantee||
440 ' will be added to the group '|| eachgrant.granted_role);
441 end if;
442 else
443 if (b_writetolog) then
444 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME, 'The existing user-group entry '||
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,
454 resp_id,
455 assignee,
456 account_status,
457 CREATED_BY,
458 CREATION_DATE,
459 LAST_UPDATED_BY,
460 LAST_UPDATE_DATE,
461 LAST_UPDATE_LOGIN,
462 ACCOUNT_STATUS_UPDATE_DATE)
463 values(t_subid1,
464 t_subid2,
465 p_business_area_id,
466 eachgrant.user_id,
467 eachgrant.responsibility_id,
468 null,
469 ADD_ROLE,
473 SYSDATE,
470 fnd_global.USER_ID,
471 SYSDATE,
472 fnd_global.USER_ID,
474 fnd_global.LOGIN_ID,
475 SYSDATE);
476 end if;
477
478 if (b_writetolog) then
479 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME,
480 'User '||eachgrant.grantee||' has been added to the group '|| eachgrant.granted_role);
481 end if;
482 else
483 if (b_writetolog) then
484 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME,
485 'Subject ids are null for the relationship between '||eachgrant.grantee||
486 ' and '||eachgrant.granted_role);
487 end if;
488 end if;
489 end if;
490
491 exception
492 when no_data_found then
493 if (b_writetolog) then
494 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME,
495 'Either user '||eachgrant.grantee||' does not exist or group '||
496 eachgrant.granted_role||' does not exist');
497 end if;
498 end;
499
500 end loop;
501
502 -- delete users and groups that are no longer in the EPB domain
503 for eachdeleted in deleted loop
504
505 select subject_id into t_subid1
506 from bism_subjects
507 where subject_name = eachdeleted.subject_name;
508
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;
518 -- exit;
519 -- end if;
520 -- end loop;
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,
530 ACCOUNT_STATUS_UPDATE_DATE = SYSDATE
531 -- Commented out for Bug: 5007124
532 -- HAS_READ_ACCESS = 0
533 where subject_id = t_subid1
534 and business_area_id = p_business_area_id
535 and account_status <> HIDE_ACCOUNT;
536 --end if;
537
538 if (b_writetolog) then
539 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME,
540 'Deleted '||eachdeleted.subject_name);
541 end if;
542
543 end loop;
544
545 -- reset reinstated(unexpired) users
546 for eachreinstated in reinstated loop
547 select subject_id into t_subid1
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,
557 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,
558 ACCOUNT_STATUS_UPDATE_DATE = SYSDATE
559 where subject_id = t_subid1
560 and account_status <> CURRENT_USER
561 and business_area_id = p_business_area_id;
562
563 if (b_writetolog) then
564 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME,
565 'Reinstated '||eachreinstated.subject_name);
566 end if;
567 end loop;
568
569 -- remove users from groups whose relationship no longer exists in the EPB domain
570 for eachrevoked in revokedroles loop
571
572 select subject_id into t_subid1
573 from bism_subjects
574 where subject_name = eachrevoked.uname;
575
576 select subject_id into t_subid2
577 from bism_subjects
578 where subject_name = eachrevoked.gname;
579
580 -- delete bism_groups
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,
593 and business_area_id = p_business_area_id
590 ACCOUNT_STATUS_UPDATE_DATE = SYSDATE
591 where subject_id = t_subid1
592 and group_id = t_subid2
594 and not(account_status = EXP_USER or account_status = HIDE_ACCOUNT);
595
596 if (b_writetolog) then
597 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME,
598 'Deleted relationship between user '||eachrevoked.uname||' and group '||eachrevoked.gname);
599 end if;
600 end loop;
601
602 -- reset reinstated(unexpired) roles for active users
603 for eachreinstatedrole in reinstatedroles loop
604 select subject_id into t_subid1
605 from bism_subjects
606 where subject_name = eachreinstatedrole.uname;
607
608 select subject_id into t_subid2
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,
618 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,
619 ACCOUNT_STATUS_UPDATE_DATE = SYSDATE
620 where subject_id = t_subid1
621 and group_id = t_subid2
622 and business_area_id = p_business_area_id;
623
624 if (b_writetolog) then
625 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, MODULE_NAME,
626 'Reinstated '||eachreinstatedrole.uname||'-'||eachreinstatedrole.gname);
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
636 and resp_id = (
637 select unique(responsibility_id)
638 from fnd_responsibility
639 where responsibility_key = SCHEMA_ADMIN);
640
641 ----------------------------------------------------------------------------------------------------------------*/
642 --remove expired admin accounts from ZPB_BUSAREA_USERS table and then ...
643 update_admin_entries(p_business_area_id);
644 --
645 -- Catch new users who have already been added to BA:
646 --
647 SYNCH_SECURITY_USERS(p_business_area_id);
648
649 end synch_users;
650
651 procedure init_user_session (p_user_id in number,
652 p_resp_id in number,
653 p_business_area_id in number) is
654
655 l_subject_id bism_subjects.subject_id%type;
656 l_group_id bism_subjects.subject_id%type;
657
658 begin
659
660 savepoint init_user_session;
661 --
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
671 --delete any existing entries for this user
672 delete from bism_groups
673 where user_id = l_subject_id
674 and user_id <> group_id;
675
676 --exception
677 -- when no_data_found then
678 --do nothing
679
680 insert into bism_groups
681 (user_id, group_id)
682 values(l_subject_id, l_group_id);
683
684 --exception
685 -- when no_data_found then
686 -- rollback to init_user_session;
687
688 end init_user_session;
689
690 --
691 -- Procedure that will insert rows into ZPB_USERS for any security
692 -- administrators who have access to a business area. Called from
693 -- the business area user's screen
694 --
695 procedure synch_security_users (p_business_area_id in number)
696 is
697 n_epbproductid number :=210;
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
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
714 AND z.resp_id = r.responsibility_id
715 AND (u.end_date IS NOT NULL AND u.end_date < SYSDATE)
716 AND r.responsibility_key = 'ZPB_MANAGER_RESP'
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'
726 AND z.business_area_id = p_business_area_id
727 AND u.application_id = n_epbproductid;
731 IS
728
729 -- Fix for Bug: 5620740
730 CURSOR new_sec_user_resp_csr
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
738 WHERE a.user_id = b.user_id
739 AND a.resp_id = c.responsibility_id
740 AND a.resp_id = d.responsibility_id
741 AND a.user_id = d.user_id
742 AND b.user_id = d.user_id
743 AND c.responsibility_id = d.responsibility_id
744 and a.user_id = e.user_id
745 and b.user_id = e.user_id
746 and d.user_id = e.user_id
747 and a.business_area_id = e.business_area_id
748 AND (b.end_date IS NULL OR b.end_date >= SYSDATE)
749 AND (c.end_date IS NULL OR c.end_date >= SYSDATE)
750 AND (d.end_date IS NULL OR d.end_date >= SYSDATE)
751 AND a.business_area_id = p_business_area_id
752 AND d.responsibility_application_id = n_epbproductid
753 AND c.responsibility_key = 'ZPB_MANAGER_RESP';
754
755 begin
756 insert into ZPB_USERS
757 (BUSINESS_AREA_ID,
758 USER_ID,
759 LAST_BUSAREA_LOGIN,
760 SHADOW_ID,
761 PERSONAL_AW,
762 CREATION_DATE,
763 CREATED_BY,
764 LAST_UPDATE_LOGIN,
765 LAST_UPDATE_DATE,
766 LAST_UPDATED_BY)
767 select /*+ LEADING (c) */
768 p_business_area_id,
769 A.USER_ID,
770 'N',
771 A.USER_ID,
772 'ZPB'||A.USER_ID||'A'||p_business_area_id,
773 sysdate,
774 FND_GLOBAL.USER_ID,
775 FND_GLOBAL.LOGIN_ID,
776 sysdate,
777 FND_GLOBAL.USER_ID
778 from ZPB_BUSAREA_USERS A,
779 FND_USER_RESP_GROUPS B,
780 FND_RESPONSIBILITY C
781 where A.USER_ID = B.USER_ID
782 and B.RESPONSIBILITY_APPLICATION_ID = 210
783 and B.RESPONSIBILITY_ID = C.RESPONSIBILITY_ID
784 and C.APPLICATION_ID = 210
785 and C.RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP'
786 and A.BUSINESS_AREA_ID = p_business_area_id
787 and A.USER_ID not in
788 (select distinct D.USER_ID
789 from ZPB_USERS D
790 where D.BUSINESS_AREA_ID = p_business_area_id);
791
792 /* ----------------------------------------------------------------------------
793 Replaced this update statement with the following for Bug: 5077013.
794 This statement blindly updates the account_status to CURRENT_USER (0)
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
804 from ZPB_BUSAREA_USERS B
805 where B.BUSINESS_AREA_ID = p_business_area_id)
806 and A.RESP_ID =
807 (select C.RESPONSIBILITY_ID
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
817 where B.BUSINESS_AREA_ID = p_business_area_id
818 and b.USER_ID = A.USER_ID)
819 and (A.RESP_ID = (select C.RESPONSIBILITY_ID
820 from FND_RESPONSIBILITY C, fnd_user_resp_groups_all d
821 where C.APPLICATION_ID = 210
822 and C.RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP'
823 and c.responsibility_id = d.responsibility_id
824 and d.user_id = a.user_id
825 and (d.end_date is NULL or d.end_date >= sysdate))
826 and exists (select user_id
827 from fnd_user fu
828 where nvl(fu.end_date,sysdate) >= sysdate
829 and A.user_id = fu.user_id));
830
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
840 where C.APPLICATION_ID = 210
841 and c.responsibility_id = d.responsibility_id
842 and C.RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP'
843 and d.user_id = a.user_id
844 and d.end_date is NOT NULL
845 and d.end_date < sysdate)
846 or A.USER_ID = (select user_id
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
859 and c.responsibility_id = d.responsibility_id
856 from FND_RESPONSIBILITY C, fnd_user_resp_groups_all d
857 where C.APPLICATION_ID = 210
858 and C.RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP'
860 and (d.end_date IS NULL or d.end_date >= sysdate))
861 and A.USER_ID = (select user_id
862 from fnd_user fu
863 where nvl(fu.end_date, sysdate) >= sysdate
864 and A.user_id = fu.user_id));
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,
874 account_status_update_date = SYSDATE
875 WHERE business_area_id = p_business_area_id
876 AND user_id = new_sec_user_resp_rec.user_id
877 AND resp_id = new_sec_user_resp_rec.resp_id
878 AND account_status = 10;
879
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,
889 account_status_update_date = SYSDATE
890 WHERE business_area_id = p_business_area_id
891 AND user_id = expired_sec_user_resp_rec.user_id
892 AND resp_id = expired_sec_user_resp_rec.resp_id
893 AND account_status <> -100;
894
895 END LOOP;
896
897 end synch_security_users;
898
899 --
900 -- Procedure will remove entries in ZPB_BUSAREA_USERS table
901 -- when the administrator account has been expired.
902 --
903 procedure update_admin_entries (p_business_area_id in number)
904 is
905 begin
906
907 delete from zpb_busarea_users
908 where user_id = (
909 select user_id
910 from zpb_busarea_users
911 where business_area_id = p_business_area_id
912 intersect
913 select /*+ LEADING (c) */ distinct(a.user_id)
914 from fnd_user a,fnd_user_resp_groups b,fnd_responsibility c
915 where a.user_id=b.user_id
916 and b.responsibility_id=c.responsibility_id
917 and c.responsibility_key = 'ZPB_MANAGER_RESP'
918 and ((a.end_date < SYSDATE) or
919 (b.end_date < SYSDATE)))
920 and business_area_id = p_business_area_id;
921
922 end update_admin_entries;
923
924 end ZPB_USER_UPDATE;
925
[Home] [Help]