1 package body WF_MAINTENANCE as
2 /* $Header: wfmtn9b.pls 120.20.12020000.2 2012/07/13 20:42:04 alsosa ship $ */
3
4 g_CommitCounter NUMBER := 0;
5 g_docommit BOOLEAN := FALSE;
6
7 -- Types for bulk operations
8 type itemRec is record
9 ( item_type varchar2(8),
10 item_key varchar2(240)
11 );
12
13 type itemActRec is record
14 ( item_type varchar2(8),
15 item_key varchar2(240),
16 process_activity number
17 );
18
19 type procActRec is record
20 ( process_item_type varchar2(8),
21 process_name varchar2(30),
22 process_version number,
23 instance_label varchar2(30),
24 instance_id number
25 );
26
27 type itemTblType is table of itemRec index by binary_integer;
28 type itemActTblType is table of itemActRec index by binary_integer;
29 type procActTblType is table of procActRec index by binary_integer;
30 type numTblType is table of number index by binary_integer;
31 type rowIdTblType is table of rowid index by binary_integer;
32
33 itemTbl itemTblType;
34 itemActTbl itemActTblType;
35 procActTbl procActTblType;
36 numTbl numTblType;
37 rowIdTbl rowIdTblType;
38
39 procedure PerformCommit;
40
41 -- procedure PropagateChangedName
42 -- Locates all occurrences of an old username and changes to
43 -- the new username.
44 --
45 -- IN:
46 -- OldName - Old Username we are changing from.
47 -- NewName - New Username we are changing to.
48 -- CommitFrequency - Number of updates we perform before commit.
49 --
50 procedure PropagateChangedName(
51 OldName in varchar2,
52 NewName in varchar2,
53 docommit in BOOLEAN )
54
55 is
56
57 l_oldname VARCHAR2(320); -- Local Variable of OldName
58 l_newname VARCHAR2(320); -- Local Variable of NewName
59 l_pname VARCHAR2(50) := 'WF_MAINT_COMPLETED_ITEMS';
60 l_pvalue varchar2(10);
61 l_size number := 5000;
62
63 l_items number;
64 l_ias number;
65 l_iash number;
66 l_ntfs number;
67 l_pas number;
68 l_rr number;
69 l_rra number;
70 l_coms number;
71 l_ra number;
72 l_wa number; --counter of WorlistAccess changes for the user
73
74
75 -- Setting up cursors for tables that would store a role name.
76 -- Some tables have columns named 'READ_ROLE' and 'WRITE_ROLE' that
77 -- are not currently used, so they are not included.
78
79 cursor cItems (l_oldname varchar2) is
80 select ITEM_TYPE, ITEM_KEY
81 from WF_ITEMS
82 where OWNER_ROLE = l_oldname;
83
84 cursor cItems2 (l_oldname varchar2) is
85 select ITEM_TYPE, ITEM_KEY
86 from WF_ITEMS
87 where OWNER_ROLE = l_oldname
88 and END_DATE IS NULL;
89
90 cursor cItemActivityStatuses (l_oldname varchar2) is
91 select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY
92 from WF_ITEM_ACTIVITY_STATUSES
93 where ASSIGNED_USER = l_oldname;
94
95 cursor cItemActivityStatuses2 (l_oldname varchar2) is
96 select WIAS.ITEM_TYPE, WIAS.ITEM_KEY, WIAS.PROCESS_ACTIVITY
97 from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_ITEMS WI
98 where WIAS.ITEM_TYPE = wi.item_type
99 and WIAS.ITEM_KEY = wi.item_key
100 and WI.END_DATE IS NULL
101 and WIAS.ASSIGNED_USER = l_oldname;
102
103 cursor cItemActivityStatuses_H (l_oldname varchar2) is
104 select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY
105 from WF_ITEM_ACTIVITY_STATUSES_H
106 where ASSIGNED_USER = l_oldname;
107
108 cursor cItemActivityStatuses_H2 (l_oldname varchar2) is
109 select WIASH.ITEM_TYPE, WIASH.ITEM_KEY, WIASH.PROCESS_ACTIVITY
110 from WF_ITEM_ACTIVITY_STATUSES_H WIASH, WF_ITEMS WI
111 where WIASH.ITEM_TYPE = wi.item_type
112 and WIASH.ITEM_KEY = wi.item_key
113 and WI.END_DATE IS NULL
114 and WIASH.ASSIGNED_USER = l_oldname;
115
116 cursor cNotifications (l_oldname varchar2) is
117 select NOTIFICATION_ID
118 from WF_NOTIFICATIONS
119 where RECIPIENT_ROLE = l_oldname
120 or ORIGINAL_RECIPIENT = l_oldname
121 or more_info_role = l_oldname
122 or from_role = l_oldname
123 or responder = l_oldname;
124
125 -- Only notifications belonging to active items
126 cursor cNotifications2 (l_oldname varchar2) is
127 select NOTIFICATION_ID
128 from WF_NOTIFICATIONS WN
129 where (WN.RECIPIENT_ROLE = l_oldname
130 or WN.ORIGINAL_RECIPIENT = l_oldname
131 or WN.MORE_INFO_ROLE = l_oldname
132 or WN.FROM_ROLE = l_oldname
133 or WN.RESPONDER = l_oldname)
134 and (exists (select '1'
135 from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_ITEMS WI
136 where WIAS.NOTIFICATION_ID = WN.NOTIFICATION_ID
137 and WIAS.NOTIFICATION_ID is not null
138 and WIAS.ITEM_TYPE = WI.ITEM_TYPE
139 and WIAS.ITEM_KEY = WI.ITEM_KEY
140 and WI.END_DATE IS NULL)
141 or exists (select '1'
142 from WF_ITEM_ACTIVITY_STATUSES_H WIASH, WF_ITEMS WI
143 where WIASH.NOTIFICATION_ID = WN.NOTIFICATION_ID
144 and WIASH.NOTIFICATION_ID is not null
145 and WIASH.ITEM_TYPE = WI.ITEM_TYPE
146 and WIASH.ITEM_KEY = WI.ITEM_KEY
147 and WI.END_DATE IS NULL));
148
149 cursor cProcessActivities (l_oldname varchar2) is
150 select PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION,
151 INSTANCE_LABEL, INSTANCE_ID
152 from WF_PROCESS_ACTIVITIES
153 where PERFORM_ROLE = l_oldname;
154
155 cursor cRoutingRules (l_oldname varchar2) is
156 select RULE_ID
157 from WF_ROUTING_RULES
158 where ROLE = l_oldname
159 or ACTION_ARGUMENT = l_oldname;
160
161 cursor cRoutingRuleAttributes (l_oldname varchar2) is
162 select ra.ROWID
163 from WF_ROUTING_RULE_ATTRIBUTES ra
164 where ra.TEXT_VALUE = l_oldname
165 and exists
166 (select null
167 from wf_message_attributes ma
168 where ma.name=ra.name
169 and ma.type='ROLE');
170
171 cursor cWfComments (l_oldname varchar2) is
172 select rowid
173 from wf_comments
174 where from_role = l_oldname
175 or to_role = l_oldname
176 or proxy_role = l_oldname;
177
178 -- Only notifications belonging to active items
179 cursor cWfComments2 (l_oldname varchar2) is
180 select WC.ROWID
181 from WF_COMMENTS WC
182 where (WC.FROM_ROLE = l_oldname
183 or WC.TO_ROLE = l_oldname
184 or WC.PROXY_ROLE = l_oldname)
185 and (exists (select '1'
186 from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_ITEMS WI
187 where WIAS.NOTIFICATION_ID = WC.NOTIFICATION_ID
188 and WIAS.NOTIFICATION_ID is not null
189 and WIAS.ITEM_TYPE = WI.ITEM_TYPE
190 and WIAS.ITEM_KEY = WI.ITEM_KEY
191 and WI.END_DATE IS NULL)
192 or exists (select '1'
193 from WF_ITEM_ACTIVITY_STATUSES_H WIASH, WF_ITEMS WI
194 where WIASH.NOTIFICATION_ID = WC.NOTIFICATION_ID
195 and WIASH.NOTIFICATION_ID is not null
196 and WIASH.ITEM_TYPE = WI.ITEM_TYPE
197 and WIASH.ITEM_KEY = WI.ITEM_KEY
198 and WI.END_DATE IS NULL));
199
200 cursor cRoleAttributes (l_oldname varchar2) is
201 select wiav.rowid
202 from wf_item_attribute_values wiav, wf_item_attributes wia
203 where wia.type = 'ROLE'
204 and wia.item_type = wiav.item_type
205 and wia.name = wiav.name
206 and wiav.text_value = l_oldname;
207
208 cursor cRoleAttributes2 (l_oldname varchar2) is
209 select wiav.rowid
210 from wf_item_attribute_values wiav, wf_item_attributes wia
211 where wia.type = 'ROLE'
212 and wia.item_type = wiav.item_type
213 and wia.name = wiav.name
214 and wiav.text_value = l_oldname
215 and exists (select '1'
216 from WF_ITEMS WI
217 where WI.ITEM_TYPE = WIAV.ITEM_TYPE
218 and WI.ITEM_KEY = WIAV.ITEM_KEY
219 and WI.END_DATE IS NULL);
220
221 l_roleInfoTAB WF_DIRECTORY.wf_local_roles_tbl_type;
222
223 cursor cWorklistAccess is
224 select fg.rowid
225 from FND_GRANTS fg
226 where fg.GRANTEE_TYPE='USER'
227 and fg.GRANTEE_ORIG_SYSTEM in ('FND_USR', 'PER')
228 and fg.PROGRAM_NAME = 'WORKFLOW_UI'
229 and fg.PARAMETER1=PropagateChangedName.OldName;
230
231 begin
232 l_newname := upper(substrb(NewName,1,320));
233 l_oldname := upper(substrb(OldName,1,320));
234
235 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
236 wf_log_pkg.string(wf_log_pkg.level_procedure, 'wf.plsql.wf_maintenance.propagateChangedName',
237 'Changing old user '||l_oldname||' to new '||l_newname);
238 end if;
239
240 g_docommit := docommit;
241
242
243 /* We check to be sure that old name no longer exists (IE: the name was
244 changed). If it is we can go ahead and effect the change.
245
246 If the old name is still active and somewhere else in the directory services
247 we can't change it, so we have to raise the error that the name still
248 exists.
249
250 We then check to be sure the new name is active and ready to receive
251 the records from the old name.
252 */
253
254 WF_DIRECTORY.GetRoleInfo2(l_oldName, l_roleInfoTAB);
255
256 if (l_roleInfoTAB(1).display_name is not NULL) then
257 if not (WF_DIRECTORY.ChangeLocalUsername(l_oldname, l_newname, FALSE)) then
258 WF_CORE.Token('ROLE', l_oldname);
259 WF_CORE.Token('PROCEDURE', 'PropagateChangedName');
260 WF_CORE.Token('PARAMETER', 'OldName');
261 WF_CORE.Raise('WFMTN_ACTIVEROLE');
262 return;
263 end if;
264 end if;
265
266 WF_DIRECTORY.GetRoleInfo2(l_newname, l_roleInfoTAB);
267
268 if (l_roleInfoTAB(1).display_name is null) then
269 WF_CORE.Token('ROLE', l_newname);
270 WF_CORE.Raise('WFNTF_ROLE');
271 return;
272 end if;
273
274
275 /* We will now start looping through the cursors and updating OldName
276 to NewName
277 */
278 l_pvalue := FND_PROFILE.value(l_pname);
279 -- This profile is shipped only for 12.1.X and above at this point. If not
280 -- available, continue to update all records as always.
281 if (l_pvalue is null or l_pvalue = 'Y') then
282
283 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
284 wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.wf_maintenance.propagateChangedName',
285 'Updating all WF data');
286 end if;
287
288 open cItems(l_oldname);
289 loop
290 fetch cItems bulk collect into itemTbl limit l_size;
291 if (itemTbl.count = 0) then
292 exit;
293 end if;
294
295 forall i in itemTbl.first..itemTbl.last
296 update wf_items
297 set owner_role = l_newname
298 where item_type = itemTbl(i).item_type
299 and item_key = itemTbl(i).item_key;
300
301 commit;
302 itemTbl.delete;
303 end loop;
304 l_items := cItems%rowcount;
305 close cItems;
306
307 open cItemActivityStatuses(l_oldname);
308 loop
309 fetch cItemActivityStatuses bulk collect into itemActTbl limit l_size;
310 if (itemActTbl.count = 0) then
311 exit;
312 end if;
313
314 forall i in itemActTbl.first..itemActTbl.last
315 update WF_ITEM_ACTIVITY_STATUSES
316 set ASSIGNED_USER = l_newname
317 where ITEM_TYPE = itemActTbl(i).item_type
318 and ITEM_KEY = itemActTbl(i).item_key
319 and PROCESS_ACTIVITY = itemActTbl(i).process_activity;
320
321 commit;
322 itemActTbl.delete;
323 end loop;
324 l_ias := cItemActivityStatuses%rowcount;
325 close cItemActivityStatuses;
326
327 open cItemActivityStatuses_H(l_oldname);
328 loop
329 fetch cItemActivityStatuses_H bulk collect into itemActTbl limit l_size;
330 if (itemActTbl.count = 0) then
331 exit;
332 end if;
333
334 forall i in itemActTbl.first..itemActTbl.last
335 update WF_ITEM_ACTIVITY_STATUSES_H
336 set ASSIGNED_USER = l_newname
337 where ITEM_TYPE = itemActTbl(i).item_type
338 and ITEM_KEY = itemActTbl(i).item_key
339 and PROCESS_ACTIVITY = itemActTbl(i).process_activity;
340
341 commit;
342 itemActTbl.delete;
343 end loop;
344 l_iash := cItemActivityStatuses_H%rowcount;
345 close cItemActivityStatuses_H;
346
347 open cNotifications (l_oldname);
348 loop
349 fetch cNotifications bulk collect into numTbl limit l_size;
350 if (numTbl.count = 0) then
351 exit;
352 end if;
353
354 forall i in numTbl.first..numTbl.last
355 update WF_NOTIFICATIONS
356 set RECIPIENT_ROLE = decode(RECIPIENT_ROLE, l_oldname, l_newname, RECIPIENT_ROLE),
357 ORIGINAL_RECIPIENT = decode(ORIGINAL_RECIPIENT, l_oldname, l_newname, ORIGINAL_RECIPIENT),
358 FROM_ROLE = decode(FROM_ROLE, l_oldname, l_newname, FROM_ROLE),
359 RESPONDER = decode(RESPONDER, l_oldname, l_newname, RESPONDER),
360 MORE_INFO_ROLE = decode(MORE_INFO_ROLE, l_oldname, l_newname, MORE_INFO_ROLE)
361 where NOTIFICATION_ID = numTbl(i);
362 commit;
363
364 numTbl.delete;
365 end loop;
366 l_ntfs := cNotifications%rowcount;
367 close cNotifications;
368
369 open cWfComments(l_oldname);
370 loop
371 fetch cWfComments bulk collect into rowIdTbl limit l_size;
372 if (rowIdTbl.count = 0) then
373 exit;
374 end if;
375
376 forall i in rowIdTbl.first..rowIdTbl.last
377 update WF_COMMENTS
378 set FROM_ROLE = decode(FROM_ROLE, l_oldname, l_newname, FROM_ROLE),
379 FROM_USER = decode(FROM_ROLE, l_oldname, l_roleInfoTAB(1).display_name, FROM_USER),
380 TO_ROLE = decode(TO_ROLE, l_oldname, l_newname, TO_ROLE),
381 TO_USER = decode(TO_ROLE, l_oldname, l_roleInfoTAB(1).display_name, TO_USER),
382 PROXY_ROLE = decode(PROXY_ROLE, l_oldname, l_newname, PROXY_ROLE)
383 where rowid = rowIdTbl(i);
384 commit;
385
386 rowIdTbl.delete;
387 end loop;
388 l_coms := cWfComments%rowcount;
389 close cWfComments;
390
391 open cRoleAttributes(l_oldname);
392 loop
393 fetch cRoleAttributes bulk collect into rowIdTbl limit l_size;
394 if (rowIdTbl.count = 0) then
395 exit;
396 end if;
397
398 forall i in rowIdTbl.first..rowIdTbl.last
399 update WF_ITEM_ATTRIBUTE_VALUES
400 set TEXT_VALUE = l_newname
401 where rowid = rowIdTbl(i);
402 commit;
403
404 rowIdTbl.delete;
405 end loop;
406 l_ra := cRoleAttributes%rowcount;
407 close cRoleAttributes;
408
409 else -- End profile = 'Y'
410
411 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
412 wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.wf_maintenance.propagateChangedName',
413 'Updating only Active WF data');
414 end if;
415
416 open cItems2(l_oldname);
417 loop
418 fetch cItems2 bulk collect into itemTbl limit l_size;
419 if (itemTbl.count = 0) then
420 exit;
421 end if;
422
423 forall i in itemTbl.first..itemTbl.last
424 update wf_items
425 set owner_role = l_newname
426 where item_type = itemTbl(i).item_type
427 and item_key = itemTbl(i).item_key;
428 commit;
429
430 itemTbl.delete;
431 end loop;
432 l_items := cItems2%rowcount;
433 close cItems2;
434
435 open cItemActivityStatuses2(l_oldname);
436 loop
437 fetch cItemActivityStatuses2 bulk collect into itemActTbl limit l_size;
438 if (itemActTbl.count = 0) then
439 exit;
440 end if;
441
442 forall i in itemActTbl.first..itemActTbl.last
443 update WF_ITEM_ACTIVITY_STATUSES
444 set ASSIGNED_USER = l_newname
445 where ITEM_TYPE = itemActTbl(i).item_type
446 and ITEM_KEY = itemActTbl(i).item_key
447 and PROCESS_ACTIVITY = itemActTbl(i).process_activity;
448 commit;
449
450 itemActTbl.delete;
451 end loop;
452 l_ias := cItemActivityStatuses2%rowcount;
453 close cItemActivityStatuses2;
454
455 open cItemActivityStatuses_H2(l_oldname);
456 loop
457 fetch cItemActivityStatuses_H2 bulk collect into itemActTbl limit l_size;
458 if (itemActTbl.count = 0) then
459 exit;
460 end if;
461
462 forall i in itemActTbl.first..itemActTbl.last
463 update WF_ITEM_ACTIVITY_STATUSES_H
464 set ASSIGNED_USER = l_newname
465 where ITEM_TYPE = itemActTbl(i).item_type
466 and ITEM_KEY = itemActTbl(i).item_key
467 and PROCESS_ACTIVITY = itemActTbl(i).process_activity;
468 commit;
469
470 itemActTbl.delete;
471 end loop;
472 l_iash := cItemActivityStatuses_H2%rowcount;
473 close cItemActivityStatuses_H2;
474
475 open cNotifications2 (l_oldname);
476 loop
477 fetch cNotifications2 bulk collect into numTbl limit l_size;
478 if (numTbl.count = 0) then
479 exit;
480 end if;
481
482 forall i in numTbl.first..numTbl.last
483 update WF_NOTIFICATIONS
484 set RECIPIENT_ROLE = decode(RECIPIENT_ROLE, l_oldname, l_newname, RECIPIENT_ROLE),
485 ORIGINAL_RECIPIENT = decode(ORIGINAL_RECIPIENT, l_oldname, l_newname, ORIGINAL_RECIPIENT),
486 FROM_ROLE = decode(FROM_ROLE, l_oldname, l_newname, FROM_ROLE),
487 RESPONDER = decode(RESPONDER, l_oldname, l_newname, RESPONDER),
488 MORE_INFO_ROLE = decode(MORE_INFO_ROLE, l_oldname, l_newname, MORE_INFO_ROLE)
489 where NOTIFICATION_ID = numTbl(i);
490 commit;
491
492 numTbl.delete;
493 end loop;
494 l_ntfs := cNotifications2%rowcount;
495 close cNotifications2;
496
497 open cWfComments2(l_oldname);
498 loop
499 fetch cWfComments2 bulk collect into rowIdTbl limit l_size;
500 if (rowIdTbl.count = 0) then
501 exit;
502 end if;
503
504 forall i in rowIdTbl.first..rowIdTbl.last
505 update WF_COMMENTS
506 set FROM_ROLE = decode(FROM_ROLE, l_oldname, l_newname, FROM_ROLE),
507 FROM_USER = decode(FROM_ROLE, l_oldname, l_roleInfoTAB(1).display_name, FROM_USER),
508 TO_ROLE = decode(TO_ROLE, l_oldname, l_newname, TO_ROLE),
509 TO_USER = decode(TO_ROLE, l_oldname, l_roleInfoTAB(1).display_name, TO_USER),
510 PROXY_ROLE = decode(PROXY_ROLE, l_oldname, l_newname, PROXY_ROLE)
511 where rowid = rowIdTbl(i);
512 commit;
513
514 rowIdTbl.delete;
515 end loop;
516 l_coms := cWfComments2%rowcount;
517 close cWfComments2;
518
519 open cRoleAttributes2(l_oldname);
520 loop
521 fetch cRoleAttributes2 bulk collect into rowIdTbl limit l_size;
522 if (rowIdTbl.count = 0) then
523 exit;
524 end if;
525
526 forall i in rowIdTbl.first..rowIdTbl.last
527 update WF_ITEM_ATTRIBUTE_VALUES
528 set TEXT_VALUE = l_newname
529 where rowid = rowIdTbl(i);
530 commit;
531
532 rowIdTbl.delete;
533 end loop;
534 l_ra := cRoleAttributes2%rowcount;
535 close cRoleAttributes2;
536
537 end if; -- End profile = 'N'
538
539 open cProcessActivities(l_oldname);
540 loop
541 fetch cProcessActivities bulk collect into procActTbl limit l_size;
542 if (procActTbl.count = 0) then
543 exit;
544 end if;
545
546 forall i in procActTbl.first..procActTbl.last
547 update WF_PROCESS_ACTIVITIES
548 set PERFORM_ROLE = l_newname
549 where PROCESS_ITEM_TYPE = procActTbl(i).process_item_type
550 and PROCESS_NAME = procActTbl(i).process_name
551 and PROCESS_VERSION = procActTbl(i).process_version
552 and INSTANCE_LABEL = procActTbl(i).instance_label
553 and INSTANCE_ID = procActTbl(i).instance_id;
554 commit;
555
556 procActTbl.delete;
557 end loop;
558 l_pas := cProcessActivities%rowcount;
559 close cProcessActivities;
560
561 open cRoutingRules(l_oldname);
562 loop
563 fetch cRoutingRules bulk collect into numTbl limit l_size;
564 if (numTbl.count = 0) then
565 exit;
566 end if;
567
568 forall i in numTbl.first..numTbl.last
569 update WF_ROUTING_RULES
570 set ROLE = l_newname
571 where RULE_ID = numTbl(i)
572 and ROLE = l_oldname;
573 commit;
574
575 forall i in numTbl.first..numTbl.last
576 update WF_ROUTING_RULES
577 set ACTION_ARGUMENT = l_newname
578 where RULE_ID = numTbl(i)
579 and ACTION_ARGUMENT = l_oldname;
580 commit;
581
582 numTbl.delete;
583 end loop;
584 l_rr := cRoutingRules%rowcount;
585 close cRoutingRules;
586
587 open cRoutingRuleAttributes(l_oldname);
588 loop
589 fetch cRoutingRuleAttributes bulk collect into rowIdTbl limit l_size;
590 if (rowIdTbl.count = 0) then
591 exit;
592 end if;
593
594 forall i in rowIdTbl.first..rowIdTbl.last
595 update wf_routing_rule_attributes
596 set text_value = l_newname
597 where rowid = rowIdTbl(i);
598 commit;
599
600 rowIdTbl.delete;
601 end loop;
602 l_rra := cRoutingRuleAttributes%rowcount;
603 close cRoutingRuleAttributes;
604
605 commit;
606
607 open cWorklistAccess;
608 LOOP
609 FETCH cWorklistAccess BULK COLLECT INTO rowIdTbl LIMIT l_size;
610 if (rowIdTbl.count = 0) then
611 exit;
612 end if;
613 FORALL i IN rowIdTbl.FIRST..rowIdTbl.LAST
614 UPDATE fnd_grants fg
615 SET fg.parameter1=PropagateChangedName.NewName
616 WHERE fg.ROWID=rowIdTbl(i);
617 COMMIT;
618 rowIdTbl.DELETE;
619 end LOOP;
620 l_wa := cWorklistAccess%ROWCOUNT;
621 CLOSE cWorklistAccess;
622
623 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
624 wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.wf_maintenance.propagateChangedName',
625 'Updated Records: WF_ITEMS:'||l_items||', WF_IAS:'||l_ias||', WF_IASH:'
626 ||l_iash||', WF_NTFS:'||l_ntfs||', WF_COMMENTS:'||l_coms);
627 wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.wf_maintenance.propagateChangedName',
628 'Updated Records: WF_PROC_ACTS:'||l_pas||', WF_ROUTING_RULES:'||l_rr
629 ||', WF_ROUTING_RULE_ATTRS:' ||l_rra||', WF_ITEM_ATTR_VALUES:'||l_ra
630 ||', WORKLIST_ACCESS:'||l_wa);
631 end if;
632
633 exception
634 when others then
635 WF_CORE.Context('WF_MAINTENANCE', 'PropagateChangedName', OldName, NewName);
636 raise;
637 end PropagateChangedName;
638
639 -- procedure PerformCommit (private)
640 -- Decides if commit should occur and commits.
641 --
642 -- IN:
643 -- No Parameters.
644 --
645 procedure PerformCommit
646
647 IS
648 BEGIN
649 if (g_docommit) then
650 g_commitCounter := g_commitCounter +1;
651 if (g_commitCounter >= WF_MAINTENANCE.g_CommitFrequency) then
652 commit;
653 g_commitCounter := 0;
654 end if;
655 end if;
656
657 END PerformCommit;
658
659 -- Procedure FixWURAEffectiveDates (private)
660 -- As part of fix 9184359. Scan table WF_USER_ROLE_ASSIGNMENTS for rows with
661 -- null effective start/end dates and will set them according the the
662 -- values of the other date columns
663 -- IN: No p_maxRows. This parameter comes from main procedure ValidateUserRoles
664 --
665 procedure FixWURAEffectiveDates (p_maxRows IN NUMBER, p_username IN VARCHAR2,
666 p_rolename IN VARCHAR2)
667 IS
668 -- TYPE WURARecord is RECORD (l_rowid ROWID,
669 -- start_date date, end_date date, u_start_date date, u_end_date date,
670 -- r_start_date date, r_end_date date, ar_start_date date, ar_end_date date,
671 -- e_start_date date, e_end_date date);
672 -- TYPE WURATab is table of WURARecord index by BINARY_INTEGER;
673 TYPE dateTab is table of date index by binary_integer;
674 l_start_dateTab dateTab;
675 l_end_dateTab dateTab;
676 l_u_start_dateTab dateTab;
677 l_u_end_dateTab dateTab;
678 l_r_start_dateTab dateTab;
679 l_r_end_dateTab dateTab;
680 l_ar_start_dateTab dateTab;
681 l_ar_end_dateTab dateTab;
682 l_e_start_dateTab dateTab;
683 l_e_end_dateTab dateTab;
684 TYPE rowidTab is table of rowid index by binary_integer;
685 l_rowidTab rowidTab;
686 cursor c_nullEffectiveDates is
687 select ROWID, start_date, end_date, user_start_date, user_end_date,
688 role_start_date, role_end_date,
689 assigning_role_start_date, assigning_role_end_date, null, null
690 from WF_USER_ROLE_ASSIGNMENTS WURA
691 where (WURA.USER_NAME = p_username or p_username is null)
692 and (WURA.ROLE_NAME = p_rolename or p_rolename is null)
693 and (WURA.EFFECTIVE_START_DATE is null
694 or WURA.EFFECTIVE_END_DATE is null);
695 BEGIN
696 open c_nullEffectiveDates;
697 <<NullEffectiveDates>>
698 loop
699 fetch c_nullEffectiveDates
700 bulk collect
701 into l_rowidTab, l_start_dateTab, l_end_dateTab, l_u_start_dateTab,
702 l_u_end_dateTab, l_r_start_dateTab, l_r_end_dateTab, l_ar_start_dateTab,
703 l_ar_end_dateTab, l_e_start_dateTab, l_e_end_dateTab
704 limit p_maxRows;
705 if l_rowidTab.COUNT > 0 then
706 for i in l_rowidTab.FIRST..l_rowidTab.LAST loop
707 WF_ROLE_HIERARCHY.Calculate_Effective_Dates(l_start_dateTab(i),
708 l_end_dateTab(i),
709 l_u_start_dateTab(i),
710 l_u_end_dateTab(i),
711 l_r_start_dateTab(i),
712 l_r_end_dateTab(i),
713 l_ar_start_dateTab(i),
714 l_ar_end_dateTab(i),
715 l_e_start_dateTab(i),
716 l_e_end_dateTab(i));
717 end loop;
718 begin
719 forall j in l_rowidTab.FIRST.. l_rowidTab.LAST
720 update WF_USER_ROLE_ASSIGNMENTS WURA
721 set WURA.EFFECTIVE_START_DATE=l_e_start_dateTab(j),
722 WURA.EFFECTIVE_END_DATE =l_e_end_dateTab(j)
723 where WURA.ROWID=l_rowidTab(j);
724 exception
725 when others then
726 if c_nullEffectiveDates%ISOPEN then
727 close c_nullEffectiveDates;
728 end if;
729 raise;
730 end;
731 end if;
732 if l_rowidTab.COUNT < p_maxRows then
733 exit NullEffectiveDates;
734 end if;
735 end loop NullEffectiveDates;
736 close c_nullEffectiveDates;
737 exception
738 when others then
739 if c_nullEffectiveDates%ISOPEN then
740 close c_nullEffectiveDates;
741 end if;
742 raise;
743 END FixWURAEffectiveDates;
744
745 -- procedure FixLUREffectiveDates (private)
746 -- Fix incorrect Effective_End_Date in WF_LOCAL_USER_ROLES as pert of bug 8423138
747 --
748 -- IN:
749 -- No p_maxRows. This parameter comes from main procedure ValidateUserRoles
750 --
751 procedure FixLUREffectiveDates(p_maxRows IN NUMBER, p_username IN VARCHAR2,
752 p_rolename IN VARCHAR2)
753 is
754 TYPE idTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
755 TYPE dateTab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
756
757 type nameTab is table of varchar2(100) index by binary_integer;
758
759 l_LURTab idTab;
760 l_URAEffectiveStartDate dateTab;
761 l_URAEffectiveEndDate dateTab;
762
763 cursor c_invalidEffectiveDates is
764 select lur.rowid,
765 ura.effective_start_date,ura.effective_end_date
766 from wf_local_user_roles lur,
767 (select user_name, role_name, min(effective_start_date) effective_start_date,
768 max (effective_end_date) effective_end_date
769 from wf_user_role_assignments group by user_name, role_name) ura
770 where ura.user_name = lur.user_name
771 and ura.role_name = lur.role_name
772 and (URA.USER_NAME=p_username or p_username is null)
773 and (URA.ROLE_NAME=p_rolename or p_rolename is null)
774 and (ura.effective_start_date <> lur.effective_start_date or
775 ura.effective_end_date <> lur.effective_end_date);
776
777 begin
778 <<InvalidEffectiveDates>>
779 loop
780 open c_invalidEffectiveDates;
781 fetch c_invalidEffectiveDates bulk collect
782 into l_LURTab,
783 l_URAEffectiveStartDate,
784 l_URAEffectiveEndDate
785 limit p_maxRows;
786 close c_invalidEffectiveDates;
787 if (l_LURTab.COUNT > 0) then
788 begin
789 forall i in l_LURTab.FIRST..l_LURTab.LAST
790 update WF_LOCAL_USER_ROLES
791 set EFFECTIVE_START_DATE=l_URAEffectiveStartDate(i),
792 EFFECTIVE_END_DATE=l_URAEffectiveEndDate(i)
793 where ROWID = l_LURTab(i);
794 exception
795 when others then
796 if (c_invalidEffectiveDates%ISOPEN) then
797 close c_invalidEffectiveDates;
798 end if;
799 raise;
800 end;
801 end if;
802 if (l_LURTab.COUNT < p_maxRows) then
803 exit InvalidEffectiveDates;
804 end if;
805 end loop InvalidEffectiveDates;
806 exception
807 when others then
808 if c_invalidEffectiveDates%ISOPEN then
809 close c_invalidEffectiveDates;
810 end if;
811 raise;
812 end FixLUREffectiveDates;
813
814 ------------------------------------------------------------------------------
815 /*
816 ** ValidateUserRoles - Validates and corrects denormalized user and role
817 ** information in user/role relationships.
818 */
819 PROCEDURE ValidateUserRoles(p_BatchSize in NUMBER,
820 p_username in varchar2,
821 p_rolename in varchar2,
822 p_check_dangling in BOOLEAN,
823 p_check_missing_ura in BOOLEAN,
824 p_UpdateWho in BOOLEAN,
825 p_parallel_processes in number) is
826
827 ColumnsMissing EXCEPTION;
828 TooManyRows EXCEPTION;
829
830 pragma exception_init(ColumnsMissing, -904);
831 pragma exception_init(TooManyRows, -1422);
832
833 TYPE charTab IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
834 TYPE dateTab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
835 TYPE numTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
836 TYPE idTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
837 TYPE origTab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
838 TYPE ownerTAGTAB IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
839
840 l_roleSrcTAB WF_DIRECTORY.roleTable;
841 l_userSrcTAB WF_DIRECTORY.userTable;
842 l_roleDestTAB WF_DIRECTORY.roleTable;
843 l_userDestTAB WF_DIRECTORY.userTable;
844 l_rowIDTAB idTab;
845 l_rowIDSrcTAB idTab;
846 l_rowIDDestTAB idTab;
847 l_stgIDTAB idTab;
848 l_userStartSrcTAB dateTab;
849 l_roleStartSrcTAB dateTab;
850 l_userEndSrcTAB dateTab;
851 l_roleEndSrcTAB dateTab;
852 l_effStartSrcTAB dateTab;
853 l_effEndSrcTAB dateTab;
854 l_AssignTAB charTab;
855 l_userStartDestTAB dateTab;
856 l_roleStartDestTAB dateTab;
857 l_userEndDestTAB dateTab;
858 l_roleEndDestTAB dateTab;
859 l_effStartDestTAB dateTab;
860 l_effEndDestTAB dateTab;
861 l_relIDTAB numTab;
862 l_maxRows number;
863 l_userOrigIDSrcTAB numTab;
864 l_roleOrigIDSrcTAB numTab;
865 l_userOrigIDDestTAB numTab;
866 l_roleOrigIDDEstTAB numTab;
867 l_userOrigSrcTAB origTab;
868 l_roleOrigSrcTAB origTab;
869 l_userOrigDestTAB origTab;
870 l_roleOrigDestTAB origTab;
871 l_assigningRoleSrcTAB WF_DIRECTORY.roleTable;
872 l_asgStartSrcTAB dateTab;
873 l_asgEndSrcTAB dateTab;
874 l_startSrcTAB dateTab;
875 l_endSrcTAB dateTab;
876
877 l_startDestTAB dateTab;
878 l_endDestTAB dateTab;
879 l_partTAB numTab;
880 l_userID number;
881 l_empID number;
882
883 sumTabIndex number;
884 ur_index number;
885 l_eIndex number;
886
887 l_activeAssigned boolean;
888 l_updateDateTAB dateTAB;
889 l_createDateTAB dateTAB;
890 l_updatedByTAB numTAB;
891 l_updateLoginTAB numTAB;
892 l_createdByTAB numTAB;
893 l_parentOrigTAB origTAB;
894 l_parentOrigIDTAB numTAB;
895 l_ownerTAGS ownerTAGTAB;
896 -- <bug 6823723>
897 l_sql varchar2(6000);
898 l_defaultParProc number;
899 l_parallelProc varchar2(5);
900
901 result number;
902 l_lockhandle varchar2(200);
903
904 -- Bug 6752821. Each cursor will have three more variations depending
905 -- on wheather a username and/or rolename is specified
906 -- cursor named %1 used when both username and rolename are provided
907 -- cursor named %2 used when only username is provided
908 -- cursor named %3 used when only rolename is provided
909 -- cursor named %4 used when none are provided
910
911 -- Missing records in WF_USER_ROLE_ASSIGNMENTS
912 -- Using same cursor parameter names as Procedures' param names.
913 cursor c_missing_user_role_asg(p_username varchar2, p_rolename varchar2 ) is
914 select user_name, role_name, -1, start_date, expiration_date,
915 created_by, creation_date, last_updated_by, last_update_date,
916 last_update_login, user_start_date, role_start_date,
917 user_end_date, role_end_date, partition_id,
918 effective_start_date, effective_end_date, user_orig_system,
919 user_orig_system_id, role_orig_system, role_orig_system_id,
920 parent_orig_system, parent_orig_system_id, owner_tag
921 from wf_local_user_roles wur
922 where (p_username IS NULL OR wur.user_name=p_username)
923 and (p_rolename IS NULL OR wur.role_name=p_rolename)
924 and not exists (select null
925 from wf_user_role_assignments wura
926 where wura.user_name = wur.user_name
927 and wura.role_name = wur.role_name
928 and (p_username is null OR wura.user_name = p_username)
929 and (p_rolename is null or wura.role_name = p_rolename)
930 );
931
932
933 -- Invalid and Duplicated records in the (FND_USR) partition
934 -- For bug 6752821: no need to consider role_name, it does not intervene,
935 -- no (FND_RESP) partition is considered.
936 cursor c_invalid_fnd_users (p_username varchar2) is
937 select wu.rowid, wu.orig_system old_orig_system,
938 wu.orig_system_id old_orig_system_id,
939 decode(nvl(fu.employee_id, -1),-1,'FND_USR','PER') new_orig_system,
940 nvl(fu.employee_id, fu.user_id)
941 from wf_local_roles partition (FND_USR) wu,
942 fnd_user fu
943 where wu.name = fu.user_name
944 and (p_username is null or wu.name = p_username)
945 and (wu.orig_system <> decode(nvl(fu.employee_id, -1),-1,'FND_USR','PER')
946 or wu.orig_system_id <> nvl(fu.employee_id, fu.user_id)
947 );
948
949
950 -- Records with invalid or duplicate FND_USR/PER references
951 -- in WF_LOCAL_USER_ROLES
952 -- For bug 6752821: no need for cursors %2 and %3 as role_name does not intervene,
953 -- no (FND_RESP) partition is considered.
954
955 cursor c_invalOrigSys (p_username varchar2) is
956 select wu.orig_system, wu.orig_system_id,
957 wur.role_orig_system, wur.role_orig_system_id,
958 wur.partition_id, wur.rowid
959 from wf_local_user_roles wur,
960 wf_local_roles partition (FND_USR) wu
961 where (p_username is null or wu.name = p_username )
962 and wu.name = wur.user_name
963 and wur.user_orig_system in ('FND_USR','PER')
964 and (wur.user_orig_system <> wu.orig_system
965 or wur.user_orig_system_id <> wu.orig_system_id
966 --check for role_orig_system in case of self-reference
967 or (wur.partition_id=1
968 and (wur.role_orig_system <> wu.orig_system
969 or wur.role_orig_system_id <> wu.orig_system_id)
970 )
971 );
972
973
974
975 --We will correct the orig_system, orig_system_id information for any
976 --incorrect fnd_usr/per user/role records. This is processed after the
977 --fnd_usr/per records in wf_local_roles are validated. The dates will be
978 --resolved in when c_userRoleAssignments are resolved.
979 -- For bug 6752821: no need to consider rolename as this check is for self-reference
980
981 cursor c_userSelfReference (p_username varchar2) is
982 select wura.rowid, wur.rowid, wu.start_date, wu.expiration_date,
983 wu.orig_system, wu.orig_system_id
984 from wf_local_user_roles partition (FND_USR) wur,
985 wf_local_roles partition (FND_USR) wu,
986 wf_user_role_assignments partition (FND_USR) wura
987 --Equi-joins to select the proper relationships between the tables
988 where (p_username is null OR wura.user_name = p_username)
989 and wura.partition_id = wu.partition_id
990 and wura.partition_id = wu.partition_id
991 and wur.user_name = wu.name
992 and wur.role_name = wu.name
993 and wura.assigning_role = wu.name
994 and wura.user_name = wu.name
995 and wura.role_name = wu.name
996 --Criteria to select records that need to be corrected, beginning with
997 --broad checks (if effective dates are null, no reason to check further)
998 --and working down to more specific checks between the orig_system/id
999 and ((wur.effective_start_date is null or
1000 wur.effective_end_date is null or
1001 wura.effective_start_date is null or
1002 wura.effective_end_date is null)
1003 or ((wur.user_orig_system <> wu.orig_system) or
1004 (wur.user_orig_system_id <> wu.orig_system_id) or
1005 (wur.role_orig_system <> wu.orig_system) or
1006 (wur.role_orig_system_id <> wu.orig_system_id))
1007 or (wura.user_orig_system is null or wura.role_orig_system is null or
1008 wura.user_orig_system_id is null or
1009 wura.user_orig_system_id is null)
1010 or (wura.user_orig_system <> wu.orig_system)
1011 or (wura.user_orig_system_id <> wu.orig_system_id)
1012 or (wura.role_orig_system <> wu.orig_system)
1013 or (wura.role_orig_system_id <> wu.orig_system_id)
1014 or (wu.start_date is null and
1015 (wur.start_date is not null or
1016 wur.user_start_date is not null or
1017 wur.role_start_date is not null or
1018 wur.effective_start_date <> to_date(1,'J')))
1019 or (wu.start_date is not null and
1020 (wur.start_date is null or wur.user_start_date is null or
1021 wur.role_start_date is null or wur.start_date <> wu.start_date or
1022 wur.user_start_date <> wu.start_date or
1023 wur.role_start_date <> wu.start_date or
1024 wur.effective_start_date <> wu.start_date))
1025 or (wu.expiration_date is null and
1026 (wur.expiration_date is not null or
1027 wur.user_end_date is not null or wur.role_end_date is not null or
1028 wur.effective_end_date <> to_date('9999/01/01','YYYY/MM/DD')))
1029 or (wu.expiration_date is not null and
1030 (wur.expiration_date is null or wur.user_end_date is null or
1031 wur.role_end_date is null or
1032 wur.expiration_date <> wu.expiration_date or
1033 wur.user_end_date <> wu.expiration_date or
1034 wur.role_end_date <> wu.expiration_date or
1035 wur.effective_end_date <> wu.expiration_date)));
1036
1037 --Now we will correct other user/role relationships, including the user
1038 --orig_system/id information of any record that an fnd_usr/per may be
1039 --participating in.
1040 cursor c_UserRoleAssignments (p_username varchar2, p_rolename varchar2) is
1041 select rowid, wura_id,wur_id,role_name,user_name,
1042 assigning_role, start_date, end_Date,role_start_date,
1043 role_end_date, user_start_date,user_end_date,
1044 role_orig_system,role_orig_system_id,
1045 user_orig_system, user_orig_system_id,
1046 assigning_role_start_date, assigning_role_end_date,
1047 effective_start_date, effective_end_date,
1048 relationship_id
1049 from wf_ur_validate_stg
1050 where (p_username is null OR user_name = p_username)
1051 and (p_rolename is null OR role_name = p_rolename)
1052 order by ROLE_NAME, USER_NAME;
1053
1054 -- Dangling records
1055 CURSOR dangling_UR_refs (p_username varchar2 , p_rolename varchar2) is
1056 select rowid
1057 from wf_local_user_roles
1058 where (p_username IS NULL OR user_name= p_username )
1059 AND (p_rolename IS NULL OR role_name = p_rolename)
1060 AND ( not exists (select null from wf_local_roles
1061 WHERE name= user_name
1062 AND (p_username IS NULL OR name= p_username)
1063 )
1064 or not EXISTS (select null from wf_local_roles
1065 WHERE NAME = role_name
1066 AND (p_rolename IS NULL OR name= p_rolename)
1067 )
1068 );
1069
1070
1071 -- Same from user_role_assignments
1072 CURSOR dangling_URA_refs (p_username varchar2, p_rolename varchar2) is
1073 select rowid
1074 from wf_user_role_assignments
1075 where (p_username IS NULL OR user_name = p_username )
1076 and (p_rolename IS NULL OR role_name = p_rolename )
1077 -- Either user name or role name NOT in wf_local_roles
1078 and (user_name not in (select name from wf_local_roles
1079 WHERE (p_username is null or name = p_username)
1080 )
1081 -- Check RoleName
1082 or role_name not in (select name from wf_local_roles
1083 WHERE (p_rolename IS NULL OR NAME = p_rolename)
1084 )
1085 );
1086
1087
1088 l_modulePkg varchar2(240) := 'WF_MAINTENANCE.ValidateUserRoles';
1089
1090 begin
1091 -- Log only
1092 -- BINDVAR_SCAN_IGNORE[2]
1093 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_modulePkg,
1094 'Begin ValidateUserRoles('||p_batchSize||')');
1095 --First validate the inbound parameter(s)
1096 if (p_BatchSize is NULL or (p_BatchSize < 1)) then
1097 l_MaxRows := 10000;
1098 else
1099 l_MaxRows := p_BatchSize;
1100 end if;
1101
1102 -- Acquire a session lock to ensure that only one instance of the program is
1103 -- running at a time.
1104 dbms_lock.allocate_unique('WF_MAINTENANCE.ValidateUserRoles',l_lockhandle);
1105
1106 if (dbms_lock.request(lockhandle=>l_lockhandle,
1107 lockmode=>dbms_lock.x_mode,
1108 timeout=>0) <> 0) then
1109 wf_core.raise('WF_LOCK_FAIL');
1110 end if;
1111
1112 if (p_check_dangling is not null and p_check_dangling) then
1113 --Validate that the users and roles who participate in user/role
1114 --relationships actually exist.
1115 begin
1116 <<Dangling_UR_Reference>>
1117 loop
1118 open dangling_UR_refs (p_username, p_rolename) ;
1119 fetch dangling_UR_refs bulk collect into l_rowIDTAB limit l_maxRows;
1120 close dangling_UR_refs;
1121
1122 if (l_rowIDTAB.COUNT > 0) then
1123 forall i in l_rowIDTAB.FIRST..l_rowIDTAB.LAST
1124 DELETE from WF_LOCAL_USER_ROLES
1125 WHERE rowid = l_rowIDTAB(i);
1126 commit;
1127 end if;
1128
1129 if (l_rowIDTAB.COUNT < l_maxRows) then
1130 exit Dangling_UR_Reference;
1131 end if;
1132 end loop Dangling_UR_Reference;
1133 exception
1134 when others then
1135 if dangling_UR_refs%ISOPEN then
1136 close dangling_UR_refs;
1137 end if;
1138
1139 raise;
1140 end;
1141 --Truncate the rowid tab.
1142 l_rowIDTAB.DELETE;
1143 begin
1144 <<Dangling_URA_Reference>>
1145 loop
1146 open dangling_URA_refs (p_username, p_rolename) ;
1147 fetch dangling_URA_refs bulk collect into l_rowIDTAB limit l_maxRows;
1148 close dangling_URA_refs;
1149
1150 if (l_rowIDTAB.COUNT > 0) then
1151 forall i in l_rowIDTAB.FIRST..l_rowIDTAB.LAST
1152 DELETE from WF_USER_ROLE_ASSIGNMENTS
1153 WHERE rowid = l_rowIDTAB(i);
1154 commit;
1155 end if;
1156
1157 if (l_rowIDTAB.COUNT < l_maxRows) then
1158 exit Dangling_URA_Reference;
1159 end if;
1160 end loop Dangling_URA_Reference;
1161 exception
1162 when others then
1163 if dangling_URA_refs%ISOPEN then
1164 close dangling_URA_refs;
1165 end if;
1166
1167 raise;
1168 end;
1169 --Truncate the rowid tab.
1170 l_rowIDTAB.DELETE;
1171 end if;
1172
1173 if (p_check_missing_ura is not null and p_check_missing_ura) then
1174 --Validate that the users and roles who participate in user/role
1175 --relationships actually exist.
1176 begin
1177 <<Missing_URA_Reference>>
1178 loop
1179 l_userSrcTAB.DELETE;
1180 open c_missing_user_role_asg (p_username, p_rolename);
1181 fetch c_missing_user_role_asg bulk collect into l_userSrcTAB,
1182 l_roleSrcTAB, l_relIDTAB, l_startSrcTAB, l_endSrcTAB,
1183 l_createdByTAB, l_createDateTAB, l_updatedByTAB, l_updateDateTAB,
1184 l_updateLoginTAB, l_userStartSrcTAB, l_roleStartSrcTAB,
1185 l_userEndSrcTAB, l_roleEndSrcTAB, l_partTAB, l_effStartSrcTAB,
1186 l_effEndSrcTAB, l_userOrigSrcTAB, l_userOrigIDSrcTAB,
1187 l_roleOrigSrcTAB, l_roleOrigIDSrcTAB, l_parentOrigTAB,
1188 l_parentOrigIDTAB, l_ownerTAGS
1189 limit l_maxRows;
1190 close c_missing_user_role_asg;
1191
1192
1193 if (l_userSrcTAB.COUNT > 0) then
1194 begin
1195 forall i in l_userSrcTAB.FIRST..l_userSrcTAB.LAST save exceptions
1196
1197 insert into WF_USER_ROLE_ASSIGNMENTS (USER_NAME,
1198 ROLE_NAME, RELATIONSHIP_ID, ASSIGNING_ROLE, START_DATE,
1199 END_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
1200 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, USER_START_DATE,
1201 ROLE_START_DATE, ASSIGNING_ROLE_START_DATE, USER_END_DATE,
1202 ROLE_END_DATE, ASSIGNING_ROLE_END_DATE, PARTITION_ID,
1203 EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, USER_ORIG_SYSTEM,
1204 USER_ORIG_SYSTEM_ID, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID,
1205 PARENT_ORIG_SYSTEM, PARENT_ORIG_SYSTEM_ID, OWNER_TAG)
1206 values (l_userSrcTAB(i), l_roleSrcTAB(i), l_relIDTAB(i),
1207 l_roleSrcTAB(i), l_startSrcTAB(i), l_endSrcTAB(i),
1208 l_createdByTAB(i), l_createDateTAB(i), l_updatedByTAB(i),
1209 l_updateDateTAB(i), l_updateLoginTAB(i), l_userStartSrcTAB(i),
1210 l_roleStartSrcTAB(i), l_roleStartSrcTAB(i), l_userEndSrcTAB(i),
1211 l_roleEndSrcTAB(i), l_roleEndSrcTAB(i), l_partTAB(i),
1212 l_effStartSrcTAB(i), l_effEndSrcTAB(i), l_userOrigSrcTAB(i),
1213 l_userOrigIDSrcTAB(i), l_roleOrigSrcTAB(i),
1214 l_roleOrigIDSrcTAB(i), l_parentOrigTAB(i),
1215 l_parentOrigIDTAB(i), l_ownerTAGS(i));
1216 commit;
1217 exception
1218 when others then
1219 for j in 1..sql%bulk_exceptions.count loop
1220 if (sql%bulk_exceptions(j).ERROR_CODE = 1) then
1221 --Ignore a dup_val_on_index. That just means that the
1222 --user/role name combination was already assigned during this
1223 --job.
1224 null;
1225 else
1226 raise;
1227 end if;
1228 end loop;
1229 end;
1230 end if;
1231
1232 if (l_userSrcTAB.COUNT < l_maxRows) then
1233 exit Missing_URA_Reference;
1234 end if;
1235 end loop Missing_URA_Reference;
1236 exception
1237 when others then
1238 if c_missing_user_role_asg%ISOPEN then
1239 close c_missing_user_role_asg;
1240 end if;
1241
1242 raise;
1243 end;
1244 end if;
1245
1246 --Now we will correct any invalid fnd_usr records in WF_LOCAL_ROLES. This
1247 --orig_system can have errors because we routinely have to change the
1248 --orig_system, orig_system_id whenever a user is associated or dis-associated
1249 --with an employee.
1250 begin
1251 <<fnd_usr_loop>>
1252 loop
1253 --Clear the l_rowIDTAB before the next iteration
1254 l_rowIDTAB.DELETE;
1255 open c_invalid_fnd_users (p_username);
1256 fetch c_invalid_fnd_users bulk collect into l_rowIDTAB, l_userOrigSrcTAB,
1257 l_userOrigIDSrcTAB, l_userOrigDestTAB, l_userOrigIDDestTAB
1258 limit l_maxRows;
1259 close c_invalid_fnd_users;
1260
1261 if (l_rowIDTAB.count > 0) then
1262 begin
1263 forall i in l_rowIDTAB.FIRST..l_rowIDTAB.LAST save exceptions
1264 UPDATE WF_LOCAL_ROLES
1265 SET orig_system = l_userOrigDestTAB(i),
1266 orig_system_id = l_userOrigIDDestTAB(i)
1267 WHERE rowid = l_rowIDTAB(i);
1268 exception
1269 when others then
1270 for j in 1..sql%bulk_exceptions.count loop
1271 l_eIndex := sql%bulk_exceptions(j).ERROR_INDEX;
1272 delete from wf_local_roles
1273 where rowid = l_rowIDTAB(l_eIndex);
1274 end loop;
1275 end;
1276 commit;
1277 end if;
1278 if (l_rowIDTab.count < l_maxRows) then
1279 commit;
1280 exit fnd_usr_loop;
1281 end if;
1282 end loop fnd_usr_loop;
1283 exception
1284 when others then
1285 if c_invalid_fnd_users%ISOPEN then
1286 close c_invalid_fnd_users;
1287 end if;
1288
1289 raise;
1290 end; --End of duplicate/invalid FND_USR/PER user correction.
1291
1292 -- Now we correct the FND_USR/PER orig_system values on the user side
1293 -- of user/role assignments as well as user-self-references in
1294 -- WF_LOCAL_USER_ROLES
1295
1296 begin
1297 <<inval_orig_sys_loop>>
1298 loop
1299 --Clear the l_rowIDTAB before the next iteration
1300 l_rowIDTAB.DELETE;
1301
1302 open c_invalOrigSys (p_username) ;
1303 fetch c_invalOrigSys bulk collect into l_userOrigSrcTAB,
1304 l_userOrigIDSrcTAB, l_roleOrigSrcTAB, l_roleOrigIDSrcTAB,
1305 l_partTAB,l_rowIDTAB
1306 limit l_maxRows;
1307 close c_invalOrigSys;
1308
1309
1310 if (l_rowIDTAB.count > 0) then
1311 for i in l_rowIDTAB.FIRST..l_rowIDTAB.LAST loop
1312 -- check whether this is a case of user=role
1313 if l_partTAB(i) = 1 then
1314 -- set the role_orig_system values as well.
1315 l_roleOrigSrcTAB(i) := l_userOrigSrcTAB(i);
1316 l_roleOrigIDSrcTAB(i) := l_userOrigIDSrcTAB(i);
1317 end if;
1318 end loop;
1319 --perform the bulk update.. delete duplicates in case of
1320 -- dup_val_on_index Exception.
1321 begin
1322 forall i in l_rowIDTAB.FIRST..l_rowIDTAB.LAST save exceptions
1323 UPDATE WF_LOCAL_USER_ROLES
1324 SET user_orig_system = l_userOrigSrcTAB(i),
1325 user_orig_system_id = l_userOrigIDSrcTAB(i),
1326 role_orig_system = l_roleOrigSrcTAB(i),
1327 role_orig_system_id = l_roleOrigIDSrcTAB(i)
1328 WHERE rowid = l_rowIDTAB(i);
1329 exception
1330 when others then
1331 for j in 1..sql%bulk_exceptions.count loop
1332 if (sql%bulk_exceptions(j).ERROR_CODE = 1) then
1333 l_eIndex := sql%bulk_exceptions(j).ERROR_INDEX;
1334 delete from wf_local_user_roles
1335 where rowid = l_rowIDTAB(l_eIndex);
1336 end if;
1337 end loop;
1338 end;
1339 commit;
1340 end if;
1341 if (l_rowIDTab.count < l_maxRows) then
1342 commit;
1343 exit inval_orig_sys_loop;
1344 end if;
1345 end loop inval_orig_sys_loop;
1346 exception
1347 when others then
1348 if c_invalOrigSys%ISOPEN then
1349 close c_invalOrigSys;
1350 end if;
1351
1352 raise;
1353 end; --End of duplicate/invalid FND_USR/PER correction in WF_LOCAL_USER_ROLES.
1354
1355 --Next, we correct the corrupt self-reference records in
1356 --wf_user_role_Assignments and wf_local_user_Roles.
1357 begin
1358 <<self_refer_loop>>
1359 loop
1360 --We will commit on each loop cycle to prevent fetch across commits, we will
1361 --close and reopen the cursor on each fetch. This would mean that we are
1362 --pulling more than 10000 if we loop more than once and would rather have
1363 --a performance impact here than encounter rollback segment problems.
1364 --The where criteria of the cursor will not re-select the updated rows so
1365 --we do not have to worry about retaining a position.
1366
1367 open c_userSelfReference (p_username);
1368 fetch c_userSelfReference
1369 bulk collect into l_rowIDTAB, l_rowIDSrcTAB, l_startSrcTAB,
1370 l_endSrcTAB, l_userOrigSrcTAB, l_userOrigIDSrcTAB
1371 limit l_maxRows;
1372 close c_userSelfReference;
1373
1374 --We now have pl/sql tables in memory that we can update with the new
1375 --values. So we loop through them and begin the processing.
1376 if (l_rowIDTAB.COUNT < 1) then
1377 exit self_refer_loop;
1378 end if;
1379
1380 --We now have a complete series of pl/sql tables with
1381 --all of the start/end dates and calculated effective start/end dates
1382 --We can then issue the bulk update.
1383 begin
1384 if (p_UpdateWho is not null and p_UpdateWho) then
1385 forall tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST save exceptions
1386 update WF_USER_ROLE_ASSIGNMENTS
1387 set ROLE_START_DATE = l_StartSrcTAB(tabIndex),
1388 ROLE_END_DATE = l_EndSrcTAB(tabIndex),
1389 USER_START_DATE = l_StartSrcTAB(tabIndex),
1390 USER_END_DATE = l_EndSrcTAB(tabIndex),
1391 START_DATE = l_StartSrcTAB(tabIndex),
1392 END_DATE = l_EndSrcTAB(tabIndex),
1393 EFFECTIVE_START_DATE = nvl(l_StartSrcTAB(tabIndex),
1394 to_date(1,'J')),
1395 EFFECTIVE_END_DATE = nvl(l_EndSrcTAB(tabIndex),
1396 to_date('9999/01/01', 'YYYY/MM/DD')),
1397 ASSIGNING_ROLE_START_DATE = l_StartSrcTAB(tabIndex),
1398 ASSIGNING_ROLE_END_DATE = l_EndSrcTAB(tabIndex),
1399 USER_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
1400 ROLE_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
1401 USER_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex),
1402 ROLE_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex),
1403 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1404 LAST_UPDATE_DATE = SYSDATE,
1405 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1406 where rowid = l_rowIDTAB(tabIndex);
1407 else --donot touch the WHO columns. This is default behavior
1408 forall tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST save exceptions
1409 update WF_USER_ROLE_ASSIGNMENTS
1410 set ROLE_START_DATE = l_StartSrcTAB(tabIndex),
1411 ROLE_END_DATE = l_EndSrcTAB(tabIndex),
1412 USER_START_DATE = l_StartSrcTAB(tabIndex),
1413 USER_END_DATE = l_EndSrcTAB(tabIndex),
1414 START_DATE = l_StartSrcTAB(tabIndex),
1415 END_DATE = l_EndSrcTAB(tabIndex),
1416 EFFECTIVE_START_DATE = nvl(l_StartSrcTAB(tabIndex),
1417 to_date(1,'J')),
1418 EFFECTIVE_END_DATE = nvl(l_EndSrcTAB(tabIndex),
1419 to_date('9999/01/01', 'YYYY/MM/DD')),
1420 ASSIGNING_ROLE_START_DATE = l_StartSrcTAB(tabIndex),
1421 ASSIGNING_ROLE_END_DATE = l_EndSrcTAB(tabIndex),
1422 USER_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
1423 ROLE_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
1424 USER_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex),
1425 ROLE_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex)
1426 where rowid = l_rowIDTAB(tabIndex);
1427 end if;
1428 exception
1429 when others then
1430 for j in 1..sql%bulk_exceptions.count loop
1431 if (sql%bulk_exceptions(j).ERROR_CODE = 1) then
1432 --If update violates dup_val_on_index, we can simply delete.
1433 l_eIndex := sql%bulk_exceptions(j).ERROR_INDEX;
1434 delete from wf_user_role_assignments
1435 where rowid = l_rowIDTAB(l_eIndex);
1436 else
1437 raise;
1438 end if;
1439 end loop;
1440 end;
1441
1442 --Commit work to save rollback
1443 commit;
1444
1445 -- update WF_LOCAL_USER_ROLES
1446 begin
1447 if (p_UpdateWho is not null and p_UpdateWho) then
1448 forall tabIndex in l_rowIDSrcTAB.FIRST..l_rowIDSrcTAB.LAST save exceptions
1449 update wf_local_user_roles partition (FND_USR)
1450 set ROLE_START_DATE = l_StartSrcTAB(tabIndex),
1451 ROLE_END_DATE = l_EndSrcTAB(tabIndex),
1452 USER_START_DATE = l_StartSrcTAB(tabIndex),
1453 USER_END_DATE = l_EndSrcTAB(tabIndex),
1454 START_DATE = l_StartSrcTAB(tabIndex),
1455 EXPIRATION_DATE = l_EndSrcTAB(tabIndex),
1456 EFFECTIVE_START_DATE = nvl(l_StartSrcTAB(tabIndex),
1457 to_date(1,'J')),
1458 EFFECTIVE_END_DATE = nvl(l_EndSrcTAB(tabIndex),
1459 to_date('9999/01/01', 'YYYY/MM/DD')),
1460 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1461 LAST_UPDATE_DATE = SYSDATE,
1462 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1463 where rowid = l_rowIDSrcTAB(tabIndex);
1464 else --donot touch the WHO columns. This is default behavior
1465 forall tabIndex in l_rowIDSrcTAB.FIRST..l_rowIDSrcTAB.LAST save exceptions
1466 update wf_local_user_roles partition (FND_USR)
1467 set ROLE_START_DATE = l_StartSrcTAB(tabIndex),
1468 ROLE_END_DATE = l_EndSrcTAB(tabIndex),
1469 USER_START_DATE = l_StartSrcTAB(tabIndex),
1470 USER_END_DATE = l_EndSrcTAB(tabIndex),
1471 START_DATE = l_StartSrcTAB(tabIndex),
1472 EXPIRATION_DATE = l_EndSrcTAB(tabIndex),
1473 EFFECTIVE_START_DATE = nvl(l_StartSrcTAB(tabIndex),
1474 to_date(1,'J')),
1475 EFFECTIVE_END_DATE = nvl(l_EndSrcTAB(tabIndex),
1476 to_date('9999/01/01', 'YYYY/MM/DD'))
1477 where rowid = l_rowIDSrcTAB(tabIndex);
1478 end if;
1479 end;
1480
1481 if (l_rowIDTAB.COUNT < l_maxRows) then --Last batch, no need to refetch
1482 commit;
1483 exit self_refer_loop;
1484 else
1485 -- reset the ROWID Table before the next set of fetch
1486 l_rowIDTAB.DELETE;
1487 commit;
1488 end if;
1489 end loop self_refer_loop;
1490 exception
1491 when others then
1492 if (c_userSelfReference%isOpen) then
1493 close c_userSelfReference;
1494 end if;
1495
1496 raise;
1497 end; --end of self-reference fix
1498
1499 commit;--commit the self reference records
1500
1501 -- reset the PL/SQL tables before we fetch the user-role cursor
1502 l_rowIDTAB.delete;
1503 l_rowIDSrcTAB.delete;
1504 l_startSrcTAB.delete;
1505 l_endSrcTAB.delete;
1506 l_userOrigSrcTAB.delete;
1507 l_userOrigIDSrcTAB.delete;
1508
1509 --Initialize the sumTabIndex counter.
1510 sumTabIndex := 0;
1511
1512 --Enable parallel DML
1513 execute IMMEDIATE 'alter session enable parallel dml';
1514
1515 --truncate the stage table now
1516 WF_DDL.TruncateTable('WF_UR_VALIDATE_STG',WF_CORE.Translate('WF_SCHEMA'),
1517 FALSE);
1518
1519 -- <bug 6823723>
1520 select min(to_number(value))
1521 into l_defaultParProc
1522 from v$parameter
1523 where name in ('parallel_max_servers','cpu_count');
1524
1525 if ((p_parallel_processes is NULL) or (p_parallel_processes < 1) or
1526 (mod(p_parallel_processes, 1) <> 0) or (p_parallel_processes > l_defaultParProc)
1527 ) then
1528 l_parallelProc := to_char(l_defaultParProc) ;
1529 else
1530 l_parallelProc := to_char(p_parallel_processes);
1531 end if;
1532 -- </bug 6823723>
1533
1534 -- populate the stage table
1535 -- bug 6823723. Now inserting as a dynamic DML to include the number of parallel processes
1536 l_sql :=
1537 'INSERT /*+ append parallel(WF_UR_VALIDATE_STG,'|| l_parallelProc ||') */
1538 INTO WF_UR_VALIDATE_STG (WURA_ID, WUR_ID , ROLE_NAME , USER_NAME ,
1539 ASSIGNING_ROLE , START_DATE , END_DATE , ROLE_START_DATE, ROLE_END_DATE
1540 , USER_START_DATE , USER_END_DATE , ROLE_ORIG_SYSTEM ,
1541 ROLE_ORIG_SYSTEM_ID , USER_ORIG_SYSTEM , USER_ORIG_SYSTEM_ID ,
1542 ASSIGNING_ROLE_START_DATE , ASSIGNING_ROLE_END_DATE ,
1543 EFFECTIVE_START_DATE , EFFECTIVE_END_DATE , RELATIONSHIP_ID )
1544 SELECT /*+ ordered parallel(WURA,'|| l_parallelProc ||') parallel(WR,'|| l_parallelProc ||
1545 ') parallel (wu,'|| l_parallelProc ||')
1546 parallel (WAR,'|| l_parallelProc ||') parallel(WUR,'|| l_parallelProc ||') */
1547 WURA.ROWID, WUR.ROWID, WURA.ROLE_NAME, WURA.USER_NAME,
1548 WURA.ASSIGNING_ROLE,
1549 DECODE(WURA.USER_NAME, WURA.ROLE_NAME, WU.START_DATE,
1550 WURA.START_DATE) START_DATE,
1551 DECODE(WURA.USER_NAME, WURA.ROLE_NAME, WU.EXPIRATION_DATE,
1552 WURA.END_DATE) END_DATE,
1553 WR.START_DATE, WR.EXPIRATION_DATE, WU.START_DATE,
1554 WU.EXPIRATION_DATE, WR.ORIG_SYSTEM, WR.ORIG_SYSTEM_ID,
1555 WU.ORIG_SYSTEM, WU.ORIG_SYSTEM_ID, WAR.START_DATE,
1556 WAR.EXPIRATION_DATE,
1557 GREATEST(NVL(WURA.START_DATE, TO_DATE(1,''J'')),
1558 NVL(WURA.USER_START_DATE, TO_DATE(1,''J'')),
1559 NVL(WURA.ROLE_START_DATE, TO_DATE(1,''J'')),
1560 NVL(WURA.ASSIGNING_ROLE_START_DATE,
1561 TO_DATE(1,''J''))) EFFECTIVE_START_DATE,
1562 LEAST(NVL(WURA.END_DATE, TO_DATE(''9999/01/01'', ''YYYY/MM/DD'')),
1563 NVL(WURA.USER_END_DATE, TO_DATE(''9999/01/01'', ''YYYY/MM/DD'')),
1564 NVL(WURA.ROLE_END_DATE, TO_DATE(''9999/01/01'', ''YYYY/MM/DD'')),
1565 NVL(WURA.ASSIGNING_ROLE_END_DATE,
1566 TO_DATE(''9999/01/01'', ''YYYY/MM/DD''))) EFFECTIVE_END_DATE,
1567 WURA.RELATIONSHIP_ID
1568 FROM
1569 WF_USER_ROLE_ASSIGNMENTS WURA,
1570 WF_LOCAL_USER_ROLES WUR ,
1571 WF_LOCAL_ROLES WAR,
1572 WF_LOCAL_ROLES WU,
1573 WF_LOCAL_ROLES WR
1574 WHERE WURA.PARTITION_ID = WAR.PARTITION_ID
1575 AND WURA.ASSIGNING_ROLE=WAR.NAME
1576 AND WURA.USER_NAME= WUR.USER_NAME
1577 AND WURA.ROLE_NAME=WUR.ROLE_NAME
1578 AND WUR.USER_NAME = WU.NAME
1579 AND WUR.USER_ORIG_SYSTEM=WU.ORIG_SYSTEM
1580 AND WUR.USER_ORIG_SYSTEM_ID= WU.ORIG_SYSTEM_ID
1581 AND WUR.ROLE_NAME = WR.NAME
1582 AND WUR.ROLE_ORIG_SYSTEM= WR.ORIG_SYSTEM
1583 AND WUR.ROLE_ORIG_SYSTEM_ID= WR.ORIG_SYSTEM_ID
1584 AND WUR.PARTITION_ID = WR.PARTITION_ID
1585 AND WUR.PARTITION_ID <> 1
1586 AND WAR.PARTITION_ID <> 1
1587 AND ( ( WUR.EFFECTIVE_START_DATE IS NULL or
1588 WUR.EFFECTIVE_END_DATE IS NULL or
1589 WURA.EFFECTIVE_START_DATE IS NULL or
1590 WURA.EFFECTIVE_END_DATE IS NULL )
1591 OR ( WURA.EFFECTIVE_START_DATE <> GREATEST(NVL(WURA.START_DATE,
1592 TO_DATE(1,''J'')), NVL(WURA.USER_START_DATE, TO_DATE(1,''J'')), NVL(
1593 WURA.ROLE_START_DATE, TO_DATE(1,''J'')), NVL(
1594 WURA.ASSIGNING_ROLE_START_DATE, TO_DATE(1,''J''))) )
1595 OR ( WURA.EFFECTIVE_END_DATE <> LEAST(NVL(WURA.END_DATE, TO_DATE(
1596 ''9999/01/01'', ''YYYY/MM/DD'')), NVL(WURA.USER_END_DATE, TO_DATE(
1597 ''9999/01/01'', ''YYYY/MM/DD'')) , NVL(WURA.ROLE_END_DATE, TO_DATE(
1598 ''9999/01/01'', ''YYYY/MM/DD'')), NVL(WURA.ASSIGNING_ROLE_END_DATE,
1599 TO_DATE(''9999/01/01'', ''YYYY/MM/DD''))))
1600 OR (WURA.USER_NAME = WURA.ROLE_NAME and
1601 (nvl(wura.start_date, to_date(1,''J'')) <>
1602 nvl(wu.start_date, to_date(1,''J'')) or
1603 nvl(wura.end_date, to_date(''9999/01/01'', ''YYYY/MM/DD'')) <>
1604 nvl(wu.expiration_date, to_date(''9999/01/01'', ''YYYY/MM/DD''))))
1605 OR ( ( WUR.ASSIGNMENT_TYPE IS NULL )
1606 OR WUR.ASSIGNMENT_TYPE NOT IN (''D'', ''I'', ''B'') )
1607 OR ( WURA.USER_ORIG_SYSTEM IS NULL
1608 OR WURA.ROLE_ORIG_SYSTEM IS NULL
1609 OR WURA.USER_ORIG_SYSTEM_ID IS NULL
1610 OR WURA.ROLE_ORIG_SYSTEM_ID IS NULL )
1611 OR ( WURA.USER_ORIG_SYSTEM <> WU.ORIG_SYSTEM
1612 OR WURA.USER_ORIG_SYSTEM_ID <> WU.ORIG_SYSTEM_ID
1613 OR WURA.ROLE_ORIG_SYSTEM <> WR.ORIG_SYSTEM
1614 OR WURA.ROLE_ORIG_SYSTEM_ID <> WR.ORIG_SYSTEM_ID )
1615 OR ( ( WU.START_DATE IS NULL
1616 AND ( WUR.USER_START_DATE IS NOT NULL
1617 OR WURA.USER_START_DATE IS NOT NULL ) )
1618 OR ( WU.START_DATE IS NOT NULL
1619 AND ( WUR.USER_START_DATE IS NULL
1620 OR WUR.USER_START_DATE <> WU.START_DATE
1621 OR WURA.USER_START_DATE IS NULL
1622 OR WURA.USER_START_DATE <> WU.START_DATE ) )
1623 OR ( WU.EXPIRATION_DATE IS NULL
1624 AND ( WUR.USER_END_DATE IS NOT NULL
1625 OR WURA.USER_END_DATE IS NOT NULL ) )
1626 OR ( WU.EXPIRATION_DATE IS NOT NULL
1627 AND ( WUR.USER_END_DATE IS NULL
1628 OR WUR.USER_END_DATE <> WU.EXPIRATION_DATE
1629 OR WURA.USER_END_DATE IS NULL
1630 OR WURA.USER_END_DATE <> WU.EXPIRATION_DATE ) ) )
1631 OR ( ( WR.START_DATE IS NULL
1632 AND ( WUR.ROLE_START_DATE IS NOT NULL
1633 OR WURA.ROLE_START_DATE IS NOT NULL ) )
1634 OR ( WR.START_DATE IS NOT NULL
1635 AND ( WUR.ROLE_START_DATE IS NULL
1636 OR WUR.ROLE_START_DATE <> WR.START_DATE
1637 OR WURA.ROLE_START_DATE IS NULL
1638 OR WURA.ROLE_START_DATE <> WR.START_DATE ) )
1639 OR ( WR.EXPIRATION_DATE IS NULL
1640 AND ( WUR.ROLE_END_DATE IS NOT NULL
1641 OR WURA.ROLE_END_DATE IS NOT NULL ) )
1642 OR ( WR.EXPIRATION_DATE IS NOT NULL
1643 AND ( WUR.ROLE_END_DATE IS NULL
1644 OR WUR.ROLE_END_DATE <> WR.EXPIRATION_DATE
1645 OR WURA.ROLE_END_DATE IS NULL
1646 OR WURA.ROLE_END_DATE <> WR.EXPIRATION_DATE ) ) )
1647 OR ( ( WAR.START_DATE IS NULL
1648 AND WURA.ASSIGNING_ROLE_START_DATE IS NOT NULL )
1649 OR ( WAR.START_DATE IS NOT NULL
1650 AND ( WURA.ASSIGNING_ROLE_START_DATE IS NULL
1651 OR WURA.ASSIGNING_ROLE_START_DATE <> WAR.START_DATE ) )
1652 OR ( WAR.EXPIRATION_DATE IS NULL
1653 AND WURA.ASSIGNING_ROLE_END_DATE IS NOT NULL )
1654 OR ( WAR.EXPIRATION_DATE IS NOT NULL
1655 AND ( WURA.ASSIGNING_ROLE_END_DATE IS NULL
1656 OR WURA.ASSIGNING_ROLE_END_DATE <> WAR.EXPIRATION_DATE ) ) ) )' ;
1657
1658 execute IMMEDIATE l_sql;
1659 commit;
1660 execute IMMEDIATE 'alter session disable parallel dml';
1661
1662 -- ALSOSA. Current progress
1663 OPEN c_UserRoleAssignments(p_username, p_rolename);
1664
1665 <<outer_loop>>
1666 loop
1667 fetch c_UserRoleAssignments
1668 bulk collect into l_stgIDTAB, l_rowIDTAB, l_rowIDSrcTAB, l_roleSrcTAB,
1669 l_userSrcTAB, l_assigningRoleSrcTAB, l_startSrcTAB,
1670 l_endSrcTAB, l_roleStartSrcTAB, l_roleEndSrcTAB,
1671 l_userStartSrcTAB, l_userEndSrcTAB, l_roleOrigSrcTAB,
1672 l_roleOrigIDSrcTAB,l_userOrigSrcTAB, l_userOrigIDSrcTAB,
1673 l_asgStartSrcTAB, l_asgEndSrcTAB, l_effStartSrcTAB,
1674 l_effEndSrcTAB, l_relIDTAB
1675 limit l_maxRows;
1676
1677
1678 --We now have pl/sql tables in memory that we can update with the new
1679 --values. So we loop through them and begin the processing.
1680 if (l_rowIDTAB.COUNT < 1) then
1681 exit outer_loop;
1682 end if;
1683
1684 --We now have a complete series of pl/sql tables with
1685 --all of the start/end dates and calculated effective start/end dates
1686 --We can then issue the bulk update..
1687 if (p_UpdateWho is not null and p_UpdateWho) then
1688 forall tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST
1689 update WF_USER_ROLE_ASSIGNMENTS
1690 set ROLE_START_DATE = l_roleStartSrcTAB(tabIndex),
1691 ROLE_END_DATE = l_roleEndSrcTAB(tabIndex),
1692 USER_START_DATE = l_userStartSrcTAB(tabIndex),
1693 USER_END_DATE = l_userEndSrcTAB(tabIndex),
1694 START_DATE = l_startSrcTAB(tabIndex),
1695 END_DATE = l_endSRcTAB(tabIndex),
1696 EFFECTIVE_START_DATE = l_effStartSrcTAB(tabIndex),
1697 EFFECTIVE_END_DATE = l_effEndSrcTAB(tabIndex),
1698 ASSIGNING_ROLE_START_DATE = l_asgStartSrcTAB(tabIndex),
1699 ASSIGNING_ROLE_END_DATE = l_asgEndSrcTAB(tabIndex),
1700 USER_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
1701 ROLE_ORIG_SYSTEM=l_roleOrigSrcTAB(tabIndex),
1702 USER_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex),
1703 ROLE_ORIG_SYSTEM_ID=l_roleOrigIDSrcTAB(tabIndex),
1704 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1705 LAST_UPDATE_DATE = SYSDATE,
1706 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1707 where rowid = l_rowIDTAB(tabIndex);
1708 else --Donot touch WHO columns. This is default behavior
1709 forall tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST
1710 update WF_USER_ROLE_ASSIGNMENTS
1711 set ROLE_START_DATE = l_roleStartSrcTAB(tabIndex),
1712 ROLE_END_DATE = l_roleEndSrcTAB(tabIndex),
1713 USER_START_DATE = l_userStartSrcTAB(tabIndex),
1714 USER_END_DATE = l_userEndSrcTAB(tabIndex),
1715 START_DATE = l_startSrcTAB(tabIndex),
1716 END_DATE = l_endSRcTAB(tabIndex),
1717 EFFECTIVE_START_DATE = l_effStartSrcTAB(tabIndex),
1718 EFFECTIVE_END_DATE = l_effEndSrcTAB(tabIndex),
1719 ASSIGNING_ROLE_START_DATE = l_asgStartSrcTAB(tabIndex),
1720 ASSIGNING_ROLE_END_DATE = l_asgEndSrcTAB(tabIndex),
1721 USER_ORIG_SYSTEM=l_userOrigSrcTAB(tabIndex),
1722 ROLE_ORIG_SYSTEM=l_roleOrigSrcTAB(tabIndex),
1723 USER_ORIG_SYSTEM_ID=l_userOrigIDSrcTAB(tabIndex),
1724 ROLE_ORIG_SYSTEM_ID=l_roleOrigIDSrcTAB(tabIndex)
1725 where rowid = l_rowIDTAB(tabIndex);
1726 end if;
1727
1728 --We will reloop through the assignment pl/sql tables and populate the
1729 --summary pl/sql tables.
1730 <<summarize_assignments>>
1731 for tabIndex in l_rowIDTAB.FIRST..l_rowIDTAB.LAST loop
1732 --we need to insert into summary table if this is the first
1733 --record to be inserted or, we have a new user/role combination
1734 --in the assignment table, which hasnt yet been inserted into the
1735 --summary table
1736 if ((l_roleDestTab.COUNT < 1) or
1737 (l_rowIDSrcTAB(tabIndex) <> l_rowIDDestTAB(sumTabIndex))) then
1738 -- before inserting, check whether the summarytable has
1739 -- grown too large
1740 if sumTabIndex >= l_maxRows then
1741 --limit reached for summary table, so perform
1742 --the bulk update and clear off the table.
1743 --We need to perform the bulk update here in addition to
1744 --bulk update after exit from the loop, so that clearing
1745 --the summary table will not lose user/role effective date
1746 --information when duplicate user/role
1747 --combinations are spread across multiple groups
1748 if (p_UpdateWho is not null and p_UpdateWho) then
1749 forall destTabIndex in l_roleDestTab.FIRST..l_roleDestTab.LAST
1750 UPDATE WF_LOCAL_USER_ROLES wur
1751 SET ROLE_START_DATE = l_roleStartDestTAB(destTabIndex),
1752 ROLE_END_DATE = l_roleEndDestTAB(destTabIndex),
1753 USER_START_DATE = l_userStartDestTAB(destTabIndex),
1754 USER_END_DATE = l_userEndDestTAB(destTabIndex),
1755 START_DATE = l_startDestTAB(destTabIndex),
1756 EXPIRATION_DATE = l_endDestTAB(destTabIndex),
1757 EFFECTIVE_START_DATE = l_effStartDestTAB(destTabIndex),
1758 EFFECTIVE_END_DATE = l_effEndDestTAB(destTabIndex),
1759 ASSIGNMENT_TYPE = l_assignTAB(destTabIndex),
1760 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1761 LAST_UPDATE_LOGIN = FND_GLOBAL.Login_Id,
1762 LAST_UPDATE_DATE = SYSDATE
1763 WHERE rowid = l_rowIDDestTAB(destTabIndex);
1764 else --Do not touch WHO columns. This is default behavior
1765 forall destTabIndex in l_roleDestTab.FIRST..l_roleDestTab.LAST
1766 UPDATE WF_LOCAL_USER_ROLES wur
1767 SET ROLE_START_DATE = l_roleStartDestTAB(destTabIndex),
1768 ROLE_END_DATE = l_roleEndDestTAB(destTabIndex),
1769 USER_START_DATE = l_userStartDestTAB(destTabIndex),
1770 USER_END_DATE = l_userEndDestTAB(destTabIndex),
1771 START_DATE = l_startDestTAB(destTabIndex),
1772 EXPIRATION_DATE = l_endDestTAB(destTabIndex),
1773 EFFECTIVE_START_DATE = l_effStartDestTAB(destTabIndex),
1774 EFFECTIVE_END_DATE = l_effEndDestTAB(destTabIndex),
1775 ASSIGNMENT_TYPE = l_assignTAB(destTabIndex)
1776 WHERE rowid = l_rowIDDestTAB(destTabIndex);
1777 end if;
1778 l_roleStartDestTAB.DELETE;
1779 l_roleEndDestTAB.DELETE;
1780 l_userStartDestTAB.DELETE;
1781 l_userEndDestTAB.DELETE;
1782 l_effStartDestTAB.DELETE;
1783 l_effEndDestTAB.DELETE;
1784 l_assignTAB.DELETE;
1785 l_startDestTAB.DELETE;
1786 l_endDestTAB.DELETE;
1787 l_roleDestTAB.DELETE;
1788 l_userDestTAB.DELETE;
1789 l_userOrigDestTAB.DELETE;
1790 l_userOrigIDDestTAB.DELETE;
1791 l_roleOrigDestTAB.DELETE;
1792 l_roleOrigIDDestTAB.DELETE;
1793 l_rowIDDestTAB.DELETE;
1794
1795 sumTabIndex := 0;
1796 end if; --sumTabIndex >= l_maxRows
1797
1798 --now perform the insert
1799 sumTabIndex := sumTabIndex + 1;
1800 l_RoleDestTAB(sumTabIndex) := l_roleSrcTAB(tabIndex);
1801 l_UserDestTAB(sumTabIndex) := l_userSRcTAB(tabIndex);
1802 l_userOrigDestTAB(sumTabIndex) := l_userOrigSrcTAB(tabIndex);
1803 l_userOrigIDDestTAB(sumTabIndex) := l_userOrigIDSrcTAB(tabIndex);
1804 l_roleOrigDestTAB(sumTabIndex) := l_roleOrigSrcTAB(tabIndex);
1805 l_roleOrigIDDestTAB(sumTabIndex) := l_roleOrigIDSrcTAB(tabIndex);
1806 l_roleStartDestTAB(sumTabIndex) := l_roleStartSrcTAB(tabIndex);
1807 l_roleEndDestTAB(sumTabIndex) := l_roleEndSrcTAB(tabIndex);
1808 l_userStartDestTAB(sumTabIndex) := l_userStartSrcTAB(tabIndex);
1809 l_userEndDestTAB(sumTabIndex) := l_userEndSrcTAB(tabIndex);
1810 l_effStartDestTAB(sumTabIndex) := l_effStartSrcTAB(tabIndex);
1811 l_effEndDestTAB(sumTabIndex) := l_effEndSrcTAB(tabIndex);
1812 l_rowIDDestTAB(sumTabIndex) := l_rowIDSrcTAB(TabIndex);
1813
1814 --Check to see if the assignment is active.
1815 if (l_effEndSrcTAB(tabIndex) > trunc(SYSDATE) and
1816 l_effStartSrcTab(tabIndex) <= trunc(SYSDATE)) then
1817 l_activeAssigned := TRUE;
1818 else
1819 l_activeAssigned := FALSE;
1820 end if;
1821
1822 --Determine the initial assignment_type.
1823 if l_relIDTAB(tabIndex) = -1 then
1824 l_AssignTAB(sumTabIndex):='D';
1825 l_startDestTAB(sumTabIndex) :=l_startSrcTAB(tabIndex);
1826 l_endDestTAB(sumTabIndex) :=l_endSrcTAB(tabIndex);
1827 else
1828 l_AssignTAB(sumTabIndex):='I';
1829 l_startDestTAB(sumTabIndex) :=null;
1830 l_endDestTAB(sumTabIndex) :=null;
1831 end if;
1832 else --Record is already in the summary table so update effective dates
1833 if l_effStartSrcTAB(tabIndex) < l_effStartDestTAB(sumTabIndex) then
1834 l_effStartDestTAB(sumTabIndex) := l_effStartSrcTAB(tabIndex);
1835 end if;
1836
1837 if l_effEndSrcTAB(tabIndex) > l_effEndDestTAB(sumTabIndex) then
1838 l_effEndDestTAB(sumTabIndex) := l_effEndSrcTAB(tabIndex);
1839 end if;
1840
1841 -- if this is a direct assignment then we need to set the start
1842 -- and end dates
1843 if l_relIDTAB(tabIndex) = -1 then
1844 l_startDestTAB(sumTabIndex) :=l_startSrcTAB(tabIndex);
1845 l_endDestTAB(sumTabIndex) :=l_endSrcTAB(tabIndex);
1846 end if;
1847
1848 --if the assignment type in summary table is Direct and
1849 --we encountered an inherited assignment in the Assignment table
1850 --or if the assignment type in summary table is inherited and we
1851 --encountered a direct assignment in the Assignment table
1852 --update the assignment_Type to Both
1853
1854 if (l_effEndSrcTAB(tabIndex) > trunc(SYSDATE) and
1855 l_effStartSrcTAB(tabIndex) <= trunc(SYSDATE)) then
1856 --This is an active assignment so we need to determine if an
1857 --active assignment was already used in calculating assignment_type
1858 if (l_activeAssigned) then
1859 --An active assignment was already used in the calculation so this
1860 --assignment will be used to determine if and existing 'D' or 'I'
1861 --should be changed into a 'B'
1862 if (((l_AssignTAB(sumTabIndex) = 'D') and
1863 (l_relIDTAB(tabIndex) <> -1)) or
1864 ((l_AssignTAB(sumTabIndex) = 'I') and
1865 (l_relIDTAB(tabIndex) = -1))) then
1866
1867 l_AssignTAB(sumTabIndex) := 'B';
1868 end if;
1869 else
1870 --This is the first active assignment, so set the initial value
1871 --Determine the initial assignment_type.
1872 if l_relIDTAB(tabIndex) = -1 then
1873 l_AssignTAB(sumTabIndex):='D';
1874 l_startDestTAB(sumTabIndex) :=l_startSrcTAB(tabIndex);
1875 l_endDestTAB(sumTabIndex) :=l_endSrcTAB(tabIndex);
1876 else
1877 l_AssignTAB(sumTabIndex):='I';
1878 l_startDestTAB(sumTabIndex) :=null;
1879 l_endDestTAB(sumTabIndex) :=null;
1880 end if;
1881
1882 --Now set l_activeAssigned to TRUE.
1883 l_activeAssigned := TRUE;
1884 end if;
1885 else
1886 --This is an expired assignment, so we will set the assignment_type
1887 --only if we have not already initialized/modified with an
1888 --active assignment.
1889 if NOT (l_activeAssigned) then
1890 if l_relIDTAB(tabIndex) = -1 then
1891 l_AssignTAB(sumTabIndex):='D';
1892 l_startDestTAB(sumTabIndex) :=l_startSrcTAB(tabIndex);
1893 l_endDestTAB(sumTabIndex) :=l_endSrcTAB(tabIndex);
1894 else
1895 l_AssignTAB(sumTabIndex):='I';
1896 l_startDestTAB(sumTabIndex) :=null;
1897 l_endDestTAB(sumTabIndex) :=null;
1898 end if;
1899 end if;
1900 end if;
1901 end if;
1902 end loop summarize_assignments;
1903
1904 --Check to see if we have the last batch and do not need to re-fetch
1905 if (l_rowIDTAB.COUNT < l_maxRows) then
1906 commit;
1907 exit outer_loop;
1908 else
1909 -- reset the ROWID Table before the next set of fetch
1910 l_rowIDTAB.DELETE;
1911 commit;
1912 end if;
1913 end loop outer_loop;
1914
1915 --when we reach here, we need to bulk update the leftover records,
1916 --if any, in the summary table.
1917
1918 if (l_roleDestTAB.COUNT) > 0 then
1919 if (p_UpdateWho is not null and p_UpdateWho) then
1920 forall destTabIndex in l_roleDestTab.FIRST..l_roleDestTab.LAST
1921 UPDATE WF_LOCAL_USER_ROLES wur
1922 SET ROLE_START_DATE = l_roleStartDestTAB(destTabIndex),
1923 ROLE_END_DATE = l_roleEndDestTAB(destTabIndex),
1924 USER_START_DATE = l_userStartDestTAB(destTabIndex),
1925 USER_END_DATE = l_userEndDestTAB(destTabIndex),
1926 EFFECTIVE_START_DATE = l_effStartDestTAB(destTabIndex),
1927 EFFECTIVE_END_DATE = l_effEndDestTAB(destTabIndex),
1928 START_DATE = l_startDestTAB(destTabIndex),
1929 EXPIRATION_DATE = l_endDestTAB(destTabIndex),
1930 ASSIGNMENT_TYPE = l_assignTAB(destTabIndex),
1931 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1932 LAST_UPDATE_LOGIN = FND_GLOBAL.Login_Id,
1933 LAST_UPDATE_DATE = SYSDATE
1934 WHERE rowid = l_rowIDDestTAB(destTabIndex);
1935 else --Do not touch WHO columns. This is default behavior.
1936 forall destTabIndex in l_roleDestTab.FIRST..l_roleDestTab.LAST
1937 UPDATE WF_LOCAL_USER_ROLES wur
1938 SET ROLE_START_DATE = l_roleStartDestTAB(destTabIndex),
1939 ROLE_END_DATE = l_roleEndDestTAB(destTabIndex),
1940 USER_START_DATE = l_userStartDestTAB(destTabIndex),
1941 USER_END_DATE = l_userEndDestTAB(destTabIndex),
1942 EFFECTIVE_START_DATE = l_effStartDestTAB(destTabIndex),
1943 EFFECTIVE_END_DATE = l_effEndDestTAB(destTabIndex),
1944 START_DATE = l_startDestTAB(destTabIndex),
1945 EXPIRATION_DATE = l_endDestTAB(destTabIndex),
1946 ASSIGNMENT_TYPE = l_assignTAB(destTabIndex)
1947 WHERE rowid = l_rowIDDestTAB(destTabIndex);
1948 end if;
1949 end if;
1950
1951 commit; --Commit final work.
1952
1953 --close the cursor now
1954 if (c_userRoleAssignments%ISOPEN) then
1955 close c_userRoleAssignments;
1956 end if;
1957
1958 -- Bug 9184359
1959 FixWURAEffectiveDates(l_maxRows , p_username, p_rolename);
1960
1961 -- Bug 8423138
1962 FixLUREffectiveDates(l_maxRows, p_username, p_rolename);
1963
1964 --release lock
1965 if (dbms_lock.release(l_lockhandle) <> 0) then
1966 wf_core.raise('WF_LOCK_FAIL');
1967 end if;
1968
1969 exception
1970 when others then
1971 result := dbms_lock.release(l_lockhandle);
1972 if (c_userRoleAssignments%ISOPEN) then
1973 close c_userRoleAssignments;
1974 end if;
1975
1976 raise;
1977 end;
1978
1979 /*
1980 ** GetUsernameChangeCounts
1981 ** Described in package specification.
1982 */
1983 function GetUsernameChangeCounts(p_name VARCHAR2) RETURN wfcount_tab pipelined is
1984 --l_wfcount_tab wfcount_tab;
1985 l_wfcount_tab wfcount_type;
1986 l_roleInfoTAB WF_DIRECTORY.wf_local_roles_tbl_type;
1987 l_partitionID WF_DIRECTORY_PARTITIONS.PARTITION_ID%TYPE;
1988 l_partitionName WF_DIRECTORY_PARTITIONS.ORIG_SYSTEM%TYPE;
1989 l_pvalue varchar2(10) := FND_PROFILE.value('WF_MAINT_COMPLETED_ITEMS');
1990 begin
1991 -- First etermine if the username actually exists
1992 WF_DIRECTORY.GetRoleInfo2(p_name, l_roleInfoTAB);
1993 if l_roleInfoTAB(1).DISPLAY_NAME is null then
1994 l_wfcount_tab := null;
1995 else
1996 l_wfcount_tab.USER_NAME:=p_name;
1997
1998 -- Determine the entries in FND_GRANTS associated to this role.
1999 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2000 from FND_GRANTS fg
2001 where fg.GRANTEE_TYPE='USER'
2002 and fg.GRANTEE_ORIG_SYSTEM in ('FND_USR', 'PER')
2003 and fg.PROGRAM_NAME = 'WORKFLOW_UI'
2004 and fg.PARAMETER1=p_name;
2005 l_wfcount_tab.TABLE_NAME :='FND_GRANTS';
2006 pipe ROW (l_wfcount_tab);
2007
2008 -- Determine the workflow processes owned by this role. Can be any role.
2009 if (l_pvalue is null or l_pvalue = 'Y') then
2010 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2011 from WF_ITEMS
2012 where OWNER_ROLE = p_name;
2013 else
2014 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2015 from WF_ITEMS
2016 where OWNER_ROLE = p_name
2017 and END_DATE is null;
2018 end if;
2019 l_wfcount_tab.TABLE_NAME :='WF_ITEMS';
2020 pipe ROW (l_wfcount_tab);
2021
2022 -- Determine rows to change in WF_ITEM_ACTIVITY_STATUSES
2023 if (l_pvalue is null or l_pvalue = 'Y') then
2024 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2025 from WF_ITEM_ACTIVITY_STATUSES WIAS
2026 where ASSIGNED_USER = p_name;
2027 else
2028 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2029 from WF_ITEM_ACTIVITY_STATUSES WIAS
2030 where exists (select '1'
2031 from WF_ITEMS WI
2032 where WI.ITEM_TYPE=WIAS.ITEM_TYPE and
2033 WI.ITEM_KEY=WIAS.ITEM_KEY and
2034 WI.END_DATE is null) and
2035 ASSIGNED_USER = p_name;
2036 end if;
2037 l_wfcount_tab.TABLE_NAME :='WF_ITEM_ACTIVITY_STATUSES';
2038 pipe ROW (l_wfcount_tab);
2039
2040 -- Determine rows to change in WF_ITEM_ACTIVITY_STATUSES_H
2041 if (l_pvalue is null or l_pvalue = 'Y') then
2042 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2043 from WF_ITEM_ACTIVITY_STATUSES_H WIASH
2044 where ASSIGNED_USER = p_name;
2045 else
2046 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2047 from WF_ITEM_ACTIVITY_STATUSES_H WIASH
2048 where exists (select '1'
2049 from WF_ITEMS WI
2050 where WI.ITEM_TYPE=WIASH.ITEM_TYPE and
2051 WI.ITEM_KEY=WIASH.ITEM_KEY and
2052 WI.END_DATE is null) and
2053 ASSIGNED_USER = p_name;
2054 end if;
2055 l_wfcount_tab.TABLE_NAME :='WF_ITEM_ACTIVITY_STATUSES_H';
2056 pipe ROW (l_wfcount_tab);
2057
2058 -- Determine rows to change in WF_NOTIFICATIONS
2059 if (l_pvalue is null or l_pvalue = 'Y') then
2060 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2061 from WF_NOTIFICATIONS WN
2062 where RECIPIENT_ROLE = p_name
2063 or ORIGINAL_RECIPIENT = p_name
2064 or more_info_role = p_name
2065 or from_role = p_name
2066 or responder = p_name;
2067 else
2068 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2069 from WF_NOTIFICATIONS WN
2070 where (exists (select '1'
2071 from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_ITEMS WI
2072 where WIAS.NOTIFICATION_ID = WN.NOTIFICATION_ID
2073 and WIAS.ITEM_TYPE = WI.ITEM_TYPE
2074 and WIAS.ITEM_KEY = WI.ITEM_KEY
2075 and WI.END_DATE IS NULL)
2076 or exists (select '1'
2077 from WF_ITEM_ACTIVITY_STATUSES_H WIASH, WF_ITEMS WI
2078 where WIASH.NOTIFICATION_ID = WN.NOTIFICATION_ID
2079 and WIASH.ITEM_TYPE = WI.ITEM_TYPE
2080 and WIASH.ITEM_KEY = WI.ITEM_KEY
2081 and WI.END_DATE IS NULL))
2082 and (RECIPIENT_ROLE = p_name
2083 or ORIGINAL_RECIPIENT = p_name
2084 or more_info_role = p_name
2085 or from_role = p_name
2086 or responder = p_name);
2087 end if;
2088 l_wfcount_tab.TABLE_NAME := 'WF_NOTIFICATIONS';
2089 pipe ROW (l_wfcount_tab);
2090
2091 -- Determine rows to change in WF_PROCESS_ACTIVITIES
2092 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2093 from WF_PROCESS_ACTIVITIES
2094 where PERFORM_ROLE = p_name;
2095 l_wfcount_tab.TABLE_NAME := 'WF_PROCESS_ACTIVITIES';
2096 pipe ROW (l_wfcount_tab);
2097
2098 -- Determine rows to change in WF_ROUTING_RULES. Applies to users only
2099 if (l_pvalue is null or l_pvalue = 'Y') then
2100 if l_roleInfoTAB(1).ORIG_SYSTEM in ('PER','FND_USR') then
2101 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2102 from WF_ROUTING_RULES
2103 where ROLE = p_name or ACTION_ARGUMENT = p_name;
2104 else
2105 l_wfcount_tab.REC_CNT := 0;
2106 end if;
2107 else
2108 if l_roleInfoTAB(1).ORIG_SYSTEM in ('PER','FND_USR') then
2109 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2110 from WF_ROUTING_RULES
2111 where (ROLE = p_name or ACTION_ARGUMENT = p_name)
2112 and BEGIN_DATE<=SYSDATE
2113 and (END_DATE is null or END_DATE>=SYSDATE);
2114 else
2115 l_wfcount_tab.REC_CNT := 0;
2116 end if;
2117 end if;
2118 l_wfcount_tab.TABLE_NAME := 'WF_ROUTING_RULES';
2119 pipe ROW (l_wfcount_tab);
2120
2121 -- Determine rows to change in WF_ROUTING_RULE_ATTRIBUTES. Applies to users only
2122 if (l_pvalue is null or l_pvalue = 'Y') then
2123 if l_roleInfoTAB(1).ORIG_SYSTEM in ('PER','FND_USR') then
2124 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2125 from WF_ROUTING_RULE_ATTRIBUTES ra, WF_ROUTING_RULES wrr
2126 where wrr.RULE_ID=ra.RULE_ID
2127 and ra.TEXT_VALUE = p_name
2128 and exists
2129 (select '1'
2130 from WF_MESSAGE_ATTRIBUTES ma
2131 where ma.NAME=ra.NAME
2132 and ma.TYPE='ROLE');
2133 else
2134 l_wfcount_tab.REC_CNT := 0;
2135 end if;
2136 else
2137 if l_roleInfoTAB(1).ORIG_SYSTEM in ('PER','FND_USR') then
2138 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2139 from WF_ROUTING_RULE_ATTRIBUTES ra, WF_ROUTING_RULES wrr
2140 where wrr.BEGIN_DATE<=SYSDATE
2141 and (wrr.END_DATE is null or wrr.END_DATE>=SYSDATE)
2142 and wrr.RULE_ID=ra.RULE_ID
2143 and ra.TEXT_VALUE = p_name
2144 and exists
2145 (select '1'
2146 from WF_MESSAGE_ATTRIBUTES ma
2147 where ma.NAME=ra.NAME
2148 and ma.TYPE='ROLE');
2149 else
2150 l_wfcount_tab.REC_CNT := 0;
2151 end if;
2152
2153 end if;
2154 l_wfcount_tab.TABLE_NAME := 'WF_ROUTING_RULE_ATTRIBUTES';
2155 pipe ROW (l_wfcount_tab);
2156
2157 -- Determine rows to change in WF_COMMENTS Applies to users only
2158 if (l_pvalue is null or l_pvalue = 'Y') then
2159 if l_roleInfoTAB(1).ORIG_SYSTEM in ('PER','FND_USR') then
2160 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2161 from WF_COMMENTS WC
2162 where (WC.FROM_ROLE = p_name
2163 or WC.TO_ROLE = p_name
2164 or WC.PROXY_ROLE = p_name)
2165 and (exists (select '1'
2166 from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_ITEMS WI
2167 where WIAS.NOTIFICATION_ID = WC.NOTIFICATION_ID
2168 and WIAS.ITEM_TYPE = WI.ITEM_TYPE
2169 and WIAS.ITEM_KEY = WI.ITEM_KEY
2170 and WI.END_DATE IS NULL)
2171 or exists (select '1'
2172 from WF_ITEM_ACTIVITY_STATUSES_H WIASH, WF_ITEMS WI
2173 where WIASH.NOTIFICATION_ID = WC.NOTIFICATION_ID
2174 and WIASH.ITEM_TYPE = WI.ITEM_TYPE
2175 and WIASH.ITEM_KEY = WI.ITEM_KEY
2176 and WI.END_DATE IS NULL));
2177 else
2178 l_wfcount_tab.REC_CNT := 0;
2179 end if;
2180 else
2181 if l_roleInfoTAB(1).ORIG_SYSTEM in ('PER','FND_USR') then
2182 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2183 from WF_COMMENTS WC
2184 where WC.FROM_ROLE = p_name
2185 or WC.TO_ROLE = p_name
2186 or WC.PROXY_ROLE = p_name;
2187 else
2188 l_wfcount_tab.REC_CNT := 0;
2189 end if;
2190 end if;
2191 l_wfcount_tab.TABLE_NAME := 'WF_COMMENTS';
2192 pipe ROW (l_wfcount_tab);
2193
2194 -- Determine rows to change in WF_ITEM_ATTRIBUTE_VALUES. Can be any role.
2195 if (l_pvalue is null or l_pvalue = 'Y') then
2196 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2197 from WF_ITEM_ATTRIBUTE_VALUES WIAV, WF_ITEM_ATTRIBUTES WIA
2198 where WIA.type = 'ROLE'
2199 and WIA.ITEM_TYPE = WIAV.ITEM_TYPE
2200 and WIA.NAME = WIAV.NAME
2201 and WIAV.TEXT_VALUE = p_name;
2202 else
2203 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2204 from WF_ITEM_ATTRIBUTE_VALUES WIAV, WF_ITEM_ATTRIBUTES WIA
2205 where WIA.type = 'ROLE'
2206 and WIA.ITEM_TYPE = WIAV.ITEM_TYPE
2207 and WIA.NAME = WIAV.NAME
2208 and WIAV.TEXT_VALUE = p_name
2209 and exists (select '1'
2210 from WF_ITEMS WI
2211 where WI.ITEM_TYPE=WIAV.ITEM_TYPE
2212 and WI.ITEM_KEY=WIAV.ITEM_KEY
2213 and WI.END_DATE is null);
2214 end if;
2215 l_wfcount_tab.TABLE_NAME := 'WF_ITEM_ATTRIBUTE_VALUES';
2216 pipe ROW (l_wfcount_tab);
2217
2218 -- Now check what happens with the WFDS tables:
2219 WF_DIRECTORY.assignPartition(l_roleInfoTAB(1).ORIG_SYSTEM, l_partitionID, l_partitionName);
2220 -- Determine rows to change in WF_LOCAL_ROLES. Can be any role
2221 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2222 from WF_LOCAL_ROLES
2223 WHERE NAME = p_name
2224 AND PARTITION_ID = l_partitionID
2225 AND ORIG_SYSTEM = l_roleInfoTAB(1).ORIG_SYSTEM
2226 AND ORIG_SYSTEM_ID = l_roleInfoTAB(1).ORIG_SYSTEM_ID;
2227 l_wfcount_tab.TABLE_NAME := 'WF_LOCAL_ROLES';
2228 pipe ROW (l_wfcount_tab);
2229
2230 -- Determine rows to change in WF_LOCAL_USER_ROLES. Can be any role
2231 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2232 from WF_LOCAL_USER_ROLES
2233 WHERE (ROLE_NAME = p_name
2234 AND ROLE_ORIG_SYSTEM = l_roleInfoTAB(1).ORIG_SYSTEM
2235 AND ROLE_ORIG_SYSTEM_ID = l_roleInfoTAB(1).ORIG_SYSTEM_ID)
2236 OR
2237 (USER_NAME = p_name
2238 AND USER_ORIG_SYSTEM = l_roleInfoTAB(1).ORIG_SYSTEM
2239 AND USER_ORIG_SYSTEM_ID = l_roleInfoTAB(1).ORIG_SYSTEM_ID);
2240 l_wfcount_tab.TABLE_NAME := 'WF_LOCAL_USER_ROLES';
2241 pipe ROW (l_wfcount_tab);
2242
2243 -- Determine rows to change in WF_USER_ROLE_ASSIGNMENTS. Can be any role
2244 select nvl(count(1), 0) into l_wfcount_tab.REC_CNT
2245 from WF_USER_ROLE_ASSIGNMENTS
2246 WHERE (ROLE_NAME = p_name
2247 AND ROLE_ORIG_SYSTEM = l_roleInfoTAB(1).ORIG_SYSTEM
2248 AND ROLE_ORIG_SYSTEM_ID = l_roleInfoTAB(1).ORIG_SYSTEM_ID)
2249 OR
2250 (USER_NAME = p_name
2251 AND USER_ORIG_SYSTEM = l_roleInfoTAB(1).ORIG_SYSTEM
2252 AND USER_ORIG_SYSTEM_ID = l_roleInfoTAB(1).ORIG_SYSTEM_ID);
2253 l_wfcount_tab.TABLE_NAME := 'WF_USER_ROLE_ASSIGNMENTS';
2254 pipe ROW (l_wfcount_tab);
2255
2256 -- Determine rows to change in WF_ROLE_HIERARCHIES. Applies to roles only
2257 if l_roleInfoTAB(1).ORIG_SYSTEM NOT in ('PER','FND_USR') then
2258 select sum (a.detail) into l_wfcount_tab.REC_CNT
2259 from (select nvl(count(1), 0) as detail
2260 from WF_ROLE_HIERARCHIES
2261 WHERE SUPER_NAME = p_name
2262 AND SUPERIOR_PARTITION_ID = l_partitionID
2263 union
2264 select nvl(count(1), 0) as detail
2265 from WF_ROLE_HIERARCHIES
2266 WHERE SUB_NAME = p_name
2267 AND PARTITION_ID = l_partitionID) a;
2268 else
2269 l_wfcount_tab.REC_CNT := 0;
2270 end if;
2271 l_wfcount_tab.TABLE_NAME := 'WF_ROLE_HIERARCHIES';
2272 pipe ROW (l_wfcount_tab);
2273 end if;
2274 return;
2275 exception
2276 when others then
2277 return;
2278 end GetUsernameChangeCounts;
2279
2280 end WF_MAINTENANCE;
[Home] [Help]