[Home] [Help]
PACKAGE BODY: APPS.MSD_COLLECT_FACT_DATA
Source
1 PACKAGE BODY MSD_COLLECT_FACT_DATA AS
2 /* $Header: msdcfctb.pls 120.6 2011/02/07 07:11:13 rissingh ship $ */
3
4 /* Bug# 4747555 */
5 C_ALL CONSTANT NUMBER := 1;
6 C_INCLUDE CONSTANT NUMBER := 2;
7 C_EXCLUDE CONSTANT NUMBER := 3;
8
9 /* Bug# 4747555 */
10 TYPE ORDER_TYPE_TABLE_TYPE IS TABLE OF VARCHAR2(100);
11 TYPE ORDER_TYPE_ID_TABLE_TYPE IS TABLE OF NUMBER;
12
13
14 /* Bug# 4747555
15 * This function validates the order types given
16 * by the user to the following procedures:
17 * 1) collect_shipment_data
18 * 2) collect_booking_data
19 * This function returns the number of invalid
20 * order types found in the user input.
21 */
22 FUNCTION validate_input_parameters (
23 p_dblink IN VARCHAR2,
24 p_collect_all_order_types IN NUMBER,
25 p_include_order_types IN VARCHAR2,
26 p_exclude_order_types IN VARCHAR2,
27 p_order_type_flag OUT NOCOPY NUMBER,
28 p_order_type_ids OUT NOCOPY VARCHAR2,
29 p_retcode OUT NOCOPY VARCHAR2)
30 RETURN NUMBER;
31
32 /* This is the wrapper routine for collecting all the fact information
33 */
34 procedure collect_fact_data(
35 errbuf OUT NOCOPY VARCHAR2,
36 retcode OUT NOCOPY VARCHAR2,
37 p_instance_id IN NUMBER,
38 p_from_date IN VARCHAR2,
39 p_to_date IN VARCHAR2,
40 p_fcst_desg IN VARCHAR2,
41 p_price_list IN VARCHAR2 ) IS
42
43 Begin
44
45
46 retcode := 0 ;
47
48 /*----------------------------------------------------------*/
49 collect_shipment_data(
50 errbuf => errbuf,
51 retcode => retcode,
52 p_instance_id => p_instance_id,
53 p_from_date => p_from_date,
54 p_to_date => p_to_date,
55 p_collect_ISO => SYS_NO ) ; /* Bug# 4615390 ISO */
56
57 if retcode <> 0 then
58 errbuf := ' Error In Shipment Data Collection';
59 return;
60 end if ;
61 /*----------------------------------------------------------*/
62 collect_booking_data(
63 errbuf => errbuf,
64 retcode => retcode,
65 p_instance_id => p_instance_id,
66 p_from_date => p_from_date,
67 p_to_date => p_to_date,
68 p_collect_ISO => SYS_NO ) ; /* Bug# 4615390 ISO */
69
70 if retcode <> 0 then
71 errbuf := ' Error In Booking Data Collection';
72 return;
73 end if ;
74
75 /*----------------------------------------------------------*/
76 collect_uom_conversion(
77 errbuf => errbuf,
78 retcode => retcode,
79 p_instance_id => p_instance_id) ;
80
81 if retcode <> 0 then
82 errbuf := ' Error In UOM Conversions Data Collection';
83 return;
84 end if ;
85
86 /*----------------------------------------------------------*/
87 collect_currency_conversion(
88 errbuf => errbuf,
89 retcode => retcode,
90 p_instance_id => p_instance_id,
91 p_from_date => p_from_date,
92 p_to_date => p_to_date ) ;
93
94 if retcode <> 0 then
95 errbuf := ' Error In Currency Conversions Data Collection';
96 return;
97 end if ;
98 /*----------------------------------------------------------*/
99
100
101 /*collect_opportunities_data(
102 errbuf => errbuf,
103 retcode => retcode,
104 p_instance_id => p_instance_id,
105 p_from_date => p_from_date,
106 p_to_date => p_to_date ) ;
107
108 collect_sales_forecast(
109 errbuf => errbuf,
110 retcode => retcode,
111 p_instance_id => p_instance_id,
112 p_from_date => p_from_date,
113 p_to_date => p_to_date ) ;*/
114
115 /*----------------------------------------------------------*/
116 collect_mfg_forecast(
117 errbuf => errbuf,
118 retcode => retcode,
119 p_instance_id => p_instance_id,
120 p_fcst_desg => p_fcst_desg ) ;
121
122 if retcode <> 0 then
123 errbuf := ' Error In MFG Forecast Data Collection';
124 return;
125 end if ;
126
127 /*----------------------------------------------------------*/
128 collect_pricing_data(
129 errbuf => errbuf,
130 retcode => retcode,
131 p_instance_id => p_instance_id,
132 p_price_list => p_price_list) ;
133
134 if retcode <> 0 then
135 errbuf := ' Error In Pricing Data Collection';
136 return;
137 end if ;
138
139
140 exception
141
142 when others then
143
144 errbuf := substr(SQLERRM,1,150);
145 fnd_file.put_line(fnd_file.log, 'Errors in collect all fact');
146 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
147 retcode := -1 ;
148
149
150 End collect_fact_data ;
151
152
153
154 procedure collect_shipment_data(
155 errbuf OUT NOCOPY VARCHAR2,
156 retcode OUT NOCOPY VARCHAR2,
157 p_instance_id IN NUMBER,
158 p_from_date IN VARCHAR2,
159 p_to_date IN VARCHAR2,
160 p_collect_ISO IN NUMBER DEFAULT SYS_NO, /* Bug# 4615390 ISO, Bug# 4865396 */
161 p_collect_all_order_types IN NUMBER DEFAULT SYS_YES, /* Bug# 4747555*/
162 p_include_order_types IN VARCHAR2 DEFAULT NULL,
163 p_exclude_order_types IN VARCHAR2 DEFAULT NULL) IS
164
165 x_instance_id varchar2(40);
166 x_dblink varchar2(128);
167 x_retcode number;
168 x_direct_load_profile boolean;
169 x_source_table VARCHAR2(50) ;
170 x_dest_table varchar2(50) ;
171 x_sql_stmt varchar2(4000);
172 x_from_date DATE;
173 x_to_date DATE;
174
175 /* OPM Comment By Rajesh Patangya */
176 x_delete_flag varchar2(1) := 'Y' ;
177 o_source_table VARCHAR2(50) ;
178 o_dblink varchar2(128);
179 o_icode varchar2(128);
180 o_retcode number;
181 o_instance_type number;
182 o_dgmt number;
183 o_apps_ver number;
184
185 l_new_refresh_num NUMBER;
186
187 /* Bug# 4747555 */
188 l_order_type_ids VARCHAR2(2000);
189 l_order_type_flag NUMBER;
190 l_invalid_count NUMBER := 0;
191
192 Begin
193
194
195 /**************************************************
196 - 1. Get the instance id from MSC_APP_INSTANCE
197 - 2. Get the Profile Value for MSD_ONE_STEP_COLLECTION
198 - to identify whether we need to insert the
199 - data into the staging tables or the
200 - fact tables.
201 - 3. Check for the Data Duplication, we should
202 - use the shipped_date for this fact data.
203 - 4. Insert the Data accordingly into the
204 - Staging or the Fact table based on the
205 - MSD_SR_SHIPMENT_DATA_V.
206 - 5. Commit
207 ****************************************************/
208
209 retcode :=0;
210
211 msd_common_utilities.get_db_link(p_instance_id, x_dblink, x_retcode);
212 if (x_retcode = -1) then
213 retcode :=-1;
214 errbuf := 'Error while getting db_link';
215 return;
216 end if;
217
218 /* Check and push setup parameters if it is not done so previously */
219 MSD_PUSH_SETUP_DATA.chk_push_setup( errbuf,
220 retcode,
221 p_instance_id);
222 IF (nvl(retcode, 0) <> 0) THEN
223 return;
224 END IF;
225
226 /* Bug# 4747555
227 * Validate the input parameters
228 * given by the user
229 */
230 l_invalid_count := validate_input_parameters (
231 x_dblink,
232 p_collect_all_order_types,
233 p_include_order_types,
234 p_exclude_order_types,
235 l_order_type_flag,
236 l_order_type_ids,
237 x_retcode);
238
239 if (x_retcode = -1) then
240 retcode :=-1;
241 return;
242 end if;
243
244 /* OPM Comment By Rajesh Patangya */
245 msd_common_utilities.get_inst_info(p_instance_id, o_dblink, o_icode,
246 o_apps_ver, o_dgmt, o_instance_type, o_retcode) ;
247 if (o_retcode = -1) then
248 retcode :=-1;
249 errbuf := 'Error while getting instance_info';
250 return;
251 end if;
252
253
254 x_source_table := MSD_COMMON_UTILITIES.SHIPMENT_SOURCE_TABLE || x_dblink ;
255
256 /* OPM Comment By Rajesh Patangya */
257 o_source_table := MSD_COMMON_UTILITIES.OPM_SHIPMENT_SOURCE_TABLE || x_dblink ;
258
259 x_dest_table := MSD_COMMON_UTILITIES.SHIPMENT_STAGING_TABLE ;
260
261 x_from_date := FND_DATE.canonical_to_date(p_from_date);
262 x_to_date := FND_DATE.canonical_to_date(p_to_date);
263
264
265 /* OPM Comment By Rajesh Patangya */
266 /* Bug# 4620927 */
267 -- if o_instance_type <> 2 then
268 /* 11i instance where instance type in not 'PROCESS' OR R12 Instance of any type */
269 if o_instance_type <> 2 OR o_apps_ver = 4 then
270 MSD_TRANSLATE_FACT_DATA.translate_shipment_data(
271 errbuf => errbuf,
272 retcode => retcode,
273 p_source_table => x_source_table,
274 p_dest_table => x_dest_table,
275 p_instance_id => p_instance_id,
276 p_from_date => x_from_date,
277 p_to_date => x_to_date,
278 p_new_refresh_num => l_new_refresh_num,
279 p_delete_flag => x_delete_flag,
280 p_collect_ISO => p_collect_ISO, /* Bug# 4615390 ISO */
281 p_order_type_flag => l_order_type_flag, /* Bug# 4747555*/
282 p_order_type_ids => l_order_type_ids);
283
284 if retcode <> 0 then
285 errbuf := ' In MSD Call for shipment';
286 return;
287 end if ;
288
289 end if ;
290
291 /* OPM Comment By Rajesh Patangya */
292 if (o_instance_type in (2,4) AND o_apps_ver = 3) then
293
294 if o_instance_type = 4 then
295 x_delete_flag := 'N' ;
296 end if ;
297
298 MSD_TRANSLATE_FACT_DATA.translate_shipment_data(
299 errbuf => errbuf,
300 retcode => retcode,
301 p_source_table => o_source_table,
302 p_dest_table => x_dest_table,
303 p_instance_id => p_instance_id,
304 p_from_date => x_from_date,
305 p_to_date => x_to_date,
306 p_new_refresh_num => l_new_refresh_num,
307 p_delete_flag => x_delete_flag,
308 p_collect_ISO => p_collect_ISO); /* Bug# 4615390 ISO */
309
310 if retcode <> 0 then
311 errbuf := ' In OPM Call for shipment';
312 return;
313 end if ;
314 end if ;
315
316
317
318 x_direct_load_profile := (fnd_profile.value('MSD_ONE_STEP_COLLECTION')='Y');
319
320 IF (x_direct_load_profile) THEN
321 MSD_PULL_FACT_DATA.pull_shipment_data( errbuf,
322 retcode);
323
324 /* DWK. Check return code from mfg_post_process */
325 IF nvl(retcode, 0) <> 0 THEN
326 fnd_file.put_line(fnd_file.log, 'Errors in pull_shipment_data');
327 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
328 return;
329 END IF;
330 END IF;
331
332 commit;
333
334 /* Added by esubrama */
335 MSD_ANALYZE_TABLES.analyze_table(x_dest_table,null);
336
337 /* Bug# 4747555- Give warning if invalid order types were found */
338 IF l_invalid_count > 0 AND retcode = 0 THEN
339 retcode := 1;
340 END IF;
341
342 exception
343
344 when others then
345
346 errbuf := substr(SQLERRM,1,150);
347 fnd_file.put_line(fnd_file.log, 'Errors in collect_shipment_data');
348 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
349 retcode := -1 ;
350 rollback;
351
352 End collect_shipment_data ;
353
354 procedure collect_booking_data(
355 errbuf OUT NOCOPY VARCHAR2,
356 retcode OUT NOCOPY VARCHAR2,
357 p_instance_id IN NUMBER,
358 p_from_date IN VARCHAR2,
359 p_to_date IN VARCHAR2,
360 p_collect_ISO IN NUMBER DEFAULT SYS_NO, /* Bug# 4615390 ISO, Bug# 4865396 */
361 p_collect_all_order_types IN NUMBER DEFAULT SYS_YES, /* Bug# 4747555*/
362 p_include_order_types IN VARCHAR2 DEFAULT NULL,
363 p_exclude_order_types IN VARCHAR2 DEFAULT NULL) IS
364
365 x_instance_id varchar2(40);
366 x_dblink varchar2(128);
367 x_retcode number;
368 x_direct_load_profile boolean;
369 x_source_table VARCHAR2(50) ;
370 x_dest_table varchar2(50) ;
371 x_from_date DATE;
372 x_to_date DATE;
373
374 /* OPM Comment By Rajesh Patangya */
375 x_delete_flag varchar2(1) := 'Y' ;
376 o_source_table VARCHAR2(50) ;
377 o_dblink varchar2(128);
378 o_icode varchar2(128);
379 o_retcode number;
380 o_instance_type number;
381 o_dgmt number;
382 o_apps_ver number;
383
384 l_new_refresh_num NUMBER;
385
386 /* Bug# 4747555 */
387 l_order_type_ids VARCHAR2(2000);
388 l_order_type_flag NUMBER;
389 l_invalid_count NUMBER := 0;
390
391 Begin
392
393
394 /**************************************************
395 - 1. Get the instance id from MSC_APP_INSTANCE
396 - 2. Get the Profile Value for MSD_ONE_STEP_COLLECTION
397 - to identify whether we need to insert the
398 - data into the staging tables or the
399 - fact tables.
400 - 3. Check for the Data Duplication, we should
401 - use the shipped_date for this fact data.
402 - 4. Insert the Data accordingly into the
403 - Staging or the Fact table based on the
404 - MSD_SR_BOOKING_DATA_V.
405 - 5. Commit
406 ****************************************************/
407
408 retcode :=0;
409
410 msd_common_utilities.get_db_link(p_instance_id, x_dblink, x_retcode);
411 if (x_retcode = -1) then
412 retcode :=-1;
413 errbuf := 'Error while getting db_link';
414 return;
415 end if;
416
417 /* Bug# 4747555
418 * Validate the input parameters
419 * given by the user
420 */
421 l_invalid_count := validate_input_parameters (
422 x_dblink,
423 p_collect_all_order_types,
424 p_include_order_types,
425 p_exclude_order_types,
426 l_order_type_flag,
427 l_order_type_ids,
428 x_retcode);
429
430 if (x_retcode = -1) then
431 retcode :=-1;
432 return;
433 end if;
434
435 /* Check and push setup parameters if it is not done so previously */
436 MSD_PUSH_SETUP_DATA.chk_push_setup( errbuf,
437 retcode,
438 p_instance_id);
439 IF (nvl(retcode, 0) <> 0) THEN
440 return;
441 END IF;
442
443 /* OPM Comment By Rajesh Patangya */
444 msd_common_utilities.get_inst_info(p_instance_id, o_dblink, o_icode,
445 o_apps_ver, o_dgmt, o_instance_type, o_retcode) ;
446 if (o_retcode = -1) then
447 retcode :=-1;
448 errbuf := 'Error while getting instance_info';
449 return;
450 end if;
451
452
453 x_source_table := MSD_COMMON_UTILITIES.BOOKING_SOURCE_TABLE || x_dblink ;
454 /* OPM Comment By Rajesh Patangya */
455 o_source_table := MSD_COMMON_UTILITIES.OPM_BOOKING_SOURCE_TABLE || x_dblink ;
456
457 x_dest_table := MSD_COMMON_UTILITIES.BOOKING_STAGING_TABLE ;
458
459 x_from_date := FND_DATE.canonical_to_date(p_from_date);
460 x_to_date := FND_DATE.canonical_to_date(p_to_date);
461
462
463 /* OPM Comment By Rajesh Patangya */
464 /* Bug# 4620927 */
465 -- if o_instance_type <> 2 then
466 /* 11i instance where instance type in not 'PROCESS' OR R12 Instance of any type */
467 if o_instance_type <> 2 OR o_apps_ver = 4 then
468 MSD_TRANSLATE_FACT_DATA.translate_booking_data(
469 errbuf => errbuf,
470 retcode => retcode,
471 p_source_table => x_source_table,
472 p_dest_table => x_dest_table,
473 p_instance_id => p_instance_id,
474 p_from_date => x_from_date,
475 p_to_date => x_to_date,
476 p_new_refresh_num => l_new_refresh_num,
477 p_delete_flag => x_delete_flag,
478 p_collect_ISO => p_collect_ISO, /* Bug# 4615390 ISO */
479 p_order_type_flag => l_order_type_flag, /* Bug# 4747555*/
480 p_order_type_ids => l_order_type_ids);
481
482 if nvl(retcode,0) <> 0 then
483 errbuf := ' In MSD Call for Booking';
484 return;
485 end if ;
486 end if ;
487
488
489 /* OPM Comment By Rajesh Patangya */
490 if (o_instance_type in (2,4) AND o_apps_ver = 3) then
491
492 if o_instance_type = 4 then
493 x_delete_flag := 'N' ;
494 end if ;
495
496 MSD_TRANSLATE_FACT_DATA.translate_booking_data(
497 errbuf => errbuf,
498 retcode => retcode,
499 p_source_table => o_source_table,
500 p_dest_table => x_dest_table,
501 p_instance_id => p_instance_id,
502 p_from_date => x_from_date,
503 p_to_date => x_to_date,
504 p_new_refresh_num => l_new_refresh_num,
505 p_delete_flag => x_delete_flag,
506 p_collect_ISO => p_collect_ISO) ; /* Bug# 4615390 ISO */
507
508 if nvl(retcode, 0) <> 0 then
509 errbuf := ' In OPM Call for Booking ';
510 return;
511 end if ;
512 end if ;
513
514 x_direct_load_profile := (fnd_profile.value('MSD_ONE_STEP_COLLECTION')='Y');
515
516 IF (x_direct_load_profile) THEN
517 MSD_PULL_FACT_DATA.pull_booking_data( errbuf,
518 retcode);
519
520 /* DWK. Check return code from mfg_post_process */
521 IF nvl(retcode, 0) <> 0 THEN
522 fnd_file.put_line(fnd_file.log, 'Errors in pull_booking_data');
523 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
524 return;
525 END IF;
526 END IF;
527
528 commit;
529
530 /* Added by esubrama */
531 MSD_ANALYZE_TABLES.analyze_table(x_dest_table,null);
532
533 /* Bug# 4747555- Give warning if invalid order types were found */
534 IF l_invalid_count > 0 AND retcode = 0 THEN
535 retcode := 1;
536 END IF;
537
538 exception
539
540 when others then
541
542 errbuf := substr(SQLERRM,1,150);
543 fnd_file.put_line(fnd_file.log, 'Errors in collect_booking_data');
544 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
545 retcode := -1 ;
546 rollback;
547
548
549 End collect_booking_data ;
550
551
552
553 procedure collect_uom_conversion(
554 errbuf OUT NOCOPY VARCHAR2,
555 retcode OUT NOCOPY VARCHAR2,
556 p_instance_id IN NUMBER) IS
557 x_instance_id varchar2(40);
558 x_dblink varchar2(128);
559 x_retcode number;
560 x_direct_load_profile boolean;
561 x_source_table VARCHAR2(50) ;
562 x_dest_table varchar2(50) ;
563
564 Begin
565
566
567 /**************************************************
568 - 1. Get the instance id from MSC_APP_INSTANCE
569 - 2. Get the Profile Value for MSD_ONE_STEP_COLLECTION
570 - to identify whether we need to insert the
571 - data into the staging tables or the
572 - fact tables.
573 - 3. Do a complete refresh for this instance,
574 - hence delete all the underlying values.
575 - 4. Insert the Data accordingly into the
576 - Staging or the Fact table based on the
577 - MSD_SR_UOM_CONVERSION_V
578 - 5. Commit
579 ****************************************************/
580
581 retcode :=0;
582
583 /* Check and push setup parameters if it is not done so previously */
584 MSD_PUSH_SETUP_DATA.chk_push_setup( errbuf,
585 retcode,
586 p_instance_id);
587 IF (nvl(retcode, 0) <> 0) THEN
588 return;
589 END IF;
590
591 msd_common_utilities.get_db_link(p_instance_id, x_dblink, x_retcode);
592 if (x_retcode = -1) then
593 retcode :=-1;
594 errbuf := 'Error while getting db_link';
595 return;
596 end if;
597
598 x_source_table := MSD_COMMON_UTILITIES.UOM_SOURCE_TABLE || x_dblink ;
599
600 x_direct_load_profile := (fnd_profile.value('MSD_ONE_STEP_COLLECTION')='Y');
601
602 x_dest_table := MSD_COMMON_UTILITIES.UOM_STAGING_TABLE ;
603
604
605 MSD_TRANSLATE_FACT_DATA.translate_uom_conversion(
606 errbuf => errbuf,
607 retcode => retcode,
608 p_source_table => x_source_table,
609 p_dest_table => x_dest_table,
610 p_instance_id => p_instance_id,
611 p_new_refresh_num => NULL) ;
612
613 if nvl(retcode, 0) <> 0 then
614 errbuf := ' In collect uom conversion/translate_uom_conversion';
615 return;
616 end if ;
617
618
619 x_direct_load_profile := (fnd_profile.value('MSD_ONE_STEP_COLLECTION')='Y');
620
621 IF (x_direct_load_profile) THEN
622 MSD_PULL_FACT_DATA.pull_uom_conversion( errbuf,
623 retcode);
624
625 /* DWK. Check return code from mfg_post_process */
626 IF nvl(retcode, 0) <> 0 THEN
627 fnd_file.put_line(fnd_file.log, 'Errors in pull_uom_conversion');
628 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
629 return;
630 END IF;
631 END IF;
632
633
634 commit;
635
636 /* Added by esubrama */
637 MSD_ANALYZE_TABLES.analyze_table(x_dest_table,null);
638
639 exception
640
641 when others then
642
643 errbuf := substr(SQLERRM,1,150);
644 fnd_file.put_line(fnd_file.log, 'Errors in collect_uom_conversion');
645 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
646 retcode := -1 ;
647 rollback;
648
649
650
651 End collect_uom_conversion ;
652
653 procedure collect_currency_conversion(
654 errbuf OUT NOCOPY VARCHAR2,
655 retcode OUT NOCOPY VARCHAR2,
656 p_instance_id IN NUMBER,
657 p_from_date IN VARCHAR2,
658 p_to_date IN VARCHAR2) IS
659 x_instance_id varchar2(40);
660 x_dblink varchar2(128);
661 x_retcode number;
662 x_direct_load_profile boolean;
663 x_source_table VARCHAR2(150) ;
664 x_dest_table varchar2(150) ;
665 x_from_date DATE;
666 x_to_date DATE;
667
668 Begin
669
670
671 /**************************************************
672 - 1. Get the instance id from MSC_APP_INSTANCE
673 - 2. Get the Profile Value for MSD_ONE_STEP_COLLECTION
674 - to identify whether we need to insert the
675 - data into the staging tables or the
676 - fact tables.
677 - 3. Do a complete refresh for this instance,
678 - hence delete all the underlying values.
679 - 4. Insert the Data accordingly into the
680 - Staging or the Fact table based on the
681 - MSD_SR_CURRENCY_CONVERSION_V
682 - 5. Commit
683 ****************************************************/
684
685 retcode :=0;
686
687
688 /* Check and push setup parameters if it is not done so previously */
689 MSD_PUSH_SETUP_DATA.chk_push_setup( errbuf,
690 retcode,
691 p_instance_id);
692 IF (nvl(retcode, 0) <> 0) THEN
693 return;
694 END IF;
695
696
697 msd_common_utilities.get_db_link(p_instance_id, x_dblink, x_retcode);
698 if (x_retcode = -1) then
699 retcode :=-1;
700 errbuf := 'Error while getting db_link';
701 return;
702 end if;
703
704 x_source_table := MSD_COMMON_UTILITIES.CURRENCY_SOURCE_TABLE || x_dblink ;
705
706 x_dest_table := MSD_COMMON_UTILITIES.CURRENCY_STAGING_TABLE ;
707
708
709 x_from_date := FND_DATE.canonical_to_date(p_from_date);
710 x_to_date := FND_DATE.canonical_to_date(p_to_date);
711
712
713 MSD_TRANSLATE_FACT_DATA.translate_currency_conversion(
714 errbuf => errbuf,
715 retcode => retcode,
716 p_source_table => x_source_table,
717 p_dest_table => x_dest_table,
718 p_instance_id => p_instance_id,
719 p_from_date => x_from_date,
720 p_to_date => x_to_date) ;
721
722 if nvl(retcode, 0) <> 0 then
723 errbuf := ' In collect currency conversion/translate_currency_conversion';
724 return;
725 end if ;
726
727
728 x_direct_load_profile := (fnd_profile.value('MSD_ONE_STEP_COLLECTION')='Y');
729
730 IF (x_direct_load_profile) THEN
731 MSD_PULL_FACT_DATA.pull_currency_conversion( errbuf,
732 retcode);
733
734 /* DWK. Check return code from mfg_post_process */
735 IF nvl(retcode, 0) <> 0 THEN
736 fnd_file.put_line(fnd_file.log, 'Errors in pull_currency_conversion');
737 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
738 return;
739 END IF;
740 END IF;
741
742
743 commit;
744
745 /* Added by esubrama */
746 MSD_ANALYZE_TABLES.analyze_table(x_dest_table,null);
747
748 exception
749
750 when others then
751
752
753 errbuf := substr(SQLERRM,1,150);
754 fnd_file.put_line(fnd_file.log, 'Errors in collect_currency_conversion');
755 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
756 retcode := -1 ;
757 rollback;
758
759 End collect_currency_conversion ;
760
761
762 procedure collect_mfg_forecast(
763 errbuf OUT NOCOPY VARCHAR2,
764 retcode OUT NOCOPY VARCHAR2,
765 p_instance_id IN NUMBER,
766 p_fcst_desg IN VARCHAR2) IS
767 x_instance_id varchar2(40);
768 x_dblink varchar2(128);
769 x_retcode number;
770 x_direct_load_profile boolean;
771 x_source_table VARCHAR2(50) ;
772 x_dest_table varchar2(50) ;
773
774 /* OPM Comment By Rajesh Patangya */
775 x_delete_flag varchar2(1) := 'Y' ;
776 o_source_table VARCHAR2(50) ;
777 o_dblink varchar2(128);
778 o_icode varchar2(128);
779 o_retcode number;
780 o_instance_type number;
781 o_dgmt number;
782 o_apps_ver number;
783
784 /* DWK For post process */
785 b_post_process BOOLEAN := TRUE;
786
787 l_new_refresh_num NUMBER;
788
789 Begin
790
791
792 /**************************************************
793 - 1. Get the instance id from MSC_APP_INSTANCE
794 - 2. Get the Profile Value for MSD_ONE_STEP_COLLECTION
795 - to identify whether we need to insert the
796 - data into the staging tables or the
797 - fact tables.
798 - 3. Check for the Data Duplication, we should
799 - use the forecast_designator for this fact.
800 - 4. Insert the Data accordingly into the
801 - Staging or the Fact table based on the
802 - MSD_SR_MFG_FCST_V
803 - 5. Commit
804 ****************************************************/
805
806 retcode :=0;
807
808
809 /* Check and push setup parameters if it is not done so previously */
810 MSD_PUSH_SETUP_DATA.chk_push_setup( errbuf,
811 retcode,
812 p_instance_id);
813 IF (nvl(retcode, 0) <> 0) THEN
814 return;
815 END IF;
816
817
818
819
820 msd_common_utilities.get_db_link(p_instance_id, x_dblink, x_retcode);
821 if (x_retcode = -1) then
822 retcode :=-1;
823 errbuf := 'Error while getting db_link';
824 return;
825 end if;
826
827 /* OPM Comment By Rajesh Patangya */
828 msd_common_utilities.get_inst_info(p_instance_id, o_dblink, o_icode,
829 o_apps_ver, o_dgmt, o_instance_type, o_retcode) ;
830 if (o_retcode = -1) then
831 retcode :=-1;
832 errbuf := 'Error while getting instance_info';
833 return;
834 end if;
835
836 x_source_table := MSD_COMMON_UTILITIES.MFG_FCST_SOURCE_TABLE || x_dblink ;
837 /* OPM Comment By Rajesh Patangya */
838 o_source_table := MSD_COMMON_UTILITIES.OPM_MFG_FCST_SOURCE_TABLE || x_dblink ;
839
840 x_dest_table := MSD_COMMON_UTILITIES.MFG_FCST_STAGING_TABLE ;
841
842 x_direct_load_profile := (fnd_profile.value('MSD_ONE_STEP_COLLECTION')='Y');
843
844 /* OPM Comment By Rajesh Patangya */
845 /* Bug# 4620927 */
846 -- IF o_instance_type <> 2 then
847 /* 11i instance where instance type in not 'PROCESS' OR R12 Instance of any type */
848 IF o_instance_type <> 2 OR o_apps_ver = 4 then
849 MSD_TRANSLATE_FACT_DATA.translate_mfg_forecast(
850 errbuf => errbuf,
851 retcode => retcode,
852 p_source_table => x_source_table,
853 p_dest_table => x_dest_table,
854 p_instance_id => p_instance_id,
855 p_fcst_desg => p_fcst_desg,
856 p_new_refresh_num => l_new_refresh_num,
857 p_delete_flag => x_delete_flag );
858
859 if nvl(retcode, 0) <> 0 then
860 errbuf := ' In MSD Call for shipment';
861 return;
862 end if ;
863 end if ;
864
865 /* OPM Comment By Rajesh Patangya */
866 IF (o_instance_type in (2,4) and o_apps_ver = 3) THEN
867
868 IF o_instance_type = 4 THEN
869 x_delete_flag := 'N' ;
870 END IF;
871
872 MSD_TRANSLATE_FACT_DATA.translate_mfg_forecast(
873 errbuf => errbuf,
874 retcode => retcode,
875 p_source_table => o_source_table,
876 p_dest_table => x_dest_table,
877 p_instance_id => p_instance_id,
878 p_fcst_desg => p_fcst_desg,
879 p_new_refresh_num => l_new_refresh_num,
880 p_delete_flag => x_delete_flag );
881
882 IF nvl(retcode, 0) <> 0 then
883 errbuf := ' In OPM Call for Manufacturing forecast ';
884 return;
885 END IF;
886 END IF;
887
888
889 x_direct_load_profile := (fnd_profile.value('MSD_ONE_STEP_COLLECTION')='Y');
890
891 /* If 1 step colloction then proceed with PULL */
892 IF (x_direct_load_profile) THEN
893 MSD_PULL_FACT_DATA.pull_mfg_forecast( errbuf,
894 retcode);
895 /* DWK. Check return code from mfg_post_process */
896 IF (retcode <> 0) THEN
897 fnd_file.put_line(fnd_file.log, 'Errors in pull_mfg_forecast');
898 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
899 return;
900 END IF;
901 END IF;
902
903 COMMIT;
904
905 /* Added by esubrama */
906 MSD_ANALYZE_TABLES.analyze_table(x_dest_table,null);
907
908 EXCEPTION
909 WHEN no_data_found THEN
910 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
911 errbuf := substr(SQLERRM,1,150);
912 retcode := -1;
913 rollback;
914 when others then
915 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
916 errbuf := substr(SQLERRM,1,150);
917 retcode := -1 ;
918 rollback;
919
920 End collect_mfg_forecast ;
921
922
923 procedure collect_pricing_data(
924 errbuf OUT NOCOPY VARCHAR2,
925 retcode OUT NOCOPY VARCHAR2,
926 p_instance_id IN NUMBER,
927 p_price_list IN VARCHAR2) IS
928
929 x_instance_id varchar2(40);
930 x_dblink varchar2(128);
931 x_retcode number;
932 x_direct_load_profile boolean;
933 x_source_table VARCHAR2(50) ;
934 x_dest_table varchar2(50) ;
935 x_sql_stmt varchar2(4000);
936 Begin
937
938
939 /**************************************************
940 - 1. Get the instance id from MSC_APP_INSTANCE
941 - 2. Get the Profile Value for MSD_ONE_STEP_COLLECTION
942 - to identify whether we need to insert the
943 - data into the staging tables or the
944 - fact tables.
945 - 3. Check for the Data Duplication, we should
946 - use the shipped_date for this fact data.
947 - 4. Insert the Data accordingly into the
948 - Staging or the Fact table based on the
949 - MSD_SR_PRICE_LIST_V.
950 - 5. Commit
951 ****************************************************/
952
953 retcode :=0;
954
955 /* Check and push setup parameters if it is not done so previously */
956 MSD_PUSH_SETUP_DATA.chk_push_setup( errbuf,
957 retcode,
958 p_instance_id);
959 IF (nvl(retcode, 0) <> 0) THEN
960 return;
961 END IF;
962
963
964 msd_common_utilities.get_db_link(p_instance_id, x_dblink, x_retcode);
965 if (x_retcode = -1) then
966 retcode :=-1;
967 errbuf := 'Error while getting db_link';
968 return;
969 end if;
970
971 x_source_table := MSD_COMMON_UTILITIES.PRICING_SOURCE_TABLE || x_dblink ;
972
973 x_dest_table := MSD_COMMON_UTILITIES.PRICING_STAGING_TABLE ;
974
975
976 MSD_TRANSLATE_FACT_DATA.translate_pricing_data(
977 errbuf => errbuf,
978 retcode => retcode,
979 p_source_table => x_source_table,
980 p_dest_table => x_dest_table,
981 p_instance_id => p_instance_id,
982 p_price_list => p_price_list,
983 p_new_refresh_num => NULL) ;
984
985 /* Bug# 5878412 */
986 commit;
987 MSD_ANALYZE_TABLES.analyze_table(x_dest_table,null);
988
989
990 /* Price List post process to eliminate dublicate price list for the same item, same
991 time period */
992
993 msd_price_list_pp.price_list_post_process( errbuf => errbuf,
994 retcode => retcode,
995 p_instance_id => p_instance_id,
996 p_price_list => p_price_list );
997
998
999
1000 x_direct_load_profile := (fnd_profile.value('MSD_ONE_STEP_COLLECTION')='Y');
1001
1002 /* If 1 step colloction then proceed with PULL */
1003 IF (x_direct_load_profile) THEN
1004 MSD_PULL_FACT_DATA.pull_pricing_data( errbuf,
1005 retcode);
1006 /* DWK. Check return code from mfg_post_process */
1007 IF (retcode <> 0) THEN
1008 fnd_file.put_line(fnd_file.log, 'Errors in pull_pricing_data');
1009 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1010 return;
1011 END IF;
1012 END IF;
1013
1014 IF nvl(retcode, 0) <> 0 then
1015 errbuf := ' In Collect Pricing Data/translate_pricing_data';
1016 return;
1017 END IF;
1018
1019 commit;
1020
1021 /* Added by esubrama */
1022 MSD_ANALYZE_TABLES.analyze_table(x_dest_table,null);
1023
1024 exception
1025
1026 when others then
1027
1028 errbuf := substr(SQLERRM,1,150);
1029 fnd_file.put_line(fnd_file.log, 'Errors in collect pricing data');
1030 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1031 retcode := -1 ;
1032 rollback;
1033
1034 End collect_pricing_data ;
1035
1036
1037 procedure purge_facts(
1038 errbuf OUT NOCOPY VARCHAR2,
1039 retcode OUT NOCOPY VARCHAR2,
1040 p_instance_id IN NUMBER) IS
1041
1042
1043 begin
1044 retcode := 0;
1045
1046 EXECUTE IMMEDIATE get_purge_sql(MSD_COMMON_UTILITIES.SHIPMENT_FACT_TABLE, p_instance_id);
1047 EXECUTE IMMEDIATE get_purge_sql(MSD_COMMON_UTILITIES.BOOKING_FACT_TABLE, p_instance_id);
1048 EXECUTE IMMEDIATE get_purge_sql(MSD_COMMON_UTILITIES.SALES_FCST_FACT_TABLE, p_instance_id);
1049 EXECUTE IMMEDIATE get_purge_sql(MSD_COMMON_UTILITIES.MFG_FCST_FACT_TABLE, p_instance_id);
1050 EXECUTE IMMEDIATE get_purge_sql(MSD_COMMON_UTILITIES.OPPORTUNITY_FACT_TABLE, p_instance_id);
1051 /* EXECUTE IMMEDIATE get_purge_sql(MSD_COMMON_UTILITIES.CURRENCY_FACT_TABLE, p_instance_id);*/
1052 EXECUTE IMMEDIATE get_purge_sql(MSD_COMMON_UTILITIES.UOM_FACT_TABLE, p_instance_id);
1053 EXECUTE IMMEDIATE get_purge_sql(MSD_COMMON_UTILITIES.LEVEL_VALUES_FACT_TABLE, p_instance_id);
1054 EXECUTE IMMEDIATE get_purge_sql(MSD_COMMON_UTILITIES.LEVEL_ASSOC_FACT_TABLE, p_instance_id);
1055 EXECUTE IMMEDIATE get_purge_sql(MSD_COMMON_UTILITIES.ITEM_INFO_FACT_TABLE, p_instance_id);
1056 EXECUTE IMMEDIATE get_purge_sql(MSD_COMMON_UTILITIES.TIME_FACT_TABLE, p_instance_id);
1057 EXECUTE IMMEDIATE get_purge_sql(MSD_COMMON_UTILITIES.PRICING_FACT_TABLE, p_instance_id);
1058
1059 COMMIT;
1060
1061 exception
1062 when others then
1063 errbuf := substr(SQLERRM,1,150);
1064 retcode := -1 ;
1065 rollback;
1066
1067 end purge_facts;
1068
1069
1070 function get_purge_sql(p_table VARCHAR2, p_instance_id NUMBER) RETURN VARCHAR2 IS
1071 ret varchar2(100);
1072 begin
1073 ret := 'delete from ' || p_table;
1074 if p_instance_id is not null then
1075 ret := ret || ' where instance = ' || p_instance_id;
1076 end if;
1077
1078 return ret;
1079 end get_purge_sql;
1080
1081 /* Bug# 4747555
1082 * This function validates the order types given
1083 * by the user to the following procedures:
1084 * 1) collect_shipment_data
1085 * 2) collect_booking_data
1086 * This function returns the number of invalid
1087 * order types found in the user input.
1088 * Returns '-1' incase of ERROR.
1089 */
1090 FUNCTION validate_input_parameters (
1091 p_dblink IN VARCHAR2,
1092 p_collect_all_order_types IN NUMBER,
1093 p_include_order_types IN VARCHAR2,
1094 p_exclude_order_types IN VARCHAR2,
1095 p_order_type_flag OUT NOCOPY NUMBER,
1096 p_order_type_ids OUT NOCOPY VARCHAR2,
1097 p_retcode OUT NOCOPY VARCHAR2)
1098 RETURN NUMBER IS
1099
1100 l_order_type_table ORDER_TYPE_TABLE_TYPE;
1101 l_order_category_code_table ORDER_TYPE_TABLE_TYPE;
1102 l_order_type_id_table ORDER_TYPE_ID_TABLE_TYPE;
1103 l_valid_order_type_table ORDER_TYPE_TABLE_TYPE;
1104 l_invalid_order_type_table ORDER_TYPE_TABLE_TYPE;
1105
1106 l_sql_stmt VARCHAR2(2000);
1107 l_order_types VARCHAR2(2000);
1108 l_original_order_types VARCHAR2(2000);
1109 l_order_type_ids VARCHAR2(2000);
1110 l_token VARCHAR2(100);
1111 l_original_token VARCHAR2(100);
1112
1113 l_order_type_flag NUMBER;
1114 l_start NUMBER := 1;
1115 l_position NUMBER := -1;
1116 l_valid_count NUMBER := 0;
1117 l_invalid_count NUMBER := 0;
1118
1119 l_found BOOLEAN;
1120
1121 BEGIN
1122
1123 /* Get all the valid order types from the source*/
1124 l_sql_stmt := 'SELECT ' ||
1125 'B.TRANSACTION_TYPE_ID ORDER_TYPE_ID, ' ||
1126 'UPPER(B.ORDER_CATEGORY_CODE) ORDER_CATEGORY_CODE, ' ||
1127 'UPPER(T.NAME) NAME ' ||
1128 'FROM ' ||
1129 'OE_TRANSACTION_TYPES_TL' || p_dblink || ' T, ' ||
1130 'OE_TRANSACTION_TYPES_ALL' || p_dblink || ' B '||
1131 'WHERE ' ||
1132 'B.TRANSACTION_TYPE_ID = T.TRANSACTION_TYPE_ID AND ' ||
1133 'B.Transaction_type_code = ''ORDER'' AND ' ||
1134 'nvl(B.SALES_DOCUMENT_TYPE_CODE,''O'') <> ''B'' AND ' ||
1135 'T.LANGUAGE = userenv(''LANG'') ';
1136
1137 EXECUTE IMMEDIATE l_sql_stmt
1138 BULK COLLECT INTO l_order_type_id_table,
1139 l_order_category_code_table,
1140 l_order_type_table;
1141
1142 IF l_order_type_table.COUNT = 0 THEN
1143 p_retcode := -1;
1144 msd_conc_log_util.display_message('No order types found in the source', msd_conc_log_util.C_ERROR);
1145 return -1;
1146 END IF;
1147
1148 IF p_collect_all_order_types = SYS_NO THEN
1149
1150 IF p_include_order_types is null AND
1151 p_exclude_order_types is null THEN
1152 p_retcode := -1;
1153 msd_conc_log_util.display_message('Both the parameters include order types and exclude order types are null', msd_conc_log_util.C_ERROR);
1154 return -1;
1155 ELSIF p_include_order_types is not null AND
1156 p_exclude_order_types is not null THEN
1157 p_retcode := -1;
1158 msd_conc_log_util.display_message('Both the parameters include order types and exclude order types are not null', msd_conc_log_util.C_ERROR);
1159 return -1;
1160 ELSIF p_include_order_types is not null THEN
1161 l_order_type_flag := C_INCLUDE;
1162 l_order_types := UPPER(p_include_order_types);
1163 l_original_order_types := p_include_order_types;
1164 ELSE
1165 l_order_type_flag := C_EXCLUDE;
1166 l_order_types := UPPER(p_exclude_order_types);
1167 l_original_order_types := p_exclude_order_types;
1168 END IF;
1169
1170 l_valid_order_type_table := ORDER_TYPE_TABLE_TYPE();
1171 l_invalid_order_type_table := ORDER_TYPE_TABLE_TYPE();
1172
1173 /* Get the valid and invalid order types given by the user */
1174 LOOP
1175
1176 l_position := INSTR( l_order_types, ',', l_start, 1);
1177
1178 /* Get the token (order type)*/
1179 IF (l_position <> 0) THEN
1180 l_token := SUBSTR( l_order_types, l_start, l_position - l_start);
1181 l_original_token := SUBSTR( l_original_order_types, l_start, l_position - l_start);
1182 ELSE
1183 l_token := SUBSTR( l_order_types, l_start);
1184 l_original_token := SUBSTR( l_original_order_types, l_start);
1185 END IF;
1186
1187 /* Validate the order type*/
1188 l_found := FALSE;
1189 FOR i in l_order_type_table.FIRST..l_order_type_table.LAST
1190 LOOP
1191
1192 /* Valid order type */
1193 IF l_order_category_code_table(i) <> 'RETURN' AND l_token = l_order_type_table(i) THEN
1194
1195 l_found := TRUE;
1196 l_valid_count := l_valid_count + 1;
1197 l_valid_order_type_table.EXTEND;
1198 l_valid_order_type_table(l_valid_count) := l_original_token;
1199
1200 IF (l_valid_count = 1) THEN
1201 l_order_type_ids := l_order_type_ids || to_char(l_order_type_id_table(i));
1202 ELSE
1203 l_order_type_ids := l_order_type_ids || ',' || to_char(l_order_type_id_table(i));
1204 END IF;
1205
1206 EXIT;
1207
1208 /* Invalid order type since order category code is 'RETURN' */
1209 ELSIF l_order_category_code_table(i) = 'RETURN' AND l_token = l_order_type_table(i) THEN
1210
1211 l_found := TRUE;
1212 l_invalid_count := l_invalid_count + 1;
1213 l_invalid_order_type_table.EXTEND;
1214 l_invalid_order_type_table(l_invalid_count) := l_original_token || ' (Order Type is RETURN)';
1215
1216 EXIT;
1217
1218 END IF;
1219
1220 END LOOP;
1221
1222 /* Invalid order type */
1223 IF l_found = FALSE THEN
1224 l_invalid_count := l_invalid_count + 1;
1225 l_invalid_order_type_table.EXTEND;
1226 l_invalid_order_type_table(l_invalid_count) := l_original_token;
1227 END IF;
1228
1229 EXIT WHEN l_position = 0;
1230 l_start := l_position + 1;
1231 END LOOP;
1232
1233 msd_conc_log_util.display_message('Demand Plan Order Types', msd_conc_log_util.C_SECTION);
1234 msd_conc_log_util.display_message(' ', msd_conc_log_util.C_HEADING);
1235
1236 msd_conc_log_util.display_message('Valid Order Types', msd_conc_log_util.C_HEADING);
1237 msd_conc_log_util.display_message('------------------------------------------------', msd_conc_log_util.C_HEADING);
1238
1239 IF l_valid_count <> 0 THEN
1240 FOR i in l_valid_order_type_table.FIRST..l_valid_order_type_table.LAST
1241 LOOP
1242 msd_conc_log_util.display_message(to_char(i) || ') ' || l_valid_order_type_table(i), msd_conc_log_util.C_INFORMATION);
1243 END LOOP;
1244 ELSE
1245 p_retcode := -1;
1246 msd_conc_log_util.display_message('No valid order types found in user input', msd_conc_log_util.C_ERROR);
1247 END IF;
1248
1249 msd_conc_log_util.display_message(' ', msd_conc_log_util.C_HEADING);
1250 msd_conc_log_util.display_message('Invalid Order Types', msd_conc_log_util.C_HEADING);
1251 msd_conc_log_util.display_message('------------------------------------------------', msd_conc_log_util.C_HEADING);
1252
1253 IF l_invalid_count <> 0 THEN
1254 FOR i in l_invalid_order_type_table.FIRST..l_invalid_order_type_table.LAST
1255 LOOP
1256 msd_conc_log_util.display_message(to_char(i) || ') ' || l_invalid_order_type_table(i), msd_conc_log_util.C_WARNING);
1257 END LOOP;
1258 END IF;
1259 msd_conc_log_util.display_message(' ', msd_conc_log_util.C_HEADING);
1260
1261 IF l_valid_count = 0 THEN
1262 return -1;
1263 END IF;
1264
1265 ELSE /* Collect all order types */
1266
1267 IF p_include_order_types is not null OR
1268 p_exclude_order_types is not null THEN
1269
1270 p_retcode := -1;
1271 msd_conc_log_util.display_message('Parameter(s) include/exclude order types not null when collect all order types is YES', msd_conc_log_util.C_ERROR);
1272 return -1;
1273
1274 ELSE
1275
1276 l_order_type_flag := C_ALL;
1277 l_order_type_ids := '';
1278
1279 END IF;
1280
1281 END IF;
1282
1283 p_order_type_flag := l_order_type_flag;
1284 p_order_type_ids := l_order_type_ids;
1285 p_retcode := 0;
1286 RETURN l_invalid_count;
1287
1288 END validate_input_parameters;
1289
1290 END MSD_COLLECT_FACT_DATA;