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:
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
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 :=
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
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;
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
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;
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,
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);
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 => '',
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
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);
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
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);
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:
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);
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';
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:
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:
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 (
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
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')),
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:
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;
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:
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;
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);
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);
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;