77:
78: SAVEPOINT PURGE_SAVEPOINT;
79:
80: IF (l_debug = 1) THEN
81: inv_log_util.trace('Purge Worker : OrgId='||p_organization_id||',Min_Date='||l_min_date||',Max_Date='||l_max_date,'TXN_PURGE_WORKER', 9);
82: END IF;
83:
84: --Check if max_date is less than min_date, if yes error out.
85: IF l_max_date < l_min_date THEN
83:
84: --Check if max_date is less than min_date, if yes error out.
85: IF l_max_date < l_min_date THEN
86: IF (l_debug = 1) THEN
87: inv_log_util.trace('Max_date cannot be less than min_date', 'TXN_PURGE_WORKER', 9);
88: END IF;
89: RAISE fnd_api.g_exc_error;
90: END IF;
91:
100: AND OPEN_FLAG = 'N' AND ROWNUM < 2;
101: exception
102: WHEN NO_DATA_FOUND THEN
103: IF (l_debug = 1) THEN
104: inv_log_util.trace('Purge Worker : Accounting Period is not closed for the Max_date','TXN_PURGE_WORKER', 9);
105: END IF;
106: l_ret := fnd_concurrent.set_completion_status('ERROR', 'Error');
107:
108: x_retcode := 2;
119: FETCH c_mmta bulk collect INTO rowid_list limit l_bulk_limit;
120:
121: IF rowid_list.first IS NULL THEN
122: IF (l_debug = 1) THEN
123: inv_log_util.trace('Purge Worker : No more Records to delete from MMTA','TXN_PURGE_WORKER', 9);
124: END IF;
125: EXIT;
126: END IF;
127:
136: END LOOP;
137: CLOSE c_mmta;
138:
139: IF (l_debug = 1) THEN
140: inv_log_util.trace('Deleted :'||l_total_count||' rows from MMTA', 'TXN_PURGE_WORKER', 9);
141: END IF;
142:
143: -- *************** Purge from MTL_TRANSACTION_LOT_NUMBERS ******************
144: l_total_count := 0;
149: FETCH c_mtlt bulk collect INTO rowid_list limit l_bulk_limit;
150:
151: IF rowid_list.first IS NULL THEN
152: IF (l_debug = 1) THEN
153: inv_log_util.trace('Purge Worker : No more Records to delete from MTLN','TXN_PURGE_WORKER', 9);
154: END IF;
155: EXIT;
156: END IF;
157:
166: END LOOP;
167: CLOSE c_mtlt;
168:
169: IF (l_debug = 1) THEN
170: inv_log_util.trace('Deleted :'||l_total_count||' rows from MTLN', 'TXN_PURGE_WORKER', 9);
171: END IF;
172:
173: -- *************** Purge from MTL_UNIT_TRANSACTIONS ******************
174: l_total_count := 0;
179: FETCH c_mut bulk collect INTO rowid_list limit l_bulk_limit;
180:
181: IF rowid_list.first IS NULL THEN
182: IF (l_debug = 1) THEN
183: inv_log_util.trace('Purge Worker : No more Records to delete from MUT','TXN_PURGE_WORKER', 9);
184: END IF;
185: EXIT;
186: END IF;
187:
196: END LOOP;
197: CLOSE c_mut;
198:
199: IF (l_debug = 1) THEN
200: inv_log_util.trace('Deleted :'||l_total_count||' rows from MUT', 'TXN_PURGE_WORKER', 9);
201: END IF;
202:
203: -- *************** Purge from MTL_TRANSACTION_ACCOUNTS ******************
204: l_total_count := 0;
209: FETCH c_mta bulk collect INTO rowid_list limit l_bulk_limit;
210:
211: IF rowid_list.first IS NULL THEN
212: IF (l_debug = 1) THEN
213: inv_log_util.trace('Purge Worker : No more Records to delete from MTA','TXN_PURGE_WORKER', 9);
214: END IF;
215: EXIT;
216: END IF;
217:
226: END LOOP;
227: CLOSE c_mta;
228:
229: IF (l_debug = 1) THEN
230: inv_log_util.trace('Deleted :'||l_total_count||' rows from MTA', 'TXN_PURGE_WORKER', 9);
231: END IF;
232:
233:
234: -- *************** Purge from MMT and dependent tables ********************
244: FETCH c_mmt bulk collect INTO rowid_list,tranid_list limit l_bulk_limit;
245:
246: IF tranid_list.first IS NULL THEN
247: IF (l_debug = 1) THEN
248: inv_log_util.trace('Purge Worker : No more Records to delete from MMT for dependant tables','TXN_PURGE_WORKER', 9);
249: END IF;
250: EXIT;
251: END IF;
252:
309: END LOOP;
310: CLOSE c_mmt;
311:
312: IF (l_debug = 1) THEN
313: inv_log_util.trace('Deleted :'||l_total_count1||' rows from WSV', 'TXN_PURGE_WORKER', 9);
314: inv_log_util.trace('Deleted :'||l_total_count2||' rows from MCACD', 'TXN_PURGE_WORKER', 9);
315: inv_log_util.trace('Deleted :'||l_total_count3||' rows from MCTCD', 'TXN_PURGE_WORKER', 9);
316: inv_log_util.trace('Deleted :'||l_total_count4||' rows from MACS', 'TXN_PURGE_WORKER', 9);
317: inv_log_util.trace('Deleted :'||l_total_count ||' rows from MMT', 'TXN_PURGE_WORKER', 9);
310: CLOSE c_mmt;
311:
312: IF (l_debug = 1) THEN
313: inv_log_util.trace('Deleted :'||l_total_count1||' rows from WSV', 'TXN_PURGE_WORKER', 9);
314: inv_log_util.trace('Deleted :'||l_total_count2||' rows from MCACD', 'TXN_PURGE_WORKER', 9);
315: inv_log_util.trace('Deleted :'||l_total_count3||' rows from MCTCD', 'TXN_PURGE_WORKER', 9);
316: inv_log_util.trace('Deleted :'||l_total_count4||' rows from MACS', 'TXN_PURGE_WORKER', 9);
317: inv_log_util.trace('Deleted :'||l_total_count ||' rows from MMT', 'TXN_PURGE_WORKER', 9);
318: END IF;
311:
312: IF (l_debug = 1) THEN
313: inv_log_util.trace('Deleted :'||l_total_count1||' rows from WSV', 'TXN_PURGE_WORKER', 9);
314: inv_log_util.trace('Deleted :'||l_total_count2||' rows from MCACD', 'TXN_PURGE_WORKER', 9);
315: inv_log_util.trace('Deleted :'||l_total_count3||' rows from MCTCD', 'TXN_PURGE_WORKER', 9);
316: inv_log_util.trace('Deleted :'||l_total_count4||' rows from MACS', 'TXN_PURGE_WORKER', 9);
317: inv_log_util.trace('Deleted :'||l_total_count ||' rows from MMT', 'TXN_PURGE_WORKER', 9);
318: END IF;
319:
312: IF (l_debug = 1) THEN
313: inv_log_util.trace('Deleted :'||l_total_count1||' rows from WSV', 'TXN_PURGE_WORKER', 9);
314: inv_log_util.trace('Deleted :'||l_total_count2||' rows from MCACD', 'TXN_PURGE_WORKER', 9);
315: inv_log_util.trace('Deleted :'||l_total_count3||' rows from MCTCD', 'TXN_PURGE_WORKER', 9);
316: inv_log_util.trace('Deleted :'||l_total_count4||' rows from MACS', 'TXN_PURGE_WORKER', 9);
317: inv_log_util.trace('Deleted :'||l_total_count ||' rows from MMT', 'TXN_PURGE_WORKER', 9);
318: END IF;
319:
320: l_ret := fnd_concurrent.set_completion_status('NORMAL', 'Success');
313: inv_log_util.trace('Deleted :'||l_total_count1||' rows from WSV', 'TXN_PURGE_WORKER', 9);
314: inv_log_util.trace('Deleted :'||l_total_count2||' rows from MCACD', 'TXN_PURGE_WORKER', 9);
315: inv_log_util.trace('Deleted :'||l_total_count3||' rows from MCTCD', 'TXN_PURGE_WORKER', 9);
316: inv_log_util.trace('Deleted :'||l_total_count4||' rows from MACS', 'TXN_PURGE_WORKER', 9);
317: inv_log_util.trace('Deleted :'||l_total_count ||' rows from MMT', 'TXN_PURGE_WORKER', 9);
318: END IF;
319:
320: l_ret := fnd_concurrent.set_completion_status('NORMAL', 'Success');
321:
324:
325: EXCEPTION
326: WHEN OTHERS THEN
327: IF (l_debug = 1) THEN
328: inv_log_util.trace('Error :'||substr(sqlerrm, 1, 100), 'TXN_PURGE_WORKER', 1);
329: END IF;
330:
331: IF c_mmt%ISOPEN THEN
332: CLOSE c_mmt;