1 PACKAGE BODY MSC_WS_ASCP AS
2 /* $Header: MSCWASCB.pls 120.10 2008/03/20 15:55:15 bnaghi noship $ */
3
4 g_IGlbDmdSchTbl MscIGlbDmdSchTbl; -- store all global demand schediles data
5 g_ILocDmdSchTbl MscILocDmdSchTbl; -- store all local demand schediles data
6 g_ILocSupSchTbl MscILocSupSchTbl; -- store all local supply schediles data
7 g_ErrorCode VARCHAR2(9);
8
9
10 -- =============================================================
11 -- Desc: Please see package spec for description
12 -- =============================================================
13 PROCEDURE LAUNCH_ASCP_BATCH (
14 processId OUT NOCOPY NUMBER,
15 status OUT NOCOPY VARCHAR2,
16 userId IN NUMBER,
17 responsibilityId IN NUMBER,
18 planId IN NUMBER,
19 launchSnapshot IN VARCHAR2,
20 launchPlanner IN VARCHAR2,
21 anchorDate IN DATE,
22 archiveCurrVersPlan IN VARCHAR2,
23 enable24x7Atp IN VARCHAR2,
24 releaseReschedules IN VARCHAR2,
25 snapStaticEntities IN VARCHAR2
26 ) AS
27 l_val_result VARCHAR2(30);
28 l_val_planname VARCHAR2(10);
29 l_val_launchsnapshot NUMBER;
30 l_val_launchplanner NUMBER;
31 l_val_archivePlan NUMBER;
32 l_val_netchange NUMBER;
33 l_val_anchordate DATE;
34 l_val_inventory_atp_flag NUMBER;
35 l_val_enable24x7atp NUMBER;
36 l_val_production NUMBER;
37 l_val_releasereschedules NUMBER;
38 l_val_snapstaticentities NUMBER;
39 l_val_orgid NUMBER;
40 l_val_instanceid NUMBER;
41 error_tracking_num NUMBER;
42
43 BEGIN
44 error_tracking_num := 1010;
45
46 -- validate and initialize apps
47 msc_ws_common.validate_user_resp(l_val_result, userid, responsibilityid);
48
49 IF(l_val_result <> 'OK') THEN
50 processid := -1;
51 status := l_val_result;
52 RETURN;
53 END IF;
54
55 error_tracking_num := 1020;
56
57 -- validate planId
58 BEGIN
59 SELECT plans.compile_designator, plans.organization_id, plans.sr_instance_id,
60 desig.inventory_atp_flag, desig.production
61 INTO l_val_planname,l_val_orgid,l_val_instanceid,l_val_inventory_atp_flag,
62 l_val_production
63 FROM msc_plans plans, msc_designators desig
64 WHERE plans.curr_plan_type in (1,2,3)
65 AND plans.organization_id = desig.organization_id
66 AND plans.sr_instance_id = desig.sr_instance_id
67 AND plans.compile_designator = desig.designator
68 AND NVL(desig.disable_date, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
69 AND plans.organization_selection <> 1
70 AND NVL(plans.copy_plan_id,-1) = -1
71 AND NVL(desig.copy_designator_id, -1) = -1
72 AND plans.plan_id = planId;
73 EXCEPTION
74 WHEN no_data_found THEN
75 processid := -1;
76 status := 'INVALID_PLANID';
77 RETURN;
78 END;
79
80
81 error_tracking_num := 1030;
82 -- validate anchor date
83 BEGIN
84 SELECT calendar_date
85 INTO l_val_anchordate
86 FROM msc_calendar_dates dates,
87 msc_trading_partners mtp
88 WHERE dates.calendar_code = mtp.calendar_code
89 AND dates.exception_set_id = mtp.calendar_exception_set_id
90 AND mtp.sr_instance_id = dates.sr_instance_id
91 AND mtp.sr_tp_id = l_val_orgid
92 AND mtp.sr_instance_id = l_val_instanceid
93 AND dates.calendar_date <= TRUNC(sysdate)
94 AND dates.calendar_date = anchordate;
95
96 EXCEPTION
97 WHEN no_data_found THEN
98 processid := -1;
99 status := 'INVALID_ANCHORDATE';
100 RETURN;
101 END;
102
103 error_tracking_num := 1040;
104 -- validate launchSnapshot
105 BEGIN
106 SELECT lookup_code
107 INTO l_val_launchsnapshot
108 FROM mfg_lookups
109 WHERE lookup_type = 'MSC_LAUNCH_SNAPSHOT'
110 AND lookup_code in decode(lookup_code,1,1,2,2,3,
111 decode((select count(*) from msc_plan_schedules
112 where plan_id = planId
113 and rownum = 1
114 and designator_type = 7
115 and input_type = 1),0,1,3) )
116 AND lookup_code = decode(launchsnapshot,'FULL',1,'NO',2,'DP_ONLY',3,-1);
117
118 EXCEPTION
119 WHEN no_data_found THEN
120 processid := -1;
121 status := 'INVALID_LAUNCH_SNAPSHOT';
122 RETURN;
123 END;
124
125
126 error_tracking_num := 1050;
127 BEGIN
128 SELECT lookup_code
129 INTO l_val_launchplanner
130 FROM MFG_LOOKUPS
131 WHERE lookup_type = 'SYS_YES_NO'
132 AND ((lookup_code = 1 AND l_val_launchsnapshot in (1,3,4)) OR
133 (l_val_launchsnapshot = 2))
134 AND lookup_code = decode(launchplanner, 'Y', msc_ws_common.sys_yes, msc_ws_common.sys_no);
135 EXCEPTION
136 WHEN no_data_found THEN
137 processid := -1;
138 status := 'INVALID_LAUNCH_PLANNER';
139 RETURN;
140 END;
141
142 error_tracking_num := 1055;
143 BEGIN
144 SELECT decode(archiveCurrVersPlan, 'Y', msc_ws_common.sys_yes, msc_ws_common.sys_no)
145 INTO l_val_archivePlan
146 FROM dual;
147
148 END;
149
150 -- netchange hidden parameter always set to the value 2, which is sys_no
151 l_val_netchange := msc_ws_common.sys_no;
152
153
154 -- populating PLAN_TYPE_DUMMY hidden parameter
155 -- Original default logic is "SELECT inventory_atp_flag from msc_designators d where
156 -- d.designator = (SELECT compile_designator from msc_plans p where
157 -- p.plan_id=:$FLEX$.MSC_SRS_SCP_NAME_LAUNCH) and d.inventory_atp_flag = 1"
158 -- Which pretty much meant this flag is set to either NULL or 1
159 IF (l_val_inventory_atp_flag <> 1)
160 THEN l_val_inventory_atp_flag := NULL;
161 END IF;
162
163
164 error_tracking_num := 1060;
165 -- validating enable24x7atp
166 BEGIN
167 SELECT lookup_code
168 INTO l_val_enable24x7atp
169 FROM MFG_LOOKUPS
170 WHERE LOOKUP_TYPE = 'MSC_24X7_PURGE'
171 AND (( LOOKUP_CODE IN (1,2,3) and NVL(l_val_inventory_atp_flag,2) = 1 )
172 OR LOOKUP_CODE=2)
173 AND LOOKUP_CODE = decode(enable24x7atp,'YES_PURGE',1,'NO',2,'YES_NO_PURGE',3,-1);
174
175 EXCEPTION
176 WHEN no_data_found THEN
177 processid := -1;
178 status := 'INVALID_ENABLE24X7ATP';
179 RETURN;
180 END;
181
182
183 -- populating RESCHEDULE_DUMMY hidden parameter
184 -- Similar default logic as l_val_inventory_atp_flag, "SELECT production from msc_designators d
185 -- where d.designator = (SELECT compile_designator from msc_plans p where p.plan_id =
186 -- :$FLEX$.MSC_SRS_SCP_NAME_LAUNCH) and d.production = 1"
187 IF (l_val_production <> 1)
188 THEN l_val_production := NULL;
189 END IF;
190
191 error_tracking_num := 1070;
192 -- validating releasereschedules
193 BEGIN
194 SELECT lookup_code
195 INTO l_val_releasereschedules
196 FROM MFG_LOOKUPS
197 WHERE lookup_type='SYS_YES_NO'
198 AND (NVL(l_val_production,2)=1 or lookup_code=2)
199 AND lookup_code = decode(releasereschedules,'Y',msc_ws_common.sys_yes, msc_ws_common.sys_no);
200 EXCEPTION
201 WHEN no_data_found THEN
202 processid := -1;
203 status := 'INVALID_RELEASE_RESCHEDULES';
204 RETURN;
205 END;
206
207
208 error_tracking_num := 1080;
209 -- snapstaticentities can be either Y or N, converted to 1 or 2
210 BEGIN
211 SELECT to_number(decode(snapstaticentities, 'Y', msc_ws_common.sys_yes, msc_ws_common.sys_no))
212 INTO l_val_snapstaticentities
213 FROM dual;
214 END;
215
216 processid := fnd_request.submit_request(
217 'MSC', -- application
218 'MSCSLPPR5', -- program
219 NULL, -- description
220 NULL, -- start_time
221 FALSE, -- sub_request
222 l_val_planname,
223 planId,
224 l_val_launchsnapshot,
225 l_val_launchplanner,
226 l_val_netchange,
227 to_char(l_val_anchordate, 'YYYY/MM/DD HH24:MI:SS'),
228 l_val_archivePlan,
229 l_val_launchplanner,
230 l_val_inventory_atp_flag, -- plan_type_dummy param
231 l_val_enable24x7atp,
232 l_val_production, -- rescheduleDummy VARCHAR2
233 l_val_releasereschedules, --release
234 l_val_snapstaticentities -- snapStaticEntities
235 );
236
237 IF(processid = 0) THEN
238 processid := -1;
239 status := 'ERROR_SUBMIT';
240 RETURN;
241 END IF;
242
243 status := 'SUCCESS';
244
245 EXCEPTION
246 WHEN others THEN
247 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
248 processId := -1;
249 return;
250 END LAUNCH_ASCP_BATCH;
251
252 PROCEDURE LAUNCH_ASCP_BATCH_PUBLIC (
253 processId OUT NOCOPY NUMBER,
254 status OUT NOCOPY VARCHAR2,
255 UserName IN VARCHAR2,
256 RespName IN VARCHAR2,
257 RespApplName IN VARCHAR2,
258 SecurityGroupName IN VARCHAR2,
259 Language IN VARCHAR2,
260 planId IN NUMBER,
261 launchSnapshot IN VARCHAR2,
262 launchPlanner IN VARCHAR2,
263 anchorDate IN DATE,
264 archiveCurrVersPlan IN VARCHAR2,
265 enable24x7Atp IN VARCHAR2,
266 releaseReschedules IN VARCHAR2,
267 snapStaticEntities IN VARCHAR2
268 ) AS
269
270 userid number;
271 respid number;
272 l_String VARCHAR2(30);
273 error_tracking_num number;
274 l_SecutirtGroupId NUMBER;
275 BEGIN
276 error_tracking_num :=2010;
277 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
278 IF (l_String <> 'OK') THEN
279 Status := l_String;
280 RETURN;
281 END IF;
282
283 error_tracking_num :=2030;
284 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSCFNSCW-SCP',l_SecutirtGroupId);
285 IF (l_String <> 'OK') THEN
286 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSCFPCMN-SCP', l_SecutirtGroupId);
287 IF (l_String <> 'OK') THEN
288 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSC_ORG_FNDRSRUN_LAUNCH_SCP',l_SecutirtGroupId);
289 IF (l_String <> 'OK') THEN
290 Status := l_String;
291 RETURN;
292 END IF;
293 END IF;
294 END IF;
295 error_tracking_num :=2040;
296 LAUNCH_ASCP_BATCH ( processId, status, userId ,respid, planId , launchSnapshot ,launchPlanner, anchorDate ,archiveCurrVersPlan , enable24x7Atp, releaseReschedules ,snapStaticEntities );
297
298
299
300 EXCEPTION
301 WHEN others THEN
302 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
303 processId := -1;
304 return;
305 END LAUNCH_ASCP_BATCH_PUBLIC;
306
307 -- =============================================================
308 -- Desc: Please see package spec for description
309 -- =============================================================
310 PROCEDURE RELEASE_ASCP ( req_id OUT NOCOPY REQTBLTYP,
311 status OUT NOCOPY VARCHAR2,
312 userId IN NUMBER,
313 responsibilityId IN NUMBER,
314 planId IN NUMBER,
315 release_time_fence_anchor_date IN VARCHAR2
316 ) AS
317 error_tracking_num NUMBER;
318 l_val_result VARCHAR2(30);
319 l_val_planid NUMBER;
320 l_val_planname VARCHAR2(10);
321 l_val_orgid NUMBER;
322 l_val_instanceid NUMBER;
323 l_rel_time_window VARCHAR2(1);
324 RETCODE NUMBER;
325 ERRMSG VARCHAR2(200);
326 l_req_id MSC_RELEASE_PK.REQTBLTYP;
327
328 i number;
329 j number :=1;
330
331 BEGIN
332 req_id := REQTBLTYP();
333 error_tracking_num :=2010;
334 msc_ws_common.validate_user_resp(l_val_result, userid, responsibilityid);
335
336 IF(l_val_result <> 'OK') THEN
337 status := l_val_result;
338 RETURN;
339 END IF;
340
341
342 error_tracking_num :=2020;
343 -- check plan id
344 BEGIN
345 SELECT plans.plan_id, plans.compile_designator, plans.organization_id, plans.sr_instance_id
346 INTO l_val_planid, l_val_planname, l_val_orgid, l_val_instanceid
347 FROM msc_plans plans, msc_designators desig
348 WHERE plans.curr_plan_type in (1,2,3)
349 AND plans.organization_id = desig.organization_id
350 AND plans.sr_instance_id = desig.sr_instance_id
351 AND plans.compile_designator = desig.designator
352 AND NVL(desig.disable_date, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
353 AND plans.organization_selection <> 1
354 AND NVL(plans.copy_plan_id,-1) = -1
355 AND NVL(desig.copy_designator_id, -1) = -1
356 AND plans.plan_id = planId;
357 EXCEPTION
358 WHEN no_data_found THEN
359
360
361 status := 'INVALID_PLANID';
362 RETURN;
363 END;
364
365 error_tracking_num :=2010;
366 if (release_time_fence_anchor_date = 'PLAN_START_DATE')THEN
367 l_rel_time_window := 'Y';
368 elsif (release_time_fence_anchor_date = 'CURRENT_DATE')THEN
369 l_rel_time_window := 'N';
370 elsE
371
372
373 status := 'INVALID_RELEASE_TIME_FENCE_ANCHOR_DATE';
374 RETURN;
375
376 end if;
377
378 error_tracking_num :=2030;
379 msc_release_pk.msc_web_service_release(planId, l_rel_time_window, RETCODE, ERRMSG,l_REQ_ID);
380 IF (RETCODE = 2) THEN
381 status := 'ERROR_RELEASE '|| ERRMSG;
382 RETURN;
383 END IF;
384
385 error_tracking_num :=2040;
386 FOR i IN 1..l_REQ_ID.count LOOP
387 if (l_REQ_ID(i).ReqID is not null) then
388 req_id.extend;
389 req_id(j) := reqrectyp(l_REQ_ID(i).instanceCode,l_REQ_ID(i).ReqID, l_REQ_ID(i).ReqType);
390 j := j + 1;
391 end if;
392
393 END LOOP;
394
395
396 status := 'SUCCESS';
397
398
399 EXCEPTION
400 WHEN others THEN
401 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
402
403
404 rollback;
405 return;
406
407
408 END RELEASE_ASCP;
409
410 PROCEDURE RELEASE_ASCP_PUBLIC ( req_id OUT NOCOPY REQTBLTYP,
411 status OUT NOCOPY VARCHAR2,
412 UserName IN VARCHAR2,
413 RespName IN VARCHAR2,
414 RespApplName IN VARCHAR2,
415 SecurityGroupName IN VARCHAR2,
416 Language IN VARCHAR2,
417 planId IN NUMBER,
418 release_time_fence_anchor_date IN VARCHAR2
419 ) AS
420 userid number;
421 respid number;
422 l_String VARCHAR2(30);
423 l_SecutirtGroupId NUMBER;
424 error_tracking_num NUMBER;
425
426
427
428 BEGIN
429
430 req_id := REQTBLTYP();
431 error_tracking_num :=2010;
432 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
433 IF (l_String <> 'OK') THEN
434 Status := l_String;
435 RETURN;
436 END IF;
437
438 error_tracking_num :=2030;
439 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSCFNSCW-SCP',l_SecutirtGroupId);
440 IF (l_String <> 'OK') THEN
441 Status := l_String;
442 RETURN;
443 END IF;
444 error_tracking_num :=2040;
445
446 RELEASE_ASCP ( req_id , status, userId ,respid, planId , release_time_fence_anchor_date );
447 EXCEPTION
448 WHEN others THEN
449 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
450
451 return;
452 END RELEASE_ASCP_PUBLIC;
453
454 -- =============================================================
455 --
456 -- SET_ASCP_PLAN_OPTIONS and its private helper functions.
457 --
458 -- Un-handled exceptions generate error tokens in the
459 -- format of ERROR_UNEXPECTED_#####.
460 -- The possible values are:
461 -- 00021 - SET_ASCP_PLAN_OPTIONS/MSC_WS_COMMON.VALIDATE_PLAN_ID
462 -- 00022 - SET_ASCP_PLAN_OPTIONS/VALIDATE_PLAN_TYPE
463 -- 00023 - SET_ASCP_PLAN_OPTIONS/MSC_WS_COMMON.VALIDATE_SIMULATION_SET_ID
464 -- 00024 - SET_ASCP_PLAN_OPTIONS/VALIDATE_GLB_DMD_SCHS/VALIDATE_G_DMD_SCH_ID
465 -- 00025 - SET_ASCP_PLAN_OPTIONS/VALIDATE_GLB_DMD_SCHS/VALIDATE_CONSUM_LVL (goe)
466 -- 00026 - SET_ASCP_PLAN_OPTIONS/VALIDATE_GLB_DMD_SCHS/VALIDATE_CONSUM_LVL (item)
467 -- 00027 - SET_ASCP_PLAN_OPTIONS/VALIDATE_LOC_DMD_SCHS/VALIDATE_L_DMD_SCH_ID
468 -- 00028 - SET_ASCP_PLAN_OPTIONS/VALIDATE_LOC_DMD_SCHS/MSC_WS_COMMON.PLAN_CONTAINS_THIS_ORG
469 -- 00029 - SET_ASCP_PLAN_OPTIONS/VALIDATE_LOC_SUP_SCHS/VALIDATE_L_SUP_SCH_ID
470 -- 00030 - SET_ASCP_PLAN_OPTIONS/VALIDATE_LOC_SUP_SCHS/MSC_WS_COMMON.PLAN_CONTAINS_THIS_ORG
471 -- 00031 - SET_ASCP_PLAN_OPTIONS/MSC_WS_COMMON.PURGE_ALL_SCHEDULES
472 -- 00032 - SET_ASCP_PLAN_OPTIONS/MSC_WS_COMMON.UPDATE_PLAN_OPTIONS
473 -- 00033 - SET_ASCP_PLAN_OPTIONS/MSC_WS_COMMON.INSERT_ALL_SCHEDULES
474 -- 00034 - SET_ASCP_PLAN_OPTIONS/MSC_WS_COMMON.INSERT_OR_UPDATE_ALL_SCHS
475 -- =============================================================
476
477
478
479 -- =============================================================
480 -- Desc: Validate plan type is ASCP
481 -- Input:
482 -- PlanId plan id.
483 --
484 -- Output: The possible return statuses are:
485 -- OK
486 -- INVALID_PLAN_TYPE
487 -- =============================================================
488 FUNCTION VALIDATE_PLAN_TYPE( PlanId IN NUMBER ) RETURN VARCHAR2 AS
489 l_ReturnString VARCHAR2(100);
490 l_Dummy NUMBER;
491 BEGIN
492 BEGIN
493 SELECT
494 1 INTO l_Dummy
495 FROM
496 msc_plans
497 WHERE
498 curr_plan_type IN (1, 2, 3) AND
499 plan_id = PlanId;
500 EXCEPTION WHEN NO_DATA_FOUND THEN
501 l_ReturnString := 'INVALID_PLAN_TYPE';
502 RETURN l_ReturnString;
503 WHEN others THEN
504 g_ErrorCode := 'ERROR_UNEXPECTED_00022';
505 raise;
506 END;
507
508 l_ReturnString := 'OK';
509 RETURN l_ReturnString;
510 END VALIDATE_PLAN_TYPE;
511
512 -- =============================================================
513 -- Desc: Validate global demand schedule id
514 -- Input:
515 -- SchId global demand schedule id.
516 -- PlanName plan name.
517 --
518 -- Output: The possible return statuses are:
519 -- OK
520 -- INVALID_GLOBALDMDSCHS_DMD_SCH_ID
521 -- =============================================================
522 FUNCTION VALIDATE_G_DMD_SCH_ID(
523 SchId IN NUMBER,
524 PlanName IN VARCHAR2
525 ) RETURN VARCHAR2 AS
526 l_Dummy NUMBER;
527 BEGIN
528 BEGIN
529 SELECT
530 1 INTO l_Dummy
531 FROM
532 msd_dp_ascp_scenarios_v
533 WHERE
534 global_scenario_flag = 'Y' AND
535 scenario_name <> PlanName AND
536 scenario_id = SchId;
537 EXCEPTION WHEN NO_DATA_FOUND THEN
538 RETURN 'INVALID_GLOBALDMDSCHS_DMD_SCH_ID';
539 WHEN others THEN
540 g_ErrorCode := 'ERROR_UNEXPECTED_00024';
541 raise;
542 END;
543
544 RETURN 'OK';
545 END VALIDATE_G_DMD_SCH_ID;
546
547 -- =============================================================
548 -- Desc: Validate the ship to consumption level. This function is
549 -- used by ASCP only. DRP and SRP have their own function in
550 -- MSC_WS_COMMON.
551 -- Input:
552 -- ShipTo Ship to consumption level.
553 -- SchId Demand schedule id.
554 -- IsLocal Is this ship to consumption level
555 -- for a local demand schedule?
556 --
557 -- Output: The possible return statuses are:
558 -- OK
559 -- INVALID_SHIP_TO_CONSUMPTION_LVL
560 -- =============================================================
561 FUNCTION VALIDATE_CONSUM_LVL(
562 ShipTo IN NUMBER,
563 SchId IN NUMBER,
564 IsLocal IN NUMBER
565 ) RETURN VARCHAR2 AS
566 l_scenario_lvl_geo NUMBER;
567 l_scenario_lvl_item NUMBER;
568 BEGIN
569 BEGIN
570 SELECT level_id INTO l_scenario_lvl_geo
571 FROM msd_dp_scn_output_levels_v
572 WHERE
573 scenario_id = SchId AND
574 level_id IN (11,15,41,42,30);
575 EXCEPTION WHEN NO_DATA_FOUND THEN
576 l_scenario_lvl_geo := 30;
577 WHEN others THEN
578 g_ErrorCode := 'ERROR_UNEXPECTED_00025';
579 raise;
580 END;
581
582 BEGIN
583 SELECT level_id INTO l_scenario_lvl_item
584 FROM msd_dp_scn_output_levels_v
585 WHERE
586 scenario_id = SchId AND
587 level_id IN (34, 40);
588 EXCEPTION WHEN NO_DATA_FOUND THEN
589 l_scenario_lvl_item := 40;
590 WHEN others THEN
591 g_ErrorCode := 'ERROR_UNEXPECTED_00026';
592 raise;
593 END;
594
595 /*
596 2 Ship
597 3 Bill
598 4 Customer
599 5 Region
600 6 Item
601 7 Customer Site
602 8 Zone
603 9 Customer Zone
604 10 Demand Class
605 */
606
607 -- this Xtra logic are found for ASCP plan only,
608 -- code block added for manipulating record group to remove customer zone
609 -- for local demand schedules for MSC_SHIP_TO_CS_ALL, MSC_SHIP_TO_CZ_ALL
610 -- MSC_SHIP_TO_CS_DC and MSC_SHIP_TO_CZ_DC.
611 IF l_scenario_lvl_item = 40 THEN
612 IF l_scenario_lvl_geo = 11 THEN
613 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CS_ALL'
614 IF IsLocal = MSC_WS_COMMON.SYS_YES THEN
615 IF ShipTo NOT IN (4, 6, 7) THEN -- Item, Customer, Customer Site
616 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
617 END IF;
618 ELSE
619 IF ShipTo NOT IN (4, 6, 7, 9) THEN -- Item, Customer, Customer Zone, Customer Site
620 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
621 END IF;
622 END IF;
623 ELSIF l_scenario_lvl_geo = 15 THEN
624 -- simulate the logic from Record Groups 'MSC_SHIP_TO_C_ALL'
625 IF ShipTo NOT IN (4, 6) THEN -- Item, Customer
626 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
627 END IF;
628 ELSIF l_scenario_lvl_geo = 42 then
629 -- simulate the logic from Record Groups 'MSC_SHIP_TO_Z_ALL'
630 IF ShipTo NOT IN (6, 8) THEN -- Item, Zone
631 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
632 END IF;
633 ELSIF l_scenario_lvl_geo = 41 THEN
634 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CZ_ALL'
635 IF IsLocal = MSC_WS_COMMON.SYS_YES THEN
636 IF ShipTo NOT IN (4, 6, 8) THEN -- Item, Customer, Zone
637 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
638 END IF;
639 ELSE
640 IF ShipTo NOT IN (4, 6, 8, 9) THEN -- Item, Customer, Customer Zone, Zone
641 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
642 END IF;
643 END IF;
644 ELSIF l_scenario_lvl_geo = 30 THEN
645 -- simulate the logic from Record Groups 'MSC_SHIP_TO_ALL_ALL'
646 IF ShipTo <> 6 THEN -- Item'
647 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
648 END IF;
649 ELSE
650 -- ???? use the default record group ?????
651 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CS_ALL'
652 IF IsLocal = MSC_WS_COMMON.SYS_YES THEN
653 IF ShipTo NOT IN (4, 6, 7) THEN -- Item, Customer, Customer Site
654 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
655 END IF;
656 ELSE
657 IF ShipTo NOT IN (4, 6, 7, 9) THEN -- Item, Customer, Customer Zone, Customer Site
658 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
659 END IF;
660 END IF;
661 END IF;
662 ELSE -- IF l_scenario_lvl_item <> 40 THEN
663 IF l_scenario_lvl_geo = 11 THEN
664 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CS_DC'
665 IF IsLocal = MSC_WS_COMMON.SYS_YES THEN
666 IF ShipTo NOT IN (4, 6, 7, 10) THEN -- Item, Customer, Demand Class, Customer Site
667 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
668 END IF;
669 ELSE
670 IF ShipTo NOT IN (4, 6, 7, 9, 10) THEN -- Item, Customer, Demand Class, Customer Zone, Customer Site
671 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
672 END IF;
673 END IF;
674 ELSIF l_scenario_lvl_geo = 15 THEN
675 -- simulate the logic from Record Groups 'MSC_SHIP_TO_C_DC'
676 IF ShipTo NOT IN (4, 6, 10) THEN -- Item, Customer, Demand Class
677 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
678 END IF;
679 ELSIF l_scenario_lvl_geo = 42 THEN
680 -- simulate the logic from Record Groups 'MSC_SHIP_TO_Z_DC'
681 IF ShipTo NOT IN (6, 8, 10) THEN -- Item, Demand Class, Zone
682 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
683 END IF;
684 ELSIF l_scenario_lvl_geo = 41 THEN
685 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CZ_DC'
686 IF IsLocal = MSC_WS_COMMON.SYS_YES THEN
687 IF ShipTo NOT IN (4, 6, 8, 10) THEN -- Item', Customer, Zone, Demand Class
688 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
689 END IF;
690 ELSE
691 IF ShipTo NOT IN (4, 6, 8, 9, 10) THEN -- Item', Customer, Customer Zone, Zone, Demand Class
692 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
693 END IF;
694 END IF;
695 ELSIF l_scenario_lvl_geo = 30 THEN
696 -- simulate the logic from Record Groups 'MSC_SHIP_TO_ALL_DC'
697 IF ShipTo NOT IN (6, 10) THEN -- Item, Demand Class
698 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
699 END IF;
700 ELSE
701 -- ???? which record group should I use ?????
702 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CS_DC'
703 IF IsLocal = MSC_WS_COMMON.SYS_YES THEN
704 IF ShipTo NOT IN (4, 6, 7, 10) THEN -- Item, Customer, Demand Class, Customer Site
705 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
706 END IF;
707 ELSE
708 IF ShipTo NOT IN (4, 6, 7, 9, 10) THEN -- Item, Customer, Demand Class, Customer Zone, Customer Site
709 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
710 END IF;
711 END IF;
712 END IF;
713 END IF;
714
715 RETURN 'OK';
716 END VALIDATE_CONSUM_LVL;
717
718 -- =============================================================
719 -- Desc: validate global demand schedules
720 -- Input:
721 -- SchTable Global demand schedules.
722 -- PlanName Plan name.
723 --
724 -- Output: The possible return statuses are:
725 -- OK
726 -- INVALID_GLOBALDMDSCHS_DMD_SCH_ID
727 -- INVALID_GLOBALDMDSCHS_SHP_TO_CONSUMPTION_LVL
728 -- =============================================================
729 FUNCTION VALIDATE_GLB_DMD_SCHS(
730 SchTable IN MscGlbDmdSchTbl,
731 PlanName IN VARCHAR2
732 ) RETURN VARCHAR2 AS
733 l_ReturnString VARCHAR2(100);
734 l_String VARCHAR2(100);
735 BEGIN
736 IF SchTable IS NOT NULL AND SchTable.count > 0 THEN
737 FOR I IN SchTable.first..SchTable.last
738 LOOP
739 -- validate demand schedule id
740 l_String := VALIDATE_G_DMD_SCH_ID(SchTable(I).DmdSchId, PlanName);
741 IF (l_String <> 'OK') THEN
742 RETURN l_String;
743 END IF;
744
745 -- validate ship to consumption level
746 l_String := VALIDATE_CONSUM_LVL(
747 SchTable(I).ShipToConsumptionLvl,
748 SchTable(I).DmdSchId,
749 MSC_WS_COMMON.SYS_NO
750 );
751 IF (l_String <> 'OK') THEN
752 RETURN 'INVALID_GLOBALDMDSCHS_SHP_TO_CONSUMPTION_LVL';
753 END IF;
754 -- add a new record for this global demand schedule into the
755 -- global table structure (for internal use). we need
756 -- the Xtra data to insert/update the database
757 g_IGlbDmdSchTbl.extend;
758 g_IGlbDmdSchTbl(g_IGlbDmdSchTbl.count) :=
759 MscIGlbDmdSchRec(SchTable(I).DmdSchId,
760 SchTable(I).ShipToConsumptionLvl,
761 1, -- input_type
762 7 -- designator_type
763 );
764
765 END LOOP;
766 END IF;
767
768 RETURN 'OK';
769 END VALIDATE_GLB_DMD_SCHS;
770
771 -- =============================================================
772 -- Desc: Validate loal demand schedule id
773 -- Input:
774 -- SchId local demand schedule id.
775 -- OrgId organization id.
776 -- InsId sr instance id.
777 -- PlanName plan name.
778 --
779 -- Output: The possible return statuses are:
780 -- OK
781 -- INVALID_LOCALDMDSCHS_DMD_SCH_ID
782 -- =============================================================
783 FUNCTION VALIDATE_L_DMD_SCH_ID(
784 DesigType OUT NOCOPY NUMBER,
785 FcstShipTo OUT NOCOPY NUMBER,
786 SchId IN NUMBER,
787 OrgId IN NUMBER,
788 InsId IN NUMBER,
789 PlanName IN VARCHAR2
790 ) RETURN VARCHAR2 AS
791 l_ReturnString VARCHAR2(100);
792 l_DesigType NUMBER;
793 l_ShipToCode VARCHAR2(80);
794 BEGIN
795 BEGIN
796 SELECT
797 desig.designator_type,
798 decode(desig.designator_type, 6, desig.update_type, -1)
799 INTO
800 DesigType,
801 FcstShipTo
802 FROM
803 msc_designators desig ,
804 fnd_lookups lu
805 WHERE
806 ( (desig.designator_type = 6 AND desig.forecast_set_id is null) OR
807 (desig.designator_type IN (1,2,3,4,5,8) )
808 ) AND
809 trunc(nvl(desig.disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
810 ( desig.designator <> PlanName OR desig.designator_type = 1 ) AND
811 desig.organization_id = OrgId AND
812 desig.sr_instance_id = InsId AND
813 desig.designator_id = SchId AND
814 lu.lookup_code(+) = desig.update_type AND
815 lu.lookup_type(+) = 'MSC_SHIP_TO'
816 UNION
817 SELECT
818 7, -1
819 FROM
820 msd_dp_ascp_scenarios_v
821 WHERE
822 global_scenario_flag ='N' AND
823 scenario_name <> PlanName AND
824 scenario_id = SchId AND
825 ( sr_instance_id = -23453 OR
826 sr_instance_id = InsId )
827 UNION
828 SELECT
829 desig.designator_type,
830 decode(desig.designator_type, 6, desig.update_type, -1)
831 FROM
832 msc_designators desig,
833 fnd_lookups lu,
834 msc_plan_organizations_v mpo
835 WHERE
836 ( (desig.designator_type = 6 AND desig.forecast_set_id is null) OR
837 (desig.designator_type IN (2,3,4,5,8))
838 ) AND
839 trunc(nvl(desig.disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
840 mpo.organization_id = desig.organization_id AND
841 mpo.sr_instance_id = desig.sr_instance_id AND
842 mpo.compile_designator = desig.designator AND
843 mpo.planned_organization = OrgId AND
844 mpo.sr_instance_id = InsId AND
845 desig.designator <> PlanName AND
846 desig.designator_id = SchId AND
847 lu.lookup_code(+) = desig.update_type AND
848 lu.lookup_type(+) = 'MSC_SHIP_TO'
849 UNION
850 SELECT
851 desig.designator_type,
852 decode(desig.designator_type, 6, desig.update_type, -1)
853 FROM
854 msc_designators desig,
855 fnd_lookups lu,
856 msc_item_sourcing mis,
857 msc_plans mp
858 WHERE
859 ( (desig.designator_type = 6 AND desig.forecast_set_id is null) OR
860 (desig.designator_type IN (2,3,4,5,8))
861 ) AND
862 trunc(nvl(desig.disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
863 mis.plan_id = mp.plan_id AND
864 mp.organization_id = desig.organization_id AND
865 mp.sr_instance_id = desig.sr_instance_id AND
866 mp.compile_designator =desig.designator AND
867 mis.source_organization_id = OrgId AND
868 mis.sr_instance_id2 = InsId AND
869 desig.designator <> PlanName AND
870 desig.designator_id = SchId AND
871 lu.lookup_code(+) = desig.update_type AND
872 lu.lookup_type(+) = 'MSC_SHIP_TO';
873 EXCEPTION WHEN NO_DATA_FOUND THEN
874 RETURN 'INVALID_LOCALDMDSCHS_DMD_SCH_ID';
875 WHEN others THEN
876 g_ErrorCode := 'ERROR_UNEXPECTED_00027';
877 raise;
878 END;
879
880 RETURN 'OK';
881 END VALIDATE_L_DMD_SCH_ID;
882
883 -- =============================================================
884 -- Desc: validate local demand schedules
885 -- Input:
886 -- SchTable Local demand schedules.
887 -- PlanName Plan name.
888 --
889 -- Output: The possible return statuses are:
890 -- OK
891 -- INVALID_LOCALDMDSCHS_ORGID
892 -- INVALID_LOCALDMDSCHS_DMD_SCH_ID
893 -- INVALID_LOCALDMDSCHS_SHP_TO_CONSUMPTION_LVL
894 -- =============================================================
895 FUNCTION VALIDATE_LOC_DMD_SCHS(
896 SchTable IN MscLocDmdSchTbl,
897 PlanId IN NUMBER,
898 PlanName IN VARCHAR2
899 ) RETURN VARCHAR2 AS
900 l_returnstring VARCHAR2(100);
901 l_OrgInsId NUMBER;
902 l_DesigType NUMBER;
903 l_FcstShipTo NUMBER;
904 l_ShipTo NUMBER;
905 l_IncTgtDmd NUMBER;
906 l_IntPlantFlg NUMBER;
907 BEGIN
908 IF SchTable IS NOT NULL AND SchTable.count > 0 THEN
909 FOR I IN SchTable.first..SchTable.last
910 LOOP
911 -- validate organization id
912 BEGIN
913 l_ReturnString := MSC_WS_COMMON.PLAN_CONTAINS_THIS_ORG(l_OrgInsId, SchTable(I).OrgId, PlanId);
914 IF (l_ReturnString <> 'OK') THEN
915 -- overwrite the error token here.
916 RETURN 'INVALID_LOCALDMDSCHS_ORGID';
917 END IF;
918 EXCEPTION WHEN others THEN
919 g_ErrorCode := 'ERROR_UNEXPECTED_00028';
920 raise;
921 END;
922
923 -- validate demand schedule id
924 l_ReturnString := VALIDATE_L_DMD_SCH_ID(
925 l_DesigType,
926 l_FcstShipTo,
927 SchTable(I).DmdSchId,
928 SchTable(I).OrgId,
929 l_OrgInsId,
930 PlanName);
931 IF (l_ReturnString <> 'OK') THEN
932 RETURN l_ReturnString; -- 'INVALID_LOCALDMDSCHS_DMD_SCH_ID'
933 END IF;
934
935 -- validate ship to consumption level
936 -- if l_DesigType = 7 , DPSCN, do validation
937 -- else if l_DesigType = 6, FCST, copy forecast_ship_to to ship to
938 -- else default to null, 1 MDS, 2 MPS, 3 MRP, 4 MPP, 5 IP, 8 DPP, 9 MNTDS, 10 MFGDS
939 IF l_DesigType = 7 THEN
940 IF SchTable(I).ShipToConsumptionLvl IS NULL THEN
941 RETURN 'INVALID_LOCALDMDSCHS_SHP_TO_CONSUMPTION_LVL';
942 END IF;
943 l_ReturnString := VALIDATE_CONSUM_LVL(
944 SchTable(I).ShipToConsumptionLvl,
945 SchTable(I).DmdSchId,
946 MSC_WS_COMMON.SYS_YES
947 );
948 IF (l_ReturnString <> 'OK') THEN
949 RETURN 'INVALID_LOCALDMDSCHS_SHP_TO_CONSUMPTION_LVL';
950 END IF;
951 l_ShipTo := SchTable(I).ShipToConsumptionLvl;
952 ELSE
953 IF SchTable(I).ShipToConsumptionLvl IS NOT NULL THEN
954 RETURN 'INVALID_LOCALDMDSCHS_SHP_TO_CONSUMPTION_LVL';
955 END IF;
956 IF l_DesigType = 6 THEN
957 l_ShipTo := l_FcstShipTo;
958 ELSE
959 l_ShipTo := NULL;
960 END IF;
961 END IF;
962
963 IF l_DesigType = 8 THEN
964 l_IncTgtDmd := MSC_WS_COMMON.BOOL_TO_NUMBER(SchTable(I).IncludeTargetDmd);
965 l_IntPlantFlg := 2; -- ignore user input, use default which is unchecked
966 ELSE
967 l_IncTgtDmd := 2; -- ignore user input, use default which is unchecked
968 l_IntPlantFlg := MSC_WS_COMMON.BOOL_TO_NUMBER(SchTable(I).InterPlantFlg);
969 END IF;
970
971 g_ILocDmdSchTbl.extend;
972 g_ILocDmdSchTbl(g_ILocDmdSchTbl.COUNT) :=
973 MscILocDmdSchRec(SchTable(I).OrgId,
974 SchTable(I).DmdSchId,
975 l_IncTgtDmd,
976 SchTable(I).ShipToConsumptionLvl,
977 l_IntPlantFlg,
978 1, -- input_type
979 l_DesigType -- designator_type
980 );
981
982
983 END LOOP;
984 END IF;
985
986 RETURN 'OK';
987 END VALIDATE_LOC_DMD_SCHS;
988
989 -- =============================================================
990 -- Desc: Validate loal supply schedule id
991 -- Input:
992 -- SchId local supply schedule id.
993 -- OrgId organization id.
994 -- InsId sr instance id.
995 -- PlanName plan name.
996 --
997 -- Output: The possible return statuses are:
998 -- OK
999 -- INVALID_LOCALSUPSCHS_SUP_SCH_ID
1000 -- =============================================================
1001 FUNCTION VALIDATE_L_SUP_SCH_ID(
1002 DesigType OUT NOCOPY NUMBER,
1003 SchId IN NUMBER,
1004 OrgId IN NUMBER,
1005 InsId IN NUMBER,
1006 PlanName IN VARCHAR2
1007 ) RETURN VARCHAR2 AS
1008 BEGIN
1009 BEGIN
1010 SELECT
1011 designator_type INTO DesigType
1012 FROM
1013 msc_designators
1014 WHERE
1015 trunc(nvl(disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
1016 designator <> PlanName AND
1017 organization_id = OrgId AND
1018 sr_instance_id = InsId AND
1019 designator_id = SchId AND
1020 designator_type not in (1,6,7,8)
1021 UNION
1022 SELECT
1023 desig.designator_type
1024 FROM
1025 msc_designators desig,
1026 msc_plan_organizations_v mpo
1027 WHERE
1028 desig.designator_type not in (1,6,7,8) AND
1029 trunc(nvl(desig.disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
1030 mpo.organization_id = desig.organization_id AND
1031 mpo.sr_instance_id = desig.sr_instance_id AND
1032 mpo.compile_designator = desig.designator AND
1033 mpo.planned_organization = OrgId AND
1034 mpo.sr_instance_id = InsId AND
1035 desig.designator <> PlanName AND
1036 desig.designator_id = SchId;
1037 EXCEPTION WHEN NO_DATA_FOUND THEN
1038 RETURN 'INVALID_LOCALSUPSCHS_SUP_SCH_ID';
1039 WHEN others THEN
1040 g_ErrorCode := 'ERROR_UNEXPECTED_00029';
1041 raise;
1042 END;
1043
1044 RETURN 'OK';
1045 END VALIDATE_L_SUP_SCH_ID;
1046
1047 -- =============================================================
1048 -- Desc: validate local supply schedules
1049 -- Input:
1050 -- SchTable Local supply schedules.
1051 -- PlanName Plan name.
1052 --
1053 -- Output: The possible return statuses are:
1054 -- OK
1055 -- INVALID_LOCALSUPSCHS_ORGID
1056 -- INVALID_LOCALSUPSCHS_SUP_SCH_ID
1057 -- =============================================================
1058 FUNCTION VALIDATE_LOC_SUP_SCHS(
1059 SchTable IN MscLocSupSchTbl,
1060 PlanId IN NUMBER,
1061 PlanName IN VARCHAR2
1062 ) RETURN VARCHAR2 AS
1063 l_ReturnString VARCHAR2(100);
1064 l_OrgInsId NUMBER;
1065 l_DesigType NUMBER;
1066 BEGIN
1067 IF SchTable IS NOT NULL AND SchTable.count > 0 THEN
1068 FOR I IN SchTable.first..SchTable.last
1069 LOOP
1070 -- validate organization id
1071 BEGIN
1072 l_ReturnString := MSC_WS_COMMON.PLAN_CONTAINS_THIS_ORG(l_OrgInsId, SchTable(I).OrgId, PlanId);
1073 IF (l_ReturnString <> 'OK') THEN
1074 -- overwrite the error token here.
1075 RETURN 'INVALID_LOCALSUPSCHS_ORGID';
1076 END IF;
1077 EXCEPTION WHEN others THEN
1078 g_ErrorCode := 'ERROR_UNEXPECTED_00030';
1079 raise;
1080 END;
1081
1082 -- validate supply schedule id
1083 l_ReturnString := VALIDATE_L_SUP_SCH_ID(
1084 l_DesigType,
1085 SchTable(I).SupSchId,
1086 SchTable(I).OrgId,
1087 l_OrgInsId,
1088 PlanName);
1089 IF (l_ReturnString <> 'OK') THEN
1090 RETURN l_ReturnString;
1091 END IF;
1092
1093 g_ILocSupSchTbl.extend;
1094 g_ILocSupSchTbl(g_ILocSupSchTbl.COUNT) :=
1095 MscILocSupSchRec(SchTable(I).OrgId,
1096 SchTable(I).SupSchId,
1097 2, -- input_type
1098 l_DesigType -- designator_type
1099 );
1100
1101
1102
1103
1104 END LOOP;
1105 END IF;
1106
1107 RETURN 'OK';
1108 END VALIDATE_LOC_SUP_SCHS;
1109
1110 -- =============================================================
1111 -- Desc: This procedure is invoked from web service to
1112 -- updates Plan Options for ASCP plans.
1113 -- Input:
1114 -- UserId User ID.
1115 -- ResponsibilityId Responsibility Id.
1116 -- PlanId Plan Id.
1117 -- ItemSimulationSet Item Simulation Set.
1118 -- Overwrite Overwrite. Expected values are All,
1119 -- Outside PTF, or None.
1120 -- PurgeAllSchsFlag There is no such parameter in UI. Allowed
1121 -- input is Y or N. This is a new parameter
1122 -- to control how Global Demand Schedules, Local
1123 -- Demand Schedules and Local Supply Schedules
1124 -- are updated / inserted. If this flag is set, all
1125 -- Global Demand Schedules, Local Demand Schedules and
1126 -- Local Supply Schedule will be purged before
1127 -- update / insert any demand / supply schedules from
1128 -- the input parameters. If this flag is not set, no
1129 -- demand / supple schedules will be purged, schedules in
1130 -- the input parameters will be updated or inserted.
1131 -- GlobalDmdSchs Global Demand Schedules. Each demand schedule contains
1132 -- the schedule id and ship to consumption level parameters.
1133 -- Although this is not a required parameter, we need both
1134 -- id and ShpToConsumptionLvl to define a demand schedule,
1135 -- so either both parameters are empty or both are entered.
1136 -- LocalDmdSchs Local Demand Schedules. List of all local demand schedules.
1137 -- Each local demand schedule contains the organization id,
1138 -- demand schedule id, include target demands, ship to
1139 -- consumption level and inter plant demand flag. Similar to
1140 -- Global Demand Schedules, these five parameters have to be
1141 -- either all empty or all entered.
1142 -- LocalSupSchs Supply Schedules.List of local supply schedules. Each local
1143 -- supply schedule contains the organization id and supply
1144 -- schedule id. Similar to Global Demand Schedules, these
1145 -- two parameters have to be either both empty or both entered
1146 --
1147 -- Output: Procedure returns a status and conc program req id.
1148 -- The possible return statuses are:
1149 -- SUCCESS if everything is ok
1150 -- ERROR_DUP_GLOBALDMDSCH
1151 -- ERROR_DUP_LOCALDMDSCH
1152 -- ERROR_DUP_LOCALSUPSCH
1153 -- ERROR_UNEXPECTED_##### unexpected error
1154 -- INVALID_FND_USERID
1155 -- INVALID_FND_RESPONSIBILITYID
1156 -- INVALID_PLANID invalid source plan id
1157 -- INVALID_PLAN_TYPE non ASCP plan
1158 -- INVALID_SIMULATION_SET_ID
1159 -- INVALID_GLOBALDMDSCHS_DMD_SCH_ID
1160 -- INVALID_GLOBALDMDSCHS_SHP_TO_CONSUMPTION_LVL
1161 -- INVALID_LOCALDMDSCHS_ORGID
1162 -- INVALID_LOCALDMDSCHS_DMD_SCH_ID
1163 -- INVALID_LOCALDMDSCHS_SHP_TO_CONSUMPTION_LVL
1164 -- INVALID_LOCALSUPSCHS_ORGID
1165 -- INVALID_LOCALSUPSCHS_SUP_SCH_NAME
1166 -- =============================================================
1167 PROCEDURE SET_ASCP_PLAN_OPTIONS (
1168 Status OUT NOCOPY VARCHAR2,
1169 UserId IN NUMBER,
1170 ResponsibilityId IN NUMBER,
1171 PlanId IN NUMBER,
1172 ItemSimulationSetId IN NUMBER default NULL,
1173 Overwrite IN VARCHAR2 default 'All',
1174 PurgeAllSchsFlag IN VARCHAR2,
1175 GlobalDmdSchs IN MscGlbDmdSchTbl default NULL,
1176 LocalDmdSchs IN MscLocDmdSchTbl default NULL,
1177 LocalSupSchs IN MscLocSupSchTbl default NULL
1178 ) AS
1179 l_String VARCHAR2(100);
1180 l_OrgId NUMBER;
1181 l_InsId NUMBER;
1182 l_PlanName VARCHAR2(10);
1183 l_Overwrite NUMBER;
1184 BEGIN
1185
1186 -- dbms_output.put_line('Matthew: Init');
1187
1188 -- init global variables
1189 g_IGlbDmdSchTbl := MscIGlbDmdSchTbl();
1190 g_ILocDmdSchTbl := MscILocDmdSchTbl();
1191 g_ILocSupSchTbl := MscILocSupSchTbl();
1192
1193
1194 -- check user id and responsibility
1195 MSC_WS_COMMON.VALIDATE_USER_RESP(l_String, UserId, ResponsibilityId);
1196 IF (l_String <> 'OK') THEN
1197 Status := l_String;
1198 RETURN;
1199 END IF;
1200
1201 -- check plan id
1202 BEGIN
1203 l_String := MSC_WS_COMMON.VALIDATE_PLAN_ID(l_OrgId, l_InsId, l_PlanName, PlanId);
1204 IF (l_String <> 'OK') THEN
1205 Status := l_String;
1206 RETURN;
1207 END IF;
1208 EXCEPTION WHEN others THEN
1209 g_ErrorCode := 'ERROR_UNEXPECTED_00021';
1210 raise;
1211 END;
1212
1213 -- check plan type
1214 l_String := VALIDATE_PLAN_TYPE(PlanId);
1215 IF (l_String <> 'OK') THEN
1216 Status := l_String;
1217 RETURN;
1218 END IF;
1219
1220 -- validate item simulation set id
1221 BEGIN
1222 l_String := MSC_WS_COMMON.VALIDATE_SIMULATION_SET_ID(ItemSimulationSetId);
1223 IF (l_String <> 'OK') THEN
1224 Status := l_String;
1225 RETURN;
1226 END IF;
1227 EXCEPTION WHEN others THEN
1228 g_ErrorCode := 'ERROR_UNEXPECTED_00023';
1229 raise;
1230 END;
1231
1232 -- Overwrite is restricted to 'All', 'Outside PTF' or 'None' by xsd.
1233 l_Overwrite := MSC_WS_COMMON.CONVERT_OVERWRITE(Overwrite);
1234
1235 -- validate global demand schedules
1236 l_String := VALIDATE_GLB_DMD_SCHS(GlobalDmdSchs, l_PlanName);
1237 IF (l_String <> 'OK') THEN
1238 Status := l_String;
1239 RETURN;
1240 END IF;
1241
1242 -- validate local demand schedules
1243 l_String := VALIDATE_LOC_DMD_SCHS(LocalDmdSchs, PlanId, l_PlanName);
1244 IF (l_String <> 'OK') THEN
1245 Status := l_String;
1246 RETURN;
1247 END IF;
1248
1249 -- validate local supply schedules
1250 l_String := VALIDATE_LOC_SUP_SCHS(LocalSupSchs, PlanId, l_PlanName);
1251 IF (l_String <> 'OK') THEN
1252 Status := l_String;
1253 RETURN;
1254 END IF;
1255
1256 -- if PurgeAllSchsFlag is set, purge all global demand schedules,
1257 -- local demand schedule and local supply schedules
1258 IF MSC_WS_COMMON.BOOL_TO_NUMBER(PurgeAllSchsFlag) = MSC_UTIL.SYS_YES THEN
1259 BEGIN
1260 MSC_WS_COMMON.PURGE_ALL_SCHEDULES(PlanId);
1261 EXCEPTION WHEN others THEN
1262 g_ErrorCode := 'ERROR_UNEXPECTED_00031';
1263 raise;
1264 END;
1265 END IF;
1266
1267 -- update item simulation set and overwrite
1268 BEGIN
1269 MSC_WS_COMMON.UPDATE_PLAN_OPTIONS(PlanId, ItemSimulationSetId, l_Overwrite);
1270 EXCEPTION WHEN others THEN
1271 g_ErrorCode := 'ERROR_UNEXPECTED_00032';
1272 raise;
1273 END;
1274
1275 -- set all global/local demand/supply schedules
1276 IF MSC_WS_COMMON.BOOL_TO_NUMBER(PurgeAllSchsFlag) = MSC_UTIL.SYS_YES THEN
1277 BEGIN
1278 l_String := MSC_WS_COMMON.INSERT_ALL_SCHEDULES(
1279 PlanId, l_InsId, UserId,
1280 g_IGlbDmdSchTbl, g_ILocDmdSchTbl, g_ILocSupSchTbl);
1281 EXCEPTION WHEN others THEN
1282 g_ErrorCode := 'ERROR_UNEXPECTED_00033';
1283 raise;
1284 END;
1285 ELSE
1286 BEGIN
1287 l_String := MSC_WS_COMMON.INSERT_OR_UPDATE_ALL_SCHS(
1288 PlanId, l_InsId, UserId,
1289 g_IGlbDmdSchTbl, g_ILocDmdSchTbl, g_ILocSupSchTbl);
1290 EXCEPTION WHEN others THEN
1291 g_ErrorCode := 'ERROR_UNEXPECTED_00034';
1292 raise;
1293 END;
1294 END IF;
1295
1296 IF (l_String <> 'OK') THEN
1297 Status := l_String;
1298 RETURN;
1299 ELSE
1300 Status := 'SUCCESS';
1301 END IF;
1302
1303 COMMIT;
1304
1305 EXCEPTION
1306 WHEN others THEN
1307 -- Status := 'Failed '||fnd_message.get;
1308 Status := g_ErrorCode;
1309 ROLLBACK;
1310
1311 END SET_ASCP_PLAN_OPTIONS;
1312
1313 PROCEDURE SET_ASCP_PLAN_OPTIONS_PUBLIC (
1314 Status OUT NOCOPY VARCHAR2,
1315 UserName IN VARCHAR2,
1316 RespName IN VARCHAR2,
1317 RespApplName IN VARCHAR2,
1318 SecurityGroupName IN VARCHAR2,
1319 Language IN VARCHAR2,
1320 PlanId IN NUMBER,
1321 ItemSimulationSetId IN NUMBER default NULL,
1322 Overwrite IN VARCHAR2 default 'All',
1323 PurgeAllSchsFlag IN VARCHAR2,
1324 GlobalDmdSchs IN MscGlbDmdSchTbl default NULL,
1325 LocalDmdSchs IN MscLocDmdSchTbl default NULL,
1326 LocalSupSchs IN MscLocSupSchTbl default NULL
1327 ) AS
1328 userid number;
1329 respid number;
1330 l_String VARCHAR2(30);
1331 error_tracking_num number;
1332 l_SecutirtGroupId NUMBER;
1333 BEGIN
1334 error_tracking_num :=2010;
1335 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
1336 IF (l_String <> 'OK') THEN
1337 Status := l_String;
1338 RETURN;
1339 END IF;
1340
1341 error_tracking_num :=2030;
1342 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSCFPPMR-SCP',l_SecutirtGroupId);
1343 IF (l_String <> 'OK') THEN
1344 Status := l_String;
1345 RETURN;
1346 END IF;
1347 error_tracking_num :=2040;
1348
1349
1350 SET_ASCP_PLAN_OPTIONS ( Status, userId , respid, PlanId, ItemSimulationSetId, Overwrite, PurgeAllSchsFlag, GlobalDmdSchs, LocalDmdSchs, LocalSupSchs );
1351
1352
1353
1354 EXCEPTION
1355 WHEN others THEN
1356 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
1357
1358 return;
1359
1360
1361 END SET_ASCP_PLAN_OPTIONS_PUBLIC;
1362
1363 END MSC_WS_ASCP;
1364
[Home] [Help]