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;