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);
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:
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;
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.');
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:
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
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: ;