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