1 PACKAGE BODY Msc_GET_name AS
2 /* $Header: MSCGPRJB.pls 120.50.12010000.4 2008/12/23 23:22:45 cnazarma ship $*/
3 FUNCTION project (arg_project_id IN NUMBER,
4 arg_org_id IN NUMBER,
5 arg_plan_id IN NUMBER,
6 arg_instance_id IN NUMBER) return varchar2 IS
7 var_project_num VARCHAR2(30);
8 BEGIN
9
10 IF arg_project_id is null or
11 arg_plan_id is null or
12 arg_instance_id is null THEN
13 return null;
14 ELSIF arg_org_id IS NULL THEN
15 select distinct project_number
16 into var_project_num
17 from msc_projects
18 where project_id = arg_project_id
19 and plan_id = -1
20 and sr_instance_id=arg_instance_id
21 and rownum = 1;
22 ELSE
23 select project_number
24 into var_project_num
25 from msc_projects
26 where project_id = arg_project_id
27 and plan_id = -1
28 and sr_instance_id=arg_instance_id
29 and organization_id = arg_org_id;
30 END IF;
31
32 return var_project_num;
33 END project;
34
35 FUNCTION task(arg_task_id IN NUMBER,
36 arg_project_id IN NUMBER,
37 arg_org_id IN NUMBER,
38 arg_plan_id IN NUMBER,
39 arg_instance_id IN NUMBER) return varchar2 IS
40 var_task_num varchar2(25);
41 BEGIN
42 IF arg_task_id is null or
43 arg_plan_id is null or
44 arg_instance_id is null THEN
45 return null;
46 ELSIF (arg_org_id is null or arg_project_id is null) THEN
47 select distinct task_number
48 into var_task_num
49 from msc_project_tasks
50 where task_id = arg_task_id
51 and plan_id = -1
52 and sr_instance_id=arg_instance_id
53 and rownum = 1;
54 ELSE
55 select task_number
56 into var_task_num
57 from msc_project_tasks
58 where project_id = arg_project_id
59 and task_id = arg_task_id
60 and plan_id = -1
61 and sr_instance_id=arg_instance_id
62 and organization_id = arg_org_id;
63 END IF;
64
65 return var_task_num;
66 END task;
67
68
69
70 FUNCTION process_priority(arg_plan_id IN NUMBER,
71 arg_sr_instance_id IN NUMBER,
72 arg_organization_id IN NUMBER,
73 arg_inventory_item_id IN NUMBER,
74 arg_process_sequence_id IN NUMBER)
75 return NUMBER IS
76 var_process_priority NUMBER;
77
78 BEGIN
79
80 IF arg_plan_id is NULL or
81 arg_sr_instance_id is NULL or
82 arg_organization_id is NULL or
83 arg_inventory_item_id is NULL or
84 arg_process_sequence_id is NULL THEN
85
86 return NULL;
87
88 END IF;
89
90 select preference
91 into var_process_priority
92 from msc_process_effectivity
93 where plan_id = arg_plan_id
94 and process_sequence_id = arg_process_sequence_id
95 and sr_instance_id = arg_sr_instance_id
96 and organization_id = arg_organization_id
97 and item_id = arg_inventory_item_id;
98
99 return var_process_priority;
100
101 END process_priority;
102
103
104 FUNCTION planning_group(arg_project_id IN NUMBER,
105 arg_org_id IN NUMBER,
106 arg_plan_id IN NUMBER,
107 arg_instance_id IN NUMBER) return varchar2 IS
108 var_plng_grp varchar2(30);
109 BEGIN
110
111 IF arg_project_id is null or
112 arg_plan_id is null or
113 arg_instance_id is null or
114 arg_org_id is null
115 THEN
116 return null;
117 END IF;
118
119 select DISTINCT planning_group
120 into var_plng_grp
121 from msc_projects
122 where project_id = arg_project_id
123 and plan_id = -1
124 and sr_instance_id=arg_instance_id
125 and organization_id = arg_org_id;
126
127 return var_plng_grp;
128 END planning_group;
129
130 FUNCTION demand_date (arg_pegging_id IN NUMBER,
131 arg_plan_id IN NUMBER) return DATE IS
132 var_date Date;
133 BEGIN
134
135 IF arg_pegging_id is null or
136 arg_plan_id is null THEN
137 return null;
138 END IF;
139
140 select md.using_assembly_demand_date
141 into var_date
142 from msc_demands md,
143 msc_full_pegging mfp
144 where mfp.pegging_id = arg_pegging_id
145 and mfp.plan_id=arg_plan_id
146 and md.demand_id = mfp.demand_id
147 and md.plan_id = mfp.plan_id;
148
149 return var_date;
150 END demand_date;
151
152
153 FUNCTION resource_over_util_cost(arg_resource_id IN NUMBER,
154 arg_department_id IN NUMBER,
155 arg_org_id IN NUMBER,
156 arg_plan_id IN NUMBER,
157 arg_instance_id IN NUMBER)return number IS
158 v_resource_over_util_cost number;
159
160 BEGIN
161
162 if arg_resource_id = -1 or
163 arg_resource_id is NULL or
164 arg_department_id is NULL or
165 arg_org_id is NULL or
166 arg_instance_id is NULL then
167 return NULL;
168 end if;
169
170 select resource_over_util_cost
171 into v_resource_over_util_cost
172 from msc_department_resources
173 where department_id = arg_department_id
174 and resource_id = arg_resource_id
175 and organization_id = arg_org_id
176 and sr_instance_id = arg_instance_id
177 and plan_id = arg_plan_id;
178 v_resource_over_util_cost:= v_resource_over_util_cost/100;
179 return v_resource_over_util_cost;
180
181 END resource_over_util_cost;
182
183
184
185
186 FUNCTION supply_date (arg_pegging_id IN NUMBER,
187 arg_plan_id IN NUMBER) return DATE IS
188 var_date Date;
189 BEGIN
190
191 IF arg_pegging_id is null or
192 arg_plan_id is null THEN
193 return null;
194 END IF;
195
196 select ms.new_schedule_date
197 into var_date
198 from msc_supplies ms,
199 msc_full_pegging mfp
200 where ms.transaction_id = mfp.transaction_id
201 and ms.plan_id=mfp.plan_id
202 and mfp.pegging_id = arg_pegging_id
203 and mfp.plan_id = arg_plan_id;
204
205 return var_date;
206 END supply_date;
207
208 FUNCTION lookup_meaning(arg_lookup_type IN varchar2,
209 arg_lookup_code IN NUMBER) return varchar2 IS
210 meaning_text varchar2(80);
211 BEGIN
212
213 if arg_lookup_code is null then
214 return null;
215 end if;
216
217 select meaning
218 into meaning_text
219 from mfg_lookups
220 where lookup_type = arg_lookup_type
221 and lookup_code = arg_lookup_code;
222 /*
223 select lv.meaning
224 into meaning_text
225 from fnd_lookup_values lv
226 where lv.language = userenv('LANG')
227 and lv.view_application_id =700
228 and lv.lookup_type = arg_lookup_type
229 and lv.lookup_code = to_char(arg_lookup_code)
230 and lv.security_group_id =
231 (select max(SECURITY_GROUP_ID)
232 from FND_LOOKUP_TYPES LT
233 where LT.VIEW_APPLICATION_ID = LV.VIEW_APPLICATION_ID
234 and LT.LOOKUP_TYPE = LV.LOOKUP_TYPE
235 and LT.SECURITY_GROUP_ID in (0,
236 to_number(decode(substr(userenv('CLIENT_INFO'),55,1),
237 ' ', '0',
238 null, '0',
239 substr(userenv('CLIENT_INFO'),55,10)))));
240 */
241 return meaning_text;
242
243 EXCEPTION when no_data_found THEN
244 return null;
245 END lookup_meaning;
246
247 FUNCTION lookup_by_plan(arg_lookup_type IN varchar2,
248 arg_lookup_code IN NUMBER,
249 arg_plan_type IN NUMBER,
250 arg_source_org_id IN NUMBER DEFAULT null) return varchar2 IS
251 meaning_text varchar2(80);
252 p_lookup_type varchar2(50);
253 p_lookup_code number;
254
255 cursor cur_lookup is
256 select meaning
257 from mfg_lookups
258 where lookup_type = p_lookup_type
259 and lookup_code = p_lookup_code;
260 BEGIN
261
262 if arg_lookup_code is null or
263 arg_lookup_type is null then
264 return null;
265 end if;
266
267 p_lookup_type := arg_lookup_type;
268 p_lookup_code := arg_lookup_code;
269
270 if arg_plan_type = 5 and
271 arg_lookup_type = 'MSC_DEMAND_ORIGINATION' and
272 arg_lookup_code in (8) then
273 p_lookup_type := 'MSC_DRP_CHANGED_DEMAND_NAME';
274 elsif arg_plan_type in (8,9) and
275 arg_lookup_type = 'MRP_ORDER_TYPE' and
276 arg_lookup_code in (1,2,51) then
277 if arg_lookup_code = 2 and
278 arg_source_org_id is not null then
279 p_lookup_code := 53; -- internal req
280 else
281 p_lookup_type := 'SRP_CHANGED_ORDER_TYPE';
282 end if;
283 end if;
284
285 open cur_lookup;
286 fetch cur_lookup into meaning_text;
287 close cur_lookup;
288
289 return meaning_text;
290
291 END lookup_by_plan;
292
293 FUNCTION fnd_lookup_meaning(arg_lookup_type IN varchar2,
294 arg_lookup_code IN NUMBER)
295 return varchar2 IS
296 meaning_text varchar2(255);
297
298 cursor cur_lookup(l_arg_lookup_type varchar2 ,l_arg_lookup_code NUMBER) is
299 select meaning from fnd_lookups
300 where lookup_type = l_arg_lookup_type
301 and lookup_code = l_arg_lookup_code;
302
303 BEGIN
304 open cur_lookup(arg_lookup_type ,arg_lookup_code);
305 fetch cur_lookup into meaning_text;
306 close cur_lookup;
307 return meaning_text;
308 END fnd_lookup_meaning;
309
310 FUNCTION location_code( arg_org_id IN NUMBER,
311 arg_location_id IN NUMBER,
312 arg_instance_id IN NUMBER) return VARCHAR2
313 IS
314 loc_code VARCHAR2(60);
315
316 BEGIN
317 IF (arg_org_id is NULL or
318 arg_org_id = -1 ) and
319 arg_location_id is not NULL THEN
320 select location_code
321 into loc_code
322 from msc_location_associations
323 where location_id = arg_location_id
324 and sr_instance_id = arg_instance_id;
325 ELSE
326 select location
327 into loc_code
328 from msc_trading_partner_sites s
329 where s.sr_tp_id = arg_org_id
330 and s.sr_instance_id = arg_instance_id;
331 END IF;
332
333 return loc_code;
334 EXCEPTION when others THEN
335 return null;
336 END location_code;
337
338
339
340 FUNCTION org_code(arg_org_id IN NUMBER,
341 arg_instance_id IN NUMBER) return varchar2 IS
342 org_text varchar2(15);
343 BEGIN
344
345 IF arg_org_id is null or
346 arg_org_id < 0 or
347 arg_instance_id is null or
348 arg_instance_id < 0 THEN
349 return null;
350 END IF;
351 select organization_code
352 into org_text
353 from msc_trading_partners
354 where partner_type=3
355 and sr_tp_id=arg_org_id
356 and sr_instance_id = arg_instance_id;
357
358 return org_text;
359 EXCEPTION when others THEN
360 return null;
361 END org_code;
362
363 --For SRP
364 FUNCTION org_code(arg_org_inst_id IN varchar2) return varchar2 is
365 CURSOR ORG_CUR(P_ORG_ID IN NUMBER, P_INST_ID IN NUMBER) IS
366 SELECT ORGANIZATION_CODE
367 FROM msc_trading_partners
368 WHERE SR_TP_ID = P_ORG_ID
369 AND SR_INSTANCE_ID = P_INST_ID
370 AND PARTNER_TYPE= 3;
371
372 L_ORG_CODE VARCHAR2(250);
373 L_ORG_ID NUMBER ;
374 L_INST_ID NUMBER ;
375 L_POS NUMBER;
376 BEGIN
377 L_POS := INSTR(arg_org_inst_id, ':');
378 L_ORG_ID := SUBSTR(arg_org_inst_id,1,L_POS-1);
379 L_INST_ID := SUBSTR(arg_org_inst_id,L_POS+1);
380 IF L_ORG_ID IS NULL OR L_iNST_ID IS NULL THEN
381 RETURN '';
382 ELSE
383 OPEN ORG_CUR(L_ORG_iD, L_INST_iD);
384 FETCH ORG_CUR INTO L_ORG_CODE;
385 CLOSE ORG_CUR;
386 END IF;
387 RETURN L_ORG_CODE;
388 EXCEPTION
389 WHEN OTHERS THEN
390 RETURN '';
391 end org_code;
392
393 FUNCTION instance_code(arg_instance_id IN NUMBER) return varchar2 IS
394 instance_text varchar2(5);
398 return null;
395 BEGIN
396
397 IF arg_instance_id is null THEN
399 END IF;
400 select instance_code
401 into instance_text
402 from msc_apps_instances
403 where instance_id = arg_instance_id;
404
405 return instance_text;
406 EXCEPTION when others THEN
407 return null;
408 END instance_code;
409
410 FUNCTION supply_order (arg_demand_type IN NUMBER,
411 arg_disp_id IN NUMBER,
412 arg_org_id IN NUMBER,
413 arg_plan_id IN NUMBER,
414 arg_instance_id IN NUMBER,
415 arg_supply_type IN NUMBER DEFAULT NULL)
416 return varchar2
417 IS
418
419 order_num varchar2(240);
420
421 cursor order_number_c is
422 select order_number
423 from msc_supplies
424 where disposition_id in
425 (select disposition_id
426 from msc_demands
427 where plan_id = arg_plan_id
428 and demand_id = arg_disp_id)
429 and sr_instance_id = arg_instance_id
430 and plan_id = arg_plan_id
431 and organization_id = arg_org_id
432 and order_type in (1,2,8,11,12);
433
434 cursor wip_entity_name_c is
435 select wip_entity_name
436 from msc_supplies
437 where disposition_id in
438 (select disposition_id
439 from msc_demands
440 where plan_id = arg_plan_id
441 and demand_id = arg_disp_id)
442 and sr_instance_id = arg_instance_id
443 and plan_id = arg_plan_id
444 and organization_id = arg_org_id
445 and order_type in (3,7,14,15,27,28);
446
447 cursor designator_c is
448 select desig.designator
449 from msc_designators desig,
450 msc_demands mgr
451 where desig.designator_id = mgr.schedule_designator_id
452 and mgr.demand_id = arg_disp_id
453 and mgr.sr_instance_id = arg_instance_id
454 and mgr.plan_id = arg_plan_id
455 and mgr.organization_id = arg_org_id;
456
457
458 BEGIN
459 -- note : arg_disp_id contains the demand_id,
460 -- since plan_id, org, instance, disposition cannot be unique in msc_demands
461 --bug 2038727
462
463 if ((arg_demand_type is NULL and arg_supply_type is NULL) or
464 arg_disp_id is null or
465 arg_instance_id is null or
466 arg_plan_id is null or
467 arg_org_id is null ) THEN
468
469 return NULL;
470 END IF;
471
472 if (arg_demand_type in (18,19,20,23,24) or
473 arg_supply_type in (1,2,8,11,12)) then
474
475 OPEN order_number_c;
476 FETCH order_number_c INTO order_num;
477 CLOSE order_number_c;
478
479 elsif (arg_demand_type in (2,3,17,25,26) or
480 arg_supply_type in (3,7,14,15,27,28)) then
481
482 OPEN wip_entity_name_c;
483 FETCH wip_entity_name_c INTO order_num;
484 CLOSE wip_entity_name_c;
485
486 elsif (arg_demand_type in (7,8,11,12,15)) then
487
488 OPEN designator_c;
489 FETCH designator_c INTO order_num;
493
490 CLOSE designator_c;
491
492 end if;
494 return (order_num);
495
496 END SUPPLY_ORDER;
497
498 FUNCTION job_name (arg_transaction_id IN NUMBER,
499 arg_plan_id IN NUMBER,
500 arg_sr_instance_id in number default null)
501 return varchar2
502 IS
503 order_num varchar2(240);
504 v_type number;
505 BEGIN
506
507 if arg_transaction_id is null or
508 arg_plan_id is null then
509 return NULL;
510 END IF;
511 -- if arg_sr_instance_id is null then
512 select order_number, order_type
513 into order_num, v_type
514 from msc_supplies
515 where transaction_id = arg_transaction_id
516 and plan_id = arg_plan_id;
517 -- else
518 select order_number, order_type
519 into order_num, v_type
520 from msc_supplies
521 where transaction_id = arg_transaction_id
522 and plan_id = arg_plan_id
523 and sr_instance_id = arg_sr_instance_id;
524 -- end if;
525
526 order_num := replace(order_num,'~','^');
527
528 if v_type = 5 then
529 if order_num is not null then
530 return order_num ||' '|| to_char(arg_transaction_id);
531 else
532 return to_char(arg_transaction_id);
533 end if;
534 else
535 return (order_num);
536 end if;
537
538 END job_name;
539
540 FUNCTION supply_type (arg_transaction_id IN NUMBER,
541 arg_plan_id IN NUMBER)
542 return varchar2
543 IS
544 v_order_type varchar2(240);
545 BEGIN
546
547 if arg_transaction_id is null or
548 arg_plan_id is null then
549 return NULL;
550 END IF;
551
552 select lookup_meaning('MRP_ORDER_TYPE', order_type)
553 into v_order_type
554 from msc_supplies
555 where transaction_id = arg_transaction_id
556 and plan_id = arg_plan_id;
557
558 return (v_order_type);
559
560 END supply_type;
561
562 FUNCTION order_type (arg_plan_id IN number,
563 arg_transaction_id IN NUMBER,
564 arg_instance_id IN NUMBER) RETURN number IS
565 v_order_type number;
566 BEGIN
567
568 if arg_transaction_id is null or
569 arg_plan_id is null or
570 arg_instance_id is null then
571 return NULL;
572 END IF;
573
574 select order_type
575 into v_order_type
576 from msc_supplies
577 where transaction_id = arg_transaction_id
578 and plan_id = arg_plan_id
579 and sr_instance_id = arg_instance_id;
580
581 return (v_order_type);
582
583 END order_type;
584
585
586 FUNCTION item_desc(arg_item_id IN NUMBER,
587 arg_org_id IN NUMBER,
588 arg_plan_id IN NUMBER,
589 arg_instance_id IN NUMBER) return varchar2 IS
590 item_description varchar2(240);
591 CURSOR item_desc IS
592 SELECT description
593 FROM msc_system_items
594 WHERE inventory_item_id = arg_item_id
595 AND plan_id = arg_plan_id
596 AND sr_instance_id = arg_instance_id;
597
598 BEGIN
599 if ( arg_item_id is null ) or
600 ( arg_item_id < 0 ) or
601 ( arg_plan_id is null ) or
602 ( arg_instance_id is null ) then
603 return null;
604 end if;
605 if arg_org_id is not null and arg_org_id > 0 then
606 select description
607 into item_description
608 from msc_system_items
609 where inventory_item_id = arg_item_id
610 and plan_id = arg_plan_id
611 and organization_id = arg_org_id
612 and sr_instance_id = arg_instance_id;
613 return item_description;
614 else
615 OPEN item_desc;
616 LOOP
617 FETCH item_desc INTO item_description;
618 EXIT WHEN item_desc%NOTFOUND;
619 if item_description is not null then
620 exit;
621 end if;
622 END LOOP;
623 CLOSE item_desc;
624 return item_description;
625 end if;
626
627 exception when no_data_found then
628 OPEN item_desc;
629 LOOP
630 FETCH item_desc INTO item_description;
631 EXIT WHEN item_desc%NOTFOUND;
632 if item_description is not null then
633 exit;
634 end if;
635 END LOOP;
636 CLOSE item_desc;
637 return item_description;
638
639 END item_desc;
640
641 FUNCTION department_code(arg_line_flag IN NUMBER,
642 arg_dept_id IN NUMBER,
643 arg_org_id IN NUMBER,
644 arg_plan_id IN NUMBER,
645 arg_instance_id IN NUMBER) return varchar2 IS
646 v_department_code varchar2(10);
647 BEGIN
648
649 if arg_line_flag is null or
650 arg_dept_id is null or
651 arg_plan_id is null or
652 arg_instance_id is null then
653
654 return null;
655
656 elsif arg_org_id is null then
657
658 select distinct department_code
659 into v_department_code
660 from msc_department_resources
661 where department_id = arg_dept_id
665
662 and plan_id = arg_plan_id
663 and sr_instance_id = arg_instance_id
664 and line_flag = arg_line_flag;
666 else
667
668 select distinct department_code
669 into v_department_code
670 from msc_department_resources
671 where department_id = arg_dept_id
672 and plan_id = arg_plan_id
673 and organization_id = arg_org_id
674 and sr_instance_id = arg_instance_id
675 and line_flag = arg_line_flag;
676
677 end if;
678
679 return v_department_code;
680 exception when no_data_found then
681 return null;
682 END department_code;
683
684 FUNCTION resource_code(arg_resource_id IN NUMBER,
685 arg_dept_id IN NUMBER,
686 arg_org_id IN NUMBER,
687 arg_plan_id IN NUMBER,
688 arg_instance_id IN NUMBER) return varchar2 IS
689 v_resource_code varchar2(30);
690 BEGIN
691
692 if arg_dept_id = -1 then -- from undo, arg_org_id will pass transaction_id
693 select mdr.resource_code
694 into v_resource_code
695 from msc_department_resources mdr,
696 msc_resource_requirements mrr
697 where mdr.department_id = mrr.department_id
698 and mdr.resource_id = arg_resource_id
699 and mdr.plan_id = mrr.plan_id
700 and mdr.organization_id = mrr.organization_id
701 and mdr.sr_instance_id = mrr.sr_instance_id
702 and mrr.plan_id = arg_plan_id
703 and mrr.sr_instance_id = arg_instance_id
704 and mrr.transaction_id = arg_org_id;
705
706 elsif arg_resource_id =-1 or
707 arg_resource_id is null or
708 arg_dept_id is null or
709 arg_org_id is null or
710 arg_plan_id is null or
711 arg_instance_id is null then
712
713 return null;
714
715 else
716
717 select resource_code
718 into v_resource_code
719 from msc_department_resources
720 where department_id = arg_dept_id
721 and resource_id = arg_resource_id
722 and plan_id = arg_plan_id
723 and organization_id = arg_org_id
724 and sr_instance_id = arg_instance_id;
725
726 end if;
727 return v_resource_code;
728 END resource_code;
729
730 FUNCTION resource_util_pct(arg_resource_id IN NUMBER,
731 arg_dept_id IN NUMBER,
732 arg_org_id IN NUMBER,
733 arg_plan_id IN NUMBER,
734 arg_instance_id IN NUMBER) return number IS
735 util_pct number;
736 BEGIN
737 if arg_resource_id =-1 or
738 arg_resource_id is null or
739 arg_dept_id is null or
740 arg_org_id is null or
741 arg_plan_id is null or
742 arg_instance_id is null then
743 util_pct := 1;
744 return util_pct;
745 else
746 select utilization
747 into util_pct
748 from msc_department_resources
749 where department_id = arg_dept_id
750 and resource_id = arg_resource_id
751 and plan_id = arg_plan_id
752 and organization_id = arg_org_id
753 and sr_instance_id = arg_instance_id;
754 end if;
755 if util_pct is null then
756 return 1;
757 else
758 return util_pct/100;
759 end if;
760 END resource_util_pct;
761
762
763 FUNCTION resource_type(arg_resource_id IN NUMBER,
764 arg_dept_id IN NUMBER,
765 arg_org_id IN NUMBER,
766 arg_plan_id IN NUMBER,
767 arg_instance_id IN NUMBER) return NUMBER IS
768 v_resource_type NUMBER;
769 BEGIN
770
771 if arg_resource_id =-1 or
772 arg_resource_id is null or
773 arg_dept_id is null or
774 arg_org_id is null or
775 arg_plan_id is null or
776 arg_instance_id is null then
777
778 return null;
779 end if;
780
781 select resource_type
782 into v_resource_type
783 from msc_department_resources
784 where department_id = arg_dept_id
785 and resource_id = arg_resource_id
786 and plan_id = arg_plan_id
787 and organization_id = arg_org_id
788 and sr_instance_id = arg_instance_id;
789
790 return v_resource_type;
791 END resource_type;
792
793 FUNCTION department_resource_code(arg_resource_id IN NUMBER,
794 arg_dept_id IN NUMBER,
795 arg_org_id IN NUMBER,
796 arg_plan_id IN NUMBER,
797 arg_instance_id IN NUMBER) return varchar2 IS
798 v_dept_resource_code varchar2(30);
799 v_org_code varchar2(15);
800 BEGIN
801
802 if arg_resource_id =-1 or
803 arg_resource_id is null or
804 arg_dept_id is null or
805 arg_org_id is null or
806 arg_plan_id is null or
807 arg_instance_id is null then
808
809 return null;
810 end if;
811
812 v_org_code := msc_get_name.org_code(arg_org_id, arg_instance_id);
813
814 select department_code || '/' || resource_code
815 into v_dept_resource_code
816 from msc_department_resources
817 where department_id = arg_dept_id
818 and resource_id = arg_resource_id
822
819 and plan_id = arg_plan_id
820 and organization_id = arg_org_id
821 and sr_instance_id = arg_instance_id;
823 return v_org_code||':'||v_dept_resource_code;
824 END department_resource_code;
825
826
827 FUNCTION resource_batchable_flag(arg_resource_id IN NUMBER,
828 arg_dept_id IN NUMBER,
829 arg_org_id IN NUMBER,
830 arg_plan_id IN NUMBER,
831 arg_instance_id IN NUMBER) return number IS
832 v_resource_batchable_flag number;
833 v_org_code varchar2(15);
834
835 CURSOR flag_c IS
836 select nvl(Batchable_flag,2)
837 from msc_department_resources
838 where department_id = arg_dept_id
839 and resource_id = arg_resource_id
840 and plan_id = arg_plan_id
841 and organization_id = arg_org_id
842 and sr_instance_id = arg_instance_id;
843
844 BEGIN
845
846 if arg_resource_id =-1 or
847 arg_resource_id is null or
848 arg_dept_id is null or
849 arg_org_id is null or
850 arg_plan_id is null or
851 arg_instance_id is null then
852 return null;
853 end if;
854
855 OPEN flag_c;
856 FETCH flag_c INTO v_resource_batchable_flag;
857 CLOSE flag_c;
858 return nvl(v_resource_batchable_flag,2);
859
860 END resource_batchable_flag;
861
862 FUNCTION resource_min_capacity(arg_resource_id IN NUMBER,
863 arg_dept_id IN NUMBER,
864 arg_org_id IN NUMBER,
865 arg_plan_id IN NUMBER,
866 arg_instance_id IN NUMBER) return number is
867 v_res_min_capacity number;
868 BEGIN
869
870 if arg_resource_id =-1 or
871 arg_resource_id is null or
872 arg_dept_id is null or
873 arg_org_id is null or
874 arg_plan_id is null or
875 arg_instance_id is null then
876 return null;
877 end if;
878
879 select decode(min_capacity,0,null, min_capacity)
880 into v_res_min_capacity
881 from msc_department_resources
882 where department_id = arg_dept_id
883 and resource_id = arg_resource_id
884 and plan_id = arg_plan_id
885 and organization_id = arg_org_id
886 and sr_instance_id = arg_instance_id;
887
888 return v_res_min_capacity;
889 END resource_min_capacity;
890
891 FUNCTION resource_max_capacity(arg_resource_id IN NUMBER,
892 arg_dept_id IN NUMBER,
893 arg_org_id IN NUMBER,
894 arg_plan_id IN NUMBER,
895 arg_instance_id IN NUMBER,
896 arg_supply_id IN NUMBER,
897 arg_batch_number IN NUMBER) return number is
898
899 v_res_max_capacity number;
900 v_assigned_units number;
901
902 BEGIN
903
904 if arg_resource_id =-1 or
905 arg_resource_id is null or
906 arg_dept_id is null or
907 arg_org_id is null or
908 arg_plan_id is null or
909 arg_batch_number is null or
910 arg_supply_id is null or
911 arg_instance_id is null then
912 return null;
913 end if;
914
915 select assigned_units
916 into v_assigned_units
917 from msc_resource_requirements
918 where department_id = arg_dept_id
919 and resource_id = arg_resource_id
920 and plan_id = arg_plan_id
921 and organization_id = arg_org_id
922 and sr_instance_id = arg_instance_id
923 and supply_id = arg_supply_id
924 and batch_number = arg_batch_number
925 and parent_id = 2
926 and rownum = 1;
927
928 select decode(max_capacity,0 , null,max_capacity)
929 into v_res_max_capacity
930 from msc_department_resources
931 where department_id = arg_dept_id
932 and resource_id = arg_resource_id
933 and plan_id = arg_plan_id
934 and organization_id = arg_org_id
935 and sr_instance_id = arg_instance_id;
936
937 return v_res_max_capacity * v_assigned_units;
938
939 END resource_max_capacity;
940
941 /*
942 FUNCTION sales_order (arg_demand_id IN NUMBER)
943 return varchar2
944 IS
945
946 order_number varchar2(240);
947
948 cursor C4 is
949 select sales_order_number
950 from msc_demands
951 where demand_id = arg_demand_id;
952 BEGIN
953 if arg_demand_id is null
954 then return null;
955 end if;
956 OPEN C4;
957 Loop
958 Fetch C4 into order_number;
959 Exit;
960 END Loop;
961 Close C4;
962 return(order_number);
963 END sales_order;
964 */
965
966 FUNCTION from_org(arg_plan_id IN NUMBER,
967 arg_transaction_id IN NUMBER,
968 arg_instance_id IN NUMBER) return varchar2 IS
969 l_org_id NUMBER;
970 BEGIN
971 if arg_plan_id is null OR arg_transaction_id is null then
972 return null;
973 else
974 select from_organization_id
975 into l_org_id
976 from msc_interorg_ship_methods
977 where transaction_id = arg_transaction_id
978 and plan_id = arg_plan_id;
979
983
980 return msc_get_name.org_code(l_org_id,arg_instance_id);
981 end if;
982 END;
984 FUNCTION to_org(arg_plan_id IN NUMBER,
985 arg_transaction_id IN NUMBER,
986 arg_instance_id IN NUMBER) return varchar2 IS
987 l_org_id NUMBER;
988 BEGIN
989 if arg_plan_id is null OR arg_transaction_id is null then
990 return null;
991 else
992 select to_organization_id
993 into l_org_id
994 from msc_interorg_ship_methods
995 where transaction_id = arg_transaction_id
996 and plan_id = arg_plan_id;
997
998 return msc_get_name.org_code(l_org_id,arg_instance_id);
999 end if;
1000 END to_org;
1001
1002 FUNCTION from_org_id(arg_plan_id IN NUMBER,
1003 arg_transaction_id IN NUMBER,
1004 arg_instance_id IN NUMBER) return number IS
1005 l_org_id NUMBER;
1006 BEGIN
1007 if arg_plan_id is null OR arg_transaction_id is null then
1008 return null;
1009 else
1010 select from_organization_id
1011 into l_org_id
1012 from msc_interorg_ship_methods
1013 where transaction_id = arg_transaction_id
1014 and plan_id = arg_plan_id;
1015
1016 return l_org_id;
1017 end if;
1018 END;
1019
1020 FUNCTION to_org_id(arg_plan_id IN NUMBER,
1021 arg_transaction_id IN NUMBER,
1022 arg_instance_id IN NUMBER) return number IS
1023 l_org_id NUMBER;
1024 BEGIN
1025 if arg_plan_id is null OR arg_transaction_id is null then
1026 return null;
1027 else
1028 select to_organization_id
1029 into l_org_id
1030 from msc_interorg_ship_methods
1031 where transaction_id = arg_transaction_id
1032 and plan_id = arg_plan_id;
1033
1034 return l_org_id;
1035 end if;
1036 END;
1037
1038 FUNCTION ship_method(arg_plan_id IN NUMBER,
1039 arg_transaction_id IN NUMBER,
1040 arg_instance_id IN NUMBER) return varchar2 IS
1041 l_ship_method varchar2(100);
1042 BEGIN
1043 if arg_plan_id is null OR arg_transaction_id is null then
1044 return null;
1045 else
1046 select ship_method
1047 into l_ship_method
1048 from msc_interorg_ship_methods
1049 where transaction_id = arg_transaction_id
1050 and plan_id = arg_plan_id;
1051
1052 return l_ship_method;
1053 end if;
1054 END;
1055
1056 FUNCTION item_name(arg_item_id IN NUMBER,
1057 arg_org_id IN NUMBER,
1058 arg_plan_id IN NUMBER,
1059 arg_instance_id IN NUMBER) return varchar2 IS
1060 v_item_name varchar2(250);
1061 BEGIN
1062 if arg_item_id is null or
1063 arg_item_id < 0 then
1064 return null;
1065 elsif arg_org_id is null or
1066 arg_plan_id is null or
1067 arg_instance_id is null then
1068 select item_name
1069 into v_item_name
1070 from msc_items
1071 where inventory_item_id = arg_item_id;
1072 return v_item_name;
1073 end if;
1074
1075 select item_name
1076 into v_item_name
1077 from msc_system_items
1078 where inventory_item_id = arg_item_id
1079 and plan_id = arg_plan_id
1080 and organization_id = arg_org_id
1081 and sr_instance_id = arg_instance_id;
1082 return v_item_name;
1083
1084 exception when no_data_found then
1085 select item_name
1086 into v_item_name
1087 from msc_items
1088 where inventory_item_id = arg_item_id;
1089 return v_item_name;
1090
1091 END item_name;
1092
1093 --For SRP
1094 FUNCTION item_name(arg_item_id IN NUMBER) return varchar2 IS
1095 CURSOR ITEM_CUR(P_INV_ITEM_ID IN NUMBER) IS
1096 select ITEM_NAME
1097 from MSC_SYSTEM_ITEMS
1098 WHERE INVENTORY_ITEM_ID = P_INV_ITEM_ID
1099 AND PLAN_ID =-1;
1100
1101 L_ITEM_NAME VARCHAR2(250);
1102 BEGIN
1103 OPEN ITEM_CUR(arg_item_id);
1104 FETCH ITEM_cUR INTO L_ITEM_NAME;
1105 CLOSE ITEM_cUR;
1106 RETURN L_ITEM_NAME;
1107 END item_name;
1108
1109 FUNCTION planner_code(arg_item_id IN NUMBER,
1110 arg_org_id IN NUMBER,
1111 arg_plan_id IN NUMBER,
1112 arg_instance_id IN NUMBER) return varchar2 IS
1113 v_planner_code varchar2(40);
1114 BEGIN
1115 if ( arg_item_id is null ) or
1116 ( arg_item_id < 0 ) or
1117 ( arg_plan_id is null ) or
1118 ( arg_org_id is null ) or
1119 ( arg_instance_id is null ) then
1120 return null;
1121 end if;
1122
1123 select planner_code
1124 into v_planner_code
1125 from msc_system_items
1126 where inventory_item_id = arg_item_id
1127 and plan_id = arg_plan_id
1128 and organization_id = arg_org_id
1129 and sr_instance_id = arg_instance_id;
1130 return v_planner_code;
1131
1132 exception when no_data_found then
1133 return null;
1134
1135 END planner_code;
1136
1137 FUNCTION supplier(arg_supplier_id IN NUMBER) return varchar2 IS
1138 supplier_name varchar2(240);
1139 BEGIN
1140
1141 if arg_supplier_id is null then
1142 return null;
1143 end if;
1144 select partner_name
1148 and partner_id=arg_supplier_id;
1145 into supplier_name
1146 from msc_trading_partners
1147 where partner_type=1
1149
1150 return supplier_name;
1151
1152 END supplier;
1153
1154 FUNCTION supplier_site(arg_supplier_site_id IN NUMBER) return varchar2 IS
1155 supplier_site varchar2(240);
1156 BEGIN
1157
1158 if arg_supplier_site_id is null then
1159 return null;
1160 end if;
1161 select tp_site_code
1162 into supplier_site
1163 from msc_trading_partner_sites
1164 where partner_site_id=arg_supplier_site_id;
1165
1166 return supplier_site;
1167
1168 END supplier_site;
1169
1170 FUNCTION customer(arg_customer_id IN NUMBER) return varchar2 IS
1171 customer_name varchar2(240);
1172 BEGIN
1173
1174 if arg_customer_id is null then
1175 return null;
1176 end if;
1177 select partner_name
1178 into customer_name
1179 from msc_trading_partners
1180 where partner_type=2
1181 and partner_id=arg_customer_id;
1182
1183 return customer_name;
1184
1185 END customer;
1186
1187 FUNCTION customer_site(arg_customer_site_id IN NUMBER) return varchar2 IS
1188 customer_site varchar2(240);
1189 BEGIN
1190 if arg_customer_site_id is null or
1191 arg_customer_site_id <= 0 then
1192 return null;
1193 end if;
1194 select location
1195 into customer_site
1196 from msc_trading_partner_sites
1197 where partner_site_id=arg_customer_site_id;
1198
1199 return customer_site;
1200
1201 END customer_site;
1202
1203 FUNCTION customer_address(arg_customer_site_id IN NUMBER) return varchar2 IS
1204 customer_address varchar2(1600);
1205 BEGIN
1206 if arg_customer_site_id is null or
1207 arg_customer_site_id <= 0 then
1208 return null;
1209 end if;
1210 select PARTNER_ADDRESS
1211 into customer_address
1212 from msc_trading_partner_sites
1213 where partner_site_id=arg_customer_site_id;
1214
1215 return customer_address;
1216
1217 END customer_address;
1218
1219 FUNCTION action(arg_source_table IN VARCHAR2,
1220 arg_bom_item_type IN NUMBER DEFAULT NULL,
1221 arg_base_item_id IN NUMBER DEFAULT NULL,
1222 arg_wip_supply_type IN NUMBER DEFAULT NULL,
1223 arg_order_type IN NUMBER DEFAULT NULL,
1224 arg_rescheduled_flag IN NUMBER DEFAULT NULL,
1225 arg_disposition_status_type IN NUMBER DEFAULT NULL,
1226 arg_new_due_date IN DATE DEFAULT NULL,
1227 arg_old_due_date IN DATE DEFAULT NULL,
1228 arg_implemented_quantity IN NUMBER DEFAULT NULL,
1229 arg_quantity_in_process IN NUMBER DEFAULT NULL,
1230 arg_quantity_rate IN NUMBER DEFAULT NULL,
1231 arg_release_time_fence_code IN NUMBER DEFAULT NULL,
1232 arg_reschedule_days IN NUMBER DEFAULT NULL,
1233 arg_firm_quantity IN NUMBER DEFAULT NULL,
1234 arg_plan_id IN NUMBER DEFAULT NULL,
1235 arg_critical_component IN NUMBER DEFAULT NULL,
1236 arg_mrp_planning_code IN NUMBER DEFAULT NULL,
1237 arg_lots_exist IN NUMBER DEFAULT NULL,
1238 arg_part_condition IN NUMBER DEFAULT NULL) RETURN varchar2
1239
1240 IS
1241
1242 CURSOR OPTIONS_C IS
1243 SELECT NVL(orders_release_configs,'N'),
1244 NVL(orders_release_phantoms,'N')
1245 FROM msc_workbench_display_options
1246 WHERE user_id = fnd_global.user_id;
1247
1248 CURSOR internal_sales(p_plan_id number,
1249 p_tran_id number, p_inst_id number) IS
1250 select ms.disposition_status_type,
1251 msc_get_name.action('MSC_SUPPLIES', msi.bom_item_type,
1252 msi.base_item_id, msi.wip_supply_type, ms.order_type,
1253 DECODE(ms.firm_planned_type,1,1,ms.reschedule_flag),
1254 ms.disposition_status_type,
1255 ms.new_schedule_date, ms.old_schedule_date,
1256 ms.implemented_quantity, ms.quantity_in_process,
1257 ms.new_order_quantity,
1258 msi.release_time_fence_code, ms.reschedule_days,
1259 ms.firm_quantity,ms.plan_id,
1260 msi.critical_component_flag, msi.mrp_planning_code,
1261 msi.lots_exist)
1262 from msc_supplies ms,
1263 msc_system_items msi
1264 where ms.plan_id = p_plan_id
1265 and ms.transaction_id = p_tran_id
1266 and ms.sr_instance_id = p_inst_id
1267 and ms.plan_id = msi.plan_id
1268 and ms.sr_instance_id = msi.sr_instance_id
1269 and ms.organization_id = msi.organization_id
1270 and ms.inventory_item_id = msi.inventory_item_id ;
1271
1272 CURSOR excp_exist(p_plan_id number,
1273 p_demand_id number) IS
1274 select 1
1275 from msc_exception_details med,
1276 msc_demands md
1277 where med.plan_id = md.plan_id
1278 and med.organization_id = md.organization_id
1279 and med.sr_instance_id = md.sr_instance_id
1280 and med.inventory_item_id = md.inventory_item_id
1281 and med.number1 = md.demand_id
1285 and (nvl(md.source_organization_id, -1) < 0 or -- can not release ISO
1282 and med.exception_type in (70,71)
1283 and md.plan_id = p_plan_id
1284 and md.demand_id = p_demand_id
1286 (md.source_organization_id >0 and
1287 md.source_org_instance_id <> md.sr_instance_id));
1288
1289 CURSOR type_of_plan(p_plan_id number) IS
1290 select plan_type
1291 from msc_plans
1292 where plan_id = p_plan_id;
1293
1294
1295 l_action VARCHAR2(80);
1296 l_configs VARCHAR2(3) := 'X';
1297 l_phantoms VARCHAR2(3) := 'X';
1298 v_temp number;
1299 l_plan_type NUMBER;
1300 v_ir_action varchar2(80);
1301
1302 CURSOR bkt_c(p_date date) IS
1303 select bucket_index
1304 from msc_plan_buckets mpb,
1305 msc_plans mp
1306 where mp.plan_id = arg_plan_id
1307 and mp.plan_id = mpb.plan_id
1308 and mp.organization_id = mpb.organization_id
1309 and mp.sr_instance_id = mpb.sr_instance_id
1310 and p_date between mpb.bkt_start_date and mpb.bkt_end_date;
1311
1312 p_new_date_index number;
1313 p_old_date_index number;
1314
1315 BEGIN
1316
1317 IF arg_source_table = 'MSC_SUPPLIES' THEN
1318 IF arg_lots_exist = 2 and
1319 arg_order_type =5 and
1320 arg_quantity_rate <> 0 then
1321 -- plan generated planned order from supply schedule
1322 return msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1323 END IF;
1324
1325 IF NVL(arg_release_time_fence_code,-1) = 7 THEN
1326 -- Shikyu item
1327 return msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1328 END IF;
1329
1330 IF arg_base_item_id IS NOT NULL
1331 OR arg_wip_supply_type = 6 THEN
1332 OPEN OPTIONS_C;
1333 FETCH OPTIONS_C INTO l_configs, l_phantoms;
1334 CLOSE OPTIONS_C;
1335 END IF;
1336
1337 IF arg_bom_item_type IN (1, 2, 3, 5) OR
1338 (arg_base_item_id IS NOT NULL AND
1339 l_configs = 'N') OR
1340 (arg_wip_supply_type = 6 AND
1341 l_phantoms = 'N') OR
1342 arg_order_type IN (7, 14, 15, 16, 17, 18, 19, 27, 28)
1343 THEN
1344
1345 -- Model Option Class
1346 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1347
1348 ELSE
1349
1350 IF arg_rescheduled_flag = 1 and arg_order_type not in (5,51) THEN
1351
1352 -- already took action
1353 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1354
1355 ELSIF arg_disposition_status_type = 2 THEN
1356 -- Cancel order
1357 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',1);
1358
1359 --pabram.srp.changes.added 76,77,78
1360 ELSIF arg_order_type in (5,51,13,76,77,78) THEN
1361 IF (NVL(arg_implemented_quantity, 0) +
1362 NVL(arg_quantity_in_process, 0) >=
1363 nvl(arg_firm_quantity,arg_quantity_rate))
1364 OR NVL(arg_release_time_fence_code,-1) = 6 THEN
1365
1366 -- nvl(arg_firm_quantity, is added for partial release
1367 -- Planned order has been released
1368 -- set action to none
1369 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1370
1371 ELSIF arg_order_type = 51 and
1372 nvl(arg_part_condition,1) = 2 THEN
1373 -- 6676007, defective part, set action to none
1374 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1375 ELSE
1376 -- Release
1377 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',4);
1378
1379 END IF;
1380
1381 ELSIF nvl(arg_reschedule_days,0) <> 0 THEN
1382 --bug#1979161, new_due_date might differ from old_due_date due to nonworking
1383 -- day issue
1384 IF (arg_order_type in (8,11,12) ) then
1385 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1386 ELSE
1387 --bug5984599, need to check if they are in same planning bucket
1388 OPEN bkt_c(trunc(arg_new_due_date));
1389 FETCH bkt_c INTO p_new_date_index;
1390 CLOSE bkt_c;
1391
1392 OPEN bkt_c(trunc(arg_old_due_date));
1393 FETCH bkt_c INTO p_old_date_index;
1394 CLOSE bkt_c;
1395
1396 IF p_new_date_index = p_old_date_index THEN
1397
1398 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1399
1400 ELSIF trunc(arg_new_due_date) > trunc(arg_old_due_date) THEN
1401 -- p_old_date_index could be null, if so, use old logic
1402
1403 -- Reschedule out
1404 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',3);
1405
1406 ELSIF trunc(arg_new_due_date) < trunc(arg_old_due_date) THEN
1407
1408 -- Reschedule in
1409 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',2);
1410 END IF;
1411 END IF;
1412 ELSIF arg_order_type in (4,13) THEN
1413 IF arg_bom_item_type IN (1, 2, 3, 5) THEN
1414
1415 -- Model option class
1416 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1417
1418 ELSE
1419
1420 -- Release
1421 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',4);
1422 END IF;
1423 ELSE
1424
1428 END IF;
1425 -- Action is not required.
1426 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1427 END IF;
1429 -- action should be set to NONE for the critical components
1430 -- Plan type = MPS, item attribute <>( MPS planning or MPS/DRP planning),
1431 -- Plan type = DRP, item attribute <> (DRP planning
1432 -- or MRP/DRP planning or MPS/DRP planning) .
1433 -- ASCP UI displays action NONE for all critical components.
1434 IF arg_critical_component = 1 THEN
1435 OPEN type_of_plan(arg_plan_id);
1436 FETCH type_of_plan INTO l_plan_type;
1437 CLOSE type_of_plan;
1438
1439 IF l_plan_type = 2 and -- MPS
1440 arg_mrp_planning_code not in (4,8 ) THEN
1441
1442 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1443
1444 ELSIF l_plan_type = 3 and -- DRP
1445 arg_mrp_planning_code NOT in (9, 7, 8) THEN
1446
1447 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1448
1449 END IF;
1450 END IF;
1451
1452 ELSE
1453
1454 -- This record does note come from MSC_SUPPLIES
1455 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1456 IF NVL(arg_release_time_fence_code,-1) = 7 THEN
1457 -- Shikyu item
1458 RETURN l_action;
1459 END IF;
1460
1461 if arg_source_table = 'MSC_DEMANDS' and
1462 arg_plan_id <> -1 and -- plan_id <> -1
1463 arg_order_type in (30,6) then -- sales order
1464 OPEN internal_sales(arg_plan_id, -- plan_id
1465 arg_base_item_id, -- disposition_id
1466 arg_wip_supply_type); -- sr_instance_id
1467 FETCH internal_sales INTO v_temp, v_ir_action;
1468 CLOSE internal_sales;
1469 -- IRproject
1470 --if v_temp = 2 then -- cancel
1471 -- l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',1);
1472 if arg_bom_item_type in ( 5, 1) and -- plan_type
1473 v_ir_action is not null then -- use action from related IR
1474 l_action := v_ir_action;
1475 else
1476 if arg_rescheduled_flag = 1 then -- already reschedule
1477 -- none
1478 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1479 elsif arg_quantity_rate <>
1480 nvl(arg_firm_quantity, -- old_demand_qty
1481 arg_quantity_rate) then
1482 -- can not release split so, bug6122088
1483 -- none
1484 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1485 elsif arg_release_time_fence_code <> -- inventory_item_id
1486 arg_reschedule_days and
1487 arg_reschedule_days <> 0 then -- original_item_id
1488 -- release, bug5972090
1489 -- item_id <> original_item_id and not split
1490 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',4);
1491 else
1492 if arg_bom_item_type = 5 then -- plan_type
1493 if nvl(arg_implemented_quantity,2) =1 or --sales_order_line_split
1494 nvl(arg_quantity_in_process,2) =1 then --fill_kill_flag
1495 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1496 return l_action;
1497 end if;
1498 end if;
1499 v_temp :=0;
1500 OPEN excp_exist(arg_plan_id, -- plan_id
1501 arg_disposition_status_type); -- demand_id
1502 FETCH excp_exist INTO v_temp;
1503 CLOSE excp_exist;
1504
1505 if v_temp = 1 then -- Sales Order changes excp exists
1506 -- release
1507 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',4);
1508 else
1509 -- none
1510 l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1511 end if;
1512 end if; -- if arg_rescheduled_flag = 1
1513 end if; -- if v_temp = 2 then -- cancel
1514 end if; -- if arg_source_table = 'MSC_DEMANDS'
1515
1516
1517 END IF;
1518 return l_action;
1519 END action;
1520
1521 /* this function takes a date and a number (seconds to represent the
1522 time since 00:00:00 of this date) and return a date */
1523 FUNCTION Date_Timenum_to_DATE(dt dATE, time number)
1524 RETURN DATE
1525 IS
1526 BEGIN
1527 return float_to_DT(DT_to_float(dt) + time/86400);
1528 END Date_Timenum_to_DATE;
1529
1530 /* this function returns the julian date in floating point format */
1531 FUNCTION DT_to_float(dt DATE)
1532 RETURN NUMBER
1533 IS
1534 BEGIN
1535 RETURN to_number(dt - to_date(1,'J'))+1;
1536
1537 END DT_to_float;
1538
1539 /* this function takes a julian date in a floating point format and returns a date */
1540 FUNCTION float_to_DT(fdt NUMBER)
1541 RETURN DATE
1542 IS
1543 BEGIN
1544 RETURN to_date(1,'J')+(fdt-1);
1545
1546 END float_to_DT;
1547
1548 -- This procedure executes dynamic sql because we cannot run
1549 -- it on the client
1550 PROCEDURE execute_dsql(arg_sql_stmt VARCHAR2) IS
1551
1555
1552 BEGIN
1553
1554 EXECUTE IMMEDIATE arg_sql_stmt;
1556 END execute_dsql;
1557
1558 FUNCTION cfm_routing_flag(p_plan_id IN NUMBER,
1559 p_org_id IN NUMBER,
1560 p_instance_id IN NUMBER,
1561 p_item_id IN NUMBER,
1562 p_alt_rtg_desig IN VARCHAR2) return number IS
1563
1564 CURSOR ALT_RTG_C IS
1565 SELECT cfm_routing_flag
1566 FROM msc_routings
1567 WHERE plan_id = p_plan_id
1568 AND sr_instance_id = p_instance_id
1569 AND organization_id = p_org_id
1570 AND assembly_item_id = p_item_id
1571 AND alternate_routing_designator = p_alt_rtg_desig;
1572
1573 CURSOR NULL_ALT_RTG_C IS
1574 SELECT cfm_routing_flag
1575 FROM msc_routings
1576 WHERE plan_id = p_plan_id
1577 AND sr_instance_id = p_instance_id
1578 AND organization_id = p_org_id
1579 AND assembly_item_id = p_item_id
1580 AND alternate_routing_designator IS NULL;
1581
1582 l_cfm_routing_flag NUMBER;
1583
1584 BEGIN
1585
1586 IF p_alt_rtg_desig IS NULL THEN
1587 OPEN NULL_ALT_RTG_C;
1588 FETCH NULL_ALT_RTG_C INTO l_cfm_routing_flag;
1589 CLOSE NULL_ALT_RTG_C;
1590 ELSE
1591 OPEN ALT_RTG_C;
1592 FETCH ALT_RTG_C INTO l_cfm_routing_flag;
1593 CLOSE ALT_RTG_C;
1594 END IF;
1595
1596 return(l_cfm_routing_flag);
1597
1598 END cfm_routing_flag;
1599
1600 FUNCTION alternate_bom(p_plan_id IN NUMBER,
1601 p_instance_id IN NUMBER,
1602 p_seq_id IN NUMBER) return varchar2 IS
1603
1604 CURSOR ALT_BOM_C IS
1605 SELECT alternate_bom_designator
1606 FROM msc_boms
1607 WHERE plan_id = p_plan_id
1608 AND sr_instance_id = p_instance_id
1609 AND bill_sequence_id = p_seq_id;
1610
1611 l_alt_bom VARCHAR2(40); --5338566 bugfix, length changed to 40
1612
1613 BEGIN
1614
1615 if p_seq_id is null then
1616 return null;
1617 end if;
1618
1619 OPEN ALT_BOM_C;
1620 FETCH ALT_BOM_C INTO l_alt_bom;
1621 CLOSE ALT_BOM_C;
1622
1623 return(l_alt_bom);
1624
1625 END alternate_bom;
1626
1627 FUNCTION alternate_rtg(p_plan_id IN NUMBER,
1628 p_instance_id IN NUMBER,
1629 p_seq_id IN NUMBER) return varchar2 IS
1630
1631 CURSOR ALT_RTG_C IS
1632 SELECT alternate_routing_designator
1633 FROM msc_routings
1634 WHERE plan_id = p_plan_id
1635 AND sr_instance_id = p_instance_id
1636 AND routing_sequence_id = p_seq_id;
1637
1638 l_alt_rtg VARCHAR2(40); --5338566 bugfix, length changed to 40
1639
1640
1641 BEGIN
1642
1643 if p_seq_id is null then
1644 return null;
1645 end if;
1646
1647 OPEN ALT_RTG_C;
1648 FETCH ALT_RTG_C INTO l_alt_rtg;
1649 CLOSE ALT_RTG_C;
1650
1651 return(l_alt_rtg);
1652
1653 END alternate_rtg;
1654
1655 FUNCTION cfm_routing_flag(p_plan_id IN NUMBER,
1656 p_instance_id IN NUMBER,
1657 p_seq_id IN NUMBER) return number IS
1658
1659 CURSOR ALT_RTG_C IS
1660 SELECT cfm_routing_flag
1661 FROM msc_routings
1662 WHERE plan_id = p_plan_id
1663 AND sr_instance_id = p_instance_id
1664 AND routing_sequence_id = p_seq_id;
1665
1666 l_cfm_routing_flag NUMBER;
1667
1668 BEGIN
1669
1670 if p_seq_id is null then
1671 return null;
1672 end if;
1673
1674 OPEN ALT_RTG_C;
1675 FETCH ALT_RTG_C INTO l_cfm_routing_flag;
1676 CLOSE ALT_RTG_C;
1677
1678 return(l_cfm_routing_flag);
1679
1680 END cfm_routing_flag;
1681
1682 --#2479454 bug-fix
1683 --p_fcst_set_id param added
1684 --so that if this is passed,return fcst name designator , else return the designator
1685 FUNCTION designator(p_desig_id IN NUMBER,
1686 p_fcst_set_id IN NUMBER default NULL,
1687 p_plan_id IN NUMBER default NULL ) return varchar2 IS
1688
1689 CURSOR DESIG_C(v_desig_id number) IS
1690 SELECT designator, forecast_set_id
1691 FROM msc_designators
1692 WHERE designator_id = v_desig_id;
1693
1694 CURSOR DESIG_FC IS
1695 SELECT designator
1696 FROM msc_designators
1697 WHERE forecast_set_id = p_fcst_set_id
1698 and designator_id = p_desig_id;
1699
1700 v_fcst_set_id number;
1701 v_fcst_set varchar2(30);
1702 l_desig VARCHAR2(30);
1703
1704 -- modification for bug 2765939
1705 -- Return null if it is a dp scenario and the plan_id is passed in function call
1706 cursor is_forecast_c is
1707 select 1
1708 from msc_designators desig,
1709 msc_plan_schedules sch
1710 where desig.designator_id = p_desig_id
1711 and desig.designator_type = 6
1712 and sch.plan_id=p_plan_id
1713 and sch.input_schedule_id = desig.forecast_set_id;
1714
1715 is_forecast number;
1716
1717 BEGIN
1718 if p_plan_id is null then
1719 if p_desig_id is null then
1720 return null;
1721 end if;
1722
1723 if ( p_fcst_set_id is not null ) then
1724
1725 OPEN DESIG_FC;
1726 FETCH DESIG_FC INTO l_desig;
1727 CLOSE DESIG_FC;
1728
1729 return(l_desig);
1730 end if;
1731
1735
1732 OPEN DESIG_C(p_desig_id);
1733 FETCH DESIG_C INTO l_desig, v_fcst_set_id;
1734 CLOSE DESIG_C;
1736 IF v_fcst_set_id is not null and
1737 p_fcst_set_id is null THEN
1738 -- 5496276, if p_fcst_set_id is not null,
1739 -- fcst_name might be shown arleady in function scenario_designator
1740 OPEN DESIG_C(v_fcst_set_id);
1741 FETCH DESIG_C INTO v_fcst_set, v_fcst_set_id;
1742 CLOSE DESIG_C;
1743 l_desig := v_fcst_set||'/'||l_desig;
1744 END IF;
1745
1746 return(l_desig);
1747 else -- if p_plan_id is not null then
1748 if (p_desig_id is null ) then
1749 return null;
1750 end if;
1751
1752 is_forecast := 0;
1753
1754 OPEN is_forecast_c;
1755 FETCH is_forecast_c into is_forecast;
1756 CLOSE is_forecast_c;
1757
1758 if is_forecast = 1 then -- forecast
1759 OPEN DESIG_C(p_desig_id);
1760 FETCH DESIG_C INTO l_desig, v_fcst_set_id;
1761 CLOSE DESIG_C;
1762
1763 return(l_desig);
1764 else
1765 return null;
1766 end if;
1767
1768 end if;
1769 END designator;
1770 -- Rama Rao
1771 -- 04/12/01
1772 -- Modified the scenario_designator function to remove the organization_id condition
1773 -- from the where clause of c_scenario_name cursor. Made the same change to the
1774 -- forecastsetname function.
1775
1776 FUNCTION scenario_designator(p_desig_id IN NUMBER,
1777 p_plan_id IN NUMBER,
1778 p_organization_id IN NUMBER,
1779 p_instance_id IN NUMBER) RETURN VARCHAR2 IS
1780
1781
1782 cursor c_designator_type_g is
1783 SELECT designator_type
1784 FROM msc_plan_schedules
1785 WHERE plan_id = p_plan_id
1786 AND organization_id = -1
1787 AND sr_instance_id = p_instance_id
1788 AND input_schedule_id = p_desig_id;
1789
1790 CURSOR c_designator_type IS
1791 SELECT designator_type
1792 FROM msc_plan_schedules
1793 WHERE plan_id = p_plan_id
1794 AND organization_id = p_organization_id
1795 AND sr_instance_id = p_instance_id
1796 AND input_schedule_id = p_desig_id;
1797
1798 CURSOR c_coll_designator_type IS
1799 SELECT designator_type
1800 FROM msc_plan_schedules
1801 WHERE organization_id = p_organization_id
1802 AND sr_instance_id = p_instance_id
1803 AND input_schedule_id = p_desig_id;
1804
1805 CURSOR DESIG_C IS
1806 SELECT designator
1807 FROM msc_designators
1808 WHERE designator_id = p_desig_id;
1809
1810 CURSOR c_scenario_name IS
1811 SELECT scenario_name
1812 FROM msd_dp_ascp_scenarios_v
1813 WHERE scenario_id = p_desig_id
1814 -- AND organization_id = p_organization_id
1815 -- AND sr_instance_id = p_instance_id; for bug 6040537
1816 and decode(sr_instance_id,
1817 -23453, p_instance_id, sr_instance_id) = p_instance_id;
1818
1819 l_desig VARCHAR2(30);
1820 l_designator_type NUMBER;
1821
1822 cursor l_count_c is
1823 SELECT count(*)
1824 FROM msc_plan_schedules
1825 WHERE plan_id = p_plan_id
1826 AND organization_id = p_organization_id
1827 AND sr_instance_id = p_instance_id
1828 AND input_schedule_id = p_desig_id;
1829
1830 l_count number;
1831
1832 BEGIN
1833
1834 if p_desig_id is null then
1835 return null;
1836 end if;
1837
1838 if p_plan_id <> -1 then
1839 open l_count_c;
1840 fetch l_count_c into l_count;
1841 close l_count_c;
1842 -- dbms_output.put_line('count is' || l_count);
1843 if l_count = 0 then
1844 OPEN c_designator_type_g;
1845 FETCH c_designator_type_g INTO l_designator_type;
1846 close c_designator_type_g;
1847 else
1848 OPEN c_designator_type;
1849 FETCH c_designator_type INTO l_designator_type;
1850 CLOSE c_designator_type;
1851 end if;
1852 else
1853 OPEN c_coll_designator_type;
1854 FETCH c_coll_designator_type INTO l_designator_type;
1855 CLOSE c_coll_designator_type;
1856 end if;
1857
1858
1859 -- dbms_output.put_line('designator type is ' || l_designator_type);
1860 IF l_designator_type = 6 THEN
1861 OPEN DESIG_C;
1862 FETCH DESIG_C INTO l_desig;
1863 CLOSE DESIG_C;
1864 ELSIF l_designator_type = 7 THEN
1865 OPEN c_scenario_name;
1866 FETCH c_scenario_name INTO l_desig;
1867 CLOSE c_scenario_name;
1868 END IF;
1869
1870 return(l_desig);
1871
1872 END scenario_designator;
1873
1874 FUNCTION forecastsetname(p_desig_id IN NUMBER,
1875 p_plan_id IN NUMBER,
1876 p_organization_id IN NUMBER,
1877 p_instance_id IN NUMBER) RETURN VARCHAR2 IS
1878 -- This cursor will return data only if p_desig_id is not forecast
1879 -- cnazarma
1880
1881 CURSOR c_designator_type IS
1882 SELECT designator_type
1883 FROM msc_plan_schedules
1884 WHERE plan_id = p_plan_id
1885 AND organization_id = p_organization_id
1886 AND sr_instance_id = p_instance_id
1887 AND input_schedule_id = p_desig_id;
1888
1889 CURSOR forecastset_name IS
1890 SELECT designator
1894 WHERE designator_id = p_desig_id
1891 FROM msc_designators
1892 WHERE designator_id = (SELECT NVL(forecast_set_id,p_desig_id)
1893 FROM msc_designators
1895 AND designator_type = 6);
1896
1897 CURSOR c_scenario_name IS
1898 SELECT scenario_name
1899 FROM msd_dp_ascp_scenarios_v
1900 WHERE scenario_id = p_desig_id
1901 -- AND organization_id = p_organization_id
1902 -- AND sr_instance_id = p_instance_id; -- for bug 6040537
1903 and decode(sr_instance_id,
1904 -23453, p_instance_id, sr_instance_id) = p_instance_id;
1905
1906 l_forecastset_name VARCHAR2(80);
1907 l_designator_type NUMBER;
1908
1909 BEGIN
1910
1911 IF p_desig_id is null then
1912 return null;
1913 END IF;
1914
1915 OPEN c_designator_type;
1916 FETCH c_designator_type INTO l_designator_type;
1917 CLOSE c_designator_type;
1918
1919
1920 IF l_designator_type = 7 THEN
1921
1922 OPEN c_scenario_name;
1923 FETCH c_scenario_name INTO l_forecastset_name;
1924 CLOSE c_scenario_name;
1925 ELSE
1926
1927 OPEN FORECASTSET_NAME;
1928 FETCH FORECASTSET_NAME INTO l_forecastset_name;
1929 CLOSE FORECASTSET_NAME;
1930 END IF;
1931
1932
1933 return(l_forecastset_name);
1934
1935 END forecastsetname;
1936
1937
1938 FUNCTION wip_status(p_transaction_id IN NUMBER) return number IS
1939
1940 CURSOR wip_cur IS
1941 SELECT wip_status_code
1942 FROM msc_supplies
1943 WHERE plan_id = -1
1944 and transaction_id = p_transaction_id;
1945 l_status number;
1946
1947 BEGIN
1948 Open wip_cur;
1949 Fetch wip_cur Into l_status;
1950 Close wip_cur;
1951
1952 return(l_status);
1953
1954 END wip_status;
1955
1956 FUNCTION source_demand_priority(p_plan_id number,
1957 p_demand_id NUMBER) return number IS
1958
1959 CURSOR dmd_cur IS
1960 SELECT order_priority
1961 FROM msc_demands
1962 WHERE plan_id = p_plan_id
1963 and demand_id = p_demand_id;
1964 l_priority number;
1965
1966 BEGIN
1967 Open dmd_cur;
1968 Fetch dmd_cur Into l_priority;
1969 Close dmd_cur;
1970
1971 return(l_priority);
1972
1973 END source_demand_priority;
1974
1975 FUNCTION batchable_uom(p_organization_id number,
1976 p_department_id number,
1977 p_resource_id number) return varchar2 IS
1978
1979 CURSOR batchable_uom_cur IS
1980 SELECT unit_of_measure
1981 FROM msc_department_resources
1982 WHERE plan_id > -1
1983 and organization_id = p_organization_id
1984 and department_id = p_department_id
1985 and resource_id = p_resource_id;
1986
1987 l_batchable_uom varchar2(3);
1988
1989 BEGIN
1990 Open batchable_uom_cur;
1991 Fetch batchable_uom_cur Into l_batchable_uom;
1992 Close batchable_uom_cur;
1993
1994 return(l_batchable_uom);
1995
1996 END batchable_uom;
1997
1998 FUNCTION plan_name(p_plan_id number) return varchar2 IS
1999
2000 CURSOR plan_name_cur IS
2001 SELECT compile_designator
2002 FROM msc_plans
2003 WHERE plan_id = p_plan_id;
2004 l_plan_name varchar2(30);
2005
2006 BEGIN
2007 Open plan_name_cur;
2008 Fetch plan_name_cur Into l_plan_name;
2009 Close plan_name_cur;
2010
2011 return(l_plan_name);
2012
2013 END plan_name;
2014 -- new function to calculate the number of workdays between two dates
2015 FUNCTION get_number_work_days(start_date date,
2016 end_date date,
2017 p_org_id number,
2018 p_inst_id number) return number IS
2019 v_start_seq NUMBER;
2020 v_end_seq NUMBER;
2021 calendar_code VARCHAR2(14);
2022 exc_set_id NUMBER;
2023
2024
2025 CURSOR seqnum_cursor(p_date DATE) IS
2026 SELECT next_seq_num
2027 FROM msc_calendar_dates
2028 WHERE calendar_code = calendar_code
2029 AND exception_set_id = exc_set_id
2030 AND sr_instance_id = p_inst_id
2031 AND calendar_date = p_date;
2032
2033 CURSOR prior_seqnum_cursor(p_date DATE) IS
2034 SELECT prior_seq_num
2035 FROM msc_calendar_dates
2036 WHERE calendar_code = calendar_code
2037 AND exception_set_id = exc_set_id
2038 AND sr_instance_id = p_inst_id
2039 AND calendar_date = p_date;
2040
2041 BEGIN
2042 -- --------------------------
2043 -- initialize calendar code
2044 -- --------------------------
2045 SELECT calendar_code, calendar_exception_set_id
2046 INTO calendar_code, exc_set_id
2047 FROM msc_trading_partners
2048 WHERE sr_tp_id = p_org_id
2049 AND sr_instance_id = p_inst_id
2050 AND partner_type = 3;
2051
2052 IF (trunc(start_date) <= trunc(end_date)) THEN
2053 OPEN seqnum_cursor(trunc(start_date));
2054 FETCH seqnum_cursor INTO v_start_seq;
2055 CLOSE seqnum_cursor;
2056 OPEN prior_seqnum_cursor(trunc(end_date));
2060 return (v_end_seq - v_start_seq + 1);
2057 FETCH prior_seqnum_cursor INTO v_end_seq;
2058 CLOSE prior_seqnum_cursor;
2059 if ( v_end_seq - v_start_seq + 1) <= 0 then return 1; end if;
2061 ELSE
2062 return(0);
2063 END IF;
2064
2065 END get_number_work_days;
2066
2067 FUNCTION demand_quantity(p_plan_id number,
2068 p_inst_id number,
2069 p_demand_id NUMBER) return number IS
2070
2071
2072 l_qty number;
2073 l_group_id NUMBER;
2074
2075 CURSOR grp_cur IS
2076 SELECT group_id
2077 FROM msc_demands
2078 WHERE plan_id = p_plan_id
2079 AND sr_instance_id = p_inst_id
2080 AND demand_id = p_demand_id;
2081
2082
2083 -- bug 2740285. Add check for group_id before
2084 -- opening the expensive dmd_cur.
2085
2086 CURSOR dmd_cur IS
2087 SELECT sum(using_requirement_quantity)
2088 FROM msc_demands
2089 WHERE plan_id = p_plan_id
2090 and sr_instance_id = p_inst_id
2091 and group_id = l_group_id;
2092
2093 BEGIN
2094 if ( p_plan_id is null OR p_inst_id is null or p_demand_id is null ) then
2095 return null;
2096 end if;
2097 OPEN grp_cur;
2098 FETCH grp_cur INTO l_group_id;
2099 CLOSE grp_cur;
2100 IF l_group_id IS NOT NULL THEN
2101 Open dmd_cur;
2102 Fetch dmd_cur Into l_qty;
2103 Close dmd_cur;
2104 return(l_qty);
2105 ELSE
2106 return NULL;
2107 END IF;
2108 END demand_quantity;
2109
2110 FUNCTION demand_order_number (p_plan_id number,
2111 p_inst_id number,
2112 p_demand_id NUMBER) return varchar2 IS
2113
2114 cursor c_demand_order_number is
2115 SELECT NVL(dem.order_number,
2116 decode(dem.origination_type,1, to_char(dem.disposition_id),
2117 3, msc_get_name.job_name(dem.disposition_id, dem.plan_id, dem.sr_instance_id),
2118 22, to_char(dem.disposition_id),
2119 29,decode(dem.plan_id, -11, msc_get_name.designator(dem.schedule_designator_id) ,
2120 decode(msi.in_source_plan,1,msc_get_name.designator(dem.schedule_designator_id,
2121 dem.forecast_set_id ),
2122 msc_get_name.scenario_designator(dem.forecast_set_id, dem.plan_id, dem.organization_id, dem.sr_instance_id)
2123 || decode(msc_get_name.designator(dem.schedule_designator_id,dem.forecast_set_id ), null, null, '/'||msc_get_name.designator(dem.schedule_designator_id,dem.forecast_set_id )))),
2124 msc_get_name.designator(dem.schedule_designator_id))) order_number
2125 from msc_demands dem, msc_system_items msi
2126 where dem.plan_id = p_plan_id
2127 and dem.sr_instance_id = p_inst_id
2128 and dem.demand_id = p_demand_id
2129 AND dem.plan_id = msi.plan_id
2130 AND dem.sr_instance_id = msi.sr_instance_id
2131 AND dem.ORGANIZATION_ID = msi.ORGANIZATION_ID
2132 AND dem.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID ;
2133
2134 cursor c_so_number is
2135 select sales_order_number
2136 from msc_sales_orders
2137 where sr_instance_id = p_inst_id
2138 and demand_id = p_demand_id;
2139
2140 l_order_number varchar2(200);
2141
2142 BEGIN
2143 if ( p_plan_id is null OR p_inst_id is null or p_demand_id is null ) then
2144 return null;
2145 end if;
2146 Open c_demand_order_number;
2147 Fetch c_demand_order_number Into l_order_number;
2148 Close c_demand_order_number;
2149
2150 if (l_order_number is null) then
2151 Open c_so_number;
2152 Fetch c_so_number Into l_order_number;
2153 Close c_so_number;
2154 end if;
2155
2156 return(l_order_number);
2157
2158 END demand_order_number ;
2159
2160 FUNCTION ABC_CLASS_ID (p_org_id number,
2161 p_inst_id number) return number is
2162 l_abc_class_id number;
2163 cursor abc_class_c (lp_org_id number,
2164 lp_inst_id number) is
2165 SELECT abc_class_id
2166 FROM msc_abc_classes
2167 WHERE organization_id = lp_org_id
2168 AND sr_instance_id = lp_inst_id
2169 ;
2170 begin
2171 open abc_class_c(p_org_id, p_inst_id);
2172 fetch abc_class_c into l_abc_class_id;
2173 close abc_class_c;
2174
2175 return l_abc_class_id;
2176
2177 end ABC_CLASS_ID;
2178
2179 FUNCTION DEMAND_CLASS (p_inst_id number,
2180 p_org_id number,
2181 p_plan varchar2) return varchar2 is
2182
2183 cursor c_dmdclass is
2184 select d.demand_class
2185 from msc_designators d
2186 where NVL(D.DESIGNATOR_TYPE,3) IN (2, 3, 4,5,-99)
2187 AND D.SR_INSTANCE_ID = p_inst_id
2188 AND D.ORGANIZATION_ID = p_org_id
2189 AND D.DESIGNATOR = p_plan ;
2190
2191 l_temp varchar2(100);
2192 begin
2193 if (p_inst_id is null or p_org_id is null or p_plan is null) then
2194 return null;
2195 else
2196 open c_dmdclass;
2197 fetch c_dmdclass into l_temp;
2198 close c_dmdclass;
2199 end if;
2200 return l_temp;
2201 end ;
2202
2203
2207 from msc_scheduling_rules msr
2204 FUNCTION DMD_PRIORITY_RULE (p_rule_id number) return varchar2 is
2205 cursor c_rule is
2206 select msr.meaning
2208 where msr.rule_id = p_RULE_ID
2209 and nvl(msr.enabled_flag, 'Y') = 'Y';
2210
2211 l_temp varchar2(240);
2212 begin
2213 if (p_rule_id is null) then
2214 return null;
2215 else
2216 open c_rule;
2217 fetch c_rule into l_temp;
2218 close c_rule;
2219 end if;
2220 return l_temp;
2221 end ;
2222
2223 FUNCTION OP_SEQ_NUM (p_plan_id number,
2224 p_inst_id number,
2225 p_org_id number,
2226 p_comp_seq_id number,
2227 p_bill_seq_id number,
2228 p_arg_1 number ) return varchar2 is
2229 cursor c_op_seq_num is
2230 select distinct mro.operation_seq_num
2231 from msc_operation_components moc,
2232 msc_routing_operations mro, msc_bom_components assy
2233 where moc.plan_id(+)= -1
2234 and moc.component_sequence_id(+)= assy.component_sequence_id
2235 and moc.sr_instance_id(+)= assy.sr_instance_id
2236 and moc.bill_sequence_id(+)= assy.bill_sequence_id
2237 and moc.plan_id=mro.plan_id(+)
2238 and moc.operation_sequence_id = mro.operation_sequence_id(+)
2239 and moc.sr_instance_id = mro.sr_instance_id(+)
2240 and moc.routing_sequence_id = mro.routing_sequence_id(+)
2241 and assy.plan_id = p_plan_id
2242 and assy.sr_instance_id = p_inst_id
2243 and assy.organization_id = p_org_id
2244 and assy.component_sequence_id = p_comp_seq_id
2245 and assy.bill_sequence_id = p_bill_seq_id ;
2246 l_temp varchar2(240);
2247 begin
2248 if (p_plan_id is null or
2249 p_inst_id is null or
2250 p_org_id is null or
2251 p_comp_seq_id is null or
2252 p_bill_seq_id is null) then
2253 return null;
2254 else
2255 open c_op_seq_num;
2256 fetch c_op_seq_num into l_temp;
2257 close c_op_seq_num;
2258 end if;
2259 return l_temp;
2260 exception
2261 when no_data_found then
2262 return null;
2263 end ;
2264
2265
2266 FUNCTION ss_method_text (p_plan_id in number,
2267 p_org_id in number, p_inst_id in number, p_item_id in number)
2268 return VARCHAR2 IS
2269 cursor l_ss_method is
2270 select decode(safety_stock_code,
2271 1,'Non-MRP Planned Percent',
2272 2,'MRP Planned Percent',null)
2273 from msc_system_items
2274 where plan_id = p_plan_id
2275 and sr_instancE_id = p_inst_id
2276 and organization_id = p_org_id
2277 and inventory_item_id = p_item_id;
2278
2279 l_ss_method_text varchar2(100);
2280
2281 BEGIN
2282 open l_ss_method;
2283 fetch l_ss_method into l_ss_method_text;
2284 close l_ss_method;
2285
2286 return l_ss_method_text;
2287 END ss_method_text;
2288
2289 FUNCTION demand_name (p_plan_id number, p_demand_id number)
2290 return varchar2 IS
2291 v_txt varchar2(100);
2292 cursor demand_c is
2293 select nvl(order_number,
2294 decode(origination_type,
2295 1,to_char(disposition_id),
2296 29, msc_get_name.scenario_designator(
2297 forecast_set_id,plan_id,organization_id,sr_instance_id),
2298 msc_get_name.designator(schedule_designator_id)
2299 )
2300 )
2301 from msc_demands
2302 where plan_id = p_plan_id
2303 and demand_id = p_demand_id;
2304 BEGIN
2305 if p_plan_id is null or
2306 p_demand_id is null then
2307 return null;
2308 end if;
2309 OPEN demand_c;
2310 FETCH demand_c INTO v_txt;
2311 CLOSE demand_c;
2312
2313 return v_txt;
2314 END demand_name;
2315
2316 FUNCTION forward_backward_days(p_plan_id number,
2317 p_schedule_desig_id number,
2318 p_fb_type number)
2319 return number IS
2320 -- Modified to check the existance of forecast by joining msc_designators
2321 -- to msc_plan_schedules
2322 -- Also modified to remove the sub queries
2323 cursor is_forecast_c is
2324 select 1
2325 from msc_designators desig,
2326 msc_plan_schedules sch
2327 where desig.designator_id = p_schedule_desig_id
2328 and desig.designator_type = 6
2329 and sch.plan_id=p_plan_id
2330 and sch.input_schedule_id = desig.forecast_set_id;
2331
2332 cursor is_forecast_cwb_c is
2333 select 1
2334 from msc_designators desig
2335 where desig.designator_id = p_schedule_desig_id
2336 and desig.designator_type = 6;
2337
2338 CURSOR is_dp_scenario_c is
2339 SELECT 1
2340 from msc_plan_schedules
2341 where plan_id = p_plan_id
2342 and input_schedule_id=p_schedule_desig_id
2343 and designator_type=7;
2344
2345 CURSOR forward_backward_days_c1 IS
2346 SELECT FORWARD_UPDATE_TIME_FENCE,
2347 BACKWARD_UPDATE_TIME_FENCE
2348 FROM msc_designators
2349 WHERE designator_id = p_schedule_desig_id;
2350
2351 CURSOR forward_backward_days_c2 IS
2352 SELECT curr_FORWARD_days,
2353 curr_BACKWARD_days
2357 l_forward_days number;
2354 FROM msc_plans
2355 WHERE plan_id = p_plan_id;
2356
2358 l_backward_days number;
2359
2360 is_forecast number;
2361 is_dp_scenario number;
2362
2363 BEGIN
2364 if (p_plan_id is null or
2365 p_schedule_desig_id is null or
2366 p_fb_type not in (1,2)) then
2367 return null;
2368 end if;
2369
2370 is_forecast := 0;
2371 is_dp_scenario := 0;
2372 if p_plan_id = -1 then
2373 OPEN is_forecast_cwb_c;
2374 FETCH is_forecast_cwb_c into is_forecast;
2375 CLOSE is_forecast_cwb_c;
2376 else
2377 OPEN is_forecast_c;
2378 FETCH is_forecast_c into is_forecast;
2379 CLOSE is_forecast_c;
2380 end if;
2381
2382 if is_forecast = 1 then -- forecast
2383 OPEN forward_backward_days_c1;
2384 FETCH forward_backward_days_c1 INTO l_forward_days, l_backward_days;
2385 CLOSE forward_backward_days_c1;
2386 else
2387 OPEN is_dp_scenario_c;
2388 FETCH is_dp_scenario_c into is_dp_scenario;
2389 CLOSE is_dp_scenario_c;
2390
2391 if is_dp_scenario = 1 then -- dp scenario
2392 OPEN forward_backward_days_c2;
2393 FETCH forward_backward_days_c2 INTO l_forward_days, l_backward_days;
2394 CLOSE forward_backward_days_c2;
2395 else
2396 l_forward_days := null;
2397 l_backward_days := null;
2398 end if;
2399 end if;
2400
2401
2402 if p_fb_type = 1 then
2403 return(l_forward_days);
2404 else
2405 return(l_backward_days);
2406 end if;
2407
2408 end forward_backward_days;
2409
2410 FUNCTION category_desc(arg_category_name IN VARCHAR2,
2411 arg_category_set_id IN NUMBER,
2412 arg_org_id IN NUMBER,
2413 arg_instance_id IN NUMBER) return varchar2 IS
2414
2415 category_description msc_item_categories.description%type;
2416
2417 CURSOR category_desc_org IS
2418 SELECT description
2419 FROM msc_item_categories
2420 WHERE category_set_id = arg_category_set_id
2421 AND category_name = arg_category_name
2422 AND sr_instance_id = arg_instance_id
2423 AND organization_id = arg_org_id
2424 AND description is NOT NULL
2425 AND rownum<2;
2426
2427 CURSOR category_desc_ins IS
2428 SELECT description
2429 FROM msc_item_categories
2430 WHERE category_set_id = arg_category_set_id
2431 AND category_name = arg_category_name
2432 AND sr_instance_id = arg_instance_id
2433 AND description is NOT NULL
2434 AND rownum<2;
2435
2436 CURSOR category_desc IS
2437 SELECT description
2438 FROM msc_item_categories
2439 WHERE category_set_id = arg_category_set_id
2440 AND category_name = arg_category_name
2441 AND description is NOT NULL
2442 AND rownum<2;
2443
2444 BEGIN
2445
2446 if ( arg_category_name is null ) or
2447 ( arg_category_set_id is null ) then
2448 return null;
2449 end if;
2450
2451 IF arg_org_id is NOT NULL AND arg_instance_id is not NULL THEN
2452
2453 OPEN category_desc_org;
2454 FETCH category_desc_org INTO category_description;
2455 CLOSE category_desc_org;
2456 return category_description;
2457
2458 ELSIF arg_org_id is NULL AND arg_instance_id is not NULL THEN
2459
2460 OPEN category_desc_ins;
2461 FETCH category_desc_ins INTO category_description;
2462 CLOSE category_desc_ins;
2463 return category_description;
2464
2465 ELSIF arg_org_id is NULL AND arg_instance_id is NULL THEN
2466
2467 OPEN category_desc;
2468 FETCH category_desc INTO category_description;
2469 CLOSE category_desc;
2470 return category_description;
2471
2472 ELSE
2473 return NULL;
2474 END IF;
2475
2476 exception when no_data_found then
2477 return null;
2478
2479 END category_desc;
2480
2481 FUNCTION MSCX_CUST_SHIP_DATE(arg_exception_id IN NUMBER) return date is
2482 l_date date;
2483 BEGIN
2484 if arg_exception_id is null OR
2485 arg_exception_id <1000 then
2486 return to_date(null);
2487 end if;
2488
2489 select decode(exception_column,
2490 'DATE1',date1,'DATE2',date2,'DATE3',date3,'DATE4',date4,
2491 'DATE5',date5,'DATE6',date6,'DATE7',date7)
2492 into l_date
2493 from msc_user_exception_components muec,
2494 msc_x_exception_details mxed
2495 where muec.exception_id = mxed.exception_type
2496 and mxed.exception_detail_id = arg_exception_id
2497 and muec.component_type = 1
2498 and muec.ak_attribute_code = 'MSCX_CUST_SHIP_DATE';
2499 return l_date;
2500 exception
2501 when no_data_found then
2502 return to_date(null);
2503 END MSCX_CUST_SHIP_DATE;
2504
2505 FUNCTION MSCX_UDE_PUB_ORDER_TYPE (arg_exception_id IN NUMBER) return number is
2506 l_temp number;
2507 BEGIN
2508 if arg_exception_id is null OR
2509 arg_exception_id <1000 then
2510 return to_number(null);
2514 'NUMBER3',NUMBER3,'NUMBER4',NUMBER4,'NUMBER5',NUMBER5,'NUMBER6',NUMBER6,'NUMBER7',NUMBER7)
2511 end if;
2512
2513 select decode(muec.exception_column, 'NUMBER1',NUMBER1,'NUMBER2',NUMBER2,
2515 into l_temp
2516 from msc_user_exception_components muec,
2517 msc_x_exception_details mxed
2518 where muec.exception_id = mxed.exception_type
2519 and mxed.exception_detail_id = arg_exception_id
2520 and muec.component_type = 1
2521 and muec.ak_attribute_code = 'MSCX_UDE_PUB_ORDER_TYPE';
2522 return l_temp;
2523 exception
2524 when no_data_found then
2525 return to_number(null);
2526 END MSCX_UDE_PUB_ORDER_TYPE;
2527
2528 FUNCTION MSCX_PLANNER_CODE (arg_exception_id IN NUMBER) return varchar2 is
2529 l_temp varchar2(250);
2530 BEGIN
2531 if arg_exception_id is null OR
2532 arg_exception_id <1000 then
2533 return null;
2534 end if;
2535
2536 select decode(muec.exception_column,
2537 'USER_ATTRIBUTE1',USER_ATTRIBUTE1,'USER_ATTRIBUTE2',USER_ATTRIBUTE2,
2538 'USER_ATTRIBUTE3',USER_ATTRIBUTE3,'USER_ATTRIBUTE4',USER_ATTRIBUTE4,
2539 'USER_ATTRIBUTE5',USER_ATTRIBUTE5,'USER_ATTRIBUTE6',USER_ATTRIBUTE6,
2540 'USER_ATTRIBUTE7',USER_ATTRIBUTE7)
2541 into l_temp
2542 from msc_user_exception_components muec,
2543 msc_x_exception_details mxed
2544 where muec.exception_id = mxed.exception_type
2545 and mxed.exception_detail_id = arg_exception_id
2546 and muec.component_type = 1
2547 and muec.ak_attribute_code = 'MSCX_PLANNER_CODE';
2548 return l_temp;
2549 exception
2550 when no_data_found then
2551 return null;
2552 END MSCX_PLANNER_CODE;
2553
2554 FUNCTION MSCX_QUANTITY (arg_exception_id IN NUMBER) return number is
2555 l_temp number;
2556 BEGIN
2557 if arg_exception_id is null OR
2558 arg_exception_id <1000 then
2559 return to_number(null);
2560 end if;
2561
2562 select decode(muec.exception_column, 'NUMBER1',NUMBER1,'NUMBER2',NUMBER2,
2563 'NUMBER3',NUMBER3,'NUMBER4',NUMBER4,'NUMBER5',NUMBER5,
2564 'NUMBER6',NUMBER6,'NUMBER7',NUMBER7)
2565 into l_temp
2566 from msc_user_exception_components muec,
2567 msc_x_exception_details mxed
2568 where muec.exception_id = mxed.exception_type
2569 and mxed.exception_detail_id = arg_exception_id
2570 and muec.component_type = 1
2571 and muec.ak_attribute_code = 'MSCX_QUANTITY';
2572 return l_temp;
2573 exception
2574 when no_data_found then
2575 return to_number(null);
2576 END MSCX_QUANTITY;
2577
2578 FUNCTION MSCX_COMP_RECEIPT_DATE (arg_exception_id IN NUMBER) return date is
2579 l_temp date;
2580 BEGIN
2581 if arg_exception_id is null OR
2582 arg_exception_id <1000 then
2583 return to_date(null);
2584 end if;
2585
2586 select decode(muec.exception_column, 'DATE1',date1,'DATE2',date2,'DATE3',date3,
2587 'DATE4',date4,'DATE5',date5,'DATE6',date6,'DATE7',date7)
2588 into l_temp
2589 from msc_user_exception_components muec,
2590 msc_x_exception_details mxed
2591 where muec.exception_id = mxed.exception_type
2592 and mxed.exception_detail_id = arg_exception_id
2593 and muec.component_type = 1
2594 and muec.ak_attribute_code = 'MSCX_COMP_RECEIPT_DATE';
2595 return l_temp;
2596 exception
2597 when no_data_found then
2598 return to_date(null);
2599 END MSCX_COMP_RECEIPT_DATE;
2600
2601 FUNCTION MSCX_COMP_REQUEST_DATE (arg_exception_id IN NUMBER) return date is
2602 l_temp date;
2603 BEGIN
2604 if arg_exception_id is null OR
2605 arg_exception_id <1000 then
2606 return to_date(null);
2607 end if;
2608
2609 select decode(muec.exception_column, 'DATE1',date1,'DATE2',date2,'DATE3',date3,
2610 'DATE4',date4,'DATE5',date5,'DATE6',date6,'DATE7',date7)
2611 into l_temp
2612 from msc_user_exception_components muec,
2613 msc_x_exception_details mxed
2614 where muec.exception_id = mxed.exception_type
2615 and muec.exception_id = arg_exception_id
2616 and mxed.exception_detail_id = arg_exception_id
2617 and muec.component_type = 1
2618 and muec.ak_attribute_code = 'MSCX_COMP_REQUEST_DATE';
2619 return l_temp;
2620 exception
2621 when no_data_found then
2622 return to_date(null);
2623 END MSCX_COMP_REQUEST_DATE;
2624
2625 FUNCTION cp_exception_type_text (arg_exception_type IN NUMBER) return varchar2 is
2626 l_text varchar2(300);
2627 begin
2628 if arg_exception_type is null then
2629 return null;
2630 end if;
2631
2632 if arg_exception_type < 999 then
2633 select meaning
2634 into l_text
2635 from mfg_lookups
2636 where lookup_type = 'MSC_X_EXCEPTION_TYPE'
2637 and lookup_code = arg_exception_type;
2638 else
2639 select EXCEPTION_TYPE exception_type_text
2640 into l_text
2641 from MSC_EXCEPTION_LOOKUP_V
2642 where to_number(EXCEPTION_TYPE_ID) = arg_exception_type;
2643 end if;
2644
2645 return l_text;
2646 exception
2647 when no_data_found then
2648 return null;
2649 end cp_exception_type_text;
2650
2651 FUNCTION resource_code_all(arg_resource_id IN NUMBER,
2652 arg_dept_id IN NUMBER,
2653 arg_org_id IN NUMBER,
2654 arg_plan_id IN NUMBER,
2658 BEGIN
2655 arg_instance_id IN NUMBER,
2656 arg_resource_type in number) return varchar2 IS
2657 v_resource_code varchar2(30);
2659 if arg_resource_id =-1 or
2660 arg_resource_id is null or
2661 arg_dept_id is null or
2662 arg_org_id is null or
2663 arg_plan_id is null or
2664 arg_instance_id is null then
2665 return null;
2666 elsif arg_resource_type = 100 then
2667 select resource_code
2668 into v_resource_code
2669 from msc_planned_resources_v
2670 where department_id = arg_dept_id
2671 and resource_id = arg_resource_id
2672 and plan_id = arg_plan_id
2673 and organization_id = arg_org_id
2674 and sr_instance_id = arg_instance_id
2675 and resource_type = resource_type;
2676 else
2677 v_resource_code := resource_code(arg_resource_id, arg_dept_id, arg_org_id, arg_plan_id, arg_instance_id);
2678 end if;
2679 return v_resource_code;
2680 exception
2681 when no_data_found then
2682 return null;
2683 END resource_code_all;
2684
2685 FUNCTION resource_desc_all(arg_resource_id IN NUMBER,
2686 arg_dept_id IN NUMBER,
2687 arg_org_id IN NUMBER,
2688 arg_plan_id IN NUMBER,
2689 arg_instance_id IN NUMBER,
2690 arg_resource_type in number) return varchar2 IS
2691 v_resource_desc varchar2(250);
2692 BEGIN
2693 if arg_resource_id =-1 or
2694 arg_resource_id is null or
2695 arg_dept_id is null or
2696 arg_org_id is null or
2697 arg_plan_id is null or
2698 arg_instance_id is null then
2699 return null;
2700 else
2701 select distinct resource_desc
2702 into v_resource_desc
2703 from msc_planned_resources_v
2704 where department_id = arg_dept_id
2705 and resource_id = arg_resource_id
2706 and plan_id = arg_plan_id
2707 and organization_id = arg_org_id
2708 and sr_instance_id = arg_instance_id
2709 and resource_type = resource_type;
2710 end if;
2711 return v_resource_desc;
2712 exception
2713 when no_data_found then
2714 return null;
2715 END resource_desc_all;
2716
2717 --Get the source_vendor_name in PWB ( added by Shailendra)
2718 FUNCTION source_supplier(arg_sr_instance_id IN NUMBER,arg_plan_id IN NUMBER,arg_supplier_id IN NUMBER,arg_source_supplier_id IN NUMBER,arg_source_org_id IN NUMBER,arg_order_type IN NUMBER) return varchar2 IS
2719 l_supp_id NUMBER;
2720 cursor c1(l_source_org_id IN NUMBER,l_sr_instance_id IN NUMBER) IS
2721 select tp.modeled_supplier_id
2722 from msc_trading_partners tp
2723 where tp.partner_type =3 and
2724 tp.sr_tp_id = l_source_org_id and
2725 tp.sr_instance_id = l_sr_instance_id;
2726 BEGIN
2727 if(arg_sr_instance_id is null OR arg_plan_id is null ) then
2728 return null;
2729 end if;
2730
2731 if(arg_plan_id = -1 OR (arg_order_type in (1,2,8) AND arg_source_org_id IS NULL)) then
2732 return supplier(arg_supplier_id);
2733 end if;
2734
2735 if(arg_source_org_id IS NOT NULL) then
2736 open c1(arg_source_org_id,arg_sr_instance_id);
2737 fetch c1 into l_supp_id;
2738 close c1;
2739
2740 else
2741 return supplier(arg_source_supplier_id);
2742 end if;
2743
2744 return supplier(l_supp_id);
2745 EXCEPTION
2746 WHEN OTHERS THEN
2747 return null;
2748 END source_supplier;
2749
2750
2751 --Get the source_vendor_site in PWB ( added by Shailendra)
2752 FUNCTION source_supplier_site(arg_sr_instance_id IN NUMBER,arg_plan_id IN NUMBER,arg_supplier_site_id IN NUMBER,arg_source_supplier_site_id IN NUMBER,arg_source_org_id IN NUMBER,arg_order_type IN NUMBER) return varchar2 IS
2753 l_supp_site_id NUMBER;
2754 cursor c1(l_source_org_id IN NUMBER,l_sr_instance_id IN NUMBER) IS
2755 select tp.modeled_supplier_site_id
2756 from msc_trading_partners tp
2757 where tp.partner_type =3 and
2758 tp.sr_tp_id = l_source_org_id and
2759 tp.sr_instance_id = l_sr_instance_id;
2760 BEGIN
2761 if(arg_sr_instance_id is null OR arg_plan_id is null ) then
2762 return null;
2763 end if;
2764
2765 if(arg_plan_id = -1 OR (arg_order_type in (1,2,8) AND arg_source_org_id IS NULL)) then
2766 return supplier_site(arg_supplier_site_id);
2767 end if;
2768
2769 if(arg_source_org_id IS NOT NULL) then
2770 open c1(arg_source_org_id,arg_sr_instance_id);
2771 fetch c1 into l_supp_site_id;
2772 close c1;
2773
2774 else
2775 return supplier_site(arg_source_supplier_site_id);
2776 end if;
2777
2778 return supplier_site(l_supp_site_id);
2779
2780 EXCEPTION
2781 WHEN OTHERS THEN
2782 return null;
2783 END source_supplier_site;
2784
2785
2786 FUNCTION category_name(arg_category_id IN number,
2787 arg_category_set_id IN NUMBER,
2788 arg_org_id IN NUMBER default null,
2789 arg_instance_id IN NUMBER default null) return varchar2 IS
2790
2791 category_name msc_item_categories.category_name%type;
2792
2793 CURSOR category_name_org IS
2794 SELECT category_name
2795 FROM msc_item_categories
2799 AND organization_id = arg_org_id
2796 WHERE category_set_id = arg_category_set_id
2797 AND sr_category_id = arg_category_id
2798 AND sr_instance_id = arg_instance_id
2800 AND description is NOT NULL
2801 AND rownum<2;
2802
2803 CURSOR category_name_ins IS
2804 SELECT category_name
2805 FROM msc_item_categories
2806 WHERE category_set_id = decode(arg_category_set_id, -1, category_set_id, arg_category_set_id)
2807 AND sr_category_id= arg_category_id
2808 AND sr_instance_id = arg_instance_id
2809 AND description is NOT NULL
2810 AND rownum<2;
2811
2812 CURSOR category_name_c IS
2813 SELECT category_name
2814 FROM msc_item_categories
2815 WHERE category_set_id = arg_category_set_id
2816 AND sr_category_id= arg_category_id
2817 AND description is NOT NULL
2818 AND rownum<2;
2819
2820 BEGIN
2821
2822 if ( arg_category_id is null ) or
2823 ( arg_category_set_id is null ) then
2824 return null;
2825 end if;
2826
2827 if (arg_category_set_id = -1 and arg_org_id = -1
2828 and arg_category_id <> -1 and arg_instance_id <> -1 ) then
2829 OPEN category_name_ins;
2830 FETCH category_name_ins INTO category_name;
2831 CLOSE category_name_ins;
2832 return category_name;
2833 end if;
2834
2835 IF arg_org_id is NOT NULL AND arg_instance_id is not NULL THEN
2836
2837 OPEN category_name_org;
2838 FETCH category_name_org INTO category_name;
2839 CLOSE category_name_org;
2840 return category_name;
2841
2842 ELSIF arg_org_id is NULL AND arg_instance_id is not NULL THEN
2843
2844 OPEN category_name_ins;
2845 FETCH category_name_ins INTO category_name;
2846 CLOSE category_name_ins;
2847 return category_name;
2848
2849 ELSIF arg_org_id is NULL AND arg_instance_id is NULL THEN
2850
2851 OPEN category_name_c;
2852 FETCH category_name_c INTO category_name;
2853 CLOSE category_name_c;
2854 return category_name;
2855
2856 ELSE
2857 return NULL;
2858 END IF;
2859
2860 exception when no_data_found then
2861 return null;
2862
2863 END category_name;
2864
2865
2866 function budget_name(arg_budget_id number) return varchar2 is
2867 budget_name varchar2(30);
2868
2869 cursor budget_name_c is
2870 select name
2871 from msc_inventory_budgets
2872 where budget_id=arg_budget_id;
2873
2874
2875 begin
2876 if arg_budget_id is null then
2877 return null;
2878 end if;
2879 open budget_name_c;
2880 fetch budget_name_c into budget_name;
2881 close budget_name_c;
2882
2883 return budget_name;
2884 exception when no_data_found then
2885 return null;
2886
2887 end budget_name;
2888
2889
2890 function drp_alloc_name(arg_rule_id number) return varchar2 is
2891
2892 rule_name varchar2(10);
2893
2894 cursor rule_name_c is
2895 select name
2896 from msc_drp_alloc_rules
2897 where rule_id=arg_rule_id;
2898
2899
2900 begin
2901 if arg_rule_id is null then
2902 return null;
2903 end if;
2904 open rule_name_c;
2905 fetch rule_name_c into rule_name;
2906 close rule_name_c;
2907
2908 return rule_name;
2909 exception when no_data_found then
2910 return null;
2911
2912 end drp_alloc_name;
2913
2914
2915
2916 function drp_pri_rule_name(arg_rule_id number) return varchar2 is
2917
2918 rule_name varchar2(30);
2919
2920 cursor rule_name_c is
2921 select meaning
2922 from msc_drp_dmd_pri_rules
2923 where rule_set_id=arg_rule_id;
2924
2925
2926 begin
2927 if arg_rule_id is null then
2928 return null;
2929 end if;
2930
2931 if arg_rule_id = -1 then
2932 rule_name := lookup_meaning('USER_DEFINED_DMD_PRIORITY',-1);
2933 else
2934 open rule_name_c;
2935 fetch rule_name_c into rule_name;
2936 close rule_name_c;
2937 end if;
2938 return rule_name;
2939 exception when no_data_found then
2940 return null;
2941
2942 end drp_pri_rule_name;
2943
2944
2945 function get_category_id (p_category_name in varchar2,
2946 p_org_id in number,
2947 p_inst_id in number) return number is
2948 cursor category_id_c is
2949 select sr_category_id
2950 from msc_item_categories
2951 where category_name = p_category_name
2952 and category_set_id = fnd_profile.value('MSR_BUDGET_CATEGORY_SET')
2953 and rownum =1 ;
2954
2955
2956 cursor category_id_c1 is
2957 select sr_category_id
2958 from msc_item_categories
2959 where category_name = p_category_name
2960 and category_set_id = fnd_profile.value('MSR_BUDGET_CATEGORY_SET')
2961 and organization_id=p_org_id
2962 and sr_instance_id=p_inst_id
2963 and rownum =1 ;
2964 l_category_id number;
2965
2966 begin
2967 if (p_org_id is not null and p_inst_id is not null
2968 and p_org_id <> -1 and p_inst_id <> -1) then
2969 open category_id_c1;
2973 open category_id_c;
2970 fetch category_id_c1 into l_category_id;
2971 close category_id_c1;
2972 else
2974 fetch category_id_c into l_category_id;
2975 close category_id_c;
2976 end if;
2977
2978 if l_category_id is null then
2979 l_category_id := -1;
2980 end if;
2981
2982 return l_category_id;
2983
2984 end;
2985
2986
2987 -- This function is to be used for calling from msc_orders_v in the
2988 -- union that gets records from msc_job_requirement_ops
2989 function get_order_number(p_inst_id in number,
2990 p_plan_id in number,
2991 p_transaction_id in number,
2992 p_coprod in number default 0) return varchar2 is
2993 cursor order_num_c is
2994 select wip_entity_name , order_number
2995 from msc_supplies
2996 where plan_id=p_plan_id
2997 and sr_instance_id = p_inst_id
2998 and transaction_id= p_transaction_id;
2999
3000 l_order_num varchar2(240);
3001 l_order_num1 varchar2(240);
3002
3003 begin
3004
3005 if (p_inst_id is null or p_plan_id is null or p_transaction_id is null) then
3006 return null;
3007 else
3008 open order_num_c;
3009 fetch order_num_c into l_order_num,l_order_num1;
3010 close order_num_c;
3011
3012 if p_coprod = 1 then
3013 return l_order_num1;
3014 else
3015 return l_order_num;
3016 end if;
3017
3018 end if;
3019 end get_order_number;
3020
3021 function get_trans_mode(p_ship_method_code in varchar2,
3022 p_instance_id in number ) return varchar2 is
3023 cursor trans_mode_c is
3024 select mode_of_transport
3025 from msc_carrier_services
3026 where ship_method_code = p_ship_method_code
3027 and sr_instance_id=p_instance_id;
3028 l_trans_mode varchar2(30);
3029 begin
3030 if p_ship_method_code is null then
3031 return null;
3032 end if;
3033
3034 open trans_mode_c;
3035 fetch trans_mode_c into l_trans_mode;
3036 close trans_mode_c;
3037
3038 return l_trans_mode;
3039
3040 end get_trans_mode;
3041
3042
3043
3044 FUNCTION lookup_meaning1(arg_lookup_type IN varchar2,
3045 arg_lookup_code IN varchar2,
3046 arg_application_id in number,
3047 arg_security_group_id in number) return varchar2 IS
3048 meaning_text varchar2(80);
3049 BEGIN
3050 if arg_lookup_code is null then
3051 return null;
3052 end if;
3053
3054 select meaning
3055 into meaning_text
3056 from fnd_lookup_values
3057 where lookup_type = arg_lookup_type
3058 and lookup_code = arg_lookup_code
3059 and view_application_id = arg_application_id
3060 and language = userenv('LANG')
3061 and security_group_id = arg_security_group_id;
3062
3063 return meaning_text;
3064
3065 EXCEPTION when no_data_found THEN
3066 return null;
3067 END lookup_meaning1;
3068
3069 function res_req_capacity(p_plan_id in number,
3070 p_transaction_id in number) return number is
3071 l_req_capacity number;
3072 begin
3073 if (p_plan_id is null or p_transaction_id is null) then
3074 return to_number(null);
3075 end if;
3076
3077 select (mif.unit_weight * mr.new_order_quantity)
3078 into l_req_capacity
3079 from msc_supplies mr,
3080 msc_system_items mif
3081 where mr.plan_id = mif.plan_id
3082 and mr.sr_instance_id = mif.sr_instance_id
3083 and mr.organization_id = mif.organization_id
3084 and mr.inventory_item_id = mif.inventory_item_id
3085 and mr.plan_id = p_plan_id
3086 and mr.transaction_id = p_transaction_id;
3087
3088 return l_req_capacity;
3089 EXCEPTION when others THEN
3090 return to_number(null);
3091 end res_req_capacity;
3092
3093 FUNCTION set_name(p_inst_id in number,
3094 p_so_line_id in number,
3095 p_set_type in number) return varchar2 IS
3096 CURSOR set_c is
3097 select decode(p_set_type, 1, ship_set_name, arrival_set_name)
3098 from msc_sales_orders
3099 where sr_instance_id = p_inst_id
3100 and demand_source_line = p_so_line_id;
3101 v_set_name varchar2(30);
3102 BEGIN
3103 if p_inst_id is null or p_so_line_id is null or p_set_type is null then
3104 return null;
3105 end if;
3106 open set_c;
3107 fetch set_c into v_set_name;
3108 close set_c;
3109 return v_set_name;
3110 EXCEPTION when others THEN
3111 return null;
3112 END set_name;
3113
3114 function get_bom_item_type(p_item_id in number) return number is
3115 cursor bom_item_c is
3116 select bom_item_type
3117 from msc_system_items
3118 where inventory_item_id = p_item_id
3119 and rownum < 2;
3120 l_bom_item_type number;
3121 begin
3122
3123 open bom_item_c;
3124 fetch bom_item_c into l_bom_item_type;
3125 close bom_item_c;
3126
3127 return l_bom_item_type;
3128 exception when others then
3129 return null;
3130 end get_bom_item_type;
3131
3132 FUNCTION get_other_customers(p_plan_id number,
3136 if (p_plan_id is null or p_schedule_desig_id is null) then
3133 p_schedule_desig_id number) return varchar2 is
3134 l_count number;
3135 begin
3137 return null;
3138 end if;
3139
3140 select count(*)
3141 into l_count
3142 from msd_dp_scn_output_levels_v dp,
3143 msc_plan_schedules ps
3144 where ps.input_schedule_id = dp.scenario_id
3145 and ps.designator_type = 7
3146 and dp.level_id in (11, 15, 41)
3147 and ps.plan_id = p_plan_id
3148 and ps.input_schedule_id = p_schedule_desig_id;
3149
3150 if l_count = 0 then
3151 return null;
3152 else
3153 return 'OTHER';
3154 end if;
3155
3156 exception when others then
3157 return null;
3158 end get_other_customers;
3159
3160 FUNCTION get_days_on_arrival(p_plan_id number,
3161 p_exception_id number,
3162 p_exception_type number,
3163 p_demand_id number,
3164 p_schedule_by number,
3165 p_late_early_flag number) return number is
3166 -- 1 schedule ship date
3167 -- 2 schedule arrival date
3168 -- 3 request ship date
3169 -- 4 request arival date
3170 -- 5 promise ship date
3171 -- 6 promise arrival date
3172
3173 -- 25 Early replenishment for sales order
3174 -- 27 Early replenishment for forecast
3175
3176 -- 24 Late replenishment for sales order
3177 -- 26 Late replenishment for forecast
3178 -- 69 Late Replenishment for MPP/MPS Demands
3179
3180 cursor c_dem (l_plan number, l_dem number) is
3181 select schedule_arrival_date, planned_arrival_date, promise_date, request_date
3182 ,using_assembly_demand_date, planned_ship_date, origination_type
3183 from msc_demands
3184 where plan_id = l_plan
3185 and demand_id = l_dem;
3186
3187 l_sched_arrival date;
3188 l_plnd_arrival date;
3189 l_promise date;
3190 l_request date;
3191
3192 l_new_due_date date;
3193 l_plnd_ship_date date;
3194 l_orig_type number;
3195
3196 retval number;
3197 BEGIN
3198 if (p_plan_id is null or p_exception_id is null
3199 or p_exception_type is null or p_demand_id is null
3200 or p_schedule_by is null or p_late_early_flag is null ) then
3201 return to_number(null);
3202 end if;
3203
3204 if (p_late_early_flag = 1) then -- late
3205 if (p_exception_type not in (24, 26, 69) ) then
3206 return to_number(null);
3207 end if;
3208 elsif (p_late_early_flag = -1) then --early
3209 if (p_exception_type not in (25, 27) ) then
3210 return to_number(null);
3211 end if;
3212 else
3213 return to_number(null);
3214 end if;
3215
3216 open c_dem(p_plan_id, p_demand_id);
3217 fetch c_dem into l_sched_arrival, l_plnd_arrival, l_promise, l_request,
3218 l_new_due_date, l_plnd_ship_date, l_orig_type;
3219 close c_dem;
3220
3221 if (l_orig_type = 29 and p_exception_id = -1) then
3222 return round(l_plnd_ship_date - l_new_due_date, 2) ;
3223 end if;
3224
3225 if (p_schedule_by in (1,2) ) then --by schedule dates
3226 retval := l_plnd_arrival - l_sched_arrival;
3227 elsif (p_schedule_by in (3,4) ) then --by request dates
3228 retval := l_plnd_arrival - l_request;
3229 elsif (p_schedule_by in (5,6) ) then --by promise dates
3230 retval := l_plnd_arrival - l_promise;
3231 else
3232 return to_number(null);
3233 end if;
3234
3235 if (p_late_early_flag = 1) then -- late
3236 if retval = 0 then
3237 return 0;
3238 elsif round(p_late_early_flag * retval, 2) = 0 then
3239 return 0.01;
3240 else
3241 return round(p_late_early_flag * retval, 2);
3242 end if;
3243 elsif (p_late_early_flag = -1) then --early
3244 if retval = 0 then
3245 return 0;
3246 elsif round(p_late_early_flag * retval, 2) = 0 then
3247 return 0.01;
3248 else
3249 return round(p_late_early_flag * retval, 2);
3250 end if;
3251 end if;
3252
3253 END get_days_on_arrival;
3254
3255 function get_cat_id (p_inventory_item_id number,
3256 p_organization_id number,
3257 p_instance_id number) return number is
3258 l_cat_set_id number;
3259 cursor cat_set_id_c is
3260 select sr_category_id
3261 from msc_item_categories
3262 where inventory_item_id=p_inventory_item_id
3263 and sr_instance_id=p_instance_id
3264 and organization_id=decode(p_organization_id, -1,organization_id, p_organization_id)
3265 and rownum < 2;
3266
3267 begin
3268
3269 open cat_set_id_c;
3270 fetch cat_set_id_c into l_cat_set_id;
3271 close cat_set_id_c;
3272
3273 return l_cat_set_id;
3274
3275 end get_cat_id;
3276
3277 function get_cat_set_id (p_inventory_item_id number,
3278 p_organization_id number,
3279 p_instance_id number) return number is
3280
3281 l_cat_id number;
3282 cursor cat_id_c is
3283 select category_set_id
3284 from msc_item_categories
3285 where inventory_item_id=p_inventory_item_id
3286 and sr_instance_id=p_instance_id
3290 begin
3287 and organization_id=decode(p_organization_id, -1, organization_id, p_organization_id)
3288 and rownum < 2;
3289
3291
3292 open cat_id_c;
3293 fetch cat_id_c into l_cat_id;
3294 close cat_id_c;
3295
3296 return l_cat_id;
3297 end get_cat_set_id;
3298
3299 FUNCTION resource_group_name(arg_resource_id IN NUMBER,
3300 arg_dept_id IN NUMBER,
3301 arg_org_id IN NUMBER,
3302 arg_plan_id IN NUMBER,
3303 arg_instance_id IN NUMBER) return varchar2 is
3304 l_group varchar2(30);
3305 begin
3306 if arg_resource_id =-1 or
3307 arg_resource_id is null or
3308 arg_dept_id is null or
3309 arg_org_id is null or
3310 arg_plan_id is null or
3311 arg_instance_id is null then
3312 return null;
3313 end if;
3314
3315 select resource_group_name
3316 into l_group
3317 from msc_department_resources
3318 where department_id = arg_dept_id
3319 and resource_id = arg_resource_id
3320 and plan_id = arg_plan_id
3321 and organization_id = arg_org_id
3322 and sr_instance_id = arg_instance_id;
3323
3324 return l_group;
3325 end resource_group_name;
3326
3327 FUNCTION new_schedule_date (arg_plan_id IN NUMBER,
3328 arg_trx_id IN NUMBER) return DATE is
3329 l_date date;
3330 begin
3331 IF arg_trx_id is null or arg_plan_id is null THEN
3332 return to_date(null);
3333 END IF;
3334
3335 select new_schedule_date
3336 into l_date
3337 from msc_supplies
3338 where transaction_id = arg_trx_id
3339 and plan_id = arg_plan_id;
3340
3341 return l_date;
3342
3343 end new_schedule_date;
3344
3345 FUNCTION Get_Zone_Name(p_zone_id IN NUMBER,
3346 p_sr_instance_id IN NUMBER )
3347 return varchar2 is
3348
3349 l_zone_name varchar2(255) ;
3350
3351 cursor cur_zone(l_zone_id number , l_sr_instance_id number) is
3352 select zone
3353 from msc_regions
3354 where region_id = l_zone_id
3355 and region_type = 10
3356 and sr_instance_id = l_sr_instance_id ;
3357
3358 begin
3359 If p_zone_id is null then
3360 l_zone_name := null ;
3361 else
3362 open cur_zone(p_zone_id , p_sr_instance_id) ;
3363 fetch cur_zone into l_zone_name ;
3364 close cur_zone ;
3365 end if;
3366 return l_zone_name ;
3367 End Get_Zone_Name;
3368
3369
3370 function alternate_bom_eff(p_process_seq_id number,
3371 p_plan_id number,
3372 p_sr_instance_id number) return varchar2 is
3373 CURSOR ALT_bom_C IS
3374 SELECT bom.alternate_bom_designator
3375 FROM msc_boms bom,
3376 msc_process_effectivity eff
3377 WHERE bom.plan_id = eff.plan_id
3378 AND bom.sr_instance_id = eff.sr_instance_id
3379 AND bom.bill_sequence_id = eff.bill_sequence_id
3380 AND eff.plan_id=p_plan_id
3381 and eff.sr_instance_id=p_sr_instance_id
3382 and eff.process_sequence_id=p_process_seq_id;
3383
3384 l_alt_bom VARCHAR2(40); --5338566 bugfix, length changed to 40
3385
3386 BEGIN
3387
3388 if p_process_seq_id is null then
3389 return null;
3390 end if;
3391
3392 OPEN ALT_bom_C;
3393 FETCH ALT_bom_C INTO l_alt_bom;
3394 CLOSE ALT_bom_C;
3395
3396 return(l_alt_bom);
3397
3398 end alternate_bom_eff;
3399
3400 function alternate_rtg_eff(p_process_seq_id number,
3401 p_plan_id number,
3402 p_sr_instance_id number) return varchar2 is
3403 CURSOR ALT_RTG_C IS
3404 SELECT rt.alternate_routing_designator
3405 FROM msc_routings rt,
3406 msc_process_effectivity eff
3407 WHERE rt.plan_id = eff.plan_id
3408 AND rt.sr_instance_id = eff.sr_instance_id
3409 AND rt.routing_sequence_id = eff.routing_sequence_id
3410 AND eff.plan_id=p_plan_id
3411 and eff.sr_instance_id=p_sr_instance_id
3412 and eff.process_sequence_id=p_process_seq_id;
3413
3414 l_alt_rtg VARCHAR2(40); --5338566 bugfix, length changed to 40
3415
3416 BEGIN
3417
3418 if p_process_seq_id is null then
3419 return null;
3420 end if;
3421
3422 OPEN ALT_RTG_C;
3423 FETCH ALT_RTG_C INTO l_alt_rtg;
3424 CLOSE ALT_RTG_C;
3425
3426 return(l_alt_rtg);
3427
3428 end alternate_rtg_eff;
3429
3430 function supply_order_number(p_order_type number,
3431 p_order_number varchar2,
3432 p_plan_id number ,
3433 p_sr_instance_id number,
3434 p_transaction_id number ,
3435 p_disposition_id number ) return varchar2
3436 is
3437
3438 cursor cur_sup_ord(l_order_type number,
3439 l_order_number varchar2,
3440 l_plan_id number ,
3441 l_sr_instance_id number,
3442 l_transaction_id number ,
3443 l_disposition_id number ) is
3444 select
3445 DECODE(l_order_type, 5,
3446 decode( l_order_number, NULL,
3447 to_char(l_transaction_id),
3448 l_order_number||' '||to_char(l_transaction_id)) ,
3452 l_plan_id, l_disposition_id, 1),1,240),
3449 14, decode(l_plan_id,
3450 -1, l_order_number,
3451 decode(substr(msc_get_name.get_order_number( l_sr_instance_id,
3453 null, to_char(l_disposition_id),
3454 substr(msc_get_name.get_order_number(l_sr_instance_id, l_plan_id,
3455 l_disposition_id, 1),1,240)||' ' || to_char(l_disposition_id))),
3456 17, decode(substr(msc_get_name.get_order_number(
3457 l_sr_instance_id,
3458 l_plan_id,
3459 l_disposition_id,
3460 1),1,240), null, to_char(l_disposition_id),
3461 substr(msc_get_name.get_order_number(
3462 l_sr_instance_id,
3463 l_plan_id,
3464 l_disposition_id,
3465 1),1,240)||' ' || to_char(l_disposition_id)),
3466 15,to_char(l_DISPOSITION_ID),
3467 16,to_char(l_DISPOSITION_ID),
3468 28,to_char(l_DISPOSITION_ID),
3469 l_order_number)
3470 from dual;
3471
3472 l_return varchar2(2000);
3473 begin
3474 if p_order_type in (51,52,76,77,78,79) then
3475 return to_char(p_transaction_id);
3476 end if;
3477 open cur_sup_ord(p_order_type , p_order_number , p_plan_id ,p_sr_instance_id , p_transaction_id ,p_disposition_id );
3478 fetch cur_sup_ord into l_return ;
3479 if cur_sup_ord%notfound then
3480 l_return := null;
3481 end if;
3482 close cur_sup_ord;
3483
3484 return l_return;
3485
3486 end supply_order_number;
3487
3488 FUNCTION operation_code(p_plan_id IN NUMBER,
3489 p_sr_instance_id IN NUMBER,
3490 p_standard_operation_id IN NUMBER) return varchar2 IS
3491
3492 v_operation_code varchar2(50);
3493
3494 CURSOR Operation_Code_C IS
3495 select operation_code
3496 from msc_std_op_resources
3497 where plan_id = p_plan_id
3498 and sr_instance_id = p_sr_instance_id
3499 and standard_operation_id = p_standard_operation_id;
3500
3501 BEGIN
3502 IF p_plan_id is null or
3503 p_sr_instance_id is null or
3504 p_standard_operation_id is null THEN
3505 return null;
3506 END IF;
3507 OPEN Operation_Code_C;
3508 FETCH Operation_Code_C INTO v_operation_code;
3509 CLOSE Operation_Code_C;
3510 return v_operation_code;
3511
3512 EXCEPTION when others THEN
3513 return null;
3514 END operation_code;
3515
3516
3517 FUNCTION setup_code(p_plan_id IN NUMBER,
3518 p_sr_instance_id IN NUMBER,
3519 p_resource_id IN NUMBER,
3520 p_organization_id IN NUMBER,
3521 p_setup_id IN NUMBER
3522 ) return varchar2 IS
3523 v_setup_code varchar2(50);
3524
3525
3526 CURSOR Setup_Code_C IS
3527 select setup_code
3528 from msc_resource_setups
3529 where
3530 plan_id=p_plan_id
3531 AND sr_instance_id=p_sr_instance_id
3532 AND resource_id = p_resource_id
3533 AND organization_id = p_organization_id
3534 AND setup_id = p_setup_id;
3535
3536 BEGIN
3537 IF p_plan_id is null or
3538 p_sr_instance_id is null or
3539 p_organization_id is null or
3540 p_setup_id is null THEN
3541 return null;
3542 END IF;
3543 OPEN Setup_Code_C;
3544 FETCH Setup_Code_C INTO v_setup_code;
3545 CLOSE Setup_Code_C;
3546 return v_setup_code;
3547
3548 EXCEPTION when others THEN
3549 return null;
3550 END setup_code;
3551
3552
3553 function get_mfd_order_number(p_order_type in number,
3554 p_order_number in varchar2,
3555 p_transaction_id in number,
3556 p_sr_instance_id in number,
3557 p_plan_id in number,
3558 p_disposition_id in number)
3559 return varchar2 is
3560
3561 l_order_num varchar2(240);
3562 l_order_num_temp varchar2(240);
3563
3564 begin
3565
3566 if ( p_sr_instance_id is null
3567 or p_plan_id is null
3568 or p_transaction_id is null) then
3569 l_order_num:= null;
3570 else
3571 IF p_order_type = 5 THEN
3572 IF p_order_number IS NULL THEN
3573 l_order_num := to_char(p_transaction_id);
3574 else
3575 l_order_num := p_order_number||' '||to_char(p_transaction_id);
3576 end if;
3577 Elsif p_order_type in (14, 17) THEN
3578 l_order_num_temp:= substr(msc_get_name.get_order_number
3579 (p_sr_instance_id,
3580 p_plan_id,
3581 p_disposition_id, 1), 1, 240);
3582 if l_order_num_temp is null then
3583 l_order_num:= to_char(p_disposition_id);
3584 else
3585 l_order_num:= l_order_num_temp||' '||to_char(p_disposition_id);
3586 end if;
3587 Elsif p_order_type in (51, 52) THEN
3588 l_order_num := to_char(p_transaction_id);
3589 Elsif p_order_type in (15, 16, 28) THEN
3590 l_order_num:= to_char(p_disposition_id);
3594 end if;
3591 else
3592 l_order_num:= p_order_number;
3593 end if;
3595
3596 return l_order_num;
3597
3598 exception
3599 when others then
3600 return null;
3601 end get_mfd_order_number;
3602
3603 /****************************************************************************************/
3604
3605 -- -----------------------------------------------------
3606 -- This function returns
3607 -- Resource_transaction_id,resource_id Or Resource_code :
3608 -- if p_column_name is 'TI'
3609 -- > Resource_transaction_id
3610 -- elsif p_column_name is 'RI'
3611 -- > resource_id
3612 -- elsif p_column_name is 'RC'
3613 -- > Resource_code
3614 -- elsif p_column_name is 'DI'
3615 -- > Department_id
3616 -- elsif p_column_name is 'DC'
3617 -- > Department_code
3618 -- ------------------------------------------------------
3619
3620 function get_res_and_dept_details(p_plan_id in number,
3621 p_sr_instance_id in number,
3622 P_res_transaction_id in number,
3623 P_column_name in varchar2)
3624 return varchar2 is
3625
3626 Cursor Cur_Resource_Details Is
3627 Select Mrr.Transaction_Id,
3628 Mrr.Resource_Id,
3629 Mdr.Resource_Code,
3630 Mrr.Department_Id,
3631 Mdr.Department_Code
3632 From Msc_Department_Resources Mdr,
3633 Msc_Resource_Requirements Mrr
3634 Where Mrr.Transaction_Id = P_Res_Transaction_Id
3635 And Mrr.Plan_Id = P_Plan_Id
3636 And Mrr.Sr_Instance_Id = P_Sr_Instance_Id
3637 And Mdr.Plan_Id = Mrr.Plan_Id
3638 And Mdr.Sr_Instance_Id = Mrr.Sr_Instance_Id
3639 And Mdr.Organization_Id = Mrr.Organization_Id
3640 And Mdr.Department_Id = Mrr.Department_Id
3641 And Mdr.Resource_Id = Mrr.Resource_Id;
3642
3643 rec_Resource_Details Cur_Resource_Details%rowtype;
3644
3645 begin
3646
3647 if (P_res_transaction_id is null
3648 or p_column_name is null) then
3649 return null;
3650 else
3651 open cur_resource_details;
3652 fetch cur_resource_details
3653 into rec_Resource_Details;
3654 if cur_resource_details%notfound then
3655 close cur_resource_details;
3656 return null;
3657 end if;
3658 close cur_resource_details;
3659 end if;
3660 if(p_column_name = 'TI') then
3661 return rec_Resource_Details.Transaction_Id;
3662 elsif(p_column_name = 'RI') then
3663 return rec_Resource_Details.Resource_id;
3664 elsif(p_column_name = 'RC') then
3665 return rec_Resource_Details.resource_code;
3666 elsif(p_column_name = 'DI') then
3667 return rec_Resource_Details.department_id;
3668 elsif(p_column_name = 'DC') then
3669 return rec_Resource_Details.department_code;
3670 else
3671 return null;
3672 end if;
3673 exception
3674 when others then
3675 if cur_resource_details%isopen then
3676 close cur_resource_details;
3677 end if;
3678 return null;
3679 end get_res_and_dept_details;
3680
3681 -- -----------------------------------------------------
3682 -- This function returns
3683 -- Minimum_transfer_qty,Minimum_time_offset,Maximum_time_offset:
3684 -- if p_column_name is 'DTYPE'
3685 -- > Dependency_Type
3686 -- elsif p_column_name is 'MTQTY'
3687 -- > Minimum_transfer_qty
3688 -- elsif p_column_name is 'MINTT'
3689 -- > Minimum_time_offset
3690 -- elsif p_column_name is 'MAXTT'
3691 -- > Maximum_time_offset
3692 -- ------------------------------------------------------
3693
3694 function get_mtq_details(p_plan_id in number,
3695 p_sr_instance_id in number,
3696 p_routing_seq_id in number,
3697 p_operation_seq_id in number,
3698 p_item_id in number,
3699 p_cfm_routing_flag in number,
3700 p_column_name in varchar2)
3701 return number is
3702
3703 cursor cur_mtq_details_mon is
3704 select dependency_type,
3705 minimum_transfer_qty,
3706 minimum_time_offset,
3707 maximum_time_offset
3708 from msc_operation_networks
3709 where plan_id = p_plan_id and
3710 sr_instance_id = p_sr_instance_id and
3711 routing_sequence_id = p_routing_seq_id and
3712 from_op_seq_id = p_operation_seq_id and
3713 from_item_id = p_item_id;
3714
3715 cursor cur_mtq_details_mro is
3716 select minimum_transfer_quantity
3717 from msc_routing_operations
3718 where plan_id = p_plan_id and
3719 sr_instance_id = p_sr_instance_id and
3720 routing_sequence_id = p_routing_seq_id and
3721 operation_sequence_id = p_operation_seq_id;
3722
3723 l_dependency_type number(10);
3727
3724 l_minimum_transfer_qty number(10);
3725 l_minimum_time_offset number(10);
3726 l_maximum_time_offset number(10);
3728 rec_mtq_details_mon cur_mtq_details_mon%rowtype;
3729 rec_mtq_details_mro cur_mtq_details_mro%rowtype;
3730
3731 begin
3732 if p_cfm_routing_flag is not null and
3733 p_cfm_routing_flag = 3 then
3734
3735 if (p_sr_instance_id is null
3736 or p_plan_id is null
3737 or P_Routing_Seq_Id is null
3738 or P_Operation_Seq_Id is null
3739 or p_item_id is null
3740 or p_column_name is null) then
3741 return null;
3742 else
3743 open cur_mtq_details_mon ;
3744 fetch cur_mtq_details_mon into rec_mtq_details_mon;
3745 if cur_mtq_details_mon%notfound then
3746 close cur_mtq_details_mon;
3747 --If the details are not available in msc_operation_networks then
3748 --get it from msc_routing_operations.
3749 open cur_mtq_details_mro;
3750 fetch cur_mtq_details_mro into rec_mtq_details_mro;
3751 if cur_mtq_details_mro%notfound then
3752 close cur_mtq_details_mro;
3753 return null;
3754 end if;
3755 close cur_mtq_details_mro;
3756 if(p_column_name = 'MTQTY') then
3757 return rec_mtq_details_mro.minimum_transfer_quantity;
3758 else
3759 return null;
3760 end if;
3761 end if;
3762 close cur_mtq_details_mon;
3763 end if;
3764
3765 if(p_column_name = 'DTYPE') then
3766 return rec_mtq_details_mon.dependency_type;
3767 elsif(p_column_name = 'MTQTY') then
3768 return rec_mtq_details_mon.minimum_transfer_qty;
3769 elsif(p_column_name = 'MINTT') then
3770 return rec_mtq_details_mon.minimum_time_offset;
3771 elsif(p_column_name = 'MAXTT') then
3772 return rec_mtq_details_mon.maximum_time_offset;
3773 else
3774 return null;
3775 end if;
3776 else
3777 --Always get it from msc_routing_operations table.
3778 open cur_mtq_details_mro;
3779 fetch cur_mtq_details_mro into rec_mtq_details_mro;
3780 if cur_mtq_details_mro%notfound then
3781 close cur_mtq_details_mro;
3782 return null;
3783 end if;
3784 close cur_mtq_details_mro;
3785 if(p_column_name = 'MTQTY') then
3786 return rec_mtq_details_mro.minimum_transfer_quantity;
3787 else
3788 return null;
3789 end if;
3790 end if;
3791 exception
3792 when others then
3793 if cur_mtq_details_mon%isopen then
3794 close cur_mtq_details_mon;
3795 end if;
3796 if cur_mtq_details_mro%isopen then
3797 close cur_mtq_details_mro;
3798 end if;
3799 return null;
3800 end get_mtq_details;
3801
3802 function eam_parent_work_order( p_plan_id number ,
3803 p_sr_instance_id number,
3804 p_transaction_id number
3805 ) return number
3806 is
3807 l_eam_parent_order_number number;
3808
3809 cursor cur_parent_order is
3810 select to_transaction_id
3811 from msc_job_operation_networks
3812 where plan_id = p_plan_id
3813 and sr_instance_id = p_sr_instance_id
3814 and transaction_id = p_transaction_id
3815 and dependency_type = 3 ;
3816 Begin
3817 open cur_parent_order ;
3818 fetch cur_parent_order into l_eam_parent_order_number;
3819 if cur_parent_order%notfound then
3820 null;
3821 end if;
3822 close cur_parent_order;
3823
3824 return l_eam_parent_order_number;
3825
3826 End eam_parent_work_order;
3827
3828 FUNCTION standard_operation_code(p_plan_id IN NUMBER,
3829 p_sr_instance_id IN NUMBER,
3830 p_resource_id IN NUMBER,
3831 p_org_id IN NUMBER,
3832 p_from_setup_id IN NUMBER,
3833 p_to_setup_id IN NUMBER
3834 ) return varchar2 IS
3835 v_operation_code varchar2(50);
3836
3837 CURSOR Operation_Code_C IS
3838 select res.operation_code
3839 from msc_std_op_resources res, msc_setup_transitions mst
3840 where res.plan_id = p_plan_id
3841 and res.sr_instance_id = p_sr_instance_id
3842 and mst.resource_id=p_resource_id
3843 and mst.organization_id=p_org_id
3844 and mst.from_setup_id=p_from_setup_id
3845 and mst.to_setup_id=p_to_setup_id
3846 and res.plan_id=mst.plan_id
3847 and res.sr_instance_id=mst.sr_instance_id
3848 and res.organization_id=mst.organization_id
3849 and res.standard_operation_id =mst.standard_operation_id;
3850
3851 BEGIN
3852 IF p_plan_id is null or
3853 p_sr_instance_id is null or
3854 p_resource_id is null or
3855 p_org_id is null or
3856 p_from_setup_id is null or
3857 p_to_setup_id is null or
3858 p_plan_id < 0 THEN
3859 return null;
3860 END IF;
3861 OPEN Operation_Code_C;
3862 FETCH Operation_Code_C INTO v_operation_code;
3863 CLOSE Operation_Code_C;
3864 return v_operation_code;
3865
3866 EXCEPTION when others THEN
3867 return null;
3868 END standard_operation_code;
3869
3870 function get_op_seq_id(p_plan_id in number,
3871 p_sr_instance_id in number,
3872 p_routing_seq_id in number,
3876 cursor cur_routing_op_seq_num is
3873 p_op_seq_num in number)
3874 return number is
3875
3877 select operation_sequence_id
3878 from msc_routing_operations
3879 where plan_id = p_plan_id
3880 and sr_instance_id = p_sr_instance_id
3881 and routing_sequence_id = p_routing_seq_id
3882 and operation_seq_num = p_op_seq_num;
3883
3884 l_op_seq_id number(10);
3885
3886 begin
3887 open cur_routing_op_seq_num ;
3888 fetch cur_routing_op_seq_num
3889 into l_op_seq_id;
3890 if cur_routing_op_seq_num%notfound then
3891 close cur_routing_op_seq_num ;
3892 return null;
3893 end if;
3894 close cur_routing_op_seq_num;
3895 return l_op_seq_id;
3896 exception
3897 when others then
3898 if cur_routing_op_seq_num%isopen then
3899 close cur_routing_op_seq_num;
3900 end if;
3901 return null;
3902 end get_op_seq_id;
3903
3904 function get_mtq_coprod_details(p_plan_id in number,
3905 p_sr_instance_id in number,
3906 p_routing_seq_id in number,
3907 p_operation_seq_id in number,
3908 p_item_id in number,
3909 p_column_name in varchar2)
3910 return number is
3911
3912 cursor cur_mtq_details is
3913 select dependency_type,
3914 minimum_transfer_qty,
3915 minimum_time_offset,
3916 maximum_time_offset
3917 from msc_operation_networks
3918 where plan_id = p_plan_id and
3919 sr_instance_id = p_sr_instance_id and
3920 routing_sequence_id = p_routing_seq_id and
3921 from_op_seq_id = p_operation_seq_id and
3922 from_item_id = p_item_id;
3923
3924 l_dependency_type number(10);
3925 l_minimum_transfer_qty number(10);
3926 l_minimum_time_offset number(10);
3927 l_maximum_time_offset number(10);
3928
3929 rec_mtq_details cur_mtq_details%rowtype;
3930
3931 begin
3932
3933 if (p_sr_instance_id is null
3934 or p_plan_id is null
3935 or P_Routing_Seq_Id is null
3936 or P_Operation_Seq_Id is null
3937 or p_item_id is null
3938 or p_column_name is null) then
3939 return null;
3940 else
3941 open cur_mtq_details ;
3942 fetch cur_mtq_details
3943 into rec_mtq_details;
3944 if cur_mtq_details%notfound then
3945 close cur_mtq_details ;
3946 return null;
3947 end if;
3948 close cur_mtq_details;
3949 end if;
3950
3951 if(p_column_name = 'DTYPE') then
3952 return rec_mtq_details.dependency_type;
3953 elsif(p_column_name = 'MTQTY') then
3954 return rec_mtq_details.minimum_transfer_qty;
3955 elsif(p_column_name = 'MINTT') then
3956 return rec_mtq_details.minimum_time_offset;
3957 elsif(p_column_name = 'MAXTT') then
3958 return rec_mtq_details.maximum_time_offset;
3959 else
3960 return null;
3961 end if;
3962 exception
3963 when others then
3964 if cur_mtq_details%isopen then
3965 close cur_mtq_details;
3966 end if;
3967 return null;
3968 end get_mtq_coprod_details;
3969
3970 function get_mfd_details(p_plan_id number,
3971 p_trans_id number,
3972 p_inst_id number,
3973 p_routing_seq_id number,
3974 p_op_seq_id number,
3975 p_item_id number,
3976 c_trans_id number,
3977 c_inst_id number,
3978 c_op_seq_id number,
3979 p_column_name varchar2) return number is
3980
3981 cursor c_intra_rtg is
3982 select distinct dependency_type,
3983 minimum_transfer_qty,
3984 minimum_time_offset,
3985 maximum_time_offset
3986 from msc_operation_networks
3987 where plan_id = p_plan_id
3988 and sr_instance_id = p_inst_id
3989 and routing_sequence_id = p_routing_seq_id
3990 and transition_type = 1
3991 and nvl(from_op_seq_id, -23453) = nvl(p_op_seq_id, -23453)
3992 and nvl(to_op_seq_id, -23453) = nvl(c_op_seq_id, -23453);
3993
3994
3995 cursor c_inter_rtg is
3996 select dependency_type,
3997 minimum_transfer_qty,
3998 minimum_time_offset,
3999 maximum_time_offset
4000 from msc_job_operation_networks
4001 where plan_id = p_plan_id
4002 and sr_instance_id = p_inst_id
4003 and transaction_id = p_trans_id
4004 and to_transaction_id = c_trans_id
4005 and transition_type = 1
4006 and from_item_id = p_item_id
4007 and nvl(from_op_seq_id, -23453) = nvl(p_op_seq_id, -23453)
4008 and nvl(to_op_seq_id, -23453) = nvl(c_op_seq_id, -23453);
4009
4010 rec_intra_rtg_details c_intra_rtg%rowtype;
4014 if ( p_inst_id = c_inst_id and p_trans_id = c_trans_id ) then
4011 rec_inter_rtg_details c_inter_rtg%rowtype;
4012
4013 begin
4015 --intra-routing
4016 open c_intra_rtg;
4017 fetch c_intra_rtg into rec_intra_rtg_details;
4018 close c_intra_rtg;
4019
4020 if(p_column_name = 'DTYPE') then
4021 return rec_intra_rtg_details.dependency_type;
4022 elsif(p_column_name = 'MTQTY') then
4023 return rec_intra_rtg_details.minimum_transfer_qty;
4024 elsif(p_column_name = 'MINTT') then
4025 return rec_intra_rtg_details.minimum_time_offset;
4026 elsif(p_column_name = 'MAXTT') then
4027 return rec_intra_rtg_details.maximum_time_offset;
4028 else
4029 return null;
4030 end if;
4031
4032 else
4033 --inter-routing
4034 open c_inter_rtg;
4035 fetch c_inter_rtg into rec_inter_rtg_details;
4036 close c_inter_rtg;
4037
4038 if(p_column_name = 'DTYPE') then
4039 return rec_inter_rtg_details.dependency_type;
4040 elsif(p_column_name = 'MTQTY') then
4041 return rec_inter_rtg_details.minimum_transfer_qty;
4042 elsif(p_column_name = 'MINTT') then
4043 return rec_inter_rtg_details.minimum_time_offset;
4044 elsif(p_column_name = 'MAXTT') then
4045 return rec_inter_rtg_details.maximum_time_offset;
4046 else
4047 return null;
4048 end if;
4049 end if;
4050 exception
4051 when others then
4052 return null;
4053 end get_mfd_details;
4054
4055 function get_supply_order_number(p_plan_id number,
4056 p_inst_id number,
4057 p_trans_id number)
4058 return varchar2
4059 is
4060 l_order_number varchar2(2000);
4061 l_order_type number;
4062 l_disposition_id number;
4063 l_supply_order_number varchar2(2000);
4064
4065 cursor curr_supply(p_curr_plan_id number , p_curr_inst_id number , p_curr_trans_id number) is
4066 select order_number , order_type , disposition_id
4067 from msc_supplies
4068 where plan_id = p_curr_plan_id
4069 and sr_instance_id = p_curr_inst_id
4070 and transaction_id = p_curr_trans_id;
4071 begin
4072 open curr_supply(p_plan_id,p_inst_id ,p_trans_id) ;
4073 fetch curr_supply into l_order_number ,l_order_type ,l_disposition_id;
4074 close curr_supply;
4075
4076 l_supply_order_number := msc_get_name.supply_order_number(l_order_type,
4077 l_order_number,
4078 p_plan_id ,
4079 p_inst_id,
4080 p_trans_id ,
4081 l_disposition_id);
4082 return l_supply_order_number ;
4083
4084 end get_supply_order_number;
4085
4086 function get_supply_item(p_plan_id number,
4087 p_inst_id number,
4088 p_trans_id number)
4089 return varchar2
4090 is
4091 l_inventory_item_id number;
4092 l_organization_id number;
4093 l_supply_item varchar2(2000);
4094
4095 cursor curr_supply(p_curr_plan_id number , p_curr_inst_id number , p_curr_trans_id number) is
4096 select inventory_item_id , organization_id
4097 from msc_supplies
4098 where plan_id = p_curr_plan_id
4099 and sr_instance_id = p_curr_inst_id
4100 and transaction_id = p_curr_trans_id;
4101 begin
4102 open curr_supply(p_plan_id,p_inst_id ,p_trans_id) ;
4103 fetch curr_supply into l_inventory_item_id ,l_organization_id;
4104 close curr_supply;
4105
4106 l_supply_item := msc_get_name.item_name(l_inventory_item_id,
4107 l_organization_id,
4108 p_trans_id,
4109 p_inst_id);
4110 return l_supply_item ;
4111
4112 end get_supply_item;
4113
4114 function get_supply_org_code(p_plan_id number,
4115 p_inst_id number,
4116 p_trans_id number)
4117 return varchar2
4118 is
4119 l_supply_org_code varchar2(100);
4120
4121 cursor curr_supply(p_curr_plan_id number , p_curr_inst_id number , p_curr_trans_id number) is
4122 select msc_get_name.org_code(organization_id , sr_instance_id )
4123 from msc_supplies
4124 where plan_id = p_curr_plan_id
4125 and sr_instance_id = p_curr_inst_id
4126 and transaction_id = p_curr_trans_id;
4127
4128 begin
4129
4130 open curr_supply(p_plan_id,p_inst_id ,p_trans_id) ;
4131 fetch curr_supply into l_supply_org_code;
4132 close curr_supply;
4133
4134 return l_supply_org_code;
4135 end get_supply_org_code;
4136
4137 function get_min_max_offset_time(p_plan_id number,
4138 p_inst_id number,
4139 p_from_trans_id number,
4140 p_to_trans_id number,
4141 p_from_op_seq_num number,
4142 p_to_op_seq_num number ,
4143 p_from_res_seq_num number,
4144 p_to_res_seq_num number ,
4145 p_min_max_flag varchar2)
4146 return number
4147 is
4148 l_minimum_offset_time number;
4149 l_maximum_offset_time number;
4150 l_offset_time number;
4151 l_from_routing_seq_id number;
4152 l_to_routing_seq_id number;
4153 l_from_operation_seq_id number;
4154 l_to_operation_seq_id number;
4155 l_from_inventory_item_id number;
4156 l_to_inventory_item_id number;
4157 l_column_name varchar2(255);
4158
4159 cursor curr_supply(p_curr_plan_id number , p_curr_inst_id number
4163 where ms.transaction_id = mrr.supply_id
4160 , p_curr_trans_id number ,p_operation_seq_num number ,p_resource_seq_num number) is
4161 select mrr.operation_sequence_id , ms.routing_sequence_id , ms.inventory_item_id
4162 from msc_supplies ms ,msc_resource_requirements mrr
4164 and ms.plan_id = mrr.plan_id
4165 and mrr.operation_seq_num = p_operation_seq_num
4166 and mrr.resource_seq_num = p_resource_seq_num
4167 and mrr.operation_seq_num is not null
4168 and ms.plan_id = p_curr_plan_id
4169 and ms.sr_instance_id = p_curr_inst_id
4170 and ms.transaction_id = p_curr_trans_id;
4171
4172 begin
4173 open curr_supply(p_plan_id ,p_inst_id ,p_from_trans_id ,p_from_op_seq_num ,p_from_res_seq_num);
4174 fetch curr_supply into l_from_operation_seq_id ,l_from_routing_seq_id ,l_from_inventory_item_id;
4175 close curr_supply;
4176
4177 open curr_supply(p_plan_id ,p_inst_id ,p_to_trans_id ,p_to_op_seq_num ,p_to_res_seq_num);
4178 fetch curr_supply into l_to_operation_seq_id ,l_to_routing_seq_id ,l_to_inventory_item_id;
4179 close curr_supply;
4180
4181 if p_min_max_flag = 'MINIMUM' then
4182 l_column_name := 'MINTT' ;
4183 elsif p_min_max_flag = 'MAXIMUM' then
4184 l_column_name := 'MAXTT' ;
4185 end if;
4186
4187 l_offset_time := get_mfd_details(p_plan_id,
4188 p_from_trans_id,
4189 p_inst_id,
4190 l_from_routing_seq_id,
4191 l_from_operation_seq_id,
4192 l_from_inventory_item_id,
4193 p_to_trans_id,
4194 p_inst_id,
4195 l_to_operation_seq_id,
4196 l_column_name);
4197 return l_offset_time;
4198
4199 end get_min_max_offset_time;
4200
4201 function get_load_ratio_diff_threshold(p_plan_id number,
4202 p_sr_instance_id number,
4203 p_organization_id number,
4204 p_department_id number,
4205 p_resource_id number)
4206 return number
4207 is
4208 l_return number;
4209
4210 cursor cur(l_plan_id number ,l_sr_instance_id number ,l_organization_id number
4211 ,l_department_id number ,l_resource_id number) is
4212 select utilization_change_percent
4213 from msc_department_resources
4214 where plan_id = l_plan_id
4215 and sr_instance_id = l_sr_instance_id
4216 and organization_id = l_organization_id
4217 and department_id = l_department_id
4218 and resource_id = l_resource_id ;
4219
4220 begin
4221 open cur(p_plan_id ,p_sr_instance_id ,p_organization_id
4222 ,p_department_id ,p_resource_id );
4223 fetch cur into l_return;
4224 close cur;
4225
4226 return l_return;
4227 end get_load_ratio_diff_threshold;
4228
4229 function get_ship_to_consumption_level(p_demand_plan_id number,
4230 p_scenario_id number
4231 )
4232 return number
4233 is
4234 l_level number;
4235 l_ship_to_code number ;
4236
4237 cursor cur_ship_to(l_demand_plan_id number ,l_scenario_id number) is
4238 select decode(level_id ,1,6
4239 ,11,7
4240 ,12,5
4241 ,15,4
4242 ,34,10
4243 ,41,9
4244 ,42,8,1)
4245 from msd_dp_scn_output_levels_v
4246 where demand_plan_id = l_demand_plan_id
4247 and scenario_id = l_scenario_id
4248 and level_id in(1,11,12,15,34,41,42);
4249 begin
4250 /*----------------------------------------------------------------------
4251 Mapping of demand scenarios with ship to consumption level is
4252 Ship_to Ship_to_code Level_id
4253 Ship 2 7
4254 Bill 3 7
4255 Customer 4 15
4256 Region 5 12
4257 Item 6 1
4258 Customer Site 7 11
4259 Zone 8 42
4260 Customer Zone 9 41
4261 Demand Class 10 34
4262 ------------------------------------------------------------------------*/
4263 open cur_ship_to(p_demand_plan_id ,p_scenario_id );
4264 fetch cur_ship_to into l_ship_to_code ;
4265 close cur_ship_to;
4266 return l_ship_to_code;
4267 end get_ship_to_consumption_level;
4268
4269
4270 function GET_preference(p_key varchar2,
4271 p_pref_id number,
4272 p_plan_type number) return varchar2 is
4273
4274 cursor get_pref_c is
4275 select key, value
4276 from msc_user_preference_values
4277 where key= p_key
4278 and preference_id = p_pref_id;
4279
4280 l_key varchar2(80);
4281 l_plan_type number;
4282
4283 cursor pref_def_c is
4284 select preference_key,default_value
4285 from msc_user_preference_keys
4286 where preference_key= p_key
4287 and plan_type = l_plan_type;
4288
4289 l_value varchar2(2000);
4290
4291
4292 cursor c_default_cat is
4293 select to_char(category_set_id)
4294 from msc_category_sets
4295 where default_flag = 1;
4296
4297 begin
4298 if p_pref_id is not null then
4299 open get_pref_c;
4300 fetch get_pref_c into l_key, l_value;
4301 CLOSE get_pref_c;
4302 end if;
4303
4304 if l_key is null or -- pref_id exists but no pref_key
4305 p_pref_id is null then
4306
4307 -- get default value
4308 l_plan_type := p_plan_type;
4309
4310 if p_plan_type not in (4,5,8) then
4311 l_plan_type := 1;
4312 end if;
4316 fetch c_default_cat into l_value;
4313
4314 if p_key = 'CATEGORY_SET_ID' then
4315 open c_default_cat;
4317 close c_default_cat;
4318 if l_value is null then
4319 l_value := fnd_profile.value('MSC_SRA_CATEGORY_SET');
4320 end if;
4321 else
4322 l_key := null;
4323 open pref_def_c;
4324 fetch pref_def_c into l_key,l_value;
4325 CLOSE pref_def_c;
4326 if l_key is null then
4327 -- find the common one from plan_type 1
4328 l_plan_type := 1;
4329 open pref_def_c;
4330 fetch pref_def_c into l_key,l_value;
4331 CLOSE pref_def_c;
4332 end if;
4333 end if;
4334 end if;
4335 return trim(l_value);
4336 end GET_preference;
4337
4338 FUNCTION lookup_fnd(arg_lookup_type IN varchar2, arg_lookup_code IN varchar2) return varchar2 IS
4339 meaning_text varchar2(80);
4340 BEGIN
4341 if arg_lookup_code is null or arg_lookup_code is null then
4342 return null;
4343 end if;
4344
4345 select meaning
4346 into meaning_text
4347 from fnd_lookups
4348 where lookup_type = arg_lookup_type
4349 and lookup_code = arg_lookup_code;
4350
4351 return meaning_text;
4352
4353 EXCEPTION when no_data_found THEN
4354 return null;
4355 END lookup_fnd;
4356
4357 function get_default_pref_id(p_user_id number) return number is
4358 cursor get_default_pref_id_c(p_user_id number) is
4359 select preference_id
4360 from msc_user_preferences
4361 where default_flag =1
4362 and user_id = p_user_id;
4363 l_def_pref_id number;
4364 begin
4365
4366 open get_default_pref_id_c(p_user_id);
4367 fetch get_default_pref_id_c into l_def_pref_id;
4368 if get_default_pref_id_c%notfound then
4369 return to_number(null);
4370 end if;
4371 return l_def_pref_id;
4372 close get_default_pref_id_c;
4373 end get_default_pref_id;
4374
4375 FUNCTION get_std_op_code(p_plan_id number,
4376 p_sr_instance_id number,
4377 p_routing_sequence_id number,
4378 p_op_seq_id number) return varchar2 is
4379 cursor get_std_op_code_c(p_plan_id number,
4380 p_sr_instance_id number,
4381 p_routing_sequence_id number,
4382 p_op_seq_id number) is
4383 select standard_operation_code
4384 from msc_routing_operations
4385 where plan_id = p_plan_id
4386 and sr_instance_id = p_sr_instance_id
4387 and routing_sequence_id = p_routing_sequence_id
4388 and operation_sequence_id = p_op_seq_id;
4389
4390 l_std_op_code varchar2(4);
4391 begin
4392 open get_std_op_code_c(p_plan_id,p_sr_instance_id,p_routing_sequence_id,p_op_seq_id);
4393 fetch get_std_op_code_c into l_std_op_code;
4394 if get_std_op_code_c%notfound then
4395 return null;
4396 end if;
4397 close get_std_op_code_c;
4398 return l_std_op_code;
4399 EXCEPTION
4400 WHEN OTHERS THEN
4401 IF get_std_op_code_c%ISOPEN THEN
4402 CLOSE get_std_op_code_c;
4403 return null;
4404 END IF;
4405 end get_std_op_code;
4406
4407 function res_instance_data(p_req_column IN varchar2,
4408 p_plan_id IN number,
4409 p_sr_instance_id IN number,
4410 p_organization_id IN number,
4411 p_department_id IN number,
4412 p_resource_id IN Number,
4413 p_supply_id IN Number,
4414 p_operation_seq_num IN Number,
4415 p_resource_seq_num IN Number,
4416 p_orig_resource_seq_num IN Number,
4417 p_parent_seq_num IN Number,
4418 p_parent_Id IN Number) return varchar2
4419 is
4420 begin
4421 return null;
4422 end res_instance_data;
4423
4424 FUNCTION get_processing_leadtime(p_plan_id number,
4425 p_org number,
4426 p_inst number,
4427 p_item number,
4428 p_supplier number,
4429 p_supplier_site number) return number
4430 IS
4431
4432 CURSOR c_processing_lt_suppliers (p_plan_id number,
4433 p_org number,
4434 p_inst number,
4435 p_item number,
4436 p_supplier number,
4437 p_supplier_site number) is
4438 select processing_lead_time
4439 from msc_item_suppliers
4440 where plan_id = p_plan_id
4441 and organization_id = p_org
4442 and sr_instance_id = p_inst
4443 and inventory_item_id = p_item
4444 and using_organization_id = -1
4445 and supplier_id = p_supplier
4446 and supplier_site_id = p_supplier_site;
4447
4448 cursor c_processing_lt_items(p_plan_id in number,
4449 p_sr_instance_id in number,
4450 p_organization_id in number,
4451 p_inventory_item_id in number)
4452 is
4453 select full_lead_time
4454 from msc_system_items
4455 where plan_id = p_plan_id
4456 and sr_instance_id = p_sr_instance_id
4457 and organization_id = p_organization_id
4458 and inventory_item_id = p_inventory_item_id;
4459
4463
4460 l_processing_leadtime number;
4461
4462 begin
4464 IF p_item IS NOT NULL and p_org IS NOT NULL THEN
4465 OPEN c_processing_lt_suppliers( p_plan_id,
4466 p_org,
4467 p_inst,
4468 p_item,
4469 p_supplier,
4470 p_supplier_site);
4471 FETCH c_processing_lt_suppliers into l_processing_leadtime;
4472 CLOSE c_processing_lt_suppliers;
4473 if l_processing_leadtime is null then
4474 OPEN c_processing_lt_items( p_plan_id,
4475 p_org,
4476 p_inst,
4477 p_item);
4478 FETCH c_processing_lt_items into l_processing_leadtime;
4479 CLOSE c_processing_lt_items;
4480 end if;
4481 end if;
4482 return l_processing_leadtime;
4483 end get_processing_leadtime;
4484
4485 FUNCTION check_cfm(p_plan_id number,p_org_id number,
4486 p_instance_id number, p_item_id number,
4487 p_transaction_id number,
4488 p_impl_alt_routing varchar2) return number is
4489 CURSOR CFM_C IS
4490 select cfm_routing_flag
4491 from msc_supplies
4492 where plan_id = p_plan_id
4493 and transaction_id = p_transaction_id
4494 and sr_instance_id = p_instance_id;
4495
4496 CURSOR routing_c IS
4497 select cfm_routing_flag
4498 from msc_routings
4499 where plan_id = p_plan_id
4500 and organization_id = p_org_id
4501 and assembly_item_id = p_item_id
4502 and sr_instance_id = p_instance_id
4503 and (alternate_routing_designator = p_impl_alt_routing or
4504 (p_impl_alt_routing is null and
4505 alternate_routing_designator is null));
4506
4507 v_temp number;
4508 BEGIN
4509
4510 if p_transaction_id is null then
4511 -- unsaved new planned order won't have transaction_id yet when this is called
4512
4513 -- for manually create planned order,
4514 -- if impl_alt_routing is entered, use it first,
4515 -- otherwise, use primary routing
4516 OPEN routing_c;
4517 FETCH routing_c into v_temp;
4518 CLOSE routing_c;
4519 else
4520 OPEN CFM_C;
4521 FETCH CFM_C into v_temp;
4522 CLOSE CFM_C;
4523
4524 end if;
4525
4526 return v_temp;
4527
4528 END check_cfm;
4529
4530 FUNCTION load_type ( p_plan_type IN NUMBER
4531 , p_plan_id IN NUMBER
4532 , p_source_table IN VARCHAR2 -- MSC_SUPPLIES or MSC_DEMANDS
4533 , p_transaction_id IN NUMBER -- or demand_id
4534 , p_organization_id IN NUMBER
4535 , p_sr_instance_id IN NUMBER -- not reqd, but can hit U1 index with this
4536 , p_order_type IN NUMBER
4537 , p_implement_as IN NUMBER
4538 , p_source_organization_id IN NUMBER
4539 , p_source_sr_instance_id IN NUMBER
4540 , p_cfm_routing_flag IN NUMBER
4541 , p_item_id IN NUMBER DEFAULT NULL
4542 , p_impl_alt_routing IN VARCHAR2 DEFAULT NULL
4543 ) RETURN NUMBER IS
4544
4545 v_cfm_routing_flag NUMBER;
4546 v_load_type NUMBER;
4547
4548 -- IRproject
4549 cursor iso_exists IS
4550 select 1
4551 from msc_demands
4552 where disposition_id = p_transaction_id
4553 and plan_id = p_plan_id
4554 and organization_id = p_source_organization_id
4555 and sr_instance_id = p_sr_instance_id;
4556
4557 cursor ir_exists IS
4558 select 1
4559 from msc_supplies
4560 where plan_id = p_plan_id
4561 and sr_instance_id = p_sr_instance_id
4562 and transaction_id in
4563 (select disposition_id
4564 from msc_demands
4565 where demand_id = p_transaction_id
4566 and plan_id = p_plan_id
4567 and organization_id = p_organization_id
4568 and sr_instance_id = p_sr_instance_id) ;
4569
4570 l_iso_in_plan number := 0;
4571 l_ir_in_plan number := 0;
4572
4573 BEGIN
4574
4575 IF p_source_table = 'MSC_SUPPLIES' THEN
4576
4577 -- not passed in manual release (MSCFNORD)
4578 -- select-all will always pass this
4579 IF p_cfm_routing_flag IS NULL and
4580 ((p_order_type = 5 and p_implement_as = 3) or
4581 p_order_type = 3) THEN
4582 v_cfm_routing_flag :=
4583 msc_get_name.check_cfm(
4584 p_plan_id,
4585 p_organization_id,
4586 p_sr_instance_id,
4587 p_item_id,
4588 p_transaction_id,
4589 p_impl_alt_routing);
4590 ELSE
4591 v_cfm_routing_flag := p_cfm_routing_flag;
4592 END IF;
4593
4594 IF p_order_type = 5 THEN
4595 -- planned order
4596 IF p_implement_as = 3 THEN
4597 IF v_cfm_routing_flag = 3 THEN
4598 -- Load Lot_based Job
4602 v_load_type := 1;
4599 v_load_type := 5;
4600 ELSE
4601 -- WIP discrete mass load
4603 END IF;
4604 ELSIF p_implement_as = 2 THEN
4605 -- PO requisition mass load
4606 v_load_type := 8;
4607 ELSIF p_implement_as = 4 THEN
4608 -- WIP repetitive mass load
4609 v_load_type := 2;
4610 ELSE
4611 v_load_type := NULL;
4612 END IF;
4613 ELSIF p_order_type = 3 THEN
4614 IF v_cfm_routing_flag = 3 THEN
4615 -- Reschedule Lot_based Job
4616 v_load_type := 6;
4617 ELSE
4618 -- WIP discrete reschedule
4619 v_load_type := 4;
4620 END IF;
4621 ELSIF p_order_type = 2 THEN
4622 -- PO requisition reschedule
4623
4624 --IRproject
4625 -- IR/ISO enhancement
4626 -- if both IR and ISO are in the plan ,
4627 -- set load_type = 64, else to 16
4628 IF p_source_organization_id <> p_organization_id THEN -- means IR
4629 open iso_exists;
4630 fetch iso_exists into l_iso_in_plan;
4631 close iso_exists;
4632 IF l_iso_in_plan = 1 THEN
4633 v_load_type := 64;
4634 ELSE
4635 v_load_type := 16;
4636 END IF;
4637 ELSE -- for all non-internal reqs
4638 v_load_type := 16;
4639 END IF;
4640
4641 ELSIF p_order_type = 1 THEN
4642 -- Purchase order reschedule
4643 v_load_type := 20;
4644 ELSIF p_order_type IN (4,13) THEN
4645 -- WIP repetitive mass load
4646 v_load_type := 2;
4647 ELSE
4648 v_load_type := NULL;
4649 END IF;
4650 IF p_plan_type = 5 THEN -- DRP
4651 IF p_order_type = 51 THEN
4652 IF p_implement_as = 2 THEN
4653 -- PO requisition mass load
4654 v_load_type := 8;
4655 ELSIF p_implement_as = 5 THEN
4656 -- internal req mass load
4657 v_load_type := 32;
4658 END IF;
4659 ELSIF p_order_type = 53 and p_source_organization_id <> p_organization_id THEN
4660
4661 --IRproject
4662 open iso_exists;
4663 fetch iso_exists into l_iso_in_plan;
4664 close iso_exists;
4665 if l_iso_in_plan = 1 then
4666 -- internal req reschedule
4667 v_load_type := 64;
4668 else
4669 v_load_type := 16;
4670 end if;
4671 END IF;
4672 END IF; -- IF p_plan_type = 5 THEN
4673
4674 --pabram.srp.release.changes.begins
4675 IF p_plan_type in (8) THEN -- srp
4676 IF p_order_type = 76 THEN
4677 -- Planned New Buy Order
4678 v_load_type := 8;
4679 ELSIF p_order_type = 51 THEN
4680 -- Planned Transfer
4681 v_load_type := 32;
4682 ELSIF p_order_type = 77 THEN
4683 -- Planned Internal Repair Order
4684 v_load_type := 256;
4685 ELSIF p_order_type = 78 THEN
4686 -- Planned External Repair Order
4687 v_load_type := 128;
4688 END IF;
4689 END IF; -- IF p_plan_type in (8)
4690 --pabram.srp.release.changes.ends
4691
4692 ELSE -- p_source_table = 'MSC_DEMANDS'
4693 IF p_order_type in (30) THEN
4694 -- v_load_type := 30;
4695 IF p_plan_type = 1 THEN -- ASCP
4696 open ir_exists;
4697 fetch ir_exists into l_ir_in_plan;
4698 close ir_exists;
4699 IF l_ir_in_plan = 1 THEN
4700 v_load_type := 64;
4701 ELSE
4702 v_load_type := 30;
4703 END IF;
4704 ELSE -- other plans,in DRP if IR does not exist order type of ISO = 30
4705 v_load_type := 30;
4706 END IF;
4707
4708 ELSIF p_order_type =53 THEN -- planned shipment
4709 IF p_implement_as = 2 THEN
4710 -- PO requisition mass load
4711 v_load_type := 8;
4712 ELSIF p_implement_as = 5 THEN
4713 -- internal req mass load
4714 v_load_type := 32;
4715 END IF;
4716 ELSIF p_order_type =54 THEN -- ISO
4717 -- internal req reschedule
4718 v_load_type := 64;
4719 ELSE
4720 v_load_type := NULL;
4721 END IF;
4722 END IF;
4723 RETURN v_load_type;
4724 EXCEPTION
4725 when others THEN
4726 RETURN null;
4727 END load_type;
4728
4729 FUNCTION get_equipment_desc(arg_plan_id IN NUMBER,
4730 arg_org_id IN NUMBER,
4731 arg_instance_id IN NUMBER,
4732 arg_item_id IN NUMBER) return varchar2 is
4733 v_item_desc varchar2(2000);
4734
4735 BEGIN
4736 if arg_item_id is null or
4737 arg_item_id < 0 then
4738 return null;
4739 elsif arg_org_id is null or
4740 arg_plan_id is null or
4741 arg_instance_id is null then
4745 where inventory_item_id = arg_item_id;
4742 select description
4743 into v_item_desc
4744 from msc_items
4746 return v_item_desc;
4747 end if;
4748
4749 select description
4750 into v_item_desc
4751 from msc_system_items
4752 where inventory_item_id = arg_item_id
4753 and plan_id = arg_plan_id
4754 and organization_id = arg_org_id
4755 and sr_instance_id = arg_instance_id;
4756 return v_item_desc;
4757
4758 exception when no_data_found then
4759 select description
4760 into v_item_desc
4761 from msc_items
4762 where inventory_item_id = arg_item_id;
4763 return v_item_desc;
4764
4765 END get_equipment_desc;
4766
4767 function isResReqSegments_Available(p_plan_id in number,
4768 p_sr_instance_id in number,
4769 p_trans_id in number)
4770 return number is
4771 cursor cur_res_req_records(p_plan_id in number,
4772 p_sr_instance_id in number,
4773 p_trans_id in number) is
4774 select count(*)
4775 from msc_segment_allocations
4776 where plan_id = p_plan_id
4777 and ((producer_sr_instance_id = p_sr_instance_id and
4778 from_res_transaction_id = p_trans_id)
4779 or (consumer_sr_instance_id = p_sr_instance_id and
4780 to_res_transaction_id = p_trans_id));
4781
4782 l_count number;
4783
4784 begin
4785 open cur_res_req_records(p_plan_id,
4786 p_sr_instance_id,
4787 p_trans_id);
4788 fetch cur_res_req_records into l_count;
4789 if cur_res_req_records%notfound then
4790 close cur_res_req_records;
4791 end if;
4792 close cur_res_req_records;
4793
4794 return l_count;
4795 end;
4796
4797 FUNCTION get_application_id(arg_application_name in varchar2) return number IS
4798 application_id number;
4799 BEGIN
4800 if arg_application_name is null then
4801 return null;
4802 end if;
4803
4804 select application_id into application_id
4805 from fnd_application
4806 where application_short_name = arg_application_name;
4807
4808 return application_id;
4809
4810 EXCEPTION when no_data_found THEN
4811 return null;
4812 END get_application_id;
4813
4814 FUNCTION setup_std_op_code(
4815 p_plan_id IN NUMBER,
4816 p_sr_instance_id IN NUMBER,
4817 p_department_id IN NUMBER,
4818 p_org_id IN NUMBER,
4819 p_supply_id IN Number,
4820 p_operation_seq_num IN Number,
4821 p_resource_seq_num IN number,
4822 p_parent_seq_num IN Number,
4823 p_setup_id IN Number,
4824 p_schedule_flag IN Number
4825 ) return varchar2 IS
4826
4827 v_from_setup_id Number;
4828 v_to_setup_id Number;
4829 v_resource_id Number;
4830 v_operation_code varchar2(50):=null;
4831 v_count number := 0;
4832 CURSOR setup_c IS
4833 select
4834 res.from_setup_id
4835 ,res.setup_id
4836 ,res.resource_id
4837 from
4838 msc_resource_requirements res
4839 where res.plan_id = p_plan_id
4840 and res.sr_instance_id = p_sr_instance_id
4841 and res.department_id=p_department_id
4842 and res.organization_id = p_org_id
4843 and res.supply_id=p_supply_id
4844 and nvl(res.operation_seq_num,-23453)=nvl(p_operation_seq_num,-23453)
4845 and nvl(res.resource_seq_num,-23453)=nvl(p_resource_seq_num,-23453)
4846 -- and nvl(res.orig_resource_seq_num, -23453)=nvl(p_parent_seq_num,-23453) -- get setup id's from parent
4847 and nvl(res.parent_seq_num, -23453)=nvl(p_parent_seq_num,-23453) -- instead get from setup activity
4848 and nvl(res.parent_id,2)=2
4849 and res.setup_id is not null
4850 and res.from_setup_id is not null;
4851
4852 BEGIN
4853
4854 IF p_plan_id is null or
4855 p_sr_instance_id is null or
4856 p_department_id is null or
4857 p_org_id is null or
4858 p_supply_id is null
4859 THEN
4860 return null;
4861 END IF;
4862
4863
4864 IF p_setup_id is not null -- SDS activity, so no need to show operation
4865 or p_schedule_flag = 1 -- YES activity, not a setup activity
4866 THEN
4867 return null;
4868 END IF;
4869
4870 OPEN setup_c;
4871 FETCH setup_c INTO v_from_setup_id, v_to_setup_id, v_resource_id;
4872 v_count := setup_c%ROWCOUNT;
4873 CLOSE setup_c;
4874
4875
4876 IF v_count > 0 THEN
4877 v_operation_code := standard_operation_code(p_plan_id,
4878 p_sr_instance_id,
4879 v_resource_id,
4880 p_org_id,
4881 v_from_setup_id,
4882 v_to_setup_id
4886 return null;
4883 );
4884 return v_operation_code;
4885 END IF;
4887
4888 EXCEPTION when others THEN
4889 return null;
4890
4891 END setup_std_op_code;
4892
4893 FUNCTION is_within_rel_time_fence(p_plan_start_date IN DATE,
4894 P_order_start_date IN DATE,
4895 p_release_time_fence_code IN NUMBER,
4896 P_cumulative_total_lead_time IN NUMBER,
4897 P_cum_manufacturing_lead_time IN NUMBER,
4898 P_full_lead_time IN NUMBER,
4899 P_release_time_fence_days IN NUMBER
4900 ) RETURN NUMBER IS
4901 l_days NUMBER;
4902 BEGIN
4903 l_days :=0;
4904 IF p_release_time_fence_code = 1 THEN
4905 l_days:= NVL(p_cumulative_total_lead_time,0);
4906 ELSIF p_release_time_fence_code = 2 THEN
4907 l_days:= NVL(P_cum_manufacturing_lead_time,0);
4908 ELSIF p_release_time_fence_code = 3 THEN
4909 l_days:= NVL(P_full_lead_time,0);
4910 ELSIF p_release_time_fence_code = 4 THEN
4911 l_days:= NVL(P_release_time_fence_days,0);
4912 END IF;
4913 IF P_order_start_date between p_plan_start_date and (p_plan_start_date+l_days) THEN
4914 RETURN 1;
4915 ELSE
4916 RETURN 2;
4917 END IF;
4918 EXCEPTION
4919 WHEN others THEN
4920 RETURN 2;
4921 END is_within_rel_time_fence;
4922
4923 -- This function executes dynamic sql and returns count.
4924
4925 function execute_sql_getcount(arg_sql_stmt VARCHAR2) return number IS
4926 v_count number := 0;
4927 BEGIN
4928
4929 EXECUTE IMMEDIATE arg_sql_stmt;
4930
4931 if (SQL%ROWCOUNT) > 0 then
4932 v_count := SQL%ROWCOUNT;
4933 else
4934 v_count := 0;
4935 end if;
4936
4937 return v_count;
4938
4939 END execute_sql_getcount;
4940
4941 FUNCTION implement_as(p_order_type number,
4942 p_org_id number,
4943 p_source_org_id number,
4944 p_source_supplier_id number,
4945 p_build_in_wip_flag number,
4946 p_planning_make_buy_code number,
4947 p_purchasing_enabled_flag number,
4948 p_cfm_routing_flag number) RETURN number IS
4949 v_out number;
4950 BEGIN
4951 IF p_order_type = 13 then
4952 v_out := 104; --'Repetitive Schedules';
4953 ELSIF p_order_type in (51,77) then
4954 -- Planned Internal Repair Order
4955 v_out := 200; -- Internal Requisition
4956 ELSIF p_order_type in (76,78) then
4957 -- Planned New Buy Order,Planned External Repair Order
4958 v_out := 101; -- purchase req
4959 ELSIF p_order_type = 79 then
4960 -- Planned Repair Work Order
4961 v_out := 102; -- discrete job
4962 ELSE -- p_order_type = 5
4963 if p_org_id = p_source_org_id then
4964 if p_build_in_wip_flag = 1 then
4965 v_out := 102; -- discrete job
4966 end if;
4967 elsif p_org_id <> p_source_org_id then
4968 v_out := 101; -- purchase req
4969 elsif p_source_supplier_id is not null then
4970 if p_purchasing_enabled_flag = 1 then
4971 v_out := 101; -- purchase req
4972 end if;
4973 elsif p_source_supplier_id is null and
4974 p_source_org_id is null then
4975 if p_planning_make_buy_code =1 then
4976 if p_build_in_wip_flag = 1 then
4977 v_out := 102; -- discrete job
4978 end if;
4979 else -- p_planning_make_buy_code =2
4980 if p_purchasing_enabled_flag =1 then
4981 v_out := 101; -- purchase req
4982 end if;
4983 end if; --if p_planning_make_buy_code =1 then
4984 end if;
4985 END IF; -- IF p_order_type = 13 then
4986
4987 if v_out = 102 and p_cfm_routing_flag =1 then
4988 v_out := 103; -- Flow Schedules
4989 end if;
4990
4991 return v_out;
4992 END implement_as;
4993
4994 FUNCTION get_res_units(p_plan_id IN NUMBER,
4995 p_sr_instance_id IN NUMBER,
4996 p_org_id IN NUMBER,
4997 p_department_id IN NUMBER,
4998 p_resource_id IN NUMBER,
4999 p_batch_start_date IN DATE,
5000 p_batch_end_date IN DATE) RETURN number
5001 IS
5002 CURSOR c_capacity_units IS
5003 select capacity_units
5004 from msc_net_resource_avail
5005 where plan_id = p_plan_id
5006 and sr_instance_id = p_sr_instance_id
5007 and organization_id = p_org_id
5008 and department_id = p_department_id
5009 and resource_id = p_resource_id
5010 and trunc(shift_date) between trunc(p_batch_start_date)
5011 and trunc(p_batch_end_date)
5012 and rownum = 1;
5013
5014 v_capacity_units number;
5015 BEGIN
5016 OPEN c_capacity_units;
5017 FETCH c_capacity_units INTO v_capacity_units;
5021 end get_res_units;
5018 CLOSE c_capacity_units;
5019
5020 return v_capacity_units;
5022
5023 FUNCTION category_set_name(p_cat_set_id NUMBER) return varchar2 is
5024
5025 cursor c_cat_set_name is
5026 select category_set_name
5027 from msc_category_sets
5028 where category_set_id = p_cat_set_id;
5029
5030 l_cat_set_name varchar2(30);
5031 begin
5032 if (p_cat_set_id is null) then
5033 return null;
5034 end if;
5035 open c_cat_set_name;
5036 fetch c_cat_set_name into l_cat_set_name;
5037 close c_cat_set_name;
5038 return l_cat_set_name;
5039 exception
5040 when others then
5041 return null;
5042 end category_set_name;
5043
5044 FUNCTION get_default_dem_pri_rule_id return number
5045 is
5046 cursor dem_pri is
5047 select rule_id
5048 from msc_scheduling_rules
5049 where default_flag = 'Y'
5050 and enabled_flag = 'Y';
5051
5052 l_rule_id number;
5053 Begin
5054 open dem_pri;
5055 fetch dem_pri into l_rule_id;
5056 close dem_pri;
5057
5058 return l_rule_id ;
5059 End get_default_dem_pri_rule_id ;
5060
5061
5062 FUNCTION get_default_dem_pri_rule
5063 return varchar2 is
5064
5065 cursor dem_pri is
5066 select meaning
5067 from msc_scheduling_rules
5068 where default_flag = 'Y'
5069 and enabled_flag = 'Y';
5070
5071 l_dem_rule varchar2(255);
5072 Begin
5073 open dem_pri;
5074 fetch dem_pri into l_dem_rule;
5075 close dem_pri;
5076
5077 return l_dem_rule;
5078 End get_default_dem_pri_rule ;
5079
5080 --5375991bugfix
5081 function op_desc(p_plan_id number, p_sr_instance_id number,
5082 p_routing_seq_id number, p_op_seq_id number) return varchar2 is
5083
5084 cursor c_op_desc_cur is
5085 select mro.operation_description
5086 from msc_routing_operations mro
5087 where mro.plan_id = p_plan_id
5088 and mro.sr_instance_id = p_sr_instance_id
5089 and mro.routing_sequence_id = nvl(p_routing_seq_id, mro.routing_sequence_id)
5090 and mro.operation_sequence_id = p_op_seq_id;
5091
5092 l_op_desc varchar2(240);
5093 begin
5094 open c_op_desc_cur;
5095 fetch c_op_desc_cur into l_op_desc;
5096 close c_op_desc_cur;
5097
5098 return l_op_desc;
5099 exception
5100 when others then
5101 return null;
5102 end op_desc;
5103
5104 FUNCTION get_order_view(p_plan_type number, p_plan_id number)
5105 return varchar2 IS
5106 v_view varchar2(100);
5107 BEGIN
5108 if p_plan_type = 5 then
5109 if p_plan_id = -1 then
5110 v_view := 'MSC_ORDERS_DRP_COL_V';
5111 else
5112 v_view := 'MSC_ORDERS_DRP_V';
5113 end if;
5114 else
5115 if p_plan_id = -1 then
5116 v_view := 'MSC_ORDERS_COL_V';
5117 else
5118 v_view := 'MSC_ORDERS_V';
5119 end if;
5120 end if;
5121 return v_view;
5122 END get_order_view;
5123
5124
5125 function get_srp_group_name(p_group_id number default null, p_user_id number default null, p_planned_by number default null)
5126 return varchar2 is
5127
5128 cursor group_by_id (c_group_id number) is
5129 select
5130 group_name
5131 from
5132 msc_srp_groups
5133 where group_id = c_group_id;
5134
5135 cursor group_by_owner(c_owner varchar2, c_planner varchar2) is
5136 select
5137 g.group_name
5138 from
5139 msc_srp_user_groups owner,
5140 msc_srp_groups g
5141 where
5142 owner.user_id = c_owner
5143 and owner.group_id=g.group_id
5144 and exists -- check if the query owner and the planner belong to the currently fetched group
5145 ( select 1 from msc_srp_user_groups planner
5146 where planner.user_id = c_planner
5147 and owner.group_id=planner.group_id
5148 )
5149 and rownum=1;
5150
5151 l_group_name varchar2(240);
5152
5153 begin
5154 if p_group_id is null and p_user_id is null then
5155 return null;
5156 end if;
5157
5158 if p_group_id is not null then
5159 open group_by_id(p_group_id);
5160 fetch group_by_id into l_group_name;
5161 close group_by_id;
5162 return l_group_name;
5163 end if;
5164
5165 if p_user_id is not null then
5166 open group_by_owner(p_user_id, nvl(p_planned_by,p_user_id));
5167 fetch group_by_owner into l_group_name;
5168 close group_by_owner;
5169 return l_group_name;
5170 end if;
5171
5172 return null;
5173
5174 exception
5175 when others then return null;
5176 end;
5177
5178 function forecast_rule_name(p_forecast_rule_id number) return varchar2 is
5179 cursor c_rule is
5180 select forecast_rule_name
5181 from msc_forecast_rules
5182 where forecast_rule_id = p_forecast_rule_id;
5183 l_rule varchar2(30);
5184 begin
5185 if (p_forecast_rule_id is null) then
5186 return null;
5187 end if;
5188 open c_rule;
5189 fetch c_rule into l_rule;
5190 close c_rule;
5194 end forecast_rule_name;
5191 return l_rule;
5192 exception
5193 when others then return null;
5195
5196
5197 Function get_order_Comments(p_plan_id in number,
5198 p_entity_type in varchar2,
5199 p_transaction_id in number)
5200 return varchar2 is
5201
5202 CURSOR get_notes_details(lp_entity_type VARCHAR2,
5203 lp_plan_id NUMBER,
5204 lp_transaction_id NUMBER) IS
5205 SELECT note_text1
5206 from msc_user_notes
5207 where entity_type = lp_entity_type
5208 and plan_id = lp_plan_id
5209 and transaction_id = lp_transaction_id;
5210 l_notes_text varchar2(4000);
5211 begin
5212 --User notes enhancement
5213
5214 if p_plan_id is null
5215 or p_entity_type is null
5216 or p_transaction_id is null then
5217 return null;
5218 end if;
5219
5220 open get_notes_details(p_entity_type,
5221 p_plan_id,
5222 p_transaction_id);
5223 fetch get_notes_details into l_notes_text;
5224 close get_notes_details;
5225 return l_notes_text;
5226 Exception
5227 when others then
5228 return null;
5232 END Msc_GET_name;
5229 END get_order_Comments;
5230
5231
[Home] [Help]