[Home] [Help]
PACKAGE BODY: APPS.AS_TAP_PURGE_PUB
Source
1 PACKAGE BODY AS_TAP_PURGE_PUB as
2 /* $Header: asxtprgb.pls 120.6 2006/01/09 00:33:33 amagupta noship $ */
3
4 G_CURSOR_LIMIT CONSTANT NUMBER := 10000;
5 G_NUM_REC CONSTANT NUMBER := 10000;
6 G_ENTITY CONSTANT VARCHAR2(25) := 'PURGE TAP RECORDS';
7 PROCEDURE Purge_Access_Tables (
8 ERRBUF OUT NOCOPY VARCHAR2,
9 RETCODE OUT NOCOPY VARCHAR2,
10 p_debug_mode IN VARCHAR2,
11 p_trace_mode IN VARCHAR2
12 ) IS
13 CURSOR c_get_corrupt IS
14 SELECT /*+ INDEX_FFS(AS_TERRITORY_ACCESSES AS_TERRITORY_ACCESSES_U1) parallel_index(AS_TERRITORY_ACCESSES,AS_TERRITORY_ACCESSES_U1,5) parallel(AS_ACCESSES_U1,5) */
15 rowid,access_id
16 from AS_TERRITORY_ACCESSES
17 where not exists
18 (select 1
19 from AS_ACCESSES_ALL_ALL acc
20 where AS_TERRITORY_ACCESSES.access_id = acc.access_id)
21 union
22 SELECT /*+ INDEX_FFS(AS_TERRITORY_ACCESSES AS_TERRITORY_ACCESSES_U1)
23 parallel_index(AS_TERRITORY_ACCESSES,AS_TERRITORY_ACCESSES_U1,5)
24 parallel(AS_ACCESSES_U1,5) */
25 rowid,access_id
26 from AS_TERRITORY_ACCESSES
27 where not exists
28 (select 1
29 from JTF_TERR_ALL terr
30 where AS_TERRITORY_ACCESSES.TERRITORY_ID = terr.TERR_ID
31 and sysdate between terr.start_date_active and
32 terr.end_date_active) ;
33
34 TYPE TBL_ROWID_TYPE IS TABLE OF VARCHAR2(2000);
35 TYPE ACC_NUM_TYPE IS TABLE OF NUMBER;
36 TYPE TERRACC_REC_TYPE IS RECORD ( tbl_rowid TBL_ROWID_TYPE,
37 access_id ACC_NUM_TYPE );
38 l_terracc_rec TERRACC_REC_TYPE;
39
40 l_cursor_limit NUMBER := G_CURSOR_LIMIT;
41 l_limit_flag BOOLEAN := FALSE;
42 l_loop_count NUMBER := 0;
43 l_flag BOOLEAN := TRUE;
44 l_first NUMBER := 0;
45 l_last NUMBER := 0;
46 l_count NUMBER := 0;
47
48
49 l_MinNumParallelProc NUMBER;
50 l_NumChildWorker NUMBER;
51 l_ActualWorkers NUMBER;
52
53 l_req_id NUMBER;
54 l_msg VARCHAR2(2000);
55 l_prg_name VARCHAR2(100) :='ON_LINE_MODE';
56 l_status BOOLEAN;
57
58 BEGIN
59
60 IF p_trace_mode = 'Y' THEN AS_GAR.SETTRACE; END IF;
61 AS_GAR.g_debug_flag := p_debug_mode;
62
63 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_prg_name|| AS_GAR.G_START);
64
65 -- Get the cursor limit
66 begin
67 l_cursor_limit :=
68 nvl(to_number(fnd_profile.value('AS_TERR_RECORDS_TO_OPEN')),
69 G_CURSOR_LIMIT) ;
70 if l_cursor_limit < 1 then
71 l_cursor_limit := G_CURSOR_LIMIT;
72 end if;
73 exception
74 when others then
75 l_cursor_limit := G_CURSOR_LIMIT;
76 end;
77 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_prg_name || 'Cursor Limit'|| l_cursor_limit);
78 l_limit_flag := FALSE;
79 l_loop_count := 0;
80
81 -- Delete with incremental commit
82 LOOP
83 if (l_limit_flag) then EXIT; End If;
84 l_loop_count := l_loop_count + 1;
85
86 -- Open Cursor - get access_ids from working table
87 BEGIN
88 OPEN c_get_corrupt;
89 FETCH c_get_corrupt
90 BULK COLLECT INTO l_terracc_rec.tbl_rowid,
91 l_terracc_rec.access_id
92 LIMIT l_cursor_limit;
93 CLOSE c_get_corrupt;
94 EXCEPTION
95 WHEN OTHERS THEN
96 IF c_get_corrupt%ISOPEN THEN
97 CLOSE c_get_corrupt;
98 END IF;
99 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_prg_name || 'Others - OPEN c_get_corrupt');
100 RAISE;
101 END;
102
103 IF l_terracc_rec.access_id.count < l_cursor_limit THEN
104 l_limit_flag := TRUE;
105 END IF;
106
107 -- Delete from tables
108 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_prg_name || 'Deleting corrupted data in AS_TERRITORY_ACCESSES');
109 IF l_terracc_rec.access_id.count > 0 THEN
110 l_flag := TRUE;
111 l_first := l_terracc_rec.access_id.first;
112 l_last := l_first + G_NUM_REC;
113
114 WHILE l_flag LOOP
115 IF l_last > l_terracc_rec.access_id.last THEN
116 l_last := l_terracc_rec.access_id.last;
117 END IF;
118 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_prg_name || 'Records to be deleted: ' ||
119 l_terracc_rec.access_id.first || '-'
120 || l_terracc_rec.access_id.last);
121 FORALL i in l_first..l_last
122 DELETE FROM AS_TERRITORY_ACCESSES
123 WHERE rowid = l_terracc_rec.tbl_rowid(i);
124 COMMIT;
125 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_prg_name|| 'Records deleted: ' || l_first ||'-'|| l_last);
126 l_first := l_last + 1;
127 l_last := l_first + G_NUM_REC;
128 IF l_first > l_terracc_rec.access_id.last THEN
129 l_flag := FALSE;
130 END IF;
131 END LOOP;
132 END IF;
133 COMMIT;
134 END LOOP;
135
136
137 --
138 -- Spawn parallel working ASTPON
139 --
140 -- Prepare for parallel processing
141 select /*+ INDEX_FFS(ABC AS_ACCESSES_U2)*/ count(*) into l_count
142 from AS_ACCESSES_ALL_ALL ABC
143 where delete_flag='Y';
144
145 l_MinNumParallelProc :=
146 nvl(TO_NUMBER(fnd_profile.value('AS_TERR_MIN_NUM_PARALLEL_PROC')),100);
147 l_NumChildWorker :=
148 nvl(TO_NUMBER(fnd_profile.value('AS_TAP_PURGE_NUM_CHILD_WORKERS')),1);
149 IF l_NumChildWorker < 1 THEN
150 l_NumChildWorker := 1;
151 END IF;
152 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_prg_name || 'Min records for Parallel Processing: '||l_MinNumParallelProc);
153 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_prg_name || 'Max Parallel Workers: '||l_NumChildWorker);
154
155 Prepare_Parallel_Processing (
156 P_Count => l_count,
157 P_MinNumParallelProc => l_MinNumParallelProc,
158 P_NumChildWorker => l_NumChildWorker,
159 X_ActualWorkersUsed => l_ActualWorkers );
160
161
162 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_prg_name || 'Actual no. of Parallel Workers: '||l_ActualWorkers);
163
164 -- submit concurrent request ASTPON
165 FOR i in 1..l_ActualWorkers LOOP
166 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_prg_name || 'Submiting ASTPON -- worker: '||i);
167 l_req_id := FND_REQUEST.SUBMIT_REQUEST(
168 application => 'AS',
169 program => 'ASTPON',
170 start_time => '',
171 sub_request => FALSE,
172 argument1 => AS_GAR.g_debug_flag,
173 argument2 => p_trace_mode,
174 argument3 => i );
175
176 IF l_req_id = 0 THEN
177 l_msg:=FND_MESSAGE.GET;
178 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_prg_name || 'Error in Submittingb Request:' || l_msg);
179 END IF;
180 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_prg_name || 'Submitted Request:' || l_req_id);
181 END LOOP;
182 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_prg_name || AS_GAR.G_END);
183 EXCEPTION
184 WHEN OTHERS THEN
185 AS_GAR.LOG_EXCEPTION(G_ENTITY, SQLERRM, TO_CHAR(SQLCODE));
186 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
187
188 END Purge_Access_Tables;
189
190
191 PROCEDURE Prepare_Parallel_Processing(
192 P_Count IN NUMBER,
193 P_MinNumParallelProc IN NUMBER,
194 P_NumChildWorker IN NUMBER,
195 X_ActualWorkersUsed OUT NOCOPY NUMBER)
196 IS
197 l_st VARCHAR2(3000);
198 stmt VARCHAR2(8000);
199
200 l_ActualWorker NUMBER := 0;
201 l_WorkerLoad NUMBER := 0;
202 l_pkg_name VARCHAR2(100) := 'PREPARE_PARALLEL_PROCESSING';
203
204 l_fnd_status VARCHAR2(2);
205 l_industry VARCHAR2(2);
206 l_oracle_schema VARCHAR2(32) := 'OSM';
207 l_schema_return BOOLEAN;
208
209 BEGIN
210
211 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_pkg_name || AS_GAR.G_START);
212
213 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_pkg_name || 'TRUNCATE TABLE AS_TAP_PURGE_WORKING');
214
215 l_schema_return := FND_INSTALLATION.get_app_info('AS', l_fnd_status, l_industry, l_oracle_schema);
216
217 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.AS_TAP_PURGE_WORKING';
218
219 --
220 -- Populate the working table
221 --
222 X_ActualWorkersUsed := 0;
223 l_ActualWorker := 0;
224 l_WorkerLoad := 0;
225
226 l_WorkerLoad := CEIL(P_Count / P_NumChildWorker);
227 If l_WorkerLoad < P_MinNumParallelProc then
228 l_WorkerLoad := P_MinNumParallelProc;
229 End If;
230
231 l_ActualWorker := CEIL(P_Count/l_WorkerLoad);
232 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_pkg_name || 'ActualWorker: '||l_ActualWorker);
233
234
235 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_pkg_name || 'Inseting into AS_TAP_PURGE_WORKING');
236
237 commit;
238 EXECUTE IMMEDIATE 'alter session enable parallel dml';
239
240 stmt := 'INSERT /*+ append parallel(i) */ INTO as_tap_purge_working i
241 (ROW_ID,
242 WORKER_ID,
243 LAST_UPDATE_DATE,
244 LAST_UPDATED_BY,
245 CREATION_DATE,
246 CREATED_BY,
247 LAST_UPDATE_LOGIN)
248 SELECT /*+ PARALLEL_INDEX(AS_ACCESSES_ALL_ALL,5) INDEX_FFS(AS_ACCESSES_ALL_ALL AS_ACCESSES_U2)*/
249 ROWID,NTILE('||l_ActualWorker||') OVER(ORDER BY ROWID) as workerid, sysdate,
250 :1,
251 sysdate,
252 :2,
253 :3
254 FROM AS_ACCESSES_ALL_ALL where delete_flag = ''Y''';
255
256 EXECUTE IMMEDIATE stmt using NVL(to_number(fnd_profile.value('USER_ID')),0),NVL(to_number(fnd_profile.value('USER_ID')),0),
257 NVL(to_number(fnd_profile.value('CONC_LOGIN_ID')),0);
258 X_ActualWorkersUsed := l_ActualWorker;
259 commit;
260 EXECUTE IMMEDIATE 'alter session disable parallel dml';
261
262 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_pkg_name || AS_GAR.G_END);
263
264 END Prepare_Parallel_Processing;
265
266 PROCEDURE Delete_Access_Records (
267 ERRBUF OUT NOCOPY VARCHAR2,
268 RETCODE OUT NOCOPY VARCHAR2,
269 p_debug_mode IN VARCHAR2,
270 p_trace_mode IN VARCHAR2,
271 p_worker_id IN NUMBER )
272 IS
273 CURSOR c_get_del_access (c_worker_id number) IS
274 select row_id
275 from as_tap_purge_working
276 where worker_id = c_worker_id;
277
278 TYPE ACC_NUM_TYPE IS TABLE OF rowid;
279 TYPE ACC_REC_TYPE IS RECORD ( access_id ACC_NUM_TYPE);
280
281 l_acc_rec ACC_REC_TYPE;
282
283 TYPE ACC_NUM_TYPE1 IS TABLE OF NUMBER;
284 TYPE ACC_REC_TYPE1 IS RECORD ( access_id ACC_NUM_TYPE1);
285
286 l_acc_rec1 ACC_REC_TYPE1;
287
288 l_flag BOOLEAN := TRUE;
289 l_first NUMBER := 0;
290 l_last NUMBER := 0;
291 l_limit_flag BOOLEAN := FALSE;
292 l_cursor_limit NUMBER := G_CURSOR_LIMIT;
293 l_loop_count NUMBER := 0;
294
295 l_st VARCHAR2(3000);
296 l_status BOOLEAN;
297 l_pkg_name VARCHAR2(100) := 'DELETE_ACCESS_RECORDS';
298
299 BEGIN
300 IF p_trace_mode = 'Y' THEN AS_GAR.SETTRACE; END IF;
301
302 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_pkg_name|| AS_GAR.G_START);
303 -- Get the cursor limit
304 begin
305 l_cursor_limit :=
306 nvl(to_number(fnd_profile.value('AS_TERR_RECORDS_TO_OPEN')),
307 G_CURSOR_LIMIT) ;
308 if l_cursor_limit < 1 then
309 l_cursor_limit := G_CURSOR_LIMIT;
310 end if;
311 exception
312 when others then
313 l_cursor_limit := G_CURSOR_LIMIT;
314 end;
315 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_pkg_name || 'Cursor Limit: '||l_cursor_limit);
316
317 l_limit_flag := FALSE;
318 l_loop_count := 0;
319
320 -- Delete with incremental commit
321
322 -- Open Cursor - get access_ids from working table
323 begin
324 OPEN c_get_del_access (p_worker_id);
325
326 LOOP
327 IF (l_limit_flag) THEN EXIT; END IF;
328 l_loop_count := l_loop_count + 1;
329
330 FETCH c_get_del_access
331 BULK COLLECT INTO l_acc_rec.access_id
332 LIMIT l_cursor_limit;
333
334 IF l_acc_rec.access_id.count < l_cursor_limit THEN
335 l_limit_flag := TRUE;
336 END IF;
337
338 -- Delete from tables
339 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_pkg_name || '---DELETE FROM AS_ACCESSES_ALL_ALL::START');
340
341 IF l_acc_rec.access_id.count > 0 THEN
342 l_flag := TRUE;
343 l_first := l_acc_rec.access_id.first;
344 l_last := l_first + G_NUM_REC;
345
346 WHILE l_flag LOOP
347 IF l_last > l_acc_rec.access_id.last THEN
348 l_last := l_acc_rec.access_id.last;
349 END IF;
350 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_pkg_name ||
351 'Records to be deleted: ' ||
352 l_acc_rec.access_id.first || '-'
353 || l_acc_rec.access_id.last);
354
355 FORALL i in l_first..l_last
356 DELETE FROM AS_ACCESSES_ALL_ALL
357 WHERE rowid = l_acc_rec.access_id(i)
358 RETURNING access_id BULK COLLECT INTO l_acc_rec1.access_id;
359 COMMIT;
360
361 FORALL j in l_first..l_last
362 DELETE /*+ index(TERRACC AS_TERRITORY_ACCESSES_u1) */
363 FROM AS_TERRITORY_ACCESSES TERRACC
364 WHERE TERRACC.access_id = l_acc_rec1.access_id(j);
365 COMMIT;
366
367 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_pkg_name ||
368 'Records deleted: ' || l_first ||
369 '-'|| l_last);
370
371 l_first := l_last + 1;
372 l_last := l_first + G_NUM_REC;
373 IF l_first > l_acc_rec.access_id.last THEN
374 l_flag := FALSE;
375 END IF;
376 END LOOP;
377 END IF;
378 COMMIT;
379 END LOOP;
380
381 CLOSE c_get_del_access;
382 EXCEPTION
383 WHEN OTHERS THEN
384 IF c_get_del_access%ISOPEN THEN
385 CLOSE c_get_del_access;
386 END IF;
387 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_pkg_name ||
388 'Others - OPEN c_get_del_access');
389 RAISE;
390 END;
391 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || l_pkg_name || AS_GAR.G_END);
392 EXCEPTION
393 WHEN OTHERS THEN
394 AS_GAR.LOG_EXCEPTION(G_ENTITY, SQLERRM, TO_CHAR(SQLCODE));
395 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
396 END Delete_Access_Records;
397
398 END AS_TAP_PURGE_PUB;