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