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