1 package body WF_PURGE as
2 /* $Header: wfprgb.pls 120.13.12010000.2 2008/09/08 11:39:36 ajverma ship $ */
3
4 --Private Variables
5 l_docommit boolean;
6 l_runtimeonly boolean;
7
8 -- procedure Move_To_History
9 -- Move wf_item_activity_status rows for particular itemtype/key from
10 -- main table to history table.
11 -- IN:
12 -- itemtype - Item type to move, or null for all itemtypes
13 -- itemkey - Item key to move, or null for all itemkeys
14 --
15 procedure Move_To_History(
16 itemtype in varchar2,
17 itemkey in varchar2)
18 is
19 begin
20 Null;
21 end Move_To_History;
22
23 --
24 -- procedure Item_Activity_Statuses
25 -- Delete from wf_item_activity_statuses and wf_item_activity_statuses_h
26 -- where end_date before argument.
27 -- IN:
28 -- itemtype - Item type to delete, or null for all itemtypes
29 -- itemkey - Item key to delete, or null for all itemkeys
30 -- enddate - Date to obsolete to
31 --
32 procedure Item_Activity_Statuses(
33 itemtype in varchar2,
34 itemkey in varchar2,
35 enddate in date)
36 is
37 begin
38 Null;
39 end Item_Activity_Statuses;
40
41 --
42 -- procedure Items
43 -- Delete items with end_time before argument.
44 -- IN:
45 -- itemtype - Item type to delete, or null for all itemtypes
46 -- itemkey - Item key to delete, or null for all itemkeys
47 -- enddate - Date to obsolete to
48 -- docommit- Do not commit if set to false
49 --
50 procedure Items(
51 itemtype in varchar2,
52 itemkey in varchar2,
53 enddate in date,
54 docommit in boolean,
55 force in boolean,
56 purgesigs in pls_integer)
57 is
58 dummy varchar2(240);
59 keep number; -- Bug 1895332
60 TYPE nidListTAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
61 l_nidListTAB nidListTAB;
62 l_tempListTAB nidListTAB;
63 l_keycount number;
64 l_signature boolean := TRUE;
65 xenddate date;
66 l_ecx_sql varchar2(2000);
67
68 -- CTILLEY bug 2755885 include rowid in where clause to resolve
69 -- infinite loop
70
71 -- CTILLEY bug 3228475 - remove like item type and item key to
72 -- performance violation. Added item5purge to query records
73 -- where itemtype is passed but no itemkey. No longer supporting
74 -- partial values for either item type or item key.
75
76 cursor item2purge is
77 select WI.END_DATE, WI.ITEM_TYPE, WI.ITEM_KEY
78 from WF_ITEMS WI
79 where WI.ITEM_TYPE = itemtype
80 and WI.END_DATE <= enddate
81 and WI.END_DATE > xenddate
82 and exists
83 (select null
84 from WF_ITEM_TYPES WIT
85 where WI.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
86 and WI.ITEM_TYPE = WIT.NAME
87 and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
88 and not exists
89 (select null
90 from WF_ITEMS WI2
91 WHERE WI2.END_DATE IS NULL
92 START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
93 AND WI2.ITEM_KEY = WI.ITEM_KEY
94 CONNECT BY PRIOR WI2.ITEM_TYPE = WI2.PARENT_ITEM_TYPE
95 AND PRIOR WI2.ITEM_KEY = WI2.PARENT_ITEM_KEY
96 UNION ALL
97 select null
98 from WF_ITEMS WI2
99 WHERE WI2.END_DATE IS NULL
100 START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
101 AND WI2.ITEM_KEY = WI.ITEM_KEY
102 CONNECT BY PRIOR WI2.PARENT_ITEM_TYPE = WI2.ITEM_TYPE
103 AND PRIOR WI2.PARENT_ITEM_KEY = WI2.ITEM_KEY)
104 order by WI.END_DATE;
105
106 -- JWSMITH bug 2070056 - add new cursor for performance
107 cursor item3purge is
108 select WI.END_DATE, WI.ITEM_TYPE, WI.ITEM_KEY
109 from WF_ITEMS WI
110 where WI.ITEM_TYPE = itemtype
111 and WI.ITEM_KEY = itemkey
112 and WI.end_date <= enddate
113 and exists
114 (select null
115 from WF_ITEM_TYPES WIT
116 where WI.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
117 and WI.ITEM_TYPE = WIT.NAME
118 and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
119 and not exists
120 (select null
121 from WF_ITEMS WI2
122 WHERE WI2.END_DATE IS NULL
123 START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
124 AND WI2.ITEM_KEY = WI.ITEM_KEY
125 CONNECT BY PRIOR WI2.ITEM_TYPE = WI2.PARENT_ITEM_TYPE
126 AND PRIOR WI2.ITEM_KEY = WI2.PARENT_ITEM_KEY
127 UNION ALL
128 select null
129 from WF_ITEMS WI2
130 WHERE WI2.END_DATE IS NULL
131 START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
132 AND WI2.ITEM_KEY = WI.ITEM_KEY
133 CONNECT BY PRIOR WI2.PARENT_ITEM_TYPE = WI2.ITEM_TYPE
134 AND PRIOR WI2.PARENT_ITEM_KEY = WI2.ITEM_KEY);
135
136 -- CTILLEY bug 2642057 - added new cursor for use when FORCE
137 -- arg is TRUE.
138 cursor item4purge is
139 select WI.END_DATE, WI.ITEM_TYPE, WI.ITEM_KEY
140 from WF_ITEMS WI
141 where WI.ITEM_KEY = itemkey
142 and WI.ITEM_TYPE = itemtype
143 and WI.end_date <= enddate;
144
145 -- CTILLEY bug 3228475 - Added new cursor to resolve performance violation
146 -- of using like condition. We will no longer support passing partial values
147 -- for item type or item key.
148
149 cursor item5purge is
150 select /*+ first_rows index(WI,WF_ITEMS_N3) */
151 WI.END_DATE, WI.ITEM_TYPE, WI.ITEM_KEY
152 from WF_ITEMS WI
153 where WI.END_DATE <= enddate
154 and WI.END_DATE > xenddate
155 and exists
156 (select null
157 from WF_ITEM_TYPES WIT
158 where WI.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
159 and WI.ITEM_TYPE = WIT.NAME
160 and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
161 and not exists
162 (select null
163 from WF_ITEMS WI2
164 WHERE WI2.END_DATE IS NULL
165 START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
166 AND WI2.ITEM_KEY = WI.ITEM_KEY
167 CONNECT BY PRIOR WI2.ITEM_TYPE = WI2.PARENT_ITEM_TYPE
168 AND PRIOR WI2.ITEM_KEY = WI2.PARENT_ITEM_KEY
169 UNION ALL
170 select null
171 from WF_ITEMS WI2
172 WHERE WI2.END_DATE IS NULL
173 START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
174 AND WI2.ITEM_KEY = WI.ITEM_KEY
175 CONNECT BY PRIOR WI2.PARENT_ITEM_TYPE = WI2.ITEM_TYPE
176 AND PRIOR WI2.PARENT_ITEM_KEY = WI2.ITEM_KEY);
177
178 no_lock exception;
179 pragma EXCEPTION_INIT(no_lock, -54);
180
181 -- ora-2292 = integrity constraint (%s.%s) violated - child record found
182 in_other_status_table exception;
183 pragma EXCEPTION_INIT(in_other_status_table, -02292);
184
185 -- CTILLEY 2755885
186 -- ora-1436 CONNECT BY loop in user data - parent_item_type = item_type
187 connect_by_loop exception;
188 pragma EXCEPTION_INIT(connect_by_loop,-1436);
189
190 -- <rwunderl:3751558>
191 invalid_component exception;
192 pragma EXCEPTION_INIT(invalid_component, -302);
193
194 invalid_identifier exception;
195 pragma EXCEPTION_INIT(invalid_identifier, -201);
196 -- </rwunderl:3751558>
197 begin
198
199 xenddate := to_date(1, 'J');
200 -- Disallow future date in enddate
201 -- Avoid something being purged before its defined persistence period.
202 if (enddate > sysdate) then
203 Wf_Core.Token('SYSDATE', to_char(sysdate));
204 Wf_Core.Raise('WF_FUTURE_END_DATE');
205 end if;
206
207 if ((itemkey is not null and (instr(itemkey,'%')>0 or itemtype is null))
208 or (instr(itemtype,'%')>0)) then
209 wf_core.raise('WFSQL_ARGS');
210 end if;
211
212 -- Build ECX Purge sql
213 --<rwunderl:3751558>
214 l_ecx_sql := 'begin ECX_PURGE.Purge_Items(:1, :2, :3, ';
215 if (l_docommit) then
216 l_ecx_sql := l_ecx_sql||'TRUE';
217 else
218 l_ecx_sql := l_ecx_sql||'FALSE';
219 end if;
220
221 if (nvl(l_runtimeonly, TRUE)) then
222 l_ecx_sql := l_ecx_sql||', TRUE';
223 else
224 l_ecx_sql := l_ecx_sql||', FALSE';
225 end if;
226
227 l_ecx_sql := l_ecx_sql||'); end;';
228 --</rwunderl:3751558>
229
230 -- for all items that are valid to purge
231 -- outer loop for purging items.
232
233 <<outer_purge>>
234 loop
235
236 if (force) then -- CTILLEY bug 2642057
237 open item4purge;
238 -- JWSMITH bug 2070056
239 -- CTILLEY bug 3228475 - no longer supporting partial values for itemtype
240 -- and itemkey.
241 elsif (itemtype is not null and itemkey is null) then
242 open item2purge;
243 elsif (itemtype is not null) then
244 open item3purge;
245 else
246 open item5purge;
247 end if;
248
249 <<item_purge_loop>>
250 loop
251
252 -- CTILLEY bug 2642057
253 if (force) then
254 -- Bug 4163328
255 -- Use bulk operations to improve performance
256 FETCH item4purge BULK COLLECT INTO l_enddateTAB,
257 l_itemtypeTAB,
258 l_itemkeyTAB LIMIT wf_purge.commit_frequency;
259 exit outer_purge when l_itemkeyTAB.COUNT = 0;
260
261 -- JWSMITH bug 2070056
262 elsif (itemtype is not null and itemkey is null) then
263 FETCH item2purge BULK COLLECT INTO l_enddateTAB,
264 l_itemtypeTAB,
265 l_itemkeyTAB LIMIT wf_purge.commit_frequency;
266 exit outer_purge when l_itemkeyTAB.COUNT = 0;
267
268 -- CTILLEY bug 3228475
269 elsif (itemtype is not null) then
270 FETCH item3purge BULK COLLECT INTO l_enddateTAB,
271 l_itemtypeTAB,
272 l_itemkeyTAB LIMIT wf_purge.commit_frequency;
273 exit outer_purge when l_itemkeyTAB.COUNT = 0;
274
275 else
276 FETCH item5purge BULK COLLECT INTO l_enddateTAB,
277 l_itemtypeTAB,
278 l_itemkeyTAB LIMIT wf_purge.commit_frequency;
279 exit outer_purge when l_itemkeyTAB.COUNT = 0;
280 end if;
281
282 l_keycount := l_itemkeyTAB.COUNT;
283
284 -- set row lock on item
285 <<outer_lock_loop>>
286 FOR j IN l_itemkeyTAB.FIRST..l_itemkeyTAB.LAST loop
287
288 if (j > l_itemkeyTAB.LAST) then
289 exit;
290 end if;
291
292 -- If we cannot lock current item, try to lock the last item on the index table.
293 -- If we cannot acquire lock on the last item, delete last item and get the
294 -- next last item on the index table until we can acquire a lock.
295 if ( not (wf_item.acquire_lock(l_itemtypeTAB(j), l_itemkeyTAB(j), FALSE))) then
296 xenddate := l_enddateTAB(j);
297 <<lock_loop>>
298 loop
299 if (l_itemkeyTAB.COUNT < j ) then
300 exit outer_lock_loop;
301
302 elsif (wf_item.acquire_lock(l_itemtypeTAB(l_itemtypeTAB.LAST),
303 l_itemkeyTAB(l_itemkeyTAB.LAST), FALSE)) then
304
305 -- Once we acquired a lock on a last item, copy the last time to
306 -- the current index so that we have a dense index table.
307 l_itemkeyTAB(j) := l_itemkeyTAB(l_itemkeyTAB.LAST);
308 l_itemkeyTAB.DELETE(l_itemkeyTAB.LAST);
309 exit lock_loop;
310 else
311 xenddate := l_enddateTAB(l_itemkeyTAB.LAST);
312 l_itemkeyTAB.DELETE(l_itemkeyTAB.LAST);
313 end if;
314 end loop lock_loop;
315 end if;
316 end loop outer_lock_loop;
317
318 if (l_itemkeyTAB.COUNT > 0) then
319
320 FOR j IN l_itemkeyTAB.FIRST..l_itemkeyTAB.LAST loop
321 begin
322 select WN.NOTIFICATION_ID
323 BULK COLLECT into l_tempListTAB
324 from WF_ITEM_ACTIVITY_STATUSES WIAS,
325 WF_NOTIFICATIONS WN
326 where WIAS.ITEM_TYPE = l_itemtypeTAB(j)
327 and WIAS.ITEM_KEY = l_itemkeyTAB(j)
328 and WIAS.NOTIFICATION_ID = WN.GROUP_ID
329 and ((purgesigs = 1)
330 or not exists
331 (select null
332 from WF_DIG_SIGS WDS
333 where SIG_OBJ_TYPE = 'WF_NTF'
334 and SIG_OBJ_ID = WN.NOTIFICATION_ID))
335 union all
336 select WN.NOTIFICATION_ID
337 from WF_ITEM_ACTIVITY_STATUSES_H WIASH,
338 WF_NOTIFICATIONS WN
339 where WIASH.ITEM_TYPE = l_itemtypeTAB(j)
340 and WIASH.ITEM_KEY = l_itemkeyTAB(j)
341 and WIASH.NOTIFICATION_ID = WN.GROUP_ID
342 and ((purgesigs = 1)
343 or not exists
344 (select null
345 from WF_DIG_SIGS WDS
346 where SIG_OBJ_TYPE = 'WF_NTF'
347 and SIG_OBJ_ID = WN.NOTIFICATION_ID));
348
349 if (l_tempListTAB.COUNT > 0) then
350 FOR i IN l_tempListTAB.FIRST..l_tempListTAB.LAST loop
351 l_nidListTAB(l_nidListTAB.COUNT + 1) := l_tempListTAB(i);
352 end loop;
353 end if;
354 end;
355 end loop;
356
357 if (l_nidListTAB.COUNT > 0) then
358 begin
359 FORALL i IN l_nidListTAB.FIRST..l_nidListTAB.LAST
360 -- now delete each notification
361 delete from WF_NOTIFICATION_ATTRIBUTES WNA
362 where WNA.NOTIFICATION_ID = l_nidListTAB(i);
363
364 --<rwunderl:3751558>
365 -- Delete the signatures related to this notification
366 -- Bug 4163328
367 -- Check if Wf_Digital_Security_Private.Purge_Signature_By_Obj_I is
368 -- accepting varray, if not don't run it again.
369 if purgesigs=1 then
370 FOR i in l_nidListTAB.FIRST..l_nidListTAB.LAST loop
371 if (l_signature) then
372 begin
373 execute IMMEDIATE 'begin Wf_Digital_Security_Private.Purge_Signature_By_Obj_Id(''WF_NTF'', :1); end;'
374 using to_char(l_nidListTAB(i));
375
376 exception
377 when invalid_identifier then
378 l_signature := FALSE;
379
380 when invalid_component then
381 l_signature := FALSE;
382 end;
383 --</rwunderl:3751558>
384 end if;
385 end loop;
386 end if;
387
388 FORALL i in l_nidListTAB.FIRST..l_nidListTAB.LAST
389 -- Delete notification comments
390 DELETE FROM wf_comments wc
391 WHERE wc.notification_id = l_nidListTAB(i);
392
393 begin
394 FORALL i in l_nidListTAB.FIRST..l_nidListTAB.LAST
395 delete from WF_NOTIFICATIONS WN
396 where WN.NOTIFICATION_ID = l_nidListTAB(i);
397 exception when in_other_status_table then
398 -- happens when nid is also in history table
399 -- we will delete this later in the loop
400 null;
401 end;
402
403 -- clear l_nidListTAB index table for next bulk processing
404 FOR i IN l_nidListTAB.FIRST..l_nidListTAB.LAST loop
405 l_nidListTAB.DELETE(i);
406 end loop;
407 exception
408 when others then
409 rollback;
410 end;
411 end if; --if (l_nidListTAB.COUNT > 0)
412
413 begin
414 --delete all status history.
415 FORALL j IN l_itemkeyTAB.FIRST..l_itemkeyTAB.LAST
416 delete from WF_ITEM_ACTIVITY_STATUSES_H
417 where ITEM_TYPE = l_itemtypeTAB(j)
418 and ITEM_KEY = l_itemkeyTAB(j);
419
420 --delete all statuses.
421 FORALL j IN l_itemkeyTAB.FIRST..l_itemkeyTAB.LAST
422 delete from WF_ITEM_ACTIVITY_STATUSES
423 where ITEM_TYPE = l_itemtypeTAB(j)
424 and ITEM_KEY = l_itemkeyTAB(j);
425
426 --delete item attributes
427 FORALL j IN l_itemkeyTAB.FIRST..l_itemkeyTAB.LAST
428 delete from WF_ITEM_ATTRIBUTE_VALUES
429 where ITEM_TYPE = l_itemtypeTAB(j)
430 and ITEM_KEY = l_itemkeyTAB(j);
431
432 -- 3966635 Workflwo Provisioning Project
433 -- Following added so as not to loose the changes required.
434 -- --delete Provision Requests, if any
435 -- delete from WF_PROV_REQUESTS
436 -- where CONTEXT like c_item_type||':'||c_item_key||':'||'%'
437 -- and source = 'WORKFLOW';
438 --
439 --finally delete the item itself.
440 FORALL j IN l_itemkeyTAB.FIRST..l_itemkeyTAB.LAST
441 delete from WF_ITEMS
442 where ITEM_TYPE = l_itemtypeTAB(j)
443 and ITEM_KEY = l_itemkeyTAB(j);
444
445 exception
446 when others then
447 rollback;
448 end;
449
450 --<rwunderl:3751558>
451 begin
452 execute immediate l_ecx_sql using itemtype, itemkey, enddate;
453 exception
454 when invalid_identifier then
455 null;
456
457 when invalid_component then
458 null;
459 end;
460 --</rwunderl:3751558>
461 end if;
462
463 if (l_keycount >= wf_purge.commit_frequency) then
464 exit item_purge_loop;
465 elsif (l_keycount < wf_purge.commit_frequency) then
466 exit outer_purge;
467 end if;
468
469 end loop item_purge_loop; -- item2purge or item3purge loop
470
471 if (item2purge%ISOPEN) then
472 close item2purge;
473 end if;
474
475 if (item3purge%ISOPEN) then
476 close item3purge;
477 end if;
478
479 if (item4purge%ISOPEN) then
480 close item4purge;
481 end if;
482
483 -- CTILLEY bug 3228475
484 if (item5purge%ISOPEN) then
485 close item5purge;
486 end if;
487
488 if ( docommit ) then
489 commit;
490 fnd_concurrent.set_preferred_rbs;
491 end if;
492
493 end loop outer_purge;
494
495 if ( docommit ) then
496 commit;
497 fnd_concurrent.set_preferred_rbs;
498 end if;
499
500 if (item2purge%ISOPEN) then
501 close item2purge;
502 end if;
503
504 if (item3purge%ISOPEN) then
505 close item3purge;
506 end if;
507
508 if (item4purge%ISOPEN) then
509 close item4purge;
510 end if;
511
512 -- CTILLEY bug 3228475
513 if (item5purge%ISOPEN) then
514 close item5purge;
515 end if;
516
517 -- Clear engine runtime cache for convenience
518 Wf_Item.ClearCache;
519
520 exception
521 when others then
522 if (item2purge%ISOPEN) then
523 close item2purge;
524 end if;
525
526 if (item3purge%ISOPEN) then
527 close item3purge;
528 end if;
529
530 if (item4purge%ISOPEN) then
531 close item4purge;
532 end if;
533
534 -- CTILLEY bug 3228475
535 if (item5purge%ISOPEN) then
536 close item5purge;
537 end if;
538
539
540 Wf_Core.Context('Wf_Purge', 'Items', itemtype, itemkey, to_char(enddate));
541 raise;
542 end Items;
543
544 --
545 -- procedure Activities
546 -- Delete old activity versions with end_time before argument,
547 -- and that are not referenced by an existing item.
548 -- IN:
549 -- itemtype - Item type to delete, or null for all itemtypes
550 -- name - Activity to delete, or null for all activities
551 -- enddate - Date to obsolete to
552 -- NOTE:
553 -- It is recommended to purge Items before purging Activities to avoid
554 -- obsolete item references preventing obsolete activities from being
555 -- deleted.
556 --
557 procedure Activities(
558 itemtype in varchar2,
559 name in varchar2,
560 enddate in date)
561 is
562 type typetab is table of varchar2(8) index by binary_integer;
563 type nametab is table of varchar2(30) index by binary_integer;
564
565 -- Select all activities of this itemtype
566
567
568 cursor actcurs1 is
569 select distinct WA.ITEM_TYPE, WA.NAME
570 from WF_ACTIVITIES WA
571 where WA.ITEM_TYPE = itemtype;
572
573 cursor actcurs2 is
574 select distinct WA.ITEM_TYPE, WA.NAME
575 from WF_ACTIVITIES WA
576 where WA.ITEM_TYPE = itemtype
577 and WA.NAME = name;
578
579 cursor actcurs3 is
580 select distinct WA.ITEM_TYPE, WA.NAME
581 from WF_ACTIVITIES WA;
582
583
584 /* JWSMITH BUG 2208127 - took out decode statment as this was */
585 /* preventing stats from being used and causing performance */
586 /* issues. Instead have two cursors and if logic to decide */
587 /* between them. See bug for more details. */
588 cursor parentcurs1(acttype in varchar2, actname in varchar2) is
589 select WPA.PROCESS_ITEM_TYPE, WPA.PROCESS_NAME
590 from WF_PROCESS_ACTIVITIES WPA
591 where WPA.ACTIVITY_ITEM_TYPE = acttype
592 and WPA.ACTIVITY_NAME = actname
593 union
594 select WA.ITEM_TYPE PROCESS_ITEM_TYPE, WA.NAME PROCESS_NAME
595 from WF_ACTIVITIES WA
596 where WA.ERROR_ITEM_TYPE = acttype
597 and WA.ERROR_PROCESS IS NOT NULL;
598
599 -- Select processes using an activity in any version,
600 -- or referencing this activity as an error process
601 cursor parentcurs2(acttype in varchar2, actname in varchar2) is
602 select WPA.PROCESS_ITEM_TYPE, WPA.PROCESS_NAME
603 from WF_PROCESS_ACTIVITIES WPA
604 where WPA.ACTIVITY_ITEM_TYPE = acttype
605 and WPA.ACTIVITY_NAME = actname
606 union
607 select WA.ITEM_TYPE PROCESS_ITEM_TYPE, WA.NAME PROCESS_NAME
608 from WF_ACTIVITIES WA
609 where WA.ERROR_ITEM_TYPE = acttype
610 and WA.ERROR_PROCESS = actname;
611
612 parent parentcurs1%rowtype;
613 parent1 parentcurs2%rowtype;
614
615 -- Select all versions of an activity before given enddate
616 cursor vercurs(acttype in varchar2, actname in varchar2) is
617 select WA.BEGIN_DATE, WA.END_DATE, WA.VERSION
618 from WF_ACTIVITIES WA
619 where WA.ITEM_TYPE = acttype
620 and WA.NAME = actname
621 and exists
622 (select null
623 from WF_ITEM_TYPES WIT
624 where WA.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=enddate
625 and WA.ITEM_TYPE = WIT.NAME
626 and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type);
627
628 --Use itemcurs1 if the rootname is 'ROOT'
629 cursor itemcurs1(begdate in date, enddate in date,
630 roottype in varchar2, rootname in varchar2) is
631 select WI.ITEM_TYPE, WI.ITEM_KEY, WI.ROOT_ACTIVITY, WI.BEGIN_DATE
632 from WF_ITEMS WI
633 where WI.BEGIN_DATE between begdate and nvl(enddate, WI.BEGIN_DATE)
634 and WI.ITEM_TYPE = roottype;
635
636 --Use itemcurs2 for other rootnames
637 cursor itemcurs2(begdate in date, enddate in date,
638 roottype in varchar2, rootname in varchar2) is
639 select WI.ITEM_TYPE, WI.ITEM_KEY, WI.ROOT_ACTIVITY, WI.BEGIN_DATE
640 from WF_ITEMS WI
641 where WI.BEGIN_DATE between begdate and nvl(enddate, WI.BEGIN_DATE)
642 and WI.ITEM_TYPE = roottype
643 and WI.ROOT_ACTIVITY = rootname ;
644
645 item itemcurs1%rowtype;
646 item1 itemcurs2%rowtype;
647
648 -- List of processes/activities yet to be checked
649 checkcur pls_integer;
650 checkmax pls_integer;
651 checktype typetab;
652 checkname nametab;
653
654 -- List of processes already checked
655 foundmax pls_integer;
656 foundtype typetab;
657 foundname nametab;
658
659 -- List of possible root processes found
660 rootmax pls_integer;
661 roottype typetab;
662 rootname nametab;
663
664 found boolean;
665
666 rootid pls_integer;
667 refflag boolean;
668
669 numvers pls_integer;
670
671 c_item_type varchar2(8);
672 c_name varchar2(30);
673
674 begin
675
676
677 -- Disallow future date in enddate
678 -- Avoid something being purged before its defined persistence period.
679 if (enddate > sysdate) then
680 Wf_Core.Token('SYSDATE', to_char(sysdate));
681 Wf_Core.Raise('WF_FUTURE_END_DATE');
682 end if;
683
684 -- Bug 3228475 Disallow partial values for itemtype and activity name
685 if ((itemtype is not null and (instr(name,'%')>0 or itemtype is null))
686 or (instr(itemtype,'%')>0)) then
687 Wf_Core.Raise('WFSQL_ARGS');
688 end if;
689 <<outer_actloop>>
690 loop
691 if (itemtype is not null and name is null) then
692 open actcurs1;
693 elsif (itemtype is not null) then
694 open actcurs2;
695 else
696 open actcurs3;
697 end if;
698
699 <<actloop>>
700 loop
701
702 if (itemtype is not null and name is null) then
703 fetch actcurs1 into c_item_type, c_name;
704 if (actcurs1%notfound) then
705 exit outer_actloop;
706 end if;
707 elsif (itemtype is not null) then
708 fetch actcurs2 into c_item_type, c_name;
709 if (actcurs2%notfound) then
710 exit outer_actloop;
711 end if;
712 else
713 fetch actcurs3 into c_item_type, c_name;
714 if (actcurs3%notfound) then
715 exit outer_actloop;
716 end if;
717 end if;
718
719
720 -- Build table of all possible root processes referencing this activity.
721 -- Start with only this activity on the check list,
722 -- and the found and root lists empty.
723 checkcur := 1;
724 checkmax := 1;
725 checktype(1) := c_item_type;
726 checkname(1) := c_name;
727 foundmax := 0;
728 rootmax := 0;
729
730 -- While processes left to check...
731 <<checkloop>>
732 while (checkcur <= checkmax) loop
733 -- JWSMITH BUG 2208127, Determine which cursor to use.
734 if (checkname(checkcur) = 'ROOT') then
735 -- If this node is already a root, add it to the list immediately.
736 rootmax := rootmax + 1;
737 roottype(rootmax) := checktype(checkcur);
738 rootname(rootmax) := checkname(checkcur);
739
740 open parentcurs1(checktype(checkcur),checkname(checkcur));
741 else
742 open parentcurs2(checktype(checkcur),checkname(checkcur));
743 end if;
744
745 -- Get all processes referencing this activity
746 <<parentloop>>
747
748 -- JWSMITH BUG 2208127, Determine which cursor to use.
749 loop
750
751 if (checkname(checkcur) = 'ROOT') then
752 fetch parentcurs1 into parent;
753 EXIT WHEN parentcurs1%NOTFOUND;
754 else
755 fetch parentcurs2 into parent1;
756 EXIT WHEN parentcurs2%NOTFOUND;
757 parent := parent1;
758 end if;
759 if (parent.process_name = 'ROOT') then
760 -- If we have reached a root node, add this to this list
761 -- of root processes to check.
762 rootmax := rootmax + 1;
763 roottype(rootmax) := checktype(checkcur);
764 rootname(rootmax) := checkname(checkcur);
765 else
766 -- Otherwise, check if we have already found this activity
767 -- (Found means it has either already been checked or is
768 -- currently in the check queue.)
769 found := FALSE;
770 <<foundloop>>
771 for i in 1 .. foundmax loop
772 if ((foundtype(i) = parent.process_item_type) and
773 (foundname(i) = parent.process_name)) then
774 found := TRUE;
775 exit foundloop;
776 end if;
777 end loop foundloop;
778 -- This is the first time this process has been found.
779 -- Add it to the found list and the list of further
780 -- processes to check.
781 if (not found) then
782 -- Add to list of processes found
783 foundmax := foundmax + 1;
784 foundtype(foundmax) := parent.process_item_type;
785 foundname(foundmax) := parent.process_name;
786
787 -- Add parent process to list to check
788 checkmax := checkmax + 1;
789 checktype(checkmax) := parent.process_item_type;
790 checkname(checkmax) := parent.process_name;
791 end if;
792 end if; -- end not root child
793 end loop parentloop;
794
795 if (parentcurs1%ISOPEN) then
796 CLOSE parentcurs1;
797 elsif (parentcurs2%ISOPEN) then
798 CLOSE parentcurs2;
799 end if;
800
801 checkcur := checkcur + 1;
802 end loop checkloop;
803
804 -- All versions of this activity ...
805 <<verloop>>
806 for ver in vercurs(c_item_type, c_name) loop
807 refflag := FALSE;
808 -- All root processes in reference table ...
809 <<rootloop>>
810 for i in 1 .. rootmax loop
811 -- All items in this version using this root process
812 if (rootname(i) = 'ROOT') then
813 open itemcurs1(ver.begin_date, ver.end_date,roottype(i),rootname(i));
814 else
815 open itemcurs2(ver.begin_date, ver.end_date,roottype(i),rootname(i));
816 end if;
817
818 /**
819 ** Use the itemcur1/itemcur2 depending on rootname
820 ** ROOT or not
821 for item in itemcurs(ver.begin_date, ver.end_date,
822 roottype(i), rootname(i)) loop
823 **/
824 <<itemloop>>
825
826 loop
827
828 if (rootname(i)= 'ROOT') then
829 fetch itemcurs1 into item;
830 EXIT WHEN itemcurs1%NOTFOUND;
831 else
832 fetch itemcurs2 into item1;
833 EXIT WHEN itemcurs2%NOTFOUND;
834 item := item1;
835 end if;
836
837 begin
838 -- Search tree for a reference
839 rootid := Wf_Process_Activity.RootInstanceId(item.item_type,
840 item.item_key, item.root_activity);
841 if (Wf_Process_Activity.IsChild(rootid, c_item_type,
842 c_name, item.begin_date)) then
843 refflag := TRUE;
844 exit rootloop;
845 end if;
846 exception
847 when others then
848 -- If any errors occur while searching the item, then the
849 -- item process has been somehow corrupted.
850 -- To be on the safe side, assume it might have a reference
851 -- and do not purge this act/version.
852 refflag := TRUE;
853 exit rootloop;
854 end;
855 end loop itemloop;
856 if (itemcurs1%ISOPEN) then
857 CLOSE itemcurs1;
858 elsif (itemcurs2%ISOPEN) then
859 CLOSE itemcurs2;
860 end if;
861 end loop rootloop;
862
863 if (itemcurs1%ISOPEN) then
864 CLOSE itemcurs1;
865 elsif (itemcurs2%ISOPEN) then
866 CLOSE itemcurs2;
867 end if;
868
869 if (not refflag) then
870 -- Purge the activity if no reference found
871
872 -- Delete any process activities and
873 -- their attribute values and transitions
874 delete from WF_ACTIVITY_ATTR_VALUES WAAV
875 where WAAV.PROCESS_ACTIVITY_ID in
876 (select WPA.INSTANCE_ID
877 from WF_PROCESS_ACTIVITIES WPA
878 where WPA.PROCESS_NAME = c_name
879 and WPA.PROCESS_ITEM_TYPE = c_item_type
880 and WPA.PROCESS_VERSION = ver.version);
881
882 delete from WF_ACTIVITY_TRANSITIONS WAT
883 where WAT.TO_PROCESS_ACTIVITY in
884 (select WPA.INSTANCE_ID
885 from WF_PROCESS_ACTIVITIES WPA
886 where WPA.PROCESS_NAME = c_name
887 and WPA.PROCESS_ITEM_TYPE = c_item_type
888 and WPA.PROCESS_VERSION = ver.version);
889
890 delete from WF_ACTIVITY_TRANSITIONS WAT
891 where WAT.FROM_PROCESS_ACTIVITY in
892 (select WPA.INSTANCE_ID
893 from WF_PROCESS_ACTIVITIES WPA
894 where WPA.PROCESS_NAME = c_name
895 and WPA.PROCESS_ITEM_TYPE = c_item_type
896 and WPA.PROCESS_VERSION = ver.version);
897
898 delete from WF_PROCESS_ACTIVITIES WPA
899 where WPA.PROCESS_NAME = c_name
900 and WPA.PROCESS_ITEM_TYPE = c_item_type
901 and WPA.PROCESS_VERSION = ver.version;
902
903 -- Delete activity attributes and _tl
904 delete from WF_ACTIVITY_ATTRIBUTES_TL WAAT
905 where WAAT.ACTIVITY_NAME = c_name
906 and WAAT.ACTIVITY_ITEM_TYPE = c_item_type
907 and WAAT.ACTIVITY_VERSION = ver.version;
908
909 delete from WF_ACTIVITY_ATTRIBUTES WAA
910 where WAA.ACTIVITY_NAME = c_name
911 and WAA.ACTIVITY_ITEM_TYPE = c_item_type
912 and WAA.ACTIVITY_VERSION = ver.version;
913
914 -- Delete from activities table and _tl table.
915 delete from WF_ACTIVITIES_TL WAT
916 where WAT.NAME = c_name
917 and WAT.ITEM_TYPE = c_item_type
918 and WAT.VERSION = ver.version;
919
920 delete from WF_ACTIVITIES WA
921 where WA.NAME = c_name
922 and WA.ITEM_TYPE = c_item_type
923 and WA.VERSION = ver.version;
924 end if;
925
926 end loop verloop;
927
928 -- If no versions of activity are left, clear any references to
929 -- this activity in ROOT folders
930 select count(1)
931 into numvers
932 from WF_ACTIVITIES WA
933 where WA.NAME = c_name
934 and WA.ITEM_TYPE = c_item_type;
935
936 if (numvers = 0) then
937 delete from WF_PROCESS_ACTIVITIES WPA
938 where WPA.PROCESS_ITEM_TYPE = c_item_type
939 and WPA.PROCESS_NAME = 'ROOT'
940 and WPA.ACTIVITY_ITEM_TYPE = c_item_type
941 and WPA.ACTIVITY_NAME = c_name;
942 end if;
943
944 end loop actloop;
945
946
947 if (actcurs1%ISOPEN) then
948 close actcurs1;
949 end if;
950
951 if (actcurs2%ISOPEN) then
952 close actcurs2;
953 end if;
954
955 if (actcurs3%ISOPEN) then
956 close actcurs3;
957 end if;
958
959 end loop outer_actloop;
960
961 if (actcurs1%ISOPEN) then
962 close actcurs1;
963 end if;
964
965 if (actcurs2%ISOPEN) then
966 close actcurs2;
967 end if;
968
969 if (actcurs3%ISOPEN) then
970 close actcurs3;
971 end if;
972
973 -- Clear engine runtime cache for convenience
974 Wf_Activity.ClearCache;
975
976 exception
977 when others then
978 if (actcurs1%ISOPEN) then
979 close actcurs1;
980 end if;
981
982 if (actcurs2%ISOPEN) then
983 close actcurs2;
984 end if;
985
986 if (actcurs3%ISOPEN) then
987 close actcurs3;
988 end if;
989 Wf_Core.Context('Wf_Purge', 'Activities', itemtype, to_char(enddate));
990 raise;
991 end Activities;
992
993 --
994 -- procedure Notifications
995 -- Delete old notifications with end_time before argument,
996 -- and that are not referenced by an existing item.
997 -- IN:
998 -- itemtype - Item type to delete, or null for all itemtypes
999 -- enddate - Date to obsolete to
1000 -- docommit- Do not commit if set to false
1001 -- NOTE:
1002 -- It is recommended to purge Items before purging Notifications to avoid
1003 -- obsolete item references preventing obsolete notifications from being
1004 -- deleted.
1005 --
1006 procedure Notifications(
1007 itemtype in varchar2,
1008 enddate in date,
1009 docommit in boolean,
1010 purgesigs in pls_integer)
1011 is
1012 TYPE nidListTAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1013 l_nidListTAB nidListTAB;
1014
1015 -- Cursor to delete all enddated notifications. If the design info for the item
1016 -- is available check for the persistence, if not just delete
1017 cursor c1 is
1018 select WN.NOTIFICATION_ID
1019 from WF_NOTIFICATIONS WN
1020 where WN.MESSAGE_TYPE = itemtype
1021 and not exists
1022 (select NULL
1023 from WF_ITEM_ACTIVITY_STATUSES WIAS
1024 where WIAS.NOTIFICATION_ID = WN.GROUP_ID)
1025 and not exists
1026 (select NULL
1027 from WF_ITEM_ACTIVITY_STATUSES_H WIAS
1028 where WIAS.NOTIFICATION_ID = WN.GROUP_ID)
1029 and (
1030 exists(
1031 select null
1032 from WF_ITEM_TYPES WIT
1033 where WN.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0) <= enddate
1034 and WN.MESSAGE_TYPE = WIT.NAME
1035 and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
1036 or not exists(
1037 select null
1038 from WF_ITEM_TYPES WIT
1039 where WN.MESSAGE_TYPE = WIT.NAME))
1040 and(
1041 (purgesigs = 1)
1042 or not exists
1043 (select null
1044 from WF_DIG_SIGS WDS
1045 where SIG_OBJ_TYPE = 'WF_NTF'
1046 and SIG_OBJ_ID = WN.NOTIFICATION_ID));
1047
1048 cursor c2 is
1049 select WN.NOTIFICATION_ID
1050 from WF_NOTIFICATIONS WN
1051 where not exists
1052 (select NULL
1053 from WF_ITEM_ACTIVITY_STATUSES WIAS
1054 where WIAS.NOTIFICATION_ID = WN.GROUP_ID)
1055 and not exists
1056 (select NULL
1057 from WF_ITEM_ACTIVITY_STATUSES_H WIAS
1058 where WIAS.NOTIFICATION_ID = WN.GROUP_ID)
1059 and (
1060 exists(
1061 select null
1062 from WF_ITEM_TYPES WIT
1063 where WN.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0) <= enddate
1064 and WN.MESSAGE_TYPE = WIT.NAME
1065 and WIT.PERSISTENCE_TYPE = Wf_Purge.persistence_type)
1066 or not exists(
1067 select null
1068 from WF_ITEM_TYPES WIT
1069 where WN.MESSAGE_TYPE = WIT.NAME))
1070 and(
1071 (purgesigs = 1)
1072 or not exists
1073 (select null
1074 from WF_DIG_SIGS WDS
1075 where SIG_OBJ_TYPE = 'WF_NTF'
1076 and SIG_OBJ_ID = WN.NOTIFICATION_ID));
1077
1078 -- <rwunderl:3751558>
1079 invalid_component exception;
1080 pragma EXCEPTION_INIT(invalid_component, -302);
1081
1082 invalid_identifier exception;
1083 pragma EXCEPTION_INIT(invalid_identifier, -201);
1084 -- </rwunderl:3751558>
1085 begin
1086
1087 -- Disallow future date in enddate
1088 -- Avoid something being purged before its defined persistence period.
1089 if (enddate > sysdate) then
1090 Wf_Core.Token('SYSDATE', to_char(sysdate));
1091 Wf_Core.Raise('WF_FUTURE_END_DATE');
1092 end if;
1093
1094 -- Bug 3228475 Disallow partial values being passed for itemtype
1095 if (instr(itemtype,'%')>0) then
1096 Wf_Core.Raise('WFSQL_ARGS');
1097 end if;
1098
1099 -- End date all the orphan notifications. This end dates all the notifications
1100 -- generated before the enddate parameter value.
1101 loop
1102 UPDATE wf_notifications wn
1103 SET end_date = nvl(begin_date, to_date('2002/08/01','YYYY/MM/DD')) + 1
1104 WHERE NOT EXISTS
1105 (SELECT NULL
1106 FROM wf_item_activity_statuses wias
1107 WHERE wias.notification_id = wn.group_id)
1108 AND NOT EXISTS
1109 (SELECT NULL
1110 FROM wf_item_activity_statuses_h wiash
1111 WHERE wiash.notification_id = wn.group_id)
1112 AND wn.end_date is null
1113 AND wn.begin_date <= enddate
1114 AND rownum < Wf_Purge.Commit_Frequency;
1115
1116 --Bug 6759163 : if statement added to avoid commit when
1117 --'docommit' parameter is false
1118 if (docommit) then
1119 commit;
1120 end if;
1121
1122 exit when (sql%NOTFOUND);
1123 end loop;
1124
1125 <<outer_ntf_loop>>
1126 loop
1127
1128 if (itemtype is not null) then
1129 open c1;
1130 else
1131 open c2;
1132 end if;
1133
1134 <<inner_ntf_loop>>
1135 loop
1136
1137 if (itemtype is not null) then
1138 -- Bug 4163328
1139 -- Use bulk operations to improve performance
1140 FETCH c1 BULK COLLECT INTO l_nidListTAB LIMIT wf_purge.commit_frequency;
1141 exit outer_ntf_loop when l_nidListTAB.COUNT = 0;
1142 else
1143 FETCH c2 BULK COLLECT INTO l_nidListTAB LIMIT wf_purge.commit_frequency;
1144 exit outer_ntf_loop when l_nidListTAB.COUNT = 0;
1145 end if;
1146
1147 -- Delete notification attributes
1148 FORALL j in l_nidListTAB.FIRST..l_nidListTAB.LAST
1149 delete from WF_NOTIFICATION_ATTRIBUTES WNA
1150 where WNA.NOTIFICATION_ID = l_nidListTAB(j);
1151
1152 --<rwunderl:3751558>
1153 -- Delete the signatures related to this notification
1154 if (purgesigs = 1) then
1155 FOR j in l_nidListTAB.FIRST..l_nidListTAB.LAST loop
1156 begin
1157 execute IMMEDIATE 'begin Wf_Digital_Security_Private.Purge_Signature_By_Obj_Id(''WF_NTF'', :1); end;'
1158 using to_char(l_nidListTAB(j));
1159 exception
1160 when invalid_identifier then
1161 null;
1162
1163 when invalid_component then
1164 null;
1165 end;
1166 --</rwunderl:3751558>
1167 end loop;
1168 end if;
1169
1170 -- Delete Notification comments
1171 FORALL j in l_nidListTAB.FIRST..l_nidListTAB.LAST
1172 DELETE FROM wf_comments wc
1173 WHERE wc.notification_id = l_nidListTAB(j);
1174
1175 -- Delete notifications
1176 FORALL j in l_nidListTAB.FIRST..l_nidListTAB.LAST
1177 delete from WF_NOTIFICATIONS WN
1178 where WN.NOTIFICATION_ID = l_nidListTAB(j);
1179
1180 if (l_nidListTAB.COUNT >= wf_purge.commit_frequency) then
1181 exit inner_ntf_loop;
1182 elsif (l_nidListTAB.COUNT < wf_purge.commit_frequency) then
1183 exit outer_ntf_loop;
1184 end if;
1185
1186 end loop inner_ntf_loop;
1187
1188 if (c1%ISOPEN) then
1189 close c1;
1190 elsif (c2%ISOPEN) then
1191 close c2;
1192 end if;
1193
1194 -- Commit Decision
1195 if (docommit) then
1196 commit;
1197 Fnd_Concurrent.Set_Preferred_RBS;
1198 end if;
1199
1200 end loop outer_ntf_loop;
1201
1202 if (c1%ISOPEN) then
1203 close c1;
1204 elsif (c2%ISOPEN) then
1205 close c2;
1206 end if;
1207
1208 -- Commit Decision
1209 if (docommit) then
1210 commit;
1211 Fnd_Concurrent.Set_Preferred_RBS;
1212 end if;
1213
1214 -- Purge AdHoc Users/Roles/User_Roles
1215 Wf_Purge.AdHocDirectory(enddate);
1216
1217 exception
1218 when others then
1219 if (c1%ISOPEN) then
1220 close c1;
1221 elsif (c2%ISOPEN) then
1222 close c2;
1223 end if;
1224
1225 Wf_Core.Context('Wf_Purge', 'Notifications', itemtype, to_char(enddate));
1226 raise;
1227 end Notifications;
1228
1229 --
1230 -- procedure Item_Notifications
1231 -- Delete notifications sent by a particular item with end_time
1232 -- before argument.
1233 -- IN:
1234 -- itemtype - Item type to delete, or null for all itemtypes
1235 -- itemkey - Item key to delete, or null for all itemkeys
1236 -- enddate - Date to obsolete to
1237 -- docommit- Do not commit if set to false
1238 --
1239 procedure Item_Notifications(
1240 itemtype in varchar2,
1241 itemkey in varchar2,
1242 enddate in date,
1243 docommit in boolean)
1244 is
1245 begin
1246 Null;
1247 end Item_Notifications;
1248
1249 --
1250 -- Total
1251 -- Delete all obsolete runtime data with end_time before argument.
1252 -- IN:
1253 -- itemtype - Item type to delete, or null for all itemtypes
1254 -- itemkey - Item key to delete, or null for all itemkeys
1255 -- enddate - Date to obsolete to
1256 -- docommit- Commit or no commit after each purge of entitiy
1257 -- runtimeonly - If true only runtime /transaction data purged
1258 -- if false both runtime and design data purged.
1259 --
1260 procedure Total(
1261 itemtype in varchar2,
1262 itemkey in varchar2,
1263 enddate in date,
1264 docommit in boolean,
1265 runtimeonly in boolean,
1266 purgesigs in pls_integer
1267 )
1268 is
1269
1270 begin
1271
1272 -- Disallow future date in enddate
1273 -- Avoid something being purged before its defined persistence period.
1274 if (enddate > sysdate) then
1275 Wf_Core.Token('SYSDATE', to_char(sysdate));
1276 Wf_Core.Raise('WF_FUTURE_END_DATE');
1277 end if;
1278
1279 -- Bug 3228475 Disallow partial values from being passed
1280 if ((itemkey is not null and (instr(itemkey,'%')>0 or itemtype is null))
1281 or (instr(itemtype,'%')>0)) then
1282 Wf_Core.Raise('WFSQL_ARGS');
1283 end if;
1284
1285 -- Bug 1636510
1286 -- Before attempting to purge items we will abort any error process
1287 -- if the activity that launched it is now complete.
1288 wf_purge.abortErrorProcess(itemtype, itemkey);
1289
1290 l_docommit := docommit;
1291 l_runtimeonly := runtimeonly;
1292 wf_purge.items(itemtype=>itemtype, itemkey=>itemkey, enddate=>enddate, docommit=>docommit,purgesigs=>purgesigs);
1293
1294 if (docommit) then
1295 commit;
1296 Fnd_Concurrent.Set_Preferred_RBS;
1297 end if;
1298
1299 --Purge design data only if runtime is
1300 --set to false
1301 if (not nvl(runtimeonly,TRUE)) then
1302 --also purge design data from activities
1303 wf_purge.activities(itemtype=>itemtype, enddate=>enddate);
1304 if (docommit) then
1305 commit;
1306 Fnd_Concurrent.Set_Preferred_RBS;
1307 end if;
1308
1309 --Directory Information
1310 Wf_Purge.Directory(enddate);
1311 if (docommit) then
1312 commit;
1313 Fnd_Concurrent.Set_Preferred_RBS;
1314 end if;
1315 end if;
1316
1317 -- Clear engine runtime cache for convenience
1318 Wf_Item.ClearCache;
1319
1320 -- Purge orphan notifications only if runtimeonly is false
1321 if (not nvl(runtimeonly, TRUE)) then
1322 --Bug 6759163 : 'docommit' parameter passed to Notifications() as well
1323 Notifications(itemtype=>itemtype, enddate=>enddate,docommit=>docommit,purgesigs=>purgesigs);
1324 end if;
1325 exception
1326 when others then
1327 Wf_Core.Context('Wf_Purge', 'Total', itemtype, itemkey, to_char(enddate));
1328 raise;
1329 end Total;
1330
1331 --
1332 -- TotalPERM
1333 -- Delete all obsolete runtime data that is of persistence type 'PERM'
1334 -- and with end_time before argument.
1335 -- IN:
1336 -- itemtype - Item type to delete, or null for all itemtypes
1337 -- itemkey - Item key to delete, or null for all itemkeys
1338 -- enddate - Date to obsolete to
1339 -- docommit- Commit or no commit after each purge of entitiy
1340 -- runtimeonly - Delete runtime data alone if set to true
1341 -- else delete both design and runtime data
1342 --
1343 procedure TotalPERM(
1344 itemtype in varchar2,
1345 itemkey in varchar2,
1346 enddate in date,
1347 docommit in boolean,
1348 runtimeonly in boolean,
1349 purgesigs in pls_integer)
1350 is
1351 begin
1352 wf_purge.persistence_type := 'PERM';
1353
1354 -- Call Total with new args
1355 Wf_Purge.Total(
1356 itemtype,
1357 itemkey,
1358 enddate,
1359 docommit,
1360 nvl(runtimeonly, TRUE),
1361 purgesigs);
1362
1363 -- Reset persistence type to the default value
1364 wf_purge.persistence_type := 'TEMP';
1365
1366 exception
1367 when others then
1368 -- Reset persistence type to the default value
1369 wf_purge.persistence_type := 'TEMP';
1370 Wf_Core.Context('Wf_Purge', 'TotalPERM', itemtype, itemkey,
1371 to_char(enddate));
1372 raise;
1373 end TotalPERM;
1374
1375
1376 --
1377 -- TotalConcurrent
1378 -- Concurrent Program version of Total
1379 -- IN:
1380 -- errbuf - CPM error message
1381 -- retcode - CPM return code (0 = success, 1 = warning, 2 = error)
1382 -- itemtype - Item type to delete, or null for all itemtypes
1383 -- itemkey - Item key to delete, or null for all itemkeys
1384 -- age - Minimum age of data to purge (in days)
1385 -- x_persistence_type - Persistence Type to be purged: 'TEMP' or 'PERM'
1386 -- runtimeonly - If 'Y' then purge runtime data alone
1387 --
1388 procedure TotalConcurrent(
1389 errbuf out NOCOPY varchar2,
1390 retcode out NOCOPY varchar2,
1391 itemtype in varchar2,
1392 itemkey in varchar2,
1393 age in varchar2,
1394 x_persistence_type in varchar2,
1395 runtimeonly in varchar2,
1396 x_commit_frequency in number,
1397 purgesigs in varchar2)
1398 is
1399 enddate date;
1400 errname varchar2(30);
1401 errmsg varchar2(2000);
1402 errstack varchar2(2000);
1403
1404 l_runtimeonly boolean := FALSE;
1405 l_purgesigs pls_integer;
1406 begin
1407 -- Convert arguments from varchar2 to real type.
1408 enddate := sysdate - to_number(age);
1409
1410 wf_purge.persistence_type := x_persistence_type;
1411 wf_purge.commit_frequency := x_commit_frequency;
1412
1413 --If runtimeonly 'N' then purge both runtime
1414 --and design data
1415 if (upper(runtimeonly) = 'Y') then
1416 l_runtimeonly := TRUE;
1417 end if;
1418
1419 if (upper(purgesigs) = 'Y') then
1420 l_purgesigs := 1;
1421 end if;
1422
1423 -- Call Total with new args
1424 Wf_Purge.Total(
1425 itemtype,
1426 itemkey,
1427 enddate,
1428 true,
1429 l_runtimeonly,
1430 l_purgesigs);
1431
1432
1433 -- Return 0 for successful completion.
1434 errbuf := '';
1435 retcode := '0';
1436 wf_purge.persistence_type := 'TEMP'; -- reset to the default value
1437 wf_purge.commit_frequency := 1000; -- reset to the default value
1438
1439 exception
1440 when others then
1441 -- Retrieve error message into errbuf
1442 wf_core.get_error(errname, errmsg, errstack);
1443 if (errmsg is not null) then
1444 errbuf := errmsg;
1445 else
1446 errbuf := sqlerrm;
1447 end if;
1448
1449 -- Return 2 for error.
1450 retcode := '2';
1451
1452 -- Reset persistence type to the default value
1453 wf_purge.persistence_type := 'TEMP';
1454 end TotalConcurrent;
1455
1456 --
1457 -- Directory
1458 -- Purge all WF_LOCAL_* tables based on expiration date
1459 -- IN:
1460 -- end_date - Date to purge to
1461 --
1462 procedure Directory(
1463 end_date in date default sysdate,
1464 orig_system in varchar2 default null)
1465 is
1466 --
1467 -- All roles that past the end_date and with no outstanding notification
1468 --
1469 -- Need to check ORIGINAL_RECIPIENT as well as the RECIPIENT_ROLE just
1470 -- in case a 'Delegate' happened, we won't purge the orginal owner.
1471 --
1472 cursor role_cursor is
1473 select local.NAME, local.ORIG_SYSTEM, local.ORIG_SYSTEM_ID,
1474 local.USER_FLAG
1475 from WF_LOCAL_ROLES local
1476 where PARTITION_ID = 0
1477 and ORIG_SYSTEM in ('WF_LOCAL_ROLES', 'WF_LOCAL_USERS')
1478 and EXPIRATION_DATE <= end_date
1479 and not exists
1480 (select NULL
1481 from WF_ROLE_HIERARCHIES
1482 where SUPER_NAME = local.NAME
1483 or SUB_NAME = local.NAME)
1484 and not exists
1485 (select NULL
1486 from WF_NOTIFICATIONS wn
1487 where wn.RECIPIENT_ROLE = local.NAME
1488 or wn.ORIGINAL_RECIPIENT = local.NAME);
1489
1490
1491 cursor orig_system_cursor (p_OrigSystem varchar2,
1492 p_endDate date) is
1493 select local.NAME, local.ORIG_SYSTEM, local.ORIG_SYSTEM_ID,
1494 local.USER_FLAG
1495 from WF_LOCAL_ROLES local
1496 where PARTITION_ID = 0
1497 and ORIG_SYSTEM = p_origSystem
1498 and (p_endDate is NULL or EXPIRATION_DATE <= p_endDate)
1499 and not exists
1500 (select NULL
1501 from WF_ROLE_HIERARCHIES
1502 where SUPER_NAME = local.NAME
1503 or SUB_NAME = local.NAME)
1504 and not exists
1505 (select NULL
1506 from WF_USER_ROLE_ASSIGNMENTS
1507 where USER_NAME = local.NAME
1508 and RELATIONSHIP_ID <> -1)
1509 and not exists
1510 (select NULL
1511 from WF_NOTIFICATIONS wn
1512 where wn.RECIPIENT_ROLE = local.NAME
1513 or wn.ORIGINAL_RECIPIENT = local.NAME)
1514 and not exists
1515 (select NULL
1516 from WF_ITEMS wi
1517 where wi.OWNER_ROLE = local.NAME);
1518
1519 rcurs role_cursor%rowtype;
1520 rcurs2 orig_system_cursor%rowtype;
1521
1522 begin
1523 -- Disallow future date in enddate
1524 -- Avoid something being purged before its expiration date.
1525 if (end_date > sysdate) then
1526 Wf_Core.Token('SYSDATE', to_char(sysdate));
1527 Wf_Core.Raise('WF_FUTURE_END_DATE');
1528 end if;
1529
1530 if (orig_system is NOT NULL) then
1531 --We are using the new feature allowing a specific orig_system to be
1532 --removed from the local partition either by end_date, or remove all with
1533 --a null end-date (providing there are no active notifications or items).
1534 --
1535 -- Purge Roles
1536 -- Delete rows in wf_local_user_roles by role_name and wf_local_roles
1537 -- by name as determined
1538 for rcurs2 in orig_system_cursor(orig_system, end_date) loop
1539 if (rcurs2.user_flag = 'Y') then
1540 delete from WF_LOCAL_USER_ROLES local
1541 where local.USER_NAME = rcurs2.name
1542 and local.USER_ORIG_SYSTEM = rcurs2.orig_system
1543 and local.USER_ORIG_SYSTEM_ID = rcurs2.orig_system_id;
1544
1545 delete from WF_USER_ROLE_ASSIGNMENTS local
1546 where local.USER_NAME = rcurs2.name
1547 and local.USER_ORIG_SYSTEM = rcurs2.orig_system
1548 and local.USER_ORIG_SYSTEM_ID = rcurs2.orig_system_id;
1549 else
1550 delete from WF_LOCAL_USER_ROLES local
1551 where local.ROLE_NAME = rcurs2.name
1552 and local.ROLE_ORIG_SYSTEM = rcurs2.orig_system
1553 and local.ROLE_ORIG_SYSTEM_ID = rcurs2.orig_system_id;
1554
1555 delete from WF_USER_ROLE_ASSIGNMENTS local
1556 where local.ROLE_NAME = rcurs2.name
1557 and local.ROLE_ORIG_SYSTEM = rcurs2.orig_system
1558 and local.ROLE_ORIG_SYSTEM_ID = rcurs2.orig_system_id;
1559
1560 end if;
1561
1562 --Remove the role
1563 delete from WF_LOCAL_ROLES local
1564 where local.NAME = rcurs2.name
1565 and local.ORIG_SYSTEM = rcurs2.orig_system
1566 and local.ORIG_SYSTEM_ID = rcurs2.orig_system_id;
1567 end loop;
1568 elsif (end_date is not null) then
1569 --
1570 -- Purge Roles
1571 -- Delete rows in wf_local_user_roles by role_name and wf_local_roles
1572 -- by name as determined
1573 for rcurs in role_cursor loop
1574 if (rcurs.user_flag = 'Y') then
1575 delete from WF_LOCAL_USER_ROLES local
1576 where local.USER_NAME = rcurs.name
1577 and local.USER_ORIG_SYSTEM = rcurs.orig_system
1578 and local.USER_ORIG_SYSTEM_ID = rcurs.orig_system_id;
1579
1580 delete from WF_USER_ROLE_ASSIGNMENTS local
1581 where local.USER_NAME = rcurs.name
1582 and local.USER_ORIG_SYSTEM = rcurs.orig_system
1583 and local.USER_ORIG_SYSTEM_ID = rcurs.orig_system_id;
1584 else
1585 delete from WF_LOCAL_USER_ROLES local
1586 where local.ROLE_NAME = rcurs.name
1587 and local.ROLE_ORIG_SYSTEM = rcurs.orig_system
1588 and local.ROLE_ORIG_SYSTEM_ID = rcurs.orig_system_id;
1589
1590 delete from WF_USER_ROLE_ASSIGNMENTS local
1591 where local.ROLE_NAME = rcurs.name
1592 and local.ROLE_ORIG_SYSTEM = rcurs.orig_system
1593 and local.ROLE_ORIG_SYSTEM_ID = rcurs.orig_system_id;
1594
1595 end if;
1596
1597 --Remove the role
1598 delete from WF_LOCAL_ROLES local
1599 where local.NAME = rcurs.name
1600 and local.ORIG_SYSTEM = rcurs.orig_system
1601 and local.ORIG_SYSTEM_ID = rcurs.orig_system_id;
1602 end loop;
1603 end if;
1604 exception
1605 when others then
1606 if (role_cursor%isopen) then
1607 close role_cursor;
1608 end if;
1609 wf_core.context('WF_PURGE', 'Directory', end_date);
1610 raise;
1611 end Directory;
1612
1613 --
1614 -- AdHocDirectory
1615 -- Purge all WF_LOCAL_* tables based on expiration date
1616 -- IN:
1617 -- end_date - Date to purge to
1618 --
1619 procedure AdHocDirectory(
1620 end_date in date)
1621 is
1622 begin
1623 Wf_Purge.Directory(end_date);
1624 exception
1625 when OTHERS then
1626 wf_core.context('WF_PURGE', 'AdHocDirectory', end_date);
1627 raise;
1628 end AdHocDirectory;
1629
1630 --
1631 -- GetPurgeableCount
1632 -- Returns the count of purgeable items for a specific itemType.
1633 -- IN:
1634 -- p_itemType in VARCHAR2
1635 --
1636 FUNCTION GetPurgeableCount (p_itemType in varchar2) return number is
1637 l_purgeable number ;
1638 BEGIN
1639 --Get all records for the itemtype
1640 select Count(*) into l_purgeable
1641 from WF_ITEMS WI
1642 where WI.ITEM_TYPE = p_itemType
1643 and WI.END_DATE <= sysdate
1644 and exists
1645 (select null
1646 from WF_ITEM_TYPES WIT
1647 where WI.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0)<=sysdate
1648 and WI.ITEM_TYPE = WIT.NAME
1649 )
1650 and not exists
1651 (select null
1652 from WF_ITEMS WI2
1653 WHERE WI2.END_DATE IS NULL
1654 START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
1655 AND WI2.ITEM_KEY = WI.ITEM_KEY
1656 CONNECT BY PRIOR WI2.ITEM_TYPE = WI2.PARENT_ITEM_TYPE
1657 AND PRIOR WI2.ITEM_KEY = WI2.PARENT_ITEM_KEY
1658 UNION ALL
1659 select null
1660 from WF_ITEMS WI2
1661 WHERE WI2.END_DATE IS NULL
1662 START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
1663 AND WI2.ITEM_KEY = WI.ITEM_KEY
1664 CONNECT BY PRIOR WI2.PARENT_ITEM_TYPE = WI2.ITEM_TYPE
1665 AND PRIOR WI2.PARENT_ITEM_KEY = WI2.ITEM_KEY);
1666 return l_purgeable;
1667 exception
1668 when OTHERS then
1669 wf_core.context('WF_PURGE', 'GetPurgeableCount', p_itemType);
1670 raise;
1671 end;
1672
1673
1674 --
1675 -- AbortErrorProcess
1676 -- Aborts the Error process for an errored activity if the activity is
1677 -- now COMPLETE.
1678 -- IN:
1679 -- itemtype in VARCHAR2
1680 -- itemkey in VARCHAR2
1681 --
1682 procedure AbortErrorProcess (itemtype varchar2,
1683 itemkey varchar2)
1684 is
1685
1686 c_item_key varchar2(240);
1687
1688 cursor c_error1 is
1689 select wi.item_key
1690 from wf_item_activity_statuses wias, wf_items wi
1691 where wi.item_type = 'WFERROR'
1692 and parent_item_type = itemtype
1693 and wi.parent_item_type = wias.item_type
1694 and wi.parent_item_key = wias.item_key
1695 and wi.parent_context = wias.process_activity
1696 and wias.activity_status = 'COMPLETE';
1697
1698 cursor c_error2 is
1699 select wi.item_key
1700 from wf_item_activity_statuses wias, wf_items wi
1701 where wi.item_type = 'WFERROR'
1702 and parent_item_type = itemtype
1703 and parent_item_key = itemkey
1704 and wi.parent_item_type = wias.item_type
1705 and wi.parent_item_key = wias.item_key
1706 and wi.parent_context = wias.process_activity
1707 and wias.activity_status = 'COMPLETE';
1708
1709 cursor c_error3 is
1710 select wi.item_key
1711 from wf_item_activity_statuses wias, wf_items wi
1712 where wi.item_type = 'WFERROR'
1713 and wi.parent_item_type = wias.item_type
1714 and wi.parent_item_key = wias.item_key
1715 and wi.parent_context = wias.process_activity
1716 and wias.activity_status = 'COMPLETE';
1717
1718
1719 begin
1720
1721 if ((itemkey is not null and (instr(itemkey,'%')>0 or itemtype is null))
1722 or (instr(itemtype,'%')>0)) then
1723 Wf_Core.Raise('WFSQL_ARGS');
1724 end if;
1725
1726 -- Outer loop
1727 <<outer_abort>>
1728 loop
1729
1730 if (itemtype is not null and itemkey is null) then
1731 open c_error1;
1732 elsif (itemtype is not null) then
1733 open c_error2;
1734 else
1735 open c_error3;
1736 end if;
1737
1738 -- Inner loop
1739 <<abort_loop>>
1740 loop
1741
1742 if (itemtype is not null and itemkey is null) then
1743 fetch c_error1 into c_item_key;
1744 if (c_error1%notfound) then
1745 exit outer_abort;
1746 end if;
1747 elsif (itemtype is not null) then
1748 fetch c_error2 into c_item_key;
1749 if (c_error2%notfound) then
1750 exit outer_abort;
1751 end if;
1752 else
1753 fetch c_error3 into c_item_key;
1754 if (c_error3%notfound) then
1755 exit outer_abort;
1756 end if;
1757 end if;
1758
1759 -- Abort the error process since the activity is now COMPLETE
1760 begin
1761
1762 wf_engine.abortprocess('WFERROR', c_item_key);
1763
1764 exception when others then
1765 null;
1766 end;
1767
1768 end loop abort_loop;
1769
1770 if (c_error1%ISOPEN) then
1771 close c_error1;
1772 end if;
1773
1774 if (c_error2%ISOPEN) then
1775 close c_error2;
1776 end if;
1777
1778 if (c_error3%ISOPEN) then
1779 close c_error3;
1780 end if;
1781
1782 end loop outer_abort;
1783
1784 if (c_error1%ISOPEN) then
1785 close c_error1;
1786 end if;
1787
1788 if (c_error2%ISOPEN) then
1789 close c_error2;
1790 end if;
1791
1792 if (c_error3%ISOPEN) then
1793 close c_error3;
1794 end if;
1795
1796 EXCEPTION WHEN OTHERS THEN
1797 if (c_error1%ISOPEN) then
1798 close c_error1;
1799 end if;
1800
1801 if (c_error2%ISOPEN) then
1802 close c_error2;
1803 end if;
1804
1805 if (c_error3%ISOPEN) then
1806 close c_error3;
1807 end if;
1808
1809 Wf_Core.Context('Wf_Purge', 'AbortErrorProcess', itemtype, itemkey);
1810 raise;
1811
1812 end AbortErrorProcess;
1813
1814 -- 3966635 Workflow Provisioning Project
1815 -- Following procedures are added in order not to loose the changes
1816 -- required.
1817 -- --
1818 -- -- procedure ProvisionRequests
1819 -- -- Delete old provisiong requests with end_time before argument.
1820 -- -- These provisioning requests are not tied explicity to a item.
1821 -- -- Those requests will be removed with the WF_PURGE.Item API.
1822 -- --
1823 -- -- IN:
1824 -- -- context - The context to match for the requests to remove.
1825 -- -- enddate - Date to obsolete to
1826 -- -- docommit- Do not commit if set to false
1827 -- -- NOTE:
1828 -- --
1829 -- procedure ProvisionRequests(
1830 -- context in varchar2,
1831 -- enddate in date,
1832 -- docommit in boolean)
1833 -- is
1834 -- l_context varchar2(320);
1835 -- l_prov_request_id number;
1836 --
1837 -- cursor c1(c_context in varchar2,
1838 -- c_end_date in date)
1839 -- is
1840 -- select prov_request_id
1841 -- from wf_prov_requests
1842 -- where source = 'EXTERNAL'
1843 -- and context like c_context
1844 -- and (end_date is not null and end_date < c_end_date);
1845 --
1846 -- begin
1847 --
1848 -- if (enddate > sysdate) then
1849 -- wf_core.context('WF_PURGE', 'ProvisionRequests');
1850 -- Wf_Core.Token('SYSDATE', to_char(sysdate));
1851 -- Wf_Core.Raise('WF_FUTURE_END_DATE');
1852 -- end if;
1853 --
1854 -- if context is null or length(context) = 0 then
1855 -- l_context := '%';
1856 -- else
1857 -- l_context := context;
1858 -- end if;
1859 --
1860 -- <<outer_prv_loop>>
1861 -- loop
1862 --
1863 -- open c1(l_context, enddate);
1864 --
1865 -- <<inner_prv_loop>>
1866 -- loop
1867 -- fetch c1 into l_prov_request_id;
1868 -- if c1%notfound then
1869 -- exit outer_prv_loop;
1870 -- end if;
1871 --
1872 -- delete from WF_PROV_REQUESTS
1873 -- where prov_request_id = l_prov_request_id;
1874 --
1875 -- exit inner_prv_loop when
1876 -- ( docommit and (c1%rowcount = wf_purge.commit_frequency));
1877 --
1878 -- end loop inner_prv_loop;
1879 --
1880 -- if c1%isopen then
1881 -- close c1;
1882 -- end if;
1883 --
1884 -- if (docommit) then
1885 -- commit;
1886 -- Fnd_Concurrent.Set_Preferred_RBS;
1887 -- end if;
1888 --
1889 -- end loop outer_prv_loop;
1890 --
1891 --
1892 -- if c1%isopen then
1893 -- close c1;
1894 -- end if;
1895 --
1896 -- if (docommit) then
1897 -- commit;
1898 -- Fnd_Concurrent.Set_Preferred_RBS;
1899 -- end if;
1900 --
1901 -- exception
1902 -- when others then
1903 -- if c1%isopen then
1904 -- close c1;
1905 -- end if;
1906 -- wf_core.context('WF_PURGE','ProvisionRequests', context, enddate);
1907 -- end ProvisionRequests;
1908 --
1909 -- --
1910 -- -- ProvisionRequestsConcurrent
1911 -- -- Concurrent Program version of Total
1912 -- -- IN:
1913 -- -- errbuf - CPM error message
1914 -- -- retcode - CPM return code (0 = success, 1 = warning, 2 = error)
1915 -- -- itemtype - Item type to delete, or null for all itemtypes
1916 -- -- itemkey - Item key to delete, or null for all itemkeys
1917 -- -- age - Minimum age of data to purge (in days)
1918 -- -- x_persistence_type - Persistence Type to be purged: 'TEMP' or 'PERM'
1919 -- -- runtimeonly - If 'Y' then purge runtime data alone
1920 -- --
1921 -- procedure ProvisionRequestsConcurrent(
1922 -- errbuf out NOCOPY varchar2,
1923 -- retcode out NOCOPY varchar2,
1924 -- context in varchar2,
1925 -- age in varchar2 default 0,
1926 -- x_commit_frequency in number default 500
1927 -- )
1928 -- is
1929 -- enddate date;
1930 -- errname varchar2(30);
1931 -- errmsg varchar2(2000);
1932 -- errstack varchar2(2000);
1933 --
1934 -- l_runtimeonly boolean := FALSE;
1935 -- begin
1936 -- -- Convert arguments from varchar2 to real type.
1937 -- if age is not null and age > 0 then
1938 -- enddate := sysdate - to_number(age);
1939 -- else
1940 -- enddate := sysdate;
1941 -- end if;
1942 --
1943 --
1944 -- if x_commit_frequency is not null or x_commit_frequency > 0 then
1945 -- wf_purge.commit_frequency := x_commit_frequency;
1946 -- end if;
1947 --
1948 -- -- Call provisionrequest with new args
1949 -- Wf_Purge.ProvisionRequests(context,
1950 -- enddate,
1951 -- true);
1952 --
1953 --
1954 -- -- Return 0 for successful completion.
1955 -- errbuf := '';
1956 -- retcode := '0';
1957 -- wf_purge.commit_frequency := 500; -- reset to the default value
1958 --
1959 -- exception
1960 -- when others then
1961 -- -- Retrieve error message into errbuf
1962 -- wf_core.get_error(errname, errmsg, errstack);
1963 -- if (errmsg is not null) then
1964 -- errbuf := errmsg;
1965 -- else
1966 -- errbuf := sqlerrm;
1967 -- end if;
1968 --
1969 -- -- Return 2 for error.
1970 -- retcode := '2';
1971
1972 -- -- Reset commit frequency to the default value
1973 -- wf_purge.commit_frequency := 500;
1974 -- end ProvisionRequestsConcurrent;
1975
1976
1977
1978 end WF_PURGE;
[Home] [Help]