[Home] [Help]
PACKAGE BODY: APPS.AMS_DEL_DISABLED_LIST_PVT
Source
1 PACKAGE BODY AMS_DEL_DISABLED_LIST_PVT AS
2 /* $Header: amsvdleb.pls 120.0 2011/01/28 11:01:22 sariff noship $ */
3
4 -- Start of Comments
5 -- Package name : AMS_DEL_DISABLED_LIST_PVT
6 -- Purpose : This package contains executable for AMS concurrent program to delete disabled list entries
7 -- Author : ANNSRINI
8 -- Created : 07-OCT-2010
9 -- History :
10 -- 07-OCT-2010 ANNSRINI - Creation
11 -- Modified by SARIFF
12 -- Modified on 02-DEC-2010
13 -- NOTE :
14 -- End of Comments
15
16 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_DEL_DISABLED_LIST_PVT';
17 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvdleb.pls';
18 G_CURSOR_LIMIT CONSTANT NUMBER := 100000;
19 G_NUM_REC CONSTANT NUMBER := 100000;
20
21
22 AMS_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
23 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
24 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
25 AMS_ERROR_ON boolean := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error);
26
27
28 -- Start of comments
29 -- API name : DEL_DISABLED_LIST_ENTRIES
30 -- Type : Private
31 -- Pre-reqs : None.
32 -- Function : CP Name : AMS : Delete Disabled List Entries
33 -- : CP Short Name: AMSDDLEPRG
34 -- : CP Executable Name: AMSDDLEEX
35 -- : CP Valueset: AMS_LIST
36 -- :
37 -- Parameters :
38 -- IN : p_list_name IN VARCHAR2
39 --
40 -- End of comments
41
42 PROCEDURE DEL_DISABLED_LIST_ENTRIES (errbuf OUT NOCOPY VARCHAR2,
43 retcode OUT NOCOPY VARCHAR2,
44 p_list_name IN VARCHAR2 )
45 IS
46
47 CURSOR c_list_entries_deleted IS
48 SELECT LIST_ENTRY_ROW_ID, LIST_HEADER_ID, ROWID PURGE_ROWID
49 FROM AMS_LIST_ENTRIES_PURGE;
50
51 TYPE TBL_ROWID_TYPE IS TABLE OF VARCHAR2(2000);
52 TYPE LIST_NUM_TYPE IS TABLE OF NUMBER;
53 TYPE LIST_REC_TYPE IS RECORD ( tbl_rowid TBL_ROWID_TYPE,
54 list_header_id LIST_NUM_TYPE,
55 purge_rowid TBL_ROWID_TYPE );
56 l_list_rec LIST_REC_TYPE;
57
58 l_api_name VARCHAR2(50) := 'Delete_Disabled_List_Entries';
59 l_api_version NUMBER := 1.0;
60 l_full_name VARCHAR2(100) := G_PKG_NAME ||'.'|| l_api_name;
61
62 l_schema VARCHAR2(32) := 'AMS';
63
64 l_cursor_limit NUMBER := G_CURSOR_LIMIT;
65 l_limit_flag BOOLEAN := FALSE;
66 l_flag BOOLEAN := TRUE;
67 l_first NUMBER := 0;
68 l_last NUMBER := 0;
69 l_loop_count NUMBER := 0;
70
71
72 BEGIN
73
74 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Start '|| l_full_name || '---');
75 FND_FILE.PUT_LINE(FND_FILE.LOG, 'list_header_id: ' || p_list_name);
76
77 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || l_schema || '.AMS_LIST_ENTRIES_PURGE');
78
79 INSERT INTO AMS_LIST_ENTRIES_PURGE (LIST_HEADER_ID,LIST_ENTRY_ROW_ID)
80 SELECT ale.list_header_id list_header_id, ale.rowid list_entry_row_id
81 FROM AMS_LIST_ENTRIES ale
82 WHERE ale.list_header_id = p_list_name
83 AND ale.enabled_flag = 'N' ;
84
85 COMMIT;
86
87 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Inserted into AMS_LIST_ENTRIES_PURGE ' );
88
89 -- Delete with incremental commit
90 LOOP
91
92 If (l_limit_flag) then EXIT; End If;
93
94 l_loop_count := l_loop_count + 1;
95
96 -- Open Cursor
97 BEGIN
98 FND_FILE.PUT_LINE(FND_FILE.LOG, ' OPEN cursor c_list_entries_deleted' );
99 OPEN c_list_entries_deleted;
100 FETCH c_list_entries_deleted
101 BULK COLLECT INTO l_list_rec.tbl_rowid,
102 l_list_rec.list_header_id,
103 l_list_rec.purge_rowid
104 LIMIT l_cursor_limit;
105 CLOSE c_list_entries_deleted;
106 EXCEPTION
107 when others then
108 IF c_list_entries_deleted%ISOPEN THEN
109 CLOSE c_list_entries_deleted;
110 END IF;
111 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Others Exception in OPEN c_list_entries_deleted ' );
112 errbuf := 'Error in cursor c_list_entries_deleted ' || SQLERRM;
113 retcode := 2;
114 RAISE;
115 END;
116
117 IF l_list_rec.list_header_id.count < l_cursor_limit THEN
118 l_limit_flag := TRUE;
119 END IF;
120
121 -- Delete from tables
122 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Delete list_entries from AMS_LIST_ENTRIES ' );
123 IF AMS_DEBUG_LOW_ON THEN
124 FND_FILE.PUT_LINE(FND_FILE.LOG, ' in loop 1, l_cursor_limit ' || l_cursor_limit );
125 FND_FILE.PUT_LINE(FND_FILE.LOG, ' in loop 1, l_list_rec.list_header_id.first ' || l_list_rec.list_header_id.first );
126 FND_FILE.PUT_LINE(FND_FILE.LOG, ' in loop 1, l_list_rec.list_header_id.last ' || l_list_rec.list_header_id.last );
127 FND_FILE.PUT_LINE(FND_FILE.LOG, ' in loop 1, l_list_rec.list_header_id.count ' || l_list_rec.list_header_id.count );
128 FND_FILE.PUT_LINE(FND_FILE.LOG, ' in loop 1, l_first ' || l_first );
129 FND_FILE.PUT_LINE(FND_FILE.LOG, ' in loop 1, l_last ' || l_last );
130 FND_FILE.PUT_LINE(FND_FILE.LOG, ' in loop 1, G_NUM_REC ' || G_NUM_REC );
131 END IF;
132
133 IF l_list_rec.list_header_id.count = 0 THEN
134 FND_FILE.PUT_LINE(FND_FILE.LOG, ' No entry for this list with enabled_flag as N ' );
135 ELSIF l_list_rec.list_header_id.count > 0 THEN
136 l_flag := TRUE;
137 l_first := l_list_rec.list_header_id.first;
138 l_last := l_first + G_NUM_REC;
139
140 WHILE l_flag LOOP
141 IF AMS_DEBUG_LOW_ON THEN
142 FND_FILE.PUT_LINE(FND_FILE.LOG, ' in loop 2 ' );
143 FND_FILE.PUT_LINE(FND_FILE.LOG, ' in loop 2, l_last ' || l_last );
144 FND_FILE.PUT_LINE(FND_FILE.LOG, ' in loop 2, l_list_rec.list_header_id.last ' || l_list_rec.list_header_id.last );
145 END IF;
146
147 IF l_last > l_list_rec.list_header_id.last THEN
148 l_last := l_list_rec.list_header_id.last;
149 END IF;
150
151 FORALL i in l_first..l_last
152 DELETE FROM AMS_LIST_ENTRIES
153 WHERE rowid = l_list_rec.tbl_rowid(i);
154 FORALL i in l_first..l_last
155 DELETE FROM AMS_LIST_ENTRIES_PURGE
156 WHERE rowid = l_list_rec.purge_rowid(i);
157 COMMIT;
158
159 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Records deleted: ' || l_first ||'-'|| l_last );
160
161 l_first := l_last + 1;
162 l_last := l_first + G_NUM_REC;
163 IF l_first > l_list_rec.list_header_id.last THEN
164 l_flag := FALSE;
165 END IF;
166 END LOOP;
167 END IF;
168 COMMIT ;
169 END LOOP;
170
171 EXCEPTION
172 WHEN others THEN
173 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Exception: others in DEL_DISABLED_LIST_ENTRIES ' );
174 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SQLCODE ' || to_char(SQLCODE) || ' SQLERRM ' || substr(SQLERRM, 1, 100));
175 errbuf := 'Error in proc DEL_DISABLED_LIST_ENTRIES ' || SQLERRM;
176 retcode := 2;
177 RAISE;
178
179 END DEL_DISABLED_LIST_ENTRIES;
180
181 END AMS_DEL_DISABLED_LIST_PVT;