[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_UPLOAD_FORECAST
Source
1 PACKAGE BODY MSD_DEM_UPLOAD_FORECAST AS
2 /* $Header: msddemufb.pls 120.51.12020000.4 2012/10/18 07:13:50 nallkuma 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 VS_MSG_UPLOAD_PCTG CONSTANT VARCHAR2(30) := 'UPLOAD PLANNING PERCENTAGE';
12 VS_MSG_UPLOAD_PCTG_DRCT CONSTANT VARCHAR2(40) := 'UPLOAD PLANNING PERCENTAGE DIRECT';
13 VS_MSG_UPLOAD_TD CONSTANT VARCHAR2(30) := 'UPLOAD TOTAL DEMAND';
14
15 VS_MSG_LOADING CONSTANT VARCHAR2(8) := 'Loading ';
16 VS_MSG_LOADED CONSTANT VARCHAR2(7) := 'Loaded ';
17 VS_MSG_STARTED CONSTANT VARCHAR2(7) := 'Started';
18 VS_MSG_SUCCEEDED CONSTANT VARCHAR2(9) := 'Succeeded';
19 VS_MSG_LOADE_ERROR CONSTANT VARCHAR2(12) := 'Load error: ';
20 VS_MSG_ITEMS CONSTANT VARCHAR2(12) := 'Items';
21 VS_MSG_LOCATIONS CONSTANT VARCHAR2(12) := 'Locations';
22 VS_MSG_SALES CONSTANT VARCHAR2(12) := 'Sales';
23
24 /*** DATA TYPES ***/
25
26 TYPE REQ_REC IS RECORD (
27 request_id NUMBER,
28 description VARCHAR2(100),
29 is_complete BOOLEAN,
30 status NUMBER);
31
32 TYPE REQ_TABLE IS TABLE OF REQ_REC INDEX BY BINARY_INTEGER;
33
34 /*** GLOBAL VARIABLES ***/
35 g_dblink VARCHAR2(50) DEFAULT NULL;
36 g_collection_method NUMBER DEFAULT NULL;
37 g_req_table REQ_TABLE;
38
39
40
41 /*** PRIVATE FUNCTIONS ***
42 * GET_LEVEL_COLUMN
43 * GET_SERIES_COLUMN
44 */
45
46 /*
47 * This function given the level name gives the level# column for the level
48 * in the data profile
49 */
50 FUNCTION GET_LEVEL_COLUMN (
51 p_data_profile_id IN NUMBER,
52 p_level_name IN VARCHAR2)
53 RETURN VARCHAR2
54 IS
55 x_table_name VARCHAR2(50) := NULL;
56 x_sql VARCHAR2(1000) := NULL;
57
58 x_lorder NUMBER := NULL;
59 x_level_column VARCHAR2(30) := NULL;
60
61 /*
62 * Bug#7199587 - Use Group Table Id instead of the Table Label field
63 * Use the ID obtained from lookups instead of hard-coded one
64 */
65 x_group_table_id NUMBER := NULL;
66 x_level_id_lkup_code VARCHAR2(30) := NULL;
67
68 BEGIN
69
70 /*
71 * Bug#7199587 - Use Group Table Id instead of the Table Label field
72 * Use the ID obtained from lookups instead of hard-coded one
73 */
74 IF (p_level_name = C_ITEM) THEN x_level_id_lkup_code := 'LEVEL_ITEM';
75 ELSIF (p_level_name = C_PRODUCT_FAMILY) THEN x_level_id_lkup_code := 'LEVEL_PRODUCT_FAMILY';
76 ELSIF (p_level_name = C_ORGANIZATION) THEN x_level_id_lkup_code := 'LEVEL_ORGANIZATION';
77 ELSIF (p_level_name = C_SITE) THEN x_level_id_lkup_code := 'LEVEL_SITE';
78 ELSIF (p_level_name = C_CUSTOMER) THEN x_level_id_lkup_code := 'LEVEL_ACCOUNT';
79 ELSIF (p_level_name = C_CUSTOMER_ZONE) THEN x_level_id_lkup_code := 'LEVEL_TRADING_PARTNER_ZONE';
80 ELSIF (p_level_name = C_ZONE) THEN x_level_id_lkup_code := 'LEVEL_ZONE';
81 ELSIF (p_level_name = C_DEMAND_CLASS) THEN x_level_id_lkup_code := 'LEVEL_DEMAND_CLASS';
82 ELSIF (p_level_name = C_PARENT_ITEM) THEN x_level_id_lkup_code := 'LEVEL_PARENT_ITEM';
83 ELSIF (p_level_name = C_ASSET_GROUP) THEN x_level_id_lkup_code := 'LEVEL_ASSET_GROUP';
84 ELSIF (p_level_name = C_CLASS_CODE) THEN x_level_id_lkup_code := 'LEVEL_CLASS_CODE';
85 ELSIF (p_level_name = C_SPF_VISIT_TYPE) THEN x_level_id_lkup_code := 'LEVEL_SPF_VT';
86 ELSIF (p_level_name = C_SPF_VISIT_STAGE_TYPE) THEN x_level_id_lkup_code := 'LEVEL_SPF_VST';
87 ELSIF (p_level_name = C_MASTER_ITEM) THEN x_level_id_lkup_code := 'LEVEL_MASTER_ITEM';
88 ELSIF (p_level_name = C_SPF_MAINTENANCE_TYPE) THEN x_level_id_lkup_code := 'LEVEL_SPF_MT';
89 ELSIF (p_level_name = C_ASSET_GROUP_ATTRIBUTE_2) THEN x_level_id_lkup_code := 'LEVEL_AGA2';
90 ELSIF (p_level_name = C_ASSET_GROUP_ATTRIBUTE_1) THEN x_level_id_lkup_code := 'LEVEL_AGA1';
91 ELSIF (p_level_name = C_ITEM_TYPE) THEN x_level_id_lkup_code := 'LEVEL_ITEM_TYPE';
92 ELSIF (p_level_name = C_CTO_BASE_MODEL) THEN x_level_id_lkup_code := 'LEVEL_CTO_BASE_MODEL';
93 ELSE
94 RETURN NULL;
95 END IF;
96
97 --syenamar Bug#7199587 /* Bug#8224935 - APP ID */ -- nallkuma
98 x_group_table_id := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
99 x_level_id_lkup_code,
100 1,
101 'group_table_id'));
102 /*
103 * Return NULL in case group_table_id is null, i.e. no value fetched from lookups.
104 * In case lookup contains invalid number exception block at end of function handles it and returns NULL.
105 */
106 IF (x_group_table_id IS NULL)
107 THEN
108 RETURN NULL;
109 END IF;
110 --syenamar
111
112 x_table_name := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'TRANSFER_QUERY_LEVELS');
113 x_sql := 'SELECT tql.lorder ' ||
114 ' FROM ' || x_table_name || ' tql, ';
115
116 x_table_name := NULL;
117 x_table_name := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'GROUP_TABLES');
118 x_sql := x_sql || x_table_name || ' gt ' ||
119 ' WHERE gt.group_table_id = ' || x_group_table_id ||
120 ' AND gt.status = ''ACTIVE'' ' ||
121 ' AND gt.group_table_id = tql.level_id ' ||
122 ' AND tql.id = ' || p_data_profile_id;
123
124 EXECUTE IMMEDIATE x_sql INTO x_lorder;
125
126 x_level_column := 'LEVEL' || to_char(x_lorder);
127
128 RETURN upper(x_level_column);
129
130 EXCEPTION
131 WHEN OTHERS THEN
132 RETURN NULL;
133
134 END GET_LEVEL_COLUMN;
135
136
137 /*
138 * This function gets the column for the series in the data profile
139 */
140 FUNCTION GET_SERIES_COLUMN (
141 p_data_profile_id IN NUMBER,
142 p_series_prefix IN VARCHAR2,
143 p_add_prefix IN VARCHAR2 DEFAULT NULL)
144 RETURN VARCHAR2
145 IS
146 x_table_name VARCHAR2(50) := NULL;
147 x_sql VARCHAR2(1000) := NULL;
148
149 x_series_prefix VARCHAR2(50) := NULL;
150 x_ffs VARCHAR2(10) := NULL;
151
152 x_series VARCHAR2(50) := NULL;
153
154
155 BEGIN
156
157 IF (p_series_prefix = 'FCST_')
158 THEN
159 x_series_prefix := p_series_prefix;
160 x_ffs := 'C_PRED';
161 ELSIF (p_series_prefix IN ('PRTY_', 'ACRY_'))
162 THEN
163 x_series_prefix := p_series_prefix;
164 x_ffs := '$$$';
165 ELSIF (p_series_prefix = 'DKEY_')
166 THEN
167 x_series_prefix := p_series_prefix || p_add_prefix;
168 x_ffs := '$$$';
169 ELSE
170 RETURN NULL;
171 END IF;
172
173 x_table_name := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'TRANSFER_QUERY_SERIES');
174 x_sql := 'SELECT cf.computed_name ' ||
175 ' FROM ' || x_table_name || ' tqs, ';
176
177 x_table_name := NULL;
178 x_table_name := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'COMPUTED_FIELDS');
179 x_sql := x_sql || x_table_name || ' cf ' ||
180 ' WHERE tqs.id = ' || p_data_profile_id ||
181 ' AND cf.forecast_type_id = tqs.series_id ' ||
182 ' AND ( upper(cf.computed_name) like ''' || x_series_prefix || '%'' ' ||
183 ' OR upper(cf.computed_name) = ''' || x_ffs || ''') ' ||
184 ' AND rownum < 2 ';
185
186 EXECUTE IMMEDIATE x_sql INTO x_series;
187
188 RETURN upper(x_series);
189
190 EXCEPTION
191 WHEN OTHERS THEN
192 RETURN NULL;
193 END GET_SERIES_COLUMN;
194
195
196
197 /*** PRIVATE PROCEDURES
198 * GET_TIME_STRINGS
199 */
200
201
202 /*
203 */
204 PROCEDURE GET_TIME_STRINGS (
205 p_bucket_type OUT NOCOPY NUMBER,
206 p_start_time OUT NOCOPY VARCHAR2,
207 p_end_time OUT NOCOPY VARCHAR2,
208 p_res_type OUT NOCOPY NUMBER,
209 p_time_from_clause OUT NOCOPY VARCHAR2,
210 p_time_res IN NUMBER,
211 p_ppct_direct_flag IN VARCHAR2 DEFAULT 0 --adding for bug#13393529, flag to indicate call from upload_cto_plng_pct_direct procedure
212 )
213 IS
214
215 x_sql VARCHAR2(1000) := NULL;
216 x_tgroup_res VARCHAR2(50) := NULL;
217 x_dm_wiz_dm_def VARCHAR2(50) := NULL;
218
219 x_tg_res VARCHAR2(100) := NULL;
220 x_months_number NUMBER := NULL;
221 x_inputs_column VARCHAR2(50) := NULL;
222 x_is_default NUMBER := NULL;
223
224 x_dm_time_bucket VARCHAR2(30) := NULL;
225 x_aggregation_method NUMBER(1) := NULL;
226
227 x_is_forward BOOLEAN := NULL;
228
229 x_inputs VARCHAR2(50) := NULL;
230 x_bucket_size NUMBER := NULL;
231
232 BEGIN
233
234 x_tgroup_res := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'TGROUP_RES');
235
236 IF (x_tgroup_res IS NULL) THEN
237 RETURN;
238 END IF;
239
240 /* Get Time Res Info */
241 x_sql :='SELECT tg_res, months_number, inputs_column, is_default ' ||
242 ' FROM ' || x_tgroup_res ||
243 ' WHERE tg_res_id = ' || p_time_res;
244
245 EXECUTE IMMEDIATE x_sql INTO x_tg_res,
246 x_months_number,
247 x_inputs_column,
248 x_is_default;
249
250 x_dm_wiz_dm_def := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'DM_WIZ_DM_DEF');
251 IF (x_dm_wiz_dm_def IS NULL) THEN
252 RETURN;
253 END IF;
254
255 /* Get the data model lowest time level */
256 x_sql :='SELECT time_bucket, aggregation_method ' ||
257 ' FROM ' || x_dm_wiz_dm_def ||
258 ' WHERE dm_or_template = 2 ' ||
259 ' AND is_active = 1 ' ||
260 ' AND rownum < 2 ';
261
262 EXECUTE IMMEDIATE x_sql INTO x_dm_time_bucket,
263 x_aggregation_method;
264
265 /* Get the aggregation type */
266 IF (upper(x_dm_time_bucket) = 'DAY') THEN
267 x_is_forward := FALSE;
268 ELSIF (upper(x_dm_time_bucket) = 'WEEK') THEN
269 IF (x_aggregation_method = 1) THEN
270 x_is_forward := TRUE;
271 ELSE
272 x_is_forward := FALSE;
273 END IF;
274 ELSIF (upper(x_dm_time_bucket) = 'MONTH') THEN
275 x_is_forward := FALSE;
276 ELSE
277 RETURN;
278 END IF;
279
280 --bug#13393529
281 --if flag for p_ppct_direct_flag is true: return offsets for start and end dates in p_start_time and p_end_time respectively, return inputs_column value for p_time_res in p_time_from_clause
282
283 /* Get the time strings */
284 IF (upper(x_dm_time_bucket) = 'DAY') THEN
285 /* Export Time Level = Day */
286 IF (x_is_default = 1) THEN
287 p_bucket_type := C_BUCKET_TYPE_DAY;
288 p_start_time := case when p_ppct_direct_flag = 1 then ' SD ' else ' exp.sdate ' end;
289 p_end_time := case when p_ppct_direct_flag = 1 then ' ED ' else ' exp.sdate ' end;
290 p_res_type := 1;
291
292 RETURN;
293
294 END IF;
295
296 IF (x_months_number IS NOT NULL) THEN
297 IF (x_months_number = 7) THEN
298 p_bucket_type := C_BUCKET_TYPE_WEEK;
299 ELSE
300 p_bucket_type := C_BUCKET_TYPE_MONTH;
301 END IF;
302
303 p_start_time := case when p_ppct_direct_flag = 1 then ' SD ' else ' exp.sdate ' end;
304 p_end_time := case when p_ppct_direct_flag = 1 then ' ED ' else ' exp.sdate ' end || ' + ' || to_char(x_months_number - 1) || ' ';
305 p_res_type := 1;
306
307 RETURN;
308
309 END IF;
310
311 ELSIF (upper(x_dm_time_bucket) = 'WEEK') THEN
312 IF (x_months_number IS NOT NULL) THEN
313 /* Export Time Level = Day */
314 IF (x_is_default = 1) THEN
315 p_bucket_type := C_BUCKET_TYPE_WEEK;
316 ELSE
317 p_bucket_type := C_BUCKET_TYPE_MONTH;
318 END IF;
319
320 IF (x_is_forward) THEN
321 p_start_time := case when p_ppct_direct_flag = 1 then ' SD ' else ' exp.sdate ' end || '- ' || to_char((x_months_number * 7) - 1) || ' ';
322 p_end_time := case when p_ppct_direct_flag = 1 then ' ED ' else ' exp.sdate ' end;
323 ELSE
324 p_start_time := case when p_ppct_direct_flag = 1 then ' SD ' else ' exp.sdate ' end;
325 p_end_time := case when p_ppct_direct_flag = 1 then ' ED ' else ' exp.sdate ' end || '+ ' || to_char((x_months_number * 7) - 1) || ' ';
326 END IF;
327
328 p_res_type := 1;
329 RETURN;
330 END IF;
331
332 ELSIF (upper(x_dm_time_bucket) = 'MONTH') THEN
333 IF (x_is_default = 1) THEN
334 p_bucket_type := C_BUCKET_TYPE_MONTH;
335 p_start_time := case when p_ppct_direct_flag = 1 then ' SD ' else ' exp.sdate ' end;
336 p_end_time := ' round(' || case when p_ppct_direct_flag = 1 then ' ED ' else ' exp.sdate ' end || '+ 16, ''MONTH'') - 1 ';
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) THEN
349 RETURN;
350 END IF;
351
352 IF (p_ppct_direct_flag = 1) THEN
353 p_time_from_clause := x_inputs_column;
354 ELSE
355 p_time_from_clause := ' (SELECT min(datet) start_time, max(datet) end_time ' ||
356 ' FROM ' || x_inputs || ' GROUP BY ' || x_inputs_column ||
357 ' ) inp ';
358 END IF;
359
360 IF (upper(x_dm_time_bucket) = 'DAY') THEN
361 p_res_type := 3;
362 p_start_time := case when p_ppct_direct_flag = 1 then ' SD ' else ' inp.start_time ' end;
363 p_end_time := case when p_ppct_direct_flag = 1 then ' ED ' else ' inp.end_time ' end;
364
365 ELSIF (upper(x_dm_time_bucket) = 'WEEK') THEN
366 IF (x_is_forward) THEN
367 p_res_type := 2;
368 p_start_time := case when p_ppct_direct_flag = 1 then ' SD ' else ' inp.start_time ' end || '- 6 ';
369 p_end_time := case when p_ppct_direct_flag = 1 then ' ED ' else ' inp.end_time ' end;
370 ELSE
371 p_res_type := 3;
372 p_start_time := case when p_ppct_direct_flag = 1 then ' SD ' else ' inp.start_time ' end;
373 p_end_time := case when p_ppct_direct_flag = 1 then ' ED ' else ' inp.end_time ' end || '+ 6 ';
374 END IF;
375 ELSE
376 p_res_type := 3;
377 p_start_time := case when p_ppct_direct_flag = 1 then ' SD ' else ' inp.start_time ' end;
378 p_end_time := ' round(' || case when p_ppct_direct_flag = 1 then ' ED ' else ' inp.end_time ' end || '+ 16, ''MONTH'') - 1 ';
379 END IF;
380
381 /* Bug#14341118 */
382 IF (upper(x_dm_time_bucket) = 'WEEK') THEN
383 x_sql := 'SELECT count(*) FROM ' || x_inputs || ' WHERE ' || x_inputs_column || ' = 1 ';
384 EXECUTE IMMEDIATE x_sql INTO x_bucket_size;
385 IF (x_bucket_size = 1) THEN
386 p_bucket_type := C_BUCKET_TYPE_WEEK;
387 ELSE
388 p_bucket_type := C_BUCKET_TYPE_MONTH;
389 END IF;
390
391 ELSE
392 /* Get the Bucket Type */
393 x_sql := 'SELECT count(*) FROM ' || x_inputs || ' WHERE ' || x_inputs_column || ' = 1 ';
394
395 EXECUTE IMMEDIATE x_sql INTO x_bucket_size;
396
397 IF (x_bucket_size = 7) THEN
398 p_bucket_type := C_BUCKET_TYPE_WEEK;
399 ELSE
400 p_bucket_type := C_BUCKET_TYPE_MONTH;
401 END IF;
402 END IF;
403
404 RETURN;
405
406 EXCEPTION
407 WHEN OTHERS THEN
408 RETURN;
409 END GET_TIME_STRINGS;
410
411
412 /*** PUBLIC FUNCTIONS ***/
413
414 /*
415 * This function returns the sr_instance_id to be used for a global forecast
416 */
417 FUNCTION GET_SR_INSTANCE_ID_FOR_GLOBAL
418 RETURN NUMBER
419 IS
420 CURSOR c_get_sr_instance_id
421 IS
422 SELECT min(instance_id)
423 FROM msc_apps_instances
424 WHERE instance_type <> 3
425 AND validation_org_id IS NOT NULL;
426
427 x_sr_instance_id NUMBER := NULL;
428 BEGIN
429 -- Check the profile MSD_DEM_SR_INSTANCE_FOR_GLOBAL_FCST, use this value if the profile is set
430 x_sr_instance_id := fnd_profile.value('MSD_DEM_SR_INSTANCE_FOR_GLOBAL_FCST');
431
432 -- If the profile is not set find the sr_instance_id using cursor
433 if (x_sr_instance_id is null) then
434 OPEN c_get_sr_instance_id;
435 FETCH c_get_sr_instance_id INTO x_sr_instance_id;
436 CLOSE c_get_sr_instance_id;
437 end if;
438
439 RETURN x_sr_instance_id;
440
441 EXCEPTION
442 WHEN OTHERS THEN
443 RETURN NULL;
444 END GET_SR_INSTANCE_ID_FOR_GLOBAL;
445
446
447
448 /* This function returns 1 if the data profile is fit for upload to ASCP
449 * Current check only includes that a forecast series with internal name
450 * starting 'FCST_' must be present.
451 */
452 FUNCTION IS_VALID_SCENARIO (
453 p_data_profile_id IN NUMBER)
454 RETURN NUMBER
455 IS
456 x_fcst_column VARCHAR2(50) := NULL;
457 BEGIN
458 x_fcst_column := get_series_column (
459 p_data_profile_id,
460 C_FORECAST_SERIES_PREFIX);
461
462 IF (x_fcst_column IS NOT NULL)
463 THEN
464 RETURN 1;
465 END IF;
466
467 RETURN 2;
468
469 EXCEPTION
470 WHEN OTHERS THEN
471 RETURN 2;
472 END IS_VALID_SCENARIO;
473
474
475
476 FUNCTION UPLOAD_TO_CP (
477 p_data_profile_id IN NUMBER)
478 RETURN NUMBER
479 IS
480
481 CURSOR FCST_AT_ITEM(data_profile_id in NUMBER) is
482 select nvl((select 1 from msd_dp_scn_output_levels_v
483 WHERE demand_plan_id = 5555555
484 and scenario_id = data_profile_id + 5555555
485 and level_id = 1),0) from dual;
486
487 CURSOR FCST_AT_GEO(data_profile_id in NUMBER) is
488 select nvl((select 1 from msd_dp_scn_output_levels_v
489 WHERE demand_plan_id = 5555555
490 and scenario_id = data_profile_id + 5555555
491 and level_id = 11),0) from dual;
492
493 CURSOR FCST_AT_ORG(data_profile_id in NUMBER) is
494 select nvl((select 1 from msd_dp_scn_output_levels_v
495 WHERE demand_plan_id = 5555555
496 and scenario_id = data_profile_id + 5555555
497 and level_id = 7),0) from dual;
498
499 fc_item number;
500 fc_geo number;
501 fc_org number;
502
503 ret_value number := 0;
504
505 BEGIN
506
507 OPEN FCST_AT_ITEM(p_data_profile_id);
508 FETCH FCST_AT_ITEM into fc_item;
509 CLOSE FCST_AT_ITEM;
510
511 OPEN FCST_AT_GEO(p_data_profile_id);
512 FETCH FCST_AT_GEO into fc_geo;
513 CLOSE FCST_AT_GEO;
514
515 OPEN FCST_AT_ORG(p_data_profile_id);
516 FETCH FCST_AT_ORG into fc_org;
517 CLOSE FCST_AT_ORG;
518
519 IF ( fc_item = 1 and fc_geo = 1 and fc_org = 1) THEN
520
521 ret_value := 1;
522
523 END IF;
524
525 return ret_value;
526
527 EXCEPTION WHEN OTHERS THEN
528 return 2;
529
530 END UPLOAD_TO_CP;
531
532
533
534
535 /* This function returns -23453 if the data profile contains non-global
536 * forecast, else it returns the id of the source instance for which
537 * global forecasting is being done.
538 */
539 FUNCTION GET_SR_INSTANCE_ID_FOR_PROFILE (
540 p_data_profile_id IN NUMBER)
541 RETURN NUMBER
542 IS
543 x_org_level VARCHAR2(50) := NULL;
544 x_sr_instance_id_for_global NUMBER := NULL;
545 BEGIN
546 x_org_level := get_level_column (
547 p_data_profile_id,
548 C_ORGANIZATION);
549
550 IF (x_org_level IS NULL) /* Global */
551 THEN
552 x_sr_instance_id_for_global := get_sr_instance_id_for_global;
553
554 IF (x_sr_instance_id_for_global IS NOT NULL)
555 THEN
556 RETURN x_sr_instance_id_for_global;
557 ELSE
558 RETURN NULL;
559 END IF;
560
561 END IF;
562
563 /* Local */
564 RETURN -23453;
565
566 EXCEPTION
567 WHEN OTHERS THEN
568 RETURN NULL;
569 END GET_SR_INSTANCE_ID_FOR_PROFILE;
570
571
572
573 /* This function gets the error type 'MAD' or 'MAPE' given the data
574 * profile id
575 */
576 FUNCTION GET_ERROR_TYPE (
577 p_data_profile_id IN NUMBER)
578 RETURN VARCHAR2
579 IS
580 x_error_column VARCHAR2(50) := NULL;
581 x_error_type VARCHAR2(50) := NULL;
582 BEGIN
583 x_error_column := get_series_column (
584 p_data_profile_id,
585 C_FCST_ACRY_SERIES_PREFIX);
586
587 IF (x_error_column IS NULL)
588 THEN
589 RETURN NULL;
590 ELSE
591
592 IF (instr(x_error_column, 'MAD') <> 0)
593 THEN
594 x_error_type := 'MAD';
595 ELSIF (instr(x_error_column, 'MAPE') <> 0)
596 THEN
597 x_error_type := 'MAPE';
598 ELSE
599 RETURN NULL;
600 END IF;
601
602 RETURN x_error_type;
603
604 END IF;
605
606 RETURN NULL;
607
608 EXCEPTION
609 WHEN OTHERS THEN
610 RETURN NULL;
611 END GET_ERROR_TYPE;
612
613
614
615 /* This function return 'Y' if the data profile contains global forecast
616 * else returns 'N'.
617 */
618 FUNCTION IS_GLOBAL_SCENARIO (
619 p_data_profile_id IN NUMBER)
620 RETURN VARCHAR2
621 IS
622 x_org_level VARCHAR2(50) := NULL;
623 BEGIN
624 x_org_level := get_level_column (
625 p_data_profile_id,
626 C_ORGANIZATION);
627
628 IF (x_org_level IS NULL) /* Global */
629 THEN
630 RETURN 'Y';
631 END IF;
632
633 RETURN 'N';
634
635 EXCEPTION
636 WHEN OTHERS THEN
637 RETURN 'N';
638 END IS_GLOBAL_SCENARIO;
639
640
641
642 /* This function returns the source key of the customer, given the customer
643 * zone
644 */
645 FUNCTION GET_CUSTOMER_FROM_TPZONE (
646 p_tp_zone IN VARCHAR2,
647 p_sr_instance_id IN NUMBER)
648 RETURN NUMBER
649 IS
650
651 x_sr_customer_pk NUMBER := NULL;
652 x_account_number VARCHAR2(255) := NULL;
653
654 BEGIN
655
656 IF (msd_dem_common_utilities.is_use_new_site_format = 0)
657 THEN
658 x_account_number := to_char(substr (p_tp_zone,
659 instr(p_tp_zone, ':', 1) + 1,
660 instr(p_tp_zone, ':', 1, 2) - instr(p_tp_zone, ':', 1) - 1));
661 ELSE
662 x_account_number := to_char(substr (p_tp_zone,
663 instr(p_tp_zone, '::', 1) + 2,
664 instr(p_tp_zone, '::', 1, 2) - instr(p_tp_zone, '::', 1) - 2));
665 END IF;
666
667 IF (x_account_number IS NOT NULL)
668 THEN
669
670 SELECT mtil.sr_tp_id
671 INTO x_sr_customer_pk
672 FROM
673 msc_tp_id_lid mtil
674 WHERE
675 mtil.sr_cust_account_number = x_account_number
676 and mtil.sr_instance_id = p_sr_instance_id;
677
678 END IF;
679
680 RETURN x_sr_customer_pk;
681
682 EXCEPTION
683 WHEN OTHERS THEN
684 RETURN NULL;
685 END GET_CUSTOMER_FROM_TPZONE;
686
687
688
689
690 /* This function returns the source key of the zone, given the customer zone
691 */
692 FUNCTION GET_ZONE_FROM_TPZONE (
693 p_tp_zone IN VARCHAR2,
694 p_sr_instance_id IN NUMBER)
695 RETURN NUMBER
696 IS
697
698 x_zone VARCHAR2(255) := NULL;
699 x_sr_zone_pk NUMBER := NULL;
700
701 BEGIN
702
703 IF (msd_dem_common_utilities.is_use_new_site_format = 0)
704 THEN
705 x_zone := substr (p_tp_zone,
706 instr(p_tp_zone, ':', 1, 2) + 1);
707 ELSE
708 x_zone := substr (p_tp_zone,
709 instr(p_tp_zone, '::', 1, 2) + 2);
710 END IF;
711
712 IF (x_zone IS NOT NULL)
713 THEN
714 SELECT mr.region_id
715 INTO x_sr_zone_pk
716 FROM msc_regions mr
717 WHERE
718 mr.zone = x_zone
719 AND mr.sr_instance_id = p_sr_instance_id;
720 END IF;
721
722 RETURN x_sr_zone_pk;
723
724 EXCEPTION
725 WHEN OTHERS THEN
726 RETURN NULL;
727 END GET_ZONE_FROM_TPZONE;
728
729
730 /*** PUBLIC PROCEDURES ***/
731
732 /*
733 * This procedure, given the export integration data profile name, pushes the
734 * forecast data along with forecast accuracy and demand priority from the
735 * export view to table MSD_DP_SCN_ENTRIES_DENORM. The member codes are
736 * transformed to the corresponding source identifiers. The 'Organization'
737 * level member is used to find out the source instance to which the record
738 * belongs.
739 * The internal names of the series will be used to get the semantic of the
740 * series. They are as follows -
741 * 1. Forecast Series - The internal name should start with 'FCST_'
742 * 2. Demand Priority Series - The internal name should start with 'PRTY_'
743 * 3. Forecast Accuracy Series - The internal name should start with 'ACRY_'
744 * 4. Destination Key Series - The internal name should start with 'DKEY_'
745 */
746 PROCEDURE UPLOAD_FORECAST (
747 p_export_data_profile IN VARCHAR2,
748 p_ind_fcst_series_iname IN VARCHAR2 DEFAULT NULL,
749 p_dep_fcst_series_iname IN VARCHAR2 DEFAULT NULL,
750 p_for_spf IN VARCHAR2 DEFAULT 2,
751 p_acry_series_iname IN VARCHAR2 DEFAULT NULL,
752 p_upload_unplanned_components IN VARCHAR2 DEFAULT 0 --adding for bug#13393529, upload ind fcst for unplanned items with pick_components_flag = 'Y'
753 )
754 IS
755
756 TYPE CUR_TYPE IS REF CURSOR;
757 x_cur_type CUR_TYPE;
758
759 x_errbuf VARCHAR2(200) := NULL;
760 x_retcode VARCHAR2(100) := NULL;
761
762 x_sql VARCHAR2(2000) := NULL;
763 x_table_name VARCHAR2(50) := NULL;
764 x_schema VARCHAR(50) := NULL;
765
766 x_profile_id NUMBER := NULL;
767 x_export_data_profile VARCHAR2(255) := NULL;
768 x_presentation_type NUMBER := NULL;
769 x_view_name VARCHAR2(30) := NULL;
770 x_time_res_id NUMBER := NULL;
771 x_unit_id NUMBER := NULL;
772 x_index_id NUMBER := NULL;
773 x_data_scale NUMBER := NULL;
774 x_integration_type NUMBER := NULL;
775 x_export_type NUMBER := NULL;
776 x_last_export_date DATE := NULL;
777 x_is_view_present NUMBER := 0;
778
779 x_dm_time_bucket VARCHAR2(30) := NULL;
780 x_aggregation_method NUMBER(1) := NULL;
781
782 x_demand_plan_id NUMBER := NULL;
783 x_scenario_id NUMBER := NULL;
784 x_demand_id_offset NUMBER := NULL;
785 x_bucket_type NUMBER := NULL;
786 x_start_time VARCHAR2(100) := NULL;
787 x_end_time VARCHAR2(100) := NULL;
788 x_sr_organization_id VARCHAR2(50) := NULL;
789 x_sr_ship_to_loc_id VARCHAR2(50) := NULL;
790 x_sr_customer_id VARCHAR2(100) := NULL;
791 x_sr_zone_id VARCHAR2(100) := NULL;
792 x_sr_demand_class VARCHAR(50) := NULL;
793 x_uom_code VARCHAR2(100) := NULL;
794 x_quantity VARCHAR2(500) := NULL;
795 x_fcst_column VARCHAR2(200) := NULL;
796 x_error_type VARCHAR2(50) := NULL;
797 x_error_column VARCHAR2(50) := NULL;
798 x_error_column_alias VARCHAR2(50) := NULL;
799 x_demand_priority_column VARCHAR2(50) := NULL;
800
801 x_select_clause VARCHAR2(3000) := NULL;
802 x_from_clause VARCHAR2(500) := NULL;
803 x_where_clause VARCHAR2(3000) := NULL;
804 x_insert_clause VARCHAR2(1000) := NULL;
805 x_small_sql VARCHAR2(600) := NULL;
806 x_large_sql VARCHAR2(6000) := NULL;
807 x_inner_view VARCHAR2(1000) := NULL;
808 x_iv_group_by VARCHAR2(1000) := NULL;
809
810 x_is_global_fcst NUMBER(1) := NULL;
811
812 x_org_level VARCHAR2(30) := NULL;
813 x_prd_level VARCHAR2(30) := NULL;
814 x_ship_to_level VARCHAR2(30) := NULL;
815 x_cust_level VARCHAR2(30) := NULL;
816 x_zone_level VARCHAR2(30) := NULL;
817 x_cust_zone_level VARCHAR2(30) := NULL;
818 x_demand_class_level VARCHAR2(30) := NULL;
819
820 x_org_key_column VARCHAR2(30) := NULL;
821 x_prd_key_column VARCHAR2(30) := NULL;
822 x_final_prd_column VARCHAR2(30) := NULL;
823 x_ship_to_key_column VARCHAR2(30) := NULL;
824
825 x_sr_instance_id_for_global NUMBER := NULL;
826
827 x_res_type NUMBER := NULL;
828 x_time_from_clause VARCHAR2(500) := NULL;
829 x_null_value_code VARCHAR2(50); /* bug#14341118 */
830
831 /* sjagathe - Added for Product Family Forecast Support */
832 x_is_pf_level VARCHAR2(30) := NULL;
833 x_num_rows NUMBER := 0;
834
835 x_boolean BOOLEAN;
836 x_dummy1 VARCHAR2(100);
837 x_dummy2 VARCHAR2(100);
838 x_msc_schema_name VARCHAR2(50);
839
840 BEGIN
841
842 /* Alter session to APPS */
843 x_small_sql := 'alter session set current_schema = APPS';
844 EXECUTE IMMEDIATE x_small_sql;
845
846
847 x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
848 IF (x_schema IS NULL)
849 THEN
850 raise_application_error (-20001, 'Error: msd_dem_upload_forecast.upload_forecast - Unable to find schema name');
851 END IF;
852
853
854 x_null_value_code := msd_dem_sr_util.get_null_code;
855
856 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
857 VS_MSG_LOADING || ' ' || p_export_data_profile || ''' , ''' || VS_MSG_STARTED || ''' ); END;';
858
859 EXECUTE IMMEDIATE x_small_sql;
860
861 /* Initialize global variables */
862 IF (p_export_data_profile IS NULL)
863 THEN
864 raise_application_error (-20002, 'Error: msd_dem_upload_forecast.upload_forecast - No export data profile name provided');
865 ELSE
866 x_export_data_profile := upper(p_export_data_profile);
867 END IF;
868
869 /* Get the export data profile info */
870 x_table_name := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'TRANSFER_QUERY');
871 x_sql := 'SELECT id, presentation_type, view_name, ' ||
872 ' time_res_id, unit_id, index_id, data_scale, ' ||
873 ' integration_type, export_type, last_export_date ' ||
874 ' FROM ' || x_table_name ||
875 ' WHERE upper(query_name) = ''' || x_export_data_profile || '''';
876
877 OPEN x_cur_type FOR x_sql;
878 FETCH x_cur_type INTO x_profile_id,
879 x_presentation_type,
880 x_view_name,
881 x_time_res_id,
882 x_unit_id,
883 x_index_id,
884 x_data_scale,
885 x_integration_type,
886 x_export_type,
887 x_last_export_date;
888 CLOSE x_cur_type;
889
890 /* Bug# 6326524 */
891 x_sql := 'SELECT count(1) FROM dba_objects ' ||
892 ' WHERE owner = upper(''' || x_schema || ''')' ||
893 ' AND object_type IN (''VIEW'', ''MATERIALIZED VIEW'') ' ||
894 ' AND object_name = upper(''' || x_view_name || ''')';
895 EXECUTE IMMEDIATE x_sql INTO x_is_view_present;
896
897
898 /*** Check basic error conditions - BEGIN ***/
899
900 IF (x_profile_id IS NULL)
901 THEN
902 raise_application_error (-20003, 'Error: msd_dem_upload_forecast.upload_forecast - Unable to get export data profile id');
903 ELSIF (x_integration_type = C_IMPORT_DATA_PROFILE)
904 THEN
905 raise_application_error (-20004, 'Error: msd_dem_upload_forecast.upload_forecast - ' || p_export_data_profile || 'is not an export data profile');
906 ELSIF (x_export_type = C_EXPORT_TYPE_INCR)
907 THEN
908 raise_application_error (-20005, 'Error: msd_dem_upload_forecast.upload_forecast - Incremental export type is not supported');
909 ELSIF (x_index_id IS NOT NULL)
910 THEN
911 raise_application_error (-20006, 'Error: msd_dem_upload_forecast.upload_forecast - Forecast amount cannot be uploaded');
912 ELSIF (x_is_view_present = 0)
913 THEN
914 raise_application_error (-20007, 'Error: msd_dem_upload_forecast.upload_forecast - Forecast has not yet been exported');
915 ELSIF (x_presentation_type = C_PSNT_TYPE_DESC)
916 THEN
917 raise_application_error (-20008, 'Error: msd_dem_upload_forecast.upload_forecast - Presentation type must by Code');
918 END IF;
919
920 /*** Check basic error conditions - END ***/
921
922 x_demand_plan_id := C_DEMAND_PLAN_ID;
923 x_scenario_id := x_profile_id + C_SCENARIO_ID_OFFSET;
924
925 x_select_clause := ' SELECT ' || x_demand_plan_id || ' , ' ||
926 x_scenario_id || ' , ' ||
927 ' rownum - 1 , ';
928
929 /*** Get Time Info - BEGIN ***/
930
931 get_time_strings (
932 x_bucket_type,
933 x_start_time,
934 x_end_time,
935 x_res_type,
936 x_time_from_clause,
937 x_time_res_id);
938
939 IF (x_res_type IS NULL)
940 THEN
941 raise_application_error (-20009, 'Error: msd_dem_upload_forecast.upload_forecast - Unable to find schema name');
942 END IF;
943
944 /*** Get Time Info - END ***/
945
946 x_select_clause := x_select_clause || x_bucket_type || ' , '
947 || x_start_time || ' , '
948 || x_end_time || ' , ';
949
950 x_from_clause := ' FROM ' || x_schema || '.' || x_view_name || ' exp, ' ||
951 ' msc_system_items msi, ';
952
953 /* Get the levels at which forecast has been exported
954 * Expected Levels -
955 * 1. Item AND/OR Product Family
956 * 2. (Site/Customer/Customer Zone/Zone) AND/OR (Ship From dimension levels)
957 * 3. Demand Class (Not Mandatory)
958 */
959
960 /* PRODUCT */
961 x_prd_level := get_level_column (x_profile_id, C_ITEM);
962 IF (x_prd_level IS NULL)
963 THEN
964 x_prd_level := get_level_column (x_profile_id, C_PRODUCT_FAMILY);
965
966 IF (x_prd_level IS NULL)
967 THEN
968 raise_application_error (-20010, 'Error: msd_dem_upload_forecast.upload_forecast - Item or Product Family level is required for upload');
969 END IF;
970 ELSE
971 x_prd_key_column := get_series_column (x_profile_id, C_DKEY_SERIES_PREFIX, C_DKEY_ITEM);
972
973 /* sjagathe - Added for Product Family Forecast Support */
974 x_is_pf_level := get_level_column (x_profile_id, C_PRODUCT_FAMILY);
975
976 END IF;
977
978 x_select_clause := x_select_clause || ' msi.sr_instance_id, ';
979
980 /* ORGANIZATION */
981 x_org_level := get_level_column (x_profile_id, C_ORGANIZATION);
982 IF (x_org_level IS NULL) /* global */
983 THEN
984 x_is_global_fcst := 1;
985 x_sr_organization_id := '-1';
986
987 x_sr_instance_id_for_global := get_sr_instance_id_for_global;
988 IF (x_sr_instance_id_for_global IS NULL)
989 THEN
990 raise_application_error (-20011, 'Error: msd_dem_upload_forecast.upload_forecast - Unable to get sr_instance_id for global forecast');
991 END IF;
992
993 ELSE
994 x_is_global_fcst := 2;
995 x_sr_organization_id := ' msi.organization_id ';
996
997 END IF;
998
999 x_select_clause := x_select_clause || x_sr_organization_id || ' , ' ||
1000 ' msi.sr_inventory_item_id, ';
1001
1002 IF (x_is_global_fcst = 2)
1003 THEN
1004 x_from_clause := x_from_clause || ' msc_trading_partners mtp_org, ';
1005 END IF;
1006
1007 /* GEOGRAPHY */
1008
1009 x_sr_ship_to_loc_id := ' NULL ';
1010 x_sr_customer_id := ' NULL ';
1011 x_sr_zone_id := ' NULL ';
1012
1013 x_ship_to_level := get_level_column (x_profile_id, C_SITE);
1014 IF (x_ship_to_level IS NOT NULL)
1015 THEN
1016 x_sr_ship_to_loc_id := ' mtpsil.sr_tp_site_id ';
1017 x_from_clause := x_from_clause || ' msc_tp_site_id_lid mtpsil, ';
1018 x_ship_to_key_column := get_series_column (x_profile_id, C_DKEY_SERIES_PREFIX, C_DKEY_SITE);
1019
1020 IF (x_ship_to_key_column IS NULL)
1021 THEN
1022 raise_application_error (-20013, 'Error: msd_dem_upload_forecast.upload_forecast - Destination key series for the level Site not found');
1023 END IF;
1024
1025 END IF;
1026
1027 x_cust_level := get_level_column (x_profile_id, C_CUSTOMER);
1028 x_cust_zone_level := get_level_column (x_profile_id, C_CUSTOMER_ZONE);
1029 IF (x_cust_level IS NOT NULL)
1030 THEN
1031 x_sr_customer_id := ' mtil.sr_tp_id ';
1032 x_from_clause := x_from_clause || ' msc_tp_id_lid mtil, ';
1033 ELSIF (x_ship_to_level IS NOT NULL)
1034 THEN
1035 x_sr_customer_id := ' mtpsil.sr_cust_acct_id ';
1036 ELSIF (x_cust_zone_level IS NOT NULL)
1037 THEN
1038 x_sr_customer_id := ' msd_dem_upload_forecast.get_customer_from_tpzone ( exp.' || x_cust_zone_level || ', mai.instance_id ) ';
1039 END IF;
1040
1041 x_zone_level := get_level_column (x_profile_id, C_ZONE);
1042 IF (x_zone_level IS NOT NULL)
1043 THEN
1044 x_sr_zone_id := ' mr.region_id ';
1045 x_from_clause := x_from_clause || ' msc_regions mr, ';
1046 ELSIF (x_cust_zone_level IS NOT NULL)
1047 THEN
1048 x_sr_zone_id := ' msd_dem_upload_forecast.get_zone_from_tpzone ( exp.' || x_cust_zone_level || ', mai.instance_id ) ';
1049 END IF;
1050
1051 x_select_clause := x_select_clause || x_sr_ship_to_loc_id || ' , ' ||
1052 x_sr_customer_id || ' , ' ||
1053 x_sr_zone_id || ' , ';
1054
1055 /* DEMAND CLASS */
1056 x_demand_class_level := get_level_column (x_profile_id, C_DEMAND_CLASS);
1057 IF (x_demand_class_level IS NULL)
1058 THEN
1059 x_sr_demand_class := ' NULL ';
1060 ELSE
1061 x_sr_demand_class := ' mdc.demand_class ';
1062 x_from_clause := x_from_clause || ' msc_demand_classes mdc, ';
1063 END IF;
1064
1065 IF (x_res_type = 1)
1066 THEN
1067 x_from_clause := x_from_clause || ' msc_apps_instances mai ';
1068 ELSE
1069 x_from_clause := x_from_clause || ' msc_apps_instances mai, ' || x_time_from_clause;
1070 END IF;
1071
1072 x_select_clause := x_select_clause || x_sr_demand_class || ' , ' ||
1073 ' msi.inventory_item_id, ';
1074
1075
1076 x_uom_code := msd_dem_common_utilities.get_uom_code (x_unit_id);
1077 x_select_clause := x_select_clause || '''' || x_uom_code || ''', ' ||
1078 ' msi.uom_code, ';
1079
1080 /* SINCE AMOUNT IS NOT AVAILABLE USE ASCP's LIST PRICE VALUE */
1081 x_select_clause := x_select_clause || ' msi.list_price * ((100 - msi.average_discount)/100), ';
1082
1083 /* FORECAST SERIES */
1084 IF ( p_ind_fcst_series_iname IS NULL
1085 AND p_dep_fcst_series_iname IS NULL)
1086 THEN
1087 x_fcst_column := get_series_column (x_profile_id, C_FORECAST_SERIES_PREFIX);
1088
1089 IF (x_fcst_column IS NULL)
1090 THEN
1091 raise_application_error (-20014, 'Error: msd_dem_upload_forecast.upload_forecast - Forecast series not found');
1092 END IF;
1093
1094 x_fcst_column := 'exp.' || x_fcst_column;
1095
1096 ELSE
1097
1098 IF ( p_ind_fcst_series_iname IS NOT NULL
1099 AND p_dep_fcst_series_iname IS NOT NULL)
1100 THEN
1101 x_fcst_column := '( nvl(exp.' || p_ind_fcst_series_iname || ',0) + nvl(exp.' || p_dep_fcst_series_iname || ',0) * decode( nvl (msi.ato_forecast_control, 3), 3, 0, 1 ) )';
1102 ELSIF (p_ind_fcst_series_iname IS NOT NULL)
1103 THEN
1104 x_fcst_column := '( nvl(exp.' || p_ind_fcst_series_iname || ',0))';
1105 ELSE
1106 x_fcst_column := '( nvl(exp.' || p_dep_fcst_series_iname || ',0) * decode( nvl (msi.ato_forecast_control, 3), 3, 0, 1 ) )';
1107 END IF;
1108
1109 END IF;
1110
1111
1112 IF (x_unit_id = 1 OR upper(x_uom_code) = 'UNITS')
1113 THEN
1114 x_quantity := ' round (' || x_fcst_column || ' * ' || x_data_scale || ', ' || C_ROUNDOFF_PLACES || ' ) ';
1115 ELSE
1116 x_quantity := ' round (' || x_fcst_column ||
1117 ' * ' || x_data_scale ||
1118 ' * decode ( ''' || x_uom_code || ''', msi.uom_code, 1, ' ||
1119 ' msd_dem_common_utilities.uom_convert(msi.inventory_item_id, ' ||
1120 ' null, ' ||
1121 '''' || x_uom_code || ''' , ' ||
1122 ' msi.uom_code)), ' ||
1123 C_ROUNDOFF_PLACES || ' ) ';
1124 END IF;
1125
1126 x_select_clause := x_select_clause || x_quantity || ' , ';
1127
1128 /* FORECAST ACCURACY */
1129 IF (p_acry_series_iname IS NULL)
1130 THEN
1131 x_error_column := get_series_column (x_profile_id, C_FCST_ACRY_SERIES_PREFIX);
1132 ELSE
1133 x_error_column := p_acry_series_iname;
1134 END IF;
1135 x_error_column_alias := x_error_column;
1136
1137 IF (x_error_column IS NULL)
1138 THEN
1139 x_select_clause := x_select_clause || ' NULL , NULL , ';
1140 ELSE
1141 IF (instr(x_error_column, 'MAD') = 0)
1142 THEN
1143 x_error_type := 'MAPE';
1144 -- bug#9734502 -- nallkuma (bug#9025110-12.1)
1145 x_error_column := x_error_column || '*100';
1146
1147 ELSE
1148 x_error_type := 'MAD';
1149 END IF;
1150 x_select_clause := x_select_clause || '''' || x_error_type || ''' , exp.' || x_error_column || ' , ';
1151 END IF;
1152
1153 /* DEMAND PRIORITY SERIES */
1154 x_demand_priority_column := get_series_column (x_profile_id, C_DEMAND_PRTY_SERIES_PREFIX);
1155
1156 IF (x_demand_priority_column IS NULL)
1157 THEN
1158 x_select_clause := x_select_clause || ' NULL , ';
1159 ELSE
1160 x_select_clause := x_select_clause || ' exp.' || x_demand_priority_column || ' , ';
1161 END IF;
1162
1163 /* sjagathe - Added for Product Family Forecast Support */
1164 IF (x_is_pf_level IS NULL)
1165 THEN
1166 x_select_clause := x_select_clause || ' NULL , ';
1167 x_select_clause := x_select_clause || ' NULL , ';
1168 ELSE
1169 x_select_clause := x_select_clause || ' exp.' || x_is_pf_level || ' , ';
1170 x_select_clause := x_select_clause || ' nvl (msi.ato_forecast_control, 3) , ';
1171 END IF;
1172
1173 /* sjagathe - Added for SPF Upload Forecast and Metrics */
1174 IF (p_for_spf = 2)
1175 THEN
1176 x_select_clause := x_select_clause || ' NULL, NULL, NULL, NULL, ';
1177 ELSE
1178 x_select_clause := x_select_clause || ' exp.acry_mape_spf_insamp * 100, ';
1179 x_select_clause := x_select_clause || ' exp.acry_mape_spf_outsamp * 100, ';
1180 x_select_clause := x_select_clause || ' exp.spf_fore_vol * 100, ';
1181 x_select_clause := x_select_clause || ' exp.spf_glob_prop, ';
1182 END IF;
1183
1184 x_select_clause := x_select_clause || ' sysdate, ' ||
1185 ' FND_GLOBAL.USER_ID, ' ||
1186 ' FND_GLOBAL.LOGIN_ID ';
1187
1188 /* BUILD WHERE CLAUSE */
1189 IF (x_is_global_fcst = 2)
1190 THEN
1191
1192 x_where_clause := ' WHERE mtp_org.partner_type = 3 ' ||
1193 ' AND exp.' || x_org_level || ' = mtp_org.organization_code ' ||
1194 ' AND msi.plan_id = -1 ' ||
1195 ' AND msi.sr_instance_id = mtp_org.sr_instance_id ' ||
1196 ' AND msi.organization_id = mtp_org.sr_tp_id ';
1197 ELSE
1198
1199 x_where_clause := ' WHERE msi.plan_id = -1 ' ||
1200 ' AND msi.sr_instance_id = ' || to_char(x_sr_instance_id_for_global) ||
1201 ' AND msi.organization_id = mai.validation_org_id ';
1202 END IF;
1203
1204 IF (x_prd_key_column IS NOT NULL)
1205 THEN
1206 x_where_clause := x_where_clause ||
1207 ' AND msi.inventory_item_id = exp.' || x_prd_key_column || ' ';
1208 ELSE
1209 x_where_clause := x_where_clause ||
1210 ' AND msi.item_name = exp.' || x_prd_level || ' ';
1211 END IF;
1212
1213 x_where_clause := x_where_clause ||
1214 ' AND msi.sr_instance_id = mai.instance_id ' ||
1215 ' AND ( msi.mrp_planning_code <> 6 ' ||
1216 case when p_upload_unplanned_components = 1 then
1217 ' OR (msi.mrp_planning_code = 6 and msi.pick_components_flag = ''Y'')' else '' end ||
1218 ' )';
1219
1220 /* Independent Forecast for options with forecast control none should be exported.
1221 IF (x_is_pf_level IS NULL) THEN
1222
1223 x_where_clause := x_where_clause || ' AND msi.ato_forecast_control <> 3 ';
1224
1225 END IF;
1226 */
1227
1228 /* Bug# 5765391 - Upload forecast for 'Unassociated' geo dimension members also */
1229 /* Bug#14341118 replaced msd_dem_sr_util.get_null_code with x_null_value_code*/
1230
1231 IF (x_ship_to_level IS NOT NULL)
1232 THEN
1233 x_where_clause := x_where_clause ||
1234 ' AND mtpsil.tp_site_id (+) = exp.' || x_ship_to_key_column || ' ' ||
1235 ' AND decode (mtpsil.sr_instance_id, null, decode (exp.' || x_ship_to_key_column || ' , null, 1, 0), mai.instance_id, 1, 0) = 1 ';
1236 END IF;
1237
1238 IF (x_cust_level IS NOT NULL)
1239 THEN
1240 IF (msd_dem_common_utilities.is_use_new_site_format <> 0)
1241 THEN
1242 x_where_clause := x_where_clause ||
1243 ' AND mtil.sr_cust_account_number (+) = to_char(substr(exp.' || x_cust_level || ',instr(exp.' || x_cust_level || ', ''::'', -1) + 2)) ' ||
1244 ' AND mtil.partner_type (+) = 2 ' ||
1245 ' AND decode (mtil.sr_instance_id, null, decode (exp.' || x_cust_level || ' , ''' || x_null_value_code || ''', 1, 0), mai.instance_id, 1, 0) = 1 ';
1246 ELSE
1247 x_where_clause := x_where_clause ||
1248 ' AND mtil.sr_cust_account_number (+) = to_char(substr(exp.' || x_cust_level || ',instr(exp.' || x_cust_level || ', '':'', -1) + 1)) ' ||
1249 ' AND mtil.partner_type (+) = 2 ' ||
1250 ' AND decode (mtil.sr_instance_id, null, decode (exp.' || x_cust_level || ' , ''' || x_null_value_code || ''', 1, 0), mai.instance_id, 1, 0) = 1 ';
1251 END IF;
1252 END IF;
1253
1254 IF (x_zone_level IS NOT NULL)
1255 THEN
1256 x_where_clause := x_where_clause ||
1257 ' AND mr.zone (+) = exp.' || x_zone_level || ' ' ||
1258 ' AND decode (mr.sr_instance_id, null, decode (exp.' || x_zone_level || ' , ''' || x_null_value_code || ''', 1, 0), mai.instance_id, 1, 0) = 1 ';
1259 END IF;
1260
1261 IF (x_demand_class_level IS NOT NULL)
1262 THEN
1263 x_where_clause := x_where_clause ||
1264 ' AND mdc.meaning (+) = exp.' || x_demand_class_level || ' ' ||
1265 ' AND decode (mdc.sr_instance_id, null, decode (exp.' || x_demand_class_level || ' , ''' || x_null_value_code || ''', 1, 0), mai.instance_id, 1, 0) = 1 ';
1266 END IF;
1267
1268 IF (x_res_type = 2)
1269 THEN
1270 x_where_clause := x_where_clause ||
1271 ' AND exp.sdate = inp.end_time ';
1272 ELSIF (x_res_type = 3)
1273 THEN
1274 x_where_clause := x_where_clause ||
1275 ' AND exp.sdate = inp.start_time ';
1276 END IF;
1277
1278
1279 /* Upload ZERO forecast quantity only if MAD forecast error is NON-ZERO */
1280 IF (x_error_column IS NOT NULL AND x_error_type = 'MAD')
1281 THEN
1282
1283 x_where_clause := x_where_clause ||
1284 ' AND decode ( ' || x_fcst_column || ' , 0 , ' ||
1285 ' decode ( nvl( exp.' || x_error_column || ' , 0) , 0, ' ||
1286 ' -1, ' ||
1287 ' 1), ' ||
1288 ' 1) = 1 ';
1289 ELSE
1290 x_where_clause := x_where_clause ||
1291 ' AND decode ( nvl( ' || x_fcst_column || ' , 0), 0, ' ||
1292 ' -1, ' ||
1293 ' 1) = 1 ';
1294 END IF;
1295
1296 x_insert_clause := 'INSERT INTO MSD_DP_SCN_ENTRIES_DENORM ( ' ||
1297 ' DEMAND_PLAN_ID, ' ||
1298 ' SCENARIO_ID, ' ||
1299 ' DEMAND_ID, ' ||
1300 ' BUCKET_TYPE, ' ||
1301 ' START_TIME, ' ||
1302 ' END_TIME, ' ||
1303 ' SR_INSTANCE_ID, ' ||
1304 ' SR_ORGANIZATION_ID, ' ||
1305 ' SR_INVENTORY_ITEM_ID, ' ||
1306 ' SR_SHIP_TO_LOC_ID, ' ||
1307 ' SR_CUSTOMER_ID, ' ||
1308 ' SR_ZONE_ID, ' ||
1309 ' DEMAND_CLASS, ' ||
1310 ' INVENTORY_ITEM_ID, ' ||
1311 ' DP_UOM_CODE, ' ||
1312 ' ASCP_UOM_CODE, ' ||
1313 ' UNIT_PRICE, ' ||
1314 ' QUANTITY, ' ||
1315 ' ERROR_TYPE, ' ||
1316 ' FORECAST_ERROR, ' ||
1317 ' PRIORITY, ' ||
1318 ' PF_NAME, ' || /* sjagathe - Added for Product Family Forecast Support */
1319 ' REQUEST_ID, ' || /* sjagathe - Added for Product Family Forecast Support */
1320 ' MAPE_IN_SAMPLE, ' || /* sjagathe - Added for SPF Upload Forecast and Metrics */
1321 ' MAPE_OUT_SAMPLE, ' || /* sjagathe - Added for SPF Upload Forecast and Metrics */
1322 ' FORECAST_VOLATILITY, ' || /* sjagathe - Added for SPF Upload Forecast and Metrics */
1323 ' AVG_DEMAND, ' || /* sjagathe - Added for SPF Upload Forecast and Metrics */
1324 ' CREATION_DATE, ' ||
1325 ' CREATED_BY, ' ||
1326 ' LAST_UPDATE_LOGIN )';
1327
1328 x_large_sql := x_insert_clause || x_select_clause || x_from_clause || x_where_clause;
1329
1330 IF (p_dep_fcst_series_iname IS NOT NULL)
1331 THEN
1332
1333 x_inner_view := '(SELECT SDATE, '
1334 || x_prd_level;
1335
1336 IF (x_prd_key_column IS NOT NULL)
1337 THEN
1338 x_inner_view := x_inner_view || ' , ' || x_prd_key_column;
1339 x_iv_group_by := ' , ' || x_prd_key_column;
1340 END IF;
1341
1342 IF (x_is_global_fcst = 2)
1343 THEN
1344 x_inner_view := x_inner_view || ' , ' || x_org_level;
1345 x_iv_group_by := x_iv_group_by || ' , ' || x_org_level;
1346 END IF;
1347
1348 IF (x_ship_to_level IS NOT NULL)
1349 THEN
1350 x_inner_view := x_inner_view || ' , ' || x_ship_to_level
1351 || ' , ' || x_ship_to_key_column;
1352 x_iv_group_by := x_iv_group_by || ' , ' || x_ship_to_level
1353 || ' , ' || x_ship_to_key_column;
1354 END IF;
1355
1356 IF (x_cust_level IS NOT NULL)
1357 THEN
1358 x_inner_view := x_inner_view || ' , ' || x_cust_level;
1359 x_iv_group_by := x_iv_group_by || ' , ' || x_cust_level;
1360 END IF;
1361
1362 IF (x_cust_zone_level IS NOT NULL)
1363 THEN
1364 x_inner_view := x_inner_view || ' , ' || x_cust_zone_level;
1365 x_iv_group_by := x_iv_group_by || ' , ' || x_cust_zone_level;
1366 END IF;
1367
1368 IF (x_zone_level IS NOT NULL)
1369 THEN
1370 x_inner_view := x_inner_view || ' , ' || x_zone_level;
1371 x_iv_group_by := x_iv_group_by || ' , ' || x_zone_level;
1372 END IF;
1373
1374 IF (x_demand_class_level IS NOT NULL)
1375 THEN
1376 x_inner_view := x_inner_view || ' , ' || x_demand_class_level;
1377 x_iv_group_by := x_iv_group_by || ' , ' || x_demand_class_level;
1378 END IF;
1379
1380 IF (p_ind_fcst_series_iname IS NOT NULL)
1381 THEN
1382 x_inner_view := x_inner_view || ' , MAX( ' || p_ind_fcst_series_iname || ' ) ' || p_ind_fcst_series_iname;
1383 END IF;
1384
1385 IF (p_dep_fcst_series_iname IS NOT NULL)
1386 THEN
1387 x_inner_view := x_inner_view || ' , SUM( ' || p_dep_fcst_series_iname || ' ) ' || p_dep_fcst_series_iname;
1388 END IF;
1389
1390 IF (x_error_column IS NOT NULL)
1391 THEN
1392 -- bug#9734502 -- nallkuma
1393 x_inner_view := x_inner_view || ' , AVG( ' || x_error_column_alias || ' ) ' || x_error_column_alias;
1394 END IF;
1395
1396 IF (x_demand_priority_column IS NOT NULL)
1397 THEN
1398 x_inner_view := x_inner_view || ' , MIN( ' || x_demand_priority_column || ' ) ' || x_demand_priority_column;
1399 END IF;
1400
1401 x_inner_view := x_inner_view || ' FROM ' || x_schema || '.' || x_view_name;
1402 x_inner_view := x_inner_view || ' GROUP BY SDATE, ' || x_prd_level || x_iv_group_by || ' ) ';
1403
1404 x_large_sql := replace (x_large_sql, x_schema || '.' || x_view_name, x_inner_view);
1405
1406 END IF;
1407
1408 /* Delete all data in the denorm for the export data profile */
1409 DELETE FROM MSD_DP_SCN_ENTRIES_DENORM
1410 WHERE demand_plan_id = x_demand_plan_id
1411 AND scenario_id = x_scenario_id;
1412
1413 COMMIT;
1414
1415 /* Insert forecast data into denorm table */
1416 EXECUTE IMMEDIATE x_large_sql;
1417 x_num_rows := SQL%ROWCOUNT;
1418
1419 /* Call Custom Hook for Upload */
1420
1421 msd_dem_custom_hooks.upload_hook (
1422 x_errbuf,
1423 x_retcode);
1424
1425 IF (x_retcode = -1)
1426 THEN
1427
1428 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
1429 VS_MSG_LOADED || ' ' || p_export_data_profile || ''' , ''' || VS_MSG_LOADE_ERROR || ''',''' || x_errbuf || ''' ); END;';
1430
1431
1432 EXECUTE IMMEDIATE x_small_sql;
1433
1434 raise_application_error (-20014, 'Error: msd_dem_upload_forecast.upload_forecast - Error in call to custom hook msd_dem_custom_hooks.upload_hook');
1435 END IF;
1436
1437 COMMIT;
1438
1439 msd_dem_collect_history_data.analyze_table (
1440 x_errbuf,
1441 x_retcode,
1442 'MSD_DP_SCN_ENTRIES_DENORM');
1443
1444
1445 /* sjagathe - Added for Product Family Forecast Support */
1446 IF (x_is_pf_level IS NOT NULL)
1447 THEN
1448
1449 IF ( x_is_global_fcst = 2 )
1450 THEN
1451
1452 INSERT INTO MSD_DP_SCN_ENTRIES_DENORM (
1453 DEMAND_PLAN_ID,
1454 SCENARIO_ID,
1455 DEMAND_ID,
1456 BUCKET_TYPE,
1457 START_TIME,
1458 END_TIME,
1459 SR_INSTANCE_ID,
1460 SR_ORGANIZATION_ID,
1461 SR_INVENTORY_ITEM_ID,
1462 SR_SHIP_TO_LOC_ID,
1463 SR_CUSTOMER_ID,
1464 SR_ZONE_ID,
1465 DEMAND_CLASS,
1466 INVENTORY_ITEM_ID,
1467 DP_UOM_CODE,
1468 ASCP_UOM_CODE,
1469 UNIT_PRICE,
1470 QUANTITY,
1471 ERROR_TYPE,
1472 FORECAST_ERROR,
1473 PRIORITY,
1474 PF_NAME,
1475 CREATION_DATE,
1476 CREATED_BY,
1477 LAST_UPDATE_LOGIN )
1478 SELECT /*+ ORDERED */
1479 x_demand_plan_id,
1480 x_scenario_id,
1481 x_num_rows + rownum - 1,
1482 x_bucket_type,
1483 entries.start_time,
1484 entries.end_time,
1485 entries.sr_instance_id,
1486 entries.sr_organization_id,
1487 msi.sr_inventory_item_id,
1488 entries.sr_ship_to_loc_id,
1489 entries.sr_customer_id,
1490 entries.sr_zone_id,
1491 entries.demand_class,
1492 msi.inventory_item_id,
1493 x_uom_code,
1494 msi.uom_code,
1495 msi.list_price * ((100 - msi.average_discount)/100),
1496 entries.quantity,
1497 null,
1498 null,
1499 null,
1500 null,
1501 sysdate,
1502 FND_GLOBAL.USER_ID,
1503 FND_GLOBAL.LOGIN_ID
1504 FROM (SELECT
1505 sr_instance_id,
1506 pf_name,
1507 sr_organization_id,
1508 sr_ship_to_loc_id,
1509 sr_customer_id,
1510 sr_zone_id,
1511 demand_class,
1512 start_time,
1513 end_time,
1514 sum(quantity) QUANTITY
1515 FROM msd_dp_scn_entries_denorm
1516 WHERE scenario_id = x_scenario_id
1517 GROUP BY sr_instance_id,
1518 pf_name,
1519 sr_organization_id,
1520 sr_ship_to_loc_id,
1521 sr_customer_id,
1522 sr_zone_id,
1523 demand_class,
1524 start_time,
1525 end_time) entries,
1526 msc_system_items msi
1527 WHERE msi.plan_id = -1
1528 AND msi.sr_instance_id = entries.sr_instance_id
1529 AND msi.organization_id = entries.sr_organization_id
1530 AND msi.item_name = entries.pf_name;
1531
1532 ELSE
1533
1534 INSERT INTO MSD_DP_SCN_ENTRIES_DENORM (
1535 DEMAND_PLAN_ID,
1536 SCENARIO_ID,
1537 DEMAND_ID,
1538 BUCKET_TYPE,
1539 START_TIME,
1540 END_TIME,
1541 SR_INSTANCE_ID,
1542 SR_ORGANIZATION_ID,
1543 SR_INVENTORY_ITEM_ID,
1544 SR_SHIP_TO_LOC_ID,
1545 SR_CUSTOMER_ID,
1546 SR_ZONE_ID,
1547 DEMAND_CLASS,
1548 INVENTORY_ITEM_ID,
1549 DP_UOM_CODE,
1550 ASCP_UOM_CODE,
1551 UNIT_PRICE,
1552 QUANTITY,
1553 ERROR_TYPE,
1554 FORECAST_ERROR,
1555 PRIORITY,
1556 PF_NAME,
1557 CREATION_DATE,
1558 CREATED_BY,
1559 LAST_UPDATE_LOGIN )
1560 SELECT /*+ ORDERED */
1561 x_demand_plan_id,
1562 x_scenario_id,
1563 x_num_rows + rownum - 1,
1564 x_bucket_type,
1565 entries.start_time,
1566 entries.end_time,
1567 entries.sr_instance_id,
1568 entries.sr_organization_id,
1569 msi.sr_inventory_item_id,
1570 entries.sr_ship_to_loc_id,
1571 entries.sr_customer_id,
1572 entries.sr_zone_id,
1573 entries.demand_class,
1574 msi.inventory_item_id,
1575 x_uom_code,
1576 msi.uom_code,
1577 msi.list_price * ((100 - msi.average_discount)/100),
1578 entries.quantity,
1579 null,
1580 null,
1581 null,
1582 null,
1583 sysdate,
1584 FND_GLOBAL.USER_ID,
1585 FND_GLOBAL.LOGIN_ID
1586 FROM (SELECT
1587 sr_instance_id,
1588 pf_name,
1589 sr_organization_id,
1590 sr_ship_to_loc_id,
1591 sr_customer_id,
1592 sr_zone_id,
1593 demand_class,
1594 start_time,
1595 end_time,
1596 sum(quantity) QUANTITY
1597 FROM msd_dp_scn_entries_denorm
1598 WHERE scenario_id = x_scenario_id
1599 GROUP BY sr_instance_id,
1600 pf_name,
1601 sr_organization_id,
1602 sr_ship_to_loc_id,
1603 sr_customer_id,
1604 sr_zone_id,
1605 demand_class,
1606 start_time,
1607 end_time) entries,
1608 msc_apps_instances mai,
1609 msc_system_items msi
1610 WHERE mai.instance_id = entries.sr_instance_id
1611 AND msi.plan_id = -1
1612 AND msi.sr_instance_id = mai.instance_id
1613 AND msi.organization_id = mai.validation_org_id
1614 AND msi.item_name = entries.pf_name;
1615
1616 END IF;
1617
1618 /* Delete Product Family members with forecast control none */
1619 DELETE FROM MSD_DP_SCN_ENTRIES_DENORM
1620 WHERE demand_plan_id = x_demand_plan_id
1621 AND scenario_id = x_scenario_id
1622 AND request_id = 3;
1623
1624 COMMIT;
1625
1626 END IF;
1627
1628 /* sjagathe - Added for SPF Upload Forecast and Metrics
1629 Metrics data is now available in the denorm table */
1630 IF (p_for_spf = 1)
1631 THEN
1632
1633 /* Delete all data in the metrics table for the export data profile */
1634 DELETE FROM MSC_DMD_SCN_METRICS
1635 WHERE plan_id = -1
1636 AND scenario_id = x_scenario_id;
1637
1638 COMMIT;
1639
1640 /* Aggregate metrics data from denorm and insert into metrics table */
1641 INSERT INTO MSC_DMD_SCN_METRICS NOLOGGING (
1642 PLAN_ID, SCENARIO_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID, SR_INSTANCE_ID,
1643 MAPE_IN_SAMPLE, MAPE_OUT_SAMPLE, FORECAST_VOLATILITY, AVG_DEMAND,
1644 CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
1645 SELECT
1646 -1, x_scenario_id, exp.inventory_item_id, exp.sr_organization_id, exp.sr_instance_id,
1647 avg(exp.mape_in_sample), avg(exp.mape_out_sample), avg(exp.forecast_volatility), avg(exp.avg_demand),
1648 FND_GLOBAL.USER_ID, systimestamp, systimestamp, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID
1649 FROM msd_dp_scn_entries_denorm exp
1650 WHERE exp.scenario_id = x_scenario_id
1651 GROUP BY exp.inventory_item_id, exp.sr_organization_id, exp.sr_instance_id;
1652
1653 COMMIT;
1654
1655 /* Get the msc schema name */
1656 x_boolean := fnd_installation.get_app_info ('MSC', x_dummy1, x_dummy2, x_msc_schema_name);
1657 msd_dem_collect_history_data.analyze_table (
1658 x_errbuf,
1659 x_retcode,
1660 x_msc_schema_name || '.MSC_DMD_SCN_METRICS');
1661
1662 END IF;
1663
1664 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
1665 VS_MSG_LOADED || ' ' || p_export_data_profile || ''' , ''' || VS_MSG_SUCCEEDED || ''' ); END;';
1666
1667
1668 EXECUTE IMMEDIATE x_small_sql;
1669
1670 /* Alter session to demantra schema */
1671 x_small_sql := 'alter session set current_schema = ' || x_schema;
1672 EXECUTE IMMEDIATE x_small_sql;
1673
1674 EXCEPTION
1675 WHEN OTHERS THEN
1676
1677 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
1678 VS_MSG_LOADED || ' ' || p_export_data_profile || ''' , ''' || VS_MSG_LOADE_ERROR || ''',''' || substr(SQLERRM,1,150) || ''' ); END;';
1679
1680
1681 EXECUTE IMMEDIATE x_small_sql;
1682
1683 /* Alter session to demantra schema */
1684 x_small_sql := 'alter session set current_schema = ' || x_schema;
1685 EXECUTE IMMEDIATE x_small_sql;
1686
1687 raise_application_error (-20015, 'Exception: msd_dem_upload_forecast.upload_forecast - ' || substr(SQLERRM,1,150));
1688
1689 END UPLOAD_FORECAST;
1690
1691
1692
1693
1694
1695 /*
1696 * This procedure export the planning percentages from Demantra to the table
1697 * MSD_DP_PLANNING_PCT_DENORM table.
1698 * The parameters are -
1699 * p_pp_export_data_profile - Export data profile used to export planning
1700 * percentages
1701 * p_fcst_export_data_profile - Export data profile used to export total demand
1702 * p_parent_item_series_iname - Internal Name of the series which holds parent
1703 * item total demand
1704 * p_option_item_series_iname - Internal Name of the series which holds the option
1705 * item dependent demand
1706 */
1707 PROCEDURE UPLOAD_PLANNING_PERCENTAGES (
1708 p_pp_export_data_profile IN VARCHAR2,
1709 p_fcst_export_data_profile IN VARCHAR2,
1710 p_pctg_series_iname IN VARCHAR2,
1711 p_parent_item_series_iname IN VARCHAR2 DEFAULT NULL,
1712 p_option_item_series_iname IN VARCHAR2 DEFAULT NULL )
1713 IS
1714
1715 TYPE CUR_TYPE IS REF CURSOR;
1716 x_cur_type CUR_TYPE;
1717
1718 x_errbuf VARCHAR2(200) := NULL;
1719 x_retcode VARCHAR2(100) := NULL;
1720
1721 x_small_sql VARCHAR2(600) := NULL;
1722 x_schema VARCHAR(50) := NULL;
1723 x_pctg_exp_dp VARCHAR2(200) := NULL;
1724 x_fcst_exp_dp VARCHAR2(200) := NULL;
1725 x_pctg_series_iname VARCHAR2(200) := NULL;
1726 x_parent_series_iname VARCHAR2(30) := NULL;
1727 x_option_series_iname VARCHAR2(30) := NULL;
1728 x_publish_variant NUMBER := 0; /* 0 - Pctg, 1-Fcst */
1729 x_table_name VARCHAR2(70) := NULL;
1730 x_sql VARCHAR2(2000) := NULL;
1731 x_uom_code VARCHAR2(100) := NULL;
1732
1733 x_profile_id NUMBER := NULL;
1734 x_presentation_type NUMBER := NULL;
1735 x_view_name VARCHAR2(30) := NULL;
1736 x_time_res_id NUMBER := NULL;
1737 x_unit_id NUMBER := NULL;
1738 x_index_id NUMBER := NULL;
1739 x_data_scale NUMBER := NULL;
1740 x_integration_type NUMBER := NULL;
1741 x_export_type NUMBER := NULL;
1742 x_is_view_present NUMBER := 0;
1743
1744 x_fcst_profile_id NUMBER := NULL;
1745 x_demand_plan_id NUMBER := NULL;
1746 x_scenario_id NUMBER := NULL;
1747 x_bucket_type NUMBER := NULL;
1748 x_start_time VARCHAR2(100) := NULL;
1749 x_end_time VARCHAR2(100) := NULL;
1750 x_res_type NUMBER := NULL;
1751 x_time_from_clause VARCHAR2(500) := NULL;
1752 x_sr_organization_id VARCHAR2(50) := NULL;
1753 x_pctg_column VARCHAR2(500) := NULL;
1754
1755 x_select_clause VARCHAR2(3000) := NULL;
1756 x_from_clause VARCHAR2(500) := NULL;
1757 x_where_clause VARCHAR2(3000) := NULL;
1758 x_insert_clause VARCHAR2(1000) := NULL;
1759 x_large_sql VARCHAR2(6000) := NULL;
1760 x_inner_view VARCHAR2(1000) := NULL;
1761
1762 x_org_level VARCHAR2(30) := NULL;
1763 x_prd_level VARCHAR2(30) := NULL;
1764 x_prd_key_column VARCHAR2(30) := NULL;
1765 x_parent_item_level VARCHAR2(30) := NULL;
1766
1767 x_is_global_fcst NUMBER(1) := NULL;
1768 x_sr_instance_id_for_global NUMBER := NULL;
1769 x_num_rows NUMBER := 0;
1770
1771 BEGIN
1772
1773 /* Alter session to APPS */
1774 x_small_sql := 'alter session set current_schema = APPS';
1775 EXECUTE IMMEDIATE x_small_sql;
1776
1777
1778 x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
1779 IF (x_schema IS NULL)
1780 THEN
1781 raise_application_error (-20001, 'Error: msd_dem_upload_forecast.upload_planning_percentages - Unable to find schema name');
1782 END IF;
1783
1784
1785
1786 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_PCTG || ''' , ''' ||
1787 VS_MSG_LOADING || ' ' || p_pp_export_data_profile || ''' , ''' || VS_MSG_STARTED || ''' ); END;';
1788
1789 EXECUTE IMMEDIATE x_small_sql;
1790
1791
1792 /* Initialize local variables */
1793 IF (p_pp_export_data_profile IS NULL)
1794 THEN
1795 raise_application_error (-20002, 'Error: msd_dem_upload_forecast.upload_planning_percentages - Planning Percentage export data profile name NOT provided');
1796 ELSE
1797 x_pctg_exp_dp := lower(p_pp_export_data_profile);
1798 END IF;
1799
1800 IF (p_fcst_export_data_profile IS NULL)
1801 THEN
1802 raise_application_error (-20003, 'Error: msd_dem_upload_forecast.upload_planning_percentages - Total Demand export data profile name NOT provided');
1803 ELSE
1804 x_fcst_exp_dp := lower(p_fcst_export_data_profile);
1805 END IF;
1806
1807 IF (p_pctg_series_iname IS NULL)
1808 THEN
1809 raise_application_error (-20004, 'Error: msd_dem_upload_forecast.upload_planning_percentages - Planning Percentage series internal name NOT provided');
1810 ELSE
1811 x_pctg_series_iname := lower(p_pctg_series_iname);
1812 END IF;
1813
1814 IF (p_parent_item_series_iname IS NOT NULL
1815 AND p_option_item_series_iname IS NOT NULL)
1816 THEN
1817 x_parent_series_iname := lower(p_parent_item_series_iname);
1818 x_option_series_iname := lower(p_option_item_series_iname);
1819 END IF;
1820
1821 IF (p_parent_item_series_iname IS NULL
1822 AND p_option_item_series_iname IS NOT NULL)
1823 THEN
1824 raise_application_error (-20005, 'Error: msd_dem_upload_forecast.upload_planning_percentages - Parent Item Demand series internal name NOT provided');
1825 END IF;
1826
1827 IF (p_option_item_series_iname IS NULL
1828 AND p_parent_item_series_iname IS NOT NULL)
1829 THEN
1830 raise_application_error (-20006, 'Error: msd_dem_upload_forecast.upload_planning_percentages - Option Item Demand series internal name NOT provided');
1831 END IF;
1832
1833 /* Determine how is planning percentage exported */
1834 IF (p_parent_item_series_iname IS NOT NULL)
1835 THEN
1836 x_publish_variant := 1;
1837 ELSE
1838 x_publish_variant := 0;
1839 END IF;
1840
1841 /* Get the export data profile info */
1842 x_table_name := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'TRANSFER_QUERY');
1843 x_sql := 'SELECT id, presentation_type, view_name, ' ||
1844 ' time_res_id, unit_id, index_id, data_scale, ' ||
1845 ' integration_type, export_type ' ||
1846 ' FROM ' || x_table_name ||
1847 ' WHERE lower(query_name) = ''' || x_pctg_exp_dp || '''';
1848
1849 OPEN x_cur_type FOR x_sql;
1850 FETCH x_cur_type INTO x_profile_id,
1851 x_presentation_type,
1852 x_view_name,
1853 x_time_res_id,
1854 x_unit_id,
1855 x_index_id,
1856 x_data_scale,
1857 x_integration_type,
1858 x_export_type;
1859 CLOSE x_cur_type;
1860
1861
1862 x_sql := 'SELECT count(1) FROM dba_objects ' ||
1863 ' WHERE owner = upper(''' || x_schema || ''')' ||
1864 ' AND object_type IN (''VIEW'', ''MATERIALIZED VIEW'') ' ||
1865 ' AND object_name = upper(''' || x_view_name || ''')';
1866 EXECUTE IMMEDIATE x_sql INTO x_is_view_present;
1867
1868 x_uom_code := msd_dem_common_utilities.get_uom_code (x_unit_id);
1869
1870 /* Get the id of the forecast profile */
1871 x_sql := 'SELECT id ' ||
1872 ' FROM ' || x_table_name ||
1873 ' WHERE lower(query_name) = ''' || x_fcst_exp_dp || '''';
1874
1875 OPEN x_cur_type FOR x_sql;
1876 FETCH x_cur_type INTO x_fcst_profile_id;
1877 CLOSE x_cur_type;
1878
1879 /*** Check basic error conditions - BEGIN ***/
1880
1881 IF (x_profile_id IS NULL)
1882 THEN
1883 raise_application_error (-20007, 'Error: msd_dem_upload_forecast.upload_planning_percentages - Unable to get pctg export data profile id');
1884 ELSIF (x_integration_type = C_IMPORT_DATA_PROFILE)
1885 THEN
1886 raise_application_error (-20008, 'Error: msd_dem_upload_forecast.upload_planning_percentages - ' || x_pctg_exp_dp || 'is not an export data profile');
1887 ELSIF (x_export_type = C_EXPORT_TYPE_INCR)
1888 THEN
1889 raise_application_error (-20009, 'Error: msd_dem_upload_forecast.upload_planning_percentages - Incremental export type is not supported');
1890 ELSIF (x_index_id IS NOT NULL)
1891 THEN
1892 raise_application_error (-20010, 'Error: msd_dem_upload_forecast.upload_planning_percentages - Forecast amount cannot be uploaded');
1893 ELSIF (x_is_view_present = 0)
1894 THEN
1895 raise_application_error (-20011, 'Error: msd_dem_upload_forecast.upload_planning_percentages - Forecast has not yet been exported');
1896 ELSIF (x_presentation_type = C_PSNT_TYPE_DESC)
1897 THEN
1898 raise_application_error (-20012, 'Error: msd_dem_upload_forecast.upload_planning_percentages - Presentation type must by Code');
1899 ELSIF (x_unit_id <> 1 AND lower(x_uom_code) <> 'units')
1900 THEN
1901 raise_application_error (-20013, 'Error: msd_dem_upload_forecast.upload_planning_percentages - Display unit is not UNITS.');
1902 ELSIF (x_fcst_profile_id IS NULL)
1903 THEN
1904 raise_application_error (-20013, 'Error: msd_dem_upload_forecast.upload_planning_percentages - Unable to get forecast export data profile id');
1905 END IF;
1906
1907 /*** Check basic error conditions - END ***/
1908
1909 x_demand_plan_id := C_DEMAND_PLAN_ID;
1910 x_scenario_id := x_fcst_profile_id + C_SCENARIO_ID_OFFSET;
1911
1912 x_select_clause := ' SELECT ' || x_demand_plan_id || ' , ' ||
1913 x_scenario_id || ' , ';
1914
1915 /*** Get Time Info - BEGIN ***/
1916
1917 get_time_strings (
1918 x_bucket_type,
1919 x_start_time,
1920 x_end_time,
1921 x_res_type,
1922 x_time_from_clause,
1923 x_time_res_id);
1924
1925 IF (x_res_type IS NULL)
1926 THEN
1927 raise_application_error (-20014, 'Error: msd_dem_upload_forecast.upload_planning_percentages - Unable to find schema name in get_time_strings');
1928 END IF;
1929
1930 /*** Get Time Info - END ***/
1931
1932 x_select_clause := x_select_clause || x_start_time || ' , '
1933 || x_end_time || ' , ';
1934
1935 x_from_clause := ' FROM ' || x_schema || '.' || x_view_name || ' exp, ' ||
1936 ' msc_system_items msi, ';
1937
1938 /* Get the levels at which planning percentages are being exported
1939 * Expected Levels -
1940 * 1. Item (Mandatory)
1941 * 2. Parent Item (Mandatory)
1942 * 3. Organization (Not Mandatory)
1943 */
1944
1945 /* PRODUCT */
1946 x_prd_level := get_level_column (x_profile_id, C_ITEM);
1947 IF (x_prd_level IS NULL)
1948 THEN
1949 raise_application_error (-20015, 'Error: msd_dem_upload_forecast.upload_planning_percentages - Item level is required for upload');
1950 ELSE
1951 x_prd_key_column := get_series_column (x_profile_id, C_DKEY_SERIES_PREFIX, C_DKEY_ITEM);
1952 END IF;
1953
1954 x_select_clause := x_select_clause || ' msi.sr_instance_id, ';
1955
1956 /* Parent Item */
1957 x_parent_item_level := get_level_column (x_profile_id, C_PARENT_ITEM);
1958 IF (x_parent_item_level IS NULL)
1959 THEN
1960 raise_application_error (-20016, 'Error: msd_dem_upload_forecast.upload_planning_percentages - Parent Item level is required for upload');
1961 END IF;
1962
1963 /* ORGANIZATION */
1964 x_org_level := get_level_column (x_profile_id, C_ORGANIZATION);
1965 IF (x_org_level IS NULL) /* global */
1966 THEN
1967 x_is_global_fcst := 1;
1968 x_sr_organization_id := '-1';
1969
1970 x_sr_instance_id_for_global := get_sr_instance_id_for_global;
1971 IF (x_sr_instance_id_for_global IS NULL)
1972 THEN
1973 raise_application_error (-20017, 'Error: msd_dem_upload_forecast.upload_planning_percentages - Unable to get sr_instance_id for global planning percentages');
1974 END IF;
1975
1976 ELSE
1977 x_is_global_fcst := 2;
1978 x_sr_organization_id := ' msi.organization_id ';
1979
1980 END IF;
1981
1982 x_select_clause := x_select_clause || x_sr_organization_id || ' , ' ||
1983 ' msi.inventory_item_id, ';
1984
1985 IF (x_is_global_fcst = 2)
1986 THEN
1987 x_from_clause := x_from_clause || ' msc_trading_partners mtp_org, ';
1988 END IF;
1989
1990 x_from_clause := x_from_clause || ' msc_system_items pitem, '
1991 || ' msc_boms mb, '
1992 || ' msc_bom_components mbc, ';
1993
1994 IF (x_res_type = 1)
1995 THEN
1996 x_from_clause := x_from_clause || ' msc_apps_instances mai ';
1997 ELSE
1998 x_from_clause := x_from_clause || ' msc_apps_instances mai, ' || x_time_from_clause;
1999 END IF;
2000
2001 x_select_clause := x_select_clause || ' MSD_DP_PLANNING_PERCENTAGES_S.nextval, '
2002 || ' mbc.component_sequence_id, '
2003 || ' mb.bill_sequence_id, '
2004 || ' pitem.inventory_item_id, ';
2005
2006 /* Planning Percentage Columns */
2007 IF (x_publish_variant = 0)
2008 THEN
2009 x_pctg_column := 'exp.' || x_pctg_series_iname;
2010 ELSE
2011 x_pctg_column := ' ( decode ( exp.' || p_parent_item_series_iname || ', null, exp.' || x_pctg_series_iname || ', 0, exp.'|| x_pctg_series_iname || ', (exp.'|| p_option_item_series_iname || '/exp.' || p_parent_item_series_iname|| ' ) ) ) ';
2012 END IF;
2013
2014 x_select_clause := x_select_clause || x_pctg_column || ' , ';
2015 x_select_clause := x_select_clause || '1, ';
2016
2017 x_select_clause := x_select_clause || ' sysdate, ' ||
2018 ' FND_GLOBAL.USER_ID, ' ||
2019 ' FND_GLOBAL.LOGIN_ID ';
2020
2021 /* BUILD WHERE CLAUSE */
2022 IF (x_is_global_fcst = 2)
2023 THEN
2024
2025 x_where_clause := ' WHERE mtp_org.partner_type = 3 ' ||
2026 ' AND exp.' || x_org_level || ' = mtp_org.organization_code ' ||
2027 ' AND msi.plan_id = -1 ' ||
2028 ' AND msi.sr_instance_id = mtp_org.sr_instance_id ' ||
2029 ' AND msi.organization_id = mtp_org.sr_tp_id ';
2030 ELSE
2031
2032 x_where_clause := ' WHERE msi.plan_id = -1 ' ||
2033 ' AND msi.sr_instance_id = ' || to_char(x_sr_instance_id_for_global) ||
2034 ' AND msi.organization_id = mai.validation_org_id ';
2035 END IF;
2036
2037 IF (x_prd_key_column IS NOT NULL)
2038 THEN
2039 x_where_clause := x_where_clause ||
2040 ' AND msi.inventory_item_id = exp.' || x_prd_key_column || ' ';
2041 ELSE
2042 x_where_clause := x_where_clause ||
2043 ' AND msi.item_name = exp.' || x_prd_level || ' ';
2044 END IF;
2045
2046 x_where_clause := x_where_clause ||
2047 ' AND msi.sr_instance_id = mai.instance_id ';
2048
2049 x_where_clause := x_where_clause ||
2050 ' AND pitem.item_name = exp.' || x_parent_item_level ||
2051 ' AND pitem.plan_id = -1 ' ||
2052 ' AND pitem.sr_instance_id = msi.sr_instance_id ' ||
2053 ' AND pitem.organization_id = msi.organization_id ' ||
2054 ' AND mb.plan_id = -1 ' ||
2055 ' AND mb.organization_id = msi.organization_id ' ||
2056 ' AND mb.sr_instance_id = msi.sr_instance_id ' ||
2057 ' AND mb.assembly_item_id = pitem.inventory_item_id ' ||
2058 ' AND mb.alternate_bom_designator is null ' ||
2059 ' AND mbc.plan_id = -1 ' ||
2060 ' AND mbc.sr_instance_id = mb.sr_instance_id ' ||
2061 ' AND mbc.bill_sequence_id = mb.bill_sequence_id ' ||
2062 ' AND mbc.inventory_item_id = msi.inventory_item_id ';
2063
2064 IF (x_res_type = 2)
2065 THEN
2066 x_where_clause := x_where_clause ||
2067 ' AND exp.sdate = inp.end_time ';
2068 ELSIF (x_res_type = 3)
2069 THEN
2070 x_where_clause := x_where_clause ||
2071 ' AND exp.sdate = inp.start_time ';
2072 END IF;
2073
2074 x_insert_clause := 'INSERT INTO MSD_DP_PLANNING_PCT_DENORM ( ' ||
2075 ' DEMAND_PLAN_ID, ' ||
2076 ' DP_SCENARIO_ID, ' ||
2077 ' DATE_FROM, ' ||
2078 ' DATE_TO, ' ||
2079 ' SR_INSTANCE_ID, ' ||
2080 ' ORGANIZATION_ID, ' ||
2081 ' INVENTORY_ITEM_ID, ' ||
2082 ' COMPONENT_SEQUENCE_ID, ' ||
2083 ' ORIG_COMPONENT_SEQUENCE_ID, ' ||
2084 ' BILL_SEQUENCE_ID, ' ||
2085 ' ASSEMBLY_ITEM_ID, ' ||
2086 ' PLANNING_FACTOR, ' ||
2087 ' PLAN_PERCENTAGE_TYPE, ' ||
2088 ' CREATION_DATE, ' ||
2089 ' CREATED_BY, ' ||
2090 ' LAST_UPDATE_LOGIN )';
2091
2092 x_large_sql := x_insert_clause || x_select_clause || x_from_clause || x_where_clause;
2093
2094 /* For Planning Percentages - Pre-aggregate the view */
2095 IF (x_publish_variant = 1)
2096 THEN
2097
2098 x_inner_view := '(SELECT SDATE, '
2099 || x_prd_level;
2100
2101 IF (x_prd_key_column IS NOT NULL)
2102 THEN
2103 x_inner_view := x_inner_view || ' , ' || x_prd_key_column;
2104 END IF;
2105
2106 x_inner_view := x_inner_view || ' , ' || x_parent_item_level;
2107
2108 IF (x_is_global_fcst = 2)
2109 THEN
2110 x_inner_view := x_inner_view || ' , ' || x_org_level;
2111 END IF;
2112
2113 x_inner_view := x_inner_view || ' , SUM( ' || x_parent_series_iname || ' ) ' || x_parent_series_iname;
2114 x_inner_view := x_inner_view || ' , SUM( ' || x_option_series_iname || ' ) ' || x_option_series_iname;
2115 x_inner_view := x_inner_view || ' , AVG( ' || x_pctg_series_iname || ' ) ' || x_pctg_series_iname;
2116
2117 x_inner_view := x_inner_view || ' FROM ' || x_schema || '.' || x_view_name;
2118
2119 x_inner_view := x_inner_view || ' GROUP BY SDATE, ' || x_prd_level;
2120
2121 IF (x_prd_key_column IS NOT NULL)
2122 THEN
2123 x_inner_view := x_inner_view || ' , ' || x_prd_key_column;
2124 END IF;
2125
2126 x_inner_view := x_inner_view || ' , ' || x_parent_item_level;
2127
2128 IF (x_is_global_fcst = 2)
2129 THEN
2130 x_inner_view := x_inner_view || ' , ' || x_org_level;
2131 END IF;
2132
2133 x_inner_view := x_inner_view || ' ) ';
2134
2135 x_large_sql := replace (x_large_sql, x_schema || '.' || x_view_name, x_inner_view);
2136
2137 END IF;
2138
2139
2140 /* Delete all data in the denorm for the export data profile */
2141 DELETE FROM MSD_DP_PLANNING_PCT_DENORM
2142 WHERE demand_plan_id = x_demand_plan_id
2143 AND dp_scenario_id = x_scenario_id;
2144
2145 COMMIT;
2146
2147 /* Insert planning percentages into denorm table */
2148 EXECUTE IMMEDIATE x_large_sql;
2149 x_num_rows := SQL%ROWCOUNT;
2150
2151 COMMIT;
2152
2153 msd_dem_collect_history_data.analyze_table (
2154 x_errbuf,
2155 x_retcode,
2156 'MSD_DP_PLANNING_PCT_DENORM');
2157
2158 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_PCTG || ''' , ''' ||
2159 VS_MSG_LOADED || ' ' || p_pp_export_data_profile || ''' , ''' || VS_MSG_SUCCEEDED || ''' ); END;';
2160
2161
2162 EXECUTE IMMEDIATE x_small_sql;
2163
2164 /* Alter session to demantra schema */
2165 x_small_sql := 'alter session set current_schema = ' || x_schema;
2166 EXECUTE IMMEDIATE x_small_sql;
2167
2168 EXCEPTION
2169 WHEN OTHERS THEN
2170
2171 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_PCTG || ''' , ''' ||
2172 VS_MSG_LOADED || ' ' || p_pp_export_data_profile || ''' , ''' || VS_MSG_LOADE_ERROR || ''',''' || substr(SQLERRM,1,150) || ''' ); END;';
2173
2174
2175 EXECUTE IMMEDIATE x_small_sql;
2176
2177 /* Alter session to demantra schema */
2178 x_small_sql := 'alter session set current_schema = ' || x_schema;
2179 EXECUTE IMMEDIATE x_small_sql;
2180
2181 raise_application_error (-20015, 'Exception: msd_dem_upload_forecast.upload_planning_percentages - ' || substr(SQLERRM,1,150));
2182
2183 END UPLOAD_PLANNING_PERCENTAGES;
2184
2185
2186
2187
2188 /*
2189 * This procedure exports total demand, forecast error and demand priority from Demantra
2190 * to the table MSD_DP_SCN_ENTRIES_DENOM.
2191 * The parameters are -
2192 * p_ind_export_data_profile - Export Data Profile used to export independent demand
2193 * p_dep_export_data_profile - Export Data Profile used to export dependent demand
2194 * p_ind_fcst_series_iname - Internal Name of the series for independent demand
2195 * p_dep_fcst_series_iname - Internal Name of the series for dependent demand
2196 */
2197 PROCEDURE UPLOAD_TOTAL_DEMAND (
2198 p_ind_export_data_profile IN VARCHAR2,
2199 p_dep_export_data_profile IN VARCHAR2,
2200 p_ind_fcst_series_iname IN VARCHAR2,
2201 p_dep_fcst_series_iname IN VARCHAR2,
2202 p_upload_unplanned_components IN VARCHAR2 DEFAULT 0
2203 )
2204 IS
2205
2206 x_errbuf VARCHAR2(200) := NULL;
2207 x_retcode VARCHAR2(100) := NULL;
2208
2209 x_small_sql VARCHAR2(600) := NULL;
2210 x_schema VARCHAR(50) := NULL;
2211 x_ind_scenario_id NUMBER := NULL;
2212 x_dep_scenario_id NUMBER := NULL;
2213 x_max_demand_id NUMBER := NULL;
2214
2215 BEGIN
2216
2217 /*** VALIDATE INPUT PARAMETERS - BEGIN ***/
2218
2219 IF ( p_ind_export_data_profile IS NULL
2220 OR p_dep_export_data_profile IS NULL
2221 OR p_ind_fcst_series_iname IS NULL
2222 OR p_dep_fcst_series_iname IS NULL)
2223 THEN
2224 raise_application_error (-20001, 'Error: msd_dem_upload_forecast.upload_total_demand - All the four input parameters must be specified');
2225 END IF;
2226
2227 /*** VALIDATE INPUT PARAMETERS - END ***/
2228
2229
2230 /* Independent Demand Publish */
2231
2232 upload_forecast(p_ind_export_data_profile, p_ind_fcst_series_iname, null, 2, null, p_upload_unplanned_components);
2233 /* Dependent Demand Publish */
2234 upload_forecast(p_dep_export_data_profile, null, p_dep_fcst_series_iname);
2235
2236
2237 /* Alter session to APPS */
2238 x_small_sql := 'alter session set current_schema = APPS';
2239 EXECUTE IMMEDIATE x_small_sql;
2240
2241
2242 x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
2243 IF (x_schema IS NULL)
2244 THEN
2245 raise_application_error (-20002, 'Error: msd_dem_upload_forecast.upload_total_demand - Unable to find schema name');
2246 END IF;
2247
2248
2249
2250 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_TD || ''' , ''' ||
2251 VS_MSG_LOADING || ' ' || p_ind_export_data_profile || ''' , ''' || VS_MSG_STARTED || ''' ); END;';
2252
2253 EXECUTE IMMEDIATE x_small_sql;
2254
2255
2256
2257 x_small_sql := 'SELECT id FROM ' || x_schema || '.TRANSFER_QUERY WHERE lower(query_name) = :1 ';
2258
2259 /* Get the id for independent data profile */
2260 EXECUTE IMMEDIATE x_small_sql
2261 INTO x_ind_scenario_id
2262 USING lower(p_ind_export_data_profile);
2263 x_ind_scenario_id := x_ind_scenario_id + C_SCENARIO_ID_OFFSET;
2264
2265 /* Get the id for dependent data profile */
2266 EXECUTE IMMEDIATE x_small_sql
2267 INTO x_dep_scenario_id
2268 USING lower(p_dep_export_data_profile);
2269 x_dep_scenario_id := x_dep_scenario_id + C_SCENARIO_ID_OFFSET;
2270
2271 /* Get the max demand id for independent demand */
2272 EXECUTE IMMEDIATE 'SELECT max(demand_id) FROM msd_dp_scn_entries_denorm WHERE scenario_id = :1'
2273 INTO x_max_demand_id
2274 USING x_ind_scenario_id;
2275 IF (x_max_demand_id IS NULL)
2276 THEN
2277 x_max_demand_id := 0; -- bug#9734502 nallkuma
2278 ELSE
2279 x_max_demand_id := x_max_demand_id + 1;
2280 END IF;
2281
2282
2283 UPDATE msd_dp_scn_entries_denorm
2284 SET scenario_id = x_ind_scenario_id,
2285 demand_id = demand_id + x_max_demand_id
2286 WHERE scenario_id = x_dep_scenario_id;
2287 COMMIT;
2288
2289
2290 msd_dem_collect_history_data.analyze_table (
2291 x_errbuf,
2292 x_retcode,
2293 'MSD_DP_SCN_ENTRIES_DENORM');
2294
2295 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_TD || ''' , ''' ||
2296 VS_MSG_LOADED || ' ' || p_ind_export_data_profile || ''' , ''' || VS_MSG_SUCCEEDED || ''' ); END;';
2297
2298
2299 EXECUTE IMMEDIATE x_small_sql;
2300
2301 /* Alter session to demantra schema */
2302 x_small_sql := 'alter session set current_schema = ' || x_schema;
2303 EXECUTE IMMEDIATE x_small_sql;
2304
2305 EXCEPTION
2306 WHEN OTHERS THEN
2307
2308 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_TD || ''' , ''' ||
2309 VS_MSG_LOADED || ' ' || p_ind_export_data_profile || ''' , ''' || VS_MSG_LOADE_ERROR || ''',''' || substr(SQLERRM,1,150) || ''' ); END;';
2310
2311
2312 EXECUTE IMMEDIATE x_small_sql;
2313
2314 /* Alter session to demantra schema */
2315 x_small_sql := 'alter session set current_schema = ' || x_schema;
2316 EXECUTE IMMEDIATE x_small_sql;
2317
2318 raise_application_error (-20015, 'Exception: msd_dem_upload_forecast.upload_total_demand - ' || substr(SQLERRM,1,150));
2319
2320 END UPLOAD_TOTAL_DEMAND;
2321
2322
2323
2324
2325
2326 /*
2327 * This procedure is a wrapper on top of existing procedure UPLOAD_FORECAST
2328 * This procedure accepts Application_IDs as arguments instead of data profile names.
2329 * The procedure get the data profile names from Demantra and then call UPLOAD FORECAST
2330 * The parameters are -
2331 * p_export_data_profile_wai - Application Id of the export data profile
2332 * p_ind_fcst_series_wai - Application Id of the independent demand series
2333 * p_dep_fcst_series_wai - Application Id of the dependent demand series
2334 */
2335
2336 PROCEDURE UPLOAD_FORECAST_WITH_APP_ID (
2337 p_export_data_profile_wai IN VARCHAR2,
2338 p_ind_fcst_series_wai IN VARCHAR2 DEFAULT NULL,
2339 p_dep_fcst_series_wai IN VARCHAR2 DEFAULT NULL,
2340 p_upload_unplanned_components IN VARCHAR2 DEFAULT 0 --adding for bug#13393529, upload ind fcst for unplanned items with pick_components_flag = 'Y'
2341 )
2342 IS
2343
2344 x_small_sql VARCHAR2(600) := NULL;
2345 x_schema VARCHAR2(50) := NULL;
2346
2347 x_export_data_profile VARCHAR2(255) := NULL;
2348 x_ind_fcst_series VARCHAR2(50) := NULL;
2349 x_dep_fcst_series VARCHAR2(50) := NULL;
2350
2351 BEGIN
2352
2353 IF (p_export_data_profile_wai IS NULL)
2354 THEN
2355 raise_application_error(-20001, 'Error: msd_dem_upload_forecast.upload_forecast_with_app_id - Export Data Profile Application ID is null');
2356 END IF;
2357
2358 /* Alter session to APPS */
2359 x_small_sql := 'alter session set current_schema = APPS';
2360 EXECUTE IMMEDIATE x_small_sql;
2361
2362
2363 x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
2364 IF (x_schema IS NULL)
2365 THEN
2366 raise_application_error (-20002, 'Error: msd_dem_upload_forecast.upload_forecast_with_app_id - Unable to find schema name');
2367 END IF;
2368
2369 /* Get the name of the data profile */
2370 BEGIN
2371
2372 EXECUTE IMMEDIATE 'SELECT query_name FROM ' || x_schema || '.TRANSFER_QUERY WHERE application_id = :1 '
2373 INTO x_export_data_profile
2374 USING p_export_data_profile_wai;
2375
2376 EXCEPTION
2377 WHEN OTHERS THEN
2378 /* Alter session to demantra schema */
2379 x_small_sql := 'alter session set current_schema = ' || x_schema;
2380 EXECUTE IMMEDIATE x_small_sql;
2381
2382 raise_application_error (-20003, 'Exception: msd_dem_upload_forecast.upload_forecast_with_app_id - Unable to find data profile ' || substr(SQLERRM,1,150));
2383 END;
2384
2385
2386 /* Get the internal name of the independent demand forecast series */
2387 BEGIN
2388
2389 IF (p_ind_fcst_series_wai IS NOT NULL)
2390 THEN
2391
2392 EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
2393 INTO x_ind_fcst_series
2394 USING p_ind_fcst_series_wai;
2395
2396 END IF;
2397
2398 EXCEPTION
2399 WHEN OTHERS THEN
2400 /* Alter session to demantra schema */
2401 x_small_sql := 'alter session set current_schema = ' || x_schema;
2402 EXECUTE IMMEDIATE x_small_sql;
2403
2404 raise_application_error (-20004, 'Exception: msd_dem_upload_forecast.upload_forecast_with_app_id - Unable to find ind fcst series ' || substr(SQLERRM,1,150));
2405 END;
2406
2407
2408 /* Get the internal name of the dependent demand forecast series */
2409 BEGIN
2410
2411 IF (p_dep_fcst_series_wai IS NOT NULL)
2412 THEN
2413
2414 EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
2415 INTO x_dep_fcst_series
2416 USING p_dep_fcst_series_wai;
2417
2418 END IF;
2419
2420 EXCEPTION
2421 WHEN OTHERS THEN
2422 /* Alter session to demantra schema */
2423 x_small_sql := 'alter session set current_schema = ' || x_schema;
2424 EXECUTE IMMEDIATE x_small_sql;
2425
2426 raise_application_error (-20005, 'Exception: msd_dem_upload_forecast.upload_forecast_with_app_id - Unable to find dep fcst series ' || substr(SQLERRM,1,150));
2427 END;
2428
2429 /* Alter session to demantra schema */
2430 x_small_sql := 'alter session set current_schema = ' || x_schema;
2431 EXECUTE IMMEDIATE x_small_sql;
2432
2433
2434
2435 upload_forecast(x_export_data_profile, x_ind_fcst_series, x_dep_fcst_series, 2, NULL, p_upload_unplanned_components);
2436
2437 EXCEPTION
2438 WHEN OTHERS THEN
2439
2440 /* Alter session to demantra schema */
2441 x_small_sql := 'alter session set current_schema = ' || x_schema;
2442 EXECUTE IMMEDIATE x_small_sql;
2443
2444 raise_application_error (-20015, 'Exception: msd_dem_upload_forecast.upload_forecast_with_app_id - ' || substr(SQLERRM,1,150));
2445
2446 END UPLOAD_FORECAST_WITH_APP_ID;
2447
2448
2449
2450 /*
2451 * This procedure export the planning percentages from Demantra to the table
2452 * MSD_DP_PLANNING_PCT_DENORM table.
2453 * The parameters are -
2454 * p_pp_export_data_profile - Export data profile used to export planning
2455 * percentages
2456 * p_fcst_export_data_profile - Export data profile used to export total demand
2457 * p_parent_item_series_iname - Internal Name of the series which holds parent
2458 * item total demand
2459 * p_option_item_series_iname - Internal Name of the series which holds the option
2460 * item dependent demand
2461 */
2462 PROCEDURE UPLOAD_CTO_PLNG_PCT_DIRECT (
2463 p_pp_export_data_profile IN VARCHAR2,
2464 p_fcst_export_data_profile IN VARCHAR2,
2465 p_pctg_series_iname IN VARCHAR2,
2466 p_parent_item_series_iname IN VARCHAR2 DEFAULT NULL,
2467 p_option_item_series_iname IN VARCHAR2 DEFAULT NULL,
2468 p_upload_pct_by_base_model IN VARCHAR2,
2469 p_exclude_pto_model IN NUMBER )
2470 IS
2471 var_boolean boolean;
2472 var_dummy1 varchar2(100);
2473 var_dummy2 varchar2(100);
2474 var_msd_schema_name varchar2(50);
2475
2476 CURSOR EXP_VIEW IS
2477 SELECT ITEM, ORGANIZATION, PARENT_ITEM, TOP_ATO_MODEL,DATE_FROM, DATE_TO, PLANNING_FACTOR
2478 FROM MSD_DP_PLANNING_PCT_TMP ORDER BY SERIAL;
2479
2480 TYPE PLN_FACTOR_RECORD
2481 IS RECORD (ITEM VARCHAR2(240), ORGANIZATION VARCHAR2(240), PARENT_ITEM VARCHAR2(240), TOP_ATO_MODEL NUMBER,
2482 DATE_FROM DATE, DATE_TO DATE, PLANNING_FACTOR NUMBER );
2483 TYPE PLN_FACTOR_TABLE IS TABLE OF PLN_FACTOR_RECORD INDEX BY BINARY_INTEGER;
2484 PLN_FACTORS PLN_FACTOR_TABLE;
2485 EXP_RECORD PLN_FACTOR_RECORD;
2486 APPEND_INDEX BINARY_INTEGER := 1;
2487 C_INSERT_BATCH_SIZE NUMBER := 120000;
2488
2489 TYPE CUR_TYPE IS REF CURSOR;
2490 x_cur_type CUR_TYPE;
2491
2492 x_errbuf VARCHAR2(200) := NULL;
2493 x_retcode VARCHAR2(100) := NULL;
2494 x_msd_schema VARCHAR(50) := NULL;
2495
2496 x_small_sql VARCHAR2(600) := NULL;
2497 x_schema VARCHAR(50) := NULL;
2498 x_pctg_exp_dp VARCHAR2(200) := NULL;
2499 x_fcst_exp_dp VARCHAR2(200) := NULL;
2500 x_pctg_series_iname VARCHAR2(200) := NULL;
2501 x_parent_series_iname VARCHAR2(30) := NULL;
2502 x_option_series_iname VARCHAR2(30) := NULL;
2503 x_publish_variant NUMBER := 0; /* 0 - Pctg, 1-Fcst */
2504 x_sql VARCHAR2(2000) := NULL;
2505 x_uom_code VARCHAR2(100) := NULL;
2506
2507 x_profile_id NUMBER := NULL;
2508 x_presentation_type NUMBER := NULL;
2509 x_view_name VARCHAR2(60) := NULL;
2510 x_time_res_id NUMBER := NULL;
2511 x_unit_id NUMBER := NULL;
2512 x_index_id NUMBER := NULL;
2513 x_data_scale NUMBER := NULL;
2514 x_integration_type NUMBER := NULL;
2515 x_export_type NUMBER := NULL;
2516 x_profile_user NUMBER := NULL;
2517 x_profile_hint VARCHAR2(500 CHAR) := NULL;
2518 x_is_view_present NUMBER := 0;
2519 x_value VARCHAR2(4000) := NULL;
2520
2521 x_fcst_profile_id NUMBER := NULL;
2522 x_demand_plan_id NUMBER := NULL;
2523 x_scenario_id NUMBER := NULL;
2524 x_bucket_type NUMBER := NULL;
2525 x_start_time VARCHAR2(100) := NULL;
2526 x_end_time VARCHAR2(100) := NULL;
2527 x_res_type NUMBER := NULL;
2528 x_time_from_clause VARCHAR2(500) := NULL;
2529 x_sr_organization_id VARCHAR2(50) := NULL;
2530 x_pctg_column VARCHAR2(500) := NULL;
2531
2532 x_select_clause VARCHAR2(4000) := NULL;
2533 x_from_clause VARCHAR2(1000) := NULL;
2534 x_where_clause VARCHAR2(2000) := NULL;
2535 x_group_by_clause VARCHAR2(4000) := NULL;
2536 x_insert_clause VARCHAR2(1000) := NULL;
2537 x_large_sql VARCHAR2(20000) := NULL;
2538 x_inner_view VARCHAR2(12000) := NULL;
2539 x_inner_view_name VARCHAR2(60) :=NULL;
2540
2541 x_org_level VARCHAR2(30) := NULL;
2542 x_prd_level VARCHAR2(30) := NULL;
2543 x_parent_item_level VARCHAR2(30) := NULL;
2544 x_base_model_level VARCHAR2(30) := NULL;
2545
2546 x_is_global_fcst NUMBER(1) := NULL;
2547 x_sr_instance_id_for_global NUMBER := NULL;
2548 x_num_rows NUMBER := 0;
2549 x_exclude_pto_model NUMBER := 1;
2550
2551 BEGIN
2552
2553 /* Alter session to APPS */
2554 x_small_sql := 'alter session set current_schema = APPS';
2555 EXECUTE IMMEDIATE x_small_sql;
2556
2557 x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
2558 IF (x_schema IS NULL) THEN
2559 raise_application_error (-20001, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - Unable to find schema name');
2560 END IF;
2561
2562 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_PCTG_DRCT || ''' , ''' ||
2563 VS_MSG_LOADING || ' ' || p_pp_export_data_profile || ''' , ''' || VS_MSG_STARTED || ''' ); END;';
2564 EXECUTE IMMEDIATE x_small_sql;
2565
2566 /* Initialize local variables */
2567 IF (p_pp_export_data_profile IS NULL) THEN
2568 raise_application_error (-20002, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - Planning Percentage export data profile name NOT provided');
2569 ELSE
2570 x_pctg_exp_dp := lower(p_pp_export_data_profile);
2571 END IF;
2572
2573 IF (p_fcst_export_data_profile IS NULL) THEN
2574 raise_application_error (-20003, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - Total Demand export data profile name NOT provided');
2575 ELSE
2576 x_fcst_exp_dp := lower(p_fcst_export_data_profile);
2577 END IF;
2578
2579 IF (p_pctg_series_iname IS NULL) THEN
2580 raise_application_error (-20004, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - Planning Percentage series internal name NOT provided');
2581 ELSE
2582 x_pctg_series_iname := lower(p_pctg_series_iname);
2583 END IF;
2584
2585 IF (p_parent_item_series_iname IS NOT NULL AND p_option_item_series_iname IS NOT NULL) THEN
2586 x_parent_series_iname := lower(p_parent_item_series_iname);
2587 x_option_series_iname := lower(p_option_item_series_iname);
2588 END IF;
2589
2590 IF (p_parent_item_series_iname IS NULL AND p_option_item_series_iname IS NOT NULL) THEN
2591 raise_application_error (-20005, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - Parent Item Demand series internal name NOT provided');
2592 END IF;
2593
2594 IF (p_option_item_series_iname IS NULL AND p_parent_item_series_iname IS NOT NULL) THEN
2595 raise_application_error (-20006, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - Option Item Demand series internal name NOT provided');
2596 END IF;
2597
2598 /* Determine how is planning percentage exported */
2599 IF (p_parent_item_series_iname IS NOT NULL) THEN
2600 x_publish_variant := 1;
2601 ELSE
2602 x_publish_variant := 0;
2603 END IF;
2604
2605 /* Get the export data profile info */
2606 x_sql := 'SELECT tq.id, tq.presentation_type, tq.view_name, ' ||
2607 ' tq.time_res_id, tq.unit_id, tq.index_id, tq.data_scale, ' ||
2608 ' tq.integration_type, tq.export_type, ' ||
2609 ' tl.user_id, ph.data_hint ' ||
2610 ' FROM ' || x_schema || '.TRANSFER_QUERY tq, ' || x_schema || '.TRANSFER_LIST tl, ' ||
2611 ' ' || x_schema || '.PROFILE_HINTS ph ' ||
2612 ' WHERE lower(query_name) = ''' || x_pctg_exp_dp || '''' ||
2613 ' AND tl.id = tq.transfer_id AND ph.profile_id(+) = tq.id ';
2614
2615 OPEN x_cur_type FOR x_sql;
2616 FETCH x_cur_type INTO x_profile_id,
2617 x_presentation_type,
2618 x_view_name,
2619 x_time_res_id,
2620 x_unit_id,
2621 x_index_id,
2622 x_data_scale,
2623 x_integration_type,
2624 x_export_type,
2625 x_profile_user,
2626 x_profile_hint;
2627 CLOSE x_cur_type;
2628
2629 /* ** export profile view will not be used but we check if the view is present to make sure export profile is run and all INT* tables are created ** */
2630 x_sql := 'SELECT count(1) FROM dba_objects ' ||
2631 ' WHERE owner = upper(''' || x_schema || ''')' ||
2632 ' AND object_type IN (''VIEW'', ''MATERIALIZED VIEW'') ' ||
2633 ' AND object_name = upper(''' || x_view_name || ''')';
2634 EXECUTE IMMEDIATE x_sql INTO x_is_view_present;
2635
2636 x_uom_code := msd_dem_common_utilities.get_uom_code (x_unit_id);
2637
2638 /* Get the id of the forecast profile */
2639 x_sql := 'SELECT id ' ||
2640 ' FROM ' || x_schema || '.transfer_query ' ||
2641 ' WHERE lower(query_name) = ''' || x_fcst_exp_dp || '''';
2642
2643 OPEN x_cur_type FOR x_sql;
2644 FETCH x_cur_type INTO x_fcst_profile_id;
2645 CLOSE x_cur_type;
2646
2647 /*** Check basic error conditions - BEGIN ***/
2648
2649 IF (x_profile_id IS NULL) THEN
2650 raise_application_error (-20007, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - Unable to get pctg export data profile id');
2651 ELSIF (x_integration_type = C_IMPORT_DATA_PROFILE) THEN
2652 raise_application_error (-20008, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - ' || x_pctg_exp_dp || 'is not an export data profile');
2653 ELSIF (x_export_type = C_EXPORT_TYPE_INCR) THEN
2654 raise_application_error (-20009, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - Incremental export type is not supported');
2655 ELSIF (x_index_id IS NOT NULL) THEN
2656 raise_application_error (-20010, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - Forecast amount cannot be uploaded');
2657 ELSIF (x_is_view_present = 0) THEN
2658 raise_application_error (-20011, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - Forecast has not yet been exported');
2659 ELSIF (x_presentation_type = C_PSNT_TYPE_DESC) THEN
2660 raise_application_error (-20012, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - Presentation type must by Code');
2661 ELSIF (x_unit_id <> 1 AND lower(x_uom_code) <> 'units') THEN
2662 raise_application_error (-20013, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - Display unit is not UNITS.');
2663 ELSIF (x_fcst_profile_id IS NULL) THEN
2664 raise_application_error (-20014, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - Unable to get forecast export data profile id');
2665 END IF;
2666
2667 /*** Check basic error conditions - END ***/
2668
2669 /* Get the levels at which planning percentages are being exported
2670 * Expected Levels -
2671 * 1. Item (Mandatory)
2672 * 2. Parent Item (Mandatory)
2673 * 3. Organization (Not Mandatory)
2674 */
2675 /* PRODUCT */
2676 x_prd_level := get_level_column (x_profile_id, C_ITEM);
2677 IF (x_prd_level IS NULL) THEN
2678 raise_application_error (-20015, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - Item level is required for upload');
2679 END IF;
2680
2681 /* Parent Item */
2682 x_parent_item_level := get_level_column (x_profile_id, C_PARENT_ITEM);
2683 IF (x_parent_item_level IS NULL) THEN
2684 raise_application_error (-20016, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - Parent Item level is required for upload');
2685 END IF;
2686
2687 /* Base Model */
2688 x_base_model_level := get_level_column (x_profile_id, C_CTO_BASE_MODEL);
2689 IF (x_base_model_level IS NULL) THEN
2690 raise_application_error (-20017, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - Base Model level is required for upload');
2691 END IF;
2692
2693 /* ORGANIZATION */
2694 x_org_level := get_level_column (x_profile_id, C_ORGANIZATION);
2695 IF (x_org_level IS NULL) THEN /* global */
2696 x_is_global_fcst := 1;
2697 x_sr_organization_id := '-1';
2698
2699 x_sr_instance_id_for_global := get_sr_instance_id_for_global;
2700 IF (x_sr_instance_id_for_global IS NULL) THEN
2701 raise_application_error (-20018, 'Error: msd_dem_upload_forecast.upload_cto_plng_pct_direct - Unable to get sr_instance_id for global planning percentages');
2702 END IF;
2703 ELSE
2704 x_is_global_fcst := 2;
2705 x_sr_organization_id := ' msi.organization_id ';
2706 END IF;
2707
2708 --truncate temp table
2709 var_boolean := fnd_installation.get_app_info ('MSD', var_dummy1, var_dummy2, var_msd_schema_name);
2710 x_small_sql := 'TRUNCATE table ' || var_msd_schema_name ||'.MSD_DP_PLANNING_PCT_TMP';
2711 EXECUTE IMMEDIATE x_small_sql;
2712
2713 /* Get Time Info - BEGIN */
2714
2715 get_time_strings (
2716 x_bucket_type,
2717 x_start_time,
2718 x_end_time,
2719 x_res_type,
2720 x_time_from_clause,
2721 x_time_res_id,
2722 1);
2723
2724 /* Get Time Info - END */
2725
2726
2727 msd_dem_common_utilities.GET_SCHEMA_NAME(x_errbuf,x_retcode,x_msd_schema,'MSD');
2728
2729 IF(p_upload_pct_by_base_model = 'Y') THEN
2730 x_exclude_pto_model := 1;
2731 ELSE
2732 x_exclude_pto_model := 0 ;
2733 END IF;
2734 x_inner_view_name := substr(x_view_name,1,50) || '_TMP_V';
2735 /* construct sql for data profile export view */
2736 x_inner_view := 'CREATE OR REPLACE FORCE VIEW ' || x_schema || '.' || x_inner_view_name || ' ( ' ||
2737 ' DATE_FROM, DATE_TO, ';
2738 x_select_clause := 'SELECT ' || case when x_profile_hint is not null then '/*+ ' || x_profile_hint || ' */ ' end ||
2739 replace(x_start_time, 'SD', 'min(inp.datet)') || ' as date_from, ' ||
2740 replace(x_end_time, 'ED', 'max(inp.datet)') || ' as date_to, ' ;
2741
2742 x_from_clause := ' FROM (SELECT DISTINCT ITEM_ID, LOCATION_ID, T_EP_CTO_ID ';
2743
2744 x_sql := 'select to_char(min(from_sales_date), ''DD-MON-RRRR'') from ' || x_schema || '.INT_DATE_' || x_profile_id || '_' || x_profile_user;
2745 execute immediate x_sql into x_value;
2746 x_where_clause := ' WHERE INP.DATET BETWEEN TO_DATE(''' || x_value || ''', ''DD-MON-RRRR'')';
2747
2748 x_sql := 'select to_char(max(to_sales_date), ''DD-MON-RRRR'') from ' || x_schema || '.INT_DATE_' || x_profile_id || '_' || x_profile_user;
2749 execute immediate x_sql into x_value;
2750 x_where_clause := x_where_clause || ' AND TO_DATE(''' || x_value || ''', ''DD-MON-RRRR'') ';
2751
2752 x_group_by_clause := ' GROUP BY ';
2753
2754 x_sql := 'select lorder from ' || x_schema || '.transfer_query_levels where id = ' || x_profile_id || ' order by lorder';
2755 open x_cur_type for x_sql;
2756 loop
2757 fetch x_cur_type into x_value;
2758 exit when x_cur_type%NOTFOUND;
2759
2760 x_inner_view := x_inner_view || 'LEVEL' || x_value || ', ';
2761 x_select_clause := x_select_clause || 'T_COMB_LIST.LEVEL' || x_value || ', ';
2762 x_from_clause := x_from_clause || ', T_POPU_LIST.LEVEL_ID' || x_value || ' ';
2763 x_where_clause := x_where_clause || 'AND T_COMB_LIST.LEVEL_ID' || x_value || ' = T_POPU_LIST.LEVEL_ID' || x_value || ' ';
2764 x_group_by_clause := x_group_by_clause || 'T_COMB_LIST.LEVEL' || x_value || ', ';
2765 end loop;
2766 close x_cur_type;
2767
2768
2769
2770
2771 if(p_upload_pct_by_base_model='Y') then
2772 x_group_by_clause := x_group_by_clause || 'decode(dem.ATO_FORECAST_CONTROL,3,NULL,DEM.TOP_ATO_MODEL_ID),' || case when x_res_type = 1 then 'INP.DATET ' else 'INP.' || x_time_from_clause end ||
2773 ' HAVING (';
2774 ELSE
2775 x_group_by_clause := x_group_by_clause || 'TOP_ATO_MODEL_ID,' || case when x_res_type = 1 then 'INP.DATET ' else 'INP.' || x_time_from_clause end ||
2776 ' HAVING (';
2777 END IF;
2778
2779 IF( x_publish_variant = 1) THEN
2780 x_sql := 'select exp_template from ' || x_schema || '.computed_fields where lower(computed_name) = ''' || x_option_series_iname || '''';
2781 execute immediate x_sql into x_value;
2782 x_inner_view := x_inner_view || x_option_series_iname || ', ';
2783 x_select_clause := x_select_clause || x_value || ' as ' || x_option_series_iname || ', ';
2784 x_group_by_clause := x_group_by_clause || '(' || x_value || ' IS NOT NULL AND ';
2785
2786 x_sql := 'select exp_template from ' || x_schema || '.computed_fields where lower(computed_name) = ''' || x_parent_series_iname || '''';
2787 execute immediate x_sql into x_value;
2788 x_inner_view := x_inner_view || x_parent_series_iname || ', ';
2789 x_select_clause := x_select_clause || x_value || ' as ' || x_parent_series_iname || ', ';
2790 x_group_by_clause := x_group_by_clause || x_value || ' > 0 ) OR ';
2791 END IF;
2792
2793 x_sql := 'select exp_template from ' || x_schema || '.computed_fields where lower(computed_name) = ''' || x_pctg_series_iname || '''';
2794 execute immediate x_sql into x_value;
2795 x_inner_view := x_inner_view || x_pctg_series_iname || ' ';
2796 x_select_clause := x_select_clause || x_value || ' as ' || x_pctg_series_iname || ' ';
2797 x_group_by_clause := x_group_by_clause || '(' || x_value || ' IS NOT NULL )) ';
2798
2799 x_from_clause := x_from_clause || 'FROM INT_POPU_' || x_profile_id || '_' || x_profile_user || ' T_POPU_LIST ' ||
2800 ' WHERE T_POPU_LIST.IS_BASE5 = 1)T_POPU_LIST, ' ||
2801 'INT_COMB_' || x_profile_id || '_' || x_profile_user || ' T_COMB_LIST, ' ||
2802 'inputs INP, T_EP_CTO_DATA BRANCH_DATA, ' ||
2803 'T_EP_CTO_MATRIX, T_EP_CTO, ';
2804
2805 x_where_clause := x_where_clause || 'AND BRANCH_DATA.ITEM_ID = T_POPU_LIST.ITEM_ID ' ||
2806 'AND BRANCH_DATA.LOCATION_ID = T_POPU_LIST.LOCATION_ID ' ||
2807 'AND BRANCH_DATA.SALES_DATE = INP.DATET ' ||
2808 'AND BRANCH_DATA.T_EP_CTO_ID = T_POPU_LIST.T_EP_CTO_ID ' ||
2809 'AND T_EP_CTO.T_EP_CTO_ID = BRANCH_DATA.T_EP_CTO_ID ' ||
2810 'AND T_EP_CTO_MATRIX.ITEM_ID = T_POPU_LIST.ITEM_ID ' ||
2811 'AND T_EP_CTO_MATRIX.LOCATION_ID = T_POPU_LIST.LOCATION_ID ' ||
2812 'AND T_EP_CTO_MATRIX.T_EP_CTO_ID = BRANCH_DATA.T_EP_CTO_ID ' ||
2813 'AND T_POPU_LIST.' || replace(x_parent_item_level, 'LEVEL', 'LEVEL_ID') || ' = T_EP_CTO_MATRIX.T_EP_ITEM_EP_ID ' ||
2814 'AND T_POPU_LIST.' || replace(x_base_model_level, 'LEVEL', 'LEVEL_ID') || ' = T_EP_CTO_MATRIX.T_EP_CTO_BASE_MODEL_ID ';
2815
2816 x_inner_view := x_inner_view || ',TOP_ATO_MODEL_ID';
2817 if(p_upload_pct_by_base_model='Y') then
2818 x_select_clause := x_select_clause || ',decode(dem.ATO_FORECAST_CONTROL,3,NULL,DEM.TOP_ATO_MODEL_ID) AS TOP_ATO_MODEL_ID';
2819 ELSE
2820 x_select_clause := x_select_clause || ',NULL AS TOP_ATO_MODEL_ID';
2821 END IF;
2822 x_from_clause := x_from_clause || x_msd_schema ||'.MSD_DEM_CTO_BOM DEM' ;
2823 x_where_clause := x_where_clause || ' AND DEM.CTO_CODE = T_EP_CTO.T_EP_CTO_CODE';
2824 IF(x_exclude_pto_model=1) then
2825 x_where_clause := x_where_clause || ' AND DEM.TOP_ATO_MODEL_ID is NOT NULL';
2826 END IF;
2827
2828
2829
2830 x_inner_view := x_inner_view || ') AS ' || x_select_clause || x_from_clause || x_where_clause || x_group_by_clause;
2831
2832 /* Alter session to demantra */
2833 x_small_sql := 'alter session set current_schema = '|| x_schema;
2834 EXECUTE IMMEDIATE x_small_sql;
2835
2836 execute immediate x_inner_view;
2837
2838 /* Alter session to APPS */
2839 x_small_sql := 'alter session set current_schema = APPS';
2840 EXECUTE IMMEDIATE x_small_sql;
2841
2842 /* Planning Percentage Columns */
2843 IF (x_publish_variant = 0) THEN
2844 x_pctg_column := x_pctg_series_iname;
2845 ELSE
2846 x_pctg_column := ' ( decode ( ' || p_parent_item_series_iname || ', null, ' || x_pctg_series_iname || ', 0, '|| x_pctg_series_iname || ', ('|| p_option_item_series_iname || '/' || p_parent_item_series_iname|| ' ) ) ) ';
2847 END IF;
2848
2849 x_insert_clause := 'INSERT into MSD_DP_PLANNING_PCT_TMP' ||
2850 ' (SERIAL, ITEM, ORGANIZATION, PARENT_ITEM,TOP_ATO_MODEL,DATE_FROM, DATE_TO, PLANNING_FACTOR) ';
2851 x_select_clause := ' SELECT ROWNUM, ITEM, ORGANIZATION, PARENT_ITEM, TOP_ATO_MODEL,DATE_FROM, DATE_TO, PLN_PCT ' ||
2852 ' FROM ( SELECT ' || x_prd_level || ' AS ITEM, ' ||
2853 case when x_is_global_fcst = 1 then '''-1''' else x_org_level end || ' AS ORGANIZATION, ' ||
2854 x_parent_item_level || ' AS PARENT_ITEM, TOP_ATO_MODEL_ID as TOP_ATO_MODEL,DATE_FROM, DATE_TO, ' ||
2855 x_pctg_column || ' AS PLN_PCT ';
2856
2857 x_from_clause := 'FROM ( SELECT DATE_FROM, DATE_TO, ' ||
2858 x_prd_level || ', ' ||
2859 case when x_is_global_fcst = 1 then '' else x_org_level || ', ' end ||
2860 x_parent_item_level || ', TOP_ATO_MODEL_ID , ' ||
2861 case when x_publish_variant = 1 then
2862 'SUM(' || x_parent_series_iname || ') ' || x_parent_series_iname ||
2863 ', SUM(' || x_option_series_iname || ') ' || x_option_series_iname || ', '
2864 end ||
2865 'AVG(' || x_pctg_series_iname || ') ' || x_pctg_series_iname || ' FROM ';
2866
2867 x_group_by_clause := ' GROUP BY DATE_FROM, DATE_TO, ' ||
2868 x_prd_level || ',' ||
2869 case when x_is_global_fcst = 1 then '' else x_org_level || ', ' end ||
2870 x_parent_item_level || ', TOP_ATO_MODEL_ID) ' ||
2871 ' ORDER BY ITEM, ORGANIZATION, PARENT_ITEM,TOP_ATO_MODEL,DATE_FROM, PLN_PCT) ';
2872
2873 x_large_sql := x_insert_clause || x_select_clause || x_from_clause || x_schema || '.' || x_inner_view_name || x_group_by_clause;
2874
2875 /* Insert planning percentages into temp table */
2876 EXECUTE IMMEDIATE x_large_sql;
2877 x_num_rows := SQL%ROWCOUNT;
2878 COMMIT;
2879
2880 x_demand_plan_id := C_DEMAND_PLAN_ID;
2881 x_scenario_id := x_fcst_profile_id + C_SCENARIO_ID_OFFSET;
2882
2883 /* Delete all data in the denorm for the export data profile */
2884 DELETE FROM MSD_DP_PLANNING_PCT_DENORM WHERE demand_plan_id = x_demand_plan_id AND dp_scenario_id = x_scenario_id;
2885 COMMIT;
2886
2887 x_insert_clause := 'INSERT INTO MSD_DP_PLANNING_PCT_DENORM ( ' ||
2888 ' DEMAND_PLAN_ID, ' ||
2889 ' DP_SCENARIO_ID, ' ||
2890 ' DATE_FROM, ' ||
2891 ' DATE_TO, ' ||
2892 ' SR_INSTANCE_ID, ' ||
2893 ' ORGANIZATION_ID, ' ||
2894 ' INVENTORY_ITEM_ID, ' ||
2895 ' COMPONENT_SEQUENCE_ID, ' ||
2896 ' ORIG_COMPONENT_SEQUENCE_ID, ' ||
2897 ' BILL_SEQUENCE_ID, ' ||
2898 ' ASSEMBLY_ITEM_ID, ' ||
2899 ' BASE_MODEL_ITEM_ID, ' ||
2900 ' PLANNING_FACTOR, ' ||
2901 ' PLAN_PERCENTAGE_TYPE, ' ||
2902 ' CREATION_DATE, ' ||
2903 ' CREATED_BY, ' ||
2904 ' LAST_UPDATE_LOGIN ) ';
2905
2906 x_select_clause := ' SELECT ' || x_demand_plan_id || ' , ' ||
2907 x_scenario_id || ' , ' ||
2908 ':1, :2, msi.sr_instance_id,' || x_sr_organization_id || ', msi.inventory_item_id, ' ||
2909 'MSD_DP_PLANNING_PERCENTAGES_S.nextval, ' ||
2910 'mbc.component_sequence_id, ' ||
2911 'mb.bill_sequence_id, ' ||
2912 'pitem.inventory_item_id, ' ||
2913 ':3,:4, 1, sysdate, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID ';
2914 x_from_clause := 'FROM msc_system_items msi, ' ||
2915 case when x_is_global_fcst = 2 then ' msc_trading_partners mtp_org, ' end ||
2916 ' msc_system_items pitem, msc_boms mb, msc_bom_components mbc, msc_apps_instances mai ';
2917
2918 IF (x_is_global_fcst = 2) THEN
2919 x_where_clause := 'WHERE mtp_org.partner_type = 3 ' ||
2920 ' AND :5 = mtp_org.organization_code ' ||
2921 ' AND msi.plan_id = -1 ' ||
2922 ' AND msi.sr_instance_id = mtp_org.sr_instance_id ' ||
2923 ' AND msi.organization_id = mtp_org.sr_tp_id ';
2924 ELSE
2925 x_where_clause := 'WHERE msi.plan_id = -1 ' ||
2926 ' AND msi.sr_instance_id = ' || to_char(x_sr_instance_id_for_global) ||
2927 ' AND msi.organization_id = mai.validation_org_id ';
2928 END IF;
2929
2930 x_where_clause := x_where_clause || ' AND msi.item_name = :6 AND msi.sr_instance_id = mai.instance_id ' ||
2931 ' AND msi.new_plan_id IS NULL AND msi.simulation_set_id IS NULL ' ||
2932 ' AND pitem.item_name = :7 AND pitem.plan_id = -1 ' ||
2933 ' AND pitem.sr_instance_id = msi.sr_instance_id ' ||
2934 ' AND pitem.organization_id = msi.organization_id ' ||
2935 ' AND pitem.new_plan_id IS NULL AND pitem.simulation_set_id IS NULL ' ||
2936 ' AND mb.plan_id = -1 AND mb.organization_id = msi.organization_id ' ||
2937 ' AND mb.sr_instance_id = msi.sr_instance_id ' ||
2938 ' AND mb.assembly_item_id = pitem.inventory_item_id ' ||
2939 ' AND mb.alternate_bom_designator is null ' ||
2940 ' AND mbc.plan_id = -1 AND mbc.sr_instance_id = mb.sr_instance_id ' ||
2941 ' AND mbc.bill_sequence_id = mb.bill_sequence_id ' ||
2942 ' AND mbc.inventory_item_id = msi.inventory_item_id ';
2943
2944 x_large_sql := x_insert_clause || x_select_clause || x_from_clause || x_where_clause;
2945
2946 OPEN EXP_VIEW;
2947 FETCH EXP_VIEW INTO EXP_RECORD;
2948
2949 IF (EXP_VIEW%NOTFOUND) THEN
2950 GOTO LOG_AND_EXIT;
2951 END IF;
2952
2953 PLN_FACTORS(APPEND_INDEX).ITEM := EXP_RECORD.ITEM;
2954 PLN_FACTORS(APPEND_INDEX).ORGANIZATION := EXP_RECORD.ORGANIZATION;
2955 PLN_FACTORS(APPEND_INDEX).PARENT_ITEM := EXP_RECORD.PARENT_ITEM;
2956 PLN_FACTORS(APPEND_INDEX).DATE_FROM := EXP_RECORD.DATE_FROM;
2957 PLN_FACTORS(APPEND_INDEX).DATE_TO := EXP_RECORD.DATE_TO;
2958 PLN_FACTORS(APPEND_INDEX).PLANNING_FACTOR := EXP_RECORD.PLANNING_FACTOR;
2959 PLN_FACTORS(APPEND_INDEX).TOP_ATO_MODEL := EXP_RECORD.TOP_ATO_MODEL ;
2960 LOOP
2961 FETCH EXP_VIEW INTO EXP_RECORD;
2962 EXIT WHEN EXP_VIEW%NOTFOUND;
2963
2964 IF (PLN_FACTORS(APPEND_INDEX).ITEM = EXP_RECORD.ITEM AND PLN_FACTORS(APPEND_INDEX).ORGANIZATION = EXP_RECORD.ORGANIZATION
2965 AND PLN_FACTORS(APPEND_INDEX).PARENT_ITEM = EXP_RECORD.PARENT_ITEM AND NVL(PLN_FACTORS(APPEND_INDEX).TOP_ATO_MODEL,0) = NVL(EXP_RECORD.TOP_ATO_MODEL,0)
2966 AND PLN_FACTORS(APPEND_INDEX).PLANNING_FACTOR = EXP_RECORD.PLANNING_FACTOR)
2967 THEN
2968 PLN_FACTORS(APPEND_INDEX).DATE_TO := EXP_RECORD.DATE_TO;
2969 ELSE
2970 IF ( PLN_FACTORS.COUNT >= C_INSERT_BATCH_SIZE ) THEN
2971 IF x_is_global_fcst = 1 THEN
2972 FORALL I IN PLN_FACTORS.FIRST..PLN_FACTORS.LAST
2973 EXECUTE IMMEDIATE x_large_sql
2974 using PLN_FACTORS(I).DATE_FROM, PLN_FACTORS(I).DATE_TO, PLN_FACTORS(I).TOP_ATO_MODEL, PLN_FACTORS(I).PLANNING_FACTOR,
2975 PLN_FACTORS(I).ITEM, PLN_FACTORS(I).PARENT_ITEM;
2976 ELSE
2977 FORALL I IN PLN_FACTORS.FIRST..PLN_FACTORS.LAST
2978 EXECUTE IMMEDIATE x_large_sql
2979 using PLN_FACTORS(I).DATE_FROM, PLN_FACTORS(I).DATE_TO,PLN_FACTORS(I).TOP_ATO_MODEL, PLN_FACTORS(I).PLANNING_FACTOR,
2980 PLN_FACTORS(I).ORGANIZATION, PLN_FACTORS(I).ITEM, PLN_FACTORS(I).PARENT_ITEM;
2981 END IF;
2982 COMMIT;
2983
2984 APPEND_INDEX := 0;
2985 PLN_FACTORS.DELETE;
2986 END IF;
2987
2988 APPEND_INDEX := APPEND_INDEX+1;
2989 PLN_FACTORS(APPEND_INDEX).ITEM := EXP_RECORD.ITEM;
2990 PLN_FACTORS(APPEND_INDEX).ORGANIZATION := EXP_RECORD.ORGANIZATION;
2991 PLN_FACTORS(APPEND_INDEX).PARENT_ITEM := EXP_RECORD.PARENT_ITEM;
2992 PLN_FACTORS(APPEND_INDEX).DATE_FROM := EXP_RECORD.DATE_FROM;
2993 PLN_FACTORS(APPEND_INDEX).DATE_TO := EXP_RECORD.DATE_TO;
2994 PLN_FACTORS(APPEND_INDEX).PLANNING_FACTOR := EXP_RECORD.PLANNING_FACTOR;
2995 PLN_FACTORS(APPEND_INDEX).TOP_ATO_MODEL := EXP_RECORD.TOP_ATO_MODEL;
2996 END IF;
2997 END LOOP;
2998
2999 IF ( PLN_FACTORS.COUNT > 0 ) THEN
3000 IF x_is_global_fcst = 1 THEN
3001 FORALL I IN PLN_FACTORS.FIRST..PLN_FACTORS.LAST
3002 EXECUTE IMMEDIATE x_large_sql
3003 using PLN_FACTORS(I).DATE_FROM, PLN_FACTORS(I).DATE_TO , PLN_FACTORS(I).TOP_ATO_MODEL, PLN_FACTORS(I).PLANNING_FACTOR,
3004 PLN_FACTORS(I).ITEM, PLN_FACTORS(I).PARENT_ITEM;
3005 ELSE
3006 FORALL I IN PLN_FACTORS.FIRST..PLN_FACTORS.LAST
3007 EXECUTE IMMEDIATE x_large_sql
3008 using PLN_FACTORS(I).DATE_FROM, PLN_FACTORS(I).DATE_TO , PLN_FACTORS(I).TOP_ATO_MODEL, PLN_FACTORS(I).PLANNING_FACTOR,
3009 PLN_FACTORS(I).ORGANIZATION, PLN_FACTORS(I).ITEM, PLN_FACTORS(I).PARENT_ITEM;
3010 END IF;
3011 COMMIT;
3012 END IF;
3013
3014 CLOSE EXP_VIEW;
3015
3016 msd_dem_collect_history_data.analyze_table (
3017 x_errbuf,
3018 x_retcode,
3019 'MSD_DP_PLANNING_PCT_DENORM');
3020
3021 <<LOG_AND_EXIT>>
3022 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_PCTG_DRCT || ''' , ''' ||
3023 VS_MSG_LOADED || ' ' || p_pp_export_data_profile || ''' , ''' || VS_MSG_SUCCEEDED || ''' ); END;';
3024
3025
3026 EXECUTE IMMEDIATE x_small_sql;
3027
3028 /* Alter session to demantra schema */
3029 x_small_sql := 'alter session set current_schema = ' || x_schema;
3030 EXECUTE IMMEDIATE x_small_sql;
3031
3032 EXCEPTION
3033 WHEN OTHERS THEN
3034 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_PCTG_DRCT || ''' , ''' ||
3035 VS_MSG_LOADED || ' ' || p_pp_export_data_profile || ''' , ''' || VS_MSG_LOADE_ERROR || ''',''' || substr(SQLERRM,1,150) || ''' ); END;';
3036
3037 EXECUTE IMMEDIATE x_small_sql;
3038
3039 /* Alter session to demantra schema */
3040 x_small_sql := 'alter session set current_schema = ' || x_schema;
3041 EXECUTE IMMEDIATE x_small_sql;
3042
3043 raise_application_error (-20019, 'Exception: msd_dem_upload_forecast.upload_cto_plng_pct_direct - ' || substr(SQLERRM,1,150));
3044 END UPLOAD_CTO_PLNG_PCT_DIRECT;
3045
3046
3047
3048 /*
3049 * This procedure is a wrapper on top of existing procedure UPLOAD_PLANNING_PERCENTAGES
3050 * This procedure accepts Application-IDs as arguments instead of data profile names.
3051 * The procedure gets thedata profile names from Demantra and then calls UPLOAD_PLANNING_PERCENTAGES
3052 * The parameters are -
3053 *
3054 */
3055 PROCEDURE UPLOAD_PLNG_PCTG_WITH_APP_ID (
3056 p_pp_export_data_profile_wai IN VARCHAR2,
3057 p_fcst_export_data_profile_wai IN VARCHAR2,
3058 p_pctg_series_wai IN VARCHAR2,
3059 p_parent_item_series_wai IN VARCHAR2 DEFAULT NULL,
3060 p_option_item_series_wai IN VARCHAR2 DEFAULT NULL,
3061 p_exclude_pto_model IN NUMBER DEFAULT 1,
3062 p_use_export_profile_view IN VARCHAR2 DEFAULT 1
3063 )
3064 IS
3065
3066 x_small_sql VARCHAR2(600) := NULL;
3067 x_schema VARCHAR2(50) := NULL;
3068
3069 x_pp_export_data_profile VARCHAR2(255) := NULL;
3070 x_fcst_export_data_profile VARCHAR2(255) := NULL;
3071 x_pctg_series VARCHAR2(50) := NULL;
3072 x_parent_item_series VARCHAR2(50) := NULL;
3073 x_option_item_series VARCHAR2(50) := NULL;
3074 x_upload_pct_by_base_model VARCHAR2(10) := NULL;
3075
3076
3077 BEGIN
3078
3079 IF (p_pp_export_data_profile_wai IS NULL)
3080 THEN
3081 raise_application_error (-20001, 'Error: msd_dem_upload_forecast.upload_plng_pctg_with_app_id - Planning Percentage export data profile APP ID NOT provided');
3082 END IF;
3083
3084 IF (p_fcst_export_data_profile_wai IS NULL)
3085 THEN
3086 raise_application_error (-20002, 'Error: msd_dem_upload_forecast.upload_plng_pctg_with_app_id - Total Demand export data profile APP ID NOT provided');
3087 END IF;
3088
3089 IF (p_pctg_series_wai IS NULL)
3090 THEN
3091 raise_application_error (-20003, 'Error: msd_dem_upload_forecast.upload_plng_pctg_with_app_id - Planning Percentage series APP ID NOT provided');
3092 END IF;
3093
3094 x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
3095 IF (x_schema IS NULL)
3096 THEN
3097 raise_application_error (-20004, 'Error: msd_dem_upload_forecast.upload_plng_pctg_with_app_id - Unable to find schema name');
3098 END IF;
3099
3100
3101 /* Get the name of the planning percentage data profile */
3102 BEGIN
3103
3104 EXECUTE IMMEDIATE 'SELECT query_name FROM ' || x_schema || '.TRANSFER_QUERY WHERE application_id = :1 '
3105 INTO x_pp_export_data_profile
3106 USING p_pp_export_data_profile_wai;
3107
3108 EXCEPTION
3109 WHEN OTHERS THEN
3110 /* Alter session to demantra schema */
3111 x_small_sql := 'alter session set current_schema = ' || x_schema;
3112 EXECUTE IMMEDIATE x_small_sql;
3113
3114 raise_application_error (-20005, 'Exception: msd_dem_upload_forecast.upload_forecast_with_app_id - Unable to find plan pct data profile ' || substr(SQLERRM,1,150));
3115 END;
3116
3117 /* Get the name of the total demand data profile */
3118 BEGIN
3119
3120 EXECUTE IMMEDIATE 'SELECT query_name FROM ' || x_schema || '.TRANSFER_QUERY WHERE application_id = :1 '
3121 INTO x_fcst_export_data_profile
3122 USING p_fcst_export_data_profile_wai;
3123
3124 EXCEPTION
3125 WHEN OTHERS THEN
3126 /* Alter session to demantra schema */
3127 x_small_sql := 'alter session set current_schema = ' || x_schema;
3128 EXECUTE IMMEDIATE x_small_sql;
3129
3130 raise_application_error (-20006, 'Exception: msd_dem_upload_forecast.upload_forecast_with_app_id - Unable to find total demand data profile ' || substr(SQLERRM,1,150));
3131 END;
3132
3133 /* Get the internal name of the planning percentage series */
3134 BEGIN
3135
3136 EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
3137 INTO x_pctg_series
3138 USING p_pctg_series_wai;
3139
3140 EXCEPTION
3141 WHEN OTHERS THEN
3142 /* Alter session to demantra schema */
3143 x_small_sql := 'alter session set current_schema = ' || x_schema;
3144 EXECUTE IMMEDIATE x_small_sql;
3145
3146 raise_application_error (-20007, 'Exception: msd_dem_upload_forecast.upload_forecast_with_app_id - Unable to find plan pct series ' || substr(SQLERRM,1,150));
3147 END;
3148
3149 /* Get the internal name of the parent item demand forecast series */
3150 BEGIN
3151
3152 IF (p_parent_item_series_wai IS NOT NULL)
3153 THEN
3154
3155 EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
3156 INTO x_parent_item_series
3157 USING p_parent_item_series_wai;
3158
3159 END IF;
3160
3161 EXCEPTION
3162 WHEN OTHERS THEN
3163 /* Alter session to demantra schema */
3164 x_small_sql := 'alter session set current_schema = ' || x_schema;
3165 EXECUTE IMMEDIATE x_small_sql;
3166
3167 raise_application_error (-20008, 'Exception: msd_dem_upload_forecast.upload_forecast_with_app_id - Unable to find parent item demand fcst series ' || substr(SQLERRM,1,150));
3168 END;
3169
3170 /* Get the internal name of the option item demand forecast series */
3171 BEGIN
3172
3173 IF (p_option_item_series_wai IS NOT NULL)
3174 THEN
3175
3176 EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
3177 INTO x_option_item_series
3178 USING p_option_item_series_wai;
3179
3180 END IF;
3181
3182 EXCEPTION
3183 WHEN OTHERS THEN
3184 /* Alter session to demantra schema */
3185 x_small_sql := 'alter session set current_schema = ' || x_schema;
3186 EXECUTE IMMEDIATE x_small_sql;
3187
3188 raise_application_error (-20009, 'Exception: msd_dem_upload_forecast.upload_forecast_with_app_id - Unable to find option item demand fcst series ' || substr(SQLERRM,1,150));
3189 END;
3190 x_upload_pct_by_base_model := NVL(fnd_profile.value('MSD_DEM_PUB_PLAN_PCT_BY_BASE_MODEL'), 'N');
3191 /* Alter session to demantra schema */
3192 x_small_sql := 'alter session set current_schema = ' || x_schema;
3193 EXECUTE IMMEDIATE x_small_sql;
3194
3195 -- bug#13610330 nallkuma
3196 IF ( p_use_export_profile_view = 1) THEN
3197 upload_planning_percentages (x_pp_export_data_profile, x_fcst_export_data_profile, x_pctg_series, x_parent_item_series, x_option_item_series);
3198 ELSE
3199 upload_cto_plng_pct_direct(x_pp_export_data_profile, x_fcst_export_data_profile, x_pctg_series, x_parent_item_series, x_option_item_series,x_upload_pct_by_base_model,p_exclude_pto_model);
3200 END IF;
3201
3202
3203 EXCEPTION
3204 WHEN OTHERS THEN
3205
3206 /* Alter session to demantra schema */
3207 x_small_sql := 'alter session set current_schema = ' || x_schema;
3208 EXECUTE IMMEDIATE x_small_sql;
3209
3210 raise_application_error (-20015, 'Exception: msd_dem_upload_forecast.upload_plng_pctg_with_app_id - ' || substr(SQLERRM,1,150));
3211
3212 END UPLOAD_PLNG_PCTG_WITH_APP_ID;
3213
3214
3215
3216 /*
3217 * This procedure is a wraper on top of existing procedure UPLOAD_TOTAL_DEMAND
3218 * This procedure accepts Application_IDs as arguments instead of data profile names.
3219 * The procedure gets the data profile names from Demantra and then calls UPLOAD_TOTAL_DEMAND
3220 * The parameters are -
3221 * p_ind_export_data_profile_wai - Application Id of the export data profile used to export independent demand
3222 * p_dep_export_data_profile_wai - Application Id of the export data profile used to export dependent demand
3223 * p_ind_fcst_series_wai - Application Id of the series which holds independent demand
3224 * p_dep_fcst_series_wai - Application Id of the series which holds dependent demand
3225 */
3226 PROCEDURE UPLOAD_CTO_FCST_WITH_APP_ID (
3227 p_ind_export_data_profile_wai IN VARCHAR2,
3228 p_dep_export_data_profile_wai IN VARCHAR2,
3229 p_ind_fcst_series_wai IN VARCHAR2,
3230 p_dep_fcst_series_wai IN VARCHAR2,
3231 p_upload_unplanned_components IN VARCHAR2 DEFAULT 0 --adding for bug#13393529, upload ind fcst for unplanned items with pick_components_flag = 'Y'
3232 )
3233 IS
3234
3235 x_small_sql VARCHAR2(600) := NULL;
3236 x_schema VARCHAR2(50) := NULL;
3237
3238 x_ind_export_data_profile VARCHAR2(255) := NULL;
3239 x_dep_export_data_profile VARCHAR2(255) := NULL;
3240 x_ind_fcst_series VARCHAR2(50) := NULL;
3241 x_dep_fcst_series VARCHAR2(50) := NULL;
3242
3243 BEGIN
3244
3245 IF ( p_ind_export_data_profile_wai IS NULL
3246 OR p_dep_export_data_profile_wai IS NULL
3247 OR p_ind_fcst_series_wai IS NULL
3248 OR p_dep_fcst_series_wai IS NULL)
3249 THEN
3250 raise_application_error (-20001, 'Error: msd_dem_upload_forecast.upload_cto_fcst_with_app_id - All the four input parameters must be specified');
3251 END IF;
3252
3253
3254 x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
3255 IF (x_schema IS NULL)
3256 THEN
3257 raise_application_error (-20002, 'Error: msd_dem_upload_forecast.upload_cto_fcst_with_app_id - Unable to find schema name');
3258 END IF;
3259
3260
3261 /* Get the name of the independent demand data profile */
3262 BEGIN
3263
3264 EXECUTE IMMEDIATE 'SELECT query_name FROM ' || x_schema || '.TRANSFER_QUERY WHERE application_id = :1 '
3265 INTO x_ind_export_data_profile
3266 USING p_ind_export_data_profile_wai;
3267
3268 EXCEPTION
3269 WHEN OTHERS THEN
3270 /* Alter session to demantra schema */
3271 x_small_sql := 'alter session set current_schema = ' || x_schema;
3272 EXECUTE IMMEDIATE x_small_sql;
3273
3274 raise_application_error (-20003, 'Exception: msd_dem_upload_forecast.upload_cto_fcst_with_app_id - Unable to find independent demand data profile ' || substr(SQLERRM,1,150));
3275 END;
3276
3277 /* Get the name of the dependent demand data profile */
3278 BEGIN
3279
3280 EXECUTE IMMEDIATE 'SELECT query_name FROM ' || x_schema || '.TRANSFER_QUERY WHERE application_id = :1 '
3281 INTO x_dep_export_data_profile
3282 USING p_dep_export_data_profile_wai;
3283
3284 EXCEPTION
3285 WHEN OTHERS THEN
3286 /* Alter session to demantra schema */
3287 x_small_sql := 'alter session set current_schema = ' || x_schema;
3288 EXECUTE IMMEDIATE x_small_sql;
3289
3290 raise_application_error (-20004, 'Exception: msd_dem_upload_forecast.upload_cto_fcst_with_app_id - Unable to find dependent demand data profile ' || substr(SQLERRM,1,150));
3291 END;
3292
3293 /* Get the internal name of the independent demand forecast series */
3294 BEGIN
3295
3296 EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
3297 INTO x_ind_fcst_series
3298 USING p_ind_fcst_series_wai;
3299
3300 EXCEPTION
3301 WHEN OTHERS THEN
3302 /* Alter session to demantra schema */
3303 x_small_sql := 'alter session set current_schema = ' || x_schema;
3304 EXECUTE IMMEDIATE x_small_sql;
3305
3306 raise_application_error (-20005, 'Exception: msd_dem_upload_forecast.upload_cto_fcst_with_app_id - Unable to find independent demand series ' || substr(SQLERRM,1,150));
3307 END;
3308
3309 /* Get the internal name of the dependent demand forecast series */
3310 BEGIN
3311
3312 EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
3313 INTO x_dep_fcst_series
3314 USING p_dep_fcst_series_wai;
3315
3316 EXCEPTION
3317 WHEN OTHERS THEN
3318 /* Alter session to demantra schema */
3319 x_small_sql := 'alter session set current_schema = ' || x_schema;
3320 EXECUTE IMMEDIATE x_small_sql;
3321
3322 raise_application_error (-20005, 'Exception: msd_dem_upload_forecast.upload_cto_fcst_with_app_id - Unable to find dependent demand series ' || substr(SQLERRM,1,150));
3323 END;
3324
3325 /* Alter session to demantra schema */
3326 x_small_sql := 'alter session set current_schema = ' || x_schema;
3327 EXECUTE IMMEDIATE x_small_sql;
3328
3329
3330
3331 upload_total_demand (x_ind_export_data_profile, x_dep_export_data_profile, x_ind_fcst_series, x_dep_fcst_series,p_upload_unplanned_components);
3332
3333
3334 EXCEPTION
3335 WHEN OTHERS THEN
3336
3337 /* Alter session to demantra schema */
3338 x_small_sql := 'alter session set current_schema = ' || x_schema;
3339 EXECUTE IMMEDIATE x_small_sql;
3340
3341 raise_application_error (-20015, 'Exception: msd_dem_upload_forecast.upload_cto_fcst_with_app_id - ' || substr(SQLERRM,1,150));
3342
3343 END UPLOAD_CTO_FCST_WITH_APP_ID;
3344
3345 /*
3346 * This procedure will load data into MSC_DMD_SCN_METRICS from
3347 * demantra's data profile - SPF Upload Metrics's view.
3348 * Arguments -
3349 * 1. SPF Upload Metrics data profile appl id
3350 * 2. SPF Upload Final Forecast data profile appl id
3351 * Fetches SPF Upload Metrics and SPF Upload Final Forecast
3352 * export data profile info.
3353 * Uses the 'SPF Upload Final Forecast' export profile id as
3354 * the scenario id for loading data/metrics into MSC_DMD_SCN_METRICS
3355 * Data is deleted first in the table against the scenario_id
3356 * then re-loaded with the new data.
3357 * Info about the loading of the data is logged in integ_status table.
3358 */
3359 PROCEDURE UPLOAD_SPF_METRICS (
3360 p_dp_metrics_appid IN VARCHAR2,
3361 p_dp_final_forecast_appid IN VARCHAR2 )
3362 AS
3363
3364 TYPE CUR_TYPE IS REF CURSOR;
3365 x_cur_type CUR_TYPE;
3366
3367 x_errbuf VARCHAR2(200) := NULL;
3368 x_retcode VARCHAR2(100) := NULL;
3369
3370 x_sql VARCHAR2(2000) := NULL;
3371 x_table_name VARCHAR2(50) := NULL;
3372 x_schema VARCHAR(50) := NULL;
3373 x_fnd_user_id number := FND_GLOBAL.USER_ID();
3374
3375 x_metrics_profile_id NUMBER := NULL;
3376 x_final_forecast_profile_id NUMBER := NULL;
3377 x_dp_metrics_appid VARCHAR2(50) := NULL;
3378 x_dp_final_forecast_appid VARCHAR2(50) := NULL;
3379
3380 x_presentation_type NUMBER := NULL;
3381 x_view_name VARCHAR2(30) := NULL;
3382 x_query_name VARCHAR2(50) := NULL;
3383 x_time_res_id NUMBER := NULL;
3384 x_unit_id NUMBER := NULL;
3385 x_index_id NUMBER := NULL;
3386 x_data_scale NUMBER := NULL;
3387 x_integration_type NUMBER := NULL;
3388 x_export_type NUMBER := NULL;
3389 x_last_export_date DATE := NULL;
3390 x_is_view_present NUMBER := 0;
3391
3392
3393 x_scenario_id NUMBER := NULL;
3394
3395 x_select_clause VARCHAR2(3000) := NULL;
3396 x_from_clause VARCHAR2(500) := NULL;
3397 x_where_clause VARCHAR2(3000) := NULL;
3398 x_insert_clause VARCHAR2(1000) := NULL;
3399 x_small_sql VARCHAR2(600) := NULL;
3400 x_large_sql VARCHAR2(6000) := NULL;
3401
3402 x_num_rows NUMBER := 0;
3403
3404 var_boolean boolean;
3405 var_dummy1 varchar2(100);
3406 var_dummy2 varchar2(100);
3407 var_msc_schema_name varchar2(50);
3408
3409 BEGIN
3410
3411 /* Alter session to APPS */
3412 x_small_sql := 'alter session set current_schema = APPS';
3413 EXECUTE IMMEDIATE x_small_sql;
3414
3415
3416 x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
3417 IF (x_schema IS NULL)
3418 THEN
3419 raise_application_error (-20001, 'Error: msd_dem_upload_forecast.upload_spf_metrics - Unable to find schema name');
3420 END IF;
3421
3422
3423 /* Initialize global variables */
3424 IF (p_dp_metrics_appid IS NULL or p_dp_final_forecast_appid IS NULL )
3425 THEN
3426 raise_application_error (-20002, 'Error: msd_dem_upload_forecast.upload_spf_metrics - No export data profile name provided');
3427 ELSE
3428 x_dp_metrics_appid := upper(p_dp_metrics_appid);
3429 x_dp_final_forecast_appid := upper(p_dp_final_forecast_appid);
3430 END IF;
3431
3432 /* Get the metrics export data profile info */
3433 x_table_name := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'TRANSFER_QUERY');
3434 x_sql := 'SELECT id, presentation_type, view_name, query_name, ' ||
3435 ' time_res_id, unit_id, index_id, data_scale, ' ||
3436 ' integration_type, export_type, last_export_date ' ||
3437 ' FROM ' || x_table_name ||
3438 ' WHERE upper(application_id) = ''' || x_dp_metrics_appid || '''';
3439
3440 OPEN x_cur_type FOR x_sql;
3441 FETCH x_cur_type INTO x_metrics_profile_id,
3442 x_presentation_type,
3443 x_view_name,
3444 x_query_name,
3445 x_time_res_id,
3446 x_unit_id,
3447 x_index_id,
3448 x_data_scale,
3449 x_integration_type,
3450 x_export_type,
3451 x_last_export_date;
3452 CLOSE x_cur_type;
3453
3454 /* Log the DP name into integ_status table */
3455 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
3456 VS_MSG_LOADING || ' ' || x_query_name || ''' , ''' || VS_MSG_STARTED || ''' ); END;';
3457
3458 EXECUTE IMMEDIATE x_small_sql;
3459
3460
3461 /* Get the final forecast export data profile id for using it for
3462 * generating scenario_id
3463 */
3464 x_sql := 'SELECT id ' ||
3465 ' FROM ' || x_table_name ||
3466 ' WHERE upper(application_id) = ''' || x_dp_final_forecast_appid || '''';
3467
3468 OPEN x_cur_type FOR x_sql;
3469 FETCH x_cur_type INTO x_final_forecast_profile_id;
3470 CLOSE x_cur_type;
3471
3472
3473 x_sql := 'SELECT count(1) FROM dba_objects ' ||
3474 ' WHERE owner = upper(''' || x_schema || ''')' ||
3475 ' AND object_type IN (''VIEW'', ''MATERIALIZED VIEW'') ' ||
3476 ' AND object_name = upper(''' || x_view_name || ''')';
3477 EXECUTE IMMEDIATE x_sql INTO x_is_view_present;
3478
3479
3480 /*** Check basic error conditions - BEGIN ***/
3481
3482 IF (x_metrics_profile_id IS NULL)
3483 THEN
3484 raise_application_error (-20003, 'Error: msd_dem_upload_forecast.upload_spf_metrics - Unable to get export data profile id');
3485 ELSIF (x_integration_type = C_IMPORT_DATA_PROFILE)
3486 THEN
3487 raise_application_error (-20004, 'Error: msd_dem_upload_forecast.upload_spf_metrics - ' || x_query_name || 'is not an export data profile');
3488 ELSIF (x_export_type = C_EXPORT_TYPE_INCR)
3489 THEN
3490 raise_application_error (-20005, 'Error: msd_dem_upload_forecast.upload_spf_metrics - Incremental export type is not supported');
3491 ELSIF (x_index_id IS NOT NULL)
3492 THEN
3493 raise_application_error (-20006, 'Error: msd_dem_upload_forecast.upload_spf_metrics - Forecast amount cannot be uploaded');
3494 ELSIF (x_is_view_present = 0)
3495 THEN
3496 raise_application_error (-20007, 'Error: msd_dem_upload_forecast.upload_spf_metrics - Forecast has not yet been exported');
3497 ELSIF (x_presentation_type = C_PSNT_TYPE_DESC)
3498 THEN
3499 raise_application_error (-20008, 'Error: msd_dem_upload_forecast.upload_spf_metrics - Presentation type must by Code');
3500 END IF;
3501
3502 /*** Check basic error conditions - END ***/
3503
3504 x_scenario_id := C_SCENARIO_ID_OFFSET + x_final_forecast_profile_id ;
3505
3506 x_select_clause := ' ( SELECT ' || '-1, '
3507 || x_scenario_id || ', '
3508 || ' METRICS_TB.inventory_item_id, '
3509 || ' METRICS_TB.organization_id, '
3510 || ' METRICS_TB.sr_instance_id, '
3511 || ' METRICS_TB.acry_mape_spf_insamp, '
3512 || ' METRICS_TB.acry_mape_spf_outsamp, '
3513 || ' METRICS_TB.spf_fore_vol, '
3514 || ' METRICS_TB.spf_glob_prop, '
3515 || x_fnd_user_id || ', '
3516 || ' SYSTIMESTAMP, '
3517 || ' SYSTIMESTAMP, '
3518 || x_fnd_user_id || ', '
3519 || ' FND_GLOBAL.LOGIN_ID ' ;
3520
3521
3522 x_from_clause := ' FROM ( SELECT '
3523 || ' msi.inventory_item_id inventory_item_id, '
3524 || ' msi.organization_id organization_id, '
3525 || ' msi.sr_instance_id sr_instance_id, '
3526 || ' avg(exp.acry_mape_spf_insamp)*100 acry_mape_spf_insamp, '
3527 || ' avg(exp.acry_mape_spf_outsamp)*100 acry_mape_spf_outsamp, '
3528 || ' avg(exp.spf_fore_vol)*100 spf_fore_vol, '
3529 || ' avg(exp.spf_glob_prop) spf_glob_prop '
3530 || ' FROM ' || x_schema || '.' || x_view_name
3531 || ' exp, msc_system_items msi, msc_trading_partners mtp_org ';
3532
3533 x_where_clause := ' WHERE mtp_org.partner_type = 3 ' ||
3534 ' AND exp.LEVEL2 = mtp_org.organization_code ' ||
3535 ' AND msi.plan_id = -1 ' ||
3536 ' AND msi.sr_instance_id = mtp_org.sr_instance_id ' ||
3537 ' AND msi.organization_id = mtp_org.sr_tp_id ' ||
3538 ' AND msi.item_name = exp.LEVEL1 ' ||
3539 ' GROUP BY msi.inventory_item_id, msi.organization_id, msi.sr_instance_id ) METRICS_TB )' ;
3540
3541
3542 x_insert_clause := ' INSERT /*+ APPEND */ INTO MSC_DMD_SCN_METRICS nologging ( ' ||
3543 ' PLAN_ID, ' ||
3544 ' SCENARIO_ID, ' ||
3545 ' INVENTORY_ITEM_ID, ' ||
3546 ' ORGANIZATION_ID, ' ||
3547 ' SR_INSTANCE_ID, ' ||
3548 ' MAPE_IN_SAMPLE, ' ||
3549 ' MAPE_OUT_SAMPLE, ' ||
3550 ' FORECAST_VOLATILITY, ' ||
3551 ' AVG_DEMAND, '||
3552 ' CREATED_BY, ' ||
3553 ' CREATION_DATE, ' ||
3554 ' LAST_UPDATE_DATE, ' ||
3555 ' LAST_UPDATED_BY, ' ||
3556 ' LAST_UPDATE_LOGIN ) ';
3557
3558 x_large_sql := x_insert_clause || x_select_clause || x_from_clause || x_where_clause;
3559
3560 /* Delete all data in the denorm for the export data profile */
3561 DELETE FROM MSC_DMD_SCN_METRICS
3562 WHERE plan_id = -1
3563 AND scenario_id = x_scenario_id;
3564
3565 COMMIT;
3566
3567 /* Insert metrics into MSC_DMD_SCN_METRICS table */
3568 EXECUTE IMMEDIATE x_large_sql;
3569 x_num_rows := SQL%ROWCOUNT;
3570
3571
3572
3573 /* Call Custom Hook for Upload */
3574
3575 msd_dem_custom_hooks.upload_hook (
3576 x_errbuf,
3577 x_retcode);
3578
3579 IF (x_retcode = -1)
3580 THEN
3581
3582 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
3583 VS_MSG_LOADED || ' ' || x_query_name || ''' , ''' || VS_MSG_LOADE_ERROR || ''',''' || x_errbuf || ''' ); END;';
3584
3585 EXECUTE IMMEDIATE x_small_sql;
3586
3587 raise_application_error (-20014, 'Error: msd_dem_upload_forecast.upload_spf_metrics - Error in call to custom hook msd_dem_custom_hooks.upload_hook');
3588 END IF;
3589
3590 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
3591 VS_MSG_LOADED || ' ' || x_query_name || ''' , ''' || VS_MSG_SUCCEEDED || ''' ); END;';
3592
3593 EXECUTE IMMEDIATE x_small_sql;
3594
3595 COMMIT;
3596
3597 var_boolean := fnd_installation.get_app_info ('MSC', var_dummy1, var_dummy2, var_msc_schema_name);
3598 msd_dem_collect_history_data.analyze_table (
3599 x_errbuf,
3600 x_retcode,
3601 var_msc_schema_name || '.MSC_DMD_SCN_METRICS');
3602
3603 /* Alter session to demantra schema */
3604 x_small_sql := 'alter session set current_schema = ' || x_schema;
3605 EXECUTE IMMEDIATE x_small_sql;
3606
3607 EXCEPTION
3608 WHEN OTHERS THEN
3609 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
3610 VS_MSG_LOADED || ' ' || x_query_name || ''' , ''' || VS_MSG_LOADE_ERROR || ''',''' || substr(SQLERRM,1,150) || ''' ); END;';
3611
3612 EXECUTE IMMEDIATE x_small_sql;
3613
3614 x_small_sql := 'alter session set current_schema = ' || x_schema;
3615 EXECUTE IMMEDIATE x_small_sql;
3616
3617 raise_application_error (-20001, 'Error: msd_dem_upload_forecast.upload_spf_metrics - '|| substr(sqlerrm,1,150));
3618
3619 END UPLOAD_SPF_METRICS;
3620
3621 /*
3622 * This procedure will identify the item/org tuples that have changed
3623 * after the last upload (full or incremental) was done,
3624 * then updates msd_dp_scn_entries_denorm (quantity column) and
3625 * msc_dmd_scn_metrics (mape_in_sample,mape_out_sample,forecast_volatility,
3626 * avg_demand columns) tables with new values in the 5 series
3627 * (SPF Final Forecast, SPF Forecast MAPE (In Sample), SPF Forecast MAPE (Out of Sample),
3628 * SPF Forecast Volatility, SPF Average Demand)
3629 * Arguments -
3630 * 1. SPF Upload Final Forecast data profile appl id
3631 * 2. SPF Upload Metrics data profile appl id
3632 * 3. SPF Final Forecast series appl id
3633 */
3634 PROCEDURE UPLOAD_SPF_INC_FORECAST (
3635 p_dp_final_forecast_appid IN VARCHAR2,
3636 p_dp_metrics_appid IN VARCHAR2,
3637 p_ff_series_appid IN VARCHAR2 DEFAULT NULL)
3638
3639 AS
3640
3641 TYPE CUR_TYPE IS REF CURSOR;
3642 x_cur_type CUR_TYPE;
3643
3644 x_errbuf VARCHAR2(200) := NULL;
3645 x_retcode VARCHAR2(100) := NULL;
3646
3647 x_sql VARCHAR2(6000) := NULL;
3648 x_table_name VARCHAR2(50) := NULL;
3649 x_schema VARCHAR2(50) := NULL;
3650 x_fcst_start_date VARCHAR2(50) := null;
3651 x_fcst_last_date VARCHAR2(50) := null;
3652 x_fnd_user_id NUMBER := FND_GLOBAL.USER_ID();
3653 x_fnd_login_id NUMBER := FND_GLOBAL.LOGIN_ID();
3654
3655
3656 x_dp_metrics_appid VARCHAR2(50) := NULL;
3657 x_dp_final_forecast_appid VARCHAR2(50) := NULL;
3658
3659 /* for 'SPF Upload Final Forecast data' data profile */
3660 x_ffcast_profile_id NUMBER := NULL;
3661 x_ffcast_presentation_type NUMBER := NULL;
3662 x_ffcast_view_name VARCHAR2(30) := NULL;
3663 x_ffcast_query_name VARCHAR2(50) := NULL;
3664 x_ffcast_time_res_id NUMBER := NULL;
3665 x_ffcast_unit_id NUMBER := NULL;
3666 x_ffcast_index_id NUMBER := NULL;
3667 x_ffcast_data_scale NUMBER := NULL;
3668 x_ffcast_integration_type NUMBER := NULL;
3669 x_ffcast_export_type NUMBER := NULL;
3670 x_ffcast_last_export_date DATE := NULL;
3671 x_ffcast_is_view_present NUMBER := 0;
3672
3673 /* for 'SPF Upload Metrics' export data profile */
3674 x_metrics_profile_id NUMBER := NULL;
3675 x_metrics_presentation_type NUMBER := NULL;
3676 x_metrics_view_name VARCHAR2(30) := NULL;
3677 x_metrics_query_name VARCHAR2(50) := NULL;
3678 x_metrics_time_res_id NUMBER := NULL;
3679 x_metrics_unit_id NUMBER := NULL;
3680 x_metrics_index_id NUMBER := NULL;
3681 x_metrics_data_scale NUMBER := NULL;
3682 x_metrics_integration_type NUMBER := NULL;
3683 x_metrics_export_type NUMBER := NULL;
3684 x_metrics_last_export_date DATE := NULL;
3685 x_metrics_is_view_present NUMBER := 0;
3686
3687 x_last_upld_time VARCHAR2(100) := NULL;
3688
3689 x_select_clause VARCHAR2(3000) := NULL;
3690 x_from_clause VARCHAR2(500) := NULL;
3691 x_where_clause VARCHAR2(3000) := NULL;
3692 x_insert_clause VARCHAR2(1000) := NULL;
3693 x_small_sql VARCHAR2(600) := NULL;
3694 x_large_sql VARCHAR2(6000) := NULL;
3695
3696 x_num_rows NUMBER := 0;
3697
3698 var_boolean boolean;
3699 var_dummy1 varchar2(100);
3700 var_dummy2 varchar2(100);
3701 var_msc_schema_name varchar2(50);
3702 var_msd_schema_name varchar2(50);
3703
3704 /* server exp for series */
3705 x_se_spf_ff VARCHAR2(1000) := NULL;
3706 x_se_spf_fv VARCHAR2(500) := NULL;
3707 x_se_spf_fmout VARCHAR2(500) := NULL;
3708 x_se_spf_fmin VARCHAR2(500) := NULL;
3709 x_se_spf_ad VARCHAR2(500) := NULL;
3710
3711 /* Record for storing the 4 metrics series data */
3712 TYPE metrics_rcd
3713 IS RECORD (
3714 inventory_item_id number,
3715 organization_id number,
3716 sr_instance_id number,
3717 metrics_mape number,
3718 acry_mape_spf_outsamp number,
3719 spf_fore_vol number,
3720 glob_prop number);
3721
3722 /* Table type for storing the metrics series data */
3723 TYPE metrics_tab IS TABLE OF metrics_rcd INDEX BY BINARY_INTEGER;
3724 metrics_table metrics_tab;
3725
3726 /* Record for storing the forecast series data */
3727 TYPE forecast_rcd
3728 IS RECORD (
3729 inventory_item_id number,
3730 sr_organization_id number,
3731 sr_instance_id number,
3732 start_time date,
3733 fcst_spf_final number);
3734
3735 /* Table type for storing the forecast series data */
3736 TYPE forecast_tab IS TABLE OF forecast_rcd INDEX BY BINARY_INTEGER;
3737 forecast_table forecast_tab;
3738
3739 BEGIN
3740
3741 /* Alter session to APPS */
3742 x_small_sql := 'alter session set current_schema = APPS';
3743 EXECUTE IMMEDIATE x_small_sql;
3744
3745
3746 x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
3747 IF (x_schema IS NULL)
3748 THEN
3749 raise_application_error (-20001, 'Error: msd_dem_upload_forecast.upload_spf_inc_forecast - Unable to find schema name');
3750 END IF;
3751
3752
3753 /* Initialize global variables */
3754 IF (p_dp_final_forecast_appid IS NULL or p_dp_metrics_appid IS NULL )
3755 THEN
3756 raise_application_error (-20002, 'Error: msd_dem_upload_forecast.upload_spf_inc_forecast - No export data profile name provided');
3757 ELSE
3758 x_dp_metrics_appid := upper(p_dp_metrics_appid);
3759 x_dp_final_forecast_appid := upper(p_dp_final_forecast_appid);
3760 END IF;
3761
3762 /* Get the 'SPF Upload Metrics' export data profile info */
3763 x_table_name := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'TRANSFER_QUERY');
3764 x_sql := 'SELECT id, presentation_type, view_name, query_name, ' ||
3765 ' time_res_id, unit_id, index_id, data_scale, ' ||
3766 ' integration_type, export_type, last_export_date ' ||
3767 ' FROM ' || x_table_name ||
3768 ' WHERE upper(application_id) = ''' || x_dp_metrics_appid || '''';
3769
3770 OPEN x_cur_type FOR x_sql;
3771 FETCH x_cur_type INTO x_metrics_profile_id,
3772 x_metrics_presentation_type,
3773 x_metrics_view_name,
3774 x_metrics_query_name,
3775 x_metrics_time_res_id,
3776 x_metrics_unit_id,
3777 x_metrics_index_id,
3778 x_metrics_data_scale,
3779 x_metrics_integration_type,
3780 x_metrics_export_type,
3781 x_metrics_last_export_date;
3782 CLOSE x_cur_type;
3783
3784 /* Log start of 'SPF Upload Metrics' DP name into integ_status table */
3785 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
3786 VS_MSG_LOADING || ' ' || x_metrics_query_name || ''' , ''' || VS_MSG_STARTED || ''' ); END;';
3787 EXECUTE IMMEDIATE x_small_sql;
3788
3789 /* Get the 'SPF Upload Final Forecast data' export data profile info */
3790 x_sql := 'SELECT id, presentation_type, view_name, query_name, ' ||
3791 ' time_res_id, unit_id, index_id, data_scale, ' ||
3792 ' integration_type, export_type, last_export_date ' ||
3793 ' FROM ' || x_table_name ||
3794 ' WHERE upper(application_id) = ''' || x_dp_final_forecast_appid || '''';
3795
3796 OPEN x_cur_type FOR x_sql;
3797 FETCH x_cur_type INTO x_ffcast_profile_id,
3798 x_ffcast_presentation_type,
3799 x_ffcast_view_name,
3800 x_ffcast_query_name,
3801 x_ffcast_time_res_id,
3802 x_ffcast_unit_id,
3803 x_ffcast_index_id,
3804 x_ffcast_data_scale,
3805 x_ffcast_integration_type,
3806 x_ffcast_export_type,
3807 x_ffcast_last_export_date;
3808 CLOSE x_cur_type;
3809
3810 /* Log start of 'SPF Upload Final Forecast data' DP name into integ_status table */
3811 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
3812 VS_MSG_LOADING || ' ' || x_ffcast_query_name || ''' , ''' || VS_MSG_STARTED || ''' ); END;';
3813 EXECUTE IMMEDIATE x_small_sql;
3814
3815 /* Check whether the views for both the data profiles are created in DB */
3816 x_sql := 'SELECT count(1) FROM dba_objects ' ||
3817 ' WHERE owner = upper(''' || x_schema || ''')' ||
3818 ' AND object_type IN (''VIEW'', ''MATERIALIZED VIEW'') ' ||
3819 ' AND object_name = upper(''' || x_metrics_view_name || ''')';
3820 EXECUTE IMMEDIATE x_sql INTO x_metrics_is_view_present;
3821
3822 x_sql := 'SELECT count(1) FROM dba_objects ' ||
3823 ' WHERE owner = upper(''' || x_schema || ''')' ||
3824 ' AND object_type IN (''VIEW'', ''MATERIALIZED VIEW'') ' ||
3825 ' AND object_name = upper(''' || x_ffcast_view_name || ''')';
3826 EXECUTE IMMEDIATE x_sql INTO x_ffcast_is_view_present;
3827
3828
3829 /*** Check basic error conditions - BEGIN ***/
3830
3831 IF (x_metrics_profile_id IS NULL or x_ffcast_profile_id IS NULL)
3832 THEN
3833 raise_application_error (-20003, 'Error: msd_dem_upload_forecast.upload_spf_metrics - Unable to get export data profile id');
3834 ELSIF (x_metrics_integration_type = C_IMPORT_DATA_PROFILE or x_ffcast_integration_type = C_IMPORT_DATA_PROFILE )
3835 THEN
3836 raise_application_error (-20004, 'Error: msd_dem_upload_forecast.upload_spf_metrics - ' || x_metrics_query_name || ' or ' || x_ffcast_query_name || 'is not an export data profile');
3837 ELSIF (x_metrics_export_type = C_EXPORT_TYPE_INCR or x_ffcast_export_type = C_EXPORT_TYPE_INCR)
3838 THEN
3839 raise_application_error (-20005, 'Error: msd_dem_upload_forecast.upload_spf_metrics - Incremental export type is not supported');
3840 ELSIF (x_metrics_index_id IS NOT NULL or x_ffcast_index_id IS NOT NULL)
3841 THEN
3842 raise_application_error (-20006, 'Error: msd_dem_upload_forecast.upload_spf_metrics - Forecast amount cannot be uploaded');
3843 ELSIF (x_metrics_is_view_present = 0 or x_ffcast_is_view_present = 0 )
3844 THEN
3845 raise_application_error (-20007, 'Error: msd_dem_upload_forecast.upload_spf_metrics - Forecast has not yet been exported');
3846 ELSIF (x_metrics_presentation_type = C_PSNT_TYPE_DESC or x_ffcast_presentation_type = C_PSNT_TYPE_DESC)
3847 THEN
3848 raise_application_error (-20008, 'Error: msd_dem_upload_forecast.upload_spf_metrics - Presentation type must by Code');
3849 END IF;
3850
3851 /*** Check basic error conditions - END ***/
3852
3853 /* Get last upload time (full or incremental) for forecast data profiles */
3854 x_small_sql := ' SELECT TO_CHAR(MAX(STATUS_DATE),''DD-MM-YYYY HH24-MI-SS'') FROM ' || x_schema || '.INTEG_STATUS '
3855 || ' WHERE SUBSTR(STAGE,9) = ''' || x_ffcast_query_name || ''''
3856 || ' AND upper(STATUS) = ''SUCCEEDED'''
3857 || ' AND upper(PROCESS) = ''UPLOAD FORECAST''' ;
3858
3859 EXECUTE IMMEDIATE x_small_sql INTO x_last_upld_time ;
3860
3861 /* Query for fetching combinations of item/org that have
3862 * changed after the last upload (incremental or full) has happened.
3863 */
3864 x_insert_clause := ' Insert /*+ APPEND */ into MSD_SPF_MATRIX_COMB nologging ('
3865 || ' T_EP_ITEM_EP_ID, '
3866 || ' T_EP_ORGANIZATION_EP_ID, '
3867 || ' SDATE, '
3868 || ' INVENTORY_ITEM_ID, '
3869 || ' SR_ORGANIZATION_ID, '
3870 || ' SR_INSTANCE_ID ) ';
3871
3872 x_select_clause := ' ( SELECT ITEMS.T_EP_ITEM_EP_ID, LOC.T_EP_ORGANIZATION_EP_ID, SPF.SALES_DATE, '
3873 || 'TEI.EBS_ITEM_DEST_KEY, MTP_ORG.SR_TP_ID, MTP_ORG.SR_INSTANCE_ID ' ;
3874
3875 x_from_clause := ' FROM '
3876 || x_schema || '.T_EP_SPF_DATA SPF, ' || x_schema || '.T_EP_ITEM TEI, ' || x_schema || '.ITEMS, '
3877 || x_schema || '.LOCATION LOC, ' || x_schema || '.T_EP_ORGANIZATION ORG, '
3878 || ' MSC_TRADING_PARTNERS MTP_ORG ' ;
3879
3880 x_where_clause := ' WHERE SPF.LAST_UPDATE_DATE > to_date(''' || x_last_upld_time || ''',''DD-MM-YYYY HH24-MI-SS'') '
3881 || ' AND SPF.ITEM_ID = ITEMS.ITEM_ID '
3882 || ' AND TEI.T_EP_ITEM_EP_ID = ITEMS.T_EP_ITEM_EP_ID '
3883 || ' AND SPF.LOCATION_ID = LOC.LOCATION_ID '
3884 || ' AND ORG.T_EP_ORGANIZATION_EP_ID = LOC.T_EP_ORGANIZATION_EP_ID '
3885 || ' AND MTP_ORG.PARTNER_TYPE = 3 '
3886 || ' AND MTP_ORG.ORGANIZATION_CODE = ORG.ORGANIZATION '
3887 || ' GROUP BY ITEMS.T_EP_ITEM_EP_ID, LOC.T_EP_ORGANIZATION_EP_ID, SPF.SALES_DATE, '
3888 || ' TEI.EBS_ITEM_DEST_KEY, MTP_ORG.SR_TP_ID, MTP_ORG.SR_INSTANCE_ID )' ;
3889
3890 x_large_sql := x_insert_clause || x_select_clause || x_from_clause || x_where_clause;
3891
3892 /* Truncate data in the MSD_SPF_MATRIX_COMB table */
3893 var_boolean := fnd_installation.get_app_info ('MSD', var_dummy1, var_dummy2, var_msd_schema_name);
3894 x_small_sql := 'TRUNCATE table ' || var_msd_schema_name ||'.MSD_SPF_MATRIX_COMB';
3895 EXECUTE IMMEDIATE x_small_sql;
3896
3897 /* Insert item/org combinations into MSD_SPF_MATRIX_COMB table */
3898 EXECUTE IMMEDIATE x_large_sql ;
3899 x_num_rows := SQL%ROWCOUNT;
3900 COMMIT;
3901
3902
3903 /* Get the server expression for all 5 series */
3904 x_small_sql := ' select replace(lower(' || x_schema || '.GET_EXP_WITH_NO_TOKEN(exp_template)),''safe_division'','''
3905 || x_schema || '.safe_division'') from '
3906 || x_schema || '.COMPUTED_FIELDS where application_id = ''COMPUTED_FIELD:4330''';
3907 EXECUTE IMMEDIATE x_small_sql into x_se_spf_ad ;
3908
3909 x_small_sql := ' select replace(lower(' || x_schema || '.GET_EXP_WITH_NO_TOKEN(exp_template)),''safe_division'','''
3910 || x_schema || '.safe_division'') from '
3911 || x_schema || '.COMPUTED_FIELDS where application_id = ''COMPUTED_FIELD:4329''';
3912 EXECUTE IMMEDIATE x_small_sql into x_se_spf_fv ;
3913
3914 /* Bug#13057561 - mpmurali */
3915 IF (p_ff_series_appid IS NULL) THEN
3916
3917 x_small_sql := ' select replace(lower(' || x_schema || '.GET_EXP_WITH_NO_TOKEN(exp_template)),''safe_division'','''
3918 || x_schema || '.safe_division'') from '
3919 || x_schema || '.COMPUTED_FIELDS where application_id = ''COMPUTED_FIELD:4328''';
3920 EXECUTE IMMEDIATE x_small_sql into x_se_spf_ff ;
3921
3922 ELSE
3923 BEGIN
3924 x_small_sql := ' select replace(lower(' || x_schema || '.GET_EXP_WITH_NO_TOKEN(exp_template)),''safe_division'','''
3925 || x_schema || '.safe_division'') from '
3926 || x_schema || '.COMPUTED_FIELDS where application_id = '|| '''' || p_ff_series_appid || '''' ;
3927 EXECUTE IMMEDIATE x_small_sql into x_se_spf_ff ;
3928 EXCEPTION WHEN others THEN
3929 raise_application_error (-20020, 'Error: msd_dem_upload_forecast.upload_spf_inc_forecast - Invalid series application id. ' );
3930 END;
3931
3932 END IF;
3933
3934 x_small_sql := ' select replace(lower(' || x_schema || '.GET_EXP_WITH_NO_TOKEN(exp_template)),''safe_division'','''
3935 || x_schema || '.safe_division'') from '
3936 || x_schema || '.COMPUTED_FIELDS where application_id = ''COMPUTED_FIELD:4327''';
3937 EXECUTE IMMEDIATE x_small_sql into x_se_spf_fmout ;
3938
3939 x_small_sql := ' select replace(lower(' || x_schema || '.GET_EXP_WITH_NO_TOKEN(exp_template)),''safe_division'','''
3940 || x_schema || '.safe_division'') from '
3941 || x_schema || '.COMPUTED_FIELDS where application_id = ''COMPUTED_FIELD:4326''';
3942 EXECUTE IMMEDIATE x_small_sql into x_se_spf_fmin ;
3943
3944
3945
3946
3947 IF (x_se_spf_ff is null or x_se_spf_fv is null or x_se_spf_fmout is null or x_se_spf_fmout is null or x_se_spf_ad is null) THEN
3948 raise_application_error (-20019, 'Error: msd_dem_upload_forecast.upload_spf_inc_forecast - Unable to find server expression for series - '
3949 || 'SPF Final Forecast,SPF Forecast Volatility,SPF Forecast MAPE (Out of Sample),'
3950 || 'SPF Forecast MAPE (In Sample),SPF Average Demand' );
3951 END IF;
3952
3953 /* get forecast start date and last date for the latest executed 'Forecast Install Base' engine */
3954 x_small_sql := 'select to_char(FH2.START_FORECAST_DATE,''DD-MM-YYYY''),to_char(FH2.LAST_FORECAST_DATE,''DD-MM-YYYY'') from ' || x_schema || '.FORECAST_HISTORY FH2 '
3955 || ' where fh2.time_sig = (select max(fh.TIME_SIG) from '
3956 || x_schema || '.FORECAST_HISTORY FH, ' || x_schema || '.ENGINE_PROFILES EP '
3957 || ' where EP.ENGINE_PROFILES_ID = FH.ENGINE_PROFILES_ID '
3958 || ' and EP.APPLICATION_ID = ''ENGINE_PROFILE:121'')' ;
3959 EXECUTE IMMEDIATE x_small_sql into x_fcst_start_date, x_fcst_last_date ;
3960
3961 IF (x_fcst_start_date is null or x_fcst_last_date is null) THEN
3962 raise_application_error (-20019, 'Error: msd_dem_upload_forecast.upload_spf_inc_forecast - Unable to fetch fcst_start_date and fcst_last_date. ');
3963 END IF;
3964
3965 /* Fetch the values of the 4 series(metrics) for a given item/org/instance tuple */
3966 x_sql := ' SELECT METRICS_TBL.inventory_item_id, '
3967 || ' METRICS_TBL.sr_organization_id, '
3968 || ' METRICS_TBL.sr_instance_id, '
3969 || ' avg(METRICS_TBL.spf_fmin)*100, '
3970 || ' avg(METRICS_TBL.spf_fmout)*100, '
3971 || ' avg(METRICS_TBL.spf_fv)*100, '
3972 || ' avg(METRICS_TBL.spf_ad) '
3973 || ' FROM (SELECT comb.inventory_item_id inventory_item_id, '
3974 || ' comb.sr_organization_id sr_organization_id, '
3975 || ' comb.sr_instance_id sr_instance_id, '
3976 || ' branch_data.sales_date, '
3977 || x_se_spf_fmin ||' spf_fmin, '
3978 || x_se_spf_fmout ||' spf_fmout, '
3979 || x_se_spf_fv ||' spf_fv, '
3980 || x_se_spf_ad ||' spf_ad '
3981 || ' FROM (select inventory_item_id, sr_organization_id, sr_instance_id, '
3982 || ' t_ep_item_ep_id, t_ep_organization_ep_id '
3983 || ' from msd_spf_matrix_comb '
3984 || ' group by inventory_item_id, sr_organization_id, sr_instance_id, '
3985 || ' t_ep_item_ep_id, t_ep_organization_ep_id ) comb, '
3986 || x_schema || '.t_ep_spf_data branch_data, '
3987 || x_schema || '.t_ep_spf_matrix, '
3988 || x_schema || '.items, '
3989 || x_schema || '.location loc'
3990 || ' WHERE comb.t_ep_item_ep_id = items.t_ep_item_ep_id'
3991 || ' AND comb.t_ep_organization_ep_id = loc.t_ep_organization_ep_id'
3992 || ' AND branch_data.t_ep_spf_id = t_ep_spf_matrix.t_ep_spf_id '
3993 || ' AND branch_data.item_id = items.item_id'
3994 || ' AND branch_data.sales_date BETWEEN to_date(''' || x_fcst_start_date || ''',''DD-MM-YYYY'')'
3995 || ' AND to_date(''' || x_fcst_last_date || ''',''DD-MM-YYYY'')'
3996 || ' AND branch_data.location_id = loc.location_id '
3997 || ' AND t_ep_spf_matrix.item_id = items.item_id'
3998 || ' AND t_ep_spf_matrix.location_id = loc.location_id '
3999 || ' GROUP BY comb.inventory_item_id, comb.sr_organization_id, comb.sr_instance_id, branch_data.sales_date) METRICS_TBL'
4000 || ' GROUP BY metrics_tbl.inventory_item_id, metrics_tbl.sr_organization_id, metrics_tbl.sr_instance_id ';
4001
4002 EXECUTE IMMEDIATE x_sql BULK COLLECT INTO metrics_table;
4003
4004 -- bug#11774264 -- 10g does not allow referencing fields of associative arrays within FORALL
4005 -- so using normal FOR loop instead (this will definitely have impact on performance)
4006 /* BULK update 4 series(metrics) columns of msc_dmd_scn_metrics table */
4007 /*FORALL i IN metrics_table.first..metrics_table.last
4008 UPDATE msc_dmd_scn_metrics
4009 SET
4010 mape_in_sample = metrics_table(i).metrics_mape,
4011 mape_out_sample = metrics_table(i).acry_mape_spf_outsamp ,
4012 forecast_volatility = metrics_table(i).spf_fore_vol ,
4013 avg_demand = metrics_table(i).glob_prop,
4014 last_update_date = SYSTIMESTAMP,
4015 last_updated_by = x_fnd_user_id,
4016 last_update_login = x_fnd_login_id
4017 WHERE
4018 plan_id = -1
4019 AND inventory_item_id = metrics_table(i).inventory_item_id
4020 AND organization_id = metrics_table(i).organization_id
4021 AND sr_instance_id = metrics_table(i).sr_instance_id
4022 AND scenario_id = x_ffcast_profile_id + C_SCENARIO_ID_OFFSET ;*/
4023
4024 FOR i IN metrics_table.first..metrics_table.last
4025 loop
4026 UPDATE msc_dmd_scn_metrics
4027 SET
4028 mape_in_sample = metrics_table(i).metrics_mape,
4029 mape_out_sample = metrics_table(i).acry_mape_spf_outsamp ,
4030 forecast_volatility = metrics_table(i).spf_fore_vol ,
4031 avg_demand = metrics_table(i).glob_prop,
4032 last_update_date = SYSTIMESTAMP,
4033 last_updated_by = x_fnd_user_id,
4034 last_update_login = x_fnd_login_id
4035 WHERE
4036 plan_id = -1
4037 AND inventory_item_id = metrics_table(i).inventory_item_id
4038 AND organization_id = metrics_table(i).organization_id
4039 AND sr_instance_id = metrics_table(i).sr_instance_id
4040 AND scenario_id = x_ffcast_profile_id + C_SCENARIO_ID_OFFSET ;
4041 end loop;
4042
4043 /* Log success of 'SPF Upload Metrics' DP name into integ_status table */
4044 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
4045 VS_MSG_LOADED || ' ' || x_metrics_query_name || ''' , ''' || VS_MSG_SUCCEEDED || ''' ); END;';
4046 EXECUTE IMMEDIATE x_small_sql;
4047
4048 COMMIT;
4049
4050
4051 /* Fetch the values of forecast series for a given item/org/instance/date tuple */
4052 x_sql := ' SELECT comb.inventory_item_id, comb.sr_organization_id, comb.sr_instance_id, comb.sdate, '
4053 || x_se_spf_ff
4054 || ' FROM msd_spf_matrix_comb comb, '
4055 || x_schema || '.t_ep_spf_data branch_data, '
4056 || x_schema || '.t_ep_spf_matrix, '
4057 || x_schema || '.items, '
4058 || x_schema || '.location loc'
4059 || ' WHERE comb.t_ep_item_ep_id = items.t_ep_item_ep_id'
4060 || ' AND comb.t_ep_organization_ep_id = loc.t_ep_organization_ep_id'
4061 || ' AND branch_data.t_ep_spf_id = t_ep_spf_matrix.t_ep_spf_id '
4062 || ' and branch_data.item_id = t_ep_spf_matrix.item_id '
4063 || ' and branch_data.location_id = t_ep_spf_matrix.location_id '
4064 || ' AND comb.sdate = branch_data.sales_date '
4065 || ' AND comb.sdate = branch_data.sales_date '
4066 || ' AND branch_data.item_id = items.item_id'
4067 || ' AND branch_data.location_id = loc.location_id '
4068 || ' GROUP BY comb.inventory_item_id, comb.sr_organization_id, comb.sr_instance_id, comb.sdate' ;
4069
4070 EXECUTE IMMEDIATE x_sql BULK COLLECT INTO forecast_table;
4071
4072 -- bug#11774264 -- 10g does not allow referencing fields of associative arrays within FORALL
4073 -- so using normal FOR loop instead (this will definitely have impact on performance)
4074 /* BULK update qty column of msd_dp_scn_entries_denorm table */
4075 /*FORALL i IN forecast_table.first..forecast_table.last
4076 UPDATE msd_dp_scn_entries_denorm
4077 SET
4078 quantity = forecast_table(i).fcst_spf_final,
4079 last_update_login = x_fnd_login_id
4080 WHERE
4081 inventory_item_id = forecast_table(i).inventory_item_id
4082 AND sr_organization_id = forecast_table(i).sr_organization_id
4083 AND sr_instance_id = forecast_table(i).sr_instance_id
4084 AND start_time = forecast_table(i).start_time
4085 AND scenario_id = x_ffcast_profile_id + C_SCENARIO_ID_OFFSET ;*/
4086
4087 FOR i IN forecast_table.first..forecast_table.last
4088 loop
4089 UPDATE msd_dp_scn_entries_denorm
4090 SET
4091 quantity = forecast_table(i).fcst_spf_final,
4092 last_update_login = x_fnd_login_id
4093 WHERE
4094 inventory_item_id = forecast_table(i).inventory_item_id
4095 AND sr_organization_id = forecast_table(i).sr_organization_id
4096 AND sr_instance_id = forecast_table(i).sr_instance_id
4097 AND start_time = forecast_table(i).start_time
4098 AND scenario_id = x_ffcast_profile_id + C_SCENARIO_ID_OFFSET ;
4099 end loop;
4100
4101 /* Log success of 'SPF Upload Final Forecast data' DP name into integ_status table */
4102 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
4103 VS_MSG_LOADED || ' ' || x_ffcast_query_name || ''' , ''' || VS_MSG_SUCCEEDED || ''' ); END;';
4104 EXECUTE IMMEDIATE x_small_sql;
4105
4106 COMMIT;
4107
4108 /* Analyze table MSC_DMD_SCN_METRICS */
4109 var_boolean := fnd_installation.get_app_info ('MSC', var_dummy1, var_dummy2, var_msc_schema_name);
4110 msd_dem_collect_history_data.analyze_table (
4111 x_errbuf,
4112 x_retcode,
4113 var_msc_schema_name || '.MSC_DMD_SCN_METRICS');
4114
4115 /* Analyze table MSD_DP_SCN_ENTRIES_DENORM */
4116 var_boolean := fnd_installation.get_app_info ('MSD', var_dummy1, var_dummy2, var_msd_schema_name);
4117 msd_dem_collect_history_data.analyze_table (
4118 x_errbuf,
4119 x_retcode,
4120 var_msd_schema_name || '.MSD_DP_SCN_ENTRIES_DENORM');
4121
4122 /* Alter session to demantra schema */
4123 x_small_sql := ' alter session set current_schema = ' || x_schema ;
4124 EXECUTE IMMEDIATE x_small_sql;
4125
4126 EXCEPTION
4127 WHEN OTHERS THEN
4128
4129 /* Log error of 'SPF Upload Metrics' DP name into integ_status table */
4130 /*x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
4131 VS_MSG_LOADED || ' ' || x_metrics_query_name || ''' , ''' || VS_MSG_LOADE_ERROR || ''',''' || substr(SQLERRM,1,150) || ''' ); END;';
4132 EXECUTE IMMEDIATE x_small_sql;*/
4133
4134 /* Log error of 'SPF Upload Final Forecast data' DP name into integ_status table */
4135 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
4136 VS_MSG_LOADED || ' ' || x_ffcast_query_name || ''' , ''' || VS_MSG_LOADE_ERROR || ''',''' || substr(SQLERRM,1,150) || ''' ); END;';
4137 EXECUTE IMMEDIATE x_small_sql;
4138
4139 /* Alter session to demantra schema */
4140 x_small_sql := ' alter session set current_schema = ' || x_schema ;
4141 EXECUTE IMMEDIATE x_small_sql;
4142
4143 raise_application_error (-20001, 'Error: msd_dem_upload_forecast.upload_spf_inc_forecast - '|| substr(sqlerrm,1,150));
4144
4145 END UPLOAD_SPF_INC_FORECAST;
4146
4147 /*
4148 * This procedure will keep the last launced workflow in WAIT state
4149 * till the previously launched workflow completes.
4150 * Arguments-
4151 * 1. p_wf_appid - workflow application id
4152 * 2. p_wf_stepid - workflow's wait step id (internal)
4153 * Logic
4154 * Fetches the status of the previously launched workflow instance
4155 * IF Running then sleep for 1 to 3 minutes and run the loop
4156 * IF NOT Running then exit loop/procedure, hence releasing the
4157 * newly launched workflow from 'wait' to 'running' state.
4158 */
4159 PROCEDURE WAIT_UNTIL_UPLOAD (
4160 p_wf_appid IN VARCHAR2,
4161 p_wf_stepid IN VARCHAR2 )
4162 AS
4163
4164 x_sql varchar2(600) := null;
4165 x_dem_schema varchar2(100) := null;
4166 x_wf_status varchar2(20) := 'Running';
4167
4168 BEGIN
4169 x_dem_schema := trim(fnd_profile.value('MSD_DEM_SCHEMA'));
4170
4171 IF (x_dem_schema IS NULL) then
4172 raise_application_error (-20001, 'Error: msd_dem_upload_forecast.wait_until_upload - Unable to find schema name');
4173 END IF;
4174
4175 /* Loop and wait till previously launched workflow instance is running */
4176 WHILE (x_wf_status = 'Running') LOOP
4177
4178 /* Fetching the status of the previously launched workflow instance
4179 * which is not in wait step and still in running status(1).
4180 */
4181 Begin
4182 x_sql := 'SELECT ''Running'' FROM ' || x_dem_schema
4183 || '.WF_PROCESS_LOG WHERE SCHEMA_ID = (select schema_id from ' || x_dem_schema
4184 || '.wf_schemas where application_id = ''' || p_wf_appid || ''')'
4185 || ' AND status = 1 '
4186 || ' AND step_id <> ''' || p_wf_stepid || ''''
4187 || ' AND rownum = 1 ' ;
4188 execute immediate x_sql into x_wf_status ;
4189 Exception
4190 WHEN NO_DATA_FOUND THEN
4191 x_wf_status := 'Not Running' ;
4192 End;
4193
4194 /* if another workflow is running, sleep for 1 to 3 minutes */
4195 IF ( x_wf_status = 'Running' ) then
4196 dbms_lock.sleep(dbms_random.value(60,180));
4197 ELSE
4198 x_wf_status := 'Not Running' ;
4199 End if;
4200
4201 END LOOP;
4202
4203 EXCEPTION
4204 WHEN OTHERS THEN
4205 raise_application_error (-20001, 'Error: msd_dem_upload_forecast.wait_until_upload - '|| substr(sqlerrm,1,150));
4206
4207 END WAIT_UNTIL_UPLOAD;
4208
4209
4210 /*
4211 * This function will check, when did the last upload has run
4212 * Arguments:
4213 * 1. appl id of given forecast data profile
4214 * Returns
4215 * = '0' to launch SPF Upload Data workflow
4216 * = '1' to launch SPF Incremental Upload workflow
4217 * Logic
4218 * Compares the timestamp of engine run and SPF Upload Data workflow
4219 * IF engine_run timestamp is later than SPF Upload Data workflow run timestamp
4220 * THEN launch SPF Upload Data workflow
4221 * ELSE launch SPF Incemental Upload workflow
4222 */
4223 FUNCTION CHECK_UPLOAD_LAST_RUN (
4224 p_dp_final_forecast_appid IN VARCHAR2 )
4225 RETURN NUMBER
4226 IS
4227
4228 x_flag number := null ;
4229 x_sql varchar2(600) := null;
4230 x_dem_schema varchar2(100) := null;
4231 x_dp_name varchar2(100) := null;
4232 x_last_eng_time date := null;
4233 x_last_upld_time date := null;
4234
4235 BEGIN
4236
4237 x_dem_schema := trim(fnd_profile.value('MSD_DEM_SCHEMA'));
4238
4239 IF (x_dem_schema is NULL) THEN
4240 raise_application_error (-20001, 'Error: msd_dem_upload_forecast.check_upload_last_run - Unable to find schema name');
4241 END IF;
4242
4243 /* Fetch the data profile name */
4244 x_sql := 'SELECT TRIM(QUERY_NAME) FROM ' ||x_dem_schema
4245 || '.TRANSFER_QUERY WHERE APPLICATION_ID = '''
4246 || p_dp_final_forecast_appid || '''';
4247 execute immediate x_sql into x_dp_name ;
4248
4249
4250 /* Fetch the last/latest timestamp for the 'SPF Upload Final Forecast' data profile
4251 * (SPF Upload Data workflow)
4252 */
4253 BEGIN
4254 x_sql := ' SELECT MAX(STATUS_DATE) FROM ' || x_dem_schema
4255 || '.INTEG_STATUS WHERE SUBSTR(STAGE,9) = ''' || x_dp_name || ''''
4256 || ' AND upper(STATUS) = ''SUCCEEDED'''
4257 || ' AND upper(PROCESS) = ''UPLOAD FORECAST''';
4258 execute immediate x_sql into x_last_upld_time ;
4259 Exception
4260 WHEN NO_DATA_FOUND THEN
4261 x_last_upld_time := null;
4262 END;
4263
4264
4265 /* Fetch the last/latest timestamp for the engine run */
4266 BEGIN
4267 x_sql := ' select max(fh.TIME_SIG) from '
4268 || x_dem_schema || '.FORECAST_HISTORY FH, ' || x_dem_schema || '.ENGINE_PROFILES EP '
4269 || ' where EP.ENGINE_PROFILES_ID = FH.ENGINE_PROFILES_ID '
4270 || ' and EP.APPLICATION_ID = ''ENGINE_PROFILE:121''';
4271
4272 execute immediate x_sql into x_last_eng_time ;
4273 Exception
4274 WHEN NO_DATA_FOUND THEN
4275 x_last_eng_time := null;
4276 END;
4277
4278 /* If the full upload (SPF Upload Data workflow) is never run
4279 * then return 0 to launch SPF Upload Data workflow
4280 */
4281 IF (x_last_upld_time is null) THEN
4282 x_flag := 0 ;
4283 RETURN x_flag;
4284 END IF;
4285
4286 /* If the 'Forecast Install Base' engine is never run */
4287 IF (x_last_eng_time is null) THEN
4288 raise_application_error (-20020, 'Error: msd_dem_upload_forecast.check_upload_last_run - Forecast_Install_Base engine is never executed ');
4289 RETURN NULL;
4290 END IF;
4291
4292
4293 /* If engine run time is greater than full upload workflow run time
4294 * then return 0 to launch SPF Upload Data workflow
4295 * else return 1 to launch SPF Incremental Upload workflow
4296 */
4297 IF ( x_last_eng_time > x_last_upld_time ) then
4298 x_flag := 0 ;
4299 RETURN x_flag;
4300 else
4301 x_flag := 1 ;
4302 RETURN x_flag;
4303 end if ;
4304
4305 EXCEPTION
4306 WHEN OTHERS THEN
4307 raise_application_error (-20001, 'Error: msd_dem_upload_forecast.check_upload_last_run - '|| substr(sqlerrm,1,150));
4308 RETURN NULL;
4309
4310 END CHECK_UPLOAD_LAST_RUN;
4311
4312
4313
4314 PROCEDURE CHECK_REQUESTS_COMPLETION (
4315 errbuf OUT NOCOPY VARCHAR2,
4316 retcode OUT NOCOPY VARCHAR2,
4317 p_num_requests IN NUMBER,
4318 p_run_time IN DATE,
4319 p_req_table IN OUT NOCOPY REQ_TABLE,
4320 p_check_interval IN NUMBER DEFAULT 60,
4321 p_time_out IN NUMBER DEFAULT 86400,
4322 p_db_link IN VARCHAR2 DEFAULT '')
4323
4324 IS
4325
4326 var_sql VARCHAR2(2000) DEFAULT NULL;
4327 var_num_complete NUMBER DEFAULT 0;
4328 var_num_success NUMBER DEFAULT 0;
4329 var_num_error NUMBER DEFAULT 0;
4330 var_num_warning NUMBER DEFAULT 0;
4331 var_phase_code VARCHAR2(1) DEFAULT NULL;
4332 var_status_code VARCHAR2(1) DEFAULT NULL;
4333 var_stat_message VARCHAR2(50) DEFAULT NULL;
4334 var_start_date NUMBER DEFAULT NULL;
4335 var_start_date_seconds NUMBER DEFAULT NULL;
4336 var_curr_time DATE DEFAULT NULL;
4337 var_curr_date NUMBER DEFAULT NULL;
4338 var_curr_date_seconds NUMBER DEFAULT NULL;
4339 var_elapsed_seconds NUMBER DEFAULT NULL;
4340
4341 BEGIN
4342
4343 msd_dem_common_utilities.log_debug ('Entering msd_spf_collect_history_data.check_requests_completion...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4344
4345 var_start_date := TO_NUMBER(TO_CHAR(p_run_time, 'J'));
4346 var_start_date_seconds := TO_NUMBER(TO_CHAR(p_run_time, 'SSSSS'));
4347
4348 var_sql := 'SELECT phase_code, status_code FROM fnd_concurrent_requests' || p_db_link || ' WHERE request_id = :req_id';
4349
4350 /* Check the status of each request in a loop */
4351 WHILE (var_num_complete <> p_num_requests)
4352 LOOP
4353
4354 FOR I IN p_req_table.FIRST..p_req_table.LAST
4355 LOOP
4356
4357 var_phase_code := NULL;
4358 var_status_code := NULL;
4359
4360 IF (p_req_table(I).is_complete)
4361 THEN
4362 goto end_for_loop;
4363 END IF;
4364
4365 EXECUTE IMMEDIATE var_sql
4366 INTO var_phase_code, var_status_code
4367 USING p_req_table(I).request_id;
4368
4369 IF (var_phase_code = 'C')
4370 THEN
4371
4372 var_num_complete := var_num_complete + 1;
4373 p_req_table(I).is_complete := TRUE;
4374
4375 IF (var_status_code IN ('R', 'I', 'C')) -- Completed Normal
4376 THEN
4377 p_req_table(I).status := 0;
4378 var_num_success := var_num_success + 1;
4379 var_stat_message := 'normally.';
4380 ELSIF (var_status_code IN ('G')) -- Completed with Warnings
4381 THEN
4382 p_req_table(I).status := 1;
4383 var_num_warning := var_num_warning + 1;
4384 var_stat_message := 'with warnings.';
4385 ELSIF (var_status_code IN ('E')) -- Completed with errors
4386 THEN
4387 p_req_table(I).status := -1;
4388 var_num_error := var_num_error + 1;
4389 var_stat_message := 'with errors.';
4390 ELSE -- Anything else
4391 p_req_table(I).status := -5;
4392 var_stat_message := 'unknown status.';
4393 END IF;
4394
4395 msd_dem_common_utilities.log_message('Concurrent Request - ' || to_char(p_req_table(I).request_id) || ' completed ' || var_stat_message);
4396
4397 END IF;
4398
4399 <<end_for_loop>>
4400 null;
4401
4402 END LOOP;
4403
4404 var_curr_time := systimestamp;
4405 var_curr_date := TO_NUMBER(TO_CHAR(var_curr_time, 'J'));
4406 var_curr_date_seconds := TO_NUMBER(TO_CHAR(var_curr_time, 'SSSSS'));
4407 var_elapsed_seconds := (((var_curr_date - var_start_date) * 86400) + (var_curr_date_seconds - var_start_date_seconds));
4408
4409 IF (var_elapsed_seconds >= p_time_out)
4410 THEN
4411
4412 retcode := -1;
4413 errbuf := 'Timeout occured while waiting for requests to finish.';
4414 msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.check_requests_completion - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4415 msd_dem_common_utilities.log_message (errbuf);
4416 RETURN;
4417
4418 END IF;
4419
4420 END LOOP;
4421
4422 IF (var_num_warning > 0)
4423 THEN
4424 retcode := 1;
4425 errbuf := 'Some requests completed with warnings';
4426 msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.check_requests_completion - WARNING ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4427 msd_dem_common_utilities.log_message (errbuf);
4428 END IF;
4429
4430 IF (var_num_error > 0)
4431 THEN
4432 retcode := -1;
4433 errbuf := 'Some requests completed with errors';
4434 msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.check_requests_completion - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4435 msd_dem_common_utilities.log_message (errbuf);
4436 END IF;
4437
4438 IF (var_num_complete <> (var_num_success + var_num_warning + var_num_error))
4439 THEN
4440 retcode := -1;
4441 errbuf := 'Some requests completed with unknown status';
4442 msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.check_requests_completion - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4443 msd_dem_common_utilities.log_message (errbuf);
4444 END IF;
4445
4446 msd_dem_common_utilities.log_debug ('Exiting msd_spf_collect_history_data.check_requests_completion...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4447
4448 EXCEPTION
4449 WHEN OTHERS THEN
4450 retcode := -1;
4451 errbuf := substr(SQLERRM,1,150);
4452 msd_dem_common_utilities.log_message ('msd_spf_collect_history_data.check_requests_completion - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4453 msd_dem_common_utilities.log_message (errbuf);
4454 RETURN;
4455
4456 END CHECK_REQUESTS_COMPLETION;
4457
4458
4459 /*
4460 * This procedure, will upload cmro planning factors to source table
4461 * Actions performed
4462 * 1. Launch workflow, Default:"Export SPF Planning factors"
4463 * 2. Get column names for levels selected in DP, Default:"SPF-cMRO Export Failure Rates"
4464 * 3. Delete from AHL_PLANNING_FACTORS table on source
4465 * 4. Insert into AHL_PLANNING_FACTORS table on source
4466 *
4467 * ------------ PARAMETERS LIST ----------------
4468 * p_sr_instance_id : Instance Id
4469 * p_export_data_profile : Demantra Export data profile appl id
4470 * p_workflow_lookup_code : Demantra Workflow EBS lookup code
4471 *
4472 */
4473 PROCEDURE UPLOAD_CMRO_PLN_FCTRS (
4474 errbuf OUT NOCOPY VARCHAR2,
4475 retcode OUT NOCOPY VARCHAR2,
4476 p_sr_instance_id IN NUMBER,
4477 p_export_data_profile IN VARCHAR2,
4478 p_workflow_lookup_code IN VARCHAR2 ,
4479 p_synchronous IN NUMBER DEFAULT C_YES,
4480 p_check_interval IN NUMBER DEFAULT 60,
4481 p_time_out IN NUMBER DEFAULT 1440
4482
4483 )
4484 IS
4485
4486 /*** DATA TYPES ***/
4487 TYPE LVL_REC IS RECORD (level_name VARCHAR2(100));
4488 TYPE LVL_TABLE IS TABLE OF LVL_REC INDEX BY BINARY_INTEGER;
4489 x_lvl_table LVL_TABLE;
4490
4491 /*** REF CURSORS ***/
4492 TYPE LVL_REF_CURSOR IS REF CURSOR ;
4493 TYPE CUR_TYPE IS REF CURSOR;
4494
4495 /*** Variables ***/
4496 x_lvl_ref_cursor LVL_REF_CURSOR ;
4497 x_cur_type CUR_TYPE ;
4498
4499 var_request_id NUMBER := NULL;
4500 var_errbuf VARCHAR2(1000) := NULL;
4501 var_retcode VARCHAR2(10) := NULL;
4502 var_request_num NUMBER := 0;
4503 var_D2S_dblink VARCHAR2(200) := NULL ;
4504
4505 x_select_clause VARCHAR2(3000) := NULL;
4506 x_from_clause VARCHAR2(1000) := NULL;
4507 x_where_clause VARCHAR2(3000) := NULL;
4508 x_insert_clause VARCHAR2(1000) := NULL;
4509 l_large_sql VARCHAR2(4000);
4510 l_sql VARCHAR2(1000);
4511 x_small_sql VARCHAR2(200);
4512 x_lvl_count NUMBER := 0;
4513 x_dem_schema VARCHAR2(100) := fnd_profile.value('MSD_DEM_SCHEMA');
4514 x_table_name VARCHAR2(100) := NULL ;
4515 x_fnd_user_id NUMBER := FND_GLOBAL.USER_ID();
4516 x_wo_item VARCHAR2(50) := MSD_DEM_COMMON_UTILITIES.GET_WORKORDER_ITEM();
4517 x_spf_master_org NUMBER := NULL;
4518 x_series_name VARCHAR2(50) := NULL ;
4519 x_count NUMBER := 0;
4520
4521 x_scenario_id NUMBER := 0 ;
4522 x_profile_id NUMBER := NULL ;
4523 x_presentation_type NUMBER := NULL ;
4524 x_view_name VARCHAR2(30) := NULL ;
4525 x_query_name VARCHAR2(50) := NULL ;
4526 x_time_res_id NUMBER := NULL ;
4527 x_unit_id NUMBER := NULL ;
4528 x_index_id NUMBER := NULL ;
4529 x_data_scale NUMBER := NULL ;
4530 x_integration_type NUMBER := NULL ;
4531 x_export_type NUMBER := NULL ;
4532 x_last_export_date DATE := NULL ;
4533 x_is_view_present NUMBER := 0 ;
4534
4535 x_AG_level VARCHAR2(30) := NULL;
4536 x_CC_level VARCHAR2(30) := NULL;
4537 x_SPF_VT_level VARCHAR2(30) := NULL;
4538 x_SPF_VST_level VARCHAR2(30) := NULL;
4539 x_MI_level VARCHAR2(30) := NULL;
4540 x_SPF_MT_level VARCHAR2(30) := NULL;
4541 x_AGA1_level VARCHAR2(30) := NULL;
4542 x_AGA2_level VARCHAR2(30) := NULL;
4543 x_ORG_level VARCHAR2(30) := NULL;
4544 x_IT_level VARCHAR2(30) := NULL;
4545
4546
4547 BEGIN
4548
4549 msd_dem_common_utilities.log_debug ('Entering: msd_dem_upload_forecast.upload_cmro_pln_fctrs - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4550 msd_dem_common_utilities.log_debug (' ');
4551
4552 /* Log the input parameters to the log file */
4553 msd_dem_common_utilities.log_message('----------------------------------Input Parameters - Begin------------------------------------------');
4554
4555 msd_dem_common_utilities.log_message(' ' || rpad('Instance Id', 30) || ' - ' || to_char(p_sr_instance_id));
4556 msd_dem_common_utilities.log_message(' ' || rpad('Data Profile Appl Id', 30) || ' - ' || to_char(p_export_data_profile));
4557 msd_dem_common_utilities.log_message(' ' || rpad('Workflow Lookup Name', 30) || ' - ' || to_char(p_workflow_lookup_code));
4558
4559
4560 msd_dem_common_utilities.log_message('-----------------------------------Input Parameters - End-------------------------------------------');
4561 msd_dem_common_utilities.log_message ('');
4562 msd_dem_common_utilities.log_message ('');
4563
4564 IF (x_dem_schema IS NULL)
4565 THEN
4566 raise_application_error (-20001, 'Error: msd_dem_upload_forecast.upload_cmro_pln_fctrs - Unable to find schema name');
4567 END IF;
4568
4569 IF (p_export_data_profile IS NULL)
4570 THEN
4571 raise_application_error (-20002, 'Error: msd_dem_upload_forecast.upload_cmro_pln_fctrs - Missing data profile application id');
4572 END IF;
4573
4574 IF (p_workflow_lookup_code IS NULL)
4575 THEN
4576 raise_application_error (-20003, 'Error: msd_dem_upload_forecast.upload_cmro_pln_fctrs - Missing workflow lookup code');
4577 END IF;
4578
4579
4580 /*------- START - Get Desination to Source DBlink -------*/
4581 msd_dem_common_utilities.get_dblink (
4582 var_errbuf,
4583 var_retcode,
4584 p_sr_instance_id,
4585 var_D2S_dblink);
4586
4587 IF (var_retcode = -1)
4588 THEN
4589 retcode := -1;
4590 errbuf := substr(SQLERRM,1,150);
4591 msd_dem_common_utilities.log_message ('ERROR(1) - msd_dem_upload_forecast.upload_cmro_pln_fctrs - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4592 msd_dem_common_utilities.log_message ('Error in call to msd_dem_common_utilities.get_dblink');
4593 msd_dem_common_utilities.log_message (errbuf);
4594 RETURN;
4595 END IF;
4596
4597 msd_dem_common_utilities.log_debug ('Desination to Source DBlink is - '|| var_D2S_dblink );
4598 msd_dem_common_utilities.log_debug (' ');
4599
4600 /*------- END - Get Desination to Source DBlink -------*/
4601
4602
4603 /* START - Launch workflow "Export SPF Planning factors" */
4604
4605 BEGIN
4606
4607 msd_dem_common_utilities.log_message ('Launching workflow - ' || p_workflow_lookup_code );
4608
4609 BEGIN
4610 var_request_id := null;
4611 var_request_id := fnd_request.submit_request ('MSD', 'MSDDEMLDW', NULL, NULL, FALSE,
4612 'WF_EXP_SPF_PLANNING_FACTORS',
4613 p_synchronous, p_check_interval, p_time_out);
4614
4615 msd_dem_common_utilities.log_message ('Request Id for "Launch Demantra Workflow" concurrent program - ' || var_request_id);
4616
4617 EXCEPTION
4618 WHEN OTHERS THEN
4619 retcode := -1;
4620 errbuf := substr(SQLERRM,1,150);
4621 msd_dem_common_utilities.log_message ('ERROR(2): msd_spf_collect_history_data.upload_cmro_pln_fctrs - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4622 msd_dem_common_utilities.log_message ('Error while submitting request for "Launch Demantra Workflow" concurrent program.');
4623 msd_dem_common_utilities.log_message (errbuf);
4624 msd_dem_common_utilities.log_message (' ');
4625 RETURN;
4626 END;
4627
4628 var_request_num := 0 ;
4629 IF (g_req_table.COUNT <> 0) THEN g_req_table.DELETE; END IF;
4630
4631 var_request_num := var_request_num + 1;
4632 g_req_table(var_request_num).REQUEST_ID := var_request_id;
4633 g_req_table(var_request_num).DESCRIPTION := 'launch Demantra Workflow';
4634 g_req_table(var_request_num).IS_COMPLETE := FALSE;
4635 g_req_table(var_request_num).status := 1;
4636
4637 COMMIT;
4638
4639 /*----- Check whether the requests are completed or not -----*/
4640 check_requests_completion(var_errbuf, var_retcode, g_req_table.LAST, systimestamp, g_req_table, p_check_interval, p_time_out, NULL);
4641
4642 IF (var_retcode = -1) THEN
4643 retcode := -1;
4644 errbuf := var_errbuf;
4645 msd_dem_common_utilities.log_message ('ERROR(3) - msd_dem_upload_forecast.upload_cmro_pln_fctrs - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4646 msd_dem_common_utilities.log_message (errbuf);
4647 RETURN;
4648 END IF;
4649
4650 IF (var_retcode = 1) THEN
4651 retcode := 1;
4652 errbuf := var_errbuf;
4653 msd_dem_common_utilities.log_message ('WARNING(1) - msd_dem_upload_forecast.upload_cmro_pln_fctrs - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4654 msd_dem_common_utilities.log_message (errbuf);
4655 END IF;
4656
4657 EXCEPTION
4658 WHEN OTHERS THEN
4659 retcode := -1;
4660 errbuf := substr(SQLERRM,1,150);
4661 msd_dem_common_utilities.log_message ('ERROR(4): msd_dem_upload_forecast.upload_cmro_pln_fctrs - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4662 msd_dem_common_utilities.log_message ('Error while launching Demantra Workflow - '|| p_workflow_lookup_code );
4663 msd_dem_common_utilities.log_message (errbuf);
4664 msd_dem_common_utilities.log_message (' ');
4665 RETURN;
4666 END;
4667
4668 /* END - Launch workflow "Export SPF Planning factors" */
4669
4670
4671 /* START - Fetch the levels selected in the 'SPF-cMRO Export Failure Rates' data profile. */
4672
4673 l_sql := 'SELECT NVL(SUM(id),0) FROM ' || x_dem_schema|| '.transfer_query WHERE '
4674 || 'upper(application_id) = upper(''' || p_export_data_profile || ''') ' ;
4675 EXECUTE IMMEDIATE l_sql INTO x_count;
4676
4677 msd_dem_common_utilities.log_debug ('SQL stmt for data profile presence - ');
4678 msd_dem_common_utilities.log_debug (l_sql);
4679 msd_dem_common_utilities.log_debug (' ');
4680
4681 IF (x_count = 0) THEN
4682 raise_application_error (-20013, 'Error: msd_dem_upload_forecast.upload_cmro_pln_fctrs - Invalid/Non-existing application id for the data profile.');
4683 ELSE
4684 x_count := 0;
4685 END IF;
4686
4687 l_sql := 'SELECT gt.table_label FROM '
4688 || x_dem_schema|| '.transfer_query tq, '
4689 || x_dem_schema|| '.transfer_query_levels tql, '
4690 || x_dem_schema|| '.group_tables gt '
4691 || 'WHERE '
4692 || 'upper(tq.application_id) = upper(''' || p_export_data_profile || ''') '
4693 || 'AND tql.id = tq.id '
4694 || 'AND gt.group_table_id = tql.level_id order by lorder ' ;
4695
4696 msd_dem_common_utilities.log_debug ('SQL stmt for fetching levels - ');
4697 msd_dem_common_utilities.log_debug (l_sql);
4698 msd_dem_common_utilities.log_debug (' ');
4699
4700 OPEN x_lvl_ref_cursor FOR l_sql ;
4701 FETCH x_lvl_ref_cursor BULK COLLECT INTO x_lvl_table ;
4702 x_lvl_count := x_lvl_ref_cursor%rowcount ;
4703 CLOSE x_lvl_ref_cursor;
4704
4705 msd_dem_common_utilities.log_debug ('Levels selected in Data Profile.');
4706 FOR i IN 1..x_lvl_count
4707 LOOP
4708 msd_dem_common_utilities.log_debug (i || '. ' || x_lvl_table(i).level_name);
4709 END LOOP;
4710 msd_dem_common_utilities.log_debug (' ');
4711
4712 /* Get the export data profile info */
4713 x_table_name := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'TRANSFER_QUERY');
4714 l_sql := 'SELECT id, presentation_type, view_name, query_name, ' ||
4715 ' time_res_id, unit_id, index_id, data_scale, ' ||
4716 ' integration_type, export_type, last_export_date ' ||
4717 ' FROM ' || x_table_name ||
4718 ' WHERE upper(application_id) = upper(''' || p_export_data_profile || ''') ';
4719
4720 OPEN x_cur_type FOR l_sql;
4721 FETCH x_cur_type INTO x_profile_id,
4722 x_presentation_type,
4723 x_view_name,
4724 x_query_name,
4725 x_time_res_id,
4726 x_unit_id,
4727 x_index_id,
4728 x_data_scale,
4729 x_integration_type,
4730 x_export_type,
4731 x_last_export_date;
4732 CLOSE x_cur_type;
4733
4734 /* Get the series internal name */
4735 x_small_sql := 'SELECT count(1) FROM ' || x_dem_schema || '.transfer_query_series WHERE id = '|| x_profile_id;
4736 EXECUTE IMMEDIATE x_small_sql INTO x_count;
4737
4738 IF (x_count > 1) THEN
4739 raise_application_error (-20012, 'Error: msd_dem_upload_forecast.upload_cmro_pln_fctrs - Data Profile cannot have more than one series.');
4740 END IF;
4741
4742 l_sql := 'SELECT computed_name FROM ' || x_dem_schema || '.computed_fields cf, ' || x_dem_schema || '.transfer_query_series tqs'
4743 || ' WHERE tqs.id = '|| x_profile_id || ' AND cf.forecast_type_id = tqs.series_id ';
4744 EXECUTE IMMEDIATE l_sql INTO x_series_name;
4745
4746
4747 /* Log the DP name into integ_status table */
4748 x_small_sql := ' BEGIN ' || x_dem_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_FCST || ''' , ''' ||
4749 VS_MSG_LOADING || ' ' || x_query_name || ''' , ''' || VS_MSG_STARTED || ''' ); END;';
4750 EXECUTE IMMEDIATE x_small_sql;
4751
4752 l_sql := 'SELECT count(1) FROM dba_objects ' ||
4753 ' WHERE owner = upper(''' || x_dem_schema || ''')' ||
4754 ' AND object_type IN (''VIEW'', ''MATERIALIZED VIEW'') ' ||
4755 ' AND object_name = upper(''' || x_view_name || ''')';
4756 EXECUTE IMMEDIATE l_sql INTO x_is_view_present;
4757
4758 /* END - Fetch the levels selected in the 'Export SPF Planning factors' data profile. */
4759
4760 /*** START - Check basic error conditions ***/
4761
4762 IF (x_profile_id IS NULL)
4763 THEN
4764 raise_application_error (-20004, 'Error: msd_dem_upload_forecast.upload_cmro_pln_fctrs - Unable to get export data profile id');
4765 ELSIF (x_integration_type = C_IMPORT_DATA_PROFILE)
4766 THEN
4767 raise_application_error (-20005, 'Error: msd_dem_upload_forecast.upload_cmro_pln_fctrs - ' || x_query_name || 'is not an export data profile');
4768 ELSIF (x_export_type = C_EXPORT_TYPE_INCR)
4769 THEN
4770 raise_application_error (-20006, 'Error: msd_dem_upload_forecast.upload_cmro_pln_fctrs - Incremental export type is not supported');
4771 ELSIF (x_index_id IS NOT NULL)
4772 THEN
4773 raise_application_error (-20007, 'Error: msd_dem_upload_forecast.upload_cmro_pln_fctrs - Forecast amount cannot be uploaded');
4774 ELSIF (x_is_view_present = 0)
4775 THEN
4776 raise_application_error (-20008, 'Error: msd_dem_upload_forecast.upload_cmro_pln_fctrs - Forecast has not yet been exported');
4777 ELSIF (x_presentation_type = C_PSNT_TYPE_DESC)
4778 THEN
4779 raise_application_error (-20009, 'Error: msd_dem_upload_forecast.upload_cmro_pln_fctrs - Presentation type must by Code');
4780 END IF;
4781
4782 /*** END - Check basic error conditions ***/
4783
4784 l_sql := 'select to_number(parameter_value) from msd_dem_setup_parameters where parameter_name like ''MSD_SPF_MASTER_ORG''';
4785 EXECUTE IMMEDIATE l_sql INTO x_spf_master_org;
4786 msd_dem_common_utilities.log_debug(l_sql);
4787 msd_dem_common_utilities.log_debug (' ');
4788
4789
4790
4791 -- Bug#14621568/13995563 add (''0'',NULL,''-777'' ) below if stmts
4792 /* get MASTER_ITEM level column*/
4793 x_MI_level := get_level_column (x_profile_id, C_MASTER_ITEM);
4794 IF (x_MI_level IS NOT NULL)
4795 THEN
4796 x_select_clause := ' SELECT inner_qry.* FROM( SELECT distinct ' || x_MI_level || 'MI, ' ;
4797 ELSE
4798 raise_application_error (-20010, 'Error: msd_dem_upload_forecast.upload_cmro_pln_fctrs - "Master Item" level is required in data profile for upload to complete successfully.');
4799 END IF;
4800
4801 /* get SPF_MAINTENANCE_TYPE level column*/
4802 x_SPF_MT_level := get_level_column (x_profile_id, C_SPF_MAINTENANCE_TYPE);
4803 IF (x_SPF_MT_level IS NOT NULL)
4804 THEN
4805 x_select_clause := x_select_clause || ' decode(' || x_SPF_MT_level || ',''0'',NULL,''-777'',NULL,lkup_MT.lookup_code) MT, ' ;
4806 ELSE
4807 raise_application_error (-20011, 'Error: msd_dem_upload_forecast.upload_cmro_pln_fctrs - "SPF Maintenance Type" level is required in data profile for upload to complete successfully.');
4808 END IF;
4809
4810 /* get ASSET_GROUP level column*/
4811 x_AG_level := get_level_column (x_profile_id, C_ASSET_GROUP);
4812 IF (x_AG_level IS NOT NULL)
4813 THEN
4814 x_select_clause := x_select_clause || 'decode(' || x_AG_level || ',''0'',NULL,''-777'',NULL,'|| x_AG_level || ') AG, ' ;
4815 ELSE
4816 x_select_clause := x_select_clause || 'NULL' || ' AG, ' ;
4817 END IF;
4818
4819 /* get ASSET_GROUP_ATTRIBUTE_1 level column*/
4820 x_AGA1_level := get_level_column (x_profile_id, C_ASSET_GROUP_ATTRIBUTE_1);
4821 IF (x_AGA1_level IS NOT NULL)
4822 THEN
4823 x_select_clause := x_select_clause || 'decode(' || x_AGA1_level || ',''0'',NULL,''-777'',NULL,'|| x_AGA1_level || ') AGA1, ' ;
4824 ELSE
4825 x_select_clause := x_select_clause || 'NULL' || ' AGA1, ' ;
4826 END IF;
4827
4828 /* get ASSET_GROUP_ATTRIBUTE_2 level column*/
4829 x_AGA2_level := get_level_column (x_profile_id, C_ASSET_GROUP_ATTRIBUTE_2);
4830 IF (x_AGA2_level IS NOT NULL)
4831 THEN
4832 x_select_clause := x_select_clause || ' decode(' || x_AGA2_level || ',''0'',NULL,''-777'',NULL,lkup_AG.lookup_code) AGA2, ' ;
4833 ELSE
4834 x_select_clause := x_select_clause || 'NULL' || ' AGA2, ' ;
4835 END IF;
4836
4837 /* get CLASS_CODE level column*/
4838 x_CC_level := get_level_column (x_profile_id, C_CLASS_CODE);
4839 IF (x_CC_level IS NOT NULL)
4840 THEN
4841 x_select_clause := x_select_clause || 'decode(' || x_CC_level || ',''0'',NULL,''-777'',NULL,'|| x_CC_level || ') CC, ' ;
4842 ELSE
4843 x_select_clause := x_select_clause || 'NULL' || ' CC, ' ;
4844 END IF;
4845
4846 /* get SPF_VISIT_TYPE level column*/
4847 x_SPF_VT_level := get_level_column (x_profile_id, C_SPF_VISIT_TYPE) ;
4848 IF (x_SPF_VT_level IS NOT NULL)
4849 THEN
4850 x_select_clause := x_select_clause || 'decode(' || x_SPF_VT_level || ',''0'',NULL,''-777'',NULL,lkup_vt.lookup_code) VT, ' ;
4851 ELSE
4852 raise_application_error (-20012, 'Error: msd_dem_upload_forecast.upload_cmro_pln_fctrs - "SPF Visit Type" level is required in data profile for upload to complete successfully.');
4853 END IF;
4854
4855 /* get SPF_VISIT_STAGE_TYPE level column*/
4856 x_SPF_VST_level := get_level_column (x_profile_id, C_SPF_VISIT_STAGE_TYPE);
4857 IF (x_SPF_VST_level IS NOT NULL)
4858 THEN
4859 x_select_clause := x_select_clause || 'decode(' || x_SPF_VST_level || ',''0'',NULL,''-777'',NULL,'|| x_SPF_VST_level || ') VST, ' ;
4860 ELSE
4861 x_select_clause := x_select_clause || 'NULL' || ' VST, ' ;
4862 END IF;
4863
4864 /* get ITEM_TYPE level column*/
4865 x_IT_level := get_level_column (x_profile_id, C_ITEM_TYPE);
4866 IF (x_IT_level IS NOT NULL)
4867 THEN
4868 x_select_clause := x_select_clause || 'decode(' || x_IT_level || ',''Material'',''I'',''Product'',''I'',''Resource'',''R'')' || ' IT, ' ;
4869 x_select_clause := REPLACE(x_select_clause, x_MI_level, 'decode(' || x_IT_level || ',''Resource'',' || x_MI_level || ',NULL)');
4870 ELSE
4871 raise_application_error (-20013, 'Error: msd_dem_upload_forecast.upload_cmro_pln_fctrs - "Item Type" level is required in data profile for upload to complete successfully.');
4872 END IF;
4873
4874 x_select_clause := x_select_clause
4875 || ' msi.sr_inventory_item_id SR_INV_ITEM_ID, '
4876 || x_series_name || ', '
4877 || 'decode(' || x_IT_level || ',''Material'',msi.uom_code,''Product'',msi.uom_code,''Resource'',NULL)' || ' UOM, '
4878 || '''MSD''' || ', '
4879 || ' systimestamp LAST_UPDATE_DATE, '
4880 || x_fnd_user_id || ' LAST_UPDATED_BY, '
4881 || ' systimestamp CREATION_DATE, '
4882 || x_fnd_user_id || ' CREATED_BY, '
4883 || ' fnd_global.login_id ' ;
4884
4885 x_from_clause := ' FROM ' || x_dem_schema || '.' || x_view_name || ' exp, '
4886 || ' msc_system_items msi, '
4887 || ' (select meaning,lookup_code from fnd_lookup_values_vl' || var_D2S_dblink ||' where lookup_type = ''AHL_MAINTENANCE_SOURCE_TYPE'') lkup_MT '
4888 || ',(select meaning,lookup_code from fnd_lookup_values_vl' || var_D2S_dblink ||' where lookup_type = ''AHL_PLANNING_VISIT_TYPE'') lkup_vt ';
4889
4890 IF (x_AGA2_level IS NOT NULL) THEN
4891 x_from_clause := x_from_clause || ', (select meaning,lookup_code from fnd_lookup_values_vl' || var_D2S_dblink
4892 ||' where lookup_type = ''AHL_FLT_OPERATIONS_TYPE'') lkup_ag ';
4893 END IF;
4894 IF (x_SPF_VST_level IS NOT NULL) THEN
4895 x_from_clause := x_from_clause || ', (select meaning,lookup_code from fnd_lookup_values_vl' || var_D2S_dblink
4896 ||' where lookup_type = ''AHL_VWP_STAGE_TYPE'') lkup_vst ';
4897 END IF;
4898
4899 x_where_clause := ' WHERE ' || ' msi.plan_id(+) = -1 '
4900 || ' AND msi.item_name(+) = exp.' || x_MI_level
4901 || ' AND exp.' || x_MI_level || ' != ''' || x_wo_item || ''''
4902 || ' AND exp.' || x_series_name || ' is not null '
4903 || ' AND msi.organization_id(+) = ' || x_spf_master_org
4904 || ' AND msi.sr_instance_id(+) = ' || p_sr_instance_id
4905 || ' AND lkup_MT.meaning(+) = exp.' || x_SPF_MT_level
4906 || ' AND lkup_vt.meaning(+) = exp.' || x_SPF_VT_level ;
4907
4908 IF (x_AGA2_level IS NOT NULL) THEN
4909 x_where_clause := x_where_clause || ' AND lkup_ag.meaning(+) = exp.' || x_AGA2_level ;
4910 END IF;
4911 IF (x_SPF_VST_level IS NOT NULL) THEN
4912 x_where_clause := x_where_clause || ' AND lkup_vst.meaning(+) = exp.' || x_SPF_VST_level ;
4913 END IF;
4914
4915 -- filtering out invalid items and resource
4916 x_where_clause := x_where_clause || ') inner_qry
4917 WHERE 1 = 1
4918 AND ((inner_qry.it = ''R'' AND inner_qry.mi IS NOT NULL)
4919 OR (inner_qry.it = ''I'' AND inner_qry.sr_inv_item_id IS NOT NULL))' ;
4920
4921
4922 x_insert_clause := ' INSERT INTO AHL_PLANNING_FACTORS' || var_D2S_dblink || ' (' ||
4923 ' CMRO_RESOURCE_NAME, ' || -- master_item level
4924 ' MAINTENANCE_TYPE_CODE, ' || -- spf_maintenance_type level
4925 ' FLEET_NAME, ' || -- asset_group level
4926 ' OPERATING_ORG_ID,' || -- asset_group_attribute_1 level
4927 ' OPERATIONS_TYPE_CODE, ' || -- asset_group_attribute_2 level
4928 ' MR_TITLE, ' || -- class_code level
4929 ' VISIT_TYPE_CODE, ' || -- spf_visit_type level
4930 ' VISIT_STAGE_TYPE_CODE, ' || -- spf_visit_stage_type level
4931 ' ITEM_RESOURCE_FLAG, ' || -- item_type level
4932 ' INVENTORY_ITEM_ID, ' ||
4933 ' USAGE, ' ||
4934 ' UOM_CODE, ' ||
4935 ' SOURCE_APPLICATION, ' ||
4936 ' LAST_UPDATE_DATE, '||
4937 ' LAST_UPDATED_BY, ' ||
4938 ' CREATION_DATE, ' ||
4939 ' CREATED_BY, ' ||
4940 ' LAST_UPDATE_LOGIN ) ' ;
4941
4942 l_large_sql := x_insert_clause || x_select_clause || x_from_clause || x_where_clause ;
4943
4944 /* START - Delete from AHL_PLANNING_FACTORS table */
4945 x_small_sql := 'Delete from AHL_PLANNING_FACTORS' || var_D2S_dblink ;
4946 EXECUTE IMMEDIATE x_small_sql;
4947 COMMIT;
4948
4949 msd_dem_common_utilities.log_debug ('Delete sql - ' || x_small_sql);
4950 msd_dem_common_utilities.log_debug (' ');
4951 /* END - Delete from AHL_PLANNING_FACTORS table */
4952
4953 /* To bypass ORA-02069 error */
4954 x_small_sql := 'ALTER SESSION SET GLOBAL_NAMES = TRUE';
4955 EXECUTE IMMEDIATE x_small_sql;
4956 COMMIT;
4957 msd_dem_common_utilities.log_debug ('Alter sql - ' || x_small_sql);
4958 msd_dem_common_utilities.log_debug (' ');
4959
4960
4961 /* START - Inserting into AHL_PLANNING_FACTORS table */
4962 msd_dem_common_utilities.log_debug (' ');
4963 msd_dem_common_utilities.log_debug ('Executing insert sql - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4964 msd_dem_common_utilities.log_debug (l_large_sql);
4965
4966 EXECUTE IMMEDIATE l_large_sql;
4967
4968 msd_dem_common_utilities.log_debug ('Executed insert sql - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4969 msd_dem_common_utilities.log_debug (' ');
4970 /* END - Inserting into AHL_PLANNING_FACTORS table */
4971
4972 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_upload_forecast.upload_cmro_pln_fctrs - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4973
4974 EXCEPTION
4975 WHEN OTHERS THEN
4976 errbuf := substr(SQLERRM,1,150);
4977 retcode := -1 ;
4978 msd_dem_common_utilities.log_message ('Exception: msd_dem_upload_forecast.upload_cmro_pln_fctrs - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
4979 msd_dem_common_utilities.log_message (errbuf);
4980 msd_dem_common_utilities.log_message (' ');
4981
4982 END UPLOAD_CMRO_PLN_FCTRS;
4983
4984
4985 /*
4986 * This procedure is used to export the forecast of the following -
4987 * 1. Independent demand of non-maintenance products and materials
4988 * 2. Work order demand of materials.
4989 * The parameters are -
4990 * p_ind_export_data_profile_wai - Application Id of the export data profile used to export independent demand
4991 * p_wod_export_data_profile_wai - Application Id of the export data profile used to export work order demand
4992 * p_ind_fcst_series_wai - Application Id of the series which holds independent demand
4993 * p_wod_fcst_series_wai - Application Id of the series which holds work order demand
4994 * p_wod_fcst_acry_series_wai - Application Id of the series which holds the work order demand MAPE value in MDP_MATRIX
4995 */
4996 PROCEDURE UPLOAD_TOTAL_DEMAND_WO (
4997 p_ind_export_data_profile_wai IN VARCHAR2,
4998 p_wod_export_data_profile_wai IN VARCHAR2,
4999 p_ind_fcst_series_wai IN VARCHAR2,
5000 p_wod_fcst_series_wai IN VARCHAR2,
5001 p_wod_fcst_acry_series_wai IN VARCHAR2 DEFAULT 'COMPUTED_FIELD:4577')
5002 IS
5003
5004 x_small_sql VARCHAR2(600) := NULL;
5005 x_schema VARCHAR2(50) := NULL;
5006 x_errbuf VARCHAR2(200) := NULL;
5007 x_retcode VARCHAR2(100) := NULL;
5008
5009 x_ind_export_data_profile VARCHAR2(255) := NULL;
5010 x_wod_export_data_profile VARCHAR2(255) := NULL;
5011 x_ind_fcst_series VARCHAR2(50) := NULL;
5012 x_wod_fcst_series VARCHAR2(50) := NULL;
5013 x_ind_scenario_id NUMBER := NULL;
5014 x_wod_scenario_id NUMBER := NULL;
5015 x_max_demand_id NUMBER := NULL;
5016
5017 x_fcst_acry_column VARCHAR2(30) := NULL;
5018
5019 BEGIN
5020
5021
5022 IF ( p_ind_export_data_profile_wai IS NULL
5023 OR p_wod_export_data_profile_wai IS NULL
5024 OR p_ind_fcst_series_wai IS NULL
5025 OR p_wod_fcst_series_wai IS NULL)
5026 THEN
5027 raise_application_error (-20001, 'Error: msd_dem_upload_forecast.upload_total_demand_wo - All the four input parameters must be specified');
5028 END IF;
5029
5030
5031 x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
5032 IF (x_schema IS NULL)
5033 THEN
5034 raise_application_error (-20002, 'Error: msd_dem_upload_forecast.upload_total_demand_wo - Unable to find schema name');
5035 END IF;
5036
5037
5038 /* Get the name of the independent demand data profile */
5039 BEGIN
5040
5041 EXECUTE IMMEDIATE 'SELECT query_name FROM ' || x_schema || '.TRANSFER_QUERY WHERE application_id = :1 '
5042 INTO x_ind_export_data_profile
5043 USING p_ind_export_data_profile_wai;
5044
5045 EXCEPTION
5046 WHEN OTHERS THEN
5047 /* Alter session to demantra schema */
5048 x_small_sql := 'alter session set current_schema = ' || x_schema;
5049 EXECUTE IMMEDIATE x_small_sql;
5050
5051 raise_application_error (-20003, 'Exception: msd_dem_upload_forecast.upload_total_demand_wo - Unable to find independent demand data profile ' || substr(SQLERRM,1,150));
5052 END;
5053
5054
5055 /* Get the name of the work order demand data profile */
5056 BEGIN
5057
5058 EXECUTE IMMEDIATE 'SELECT query_name FROM ' || x_schema || '.TRANSFER_QUERY WHERE application_id = :1 '
5059 INTO x_wod_export_data_profile
5060 USING p_wod_export_data_profile_wai;
5061
5062 EXCEPTION
5063 WHEN OTHERS THEN
5064 /* Alter session to demantra schema */
5065 x_small_sql := 'alter session set current_schema = ' || x_schema;
5066 EXECUTE IMMEDIATE x_small_sql;
5067
5068 raise_application_error (-20004, 'Exception: msd_dem_upload_forecast.upload_total_demand_wo - Unable to find work order demand data profile ' || substr(SQLERRM,1,150));
5069 END;
5070
5071
5072 /* Get the internal name of the independent demand forecast series */
5073 BEGIN
5074
5075 EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
5076 INTO x_ind_fcst_series
5077 USING p_ind_fcst_series_wai;
5078
5079 EXCEPTION
5080 WHEN OTHERS THEN
5081 /* Alter session to demantra schema */
5082 x_small_sql := 'alter session set current_schema = ' || x_schema;
5083 EXECUTE IMMEDIATE x_small_sql;
5084
5085 raise_application_error (-20005, 'Exception: msd_dem_upload_forecast.upload_total_demand_wo - Unable to find independent demand series ' || substr(SQLERRM,1,150));
5086 END;
5087
5088
5089 /* Get the internal name of the work order demand forecast series */
5090 BEGIN
5091
5092 EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
5093 INTO x_wod_fcst_series
5094 USING p_wod_fcst_series_wai;
5095
5096 EXCEPTION
5097 WHEN OTHERS THEN
5098 /* Alter session to demantra schema */
5099 x_small_sql := 'alter session set current_schema = ' || x_schema;
5100 EXECUTE IMMEDIATE x_small_sql;
5101
5102 raise_application_error (-20006, 'Exception: msd_dem_upload_forecast.upload_total_demand_wo - Unable to find work order demand series ' || substr(SQLERRM,1,150));
5103 END;
5104
5105 /* Alter session to demantra schema */
5106 x_small_sql := 'alter session set current_schema = ' || x_schema;
5107 EXECUTE IMMEDIATE x_small_sql;
5108
5109 /* Independent Demand Publish */
5110 upload_forecast(x_ind_export_data_profile, x_ind_fcst_series, null);
5111
5112 /* Dependent Demand Publish */
5113 upload_forecast(x_wod_export_data_profile, null, x_wod_fcst_series);
5114
5115 /* Alter session to APPS */
5116 x_small_sql := 'alter session set current_schema = APPS';
5117 EXECUTE IMMEDIATE x_small_sql;
5118
5119 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_TD || ''' , ''' ||
5120 VS_MSG_LOADING || ' ' || x_ind_export_data_profile || ''' , ''' || VS_MSG_STARTED || ''' ); END;';
5121
5122 EXECUTE IMMEDIATE x_small_sql;
5123
5124
5125 x_small_sql := 'SELECT id FROM ' || x_schema || '.TRANSFER_QUERY WHERE lower(query_name) = :1 ';
5126
5127 /* Get the id for independent data profile */
5128 EXECUTE IMMEDIATE x_small_sql
5129 INTO x_ind_scenario_id
5130 USING lower(x_ind_export_data_profile);
5131 x_ind_scenario_id := x_ind_scenario_id + C_SCENARIO_ID_OFFSET;
5132
5133 /* Get the id for work order data profile */
5134 EXECUTE IMMEDIATE x_small_sql
5135 INTO x_wod_scenario_id
5136 USING lower(x_wod_export_data_profile);
5137 x_wod_scenario_id := x_wod_scenario_id + C_SCENARIO_ID_OFFSET;
5138
5139 /* First update the forecast error value for work order data profile's data in the denorm table before merge */
5140 EXECUTE IMMEDIATE 'SELECT dbname FROM ' || x_schema || '.computed_fields WHERE application_id = :1'
5141 INTO x_fcst_acry_column
5142 USING p_wod_fcst_acry_series_wai;
5143
5144 EXECUTE IMMEDIATE ' UPDATE MSD_DP_SCN_ENTRIES_DENORM d '
5145 || ' SET forecast_error = nvl((SELECT 100 * ' || x_fcst_acry_column
5146 || ' FROM msc_trading_partners mtp, '
5147 || ' msc_system_items msi, '
5148 || x_schema || '.T_EP_ITEM tei, '
5149 || x_schema || '.T_EP_ORGANIZATION teo, '
5150 || x_schema || '.MDP_MATRIX mm '
5151 || ' WHERE mtp.partner_type = 3 '
5152 || ' AND mtp.sr_instance_id = d.sr_instance_id '
5153 || ' AND mtp.sr_tp_id = d.sr_organization_id '
5154 || ' AND msi.plan_id = -1 '
5155 || ' AND msi.sr_instance_id = d.sr_instance_id '
5156 || ' AND msi.organization_id = d.sr_organization_id '
5157 || ' AND msi.sr_inventory_item_id = d.sr_inventory_item_id '
5158 || ' AND tei.item = msi.item_name '
5159 || ' AND tei.t_ep_i_att_10_ep_id = 1 '
5160 || ' AND teo.organization = mtp.organization_code '
5161 || ' AND mm.t_ep_item_ep_id = tei.t_ep_item_ep_id '
5162 || ' AND mm.t_ep_organization_ep_id = teo.t_ep_organization_ep_id '
5163 || ' AND rownum < 2 ), forecast_error) '
5164 || ' WHERE d.scenario_id = ' || x_wod_scenario_id;
5165
5166 COMMIT;
5167
5168 /* Get the max demand id for independent demand */
5169 EXECUTE IMMEDIATE 'SELECT max(demand_id) FROM msd_dp_scn_entries_denorm WHERE scenario_id = :1'
5170 INTO x_max_demand_id
5171 USING x_ind_scenario_id;
5172 IF (x_max_demand_id IS NULL)
5173 THEN
5174 x_max_demand_id := 0;
5175 ELSE
5176 x_max_demand_id := x_max_demand_id + 1;
5177 END IF;
5178
5179 /* Merge the independent demand and work order demand forecast */
5180 MERGE INTO msd_dp_scn_entries_denorm d1
5181 USING (SELECT rownum rn, demand_plan_id, scenario_id, demand_id, bucket_type, start_time, end_time, quantity,
5182 sr_organization_id, sr_instance_id, sr_inventory_item_id, error_type, forecast_error, inventory_item_id,
5183 dp_uom_code, ascp_uom_code, unit_price, creation_date, created_by, last_update_login
5184 FROM msd_dp_scn_entries_denorm
5185 WHERE scenario_id = x_wod_scenario_id) d2
5186 ON ( d1.scenario_id = x_ind_scenario_id
5187 AND d1.sr_organization_id = d2.sr_organization_id
5188 AND d1.sr_instance_id = d2.sr_instance_id
5189 AND d1.inventory_item_id = d2.inventory_item_id
5190 AND d1.start_time = d2.start_time )
5191 WHEN MATCHED THEN
5192 UPDATE
5193 SET d1.quantity = d1.quantity + d2.quantity,
5194 d1.forecast_error = (d1.forecast_error + d2.forecast_error)/2
5195 WHEN NOT MATCHED THEN
5196 INSERT (DEMAND_PLAN_ID, SCENARIO_ID, DEMAND_ID, BUCKET_TYPE, START_TIME, END_TIME, QUANTITY,
5197 SR_ORGANIZATION_ID, SR_INSTANCE_ID, SR_INVENTORY_ITEM_ID, ERROR_TYPE, FORECAST_ERROR, INVENTORY_ITEM_ID,
5198 DP_UOM_CODE, ASCP_UOM_CODE, UNIT_PRICE, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN)
5199 VALUES (d2.demand_plan_id, x_ind_scenario_id, x_max_demand_id + d2.rn, d2.bucket_type, d2.start_time, d2.end_time, d2.quantity,
5200 d2.sr_organization_id, d2.sr_instance_id, d2.sr_inventory_item_id, d2.error_type, d2.forecast_error, d2.inventory_item_id,
5201 d2.dp_uom_code, d2.ascp_uom_code, d2.unit_price, d2.creation_date, d2.created_by, d2.last_update_login);
5202
5203 COMMIT;
5204
5205 /* Delete the forecast data with scenario id of x_wod_scenario_id */
5206 DELETE FROM msd_dp_scn_entries_denorm
5207 WHERE scenario_id = x_wod_scenario_id;
5208
5209 COMMIT;
5210
5211 msd_dem_collect_history_data.analyze_table (
5212 x_errbuf,
5213 x_retcode,
5214 'MSD_DP_SCN_ENTRIES_DENORM');
5215
5216 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_TD || ''' , ''' ||
5217 VS_MSG_LOADED || ' ' || x_ind_export_data_profile || ''' , ''' || VS_MSG_SUCCEEDED || ''' ); END;';
5218
5219
5220 EXECUTE IMMEDIATE x_small_sql;
5221
5222 /* Alter session to demantra schema */
5223 x_small_sql := 'alter session set current_schema = ' || x_schema;
5224 EXECUTE IMMEDIATE x_small_sql;
5225
5226 EXCEPTION
5227 WHEN OTHERS THEN
5228
5229 x_small_sql := ' BEGIN ' || x_schema || '.dl_log_status(''' || VS_MSG_UPLOAD_TD || ''' , ''' ||
5230 VS_MSG_LOADED || ' ' || x_ind_export_data_profile || ''' , ''' || VS_MSG_LOADE_ERROR || ''',''' || substr(SQLERRM,1,150) || ''' ); END;';
5231
5232
5233 EXECUTE IMMEDIATE x_small_sql;
5234
5235 /* Alter session to demantra schema */
5236 x_small_sql := 'alter session set current_schema = ' || x_schema;
5237 EXECUTE IMMEDIATE x_small_sql;
5238
5239 raise_application_error (-20015, 'Exception: msd_dem_upload_forecast.upload_total_demand_wo - ' || substr(SQLERRM,1,150));
5240
5241
5242 END UPLOAD_TOTAL_DEMAND_WO;
5243
5244
5245
5246
5247 /*
5248 * This procedure will load SPF forecast data into MSD_DP_SCN_ENTRIES_DENORM and
5249 * SPF metrics into MSC_DMD_SCN_METRICS.
5250 * Arguments -
5251 * 1. SPF Upload Forecast and Metrics data profile appl id
5252 * Process -
5253 * a. First forecast and metrics data is loaded to MSD_DP_SCN_ENTRIES_DENORM
5254 * b. Then aggregated (time independent) metrics data is copied over to
5255 * MSC_DMD_SCN_METRICS
5256 *
5257 * Note - This way the Demantra export view is queries only once and hence
5258 * improves the total run time.
5259 *
5260 * Info about the loading of the data is logged in integ_status table.
5261 */
5262 PROCEDURE UPLOAD_SPF_FORECAST_METRICS (
5263 p_dp_spf_fcst_metrics_appid IN VARCHAR2,
5264 p_acry_series_wai IN VARCHAR2 DEFAULT NULL,
5265 p_parallel_degree IN NUMBER DEFAULT NULL )
5266 IS
5267
5268 x_small_sql VARCHAR2(600) := NULL;
5269 x_schema VARCHAR2(50) := NULL;
5270 x_export_data_profile VARCHAR2(255) := NULL;
5271 x_acry_series_iname VARCHAR2(255) := NULL;
5272
5273 BEGIN
5274
5275 IF (p_dp_spf_fcst_metrics_appid IS NULL)
5276 THEN
5277 raise_application_error(-20001, 'Error: msd_dem_upload_forecast.upload_spf_forecast_metrics - Export Data Profile Application ID is null');
5278 END IF;
5279
5280 x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
5281 IF (x_schema IS NULL)
5282 THEN
5283 raise_application_error (-20002, 'Error: msd_dem_upload_forecast.upload_spf_forecast_metrics - Unable to find schema name');
5284 END IF;
5285
5286 /* Get the name of the data profile */
5287 BEGIN
5288
5289 EXECUTE IMMEDIATE 'SELECT query_name FROM ' || x_schema || '.TRANSFER_QUERY WHERE application_id = :1 '
5290 INTO x_export_data_profile
5291 USING p_dp_spf_fcst_metrics_appid;
5292
5293 EXCEPTION
5294 WHEN OTHERS THEN
5295 raise_application_error (-20003, 'Exception: msd_dem_upload_forecast.upload_spf_forecast_metrics - Unable to find data profile ' || substr(SQLERRM,1,150));
5296 END;
5297
5298 IF (p_acry_series_wai IS NOT NULL)
5299 THEN
5300
5301 /* Get the internal name from computed_fields table */
5302 BEGIN
5303
5304 EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
5305 INTO x_acry_series_iname
5306 USING p_acry_series_wai;
5307
5308 EXCEPTION
5309 WHEN OTHERS THEN
5310 raise_application_error (-20003, 'Exception: msd_dem_upload_forecast.upload_spf_forecast_metrics - Unable to get Metrics series internal name with given application id. ' || substr(SQLERRM,1,150));
5311
5312 END;
5313
5314 END IF;
5315
5316 IF (p_parallel_degree IS NOT NULL)
5317 THEN
5318
5319 EXECUTE IMMEDIATE 'alter table ' || x_schema || '.t_ep_spf_matrix PARALLEL (DEGREE ' || to_char(p_parallel_degree) || ')';
5320 EXECUTE IMMEDIATE 'alter table ' || x_schema || '.t_ep_spf_data PARALLEL (DEGREE ' || to_char(p_parallel_degree) || ')';
5321
5322 END IF;
5323
5324 upload_forecast(x_export_data_profile, null, null, 1, x_acry_series_iname);
5325
5326 IF (p_parallel_degree IS NOT NULL)
5327 THEN
5328
5329 EXECUTE IMMEDIATE 'alter table ' || x_schema || '.t_ep_spf_matrix NOPARALLEL ';
5330 EXECUTE IMMEDIATE 'alter table ' || x_schema || '.t_ep_spf_data NOPARALLEL ';
5331
5332 END IF;
5333
5334 EXCEPTION
5335 WHEN OTHERS THEN
5336
5337 /* Alter session to demantra schema */
5338 x_small_sql := 'alter session set current_schema = ' || x_schema;
5339 EXECUTE IMMEDIATE x_small_sql;
5340
5341 raise_application_error (-20015, 'Exception: msd_dem_upload_forecast.upload_spf_forecast_metrics - ' || substr(SQLERRM,1,150));
5342
5343 END UPLOAD_SPF_FORECAST_METRICS;
5344
5345
5346
5347
5348 END MSD_DEM_UPLOAD_FORECAST;