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