[Home] [Help]
PACKAGE BODY: APPS.ICX_POR_BULK_LDR_PURGE
Source
1 PACKAGE BODY ICX_POR_BULK_LDR_PURGE AS
2 /* $Header: ICXBLKPB.pls 115.2 2004/03/31 18:43:51 vkartik ship $*/
3
4 /**
5 ** Proc : purge_bulk_ldr_tables
6 ** Desc : Purges Bulk Loader tables beyond the no of days specified.
7 **/
8 PROCEDURE purge_bulk_ldr_tables(p_no_of_days IN NUMBER DEFAULT 30,
9 p_commit_size IN NUMBER DEFAULT 2500)AS
10 gDeleteJobNumbers dbms_sql.number_table;
11 gcommit_size NUMBER := 0;
12 l_continue BOOLEAN := TRUE;
13 xErrloc INTEGER := 0;
14
15 BEGIN
16
17 xErrLoc := 100;
18
19 --Divide by 3 as deleting from three tables in one transaction
20 --Lower commit size will reduce the chances of Rollback Segment issues.
21 SELECT round(p_commit_size/3)
22 INTO gcommit_size
23 FROM dual;
24
25 WHILE l_continue LOOP
26 DELETE FROM icx_por_batch_jobs
27 WHERE submission_datetime <= (SYSDATE-p_no_of_days)
28 AND ROWNUM <= gcommit_size
29 RETURNING job_number BULK COLLECT INTO gDeleteJobNumbers;
30
31 xErrLoc := 200;
32
33 IF ( SQL%ROWCOUNT < gcommit_size ) THEN
34 l_continue := FALSE;
35 END IF;
36
37 xErrLoc := 300;
38
39 FORALL i IN 1..gDeleteJobNumbers.COUNT
40 DELETE FROM icx_por_failed_line_messages
41 WHERE job_number = gDeleteJobNumbers(i);
42
43 xErrLoc := 400;
44
45 FORALL i IN 1..gDeleteJobNumbers.COUNT
46 DELETE FROM icx_por_failed_lines
47 WHERE job_number = gDeleteJobNumbers(i);
48
49 xErrLoc := 500;
50
51 FORALL i IN 1..gDeleteJobNumbers.COUNT
52 DELETE FROM icx_por_contract_references
53 WHERE job_number = gDeleteJobNumbers(i);
54
55 xErrLoc := 600;
56
57 COMMIT;
58 END LOOP;
59
60 EXCEPTION
61 WHEN OTHERS THEN
62 ROLLBACK;
63 RAISE_APPLICATION_ERROR(-20000,
64 'Exception at icx_por_bulk_ldr_purge.purge_bulk_ldr_tables('
65 || xErrloc || '): ' ||SQLERRM);
66 END purge_bulk_ldr_tables;
67
68 END ICX_POR_BULK_LDR_PURGE;