1 PACKAGE BODY inv_convert AS
2 /* $Header: INVUMCNB.pls 120.10.12020000.6 2013/04/04 06:05:56 brana 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.10.12020000.6 2013/04/04 06:05:56 brana 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 /* Fix for #7434784. Lot conversion should look into lot_conversion table and
135 also standard interclass conversion.
136
137 e.g. User will define conversion between primary and secondary UOM for a specfic lot
138 However conversion between transaction uom and primary/secondary uom will exists only in
139 interclass conversion table.
140 */
141
142
143 cursor lot_interclass_conversions is
144 select decode(to_uom_class, to_class, 1, 2) to_flag,
145 decode(from_uom_class, from_class, 1, to_class, 2, 0) from_flag,
146 conversion_rate rate
147 from (
148 select from_uom_class, to_uom_class , conversion_rate
149 from mtl_lot_uom_class_conversions
150 where inventory_item_id = item_id
151 and organization_id = p_organization_id
152 and lot_number = p_lot_number
153 and to_uom_class in (from_class, to_class)
154 and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate)
155 union all
156 (
157 select from_uom_class, to_uom_class , conversion_rate
158 from mtl_uom_class_conversions mucc
159 where inventory_item_id = item_id
160 and to_uom_class in (from_class, to_class)
161 and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate)
162 and not exists (
163 select 1
164 from mtl_lot_uom_class_conversions mluc
165 where inventory_item_id = item_id
166 and organization_id = p_organization_id
167 and lot_number = p_lot_number
168 and to_uom_class in (from_class, to_class)
169 and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate)
170 and mluc.from_uom_class = mucc.from_uom_class
171 and mluc.to_uom_class = mucc.to_uom_class
172 )
173 )
174 ) ;
175 /*===============================================
176 Added record type for the above cursor.
177 ===============================================*/
178 lot_class_rec lot_interclass_conversions%rowtype;
179
180 invalid_conversion exception;
181
182 type conv_tab is table of number
183 index by binary_integer;
184
185 type class_tab is table of varchar2(10)
186 index by binary_integer;
187
188 interclass_rate_tab conv_tab;
189 from_class_flag_tab conv_tab;
190 to_class_flag_tab conv_tab;
191 from_rate_tab conv_tab;
192 to_rate_tab conv_tab;
193 from_class_tab class_tab;
194 to_class_tab class_tab;
195
196 std_index number;
197 class_index number;
198
199 from_rate number := 1;
200 to_rate number := 1;
201 interclass_rate number := 1;
202 to_class_rate number := 1;
203 from_class_rate number := 1;
204 msgbuf varchar2(200);
205
206 begin
207
208 /*
209 ** Conversion between between two UOMS.
210 **
211 ** 1. The conversion always starts from the conversion defined, if exists,
212 ** for an specified item.
213 ** 2. If the conversion id not defined for that specific item, then the
214 ** standard conversion, which is defined for all items, is used.
215 ** 3. When the conversion involves two different classes, then
216 ** interclass conversion is activated.
217 */
218
219
220 /*
221 ** If from and to units are the same, conversion rate is 1.
222 ** Go immediately to the end of the procedure to exit.
223 */
224
225
226 if (from_unit = to_unit) then
227
228 uom_rate := 1;
229 goto procedure_end;
230
231 end if;
232
233 /*=======================================
234 Joe DiIorio 09/2004 INVCONV
235 Copy input variables.
236 =====================================*/
237 p_lot_number := lot_number;
238 p_organization_id := organization_id;
239
240 /*
241 ** Get item specific or standard conversions
242 */
243
244 open from_standard_conversions;
245
246 std_index := 0;
247
248 loop
249
250 fetch from_standard_conversions into from_std_rec;
251 exit when from_standard_conversions%notfound;
252
253 std_index := std_index + 1;
254
255 from_rate_tab(std_index) := from_std_rec.std_from_rate;
256 from_class_tab(std_index) := from_std_rec.std_from_class;
257
258 end loop;
259
260 close from_standard_conversions;
261
262 if (std_index = 0) then
263
264 /*
265 ** No conversions defined
266 */
267
268 msgbuf := msgbuf||'Invalid standard conversion : ';
269 msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
270 msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
271 raise invalid_conversion;
272
273 else
274
275 /*
276 ** Conversions are ordered. Item specific conversions will be
277 ** returned first.
278 */
279
280 from_class := from_class_tab(1);
281 from_rate := from_rate_tab(1);
282
283 end if;
284
285 open to_standard_conversions;
286
287 std_index := 0;
288
289 loop
290
291 fetch to_standard_conversions into to_std_rec;
292 exit when to_standard_conversions%notfound;
293
294 std_index := std_index + 1;
295
296 to_rate_tab(std_index) := to_std_rec.std_to_rate;
297 to_class_tab(std_index) := to_std_rec.std_to_class;
298
299 end loop;
300
301 close to_standard_conversions;
302
303 if (std_index = 0) then
304
305 /*
306 ** No conversions defined
307 */
308
309 msgbuf := msgbuf||'Invalid standard conversion : ';
310 msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
311 msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
312 raise invalid_conversion;
313
314 else
315
316 /*
317 ** Conversions are ordered. Item specific conversions will be
318 ** returned first.
319 */
320
321 to_class := to_class_tab(1);
322 to_rate := to_rate_tab(1);
323
324 end if;
325
326 /******
327
328 -- BUG 2899727. Commenting this portion of the code. The check is
329 -- being done after both the cursons above.
330
331 --if (std_index = 0) then
332
333 -- /*
334 -- ** No conversions defined
335 -- */
336
337 -- msgbuf := msgbuf||'Invalid standard conversion : ';
338 -- msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
339 -- msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
340 -- raise invalid_conversion;
341
342 --else
343
344 -- /*
345 -- ** Conversions are ordered. Item specific conversions will be
346 -- ** returned first.
347 -- */
348
349 -- from_class := from_class_tab(1);
350 -- to_class := to_class_tab(1);
351 -- from_rate := from_rate_tab(1);
352 -- to_rate := to_rate_tab(1);
353
354 -- end if;
355 -- End bug 2899727.
356
357 if (from_class <> to_class) then
358 -- Bug 5447516 If item_id is 0 raise an error as intercalss conversions can be
359 -- defined only at item level.
360 if item_id = 0 then
361 raise invalid_conversion;
362 end if;
363 class_index := 0;
364
365 /*=======================================
366 Joe DiIorio 09/2004 INVCONV
367 If there is a lot number try and get
368 the lot specific conversion first.
369 =====================================*/
370
371 IF (lot_number is NOT NULL AND organization_id IS NOT NULL) THEN
372 open lot_interclass_conversions;
373 LOOP
374 FETCH lot_interclass_conversions INTO lot_class_rec;
375 EXIT WHEN lot_interclass_conversions%NOTFOUND;
376 class_index := class_index + 1;
377 to_class_flag_tab(class_index) := lot_class_rec.to_flag;
378 from_class_flag_tab(class_index) := lot_class_rec.from_flag;
379 interclass_rate_tab(class_index) := lot_class_rec.rate;
380 END LOOP;
381 close lot_interclass_conversions;
382
383 END IF;
384
385
386 /*
387 ** Load interclass conversion tables
388 ** If two rows are returned, it implies that there is no direct
389 ** conversion between them.
390 ** If one row is returned, then it may imply that there is a direct
391 ** conversion between them or one class is not defined in the
392 ** class conversion table.
393 */
394
395 /* check interclass first */
396
397 IF (class_index = 0) THEN
398 open interclass_conversions;
399
400 loop
401
402 fetch interclass_conversions into class_rec;
403 exit when interclass_conversions%notfound;
404
405 class_index := class_index + 1;
406
407 to_class_flag_tab(class_index) := class_rec.to_flag;
408 from_class_flag_tab(class_index) := class_rec.from_flag;
409 interclass_rate_tab(class_index) := class_rec.rate;
410
411 end loop;
412
413 close interclass_conversions;
414
415 END IF;
416
417
418
419 if (class_index = 2) then
420
421 if (to_class_flag_tab(1) = 1) then
422
423 to_class_rate := interclass_rate_tab(1);
424 from_class_rate := interclass_rate_tab(2);
425
426 else
427
428 to_class_rate := interclass_rate_tab(2);
429 from_class_rate := interclass_rate_tab(1);
430
431 end if;
432
433 --Bug 2907403
434 interclass_rate := from_class_rate/to_class_rate;
435
436 elsif ((class_index = 1) and
437 (to_class_flag_tab(1) = from_class_flag_tab(1) )) then
438
439
440 if (to_class_flag_tab(1) = 1) then
441
442 to_class_rate := interclass_rate_tab(1);
443 from_class_rate := 1;
444
445 else
446
447 to_class_rate := 1;
448 from_class_rate := interclass_rate_tab(1);
449
450 end if;
451
452
453 interclass_rate := from_class_rate/to_class_rate;
454
455 else
456
457 /*
458 ** No interclass conversion is defined
459 */
460
461 msgbuf := msgbuf||'Invalid Interclass conversion : ';
462 msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
463 msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
464 raise invalid_conversion;
465
466
467 end if;
468
469
470 end if;
471
472
473 /*
474 ** conversion rates are defaulted to '1' at the start of the procedure
475 ** so seperate calculations are not required for standard/interclass
476 ** conversions
477 */
478
479 uom_rate := (from_rate * interclass_rate) / to_rate;
480
481 /*
482 ** Put a label and a null statement over here so that you can
483 ** the goto statements can branch here.
484 */
485
486 <<procedure_end>>
487
488 null;
489
490 exception
491
492 when others then
493 -- raise_application_error(-20001, sqlerrm||'---'||msgbuf);
494 uom_rate := -99999;
495 END inv_um_conversion;
496
497
498 FUNCTION inv_um_convert (
499 item_id number,
500 precision number,
501 from_quantity number,
502 from_unit varchar2,
503 to_unit varchar2,
504 from_name varchar2,
505 to_name varchar2) RETURN number IS
506
507
508 uom_rate NUMBER;
509 BEGIN
510 uom_rate := inv_um_convert (
511 item_id,
512 NULL,
513 NULL,
514 precision,
515 from_quantity,
516 from_unit,
517 to_unit,
518 from_name,
519 to_name);
520
521 RETURN uom_rate;
522
523 EXCEPTION
524
525 when others then
526 return (-99999);
527 END inv_um_convert;
528
529
530 /*=======================================
531 Joe DiIorio 09/2004 INVCONV
532 Created overloaded version to accept
533 lot number.
534 =====================================*/
535
536 FUNCTION inv_um_convert (
537 item_id number,
538 lot_number varchar2,
539 organization_id number,
540 precision number,
541 from_quantity number,
542 from_unit varchar2,
543 to_unit varchar2,
544 from_name varchar2,
545 to_name varchar2) RETURN number IS
546
547
548 /*
549 ** declare variables that are passed to inv_uom_conversion
550 */
551
552 uom_rate number;
553 msgbuf varchar2(200);
554 from_qty_num number;
555 fm_unt varchar2(3);
556 to_unt varchar2(3);
557 eff_precision number;
558
559 BEGIN
560
561 /* Call the inv_uom_conversion procedure to calculate the uom_rate
562 ** and return. If from_quantity is not null, the function
563 ** assumes that to_quantity is the desired result, and this is
564 ** what is returned. Precision is defaulted to 2 decimals, unless
565 ** a different value is provided by caller of function.
566 ** This function previously calculated the conversion rate in the
567 ** body of the function itself. This was replaced by the present
568 ** procedure call because of a PL/SQL bug which caused memory leaks
569 ** while using tables in functions. Refer to bug 191321 for details.
570 */
571
572 if ( from_unit IS NULL and to_unit IS NULL ) then
573 SELECT uom_code INTO fm_unt FROM mtl_units_of_measure
574 WHERE unit_of_measure = from_name;
575
576 SELECT uom_code INTO to_unt FROM mtl_units_of_measure
577 WHERE unit_of_measure = to_name;
578 else
579 fm_unt := from_unit;
580 to_unt := to_unit;
581 end if;
582
583 /*=====================================
584 Joe DiIorio 09/2004 INVCONV
585 Added lot_number to parameter list.
586 =====================================*/
587
588 inv_um_conversion(fm_unt, to_unt, item_id, lot_number, organization_id, uom_rate);
589 if ( uom_rate = -99999 ) then
590 return(-99999);
591 end if;
592 if ( from_quantity IS NOT NULL ) then
593 uom_rate := from_quantity * uom_rate;
594 end if;
595
596
597 /** Default precision for inventory was 6 decimals
598 Changed the default precision to 5 since INV supports a standard
599 precision of 5 decimal places.
600 */
601 if (precision IS NULL) then
602 eff_precision := 5 ;
603 else
604 eff_precision := precision ;
605 end if;
606 uom_rate := round(uom_rate, eff_precision);
607
608 RETURN uom_rate;
609
610 EXCEPTION
611
612 when others then
613 -- raise_application_error(-20001, sqlerrm||'---'||msgbuf);
614 return (-99999);
615 END inv_um_convert;
616
617
618 FUNCTION inv_um_convert_new (
619 item_id number,
620 precision number,
621 from_quantity number,
622 from_unit varchar2,
623 to_unit varchar2,
624 from_name varchar2,
625 to_name varchar2,
626 capacity_type varchar2) RETURN number IS
627
628 uom_rate NUMBER;
629
630 BEGIN
631 uom_rate := inv_um_convert_new (
632 item_id,
633 NULL,
634 NULL,
635 precision,
636 from_quantity,
637 from_unit,
638 to_unit,
639 from_name,
640 to_name,
641 capacity_type);
642
643 RETURN uom_rate;
644
645 EXCEPTION
646
647 when others then
648 -- raise_application_error(-20001, sqlerrm||'---'||msgbuf);
649 return (-99999);
650 END inv_um_convert_new;
651
652
653 FUNCTION inv_um_convert_new (
654 item_id number,
655 lot_number varchar2,
656 organization_id number,
657 precision number,
658 from_quantity number,
659 from_unit varchar2,
660 to_unit varchar2,
661 from_name varchar2,
662 to_name varchar2,
663 capacity_type varchar2) RETURN number IS
664
665
666 /*
667 ** declare variables that are passed to inv_uom_conversion
668 */
669
670 uom_rate number;
671 msgbuf varchar2(200);
672 from_qty_num number;
673 fm_unt varchar2(3);
674 to_unt varchar2(3);
675 eff_precision number;
676 l_capacity VARCHAR2(1); -- 'W' , 'V' ,'U'
677
678 BEGIN
679
680 /* Call the inv_uom_conversion procedure to calculate the uom_rate
681 ** and return. If from_quantity is not null, the function
682 ** assumes that to_quantity is the desired result, and this is
683 ** what is returned. Precision is defaulted to 2 decimals, unless
684 ** a different value is provided by caller of function.
685 ** This function previously calculated the conversion rate in the
686 ** body of the function itself. This was replaced by the present
687 ** procedure call because of a PL/SQL bug which caused memory leaks
688 ** while using tables in functions. Refer to bug 191321 for details.
689 */
690
691 if ( from_unit IS NULL and to_unit IS NULL ) then
692 SELECT uom_code INTO fm_unt FROM mtl_units_of_measure
693 WHERE unit_of_measure = from_name;
694
695 SELECT uom_code INTO to_unt FROM mtl_units_of_measure
696 WHERE unit_of_measure = to_name;
697 else
698 fm_unt := from_unit;
699 to_unt := to_unit;
700 end if;
701
702 -- bug 3144743
703 -- cache the following values from_uom, to_uom and the uom_rate
704 -- for better performance
705 l_capacity := capacity_type;
706
707 if l_capacity = 'U' then
708 if (nvl(g_u_from_unit, 'XYZ') = fm_unt )
709 and (nvl(g_u_to_unit, 'XYZ') = to_unit)
710 and (nvl(g_u_item_id ,-99999) = item_id)
711 and (nvl(g_u_uom_rate, -99999) <> -99999) then
712
713 uom_rate := g_u_uom_rate;
714 else
715 inv_um_conversion(fm_unt, to_unt, item_id, lot_number, organization_id, uom_rate);
716 g_u_from_unit := fm_unt;
717 g_u_to_unit := to_unit;
718 g_u_uom_rate := uom_rate;
719 g_u_item_id := item_id;
720 end if;
721 elsif l_capacity = 'V' then
722 if (nvl(g_v_from_unit, 'XYZ') = fm_unt )
723 and (nvl(g_v_to_unit, 'XYZ') = to_unit)
724 and (nvl(g_v_item_id ,-99999) = item_id)
725 and (nvl(g_v_uom_rate, -99999) <> -99999) then
726
727 uom_rate := g_v_uom_rate;
728 else
729 inv_um_conversion(fm_unt, to_unt, item_id, lot_number, organization_id, uom_rate);
730 g_v_from_unit := fm_unt;
731 g_v_to_unit := to_unit;
732 g_v_uom_rate := uom_rate;
733 g_v_item_id := item_id;
734 end if;
735 elsif l_capacity = 'W' then
736 if (nvl(g_w_from_unit, 'XYZ') = fm_unt )
737 and (nvl(g_w_to_unit, 'XYZ') = to_unit)
738 and (nvl(g_w_item_id ,-99999) = item_id)
739 and (nvl(g_w_uom_rate, -99999) <> -99999) then
740
741 uom_rate := g_w_uom_rate;
742 else
743 inv_um_conversion(fm_unt, to_unt, item_id, lot_number, organization_id, uom_rate);
744 g_w_from_unit := fm_unt;
745 g_w_to_unit := to_unit;
746 g_w_uom_rate := uom_rate;
747 g_w_item_id := item_id;
748 end if;
749 end if;
750
751 if ( uom_rate = -99999 ) then
752 return(-99999);
753 end if;
754 if ( from_quantity IS NOT NULL ) then
755 uom_rate := from_quantity * uom_rate;
756 end if;
757
758
759 /** Default precision for inventory was 6 decimals
760 Changed the default precision to 5 since INV supports a standard
761 precision of 5 decimal places.
762 */
763 if (precision IS NULL) then
764 eff_precision := 5 ;
765 else
766 eff_precision := precision ;
767 end if;
768 uom_rate := round(uom_rate, eff_precision);
769
770 RETURN uom_rate;
771
772 EXCEPTION
773
774 when others then
775 -- raise_application_error(-20001, sqlerrm||'---'||msgbuf);
776 return (-99999);
777 END inv_um_convert_new;
778
779
780 FUNCTION validate_item_uom (p_uom_code IN VARCHAR2,
781 p_item_id IN NUMBER,
782 p_organization_id IN NUMBER)
783 return BOOLEAN IS
784
785
786 l_primary_uom_code varchar(3) := null;
787 l_allowed_units number := null;
788
789 l_uom_code varchar2(3) := null;
790 l_uom_class varchar2(10) := null;
791
792 Cursor c_msi is
793 select PRIMARY_UOM_CODE, ALLOWED_UNITS_LOOKUP_CODE
794 from mtl_system_items msi,
795 MTL_UNITS_OF_MEASURE MTLUOM2
796 where msi.ORGANIZATION_ID = p_organization_id AND
797 msi.INVENTORY_ITEM_ID = p_item_id AND
798 MTLUOM2.uom_code = msi.PRIMARY_UOM_CODE AND
799 NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE);
800
801
802 cursor c_std_cvr_sameClass is
803 select MTLUOM2.uom_code, MTLUCV.uom_class
804 from MTL_UNITS_OF_MEASURE MTLUOM2,
805 MTL_UOM_CONVERSIONS MTLUCV,
806 MTL_UOM_CLASSES MTLCLS
807 where
808 MTLUOM2.uom_code = p_uom_code AND
809 MTLUCV.uom_code = MTLUOM2.uom_code AND
810 MTLUCV.inventory_item_id=0 AND
811 MTLCLS.uom_class = MTLUOM2.uom_class AND
812 NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
813 NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
814 NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
815 MTLUCV.uom_class = (select MTLPRI1.uom_class
816 from MTL_UNITS_OF_MEASURE MTLPRI1
817 where MTLPRI1.uom_code = l_primary_uom_code AND
818 NVL(MTLPRI1.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
819 );
820
821 cursor c_item_cvr_sameClass is
822 select MTLUOM2.uom_code, MTLUCV.uom_class
823 from MTL_UNITS_OF_MEASURE MTLUOM2,
824 MTL_UOM_CONVERSIONS MTLUCV,
825 MTL_UOM_CLASSES MTLCLS
826 where MTLUOM2.uom_code = p_uom_code AND
827 MTLUCV.uom_code = MTLUOM2.uom_code AND
828 MTLUCV.inventory_item_id = p_item_id AND
829 MTLCLS.uom_class = MTLUOM2.uom_class AND
830 NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
831 NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
832 NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE);
833
834
835 cursor c_complex is
836
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 = p_item_id
849 UNION ALL
850 select MTLUOM2.uom_code, MTLUOM2.uom_class
851 from MTL_UNITS_OF_MEASURE MTLUOM2,
852 MTL_UOM_CONVERSIONS MTLUCV,
853 MTL_UOM_CLASSES MTLCLS
854 where
855 MTLUOM2.uom_code = p_uom_code AND
856 MTLUCV.uom_code = MTLUOM2.uom_code AND
857 MTLCLS.uom_class = MTLUOM2.uom_class AND
858 NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
859 NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
860 NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
861 l_allowed_units in (1,3) AND MTLUCV.inventory_item_id=0 AND
862 MTLUCV.uom_class = (select MTLPRI1.uom_class
863 from MTL_UNITS_OF_MEASURE MTLPRI1
864 where MTLPRI1.uom_code = l_primary_uom_code
865 )
866 UNION ALL
867 select MTLUOM2.uom_code, MTLUOM2.uom_class
868 from MTL_UNITS_OF_MEASURE MTLUOM2,
869 MTL_UOM_CONVERSIONS MTLUCV,
870 MTL_UOM_CLASSES MTLCLS
871 where
872 MTLUOM2.uom_code = p_uom_code AND
873 MTLUCV.uom_code = MTLUOM2.uom_code AND
874 MTLCLS.uom_class = MTLUOM2.uom_class AND
875 NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
876 NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
877 NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
878 l_allowed_units in (1,3) AND MTLUCV.inventory_item_id=0 AND
879 exists(
880 select 'UOM_CLASS conversion exists for the class of UOM supplied'
881 from MTL_UOM_CLASS_CONVERSIONS MTLUCC1
882 where
883 MTLUCC1.to_uom_class = MTLUCV.uom_class AND
884 MTLUCC1.inventory_item_id = p_item_id AND
885 NVL(MTLUCC1.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
886 )
887 UNION ALL
888 select MTLUOM2.uom_code, MTLUOM2.uom_class
889 from MTL_UNITS_OF_MEASURE MTLUOM2,
890 MTL_UOM_CONVERSIONS MTLUCV,
891 MTL_UOM_CLASSES MTLCLS
892 where
893 MTLUOM2.uom_code = p_uom_code AND
894 MTLUCV.uom_code = MTLUOM2.uom_code AND
895 MTLCLS.uom_class = MTLUOM2.uom_class AND
896 NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
897 NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
898 NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
899 l_allowed_units in (2,3) AND MTLUCV.inventory_item_id=0 AND
900 exists(
901 select 'UOM_CLASS conversion exists for the class of UOM supplied'
902 from MTL_UOM_CLASS_CONVERSIONS MTLUCC
903 where
904 MTLUCC.to_uom_class = MTLUCV.uom_class AND
905 MTLUCC.INVENTORY_ITEM_ID = p_item_id AND
906 NVL(MTLUCC.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
907 )
908 UNION ALL
909 select MTLUOM2.uom_code, MTLUOM2.uom_class
910 from MTL_UNITS_OF_MEASURE MTLUOM2,
911 MTL_UOM_CONVERSIONS MTLUCV,
912 MTL_UOM_CLASSES MTLCLS
913 where
914 MTLUOM2.uom_code = p_uom_code AND
915 MTLUCV.uom_code = MTLUOM2.uom_code AND
916 MTLCLS.uom_class = MTLUOM2.uom_class AND
917 NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
918 NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
919 NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
920 l_allowed_units in (2,3) AND MTLUCV.inventory_item_id=0 AND
921 MTLUCV.uom_class = (select MTLPRI.uom_class
922 from MTL_UNITS_OF_MEASURE MTLPRI
923 where MTLPRI.uom_code = l_primary_uom_code
924 );
925
926
927
928 BEGIN
929
930 IF (p_uom_code IS NULL
931 OR p_item_id IS NULL
932 OR p_organization_id IS NULL) THEN
933
934 return(FALSE);
935 END IF;
936
937 /* To improve performance, we will check for the most common cases first:
938 - The UOM_CODE supplied is the same as the PRIMARY_UOM_CODE of the item.
939 - The UOM_CODE supplied is in the same UOM_CLASS as the PRIMARY_UOM_CODE
940 and there is a conversion entry for it.
941 Then, if we still dont get a hit, we will test for the more complex cases,
942 like interclass conversions.
943
944 Get the primary_uom_code for the item. Also, get the allowed conversions
945 (standard, item only, or both) in case, we need it later.
946 */
947 open c_msi;
948 fetch c_msi into l_primary_uom_code, l_allowed_units;
949
950 IF c_msi%ISOPEN THEN
951 close c_msi;
952 END IF;
953
954 /* If the uom_code supplied is same as item primary_uom_code then
955 uom_code is valid. Return success.
956 */
957 IF p_uom_code = l_primary_uom_code THEN
958
959 return(TRUE);
960 END IF;
961
962 /* If only standard conversion is allowed, then check for UOM_CODE in
963 the same UOM_CLASS as the PRIMARY_UOM_CODE as the item
964 */
965 open c_std_cvr_sameClass;
966 fetch c_std_cvr_sameClass into l_uom_code, l_uom_class;
967 IF c_std_cvr_sameClass%FOUND THEN
968
969 IF c_std_cvr_sameClass%ISOPEN THEN
970 close c_std_cvr_sameClass;
971 END IF;
972 return(TRUE);
973 END IF;
974
975 /* If only item conversion is allowed, then check for UOM_CODE in
976 the same UOM_CLASS as the PRIMARY_UOM_CODE as the item
977 */
978 open c_item_cvr_sameClass;
979 fetch c_item_cvr_sameClass into l_uom_code, l_uom_class;
980 IF c_item_cvr_sameClass%FOUND THEN
981
982 IF c_item_cvr_sameClass%ISOPEN THEN
983 close c_item_cvr_sameClass;
984 END IF;
985
986 return(TRUE);
987 END IF;
988
989 /* If UOM_CODE supplied is not in same class as item PRIMARY_UOM_CODE,
990 then check more complex case i.e. inter-class.
991 This sql takes care of all cases.
992 */
993 open c_complex;
994 fetch c_complex into l_uom_code, l_uom_class;
995 IF c_complex%FOUND THEN
996
997 IF c_complex%ISOPEN THEN
998 close c_complex;
999 END IF;
1000
1001 return(TRUE);
1002 END IF;
1003
1004 /* If we are here, then we did not find a match for the UOM_CODE supplied.
1005 Therefore, UOM_CODE is not valid. return failure.
1006 */
1007 return (FALSE);
1008
1009 EXCEPTION
1010
1011 WHEN OTHERS THEN
1012 IF c_msi%ISOPEN THEN
1013 close c_msi;
1014 END IF;
1015
1016 IF c_item_cvr_sameClass%ISOPEN THEN
1017 close c_item_cvr_sameClass;
1018 END IF;
1019
1020 IF c_complex%ISOPEN THEN
1021 close c_complex;
1022 END IF;
1023
1024 IF c_std_cvr_sameClass%ISOPEN THEN
1025 close c_std_cvr_sameClass;
1026 END IF;
1027
1028 RAISE;
1029
1030 END validate_item_uom;
1031
1032
1033
1034 PROCEDURE print_debug( p_message VARCHAR2, p_procname VARCHAR2 := NULL, p_level NUMBER := 9) IS
1035 BEGIN
1036 --dbms_output.put_line(p_message);
1037 inv_log_util.trace(
1038 p_message => p_message
1039 , p_module => g_pkg_name||'.'||p_procname
1040 , p_level => p_level);
1041 END print_debug;
1042
1043 PROCEDURE pick_uom_convert(
1044 p_org_id NUMBER,
1045 p_item_id NUMBER,
1046 p_sub_code VARCHAR2,
1047 p_loc_id NUMBER,
1048 p_alloc_uom VARCHAR2,
1049 p_alloc_qty NUMBER,
1050 x_pick_uom OUT NOCOPY VARCHAR2,
1051 x_pick_qty OUT NOCOPY NUMBER,
1052 x_uom_string OUT NOCOPY VARCHAR2,
1053 x_return_status OUT NOCOPY VARCHAR2,
1054 x_msg_data OUT NOCOPY VARCHAR2,
1055 x_msg_count OUT NOCOPY NUMBER) IS
1056
1057 l_loc_uom VARCHAR2(3):= null;
1058 l_uom_string VARCHAR2(20) := null;
1059 l_api_name CONSTANT VARCHAR2(30) := 'PICK_UOM_CONVERT';
1060 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1061
1062 begin
1063
1064 -- Initialize API return status to success
1065 x_return_status := fnd_api.g_ret_sts_success;
1066
1067
1068 IF ( l_debug = 1 ) THEN
1069 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
1070 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);
1071 print_debug('p_alloc_uom => '||p_alloc_uom||' p_alloc_qty=>'||p_alloc_qty , 4);
1072 END IF;
1073
1074 if (p_loc_id IS NOT NULL) THEN
1075 begin
1076 SELECT pick_uom_code, inv_ui_item_lovs.get_conversion_rate(pick_uom_code, p_org_Id, p_item_Id)
1077 INTO l_loc_uom, l_uom_string
1078 FROM mtl_item_locations
1079 WHERE organization_id = p_org_id
1080 AND subinventory_code = p_sub_code
1081 AND inventory_location_id = p_loc_id;
1082 exception
1083 WHEN OTHERS THEN
1084 IF (l_debug = 1) THEN
1085 print_debug(l_api_name ||' Error In deriving Locator Level Pick UOM', 1);
1086 IF ( SQLCODE IS NOT NULL ) THEN
1087 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
1088 END IF;
1089 END IF;
1090 x_pick_uom := p_alloc_uom;
1091 x_pick_qty := p_alloc_qty;
1092 x_return_status := 'E1'; --error in getting loc pick uom
1093 return;
1094 end;
1095 end if;
1096
1097 if (l_loc_uom IS NULL) THEN
1098 begin
1099 SELECT pick_uom_code, inv_ui_item_lovs.get_conversion_rate(pick_uom_code,
1100 p_org_Id,
1101 p_item_Id)
1102 INTO l_loc_uom, l_uom_string
1103 FROM MTL_SECONDARY_INVENTORIES
1104 WHERE secondary_inventory_name = p_sub_code
1105 AND organization_id = p_org_id;
1106 exception
1107 WHEN OTHERS THEN
1108 IF (l_debug = 1) THEN
1109 print_debug(l_api_name ||' Error In deriving SubInventory Level Pick UOM', 1);
1110 IF ( SQLCODE IS NOT NULL ) THEN
1111 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
1112 END IF;
1113 END IF;
1114 x_pick_uom := p_alloc_uom;
1115 x_pick_qty := p_alloc_qty;
1116 x_return_status := 'E2'; --error in getting sub pick uom
1117 return;
1118 end;
1119 end if;
1120 if (l_loc_uom IS NULL) THEN
1121 x_pick_uom := p_alloc_uom;
1122 x_pick_qty := p_alloc_qty;
1123 x_return_status := 'W1'; --no loc level or sub level pick uom defined
1124 return;
1125 else
1126 --call the uom convert routine
1127 x_pick_qty := inv_um_convert(p_item_id,
1128 null,
1129 p_alloc_qty,
1130 p_alloc_uom,
1131 l_loc_uom,
1132 null,
1133 null);
1134 --return value of x_out_qty should be integer, if not return the in_qty and in_uom value
1135 if (trunc(x_pick_qty) = x_pick_qty AND x_pick_qty > 0) THEN
1136 print_debug(l_api_name ||' coming to if part', 1);
1137 x_pick_uom := l_loc_uom;
1138 x_uom_string := l_uom_string;
1139 x_return_status := 'S'; --success
1140 else
1141 print_debug(l_api_name ||' coming to else part', 1);
1142 x_pick_uom := p_alloc_uom;
1143 x_pick_qty := p_alloc_qty;
1144 x_return_status := 'W2'; --could not convert the value in integer
1145 end if;
1146 return;
1147 end if;
1148 end pick_uom_convert;
1149
1150 PROCEDURE pick_uom_convert_for_dual_uom(
1151 p_org_id NUMBER,
1152 p_temp_id NUMBER,
1153 p_item_id NUMBER,
1154 p_sub_code VARCHAR2,
1155 p_loc_id NUMBER,
1156 p_alloc_uom VARCHAR2,
1157 p_alloc_qty NUMBER,
1158 x_pick_uom OUT NOCOPY VARCHAR2,
1159 x_pick_qty OUT NOCOPY NUMBER,
1160 x_uom_string OUT NOCOPY VARCHAR2,
1161 x_return_status OUT NOCOPY VARCHAR2,
1162 x_msg_data OUT NOCOPY VARCHAR2,
1163 x_msg_count OUT NOCOPY NUMBER) IS
1164
1165 l_loc_uom VARCHAR2(3):= null;
1166 l_mmtt_sec_uom VARCHAR2(3) := null;
1167 l_mmtt_sec_qty NUMBER;
1168 l_fulfillment_base VARCHAR2(1) := 'P';
1169 l_uom_string VARCHAR2(20) := null;
1170 l_api_name CONSTANT VARCHAR2(30) := 'pick_uom_convert_for_dual_uom';
1171 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1172
1173 begin
1174
1175 -- Initialize API return status to success
1176 x_return_status := fnd_api.g_ret_sts_success;
1177
1178
1179 BEGIN
1180 SELECT secondary_transaction_quantity, SECONDARY_UOM_CODE, NVL(fulfillment_base, 'P')
1181 INTO l_mmtt_sec_qty, l_mmtt_sec_uom, l_fulfillment_base
1182 FROM mtl_material_transactions_temp
1183 WHERE organization_id = p_org_id
1184 AND transaction_temp_id = p_temp_id;
1185 EXCEPTION
1186 WHEN OTHERS THEN
1187 IF (l_debug = 1) THEN
1188 print_debug(l_api_name ||' Error In finding sec uom from MMTT', 1);
1189 IF ( SQLCODE IS NOT NULL ) THEN
1190 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
1191 END IF;
1192 END IF;
1193 x_pick_uom := p_alloc_uom;
1194 x_pick_qty := p_alloc_qty;
1195 x_return_status := 'E1'; --error in getting sec uom from MMTT
1196 RETURN;
1197 END;
1198
1199 IF ( l_debug = 1 ) THEN
1200 print_debug(l_api_name || 'Entered ' || g_pkg_version, 1);
1201 print_debug('p_org_id => '|| p_org_id||' p_temp_id=>'||p_temp_id ||' p_item_id=>'||p_item_id||' p_sub_code=>'||p_sub_code||' p_loc_id=>'||p_loc_id ,4);
1202 print_debug('p_alloc_uom => '||p_alloc_uom||' p_alloc_qty=>'||p_alloc_qty ||' l_fulfillment_base=>'||l_fulfillment_base , 4);
1203 END IF;
1204
1205 if (p_loc_id IS NOT NULL) THEN
1206 begin
1207 SELECT pick_uom_code, inv_ui_item_lovs.get_conversion_rate(pick_uom_code, p_org_Id, p_item_Id, l_fulfillment_base)
1208 INTO l_loc_uom, l_uom_string
1209 FROM mtl_item_locations
1210 WHERE organization_id = p_org_id
1211 AND subinventory_code = p_sub_code
1212 AND inventory_location_id = p_loc_id;
1213 exception
1214 WHEN OTHERS THEN
1215 IF (l_debug = 1) THEN
1216 print_debug(l_api_name ||' Error In deriving Locator Level Pick UOM', 1);
1217 IF ( SQLCODE IS NOT NULL ) THEN
1218 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
1219 END IF;
1220 END IF;
1221 x_pick_uom := p_alloc_uom;
1222 x_pick_qty := p_alloc_qty;
1223 x_return_status := 'E1'; --error in getting loc pick uom
1224 return;
1225 end;
1226 end if;
1227
1228 if (l_loc_uom IS NULL) THEN
1229 begin
1230 SELECT pick_uom_code, inv_ui_item_lovs.get_conversion_rate(pick_uom_code,
1231 p_org_Id,
1232 p_item_Id,
1233 l_fulfillment_base)
1234 INTO l_loc_uom, l_uom_string
1235 FROM MTL_SECONDARY_INVENTORIES
1236 WHERE secondary_inventory_name = p_sub_code
1237 AND organization_id = p_org_id;
1238 exception
1239 WHEN OTHERS THEN
1240 IF (l_debug = 1) THEN
1241 print_debug(l_api_name ||' Error In deriving SubInventory Level Pick UOM', 1);
1242 IF ( SQLCODE IS NOT NULL ) THEN
1243 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
1244 END IF;
1245 END IF;
1246 x_pick_uom := p_alloc_uom;
1247 x_pick_qty := p_alloc_qty;
1248 x_return_status := 'E2'; --error in getting sub pick uom
1249 return;
1250 end;
1251 end if;
1252 if (l_loc_uom IS NULL ) THEN
1253 x_pick_uom := p_alloc_uom;
1254 x_pick_qty := p_alloc_qty;
1255 x_return_status := 'W1'; --no loc level or sub level pick uom defined
1256 return;
1257 --MUOM
1258 ELSIF(NVL(inv_convert.is_uom_in_same_class(l_loc_uom, p_org_id , p_item_id , Nvl(l_fulfillment_base,'P')),'N') = 'N') THEN
1259 x_pick_uom := p_alloc_uom;
1260 x_pick_qty := p_alloc_qty;
1261 x_return_status := fnd_api.g_ret_sts_success;
1262 RETURN;
1263 --MUOM
1264 else
1265
1266 print_debug('value of l_mmtt_sec_uom => '||l_mmtt_sec_uom||' l_loc_uom=>'||l_loc_uom , 4);
1267 print_debug('value of l_uom_string => '||l_uom_string||' l_mmtt_sec_qty=>'||l_mmtt_sec_qty , 4);
1268
1269 IF(l_mmtt_sec_uom IS NOT NULL AND l_mmtt_sec_uom = l_loc_uom) THEN
1270 x_pick_uom := l_loc_uom;
1271 x_uom_string := l_uom_string;
1272 x_pick_qty := l_mmtt_sec_qty;
1273 x_return_status := 'S'; --success
1274 ELSE
1275
1276 --call the uom convert routine
1277 x_pick_qty := inv_um_convert(p_item_id,
1278 null,
1279 p_alloc_qty,
1280 p_alloc_uom,
1281 l_loc_uom,
1282 null,
1283 null);
1284 --return value of x_out_qty should be integer, if not return the in_qty and in_uom value
1285 if (trunc(x_pick_qty) = x_pick_qty AND x_pick_qty > 0) THEN
1286 print_debug(l_api_name ||' coming to if part', 1);
1287 x_pick_uom := l_loc_uom;
1288 x_uom_string := l_uom_string;
1289 x_return_status := 'S'; --success
1290 else
1291 print_debug(l_api_name ||' coming to else part', 1);
1292 x_pick_uom := p_alloc_uom;
1293 x_pick_qty := p_alloc_qty;
1294 x_return_status := 'W2'; --could not convert the value in integer
1295 end if;
1296 END IF;
1297 return;
1298 end if;
1299 end pick_uom_convert_for_dual_uom;
1300
1301 --MUOM
1302 FUNCTION is_uom_in_same_class (p_uom IN VARCHAR2
1303 ,p_organization_id IN NUMBER
1304 ,p_item_id IN NUMBER
1305 ,p_fulfillment_base IN VARCHAR2) RETURN VARCHAR2 IS
1306
1307 l_uom_in_same_class VARCHAR2(1) :='Y';
1308 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1309
1310 BEGIN
1311 IF Nvl(p_fulfillment_base,'P') = 'P'
1312 THEN
1313 IF ( l_debug = 1 ) THEN
1314 print_debug('p_fulfillment_base '||p_fulfillment_base, 4);
1315 print_debug('FB = P Hence returning '||p_fulfillment_base, 4);
1316 END IF;
1317 RETURN l_uom_in_same_class;
1318
1319 ELSE
1320
1321 SELECT 'Y'
1322 INTO l_uom_in_same_class
1323 FROM mtl_system_items msi ,
1324 mtl_item_uoms_view mv1,
1325 mtl_item_uoms_view mv2
1326 WHERE msi.organization_id = p_organization_id
1327 AND msi.inventory_item_id = p_item_id
1328 AND msi.inventory_item_id = mv1.inventory_item_id
1329 AND msi.organization_id = mv1.organization_id
1330 AND mv1.organization_id = mv2.organization_id
1331 AND mv1.inventory_item_id = mv2.inventory_item_id
1332 AND mv1.uom_code = p_uom
1333 AND mv2.uom_code = msi.secondary_uom_code
1334 AND mv1.uom_class = mv2.uom_class
1335 AND ROWNUM = 1;
1336
1337 IF ( l_debug = 1 ) THEN
1338 print_debug('Returning l_uom_in_same_class: '||l_uom_in_same_class, 4);
1339 END IF;
1340
1341 RETURN l_uom_in_same_class;
1342
1343 END IF;
1344
1345 EXCEPTION
1346 WHEN OTHERS THEN
1347 IF ( l_debug = 1 ) THEN
1348 print_debug('In exception '||SQLERRM, 4);
1349 END IF;
1350 l_uom_in_same_class := 'N';
1351
1352 IF ( l_debug = 1 ) THEN
1353 print_debug('l_uom_in_same_class '||l_uom_in_same_class, 4);
1354 END IF;
1355 RETURN l_uom_in_same_class;
1356 END;
1357 --MUOM
1358
1359 -- Functions checks if quantities entered for dual uom
1360 -- items are within deviation range.
1361
1362 FUNCTION within_deviation(p_organization_id IN number,
1363 p_inventory_item_id IN number,
1364 p_lot_number IN varchar2,
1365 p_precision IN number,
1366 p_quantity IN number,
1367 p_uom_code1 IN varchar2,
1368 p_quantity2 IN number,
1369 p_uom_code2 IN varchar2,
1370 p_unit_of_measure1 IN varchar2,
1371 p_unit_of_measure2 IN varchar2,
1372 -- muom
1373 /* When splitting a line/detail, the deviation check is done only on the split quantity.
1374 If the deviation check needs to be done on the remaining quantity, then this api needs
1375 to be called for the remaining quantity and p_split_check should be passed as 'Y'. If
1376 the deviation check fails, then a new message is shown.
1377 */
1378 p_split_check IN varchar2)
1379
1380 RETURN NUMBER IS
1381
1382 DEV_LOW_ERROR EXCEPTION;
1383 DEV_HIGH_ERROR EXCEPTION;
1384 INVALID_ITEM EXCEPTION;
1385 INCORRECT_FIXED_VALUE EXCEPTION;
1386 INVALID_UOM_CONV EXCEPTION;
1387
1388 l_converted_qty NUMBER;
1389 l_high_boundary NUMBER;
1390 l_low_boundary NUMBER;
1391 l_converted_prim_qty NUMBER; --ADM bug 9959125
1392
1393
1394 /*========================================
1395 Cursor to retrieve uom code.
1396 ========================================*/
1397
1398 CURSOR c_get_uom_code (p_unit VARCHAR2) IS
1399 SELECT uom_code
1400 FROM mtl_units_of_measure
1401 WHERE unit_of_measure = p_unit;
1402
1403 l_uom_code1 MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1404 l_uom_code2 MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1405 x_precision NUMBER;
1406 l_debug PLS_INTEGER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1407 l_procname VARCHAR2 (20) := 'within_deviation';
1408 BEGIN
1409
1410 FND_MSG_PUB.INITIALIZE;
1411
1412 if (l_debug = 1) then
1413 print_debug('p_organization_id : '||p_organization_id , l_procname);
1414 print_debug('p_inventory_item_id: '||p_inventory_item_id, l_procname);
1415 print_debug('p_lot_number : '||p_lot_number , l_procname);
1416 print_debug('p_precision : '||p_precision , l_procname);
1417 print_debug('p_quantity : '||p_quantity , l_procname);
1418 print_debug('p_uom_code1 : '||p_uom_code1 , l_procname);
1419 print_debug('p_quantity2 : '||p_quantity2 , l_procname);
1420 print_debug('p_uom_code2 : '||p_uom_code2 , l_procname);
1421 print_debug('p_unit_of_measure1 : '||p_unit_of_measure1 , l_procname);
1422 print_debug('p_unit_of_measure2 : '||p_unit_of_measure2 , l_procname);
1423 -- muom
1424 print_debug('p_split_check : '||p_split_check , l_procname);
1425 end if;
1426
1427
1428 /*=============================================
1429 Must have a precision value.
1430 ===========================================*/
1431 x_precision := nvl(p_precision,5);
1432
1433 /*=============================================
1434 Get Item Info. Used Cache if available.
1435 ===========================================*/
1436
1437
1438 IF NOT (INV_CACHE.set_item_rec(p_organization_id, p_inventory_item_id)) THEN
1439 RAISE INVALID_ITEM;
1440 END IF;
1441
1442 /*=============================================
1443 Determine if the Deviation Check is Required.
1444 =============================================*/
1445
1446 IF (INV_CACHE.item_rec.tracking_quantity_ind = 'PS' or
1447 INV_CACHE.item_rec.ont_pricing_qty_source = 'S') THEN
1448 /*===================================================
1449 Get uom codes when units_of_measure are sent in.
1450 ===================================================*/
1451 IF (p_unit_of_measure1 IS NOT NULL) THEN
1452 OPEN c_get_uom_code (p_unit_of_measure1);
1453 FETCH c_get_uom_code INTO l_uom_code1;
1454 CLOSE c_get_uom_code;
1455 OPEN c_get_uom_code (p_unit_of_measure2);
1456 FETCH c_get_uom_code INTO l_uom_code2;
1457 CLOSE c_get_uom_code;
1458 ELSE
1459 l_uom_code1 := p_uom_code1;
1460 l_uom_code2 := p_uom_code2;
1461 END IF;
1462
1463
1464 /*===============================
1465 Convert qty 1 to qty2.
1466 ===============================*/
1467 l_converted_qty := inv_um_convert(p_inventory_item_id, p_lot_number,
1468 p_organization_id, x_precision, p_quantity,
1469 l_uom_code1, l_uom_code2, NULL, NULL);
1470 if (l_debug = 1) then
1471 print_debug('l_converted_sec_qty : '||l_converted_qty , l_procname);
1472 end if;
1473
1474 IF (l_converted_qty = -99999) THEN
1475 RAISE INVALID_UOM_CONV;
1476 END IF;
1477
1478 if (l_debug = 1) then
1479 print_debug('secondary_default_ind : '||INV_CACHE.item_rec.secondary_default_ind , l_procname);
1480 end if;
1481
1482 /*====================================
1483 If the secondary default is fixed
1484 make sure the quantities match.
1485 ====================================*/
1486 --Fixed for bug#7562694
1487 --Condition for fixed conversion has been modified.
1488 --Due to rounding to 5 places even for fixed conversion as well
1489 --there could be deviation of at most 0.00001 qty.
1490 --if the diff is more than 0.00001 then raise error.
1491
1492 IF (((INV_CACHE.item_rec.secondary_default_ind = 'F') OR ( INV_CACHE.item_rec.secondary_default_ind IN ('D','N')
1493 AND INV_CACHE.item_rec.dual_uom_deviation_high = 0
1494 AND INV_CACHE.item_rec.dual_uom_deviation_low = 0)) AND
1495 (abs(l_converted_qty - p_quantity2) >0.00001) ) THEN
1496
1497 --ADM bug 9959125, reconverting sec to primary to check whether that matches instead of just checking sec qty match.
1498 l_converted_prim_qty := inv_um_convert(p_inventory_item_id, p_lot_number,
1499 p_organization_id, x_precision, p_quantity2,
1500 l_uom_code2, l_uom_code1, NULL, NULL);
1501
1502 if (l_debug = 1) then
1503 print_debug('l_converted_prim_qty : '||l_converted_prim_qty , l_procname);
1504 end if;
1505
1506 IF (l_converted_prim_qty = -99999) THEN
1507 RAISE INVALID_UOM_CONV;
1508 END IF;
1509
1510 IF (abs(l_converted_prim_qty - p_quantity) >0.00001) THEN
1511 if (l_debug = 1) then
1512 print_debug('returning error for fixed item as both sec and pri converted qty are not matching' , l_procname);
1513 end if;
1514 RAISE INCORRECT_FIXED_VALUE;
1515 END IF;
1516
1517 if (l_debug = 1) then
1518 print_debug('returning true for fixed item as primary converted qty is matching' , l_procname);
1519 end if;
1520
1521 RETURN G_TRUE;
1522 END IF;
1523
1524 /*=================================
1525 Compute upper/lower boundaries.
1526 =================================*/
1527
1528 if (l_debug = 1) then
1529 print_debug('dual_uom_deviation_high : '||INV_CACHE.item_rec.dual_uom_deviation_high ||'%', l_procname);
1530 print_debug('dual_uom_deviation_low : '||INV_CACHE.item_rec.dual_uom_deviation_low ||'%', l_procname);
1531 end if;
1532
1533 l_high_boundary := l_converted_qty * (1 + (INV_CACHE.item_rec.dual_uom_deviation_high/100));
1534 l_low_boundary := l_converted_qty * (1 - (INV_CACHE.item_rec.dual_uom_deviation_low/100));
1535
1536 if (l_debug = 1) then
1537 print_debug('Is '||p_quantity2 ||' between '|| l_low_boundary ||' and '||l_high_boundary ||'?', l_procname);
1538 end if;
1539
1540 /*=============================================================
1541 Check if qty2 is within boundaries allowing for precision.
1542 =============================================================*/
1543
1544 IF ((l_low_boundary - p_quantity2) > power(10,-(x_precision/*-1*/)) ) THEN -- bug#10238270 remove the -1
1545 RAISE DEV_LOW_ERROR;
1546 END IF;
1547 IF ((p_quantity2 - l_high_boundary) > power(10,-(x_precision/*-1*/)) ) THEN -- bug#10238270 remove the -1
1548 RAISE DEV_HIGH_ERROR;
1549 END IF;
1550
1551 END IF;
1552
1553 RETURN G_TRUE;
1554
1555
1556
1557 EXCEPTION
1558 WHEN INVALID_ITEM THEN
1559 FND_MESSAGE.SET_NAME('INV','INV_INVALID_ITEM');
1560 FND_MSG_PUB.ADD;
1561 RETURN G_FALSE;
1562 WHEN INCORRECT_FIXED_VALUE THEN
1563 FND_MESSAGE.SET_NAME('INV','INV_INCORRECT_FIXED_VALUE');
1564 FND_MSG_PUB.ADD;
1565 RETURN G_FALSE;
1566 WHEN INVALID_UOM_CONV THEN
1567 FND_MESSAGE.SET_NAME('INV','INV_INVALID_UOM_CONV');
1568 FND_MESSAGE.SET_TOKEN ('VALUE1',l_uom_code1);
1569 FND_MESSAGE.SET_TOKEN ('VALUE2',l_uom_code2);
1570 FND_MSG_PUB.ADD;
1571 RETURN G_FALSE;
1572 WHEN DEV_LOW_ERROR THEN
1573 -- muom
1574 IF p_split_check = 'Y' THEN
1575 FND_MESSAGE.SET_NAME('INV','INV_OTH_DEVIATION_LO_ERR');
1576 ELSE
1577 FND_MESSAGE.SET_NAME('INV','INV_DEVIATION_LO_ERR');
1578 END IF;
1579 FND_MSG_PUB.ADD;
1580 RETURN G_FALSE;
1581 WHEN DEV_HIGH_ERROR THEN
1582 -- muom
1583 IF p_split_check = 'Y' THEN
1584 FND_MESSAGE.SET_NAME('INV','INV_OTH_DEVIATION_HI_ERR');
1585 ELSE
1586 FND_MESSAGE.SET_NAME('INV','INV_DEVIATION_HI_ERR');
1587 END IF;
1588 FND_MSG_PUB.ADD;
1589 RETURN G_FALSE;
1590 WHEN OTHERS THEN
1591 RETURN G_FALSE;
1592
1593
1594 END within_deviation;
1595
1596 --Added for bug 6761510 for caching of uom conversion
1597 FUNCTION inv_um_convert(p_item_id IN NUMBER,
1598 p_from_uom_code IN VARCHAR2,
1599 p_to_uom_code IN VARCHAR2) RETURN NUMBER
1600 IS
1601
1602 l_conversion_rate NUMBER;
1603 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1604 l_api_name CONSTANT VARCHAR2(30) := 'inv_um_convert';
1605
1606 BEGIN
1607 IF (p_from_uom_code = p_to_uom_code) THEN
1608 -- No conversion necessary
1609 l_conversion_rate := 1;
1610 ELSE
1611 -- Check if the conversion rate for the item/from UOM/to UOM combination is cached
1612 IF (g_item_uom_conversion_tb.EXISTS(p_item_id) AND
1613 g_item_uom_conversion_tb(p_item_id).EXISTS(p_from_uom_code) AND
1614 g_item_uom_conversion_tb(p_item_id)(p_from_uom_code).EXISTS(p_to_uom_code)) THEN
1615 -- Conversion rate is cached so just use the value
1616 l_conversion_rate := g_item_uom_conversion_tb(p_item_id)(p_from_uom_code)(p_to_uom_code);
1617 ELSE
1618 -- Conversion rate is not cached so query and store the value
1619 inv_convert.inv_um_conversion(from_unit => p_from_uom_code,
1620 to_unit => p_to_uom_code,
1621 item_id => p_item_id,
1622 uom_rate => l_conversion_rate);
1623 IF (l_conversion_rate > 0) THEN
1624 -- Store the conversion rate and also the reverse conversion.
1625 -- Do this only if the conversion rate returned is valid, i.e. not negative.
1626 -- {{
1627 -- Test having an exception when retrieving the UOM conversion rate. }}
1628 g_item_uom_conversion_tb(p_item_id)(p_from_uom_code)(p_to_uom_code) := l_conversion_rate;
1629 g_item_uom_conversion_tb(p_item_id)(p_to_uom_code)(p_from_uom_code) := 1 /l_conversion_rate;
1630 END IF;
1631 END IF;
1632 END IF;
1633
1634 -- Return the conversion rate retrieved
1635 RETURN l_conversion_rate;
1636
1637 EXCEPTION
1638 WHEN OTHERS THEN
1639 IF l_debug = 1 THEN
1640 print_debug(l_api_name || 'Exception in inv_um_convert ' || sqlcode || ', ' || sqlerrm, 1);
1641 END IF;
1642 -- If an exception occurs, return a negative value.
1643 -- The calling program should interpret this as an exception in retrieving
1644 -- the UOM conversion rate.
1645 RETURN -999;
1646 END inv_um_convert;
1647
1648
1649 -- ==========================================================================
1650 -- PROCEDURE : create_uom_conversion
1651 -- PARAMETERS : -
1652 -- COMMENT : Creates the conversion between two uom's using uom_rate
1653 -- to_uom_code = uom_rate * from_uom_code
1654 -- x_return_status values
1655 -- S : Successful conversion creation
1656 -- W : Conversion exists prior
1657 -- E : Error in creation conversion
1658 -- U : Unexpected error occured
1659 -- BUG NUMBER : 9335882
1660 -- ==========================================================================
1661
1662 PROCEDURE create_uom_conversion ( p_from_uom_code VARCHAR2 ,
1663 p_to_uom_code VARCHAR2 ,
1664 p_item_id NUMBER ,
1665 p_uom_rate NUMBER ,
1666 x_return_status OUT NOCOPY VARCHAR2
1667 ) IS
1668
1669 l_from_class VARCHAR2(10);
1670 l_to_class VARCHAR2(10);
1671 l_from_unit_of_measure VARCHAR2(25);
1672 l_to_unit_of_measure VARCHAR2(25);
1673 l_from_base_uom_flag VARCHAR2(1);
1674 l_to_base_uom_flag VARCHAR2(1);
1675
1676 l_temp_uom VARCHAR2(3);
1677 l_temp_item_id NUMBER;
1678 l_conversion_exists VARCHAR2(1);
1679 l_primary_uom_code VARCHAR2(3);
1680
1681 l_invalid_uom_exc EXCEPTION ;
1682 l_uom_fromto_exc EXCEPTION ;
1683 l_invalid_item_exc EXCEPTION ;
1684 l_conversion_exists_exc EXCEPTION ;
1685 l_ret_conv_exists_warning CONSTANT VARCHAR2(1) := 'W' ;
1686
1687 BEGIN
1688
1689 IF (p_from_uom_code = NULL) OR (p_to_uom_code = NULL) THEN
1690 print_debug(' UOM_code is null ' || g_pkg_version, 1);
1691 RAISE l_invalid_uom_exc ;
1692 ELSIF p_from_uom_code = p_to_uom_code THEN
1693 print_debug(' from and to uom codes equal ' || g_pkg_version, 1);
1694 RAISE l_uom_fromto_exc ;
1695 END IF ;
1696
1697 BEGIN
1698 SELECT unit_of_measure , uom_class , base_uom_flag
1699 INTO l_from_unit_of_measure , l_from_class , l_from_base_uom_flag
1700 FROM MTL_UNITS_OF_MEASURE_VL
1701 WHERE uom_code = p_from_uom_code
1702 AND nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate);
1703 EXCEPTION
1704 WHEN no_data_found THEN
1705 print_debug(p_from_uom_code || ' doesnot exist ' || g_pkg_version, 1);
1706 RAISE l_invalid_uom_exc ;
1707 END ;
1708
1709 BEGIN
1710 SELECT unit_of_measure , uom_class , base_uom_flag
1711 INTO l_to_unit_of_measure ,l_to_class , l_to_base_uom_flag
1712 FROM MTL_UNITS_OF_MEASURE_VL
1713 WHERE uom_code = p_to_uom_code
1714 AND nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate);
1715 EXCEPTION
1716 WHEN no_data_found THEN
1717 print_debug(p_to_uom_code || ' doesnot exist ' || g_pkg_version, 1);
1718 RAISE l_invalid_uom_exc ;
1719 END ;
1720
1721
1722 IF l_from_base_uom_flag <> 'Y' THEN
1723 print_debug(p_to_uom_code || ' doesnot exist ' || g_pkg_version, 1);
1724 RAISE l_invalid_uom_exc ;
1725 END IF;
1726
1727 IF l_from_class = l_to_class THEN
1728 IF p_item_id <> 0 THEN
1729 BEGIN
1730 SELECT DISTINCT inventory_item_id
1731 INTO l_temp_item_id
1732 FROM mtl_system_items_vl
1733 WHERE inventory_item_id = p_item_id
1734 AND inventory_item_id IN (SELECT DISTINCT I.inventory_item_id FROM mtl_system_items_vl I
1735 WHERE I.enabled_flag = 'Y'
1736 AND (SYSDATE BETWEEN NVL(TRUNC(I.start_date_active),SYSDATE )
1737 AND NVL(TRUNC(I.end_date_active),SYSDATE))
1738 AND ( EXISTS (SELECT A.unit_of_measure FROM mtl_units_of_measure A
1739 WHERE (A.uom_class IN (SELECT to_uom_class FROM mtl_uom_class_conversions B
1740 WHERE B.inventory_item_id = I.inventory_item_id)
1741 OR A.uom_class = (SELECT Z.uom_class FROM mtl_units_of_measure Z
1742 WHERE Z.uom_code = I.primary_uom_code))
1743 AND A.base_uom_flag <> 'Y'
1744 AND NVL(A.disable_date, SYSDATE+1) > SYSDATE
1745 AND A.uom_class = NVL(l_to_class, A.uom_class))));
1746 EXCEPTION
1747 WHEN No_Data_Found THEN
1748 print_debug(p_item_id || ' item not valid for intra class conversion ' || g_pkg_version, 1);
1749 RAISE l_invalid_item_exc;
1750 END ;
1751
1752 BEGIN
1753 SELECT DISTINCT x.uom_code
1754 INTO l_temp_uom
1755 FROM mtl_units_of_measure x
1756 WHERE x.uom_code = p_to_uom_code
1757 AND x.uom_code IN (SELECT DISTINCT a.uom_code FROM mtl_units_of_measure a
1758 WHERE (a.uom_class in (select to_uom_class
1759 from mtl_uom_class_conversions b
1760 where b.inventory_item_id = p_item_id)
1761 or a.uom_class =(select DISTINCT z.uom_class
1762 from mtl_units_of_measure z , mtl_system_items_vl m
1763 where m.inventory_item_id = p_item_id
1764 AND z.uom_code = m.primary_uom_code
1765 ))
1766 and a.base_uom_flag <> 'Y'
1767 and nvl(a.disable_date,sysdate+1) > SYSDATE);
1768 EXCEPTION
1769 WHEN No_Data_Found THEN
1770 print_debug(p_to_uom_code || ' UOM not valid for intra class conversion ' || g_pkg_version, 1);
1771 RAISE l_invalid_uom_exc;
1772 END ;
1773
1774
1775 BEGIN
1776 SELECT 'Y'
1777 INTO l_conversion_exists
1778 FROM mtl_uom_conversions
1779 WHERE inventory_item_id = p_item_id
1780 AND uom_code = p_to_uom_code ;
1781 EXCEPTION
1782 WHEN no_data_found THEN
1783 print_debug(' Creating Intra-class conversion ' || g_pkg_version, 1);
1784 l_conversion_exists := 'N' ;
1785 END ;
1786
1787 ELSE
1788 BEGIN
1789 SELECT 'Y'
1790 INTO l_conversion_exists
1791 FROM mtl_uom_conversions
1792 WHERE inventory_item_id = 0
1793 AND uom_code = p_to_uom_code ;
1794 EXCEPTION
1795 WHEN no_data_found THEN
1796 print_debug(' Creating Standard conversion ' || g_pkg_version, 1);
1797 l_conversion_exists := 'N' ;
1798 END ;
1799
1800 END IF ;
1801
1802 IF l_conversion_exists = 'N' THEN
1803 INSERT INTO mtl_uom_conversions
1804 (inventory_item_id,
1805 unit_of_measure,
1806 uom_code,
1807 uom_class,
1808 last_update_date,
1809 last_updated_by,
1810 creation_date,
1811 created_by,
1812 last_update_login,
1813 conversion_rate,
1814 default_conversion_flag)
1815 VALUES (p_item_id,
1816 l_to_unit_of_measure,
1817 p_to_uom_code,
1818 l_to_class,
1819 sysdate,
1820 fnd_global.user_id,
1821 sysdate,
1822 fnd_global.user_id,
1823 -1,
1824 p_uom_rate,
1825 'N');
1826 ELSE
1827 print_debug(' Conversion already exists' || g_pkg_version, 1);
1828 RAISE l_conversion_exists_exc;
1829 END IF ;
1830
1831 ELSE
1832 IF p_item_id = 0 THEN
1833 print_debug(' Inter-class conversion cannot be created if item_id =0' || g_pkg_version, 1);
1834 RAISE l_invalid_item_exc ;
1835 ELSE
1836 IF l_to_base_uom_flag <> 'Y' THEN
1837 print_debug(' inter class conversion cannot be done for non base units ' || g_pkg_version, 1);
1838 RAISE l_invalid_uom_exc ;
1839 END IF ;
1840
1841 BEGIN
1842 SELECT DISTINCT inventory_item_id , primary_uom_code INTO l_temp_item_id , l_primary_uom_code
1843 FROM mtl_system_items_vl
1844 WHERE inventory_item_id = p_item_id
1845 AND inventory_item_id IN (SELECT DISTINCT I.inventory_item_id FROM mtl_system_items_vl I
1846 WHERE I.enabled_flag = 'Y'
1847 AND (SYSDATE BETWEEN NVL(TRUNC(I.start_date_active),SYSDATE )
1848 AND NVL(TRUNC(I.end_date_active),SYSDATE))
1849 AND ( EXISTS (SELECT A.unit_of_measure FROM mtl_units_of_measure A
1850 WHERE (A.uom_class <> (SELECT R.uom_class FROM mtl_units_of_measure R
1851 WHERE R.uom_code = I.primary_uom_code))
1852 AND A.base_uom_flag = 'Y'
1853 AND NVL(A.disable_date, SYSDATE+1) > SYSDATE
1854 AND A.uom_class = NVL(l_to_class,A.uom_class))));
1855 EXCEPTION
1856 WHEN No_Data_Found THEN
1857 print_debug(p_item_id || ' item not valid for inter class conversion ' || g_pkg_version, 1);
1858 RAISE l_invalid_item_exc ;
1859 END ;
1860
1861 BEGIN
1862 SELECT 'Y'
1863 INTO l_conversion_exists
1864 FROM mtl_uom_class_conversions
1865 WHERE inventory_item_id = p_item_id
1866 AND to_uom_code = p_to_uom_code ;
1867 EXCEPTION
1868 WHEN no_data_found THEN
1869 print_debug(' Creating Inter-class conversion ' || g_pkg_version, 1);
1870 l_conversion_exists := 'N' ;
1871 END ;
1872
1873 IF l_conversion_exists = 'N' THEN
1874 INSERT INTO mtl_uom_class_conversions
1875 (inventory_item_id,
1876 from_unit_of_measure,
1877 from_uom_code,
1878 from_uom_class,
1879 to_unit_of_measure,
1880 to_uom_code,
1881 to_uom_class,
1882 last_update_date,
1883 last_updated_by,
1884 creation_date,
1885 created_by,
1886 last_update_login,
1887 conversion_rate)
1888 VALUES (p_item_id,
1889 l_from_unit_of_measure,
1890 p_from_uom_code,
1891 l_from_class,
1892 l_to_unit_of_measure,
1893 p_to_uom_code,
1894 l_to_class,
1895 sysdate,
1896 fnd_global.user_id,
1897 sysdate,
1898 fnd_global.user_id,
1899 -1,
1900 p_uom_rate);
1901 ELSE
1902 print_debug(' inter class conversion already exists' || g_pkg_version, 1);
1903 RAISE l_conversion_exists_exc;
1904 END IF ;
1905
1906 END IF ;
1907 END IF ;
1908
1909 print_debug(' successfully returned from the package create_uom_conversion ' || g_pkg_version, 1);
1910 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1911
1912 EXCEPTION
1913 WHEN l_invalid_uom_exc THEN
1914 fnd_message.set_name('INV', 'INV_UOM_NOTFOUND');
1915 fnd_msg_pub.ADD;
1916 x_return_status := FND_API.G_RET_STS_ERROR;
1917 WHEN l_conversion_exists_exc THEN
1918 x_return_status := l_ret_conv_exists_warning;
1919 WHEN l_invalid_item_exc THEN
1920 fnd_message.set_name('INV', 'INV_INVALID_ITEM');
1921 fnd_msg_pub.ADD;
1922 x_return_status := FND_API.G_RET_STS_ERROR;
1923 WHEN l_uom_fromto_exc THEN
1924 fnd_message.set_name('INV', 'INV_LOTC_UOM_FROMTO_ERROR');
1925 fnd_msg_pub.ADD;
1926 x_return_status := FND_API.G_RET_STS_ERROR;
1927 WHEN OTHERS THEN
1928 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1929
1930 END create_uom_conversion;
1931
1932
1933 END inv_convert;
[Home] [Help]