1 PACKAGE BODY msd_validate_demand_plan AS
2 /* $Header: msddpvlb.pls 120.15 2006/09/08 08:31:38 amitku noship $ */
3 --
4 -- Private procedures
5
6 Procedure chk_required_dim (p_demand_plan_id in number);
7 Procedure chk_usr_dim (p_demand_plan_id in number);
8 Procedure chk_usd_dim (p_demand_plan_id in number);
9 Procedure chk_dim_hier (p_demand_plan_id in number);
10 Procedure chk_dim_lvl_val (p_demand_plan_id in number);
11 Procedure chk_input_param (p_demand_plan_id in number);
12 Procedure chk_fact_data (p_demand_plan_id in number);
13 Procedure chk_scenarios (p_demand_plan_id in number,
14 p_g_min_tim_lvl_id in number,
15 p_m_min_tim_lvl_id in number,
16 p_f_min_tim_lvl_id in number,
17 p_c_min_tim_lvl_id in number);
18 Procedure chk_time_data (p_demand_plan_id in number);
19 Procedure chk_calendars (p_demand_plan_id in number,
20 p_calendar_type in number,
21 p_lowest_lvl_id in varchar2);
22 Procedure chk_min_time (p_g_min_tim_lvl_id in number,
23 p_m_min_tim_lvl_id in number,
24 p_f_min_tim_lvl_id in number,
25 p_c_min_tim_lvl_id in number);
26 Procedure chk_uom_data (p_demand_plan_id in number);
27 Procedure chk_curr_data (p_demand_plan_id in number);
28 Procedure chk_output_levels (p_demand_plan_id in number);
29 Procedure chk_dup_dim_output_levels (p_demand_plan_id in number);
30 Procedure chk_ip_multiple (p_parameter_type in varchar2,
31 p_multiple_flag in varchar2,
32 p_parameter_name in varchar2);
33 Procedure chk_ip_allo_agg (p_parameter_type in varchar2,
34 p_parameter_name in varchar2,
35 p_demand_plan_id in number,
36 p_cs_def_id in number,
37 p_stream_id in number);
38
39 Procedure chk_scen_events (p_demand_plan_id in number);
40 Procedure chk_iv_org (p_demand_plan_id in number,
41 p_iv_flag in varchar2,
42 p_stripe_stream_name in varchar2,
43 p_stripe_sr_level_pk in varchar2);
44
45 Procedure chk_iso_org (p_demand_plan_id in number);
46
47 Procedure update_plan (p_demand_plan_id in number,
48 p_ret_code in number);
49 Procedure show_message (p_text in varchar2);
50 Procedure debug_out (p_text in varchar2);
51
52 --Added for multiple composites enhancements
53 Procedure chk_composite_group_dimension (p_demand_plan_id in number);
54 Procedure chk_composite_group_level (p_demand_plan_id in number);
55 Function get_level_column_name (p_dim_code in varchar2,
56 p_cs_id in number)
57 return Varchar2;
58
59 Function get_level_id (p_view_name in varchar2,
60 p_View_level_col in varchar2,
61 p_view_date_col in varchar2,
62 p_start_date in date,
63 p_end_date in date,
64 p_system_flag in Varchar2,
65 p_multi_stream_flag in Varchar2,
66 p_parameter_name in Varchar2,
67 p_cs_id in Number,
68 p_call_source in Number,
69 p_input_demand_plan_id in Number,
70 p_input_scenario_id in Number)
71 return Number;
72
73 Procedure Lock_Row(p_demand_plan_id in number);
74
75 function get_desig_clmn_name (p_cs_id in number) return VARCHAR2;
76
77 --Added for multiple composites enhancements
78
79 /* Bug# 5248868
80 * This procedure validates that whether price list data exists
81 * for the price lists specified in the demand plan.
82 * Note: Only time range validation is done
83 */
84 PROCEDURE chk_price_list_data (p_demand_plan_id IN NUMBER);
85
86 --
87 --
88 -- Constants
89 --
90 FATAL_ERROR Constant varchar2(30):='FATAL_ERROR';
91 ERROR Constant varchar2(30):='ERROR';
92 WARNING Constant varchar2(30):='WARNING';
93 INFORMATION Constant varchar2(30):='INFORMATION';
94 HEADING Constant varchar2(30):='HEADING';
95 SECTION Constant varchar2(30):='SECTION';
96 SUCCESS Constant varchar2(30):='SUCCESS';
97
98 C_YES_FLAG Constant varchar2(30):= 'Y';
99
100 l_debug VARCHAR2(240) := NVL(fnd_profile.value('MRP_DEBUG'), 'N');
101
102 --
103 -- get demand plan record
104 --
105
106 CURSOR get_dp (p_demand_plan_id NUMBER) IS
107 SELECT base_uom,
108 demand_plan_id,
109 demand_plan_name,
110 enable_fcst_explosion,
111 g_min_tim_lvl_id,
112 f_min_tim_lvl_id,
113 c_min_tim_lvl_id,
114 m_min_tim_lvl_id,
115 use_org_specific_bom_flag,
116 stripe_sr_level_pk,
117 stripe_stream_name
118 FROM msd_demand_plans
119 WHERE demand_plan_id = p_demand_plan_id;
120
121 --
122 -- check TIM and PRD dimensions are there
123 --
124 CURSOR get_dim (p_demand_plan_id NUMBER,
125 p_dp_dimension_code VARCHAR2,
126 p_dimension_code VARCHAR2) IS
127 SELECT dimension_code
128 FROM msd_dp_dimensions_v
129 WHERE demand_plan_id = p_demand_plan_id
130 AND dp_dimension_code = p_dp_dimension_code
131 AND dimension_code = p_dimension_code;
132
133 --
134 -- check there are at least three user dimensions and not more than four.
135 --
136 CURSOR chk_user_dim (p_demand_plan_id number) IS
137 SELECT COUNT(DISTINCT dp_dimension_code)
138 FROM msd_dp_dimensions_v
139 WHERE demand_plan_id = p_demand_plan_id;
140
141 --
142 -- check there is only one collapsed dimension.
143 --
144 CURSOR chk_coll_dim (p_demand_plan_id number) IS
145 SELECT COUNT(1)
146 FROM (
147 SELECT dp_dimension_code, count(1)
148 FROM msd_dp_dimensions_v
149 WHERE demand_plan_id = p_demand_plan_id
150 GROUP BY dp_dimension_code
151 HAVING COUNT(1) > 1);
152
153 --
154 -- check all dimensions are used in the demand plan.
155 --
156 CURSOR chk_used_dim (p_demand_plan_id number) IS
157 SELECT DISTINCT dp_dimension_code
158 FROM msd_dp_dimensions_v mddv
159 WHERE demand_plan_id = p_demand_plan_id
160 and not exists (
161 select 1
162 from msd_cs_definitions mcd, msd_cs_defn_dim_dtls mcdd, msd_dp_parameters mdp
163 where mcd.cs_definition_id = mcdd.cs_definition_id
164 and mdp.parameter_type = mcd.name
165 and mdp.demand_plan_id = p_demand_plan_id
166 and mcdd.dimension_code = mddv.dp_dimension_code
167 and mcdd.collect_flag = C_YES_FLAG);
168
169 --
170 -- find user dimensions that don't have hierarchies or have invald hierarchies
171 --
172 CURSOR get_usr_dim_with_no_hier (p_demand_plan_id in number) IS
173 SELECT DISTINCT dp_dimension dp_dimension
174 FROM msd_dp_dimensions_v pd
175 WHERE demand_plan_id = p_demand_plan_id
176 AND dp_dimension_code <> 'TIM'
177 AND NOT EXISTS
178 (SELECT hierarchy_id
179 FROM msd_dp_hierarchies_v dh
180 WHERE pd.demand_plan_id = dh.demand_plan_id
181 AND pd.dp_dimension_code = dh.dp_dimension_code);
182 --
183 -- find dimensions that don't have hierarchies or have invalid hierarchies
184 --
185 CURSOR get_dim_with_no_hier (p_demand_plan_id IN NUMBER) IS
186 SELECT DISTINCT dimension dimension_code
187 FROM msd_dp_dimensions_v pd
188 WHERE demand_plan_id = p_demand_plan_id
189 AND dp_dimension_code <> 'TIM'
190 AND NOT EXISTS
191 (select hierarchy_id
192 from msd_dp_hierarchies_v dh
193 where pd.demand_plan_id = dh.demand_plan_id
194 and pd.dimension_code = dh.owning_dimension_code);
195
196
197 -- Find dimension that exist as dp_dimension code but not as dimension.
198 CURSOR get_hier_collaps (p_demand_plan_id NUMBER) IS
199 SELECT DISTINCT dp_dimension
200 FROM msd_dp_dimensions_v dd1
201 WHERE demand_plan_id = p_demand_plan_id
202 AND NOT EXISTS
203 (select 1
204 from msd_dp_dimensions_v dd2
205 where dd2.demand_plan_id = p_demand_plan_id
206 and dd1.dp_dimension_code = dd2.dimension_code
207 and dd1.dp_dimension_code = dd2.dp_dimension_code
208 );
209 -- Find invalid hierarchies in the plan
210 cursor get_inval_hier (p_demand_plan_id number) is
211 select
212 dh.hierarchy_name,
213 hi.dimension_code
214 from
215 msd_dp_hierarchies_v dh,
216 msd_hierarchies hi
217 where
218 dh.hierarchy_id = hi.hierarchy_id and
219 dh.demand_plan_id = p_demand_plan_id and
220 hi.valid_flag <> '1';
221 --
222 -- Find demand plan dimension, except time, that don't have level values
223 --
224 CURSOR get_dim_no_lvl( p_demand_plan_id NUMBER) IS
225 SELECT DISTINCT dp_dimension_code,
226 hl. hierarchy_name,
227 level_name
228 FROM msd_dp_hierarchies_v dh,
229 msd_hierarchy_levels_v hl
230 WHERE demand_plan_id = p_demand_plan_id
231 AND dp_dimension_code <> 'TIM'
232 AND dh.hierarchy_id = hl.hierarchy_id
233 AND level_id NOT IN
234 (select distinct level_id
235 from msd_level_values lv);
236
237 --
238 -- Validate Time dimensions has values
239 --
240 CURSOR get_tim(p_calendar_type VARCHAR2, p_calendar_code VARCHAR2,
241 p_start_date DATE, p_end_date DATE) IS
242 SELECT MIN(day) min_date, MAX(day) max_date
243 FROM msd_time dp
244 WHERE dp.calendar_type = p_calendar_type
245 AND dp.calendar_code = p_calendar_code
246 AND day between p_start_date and p_end_date;
247
248 --
249 -- Validate all Demand Plan Calendars
250 --
251 CURSOR get_dp_cal(p_demand_plan_id NUMBER) IS
252 SELECT calendar_type, calendar_code, decode(calendar_type,
253 1, initcap(calendar_code),
254 calendar_code) op_cal_code
255 FROM msd_dp_calendars
256 WHERE demand_plan_id = p_demand_plan_id;
257
258 --
259 -- Validate input parameters
260 --
261 CURSOR get_dupl_input_parameter (p_demand_plan_id NUMBER) IS
262 SELECT parameter_type,
263 parameter_name,
264 forecast_date_used,
265 count(*)
266 FROM msd_dp_parameters_cs_v
267 WHERE demand_plan_id = p_demand_plan_id
268 AND parameter_type_id <> '7'
269 GROUP BY parameter_type,
270 parameter_name,
271 forecast_date_used
272 HAVING COUNT(*) > 1;
273 --
274 -- Find all the scenarios
275 --
276 CURSOR get_scen (p_demand_plan_id NUMBER) IS
277 SELECT mds.scenario_id,
278 mds.forecast_based_on,
279 mds.parameter_name,
280 mds.scenario_name,
281 mds.history_start_date,
282 mds.history_end_date,
283 mds.horizon_start_date,
284 mds.horizon_end_date,
285 mds.publish_flag,
286 mds.output_period_type,
287 csd.cs_definition_id,
288 csd.system_flag,
289 csd.multiple_stream_flag,
290 csd.allocation_allowed_flag,
291 csd.lowest_level_flag,
292 mdp.input_demand_plan_id,
293 mdp.input_scenario_id,
294 mdp.forecast_date_used,
295 mdp.start_date prm_start_date,
296 mdp.end_date prm_end_date,
297 nvl(mdp.view_name, nvl(csd.planning_server_view_name,'MSD_CS_DATA_V')) view_name,
298 msd_cs_dfn_utl.get_planning_server_clmn(csd.cs_definition_id, mdp.FORECAST_DATE_USED) date_planning_view_clmn,
299 cdd.collect_level_id
300 FROM msd_dp_scenarios mds,
301 msd_dp_parameters mdp,
302 msd_cs_definitions csd,
303 msd_cs_defn_dim_dtls cdd
304 WHERE mds.demand_plan_id = p_demand_plan_id
305 AND mds.enable_flag = 'Y'
306 AND mdp.demand_plan_id (+) = mds.demand_plan_id
307 AND mdp.parameter_type (+) = mds.forecast_based_on
308 AND mdp.forecast_date_used (+) = mds.forecast_date_used
309 AND nvl(mdp.parameter_name, '-*()') = nvl(mds.parameter_name, '-*()')
310 AND csd.name (+) = mdp.parameter_type
311 AND cdd.cs_definition_id (+) = csd.cs_definition_id
312 AND cdd.dimension_code (+) = 'TIM'
313 and cdd.collect_flag (+) = 'Y';
314
315 --
316 -- get output levels w/Org Specific BOM
317 --
318 CURSOR get_output_levels_org( p_demand_plan_id NUMBER, p_scenario_id NUMBER) IS
319 select count( distinct decode(mlv.level_id, 3, 1, mlv.level_id))
320 from msd_levels mlv,
321 msd_dp_scenario_output_levels mdsol,
322 msd_demand_plans mdp
323 where mdsol.demand_plan_id = p_demand_plan_id
324 and mdsol.demand_plan_id = mdp.demand_plan_id
325 and nvl(mlv.plan_type,'DP') = decode(mdp.plan_type,'SOP','DP','','DP',mdp.plan_type)
326 and mlv.level_id = mdsol.level_id
327 and mdsol.scenario_id = p_scenario_id
328 and mlv.level_id in (7, 1, 3);
329
330 --
331 -- get output levels w/out Org Specific BOM
332 --
333 CURSOR get_output_levels( p_demand_plan_id NUMBER, p_scenario_id NUMBER) IS
334 select count( distinct decode(mlv.level_id, 3, 1, mlv.level_id))
335 from msd_levels mlv,
336 msd_dp_scenario_output_levels mdsol,
337 msd_demand_plans mdp
338 where mdsol.demand_plan_id = p_demand_plan_id
339 and mdsol.demand_plan_id = mdp.demand_plan_id
340 and nvl(mlv.plan_type,'DP') = decode(mdp.plan_type,'SOP','DP','','DP',mdp.plan_type)
341 and mlv.level_id = mdsol.level_id
342 and mdsol.scenario_id = p_scenario_id
343 and mlv.level_id in (1, 3);
344
345 --
346 -- get invalid output parameters. do not check for top level values.
347 --
348 CURSOR get_inv_output_levels ( p_demand_plan_id IN NUMBER) IS
349 SELECT scen.scenario_name,
350 ml.level_name,
351 ml.dimension_code
352 FROM msd_dp_scenario_output_levels a, msd_levels ml, msd_dp_scenarios scen, msd_demand_plans mdp
353 WHERE a.demand_plan_id = p_demand_plan_id
354 and a.demand_plan_id = mdp.demand_plan_id
355 and nvl(ml.plan_type,'DP') = decode(mdp.plan_type,'SOP','DP','','DP',mdp.plan_type)
356 AND a.level_id = ml.level_id
357 AND a.scenario_id = scen.scenario_id
358 AND scen.enable_flag = 'Y'
359 AND a.level_id not in
360 ( select b.level_id
361 from msd_hierarchy_levels b,
362 msd_dp_hierarchies_v c
363 where b.hierarchy_id = c.hierarchy_id
364 and c.demand_plan_id = p_demand_plan_id
365 union
366 select b.parent_level_id
367 from msd_hierarchy_levels b,
368 msd_dp_hierarchies_v c
369 where b.hierarchy_id = c.hierarchy_id
370 and c.demand_plan_id = p_demand_plan_id
371 );
372
373
374
375 --
376 -- get duplicate dimensions in output parameters.
377 --
378 CURSOR get_dup_dim_output_levels ( p_demand_plan_id IN NUMBER) IS
379 SELECT scen.scenario_name,ml.dimension_code, count(*)
380 FROM msd_dp_scenario_output_levels a, msd_levels ml, msd_dp_scenarios scen, msd_demand_plans mdp
381 WHERE a.level_id = ml.level_id
382 and a.demand_plan_id = mdp.demand_plan_id
383 and nvl(ml.plan_type,'DP') = decode(mdp.plan_type,'SOP','DP','','DP',mdp.plan_type)
384 AND a.scenario_id = scen.scenario_id
385 AND scen.enable_flag = 'Y'
386 and scen.demand_plan_id = p_demand_plan_id
387 group by scen.scenario_name,ml.dimension_code
388 having count(*) >1 ;
389
390
391
392
393 --
394 -- get associated input parameter
395 --
396 CURSOR get_input_param(p_demand_plan_id NUMBER,
397 p_parameter_type VARCHAR2,
398 p_cs_name VARCHAR2,
399 p_date_used in VARCHAR2) IS
400 SELECT start_date, end_date
401 FROM msd_dp_parameters
402 WHERE demand_plan_id = p_demand_plan_id
403 AND parameter_type = p_parameter_type
404 AND (((parameter_name IS NULL) AND (p_cs_name IS NULL))
405 OR
406 ((parameter_name IS NOT NULL) AND (p_cs_name IS NOT NULL)
407 AND (parameter_name = p_cs_name)))
408 AND (((forecast_date_used is NULL) and (p_date_used IS NULL))
409 OR
410 ((forecast_date_used IS NOT NULL) AND (p_date_used IS NOT NULL)
411 AND (forecast_date_used = p_date_used)))
412 AND parameter_type <> '7';
413 --
414 -- get invalid input parameters. these parameters do not have
415 -- hierarchies which contain levels in the stream definition.
416 --
417 /* Modified by DWK. for date_clmn: In case of Input scenario which does not
418 have any forecast_date_used column specified, Use 'END_DATE' intead. */
419 CURSOR get_inv_hier_prms (p_demand_plan_id IN NUMBER) IS
420 SELECT DISTINCT mcd.description,
421 mcdd.dimension_code,
422 mcdd.collect_level_id level_id,
423 mcd.cs_definition_id,
424 mcd.multiple_stream_flag,
425 nvl(mcd.planning_server_view_name,'MSD_CS_DATA_V') planning_server_view_name,
426 mdp.parameter_name,
427 mdp.start_date,
428 mdp.end_date,
429 mcd.system_flag,
430 MSD_CS_DFN_UTL.get_planning_server_clmn(mcd.cs_definition_id, nvl(mdp.FORECAST_DATE_USED, 'END_DATE')) date_clmn,
431 mdp.input_demand_plan_id,
432 mdp.input_scenario_id,
433 mdp.revision
434 FROM msd_cs_defn_dim_dtls mcdd,
435 msd_dp_parameters mdp,
436 msd_cs_definitions mcd,
437 msd_dp_dimensions_v mdd
438 WHERE mdp.demand_plan_id = p_demand_plan_id
439 AND mdd.demand_plan_id = p_demand_plan_id
440 AND mdd.dimension_code = mcdd.dimension_code
441 AND mcdd.cs_definition_id = mcd.cs_definition_id
442 AND mcd.name = mdp.parameter_type
443 AND nvl(mcdd.collect_level_id, 0) not in
444 ( select b.level_id
445 from msd_hierarchy_levels b,
446 msd_dp_hierarchies_v c
447 where b.hierarchy_id = c.hierarchy_id
448 and c.demand_plan_id = p_demand_plan_id
449 and mcdd.dimension_code <> 'TIM'
450 union
451 select b.parent_level_id
452 from msd_hierarchy_levels b,
453 msd_dp_hierarchies_v c
454 where b.hierarchy_id = c.hierarchy_id
455 and c.demand_plan_id = p_demand_plan_id
456 and mcdd.dimension_code <> 'TIM'
457 );
458
459 -- Determine whether level is contain in Dp Hierarchies.
460 CURSOR get_inv_hier_lvl_prms (p_demand_plan_id IN NUMBER, p_lvl_id in NUMBER) is
461 select 1
462 from msd_hierarchy_levels b,
463 msd_dp_hierarchies_v c
464 where b.hierarchy_id = c.hierarchy_id
465 and c.demand_plan_id = p_demand_plan_id
466 and (b.level_id = p_lvl_id
467 or
468 b.parent_level_id = p_lvl_id);
469
470 -- Get Invalid Time Hierarchy Params
471 CURSOR get_inv_hier_tim_prms (p_demand_plan_id IN NUMBER, p_lvl_id in NUMBER) IS
472 select meaning
473 from fnd_lookup_values_vl
474 where lookup_type = 'MSD_PERIOD_TYPE'
475 and lookup_code = p_lvl_id
476 and not exists (
477 select 1
478 from msd_dp_calendars
479 where demand_plan_id = p_demand_plan_id
480 and calendar_type = decode(p_lvl_id,
481 1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4,calendar_type));
482
483
484 --
485 -- UOM Conversions
486 --
487 CURSOR uom_conv (p_base_uom VARCHAR2) IS
488 SELECT 1
489 FROM msd_uom_conversions
490 WHERE (from_uom_code = p_base_uom OR to_uom_code = p_base_uom)
491 AND ROWNUM < 2;
492 --
493 -- Currency Conversion
494 --
495 CURSOR curr_conv (p_curr VARCHAR2, p_from_date IN DATE, p_to_date IN DATE) IS
496 SELECT 1
497 FROM msd_currency_conversions
498 WHERE from_currency = NVL(p_curr, from_currency)
499 AND conversion_date BETWEEN p_from_date AND p_to_date
500 AND ROWNUM < 2;
501
502 /* Retrieves all Parameters within a Demand Plan Definition. */
503 CURSOR get_all_input_param (p_demand_plan_id IN NUMBER) IS
504 SELECT mdp.demand_plan_id,
505 mdp.parameter_id,
506 mdp.cs_definition_id,
507 mdp.system_flag, /* Add to view */
508 mdp.cs_type,
509 mdp.parameter_type_id,
510 mdp.parameter_type,
511 mdp.parameter_name,
512 mdp.multiple_stream_flag,
513 mdp.forecast_date_used,
514 mdp.date_planning_view_clmn,
515 mdp.start_date,
516 mdp.end_date,
517 mdp.view_name,
518 mdp.input_demand_plan_id,
519 mdp.input_scenario_id,
520 mdp.revision,
521 mdp.allo_agg_basis_stream_id
522 FROM msd_dp_parameters_cs_v mdp
523 WHERE mdp.demand_plan_id = p_demand_plan_id
524 AND mdp.parameter_type_id <> '7';
525
526 CURSOR get_ps_view_name (p_cs_def_id NUMBER, p_demand_plan_id number) IS
527 SELECT decode(stripe_stream_name,
528 null, decode(stripe_sr_level_pk,
529 null,
530 nvl(planning_server_view_name,'MSD_CS_DATA_V'),
531 planning_server_view_name_ds),
532 planning_server_view_name_ds),
533 planning_server_view_name
534 FROM msd_cs_definitions_vl,
535 msd_demand_plans mdp
536 WHERE cs_definition_id = p_cs_def_id
537 and mdp.demand_plan_id = p_demand_plan_id;
538
539 CURSOR param_cs_wgt (p_cs_def_id NUMBER) IS
540 SELECT count(1)
541 FROM msd_cs_defn_column_dtls a
542 WHERE a.cs_definition_id = p_cs_def_id
543 AND ((a.allocation_type IN ('AVG', 'WGT'))
544 OR (a.aggregation_type = 'WGT')
545 OR exists (select 1
546 from msd_cs_clmn_dim_dtls b
547 where a.cs_column_dtls_id = b.cs_column_dtls_id
548 and ((b.allocation_type IN ('AVG', 'WGT'))
549 OR (b.aggregation_type = 'WGT'))));
550
551
552 CURSOR get_one_param (p_demand_plan_id IN NUMBER, p_parameter_type IN VARCHAR2) IS
553 SELECT cs_definition_id
554 FROM msd_dp_parameters_cs_v
555 WHERE demand_plan_id = p_demand_plan_id
556 AND parameter_type = p_parameter_type
557 AND parameter_type <> '7';
558
559 --Added for multiple composites enhancements
560 --
561 -- check composite group has the same dimension
562 --
563 CURSOR chk_comp_group(p_demand_plan_id NUMBER) IS
564 SELECT DISTINCT DEF2.DESCRIPTION DESCRIPTION,
565 DEF2.COMPOSITE_GROUP_CODE COMPOSITE_GROUP_CODE
566 FROM MSD_CS_DEFINITIONS DEF1,
567 MSD_CS_DEFINITIONS DEF2,
568 MSD_CS_DEFN_DIM_DTLS DIM1,
569 MSD_CS_DEFN_DIM_DTLS DIM2,
570 MSD_DP_PARAMETERS MPV1,
571 MSD_DP_PARAMETERS MPV2
572 WHERE MPV1.DEMAND_PLAN_ID = p_demand_plan_id
573 AND DEF1.name = MPV1.parameter_type
574 AND DEF1.COMPOSITE_GROUP_CODE IS NOT NULL
575 AND NVL(DEF1.ENABLE_FLAG,'Y') = 'Y'
576 AND DIM1.CS_DEFINITION_ID = DEF1.CS_DEFINITION_ID
577 AND NVL(DEF2.ENABLE_FLAG,'Y') = 'Y'
578 AND NVL(DEF2.COMPOSITE_GROUP_CODE,-9999) = NVL(DEF1.COMPOSITE_GROUP_CODE,-9998)
579 AND DIM2.CS_DEFINITION_ID = DEF2.CS_DEFINITION_ID
580 AND DIM2.DIMENSION_CODE = DIM1.DIMENSION_CODE
581 AND DIM2.COLLECT_FLAG <> DIM1.COLLECT_FLAG
582 AND MPV2.DEMAND_PLAN_ID = p_demand_plan_id
583 AND MPV2.parameter_type <> MPV1.parameter_type
584 AND MPV2.parameter_type = DEF2.name;
585
586 --
587 -- check composite group has the same level
588 --
589 CURSOR chk_comp_group_lvl(p_demand_plan_id NUMBER) IS
590 SELECT DEF1.DESCRIPTION DESCRIPTION1,
591 DEF2.DESCRIPTION DESCRIPTION2,
592 DEF2.NAME NAME2,
593 DEF1.NAME NAME1,
594 DEF2.COMPOSITE_GROUP_CODE COMPOSITE_GROUP_CODE,
595 NVL(DIM1.COLLECT_LEVEL_ID,-1234) LEVEL_ID1,
596 NVL(DIM2.COLLECT_LEVEL_ID,-9999) LEVEL_ID2,
597 DIM1.DIMENSION_CODE DIM1_CODE,
598 DIM2.DIMENSION_CODE DIM2_CODE,
599 DEF1.CS_DEFINITION_ID CS_ID1,
600 DEF2.CS_DEFINITION_ID CS_ID2,
601 nvl(DEF1.PLANNING_SERVER_VIEW_NAME,'MSD_CS_DATA_V') VIEW_NAME1,
602 nvl(DEF2.PLANNING_SERVER_VIEW_NAME,'MSD_CS_DATA_V') VIEW_NAME2,
603 MPV1.START_DATE START_DATE1,
604 MPV1.END_DATE END_DATE1,
605 MPV2.START_DATE START_DATE2,
606 MPV2.END_DATE END_DATE2,
607 MPV1.INPUT_DEMAND_PLAN_ID INPUT_DEMAND_PLAN_ID1,
608 MPV2.INPUT_DEMAND_PLAN_ID INPUT_DEMAND_PLAN_ID2,
609 MPV1.INPUT_SCENARIO_ID INPUT_SCENARIO_ID1,
610 MPV2.INPUT_SCENARIO_ID INPUT_SCENARIO_ID2,
611 DECODE(DEF1.NAME,'MSD_INPUT_SCENARIO',MPV1.REVISION,MPV1.PARAMETER_NAME) PARAM1,
612 DECODE(DEF2.NAME,'MSD_INPUT_SCENARIO',MPV2.REVISION,MPV2.PARAMETER_NAME) PARAM2,
613 DECODE(DEF1.NAME,'MSD_INPUT_SCENARIO','TIME_LVL_VAL_FROM',MSD_CS_DFN_UTL.get_planning_server_clmn(def1.cs_definition_id, mpv1.FORECAST_DATE_USED)) DATE_CLMN1,
614 DECODE(DEF2.NAME,'MSD_INPUT_SCENARIO','TIME_LVL_VAL_FROM',MSD_CS_DFN_UTL.get_planning_server_clmn(def2.cs_definition_id, mpv2.FORECAST_DATE_USED)) DATE_CLMN2,
615 DEF1.SYSTEM_FLAG SYSTEM_FLAG1,
616 DEF2.SYSTEM_FLAG SYSTEM_FLAG2,
617 DEF1.MULTIPLE_STREAM_FLAG MULTI_STREAM_FLAG1,
618 DEF2.MULTIPLE_STREAM_FLAG MULTI_STREAM_FLAG2
619 FROM MSD_CS_DEFINITIONS DEF1,
620 MSD_CS_DEFINITIONS DEF2,
621 MSD_CS_DEFN_DIM_DTLS DIM1,
622 MSD_CS_DEFN_DIM_DTLS DIM2,
623 MSD_DP_PARAMETERS MPV1,
624 MSD_DP_PARAMETERS MPV2
625 WHERE MPV1.DEMAND_PLAN_ID = p_demand_plan_id
626 AND DEF1.name = MPV1.parameter_type
627 AND NVL(DEF1.ENABLE_FLAG,'Y') = 'Y'
628 --AND NVL(DEF1.LOWEST_LEVEL_FLAG,1) = 0
629 AND DIM1.CS_DEFINITION_ID = DEF1.CS_DEFINITION_ID
630 AND DIM1.COLLECT_FLAG = 'Y'
631 AND NVL(DEF2.ENABLE_FLAG,'Y') = 'Y'
632 --AND NVL(DEF2.LOWEST_LEVEL_FLAG,1) = 0
633 AND NVL(DEF2.COMPOSITE_GROUP_CODE,-9999) = NVL(DEF1.COMPOSITE_GROUP_CODE,-1234)
634 AND DIM2.CS_DEFINITION_ID = DEF2.CS_DEFINITION_ID
635 AND DIM2.DIMENSION_CODE = DIM1.DIMENSION_CODE
636 AND DIM2.COLLECT_FLAG = 'Y' -- Bug# 4562757
637 AND NVL(DIM2.COLLECT_LEVEL_ID,-9999) <> NVL(DIM1.COLLECT_LEVEL_ID,-1234)
638 AND MPV2.DEMAND_PLAN_ID = p_demand_plan_id
639 AND MPV2.parameter_type <> MPV1.parameter_type
640 AND MPV2.parameter_type = DEF2.name
641 ORDER BY NAME2;
642
643 --
644 -- Derives the level_id column name in the planning server view for a given
645 -- Dimension and stream
646 --
647 CURSOR get_lvl_column_name(p_dim_code Varchar2,p_cs_id NUMBER) IS
648 SELECT decode(planning_view_column_name,'TIM_LEVEL_ID','TIME_LEVEL_ID',
649 planning_view_column_name)
650 FROM msd_cs_defn_column_dtls_v
651 WHERE column_identifier = upper(p_dim_code)||'_LEVEL_ID'
652 AND identifier_type = 'DIMENSION_ID'
653 AND cs_definition_id = p_cs_id;
654
655 --Added for multiple composites enhancements
656
657 /*
658 * Variables
659 */
660 dummy varchar2(80);
661 l_cnt number;
662 l_dp_max_date date;
663 l_dp_min_date date;
664 l_a_date date;
665 l_b_date date;
666 l_dp_rec get_dp%rowtype;
667 l_min_date date;
668 l_max_date date;
669 l_result varchar2(30);
670 g_ret_code number;
671 --
672 -- USED BY DISPLAY_MESSAGE
673 --
674 l_last_msg_type varchar2(30);
675 --
676 -- Define Exception
677 --
678 EX_FATAL_ERROR Exception;
679 --
680 -- Private functions/proceudres
681 --
682 --
683 -- Store result
684 --
685 Procedure calc_result ( p_msg_type in varchar2) is
686 Begin
687 if p_msg_type = FATAL_ERROR then
688 g_ret_code := 4;
689 l_result := FATAL_ERROR;
690 elsif p_msg_type = ERROR then
691 g_ret_code := 2;
692 l_result := p_msg_type;
693 elsif p_msg_type = WARNING then
694 if g_ret_code <> 2 then
695 g_ret_code := 1;
696 l_result := p_msg_type;
697 end if;
698 end if;
699 End;
700 --
701 Procedure show_message(p_text in varchar2) is
702
703 Begin
704
705 if (p_text is not NULL) then
706 fnd_file.put_line(fnd_file.log, p_text);
707 -- dbms_output.put_line(p_text);
708 end if;
709
710 --
711 end;
712
713 Procedure debug_out(p_text in varchar2) is
714 i number := 1;
715 Begin
716 while i<= length(p_text) loop
717 fnd_file.put_line(fnd_file.output, substr(p_text, i, 90));
718 -- dbms_output.put_line(substr(p_text, i, 90));
719 i := i+90;
720 end loop;
721 end;
722 --
723
724 Procedure display_message(p_text varchar2, msg_type varchar2 default null) is
725 l_tab varchar2(4):=' ';
726 L_MAX_LENGTH number:=90;
727 Begin
728 if msg_type = SECTION then
729 if nvl(l_last_msg_type, 'xx') <> SECTION then
730 show_message('');
731 end if;
732 --
733 show_message( substr(p_text, 1, L_MAX_LENGTH) );
734 --
735 elsif msg_type in (INFORMATION, HEADING) then
736 show_message( l_tab || substr(p_text, 1, L_MAX_LENGTH));
737 else
738 show_message( l_tab || rpad(p_text, L_MAX_LENGTH) || ' ' || msg_type );
739 end if;
740 --
741 if msg_type in (ERROR, WARNING, FATAL_ERROR) then
742 calc_result (msg_type);
743 end if;
744 --
745 if msg_type = FATAL_ERROR then
746 show_message(' ');
747 show_message( l_tab || 'Exiting Demand Plan validation process with FATAL ERROR');
748 raise EX_FATAL_ERROR;
749 end if;
750 --
751 l_last_msg_type := msg_type;
752 End;
753 --
754 Procedure Blank_Line is
755 Begin
756 fnd_file.put_line(fnd_file.log, '');
757 -- dbms_output.put_line('');
758 End;
759 --
760 PROCEDURE CHK_CAL_FOR_BUCKET (p_date IN DATE,
761 p_demand_plan_id IN NUMBER,
762 p_field_name IN VARCHAR2) IS
763 CURSOR C1 IS
764 SELECT count(1)
765 FROM msd_time mtv, msd_dp_calendars mdc
766 WHERE mtv.day = p_date
767 AND rownum = 1
768 AND mtv.calendar_type = mdc.calendar_type
769 AND mtv.calendar_code = mdc.calendar_code
770 AND mdc.demand_plan_id = p_demand_plan_id;
771
772
773 num_buckets number := 0;
774
775 BEGIN
776
777 open c1;
778 fetch c1 into num_buckets;
779
780 if num_buckets = 0 then
781 display_message(p_field_name || 'not in any calendars', WARNING);
782 end if;
783
784 close c1;
785
786 END CHK_CAL_FOR_BUCKET;
787
788 Function func_output_level_string (p_demand_plan_id in number,
789 p_scenario_id in number)
790 Return varchar2 is
791
792 cursor c1(p_demand_plan_id number,p_scenario_id number) is
793 select
794 --scen.scenario_name,
795 dim1||decode(dim2,NULL,'',','||dim2)||decode(dim3,NULL,'',','||dim3)||decode(dim4,NULL,'',','||dim4)||decode(dim5,NULL,'',','||dim5)||decode(dim6,NULL,'',','||dim6)||decode(dim7,NULL,'',','||dim7)||decode(dim8,NULL,'',','||dim8)
796 from (
797 select demand_plan_id,scenario_id,level_id as dim1,
798 LEAD(level_id,1) over (partition by scenario_id order by level_id) as dim2,
799 LEAD(level_id,2) over (partition by scenario_id order by level_id) as dim3,
800 LEAD(level_id,3) over (partition by scenario_id order by level_id) as dim4,
801 LEAD(level_id,4) over (partition by scenario_id order by level_id) as dim5,
802 LEAD(level_id,5) over (partition by scenario_id order by level_id) as dim6,
803 LEAD(level_id,6) over (partition by scenario_id order by level_id) as dim7,
804 LEAD(level_id,7) over (partition by scenario_id order by level_id) as dim8,
805 row_number() over (partition by scenario_id order by level_id) as rno
806 from msd_dp_scenario_output_levels
807 ) a,
808 msd_dp_scenarios scen
809 where a.rno=1
810 and a.scenario_id = scen.scenario_id
811 and a.demand_plan_id = scen.demand_plan_id
812 and scen.demand_plan_id = p_demand_plan_id
813 and scen.scenario_id = p_scenario_id;
814
815 l_output_level_str varchar2(240) := to_char(NULL);
816 Begin
817
818 open c1 (p_demand_plan_id,p_scenario_id);
819 fetch c1 into l_output_level_str;
820 close c1;
821
822 if (l_output_level_str is null) then
823 l_output_level_str := '-999';
824 end if;
825
826 return l_output_level_str;
827
828 End func_output_level_string;
829 --
830 -- Validate that Demand Priority Scenario Output Levels are matched with the Scenario Attached
831 --
832 Procedure chk_priority_scen_levels ( p_demand_plan_id in number) is
833
834 cursor c1(p_demand_plan_id number) is
835 select a.scenario_id ,a.scenario_name fcst_scenario_name,a.dmd_priority_scenario_id,b.scenario_name pri_scenario_name
836 from msd_dp_scenarios a,
837 msd_dp_scenarios b
838 where a.demand_plan_id = p_demand_plan_id
839 and a.dmd_priority_scenario_id is not null
840 and b.scenario_id = a.dmd_priority_scenario_id
841 and b.demand_plan_id = p_demand_plan_id;
842
843
844 Begin
845
846 if l_debug = 'Y' then
847 debug_out( 'Entering chk_priority_scen_levels ' || to_char(sysdate, 'hh24:mi:ss'));
848 end if;
849
850 display_message( 'Validating Forecast and attached Priority Scenario Output Levels' , SECTION);
851 display_message( rpad('Forecast Scenario Name', 30) || ' ' || rpad('Demand Priority Scenario Name', 30) || ' ' ||
852 'Error Description' , HEADING);
853 display_message( rpad('-', 30, '-') || ' ' || rpad('-', 30, '-') || ' ' ||
854 rpad('-' ,30, '-'), HEADING);
855 --
856 for c1_rec in c1(p_demand_plan_id) loop
857
858 if func_output_level_string(p_demand_plan_id,c1_rec.scenario_id) <> func_output_level_string(p_demand_plan_id,c1_rec.dmd_priority_scenario_id) then
859 --
860 -- error in output levels
861 display_message( rpad(c1_rec.fcst_scenario_name, 30) || ' ' ||
862 rpad(c1_rec.pri_scenario_name, 30) || 'has different output levels.', ERROR);
863
864 end if;
865
866 end loop;
867
868 if l_debug = 'Y' then
869 debug_out( 'Exiting chk_priority_scen_levels ' || to_char(sysdate, 'hh24:mi:ss'));
870 end if;
871
872 End chk_priority_scen_levels;
873
874
875
876 Procedure validate_demand_plan(
877 errbuf out nocopy varchar2,
878 retcode out nocopy varchar2,
879 p_demand_plan_id in number) is
880
881 -- Delete before arcsing in
882 -- i number := 0;
883 --
884
885 /*Bug# 4345323 Used to store the plan type(LIABILITY or SOP or NULL(for DP)) */
886 l_plan_type VARCHAR2(255);
887
888 Begin
889
890
891 /* Bug# 4345323 Get the plan type of the plan */
892 SELECT plan_type
893 INTO l_plan_type
894 FROM msd_demand_plans
895 WHERE demand_plan_id = p_demand_plan_id;
896
897 /* Bug# 4345323 Call validate_liability_plan if plan type is LIABILITY*/
898 IF nvl(l_plan_type,'DP') = 'LIABILITY' THEN
899
900 MSD_LIABILITY.validate_liability_plan (
901 errbuf,
902 retcode,
903 p_demand_plan_id );
904
905 RETURN;
906 END IF;
907
908
909 /* Make sure this plan is not being used. */
910 Lock_Row(p_demand_plan_id);
911
912 if ( g_ret_code = '2' ) then
913 retcode := '2';
914 return;
915 end if;
916
917
918 /* Set demand plan for session */
919 msd_stripe_demand_plan.set_demand_plan(p_demand_plan_id);
920
921 /* Build/Update Stripe for demand plan */
922 msd_stripe_demand_plan.stripe_demand_plan(errbuf,
923 retcode,
924 p_demand_plan_id);
925
926 /* Only continue if no errors were reported. */
927 if (retcode = '2') then
928 return;
929 end if;
930 /* End Build Stripe */
931
932 /* Print the debug statement if debug is on */
933 if l_debug = 'Y' then
934 debug_out( 'Entering Validate_Demand_Plan ' || to_char(sysdate, 'hh24:mi:ss'));
935 end if;
936
937 --
938 -- initialize
939 --
940 l_result := SUCCESS;
941 g_ret_code := 0;
942 --
943 -- find demand plan
944 --
945 display_message('Demand Plan Validation Process ', SECTION);
946 display_message(' ', HEADING);
947 display_message('Demand Plan Definition Details', HEADING);
948 display_message('------------------------------', HEADING);
949 display_message('Demand Plan ID : ' || p_demand_plan_id, INFORMATION);
950 -- get demand plan
951 open get_dp (p_demand_plan_id);
952 fetch get_dp into l_dp_rec;
953 if get_dp%notfound then
954 close get_dp;
955 display_message('Demand Plan : ' || p_demand_plan_id || ' not found ', FATAL_ERROR);
956 return;
957 else
958 display_message('Demand Plan Name : ' || l_dp_rec.demand_plan_name , INFORMATION);
959 end if;
960 close get_dp;
961 --
962 -- Validate that required dimensions defined for the plan.
963 --
964 chk_required_dim (p_demand_plan_id);
965 --
966 -- Validate that number of user dimensions defined for the plan are valid.
967 --
968 chk_usr_dim (p_demand_plan_id);
969 --
970 -- Validate that the dimensions defined for the plan are used.
971 --
972 chk_usd_dim (p_demand_plan_id);
973 --
974 -- Check all the dimensions, except time, have at least one valid hierarchy associated with it
975 -- and no invalid hierarchy is associated
976 --
977 chk_dim_hier (p_demand_plan_id);
978 --
979 -- Check that dimensions, except time, have level values
980 --
981 chk_dim_lvl_val (p_demand_plan_id );
982 --
983 -- Validate that input parameters are unique
984 --
985 chk_input_param (p_demand_plan_id);
986 --
987 -- Check scenario has related fact data
988 --
989 chk_fact_data (p_demand_plan_id);
990 --
991 -- Validate scenarios
992 --
993 chk_scenarios (p_demand_plan_id,
994 l_dp_rec.g_min_tim_lvl_id,
995 l_dp_rec.m_min_tim_lvl_id,
996 l_dp_rec.f_min_tim_lvl_id,
997 l_dp_rec.c_min_tim_lvl_id);
998 --
999 -- Validate output levels
1000 --
1001 chk_output_levels (p_demand_plan_id);
1002 chk_dup_dim_output_levels(p_demand_plan_id);
1003
1004 chk_priority_scen_levels(p_demand_plan_id);
1005 --
1006 -- Verify Calendar and Time dimension
1007 --
1008 chk_time_data (p_demand_plan_id);
1009 --
1010 -- Validate Demand Plan Calendars data
1011 --
1012 chk_calendars (p_demand_plan_id, 1, l_dp_rec.g_min_tim_lvl_id);
1013 chk_calendars (p_demand_plan_id, 2, l_dp_rec.m_min_tim_lvl_id);
1014 chk_calendars (p_demand_plan_id, 3, l_dp_rec.f_min_tim_lvl_id);
1015 chk_calendars (p_demand_plan_id, 4, l_dp_rec.c_min_tim_lvl_id);
1016 --
1017 -- Validate Lowest Time Levels
1018 --
1019 chk_min_time (l_dp_rec.g_min_tim_lvl_id,
1020 l_dp_rec.m_min_tim_lvl_id,
1021 l_dp_rec.f_min_tim_lvl_id,
1022 l_dp_rec.c_min_tim_lvl_id);
1023 --
1024 -- Validate UOM conversion data
1025 --
1026 chk_uom_data (p_demand_plan_id );
1027 --
1028 -- Validate currency conversion data
1029 --
1030 chk_curr_data (p_demand_plan_id );
1031 --
1032
1033 chk_scen_events(p_demand_plan_id);
1034
1035 --Added for multiple composites enhancements
1036 --
1037 -- Validate whether composite groups has the same dimension
1038 --
1039 chk_composite_group_dimension (p_demand_plan_id);
1040 --
1041
1042 --
1043 -- Validate whether composite groups has the same levels
1044 --
1045 chk_composite_group_level (p_demand_plan_id);
1046 --
1047 --Added for multiple composites enhancements
1048
1049 /* Bug# 5248868
1050 * This procedure validates that whether price list data exists
1051 * for the price lists specified in the demand plan.
1052 * Note: Only time range validation is done
1053 */
1054 chk_price_list_data (p_demand_plan_id);
1055
1056 --
1057 -- Validate Item Validation Org
1058 --
1059 chk_iv_org ( p_demand_plan_id , l_dp_rec.use_org_specific_bom_flag, l_dp_rec.stripe_stream_name, l_dp_rec.stripe_sr_level_pk);
1060 --
1061
1062 --
1063 -- Validate the Organizations for ISO
1064 --
1065 chk_iso_org ( p_demand_plan_id );
1066 --
1067
1068 display_message('End Validation Process.', SECTION);
1069 display_message('Exiting with : ', l_result);
1070 --
1071 retcode := g_ret_code;
1072 -- update plan
1073 update_plan(p_demand_plan_id, g_ret_code);
1074 --
1075
1076 Exception
1077 When EX_FATAL_ERROR then
1078 retcode := 2;
1079 errbuf := substr( sqlerrm, 1, 80);
1080 when others then
1081 retcode := 2;
1082 errbuf := substr( sqlerrm, 1, 80);
1083 End;
1084
1085 Procedure chk_required_dim (p_demand_plan_id in number) is
1086 Begin
1087
1088 if l_debug = 'Y' then
1089 debug_out( 'Entering chk_required_dim ' || to_char(sysdate, 'hh24:mi:ss'));
1090 end if;
1091
1092 display_message('Checking for required dimensions ', SECTION);
1093 display_message('Dimension Name ', HEADING);
1094 display_message('-------------------------------- ', HEADING);
1095 --
1096 /* Check that Product and Time dimensions are defined */
1097
1098 dummy := null;
1099 open get_dim(p_demand_plan_id, 'TIM', 'TIM');
1100 fetch get_dim into dummy;
1101 close get_dim;
1102
1103 if dummy is null then
1104 -- Time dimension is not defined
1105 -- write_error('Time Dimension not defined');
1106 display_message('Time Dimension does not exist ' , ERROR);
1107 end if;
1108
1109 dummy := null;
1110 open get_dim(p_demand_plan_id, 'PRD', 'PRD');
1111 fetch get_dim into dummy;
1112 close get_dim;
1113
1114 if dummy is null then
1115 -- Product dimension is not defined
1116 -- write_error('Product Dimension not defined');
1117 display_message('Product Dimension is not defined ' , ERROR);
1118 end if;
1119
1120 if ((nvl(l_dp_rec.enable_fcst_explosion, 'Y') = 'Y')
1121 and
1122 (l_dp_rec.use_org_specific_bom_flag='Y')) then
1123 -- For Dependent Demand ORG is a mandatory dimension
1124 dummy := null;
1125 open get_dim(p_demand_plan_id, 'ORG', 'ORG');
1126 fetch get_dim into dummy;
1127 close get_dim;
1128
1129 if dummy is null then
1130 -- ORG dimension is not defined
1131 -- write_error('Organization Dimension not defined');
1132 display_message('Organization Dimension is not defined ' , ERROR);
1133 end if;
1134 end if;
1135
1136 if l_debug = 'Y' then
1137 debug_out( 'Exiting chk_required_dim ' || to_char(sysdate, 'hh24:mi:ss'));
1138 end if;
1139 End;
1140
1141 Procedure chk_usr_dim (p_demand_plan_id in number) is
1142 Begin
1143
1144 if l_debug = 'Y' then
1145 debug_out( 'Entering chk_usr_dim ' || to_char(sysdate, 'hh24:mi:ss'));
1146 end if;
1147
1148 display_message('Checking user dimensions ', SECTION);
1149 display_message('Error Description', HEADING);
1150 display_message(rpad('-', 60, '-'), HEADING);
1151 --
1152 -- Check that there are at least three user dimesnions and maximum of four in total
1153 --
1154 open chk_user_dim( p_demand_plan_id);
1155 fetch chk_user_dim into l_cnt;
1156 close chk_user_dim;
1157
1158 if l_cnt < 3 then
1159 -- write_error('Demand Plan must have at least three user dimensions');
1160 display_message('Demand Plan must have at least three user dimensions' , ERROR);
1161 elsif l_cnt > 4 then
1162 -- write_error('Demand Plan can not have more than four user dimensions');
1163 display_message('Demand Plan has more than four user dimensions' , ERROR);
1164 end if;
1165
1166 --
1167 -- Check that there is only one user dimension collapsed.
1168 --
1169 l_cnt := 0;
1170 open chk_coll_dim(p_demand_plan_id);
1171 fetch chk_coll_dim into l_cnt;
1172 close chk_coll_dim;
1173
1174 if l_cnt > 1 then
1175 display_message('Demand Plan only supports one collapsed dimension.' , ERROR);
1176 end if;
1177
1178 if l_debug = 'Y' then
1179 debug_out( 'Exiting chk_usr_dim ' || to_char(sysdate, 'hh24:mi:ss'));
1180 end if;
1181
1182 End;
1183 --
1184
1185 Procedure chk_usd_dim (p_demand_plan_id in number) is
1186 Begin
1187
1188 if l_debug = 'Y' then
1189 debug_out( 'Entering chk_usd_dim ' || to_char(sysdate, 'hh24:mi:ss'));
1190 end if;
1191
1192 display_message('Checking used dimensions ', SECTION);
1193 display_message(rpad('Dimension Name', 31) || rpad('Error description', 40), HEADING);
1194 display_message(rpad('-', 30, '-') || ' ' || rpad('-', 50, '-'), HEADING);
1195
1196 for l_token in chk_used_dim(p_demand_plan_id) loop
1197 display_message(rpad(l_token.dp_dimension_code, 31) || rpad('Not included in any input parameters.', 40), ERROR);
1198 end loop;
1199
1200
1201 if l_debug = 'Y' then
1202 debug_out( 'Exiting chk_usd_dim ' || to_char(sysdate, 'hh24:mi:ss'));
1203 end if;
1204
1205 End;
1206 --
1207
1208 Procedure chk_dim_hier (p_demand_plan_id in number) is
1209 Begin
1210
1211 if l_debug = 'Y' then
1212 debug_out( 'Entering chk_dim_hier ' || to_char(sysdate, 'hh24:mi:ss'));
1213 end if;
1214
1215 display_message('Checking for Dimensions with no hierarchy' , SECTION);
1216 display_message(rpad('Dimension Name', 31) || rpad('Type ', 11) || rpad('Error description', 40), HEADING);
1217 display_message(rpad('-', 30, '-') || ' ' || rpad('-', 10, '-') || ' ' || rpad('-', 50, '-'), HEADING);
1218 --
1219 for dim_usr_rec in get_usr_dim_with_no_hier( p_demand_plan_id)
1220 loop
1221 -- dimensions fetched here have no hierarchy
1222 display_message(rpad(dim_usr_rec.dp_dimension, 31) || rpad('USER', 11) ||
1223 'has no associated hierarchy' , ERROR);
1224 end loop;
1225 --
1226 for dim_rec in get_dim_with_no_hier( p_demand_plan_id)
1227 loop
1228 -- dimensions fetched here have no hierarchy
1229 display_message(rpad(dim_rec.dimension_code, 31) || rpad('DIM', 11) ||
1230 'has no associated hierarchy' , ERROR);
1231 end loop;
1232 --
1233
1234 for c_rec in get_hier_collaps (p_demand_plan_id)
1235 loop
1236 display_message( rpad(c_rec.dp_dimension, 31) || 'does not exist as Dimension itself.', ERROR);
1237 end loop;
1238 --
1239
1240 display_message('Checking for invalid hierarchies for the demand plan' , SECTION);
1241 display_message('Hierarchy Name', HEADING);
1242 display_message(rpad('-', 30, '-'), HEADING);
1243 --
1244 /* Check if there are any invalid hierarchies in the demand plan */
1245 for hier_rec in get_inval_hier(p_demand_plan_id)
1246 loop
1247 display_message(hier_rec.hierarchy_name , ERROR);
1248 end loop;
1249 --
1250
1251 if l_debug = 'Y' then
1252 debug_out( 'Exiting chk_dim_hier ' || to_char(sysdate, 'hh24:mi:ss'));
1253 end if;
1254
1255 End;
1256 --
1257 -- Procedure to check dimension has level values
1258 --
1259 Procedure chk_dim_lvl_val (p_demand_plan_id in number) is
1260 Begin
1261
1262 if l_debug = 'Y' then
1263 debug_out( 'Entering chk_dim_lvl ' || to_char(sysdate, 'hh24:mi:ss'));
1264 end if;
1265
1266 display_message('Checking for dimensions that do not have level values ' , SECTION);
1267 display_message(rpad('DIM', 6) || rpad('Hierarchy Name', 33) || rpad('Level Name', 33), HEADING);
1268 display_message(rpad('-', 5, '-') || rpad(' ', 32, '-') || rpad(' ', 32, '-'), HEADING);
1269 --
1270
1271
1272 /* Check that each dimension, except time, has level values */
1273 for get_dim_no_lvl_rec in get_dim_no_lvl (p_demand_plan_id)
1274 loop
1275 display_message(rpad(get_dim_no_lvl_rec.dp_dimension_code, 5) || ' ' ||
1276 rpad(get_dim_no_lvl_rec.hierarchy_name, 32) || ' ' ||
1277 rpad(get_dim_no_lvl_rec.level_name, 32) , ERROR);
1278 end loop;
1279
1280 if l_debug = 'Y' then
1281 debug_out( 'Exiting chk_dim_lvl ' || to_char(sysdate, 'hh24:mi:ss'));
1282 end if;
1283
1284 End;
1285 --
1286 -- Procedure to check unique input parameters
1287 --
1288 Procedure chk_input_param (p_demand_plan_id in number) is
1289 lv_level_id1 number;
1290 x_meaning varchar2(200);
1291 x_name varchar2(300);
1292 x_parameter_name varchar2(320);
1293 x_is_dp_hier_level number;
1294
1295 cursor get_cs_name (p_cs_definition_id in number) is
1296 select name
1297 from msd_cs_definitions
1298 where cs_definition_id = p_cs_definition_id;
1299
1300 /* Bug #5464757 */
1301 Cursor COL_DIM_IN_PARA(P_PARA_TYPE in varchar2) is
1302 SELECT MDD.DIMENSION_CODE dim_code FROM MSD.msd_dP_dimensions MDD
1303 where MDD.demand_plan_id =p_demand_plan_id
1304 and MDD.dimension_code<>MDD.dp_dimension_code
1305 AND MDD.DIMENSION_CODE NOT IN
1306 (SELECT MCD.DIMENSION_CODE
1307 FROM MSD_CS_DEFN_DIM_DTLS MCD
1308 WHERE MCD.CS_DEFINITION_ID IN
1309 (select MCDS.CS_DEFINITION_ID
1310 FROM MSD_CS_DEFINITIONS MCDS
1311 WHERE MCDS.NAME = P_PARA_TYPE)
1312 and mcd.collect_flag='Y')
1313 AND MDD.DP_DIMENSION_CODE IN
1314 (SELECT MCD.DIMENSION_CODE
1315 FROM MSD_CS_DEFN_DIM_DTLS MCD
1316 WHERE MCD.CS_DEFINITION_ID IN
1317 (select MCDS.CS_DEFINITION_ID
1318 FROM MSD_CS_DEFINITIONS MCDS
1319 WHERE MCDS.NAME = P_PARA_TYPE)
1320 and mcd.collect_flag='Y')
1321 Union All
1322 SELECT MDD.DP_DIMENSION_CODE dim_code FROM MSD.msd_dP_dimensions MDD
1323 where MDD.demand_plan_id =p_demand_plan_id
1324 and MDD.dimension_code<>MDD.dp_dimension_code
1325 AND MDD.DP_DIMENSION_CODE NOT IN
1326 (SELECT MCD.DIMENSION_CODE
1327 FROM MSD_CS_DEFN_DIM_DTLS MCD
1328 WHERE MCD.CS_DEFINITION_ID IN
1329 (select MCDS.CS_DEFINITION_ID
1330 FROM MSD_CS_DEFINITIONS MCDS
1331 WHERE MCDS.NAME = P_PARA_TYPE)
1332 and mcd.collect_flag='Y')
1333 AND MDD.DIMENSION_CODE IN
1334 (SELECT MCD.DIMENSION_CODE
1335 FROM MSD_CS_DEFN_DIM_DTLS MCD
1336 WHERE MCD.CS_DEFINITION_ID IN
1337 (select MCDS.CS_DEFINITION_ID
1338 FROM MSD_CS_DEFINITIONS MCDS
1339 WHERE MCDS.NAME = P_PARA_TYPE)
1340 and mcd.collect_flag='Y');
1341
1342 Begin
1343
1344 if l_debug = 'Y' then
1345 debug_out( 'Entering chk_input_param ' || to_char(sysdate, 'hh24:mi:ss'));
1346 end if;
1347
1348 display_message('Validating Input Parameters ' , SECTION);
1349 display_message( rpad('Parameter Type', 31) ||
1350 rpad('Forecast By /Parameter Name', 31) ||
1351 rpad('Condition', 31) , HEADING);
1352 display_message( rpad('-', 30, '-') || ' ' ||
1353 rpad('-', 30, '-') || ' ' ||
1354 rpad('-', 30, '-'), HEADING);
1355 --
1356 /* Valdate input parameters */
1357 for param_rec in get_dupl_input_parameter(p_demand_plan_id)
1358 loop
1359 -- all records fetched are erroneous
1360 -- parameter_type, forecast_date_used must be unique for a demand plan
1361 display_message( rpad(param_rec.parameter_type, 31) ||
1362 rpad(nvl(param_rec.forecast_date_used, param_rec.parameter_name), 30) ||
1363 rpad('Duplicate Parameter.', 30),
1364 ERROR);
1365 end loop;
1366
1367 /* Check to see if a Parameter contains a level which is not
1368 * included in any attached hierachies.
1369 */
1370
1371 for param_rec in get_inv_hier_prms(p_demand_plan_id)
1372 loop
1373
1374 if (param_rec.level_id is null) then
1375
1376 -- Use revision if an Input Scenario
1377 open get_cs_name(param_rec.cs_definition_id);
1378 fetch get_cs_name into x_name;
1379 close get_cs_name;
1380
1381 if (x_name = 'MSD_INPUT_SCENARIO') then
1382 x_parameter_name := param_rec.revision;
1383 else
1384 x_parameter_name := param_rec.parameter_name;
1385 end if;
1386
1387 --Find level id used in stream
1388 lv_level_id1 := get_level_id(param_rec.planning_server_view_name,
1389 get_level_column_name(param_rec.dimension_code,
1390 param_rec.cs_definition_id),
1391 param_rec.date_clmn,
1392 param_rec.start_date,
1393 param_rec.end_date,
1394 param_rec.system_flag,
1395 param_rec.multiple_stream_flag,
1396 x_parameter_name,
1397 param_rec.cs_definition_id,
1398 param_rec.level_id,
1399 param_rec.input_demand_plan_id,
1400 param_rec.input_scenario_id);
1401
1402 else
1403 lv_level_id1 := param_rec.level_id;
1404
1405 end if;
1406
1407
1408 if lv_level_id1 is not null and param_rec.dimension_code <> 'TIM' then
1409 -- all records fetched are erroneous
1410
1411 begin
1412
1413 open get_inv_hier_lvl_prms (p_demand_plan_id, lv_level_id1);
1414 fetch get_inv_hier_lvl_prms into x_is_dp_hier_level;
1415 close get_inv_hier_lvl_prms;
1416
1417 if (x_is_dp_hier_level is null) then
1418 display_message( rpad(param_rec.description, 62) ||
1419 rpad(msd_common_utilities.get_level_name(lv_level_id1), 28),
1420 ERROR);
1421 display_message( rpad(' ', 62) ||
1422 rpad('Not in DP Hierarchies.', 28),
1423 INFORMATION);
1424 end if;
1425
1426 exception when others then
1427 null;
1428 end;
1429
1430 elsif lv_level_id1 is not null and param_rec.dimension_code = 'TIM' then
1431 -- find level id in msd dp calendars to see if it exists.
1432
1433 begin
1434
1435 open get_inv_hier_tim_prms(p_demand_plan_id, lv_level_id1);
1436 fetch get_inv_hier_tim_prms into x_meaning;
1437 close get_inv_hier_tim_prms;
1438
1439 if (x_meaning is not null) then
1440 display_message( rpad(param_rec.description, 62) ||
1441 rpad(x_meaning, 28),
1442 ERROR);
1443 display_message( rpad(' ',62) ||
1444 rpad('Not in DP Hierarchies.', 28),
1445 INFORMATION);
1446 end if;
1447
1448 exception when others
1449 then null;
1450 end;
1451
1452 end if;
1453 lv_level_id1 := null;
1454 x_meaning := null;
1455 x_is_dp_hier_level := null;
1456 x_parameter_name := null;
1457
1458 end loop;
1459
1460
1461 /* Additions. Check for multiple stream flag and whether the name is correct. */
1462 /* Sort through all parameters defined for this demand plan definition. */
1463 for param_rec in get_all_input_param (p_demand_plan_id)
1464 loop
1465 /* Bug #5464757: Error when ony one of the consituent dimension in collapsed dimension
1466 is not present in the parameter. */
1467 FOR COL_DIM IN COL_DIM_IN_PARA(param_rec.PARAMETER_TYPE_ID)
1468 loop
1469 display_message( rpad(param_rec.parameter_type, 31) ||
1470 rpad(nvl(param_rec.forecast_date_used, param_rec.parameter_name), 20) ||
1471 rpad('Collapse Dim: '|| COL_DIM.dim_code || ' is not in the parameter.' , 40),
1472 ERROR);
1473 end loop;
1474
1475 chk_ip_multiple(param_rec.parameter_type,
1476 param_rec.multiple_stream_flag,
1477 param_rec.parameter_name);
1478 chk_ip_allo_agg(param_rec.parameter_type,
1479 param_rec.parameter_name,
1480 param_rec.demand_plan_id,
1481 param_rec.cs_definition_id,
1482 param_rec.allo_agg_basis_stream_id);
1483
1484 chk_cal_for_bucket (param_rec.start_date,
1485 param_rec.demand_plan_id,
1486 rpad(param_rec.parameter_type,31) || rpad(param_rec.parameter_name, 31) || 'Start Date ');
1487
1488 chk_cal_for_bucket (param_rec.end_date,
1489 param_rec.demand_plan_id,
1490 rpad(param_rec.parameter_type,31) || rpad(param_rec.parameter_name, 31) || 'End Date ');
1491
1492 end loop;
1493
1494 if l_debug = 'Y' then
1495 debug_out( 'Exiting chk_input_param ' || to_char(sysdate, 'hh24:mi:ss'));
1496 end if;
1497
1498 End;
1499 --
1500 -- Procedure to validate scenarios
1501 --
1502 Procedure chk_scenarios (p_demand_plan_id in number,
1503 p_g_min_tim_lvl_id in number,
1504 p_m_min_tim_lvl_id in number,
1505 p_f_min_tim_lvl_id in number,
1506 p_c_min_tim_lvl_id in number) is
1507
1508 b_empty_scenario BOOLEAN := TRUE;
1509
1510 cursor c1(p_sc_id in number) is
1511 Select decode(output_period_type,
1512 9, 0,
1513 8, 1,
1514 7, 1,
1515 6, 1,
1516 5, 3,
1517 4, 3,
1518 3, 3,
1519 2, 2,
1520 1, 2,
1521 4)
1522 from msd_dp_scenarios md
1523 where md.scenario_id = p_sc_id;
1524
1525 cursor c2 is
1526 select count(1)
1527 from msd_dp_calendars
1528 where demand_plan_id = p_demand_plan_id;
1529
1530 cursor c3(p_cal_type in number) is
1531 select count(1)
1532 from msd_dp_calendars
1533 where demand_plan_id = p_demand_plan_id
1534 and calendar_type = p_cal_type;
1535
1536 cursor c4(p_lvl_id in number) is
1537 Select decode(p_lvl_id,
1538 9, 0,
1539 8, 1,
1540 7, 1,
1541 6, 1,
1542 5, 3,
1543 4, 3,
1544 3, 3,
1545 2, 2,
1546 1, 2,
1547 4)
1548 from dual;
1549
1550 x_cal_type number := 0;
1551 x_count number := 0;
1552 x_stream_defined number := 0;
1553 param_time_level_id number := 0;
1554 param_cal_type number := 0;
1555
1556 Begin
1557
1558 if l_debug = 'Y' then
1559 debug_out( 'Entering chk_scenarios ' || to_char(sysdate, 'hh24:mi:ss'));
1560 end if;
1561
1562 display_message('Validating scenarios' , SECTION);
1563 display_message(rpad('Scenario Name', 31) || rpad('Error Description', 60), HEADING);
1564 display_message(rpad('-', 30, '-') || ' ' || rpad('-', 60, '-'), HEADING);
1565 --
1566 /* Validate Scenarios */
1567 FOR scen_rec IN get_scen (p_demand_plan_id) LOOP
1568 /* When code reaches this line, it means there is at least 1 scenario
1569 associated with this Demand Plan. */
1570 IF (b_empty_scenario) THEN
1571 b_empty_scenario := FALSE;
1572 END IF;
1573
1574 IF (scen_rec.forecast_based_on is not null) THEN
1575 -- verify there is input parameter defined for the scenario
1576 l_a_date := null;
1577 l_b_date := null;
1578 open get_input_param(p_demand_plan_id, scen_rec.forecast_based_on, scen_rec.parameter_name, scen_rec.forecast_date_used);
1579
1580 fetch get_input_param into l_a_date, l_b_date;
1581 close get_input_param ;
1582 if l_a_date is null then
1583 -- there should be an associated input parameter
1584 display_message( rpad(scen_rec.scenario_name, 31) ||
1585 rpad('Input parameter not found for ', 60) , ERROR);
1586 else
1587
1588 -- scenario's history date range must be well within input parameter's history date range
1589 if l_a_date > scen_rec.history_start_date then
1590 -- scenario's history start date must be later than the associated input parameter
1591 display_message( rpad(scen_rec.scenario_name , 31) ||
1592 rpad('History start date must be >= input parameter''s start date', 60) , ERROR);
1593 end if;
1594 --
1595 if l_b_date is not null and scen_rec.history_end_date is not null then
1596 if l_b_date < scen_rec.history_end_date then
1597 -- scenario's history end date must be prior to the associated input parameter
1598 display_message( rpad(scen_rec.scenario_name, 31) ||
1599 rpad('History end date must be <= input parameter''s end date.', 60) , ERROR);
1600 end if;
1601 end if;
1602 --
1603 end if;
1604
1605 end if;
1606 --
1607 -- verify horizon date
1608 -- changed if statement.
1609 if ((scen_rec.history_end_date is not null) and
1610 (scen_rec.horizon_start_date <= scen_rec.history_end_date)) then
1611 -- horizon start date must be later than history_end_date
1612 display_message( rpad(scen_rec.scenario_name, 31) ||
1613 rpad('Horizon start date must be later than history end date.', 60) , ERROR);
1614 end if;
1615 --
1616 if nvl(scen_rec.publish_flag, 'N') = 'Y' then
1617 -- Verify it has required output levels
1618 l_cnt := 0;
1619
1620 if (l_dp_rec.use_org_specific_bom_flag = 'Y') then
1621 open get_output_levels_org(l_dp_rec.demand_plan_id, scen_rec.scenario_id) ;
1622 fetch get_output_levels_org into l_cnt;
1623 close get_output_levels_org;
1624 if l_cnt <> 2 then
1625 -- publishable scenario must have output levels....
1626 display_message( rpad(scen_rec.scenario_name, 31) ||
1627 rpad('Must have output levels (Item or Prod. family) and Org.', 60) , ERROR);
1628 end if;
1629 else
1630 open get_output_levels(l_dp_rec.demand_plan_id, scen_rec.scenario_id) ;
1631 fetch get_output_levels into l_cnt;
1632 close get_output_levels;
1633 if l_cnt <> 1 then
1634 -- publishable scenario must have output levels....
1635 display_message( rpad(scen_rec.scenario_name, 31) ||
1636 rpad('Must have output levels (Item or Prod. family) and Org.', 60) , ERROR);
1637 end if;
1638 end if;
1639
1640 -- verify forecast level
1641 if scen_rec.output_period_type not in (1,2,9) then
1642 -- must be at day level
1643 display_message( rpad(scen_rec.scenario_name, 31) ||
1644 rpad('Only Man. Period, Man. Week, or Day can be published.', 60) , ERROR);
1645 end if;
1646
1647 end if;
1648 --
1649 -- save max horizon date
1650 --
1651 if l_dp_max_date is null or l_dp_max_date < scen_rec.horizon_end_date then
1652 l_dp_max_date := scen_rec.horizon_end_date;
1653 end if;
1654 --
1655 -- save min. history date
1656 --
1657 if ((scen_rec.history_start_date is not null) and
1658 (l_dp_min_date is null or l_dp_min_date < scen_rec.history_start_date)) then
1659 l_dp_min_date := scen_rec.history_start_date;
1660 end if;
1661
1662
1663 if (scen_rec.history_start_date is not null) then
1664 chk_cal_for_bucket (scen_rec.history_start_date,
1665 p_demand_plan_id,
1666 rpad(scen_rec.scenario_name, 31) || 'History Start Date ');
1667 end if;
1668
1669 if (scen_rec.history_end_date is not null) then
1670 chk_cal_for_bucket (scen_rec.history_end_date,
1671 p_demand_plan_id,
1672 rpad(scen_rec.scenario_name, 31) || 'History End Date ');
1673 end if;
1674
1675 if (scen_rec.horizon_start_date is not null) then
1676 chk_cal_for_bucket (scen_rec.horizon_start_date,
1677 p_demand_plan_id,
1678 rpad(scen_rec.scenario_name, 31) || 'Horizon Start Date ');
1679 end if;
1680
1681 if (scen_rec.horizon_end_date is not null) then
1682 chk_cal_for_bucket (scen_rec.horizon_end_date,
1683 p_demand_plan_id,
1684 rpad(scen_rec.scenario_name, 31) || 'Horizon End Date ');
1685 end if;
1686
1687 /** Added for Multiple Time Hierarchies **/
1688
1689 /** Check if Calendar is attached for Output Period Type **/
1690
1691 if (scen_rec.output_period_type is not null) then
1692 open c1(scen_rec.scenario_id);
1693 fetch c1 into x_cal_type;
1694 close c1;
1695
1696 if (x_cal_type = 0) then
1697 open c2;
1698 fetch c2 into x_count;
1699 close c2;
1700 else
1701 open c3(x_cal_type);
1702 fetch c3 into x_count;
1703 close c3;
1704 end if;
1705
1706 if (x_count = 0) then
1707 display_message( rpad(scen_rec.scenario_name, 31) ||
1708 rpad('Calendar for Output Period type not attached to plan.', 60) , ERROR);
1709 end if;
1710 end if;
1711
1712
1713 /** Treat Day as a separate case **/
1714 if (scen_rec.output_period_type = 9) then
1715 if (p_g_min_tim_lvl_id is null) and
1716 (p_f_min_tim_lvl_id is null) and
1717 (p_m_min_tim_lvl_id is null) and
1718 (p_c_min_tim_lvl_id is null) then
1719 null;
1720 elsif((p_g_min_tim_lvl_id = 9) or
1721 (p_f_min_tim_lvl_id = 9) or
1722 (p_m_min_tim_lvl_id = 9) or
1723 (p_c_min_tim_lvl_id = 9)) then
1724 null;
1725 else
1726 display_message( rpad(scen_rec.scenario_name, 31) ||
1727 rpad('Output period type is less than minimum time level', 60) , ERROR);
1728 end if;
1729 elsif scen_rec.output_period_type is not null then
1730 if ((scen_rec.output_period_type between 6 and 8) and ((p_g_min_tim_lvl_id > scen_rec.output_period_type) and (p_g_min_tim_lvl_id <> 9)))
1731 or
1732 ((scen_rec.output_period_type between 3 and 5) and ((p_f_min_tim_lvl_id > scen_rec.output_period_type) and (p_f_min_tim_lvl_id <> 9)))
1733 or
1734 ((scen_rec.output_period_type between 1 and 2) and ((p_m_min_tim_lvl_id > scen_rec.output_period_type) and (p_m_min_tim_lvl_id <> 9)))
1735 or
1736 ((scen_rec.output_period_type between 10 and 13) and ((p_c_min_tim_lvl_id > scen_rec.output_period_type) and (p_c_min_tim_lvl_id <> 9))) then
1737 display_message( rpad(scen_rec.scenario_name, 31) ||
1738 rpad('Output period type is less than minimum time level', 60) , ERROR);
1739
1740 end if;
1741
1742
1743 end if;
1744
1745 /** Check if Output Period Type is in same calendar as Input Parameter **/
1746 if ((scen_rec.forecast_based_on is not null)
1747 and ((scen_rec.allocation_allowed_flag = 'N')
1748 or
1749 (scen_rec.allocation_allowed_flag='Y' and scen_rec.lowest_level_flag=x_stream_defined))) then
1750
1751 if (scen_rec.collect_level_id is null) then
1752
1753 /** If not, then check in fact tables for value **/
1754 param_time_level_id := get_level_id( scen_rec.view_name,
1755 get_level_column_name('TIM',scen_rec.cs_definition_id),
1756 scen_rec.date_planning_view_clmn,
1757 scen_rec.prm_start_date,
1758 scen_rec.prm_end_date,
1759 scen_rec.system_flag,
1760 scen_rec.multiple_stream_flag,
1761 scen_rec.parameter_name,
1762 scen_rec.cs_definition_id,
1763 scen_rec.collect_level_id,
1764 scen_rec.input_demand_plan_id,
1765 scen_rec.input_scenario_id);
1766
1767 else
1768
1769 param_time_level_id := scen_rec.collect_level_id;
1770
1771 end if;
1772
1773 open c4(param_time_level_id);
1774 fetch c4 into param_cal_type;
1775 close c4;
1776
1777 if ((param_cal_type <> 0) and (x_cal_type <> 0) and (param_cal_type <> x_cal_type)) then
1778 display_message( rpad(scen_rec.scenario_name, 31) ||
1779 rpad('Choose an Output Period Type in the same type ', 60) , WARNING);
1780 display_message( rpad(' ', 31) ||
1781 rpad('of calendar as the Input Parameter''s Time Level.', 60) , INFORMATION);
1782
1783 elsif ((param_time_level_id <> 9)
1784 and (scen_rec.output_period_type <> 9)
1785 and (param_time_level_id > scen_rec.output_period_type)) then
1786
1787 display_message( rpad(scen_rec.scenario_name, 31) ||
1788 rpad('Choose an Output Period Type at or above ', 60) , WARNING);
1789 display_message( rpad(' ', 31) ||
1790 rpad('the Input Parameter''s Time Level.', 60) , INFORMATION);
1791
1792 elsif ((param_time_level_id <> 9)
1793 and (scen_rec.output_period_type = 9)) then
1794 display_message( rpad(scen_rec.scenario_name, 31) ||
1795 rpad('Choose an Output Period Type at or above ', 60) , WARNING);
1796 display_message( rpad(' ', 31) ||
1797 rpad('the Input Parameter''s Time Level.', 60) , INFORMATION);
1798
1799 end if;
1800 end if;
1801
1802 /* End Output Period Type Check */
1803
1804 x_count := 0;
1805 x_cal_type := 0;
1806 param_time_level_id := null;
1807 param_cal_type := 0;
1808
1809 /** End additions for Multiple Time Hierarchies **/
1810
1811 END LOOP; -- End of FOR scen_rec loop
1812
1813 -- If there is no scenario specified for the D.P. then generate warning message.
1814 IF (b_empty_scenario) THEN
1815 display_message(rpad(l_dp_rec.demand_plan_name,30) || ' ' ||
1816 rpad('No scenario is specified for this Demand Plan',60), ERROR);
1817 END IF;
1818
1819 if l_debug = 'Y' then
1820 debug_out( 'Exiting chk_scenarios ' || to_char(sysdate, 'hh24:mi:ss'));
1821 end if;
1822
1823 End;
1824 --
1825 -- Validate output levels
1826 --
1827 Procedure chk_output_levels ( p_demand_plan_id in number) is
1828 Begin
1829
1830 if l_debug = 'Y' then
1831 debug_out( 'Entering chk_otuput_levels ' || to_char(sysdate, 'hh24:mi:ss'));
1832 end if;
1833
1834 display_message( 'Validating Output Levels' , SECTION);
1835 display_message( rpad('Scenario Name', 30) || ' ' || rpad('Level Name', 20) || ' ' ||
1836 rpad('Dimension', 10) || 'Error Description' , HEADING);
1837 display_message( rpad('-', 30, '-') || ' ' || rpad('-', 20, '-') || ' ' ||
1838 rpad('-', 9, '-') || ' ' || rpad('-' ,30, '-'), HEADING);
1839 --
1840 for c1_rec in get_inv_output_levels(p_demand_plan_id) loop
1841 --
1842 -- error in output levels
1843 display_message( rpad(c1_rec.scenario_name, 30) || ' ' ||
1844 rpad(c1_rec.level_name, 20) || ' ' || rpad(c1_rec.dimension_code, 10)
1845 || 'has no associated hierarchy.', ERROR);
1846 end loop;
1847
1848 if l_debug = 'Y' then
1849 debug_out( 'Entering chk_output_levels ' || to_char(sysdate, 'hh24:mi:ss'));
1850 end if;
1851
1852 End;
1853
1854 Procedure chk_dup_dim_output_levels ( p_demand_plan_id in number) is
1855 Begin
1856
1857 if l_debug = 'Y' then
1858 debug_out( 'Entering chk_dup_dim_otuput_levels ' || to_char(sysdate, 'hh24:mi:ss'));
1859 end if;
1860
1861 display_message( 'Checking Duplicate Dimensions in Output Levels' , SECTION);
1862 display_message( rpad('Scenario Name', 30) || ' ' ||rpad('Dimension', 10) || 'Error Description' , HEADING);
1863 display_message( rpad('-', 30, '-') || ' ' || ' ' ||rpad('-', 9, '-') || ' ' || rpad('-' ,30, '-'), HEADING);
1864 --
1865 for c1_rec in get_dup_dim_output_levels(p_demand_plan_id) loop
1866 --
1867 -- error in output levels
1868 display_message( rpad(c1_rec.scenario_name, 30) || ' ' ||
1869 rpad(c1_rec.dimension_code, 10)
1870 || 'has more than one levels selected.', ERROR);
1871 end loop;
1872
1873 if l_debug = 'Y' then
1874 debug_out( 'Exiting chk_dup_dim_output_levels ' || to_char(sysdate, 'hh24:mi:ss'));
1875 end if;
1876
1877 End;
1878 --
1879 -- Validate Time Data
1880 --
1881 Procedure chk_time_data (p_demand_plan_id in number) is
1882 --
1883 cursor get_input_date (p_demand_plan_id in number) is
1884 select
1885 min(start_date), max(end_date)
1886 from
1887 msd_dp_parameters_cs_v
1888 where
1889 demand_plan_id = p_demand_plan_id;
1890 --
1891 l_min_1 date;
1892 l_max_1 date;
1893 --
1894 Begin
1895
1896 if l_debug = 'Y' then
1897 debug_out( 'Entering chk_time_data ' || to_char(sysdate, 'hh24:mi:ss'));
1898 end if;
1899
1900 display_message( 'Validating Calendar properties' , SECTION);
1901 display_message( 'Error Description' , HEADING);
1902 display_message( rpad('-', 80, '-') , HEADING);
1903 /* verify calendar */
1904
1905 /* Removed for Multiple Time Hierarchies.
1906 *
1907 * if l_dp_rec.calendar_type not in (
1908 * MSD_COMMON_UTILITIES.GREGORIAN_CALENDAR,
1909 * MSD_COMMON_UTILITIES.MANUFACTURING_CALENDAR,
1910 * MSD_COMMON_UTILITIES.FISCAL_CALENDAR )
1911 *
1912 * then
1913 * -- invalid calendar
1914 * display_message( 'Calendar is not a valid calendar' , ERROR);
1915 * else
1916 */
1917
1918 --
1919 -- Get min and max date from input parameters
1920 --
1921 open get_input_date (p_demand_plan_id);
1922 fetch get_input_date into l_min_1, l_max_1;
1923 close get_input_date;
1924 --
1925 if ((l_min_1 < l_dp_min_date) or (l_dp_min_date is null)) then
1926 l_dp_min_date := l_min_1;
1927 end if;
1928 --
1929 if ((l_max_1 > l_dp_max_date) or (l_dp_max_date is null)) then
1930 l_dp_max_date := l_max_1;
1931 end if;
1932 --
1933 -- Validate time dimension
1934 --
1935 for cal_rec in get_dp_cal(p_demand_plan_id) loop
1936
1937 l_min_date := null;
1938 l_max_date := null;
1939
1940 for c1_rec in get_tim(cal_rec.calendar_type, cal_rec.calendar_code,l_dp_min_date,l_dp_max_date) loop
1941
1942 l_min_date := c1_rec.min_date ;
1943 l_max_date := c1_rec.max_date ;
1944
1945 if l_min_date is null or l_max_date is null then
1946 -- error time dimesnion does not have values
1947
1948 display_message('Time data not found in ' || cal_rec.op_cal_code, ERROR);
1949 end if;
1950 --
1951 if l_min_date > l_dp_min_date then
1952 -- time dimension does not have data for history_start_date
1953 display_message( cal_rec.op_cal_code || ' does not have data for history start date ' ||
1954 to_char(l_dp_min_date, 'DD-Mon-YYYY'), ERROR);
1955 end if;
1956 --
1957 if l_max_date < l_dp_max_date then
1958 -- time dimension does not have data for history_start_date
1959 display_message( cal_rec.op_cal_code || ' does not have data for the last horizon date ' ||
1960 to_char(l_dp_max_date, 'DD-Mon-YYYY'), ERROR);
1961 end if;
1962 --
1963 end loop;
1964 end loop;
1965
1966 if l_debug = 'Y' then
1967 debug_out( 'Exiting chk_time_data ' || to_char(sysdate, 'hh24:mi:ss'));
1968 end if;
1969
1970 End;
1971 --
1972 -- check for UOM conversion data
1973 --
1974 Procedure chk_uom_data (p_demand_plan_id in number) is
1975 Begin
1976
1977 if l_debug = 'Y' then
1978 debug_out( 'Entering chk_uom_data ' || to_char(sysdate, 'hh24:mi:ss'));
1979 end if;
1980
1981 display_message( 'Validating UOM properties' , SECTION);
1982 IF (l_dp_rec.base_uom IS NULL) THEN
1983 display_message( 'Base UOM has not been defined.', ERROR);
1984 ELSE
1985 -- UOM Conversion data
1986 open uom_conv (l_dp_rec.base_uom);
1987 fetch uom_conv into l_cnt;
1988 close uom_conv;
1989
1990 if l_cnt = 0 then
1991 -- UOM conversion not collected
1992 display_message( 'UOM conversion rate not defined.', ERROR);
1993 end if;
1994 END IF;
1995
1996 if l_debug = 'Y' then
1997 debug_out( 'Exiting chk_uom_data ' || to_char(sysdate, 'hh24:mi:ss'));
1998 end if;
1999
2000 End;
2001 --
2002 -- Check for currency conversion data
2003 --
2004 Procedure chk_curr_data (p_demand_plan_id in number) is
2005 Begin
2006
2007 if l_debug = 'Y' then
2008 debug_out( 'Entering chk_curr_data ' || to_char(sysdate, 'hh24:mi:ss'));
2009 end if;
2010
2011 -- currency conversion data
2012 if fnd_profile.value('MSD_CURRENCY_CODE') is not null then
2013 open curr_conv (fnd_profile.value('MSD_CURRENCY_CODE'), l_dp_min_date , l_dp_max_date);
2014 fetch curr_conv into l_cnt;
2015 close curr_conv;
2016 --
2017 if l_cnt = 0 then
2018 -- no currency conversion data
2019 display_message( 'Currency conversion data not found for ' || fnd_profile.value('MSD_CURRENCY_CODE'), WARNING);
2020 end if;
2021 end if;
2022 --
2023
2024 if l_debug = 'Y' then
2025 debug_out( 'Exiting chk_curr_data ' || to_char(sysdate, 'hh24:mi:ss'));
2026 end if;
2027
2028 End;
2029 --
2030
2031 /* For each parameter defined within the demand plan definition check that
2032 * fact data exists in the Fact Table for the Parameter Name, Forecast Date,
2033 * and Data Range specified.
2034 */
2035
2036 PROCEDURE chk_fact_data (p_demand_plan_id IN NUMBER) IS
2037
2038 /* Cursor used to check the Fact Data */
2039 Type FACT_DATA_TYPE is REF CURSOR;
2040 --
2041 c_fact_data FACT_DATA_TYPE;
2042 --
2043 /* Fact Data Table Name */
2044 l_source VARCHAR2(30);
2045 l_ps_view_name VARCHAR2(30);
2046
2047 /* Date Column Name in Fact Table */
2048 l_date_col VARCHAR2(30);
2049
2050 /* Start Date of Parameter History */
2051 l_start_date VARCHAR2(200);
2052
2053 /* End Date of Parameter History */
2054 l_end_date VARCHAR2(200);
2055
2056 /* Designator Column Name in Fact Table. */
2057 l_name VARCHAR2(300);
2058
2059 /* Dynamic SQL string used to check fact data. */
2060 l_stmt VARCHAR2(2000);
2061
2062 /* Number of rows fetched from Fact Data during check. */
2063 l_count NUMBER;
2064
2065 /* String printed to output to identify each Parameter being tested. */
2066 l_output VARCHAR2(2000);
2067
2068 /* Index of character being printed for error messages. */
2069 i NUMBER;
2070
2071 /* Find out whether stream is local or not. Used for Checking
2072 * which rows are deleted...for Net Change
2073 */
2074 cursor get_stream_type(p_cs_definition_id number) is
2075 select cs_type
2076 from msd_cs_definitions
2077 where cs_definition_id = p_cs_definition_id;
2078
2079 /* Stream type for Custom Stream */
2080 x_stream_type varchar2(30);
2081
2082 /* Find the collect level id for time */
2083 cursor get_col_lvl_id(p_cs_definition_id in number, p_dim_code in varchar2) is
2084 select collect_level_id
2085 from msd_cs_defn_dim_dtls
2086 where cs_definition_id = p_cs_definition_id
2087 and dimension_code = p_dim_code;
2088
2089 /* Translate Time Level Id into Time Column in MSD_TIME */
2090 cursor tim_lvl_id_2_clmn (p_lvl_id in number) is
2091 select decode(p_lvl_id,
2092 '1', 'WEEK_END_DATE ',
2093 '2', 'MONTH_END_DATE ',
2094 '3', 'MONTH_END_DATE ',
2095 '4', 'QUARTER_END_DATE ',
2096 '5', 'YEAR_END_DATE ',
2097 '6', 'MONTH_END_DATE ',
2098 '7', 'QUARTER_END_DATE ',
2099 '8', 'YEAR_END_DATE ',
2100 '9', 'DAY',
2101 '10', 'WEEK_END_DATE ',
2102 '11', 'MONTH_END_DATE ',
2103 '12', 'QUARTER_END_DATE ',
2104 '13', 'YEAR_END_DATE ',
2105 'DAY ')
2106 from dual;
2107
2108 /* Map the Time Level Id to the calendar type */
2109 /* If day then any calendar is fine */
2110 /* This is handled with the last case. we return column name */
2111
2112 cursor tim_lvl_id_2_cal_type (p_time_level_id in number) is
2113 select decode (p_time_level_id,
2114 1, '2',
2115 2, '2',
2116 3, '3',
2117 4, '3',
2118 5, '3',
2119 6, '1',
2120 7, '1',
2121 8, '1',
2122 10, '4',
2123 11, '4',
2124 12, '4',
2125 13, '4',
2126 'tim.calendar_type')
2127 from dual;
2128
2129 time_level_id number;
2130 time_level_column varchar2(100);
2131 time_cal_type varchar2(100);
2132
2133 BEGIN
2134
2135 if l_debug = 'Y' then
2136 debug_out( 'Entering chk_fact_data ' || to_char(sysdate, 'hh24:mi:ss'));
2137 end if;
2138
2139
2140 /* Titles for each Parameter in the Log File */
2141 display_message( 'Validating Fact Data', SECTION);
2142 display_message( rpad('Parameter Type', 24) || rpad('Forecast Date', 20) ||
2143 'Start Date End Date Error Description', HEADING);
2144 display_message( rpad('-', 23, '-') || ' ' || rpad('-', 19, '-') || ' ' ||
2145 '---------- --------- -------------------------', HEADING);
2146 --
2147 /* Loop through each parameter defined in the Demand Plan */
2148 FOR c_input_rec IN get_all_input_param (p_demand_plan_id) LOOP
2149 --
2150 /* Re-initializing variables */
2151 l_output := NULL;
2152 l_source := NULL;
2153 l_ps_view_name := NULL;
2154 l_name := NULL;
2155 l_date_col := NULL;
2156
2157 /* Retrieve Value for Fact View and Desginator Column Name */
2158 open get_ps_view_name (c_input_rec.cs_definition_id, p_demand_plan_id);
2159 fetch get_ps_view_name into l_source, l_ps_view_name;
2160 close get_ps_view_name;
2161
2162 l_name := get_desig_clmn_name(c_input_rec.cs_definition_id);
2163
2164 /* Retrieve Value for Date Column Name */
2165 l_date_col := c_input_rec.date_planning_view_clmn;
2166
2167 /* Prepare String Output for Log File */
2168 /* Parameter Type, Date Type, Start Date, End Date */
2169 /* The Desginator is displayed later in a following line. */
2170 l_output := RPAD(c_input_rec.parameter_type, 24) ||
2171 RPAD(nvl(nvl(c_input_rec.forecast_date_used, l_date_col),' '), 20) ||
2172 TO_CHAR(c_input_rec.start_date, 'dd-mm-yyyy') || ' ' ||
2173 TO_CHAR(c_input_rec.end_date, 'dd-mm-yyyy');
2174
2175 /* Check if the user specified a view in the Parameters Tab to Override */
2176 IF c_input_rec.view_name IS NOT NULL THEN
2177 l_source := c_input_rec.view_name;
2178 l_ps_view_name := c_input_rec.view_name;
2179 END IF;
2180
2181 /******************************************************************************
2182 * Only check for fact data if the Fact Table, Date Column, and Designator
2183 * Column (if necesary) exists.
2184 *
2185 * (1) The Planning Server View must be NOT NULL.
2186 * (2) The Planning Server Date Column must be NOT NULL.
2187 * (3) If the Parameter has multiple streams then the Planning Server Designator
2188 * Column must be NOT NULL.
2189 * (4) If any one of the above cases fail and the Parameter is of type 'Input Scenario',
2190 * then continue with the check.
2191 *
2192 * Structure of the Dynamic SQL String is as follows for Streams not using
2193 * Custom Stream Fact Tables:
2194 *
2195 * SELECT count(1)
2196 * FROM "User Defined Planning Server View Name"
2197 * WHERE "User Defined Server View Date Column" IS BETWEEN "Start Date" AND "End Date"
2198 *
2199 *
2200 * Structure of the Dynamic SQL String is as follows for Streams using Custom Stream Fact Tables:
2201 *
2202 * SELECT count(1)
2203 * FROM "Custom Stream Planning Server View Name"
2204 * WHERE "Custom Stream Planning Server View Date Column" IS BETWEEN "Start Date" AND "End Date"
2205 * AND Custom_Stream_Definition = "Parameter Custom Stream Definition"
2206 *
2207 * Structure of the Dynamic SQL String is as follows for Input Scenario Streams
2208 *
2209 * SELECT count(1)
2210 * FROM msd_dp_scenario_entries
2211 * WHERE tim_lvl_val_to IS BETWEEN "Start Date" AND "End Date"
2212 * AND scenario_id = input_scenario_id
2213 * AND ...
2214 **************************************************************************************/
2215
2216 IF ( ((l_source IS NOT NULL) AND
2217 (l_date_col IS NOT NULL) AND
2218 (
2219 (nvl(c_input_rec.multiple_stream_flag,'N') = 'Y' AND l_name IS NOT NULL)
2220 OR
2221 (nvl(c_input_rec.multiple_stream_flag,'N') <> 'Y')
2222 )
2223 )
2224 OR
2225 (c_input_rec.parameter_type_id in ('MSD_INPUT_SCENARIO'))) THEN
2226 BEGIN
2227
2228 /* History Start Date and End Date */
2229 l_start_date := 'TO_DATE(''' || TO_CHAR(c_input_rec.start_date, 'ddmmyyyy') || ''', ''ddmmyyyy'')';
2230 l_end_date := 'TO_DATE(''' || TO_CHAR(c_input_rec.end_date, 'ddmmyyyy') || ''', ''ddmmyyyy'')';
2231
2232 /* Select, FROM portions of Dynamic Statement */
2233 l_stmt := 'SELECT COUNT(*) FROM ' || l_source || ' src WHERE ';
2234
2235 /* For Streams other than Input Scenario use Generic Where clause */
2236 IF (c_input_rec.parameter_type_id NOT IN ('MSD_INPUT_SCENARIO')) THEN
2237 l_stmt := l_stmt || 'src.' || l_date_col ||
2238 ' BETWEEN ' || l_start_date || ' AND ' || l_end_date || ' AND ROWNUM < 5';
2239
2240 /* For multiple streams check designator */
2241 IF (nvl(c_input_rec.multiple_stream_flag,'N') = 'Y') THEN
2242 l_stmt := l_stmt || ' AND src.' || l_name || ' = ''' || replace(c_input_rec.parameter_name, '''', '''''') || '''';
2243 END IF;
2244
2245 /* For Streams Defined using Custom Stream Fact Tables */
2246 IF (c_input_rec.system_flag = 'C') then
2247 l_stmt := l_stmt || ' AND src.cs_definition_id = ' || c_input_rec.cs_definition_id;
2248 END IF;
2249
2250 /* Add demand plan id if included for striping. */
2251
2252 if ((l_dp_rec.stripe_stream_name is not null) or (l_dp_rec.stripe_sr_level_pk is not null)) then
2253 if ((c_input_rec.system_flag = 'I') OR (c_input_rec.cs_type in ('SOURCE','STAGE'))) then
2254 l_stmt := l_stmt || ' AND src.demand_plan_id = ' || p_demand_plan_id;
2255 end if;
2256 end if;
2257
2258 /* Included for Net Change */
2259 open get_stream_type(c_input_rec.cs_definition_id);
2260 fetch get_stream_type into x_stream_type;
2261 close get_stream_type;
2262
2263 /* Find Time level id column */
2264 open get_col_lvl_id(c_input_rec.cs_definition_id, 'TIM');
2265 fetch get_col_lvl_id into time_level_id;
2266 close get_col_lvl_id;
2267
2268 if (time_level_id is null) then
2269
2270 time_level_id := get_level_id( l_ps_view_name,
2271 get_level_column_name('TIM',c_input_rec.cs_definition_id),
2272 l_date_col,
2273 c_input_rec.start_date,
2274 c_input_rec.end_date,
2275 c_input_rec.system_flag,
2276 c_input_rec.multiple_stream_flag,
2277 c_input_rec.parameter_name,
2278 c_input_rec.cs_definition_id,
2279 9,
2280 -999,
2281 -999);
2282
2283 end if;
2284
2285 /* Translate TIme level id into Time Column */
2286 open tim_lvl_id_2_clmn (time_level_id);
2287 fetch tim_lvl_id_2_clmn into time_level_column;
2288 close tim_lvl_id_2_clmn;
2289
2290 /* Translate Time Level Id to Calendar Type */
2291 open tim_lvl_id_2_cal_type(time_level_id);
2292 fetch tim_lvl_id_2_cal_type into time_cal_type;
2293 close tim_lvl_id_2_cal_type;
2294
2295 If (x_stream_type <> 'LOCAL') then
2296 l_stmt := l_stmt || ' AND src.action_code <> ''D''';
2297 End IF;
2298
2299 /* BUG 2419958 : Add clause to check that data exists in msd_time_v too.
2300 * Retrieve the end_date and level_id to find out a row in time and fact that match.
2301 */
2302 l_stmt := l_stmt || ' AND EXISTS (select 1 from msd_time tim where src.';
2303 l_stmt := l_stmt || l_date_col || ' = tim.' || nvl(time_level_column, 'DAY');
2304
2305 l_stmt := l_stmt || ' and (tim.calendar_type, tim.calendar_code) in (select mdc.calendar_type, mdc.calendar_code from msd_dp_calendars mdc where mdc.demand_plan_id = ' || p_demand_plan_id || ')';
2306 l_stmt := l_stmt || ' and ' || time_cal_type || ' = tim.calendar_type)';
2307
2308
2309 ELSE
2310 /* Special case for Input Scenario.
2311 * Checks for the Following :
2312 * (1) Scenario entry exists for Demand Plan Id, Scenario Id, Revision specified.
2313 * (2) Range of Date in One Entry exists for Parameter's Date Range
2314 */
2315
2316 /* Checks for Input Scenario */
2317 l_stmt := l_stmt || ' src.time_lvl_val_to BETWEEN ' || l_start_date || ' AND ' || l_end_date;
2318 l_stmt := l_stmt || ' AND src.scenario_id = ' || NVL(c_input_rec.input_scenario_id, -999);
2319 l_stmt := l_stmt || ' AND src.demand_plan_id = ' || NVL(c_input_rec.input_demand_plan_id, -999);
2320 l_stmt := l_stmt || ' AND src.revision = ''' || NVL(c_input_rec.revision, -999) || '''';
2321
2322 -- Performance enhancement if plan is LOB enabled.
2323 if ((l_dp_rec.stripe_stream_name is not null)
2324 or
2325 (l_dp_rec.stripe_sr_level_pk is not null)) then
2326
2327 l_stmt := l_stmt || ' AND src.stripe_demand_plan_id = ' || p_demand_plan_id;
2328 end if;
2329
2330 l_stmt := l_stmt || ' AND ROWNUM < 2';
2331
2332 END IF;
2333
2334 /* Print the debug statement if debug is on */
2335 if l_debug = 'Y' then
2336 debug_out( l_stmt);
2337 end if;
2338
2339 OPEN c_fact_data FOR l_stmt;
2340 FETCH c_fact_data INTO l_count;
2341 CLOSE c_fact_data;
2342 IF l_count = 0 THEN
2343 display_message(l_output || ' fact data does not exist' , WARNING);
2344 IF nvl(c_input_rec.multiple_stream_flag,'N') = 'Y' THEN
2345 display_message(' (' || replace(c_input_rec.parameter_name, '''', ''''''), INFORMATION);
2346 END IF;
2347 END IF;
2348
2349 EXCEPTION
2350 WHEN OTHERS THEN
2351 IF c_fact_data%ISOPEN THEN
2352 CLOSE c_fact_data;
2353 END IF;
2354 display_message(l_output || ' fact data check failed.', ERROR);
2355 --
2356 debug_out( substr(sqlerrm, 1 , 90) );
2357 debug_out( substr(sqlerrm, 91, 90) );
2358
2359 i := 1;
2360 while i<= length(l_stmt) loop
2361 debug_out( substr(l_stmt, i, 90) );
2362 i := i+90;
2363 end loop;
2364 --
2365 END;
2366 ELSE
2367 display_message(l_output || ' fact data check not done', WARNING);
2368 display_message('Source View Name : ' || nvl(l_source, 'Missing'), INFORMATION);
2369 display_message('Date Column Name : ' || nvl(l_date_col, 'Missing'), INFORMATION);
2370 display_message('Designator Column Name : ' || l_name, INFORMATION);
2371 display_message('Multiple Stream Flag : ' || nvl(c_input_rec.multiple_stream_flag,'N'), INFORMATION);
2372 END IF;
2373 END LOOP;
2374
2375 if l_debug = 'Y' then
2376 debug_out( 'Exiting chk_fact_data ' || to_char(sysdate, 'hh24:mi:ss'));
2377 end if;
2378
2379 END chk_fact_data;
2380 --
2381 Procedure update_plan(p_demand_plan_id in number, p_ret_code in number) is
2382 Begin
2383 --
2384
2385 if l_debug = 'Y' then
2386 debug_out( 'Entering update_plan ' || to_char(sysdate, 'hh24:mi:ss'));
2387 end if;
2388
2389 if nvl(p_ret_code, '1') <> '0' and nvl(p_ret_code, '2') <> '1' then
2390 update msd_demand_plans
2391 set valid_flag = '1'
2392 where demand_plan_id = p_demand_plan_id;
2393 else
2394 --
2395 update msd_demand_plans
2396 set valid_flag = '0'
2397 where demand_plan_id = p_demand_plan_id;
2398 end if;
2399 --
2400
2401 if l_debug = 'Y' then
2402 debug_out( 'Exiting update_plan ' || to_char(sysdate, 'hh24:mi:ss'));
2403 end if;
2404
2405 End;
2406 --
2407 Procedure chk_ip_multiple(p_parameter_type in varchar2, p_multiple_flag in varchar2, p_parameter_name in varchar2) is
2408 Begin
2409
2410 if l_debug = 'Y' then
2411 debug_out( 'Entering chk_ip_multiple ' || to_char(sysdate, 'hh24:mi:ss'));
2412 end if;
2413
2414 if ((p_multiple_flag = 'Y') and (p_parameter_name is null)) then
2415 /* Display error */
2416 display_message( rpad(p_parameter_type, 31) ||
2417 rpad(nvl(p_parameter_name,' '), 30) ||
2418 rpad('Name required with multiple stream ', 30),
2419 ERROR);
2420 elsif ((p_multiple_flag = 'N') and (p_parameter_name is not null)) then
2421 /* Display error */
2422 display_message( rpad(p_parameter_type, 31) ||
2423 rpad(p_parameter_name, 30) ||
2424 rpad('Name undefined without multiple stream ', 30),
2425 ERROR);
2426 elsif (p_parameter_name is not null) then
2427 null;
2428 end if;
2429
2430 if l_debug = 'Y' then
2431 debug_out( 'Exiting chk_ip_multiple ' || to_char(sysdate, 'hh24:mi:ss'));
2432 end if;
2433
2434 End;
2435 --
2436
2437
2438 Procedure chk_ip_allo_agg(p_parameter_type in varchar2, p_parameter_name in varchar2, p_demand_plan_id in number, p_cs_def_id in number, p_stream_id in number) is
2439 l_token number;
2440 Begin
2441
2442 if l_debug = 'Y' then
2443 debug_out( 'Entering chk_ip_allo_agg ' || to_char(sysdate, 'hh24:mi:ss'));
2444 end if;
2445
2446 /* The number of columns that can use Allo/Agg */
2447 open param_cs_wgt(p_cs_def_id);
2448 fetch param_cs_wgt into l_token;
2449 close param_cs_wgt;
2450
2451 /* The Allo/Agg stream is not defined and is not Usable */
2452 if ((p_stream_id is null) and (l_token = 0)) then
2453 /* Nothing defined, nothing usable, nothing to check. */
2454 RETURN;
2455 /* The Allo/Agg stream is defined but is not Usable */
2456 elsif ((p_stream_id is not null) and (l_token = 0)) then
2457 /* The user cannot see unusable values. Don't show that this data exists. */
2458 RETURN;
2459 /*
2460 * display_message( rpad(p_parameter_type, 31) ||
2461 * rpad(p_parameter_name, 30) ||
2462 * rpad('Allocation/Aggregation Stream not needed.', 50),
2463 * WARNING);
2464 */
2465 /* The Allo/Agg Stream is not defined but is Usable */
2466 elsif ((p_stream_id is null) and (l_token > 0)) then
2467 display_message( rpad(p_parameter_type, 31) ||
2468 rpad(p_parameter_name, 30) ||
2469 rpad('Allocation/Aggregation Stream should be defined.', 50),
2470 ERROR);
2471 /* The Allo/Agg Stream is defined and is Usable */
2472 else
2473 /* check if parameter is valid. */
2474 open get_one_param(p_demand_plan_id, p_stream_id);
2475 fetch get_one_param into l_token;
2476 close get_one_param;
2477 if (l_token = 0) then
2478 /* Display error that the chosen allo_agg_basis_stream_name is invalid. */
2479 display_message( rpad(p_parameter_type, 31) ||
2480 rpad(p_parameter_name, 30) ||
2481 rpad('Allocation Aggregation Stream is not Defined as a Parameter', 65),
2482 ERROR);
2483 end if;
2484 end if;
2485
2486 if l_debug = 'Y' then
2487 debug_out( 'Exiting chk_ip_allo_agg ' || to_char(sysdate, 'hh24:mi:ss'));
2488 end if;
2489
2490 End;
2491
2492 Procedure chk_scen_events( p_demand_plan_id in number) is
2493 cursor c is
2494 select mev.event_name, mev.event_id, mep.product_lvl_id, mep.product_lvl_name, mep.product_lvl_val
2495 from msd_event_products_v mep, msd_events_v mev
2496 where
2497 mep.event_id in ((select mdse.event_id
2498 from msd_dp_scenario_events mdse, msd_dp_scenarios b
2499 where mdse.demand_plan_id = p_demand_plan_id
2500 and mdse.scenario_id = b.scenario_id
2501 and b.enable_flag = 'Y')
2502 union
2503 (select mde.event_id
2504 from msd_dp_events_v mde
2505 where mde.demand_plan_id = p_demand_plan_id))
2506 and
2507 0 = (select count(1) from msd_npi_related_products_v mnrp where mnrp.seq_id = mep.seq_id)
2508 and
2509 mep.event_id = mev.event_id
2510 and mev.event_type_id = '3';
2511
2512 Begin
2513
2514 if l_debug = 'Y' then
2515 debug_out( 'Entering chk_scen_events ' || to_char(sysdate, 'hh24:mi:ss'));
2516 end if;
2517
2518 display_message( 'Validating All Events contained in Demand Plan Definition', SECTION);
2519 display_message( rpad('Event Name', 15) || rpad('Product Level Name', 24) ||
2520 rpad('Product Level Value', 24) || rpad('Description', 30), HEADING);
2521 display_message( '-----------------------------------------------------------------------------', HEADING);
2522 for token in c
2523 loop
2524 display_message(rpad(token.event_name, 24) || rpad(token.product_lvl_name, 20) || rpad(token.product_lvl_val, 20) || rpad('No Base Product for NPI', 30),ERROR);
2525 end loop;
2526
2527 if l_debug = 'Y' then
2528 debug_out( 'Exiting chk_scen_events ' || to_char(sysdate, 'hh24:mi:ss'));
2529 end if;
2530
2531 End CHK_SCEN_EVENTS;
2532
2533 procedure chk_calendars (p_demand_plan_id in number,
2534 p_calendar_type in number,
2535 p_lowest_lvl_id in varchar2) is
2536
2537 l_stmt varchar2(2000);
2538 l_start_date varchar2(2000) := 'TO_DATE(''' || TO_CHAR(l_dp_min_date, 'ddmmyyyy') || ''', ''ddmmyyyy'')';
2539 l_end_date varchar2(2000) := 'TO_DATE(''' || TO_CHAR(l_dp_max_date, 'ddmmyyyy') || ''', ''ddmmyyyy'')';
2540 p_date_clmn varchar2(100);
2541 x_calendar_type varchar2(80);
2542 x_period_type varchar2(80);
2543 x_period_value date;
2544 l_num_cal_codes number;
2545
2546 cursor c4 is
2547 select 1
2548 from msd_dp_calendars
2549 where calendar_type = p_calendar_type
2550 and demand_plan_id = p_demand_plan_id;
2551
2552 CURSOR c1 IS
2553 SELECT count(1)
2554 FROM msd_dp_calendars
2555 WHERE demand_plan_id = p_demand_plan_id
2556 AND calendar_type = p_calendar_type;
2557
2558 type c_cal_type is ref cursor;
2559 c_cal_data c_cal_type;
2560 num_cal_codes number;
2561 x_count number := 0;
2562
2563 CURSOR c2 IS
2564 select decode(p_lowest_lvl_id,
2565 '1', ' WEEK_END_DATE ',
2566 '2', ' MONTH_END_DATE ',
2567 '3', ' MONTH_END_DATE ',
2568 '4', ' QUARTER_END_DATE ',
2569 '5', ' YEAR_END_DATE ',
2570 '6', ' MONTH_END_DATE ',
2571 '7', ' QUARTER_END_DATE ',
2572 '8', ' YEAR_END_DATE ',
2573 '9', ' DAY',
2574 '10', ' WEEK_END_DATE ',
2575 '11', ' MONTH_END_DATE ',
2576 '12', ' QUARTER_END_DATE ',
2577 '13', ' YEAR_END_DATE ',
2578 ' DAY ')
2579 from dual;
2580
2581 CURSOR get_meaning_1 (p_lookup_type in varchar2, p_lookup_code in varchar2) IS
2582 select meaning
2583 from fnd_lookup_values_vl
2584 where lookup_type = p_lookup_type
2585 and lookup_code = p_lookup_code;
2586
2587 CURSOR get_meaning_2 (p_lookup_type in varchar2, p_lookup_code in number) IS
2588 select meaning
2589 from fnd_lookup_values_vl
2590 where lookup_type = p_lookup_type
2591 and lookup_code = to_char(p_lookup_code);
2592
2593 begin
2594
2595 if l_debug = 'Y' then
2596 debug_out( 'Entering chk_calendars ' || to_char(sysdate, 'hh24:mi:ss'));
2597 end if;
2598
2599 /* Get the Description fors Output */
2600
2601 open get_meaning_1('MSD_CALENDAR_TYPE', to_char(p_calendar_type));
2602 fetch get_meaning_1 into x_calendar_type;
2603 if (get_meaning_1%NOTFOUND) then
2604 x_calendar_type := 'UNDEFINED';
2605 end if;
2606 close get_meaning_1;
2607
2608 open get_meaning_2('MSD_PERIOD_TYPE', p_lowest_lvl_id);
2609 fetch get_meaning_2 into x_period_type;
2610 if (get_meaning_2%NOTFOUND) then
2611 x_period_type := 'UNDEFINED';
2612 end if;
2613 close get_meaning_2;
2614
2615
2616 /* Only continue checking if the Minimum time level is specified. */
2617 if (p_lowest_lvl_id is null) then
2618 open c1;
2619 fetch c1 into l_num_cal_codes;
2620 close c1;
2621 if (l_num_cal_codes >= 1) then
2622 display_message('Lowest Time Level is not specified for '||x_calendar_type,ERROR);
2623 end if;
2624 return;
2625 end if;
2626
2627
2628
2629 /* Check to see if any calendars have been attached for this type.
2630 * Only check if the minimum time level has been specified.
2631 */
2632
2633
2634 open c4;
2635 fetch c4 into num_cal_codes;
2636 close c4;
2637 if (num_cal_codes = 1) then
2638 num_cal_codes := 0;
2639 else
2640
2641 display_message( rpad(x_calendar_type, 40) || rpad (x_period_type, 20) || rpad('Calendar not chosen.', 63), WARNING);
2642 return;
2643 end if;
2644
2645 /* Only continue check if the Calendar is not Gregorian. */
2646 /* There will always be only one Gregorian Calendar attached. */
2647 if (p_calendar_type = 1) then
2648 return;
2649 end if;
2650
2651 open c1;
2652 fetch c1 into num_cal_codes;
2653 close c1;
2654
2655 open c2;
2656 fetch c2 into p_date_clmn;
2657 close c2;
2658
2659 l_stmt := 'SELECT ' || p_date_clmn;
2660 l_stmt := l_stmt || ' from msd_time ';
2661 l_stmt := l_stmt || ' where calendar_type = ' || p_calendar_type;
2662 l_stmt := l_stmt || ' and calendar_code in ';
2663 l_stmt := l_stmt || '(SELECT calendar_code FROM msd_dp_calendars';
2664 l_stmt := l_stmt || ' WHERE demand_plan_id = ' || p_demand_plan_id;
2665 l_stmt := l_stmt || ' AND calendar_type = ' || p_calendar_type || ')';
2666 l_stmt := l_stmt || ' AND day between ' || l_start_date || ' and ' || l_end_date;
2667 l_stmt := l_stmt || ' group by ' || p_date_clmn|| ' having count(distinct calendar_code || ' || p_date_clmn || ') < ' || num_cal_codes;
2668
2669 /* display_message (substr(l_stmt,1,90), WARNING);
2670 * display_message (substr(l_stmt,91,180), WARNING);
2671 * display_message (substr(l_stmt,181,270), WARNING);
2672 * display_message (substr(l_stmt,271,360), WARNING);
2673 */
2674
2675 x_count := 0;
2676
2677 if l_debug = 'Y' then
2678 debug_out( l_stmt);
2679 end if;
2680
2681 open c_cal_data for l_stmt;
2682 loop
2683
2684 fetch c_cal_data into x_period_value;
2685 exit when c_cal_data%NOTFOUND;
2686
2687 if (x_count = 0) then
2688 display_message(' ' || 'Validating All the ' || x_calendar_type || ' at ' || x_period_type || ' has same bucket start and end dates.', SECTION);
2689 display_message( ' ' || rpad('Calendar Type', 25) || rpad ('Period Type', 20) || rpad('Description', 63), HEADING);
2690 display_message( '-----------------------------------------------------------------------------', HEADING);
2691
2692 end if;
2693
2694 x_count := x_count + 1;
2695
2696
2697 if (x_count = 10) then
2698 exit;
2699 end if;
2700
2701 display_message( ' ' || rpad(x_calendar_type, 25) || rpad (x_period_type, 20) || rpad(x_period_value || ' not matched.', 63), ERROR);
2702 end loop;
2703
2704 close c_cal_data;
2705
2706 if l_debug = 'Y' then
2707 debug_out( 'Exiting chk_calendars ' || to_char(sysdate, 'hh24:mi:ss'));
2708 end if;
2709
2710
2711 EXCEPTION
2712 WHEN OTHERS THEN
2713 debug_out( substr(sqlerrm, 91, 90) );
2714 x_count := 1;
2715 while x_count<= length(l_stmt) loop
2716 debug_out( substr(l_stmt, x_count, 90) );
2717 x_count := x_count+90;
2718 end loop;
2719 End chk_calendars;
2720
2721 --Added for multiple composites enhancements
2722
2723 --
2724 -- check composite group has the same dimension
2725 --
2726 Procedure chk_composite_group_dimension (p_demand_plan_id in number) is
2727 Begin
2728
2729 if l_debug = 'Y' then
2730 debug_out( 'Entering chk_composite_group_dimension ' || to_char(sysdate, 'hh24:mi:ss'));
2731 end if;
2732
2733 display_message('Checking for Composites having streams with different dimensions' , SECTION);
2734 display_message(rpad(rpad('Composite ', 11)||'Stream Name', 101) , HEADING);
2735 display_message(rpad('-', 10, '-') || ' ' || rpad('-', 100, '-')|| ' ' ||rpad('-', 3, '-') , HEADING);
2736 --
2737 FOR j IN chk_comp_group(p_demand_plan_id)
2738 LOOP
2739 display_message( rpad(j.composite_group_code , 10) ||rpad(j.description, 100) , WARNING);
2740 END LOOP;
2741
2742 if l_debug = 'Y' then
2743 debug_out( 'Entering chk_composite_group_dimension ' || to_char(sysdate, 'hh24:mi:ss'));
2744 end if;
2745
2746 END chk_composite_group_dimension;
2747
2748 --
2749 -- check composite group has the same level
2750 --
2751 Procedure chk_composite_group_level (p_demand_plan_id in number) is
2752 lv_level_id1 msd_levels.level_id%TYPE := 0;
2753 lv_level_id2 msd_levels.level_id%TYPE := 0;
2754 lv_date_column_name msd_cs_defn_column_dtls.planning_view_column_name%TYPE;
2755 lv_previous_stream msd_cs_definitions.name%TYPE := '##'; --Previous stream verified
2756 lv_level_differ Number :=2; --1-Yes and 2-No
2757
2758 Begin
2759
2760 if l_debug = 'Y' then
2761 debug_out( 'Entering chk_composite_group_level ' || to_char(sysdate, 'hh24:mi:ss'));
2762 end if;
2763
2764 display_message('Checking for Composites having streams with different dimension levels' , SECTION);
2765 display_message( rpad('Composite ', 11)|| rpad('Stream Name', 101) , HEADING);
2766 display_message( rpad('-', 10, '-') || ' ' ||rpad('-', 100, '-'), HEADING);
2767 --
2768 FOR j IN chk_comp_group_lvl(p_demand_plan_id)
2769 LOOP
2770
2771 IF (lv_previous_stream = j.name2 AND lv_level_differ = 2) OR
2772 (lv_previous_stream <> j.name2) THEN
2773
2774 IF j.level_id1 = -1234 THEN
2775
2776 --Derive the level id from the fact tables if the level id's are not defined
2777 --in the stream definition(for eg.,Input Scenario)
2778 lv_level_id1 := get_level_id(j.view_name1,
2779 get_level_column_name(j.dim1_code,j.cs_id1),
2780 j.date_clmn1,
2781 j.start_date1,
2782 j.end_date1,
2783 j.system_flag1,
2784 j.multi_stream_flag1,
2785 j.param1,
2786 j.cs_id1,
2787 j.level_id1,
2788 j.input_demand_plan_id1,
2789 j.input_scenario_id1);
2790 ELSE
2791 lv_level_id1 := j.level_id1;
2792 END IF;
2793
2794 IF j.level_id2 = -9999 THEN
2795 --Derive the level id from the fact tables if the level id's are not defined
2796 --in the stream definition(for eg.,Input Scenario)
2797 lv_level_id2 := get_level_id(j.view_name2,
2798 get_level_column_name(j.dim2_code,j.cs_id2),
2799 j.date_clmn2,
2800 j.start_date2,
2801 j.end_date2,
2802 j.system_flag2,
2803 j.multi_stream_flag2,
2804 j.param2,
2805 j.cs_id2,
2806 j.level_id2,
2807 j.input_demand_plan_id2,
2808 j.input_scenario_id2);
2809 ELSE
2810 lv_level_id2 := j.level_id2;
2811 END IF;
2812
2813 --Level is not matching or level id is null
2814 IF ( (j.level_id1 <> j.level_id2 AND
2815 j.level_id1 <> -1234 AND
2816 j.level_id2 <> -9999) OR
2817 (nvl(lv_level_id1,-1234) <> nvl(lv_level_id2,-9999))) THEN
2818
2819 display_message( rpad(j.composite_group_code, 10) ||rpad(j.description2, 100), WARNING);
2820 lv_level_differ := 1;
2821 ELSE
2822 lv_level_differ := 2;
2823 END IF;
2824
2825 lv_previous_stream := j.name2;
2826 lv_level_id1 := 0;--resetting the level id's
2827 lv_level_id2 := 0;
2828
2829 END IF;
2830 END LOOP;
2831
2832 if l_debug = 'Y' then
2833 debug_out( 'Exiting chk_composite_group_level ' || to_char(sysdate, 'hh24:mi:ss'));
2834 end if;
2835
2836 END chk_composite_group_level;
2837
2838 --
2839 -- Derives the level_id column name in the planning server view for a given
2840 -- Dimension and stream
2841 --
2842 Function get_level_column_name (p_dim_code in varchar2,
2843 p_cs_id in number)
2844 return Varchar2 is
2845
2846 lv_level_column_name msd_cs_defn_column_dtls.planning_view_column_name%TYPE;
2847
2848 Begin
2849 OPEN get_lvl_column_name(p_dim_code,p_cs_id);
2850 FETCH get_lvl_column_name INTO lv_level_column_name;
2851 CLOSE get_lvl_column_name;
2852 RETURN lv_level_column_name;
2853 END get_level_column_name;
2854
2855 --
2856 -- Derives the level_id from the fact table
2857 --
2858 Function get_level_id (p_view_name in varchar2,
2859 p_View_level_col in varchar2,
2860 p_view_date_col in varchar2,
2861 p_start_date in date,
2862 p_end_date in date,
2863 p_system_flag in Varchar2,
2864 p_multi_stream_flag in Varchar2,
2865 p_parameter_name in Varchar2,
2866 p_cs_id in Number,
2867 p_call_source in Number,
2868 p_input_demand_plan_id in Number,
2869 p_input_scenario_id in Number)
2870 return Number is
2871 lv_level_id msd_levels.level_id%TYPE;
2872 lv_sql_stmt Varchar2(2000);
2873 lv_start_date Varchar2(100);
2874 lv_end_date Varchar2(100);
2875 lv_name VARCHAR2(300);--Designator Column Name in Fact Table.
2876
2877 /* Index of character being printed for error messages. */
2878 i NUMBER;
2879
2880 TYPE get_level_typ IS REF CURSOR;
2881 get_level get_level_typ; -- declare cursor variable
2882
2883 /* Added to check if custom stream is Input Scenario */
2884 cursor get_cs_name (p_cs_definition_id in number) is
2885 select name
2886 from msd_cs_definitions
2887 where cs_definition_id = p_cs_definition_id;
2888
2889 x_name varchar2(100);
2890 x_input_scenario varchar2(100) := 'MSD_INPUT_SCENARIO';
2891
2892 Begin
2893
2894 lv_level_id := p_call_source;
2895
2896 lv_name := get_desig_clmn_name(p_cs_id);
2897
2898 open get_cs_name ( p_cs_id );
2899 fetch get_cs_name into x_name;
2900 close get_cs_name;
2901
2902 lv_start_date := 'TO_DATE(''' || TO_CHAR(p_start_date, 'DD-MON-YYYY') || ''', ''DD-MON-YYYY'')';
2903 lv_end_date := 'TO_DATE(''' || TO_CHAR(p_end_date, 'DD-MON-YYYY') || ''', ''DD-MON-YYYY'')';
2904
2905 /* Input Scenario is a special case.
2906 * The demand plan and scenario are needed to access the index;
2907 * thats why they are added as filters.
2908 */
2909
2910 if (x_name = x_input_scenario) AND
2911 p_input_demand_plan_id is not null AND
2912 p_input_scenario_id is not null AND
2913 lv_name is not null AND
2914 p_parameter_name is not null then
2915
2916 lv_sql_stmt := 'SELECT decode('||p_View_level_col || ',0,null,' || p_View_level_col || ')'
2917 ||' FROM '||p_view_name
2918 ||' WHERE '||p_view_date_col||' >= '||lv_start_date
2919 ||' AND '||p_view_date_col||' <= '||lv_end_date
2920 ||' AND rownum < 2';
2921
2922 lv_sql_stmt := lv_sql_stmt || ' AND demand_plan_id = ' || p_input_demand_plan_id;
2923 lv_sql_stmt := lv_sql_stmt || ' AND scenario_id = ' || p_input_scenario_id;
2924 lv_sql_stmt := lv_sql_stmt
2925 ||' AND ' || lv_name || ' = '''|| replace( p_parameter_name, '''', '''''' )|| '''';
2926
2927 else
2928
2929 lv_sql_stmt := 'SELECT '||p_View_level_col
2930 ||' FROM '||p_view_name
2931 ||' WHERE '||p_view_date_col||' >= '||lv_start_date
2932 ||' AND '||p_view_date_col||' <= '||lv_end_date
2933 ||' AND rownum < 2'
2934 ||' AND action_code <> ''D''';
2935
2936 --For multiple streams check designator
2937 IF (nvl(p_multi_stream_flag,'N') = 'Y') AND
2938 lv_name IS NOT NULL AND
2939 p_parameter_name IS NOT NULL THEN
2940 lv_sql_stmt := lv_sql_stmt
2941 ||' AND ' || lv_name || ' = '''|| replace( p_parameter_name, '''', '''''' ) || '''';
2942 END IF;
2943
2944 --For Streams Defined using Custom Stream Fact Tables
2945 IF (p_system_flag = 'C') THEN
2946 lv_sql_stmt := lv_sql_stmt
2947 || ' AND cs_definition_id = ' || p_cs_id;
2948 END IF;
2949
2950 END IF;
2951
2952
2953 IF p_View_level_col IS NOT NULL AND p_view_name IS NOT NULL THEN
2954
2955 if l_debug = 'Y' then
2956 debug_out( lv_sql_stmt);
2957 end if;
2958
2959 OPEN get_level FOR lv_sql_stmt;
2960 FETCH get_level INTO lv_level_id;
2961 CLOSE get_level;
2962 END IF;
2963
2964 RETURN lv_level_id;
2965
2966 EXCEPTION
2967 WHEN OTHERS THEN
2968 IF get_level%ISOPEN THEN
2969 CLOSE get_level;
2970 END IF;
2971
2972 display_message( ' fact data check failed for Multiple Composites.', WARNING);
2973 --
2974 debug_out( substr(sqlerrm, 1 , 90) );
2975 debug_out( substr(sqlerrm, 91, 90) );
2976
2977 i := 1;
2978 while i<= length(lv_sql_stmt)
2979 loop
2980 debug_out( substr(lv_sql_stmt, i, 90));
2981 i := i+90;
2982 end loop;
2983
2984 RETURN lv_level_id;
2985 END get_level_id;
2986
2987 --Added for multiple composites enhancements
2988
2989
2990 -- Added for checking lowest time levels
2991 Procedure chk_min_time (p_g_min_tim_lvl_id in number,
2992 p_m_min_tim_lvl_id in number,
2993 p_f_min_tim_lvl_id in number,
2994 p_c_min_tim_lvl_id in number) is
2995 begin
2996
2997 if l_debug= 'Y' then
2998 debug_out( 'Entering chk_min_time ' || to_char(sysdate, 'hh24:mi:ss'));
2999 end if;
3000
3001 display_message('Checking lowest time levels', SECTION);
3002 display_message('Error Description', HEADING);
3003 display_message(rpad('-', 60, '-'), HEADING);
3004
3005 if (9 in (p_g_min_tim_lvl_id,p_m_min_tim_lvl_id,p_f_min_tim_lvl_id,p_c_min_tim_lvl_id))
3006 and
3007 ((nvl(p_g_min_tim_lvl_id,9) <> 9)
3008 or (nvl(p_c_min_tim_lvl_id,9) <> 9)
3009 or (nvl(p_f_min_tim_lvl_id,9) <> 9)
3010 or (nvl(p_m_min_tim_lvl_id,9) <> 9)
3011 ) then
3012 display_message('If day is chosen as a lowest time level, others can only be day too.', ERROR);
3013 end if;
3014
3015 if l_debug = 'Y' then
3016 debug_out( 'Exiting chk_min_time ' || to_char(sysdate, 'hh24:mi:ss'));
3017 end if;
3018
3019 End chk_min_time;
3020 -- End check lowest time levels.
3021
3022 /* Lock the row until demand plan validation completes */
3023
3024 Procedure Lock_Row(p_demand_plan_id in number) Is
3025 Counter NUMBER;
3026 CURSOR C IS
3027 SELECT demand_plan_name
3028 FROM msd_demand_plans
3029 WHERE demand_plan_id = p_demand_plan_id
3030 FOR UPDATE of demand_plan_name NOWAIT;
3031 Recinfo C%ROWTYPE;
3032 BEGIN
3033 OPEN C;
3034 FETCH C INTO Recinfo;
3035 if (C%NOTFOUND) then
3036 CLOSE C;
3037 return;
3038 end if;
3039
3040 CLOSE C;
3041
3042 EXCEPTION
3043 When APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION then
3044 IF (C% ISOPEN) THEN
3045 close C;
3046 END IF;
3047 display_message('Cannot obtain a lock on this demand plan.', ERROR);
3048 return;
3049 END Lock_Row;
3050
3051 function get_desig_clmn_name (p_cs_id in number) return VARCHAR2 IS
3052
3053 CURSOR get_str_name (p_id NUMBER) IS
3054 SELECT planning_view_column_name
3055 FROM msd_cs_defn_column_dtls_v
3056 WHERE cs_definition_id = p_id
3057 AND identifier_type = 'CSIDEN';
3058
3059 x_str_name varchar2(30);
3060
3061 BEGIN
3062
3063 open get_str_name (p_cs_id);
3064 fetch get_str_name into x_str_name;
3065 close get_str_name;
3066
3067 if (x_str_name is null) then
3068 x_str_name := 'CS_NAME';
3069 end if;
3070
3071 return x_str_name;
3072 end;
3073
3074 --
3075 -- Procedure to check that Item Validation is Collected and
3076 -- exists in level values.
3077 --
3078 Procedure chk_iv_org (p_demand_plan_id in number, p_iv_flag in varchar2, p_stripe_stream_name in varchar2, p_stripe_sr_level_pk in varchar2) is
3079
3080 cursor get_no_iv_org is
3081 select instance_code
3082 from msc_apps_instances
3083 where validation_org_id is null;
3084
3085 cursor get_iv_org is
3086 select to_char(instance_id), to_char(validation_org_id) sr_level_pk
3087 from msc_apps_instances
3088 minus
3089 select instance, sr_level_pk
3090 from msd_level_values_ds
3091 where demand_plan_id = p_demand_plan_id
3092 and level_id = 7;
3093
3094
3095 Begin
3096
3097 if l_debug = 'Y' then
3098 debug_out( 'Entering chk_iv_org ' || to_char(sysdate, 'hh24:mi:ss'));
3099 end if;
3100
3101 if (p_iv_flag = 'N') then
3102
3103 display_message('Checking Item Validation Settings ' , SECTION);
3104 display_message(rpad('-', 5, '-') || rpad(' ', 64, '-'), HEADING);
3105 --
3106
3107 -- Check to see if Item validation Org is setup for each instance.
3108 for get_no_iv_org_rec in get_no_iv_org
3109 loop
3110 display_message(rpad(get_no_iv_org_rec.instance_code, 5) || ' ' ||
3111 rpad('Item Validation Org not collected in this Instance.', 64), WARNING);
3112 end loop;
3113
3114 if ((p_stripe_stream_name is not null) or (p_stripe_sr_level_pk is not null)) then
3115 for get_iv_org_rec in get_iv_org
3116 loop
3117 display_message(rpad('Not all Item Validation Orgs found in Demand Partition.', 64), WARNING);
3118 exit;
3119 end loop;
3120 end if;
3121
3122 if l_debug = 'Y' then
3123 debug_out( 'Exiting chk_iv_org ' || to_char(sysdate, 'hh24:mi:ss'));
3124 end if;
3125 end if;
3126
3127 End;
3128
3129 Procedure chk_iso_org (p_demand_plan_id in number)
3130 is
3131
3132 cursor is_plan_lob (p_demand_plan_id number)
3133 is
3134 select stripe_level_id,build_stripe_stream_name
3135 from msd_demand_plans
3136 where demand_plan_id = p_demand_plan_id;
3137
3138 cursor invalid_internal_orgs (p_demand_plan_id number)
3139 is
3140 select mdio.sr_organization_id, mlv.level_value
3141 from msd_dp_iso_organizations mdio,
3142 -- msd_level_values_ds geo,
3143 msd_level_values_ds org,
3144 msd_level_values mlv
3145 where mdio.demand_plan_id = p_demand_plan_id
3146 and mdio.demand_plan_id = org.demand_plan_id
3147 and mdio.sr_instance_id = org.instance
3148 --and geo.level_id = 15
3149 --and geo.system_attribute1 = 'I'
3150 and org.sr_level_pk = mdio.sr_organization_id
3151 and org.level_id = 7
3152 --and org.instance = geo.instance
3153 --and org.demand_plan_id = geo.demand_plan_id
3154 --and org.sr_level_pk = geo.sr_level_pk
3155 and mlv.instance = org.instance
3156 and mlv.sr_level_pk = org.sr_level_pk
3157 and mlv.level_id = org.level_id;
3158
3159 Recinfo invalid_internal_orgs%ROWTYPE;
3160 Rec_is_plan_lob is_plan_lob%ROWTYPE;
3161
3162 l_flag BOOLEAN := FALSE;
3163 l_flag1 BOOLEAN := FALSE;
3164
3165 Begin
3166
3167 if l_debug = 'Y' then
3168 debug_out( 'Entering chk_iso_org ' || to_char(sysdate, 'hh24:mi:ss'));
3169 end if;
3170
3171 open is_plan_lob(p_demand_plan_id);
3172 fetch is_plan_lob INTO Rec_is_plan_lob;
3173 if (is_plan_lob%NOTFOUND) then
3174 debug_out( 'Plan is not striped.The Internal organizations attached to plan (if any) will not be considered.');
3175 else
3176 l_flag := TRUE;
3177 end if;
3178 close is_plan_lob;
3179
3180 if l_flag then
3181
3182 display_message('Checking any Internal Organizations attached are also part of Plan Scope' , SECTION);
3183 display_message(rpad('Organization', 36) || rpad('Error description', 55) , HEADING);
3184 display_message(rpad('-', 35, '-') || ' ' || rpad('-', 55, '-') , HEADING);
3185
3186 open invalid_internal_orgs (p_demand_plan_id);
3187 loop
3188 fetch invalid_internal_orgs INTO Recinfo;
3189
3190 if (invalid_internal_orgs%NOTFOUND) then
3191
3192 if l_flag1 then
3193 null;
3194 else
3195 debug_out( 'None of the Internal Organizations attached are included in plan scope');
3196 end if;
3197
3198 exit;
3199
3200 else
3201
3202 l_flag1 := TRUE;
3203 display_message( rpad(Recinfo.level_value , 36) || rpad('This internal organization already exists in the plan.', 55) , WARNING);
3204
3205
3206 end if;
3207 end loop;
3208
3209 close invalid_internal_orgs;
3210
3211 end if;
3212
3213
3214 if l_debug = 'Y' then
3215 debug_out( 'Exiting chk_iso_org ' || to_char(sysdate, 'hh24:mi:ss'));
3216 end if;
3217
3218 End;
3219
3220 /* Bug# 5248868
3221 * This procedure validates that whether price list data exists
3222 * for the price lists specified in the demand plan.
3223 * Note: Only time range validation is done
3224 */
3225 PROCEDURE chk_price_list_data (p_demand_plan_id IN NUMBER)
3226 IS
3227
3228 /*
3229 * Get all the price lists specified in the price list tab of the
3230 * demand plan.
3231 */
3232 CURSOR c_get_price_lists
3233 IS
3234 SELECT
3235 price_list_name
3236 FROM
3237 msd_dp_price_lists
3238 WHERE
3239 demand_plan_id = p_demand_plan_id;
3240
3241 /*
3242 * Get all the scenarios to which price lists are attached
3243 */
3244 CURSOR c_get_scen_with_price_lists
3245 IS
3246 SELECT
3247 scenario_id,
3248 scenario_name,
3249 horizon_start_date,
3250 horizon_end_date,
3251 price_list_name
3252 FROM
3253 msd_dp_scenarios
3254 WHERE
3255 demand_plan_id = p_demand_plan_id
3256 AND price_list_name IS NOT NULL;
3257
3258 /*
3259 * Get all the input parameters to which price lists are attached
3260 */
3261 CURSOR c_get_param_with_price_lists
3262 IS
3263 SELECT
3264 mdp.parameter_id,
3265 mdp.parameter_type_id,
3266 mdp.parameter_type,
3267 mdp.parameter_name,
3268 mdp.multiple_stream_flag,
3269 mdp.forecast_date_used,
3270 mdp.date_planning_view_clmn,
3271 mdp.start_date,
3272 mdp.end_date,
3273 mdp.price_list_name
3274 FROM
3275 msd_dp_parameters_cs_v mdp
3276 WHERE mdp.demand_plan_id = p_demand_plan_id
3277 AND mdp.parameter_type_id <> '7'
3278 AND nvl(mdp.stream_type,'ABCD') not in ('ARCHIVED','ARCHIVED_TIM','CALCULATED','PLACEHOLDER')
3279 AND mdp.price_list_name IS NOT NULL;
3280
3281 X_MIN_DATE DATE := to_date ('01-01-1000', 'DD-MM-YYYY');
3282 X_MAX_DATE DATE := to_date ('01-01-4000', 'DD-MM-YYYY');
3283
3284 x_price_list_data_found NUMBER := -1;
3285
3286 BEGIN
3287
3288 IF l_debug = 'Y' THEN
3289 debug_out( 'Entering chk_price_list_data ' || to_char(sysdate, 'hh24:mi:ss'));
3290 END IF;
3291
3292 /* Titles for each Price list in the Log File */
3293 display_message( 'Validating Price List Data', SECTION);
3294 display_message( rpad('Price List Name', 24) || 'Start Date End Date Error Description', HEADING);
3295 display_message( rpad('-', 23, '-') || ' ' || '---------- ---------- -----------------------------------', HEADING);
3296
3297 /* Loop through for each price list specified in the price lists tab of the demand plan */
3298 FOR c_price_list_rec IN c_get_price_lists
3299 LOOP
3300
3301 x_price_list_data_found := -1;
3302
3303 BEGIN
3304
3305 SELECT 1
3306 INTO x_price_list_data_found
3307 FROM dual
3308 WHERE EXISTS (SELECT 1
3309 FROM msd_price_list_v
3310 WHERE price_list_name = c_price_list_rec.price_list_name
3311 AND ( ( nvl(start_date, X_MIN_DATE) <= l_dp_min_date
3312 AND nvl(end_date, X_MAX_DATE) >= l_dp_min_date)
3313 OR ( nvl(start_date, X_MIN_DATE) >= l_dp_min_date
3314 AND nvl(end_date, X_MAX_DATE) <= l_dp_max_date)
3315 OR ( nvl(start_date, X_MIN_DATE) <= l_dp_max_date
3316 AND nvl(end_date, X_MAX_DATE) >= l_dp_max_date))
3317 AND rownum < 2);
3318 EXCEPTION
3319 WHEN NO_DATA_FOUND THEN
3320 x_price_list_data_found := -1;
3321
3322 END;
3323
3324 IF (x_price_list_data_found = -1) THEN
3325
3326 display_message( rpad(c_price_list_rec.price_list_name, 23) || ' ' ||
3327 to_char(l_dp_min_date, 'DD-MM-YYYY') || ' ' ||
3328 to_char(l_dp_max_date, 'DD-MM-YYYY') || ' ' ||
3329 ' Price list data does not exist', WARNING);
3330
3331 END IF;
3332
3333
3334 END LOOP;
3335
3336
3337 /* Titles for each Scenario with price list in the Log File */
3338 display_message( 'Validating Price List Data for Scenarios', SECTION);
3339 display_message( rpad ('Scenario Name', 18) || rpad('Price List Name', 18) || 'Start Date End Date Error Description', HEADING);
3340 display_message( rpad('-', 17, '-') || ' ' || rpad('-', 17, '-') || ' ' || '---------- ---------- --------------------------------', HEADING);
3341
3342 /* Loop through for each scenario for which price list is specified */
3343 FOR c_scn_with_pls IN c_get_scen_with_price_lists
3344 LOOP
3345
3346 x_price_list_data_found := -1;
3347
3348 BEGIN
3349
3350 SELECT 1
3351 INTO x_price_list_data_found
3352 FROM dual
3353 WHERE EXISTS (SELECT 1
3354 FROM msd_price_list_v
3355 WHERE price_list_name = c_scn_with_pls.price_list_name
3356 AND ( ( nvl(start_date, X_MIN_DATE) <= c_scn_with_pls.horizon_start_date
3357 AND nvl(end_date, X_MAX_DATE) >= c_scn_with_pls.horizon_start_date)
3358 OR ( nvl(start_date, X_MIN_DATE) >= c_scn_with_pls.horizon_start_date
3359 AND nvl(end_date, X_MAX_DATE) <= c_scn_with_pls.horizon_end_date)
3360 OR ( nvl(start_date, X_MIN_DATE) <= c_scn_with_pls.horizon_end_date
3361 AND nvl(end_date, X_MAX_DATE) >= c_scn_with_pls.horizon_end_date))
3362 AND rownum < 2);
3363 EXCEPTION
3364 WHEN NO_DATA_FOUND THEN
3365 x_price_list_data_found := -1;
3366
3367 END;
3368
3369 IF (x_price_list_data_found = -1) THEN
3370
3371 display_message( rpad(c_scn_with_pls.scenario_name, 17) || ' ' ||
3372 rpad(c_scn_with_pls.price_list_name, 17) || ' ' ||
3373 to_char(c_scn_with_pls.horizon_start_date, 'DD-MM-YYYY') || ' ' ||
3374 to_char(c_scn_with_pls.horizon_end_date, 'DD-MM-YYYY') || ' ' ||
3375 ' Price list data does not exist', WARNING);
3376
3377 END IF;
3378
3379 END LOOP;
3380
3381
3382 /* Titles for each Parameter with price list in the Log File */
3383 display_message( 'Validating Price List Data for Input Parameters', SECTION);
3384 display_message( rpad ('Parameter Name', 18) || rpad('Price List Name', 18) || 'Start Date End Date Error Description', HEADING);
3385 display_message( rpad('-', 17, '-') || ' ' || rpad('-', 17, '-') || ' ' || '---------- ---------- --------------------------------', HEADING);
3386
3387 /* Loop through for each parameter for which price list is specified */
3388 FOR c_param_with_pls IN c_get_param_with_price_lists
3389 LOOP
3390
3391 x_price_list_data_found := -1;
3392
3393 BEGIN
3394
3395 SELECT 1
3396 INTO x_price_list_data_found
3397 FROM dual
3398 WHERE EXISTS (SELECT 1
3399 FROM msd_price_list_v
3400 WHERE price_list_name = c_param_with_pls.price_list_name
3401 AND ( ( nvl(start_date, X_MIN_DATE) <= c_param_with_pls.start_date
3402 AND nvl(end_date, X_MAX_DATE) >= c_param_with_pls.start_date)
3403 OR ( nvl(start_date, X_MIN_DATE) >= c_param_with_pls.start_date
3404 AND nvl(end_date, X_MAX_DATE) <= c_param_with_pls.end_date)
3405 OR ( nvl(start_date, X_MIN_DATE) <= c_param_with_pls.end_date
3406 AND nvl(end_date, X_MAX_DATE) >= c_param_with_pls.end_date))
3407 AND rownum < 2);
3408 EXCEPTION
3409 WHEN NO_DATA_FOUND THEN
3410 x_price_list_data_found := -1;
3411
3412 END;
3413
3414 IF (x_price_list_data_found = -1) THEN
3415
3416 display_message( rpad(c_param_with_pls.parameter_type, 17) || ' ' ||
3417 rpad(c_param_with_pls.price_list_name, 17) || ' ' ||
3418 to_char(c_param_with_pls.start_date, 'DD-MM-YYYY') || ' ' ||
3419 to_char(c_param_with_pls.end_date, 'DD-MM-YYYY') || ' ' ||
3420 ' Price list data does not exist', WARNING);
3421
3422 IF nvl(c_param_with_pls.multiple_stream_flag,'N') = 'Y' THEN
3423 display_message(' (' || replace(c_param_with_pls.parameter_name, '''', '''''') ||
3424 '):' || nvl(nvl(c_param_with_pls.forecast_date_used, c_param_with_pls.date_planning_view_clmn),' '), INFORMATION);
3425 ELSE
3426 display_message(' :' || nvl(nvl(c_param_with_pls.forecast_date_used, c_param_with_pls.date_planning_view_clmn),' '), INFORMATION);
3427 END IF;
3428
3429 END IF;
3430
3431
3432 END LOOP;
3433
3434 IF l_debug = 'Y' THEN
3435 debug_out( 'Exiting chk_price_list_data ' || to_char(sysdate, 'hh24:mi:ss'));
3436 END IF;
3437
3438 EXCEPTION
3439 WHEN OTHERS THEN
3440 show_message(' Exiting chk_price_list_data with ERROR - ');
3441 show_message(substr( sqlerrm, 1, 80));
3442
3443 END chk_price_list_data;
3444
3445 End;
[Home] [Help]