1 PACKAGE BODY EDW_UTIL AS
2 /* $Header: EDWSRGTB.pls 115.15 2003/02/28 02:01:37 rjin ship $ */
3 VERSION CONSTANT CHAR(80) := '$Header: EDWSRGTB.pls 115.15 2003/02/28 02:01:37 rjin ship $';
4
5 FUNCTION get_base_currency( p_organization_id IN NUMBER)
6 RETURN VARCHAR2 IS
7
8 l_currency_code VARCHAR2(15);
9
10 BEGIN
11 SELECT gsob.currency_code
12 INTO l_currency_code
13 FROM hr_all_organization_units hou,
14 hr_organization_information hoi,
15 gl_sets_of_books gsob
16 WHERE hou.organization_id = hoi.organization_id
17 AND ( hoi.org_information_context || '') ='Accounting Information'
18 AND hoi.org_information1 = to_char(gsob.set_of_books_id)
19 AND hou.organization_id = p_organization_id;
20
21 return l_currency_code;
22
23 EXCEPTION when others then
24 return( 'Invalid - ' || p_organization_id);
25
26 END get_base_currency;
27
28 FUNCTION get_item_cost(p_item_id in number,
29 p_org_id in number)
30 return NUMBER IS
31
32 l_cost NUMBER;
33
34 BEGIN
35
36 SELECT NVL(cst.item_cost,0)
37 INTO l_cost
38 FROM cst_cost_types cct,
39 mtl_parameters mtl,
40 cst_item_costs cst
41 WHERE (cst.cost_type_id = cct.cost_type_id
42 OR (cst.cost_type_id = cct.default_cost_type_id
43 AND (NOT EXISTS (SELECT 'Primary Cost Type Row'
44 FROM cst_item_costs cst1
45 WHERE cst1.inventory_item_id = cst.inventory_item_id
46 AND cst1.organization_id = cst.organization_id
47 AND cst1.cost_type_id = cct.cost_type_id))))
48 AND cct.costing_method_type = mtl.primary_cost_method
49 AND cct.cost_type_id = DECODE(mtl.primary_cost_method,1,1,2,2,1)
50 AND mtl.organization_id = p_org_id
51 AND cst.inventory_item_id = p_item_id
52 AND cst.organization_id = mtl.cost_organization_id;
53
54 return(l_cost);
55
56 EXCEPTION
57 WHEN NO_DATA_FOUND THEN
58 return 0;
59
60 END get_item_cost;
61
62 /*
63 FUNCTION get_item_price(p_item_id in number,
64 p_org_id in number,
65 p_price_list_id in number default null,
66 p_currency in varchar2 default null)
67 return NUMBER IS
68
69 l_price number;
70
71 BEGIN
72 SELECT round(list_price *
73 -- (1 - (NVL(FND_PROFILE.Value_Specific('MRP_BIS_AV_DISCOUNT'),0)/100)),
74 (1 - (NVL( fval1.profile_option_value, 0)/100)),
75 NVL(spl.rounding_factor,2))
76 INTO l_price
77 FROM so_price_list_lines sopl,
78 fnd_profile_option_values fval1,
79 fnd_profile_options fpo1,
80 fnd_profile_option_values fval2,
81 fnd_profile_options fpo2,
82 mtl_system_items msi,
83 so_price_lists spl
84 WHERE spl.price_list_id = fval2.profile_option_value
85 -- WHERE spl.price_list_id = FND_PROFILE.Value_Specific('MRP_BIS_PRICE_LIST')
86 AND sopl.price_list_id = spl.price_list_id
87 AND sopl.inventory_item_id = p_item_id
88 AND msi.inventory_item_id = p_item_id
89 AND msi.organization_id = p_org_id
90 AND nvl(sopl.unit_code,' ') = nvl(msi.primary_uom_code,' ')
91 AND sysdate between nvl(sopl.start_date_active, sysdate-1)
92 and nvl(sopl.end_date_active, sysdate+1)
93 AND fval1.profile_option_id = fpo1.profile_option_id
94 AND fval1.application_id = fpo1.application_id
95 AND fval1.level_id = 10001 -- Site level
96 AND fval1.level_value = 0
97 AND nvl(fpo1.end_date_active,sysdate) >= sysdate
98 AND fpo1.start_date_active <= sysdate
99 AND fpo1.profile_option_name = 'MRP:MRP_BIS_AV_DISCOUNT'
100 AND fval2.profile_option_id = fpo2.profile_option_id
101 AND fval2.application_id = fpo2.application_id
102 AND fval2.level_id = 10001 -- Site level
103 AND fval2.level_value = 0
104 AND nvl(fpo2.end_date_active,sysdate) >= sysdate
105 AND fpo2.start_date_active <= sysdate
106 AND fpo2.profile_option_name = 'MRP:MRP_BISPRICE_LIST'
107 AND rownum = 1;
108
109 return l_price;
110
111 EXCEPTION
112 WHEN NO_DATA_FOUND THEN
113 return EDW_UTIL.get_item_cost(p_item_id, p_org_id);
114
115 END get_item_price;
116 */
117
118 FUNCTION get_est_ship_date (p_disposition_id IN NUMBER,
119 p_organization_id IN NUMBER,
120 p_compile_designator IN VARCHAR2)
121 RETURN DATE IS
122
123 l_days NUMBER;
124
125 BEGIN
126
127 SELECT max(rec.new_schedule_date - peg2.demand_date)
128 INTO l_days
129 FROM mrp_gross_requirements greq,
130 mrp_recommendations rec,
131 mrp_full_pegging peg1,
132 mrp_full_pegging peg2
133 WHERE greq.disposition_id = p_disposition_id
134 AND peg1.demand_id = greq.demand_id
135 AND peg1.transaction_id = rec.transaction_id
136 AND peg1.pegging_id = peg2.end_pegging_id
137 AND peg2.organization_id = p_organization_id
138 AND peg2.compile_designator = p_compile_designator;
139
140
141 RETURN sysdate;
142
143 EXCEPTION
144
145 WHEN OTHERS THEN
146
147 RETURN sysdate;
148
149 END get_est_ship_date;
150
151 FUNCTION get_party_id(p_sold_to_org_id IN NUMBER)
152 RETURN NUMBER IS
153 l_party_id NUMBER;
154 BEGIN
155
156 SELECT hca.party_id
157 INTO l_party_id
158 FROM
159 hz_cust_accounts hca
160 WHERE
161 hca.cust_account_id = p_sold_to_org_id;
162
163 RETURN l_party_id;
164
165 EXCEPTION
166 WHEN NO_DATA_FOUND THEN
167 return to_number(NULL);
168 END get_party_id;
169
170 FUNCTION get_line_detail_count(p_line_id IN NUMBER)
171 RETURN NUMBER IS
172 l_count NUMBER;
173 BEGIN
174
175 SELECT count(*)
176 INTO l_count
177 FROM
178 wsh_delivery_details wdd
179 WHERE
180 wdd.SOURCE_LINE_ID = p_line_id
181 AND wdd.inv_interfaced_flag = 'Y'
182 and wdd.SOURCE_CODE = 'OE';
183
184 /* just in case, there is bad data */
185 IF l_count = 0 THEN
186 l_count := 1;
187 END IF;
188
189 RETURN l_count;
190
191 EXCEPTION
192 WHEN NO_DATA_FOUND THEN
193 return 1;
194 END get_line_detail_count;
195
196 FUNCTION get_pto_mmt_count(p_line_id IN NUMBER)
197 RETURN NUMBER IS
198 l_count NUMBER;
199
200 BEGIN
201
202 select count(*) INTO l_count
203 from wsh_delivery_details wdd,
204 oe_order_lines_all l
205 where l.top_model_line_id = p_line_id
206 and wdd.source_line_id = l.line_id
207 AND wdd.inv_interfaced_flag = 'Y'
208 and wdd.SOURCE_CODE = 'OE';
209
210 /*
211 SELECT count(*)
212 INTO l_count
213 FROM
214 mtl_material_transactions
215 WHERE
216 trx_source_line_id = p_line_id;
217 */
218
219 /* just in case, there is bad data */
220 IF l_count = 0 THEN
221 l_count := 1;
222 END IF;
223
224 RETURN l_count;
225
226 EXCEPTION
227 WHEN NO_DATA_FOUND THEN
228 return 1;
229 END get_pto_mmt_count;
230
231
232 FUNCTION get_wh_global_currency RETURN VARCHAR2 IS
233 v_currency_code VARCHAR2(20);
234 BEGIN
235
236 SELECT warehouse_currency_code
237 INTO v_currency_code
238 FROM edw_local_system_parameters;
239
240 RETURN (v_currency_code);
241
242 EXCEPTION
243 WHEN NO_DATA_FOUND THEN
244 RETURN('UNKNOWN');
245
246 END get_wh_global_currency;
247
248 FUNCTION get_rep_sched_scrapped_qty(rep_sched_id number,
249 organization_id number) RETURN NUMBER IS
250 l_scrapped number:=0;
251 BEGIN
252 begin
253 select sum(wo.quantity_scrapped) into
254 l_scrapped
255 from wip_operations wo
256 where
257 wo.repetitive_schedule_id=rep_sched_id
258 and wo.organization_id=organization_id;
259 exception when others then
260 l_scrapped:=0;
261 end;
262
263 return l_scrapped;
264 END get_rep_sched_scrapped_qty;
265
266 FUNCTION get_app_info return VARCHAR2 is
267 l_dummy1 Varchar2(32);
268 l_dummy2 Varchar2(32);
269 l_retval Boolean;
270 l_bis_owner Varchar2(32):='BIS';
271
272 begin
273 l_retval := FND_INSTALLATION.GET_APP_INFO('BIS',
274 l_dummy1, l_dummy2,
275 l_bis_owner);
276 return l_bis_owner;
277 exception when others then
278 return l_bis_owner;
279
280 end get_app_info ;
281
282 function get_base_transaction_value(p_transaction_id number,
283 p_transfer_transaction_id number,
284 p_action_id number,
285 p_quantity number) RETURN NUMBER IS
286 l_value number;
287 begin
288
289 select sum(BASE_TRANSACTION_VALUE) into l_value
290 from
291 MTL_TRANSACTION_ACCOUNTS
292 where
293 ACCOUNTING_LINE_TYPE=1
294 and sign(primary_quantity) = decode(p_action_id,2,
295 sign(p_quantity),
296 sign(primary_quantity))
297 and TRANSACTION_ID = decode(p_action_id,
298 2, decode(sign(p_quantity),
299 -1,p_transaction_id,
300 p_transfer_transaction_id),
301 3, decode(sign(p_quantity),
302 -1,p_transaction_id,
303 p_transfer_transaction_id),
304 p_transaction_id);
305
306 return nvl(l_value,0);
307
308 exception when others then
309 return 0;
310 end get_base_transaction_value;
311
312 /******************************************************************************
313 * Convert transactional UOM to EDW Base UOM
314 ******************************************************************************/
315 FUNCTION get_edw_base_uom(p_uom_code IN VARCHAR2,
316 p_inventory_id IN NUMBER)
317 RETURN VARCHAR2 IS
318 l_inventory_id NUMBER;
319 l_edw_uom_code VARCHAR2(20);
320 CURSOR global_uom_c IS
321 SELECT UOM_EDW_BASE_UOM
322 FROM EDW_MTL_LOCAL_UOM_M
323 WHERE
324 UOM_EDW_UOM_PK = p_uom_code
325 AND UOM_global_flag = 'Y';
326 CURSOR uom_c IS
327 SELECT edw_base_uom_fk
328 FROM OPI_EDW_LOCAL_UOM_CONV_F, edw_local_instance
329 WHERE ((l_inventory_id IS NULL OR l_inventory_id = 0)
330 AND uom_conv_pk = 'STANDARD'||'-'|| p_uom_code||'-'|| instance_code)
331 OR (l_inventory_id <> 0
332 AND uom_conv_pk = l_inventory_id||'-'||p_uom_code||'-'||instance_code);
333 BEGIN
334 l_inventory_id := p_inventory_id;
335 OPEN global_uom_c;
336 FETCH global_uom_c INTO l_edw_uom_code;
337 IF global_uom_c%NOTFOUND THEN
338 CLOSE global_uom_c;
339 OPEN uom_c;
340 FETCH uom_c INTO l_edw_uom_code;
341 IF uom_c%NOTFOUND AND l_inventory_id <> 0 THEN
342 l_inventory_id := 0;
343 CLOSE uom_c;
344 -- no item specific uom - getting STANDARD uom
345 OPEN uom_c;
346 FETCH uom_c INTO l_edw_uom_code;
347 END IF;
348 IF uom_c%NOTFOUND THEN
349 -- Fix for Bug# 1844559
350 -- RETURN 'NA_EDW';
351 RETURN 'Invalid - '||p_uom_code ;
352 ELSE
353 RETURN l_edw_uom_code;
354 END IF;
355 ELSE
356 RETURN l_edw_uom_code;
357 END IF;
358 EXCEPTION WHEN OTHERS THEN
359 RETURN('Invalid - '||p_uom_code);
360 END get_edw_base_uom;
361
362 /********************************************************************************************************
363 * Convert transactional UOM to EDW Base UOM (overloaded version that accepts Instance_Code as Parameter)
364 *******************************************************************************************************/
365
366
367 FUNCTION get_edw_base_uom(p_uom_code IN VARCHAR2,
368 p_inventory_id IN NUMBER, p_instance_code IN VARCHAR2)
369 RETURN VARCHAR2 IS
370 l_inventory_id NUMBER;
371 l_edw_uom_code VARCHAR2(20);
372 CURSOR global_uom_c IS
373 SELECT UOM_EDW_BASE_UOM
374 FROM EDW_MTL_LOCAL_UOM_M
375 WHERE
376 UOM_EDW_UOM_PK = p_uom_code
377 AND UOM_global_flag = 'Y';
378 CURSOR uom_c IS
379 SELECT edw_base_uom_fk
380 FROM OPI_EDW_LOCAL_UOM_CONV_F
381 WHERE ((l_inventory_id IS NULL OR l_inventory_id = 0)
382 AND uom_conv_pk = 'STANDARD'||'-'|| p_uom_code||'-'||p_instance_code)
383 OR (l_inventory_id <> 0
384 AND uom_conv_pk = l_inventory_id||'-'||p_uom_code||'-'||p_instance_code);
385 BEGIN
386 l_inventory_id := p_inventory_id;
387 OPEN global_uom_c;
388 FETCH global_uom_c INTO l_edw_uom_code;
389 IF global_uom_c%NOTFOUND THEN
390 CLOSE global_uom_c;
391 OPEN uom_c;
392 FETCH uom_c INTO l_edw_uom_code;
393 IF uom_c%NOTFOUND AND l_inventory_id <> 0 THEN
394 l_inventory_id := 0;
395 CLOSE uom_c;
396 -- no item specific uom - getting STANDARD uom
397 OPEN uom_c;
398 FETCH uom_c INTO l_edw_uom_code;
399 END IF;
400 IF uom_c%NOTFOUND THEN
401 -- Fix for Bug# 1844559
402 -- RETURN 'NA_EDW';
403 RETURN 'Invalid - '||p_uom_code ;
404 ELSE
405 RETURN l_edw_uom_code;
406 END IF;
407 ELSE
408 RETURN l_edw_uom_code;
409 END IF;
410 EXCEPTION WHEN OTHERS THEN
411 RETURN('Invalid - '||p_uom_code);
412 END get_edw_base_uom;
413
414
415 /******************************************************************************
416 * Get conversion_rate for conversion from transactional UOM to EDW Base UOM
417 ******************************************************************************/
418 FUNCTION get_uom_conv_rate(p_uom_code IN VARCHAR2,
419 p_inventory_id IN NUMBER)
420 RETURN NUMBER IS
421 l_inventory_id NUMBER;
422 l_edw_conv_rate NUMBER;
423
424 CURSOR global_uom_c IS
425 SELECT UOM_conversion_rate
426 FROM EDW_MTL_LOCAL_UOM_M
427 WHERE
428 UOM_EDW_UOM_PK = p_uom_code
429 AND UOM_global_flag = 'Y';
430 CURSOR uom_c IS
431 SELECT EDW_CONVERSION_RATE
432 FROM OPI_EDW_LOCAL_UOM_CONV_F, edw_local_instance
433 WHERE
434 CLASS_CONVERSION_FLAG = 'N' AND (
435 ((l_inventory_id IS NULL OR l_inventory_id = 0)
436 AND uom_conv_pk = 'STANDARD'||'-'|| p_uom_code||'-'|| instance_code)
437 OR (l_inventory_id <> 0
438 AND uom_conv_pk = l_inventory_id||'-'||p_uom_code||'-'||instance_code) );
439 BEGIN
440 l_inventory_id := p_inventory_id;
441 OPEN global_uom_c;
442 FETCH global_uom_c INTO l_edw_conv_rate;
443 IF global_uom_c%NOTFOUND THEN
444 CLOSE global_uom_c;
445 OPEN uom_c;
446 FETCH uom_c INTO l_edw_conv_rate;
447 IF uom_c%NOTFOUND AND l_inventory_id <> 0 THEN
448 l_inventory_id := 0;
449 CLOSE uom_c;
450 -- no item specific uom. Getting STANDARD uom
451 OPEN uom_c;
452 FETCH uom_c INTO l_edw_conv_rate;
453 END IF;
454 IF uom_c%NOTFOUND THEN
455 RETURN 'NA_EDW';
456 ELSE
460 RETURN l_edw_conv_rate;
457 RETURN l_edw_conv_rate;
458 END IF;
459 ELSE
461 END IF;
462 EXCEPTION WHEN OTHERS THEN
463 RETURN(NULL);
464
465 END get_uom_conv_rate;
466
467
468 /****************************************************************************************************************************************
469 * Get conversion_rate for conversion from transactional UOM to EDW Base UOM (overloaded version that accepts Instance_Code as Parameter)
470 ***************************************************************************************************************************************/
471
472 FUNCTION get_uom_conv_rate(p_uom_code IN VARCHAR2,
473 p_inventory_id IN NUMBER, p_instance_code IN VARCHAR2)
474 RETURN NUMBER IS
475 l_inventory_id NUMBER;
476 l_edw_conv_rate NUMBER;
477
478 CURSOR global_uom_c IS
479 SELECT UOM_conversion_rate
480 FROM EDW_MTL_LOCAL_UOM_M
481 WHERE
482 UOM_EDW_UOM_PK = p_uom_code
483 AND UOM_global_flag = 'Y';
484 CURSOR uom_c IS
485 SELECT EDW_CONVERSION_RATE
486 FROM OPI_EDW_LOCAL_UOM_CONV_F
487 WHERE
488 CLASS_CONVERSION_FLAG = 'N' AND (
489 ((l_inventory_id IS NULL OR l_inventory_id = 0)
490 AND uom_conv_pk = 'STANDARD'||'-'|| p_uom_code||'-'|| p_instance_code )
491 OR (l_inventory_id <> 0
492 AND uom_conv_pk = l_inventory_id||'-'||p_uom_code||'-'||p_instance_code) );
493 BEGIN
494 l_inventory_id := p_inventory_id;
495 OPEN global_uom_c;
496 FETCH global_uom_c INTO l_edw_conv_rate;
497 IF global_uom_c%NOTFOUND THEN
498 CLOSE global_uom_c;
499 OPEN uom_c;
500 FETCH uom_c INTO l_edw_conv_rate;
501 IF uom_c%NOTFOUND AND l_inventory_id <> 0 THEN
502 l_inventory_id := 0;
503 CLOSE uom_c;
504 -- no item specific uom. Getting STANDARD uom
505 OPEN uom_c;
506 FETCH uom_c INTO l_edw_conv_rate;
507 END IF;
508 IF uom_c%NOTFOUND THEN
509 RETURN 'NA_EDW';
510 ELSE
511 RETURN l_edw_conv_rate;
512 END IF;
513 ELSE
514 RETURN l_edw_conv_rate;
515 END IF;
516 EXCEPTION WHEN OTHERS THEN
517 RETURN(NULL);
518
519 END get_uom_conv_rate;
520
521 /************************************************************************************
522 * Get conversion_rate for conversion from one EDW Base UOM to another EDW Base UOM
523 ************************************************************************************/
524
525 FUNCTION get_uom_conv_rate(p_inventory_id IN NUMBER,
526 p_from_edw_base_uom_code IN VARCHAR2,
527 p_to_edw_base_uom_code IN VARCHAR2)
528 RETURN NUMBER IS
529 l_inventory_id NUMBER;
530 l_edw_conv_rate NUMBER;
531
532 CURSOR uom_c IS
533 SELECT EDW_CONVERSION_RATE
534 FROM OPI_EDW_LOCAL_UOM_CONV_F, edw_local_instance
535 WHERE
536 CLASS_CONVERSION_FLAG = 'Y' AND (
537 ((l_inventory_id IS NULL OR l_inventory_id = 0)
538 AND uom_conv_pk = 'STANDARD'||'-'|| p_from_edw_base_uom_code||'-'||
539 p_to_edw_base_uom_code||'-'||instance_code)
540 OR (l_inventory_id <> 0
541 AND uom_conv_pk = l_inventory_id||'-'|| p_from_edw_base_uom_code||'-'||
542 p_to_edw_base_uom_code||'-'||instance_code) );
543
544 BEGIN
545 l_inventory_id := p_inventory_id;
546 OPEN uom_c;
547 FETCH uom_c INTO l_edw_conv_rate;
548 IF uom_c%NOTFOUND AND l_inventory_id <> 0 THEN
549 l_inventory_id := 0;
550 CLOSE uom_c;
551 -- no item specific uom. Getting STANDARD uom
552 OPEN uom_c;
553 FETCH uom_c INTO l_edw_conv_rate;
554 END IF;
555 IF uom_c%NOTFOUND THEN
556 CLOSE uom_c;
557 RETURN 'NA_EDW';
558 ELSE
559 CLOSE uom_c;
560 RETURN l_edw_conv_rate;
561 END IF;
562 EXCEPTION WHEN OTHERS THEN
563 RETURN(NULL);
564
565 END get_uom_conv_rate;
566
567
568 /***********************************************************************************************************************************************
569 * Get conversion_rate for conversion from one EDW Base UOM to another EDW Base UOM (overloaded version that accepts Instance_Code as Parameter)
570 **********************************************************************************************************************************************/
571
572 FUNCTION get_uom_conv_rate(p_inventory_id IN NUMBER,
573 p_from_edw_base_uom_code IN VARCHAR2,
574 p_to_edw_base_uom_code IN VARCHAR2, p_instance_code IN VARCHAR2)
575 RETURN NUMBER IS
576 l_inventory_id NUMBER;
577 l_edw_conv_rate NUMBER;
578
579 CURSOR uom_c IS
580 SELECT EDW_CONVERSION_RATE
581 FROM OPI_EDW_LOCAL_UOM_CONV_F
582 WHERE
583 CLASS_CONVERSION_FLAG = 'Y' AND (
587 OR (l_inventory_id <> 0
584 ((l_inventory_id IS NULL OR l_inventory_id = 0)
585 AND uom_conv_pk = 'STANDARD'||'-'|| p_from_edw_base_uom_code||'-'||
586 p_to_edw_base_uom_code||'-'||p_instance_code)
588 AND uom_conv_pk = l_inventory_id||'-'|| p_from_edw_base_uom_code||'-'||
589 p_to_edw_base_uom_code||'-'||p_instance_code) );
590
591 BEGIN
592 l_inventory_id := p_inventory_id;
593 OPEN uom_c;
594 FETCH uom_c INTO l_edw_conv_rate;
595 IF uom_c%NOTFOUND AND l_inventory_id <> 0 THEN
596 l_inventory_id := 0;
597 CLOSE uom_c;
598 -- no item specific uom. Getting STANDARD uom
599 OPEN uom_c;
600 FETCH uom_c INTO l_edw_conv_rate;
601 END IF;
602 IF uom_c%NOTFOUND THEN
603 CLOSE uom_c;
604 RETURN 'NA_EDW';
605 ELSE
606 CLOSE uom_c;
607 RETURN l_edw_conv_rate;
608 END IF;
609 EXCEPTION WHEN OTHERS THEN
610 RETURN(NULL);
611
612 END get_uom_conv_rate;
613
614 /******************************************************************************
615 * Get EDW UOM corresponding to a transactional UOM
616 ******************************************************************************/
617 FUNCTION get_edw_uom(p_uom_code IN VARCHAR2,
618 p_inventory_id IN NUMBER)
619 RETURN VARCHAR2 IS
620 l_inventory_id NUMBER;
621 l_edw_uom_code VARCHAR2(20);
622 CURSOR global_uom_c IS
623 SELECT uom_edw_uom_pk
624 FROM EDW_MTL_LOCAL_UOM_M
625 WHERE
626 UOM_EDW_UOM_PK = p_uom_code
627 AND UOM_global_flag = 'Y';
628 CURSOR uom_c IS
629 SELECT edw_uom_fk
630 FROM OPI_EDW_LOCAL_UOM_CONV_F, edw_local_instance
631 WHERE ((l_inventory_id IS NULL OR l_inventory_id = 0)
632 AND uom_conv_pk = 'STANDARD'||'-'|| p_uom_code||'-'|| instance_code)
633 OR (l_inventory_id <> 0
634 AND uom_conv_pk = l_inventory_id||'-'||p_uom_code||'-'||instance_code);
635 BEGIN
636 l_inventory_id := p_inventory_id;
637 OPEN global_uom_c;
638 FETCH global_uom_c INTO l_edw_uom_code;
639 IF global_uom_c%NOTFOUND THEN
640 CLOSE global_uom_c;
641 OPEN uom_c;
642 FETCH uom_c INTO l_edw_uom_code;
643 IF uom_c%NOTFOUND AND l_inventory_id <> 0 THEN
644 l_inventory_id := 0;
645 CLOSE uom_c;
646 -- no item specific uom - getting STANDARD uom
647 OPEN uom_c;
648 FETCH uom_c INTO l_edw_uom_code;
649 END IF;
650 IF uom_c%NOTFOUND THEN
651 -- Fix for Bug# 1844559
652 -- RETURN 'NA_EDW';
653 RETURN 'Invalid - '||p_uom_code ;
654 ELSE
655 RETURN l_edw_uom_code;
656 END IF;
657 ELSE
658 RETURN l_edw_uom_code;
659 END IF;
660 EXCEPTION WHEN OTHERS THEN
661 RETURN('Invalid - '||p_uom_code);
662 END get_edw_uom;
663
664
665 /***************************************************************************************************************
666 * Get EDW UOM corresponding to a transactional UOM (overloaded version that accepts Instance_Code as Parameter)
667 **************************************************************************************************************/
668 FUNCTION get_edw_uom(p_uom_code IN VARCHAR2,
669 p_inventory_id IN NUMBER, p_instance_code IN VARCHAR2)
670 RETURN VARCHAR2 IS
671 l_inventory_id NUMBER;
672 l_edw_uom_code VARCHAR2(20);
673 CURSOR global_uom_c IS
674 SELECT uom_edw_uom_pk
675 FROM EDW_MTL_LOCAL_UOM_M
676 WHERE
677 UOM_EDW_UOM_PK = p_uom_code
678 AND UOM_global_flag = 'Y';
679 CURSOR uom_c IS
680 SELECT edw_uom_fk
681 FROM OPI_EDW_LOCAL_UOM_CONV_F
682 WHERE ((l_inventory_id IS NULL OR l_inventory_id = 0)
683 AND uom_conv_pk = 'STANDARD'||'-'|| p_uom_code||'-'|| p_instance_code)
684 OR (l_inventory_id <> 0
685 AND uom_conv_pk = l_inventory_id||'-'||p_uom_code||'-'||p_instance_code);
686 BEGIN
687 l_inventory_id := p_inventory_id;
688 OPEN global_uom_c;
689 FETCH global_uom_c INTO l_edw_uom_code;
690 IF global_uom_c%NOTFOUND THEN
691 CLOSE global_uom_c;
692 OPEN uom_c;
693 FETCH uom_c INTO l_edw_uom_code;
694 IF uom_c%NOTFOUND AND l_inventory_id <> 0 THEN
695 l_inventory_id := 0;
696 CLOSE uom_c;
697 -- no item specific uom - getting STANDARD uom
698 OPEN uom_c;
699 FETCH uom_c INTO l_edw_uom_code;
700 END IF;
701 IF uom_c%NOTFOUND THEN
702 -- Fix for Bug# 1844559
703 -- RETURN 'NA_EDW';
704 RETURN 'Invalid - '||p_uom_code ;
705 ELSE
706 RETURN l_edw_uom_code;
707 END IF;
708 ELSE
709 RETURN l_edw_uom_code;
710 END IF;
711 EXCEPTION WHEN OTHERS THEN
712 RETURN('Invalid - '||p_uom_code);
713 END get_edw_uom;
714
715 /******************************************************************************
716 * Get the required Id. based on p_type being 1 - 5
717 ******************************************************************************/
718 -- --------------------------------------------------------------
719 -- For Operating Unit p_type = 1,
720 -- For Business Unit p_type = 2,
721 -- For Legal Entity p_type = 3,
722 -- For Set of Books p_type = 4
723 -- For Chart of Acct. p_type = 5
724 -- --------------------------------------------------------------
725
726 FUNCTION bus_unit_id (p_type IN NUMBER,
727 p_organization_id IN NUMBER) return NUMBER IS
728
729 l_bus_unit_id NUMBER;
730
731 cursor c is
732 SELECT decode(p_type,
733 1,DECODE(FPG.MULTI_ORG_FLAG,'Y',TO_NUMBER(HOI.ORG_INFORMATION3), NULL),
734 2,HOU.BUSINESS_GROUP_ID,
735 3,TO_NUMBER(HOI.ORG_INFORMATION2),
736 4,GSOB.SET_OF_BOOKS_ID,
737 5,GSOB.CHART_OF_ACCOUNTS_ID)
738 FROM
739 HR_all_ORGANIZATION_UNITS HOU,
740 HR_ORGANIZATION_INFORMATION HOI,
741 GL_SETS_OF_BOOKS GSOB,
742 FND_PRODUCT_GROUPS FPG
743 WHERE
744 HOU.ORGANIZATION_ID = HOI.ORGANIZATION_ID
745 AND (HOI.ORG_INFORMATION_CONTEXT || '') ='Accounting Information'
746 AND HOI.ORG_INFORMATION1 = TO_CHAR(GSOB.SET_OF_BOOKS_ID)
747 AND HOU.organization_id = p_organization_id;
748 BEGIN
749 l_bus_unit_id := -1;
750
751 OPEN c;
752 FETCH c INTO l_bus_unit_id;
753 CLOSE c;
754
755 return l_bus_unit_id;
756
757
758 EXCEPTION when others then
759 close c;
760 return -1;
761
762 END bus_unit_id;
763
764 END EDW_UTIL;
765
[Home] [Help]