[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_UPLOAD_FORECAST
Source
1 PACKAGE BODY MSD_DEM_UPLOAD_FORECAST AS
2 /* $Header: msddemufb.pls 120.4.12010000.4 2008/07/11 09:52:20 syenamar ship $ */
3
4 /*** CONSTANTS ***/
5
6
7 VS_MSG_SALES_TABLE CONSTANT VARCHAR2(16) := 'LOAD SALES TABLE';
8 VS_MSG_ITEMS_TABLE CONSTANT VARCHAR2(16) := 'LOAD ITEMS TABLE';
9 VS_MSG_LOCATION_TABLE CONSTANT VARCHAR2(19) := 'LOAD LOCATION TABLE';
10 VS_MSG_UPLOAD_FCST CONSTANT VARCHAR2(15) := 'UPLOAD FORECAST';
11
12 VS_MSG_LOADING CONSTANT VARCHAR2(8) := 'Loading ';
13 VS_MSG_LOADED CONSTANT VARCHAR2(7) := 'Loaded ';
14 VS_MSG_STARTED CONSTANT VARCHAR2(7) := 'Started';
15 VS_MSG_SUCCEEDED CONSTANT VARCHAR2(9) := 'Succeeded';
16 VS_MSG_LOADE_ERROR CONSTANT VARCHAR2(12) := 'Load error: ';
17 VS_MSG_ITEMS CONSTANT VARCHAR2(12) := 'Items';
18 VS_MSG_LOCATIONS CONSTANT VARCHAR2(12) := 'Locations';
19 VS_MSG_SALES CONSTANT VARCHAR2(12) := 'Sales';
20
21 /*** PRIVATE FUNCTIONS ***
22 * GET_LEVEL_COLUMN
23 * GET_SERIES_COLUMN
24 */
25
26 /*
27 * This function given the level name gives the level# column for the level
28 * in the data profile
29 */
30 FUNCTION GET_LEVEL_COLUMN (
31 p_data_profile_id IN NUMBER,
32 p_level_name IN VARCHAR2)
33 RETURN VARCHAR2
34 IS
35 x_table_name VARCHAR2(50) := NULL;
36 x_sql VARCHAR2(1000) := NULL;
37
38 x_lorder NUMBER := NULL;
39 x_level_column VARCHAR2(30) := NULL;
40
41 /*
42 * Bug#7197339 - Use Group Table Id instead of the Table Label field
43 * Use the ID obtained from lookups instead of hard-coded one
44 */
45 x_group_table_id NUMBER := NULL;
46 x_level_id_lkup_code VARCHAR2(30) := NULL;
47
48 BEGIN
49
50 /*
51 * Bug#7197339 - Use Group Table Id instead of the Table Label field
52 * Use the ID obtained from lookups instead of hard-coded one
53 */
54 IF (p_level_name = C_ITEM)
55 THEN
56 x_level_id_lkup_code := 'LEVEL_ITEM';
57 ELSIF (p_level_name = C_PRODUCT_FAMILY)
58 THEN
59 x_level_id_lkup_code := 'LEVEL_PRODUCT_FAMILY';
60 ELSIF (p_level_name = C_ORGANIZATION)
61 THEN
62 x_level_id_lkup_code := 'LEVEL_ORGANIZATION';
63 ELSIF (p_level_name = C_SITE)
64 THEN
65 x_level_id_lkup_code := 'LEVEL_SITE';
66 ELSIF (p_level_name = C_CUSTOMER)
67 THEN
68 x_level_id_lkup_code := 'LEVEL_ACCOUNT';
69 ELSIF (p_level_name = C_CUSTOMER_ZONE)
70 THEN
71 x_level_id_lkup_code := 'LEVEL_TRADING_PARTNER_ZONE';
72 ELSIF (p_level_name = C_ZONE)
73 THEN
74 x_level_id_lkup_code := 'LEVEL_ZONE';
75 ELSIF (p_level_name = C_DEMAND_CLASS)
76 THEN
77 x_level_id_lkup_code := 'LEVEL_DEMAND_CLASS';
78 ELSE
79 RETURN NULL;
80 END IF;
81
82 --syenamar Bug#7197339
83 x_group_table_id := to_number(msd_dem_common_utilities.get_lookup_value ('MSD_DEM_DEMANTRA_OBJECT_ID', x_level_id_lkup_code));
84 /*
85 * Return NULL in case group_table_id is null, i.e. no value fetched from lookups.
86 * In case lookup contains invalid number exception block at end of function handles it and returns NULL.
87 */
88 IF (x_group_table_id IS NULL)
89 THEN
90 RETURN NULL;
91 END IF;
92 --syenamar
93
94 x_table_name := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'TRANSFER_QUERY_LEVELS');
95 x_sql := 'SELECT tql.lorder ' ||
96 ' FROM ' || x_table_name || ' tql, ';
97
98 x_table_name := NULL;
99 x_table_name := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'GROUP_TABLES');
100 x_sql := x_sql || x_table_name || ' gt ' ||
101 ' WHERE gt.group_table_id = ' || x_group_table_id ||
102 ' AND gt.status = ''ACTIVE'' ' ||
103 ' AND gt.group_table_id = tql.level_id ' ||
104 ' AND tql.id = ' || p_data_profile_id;
105
106 EXECUTE IMMEDIATE x_sql INTO x_lorder;
107
108 x_level_column := 'LEVEL' || to_char(x_lorder);
109
110 RETURN upper(x_level_column);
111
112 EXCEPTION
113 WHEN OTHERS THEN
114 RETURN NULL;
115
116 END GET_LEVEL_COLUMN;
117
118
119 /*
120 * This function gets the column for the series in the data profile
121 */
122 FUNCTION GET_SERIES_COLUMN (
123 p_data_profile_id IN NUMBER,
124 p_series_prefix IN VARCHAR2,
125 p_add_prefix IN VARCHAR2 DEFAULT NULL)
126 RETURN VARCHAR2
127 IS
128 x_table_name VARCHAR2(50) := NULL;
129 x_sql VARCHAR2(1000) := NULL;
130
131 x_series_prefix VARCHAR2(50) := NULL;
132 x_ffs VARCHAR2(10) := NULL;
133
134 x_series VARCHAR2(50) := NULL;
135
136
137 BEGIN
138
139 IF (p_series_prefix = 'FCST_')
140 THEN
141 x_series_prefix := p_series_prefix;
142 x_ffs := 'C_PRED';
143 ELSIF (p_series_prefix IN ('PRTY_', 'ACRY_'))
144 THEN
145 x_series_prefix := p_series_prefix;
146 x_ffs := '$$$';
147 ELSIF (p_series_prefix = 'DKEY_')
148 THEN
149 x_series_prefix := p_series_prefix || p_add_prefix;
150 x_ffs := '$$$';
151 ELSE
152 RETURN NULL;
153 END IF;
154
155 x_table_name := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'TRANSFER_QUERY_SERIES');
156 x_sql := 'SELECT cf.computed_name ' ||
157 ' FROM ' || x_table_name || ' tqs, ';
158
159 x_table_name := NULL;
160 x_table_name := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'COMPUTED_FIELDS');
161 x_sql := x_sql || x_table_name || ' cf ' ||
162 ' WHERE tqs.id = ' || p_data_profile_id ||
163 ' AND cf.forecast_type_id = tqs.series_id ' ||
164 ' AND ( upper(cf.computed_name) like ''' || x_series_prefix || '%'' ' ||
165 ' OR upper(cf.computed_name) = ''' || x_ffs || ''') ' ||
166 ' AND rownum < 2 ';
167
168 EXECUTE IMMEDIATE x_sql INTO x_series;
169
170 RETURN upper(x_series);
171
172 EXCEPTION
173 WHEN OTHERS THEN
174 RETURN NULL;
175 END GET_SERIES_COLUMN;
176
177
178
179 /*** PRIVATE PROCEDURES
180 * GET_TIME_STRINGS
181 */
182
183
184 /*
185 */
186 PROCEDURE GET_TIME_STRINGS (
187 p_bucket_type OUT NOCOPY NUMBER,
188 p_start_time OUT NOCOPY VARCHAR2,
189 p_end_time OUT NOCOPY VARCHAR2,
190 p_res_type OUT NOCOPY NUMBER,
191 p_time_from_clause OUT NOCOPY VARCHAR2,
192 p_time_res IN NUMBER)
193 IS
194
195 x_sql VARCHAR2(1000) := NULL;
196 x_tgroup_res VARCHAR2(50) := NULL;
197 x_dm_wiz_dm_def VARCHAR2(50) := NULL;
198
199 x_tg_res VARCHAR2(100) := NULL;
200 x_months_number NUMBER := NULL;
201 x_inputs_column VARCHAR2(50) := NULL;
202 x_is_default NUMBER := NULL;
203
204 x_dm_time_bucket VARCHAR2(30) := NULL;
205 x_aggregation_method NUMBER(1) := NULL;
206
207 x_is_forward BOOLEAN := NULL;
208
209 x_inputs VARCHAR2(50) := NULL;
210 x_bucket_size NUMBER := NULL;
211
212 BEGIN
213
214 x_tgroup_res := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'TGROUP_RES');
215
216 IF (x_tgroup_res IS NULL)
217 THEN
218 RETURN;
219 END IF;
220
221 /* Get Time Res Info */
222 x_sql := 'SELECT tg_res, months_number, inputs_column, is_default ' ||
223 ' FROM ' || x_tgroup_res ||
224 ' WHERE tg_res_id = ' || p_time_res;
225
226 EXECUTE IMMEDIATE x_sql INTO x_tg_res,
227 x_months_number,
228 x_inputs_column,
229 x_is_default;
230
231 x_dm_wiz_dm_def := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'DM_WIZ_DM_DEF');
232 IF (x_dm_wiz_dm_def IS NULL)
233 THEN
234 RETURN;
235 END IF;
236
237 /* Get the data model lowest time level */
238 x_sql := 'SELECT time_bucket, aggregation_method ' ||
239 ' FROM ' || x_dm_wiz_dm_def ||
240 ' WHERE dm_or_template = 2 ' ||
241 ' AND is_active = 1 ' ||
242 ' AND rownum < 2 ';
243
244 EXECUTE IMMEDIATE x_sql INTO x_dm_time_bucket,
245 x_aggregation_method;
246
247 /* Get the aggregation type */
248 IF (upper(x_dm_time_bucket) = 'DAY')
249 THEN
250 x_is_forward := FALSE;
251 ELSIF (upper(x_dm_time_bucket) = 'WEEK')
252 THEN
253 IF (x_aggregation_method = 1)
254 THEN
255 x_is_forward := TRUE;
256 ELSE
257 x_is_forward := FALSE;
258 END IF;
259 ELSIF (upper(x_dm_time_bucket) = 'MONTH')
260 THEN
261 x_is_forward := FALSE;
262 ELSE
263 RETURN;
264 END IF;
265
266
267 /* Get the time strings */
268 IF (upper(x_dm_time_bucket) = 'DAY')
269 THEN
270 /* Export Time Level = Day */
271 IF (x_is_default = 1)
272 THEN
273 p_bucket_type := C_BUCKET_TYPE_DAY;
274
275 p_start_time := ' exp.sdate ';
276 p_end_time := ' exp.sdate ';
277
278 p_res_type := 1;
279
280 RETURN;
281
282 END IF;
283
284 IF (x_months_number IS NOT NULL)
285 THEN
286 IF (x_months_number = 7)
287 THEN
288 p_bucket_type := C_BUCKET_TYPE_WEEK;
289 ELSE
290 p_bucket_type := C_BUCKET_TYPE_MONTH;
291 END IF;
292
293 p_start_time := ' exp.sdate ';
294 p_end_time := ' exp.sdate + ' || to_char(x_months_number - 1) || ' ';
295
296 p_res_type := 1;
297
298 RETURN;
299
300 END IF;
301
302 ELSIF (upper(x_dm_time_bucket) = 'WEEK')
303 THEN
304 IF (x_months_number IS NOT NULL)
305 THEN
306 /* Export Time Level = Day */
307 IF (x_is_default = 1)
308 THEN
309 p_bucket_type := C_BUCKET_TYPE_WEEK;
310 ELSE
311 p_bucket_type := C_BUCKET_TYPE_MONTH;
312 END IF;
313
314 IF (x_is_forward)
315 THEN
316 p_start_time := ' exp.sdate - ' || to_char((x_months_number * 7) - 1) || ' ';
317 p_end_time := ' exp.sdate ';
318 ELSE
319 p_start_time := ' exp.sdate ';
320 p_end_time := ' exp.sdate + ' || to_char((x_months_number * 7) - 1) || ' ';
321 END IF;
322
323 p_res_type := 1;
324
325 RETURN;
326 END IF;
327
328 ELSIF (upper(x_dm_time_bucket) = 'MONTH')
329 THEN
330 IF (x_is_default = 1)
331 THEN
332 p_bucket_type := C_BUCKET_TYPE_MONTH;
333
334 p_start_time := ' exp.sdate ';
335 p_end_time := ' round(exp.sdate + 16, ''MONTH'') - 1 ';
336
337 p_res_type := 1;
338
339 END IF;
340
341 RETURN;
342 ELSE
343 RETURN;
344 END IF;
345
346 /* Time Resolution Bucket Size is not fixed, availabe in INPUTS */
347 x_inputs := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'INPUTS');
348 IF (x_inputs IS NULL OR x_inputs_column IS NULL)
349 THEN
350 RETURN;
351 END IF;
352
353 p_time_from_clause := ' (SELECT min(datet) start_time, max(datet) end_time ' ||
354 ' FROM ' || x_inputs || ' GROUP BY ' || x_inputs_column ||
355 ' ) inp ';
356
357 IF (upper(x_dm_time_bucket) = 'DAY')
358 THEN
359 p_res_type := 3;
360
361 p_start_time := ' inp.start_time ';
362 p_end_time := ' inp.end_time ';
363
364 ELSIF (upper(x_dm_time_bucket) = 'WEEK')
365 THEN
366 IF (x_is_forward)
367 THEN
368 p_res_type := 2;
369
370 p_start_time := ' inp.start_time - 6 ';
371 p_end_time := ' inp.end_time ';
372 ELSE
373 p_res_type := 3;
374
375 p_start_time := ' inp.start_time ';
376 p_end_time := ' inp.end_time + 6 ';
377 END IF;
378 ELSE
379 p_res_type := 3;
380
381 p_start_time := ' inp.start_time ';
382 p_end_time := ' round(inp.end_time + 16, ''MONTH'') - 1 ';
383 END IF;
384
385 IF (upper(x_dm_time_bucket) = 'WEEK')
386 THEN
387 p_bucket_type := C_BUCKET_TYPE_MONTH;
388 ELSE
389
390 /* Get the Bucket Type */
391 x_sql := 'SELECT count(*) FROM ' || x_inputs || ' WHERE ' || x_inputs_column || ' = 1 ';
392
393 EXECUTE IMMEDIATE x_sql INTO x_bucket_size;
394
395 IF (x_bucket_size = 7)
396 THEN
397 p_bucket_type := C_BUCKET_TYPE_WEEK;
398 ELSE
399 p_bucket_type := C_BUCKET_TYPE_MONTH;
400 END IF;
401 END IF;
402
403 RETURN;
404
405 EXCEPTION
406 WHEN OTHERS THEN
407 RETURN;
408 END GET_TIME_STRINGS;
409
410
411 /*** PUBLIC FUNCTIONS ***/
412
413 /*
414 * This function returns the sr_instance_id to be used for a global forecast
415 */
416 FUNCTION GET_SR_INSTANCE_ID_FOR_GLOBAL
417 RETURN NUMBER
418 IS
419 CURSOR c_get_sr_instance_id
420 IS
421 SELECT min(instance_id)
422 FROM msc_apps_instances
423 WHERE instance_type <> 3
424 AND validation_org_id IS NOT NULL;
425
426 x_sr_instance_id NUMBER := NULL;
427 BEGIN
428 OPEN c_get_sr_instance_id;
429 FETCH c_get_sr_instance_id INTO x_sr_instance_id;
430 CLOSE c_get_sr_instance_id;
431
432 RETURN x_sr_instance_id;
433
434 EXCEPTION
435 WHEN OTHERS THEN
436 RETURN NULL;
437 END GET_SR_INSTANCE_ID_FOR_GLOBAL;
438
439
440
441 /* This function returns 1 if the data profile is fit for upload to ASCP
442 * Current check only includes that a forecast series with internal name
443 * starting 'FCST_' must be present.
444 */
445 FUNCTION IS_VALID_SCENARIO (
446 p_data_profile_id IN NUMBER)
447 RETURN NUMBER
448 IS
449 x_fcst_column VARCHAR2(50) := NULL;
450 BEGIN
451 x_fcst_column := get_series_column (
452 p_data_profile_id,
453 C_FORECAST_SERIES_PREFIX);
454
455 IF (x_fcst_column IS NOT NULL)
456 THEN
457 RETURN 1;
458 END IF;
459
460 RETURN 2;
461
462 EXCEPTION
463 WHEN OTHERS THEN
464 RETURN 2;
465 END IS_VALID_SCENARIO;
466
467
468
469 /* This function returns -23453 if the data profile contains non-global
470 * forecast, else it returns the id of the source instance for which
471 * global forecasting is being done.
472 */
473 FUNCTION GET_SR_INSTANCE_ID_FOR_PROFILE (
474 p_data_profile_id IN NUMBER)
475 RETURN NUMBER
476 IS
477 x_org_level VARCHAR2(50) := NULL;
478 x_sr_instance_id_for_global NUMBER := NULL;
479 BEGIN
480 x_org_level := get_level_column (
481 p_data_profile_id,
482 C_ORGANIZATION);
483
484 IF (x_org_level IS NULL) /* Global */
485 THEN
486 x_sr_instance_id_for_global := get_sr_instance_id_for_global;
487
488 IF (x_sr_instance_id_for_global IS NOT NULL)
489 THEN
490 RETURN x_sr_instance_id_for_global;
491 ELSE
492 RETURN NULL;
493 END IF;
494
495 END IF;
496
497 /* Local */
498 RETURN -23453;
499
500 EXCEPTION
501 WHEN OTHERS THEN
502 RETURN NULL;
503 END GET_SR_INSTANCE_ID_FOR_PROFILE;
504
505
506
507 /* This function gets the error type 'MAD' or 'MAPE' given the data
508 * profile id
509 */
510 FUNCTION GET_ERROR_TYPE (
511 p_data_profile_id IN NUMBER)
512 RETURN VARCHAR2
513 IS
514 x_error_column VARCHAR2(50) := NULL;
515 x_error_type VARCHAR2(50) := NULL;
516 BEGIN
517 x_error_column := get_series_column (
518 p_data_profile_id,
519 C_FCST_ACRY_SERIES_PREFIX);
520
521 IF (x_error_column IS NULL)
522 THEN
523 RETURN NULL;
524 ELSE
525
526 IF (instr(x_error_column, 'MAD') <> 0)
527 THEN
528 x_error_type := 'MAD';
529 ELSIF (instr(x_error_column, 'MAPE') <> 0)
530 THEN
531 x_error_type := 'MAPE';
532 ELSE
533 RETURN NULL;
534 END IF;
535
536 RETURN x_error_type;
537
538 END IF;
539
540 RETURN NULL;
541
542 EXCEPTION
543 WHEN OTHERS THEN
544 RETURN NULL;
545 END GET_ERROR_TYPE;
546
547
548
549 /* This function return 'Y' if the data profile contains global forecast
550 * else returns 'N'.
551 */
552 FUNCTION IS_GLOBAL_SCENARIO (
553 p_data_profile_id IN NUMBER)
554 RETURN VARCHAR2
555 IS
556 x_org_level VARCHAR2(50) := NULL;
557 BEGIN
558 x_org_level := get_level_column (
559 p_data_profile_id,
560 C_ORGANIZATION);
561
562 IF (x_org_level IS NULL) /* Global */
563 THEN
564 RETURN 'Y';
565 END IF;
566
567 RETURN 'N';
568
569 EXCEPTION
570 WHEN OTHERS THEN
571 RETURN 'N';
572 END IS_GLOBAL_SCENARIO;
573
574
575
576 /* This function returns the source key of the customer, given the customer
577 * zone
578 */
579 FUNCTION GET_CUSTOMER_FROM_TPZONE (
580 p_tp_zone IN VARCHAR2,
581 p_sr_instance_id IN NUMBER)
582 RETURN NUMBER
583 IS
584
585 x_sr_customer_pk NUMBER := NULL;
586 x_account_number VARCHAR2(255) := NULL;
587
588 BEGIN
589
590 x_account_number := to_char(substr (p_tp_zone,
591 instr(p_tp_zone, ':', 1) + 1,
592 instr(p_tp_zone, ':', 1, 2) - instr(p_tp_zone, ':', 1) - 1));
593
594 IF (x_account_number IS NOT NULL)
595 THEN
596
597 SELECT mtil.sr_tp_id
598 INTO x_sr_customer_pk
599 FROM
600 msc_tp_id_lid mtil
601 WHERE
602 mtil.sr_cust_account_number = x_account_number
603 and mtil.sr_instance_id = p_sr_instance_id;
604
605 END IF;
606
607 RETURN x_sr_customer_pk;
608
609 EXCEPTION
610 WHEN OTHERS THEN
611 RETURN NULL;
612 END GET_CUSTOMER_FROM_TPZONE;
613
614
615
616
617 /* This function returns the source key of the zone, given the customer zone
618 */
619 FUNCTION GET_ZONE_FROM_TPZONE (
620 p_tp_zone IN VARCHAR2,
621 p_sr_instance_id IN NUMBER)
622 RETURN NUMBER
623 IS
624
625 x_zone VARCHAR2(255) := NULL;
626 x_sr_zone_pk NUMBER := NULL;
627
628 BEGIN
629
630 x_zone := substr (p_tp_zone,
631 instr(p_tp_zone, ':', 1, 2) + 1);
632
633 IF (x_zone IS NOT NULL)
634 THEN
635 SELECT mr.region_id
636 INTO x_sr_zone_pk
637 FROM msc_regions mr
638 WHERE
639 mr.zone = x_zone
640 AND mr.sr_instance_id = p_sr_instance_id;
641 END IF;
642
643 RETURN x_sr_zone_pk;
644
645 EXCEPTION
646 WHEN OTHERS THEN
647 RETURN NULL;
648 END GET_ZONE_FROM_TPZONE;
649
650
651 /*** PUBLIC PROCEDURES ***/
652
653 /*
654 * This procedure, given the export integration data profile name, pushes the
655 * forecast data along with forecast accuracy and demand priority from the
656 * export view to table MSD_DP_SCN_ENTRIES_DENORM. The member codes are
657 * transformed to the corresponding source identifiers. The 'Organization'
658 * level member is used to find out the source instance to which the record
659 * belongs.
660 * The internal names of the series will be used to get the semantic of the
661 * series. They are as follows -
662 * 1. Forecast Series - The internal name should start with 'FCST_'
663 * 2. Demand Priority Series - The internal name should start with 'PRTY_'
664 * 3. Forecast Accuracy Series - The internal name should start with 'ACRY_'
665 * 4. Destination Key Series - The internal name should start with 'DKEY_'
666 */
667 PROCEDURE UPLOAD_FORECAST (
668 p_export_data_profile IN VARCHAR2)
669 IS
670
671 TYPE CUR_TYPE IS REF CURSOR;
672 x_cur_type CUR_TYPE;
673
674 x_errbuf VARCHAR2(200) := NULL;
675 x_retcode VARCHAR2(100) := NULL;
676
677 x_sql VARCHAR2(2000) := NULL;
678 x_table_name VARCHAR2(50) := NULL;
679 x_schema VARCHAR(50) := NULL;
680
681 x_profile_id NUMBER := NULL;
682 x_export_data_profile VARCHAR2(50) := NULL;
683 x_presentation_type NUMBER := NULL;
684 x_view_name VARCHAR2(30) := NULL;
685 x_time_res_id NUMBER := NULL;
686 x_unit_id NUMBER := NULL;
687 x_index_id NUMBER := NULL;
688 x_data_scale NUMBER := NULL;
689 x_integration_type NUMBER := NULL;
690 x_export_type NUMBER := NULL;
691 x_last_export_date DATE := NULL;
692 x_is_view_present NUMBER := 0;
693
694 x_dm_time_bucket VARCHAR2(30) := NULL;
695 x_aggregation_method NUMBER(1) := NULL;
696
697 x_demand_plan_id NUMBER := NULL;
698 x_scenario_id NUMBER := NULL;
699 x_demand_id_offset NUMBER := NULL;
700 x_bucket_type NUMBER := NULL;
701 x_start_time VARCHAR2(100) := NULL;
702 x_end_time VARCHAR2(100) := NULL;
703 x_sr_organization_id VARCHAR2(50) := NULL;
704 x_sr_ship_to_loc_id VARCHAR2(50) := NULL;
705 x_sr_customer_id VARCHAR2(100) := NULL;
706 x_sr_zone_id VARCHAR2(100) := NULL;
707 x_sr_demand_class VARCHAR(50) := NULL;
708 x_uom_code VARCHAR2(100) := NULL;
709 x_quantity VARCHAR2(500) := NULL;
710 x_fcst_column VARCHAR2(50) := NULL;
711 x_error_type VARCHAR2(50) := NULL;
712 x_error_column VARCHAR2(50) := NULL;
713 x_demand_priority_column VARCHAR2(50) := NULL;
714
715 x_select_clause VARCHAR2(3000) := NULL;
716 x_from_clause VARCHAR2(500) := NULL;
717 x_where_clause VARCHAR2(3000) := NULL;
718 x_insert_clause VARCHAR2(1000) := NULL;
719 x_small_sql VARCHAR2(600) := NULL;
720 x_large_sql VARCHAR2(6000) := NULL;
721
722 x_is_global_fcst NUMBER(1) := NULL;
723
724 x_org_level VARCHAR2(30) := NULL;
725 x_prd_level VARCHAR2(30) := NULL;
726 x_ship_to_level VARCHAR2(30) := NULL;
727 x_cust_level VARCHAR2(30) := NULL;
728 x_zone_level VARCHAR2(30) := NULL;
729 x_cust_zone_level VARCHAR2(30) := NULL;
730 x_demand_class_level VARCHAR2(30) := NULL;
731
732 x_org_key_column VARCHAR2(30) := NULL;
733 x_prd_key_column VARCHAR2(30) := NULL;
734 x_final_prd_column VARCHAR2(30) := NULL;
735 x_ship_to_key_column VARCHAR2(30) := NULL;
736
737 x_sr_instance_id_for_global NUMBER := NULL;
738
739 x_res_type NUMBER := NULL;
740 x_time_from_clause VARCHAR2(500) := NULL;
741
742 BEGIN
743
744 /* Alter session to APPS */
745 x_small_sql := 'alter session set current_schema = APPS';
746 EXECUTE IMMEDIATE x_small_sql;
747
748
749 x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
750 IF (x_schema IS NULL)
751 THEN
752 raise_application_error (-20001, 'Error: msd_dem_upload_forecast.upload_forecast - Unable to find schema name');
753 END IF;
754
755
756
757 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
758 VS_MSG_LOADING || ' ' || p_export_data_profile || ''' , ''' || VS_MSG_STARTED || ''' ); END;';
759
760 EXECUTE IMMEDIATE x_small_sql;
761
762 /* Initialize global variables */
763 IF (p_export_data_profile IS NULL)
764 THEN
765 raise_application_error (-20002, 'Error: msd_dem_upload_forecast.upload_forecast - No export data profile name provided');
766 ELSE
767 x_export_data_profile := upper(p_export_data_profile);
768 END IF;
769
770 /* Get the export data profile info */
771 x_table_name := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'TRANSFER_QUERY');
772 x_sql := 'SELECT id, presentation_type, view_name, ' ||
773 ' time_res_id, unit_id, index_id, data_scale, ' ||
774 ' integration_type, export_type, last_export_date ' ||
775 ' FROM ' || x_table_name ||
776 ' WHERE upper(query_name) = ''' || x_export_data_profile || '''';
777
778 OPEN x_cur_type FOR x_sql;
779 FETCH x_cur_type INTO x_profile_id,
780 x_presentation_type,
781 x_view_name,
782 x_time_res_id,
783 x_unit_id,
784 x_index_id,
785 x_data_scale,
786 x_integration_type,
787 x_export_type,
788 x_last_export_date;
789 CLOSE x_cur_type;
790
791 /* Bug# 6326524 */
792 x_sql := 'SELECT count(1) FROM all_views ' ||
793 ' WHERE owner = upper(''' || x_schema || ''')' ||
794 ' AND view_name = upper(''' || x_view_name || ''')';
795 EXECUTE IMMEDIATE x_sql INTO x_is_view_present;
796
797
798 /*** Check basic error conditions - BEGIN ***/
799
800 IF (x_profile_id IS NULL)
801 THEN
802 raise_application_error (-20003, 'Error: msd_dem_upload_forecast.upload_forecast - Unable to get export data profile id');
803 ELSIF (x_integration_type = C_IMPORT_DATA_PROFILE)
804 THEN
805 raise_application_error (-20004, 'Error: msd_dem_upload_forecast.upload_forecast - ' || p_export_data_profile || 'is not an export data profile');
806 ELSIF (x_export_type = C_EXPORT_TYPE_INCR)
807 THEN
808 raise_application_error (-20005, 'Error: msd_dem_upload_forecast.upload_forecast - Incremental export type is not supported');
809 ELSIF (x_index_id IS NOT NULL)
810 THEN
811 raise_application_error (-20006, 'Error: msd_dem_upload_forecast.upload_forecast - Forecast amount cannot be uploaded');
812 ELSIF (x_is_view_present = 0)
813 THEN
814 raise_application_error (-20007, 'Error: msd_dem_upload_forecast.upload_forecast - Forecast has not yet been exported');
815 ELSIF (x_presentation_type = C_PSNT_TYPE_DESC)
816 THEN
817 raise_application_error (-20008, 'Error: msd_dem_upload_forecast.upload_forecast - Presentation type must by Code');
818 END IF;
819
820 /*** Check basic error conditions - END ***/
821
822
823 x_demand_plan_id := C_DEMAND_PLAN_ID;
824 x_scenario_id := x_profile_id + C_SCENARIO_ID_OFFSET;
825
826 x_select_clause := ' SELECT ' || x_demand_plan_id || ' , ' ||
827 x_scenario_id || ' , ' ||
828 ' rownum - 1 , ';
829
830 /*** Get Time Info - BEGIN ***/
831
832 get_time_strings (
833 x_bucket_type,
834 x_start_time,
835 x_end_time,
836 x_res_type,
837 x_time_from_clause,
838 x_time_res_id);
839
840 IF (x_res_type IS NULL)
841 THEN
842 raise_application_error (-20009, 'Error: msd_dem_upload_forecast.upload_forecast - Unable to find schema name');
843 END IF;
844
845 /*** Get Time Info - END ***/
846
847 x_select_clause := x_select_clause || x_bucket_type || ' , '
848 || x_start_time || ' , '
849 || x_end_time || ' , ';
850
851 x_from_clause := ' FROM ' || x_schema || '.' || x_view_name || ' exp, ' ||
852 ' msc_system_items msi, ';
853
854 /* Get the levels at which forecast has been exported
855 * Expected Levels -
856 * 1. Item AND/OR Product Family
857 * 2. (Site/Customer/Customer Zone/Zone) AND/OR (Ship From dimension levels)
858 * 3. Demand Class (Not Mandatory)
859 */
860
861 /* PRODUCT */
862 x_prd_level := get_level_column (x_profile_id, C_ITEM);
863 IF (x_prd_level IS NULL)
864 THEN
865 x_prd_level := get_level_column (x_profile_id, C_PRODUCT_FAMILY);
866
867 IF (x_prd_level IS NULL)
868 THEN
869 raise_application_error (-20010, 'Error: msd_dem_upload_forecast.upload_forecast - Item or Product Family level is required for upload');
870 END IF;
871 ELSE
872 x_prd_key_column := get_series_column (x_profile_id, C_DKEY_SERIES_PREFIX, C_DKEY_ITEM);
873 END IF;
874
875 x_select_clause := x_select_clause || ' msi.sr_instance_id, ';
876
877 /* ORGANIZATION */
878 x_org_level := get_level_column (x_profile_id, C_ORGANIZATION);
879 IF (x_org_level IS NULL) /* global */
880 THEN
881 x_is_global_fcst := 1;
882 x_sr_organization_id := '-1';
883
884 x_sr_instance_id_for_global := get_sr_instance_id_for_global;
885 IF (x_sr_instance_id_for_global IS NULL)
886 THEN
887 raise_application_error (-20011, 'Error: msd_dem_upload_forecast.upload_forecast - Unable to get sr_instance_id for global forecast');
888 END IF;
889
890 ELSE
891 x_is_global_fcst := 2;
892 x_sr_organization_id := ' msi.organization_id ';
893
894 END IF;
895
896 x_select_clause := x_select_clause || x_sr_organization_id || ' , ' ||
897 ' msi.sr_inventory_item_id, ';
898
899 IF (x_is_global_fcst = 2)
900 THEN
901 x_from_clause := x_from_clause || ' msc_trading_partners mtp_org, ';
902 END IF;
903
904 /* GEOGRAPHY */
905
906 x_sr_ship_to_loc_id := ' NULL ';
907 x_sr_customer_id := ' NULL ';
908 x_sr_zone_id := ' NULL ';
909
910 x_ship_to_level := get_level_column (x_profile_id, C_SITE);
911 IF (x_ship_to_level IS NOT NULL)
912 THEN
913 x_sr_ship_to_loc_id := ' mtpsil.sr_tp_site_id ';
914 x_from_clause := x_from_clause || ' msc_tp_site_id_lid mtpsil, ';
915 x_ship_to_key_column := get_series_column (x_profile_id, C_DKEY_SERIES_PREFIX, C_DKEY_SITE);
916
917 IF (x_ship_to_key_column IS NULL)
918 THEN
919 raise_application_error (-20013, 'Error: msd_dem_upload_forecast.upload_forecast - Destination key series for the level Site not found');
920 END IF;
921
922 END IF;
923
924 x_cust_level := get_level_column (x_profile_id, C_CUSTOMER);
925 x_cust_zone_level := get_level_column (x_profile_id, C_CUSTOMER_ZONE);
926 IF (x_cust_level IS NOT NULL)
927 THEN
928 x_sr_customer_id := ' mtil.sr_tp_id ';
929 x_from_clause := x_from_clause || ' msc_tp_id_lid mtil, ';
930 ELSIF (x_ship_to_level IS NOT NULL)
931 THEN
932 x_sr_customer_id := ' mtpsil.sr_cust_acct_id ';
933 ELSIF (x_cust_zone_level IS NOT NULL)
934 THEN
935 x_sr_customer_id := ' msd_dem_upload_forecast.get_customer_from_tpzone ( exp.' || x_cust_zone_level || ', mai.instance_id ) ';
936 END IF;
937
938 x_zone_level := get_level_column (x_profile_id, C_ZONE);
939 IF (x_zone_level IS NOT NULL)
940 THEN
941 x_sr_zone_id := ' mr.region_id ';
942 x_from_clause := x_from_clause || ' msc_regions mr, ';
943 ELSIF (x_cust_zone_level IS NOT NULL)
944 THEN
945 x_sr_zone_id := ' msd_dem_upload_forecast.get_zone_from_tpzone ( exp.' || x_cust_zone_level || ', mai.instance_id ) ';
946 END IF;
947
948 x_select_clause := x_select_clause || x_sr_ship_to_loc_id || ' , ' ||
949 x_sr_customer_id || ' , ' ||
950 x_sr_zone_id || ' , ';
951
952 /* DEMAND CLASS */
953 x_demand_class_level := get_level_column (x_profile_id, C_DEMAND_CLASS);
954 IF (x_demand_class_level IS NULL)
955 THEN
956 x_sr_demand_class := ' NULL ';
957 ELSE
958 x_sr_demand_class := ' mdc.demand_class ';
959 x_from_clause := x_from_clause || ' msc_demand_classes mdc, ';
960 END IF;
961
962 IF (x_res_type = 1)
963 THEN
964 x_from_clause := x_from_clause || ' msc_apps_instances mai ';
965 ELSE
966 x_from_clause := x_from_clause || ' msc_apps_instances mai, ' || x_time_from_clause;
967 END IF;
968
969 x_select_clause := x_select_clause || x_sr_demand_class || ' , ' ||
970 ' msi.inventory_item_id, ';
971
972
973 x_uom_code := msd_dem_common_utilities.get_uom_code (x_unit_id);
974 x_select_clause := x_select_clause || '''' || x_uom_code || ''', ' ||
975 ' msi.uom_code, ';
976
977 /* SINCE AMOUNT IS NOT AVAILABLE USE ASCP's LIST PRICE VALUE */
978 x_select_clause := x_select_clause || ' msi.list_price * ((100 - msi.average_discount)/100), ';
979
980 /* FORECAST SERIES */
981 x_fcst_column := get_series_column (x_profile_id, C_FORECAST_SERIES_PREFIX);
982
983 IF (x_fcst_column IS NULL)
984 THEN
985 raise_application_error (-20014, 'Error: msd_dem_upload_forecast.upload_forecast - Forecast series not found');
986 END IF;
987
988 IF (x_unit_id = 1 OR upper(x_uom_code) = 'UNITS')
989 THEN
990 x_quantity := ' round (exp.' || x_fcst_column || ' * ' || x_data_scale || ', ' || C_ROUNDOFF_PLACES || ' ) ';
991 ELSE
992 x_quantity := ' round (exp.' || x_fcst_column ||
993 ' * ' || x_data_scale ||
994 ' * decode ( ''' || x_uom_code || ''', msi.uom_code, 1, ' ||
995 ' msd_dem_common_utilities.uom_convert(msi.inventory_item_id, ' ||
996 ' null, ' ||
997 '''' || x_uom_code || ''' , ' ||
998 ' msi.uom_code)), ' ||
999 C_ROUNDOFF_PLACES || ' ) ';
1000 END IF;
1001
1002 x_select_clause := x_select_clause || x_quantity || ' , ';
1003
1004 /* FORECAST ACCURACY */
1005 x_error_column := get_series_column (x_profile_id, C_FCST_ACRY_SERIES_PREFIX);
1006
1007 IF (x_error_column IS NULL)
1008 THEN
1009 x_select_clause := x_select_clause || ' NULL , NULL , ';
1010 ELSE
1011 IF (instr(x_error_column, 'MAD') = 0)
1012 THEN
1013 x_error_type := 'MAPE';
1014 ELSE
1015 x_error_type := 'MAD';
1016 END IF;
1017 x_select_clause := x_select_clause || '''' || x_error_type || ''' , exp.' || x_error_column || ' , ';
1018 END IF;
1019
1020 /* DEMAND PRIORITY SERIES */
1021 x_demand_priority_column := get_series_column (x_profile_id, C_DEMAND_PRTY_SERIES_PREFIX);
1022
1023 IF (x_demand_priority_column IS NULL)
1024 THEN
1025 x_select_clause := x_select_clause || ' NULL , ';
1026 ELSE
1027 x_select_clause := x_select_clause || ' exp.' || x_demand_priority_column || ' , ';
1028 END IF;
1029
1030 x_select_clause := x_select_clause || ' sysdate, ' ||
1031 ' FND_GLOBAL.USER_ID, ' ||
1032 ' FND_GLOBAL.LOGIN_ID ';
1033
1034 /* BUILD WHERE CLAUSE */
1035 IF (x_is_global_fcst = 2)
1036 THEN
1037
1038 x_where_clause := ' WHERE mtp_org.partner_type = 3 ' ||
1039 ' AND exp.' || x_org_level || ' = mtp_org.organization_code ' ||
1040 ' AND msi.plan_id = -1 ' ||
1041 ' AND msi.sr_instance_id = mtp_org.sr_instance_id ' ||
1042 ' AND msi.organization_id = mtp_org.sr_tp_id ';
1043 ELSE
1044
1045 x_where_clause := ' WHERE msi.plan_id = -1 ' ||
1046 ' AND msi.sr_instance_id = ' || to_char(x_sr_instance_id_for_global) ||
1047 ' AND msi.organization_id = mai.validation_org_id ';
1048 END IF;
1049
1050 IF (x_prd_key_column IS NOT NULL)
1051 THEN
1052 x_where_clause := x_where_clause ||
1053 ' AND msi.inventory_item_id = exp.' || x_prd_key_column || ' ';
1054 ELSE
1055 x_where_clause := x_where_clause ||
1056 ' AND msi.item_name = exp.' || x_prd_level || ' ';
1057 END IF;
1058
1059 x_where_clause := x_where_clause ||
1060 ' AND msi.sr_instance_id = mai.instance_id ' ||
1061 ' AND msi.ato_forecast_control <> 3 ' ||
1062 ' AND msi.mrp_planning_code <> 6 ';
1063
1064 /* Bug# 5765391 - Upload forecast for 'Unassociated' geo dimension members also */
1065
1066 IF (x_ship_to_level IS NOT NULL)
1067 THEN
1068 x_where_clause := x_where_clause ||
1069 ' AND mtpsil.tp_site_id (+) = exp.' || x_ship_to_key_column || ' ' ||
1070 ' AND decode (mtpsil.sr_instance_id, null, decode (exp.' || x_ship_to_key_column || ' , null, 1, 0), mai.instance_id, 1, 0) = 1 ';
1071 END IF;
1072
1073 IF (x_cust_level IS NOT NULL)
1074 THEN
1075 x_where_clause := x_where_clause ||
1076 ' AND mtil.sr_cust_account_number (+) = to_char(substr(exp.' || x_cust_level || ',instr(exp.' || x_cust_level || ', '':'', -1) + 1)) ' ||
1077 ' AND mtil.partner_type (+) = 2 ' ||
1078 ' AND decode (mtil.sr_instance_id, null, decode (exp.' || x_cust_level || ' , msd_dem_sr_util.get_null_code, 1, 0), mai.instance_id, 1, 0) = 1 ';
1079 END IF;
1080
1081 IF (x_zone_level IS NOT NULL)
1082 THEN
1083 x_where_clause := x_where_clause ||
1084 ' AND mr.zone (+) = exp.' || x_zone_level || ' ' ||
1085 ' AND decode (mr.sr_instance_id, null, decode (exp.' || x_zone_level || ' , msd_dem_sr_util.get_null_code, 1, 0), mai.instance_id, 1, 0) = 1 ';
1086 END IF;
1087
1088 IF (x_demand_class_level IS NOT NULL)
1089 THEN
1090 x_where_clause := x_where_clause ||
1091 ' AND mdc.meaning (+) = exp.' || x_demand_class_level || ' ' ||
1092 ' AND decode (mdc.sr_instance_id, null, decode (exp.' || x_demand_class_level || ' , msd_dem_sr_util.get_null_code, 1, 0), mai.instance_id, 1, 0) = 1 ';
1093 END IF;
1094
1095 IF (x_res_type = 2)
1096 THEN
1097 x_where_clause := x_where_clause ||
1098 ' AND exp.sdate = inp.end_time ';
1099 ELSIF (x_res_type = 3)
1100 THEN
1101 x_where_clause := x_where_clause ||
1102 ' AND exp.sdate = inp.start_time ';
1103 END IF;
1104
1105
1106 /* Upload ZERO forecast quantity only if MAD forecast error is NON-ZERO */
1107 IF (x_error_column IS NOT NULL AND x_error_type = 'MAD')
1108 THEN
1109
1110 x_where_clause := x_where_clause ||
1111 ' AND decode ( exp.' || x_fcst_column || ' , 0 , ' ||
1112 ' decode ( nvl( exp.' || x_error_column || ' , 0) , 0, ' ||
1113 ' -1, ' ||
1114 ' 1), ' ||
1115 ' 1) = 1 ';
1116 ELSE
1117 x_where_clause := x_where_clause ||
1118 ' AND decode ( nvl( exp.' || x_fcst_column || ' , 0), 0, ' ||
1119 ' -1, ' ||
1120 ' 1) = 1 ';
1121 END IF;
1122
1123 x_insert_clause := 'INSERT INTO MSD_DP_SCN_ENTRIES_DENORM ( ' ||
1124 ' DEMAND_PLAN_ID, ' ||
1125 ' SCENARIO_ID, ' ||
1126 ' DEMAND_ID, ' ||
1127 ' BUCKET_TYPE, ' ||
1128 ' START_TIME, ' ||
1129 ' END_TIME, ' ||
1130 ' SR_INSTANCE_ID, ' ||
1131 ' SR_ORGANIZATION_ID, ' ||
1132 ' SR_INVENTORY_ITEM_ID, ' ||
1133 ' SR_SHIP_TO_LOC_ID, ' ||
1134 ' SR_CUSTOMER_ID, ' ||
1135 ' SR_ZONE_ID, ' ||
1136 ' DEMAND_CLASS, ' ||
1137 ' INVENTORY_ITEM_ID, ' ||
1138 ' DP_UOM_CODE, ' ||
1139 ' ASCP_UOM_CODE, ' ||
1140 ' UNIT_PRICE, ' ||
1141 ' QUANTITY, ' ||
1142 ' ERROR_TYPE, ' ||
1143 ' FORECAST_ERROR, ' ||
1144 ' PRIORITY, ' ||
1145 ' CREATION_DATE, ' ||
1146 ' CREATED_BY, ' ||
1147 ' LAST_UPDATE_LOGIN )';
1148
1149 x_large_sql := x_insert_clause || x_select_clause || x_from_clause || x_where_clause;
1150
1151 /* Delete all data in the denorm for the export data profile */
1152 DELETE FROM MSD_DP_SCN_ENTRIES_DENORM
1153 WHERE demand_plan_id = x_demand_plan_id
1154 AND scenario_id = x_scenario_id;
1155
1156 /* Insert forecast data into denorm table */
1157 EXECUTE IMMEDIATE x_large_sql;
1158
1159 /* Call Custom Hook for Upload */
1160
1161 msd_dem_custom_hooks.upload_hook (
1162 x_errbuf,
1163 x_retcode);
1164
1165 IF (x_retcode = -1)
1166 THEN
1167
1168 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
1169 VS_MSG_LOADED || ' ' || p_export_data_profile || ''' , ''' || VS_MSG_LOADE_ERROR || ''',''' || x_errbuf || ''' ); END;';
1170
1171
1172 EXECUTE IMMEDIATE x_small_sql;
1173
1174 raise_application_error (-20014, 'Error: msd_dem_upload_forecast.upload_forecast - Error in call to custom hook msd_dem_custom_hooks.upload_hook');
1175 END IF;
1176
1177 COMMIT;
1178
1179 msd_dem_collect_history_data.analyze_table (
1180 x_errbuf,
1181 x_retcode,
1182 'MSD_DP_SCN_ENTRIES_DENORM');
1183
1184 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
1185 VS_MSG_LOADED || ' ' || p_export_data_profile || ''' , ''' || VS_MSG_SUCCEEDED || ''' ); END;';
1186
1187
1188 EXECUTE IMMEDIATE x_small_sql;
1189
1190 /* Alter session to demantra schema */
1191 x_small_sql := 'alter session set current_schema = ' || x_schema;
1192 EXECUTE IMMEDIATE x_small_sql;
1193
1194 EXCEPTION
1195 WHEN OTHERS THEN
1196
1197 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
1198 VS_MSG_LOADED || ' ' || p_export_data_profile || ''' , ''' || VS_MSG_LOADE_ERROR || ''',''' || substr(SQLERRM,1,150) || ''' ); END;';
1199
1200
1201 EXECUTE IMMEDIATE x_small_sql;
1202
1203 /* Alter session to demantra schema */
1204 x_small_sql := 'alter session set current_schema = ' || x_schema;
1205 EXECUTE IMMEDIATE x_small_sql;
1206
1207 raise_application_error (-20015, 'Exception: msd_dem_upload_forecast.upload_forecast - ' || substr(SQLERRM,1,150));
1208
1209 END UPLOAD_FORECAST;
1210
1211
1212 END MSD_DEM_UPLOAD_FORECAST;