[Home] [Help]
PACKAGE BODY: APPS.JTF_FM_HISTORY_UTIL_PVT
Source
1 PACKAGE BODY JTF_FM_HISTORY_UTIL_PVT AS
2 /* $Header: jtfvfmhb.pls 120.12 2006/06/23 23:01:17 jakaur ship $*/
3 g_pkg_name CONSTANT VARCHAR2(30) := 'JTF_FM_HISTORY_PVT';
4
5
6 /*----------------------------------------------------------------------------
7 Forward declaration of private objects
8 -----------------------------------------------------------------------------*/
9 PROCEDURE delete_request_contents ( p_request_id IN NUMBER
10 , x_return_status OUT NOCOPY VARCHAR2
11 ) ;
12
13 PROCEDURE delete_processed ( p_request_id IN NUMBER
14 , x_return_status OUT NOCOPY VARCHAR2
15 ) ;
16
17 PROCEDURE delete_content_failures ( p_request_id IN NUMBER
18 , x_return_status OUT NOCOPY VARCHAR2
19 );
20
21 PROCEDURE delete_email_stats ( p_request_id IN NUMBER
22 , x_return_status OUT NOCOPY VARCHAR2
23 );
24
25 PROCEDURE delete_request_history ( p_rowid IN ROWID
26 , x_return_status OUT NOCOPY VARCHAR2
27 );
28 /*----------------------------------------------------------------------------
29 Forward declaration of private objects ends here
30 -----------------------------------------------------------------------------*/
31
32 --------------------------------------------------------------------------------
33 -- PROCEDURE
34 -- DELETE_HISTORY_RECORDS
35 --
36 -- PURPOSE
37 -- Delete a request history record from all the related tablers
38 --
39 -- PARAMETERS
40 -- request history id
41 --------------------------------------------------------------------------------
42 PROCEDURE DELETE_REQUEST_HISTORY(p_request_id IN NUMBER)
43 IS
44 BEGIN
45 DELETE FROM JTF_FM_PROCESSED WHERE REQUEST_ID = p_request_id;
46 DELETE FROM JTF_FM_REQUEST_CONTENTS WHERE REQUEST_ID = p_request_id;
47 DELETE FROM JTF_FM_REQUEST_HISTORY WHERE HIST_REQ_ID = p_request_id;
48
49 COMMIT WORK;
50
51 END DELETE_REQUEST_HISTORY;
52
53 --------------------------------------------------------------------------------
54 -- PROCEDURE
55 -- DELETE_HISTORY_RECORDS_BATCH
56 --
57 -- PURPOSE
58 -- Delete request history record from all the related tablers. All those records
59 -- which were processed prior to a given date should be deleted.
60 --
61 -- PARAMETERS
62 -- p_data_age
63 -- Number of days back from current date.
64 --------------------------------------------------------------------------------
65 PROCEDURE delete_request_history_batch ( x_error_buffer OUT NOCOPY VARCHAR2
66 , x_return_code OUT NOCOPY NUMBER
67 , p_data_age IN NUMBER
68 )
69 IS
70 CURSOR c_headers IS
71 SELECT hist_req_id request_id, ROWID, submit_dt_tm last_update_date
72 FROM jtf_fm_request_history_all
73 WHERE TRUNC(submit_dt_tm) <= TRUNC(SYSDATE) - p_data_age
74 ORDER BY hist_req_id ASC;
75 l_return_status VARCHAR2(1);
76 l_message VARCHAR2(4000);
77 e_error_found EXCEPTION;
78 BEGIN
79 l_message := 'Starting the purge program....';
80 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
81
82 l_message := 'Requests processed on or prior to '||TO_CHAR((SYSDATE - p_data_age), 'DD-MON-RRRR')||' will be purged.';
83 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
84
85 l_message := '';
86 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
87
88 FOR c_header_record IN c_headers
89 LOOP
90 FND_FILE.NEW_LINE(FND_FILE.LOG, 2);
91 l_message := 'Starting purging for request ID: '|| c_header_record.request_id ||' (Last Updated On: '|| TO_CHAR(c_header_record.last_update_date, 'DD-MON-RRRR') ||')';
92 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
93
94 BEGIN
95 SAVEPOINT before_delete;
96 delete_processed ( p_request_id => c_header_record.request_id
97 , x_return_status => l_return_status
98 );
99
100 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
101 THEN
102 RAISE e_error_found;
103 END IF;
104
105 delete_request_contents ( p_request_id => c_header_record.request_id
106 , x_return_status => l_return_status
107 );
108
109 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
110 THEN
111 RAISE e_error_found;
112 END IF;
113
114
115 delete_content_failures ( p_request_id => c_header_record.request_id
116 , x_return_status => l_return_status
117 );
118
119 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
120 THEN
121 RAISE e_error_found;
122 END IF;
123
124
125 delete_email_stats ( p_request_id => c_header_record.request_id
126 , x_return_status => l_return_status
127 );
128
129 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
130 THEN
131 RAISE e_error_found;
132 END IF;
133
134 delete_request_history ( p_rowid => c_header_record.rowid
135 , x_return_status => l_return_status
136 );
137
138 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
139 THEN
140 RAISE e_error_found;
141 END IF;
142
143
144 COMMIT;
145 EXCEPTION
146 WHEN e_error_found
147 THEN
148 ROLLBACK TO before_delete;
149 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
150 x_return_code := 2;
151 x_error_buffer := SQLERRM;
152 END;
153
154 l_message := 'Ending purging for request ID: '|| c_header_record.request_id;
155 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
156 END LOOP;
157
158 l_message := 'Purging funished.';
159 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
160 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
161
162
163 x_return_code := 0;
164 EXCEPTION
165 WHEN OTHERS
166 THEN
167 x_return_code := 2;
168 x_error_buffer := SQLERRM;
169 END delete_request_history_batch;
170
171 --------------------------------------------------------------------------------
172 -- PROCEDURE
173 -- DELETE_REQUEST_CONTENT
174 --
175 -- PURPOSE
176 -- Delete history record from jtf_fm_request_contents table for a given request ID.
177 --
178 -- PARAMETERS
179 -- p_request_id
180 -- Request ID for which the records should be deleted.
181 --------------------------------------------------------------------------------
182 PROCEDURE delete_request_contents ( p_request_id IN NUMBER
183 , x_return_status OUT NOCOPY VARCHAR2
184 )
185 IS
186 l_message VARCHAR2(4000);
187 l_count NUMBER DEFAULT 0;
188 BEGIN
189 DELETE
190 FROM jtf_fm_request_contents
191 WHERE request_id = p_request_id ;
192
193 l_count := SQL%ROWCOUNT;
194
195 l_message := RPAD('JTF_FM_REQUEST_CONTENTS table, records purged: ', 60, ' ') || l_count;
196 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
197
198 x_return_status := FND_API.G_RET_STS_SUCCESS;
199 EXCEPTION
200 WHEN OTHERS
201 THEN
202 x_return_status := FND_API.G_RET_STS_ERROR;
203 END delete_request_contents ;
204
205 --------------------------------------------------------------------------------
206 -- PROCEDURE
207 -- DELETE_PROCESSED
208 --
209 -- PURPOSE
210 -- Delete history record from jtf_fm_processed table for a given request ID.
211 --
212 -- PARAMETERS
213 -- p_request_id
214 -- Request ID for which the records should be deleted.
215 --------------------------------------------------------------------------------
216 PROCEDURE delete_processed ( p_request_id IN NUMBER
217 , x_return_status OUT NOCOPY VARCHAR2
218 )
219 IS
220 l_count NUMBER DEFAULT 0;
221 l_message VARCHAR2(4000);
222 BEGIN
223 DELETE
224 FROM jtf_fm_processed
225 WHERE request_id = p_request_id ;
226
227 l_count := SQL%ROWCOUNT;
228
229 l_message := RPAD('JTF_FM_PROCESSED table, records purged: ', 60, ' ') || l_count;
230 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
231
232 x_return_status := FND_API.G_RET_STS_SUCCESS;
233 EXCEPTION
234 WHEN OTHERS
235 THEN
236 x_return_status := FND_API.G_RET_STS_ERROR;
237 END delete_processed ;
238
239 --------------------------------------------------------------------------------
240 -- PROCEDURE
241 -- DELETE_CONTENT_FAILURES
242 --
243 -- PURPOSE
244 -- Delete history records from jtf_fm_content_failures table for a given request ID.
245 --
246 -- PARAMETERS
247 -- p_request_id
248 -- Request ID for which the records should be deleted.
249 --------------------------------------------------------------------------------
250 PROCEDURE delete_content_failures ( p_request_id IN NUMBER
251 , x_return_status OUT NOCOPY VARCHAR2
252 )
253 IS
254 l_count NUMBER DEFAULT 0;
255 l_message VARCHAR2(4000);
256 BEGIN
257 DELETE
258 FROM jtf_fm_content_failures
259 WHERE request_id = p_request_id ;
260
261 l_count := SQL%ROWCOUNT;
262
263 l_message := RPAD('JTF_FM_CONTENT_FAILURES table, records purged: ', 60, ' ') || l_count;
264 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
265
266 x_return_status := FND_API.G_RET_STS_SUCCESS;
267 EXCEPTION
268 WHEN OTHERS
269 THEN
270 x_return_status := FND_API.G_RET_STS_ERROR;
271 END delete_content_failures ;
272
273 --------------------------------------------------------------------------------
274 -- PROCEDURE
275 -- DELETE_EMAIL_STATS
276 --
277 -- PURPOSE
278 -- Delete history record from jtf_fm_email_stats table for a given request ID.
279 --
280 -- PARAMETERS
281 -- p_request_id
282 -- Request ID for which the records should be deleted.
283 --------------------------------------------------------------------------------
284 PROCEDURE delete_email_stats ( p_request_id IN NUMBER
285 , x_return_status OUT NOCOPY VARCHAR2
286 )
287 IS
288 l_count NUMBER DEFAULT 0;
289 l_message VARCHAR2(4000);
290 BEGIN
291 DELETE
292 FROM jtf_fm_email_stats
293 WHERE request_id = p_request_id ;
294
295 l_count := SQL%ROWCOUNT;
296
297 l_message := RPAD('JTF_FM_EMAIL_STAT table, records purged: ', 60, ' ') || l_count;
298 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
299
300 x_return_status := FND_API.G_RET_STS_SUCCESS;
301 EXCEPTION
302 WHEN OTHERS
303 THEN
304 x_return_status := FND_API.G_RET_STS_ERROR;
305 END delete_email_stats ;
306
307 --------------------------------------------------------------------------------
308 -- PROCEDURE
309 -- DELETE_REQUEST_HISTORY
310 --
311 -- PURPOSE
312 -- Delete history record from jtf_fm_request_history table for a given ROWID.
313 --
314 -- PARAMETERS
315 -- p_request_id
316 -- Request ID for which the records should be deleted.
317 --------------------------------------------------------------------------------
318 PROCEDURE delete_request_history ( p_rowid IN ROWID
319 , x_return_status OUT NOCOPY VARCHAR2
320 )
321 IS
322 l_count NUMBER DEFAULT 0;
323 l_message VARCHAR2(4000);
324 BEGIN
325 DELETE
326 FROM jtf_fm_request_history
327 WHERE ROWID = p_rowid;
328
329 l_count := SQL%ROWCOUNT;
330
331 l_message := RPAD('JTF_FM_REQUEST_HISTORY table, records purged: ', 60, ' ') || l_count;
332 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
333
334 x_return_status := FND_API.G_RET_STS_SUCCESS;
335 EXCEPTION
336 WHEN OTHERS
337 THEN
338 x_return_status := FND_API.G_RET_STS_ERROR;
339 END delete_request_history;
340
341 --------------------------------------------------------------------------------
342 -- PROCEDURE
343 -- PURGE_HISTORY_MGR
344 --
345 -- PURPOSE
346 -- The parent program that will start off the child threads for deletion.
347 -- Delete history record from various tables for a given data age.
348 --
349 -- PARAMETERS
350 -- p_data_age
351 -- Number of days for which the records should be deleted.
352 -- p_batch_size
353 -- Number of records after which a commit should be applied.
354 -- p_num_workers
355 -- Number of worker threads that should be started.
356 --------------------------------------------------------------------------------
357
358 PROCEDURE purge_history_mgr ( x_errbuf OUT NOCOPY VARCHAR2
359 , x_retcode OUT NOCOPY VARCHAR2
360 , p_data_age IN NUMBER
361 , p_batch_size IN NUMBER
365 l_retcode NUMBER;
362 , p_num_workers IN NUMBER
363 ) IS
364 l_errbuf VARCHAR2(32767);
366 l_count NUMBER := 0;
367 l_conc_request_id NUMBER;
368 l_children_done BOOLEAN;
369 BEGIN
370
371 -- updating the purge_flag
372 UPDATE jtf_fm_request_history_all
373 SET purge_flag = 'Y'
374 WHERE TRUNC(last_update_date) <= TRUNC(SYSDATE) - p_data_age
375 ;
376
377 COMMIT;
378
379 l_conc_request_id := FND_GLOBAL.conc_request_id();
380
381 ad_conc_utils_pkg.submit_subrequests( x_errbuf => l_errbuf
382 , x_retcode => l_retcode
383 , x_workerconc_app_shortname => 'JTF'
384 , x_workerconc_progname => 'JTF_FM_PURGE_REQ_WKR'
385 , x_batch_size => p_batch_size
386 , x_num_workers => p_num_workers
387 , x_argument4 => l_conc_request_id
388 );
389
390 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Starting purge program.....');
391 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Requests processed on or prior to '||TO_CHAR((SYSDATE - p_data_age), 'DD-MON-RRRR')||' will be purged.');
392 FND_FILE.NEW_LINE(FND_FILE.LOG, 2);
393
394 l_children_done := FND_CONCURRENT.children_done ( parent_request_id => l_conc_request_id
395 , recursive_flag => 'N'
396 , interval => 15
397 );
398
399 IF ((l_retcode <> ad_conc_utils_pkg.conc_fail) AND
400 (l_children_done))
401 THEN
402 DECLARE
403 CURSOR c_history_id IS
404 SELECT hist_req_id
405 FROM jtf_fm_request_history_all
406 WHERE purge_flag = 'Y'
407 ORDER BY hist_req_id ASC;
408 TYPE typ_request_id IS TABLE OF jtf_fm_request_history_all.hist_req_id%TYPE;
409 tab_request_id typ_request_id;
410 BEGIN
411 FND_FILE.NEW_LINE(FND_FILE.LOG, 2);
412 FND_FILE.PUT_LINE(FND_FILE.LOG, 'JTF_FM_PROCESSED table purged.');
413 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Starting purging of other child tables....');
414
415 OPEN c_history_id;
416 LOOP
417 FETCH c_history_id BULK COLLECT INTO tab_request_id LIMIT 1000;
418 FORALL i IN 1 .. tab_request_id.COUNT
419 DELETE
420 FROM jtf_fm_request_contents
421 WHERE request_id = tab_request_id(i)
422 ;
423
424 FORALL i IN 1 .. tab_request_id.COUNT
425 DELETE
426 FROM jtf_fm_content_failures
427 WHERE request_id = tab_request_id(i)
428 ;
429
430 FORALL i IN 1 .. tab_request_id.COUNT
431 DELETE
432 FROM jtf_fm_email_stats
433 WHERE request_id = tab_request_id(i)
434 ;
435
436 EXIT WHEN c_history_id%NOTFOUND;
437 COMMIT;
438 END LOOP;
439 CLOSE c_history_id;
440
441
442 DELETE
443 FROM jtf_fm_request_history_all
444 WHERE purge_flag = 'Y';
445
446 l_count := SQL%ROWCOUNT;
447
448 COMMIT;
449
450 DELETE
451 FROM jtf_fm_int_request_header
452 WHERE TRUNC(last_update_date) <= TRUNC(SYSDATE) - p_data_age
453 ;
454
455 COMMIT;
456
457 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
458
459 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number of Requests Purged: '|| l_count);
460
461 EXCEPTION
462 WHEN OTHERS
463 THEN
464 x_retcode := ad_conc_utils_pkg.conc_fail;
465 x_errbuf := SQLERRM;
466 RAISE;
467 END;
468 END IF;
469
470
471 x_retcode := ad_conc_utils_pkg.conc_success;
472
473 EXCEPTION
474 WHEN OTHERS
475 THEN
476 x_retcode := ad_conc_utils_pkg.conc_fail;
477 x_errbuf := SQLERRM;
478 RAISE;
479 END purge_history_mgr;
480
481 --------------------------------------------------------------------------------
482 -- PROCEDURE
483 -- PURGE_HISTORY_WKR
484 --
485 -- PURPOSE
486 -- The worker program that will delete ther rows from JTF_FM_PROCESSED
487 -- table based upon the ROWID (based upon LTU Processing).
488 --
489 -- PARAMETERS
490 -- x_batch_size
491 -- Number of records after which a commit should be applied.
492 -- x_worker_id
493 -- Unique identifier for a worker thread.
494 -- x_num_workers
495 -- Number of worker threads that should be started.
496 -- x_argument4
497 -- Concurrent program ID. Passed in by the parent thread.
498 --------------------------------------------------------------------------------
499 PROCEDURE purge_history_wkr ( x_errbuf OUT NOCOPY VARCHAR2
500 , x_retcode OUT NOCOPY VARCHAR2
501 , x_batch_size IN NUMBER
502 , x_worker_id IN NUMBER
503 , x_num_workers IN NUMBER
504 , x_argument4 IN VARCHAR2
505 ) IS
506 l_worker_id NUMBER;
507 l_product VARCHAR2(30) := 'JTF';
508 l_table_name VARCHAR2(30) := 'JTF_FM_PROCESSED';
509 l_update_name VARCHAR2(30);
510 l_status VARCHAR2(30);
511 l_industry VARCHAR2(30);
512 l_restatus BOOLEAN;
513 l_table_owner VARCHAR2(30);
514 l_any_rows_to_process BOOLEAN;
515 l_start_rowid ROWID;
516 l_end_rowid ROWID;
517 l_rows_processed NUMBER;
518 BEGIN
519 --
520 --Get schema name of the table for ROWID range processing
521 --
522
523 l_restatus := fnd_installation.get_app_info ( l_product, l_status, l_industry, l_table_owner );
524
525 IF (( l_restatus = FALSE ) OR
526 ( l_table_owner IS NULL))
527 THEN
528 RAISE_APPLICATION_ERROR(-20001, 'Cannot get schema name for product: '|| l_product );
529 END IF;
530
531 FND_FILE.PUT_LINE( FND_FILE.LOG, 'X_Worker_Id: '|| x_worker_id );
532 FND_FILE.PUT_LINE( FND_FILE.LOG, 'X_Num_Workers: '|| x_num_workers );
533
534 l_update_name := x_argument4;
535
536
537 --
538 -- Worker Processing
539 --
540
541 BEGIN
542 ad_parallel_updates_pkg.initialize_rowid_range
543 (
544 ad_parallel_updates_pkg.ROWID_RANGE
545 , l_table_owner
546 , l_table_name
547 , l_update_name
548 , x_worker_id
549 , x_num_workers
550 , x_batch_size
551 , 0
552 );
553
554 ad_parallel_updates_pkg.get_rowid_range
555 (
556 l_start_rowid
557 , l_end_rowid
558 , l_any_rows_to_process
559 , x_batch_size
560 , TRUE
561 );
562
563 WHILE ( l_any_rows_to_process = TRUE )
564 LOOP
565 DELETE /*+ rowid(jfp) */
566 FROM jtf_fm_processed jtp
567 WHERE request_id IN ( SELECT /*+ index(jrh, jtf.jtf_fm_request_history_all_nu2) */ hist_req_id
568 FROM jtf_fm_request_history_all jrh
569 WHERE purge_flag = 'Y'
570 )
571 AND ROWID BETWEEN l_start_rowid AND l_end_rowid
572 ;
573
574 l_rows_processed := SQL%ROWCOUNT;
575
576 ad_parallel_updates_pkg.processed_rowid_range
577 (
578 l_rows_processed
579 , l_end_rowid
580 );
581
582 COMMIT;
583
584 ad_parallel_updates_pkg.get_rowid_range
585 (
586 l_start_rowid
587 , l_end_rowid
588 , l_any_rows_to_process
589 , x_batch_size
590 , FALSE
591 );
592 END LOOP;
593
594 x_retcode := ad_conc_utils_pkg.conc_success;
595
596 EXCEPTION
597 WHEN OTHERS
598 THEN
599 x_retcode := ad_conc_utils_pkg.conc_fail;
600 x_errbuf := SQLERRM;
601 RAISE;
602 END;
603
604 EXCEPTION
605 WHEN OTHERS
606 THEN
607 x_retcode := ad_conc_utils_pkg.conc_fail;
608 x_errbuf := SQLERRM;
609 END purge_history_wkr;
610
611 END JTF_FM_HISTORY_UTIL_PVT;