1 PACKAGE BODY MSD_DEM_SR_UTIL AS
2 /* $Header: msddemsub.pls 120.12.12020000.3 2012/10/16 10:10:03 nallkuma ship $ */
3
4 NULL_VALUE_PK_CONST constant number := -777;
5 NULL_VALUE_CODE_CONST constant varchar2(2) := '0';
6 NULL_VALUE_CONST constant varchar2(20) := 'unassociated';
7 CONST_DEFAULT_CODE constant varchar2(20) := 'Default';
8
9 C_YES constant number := 1;
10 C_NO constant number := 2;
11
12 /* BOM ITEM TYPE */
13 C_MODEL constant number := 1;
14 C_OPTION_CLASS constant number := 2;
15 C_PLANNING constant number := 3;
16 C_STANDARD constant number := 4;
17 C_PRODUCT_FAMILY constant number := 5;
18
19
20 /*** PUBLIC PROCEDURES ***
21 * EXECUTE_REMOTE_QUERY
22 */
23
24 /*
25 * This procedure executes a query passed from a remote database.
26 */
27
28 procedure EXECUTE_REMOTE_QUERY(query IN VARCHAR2)
29 IS
30 BEGIN
31 EXECUTE IMMEDIATE query;
32 EXCEPTION
33 WHEN others THEN
34 RETURN;
35 END EXECUTE_REMOTE_QUERY;
36
37
38 /*** FUNCTIONS ***
39 * SET_CUSTOMER_ATTRIBUTE
40 * GET_CATEGORY_SET_ID
41 * GET_CONVERSION_TYPE
42 * GET_MASTER_ORGANIZATION
43 * GET_CUSTOMER_ATTRIBUTE
44 * GET_NULL_PK
45 * GET_NULL_CODE
46 * GET_NULL_DESC
47 * GET_DEFAULT_CODE
48 * UOM_CONV
49 * IS_ITEM_OPTIONAL_FOR_LVL
50 * IS_PRODUCT_FAMILY_FORECASTABLE
51 * CONFIG_ITEM_EXISTS
52 * CONVERT_GLOBAL_AMT
53 * GET_ZONE_ATTR
54 * GET_SR_ZONE_DESC
55 * GET_SR_ZONE_PK
56 * IS_TXN_DEPOT_REPAIR
57 * GET_SERVICE_REQ_ORG_ID
58 * FIND_PARENT_ITEM
59 * FIND_BASE_MODEL
60 * IS_ITEM_OPTIONAL_FOR_FACT
61 * GET_SPF_MASTER_ORGANIZATION
62 */
63
64
65 /*
66 * Usability Enhancements. Bug # 3509147.
67 * This function sets the value of profile MSD_DEM_CUSTOMER_ATTRIBUTE to NONE
68 * if collecting for the first time
69 */
70 FUNCTION SET_CUSTOMER_ATTRIBUTE (
71 p_profile_code IN VARCHAR2,
72 p_profile_value IN VARCHAR2,
73 p_profile_level IN VARCHAR2)
74 RETURN NUMBER
75 IS
76 x_return_value BOOLEAN;
77 BEGIN
78 x_return_value := fnd_profile.save (
79 p_profile_code,
80 p_profile_value,
81 p_profile_level);
82 IF (x_return_value)
83 THEN
84 RETURN 1;
85 ELSE
86 RETURN 2;
87 END IF;
88
89 RETURN 2;
90
91 EXCEPTION
92 WHEN OTHERS THEN
93 RETURN 2;
94
95 END SET_CUSTOMER_ATTRIBUTE;
96
97
98 /*
99 * This function gets the value of the source profile MSD_DEM_CATEGORY_SET_NAME
100 */
101 FUNCTION GET_CATEGORY_SET_ID
102 RETURN NUMBER
103 IS
104 x_category_set_id NUMBER := -1;
105 BEGIN
106 x_category_set_id := fnd_profile.value ('MSD_DEM_CATEGORY_SET_NAME');
107 RETURN x_category_set_id;
108 EXCEPTION
109 WHEN OTHERS THEN
110 RETURN NULL;
111 END GET_CATEGORY_SET_ID;
112
113
114 /*
115 * This function gets the value of the source profile MSD_DEM_CONVERSION_TYPE
116 */
117 FUNCTION GET_CONVERSION_TYPE
118 RETURN VARCHAR2
119 IS
120 x_conversion_type VARCHAR2(100) := NULL;
121 BEGIN
122 x_conversion_type := fnd_profile.value ('MSD_DEM_CONVERSION_TYPE');
123 RETURN x_conversion_type;
124 EXCEPTION
125 WHEN OTHERS THEN
126 RETURN NULL;
127 END GET_CONVERSION_TYPE;
128
129
130 /*
131 * This function gets the ID of the master organization in the source instance.
132 */
133 FUNCTION GET_MASTER_ORGANIZATION
134 RETURN NUMBER
135 IS
136 x_master_organization NUMBER := NULL;
137 x_multi_org_flag VARCHAR2(1) := NULL;
138 BEGIN
139
140 BEGIN
141
142 SELECT multi_org_flag
143 INTO x_multi_org_flag
144 FROM fnd_product_groups
145 WHERE product_group_type = 'Standard';
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 x_multi_org_flag := 'Y';
150
151 END;
152
153 x_master_organization := fnd_profile.value ('MSD_DEM_MASTER_ORG');
154
155 IF (x_multi_org_flag = 'Y')
156 THEN
157 IF (x_master_organization IS NULL)
158 THEN
159
160 SELECT organization_id
161 INTO x_master_organization
162 FROM mtl_parameters
163 WHERE organization_id = master_organization_id
164 AND rownum <2;
165 END IF;
166
167 ELSE /* Single Master Organization OE Instance */
168
169 SELECT organization_id
170 INTO x_master_organization
171 FROM mtl_parameters
172 WHERE organization_id = master_organization_id
173 AND rownum <2;
174
175 END IF;
176
177 RETURN x_master_organization;
178
179 EXCEPTION
180 WHEN OTHERS THEN
181 RETURN NULL;
182
183 END GET_MASTER_ORGANIZATION;
184
185
186 /*
187 * This function gets the value of the source profile MSD_DEM_CUSTOMER_ATTRIBUTE
188 */
189 FUNCTION GET_CUSTOMER_ATTRIBUTE
190 RETURN VARCHAR2
191 IS
192 x_customer_attribute VARCHAR2(100) := NULL;
193 BEGIN
194 x_customer_attribute := fnd_profile.value ('MSD_DEM_CUSTOMER_ATTRIBUTE');
195 RETURN x_customer_attribute;
196 EXCEPTION
197 WHEN OTHERS THEN
198 RETURN NULL;
199 END GET_CUSTOMER_ATTRIBUTE;
200
201
202
203 function get_null_pk return number IS
204 BEGIN
205 return NULL_VALUE_PK_CONST;
206 END;
207
208 function get_null_code return VARCHAR2 IS
209 BEGIN
210 return NULL_VALUE_CODE_CONST;
211 END;
212
213 function get_null_desc return VARCHAR2 IS
214 BEGIN
215 return NULL_VALUE_CONST;
216 END;
217
218 function GET_DEFAULT_CODE return VARCHAR2 IS
219 BEGIN
220 return CONST_DEFAULT_CODE;
221 END;
222
223
224 function uom_conv (uom_code varchar2,
225 item_id number) return number as
226
227 base_uom varchar2(3);
228 conv_rate number:=1;
229 l_master_org number;
230 l_master_uom varchar2(3);
231
232 cursor base_uom_code_conversion(p_item_id number, p_uom_code varchar2) is
233 select t.conversion_rate std_conv_rate
234 from mtl_uom_conversions t
235 where t.inventory_item_id in (p_item_id, 0)
236 and t.uom_code= p_uom_code
237 and nvl(t.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
238 and t.conversion_rate is not null
239 order by t.inventory_item_id desc;
240
241 begin
242
243 /*
244 ** Conversion between between two UOMS.
245 **
246 ** 1. The conversion always starts from the conversion defined, if exists,
247 ** for an specified item.
248 ** 2. If the conversion id not defined for that specific item, then the
249 ** standard conversion, which is defined for all items, is used.
250 */
251
252 /*
253 open base_uom_code_conversion(item_id, uom_code);
254 fetch base_uom_code_conversion into conv_rate;
255 close base_uom_code_conversion;
256 */
257
258 select to_number(parameter_value)
259 into l_master_org
260 from msd_dem_setup_parameters
261 where parameter_name = 'MSD_DEM_MASTER_ORG';
262
263 select NVL(primary_uom_code,'Ea')
264 into l_master_uom
265 from mtl_system_items
266 where inventory_item_id = item_id
267 and organization_id = l_master_org;
268
269 conv_rate := inv_convert.inv_um_convert(item_id,NULL,NULL,uom_code,l_master_uom,NULL,NULL);
270
271 if (conv_rate = -99999) then
272 conv_rate := 1;
273 end if;
274
275 return conv_rate;
276
277
278 exception
279
280 when others then
281
282 return 1;
283
284 end uom_conv;
285
286
287 FUNCTION IS_ITEM_OPTIONAL_FOR_LVL(p_component_item_id in NUMBER) RETURN NUMBER IS
288
289 CURSOR c_optional IS
290 select 1
291 from
292 msd_dem_app_instance_orgs morg,
293 bom_bill_of_materials bbm,
294 mtl_system_items msi, -- Parent
295 bom_inventory_components bic
296 where
297 bic.bill_sequence_id = bbm.bill_sequence_id
298 and bbm.organization_id = morg.organization_id
299 and msi.organization_id = bbm.organization_id
300 and msi.inventory_item_id = bbm.assembly_item_id
301 and msi.bom_item_type not in (C_PLANNING, C_STANDARD, C_PRODUCT_FAMILY)
302 and bic.optional = C_YES
303 and ( msi.bom_item_type = 2 or
304 ( msi.bom_item_type = 1 and msi.ato_forecast_control in (1, 2) )
305 )
306 and bic.component_item_id = p_component_item_id;
307
308 l_count NUMBER := 0;
309
310
311 BEGIN
312
313 IF p_component_item_id is NOT NULL THEN
314 OPEN c_optional;
315 FETCH c_optional INTO l_count;
316 CLOSE c_optional;
317 END IF;
318
319 IF l_count = 0 THEN
320 return C_NO;
321 ELSE
322 return C_YES;
323 END IF;
324
325 END IS_ITEM_OPTIONAL_FOR_LVL;
326
327
328 FUNCTION IS_PRODUCT_FAMILY_FORECASTABLE (p_org_id in NUMBER,
329 p_inventory_item_id in NUMBER,
330 p_check_optional in NUMBER) RETURN NUMBER IS
331
332
333 CURSOR c_count IS
334 SELECT
335 count(1)
336 FROM
337 mtl_system_items parent,
338 bom_inventory_components bic,
339 bom_bill_of_materials bom
340 WHERE
341 parent.bom_item_type = 5 and
342 parent.organization_id = bom.organization_id and
343 bom.ASSEMBLY_ITEM_ID = parent.inventory_item_id and
344 bom.bill_sequence_id = bic.bill_sequence_id and
345 bic.component_item_id = p_inventory_item_id and
346 bom.organization_id = p_org_id and
347 nvl(parent.ato_forecast_control, 3) <> 3;
348
349 l_count NUMBER := 0;
350 l_optional NUMBER := C_NO;
351
352 BEGIN
353
354 OPEN c_count;
355 FETCH c_count INTO l_count;
356 IF c_count%ISOPEN THEN
357 CLOSE c_count;
358 END IF;
359
360 IF l_count > 0 THEN
361 return C_YES;
362 ELSE
363 IF p_check_optional = C_YES THEN
364 l_optional := IS_ITEM_OPTIONAL_FOR_LVL(p_inventory_item_id);
365 END IF;
366
367 IF l_optional = C_YES THEN
368 return C_YES;
369 ELSE
370 return C_NO;
371 END IF;
372 END IF;
373
374 EXCEPTION
375 when others then
376 return NULL;
377
378 END IS_PRODUCT_FAMILY_FORECASTABLE;
379
380
381 FUNCTION CONFIG_ITEM_EXISTS ( p_header_id IN NUMBER,
382 p_org_id IN NUMBER,
383 p_ato_line_id IN NUMBER) RETURN NUMBER IS
384
385 CURSOR c_config_model (p_header_id IN NUMBER,
386 p_org_id IN NUMBER,
387 p_ato_line_id IN NUMBER) IS
388 select count(1)
389 from mtl_system_items itm
390 where inventory_item_id = (select inventory_item_id
391 from oe_order_lines_all l
392 where l.item_type_code = 'CONFIG'
393 and l.header_id = p_header_id
394 and l.org_id = p_org_id
395 and l.ato_line_id = p_ato_line_id )
396 and itm.organization_id = p_org_id
397 and nvl(itm.ato_forecast_control, 3) <> 3
398 and itm.base_item_id is not null;
399
400 l_item_count NUMBER := 0;
401
402 BEGIN
403
404 IF p_header_id is NOT NULL THEN
405 OPEN c_config_model (p_header_id,
406 p_org_id,
407 p_ato_line_id);
408 FETCH c_config_model INTO l_item_count;
409 CLOSE c_config_model;
410
411 IF nvl(l_item_count, 0) = 1 THEN
412 return C_YES;
413 ELSE
414 return C_NO;
415 END IF;
416 ELSE
417 return C_NO;
418 END IF;
419
420 EXCEPTION
421 WHEN OTHERS THEN
422 return C_NO;
423
424 END CONFIG_ITEM_EXISTS;
425
426
427 function convert_global_amt(p_curr_code in varchar2, p_date in date) return number IS
428 l_ret number;
429 c_global_currency_code varchar2(40);
430 c_global_rate_type varchar2(40);
431 BEGIN
432
433 select parameter_value
434 into c_global_currency_code
435 from msd_dem_setup_parameters
436 where parameter_name = 'MSD_DEM_CURRENCY_CODE';
437
438 select parameter_value
439 into c_global_rate_type
440 from msd_dem_setup_parameters
441 where parameter_name = 'MSD_DEM_CONVERSION_TYPE';
442
443 if (p_curr_code = c_global_currency_code) then
444 l_ret := 1;
445 else
446 l_ret := GL_CURRENCY_API.convert_amount_sql (
447 p_curr_code,
448 c_global_currency_code,
449 p_date,
450 c_global_rate_type,
451 1
452 );
453 end if;
454 return l_ret;
455
456 EXCEPTION when others then return NULL;
457
458 END convert_global_amt;
459
460 function get_zone_attr return varchar2 is --jarora
461 x_zone_attr varchar2(100);
462 x_wsh_application_id number := 665;
463 x_end_user_column_name varchar2(100) := 'Zone Usage';
464 x_des_fname varchar2(100) := 'WSH_REGIONS';
465
466 cursor c1 is
467 select application_column_name
468 from fnd_descr_flex_column_usages
469 where end_user_column_name = x_end_user_column_name
470 and descriptive_flexfield_name = x_des_fname
471 and application_id = x_wsh_application_id;
472
473 begin
474 open c1;
475 fetch c1 into x_zone_attr;
476 close c1;
477
478 return x_zone_attr;
479
480 exception
481 when others then return NULL;
482 end get_zone_attr;
483
484 FUNCTION get_sr_zone_desc ( p_location_id IN NUMBER,
485 p_zone_attr IN VARCHAR2) return varchar2 IS --jarora
486
487 l_sql_stmt varchar2(2000);
488
489 x_region_id varchar2(240):= null;
490
491 begin
492
493 if ((p_location_id is null) or (p_zone_attr is null)) then
494 return msd_sr_util.get_null_desc;
495 else
496
497 l_sql_stmt := ' select wrv.zone ' ||
498 ' from wsh_region_locations wrl, ' ||
499 ' wsh_zone_regions wzr, ' ||
500 ' wsh_regions_v wrv ' ||
501 ' where wrl.location_id = ''' || p_location_id || '''' ||
502 ' and wrl.region_id = wzr.region_id ' ||
503 ' and wzr.parent_region_id = wrv.region_id ' ||
504 ' and wrv.region_type = 10 ' ||
505 ' and decode(nvl(lower(''' || p_zone_attr || ''' ), ''2''), ''attribute1'', wrv.attribute1, ' ||
506 ' ''attribute2'', wrv.attribute2, ''attribute3'',wrv.attribute3, ''attribute4'', ' ||
507 ' wrv.attribute4, ''attribute5'', wrv.attribute5, ''attribute6'', wrv.attribute6, ' ||
508 ' ''attribute7'', wrv.attribute7, ''attribute8'', wrv.attribute8, ''attribute9'', ' ||
509 ' wrv.attribute9, ''attribute10'', wrv.attribute10, ''attribute11'', wrv.attribute11, ' ||
510 ' ''attribute12'', wrv.attribute12, ''attribute13'', wrv.attribute13, ''attribute14'', ' ||
511 ' wrv.attribute14, ''attribute15'', wrv.attribute15, ''2'') = ''1'' ' ||
512 ' order by wrv.region_id';
513
514 execute immediate l_sql_stmt into x_region_id;
515
516 if (x_region_id is not null) then
517 return x_region_id;
518 else
519 return msd_sr_util.get_null_desc;
520 end if;
521 end if;
522 EXCEPTION
523 when others then return msd_sr_util.get_null_desc;
524 end get_sr_zone_desc;
525
526 FUNCTION get_sr_zone_pk ( p_location_id IN NUMBER,
527 p_zone_attr IN VARCHAR2) return number IS --jarora
528
529 cursor c1 is
530 select wrv.region_id
531 from wsh_region_locations wrl,
532 wsh_zone_regions wzr,
533 wsh_regions_v wrv
534 where wrl.location_id = p_location_id
535 and wrl.region_id = wzr.region_id
536 and wzr.parent_region_id = wrv.region_id
537 and wrv.region_type = 10
538 and decode(nvl(lower(p_zone_attr), '2'), 'attribute1', wrv.attribute1,
539 'attribute2', wrv.attribute2, 'attribute3',wrv.attribute3, 'attribute4',
540 wrv.attribute4, 'attribute5', wrv.attribute5, 'attribute6', wrv.attribute6,
541 'attribute7', wrv.attribute7, 'attribute8', wrv.attribute8, 'attribute9',
542 wrv.attribute9, 'attribute10', wrv.attribute10, 'attribute11', wrv.attribute11,
543 'attribute12', wrv.attribute12, 'attribute13', wrv.attribute13, 'attribute14',
544 wrv.attribute14, 'attribute15', wrv.attribute15, '2') = '1'
545 order by wrv.region_id;
546
547 x_region_id number:= null;
548
549 begin
550
551 if ((p_location_id is null) or (p_zone_attr is null)) then
552 return -777;
553 else
554 open c1;
555 fetch c1 into x_region_id;
556 close c1;
557
558 if (x_region_id is not null) then
559 return x_region_id;
560 else
561 return -777;
562 end if;
563 end if;
564 EXCEPTION
565 when no_data_found then return -777;
566 end get_sr_zone_pk;
567
568 FUNCTION is_txn_depot_repair(p_txn_source_id IN NUMBER) return VARCHAR2 is --jarora
569
570 x_row_num number;
571 cursor c1 is
572 select 1
573 from csd_repair_job_xref crjx
574 where wip_entity_id = p_txn_source_id
575 order by repair_job_xref_id;
576
577 begin
578
579 if (p_txn_source_id is null) then
580 return 'N';
581 else
582 open c1;
583 fetch c1 into x_row_num;
584 close c1;
585
586 if (x_row_num is not null) then
587 return 'Y';
588 else
589 return 'N';
590 end if;
591 end if;
592 EXCEPTION
593 when no_data_found then return 'N';
594 end is_txn_depot_repair;
595
596 FUNCTION get_service_req_org_id (p_txn_source_id IN NUMBER) return NUMBER is --jarora
597 x_org_id number;
598
599 cursor c1 is
600 select organization_id
601 from csd_repair_job_xref crjx
602 where wip_entity_id = p_txn_source_id
603 order by repair_job_xref_id desc;
604
605 begin
606
607 if (p_txn_source_id is null) then
608 return -777;
609 else
610 open c1;
611 fetch c1 into x_org_id;
612 close c1;
613
614 if (x_org_id is not null) then
615 return x_org_id;
616 else
617 return -777;
618 end if;
619 end if;
620 EXCEPTION
621 when no_data_found then return -777;
622 end get_service_req_org_id;
623
624 /* This function checks if data has to be collected for a given customer(party).
625 * Returns 1 (true) if all the customer accounts associated with the customer are enabled,
626 * returns 2 (false) if any of the customer accounts associated is disabled.
627 */
628 FUNCTION get_data_for_customer(p_party_id in number, p_cust_attribute in varchar2) return NUMBER --syenamar
629 is
630
631 x_sql varchar2(2000) := null;
632 x_disabled_accnts number;
633
634 begin
635
636 x_sql := 'select count(cust_account_id) from hz_cust_accounts' ||
637 ' where party_id = ' || p_party_id ||
638 ' and ( ' || p_cust_attribute || ' <> ''1'' or ' || p_cust_attribute || ' is null)';
639 execute immediate x_sql into x_disabled_accnts;
640
641 if x_disabled_accnts <> 0 then
642 return 2;
643 else
644 return 1;
645 end if;
646
647 end get_data_for_customer;
648
649 /*
650 * This function gets the parent item for the given item. If profile MSD_DEM: Calculate
651 * Planning Percentage is set to 'Yes, for "Consume & Derive" Options only' then it gets
652 * nearest parent model.
653 */
654 FUNCTION FIND_PARENT_ITEM ( p_link_to_line_id IN NUMBER,
655 p_include_class IN VARCHAR2 )
656 RETURN NUMBER
657 IS
658
659 x_line_id NUMBER := p_link_to_line_id;
660 x_item_type_code VARCHAR2(10) := NULL;
661 x_parent_item_id NUMBER := NULL;
662 x_link_to_line_id NUMBER := 0;
663 x_ato_line_id NUMBER := 0;
664 x_loop BOOLEAN := TRUE;
665
666 CURSOR C_PARENT
667 IS
668 SELECT
669 inventory_item_id,
670 item_type_code,
671 link_to_line_id,
672 ato_line_id
673 FROM
674 oe_order_lines_all
675 WHERE
676 line_id = x_line_id;
677
678 BEGIN
679
680 WHILE (x_loop)
681 LOOP
682
683 IF (p_include_class = 'Y')
684 THEN
685 x_loop := FALSE;
686 END IF;
687
688 OPEN C_PARENT;
689 FETCH C_PARENT INTO x_parent_item_id,
690 x_item_type_code,
691 x_link_to_line_id,
692 x_ato_line_id;
693 CLOSE C_PARENT;
694
695 IF (x_item_type_code = 'CLASS'
696 AND (nvl(x_ato_line_id, -999) <> x_line_id))
697 THEN
698 x_line_id := x_link_to_line_id;
699 ELSE
700 x_loop := FALSE;
701 END IF;
702
703 END LOOP;
704
705 RETURN x_parent_item_id;
706
707 END FIND_PARENT_ITEM;
708
709
710 /*
711 * This function get the inventory_item_id of the base model in the configuration.
712 */
713 FUNCTION FIND_BASE_MODEL ( p_top_model_line_id IN NUMBER )
714 RETURN NUMBER
715 IS
716
717 x_base_model_id NUMBER := NULL;
718
719 BEGIN
720
721 SELECT inventory_item_id
722 INTO x_base_model_id
723 FROM oe_order_lines_all
724 WHERE line_id = p_top_model_line_id;
725
726 RETURN x_base_model_id;
727
728 END FIND_BASE_MODEL;
729
730
731 /*
732 * This function is called when item has ato_forecast_control = NONE.
733 * First, check whether the given component is optional component in the BOM or not.
734 * If so, then find the parent's component sequence id and then check whether the
735 * parent is either ((optional with forecast control = none) or (consume and derive))
736 */
737 FUNCTION IS_ITEM_OPTIONAL_FOR_FACT ( p_component_item_id IN NUMBER,
738 p_component_sequence_id IN NUMBER,
739 p_parent_line_id IN NUMBER )
740 RETURN NUMBER
741 IS
742
743 x_component_sequence_id NUMBER := p_component_sequence_id;
744 x_parent_component_sequence_id NUMBER := NULL;
745 x_count NUMBER := 0;
746
747 CURSOR C_IS_OPTIONAL
748 IS
749 SELECT 1
750 FROM msd_dem_app_instance_orgs mdaio,
751 bom_bill_of_materials bbm,
752 mtl_system_items msi,
753 bom_inventory_components bic
754 WHERE
755 bic.component_sequence_id = x_component_sequence_id
756 AND bic.bill_sequence_id = bbm.bill_sequence_id
757 AND bbm.organization_id = mdaio.organization_id
758 AND msi.organization_id = bbm.organization_id
759 AND msi.inventory_item_id = bbm.assembly_item_id
760 AND msi.bom_item_type not in (C_PLANNING, C_STANDARD, C_PRODUCT_FAMILY)
761 AND bic.optional = C_YES;
762
763 CURSOR C_PARENT_ITEM
764 IS
765 SELECT component_sequence_id
766 FROM oe_order_lines_all
767 WHERE line_id = p_parent_line_id;
768
769 /* The parent of optional item has to be either consume and drive or none with optional = YES */
770 CURSOR C_IS_PARENT_OPTIONAL
771 IS
772 SELECT
773 1
774 FROM msd_dem_app_instance_orgs mdaio,
775 bom_bill_of_materials bbm,
776 mtl_system_items msi,
777 bom_inventory_components bic
778 WHERE
779 bic.component_sequence_id = x_component_sequence_id
780 AND bic.bill_sequence_id = bbm.bill_sequence_id
781 AND bbm.organization_id = mdaio.organization_id
782 AND msi.organization_id = bbm.organization_id
783 AND msi.inventory_item_id = bic.component_item_id
784 AND (( msi.ato_forecast_control = 3
785 AND bic.optional = C_YES)
786 OR
787 msi.ato_forecast_control in (1, 2));
788
789 BEGIN
790
791 OPEN C_IS_OPTIONAL;
792 FETCH C_IS_OPTIONAL INTO x_count;
793 CLOSE C_IS_OPTIONAL;
794
795 IF (x_count <> 0)
796 THEN
797
798 OPEN C_PARENT_ITEM;
799 FETCH C_PARENT_ITEM INTO x_parent_component_sequence_id;
800 CLOSE C_PARENT_ITEM;
801
802 x_component_sequence_id := x_parent_component_sequence_id;
803
804 IF (x_component_sequence_id IS NOT NULL)
805 THEN
806 x_count := 0;
807 OPEN C_IS_PARENT_OPTIONAL;
808 FETCH C_IS_PARENT_OPTIONAL INTO x_count;
809 CLOSE C_IS_PARENT_OPTIONAL;
810 END IF;
811 END IF;
812
813 IF (x_count <> 0)
814 THEN
815 RETURN C_YES;
816 END IF;
817
818 RETURN C_NO;
819
820 END IS_ITEM_OPTIONAL_FOR_FACT;
821
822
823
824
825 /*
826 * This function gets the value of the source profile MSD_SPF_ORGANIZATION_ATTRIBUTE
827 */
828 FUNCTION GET_SPF_MASTER_ORGANIZATION
829 RETURN VARCHAR2
830 IS
831 var_organization_id VARCHAR2(100) DEFAULT NULL;
832 BEGIN
833 var_organization_id := fnd_profile.value ('MSD_SPF_MASTER_ORG');
834 RETURN var_organization_id;
835 EXCEPTION
836 WHEN OTHERS THEN
837 RETURN NULL;
838 END GET_SPF_MASTER_ORGANIZATION;
839
840 /*
841 * This function gets the sr_pk of base model from SR associated to given ISO line
842 */
843 FUNCTION GET_BASE_ITEM_IN_SR_FOR_LINE ( p_order_line_id in number)
844 RETURN NUMBER
845 IS
846 x_sql varchar2(800) := null;
847 x_item_id number := null;
848 begin
849 x_sql := 'select cia.inventory_item_id ' ||
850 ' from csp_req_line_details crld, ' ||
851 ' csp_requirement_lines crl, ' ||
852 ' csp_requirement_headers crh, ' ||
853 ' jtf_tasks_b jtb, ' ||
854 ' cs_incidents_all cia ' ||
855 ' where crld.source_type = ''IO'' ' ||
856 ' and crld.source_id = :1 ' ||
857 ' and crl.requirement_line_id = crld.requirement_line_id ' ||
858 ' and crh.requirement_header_id = crl.requirement_header_id ' ||
859 ' and jtb.task_id = crh.task_id ' ||
860 ' and jtb.source_object_type_code = ''SR'' ' ||
861 ' and cia.incident_id = jtb.source_object_id ' ||
862 ' and rownum < 2 ' ; --Bug#13561423
863 execute immediate x_sql into x_item_id using p_order_line_id;
864 RETURN x_item_id;
865 end GET_BASE_ITEM_IN_SR_FOR_LINE;
866
867 --Bug 14683310--kkhatri--12.2.1
868 --Bug 13716734--kkahtri-12.3
869 /*
870 * This function will check whether the sub-inventory used for iso is
871 * GOOD - then returns 1
872 * BAD - then returns 2
873 */
874
875 FUNCTION GET_SUB_INV_TYPE
876 ( p_order_line_id number, p_attribute_col varchar2)
877 RETURN number
878 is
879 P_SUB_INV_TYPE number(10):= null;
880 x_sql varchar2(1000) := null;
881
882 begin
883
884 x_sql := ' select decode(nvl(' || p_attribute_col || ',''G''),''B'',2,1) '
885 || ' from '
886 || ' MTL_MATERIAL_TRANSACTIONS MMT,'
887 || ' MTL_SECONDARY_INVENTORIES MSI'
888 || ' where MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID'
889 || ' and MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME'
890 || ' and MMT.TRANSACTION_TYPE_ID = 62' -- Int Order Intr Ship
891 || ' and MMT.TRANSACTION_SOURCE_TYPE_ID = 8' -- Internal Orders
892 || ' and MMT.TRX_SOURCE_LINE_ID = ' || p_order_line_id
893 || ' and rownum < 2 ' ;
894
895 execute immediate x_sql into P_SUB_INV_TYPE ;
896 return P_SUB_INV_TYPE;
897
898 EXCEPTION
899 when OTHERS then
900 return 1;
901 end GET_SUB_INV_TYPE;
902
903 /*
904 * This function gets the transit type flag for a given visit type
905 */
906 FUNCTION GET_TRANSIT_TYPE_FLAG (p_visit_type_code IN VARCHAR2)
907 RETURN VARCHAR2
908 IS
909
910 x_sql VARCHAR2(1000);
911 x_transit_type_flag VARCHAR2(10) := 'N';
912
913 BEGIN
914
915 x_sql := 'SELECT transit_type_flag FROM ahl_visit_types_b where visit_type_code = :1 and rownum < 2';
916
917 EXECUTE IMMEDIATE x_sql
918 INTO x_transit_type_flag
919 USING p_visit_type_code;
920
921 RETURN x_transit_type_flag;
922
923 EXCEPTION
924 WHEN OTHERS THEN
925 RETURN 'N';
926
927 END GET_TRANSIT_TYPE_FLAG;
928
929 END MSD_DEM_SR_UTIL;
[Home] [Help]