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