1 PACKAGE BODY GMICUOM AS
2 /* $Header: gmicuomb.pls 115.17 2003/04/07 22:15:57 adeshmuk ship $ */
3 /* =============================================
4 PROCEDURE:
5 icuomcv
6
7 DESCRIPTION:
8 This PL/SQL procedure is responsible for
9 calling the unit of measure function contained
10 in this package and handling all error messaging
11 centrally.
12
13 SYNOPSIS:
14 GMICUOM.icuomcv(pitem_id, plot_id, pcur_qty,
15 pcur_uom, pnew_uom);
16
17 ============================================= */
18 PROCEDURE icuomcv(pitem_id NUMBER,
19 plot_id NUMBER,
20 pcur_qty NUMBER,
21 pcur_uom VARCHAR2,
22 pnew_uom VARCHAR2,
23 onew_qty OUT NOCOPY NUMBER) IS
24
25 /* Local variable initialization and
26 declarations.
27 ================================= */
28 l_iret NUMBER := -1;
29 l_atomic NUMBER := 0;
30 l_neg_flag NUMBER := 0;
31 l_cur_qty NUMBER := 0;
32
33 BEGIN
34
35 IF(pcur_qty < 0) THEN
36 l_cur_qty := (pcur_qty * -1);
37 l_neg_flag := 1;
38 ELSE
39 l_cur_qty := pcur_qty;
40 END IF;
41
42 l_iret := GMICUOM.uom_conversion(pitem_id,
43 plot_id, l_cur_qty, pcur_uom, pnew_uom,
44 l_atomic);
45
46 IF(l_iret >= 0) THEN
47 IF(l_neg_flag = 1) THEN
48 onew_qty := (l_iret * -1);
49 ELSE
50 onew_qty := l_iret;
51 END IF;
52 ELSIF (l_iret = -1) THEN
53 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_PACKAGE_ERR');
54 APP_EXCEPTION.raise_exception;
55
56 ELSIF (l_iret = -3) THEN
57 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURUMTYPE_ERR');
58 APP_EXCEPTION.raise_exception;
59
60 ELSIF (l_iret = -4) THEN
61 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NEWUMTYPE_ERR');
62 APP_EXCEPTION.raise_exception;
63
64 ELSIF (l_iret = -5) THEN
65 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
66 FND_MESSAGE.set_token('FROMUOM',pcur_uom);
67 FND_MESSAGE.set_token('TOUOM',pnew_uom);
68 APP_EXCEPTION.raise_exception;
69
70 ELSIF (l_iret = -6) THEN
71 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUMTYPE_ERR');
72 APP_EXCEPTION.raise_exception;
73
74 ELSIF (l_iret = -7) THEN
75 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURFACTOR_ERR');
76 APP_EXCEPTION.raise_exception;
77
78 ELSIF (l_iret = -10) THEN
79 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
80 FND_MESSAGE.set_token('FROMUOM',pcur_uom);
81 FND_MESSAGE.set_token('TOUOM',pnew_uom);
82 APP_EXCEPTION.raise_exception;
83
84 ELSIF (l_iret = -11) THEN
85 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NOITEMID_ERR');
86 APP_EXCEPTION.raise_exception;
87
88 ELSIF (l_iret < -11) THEN
89 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_FATAL_ERR');
90 APP_EXCEPTION.raise_exception;
91
92 END IF;
93
94 END icuomcv;
95 /* =============================================
96 PROCEDURE:
97 icuomcvl LAB MGT ONLY!
98
99 DESCRIPTION:
100 This PL/SQL procedure is responsible for
101 calling the unit of measure function contained
102 in this package and handling all error messaging
103 centrally.
104
105 SYNOPSIS:
106 GMICUOM.icuomcvl(pitem_id, plot_id, pcur_qty,
107 pcur_uom, pnew_uom, pnew_qty,
108 plab_type, pcnv_factor);
109
110 ================================================
111 WJ Harris III 19-NOV-98 R11.0 resynch
112 Modified function uom_conversion for LAB.
113 Modified procedure icuomcvl for LAB.
114 Added parameter pcnv_factor to both to comply
115 with Sierra Atlantic changes for Laboratory Mgt.
116 ============================================= */
117 PROCEDURE icuomcvl(pitem_id NUMBER,
118 pformula_id NUMBER,
119 pcur_qty NUMBER,
120 pcur_uom VARCHAR2,
121 pnew_uom VARCHAR2,
122 plab_type VARCHAR2,
123 pcnv_factor NUMBER,
124 onew_qty OUT NOCOPY NUMBER) IS
125
126 /* Local variable initialization and
127 declarations.
128 ================================= */
129 l_iret NUMBER := -1;
130 l_atomic NUMBER := 0;
131 l_neg_flag NUMBER := 0;
132 l_cur_qty NUMBER := 0;
133
134 BEGIN
135
136 IF(pcur_qty < 0) THEN
137 l_cur_qty := (pcur_qty * -1);
138 l_neg_flag := 1;
139 ELSE
140 l_cur_qty := pcur_qty;
141 END IF;
142
143 l_iret := GMICUOM.uom_conversion(pitem_id,
144 pformula_id, l_cur_qty, pcur_uom, pnew_uom,
145 l_atomic, plab_type, pcnv_factor);
146
147 IF(l_iret >= 0) THEN
148 IF(l_neg_flag = 1) THEN
149 onew_qty := (l_iret * -1);
150 ELSE
151 onew_qty := l_iret;
152 END IF;
153 ELSIF (l_iret = -1) THEN
154 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_PACKAGE_ERR');
155 APP_EXCEPTION.raise_exception;
156
157 ELSIF (l_iret = -2) THEN
158 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_LABTYPE_ERR');
159 APP_EXCEPTION.raise_exception;
160
161 ELSIF (l_iret = -3) THEN
162 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURUMTYPE_ERR');
163 APP_EXCEPTION.raise_exception;
164
165 ELSIF (l_iret = -4) THEN
166 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NEWUMTYPE_ERR');
167 APP_EXCEPTION.raise_exception;
168
169 ELSIF (l_iret = -5) THEN
170 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR');
171 APP_EXCEPTION.raise_exception;
172
173 ELSIF (l_iret = -6) THEN
174 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUMTYPE_ERR');
175 APP_EXCEPTION.raise_exception;
176
177 ELSIF (l_iret = -7) THEN
178 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURFACTOR_ERR');
179 APP_EXCEPTION.raise_exception;
180
181 ELSIF (l_iret = -8) THEN
182 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_LMDENSITY_ERR');
183 APP_EXCEPTION.raise_exception;
184
185 ELSIF (l_iret = -9) THEN
186 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_LABFACTOR_ERR');
187 APP_EXCEPTION.raise_exception;
188
189 ELSIF (l_iret = -10) THEN
190 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
191 FND_MESSAGE.set_token('FROMUOM',pcur_uom);
192 FND_MESSAGE.set_token('TOUOM',pnew_uom);
193 APP_EXCEPTION.raise_exception;
194
195 ELSIF (l_iret = -11) THEN
196 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NOITEMID_ERR');
197 APP_EXCEPTION.raise_exception;
198
199 ELSIF (l_iret < -11) THEN
200 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_FATAL_ERR');
201 APP_EXCEPTION.raise_exception;
202
203 END IF;
204
205 END icuomcvl;
206 /* =============================================
207 FUNCTION:
208 uom_conversion OVERLOADED FUNCTION!
209
210 DESCRIPTION:
211 This PL/SQL function is responsible for
212 calculating and returning the converted
213 quantity of an item in the unit of measure
214 specified.
215
216 The uom_conversion function ASSUMES POSITIVE NUMBERS ONLY!
217 ALL CALLERS MUST DEAL WITH NEGATIVE NUMBERS PRIOR TO
218 CALLING THIS FUNCTION!
219
220 PARAMETERS:
221 pitem_id The surrogate key of the item number
222
223 plot_id The surrogate key for the lot number/
224 sublot of the item number being converted.
225 ALLOWS ZERO if performing a LAB conversion.
226
227 pcur_qty The current quantity to convert.
228
229 pcur_uom The current unit of measure to convert from.
230
231 pnew_uom The unit of measure to convert to.
232
233 patomic Flag to determine if decimal percision is
234 required as part of the conversion.
235 0 = No, provide full precision.
236 1 = Yes, provide integer ONLY!
237
238 SPECIAL NOTES:
239
240 RETURNS:
241 >=0 - SUCCESS
242 -1 - Package problem.
243 -3 - UM_TYPE and conversion factor for current UOM not found.
244 -4 - UM_TYPE and conversion factor for NEW UOM not found.
245 -5 - Cannot determine INVENTORY UOM for item.
246 -6 - UM_TYPE and conversion factor for INV UOM not found.
247 -7 - Cannot find conversion factor for CURRENT UOM.
248 -10 - Cannot find conversion factor for NEW UOM.
249 -11 - Item_id not passed as a parameter.
250
251 HISTORY
252 Jalaj Srivastava Bug 1713699 05-APR-2001
253 Apps provides item specific intra class conversion.
254 we need to use them if it exists for a item.
255
256 G. Muratore Bug 2236392 15-APR-2002
257 Rounding problem corrected by changing the order of operations
258 in uom_conversion. The code no longer derives the factor and
259 then applies. It does the full calculation in one step.
260
261 A. Mundhe Bug 2844068 07-APR-2003
262 Modified the uom_conversion function to use more - precise type
263 factor or type factorrev and to calculate the new qty accordingly.
264 ============================================================== */
265 FUNCTION uom_conversion(pitem_id NUMBER,
266 plot_id NUMBER,
267 pcur_qty NUMBER ,
268 pcur_uom VARCHAR2,
269 pnew_uom VARCHAR2,
270 patomic NUMBER)
271 RETURN NUMBER IS
272 /* Variable Declarations
273 ===================== */
274 l_item_invum_code uomcode_type;
275 l_cur_uom_code uomcode_type;
276 l_new_uom_code uomcode_type;
277 l_cur_um_type sy_uoms_typ.um_type%TYPE;
278 l_new_um_type sy_uoms_typ.um_type%TYPE;
279 l_inv_um_type sy_uoms_typ.um_type%TYPE;
280 l_cur_uom_factor ic_item_cnv.type_factor%TYPE;
281 l_cur_conv_factor ic_item_cnv.type_factor%TYPE;
282 l_cur_conv_factorrev ic_item_cnv.type_factor%TYPE; /* Bug 2844068 */
283 l_new_uom_factor ic_item_cnv.type_factor%TYPE;
284 l_new_conv_factor ic_item_cnv.type_factor%TYPE;
285 l_new_conv_factorrev ic_item_cnv.type_factor%TYPE; /* Bug 2844068 */
286 l_inv_uom_factor ic_item_cnv.type_factor%TYPE;
287 l_lab_conv_factor lm_item_dat.num_data%TYPE;
288 l_parm_name lm_item_dat.tech_parm_name%TYPE;
289 l_factor NUMBER;
290 l_new_qty NUMBER;
291 l_item_no ic_item_mst.item_no%type;
292 l_inventory_item_id mtl_system_items.inventory_item_id%type;
293 var1 NUMBER;
294
295 /* Bug 2844068 */
296 l_factor_len NUMBER;
297 l_factorrev_len NUMBER;
298
299 /* Cursor Definitions
300 ==================*/
301 CURSOR get_uom_type(Vum_code uomcode_type,
302 Vinventory_item_id mtl_system_items.inventory_item_id%type) IS
303 SELECT 1, uomc.uom_class um_type, uomc.conversion_rate std_factor
304 FROM mtl_uom_conversions uomc, mtl_units_of_measure uom, sy_uoms_mst sy
305 WHERE sy.um_code = Vum_code
306 AND uom.unit_of_measure = sy.unit_of_measure
307 AND uomc.uom_code = uom.uom_code
308 AND uomc.inventory_item_id = Vinventory_item_id
309 AND ( (uomc.disable_date IS NULL)
310 OR (uomc.disable_date > sysdate) )
311 UNION
312 SELECT 2, um_type, std_factor
313 FROM sy_uoms_mst
314 WHERE um_code = Vum_code
315 AND delete_mark = 0
316 ORDER by 1;
317
318
319 CURSOR get_inv_uom(v_item_id itm_surg_type) IS
320 SELECT item_um
321 FROM ic_item_mst
322 WHERE item_id = v_item_id;
323
324 /* Bug 2844068 */
325 /* Added type_factorrev to the query */
326 CURSOR get_conversion_factor(v_item_id itm_surg_type,
327 v_lot_id lot_surg_type,
328 v_um_type sy_uoms_typ.um_type%TYPE) IS
329 SELECT type_factor, type_factorrev
330 FROM ic_item_cnv
331 WHERE item_id = v_item_id
332 AND lot_id = v_lot_id
333 AND delete_mark = 0
334 AND um_type = v_um_type;
335
336 CURSOR get_item_no (Vitem_id ic_item_mst.item_id%type) IS
337 SELECT item_no
338 FROM ic_item_mst
339 WHERE item_id = Vitem_id;
340
341 CURSOR get_inventory_item_id(Vitem_no ic_item_mst.item_no%type) IS
342 SELECT inventory_item_id
343 FROM mtl_system_items
344 WHERE segment1= Vitem_no
345 AND rownum = 1;
346 /* ================================================*/
347 BEGIN
348
349 l_cur_uom_code := pcur_uom;
350 l_new_uom_code := pnew_uom;
351 l_item_invum_code := NULL;
352 l_cur_um_type := NULL;
353 l_new_um_type := NULL;
354 l_inv_um_type := NULL;
355 l_cur_uom_factor := 0;
356 l_cur_conv_factor := 0;
357 l_cur_conv_factorrev := 0; /* Bug 2844068 */
358 l_new_uom_factor := 0;
359 l_new_conv_factor := 0;
360 l_new_conv_factorrev := 0; /* Bug 2844068 */
361 l_inv_uom_factor := 0;
362 l_factor := 0;
363 l_new_qty := 0;
364
365 /* ===================================
366 ENFORCE PARAMETER LAWS!
367 ===================================*/
368 IF(pitem_id IS NULL) THEN
369 RETURN UOM_NOITEM_ERR;
370 END IF;
371
372 /* ===================================
373 OK ... if the passed units of
374 measure are the same, then there is
375 nothing to do!
376 ===================================*/
377 IF(pcur_uom = pnew_uom) THEN
378 l_new_qty := pcur_qty;
379 RETURN l_new_qty;
380 END IF;
381 /*====================================
382 Jalaj Srivastava Bug 1713699
383 We need opm item no and inventory item
384 id in order to get specific
385 item conversions.
386 ===================================*/
387 OPEN get_item_no(pitem_id);
388 FETCH get_item_no INTO l_item_no;
389 CLOSE get_item_no;
390
391 OPEN get_inventory_item_id(l_item_no);
392 FETCH get_inventory_item_id INTO l_inventory_item_id;
393 CLOSE get_inventory_item_id;
394
395 /* ===================================
396 Step One - determine the um_type
397 and standard factor for the current
398 unit of measure.
399 ===================================*/
400 OPEN get_uom_type(l_cur_uom_code, l_inventory_item_id);
401 FETCH get_uom_type INTO
402 var1, l_cur_um_type, l_cur_uom_factor;
403
404 IF(get_uom_type%NOTFOUND) THEN
405
406 CLOSE get_uom_type;
407 RETURN UOM_CUR_UOMTYPE_ERR;
408 END IF;
409
410
411 CLOSE get_uom_type;
412
413 /* ==================================
414 Step TWO - determine the um_type
415 and standard factor for the new
416 unit of measure.
417 ==================================*/
418 OPEN get_uom_type(l_new_uom_code, l_inventory_item_id);
419 FETCH get_uom_type INTO
420 var1, l_new_um_type, l_new_uom_factor;
421
422 IF(get_uom_type%NOTFOUND) THEN
423
424 CLOSE get_uom_type;
425 RETURN UOM_NEW_UOMTYPE_ERR;
426 END IF;
427
428
429 CLOSE get_uom_type;
430
431 /* =========================================
432 Step Three - If the unit of measure types
433 are the SAME, perform calculation cause
434 we are done!
435 ========================================= */
436 /*
437 IF(l_cur_um_type = l_new_um_type) THEN
438 l_factor := (l_cur_uom_factor / l_new_uom_factor);
439 IF(patomic = 1) THEN
440 l_new_qty := ROUND(pcur_qty * l_factor);
441 ELSE
442 l_new_qty := (pcur_qty * l_factor);
443 END IF;
444
445 RETURN l_new_qty;
446 END IF;
447 */
448
449 /* Bug 2236392 Rounding problem corrected by
450 changing the order of operations in uom_conversion.
451 Old code commented out above. */
452 IF(l_cur_um_type = l_new_um_type) THEN
453 l_new_qty := (pcur_qty * l_cur_uom_factor / l_new_uom_factor);
454
455 IF(patomic = 1) THEN
456 l_new_qty := ROUND(l_new_qty);
457 END IF;
458
459 RETURN l_new_qty;
460 END IF;
461
462
463 /* =========================================
464 Step Four - If the unit of measure types
465 are NOT THE SAME, get the item's inventory
466 uom type an conversion factor.
467 ========================================= */
468 IF(l_cur_um_type <> l_new_um_type) THEN
469 OPEN get_inv_uom(pitem_id);
470 FETCH get_inv_uom INTO
471 l_item_invum_code;
472
473 IF(get_inv_uom%NOTFOUND) THEN
474
475 CLOSE get_inv_uom;
476 RETURN UOM_INVUOM_ERR;
477 END IF;
478 CLOSE get_inv_uom;
479
480 OPEN get_uom_type(l_item_invum_code, l_inventory_item_id);
481 FETCH get_uom_type INTO
482 var1, l_inv_um_type, l_inv_uom_factor;
483
484 IF(get_uom_type%NOTFOUND) THEN
485
486 CLOSE get_uom_type;
487 RETURN UOM_INV_UOMTYPE_ERR;
488 END IF;
489
490 CLOSE get_uom_type;
491
492 END IF;
493
494 /* ==============================================
495 Step FIVE - If the uom types for the current
496 and the inventory uoms are NOT THE SAME,
497 we need the CONVERSION factors for both the
498 current and new uoms so that we can convert
499 to the items primary uom.
500 ============================================== */
501
502 /* Bug 2844068 */
503 /* Fetch type_factorrev from the cursor */
504 IF(l_cur_um_type <> l_inv_um_type) THEN
505 OPEN get_conversion_factor(pitem_id, plot_id, l_cur_um_type);
506 FETCH get_conversion_factor INTO
507 l_cur_conv_factor, l_cur_conv_factorrev;
508
509 IF(get_conversion_factor%NOTFOUND) THEN
510 /* =======================================
511 We have to check for an ITEM SPECIFIC
512 conversion factor if one does not exist
513 for the lot.
514 ======================================= */
515 CLOSE get_conversion_factor;
516
517 /* Bug 2844068 */
518 /* Fetch type_factorrev from the cursor */
519 OPEN get_conversion_factor(pitem_id, default_lot,
520 l_cur_um_type);
521 FETCH get_conversion_factor INTO
522 l_cur_conv_factor, l_cur_conv_factorrev;
523
524 IF(get_conversion_factor%NOTFOUND) THEN
525 CLOSE get_conversion_factor;
526 RETURN UOM_CUR_CONV_ERR;
527 END IF;
528 END IF;
529 CLOSE get_conversion_factor;
530 ELSIF(l_cur_um_type = l_inv_um_type) THEN
531 l_cur_conv_factor := cur_factor_default;
532 /* Bug 2844068 */
533 l_cur_conv_factorrev := cur_factor_default;
534 END IF;
535
536 /* ==============================================
537 Step SEVEN - If the uom types are NOT THE SAME,
538 we need the CONVERSION factors for both the
539 current and new uoms so that we can convert
540 to the items primary uom.
541 ==============================================*/
542
543 IF(l_inv_um_type <> l_new_um_type) THEN
544 /* Bug 2844068 */
545 /* Fetch type_factorrev from the cursor */
546 OPEN get_conversion_factor(pitem_id, plot_id, l_new_um_type);
547 FETCH get_conversion_factor INTO
548 l_new_conv_factor, l_new_conv_factorrev;
549
550 IF(get_conversion_factor%NOTFOUND) THEN
551 /* =======================================
552 We have to check for an ITEM SPECIFIC
553 conversion factor if one does not exist
554 for the lot.
555 =======================================*/
556 CLOSE get_conversion_factor;
557
558 OPEN get_conversion_factor(pitem_id, default_lot,
559 l_new_um_type);
560 /* Bug 2844068 */
561 /* Fetch type_factorrev from the cursor */
562 FETCH get_conversion_factor INTO
563 l_new_conv_factor, l_new_conv_factorrev;
564
565 IF(get_conversion_factor%NOTFOUND) THEN
566 CLOSE get_conversion_factor;
567 RETURN UOM_NEW_CONV_ERR;
568 END IF;
569 END IF;
570 CLOSE get_conversion_factor;
571
572 ELSIF(l_inv_um_type = l_new_um_type) THEN
573 l_new_conv_factor := new_factor_default;
574 /* Bug 2844068 */
575 l_new_conv_factorrev := new_factor_default;
576 END IF;
577
578 /* ======================================
579 Conversion Please .... Thank you very
580 much!!!
581 ======================================*/
582 /*
583 l_factor := ((l_cur_uom_factor * l_cur_conv_factor) /
584 (l_new_uom_factor * l_new_conv_factor));
585 IF(patomic = 1) THEN
586 l_new_qty := ROUND(pcur_qty * l_factor);
587 ELSE
588 l_new_qty := (pcur_qty * l_factor);
589 END IF;
590
591 RETURN l_new_qty;
592 */
593
594 /* Bug 2236392 Rounding problem corrected by
595 changing the order of operations in uom_conversion.
596 Old code commented out above. */
597
598 /* Bug 2844068 */
599 /* Calculate the number of decimal places in type factor and type factorrev.
600 Use the factor with less number of decimal places and calcualte the new qty
601 accordingly. */
602
603 l_factor_len := length(TO_CHAR(l_new_conv_factor - floor(l_new_conv_factor)))-1;
604 l_factorrev_len := length(TO_CHAR(l_new_conv_factorrev - floor(l_new_conv_factorrev)))-1;
605
606 IF (l_factor_len < l_factorrev_len) THEN
607 l_new_qty := ((pcur_qty * l_cur_uom_factor * l_cur_conv_factor) /
608 (l_new_uom_factor * l_new_conv_factor));
609 ELSE
610 l_new_qty := ((pcur_qty * l_cur_uom_factor * l_cur_conv_factor * l_new_conv_factorrev) /
611 (l_new_uom_factor));
612 END IF;
613
614 IF(patomic = 1) THEN
615 l_new_qty := ROUND(l_new_qty);
616 END IF;
617
618 RETURN l_new_qty;
619
620 EXCEPTION
621 WHEN OTHERS THEN
622
623 RETURN SQLCODE;
624 END uom_conversion;
625 /* =============================================
626 FUNCTION:
627 uom_conversion OVERLOADED FUNCTION
628 LAB ONLY!
629
630 DESCRIPTION:
631 This PL/SQL function is responsible for
632 calculating and returning the converted
633 quantity of an item in the unit of measure
634 specified.
635
636 The uom_conversion function ASSUMES POSITIVE NUMBERS ONLY!
637 ALL CALLERS MUST DEAL WITH NEGATIVE NUMBERS PRIOR TO
638 CALLING THIS FUNCTION!
639
640 PARAMETERS:
641 pitem_id The surrogate key of the item number
642
643 pformula_id The surrogate key for the formula/version
644 being converted. ALLOWS ZERO if performing
645 a regular conversion. FOR LAB MGT ONLY!
646
647 pcur_qty The current quantity to convert.
648
649 pcur_uom The current unit of measure to convert from.
650
651 pnew_uom The unit of measure to convert to.
652
653 patomic Flag to determine if decimal percision is
654 required as part of the conversion.
655 0 = No, provide full precision.
656 1 = Yes, provide integer ONLY!
657
658 plab_type The technical parameter group name.
659 IT IS REQUIRED.
660
661 pcnv_factor Conversion factor for density passed
662 by the user. NOT REQUIRED!
663
664 SPECIAL NOTES:
665 Added new parameter pcnv_factor to coincide with
666 Sierra Atlantic changes for lab UOM conversions. This
667 allows the user to perform conversions based on user
668 entry for the value of conversion factor instead of
669 getting the value from the database.
670
671 RETURNS:
672 >=0 - SUCCESS
673 -1 - Package problem.
674 -2 - Lab Type not passed for LAB conversion.
675 -3 - UM_TYPE and conversion factor for current UOM not found.
676 -4 - UM_TYPE and conversion factor for NEW UOM not found.
677 -5 - Cannot determine INVENTORY UOM for item.
678 -6 - UM_TYPE and conversion factor for INV UOM not found.
679 -7 - Cannot find conversion factor for CURRENT UOM.
680 -8 - LAB CONVERSION - LM$DENSITY variable not found.
681 -9 - LAB CONVERSION - conversion factor not found.
682 -10 - Cannot find conversion factor for NEW UOM.
683 -11 - Item_id not passed as a parameter.
684
685 HISTORY:
686 WJ Harris III 19-NOV-98 resynch
687 Added pcnv_factor parameter for laboratory management to
688 coincide with Sierra Atlantic modifications approved by
689 Karen Theel. These were not communicated or approved by
690 anyone else.
691 Jalaj Srivastava Bug 1713699 05-APR-2001
692 Apps provides item specific intra class conversion.
693 we need to use them if it exists for a item.
694
695 G. Muratore Bug 2236392 15-APR-2002
696 Rounding problem corrected by changing the order of operations
697 in uom_conversion. The code no longer derives the factor and
698 then applies. It does the full calculation in one step.
699
700 ============================================================== */
701 FUNCTION uom_conversion(pitem_id NUMBER,
702 pformula_id NUMBER,
703 pcur_qty NUMBER,
704 pcur_uom VARCHAR2,
705 pnew_uom VARCHAR2,
706 patomic NUMBER,
707 plab_type VARCHAR2,
708 pcnv_factor NUMBER DEFAULT 0)
709 RETURN NUMBER IS
710 /* Variable Declarations
711 =====================*/
712 l_item_invum_code uomcode_type;
713 l_cur_uom_code uomcode_type;
714 l_new_uom_code uomcode_type;
715 l_cur_um_type sy_uoms_typ.um_type%TYPE;
716 l_new_um_type sy_uoms_typ.um_type%TYPE;
717 l_inv_um_type sy_uoms_typ.um_type%TYPE;
718 l_cur_uom_factor ic_item_cnv.type_factor%TYPE;
719 l_cur_conv_factor ic_item_cnv.type_factor%TYPE;
720 l_new_uom_factor ic_item_cnv.type_factor%TYPE;
721 l_new_conv_factor ic_item_cnv.type_factor%TYPE;
722 l_inv_uom_factor ic_item_cnv.type_factor%TYPE;
723 l_lab_conv_factor lm_item_dat.num_data%TYPE;
724 l_parm_name lm_item_dat.tech_parm_name%TYPE;
725 l_factor NUMBER;
726 l_new_qty NUMBER;
727 l_item_no ic_item_mst.item_no%type;
728 l_inventory_item_id mtl_system_items.inventory_item_id%type;
729 var1 NUMBER;
730
731 /* Cursor Definitions
732 ==================*/
733 CURSOR get_uom_type(Vum_code uomcode_type,
734 Vinventory_item_id mtl_system_items.inventory_item_id%type) IS
735 SELECT 1, uomc.uom_class um_type, uomc.conversion_rate std_factor
736 FROM mtl_uom_conversions uomc, mtl_units_of_measure uom, sy_uoms_mst sy
737 WHERE sy.um_code = Vum_code
738 AND uom.unit_of_measure = sy.unit_of_measure
739 AND uomc.uom_code = uom.uom_code
740 AND uomc.inventory_item_id = Vinventory_item_id
741 UNION
742 SELECT 2, um_type, std_factor
743 FROM sy_uoms_mst
744 WHERE um_code = Vum_code
745 AND delete_mark = 0
746 ORDER by 1;
747
748 CURSOR get_inv_uom(v_item_id itm_surg_type) IS
749 SELECT item_um
750 FROM ic_item_mst
751 WHERE item_id = v_item_id;
752
753 CURSOR get_conversion_factor(v_item_id itm_surg_type,
754 v_lot_id lot_surg_type,
755 v_um_type sy_uoms_typ.um_type%TYPE) IS
756 SELECT type_factor
757 FROM ic_item_cnv
758 WHERE item_id = v_item_id
759 AND lot_id = v_lot_id
760 AND delete_mark = 0
761 AND um_type = v_um_type;
762
763 CURSOR get_lab_conv_factor(v_lab_type lab_type,
764 v_item_id itm_surg_type,
765 v_formula_id form_surg_type,
766 v_parm_name lm_item_dat.tech_parm_name%TYPE) IS
767 SELECT num_data
768 FROM lm_item_dat
769 WHERE orgn_code = v_lab_type
770 AND item_id = v_item_id
771 AND formula_id = v_formula_id
772 AND tech_parm_name = v_parm_name
773 AND delete_mark = 0;
774
775 CURSOR get_item_no (Vitem_id ic_item_mst.item_id%type) IS
776 SELECT item_no
777 FROM ic_item_mst
778 WHERE item_id = Vitem_id;
779
780 CURSOR get_inventory_item_id(Vitem_no ic_item_mst.item_no%type) IS
781 SELECT inventory_item_id
782 FROM mtl_system_items
783 WHERE segment1= Vitem_no
784 AND rownum = 1;
785
786 /* ================================================*/
787 BEGIN
788
789 l_cur_uom_code := pcur_uom;
790 l_new_uom_code := pnew_uom;
791 l_item_invum_code := NULL;
792 l_cur_um_type := NULL;
793 l_new_um_type := NULL;
794 l_inv_um_type := NULL;
795 l_cur_uom_factor := 0;
796 l_cur_conv_factor := 0;
797 l_new_uom_factor := 0;
798 l_new_conv_factor := 0;
799 l_inv_uom_factor := 0;
800 l_factor := 0;
801 l_new_qty := 0;
802
803 /* ===================================
804 ENFORCE PARAMETER LAWS!
805 ===================================*/
806 IF(pitem_id IS NULL OR pitem_id = 0) THEN
807 RETURN UOM_NOITEM_ERR;
808 END IF;
809
810 IF(plab_type IS NULL) THEN
811 RETURN UOM_LAB_TYPE_ERR;
812 END IF;
813
814 /* First we must get the SYSTEMS density parameter
815 NAME. If we do not have one bail dude .... there
816 is a setup error!
817 =================================================*/
818 l_parm_name := FND_PROFILE.value('LM$DENSITY');
819
820 IF(l_parm_name IS NULL) THEN
821 RETURN UOM_LAB_CONST_ERR;
822 END IF;
823 /* ===================================
824 OK ... if the passed units of
825 measure are the same, then there is
826 nothing to do!
827 ===================================*/
828 IF(pcur_uom = pnew_uom) THEN
829 l_new_qty := pcur_qty;
830 RETURN l_new_qty;
831 END IF;
832
833 /*====================================
834 Jalaj Srivastava Bug 1713699
835 We need to get the item no and
836 inventory item id in order to get
837 specific item conversions.
838 ===================================*/
839 OPEN get_item_no(pitem_id);
840 FETCH get_item_no INTO l_item_no;
841 CLOSE get_item_no;
842
843 OPEN get_inventory_item_id(l_item_no);
844 FETCH get_inventory_item_id INTO l_inventory_item_id;
845 CLOSE get_inventory_item_id;
846
847
848 /* ===================================
849 Step One - determine the um_type
850 and standard factor for the current
851 unit of measure.
852 ===================================*/
853 OPEN get_uom_type(l_cur_uom_code, l_inventory_item_id);
854 FETCH get_uom_type INTO
855 var1, l_cur_um_type, l_cur_uom_factor;
856
857 IF(get_uom_type%NOTFOUND) THEN
858 CLOSE get_uom_type;
859 RETURN UOM_CUR_UOMTYPE_ERR;
860 END IF;
861
862 CLOSE get_uom_type;
863
864 /* ==================================
865 Step TWO - determine the um_type
866 and standard factor for the new
867 unit of measure.
868 ==================================*/
869 OPEN get_uom_type(l_new_uom_code, l_inventory_item_id);
870 FETCH get_uom_type INTO
871 var1, l_new_um_type, l_new_uom_factor;
872
873 IF(get_uom_type%NOTFOUND) THEN
874
875 CLOSE get_uom_type;
876 RETURN UOM_NEW_UOMTYPE_ERR;
877 END IF;
878
879 CLOSE get_uom_type;
880
881 /* =========================================
882 Step Three - If the unit of measure types
883 are the SAME, perform calculation cause
884 we are done!
885 =========================================*/
886 /*
887 IF(l_cur_um_type = l_new_um_type) THEN
888 l_factor := (l_cur_uom_factor / l_new_uom_factor);
889 IF(patomic = 1) THEN
890 l_new_qty := ROUND(pcur_qty * l_factor);
891 ELSE
892 l_new_qty := (pcur_qty * l_factor);
893 END IF;
894
895
896 RETURN l_new_qty;
897 END IF;
898 */
899
900 /* Bug 2236392 Rounding problem corrected by
901 changing the order of operations in uom_conversion.
902 Old code commented out above. */
903 IF(l_cur_um_type = l_new_um_type) THEN
904 l_new_qty := (pcur_qty * l_cur_uom_factor / l_new_uom_factor);
905
906 IF(patomic = 1) THEN
907 l_new_qty := ROUND(l_new_qty);
908 END IF;
909
910 RETURN l_new_qty;
911 END IF;
912
913
914 /* =========================================
915 Step Four - If the unit of measure types
916 are NOT THE SAME, get the item's inventory
917 uom type an conversion factor.
918 =========================================*/
919 IF(l_cur_um_type <> l_new_um_type) THEN
920 OPEN get_inv_uom(pitem_id);
921 FETCH get_inv_uom INTO
922 l_item_invum_code;
923
924 IF(get_inv_uom%NOTFOUND) THEN
925
926 CLOSE get_inv_uom;
927 RETURN UOM_INVUOM_ERR;
928 END IF;
929 CLOSE get_inv_uom;
930
931 OPEN get_uom_type(l_item_invum_code, l_inventory_item_id);
932 FETCH get_uom_type INTO
933 var1, l_inv_um_type, l_inv_uom_factor;
934
935 IF(get_uom_type%NOTFOUND) THEN
936
937 CLOSE get_uom_type;
938 RETURN UOM_INV_UOMTYPE_ERR;
939 END IF;
940
941 CLOSE get_uom_type;
942
943 END IF;
944
945 /* ==============================================
946 Step FIVE - If the uom types for the current
947 and the inventory uoms are NOT THE SAME,
948 we need the CONVERSION factors for both the
949 current and new uoms so that we can convert
950 to the items primary uom.
951 ==============================================*/
952 IF(l_cur_um_type <> l_inv_um_type) THEN
953 /* ===========================================
954 let's get the lab conversion factor
955 shall we ?! If the user has passed a
956 conversion factor to use, then use it.
957 Otherwise ..... lets get it from the database.
958 ==============================================*/
959 IF(pcnv_factor <> 0) THEN
960 l_cur_conv_factor := pcnv_factor;
961 ELSE
962 OPEN get_lab_conv_factor(plab_type, pitem_id, pformula_id,
963 l_parm_name);
964 FETCH get_lab_conv_factor INTO
965 l_cur_conv_factor;
966
967 IF(get_lab_conv_factor%NOTFOUND) THEN
968 CLOSE get_lab_conv_factor;
969 RETURN UOM_LAB_CONV_ERR;
970 END IF;
971 CLOSE get_lab_conv_factor;
972 END IF;
973 ELSIF(l_cur_um_type = l_inv_um_type) THEN
974 l_cur_conv_factor := cur_factor_default;
975 END IF;
976
977 /* ==============================================
978 Step SEVEN - If the uom types are NOT THE SAME,
979 we need the CONVERSION factors for both the
980 current and new uoms so that we can convert
981 to the items primary uom.
982 ==============================================*/
983 IF(l_inv_um_type <> l_new_um_type) THEN
984 /* ===========================================
985 Let's get the lab conversion factor
986 shall we ?! If the user has passed a
987 conversion factor to use, then use it.
988 Otherwise ..... lets get it from the database.
989 ===========================================*/
990 IF(pcnv_factor <> 0) THEN
991 l_new_conv_factor := pcnv_factor;
992 ELSE
993 OPEN get_lab_conv_factor(plab_type, pitem_id, pformula_id,
994 l_parm_name);
995 FETCH get_lab_conv_factor INTO
996 l_new_conv_factor;
997
998 IF(get_lab_conv_factor%NOTFOUND) THEN
999 CLOSE get_lab_conv_factor;
1000 RETURN UOM_LAB_CONV_ERR;
1001 END IF;
1002 CLOSE get_lab_conv_factor;
1003 END IF;
1004 ELSIF(l_inv_um_type = l_new_um_type) THEN
1005 l_new_conv_factor := new_factor_default;
1006 END IF;
1007
1008 /* ======================================
1009 Conversion Please .... Thank you very
1010 much!!!
1011 ======================================*/
1012 /*
1013 l_factor := ((l_cur_uom_factor * l_cur_conv_factor) /
1014 (l_new_uom_factor * l_new_conv_factor));
1015 IF(patomic = 1) THEN
1016 l_new_qty := ROUND(pcur_qty * l_factor);
1017 ELSE
1018 l_new_qty := (pcur_qty * l_factor);
1019 END IF;
1020
1021
1022 RETURN l_new_qty;
1023 */
1024
1025 /* Bug 2236392 Rounding problem corrected by
1026 changing the order of operations in uom_conversion.
1027 Old code commented out above. */
1028 l_new_qty := ((pcur_qty * l_cur_uom_factor * l_cur_conv_factor) /
1029 (l_new_uom_factor * l_new_conv_factor));
1030
1031 IF(patomic = 1) THEN
1032 l_new_qty := ROUND(l_new_qty);
1033 END IF;
1034
1035 RETURN l_new_qty;
1036
1037
1038 EXCEPTION
1039 WHEN OTHERS THEN
1040
1041 RETURN SQLCODE;
1042 END uom_conversion;
1043 /* =============================================
1044 FUNCTION:
1045 i2uom_cv
1046
1047 DESCRIPTION:
1048 This PL/SQL function is responsible for
1049 calculating and returning the converted
1050 quantity of an item in the unit of measure
1051 specified from within a select statement for I2.
1052
1053 PARAMETERS:
1054 pitem_id The surrogate key of the item number
1055
1056 plot_id The surrogate key for the lot number/
1057 sublot of the item number being converted.
1058 ALLOWS ZERO if performing a LAB conversion.
1059
1060 pcur_qty The current quantity to convert.
1061
1062 pcur_uom The current unit of measure to convert from.
1063
1064 pnew_uom The unit of measure to convert to.
1065
1066 SPECIAL NOTES:
1067
1068 RETURNS:
1069 0 - SUCCESS
1070 -1 - Package problem.
1071 -3 - UM_TYPE and conversion factor for current UOM not found.
1072 -4 - UM_TYPE and conversion factor for NEW UOM not found.
1073 -5 - Cannot determine INVENTORY UOM for item.
1074 -6 - UM_TYPE and conversion factor for INV UOM not found.
1075 -7 - Cannot find conversion factor for CURRENT UOM.
1076 -10 - Cannot find conversion factor for NEW UOM.
1077 ============================================================== */
1078 FUNCTION i2uom_cv(pitem_id NUMBER,
1079 plot_id NUMBER,
1080 pcur_uom VARCHAR2,
1081 pcur_qty NUMBER ,
1082 pnew_uom VARCHAR2)
1083 RETURN NUMBER IS
1084
1085 /* Variable Declarations
1086 =====================*/
1087 l_iret NUMBER := -1;
1088 l_atomic NUMBER := 0;
1089
1090 BEGIN
1091
1092 l_iret := GMICUOM.uom_conversion(pitem_id, plot_id,
1093 pcur_qty, pcur_uom, pnew_uom, l_atomic);
1094
1095 IF(l_iret <= 0) THEN
1096 RETURN 0;
1097 ELSE
1098 RETURN l_iret;
1099 END IF;
1100
1101 EXCEPTION
1102 WHEN OTHERS THEN
1103 RETURN SQLCODE;
1104
1105 END i2uom_cv;
1106
1107
1108 END;
[Home] [Help]