1 PACKAGE BODY INV_HV_TXN_PURGE AS
2 /* $Header: INVHVPGB.pls 120.0.12010000.2 2008/10/15 11:19:31 sabghosh ship $ */
3
4
5 --Procedure to purge the transaction tables
6
7 /* The purge will be carried out by
8 1. Creating a temporary table with the required data alone.
9 2. Truncating the original table.
10 3. Inserting back the rows from the temporarary table to
11 the original table.
12 4. Commiting the transactions
13 5. Dropping the temporary tables
14
15 By this way, if the data to be deleted is very high, the program's performance
16 will be better when compared with actually deleting the rows.
17 If rows to be deleted are very less then script invtxnpg.sql will be better
18 in terms of performance where we wmploy direct deltion from the tables.
19 */
20
21 /*
22 The parameters are
23 1. x_errbuf -- Error buffer to concurrent program
24 2. x_retcode -- Indicates the return status of the concurrent program
25 3. p_organization_id -- Organization for which the purge has to be carried out,
26 If this is null then the records for all the organizations
27 will be purged.
28 4. p_cut_off_date -- The records whose transaction_date below this date
29 will be deleted. This is mandatory parameter.
30 This will be also used to check for accounting period.
31 If the period is open then purge won't be carried out.
32 */
33
34 /* Configurable Variables are
35
36 1. max_rows_to_del - This variable determines which approach has to be selected
37 for deleting the rows, either the direct approach or the
38 temp table approach. If the rows to be deleted are less than
39 this value then the records will be deleted directly else
40 it will be deleted via temp table approach.
41 Currently this is set to 100000. But it can be changed
42 based on the requirements
43 2. l_bulk_limit - This is the bulk collect limit for deletion.
44 This can be configured based on the database stats.
45 This is currently set to 5000.
46
47 */
48
49 PROCEDURE Txn_Purge( x_errbuf OUT NOCOPY VARCHAR2
50 ,x_retcode OUT NOCOPY NUMBER
51 ,p_organization_id IN NUMBER := NULL
52 ,p_cut_off_date IN VARCHAR2
53 )
54 IS
55
56 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
57 l_cut_off_date DATE := fnd_date.canonical_to_date(p_cut_off_date);
58 s_cut_off_date VARCHAR2(10);
59
60 s_sql_stmt VARCHAR2(1000);
61 l_ret_msg BOOLEAN;
62 error VARCHAR2(400);
63
64 cursor_name INTEGER;
65 rows_processed INTEGER;
66 rows_to_del NUMBER := 0;
67 max_rows_to_del NUMBER := 100000;
68 l_bulk_limit NUMBER := 5000;
69
70 inv_user_name VARCHAR2(30);
71
72 TYPE rowidtab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
73 rowid_list rowidtab;
74
75 x_validation VARCHAR2(10);
76 bad_input_period EXCEPTION;
77
78 CURSOR get_open_period (x_period_start_date DATE , x_organization_id NUMBER )
79 IS SELECT 'OPEN' sdate
80 FROM org_acct_periods
81 WHERE INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(x_period_start_date,x_organization_id)
82 >= ( SELECT MIN(period_start_date)
83 FROM org_acct_periods
84 WHERE organization_id = x_organization_id
85 AND open_flag = 'Y'
86 )
87 AND open_flag = 'Y';
88
89 CURSOR c_mmta IS
90 SELECT ROWID
91 FROM MTL_MATERIAL_TXN_ALLOCATIONS
92 WHERE transaction_date < l_cut_off_date
93 AND (p_organization_id IS NULL OR organization_id = p_organization_id);
94
95 CURSOR c_mmt IS
96 SELECT ROWID
97 FROM MTL_MATERIAL_TRANSACTIONS
98 WHERE transaction_date < l_cut_off_date
99 AND (p_organization_id IS NULL OR organization_id = p_organization_id);
100
101
102 CURSOR c_mtlt IS
103 SELECT ROWID
104 FROM MTL_TRANSACTION_LOT_NUMBERS
105 WHERE transaction_date < l_cut_off_date
106 AND (p_organization_id IS NULL OR organization_id = p_organization_id);
107
108
109 CURSOR c_mut IS
110 SELECT ROWID
111 FROM MTL_UNIT_TRANSACTIONS
112 WHERE transaction_date < l_cut_off_date
113 AND (p_organization_id IS NULL OR organization_id = p_organization_id);
114
115
116 CURSOR c_mta IS
117 SELECT ROWID
118 FROM MTL_TRANSACTION_ACCOUNTS
119 WHERE transaction_date < l_cut_off_date
120 AND (p_organization_id IS NULL OR organization_id = p_organization_id);
121
122
123 BEGIN
124
125 inv_trx_util_pub.TRACE('Debug Level = '|| l_debug);
126 inv_trx_util_pub.TRACE('Organization Id = '|| p_organization_id);
127 inv_trx_util_pub.TRACE('Cut off date = '|| p_cut_off_date);
128
129 --l_cut_off_date := To_Date( To_Char(fnd_date.canonical_to_date(p_cut_off_date),'MM-DD-RRRR'), 'MM-DD-RRRR');
130 s_cut_off_date := To_Char(fnd_date.canonical_to_date(p_cut_off_date),'MM-DD-RRRR');
131 inv_trx_util_pub.TRACE('Cut off date (' || l_cut_off_date || ') in MM-DD-RRRR format = '|| s_cut_off_date);
132
133 inv_user_name := upper('INV');
134 inv_trx_util_pub.TRACE('Inventory User Name = '|| inv_user_name);
135
136 /*
137 Validate to see if the accounting period is open.
138 If period is open then raise bad_input_period exception, else we do nothing.
139 If an organization is specified only validate that organization,
140 otherwise validate all organizations.
141 */
142 BEGIN
143
144 IF (l_debug = 1) THEN
145 inv_trx_util_pub.TRACE(' Accounting period check' );
146 END IF;
147
148 --Organization entered, so check the period of that org only
149 IF p_organization_id is not null THEN
150
151 OPEN get_open_period (l_cut_off_date, p_organization_id);
152 FETCH get_open_period INTO x_validation;
153 CLOSE get_open_period;
154
155 IF (l_debug = 1) THEN
156 inv_trx_util_pub.TRACE(' Organization_Id = ' || p_organization_id || ' Status = ' || x_validation );
157 END IF;
158
159 --Organization not provided, so Loop throught all organizations
160 ELSE
161
162 FOR i in (SELECT ood.organization_id FROM org_organization_definitions ood )
163 LOOP -- Organization loop
164
165 OPEN get_open_period (l_cut_off_date, i.organization_id);
166 FETCH get_open_period INTO x_validation;
167 CLOSE get_open_period;
168
169 IF x_validation = 'OPEN' THEN
170 IF (l_debug = 1) THEN
171 inv_trx_util_pub.TRACE('Accounting period is open for Organization_Id = ' || i.organization_id || ' Status = ' || x_validation );
172 END IF;
173 -- Period is open so exit out of the organization loop
174 EXIT;
175 END IF;
176
177 END LOOP; --Organizations loop
178
179 END IF; -- open period check
180
181 --Check for x_validation. If it is open raise exception. Else continue processing.
182 IF x_validation = 'OPEN' THEN
183 RAISE bad_input_period;
184 END IF;
185
186 IF (l_debug = 1) THEN
187 inv_trx_util_pub.TRACE(' Open period check completed sucessfully ' );
188 END IF;
189
190 EXCEPTION
191 WHEN bad_input_period THEN
192 IF (l_debug = 1) THEN
193 inv_trx_util_pub.TRACE(' Accounting Period is open. Please check the cut-off date ' || l_cut_off_date);
194 END IF;
195 RAISE fnd_api.g_exc_error;
196
197 WHEN OTHERS THEN
198 IF (l_debug = 1) THEN
199 inv_trx_util_pub.TRACE(' Exception in open period check: ' || SQLERRM);
200 END IF;
201 RAISE fnd_api.g_exc_unexpected_error;
202 END;
203
204
205
206 --Purging MTL_MATERIAL_TXN_ALLOCATIONS
207 BEGIN
208
209 IF (l_debug = 1) THEN
210 inv_trx_util_pub.TRACE(' Purging MTL_MATERIAL_TXN_ALLOCATIONS ... ' );
211 END IF;
212
213 -- This will get the count of rows to be deleted
214 BEGIN
215 SELECT count(transaction_id)
216 INTO rows_to_del
217 FROM mtl_material_txn_allocations
218 WHERE transaction_date < l_cut_off_date
219 AND (p_organization_id IS NULL OR organization_id = p_organization_id);
220
221 EXCEPTION
222 -- Some exception has occured
223 WHEN no_data_found THEN
224 IF (l_debug = 1) THEN
225 inv_trx_util_pub.TRACE(' Exception: ' || SQLERRM );
226 END IF;
227 rows_to_del := 0;
228
229
230 WHEN OTHERS THEN
231 IF (l_debug = 1) THEN
232 inv_trx_util_pub.TRACE(' Exception: ' || SQLERRM );
233 END IF;
234 rows_to_del := max_rows_to_del + 1;
235 END;
236
237
238 IF rows_to_del < max_rows_to_del THEN
239 --Rows to be deleted are less hence delete them directly
240
241 IF (l_debug = 1) THEN
242 inv_trx_util_pub.TRACE(' Deleting from MTL_MATERIAL_TXN_ALLOCATIONS -- Direct deletion approach' );
243 END IF;
244
245 OPEN c_mmta;
246 LOOP
247
248 FETCH c_mmta bulk collect INTO rowid_list limit l_bulk_limit;
249
250 IF rowid_list.first IS NULL THEN
251 inv_trx_util_pub.TRACE(' exiting out of the loop since there are no more records to delete ' );
252 EXIT;
253 END IF;
254
255 FORALL i IN rowid_list.first .. rowid_list.last
256 DELETE FROM mtl_material_txn_allocations
257 WHERE ROWID = rowid_list(i);
258
259 COMMIT;
260 EXIT WHEN c_mmta%notfound;
261 END LOOP;
262
263 IF (l_debug = 1) THEN
264 inv_trx_util_pub.TRACE(' Deleted ' || rows_to_del || ' row(s) from MTL_MATERIAL_TXN_ALLOCATIONS ' );
265 END IF;
266
267 CLOSE c_mmta;
268
269 IF (l_debug = 1) THEN
270 inv_trx_util_pub.TRACE(' Purged MTL_MATERIAL_TXN_ALLOCATIONS sucessfully ' );
271 END IF;
272
273 ELSE
274 -- Rows to be delted are more hence follow Temp table creation method
275 s_sql_stmt := ' CREATE TABLE mtl_material_txn_alloc_bu '
276 || ' STORAGE (initial 1 M next 1 M minextents 1 maxextents unlimited) '
277 || ' NOLOGGING AS '
278 || ' SELECT * FROM MTL_MATERIAL_TXN_ALLOCATIONS '
279 || ' WHERE 1 = 1 ' ;
280
281 IF p_organization_id IS NOT NULL THEN
282 s_sql_stmt := s_sql_stmt || ' and organization_id <> ' || p_organization_id;
283 s_sql_stmt := s_sql_stmt || ' or ( organization_id = ' || p_organization_id ||
284 ' and transaction_date >= to_date( '' ' || s_cut_off_date || ' '' , ''MM-DD-RRRR'' ) )';
285 ELSE
286 s_sql_stmt := s_sql_stmt || ' and transaction_date >= to_date( '' ' || s_cut_off_date || ' '' , ''MM-DD-RRRR'' )';
287 END IF;
288
289 cursor_name := dbms_sql.open_cursor;
290 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
291 rows_processed := dbms_sql.execute(cursor_name);
292 DBMS_SQL.close_cursor(cursor_name);
293
294 IF (l_debug = 1) THEN
295 inv_trx_util_pub.TRACE(' Temp Table mtl_material_txn_alloc_bu created.' );
296 END IF;
297
298 --Truncate the original table
299 s_sql_stmt := 'TRUNCATE TABLE '|| inv_user_name || '.MTL_MATERIAL_TXN_ALLOCATIONS';
300 cursor_name := dbms_sql.open_cursor;
301 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
302 rows_processed := dbms_sql.execute(cursor_name);
303 DBMS_SQL.close_cursor(cursor_name);
304
305 IF (l_debug = 1) THEN
306 inv_trx_util_pub.TRACE(' Truncated the table MTL_MATERIAL_TXN_ALLOCATIONS');
307 END IF;
308
309 -- Insert required rows back to original table
310 s_sql_stmt := 'INSERT INTO MTL_MATERIAL_TXN_ALLOCATIONS SELECT * FROM mtl_material_txn_alloc_bu';
311 cursor_name := dbms_sql.open_cursor;
312 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
313 rows_processed := dbms_sql.execute(cursor_name);
314 DBMS_SQL.close_cursor(cursor_name);
315
316 IF (l_debug = 1) THEN
317 inv_trx_util_pub.TRACE(' Inserted ' || rows_processed || ' row(s) into the table MTL_MATERIAL_TXN_ALLOCATIONS');
318 END IF;
319
320 --Commit the transaction
321 COMMIT;
322 IF (l_debug = 1) THEN
323 inv_trx_util_pub.TRACE(' Commited the transactions ');
324 END IF;
325
326 --Drop the temporary table
327 s_sql_stmt := 'DROP TABLE mtl_material_txn_alloc_bu';
328 cursor_name := dbms_sql.open_cursor;
329 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
330 rows_processed := dbms_sql.execute(cursor_name);
331 DBMS_SQL.close_cursor(cursor_name);
332
333 IF (l_debug = 1) THEN
334 inv_trx_util_pub.TRACE(' Dropped the temporary table mtl_material_txn_alloc_bu');
335 END IF;
336
337 END IF;
338
339 EXCEPTION
340 WHEN OTHERS THEN
341 IF (l_debug = 1) THEN
342 inv_trx_util_pub.TRACE(' Exception: ' || SQLERRM );
343 END IF;
344
345 IF DBMS_SQL.IS_OPEN(cursor_name) THEN
346 DBMS_SQL.CLOSE_CURSOR(cursor_name);
347 END IF;
348
349 RAISE fnd_api.g_exc_error;
350 END;
351
352
353
354 --Purging MTL_MATERIAL_TRANSACTIONS
355 BEGIN
356
357 IF (l_debug = 1) THEN
358 inv_trx_util_pub.TRACE(' Purging MTL_MATERIAL_TRANSACTIONS ... ' );
359 END IF;
360
361 -- This will get the count of rows to be deleted
362 BEGIN
363 SELECT count(transaction_id)
364 INTO rows_to_del
365 FROM mtl_material_transactions
366 WHERE transaction_date < l_cut_off_date
367 AND (p_organization_id IS NULL OR organization_id = p_organization_id);
368
369 EXCEPTION
370 -- Some exception has occured
371 WHEN no_data_found THEN
372 IF (l_debug = 1) THEN
373 inv_trx_util_pub.TRACE(' Exception: ' || SQLERRM );
374 END IF;
375 rows_to_del := 0;
376
377
378 WHEN OTHERS THEN
379 IF (l_debug = 1) THEN
380 inv_trx_util_pub.TRACE(' Exception: ' || SQLERRM );
381 END IF;
382 rows_to_del := max_rows_to_del + 1;
383 END;
384
385
386 IF rows_to_del < max_rows_to_del THEN
387 --Rows to be deleted are less hence delete them directly
388
389 IF (l_debug = 1) THEN
390 inv_trx_util_pub.TRACE(' Deleting from MTL_MATERIAL_TRANSACTIONS -- Direct deletion approach' );
391 END IF;
392
393 OPEN c_mmt;
394 LOOP
395 FETCH c_mmt bulk collect INTO rowid_list limit l_bulk_limit;
396
397 IF rowid_list.first IS NULL THEN
398 inv_trx_util_pub.TRACE(' exiting out of the loop since there are no more records to delete ' );
399 EXIT;
400 END IF;
401
402 FORALL i IN rowid_list.first .. rowid_list.last
403 DELETE FROM mtl_material_transactions
404 WHERE ROWID = rowid_list(i);
405 COMMIT;
406 EXIT WHEN c_mmt%notfound;
407 END LOOP;
408
409 IF (l_debug = 1) THEN
410 inv_trx_util_pub.TRACE(' Deleted ' || rows_to_del || ' row(s) from MTL_MATERIAL_TRANSACTIONS ' );
411 END IF;
412
413 CLOSE c_mmt;
414
415 IF (l_debug = 1) THEN
416 inv_trx_util_pub.TRACE(' Purged MTL_MATERIAL_TRANSACTIONS sucessfully ' );
417 END IF;
418
419 ELSE
420
421 -- Rows to be delted are more hence follow Temp table creation method
422 s_sql_stmt := ' CREATE TABLE mtl_material_transactions_bu '
423 || ' STORAGE (initial 1 M next 1 M minextents 1 maxextents unlimited) '
424 || ' NOLOGGING AS '
425 || ' SELECT * FROM MTL_MATERIAL_TRANSACTIONS '
426 || ' WHERE 1 = 1 ' ;
427
428 IF p_organization_id IS NOT NULL THEN
429 s_sql_stmt := s_sql_stmt || ' and organization_id <> ' || p_organization_id;
430 s_sql_stmt := s_sql_stmt || ' or ( organization_id = ' || p_organization_id ||
431 ' and transaction_date >= to_date( '' ' || s_cut_off_date || ' '' , ''MM-DD-RRRR'' ) )';
432 ELSE
433 s_sql_stmt := s_sql_stmt || ' and transaction_date >= to_date( '' ' || s_cut_off_date || ' '' , ''MM-DD-RRRR'' )';
434 END IF;
435
436 cursor_name := dbms_sql.open_cursor;
437 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
438 rows_processed := dbms_sql.execute(cursor_name);
439 DBMS_SQL.close_cursor(cursor_name);
440
441 IF (l_debug = 1) THEN
442 inv_trx_util_pub.TRACE(' Temp Table mtl_material_transactions_bu created.' );
443 END IF;
444
445 --Truncate the original table
446 s_sql_stmt := 'TRUNCATE TABLE '|| inv_user_name || '.MTL_MATERIAL_TRANSACTIONS';
447 cursor_name := dbms_sql.open_cursor;
448 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
449 rows_processed := dbms_sql.execute(cursor_name);
450 DBMS_SQL.close_cursor(cursor_name);
451
452 IF (l_debug = 1) THEN
453 inv_trx_util_pub.TRACE(' Truncated the table MTL_MATERIAL_TRANSACTIONS');
454 END IF;
455
456 -- Insert required rows back to original table
457 s_sql_stmt := 'INSERT INTO MTL_MATERIAL_TRANSACTIONS SELECT * FROM mtl_material_transactions_bu';
458 cursor_name := dbms_sql.open_cursor;
459 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
460 rows_processed := dbms_sql.execute(cursor_name);
461 DBMS_SQL.close_cursor(cursor_name);
462
463 IF (l_debug = 1) THEN
464 inv_trx_util_pub.TRACE(' Inserted ' || rows_processed || ' row(s) into the table MTL_MATERIAL_TRANSACTIONS');
465 END IF;
466
467 --Commit the transaction
468 COMMIT;
469 IF (l_debug = 1) THEN
470 inv_trx_util_pub.TRACE(' Commited the transactions ');
471 END IF;
472
473 --Drop the temporary table
474 s_sql_stmt := 'DROP TABLE mtl_material_transactions_bu';
475 cursor_name := dbms_sql.open_cursor;
476 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
477 rows_processed := dbms_sql.execute(cursor_name);
478 DBMS_SQL.close_cursor(cursor_name);
479
480 IF (l_debug = 1) THEN
481 inv_trx_util_pub.TRACE(' Dropped the temporary table mtl_material_transactions_bu');
482 END IF;
483
484 END IF;
485
486 EXCEPTION
487 WHEN OTHERS THEN
488 IF (l_debug = 1) THEN
489 inv_trx_util_pub.TRACE(' Exception: ' || SQLERRM );
490 END IF;
491
492 IF DBMS_SQL.IS_OPEN(cursor_name) THEN
493 DBMS_SQL.CLOSE_CURSOR(cursor_name);
494 END IF;
495
496 RAISE fnd_api.g_exc_error;
497 END;
498
499
500
501 --Purging MTL_TRANSACTION_LOT_NUMBERS
502 BEGIN
503
504 IF (l_debug = 1) THEN
505 inv_trx_util_pub.TRACE(' Purging MTL_TRANSACTION_LOT_NUMBERS ... ' );
506 END IF;
507
508 -- This will get the count of rows to be deleted
509 BEGIN
510 SELECT count(transaction_id)
511 INTO rows_to_del
512 FROM mtl_transaction_lot_numbers
513 WHERE transaction_date < l_cut_off_date
514 AND (p_organization_id IS NULL OR organization_id = p_organization_id);
515
516 EXCEPTION
517 -- Some exception has occured
518 WHEN no_data_found THEN
519 IF (l_debug = 1) THEN
520 inv_trx_util_pub.TRACE(' Exception: ' || SQLERRM );
521 END IF;
522 rows_to_del := 0;
523
524
525 WHEN OTHERS THEN
526 IF (l_debug = 1) THEN
527 inv_trx_util_pub.TRACE(' Exception: ' || SQLERRM );
528 END IF;
529 rows_to_del := max_rows_to_del + 1;
530 END;
531
532
533 IF rows_to_del < max_rows_to_del THEN
534 --Rows to be deleted are less hence delete them directly
535
536 IF (l_debug = 1) THEN
537 inv_trx_util_pub.TRACE(' Deleting from MTL_TRANSACTION_LOT_NUMBERS -- Direct deletion approach' );
538 END IF;
539
540 OPEN c_mtlt;
541 LOOP
542 FETCH c_mtlt bulk collect INTO rowid_list limit l_bulk_limit;
543
544 IF rowid_list.first IS NULL THEN
545 inv_trx_util_pub.TRACE(' exiting out of the loop since there are no more records to delete ' );
546 EXIT;
547 END IF;
548
549 FORALL i IN rowid_list.first .. rowid_list.last
550 DELETE FROM mtl_transaction_lot_numbers
551 WHERE ROWID = rowid_list(i);
552 COMMIT;
553 EXIT WHEN c_mtlt%notfound;
554 END LOOP;
555
556 IF (l_debug = 1) THEN
557 inv_trx_util_pub.TRACE(' Deleted ' || rows_to_del || ' row(s) from MTL_TRANSACTION_LOT_NUMBERS ' );
558 END IF;
559
560 CLOSE c_mtlt;
561
562 IF (l_debug = 1) THEN
563 inv_trx_util_pub.TRACE(' Purged MTL_TRANSACTION_LOT_NUMBERS sucessfully ' );
564 END IF;
565
566 ELSE
567
568 -- Rows to be delted are more hence follow Temp table creation method
569 s_sql_stmt := ' CREATE TABLE mtl_transaction_lot_numbers_bu '
570 || ' STORAGE (initial 1 M next 1 M minextents 1 maxextents unlimited) '
571 || ' NOLOGGING AS '
572 || ' SELECT * FROM MTL_TRANSACTION_LOT_NUMBERS '
573 || ' WHERE 1 = 1 ' ;
574
575 IF p_organization_id IS NOT NULL THEN
576 s_sql_stmt := s_sql_stmt || ' and organization_id <> ' || p_organization_id;
577 s_sql_stmt := s_sql_stmt || ' or ( organization_id = ' || p_organization_id ||
578 ' and transaction_date >= to_date( '' ' || s_cut_off_date || ' '' , ''MM-DD-RRRR'' ) )';
579 ELSE
580 s_sql_stmt := s_sql_stmt || ' and transaction_date >= to_date( '' ' || s_cut_off_date || ' '' , ''MM-DD-RRRR'' )';
581 END IF;
582
583 cursor_name := dbms_sql.open_cursor;
584 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
585 rows_processed := dbms_sql.execute(cursor_name);
586 DBMS_SQL.close_cursor(cursor_name);
587
588 IF (l_debug = 1) THEN
589 inv_trx_util_pub.TRACE(' Temp Table mtl_transaction_lot_numbers_bu created.' );
590 END IF;
591
592 --Truncate the original table
593 s_sql_stmt := 'TRUNCATE TABLE '|| inv_user_name || '.MTL_TRANSACTION_LOT_NUMBERS';
594 cursor_name := dbms_sql.open_cursor;
595 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
596 rows_processed := dbms_sql.execute(cursor_name);
597 DBMS_SQL.close_cursor(cursor_name);
598
599 IF (l_debug = 1) THEN
600 inv_trx_util_pub.TRACE(' Truncated the table MTL_TRANSACTION_LOT_NUMBERS');
601 END IF;
602
603 -- Insert required rows back to original table
604 s_sql_stmt := 'INSERT INTO MTL_TRANSACTION_LOT_NUMBERS SELECT * FROM mtl_transaction_lot_numbers_bu';
605 cursor_name := dbms_sql.open_cursor;
606 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
607 rows_processed := dbms_sql.execute(cursor_name);
608 DBMS_SQL.close_cursor(cursor_name);
609
610 IF (l_debug = 1) THEN
611 inv_trx_util_pub.TRACE(' Inserted ' || rows_processed || ' row(s) into the table MTL_TRANSACTION_LOT_NUMBERS');
612 END IF;
613
614 --Commit the transaction
615 COMMIT;
616 IF (l_debug = 1) THEN
617 inv_trx_util_pub.TRACE(' Commited the transactions ');
618 END IF;
619
620 --Drop the temporary table
621 s_sql_stmt := 'DROP TABLE mtl_transaction_lot_numbers_bu';
622 cursor_name := dbms_sql.open_cursor;
623 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
624 rows_processed := dbms_sql.execute(cursor_name);
625 DBMS_SQL.close_cursor(cursor_name);
626
627 IF (l_debug = 1) THEN
628 inv_trx_util_pub.TRACE(' Dropped the temporary table mtl_transaction_lot_numbers_bu');
629 END IF;
630
631 END IF;
632
633 EXCEPTION
634 WHEN OTHERS THEN
635 IF (l_debug = 1) THEN
636 inv_trx_util_pub.TRACE(' Exception: ' || SQLERRM );
637 END IF;
638
639 IF DBMS_SQL.IS_OPEN(cursor_name) THEN
640 DBMS_SQL.CLOSE_CURSOR(cursor_name);
641 END IF;
642
643 RAISE fnd_api.g_exc_error;
644 END;
645
646
647
648 --Purging MTL_UNIT_TRANSACTIONS
649 BEGIN
650
651 IF (l_debug = 1) THEN
652 inv_trx_util_pub.TRACE(' Purging MTL_UNIT_TRANSACTIONS ... ' );
653 END IF;
654
655 -- This will get the count of rows to be deleted
656 BEGIN
657 SELECT count(transaction_id)
658 INTO rows_to_del
659 FROM mtl_unit_transactions
660 WHERE transaction_date < l_cut_off_date
661 AND (p_organization_id IS NULL OR organization_id = p_organization_id);
662
663 EXCEPTION
664 -- Some exception has occured
665 WHEN no_data_found THEN
666 IF (l_debug = 1) THEN
667 inv_trx_util_pub.TRACE(' Exception: ' || SQLERRM );
668 END IF;
669 rows_to_del := 0;
670
671
672 WHEN OTHERS THEN
673 IF (l_debug = 1) THEN
674 inv_trx_util_pub.TRACE(' Exception: ' || SQLERRM );
675 END IF;
676 rows_to_del := max_rows_to_del + 1;
677 END;
678
679
680 IF rows_to_del < max_rows_to_del THEN
681 --Rows to be deleted are less hence delete them directly
682
683 IF (l_debug = 1) THEN
684 inv_trx_util_pub.TRACE(' Deleting from MTL_UNIT_TRANSACTIONS -- Direct deletion approach' );
685 END IF;
686
687 OPEN c_mut;
688 LOOP
689 FETCH c_mut bulk collect INTO rowid_list limit l_bulk_limit;
690
691 IF rowid_list.first IS NULL THEN
692 inv_trx_util_pub.TRACE(' exiting out of the loop since there are no more records to delete ' );
693 EXIT;
694 END IF;
695
696 FORALL i IN rowid_list.first .. rowid_list.last
697 DELETE FROM mtl_unit_transactions
698 WHERE ROWID = rowid_list(i);
699 COMMIT;
700 EXIT WHEN c_mut%notfound;
701 END LOOP;
702
703 IF (l_debug = 1) THEN
704 inv_trx_util_pub.TRACE(' Deleted ' || rows_to_del || ' row(s) from MTL_UNIT_TRANSACTIONS ' );
705 END IF;
706
707 CLOSE c_mut;
708
709 IF (l_debug = 1) THEN
710 inv_trx_util_pub.TRACE(' Purged MTL_UNIT_TRANSACTIONS sucessfully ' );
711 END IF;
712
713 ELSE
714
715 -- Rows to be delted are more hence follow Temp table creation method
716 s_sql_stmt := ' CREATE TABLE mtl_unit_transactions_bu '
717 || ' STORAGE (initial 1 M next 1 M minextents 1 maxextents unlimited) '
718 || ' NOLOGGING AS '
719 || ' SELECT * FROM MTL_UNIT_TRANSACTIONS '
720 || ' WHERE 1 = 1 ' ;
721
722 IF p_organization_id IS NOT NULL THEN
723 s_sql_stmt := s_sql_stmt || ' and organization_id <> ' || p_organization_id;
724 s_sql_stmt := s_sql_stmt || ' or ( organization_id = ' || p_organization_id ||
725 ' and transaction_date >= to_date( '' ' || s_cut_off_date || ' '' , ''MM-DD-RRRR'' ) )';
726 ELSE
727 s_sql_stmt := s_sql_stmt || ' and transaction_date >= to_date( '' ' || s_cut_off_date || ' '' , ''MM-DD-RRRR'' )';
728 END IF;
729
730 cursor_name := dbms_sql.open_cursor;
731 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
732 rows_processed := dbms_sql.execute(cursor_name);
733 DBMS_SQL.close_cursor(cursor_name);
734
735 IF (l_debug = 1) THEN
736 inv_trx_util_pub.TRACE(' Temp Table mtl_unit_transactions_bu created.' );
737 END IF;
738
739 --Truncate the original table
740 s_sql_stmt := 'TRUNCATE TABLE '|| inv_user_name || '.MTL_UNIT_TRANSACTIONS';
741 cursor_name := dbms_sql.open_cursor;
742 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
743 rows_processed := dbms_sql.execute(cursor_name);
744 DBMS_SQL.close_cursor(cursor_name);
745
746 IF (l_debug = 1) THEN
747 inv_trx_util_pub.TRACE(' Truncated the table MTL_UNIT_TRANSACTIONS');
748 END IF;
749
750 -- Insert required rows back to original table
751 s_sql_stmt := 'INSERT INTO MTL_UNIT_TRANSACTIONS SELECT * FROM mtl_unit_transactions_bu';
752 cursor_name := dbms_sql.open_cursor;
753 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
754 rows_processed := dbms_sql.execute(cursor_name);
755 DBMS_SQL.close_cursor(cursor_name);
756
757 IF (l_debug = 1) THEN
758 inv_trx_util_pub.TRACE(' Inserted ' || rows_processed || ' row(s) into the table MTL_UNIT_TRANSACTIONS');
759 END IF;
760
761 --Commit the transaction
762 COMMIT;
763 IF (l_debug = 1) THEN
764 inv_trx_util_pub.TRACE(' Commited the transactions ');
765 END IF;
766
767 --Drop the temporary table
768 s_sql_stmt := 'DROP TABLE mtl_unit_transactions_bu';
769 cursor_name := dbms_sql.open_cursor;
770 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
771 rows_processed := dbms_sql.execute(cursor_name);
772 DBMS_SQL.close_cursor(cursor_name);
773
774 IF (l_debug = 1) THEN
775 inv_trx_util_pub.TRACE(' Dropped the temporary table mtl_unit_transactions_bu');
776 END IF;
777
778 END IF;
779
780 EXCEPTION
781 WHEN OTHERS THEN
782 IF (l_debug = 1) THEN
783 inv_trx_util_pub.TRACE(' Exception: ' || SQLERRM );
784 END IF;
785
786 IF DBMS_SQL.IS_OPEN(cursor_name) THEN
787 DBMS_SQL.CLOSE_CURSOR(cursor_name);
788 END IF;
789
790 RAISE fnd_api.g_exc_error;
791 END;
792
793
794
795 --Purging MTL_TRANSACTION_ACCOUNTS
796 BEGIN
797
798 IF (l_debug = 1) THEN
799 inv_trx_util_pub.TRACE(' Purging MTL_TRANSACTION_ACCOUNTS ... ' );
800 END IF;
801
802 -- This will get the count of rows to be deleted
803 BEGIN
804 --Start bug 7336061
805 /*SELECT count(transaction_id)
806 INTO rows_to_del
807 FROM mtl_transaction_accounts
808 WHERE transaction_date < l_cut_off_date
809 AND (p_organization_id IS NULL OR organization_id = p_organization_id);*/
810
811 IF p_organization_id IS NULL THEN
812
813 SELECT COUNT(transaction_id)
814 INTO rows_to_del
815 FROM mtl_transaction_accounts
816 WHERE transaction_date < l_cut_off_date ;
817
818 ELSE
819
820 SELECT COUNT(transaction_id)
821 INTO rows_to_del
822 FROM mtl_transaction_accounts
823 WHERE transaction_date < l_cut_off_date
824 AND organization_id = p_organization_id ;
825
826 END IF ;
827 --End bug 7336061
828 EXCEPTION
829 -- Some exception has occured
830 WHEN no_data_found THEN
831 IF (l_debug = 1) THEN
832 inv_trx_util_pub.TRACE(' Exception: ' || SQLERRM );
833 END IF;
834 rows_to_del := 0;
835
836
837 WHEN OTHERS THEN
838 IF (l_debug = 1) THEN
839 inv_trx_util_pub.TRACE(' Exception: ' || SQLERRM );
840 END IF;
841 rows_to_del := max_rows_to_del + 1;
842 END;
843
844
845 IF rows_to_del < max_rows_to_del THEN
846 --Rows to be deleted are less hence delete them directly
847
848 IF (l_debug = 1) THEN
849 inv_trx_util_pub.TRACE(' Deleting from MTL_TRANSACTION_ACCOUNTS -- Direct deletion approach' );
850 END IF;
851
852 OPEN c_mta;
853 LOOP
854 FETCH c_mta bulk collect INTO rowid_list limit l_bulk_limit;
855
856 IF rowid_list.first IS NULL THEN
857 inv_trx_util_pub.TRACE(' exiting out of the loop since there are no more records to delete ' );
858 EXIT;
859 END IF;
860
861 FORALL i IN rowid_list.first .. rowid_list.last
862 DELETE FROM mtl_transaction_accounts
863 WHERE ROWID = rowid_list(i);
864 COMMIT;
865 EXIT WHEN c_mta%notfound;
866 END LOOP;
867
868 IF (l_debug = 1) THEN
869 inv_trx_util_pub.TRACE(' Deleted ' || rows_to_del || ' row(s) from MTL_TRANSACTION_ACCOUNTS ' );
870 END IF;
871
872 CLOSE c_mta;
873
874 IF (l_debug = 1) THEN
875 inv_trx_util_pub.TRACE(' Purged MTL_TRANSACTION_ACCOUNTS sucessfully ' );
876 END IF;
877
878 ELSE
879
880 -- Rows to be delted are more hence follow Temp table creation method
881 s_sql_stmt := ' CREATE TABLE mtl_transaction_accounts_bu '
882 || ' STORAGE (initial 1 M next 1 M minextents 1 maxextents unlimited) '
883 || ' NOLOGGING AS '
884 || ' SELECT * FROM MTL_TRANSACTION_ACCOUNTS '
885 || ' WHERE 1 = 1 ' ;
886
887 IF p_organization_id IS NOT NULL THEN
888 s_sql_stmt := s_sql_stmt || ' and organization_id <> ' || p_organization_id;
889 s_sql_stmt := s_sql_stmt || ' or ( organization_id = ' || p_organization_id ||
890 ' and transaction_date >= to_date( '' ' || s_cut_off_date || ' '' , ''MM-DD-RRRR'' ) )';
891 ELSE
892 s_sql_stmt := s_sql_stmt || ' and transaction_date >= to_date( '' ' || s_cut_off_date || ' '' , ''MM-DD-RRRR'' )';
893 END IF;
894
895 cursor_name := dbms_sql.open_cursor;
896 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
897 rows_processed := dbms_sql.execute(cursor_name);
898 DBMS_SQL.close_cursor(cursor_name);
899
900 IF (l_debug = 1) THEN
901 inv_trx_util_pub.TRACE(' Temp Table mtl_transaction_accounts_bu created.' );
902 END IF;
903
904 --Truncate the original table
905 s_sql_stmt := 'TRUNCATE TABLE '|| inv_user_name || '.MTL_TRANSACTION_ACCOUNTS';
906 cursor_name := dbms_sql.open_cursor;
907 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
908 rows_processed := dbms_sql.execute(cursor_name);
909 DBMS_SQL.close_cursor(cursor_name);
910
911 IF (l_debug = 1) THEN
912 inv_trx_util_pub.TRACE(' Truncated the table MTL_TRANSACTION_ACCOUNTS');
913 END IF;
914
915 -- Insert required rows back to original table
916 s_sql_stmt := 'INSERT INTO MTL_TRANSACTION_ACCOUNTS SELECT * FROM mtl_transaction_accounts_bu';
917 cursor_name := dbms_sql.open_cursor;
918 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
919 rows_processed := dbms_sql.execute(cursor_name);
920 DBMS_SQL.close_cursor(cursor_name);
921
922 IF (l_debug = 1) THEN
923 inv_trx_util_pub.TRACE(' Inserted ' || rows_processed || ' row(s) into the table MTL_TRANSACTION_ACCOUNTS');
924 END IF;
925
926 --Commit the transaction
927 COMMIT;
928 IF (l_debug = 1) THEN
929 inv_trx_util_pub.TRACE(' Commited the transactions ');
930 END IF;
931
932 --Drop the temporary table
933 s_sql_stmt := 'DROP TABLE mtl_transaction_accounts_bu';
934 cursor_name := dbms_sql.open_cursor;
935 DBMS_SQL.PARSE(cursor_name, s_sql_stmt, dbms_sql.native);
936 rows_processed := dbms_sql.execute(cursor_name);
937 DBMS_SQL.close_cursor(cursor_name);
938
939 IF (l_debug = 1) THEN
940 inv_trx_util_pub.TRACE(' Dropped the temporary table mtl_transaction_accounts_bu');
941 END IF;
942
943 END IF;
944
945 EXCEPTION
946 WHEN OTHERS THEN
947 IF (l_debug = 1) THEN
948 inv_trx_util_pub.TRACE(' Exception: ' || SQLERRM );
949 END IF;
950
951 IF DBMS_SQL.IS_OPEN(cursor_name) THEN
952 DBMS_SQL.CLOSE_CURSOR(cursor_name);
953 END IF;
954
955 RAISE fnd_api.g_exc_error;
956 END;
957
958 --return sucess
959 l_ret_msg := fnd_concurrent.set_completion_status('NORMAL', 'NORMAL');
960 x_retcode := retcode_success;
961 x_errbuf := NULL;
962
963 inv_trx_util_pub.TRACE('High Volume Transactions Purge completed sucessfully');
964
965 EXCEPTION
966
967 WHEN fnd_api.g_exc_error THEN
968 error := SQLERRM;
969 IF (l_debug = 1) THEN
970 inv_trx_util_pub.TRACE('The error is '|| error, 'INVHVPG', 9);
971 END IF;
972
973 IF c_mmta%ISOPEN THEN
974 CLOSE c_mmta;
975 END IF;
976
977 IF c_mmt%ISOPEN THEN
978 CLOSE c_mmt;
979 END IF;
980
981 IF c_mtlt%ISOPEN THEN
982 CLOSE c_mtlt;
983 END IF;
984
985 IF c_mut%ISOPEN THEN
986 CLOSE c_mut;
987 END IF;
988
989 IF c_mta%ISOPEN THEN
990 CLOSE c_mta;
991 END IF;
992
993 l_ret_msg := fnd_concurrent.set_completion_status('ERROR', 'ERROR');
994 x_retcode := retcode_error;
995 x_errbuf := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
996
997 WHEN fnd_api.g_exc_unexpected_error THEN
998 error := SQLERRM;
999 IF (l_debug = 1) THEN
1000 inv_trx_util_pub.TRACE('The error is '|| error, 'INVHVPG', 9);
1001 END IF;
1002
1003 IF c_mmta%ISOPEN THEN
1004 CLOSE c_mmta;
1005 END IF;
1006
1007 IF c_mmt%ISOPEN THEN
1008 CLOSE c_mmt;
1009 END IF;
1010
1011 IF c_mtlt%ISOPEN THEN
1012 CLOSE c_mtlt;
1013 END IF;
1014
1015 IF c_mut%ISOPEN THEN
1016 CLOSE c_mut;
1017 END IF;
1018
1019 IF c_mta%ISOPEN THEN
1020 CLOSE c_mta;
1021 END IF;
1022
1023 l_ret_msg := fnd_concurrent.set_completion_status('ERROR', 'ERROR');
1024 x_retcode := retcode_error;
1025 x_errbuf := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1026
1027 WHEN OTHERS THEN
1028 error := SQLERRM;
1029 IF (l_debug = 1) THEN
1030 inv_trx_util_pub.TRACE('The error is '|| error, 'INVHVPG', 9);
1031 END IF;
1032
1033 IF c_mmta%ISOPEN THEN
1034 CLOSE c_mmta;
1035 END IF;
1036
1037 IF c_mmt%ISOPEN THEN
1038 CLOSE c_mmt;
1039 END IF;
1040
1041 IF c_mtlt%ISOPEN THEN
1042 CLOSE c_mtlt;
1043 END IF;
1044
1045 IF c_mut%ISOPEN THEN
1046 CLOSE c_mut;
1047 END IF;
1048
1049 IF c_mta%ISOPEN THEN
1050 CLOSE c_mta;
1051 END IF;
1052
1053 l_ret_msg := fnd_concurrent.set_completion_status('ERROR', 'ERROR');
1054 x_retcode := retcode_error;
1055 x_errbuf := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1056
1057 END Txn_Purge;
1058
1059 END INV_HV_TXN_PURGE;
[Home] [Help]