1 PACKAGE BODY INV_TXN_PURGE_WORKER AS
2 /* $Header: INVTPGWB.pls 120.1.12000000.2 2007/02/21 23:11:53 yssingh ship $ */
3
4 --Procedure to purge the transaction tables
5
6
7 PROCEDURE Txn_Purge_Worker(
8 x_errbuf OUT NOCOPY VARCHAR2
9 ,x_retcode OUT NOCOPY NUMBER
10 ,p_organization_id IN NUMBER
11 ,p_min_date IN VARCHAR2
12 ,p_max_date IN VARCHAR2
13 )
14 IS
15
16 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
17
18
19 l_min_date DATE := fnd_date.displaydate_to_date(p_min_date);
20 l_max_date DATE := fnd_date.displaydate_to_date(p_max_date);
21
22 l_bulk_limit NUMBER := 2000;
23
24 TYPE rowidtab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
25 rowid_list rowidtab;
26
27 TYPE tranidtab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
28 tranid_list tranidtab;
29
30 l_ret boolean;
31 l_tempvar number;
32
33 l_count number;
34 l_total_count number;
35 l_total_count1 number;
36 l_total_count2 number;
37 l_total_count3 number;
38 l_total_count4 number;
39
40 --Bug3681437:Used the trunc cmd for transaction_date validation for all 5 cursors below
41
42 -- bug 4951747 : removed trunc to leverage MTL_MATERIAL_TRANSACTIONS_N5 index
43
44 -- Bug 5894075 : removed trunc from all the queries and added - (1/(24*3600)) to max date
45 CURSOR c_mmt IS
46 SELECT ROWID, TRANSACTION_ID
47 FROM MTL_MATERIAL_TRANSACTIONS
48 WHERE transaction_date >= l_min_date and transaction_date <= l_max_date + 1-(1/(24*3600))
49 AND organization_id = p_organization_id;
50
51 CURSOR c_mmta IS
52 SELECT ROWID
53 FROM MTL_MATERIAL_TXN_ALLOCATIONS
54 WHERE transaction_date >= l_min_date and transaction_date <= l_max_date + 1-(1/(24*3600))
55 AND organization_id = p_organization_id;
56
57 CURSOR c_mtlt IS
58 SELECT ROWID
59 FROM MTL_TRANSACTION_LOT_NUMBERS
60 WHERE transaction_date >= l_min_date and transaction_date <= l_max_date + 1-(1/(24*3600))
61 AND organization_id = p_organization_id;
62
63 CURSOR c_mut IS
64 SELECT ROWID
65 FROM MTL_UNIT_TRANSACTIONS
66 WHERE transaction_date >= l_min_date and transaction_date <= l_max_date + 1-(1/(24*3600))
67 AND organization_id = p_organization_id;
68
69 -- bug 4951747 : removed trunc to leverage MTL_TRANSACTION_ACCOUNTS_N5 index
70 CURSOR c_mta IS
71 SELECT ROWID
72 FROM MTL_TRANSACTION_ACCOUNTS
73 WHERE transaction_date >= l_min_date and transaction_date <= l_max_date + 1-(1/(24*3600))
74 AND organization_id = p_organization_id;
75
76 BEGIN
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
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
92 -- Validate the Organization passed to ensure that it has no OPEN
93 -- accounting periods for the Max_date specified
94 begin
95 SELECT 1
96 into l_tempvar
97 FROM ORG_ACCT_PERIODS
98 WHERE ORGANIZATION_ID = p_organization_id
99 AND SCHEDULE_CLOSE_DATE >= l_max_date
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;
109 x_errbuf := 'Error';
110 return;
111 end ;
112
113 -- *************** Purge from MTL_MATERIAL_TXN_ALLOCATIONS ****************
114 l_total_count := 0;
115 OPEN c_mmta;
116 LOOP
117 l_count := 0;
118
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
128 FORALL i IN rowid_list.first .. rowid_list.last
129 DELETE FROM MTL_MATERIAL_TXN_ALLOCATIONS
130 WHERE ROWID = rowid_list(i);
131
132 l_count := SQL%ROWCOUNT;
133 COMMIT;
134 l_total_count := l_total_count + l_count;
135 EXIT WHEN c_mmta%notfound;
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;
145 OPEN c_mtlt;
146 LOOP
147 l_count := 0;
148
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
158 FORALL i IN rowid_list.first .. rowid_list.last
159 DELETE FROM MTL_TRANSACTION_LOT_NUMBERS
160 WHERE ROWID = rowid_list(i);
161
162 l_count := SQL%ROWCOUNT;
163 COMMIT;
164 l_total_count := l_total_count + l_count;
165 EXIT WHEN c_mtlt%notfound;
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;
175 OPEN c_mut;
176 LOOP
177 l_count := 0;
178
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
188 FORALL i IN rowid_list.first .. rowid_list.last
189 DELETE FROM MTL_UNIT_TRANSACTIONS
190 WHERE ROWID = rowid_list(i);
191
192 l_count := SQL%ROWCOUNT;
193 COMMIT;
194 l_total_count := l_total_count + l_count;
195 EXIT WHEN c_mut%notfound;
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;
205 OPEN c_mta;
206 LOOP
207 l_count := 0;
208
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
218 FORALL i IN rowid_list.first .. rowid_list.last
219 DELETE FROM MTL_TRANSACTION_ACCOUNTS
220 WHERE ROWID = rowid_list(i);
221
222 l_count := SQL%ROWCOUNT;
223 COMMIT;
224 l_total_count := l_total_count + l_count;
225 EXIT WHEN c_mta%notfound;
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 ********************
235 l_total_count1 := 0;
236 l_total_count2 := 0;
237 l_total_count3 := 0;
238 l_total_count4 := 0;
239 l_total_count := 0;
240
241 OPEN c_mmt;
242 LOOP
243
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
253 -- *************** Purge from WIP_SCRAP_VALUES ************************
254 l_count := 0;
255
256 FORALL i IN tranid_list.first .. tranid_list.last
257 DELETE FROM WIP_SCRAP_VALUES
258 WHERE TRANSACTION_ID = tranid_list(i);
259
260 l_count := SQL%ROWCOUNT;
261 COMMIT;
262 l_total_count1 := l_total_count1 + l_count;
263
264 -- *************** Purge from MTL_CST_ACTUAL_COST_DETAILS ********************
265 l_count := 0;
266
267 FORALL i IN tranid_list.first .. tranid_list.last
268 DELETE FROM MTL_CST_ACTUAL_COST_DETAILS
269 WHERE TRANSACTION_ID = tranid_list(i);
270
271 l_count := SQL%ROWCOUNT;
272 COMMIT;
273 l_total_count2 := l_total_count2 + l_count;
274
275 -- *************** Purge from MTL_CST_TXN_COST_DETAILS ********************
276 l_count := 0;
277
278 FORALL i IN tranid_list.first .. tranid_list.last
279 DELETE FROM MTL_CST_TXN_COST_DETAILS
280 WHERE TRANSACTION_ID = tranid_list(i);
281
282 l_count := SQL%ROWCOUNT;
283 COMMIT;
284 l_total_count3 := l_total_count3 + l_count;
285
286 -- *************** Purge from MTL_ACTUAL_COST_SUBELEMENT ********************
287 l_count := 0;
288
289 FORALL i IN tranid_list.first .. tranid_list.last
290 DELETE FROM MTL_ACTUAL_COST_SUBELEMENT
291 WHERE TRANSACTION_ID = tranid_list(i);
292
293 l_count := SQL%ROWCOUNT;
294 COMMIT;
295 l_total_count4 := l_total_count4 + l_count;
296
297 -- *************** Purge from MTL_MATERIAL_TRANSACTIONS ********************
298 l_count := 0;
299
300 FORALL i IN rowid_list.first .. rowid_list.last
301 DELETE FROM MTL_MATERIAL_TRANSACTIONS
302 WHERE ROWID = rowid_list(i);
303
304 l_count := SQL%ROWCOUNT;
305 COMMIT;
306 l_total_count := l_total_count + l_count;
307
308 EXIT WHEN c_mmt%notfound;
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);
318 END IF;
319
320 l_ret := fnd_concurrent.set_completion_status('NORMAL', 'Success');
321
322 x_retcode := 0;
323 x_errbuf := 'Success';
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;
333 END IF;
334
335 IF c_mmta%ISOPEN THEN
336 CLOSE c_mmta;
337 END IF;
338
339 IF c_mtlt%ISOPEN THEN
340 CLOSE c_mtlt;
341 END IF;
342
343 IF c_mut%ISOPEN THEN
344 CLOSE c_mut;
345 END IF;
346
347 IF c_mta%ISOPEN THEN
348 CLOSE c_mta;
349 END IF;
350
351 ROLLBACK TO PURGE_SAVEPOINT;
352
353 l_ret := fnd_concurrent.set_completion_status('ERROR', 'Error');
354
355 x_retcode := 2;
356 x_errbuf := 'Error';
357
358 END Txn_Purge_Worker;
359
360 END INV_TXN_PURGE_WORKER;
[Home] [Help]