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