[Home] [Help]
PACKAGE BODY: APPS.MSD_PULL_FACT_DATA
Source
1 PACKAGE BODY MSD_PULL_FACT_DATA AS
2 /* $Header: msdpfctb.pls 120.3 2005/12/07 07:38:58 sjagathe noship $ */
3
4
5 /**** Private Procedures Definitions ********/
6 procedure Clean_Staging_Table(
7 errbuf OUT NOCOPY VARCHAR2,
8 retcode OUT NOCOPY VARCHAR2,
9 p_table_name IN VARCHAR2,
10 p_date_column IN VARCHAR2,
11 p_instance_id IN NUMBER,
12 p_from_date IN VARCHAR2,
13 p_to_date IN VARCHAR2,
14 p_fcst_desg IN VARCHAR2 );
15
16
17 procedure Clean_Pricing_Staging_Table(
18 errbuf OUT NOCOPY VARCHAR2,
19 retcode OUT NOCOPY VARCHAR2,
20 p_table_name IN VARCHAR2,
21 p_instance_id IN NUMBER,
22 p_price_list IN VARCHAR2);
23
24
25 C_FROM_DATE CONSTANT DATE := to_date('01-01-1000','DD-MM-YYYY');
26 C_TO_DATE CONSTANT DATE := to_date('01-01-4000','DD-MM-YYYY');
27
28
29
30 /*********** Public Procedures ***********/
31
32 procedure pull_fact_data(
33 errbuf OUT NOCOPY VARCHAR2,
34 retcode OUT NOCOPY VARCHAR2) IS
35
36 Begin
37
38
39 retcode := 0 ;
40
41 pull_shipment_data(
42 errbuf => errbuf,
43 retcode => retcode);
44
45 pull_booking_data(
46 errbuf => errbuf,
47 retcode => retcode);
48
49 pull_uom_conversion(
50 errbuf => errbuf,
51 retcode => retcode ) ;
52
53 pull_currency_conversion(
54 errbuf => errbuf,
55 retcode => retcode ) ;
56
57 /*pull_opportunities_data(
58 errbuf => errbuf,
59 retcode => retcode ) ;
60
61 pull_sales_forecast(
62 errbuf => errbuf,
63 retcode => retcode ) ;*/
64
65 pull_mfg_forecast(
66 errbuf => errbuf,
67 retcode => retcode ) ;
68
69 pull_pricing_data(
70 errbuf => errbuf,
71 retcode => retcode ) ;
72
73
74
75 exception
76
77 when others then
78
79 errbuf := substr(SQLERRM,1,150);
80 retcode := -1 ;
81
82 End pull_fact_data ;
83
84
85
86 procedure pull_shipment_data(
87 errbuf OUT NOCOPY VARCHAR2,
88 retcode OUT NOCOPY VARCHAR2) IS
89 x_source_table VARCHAR2(50) := MSD_COMMON_UTILITIES.SHIPMENT_STAGING_TABLE ;
90 x_dest_table VARCHAR2(50) := MSD_COMMON_UTILITIES.SHIPMENT_FACT_TABLE ;
91 x_delete_flag VARCHAR2(1) := 'Y' ;
92 /******************************************************
93 Cursor to get distinct Instance, Max Data and Min Date
94 ******************************************************/
95 /* DWK. Fix Bug 2220983. Do not include instance = '0' in the cursor */
96 Cursor Shipment is
97 select instance,
98 min(shipped_date) min_ship_date,
99 max(shipped_date) max_ship_date
100 from msd_st_shipment_data
101 where instance <> '0'
102 group by instance ;
103
104 l_new_refresh_num NUMBER;
105
106 Begin
107
108
109 retcode :=0;
110
111 SELECT msd.msd_last_refresh_number_s.nextval into l_new_refresh_Num from dual;
112
113
114 For Shipment_Rec IN Shipment LOOP
115
116 MSD_TRANSLATE_FACT_DATA.translate_shipment_data(
117 errbuf => errbuf,
118 retcode => retcode,
119 p_source_table => x_source_table,
120 p_dest_table => x_dest_table,
121 p_instance_id => Shipment_Rec.instance,
122 p_from_date => Shipment_Rec.min_ship_date,
123 p_to_date => Shipment_Rec.max_ship_date,
124 p_new_refresh_num => l_new_refresh_num,
125 p_delete_flag => x_delete_flag);
126
127 Clean_Staging_Table(
128 errbuf => errbuf,
129 retcode => retcode,
130 p_table_name => x_source_table,
131 p_date_column => MSD_COMMON_UTILITIES.SHIPMENT_DATE_USED,
132 p_instance_id => Shipment_Rec.instance,
133 p_from_date => to_char(Shipment_Rec.min_ship_date, 'dd-mon-rrrr'),
134 p_to_date => to_char(Shipment_Rec.max_ship_date, 'dd-mon-rrrr'),
135 p_fcst_desg => null );
136
137
138 End Loop ;
139
140 /* Delete fact rows that are not used by any demand plans */
141 MSD_TRANSLATE_FACT_DATA.clean_fact_data( errbuf,
142 retcode,
143 x_dest_table);
144
145 commit;
146
147 /* Added by esubrama */
148 MSD_ANALYZE_TABLES.analyze_table('MSD_SHIPMENT_DATA',null);
149 MSD_ANALYZE_TABLES.analyze_table('MSD_ST_SHIPMENT_DATA',null);
150 exception
151
152 when others then
153
154 errbuf := substr(SQLERRM,1,150);
155 retcode := -1 ;
156 rollback;
157
158 End pull_shipment_data ;
159
160 procedure pull_booking_data(
161 errbuf OUT NOCOPY VARCHAR2,
162 retcode OUT NOCOPY VARCHAR2) IS
163
164 x_source_table VARCHAR2(50) := MSD_COMMON_UTILITIES.BOOKING_STAGING_TABLE ;
165 x_dest_table VARCHAR2(50) := MSD_COMMON_UTILITIES.BOOKING_FACT_TABLE ;
166 x_delete_flag VARCHAR2(1) := 'Y' ;
167 /******************************************************
168 Cursor to get distinct Instance, Max Data and Min Date
169 ******************************************************/
170 /* DWK. Fix Bug 2220983. Do not include instance = '0' in the cursor */
171 Cursor Booking is
172 select instance,
173 min(booked_date) min_ship_date,
174 max(booked_date) max_ship_date
175 from msd_st_booking_data
176 where instance <> '0'
177 group by instance ;
178
179 l_new_refresh_num NUMBER;
180
181 Begin
182
183
184 retcode :=0;
185
186 SELECT msd.msd_last_refresh_number_s.nextval into l_new_refresh_Num from dual;
187
188
189 For Booking_Rec IN Booking LOOP
190
191 MSD_TRANSLATE_FACT_DATA.translate_booking_data(
192 errbuf => errbuf,
193 retcode => retcode,
194 p_source_table => x_source_table,
195 p_dest_table => x_dest_table,
196 p_instance_id => Booking_Rec.instance,
197 p_from_date => Booking_Rec.min_ship_date,
198 p_to_date => Booking_Rec.max_ship_date,
199 p_new_refresh_num => l_new_refresh_num,
200 p_delete_flag => x_delete_flag);
201
202 Clean_Staging_Table(
203 errbuf => errbuf,
204 retcode => retcode,
205 p_table_name => x_source_table,
206 p_date_column => MSD_COMMON_UTILITIES.BOOKING_DATE_USED,
207 p_instance_id => Booking_Rec.instance,
208 p_from_date => to_char(Booking_Rec.min_ship_date, 'dd-mon-rrrr'),
209 p_to_date => to_char(Booking_Rec.max_ship_date, 'dd-mon-rrrr'),
210 p_fcst_desg => null );
211
212
213 End Loop ;
214
215 /* Delete fact rows that are not used by any demand plans */
216 MSD_TRANSLATE_FACT_DATA.clean_fact_data( errbuf,
217 retcode,
218 x_dest_table);
219
220
221 commit;
222
223 /* Added by esubrama */
224 MSD_ANALYZE_TABLES.analyze_table('MSD_BOOKING_DATA',null);
225 MSD_ANALYZE_TABLES.analyze_table('MSD_ST_BOOKING_DATA',null);
226
227 EXCEPTION
228
229 when others then
230
231 errbuf := substr(SQLERRM,1,150);
232 retcode := -1 ;
233 rollback;
234
235 End pull_booking_data ;
236
237
238
239 procedure pull_uom_conversion(
240 errbuf OUT NOCOPY VARCHAR2,
241 retcode OUT NOCOPY VARCHAR2) IS
242 x_source_table VARCHAR2(50) := MSD_COMMON_UTILITIES.UOM_STAGING_TABLE ;
243 x_dest_table VARCHAR2(50) := MSD_COMMON_UTILITIES.UOM_FACT_TABLE ;
244
245 /* DWK. Fix Bug 2220983. Do not include instance = '0' in the cursor */
246 Cursor UOM is
247 select instance
248 from msd_st_uom_conversions
249 where instance <> '0'
250 group by instance;
251
252 l_new_refresh_num NUMBER;
253
254 Begin
255
256
257 retcode := 0;
258
259
260 SELECT msd.msd_last_refresh_number_s.nextval into l_new_refresh_Num from dual;
261
262 For UOM_Rec IN UOM LOOP
263
264 MSD_TRANSLATE_FACT_DATA.translate_uom_conversion(
265 errbuf => errbuf,
266 retcode => retcode,
267 p_source_table => x_source_table,
268 p_dest_table => x_dest_table,
269 p_instance_id => UOM_Rec.instance,
270 p_new_refresh_num => l_new_refresh_num ) ;
271
272
273 Clean_Staging_Table(
274 errbuf => errbuf,
275 retcode => retcode,
276 p_table_name => x_source_table,
277 p_date_column => null,
278 p_instance_id => UOM_Rec.instance,
279 p_from_date => null,
280 p_to_date => null,
281 p_fcst_desg => null );
282
283
284 End Loop;
285
286
287 /* Delete fact rows that are not used by any demand plans */
288 /* Not needed. Records are physically deleted
289
290 MSD_TRANSLATE_FACT_DATA.clean_fact_data( errbuf,
291 retcode,
292 x_dest_table);
293 */
294
295 commit;
296
297 /* Added by esubrama */
298 MSD_ANALYZE_TABLES.analyze_table('MSD_UOM_CONVERSIONS',null);
299 MSD_ANALYZE_TABLES.analyze_table('MSD_ST_UOM_CONVERSIONS',null);
300 exception
301
302 when others then
303
304 errbuf := substr(SQLERRM,1,150);
305 retcode := -1 ;
306 rollback;
307
308 End pull_uom_conversion ;
309
310 procedure pull_currency_conversion(
311 errbuf OUT NOCOPY VARCHAR2,
312 retcode OUT NOCOPY VARCHAR2) IS
313 x_source_table VARCHAR2(50) := MSD_COMMON_UTILITIES.CURRENCY_STAGING_TABLE ;
314 x_dest_table VARCHAR2(50) := MSD_COMMON_UTILITIES.CURRENCY_FACT_TABLE ;
315 /******************************************************
316 Cursor to get Max Data and Min Date
317 ******************************************************/
318
319 /* DWK. Fix Bug 2220983. Do not include instance = '0' in the cursor */
320 Cursor Currency_Conversion is
321 select
322 min(conversion_date) min_ship_date,
323 max(conversion_date) max_ship_date
324 from msd_st_currency_conversions
325 where nvl(instance, '-888') <> '0';
326
327 Begin
328
329
330 retcode :=0;
331
332 For Curr_Conv_Rec IN Currency_Conversion LOOP
333
334 /* This condition prevent executing PULL when there is no rows in
335 staging table, since min always returns a row even there is
336 none in the staging table */
337
338 IF Curr_Conv_Rec.min_ship_date is not null THEN
339
340 MSD_TRANSLATE_FACT_DATA.translate_currency_conversion(
341 errbuf => errbuf,
342 retcode => retcode,
343 p_source_table => x_source_table,
344 p_dest_table => x_dest_table,
345 p_instance_id => null,
346 p_from_date => Curr_Conv_Rec.min_ship_date,
347 p_to_date => Curr_Conv_Rec.max_ship_date ) ;
348
349 Clean_Staging_Table(
350 errbuf => errbuf,
351 retcode => retcode,
352 p_table_name => x_source_table,
353 p_instance_id => null,
354 p_date_column => MSD_COMMON_UTILITIES.CURRENCY_DATE_USED,
355 p_from_date => to_char(Curr_Conv_Rec.min_ship_date, 'dd-mon-rrrr'),
356 p_to_date => to_char(Curr_Conv_Rec.max_ship_date, 'dd-mon-rrrr'),
357 p_fcst_desg => null );
358 END IF;
359
360 End Loop ;
361
362 commit;
363
364 /* Added by esubrama */
365 MSD_ANALYZE_TABLES.analyze_table('MSD_CURRENCY_CONVERSIONS',null);
366 MSD_ANALYZE_TABLES.analyze_table('MSD_ST_CURRENCY_CONVERSIONS',null);
367
368 exception
369
370 when others then
371
372 errbuf := substr(SQLERRM,1,150);
373 retcode := -1 ;
374 rollback;
375
376
377 End pull_currency_conversion ;
378
379
380 /* procedure pull_opportunities_data(
381 errbuf OUT NOCOPY VARCHAR2,
382 retcode OUT NOCOPY VARCHAR2) IS
383 x_source_table VARCHAR2(50) := MSD_COMMON_UTILITIES.OPPORTUNITY_STAGING_TABLE ;
384 x_dest_table VARCHAR2(50) := MSD_COMMON_UTILITIES.OPPORTUNITY_FACT_TABLE ;*/
385 /******************************************************
386 Cursor to get distinct Instance, Max Data and Min Date
387 ******************************************************/
388 /* Cursor Opportunity is
389 select instance,
390 min(ship_date) min_ship_date,
391 max(ship_date) max_ship_date
392 from msd_st_sales_opportunity_data
393 group by instance ;
394 Begin
395
396
397 retcode :=0;
398
399 For Opportunity_Rec IN Opportunity LOOP
400
401
402 MSD_TRANSLATE_FACT_DATA.translate_opportunities_data(
403 errbuf => errbuf,
404 retcode => retcode,
405 p_source_table => x_source_table,
406 p_dest_table => x_dest_table,
407 p_instance_id => Opportunity_Rec.instance,
408 p_from_date => Opportunity_Rec.min_ship_date,
409 p_to_date => Opportunity_Rec.max_ship_date ) ;
410
411 Clean_Staging_Table(
412 errbuf => errbuf,
413 retcode => retcode,
414 p_table_name => x_source_table,
415 p_instance_id => Opportunity_Rec.instance,
416 p_date_column => MSD_COMMON_UTILITIES.OPPORTUNITY_DATE_USED,
417 p_from_date => to_char(Opportunity_Rec.min_ship_date, 'dd-mon-rrrr'),
418 p_to_date => to_char(Opportunity_Rec.max_ship_date, 'dd-mon-rrrr'),
419 p_fcst_desg => null );
420
421
422 End Loop ;
423
424 commit;
425
426 -- Added by esubrama
427 MSD_ANALYZE_TABLES.analyze_table('MSD_SALES_OPPORTUNITY_DATA',null);
428 MSD_ANALYZE_TABLES.analyze_table('MSD_ST_SALES_OPPORTUNITY_DATA',null);
429
430 exception
431
432 when others then
433
434 errbuf := substr(SQLERRM,1,150);
435 retcode := -1 ;
436 rollback;
437
438
439 End pull_opportunities_data ;
440
441 */
442
443 /*procedure pull_sales_forecast(
444 errbuf OUT NOCOPY VARCHAR2,
445 retcode OUT NOCOPY VARCHAR2) IS
446 x_source_table VARCHAR2(50) := MSD_COMMON_UTILITIES.SALES_FCST_STAGING_TABLE ;
447 x_dest_table VARCHAR2(50) := MSD_COMMON_UTILITIES.SALES_FCST_FACT_TABLE ;*/
448 /******************************************************
449 Cursor to get distinct Instance, Max Data and Min Date
450 ******************************************************/
451 /*
452 Cursor Sales_Forecast is
453 select instance,
454 min(period_start_date) min_ship_date,
455 max(period_end_date) max_ship_date
456 from msd_st_sales_forecast
457 group by instance ;
458 Begin
459
460
461 retcode :=0;
462
463 For Sales_Fcst_Rec IN Sales_Forecast LOOP
464
465
466 MSD_TRANSLATE_FACT_DATA.translate_sales_forecast(
467 errbuf => errbuf,
468 retcode => retcode,
469 p_source_table => x_source_table,
470 p_dest_table => x_dest_table,
471 p_instance_id => Sales_Fcst_Rec.instance,
472 p_fcst_desg => null,
473 p_from_date => Sales_Fcst_Rec.min_ship_date,
474 p_to_date => Sales_Fcst_Rec.max_ship_date ) ;
475
476
477 Delete from msd_st_sales_forecast
478 where instance = Sales_Fcst_Rec.instance
479 and ( ( to_date(period_start_date,'DD-MON-RRRR')
480 between to_date(Sales_Fcst_Rec.min_ship_date,'DD-MON-RRRR')
481 and to_date(Sales_Fcst_Rec.max_ship_date,'DD-MON-RRRR')
482 )
483 OR ( to_date(period_end_date,'DD-MON-RRRR')
484 between to_date(Sales_Fcst_Rec.min_ship_date,'DD-MON-RRRR')
485 and to_date(Sales_Fcst_Rec.max_ship_date,'DD-MON-RRRR')
486 )
487 );
488
489 commit ;
490
491 End Loop ;
492
493 -- Added by esubrama
494 MSD_ANALYZE_TABLES.analyze_table('MSD_SALES_FORECAST',null);
495 MSD_ANALYZE_TABLES.analyze_table('MSD_ST_SALES_FORECAST',null);
496
497 exception
498
499 when others then
500
501 errbuf := substr(SQLERRM,1,150);
502 retcode := -1 ;
503
504
505 End pull_sales_forecast ;
506 */
507
508
509
510 procedure pull_mfg_forecast(
511 errbuf OUT NOCOPY VARCHAR2,
512 retcode OUT NOCOPY VARCHAR2) IS
513 x_source_table VARCHAR2(50) := MSD_COMMON_UTILITIES.MFG_FCST_STAGING_TABLE ;
514 x_dest_table VARCHAR2(50) := MSD_COMMON_UTILITIES.MFG_FCST_FACT_TABLE ;
515 x_delete_flag VARCHAR2(1) := 'Y' ;
516
517 b_has_error BOOLEAN := FALSE;
518
519 /***************************************************
520 Cursor to get distinct Instance, Forecast Desg
521 ****************************************************/
522
523 /* DWK. Fix Bug 2220983. Do not include instance = '0' in the cursor */
524 Cursor Mfg_Forecast is
525 select instance,
526 forecast_designator
527 from msd_st_mfg_forecast
528 where instance <> '0'
529 group by instance, forecast_designator
530 order by instance;
531
532 /* DWK To Populate calendar */
533 l_temp_instance VARCHAR2(20) := ' ';
534 b_post_process BOOLEAN := TRUE;
535
536 l_new_refresh_num NUMBER;
537
538 Begin
539
540
541 SELECT msd.msd_last_refresh_number_s.nextval into l_new_refresh_Num from dual;
542
543 FOR Mfg_Fcst_Rec IN Mfg_Forecast LOOP
544 DECLARE
545 e_post_process_err EXCEPTION;
546 BEGIN
547
548 retcode :=0;
549
550 MSD_TRANSLATE_FACT_DATA.translate_mfg_forecast(
551 errbuf => errbuf,
552 retcode => retcode,
553 p_source_table => x_source_table,
554 p_dest_table => x_dest_table,
555 p_instance_id => Mfg_Fcst_Rec.instance,
556 p_fcst_desg => Mfg_Fcst_Rec.forecast_designator,
557 p_new_refresh_num => l_new_refresh_num,
558 p_delete_flag => x_delete_flag );
559
560 /* DWK Is posst process required */
561 b_post_process := MSD_TRANSLATE_FACT_DATA.Is_Post_Process_Required(errbuf,
562 retcode, Mfg_Fcst_Rec.instance, Mfg_Fcst_Rec.forecast_designator);
563
564 IF ( b_post_process) THEN
565 IF (Mfg_Fcst_Rec.instance <> l_temp_instance) THEN
566 l_temp_instance := Mfg_Fcst_Rec.instance;
567 MSD_TRANSLATE_FACT_DATA.populate_calendar( errbuf,
568 retcode,
569 Mfg_Fcst_Rec.instance,
570 l_new_refresh_num,
571 MSD_COMMON_UTILITIES.MFG_FCST_STAGING_TABLE);
572 END IF;
573
574 /* Proceed post-process */
575 MSD_TRANSLATE_FACT_DATA.mfg_post_process( errbuf,
576 retcode,
577 Mfg_Fcst_Rec.instance,
578 Mfg_Fcst_Rec.forecast_designator,
579 l_new_refresh_num);
580 IF (retcode = -1) THEN
581 RAISE e_post_process_err;
582 END IF;
583 END IF; /* End of b_post_process */
584
585
586 Clean_Staging_Table(
587 errbuf => errbuf,
588 retcode => retcode,
589 p_table_name => x_source_table,
590 p_date_column => null,
591 p_instance_id => Mfg_Fcst_Rec.instance,
592 p_from_date => null,
593 p_to_date => null,
594 p_fcst_desg => Mfg_Fcst_Rec.forecast_designator );
595
596 /*
597 DWK If any error exist for the given batch process,
598 then save error status in b_has_error and then proceed next batch
599 */
600 EXCEPTION
601 WHEN no_data_found THEN
602 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
603 errbuf := substr(SQLERRM,1,150);
604 b_has_error := TRUE;
605 raise;
606 WHEN e_post_process_err THEN
607 fnd_file.put_line(fnd_file.log, 'Errors in mfg_post_process : Designator '||
608 Mfg_Fcst_Rec.forecast_designator);
609 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
610 errbuf := 'Error occured in mfg_post_process';
611 b_has_error := TRUE;
612 l_temp_instance := ' ';
613 raise;
614 WHEN others THEN
615 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
616 errbuf := substr(SQLERRM,1,150);
617 b_has_error := TRUE;
618 raise;
619 END;
620 END LOOP;
621
622
623
624 /* Delete fact rows that are not used by any demand plans */
625 MSD_TRANSLATE_FACT_DATA.clean_fact_data( errbuf,
626 retcode,
627 x_dest_table);
628
629 /* DWK Delete existing calendar after post process */
630 DELETE msd_st_time WHERE instance = '-999';
631
632 /* DWK Put recode code back to error status when error exists within loop */
633 IF (b_has_error) THEN
634 retcode := -1;
635 END IF;
636
637
638 COMMIT;
639
640 /* Added by esubrama */
641 MSD_ANALYZE_TABLES.analyze_table('MSD_MFG_FORECAST',null);
642 MSD_ANALYZE_TABLES.analyze_table('MSD_ST_MFG_FORECAST',null);
643
644 EXCEPTION
645 when others then
646 errbuf := substr(SQLERRM,1,150);
647 retcode := -1 ;
648 rollback;
649
650 End pull_mfg_forecast ;
651
652
653 procedure pull_pricing_data(
654 errbuf OUT NOCOPY VARCHAR2,
655 retcode OUT NOCOPY VARCHAR2) IS
656 x_source_table VARCHAR2(50) := MSD_COMMON_UTILITIES.PRICING_STAGING_TABLE ;
657 x_dest_table VARCHAR2(50) := MSD_COMMON_UTILITIES.PRICING_FACT_TABLE ;
658
659 /******************************************************
660 Cursor to get distinct Instance
661 ******************************************************/
662 /* DWK. Fix Bug 2220983. Do not include instance = '0' in the cursor */
663 Cursor Pricing is
664 select distinct instance, price_list_name
665 from msd_st_price_list
666 where nvl(instance, '888') <> '0' and price_list_name is not null;
667
668
669 l_new_refresh_num NUMBER;
670 Begin
671
672
673 retcode :=0;
674
675 SELECT msd.msd_last_refresh_number_s.nextval into l_new_refresh_Num from dual;
676
677 For Pricing_Rec IN Pricing LOOP
678
679 MSD_TRANSLATE_FACT_DATA.translate_pricing_data(
680 errbuf => errbuf,
681 retcode => retcode,
682 p_source_table => x_source_table,
683 p_dest_table => x_dest_table,
684 p_instance_id => Pricing_Rec.instance,
685 p_price_list => Pricing_Rec.price_list_name,
686 p_new_refresh_num => l_new_refresh_num) ;
687
688 Clean_Pricing_Staging_Table(
689 errbuf => errbuf,
690 retcode => retcode,
691 p_table_name => x_source_table,
692 p_instance_id => Pricing_Rec.instance,
693 p_price_list => Pricing_Rec.price_list_name);
694
695 End Loop ;
696
697
698 /* Delete fact rows that are not used by any demand plans */
699 MSD_TRANSLATE_FACT_DATA.clean_fact_data( errbuf,
700 retcode,
701 x_dest_table);
702
703
704 commit;
705
706 /* Added by esubrama */
707 MSD_ANALYZE_TABLES.analyze_table('MSD_PRICE_LIST',null);
708 MSD_ANALYZE_TABLES.analyze_table('MSD_ST_PRICE_LIST',null);
709
710 exception
711 when others then
712
713 errbuf := substr(SQLERRM,1,150);
714 retcode := -1 ;
715 rollback;
716
717 End pull_pricing_data ;
718
719
720 procedure pull_events(
721 errbuf OUT NOCOPY VARCHAR2,
722 retcode OUT NOCOPY VARCHAR2) IS
723
724 BEGIN
725 null;
726 End pull_events;
727
728
729
730
731 /**************** Private Procedures ***************/
732
733 procedure Clean_Staging_Table(
734 errbuf OUT NOCOPY VARCHAR2,
735 retcode OUT NOCOPY VARCHAR2,
736 p_table_name IN VARCHAR2,
737 p_date_column IN VARCHAR2,
738 p_instance_id IN NUMBER,
739 p_from_date IN VARCHAR2,
740 p_to_date IN VARCHAR2,
741 p_fcst_desg IN VARCHAR2 ) IS
742 x_sql_statement VARCHAR2(2000);
743
744 Begin
745 /* DWK. Do not delete any rows with instance = '0'
746 Attention. Currency staging won't have any instance id */
747 x_sql_statement := 'DELETE FROM ' || p_table_name ||
748 ' where nvl(instance,''-999'') <> ''0'' ' ||
749 ' and nvl(instance,''-999'') = ' ||
750 ' nvl(:p_instance_id, nvl(instance,''-999'')) ';
751
752 IF p_date_column is not NULL THEN
753 x_sql_statement := x_sql_statement ||' and ' || p_date_column ||
754 ' between to_date(:p_from_date, ''DD-MON-RRRR'') AND ' ||
755 ' to_date(:p_to_date, ''DD-MON-RRRR'') ';
756
757 IF p_fcst_desg is not null THEN
758 x_sql_statement := x_sql_statement ||
759 ' and forecast_designator = :fcst_desg ';
760 EXECUTE IMMEDIATE x_sql_statement
761 USING p_instance_id, nvl(p_from_date, to_char(C_FROM_DATE, 'DD-MON-RRRR')),
762 nvl(p_to_date,to_char(C_TO_DATE, 'DD-MON-RRRR')), p_fcst_desg;
763 ELSE
764 EXECUTE IMMEDIATE x_sql_statement
765 USING p_instance_id, nvl(p_from_date,to_char(C_FROM_DATE, 'DD-MON-RRRR')),
766 nvl(p_to_date,to_char(C_TO_DATE, 'DD-MON-RRRR'));
767 END IF;
768
769 ELSE /* If date column is null */
770
771 IF p_fcst_desg is not null THEN
772 x_sql_statement := x_sql_statement ||
773 ' and forecast_designator = :p_fcst_desg ';
774 EXECUTE IMMEDIATE x_sql_statement USING p_instance_id, p_fcst_desg;
775 ELSE
776 EXECUTE IMMEDIATE x_sql_statement USING p_instance_id;
777 END IF;
778 END IF;
779
780 -- insert into msd_test values(x_sql_statement) ;
781 -- dbms_output.put_line(v_sql_stmt);
782
783
784 exception
785 when others then
786
787 errbuf := substr(SQLERRM,1,150);
788 retcode := -1 ;
789 raise;
790
791 End Clean_Staging_Table ;
792
793
794 procedure Clean_Pricing_Staging_Table(
795 errbuf OUT NOCOPY VARCHAR2,
796 retcode OUT NOCOPY VARCHAR2,
797 p_table_name IN VARCHAR2,
798 p_instance_id IN NUMBER,
799 p_price_list IN VARCHAR2) IS
800 x_sql_statement VARCHAR2(2000);
801 Begin
802
803 /* Using nvl in price list will degrade the performance, no index
804 However, dynamic sql complaince need to use nvl in this case */
805
806 x_sql_statement := ' DELETE FROM ' || p_table_name ||
807 ' where instance = nvl(:p_instance_id, instance) '||
808 ' and price_list_name = nvl(:p_price_list, price_list_name)';
809 EXECUTE IMMEDIATE x_sql_statement USING p_instance_id, p_price_list;
810
811 exception
812 when others then
813 errbuf := substr(SQLERRM,1,150);
814 retcode := -1 ;
815 raise;
816
817 End Clean_Pricing_Staging_Table ;
818
819
820 END MSD_PULL_FACT_DATA;