PACKAGE BODY: APPS.FND_BC4J_CLEANUP_PKG

Source

1 PACKAGE BODY FND_BC4J_CLEANUP_PKG AS
2 /* $Header: FNDBCCLB.pls 115.5 2002/10/18 17:23:10 aviswana ship $ */
3 
4 PROCEDURE Delete_Transaction_Rows(p_older_than_date IN DATE) IS
5     h_counter		NUMBER := 0;
6     h_commit_level	NUMBER := 200;
7     CURSOR select_cursor IS
8 	SELECT rowid FROM fnd_ps_txn
9 	WHERE creation_date < p_older_than_date;
10 BEGIN
11     FOR rec IN select_cursor LOOP
12  	DELETE FROM fnd_ps_txn
13 	WHERE rowid = rec.rowid;
14 
15 	h_counter := h_counter + 1;
16 	IF (h_counter = h_commit_level) THEN
17 	    commit work;
18 	    h_counter := 0;
19         END IF;
20     END LOOP;
21 
22     IF (h_counter > 0) THEN
23 	commit work;
24     END IF;
25 
26 EXCEPTION
27     WHEN OTHERS THEN
28         rollback work;
29         fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
30         fnd_message.set_token('ERRNO', to_char(sqlcode));
31         fnd_message.set_token('REASON', sqlerrm);
32         fnd_message.set_token('ROUTINE',
33 		'FND_BC4J_CLEANUP.Delete_Transaction_Rows');
34         app_exception.raise_exception;
35 END Delete_Transaction_Rows;
36 
37 
38 PROCEDURE Delete_Control_Rows(p_older_than_date IN DATE) IS
39     h_counter		NUMBER := 0;
40     h_commit_level	NUMBER := 200;
41     CURSOR select_cursor IS
42 	SELECT rowid FROM fnd_pcoll_control
43 	WHERE updatedate < p_older_than_date;
44 BEGIN
45     FOR rec IN select_cursor LOOP
46  	DELETE FROM fnd_pcoll_control
47 	WHERE rowid = rec.rowid;
48 
49 	h_counter := h_counter + 1;
50 	IF (h_counter = h_commit_level) THEN
51 	    commit work;
52 	    h_counter := 0;
53         END IF;
54     END LOOP;
55 
56     IF (h_counter > 0) THEN
57 	commit work;
58     END IF;
59 
60 EXCEPTION
61     WHEN OTHERS THEN
62         rollback work;
63         fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
64         fnd_message.set_token('ERRNO', to_char(sqlcode));
65         fnd_message.set_token('REASON', sqlerrm);
66         fnd_message.set_token('ROUTINE',
67 		'FND_BC4J_CLEANUP.Delete_Control_Rows');
68         app_exception.raise_exception;
69 END Delete_Control_Rows;
70 
71 
72 end FND_BC4J_CLEANUP_PKG;