1 PACKAGE BODY inv_convert AS
2 /* $Header: INVUMCNB.pls 120.2.12010000.2 2008/07/29 13:47:55 ptkumar ship $ */
3 -- Bug # 3144743
4 -- Put away Performance Issue
5
6 g_u_uom_rate number;
7 g_u_from_unit varchar2(10);
8 g_u_to_unit varchar2(10);
9 g_u_item_id varchar2(10);
10
11 g_v_uom_rate number;
12 g_v_from_unit varchar2(10);
13 g_v_to_unit varchar2(10);
14 g_v_item_id varchar2(10);
15
16 g_w_uom_rate number;
17 g_w_from_unit varchar2(10);
18 g_w_to_unit varchar2(10);
19 g_w_item_id varchar2(10);
20
21 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_CONVERT';
22 g_pkg_version CONSTANT VARCHAR2(100) := '$Header: INVUMCNB.pls 120.2.12010000.2 2008/07/29 13:47:55 ptkumar ship $';
23
24 PROCEDURE inv_um_conversion (
25 from_unit varchar2,
26 to_unit varchar2,
27 item_id number,
28 uom_rate out nocopy number )
29 IS
30
31 BEGIN
32
33 inv_um_conversion (
34 from_unit,
35 to_unit,
36 item_id,
37 NULL,
38 NULL,
39 uom_rate);
40
41
42 END inv_um_conversion;
43
44
45 PROCEDURE inv_um_conversion (
46 from_unit varchar2,
47 to_unit varchar2,
48 item_id number,
49 lot_number varchar2,
50 organization_id number,
51 uom_rate out nocopy number )
52 IS
53
54 /*
55 ** declare variables that are referenced in the cursor definitions
56 */
57
58 from_class varchar2(10);
59 to_class varchar2(10);
60
61 /*===============================================
62 Joe DiIorio 09/2004 INVCONV
63 Added variable to use lot_number in cursor
64 lot_interclass_conversions.
65 ===============================================*/
66 p_lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE;
67 p_organization_id NUMBER;
68
69 -- Bug 2899727. Since there is no join between t and f,
70 -- leads to a cartesian product.
71 -- So, splitting the cursor into two different sqls.
72
73 /***
74 cursor standard_conversions is
75 select t.conversion_rate std_to_rate,
76 t.uom_class std_to_class,
77 f.conversion_rate std_from_rate,
78 f.uom_class std_from_class
79 from mtl_uom_conversions t,
80 mtl_uom_conversions f
81 where t.inventory_item_id in (item_id, 0)
82 and t.uom_code = to_unit
83 and nvl(t.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
84 and f.inventory_item_id in (item_id, 0)
85 and f.uom_code = from_unit
86 and nvl(f.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
87 order by t.inventory_item_id desc,
88 f.inventory_item_id desc;
89
90 std_rec standard_conversions%rowtype;
91
92 *****/
93
94 cursor from_standard_conversions is
95 select conversion_rate std_from_rate,
96 uom_class std_from_class
97 from mtl_uom_conversions
98 where inventory_item_id in (item_id, 0)
99 and uom_code = from_unit
100 and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate)
101 order by inventory_item_id desc;
102
103 from_std_rec from_standard_conversions%rowtype;
104
105 cursor to_standard_conversions is
106 select conversion_rate std_to_rate,
107 uom_class std_to_class
108 from mtl_uom_conversions
109 where inventory_item_id in (item_id, 0)
110 and uom_code = to_unit
111 and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate)
112 order by inventory_item_id desc;
113
114 to_std_rec to_standard_conversions%rowtype;
115
116
117 cursor interclass_conversions is
118 select decode(to_uom_class, to_class, 1, 2) to_flag,
119 decode(from_uom_class, from_class, 1, to_class, 2, 0) from_flag,
120 conversion_rate rate
121 from mtl_uom_class_conversions
122 where inventory_item_id = item_id
123 and to_uom_class in (from_class, to_class)
124 and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate);
125
126 class_rec interclass_conversions%rowtype;
127
128
129 /*===============================================
130 Joe DiIorio 09/2004 INVCONV
131 Cursor added for lot specific interclass./
132 ===============================================*/
133
134 cursor lot_interclass_conversions is
135 select decode(to_uom_class, to_class, 1, 2) to_flag,
136 decode(from_uom_class, from_class, 1, to_class, 2, 0) from_flag,
137 conversion_rate rate
138 from mtl_lot_uom_class_conversions
139 where inventory_item_id = item_id
140 and organization_id = p_organization_id
141 and lot_number = p_lot_number
142 and to_uom_class in (from_class, to_class)
143 and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate);
144
145 /*===============================================
146 Added record type for the above cursor.
147 ===============================================*/
148 lot_class_rec lot_interclass_conversions%rowtype;
149
150 invalid_conversion exception;
151
152 type conv_tab is table of number
153 index by binary_integer;
154
155 type class_tab is table of varchar2(10)
156 index by binary_integer;
157
158 interclass_rate_tab conv_tab;
159 from_class_flag_tab conv_tab;
160 to_class_flag_tab conv_tab;
161 from_rate_tab conv_tab;
162 to_rate_tab conv_tab;
163 from_class_tab class_tab;
164 to_class_tab class_tab;
165
166 std_index number;
167 class_index number;
168
169 from_rate number := 1;
170 to_rate number := 1;
171 interclass_rate number := 1;
172 to_class_rate number := 1;
173 from_class_rate number := 1;
174 msgbuf varchar2(200);
175
176 begin
177
178 /*
179 ** Conversion between between two UOMS.
180 **
181 ** 1. The conversion always starts from the conversion defined, if exists,
182 ** for an specified item.
183 ** 2. If the conversion id not defined for that specific item, then the
184 ** standard conversion, which is defined for all items, is used.
185 ** 3. When the conversion involves two different classes, then
186 ** interclass conversion is activated.
187 */
188
189
190 /*
191 ** If from and to units are the same, conversion rate is 1.
192 ** Go immediately to the end of the procedure to exit.
193 */
194
195
196 if (from_unit = to_unit) then
197
198 uom_rate := 1;
199 goto procedure_end;
200
201 end if;
202
203 /*=======================================
204 Joe DiIorio 09/2004 INVCONV
205 Copy input variables.
206 =====================================*/
207 p_lot_number := lot_number;
208 p_organization_id := organization_id;
209
210 /*
211 ** Get item specific or standard conversions
212 */
213
214 open from_standard_conversions;
215
216 std_index := 0;
217
218 loop
219
220 fetch from_standard_conversions into from_std_rec;
221 exit when from_standard_conversions%notfound;
222
223 std_index := std_index + 1;
224
225 from_rate_tab(std_index) := from_std_rec.std_from_rate;
226 from_class_tab(std_index) := from_std_rec.std_from_class;
227
228 end loop;
229
230 close from_standard_conversions;
231
232 if (std_index = 0) then
233
234 /*
235 ** No conversions defined
236 */
237
238 msgbuf := msgbuf||'Invalid standard conversion : ';
239 msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
240 msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
241 raise invalid_conversion;
242
243 else
244
245 /*
246 ** Conversions are ordered. Item specific conversions will be
247 ** returned first.
248 */
249
250 from_class := from_class_tab(1);
251 from_rate := from_rate_tab(1);
252
253 end if;
254
255 open to_standard_conversions;
256
257 std_index := 0;
258
259 loop
260
261 fetch to_standard_conversions into to_std_rec;
262 exit when to_standard_conversions%notfound;
263
264 std_index := std_index + 1;
265
266 to_rate_tab(std_index) := to_std_rec.std_to_rate;
267 to_class_tab(std_index) := to_std_rec.std_to_class;
268
269 end loop;
270
271 close to_standard_conversions;
272
273 if (std_index = 0) then
274
275 /*
276 ** No conversions defined
277 */
278
279 msgbuf := msgbuf||'Invalid standard conversion : ';
280 msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
281 msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
282 raise invalid_conversion;
283
284 else
285
286 /*
287 ** Conversions are ordered. Item specific conversions will be
288 ** returned first.
289 */
290
291 to_class := to_class_tab(1);
292 to_rate := to_rate_tab(1);
293
294 end if;
295
296 /******
297
298 -- BUG 2899727. Commenting this portion of the code. The check is
299 -- being done after both the cursons above.
300
301 --if (std_index = 0) then
302
303 -- /*
304 -- ** No conversions defined
305 -- */
306
307 -- msgbuf := msgbuf||'Invalid standard conversion : ';
308 -- msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
309 -- msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
310 -- raise invalid_conversion;
311
312 --else
313
314 -- /*
315 -- ** Conversions are ordered. Item specific conversions will be
316 -- ** returned first.
317 -- */
318
319 -- from_class := from_class_tab(1);
320 -- to_class := to_class_tab(1);
321 -- from_rate := from_rate_tab(1);
322 -- to_rate := to_rate_tab(1);
323
324 -- end if;
325 -- End bug 2899727.
326
327 if (from_class <> to_class) then
328 -- Bug 5447516 If item_id is 0 raise an error as intercalss conversions can be
329 -- defined only at item level.
330 if item_id = 0 then
331 raise invalid_conversion;
332 end if;
333 class_index := 0;
334
335 /*=======================================
336 Joe DiIorio 09/2004 INVCONV
337 If there is a lot number try and get
338 the lot specific conversion first.
339 =====================================*/
340
341 IF (lot_number is NOT NULL AND organization_id IS NOT NULL) THEN
342 open lot_interclass_conversions;
343 LOOP
344 FETCH lot_interclass_conversions INTO lot_class_rec;
345 EXIT WHEN lot_interclass_conversions%NOTFOUND;
346 class_index := class_index + 1;
347 to_class_flag_tab(class_index) := lot_class_rec.to_flag;
348 from_class_flag_tab(class_index) := lot_class_rec.from_flag;
349 interclass_rate_tab(class_index) := lot_class_rec.rate;
350 END LOOP;
351 close lot_interclass_conversions;
352
353 END IF;
354
355
356 /*
357 ** Load interclass conversion tables
358 ** If two rows are returned, it implies that there is no direct
359 ** conversion between them.
360 ** If one row is returned, then it may imply that there is a direct
361 ** conversion between them or one class is not defined in the
362 ** class conversion table.
363 */
364
365 /* check interclass first */
366
367 IF (class_index = 0) THEN
368 open interclass_conversions;
369
370 loop
371
372 fetch interclass_conversions into class_rec;
373 exit when interclass_conversions%notfound;
374
375 class_index := class_index + 1;
376
377 to_class_flag_tab(class_index) := class_rec.to_flag;
378 from_class_flag_tab(class_index) := class_rec.from_flag;
379 interclass_rate_tab(class_index) := class_rec.rate;
380
381 end loop;
382
383 close interclass_conversions;
384
385 END IF;
386
387
388
389 if (class_index = 2) then
390
391 if (to_class_flag_tab(1) = 1) then
392
393 to_class_rate := interclass_rate_tab(1);
394 from_class_rate := interclass_rate_tab(2);
395
396 else
397
398 to_class_rate := interclass_rate_tab(2);
399 from_class_rate := interclass_rate_tab(1);
400
401 end if;
402
403 --Bug 2907403
404 interclass_rate := from_class_rate/to_class_rate;
405
406 elsif ((class_index = 1) and
407 (to_class_flag_tab(1) = from_class_flag_tab(1) )) then
408
409
410 if (to_class_flag_tab(1) = 1) then
411
412 to_class_rate := interclass_rate_tab(1);
413 from_class_rate := 1;
414
415 else
416
417 to_class_rate := 1;
418 from_class_rate := interclass_rate_tab(1);
419
420 end if;
421
422
423 interclass_rate := from_class_rate/to_class_rate;
424
425 else
426
427 /*
428 ** No interclass conversion is defined
429 */
430
431 msgbuf := msgbuf||'Invalid Interclass conversion : ';
432 msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
433 msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
434 raise invalid_conversion;
435
436
437 end if;
438
439
440 end if;
441
442
443 /*
444 ** conversion rates are defaulted to '1' at the start of the procedure
445 ** so seperate calculations are not required for standard/interclass
446 ** conversions
447 */
448
449 uom_rate := (from_rate * interclass_rate) / to_rate;
450
451 /*
452 ** Put a label and a null statement over here so that you can
453 ** the goto statements can branch here.
454 */
455
456 <<procedure_end>>
457
458 null;
459
460 exception
461
462 when others then
463 -- raise_application_error(-20001, sqlerrm||'---'||msgbuf);
464 uom_rate := -99999;
465 END inv_um_conversion;
466
467
468 FUNCTION inv_um_convert (
469 item_id number,
470 precision number,
471 from_quantity number,
472 from_unit varchar2,
473 to_unit varchar2,
474 from_name varchar2,
475 to_name varchar2) RETURN number IS
476
477
478 uom_rate NUMBER;
479 BEGIN
480 uom_rate := inv_um_convert (
481 item_id,
482 NULL,
483 NULL,
484 precision,
485 from_quantity,
486 from_unit,
487 to_unit,
488 from_name,
489 to_name);
490
491 RETURN uom_rate;
492
493 EXCEPTION
494
495 when others then
496 return (-99999);
497 END inv_um_convert;
498
499
500 /*=======================================
501 Joe DiIorio 09/2004 INVCONV
502 Created overloaded version to accept
503 lot number.
504 =====================================*/
505
506 FUNCTION inv_um_convert (
507 item_id number,
508 lot_number varchar2,
509 organization_id number,
510 precision number,
511 from_quantity number,
512 from_unit varchar2,
513 to_unit varchar2,
514 from_name varchar2,
515 to_name varchar2) RETURN number IS
516
517
518 /*
519 ** declare variables that are passed to inv_uom_conversion
520 */
521
522 uom_rate number;
523 msgbuf varchar2(200);
524 from_qty_num number;
525 fm_unt varchar2(3);
526 to_unt varchar2(3);
527 eff_precision number;
528
529 BEGIN
530
531 /* Call the inv_uom_conversion procedure to calculate the uom_rate
532 ** and return. If from_quantity is not null, the function
533 ** assumes that to_quantity is the desired result, and this is
534 ** what is returned. Precision is defaulted to 2 decimals, unless
535 ** a different value is provided by caller of function.
536 ** This function previously calculated the conversion rate in the
537 ** body of the function itself. This was replaced by the present
538 ** procedure call because of a PL/SQL bug which caused memory leaks
539 ** while using tables in functions. Refer to bug 191321 for details.
540 */
541
542 if ( from_unit IS NULL and to_unit IS NULL ) then
543 SELECT uom_code INTO fm_unt FROM mtl_units_of_measure
544 WHERE unit_of_measure = from_name;
545
546 SELECT uom_code INTO to_unt FROM mtl_units_of_measure
547 WHERE unit_of_measure = to_name;
548 else
549 fm_unt := from_unit;
550 to_unt := to_unit;
551 end if;
552
553 /*=====================================
554 Joe DiIorio 09/2004 INVCONV
555 Added lot_number to parameter list.
556 =====================================*/
557
558 inv_um_conversion(fm_unt, to_unt, item_id, lot_number, organization_id, uom_rate);
559 if ( uom_rate = -99999 ) then
560 return(-99999);
561 end if;
562 if ( from_quantity IS NOT NULL ) then
563 uom_rate := from_quantity * uom_rate;
564 end if;
565
566
567 /** Default precision for inventory was 6 decimals
568 Changed the default precision to 5 since INV supports a standard
569 precision of 5 decimal places.
570 */
571 if (precision IS NULL) then
572 eff_precision := 5 ;
573 else
574 eff_precision := precision ;
575 end if;
576 uom_rate := round(uom_rate, eff_precision);
577
578 RETURN uom_rate;
579
580 EXCEPTION
581
582 when others then
583 -- raise_application_error(-20001, sqlerrm||'---'||msgbuf);
584 return (-99999);
585 END inv_um_convert;
586
587
588 FUNCTION inv_um_convert_new (
589 item_id number,
590 precision number,
591 from_quantity number,
592 from_unit varchar2,
593 to_unit varchar2,
594 from_name varchar2,
595 to_name varchar2,
596 capacity_type varchar2) RETURN number IS
597
598 uom_rate NUMBER;
599
600 BEGIN
601 uom_rate := inv_um_convert_new (
602 item_id,
603 NULL,
604 NULL,
605 precision,
606 from_quantity,
607 from_unit,
608 to_unit,
609 from_name,
610 to_name,
611 capacity_type);
612
613 RETURN uom_rate;
614
615 EXCEPTION
616
617 when others then
618 -- raise_application_error(-20001, sqlerrm||'---'||msgbuf);
619 return (-99999);
620 END inv_um_convert_new;
621
622
623 FUNCTION inv_um_convert_new (
624 item_id number,
625 lot_number varchar2,
626 organization_id number,
627 precision number,
628 from_quantity number,
629 from_unit varchar2,
630 to_unit varchar2,
631 from_name varchar2,
632 to_name varchar2,
633 capacity_type varchar2) RETURN number IS
634
635
636 /*
637 ** declare variables that are passed to inv_uom_conversion
638 */
639
640 uom_rate number;
641 msgbuf varchar2(200);
642 from_qty_num number;
643 fm_unt varchar2(3);
644 to_unt varchar2(3);
645 eff_precision number;
646 l_capacity VARCHAR2(1); -- 'W' , 'V' ,'U'
647
648 BEGIN
649
650 /* Call the inv_uom_conversion procedure to calculate the uom_rate
651 ** and return. If from_quantity is not null, the function
652 ** assumes that to_quantity is the desired result, and this is
653 ** what is returned. Precision is defaulted to 2 decimals, unless
654 ** a different value is provided by caller of function.
655 ** This function previously calculated the conversion rate in the
656 ** body of the function itself. This was replaced by the present
657 ** procedure call because of a PL/SQL bug which caused memory leaks
658 ** while using tables in functions. Refer to bug 191321 for details.
659 */
660
661 if ( from_unit IS NULL and to_unit IS NULL ) then
662 SELECT uom_code INTO fm_unt FROM mtl_units_of_measure
663 WHERE unit_of_measure = from_name;
664
665 SELECT uom_code INTO to_unt FROM mtl_units_of_measure
666 WHERE unit_of_measure = to_name;
667 else
668 fm_unt := from_unit;
669 to_unt := to_unit;
670 end if;
671
672 -- bug 3144743
673 -- cache the following values from_uom, to_uom and the uom_rate
674 -- for better performance
675 l_capacity := capacity_type;
676
677 if l_capacity = 'U' then
678 if (nvl(g_u_from_unit, 'XYZ') = fm_unt )
679 and (nvl(g_u_to_unit, 'XYZ') = to_unit)
680 and (nvl(g_u_item_id ,-99999) = item_id)
681 and (nvl(g_u_uom_rate, -99999) <> -99999) then
682
683 uom_rate := g_u_uom_rate;
684 else
685 inv_um_conversion(fm_unt, to_unt, item_id, lot_number, organization_id, uom_rate);
686 g_u_from_unit := fm_unt;
687 g_u_to_unit := to_unit;
688 g_u_uom_rate := uom_rate;
689 g_u_item_id := item_id;
690 end if;
691 elsif l_capacity = 'V' then
692 if (nvl(g_v_from_unit, 'XYZ') = fm_unt )
693 and (nvl(g_v_to_unit, 'XYZ') = to_unit)
694 and (nvl(g_v_item_id ,-99999) = item_id)
695 and (nvl(g_v_uom_rate, -99999) <> -99999) then
696
697 uom_rate := g_v_uom_rate;
698 else
699 inv_um_conversion(fm_unt, to_unt, item_id, lot_number, organization_id, uom_rate);
700 g_v_from_unit := fm_unt;
701 g_v_to_unit := to_unit;
702 g_v_uom_rate := uom_rate;
703 g_v_item_id := item_id;
704 end if;
705 elsif l_capacity = 'W' then
706 if (nvl(g_w_from_unit, 'XYZ') = fm_unt )
707 and (nvl(g_w_to_unit, 'XYZ') = to_unit)
708 and (nvl(g_w_item_id ,-99999) = item_id)
709 and (nvl(g_w_uom_rate, -99999) <> -99999) then
710
711 uom_rate := g_w_uom_rate;
712 else
713 inv_um_conversion(fm_unt, to_unt, item_id, lot_number, organization_id, uom_rate);
714 g_w_from_unit := fm_unt;
715 g_w_to_unit := to_unit;
716 g_w_uom_rate := uom_rate;
717 g_w_item_id := item_id;
718 end if;
719 end if;
720
721 if ( uom_rate = -99999 ) then
722 return(-99999);
723 end if;
724 if ( from_quantity IS NOT NULL ) then
725 uom_rate := from_quantity * uom_rate;
726 end if;
727
728
729 /** Default precision for inventory was 6 decimals
730 Changed the default precision to 5 since INV supports a standard
731 precision of 5 decimal places.
732 */
733 if (precision IS NULL) then
734 eff_precision := 5 ;
735 else
736 eff_precision := precision ;
737 end if;
738 uom_rate := round(uom_rate, eff_precision);
739
740 RETURN uom_rate;
741
742 EXCEPTION
743
744 when others then
745 -- raise_application_error(-20001, sqlerrm||'---'||msgbuf);
746 return (-99999);
747 END inv_um_convert_new;
748
749
750 FUNCTION validate_item_uom (p_uom_code IN VARCHAR2,
751 p_item_id IN NUMBER,
752 p_organization_id IN NUMBER)
753 return BOOLEAN IS
754
755
756 l_primary_uom_code varchar(3) := null;
757 l_allowed_units number := null;
758
759 l_uom_code varchar2(3) := null;
760 l_uom_class varchar2(10) := null;
761
762 Cursor c_msi is
763 select PRIMARY_UOM_CODE, ALLOWED_UNITS_LOOKUP_CODE
764 from mtl_system_items msi,
765 MTL_UNITS_OF_MEASURE MTLUOM2
766 where msi.ORGANIZATION_ID = p_organization_id AND
767 msi.INVENTORY_ITEM_ID = p_item_id AND
768 MTLUOM2.uom_code = msi.PRIMARY_UOM_CODE AND
769 NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE);
770
771
772 cursor c_std_cvr_sameClass is
773 select MTLUOM2.uom_code, MTLUCV.uom_class
774 from MTL_UNITS_OF_MEASURE MTLUOM2,
775 MTL_UOM_CONVERSIONS MTLUCV,
776 MTL_UOM_CLASSES MTLCLS
777 where
778 MTLUOM2.uom_code = p_uom_code AND
779 MTLUCV.uom_code = MTLUOM2.uom_code AND
780 MTLUCV.inventory_item_id=0 AND
781 MTLCLS.uom_class = MTLUOM2.uom_class AND
782 NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
783 NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
784 NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
785 MTLUCV.uom_class = (select MTLPRI1.uom_class
786 from MTL_UNITS_OF_MEASURE MTLPRI1
787 where MTLPRI1.uom_code = l_primary_uom_code AND
788 NVL(MTLPRI1.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
789 );
790
791 cursor c_item_cvr_sameClass is
792 select MTLUOM2.uom_code, MTLUCV.uom_class
793 from MTL_UNITS_OF_MEASURE MTLUOM2,
794 MTL_UOM_CONVERSIONS MTLUCV,
795 MTL_UOM_CLASSES MTLCLS
796 where MTLUOM2.uom_code = p_uom_code AND
797 MTLUCV.uom_code = MTLUOM2.uom_code AND
798 MTLUCV.inventory_item_id = p_item_id AND
799 MTLCLS.uom_class = MTLUOM2.uom_class AND
800 NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
801 NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
802 NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE);
803
804
805 cursor c_complex is
806
807 select MTLUOM2.uom_code, MTLUOM2.uom_class
808 from MTL_UNITS_OF_MEASURE MTLUOM2,
809 MTL_UOM_CONVERSIONS MTLUCV,
810 MTL_UOM_CLASSES MTLCLS
811 where
812 MTLUOM2.uom_code = p_uom_code AND
813 MTLUCV.uom_code = MTLUOM2.uom_code AND
814 MTLCLS.uom_class = MTLUOM2.uom_class AND
815 NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
816 NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
817 NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
818 l_allowed_units in (1,3) AND MTLUCV.inventory_item_id = p_item_id
819 UNION ALL
820 select MTLUOM2.uom_code, MTLUOM2.uom_class
821 from MTL_UNITS_OF_MEASURE MTLUOM2,
822 MTL_UOM_CONVERSIONS MTLUCV,
823 MTL_UOM_CLASSES MTLCLS
824 where
825 MTLUOM2.uom_code = p_uom_code AND
826 MTLUCV.uom_code = MTLUOM2.uom_code AND
827 MTLCLS.uom_class = MTLUOM2.uom_class AND
828 NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
829 NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
830 NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
831 l_allowed_units in (1,3) AND MTLUCV.inventory_item_id=0 AND
832 MTLUCV.uom_class = (select MTLPRI1.uom_class
833 from MTL_UNITS_OF_MEASURE MTLPRI1
834 where MTLPRI1.uom_code = l_primary_uom_code
835 )
836 UNION ALL
837 select MTLUOM2.uom_code, MTLUOM2.uom_class
838 from MTL_UNITS_OF_MEASURE MTLUOM2,
839 MTL_UOM_CONVERSIONS MTLUCV,
840 MTL_UOM_CLASSES MTLCLS
841 where
842 MTLUOM2.uom_code = p_uom_code AND
843 MTLUCV.uom_code = MTLUOM2.uom_code AND
844 MTLCLS.uom_class = MTLUOM2.uom_class AND
845 NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
846 NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
847 NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
848 l_allowed_units in (1,3) AND MTLUCV.inventory_item_id=0 AND
849 exists(
850 select 'UOM_CLASS conversion exists for the class of UOM supplied'
851 from MTL_UOM_CLASS_CONVERSIONS MTLUCC1
852 where
853 MTLUCC1.to_uom_class = MTLUCV.uom_class AND
854 MTLUCC1.inventory_item_id = p_item_id AND
855 NVL(MTLUCC1.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
856 )
857 UNION ALL
858 select MTLUOM2.uom_code, MTLUOM2.uom_class
859 from MTL_UNITS_OF_MEASURE MTLUOM2,
860 MTL_UOM_CONVERSIONS MTLUCV,
861 MTL_UOM_CLASSES MTLCLS
862 where
863 MTLUOM2.uom_code = p_uom_code AND
864 MTLUCV.uom_code = MTLUOM2.uom_code AND
865 MTLCLS.uom_class = MTLUOM2.uom_class AND
866 NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
867 NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
868 NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
869 l_allowed_units in (2,3) AND MTLUCV.inventory_item_id=0 AND
870 exists(
871 select 'UOM_CLASS conversion exists for the class of UOM supplied'
872 from MTL_UOM_CLASS_CONVERSIONS MTLUCC
873 where
874 MTLUCC.to_uom_class = MTLUCV.uom_class AND
875 MTLUCC.INVENTORY_ITEM_ID = p_item_id AND
876 NVL(MTLUCC.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
877 )
878 UNION ALL
879 select MTLUOM2.uom_code, MTLUOM2.uom_class
880 from MTL_UNITS_OF_MEASURE MTLUOM2,
881 MTL_UOM_CONVERSIONS MTLUCV,
882 MTL_UOM_CLASSES MTLCLS
883 where
884 MTLUOM2.uom_code = p_uom_code AND
885 MTLUCV.uom_code = MTLUOM2.uom_code AND
886 MTLCLS.uom_class = MTLUOM2.uom_class AND
887 NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
888 NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
889 NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
890 l_allowed_units in (2,3) AND MTLUCV.inventory_item_id=0 AND
891 MTLUCV.uom_class = (select MTLPRI.uom_class
892 from MTL_UNITS_OF_MEASURE MTLPRI
893 where MTLPRI.uom_code = l_primary_uom_code
894 );
895
896
897
898 BEGIN
899
900 IF (p_uom_code IS NULL
901 OR p_item_id IS NULL
902 OR p_organization_id IS NULL) THEN
903
904 return(FALSE);
905 END IF;
906
907 /* To improve performance, we will check for the most common cases first:
908 - The UOM_CODE supplied is the same as the PRIMARY_UOM_CODE of the item.
909 - The UOM_CODE supplied is in the same UOM_CLASS as the PRIMARY_UOM_CODE
910 and there is a conversion entry for it.
911 Then, if we still dont get a hit, we will test for the more complex cases,
912 like interclass conversions.
913
914 Get the primary_uom_code for the item. Also, get the allowed conversions
915 (standard, item only, or both) in case, we need it later.
916 */
917 open c_msi;
918 fetch c_msi into l_primary_uom_code, l_allowed_units;
919
920 IF c_msi%ISOPEN THEN
921 close c_msi;
922 END IF;
923
924 /* If the uom_code supplied is same as item primary_uom_code then
925 uom_code is valid. Return success.
926 */
927 IF p_uom_code = l_primary_uom_code THEN
928
929 return(TRUE);
930 END IF;
931
932 /* If only standard conversion is allowed, then check for UOM_CODE in
933 the same UOM_CLASS as the PRIMARY_UOM_CODE as the item
934 */
935 open c_std_cvr_sameClass;
936 fetch c_std_cvr_sameClass into l_uom_code, l_uom_class;
937 IF c_std_cvr_sameClass%FOUND THEN
938
939 IF c_std_cvr_sameClass%ISOPEN THEN
940 close c_std_cvr_sameClass;
941 END IF;
942 return(TRUE);
943 END IF;
944
945 /* If only item conversion is allowed, then check for UOM_CODE in
946 the same UOM_CLASS as the PRIMARY_UOM_CODE as the item
947 */
948 open c_item_cvr_sameClass;
949 fetch c_item_cvr_sameClass into l_uom_code, l_uom_class;
950 IF c_item_cvr_sameClass%FOUND THEN
951
952 IF c_item_cvr_sameClass%ISOPEN THEN
953 close c_item_cvr_sameClass;
954 END IF;
955
956 return(TRUE);
957 END IF;
958
959 /* If UOM_CODE supplied is not in same class as item PRIMARY_UOM_CODE,
960 then check more complex case i.e. inter-class.
961 This sql takes care of all cases.
962 */
963 open c_complex;
964 fetch c_complex into l_uom_code, l_uom_class;
965 IF c_complex%FOUND THEN
966
967 IF c_complex%ISOPEN THEN
968 close c_complex;
969 END IF;
970
971 return(TRUE);
972 END IF;
973
974 /* If we are here, then we did not find a match for the UOM_CODE supplied.
975 Therefore, UOM_CODE is not valid. return failure.
976 */
977 return (FALSE);
978
979 EXCEPTION
980
981 WHEN OTHERS THEN
982 IF c_msi%ISOPEN THEN
983 close c_msi;
984 END IF;
985
986 IF c_item_cvr_sameClass%ISOPEN THEN
987 close c_item_cvr_sameClass;
988 END IF;
989
990 IF c_complex%ISOPEN THEN
991 close c_complex;
992 END IF;
993
994 IF c_std_cvr_sameClass%ISOPEN THEN
995 close c_std_cvr_sameClass;
996 END IF;
997
998 RAISE;
999
1000 END validate_item_uom;
1001
1002
1003
1004 PROCEDURE print_debug( p_message VARCHAR2, p_procname VARCHAR2 := NULL, p_level NUMBER := 9) IS
1005 BEGIN
1006 --dbms_output.put_line(p_message);
1007 inv_log_util.trace(
1008 p_message => p_message
1009 , p_module => g_pkg_name||'.'||p_procname
1010 , p_level => p_level);
1011 END print_debug;
1012
1013 PROCEDURE pick_uom_convert(
1014 p_org_id NUMBER,
1015 p_item_id NUMBER,
1016 p_sub_code VARCHAR2,
1017 p_loc_id NUMBER,
1018 p_alloc_uom VARCHAR2,
1019 p_alloc_qty NUMBER,
1020 x_pick_uom OUT NOCOPY VARCHAR2,
1021 x_pick_qty OUT NOCOPY NUMBER,
1022 x_uom_string OUT NOCOPY VARCHAR2,
1023 x_return_status OUT NOCOPY VARCHAR2,
1024 x_msg_data OUT NOCOPY VARCHAR2,
1025 x_msg_count OUT NOCOPY NUMBER) IS
1026
1027 l_loc_uom VARCHAR2(3):= null;
1028 l_uom_string VARCHAR2(20) := null;
1029 l_api_name CONSTANT VARCHAR2(30) := 'PICK_UOM_CONVERT';
1030 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1031
1032 begin
1033
1034 -- Initialize API return status to success
1035 x_return_status := fnd_api.g_ret_sts_success;
1036
1037
1038 IF ( l_debug = 1 ) THEN
1039 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
1040 print_debug('p_org_id => '|| p_org_id||' p_item_id=>'||p_item_id||' p_sub_code=>'||p_sub_code||' p_loc_id=>'||p_loc_id ,4);
1041 print_debug('p_alloc_uom => '||p_alloc_uom||' p_alloc_qty=>'||p_alloc_qty , 4);
1042 END IF;
1043
1044 if (p_loc_id IS NOT NULL) THEN
1045 begin
1046 SELECT pick_uom_code, inv_ui_item_lovs.get_conversion_rate(pick_uom_code, p_org_Id, p_item_Id)
1047 INTO l_loc_uom, l_uom_string
1048 FROM mtl_item_locations
1049 WHERE organization_id = p_org_id
1050 AND subinventory_code = p_sub_code
1051 AND inventory_location_id = p_loc_id;
1052 exception
1053 WHEN OTHERS THEN
1054 IF (l_debug = 1) THEN
1055 print_debug(l_api_name ||' Error In deriving Locator Level Pick UOM', 1);
1056 IF ( SQLCODE IS NOT NULL ) THEN
1057 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
1058 END IF;
1059 END IF;
1060 x_pick_uom := p_alloc_uom;
1061 x_pick_qty := p_alloc_qty;
1062 x_return_status := 'E1'; --error in getting loc pick uom
1063 return;
1064 end;
1065 end if;
1066
1067 if (l_loc_uom IS NULL) THEN
1068 begin
1069 SELECT pick_uom_code, inv_ui_item_lovs.get_conversion_rate(pick_uom_code,
1070 p_org_Id,
1071 p_item_Id)
1072 INTO l_loc_uom, l_uom_string
1073 FROM MTL_SECONDARY_INVENTORIES
1074 WHERE secondary_inventory_name = p_sub_code
1075 AND organization_id = p_org_id;
1076 exception
1077 WHEN OTHERS THEN
1078 IF (l_debug = 1) THEN
1079 print_debug(l_api_name ||' Error In deriving SubInventory Level Pick UOM', 1);
1080 IF ( SQLCODE IS NOT NULL ) THEN
1081 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
1082 END IF;
1083 END IF;
1084 x_pick_uom := p_alloc_uom;
1085 x_pick_qty := p_alloc_qty;
1086 x_return_status := 'E2'; --error in getting sub pick uom
1087 return;
1088 end;
1089 end if;
1090 if (l_loc_uom IS NULL) THEN
1091 x_pick_uom := p_alloc_uom;
1092 x_pick_qty := p_alloc_qty;
1093 x_return_status := 'W1'; --no loc level or sub level pick uom defined
1094 return;
1095 else
1096 --call the uom convert routine
1097 x_pick_qty := inv_um_convert(p_item_id,
1098 null,
1099 p_alloc_qty,
1100 p_alloc_uom,
1101 l_loc_uom,
1102 null,
1103 null);
1104 --return value of x_out_qty should be integer, if not return the in_qty and in_uom value
1105 if (trunc(x_pick_qty) = x_pick_qty AND x_pick_qty > 0) THEN
1106 x_pick_uom := l_loc_uom;
1107 x_uom_string := l_uom_string;
1108 x_return_status := 'S'; --success
1109 else
1110 x_pick_uom := p_alloc_uom;
1111 x_pick_qty := p_alloc_qty;
1112 x_return_status := 'W2'; --could not convert the value in integer
1113 end if;
1114 return;
1115 end if;
1116 end pick_uom_convert;
1117
1118 -- Functions checks if quantities entered for dual uom
1119 -- items are within deviation range.
1120
1121 FUNCTION within_deviation(p_organization_id IN number,
1122 p_inventory_item_id IN number,
1123 p_lot_number IN varchar2,
1124 p_precision IN number,
1125 p_quantity IN number,
1126 p_uom_code1 IN varchar2,
1127 p_quantity2 IN number,
1128 p_uom_code2 IN varchar2,
1129 p_unit_of_measure1 IN varchar2,
1130 p_unit_of_measure2 IN varchar2)
1131
1132 RETURN NUMBER IS
1133
1134 DEV_LOW_ERROR EXCEPTION;
1135 DEV_HIGH_ERROR EXCEPTION;
1136 INVALID_ITEM EXCEPTION;
1137 INCORRECT_FIXED_VALUE EXCEPTION;
1138 INVALID_UOM_CONV EXCEPTION;
1139
1140 l_converted_qty NUMBER;
1141 l_high_boundary NUMBER;
1142 l_low_boundary NUMBER;
1143
1144
1145 /*========================================
1146 Cursor to retrieve uom code.
1147 ========================================*/
1148
1149 CURSOR c_get_uom_code (p_unit VARCHAR2) IS
1150 SELECT uom_code
1151 FROM mtl_units_of_measure
1152 WHERE unit_of_measure = p_unit;
1153
1154 l_uom_code1 MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1155 l_uom_code2 MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1156 x_precision NUMBER;
1157 l_debug PLS_INTEGER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1158 l_procname VARCHAR2 (20) := 'within_deviation';
1159 BEGIN
1160
1161 FND_MSG_PUB.INITIALIZE;
1162
1163 if (l_debug = 1) then
1164 print_debug('p_organization_id : '||p_organization_id , l_procname);
1165 print_debug('p_inventory_item_id: '||p_inventory_item_id, l_procname);
1166 print_debug('p_lot_number : '||p_lot_number , l_procname);
1167 print_debug('p_precision : '||p_precision , l_procname);
1168 print_debug('p_quantity : '||p_quantity , l_procname);
1169 print_debug('p_uom_code1 : '||p_uom_code1 , l_procname);
1170 print_debug('p_quantity2 : '||p_quantity2 , l_procname);
1171 print_debug('p_uom_code2 : '||p_uom_code2 , l_procname);
1172 print_debug('p_unit_of_measure1 : '||p_unit_of_measure1 , l_procname);
1173 print_debug('p_unit_of_measure2 : '||p_unit_of_measure2 , l_procname);
1174 end if;
1175
1176
1177 /*=============================================
1178 Must have a precision value.
1179 ===========================================*/
1180 x_precision := nvl(p_precision,5);
1181
1182 /*=============================================
1183 Get Item Info. Used Cache if available.
1184 ===========================================*/
1185
1186
1187 IF NOT (INV_CACHE.set_item_rec(p_organization_id, p_inventory_item_id)) THEN
1188 RAISE INVALID_ITEM;
1189 END IF;
1190
1191 /*=============================================
1192 Determine if the Deviation Check is Required.
1193 =============================================*/
1194
1195 IF (INV_CACHE.item_rec.tracking_quantity_ind = 'PS' or
1196 INV_CACHE.item_rec.ont_pricing_qty_source = 'S') THEN
1197 /*===================================================
1198 Get uom codes when units_of_measure are sent in.
1199 ===================================================*/
1200 IF (p_unit_of_measure1 IS NOT NULL) THEN
1201 OPEN c_get_uom_code (p_unit_of_measure1);
1202 FETCH c_get_uom_code INTO l_uom_code1;
1203 CLOSE c_get_uom_code;
1204 OPEN c_get_uom_code (p_unit_of_measure2);
1205 FETCH c_get_uom_code INTO l_uom_code2;
1206 CLOSE c_get_uom_code;
1207 ELSE
1208 l_uom_code1 := p_uom_code1;
1209 l_uom_code2 := p_uom_code2;
1210 END IF;
1211
1212
1213 /*===============================
1214 Convert qty 1 to qty2.
1215 ===============================*/
1216 l_converted_qty := inv_um_convert(p_inventory_item_id, p_lot_number,
1217 p_organization_id, x_precision, p_quantity,
1218 l_uom_code1, l_uom_code2, NULL, NULL);
1219 if (l_debug = 1) then
1220 print_debug('l_converted_sec_qty: '||l_converted_qty , l_procname);
1221 end if;
1222
1223 IF (l_converted_qty = -99999) THEN
1224 RAISE INVALID_UOM_CONV;
1225 END IF;
1226
1227 /*====================================
1228 If the secondary default is fixed
1229 make sure the quantities match.
1230 ====================================*/
1231 IF (INV_CACHE.item_rec.secondary_default_ind = 'F' AND
1232 p_quantity2 <> l_converted_qty) THEN
1233 RAISE INCORRECT_FIXED_VALUE;
1234 END IF;
1235
1236 /*=================================
1237 Compute upper/lower boundaries.
1238 =================================*/
1239
1240 l_high_boundary :=
1241 l_converted_qty * (1 + (INV_CACHE.item_rec.dual_uom_deviation_high/100));
1242 l_low_boundary :=
1243 l_converted_qty * (1 - (INV_CACHE.item_rec.dual_uom_deviation_low/100));
1244
1245 if (l_debug = 1) then
1246 print_debug('Is '||p_quantity2 ||' between '|| l_low_boundary ||' and '||l_high_boundary ||'?', l_procname);
1247 end if;
1248
1249 /*=============================================================
1250 Check if qty2 is within boundaries allowing for precision.
1251 =============================================================*/
1252
1253 IF ((l_low_boundary - p_quantity2) > power(10,-(x_precision-1)) ) THEN
1254 RAISE DEV_LOW_ERROR;
1255 END IF;
1256 IF ((p_quantity2 - l_high_boundary) > power(10,-(x_precision-1)) ) THEN
1257 RAISE DEV_HIGH_ERROR;
1258 END IF;
1259
1260 END IF;
1261
1262 RETURN G_TRUE;
1263
1264
1265
1266 EXCEPTION
1267 WHEN INVALID_ITEM THEN
1268 FND_MESSAGE.SET_NAME('INV','INV_INVALID_ITEM');
1269 FND_MSG_PUB.ADD;
1270 RETURN G_FALSE;
1271 WHEN INCORRECT_FIXED_VALUE THEN
1272 FND_MESSAGE.SET_NAME('INV','INV_INCORRECT_FIXED_VALUE');
1273 FND_MSG_PUB.ADD;
1274 RETURN G_FALSE;
1275 WHEN INVALID_UOM_CONV THEN
1276 FND_MESSAGE.SET_NAME('INV','INV_INVALID_UOM_CONV');
1277 FND_MESSAGE.SET_TOKEN ('VALUE1',l_uom_code1);
1278 FND_MESSAGE.SET_TOKEN ('VALUE2',l_uom_code2);
1279 FND_MSG_PUB.ADD;
1280 RETURN G_FALSE;
1281 WHEN DEV_LOW_ERROR THEN
1282 FND_MESSAGE.SET_NAME('INV','INV_DEVIATION_LO_ERR');
1283 FND_MSG_PUB.ADD;
1284 RETURN G_FALSE;
1285 WHEN DEV_HIGH_ERROR THEN
1286 FND_MESSAGE.SET_NAME('INV','INV_DEVIATION_HI_ERR');
1287 FND_MSG_PUB.ADD;
1288 RETURN G_FALSE;
1289 WHEN OTHERS THEN
1290 RETURN G_FALSE;
1291
1292
1293 END within_deviation;
1294
1295 --Added for bug 6761510 for caching of uom conversion
1296 FUNCTION inv_um_convert(p_item_id IN NUMBER,
1297 p_from_uom_code IN VARCHAR2,
1298 p_to_uom_code IN VARCHAR2) RETURN NUMBER
1299 IS
1300
1301 l_conversion_rate NUMBER;
1302 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1303 l_api_name CONSTANT VARCHAR2(30) := 'inv_um_convert';
1304
1305 BEGIN
1306 IF (p_from_uom_code = p_to_uom_code) THEN
1307 -- No conversion necessary
1308 l_conversion_rate := 1;
1309 ELSE
1310 -- Check if the conversion rate for the item/from UOM/to UOM combination is cached
1311 IF (g_item_uom_conversion_tb.EXISTS(p_item_id) AND
1312 g_item_uom_conversion_tb(p_item_id).EXISTS(p_from_uom_code) AND
1313 g_item_uom_conversion_tb(p_item_id)(p_from_uom_code).EXISTS(p_to_uom_code)) THEN
1314 -- Conversion rate is cached so just use the value
1315 l_conversion_rate := g_item_uom_conversion_tb(p_item_id)(p_from_uom_code)(p_to_uom_code);
1316 ELSE
1317 -- Conversion rate is not cached so query and store the value
1318 inv_convert.inv_um_conversion(from_unit => p_from_uom_code,
1319 to_unit => p_to_uom_code,
1320 item_id => p_item_id,
1321 uom_rate => l_conversion_rate);
1322 IF (l_conversion_rate > 0) THEN
1323 -- Store the conversion rate and also the reverse conversion.
1324 -- Do this only if the conversion rate returned is valid, i.e. not negative.
1325 -- {{
1326 -- Test having an exception when retrieving the UOM conversion rate. }}
1327 g_item_uom_conversion_tb(p_item_id)(p_from_uom_code)(p_to_uom_code) := l_conversion_rate;
1328 g_item_uom_conversion_tb(p_item_id)(p_to_uom_code)(p_from_uom_code) := 1 /l_conversion_rate;
1329 END IF;
1330 END IF;
1331 END IF;
1332
1333 -- Return the conversion rate retrieved
1334 RETURN l_conversion_rate;
1335
1336 EXCEPTION
1337 WHEN OTHERS THEN
1338 IF l_debug = 1 THEN
1339 print_debug(l_api_name || 'Exception in inv_um_convert ' || sqlcode || ', ' || sqlerrm, 1);
1340 END IF;
1341 -- If an exception occurs, return a negative value.
1342 -- The calling program should interpret this as an exception in retrieving
1343 -- the UOM conversion rate.
1344 RETURN -999;
1345 END inv_um_convert;
1346
1347
1348 END inv_convert;
[Home] [Help]