1 PACKAGE BODY MSD_DEM_SR_UTIL AS
2 /* $Header: msddemsub.pls 120.4 2007/10/31 12:51:46 syenamar noship $ */
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
8 C_YES constant number := 1;
9 C_NO constant number := 2;
10
11 /* BOM ITEM TYPE */
12 C_MODEL constant number := 1;
13 C_OPTION_CLASS constant number := 2;
14 C_PLANNING constant number := 3;
15 C_STANDARD constant number := 4;
16 C_PRODUCT_FAMILY constant number := 5;
17
18
19 /*** PUBLIC PROCEDURES ***
20 * EXECUTE_REMOTE_QUERY
21 */
22
23 /*
24 * This procedure executes a query passed from a remote database.
25 */
26
27 procedure EXECUTE_REMOTE_QUERY(query IN VARCHAR2)
28 IS
29 BEGIN
30 EXECUTE IMMEDIATE query;
31 EXCEPTION
32 WHEN others THEN
33 RETURN;
34 END EXECUTE_REMOTE_QUERY;
35
36
37 /*** PUBLIC FUNCTIONS ***
38 * SET_CUSTOMER_ATTRIBUTE
39 * GET_CATEGORY_SET_ID
40 * GET_CONVERSION_TYPE
41 * GET_MASTER_ORGANIZATION
42 * GET_CUSTOMER_ATTRIBUTE
43 */
44
45
46 /*
47 * Usability Enhancements. Bug # 3509147.
48 * This function sets the value of profile MSD_DEM_CUSTOMER_ATTRIBUTE to NONE
49 * if collecting for the first time
50 */
51 FUNCTION SET_CUSTOMER_ATTRIBUTE (
52 p_profile_code IN VARCHAR2,
53 p_profile_value IN VARCHAR2,
54 p_profile_level IN VARCHAR2)
55 RETURN NUMBER
56 IS
57 x_return_value BOOLEAN;
58 BEGIN
59 x_return_value := fnd_profile.save (
60 p_profile_code,
61 p_profile_value,
62 p_profile_level);
63 IF (x_return_value)
64 THEN
65 RETURN 1;
66 ELSE
67 RETURN 2;
68 END IF;
69
70 RETURN 2;
71
72 EXCEPTION
73 WHEN OTHERS THEN
74 RETURN 2;
75
76 END SET_CUSTOMER_ATTRIBUTE;
77
78
79 /*
80 * This function gets the value of the source profile MSD_DEM_CATEGORY_SET_NAME
81 */
82 FUNCTION GET_CATEGORY_SET_ID
83 RETURN NUMBER
84 IS
85 x_category_set_id NUMBER := -1;
86 BEGIN
87 x_category_set_id := fnd_profile.value ('MSD_DEM_CATEGORY_SET_NAME');
88 RETURN x_category_set_id;
89 EXCEPTION
90 WHEN OTHERS THEN
91 RETURN NULL;
92 END GET_CATEGORY_SET_ID;
93
94
95 /*
96 * This function gets the value of the source profile MSD_DEM_CONVERSION_TYPE
97 */
98 FUNCTION GET_CONVERSION_TYPE
99 RETURN VARCHAR2
100 IS
101 x_conversion_type VARCHAR2(100) := NULL;
102 BEGIN
103 x_conversion_type := fnd_profile.value ('MSD_DEM_CONVERSION_TYPE');
104 RETURN x_conversion_type;
105 EXCEPTION
106 WHEN OTHERS THEN
107 RETURN NULL;
108 END GET_CONVERSION_TYPE;
109
110
111 /*
112 * This function gets the ID of the master organization in the source instance.
113 */
114 FUNCTION GET_MASTER_ORGANIZATION
115 RETURN NUMBER
116 IS
117 x_master_organization NUMBER := NULL;
118 x_multi_org_flag VARCHAR2(1) := NULL;
119 BEGIN
120
121 BEGIN
122
123 SELECT multi_org_flag
124 INTO x_multi_org_flag
125 FROM fnd_product_groups
126 WHERE product_group_type = 'Standard';
127
128 EXCEPTION
129 WHEN OTHERS THEN
130 x_multi_org_flag := 'Y';
131
132 END;
133
134 x_master_organization := fnd_profile.value ('MSD_DEM_MASTER_ORG');
135
136 IF (x_multi_org_flag = 'Y')
137 THEN
138 IF (x_master_organization IS NULL)
139 THEN
140
141 SELECT organization_id
142 INTO x_master_organization
143 FROM mtl_parameters
144 WHERE organization_id = master_organization_id
145 AND rownum <2;
146 END IF;
147
148 ELSE /* Single Master Organization OE Instance */
149
150 SELECT organization_id
151 INTO x_master_organization
152 FROM mtl_parameters
153 WHERE organization_id = master_organization_id
154 AND rownum <2;
155
156 END IF;
157
158 RETURN x_master_organization;
159
160 EXCEPTION
161 WHEN OTHERS THEN
162 RETURN NULL;
163
164 END GET_MASTER_ORGANIZATION;
165
166
167 /*
168 * This function gets the value of the source profile MSD_DEM_CUSTOMER_ATTRIBUTE
169 */
170 FUNCTION GET_CUSTOMER_ATTRIBUTE
171 RETURN VARCHAR2
172 IS
173 x_customer_attribute VARCHAR2(100) := NULL;
174 BEGIN
175 x_customer_attribute := fnd_profile.value ('MSD_DEM_CUSTOMER_ATTRIBUTE');
176 RETURN x_customer_attribute;
177 EXCEPTION
178 WHEN OTHERS THEN
179 RETURN NULL;
180 END GET_CUSTOMER_ATTRIBUTE;
181
182
183
184 function get_null_pk return number IS
185 BEGIN
186 return NULL_VALUE_PK_CONST;
187 END;
188
189 function get_null_code return VARCHAR2 IS
190 BEGIN
191 return NULL_VALUE_CODE_CONST;
192 END;
193
194 function get_null_desc return VARCHAR2 IS
195 BEGIN
196 return NULL_VALUE_CONST;
197 END;
198
199
200 function uom_conv (uom_code varchar2,
201 item_id number) return number as
202
203 base_uom varchar2(3);
204 conv_rate number:=1;
205 l_master_org number;
206 l_master_uom varchar2(3);
207
208 cursor base_uom_code_conversion(p_item_id number, p_uom_code varchar2) is
209 select t.conversion_rate std_conv_rate
210 from mtl_uom_conversions t
211 where t.inventory_item_id in (p_item_id, 0)
212 and t.uom_code= p_uom_code
213 and nvl(t.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
214 and t.conversion_rate is not null
215 order by t.inventory_item_id desc;
216
217 begin
218
219 /*
220 ** Conversion between between two UOMS.
221 **
222 ** 1. The conversion always starts from the conversion defined, if exists,
223 ** for an specified item.
224 ** 2. If the conversion id not defined for that specific item, then the
225 ** standard conversion, which is defined for all items, is used.
226 */
227
228 /*
229 open base_uom_code_conversion(item_id, uom_code);
230 fetch base_uom_code_conversion into conv_rate;
231 close base_uom_code_conversion;
232 */
233
234 select to_number(parameter_value)
235 into l_master_org
236 from msd_dem_setup_parameters
237 where parameter_name = 'MSD_DEM_MASTER_ORG';
238
239 select NVL(primary_uom_code,'Ea')
240 into l_master_uom
241 from mtl_system_items
242 where inventory_item_id = item_id
243 and organization_id = l_master_org;
244
245 conv_rate := inv_convert.inv_um_convert(item_id,NULL,NULL,uom_code,l_master_uom,NULL,NULL);
246
247 if (conv_rate = -99999) then
248 conv_rate := 1;
249 end if;
250
251 return conv_rate;
252
253
254 exception
255
256 when others then
257
258 return 1;
259
260 end uom_conv;
261
262
263 FUNCTION IS_ITEM_OPTIONAL_FOR_LVL(p_component_item_id in NUMBER) RETURN NUMBER IS
264
265 CURSOR c_optional IS
266 select 1
267 from
268 msd_dem_app_instance_orgs morg,
269 bom_bill_of_materials bbm,
270 mtl_system_items msi, -- Parent
271 bom_inventory_components bic
272 where
273 bic.bill_sequence_id = bbm.bill_sequence_id
274 and bbm.organization_id = morg.organization_id
275 and msi.organization_id = bbm.organization_id
276 and msi.inventory_item_id = bbm.assembly_item_id
277 and msi.bom_item_type not in (C_PLANNING, C_STANDARD, C_PRODUCT_FAMILY)
278 and bic.optional = C_YES
279 and ( msi.bom_item_type = 2 or
280 ( msi.bom_item_type = 1 and msi.ato_forecast_control in (1, 2) )
281 )
282 and bic.component_item_id = p_component_item_id;
283
284 l_count NUMBER := 0;
285
286
287 BEGIN
288
289 IF p_component_item_id is NOT NULL THEN
290 OPEN c_optional;
291 FETCH c_optional INTO l_count;
292 CLOSE c_optional;
293 END IF;
294
295 IF l_count = 0 THEN
296 return C_NO;
297 ELSE
298 return C_YES;
299 END IF;
300
301 END IS_ITEM_OPTIONAL_FOR_LVL;
302
303
304 FUNCTION IS_PRODUCT_FAMILY_FORECASTABLE (p_org_id in NUMBER,
305 p_inventory_item_id in NUMBER,
306 p_check_optional in NUMBER) RETURN NUMBER IS
307
308
309 CURSOR c_count IS
310 SELECT
311 count(1)
312 FROM
313 mtl_system_items parent,
314 bom_inventory_components bic,
315 bom_bill_of_materials bom
316 WHERE
317 parent.bom_item_type = 5 and
318 parent.organization_id = bom.organization_id and
319 bom.ASSEMBLY_ITEM_ID = parent.inventory_item_id and
320 bom.bill_sequence_id = bic.bill_sequence_id and
321 bic.component_item_id = p_inventory_item_id and
322 bom.organization_id = p_org_id and
323 nvl(parent.ato_forecast_control, 3) <> 3;
324
325 l_count NUMBER := 0;
326 l_optional NUMBER := C_NO;
327
328 BEGIN
329
330 OPEN c_count;
331 FETCH c_count INTO l_count;
332 IF c_count%ISOPEN THEN
333 CLOSE c_count;
334 END IF;
335
336 IF l_count > 0 THEN
337 return C_YES;
338 ELSE
339 IF p_check_optional = C_YES THEN
340 l_optional := IS_ITEM_OPTIONAL_FOR_LVL(p_inventory_item_id);
341 END IF;
342
343 IF l_optional = C_YES THEN
344 return C_YES;
345 ELSE
346 return C_NO;
347 END IF;
348 END IF;
349
350 EXCEPTION
351 when others then
352 return NULL;
353
354 END IS_PRODUCT_FAMILY_FORECASTABLE;
355
356
357 FUNCTION CONFIG_ITEM_EXISTS ( p_header_id IN NUMBER,
358 p_org_id IN NUMBER,
359 p_ato_line_id IN NUMBER) RETURN NUMBER IS
360
361 CURSOR c_config_model (p_header_id IN NUMBER,
362 p_org_id IN NUMBER,
363 p_ato_line_id IN NUMBER) IS
364 select count(1)
365 from mtl_system_items itm
366 where inventory_item_id = (select inventory_item_id
367 from oe_order_lines_all l
368 where l.item_type_code = 'CONFIG'
369 and l.header_id = p_header_id
370 and l.org_id = p_org_id
371 and l.ato_line_id = p_ato_line_id )
372 and itm.organization_id = p_org_id
373 and nvl(itm.ato_forecast_control, 3) <> 3
374 and itm.base_item_id is not null;
375
376 l_item_count NUMBER := 0;
377
378 BEGIN
379
380 IF p_header_id is NOT NULL THEN
381 OPEN c_config_model (p_header_id,
382 p_org_id,
383 p_ato_line_id);
384 FETCH c_config_model INTO l_item_count;
385 CLOSE c_config_model;
386
387 IF nvl(l_item_count, 0) = 1 THEN
388 return C_YES;
389 ELSE
390 return C_NO;
391 END IF;
392 ELSE
393 return C_NO;
394 END IF;
395
396 EXCEPTION
397 WHEN OTHERS THEN
398 return C_NO;
399
400 END CONFIG_ITEM_EXISTS;
401
402
403 function convert_global_amt(p_curr_code in varchar2, p_date in date) return number IS
404 l_ret number;
405 c_global_currency_code varchar2(40);
406 c_global_rate_type varchar2(40);
407 BEGIN
408
409 select parameter_value
410 into c_global_currency_code
411 from msd_dem_setup_parameters
412 where parameter_name = 'MSD_DEM_CURRENCY_CODE';
413
414 select parameter_value
415 into c_global_rate_type
416 from msd_dem_setup_parameters
417 where parameter_name = 'MSD_DEM_CONVERSION_TYPE';
418
419 if (p_curr_code = c_global_currency_code) then
420 l_ret := 1;
421 else
422 l_ret := GL_CURRENCY_API.convert_amount_sql (
423 p_curr_code,
424 c_global_currency_code,
425 p_date,
426 c_global_rate_type,
427 1
428 );
429 end if;
430 return l_ret;
431
432 EXCEPTION when others then return NULL;
433
434 END convert_global_amt;
435
436 function get_zone_attr return varchar2 is --jarora
437 x_zone_attr varchar2(100);
438 x_wsh_application_id number := 665;
439 x_end_user_column_name varchar2(100) := 'Zone Usage';
440 x_des_fname varchar2(100) := 'WSH_REGIONS';
441
442 cursor c1 is
443 select application_column_name
444 from fnd_descr_flex_column_usages
445 where end_user_column_name = x_end_user_column_name
446 and descriptive_flexfield_name = x_des_fname
447 and application_id = x_wsh_application_id;
448
449 begin
450 open c1;
451 fetch c1 into x_zone_attr;
452 close c1;
453
454 return x_zone_attr;
455
456 exception
457 when others then return NULL;
458 end get_zone_attr;
459
460 FUNCTION get_sr_zone_desc ( p_location_id IN NUMBER,
461 p_zone_attr IN VARCHAR2) return varchar2 IS --jarora
462
463 l_sql_stmt varchar2(2000);
464
465 x_region_id varchar2(240):= null;
466
467 begin
468
469 if ((p_location_id is null) or (p_zone_attr is null)) then
470 return msd_sr_util.get_null_desc;
471 else
472
473 l_sql_stmt := ' select wrv.zone ' ||
474 ' from wsh_region_locations wrl, ' ||
475 ' wsh_zone_regions wzr, ' ||
476 ' wsh_regions_v wrv ' ||
477 ' where wrl.location_id = ''' || p_location_id || '''' ||
478 ' and wrl.region_id = wzr.region_id ' ||
479 ' and wzr.parent_region_id = wrv.region_id ' ||
480 ' and wrv.region_type = 10 ' ||
481 ' and decode(nvl(lower(''' || p_zone_attr || ''' ), ''2''), ''attribute1'', wrv.attribute1, ' ||
482 ' ''attribute2'', wrv.attribute2, ''attribute3'',wrv.attribute3, ''attribute4'', ' ||
483 ' wrv.attribute4, ''attribute5'', wrv.attribute5, ''attribute6'', wrv.attribute6, ' ||
484 ' ''attribute7'', wrv.attribute7, ''attribute8'', wrv.attribute8, ''attribute9'', ' ||
485 ' wrv.attribute9, ''attribute10'', wrv.attribute10, ''attribute11'', wrv.attribute11, ' ||
489
486 ' ''attribute12'', wrv.attribute12, ''attribute13'', wrv.attribute13, ''attribute14'', ' ||
487 ' wrv.attribute14, ''attribute15'', wrv.attribute15, ''2'') = ''1'' ' ||
488 ' order by wrv.region_id';
490 execute immediate l_sql_stmt into x_region_id;
491
492 if (x_region_id is not null) then
493 return x_region_id;
494 else
495 return msd_sr_util.get_null_desc;
496 end if;
497 end if;
498 EXCEPTION
499 when others then return msd_sr_util.get_null_desc;
500 end get_sr_zone_desc;
501
502 FUNCTION get_sr_zone_pk ( p_location_id IN NUMBER,
503 p_zone_attr IN VARCHAR2) return number IS --jarora
504
505 cursor c1 is
506 select wrv.region_id
507 from wsh_region_locations wrl,
508 wsh_zone_regions wzr,
509 wsh_regions_v wrv
510 where wrl.location_id = p_location_id
511 and wrl.region_id = wzr.region_id
512 and wzr.parent_region_id = wrv.region_id
513 and wrv.region_type = 10
514 and decode(nvl(lower(p_zone_attr), '2'), 'attribute1', wrv.attribute1,
515 'attribute2', wrv.attribute2, 'attribute3',wrv.attribute3, 'attribute4',
516 wrv.attribute4, 'attribute5', wrv.attribute5, 'attribute6', wrv.attribute6,
517 'attribute7', wrv.attribute7, 'attribute8', wrv.attribute8, 'attribute9',
518 wrv.attribute9, 'attribute10', wrv.attribute10, 'attribute11', wrv.attribute11,
519 'attribute12', wrv.attribute12, 'attribute13', wrv.attribute13, 'attribute14',
520 wrv.attribute14, 'attribute15', wrv.attribute15, '2') = '1'
521 order by wrv.region_id;
522
523 x_region_id number:= null;
524
525 begin
526
527 if ((p_location_id is null) or (p_zone_attr is null)) then
528 return -777;
529 else
530 open c1;
531 fetch c1 into x_region_id;
532 close c1;
533
534 if (x_region_id is not null) then
535 return x_region_id;
536 else
537 return -777;
538 end if;
539 end if;
540 EXCEPTION
541 when no_data_found then return -777;
542 end get_sr_zone_pk;
543
544 FUNCTION is_txn_depot_repair(p_txn_source_id IN NUMBER) return VARCHAR2 is --jarora
545
546 x_row_num number;
547 cursor c1 is
548 select 1
549 from csd_repair_job_xref crjx
550 where wip_entity_id = p_txn_source_id
551 order by repair_job_xref_id;
552
553 begin
554
555 if (p_txn_source_id is null) then
556 return 'N';
557 else
558 open c1;
559 fetch c1 into x_row_num;
560 close c1;
561
562 if (x_row_num is not null) then
563 return 'Y';
564 else
565 return 'N';
566 end if;
567 end if;
568 EXCEPTION
569 when no_data_found then return 'N';
570 end is_txn_depot_repair;
571
572 FUNCTION get_service_req_org_id (p_txn_source_id IN NUMBER) return NUMBER is --jarora
573 x_org_id number;
574
575 cursor c1 is
576 select organization_id
577 from csd_repair_job_xref crjx
578 where wip_entity_id = p_txn_source_id
579 order by repair_job_xref_id desc;
580
581 begin
582
583 if (p_txn_source_id is null) then
584 return -777;
585 else
586 open c1;
587 fetch c1 into x_org_id;
588 close c1;
589
590 if (x_org_id is not null) then
591 return x_org_id;
592 else
593 return -777;
594 end if;
595 end if;
596 EXCEPTION
597 when no_data_found then return -777;
598 end get_service_req_org_id;
599
600
601 END MSD_DEM_SR_UTIL;
[Home] [Help]