1 PACKAGE BODY MSC_WS_COMMON AS
2 /* $Header: MSCWCOMB.pls 120.8 2008/03/25 19:22:50 bnaghi noship $ */
3
4 PROCEDURE VALIDATE_USER_RESP(
5 VRETURN OUT NOCOPY VARCHAR2,
6 USERID IN NUMBER,
7 RESPID IN NUMBER,
8 SECURITYID IN NUMBER DEFAULT 0) AS
9
10 V_USER_ID NUMBER;
11 V_RESPID NUMBER;
12 V_APPID NUMBER :=0;
13 BEGIN
14
15 BEGIN
16 SELECT USER_ID INTO V_USER_ID
17 FROM FND_USER
18 WHERE USER_ID = USERID;
19 EXCEPTION WHEN no_data_found THEN
20 VRETURN := 'INVALID_USERID';
21 RETURN;
22 WHEN others THEN
23 raise;
24 END;
25
26 BEGIN
27 SELECT RESPONSIBILITY_ID INTO V_RESPID
28 FROM FND_USER_RESP_GROUPS
29 WHERE USER_ID = V_USER_ID AND RESPONSIBILITY_ID = RESPID AND
30 (sysdate BETWEEN nvl(start_date,sysdate) AND nvl(end_date,sysdate));
31 EXCEPTION WHEN no_data_found THEN
32 VRETURN := 'USER_NOT_ASSIGNED_RESP';
33 RETURN;
34 WHEN others THEN
35 raise;
36 END;
37
38 BEGIN
39 SELECT APPLICATION_ID INTO V_APPID
40 FROM FND_RESPONSIBILITY
41 WHERE RESPONSIBILITY_ID = V_RESPID;
42 EXCEPTION WHEN others THEN
43 raise;
44 END;
45
46
47 fnd_global.apps_initialize(USERID, RESPID, V_APPID, SECURITYID);
48 VRETURN :='OK';
49
50 END VALIDATE_USER_RESP;
51 PROCEDURE VALIDATE_USER_RESP_FUNC(
52 VRETURN OUT NOCOPY VARCHAR2,
53 USERID IN NUMBER,
54 RESPID IN NUMBER,
55 FUNC_NAME IN VARCHAR2,
56 SECURITYID IN NUMBER
57 ) IS
58 l_Status VARCHAR2(100);
59 BEGIN
60 MSC_WS_COMMON.VALIDATE_USER_RESP(l_Status, USERID, RESPID,SECURITYID);
61 IF (l_Status<> 'OK') THEN
62 VRETURN:= l_Status;
63 RETURN;
64 END IF;
65
66 -- check form function
67 IF (fnd_function.test( FUNC_NAME) ) THEN
68 VRETURN := 'OK';
69 ELSE
70 VRETURN := 'RESP_NO_ACCESS_TO_WEBSERVICE';
71 END IF;
72
73
74
75 END VALIDATE_USER_RESP_FUNC;
76
77 PROCEDURE GET_PERMISSION_IDS(
78 Status OUT NOCOPY VARCHAR2,
79 UserId OUT NOCOPY NUMBER,
80 ResponsibilityId OUT NOCOPY NUMBER,
81 SecurityGroupId OUT NOCOPY NUMBER,
82 UserName IN VARCHAR2,
83 RespName IN VARCHAR2,
84 RespAppName IN VARCHAR2,
85 SecurityGroupName IN VARCHAR2,
86 Language IN VARCHAR2
87 ) AS
88 l_LanguageCode VARCHAR2(4);
89 l_application_id NUMBER;
90 BEGIN
91 -- query user id by UserName.
92 BEGIN
93 SELECT user_id INTO UserId
94 FROM fnd_user
95 WHERE user_name = upper(UserName);
96 EXCEPTION
97 WHEN no_data_found THEN
98 Status := 'INVALID_USER_NAME';
99 RETURN;
100 WHEN others THEN
101 Status := 'ERROR_UNEXPECTED_00001';
102 RETURN;
103 END;
104
105 -- query language code by Language.
106 BEGIN
107 SELECT language_code INTO l_LanguageCode
108 FROM fnd_languages
109 WHERE nls_language = Language;
110 EXCEPTION
111 WHEN no_data_found THEN
112 Status := 'INVALID_LANGUAGE';
113 RETURN;
114 WHEN others THEN
115 Status := 'ERROR_UNEXPECTED_00002';
116 RETURN;
117 END;
118
119
120 -- query application_id by application_code.
121 BEGIN
122 SELECT application_id INTO l_application_id
123 FROM fnd_application
124 WHERE application_short_name = RespAppName;
125 EXCEPTION
126 WHEN no_data_found THEN
127 Status := 'INVALID_APPLICATION';
128 RETURN;
129 WHEN others THEN
130 Status := 'ERROR_UNEXPECTED_00003';
131 RETURN;
132 END;
133
134
135 -- query responsibility id by RespName and RespAppName.
136 BEGIN
137 SELECT resp_tl.responsibility_id INTO ResponsibilityId
138 FROM
139 fnd_responsibility_tl resp_tl
140 WHERE
141 resp_tl.application_id = l_application_id AND
142 resp_tl.language = l_LanguageCode AND
143 resp_tl.responsibility_name = RespName;
144
145 EXCEPTION
146 WHEN no_data_found THEN
147 Status := 'INVALID_RESP_NAME';
148 RETURN;
149 WHEN others THEN
150 Status := 'ERROR_UNEXPECTED_00004';
151 RETURN;
152 END;
153
154 -- query security group id by SecurityGroupName.
155 BEGIN
156 SELECT security_group_id INTO SecurityGroupId
157 FROM fnd_security_groups
158 WHERE security_group_key = SecurityGroupName;
159 EXCEPTION
160 WHEN no_data_found THEN
161 Status := 'INVALID_SECUTITY_GROUP_NAME';
162 RETURN;
163 WHEN others THEN
164 Status := 'ERROR_UNEXPECTED_00005';
165 RETURN;
166 END;
167
168 END GET_PERMISSION_IDS;
169
170 -- get plan name from plan Id
171 FUNCTION GET_PLAN_NAME_BY_PLAN_ID(
172 Status OUT NOCOPY VARCHAR2,
173 PlanId IN NUMBER
174 ) RETURN BOOLEAN AS
175 l_PlanName VARCHAR2(10);
176 BEGIN
177 BEGIN
178 SELECT COMPILE_DESIGNATOR INTO l_PlanName
179 FROM MSC_PLANS
180 WHERE PLAN_ID = PlanId;
181 EXCEPTION WHEN NO_DATA_FOUND THEN
182 Status := 'INVALID_PLANID';
183 RETURN FALSE;
184 WHEN others THEN
185 raise;
186 END;
187
188 Status := l_PlanName;
189 RETURN TRUE;
190 END GET_PLAN_NAME_BY_PLAN_ID;
191
192
193
194 FUNCTION Bool_to_Number( flag IN varchar2) RETURN number IS
195 begin
196 if ( flag ='Y') then return MSC_UTIL.SYS_YES;
197 else return MSC_UTIL.SYS_NO;
198 end if;
199 end Bool_to_Number;
200
201 FUNCTION get_cat_set_id(arg_plan_id number) RETURN NUMBER is
202 l_cat_set_id number;
203 l_def_pref_id number;
204 l_plan_type number;
205 cursor plan_type_c(v_plan_id number) is
206 select curr_plan_type
207 from msc_plans
208 where plan_id = v_plan_id;
209 BEGIN
210 open plan_type_c(arg_plan_id);
211 fetch plan_type_c into l_plan_type;
212 close plan_type_c;
213
214 l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
215 l_cat_set_id:= msc_get_name.GET_preference('CATEGORY_SET_ID',l_def_pref_id, l_plan_type);
216 return l_cat_set_id;
217 END get_cat_set_id;
218
219
220 -- =============================================================
221 --
222 -- Helper functions used by Set Plan Options.
223 --
224 -- Caller has to handle the exception.
225 --
226 -- =============================================================
227
228
229 -- =============================================================
230 -- Desc: Validate plan id, simulate the logic from Value
231 -- Set "MSC_SRS_NAME_COPY"
232 -- Input:
233 -- PlanId Plan Id.
234 --
235 -- Output: The possible return statuses are:
236 -- OK
237 -- INVALID_PLANID
238 -- =============================================================
239 FUNCTION VALIDATE_PLAN_ID(
240 OrgId OUT NOCOPY NUMBER,
241 InsId OUT NOCOPY NUMBER,
242 PlanName OUT NOCOPY VARCHAR2,
243 PlanId IN NUMBER
244 ) RETURN VARCHAR2 AS
245 l_ReturnString VARCHAR2(100);
246 BEGIN
247 BEGIN
248 SELECT
249 plans.organization_id,
250 plans.sr_instance_id,
251 plans.compile_designator
252 INTO
253 OrgId,
254 InsId,
255 PlanName
256 FROM
257 msc_plans plans,
258 msc_designators desig
259 WHERE
260 plans.organization_id = desig.organization_id AND
261 plans.sr_instance_id = desig.sr_instance_id AND
262 plans.compile_designator = desig.designator AND
263 NVL(desig.disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE) AND
264 plans.organization_selection <> 1 AND
265 plans.curr_plan_type in (1,2,3,4,5,8,9) AND
266 plans.plan_id <> -1 AND
267 NVL(plans.copy_plan_id,-1) = -1 AND
268 NVL(desig.copy_designator_id, -1) = -1 AND
269 plans.plan_id = PlanId;
270 EXCEPTION WHEN NO_DATA_FOUND THEN
271 RETURN 'INVALID_PLANID';
272 WHEN others THEN
273 raise;
274 END;
275
276 RETURN 'OK';
277 END VALIDATE_PLAN_ID;
278
279 -- =============================================================
280 -- Desc: Check if the plan contains this org organization.
281 -- for a pecified plan id
282 -- Input:
283 -- OrgId Organization Id.
284 -- PlanId Plan Id.
285 --
286 -- Output: The possible return statuses are:
287 -- OK
288 -- INVALID_ORGID
289 -- =============================================================
290 FUNCTION PLAN_CONTAINS_THIS_ORG(
291 InsId OUT NOCOPY NUMBER,
292 OrgId IN NUMBER,
293 PlanId IN NUMBER
294 ) RETURN VARCHAR2 AS
295 BEGIN
296 BEGIN
297 -- check if the organization is already in the plan
298 -- its not allow to add organization to the plan in the set plan option
299 SELECT
300 sr_instance_id INTO InsId
301 FROM
302 msc_plan_organizations
303 WHERE
304 plan_id = PlanId AND
305 organization_id = OrgId;
306 EXCEPTION WHEN NO_DATA_FOUND THEN
307 RETURN 'INVALID_ORGID';
308 WHEN others THEN
309 raise;
310 END;
311
312 RETURN 'OK';
313 END PLAN_CONTAINS_THIS_ORG;
314
315
316 -- =============================================================
317 -- Desc: Validate item simulation set id.
318 -- Input:
319 -- SetId Simulation set Id.
320 --
321 -- Output: The possible return statuses are:
322 -- OK
323 -- INVALID_SIMULATION_SET_ID
324 -- =============================================================
325 FUNCTION VALIDATE_SIMULATION_SET_ID( SetId IN NUMBER ) RETURN VARCHAR2 AS
326 l_Dummy NUMBER;
327 BEGIN
328 IF SetId IS NOT NULL THEN
329 BEGIN
330 SELECT
331 1 INTO l_Dummy
332 FROM
333 msc_item_simulation_sets
334 WHERE
335 simulation_set_id = SetId;
336 EXCEPTION WHEN NO_DATA_FOUND THEN
337 RETURN 'INVALID_SIMULATION_SET_ID';
338 WHEN others THEN
339 raise;
340 END;
341 END IF;
342
343 RETURN 'OK';
344 END VALIDATE_SIMULATION_SET_ID;
345
346 -- =============================================================
347 -- Desc: purge all schedules, including global demand schediles,
348 -- local demand schedules and local supply schedules for this plan
349 --
350 -- Input:
351 -- PlanId Id of the plan.
352 --
353 -- Output: No output.
354 -- =============================================================
355 PROCEDURE PURGE_ALL_SCHEDULES(PlanId IN NUMBER) AS
356 BEGIN
357 BEGIN
358 DELETE FROM msc_plan_schedules
359 WHERE
360 plan_id = PlanId;
361 EXCEPTION WHEN others THEN
362 raise;
363 END;
364
365 END PURGE_ALL_SCHEDULES;
366
367 -- =============================================================
368 -- Desc: update item simulation set and overwrite
369 --
370 -- Note, this function doesn't update overwrite supplies nor nanual forecast.
371 --
372 -- Input:
373 -- PlanId Id of the plan.
374 -- ItemSimulationSetId Id of the item simulation set.
375 -- Overwrite Overwrite.
376 --
377 -- Output: No output.
378 -- =============================================================
379 PROCEDURE UPDATE_PLAN_OPTIONS(
380 PlanId IN NUMBER,
381 ItemSimulationSetId IN NUMBER,
382 Overwrite IN NUMBER
383 ) AS
384 BEGIN
385 BEGIN
386 UPDATE msc_plans
387 SET
388 item_simulation_set_id = ItemSimulationSetId,
389 curr_overwrite_option = Overwrite
390 WHERE
391 plan_id = PlanId;
392 EXCEPTION WHEN others THEN
393 raise;
394 END;
395
396 END UPDATE_PLAN_OPTIONS;
397
398
399 -- =============================================================
400 -- Desc: Convert Overwrite from string to number
401 --
402 -- Input:
403 -- Overwrite Overwrite.
404 --
405 -- Output: Convert Overwrite in number.
406 -- =============================================================
407 FUNCTION CONVERT_OVERWRITE( Overwrite IN VARCHAR2 ) RETURN NUMBER AS
408 l_Overwrite NUMBER;
409 BEGIN
410 -- Overwrite is restricted to 'ALL', 'OUTSIDE_PTF' or 'NONE' by xsd.
411 CASE Overwrite
412 WHEN 'ALL' THEN l_Overwrite := 1;
413 WHEN 'OUTSIDE_PTF' THEN l_Overwrite := 2;
414 WHEN 'NONE' THEN l_Overwrite := 3;
415 END CASE;
416 RETURN l_Overwrite;
417 END CONVERT_OVERWRITE;
418
419 -- =============================================================
420 -- Desc: Insert a global demand schedule
421 --
422 -- Input:
423 -- PlanId Id of the plan.
424 -- InsId Sr instance id.
425 -- UserId user id,
426 -- Schrec global demand schedule data.
427 --
428 -- Output: The possible return statuses are:
429 -- OK
430 -- ERROR_DUP_GLOBALDMDSCH
431 -- =============================================================
432 FUNCTION INSERT_G_DMD_SCH(
433 PlanId IN NUMBER,
434 InsId IN NUMBER,
435 UserId IN NUMBER,
436 SchRec IN MscIGlbDmdSchRec
437 ) RETURN VARCHAR2 AS
438 BEGIN
439 BEGIN
440 INSERT INTO msc_plan_schedules
441 (
442 plan_id, organization_id, input_schedule_id, sr_instance_id,
443 input_type, last_update_date, last_updated_by,
444 creation_date, created_by, designator_type, ship_to
445 )
446 VALUES
447 (
448 PlanId, -1, SchRec.DmdSchId, InsId,
449 SchRec.input_type, sysdate, UserId,
450 sysdate, UserId, SchRec.designator_type, SchRec.ShipToConsumptionLvl
451 ) ;
452 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
453 RETURN 'ERROR_DUP_GLOBALDMDSCH';
454 WHEN others THEN
455 raise;
456 END;
457
458 RETURN 'OK';
459 END INSERT_G_DMD_SCH;
460
461 -- =============================================================
462 -- Desc: Insert a local demand schedule
463 --
464 -- Input:
465 -- PlanId Id of the plan.
466 -- InsId Sr instance id.
467 -- UserId user id,
468 -- Schrec local demand schedule data.
469 --
470 -- Output: The possible return statuses are:
471 -- OK
472 -- ERROR_DUP_LOCALDMDSCH
473 -- =============================================================
474 FUNCTION INSERT_L_DMD_SCH(
475 PlanId IN NUMBER,
476 InsId IN NUMBER,
477 UserId IN NUMBER,
478 SchRec IN MscILocDmdSchRec
479 ) RETURN VARCHAR2 AS
480 BEGIN
481 BEGIN
482 INSERT INTO msc_plan_schedules
483 (
484 plan_id, organization_id,
485 input_schedule_id, sr_instance_id,
486 input_type, last_update_date, last_updated_by,
487 creation_date, created_by, designator_type,
488 include_target_demands,
489 ship_to,
490 interplant_demand_flag
491 )
492 VALUES
493 (
494 PlanId, SchRec.OrgId,
495 SchRec.DmdSchId, InsId,
496 SchRec.input_type, sysdate, UserId,
497 sysdate, UserId, SchRec.designator_type,
498 SchRec.IncludeTargetDmd,
499 SchRec.ShipToConsumptionLvl,
500 SchRec.InterPlantFlg
501 ) ;
502 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
503 RETURN 'ERROR_DUP_LOCALDMDSCH';
504 WHEN others THEN
505 raise;
506 END;
507
508 RETURN 'OK';
509 END INSERT_L_DMD_SCH;
510
511 -- =============================================================
512 -- Desc: Insert a local supply schedule
513 --
514 -- Input:
515 -- PlanId Id of the plan.
516 -- InsId Sr instance id.
517 -- UserId user id,
518 -- Schrec local supply schedule data.
519 --
520 -- Output: The possible return statuses are:
521 -- OK
522 -- ERROR_DUP_LOCALSUPSCH
523 -- =============================================================
524 FUNCTION INSERT_L_SUP_SCH(
525 PlanId IN NUMBER,
526 InsId IN NUMBER,
527 UserId IN NUMBER,
528 SchRec IN MscILocSupSchRec
529 ) RETURN VARCHAR2 AS
530 BEGIN
531 BEGIN
532 INSERT INTO msc_plan_schedules
533 (
534 plan_id, organization_id,
535 input_schedule_id, sr_instance_id,
536 input_type, last_update_date, last_updated_by,
537 creation_date, created_by, designator_type
538 )
539 VALUES
540 (
541 PlanId, SchRec.OrgId,
542 SchRec.SupSchId, InsId,
543 SchRec.input_type, sysdate, UserId,
544 sysdate, UserId, SchRec.designator_type
545 ) ;
546 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
547 RETURN 'ERROR_DUP_LOCALSUPSCH';
548 WHEN others THEN
549 raise;
550 END;
551
552 RETURN 'OK';
553 END INSERT_L_SUP_SCH;
554
555 -- =============================================================
556 -- Desc: this function is call when the PurgeAllSchsFlag is set,
557 -- insert all schedules, including global demand schediles,
558 -- local demand schedules and local supply schedules for this plan
559 --
560 -- Note, this function doesn't update global return forecasts
561 --
562 -- Input:
563 -- PlanId Id of the plan.
564 -- InsId Sr instance id.
565 -- UserId user id,
566 -- GlbDmdSchs global demand schedules
567 -- LocDmdSchs local demand schedules
568 -- LocSupSchs local supply schedules
569 --
570 -- Output: No output.
571 -- =============================================================
572 FUNCTION INSERT_ALL_SCHEDULES(
573 PlanId IN NUMBER,
574 InsId IN NUMBER,
575 UserId IN NUMBER,
576 GlbDmdSchs IN MscIGlbDmdSchTbl,
577 LocDmdSchs IN MscILocDmdSchTbl,
578 LocSupSchs IN MscILocSupSchTbl
579 ) RETURN VARCHAR2 AS
580 l_String VARCHAR2(100);
581 BEGIN
582 -- insert all global demand schedules
583 IF GlbDmdSchs.COUNT > 0 THEN
584 FOR I IN GlbDmdSchs.first..GlbDmdSchs.last
585 LOOP
586 l_String := INSERT_G_DMD_SCH(PlanId, InsId, UserId, GlbDmdSchs(I));
587 IF (l_String <> 'OK') THEN
588 RETURN l_String;
589 END IF;
590 END LOOP;
591 END IF;
592
593 -- insert all local demand schedules
594 IF LocDmdSchs.COUNT > 0 THEN
595 FOR I IN LocDmdSchs.first..LocDmdSchs.last
596 LOOP
597 l_String := INSERT_L_DMD_SCH(PlanId, InsId, UserId, LocDmdSchs(I));
598 IF (l_String <> 'OK') THEN
599 RETURN l_String;
600 END IF;
601 END LOOP;
602 END IF;
603
604 -- insert all local supply schedules
605 IF LocSupSchs.COUNT > 0 THEN
606 FOR I IN LocSupSchs.first..LocSupSchs.last
607 LOOP
608 l_String := INSERT_L_SUP_SCH(PlanId, InsId, UserId, LocSupSchs(I));
609 IF (l_String <> 'OK') THEN
610 RETURN l_String;
611 END IF;
612 END LOOP;
613 END IF;
614
615 RETURN 'OK';
616 END INSERT_ALL_SCHEDULES;
617
618 -- =============================================================
619 -- Desc: Update a global demand schedule
620 --
621 -- Input:
622 -- PlanId Id of the plan.
623 -- InsId Sr instance id.
624 -- UserId user id,
625 -- Schrec global demand schedule data.
626 --
627 -- Output: No output.
628 -- =============================================================
629 PROCEDURE UPDATE_G_DMD_SCH(
630 PlanId IN NUMBER,
631 InsId IN NUMBER,
632 UserId IN NUMBER,
633 SchRec IN MscIGlbDmdSchRec
634 ) AS
635 BEGIN
636 BEGIN
637 UPDATE msc_plan_schedules
638 SET
639 ship_to = SchRec.ShipToConsumptionLvl
640 WHERE
641 plan_id = PlanId AND
642 organization_id = -1 AND
643 sr_instance_id = InsId AND
644 input_schedule_id = SchRec.DmdSchId;
645 EXCEPTION WHEN others THEN
646 raise;
647 END;
648
649 END UPDATE_G_DMD_SCH;
650
651 -- =============================================================
652 -- Desc: update a local demand schedule
653 --
654 -- Input:
655 -- PlanId Id of the plan.
656 -- InsId Sr instance id.
657 -- UserId user id,
658 -- Schrec local demand schedule data.
659 --
660 -- Output: No output.
661 -- =============================================================
662 PROCEDURE UPDATE_L_DMD_SCH(
663 PlanId IN NUMBER,
664 InsId IN NUMBER,
665 UserId IN NUMBER,
666 SchRec IN MscILocDmdSchRec
667 ) AS
668 BEGIN
669 BEGIN
670 UPDATE msc_plan_schedules
671 SET
672 ship_to = SchRec.ShipToConsumptionLvl,
673 include_target_demands = SchRec.IncludeTargetDmd,
674 interplant_demand_flag = SchRec.InterPlantFlg
675 WHERE
676 plan_id = PlanId AND
677 organization_id = SchRec.OrgId AND
678 sr_instance_id = InsId AND
679 input_schedule_id = SchRec.DmdSchId;
680 EXCEPTION WHEN others THEN
681 raise;
682 END;
683
684 END UPDATE_L_DMD_SCH;
685
686 -- =============================================================
687 -- Desc: this function is call when the PurgeAllSchsFlag is not set,
688 -- insert or update all schedules, including global demand schediles,
689 -- local demand schedules and local supply schedules for this plan
690 --
691 -- Note, this function doesn't insert or update global return forecasts
692 --
693 -- Input:
694 -- PlanId Id of the plan.
695 -- InsId Sr instance id.
696 -- UserId user id,
697 -- GlbDmdSchs global demand schedules
698 -- LocDmdSchs local demand schedules
699 -- LocSupSchs local supply schedules
700 --
701 -- Output: No output.
702 -- =============================================================
703 FUNCTION INSERT_OR_UPDATE_ALL_SCHS(
704 PlanId IN NUMBER,
705 InsId IN NUMBER,
706 UserId IN NUMBER,
707 GlbDmdSchs IN MscIGlbDmdSchTbl,
708 LocDmdSchs IN MscILocDmdSchTbl,
709 LocSupSchs IN MscILocSupSchTbl
710 ) RETURN VARCHAR2 AS
711 l_Dummy NUMBER;
712 l_String VARCHAR2(100);
713 BEGIN
714 -- insert/update all global demand schedules
715 IF GlbDmdSchs.COUNT > 0 THEN
716 FOR I IN GlbDmdSchs.first..GlbDmdSchs.last
717 LOOP
718 BEGIN
719 Select count(*) INTO l_Dummy
720 FROM msc_plan_schedules
721 WHERE
722 plan_id = PlanId AND
723 organization_id = -1 AND
724 sr_instance_id = InsId AND
725 input_schedule_id = GlbDmdSchs(I).DmdSchId;
726 EXCEPTION WHEN others THEN
727 raise;
728 END;
729 IF l_Dummy = 0 THEN
730 l_String := INSERT_G_DMD_SCH(PlanId, InsId, UserId, GlbDmdSchs(I));
731 IF (l_String <> 'OK') THEN
732 RETURN l_String;
733 END IF;
734 ELSE
735 UPDATE_G_DMD_SCH(PlanId, InsId, UserId, GlbDmdSchs(I));
736 END IF;
737 END LOOP;
738 END IF;
739
740 -- insert/update all local demand schedules
741 IF LocDmdSchs.COUNT > 0 THEN
742 FOR I IN LocDmdSchs.first..LocDmdSchs.last
743 LOOP
744 BEGIN
745 Select count(*) INTO l_Dummy
746 FROM msc_plan_schedules
747 WHERE
748 plan_id = PlanId AND
749 organization_id = LocDmdSchs(I).OrgId AND
750 sr_instance_id = InsId AND
751 input_schedule_id = LocDmdSchs(I).DmdSchId;
752 EXCEPTION WHEN others THEN
753 raise;
754 END;
755 IF l_Dummy = 0 THEN
756 l_String := INSERT_L_DMD_SCH(PlanId, InsId, UserId, LocDmdSchs(I));
757 IF (l_String <> 'OK') THEN
758 RETURN l_String;
759 END IF;
760 ELSE
761 UPDATE_L_DMD_SCH(PlanId, InsId, UserId, LocDmdSchs(I));
762 END IF;
763 END LOOP;
764 END IF;
765
766 -- insert/update all local supply schedules
767 IF LocSupSchs.COUNT > 0 THEN
768 FOR I IN LocSupSchs.first..LocSupSchs.last
769 LOOP
770 BEGIN
771 Select count(*) INTO l_Dummy
772 FROM msc_plan_schedules
773 WHERE
774 plan_id = PlanId AND
775 organization_id = LocSupSchs(I).OrgId AND
776 sr_instance_id = InsId AND
777 input_schedule_id = LocSupSchs(I).SupSchId;
778 EXCEPTION WHEN others THEN
779 raise;
780 END;
781 IF l_Dummy = 0 THEN
782 l_String := INSERT_L_SUP_SCH(PlanId, InsId, UserId, LocSupSchs(I));
783 IF (l_String <> 'OK') THEN
784 RETURN l_String;
785 END IF;
786 ELSE
787 -- Nothing to update for local supply schedule
788 NULL;
789 END IF;
790 END LOOP;
791 END IF;
792
793 RETURN 'OK';
794 END INSERT_OR_UPDATE_ALL_SCHS;
795
796
797 -- =============================================================
798 -- Desc: Validate the global demand schedule id. This function is
799 -- used by DRP and SRP. ASCP has its own function.
800 -- Input:
801 -- SchId Global demand schedule id.
802 -- PlanName Plan name.
803 --
804 -- Output: The possible return statuses are:
805 -- OK
806 -- INVALID_GLOBALDMDSCHS_DMD_SCH_ID
807 --- =============================================================
808 FUNCTION VALIDATE_G_DMD_SCH_ID(
809 SchId IN NUMBER,
810 PlanName IN VARCHAR2
811 ) RETURN VARCHAR2 AS
812 l_Dummy NUMBER;
813 BEGIN
814 BEGIN
815 SELECT
816 1 INTO l_Dummy
817 FROM
818 msd_dp_ascp_scenarios_v
819 WHERE
820 global_scenario_flag = 'Y' AND
821 last_revision IS NOT NULL AND -- ASCP doesn't has this condition
822 scenario_name <> PlanName AND
823 scenario_id = SchId;
824 EXCEPTION WHEN NO_DATA_FOUND THEN
825 RETURN 'INVALID_GLOBALDMDSCHS_DMD_SCH_ID';
826 WHEN others THEN
827 raise;
828 END;
829
830 RETURN 'OK';
831 END VALIDATE_G_DMD_SCH_Id;
832
833
834 -- =============================================================
835 -- Desc: Validate the ship to consumption level. This function is
836 -- used by DRP and SRP. ASCP have its own function in
837 -- MSC_WS_ASCP.
838 -- Input:
839 -- ShipTo Ship to consumption level.
840 -- SchId Demand schedule id.
841 -- IsLocal Is this ship to consumption level
842 -- for a local demand schedule?
843 --
844 -- Output: The possible return statuses are:
845 -- OK
846 -- INVALID_SHIP_TO_CONSUMPTION_LVL
847 -- =============================================================
848 FUNCTION VALIDATE_CONSUM_LVL(
849 ShipTo IN NUMBER,
850 SchId IN NUMBER
851 ) RETURN VARCHAR2 AS
852 l_scenario_lvl_geo NUMBER;
853 l_scenario_lvl_item NUMBER;
854 BEGIN
855 BEGIN
856 SELECT level_id INTO l_scenario_lvl_geo
857 FROM msd_dp_scenario_output_levels
858 WHERE
859 scenario_id = SchId AND
860 level_id IN (11,15,41,42,40);
861 EXCEPTION WHEN NO_DATA_FOUND THEN
862 l_scenario_lvl_geo := 30;
863 WHEN others THEN
864 raise;
865 END;
866
867 BEGIN
868 SELECT level_id INTO l_scenario_lvl_item
869 FROM msd_dp_scenario_output_levels
870 WHERE
871 scenario_id = SchId AND
872 level_id = 34;
873 EXCEPTION WHEN NO_DATA_FOUND THEN
874 l_scenario_lvl_item := 40;
875 WHEN others THEN
876 raise;
877 END;
878
879 /*
880 2 Ship
881 3 Bill
882 4 Customer
883 5 Region
884 6 Item
885 7 Customer Site
886 8 Zone
887 9 Customer Zone
888 10 Demand Class
889 */
890
891 -- dbms_output.put_line('SchId: ' || SchId);
892 -- dbms_output.put_line('l_scenario_lvl_item: ' || l_scenario_lvl_item);
893 -- dbms_output.put_line('l_scenario_lvl_geo: ' || l_scenario_lvl_geo);
894 IF l_scenario_lvl_item = 40 THEN
895 IF l_scenario_lvl_geo = 11 THEN
896 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CS_ALL'
897 IF ShipTo NOT IN (4, 6, 7, 9) THEN -- Item, Customer, Customer Zone, Customer Site
898 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
899 END IF;
900 ELSIF l_scenario_lvl_geo = 15 THEN
901 -- simulate the logic from Record Groups 'MSC_SHIP_TO_C_ALL'
902 IF ShipTo NOT IN (4, 6) THEN -- Item, Customer
903 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
904 END IF;
905 ELSIF l_scenario_lvl_geo = 42 then
906 -- simulate the logic from Record Groups 'MSC_SHIP_TO_Z_ALL'
907 IF ShipTo NOT IN (6, 8) THEN -- Item, Zone
908 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
909 END IF;
910 ELSIF l_scenario_lvl_geo = 41 THEN
911 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CZ_ALL'
912 IF ShipTo NOT IN (4, 6, 8, 9) THEN -- Item, Customer, Customer Zone, Zone
913 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
914 END IF;
915 ELSIF l_scenario_lvl_geo = 30 THEN
916 -- simulate the logic from Record Groups 'MSC_SHIP_TO_ALL_ALL'
917 IF ShipTo <> 6 THEN -- Item'
918 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
919 END IF;
920 ELSE
921 -- ???? use the default record group ?????
922 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CS_ALL'
923 IF ShipTo NOT IN (4, 6, 7, 9) THEN -- Item, Customer, Customer Zone, Customer Site
924 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
925 END IF;
926 END IF;
927 ELSE -- IF l_scenario_lvl_item <> 40 THEN
928 IF l_scenario_lvl_geo = 11 THEN
929 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CS_DC'
930 IF ShipTo NOT IN (4, 6, 7, 9, 10) THEN -- Item, Customer, Demand Class, Customer Zone, Customer Site
931 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
932 END IF;
933 ELSIF l_scenario_lvl_geo = 15 THEN
934 -- simulate the logic from Record Groups 'MSC_SHIP_TO_C_DC'
935 IF ShipTo NOT IN (4, 6, 10) THEN -- Item, Customer, Demand Class
936 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
937 END IF;
938 ELSIF l_scenario_lvl_geo = 42 THEN
939 -- simulate the logic from Record Groups 'MSC_SHIP_TO_Z_DC'
940 IF ShipTo NOT IN (6, 8, 10) THEN -- Item, Demand Class, Zone
941 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
942 END IF;
943 ELSIF l_scenario_lvl_geo = 41 THEN
944 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CZ_DC'
945 IF ShipTo NOT IN (4, 6, 8, 9, 10) THEN -- Item', Customer, Customer Zone, Zone, Demand Class
946 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
947 END IF;
948 ELSIF l_scenario_lvl_geo = 30 THEN
949 -- simulate the logic from Record Groups 'MSC_SHIP_TO_ALL_DC'
950 IF ShipTo NOT IN (6, 10) THEN -- Item, Demand Class
951 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
952 END IF;
953 ELSE
954 -- ???? which record group should I use ?????
955 -- simulate the logic from Record Groups 'MSC_SHIP_TO_CS_DC'
956 IF ShipTo NOT IN (4, 6, 7, 9, 10) THEN -- Item, Customer, Demand Class, Customer Zone, Customer Site
957 RETURN 'INVALID_SHIP_TO_CONSUMPTION_LVL';
958 END IF;
959 END IF;
960 END IF;
961
962 RETURN 'OK';
963 END VALIDATE_CONSUM_LVL;
964
965
966 -- =============================================================
967 -- Desc: Validate loal supply schedule id
968 -- Input:
969 -- SchId local supply schedule id.
970 -- OrgId organization id.
971 -- InsId sr instance id.
972 -- PlanName plan name.
973 --
974 -- Output: The possible return statuses are:
975 -- OK
976 -- INVALID_LOCALSUPSCHS_SUP_SCH_ID
977 -- =============================================================
978 FUNCTION VALIDATE_L_SUP_SCH_ID(
979 DesigType OUT NOCOPY NUMBER,
980 SchId IN NUMBER,
981 OrgId IN NUMBER,
982 InsId IN NUMBER,
983 PlanName IN VARCHAR2
984 ) RETURN VARCHAR2 AS
985 BEGIN
986 BEGIN
987 SELECT
988 designator_type INTO DesigType
989 FROM
990 msc_designators
991 WHERE
992 trunc(nvl(disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
993 designator <> PlanName AND
994 organization_id = OrgId AND
995 sr_instance_id = InsId AND
996 designator_id = SchId AND
997 designator_type not in (1,6)
998 UNION
999 SELECT
1000 desig.designator_type
1001 FROM
1002 msc_designators desig,
1003 msc_plan_organizations_v mpo
1004 WHERE
1005 desig.designator_type not in (1,6) AND
1006 trunc(nvl(desig.disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
1007 mpo.organization_id = desig.organization_id AND
1008 mpo.sr_instance_id = desig.sr_instance_id AND
1009 mpo.compile_designator = desig.designator AND
1010 mpo.planned_organization = OrgId AND
1011 mpo.sr_instance_id = InsId AND
1012 desig.designator <> PlanName AND
1013 desig.designator_id = SchId;
1014 EXCEPTION WHEN NO_DATA_FOUND THEN
1015 RETURN 'INVALID_LOCALSUPSCHS_SUP_SCH_ID';
1016 WHEN others THEN
1017 raise;
1018 END;
1019
1020 RETURN 'OK';
1021 END VALIDATE_L_SUP_SCH_ID;
1022
1023 -- =============================================================
1024 -- Desc: validate local supply schedules
1025 -- Input:
1026 -- SchTable Local supply schedules.
1027 -- PlanName Plan name.
1028 --
1029 -- Output: The possible return statuses are:
1030 -- OK
1031 -- INVALID_LOCALSUPSCHS_ORGID
1032 -- INVALID_LOCALSUPSCHS_SUP_SCH_ID
1033 -- =============================================================
1034 FUNCTION VALIDATE_LOC_SUP_SCHS(
1035 OutSchTable OUT NOCOPY MscILocSupSchTbl,
1036 InSchTable IN MscLocSupSchTbl,
1037 PlanId IN NUMBER,
1038 PlanName IN VARCHAR2
1039 ) RETURN VARCHAR2 AS
1040 l_ReturnString VARCHAR2(100);
1041 l_OrgInsId NUMBER;
1042 l_DesigType NUMBER;
1043 BEGIN
1044 OutSchTable := MscILocSupSchTbl(); -- need to re-init it here
1045 IF InSchTable IS NOT NULL AND InSchTable.count > 0 THEN
1046 FOR I IN InSchTable.first..InSchTable.last
1047 LOOP
1048 -- validate organization id
1049 BEGIN
1050 l_ReturnString := MSC_WS_COMMON.PLAN_CONTAINS_THIS_ORG(l_OrgInsId, InSchTable(I).OrgId, PlanId);
1051 IF (l_ReturnString <> 'OK') THEN
1052 -- overwrite the error token here.
1053 l_ReturnString := 'INVALID_LOCALSUPSCHS_ORGID';
1054 RETURN l_ReturnString;
1055 END IF;
1056 EXCEPTION WHEN others THEN
1057 raise;
1058 END;
1059
1060 -- validate supply schedule id
1061 l_ReturnString := VALIDATE_L_SUP_SCH_ID(
1062 l_DesigType,
1063 InSchTable(I).SupSchId,
1064 InSchTable(I).OrgId,
1065 l_OrgInsId,
1066 PlanName);
1067 IF (l_ReturnString <> 'OK') THEN
1068 RETURN l_ReturnString;
1069 END IF;
1070
1071 OutSchTable.extend;
1072 OutSchTable(OutSchTable.count) :=
1073 MscILocSupSchRec(InSchTable(I).OrgId,
1074 InSchTable(I).SupSchId,
1075 2, -- input_type
1076 l_DesigType -- designator_type
1077 );
1078
1079 END LOOP;
1080 END IF;
1081
1082 RETURN 'OK';
1083 END VALIDATE_LOC_SUP_SCHS;
1084
1085 END MSC_WS_COMMON;
1086
[Home] [Help]