[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_PURGE_STAGING
Source
1 PACKAGE BODY MSC_CL_PURGE_STAGING AS
2 /* $Header: MSCCLPSB.pls 120.8.12020000.2 2012/09/27 09:46:53 swundapa ship $ */
3
4
5 v_batch_size NUMBER ;
6 v_debug NUMBER;
7 v_applsys_schema VARCHAR2(32);
8 v_program_status NUMBER := G_SUCCESS;
9
10
11 -- Declaring the Private Procedures
12 PROCEDURE delete_records ( p_instance_code IN VARCHAR2 , p_del_rej_rec IN NUMBER );
13
14 -- Declaring the Private Functions
15 FUNCTION is_purge_possible ( ERRBUF OUT NOCOPY VARCHAR2, RETCODE OUT NOCOPY NUMBER, pINSTANCE_CODE IN VARCHAR2 , pINSTANCE_ID IN NUMBER )
16 RETURN BOOLEAN;
17
18
19 /*========================================================================================+
20 | DESCRIPTION : This procedure is called to delete the records for a particular |
21 | instance from all the tables from lookup type MSC_X_SETUP_ENTITY_CODE |
22 +========================================================================================*/
23
24 PROCEDURE delete_records ( p_instance_code IN VARCHAR2 , p_del_rej_rec IN NUMBER )
25 AS
26
27 lv_instance_code VARCHAR2(5);
28 lv_p_del_rej_rec NUMBER;
29
30
31 lv_tablename FND_LOOKUP_VALUES.attribute1%Type;
32 lv_errtxt VARCHAR2(300);
33
34 lv_total number :=0; -- total number of rows deleted
35
36 CURSOR table_names IS
37 SELECT DISTINCT (LV.ATTRIBUTE1) TABLE_NAME
38 FROM FND_LOOKUP_VALUES LV
39 WHERE LV.ENABLED_FLAG = 'Y'
40 AND LV.VIEW_APPLICATION_ID = 700
41 AND SUBSTR (LV.ATTRIBUTE1, 1, 3) = 'MSC'
42 AND nvl(LV.ATTRIBUTE4,2) = 2
43 AND LV.LOOKUP_TYPE = 'MSC_X_SETUP_ENTITY_CODE';
44
45 lv_sql_stmt VARCHAR2(500);
46
47
48 table_not_found EXCEPTION;
49 PRAGMA EXCEPTION_INIT (table_not_found,-00942);
50
51 synonym_translation_invalid EXCEPTION;
52 PRAGMA EXCEPTION_INIT (synonym_translation_invalid,-00980);
53
54 BEGIN
55
56
57 lv_instance_code := p_instance_code;
58 lv_p_del_rej_rec :=p_del_rej_rec;
59 v_batch_size := TO_NUMBER(NVL(FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE'),75000));
60
61
62 OPEN table_names;
63 LOOP
64 lv_total := 0;
65 FETCH table_names INTO lv_tablename;
66 EXIT WHEN table_names%NOTFOUND;
67 loop
68
69 IF ( lv_tablename = 'MSC_ST_PROFILES' ) THEN
70 IF ( lv_p_del_rej_rec = SYS_YES ) THEN
71 lv_sql_stmt := 'DELETE FROM '||lv_tablename||' WHERE ROWNUM <= '||v_batch_size||' AND PROCESS_FLAG IN ( '||G_ERROR_FLG||','||G_PROPAGATION||')';
72 ELSE
73 lv_sql_stmt := 'DELETE FROM '||lv_tablename||' WHERE ROWNUM <= '||v_batch_size||'';
74 END IF;
75 ELSE
76 IF ( lv_p_del_rej_rec = SYS_YES ) THEN
77 lv_sql_stmt := 'DELETE FROM '||lv_tablename||' WHERE SR_INSTANCE_CODE= '''||lv_instance_code||''' AND ROWNUM <= '||v_batch_size||' AND PROCESS_FLAG IN ( '||G_ERROR_FLG||','||G_PROPAGATION||')';
78 ELSE
79 lv_sql_stmt := 'DELETE FROM '||lv_tablename||' WHERE SR_INSTANCE_CODE= '''||lv_instance_code||''' AND ROWNUM <= '||v_batch_size||'';
80 END IF;
81 END IF;
82
83
84
85 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Sql statements executed-'||lv_sql_stmt);
86
87
88
89 BEGIN
90 EXECUTE IMMEDIATE lv_sql_stmt;
91
92 EXCEPTION
93
94 WHEN table_not_found THEN
95 lv_errtxt := substr(SQLERRM,1,240) ;
96 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,lv_errtxt);
97 exit;
98
99 WHEN synonym_translation_invalid THEN
100 lv_errtxt := substr(SQLERRM,1,240) ;
101 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,lv_errtxt);
102 exit;
103
104 WHEN OTHERS THEN
105 lv_errtxt := substr(SQLERRM,1,240) ;
106 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,lv_errtxt);
107 exit;
108
109 END;
110
111 lv_total := lv_total+SQL%ROWCOUNT ;
112
113 EXIT WHEN SQL%NOTFOUND;
114
115 COMMIT;
116 end loop;
117
118 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No. of rows deleted from '|| lv_tablename ||' - '||lv_total);
119
120
121 END LOOP;
122 CLOSE table_names;
123
124
125 EXCEPTION
126
127 WHEN OTHERS THEN
128
129 lv_errtxt := substr(SQLERRM,1,240) ;
130 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,lv_errtxt);
131
132 END delete_records;
133
134
135 /*========================================================================================+
136 | DESCRIPTION : This fuction is called to check whether the st_status for a particular |
137 | instance is not in PULLING , LOADING and PRE-PROCESSING |
138 +========================================================================================*/
139
140
141 FUNCTION is_purge_possible ( ERRBUF OUT NOCOPY VARCHAR2, RETCODE OUT NOCOPY NUMBER, pINSTANCE_CODE IN VARCHAR2 , pINSTANCE_ID IN NUMBER )
142 RETURN BOOLEAN
143 AS
144 lv_staging_table_status NUMBER;
145
146 BEGIN
147
148 SELECT ST_STATUS INTO lv_staging_table_status
149 FROM msc_apps_instances
150 WHERE INSTANCE_CODE= pINSTANCE_CODE
151 FOR UPDATE;
152
153 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Entered to check whether purge possible for the instance-'||pINSTANCE_CODE);
154
155
156
157
158 IF lv_staging_table_status= G_ST_PULLING THEN
159 FND_MESSAGE.SET_NAME('MSC', 'MSC_ST_ERROR_PULLING');
160 ERRBUF:= FND_MESSAGE.GET;
161
162 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,ERRBUF);
163
164
165 IF ( pINSTANCE_ID <> -1 )
166 THEN
167 v_program_status :=G_ERROR;
168
169 ELSE
170 v_program_status :=G_WARNING;
171
172 END IF;
173
174 RETURN FALSE;
175
176
177 ELSIF lv_staging_table_status= G_ST_COLLECTING THEN
178 FND_MESSAGE.SET_NAME('MSC', 'MSC_ST_ERROR_LOADING');
179 ERRBUF:= FND_MESSAGE.GET;
180
181 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,ERRBUF);
182
183
184 IF ( pINSTANCE_ID <> -1 )
185 THEN
186 v_program_status :=G_ERROR;
187
188 ELSE
189 v_program_status :=G_WARNING;
190
191 END IF;
192
193 RETURN FALSE;
194
195 ELSIF lv_staging_table_status= G_ST_PRE_PROCESSING THEN
196 FND_MESSAGE.SET_NAME('MSC', 'MSC_ST_ERROR_PRE_PROCESSING');
197 ERRBUF:= FND_MESSAGE.GET;
198
199 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,ERRBUF);
200
201
202 IF ( pINSTANCE_ID <> -1 )
203 THEN
204 v_program_status :=G_ERROR;
205
206 ELSE
207 v_program_status :=G_WARNING;
208
209 END IF;
210
211 RETURN FALSE;
212
213
214 ELSE
215 UPDATE msc_apps_instances
216 SET st_status=G_ST_PURGING
217 WHERE INSTANCE_CODE= pINSTANCE_CODE;
218 COMMIT;
219
220 RETURN TRUE;
221
222 END IF;
223
224
225 END is_purge_possible;
226
227 /*=============================================================================================+
228 | DESCRIPTION : This is the main program that deletes the records from the MSC staging |
229 | tables.It takes instance_code as a parameter and deletes records for the |
230 | instance only when st_status for this instance is not in G_ST_PULLING, |
231 | G_ST_COLLECTING and G_ST_PRE_PROCESSING .If the instance_code is null |
232 | then it will delete records from all instances after checking the st_status. |
233 | It also takes a parameter , whether to delete only errored out records or |
234 | all legacy data (st_status check before deletion will only take place |
235 | when 'delete only rejected records parameter is set to NO). |
236 +=============================================================================================*/
237
238
239 PROCEDURE launch_purge ( ERRBUF OUT NOCOPY VARCHAR2,
240 RETCODE OUT NOCOPY NUMBER,
241 p_instance_id IN NUMBER,
242 p_del_rej_rec IN NUMBER )
243
244 AS
245
246 CURSOR table_names IS
247 SELECT DISTINCT (LV.ATTRIBUTE1) TABLE_NAME
248 FROM FND_LOOKUP_VALUES LV
249 WHERE LV.ENABLED_FLAG = 'Y'
250 AND LV.VIEW_APPLICATION_ID = 700
251 AND SUBSTR (LV.ATTRIBUTE1, 1, 3) = 'MSC'
252 AND LV.LOOKUP_TYPE = 'MSC_X_SETUP_ENTITY_CODE';
253
254 CURSOR instance_codes ( cp_instance_id NUMBER ) IS
255 SELECT instance_code,instance_type,st_status
256 FROM msc_apps_instances
257 WHERE instance_id=cp_instance_id
258 UNION ALL
259 SELECT instance_code,instance_type,st_status
260 FROM msc_apps_instances
261 WHERE cp_instance_id =-1;
262
263 -- Cursor P and q are for update to lock the records before checking for the st_status.
264 CURSOR p IS
265 SELECT instance_code
266 FROM msc_apps_instances
267 WHERE st_status NOT IN (G_ST_PULLING,G_ST_COLLECTING,G_ST_PRE_PROCESSING)
268 FOR UPDATE;
269
270 CURSOR q (cp_instance_id NUMBER ) IS
271 SELECT instance_code
272 FROM msc_apps_instances
273 WHERE st_status NOT IN (G_ST_PULLING,G_ST_COLLECTING,G_ST_PRE_PROCESSING)
274 AND instance_id=cp_instance_id
275 FOR UPDATE;
276
277 -- input variables of procedure
278 lv_p_del_rej_rec NUMBER;
279 lv_p_instance_id NUMBER ;
280
281 -- variable for cursor table_names
282 lv_table_name FND_LOOKUP_VALUES.attribute1%Type;
283
284 -- variable for cursor instance_codes
285 lv_p_instance_code VARCHAR2(5);
286 lv_st_status NUMBER;
287 lv_instance_type NUMBER;
288
289 lv_inst_flag NUMBER := 0 ;
290 lv_leg_inst_flag NUMBER := 0;
291 lv_st_status_flag NUMBER := 0;
292 lv_trunc_profile NUMBER := SYS_NO;
293
294 lv_trunc_flag NUMBER := SYS_NO;
295
296 lv_sql_stmt VARCHAR2(500);
297 lv_errtxt VARCHAR2(300);
298
299 lv_retval boolean;
300 lv_dummy1 varchar2(32);
301 lv_dummy2 varchar2(32);
302
303 table_not_found EXCEPTION;
304 PRAGMA EXCEPTION_INIT (table_not_found,-00942);
305
306
307 BEGIN
308
309
310 lv_p_instance_id := nvl( p_instance_id ,-1);
311 lv_p_del_rej_rec :=p_del_rej_rec;
312
313
314 OPEN table_names;
315 FETCH table_names INTO lv_table_name;
316 IF ( table_names%ROWCOUNT = 0 ) THEN
317 FND_MESSAGE.SET_NAME('MSC','MSC_PS_INVALID_LOOKUP');
318 ERRBUF:= FND_MESSAGE.GET;
319 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,ERRBUF);
320 v_program_status := G_ERROR;
321
322 CLOSE table_names;
323 ELSE -- IF ( table_names%ROWCOUNT = 0 ) THEN
324
325
326 CLOSE table_names;
327 SELECT DECODE(nvl(FND_PROFILE.VALUE('MRP_DEBUG'),'N'),'Y',1,2),
328 DECODE(nvl(FND_PROFILE.VALUE('MSC_PURGE_ST_CONTROL'),'No'),'Yes',1,2)
329 INTO v_debug,lv_trunc_profile
330 FROM dual;
331
332
333 IF (lv_trunc_profile = SYS_YES AND lv_p_del_rej_rec=SYS_NO ) THEN
334 SELECT count(*) INTO lv_leg_inst_flag FROM msc_apps_instances WHERE instance_type = G_INS_OTHER ;
335 SELECT count(*) INTO lv_inst_flag FROM msc_apps_instances;
336
337 -- locking the records in msc_apps_instances before checking the st_status
338 IF (lv_p_instance_id <> -1) THEN
339 open q(lv_p_instance_id);
340 close q;
341 ELSE
342 open p;
343 close p;
344 END IF;
345 -- Counting number of instances for which st_status is G_ST_PULLING , G_ST_COLLECTING and G_ST_PRE_PROCESSING
346 SELECT count(*) INTO lv_st_status_flag FROM msc_apps_instances WHERE st_status IN ( G_ST_PULLING,G_ST_COLLECTING,G_ST_PRE_PROCESSING ) AND ((instance_id=lv_p_instance_id) OR (lv_p_instance_id=-1));
347
348 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Value of lv_st_status_flag-'||lv_st_status_flag);
349
350 END IF;
351
352 -- Setting the truncation flag
353 IF ( (lv_p_del_rej_rec = SYS_NO ) AND ( lv_trunc_profile = SYS_YES ) AND ((lv_p_instance_id = -1) OR (lv_inst_flag = 1)) AND (lv_leg_inst_flag = lv_inst_flag) AND (lv_st_status_flag = 0) )
354 THEN
355 lv_trunc_flag :=SYS_YES;
356 ELSE
357 lv_trunc_flag :=SYS_NO;
358 END IF;
359
360
361 IF (lv_trunc_flag=SYS_YES) THEN
362
363 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Truncation flag is YES. Entering in truncation LOOP');
364
365
366 UPDATE msc_apps_instances
367 SET st_status= G_ST_PURGING;
368 COMMIT;
369
370 lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'MSC', lv_dummy1, lv_dummy2, v_applsys_schema);
371
372 OPEN table_names;
373
374
375 LOOP
376 FETCH table_names INTO lv_table_name;
377 EXIT WHEN table_names%NOTFOUND;
378 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_table_name);
379
380
381
382 BEGIN
383 lv_sql_stmt := 'TRUNCATE TABLE '||v_applsys_schema||'.'||lv_table_name|| '';
384
385 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Sql statements to be executed-'||lv_sql_stmt);
386
387
388
389
390 EXECUTE IMMEDIATE lv_sql_stmt;
391
392 EXCEPTION
393
394 WHEN table_not_found THEN
395 lv_errtxt := substr(SQLERRM,1,240) ;
396 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,lv_errtxt);
397
398
399 WHEN OTHERS THEN
400 lv_errtxt := substr(SQLERRM,1,240) ;
401 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,lv_errtxt);
402
403
404 END;
405
406 END LOOP;
407 CLOSE table_names;
408
409 UPDATE msc_apps_instances
410 SET st_status= G_ST_EMPTY;
411 COMMIT;
412
413 ELSE
414
415 commit; -- To break the lock on the records, acquired while opening the cursor p or q
416
417 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Truncation flag is NO. Entered in DELETION LOOP');
418
419
420 OPEN instance_codes(lv_p_instance_id);
421 LOOP
422 FETCH instance_codes INTO lv_p_instance_code,lv_instance_type,lv_st_status;
423 EXIT WHEN instance_codes%NOTFOUND;
424
425 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_p_instance_code);
426
427
428 IF (lv_p_del_rej_rec=SYS_YES) THEN
429
430 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Deleting without checking the ST_STATUS');
431
432
433
434
435 delete_records( lv_p_instance_code,lv_p_del_rej_rec);
436
437 ELSE
438
439
440 IF ( is_purge_possible( ERRBUF,RETCODE,lv_p_instance_code,lv_p_instance_id) ) THEN
441
442
443 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Deleting after checking the ST_STATUS');
444
445
446 delete_records(lv_p_instance_code,lv_p_del_rej_rec);
447
448
449 IF ( lv_instance_type = G_INS_OTHER ) THEN
450
451 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,' Instance type is LEGACY ,so setting st_status to empty');
452
453
454 UPDATE msc_apps_instances
455 SET st_status=G_ST_EMPTY
456 WHERE instance_code=lv_p_instance_code;
457 COMMIT;
458
459 ELSE
460
461 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Instance type is ERP ,so setting st_status to previous st_status-'||lv_st_status);
462
463
464 UPDATE msc_apps_instances
465 SET st_status=lv_st_status
466 WHERE instance_code=lv_p_instance_code;
467 COMMIT;
468
469 END IF;
470
471
472 END IF;
473 END IF;
474
475 END LOOP;
476 CLOSE instance_codes;
477 END IF;
478 END IF; -- IF ( table_names%ROWCOUNT = 0 ) THEN
479
480 IF v_program_status=G_WARNING THEN
481 RETCODE := G_WARNING;
482 ELSIF v_program_status=G_ERROR THEN
483 RETCODE := G_ERROR;
484 END IF;
485
486
487 EXCEPTION
488
489 WHEN OTHERS THEN
490 ERRBUF := SQLERRM;
491 RETCODE := SQLCODE;
492
493 lv_errtxt := substr(SQLERRM,1,240) ;
494 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,lv_errtxt);
495
496 END launch_purge;
497
498 --===================================================================
499 PROCEDURE PURGE_STAGING_TABLES_TRNC( p_instance_id IN NUMBER) IS
500
501
502
503 BEGIN
504
505
506
507 ---------------- CALENDAR --------------------
508
509
510
511 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE('MSC_ST_CALENDAR_ASSIGNMENTS');
512
513 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE('MSC_ST_CALENDAR_SHIFTS');
514
515 ---------------- BOM --------------------
516
517
518
519 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_BOM_COMPONENTS');
520
521
522
523 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_BOMS');
524
525
526
527 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_CO_PRODUCTS'); -- for OSFM Integration (bug fix 2377866)
528
529
530
531 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_COMPONENT_SUBSTITUTES');
532
533
534
535 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_ROUTINGS');
536
537
538
539 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_ROUTING_OPERATIONS');
540
541
542
543 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_OPERATION_RESOURCES');
544
545
546
547 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_OPERATION_RESOURCE_SEQS');
548
549
550
551 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_OPERATION_NETWORKS');
552
553
554
555 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_PROCESS_EFFECTIVITY');
556
557
558
559 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_OPERATION_COMPONENTS');
560
561
562
563 ---------------- BOR -------------------
564
565
566
567 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_BILL_OF_RESOURCES');
568
569
570
571 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_BOR_REQUIREMENTS');
572
573
574
575 ---------------- CALENDAR_DATE -------------
576
577
578
579 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_CALENDAR_DATES');
580
581
582
583 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_PERIOD_START_DATES');
584
585
586
587 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_CAL_YEAR_START_DATES');
588
589
590
591 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_CAL_WEEK_START_DATES');
592
593
594
595 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_RESOURCE_SHIFTS');
596
597
598
599 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_CALENDAR_SHIFTS');
600
601
602
603 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_SHIFT_DATES');
604
605
606
607 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_RESOURCE_CHANGES');
608
609
610
611 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_SHIFT_TIMES');
612
613
614
615 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_SHIFT_EXCEPTIONS');
616
617
618
619 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_NET_RESOURCE_AVAIL');
620
621
622
623 ---------------- CATEGORY -------------
624
625
626
627 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_ITEM_CATEGORIES');
628
629
630
631 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_CATEGORY_SETS');
632
633
634
635 ---------------- DEMAND -------------
636
637
638
639 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_DEMANDS');
640
641
642
643 ---------------- SALES ORDER -------------
644
645
646
647 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_SALES_ORDERS');
648
649
650
651 ---------------- HARD RESERVATION -------------
652
653
654
655 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_RESERVATIONS');
656
657
658
659 ---------------- ITEM -------------
660
661
662
663 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_SYSTEM_ITEMS');
664
665
666
667 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_ABC_CLASSES');
668
669
670
671 ----------------- ITEM SUBSTITUTES -------------
672
673
674
675 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE('MSC_ST_ITEM_SUBSTITUTES');
676
677
678
679 ---------------- RESOURCE -------------
680
681
682
683 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_DEPARTMENT_RESOURCES');
684
685
686
687 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_SIMULATION_SETS');
688
689
690
691 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_RESOURCE_GROUPS');
692
693
694
695 ---------------- SAFETY STOCK-------------
696
697
698
699 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_SAFETY_STOCKS');
700
701
702
703 ---------------- SCHEDULE DESIGNATOR -------------
704
705
706
707 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_DESIGNATORS');
708
709
710
711 ---------------- SOURCING -------------
712
713
714
715 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_ASSIGNMENT_SETS');
716
717
718
719 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_SOURCING_RULES');
720
721
722
723 /* delete FROM MSC_ST_SR_ASSIGNMENTS
724
725 // WHERE SR_ASSIGNMENT_INSTANCE_ID= p_instance_id;
726
727 */
728
729
730
731 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_SR_ASSIGNMENTS');
732
733
734
735 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_SR_RECEIPT_ORG');
736
737
738
739 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_SR_SOURCE_ORG');
740
741
742
743 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_INTERORG_SHIP_METHODS');
744
745
746
747 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_REGIONS');
748
749
750
751 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_ZONE_REGIONS');
752
753
754
755 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_REGION_LOCATIONS');
756
757
758
759 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_REGION_SITES');
760
761
762
763 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_CARRIER_SERVICES');
764
765
766
767 ---------------- SUB INVENTORY -------------
768
769
770
771 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_SUB_INVENTORIES');
772
773
774
775 ---------------- SUPPLIER CAPACITY -------------
776
777
778
779 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_ITEM_SUPPLIERS');
780
781
782
783 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_SUPPLIER_CAPACITIES');
784
785
786
787 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_SUPPLIER_FLEX_FENCES');
788
789
790
791 ---------------- SUPPLY -------------
792
793
794
795 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_SUPPLIES');
796
797
798
799 ---------------- RESOURCE REQUIREMENT -------------
800
801
802
803 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_RESOURCE_REQUIREMENTS');
804
805
806
807 ---------------- TRADING PARTNER -------------
808
809
810
811 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_TRADING_PARTNERS');
812
813
814
815 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_TRADING_PARTNER_SITES');
816
817
818
819 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_LOCATION_ASSOCIATIONS');
820
821
822
823 ---------------- UNIT NUMBER -------------
824
825
826
827 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_UNIT_NUMBERS');
828
829
830
831 ---------------- PROJECT -------------
832
833
834
835 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_PROJECTS');
836
837
838
839 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_PROJECT_TASKS');
840
841
842
843 ---------------- PARAMETER -------------
844
845
846
847 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_PARAMETERS');
848
849
850
851 ---------------- UOM -------------
852
853
854
855 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_UNITS_OF_MEASURE');
856
857
858
859 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_UOM_CLASS_CONVERSIONS');
860
861
862
863 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_UOM_CONVERSIONS');
864
865
866
867 ---------------- BIS -------------
868
869
870
871 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_BIS_PFMC_MEASURES');
872
873
874
875 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_BIS_TARGET_LEVELS');
876
877
878
879 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_BIS_TARGETS');
880
881
882
883 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_BIS_BUSINESS_PLANS');
884
885
886
887 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_BIS_PERIODS');
888
889
890
891 ---------------- ATP RULES -------------
892
893
894
895 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_ATP_RULES');
896
897
898
899 ---------------- PLANNERS -------------
900
901
902
903 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_PLANNERS');
904
905
906
907 ---------------- DEMAND CLASS -------------
908
909
910
911 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_DEMAND_CLASSES');
912
913
914
915 ---------------- PARTNER CONTACTS -----------
916
917
918
919 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_PARTNER_CONTACTS');
920
921
922
923 ---------------- LEGACY TABLES --------------
924
925
926
927 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_ITEM_SOURCING');
928
929
930
931 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_CALENDARS');
932
933
934
935 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_WORKDAY_PATTERNS');
936
937
938
939 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_CALENDAR_EXCEPTIONS');
940
941
942
943 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_GROUPS');
944
945
946
947 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_GROUP_COMPANIES');
948
949
950
951 ------------- JOB DETAILS ---------------------
952
953
954
955 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_JOB_OPERATION_NETWORKS');
956
957
958
959 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_JOB_OPERATIONS');
960
961
962
963 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_JOB_REQUIREMENT_OPS');
964
965
966
967 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_JOB_OP_RESOURCES');
968
969
970
971 /* SCE Change starts */
972
973 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_COMPANY_USERS');
974
975 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_ITEM_CUSTOMERS');
976
977 /* SCE Change ends */
978
979
980
981 -------------- TRIP TABLES ---------------------
982
983
984
985 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_TRIPS');
986
987
988
989 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_TRIP_STOPS');
990
991
992
993 --------- PROFILE TABLES --------------
994
995
996
997 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_APPS_INSTANCES');
998
999
1000
1001 /* ds_change: start */
1002
1003 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_DEPT_RES_INSTANCES');
1004
1005 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_NET_RES_INST_AVAIL');
1006
1007 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_JOB_OP_RES_INSTANCES');
1008
1009 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_RESOURCE_INSTANCE_REQS');
1010
1011 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_RESOURCE_SETUPS');
1012
1013 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_SETUP_TRANSITIONS');
1014
1015 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_STD_OP_RESOURCES');
1016
1017 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_RES_INSTANCE_CHANGES');
1018
1019 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_RESOURCE_CHARGES');
1020
1021 /* ds_change: end */
1022
1023
1024
1025 ---------------- SR LOOKUPS -------------
1026
1027 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_SR_LOOKUPS');
1028
1029
1030
1031 ----------------- FISCAL CALENDAR ------------------
1032
1033 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ST_CALENDAR_MONTHS');
1034
1035
1036
1037 END PURGE_STAGING_TABLES_TRNC;
1038
1039
1040
1041 -- =========== Purge Tables by Deleting them ==============
1042
1043
1044
1045 PROCEDURE PURGE_STAGING_TABLES_DEL( p_instance_id IN NUMBER) IS
1046
1047
1048
1049 BEGIN
1050
1051
1052
1053 ---------------- CALENDAR --------------------
1054
1055
1056
1057 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_CALENDAR_ASSIGNMENTS', p_instance_id, NULL);
1058
1059
1060
1061 COMMIT;
1062
1063
1064
1065
1066
1067 ---------------- BOM --------------------
1068
1069
1070
1071 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BOM_COMPONENTS', p_instance_id, NULL);
1072
1073
1074
1075 COMMIT;
1076
1077
1078
1079 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BOMS', p_instance_id, NULL);
1080
1081
1082
1083 COMMIT;
1084
1085
1086
1087 -- for OSFM Integration (bug fix 2377866)
1088
1089 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_CO_PRODUCTS', p_instance_id, NULL);
1090
1091
1092
1093 COMMIT;
1094
1095
1096
1097 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_COMPONENT_SUBSTITUTES', p_instance_id, NULL);
1098
1099
1100
1101 COMMIT;
1102
1103
1104
1105 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_ROUTINGS', p_instance_id, NULL);
1106
1107
1108
1109 COMMIT;
1110
1111
1112
1113 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_ROUTING_OPERATIONS', p_instance_id, NULL);
1114
1115
1116
1117 COMMIT;
1118
1119
1120
1121 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_OPERATION_RESOURCES', p_instance_id, NULL);
1122
1123
1124
1125 COMMIT;
1126
1127
1128
1129 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_OPERATION_RESOURCE_SEQS', p_instance_id, NULL);
1130
1131
1132
1133 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_OPERATION_NETWORKS',p_instance_id,NULL);
1134
1135
1136
1137 COMMIT;
1138
1139
1140
1141 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_PROCESS_EFFECTIVITY', p_instance_id, NULL);
1142
1143
1144
1145 COMMIT;
1146
1147
1148
1149 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_OPERATION_COMPONENTS', p_instance_id, NULL);
1150
1151
1152
1153 COMMIT;
1154
1155
1156
1157 ---------------- BOR -------------------
1158
1159
1160
1161 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BILL_OF_RESOURCES', p_instance_id, NULL);
1162
1163
1164
1165 COMMIT;
1166
1167
1168
1169 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BOR_REQUIREMENTS', p_instance_id, NULL);
1170
1171
1172
1173 COMMIT;
1174
1175
1176
1177 ---------------- CALENDAR_DATE -------------
1178
1179
1180
1181 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_CALENDAR_DATES', p_instance_id, NULL);
1182
1183
1184
1185 COMMIT;
1186
1187
1188
1189 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_PERIOD_START_DATES', p_instance_id, NULL);
1190
1191
1192
1193 COMMIT;
1194
1195
1196
1197 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_CAL_YEAR_START_DATES', p_instance_id, NULL);
1198
1199
1200
1201 COMMIT;
1202
1203
1204
1205 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_CAL_WEEK_START_DATES', p_instance_id, NULL);
1206
1207
1208
1209 COMMIT;
1210
1211
1212
1213 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RESOURCE_SHIFTS', p_instance_id, NULL);
1214
1215
1216
1217 COMMIT;
1218
1219
1220
1221 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_CALENDAR_SHIFTS', p_instance_id, NULL);
1222
1223
1224
1225 COMMIT;
1226
1227
1228
1229 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SHIFT_DATES', p_instance_id, NULL);
1230
1231
1232
1233 COMMIT;
1234
1235
1236
1237 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RESOURCE_CHANGES', p_instance_id, NULL);
1238
1239
1240
1241 COMMIT;
1242
1243
1244
1245 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SHIFT_TIMES', p_instance_id, NULL);
1246
1247
1248
1249 COMMIT;
1250
1251
1252
1253 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SHIFT_EXCEPTIONS', p_instance_id, NULL);
1254
1255
1256
1257 COMMIT;
1258
1259
1260
1261 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_NET_RESOURCE_AVAIL', p_instance_id, NULL);
1262
1263
1264
1265 COMMIT;
1266
1267
1268
1269 ---------------- CATEGORY -------------
1270
1271 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_ITEM_CATEGORIES', p_instance_id, NULL);
1272
1273
1274
1275 COMMIT;
1276
1277
1278
1279 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_CATEGORY_SETS', p_instance_id, NULL);
1280
1281
1282
1283 COMMIT;
1284
1285
1286
1287 ---------------- DEMAND -------------
1288
1289 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_DEMANDS', p_instance_id, NULL);
1290
1291
1292
1293 COMMIT;
1294
1295
1296
1297 ---------------- SALES ORDER -------------
1298
1299 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SALES_ORDERS', p_instance_id, NULL);
1300
1301
1302
1303 COMMIT;
1304
1305
1306
1307 ---------------- HARD RESERVATION -------------
1308
1309 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RESERVATIONS', p_instance_id, NULL);
1310
1311
1312
1313 COMMIT;
1314
1315
1316
1317 ---------------- ITEM -------------
1318
1319 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SYSTEM_ITEMS', p_instance_id, NULL);
1320
1321
1322
1323 COMMIT;
1324
1325
1326
1327 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SYSTEM_ITEMS', p_instance_id, NULL);
1328
1329
1330
1331 COMMIT;
1332
1333
1334
1335 ----------------- ITEM SUBSTITUTES -------------
1336
1337
1338
1339 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_ITEM_SUBSTITUTES',p_instance_id, NULL);
1340
1341
1342
1343 COMMIT;
1344
1345
1346
1347 ---------------- RESOURCE -------------
1348
1349 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_DEPARTMENT_RESOURCES', p_instance_id, NULL);
1350
1351
1352
1353 COMMIT;
1354
1355
1356
1357 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SIMULATION_SETS', p_instance_id, NULL);
1358
1359
1360
1361 COMMIT;
1362
1363
1364
1365 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RESOURCE_GROUPS', p_instance_id, NULL);
1366
1367
1368
1369 COMMIT;
1370
1371 ---------------- SAFETY STOCK-------------
1372
1373
1374
1375 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SAFETY_STOCKS', p_instance_id, NULL);
1376
1377
1378
1379 COMMIT;
1380
1381
1382
1383 ---------------- SCHEDULE DESIGNATOR -------------
1384
1385 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_DESIGNATORS', p_instance_id, NULL);
1386
1387
1388
1389 COMMIT;
1390
1391
1392
1393 ---------------- SOURCING -------------
1394
1395 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_ASSIGNMENT_SETS', p_instance_id, NULL);
1396
1397
1398
1399 COMMIT;
1400
1401
1402
1403 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SOURCING_RULES', p_instance_id, NULL);
1404
1405
1406
1407 COMMIT;
1408
1409
1410
1411 DELETE FROM MSC_ST_SR_ASSIGNMENTS
1412
1413 WHERE SR_ASSIGNMENT_INSTANCE_ID= p_instance_id;
1414
1415
1416
1417 COMMIT;
1418
1419
1420
1421 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SR_RECEIPT_ORG', p_instance_id, NULL);
1422
1423
1424
1425 COMMIT;
1426
1427
1428
1429 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SR_SOURCE_ORG', p_instance_id, NULL);
1430
1431
1432
1433 COMMIT;
1434
1435
1436
1437 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_INTERORG_SHIP_METHODS', p_instance_id, NULL);
1438
1439
1440
1441 COMMIT;
1442
1443
1444
1445 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_REGIONS', p_instance_id, NULL);
1446
1447
1448
1449 COMMIT;
1450
1451
1452
1453 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_ZONE_REGIONS', p_instance_id, NULL);
1454
1455
1456
1457 COMMIT;
1458
1459
1460
1461 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_REGION_LOCATIONS', p_instance_id, NULL);
1462
1463
1464
1465 COMMIT;
1466
1467
1468
1469 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_REGION_SITES', p_instance_id, NULL);
1470
1471
1472
1473 COMMIT;
1474
1475
1476
1477 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_CARRIER_SERVICES', p_instance_id, NULL);
1478
1479
1480
1481 COMMIT;
1482
1483 ---------------- SUB INVENTORY -------------
1484
1485 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SUB_INVENTORIES', p_instance_id, NULL);
1486
1487
1488
1489 COMMIT;
1490
1491
1492
1493 ---------------- SUPPLIER CAPACITY -------------
1494
1495 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_ITEM_SUPPLIERS', p_instance_id, NULL);
1496
1497
1498
1499 COMMIT;
1500
1501
1502
1503 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SUPPLIER_CAPACITIES', p_instance_id, NULL);
1504
1505
1506
1507 COMMIT;
1508
1509
1510
1511 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SUPPLIER_FLEX_FENCES', p_instance_id, NULL);
1512
1513
1514
1515 COMMIT;
1516
1517
1518
1519 ---------------- SUPPLY -------------
1520
1521 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SUPPLIES', p_instance_id, NULL);
1522
1523
1524
1525 COMMIT;
1526
1527
1528
1529 ---------------- RESOURCE REQUIREMENT -------------
1530
1531 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RESOURCE_REQUIREMENTS', p_instance_id, NULL);
1532
1533
1534
1535 COMMIT;
1536
1537
1538
1539 ---------------- TRADING PARTNER -------------
1540
1541 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_TRADING_PARTNERS', p_instance_id, NULL);
1542
1543
1544
1545 COMMIT;
1546
1547
1548
1549 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_TRADING_PARTNER_SITES', p_instance_id, NULL);
1550
1551
1552
1553 COMMIT;
1554
1555
1556
1557 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_LOCATION_ASSOCIATIONS', p_instance_id, NULL);
1558
1559
1560
1561 COMMIT;
1562
1563
1564
1565 ---------------- UNIT NUMBER -------------
1566
1567 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_UNIT_NUMBERS', p_instance_id, NULL);
1568
1569
1570
1571 COMMIT;
1572
1573 ---------------- PROJECT -------------
1574
1575 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_PROJECTS', p_instance_id, NULL);
1576
1577
1578
1579 COMMIT;
1580
1581
1582
1583 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_PROJECT_TASKS', p_instance_id, NULL);
1584
1585
1586
1587 COMMIT;
1588
1589
1590
1591 ---------------- PARAMETER -------------
1592
1593 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_PARAMETERS', p_instance_id, NULL);
1594
1595
1596
1597 COMMIT;
1598
1599
1600
1601 ---------------- UOM -------------
1602
1603 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_UNITS_OF_MEASURE', p_instance_id, NULL);
1604
1605
1606
1607 COMMIT;
1608
1609
1610
1611 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_UOM_CLASS_CONVERSIONS', p_instance_id, NULL);
1612
1613
1614
1615 COMMIT;
1616
1617
1618
1619 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_UOM_CONVERSIONS', p_instance_id, NULL);
1620
1621
1622
1623 COMMIT;
1624
1625
1626
1627 ---------------- BIS -------------
1628
1629 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BIS_PFMC_MEASURES', p_instance_id, NULL);
1630
1631
1632
1633 COMMIT;
1634
1635
1636
1637 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BIS_TARGET_LEVELS', p_instance_id, NULL);
1638
1639
1640
1641 COMMIT;
1642
1643
1644
1645 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BIS_TARGETS', p_instance_id, NULL);
1646
1647
1648
1649 COMMIT;
1650
1651
1652
1653 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BIS_BUSINESS_PLANS', p_instance_id, NULL);
1654
1655
1656
1657 COMMIT;
1658
1659
1660
1661 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BIS_PERIODS', p_instance_id, NULL);
1662
1663
1664
1665 COMMIT;
1666
1667
1668
1669 ---------------- ATP RULES -------------
1670
1671 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_ATP_RULES', p_instance_id, NULL);
1672
1673
1674
1675 COMMIT;
1676
1677
1678
1679 ---------------- PLANNERS -------------
1680
1681 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_PLANNERS', p_instance_id, NULL);
1682
1683
1684
1685 COMMIT;
1686
1687
1688
1689 ---------------- DEMAND CLASS -------------
1690
1691 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_DEMAND_CLASSES', p_instance_id, NULL);
1692
1693
1694
1695 COMMIT;
1696
1697
1698
1699 ---------------- PARTNER CONTACTS -----------
1700
1701 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_PARTNER_CONTACTS', p_instance_id, NULL);
1702
1703
1704
1705 COMMIT;
1706
1707
1708
1709 ---------------- LEGACY TABLES --------------
1710
1711 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_ITEM_SOURCING',p_instance_id, NULL);
1712
1713
1714
1715 COMMIT;
1716
1717
1718
1719 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_CALENDARS',p_instance_id, NULL);
1720
1721
1722
1723 COMMIT;
1724
1725
1726
1727 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_WORKDAY_PATTERNS',p_instance_id, NULL);
1728
1729
1730
1731 COMMIT;
1732
1733
1734
1735 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_CALENDAR_EXCEPTIONS',p_instance_id, NULL);
1736
1737
1738
1739 COMMIT;
1740
1741
1742
1743 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_GROUPS',p_instance_id, NULL);
1744
1745
1746
1747 COMMIT;
1748
1749
1750
1751 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_GROUP_COMPANIES',p_instance_id, NULL);
1752
1753
1754
1755 COMMIT;
1756
1757
1758
1759 /* SCE change starts */
1760
1761 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_COMPANY_USERS', p_instance_id, NULL);
1762
1763
1764
1765 COMMIT;
1766
1767
1768
1769 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_ITEM_CUSTOMERS', p_instance_id, NULL);
1770
1771
1772
1773 COMMIT;
1774
1775 /* SCE change ends */
1776
1777
1778
1779 ------------- JOB DETAILS ---------------------
1780
1781
1782
1783 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_JOB_OPERATION_NETWORKS',p_instance_id, NULL);
1784
1785
1786
1787 COMMIT;
1788
1789
1790
1791 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_JOB_OPERATIONS',p_instance_id, NULL);
1792
1793
1794
1795 COMMIT;
1796
1797
1798
1799 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_JOB_REQUIREMENT_OPS',p_instance_id, NULL);
1800
1801
1802
1803 COMMIT;
1804
1805
1806
1807 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_JOB_OP_RESOURCES',p_instance_id, NULL);
1808
1809
1810
1811 COMMIT;
1812
1813
1814
1815 ------------- TRIP ---------------------
1816
1817
1818
1819 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_TRIPS',p_instance_id, NULL);
1820
1821
1822
1823 COMMIT;
1824
1825
1826
1827 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_TRIP_STOPS',p_instance_id, NULL);
1828
1829
1830
1831 COMMIT;
1832
1833
1834
1835 ----------- PROFILE ----------------
1836
1837
1838
1839 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_APPS_INSTANCES',p_instance_id, NULL);
1840
1841
1842
1843 COMMIT;
1844
1845
1846
1847
1848
1849 /* ds_change: start */
1850
1851 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_DEPT_RES_INSTANCES',p_instance_id, NULL);
1852
1853 commit;
1854
1855 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_NET_RES_INST_AVAIL',p_instance_id, NULL);
1856
1857 commit;
1858
1859 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_JOB_OP_RES_INSTANCES',p_instance_id, NULL);
1860
1861 commit;
1862
1863 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RESOURCE_INSTANCE_REQS',p_instance_id, NULL);
1864
1865 commit;
1866
1867 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RESOURCE_SETUPS',p_instance_id, NULL);
1868
1869 commit;
1870
1871 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SETUP_TRANSITIONS',p_instance_id, NULL);
1872
1873 commit;
1874
1875 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_STD_OP_RESOURCES',p_instance_id, NULL);
1876
1877 commit;
1878
1879 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RES_INSTANCE_CHANGES',p_instance_id, NULL);
1880
1881 commit;
1882
1883 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RESOURCE_CHARGES',p_instance_id, NULL);
1884
1885 commit;
1886
1887 /* ds_change: end */
1888
1889 ---------------- LOOKUPS -------------
1890
1891 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SR_LOOKUPS', p_instance_id, NULL);
1892
1893 COMMIT;
1894
1895 ---------------------- FISCAL CALENDAR -----------------------------
1896
1897 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_CALENDAR_MONTHS',p_instance_id, NULL);
1898
1899 commit;
1900
1901
1902
1903 END PURGE_STAGING_TABLES_DEL;
1904
1905
1906
1907 PROCEDURE PURGE_STAGING_TABLES_SUB( p_instance_id IN NUMBER,
1908 p_Blind_Purge IN NUMBER:=SYS_NO)
1909
1910 IS
1911
1912 lv_control_flag NUMBER;
1913
1914
1915
1916 lv_sql_stmt VARCHAR2(2048);
1917
1918 lv_pbs NUMBER;
1919
1920
1921
1922 lv_instance_type NUMBER;
1923
1924 lv_last_refresh_type VARCHAR2(1);
1925
1926
1927
1928 lv_retval boolean;
1929
1930 lv_dummy1 varchar2(32);
1931
1932 lv_dummy2 varchar2(32);
1933
1934 lv_schema varchar2(30);
1935
1936 lv_prod_short_name varchar2(30);
1937
1938
1939
1940 CURSOR c_tab_list IS
1941
1942 SELECT attribute1 application_id, attribute2 table_name, attribute5 part_type
1943 FROM fnd_lookup_values
1944 WHERE lookup_type = 'MSC_STAGING_TABLE' AND
1945 enabled_flag = 'Y' AND
1946 view_application_id = 700 AND
1947 language = userenv('lang');
1948
1949
1950
1951 BEGIN
1952 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'inside purge staging table ');
1953
1954 SELECT decode(nvl(fnd_profile.value('MSC_PURGE_ST_CONTROL'),'N'),'Y',1,2)
1955 INTO lv_control_flag
1956 FROM dual;
1957
1958 IF p_instance_id IS NOT NULL THEN
1959 SELECT instance_type, lrtype
1960 INTO lv_instance_type, lv_last_refresh_type
1961 FROM msc_apps_instances
1962 WHERE instance_id= p_instance_id;
1963 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'instance_type='||lv_instance_type);
1964 END IF;
1965 lv_pbs := nvl(TO_NUMBER(FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')), 2000);
1966 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'before loop');
1967 FOR c_rec IN c_tab_list
1968 LOOP
1969
1970 lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(to_number(c_rec.application_id));
1971 lv_retval := FND_INSTALLATION.GET_APP_INFO (lv_prod_short_name, lv_dummy1, lv_dummy2, lv_schema);
1972 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'Schema name - ' || lv_schema);
1973
1974
1975 IF (p_Blind_Purge = SYS_YES) OR
1976 (lv_control_flag = 1 AND lv_instance_type <> G_INS_OTHER AND c_rec.part_type <> 'L') THEN -- do a blind purge
1977 lv_sql_stmt:= 'TRUNCATE TABLE ' || lv_schema || '.' || c_rec.table_name||' DROP STORAGE';
1978 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'lv_sql_stmt1 - ' || lv_sql_stmt);
1979
1980 EXECUTE IMMEDIATE lv_sql_stmt;
1981
1982 ELSIF (lv_instance_type = G_INS_OTHER) OR (c_rec.part_type <> 'L') THEN
1983 lv_sql_stmt:= ' DELETE ' || c_rec.table_name
1984 || ' WHERE sr_instance_id = ' || p_instance_id
1985 || ' AND rownum < ' || lv_pbs;
1986 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'lv_sql_stmt1 - ' || lv_sql_stmt);
1987 LOOP
1988 EXECUTE IMMEDIATE lv_sql_stmt;
1989 EXIT WHEN SQL%ROWCOUNT = 0;
1990 COMMIT;
1991 END LOOP;
1992
1993 ELSE
1994
1995 lv_sql_stmt:= 'ALTER TABLE ' || lv_schema || '.' || c_rec.table_name
1996 || ' TRUNCATE PARTITION ' || SUBSTR(c_rec.table_name, 8) || '_' || p_instance_id;
1997
1998 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'lv_sql_stmt2 - ' || lv_sql_stmt);
1999 EXECUTE IMMEDIATE lv_sql_stmt;
2000 END IF;
2001
2002 END LOOP;
2003 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'exiting purge staging table ');
2004
2005
2006
2007 EXCEPTION
2008 WHEN OTHERS THEN
2009 IF SQLCODE IN (-01578,-26040) THEN
2010 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
2011 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'To rectify this problem -');
2012 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Run concurrent program "Truncate Planning Staging Tables" ');
2013 END IF;
2014 RAISE;
2015 END PURGE_STAGING_TABLES_SUB;
2016 PROCEDURE TRUNCATE_STAGING_TABLES(ERRBUF OUT NOCOPY VARCHAR2,
2017 RETCODE OUT NOCOPY NUMBER)
2018 AS
2019 BEGIN
2020 PURGE_STAGING_TABLES_SUB( p_instance_id => NULL,
2021 p_Blind_Purge => SYS_YES);
2022 EXCEPTION
2023 WHEN OTHERS THEN
2024 RETCODE := G_ERROR;
2025 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
2026 RAISE;
2027 END TRUNCATE_STAGING_TABLES;
2028 END msc_cl_purge_staging ;