1 PACKAGE BODY MSC_WS_IO AS
2 /* $Header: MSCWIOPB.pls 120.5 2008/03/20 15:57:58 bnaghi noship $ */
3
4 g_IGlbDmdSchTbl MscIGlbIODmdSchTbl; -- store all global demand schediles data
5 g_ILocDmdSchTbl MscILocIODmdSchTbl; -- store all local demand schediles data
6 g_ErrorCode VARCHAR2(9);
7
8 -- =============================================================
9 -- Desc: Please see package spec file for description
10 -- =============================================================
11 PROCEDURE LAUNCH_IO_BATCH (
12 processId OUT NOCOPY NUMBER,
13 status OUT NOCOPY VARCHAR2,
14 userId IN NUMBER,
15 responsibilityId IN NUMBER,
16 planId IN NUMBER,
17 anchorDate IN DATE,
18 archiveCurrVersPlan IN VARCHAR2) AS
19
20 l_error_tracking_num NUMBER;
21 l_result VARCHAR2(30);
22 l_plan_name VARCHAR2(10);
23 l_org_id NUMBER;
24 l_sr_instance_id NUMBER;
25 l_inventory_atp_flag NUMBER;
26 l_production NUMBER;
27 l_release_resched NUMBER;
28 l_24x7_purge NUMBER;
29 l_val_archivePlan NUMBER;
30
31 BEGIN
32
33 l_error_tracking_num := 3010;
34
35 -- ------------------------------------
36 -- validate and initialize apps
37 -- ------------------------------------
38 MSC_WS_COMMON.VALIDATE_USER_RESP(l_result, userId, responsibilityId);
39
40 IF (l_result <> 'OK') THEN
41 processId := -1;
42 status := l_result;
43 RETURN;
44 END IF;
45
46 l_error_tracking_num := 3020;
47
48 -- ------------------------------------
49 -- validate planId
50 -- ------------------------------------
51
52 BEGIN
53 SELECT plans.compile_designator, plans.organization_id, plans.sr_instance_id,
54 desig.inventory_atp_flag, desig.production
55 INTO l_plan_name, l_org_id, l_sr_instance_id, l_inventory_atp_flag, l_production
56 FROM msc_plans plans, msc_designators desig
57 WHERE plans.curr_plan_type = 4
58 AND plans.organization_id = desig.organization_id
59 AND plans.sr_instance_id = desig.sr_instance_id
60 AND plans.compile_designator = desig.designator
61 AND NVL(desig.disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE)
62 AND plans.organization_selection <> 1
63 AND plans.plan_id = planId;
64
65 EXCEPTION
66 WHEN no_data_found THEN
67 processId := -1;
68 status := 'INVALID_PLANID';
69 RETURN;
70 END;
71
72 l_error_tracking_num := 3030;
73
74 -- ------------------------------------
75 -- validate anchorDate
76 -- ------------------------------------
77 BEGIN
78 SELECT 'Y'
79 INTO l_result
80 FROM msc_calendar_dates dates,
81 msc_trading_partners mtp
82 WHERE dates.calendar_code = mtp.calendar_code
83 AND dates.exception_set_id = mtp.calendar_exception_set_id
84 AND mtp.sr_instance_id = dates.sr_instance_id
85 AND mtp.sr_tp_id = l_org_id
86 AND mtp.sr_instance_id = l_sr_instance_id
87 AND dates.calendar_date <= TRUNC(SYSDATE)
88 AND dates.calendar_date = anchorDate;
89 EXCEPTION
90 WHEN no_data_found THEN
91 processId := -1;
92 status := 'INVALID_ANCHORDATE';
93 RETURN;
94 END;
95
96
97 l_error_tracking_num:= 3035;
98
99 BEGIN
100 SELECT decode(archiveCurrVersPlan, 'Y', msc_ws_common.sys_yes, msc_ws_common.sys_no)
101 INTO l_val_archivePlan
102 FROM dual;
103
104 END;
105
106 l_error_tracking_num := 3040;
107
108 -- ------------------------------------
109 -- setting hidden parameters
110 -- ------------------------------------
111
112 -- PLAN_TYPE_DUMMY hidden parameter
113 -- Original default logic is "SELECT inventory_atp_flag from msc_designators d
114 -- where d.designator = :$FLEX$.MSC_SRS_SRO_NAME_LAUNCH_1 and d.inventory_atp_flag = 1"
115 -- Which pretty much meant this flag is set to either NULL or 1
116 IF (l_inventory_atp_flag <> 1) THEN l_inventory_atp_flag := NULL;
117 END IF;
118
119
120 -- MSC_24X7 hidden parameter
121 -- Original default logic is "Select meaning From Mfg_Lookups Where Lookup_Type = 'MSC_24X7_PURGE'
122 -- AND (DECODE(NVL(:$FLEX$.FND_CHAR240,2),2,2,1)=2 OR LOOKUP_CODE=2
123 -- Where FND_CHAR240 was referring to l_inventory_atp_flag variable. This is tricky since
124 -- if l_inventory_atp_flag is NULL, the above query will return 2 values, which invalidates the
125 -- default logic (since default logic can only return 1 value) resulting in default value being NULL
126 -- Most likely, this is some kinda of faulty logic in conc program definition
127 IF (l_inventory_atp_flag is NULL)
128 THEN l_24x7_purge := NULL;
129 ELSE l_24x7_purge := 2;
130 END IF;
131
132 -- RESCHEDULE_DUMMY parameter
133 -- Similar default logic as l_inventory_atp, "SELECT production from msc_designators d
134 -- where d.designator = :$FLEX$.MSC_SRS_SRO_NAME_LAUNCH_1 and d.production = 1"
135 IF (l_production <> 1)
136 THEN l_production := NULL;
137 END IF;
138
139 -- Similar logic as l_24x7_purge
140 IF (l_production is NULL)
141 THEN l_release_resched := NULL;
142 ELSE l_release_resched := 2;
143 END IF;
144
145
146 l_error_tracking_num := 3050;
147
148 -- ------------------------------------
149 -- Launch conc program
150 -- ------------------------------------
151 processId := FND_REQUEST.SUBMIT_REQUEST(
152 'MSC', -- application
153 'MSCSLPPR4', -- program
154 NULL, -- description
155 NULL, -- start_time
156 FALSE, -- sub_request
157 l_plan_name, -- plan name, argument 1
158 planId, -- plan name hidden, argument 2
159 to_char(msc_ws_common.sys_yes), -- launch snapshot, argument 3, set to Yes always
160 to_char(msc_ws_common.sys_yes), -- launch planner, argument 4, set to Yes always
161 to_char(msc_ws_common.sys_no), -- netchange, argument 5, set to No always
162 fnd_date.date_to_chardate(anchorDate), -- anchor date, argument 6
163 l_val_archivePlan,
164 l_inventory_atp_flag, -- plan type dummy, argument 7
165 l_24x7_purge, -- msc 24x7, argument 8
166 l_production, -- reschedule dummy, argument 9
167 l_release_resched); -- release rescheduled, argument 10
168
169 IF (processId = 0) THEN
170 processId := -1;
171 status := 'ERROR_SUBMIT';
172 return;
173 END IF;
174
175 status := 'SUCCESS';
176
177 EXCEPTION
178 WHEN others THEN
179 status := 'ERROR_UNEXPECTED_'||l_error_tracking_num;
180 processId := -1;
181 return;
182
183 END LAUNCH_IO_BATCH;
184
185
186 PROCEDURE LAUNCH_IO_BATCH_PUBLIC (
187 processId OUT NOCOPY NUMBER,
188 status OUT NOCOPY VARCHAR2,
189 UserName IN VARCHAR2,
190 RespName IN VARCHAR2,
191 RespApplName IN VARCHAR2,
192 SecurityGroupName IN VARCHAR2,
193 Language IN VARCHAR2,
194 planId IN NUMBER,
195 anchorDate IN DATE,
196 archiveCurrVersPlan IN VARCHAR2)AS
197 userid number;
198 respid number;
199 l_String VARCHAR2(30);
200 error_tracking_num number;
201 l_SecutirtGroupId NUMBER;
202 BEGIN
203 error_tracking_num :=2010;
204 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
205 IF (l_String <> 'OK') THEN
206 Status := l_String;
207 RETURN;
208 END IF;
209
210 error_tracking_num :=2030;
211
212 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSCFNSCW-SRO',l_SecutirtGroupId);
213 IF (l_String <> 'OK') THEN
214 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSCFPCMN-SRO', l_SecutirtGroupId);
215 IF (l_String <> 'OK') THEN
216 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSC_ORG_FNDRSRUN_LAUNCH_SRO',l_SecutirtGroupId);
217 IF (l_String <> 'OK') THEN
218 Status := l_String;
219 RETURN;
220 END IF;
221 END IF;
222 END IF;
223 error_tracking_num :=2040;
224
225 LAUNCH_IO_BATCH ( processId, status, userId ,respid, planId , anchorDate, archiveCurrVersPlan );
226 -- dbms_output.put_line('USERID=' || userid);
227
228
229 EXCEPTION
230 WHEN others THEN
231 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
232
233 return;
234 END LAUNCH_IO_BATCH_PUBLIC;
235
236
237 -- =============================================================
238 --
239 -- SET_IO_PLAN_OPTIONS and its private helper functions.
240 --
241 -- Un-handled exceptions generate error tokens in the
242 -- format of ERROR_UNEXPECTED_#####.
243 -- The possible values are:
244 -- 00301 - SET_IO_PLAN_OPTIONS/VALIDATE_PLAN_ID
245 -- 00302 - SET_IO_PLAN_OPTIONS/VALIDATE_PLAN_TYPE
246 -- 00303 - SET_IO_PLAN_OPTIONS/MSC_WS_COMMON.VALIDATE_SIMULATION_SET_ID
247 -- 00304 - SET_IO_PLAN_OPTIONS/VALIDATE_SER_LVL_SET_ID
248 -- 00305 - SET_IO_PLAN_OPTIONS/VALIDATE_GLB_DMD_SCHS/VALIDATE_G_DMD_SCH_ID
249 -- 00306 - SET_IO_PLAN_OPTIONS/VALIDATE_GLB_DMD_SCHS/VALIDATE_CONSUM_LVL (goe)
250 -- - SET_IO_PLAN_OPTIONS/VALIDATE_LOC_DMD_SCHS/VALIDATE_CONSUM_LVL (goe)
251 -- 00307 - SET_IO_PLAN_OPTIONS/VALIDATE_GLB_DMD_SCHS/VALIDATE_CONSUM_LVL (item)
252 -- - SET_IO_PLAN_OPTIONS/VALIDATE_LOC_DMD_SCHS/VALIDATE_CONSUM_LVL (item)
253 -- -- 00308 - SET_IO_PLAN_OPTIONS/VALIDATE_GLB_DMD_SCHS/VALIDATE_G_VARIABILITY/VALIDATE_G_VARIABILITY_ID
254 -- 00309 - SET_IO_PLAN_OPTIONS/VALIDATE_LOC_DMD_SCHS/MSC_WS_COMMON.PLAN_CONTAINS_THIS_ORG
255 -- 00310 - SET_IO_PLAN_OPTIONS/VALIDATE_LOC_DMD_SCHS/VALIDATE_L_DMD_SCH_ID
256 -- 00311 - SET_IO_PLAN_OPTIONS/MSC_WS_COMMON.PURGE_ALL_SCHEDULES
257 -- 00312 - SET_IO_PLAN_OPTIONS/UPDATE_PLAN_OPTIONS
258 -- 00313 - SET_IO_PLAN_OPTIONS/INSERT_ALL_SCHEDULES/INSERT_GLB_DMD_SCHEDULE
259 -- SET_IO_PLAN_OPTIONS/INSERT_OR_UPDATE_ALL_SCHS/INSERT_GLB_DMD_SCHEDULE
260 -- 00314 - SET_IO_PLAN_OPTIONS/INSERT_ALL_SCHEDULES/INSERT_LOC_DMD_SCHEDULE
261 -- - SET_IO_PLAN_OPTIONS/INSERT_OR_UPDATE_ALL_SCHS/INSERT_LOC_DMD_SCHEDULE
262 -- 00315 - SET_IO_PLAN_OPTIONS/INSERT_OR_UPDATE_ALL_SCHS
263 -- 00316 - SET_IO_PLAN_OPTIONS/INSERT_OR_UPDATE_ALL_SCHS/UPDATE_GLB_DMD_SCHEDULE
264 -- 00317 - SET_IO_PLAN_OPTIONS/INSERT_OR_UPDATE_ALL_SCHS
265 -- 00318 - SET_IO_PLAN_OPTIONS/INSERT_OR_UPDATE_ALL_SCHS/UPDATE_LOC_DMD_SCHEDULE
266 -- =============================================================
267
268 -- =============================================================
269 -- Desc: Validate plan id, copy the where clause from LAUNCH_IO_BATCH
270 --
271 -- Input:
272 -- PlanId Plan Id.
273 --
274 -- Output: The possible return statuses are:
275 -- OK
276 -- INVALID_PLANID
277 -- =============================================================
278 FUNCTION VALIDATE_PLAN_ID(
279 OrgId OUT NOCOPY NUMBER,
280 InsId OUT NOCOPY NUMBER,
281 PlanName OUT NOCOPY VARCHAR2,
282 PlanId IN NUMBER
283 ) RETURN VARCHAR2 AS
284 l_ReturnString VARCHAR2(100);
285 BEGIN
286 BEGIN
287 SELECT
288 plans.organization_id,
289 plans.sr_instance_id,
290 plans.compile_designator
291 INTO
292 OrgId,
293 InsId,
294 PlanName
295 FROM
296 msc_plans plans,
297 msc_designators desig
298 WHERE
299 plans.organization_id = desig.organization_id AND
300 plans.sr_instance_id = desig.sr_instance_id AND
301 plans.compile_designator = desig.designator AND
302 NVL(desig.disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE) AND
303 plans.organization_selection <> 1 AND
304 plans.curr_plan_type in (1,2,3,4,5,8,9) AND
305 plans.plan_id <> -1 AND
306 -- NVL(plans.copy_plan_id,-1) = -1 AND
307 -- NVL(desig.copy_designator_id, -1) = -1 AND
308 plans.plan_id = PlanId;
309 EXCEPTION WHEN NO_DATA_FOUND THEN
310 RETURN 'INVALID_PLANID';
311 WHEN others THEN
312 g_ErrorCode := 'ERROR_UNEXPECTED_00301';
313 raise;
314 END;
315
316 RETURN 'OK';
317 END VALIDATE_PLAN_ID;
318
319 -- =============================================================
320 -- Desc: Validate plan type is IO
321 -- Input:
322 -- PlanId plan id.
323 --
324 -- Output: The possible return statuses are:
325 -- OK
326 -- INVALID_PLAN_TYPE
327 -- =============================================================
328 FUNCTION VALIDATE_PLAN_TYPE( PlanId IN NUMBER ) RETURN VARCHAR2 AS
329 l_Dummy NUMBER;
330 BEGIN
331 BEGIN
332 SELECT
333 1 INTO l_Dummy
334 FROM
335 msc_plans
336 WHERE
337 curr_plan_type = 4 AND
338 plan_id = PlanId;
339 EXCEPTION WHEN NO_DATA_FOUND THEN
340 RETURN 'INVALID_PLAN_TYPE';
341 WHEN others THEN
342 g_ErrorCode := 'ERROR_UNEXPECTED_00302';
343 raise;
344 END;
345
346 RETURN 'OK';
347 END VALIDATE_PLAN_TYPE;
348
349 -- =============================================================
350 -- Desc: Validate Service Level set id.
351 -- Input:
352 -- SetId Service level set Id.
353 --
354 -- Output: The possible return statuses are:
355 -- OK
356 -- INVALID_SERVICE_LVL_SET_ID
357 -- =============================================================
358 FUNCTION VALIDATE_SER_LVL_SET_ID( SetId IN NUMBER ) RETURN VARCHAR2 AS
359 l_Dummy NUMBER;
360 BEGIN
361 IF SetId IS NOT NULL THEN
362 BEGIN
363 SELECT
364 1 INTO l_Dummy
365 FROM
366 msc_service_level_sets
367 WHERE
368 service_level_set_id = SetId;
369 EXCEPTION WHEN NO_DATA_FOUND THEN
370 RETURN 'INVALID_SERVICE_LVL_SET_ID';
371 WHEN others THEN
372 g_ErrorCode := 'ERROR_UNEXPECTED_00304';
373 raise;
374 END;
375 END IF;
376
377 RETURN 'OK';
378 END VALIDATE_SER_LVL_SET_ID;
379
380 -- =============================================================
381 -- Desc: Validate global demand schedule id
382 -- Input:
383 -- SchId global demand schedule id.
384 -- PlanName plan name.
385 --
386 -- Output: The possible return statuses are:
387 -- OK
388 -- INVALID_GLOBALDMDSCHS_DMD_SCH_ID
389 -- =============================================================
390 FUNCTION VALIDATE_G_DMD_SCH_ID(
391 ErrorType OUT NOCOPY VARCHAR2,
392 SchId IN NUMBER,
393 PlanName IN VARCHAR2
394 ) RETURN VARCHAR2 AS
395 BEGIN
396 BEGIN
397 SELECT
398 error_type INTO ErrorType
399 FROM
400 msd_dp_ascp_scenarios_v
401 WHERE
402 global_scenario_flag = 'Y' AND
403 scenario_name <> PlanName AND
404 scenario_id = SchId AND
405 last_revision IS NOT NULL;
406 EXCEPTION WHEN NO_DATA_FOUND THEN
407 RETURN 'INVALID_GLOBALDMDSCHS_DMD_SCH_ID';
408 WHEN others THEN
409 g_ErrorCode := 'ERROR_UNEXPECTED_00305';
410 raise;
411 END;
412
413 RETURN 'OK';
414 END VALIDATE_G_DMD_SCH_ID;
415
416 -- =============================================================
417 -- Desc: Validate the ship to consumption level. This function is
418 -- used by IO only. DRP and SRP have their own function in
419 -- MSC_WS_COMMON. ASCP has its own in MSC_WS_ASCP.
420 -- Input:
421 -- ShipTo Ship to consumption level.
422 -- SchId Demand schedule id.
423 --
424 -- Output: The possible return statuses are:
425 -- OK
426 -- INVALID_SHIP_TO_CONSUMPTION_LVL
427 -- =============================================================
428 FUNCTION VALIDATE_CONSUM_LVL(
429 ShipTo IN NUMBER,
430 SchId IN NUMBER
431 ) RETURN VARCHAR2 AS
432 l_scenario_lvl_geo NUMBER;
433 l_scenario_lvl_item NUMBER;
434 BEGIN
435 BEGIN
436 SELECT level_id INTO l_scenario_lvl_geo
437 FROM msd_dp_scenario_output_levels
438 WHERE scenario_id = SchId AND level_id in (11,15,41,42,30);
439 EXCEPTION WHEN NO_DATA_FOUND THEN
440 l_scenario_lvl_geo := 30;
441 WHEN others THEN
442 g_ErrorCode := 'ERROR_UNEXPECTED_00306';
443 raise;
444 END;
445
446 BEGIN
447 SELECT level_id INTO l_scenario_lvl_item
448 FROM msd_dp_scenario_output_levels
449 WHERE scenario_id = SchId AND level_id in (34,40);
450 EXCEPTION WHEN NO_DATA_FOUND THEN
451 l_scenario_lvl_item := 40;
452 WHEN others THEN
453 g_ErrorCode := 'ERROR_UNEXPECTED_00307';
454 raise;
455 END;
456
457 /*
458 2 Ship
459 3 Bill
460 4 Customer
461 5 Region
462 6 Item
463 7 Customer Site
464 8 Zone
465 9 Customer Zone
466 10 Demand Class
467 */
468 -- dbms_output.put_line('SchId: ' || SchId);
469 -- dbms_output.put_line('l_scenario_lvl_item: ' || l_scenario_lvl_item);
470 -- dbms_output.put_line('l_scenario_lvl_geo: ' || l_scenario_lvl_geo);
471
472 IF l_scenario_lvl_item = 40 THEN
473 IF l_scenario_lvl_geo = 11 THEN
474 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CS_ALL'
475 IF ShipTo NOT IN (4, 6, 7, 9) THEN -- Item, Customer, Customer Zone, Customer Site
476 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
477 END IF;
478 ELSIF l_scenario_lvl_geo = 15 THEN
479 -- simulate the logic from Record Groups 'MSC_SHIP_TO_C_ALL'
480 IF ShipTo NOT IN (4, 6) THEN -- Item, Customer
481 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
482 END IF;
483 ELSIF l_scenario_lvl_geo = 42 then
484 -- simulate the logic from Record Groups 'MSC_SHIP_TO_Z_ALL'
485 IF ShipTo NOT IN (6, 8) THEN -- Item, Zone
486 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
487 END IF;
488 ELSIF l_scenario_lvl_geo = 41 THEN
489 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CZ_ALL'
490 IF ShipTo NOT IN (4, 6, 8, 9) THEN -- Item, Customer, Customer Zone, Zone
491 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
492 END IF;
493 ELSIF l_scenario_lvl_geo = 30 THEN
494 -- simulate the logic from Record Groups 'MSC_SHIP_TO_ALL_ALL'
495 IF ShipTo <> 6 THEN -- Item'
496 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
497 END IF;
498 ELSE
499 -- ???? use the default record group ?????
500 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CS_ALL'
501 IF ShipTo NOT IN (4, 6, 7, 9) THEN -- Item, Customer, Customer Zone, Customer Site
502 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
503 END IF;
504 END IF;
505 ELSE -- IF l_scenario_lvl_item <> 40 THEN
506 IF l_scenario_lvl_geo = 11 THEN
507 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CS_DC'
508 IF ShipTo NOT IN (4, 6, 7, 9, 10) THEN -- Item, Customer, Demand Class, Customer Zone, Customer Site
509 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
510 END IF;
511 ELSIF l_scenario_lvl_geo = 15 THEN
512 -- simulate the logic from Record Groups 'MSC_SHIP_TO_C_DC'
513 IF ShipTo NOT IN (4, 6, 10) THEN -- Item, Customer, Demand Class
514 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
515 END IF;
516 ELSIF l_scenario_lvl_geo = 42 THEN
517 -- simulate the logic from Record Groups 'MSC_SHIP_TO_Z_DC'
518 IF ShipTo NOT IN (6, 8, 10) THEN -- Item, Demand Class, Zone
519 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
520 END IF;
521 ELSIF l_scenario_lvl_geo = 41 THEN
522 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CZ_DC'
523 IF ShipTo NOT IN (4, 6, 8, 9, 10) THEN -- Item', Customer, Customer Zone, Zone, Demand Class
524 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
525 END IF;
526 ELSIF l_scenario_lvl_geo = 30 THEN
527 -- simulate the logic from Record Groups 'MSC_SHIP_TO_ALL_DC'
528 IF ShipTo NOT IN (6, 10) THEN -- Item, Demand Class
529 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
530 END IF;
531 ELSE
532 -- ???? which record group should I use ?????
533 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CS_DC'
534 IF ShipTo NOT IN (4, 6, 7, 9, 10) THEN -- Item, Customer, Demand Class, Customer Zone, Customer Site
535 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
536 END IF;
537 END IF;
538 END IF;
539
540 RETURN 'OK';
541 END VALIDATE_CONSUM_LVL;
542
543 -- =============================================================
544 -- Desc: Validate demand variability type
545 --
546 -- Input:
547 -- VarId demand variability type id.
548 -- DesigType
549 --
550 -- Output: The possible return statuses are:
551 -- OK
552 -- INVALID
553 -- =============================================================
554 FUNCTION VALIDATE_VARIABILITY_ID(
555 VarId IN NUMBER,
556 DesigType IN NUMBER
557 ) RETURN VARCHAR2 AS
558 BEGIN
559 IF VarId = 1 AND DesigType <> 7 THEN
560 RETURN 'INVALID'; -- caller has to overwrite this
561 ELSIF VarId < 1 OR VarId > 3 THEN
562 RETURN 'INVALID'; -- caller has to overwrite this
563 END IF;
564 RETURN 'OK';
565 END VALIDATE_VARIABILITY_ID;
566
567
568 -- =============================================================
569 -- Desc: Validate demand variability type and its dependance
570 -- Input:
571 -- SchRec global demand schedule data.
572 --
573 -- Output: The possible return statuses are:
574 -- OK
575 -- INVALID_GLOBALDMDSCHS_VARIABILITY_TYPE
576 -- INVALID_GLOBALDMDSCHS_PROBABILITY
577 -- INVALID_GLOBALDMDSCHS_MEAN_ABS_PCT_ERROR
578 -- =============================================================
579 FUNCTION VALIDATE_G_VARIABILITY(SchRec IN MscGlbIODmdSchRec) RETURN VARCHAR2 AS
580 l_String VARCHAR2(100);
581 BEGIN
582 l_String := VALIDATE_VARIABILITY_ID(SchRec.DmdVariabilityType, 7);
583 IF l_String <> 'OK' THEN
584 RETURN 'INVALID_GLOBALDMDSCHS_VARIABILITY_TYPE';
585 END IF;
586 BEGIN
587 CASE SchRec.DmdVariabilityType
588 WHEN 1 THEN -- Accuracy_Metric_MAD
589 BEGIN
590 -- probability can be null or in the range of 0 to 1
591 IF SchRec.Probability IS NOT NULL THEN
592 IF SchRec.Probability < 0 OR SchRec.Probability > 1 THEN
593 RETURN 'INVALID_GLOBALDMDSCHS_PROBABILITY';
594 END IF;
595 END IF;
596 -- mean absolute % error can be null or in the range of 0 to 100
597 IF SchRec.MeanAbsPctError IS NOT NULL THEN
598 IF SchRec.MeanAbsPctError < 0 OR SchRec.MeanAbsPctError > 100 THEN
599 RETURN 'INVALID_GLOBALDMDSCHS_MEAN_ABS_PCT_ERROR';
600 END IF;
601 END IF;
602 END;
603 WHEN 2 THEN -- 'Probability'
604 -- probability is required and in the range of 0 to 1
605 IF SchRec.Probability IS NULL THEN
606 RETURN 'INVALID_GLOBALDMDSCHS_PROBABILITY';
607 ELSE
608 IF SchRec.Probability < 0 OR SchRec.Probability > 1 THEN
609 RETURN 'INVALID_GLOBALDMDSCHS_PROBABILITY';
610 END IF;
611 END IF;
612 -- mean absolute % error has to be null
613 IF SchRec.MeanAbsPctError IS NOT NULL THEN
614 RETURN 'INVALID_GLOBALDMDSCHS_MEAN_ABS_PCT_ERROR';
615 END IF;
616 WHEN 3 THEN -- 'Mean_Absolute_Pct_Err'
617 -- probability has to be null
618 IF SchRec.Probability IS NOT NULL THEN
619 RETURN 'INVALID_GLOBALDMDSCHS_PROBABILITY';
620 END IF;
621 -- mean absolute % error is required and in the range of 0 to 100
622 IF SchRec.MeanAbsPctError IS NOT NULL THEN
623 IF SchRec.MeanAbsPctError < 0 OR SchRec.MeanAbsPctError > 100 THEN
624 RETURN 'INVALID_GLOBALDMDSCHS_MEAN_ABS_PCT_ERROR';
625 END IF;
626 END IF;
627 END CASE;
628 END;
629
630 RETURN 'OK';
631 END VALIDATE_G_VARIABILITY;
632
633 -- =============================================================
634 -- Desc: load global demand schedules
635 -- Input:
636 -- PurgeAll Purge All Schs Flag
637 -- PlanId
638 --
639 -- Output: No output.
640 -- =============================================================
641 PROCEDURE LOAD_GLB_DMD_SCH_TBL(
642 SchTbl OUT NOCOPY MscIDmdSchVarTbl,
643 PurgeAll IN VARCHAR2,
644 PlanId IN NUMBER
645 ) AS
646 cursor schedule_c(idPlan number) is
647 SELECT
648 sr_instance_id,
649 input_schedule_id,
650 demand_variability_type,
651 probability
652 FROM
653 msc_plan_schedules
654 WHERE
655 plan_id = idPlan AND
656 organization_id = -1;
657
658 l_InsId NUMBER;
659 l_SchedId NUMBER;
660 l_VarType NUMBER;
661 l_Probability NUMBER;
662 BEGIN
663 SchTbl := MscIDmdSchVarTbl();
664 SchTbl.DELETE;
665 IF MSC_WS_COMMON.BOOL_TO_NUMBER(PurgeAll) = MSC_UTIL.SYS_NO THEN
666 BEGIN
667 OPEN schedule_c(PlanId);
668 LOOP
669 FETCH schedule_c into l_InsId, l_SchedId, l_VarType, l_Probability;
670 EXIT WHEN schedule_c%NOTFOUND;
671 SchTbl.extend;
672 SchTbl(SchTbl.count) :=
673 MscIDmdSchVarRec(
674 l_InsId,
675 -1,
676 l_SchedId,
677 l_VarType,
678 l_Probability
679 );
680 END LOOP;
681 CLOSE schedule_c;
682 END;
683 END IF;
684
685 END LOAD_GLB_DMD_SCH_TBL;
686
687 -- =============================================================
688 -- Desc: All global demand schedules have the same variability type
689 -- If the the variability type is probability, the sun must equal to 1
690 -- Input:
691 -- InsId Instance id
692 --
693 -- Output: The possible return statuses are:
694 -- OK
695 -- INVALID_VAR_TYPE_IN_GBL_SCH
696 -- INVALID_GLB_SUM_OF_PROB
697 -- =============================================================
698 FUNCTION VALIDATE_G_PROB(
699 PurgeAll IN VARCHAR2,
700 InsId IN NUMBER,
701 PlanId IN NUMBER
702 ) RETURN VARCHAR2 AS
703 l_String VARCHAR2(100);
704 l_SchTbl MscIDmdSchVarTbl; -- global shedules in database
705 l_VarType NUMBER;
706 l_Sum NUMBER;
707 BEGIN
708 IF g_IGlbDmdSchTbl.COUNT > 0 THEN
709 -- get all global schedules from database and store them in l_SchTbl
710 LOAD_GLB_DMD_SCH_TBL(l_SchTbl, PurgeAll, PlanId);
711 l_VarType := -1;
712 l_Sum := 0;
713 FOR I in g_IGlbDmdSchTbl.first..g_IGlbDmdSchTbl.last
714 LOOP
715 IF l_VarType = -1 THEN
716 l_VarType := g_IGlbDmdSchTbl(I).DmdVariabilityType;
717 ELSIF l_VarType <> g_IGlbDmdSchTbl(I).DmdVariabilityType THEN
718 RETURN 'INVALID_VAR_TYPE_IN_GBL_SCH';
719 END IF;
720 IF l_VarType = 2 THEN
721 l_Sum := l_Sum + g_IGlbDmdSchTbl(I).Probability;
722 END IF;
723 -- delete this demand schedule from l_SchTbl.
724 IF l_SchTbl.COUNT > 0 THEN
725 FOR J IN l_SchTbl.first..l_SchTbl.last
726 LOOP
727 BEGIN
728 IF l_SchTbl(J).InsId = InsId AND
729 l_SchTbl(J).SchId = g_IGlbDmdSchTbl(I).DmdSchId THEN
730 l_SchTbl.delete(J);
731 EXIT;
732 END IF;
733 EXCEPTION WHEN NO_DATA_FOUND THEN
734 NULL; -- skip, this element is deleted.
735 END;
736 END LOOP;
737 END IF;
738 END LOOP;
739 IF l_SchTbl.COUNT > 0 THEN
740 FOR I IN l_SchTbl.first..l_SchTbl.last
741 LOOP
742 BEGIN
743 IF l_SchTbl(I).DmdVariabilityType <> l_VarType THEN
744 RETURN 'INVALID_VAR_TYPE_IN_GBL_SCH';
745 ELSIF l_VarType = 2 THEN
746 l_Sum := l_Sum + l_SchTbl(I).Probability;
747 END IF;
748 EXCEPTION WHEN NO_DATA_FOUND THEN
749 NULL; -- skip, this element is deleted.
750 END;
751 END LOOP;
752 END IF;
753 -- test the sum
754 IF l_VarType = 2 AND l_Sum <> 1 THEN
755 RETURN 'INVALID_GBL_SUM_OF_PROB';
756 END IF;
757 END IF;
758 RETURN 'OK';
759 END VALIDATE_G_PROB;
760
761 -- =============================================================
762 -- Desc: validate global demand schedules
763 -- Input:
764 -- SchTable Global demand schedules.
765 -- PlanName Plan name.
766 --
767 -- Output: The possible return statuses are:
768 -- OK
769 -- INVALID_GLOBALDMDSCHS_DMD_SCH_ID
770 -- INVALID_GLOBALDMDSCHS_SHP_TO_CONSUMPTION_LVL
771 -- =============================================================
772 FUNCTION VALIDATE_GLB_DMD_SCHS(
773 SchTable IN MscGlbIODmdSchTbl,
774 PlanId IN NUMBER,
775 PlanName IN VARCHAR2,
776 InsId IN NUMBER,
777 PurgeAll IN VARCHAR2
778 ) RETURN VARCHAR2 AS
779 l_String VARCHAR2(100);
780 l_ErrorType VARCHAR2(30);
781 BEGIN
782 IF SchTable IS NOT NULL AND SchTable.COUNT > 0 THEN
783 FOR I IN SchTable.first..SchTable.last
784 LOOP
785 -- validate demand schedule id
786 l_String := VALIDATE_G_DMD_SCH_ID(l_ErrorType, SchTable(I).DmdSchId, PlanName);
787 IF (l_String <> 'OK') THEN
788 RETURN l_String;
789 END IF;
790
791 -- validate ship to consumption level
792 l_String := VALIDATE_CONSUM_LVL(
793 SchTable(I).ShipToConsumptionLvl,
794 SchTable(I).DmdSchId
795 );
796 IF (l_String <> 'OK') THEN
797 RETURN 'INVALID_GLOBALDMDSCHS_SHP_TO_CONSUMPTION_LVL';
798 END IF;
799
800 -- validate Type of Demand Variability and its dependence
801 l_String := VALIDATE_G_VARIABILITY(SchTable(I));
802 IF (l_String <> 'OK') THEN
803 RETURN l_String;
804 END IF;
805
806 g_IGlbDmdSchTbl.extend;
807 g_IGlbDmdSchTbl(g_IGlbDmdSchTbl.count) :=
808 MscIGlbIODmdSchRec(SchTable(I).DmdSchId,
809 SchTable(I).ShipToConsumptionLvl,
810 SchTable(I).DmdVariabilityType,
811 SchTable(I).Probability,
812 SchTable(I).MeanAbsPctError,
813 1, -- input_type
814 7 -- designator_type
815 );
816
817 END LOOP;
818 l_String := VALIDATE_G_PROB(PurgeAll, InsId, PlanId);
819 IF (l_String <> 'OK') THEN
820 RETURN l_String;
821 END IF;
822 END IF;
823
824 RETURN 'OK';
825 END VALIDATE_GLB_DMD_SCHS;
826
827 -- =============================================================
828 -- Desc: load local demand schedules for this scenario set
829 -- Input:
830 -- PurgeAll Purge All Schs Flag
831 -- ScenarioSetId
832 -- PlanId
833 --
834 -- Output: No output.
835 -- =============================================================
836 PROCEDURE LOAD_LOC_DMD_SCH_TBL(
837 SchTbl OUT NOCOPY MscIDmdSchVarTbl,
838 PurgeAll IN VARCHAR2,
839 ScenarioSetId IN NUMBER,
840 PlanId IN NUMBER
841 ) AS
842 cursor schedule_c(idPlan number, idScenarioSet number) is
843 SELECT
844 sr_instance_id,
845 organization_id,
846 input_schedule_id,
847 demand_variability_type,
848 probability
849 FROM
850 msc_plan_schedules
851 WHERE
852 plan_id = idPlan AND
853 organization_id <> -1 AND
854 scenario_set = idScenarioSet;
855
856 l_InsId NUMBER;
857 l_OrgId NUMBER;
858 l_SchedId NUMBER;
859 l_VarType NUMBER;
860 l_Probability NUMBER;
861 BEGIN
862 SchTbl := MscIDmdSchVarTbl();
863 SchTbl.DELETE;
864 IF MSC_WS_COMMON.BOOL_TO_NUMBER(PurgeAll) = MSC_UTIL.SYS_NO THEN
865 BEGIN
866 OPEN schedule_c(PlanId, ScenarioSetId);
867 LOOP
868 FETCH schedule_c into l_InsId, l_OrgId, l_SchedId, l_VarType, l_Probability;
869 EXIT WHEN schedule_c%NOTFOUND;
870 SchTbl.extend;
871 SchTbl(SchTbl.count) :=
872 MscIDmdSchVarRec(
873 l_InsId,
874 l_OrgId,
875 l_SchedId,
876 l_VarType,
877 l_Probability
878 );
879 END LOOP;
880 CLOSE schedule_c;
881 END;
882 END IF;
883
884 END LOAD_LOC_DMD_SCH_TBL;
885
886 -- =============================================================
887 -- Desc: All demand schedules in a scenario set have the same variability type
888 -- If the the variability type is probability, the sun must equal to 1 in any scenario set
889 -- Input:
890 -- InsId Instance id
891 --
892 -- Output: The possible return statuses are:
893 -- OK
894 -- INVALID_VAR_TYPE_IN_SCENARIO_SET
895 -- INVALID_LOC_SUM_OF_PROB
896 -- =============================================================
897 FUNCTION VALIDATE_L_PROB(
898 PurgeAll IN VARCHAR2,
899 InsId IN NUMBER,
900 PlanId IN NUMBER
901 ) RETURN VARCHAR2 AS
902 l_String VARCHAR2(100);
903 l_ScenarioTbl MscNumberArr; -- scenario sets in local demand schedules parameter
904 l_SchTbl MscIDmdSchVarTbl; -- local shedules for a single scenario set
905 l_Insert NUMBER;
906 l_VarType NUMBER;
907 l_Sum NUMBER;
908 BEGIN
909 IF g_ILocDmdSchTbl.COUNT > 0 THEN
910 l_ScenarioTbl := MscNumberArr();
911
912 -- insert all scenario set from parameter into l_ScenarioTbl
913 FOR I IN g_ILocDmdSchTbl.first..g_ILocDmdSchTbl.last
914 LOOP
915 l_Insert := MSC_UTIL.SYS_YES;
916 IF l_ScenarioTbl.COUNT > 0 THEN
917 FOR J IN l_ScenarioTbl.first..l_ScenarioTbl.last
918 LOOP
919 IF l_ScenarioTbl(J) = g_ILocDmdSchTbl(I).ScenarioSet THEN
920 l_Insert := MSC_UTIL.SYS_NO;
921 EXIT;
922 END IF;
923 END LOOP;
924 END IF;
925 IF l_Insert = MSC_UTIL.SYS_YES THEN
926 l_ScenarioTbl.extend;
927 l_ScenarioTbl(l_ScenarioTbl.COUNT) := g_ILocDmdSchTbl(I).ScenarioSet;
928 END IF;
929 END LOOP;
930
931 -- l_ScenarioTbl must not empty!!!
932 FOR I IN l_ScenarioTbl.first..l_ScenarioTbl.last
933 LOOP
934 -- get all schedules for this scenario set from database and store them in l_SchTbl
935 LOAD_LOC_DMD_SCH_TBL(l_SchTbl, PurgeAll, l_ScenarioTbl(I), PlanId);
936
937 l_VarType := -1;
938 l_Sum := 0;
939 FOR J in g_ILocDmdSchTbl.first..g_ILocDmdSchTbl.last
940 LOOP
941 IF g_ILocDmdSchTbl(J).ScenarioSet = l_ScenarioTbl(I) THEN
942 IF l_VarType = -1 THEN
943 l_VarType := g_ILocDmdSchTbl(J).DmdVariabilityType;
944 ELSIF l_VarType <> g_ILocDmdSchTbl(J).DmdVariabilityType THEN
945 RETURN 'INVALID_VAR_TYPE_IN_SCENARIO_SET';
946 END IF;
947 IF l_VarType = 2 THEN
948 l_Sum := l_Sum + g_ILocDmdSchTbl(J).Probability;
949 END IF;
950 END IF;
951 -- delete this demand schedule from l_SchTbl.
952 IF l_SchTbl.COUNT > 0 THEN
953 FOR K IN l_SchTbl.first..l_SchTbl.last
954 LOOP
955 BEGIN
956 IF l_SchTbl(K).InsId = InsId AND
957 l_SchTbl(K).OrgId = g_ILocDmdSchTbl(J).OrgId AND
958 l_SchTbl(K).SchId = g_ILocDmdSchTbl(J).DmdSchId THEN
959 l_SchTbl.delete(K);
960 EXIT;
961 END IF;
962 EXCEPTION WHEN NO_DATA_FOUND THEN
963 NULL; -- skip, this element is deleted.
964 END;
965 END LOOP;
966 END IF;
967 END LOOP;
968 IF l_SchTbl.COUNT > 0 THEN
969 FOR L IN l_SchTbl.first..l_SchTbl.last
970 LOOP
971 IF l_SchTbl(L).DmdVariabilityType <> l_VarType THEN
972 RETURN 'INVALID_VAR_TYPE_IN_SCENARIO_SET';
973 ELSIF l_VarType = 2 THEN
974 l_Sum := l_Sum + l_SchTbl(L).Probability;
975 END IF;
976 END LOOP;
977 END IF;
978 -- test the sum
979 IF l_VarType = 2 AND l_Sum <> 1 THEN
980 RETURN 'INVALID_LOC_SUM_OF_PROB';
981 END IF;
982 END LOOP;
983 END IF;
984 RETURN 'OK';
985 END VALIDATE_L_PROB;
986
987 -- =============================================================
988 -- Desc: Validate loal demand schedule id
989 -- Input:
990 -- SchId local demand schedule id.
991 -- OrgId organization id.
992 -- InsId sr instance id.
993 -- PlanName plan name.
994 --
995 -- Output: The possible return statuses are:
996 -- OK
997 -- INVALID_LOCALDMDSCHS_DMD_SCH_ID
998 -- =============================================================
999 FUNCTION VALIDATE_L_DMD_SCH_ID(
1000 DesigType OUT NOCOPY NUMBER,
1001 SchId IN NUMBER,
1002 OrgId IN NUMBER,
1003 InsId IN NUMBER,
1004 PlanName IN VARCHAR2
1005 ) RETURN VARCHAR2 AS
1006 BEGIN
1007 BEGIN
1008 SELECT designator_type
1009 INTO DesigType
1010 FROM msc_designators
1011 WHERE
1012 ((designator_type = 6 AND forecast_set_id IS NULL) OR
1013 (designator_type in (1,2,3,4,5,8)) ) AND
1014 trunc(nvl(disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
1015 (designator <> PlanName OR designator_type = 1) AND
1016 organization_id = OrgId AND
1017 sr_instance_id = InsId AND
1018 designator_id = SchId
1019 UNION
1020 SELECT 7
1021 FROM msd_dp_ascp_scenarios_v
1022 WHERE
1023 global_scenario_flag = 'N' AND
1024 scenario_name <> PlanName AND
1025 sr_instance_id = InsId AND
1026 (sr_instance_id = -23453 OR sr_instance_id = InsId) AND
1027 scenario_id = SchId
1028 UNION
1029 SELECT designator_type
1030 FROM
1031 msc_designators desig,
1032 msc_plan_organizations_v mpo
1033 WHERE
1034 ((desig.designator_type = 6 and desig.forecast_set_id IS NULL) OR
1035 (desig.designator_type IN (2,3,4,5,8) )) AND
1036 NVL(desig.disable_date, trunc(sysdate) + 1) > trunc(sysdate) AND
1037 mpo.organization_id = desig.organization_id AND
1038 mpo.sr_instance_id = desig.sr_instance_id AND
1039 mpo.compile_designator = desig.designator AND
1040 mpo.planned_organization = OrgId AND
1041 mpo.sr_instance_id = InsId AND
1042 desig.designator <> PlanName AND
1043 desig.designator_id = SchId
1044 UNION
1045 SELECT desig.designator_type
1046 FROM
1047 msc_designators desig,
1048 msc_item_sourcing mis,
1049 msc_plans mp
1050 WHERE
1051 ((desig.designator_type = 6 AND desig.forecast_set_id IS NULL) OR
1052 (desig.designator_type IN (2,3,4,5,8)) ) AND
1053 trunc(nvl(desig.disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
1054 mis.plan_id = mp.plan_id AND
1055 mp.organization_id = desig.organization_id AND
1056 mp.sr_instance_id = desig.sr_instance_id AND
1057 mp.compile_designator = desig.designator AND
1058 mis.source_organization_id = OrgId AND
1059 mis.sr_instance_id2 = InsId AND
1060 desig.designator <> PlanName AND
1061 desig.designator_id = SchId;
1062 EXCEPTION WHEN NO_DATA_FOUND THEN
1063 RETURN 'INVALID_LOCALDMDSCHS_DMD_SCH_ID';
1064 WHEN others THEN
1065 g_ErrorCode := 'ERROR_UNEXPECTED_00310';
1066 raise;
1067 END;
1068
1069 RETURN 'OK';
1070 END VALIDATE_L_DMD_SCH_ID;
1071
1072 -- =============================================================
1073 -- Desc: Validate demand variability type and its dependance
1074 -- Input:
1075 -- SchRec local demand schedule data.
1076 -- DesigType Designator type.
1077 --
1078 -- Output: The possible return statuses are:
1079 -- OK
1080 -- INVALID_LOCALDMDSCHS_VARIABILITY_TYPE
1081 -- INVALID_LOCBALDMDSCHS_PROBABILITY
1082 -- INVALID_LOCBALDMDSCHS_MEAN_ABS_PCT_ERROR
1083 -- =============================================================
1084 FUNCTION VALIDATE_L_VARIABILITY(
1085 SchRec IN MscLocIODmdSchRec,
1086 DesigType IN NUMBER
1087 ) RETURN VARCHAR2 AS
1088 l_String VARCHAR2(100);
1089 BEGIN
1090 l_String := VALIDATE_VARIABILITY_ID(SchRec.DmdVariabilityType, DesigType);
1091 IF l_String <> 'OK' THEN
1092 RETURN 'INVALID_LOCALDMDSCHS_VARIABILITY_TYPE';
1093 END IF;
1094 BEGIN
1095 CASE SchRec.DmdVariabilityType
1096 WHEN 1 THEN -- Accuracy_Metric_MAD
1097 BEGIN
1098 -- probability has to be null
1099 IF SchRec.Probability IS NOT NULL THEN
1100 RETURN 'INVALID_LOCBALDMDSCHS_PROBABILITY';
1101 END IF;
1102 -- mean absolute % error has to be null
1103 IF SchRec.MeanAbsPctError IS NOT NULL THEN
1104 RETURN 'INVALID_LOCBALDMDSCHS_MEAN_ABS_PCT_ERROR';
1105 END IF;
1106 END;
1107 WHEN 2 THEN -- 'Probability'
1108 -- probability is required and in the range of 0 to 1
1109 IF SchRec.Probability IS NULL THEN
1110 RETURN 'INVALID_LOCBALDMDSCHS_PROBABILITY';
1111 ELSE
1112 IF SchRec.Probability < 0 OR SchRec.Probability > 1 THEN
1113 RETURN 'INVALID_LOCBALDMDSCHS_PROBABILITY';
1114 END IF;
1115 END IF;
1116 -- mean absolute % error has to be null
1117 IF SchRec.MeanAbsPctError IS NOT NULL THEN
1118 RETURN 'INVALID_LOCBALDMDSCHS_MEAN_ABS_PCT_ERROR';
1119 END IF;
1120 WHEN 3 THEN -- 'Mean_Absolute_Pct_Err'
1121 -- probability has to be null
1122 IF SchRec.Probability IS NOT NULL THEN
1123 RETURN 'INVALID_LOCBALDMDSCHS_PROBABILITY';
1124 END IF;
1125 -- mean absolute % error is required and in the range of 0 to 100
1126 IF SchRec.MeanAbsPctError IS NULL THEN
1127 RETURN 'INVALID_LOCBALDMDSCHS_MEAN_ABS_PCT_ERROR';
1128 ELSE
1129 IF SchRec.MeanAbsPctError < 0 OR SchRec.MeanAbsPctError > 100 THEN
1130 RETURN 'INVALID_LOCBALDMDSCHS_MEAN_ABS_PCT_ERROR';
1131 END IF;
1132 END IF;
1133 END CASE;
1134 END;
1135
1136 RETURN 'OK';
1137 END VALIDATE_L_VARIABILITY;
1138
1139 -- =============================================================
1140 -- Desc: validate local demand schedules
1141 -- Input:
1142 -- SchTable Local demand schedules.
1143 -- PlanId Plan id
1144 -- PlanName Plan name.
1145 -- InsId Instance id
1146 -- PurgeAll Purge All Schs Flag
1147 --
1148 -- Output: The possible return statuses are:
1149 -- OK
1150 -- INVALID_LOCALDMDSCHS_ORGID
1151 -- INVALID_LOCALDMDSCHS_SCENARIO_SET
1152 -- INVALID_LOCALDMDSCHS_DMD_SCH_ID
1153 -- INVALID_LOCALDMDSCHS_SHP_TO_CONSUMPTION_LVL
1154 -- =============================================================
1155 FUNCTION VALIDATE_LOC_DMD_SCHS(
1156 SchTable IN MscLocIODmdSchTbl,
1157 PlanId IN NUMBER,
1158 PlanName IN VARCHAR2,
1159 InsId IN NUMBER,
1160 PurgeAll IN VARCHAR2
1161 ) RETURN VARCHAR2 AS
1162 l_ReturnString VARCHAR2(100);
1163 l_OrgInsId NUMBER;
1164 l_DesigType NUMBER;
1165 BEGIN
1166 IF SchTable IS NOT NULL AND SchTable.count > 0 THEN
1167 FOR I IN SchTable.first..SchTable.last
1168 LOOP
1169 -- validate organization id
1170 BEGIN
1171 l_ReturnString := MSC_WS_COMMON.PLAN_CONTAINS_THIS_ORG(l_OrgInsId, SchTable(I).OrgId, PlanId);
1172 IF (l_ReturnString <> 'OK') THEN
1173 -- overwrite the error token here.
1174 l_ReturnString := 'INVALID_LOCALDMDSCHS_ORGID';
1175 RETURN l_ReturnString;
1176 END IF;
1177 EXCEPTION WHEN others THEN
1178 g_ErrorCode := 'ERROR_UNEXPECTED_00309';
1179 raise;
1180 END;
1181
1182 -- validate scenario set
1183 IF SchTable(I).ScenarioSet IS NULL THEN
1184 RETURN 'INVALID_LOCALDMDSCHS_SCENARIO_SET';
1185 ELSIF SchTable(I).ScenarioSet < 0 OR SchTable(I).ScenarioSet > 9999 THEN
1186 RETURN 'INVALID_LOCALDMDSCHS_SCENARIO_SET';
1187 END IF;
1188
1189 -- validate demand schedule id
1190 l_ReturnString := VALIDATE_L_DMD_SCH_ID(
1191 l_DesigType,
1192 SchTable(I).DmdSchId,
1193 SchTable(I).OrgId,
1194 l_OrgInsId,
1195 PlanName);
1196 IF (l_ReturnString <> 'OK') THEN
1197 RETURN l_ReturnString;
1198 END IF;
1199
1200 -- validate ship to consumption level
1201 IF l_DesigType = 7 THEN
1202 l_ReturnString := VALIDATE_CONSUM_LVL(
1203 SchTable(I).ShipToConsumptionLvl,
1204 SchTable(I).DmdSchId);
1205 IF (l_ReturnString <> 'OK') THEN
1206 RETURN 'INVALID_LOCALDMDSCHS_SHP_TO_CONSUMPTION_LVL';
1207 END IF;
1208 ELSE
1209 IF SchTable(I).ShipToConsumptionLvl IS NOT NULL THEN
1210 RETURN 'INVALID_LOCALDMDSCHS_SHP_TO_CONSUMPTION_LVL';
1211 END IF;
1212 END IF;
1213
1214 -- validate Type of Demand Variability and its dependence
1215 l_ReturnString := VALIDATE_L_VARIABILITY(SchTable(I), l_DesigType);
1216 IF (l_ReturnString <> 'OK') THEN
1217 RETURN l_ReturnString;
1218 END IF;
1219
1220 g_ILocDmdSchTbl.extend;
1221 g_ILocDmdSchTbl(g_ILocDmdSchTbl.count) :=
1222 MscILocIODmdSchRec(SchTable(I).OrgId,
1223 SchTable(I).ScenarioSet,
1224 SchTable(I).DmdSchId,
1225 SchTable(I).ShipToConsumptionLvl,
1226 SchTable(I).DmdVariabilityType,
1227 SchTable(I).Probability,
1228 SchTable(I).MeanAbsPctError,
1229 1, -- input_type
1230 l_DesigType -- designator_type
1231 );
1232 END LOOP;
1233 l_ReturnString := VALIDATE_L_PROB(PurgeAll, InsId, PlanId);
1234 IF (l_ReturnString <> 'OK') THEN
1235 RETURN l_ReturnString;
1236 END IF;
1237 END IF;
1238
1239 RETURN 'OK';
1240 END VALIDATE_LOC_DMD_SCHS;
1241
1242 -- =============================================================
1243 -- Desc: update item simulation set and service level set
1244 --
1245 -- Input:
1246 -- PlanId Id of the plan.
1247 -- ItemSimulationSetId Id of the item simulation set.
1248 -- SvcLvlSet Service level set.
1249 --
1250 -- Output: No output.
1251 -- =============================================================
1252 PROCEDURE UPDATE_PLAN_OPTIONS(
1253 PlanId IN NUMBER,
1254 ItemSimulationSetId IN NUMBER,
1255 SvcLvlSetId IN NUMBER
1256 ) AS
1257 BEGIN
1258 BEGIN
1259 UPDATE msc_plans
1260 SET
1261 item_simulation_set_id = ItemSimulationSetId,
1262 curr_service_level_set_id = SvcLvlSetId
1263 WHERE
1264 plan_id = PlanId;
1265 EXCEPTION WHEN others THEN
1266 g_ErrorCode := 'ERROR_UNEXPECTED_00312';
1267 raise;
1268 END;
1269
1270 END UPDATE_PLAN_OPTIONS;
1271
1272 -- =============================================================
1273 -- Desc: Insert a global demand schedule
1274 --
1275 -- Input:
1276 -- PlanId Id of the plan.
1277 -- InsId Sr instance id.
1278 -- UserId user id,
1279 -- Schrec global demand schedule data.
1280 --
1281 -- Output: The possible return statuses are:
1282 -- OK
1283 -- ERROR_DUP_GLOBALDMDSCH
1284 -- =============================================================
1285 FUNCTION INSERT_GLB_DMD_SCHEDULE(
1286 PlanId IN NUMBER,
1287 InsId IN NUMBER,
1288 UserId IN NUMBER,
1289 SchRec IN MscIGlbIODmdSchRec
1290 ) RETURN VARCHAR2 AS
1291 BEGIN
1292 BEGIN
1293 INSERT INTO msc_plan_schedules
1294 (
1295 plan_id, organization_id, input_schedule_id, sr_instance_id,
1296 input_type, last_update_date, last_updated_by,
1297 creation_date, created_by, designator_type, ship_to,
1298 demand_variability_type, probability, mape_value
1299 )
1300 VALUES
1301 (
1302 PlanId, -1, SchRec.DmdSchId, InsId,
1303 SchRec.input_type, sysdate, UserId,
1304 sysdate, UserId, SchRec.designator_type, SchRec.ShipToConsumptionLvl,
1305 SchRec.DmdVariabilityType, SchRec.Probability, SchRec.MeanAbsPctError
1306 );
1307 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
1308 RETURN 'ERROR_DUP_GLOBALDMDSCH';
1309 WHEN others THEN
1310 g_ErrorCode := 'ERROR_UNEXPECTED_00313';
1311 raise;
1312 END;
1313
1314 RETURN 'OK';
1315 END INSERT_GLB_DMD_SCHEDULE;
1316
1317
1318 -- =============================================================
1319 -- Desc: Insert a locaal demand schedule
1320 --
1321 -- Input:
1322 -- PlanId Id of the plan.
1323 -- InsId Sr instance id.
1324 -- UserId user id,
1325 -- Schrec local demand schedule data.
1326 --
1327 -- Output: The possible return statuses are:
1328 -- OK
1329 -- ERROR_DUP_LOCBALDMDSCH
1330 -- =============================================================
1331 FUNCTION INSERT_LOC_DMD_SCHEDULE(
1332 PlanId IN NUMBER,
1333 InsId IN NUMBER,
1334 UserId IN NUMBER,
1335 SchRec IN MscILocIODmdSchRec
1336 ) RETURN VARCHAR2 AS
1337 BEGIN
1338 BEGIN
1339 INSERT INTO msc_plan_schedules
1340 (
1341 plan_id, organization_id, input_schedule_id, sr_instance_id,
1342 input_type, last_update_date, last_updated_by,
1343 creation_date, created_by, designator_type, ship_to,
1344 demand_variability_type, probability, mape_value,
1345 scenario_set, include_target_demands -- include_target_demands is hard coded to 2
1346 )
1347 VALUES
1348 (
1349 PlanId, SchRec.OrgId, SchRec.DmdSchId, InsId,
1350 SchRec.input_type, sysdate, UserId,
1351 sysdate, UserId, SchRec.designator_type, SchRec.ShipToConsumptionLvl,
1352 SchRec.DmdVariabilityType, SchRec.Probability, SchRec.MeanAbsPctError,
1353 SchRec.ScenarioSet, 2
1354 );
1355 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
1356 RETURN 'ERROR_DUP_LOCBALDMDSCH';
1357 WHEN others THEN
1358 g_ErrorCode := 'ERROR_UNEXPECTED_00314';
1359 raise;
1360 END;
1361
1362 RETURN 'OK';
1363 END INSERT_LOC_DMD_SCHEDULE;
1364
1365 -- =============================================================
1366 -- Desc: this function is call when the PurgeAllSchsFlag is set,
1367 -- insert all global and local demand schedules.
1368 --
1369 -- Input:
1370 -- PlanId Id of the plan.
1371 -- InsId Sr instance id.
1372 -- UserId user id,
1373 --
1374 -- Output: No output.
1375 -- =============================================================
1376 FUNCTION INSERT_ALL_SCHEDULES(
1377 PlanId IN NUMBER,
1378 InsId IN NUMBER,
1379 UserId IN NUMBER
1380 ) RETURN VARCHAR2 AS
1381 l_ReturnString VARCHAR2(100);
1382 BEGIN
1383 -- insert global demand schedules
1384 IF g_IGlbDmdSchTbl.COUNT > 0 THEN
1385 FOR I IN g_IGlbDmdSchTbl.first..g_IGlbDmdSchTbl.last
1386 LOOP
1387 l_ReturnString:= INSERT_GLB_DMD_SCHEDULE(PlanId, InsId, UserId, g_IGlbDmdSchTbl(I));
1388 IF (l_ReturnString <> 'OK') THEN
1389 RETURN l_ReturnString;
1390 END IF;
1391 END LOOP;
1392 END IF;
1393
1394 -- insert local demand schedules
1395 IF g_ILocDmdSchTbl.COUNT > 0 THEN
1396 FOR I IN g_ILocDmdSchTbl.first..g_ILocDmdSchTbl.last
1397 LOOP
1398 l_ReturnString:= INSERT_LOC_DMD_SCHEDULE(PlanId, InsId, UserId, g_ILocDmdSchTbl(I));
1399 IF (l_ReturnString <> 'OK') THEN
1400 RETURN l_ReturnString;
1401 END IF;
1402 END LOOP;
1403 END IF;
1404
1405 RETURN 'OK';
1406 END INSERT_ALL_SCHEDULES;
1407
1408 -- =============================================================
1409 -- Desc: Update a global demand schedule
1410 --
1411 -- Input:
1412 -- PlanId Id of the plan.
1413 -- InsId Sr instance id.
1414 -- UserId user id,
1415 -- Schrec global demand schedule data.
1416 --
1417 -- Output: No output.
1418 -- =============================================================
1419 PROCEDURE UPDATE_GLB_DMD_SCHEDULE(
1420 PlanId IN NUMBER,
1421 InsId IN NUMBER,
1422 UserId IN NUMBER,
1423 SchRec IN MscIGlbIODmdSchRec
1424 ) AS
1425 BEGIN
1426 BEGIN
1427 UPDATE msc_plan_schedules
1428 SET
1429 ship_to = SchRec.ShipToConsumptionLvl,
1430 demand_variability_type = SchRec.DmdVariabilityType,
1431 probability = SchRec.Probability,
1432 mape_value = SchRec.MeanAbsPctError
1433 WHERE
1434 plan_id = PlanId AND
1435 organization_id = -1 AND
1436 sr_instance_id = InsId AND
1437 input_schedule_id = SchRec.DmdSchId;
1438 EXCEPTION WHEN others THEN
1439 g_ErrorCode := 'ERROR_UNEXPECTED_00316';
1440 raise;
1441 END;
1442
1443 END UPDATE_GLB_DMD_SCHEDULE;
1444
1445 -- =============================================================
1446 -- Desc: Update a local demand schedule
1447 --
1448 -- Input:
1449 -- PlanId Id of the plan.
1450 -- InsId Sr instance id.
1451 -- UserId user id,
1452 -- Schrec local demand schedule data.
1453 --
1454 -- Output: No output.
1455 -- =============================================================
1456 PROCEDURE UPDATE_LOC_DMD_SCHEDULE(
1457 PlanId IN NUMBER,
1458 InsId IN NUMBER,
1459 UserId IN NUMBER,
1460 SchRec IN MscILocIODmdSchRec
1461 ) AS
1462 BEGIN
1463 BEGIN
1464 UPDATE msc_plan_schedules
1465 SET
1466 ship_to = SchRec.ShipToConsumptionLvl,
1467 demand_variability_type = SchRec.DmdVariabilityType,
1468 probability = SchRec.Probability,
1469 mape_value = SchRec.MeanAbsPctError,
1470 scenario_set = SchRec.ScenarioSet
1471 WHERE
1472 plan_id = PlanId AND
1473 organization_id = SchRec.OrgId AND
1474 sr_instance_id = InsId AND
1475 input_schedule_id = SchRec.DmdSchId;
1476 EXCEPTION WHEN others THEN
1477 g_ErrorCode := 'ERROR_UNEXPECTED_00318';
1478 raise;
1479 END;
1480
1481 END UPDATE_LOC_DMD_SCHEDULE;
1482
1483 -- =============================================================
1484 -- Desc: this function is call when the PurgeAllSchsFlag is not set,
1485 -- insert or update bothe global and local demand schedules.
1486 --
1487 -- Input:
1488 -- PlanId Id of the plan.
1489 -- InsId Sr instance id.
1490 -- UserId user id,
1491 --
1492 -- Output: No output.
1493 -- =============================================================
1494 FUNCTION INSERT_OR_UPDATE_ALL_SCHS(
1495 PlanId IN NUMBER,
1496 InsId IN NUMBER,
1497 UserId IN NUMBER
1498 ) RETURN VARCHAR2 AS
1499 l_ReturnString VARCHAR2(100);
1500 l_Dummy NUMBER;
1501 BEGIN
1502 -- insert/update all global demand schedules
1503 IF g_IGlbDmdSchTbl.COUNT > 0 THEN
1504 FOR I IN g_IGlbDmdSchTbl.FIRST..g_IGlbDmdSchTbl.LAST
1505 LOOP
1506 BEGIN
1507 Select count(*) INTO l_Dummy
1508 FROM msc_plan_schedules
1509 WHERE
1510 plan_id = PlanId AND
1511 organization_id = -1 AND
1512 sr_instance_id = InsId AND
1513 input_schedule_id = g_IGlbDmdSchTbl(I).DmdSchId;
1514 EXCEPTION WHEN others THEN
1515 g_ErrorCode := 'ERROR_UNEXPECTED_00315';
1516 raise;
1517 END;
1518 IF l_Dummy = 0 THEN
1519 l_ReturnString := INSERT_GLB_DMD_SCHEDULE(PlanId, InsId, UserId, g_IGlbDmdSchTbl(I));
1520 ELSE
1521 UPDATE_GLB_DMD_SCHEDULE(PlanId, InsId, UserId, g_IGlbDmdSchTbl(I));
1522 END IF;
1523 END LOOP;
1524 END IF;
1525
1526 -- insert/update all local demand schedules
1527 IF g_ILocDmdSchTbl.COUNT > 0 THEN
1528 FOR I IN g_ILocDmdSchTbl.first..g_ILocDmdSchTbl.last
1529 LOOP
1530 BEGIN
1531 Select count(*) INTO l_Dummy
1532 FROM msc_plan_schedules
1533 WHERE
1534 plan_id = PlanId AND
1535 organization_id = g_ILocDmdSchTbl(I).OrgId AND
1536 sr_instance_id = InsId AND
1537 input_schedule_id = g_ILocDmdSchTbl(I).DmdSchId;
1538 EXCEPTION WHEN others THEN
1539 g_ErrorCode := 'ERROR_UNEXPECTED_7';
1540 raise;
1541 END;
1542 IF l_Dummy = 0 THEN
1543 l_ReturnString := INSERT_LOC_DMD_SCHEDULE(PlanId, InsId, UserId, g_ILocDmdSchTbl(I));
1544 IF (l_ReturnString <> 'OK') THEN
1545 RETURN l_ReturnString;
1546 END IF;
1547 ELSE
1548 UPDATE_LOC_DMD_SCHEDULE(PlanId, InsId, UserId, g_ILocDmdSchTbl(I));
1549 IF (l_ReturnString <> 'OK') THEN
1550 RETURN l_ReturnString;
1551 END IF;
1552 END IF;
1553 END LOOP;
1554 END IF;
1555
1556
1557 RETURN 'OK';
1558 END INSERT_OR_UPDATE_ALL_SCHS;
1559
1560 -- =============================================================
1561 -- Desc: This procedure is invoked from web service to
1562 -- updates Plan Options for IO plans.
1563 -- Input:
1564 -- UserId User ID.
1565 -- ResponsibilityId Responsibility Id.
1566 -- PlanId Plan Id.
1567 -- ItemSimulationSet Item Simulation Set.
1568 -- ServiceLvlSetId Service Level Set Id.
1569 -- PurgeAllSchsFlag There is no such parameter in UI. Allowed
1570 -- input is Y or N. This is a new parameter
1571 -- to control how Global and local Demand Schedules
1572 -- are updated / inserted. If this flag is set, all
1573 -- Global and Local Demand will be purged before
1574 -- update / insert from the input parameters.
1575 -- If this flag is not set, no global or local demand
1576 -- schedules will be purged, schedules in the input
1577 -- parameters will be updated or inserted.
1578 -- GlobalDmdSchs Global Demand Schedules. Each global demand schedule
1579 -- contains the schedule id, ship to consumption,
1580 -- demand variability type, probability and/or
1581 -- mean absolue % error parameters.
1582 -- LocalDmdSchs Local Demand Schedules. Each local demand schedule
1583 -- contains the organization id, schedule id, scenario set id,
1584 -- ship to consumption, demand variability type, probability and/or
1585 -- mean absolue % error parameters.
1586 --
1587 -- Output: Procedure returns a status and conc program req id.
1588 -- The possible return statuses are:
1589 -- SUCCESS if everything is ok
1590 -- ERROR_DUP_GLOBALDMDSCH
1591 -- ERROR_DUP_LOCBALDMDSCH
1592 -- ERROR_UNEXPECTED_##### unexpected error
1593 -- INVALID_FND_USERID
1594 -- INVALID_FND_RESPONSIBILITYID
1595 -- INVALID_PLANID invalid source plan id
1596 -- INVALID_PLAN_TYPE non IO plan
1597 -- INVALID_SIMULATION_SET_ID
1598 -- INVALID_SERVICE_LVL_SET_ID
1599 -- INVALID_GLOBALDMDSCHS_DMD_SCH_ID
1600 -- INVALID_GLOBALDMDSCHS_SHP_TO_CONSUMPTION_LVL
1601 -- INVALID_GLOBALDMDSCHS_VARIABILITY_TYPE
1602 -- INVALID_GLOBALDMDSCHS_PROBABILITY
1603 -- INVALID_GLOBALDMDSCHS_MEAN_ABS_PCT_ERROR
1604 -- INVALID_VAR_TYPE_IN_GBL_SCH
1605 -- INVALID_GLB_SUM_OF_PROB
1606 -- INVALID_LOCALDMDSCHS_ORGID
1607 -- INVALID_LOCALDMDSCHS_SCENARIO_SET
1608 -- INVALID_LOCALDMDSCHS_DMD_SCH_ID
1609 -- INVALID_LOCALDMDSCHS_SHP_TO_CONSUMPTION_LVL
1610 -- INVALID_LOCALDMDSCHS_VARIABILITY_TYPE
1611 -- INVALID_LOCBALDMDSCHS_PROBABILITY
1612 -- INVALID_LOCBALDMDSCHS_MEAN_ABS_PCT_ERROR
1613 -- INVALID_VAR_TYPE_IN_SCENARIO_SET
1614 -- INVALID_LOC_SUM_OF_PROB
1615 -- =============================================================
1616 PROCEDURE SET_IO_PLAN_OPTIONS(
1617 Status OUT NOCOPY VARCHAR2,
1618 UserId IN NUMBER,
1619 ResponsibilityId IN NUMBER,
1620 PlanId IN NUMBER,
1621 ItemSimulationSetId IN NUMBER default NULL,
1622 ServiceLvlSetId IN NUMBER default NULL,
1623 PurgeAllSchsFlag IN VARCHAR2,
1624 GlobalDmdSchs IN MscGlbIODmdSchTbl default NULL,
1625 LocalDmdSchs IN MscLocIODmdSchTbl default NULL
1626 ) IS
1627 l_String VARCHAR2(100);
1628 l_OrgId NUMBER;
1629 l_InsId NUMBER;
1630 l_PlanName VARCHAR2(10);
1631 BEGIN
1632 -- dbms_output.put_line('Matthew: Init');
1633
1634 -- init global variables
1635 g_IGlbDmdSchTbl := MscIGlbIODmdSchTbl();
1636 g_ILocDmdSchTbl := MscILocIODmdSchTbl();
1637
1638 -- check user id and responsibility
1639 MSC_WS_COMMON.VALIDATE_USER_RESP(l_String, UserId, ResponsibilityId);
1640 IF (l_String <> 'OK') THEN
1641 Status := l_String;
1642 RETURN;
1643 END IF;
1644
1645 -- check plan id
1646 -- l_String := MSC_WS_COMMON.VALIDATE_PLAN_ID(l_OrgId, l_InsId, l_PlanName, PlanId);
1647 l_String := VALIDATE_PLAN_ID(l_OrgId, l_InsId, l_PlanName, PlanId);
1648 IF (l_String <> 'OK') THEN
1649 Status := l_String;
1650 RETURN;
1651 END IF;
1652
1653 -- check plan type
1654 l_String := VALIDATE_PLAN_TYPE(PlanId);
1655 IF (l_String <> 'OK') THEN
1656 Status := l_String;
1657 RETURN;
1658 END IF;
1659
1660
1661 -- validate item simulation set id
1662 BEGIN
1663 l_String := MSC_WS_COMMON.VALIDATE_SIMULATION_SET_ID(ItemSimulationSetId);
1664 IF (l_String <> 'OK') THEN
1665 Status := l_String;
1666 RETURN;
1667 END IF;
1668 EXCEPTION WHEN others THEN
1669 g_ErrorCode := 'ERROR_UNEXPECTED_00303';
1670 raise;
1671 END;
1672
1673 -- validate service level set id
1674 l_String := VALIDATE_SER_LVL_SET_ID(ServiceLvlSetId);
1675 IF (l_String <> 'OK') THEN
1676 Status := l_String;
1677 RETURN;
1678 END IF;
1679
1680
1681 -- validate global demand schedules
1682 l_String := VALIDATE_GLB_DMD_SCHS(GlobalDmdSchs, PlanId, l_PlanName, l_InsId, PurgeAllSchsFlag);
1683 IF (l_String <> 'OK') THEN
1684 Status := l_String;
1685 RETURN;
1686 END IF;
1687
1688 -- validate local demand schedules
1689 l_String := VALIDATE_LOC_DMD_SCHS(LocalDmdSchs, PlanId, l_PlanName, l_InsId, PurgeAllSchsFlag);
1690 IF (l_String <> 'OK') THEN
1691 Status := l_String;
1692 RETURN;
1693 END IF;
1694
1695 -- if PurgeAllSchsFlag is set, purge all global demand schedules,
1696 -- local demand schedule and local supply schedules
1697 IF MSC_WS_COMMON.BOOL_TO_NUMBER(PurgeAllSchsFlag) = MSC_UTIL.SYS_YES THEN
1698 BEGIN
1699 MSC_WS_COMMON.PURGE_ALL_SCHEDULES(PlanId);
1700 EXCEPTION WHEN others THEN
1701 g_ErrorCode := 'ERROR_UNEXPECTED_00011';
1702 raise;
1703 END;
1704 END IF;
1705
1706 -- update item simulation set and overwrite
1707 UPDATE_PLAN_OPTIONS(PlanId, ItemSimulationSetId, ServiceLvlSetId);
1708
1709 -- set all global/local demand/supply schedules
1710 IF MSC_WS_COMMON.BOOL_TO_NUMBER(PurgeAllSchsFlag) = MSC_UTIL.SYS_YES THEN
1711 l_String := INSERT_ALL_SCHEDULES(PlanId, l_InsId, UserId);
1712 ELSE
1713 l_String := INSERT_OR_UPDATE_ALL_SCHS(PlanId, l_InsId, UserId);
1714 END IF;
1715
1716 IF (l_String <> 'OK') THEN
1717 Status := l_String;
1718 RETURN;
1719 ELSE
1720 Status := 'SUCCESS';
1721 END IF;
1722
1723 COMMIT;
1724
1725 EXCEPTION
1726 WHEN others THEN
1727 -- Status := 'Failed '||fnd_message.get;
1728 Status := g_ErrorCode;
1729 ROLLBACK;
1730
1731 END SET_IO_PLAN_OPTIONS;
1732
1733
1734 PROCEDURE SET_IO_PLAN_OPTIONS_PUBLIC (
1735
1736 status OUT NOCOPY VARCHAR2,
1737 UserName IN VARCHAR2,
1738 RespName IN VARCHAR2,
1739 RespApplName IN VARCHAR2,
1740 SecurityGroupName IN VARCHAR2,
1741 Language IN VARCHAR2,
1742 PlanId IN NUMBER,
1743 ItemSimulationSetId IN NUMBER default NULL,
1744 ServiceLvlSetId IN NUMBER default NULL,
1745 PurgeAllSchsFlag IN VARCHAR2,
1746 GlobalDmdSchs IN MscGlbIODmdSchTbl default NULL,
1747 LocalDmdSchs IN MscLocIODmdSchTbl default NULL
1748
1749 ) AS
1750 userid number;
1751 respid number;
1752 l_String VARCHAR2(30);
1753 error_tracking_num number;
1754 l_SecutirtGroupId NUMBER;
1755 BEGIN
1756 error_tracking_num :=2010;
1757 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
1758 IF (l_String <> 'OK') THEN
1759 Status := l_String;
1760 RETURN;
1761 END IF;
1762
1763 error_tracking_num :=2030;
1764 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSCFPPMR-SRO',l_SecutirtGroupId);
1765 IF (l_String <> 'OK') THEN
1766 Status := l_String;
1767 RETURN;
1768 END IF;
1769 error_tracking_num :=2040;
1770
1771 SET_IO_PLAN_OPTIONS ( Status, userId , respid, PlanId, ItemSimulationSetId, ServiceLvlSetId, PurgeAllSchsFlag, GlobalDmdSchs, LocalDmdSchs );
1772 -- dbms_output.put_line('USERID=' || userid);
1773
1774
1775 EXCEPTION
1776 WHEN others THEN
1777 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
1778
1779 return;
1780 END SET_IO_PLAN_OPTIONS_PUBLIC;
1781
1782 END MSC_WS_IO;
1783
[Home] [Help]