1 package body WF_MAINTENANCE as
2 /* $Header: wfmtn9b.pls 120.12.12010000.2 2008/08/26 20:42:20 alepe ship $ */
3
4 g_CommitCounter NUMBER := 0;
5 g_docommit BOOLEAN := FALSE;
6 procedure PerformCommit;
7
8 -- procedure PropagateChangedName
9 -- Locates all occurrences of an old username and changes to
10 -- the new username.
11 --
12 -- IN:
13 -- OldName - Old Username we are changing from.
14 -- NewName - New Username we are changing to.
15 -- CommitFrequency - Number of updates we perform before commit.
16 --
17 procedure PropagateChangedName(
18 OldName in varchar2,
19 NewName in varchar2,
20 docommit in BOOLEAN )
21
22 is
23
24 l_oldname VARCHAR2(320); -- Local Variable of OldName
25 l_newname VARCHAR2(320); -- Local Variable of NewName
26
27 -- Setting up cursors for tables that would store a role name.
28 -- Some tables have columns named 'READ_ROLE' and 'WRITE_ROLE' that
29 -- are not currently used, so they are not included.
30
31 cursor Items (l_oldname varchar2) is
32 select ITEM_TYPE, ITEM_KEY
33 from WF_ITEMS
34 where OWNER_ROLE = l_oldname;
35
36 cursor ItemActivityStatuses (l_oldname varchar2) is
37 select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY
38 from WF_ITEM_ACTIVITY_STATUSES
39 where ASSIGNED_USER = l_oldname;
40
41 cursor ItemActivityStatuses_H (l_oldname varchar2) is
42 select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY
43 from WF_ITEM_ACTIVITY_STATUSES_H
44 where ASSIGNED_USER = l_oldname;
45
46 cursor Notifications (l_oldname varchar2) is
47 select NOTIFICATION_ID
48 from WF_NOTIFICATIONS
49 where RECIPIENT_ROLE = l_oldname
50 or ORIGINAL_RECIPIENT = l_oldname
51 or more_info_role = l_oldname
52 or from_role = l_oldname
53 or responder = l_oldname;
54
55 cursor ProcessActivities (l_oldname varchar2) is
56 select PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION,
57 INSTANCE_LABEL, INSTANCE_ID
58 from WF_PROCESS_ACTIVITIES
59 where PERFORM_ROLE = l_oldname;
60
61 cursor RoutingRules (l_oldname varchar2) is
62 select RULE_ID
63 from WF_ROUTING_RULES
64 where ROLE = l_oldname
65 or ACTION_ARGUMENT = l_oldname;
66
67 cursor wfComments (l_oldname varchar2) is
68 select rowid
69 from wf_comments
70 where from_role = l_oldname
71 or to_role = l_oldname
72 or proxy_role = l_oldname;
73
74 cursor roleAttributes (l_oldname varchar2) is
75 select wiav.rowid
76 from wf_item_attribute_values wiav, wf_item_attributes wia
77 where wia.type = 'ROLE'
78 and wia.item_type = wiav.item_type
79 and wia.name = wiav.name
80 and wiav.text_value = l_oldname;
81
82 l_roleInfoTAB WF_DIRECTORY.wf_local_roles_tbl_type;
83
84 begin
85 l_newname := upper(substrb(NewName,1,320));
86 l_oldname := upper(substrb(OldName,1,320));
87 g_docommit := docommit;
88
89
90 /* We check to be sure that old name no longer exists (IE: the name was
91 changed). If it is we can go ahead and effect the change.
92
93 If the old name is still active and somewhere else in the directory services
94 we can't change it, so we have to raise the error that the name still
95 exists.
96
97 We then check to be sure the new name is active and ready to receive
98 the records from the old name.
99 */
100
101 WF_DIRECTORY.GetRoleInfo2(l_oldName, l_roleInfoTAB);
102
103 if (l_roleInfoTAB(1).display_name is not NULL) then
104 if not (WF_DIRECTORY.ChangeLocalUsername(l_oldname, l_newname, FALSE)) then
105 WF_CORE.Token('ROLE', l_oldname);
106 WF_CORE.Token('PROCEDURE', 'PropagateChangedName');
107 WF_CORE.Token('PARAMETER', 'OldName');
108 WF_CORE.Raise('WFMTN_ACTIVEROLE');
109 return;
110 end if;
111 end if;
112
113 WF_DIRECTORY.GetRoleInfo2(l_newname, l_roleInfoTAB);
114
115 if (l_roleInfoTAB(1).display_name is null) then
116 WF_CORE.Token('ROLE', l_newname);
117 WF_CORE.Raise('WFNTF_ROLE');
118 return;
119 end if;
120
121
122 /* We will now start looping through the cursors and updating OldName
123 to NewName
124 */
125 for I in Items (l_oldname) loop
126
127 update WF_ITEMS
128 set OWNER_ROLE = l_newname
129 where ITEM_TYPE = i.item_type
130 and item_key = i.item_key;
131
132 PerformCommit();
133 end loop;
134
135 for IAS in ItemActivityStatuses (l_oldname) loop
136
137 update WF_ITEM_ACTIVITY_STATUSES
138 set ASSIGNED_USER = l_newname
139 where ITEM_TYPE = ias.item_type
140 and ITEM_KEY = ias.item_key
141 and PROCESS_ACTIVITY = ias.process_activity;
142
143 PerformCommit();
144 end loop;
145
146 for IASH in ItemActivityStatuses_H (l_oldname) loop
147
148 update WF_ITEM_ACTIVITY_STATUSES_H
149 set ASSIGNED_USER = l_newname
150 where ITEM_TYPE = iash.item_type
151 and ITEM_KEY = iash.item_key
152 and PROCESS_ACTIVITY = iash.process_activity;
153
154 PerformCommit();
155 end loop;
156
157 for NTF in Notifications (l_oldname) loop
158
159 update WF_NOTIFICATIONS
160 set RECIPIENT_ROLE = l_newname
161 where NOTIFICATION_ID = ntf.notification_id
162 and RECIPIENT_ROLE = l_oldname;
163
164 update WF_NOTIFICATIONS
165 set ORIGINAL_RECIPIENT = l_newname
166 where NOTIFICATION_ID = ntf.notification_id
167 and ORIGINAL_RECIPIENT = l_oldname;
168
169 update WF_NOTIFICATIONS
170 set MORE_INFO_ROLE = l_newname
171 where NOTIFICATION_ID = ntf.notification_id
172 and MORE_INFO_ROLE = l_oldname;
173
174 update WF_NOTIFICATIONS
175 set FROM_ROLE = l_newname
176 where NOTIFICATION_ID = ntf.notification_id
177 and FROM_ROLE = l_oldname;
178
179 update WF_NOTIFICATIONS
180 set RESPONDER = l_newname
181 where NOTIFICATION_ID = ntf.notification_id
182 and RESPONDER = l_oldname;
183
184 PerformCommit();
185 end loop;
186
187 for PAct in ProcessActivities (l_oldname) loop
188
189 update WF_PROCESS_ACTIVITIES
190 set PERFORM_ROLE = l_newname
191 where PROCESS_ITEM_TYPE = pact.process_item_type
192 and PROCESS_NAME = pact.process_name
193 and PROCESS_VERSION = pact.process_version
194 and INSTANCE_LABEL = pact.instance_label
195 and INSTANCE_ID = pact.instance_id;
196
197 PerformCommit();
198 end loop;
199
200 for RR in RoutingRules (l_oldname) loop
201
202 update WF_ROUTING_RULES
203 set ROLE = l_newname
204 where RULE_ID = rr.rule_id
205 and ROLE = l_oldname;
206
207 update WF_ROUTING_RULES
208 set ACTION_ARGUMENT = l_newname
209 where RULE_ID = rr.rule_id
210 and ACTION_ARGUMENT = l_oldname;
211
212 PerformCommit();
213 end loop;
214
215 for wcom in wfComments (l_oldname) loop
216 update WF_COMMENTS
217 set FROM_ROLE = l_newname,
218 FROM_USER = l_roleInfoTAB(1).display_name
219 where rowid = wcom.rowid
220 and FROM_ROLE = l_oldName;
221
222 update WF_COMMENTS
223 set TO_ROLE = l_newname,
224 TO_USER = l_roleInfoTAB(1).display_name
225 where rowid = wcom.rowid
226 and TO_ROLE = l_oldName;
227
228 update WF_COMMENTS
229 set PROXY_ROLE = l_newname
230 where rowid = wcom.rowid
231 and PROXY_ROLE = l_oldName;
232
233 PerformCommit();
234 end loop;
235
236 for rAttr in roleAttributes (l_oldname) loop
237 update WF_ITEM_ATTRIBUTE_VALUES
238 set TEXT_VALUE = l_newname
239 where rowid = rAttr.rowid;
240
241 PerformCommit();
242 end loop;
243 commit;
244
245 exception
246 when others then
247 WF_CORE.Context('WF_MAINTENANCE', 'PropagateChangedName', OldName, NewName);
248 raise;
249 end PropagateChangedName;
250
251 -- procedure PerformCommit (private)
252 -- Decides if commit should occur and commits.
253 --
254 -- IN:
255 -- No Parameters.
256 --
257 procedure PerformCommit
258
259 IS
260 BEGIN
261 if (g_docommit) then
262 g_commitCounter := g_commitCounter +1;
263 if (g_commitCounter >= WF_MAINTENANCE.g_CommitFrequency) then
264 commit;
265 g_commitCounter := 0;
266 end if;
267 end if;
268
269 END PerformCommit;
270
271 ------------------------------------------------------------------------------
272 /*
273 ** ValidateUserRoles - Validates and corrects denormalized user and role
274 ** information in user/role relationships.
275 */
276 PROCEDURE ValidateUserRoles(p_BatchSize in NUMBER,
277 p_check_dangling in BOOLEAN,
278 p_check_missing_ura in BOOLEAN,
279 p_UpdateWho in BOOLEAN,
280 p_parallel_processes in number) is
281
282 ColumnsMissing EXCEPTION;
283 TooManyRows EXCEPTION;
284
285 pragma exception_init(ColumnsMissing, -904);
286 pragma exception_init(TooManyRows, -1422);
287
288 TYPE charTab IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
289 TYPE dateTab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
290 TYPE numTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
291 TYPE idTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
292 TYPE origTab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
293 TYPE ownerTAGTAB IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
294
295 l_roleSrcTAB WF_DIRECTORY.roleTable;
296 l_userSrcTAB WF_DIRECTORY.userTable;
297 l_roleDestTAB WF_DIRECTORY.roleTable;
298 l_userDestTAB WF_DIRECTORY.userTable;
299 l_rowIDTAB idTab;
300 l_rowIDSrcTAB idTab;
301 l_rowIDDestTAB idTab;
302 l_stgIDTAB idTab;
303 l_userStartSrcTAB dateTab;
304 l_roleStartSrcTAB dateTab;
305 l_userEndSrcTAB dateTab;
306 l_roleEndSrcTAB dateTab;
307 l_effStartSrcTAB dateTab;
308 l_effEndSrcTAB dateTab;
309 l_AssignTAB charTab;
310 l_userStartDestTAB dateTab;
311 l_roleStartDestTAB dateTab;
312 l_userEndDestTAB dateTab;
313 l_roleEndDestTAB dateTab;
314 l_effStartDestTAB dateTab;
315 l_effEndDestTAB dateTab;
316 l_relIDTAB numTab;
317 l_maxRows number;
318 l_userOrigIDSrcTAB numTab;
319 l_roleOrigIDSrcTAB numTab;
320 l_userOrigIDDestTAB numTab;
321 l_roleOrigIDDEstTAB numTab;
322 l_userOrigSrcTAB origTab;
323 l_roleOrigSrcTAB origTab;
324 l_userOrigDestTAB origTab;
325 l_roleOrigDestTAB origTab;
326 l_assigningRoleSrcTAB WF_DIRECTORY.roleTable;
327 l_asgStartSrcTAB dateTab;
328 l_asgEndSrcTAB dateTab;
329 l_startSrcTAB dateTab;
330 l_endSrcTAB dateTab;
331
332 l_startDestTAB dateTab;
333 l_endDestTAB dateTab;
334 l_partTAB numTab;
335 l_userID number;
336 l_empID number;
337
338 sumTabIndex number;
339 ur_index number;
340 l_eIndex number;
341
342 l_activeAssigned boolean;
343 l_updateDateTAB dateTAB;
344 l_createDateTAB dateTAB;
345 l_updatedByTAB numTAB;
346 l_updateLoginTAB numTAB;
347 l_createdByTAB numTAB;
348 l_parentOrigTAB origTAB;
349 l_parentOrigIDTAB numTAB;
350 l_ownerTAGS ownerTAGTAB;
351 -- <bug 6823723>
352 l_sql varchar2(6000);
353 l_defaultParProc number;
354 l_parallelProc varchar2(5);
355
356 result number;
357 l_lockhandle varchar2(200);
358
359 --Missing records in WF_USER_ROLE_ASSIGNMENTS
360 cursor c_missing_user_role_asg is
361 select user_name, role_name, -1, start_date, expiration_date,
362 created_by, creation_date, last_updated_by, last_update_date,
363 last_update_login, user_start_date, role_start_date,
364 user_end_date, role_end_date, partition_id,
365 effective_start_date, effective_end_date, user_orig_system,
366 user_orig_system_id, role_orig_system, role_orig_system_id,
367 parent_orig_system, parent_orig_system_id, owner_tag
368 from wf_local_user_roles wur
369 where not exists (select null
370 from wf_user_role_assignments wura
371 where wura.user_name = wur.user_name
372 and wura.role_name = wur.role_name);
373
374 --Invalid and Duplicated records in the (FND_USR) partition
375 cursor c_invalid_fnd_users is
376 select wu.rowid, wu.orig_system old_orig_system,
377 wu.orig_system_id old_orig_system_id,
378 decode(nvl(fu.employee_id, -1),-1,'FND_USR','PER') new_orig_system,
379 nvl(fu.employee_id, fu.user_id)
380 from wf_local_roles partition (FND_USR) wu, fnd_user fu
381 where wu.name = fu.user_name
382 and (wu.orig_system <> decode(nvl(fu.employee_id, -1),-1,'FND_USR','PER')
383 or wu.orig_system_id <> nvl(fu.employee_id, fu.user_id));
384
385 -- Records with invalid or duplicate FND_USR/PER references
386 -- in WF_LOCAL_USER_ROLES
387 cursor c_invalOrigSys is
388 select wu.orig_system, wu.orig_system_id,
389 wur.role_orig_system, wur.role_orig_system_id,
390 wur.partition_id, wur.rowid
391 from wf_local_user_roles wur,
392 wf_local_roles partition (FND_USR) wu
393 where wu.name = wur.user_name
394 and wur.user_orig_system in ('FND_USR','PER')
395 and (wur.user_orig_system <> wu.orig_system
396 or wur.user_orig_system_id <> wu.orig_system_id
397 --check for role_orig_system in case of self-reference
398 or (wur.partition_id=1 and (wur.role_orig_system <> wu.orig_system
399 or wur.role_orig_system_id <> wu.orig_system_id)));
400
401 --We will correct the orig_system, orig_system_id information for any
402 --incorrect fnd_usr/per user/role records. This is processed after the
403 --fnd_usr/per records in wf_local_roles are validated. The dates will be
404 --resolved in when c_userRoleAssignments are resolved.
405 cursor c_userSelfReference is
406 select wura.rowid, wur.rowid, wu.start_date, wu.expiration_date,
407 wu.orig_system, wu.orig_system_id
408 from wf_local_user_roles partition (FND_USR) wur,
409 wf_local_roles partition (FND_USR) wu,
410 wf_user_role_assignments partition (FND_USR) wura
411 --Equi-joins to select the proper relationships between the tables
412 where wura.partition_id = wu.partition_id
413 and wura.partition_id = wu.partition_id
414 and wur.user_name = wu.name
415 and wur.role_name = wu.name
416 and wura.assigning_role = wu.name
417 and wura.user_name = wu.name
418 and wura.role_name = wu.name
419 --Criteria to select records that need to be corrected, beginning with
420 --broad checks (if effective dates are null, no reason to check further)
421 --and working down to more specific checks between the orig_system/id
422 and ((wur.effective_start_date is null or
423 wur.effective_end_date is null or
424 wura.effective_start_date is null or
425 wura.effective_end_date is null)
426 or ((wur.user_orig_system <> wu.orig_system) or
427 (wur.user_orig_system_id <> wu.orig_system_id) or
428 (wur.role_orig_system <> wu.orig_system) or
429 (wur.role_orig_system_id <> wu.orig_system_id))
430 or (wura.user_orig_system is null or wura.role_orig_system is null or
431 wura.user_orig_system_id is null or
432 wura.user_orig_system_id is null)
433 or (wura.user_orig_system <> wu.orig_system)
434 or (wura.user_orig_system_id <> wu.orig_system_id)
435 or (wura.role_orig_system <> wu.orig_system)
436 or (wura.role_orig_system_id <> wu.orig_system_id)
437 or (wu.start_date is null and
438 (wur.start_date is not null or
439 wur.user_start_date is not null or
440 wur.role_start_date is not null or
441 wur.effective_start_date <> to_date(1,'J')))
442 or (wu.start_date is not null and
443 (wur.start_date is null or wur.user_start_date is null or
444 wur.role_start_date is null or wur.start_date <> wu.start_date or
445 wur.user_start_date <> wu.start_date or
446 wur.role_start_date <> wu.start_date or
447 wur.effective_start_date <> wu.start_date))
448 or (wu.expiration_date is null and
449 (wur.expiration_date is not null or
450 wur.user_end_date is not null or wur.role_end_date is not null or
451 wur.effective_end_date <> to_date('9999/01/01','YYYY/MM/DD')))
452 or (wu.expiration_date is not null and
453 (wur.expiration_date is null or wur.user_end_date is null or
454 wur.role_end_date is null or
455 wur.expiration_date <> wu.expiration_date or
456 wur.user_end_date <> wu.expiration_date or
457 wur.role_end_date <> wu.expiration_date or
458 wur.effective_end_date <> wu.expiration_date)));
459
460 --Now we will correct other user/role relationships, including the user
461 --orig_system/id information of any record that an fnd_usr/per may be
462 --participating in.
463 cursor c_UserRoleAssignments is
464 select rowid, wura_id,wur_id,role_name,user_name,
465 assigning_role, start_date, end_Date,role_start_date,
466 role_end_date, user_start_date,user_end_date,
467 role_orig_system,role_orig_system_id,
468 user_orig_system, user_orig_system_id,
469 assigning_role_start_date, assigning_role_end_date,
470 effective_start_date, effective_end_date,
471 relationship_id
472 from wf_ur_validate_stg
473 order by ROLE_NAME, USER_NAME;
474
475 CURSOR dangling_UR_refs is
476 select rowid
477 from wf_local_user_roles
478 where user_name not in (select name from wf_local_roles)
479 or role_name not in (select name from wf_local_roles);
480
481 CURSOR dangling_URA_refs is
482 select rowid
483 from wf_user_role_assignments
484 where user_name not in (select name from wf_local_roles)
485 or role_name not in (select name from wf_local_roles);
486
487 l_modulePkg varchar2(240) := 'WF_MAINTENANCE.ValidateUserRoles';
488
489 begin
490 -- Log only
491 -- BINDVAR_SCAN_IGNORE[2]
492 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_modulePkg,
493 'Begin ValidateUserRoles('||p_batchSize||')');
494 --First validate the inbound parameter(s)
495 if (p_BatchSize is NULL or (p_BatchSize < 1)) then
496 l_MaxRows := 10000;
497 else
498 l_MaxRows := p_BatchSize;
499 end if;
500
501 -- Acquire a session lock to ensure that only one instance of the program is
502 -- running at a time.
503 dbms_lock.allocate_unique('WF_MAINTENANCE.ValidateUserRoles',l_lockhandle);
504
505 if (dbms_lock.request(lockhandle=>l_lockhandle,
506 lockmode=>dbms_lock.x_mode,
507 timeout=>0) <> 0) then
508 wf_core.raise('WF_LOCK_FAIL');
509 end if;
510
511 if (p_check_dangling is not null and p_check_dangling) then
512 --Validate that the users and roles who participate in user/role
513 --relationships actually exist.
514 begin
515 <<Dangling_UR_Reference>>
516 loop
517 open dangling_UR_refs;
518 fetch dangling_UR_refs bulk collect into l_rowIDTAB limit l_maxRows;
519 close dangling_UR_refs;
520
521 if (l_rowIDTAB.COUNT > 0) then
522 forall i in l_rowIDTAB.FIRST..l_rowIDTAB.LAST
523 DELETE from WF_LOCAL_USER_ROLES
524 WHERE rowid = l_rowIDTAB(i);
525 commit;
526 end if;
527
528 if (l_rowIDTAB.COUNT < l_maxRows) then
529 exit Dangling_UR_Reference;
530 end if;
531 end loop Dangling_UR_Reference;
532 exception
533 when others then
534 if dangling_UR_refs%ISOPEN then
535 close dangling_UR_refs;
536 end if;
537 raise;
538 end;
539 --Truncate the rowid tab.
540 l_rowIDTAB.DELETE;
541 begin
542 <<Dangling_URA_Reference>>
543 loop
544 open dangling_URA_refs;
545 fetch dangling_URA_refs bulk collect into l_rowIDTAB limit l_maxRows;
546 close dangling_URA_refs;
547
548 if (l_rowIDTAB.COUNT > 0) then
549 forall i in l_rowIDTAB.FIRST..l_rowIDTAB.LAST
550 DELETE from WF_USER_ROLE_ASSIGNMENTS
551 WHERE rowid = l_rowIDTAB(i);
552 commit;
553 end if;
554
555 if (l_rowIDTAB.COUNT < l_maxRows) then
556 exit Dangling_URA_Reference;
557 end if;
558 end loop Dangling_URA_Reference;
559 exception
560 when others then
561 if dangling_URA_refs%ISOPEN then
562 close dangling_URA_refs;
563 end if;
564 raise;
565 end;
566 --Truncate the rowid tab.
567 l_rowIDTAB.DELETE;
568 end if;
569
570 if (p_check_missing_ura is not null and p_check_missing_ura) then
571 --Validate that the users and roles who participate in user/role
572 --relationships actually exist.
573 begin
574 <<Missing_URA_Reference>>
575 loop
576 l_userSrcTAB.DELETE;
577 open c_missing_user_role_asg;
578 fetch c_missing_user_role_asg bulk collect into l_userSrcTAB,
579 l_roleSrcTAB, l_relIDTAB, l_startSrcTAB, l_endSrcTAB,
580 l_createdByTAB, l_createDateTAB, l_updatedByTAB, l_updateDateTAB,
581 l_updateLoginTAB, l_userStartSrcTAB, l_roleStartSrcTAB,
582 l_userEndSrcTAB, l_roleEndSrcTAB, l_partTAB, l_effStartSrcTAB,
583 l_effEndSrcTAB, l_userOrigSrcTAB, l_userOrigIDSrcTAB,
584 l_roleOrigSrcTAB, l_roleOrigIDSrcTAB, l_parentOrigTAB,
585 l_parentOrigIDTAB, l_ownerTAGS
586 limit l_maxRows;
587 close c_missing_user_role_asg;
588
589 if (l_userSrcTAB.COUNT > 0) then
590 begin
591 forall i in l_userSrcTAB.FIRST..l_userSrcTAB.LAST save exceptions
592 insert into WF_USER_ROLE_ASSIGNMENTS (USER_NAME,
593 ROLE_NAME, RELATIONSHIP_ID, ASSIGNING_ROLE, START_DATE,
594 END_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
595 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, USER_START_DATE,
596 ROLE_START_DATE, ASSIGNING_ROLE_START_DATE, USER_END_DATE,
597 ROLE_END_DATE, ASSIGNING_ROLE_END_DATE, PARTITION_ID,
598 EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, USER_ORIG_SYSTEM,
599 USER_ORIG_SYSTEM_ID, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID,
600 PARENT_ORIG_SYSTEM, PARENT_ORIG_SYSTEM_ID, OWNER_TAG)
601 values (l_userSrcTAB(i), l_roleSrcTAB(i), l_relIDTAB(i),
602 l_roleSrcTAB(i), l_startSrcTAB(i), l_endSrcTAB(i),
603 l_createdByTAB(i), l_createDateTAB(i), l_updatedByTAB(i),
604 l_updateDateTAB(i), l_updateLoginTAB(i), l_userStartSrcTAB(i),
605 l_roleStartSrcTAB(i), l_roleStartSrcTAB(i), l_userEndSrcTAB(i),
606 l_roleEndSrcTAB(i), l_roleEndSrcTAB(i), l_partTAB(i),
607 l_effStartSrcTAB(i), l_effEndSrcTAB(i), l_userOrigSrcTAB(i),
608 l_userOrigIDSrcTAB(i), l_roleOrigSrcTAB(i),
609 l_roleOrigIDSrcTAB(i), l_parentOrigTAB(i),
610 l_parentOrigIDTAB(i), l_ownerTAGS(i));
611 commit;
612 exception
613 when others then
614 for j in 1..sql%bulk_exceptions.count loop
615 if (sql%bulk_exceptions(j).ERROR_CODE = 1) then
616 --Ignore a dup_val_on_index. That just means that the
617 --user/role name combination was already assigned during this
618 --job.
619 null;
620 else
621 raise;
622 end if;
623 end loop;
624 end;
625 end if;
626
627 if (l_userSrcTAB.COUNT < l_maxRows) then
628 exit Missing_URA_Reference;
629 end if;
630 end loop Missing_URA_Referencee;
631 exception
632 when others then
633 if c_missing_user_role_asg%ISOPEN then
634 close c_missing_user_role_asg;
635 end if;
636 raise;
637 end;
638 end if;
639
640 --Now we will correct any invalid fnd_usr records in WF_LOCAL_ROLES. This
641 --orig_system can have errors because we routinely have to change the
642 --orig_system, orig_system_id whenever a user is associated or dis-associated
643 --with an employee.
644 begin
645 <<fnd_usr_loop>>
646 loop
647 --Clear the l_rowIDTAB before the next iteration
648 l_rowIDTAB.DELETE;
649 open c_invalid_fnd_users;
650 fetch c_invalid_fnd_users bulk collect into l_rowIDTAB, l_userOrigSrcTAB,
651 l_userOrigIDSrcTAB, l_userOrigDestTAB, l_userOrigIDDestTAB
652 limit l_maxRows;
653 close c_invalid_fnd_users;
654
655 if (l_rowIDTAB.count > 0) then
656 begin
657 forall i in l_rowIDTAB.FIRST..l_rowIDTAB.LAST save exceptions
658 UPDATE WF_LOCAL_ROLES
659 SET orig_system = l_userOrigDestTAB(i),
660 orig_system_id = l_userOrigIDDestTAB(i)
661 WHERE rowid = l_rowIDTAB(i);
662 exception
663 when others then
664 for j in 1..sql%bulk_exceptions.count loop
665 l_eIndex := sql%bulk_exceptions(j).ERROR_INDEX;
666 delete from wf_local_roles
667 where rowid = l_rowIDTAB(l_eIndex);
668 end loop;
669 end;
670 commit;
671 end if;
672 if (l_rowIDTab.count < l_maxRows) then
673 commit;
674 exit fnd_usr_loop;
675 end if;
676 end loop fnd_usr_loop;
677 exception
678 when others then
679 if c_invalid_fnd_users%ISOPEN then
680 close c_invalid_fnd_users;
681 end if;
682 raise;
683 end; --End of duplicate/invalid FND_USR/PER user correction.
684
685 -- Now we correct the FND_USR/PER orig_system values on the user side
686 -- of user/role assignments as well as user-self-references in
687 -- WF_LOCAL_USER_ROLES
688
689 begin
690 <<inval_orig_sys_loop>>
691 loop
692 --Clear the l_rowIDTAB before the next iteration
693 l_rowIDTAB.DELETE;
694 open c_invalOrigSys;
695 fetch c_invalOrigSys bulk collect into l_userOrigSrcTAB,
696 l_userOrigIDSrcTAB, l_roleOrigSrcTAB, l_roleOrigIDSrcTAB,
697 l_partTAB,l_rowIDTAB
698 limit l_maxRows;
699 close c_invalOrigSys;
700
701 if (l_rowIDTAB.count > 0) then
702 for i in l_rowIDTAB.FIRST..l_rowIDTAB.LAST loop
703 -- check whether this is a case of user=role
704 if l_partTAB(i) = 1 then
705 -- set the role_orig_system values as well.
706 l_roleOrigSrcTAB(i) := l_userOrigSrcTAB(i);
707 l_roleOrigIDSrcTAB(i) := l_userOrigIDSrcTAB(i);
708 end if;
709 end loop;
710 --perform the bulk update.. delete duplicates in case of
711 -- dup_val_on_index Exception.
712 begin
713 forall i in l_rowIDTAB.FIRST..l_rowIDTAB.LAST save exceptions
714 UPDATE WF_LOCAL_USER_ROLES
715 SET user_orig_system = l_userOrigSrcTAB(i),
716 user_orig_system_id = l_userOrigIDSrcTAB(i),
717 role_orig_system = l_roleOrigSrcTAB(i),
718 role_orig_system_id = l_roleOrigIDSrcTAB(i)
719 WHERE rowid = l_rowIDTAB(i);
720 exception
721 when others then
722 for j in 1..sql%bulk_exceptions.count loop
723 if (sql%bulk_exceptions(j).ERROR_CODE = 1) then
724 l_eIndex := sql%bulk_exceptions(j).ERROR_INDEX;
725 delete from wf_local_user_roles
726 where rowid = l_rowIDTAB(l_eIndex);
727 end if;
728 end loop;
729 end;
730 commit;
731 end if;
732 if (l_rowIDTab.count < l_maxRows) then
733 commit;
734 exit inval_orig_sys_loop;
735 end if;
736 end loop inval_orig_sys_loop;
737 exception
738 when others then
739 if c_invalOrigSys%ISOPEN then
740 close c_invalOrigSys;
741 end if;
742 raise;
743 end; --End of duplicate/invalid FND_USR/PER correction in WF_LOCAL_USER_ROLES.
744
745 --Next, we correct the corrupt self-reference records in
746 --wf_user_role_Assignments and wf_local_user_Roles.
747 begin
748 <<self_refer_loop>>
749 loop
750 --We will commit on each loop cycle to prevent fetch across commits, we will
751 --close and reopen the cursor on each fetch. This would mean that we are
752 --pulling more than 10000 if we loop more than once and would rather have
753 --a performance impact here than encounter rollback segment problems.
754 --The where criteria of the cursor will not re-select the updated rows so
755 --we do not have to worry about retaining a position.
756 open c_userSelfReference;
757 fetch c_userSelfReference
758 bulk collect into l_rowIDTAB, l_rowIDSrcTAB, l_startSrcTAB,
759 l_endSrcTAB, l_userOrigSrcTAB, l_userOrigIDSrcTAB
760 limit l_maxRows;
761 close c_userSelfReference;
762
763 --We now have pl/sql tables in memory that we can update with the new
764 --values. So we loop through them and begin the processing.
765 if (l_rowIDTAB.COUNT < 1) then
766 exit self_refer_loop;
767 end if;
768
769 --We now have a complete series of pl/sql tables with
770 --all of the start/end dates and calculated effective start/end dates
771 --We can then issue the bulk update.
772 begin
773 if (p_UpdateWho is not null and p_UpdateWho) then
774 forall tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST save exceptions
775 update WF_USER_ROLE_ASSIGNMENTS
776 set ROLE_START_DATE = l_StartSrcTAB(tabIndex),
777 ROLE_END_DATE = l_EndSrcTAB(tabIndex),
778 USER_START_DATE = l_StartSrcTAB(tabIndex),
779 USER_END_DATE = l_EndSrcTAB(tabIndex),
780 START_DATE = l_StartSrcTAB(tabIndex),
781 END_DATE = l_EndSrcTAB(tabIndex),
782 EFFECTIVE_START_DATE = nvl(l_StartSrcTAB(tabIndex),
783 to_date(1,'J')),
784 EFFECTIVE_END_DATE = nvl(l_EndSrcTAB(tabIndex),
785 to_date('9999/01/01', 'YYYY/MM/DD')),
786 ASSIGNING_ROLE_START_DATE = l_StartSrcTAB(tabIndex),
787 ASSIGNING_ROLE_END_DATE = l_EndSrcTAB(tabIndex),
788 USER_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
789 ROLE_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
790 USER_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex),
791 ROLE_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex),
792 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
793 LAST_UPDATE_DATE = SYSDATE,
794 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
795 where rowid = l_rowIDTAB(tabIndex);
796 else --donot touch the WHO columns. This is default behavior
797 forall tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST save exceptions
798 update WF_USER_ROLE_ASSIGNMENTS
799 set ROLE_START_DATE = l_StartSrcTAB(tabIndex),
800 ROLE_END_DATE = l_EndSrcTAB(tabIndex),
801 USER_START_DATE = l_StartSrcTAB(tabIndex),
802 USER_END_DATE = l_EndSrcTAB(tabIndex),
803 START_DATE = l_StartSrcTAB(tabIndex),
804 END_DATE = l_EndSrcTAB(tabIndex),
805 EFFECTIVE_START_DATE = nvl(l_StartSrcTAB(tabIndex),
806 to_date(1,'J')),
807 EFFECTIVE_END_DATE = nvl(l_EndSrcTAB(tabIndex),
808 to_date('9999/01/01', 'YYYY/MM/DD')),
809 ASSIGNING_ROLE_START_DATE = l_StartSrcTAB(tabIndex),
810 ASSIGNING_ROLE_END_DATE = l_EndSrcTAB(tabIndex),
811 USER_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
812 ROLE_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
813 USER_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex),
814 ROLE_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex)
815 where rowid = l_rowIDTAB(tabIndex);
816 end if;
817 exception
818 when others then
819 for j in 1..sql%bulk_exceptions.count loop
820 if (sql%bulk_exceptions(j).ERROR_CODE = 1) then
821 --If update violates dup_val_on_index, we can simply delete.
822 l_eIndex := sql%bulk_exceptions(j).ERROR_INDEX;
823 delete from wf_user_role_assignments
824 where rowid = l_rowIDTAB(l_eIndex);
825 else
826 raise;
827 end if;
828 end loop;
829 end;
830
831 --Commit work to save rollback
832 commit;
833
834 -- update WF_LOCAL_USER_ROLES
835 begin
836 if (p_UpdateWho is not null and p_UpdateWho) then
837 forall tabIndex in l_rowIDSrcTAB.FIRST..l_rowIDSrcTAB.LAST save exceptions
838 update wf_local_user_roles partition (FND_USR)
839 set ROLE_START_DATE = l_StartSrcTAB(tabIndex),
840 ROLE_END_DATE = l_EndSrcTAB(tabIndex),
841 USER_START_DATE = l_StartSrcTAB(tabIndex),
842 USER_END_DATE = l_EndSrcTAB(tabIndex),
843 START_DATE = l_StartSrcTAB(tabIndex),
844 EXPIRATION_DATE = l_EndSrcTAB(tabIndex),
845 EFFECTIVE_START_DATE = nvl(l_StartSrcTAB(tabIndex),
846 to_date(1,'J')),
847 EFFECTIVE_END_DATE = nvl(l_EndSrcTAB(tabIndex),
848 to_date('9999/01/01', 'YYYY/MM/DD')),
849 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
850 LAST_UPDATE_DATE = SYSDATE,
851 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
852 where rowid = l_rowIDSrcTAB(tabIndex);
853 else --donot touch the WHO columns. This is default behavior
854 forall tabIndex in l_rowIDSrcTAB.FIRST..l_rowIDSrcTAB.LAST save exceptions
855 update wf_local_user_roles partition (FND_USR)
856 set ROLE_START_DATE = l_StartSrcTAB(tabIndex),
857 ROLE_END_DATE = l_EndSrcTAB(tabIndex),
858 USER_START_DATE = l_StartSrcTAB(tabIndex),
859 USER_END_DATE = l_EndSrcTAB(tabIndex),
860 START_DATE = l_StartSrcTAB(tabIndex),
861 EXPIRATION_DATE = l_EndSrcTAB(tabIndex),
862 EFFECTIVE_START_DATE = nvl(l_StartSrcTAB(tabIndex),
863 to_date(1,'J')),
864 EFFECTIVE_END_DATE = nvl(l_EndSrcTAB(tabIndex),
865 to_date('9999/01/01', 'YYYY/MM/DD'))
866 where rowid = l_rowIDSrcTAB(tabIndex);
867 end if;
868 end;
869
870 if (l_rowIDTAB.COUNT < l_maxRows) then --Last batch, no need to refetch
871 commit;
872 exit self_refer_loop;
873 else
874 -- reset the ROWID Table before the next set of fetch
875 l_rowIDTAB.DELETE;
876 commit;
877 end if;
878 end loop self_refer_loop;
879 exception
880 when others then
881 if (c_userSelfReference%isOpen) then
882 close c_userSelfReference;
883 end if;
884 raise;
885 end; --end of self-reference fix
886
887 commit;--commit the self reference records
888
889 -- reset the PL/SQL tables before we fetch the user-role cursor
890 l_rowIDTAB.delete;
891 l_rowIDSrcTAB.delete;
892 l_startSrcTAB.delete;
893 l_endSrcTAB.delete;
894 l_userOrigSrcTAB.delete;
895 l_userOrigIDSrcTAB.delete;
896
897 --Initialize the sumTabIndex counter.
898 sumTabIndex := 0;
899
900 --Enable parallel DML
901 execute IMMEDIATE 'alter session enable parallel dml';
902
903 --truncate the stage table now
904 WF_DDL.TruncateTable('WF_UR_VALIDATE_STG',WF_CORE.Translate('WF_SCHEMA'),
905 FALSE);
906
907 -- <bug 6823723>
908 select min(to_number(value))
909 into l_defaultParProc
910 from v$parameter
911 where name in ('parallel_max_servers','cpu_count');
912
913 if ((p_parallel_processes is NULL) or (p_parallel_processes < 1) or
914 (mod(p_parallel_processes, 1) <> 0) or (p_parallel_processes > l_defaultParProc)
915 ) then
916 l_parallelProc := to_char(l_defaultParProc) ;
917 else
918 l_parallelProc := to_char(p_parallel_processes);
919 end if;
920 -- </bug 6823723>
921
922 -- populate the stage table
923 -- bug 6823723. Now inserting as a dynamic DML to include the number of parallel processes
924 l_sql :=
925 'INSERT /*+ append parallel(WF_UR_VALIDATE_STG,'|| l_parallelProc ||') */
926 INTO WF_UR_VALIDATE_STG (WURA_ID, WUR_ID , ROLE_NAME , USER_NAME ,
927 ASSIGNING_ROLE , START_DATE , END_DATE , ROLE_START_DATE, ROLE_END_DATE
928 , USER_START_DATE , USER_END_DATE , ROLE_ORIG_SYSTEM ,
929 ROLE_ORIG_SYSTEM_ID , USER_ORIG_SYSTEM , USER_ORIG_SYSTEM_ID ,
930 ASSIGNING_ROLE_START_DATE , ASSIGNING_ROLE_END_DATE ,
931 EFFECTIVE_START_DATE , EFFECTIVE_END_DATE , RELATIONSHIP_ID )
932 SELECT /*+ ordered parallel(WURA,'|| l_parallelProc ||') parallel(WR,'|| l_parallelProc ||
933 ') parallel (wu,'|| l_parallelProc ||')
934 parallel (WAR,'|| l_parallelProc ||') parallel(WUR,'|| l_parallelProc ||') */
935 WURA.ROWID, WUR.ROWID, WURA.ROLE_NAME, WURA.USER_NAME,
936 WURA.ASSIGNING_ROLE,
937 DECODE(WURA.USER_NAME, WURA.ROLE_NAME, WU.START_DATE,
938 WURA.START_DATE) START_DATE,
939 DECODE(WURA.USER_NAME, WURA.ROLE_NAME, WU.EXPIRATION_DATE,
940 WURA.END_DATE) END_DATE,
941 WR.START_DATE, WR.EXPIRATION_DATE, WU.START_DATE,
942 WU.EXPIRATION_DATE, WR.ORIG_SYSTEM, WR.ORIG_SYSTEM_ID,
943 WU.ORIG_SYSTEM, WU.ORIG_SYSTEM_ID, WAR.START_DATE,
944 WAR.EXPIRATION_DATE,
945 GREATEST(NVL(WURA.START_DATE, TO_DATE(1,''J'')),
946 NVL(WURA.USER_START_DATE, TO_DATE(1,''J'')),
947 NVL(WURA.ROLE_START_DATE, TO_DATE(1,''J'')),
948 NVL(WURA.ASSIGNING_ROLE_START_DATE,
949 TO_DATE(1,''J''))) EFFECTIVE_START_DATE,
950 LEAST(NVL(WURA.END_DATE, TO_DATE(''9999/01/01'', ''YYYY/MM/DD'')),
951 NVL(WURA.USER_END_DATE, TO_DATE(''9999/01/01'', ''YYYY/MM/DD'')),
952 NVL(WURA.ROLE_END_DATE, TO_DATE(''9999/01/01'', ''YYYY/MM/DD'')),
953 NVL(WURA.ASSIGNING_ROLE_END_DATE,
954 TO_DATE(''9999/01/01'', ''YYYY/MM/DD''))) EFFECTIVE_END_DATE,
955 WURA.RELATIONSHIP_ID
956 FROM
957 WF_USER_ROLE_ASSIGNMENTS WURA,
958 WF_LOCAL_USER_ROLES WUR ,
959 WF_LOCAL_ROLES WAR,
960 WF_LOCAL_ROLES WU,
961 WF_LOCAL_ROLES WR
962 WHERE WURA.PARTITION_ID = WAR.PARTITION_ID
963 AND WURA.ASSIGNING_ROLE=WAR.NAME
964 AND WURA.USER_NAME= WUR.USER_NAME
965 AND WURA.ROLE_NAME=WUR.ROLE_NAME
966 AND WUR.USER_NAME = WU.NAME
967 AND WUR.USER_ORIG_SYSTEM=WU.ORIG_SYSTEM
968 AND WUR.USER_ORIG_SYSTEM_ID= WU.ORIG_SYSTEM_ID
969 AND WUR.ROLE_NAME = WR.NAME
970 AND WUR.ROLE_ORIG_SYSTEM= WR.ORIG_SYSTEM
971 AND WUR.ROLE_ORIG_SYSTEM_ID= WR.ORIG_SYSTEM_ID
972 AND WUR.PARTITION_ID = WR.PARTITION_ID
973 AND WUR.PARTITION_ID <> 1
974 AND WAR.PARTITION_ID <> 1
975 AND ( ( WUR.EFFECTIVE_START_DATE IS NULL or
976 WUR.EFFECTIVE_END_DATE IS NULL or
977 WURA.EFFECTIVE_START_DATE IS NULL or
978 WURA.EFFECTIVE_END_DATE IS NULL )
979 OR ( WURA.EFFECTIVE_START_DATE <> GREATEST(NVL(WURA.START_DATE,
980 TO_DATE(1,''J'')), NVL(WURA.USER_START_DATE, TO_DATE(1,''J'')), NVL(
981 WURA.ROLE_START_DATE, TO_DATE(1,''J'')), NVL(
982 WURA.ASSIGNING_ROLE_START_DATE, TO_DATE(1,''J''))) )
983 OR ( WURA.EFFECTIVE_END_DATE <> LEAST(NVL(WURA.END_DATE, TO_DATE(
984 ''9999/01/01'', ''YYYY/MM/DD'')), NVL(WURA.USER_END_DATE, TO_DATE(
985 ''9999/01/01'', ''YYYY/MM/DD'')) , NVL(WURA.ROLE_END_DATE, TO_DATE(
986 ''9999/01/01'', ''YYYY/MM/DD'')), NVL(WURA.ASSIGNING_ROLE_END_DATE,
987 TO_DATE(''9999/01/01'', ''YYYY/MM/DD''))))
988 OR (WURA.USER_NAME = WURA.ROLE_NAME and
989 (nvl(wura.start_date, to_date(1,''J'')) <>
990 nvl(wu.start_date, to_date(1,''J'')) or
991 nvl(wura.end_date, to_date(''9999/01/01'', ''YYYY/MM/DD'')) <>
992 nvl(wu.expiration_date, to_date(''9999/01/01'', ''YYYY/MM/DD''))))
993 OR ( ( WUR.ASSIGNMENT_TYPE IS NULL )
994 OR WUR.ASSIGNMENT_TYPE NOT IN (''D'', ''I'', ''B'') )
995 OR ( WURA.USER_ORIG_SYSTEM IS NULL
996 OR WURA.ROLE_ORIG_SYSTEM IS NULL
997 OR WURA.USER_ORIG_SYSTEM_ID IS NULL
998 OR WURA.ROLE_ORIG_SYSTEM_ID IS NULL )
999 OR ( WURA.USER_ORIG_SYSTEM <> WU.ORIG_SYSTEM
1000 OR WURA.USER_ORIG_SYSTEM_ID <> WU.ORIG_SYSTEM_ID
1001 OR WURA.ROLE_ORIG_SYSTEM <> WR.ORIG_SYSTEM
1002 OR WURA.ROLE_ORIG_SYSTEM_ID <> WR.ORIG_SYSTEM_ID )
1003 OR ( ( WU.START_DATE IS NULL
1004 AND ( WUR.USER_START_DATE IS NOT NULL
1005 OR WURA.USER_START_DATE IS NOT NULL ) )
1006 OR ( WU.START_DATE IS NOT NULL
1007 AND ( WUR.USER_START_DATE IS NULL
1008 OR WUR.USER_START_DATE <> WU.START_DATE
1009 OR WURA.USER_START_DATE IS NULL
1010 OR WURA.USER_START_DATE <> WU.START_DATE ) )
1011 OR ( WU.EXPIRATION_DATE IS NULL
1012 AND ( WUR.USER_END_DATE IS NOT NULL
1013 OR WURA.USER_END_DATE IS NOT NULL ) )
1014 OR ( WU.EXPIRATION_DATE IS NOT NULL
1015 AND ( WUR.USER_END_DATE IS NULL
1016 OR WUR.USER_END_DATE <> WU.EXPIRATION_DATE
1017 OR WURA.USER_END_DATE IS NULL
1018 OR WURA.USER_END_DATE <> WU.EXPIRATION_DATE ) ) )
1019 OR ( ( WR.START_DATE IS NULL
1020 AND ( WUR.ROLE_START_DATE IS NOT NULL
1021 OR WURA.ROLE_START_DATE IS NOT NULL ) )
1022 OR ( WR.START_DATE IS NOT NULL
1023 AND ( WUR.ROLE_START_DATE IS NULL
1024 OR WUR.ROLE_START_DATE <> WR.START_DATE
1025 OR WURA.ROLE_START_DATE IS NULL
1026 OR WURA.ROLE_START_DATE <> WR.START_DATE ) )
1027 OR ( WR.EXPIRATION_DATE IS NULL
1028 AND ( WUR.ROLE_END_DATE IS NOT NULL
1029 OR WURA.ROLE_END_DATE IS NOT NULL ) )
1030 OR ( WR.EXPIRATION_DATE IS NOT NULL
1031 AND ( WUR.ROLE_END_DATE IS NULL
1032 OR WUR.ROLE_END_DATE <> WR.EXPIRATION_DATE
1033 OR WURA.ROLE_END_DATE IS NULL
1034 OR WURA.ROLE_END_DATE <> WR.EXPIRATION_DATE ) ) )
1035 OR ( ( WAR.START_DATE IS NULL
1036 AND WURA.ASSIGNING_ROLE_START_DATE IS NOT NULL )
1037 OR ( WAR.START_DATE IS NOT NULL
1038 AND ( WURA.ASSIGNING_ROLE_START_DATE IS NULL
1039 OR WURA.ASSIGNING_ROLE_START_DATE <> WAR.START_DATE ) )
1040 OR ( WAR.EXPIRATION_DATE IS NULL
1041 AND WURA.ASSIGNING_ROLE_END_DATE IS NOT NULL )
1042 OR ( WAR.EXPIRATION_DATE IS NOT NULL
1043 AND ( WURA.ASSIGNING_ROLE_END_DATE IS NULL
1044 OR WURA.ASSIGNING_ROLE_END_DATE <> WAR.EXPIRATION_DATE ) ) ) )' ;
1045
1046 execute IMMEDIATE l_sql;
1047 commit;
1048 execute IMMEDIATE 'alter session disable parallel dml';
1049
1050 open c_UserRoleAssignments;
1051 <<outer_loop>>
1052 loop
1053 fetch c_UserRoleAssignments
1054 bulk collect into l_stgIDTAB, l_rowIDTAB, l_rowIDSrcTAB, l_roleSrcTAB,
1055 l_userSrcTAB, l_assigningRoleSrcTAB, l_startSrcTAB,
1056 l_endSrcTAB, l_roleStartSrcTAB, l_roleEndSrcTAB,
1057 l_userStartSrcTAB, l_userEndSrcTAB, l_roleOrigSrcTAB,
1058 l_roleOrigIDSrcTAB,l_userOrigSrcTAB,
1059 l_userOrigIDSrcTAB,l_asgStartSrcTAB,
1060 l_asgEndSrcTAB, l_effStartSrcTAB,l_effEndSrcTAB,
1061 l_relIDTAB
1062 limit l_maxRows;
1063
1064 --We now have pl/sql tables in memory that we can update with the new
1065 --values. So we loop through them and begin the processing.
1066 if (l_rowIDTAB.COUNT < 1) then
1067 exit outer_loop;
1068 end if;
1069
1070 --We now have a complete series of pl/sql tables with
1071 --all of the start/end dates and calculated effective start/end dates
1072 --We can then issue the bulk update..
1073 if (p_UpdateWho is not null and p_UpdateWho) then
1074 forall tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST
1075 update WF_USER_ROLE_ASSIGNMENTS
1076 set ROLE_START_DATE = l_roleStartSrcTAB(tabIndex),
1077 ROLE_END_DATE = l_roleEndSrcTAB(tabIndex),
1078 USER_START_DATE = l_userStartSrcTAB(tabIndex),
1079 USER_END_DATE = l_userEndSrcTAB(tabIndex),
1080 START_DATE = l_startSrcTAB(tabIndex),
1081 END_DATE = l_endSRcTAB(tabIndex),
1082 EFFECTIVE_START_DATE = l_effStartSrcTAB(tabIndex),
1083 EFFECTIVE_END_DATE = l_effEndSrcTAB(tabIndex),
1084 ASSIGNING_ROLE_START_DATE = l_asgStartSrcTAB(tabIndex),
1085 ASSIGNING_ROLE_END_DATE = l_asgEndSrcTAB(tabIndex),
1086 USER_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
1087 ROLE_ORIG_SYSTEM=l_roleOrigSrcTAB(tabIndex),
1088 USER_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex),
1089 ROLE_ORIG_SYSTEM_ID=l_roleOrigIDSrcTAB(tabIndex),
1090 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1091 LAST_UPDATE_DATE = SYSDATE,
1092 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1093 where rowid = l_rowIDTAB(tabIndex);
1094 else --Donot touch WHO columns. This is default behavior
1095 forall tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST
1096 update WF_USER_ROLE_ASSIGNMENTS
1097 set ROLE_START_DATE = l_roleStartSrcTAB(tabIndex),
1098 ROLE_END_DATE = l_roleEndSrcTAB(tabIndex),
1099 USER_START_DATE = l_userStartSrcTAB(tabIndex),
1100 USER_END_DATE = l_userEndSrcTAB(tabIndex),
1101 START_DATE = l_startSrcTAB(tabIndex),
1102 END_DATE = l_endSRcTAB(tabIndex),
1103 EFFECTIVE_START_DATE = l_effStartSrcTAB(tabIndex),
1104 EFFECTIVE_END_DATE = l_effEndSrcTAB(tabIndex),
1105 ASSIGNING_ROLE_START_DATE = l_asgStartSrcTAB(tabIndex),
1106 ASSIGNING_ROLE_END_DATE = l_asgEndSrcTAB(tabIndex),
1107 USER_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
1108 ROLE_ORIG_SYSTEM=l_roleOrigSrcTAB(tabIndex),
1109 USER_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex),
1110 ROLE_ORIG_SYSTEM_ID=l_roleOrigIDSrcTAB(tabIndex)
1111 where rowid = l_rowIDTAB(tabIndex);
1112 end if;
1113
1114 --We will reloop through the assignment pl/sql tables and populate the
1115 --summary pl/sql tables.
1116 <<summarize_assignments>>
1117 for tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST loop
1118 --we need to insert into summary table if this is the first
1119 --record to be inserted or, we have a new user/role combination
1120 --in the assignment table, which hasnt yet been inserted into the
1121 --summary table
1122 if ((l_roleDestTab.COUNT < 1) or
1123 (l_rowIDSrcTAB(tabIndex) <> l_rowIDDestTAB(sumTabIndex))) then
1124 -- before inserting, check whether the summarytable has
1125 -- grown too large
1126 if sumTabIndex >= l_maxRows then
1127 --limit reached for summary table, so perform
1128 --the bulk update and clear off the table.
1129 --We need to perform the bulk update here in addition to
1130 --bulk update after exit from the loop, so that clearing
1131 --the summary table will not lose user/role effective date
1132 --information when duplicate user/role
1133 --combinations are spread across multiple groups
1134 if (p_UpdateWho is not null and p_UpdateWho) then
1135 forall destTabIndex in l_roleDestTab.FIRST..l_roleDestTab.LAST
1136 UPDATE WF_LOCAL_USER_ROLES wur
1137 SET ROLE_START_DATE = l_roleStartDestTAB(destTabIndex),
1138 ROLE_END_DATE = l_roleEndDestTAB(destTabIndex),
1139 USER_START_DATE = l_userStartDestTAB(destTabIndex),
1140 USER_END_DATE = l_userEndDestTAB(destTabIndex),
1141 START_DATE = l_startDestTAB(destTabIndex),
1142 EXPIRATION_DATE = l_endDestTAB(destTabIndex),
1143 EFFECTIVE_START_DATE = l_effStartDestTAB(destTabIndex),
1144 EFFECTIVE_END_DATE = l_effEndDestTAB(destTabIndex),
1145 ASSIGNMENT_TYPE = l_assignTAB(destTabIndex),
1146 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1147 LAST_UPDATE_LOGIN = FND_GLOBAL.Login_Id,
1148 LAST_UPDATE_DATE = SYSDATE
1149 WHERE rowid = l_rowIDDestTAB(destTabIndex);
1150 else --Do not touch WHO columns. This is default behavior
1151 forall destTabIndex in l_roleDestTab.FIRST..l_roleDestTab.LAST
1152 UPDATE WF_LOCAL_USER_ROLES wur
1153 SET ROLE_START_DATE = l_roleStartDestTAB(destTabIndex),
1154 ROLE_END_DATE = l_roleEndDestTAB(destTabIndex),
1155 USER_START_DATE = l_userStartDestTAB(destTabIndex),
1156 USER_END_DATE = l_userEndDestTAB(destTabIndex),
1157 START_DATE = l_startDestTAB(destTabIndex),
1158 EXPIRATION_DATE = l_endDestTAB(destTabIndex),
1159 EFFECTIVE_START_DATE = l_effStartDestTAB(destTabIndex),
1160 EFFECTIVE_END_DATE = l_effEndDestTAB(destTabIndex),
1161 ASSIGNMENT_TYPE = l_assignTAB(destTabIndex)
1162 WHERE rowid = l_rowIDDestTAB(destTabIndex);
1163 end if;
1164 l_roleStartDestTAB.DELETE;
1165 l_roleEndDestTAB.DELETE;
1166 l_userStartDestTAB.DELETE;
1167 l_userEndDestTAB.DELETE;
1168 l_effStartDestTAB.DELETE;
1169 l_effEndDestTAB.DELETE;
1170 l_assignTAB.DELETE;
1171 l_startDestTAB.DELETE;
1172 l_endDestTAB.DELETE;
1173 l_roleDestTAB.DELETE;
1174 l_userDestTAB.DELETE;
1175 l_userOrigDestTAB.DELETE;
1176 l_userOrigIDDestTAB.DELETE;
1177 l_roleOrigDestTAB.DELETE;
1178 l_roleOrigIDDestTAB.DELETE;
1179 l_rowIDDestTAB.DELETE;
1180
1181 sumTabIndex := 0;
1182 end if; --sumTabIndex >= l_maxRows
1183
1184 --now perform the insert
1185 sumTabIndex := sumTabIndex + 1;
1186 l_RoleDestTAB(sumTabIndex) := l_roleSrcTAB(tabIndex);
1187 l_UserDestTAB(sumTabIndex) := l_userSRcTAB(tabIndex);
1188 l_userOrigDestTAB(sumTabIndex) := l_userOrigSrcTAB(tabIndex);
1189 l_userOrigIDDestTAB(sumTabIndex) := l_userOrigIDSrcTAB(tabIndex);
1190 l_roleOrigDestTAB(sumTabIndex) := l_roleOrigSrcTAB(tabIndex);
1191 l_roleOrigIDDestTAB(sumTabIndex) := l_roleOrigIDSrcTAB(tabIndex);
1192 l_roleStartDestTAB(sumTabIndex) := l_roleStartSrcTAB(tabIndex);
1193 l_roleEndDestTAB(sumTabIndex) := l_roleEndSrcTAB(tabIndex);
1194 l_userStartDestTAB(sumTabIndex) := l_userStartSrcTAB(tabIndex);
1195 l_userEndDestTAB(sumTabIndex) := l_userEndSrcTAB(tabIndex);
1196 l_effStartDestTAB(sumTabIndex) := l_effStartSrcTAB(tabIndex);
1197 l_effEndDestTAB(sumTabIndex) := l_effEndSrcTAB(tabIndex);
1198 l_rowIDDestTAB(sumTabIndex) := l_rowIDSrcTAB(TabIndex);
1199
1200 --Check to see if the assignment is active.
1201 if (l_effEndSrcTAB(tabIndex) > trunc(SYSDATE) and
1202 l_effStartSrcTab(tabIndex) <= trunc(SYSDATE)) then
1203 l_activeAssigned := TRUE;
1204 else
1205 l_activeAssigned := FALSE;
1206 end if;
1207
1208 --Determine the initial assignment_type.
1209 if l_relIDTAB(tabIndex) = -1 then
1210 l_AssignTAB(sumTabIndex):='D';
1211 l_startDestTAB(sumTabIndex) :=l_startSrcTAB(tabIndex);
1212 l_endDestTAB(sumTabIndex) :=l_endSrcTAB(tabIndex);
1213 else
1214 l_AssignTAB(sumTabIndex):='I';
1215 l_startDestTAB(sumTabIndex) :=null;
1216 l_endDestTAB(sumTabIndex) :=null;
1217 end if;
1218 else --Record is already in the summary table so update effective dates
1219 if l_effStartSrcTAB(tabIndex) < l_effStartDestTAB(sumTabIndex) then
1220 l_effStartDestTAB(sumTabIndex) := l_effStartSrcTAB(tabIndex);
1221 end if;
1222
1223 if l_effEndSrcTAB(tabIndex) > l_effEndDestTAB(sumTabIndex) then
1224 l_effEndDestTAB(sumTabIndex) := l_effEndSrcTAB(tabIndex);
1225 end if;
1226
1227 -- if this is a direct assignment then we need to set the start
1228 -- and end dates
1229 if l_relIDTAB(tabIndex) = -1 then
1230 l_startDestTAB(sumTabIndex) :=l_startSrcTAB(tabIndex);
1231 l_endDestTAB(sumTabIndex) :=l_endSrcTAB(tabIndex);
1232 end if;
1233
1234 --if the assignment type in summary table is Direct and
1235 --we encountered an inherited assignment in the Assignment table
1236 --or if the assignment type in summary table is inherited and we
1237 --encountered a direct assignment in the Assignment table
1238 --update the assignment_Type to Both
1239
1240 if (l_effEndSrcTAB(tabIndex) > trunc(SYSDATE) and
1241 l_effStartSrcTAB(tabIndex) <= trunc(SYSDATE)) then
1242 --This is an active assignment so we need to determine if an
1243 --active assignment was already used in calculating assignment_type
1244 if (l_activeAssigned) then
1245 --An active assignment was already used in the calculation so this
1246 --assignment will be used to determine if and existing 'D' or 'I'
1247 --should be changed into a 'B'
1248 if (((l_AssignTAB(sumTabIndex) = 'D') and
1249 (l_relIDTAB(tabIndex) <> -1)) or
1250 ((l_AssignTAB(sumTabIndex) = 'I') and
1251 (l_relIDTAB(tabIndex) = -1))) then
1252
1253 l_AssignTAB(sumTabIndex) := 'B';
1254 end if;
1255 else
1256 --This is the first active assignment, so set the initial value
1257 --Determine the initial assignment_type.
1258 if l_relIDTAB(tabIndex) = -1 then
1259 l_AssignTAB(sumTabIndex):='D';
1260 l_startDestTAB(sumTabIndex) :=l_startSrcTAB(tabIndex);
1261 l_endDestTAB(sumTabIndex) :=l_endSrcTAB(tabIndex);
1262 else
1263 l_AssignTAB(sumTabIndex):='I';
1264 l_startDestTAB(sumTabIndex) :=null;
1265 l_endDestTAB(sumTabIndex) :=null;
1266 end if;
1267
1268 --Now set l_activeAssigned to TRUE.
1269 l_activeAssigned := TRUE;
1270 end if;
1271 else
1272 --This is an expired assignment, so we will set the assignment_type
1273 --only if we have not already initialized/modified with an
1274 --active assignment.
1275 if NOT (l_activeAssigned) then
1276 if l_relIDTAB(tabIndex) = -1 then
1277 l_AssignTAB(sumTabIndex):='D';
1278 l_startDestTAB(sumTabIndex) :=l_startSrcTAB(tabIndex);
1279 l_endDestTAB(sumTabIndex) :=l_endSrcTAB(tabIndex);
1280 else
1281 l_AssignTAB(sumTabIndex):='I';
1282 l_startDestTAB(sumTabIndex) :=null;
1283 l_endDestTAB(sumTabIndex) :=null;
1284 end if;
1285 end if;
1286 end if;
1287 end if;
1288 end loop summarize_assignments;
1289
1290 --Check to see if we have the last batch and do not need to re-fetch
1291 if (l_rowIDTAB.COUNT < l_maxRows) then
1292 commit;
1293 exit outer_loop;
1294 else
1295 -- reset the ROWID Table before the next set of fetch
1296 l_rowIDTAB.DELETE;
1297 commit;
1298 end if;
1299 end loop outer_loop;
1300
1301 --when we reach here, we need to bulk update the leftover records,
1302 --if any, in the summary table.
1303
1304 if (l_roleDestTAB.COUNT) > 0 then
1305 if (p_UpdateWho is not null and p_UpdateWho) then
1306 forall destTabIndex in l_roleDestTab.FIRST..l_roleDestTab.LAST
1307 UPDATE WF_LOCAL_USER_ROLES wur
1308 SET ROLE_START_DATE = l_roleStartDestTAB(destTabIndex),
1309 ROLE_END_DATE = l_roleEndDestTAB(destTabIndex),
1310 USER_START_DATE = l_userStartDestTAB(destTabIndex),
1311 USER_END_DATE = l_userEndDestTAB(destTabIndex),
1312 EFFECTIVE_START_DATE = l_effStartDestTAB(destTabIndex),
1313 EFFECTIVE_END_DATE = l_effEndDestTAB(destTabIndex),
1314 START_DATE = l_startDestTAB(destTabIndex),
1315 EXPIRATION_DATE = l_endDestTAB(destTabIndex),
1316 ASSIGNMENT_TYPE = l_assignTAB(destTabIndex),
1317 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1318 LAST_UPDATE_LOGIN = FND_GLOBAL.Login_Id,
1319 LAST_UPDATE_DATE = SYSDATE
1320 WHERE rowid = l_rowIDDestTAB(destTabIndex);
1321 else --Do not touch WHO columns. This is default behavior.
1322 forall destTabIndex in l_roleDestTab.FIRST..l_roleDestTab.LAST
1323 UPDATE WF_LOCAL_USER_ROLES wur
1324 SET ROLE_START_DATE = l_roleStartDestTAB(destTabIndex),
1325 ROLE_END_DATE = l_roleEndDestTAB(destTabIndex),
1326 USER_START_DATE = l_userStartDestTAB(destTabIndex),
1327 USER_END_DATE = l_userEndDestTAB(destTabIndex),
1328 EFFECTIVE_START_DATE = l_effStartDestTAB(destTabIndex),
1329 EFFECTIVE_END_DATE = l_effEndDestTAB(destTabIndex),
1330 START_DATE = l_startDestTAB(destTabIndex),
1331 EXPIRATION_DATE = l_endDestTAB(destTabIndex),
1332 ASSIGNMENT_TYPE = l_assignTAB(destTabIndex)
1333 WHERE rowid = l_rowIDDestTAB(destTabIndex);
1334 end if;
1335 end if;
1336 commit; --Commit final work.
1337 --close the cursor now
1338 if (c_userRoleAssignments%ISOPEN) then
1339 close c_userRoleAssignments;
1340 end if;
1341 --release lock
1342 if (dbms_lock.release(l_lockhandle) <> 0) then
1343 wf_core.raise('WF_LOCK_FAIL');
1344 end if;
1345
1346 exception
1347 when others then
1348 result := dbms_lock.release(l_lockhandle);
1349 if (c_userRoleAssignments%ISOPEN) then
1350 close c_userRoleAssignments;
1351 end if;
1352 raise;
1353 end;
1354
1355 end WF_MAINTENANCE;
[Home] [Help]