1 PACKAGE BODY MSD_COMMON_UTILITIES AS
2 /* $Header: msdcmutb.pls 120.5 2010/05/17 06:01:30 lannapra ship $ */
3
4
5 /* Public Procedures */
6
7 procedure get_inst_info(
8 p_instance_id IN NUMBER,
9 p_dblink IN OUT NOCOPY VARCHAR2,
10 p_icode IN OUT NOCOPY VARCHAR2,
11 p_apps_ver IN OUT NOCOPY NUMBER,
12 p_dgmt IN OUT NOCOPY NUMBER,
13 p_instance_type IN OUT NOCOPY VARCHAR2,
14 p_retcode IN OUT NOCOPY NUMBER) IS
15 Begin
16
17 SELECT decode( m2a_dblink,
18 null, '',
19 '@'||m2a_dblink),
20 instance_code,
21 apps_ver,
22 gmt_difference/24.0,
23 instance_type
24 INTO p_dblink,
25 p_icode,
26 p_apps_ver,
27 p_dgmt,
28 p_instance_type
29 FROM MSC_APPS_INSTANCES
30 WHERE instance_id= p_instance_id;
31
32 p_retcode := 1 ;
33
34 Exception
35 when others then
36 p_dblink := null ;
37 p_icode := null ;
38 p_apps_ver := null ;
39 p_dgmt := null ;
40 p_instance_type := null ;
41 p_retcode := -1 ;
42
43 End get_inst_info ;
44
45 procedure get_db_link(
46 p_instance_id IN NUMBER,
47 p_dblink IN OUT NOCOPY VARCHAR2,
48 p_retcode IN OUT NOCOPY NUMBER) IS
49 Begin
50
51 SELECT decode( m2a_dblink,
52 null, '',
53 '@'||m2a_dblink)
54 INTO p_dblink
55 FROM MSC_APPS_INSTANCES
56 WHERE instance_id= p_instance_id;
57
58
59 -- zia: changed retcode to 0, since 1 means warning
60 --p_retcode := 1 ;
61 p_retcode := 0;
62
63 Exception
64 when others then
65 p_dblink := null ;
66 p_retcode := -1 ;
67
68
69 End get_db_link ;
70
71 function get_item_key(
72 p_instance_id IN NUMBER,
73 p_sr_key IN VARCHAR2,
74 p_val IN VARCHAR2,
75 p_level_id IN NUMBER
76 ) return number Is
77 l_ret NUMBER;
78 l_def_level_id number:=28;
79 BEGIN
80 if p_val is NULL then
81 select level_pk
82 into l_ret
83 from msd_level_values
84 where instance = p_instance_id
85 and p_sr_key = sr_level_pk
86 and level_id = nvl(p_level_id, l_def_level_id);
87 else
88 select level_pk
89 into l_ret
90 from msd_level_values
91 where instance = p_instance_id
92 and level_value = p_val
93 and level_id = nvl(p_level_id, l_def_level_id);
94 end if;
95 return l_ret;
96 EXCEPTION when others then return NULL;
97 END;
98
99 function get_org_key(
100 p_instance_id IN NUMBER,
101 p_sr_key IN VARCHAR2,
102 p_val IN VARCHAR2,
103 p_level_id IN NUMBER
104 ) return number Is
105 l_ret NUMBER;
106 l_def_level_id number:=29;
107 BEGIN
108 if p_val is NULL then
109 select level_pk
110 into l_ret
111 from msd_level_values
112 where instance = p_instance_id
113 and p_sr_key = sr_level_pk
114 and level_id = nvl(p_level_id, l_def_level_id);
115 else
116 select level_pk
117 into l_ret
118 from msd_level_values
119 where instance = p_instance_id
120 and level_value = p_val
121 and level_id = nvl(p_level_id, l_def_level_id);
122 end if;
123 return l_ret;
124 EXCEPTION when others then return NULL;
125 END;
126
127
128 function get_level_value_pk(
129 p_instance_id IN NUMBER,
130 p_sr_key IN VARCHAR2,
131 p_val IN VARCHAR2,
132 p_level_id IN NUMBER
133 ) return number IS
134 l_ret NUMBER;
135
136 cursor sr_lvl_pk_c1 (p_instance_id IN NUMBER, p_sr_key IN VARCHAR2, p_level_id IN NUMBER) IS
137 select level_pk
138 from msd_level_values
139 where instance = p_instance_id
140 and sr_level_pk = p_sr_key
141 and level_id = p_level_id;
142
143 cursor lvl_val_c1 (p_instance_id IN NUMBER, p_val IN VARCHAR2, p_level_id IN NUMBER) IS
144 select level_pk
145 from msd_level_values
146 where instance = p_instance_id
147 and level_value = p_val
148 and level_id = p_level_id;
149
150 BEGIN
151 if p_level_id is null or (p_val is NULL and p_sr_key is null) then
152 l_ret := null;
153
154 elsif p_sr_key is not null then
155
156 open sr_lvl_pk_c1(p_instance_id, p_sr_key, p_level_id);
157 fetch sr_lvl_pk_c1 into l_ret;
158 close sr_lvl_pk_c1;
159
160 else
161
162 open lvl_val_c1(p_instance_id, p_val, p_level_id);
163 fetch lvl_val_c1 into l_ret;
164 close lvl_val_c1;
165
166 end if;
167
168 return l_ret;
169
170 EXCEPTION when others then return NULL;
171 END;
172
173 function get_loc_key(
174 p_instance_id IN NUMBER,
175 p_sr_key IN VARCHAR2,
176 p_val IN VARCHAR2,
177 p_level_id IN NUMBER
178 ) return number Is
179 l_ret NUMBER;
180 l_def_level_id number:=30;
181 BEGIN
182 if p_val is NULL then
183 select level_pk
184 into l_ret
185 from msd_level_values
186 where instance = p_instance_id
187 and p_sr_key = sr_level_pk
188 and level_id = nvl(p_level_id, l_def_level_id);
189 else
190 SELECT level_pk
191 into l_ret
192 from msd_level_values
193 where instance = p_instance_id
194 and level_value = p_val
195 and level_id = nvl(p_level_id, l_def_level_id);
196 end if;
197 return l_ret;
198 EXCEPTION when others then return NULL;
199 END;
200
201
202 function get_cus_key(
203 p_instance_id IN NUMBER,
204 p_sr_key IN VARCHAR2,
205 p_val IN VARCHAR2,
206 p_level_id IN NUMBER
207 ) return number Is
208 l_ret NUMBER;
209 l_def_level_id number:=30;
210 BEGIN
211 if p_val is NULL then
212 select level_pk
213 into l_ret
214 from msd_level_values
215 where instance = p_instance_id
216 and p_sr_key = sr_level_pk
217 and level_id = nvl(p_level_id, l_def_level_id);
218 else
219 select level_pk
220 into l_ret
221 from msd_level_values
222 where instance = p_instance_id
223 and level_value = p_val
224 and level_id = nvl(p_level_id, l_def_level_id);
225 end if;
226 return l_ret;
227 EXCEPTION when others then return NULL;
228 END;
229
230
231 function get_salesrep_key(
232 p_instance_id IN NUMBER,
233 p_sr_key IN VARCHAR2,
234 p_val IN VARCHAR2,
235 p_level_id IN NUMBER
236 ) return number Is
237 l_ret NUMBER;
238 l_def_level_id number:=32;
239 BEGIN
240 if p_val is NULL then
241 select level_pk
242 into l_ret
243 from msd_level_values
244 where instance = p_instance_id
245 and p_sr_key = sr_level_pk
246 and level_id = nvl(p_level_id, l_def_level_id);
247 else
248 select level_pk
249 into l_ret
250 from msd_level_values
251 where instance = p_instance_id
252 and level_value = p_val
253 and level_id = nvl(p_level_id, l_def_level_id);
254 end if;
255 return l_ret;
256 EXCEPTION when others then return NULL;
257 END;
258
259
260 function get_sc_key(
261 p_instance_id IN NUMBER,
262 p_sr_key IN VARCHAR2,
263 p_val IN VARCHAR2,
264 p_level_id IN NUMBER
265 ) return number Is
266 l_ret NUMBER;
267 l_def_level_id number:=33;
268 BEGIN
269 if p_val is NULL then
270 select level_pk
271 into l_ret
272 from msd_level_values
273 where instance = p_instance_id
274 and p_sr_key = sr_level_pk
275 and level_id = nvl(p_level_id, l_def_level_id);
276 else
277 select level_pk
278 into l_ret
279 from msd_level_values
280 where instance = p_instance_id
281 and level_value = p_val
282 and level_id = nvl(p_level_id, l_def_level_id);
283 end if;
284 return l_ret;
285 EXCEPTION when others then return NULL;
286 END;
287
288
289
290 function get_dcs_key(
291 p_instance_id IN NUMBER,
292 p_sr_key IN VARCHAR2,
293 p_val IN VARCHAR2,
294 p_level_id IN NUMBER
295 ) return number Is
296 l_ret NUMBER;
297 l_def_level_id number:=40;
298 BEGIN
299 if p_val is NULL then
300 select level_pk
301 into l_ret
302 from msd_level_values
303 where instance = p_instance_id
304 and p_sr_key = sr_level_pk
305 and level_id = nvl(p_level_id, l_def_level_id);
306 else
307 select level_pk
308 into l_ret
309 from msd_level_values
310 where instance = p_instance_id
311 and level_value = p_val
312 and level_id = nvl(p_level_id, l_def_level_id);
313 end if;
314 return l_ret;
315 EXCEPTION when others then return NULL;
316 END;
317
318
319 function get_level_pk return number Is
320 x_temp number ;
321 Begin
322
323 select msd_level_values_s.nextval into x_temp
324 from sys.dual ;
325
326 return x_temp;
327
328
329 exception
330
331 when others then
332 null ;
333
334 end get_level_pk ;
335
336 function get_level_name( p_level_id IN NUMBER ) return varchar2
337 is
338 l_level_name varchar2(30);
339 begin
340
341 if p_level_id is NULL then
342
343 l_level_name := null ;
344
345 else
346
347 select level_name
348 into l_level_name
349 from msd_levels
350 where level_id = p_level_id
351 and plan_type is null;
352
353 end if;
354
355 return l_level_name;
356
357 EXCEPTION when others then return NULL;
358
359 END get_level_name;
360
361 FUNCTION get_supplier_calendar(
362 p_plan_id in number,
363 p_sr_instance_id in number,
364 p_organization_id in number,
365 p_inventory_item_id in number,
366 p_supplier_id in number,
367 p_supplier_site_id in number,
368 p_using_organization_id in number
369 ) return varchar2 is
370
371 cursor c1 (p_plan_id in number, p_sr_instance_id IN NUMBER, p_organization_id IN number, p_inventory_item_id IN NUMBER,
372 p_supplier_id in number, p_supplier_site_id in number, p_using_organization_id in number) IS
373 select DELIVERY_CALENDAR_CODE
374 from msc_item_suppliers
375 where plan_id = p_plan_id
376 and sr_instance_id = p_sr_instance_id
377 and organization_id = p_organization_id
378 and inventory_item_id = p_inventory_item_id
379 and supplier_id = p_supplier_id
380 and supplier_site_id = p_supplier_site_id
381 and using_organization_id = p_using_organization_id;
382
383 cursor c2 (p_sr_instance_id IN NUMBER, p_organization_id IN number) IS
384 select calendar_code
385 from msc_trading_partners
386 where partner_type = 3
387 and sr_tp_id = p_organization_id
388 and sr_instance_id = p_sr_instance_id;
389
390 l_ret varchar2(30) := null;
391 Begin
392
393 open c1 (p_plan_id, p_sr_instance_id, p_organization_id, p_inventory_item_id,
394 p_supplier_id, p_supplier_site_id, p_using_organization_id);
395 fetch c1 into l_ret;
396 close c1;
397
398 if l_ret is null then
399 open c2 (p_sr_instance_id, p_organization_id);
400 fetch c2 into l_ret;
401 close c2;
402 end if;
403
404 return l_ret;
405 EXCEPTION when others then return NULL;
406
407 End get_supplier_calendar;
408
409 FUNCTION get_safety_stock_enddate(
410 p_plan_id in number,
411 p_sr_instance_id in number,
412 p_organization_id in number,
413 p_inventory_item_id in number,
414 p_period_start_date in date
415 ) return date is
416
417 cursor c1 (p_plan_id in number, p_sr_instance_id IN NUMBER, p_organization_id IN number,
418 p_inventory_item_id IN NUMBER, p_period_start_date IN DATE) IS
419 select min(period_start_date) -1 period_end_date
420 from msc_safety_stocks
421 where plan_id = p_plan_id
422 and sr_instance_id = p_sr_instance_id
423 and organization_id = p_organization_id
424 and inventory_item_id = p_inventory_item_id
425 and period_start_date > p_period_start_date;
426
427 cursor c2 (p_plan_id in number) IS
428 select CURR_CUTOFF_DATE
429 from msc_plans
430 where plan_id = p_plan_id;
431
432 l_ret date := null;
433 Begin
434
435 open c1 (p_plan_id, p_sr_instance_id, p_organization_id, p_inventory_item_id, p_period_start_date);
436 fetch c1 into l_ret;
437 close c1;
438
439 if l_ret is null then
440 open c2 (p_plan_id);
441 fetch c2 into l_ret;
442 close c2;
443 end if;
444
445 return l_ret;
446 EXCEPTION when others then return NULL;
447
448 End get_safety_stock_enddate;
449
450 function get_sr_level_pk return number is
451 v_ret number;
452 v_count number := 1;
453
454 begin
455
456 -- loop until unique key is generated
457 while (v_count > 0) loop
458 select MSD_SR_LEVEL_PK_S.nextval
459 into v_ret
460 from sys.dual;
461
462 select count(*)
463 into v_count
464 from msd_level_values
465 where sr_level_pk = to_char(v_ret);
466 end loop;
467
468 return v_ret;
469
470 EXCEPTION
471 when others then
472 return null;
473
474 end get_sr_level_pk;
475
476 --This function is added to derive unique SR_LEVEL_PK for the Level
477 --Values during Legacy Loads.
478 function get_sr_level_pk (p_instance_id in NUMBER,
479 p_instance_code in VARCHAR2)
480 return number is
481 v_next number;
482 v_count1 number := 1;
483 v_count2 number := 1;
484 begin
485
486 -- loop until unique key is generated from staging and fact table
487 while ( v_count1 > 0 OR v_count2 > 0) loop
488
489 select MSD_SR_LEVEL_PK_S.nextval
490 into v_next
491 from sys.dual;
492
493 select count(*)
494 into v_count1
495 from msd_st_level_values
496 where sr_instance_code = p_instance_code
497 and process_flag = G_IN_PROCESS
498 and sr_level_pk = to_char(v_next);
499
500 select count(*)
501 into v_count2
502 from msd_level_values
503 where instance = to_char(p_instance_id)
504 and sr_level_pk = to_char(v_next);
505
506 end loop;
507
508 return v_next;
509
510 EXCEPTION
511 when others then
512 msc_st_util.log_message(SQLERRM);
513 return null;
514
515 end get_sr_level_pk;
516
517 /* OPM Procedure added for OPM DP integration
518 This takes level_id as input and gets the dimension code of the owning dimension
519 */
520 procedure get_dimension_code(
521 p_level_id IN NUMBER,
522 p_dimension_code IN OUT NOCOPY VARCHAR2,
523 p_retcode IN OUT NOCOPY NUMBER) IS
524 Begin
525
526 SELECT
527 dimension_code
528 INTO
529 p_dimension_code
530 FROM MSD_LEVELS
531 WHERE level_id = p_level_id
532 AND plan_type is null;
533
534 p_retcode := 1 ;
535
536 Exception
537 when others then
538 p_dimension_code := null ;
539 p_retcode := -1 ;
540
541 End get_dimension_code ;
542
543 function get_level_value(p_level_pk in number) return varchar2 is
544
545 cursor c1 is
546 select level_value
547 from msd_level_values
548 where level_pk = p_level_pk;
549
550 l_value varchar2(255);
551
552 Begin
553 open c1;
554 fetch c1 into l_value;
555 close c1;
556
557 return l_value;
558 End;
559
560
561
562 /***************************************************************************
563
564 Procedure: msd_uom_conversion
565
566 ****************************************************************************/
567 PROCEDURE msd_uom_conversion (from_unit varchar2,
568 to_unit varchar2,
569 item_id number,
570 uom_rate OUT NOCOPY number ) IS
571
572 from_class varchar2(10);
573 to_class varchar2(10);
574
575 CURSOR standard_conversions IS
576 select t.conversion_rate std_to_rate,
577 t.uom_class std_to_class,
578 f.conversion_rate std_from_rate,
579 f.uom_class std_from_class
580 from msc_uom_conversions t,
581 msc_uom_conversions f
582 where t.inventory_item_id in (item_id, 0) and
583 t.uom_code = to_unit and
584 nvl(t.disable_date, trunc(sysdate) + 1) > trunc(sysdate) and
585 f.inventory_item_id in (item_id, 0) and
586 f.uom_code = from_unit and
587 nvl(f.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
588 order by t.inventory_item_id desc, f.inventory_item_id desc;
589
590
591 std_rec standard_conversions%rowtype;
592
593
594 CURSOR interclass_conversions(p_from_class VARCHAR2, p_to_class VARCHAR2) IS
595 select decode(from_uom_class, p_from_class, 1, 2) from_flag,
596 decode(to_uom_class, p_to_class, 1, 2) to_flag,
597 conversion_rate rate
598 from msc_uom_class_conversions
599 where inventory_item_id = item_id and
600 nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate) and
601 ( (from_uom_class = p_from_class and to_uom_class = p_to_class) or
602 (from_uom_class = p_to_class and to_uom_class = p_from_class) );
603
604 class_rec interclass_conversions%rowtype;
605
606 invalid_conversion exception;
607
608 type conv_tab is table of number index by binary_integer;
609 type class_tab is table of varchar2(10) index by binary_integer;
610
611 interclass_rate_tab conv_tab;
612 from_class_flag_tab conv_tab;
613 to_class_flag_tab conv_tab;
614 from_rate_tab conv_tab;
615 to_rate_tab conv_tab;
616 from_class_tab class_tab;
617 to_class_tab class_tab;
618
619 std_index number;
620 class_index number;
621
622 from_rate number := 1;
623 to_rate number := 1;
624 interclass_rate number := 1;
625 to_class_rate number := 1;
626 from_class_rate number := 1;
627 msgbuf varchar2(500);
628
629 begin
630
631 /*
632 ** Conversion between between two UOMS.
633 **
634 ** 1. The conversion always starts from the conversion defined, if exists,
635 ** for an specified item.
636 ** 2. If the conversion id not defined for that specific item, then the
637 ** standard conversion, which is defined for all items, is used.
638 ** 3. When the conversion involves two different classes, then
639 ** interclass conversion is activated.
640 */
641
642 /* If from and to units are the same, conversion rate is 1.
643 Go immediately to the end of the procedure to exit.*/
644
645 if (from_unit = to_unit) then
646 uom_rate := 1;
647 goto procedure_end;
648 end if;
649
650
651 /* Get item specific or standard conversions */
652 open standard_conversions;
653 std_index := 0;
654 loop
655
656 std_index := std_index + 1;
657
658 fetch standard_conversions into std_rec;
659 exit when standard_conversions%notfound;
660
661 from_rate_tab(std_index) := std_rec.std_from_rate;
662 from_class_tab(std_index) := std_rec.std_from_class;
663 to_rate_tab(std_index) := std_rec.std_to_rate;
664 to_class_tab(std_index) := std_rec.std_to_class;
665
666 end loop;
667
668 close standard_conversions;
669
670 if (std_index = 0) then /* No conversions defined */
671 msgbuf := msgbuf||'Invalid standard conversion : ';
672 msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
673 msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
674 raise invalid_conversion;
675
676 else
677 /* Conversions are ordered.
678 Item specific conversions will be returned first. */
679
680 from_class := from_class_tab(1);
681 to_class := to_class_tab(1);
682 from_rate := from_rate_tab(1);
683 to_rate := to_rate_tab(1);
684
685 end if;
686
687
688 /* Load interclass conversion tables */
689 if (from_class <> to_class) then
690 class_index := 0;
691 open interclass_conversions (from_class, to_class);
692 loop
693
694 fetch interclass_conversions into class_rec;
695 exit when interclass_conversions%notfound;
696
697 class_index := class_index + 1;
698
699 to_class_flag_tab(class_index) := class_rec.to_flag;
700 from_class_flag_tab(class_index) := class_rec.from_flag;
701 interclass_rate_tab(class_index) := class_rec.rate;
702
703 end loop;
704 close interclass_conversions;
705
706 /* No interclass conversion is defined */
707 if (class_index = 0 ) then
708 msgbuf := msgbuf||'Invalid Interclass conversion : ';
709 msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
710 msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
711 raise invalid_conversion;
712 else
713 if ( to_class_flag_tab(1) = 1 and from_class_flag_tab(1) = 1 ) then
714 to_class_rate := interclass_rate_tab(1);
715 from_class_rate := 1;
716 else
717 from_class_rate := interclass_rate_tab(1);
718 to_class_rate := 1;
719 end if;
720 interclass_rate := from_class_rate/to_class_rate;
721 end if;
722 end if; /* End of from_class <> to_class */
723
724 /*
725 ** conversion rates are defaulted to '1' at the start of the procedure
726 ** so seperate calculations are not required for standard/interclass
727 ** conversions
728 */
729
730 if (to_rate <> 0 ) then
731 uom_rate := (from_rate * interclass_rate) / to_rate;
732 else
733 uom_rate := 1;
734 end if;
735
736
737 /* Put a label and a null statement over here so that you can
738 the goto statements can branch here */
739 <<procedure_end>>
740
741 null;
742
743 exception
744
745 when others then
746 uom_rate := 1;
747
748 END msd_uom_conversion;
749
750
751 /*******************************************************************
752
753 Function : uom_conv
754
755 *********************************************************************/
756
757 function uom_conv (uom_code varchar2,
758 item_id number default null) return number as
759
760 base_uom varchar2(3);
761 conv_rate number:=1;
762 l_master_org number;
763 l_master_uom varchar2(3);
764
765 begin
766
767 select to_number(parameter_value)
768 into l_master_org
769 from msd_setup_parameters
770 where parameter_name = 'MSD_MASTER_ORG';
771
772 select NVL(uom_code,'Ea')
773 into l_master_uom
774 from msc_system_items
775 where inventory_item_id = item_id
776 and organization_id = l_master_org;
777
778 /* Convert to Master org primary uom */
779
780 msd_uom_conversion(uom_code,l_master_uom,item_id,conv_rate);
781
782 return conv_rate;
783
784
785 exception
786
787 when others then
788
789 return 1;
790
791 end uom_conv;
792
793
794 /*******************************************************************
795
796 Function : msd_uom_convert
797
798 *********************************************************************/
799 FUNCTION msd_uom_convert (p_item_id number,
800 p_precision number,
801 p_from_unit varchar2,
802 p_to_unit varchar2) RETURN number is
803
804 l_uom_rate number;
805 l_msgbuf varchar2(200);
806 l_eff_precision number;
807
808 l_from_unit varchar2(10);
809 l_to_unit varchar2(10);
810
811 BEGIN
812
813
814 IF (p_from_unit is not null and p_to_unit is not null) THEN
815 msd_uom_conversion(p_from_unit, p_to_unit,p_item_id, l_uom_rate);
816
817 if ( l_uom_rate = -99999 ) then
818 return 1;
819 end if;
820
821 if (p_precision IS NULL) then
822 return l_uom_rate;
823 else
824 return round(l_uom_rate, p_precision);
825 end if;
826
827 ELSE /* if either p_from_unit or p_to_unit is null */
828 RETURN 1;
829 END IF;
830
831
832 EXCEPTION
833
834 when others then
835 return 1;
836 END msd_uom_convert;
837
838
839 /*******************************************************************
840
841 Function : get_parent_level_pk
842
843 *********************************************************************/
844
845 FUNCTION get_parent_level_pk (
846 p_instance_id varchar2,
847 p_level_id number,
848 p_parent_level_id number,
849 p_sr_level_pk varchar2
850 ) return number is
851
852 CURSOR c_parent_level_pk IS
853 select sr_parent_level_pk
854 from msd_level_associations
855 where
856 instance = p_instance_id and
857 level_id = p_level_id and
858 parent_level_id = p_parent_level_id and
859 sr_level_pk = p_sr_level_pk;
860
861
862 l_parent_level_pk varchar2(240) := NULL;
863
864 BEGIN
865
866
867 OPEN c_parent_level_pk;
868 FETCH c_parent_level_pk INTO l_parent_level_pk;
869 CLOSE c_parent_level_pk;
870
871 IF (l_parent_level_pk is NULL) then
872 return NULL;
873 END IF;
874
875 /* return parent_level_pk only if it contains numeric values only */
876 IF ( ltrim(l_parent_level_pk,'.0123456789') is NULL ) THEN
877 return to_number(l_parent_level_pk);
878 ELSE
879 return NULL;
880 END IF;
881
882 EXCEPTION
883
884 when others then
885 return (NULL);
886 END get_parent_level_pk;
887
888 /*******************************************************************
889
890 Function : get_child_level_pk
891
892 *********************************************************************/
893
894 FUNCTION get_child_level_pk (
895 p_instance_id varchar2,
896 p_level_id number,
897 p_parent_level_id number,
898 p_sr_level_pk varchar2
899 ) return number is
900
901 CURSOR c_child_level_pk IS
902 select sr_level_pk
903 from msd_level_associations
904 where
905 instance = p_instance_id and
906 level_id = p_level_id and
907 parent_level_id = p_parent_level_id and
908 sr_parent_level_pk = p_sr_level_pk and
909 rownum < 2;
910
911
912 l_child_level_pk varchar2(240) := NULL;
913
914 BEGIN
915
916
917 OPEN c_child_level_pk;
918 FETCH c_child_level_pk INTO l_child_level_pk;
919 CLOSE c_child_level_pk;
920
921 IF (l_child_level_pk is NULL) then
922 return NULL;
923 END IF;
924
925 /* return child_level_pk only if it contains numeric values only */
926 IF ( ltrim(l_child_level_pk,'.0123456789') is NULL ) THEN
927 return to_number(l_child_level_pk);
928 ELSE
929 return NULL;
930 END IF;
931
932 EXCEPTION
933
934 when others then
935 return (NULL);
936 END get_child_level_pk;
937
938
939 /*******************************************************************
940
941 Function : is_global_scenario
942
943 Determines whether scenario is passed to ascp as global forecast
944 or not. Possible return values are :
945
946 (1) Y : Global Forecast. Data is published at All Organizations.
947
948 (2) N : Local Forecast : Data is published at Organization level.
949
950 (3) Null : Data published with Org specific BOM and not Org output level.
951 not compatible with ASCP.
952
953 Ouput Level *** Org ** All Org ** Other Level ++ No Org Level
954
955 Org Spec *** N ** Null ** Null ** Null
956
957 Global *** Y ** Y ** Y ** Y
958
959 No Bom *** N ** Y ** Y ** Y
960
961
962 *********************************************************************/
963
964 FUNCTION is_global_scenario (
965 p_demand_plan_id number,
966 p_scenario_id number,
967 p_use_org_specific_bom_flag varchar2
968 ) return varchar2 is
969
970 CURSOR get_org_out_level
971 IS
972 select mol.level_id
973 from msd_dp_scenario_output_levels mol
974 where
975 mol.demand_plan_id = p_demand_plan_id
976 and mol.scenario_id = p_scenario_id
977 and mol.level_id = 7;
978
979
980 x_org_level_id varchar2(30);
981
982 BEGIN
983
984 -- Check output level for scenario
985 -- Will be null if none defined.
986
987 open get_org_out_level;
988 fetch get_org_out_level into x_org_level_id;
989 close get_org_out_level;
990
991 -- The forecast is only local if global bom is not used
992 -- and output level is organization.
993
994 if ( ((p_use_org_specific_bom_flag = 'Y') or (p_use_org_specific_bom_flag is null))
995 and (x_org_level_id = 7)) then
996 return 'N';
997 elsif (p_use_org_specific_bom_flag = 'Y') and (nvl(x_org_level_id, 0) <> 7) then
998 return null;
999 else
1000 -- if (global bom) or (no bom used and not published at org)
1001 return 'Y';
1002 end if;
1003
1004 EXCEPTION
1005
1006 when others then
1007 return null;
1008 END is_global_scenario;
1009
1010
1011
1012 /*******************************************************************
1013
1014 Function : IS_VALID_PF_EXIST
1015
1016 *********************************************************************/
1017
1018
1019 FUNCTION IS_VALID_PF_EXIST ( p_instance in VARCHAR2,
1020 p_inventory_item_id in NUMBER) RETURN NUMBER IS
1021
1022 CURSOR c_count IS
1023 SELECT
1024 1
1025 FROM
1026 msd_level_associations
1027 WHERE
1028 instance = p_instance
1029 and level_id = 1 -- item
1030 and sr_level_pk = p_inventory_item_id
1031 and parent_level_id = 3 -- product family
1032 and sr_parent_level_pk <> '-777'; -- others
1033
1034 l_count NUMBER := 0;
1035
1036 BEGIN
1037
1038 OPEN c_count;
1039 FETCH c_count INTO l_count;
1040 IF c_count%ISOPEN THEN
1041 CLOSE c_count;
1042 END IF;
1043
1044 /*Yes*/
1045 IF l_count > 0 THEN
1046 return 1;
1047 ELSE /* NO */
1048 return 2;
1049 END IF;
1050
1051 EXCEPTION
1052 when others then
1053 return NULL;
1054
1055 END IS_VALID_PF_EXIST;
1056
1057
1058
1059 /*******************************************************************
1060
1061 Function : get_end_date
1062
1063 *********************************************************************/
1064
1065 Function get_end_date(
1066 p_date in date,
1067 p_calendar_type in number,
1068 p_calendar_code in varchar2,
1069 p_bucket_type in number) return date is
1070
1071 /*
1072 For BUCKET_TYPE
1073 -------------------
1074 9 ----------> DAY
1075 1 ----------> WEEK
1076 2 ----------> MONTH
1077 */
1078 cursor c1 is
1079 select /*+ CACHE */ decode(p_bucket_type, 9, day, 1, week_end_date, month_end_date) from msd_time
1080 where
1081 calendar_type = p_calendar_type and
1082 calendar_code = p_calendar_code and
1083 p_date = day;
1084
1085 l_ret date := NULL;
1086
1087 Begin
1088 open c1;
1089 fetch c1 into l_ret;
1090 close c1;
1091
1092 return l_ret;
1093
1094 End get_end_date;
1095
1096
1097 /*******************************************************************
1098 Function : get_lvl_pk_from_tp_id
1099
1100 This function returns level PK for a customer from
1101 PartnerID(tp_id).
1102
1103 *********************************************************************/
1104 Function get_lvl_pk_from_tp_id(
1105 p_tp_id in number,
1106 p_sr_instance_id in number) return number is
1107
1108 cursor c_lvl_pk is
1109 select mlv.level_pk
1110 from msc_tp_id_lid mtp, msd_level_values mlv
1111 where
1112 mtp.tp_id = p_tp_id
1113 and mtp.sr_instance_id = p_sr_instance_id
1114 and mtp.partner_type = 2
1115 and mtp.sr_company_id = -1
1116 and mtp.sr_instance_id = mlv.instance
1117 and mtp.sr_tp_id = mlv.sr_level_pk
1118 and mlv.level_id = 15
1119 and rownum < 2;
1120
1121 l_lvl_pk number;
1122
1123 BEGIN
1124
1125 OPEN c_lvl_pk;
1126 FETCH c_lvl_pk INTO l_lvl_pk;
1127 CLOSE c_lvl_pk;
1128
1129 return l_lvl_pk;
1130
1131 EXCEPTION
1132 when others then
1133 return NULL;
1134
1135 END get_lvl_pk_from_tp_id;
1136
1137 /*******************************************************************
1138 Function : get_translated_date
1139
1140 This function is used for time aggregation by DPE when downloading
1141 data from DPS.
1142 *********************************************************************/
1143
1144 FUNCTION get_translated_date (p_sql in varchar2, p_date in date) return date is
1145
1146 ldt date;
1147
1148 Begin
1149 execute immediate p_sql into ldt using p_date;
1150 return ldt;
1151 end get_translated_date;
1152
1153 /*******************************************************************
1154 Function : get_iHelp_URL_prefix
1155
1156 This function is used to build the iHelp URL in DPE by appending the
1157 help topic to the prefix
1158 *********************************************************************/
1159
1160 function get_iHelp_URL_prefix return varchar2 is
1161
1162 begin
1163 return fnd_profile.value('HELP_WEB_AGENT') || '&lang=' || userenv('lang') || '&root=' || fnd_profile.value('HELP_TREE_ROOT') || '&path=' || userenv('lang') || '/MSD/@';
1164 end get_iHelp_URL_prefix;
1165
1166 /*******************************************************************
1167 Procedure : detach_all_aws
1168
1169 This procedure is used to detach all attached workspaces before
1170 releasing a connection back into the connection pool
1171 *********************************************************************/
1172
1173 procedure detach_all_aws is
1174 aw_command varchar2(4000);
1175 begin
1176 -- construct dml command
1177 aw_command := 'shw nafill(filterlines(aw(list) if value eq ''EXPRESS'' then na else joinchars(''aw detach noq '' value '';'')) '' '')';
1178 aw_command := dbms_lob.substr(dbms_aw_interp(aw_command));
1179 -- execute detach commands
1180 dbms_aw_interp_silent(aw_command);
1181 execute immediate 'begin dbms_aw.shutdown;end;';
1182 end detach_all_aws;
1183
1184
1185 /*******************************************************************
1186 Fucntion : Get_Conc_Request_Status
1187
1188 This function returns the phase of the concurrent request
1189 *********************************************************************/
1190
1191 function Get_Conc_Request_Status(conc_request_id NUMBER)
1192 return varchar2 is
1193
1194 l_del_req_id NUMBER := conc_request_id;
1195 l_phase VARCHAR2(100);
1196 l_status VARCHAR2(100);
1197 l_dev_status VARCHAR2(100);
1198 l_dev_phase VARCHAR2(100);
1199 l_message VARCHAR2(100);
1200 l_ret_val boolean;
1201
1202 Begin
1203 l_ret_val := fnd_concurrent.get_request_status(l_del_req_id,'','',l_phase,l_status,l_dev_phase,l_dev_status,l_message);
1204 if l_ret_val = TRUE then
1205 return(l_dev_phase);
1206 else
1207 return('COMPLETE');
1208 end if;
1209 End Get_Conc_Request_Status;
1210
1211 /* wrappers for dbms_aw package */
1212 FUNCTION DBMS_AW_INTERP(cmd varchar2) RETURN CLOB IS
1213 v_ret clob;
1214 begin
1215 execute immediate 'select dbms_aw.interp(:1) from dual' into v_ret using cmd;
1216 return v_ret;
1217 end DBMS_AW_INTERP;
1218
1219 FUNCTION DBMS_AW_INTERPCLOB(cmd clob) RETURN CLOB IS
1220 v_ret clob;
1221 begin
1222 execute immediate 'select dbms_aw.interpclob(:1) from dual' into v_ret using cmd;
1223 return v_ret;
1224 end DBMS_AW_INTERPCLOB;
1225
1226 PROCEDURE DBMS_AW_INTERP_SILENT(cmd varchar2) IS
1227 begin
1228 execute immediate 'begin dbms_aw.interp_silent(:1);end;' using cmd;
1229 end DBMS_AW_INTERP_SILENT;
1230
1231 PROCEDURE DBMS_AW_EXECUTE(cmd varchar2) IS
1232 begin
1233 execute immediate 'begin dbms_aw.execute(:1);end;' using cmd;
1234 end DBMS_AW_EXECUTE;
1235
1236 FUNCTION GET_BUCKET_END_DATE (P_EFFECTIVE_DATE IN DATE,
1237 P_OFFSET IN NUMBER,
1238 P_TIME_LEVEL_ID IN NUMBER,
1239 P_CALENDAR_CODE IN VARCHAR2) RETURN DATE IS
1240
1241 cursor c1 is
1242 select distinct decode(P_TIME_LEVEL_ID,
1243 1,week_end_date,
1244 2,month_end_date,
1245 3,month_end_date,
1246 4,quarter_end_date,
1247 5,year_end_date,
1248 6,month_end_date,
1249 7,quarter_end_date,
1250 8,year_end_date,
1251 10,week_end_date,
1252 11,month_end_date,
1253 12,quarter_end_date,
1254 13,year_end_date)
1255 from msd_time
1256 where calendar_type = decode(P_TIME_LEVEL_ID,1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4)
1257 and calendar_code = P_CALENDAR_CODE
1258 and decode(P_TIME_LEVEL_ID,
1259 1,week_end_date,
1260 2,month_end_date,
1261 3,month_end_date,
1262 4,quarter_end_date,
1263 5,year_end_date,
1264 6,month_end_date,
1265 7,quarter_end_date,
1266 8,year_end_date,
1267 10,week_end_date,
1268 11,month_end_date,
1269 12,quarter_end_date,
1270 13,year_end_date) = day
1271 and P_EFFECTIVE_DATE between
1272 decode(P_TIME_LEVEL_ID,
1273 1,week_start_date,
1274 2,month_start_date,
1275 3,month_start_date,
1276 4,quarter_start_date,
1277 5,year_start_date,
1278 6,month_start_date,
1279 7,quarter_start_date,
1280 8,year_start_date,
1281 10,week_start_date,
1282 11,month_start_date,
1283 12,quarter_start_date,
1284 13,year_start_date) and day
1285 and P_OFFSET = 1;
1286
1287 cursor c2(p_date date,p_offset number) is
1288 select bucket_date from
1289 (select distinct decode(P_TIME_LEVEL_ID,
1290 1,week_end_date,
1291 2,month_end_date,
1292 3,month_end_date,
1293 4,quarter_end_date,
1294 5,year_end_date,
1295 6,month_end_date,
1296 7,quarter_end_date,
1297 8,year_end_date,
1298 10,week_end_date,
1299 11,month_end_date,
1300 12,quarter_end_date,
1301 13,year_end_date) bucket_date
1302 from msd_time
1303 where calendar_type = decode(P_TIME_LEVEL_ID,1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4)
1304 and calendar_code = P_CALENDAR_CODE
1305 and decode(P_TIME_LEVEL_ID,
1306 1,week_end_date,
1307 2,month_end_date,
1308 3,month_end_date,
1309 4,quarter_end_date,
1310 5,year_end_date,
1311 6,month_end_date,
1312 7,quarter_end_date,
1313 8,year_end_date,
1314 10,week_end_date,
1315 11,month_end_date,
1316 12,quarter_end_date,
1317 13,year_end_date) = day
1318 and day >= p_date
1319 order by bucket_date)
1320 where rownum <= p_offset;
1321
1322 cursor c3(p_date date,p_offset number) is
1323 select bucket_date from
1324 (select distinct decode(P_TIME_LEVEL_ID,
1325 1,week_end_date,
1326 2,month_end_date,
1327 3,month_end_date,
1328 4,quarter_end_date,
1329 5,year_end_date,
1330 6,month_end_date,
1331 7,quarter_end_date,
1332 8,year_end_date,
1333 10,week_end_date,
1334 11,month_end_date,
1335 12,quarter_end_date,
1336 13,year_end_date) bucket_date
1337 from msd_time
1338 where calendar_type = decode(P_TIME_LEVEL_ID,1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4)
1339 and calendar_code = P_CALENDAR_CODE
1340 and decode(P_TIME_LEVEL_ID,
1341 1,week_end_date,
1342 2,month_end_date,
1343 3,month_end_date,
1344 4,quarter_end_date,
1345 5,year_end_date,
1346 6,month_end_date,
1347 7,quarter_end_date,
1348 8,year_end_date,
1349 10,week_end_date,
1350 11,month_end_date,
1351 12,quarter_end_date,
1352 13,year_end_date) = day
1353 and day <= p_date
1354 order by bucket_date desc)
1355 where rownum <= abs(p_offset);
1356
1357
1358 l_return_date DATE := P_EFFECTIVE_DATE;
1359
1360 BEGIN
1361
1362 if p_offset = 1 then
1363
1364 open c1;
1365 fetch c1 into l_return_date;
1366 close c1;
1367
1368 elsif p_offset > 1 then
1369
1370 for i in c2(p_effective_date,p_offset) loop
1371 l_return_date := i.bucket_date;
1372 end loop;
1373
1374 elsif p_offset < 0 then
1375
1376 for i in c3(p_effective_date,p_offset) loop
1377 l_return_date := i.bucket_date;
1378 end loop;
1379
1380 end if;
1381
1382
1383 return l_return_date;
1384
1385 exception
1386 when others then
1387 l_return_date := P_EFFECTIVE_DATE;
1388 return l_return_date;
1389
1390 END GET_BUCKET_END_DATE;
1391
1392
1393 FUNCTION GET_BUCKET_START_DATE (P_EFFECTIVE_DATE IN DATE,
1394 P_OFFSET IN NUMBER,
1395 P_TIME_LEVEL_ID IN NUMBER,
1396 P_CALENDAR_CODE IN VARCHAR2) RETURN DATE IS
1397
1398 cursor c1 is
1399 select distinct decode(P_TIME_LEVEL_ID,
1400 1,week_start_date,
1401 2,month_start_date,
1402 3,month_start_date,
1403 4,quarter_start_date,
1404 5,year_start_date,
1405 6,month_start_date,
1406 7,quarter_start_date,
1407 8,year_start_date,
1408 10,week_start_date,
1409 11,month_start_date,
1410 12,quarter_start_date,
1411 13,year_start_date)
1412 from msd_time
1413 where calendar_type = decode(P_TIME_LEVEL_ID,1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4)
1414 and calendar_code = P_CALENDAR_CODE
1415 and decode(P_TIME_LEVEL_ID,
1416 1,week_start_date,
1417 2,month_start_date,
1418 3,month_start_date,
1419 4,quarter_start_date,
1420 5,year_start_date,
1421 6,month_start_date,
1422 7,quarter_start_date,
1423 8,year_start_date,
1424 10,week_start_date,
1425 11,month_start_date,
1426 12,quarter_start_date,
1427 13,year_start_date) = day
1428 and P_EFFECTIVE_DATE between day and
1429 decode(P_TIME_LEVEL_ID,
1430 1,week_end_date,
1431 2,month_end_date,
1432 3,month_end_date,
1433 4,quarter_end_date,
1434 5,year_end_date,
1435 6,month_end_date,
1436 7,quarter_end_date,
1437 8,year_end_date,
1438 10,week_end_date,
1439 11,month_end_date,
1440 12,quarter_end_date,
1441 13,year_end_date)
1442 and P_OFFSET = 1;
1443
1444 cursor c2(p_date date,p_offset number) is
1445 select bucket_date from
1446 (select distinct decode(P_TIME_LEVEL_ID,
1447 1,week_start_date,
1448 2,month_start_date,
1449 3,month_start_date,
1450 4,quarter_start_date,
1451 5,year_start_date,
1452 6,month_start_date,
1453 7,quarter_start_date,
1454 8,year_start_date,
1455 10,week_start_date,
1456 11,month_start_date,
1457 12,quarter_start_date,
1458 13,year_start_date) bucket_date
1459 from msd_time
1460 where calendar_type = decode(P_TIME_LEVEL_ID,1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4)
1461 and calendar_code = P_CALENDAR_CODE
1462 and decode(P_TIME_LEVEL_ID,
1463 1,week_start_date,
1464 2,month_start_date,
1465 3,month_start_date,
1466 4,quarter_start_date,
1467 5,year_start_date,
1468 6,month_start_date,
1469 7,quarter_start_date,
1470 8,year_start_date,
1471 10,week_start_date,
1472 11,month_start_date,
1473 12,quarter_start_date,
1474 13,year_start_date) = day
1475 and day >= p_date
1476 order by bucket_date)
1477 where rownum <= p_offset;
1478
1479 cursor c3(p_date date,p_offset number) is
1480 select bucket_date from
1481 (select distinct decode(P_TIME_LEVEL_ID,
1482 1,week_start_date,
1483 2,month_start_date,
1484 3,month_start_date,
1485 4,quarter_start_date,
1486 5,year_start_date,
1487 6,month_start_date,
1488 7,quarter_start_date,
1489 8,year_start_date,
1490 10,week_start_date,
1491 11,month_start_date,
1492 12,quarter_start_date,
1493 13,year_start_date) bucket_date
1494 from msd_time
1495 where calendar_type = decode(P_TIME_LEVEL_ID,1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4)
1496 and calendar_code = P_CALENDAR_CODE
1497 and decode(P_TIME_LEVEL_ID,
1498 1,week_start_date,
1499 2,month_start_date,
1500 3,month_start_date,
1501 4,quarter_start_date,
1502 5,year_start_date,
1503 6,month_start_date,
1504 7,quarter_start_date,
1505 8,year_start_date,
1506 10,week_start_date,
1507 11,month_start_date,
1508 12,quarter_start_date,
1509 13,year_start_date) = day
1510 and day <= p_date
1511 order by bucket_date desc)
1512 where rownum <= abs(p_offset);
1513
1514
1515 l_return_date DATE := P_EFFECTIVE_DATE;
1516
1517 BEGIN
1518
1519 if p_offset = 1 then
1520
1521 open c1;
1522 fetch c1 into l_return_date;
1523 close c1;
1524
1525 elsif p_offset > 1 then
1526
1527 for i in c2(p_effective_date,p_offset-1) loop
1528 l_return_date := i.bucket_date;
1529 end loop;
1530
1531 elsif p_offset < 0 then
1532
1533 for i in c3(p_effective_date,p_offset-1) loop
1534 l_return_date := i.bucket_date;
1535 end loop;
1536
1537 end if;
1538
1539
1540 return l_return_date;
1541
1542 exception
1543 when others then
1544 l_return_date := P_EFFECTIVE_DATE;
1545 return l_return_date;
1546
1547 END GET_BUCKET_START_DATE;
1548
1549
1550 FUNCTION GET_AGE_IN_BUCKETS(P_START_DATE IN DATE,
1551 P_END_DATE IN DATE,
1552 P_TIME_LEVEL_ID IN NUMBER,
1553 P_CALENDAR_CODE IN VARCHAR2) RETURN NUMBER IS
1554
1555 l_age NUMBER := 0;
1556
1557 cursor c1 is
1558 select count(distinct decode(P_TIME_LEVEL_ID,
1559 1,week_start_date,
1560 2,month_start_date,
1561 3,month_start_date,
1562 4,quarter_start_date,
1563 5,year_start_date,
1564 6,month_start_date,
1565 7,quarter_start_date,
1566 8,year_start_date,
1567 10,week_start_date,
1568 11,month_start_date,
1569 12,quarter_start_date,
1570 13,year_start_date))
1571 from msd_time
1572 where calendar_type = decode(P_TIME_LEVEL_ID,1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4)
1573 and calendar_code = P_CALENDAR_CODE
1574 and decode(P_TIME_LEVEL_ID,
1575 1,week_start_date,
1576 2,month_start_date,
1577 3,month_start_date,
1578 4,quarter_start_date,
1579 5,year_start_date,
1580 6,month_start_date,
1581 7,quarter_start_date,
1582 8,year_start_date,
1583 10,week_start_date,
1584 11,month_start_date,
1585 12,quarter_start_date,
1586 13,year_start_date) = day
1587 and day between P_START_DATE and P_END_DATE;
1588
1589
1590 BEGIN
1591
1592 open c1;
1593 fetch c1 into l_age;
1594 close c1;
1595
1596 return l_age;
1597
1598 exception
1599 when others then
1600 l_age := 0;
1601 return l_age;
1602
1603 END GET_AGE_IN_BUCKETS;
1604
1605 FUNCTION GET_SR_LEVEL_PK(P_INSTANCE_ID IN NUMBER, P_LEVEL_ID IN NUMBER, P_LEVEL_PK IN NUMBER, P_LEVEL_VALUE OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1606
1607 cursor c1 is
1608 select sr_level_pk, level_value
1609 from msd_level_values
1610 where instance = P_INSTANCE_ID
1611 and level_id = P_LEVEL_ID
1612 and level_pk = P_LEVEL_PK;
1613
1614 l_level_value VARCHAR2(200);
1615 l_sr_level_pk VARCHAR2(200);
1616
1617 BEGIN
1618
1619 open c1;
1620 fetch c1 into l_sr_level_pk,l_level_value;
1621 close c1;
1622
1623 return l_sr_level_pk;
1624
1625 END GET_SR_LEVEL_PK;
1626
1627 function get_dp_enabled_flag (
1628 p_instance_id IN NUMBER,
1629 p_sr_key IN VARCHAR2,
1630 p_val IN VARCHAR2,
1631 p_level_id IN NUMBER
1632 ) return number IS
1633
1634 l_ret NUMBER;
1635 l_def_level_id number:=28;
1636 BEGIN
1637 if p_val is NULL then
1638 select dp_enabled_flag
1639 into l_ret
1640 from msd_level_values
1641 where instance = p_instance_id
1642 and p_sr_key = sr_level_pk
1643 and level_id = nvl(p_level_id, l_def_level_id);
1644 else
1645 select dp_enabled_flag
1646 into l_ret
1647 from msd_level_values
1648 where instance = p_instance_id
1649 and level_value = p_val
1650 and level_id = nvl(p_level_id, l_def_level_id);
1651 end if;
1652 return l_ret;
1653 EXCEPTION when others then return NULL;
1654
1655 END get_dp_enabled_flag;
1656
1657
1658 procedure dp_log(plan_id number, msg varchar2, msg_type varchar2) IS
1659 script varchar2(4000);
1660 begin
1661 script := 'aw attach odpcode ro;call dp.log('''||msg||''' '''||msg_type||''' NA NA '''||plan_id||''')';
1662
1663 dbms_aw_interp_silent(script);
1664
1665 exception
1666 when others then
1667 null;
1668 end dp_log;
1669
1670 /*Bug#4249928 */
1671 Function get_system_attribute1_desc(p_lookup_code in varchar2)
1672 return varchar2
1673 is
1674 l_system_attribute1 varchar2(240);
1675 begin
1676 select meaning into l_system_attribute1
1677 from fnd_lookup_values_vl
1678 where
1679 LOOKUP_TYPE='MSD_LEVEL_VALUE_DESC' and
1680 LOOKUP_CODE= p_lookup_code;
1681 return l_system_attribute1;
1682 end get_system_attribute1_desc;
1683
1684 Function EFFEC_AUTH( P_period_start_date date
1685 ,p_period_end_date date
1686 ,p_supplier_id number
1687 ,p_sr_instance_id number
1688 ,p_organization_id number
1689 ,p_inventory_item_id number
1690 ,p_supplier_site_id number
1691 ,p_demand_plan_id number)
1692 return number
1693 is
1694 l_auth_percent number;
1695 begin
1696 select sum(mad.PERCENTAGE_PURCHASE_PRICE)/((p_period_end_date - p_period_start_date +1 )*100) into l_auth_percent
1697 from MSC_ASL_AUTH_DETAILS mad,
1698 msd_time mt,
1699 msd_demand_plans mdp
1700 where p_supplier_id = mad.supplier_id and
1701 p_sr_instance_id = mad.sr_instance_id and
1702 p_organization_id = mad.organization_id and
1703 p_inventory_item_id = mad.inventory_item_id and
1704 p_supplier_site_id = mad.supplier_site_id and
1705 mdp.demand_plan_id = p_demand_plan_id and
1706 mt.day between trunc(mdp.plan_start_date+mad.start_days) and trunc(mdp.plan_START_date+mad.end_days) and
1707 mt.calendar_code='GREGORIAN'and
1708 mt.calendar_type=1 and
1709 mt.day between p_period_start_date and p_period_end_date ;
1710 return l_auth_percent;
1711 end;
1712
1713 PROCEDURE AUDIT_MEASURES(errbuf out nocopy varchar2,
1714 retcode out nocopy varchar2,demand_plan_id number) IS
1715 x_result clob;
1716 x_i number := 0;
1717 begin
1718 -- DBMS_AW_EXECUTE('aw attach odpcode ro');
1719 x_result := DBMS_AW_INTERP('aw attach odpcode ro;show DP.MEAS.AUDIT('''||demand_plan_id||''')');
1720 DBMS_AW_EXECUTE('aw detach odpcode');
1721 LOOP
1722 x_i := x_i+1;
1723 fnd_file.put_line(fnd_file.log, to_char(substr(x_result,((x_i-1)*4000)+1,4000)));
1724 EXIT WHEN x_i>=(length(x_result)/4000);
1725 END LOOP;
1726 end AUDIT_MEASURES;
1727
1728
1729 END MSD_COMMON_UTILITIES ;
[Home] [Help]