1 PACKAGE BODY MSD_SR_UTIL AS
2 /* $Header: msdutilb.pls 120.11 2011/12/09 11:46:09 rissingh ship $ */
3
4 /* Public Constants */
5 NULL_VALUE_CONST constant varchar2(10) := 'Other';
6 NULL_VALUE_PK_CONST constant number := -777;
7 ALL_CHANNELS_CONST constant varchar(20) := 'All Channels';
8 ALL_CHANNELS_PK_CONST constant number := -5 ;
9 ALL_GEOGRAPHY_CONST constant varchar(20) := 'All Geography';
10 ALL_GEOGRAPHY_PK_CONST constant number := -3 ;
11 ALL_ORGANIZATIONS_CONST constant varchar(20) := 'All Organizations';
12 ALL_ORGANIZATIONS_PK_CONST constant number := -2 ;
13 ALL_PRODUCTS_CONST constant varchar(20) := 'All Products';
14 ALL_PRODUCTS_PK_CONST constant number := -1 ;
15 ALL_SALESREP_CONST constant varchar(20) := 'All Sales Rep';
16 ALL_SALESREP_PK_CONST constant number := -4 ;
17
18 ALL_DEMANDCLASS_CONST constant varchar(20) := 'All Demand Class';
19 ALL_DEMANDCLASS_PK_CONST constant number := -6 ;
20
21 SUPPLIERS_CONST constant varchar2(20) := 'Suppliers'; --jarorad
22 SUPPLIERS_PK_CONST constant number := -999; --jarorad
23
24 /* For Liability */
25 ALL_SUPPLIER_PK_CONST constant number := -3 ; ---vinekuma
26 ALL_AUTHORIZATION_PK_CONST constant number := -6 ; ----vinekuma
27
28
29 C_YES constant number := 1;
30 C_NO constant number := 2;
31
32
33 /* BOM ITEM TYPE */
34 C_MODEL constant number := 1;
35 C_OPTION_CLASS constant number := 2;
36 C_PLANNING constant number := 3;
37 C_STANDARD constant number := 4;
38 C_PRODUCT_FAMILY constant number := 5;
39
40
41
42 /* Public Functions */
43
44 /* This function is not being used in anywhere.
45 function org(p_org_id in NUMBER) return VARCHAR2 IS
46 l_org VARCHAR2(240);
47 BEGIN
48
49 if p_org_id is NULL then
50 return 'Other';
51 end if;
52
53 -- DWK Replaced by the following sql stmt. Performance Tunned
54 -- select organization_name
55 -- into l_org
56 -- from org_organization_definitions
57 -- where organization_id = p_org_id;
58
59 select name
60 into l_org
61 from HR_ORGANIZATION_UNITS
62 where organization_id = p_org_id;
63
64 return l_org;
65
66 EXCEPTION when others then return NULL;
67
68 END org;
69 */
70
71 function item(p_item_id in NUMBER, p_org_id in NUMBER) return VARCHAR2 IS
72 l_item VARCHAR2(40);
73 BEGIN
74 if p_item_id is NULL then return 'Other'; end if;
75 if p_org_id is NULL then
76 select concatenated_segments
77 into l_item
78 from mtl_system_items_kfv
79 where inventory_item_id = p_item_id AND organization_id is NULL;
80 else
81 select concatenated_segments
82 into l_item
83 from mtl_system_items_kfv
84 where inventory_item_id = p_item_id AND organization_id = p_org_id;
85 end if;
86 return l_item;
87
88 EXCEPTION when others then return NULL;
89
90 END item;
91
92 function cust(p_cust_id in NUMBER) return VARCHAR2 IS
93 l_cust varchar2(50);
94 BEGIN
95 if p_cust_id is NULL then return 'Other'; end if;
96
97 --Bug 4585376 - RA_CUSTOMERS replaced by HZ_PARTIES and HZ_CUST_ACCOUNTS
98 select substrb(PARTY.PARTY_NAME,1,50) customer_name
99 into l_cust
100 from HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT
101 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
102 and CUST_ACCT.CUST_ACCOUNT_ID = p_cust_id;
103
104 return l_cust;
105
106 EXCEPTION when others then return NULL;
107
108 END cust;
109
110 function schn(p_schn_id in VARCHAR2) return VARCHAR2 IS
111 BEGIN
112 return NVL(p_schn_id, 'Other');
113
114 EXCEPTION when others then return NULL;
115
116 END schn;
117
118 function srep(p_srep_id in NUMBER, p_org_id in NUMBER) return VARCHAR2 IS
119 l_ret varchar2(240);
120 BEGIN
121 if p_srep_id is NULL then return 'Other'; end if;
122 select name
123 into l_ret
124 from ra_salesreps_all
125 where (salesrep_id = p_srep_id) AND
126 ((org_id is NULL) OR (org_id = p_org_id));
127 return l_ret;
128
129 EXCEPTION when others then return NULL;
130
131 END srep;
132
133 -- slightly modified version of edw source.
134 function uom_conv (uom_code varchar2,
135 item_id number) return number as
136
137 base_uom varchar2(3);
138 conv_rate number:=1;
139 l_master_org number;
140 l_master_uom varchar2(3);
141
142 cursor base_uom_code_conversion(p_item_id number, p_uom_code varchar2) is
143 select t.conversion_rate std_conv_rate
144 from mtl_uom_conversions t
145 where t.inventory_item_id in (p_item_id, 0)
146 and t.uom_code= p_uom_code
147 and nvl(t.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
148 and t.conversion_rate is not null
149 order by t.inventory_item_id desc;
150
151 begin
152
153 /*
154 ** Conversion between between two UOMS.
155 **
156 ** 1. The conversion always starts from the conversion defined, if exists,
157 ** for an specified item.
158 ** 2. If the conversion id not defined for that specific item, then the
159 ** standard conversion, which is defined for all items, is used.
160 */
161
162 /*
163 open base_uom_code_conversion(item_id, uom_code);
164 fetch base_uom_code_conversion into conv_rate;
165 close base_uom_code_conversion;
166 */
167
168 select to_number(parameter_value)
169 into l_master_org
170 from msd_setup_parameters
171 where parameter_name = 'MSD_MASTER_ORG';
172
173 select NVL(primary_uom_code,'Ea')
174 into l_master_uom
175 from mtl_system_items
176 where inventory_item_id = item_id
177 and organization_id = l_master_org;
178
179 conv_rate := inv_convert.inv_um_convert(item_id,NULL,NULL,uom_code,l_master_uom,NULL,NULL);
180
181 if (conv_rate = -99999) then
182 conv_rate := 1;
183 end if;
184
185 return conv_rate;
186
187
188 exception
189
190 when others then
191
192 return 1;
193
194 end uom_conv;
195
196 function get_item_cost(p_item_id in number, p_org_id in number) return number IS
197 l_ret number;
198 BEGIN
199 if p_item_id is null then return NULL; end if;
200 select list_price_per_unit
201 into l_ret
202 from mtl_system_items
203 where (p_item_id = inventory_item_id) and
204 ((organization_id = p_org_id) OR (organization_id is null));
205 return l_ret;
206
207 EXCEPTION when others then return NULL;
208
209 END get_item_cost;
210
211 function convert_global_amt(p_curr_code in varchar2, p_date in date) return number IS
212 l_ret number;
213 c_global_currency_code varchar2(40);
214 c_global_rate_type varchar2(40);
215 BEGIN
216
217 select parameter_value
218 into c_global_currency_code
219 from msd_setup_parameters
220 where parameter_name = 'MSD_CURRENCY_CODE';
221
222 select parameter_value
223 into c_global_rate_type
224 from msd_setup_parameters
225 where parameter_name = 'MSD_CONVERSION_TYPE';
226
227 if (p_curr_code = c_global_currency_code) then
228 l_ret := 1;
229 else
230 l_ret := GL_CURRENCY_API.get_rate_sql (
231 p_curr_code,
232 c_global_currency_code,
233 p_date,
234 c_global_rate_type
235 );
236 end if;
237 return l_ret;
238
239 EXCEPTION when others then return NULL;
240
241 END convert_global_amt;
242
243 function shipped_date(p_departure_id in number) return date IS
244 l_ret date;
245 BEGIN
246 if p_departure_id is null then return null; end if;
247 select actual_departure_date
248 into l_ret
249 from wsh_departures
250 where departure_id = p_departure_id;
251 return l_ret;
252
253 EXCEPTION when others then return NULL;
254
255 END shipped_date ;
256
257 function booked_date(p_header_id in number) return date IS
258 l_ret date;
259 BEGIN
260 -- The commented out code was used at one point; however, now OE's datamodel
261 -- has changed, they added the booked_date column to their base table
262 -- and it is just more convenient to go directly their. However, just in case
263 -- I'm saving the old code. mostrovs. 02/17/00
264 --
265 -- SELECT end_date
266 -- INTO l_ret
267 -- FROM wf_item_activity_statuses
268 -- WHERE item_type = OE_GLOBALS.G_WFI_HDR
269 -- AND item_key = p_header_id
270 -- AND process_activity IN (SELECT wpa.instance_id
271 -- FROM wf_process_activities wpa
272 -- WHERE wpa.activity_item_type = OE_GLOBALS.G_WFI_HDR
273 -- AND wpa.activity_name = 'BOOK_ORDER');
274 --
275 select booked_date
276 into l_ret
277 from oe_order_headers_all
278 where header_id = p_header_id;
279 return l_ret;
280
281 EXCEPTION when others then return NULL;
282
283 END Booked_Date;
284
285
286 function location(p_loc_id in number) return varchar2 IS
287 l_ret varchar2(240);
288 BEGIN
289 if p_loc_id is null then return 'Other'; end if;
290
291 --Bug 4585376 RA_CUSTOMERS, RA_ADDRESSES_ALL and RA_SITE_USES_ALL replaced by HZ_CUST_SITE_USES_ALL, HZ_CUST_ACCOUNTS, HZ_PARTIES and HZ_CUST_ACCT_SITES_ALL
292 select substrb(hp.PARTY_NAME,1,50) || '-' || csu.location
293 into l_ret
294 from HZ_CUST_SITE_USES_ALL csu,
295 HZ_CUST_ACCOUNTS ca,
296 HZ_PARTIES hp,
297 HZ_CUST_ACCT_SITES_ALL cas
298 where csu.site_use_id = p_loc_id
299 and csu.cust_acct_site_id = cas.cust_acct_site_id
300 and cas.cust_account_id = ca.cust_account_id
301 and ca.cust_account_id = hp.party_id;
302
303 return l_ret;
304
305 EXCEPTION when others then return NULL;
306
307 END location;
308
309 function Master_Organization return number is
310 x_master_org number;
311 x_product_group_type varchar2(1) ;
312 x_out boolean;
313 begin
314
315 begin
316
317 select fpg.MULTI_ORG_FLAG into x_product_group_type
318 from fnd_product_groups fpg
319 where fpg.product_group_type='Standard' ;
320
321 exception
322
323 when others then
324 x_product_group_type := 'Y' ;
325
326 end ;
327
328 /* Get the profile option MSD_MASTER_ORG */
329
330 x_master_org := fnd_profile.value('MSD_MASTER_ORG') ;
331
332 if (x_product_group_type = 'Y') then
333 if (x_master_org is NULL) then
334 select organization_id into x_master_org
335 from mtl_parameters
336 where organization_id = master_organization_id
337 and rownum < 2 ;
338 end if;
339
340 /* Single Master Organization OE Instance */
341 else
342 select organization_id into x_master_org
343 from mtl_parameters
344 where organization_id = master_organization_id
345 and rownum < 2 ;
346 end if;
347
348
349 return x_master_org ;
350
351
352 EXCEPTION when others then return NULL;
353
354 End Master_Organization ;
355
356 /* Bug# 4157588 */
357 function Item_Organization return varchar2 is
358 x_item_org varchar2(255);
359
360 begin
361
362 /* Get the profile option MSD_ITEM_ORG */
363
364 x_item_org := fnd_profile.value('MSD_ITEM_ORG') ;
365
366 return x_item_org;
367
368 exception
369
370 when others then return NULL;
371
372 End Item_Organization ;
373
374 function get_category_set_id return number is
375 x_cat_set_id number;
376 begin
377
378 /* Get the profile option MSD_CATEGORY_SET_NAME */
379
380 x_cat_set_id := fnd_profile.value('MSD_CATEGORY_SET_NAME') ;
381
382 return x_cat_set_id ;
383
384 exception
385
386 when others then return NULL;
387
388 end get_category_set_id;
389
390
391 function get_conversion_type return varchar2 is
392 x_conv_type varchar2(100);
393 begin
394
395 /* Get the profile option MSD_CONVERSION_TYPE */
396
397 x_conv_type := fnd_profile.value('MSD_CONVERSION_TYPE') ;
398
399 return x_conv_type;
400
401 exception
402
403 when others then return NULL;
404
405 end get_conversion_type;
406
407 function get_customer_attr return varchar2 is
408 x_cust_attr varchar2(100);
409 begin
410
411 /* Get the profile option MSD_CUSTOMER_ATTRIBUTE */
412
413 x_cust_attr := fnd_profile.value('MSD_CUSTOMER_ATTRIBUTE') ;
414
415 return x_cust_attr;
416
417 exception
418
419 when others then return NULL;
420
421 end get_customer_attr;
422
423
424
425 function get_null_pk return number IS
426 BEGIN
427 return NULL_VALUE_PK_CONST;
428 END;
429
430 function get_null_desc return VARCHAR2 IS
431 BEGIN
432 return get_dimension_desc('MSD_DIM_ALL_DESC','OTH');
433 END;
434
435 function get_all_scs_pk return number IS
436 BEGIN
437 return ALL_CHANNELS_PK_CONST ;
438 END;
439
440 function get_all_scs_desc return VARCHAR2 IS
441 BEGIN
442 return get_dimension_desc('MSD_DIM_ALL_DESC','CHN');
443 END;
444
445 function get_all_geo_pk return number IS
446 BEGIN
447 return ALL_GEOGRAPHY_PK_CONST ;
448 END;
449
450 function get_all_geo_desc return VARCHAR2 IS
451 BEGIN
452 return get_dimension_desc('MSD_DIM_ALL_DESC','GEO');
453 END;
454
455 function get_all_org_pk return number IS
456 BEGIN
457 return ALL_ORGANIZATIONS_PK_CONST ;
458 END;
459
460 function get_all_org_desc return VARCHAR2 IS
461 BEGIN
462 return get_dimension_desc('MSD_DIM_ALL_DESC','ORG');
463 END;
464
465 function get_all_prd_pk return number IS
466 BEGIN
467 return ALL_PRODUCTS_PK_CONST ;
468 END;
469
470 function get_all_prd_desc return VARCHAR2 IS
471 BEGIN
472 return get_dimension_desc('MSD_DIM_ALL_DESC','PRD');
473 END;
474
475 function get_all_rep_pk return number IS
476 BEGIN
477 return ALL_SALESREP_PK_CONST ;
478 END;
479
480 function get_all_rep_desc return VARCHAR2 IS
481 BEGIN
482 return get_dimension_desc('MSD_DIM_ALL_DESC','REP');
483 END;
484
485 function get_all_dcs_pk return number IS
486 BEGIN
487 return ALL_DEMANDCLASS_PK_CONST ;
488 END;
489
490 function get_all_dcs_desc return VARCHAR2 IS
491 BEGIN
492 return get_dimension_desc('MSD_DIM_ALL_DESC','DCS');
493 END;
494
495
496 FUNCTION get_dimension_desc(p_type varchar2,
497 p_code varchar2) return VARCHAR2 IS
498
499
500 CURSOR c_dim_desc(l_type varchar2, l_code varchar2) IS
501 select meaning from fnd_lookup_values
502 where lookup_type = l_type
503 and language = userenv('LANG')
504 and lookup_code = l_code;
505
506 l_dim_desc varchar2(240) := NULL;
507
508 BEGIN
509
510 OPEN c_dim_desc(p_type, p_code);
511 FETCH c_dim_desc INTO l_dim_desc;
512 CLOSE c_dim_desc;
513
514 IF l_dim_desc is NULL THEN
515 RETURN p_code;
516 ELSE
517 RETURN l_dim_desc;
518 END IF;
519
520 END get_dimension_desc;
521
522
523
524 FUNCTION IS_ITEM_OPTIONAL_FOR_LVL(p_component_item_id in NUMBER) RETURN NUMBER IS
525
526 CURSOR c_optional IS
527 select 1
528 from
529 msd_app_instance_orgs morg,
530 bom_bill_of_materials bbm,
531 mtl_system_items msi, -- Parent
532 bom_inventory_components bic
533 where
534 bic.bill_sequence_id = bbm.bill_sequence_id
535 and bbm.organization_id = morg.organization_id
536 and msi.organization_id = bbm.organization_id
537 and msi.inventory_item_id = bbm.assembly_item_id
538 and msi.bom_item_type not in (C_PLANNING, C_STANDARD, C_PRODUCT_FAMILY)
539 and bic.optional = C_YES
540 and ( msi.bom_item_type = 2 or
541 ( msi.bom_item_type = 1 and msi.ato_forecast_control in (1, 2) )
542 )
543 and bic.component_item_id = p_component_item_id;
544
545 l_count NUMBER := 0;
546
547
548 BEGIN
549
550 IF p_component_item_id is NOT NULL THEN
551 OPEN c_optional;
552 FETCH c_optional INTO l_count;
553 CLOSE c_optional;
554 END IF;
555
556 IF l_count = 0 THEN
557 return C_NO;
558 ELSE
559 return C_YES;
560 END IF;
561
562 END IS_ITEM_OPTIONAL_FOR_LVL;
563
564
565 /* Bug# 4157588 */
566 FUNCTION IS_ITEM_OPTIONAL_FOR_LVL(p_component_item_id in NUMBER, p_org_id in NUMBER) RETURN NUMBER IS
567
568 CURSOR c_optional IS
569 select 1
570 from
571 msd_app_instance_orgs morg,
572 bom_bill_of_materials bbm,
573 mtl_system_items msi, -- Parent
574 bom_inventory_components bic
575 where
576 bic.bill_sequence_id = bbm.bill_sequence_id
577 and bbm.organization_id = morg.organization_id
578 and msi.organization_id = bbm.organization_id
579 and msi.inventory_item_id = bbm.assembly_item_id
580 and msi.bom_item_type not in (C_PLANNING, C_STANDARD, C_PRODUCT_FAMILY)
581 and bic.optional = C_YES
582 and ( msi.bom_item_type = 2 or
583 ( msi.bom_item_type = 1 and msi.ato_forecast_control in (1, 2) )
584 )
585 and bic.component_item_id = p_component_item_id
586 and bbm.organization_id = p_org_id;
587
588 l_count NUMBER := 0;
589
590
591 BEGIN
592
593 IF p_component_item_id is NOT NULL THEN
594 OPEN c_optional;
595 FETCH c_optional INTO l_count;
596 CLOSE c_optional;
597 END IF;
598
599 IF l_count = 0 THEN
600 return C_NO;
601 ELSE
602 return C_YES;
603 END IF;
604
605 END IS_ITEM_OPTIONAL_FOR_LVL;
606
607
608
609 /* We only execute this funciton when items in OM has
610 ato_forecast_control = NONE
611 First, we check whether the given component is
612 optional component in the BOM or not.
613 IF so, then find the parent's component sequence id
614 and then check whether the parent is either
615 (optional with forecast control = none or
616 consume and drive)
617 */
618
619 FUNCTION IS_ITEM_OPTIONAL_FOR_FACT(p_component_item_id in NUMBER,
620 p_component_sequence_id in NUMBER,
621 p_parent_line_id in NUMBER) RETURN NUMBER IS
622
623
624 l_component_seq_id NUMBER := p_component_sequence_id;
625 l_parent_component_seq_id NUMBER := NULL;
626
627
628 CURSOR c_optional IS
629 select 1
630 from
631 msd_app_instance_orgs morg,
632 bom_bill_of_materials bbm,
633 mtl_system_items msi, -- Parent
634 bom_inventory_components bic
635 where
636 bic.component_sequence_id = l_component_seq_id
637 and bic.bill_sequence_id = bbm.bill_sequence_id
638 and bbm.organization_id = morg.organization_id
639 and msi.organization_id = bbm.organization_id
640 and msi.inventory_item_id = bbm.assembly_item_id
641 and msi.bom_item_type not in (C_PLANNING, C_STANDARD, C_PRODUCT_FAMILY)
642 and bic.optional = C_YES;
643
644
645
646 CURSOR c_parent_item IS
647 select component_sequence_id
648 from oe_order_lines_all
649 where line_id = p_parent_line_id;
650
651
652 /* The parent of optional item has to be
653 either consume and drive or
654 none with optional = YES */
655
656 CURSOR c_parent_optional IS
657 select 1
658 from
659 msd_app_instance_orgs morg,
660 bom_bill_of_materials bbm,
661 mtl_system_items msi,
662 bom_inventory_components bic
663 where
664 bic.bill_sequence_id = bbm.bill_sequence_id
665 and bbm.organization_id = morg.organization_id
666 and msi.organization_id = bbm.organization_id
667 and msi.inventory_item_id = bic.component_item_id
668 and
669 ( (msi.ato_forecast_control = 3 and bic.optional = C_YES)
670 or
671 msi.ato_forecast_control in (1, 2)
672 )
673 and bic.component_sequence_id = l_component_seq_id;
674
675 l_count NUMBER := 0;
676
677 BEGIN
678
679
680 IF l_component_seq_id IS NOT NULL THEN
681 OPEN c_optional;
682 FETCH c_optional INTO l_count;
683 CLOSE c_optional;
684 IF (l_count <> 0 and p_parent_line_id is not null) THEN
685
686 IF (p_parent_line_id is not null) THEN
687 OPEN c_parent_item;
688 FETCH c_parent_item INTO l_parent_component_seq_id;
689 CLOSE c_parent_item;
690
691 l_component_seq_id := l_parent_component_seq_id;
692
693 IF l_component_seq_id IS NOT NULL THEN
694 OPEN c_parent_optional;
695 FETCH c_parent_optional INTO l_count;
696 CLOSE c_parent_optional;
697 END IF;
698 END IF;
699 ELSE /* if there was no parent_line_id then return NO */
700 l_count := 0;
701 END IF;
702 END IF;
703
704 IF l_count = 0 THEN
705 return C_NO;
706 ELSE
707 return C_YES;
708 END IF;
709
710 END IS_ITEM_OPTIONAL_FOR_FACT;
711
712
713 /*
714 FUNCTION FIND_PARENT_FOR_PTO( p_comp_seq_id IN NUMBER,
715 p_link_to_line_id IN NUMBER,
716 p_include_class IN VARCHAR2) RETURN NUMBER IS
717
718
719 CURSOR c_parent IS
720 SELECT assemb.assembly_item_id, msi.bom_item_type
721 FROM bom_bill_of_materials assemb,
722 bom_inventory_components comp,
723 mtl_system_items msi
724 WHERE
725 assemb.bill_sequence_id = comp.bill_sequence_id
726 and comp.component_sequence_id = p_comp_seq_id
727 and assemb.assembly_item_id = msi.inventory_item_id
728 and assemb.organization_id = msi.organization_id;
729
730 l_parent_id NUMBER := NULL;
731 l_bom_item_type NUMBER := NULL;
732
733 BEGIN
734
735 OPEN c_parent;
736 FETCH c_parent INTO l_parent_id, l_bom_item_type;
737 CLOSE c_parent;
738
739 IF (l_parent_id is not null AND l_bom_item_type is not null) THEN
740
741 IF p_include_class = 'N' THEN
742
743 IF l_bom_item_type = C_OPTION_CLASS THEN
744 l_parent_id := FIND_PARENT_ITEM(p_link_to_line_id, p_include_class);
745 END IF;
746
747 END IF;
748
749 return l_parent_id;
750
751 ELSE
752
753 return -1;
754
755 END IF;
756
757
758 END FIND_PARENT_FOR_PTO;
759
760 */
761
762
763
764 FUNCTION FIND_PARENT_ITEM (p_link_to_line_id in NUMBER,
765 p_include_class in varchar2) RETURN NUMBER IS
766
767
768 l_line_id NUMBER := p_link_to_line_id;
769
770 CURSOR c_parent_id IS
771 select
772 inventory_item_id, item_type_code, link_to_line_id, ato_line_id
773 from oe_order_lines_all
774 where line_id = l_line_id;
775
776 l_item_type_code VARCHAR2(10);
777 l_parent_item_id NUMBER := NULL;
778 l_link_to_line_id NUMBER := 0;
779 l_ato_line_id NUMBER := 0;
780 b_loop BOOLEAN := TRUE;
781
782 BEGIN
783
784 WHILE (b_loop) LOOP
785 IF (p_include_class = 'Y') THEN
786 b_loop := FALSE;
787 END IF;
788
789 IF (l_line_id is not NULL) THEN
790 OPEN c_parent_id;
791 FETCH c_parent_id INTO l_parent_item_id, l_item_type_code, l_link_to_line_id, l_ato_line_id;
792 CLOSE c_parent_id;
793
794 IF (l_item_type_code = 'CLASS' and
795 (nvl(l_ato_line_id, -999) <> l_line_id) ) THEN
796 l_line_id := l_link_to_line_id;
797 ELSE
798 b_loop := FALSE;
799 END IF;
800 ELSE
801 b_loop := FALSE;
802 END IF;
803 END LOOP;
804
805 return l_parent_item_id;
806
807 END FIND_PARENT_ITEM;
808
809 FUNCTION IS_PF_FORECASTABLE (p_org_id in NUMBER,
810 p_inventory_item_id in NUMBER,
811 p_product_family_item_id in NUMBER,
812 p_check_optional in NUMBER,
813 pf_available in NUMBER default 1) RETURN NUMBER IS
814
815
816 CURSOR c_count IS
817 SELECT
818 count(1)
819 FROM
820 mtl_system_items parent
821 WHERE
822 parent.organization_id = p_org_id and
823 parent.inventory_item_id = p_product_family_item_id and
824 parent.bom_item_type = 5 and
825 nvl(parent.ato_forecast_control, 3) <> 3;
826
827 CURSOR c_count1 IS
828 SELECT
829 count(1)
830 FROM
831 mtl_system_items pf,
832 mtl_system_items parent
833 WHERE
834 pf.organization_id = p_org_id and
835 pf.inventory_item_id = p_inventory_item_id and
836 parent.organization_id = p_org_id and
837 parent.inventory_item_id = pf.product_family_item_id and
838 parent.bom_item_type = 5 and
839 nvl(parent.ato_forecast_control, 3) <> 3 ;
840
841 l_count NUMBER := 0;
842 l_optional NUMBER := C_NO;
843
844 BEGIN
845 IF pf_available = 1 then
846 OPEN c_count;
847 FETCH c_count INTO l_count;
848 IF c_count%ISOPEN THEN
849 CLOSE c_count;
850 END IF;
851 else
852 OPEN c_count1;
853 FETCH c_count1 INTO l_count;
854 IF c_count1%ISOPEN THEN
855 CLOSE c_count1;
856 END IF;
857 end if;
858
859 IF l_count > 0 THEN
860 return C_YES;
861 ELSE
862 IF p_check_optional = C_YES THEN
863 l_optional := IS_ITEM_OPTIONAL_FOR_LVL(p_inventory_item_id);
864 END IF;
865
866 IF l_optional = C_YES THEN
867 return C_YES;
868 ELSE
869 return C_NO;
870 END IF;
871 END IF;
872
873 EXCEPTION
874 when others then
875 return NULL;
876
877 END IS_PF_FORECASTABLE;
878
879
880
881 FUNCTION IS_PRODUCT_FAMILY_FORECASTABLE (p_org_id in NUMBER,
882 p_inventory_item_id in NUMBER,
883 p_check_optional in NUMBER) RETURN NUMBER IS
884
885
886 CURSOR c_count IS
887 SELECT
888 count(1)
889 FROM
890 mtl_system_items parent,
891 bom_inventory_components bic,
892 bom_bill_of_materials bom
893 WHERE
894 parent.bom_item_type = 5 and
895 parent.organization_id = bom.organization_id and
896 bom.ASSEMBLY_ITEM_ID = parent.inventory_item_id and
897 bom.bill_sequence_id = bic.bill_sequence_id and
898 bic.component_item_id = p_inventory_item_id and
899 bom.organization_id = p_org_id and
900 nvl(parent.ato_forecast_control, 3) <> 3;
901
902 l_count NUMBER := 0;
903 l_optional NUMBER := C_NO;
904
905 BEGIN
906
907 OPEN c_count;
908 FETCH c_count INTO l_count;
909 IF c_count%ISOPEN THEN
910 CLOSE c_count;
911 END IF;
912
913 IF l_count > 0 THEN
914 return C_YES;
915 ELSE
916 IF p_check_optional = C_YES THEN
917 l_optional := IS_ITEM_OPTIONAL_FOR_LVL(p_inventory_item_id);
918 END IF;
919
920 IF l_optional = C_YES THEN
921 return C_YES;
922 ELSE
923 return C_NO;
924 END IF;
925 END IF;
926
927 EXCEPTION
928 when others then
929 return NULL;
930
931 END IS_PRODUCT_FAMILY_FORECASTABLE;
932
933
934 FUNCTION CONFIG_ITEM_EXISTS ( p_header_id IN NUMBER,
935 p_org_id IN NUMBER,
936 p_ato_line_id IN NUMBER) RETURN NUMBER IS
937
938 CURSOR c_config_model (p_header_id IN NUMBER,
939 p_org_id IN NUMBER,
940 p_ato_line_id IN NUMBER) IS
941 select count(1)
942 from mtl_system_items itm
943 where inventory_item_id = (select inventory_item_id
944 from oe_order_lines_all l
945 where l.item_type_code = 'CONFIG'
946 and l.header_id = p_header_id
947 and l.org_id = p_org_id
948 and l.ato_line_id = p_ato_line_id )
949 and itm.organization_id = p_org_id
950 and nvl(itm.ato_forecast_control, 3) <> 3
951 and itm.base_item_id is not null;
952
953 l_item_count NUMBER := 0;
954
955 BEGIN
956
957 IF p_header_id is NOT NULL THEN
958 OPEN c_config_model (p_header_id,
959 p_org_id,
960 p_ato_line_id);
961 FETCH c_config_model INTO l_item_count;
962 CLOSE c_config_model;
963
964 IF nvl(l_item_count, 0) = 1 THEN
965 return C_YES;
966 ELSE
967 return C_NO;
968 END IF;
969 ELSE
970 return C_NO;
971 END IF;
972
973 EXCEPTION
974 WHEN OTHERS THEN
975 return C_NO;
976
977 END CONFIG_ITEM_EXISTS;
978
979
980
981 FUNCTION on_hold(p_header_id in NUMBER, p_line_id in NUMBER) return VARCHAR2
982 IS
983 l_on_hold VARCHAR2(1) := 'N';
984
985 Cursor on_hold_flag IS
986 select decode(min(nvl(released_flag,'Y')),'N','Y','N')
987 from oe_order_holds_all
988 where header_id = p_header_id
989 AND nvl(line_id,p_line_id) = p_line_id
990 group by header_id,nvl(line_id,p_line_id);
991
992 BEGIN
993
994 OPEN on_hold_flag;
995 FETCH on_hold_flag into l_on_hold;
996
997 CLOSE on_hold_flag;
998
999 return l_on_hold;
1000
1001 EXCEPTION
1002 when no_data_found then return l_on_hold;
1003 when others then return NULL;
1004
1005 END on_hold;
1006
1007 function get_zone_attr return varchar2 is
1008 x_zone_attr varchar2(100);
1009 x_wsh_application_id number := 665;
1010 x_end_user_column_name varchar2(100) := 'Zone Usage';
1011 x_des_fname varchar2(100) := 'WSH_REGIONS';
1012
1013 cursor c1 is
1014 select application_column_name
1015 from fnd_descr_flex_column_usages
1016 where end_user_column_name = x_end_user_column_name
1017 and descriptive_flexfield_name = x_des_fname
1018 and application_id = x_wsh_application_id;
1019
1020 begin
1021 open c1;
1022 fetch c1 into x_zone_attr;
1023 close c1;
1024
1025 return x_zone_attr;
1026
1027 exception
1028 when others then return NULL;
1029 end get_zone_attr;
1030
1031 /* Usability Enhancements. Bug # 3509147. This function sets the value of profile MSD_CUSTOMER_ATTRIBUTE to NONE
1032 if collecting for the first time */
1033 function set_customer_attr( p_profile_name IN VARCHAR2,
1034 p_profile_value IN VARCHAR2,
1035 p_profile_Level IN VARCHAR2) return number is
1036 x_ret_value boolean;
1037 begin
1038 x_ret_value := fnd_profile.save(p_profile_name,p_profile_value,p_profile_Level);
1039 if x_ret_value then
1040 return 0;
1041 else
1042 return 2;
1043 end if;
1044 end;
1045
1046 FUNCTION get_sr_zone_pk ( p_location_id IN NUMBER,
1047 p_zone_attr IN VARCHAR2) return number IS
1048
1049 cursor c1 is
1050 select wrv.region_id
1051 from wsh_region_locations wrl,
1052 wsh_zone_regions wzr,
1053 wsh_regions_v wrv
1054 where wrl.location_id = p_location_id
1055 and wrl.region_id = wzr.region_id
1056 and wzr.parent_region_id = wrv.region_id
1057 and wrv.region_type = 10
1058 and decode(nvl(lower(p_zone_attr), '2'), 'attribute1', wrv.attribute1,
1059 'attribute2', wrv.attribute2, 'attribute3',wrv.attribute3, 'attribute4',
1060 wrv.attribute4, 'attribute5', wrv.attribute5, 'attribute6', wrv.attribute6,
1061 'attribute7', wrv.attribute7, 'attribute8', wrv.attribute8, 'attribute9',
1062 wrv.attribute9, 'attribute10', wrv.attribute10, 'attribute11', wrv.attribute11,
1063 'attribute12', wrv.attribute12, 'attribute13', wrv.attribute13, 'attribute14',
1064 wrv.attribute14, 'attribute15', wrv.attribute15, '2') = '1'
1065 order by wrv.region_id;
1066
1067 x_region_id number:= null;
1068
1069 begin
1070
1071 if ((p_location_id is null) or (p_zone_attr is null)) then
1072 return -777;
1073 else
1074 open c1;
1075 fetch c1 into x_region_id;
1076 close c1;
1077
1078 if (x_region_id is not null) then
1079 return x_region_id;
1080 else
1081 return -777;
1082 end if;
1083 end if;
1084 EXCEPTION
1085 when no_data_found then return -777;
1086 end get_sr_zone_pk;
1087
1088 /* This function is used in MSD_SR_SERVICE_PARTS_USAGE_V.
1089 * It is required in flow Depot Repair WIP, because
1090 * a repair job can be submitted for several orders
1091 * and vice versa.
1092 *
1093 * Arg : p_txn_source_id is the material transaction source id
1094 */
1095 FUNCTION get_service_req_org_id (p_txn_source_id IN NUMBER) return NUMBER is
1096 x_org_id number;
1097
1098 cursor c1 is
1099 select organization_id
1100 from csd_repair_job_xref crjx
1101 where wip_entity_id = p_txn_source_id
1102 order by repair_job_xref_id desc;
1103
1104 begin
1105
1106 if (p_txn_source_id is null) then
1107 return -777;
1108 else
1109 open c1;
1110 fetch c1 into x_org_id;
1111 close c1;
1112
1113 if (x_org_id is not null) then
1114 return x_org_id;
1115 else
1116 return -777;
1117 end if;
1118 end if;
1119 EXCEPTION
1120 when no_data_found then return -777;
1121 end get_service_req_org_id;
1122
1123 /* This function is used in MSD_SR_SERVICE_PARTS_USAGE_V.
1124 * It is required in flow Depot Repair WIP, because
1125 * a repair job can be submitted for several orders
1126 * and vice versa.
1127 *
1128 * Arg : p_txn_source_id is the material transaction source id
1129 * Arg : p_cust_filter is the profile value for Customer Attribute
1130 */
1131
1132 FUNCTION get_service_req_acct_id (p_txn_source_id IN NUMBER,
1133 p_cust_filter in varchar2) return NUMBER is
1134 x_rpr_line_id number;
1135 x_acct_id number;
1136
1137 cursor c1 is
1138 select repair_line_id
1139 from csd_repair_job_xref
1140 where wip_entity_id = p_txn_source_id
1141 order by repair_job_xref_id desc;
1142
1143 cursor c2 (p_repair_line_id in number) is
1144 --Bug 4585376 RA_CUSTOMERS replaced by HZ_CUST_ACCOUNTS
1145 select decode(decode(nvl(lower(p_cust_filter), '1'), '1', '1', 'attribute1',
1146 cust.attribute1, 'attribute2', cust.attribute2, 'attribute3', cust.attribute3,
1147 'attribute4', cust.attribute4, 'attribute5', cust.attribute5, 'attribute6',
1148 cust.attribute6, 'attribute7', cust.attribute7, 'attribute8', cust.attribute8,
1149 'attribute9', cust.attribute9, 'attribute10', cust.attribute10, 'attribute11',
1150 cust.attribute11, 'attribute12', cust.attribute12, 'attribute13',cust.attribute13,
1151 'attribute14', cust.attribute14, 'attribute15', cust.attribute15, '2'), '1',
1152 nvl(cia.account_id, msd_sr_util.get_null_pk), msd_sr_util.get_null_pk)
1153 from cs_incidents_all_b cia,
1154 hz_cust_accounts cust,
1155 csd_repairs crp
1156 where crp.repair_line_id = p_repair_line_id
1157 and cia.incident_id = crp.incident_id
1158 and cust.cust_account_id = cia.account_id;
1159
1160 begin
1161
1162 if (p_txn_source_id is null) then
1163 return -777;
1164 else
1165 open c1;
1166 fetch c1 into x_rpr_line_id;
1167 close c1;
1168
1169 if (x_rpr_line_id is not null) then
1170 open c2(x_rpr_line_id);
1171 fetch c2 into x_acct_id;
1172 close c2;
1173
1174 if (x_acct_id is null) then
1175 return -777;
1176 else
1177 return x_acct_id;
1178 end if;
1179 else
1180 return -777;
1181 end if;
1182 end if;
1183 EXCEPTION
1184 when no_data_found then return -777;
1185 end get_service_req_acct_id;
1186
1187 /* This function is used in MSD_SR_SERVICE_PARTS_USAGE_V.
1188 * It is required in flow Depot Repair WIP, because
1189 * a repair job can be submitted for several orders
1190 * and vice versa.
1191 *
1192 * Arg : p_txn_source_id is the material transaction source id
1193 * Arg : p_zone_filter is the profile value for Customer Attribute
1194 */
1195
1196 FUNCTION get_service_req_zone_id (p_txn_source_id IN NUMBER,
1197 p_zone_filter in VARCHAR2) return NUMBER is
1198 x_rpr_line_id number;
1199 x_zone_id number;
1200
1201 cursor c1 is
1202 select repair_line_id
1203 from csd_repair_job_xref
1204 where wip_entity_id = p_txn_source_id
1205 order by repair_job_xref_id desc;
1206
1207 cursor c2 (p_repair_line_id in number) is
1208 select get_sr_zone_pk(hps.location_id, p_zone_filter)
1209 from cs_incidents_all_b cia,
1210 hz_party_sites hps,
1211 csd_repairs crp
1212 where crp.repair_line_id = p_repair_line_id
1213 and cia.incident_id = crp.incident_id
1214 and cia.install_site_id = hps.party_site_id (+);
1215
1216 begin
1217
1218 if (p_txn_source_id is null) then
1219 return -777;
1220 else
1221 open c1;
1222 fetch c1 into x_rpr_line_id;
1223 close c1;
1224
1225 if (x_rpr_line_id is not null) then
1226 open c2(x_rpr_line_id);
1227 fetch c2 into x_zone_id;
1228 close c2;
1229
1230 if (x_zone_id is null) then
1231 return -777;
1232 else
1233 return x_zone_id;
1234 end if;
1235 else
1236 return -777;
1237 end if;
1238 end if;
1239 EXCEPTION
1240 when no_data_found then return -777;
1241 end get_service_req_zone_id;
1242
1243 FUNCTION is_txn_depot_repair(p_txn_source_id IN NUMBER) return VARCHAR2 is
1244
1245 x_row_num number;
1246 cursor c1 is
1247 select 1
1248 from csd_repair_job_xref crjx
1249 where wip_entity_id = p_txn_source_id
1250 order by repair_job_xref_id;
1251
1252 begin
1253
1254 if (p_txn_source_id is null) then
1255 return 'N';
1256 else
1257 open c1;
1258 fetch c1 into x_row_num;
1259 close c1;
1260
1261 if (x_row_num is not null) then
1262 return 'Y';
1263 else
1264 return 'N';
1265 end if;
1266 end if;
1267 EXCEPTION
1268 when no_data_found then return 'N';
1269 end is_txn_depot_repair;
1270
1271 /* This function has been added for the view MSD_SR_SALES_FCST_V to fix bug#3785195 which is forward port of Bug#3733193 */
1272 /* This function returns the customer_id for a given party_id */
1273 /* arg : p_party_id is the party_id of the customer */
1274
1275 FUNCTION get_customer_id( p_party_id IN NUMBER) return NUMBER is
1276
1277 x_customer_id number ;
1278
1279
1280 cursor c_customer_id is
1281 --Bug 4585376 RA_CUSTOMERS replaced by HZ_CUST_ACCOUNTS
1282 select decode( decode(nvl(lower(filtercust.parameter_value), '1'), '1', '1',
1283 'attribute1', cust.attribute1,
1284 'attribute2', cust.attribute2,
1285 'attribute3', cust.attribute3,
1286 'attribute4', cust.attribute4,
1287 'attribute5', cust.attribute5,
1288 'attribute6', cust.attribute6,
1289 'attribute7', cust.attribute7,
1290 'attribute8', cust.attribute8,
1291 'attribute9', cust.attribute9,
1292 'attribute10', cust.attribute10,
1293 'attribute11', cust.attribute11,
1294 'attribute12', cust.attribute12,
1295 'attribute13',cust.attribute13,
1296 'attribute14', cust.attribute14,
1297 'attribute15', cust.attribute15, '2')
1298 , '1', nvl(cust.cust_account_id,msd_sr_util.get_null_pk), msd_sr_util.get_null_pk)
1299 from
1300 hz_cust_accounts cust,
1301 (select parameter_value from msd_setup_parameters where parameter_name = 'MSD_CUSTOMER_ATTRIBUTE') filtercust
1302 where cust.party_id = p_party_id
1303 order by cust.cust_account_id ASC;
1304
1305
1306 BEGIN
1307
1308 open c_customer_id ;
1309 fetch c_customer_id into x_customer_id ;
1310 close c_customer_id ;
1311
1312 if( x_customer_id is not null) then
1313 return x_customer_id ;
1314 else
1315 return NULL_VALUE_PK_CONST ;
1316 end if ;
1317
1318 EXCEPTION
1319 when others then return NULL_VALUE_PK_CONST ;
1320 end get_customer_id ;
1321
1322 /* jarorad */
1323 FUNCTION dp_enabled_item (p_inventory_item_id in NUMBER,
1324 p_organization_id in NUMBER)
1325 RETURN NUMBER
1326 is
1327 lv_dp_enabled_item number := 0;
1328 lv_mrp_planning_code number := NULL;
1329 lv_pick_components_flag varchar2(1) := to_char(NULL);
1330
1331 BEGIN
1332
1333 select mrp_planning_code,pick_components_flag
1334 into lv_mrp_planning_code,lv_pick_components_flag
1335 from mtl_system_items_kfv
1336 where inventory_item_id = p_inventory_item_id
1337 and organization_id = p_organization_id;
1338
1339 If ( lv_mrp_planning_code <> 6 OR (lv_mrp_planning_code = 6 and lv_pick_components_flag ='Y')) THEN
1340 lv_dp_enabled_item := C_YES;
1341 else
1342 lv_dp_enabled_item := C_NO;
1343 end if;
1344
1345 return lv_dp_enabled_item;
1346
1347 EXCEPTION
1348 WHEN OTHERS THEN
1349 RETURN C_YES;
1350
1351 END dp_enabled_item;
1352 /* jarorad */
1353
1354 /* vinekuma */
1355 /* The following Functions are used in Liability View */
1356 function get_all_sup_pk return number
1357 IS
1358 BEGIN
1359 return ALL_SUPPLIER_PK_CONST ;
1360 END;
1361
1362 function get_all_sup_desc return varchar2
1363 IS
1364 BEGIN
1365 return get_dimension_desc('MSD_DIM_ALL_DESC','SUP');
1366 END;
1367
1368 function get_all_auth_pk return number
1369 IS
1370 BEGIN
1371 return ALL_AUTHORIZATION_PK_CONST ;
1372 END;
1373
1374
1375 function get_all_auth_desc return varchar2
1376 IS
1377 BEGIN
1378 return get_dimension_desc('MSD_DIM_ALL_DESC','AUTH');
1379 END;
1380 /* vinekuma */
1381
1382 FUNCTION get_onhand_quantity(
1383 p_organization_id in number,
1384 p_inventory_item_id in number,
1385 p_transaction_date in date
1386 ) return number is
1387
1388 /* Bug 12901216 */
1389 /*
1390 cursor c2 (p_organization_id IN number, p_inventory_item_id IN NUMBER,p_transaction_date IN DATE)
1391 IS
1392 SELECT c_onhand_qty
1393 FROM (select fact.item_org_id,
1394 sum(decode(cal.report_date, p_transaction_date, onhand_qty, null)) c_onhand_qty
1395 from opi_inv_val_sum_mv fact,
1396 fii_time_rpt_struct_v cal
1397 where fact.time_id = cal.time_id
1398 and fact.item_org_id = (p_inventory_item_id||'-'||p_organization_id)
1399 and fact.organization_id = p_organization_id
1400 and fact.aggregation_level_flag = 0
1401 and cal.report_date = p_transaction_date
1402 and bitand(cal.record_type_id, 1143) = cal.record_type_id
1403 group by fact.item_org_id );
1404
1405 l_onhand_qty number := 0;
1406
1407 Begin
1408
1409 open c2 (p_organization_id, p_inventory_item_id, p_transaction_date);
1410 fetch c2 into l_onhand_qty;
1411 close c2;
1412
1413 return l_onhand_qty;
1414
1415 EXCEPTION when others then return NULL;
1416 */
1417
1418 begin
1419
1420 return null;
1421
1422 End get_onhand_quantity;
1423
1424 function get_suppliers_pk return number IS
1425 BEGIN
1426 return SUPPLIERS_PK_CONST ;
1427 END;
1428
1429 function get_suppliers_desc return VARCHAR2 IS
1430 BEGIN
1431 return get_dimension_desc('MSD_DIM_ALL_DESC','SUPP');
1432 END;
1433
1434 function get_internal_customers_desc return VARCHAR2 is
1435 BEGIN
1436 return get_dimension_desc('MSD_DIM_ALL_DESC','ICUS');
1437 END;
1438
1439 FUNCTION get_eol_category_set_id
1440 RETURN NUMBER
1441 IS
1442 x_cat_set_id number;
1443 BEGIN
1444 /* Get the profile option MSD_EOL_CATEGORY_SET_NAME */
1445 x_cat_set_id := fnd_profile.value('MSD_EOL_CATEGORY_SET_NAME') ;
1446
1447 RETURN x_cat_set_id ;
1448
1449 EXCEPTION
1450 WHEN OTHERS THEN RETURN NULL;
1451 END get_eol_category_set_id;
1452
1453 /* Bug# 5367784 */
1454 /* This function is used in Zone hierarchy views
1455 * to return Source Level Value Pks for Customer Zone Level Values.
1456 */
1457
1458 FUNCTION get_sr_custzone_pk ( p_location_id IN NUMBER,
1459 p_customer_id IN NUMBER,
1460 p_zone_attr IN VARCHAR2) RETURN VARCHAR2 is
1461
1462 l_sql_stmt varchar2(2000);
1463
1464 x_region_id number:= null;
1465
1466 begin
1467
1468 if ((p_location_id is null) or (p_zone_attr is null)) then
1469 return msd_sr_util.get_null_pk;
1470 else
1471
1472 l_sql_stmt := ' select wrv.region_id ' ||
1473 ' from wsh_region_locations wrl, ' ||
1474 ' wsh_zone_regions wzr, ' ||
1475 ' wsh_regions_v wrv ' ||
1476 ' where wrl.location_id = ''' || p_location_id || '''' ||
1477 ' and wrl.region_id = wzr.region_id ' ||
1478 ' and wzr.parent_region_id = wrv.region_id ' ||
1479 ' and wrv.region_type = 10 ' ||
1480 ' and decode(nvl(lower( ''' || p_zone_attr || ''' ), ''2''), ''attribute1'', wrv.attribute1, ' ||
1481 ' ''attribute2'', wrv.attribute2, ''attribute3'',wrv.attribute3, ''attribute4'', ' ||
1482 ' wrv.attribute4, ''attribute5'', wrv.attribute5, ''attribute6'', wrv.attribute6, ' ||
1483 ' ''attribute7'', wrv.attribute7, ''attribute8'', wrv.attribute8, ''attribute9'', ' ||
1484 ' wrv.attribute9, ''attribute10'', wrv.attribute10, ''attribute11'', wrv.attribute11, ' ||
1485 ' ''attribute12'', wrv.attribute12, ''attribute13'', wrv.attribute13, ''attribute14'', ' ||
1486 ' wrv.attribute14, ''attribute15'', wrv.attribute15, ''2'') = ''1'' ' ||
1487 ' order by wrv.region_id';
1488
1489 execute immediate l_sql_stmt into x_region_id;
1490
1491 if (x_region_id is not null) then
1492 return to_char(p_customer_id) || ':' || to_char(x_region_id);
1493 else
1494 return msd_sr_util.get_null_pk;
1495 end if;
1496 end if;
1497 EXCEPTION
1498 when no_data_found then return msd_sr_util.get_null_pk;
1499 end get_sr_custzone_pk;
1500
1501 /* This function is used in Zone hierarchy views
1502 * to return description for Customer Zone Level Values.
1503 */
1504
1505 FUNCTION get_sr_custzone_desc ( p_location_id IN NUMBER,
1506 p_customer_name IN VARCHAR2,
1507 p_zone_attr IN VARCHAR2) RETURN VARCHAR2 is
1508
1509 l_sql_stmt varchar2(2000);
1510
1511 x_region_id varchar2(240):= null;
1512
1513 begin
1514
1515 if ((p_location_id is null) or (p_zone_attr is null)) then
1516 return msd_sr_util.get_null_desc;
1517 else
1518
1519 l_sql_stmt := ' select wrv.zone ' ||
1520 ' from wsh_region_locations wrl, ' ||
1521 ' wsh_zone_regions wzr, ' ||
1522 ' wsh_regions_v wrv ' ||
1523 ' where wrl.location_id = ''' || p_location_id || '''' ||
1524 ' and wrl.region_id = wzr.region_id ' ||
1525 ' and wzr.parent_region_id = wrv.region_id ' ||
1526 ' and wrv.region_type = 10 ' ||
1527 ' and decode(nvl(lower( ''' || p_zone_attr || ''' ), ''2''), ''attribute1'', wrv.attribute1, ' ||
1528 ' ''attribute2'', wrv.attribute2, ''attribute3'',wrv.attribute3, ''attribute4'', ' ||
1529 ' wrv.attribute4, ''attribute5'', wrv.attribute5, ''attribute6'', wrv.attribute6, ' ||
1530 ' ''attribute7'', wrv.attribute7, ''attribute8'', wrv.attribute8, ''attribute9'', ' ||
1531 ' wrv.attribute9, ''attribute10'', wrv.attribute10, ''attribute11'', wrv.attribute11, ' ||
1532 ' ''attribute12'', wrv.attribute12, ''attribute13'', wrv.attribute13, ''attribute14'', ' ||
1533 ' wrv.attribute14, ''attribute15'', wrv.attribute15, ''2'') = ''1'' ' ||
1534 ' order by wrv.region_id';
1535
1536 execute immediate l_sql_stmt into x_region_id;
1537
1538 if (x_region_id is not null) then
1539 return p_customer_name || ':' || x_region_id;
1540 else
1541 return msd_sr_util.get_null_desc;
1542 end if;
1543 end if;
1544 EXCEPTION
1545 when no_data_found then return msd_sr_util.get_null_desc;
1546 end get_sr_custzone_desc;
1547
1548 /* This function is used in Zone hierarchy views
1549 * to return Source Level Value Pks for Zone Level Values.
1550 * It is also used in Service Part Planning Server Views.
1551 */
1552
1553 FUNCTION get_sr_zone_pk1 ( p_location_id IN NUMBER,
1554 p_zone_attr IN VARCHAR2) return number IS
1555
1556 l_sql_stmt varchar2(2000);
1557
1558 x_region_id number:= null;
1559
1560 begin
1561
1562 if ((p_location_id is null) or (p_zone_attr is null)) then
1563 return msd_sr_util.get_null_pk;
1564 else
1565
1566 l_sql_stmt := ' select wrv.region_id ' ||
1567 ' from wsh_region_locations wrl, ' ||
1568 ' wsh_zone_regions wzr, ' ||
1569 ' wsh_regions_v wrv ' ||
1570 ' where wrl.location_id = ''' || p_location_id || '''' ||
1571 ' and wrl.region_id = wzr.region_id ' ||
1572 ' and wzr.parent_region_id = wrv.region_id ' ||
1573 ' and wrv.region_type = 10 ' ||
1574 ' and decode(nvl(lower( ''' || p_zone_attr || ''' ), ''2''), ''attribute1'', wrv.attribute1, ' ||
1575 ' ''attribute2'', wrv.attribute2, ''attribute3'',wrv.attribute3, ''attribute4'', ' ||
1576 ' wrv.attribute4, ''attribute5'', wrv.attribute5, ''attribute6'', wrv.attribute6, ' ||
1577 ' ''attribute7'', wrv.attribute7, ''attribute8'', wrv.attribute8, ''attribute9'', ' ||
1578 ' wrv.attribute9, ''attribute10'', wrv.attribute10, ''attribute11'', wrv.attribute11, ' ||
1579 ' ''attribute12'', wrv.attribute12, ''attribute13'', wrv.attribute13, ''attribute14'', ' ||
1580 ' wrv.attribute14, ''attribute15'', wrv.attribute15, ''2'') = ''1'' ' ||
1581 ' order by wrv.region_id';
1582
1583 execute immediate l_sql_stmt into x_region_id;
1584
1585 if (x_region_id is not null) then
1586 return x_region_id;
1587 else
1588 return msd_sr_util.get_null_pk;
1589 end if;
1590 end if;
1591 EXCEPTION
1592 when others then return msd_sr_util.get_null_pk;
1593 end get_sr_zone_pk1;
1594
1595
1596 /* This function is used in Zone hierarchy views
1597 * to return description for Zone Level Values.
1598 */
1599
1600 FUNCTION get_sr_zone_desc ( p_location_id IN NUMBER,
1601 p_zone_attr IN VARCHAR2) return varchar2 IS
1602
1603 l_sql_stmt varchar2(2000);
1604
1605 x_region_id varchar2(240):= null;
1606
1607 begin
1608
1609 if ((p_location_id is null) or (p_zone_attr is null)) then
1610 return msd_sr_util.get_null_desc;
1611 else
1612
1613 l_sql_stmt := ' select wrv.zone ' ||
1614 ' from wsh_region_locations wrl, ' ||
1615 ' wsh_zone_regions wzr, ' ||
1616 ' wsh_regions_v wrv ' ||
1617 ' where wrl.location_id = ''' || p_location_id || '''' ||
1618 ' and wrl.region_id = wzr.region_id ' ||
1619 ' and wzr.parent_region_id = wrv.region_id ' ||
1620 ' and wrv.region_type = 10 ' ||
1621 ' and decode(nvl(lower(''' || p_zone_attr || ''' ), ''2''), ''attribute1'', wrv.attribute1, ' ||
1622 ' ''attribute2'', wrv.attribute2, ''attribute3'',wrv.attribute3, ''attribute4'', ' ||
1623 ' wrv.attribute4, ''attribute5'', wrv.attribute5, ''attribute6'', wrv.attribute6, ' ||
1624 ' ''attribute7'', wrv.attribute7, ''attribute8'', wrv.attribute8, ''attribute9'', ' ||
1625 ' wrv.attribute9, ''attribute10'', wrv.attribute10, ''attribute11'', wrv.attribute11, ' ||
1626 ' ''attribute12'', wrv.attribute12, ''attribute13'', wrv.attribute13, ''attribute14'', ' ||
1627 ' wrv.attribute14, ''attribute15'', wrv.attribute15, ''2'') = ''1'' ' ||
1628 ' order by wrv.region_id';
1629
1630 execute immediate l_sql_stmt into x_region_id;
1631
1632 if (x_region_id is not null) then
1633 return x_region_id;
1634 else
1635 return msd_sr_util.get_null_desc;
1636 end if;
1637 end if;
1638 EXCEPTION
1639 when others then return msd_sr_util.get_null_desc;
1640 end get_sr_zone_desc;
1641
1642 END MSD_SR_UTIL;
[Home] [Help]