[Home] [Help]
PACKAGE BODY: APPS.MSC_PURGE_LID
Source
1 PACKAGE BODY MSC_PURGE_LID AS
2 /*$Header: MSCPPURB.pls 120.1.12010000.2 2008/05/22 13:01:14 rsyadav ship $ */
3 v_sql_stmt PLS_INTEGER;--Holds the DML statement no used for error logging.
4
5 -- ========= Global Parameters ===========
6 v_instance_id NUMBER ;
7 v_date DATE;
8 v_debug BOOLEAN;
9
10 -- User Environment --
11 v_current_date DATE ;
12 v_current_user NUMBER;
13 v_login_user NUMBER;
14 v_request_id NUMBER;
15 v_prog_appl_id NUMBER;
16 v_program_id NUMBER;
17 v_applsys_schema VARCHAR2(32) := 'MSC';
18
19
20
21 /*=======================================================================
22 This function return true if the status of staging table is ready/purging
23 or empty.
24 =========================================================================*/
25
26 FUNCTION CHECK_ST_STATUS( errbuf OUT NOCOPY VARCHAR2,
27 retcode OUT NOCOPY NUMBER,
28 p_instance_id IN NUMBER )
29 RETURN BOOLEAN
30 IS
31 lv_staging_table_status NUMBER;
32 lv_instance_enabled NUMBER;
33
34 BEGIN
35
36 SELECT mai.enable_flag,
37 mai.st_status
38 INTO lv_instance_enabled, lv_staging_table_status
39 FROM MSC_APPS_INSTANCES mai
40 WHERE mai.INSTANCE_ID= v_instance_id
41 AND mai.instance_type IN (G_INS_OTHER,G_INS_EXCH) ;
42
43
44 IF lv_instance_enabled= SYS_YES THEN
45
46 IF lv_staging_table_status= G_ST_READY THEN
47
48 FND_MESSAGE.SET_NAME('MSC', 'MSC_ST_ERROR_DATA_EXIST');
49 ERRBUF:= FND_MESSAGE.GET;
50 retcode:= G_SUCCESS ;
51 RETURN TRUE ;
52
53 ELSIF lv_staging_table_status= G_ST_PULLING THEN
54 FND_MESSAGE.SET_NAME('MSC', 'MSC_ST_ERROR_PULLING');
55 errbuf:= FND_MESSAGE.GET;
56 retcode:= G_ERROR ;
57 RETURN FALSE ;
58
59 ELSIF lv_staging_table_status= G_ST_COLLECTING THEN
60 FND_MESSAGE.SET_NAME('MSC', 'MSC_ST_ERROR_LOADING');
61 ERRBUF:= FND_MESSAGE.GET;
62 retcode:= G_ERROR ;
63 RETURN FALSE ;
64
65 ELSIF lv_staging_table_status= G_ST_PURGING THEN
66
67 FND_MESSAGE.SET_NAME('MSC', 'MSC_ST_ERROR_PURGING');
68 ERRBUF:= FND_MESSAGE.GET;
69 retcode:= G_SUCCESS ;
70 RETURN TRUE ;
71
72 ELSIF lv_staging_table_status= G_ST_PRE_PROCESSING THEN
73
74 FND_MESSAGE.SET_NAME('MSC', 'MSC_ST_PRE_PROCESSING');
75 ERRBUF:= FND_MESSAGE.GET;
76 retcode:= G_ERROR ;
77 RETURN FALSE ;
78
79 ELSE
80 retcode:= G_SUCCESS ;
81 RETURN TRUE ;
82 END IF;
83
84 ELSE
85 FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_INSTANCE_INACTIVE');
86 errbuf:= FND_MESSAGE.GET;
87 retcode:= G_ERROR;
88 RETURN FALSE;
89 END IF;
90
91 EXCEPTION
92 WHEN OTHERS THEN
93 errbuf := SQLERRM;
94 retcode := SQLCODE;
95 RETURN FALSE;
96
97 END CHECK_ST_STATUS;
98
99 FUNCTION is_msctbl_partitioned ( p_table_name IN VARCHAR2)
100 RETURN BOOLEAN
101 IS
102 lv_partitioned VARCHAR2(3);
103
104 CURSOR c_partitioned IS
105 SELECT tab.partitioned
106 FROM dba_tables tab,
107 FND_ORACLE_USERID a,
108 FND_PRODUCT_INSTALLATIONS b
109 WHERE a.oracle_id = b.oracle_id
110 AND b.application_id= 724
111 AND tab.owner= a.oracle_username
112 AND tab.table_name= p_table_name;
113
114 BEGIN
115
116 OPEN c_partitioned;
117 FETCH c_partitioned INTO lv_partitioned;
118 CLOSE c_partitioned;
119
120 IF lv_partitioned='YES' THEN RETURN TRUE; END IF;
121 RETURN FALSE;
122 EXCEPTION
123 WHEN OTHERS THEN
124 RETURN FALSE;
125 END is_msctbl_partitioned;
126
127 /*=======================================================================
128 This function deletes record from the MSC tables.
129 =========================================================================*/
130
131 PROCEDURE DELETE_MSC_TABLE( p_table_name IN VARCHAR2,
132 p_instance_id IN NUMBER,
133 p_plan_id IN NUMBER:= NULL,
134 p_sub_str IN VARCHAR2:= NULL) IS
135
136 lv_cnt NUMBER;
137 lv_pbs NUMBER;
138 lv_sql_stmt VARCHAR2(2048);
139
140 lv_task_start_time DATE;
141
142 lv_partition_name VARCHAR2(30);
143 lv_is_plan NUMBER;
144
145 lv_msg_data VARCHAR2(2048);
146 lv_return_status VARCHAR2(2048);
147 lv_errtext VARCHAR2(2048);
148
149 BEGIN
150
151 lv_task_start_time:= SYSDATE;
152
153 FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
154 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'DELETE_MSC_TABLE:'||p_table_name);
155 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,' '||FND_MESSAGE.GET);
156
157 IF p_sub_str IS NULL
158 AND is_msctbl_partitioned( p_table_name) THEN
159
160 IF p_plan_id= -1 OR p_plan_id IS NULL THEN
161 lv_is_plan:= SYS_NO;
162 ELSE
163 lv_is_plan:= SYS_YES;
164 END IF;
165
166 msc_manage_plan_partitions.get_partition_name
167 ( p_plan_id,
168 p_instance_id,
169 p_table_name,
170 lv_is_plan,
171 lv_partition_name,
172 lv_return_status,
173 lv_msg_data);
174
175
176 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Partition Name : '||lv_partition_name);
177
178 v_sql_stmt := 01;
179
180 lv_sql_stmt:= 'ALTER TABLE '||p_table_name
181 ||' TRUNCATE PARTITION '||lv_partition_name;
182
183
184 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
185
186
187 AD_DDL.DO_DDL( APPLSYS_SCHEMA => v_applsys_schema,
188 APPLICATION_SHORT_NAME => 'MSC',
189 STATEMENT_TYPE => AD_DDL.ALTER_TABLE,
190 STATEMENT => lv_sql_stmt,
191 OBJECT_NAME => p_table_name);
192
193 ELSE
194
195 lv_pbs:= TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE'));
196
197 IF p_plan_id IS NULL THEN
198
199 v_sql_stmt := 02;
200
201 lv_sql_stmt:= 'SELECT COUNT(*)'
202 ||' FROM '|| p_table_name
203 ||' WHERE SR_INSTANCE_ID= :p_instance_id '
204 || p_sub_str;
205
206 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
207 EXECUTE IMMEDIATE lv_sql_stmt
208 INTO lv_cnt
209 USING p_instance_id;
210
211
212
213
214
215
216 IF lv_pbs IS NULL OR
217 lv_cnt < lv_pbs THEN
218
219 v_sql_stmt := 03;
220
221 lv_sql_stmt:= 'DELETE '||p_table_name
222 ||' WHERE SR_INSTANCE_ID= :p_instance_id '
223 || p_sub_str;
224
225 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
226 EXECUTE IMMEDIATE lv_sql_stmt
227 USING p_instance_id;
228
229
230
231
232 COMMIT;
233
234 ELSE
235
236 v_sql_stmt := 04;
237 lv_sql_stmt:= 'DELETE '||p_table_name
238 ||' WHERE SR_INSTANCE_ID= :p_instance_id '
239 || p_sub_str
240 ||' AND ROWNUM < :lv_pbs';
241 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
242
243 LOOP
244
245 EXECUTE IMMEDIATE lv_sql_stmt
246 USING p_instance_id, lv_pbs;
247
248 EXIT WHEN SQL%ROWCOUNT= 0;
249
250 COMMIT;
251
252 END LOOP;
253
254 END IF;
255
256 ELSE
257
258 v_sql_stmt := 05;
259 lv_sql_stmt:= 'SELECT COUNT(*)'
260 ||' FROM '||p_table_name
261 ||' WHERE SR_INSTANCE_ID= :p_instance_id'
262 ||' AND PLAN_ID= -1 '
263 || p_sub_str;
264
265
266 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
267
268
269 EXECUTE IMMEDIATE lv_sql_stmt
270 INTO lv_cnt
271 USING p_instance_id;
272
273 IF lv_pbs IS NULL OR
274 lv_cnt < lv_pbs THEN
275
276 v_sql_stmt := 06;
277
278 lv_sql_stmt:= 'DELETE '||p_table_name
279 ||' WHERE SR_INSTANCE_ID= :lv_instance_id'
280 ||' AND PLAN_ID= -1 '
281 || p_sub_str;
282
283 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
284
285
286 EXECUTE IMMEDIATE lv_sql_stmt
287 USING p_instance_id;
288
289 COMMIT;
290
291 ELSE
292 v_sql_stmt := 07;
293 lv_sql_stmt:= 'DELETE '||p_table_name
294 ||' WHERE SR_INSTANCE_ID= :p_instance_id '
295 ||' AND PLAN_ID= -1 '
296 || p_sub_str
297 ||' AND ROWNUM < :lv_pbs';
298
299 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
300
301
302 LOOP
303
304 EXECUTE IMMEDIATE lv_sql_stmt
305 USING p_instance_id, lv_pbs;
306
307 EXIT WHEN SQL%ROWCOUNT= 0;
308
309 COMMIT;
310
311 END LOOP;
312
313 END IF;
314 END IF;
315 END IF;
316
317 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
318 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',
319 TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
320 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,' '||FND_MESSAGE.GET);
321
322 EXCEPTION
323 WHEN OTHERS THEN
324 lv_errtext := substr('DELETE_MSC_TABLE'||'('
325 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
326
327 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,lv_errtext);
328
329 END DELETE_MSC_TABLE;
330
331
332
333 /*=======================================================================
334 This function deletes record from MSC_LOCAL_ID_XXX table
335 =========================================================================*/
336
337 PROCEDURE DELETE_LID_TABLE(p_entity_name IN VARCHAR2,
338 p_lid_table IN VARCHAR2,
339 p_instance_id IN NUMBER,
340 p_where_str IN VARCHAR2) IS
341
342
343 lv_cnt NUMBER;
344 lv_pbs NUMBER;
345 lv_total NUMBER := 0;
346 lv_sql_stmt VARCHAR2(2048);
347 lv_where_str VARCHAR2(2048);
348
349 lv_task_start_time DATE;
350
351 lv_return NUMBER;
352 lv_errtext VARCHAR2(2048);
353 ex_logging_err EXCEPTION;
354
355 BEGIN
356
357 lv_task_start_time:= SYSDATE;
358
359 FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
360 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'DELETE_LID_TABLE:'||p_lid_table);
361 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,' '||FND_MESSAGE.GET);
362
363 lv_where_str := p_where_str;
364 lv_pbs:= TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE'));
365
366 v_sql_stmt := 01;
367 lv_sql_stmt := ' SELECT COUNT(*) '
368 ||' FROM '||p_lid_table||' lid'
369 ||' WHERE lid.instance_id = :p_instance_id'
370 ||' AND lid.entity_name = :p_entity_name'
371 || lv_where_str ;
372
373 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
374
375
376 EXECUTE IMMEDIATE lv_sql_stmt
377 INTO lv_cnt
378 USING p_instance_id,p_entity_name;
379
380
381 IF lv_pbs IS NULL OR
382 lv_cnt < lv_pbs THEN
383
384 v_sql_stmt := 02;
385 lv_sql_stmt := 'DELETE FROM '||p_lid_table||' lid'
386 ||' WHERE lid.instance_id = :p_instance_id'
387 ||' AND lid.entity_name = :p_entity_name'
388 || lv_where_str ;
389
390
391 EXECUTE IMMEDIATE lv_sql_stmt
392 USING p_instance_id,p_entity_name;
393
394 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
395
396
397 lv_total := lv_total+SQL%ROWCOUNT ;
398
399 ELSE
400 v_sql_stmt := 03;
401 lv_sql_stmt := 'DELETE FROM '||p_lid_table||' lid'
402 ||' WHERE lid.instance_id = :p_instance_id'
403 ||' AND lid.entity_name = :p_entity_name'
404 || lv_where_str
405 ||' AND ROWNUM < :lv_pbs';
406
407 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
408
409 lv_total := 0;
410
411 LOOP
412 EXECUTE IMMEDIATE lv_sql_stmt
413 USING p_instance_id,p_entity_name,lv_pbs;
414
415 lv_total := lv_total+ SQL%ROWCOUNT;
416
417 EXIT WHEN SQL%ROWCOUNT = 0;
418
419 COMMIT;
420 END LOOP ;
421 END IF ; -- batch size
422
423
424
425 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Table: '||p_lid_table||' Entity :'||p_entity_name);
426 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
427
428
429 COMMIT;
430
431
432 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
433 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',
434 TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
435 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,' '||FND_MESSAGE.GET);
436
437
438 EXCEPTION
439
440 WHEN OTHERS THEN
441 lv_errtext := substr('DELETE_LID_TABLE'||'('
442 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
443
444 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,lv_errtext);
445
446 END DELETE_LID_TABLE ;
447
448
449 /*==========================================================================+
450 This is the main program that deletes the record from ths MSC_LOCAL_ID_XXX
451 table . This accepts parameter, date upto which the record has to be deleted
452 and different Business Object for which this program should delete LID .
453 If it is complete refresh then it deletes record from the ODS and LID both.
454 +==========================================================================*/
455
456 PROCEDURE PURGE_LID_TABLES(ERRBUF OUT NOCOPY VARCHAR2,
457 RETCODE OUT NOCOPY NUMBER,
458 p_instance_id IN NUMBER,
459 p_complete_refresh IN NUMBER DEFAULT SYS_NO,
460 p_date IN VARCHAR2,
461 p_supply_flag IN NUMBER DEFAULT SYS_NO,
462 p_demand_flag IN NUMBER DEFAULT SYS_NO)
463 IS
464
465 lv_errtext VARCHAR2(5000);
466 lv_where_str VARCHAR2(2048);
467 lv_row_count NUMBER ;
468 lv_return NUMBER;
469 lv_retval BOOLEAN;
470 lv_dummy1 VARCHAR2(32);
471 lv_dummy2 VARCHAR2(32);
472 ex_logging_err EXCEPTION;
473
474 -- from here added for the deletion from lid tables for non complete refresh ( for the bug fix 2229944)--
475
476 lv_total NUMBER := 0;
477 lv_task_start_time DATE;
478 TYPE RowidTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
479 lb_rowid RowidTab;
480
481 CURSOR C1(p_instance_id NUMBER) IS
482 SELECT rowid
483 FROM msc_local_id_demand lid
484 WHERE lid.entity_name ='DISPOSITION_ID_FCT'
485 AND lid.instance_id = p_instance_id
486 MINUS
487 SELECT distinct lid.rowid
488 FROM msc_local_id_demand lid, msc_demands ms
489 WHERE lid.instance_id = p_instance_id
490 AND lid.entity_name = 'DISPOSITION_ID_FCT'
491 AND ms.sales_order_line_id = lid.local_id
492 AND ms.plan_id = -1
493 AND ms.origination_type = 29
494 AND ms.sr_instance_id = lid.instance_id;
495
496 CURSOR C2(p_instance_id NUMBER) IS
497 SELECT rowid
498 FROM msc_local_id_demand lid
499 WHERE lid.entity_name ='DISPOSITION_ID_MDS'
500 AND lid.instance_id = p_instance_id
501 MINUS
502 SELECT distinct lid.rowid
503 FROM msc_local_id_demand lid, msc_demands ms
504 WHERE lid.instance_id = p_instance_id
505 AND lid.entity_name = 'DISPOSITION_ID_MDS'
506 AND ms.disposition_id = lid.local_id
507 AND ms.origination_type = 8
508 AND ms.plan_id = -1
509 AND ms.sr_instance_id = lid.instance_id;
510
511 CURSOR C3(p_instance_id NUMBER) IS
512 SELECT rowid
513 FROM msc_local_id_demand lid
514 WHERE lid.entity_name ='SALES_ORDER_ID'
515 AND lid.instance_id = p_instance_id
516 MINUS
517 SELECT distinct lid.rowid
518 FROM msc_local_id_demand lid, msc_sales_orders ms
519 WHERE lid.instance_id = p_instance_id
520 AND lid.entity_name = 'SALES_ORDER_ID'
521 AND ms.demand_source_header_id = lid.local_id
522 AND ms.sr_instance_id = lid.instance_id;
523
524 CURSOR C4(p_instance_id NUMBER) IS
525 SELECT rowid
526 FROM msc_local_id_demand lid
527 WHERE lid.entity_name ='DEMAND_ID'
528 AND lid.instance_id = p_instance_id
529 MINUS
530 SELECT distinct lid.rowid
531 FROM msc_local_id_demand lid, msc_sales_orders ms
532 WHERE lid.instance_id = p_instance_id
533 AND lid.entity_name = 'DEMAND_ID'
534 AND ms.demand_id = lid.local_id
535 AND ms.sr_instance_id = lid.instance_id;
536
537 CURSOR C5(p_instance_id NUMBER) IS
538 SELECT rowid
539 FROM msc_local_id_supply lid
540 WHERE lid.entity_name ='WIP_ENTITY_ID'
541 AND lid.instance_id = p_instance_id
542 MINUS
543 SELECT distinct lid.rowid
544 FROM msc_local_id_supply lid, msc_supplies ms
545 WHERE lid.instance_id = p_instance_id
546 AND lid.entity_name ='WIP_ENTITY_ID'
547 AND ms.disposition_id = lid.local_id
548 AND ms.order_type IN (3,7,27)
549 AND ms.plan_id = -1
550 AND ms.sr_instance_id = lid.instance_id;
551
552 CURSOR C6(p_instance_id NUMBER) IS
553 SELECT rowid
554 FROM msc_local_id_supply lid
555 WHERE lid.entity_name ='SR_MTL_SUPPLY_ID'
556 AND lid.instance_id = p_instance_id
557 MINUS
558 SELECT distinct lid.rowid
559 FROM msc_local_id_supply lid, msc_supplies ms
560 WHERE lid.instance_id = p_instance_id
561 AND lid.entity_name = 'SR_MTL_SUPPLY_ID'
562 AND ms.sr_mtl_supply_id = lid.local_id
563 AND ms.order_type IN (1,2,8,11,12)
564 AND ms.plan_id = -1
565 AND ms.sr_instance_id = lid.instance_id;
566
567 CURSOR C7(p_instance_id NUMBER) IS
568 SELECT rowid
569 FROM msc_local_id_supply lid
570 WHERE lid.entity_name ='PO_LINE_ID'
571 AND lid.instance_id = p_instance_id
572 MINUS
573 SELECT distinct lid.rowid
574 FROM msc_local_id_supply lid, msc_supplies ms
575 WHERE lid.instance_id = p_instance_id
576 AND lid.entity_name = 'PO_LINE_ID'
577 AND ms.po_line_id = lid.local_id
578 AND ms.order_type IN (1,2,8,11,12)
579 AND ms.plan_id = -1
580 AND ms.sr_instance_id = lid.instance_id;
581
582 CURSOR C8(p_instance_id NUMBER) IS
583 SELECT rowid
584 FROM msc_local_id_supply lid
585 WHERE lid.entity_name ='DISPOSITION_ID'
586 AND lid.instance_id = p_instance_id
587 MINUS
588 SELECT distinct lid.rowid
589 FROM msc_local_id_supply lid, msc_supplies ms
590 WHERE lid.instance_id = p_instance_id
591 AND lid.entity_name = 'DISPOSITION_ID'
592 AND ms.disposition_id = lid.local_id
593 AND ms.order_type IN (1,2,8,11,12)
594 AND ms.plan_id = -1
595 AND ms.sr_instance_id = lid.instance_id;
596
597 CURSOR C9(p_instance_id NUMBER) IS
598 SELECT rowid
599 FROM msc_local_id_supply lid
600 WHERE lid.entity_name ='DISPOSITION_ID_MPS'
601 AND lid.instance_id = p_instance_id
602 MINUS
603 SELECT distinct lid.rowid
604 FROM msc_local_id_supply lid, msc_supplies ms
605 WHERE lid.instance_id = p_instance_id
606 AND lid.entity_name = 'DISPOSITION_ID_MPS'
607 AND ms.disposition_id = lid.local_id
608 AND ms.order_type = 5
609 AND ms.plan_id = -1
610 AND ms.sr_instance_id = lid.instance_id;
611
612 CURSOR C10(p_instance_id NUMBER) IS
613 SELECT rowid
614 FROM msc_local_id_supply lid
615 WHERE lid.entity_name ='SCHEDULE_GROUP_ID'
616 AND lid.instance_id = p_instance_id
617 MINUS
618 SELECT distinct lid. rowid
619 FROM msc_local_id_supply lid, msc_supplies ms
620 WHERE lid.instance_id = p_instance_id
621 AND lid.entity_name = 'SCHEDULE_GROUP_ID'
622 AND ms.schedule_group_id = lid.local_id
623 AND ms.plan_id = -1
624 AND ms.sr_instance_id = lid.instance_id;
625
626 -- till here added for the deletion of lid tables ( for the bug fix 2229944) --
627
628
629 BEGIN
630
631 --========= Setting global variables==============--------------
632 v_current_date := SYSDATE ;
633 v_current_user := FND_GLOBAL.USER_ID ;
634 v_login_user := FND_GLOBAL.CONC_LOGIN_ID;
635 v_request_id := FND_GLOBAL.CONC_REQUEST_ID;
636 v_prog_appl_id := FND_GLOBAL.PROG_APPL_ID;
637 v_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
638
639 v_instance_id := p_instance_id;
640 v_date := fnd_date.canonical_to_date(p_date);
641 lv_retval := FND_INSTALLATION.GET_APP_INFO(
642 'FND', lv_dummy1,lv_dummy2, v_applsys_schema);
643
644
645
646 -- ===== Switch on/ off debug based on MRP: Debug Profile=====--
647
648 v_debug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
649
650
651 --======= Check the Status of the instance.
652 --======= Delete only if the staging table is empty
653
654 IF NOT CHECK_ST_STATUS(p_instance_id => v_instance_id,
655 errbuf => lv_errtext,
656 retcode => v_sql_stmt) THEN
657
658 RAISE ex_logging_err ;
659 END IF;
660
661 ----------------------Complete Referesh ----------------------------
662 IF p_complete_refresh = SYS_YES THEN
663
664 /* if complete refresh, regen the key mapping data */
665
666 DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= v_instance_id;
667 --DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= v_instance_id;
668 --DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= v_instance_id;
669 --DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= v_instance_id;
670 DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
671 --DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1;
672 --DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
673 --DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
674
675 COMMIT;
676
677
678 ----------------------DELETE ITEM ----------------------------
679 DELETE_MSC_TABLE('MSC_SYSTEM_ITEMS', v_instance_id, -1);
680
681 DELETE_LID_TABLE( p_entity_name => 'SR_INVENTORY_ITEM_ID',
682 p_lid_table => 'MSC_LOCAL_ID_ITEM',
683 p_instance_id => v_instance_id ,
684 p_where_str => NULL );
685
686
687 ----------------------DELETE ABC Class ----------------------------
688 DELETE_MSC_TABLE( 'MSC_ABC_CLASSES', v_instance_id, NULL);
689
690 DELETE_LID_TABLE( p_entity_name => 'ABC_CLASS_ID',
691 p_lid_table => 'MSC_LOCAL_ID_MISC',
692 p_instance_id => v_instance_id ,
693 p_where_str => NULL );
694
695 -----------------------DELETE Item Substitutes---------------------------
696 DELETE_MSC_TABLE( 'MSC_ITEM_SUBSTITUTES', v_instance_id, -1);
697 ----------------------DELETE BOM ----------------------------
698 DELETE_MSC_TABLE( 'MSC_BOMS', v_instance_id, -1);
699
700 DELETE_LID_TABLE( p_entity_name => 'BILL_SEQUENCE_ID',
701 p_lid_table => 'MSC_LOCAL_ID_SETUP',
702 p_instance_id => v_instance_id ,
703 p_where_str => NULL );
704
705 DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', v_instance_id, -1);
706
707 DELETE_LID_TABLE( p_entity_name => 'COMPONENT_SEQUENCE_ID',
708 p_lid_table => 'MSC_LOCAL_ID_SETUP',
709 p_instance_id => v_instance_id,
710 p_where_str => NULL );
711
712 DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', v_instance_id, -1);
713
714 -- For OSFM support --
715 DELETE_LID_TABLE( p_entity_name => 'CO_PRODUCT_GROUP_ID',
716 p_lid_table => 'MSC_LOCAL_ID_SETUP',
717 p_instance_id => v_instance_id,
718 p_where_str => NULL );
719
720
721 ---------------------DELETE Routing---------------------------
722
723 DELETE_MSC_TABLE( 'MSC_ROUTINGS', v_instance_id, -1);
724
725 DELETE_LID_TABLE( p_entity_name => 'ROUTING_SEQUENCE_ID',
726 p_lid_table => 'MSC_LOCAL_ID_SETUP',
727 p_instance_id => v_instance_id,
728 p_where_str => NULL );
729
730
731 DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', v_instance_id, -1);
732
733 DELETE_LID_TABLE( p_entity_name => 'OPERATION_SEQUENCE_ID',
734 p_lid_table => 'MSC_LOCAL_ID_SETUP',
735 p_instance_id => v_instance_id,
736 p_where_str => NULL );
737
738 DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', v_instance_id, -1);
739
740 DELETE_LID_TABLE( p_entity_name => 'RESOURCE_SEQ_NUM',
741 p_lid_table => 'MSC_LOCAL_ID_SETUP',
742 p_instance_id => v_instance_id,
743 p_where_str => NULL );
744
745 DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', v_instance_id, -1);
746 DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', v_instance_id, -1);
747 DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', v_instance_id, -1);
748
749 -- Added for OSFM support --
750 DELETE_MSC_TABLE( 'MSC_OPERATION_NETWORKS', v_instance_id, -1);
751
752 --------------------------DELETE ASL-------------------------------------
753 DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', v_instance_id, -1);
754 DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', v_instance_id, -1);
755 DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', v_instance_id, -1);
756 -------------------------------------------------------------------------
757
758 --------------------------DELETE Resource Group--------------------------
759 DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', v_instance_id, NULL);
760 --------------------------DELETE Department/Line ------------------------
761 DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', v_instance_id, -1);
762
763 DELETE_LID_TABLE( p_entity_name => 'DEPARTMENT_ID',
764 p_lid_table => 'MSC_LOCAL_ID_SETUP',
765 p_instance_id => v_instance_id,
766 p_where_str => NULL );
767
768 DELETE_LID_TABLE( p_entity_name => 'LINE_ID',
769 p_lid_table => 'MSC_LOCAL_ID_SETUP',
770 p_instance_id => v_instance_id,
771 p_where_str => NULL );
772
773 DELETE_LID_TABLE( p_entity_name => 'RESOURCE_ID',
774 p_lid_table => 'MSC_LOCAL_ID_SETUP',
775 p_instance_id => v_instance_id,
776 p_where_str => NULL );
777
778 DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', v_instance_id, NULL);
779 DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', v_instance_id, NULL);
780 DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', v_instance_id, NULL);
781 DELETE_MSC_TABLE( 'MSC_RESOURCE_REQUIREMENTS', v_instance_id, -1);
782
783
784 --------------- DELETE PROJECT/TASK----------------------------------
785 DELETE_MSC_TABLE( 'MSC_PROJECTS', v_instance_id, -1);
786
787 DELETE_LID_TABLE( p_entity_name => 'PROJECT_ID',
788 p_lid_table => 'MSC_LOCAL_ID_MISC',
789 p_instance_id => v_instance_id,
790 p_where_str => NULL );
791
792 DELETE_MSC_TABLE( 'MSC_PROJECT_TASKS', v_instance_id, -1);
793
794 DELETE_LID_TABLE( p_entity_name => 'TASK_ID',
795 p_lid_table => 'MSC_LOCAL_ID_MISC',
796 p_instance_id => v_instance_id,
797 p_where_str => NULL );
798
799 DELETE_LID_TABLE( p_entity_name => 'COSTING_GROUP_ID',
800 p_lid_table => 'MSC_LOCAL_ID_MISC',
801 p_instance_id => v_instance_id,
802 p_where_str => NULL );
803
804 --------------- DELETE Demand Class--------------------
805
806 DELETE_MSC_TABLE( 'MSC_DEMAND_CLASSES', v_instance_id, NULL);
807
808 --------------- DELETE Trading Partner--------------------
809 -- For org directly deleting from ODS, as done in MSCCLBAB
810 -- We do not delete vendor an customer from the ODS
811
812 -- DELETE_MSC_TABLE( 'MSC_TRADING_PARTNERS', v_instance_id, NULL,
813 -- 'AND PARTNER_TYPE=3');
814
815 /* DELETE MSC_TRADING_PARTNERS
816 WHERE sr_instance_id= v_instance_id
817 AND partner_type=3;
818
819 DELETE_LID_TABLE( p_entity_name => 'SR_TP_ID',
820 p_lid_table => 'MSC_LOCAL_ID_SETUP',
821 p_instance_id => v_instance_id,
822 p_where_str => NULL );
823
824
825 DELETE_MSC_TABLE( 'MSC_TRADING_PARTNER_SITES', v_instance_id, NULL,
826 'AND PARTNER_TYPE=3');
827
828 DELETE_LID_TABLE( p_entity_name => 'SR_TP_SITE_ID',
829 p_lid_table => 'MSC_LOCAL_ID_SETUP',
830 p_instance_id => v_instance_id,
831 p_where_str => NULL );
832
833 DELETE_MSC_TABLE( 'MSC_PARTNER_CONTACTS', v_instance_id, NULL);
834
835 -- DELETE_MSC_TABLE( 'MSC_LOCATION_ASSOCIATIONS', v_instance_id, NULL);
836
837 DELETE MSC_LOCATION_ASSOCIATIONS
838 WHERE SR_INSTANCE_ID= v_instance_id;
839
840 DELETE_LID_TABLE(p_entity_name => 'LOCATION_ID',
841 p_lid_table => 'MSC_LOCAL_ID_SETUP',
842 p_instance_id => v_instance_id,
843 p_where_str => NULL ); */ --- for legacyno deletion
844
845
846 -----------------------DELETE Planners---------------------------
847 DELETE_MSC_TABLE( 'MSC_PLANNERS', v_instance_id, NULL);
848
849 --------------- DELETE Category---------------------------
850 DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', v_instance_id, NULL);
851
852 DELETE_LID_TABLE( p_entity_name => 'SR_CATEGORY_ID',
853 p_lid_table => 'MSC_LOCAL_ID_MISC',
854 p_instance_id => v_instance_id,
855 p_where_str => NULL );
856
857 /* DELETE_LID_TABLE( p_entity_name => 'SR_CATEGORY_SET_ID',
858 p_lid_table => 'MSC_LOCAL_ID_MISC',
859 p_instance_id => v_instance_id,
860 p_where_str => NULL ); */ -- as we do not purge ODS
861
862 ------------- DELETE Calendar--------------------------------
863 -- For legacy we will not be deleting any calendar tables
864
865 /* DELETE_MSC_TABLE( 'MSC_PERIOD_START_DATES', v_instance_id, NULL);
866
867 DELETE_MSC_TABLE( 'MSC_CAL_YEAR_START_DATES', v_instance_id, NULL);
868
869 DELETE_MSC_TABLE( 'MSC_CAL_WEEK_START_DATES', v_instance_id, NULL);
870
871 DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', v_instance_id, NULL);
872
873 DELETE_MSC_TABLE( 'MSC_CALENDAR_SHIFTS', v_instance_id, NULL);
874
875 DELETE_MSC_TABLE( 'MSC_SHIFT_DATES', v_instance_id, NULL);
876
877 DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', v_instance_id, NULL);
878
879 DELETE_MSC_TABLE( 'MSC_SHIFT_TIMES', v_instance_id, NULL);
880
881 DELETE_MSC_TABLE( 'MSC_SHIFT_EXCEPTIONS', v_instance_id, NULL);
882
883 DELETE_LID_TABLE( p_entity_name => 'SHIFT_NUM',
884 p_lid_table => 'MSC_LOCAL_ID_SETUP',
885 p_instance_id => v_instance_id,
886 p_where_str => NULL ); */
887
888 ------------------DELETE SOURCING--------------------------------
889 DELETE_MSC_TABLE( 'MSC_INTERORG_SHIP_METHODS', v_instance_id,-1);
890 ------------------DELETE SOURCING--------------------------------
891 /* -- not deleting from ODS because of bug 1219661 as done in MSCCLBAB
892
893 DELETE_LID_TABLE( p_entity_name => 'SOURCING_RULE_ID',
894 p_lid_table => 'MSC_LOCAL_ID_MISC',
895 p_instance_id => v_instance_id ,
896 p_where_str => NULL );
897
898 DELETE_LID_TABLE( p_entity_name => 'ASSIGNMENT_SET_ID',
899 p_lid_table => 'MSC_LOCAL_ID_MISC',
900 p_instance_id => v_instance_id,
901 p_where_str => NULL );
902
903
904 DELETE_LID_TABLE( p_entity_name => 'SR_RECEIPT_ID',
905 p_lid_table => 'MSC_LOCAL_ID_MISC',
906 p_instance_id => v_instance_id,
907 p_where_str => NULL );
908
909 DELETE_LID_TABLE( p_entity_name => 'SR_SOURCE_ID',
910 p_lid_table => 'MSC_LOCAL_ID_MISC',
911 p_instance_id => v_instance_id,
912 p_where_str => NULL );
913
914 DELETE_LID_TABLE( p_entity_name => 'ASSIGNMENT_ID',
915 p_lid_table => 'MSC_LOCAL_ID_MISC',
916 p_instance_id => v_instance_id,
917 p_where_str => NULL ); */
918
919 ------------------DELETE UOM-----------------------------------------
920 -- No deletion IN ODS
921 -- No deltion in LID
922
923 ------------------DELETE Designator-----------------------------------------
924
925 UPDATE MSC_DESIGNATORS
926 SET DISABLE_DATE= v_current_date,
927 LAST_UPDATE_DATE= v_current_date,
928 LAST_UPDATED_BY= v_current_user
929 WHERE SR_INSTANCE_ID= v_instance_id
930 AND COLLECTED_FLAG= SYS_YES;
931
932 -----------------------DELETE Safety Stock---------------------------
933 DELETE_MSC_TABLE( 'MSC_SAFETY_STOCKS', v_instance_id, -1);
934
935 -----------------------DELETE Hard Reservations-----------------------
936 DELETE_MSC_TABLE( 'MSC_RESERVATIONS', v_instance_id, -1);
937
938 -----------------------DELETE Demand----------------------------
939 DELETE_MSC_TABLE( 'MSC_DEMANDS', v_instance_id, -1 );
940 DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', v_instance_id, NULL);
941
942
943 DELETE_LID_TABLE( p_entity_name => 'DISPOSITION_ID_FCT',
944 p_lid_table => 'MSC_LOCAL_ID_DEMAND',
945 p_instance_id => v_instance_id ,
946 p_where_str => NULL );
947
948 DELETE_LID_TABLE( p_entity_name => 'DISPOSITION_ID_MDS',
949 p_lid_table => 'MSC_LOCAL_ID_DEMAND',
950 p_instance_id => v_instance_id,
951 p_where_str => NULL );
952
953 DELETE_LID_TABLE( p_entity_name => 'DISPOSITION_ID_FCT',
954 p_lid_table => 'MSC_LOCAL_ID_DEMAND',
955 p_instance_id => v_instance_id,
956 p_where_str => NULL );
957
958 DELETE_LID_TABLE( p_entity_name => 'SALES_ORDER_ID',
959 p_lid_table => 'MSC_LOCAL_ID_DEMAND',
960 p_instance_id => v_instance_id,
961 p_where_str => NULL );
962
963 DELETE_LID_TABLE( p_entity_name => 'DEMAND_ID',
964 p_lid_table => 'MSC_LOCAL_ID_DEMAND',
965 p_instance_id => v_instance_id,
966 p_where_str => NULL );
967
968 -----------------------DELETE Supply----------------------------
969
970 DELETE_MSC_TABLE( 'MSC_SUPPLIES', v_instance_id, -1);
971
972 DELETE_LID_TABLE( p_entity_name => 'DISPOSITION_ID',
973 p_lid_table => 'MSC_LOCAL_ID_SUPPLY',
974 p_instance_id => v_instance_id,
975 p_where_str => NULL );
976
977 DELETE_LID_TABLE( p_entity_name => 'PO_LINE_ID',
978 p_lid_table => 'MSC_LOCAL_ID_SUPPLY',
979 p_instance_id => v_instance_id,
980 p_where_str => NULL );
981
982 DELETE_LID_TABLE( p_entity_name => 'SCHEDULE_GROUP_ID',
983 p_lid_table => 'MSC_LOCAL_ID_SUPPLY',
984 p_instance_id => v_instance_id,
985 p_where_str => NULL );
986
987 DELETE_LID_TABLE( p_entity_name => 'DISPOSTION_ID_MPS',
988 p_lid_table => 'MSC_LOCAL_ID_SUPPLY',
989 p_instance_id => v_instance_id,
990 p_where_str => NULL );
991
992 DELETE_LID_TABLE( p_entity_name => 'SR_MTL_SUPPLY_ID',
993 p_lid_table => 'MSC_LOCAL_ID_SUPPLY',
994 p_instance_id => v_instance_id,
995 p_where_str => NULL );
996
997 DELETE_LID_TABLE( p_entity_name => 'WIP_ENTITY_ID',
998 p_lid_table => 'MSC_LOCAL_ID_SUPPLY',
999 p_instance_id => v_instance_id,
1000 p_where_str => NULL );
1001
1002 -------- Delete OSFM tables ---------------------------
1003
1004 DELETE_MSC_TABLE('MSC_JOB_OPERATION_NETWORKS', v_instance_id, -1);
1005 DELETE_MSC_TABLE('MSC_JOB_OPERATIONS', v_instance_id, -1);
1006 DELETE_MSC_TABLE('MSC_JOB_REQUIREMENT_OPS', v_instance_id, -1);
1007 DELETE_MSC_TABLE('MSC_JOB_OP_RESOURCES', v_instance_id, -1);
1008
1009
1010 ELSE -- if not complete refresh
1011
1012 /*************************************************************************
1013 From here modified for the bug fix 2229944
1014
1015 Note the deletion from lid tables done using cursors,
1016 becuase deletion using co-related sub queries was
1017 becoming a major performance issue without indexes.
1018
1019 *************************************************************************/
1020
1021
1022 IF p_demand_flag = SYS_YES THEN
1023
1024 DELETE_MSC_TABLE( p_table_name =>'MSC_DEMANDS',
1025 p_instance_id => v_instance_id,
1026 p_plan_id => -1,
1027 p_sub_str => ' and trunc(USING_ASSEMBLY_DEMAND_DATE) <= '||'trunc(to_date('||''''||p_date||''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''''||'))');
1028
1029
1030 DELETE_MSC_TABLE( p_table_name =>'MSC_SALES_ORDERS',
1031 p_instance_id => v_instance_id,
1032 p_plan_id => NULL,
1033 p_sub_str => ' and trunc(REQUIREMENT_DATE) <= '||'trunc(to_date('||''''||p_date||''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''''||'))');
1034
1035
1036 -- Delete from MSC_LOCAL_ID_DEMAND for entity_name DISPOSITION_ID_FCT starts here --
1037
1038 lv_task_start_time:= SYSDATE;
1039 lv_total := 0;
1040 v_sql_stmt := 01;
1041
1042
1043 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_DEMAND for entity_name DISPOSITION_ID_FCT' );
1044
1045
1046 OPEN C1(v_instance_id);
1047
1048 FETCH C1 BULK COLLECT INTO lb_rowid ;
1049
1050 IF C1%ROWCOUNT > 0 THEN
1051
1052 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1053
1054 DELETE FROM MSC_LOCAL_ID_DEMAND
1055 WHERE ROWID = lb_rowid(j);
1056
1057 lv_total := C1%ROWCOUNT;
1058
1059 END IF;
1060
1061 CLOSE C1;
1062
1063 COMMIT;
1064
1065 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
1066
1067
1068 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
1069 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
1070 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,' '||FND_MESSAGE.GET);
1071
1072 -- Delete from MSC_LOCAL_ID_DEMAND for entity_name DISPOSITION_ID_FCT ends here----
1073
1074
1075 -- Delete from MSC_LOCAL_ID_DEMAND for entity_name DISPOSITION_ID_MDS starts here --
1076
1077 lv_task_start_time:= SYSDATE;
1078 lv_total := 0;
1079 v_sql_stmt := 02;
1080
1081 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_DEMAND for entity_name DISPOSITION_ID_MDS');
1082
1083
1084 OPEN C2(v_instance_id);
1085
1086 FETCH C2 BULK COLLECT INTO lb_rowid ;
1087
1088 IF C2%ROWCOUNT > 0 THEN
1089
1090 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1091
1092 DELETE FROM MSC_LOCAL_ID_DEMAND
1093 WHERE ROWID = lb_rowid(j);
1094
1095 lv_total := C2%ROWCOUNT;
1096
1097 END IF;
1098
1099 CLOSE C2;
1100
1101 COMMIT;
1102
1103
1104 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
1105
1106
1107 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
1108 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
1109 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,' '||FND_MESSAGE.GET);
1110
1111 -- Delete from MSC_LOCAL_ID_DEMAND for entity_name DISPOSITION_ID_MDS ends here----
1112
1113
1114 -- Delete from MSC_LOCAL_ID_DEMAND for entity_name SALES_ORDER_ID starts here --
1115
1116 lv_task_start_time:= SYSDATE;
1117 lv_total := 0;
1118 v_sql_stmt := 03;
1119
1120 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_DEMAND for entity_name SALES_ORDER_ID');
1121
1122
1123 OPEN C3(v_instance_id);
1124
1125 FETCH C3 BULK COLLECT INTO lb_rowid ;
1126
1127 IF C3%ROWCOUNT > 0 THEN
1128
1129 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1130
1131 DELETE FROM MSC_LOCAL_ID_DEMAND
1132 WHERE ROWID = lb_rowid(j);
1133
1134 lv_total := C3%ROWCOUNT;
1135
1136 END IF;
1137
1138 CLOSE C3;
1139
1140 COMMIT;
1141
1142
1143 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
1144
1145 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
1146 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
1147 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,' '||FND_MESSAGE.GET);
1148
1149 -- Deletion from MSC_LOCAL_ID_DEMAND for entity_name SALES_ORDER_ID ends here----
1150
1151
1152 -- Delete from MSC_LOCAL_ID_DEMAND for entity_name DEMAND_ID starts here --
1153
1154 lv_task_start_time:= SYSDATE;
1155 lv_total := 0;
1156 v_sql_stmt := 04;
1157
1158 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_DEMAND for entity_name DEMAND_ID');
1159
1160
1161 OPEN C4(v_instance_id);
1162
1163 FETCH C4 BULK COLLECT INTO lb_rowid ;
1164
1165 IF C4%ROWCOUNT > 0 THEN
1166
1167 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1168
1169 DELETE FROM MSC_LOCAL_ID_DEMAND
1170 WHERE ROWID = lb_rowid(j);
1171
1172 lv_total := C4%ROWCOUNT;
1173
1174 END IF;
1175
1176 CLOSE C4;
1177
1178 COMMIT;
1179
1180 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
1181
1182 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
1183 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
1184 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,' '||FND_MESSAGE.GET);
1185
1186 -- Deletion from MSC_LOCAL_ID_DEMAND for entity_name DEMAND_ID ends here ----
1187
1188
1189 END IF; -- p_demand_flag
1190
1191
1192 IF p_supply_flag = SYS_YES THEN
1193 DELETE_MSC_TABLE( p_table_name =>'MSC_SUPPLIES',
1194 p_instance_id => v_instance_id,
1195 p_plan_id => -1,
1196 p_sub_str => ' and trunc(NEW_SCHEDULE_DATE) <= '||'trunc(to_date('||''''||p_date||''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''''||'))');
1197
1198
1199 -- Delete from MSC_LOCAL_ID_SUPPLY for entity_name WIP_ENTITY_IDstarts here --
1200
1201 lv_task_start_time:= SYSDATE;
1202 lv_total := 0;
1203 v_sql_stmt := 05;
1204
1205 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name WIP_ENTITY_ID');
1206
1207 OPEN C5(v_instance_id);
1208
1209 FETCH C5 BULK COLLECT INTO lb_rowid ;
1210 IF C5%ROWCOUNT > 0 THEN
1211
1212 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1213
1214 DELETE FROM MSC_LOCAL_ID_SUPPLY
1215 WHERE ROWID = lb_rowid(j);
1216
1217 lv_total := C5%ROWCOUNT;
1218
1219 END IF;
1220
1221 CLOSE C5;
1222
1223 COMMIT;
1224
1225 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
1226
1227
1228 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
1229 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
1230 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,' '||FND_MESSAGE.GET);
1231
1232 -- Deletion from MSC_LOCAL_ID_SUPPLY for entity_name WIP_ENTITY_ID ends here ----
1233
1234
1235 -- Delete from MSC_LOCAL_ID_SUPPLY for entity_name SR_MTL_SUPPLY_ID starts here --
1236
1237 lv_task_start_time:= SYSDATE;
1238 lv_total := 0;
1239 v_sql_stmt := 06;
1240
1241 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name SR_MTL_SUPPLY_ID');
1242
1243
1244 OPEN C6(v_instance_id);
1245
1246 FETCH C6 BULK COLLECT INTO lb_rowid ;
1247
1248 IF C6%ROWCOUNT > 0 THEN
1249
1250 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1251
1252 DELETE FROM MSC_LOCAL_ID_SUPPLY
1253 WHERE ROWID = lb_rowid(j);
1254
1255 lv_total := C6%ROWCOUNT;
1256
1257 END IF;
1258
1259 CLOSE C6;
1260
1261 COMMIT;
1262
1263 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
1264
1265
1266 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
1267 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
1268 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,' '||FND_MESSAGE.GET);
1269
1270 -- Deletion from MSC_LOCAL_ID_SUPPLY for entity_name SR_MTL_SUPPLY_ID ends here ----
1271
1272
1273 -- Delete from MSC_LOCAL_ID_SUPPLY for entity_name PO_LINE_ID starts here --
1274
1275 lv_task_start_time:= SYSDATE;
1276 lv_total := 0;
1277 v_sql_stmt := 07;
1278
1279 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name PO_LINE_ID');
1280
1281 OPEN C7(v_instance_id);
1282
1283 FETCH C7 BULK COLLECT INTO lb_rowid ;
1284
1285 IF C7%ROWCOUNT > 0 THEN
1286
1287 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1288
1289 DELETE FROM MSC_LOCAL_ID_SUPPLY
1290 WHERE ROWID = lb_rowid(j);
1291
1292 lv_total := C7%ROWCOUNT;
1293
1294 END IF;
1295
1296 CLOSE C7;
1297
1298 COMMIT;
1299
1300 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
1301
1302
1303 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
1304 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
1305 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,' '||FND_MESSAGE.GET);
1306
1307 -- Deletion from MSC_LOCAL_ID_SUPPLY for entity_name PO_LINE_ID ends here ----
1308
1309 -- Delete from MSC_LOCAL_ID_SUPPLY for entity_name DISPOSITION_ID starts here --
1310
1311 lv_task_start_time:= SYSDATE;
1312 lv_total := 0;
1313 v_sql_stmt := 08;
1314
1315 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name DISPOSITION_ID');
1316
1317
1318 OPEN C8(v_instance_id);
1319
1320 FETCH C8 BULK COLLECT INTO lb_rowid ;
1321
1322 IF C8%ROWCOUNT > 0 THEN
1323
1324 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1325
1326 DELETE FROM MSC_LOCAL_ID_SUPPLY
1327 WHERE ROWID = lb_rowid(j);
1328
1329 lv_total := C8%ROWCOUNT;
1330
1331 END IF;
1332
1333 CLOSE C8;
1334
1335 COMMIT;
1336
1337 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
1338
1339
1340 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
1341 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
1342 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,' '||FND_MESSAGE.GET);
1343
1344 -- Deletion from MSC_LOCAL_ID_SUPPLY for entity_name DISPOSITION_ID ends here ----
1345
1346
1347 -- Delete from MSC_LOCAL_ID_SUPPLY for entity_name DISPOSITION_ID_MPS starts here --
1348
1349 lv_task_start_time:= SYSDATE;
1350 lv_total := 0;
1351 v_sql_stmt := 09;
1352
1353 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name DISPOSITION_ID_MPS');
1354
1355
1356 OPEN C9(v_instance_id);
1357
1358 FETCH C9 BULK COLLECT INTO lb_rowid ;
1359
1360 IF C9%ROWCOUNT > 0 THEN
1361
1362 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1363
1364 DELETE FROM MSC_LOCAL_ID_SUPPLY
1365 WHERE ROWID = lb_rowid(j);
1366
1367 lv_total := C9%ROWCOUNT;
1368
1369 END IF;
1370
1371 CLOSE C9;
1372
1373 COMMIT;
1374
1375 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);
1376
1377
1378 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
1379 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
1380 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,' '||FND_MESSAGE.GET);
1381
1382 -- Deletion from MSC_LOCAL_ID_SUPPLY for entity_name DISPOSITION_ID_MPS ends here ----
1383
1384
1385 -- Delete from MSC_LOCAL_ID_SUPPLY for entity_name SCHEDULE_GROUP_ID starts here --
1386
1387 lv_task_start_time:= SYSDATE;
1388 lv_total := 0;
1389 v_sql_stmt := 10;
1390
1391 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name SCHEDULE_GROUP_ID');
1392
1393
1394 OPEN C10(v_instance_id);
1395
1396 FETCH C10 BULK COLLECT INTO lb_rowid ;
1397
1398 IF C10%ROWCOUNT > 0 THEN
1399
1400 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1401
1402 DELETE FROM MSC_LOCAL_ID_SUPPLY
1403 WHERE ROWID = lb_rowid(j);
1404
1405 lv_total := C10%ROWCOUNT;
1406
1407 END IF;
1408
1409 CLOSE C10;
1410
1411 COMMIT;
1412
1413 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,' No of Rows deleted : '||lv_total);
1414
1415
1416 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
1417 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
1418 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,' '||FND_MESSAGE.GET);
1419
1420 -- Deletion from MSC_LOCAL_ID_SUPPLY for entity_name SCHEDULE_GROUP_ID ends here ----
1421 END IF ; -- p_suply_flag
1422 END IF ; -- Complete refresh
1423
1424 --------- Till here modified for the bug fix 2229944 --------------
1425
1426
1427 FND_MESSAGE.SET_NAME('MSC', 'MSC_PP_PURGE_SUCCEED');
1428 ERRBUF:= FND_MESSAGE.GET;
1429 RETCODE := G_SUCCESS;
1430 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, ERRBUF);
1431 RETCODE := G_SUCCESS;
1432
1433
1434 EXCEPTION
1435
1436 WHEN ex_logging_err THEN
1437 ERRBUF := lv_errtext;
1438 RETCODE := G_ERROR;
1439 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,lv_errtext);
1440
1441 WHEN OTHERS THEN
1442 ERRBUF := SQLERRM;
1443 RETCODE := SQLCODE;
1444 lv_errtext := substr(v_sql_stmt||SQLERRM,1,240) ;
1445 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,lv_errtext);
1446 END PURGE_LID_TABLES ;
1447
1448 PROCEDURE PURGE_ODS_TABLES_DEL( p_instance_id IN NUMBER) IS
1449
1450 BEGIN
1451 ---------------- BOM --------------------
1452 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', p_instance_id, NULL);
1453 COMMIT;
1454 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', p_instance_id, NULL);
1455 COMMIT;
1456
1457 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', p_instance_id, NULL);
1458 COMMIT;
1459 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', p_instance_id, NULL);
1460 COMMIT;
1461
1462 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', p_instance_id, NULL);
1463 COMMIT;
1464 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', p_instance_id, NULL);
1465 COMMIT;
1466 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', p_instance_id, NULL);
1467 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_OPERATION_NETWORKS',p_instance_id,NULL);
1468 COMMIT;
1469
1470 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', p_instance_id, NULL);
1471 COMMIT;
1472
1473 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', p_instance_id, NULL);
1474 COMMIT;
1475 ---------------- BOR -------------------
1476 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', p_instance_id, NULL);
1477 COMMIT;
1478 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', p_instance_id, NULL);
1479 COMMIT;
1480 ---------------- CALENDAR_DATE -------------
1481 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_DATES', p_instance_id, NULL);
1482 COMMIT;
1483 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PERIOD_START_DATES', p_instance_id, NULL);
1484 COMMIT;
1485 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_YEAR_START_DATES', p_instance_id, NULL);
1486 COMMIT;
1487 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_WEEK_START_DATES', p_instance_id, NULL);
1488 COMMIT;
1489 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', p_instance_id, NULL);
1490
1491 COMMIT;
1492 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_SHIFTS', p_instance_id, NULL);
1493 COMMIT;
1494 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_DATES', p_instance_id, NULL);
1495 COMMIT;
1496 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', p_instance_id, NULL);
1497 COMMIT;
1498 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_TIMES', p_instance_id, NULL);
1499 COMMIT;
1500 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_EXCEPTIONS', p_instance_id, NULL);
1501 COMMIT;
1502 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'debug-02');
1503 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', p_instance_id, NULL);
1504 COMMIT;
1505 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', p_instance_id, NULL);
1506 COMMIT;
1507 ---------------- CATEGORY -------------
1508 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', p_instance_id, NULL);
1509 COMMIT;
1510 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CATEGORY_SETS', p_instance_id, NULL);
1511 COMMIT;
1512 ---------------- DEMAND -------------
1513 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEMANDS', p_instance_id, NULL);
1514 COMMIT;
1515 ---------------- SALES ORDER -------------
1516 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', p_instance_id, NULL);
1517 COMMIT;
1518 ---------------- HARD RESERVATION -------------
1519 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', p_instance_id, NULL);
1520 COMMIT;
1521 ---------------- ITEM -------------
1522 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', p_instance_id, NULL);
1523 COMMIT;
1524 ---------------- RESOURCE -------------
1525 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', p_instance_id, NULL);
1526 COMMIT;
1527 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', p_instance_id, NULL);
1528 COMMIT;
1529 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', p_instance_id, NULL);
1530 COMMIT;
1531 ---------------- SAFETY STOCK-------------
1532 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SAFETY_STOCKS', p_instance_id, NULL);
1533 COMMIT;
1534 ---------------- SCHEDULE DESIGNATOR -------------
1535 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DESIGNATORS', p_instance_id, NULL);
1536 COMMIT;
1537 ---------------- SOURCING -------------
1538 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ASSIGNMENT_SETS', p_instance_id, NULL);
1539 COMMIT;
1540 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SOURCING_RULES', p_instance_id, NULL);
1541 COMMIT;
1542 DELETE FROM MSC_SR_ASSIGNMENTS
1543 WHERE SR_ASSIGNMENT_INSTANCE_ID= p_instance_id;
1544 COMMIT;
1545 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SR_RECEIPT_ORG', p_instance_id, NULL);
1546 COMMIT;
1547 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SR_SOURCE_ORG', p_instance_id, NULL);
1548 COMMIT;
1549 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_INTERORG_SHIP_METHODS', p_instance_id, NULL);
1550 COMMIT;
1551 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CARRIER_SERVICES', p_instance_id, NULL);
1552 COMMIT;
1553 ---------------- SUB INVENTORY -------------
1554 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUB_INVENTORIES', p_instance_id, NULL);
1555 COMMIT;
1556 ---------------- SUPPLIER CAPACITY -------------
1557 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', p_instance_id, NULL);
1558 COMMIT;
1559 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', p_instance_id, NULL);
1560 COMMIT;
1561 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', p_instance_id, NULL);
1562 COMMIT;
1563 ---------------- SUPPLY -------------
1564 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', p_instance_id, NULL);
1565 COMMIT;
1566 ---------------- RESOURCE REQUIREMENT -------------
1567 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_REQUIREMENTS', p_instance_id, NULL);
1568 COMMIT;
1569 ---------------- TRADING PARTNER -------------
1570 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRADING_PARTNERS', p_instance_id, NULL);
1571 COMMIT;
1572 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRADING_PARTNER_SITES', p_instance_id, NULL);
1573 COMMIT;
1574 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_LOCATION_ASSOCIATIONS', p_instance_id, NULL);
1575 COMMIT;
1576 ---------------- UNIT NUMBER -------------
1577 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_UNIT_NUMBERS', p_instance_id, NULL);
1578 COMMIT;
1579 ---------------- PROJECT -------------
1580 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROJECTS', p_instance_id, NULL);
1581 COMMIT;
1582 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROJECT_TASKS', p_instance_id, NULL);
1583 COMMIT;
1584 ---------------- PARAMETER -------------
1585 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PARAMETERS', p_instance_id, NULL);
1586 COMMIT;
1587 ---------------- UOM -------------
1588 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_UNITS_OF_MEASURE', p_instance_id, NULL);
1589 COMMIT;
1590 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_UOM_CLASS_CONVERSIONS', p_instance_id, NULL);
1591 COMMIT;
1592 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_UOM_CONVERSIONS', p_instance_id, NULL);
1593 COMMIT;
1594 ---------------- BIS -------------
1595 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BIS_PERFORMANCE_MEASURES', p_instance_id, NULL);
1596 COMMIT;
1597 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BIS_TARGET_LEVELS', p_instance_id, NULL);
1598 COMMIT;
1599 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BIS_TARGETS', p_instance_id, NULL);
1600 COMMIT;
1601 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BIS_BUSINESS_PLANS', p_instance_id, NULL);
1602 COMMIT;
1603 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BIS_PERIODS', p_instance_id, NULL);
1604 COMMIT;
1605 ---------------- ATP RULES -------------
1606 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ATP_RULES', p_instance_id, NULL);
1607 COMMIT;
1608 ---------------- PLANNERS -------------
1609 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PLANNERS', p_instance_id, NULL);
1610 COMMIT;
1611 ---------------- DEMAND CLASS -------------
1612 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEMAND_CLASSES', p_instance_id, NULL);
1613 COMMIT;
1614 ---------------- PARTNER CONTACTS -----------
1615 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PARTNER_CONTACTS', p_instance_id, NULL);
1616 COMMIT;
1617 ---------------- LEGACY TABLES --------------
1618 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ITEM_SOURCING',p_instance_id, NULL);
1619 COMMIT;
1620 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_CALENDARS',p_instance_id, NULL);
1621 COMMIT;
1622 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_WORKDAY_PATTERNS',p_instance_id, NULL);
1623 COMMIT;
1624 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_CALENDAR_EXCEPTIONS',p_instance_id, NULL);
1625 COMMIT;
1626 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_GROUPS',p_instance_id, NULL);
1627 COMMIT;
1628 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_GROUP_COMPANIES',p_instance_id, NULL);
1629 COMMIT;
1630 ---------------- TRIP --------------
1631 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_TRIPS',p_instance_id, NULL);
1632 COMMIT;
1633 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_TRIP_STOPS',p_instance_id, NULL);
1634 COMMIT;
1635
1636 /* ds_change: start */
1637 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES',p_instance_id, NULL);
1638 commit;
1639 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL',p_instance_id, NULL);
1640 commit;
1641 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_JOB_OP_RES_INSTANCES',p_instance_id, NULL);
1642 commit;
1643 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_INSTANCE_REQS',p_instance_id, NULL);
1644 commit;
1645 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SETUPS',p_instance_id, NULL);
1646 commit;
1647 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SETUP_TRANSITIONS',p_instance_id, NULL);
1648 commit;
1649 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_STD_OP_RESOURCES',p_instance_id, NULL);
1650 commit;
1651 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RES_INSTANCE_CHANGES',p_instance_id, NULL);
1652 commit;
1653 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_CHARGES',p_instance_id, NULL);
1654 commit;
1655 /* ds_change: end */
1656 MSC_CL_COLLECTION.DELETE_MSC_TABLE ('MSC_SR_LOOKUPS', p_instance_id, NULL);
1657 commit;
1658 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_MONTHS',p_instance_id, NULL);
1659 commit;
1660
1661 END PURGE_ODS_TABLES_DEL;
1662 --=======================================================================
1663
1664 FUNCTION GET_M2A_DBLINK(pInstId NUMBER) RETURN VARCHAR
1665
1666 IS
1667
1668 lv_dblink VARCHAR2(30);
1669
1670 BEGIN
1671
1672 SELECT DECODE( M2A_DBLINK, NULL, ' ', '@'||M2A_DBLINK)
1673
1674 INTO lv_dblink
1675
1676 FROM MSC_APPS_INSTANCES
1677
1678 WHERE INSTANCE_ID= pInstId;
1679
1680
1681
1682 RETURN lv_dblink;
1683
1684 EXCEPTION
1685
1686 WHEN NO_DATA_FOUND THEN
1687
1688 RETURN NULL;
1689
1690 END GET_M2A_DBLINK;
1691
1692 --=======================================================================
1693 PROCEDURE PURGE_INSTANCE_DATA( ERRBUF OUT NOCOPY VARCHAR2,
1694
1695 RETCODE OUT NOCOPY NUMBER,
1696
1697 pInstList tblTyp)
1698
1699 IS
1700
1701 TYPE cur_typ IS REF CURSOR;
1702
1703 v_index_cur cur_typ;
1704
1705 lv_inst_str VARCHAR2(2000);
1706
1707 lv_qry_str VARCHAR2(4000);
1708
1709 lv_tab VARCHAR2(30);
1710
1711 lv_tab_Part VARCHAR2(30);
1712
1713 row_limit number;
1714
1715 lv_dummy1 VARCHAR2(30);
1716
1717 lv_dummy2 VARCHAR2(30);
1718
1719 lv_schema VARCHAR2(30);
1720
1721 lv_source_schema VARCHAR2(30);
1722
1723 lv_schema_short_nm VARCHAR2(30);
1724
1725 lv_err_flag BOOLEAN :=FALSE;
1726
1727 BEGIN
1728
1729
1730
1731
1732
1733 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'******* Prcocedure PURGE_INSTANCE_DATA *******');
1734
1735 -- Get row_limit and schema
1736
1737 BEGIN
1738
1739
1740
1741 SELECT application_short_name
1742
1743 INTO lv_schema_short_nm
1744
1745 FROM fnd_application
1746
1747 WHERE application_id=724;
1748
1749
1750
1751 IF NOT FND_INSTALLATION.GET_APP_INFO ( lv_schema_short_nm, lv_dummy1, lv_dummy2, lv_schema) THEN
1752
1753 ERRBUF := lv_schema_short_nm||'--Schema not found';
1754
1755 RETCODE := MSC_UTIL.G_ERROR;
1756
1757 RETURN;
1758
1759 END IF;
1760
1761 EXCEPTION
1762
1763 WHEN NO_DATA_FOUND THEN
1764
1765 ERRBUF := 'Schema Short name for application_id 724 Not found';
1766
1767 RETCODE := MSC_UTIL.G_ERROR;
1768
1769 RETURN;
1770
1771 END;
1772
1773
1774
1775 row_limit := TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE'));
1776
1777 --
1778
1779 -- Generate List of Instances passed --
1780
1781 lv_inst_str := '(';
1782
1783 FOR indx IN pInstList.FIRST .. pInstList.LAST
1784
1785 LOOP
1786
1787 lv_inst_str := lv_inst_str ||''''|| pInstList(indx)||'''' || ',' ;
1788
1789 END LOOP;
1790
1791 lv_inst_str := substr(lv_inst_str,1,length(lv_inst_str)-1) || ')';
1792
1793 --End Generate List of Instances passed --
1794
1795
1796
1797 lv_qry_str := 'SELECT table_name,partition_name '
1798
1799 ||' FROM fnd_lookup_values a,DBA_TAB_PARTITIONS b'
1800
1801 ||' WHERE a.attribute2 = b.table_name' -- (Not in MSC_%)see that meaning is there in upper case
1802
1803 ||' AND b.table_owner = :B1'
1804
1805 ||' AND a.lookup_type IN (''MSC_ODS_TABLE'',''MSC_OTHER_TABLE'')'-- see that staging table can be included here
1806
1807 ||' AND a.ATTRIBUTE11 = ''Y''' -- Column SR_INSTANCE_ID Present
1808
1809 ||' AND a.enabled_flag = ''Y'''
1810
1811 ||' AND a.view_application_id = 700'
1812
1813 ||' AND a.language = userenv(''lang'')'
1814
1815 ||' AND a.attribute5 != ''U''' -- Table is Partitioned
1816
1817 ||' AND NVL(a.attribute13,''-1'')!=''G'''
1818
1819 ||' AND b.partition_name like substr( a.attribute2,5)||''%'''
1820
1821 --AND INSTR(partition_name,'__') > 0
1822
1823 ||' AND SUBSTR(b.partition_name,INSTR(partition_name,''__'')+2) IN '||lv_inst_str;
1824
1825
1826
1827 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,lv_qry_str);
1828
1829 --EXECUTE IMMEDIATE lv_qry_str1 INTO lv_tab_list,lv_Index_list ;
1830
1831 BEGIN
1832
1833 OPEN v_index_cur FOR lv_qry_str USING lv_schema;
1834
1835 LOOP
1836
1837 FETCH v_index_cur INTO lv_tab, lv_tab_Part;
1838
1839 EXIT WHEN v_index_cur%NOTFOUND;
1840
1841 BEGIN
1842
1843 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'ALTER TABLE '||lv_schema||'.'||lv_tab||' DROP PARTITION '||lv_tab_Part);
1844
1845 EXECUTE IMMEDIATE 'ALTER TABLE '||lv_schema||'.'||lv_tab||' DROP PARTITION '||lv_tab_Part ;
1846
1847 EXCEPTION
1848
1849 WHEN OTHERS THEN
1850
1851 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'ERROR IN DEL_INST --'||SQLERRM);
1852
1853 RETCODE := MSC_UTIL.G_WARNING;
1854
1855 END;
1856
1857 END LOOP;
1858
1859 CLOSE v_index_cur;
1860
1861 EXCEPTION
1862
1863 WHEN OTHERS THEN
1864
1865 ERRBUF := SQLERRM;
1866
1867 RETCODE := MSC_UTIL.G_ERROR;
1868
1869 RETURN;
1870
1871 END;
1872
1873
1874
1875 -- make inst str number only
1876
1877 SELECT REPLACE(lv_inst_str,'''','') INTO lv_inst_str FROM DUAL;
1878
1879 --
1880
1881 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'******* Delete Rows for Instance *******');
1882
1883 FOR tab IN ( SELECT attribute2 tname,ATTRIBUTE12 Plan_Id
1884
1885 FROM fnd_lookup_values a, dba_tables b
1886
1887 WHERE a.attribute2 = b.table_name
1888
1889 AND b.owner = lv_schema
1890
1891 AND lookup_type IN ('MSC_ODS_TABLE','MSC_OTHER_TABLE')
1892
1893 AND enabled_flag = 'Y'
1894
1895 AND view_application_id = 700
1896
1897 AND language = userenv('lang')
1898
1899 AND attribute5='U' -- Unpartitioned table
1900
1901 AND a.ATTRIBUTE11 = 'Y' -- Column SR_INSTANCE_ID Present
1902
1903 AND NVL(attribute13,'-1')<>'G') -- to check
1904
1905 LOOP
1906
1907 IF tab.Plan_Id = 'Y' THEN
1908
1909 lv_qry_str := 'DELETE FROM '||lv_schema||'.'||tab.tname||' WHERE plan_id= -1 AND sr_instance_id IN '||lv_inst_str||' AND ROWNUM <= '||row_limit;
1910
1911 -- TRC('DELETE FROM '||tab.tname||' WHERE plan_id= -1 AND sr_instance_id IN '||lv_inst_str);
1912
1913 ELSE
1914
1915 lv_qry_str := 'DELETE FROM '||lv_schema||'.'||tab.tname||' WHERE sr_instance_id IN '||lv_inst_str||' AND ROWNUM <= '||row_limit;
1916
1917 -- TRC('DELETE FROM '||tab.tname||' WHERE sr_instance_id IN '||lv_inst_str);
1918
1919 END IF;
1920
1921 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,lv_qry_str);
1922
1923
1924
1925 -- Delete Using Rownum Limitation
1926
1927 LOOP
1928
1929 lv_err_flag := FALSE;
1930
1931 BEGIN
1932
1933 EXECUTE IMMEDIATE lv_qry_str ;
1934
1935 EXCEPTION
1936
1937 WHEN OTHERS THEN
1938
1939 RETCODE := MSC_UTIL.G_WARNING;
1940
1941 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'Error while deleting from '||tab.tname|| '--'||SQLERRM);
1942
1943 lv_err_flag := TRUE;
1944
1945 END;
1946
1947 EXIT WHEN (SQL%ROWCOUNT < row_limit) OR (lv_err_flag = TRUE); -- Exit when Not enough rows to be deleted;
1948
1949 COMMIT; -- After Commit SQL%ROWCOUNT always returns 0 <-- check whether you can get the desired row count after commit
1950
1951 END LOOP;
1952
1953
1954
1955 END LOOP;
1956
1957
1958
1959
1960
1961
1962
1963 -- Delete Instance from MSC_INST_PARTITIONS, MSC_APPS_INSTANCES, MRP_AP_APPS_INSTANCES_ALL GET_DBLINK(pInstId NUMBER)
1964
1965
1966
1967 -- get source schema short name.
1968
1969 BEGIN
1970
1971 SELECT application_short_name
1972
1973 INTO lv_schema_short_nm
1974
1975 FROM fnd_application
1976
1977 WHERE application_id=704;
1978
1979 EXCEPTION
1980
1981 WHEN NO_DATA_FOUND THEN
1982
1983 ERRBUF := 'Schema Short name for application_id 704 Not found';
1984
1985 RETCODE := MSC_UTIL.G_WARNING;
1986
1987 END;
1988
1989
1990
1991
1992
1993 -- Delete from Source MRP_AP_APPS_INSTANCES_ALL using M2A DBLINK.
1994
1995 IF FND_INSTALLATION.GET_APP_INFO ( lv_schema_short_nm, lv_dummy1, lv_dummy2, lv_source_schema) THEN --schema name exist
1996
1997
1998
1999 FOR indx IN pInstList.FIRST .. pInstList.LAST
2000
2001 LOOP
2002
2003 BEGIN
2004
2005 lv_qry_str := 'DELETE FROM '||lv_source_schema ||'.MRP_AP_APPS_INSTANCES_ALL'||GET_M2A_DBLINK(pInstList(indx) )
2006
2007 ||' WHERE instance_id IN '||lv_inst_str;
2008
2009 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,lv_qry_str);
2010
2011 EXECUTE IMMEDIATE lv_qry_str;
2012
2013 EXCEPTION
2014
2015 WHEN OTHERS THEN
2016
2017 RETCODE := MSC_UTIL.G_WARNING;
2018
2019 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'Error while deleting from MRP_AP_APPS_INSTANCES_ALL --'||SQLERRM);
2020
2021 END;
2022
2023 END LOOP;
2024
2025 ELSE
2026
2027 ERRBUF := 'Source Schema not found';
2028
2029 RETCODE := MSC_UTIL.G_WARNING;
2030
2031 END IF;
2032
2033 --
2034
2035
2036
2037 BEGIN -- delete from MSC_INST_PARTITIONS --
2038
2039 lv_qry_str := 'DELETE FROM '||lv_schema||'.MSC_INST_PARTITIONS WHERE instance_id IN '||lv_inst_str;
2040
2041 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,lv_qry_str);
2042
2043 EXECUTE IMMEDIATE lv_qry_str;
2044
2045 EXCEPTION
2046
2047 WHEN OTHERS THEN
2048
2049 RETCODE := MSC_UTIL.G_WARNING;
2050
2051 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'Error while deleting from MSC_INST_PARTITIONS --'||SQLERRM);
2052
2053 END;
2054
2055 --
2056
2057 BEGIN -- delete from MSC_APPS_INSTANCES --
2058
2059 lv_qry_str := 'DELETE FROM '||lv_schema||'.MSC_APPS_INSTANCES WHERE instance_id IN '||lv_inst_str;
2060
2061 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,lv_qry_str);
2062
2063 EXECUTE IMMEDIATE lv_qry_str;
2064
2065 EXCEPTION
2066
2067 WHEN OTHERS THEN
2068
2069 RETCODE := MSC_UTIL.G_WARNING;
2070
2071 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'Error while deleting from MSC_APPS_INSTANCES --'||SQLERRM);
2072
2073 END;
2074
2075 COMMIT;
2076
2077 --
2078
2079 IF NVL(RETCODE,-1) <> MSC_UTIL.G_WARNING THEN
2080
2081 RETCODE := MSC_UTIL.G_SUCCESS;
2082
2083 END IF;
2084
2085 EXCEPTION
2086
2087 WHEN OTHERS THEN
2088
2089 ERRBUF := SQLERRM;
2090
2091 RETCODE := MSC_UTIL.G_ERROR;
2092
2093 END PURGE_INSTANCE_DATA;
2094
2095 ---------------------------------PURGE_PLAN_DATA----------------------------------------
2096
2097
2098
2099 PROCEDURE PURGE_PLAN_DATA( ERRBUF OUT NOCOPY VARCHAR2,
2100
2101 RETCODE OUT NOCOPY NUMBER,
2102
2103 pPlanList tblTyp)
2104
2105 IS
2106
2107
2108
2109 TYPE cur_typ IS REF CURSOR;
2110
2111 v_plan_part_cur cur_typ;
2112
2113 lv_plan_str VARCHAR2(2000);
2114
2115 lv_qry_str VARCHAR2(4000);
2116
2117 lv_tab VARCHAR2(30);
2118
2119 lv_tab_Part VARCHAR2(30);
2120
2121 row_limit number;
2122
2123 lv_dummy1 VARCHAR2(30);
2124
2125 lv_dummy2 VARCHAR2(30);
2126
2127 lv_schema VARCHAR2(30);
2128
2129 lv_schema_short_nm VARCHAR2(30);
2130
2131 lv_err_flag BOOLEAN:=FALSE;
2132
2133
2134
2135 BEGIN
2136
2137
2138
2139
2140
2141 -- Get row_limit and schema
2142
2143 BEGIN
2144
2145
2146
2147 SELECT application_short_name
2148
2149 INTO lv_schema_short_nm
2150
2151 FROM fnd_application
2152
2153 WHERE application_id=724;
2154
2155
2156
2157 IF NOT FND_INSTALLATION.GET_APP_INFO ( lv_schema_short_nm, lv_dummy1, lv_dummy2, lv_schema) THEN
2158
2159 ERRBUF := lv_schema_short_nm||'--Schema not found ';
2160
2161 RETCODE := MSC_UTIL.G_ERROR;
2162
2163 RETURN;
2164
2165 END IF;
2166
2167 EXCEPTION
2168
2169 WHEN NO_DATA_FOUND THEN
2170
2171 ERRBUF := 'Schema Short name for application_id 724 Not found';
2172
2173 RETCODE := MSC_UTIL.G_ERROR;
2174
2175 RETURN;
2176
2177 END;
2178
2179
2180
2181 row_limit := TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE'));
2182
2183 --
2184
2185 -- Generate List of Instances passed --
2186
2187
2188
2189 lv_plan_str := '(';
2190
2191 FOR indx IN pPlanList.FIRST .. pPlanList.LAST
2192
2193 LOOP
2194
2195 lv_plan_str := lv_plan_str ||''''|| pPlanList(indx)||'''' || ',' ;
2196
2197 END LOOP;
2198
2199 lv_plan_str := substr(lv_plan_str,1,length(lv_plan_str)-1) || ')';
2200
2201 --End Generate List of Instances passed --
2202
2203 --
2204
2205 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'******* Drop Plan Partition ***********');
2206
2207 lv_qry_str := 'SELECT table_name,partition_name '
2208
2209 ||' FROM fnd_lookup_values a,DBA_TAB_PARTITIONS b'
2210
2211 ||' WHERE a.attribute2 = b.table_name' --
2212
2213 ||' AND b.table_owner = :B1'
2214
2215 ||' AND a.lookup_type IN (''MSC_ODS_TABLE'',''MSC_PDSONLY_TABLE'',''MSC_OTHER_TABLE'')'-- see that staging table can be included here
2216
2217 ||' AND a.ATTRIBUTE12 = ''Y''' -- Column PLAN_ID Present
2218
2219 ||' AND a.enabled_flag = ''Y'''
2220
2221 ||' AND a.view_application_id = 700'
2222
2223 ||' AND a.language = userenv(''lang'')'
2224
2225 ||' AND a.attribute5 != ''U''' -- Table is Partitioned
2226
2227 ||' AND b.partition_name like substr( a.attribute2,5)||''%'''
2228
2229 ||' AND NVL(a.attribute13,''-1'')!=''G'''
2230
2231 ||' AND LTRIM(''MSC_''||partition_name, table_name||''_'') IN '||lv_plan_str;
2232
2233 BEGIN
2234
2235 OPEN v_plan_part_cur FOR lv_qry_str USING lv_schema;
2236
2237 LOOP
2238
2239 FETCH v_plan_part_cur INTO lv_tab, lv_tab_Part;
2240
2241 EXIT WHEN v_plan_part_cur%NOTFOUND;
2242
2243 BEGIN
2244
2245 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'ALTER TABLE '||lv_schema||'.'||lv_tab||' DROP PARTITION '||lv_tab_Part);
2246
2247 EXECUTE IMMEDIATE 'ALTER TABLE '||lv_schema||'.'||lv_tab||' DROP PARTITION '||lv_tab_Part;
2248
2249 EXCEPTION
2250
2251 WHEN OTHERS THEN
2252
2253 RETCODE := MSC_UTIL.G_WARNING;
2254
2255 END;
2256
2257 --TRC(lv_tab||'-----------'||lv_tab_Part);
2258
2259 END LOOP;
2260
2261 CLOSE v_plan_part_cur;
2262
2263 EXCEPTION
2264
2265 WHEN OTHERS THEN
2266
2267 ERRBUF := SQLERRM;
2268
2269 RETCODE := MSC_UTIL.G_ERROR;
2270
2271 RETURN;
2272
2273 END;
2274
2275 -- change plan list to number only
2276
2277 SELECT REPLACE(lv_plan_str,'''','') INTO lv_plan_str FROM DUAL;
2278
2279 --
2280
2281 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'******* Delete Rows for plan***********');
2282
2283 -- delete rows for Plan
2284
2285 FOR tab IN (SELECT a.attribute2 tname
2286
2287 FROM fnd_lookup_values a,dba_tables b
2288
2289 WHERE a.attribute2 = b.table_name
2290
2291 AND b.owner = lv_schema
2292
2293 AND lookup_type IN ('MSC_ODS_TABLE','MSC_PDSONLY_TABLE','MSC_OTHER_TABLE')
2294
2295 AND enabled_flag = 'Y'
2296
2297 AND view_application_id = 700
2298
2299 AND language = userenv('lang')
2300
2301 AND attribute5 = DECODE(fnd_profile.value('MSC_SHARE_PARTITIONS'),'Y',attribute5,'U') -- Unpartitioned table
2302
2303 AND a.ATTRIBUTE12 = 'Y' -- Column PLAN_ID Present
2304
2305 AND NVL(attribute13,'-1')<>'G')
2306
2307 LOOP
2308
2309 lv_qry_str := 'DELETE FROM '||lv_schema||'.'||tab.tname||' WHERE plan_id IN '||lv_plan_str||' AND ROWNUM <= '||row_limit;
2310
2311 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,lv_qry_str);
2312
2313 LOOP
2314
2315 lv_err_flag := FALSE;
2316
2317 BEGIN
2318
2319 EXECUTE IMMEDIATE lv_qry_str ;
2320
2321 EXCEPTION
2322
2323 WHEN OTHERS THEN
2324
2325 RETCODE := MSC_UTIL.G_WARNING;
2326
2327 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'ERROR IN DEL_INST --'||SQLERRM);
2328
2329 lv_err_flag := TRUE;
2330
2331 END;
2332
2333 EXIT WHEN (SQL%ROWCOUNT < row_limit) OR (lv_err_flag = TRUE); -- Exit when Not enough rows to be deleted;
2334
2335 COMMIT;
2336
2337 END LOOP;
2338
2339 END LOOP;
2340
2341 COMMIT;
2342
2343
2344
2345 IF NVL(RETCODE,-1) <> MSC_UTIL.G_WARNING THEN
2346
2347 RETCODE := MSC_UTIL.G_SUCCESS;
2348
2349 END IF;
2350
2351 EXCEPTION
2352
2353 WHEN OTHERS THEN
2354
2355 ERRBUF := SQLERRM;
2356
2357 RETCODE := MSC_UTIL.G_ERROR;
2358
2359 END PURGE_PLAN_DATA;
2360
2361 --======================================================================
2362
2363 PROCEDURE PURGE_INSTANCE_PLAN_DATA( ERRBUF OUT NOCOPY VARCHAR2,
2364
2365 RETCODE OUT NOCOPY NUMBER,
2366
2367 pInstanceId NUMBER,
2368
2369 pPlanId NUMBER)
2370
2371 IS
2372 BEGIN
2373 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'****************START PURGE_INSTANCE_PLAN_DATA**********************');
2374
2375 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'******---InstanceId = '||nvl(to_char(pInstanceId),'NULL')
2376
2377 || ' AND PlanId = '||nvl(to_char(pPlanId),'NULL')
2378
2379 ||' -------***** ');
2380
2381
2382
2383 IF pInstanceId IS NOT NULL THEN
2384
2385
2386
2387 DECLARE
2388
2389 lv_Inst_List tblTyp:=tblTyp(pInstanceId) ;
2390
2391 BEGIN
2392
2393 PURGE_INSTANCE_DATA(ERRBUF,RETCODE,lv_Inst_List);
2394
2395 IF RETCODE = MSC_UTIL.G_ERROR THEN
2396
2397 RETURN;
2398
2399 END IF;
2400
2401 END;
2402
2403
2404
2405 END IF;
2406
2407
2408
2409 IF pPlanId IS NOT NULL THEN
2410
2411
2412
2413 DECLARE
2414
2415 lv_Plan_List tblTyp:=tblTyp(pPlanId) ;
2416
2417 BEGIN
2418
2419 PURGE_PLAN_DATA(ERRBUF,RETCODE,lv_Plan_List);
2420
2421 END;
2422
2423
2424
2425 END IF;
2426
2427
2428
2429 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'****************************END******************************************');
2430
2431
2432
2433 EXCEPTION
2434
2435 WHEN OTHERS THEN
2436
2437 ERRBUF := SQLERRM;
2438
2439 RETCODE := MSC_UTIL.G_ERROR;
2440
2441 END PURGE_INSTANCE_PLAN_DATA;
2442
2443 END MSC_PURGE_LID ;