[Home] [Help]
PACKAGE BODY: APPS.MSD_PURGE
Source
1 PACKAGE BODY MSD_PURGE AS
2 /* $Header: msdpurgb.pls 115.17 2004/06/30 16:58:10 jarora ship $ */
3
4
5 /* The following are helper cursors and procedures for deleting
6 * information in the headers table for custom streams. There are
7 * two important cases:
8 *
9 * 1. When the date is specified: In this case it is not known
10 * which information is deleted from the msd_cs_data table.
11 * Therfore, each header needs to be checked if a row
12 * exists in the msd_cs_data table that refers to it.
13 *
14 * 2. When no date is specified the data from msd_cs_data_headers
15 * can immediately be deleted.
16 */
17
18 /* If given the name of a stream this provides the definition id.
19 * This is useful when on the UI, there is no option to select
20 * the custom stream, but represented in a hard-coded label.
21 */
22
23 CURSOR get_cs_definition_id(p_cs_name in VARCHAR2) IS
24 SELECT cs_definition_id
25 FROM msd_cs_definitions
26 WHERE name = p_cs_name;
27
28 CURSOR get_cs_system_flag(p_cs_iden in VARCHAR2) IS
29 SELECT system_flag
30 FROM msd_cs_definitions
31 WHERE cs_definition_id = p_cs_iden;
32
33
34 /* Helper Functions */
35
36
37 /* Helper procedure for deleting data from custom stream tables.
38 */
39
40 procedure delete_cs_data ( p_instance_id IN varchar2,
41 p_cs_definition_id IN number,
42 p_cs_designator IN varchar2,
43 p_from_date IN date,
44 p_to_date IN date ) IS
45
46 TYPE cs_data_id_tab is table of msd_cs_data.cs_data_id%TYPE;
47
48 t_cs_data_id cs_data_id_tab;
49
50
51 cursor get_cs_data is
52 select cs_data_id
53 from msd_cs_data
54 where cs_definition_id in (select cs_definition_id
55 from msd_cs_definitions
56 where cs_definition_id = nvl(p_cs_definition_id , cs_definition_id)
57 and ((p_cs_definition_id is not null) or
58 (system_flag = 'C')))
59 and nvl(cs_name, '#$#$^&&&!!!!!!$%$%$%$%090@@') = nvl(p_cs_designator, nvl(cs_name, '#$#$^&&&!!!!!!$%$%$%$%090@@'))
60 and nvl(attribute_43, '0001/01/01') between nvl(to_char(p_from_date, 'YYYY/MM/DD'), '0001/01/01')
61 and nvl(to_char(p_to_date, 'YYYY/MM/DD'), '4317/12/31')
62 and nvl(attribute_1, '-999') = nvl(p_instance_id, nvl(attribute_1, '-999'));
63
64 C_NUM_DELETE_ROWS number := 1000;
65
66 begin
67
68 open get_cs_data;
69
70 loop
71
72 fetch get_cs_data bulk collect into t_cs_data_id LIMIT C_NUM_DELETE_ROWS;
73
74 if (t_cs_data_id.exists(1)) then
75
76 FORALL i IN t_cs_data_id.FIRST..t_cs_data_id.LAST
77 DELETE FROM msd_cs_data
78 WHERE cs_data_id = t_cs_data_id(i);
79
80 FORALL i IN t_cs_data_id.FIRST..t_cs_data_id.LAST
81 DELETE FROM msd_cs_data_ds
82 WHERE cs_data_id = t_cs_data_id(i);
83 else
84 exit;
85 end if;
86
87 end loop;
88
89 close get_cs_data;
90
91 /* Analyze Custom Stream Tables */
92 MSD_ANALYZE_TABLES.analyze_table(null, 5);
93 /* End Analyze */
94
95 end delete_cs_data;
96
97 /* Table handler for the cs_data_header table. */
98 procedure delete_cs_headers (p_instance_id IN NUMBER,
99 p_cs_def_id IN NUMBER,
100 p_cs_name IN VARCHAR2) IS
101
102 begin
103
104 delete from msd_cs_data_headers
105 where instance = nvl(p_instance_id, instance)
106 and cs_definition_id = nvl(p_cs_def_id, cs_definition_id)
107 and cs_name = nvl(p_cs_name, cs_name);
108
109
110
111 end delete_cs_headers;
112
113
114 /* Check for each header whether it contains a child row in msd_cs_data.
115 * This procedure is called when date parameters are entered in purge.
116 */
117 procedure check_cs_headers (p_instance_id IN NUMBER,
118 p_cs_def_id IN NUMBER,
119 p_cs_name IN VARCHAR2) IS
120
121 x_num_rows number := 1;
122
123 cursor check_cs_data(p_instance_id in NUMBER, p_cs_id in number, p_cs_name in VARCHAR2) is
124 select 1
125 from msd_cs_data
126 where attribute_1 = p_instance_id
127 and cs_definition_id = p_cs_id
128 and cs_name = p_cs_name
129 and rownum = x_num_rows;
130
131 cursor check_cs_data_headers is
132 select *
133 from msd_cs_data_headers
134 where instance = nvl(p_instance_id, instance)
135 and cs_definition_id = nvl(p_cs_def_id, cs_definition_id)
136 and cs_name = nvl(p_cs_name, cs_name)
137 and exists ( select 1
138 from msd_cs_definitions csd
139 where csd.cs_definition_id = msd_cs_data_headers.cs_definition_id
140 and csd.system_flag = 'C' );
141
142 x_count number := 0;
143
144 begin
145
146 /* Loop through all headers */
147
148 for cs_rec in check_cs_data_headers loop
149
150 open check_cs_data(cs_rec.instance, cs_rec.cs_definition_id, cs_rec.cs_name);
151 fetch check_cs_data into x_count;
152 if (check_cs_data%NOTFOUND) then
153 delete_cs_headers(cs_rec.instance, cs_rec.cs_definition_id, cs_rec.cs_name);
154 end if;
155 close check_cs_data;
156
157 x_count := 0;
158
159 end loop;
160
161 end check_cs_headers;
162
163
164 /* This procedure is called after data is deleted from msd_cs_data.
165 * It will determine whether dates are defined and determine
166 * whether headers need to check for children in msd_cs_data
167 */
168
169 procedure purge_cs_data_headers(p_instance_id IN NUMBER,
170 p_from_date IN VARCHAR2,
171 p_to_date IN VARCHAR2,
172 p_cs_def_id IN NUMBER,
173 p_cs_name IN VARCHAR2) IS
174
175 begin
176
177 /* Date is included so the msd_cs_data needs to be checked for rows
178 * that are deleted.
179 */
180
181 if (p_from_date is not null) or (p_to_date is not null) then
182
183 /* Deletes data in headers checking date-filtered deletes in msd_cs_data */
184 check_cs_headers (p_instance_id, p_cs_def_id, p_cs_name);
185
186 else
187
188 /* Deletes directly from headers since no date filtering done. */
189
190 delete from msd_cs_data_headers
191 where instance = nvl(p_instance_id, instance)
192 and cs_definition_id = nvl(p_cs_def_id, cs_definition_id)
193 and cs_name = nvl(p_cs_name, cs_name)
194 and exists ( select 1
195 from msd_cs_definitions csd
196 where csd.cs_definition_id = msd_cs_data_headers.cs_definition_id
197 and csd.system_flag = 'C' );
198
199 end if;
200
201 end purge_cs_data_headers;
202
203 /* Check for each header whether it contains a child row in msd_cs_data.
204 * This procedure is called when date parameters are entered in purge.
205 */
206 procedure check_mfg_headers (p_instance_id IN NUMBER,
207 p_cs_def_id IN NUMBER,
208 p_cs_name IN VARCHAR2) IS
209
210 x_num_rows number := 1;
211
212 cursor check_mfg_data(p_instance_id in NUMBER, p_cs_id in number, p_cs_name in VARCHAR2) is
213 select 1
214 from msd_mfg_forecast
215 where instance = p_instance_id
216 and forecast_designator = p_cs_name
217 and rownum = x_num_rows;
218
219 cursor check_cs_data_headers is
220 select *
221 from msd_cs_data_headers
222 where instance = nvl(p_instance_id, instance)
223 and cs_definition_id = nvl(p_cs_def_id, cs_definition_id)
224 and cs_name = nvl(p_cs_name, cs_name);
225
226 x_count number := 0;
227
228 begin
229
230 /* Loop through all headers */
231
232 for cs_rec in check_cs_data_headers loop
233
234 open check_mfg_data(cs_rec.instance, cs_rec.cs_definition_id, cs_rec.cs_name);
235 fetch check_mfg_data into x_count;
236 if (check_mfg_data%NOTFOUND) then
237 delete_cs_headers(cs_rec.instance, cs_rec.cs_definition_id, cs_rec.cs_name);
238 end if;
239 close check_mfg_data;
240
241 x_count := 0;
242
243 end loop;
244
245 end check_mfg_headers;
246
247
248 /* This procedure is called after data is deleted from msd_mfg_forecast.
249 * It will determine whether dates are defined and determine
250 * whether headers need to check for children in msd_mfg_forecast
251 */
252
253 procedure purge_mfg_data_headers(p_instance_id IN NUMBER,
254 p_cs_def_id IN NUMBER,
255 p_l_date IN NUMBER,
256 p_cs_name IN VARCHAR2) IS
257
258 begin
259
260 /* Date is included so the msd_cs_data needs to be checked for rows
261 * that are deleted.
262 */
263
264 if (p_l_date <> 0) then
265
266 /* Deletes data in headers checking date-filtered deletes in msd_cs_data */
267 check_mfg_headers (p_instance_id, p_cs_def_id, p_cs_name);
268
269 else
270
271 /* Deletes directly from headers since no date filtering done. */
272 delete_cs_headers(p_instance_id, p_cs_def_id, p_cs_name);
273
274 end if;
275
276 end purge_mfg_data_headers;
277
278 /* Check for each header whether it contains a child row in msd_cs_data.
279 * This procedure is called when date parameters are entered in purge.
280 */
281 procedure check_int_headers (p_instance_id IN NUMBER,
282 p_cs_def_id IN NUMBER,
283 p_cs_name IN VARCHAR2) IS
284
285 x_num_rows number := 1;
286
287 cursor check_int_data(p_instance_id in NUMBER, p_cs_id in number, p_cs_name in VARCHAR2) is
288 select 1
289 from msd_cs_data
290 where attribute_1 = p_instance_id
291 and cs_definition_id = p_cs_id
292 and cs_name = p_cs_name
293 and rownum = x_num_rows;
294
295 cursor check_cs_data_headers is
296 select *
297 from msd_cs_data_headers
298 where instance = nvl(p_instance_id, instance)
299 and cs_definition_id = p_cs_def_id
300 and cs_name = nvl(p_cs_name, cs_name);
301
302 x_count number := 0;
303
304 begin
305
306 /* Loop through all headers */
307
308 for cs_rec in check_cs_data_headers loop
309
310 open check_int_data(cs_rec.instance, cs_rec.cs_definition_id, cs_rec.cs_name);
311 fetch check_int_data into x_count;
312 if (check_int_data%NOTFOUND) then
313 delete_cs_headers(cs_rec.instance, cs_rec.cs_definition_id, cs_rec.cs_name);
314 end if;
315 close check_int_data;
316
317 x_count := 0;
318
319 end loop;
320
321 end check_int_headers;
322
323
324 /* This procedure is called after data is deleted from msd_cs_data.
325 * It will determine whether dates are defined and determine
326 * whether headers need to check for children in msd_cs_data
327 */
328
329 procedure purge_int_data_headers(p_instance_id IN NUMBER,
330 p_from_date IN VARCHAR2,
331 p_to_date IN VARCHAR2,
332 p_cs_def_id IN NUMBER,
333 p_cs_name IN VARCHAR2) IS
334
335 begin
336
337 /* Date is included so the msd_cs_data needs to be checked for rows
338 * that are deleted.
339 */
340
341 if (p_from_date is not null) or (p_to_date is not null) then
342
343 /* Deletes data in headers checking date-filtered deletes in msd_cs_data */
344 check_int_headers (p_instance_id, p_cs_def_id, p_cs_name);
345
346 else
347
348 /* Deletes directly from headers since no date filtering done. */
349
350 delete from msd_cs_data_headers
351 where instance = nvl(p_instance_id, instance)
352 and cs_definition_id = p_cs_def_id
353 and cs_name = nvl(p_cs_name, cs_name);
354
355 end if;
356
357 end purge_int_data_headers;
358
359
360
361 /* Public Procedures */
362
363 procedure purge_facts(
364 errbuf OUT NOCOPY VARCHAR2,
365 retcode OUT NOCOPY VARCHAR2,
366 p_instance_id IN NUMBER,
367 p_from_date IN VARCHAR2,
368 p_to_date IN VARCHAR2,
369 p_shipment_yes_no IN NUMBER,
370 p_booking_yes_no IN NUMBER,
371 p_mfg_fcst_yes_no IN NUMBER,
372 p_mfg_fcst_desg IN VARCHAR2,
373 p_sales_opp_yes_no IN NUMBER,
374 p_cust_order_yes_no IN NUMBER,
375 p_cust_sales_yes_no IN NUMBER,
376 p_cs_data_yes_no IN NUMBER,
377 p_cs_definition_id IN NUMBER,
378 p_cs_designator IN VARCHAR2,
379 p_curr_yes_no IN NUMBER,
380 p_uom_yes_no IN NUMBER,
381 p_time_yes_no IN NUMBER,
382 p_calendar_code IN VARCHAR2,
383 p_pricing_yes_no IN NUMBER,
384 p_price_list IN VARCHAR2,
385 p_scn_ent_yes_no IN NUMBER,
386 p_demand_plan_id IN NUMBER,
387 p_scenario_id IN NUMBER,
388 p_revision IN VARCHAR2,
389 p_level_values_yes_no IN NUMBER
390 ) IS
391
392 x_from_date DATE;
393 x_to_date DATE;
394 l_delete_from varchar2(500);
395 l_date_where varchar2(500);
396 l_where varchar2(500);
397 l_final_str varchar2(4000);
398 l_date_used number;
399 x_cs_id number;
400 x_sys_flag varchar2(100);
401
402 l_latest_revision number;
403 b_delete_denorm boolean := false;
404
405 begin
406 l_date_used := 0;
407 x_from_date := FND_DATE.canonical_to_date(p_from_date);
408 x_to_date := FND_DATE.canonical_to_date(p_to_date);
409
410 /* Build the date filter where-clause */
411
412 if x_from_date is not null and x_to_date is not null then
413 l_date_where := l_date_where ||
414 ' between to_date(''' ||
415 to_char(x_from_date, 'dd-mon-yyyy') || ''',''DD-MON-RRRR'') AND to_date(''' ||
416 to_char(x_to_date, 'dd-mon-yyyy') || ''',''DD-MON-RRRR'') ' ;
417 l_date_used := 1;
418 elsif x_to_date is not null then
419 l_date_where := l_date_where ||
420 ' <= to_date(''' ||
421 to_char(x_to_date, 'dd-mon-yyyy') || ''',''DD-MON-RRRR'') ' ;
422 l_date_used := 1;
423 elsif x_from_date is not null then
424 l_date_where := l_date_where ||
425 ' >= to_date(''' ||
426 to_char(x_from_date, 'dd-mon-yyyy') || ''',''DD-MON-RRRR'') ' ;
427 l_date_used := 1;
428 end if ;
429
430 -- Delete Shipment data
431 if (p_shipment_yes_no = MSD_COMMON_UTILITIES.MSD_YES_FLAG) then
432 l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.SHIPMENT_FACT_TABLE;
433 l_where := ' where instance = nvl(''' || p_instance_id || ''',instance)' ;
434 if(l_date_used = 1) then
435 l_where := l_where || ' and shipped_date ' || l_date_where;
436 end if;
437 l_final_str := l_delete_from || l_where;
438 EXECUTE IMMEDIATE l_final_str;
439
440 /* Analyze Shipment Fact Tables */
441 MSD_ANALYZE_TABLES.analyze_table(MSD_COMMON_UTILITIES.SHIPMENT_FACT_TABLE,null);
442 /* End Analyze Shipment Fact Tables */
443
444 end if;
445
446 -- Delete Booking data
447 if (p_booking_yes_no = MSD_COMMON_UTILITIES.MSD_YES_FLAG) then
448 l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.BOOKING_FACT_TABLE;
449 l_where := ' where instance = nvl(''' || p_instance_id || ''',instance)' ;
450 if(l_date_used = 1) then
451 l_where := l_where || ' and booked_date ' || l_date_where;
452 end if;
453 l_final_str := l_delete_from || l_where;
454 EXECUTE IMMEDIATE l_final_str;
455
456 /* Analyze Booking Fact Tables */
457 MSD_ANALYZE_TABLES.analyze_table(MSD_COMMON_UTILITIES.BOOKING_FACT_TABLE,null);
458 /* End Analyze Booking Fact Tables */
459
460 end if;
461
462
463
464 -- Delete Manufacturing Forecast data
465 if (p_mfg_fcst_yes_no = MSD_COMMON_UTILITIES.MSD_YES_FLAG) then
466 l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.MFG_FCST_FACT_TABLE;
467 l_where := ' where instance = nvl(''' || p_instance_id || ''',instance)' ;
468 if(l_date_used = 1) then
469 l_where := l_where || ' and forecast_date ' || l_date_where;
470 end if;
471 l_where := l_where || ' and forecast_designator = nvl(:p_mfg_fcst_desg, forecast_designator)' ;
472 l_final_str := l_delete_from || l_where;
473 EXECUTE IMMEDIATE l_final_str using p_mfg_fcst_desg;
474
475 open get_cs_definition_id('MSD_MANUFACTURING_FORECAST');
476 fetch get_cs_definition_id into x_cs_id;
477 close get_cs_definition_id;
478
479 /* Remove header information. If necesary.*/
480 purge_mfg_data_headers(
481 p_instance_id,
482 x_cs_id,
483 l_date_used,
484 p_mfg_fcst_desg);
485
486 /* End Remove headers */
487
488 /* Analyze Manufacturing Forecast Fact Tables */
489 MSD_ANALYZE_TABLES.analyze_table(MSD_COMMON_UTILITIES.MFG_FCST_FACT_TABLE, null);
490 /* End Analyze Manufacturing Forecast Fact Tables */
491
492
493 end if;
494
495 -- Delete Sales Opportunity data
496 if (p_sales_opp_yes_no = MSD_COMMON_UTILITIES.MSD_YES_FLAG) then
497
498 open get_cs_definition_id('MSD_SALES_OPPORTUNITY');
499 fetch get_cs_definition_id into x_cs_id;
500 close get_cs_definition_id;
501
502 delete_cs_data ( p_instance_id,
503 x_cs_id,
504 null,
505 x_from_date,
506 x_to_date );
507
508 purge_int_data_headers(
509 p_instance_id,
510 p_from_date,
511 p_to_date,
512 x_cs_id,
513 null);
514 end if;
515
516
517 /** Added for Bug 2488293 - PURGE CUSTOM STREAM DATA GOT FROM DP-CP INTEGRATION **/
518 -- Delete Customer Orders Forecast data
519 if (p_cust_order_yes_no = MSD_COMMON_UTILITIES.MSD_YES_FLAG) then
520
521 open get_cs_definition_id('MSD_CUSTOMER_ORDER_FORECAST');
522 fetch get_cs_definition_id into x_cs_id;
523 close get_cs_definition_id;
524
525 delete_cs_data ( p_instance_id,
526 x_cs_id,
527 null,
528 x_from_date,
529 x_to_date );
530
531 purge_int_data_headers(
532 p_instance_id,
533 p_from_date,
534 p_to_date,
535 x_cs_id,
536 null);
537
538 end if;
539
540 -- Delete Customer Sales Forecast data
541 if (p_cust_sales_yes_no = MSD_COMMON_UTILITIES.MSD_YES_FLAG) then
542
543 open get_cs_definition_id('MSD_CUSTOMER_SALES_FORECAST');
544 fetch get_cs_definition_id into x_cs_id;
545 close get_cs_definition_id;
546
547
548 delete_cs_data ( p_instance_id,
549 x_cs_id,
550 null,
551 x_from_date,
552 x_to_date );
553
554 purge_int_data_headers(p_instance_id,
555 p_from_date,
556 p_to_date,
557 x_cs_id,
558 null);
559 end if;
560
561
562 /** Delete Custom Data **/
563 if p_cs_data_yes_no = MSD_COMMON_UTILITIES.MSD_YES_FLAG then
564
565 if (p_cs_definition_id is not null) then
566 open get_cs_system_flag(p_cs_definition_id);
567 fetch get_cs_system_flag into x_sys_flag;
568 close get_cs_system_flag;
569 end if;
570
571 if (x_sys_flag = 'I') then
572
573 /* Seeded custom stream can only be deleted one at a time.
574 * Therefore the user must have specified the id of the
575 * stream.
576 */
577
578 if(p_cs_definition_id is not null) then
579
580 delete_cs_data ( p_instance_id,
581 p_cs_definition_id,
582 p_cs_designator,
583 x_from_date,
584 x_to_date );
585
586 purge_int_data_headers(p_instance_id,
587 p_from_date,
588 p_to_date,
589 p_cs_definition_id,
590 p_cs_designator);
591
592 end if;
593
594 else
595
596 delete_cs_data ( p_instance_id,
597 p_cs_definition_id,
598 p_cs_designator,
599 x_from_date,
600 x_to_date );
601
602 purge_cs_data_headers(p_instance_id,
603 p_from_date,
604 p_to_date,
605 p_cs_definition_id,
606 p_cs_designator);
607
608 end if;
609
610 end if;
611
612 /** Delete Currency data **/
613 if (p_curr_yes_no = MSD_COMMON_UTILITIES.MSD_YES_FLAG) then
614 l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.CURRENCY_FACT_TABLE;
615 l_final_str := l_delete_from;
616 EXECUTE IMMEDIATE l_final_str;
617
618 /* Analyze Currency Code Fact Tables */
619 MSD_ANALYZE_TABLES.analyze_table(MSD_COMMON_UTILITIES.CURRENCY_FACT_TABLE, null);
620 /* End Analyze Currency Code Fact Tables */
621
622 end if;
623
624 -- Delete UOM data
625 if (p_uom_yes_no = MSD_COMMON_UTILITIES.MSD_YES_FLAG) then
626 l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.UOM_FACT_TABLE;
627 l_where := ' where nvl(instance,1) = nvl(''' || p_instance_id || ''',nvl(instance,1))' ;
628 l_final_str := l_delete_from || l_where;
629 EXECUTE IMMEDIATE l_final_str;
630
631 /* Analyze Uom Conversion Fact Tables */
632 MSD_ANALYZE_TABLES.analyze_table(MSD_COMMON_UTILITIES.UOM_FACT_TABLE, null);
633 /* End Analyze Uom Conversion Fact Tables */
634
635 end if;
636
637 -- Delete Time data
638 if (p_time_yes_no = MSD_COMMON_UTILITIES.MSD_YES_FLAG) then
639
640 l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.TIME_FACT_TABLE;
641
642 if (p_calendar_code = 'GREGORIAN') then
643
644 l_where := ' where calendar_type = ' || MSD_COMMON_UTILITIES.GREGORIAN_CALENDAR;
645 if(l_date_used = 1) then
646 l_where := l_where || ' and day ' || l_date_where;
647 end if;
648 l_final_str := l_delete_from || l_where;
649 EXECUTE IMMEDIATE l_final_str;
650
651 else
652
653 l_where := ' where instance = nvl(''' || p_instance_id || ''',instance)' ;
654 l_where := l_where || ' and calendar_code = nvl(:p_calendar_code,calendar_code)' ;
655 if(l_date_used = 1) then
656 l_where := l_where || ' and day ' || l_date_where;
657 end if;
658 l_final_str := l_delete_from || l_where;
659 EXECUTE IMMEDIATE l_final_str using p_calendar_code;
660
661 end if;
662
663 /* Analyze Time Fact Tables */
664 MSD_ANALYZE_TABLES.analyze_table(MSD_COMMON_UTILITIES.TIME_FACT_TABLE, null);
665 /* End Analyze Time Fact Tables */
666
667 end if;
668
669
670 -- Delete Pricing data
671 if (p_pricing_yes_no = MSD_COMMON_UTILITIES.MSD_YES_FLAG) then
672 l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.PRICING_FACT_TABLE;
673 l_where := ' where instance = nvl(''' || p_instance_id || ''',instance)' ;
674 l_where := l_where || ' and price_list_name = nvl(:p_price_list, price_list_name)' ;
675 l_final_str := l_delete_from || l_where;
676 EXECUTE IMMEDIATE l_final_str using p_price_list;
677
678 /* Analyze Pricing Fact Tables */
679 MSD_ANALYZE_TABLES.analyze_table(MSD_COMMON_UTILITIES.PRICING_FACT_TABLE, null);
680 /* End Analyze Pricing Fact Tables */
681
682 end if;
683
684
685 -- Delete Scenario entries data
686 if (p_scn_ent_yes_no = MSD_COMMON_UTILITIES.MSD_YES_FLAG) then
687
688 IF p_revision is not null THEN
689
690 SELECt nvl(max(revision), -999) into l_latest_revision
691 from msd_dp_scenario_revisions
692 where demand_plan_id = p_demand_plan_id and
693 scenario_id = p_scenario_id;
694
695 IF (p_revision = l_latest_revision) THEN
696 b_delete_denorm := TRUE;
697 ELSE
698 b_delete_denorm := FALSE;
699 END IF;
700 END IF;
701
702 l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.SCENARIO_ENTRIES_TABLE;
703 l_where := ' where nvl(instance,-999) = nvl(''' || p_instance_id || ''', nvl(instance,-999))' ;
704 l_where := l_where || ' and demand_plan_id = nvl(''' || p_demand_plan_id || ''',demand_plan_id)' ;
705 l_where := l_where || ' and scenario_id = nvl(''' || p_scenario_id || ''',scenario_id)' ;
706 l_where := l_where || ' and revision = nvl(''' || p_revision || ''',revision)' ;
707 l_final_str := l_delete_from || l_where;
708 EXECUTE IMMEDIATE l_final_str;
709
710 l_delete_from := 'delete from msd_dp_scenario_revisions';
711 l_where := ' where demand_plan_id = nvl(''' || p_demand_plan_id || ''',demand_plan_id)' ;
712 l_where := l_where || ' and scenario_id = nvl(''' || p_scenario_id || ''',scenario_id)' ;
713 l_where := l_where || ' and revision = nvl(''' || p_revision || ''',revision)' ;
714 l_final_str := l_delete_from || l_where;
715 EXECUTE IMMEDIATE l_final_str;
716
717 l_delete_from := 'delete from msd_dp_planning_percentages';
718 l_where := ' where demand_plan_id = nvl(''' || p_demand_plan_id || ''',demand_plan_id)' ;
719 l_where := l_where || ' and dp_scenario_id = nvl(''' || p_scenario_id || ''', dp_scenario_id)' ;
720 l_where := l_where || ' and revision = nvl(''' || p_revision || ''',revision)' ;
721 l_where := l_where || ' and nvl(instance,-999) = nvl(''' || p_instance_id || ''', nvl(instance,-999)) ';
722 l_final_str := l_delete_from || l_where;
723 EXECUTE IMMEDIATE l_final_str;
724
725 IF ( p_revision is null or
726 b_delete_denorm = TRUE ) THEN
727
728 l_delete_from := 'delete from msd_dp_scn_entries_denorm ';
729 l_where := ' where nvl(sr_instance_id,-999) = nvl(''' || p_instance_id || ''', nvl(sr_instance_id,-999))' ;
730 l_where := l_where || ' and demand_plan_id = nvl(''' || p_demand_plan_id || ''',demand_plan_id)' ;
731 l_where := l_where || ' and scenario_id = nvl(''' || p_scenario_id || ''',scenario_id) ' ;
732 l_final_str := l_delete_from || l_where;
733 EXECUTE IMMEDIATE l_final_str;
734
735 l_delete_from := 'delete from msd_dp_planning_pct_denorm ';
736 l_where := ' where nvl(sr_instance_id,-999) = nvl(''' || p_instance_id || ''', nvl(sr_instance_id,-999))' ;
737 l_where := l_where || ' and demand_plan_id = nvl(''' || p_demand_plan_id || ''',demand_plan_id)' ;
738 l_where := l_where || ' and dp_scenario_id = nvl(''' || p_scenario_id || ''', dp_scenario_id) ' ;
739 l_final_str := l_delete_from || l_where;
740 EXECUTE IMMEDIATE l_final_str;
741
742 END IF;
743
744 /* Analyze Scenario Entry Fact Tables */
745 MSD_ANALYZE_TABLES.analyze_table(MSD_COMMON_UTILITIES.SCENARIO_ENTRIES_TABLE, null);
746 /* End Analyze Scenario Entry Fact Tables */
747
748
749 end if;
750
751 -- Delete Level value, associations and item attributes data
752 if (p_level_values_yes_no = MSD_COMMON_UTILITIES.MSD_YES_FLAG) then
753 l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.LEVEL_VALUES_FACT_TABLE;
754 l_where := ' where instance = nvl(''' || p_instance_id || ''',instance)' ;
755 l_final_str := l_delete_from || l_where;
756 EXECUTE IMMEDIATE l_final_str;
757
758 l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.LEVEL_ASSOC_FACT_TABLE;
759 l_where := ' where instance = nvl(''' || p_instance_id || ''',instance)' ;
760 l_final_str := l_delete_from || l_where;
761 EXECUTE IMMEDIATE l_final_str;
762
763 l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.ITEM_INFO_FACT_TABLE;
764 l_where := ' where instance = nvl(''' || p_instance_id || ''',instance)' ;
765 l_final_str := l_delete_from || l_where;
766 EXECUTE IMMEDIATE l_final_str;
767
768 l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.MSD_LOCAL_ID_SETUP_TABLE;
769 l_where := ' where instance_id = nvl(''' || p_instance_id || ''',instance_id)' ;
770 l_final_str := l_delete_from || l_where;
771 EXECUTE IMMEDIATE l_final_str;
772
773 l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.LEVEL_ORG_ASSCNS_FACT_TABLE;
774 l_where := ' where instance = nvl(''' || p_instance_id || ''',instance)' ;
775 l_final_str := l_delete_from || l_where;
776 EXECUTE IMMEDIATE l_final_str;
777
778 l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.ITEM_RELATIONSHIPS_FACT_TABLE;
779 l_where := ' where instance_id = nvl(''' || p_instance_id || ''',instance_id)' ;
780 l_final_str := l_delete_from || l_where;
781 EXECUTE IMMEDIATE l_final_str;
782
783
784 /* Added for Deleting Stripes Data */
785 delete from msd_level_values_ds;
786
787 delete from msd_dp_parameters_ds;
788
789 delete from msd_cs_data_ds;
790
791 update msd_demand_plans
792 set build_stripe_level_pk = null,
793 build_stripe_stream_name = null,
794 build_stripe_stream_desig = null,
795 build_stripe_stream_ref_num = null;
796
797 /* End Deleting Stripes Data */
798
799 /* Analyze Level Value Fact Tables */
800 MSD_ANALYZE_TABLES.analyze_table(null,2);
801 /* End Analyze Level Value Fact Tables */
802
803 end if;
804
805 commit;
806
807
808 exception
809 when others then
810 errbuf := substr(SQLERRM,1,150);
811 retcode := -1 ;
812
813 end purge_facts;
814
815 END MSD_PURGE ;
816