1 PACKAGE BODY XDP_PURGE AS
2 /* $Header: XDPPRGB.pls 120.2 2006/08/09 13:47:15 dputhiye noship $ */
3
4 g_debug_level NUMBER := 4;
5
6 bAudit VARCHAR2(8) := 'FALSE';
7 g_purge_method VARCHAR2(8) := 'CM';
8
9 -- number of exceptions will be allowed before jumping out a loop.
10 g_max_exceptions NUMBER := 5;
11 -- commit every P_MSG_NAME g_max_rows_before_commit deletions
12 g_max_rows_before_commit NUMBER := 5000;
13
14 G_Total_WK_TIME NUMBER := 0;
15 G_PURGE_WORK_FLOW VARCHAR2(8) := 'FALSE';
16
17 PROCEDURE RECORDS_PURGED_MSGS(
18 p_no_records IN number DEFAULT 0,
19 p_table_name IN VARCHAR2 DEFAULT NULL,
20 p_msg_name IN VARCHAR2 DEFAULT 'XDP_PURGE_REC',
21 p_debug_level IN NUMBER DEFAULT 0 -- DEFAULT TO CONSICE MESSAGES
22 )
23 IS
24 l_error_msg VARCHAR2(2000);
25 BEGIN
26 IF p_debug_level >= g_debug_level THEN
27 RETURN;
28 END IF;
29 IF p_no_records > 1 THEN
30 FND_MESSAGE.SET_NAME('XDP',p_msg_name||'S');
31 FND_MESSAGE.SET_TOKEN('RECORDS',p_no_records);
32 FND_MESSAGE.SET_TOKEN('TABLE_NAME',p_table_name);
33 ELSIF (p_no_records = 0) THEN
34 RETURN; -- do not log anythig
35 FND_MESSAGE.SET_NAME('XDP',p_msg_name||'S');
36 FND_MESSAGE.SET_TOKEN('RECORDS','No');
37 FND_MESSAGE.SET_TOKEN('TABLE_NAME',p_table_name);
38 ELSE
39 FND_MESSAGE.SET_NAME('XDP',p_msg_name);
40 FND_MESSAGE.SET_TOKEN('TABLE_NAME',p_table_name);
41 END IF;
42 l_error_msg := SUBSTR(FND_MESSAGE.GET,0,1999);
43
44 -- IF CALLER IS CONCURRENT MANAGER, USE FND_FILE
45 -- OR ELSE USE DBMS_OUTPUT
46
47 IF g_purge_method = 'CM' THEN
48 FND_FILE.PUT_LINE(FND_FILE.LOG,l_error_msg);
49 ELSE
50 -- DBMS_OUTPUT.PUT_LINE(l_error_msg);
51 null;
52 END IF;
53 END;
54
55 --
56 -- Internal procedure for handling messages
57 -- Name PURGE_ERRORS
58 --
59
60 PROCEDURE PURGE_ERRORS(
61 p_msg IN VARCHAR2 DEFAULT NULL,
62 p_commit IN BOOLEAN DEFAULT FALSE,
63 p_abort IN BOOLEAN DEFAULT FALSE,
64 p_debug_level IN NUMBER DEFAULT 0 -- DEFAULT TO CONSICE MESSAGES
65 )
66 IS
67 BEGIN
68
69 IF g_purge_method = 'CM' THEN
70 FND_FILE.PUT_LINE(FND_FILE.LOG,P_MSG);
71 ELSE
72 -- DBMS_OUTPUT.PUT_LINE(P_MSG);
73 null;
74 END IF;
75
76 IF p_commit THEN
77 COMMIT;
78 END IF;
79
80 IF p_abort THEN
81 APP_EXCEPTION.RAISE_EXCEPTION;
82 END IF;
83 END PURGE_ERRORS;
84
85 FUNCTION USER_ROLLBACK_CTRL(p_rollback_segment VARCHAR2) RETURN BOOLEAN IS
86 l_result BOOLEAN := FALSE;
87 l_stmt VARCHAR2(200);
88 BEGIN
89 --
90 -- Rollback segment controlled by customers. No incremental commit will be performed
91 -- for such cases. It is user's responsibility to make sure the rollback
92 -- segment is big enough to handle the transaction
93 -- However, if fails to transaction rollback segment, then default rollback segment
94 -- will still be used with incremental commits.
95 --
96
97 IF (p_rollback_segment IS NOT NULL) THEN
98 PURGE_ERRORS('Setting rollback segment to '||p_rollback_segment);
99 -- IF FAILS, ON EXCEPTION, FLAG WLLL BE SET FALSE
100 COMMIT; -- we have to clean up the current transaction.
101 l_stmt := 'SET TRANSACTION USE ROLLBACK SEGMENT '||p_rollback_segment;
102 EXECUTE IMMEDIATE l_STMT;
103
104 -- SET TRANSACTION USE ROLLBACK SEGMENT :p_rollback_segment;
105 PURGE_ERRORS(p_rollback_segment || ' rollback segment will be used!');
106 l_result := TRUE;
107 ELSE
108 PURGE_ERRORS('Default rollback segment will be used');
109 END IF;
110 RETURN(l_result);
111 EXCEPTION
112 WHEN OTHERS THEN
113 PURGE_ERRORS('Default rollback segment will be used');
114 PURGE_ERRORS(SUBSTR(SQLERRM, 1, 512));
115 RETURN(FALSE);
116 END USER_ROLLBACK_CTRL;
117
118 PROCEDURE XDP_CHECK_ORDER(
119 p_order_id IN VARCHAR2 DEFAULT NULL
120 ) IS
121
122 l_no_records NUMBER := 0;
123 l_counter NUMBER := 0;
124 l_temp NUMBER := 0;
125 l_order_exist BOOLEAN := FALSE;
126 l_wi_type WF_ITEMS.ITEM_TYPE%TYPE;
127 l_wi_key WF_ITEMS.ITEM_KEY%TYPE;
128
129 BEGIN
130 IF p_order_id IS NULL THEN
131 -- CHECK IF THERE ARE ANY RECORDS IN ANY TABLE THAT HAVE
132 -- ORDER_IDS THAT DO NOT EXSIT IN XDP_ORDER_HEADER TABLE
133
134 RETURN;
135 END IF;
136
137 SELECT COUNT(*) INTO l_no_records FROM XDP_ORDER_HEADERS
138 WHERE ORDER_ID = p_order_id;
139
140 RECORDS_PURGED_MSGS(l_no_records,'XDP_ORDER_HEADERS WHERE ORDER_ID = '||p_order_id,
141 'XDP_FOUND_REC',0);
142
143 IF l_no_records > 0 THEN
144 l_order_exist := TRUE;
145 END IF;
146
147 SELECT COUNT(*) INTO l_no_records FROM XDP_ORDER_RELATIONSHIPS
148 WHERE ORDER_ID = p_order_id;
149
150 RECORDS_PURGED_MSGS(l_no_records ,'XDP_ORDER_RELATIONSHIPS WHERE ORDER_ID = '||p_order_id,
151 'XDP_FOUND_REC',0);
152
153 SELECT COUNT(*) INTO l_no_records FROM XNP_MSGS
154 WHERE ORDER_ID = p_order_id;
155
156 RECORDS_PURGED_MSGS(l_no_records,'XNP_MSGS WHERE ORDER_ID = '||p_order_id,
157 'XDP_FOUND_REC',0);
158
159 SELECT COUNT(*) INTO l_no_records FROM XDP_ORDER_PARAMETERS
160 WHERE ORDER_ID = p_order_id;
161
162 RECORDS_PURGED_MSGS(l_no_records,'XDP_ORDER_PARAMETERS WHERE ORDER_ID = '||p_order_id,
163 'XDP_FOUND_REC',0);
164
165 SELECT COUNT(*) INTO l_no_records FROM XDP_ORDER_BUNDLES
166 WHERE ORDER_ID = p_order_id;
167
168 RECORDS_PURGED_MSGS(l_no_records,'XDP_ORDER_BUNDLES WHERE ORDER_ID = '||p_order_id,
169 'XDP_FOUND_REC',0);
170
171 SELECT COUNT(*) INTO l_no_records FROM XNP_CALLBACK_EVENTS
172 WHERE ORDER_ID = p_order_id;
173 RECORDS_PURGED_MSGS(l_no_records,'XNP_CALLBACK_EVENTS WHERE ORDER_ID = '||p_order_id,
174 'XDP_FOUND_REC',0);
175
176 SELECT COUNT(*) INTO l_no_records FROM XNP_TIMER_REGISTRY
177 WHERE ORDER_ID = p_order_id;
178 RECORDS_PURGED_MSGS(l_no_records,'XNP_TIMER_REGISTRY WHERE ORDER_ID = '||p_order_id,
179 'XDP_FOUND_REC',0);
180
181 SELECT COUNT(*) INTO l_no_records FROM XNP_SYNC_REGISTRATION
182 WHERE ORDER_ID = p_order_id;
183 RECORDS_PURGED_MSGS(l_no_records,'XNP_SYNC_REGISTRATION WHERE ORDER_ID = '||p_order_id,
184 'XDP_FOUND_REC',0);
185
186 IF l_order_exist THEN
187 SELECT wf_item_type,wf_item_key INTO l_wi_type,l_wi_key
188 FROM XDP_ORDER_HEADERS
189 WHERE ORDER_ID = p_order_id;
190
191 SELECT count(*) INTO l_no_records
192 FROM WF_ITEMS
193 START WITH item_type = l_wi_type AND item_key = l_wi_key
194 CONNECT BY PRIOR item_key = parent_item_key
195 AND PRIOR item_type = parent_item_type;
196 RECORDS_PURGED_MSGS(l_no_records,'WF_ITEMS WHERE ORDER_ID = '||p_order_id,
197 'XDP_FOUND_REC',0);
198 END IF;
199
200 SELECT COUNT(*) INTO l_no_records FROM XDP_ORDER_LINE_ITEMS
201 WHERE ORDER_ID = p_order_id;
202
203 RECORDS_PURGED_MSGS(l_no_records,'XDP_ORDER_LINE_ITEMS WHERE ORDER_ID = '||p_order_id,
204 'XDP_FOUND_REC',0);
205
206 IF l_no_records <> 0 THEN
207 FOR c_item IN (SELECT LINE_ITEM_ID FROM XDP_ORDER_LINE_ITEMS
208 WHERE ORDER_ID = p_order_id)
209 LOOP
210 SELECT COUNT(*) INTO l_no_records FROM XDP_ORDER_LINEITEM_DETS
211 WHERE LINE_ITEM_ID = c_item.LINE_ITEM_ID;
212
213 RECORDS_PURGED_MSGS(l_no_records,'XDP_ORDER_LINEITEM_DETS WHERE LINE_ITEM_ID = '
214 ||c_item.LINE_ITEM_ID,'XDP_FOUND_REC',1);
215
216 SELECT COUNT(*) INTO l_no_records FROM XDP_LINE_RELATIONSHIPS
217 WHERE LINE_ITEM_ID = c_item.LINE_ITEM_ID;
218
219 RECORDS_PURGED_MSGS(l_counter,'XDP_LINE_RELATIONSHIPS WHERE LINE_ITEM_ID = '
220 ||c_item.LINE_ITEM_ID,'XDP_FOUND_REC',1);
221
222 SELECT COUNT(*) INTO l_no_records FROM XDP_FULFILL_WORKLIST
223 WHERE LINE_ITEM_ID = c_item.LINE_ITEM_ID;
224
225 RECORDS_PURGED_MSGS(l_counter,'XDP_FULFILL_WORKLIST WHERE LINE_ITEM_ID = '
226 ||c_item.LINE_ITEM_ID,'XDP_FOUND_REC',1);
227
228 IF l_no_records <> 0 THEN
229 FOR c_fw IN (SELECT WORKITEM_INSTANCE_ID FROM XDP_FULFILL_WORKLIST
230 WHERE LINE_ITEM_ID = C_ITEM.LINE_ITEM_ID)
231 LOOP
232 SELECT COUNT(*) INTO l_temp FROM XDP_WI_RELATIONSHIPS
233 WHERE WORKITEM_INSTANCE_ID = c_fw.WORKITEM_INSTANCE_ID;
234
235 RECORDS_PURGED_MSGS(l_counter,'XDP_WI_RELATIONSHIPS '||
236 'WHERE WORKITEM_INSTANCE_ID = '
237 ||c_fw.WORKITEM_INSTANCE_ID,'XDP_FOUND_REC',2);
238
239 SELECT COUNT(*) INTO l_temp FROM XDP_WORKLIST_DETAILS
240 WHERE WORKITEM_INSTANCE_ID = c_fw.WORKITEM_INSTANCE_ID;
241
242 RECORDS_PURGED_MSGS(l_counter,'XDP_WORKLIST_DETAILS '||
243 'WHERE WORKITEM_INSTANCE_ID = '
244 ||c_fw.WORKITEM_INSTANCE_ID,'XDP_FOUND_REC',2);
245
246 SELECT COUNT(*) INTO l_temp FROM XDP_FA_RUNTIME_LIST
247 WHERE WORKITEM_INSTANCE_ID = c_fw.WORKITEM_INSTANCE_ID;
248
249 RECORDS_PURGED_MSGS(l_counter,'XDP_FA_RUNTIME_LIST '||
250 'WHERE WORKITEM_INSTANCE_ID = '
251 ||c_fw.WORKITEM_INSTANCE_ID,'XDP_FOUND_REC',2);
252
253 IF l_temp <> 0 THEN
254 FOR c_fa IN (SELECT FA_INSTANCE_ID FROM XDP_FA_RUNTIME_LIST
255 WHERE WORKITEM_INSTANCE_ID = c_fw.WORKITEM_INSTANCE_ID)
256 LOOP
257 SELECT COUNT(*) INTO l_temp FROM XDP_FA_DETAILS WHERE
258 FA_INSTANCE_ID = c_fa.FA_INSTANCE_ID;
259
260 RECORDS_PURGED_MSGS(l_counter,'XDP_FA_DETAILS WHERE FA_INSTANCE_ID = '
261 ||c_fa.FA_INSTANCE_ID,'XDP_FOUND_REC',3);
262 SELECT COUNT(*) INTO l_temp FROM XDP_FE_CMD_AUD_TRAILS
263 WHERE FA_INSTANCE_ID = c_fa.FA_INSTANCE_ID;
264
265 RECORDS_PURGED_MSGS(l_counter,'XDP_FE_CMD_AUD_TRAILS WHERE FA_INSTANCE_ID = '
266 ||c_fa.FA_INSTANCE_ID,'XDP_FOUND_REC',3);
267 END LOOP;
268 END IF;
269
270 SELECT COUNT(*) INTO l_temp FROM XDP_FMC_AUDIT_TRAILS
271 WHERE WORKITEM_INSTANCE_ID = c_fw.WORKITEM_INSTANCE_ID;
272
273 RECORDS_PURGED_MSGS(l_counter,'XDP_FMC_AUDIT_TRAILS '||
274 'WHERE WORKITEM_INSTANCE_ID = '
275 ||c_fw.WORKITEM_INSTANCE_ID,'XDP_FOUND_REC',2);
276
277 IF l_temp <> 0 THEN
278 SELECT COUNT(*) INTO l_temp FROM XDP_FMC_AUD_TRAIL_DETS WHERE FMC_ID IN
279 (SELECT FMC_ID FROM XDP_FMC_AUDIT_TRAILS
280 WHERE WORKITEM_INSTANCE_ID = c_fw.WORKITEM_INSTANCE_ID);
281
282 RECORDS_PURGED_MSGS(l_counter,'XDP_FMC_AUD_TRAIL_DETS '||
283 'WHERE WORKITEM_INSTANCE_ID = '
284 ||c_fw.WORKITEM_INSTANCE_ID,'XDP_FOUND_REC',3);
285 END IF;
286 END LOOP;
287 END IF;
288 END LOOP;
289 END IF;
290 END XDP_CHECK_ORDER;
291
292 PROCEDURE XDP_CHECK_SOA
293 (
294 p_sv_soa_id IN VARCHAR2 DEFAULT NULL,
295 p_time_from IN DATE DEFAULT NULL,
296 p_time_to IN DATE DEFAULT NULL
297 ) IS
298
299 l_no_records NUMBER :=0;
300
301 BEGIN
302
303 IF p_sv_soa_id IS NULL THEN
304 IF (p_time_from IS NULL) OR (p_time_to IS NULL) THEN
305 FND_MESSAGE.SET_NAME('XDP','XDP_PURGE_ERROR');
306 FND_MESSAGE.SET_TOKEN('OBJECT','SOA');
307 FND_MESSAGE.SET_TOKEN('CONDITION','XDP_CHECK_SOA');
308 PURGE_ERRORS(FND_MESSAGE.GET);
309 END IF;
310
311 FOR l_sv_soa IN (SELECT SV_SOA_ID FROM XNP_SV_SOA A, XNP_SV_STATUS_TYPES_B B
312 WHERE a.STATUS_TYPE_CODE = b.STATUS_TYPE_CODE
313 AND b.PHASE_INDICATOR ='OLD'
314 AND a.MODIFIED_DATE < p_time_to
315 AND a.MODIFIED_DATE > p_time_from)
316 LOOP
317 SELECT COUNT(*) INTO l_no_records FROM XNP_SV_SOA_JN
318 WHERE SV_SOA_ID = l_sv_soa.SV_SOA_ID;
319
320 RECORDS_PURGED_MSGS(l_no_records,'XNP_SV_SOA_JN WHERE SV_SOA_ID ='
321 ||l_sv_soa.SV_SOA_ID,'XDP_FOUND_REC',1);
322
323 SELECT COUNT(*) INTO l_no_records FROM XNP_SV_FAILURES
324 WHERE SV_SOA_ID = l_sv_soa.SV_SOA_ID;
325
326 RECORDS_PURGED_MSGS(l_no_records,'XNP_SV_FAILURES WHERE SV_SOA_ID ='
327 ||l_sv_soa.SV_SOA_ID,'XDP_FOUND_REC',1);
328
329 SELECT COUNT(*) INTO l_no_records FROM XNP_SV_EVENT_HISTORY
330 WHERE SV_SOA_ID = l_sv_soa.SV_SOA_ID;
331
332 RECORDS_PURGED_MSGS(l_no_records,'XNP_SV_EVENT_HISTORY WHERE SV_SOA_ID ='
333 ||l_sv_soa.SV_SOA_ID,'XDP_FOUND_REC',1);
334 END LOOP;
335 RETURN;
336 END IF;
337
338 SELECT COUNT(*) INTO l_no_records FROM XNP_SV_SOA WHERE SV_SOA_ID = p_sv_soa_id;
339 RECORDS_PURGED_MSGS(l_no_records,'XNP_SV_SOA WHERE SV_SOA_ID ='
340 ||p_sv_soa_id,'XDP_FOUND_REC',0);
341
342 SELECT COUNT(*) INTO l_no_records FROM XNP_SV_EVENT_HISTORY
343 WHERE SV_SOA_ID = p_sv_soa_id;
344 RECORDS_PURGED_MSGS(l_no_records,'XNP_SV_EVENT_HISTORY WHERE SV_SOA_ID ='
345 ||p_sv_soa_id,'XDP_FOUND_REC',0);
346 SELECT COUNT(*) INTO l_no_records FROM XNP_SV_FAILURES
347 WHERE SV_SOA_ID = p_sv_soa_id;
348 RECORDS_PURGED_MSGS(l_no_records,'XNP_SV_FAILURES WHERE SV_SOA_ID ='
349 ||p_sv_soa_id,'XDP_FOUND_REC',0);
350 SELECT COUNT(*) INTO l_no_records FROM XNP_SV_SOA_JN
351 WHERE SV_SOA_ID = p_sv_soa_id;
352 RECORDS_PURGED_MSGS(l_no_records,'XNP_SV_SOA_JN WHERE SV_SOA_ID ='
353 ||p_sv_soa_id,'XDP_FOUND_REC',0);
354 COMMIT;
355 EXCEPTION
356 WHEN OTHERS THEN
357 FND_MESSAGE.SET_NAME('XDP','XDP_PURGE_ERROR');
358 FND_MESSAGE.SET_TOKEN('OBJECT','SOA');
359 FND_MESSAGE.SET_TOKEN('CONDITION','XDP_CHECK_SOA');
360 PURGE_ERRORS(FND_MESSAGE.GET);
361 END XDP_CHECK_SOA;
362
363 -- Internal procedure for verifying if an order is deletable
364 -- Name IS_ORDER_DELETABLE
365 --
366
367 PROCEDURE IS_ORDER_DELETABLE(
368 p_order_id IN XDP_ORDER_HEADERS.ORDER_ID%TYPE,
369 p_date_from IN DATE,
370 p_date_to IN DATE,
371 p_order_deletable OUT NOCOPY VARCHAR2
372 ) IS
373 --
374 --The following cursor is to find all related orders in the heirachy
375 -- rooted from the order with id of p_order_id.
376 -- If any one of this related orders do not satisfy the given
377 -- purging conditions, then the current order is not deletable.
378 --
379 CURSOR c_order_relation(p_order_id VARCHAR2) IS
380 SELECT RELATED_ORDER_ID
381 FROM XDP_ORDER_RELATIONSHIPS
382 START WITH ORDER_ID = p_order_id
383 CONNECT BY PRIOR RELATED_ORDER_ID = ORDER_ID;
384
385 l_order_state XDP_ORDER_HEADERS.STATUS_CODE%TYPE;
386 --l_order_state XDP_ORDER_HEADERS.ORDER_ID%TYPE;
387 l_completion_date DATE;
388
389 BEGIN
390 p_order_deletable :='FALSE';
391 FOR l_order_relation IN c_order_relation(p_order_id) LOOP
392 --Date: 09 AUG 2006 Author: DPUTHIYE Bug #:5446335
393 --This SQL should consider CANCEL_PROVISIONING_DATE for cancelled orders as well.
394 --Dependencies: None.
395 /* SELECT status_code,
396 completion_date
397 INTO l_order_state,
398 l_completion_date
399 FROM XDP_ORDER_HEADERS
400 WHERE order_id = l_order_relation.related_order_id; */
401
402 SELECT status_code,
403 NVL(completion_date, CANCEL_PROVISIONING_DATE)
404 INTO l_order_state,
405 l_completion_date
406 FROM XDP_ORDER_HEADERS
407 WHERE order_id = l_order_relation.related_order_id;
408
409 IF (l_completion_date < p_date_from) OR
410 (l_completion_date > p_date_to) OR
411 (l_order_state <> 'SUCCESS') OR
412 (l_order_state <> 'SUCCESS_WITH_OVERRIDE') OR
413 (l_order_state <> 'CANCELED') OR
414 (l_order_state <> 'ABORTED')
415 THEN
416 RETURN; --return false
417 END IF;
418 END LOOP;
422 PURGE_ERRORS(SUBSTR(SQLERRM, 1, 512));
419 p_order_deletable :='TRUE';
420 EXCEPTION
421 WHEN OTHERS THEN
423 --SILENCE THE EXCEPTION, WILL NOT BE HANDLED
424 END IS_ORDER_DELETABLE;
425
426 -- Internal procedure for deleting fulfile worklist for a given line item
427 -- Name DELETE_FULFILL_WORKLIST
428
429 PROCEDURE DELETE_FULFILL_WORKLIST(
430 p_line_item_id IN XDP_ORDER_LINE_ITEMS.LINE_ITEM_ID%TYPE,
431 p_run_mode IN VARCHAR2
432 ) IS
433 CURSOR c_fulfill_worklist IS
434 SELECT WORKITEM_INSTANCE_ID FROM XDP_FULFILL_WORKLIST WHERE
435 line_item_id = p_line_item_id
436 FOR UPDATE NOWAIT;
437
438 CURSOR c_fulfill_action(p_workitem_instance_id XDP_FULFILL_WORKLIST.WORKITEM_INSTANCE_ID%TYPE) IS
439 SELECT FA_INSTANCE_ID FROM XDP_FA_RUNTIME_LIST
440 WHERE WORKITEM_INSTANCE_ID = p_workitem_instance_id
441 FOR UPDATE NOWAIT;
442
443 l_temp NUMBER := 0;
444 l_wi_counter NUMBER :=0;
445 l_rec_name VARCHAR2(16) := 'XDP_FOUND_REC';
446 BEGIN
447 IF(p_run_mode = 'PURGE') THEN
448 l_rec_name := 'XDP_PURGE_REC';
449 END IF;
450
451 FOR l_worklist IN c_fulfill_worklist LOOP
452 -- LOG PURGING INFORMATION
453 FOR l_fa IN c_fulfill_action(l_worklist.WORKITEM_INSTANCE_ID) LOOP
454 IF ((bAudit = 'TRUE') AND (p_run_mode = 'PURGE')) or (p_run_mode = 'VERIFY') THEN
455 SELECT COUNT(*) INTO l_temp FROM XDP_FA_DETAILS
456 WHERE FA_INSTANCE_ID = l_fa.FA_INSTANCE_ID;
457 RECORDS_PURGED_MSGS(l_temp,'XDP_FA_DETAILS WHERE FA_INSTANCE_ID = '
458 ||l_fa.FA_INSTANCE_ID,l_rec_name,1);
459
460 SELECT COUNT(*) INTO l_temp FROM XDP_FE_CMD_AUD_TRAILS
461 WHERE FA_INSTANCE_ID = l_fa.FA_INSTANCE_ID;
462 RECORDS_PURGED_MSGS(l_temp,'XDP_FE_CMD_AUD_TRAILS WHERE FA_INSTANCE_ID = '
463 ||l_fa.FA_INSTANCE_ID,l_rec_name,1);
464 END IF;
465
466 IF p_run_mode = 'PURGE' THEN
467 DELETE FROM XDP_FA_DETAILS WHERE FA_INSTANCE_ID = l_fa.FA_INSTANCE_ID;
468 DELETE FROM XDP_FE_CMD_AUD_TRAILS WHERE FA_INSTANCE_ID = l_fa.FA_INSTANCE_ID;
469 END IF;
470 END LOOP;
471
472 IF ((bAudit = 'TRUE') AND (p_run_mode = 'PURGE')) or (p_run_mode = 'VERIFY') THEN
473 SELECT COUNT(*) INTO l_temp FROM XDP_FMC_AUD_TRAIL_DETS
474 WHERE FMC_ID IN
475 (SELECT FMC_ID FROM XDP_FMC_AUDIT_TRAILS
476 WHERE WORKITEM_INSTANCE_ID
477 = l_worklist.WORKITEM_INSTANCE_ID);
478 RECORDS_PURGED_MSGS(l_temp ,'XDP_FE_CMD_AUD_TRAILS WHERE WORKITEM_INSTANCE_ID = '
479 ||l_worklist.WORKITEM_INSTANCE_ID,l_rec_name,1);
480
481 SELECT COUNT(*) INTO l_temp FROM XDP_FA_RUNTIME_LIST
482 WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;
483 RECORDS_PURGED_MSGS(l_temp, 'XDP_FA_RUNTIME_LIST WHERE WORKITEM_INSTANCE_ID = '
484 ||l_worklist.WORKITEM_INSTANCE_ID,l_rec_name,1);
485
486 SELECT COUNT(*) INTO l_temp FROM XDP_FMC_AUDIT_TRAILS
487 WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;
488 RECORDS_PURGED_MSGS(l_temp,'XDP_FMC_AUDIT_TRAILS WHERE WORKITEM_INSTANCE_ID = '
489 ||l_worklist.WORKITEM_INSTANCE_ID,l_rec_name,1);
490
491 SELECT COUNT(*) INTO l_temp FROM XDP_WI_RELATIONSHIPS
492 WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;
493 RECORDS_PURGED_MSGS(l_temp,'XDP_WI_RELATIONSHIPS WHERE WORKITEM_INSTANCE_ID = '
494 ||l_worklist.WORKITEM_INSTANCE_ID,l_rec_name,1);
495
496 SELECT COUNT(*) INTO l_temp FROM XDP_WORKLIST_DETAILS
497 WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;
498 RECORDS_PURGED_MSGS(l_temp,'XDP_WORKLIST_DETAILS WHERE WORKITEM_INSTANCE_ID = '
499 ||l_worklist.WORKITEM_INSTANCE_ID,l_rec_name,1);
500 END IF;
501 -- Purging if it is in run mode
502 IF p_run_mode = 'PURGE' THEN
503 DELETE FROM XDP_FMC_AUD_TRAIL_DETS
504 WHERE FMC_ID IN
505 (SELECT FMC_ID FROM XDP_FMC_AUDIT_TRAILS
506 WHERE WORKITEM_INSTANCE_ID
507 = l_worklist.WORKITEM_INSTANCE_ID);
508
509 DELETE FROM XDP_FA_RUNTIME_LIST
510 WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;
511
512 DELETE FROM XDP_FMC_AUDIT_TRAILS
513 WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;
514
515 DELETE FROM XDP_WI_RELATIONSHIPS
516 WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;
517
518 DELETE FROM XDP_WORKLIST_DETAILS
519 WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;
520
521 DELETE FROM XDP_FULFILL_WORKLIST
522 WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;
523 END IF;
524
525 l_wi_counter := l_wi_counter+1;
526
527 END LOOP;
528 RECORDS_PURGED_MSGS(l_wi_counter,'XDP_FULFILL_WORKLIST WHERE LINE_ITEM_ID = '
529 ||p_line_item_id,l_rec_name,0);
530 EXCEPTION
531 WHEN OTHERS THEN
532 PURGE_ERRORS(SUBSTR(SQLERRM, 1, 512),P_ABORT => TRUE);
533
534 END DELETE_FULFILL_WORKLIST;
535
536 -- Internal procedure for deleting line items for an order
537 -- Name DELETE_LINE_ITEMS
538 --
539
540 PROCEDURE DELETE_LINE_ITEMS
541 (
542 p_order_id IN XDP_ORDER_HEADERS.ORDER_ID%TYPE,
543 p_run_mode IN VARCHAR2
544 ) IS
545
546 CURSOR c_line_item IS
547 SELECT LINE_ITEM_ID
548 FROM XDP_ORDER_LINE_ITEMS
549 WHERE order_id = p_order_id
553 l_li_number NUMBER := 0;
550 FOR UPDATE OF LINE_ITEM_ID NOWAIT;
551
552 l_temp NUMBER := 0;
554 l_rec_name VARCHAR2(16) := 'XDP_FOUND_REC';
555
556 BEGIN
557 IF(p_run_mode = 'PURGE') THEN
558 l_rec_name := 'XDP_PURGE_REC';
559 END IF;
560
561 FOR l_line_item IN c_line_item LOOP
562 Delete_Fulfill_Worklist(l_line_item.line_item_id,p_run_mode);
563 IF ((bAudit = 'TRUE') AND (p_run_mode = 'PURGE')) or (p_run_mode = 'VERIFY') THEN
564
565 SELECT COUNT(*)
566 INTO l_temp
567 FROM XDP_ORDER_LINEITEM_DETS
568 WHERE line_item_id = l_line_item.line_item_id;
569
570 RECORDS_PURGED_MSGS(l_temp, 'XDP_ORDER_LINEITEM_DETS WHERE LINE_ITEM_ID = '
571 ||l_line_item.line_item_id,l_rec_name,1);
572
573 SELECT COUNT(*)
574 INTO l_temp
575 FROM XDP_LINE_RELATIONSHIPS
576 WHERE line_item_id = l_line_item.line_item_id;
577
578 RECORDS_PURGED_MSGS(l_temp,'XDP_LINE_RELATIONSHIPS WHERE LINE_ITEM_ID = '
579 ||l_line_item.line_item_id,l_rec_name,1);
580 END IF;
581
582 IF p_run_mode = 'PURGE' THEN
583
584 DELETE FROM XDP_ORDER_LINEITEM_DETS
585 WHERE line_item_id = l_line_item.line_item_id;
586
587 DELETE FROM XDP_LINE_RELATIONSHIPS
588 WHERE line_item_id = l_line_item.line_item_id;
589
590 DELETE FROM XDP_ORDER_LINE_ITEMS WHERE CURRENT OF c_line_item;
591 END IF;
592 l_li_number := l_li_number+1;
593 END LOOP;
594 RECORDS_PURGED_MSGS(l_li_number,'XDP_ORDER_LINE_ITEMS WHERE ORDER_ID = '
595 ||p_order_id,l_rec_name,0);
596 EXCEPTION
597 WHEN OTHERS THEN
598 PURGE_ERRORS(SUBSTR(SQLERRM, 1, 512),P_ABORT => TRUE);
599 END DELETE_LINE_ITEMS;
600
601 --
602 -- Internal procedure for deleting workflow items for an order
603 -- Name DELETE_WF_ITEMS
604 --
605 -- This procedure will purge all workflow items that are connected to
606 -- the work item defined by the
607 -- WF_ITEM_TYPE AND WF_ITEM_KEY
608 -- in XDP_ORDER_HEADER table where order_id = p_order_id
609 --
610 -- A cursor is defined against WF_ITEMS table using connect by statement
611 --
612 -- Code reviewers:
613 -- would this risk of purging work items that are not belong to XDP????
614 --
615 PROCEDURE DELETE_WF_ITEMS
616 (
617 p_order_id IN VARCHAR2,
618 p_run_mode IN VARCHAR2
619 ) IS
620 CURSOR c_wi(p_wi_type IN VARCHAR2,p_wi_key IN VARCHAR2) IS
621 SELECT level,item_type,item_key
622 FROM WF_ITEMS
623 START WITH item_type = p_wi_type
624 AND item_key = p_wi_key
625 CONNECT BY PRIOR item_key = parent_item_key
626 AND PRIOR item_type = parent_item_type;
627
628 l_wi_type XDP_ORDER_HEADERS.WF_ITEM_TYPE%TYPE;
629 l_wi_key XDP_ORDER_HEADERS.WF_ITEM_KEY%TYPE;
630 l_counter NUMBER := 0;
631 l_rec_name VARCHAR2(16) := 'XDP_FOUND_REC';
632 l_wi_name VARCHAR2(16) := 'XDP_FOUND_WI';
633
634 l_PType VARCHAR2(8);
635
636 l_mini_secs NUMBER := 0;
637
638 BEGIN
639 -- DO NOTHING IS FLAG IS FALSE
640 IF (G_PURGE_WORK_FLOW = 'FALSE') THEN
641 RETURN;
642 END IF;
643
644 IF(p_run_mode = 'PURGE') THEN
645 l_rec_name := 'XDP_PURGE_REC';
646 l_wi_name := 'XDP_PURGE_WI';
647 END IF;
648
649 IF p_order_id IS NULL THEN
650 RETURN;
651 END IF;
652
653 l_mini_secs := DBMS_UTILITY.get_time;
654
655 SELECT wf_item_type,
656 wf_item_key
657 INTO l_wi_type,l_wi_key
658 FROM XDP_ORDER_HEADERS
659 WHERE ORDER_ID = p_order_id;
660
661 IF SQL%NOTFOUND THEN
662 RETURN;
663 END IF;
664
665 -- Purge all work items assocaited with an order
666 FOR l_wi in c_wi(l_wi_type,l_wi_key) LOOP
667
668 IF ((bAudit = 'TRUE') AND (p_run_mode = 'PURGE')) or (p_run_mode = 'VERIFY') THEN
669 RECORDS_PURGED_MSGS(1, 'WORKFLOW ITEM = '
670 ||l_wi.item_type || l_wi.item_key,1);
671 END IF;
672
673 IF (p_run_mode = 'PURGE') AND (l_wi.item_key IS NOT NULL) THEN
674 SELECT persistence_type INTO l_PType
675 FROM wf_item_types
676 WHERE NAME = l_wi.item_type;
677
678 IF SQL%NOTFOUND THEN
679 RETURN; -- DO NOTHING
680 END IF;
681
682 wf_purge.persistence_type := l_PType;
683 WF_PURGE.TOTAL(l_wi.item_type, l_wi.item_key, SYSDATE,FALSE);
684 NULL;
685 END IF;
686 l_counter := l_counter + 1;
687 END LOOP;
688 RECORDS_PURGED_MSGS(l_counter, 'WF_ITEMS WHERE ORDER_ID = '
689 ||p_order_id,l_rec_name,0);
690 G_Total_WK_TIME := G_Total_WK_TIME + DBMS_UTILITY.get_time - l_mini_secs;
691 EXCEPTION
692 WHEN OTHERS THEN
693 PURGE_ERRORS(P_MSG=>SUBSTR(SQLERRM, 1, 512),P_ABORT => TRUE);
694 END DELETE_WF_ITEMS;
695
696 -- Procedure XDP_PURGE_ORDER
697 -- Purge order for a given id
698 --
699 -- IN:
700 -- p_order_id - order id to be purged
701 -- p_time_from - date from which data is purged
702 -- p_time_to - date to which data is purged
703 -- p_run_mode - specify run mode when this API is called.--
704 -- - 'PURGE', to purge data
708 -- - in the database should be purged or not
705 -- - 'VERIFY', to verify setting and print out data will be purged
706 -- - 'CHECK', to check transactional data in the database
707 -- p_order_purge_mode - indicate if messages whose orders still exist
709 -- x_purge_status - indicate if purge is success or not,
710 -- - SUCCESS the order is or to be purged
711 -- - FAIL the order is not , or not to be purged
712 -- - EXCEPTION Exception occured while purge this order
713 -- -
714
715 PROCEDURE XDP_PURGE_ORDER
716 (
717 p_order_id IN NUMBER,
718 p_time_from IN DATE,
719 p_time_to IN DATE,
720 p_run_mode IN VARCHAR2 DEFAULT 'VERIFY',
721 p_purge_msg IN BOOLEAN DEFAULT FALSE,
722 x_purge_status OUT NOCOPY VARCHAR2
723 ) IS
724 -- it might be a good idea to make this autonomous
725 -- so that it will share no rollback segment with the main
726 -- transaction context, hence, no persistent view
727 -- PRAGMA AUTONOMOUS_TRANSACTION
728 l_temp NUMBER := 0;
729 l_order_deletable VARCHAR2(6);
730 l_exception_counter NUMBER := 0;
731 l_user_rollback BOOLEAN := FALSE;
732 l_rec_name VARCHAR2(16) := 'XDP_FOUND_REC';
733 l_dummy number;
734
735 BEGIN
736 SavePoint Order_Rollback;
737 --
738 -- to be sure that the order can be deleted
739 -- On exceptions, FALSE will be returned, a message will be logged
740 -- and committed to database
741 --
742 IS_ORDER_DELETABLE(p_order_id,p_time_from,p_time_to,l_order_deletable);
743
744 IF p_run_mode ='CHECK' THEN
745 IF l_order_deletable = 'TRUE' THEN
746 XDP_CHECK_ORDER(p_order_id);
747 END IF;
748 ELSE
749
750 --
751 -- To delete the order
752 -- On exceptions, a message will be logged and committed to database
753 -- carries on to next order
754 --
755 IF l_order_deletable = 'TRUE' THEN
756 BEGIN
757
758 -- Lock the record for update
759
760 FND_MESSAGE.SET_NAME('XDP','XDP_PURGE_ORD');
761 FND_MESSAGE.SET_TOKEN('ORDER_ID',p_order_id);
762 PURGE_ERRORS(FND_MESSAGE.GET);
763
764 SELECT 1 INTO l_dummy FROM XDP_ORDER_HEADERS
765 WHERE order_id = p_order_id for update nowait;
766
767 DELETE_LINE_ITEMS(p_order_id,p_run_mode);
768 DELETE_WF_ITEMS(p_order_id,p_run_mode);
769
770 IF ((bAudit = 'TRUE') AND (p_run_mode = 'PURGE')) or (p_run_mode = 'VERIFY') THEN
771 SELECT COUNT(*) INTO l_temp FROM XDP_ORDER_BUNDLES
772 WHERE order_id = p_order_id;
773
774 RECORDS_PURGED_MSGS(l_temp,'XDP_ORDER_BUNDLES WHERE ORDER_ID = '
775 ||p_order_id,l_rec_name,0);
776 SELECT COUNT(*) INTO l_temp FROM XDP_ORDER_RELATIONSHIPS
777 WHERE order_id = p_order_id;
778 RECORDS_PURGED_MSGS(l_temp,'XDP_ORDER_RELATIONSHIPS WHERE ORDER_ID = '
779 ||p_order_id,l_rec_name,0);
780 SELECT COUNT(*) INTO l_temp FROM XDP_ORDER_PARAMETERS
781 WHERE order_id = p_order_id;
782 RECORDS_PURGED_MSGS(l_temp,'XDP_ORDER_PARAMETERS WHERE ORDER_ID = '
783 ||p_order_id,l_rec_name,0);
784
785 -- If p_purge_msg is true, purge both internal and external
786 -- messages associated with the order
787 -- else, only purge internal messages which are identified
788 -- the the protected flag in xnp_msg_type_b
789
790 IF p_purge_msg THEN
791 SELECT COUNT(*) INTO l_temp FROM XNP_MSGS
792 WHERE order_id = p_order_id;
793 ELSE
794 SELECT COUNT(*) INTO l_temp FROM XNP_MSGS
795 WHERE order_id = p_order_id
796 AND msg_code IN
797 (SELECT MSG_CODE FROM XNP_MSG_TYPES_B
798 WHERE PROTECTED_FLAG='Y');
799 END IF;
800
801 RECORDS_PURGED_MSGS(l_temp,'XNP_MSGS WHERE ORDER_ID = '||p_order_id
802 ,l_rec_name,0);
803 SELECT COUNT(*) INTO l_temp FROM XNP_CALLBACK_EVENTS
804 WHERE order_id = p_order_id;
805 RECORDS_PURGED_MSGS(l_temp,'XNP_CALLBACK_EVENTS WHERE ORDER_ID = '
806 ||p_order_id ,l_rec_name,0);
807 SELECT COUNT(*) INTO l_temp FROM XNP_TIMER_REGISTRY
808 WHERE order_id = p_order_id;
809 RECORDS_PURGED_MSGS(l_temp,'XNP_TIMER_REGISTRY WHERE ORDER_ID = '
810 ||p_order_id ,l_rec_name,0);
811 SELECT COUNT(*) INTO l_temp FROM XNP_SYNC_REGISTRATION
812 WHERE order_id = p_order_id;
813 RECORDS_PURGED_MSGS(l_temp,'XNP_SYNC_REGISTRATION WHERE ORDER_ID = '
814 ||p_order_id ,l_rec_name,0);
815 END IF;
816
817 IF p_run_mode = 'PURGE' THEN
818 DELETE FROM XDP_ORDER_BUNDLES
819 WHERE order_id = p_order_id;
820 DELETE FROM XDP_ORDER_RELATIONSHIPS
821 WHERE order_id = p_order_id;
822 DELETE FROM XDP_ORDER_PARAMETERS
823 WHERE order_id = p_order_id;
824 --
825 -- if this flag is true, we will delete all messages associated with this order
829 IF p_purge_msg THEN
826 -- else we will only delete internal messages that are associated with this order
827 -- This operation will not delete any messages whose order id field is null.
828 --
830 DELETE FROM XNP_MSGS
831 WHERE order_id = p_order_id;
832 ELSE
833 DELETE FROM XNP_MSGS
834 WHERE order_id = p_order_id
835 AND msg_code IN
836 (SELECT MSG_CODE FROM XNP_MSG_TYPES_B
837 WHERE PROTECTED_FLAG='Y');
838 END IF;
839
840 DELETE FROM XNP_CALLBACK_EVENTS
841 WHERE order_id = p_order_id;
842
843 DELETE FROM XNP_TIMER_REGISTRY
844 WHERE order_id = p_order_id;
845 DELETE FROM XNP_SYNC_REGISTRATION
846 WHERE order_id = p_order_id ;
847 DELETE FROM XDP_ORDER_HEADERS
848 WHERE order_id = p_order_id ;
849 -- COMMIT;
850 END IF;
851 EXCEPTION
852 WHEN OTHERS THEN
853 ROLLBACK TO Order_Rollback;
854 x_purge_status := 'EXCEPTION';
855 END;
856 END IF;
857 END IF ;
858
859 IF l_order_deletable = 'TRUE' THEN
860 x_purge_status := 'SUCCESS';
861 ELSE
862 x_purge_status := 'FAIL';
863 END IF;
864
865 EXCEPTION
866 WHEN OTHERS THEN
867 ROLLBACK TO Order_Rollback;
868 x_purge_status := 'EXCEPTION';
869 x_purge_status := 'FAIL';
870 FND_MESSAGE.SET_NAME('XDP','XDP_PURGE_ERROR');
871 FND_MESSAGE.SET_TOKEN('OBJECT','ORDER');
872 FND_MESSAGE.SET_TOKEN('CONDITION','');
873 PURGE_ERRORS(FND_MESSAGE.GET);
874 PURGE_ERRORS(SUBSTR(SQLERRM, 1, 512));
875 END XDP_PURGE_ORDER;
876
877 PROCEDURE XDP_PURGE_ORDERS
878 (
879 p_time_from IN DATE,
880 p_time_to IN DATE,
881 p_run_mode IN VARCHAR2 DEFAULT 'VERIFY',
882 p_purge_msg IN BOOLEAN DEFAULT FALSE,
883 p_rollback_segment IN VARCHAR2 DEFAULT NULL,
884 x_orders_purged OUT NOCOPY NUMBER
885 ) IS
886
887 --Date: 09 AUG 2006 Author: DPUTHIYE Bug #:5446335
888 --This SQL should consider CANCEL_PROVISIONING_DATE for cancelled orders as well.
889 --Dependencies: None.
890 /* CURSOR c_order(p_time_from IN DATE,p_time_to IN DATE) IS
891 SELECT ORDER_ID FROM XDP_ORDER_HEADERS
892 WHERE STATUS_CODE IN ('SUCCESS','SUCCESS_WITH_OVERRIDE','ABORTED','CANCELED')
893 AND COMPLETION_DATE < p_time_to
894 AND COMPLETION_DATE > p_time_from; */
895
896 CURSOR c_order(p_time_from IN DATE,p_time_to IN DATE) IS
897 SELECT ORDER_ID FROM XDP_ORDER_HEADERS
898 WHERE STATUS_CODE IN ('SUCCESS','SUCCESS_WITH_OVERRIDE','ABORTED','CANCELED')
899 AND NVL(COMPLETION_DATE, CANCEL_PROVISIONING_DATE) < p_time_to
900 AND NVL(COMPLETION_DATE, CANCEL_PROVISIONING_DATE) > p_time_from;
901
902 l_temp NUMBER := 0;
903
904 l_order_id XDP_ORDER_HEADERS.ORDER_ID%TYPE;
905
906 l_order_deletable VARCHAR2(6);
907 l_exception_counter NUMBER := 0;
908 l_user_rollback BOOLEAN := FALSE;
909 l_rec_name VARCHAR2(16) := 'XDP_FOUND_REC';
910
911 l_purge_status VARCHAR2(20);
912 l_counter NUMBER := 0;
913 --l_dummy NUMBER;
914 l_mini_secs NUMBER :=0;
915 BEGIN
916 PURGE_ERRORS('==== '||p_run_mode|| ' ORDERS ' ||' ===================');
917 IF(p_run_mode = 'PURGE') THEN
918 l_rec_name := 'XDP_PURGE_REC';
919 END IF;
920
921 IF p_run_mode ='CHECK' THEN
922 --Date: 09 AUG 2006 Author: DPUTHIYE Bug #:5446335
923 --This SQL should consider CANCEL_PROVISIONING_DATE for cancelled orders as well.
924 --Dependencies: None.
925 /* SELECT COUNT(*) INTO l_temp FROM XDP_ORDER_HEADERS
926 WHERE STATUS_CODE IN ('SUCCESS','SUCCESS_WITH_OVERRIDE','ABORTED','CANCELED')
927 AND COMPLETION_DATE < p_time_to
928 AND COMPLETION_DATE > p_time_from; */
929
930 SELECT COUNT(*) INTO l_temp FROM XDP_ORDER_HEADERS
931 WHERE STATUS_CODE IN ('SUCCESS','SUCCESS_WITH_OVERRIDE','ABORTED','CANCELED')
932 AND NVL(COMPLETION_DATE, CANCEL_PROVISIONING_DATE) < p_time_to
933 AND NVL(COMPLETION_DATE, CANCEL_PROVISIONING_DATE) > p_time_from;
934
935 IF l_temp = 0 THEN
936 XDP_CHECK_ORDER(NULL);
937 END IF;
938 END IF;
939
940 -- l_user_rollback := USER_ROLLBACK_CTRL(p_rollback_segment);
941
942 l_mini_secs := DBMS_UTILITY.get_time;
943 G_Total_WK_TIME := 0;
944 x_orders_purged :=0;
945 --
946 -- This loop is introduced so that when it is to purge orders,
947 -- we will close the select cursor after certain number of
948 -- deletions. The purpose of this method is to minize the impact of
949 -- the size of a rollback segment. Although c_order cursor is read only,
950 -- in order to maintain a persistent view, Oracle reads from rollback segment
951 -- if a block has been changed as the cursor is open.
952 --
953 <<OUTER_LOOP>>
954 LOOP
955 -- REOPEN IT AFTER COMMIT
956 l_counter := 0;
957
958 OPEN c_order(p_time_from,p_time_to);
959 <<INNER_LOOP>>
960 LOOP
961 -- open/reopen the cursor
962 FETCH c_order INTO l_order_id;
963
964 IF c_order%notfound THEN
965 EXIT OUTER_LOOP;
966 END IF;
967
968 XDP_PURGE_ORDER(
972 p_run_mode,
969 l_order_id,
970 p_time_from,
971 p_time_to,
973 p_purge_msg,
974 l_purge_status
975 );
976
977 IF l_purge_status = 'SUCCESS' THEN
978 x_orders_purged := x_orders_purged + 1;
979 -- Reset exception counter
980 l_exception_counter := 0;
981 ELSIF l_purge_status = 'EXCEPTION' THEN
982 l_exception_counter := l_exception_counter + 1;
983 FND_MESSAGE.SET_NAME('XDP','XDP_PURGE_ERROR');
984 FND_MESSAGE.SET_TOKEN('OBJECT','ORDER');
985 FND_MESSAGE.SET_TOKEN('CONDITION','order_id ='|| l_order_id);
986 PURGE_ERRORS(FND_MESSAGE.GET);
987 PURGE_ERRORS(SUBSTR(SQLERRM, 1, 512));
988
989 -- if exceptions continusly occurred for more than gl_max_exception
990 -- then bubble up the exception and get out
991 IF (l_exception_counter > g_max_exceptions) THEN
992 PURGE_ERRORS(FND_MESSAGE.GET,p_abort=>TRUE);
993 END IF;
994 END IF;
995
996 -- This only applies when it is to purge, where deletions have occured
997 IF (l_counter > g_max_rows_before_commit) AND (p_run_mode = 'PURGE') THEN
998 PURGE_ERRORS(l_counter || ' orders purged, will reopen the order cursor');
999 -- Reset counter and jump out the inner loop
1000 l_counter := 0;
1001 COMMIT;
1002 EXIT INNER_LOOP;
1003 END IF;
1004 l_counter := l_counter+1;
1005 END LOOP INNER_LOOP;
1006
1007 -- if the cursor is open at this pointer, close it
1008 IF (c_order%ISOPEN) THEN
1009 CLOSE c_order;
1010 END IF;
1011 END LOOP OUTER_LOOP;
1012 RECORDS_PURGED_MSGS(x_orders_purged,'XDP_ORDER_HEADERS',l_rec_name,-1);
1013 l_mini_secs := DBMS_UTILITY.get_time - l_mini_secs;
1014 PURGE_ERRORS('Total '||l_mini_secs/100 || ' seconds used to purge orders');
1015 PURGE_ERRORS((G_Total_WK_TIME/100) || ' seconds used to purge workflow items for these orders');
1016 COMMIT;
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 FND_MESSAGE.SET_NAME('XDP','XDP_PURGE_ERROR');
1020 FND_MESSAGE.SET_TOKEN('OBJECT','ORDER');
1021 FND_MESSAGE.SET_TOKEN('CONDITION','');
1022
1023 PURGE_ERRORS(FND_MESSAGE.GET,P_COMMIT => TRUE);
1024 PURGE_ERRORS(SUBSTR(SQLERRM, 1, 512));
1025 RAISE;
1026 END XDP_PURGE_ORDERS;
1027 --
1028 -- Procedure XDP_PRUGE_TIMER
1029 -- Purge runtime timers for a given message
1030 -- timers associated with orders are purged with orders
1031 --
1032 PROCEDURE XDP_PRUGE_TIMER(p_msg_id IN NUMBER) IS
1033 BEGIN
1034 DELETE FROM xnp_timer_registry WHERE timer_id = p_msg_id;
1035 END XDP_PRUGE_TIMER;
1036
1037 -- Procedure XDP_PURGE_MESSAGES
1038 -- Purge runtime messages from SFM
1039 --
1040 -- IN:
1041 -- p_time_from - date from which data is purged
1042 -- p_time_to - date to which data is purged
1043 -- p_run_mode - specify run mode when this API is called.--
1044 -- - 'PURGE', to purge data
1045 -- - 'VERIFY', to verify setting and print out data will be purged
1046 -- - 'CHECK', to check transactional data in the database
1047 -- p_order_purge_mode - indicate if messages whose orders still exist
1048 -- - in the database should be purged or not
1049
1050 -- The assumption here is that
1051 PROCEDURE XDP_PURGE_MESSAGES
1052 (
1053 p_time_from IN DATE,
1054 p_time_to IN DATE,
1055 p_run_mode IN VARCHAR2 DEFAULT 'PURGE',
1056 p_order_purge_mode IN BOOLEAN DEFAULT FALSE,
1057 p_rollback_segment IN VARCHAR2 DEFAULT NULL
1058 ) IS
1059
1060 -- delete all messages that are not associated with an order
1061 -- 03/30/2001. Modified. Replaced CREATION_DATE with MSG_CREATION_DATE. rnyberg
1062 CURSOR c_xnp_msgs_1 IS
1063 SELECT MSG_ID FROM XNP_MSGS M
1064 WHERE
1065 (M.MSG_STATUS IN ('PROCESSED','TIME_OUT'))
1066 AND MSG_CREATION_DATE < p_time_to
1067 AND MSG_CREATION_DATE > p_time_from
1068 FOR UPDATE OF MSG_ID NOWAIT;
1069
1070 -- 03/30/2001. Modified. Replaced CREATION_DATE with MSG_CREATION_DATE. rnyberg
1071 CURSOR c_xnp_msgs_2 IS
1072 SELECT MSG_ID FROM XNP_MSGS M
1073 WHERE
1074 (M.MSG_STATUS IN ('PROCESSED','TIME_OUT'))
1075 AND MSG_CREATION_DATE < p_time_to
1076 AND MSG_CREATION_DATE > p_time_from
1077 AND NOT Exists (SELECT ORDER_ID from xdp_order_headers where (order_id=m.order_id))
1078 FOR UPDATE OF MSG_ID NOWAIT;
1079
1080 l_xnp_msg XNP_MSGS.MSG_ID%TYPE;
1081
1082 l_counter NUMBER := 0;
1083 l_no_msgs NUMBER := 0;
1084 l_temp NUMBER := 0;
1085 l_user_rollback BOOLEAN := TRUE;
1086
1087 l_rec_name VARCHAR2(16) := 'XDP_FOUND_REC';
1088
1089 BEGIN
1090 PURGE_ERRORS('==== '||p_run_mode|| ' Messages ' ||' ===================');
1091 IF(p_run_mode = 'CHECK') THEN
1092 IF(P_ORDER_PURGE_MODE) THEN
1093 -- 03/30/2001. Modified. Replaced CREATION_DATE with MSG_CREATION_DATE. rnyberg
1094 SELECT COUNT(*) INTO l_no_msgs FROM XNP_MSGS
1095 WHERE
1096 MSG_STATUS IN ('PROCESSED','TIME_OUT')
1097 AND MSG_CREATION_DATE < p_time_to
1098 AND MSG_CREATION_DATE > p_time_from;
1099 ELSE
1100 SELECT COUNT(*) INTO l_no_msgs FROM XNP_MSGS M
1101 WHERE
1105 AND MSG_CREATION_DATE > p_time_from
1102 -- 03/30/2001. Modified. Replaced CREATION_DATE with MSG_CREATION_DATE. rnyberg
1103 MSG_STATUS IN ('PROCESSED','TIME_OUT')
1104 AND MSG_CREATION_DATE < p_time_to
1106 AND NOT Exists (SELECT ORDER_ID from xdp_order_headers where (order_id=m.order_id));
1107 END IF;
1108 RECORDS_PURGED_MSGS(l_no_msgs,'XNP_MSGS',l_rec_name,-1);
1109 RETURN;
1110 END IF;
1111
1112 IF(p_run_mode = 'PURGE') THEN
1113 l_rec_name := 'XDP_PURGE_REC';
1114 END IF;
1115
1116 l_user_rollback := USER_ROLLBACK_CTRL(p_rollback_segment);
1117 -- purge messages that are not associated with an order
1118 l_counter := 0;
1119 <<OUTER_XNP_MSG>>
1120 LOOP
1121 SAVEPOINT MSG_RLBK;
1122 -- REOPEN IT AFTER COMMIT
1123 IF(P_ORDER_PURGE_MODE) THEN
1124 OPEN c_xnp_msgs_1;
1125 ELSE
1126 OPEN c_xnp_msgs_2;
1127 END IF;
1128 <<INNER_XNP_MSG>>
1129 LOOP
1130 IF(P_ORDER_PURGE_MODE) THEN
1131 FETCH c_xnp_msgs_1 INTO l_xnp_msg;
1132 IF c_xnp_msgs_1%notfound THEN
1133 EXIT OUTER_XNP_MSG; -- we are done
1134 END IF;
1135
1136 IF p_run_mode = 'PURGE' THEN
1137 XDP_PRUGE_TIMER(l_xnp_msg);
1138
1139 DELETE FROM XNP_MSGS WHERE CURRENT OF c_xnp_msgs_1;
1140 END IF;
1141 ELSE
1142 FETCH c_xnp_msgs_2 INTO l_xnp_msg;
1143 IF c_xnp_msgs_2%notfound THEN
1144 EXIT OUTER_XNP_MSG; -- we are done
1145 END IF;
1146
1147 IF p_run_mode = 'PURGE' THEN
1148 XDP_PRUGE_TIMER(l_xnp_msg);
1149 --
1150 DELETE FROM XNP_MSGS WHERE CURRENT OF c_xnp_msgs_2;
1151 END IF;
1152 END IF;
1153
1154 l_no_msgs := l_no_msgs + 1;
1155 l_counter := l_counter+1;
1156
1157 IF (l_counter > g_max_rows_before_commit) AND (NOT l_user_rollback) AND (p_run_mode = 'PURGE') THEN
1158 l_counter := 0;
1159 COMMIT;
1160 EXIT INNER_XNP_MSG; -- commit and reopen the cursor
1161 END IF;
1162
1163 END LOOP INNER_XNP_MSG;
1164
1165 IF(P_ORDER_PURGE_MODE) THEN
1166 IF (c_xnp_msgs_1%ISOPEN) THEN
1167 CLOSE c_xnp_msgs_1;
1168 END IF;
1169 ELSE
1170 IF (c_xnp_msgs_2%ISOPEN) THEN
1171 CLOSE c_xnp_msgs_2;
1172 END IF;
1173 END IF;
1174 END LOOP OUTER_XNP_MSG;
1175
1176 COMMIT;
1177 RECORDS_PURGED_MSGS(l_no_msgs,'XNP_MSGS',l_rec_name,-1);
1178 EXCEPTION
1179 WHEN OTHERS THEN
1180 ROLLBACK TO MSG_RLBK;
1181 FND_MESSAGE.SET_NAME('XDP','XDP_PURGE_ERROR');
1182 FND_MESSAGE.SET_TOKEN('OBJECT','MESSAGES');
1183 FND_MESSAGE.SET_TOKEN('CONDITION',SQLERRM);
1184 PURGE_ERRORS(FND_MESSAGE.GET,P_COMMIT => TRUE);
1185 RAISE;
1186 END XDP_PURGE_MESSAGES;
1187
1188 -- Procedure XDP_PURGE_SOA
1189 -- Purge soa data from SFM
1190 --
1191 -- IN:
1192 -- p_time_from - date from which data is purged
1193 -- p_time_to - date to which data is purged
1194 -- p_run_mode - specify run mode when this API is called.--
1195 -- - 'PURGE', to purge data
1196 -- - 'VERIFY', to verify setting and print out data will be purged
1197 -- - 'CHECK', to check transactional data in the database
1198
1199 PROCEDURE XDP_PURGE_SOA
1200 (
1201 p_time_from IN DATE,
1202 p_time_to IN DATE,
1203 p_run_mode IN VARCHAR2 DEFAULT 'VERIFY',
1204 p_rollback_segment IN VARCHAR2 DEFAULT NULL
1205 ) IS
1206 CURSOR c_xnp_soa(p_time_from IN DATE,p_time_to IN DATE) IS
1207 SELECT SV_SOA_ID FROM XNP_SV_SOA A, XNP_SV_STATUS_TYPES_B B
1208 WHERE a.STATUS_TYPE_CODE = b.STATUS_TYPE_CODE
1209 AND b.PHASE_INDICATOR ='OLD'
1210 AND a.MODIFIED_DATE < p_time_to
1211 AND a.MODIFIED_DATE > p_time_from FOR UPDATE OF A.SV_SOA_ID NOWAIT;
1212
1213 l_xnp_soa_id XNP_SV_SOA.SV_SOA_ID%TYPE;
1214 l_counter NUMBER := 0;
1215 l_no_msgs NUMBER := 0;
1216 l_temp NUMBER := 0;
1217 l_user_rollback BOOLEAN := TRUE;
1218 l_rec_name VARCHAR2(16) := 'XDP_FOUND_REC';
1219 BEGIN
1220 SAVEPOINT SOA_RBKS;
1221
1222 PURGE_ERRORS('==== '||p_run_mode|| ' SOA ' ||' ===================');
1223
1224 IF(p_run_mode = 'PURGE') THEN
1225 l_rec_name := 'XDP_PURGE_REC';
1226 END IF;
1227 IF(p_run_mode = 'PURGE') THEN
1228 l_rec_name := 'XDP_PURGE_REC';
1229 END IF;
1230
1231 IF p_run_mode ='CHECK' THEN
1232 SELECT COUNT(*) INTO l_temp FROM XNP_SV_SOA A, XNP_SV_STATUS_TYPES_B B
1233 WHERE a.STATUS_TYPE_CODE = b.STATUS_TYPE_CODE
1234 AND b.PHASE_INDICATOR ='OLD'
1235 AND a.MODIFIED_DATE < p_time_to
1236 AND a.MODIFIED_DATE > p_time_from;
1237
1238 IF l_temp = 0 THEN
1239 XDP_CHECK_SOA(NULL,p_time_from,p_time_to);
1240 RECORDS_PURGED_MSGS(l_temp,'XNP_SV_SOA',l_rec_name,0);
1241 RETURN;
1242 END IF;
1243 END IF;
1244
1245 l_user_rollback := USER_ROLLBACK_CTRL(p_rollback_segment);
1246
1247 FOR l_xnp_soa in c_xnp_soa(p_time_from,p_time_to) LOOP
1248 PURGE_ERRORS('==== '||p_run_mode||' SOA '||l_xnp_soa.sv_soa_id ||' ===================');
1249 IF p_run_mode = 'CHECK' THEN
1250 XDP_CHECK_SOA(l_xnp_soa.sv_soa_id);
1254 RECORDS_PURGED_MSGS(l_temp,'XNP_SV_EVENT_HISTORY WHERE SV_SOA_ID ='
1251 ELSE IF ((bAudit = 'TRUE') AND (p_run_mode = 'PURGE')) or (p_run_mode = 'VERIFY') THEN
1252 SELECT COUNT(*) INTO l_temp FROM XNP_SV_EVENT_HISTORY
1253 WHERE SV_SOA_ID = l_xnp_soa.sv_soa_id;
1255 ||l_xnp_soa.sv_soa_id,l_rec_name,0);
1256 SELECT COUNT(*) INTO l_temp FROM XNP_SV_FAILURES
1257 WHERE SV_SOA_ID = l_xnp_soa.sv_soa_id;
1258 RECORDS_PURGED_MSGS(l_temp,'XNP_SV_FAILURES WHERE SV_SOA_ID ='
1259 ||l_xnp_soa.sv_soa_id,l_rec_name,0);
1260 SELECT COUNT(*) INTO l_temp FROM XNP_SV_SOA_JN
1261 WHERE SV_SOA_ID = l_xnp_soa.sv_soa_id;
1262 RECORDS_PURGED_MSGS(l_temp,'XNP_SV_SOA_JN WHERE SV_SOA_ID ='
1263 ||l_xnp_soa.sv_soa_id,l_rec_name,0);
1264 END IF;
1265 END IF;
1266 END LOOP;
1267
1268 l_counter := 0;
1269 IF p_run_mode = 'PURGE' THEN
1270 <<OUTER_XNP_SOA>>
1271 LOOP
1272 -- REOPEN IT AFTER COMMIT
1273 SAVEPOINT SOA_RBKS;
1274 BEGIN
1275 OPEN c_xnp_soa(p_time_from,p_time_to);
1276 <<INNER_XNP_SOA>>
1277 LOOP
1278 FETCH c_xnp_soa INTO l_xnp_soa_id;
1279 -- IF NO DATA FOUND, THEN EXIT
1280 IF c_xnp_soa%notfound THEN
1281 EXIT OUTER_XNP_SOA; -- we are done
1282 END IF;
1283
1284 DELETE FROM XNP_SV_EVENT_HISTORY WHERE
1285 SV_SOA_ID = l_xnp_soa_id;
1286 DELETE FROM XNP_SV_FAILURES WHERE
1287 SV_SOA_ID = l_xnp_soa_id;
1288 DELETE FROM XNP_SV_SOA_JN WHERE
1289 SV_SOA_ID = l_xnp_soa_id;
1290 DELETE FROM XNP_SV_SOA WHERE CURRENT OF c_xnp_soa;
1291 l_no_msgs := l_no_msgs +1;
1292 l_counter := l_counter+1;
1293 IF (l_counter > g_max_rows_before_commit) AND (NOT l_user_rollback) THEN
1294 l_counter := 0;
1295 COMMIT;
1296 EXIT INNER_XNP_SOA; -- commit and reopen the cursor
1297 END IF;
1298 END LOOP INNER_XNP_SOA;
1299 IF (c_xnp_soa%ISOPEN) THEN
1300 CLOSE c_xnp_soa;
1301 END IF;
1302 EXCEPTION
1303 WHEN OTHERS THEN
1304 ROLLBACK TO SOA_RBKS;
1305 END;
1306 END LOOP OUTER_XNP_SOA;
1307 END IF;
1308 COMMIT;
1309
1310 RECORDS_PURGED_MSGS(l_no_msgs,'XNP_SV_SOA',l_rec_name,-1);
1311
1312 COMMIT;
1313 EXCEPTION
1314 WHEN OTHERS THEN
1315 ROLLBACK;
1316 FND_MESSAGE.SET_NAME('XDP','XDP_PURGE_ERROR');
1317 FND_MESSAGE.SET_TOKEN('OBJECT','SOA');
1318 FND_MESSAGE.SET_TOKEN('CONDITION','');
1319 PURGE_ERRORS(FND_MESSAGE.GET,P_COMMIT => TRUE);
1320 RAISE;
1321 END XDP_PURGE_SOA;
1322
1323 -- Procedure XDP_PURGE_MISC
1324 -- Purge debug, exception and misc workflow data from SFM
1325 --
1326 -- IN:
1327 -- p_time_from - date from which data is purged
1328 -- p_time_to - date to which data is purged
1329 -- p_run_mode - specify run mode when this API is called.
1330 -- -for 'TRUE', the program will purge data
1331 -- - for any other text, the program will only test
1332 -- - the purge and log affected data into log files.
1333 -- - if not specified, the program will purge data
1334
1335 PROCEDURE XDP_PURGE_MISC
1336 (
1337 p_time_from IN DATE,
1338 p_time_to IN DATE,
1339 p_run_mode IN VARCHAR2 DEFAULT 'VERIFY',
1340 p_rollback_segment IN VARCHAR2 DEFAULT NULL
1341 ) IS
1342
1343 CURSOR c_xdp_debug(p_time_from IN DATE,p_time_to IN DATE) IS
1344 SELECT DEBUG_TYPE FROM XDP_DEBUG
1345 WHERE LAST_UPDATE_DATE < p_time_to
1346 AND LAST_UPDATE_DATE > p_time_from FOR UPDATE OF DEBUG_TYPE NOWAIT;
1347
1348 CURSOR c_xdp_errors(p_time_from IN DATE,p_time_to IN DATE) IS
1349 SELECT ERROR_ID FROM XDP_ERROR_LOG
1350 WHERE LAST_UPDATE_DATE < p_time_to
1351 AND LAST_UPDATE_DATE > p_time_from FOR UPDATE OF ERROR_ID NOWAIT;
1352
1353
1354 CURSOR c_xnp_debug(p_time_from IN DATE,p_time_to IN DATE) IS
1355 SELECT DEBUG_ID FROM XNP_DEBUG
1356 WHERE LAST_UPDATE_DATE < p_time_to
1357 AND LAST_UPDATE_DATE > p_time_from FOR UPDATE OF DEBUG_ID NOWAIT;
1358
1359 l_xdp_debug_type XDP_DEBUG.DEBUG_TYPE%TYPE;
1360 l_xdp_error_id XDP_ERROR_LOG.ERROR_ID%TYPE;
1361 l_xnp_debug_id XNP_DEBUG.DEBUG_ID%TYPE;
1362
1363 l_counter NUMBER := 0;
1364 l_no_reords NUMBER := 0;
1365 l_user_rollback BOOLEAN := TRUE;
1366
1367 l_rec_name VARCHAR2(16) := 'XDP_FOUND_REC';
1368
1369 BEGIN
1370 PURGE_ERRORS('==== '||p_run_mode|| ' Misc ' ||' ===================');
1371
1372 IF(p_run_mode = 'PURGE') THEN
1373 l_rec_name := 'XDP_PURGE_REC';
1374 END IF;
1375
1376 SELECT COUNT(*) INTO l_no_reords FROM XDP_DEBUG
1377 WHERE LAST_UPDATE_DATE < p_time_to
1378 AND LAST_UPDATE_DATE > p_time_from;
1379
1380 RECORDS_PURGED_MSGS(l_no_reords,'XDP_DEBUG',l_rec_name,0);
1381 l_user_rollback := USER_ROLLBACK_CTRL(p_rollback_segment);
1382 IF p_run_mode = 'PURGE' THEN
1383 <<OUTER_LOOP>>
1384 LOOP
1385 -- REOPEN IT AFTER COMMIT
1386 OPEN c_xdp_debug(p_time_from,p_time_to);
1387 <<INNER_LOOP>>
1388 LOOP
1389 FETCH c_xdp_debug INTO l_xdp_debug_type;
1390
1391 -- IF NO DATA FOUND, THEN EXIT
1392 IF c_xdp_debug%notfound THEN
1393 EXIT OUTER_LOOP;
1394 END IF;
1395
1399 IF (l_counter > g_max_rows_before_commit) AND (NOT l_user_rollback) THEN
1396 DELETE FROM XDP_DEBUG WHERE CURRENT OF c_xdp_debug;
1397
1398 l_counter:=l_counter+1;
1400 l_counter := 0;
1401 COMMIT;
1402 EXIT INNER_LOOP;
1403 END IF;
1404
1405 END LOOP INNER_LOOP;
1406
1407 IF (c_xdp_debug%ISOPEN) THEN
1408 CLOSE c_xdp_debug;
1409 END IF;
1410 END LOOP OUTER_LOOP;
1411 END IF;
1412
1413 COMMIT;
1414
1415 SELECT COUNT(*) INTO l_no_reords FROM XDP_ERROR_LOG
1416 WHERE LAST_UPDATE_DATE < p_time_to
1417 AND LAST_UPDATE_DATE > p_time_from;
1418
1419 RECORDS_PURGED_MSGS(l_no_reords,'XDP_ERROR_LOG',l_rec_name,-1);
1420 l_user_rollback := USER_ROLLBACK_CTRL(p_rollback_segment);
1421 l_counter := 0;
1422
1423 IF p_run_mode = 'PURGE' THEN
1424 <<OUTER_ERROR>>
1425 LOOP
1426
1427 -- REOPEN IT AFTER COMMIT
1428 OPEN c_xdp_errors(p_time_from,p_time_to);
1429 <<INNER_ERROR>>
1430 LOOP
1431 FETCH c_xdp_errors INTO l_xdp_error_id;
1432 -- IF NO DATA FOUND, THEN EXIT
1433 IF c_xdp_errors%notfound THEN
1434 EXIT OUTER_ERROR;
1435 END IF;
1436
1437 DELETE FROM XDP_ERROR_LOG WHERE CURRENT OF c_xdp_errors;
1438 l_counter:=l_counter+1;
1439 IF (l_counter > g_max_rows_before_commit) AND (NOT l_user_rollback) THEN
1440 l_counter := 0;
1441 COMMIT;
1442 EXIT INNER_ERROR;
1443 END IF;
1444 END LOOP INNER_ERROR;
1445 IF (c_xdp_errors%ISOPEN) THEN
1446 CLOSE c_xdp_errors;
1447 END IF;
1448 END LOOP OUTER_ERROR;
1449 END IF;
1450 COMMIT;
1451
1452 l_user_rollback := USER_ROLLBACK_CTRL(p_rollback_segment);
1453
1454 SELECT COUNT(*) INTO l_no_reords FROM XNP_DEBUG
1455 WHERE LAST_UPDATE_DATE < p_time_to
1456 AND LAST_UPDATE_DATE > p_time_from;
1457
1458 RECORDS_PURGED_MSGS(l_no_reords,'XNP_DEBUG',l_rec_name,-1);
1459
1460 l_counter := 0;
1461 IF p_run_mode = 'PURGE' THEN
1462 <<OUTER_XNP_DEBUG>>
1463 LOOP
1464 -- REOPEN IT AFTER COMMIT
1465 OPEN c_xnp_debug(p_time_from,p_time_to);
1466 <<INNER_XNP_DEBUG>>
1467 LOOP
1468 FETCH c_xnp_debug INTO l_xnp_debug_id;
1469 -- IF NO DATA FOUND, THEN EXIT
1470 IF c_xnp_debug%notfound THEN
1471 EXIT OUTER_XNP_DEBUG; -- we are done
1472 END IF;
1473
1474 DELETE FROM XNP_DEBUG WHERE CURRENT OF c_xnp_debug;
1475 l_counter:=l_counter+1;
1476 IF (l_counter > g_max_rows_before_commit) AND (NOT l_user_rollback) THEN
1477 l_counter := 0;
1478 COMMIT;
1479 EXIT INNER_XNP_DEBUG; -- commit and reopen the cursor
1480 END IF;
1481 END LOOP INNER_XNP_DEBUG;
1482 IF (c_xnp_debug%ISOPEN) THEN
1483 CLOSE c_xnp_debug;
1484 END IF;
1485 END LOOP OUTER_XNP_DEBUG;
1486 END IF;
1487 COMMIT;
1488
1489 l_user_rollback := USER_ROLLBACK_CTRL(p_rollback_segment);
1490
1491 SELECT COUNT(*) INTO l_no_reords FROM WF_ITEMS
1492 WHERE ITEM_TYPE='XDPRECOV' AND END_DATE < p_time_to;
1493
1494 RECORDS_PURGED_MSGS(l_no_reords,'WF_ITEMS XDPRECOV',l_rec_name,-1);
1495
1496
1497 IF p_run_mode = 'PURGE' THEN
1498 IF G_PURGE_WORK_FLOW = 'TRUE' THEN
1499 SELECT persistence_type INTO WF_PURGE.PERSISTENCE_TYPE
1500 FROM wf_item_types WHERE NAME = 'XDPRECOV';
1501 WF_PURGE.TOTAL('XDPRECOV', NULL, p_time_to);
1502 END IF;
1503 END IF;
1504
1505 COMMIT;
1506 EXCEPTION
1507 WHEN OTHERS THEN
1508 ROLLBACK;
1509 FND_MESSAGE.SET_NAME('XDP','XDP_PURGE_ERROR');
1510 FND_MESSAGE.SET_TOKEN('OBJECT','MISC');
1511 PURGE_ERRORS(FND_MESSAGE.GET);
1512 RAISE;
1513 END XDP_PURGE_MISC;
1514
1515 -- Procedure PURGE
1516 -- Purge obsolete data from SFM
1517 --
1518 -- IN:
1519 -- p_number_of_days
1520 -- - number of days of data will be retained in SFM.
1521 -- - if not specified, g_min_number_of_days will be
1522 -- - used if specified as null, or negative or less
1523 -- - than g_min_number_of_days.
1524 -- p_run_mode - specify run mode when this API is called.--
1525 -- - 'PURGE', to purge data
1526 -- - 'VERIFY', to verify setting and print out data will be purged
1527 -- - 'CHECK', to check transactional data in the database
1528 -- p_purge_data_set
1529 -- - indicate what data to be purged,
1530 -- - eg. '[ORDER, SOA, MSGS, MISC]' will purge order, soa
1531 -- - , messages and debug/error data
1532 -- - '[ORDER]' will only purge order data
1533 -- - Default is null, means will not purge at all
1534 --
1535 -- p_purge_msg_flag
1536 -- - indicate if messages whose orders still exist
1537 -- - in the database should be purged or not
1538 --
1539 -- p_purge_order_flag
1540 -- - indicate if the external messages related to orders
1541 -- - will be purged
1542 --
1543 -- p_max_exceptions - number of continuous exceptions allowed before terminating a purge
1544 --
1545 -- p_log_mode - indicate if how you would like to log messages for
1546 -- - purging operation. Available option TERSE and VERBOSE
1547 -- -- any other words will result no message logged
1551 -- OUT:
1548 --
1549 -- p_rollback_segment - indicate what rollback segment should be used. If null, default
1550 -- - rollback segment will be used
1552 -- ERRBUF - as required by concurrent manager
1553 -- RETCODE - as required by concurrent manager
1554 --
1555 --
1556 -- Note: for the concurrent manager, exceptions will be silenced
1557 -- with proper messages returned in ERRBUF. RETCODE is 2 for
1558 -- exception errors
1559 --
1560
1561 PROCEDURE PURGE
1562 (
1563 ERRBUF OUT NOCOPY VARCHAR2,
1564 RETCODE OUT NOCOPY VARCHAR2,
1565 p_number_of_days IN NUMBER DEFAULT g_min_number_of_days,
1566 p_run_mode IN VARCHAR2 DEFAULT 'VERIFY',
1567 p_purge_data_set IN VARCHAR2 DEFAULT '[ORDER,SOA,MSGS,MISC]',
1568 p_purge_msg_flag IN VARCHAR2 DEFAULT 'TRUE',
1569 p_purge_order_flag IN VARCHAR2 DEFAULT 'TRUE',
1570 p_max_exceptions IN NUMBER DEFAULT 10,
1571 p_log_mode IN VARCHAR2 DEFAULT 'TERSE',
1572 p_rollback_segment IN VARCHAR2 DEFAULT NULL
1573 ) IS
1574
1575 l_time_to DATE;
1576 l_time_from DATE;
1577 l_purge_order_with_msg BOOLEAN;
1578 l_purge_msgs_regardless BOOLEAN;
1579 l_orders_purged NUMBER;
1580 l_rollback_segment VARCHAR2(200);
1581 BEGIN
1582 l_purge_order_with_msg := p_purge_order_flag = 'TRUE';
1583 l_purge_msgs_regardless := p_purge_msg_flag = 'TRUE';
1584 g_max_rows_before_commit := FND_PROFILE.VALUE('XDP_THRESHOLD_PURGE_TRANSACTION');
1585 IF (g_max_rows_before_commit IS NULL) THEN
1586 g_max_rows_before_commit := 5000;
1587 END IF;
1588
1589 IF (g_max_rows_before_commit < 1) THEN
1590 g_max_rows_before_commit := 10;
1591 END IF;
1592
1593 --
1594 -- In case value is assigned to null
1595 -- It is not likely that user would have a rollback segment called null
1596 --
1597 l_rollback_segment := p_rollback_segment;
1598 IF l_rollback_segment IS NOT NULL THEN
1599 IF l_rollback_segment = 'NULL' THEN
1600 l_rollback_segment := NULL;
1601 END IF;
1602 END IF;
1603 --
1604 -- This is a global value for this module. It is used to determine what information
1605 -- will be logged.
1606 --
1607 IF p_log_mode = 'TERSE' THEN
1608 g_debug_level := 1;
1609 ELSIF p_log_mode = 'VERBOSE' THEN
1610 g_debug_level := 4;
1611 ELSE
1612 g_debug_level := 0; -- Do not log anything
1613 END IF;
1614
1615 g_max_exceptions := p_max_exceptions;
1616
1617 --
1618 -- Only CHECK, PURGE or VERIFY are legal values for p_run_mode. If not, return an error
1619 --
1620 IF (p_run_mode <> 'CHECK') AND (p_run_mode <> 'PURGE') AND
1621 (p_run_mode <> 'VERIFY')
1622 THEN
1623 FND_MESSAGE.SET_NAME('XDP','XDP_PURGE_ERROR');
1624 FND_MESSAGE.SET_TOKEN('OBJECT','PURGE');
1625 FND_MESSAGE.SET_TOKEN('CONDITION','p_run_mode ='||p_run_mode);
1626 RETCODE := 2;
1627 ERRBUF := FND_MESSAGE.GET;
1628 PURGE_ERRORS(P_MSG => ERRBUF);
1629 RETURN;
1630 END IF;
1631 --
1632 -- For concurrent manager, do not use DBMS_OUTPUT
1633 -- Otherwise, use dbms_ouptut with one meg buffer
1634 -- This option is not available for customers.
1635 --
1636 IF (g_purge_method <> 'CM') THEN
1637 -- DBMS_OUTPUT.ENABLE(1000000);
1638 NULL;
1639 END IF;
1640
1641 -- set the from time to a very old date
1642 l_time_from := to_date('01-01-1900','mm-dd-yyyy');
1643
1644 IF (p_number_of_days IS NULL) OR
1645 (p_number_of_days < g_min_number_of_days )
1646 THEN
1647 l_time_to := SYSDATE - g_min_number_of_days;
1648 ELSE
1649 l_time_to := SYSDATE - p_number_of_days;
1650 END IF;
1651
1652 FND_MESSAGE.SET_NAME('XDP','XDP_PURGE_PROCESS');
1653
1654 --default time format will be used to convert time string
1655
1656 FND_MESSAGE.SET_TOKEN('DATE_FROM',TO_CHAR(l_time_from,'MM-DD-YYYY HH:MI:SS'));
1657 FND_MESSAGE.SET_TOKEN('DATE_TO',TO_CHAR(l_time_to,'MM-DD-YYYY HH:MI:SS'));
1658 PURGE_ERRORS(P_MSG=>FND_MESSAGE.GET);
1659 --
1660 -- By default, we will not manage workflow runtime data.
1661 -- There is a performance hit if this flag is turned on. It is
1662 -- better for performance to purge workflow using workflow purging
1663 -- utility than through this purge function. However, the option
1664 -- is here if that is required.
1665 --
1666 IF INSTR(p_purge_data_set,'ORDER_WITH_WF') <> 0 THEN
1667 G_PURGE_WORK_FLOW := 'TRUE';
1668 END IF;
1669
1670
1671 IF INSTR(p_purge_data_set,'ORDER') <> 0 THEN
1672 XDP_PURGE_ORDERS(l_time_from,l_time_to,p_run_mode,
1673 l_purge_order_with_msg,
1674 l_rollback_segment,l_orders_purged);
1675
1676 RECORDS_PURGED_MSGS(l_orders_purged,'XDP_ORDER_HEADERS','ORDER',-1);
1677 END IF;
1678
1679 IF INSTR(p_purge_data_set,'SOA') <> 0 THEN
1680 XDP_PURGE_SOA(l_time_from,l_time_to,p_run_mode,l_rollback_segment);
1681 END IF;
1682
1683 IF INSTR(p_purge_data_set,'MSG') <> 0 THEN
1684 XDP_PURGE_MESSAGES(
1685 l_time_from,l_time_to,p_run_mode,
1686 l_purge_msgs_regardless,
1687 l_rollback_segment);
1688 END IF;
1689
1690 IF INSTR(p_purge_data_set,'MISC') <> 0 THEN
1691 XDP_PURGE_MISC(l_time_from,l_time_to,p_run_mode,l_rollback_segment);
1692 END IF;
1693
1694 RETCODE := 0;
1695 ERRBUF := 'Success';
1696 COMMIT;
1697 EXCEPTION
1698 WHEN OTHERS THEN
1699 ROLLBACK;
1700 RETCODE := 2;
1701 FND_MESSAGE.SET_NAME('XDP','XDP_PURGE_ERROR');
1702 FND_MESSAGE.SET_TOKEN('OBJECT','PURGE');
1703 FND_MESSAGE.SET_TOKEN('CONDITION',SQLERRM);
1704 ERRBUF := FND_MESSAGE.GET;
1705 -- PURGE_ERRORS(P_MSG => ERRBUF,P_COMMIT => TRUE);
1706 END PURGE;
1707
1708 END XDP_PURGE;
1709
[Home] [Help]