1 PACKAGE BODY GMD_LABUOM_CALCULATE_PKG as
2 /*$Header: GMDSUOMB.pls 120.1 2005/10/26 12:47 rajreddy noship $ */
3
4 /* =============================================
5 FUNCTION:
6 uom_conversion OVERLOADED FUNCTION
7 LAB ONLY!
8
9 DESCRIPTION:
10 This PL/SQL function is responsible for
11 calculating and returning the converted
12 quantity of an item in the unit of measure
13 specified.
14
15 The uom_conversion function ASSUMES POSITIVE NUMBERS ONLY!
16 ALL CALLERS MUST DEAL WITH NEGATIVE NUMBERS PRIOR TO
17 CALLING THIS FUNCTION!
18
19 PARAMETERS:
20 pitem_id The surrogate key of the item number
21
22 pformula_id The surrogate key for the formula/version
23 being converted. ALLOWS ZERO if performing
24 a regular conversion. FOR LAB MGT ONLY!
25
26 pcur_qty The current quantity to convert.
27
28 pcur_uom The current unit of measure to convert from.
29
30 pnew_uom The unit of measure to convert to.
31
32 patomic Flag to determine if decimal percision is
33 required as part of the conversion.
34 0 = No, provide full precision.
35 1 = Yes, provide integer ONLY!
36
37 plab_id Organization_id
38
39 pcnv_factor Conversion factor for density passed
40 by the user. NOT REQUIRED!
41 RETURNS:
42 >=0 - SUCCESS
43 -1 - Package problem.
44 -2 - Lab Type not passed for LAB conversion.
45 -3 - UOM_CLASS and conversion factor for current UOM not found.
46 -4 - UOM_CLASS and conversion factor for NEW UOM not found.
47 -5 - Cannot determine INVENTORY UOM for item.
48 -6 - UOM_CLASS and conversion factor for INV UOM not found.
49 -7 - Cannot find conversion factor for CURRENT UOM.
50 -8 - LAB CONVERSION - LM$DENSITY variable not found.
51 -9 - LAB CONVERSION - conversion factor not found.
52 -10 - Cannot find conversion factor for NEW UOM.
53 -11 - Item_id not passed as a parameter.
54 ============================================================== */
55
56 FUNCTION uom_conversion(pitem_id NUMBER,
57 pformula_id NUMBER,
58 plot_number VARCHAR2,
59 pcur_qty NUMBER,
60 pcur_uom VARCHAR2,
61 pnew_uom VARCHAR2,
62 patomic NUMBER,
63 plab_id NUMBER,
64 pcnv_factor NUMBER DEFAULT 0) RETURN NUMBER IS
65
66 -- Variable Declarations
67 l_item_invum_code mtl_units_of_measure.uom_code%TYPE;
68 l_cur_uom_code mtl_units_of_measure.uom_code%TYPE;
69 l_new_uom_code mtl_units_of_measure.uom_code%TYPE;
70 l_cur_um_type mtl_uom_classes.uom_class%TYPE;
71 l_new_um_type mtl_uom_classes.uom_class%TYPE;
72 l_inv_um_type mtl_uom_classes.uom_class%TYPE;
73 l_cur_uom_factor mtl_uom_conversions.conversion_rate%TYPE;
74 l_cur_conv_factor mtl_uom_conversions.conversion_rate%TYPE;
75 l_new_uom_factor mtl_uom_conversions.conversion_rate%TYPE;
76 l_new_conv_factor mtl_uom_conversions.conversion_rate%TYPE;
77 l_inv_uom_factor mtl_uom_conversions.conversion_rate%TYPE;
78 l_lab_conv_factor lm_item_dat.num_data%TYPE;
79 l_parm_name lm_item_dat.tech_parm_name%TYPE;
80 l_factor NUMBER;
81 l_new_qty NUMBER;
82 l_item_no mtl_system_items_kfv.concatenated_segments%type;
83 l_inventory_item_id mtl_system_items.inventory_item_id%type;
84 var1 NUMBER;
85 l_factor_len NUMBER;
86 l_factorrev_len NUMBER;
87 /* Cursor Definitions
88 ==================*/
89 CURSOR get_uom_type(Vum_code mtl_units_of_measure.uom_code%TYPE,
90 Vinventory_item_id mtl_system_items.inventory_item_id%type) IS
91 SELECT 1, uomc.uom_class um_type, uomc.conversion_rate std_factor
92 FROM mtl_uom_conversions uomc, mtl_units_of_measure uom
93 WHERE uom.uom_code = Vum_code
94 AND uomc.uom_code = uom.uom_code
95 AND uomc.inventory_item_id = Vinventory_item_id
96 UNION
97 SELECT 2, b.uom_class, a.conversion_rate
98 FROM mtl_uom_conversions a, mtl_units_of_measure b
99 WHERE a.uom_code = b.uom_code
100 AND b.uom_code = Vum_code
101 ORDER by 1;
102
103 CURSOR get_inv_uom(v_item_id mtl_system_items.inventory_item_id%type) IS
104 SELECT primary_uom_code
105 FROM mtl_system_items
106 WHERE inventory_item_id = v_item_id;
107
108 CURSOR get_lab_conv_factor(v_lab_type NUMBER,
109 v_lot_number mtl_lot_numbers.lot_number%type,
110 v_item_id mtl_system_items.inventory_item_id%type,
111 v_formula_id lm_item_dat.formula_id%TYPE,
112 v_parm_name lm_item_dat.tech_parm_name%TYPE) IS
113 SELECT num_data
114 FROM gmd_technical_data_vl
115 WHERE organization_id = v_lab_type
116 AND inventory_item_id = v_item_id
117 AND lot_number = v_lot_number
118 AND formula_id = v_formula_id
119 AND tech_parm_name = v_parm_name;
120
121 CURSOR get_item_no (Vitem_id mtl_system_items.inventory_item_id%type) IS
122 SELECT concatenated_segments
123 FROM mtl_system_items_kfv
124 WHERE inventory_item_id = Vitem_id;
125 CURSOR Cur_get_um_type (V_uom_code VARCHAR2) IS
126 SELECT uom_class
127 FROM mtl_units_of_measure
128 WHERE uom_code = V_uom_code;
129
130 l_curr_uom_type VARCHAR2(30);
131 l_new_uom_type VARCHAR2(30);
132 X_return_status VARCHAR2(1);
133 l_mass_uom_type VARCHAR2(30);
134 l_vol_uom_type VARCHAR2(30);
135 BEGIN
136 gmd_api_grp.fetch_parm_values(P_orgn_id => plab_id,
137 P_parm_name => 'GMD_MASS_UM_TYPE',
138 P_parm_value => l_mass_uom_type,
139 X_return_status => X_return_status);
140 IF (X_return_status <> 'S') THEN
141 NULL;
142 END IF;
143
144 gmd_api_grp.fetch_parm_values(P_orgn_id => plab_id,
145 P_parm_name => 'GMD_VOLUME_UM_TYPE',
146 P_parm_value => l_vol_uom_type,
147 X_return_status => X_return_status);
148 IF (X_return_status <> 'S') THEN
149 NULL;
150 END IF;
151
152 OPEN Cur_get_um_type (pcur_uom);
153 FETCH Cur_get_um_type INTO l_curr_uom_type;
154 CLOSE Cur_get_um_type;
155
156 OPEN Cur_get_um_type (pnew_uom);
157 FETCH Cur_get_um_type INTO l_new_uom_type;
158 CLOSE Cur_get_um_type;
159
160 IF (NVL(pcnv_factor,0) > 0 AND (l_curr_uom_type IN (l_mass_uom_type, l_vol_uom_type)
161 AND l_new_uom_type IN (l_mass_uom_type, l_vol_uom_type))) THEN
162 l_cur_uom_code := pcur_uom;
163 l_new_uom_code := pnew_uom;
164 l_item_invum_code := NULL;
165 l_cur_um_type := NULL;
166 l_new_um_type := NULL;
167 l_inv_um_type := NULL;
168 l_cur_uom_factor := 0;
169 l_cur_conv_factor := 0;
170 l_new_uom_factor := 0;
171 l_new_conv_factor := 0;
172 l_inv_uom_factor := 0;
173 l_factor := 0;
174 l_new_qty := 0;
175
176 /* ===================================
177 ENFORCE PARAMETER LAWS!
178 ===================================*/
179 IF(pitem_id IS NULL OR pitem_id = 0) THEN
180 RETURN UOM_NOITEM_ERR;
181 END IF;
182
183 IF(plab_id IS NULL) THEN
184 RETURN UOM_LAB_TYPE_ERR;
185 END IF;
186
187 /* First we must get the SYSTEMS density parameter
188 NAME. If we do not have one bail dude .... there
189 is a setup error!
190 =================================================*/
191 l_parm_name := FND_PROFILE.value('LM$DENSITY');
192
193 IF(l_parm_name IS NULL) THEN
194 RETURN UOM_LAB_CONST_ERR;
195 END IF;
196 /* ===================================
197 OK ... if the passed units of
198 measure are the same, then there is
199 nothing to do!
200 ===================================*/
201 IF(pcur_uom = pnew_uom) THEN
202 l_new_qty := pcur_qty;
203 RETURN l_new_qty;
204 END IF;
205
206 OPEN get_item_no(pitem_id);
207 FETCH get_item_no INTO l_item_no;
208 CLOSE get_item_no;
209
210 /* ===================================
211 Step One - determine the uom_class
212 and standard factor for the current
213 unit of measure.
214 ===================================*/
215 OPEN get_uom_type(l_cur_uom_code, pitem_id);
216 FETCH get_uom_type INTO var1, l_cur_um_type, l_cur_uom_factor;
217 IF(get_uom_type%NOTFOUND) THEN
218 CLOSE get_uom_type;
219 RETURN UOM_CUR_UOMTYPE_ERR;
220 END IF;
221 CLOSE get_uom_type;
222
223 /* ==================================
224 Step TWO - determine the uom_class
225 and standard factor for the new
226 unit of measure.
227 ==================================*/
228 OPEN get_uom_type(l_new_uom_code, pitem_id);
229 FETCH get_uom_type INTO var1, l_new_um_type, l_new_uom_factor;
230 IF(get_uom_type%NOTFOUND) THEN
231 CLOSE get_uom_type;
232 RETURN UOM_NEW_UOMTYPE_ERR;
233 END IF;
234 CLOSE get_uom_type;
235
236 /*Rounding problem corrected by changing the order of operations in uom_conversion.*/
237 IF(l_cur_um_type = l_new_um_type) THEN
238 l_new_qty := (pcur_qty * l_cur_uom_factor / l_new_uom_factor);
239 IF(patomic = 1) THEN
240 l_new_qty := ROUND(l_new_qty);
241 END IF;
242 RETURN l_new_qty;
243 END IF;
244
245 /* =========================================
246 Step Four - If the unit of measure types
247 are NOT THE SAME, get the item's inventory
248 uom class an conversion factor.
249 =========================================*/
250 IF(l_cur_um_type <> l_new_um_type) THEN
251 OPEN get_inv_uom(pitem_id);
252 FETCH get_inv_uom INTO l_item_invum_code;
253 IF(get_inv_uom%NOTFOUND) THEN
254 CLOSE get_inv_uom;
255 RETURN UOM_INVUOM_ERR;
256 END IF;
257 CLOSE get_inv_uom;
258
259 OPEN get_uom_type(l_item_invum_code, pitem_id);
260 FETCH get_uom_type INTO var1, l_inv_um_type, l_inv_uom_factor;
261 IF(get_uom_type%NOTFOUND) THEN
262 CLOSE get_uom_type;
263 RETURN UOM_INV_UOMTYPE_ERR;
264 END IF;
265 CLOSE get_uom_type;
266 END IF;
267
268 /* ==============================================
269 Step FIVE - If the uom classes for the current
270 and the inventory uoms are NOT THE SAME,
271 we need the CONVERSION factors for both the
272 current and new uoms so that we can convert
273 to the items primary uom.
274 ==============================================*/
275 IF(l_cur_um_type <> l_inv_um_type) THEN
276 IF(pcnv_factor <> 0) THEN
277 l_cur_conv_factor := pcnv_factor;
278 ELSE
279 OPEN get_lab_conv_factor(plab_id,plot_number, pitem_id, pformula_id,l_parm_name);
280 FETCH get_lab_conv_factor INTO l_cur_conv_factor;
281 IF(get_lab_conv_factor%NOTFOUND) THEN
282 CLOSE get_lab_conv_factor;
283 RETURN UOM_LAB_CONV_ERR;
284 END IF;
285 CLOSE get_lab_conv_factor;
286 END IF;
287 ELSIF(l_cur_um_type = l_inv_um_type) THEN
288 l_cur_conv_factor := cur_factor_default;
289 END IF;
290
291 /* ==============================================
292 Step SEVEN - If the uom classes are NOT THE SAME,
293 we need the CONVERSION factors for both the
294 current and new uoms so that we can convert
295 to the items primary uom.
296 ==============================================*/
297 IF(l_inv_um_type <> l_new_um_type) THEN
298 IF(pcnv_factor <> 0) THEN
299 l_new_conv_factor := pcnv_factor;
300 ELSE
301 OPEN get_lab_conv_factor(plab_id, plot_number, pitem_id, pformula_id,l_parm_name);
302 FETCH get_lab_conv_factor INTO l_new_conv_factor;
303 IF(get_lab_conv_factor%NOTFOUND) THEN
304 CLOSE get_lab_conv_factor;
305 RETURN UOM_LAB_CONV_ERR;
306 END IF;
307 CLOSE get_lab_conv_factor;
308 END IF;
309 ELSIF(l_inv_um_type = l_new_um_type) THEN
310 l_new_conv_factor := new_factor_default;
311 END IF;
312
313 /* Rounding problem corrected by changing the order of operations in uom_conversion.*/
314 l_new_qty := ((pcur_qty * l_cur_uom_factor * l_cur_conv_factor) /
315 (l_new_uom_factor * l_new_conv_factor));
316 IF(patomic = 1) THEN
317 l_new_qty := ROUND(l_new_qty);
318 END IF;
319 ELSE
320 l_new_qty := INV_CONVERT.INV_UM_CONVERT (item_id => pitem_id,
321 lot_number => plot_number,
322 organization_id => plab_id,
323 precision => 5,
324 from_quantity => pcur_qty,
325 from_unit => pcur_uom,
326 to_unit => pnew_uom,
327 from_name => NULL,
328 to_name => NULL);
329 END IF;
330 RETURN l_new_qty;
331 EXCEPTION
332 WHEN OTHERS THEN
333 RETURN SQLCODE;
334 END uom_conversion;
335
336 END GMD_LABUOM_CALCULATE_PKG;
[Home] [Help]