1 PACKAGE BODY GMISYUM AS
2 /* $Header: GMISYUMB.pls 115.3 99/07/16 04:49:14 porting ship $ */
3 FUNCTION sy_uom_find(V_uom VARCHAR2, V_um_type IN OUT VARCHAR2, V_std_factor IN OUT NUMBER) RETURN NUMBER IS
4 /****sy sy_uom_find*************************************************
5
6 * Procedure
7 * sy_uom_find
8 *
9 * Description
10 * This function is responsible for retrieving the um_type and
11 * standard factor from GEMMS given specific unit of measure
12 * code.
13 *
14 * Parameters
15 * V_uom UOM Code.
16 * V_um_type Value of UOM TYPE.
17 * V_std_factor Value of standard factor.
18 *
19 * Return Values
20 * 1 Success
21 * -1 Failure
22 * *****************************************************************/
23 CURSOR Cur_uom_fact IS
24 SELECT um_type,std_factor
25 FROM sy_uoms_mst
26 WHERE um_code = V_uom
27 AND delete_mark = 0;
28 BEGIN
29 OPEN Cur_uom_fact;
30 FETCH Cur_uom_fact INTO V_um_type, V_std_factor;
31 IF Cur_uom_fact%NOTFOUND THEN
32 CLOSE Cur_uom_fact;
33 RETURN(-1);
34 END IF;
35 CLOSE Cur_uom_fact;
36 RETURN(1);
37 END sy_uom_find;
38 FUNCTION sy_cnv_find(V_item_id NUMBER, V_lot_id NUMBER, V_um_type VARCHAR2, V_cnv_factor IN OUT NUMBER) RETURN NUMBER IS
39 /****sy sy_cnv_find**************************************
40
41 * Procedure
42 * sy_cnv_find
43 *
44 * Description
45 * This function is responsible for returning the
46 * conversion factor given a valid item_id, lot_id, and um_type.
47 *
48 * Parameters
49 * V_item_id - passed in valid item_id
50 * V_lot_id - passed in valid lot_id
51 * V_um_type - passed in valid um_type [ie MASS, VOL]
52 * V_cnv_factor - return variable with factor.
53 *
54 * Return Values
55 * 1 Success
56 * -1 Failure
57 *************************************************************************/
58 CURSOR Cur_type_factor IS
59 SELECT type_factor
60 FROM ic_item_cnv
61 WHERE item_id = V_item_id
62 AND lot_id = V_lot_id
63 AND um_type = V_um_type;
64 BEGIN
65 OPEN Cur_type_factor;
66 FETCH Cur_type_factor INTO V_cnv_factor;
67 IF Cur_type_factor%NOTFOUND THEN
68 CLOSE Cur_type_factor;
69 RETURN(-1);
70 END IF;
71 CLOSE Cur_type_factor;
72 RETURN(1);
73 END sy_cnv_find;
74 FUNCTION sy_lab_find (V_item_id NUMBER, V_lot_id NUMBER, V_lab_type VARCHAR2, V_cnv_factor IN OUT NUMBER) RETURN NUMBER IS
75 /****sy sy_lab_find**************************************
76
77 * Procedure
78 * sy_lab_find
79 *
80 * Description
81 *
82 * NOTES:
83 * For LAB the lot_id holds the formula_id. This is critical for
84 * the proper selection of the conversion factor.
85 *
86 * Lab type is a passed parameter from the call lm_uomcv. It
87 * populates the global variable 'lab_type' which is needed
88 * for the select criteria in determining the conversion factor.
89 *
90 * This is because the lab module allows for different
91 * conversion factors based on density the standard item conversion
92 * table is not used here.
93 *
94 * Parameters
95 * none
96 *
97 * Return Values
98 * 1 Success
99 * -1 Failure
100 *************************************************************************/
101 X_density VARCHAR2(40);
102 CURSOR Cur_num_data IS
103 SELECT num_data
104 FROM lm_item_dat
105 WHERE lab_type = V_lab_type
106 AND item_id = V_item_id
107 AND formula_id = V_lot_id
108 AND tech_parm_name = X_density;
109 BEGIN
110 IF NOT (FND_PROFILE.DEFINED('LM$DENSITY')) THEN
111 RETURN(-1);
112 END IF;
113 X_density := FND_PROFILE.VALUE('LM$DENSITY');
114 OPEN Cur_num_data;
115 FETCH Cur_num_data INTO V_cnv_factor;
116 IF Cur_num_data%NOTFOUND THEN
117 CLOSE Cur_num_data;
118 RETURN(-1);
119 END IF;
120 CLOSE Cur_num_data;
121 RETURN(1);
122 END sy_lab_find;
123 FUNCTION sy_uomcv(V_item_id NUMBER, V_lot_id NUMBER, V_cur_qty NUMBER,
124 V_cur_uom VARCHAR2, V_inv_uom VARCHAR2, V_new_qty IN OUT NUMBER,
125 V_new_uom VARCHAR2, V_perform_lab NUMBER DEFAULT 0,
126 V_density_conv_factor NUMBER DEFAULT 0, V_def_lab VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
127 /****sy sy_uomcv *****************************************************
128 * NAME* sy_uomcv -- perform unit of measure conversion from C
129 * SYNOPSIS
130 * int sy_uomcv (int, int, double, char *, char *, double *,
131 * char *, int)
132 * NUMBER V_item_id valid item id in GEMMS DB.
133 * NUMBER V_lot_id ZERO or valid lot_id in GEMMS DB.
134 * NUMBER V_cur_qty current quantity to convert
135 * VARCHAR2 V_cur_uom current UOM.
136 * VARCHAR2 V_inv_uom the items base inventory UOM.
137 * (NOT REQUIRED)
138 * NUMBER V_new_qty pointer to variable to hold new
139 * converted quantity.
140 * VARCHAR2 V_new_uom pointer to UOM to convert to.
141 *
142 *
143 * USAGE
144 * sy_uomcv (1, 0, 10.5, 'LB', NULL, new_qty, 'GAL', ) ;
145 *
146 * DESCRIPTION
147 * Fetches inventory unit of measure from database if not given.
148 * Caches unit of measure types and item-specific conversion factors.
149 *
150 * RETURNS
151 * 1 = ok
152 * <= 0 = error
153 * ERR_UOM_UNKNOWN = unknown error
154 * ERR_UOM_ITEM_CNV = error fetching from ic_item_cnv
155 * ERR_UOMS_MST = error fetching from sy_uoms_mst
159 * ERR_UOM_NOINV = no "INV" item-specific factor found
156 * ERR_UOM_CODE_FACTOR = item-specific factors found == 0
157 * ERR_UOM_NOCUR = no "from" item-specific factor found
158 * ERR_UOM_NONEW = no "to" item-specific factor found
160 * ERR_UOM_STD_FACTOR = standard factor found == 0
161 * ERR_UOMS_NOT_FOUND = type/factor not found
162 * ERR_UOM_ITEM_MST = Error fetching from ic_item_mst
163 * **************************************************************************/
164 ERR_UOM_UNKNOWN CONSTANT NUMBER := -3350;
165 ERR_UOM_ITEM_CNV CONSTANT NUMBER := -3351;
166 ERR_UOMS_MST CONSTANT NUMBER := -3352;
167 ERR_UOM_CODE_FACTOR CONSTANT NUMBER := -3355;
168 ERR_UOM_NOCUR CONSTANT NUMBER := -3356;
169 ERR_UOM_NONEW CONSTANT NUMBER := -3357;
170 ERR_UOM_NOINV CONSTANT NUMBER := -3358;
171 ERR_UOM_STD_FACTOR CONSTANT NUMBER := -3359;
172 ERR_UOMS_NOT_FOUND CONSTANT NUMBER := -3361;
173 ERR_UOM_ITEM_MST CONSTANT NUMBER := -3362;
174 ERR_LAB_CNV CONSTANT NUMBER := -3367;
175 CURSOR Cur_inv_uom IS
176 SELECT item_um
177 FROM ic_item_mst
178 WHERE item_id = V_item_id;
179 X_retvar NUMBER;
183 X_cur_cnv_factor NUMBER DEFAULT 1;
180 X_cur_um_type sy_uoms_typ.um_type%TYPE;
181 X_new_um_type sy_uoms_typ.um_type%TYPE;
182 X_inv_um_type sy_uoms_typ.um_type%TYPE;
184 X_new_cnv_factor NUMBER DEFAULT 1;
185 X_cur_std_factor NUMBER DEFAULT 0;
186 X_new_std_factor NUMBER DEFAULT 0;
187 X_inv_std_factor NUMBER DEFAULT 0;
188 X_factor NUMBER DEFAULT 0;
189 X_inv_uom VARCHAR2(4);
190 BEGIN
191 /* ========================================
192 * Validate passed parameters and act
193 * accordingly.
194 * ======================================== */
195 IF V_cur_qty = 0 THEN
196 V_new_qty := 0;
197 END IF;
198 IF (V_cur_uom IS NULL) THEN
199 RETURN(ERR_UOM_NOCUR);
200 END IF;
201 IF (V_new_uom IS NULL) THEN
202 RETURN(ERR_UOM_NONEW);
203 END IF;
204 /* ==================================
205 UOMs are the same, this routine
206 should not have been called so
207 update the new quantity and return
208 to caller.
209 ================================== */
210 IF (V_cur_uom = V_new_uom) THEN
211 V_new_qty := V_cur_qty;
212 Return(1);
213 END IF;
214 /* ========================================
215 Determine um_type and standard factor
216 for current uom.
217 ======================================== */
218 X_retvar := sy_uom_find (V_cur_uom, X_cur_um_type, X_cur_std_factor);
219 IF X_retvar <> 1 THEN
220 RETURN(ERR_UOM_NOCUR);
221 END IF;
222 /* ========================================
223 Determine um_type and standard factor
224 for new uom.
225 ======================================== */
226 X_retvar := sy_uom_find (V_new_uom, X_new_um_type, X_new_std_factor);
227 IF X_retvar <> 1 THEN
228 RETURN(ERR_UOM_NONEW);
229 END IF;
230 /* ========================================
231 If the um_types for both current and new
232 uom(s) ARE THE SAME, perform calculation
233 and return.
234 ============================================ */
235 IF (X_cur_um_type = X_new_um_type) THEN
236 X_factor := (X_cur_std_factor / X_new_std_factor);
237 V_new_qty := ROUND(V_cur_qty * X_factor, 9);
238 RETURN(1);
239 ELSE
240 /* ============================================
241 If the um_types for both current and new
242 uom(s) ARE NOT THE SAME, get the inventory uom
243 for the item if it is null.
244 ============================================ */
245 IF V_inv_uom IS NULL THEN
246 OPEN Cur_inv_uom;
247 FETCH Cur_inv_uom INTO X_inv_uom;
248 IF Cur_inv_uom%NOTFOUND THEN
249 CLOSE Cur_inv_uom;
250 RETURN(ERR_UOM_ITEM_MST);
251 END IF;
252 CLOSE Cur_inv_uom;
253 ELSE
254 X_inv_uom := V_inv_uom;
255 END IF;
256 /* =========================================
257 Get the um_type and standard factor for
258 the inventory uom.
259 ========================================= */
260 X_retvar := sy_uom_find (X_inv_uom, X_inv_um_type, X_inv_std_factor);
261 IF X_retvar <> 1 THEN
262 RETURN(ERR_UOM_NOINV);
263 END IF;
264 /* =========================================
265 If the um_types for both the inventory
266 uom and the current uom ARE NOT THE SAME, get
267 the CONVERSION FACTOR for the current
268 um_type.
269 ========================================= */
270 IF (X_cur_um_type <> X_inv_um_type) THEN
271 IF (V_perform_lab = 1) THEN
272 IF (V_density_conv_factor = 0) THEN
276 END IF;
273 X_retvar := sy_lab_find(V_item_id, V_lot_id, V_def_lab, X_cur_cnv_factor);
274 IF X_retvar <> 1 THEN
275 RETURN(ERR_LAB_CNV);
277 ELSE
278 X_cur_cnv_factor := V_density_conv_factor;
279 END IF;
280 ELSE
281 X_retvar := sy_cnv_find(V_item_id, V_lot_id, X_cur_um_type, X_cur_cnv_factor);
282 IF X_retvar <> 1 THEN
283 /* ======================================
284 BUSINESS RULE - if a specific lot_id
285 * conversion factor is not found, return
286 * the item specific conversion factor.
287 * ====================================== */
288 X_retvar := sy_cnv_find(V_item_id, 0, X_cur_um_type, X_cur_cnv_factor);
289 IF X_retvar <> 1 THEN
290 RETURN(ERR_UOM_ITEM_CNV);
291 END IF;
292 END IF;
293 END IF;
294 END IF;
295 /* =========================================
296 If the um_types for both the inventory
297 uom and the new uom ARE NOT THE SAME, get
298 the CONVERSION FACTOR for the new
299 um_type.
300 ========================================= */
301 IF (X_new_um_type <> X_inv_um_type) THEN
302 IF (V_perform_lab = 1) THEN
303 IF (V_density_conv_factor = 0) THEN
304 X_retvar := sy_lab_find(V_item_id, V_lot_id, V_def_lab, X_new_cnv_factor);
305 IF X_retvar <> 1 THEN
306 RETURN(ERR_LAB_CNV);
307 END IF;
308 ELSE
309 X_new_cnv_factor := V_density_conv_factor;
310 END IF;
311 ELSE
312 X_retvar := sy_cnv_find(V_item_id, V_lot_id, X_new_um_type, X_new_cnv_factor);
313 IF X_retvar <> 1 THEN
314 /* ======================================
315 * BUSINESS RULE - if a specific lot_id
316 * conversion factor is not found, return
317 * the item specific conversion factor.
318 * ====================================== */
319 X_retvar := sy_cnv_find(V_item_id, 0, X_new_um_type, X_new_cnv_factor);
320 IF X_retvar <> 1 THEN
321 RETURN(ERR_UOM_ITEM_CNV);
322 END IF;
323 END IF;
324 END IF;
325 END IF;
326 /* ==================================================
327 OK... Time to perform conversion
328 ================================================== */
329 X_factor := ((X_cur_std_factor * X_cur_cnv_factor) /
330 (X_new_std_factor * X_new_cnv_factor));
331 V_new_qty := (V_cur_qty * X_factor);
332 RETURN(1);
333 END IF;
334 END sy_uomcv;
335 END gmisyum;
[Home] [Help]