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