[Home] [Help]
PACKAGE BODY: APPS.MSC_WS_DRP
Source
1 PACKAGE BODY MSC_WS_DRP AS
2 /* $Header: MSCWDRPB.pls 120.10 2008/03/20 15:56:53 bnaghi noship $ */
3
4
5 g_IGlbDmdSchTbl MscIGlbDmdSchTbl; -- store all global demand schediles 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_DRP_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 ) AS
26 l_val_result VARCHAR2(30);
27 l_val_planId NUMBER;
28 l_val_planName VARCHAR2(10);
29 l_val_launchSnapshot NUMBER;
30 l_val_launchPlanner NUMBER;
31 l_val_netchange NUMBER;
32 l_val_anchorDate DATE;
33 l_val_archivePlan NUMBER;
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_orgId NUMBER;
39 l_val_instanceId NUMBER;
40 error_tracking_num NUMBER;
41 BEGIN
42 error_tracking_num := 1010;
43
44 -- validate and initialize apps
45 MSC_WS_COMMON.VALIDATE_USER_RESP(l_val_result, userId, responsibilityId);
46
47 IF (l_val_result <> 'OK') THEN
48 processId := -1;
49 status := l_val_result;
50 RETURN;
51 END IF;
52
53 error_tracking_num := 1020;
54
55 -- validate planId
56 BEGIN
57 SELECT plans.compile_designator, plans.organization_id, plans.sr_instance_id,
58 desig.inventory_atp_flag, desig.production
59 INTO l_val_planName,l_val_orgId,l_val_instanceId,l_val_inventory_atp_flag,
60 l_val_production
61 FROM msc_plans plans, msc_designators desig
62 WHERE plans.curr_plan_type = 5
63 AND plans.organization_id = desig.organization_id
64 AND plans.sr_instance_id = desig.sr_instance_id
65 AND plans.compile_designator = desig.designator
66 AND NVL(desig.disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE)
67 AND plans.plan_id = planId;
68
69
70 EXCEPTION
71 WHEN no_data_found THEN
72 processid := -1;
73 status := 'INVALID_PLANID';
74 RETURN;
75 END;
76
77
78 error_tracking_num := 1030;
79 -- validate anchor date
80 BEGIN
81 SELECT calendar_date
82 INTO l_val_anchorDate
83 FROM msc_calendar_dates dates,
84 msc_trading_partners mtp
85 WHERE dates.calendar_code = mtp.calendar_code
86 AND dates.exception_set_id = mtp.calendar_exception_set_id
87 AND mtp.sr_instance_id = dates.sr_instance_id
88 AND mtp.sr_tp_id = l_val_orgid
89 AND mtp.sr_instance_id = l_val_instanceid
90 AND dates.calendar_date <= TRUNC(sysdate)
91 AND dates.calendar_date = anchorDate;
92
93 EXCEPTION
94 WHEN no_data_found THEN
95 processid := -1;
96 status := 'INVALID_ANCHORDATE';
97 RETURN;
98 END;
99
100
101 error_tracking_num := 1040;
102 -- expected values are Y and N , if it is Y then converted to SYS_YES ,others to SYS_NO
103 BEGIN
104 SELECT DECODE(launchSnapshot,
105 'Y' ,MSC_WS_COMMON.SYS_YES, MSC_WS_COMMON.SYS_NO)
106 INTO l_val_launchSnapshot
107 FROM DUAL;
108
109 END;
110
111
112 error_tracking_num := 1050;
113 -- validate launchPlanner
114 BEGIN
115 SELECT lookup_code
116 INTO l_val_launchPlanner
117 FROM MFG_LOOKUPS
118 WHERE lookup_type = 'SYS_YES_NO'
119 AND ((lookup_code = 1 AND l_val_launchSnapshot = 1) OR
120 (l_val_launchSnapshot = 2))
121 AND lookup_code = decode(launchPlanner, 'Y', msc_ws_common.sys_yes, msc_ws_common.sys_no);
122 EXCEPTION
123 WHEN no_data_found THEN
124 processid := -1;
125 status := 'INVALID_LAUNCH_PLANNER';
126 RETURN;
127 END;
128
129 error_tracking_num := 1055;
130
131 BEGIN
132 SELECT decode(archiveCurrVersPlan, 'Y', msc_ws_common.sys_yes, msc_ws_common.sys_no)
133 INTO l_val_archivePlan
134 FROM dual;
135
136 END;
137 -- netchange hidden parameter always set to the value 2, which is sys_no
138 l_val_netchange := msc_ws_common.sys_no;
139
140
141
142 -- populating PLAN_TYPE_DUMMY hidden parameter
143 -- Original default logic is "SELECT inventory_atp_flag from msc_designators d
144 -- where d.designator = :$FLEX$.MSC_SRS_SRO_NAME_LAUNCH_1 and d.inventory_atp_flag = 1"
145 -- Which pretty much meant this flag is set to either NULL or 1
146 IF (l_val_inventory_atp_flag <> 1)
147 THEN l_val_inventory_atp_flag := NULL;
148 END IF;
149
150
151 error_tracking_num := 1060;
152 -- validating enable24x7atp
153 BEGIN
154 SELECT lookup_code
155 INTO l_val_enable24x7Atp
156 FROM MFG_LOOKUPS
157 WHERE LOOKUP_TYPE = 'MSC_24X7_PURGE'
158 AND (( LOOKUP_CODE IN (1,2,3) and NVL(l_val_inventory_atp_flag,2) = 1 )
159 OR LOOKUP_CODE=2)
160 AND LOOKUP_CODE = decode(enable24x7atp,'YES_PURGE',1,'NO',2,'YES_NO_PURGE',3,-1);
161 EXCEPTION
162 WHEN no_data_found THEN
163 processid := -1;
164 status := 'INVALID_ENABLE24X7ATP';
165 RETURN;
166 END;
167
168
169 -- populating RESCHEDULE_DUMMY hidden parameter
170 -- Similar default logic as l_val_inventory_atp_flag, "SELECT production from
171 -- msc_designators d where d.designator = :$FLEX$.MSC_SRS_SRO_NAME_LAUNCH_1 and d.production = 1"
172 IF (l_val_production <> 1)
173 THEN l_val_production := NULL;
174 END IF;
175
176 error_tracking_num := 1070;
177 -- validating releaseReschedules
178 BEGIN
179 SELECT lookup_code
180 INTO l_val_releaseReschedules
181 FROM MFG_LOOKUPS
182 WHERE lookup_type='SYS_YES_NO'
183 AND (NVL(l_val_production,2)=1 or lookup_code=2)
184 AND lookup_code = decode(releaseReschedules,'Y',msc_ws_common.sys_yes, msc_ws_common.sys_no);
185 EXCEPTION
186 WHEN no_data_found THEN
187 processid := -1;
188 status := 'INVALID_RELEASE_RESCHEDULES';
189 RETURN;
190 END;
191
192 processId := FND_REQUEST.SUBMIT_REQUEST(
193 'MSC', -- application
194 'MSCSLPPR6', -- program
195 NULL, -- description
196 NULL, -- start_time
197 FALSE, -- sub_request
198 l_val_planName,
199 planId,
200 l_val_launchSnapshot,
201 l_val_launchPlanner,
202 l_val_netchange, -- netchange,
203 to_char(l_val_anchorDate, 'YYYY/MM/DD HH24:MI:SS'),
204 l_val_archivePlan,
205 l_val_inventory_atp_flag, -- plan_type_dummy param
206 l_val_enable24x7Atp,
207 l_val_production, -- rescheduleDummy VARCHAR2
208 l_val_releaseReschedules -- release
209 );
210
211 IF (processId = 0) then
212 processId := -1;
213 status := 'ERROR_SUBMIT';
214 return;
215 END IF;
216
217 status := 'SUCCESS';
218
219 EXCEPTION
220 WHEN others THEN
221 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
222 processId := -1;
223 return;
224 END LAUNCH_DRP_BATCH;
225
226 PROCEDURE LAUNCH_DRP_BATCH_PUBLIC (
227 processId OUT NOCOPY NUMBER,
228 status OUT NOCOPY VARCHAR2,
229 UserName IN VARCHAR2,
230 RespName IN VARCHAR2,
231 RespApplName IN VARCHAR2,
232 SecurityGroupName IN VARCHAR2,
233 Language IN VARCHAR2,
234 planId IN NUMBER,
235 launchSnapshot IN VARCHAR2,
236 launchPlanner IN VARCHAR2,
237 anchorDate IN DATE,
238 archiveCurrVersPlan IN VARCHAR2,
239 enable24x7Atp IN VARCHAR2,
240 releaseReschedules IN VARCHAR2
241
242 ) AS
243
244 userid number;
245 respid number;
246 l_String VARCHAR2(30);
247 error_tracking_num number;
248 l_SecutirtGroupId NUMBER;
249 BEGIN
250 error_tracking_num :=2010;
251 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
252 IF (l_String <> 'OK') THEN
253 Status := l_String;
254 RETURN;
255 END IF;
256
257
258 error_tracking_num :=2030;
259 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSCFNDRP',l_SecutirtGroupId);
260 IF (l_String <> 'OK') THEN
261 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSCFPCDP', l_SecutirtGroupId);
262 IF (l_String <> 'OK') THEN
263 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSC_ORG_FNDRSRUN_LAUNCH_DRP',l_SecutirtGroupId);
264 IF (l_String <> 'OK') THEN
265 Status := l_String;
266 RETURN;
267 END IF;
268 END IF;
269 END IF;
270
271 error_tracking_num :=2040;
272 LAUNCH_DRP_BATCH ( processId, status, userId ,respid, planId , launchSnapshot ,launchPlanner, anchorDate ,archiveCurrVersPlan, enable24x7Atp, releaseReschedules );
273
274
275
276 EXCEPTION
277 WHEN others THEN
278 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
279 processId := -1;
280 return;
281 END LAUNCH_DRP_BATCH_PUBLIC;
282
283 -- =============================================================
284 -- Desc: Please see package spec for description
285 -- =============================================================
286 PROCEDURE RELEASE_DRP ( req_id OUT NOCOPY REQTBLTYP,
287 status OUT NOCOPY VARCHAR2,
288 userId IN NUMBER,
289 responsibilityId IN NUMBER,
290 planId IN NUMBER,
291 release_time_fence_anchor_date IN VARCHAR2
292 ) AS
293 error_tracking_num NUMBER;
294 l_val_result VARCHAR2(30);
295 l_val_planid NUMBER;
296 l_val_planName VARCHAR2(10);
297 l_val_orgid NUMBER;
298 l_val_instanceid NUMBER;
299 l_rel_time_window VARCHAR2(1);
300 RETCODE NUMBER;
301 ERRMSG VARCHAR2(200);
302 l_req_id MSC_RELEASE_PK.REQTBLTYP;
303
304 i number;
305 j number :=1;
306
307 BEGIN
308 req_id := REQTBLTYP();
309 error_tracking_num :=2010;
310 msc_ws_common.validate_user_resp(l_val_result, userid, responsibilityid);
311
312 IF(l_val_result <> 'OK') THEN
313 status := l_val_result;
314 RETURN;
315 END IF;
316
317
318 error_tracking_num :=2020;
319 -- check plan id
320 BEGIN
321 SELECT plans.compile_designator
322 INTO l_val_planName
323 FROM msc_plans plans, msc_designators desig
324 WHERE plans.curr_plan_type = 5
325 AND plans.organization_id = desig.organization_id
326 AND plans.sr_instance_id = desig.sr_instance_id
327 AND plans.compile_designator = desig.designator
328 AND NVL(desig.disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE)
329 AND plans.plan_id = planId;
330 EXCEPTION
331 WHEN no_data_found THEN
332 status := 'INVALID_PLANID';
333 RETURN;
334 END;
335
336 error_tracking_num :=2010;
337 if (release_time_fence_anchor_date = 'PLAN_START_DATE')THEN
338 l_rel_time_window := 'Y';
339 elsif (release_time_fence_anchor_date = 'CURRENT_DATE')THEN
340 l_rel_time_window := 'N';
341 elsE
342
343
344 status := 'INVALID_RELEASE_TIME_FENCE_ANCHOR_DATE';
345 RETURN;
346
347 end if;
348
349 error_tracking_num :=2030;
350 msc_release_pk.msc_web_service_release(planId, l_rel_time_window, RETCODE, ERRMSG,l_REQ_ID);
351 IF (RETCODE = 2) THEN
352 status := 'ERROR_RELEASE '|| ERRMSG;
353 RETURN;
354 END IF;
355
356 error_tracking_num :=2040;
357 FOR i IN 1..l_REQ_ID.count LOOP
358 if (l_REQ_ID(i).ReqID is not null) then
359 req_id.extend;
360 req_id(j) := reqrectyp(l_REQ_ID(i).instanceCode,l_REQ_ID(i).ReqID, l_REQ_ID(i).ReqType);
361 j := j + 1;
362 end if;
363
364 END LOOP;
365
366
367 status := 'SUCCESS';
368
369
370 EXCEPTION
371 WHEN others THEN
372 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
373
374
375 rollback;
376 return;
377
378
379 END RELEASE_DRP;
380
381 PROCEDURE RELEASE_DRP_PUBLIC ( req_id OUT NOCOPY REQTBLTYP,
382 status OUT NOCOPY VARCHAR2,
383 UserName IN VARCHAR2,
384 RespName IN VARCHAR2,
385 RespApplName IN VARCHAR2,
386 SecurityGroupName IN VARCHAR2,
387 Language IN VARCHAR2,
388 planId IN NUMBER,
389 release_time_fence_anchor_date IN VARCHAR2
390 ) AS
391 userid number;
392 respid number;
393 l_String VARCHAR2(30);
394 error_tracking_num number;
395 l_SecutirtGroupId NUMBER;
396 BEGIN
397 req_id := REQTBLTYP();
398 error_tracking_num :=2010;
399 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
400 IF (l_String <> 'OK') THEN
401 Status := l_String;
402 RETURN;
403 END IF;
404
405 error_tracking_num :=2030;
406
407 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSCFNDRP',l_SecutirtGroupId);
408 IF (l_String <> 'OK') THEN
409 Status := l_String;
410 RETURN;
411 END IF;
412 -- END IF;
413 error_tracking_num :=2040;
414
415 RELEASE_DRP ( req_id , status, userId ,respid, planId , release_time_fence_anchor_date );
416
417
418
419 EXCEPTION
420 WHEN others THEN
421 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
422
423 return;
424 END RELEASE_DRP_PUBLIC;
425
426 -- =============================================================
427 --
428 -- SET_DRP_PLAN_OPTIONS and its private helper functions.
429 --
430 -- Un-handled exceptions generate error tokens in the
431 -- format of ERROR_UNEXPECTED_#####.
432 -- The possible values are:
433 -- 00101 - SET_DRP_PLAN_OPTIONS/MSC_WS_COMMON.VALIDATE_PLAN_ID
434 -- 00102 - SET_DRP_PLAN_OPTIONS/VALIDATE_PLAN_TYPE
435 -- 00103 - SET_DRP_PLAN_OPTIONS/MSC_WS_COMMON.VALIDATE_SIMULATION_SET_ID
436 -- 00104 - SET_DRP_PLAN_OPTIONS/VALIDATE_GLB_DMD_SCHS/MSC_WS_COMMON.VALIDATE_G_DMD_SCH_ID
437 -- 00105 - SET_DRP_PLAN_OPTIONS/VALIDATE_GLB_DMD_SCHS/MSC_WS_COMMON.VALIDATE_CONSUM_LVL (goe / item)
438 -- 00106 - SET_DRP_PLAN_OPTIONS/VALIDATE_LOC_DMD_SCHS/VALIDATE_L_DMD_SCH_ID
439 -- 00107 - SET_DRP_PLAN_OPTIONS/VALIDATE_LOC_DMD_SCHS/MSC_WS_COMMON.PLAN_CONTAINS_THIS_ORG
440 -- 00108 - SET_DRP_PLAN_OPTIONS/MSC_WS_COMMON.VALIDATE_LOC_SUP_SCHS
441 -- 00109 - SET_DRP_PLAN_OPTIONS/MSC_WS_COMMON.PURGE_ALL_SCHEDULES
442 -- 00110 - SET_DRP_PLAN_OPTIONS/MSC_WS_COMMON.UPDATE_PLAN_OPTIONS
443 -- 00111 - SET_DRP_PLAN_OPTIONS/MSC_WS_COMMON.INSERT_ALL_SCHEDULES
444 -- 00112 - SET_DRP_PLAN_OPTIONS/MSC_WS_COMMON.INSERT_OR_UPDATE_ALL_SCHS
445 -- =============================================================
446
447 -- =============================================================
448 -- Desc: Validate plan id, copy the where clause from LAUNCH_DRP_BATCH
449 --
450 -- Input:
451 -- PlanId Plan Id.
452 --
453 -- Output: The possible return statuses are:
454 -- OK
455 -- INVALID_PLANID
456 -- =============================================================
457 FUNCTION VALIDATE_PLAN_ID(
458 OrgId OUT NOCOPY NUMBER,
459 InsId OUT NOCOPY NUMBER,
460 PlanName OUT NOCOPY VARCHAR2,
461 PlanId IN NUMBER
462 ) RETURN VARCHAR2 AS
463 l_ReturnString VARCHAR2(100);
464 BEGIN
465 BEGIN
466 SELECT
467 plans.organization_id,
468 plans.sr_instance_id,
469 plans.compile_designator
470 INTO
471 OrgId,
472 InsId,
473 PlanName
474 FROM
475 msc_plans plans,
476 msc_designators desig
477 WHERE
478 plans.organization_id = desig.organization_id AND
479 plans.sr_instance_id = desig.sr_instance_id AND
480 plans.compile_designator = desig.designator AND
481 NVL(desig.disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE) AND
482 -- plans.organization_selection <> 1 AND
483 plans.curr_plan_type in (1,2,3,4,5,8,9) AND
484 plans.plan_id <> -1 AND
485 -- NVL(plans.copy_plan_id,-1) = -1 AND
486 -- NVL(desig.copy_designator_id, -1) = -1 AND
487 plans.plan_id = PlanId;
488 EXCEPTION WHEN NO_DATA_FOUND THEN
489 RETURN 'INVALID_PLANID';
490 WHEN others THEN
491 g_ErrorCode := 'ERROR_UNEXPECTED_00101';
492 raise;
493 END;
494
495 RETURN 'OK';
496 END VALIDATE_PLAN_ID;
497
498 -- =============================================================
499 -- Desc: Validate plan type is DRP
500 -- Input:
501 -- PlanId plan id.
502 --
503 -- Output: The possible return statuses are:
504 -- OK
505 -- INVALID_PLAN_TYPE
506 -- =============================================================
507 FUNCTION VALIDATE_PLAN_TYPE( PlanId IN NUMBER ) RETURN VARCHAR2 AS
508 l_Dummy NUMBER;
509 BEGIN
510 BEGIN
511 SELECT
512 1 INTO l_Dummy
513 FROM
514 msc_plans
515 WHERE
516 curr_plan_type = 5 AND
517 plan_id = PlanId;
518 EXCEPTION WHEN NO_DATA_FOUND THEN
519 RETURN 'INVALID_PLAN_TYPE';
520 WHEN others THEN
521 g_ErrorCode := 'ERROR_UNEXPECTED_00102';
522 raise;
523 END;
524
525 RETURN 'OK';
526 END VALIDATE_PLAN_TYPE;
527
528 -- =============================================================
529 -- Desc: validate global demand schedules
530 -- Input:
531 -- SchTable Global demand schedules.
532 -- PlanName Plan name.
533 --
534 -- Output: The possible return statuses are:
535 -- OK
536 -- INVALID_GLOBALDMDSCHS_DMD_SCH_ID
537 -- INVALID_GLOBALDMDSCHS_SHP_TO_CONSUMPTION_LVL
538 -- =============================================================
539 FUNCTION VALIDATE_GLB_DMD_SCHS(
540 SchTable IN MscGlbDmdSchTbl,
541 PlanName IN VARCHAR2
542 ) RETURN VARCHAR2 AS
543 l_ReturnString VARCHAR2(100);
544 l_String VARCHAR2(100);
545 BEGIN
546 IF SchTable IS NOT NULL AND SchTable.count > 0 THEN
547 FOR I IN SchTable.first..SchTable.last
548 LOOP
549 -- validate demand schedule id
550 BEGIN
551 l_String := MSC_WS_COMMON.VALIDATE_G_DMD_SCH_ID(SchTable(I).DmdSchId, PlanName);
552 IF (l_String <> 'OK') THEN
553 RETURN l_String;
554 END IF;
555 EXCEPTION WHEN others THEN
556 g_ErrorCode := 'ERROR_UNEXPECTED_00104';
557 raise;
558 END;
559
560 -- validate ship to consumption level
561 BEGIN
562 l_String := MSC_WS_COMMON.VALIDATE_CONSUM_LVL(
563 SchTable(I).ShipToConsumptionLvl,
564 SchTable(I).DmdSchId);
565 IF (l_String <> 'OK') THEN
566 RETURN 'INVALID_GLOBALDMDSCHS_SHP_TO_CONSUMPTION_LVL';
567 END IF;
568 EXCEPTION WHEN others THEN
569 g_ErrorCode := 'ERROR_UNEXPECTED_00105';
570 raise;
571 END;
572 g_IGlbDmdSchTbl.extend;
573 g_IGlbDmdSchTbl(g_IGlbDmdSchTbl.count) :=
574 MscIGlbDmdSchRec(SchTable(I).DmdSchId,
575 SchTable(I).ShipToConsumptionLvl,
576 1, -- input_type
577 7 -- designator_type
578 );
579 END LOOP;
580 END IF;
581
582 l_ReturnString := 'OK';
583 RETURN l_ReturnString;
584 END VALIDATE_GLB_DMD_SCHS;
585
586 -- =============================================================
587 -- Desc: Validate loal demand schedule id
588 -- Input:
589 -- SchId local demand schedule id.
590 -- OrgId organization id.
591 -- InsId sr instance id.
592 -- PlanName plan name.
593 --
594 -- Output: The possible return statuses are:
595 -- OK
596 -- INVALID_LOCALDMDSCHS_DMD_SCH_ID
597 -- =============================================================
598 FUNCTION VALIDATE_L_DMD_SCH_ID(
599 DesigType OUT NOCOPY NUMBER,
600 FcstShipTo OUT NOCOPY NUMBER,
601 SchId IN NUMBER,
602 OrgId IN NUMBER,
603 InsId IN NUMBER,
604 PlanName IN VARCHAR2
605 ) RETURN VARCHAR2 AS
606 BEGIN
607 BEGIN
608 SELECT
609 desig.designator_type,
610 decode(desig.designator_type, 6, desig.update_type, -1)
611 INTO
612 DesigType,
613 FcstShipTo
614 FROM
615 msc_designators desig,
616 fnd_lookups lu
617 WHERE
618 ( (desig.designator_type = 6 and desig.forecast_set_id is null) OR
619 (desig.designator_type in (5,8)) ) AND
620 trunc(nvl(desig.disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
621 ( desig.designator <> PlanName OR desig.designator_type = 1 ) AND
622 desig.organization_id = OrgId AND
623 desig.sr_instance_id = InsId AND
624 desig.designator_id = SchId AND
625 lu.lookup_code(+) = desig.update_type AND
626 lu.lookup_type(+) = 'MSC_SHIP_TO'
627 UNION
628 SELECT
629 7, -1
630 FROM
631 msd_dp_ascp_scenarios_v
632 WHERE
633 scenario_name <> PlanName AND
634 sr_instance_id = InsId AND
635 scenario_id = SchId AND
636 last_revision IS NOT NULL
637 UNION
638 SELECT
639 desig.designator_type,
640 decode(desig.designator_type, 6, desig.update_type, -1)
641 FROM
642 msc_designators desig,
643 msc_plan_organizations_v mpo,
644 fnd_lookups lu
645 WHERE
646 ( (desig.designator_type = 6 AND desig.forecast_set_id is null) OR
647 (desig.designator_type in (5,8)) ) AND
648 trunc(nvl(desig.disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
649 mpo.organization_id = desig.organization_id AND
650 mpo.sr_instance_id = desig.sr_instance_id AND
651 mpo.compile_designator = desig.designator AND
652 mpo.planned_organization = OrgId AND
653 mpo.sr_instance_id = InsId AND
654 desig.designator <> PlanName AND
655 desig.designator_id = SchId AND
656 lu.lookup_code(+) = desig.update_type AND
657 lu.lookup_type(+) = 'MSC_SHIP_TO'
658 UNION
659 SELECT
660 desig.designator_type,
661 decode(desig.designator_type, 6, desig.update_type, -1)
662 FROM
663 msc_designators desig,
664 msc_item_sourcing mis,
665 msc_plans mp,
666 fnd_lookups lu
667 WHERE
668 ( (desig.designator_type = 6 AND desig.forecast_set_id is null) OR
669 (desig.designator_type IN (5,8)) ) AND
670 trunc(nvl(desig.disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
671 mis.plan_id = mp.plan_id AND
672 mp.organization_id = desig.organization_id AND
673 mp.sr_instance_id = desig.sr_instance_id AND
674 mp.compile_designator = desig.designator AND
675 mis.source_organization_id = OrgId AND
676 mis.sr_instance_id2 = InsId AND
677 desig.designator <> PlanName AND
678 desig.designator_id = SchId AND
679 lu.lookup_code(+) = desig.update_type AND
680 lu.lookup_type(+) = 'MSC_SHIP_TO';
681 EXCEPTION WHEN NO_DATA_FOUND THEN
682 RETURN 'INVALID_LOCALDMDSCHS_DMD_SCH_ID';
683 WHEN others THEN
684 g_ErrorCode := 'ERROR_UNEXPECTED_00106';
685 raise;
686 END;
687
688 RETURN 'OK';
689 END VALIDATE_L_DMD_SCH_ID;
690
691 -- =============================================================
692 -- Desc: validate local demand schedules
693 -- Input:
694 -- SchTable Local demand schedules.
695 -- PlanName Plan name.
696 --
697 -- Output: The possible return statuses are:
698 -- OK
699 -- INVALID_LOCALDMDSCHS_ORGID
700 -- INVALID_LOCALDMDSCHS_DMD_SCH_ID
701 -- INVALID_LOCALDMDSCHS_SHP_TO_CONSUMPTION_LVL
702 -- =============================================================
703 FUNCTION VALIDATE_LOC_DMD_SCHS(
704 SchTable IN MscLocSRPDmdSchTbl,
705 PlanId IN NUMBER,
706 PlanName IN VARCHAR2
707 ) RETURN VARCHAR2 AS
708 l_ReturnString VARCHAR2(100);
709 l_OrgInsId NUMBER;
710 l_DesigType NUMBER;
711 l_FcstShipTo NUMBER;
712 l_ShipTo NUMBER;
713 BEGIN
714 IF SchTable IS NOT NULL AND SchTable.count > 0 THEN
715 FOR I IN SchTable.first..SchTable.last
716 LOOP
717 -- validate organization id
718 BEGIN
719 l_ReturnString := MSC_WS_COMMON.PLAN_CONTAINS_THIS_ORG(l_OrgInsId, SchTable(I).OrgId, PlanId);
720 IF (l_ReturnString <> 'OK') THEN
721 -- overwrite the error token here.
722 l_ReturnString := 'INVALID_LOCALDMDSCHS_ORGID';
723 RETURN l_ReturnString;
724 END IF;
725 EXCEPTION WHEN others THEN
726 g_ErrorCode := 'ERROR_UNEXPECTED_00107';
727 raise;
728 END;
729
730 -- validate demand schedule id
731 l_ReturnString := VALIDATE_L_DMD_SCH_ID(
732 l_DesigType,
733 l_FcstShipTo,
734 SchTable(I).DmdSchId,
735 SchTable(I).OrgId,
736 l_OrgInsId,
737 PlanName);
738 IF (l_ReturnString <> 'OK') THEN
739 RETURN l_ReturnString;
740 END IF;
741
742 -- validate ship to consumption level
743 -- if l_DesigType = 7 , DPSCN, do validation
744 -- else if l_DesigType = 6, FCST, copy forecast_ship_to to ship to
745 -- else default to null, 1 MDS, 2 MPS, 3 MRP, 4 MPP, 5 IP, 8 DPP, 9 MNTDS, 10 MFGDS
746 IF l_DesigType = 7 THEN
747 IF SchTable(I).ShipToConsumptionLvl IS NULL THEN
748 RETURN 'INVALID_LOCALDMDSCHS_SHP_TO_CONSUMPTION_LVL';
749 END IF;
750 l_ShipTo := SchTable(I).ShipToConsumptionLvl;
751 l_ReturnString := MSC_WS_COMMON.VALIDATE_CONSUM_LVL(
752 SchTable(I).ShipToConsumptionLvl,
753 SchTable(I).DmdSchId);
754 IF (l_ReturnString <> 'OK') THEN
755 RETURN 'INVALID_LOCALDMDSCHS_SHP_TO_CONSUMPTION_LVL';
756 END IF;
757 ELSE
758 IF SchTable(I).ShipToConsumptionLvl IS NOT NULL THEN
759 RETURN 'INVALID_LOCALDMDSCHS_SHP_TO_CONSUMPTION_LVL';
760 END IF;
761 IF l_DesigType = 6 THEN
762 l_ShipTo := l_FcstShipTo;
763 ELSE
764 l_ShipTo := NULL;
765 END IF;
766 END IF;
767
768 -- I don't see any check boxes for include target demands and inter plant in UI ???
769
770 g_ILocDmdSchTbl.extend;
771 g_ILocDmdSchTbl(g_ILocDmdSchTbl.count) :=
772 MscILocDmdSchRec(SchTable(I).OrgId,
773 SchTable(I).DmdSchId,
774 2, -- IncludeTargetDmd is hard coded to 2
775 l_ShipTo,
776 NULL,
777 1, -- input_type
778 l_DesigType -- designator_type
779 );
780 END LOOP;
781 END IF;
782
783 l_ReturnString := 'OK';
784 RETURN l_ReturnString;
785 END VALIDATE_LOC_DMD_SCHS;
786
787 -- =============================================================
788 -- Desc: This procedure is invoked from web service to
789 -- updates Plan Options for DRP plans.
790 -- Input:
791 -- UserId User ID.
792 -- ResponsibilityId Responsibility Id.
793 -- PlanId Plan Id.
794 -- ItemSimulationSet Item Simulation Set.
795 -- Overwrite Overwrite. Expected values are All,
796 -- Outside PTF or None.
797 -- PurgeAllSchsFlag There is no such parameter in UI. Allowed
798 -- input is Y or N. This is a new parameter
799 -- to control how Global Demand Schedules, Local
800 -- Demand Schedules and Local Supply Schedules
801 -- are updated / inserted. If this flag is set, all
802 -- Global Demand Schedules, Local Demand Schedules and
803 -- Local Supply Schedule will be purged before
804 -- update / insert any demand / supply schedules from
805 -- the input parameters. If this flag is not set, no
806 -- demand / supple schedules will be purged, schedules in
807 -- the input parameters will be updated or inserted.
808 -- GlobalDmdSchs Global Demand Schedules. Each demand schedule contains
809 -- the schedule id and ship to consumption level parameters.
810 -- Although this is not a required parameter, we need both
811 -- id and ShpToConsumptionLvl to define a demand schedule,
812 -- so either both parameters are empty or both are entered.
813 -- LocalDmdSchs Local Demand Schedules. List of all local demand schedules.
814 -- Each local demand schedule contains the organization id,
815 -- demand schedule id and ship to consumption level. Similar to
816 -- Global Demand Schedules, these Three parameters have to be
817 -- LocalSupSchs Supply Schedules.List of local supply schedules. Each local
818 -- supply schedule contains the organization id and supply
819 -- schedule id. Similar to Global Demand Schedules, these
820 -- two parameters have to be either both empty or both entered
821 --
822 -- Output: Procedure returns a status and conc program req id.
823 -- The possible return statuses are:
824 -- SUCCESS if everything is ok
825 -- ERROR_DUP_GLOBALDMDSCH
826 -- ERROR_DUP_LOCALDMDSCH
827 -- ERROR_DUP_LOCALSUPSCH
828 -- ERROR_UNEXPECTED_##### unexpected error
829 -- INVALID_FND_USERID
830 -- INVALID_FND_RESPONSIBILITYID
831 -- INVALID_PLANID invalid source plan id
832 -- INVALID_PLAN_TYPE non DRP plan
833 -- INVALID_SIMULATION_SET_ID
834 -- INVALID_OVERWRITE Only 'Y' or 'N' is allowed.
835 -- INVALID_GLOBALDMDSCHS_DMD_SCH_NAME
836 -- INVALID_GLOBALDMDSCHS_SHP_TO_CONSUMPTION_LVL
837 -- INVALID_LOCALDMDSCHS_ORGID
838 -- INVALID_LOCALDMDSCHS_DMD_SCH_ID
839 -- INVALID_LOCALDMDSCHS_SHP_TO_CONSUMPTION_LVL
840 -- INVALID_LOCALSUPSCHS_ORGID
841 -- INVALID_LOCALSUPSCHS_SUP_SCH_NAME
842 -- =============================================================
843 PROCEDURE SET_DRP_PLAN_OPTIONS (
844 Status OUT NOCOPY VARCHAR2,
845 UserId IN NUMBER,
846 ResponsibilityId IN NUMBER,
847 PlanId IN NUMBER,
848 ItemSimulationSetId IN NUMBER default NULL,
849 Overwrite IN VARCHAR2 default 'All',
850 PurgeAllSchsFlag IN VARCHAR2,
851 GlobalDmdSchs IN MscGlbDmdSchTbl default NULL,
852 LocalDmdSchs IN MscLocSRPDmdSchTbl default NULL,
853 LocalSupSchs IN MscLocSupSchTbl default NULL
854 ) AS
855 l_String VARCHAR2(100);
856 l_OrgId NUMBER;
857 l_InsId NUMBER;
858 l_PlanName VARCHAR2(10);
859 l_Overwrite NUMBER;
860 BEGIN
861 -- dbms_output.put_line('Matthew: Init');
862
863 -- init global variables
864 g_IGlbDmdSchTbl := MscIGlbDmdSchTbl();
865 g_ILocDmdSchTbl := MscILocDmdSchTbl();
866 g_ILocSupSchTbl := MscILocSupSchTbl();
867
868 -- check user id and responsibility
869 MSC_WS_COMMON.VALIDATE_USER_RESP(l_String, UserId, ResponsibilityId);
870 IF (l_String <> 'OK') THEN
871 Status := l_String;
872 RETURN;
873 END IF;
874
875 -- check plan id
876 -- l_String := MSC_WS_COMMON.VALIDATE_PLAN_ID(l_OrgId, l_InsId, l_PlanName, PlanId);
877 l_String := VALIDATE_PLAN_ID(l_OrgId, l_InsId, l_PlanName, PlanId);
878 IF (l_String <> 'OK') THEN
879 Status := l_String;
880 RETURN;
881 END IF;
882
883 -- check plan type
884 l_String := VALIDATE_PLAN_TYPE(PlanId);
885 IF (l_String <> 'OK') THEN
886 Status := l_String;
887 RETURN;
888 END IF;
889
890 -- validate item simulation set id
891 BEGIN
892 l_String := MSC_WS_COMMON.VALIDATE_SIMULATION_SET_ID(ItemSimulationSetId);
893 IF (l_String <> 'OK') THEN
894 Status := l_String;
895 RETURN;
896 END IF;
897 EXCEPTION WHEN others THEN
898 g_ErrorCode := 'ERROR_UNEXPECTED_00103';
899 raise;
900 END;
901
902 l_Overwrite := MSC_WS_COMMON.CONVERT_OVERWRITE(Overwrite);
903
904
905 -- validate global demand schedules
906 l_String := VALIDATE_GLB_DMD_SCHS(GlobalDmdSchs, l_PlanName);
907 IF (l_String <> 'OK') THEN
908 Status := l_String;
909 RETURN;
910 END IF;
911
912 -- validate local demand schedules
913 l_String := VALIDATE_LOC_DMD_SCHS(LocalDmdSchs, PlanId, l_PlanName);
914 IF (l_String <> 'OK') THEN
915 Status := l_String;
916 RETURN;
917 END IF;
918
919 -- validate local supply schedules
920 BEGIN
921 l_String := MSC_WS_COMMON.VALIDATE_LOC_SUP_SCHS(g_ILocSupSchTbl, LocalSupSchs, PlanId, l_PlanName);
922 IF (l_String <> 'OK') THEN
923 Status := l_String;
924 RETURN;
925 END IF;
926 EXCEPTION WHEN others THEN
927 g_ErrorCode := 'ERROR_UNEXPECTED_00108';
928 raise;
929 END;
930
931 -- if PurgeAllSchsFlag is set, purge all global demand schedules,
932 -- local demand schedule and local supply schedules
933 IF MSC_WS_COMMON.BOOL_TO_NUMBER(PurgeAllSchsFlag) = MSC_UTIL.SYS_YES THEN
934 BEGIN
935 MSC_WS_COMMON.PURGE_ALL_SCHEDULES(PlanId);
936 EXCEPTION WHEN others THEN
937 g_ErrorCode := 'ERROR_UNEXPECTED_00109';
938 raise;
939 END;
940 END IF;
941
942 -- update item simulation set and overwrite
943 BEGIN
944 MSC_WS_COMMON.UPDATE_PLAN_OPTIONS(PlanId, ItemSimulationSetId, l_Overwrite);
945 EXCEPTION WHEN others THEN
946 g_ErrorCode := 'ERROR_UNEXPECTED_00110';
947 raise;
948 END;
949
950 -- set all global/local demand/supply schedules
951 IF MSC_WS_COMMON.BOOL_TO_NUMBER(PurgeAllSchsFlag) = MSC_UTIL.SYS_YES THEN
952 BEGIN
953 l_String := MSC_WS_COMMON.INSERT_ALL_SCHEDULES(
954 PlanId, l_InsId, UserId,
955 g_IGlbDmdSchTbl, g_ILocDmdSchTbl, g_ILocSupSchTbl);
956 EXCEPTION WHEN others THEN
957 g_ErrorCode := 'ERROR_UNEXPECTED_00111';
958 raise;
959 END;
960 ELSE
961 BEGIN
962 l_String := MSC_WS_COMMON.INSERT_OR_UPDATE_ALL_SCHS(
963 PlanId, l_InsId, UserId,
964 g_IGlbDmdSchTbl, g_ILocDmdSchTbl, g_ILocSupSchTbl);
965 EXCEPTION WHEN others THEN
966 g_ErrorCode := 'ERROR_UNEXPECTED_00112';
967 raise;
968 END;
969 END IF;
970
971 IF (l_String <> 'OK') THEN
972 Status := l_String;
973 RETURN;
974 ELSE
975 Status := 'SUCCESS';
976 END IF;
977
978 COMMIT;
979
980 EXCEPTION
981 WHEN others THEN
982 -- Status := 'Failed '||fnd_message.get;
983 Status := g_ErrorCode;
984 ROLLBACK;
985
986 END SET_DRP_PLAN_OPTIONS;
987
988 PROCEDURE SET_DRP_PLAN_OPTIONS_PUBLIC (
989 Status OUT NOCOPY VARCHAR2,
990 UserName IN VARCHAR2,
991 RespName IN VARCHAR2,
992 RespApplName IN VARCHAR2,
993 SecurityGroupName IN VARCHAR2,
994 Language IN VARCHAR2,
995 PlanId IN NUMBER,
996 ItemSimulationSetId IN NUMBER default NULL,
997 Overwrite IN VARCHAR2 default 'All',
998 PurgeAllSchsFlag IN VARCHAR2,
999 GlobalDmdSchs IN MscGlbDmdSchTbl default NULL,
1000 LocalDmdSchs IN MscLocSRPDmdSchTbl default NULL,
1001 LocalSupSchs IN MscLocSupSchTbl default NULL
1002 ) AS
1003 userid number;
1004 respid number;
1005 l_String VARCHAR2(30);
1006 error_tracking_num number;
1007 l_SecutirtGroupId NUMBER;
1008 BEGIN
1009 error_tracking_num :=2010;
1010 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
1011 IF (l_String <> 'OK') THEN
1012 Status := l_String;
1013 RETURN;
1014 END IF;
1015
1016 error_tracking_num :=2030;
1017 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSCFPDRP',l_SecutirtGroupId);
1018 -- MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MRPFPPMRSDRP',l_SecutirtGroupId);
1019 IF (l_String <> 'OK') THEN
1020 Status := l_String;
1021 RETURN;
1022 END IF;
1023
1024 error_tracking_num :=2040;
1025
1026
1027 SET_DRP_PLAN_OPTIONS ( Status, userId , respid, PlanId, ItemSimulationSetId, Overwrite, PurgeAllSchsFlag, GlobalDmdSchs, LocalDmdSchs, LocalSupSchs );
1028
1029
1030
1031 EXCEPTION
1032 WHEN others THEN
1033 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
1034
1035 return;
1036
1037
1038 END SET_DRP_PLAN_OPTIONS_PUBLIC;
1039
1040 END MSC_WS_DRP;
1041