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