[Home] [Help]
PACKAGE BODY: APPS.GMD_FETCH_VALIDITY_RULES
Source
1 PACKAGE BODY gmd_fetch_validity_rules AS
2 /* $Header: GMDPVRFB.pls 120.3 2006/11/21 16:43:54 txdaniel ship $ */
3
4 G_PKG_NAME VARCHAR2(32);
5
6 /*======================================================================
7 -- PROCEDURE :
8 -- get_validity_rules
9 --
10 -- DESCRIPTION:
11 -- This PL/SQL procedure is responsible for getting the
12 -- validity rules based on the input parameters.
13 --
14 -- REQUIREMENTS
15 --
16 -- SYNOPSIS:
17 -- get_validity_rules (1.0, X_init_msg_list, X_recipe_id, X_item_id,
18 -- X_orgn_id, X_product_qty, X_uom, X_recipe_use,
19 -- X_total_input, X_total_output, X_status,
20 -- X_return_status, X_msg_count, X_msg_data,
21 -- X_return_code, X_vr_table);
22 --
23 --
24 --===================================================================== */
25 PROCEDURE get_validity_rules(p_api_version IN NUMBER,
26 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
27 p_recipe_id IN NUMBER,
28 p_item_id IN NUMBER := NULL,
29 p_organization_id IN NUMBER := NULL,
30 p_product_qty IN NUMBER := NULL,
31 p_uom IN VARCHAR2 := NULL,
32 p_recipe_use IN VARCHAR2 := NULL,
33 p_total_input IN NUMBER,
34 p_total_output IN NUMBER,
35 p_status IN VARCHAR2 := NULL,
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_msg_count OUT NOCOPY NUMBER,
38 x_msg_data OUT NOCOPY VARCHAR2,
39 x_return_code OUT NOCOPY NUMBER,
40 X_recipe_validity_out OUT NOCOPY recipe_validity_tbl) IS
41
42 -- local Variables
43 l_api_name VARCHAR2(30) := 'get_validity_rules';
44 l_api_version NUMBER := 1.0;
45 i NUMBER := 0;
46
47 l_item_uom VARCHAR2(4);
48 l_line_um VARCHAR2(4);
49 l_quantity NUMBER;
50 l_item_qty NUMBER;
51 l_scale_type NUMBER;
52 l_msg_count NUMBER;
53 l_msg_data VARCHAR2(240);
54 l_return_code NUMBER;
55 l_return_status VARCHAR2(10);
56 l_yield_um VARCHAR2(4);
57 l_formula_id NUMBER;
58 l_formula_output NUMBER;
59 l_formula_input NUMBER;
60 l_total_output NUMBER;
61 l_total_input NUMBER;
62 l_output_ratio NUMBER;
63 l_ingred_prod_ratio NUMBER;
64 l_batchformula_ratio NUMBER;
65 l_contributing_qty NUMBER;
66 l_yield_type VARCHAR2(25);
67 l_return_stat VARCHAR2(10);
68
69 --Cursor to get data based on recipe ID and input and output qty.
70 CURSOR get_val(v_orgn_id NUMBER) IS
71 SELECT recipe_validity_rule_id, recipe_id , organization_id, inventory_item_id ,revision, recipe_use,
72 preference, start_date, end_date , min_qty, max_qty , std_qty, detail_uom,
73 inv_min_qty, inv_max_qty, delete_mark, validity_rule_status
74 FROM gmd_recipe_validity_rules
75 WHERE recipe_id = p_recipe_id
76 AND ((validity_rule_status BETWEEN 700 AND 799) OR (validity_rule_status BETWEEN 400 AND 499) )
77 AND (p_recipe_use IS NULL OR recipe_use = p_recipe_use)
78 AND ( organization_id = v_orgn_id OR v_orgn_id IS NULL)
79 AND (TRUNC(SYSDATE) BETWEEN TRUNC(start_date) AND nvl(TRUNC(end_date),SYSDATE+1))
80 ORDER BY preference;
81
82 --Cursor to get data based on item.
83 CURSOR get_val_item(v_orgn_id NUMBER, l_quantity NUMBER) IS
84 SELECT recipe_validity_rule_id, recipe_id , organization_id, inventory_item_id ,revision, recipe_use,
85 preference, start_date, end_date , min_qty, max_qty , std_qty, detail_uom,
86 inv_min_qty, inv_max_qty, delete_mark, validity_rule_status
87 FROM gmd_recipe_validity_rules
88 WHERE (inventory_item_id = p_item_id)
89 AND ((validity_rule_status BETWEEN 700 AND 799) OR (validity_rule_status BETWEEN 400 AND 499 ))
90 AND (inv_min_qty <= l_quantity AND inv_max_qty >= l_quantity )
91 AND ((organization_id = v_orgn_id) OR (v_orgn_id IS NULL))
92 AND ((recipe_use = p_recipe_use) OR (p_recipe_use IS NULL))
93 ORDER BY preference;
94
95 CURSOR cur_item_uom(p_item_id NUMBER) IS
96 SELECT primary_uom_code
97 FROM mtl_system_items
98 WHERE inventory_item_id = p_item_id;
99
100 CURSOR Cur_std_um (v_yield_type VARCHAR2 ) IS
101 SELECT uom_code
102 FROM mtl_units_of_measure
103 WHERE uom_class = v_yield_type
104 AND base_uom_flag = 'Y';
105
106 CURSOR Cur_get_qty(V_item_id NUMBER) IS
107 SELECT qty, scale_type, detail_uom
108 FROM fm_matl_dtl
109 WHERE formula_id = l_formula_id
110 AND inventory_item_id = V_item_id
111 AND line_type = 1
112 ORDER BY line_no;
113
114 /* Exceptions */
115 NO_YIELD_TYPE_UM EXCEPTION;
116 GET_FORMULA_ERR EXCEPTION;
117 GET_TOTAL_QTY_ERR EXCEPTION;
118 GET_OUTPUT_RATIO_ERR EXCEPTION;
119 GET_INGREDPROD_RATIO_ERR EXCEPTION;
120 GET_BATCHFORMULA_RATIO_ERR EXCEPTION;
121 GET_CONTRIBUTING_QTY_ERR EXCEPTION;
122 GET_INPUT_RATIO_ERR EXCEPTION;
123 ITEM_UOM_CONV_ERR EXCEPTION;
124 UOM_CONVERSION_ERROR EXCEPTION;
125 BEGIN
126 IF (NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
127 l_api_name, G_PKG_NAME)) THEN
128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
129 END IF;
130 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
131 FND_MSG_PUB.initialize;
132 END IF;
133 X_return_status := FND_API.G_RET_STS_SUCCESS;
134
135 -- FND_PROFILE.VALUE_SPECIFIC('FM_YIELD_TYPE',FND_GLOBAL.USER_ID);
136 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => p_organization_id ,
137 P_parm_name => 'FM_YIELD_TYPE' ,
138 P_parm_value => l_yield_type ,
139 X_return_status => l_return_stat );
140
141 /* Get yield type um */
142 OPEN Cur_std_um(l_yield_type);
143 FETCH Cur_std_um INTO l_yield_um;
144 IF (Cur_std_um%NOTFOUND) THEN
145 CLOSE Cur_std_um;
146 RAISE NO_YIELD_TYPE_UM;
147 END IF;
148 CLOSE Cur_std_um;
149 /* Check for possible ways to get validity rules */
150 IF (p_recipe_id IS NOT NULL AND p_total_output IS NOT NULL OR
151 p_recipe_id IS NOT NULL AND p_total_input IS NOT NULL) THEN
152 /* Get the formula for this recipe */
153 gmd_recipe_fetch_pub.get_formula_id(p_api_version => p_api_version,
154 p_init_msg_list => p_init_msg_list,
155 p_recipe_no => NULL,
156 p_recipe_version => NULL,
157 p_recipe_id => p_recipe_id,
158 x_return_status => l_return_status,
159 x_msg_count => l_msg_count,
160 x_msg_data => l_msg_data,
161 x_return_code => l_return_code,
162 x_formula_id => l_formula_id);
163 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
164 RAISE GET_FORMULA_ERR;
165 END IF;
166 gmd_common_val.calculate_total_qty(formula_id => l_formula_id,
167 x_product_qty => l_formula_output,
168 x_ingredient_qty => l_formula_input,
169 x_uom => l_yield_um,
170 x_return_status => l_return_status,
171 X_MSG_COUNT => l_msg_count,
172 X_MSG_DATA => l_msg_data );
173 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
174 RAISE GET_TOTAL_QTY_ERR;
175 END IF;
176 IF (p_total_output IS NOT NULL) THEN
177 /* Convert total output qty to standard UOM of FM_YIELD_TYPE */
178 IF (p_uom <> l_yield_um) THEN
179 -- l_total_output := gmicuom.uom_conversion(0, 0, p_total_output, p_uom, l_yield_um, 0);
180 l_total_output := INV_CONVERT.inv_um_convert(item_id => 0
181 ,precision => 5
182 ,from_quantity => p_total_output
183 ,from_unit => p_uom
184 ,to_unit => l_yield_um
185 ,from_name => NULL
186 ,to_name => NULL);
187 ELSE
188 l_total_output := p_total_output;
189 END IF;
190
191 /* Try to get validity rules based on recipe ID and total output qty */
192 /* Get the ratio of the batch output qty to the ratio of the formula ouput qty */
193 gmd_fetch_validity_rules.get_output_ratio(p_formula_id => l_formula_id,
194 p_batch_output => l_total_output,
195 p_yield_um => l_yield_um,
196 p_formula_output => l_formula_output,
197 x_return_status => l_return_status,
198 X_output_ratio => l_output_ratio);
199 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
200 RAISE GET_OUTPUT_RATIO_ERR;
201 END IF;
202 ELSIF (p_total_input IS NOT NULL) THEN
203 /* Try to get validity rules based on recipe ID and total input qty */
204
205 /* Convert total input qty to standard UOM of FM_YIELD_TYPE */
206 IF (p_uom <> l_yield_um) THEN
207 -- l_total_input := gmicuom.uom_conversion(0, 0, p_total_input, p_uom, l_yield_um, 0);
208 l_total_input := INV_CONVERT.inv_um_convert(item_id => 0
209 ,precision => 5
210 ,from_quantity => p_total_input
211 ,from_unit => p_uom
212 ,to_unit => l_yield_um
213 ,from_name => NULL
214 ,to_name => NULL);
215 ELSE
216 l_total_input := p_total_input;
217 END IF;
218
219 /* Get the product to ingredient ratio for the formula */
220 gmd_fetch_validity_rules.get_ingredprod_ratio(p_formula_id => l_formula_id,
221 p_yield_um => l_yield_um,
222 x_return_status => l_return_status,
223 X_ingred_prod_ratio => l_ingred_prod_ratio);
224 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
225 RAISE GET_INGREDPROD_RATIO_ERR;
226 END IF;
227 /* Get the ratio of the batch input to the formula input */
228 gmd_fetch_validity_rules.get_batchformula_ratio(p_formula_id => l_formula_id,
229 p_batch_input => l_total_input,
230 p_yield_um => l_yield_um,
231 p_formula_input => l_formula_input,
232 x_return_status => l_return_status,
233 X_batchformula_ratio => l_batchformula_ratio);
234 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
235 RAISE GET_BATCHFORMULA_RATIO_ERR;
236 END IF;
237
238 /* Get the contributing qty of the formula */
239 gmd_fetch_validity_rules.get_contributing_qty(p_formula_id => l_formula_id,
240 p_recipe_id => p_recipe_id,
241 p_batchformula_ratio => l_batchformula_ratio,
242 p_yield_um => l_yield_um,
243 x_return_status => l_return_status,
244 X_contributing_qty => l_contributing_qty);
245 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
246 RAISE GET_CONTRIBUTING_QTY_ERR;
247 END IF;
248 /* Calculate actual contributing qty of formula */
249 l_contributing_qty := l_contributing_qty * l_ingred_prod_ratio;
250
251 /* Get the ratio of the product based on contributing qty */
252 gmd_fetch_validity_rules.get_input_ratio(p_formula_id => l_formula_id,
253 p_contributing_qty => l_contributing_qty,
254 p_yield_um => l_yield_um,
255 p_formula_output => l_formula_output,
256 x_return_status => l_return_status,
257 X_output_ratio => l_output_ratio);
258 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
259 RAISE GET_INPUT_RATIO_ERR;
260 END IF;
261 END IF;
262
263 /* Get all the possible validity rules and check if it can be used for this input/output qty */
264 FOR get_rec IN get_val(p_organization_id) LOOP
265 OPEN Cur_get_qty(get_rec.inventory_item_id);
266 FETCH Cur_get_qty INTO l_item_qty, l_scale_type, l_line_um;
267 CLOSE Cur_get_qty;
268 IF (l_scale_type = 1) THEN
269 l_item_qty := l_item_qty * l_output_ratio;
270 IF (l_line_um <> get_rec.detail_uom) THEN
271
272 l_item_qty := INV_CONVERT.inv_um_convert(item_id => get_rec.inventory_item_id
273 ,precision => 5
274 ,from_quantity => l_item_qty
275 ,from_unit => l_line_um
276 ,to_unit => get_rec.detail_uom
277 ,from_name => NULL
278 ,to_name => NULL);
279
280 END IF;
281 IF (l_item_qty >= get_rec.min_qty AND l_item_qty <= get_rec.max_qty) THEN
282 i := i + 1;
283 x_recipe_validity_out(i).recipe_validity_rule_id := get_rec.recipe_validity_rule_id;
284 x_recipe_validity_out(i).recipe_id := get_rec.recipe_id;
285 x_recipe_validity_out(i).inventory_item_id := get_rec.inventory_item_id;
286 x_recipe_validity_out(i).revision := get_rec.revision;
287 x_recipe_validity_out(i).recipe_use := get_rec.recipe_use;
288 x_recipe_validity_out(i).std_qty := get_rec.std_qty;
289 x_recipe_validity_out(i).organization_id := get_rec.organization_id;
290 x_recipe_validity_out(i).preference := get_rec.preference ;
291 x_recipe_validity_out(i).start_date := get_rec.start_date;
292 x_recipe_validity_out(i).end_date := get_rec.end_date;
293 x_recipe_validity_out(i).min_qty := get_rec.min_qty;
294 x_recipe_validity_out(i).max_qty := get_rec.max_qty;
295 x_recipe_validity_out(i).std_qty := get_rec.std_qty;
296 x_recipe_validity_out(i).detail_uom := get_rec.detail_uom;
297 x_recipe_validity_out(i).inv_min_qty := get_rec.inv_min_qty;
298 x_recipe_validity_out(i).inv_max_qty := get_rec.inv_max_qty;
299 x_recipe_validity_out(i).validity_rule_status := get_rec.validity_rule_status;
300 END IF;
301 END IF;
302 END LOOP;
303 ELSIF (p_recipe_id IS NOT NULL) THEN
304 /* Try to get validity rules based on recipe ID */
305 FOR get_rec IN get_val(p_organization_id) LOOP
306 x_return_status := FND_API.G_RET_STS_SUCCESS;
307 i := i + 1;
308 x_recipe_validity_out(i).recipe_validity_rule_id := get_rec.recipe_validity_rule_id;
309 x_recipe_validity_out(i).recipe_id := get_rec.recipe_id;
310 x_recipe_validity_out(i).inventory_item_id := get_rec.inventory_item_id;
311 x_recipe_validity_out(i).revision := get_rec.revision;
312 x_recipe_validity_out(i).recipe_use := get_rec.recipe_use;
313 x_recipe_validity_out(i).std_qty := get_rec.std_qty;
314 x_recipe_validity_out(i).organization_id := get_rec.organization_id;
315 x_recipe_validity_out(i).preference := get_rec.preference;
316 x_recipe_validity_out(i).start_date := get_rec.start_date;
317 x_recipe_validity_out(i).end_date := get_rec.end_date;
318 x_recipe_validity_out(i).min_qty := get_rec.min_qty;
319 x_recipe_validity_out(i).max_qty := get_rec.max_qty;
320 x_recipe_validity_out(i).std_qty := get_rec.std_qty;
321 x_recipe_validity_out(i).detail_uom := get_rec.detail_uom;
322 x_recipe_validity_out(i).inv_min_qty := get_rec.inv_min_qty;
323 x_recipe_validity_out(i).inv_max_qty := get_rec.inv_max_qty;
324 x_recipe_validity_out(i).validity_rule_status := get_rec.validity_rule_status;
325 END LOOP;
326 ELSIF (p_item_id IS NOT NULL) THEN
327 /* Try to get validity rules based on Item */
328 OPEN cur_item_uom(p_item_id);
329 FETCH cur_item_uom INTO l_item_uom;
330 CLOSE cur_item_uom;
331 IF (p_uom <> l_item_uom) THEN
332 l_quantity := INV_CONVERT.inv_um_convert(item_id => p_item_id
333 ,precision => 5
334 ,from_quantity => p_product_qty
335 ,from_unit => p_uom
336 ,to_unit => l_item_uom
337 ,from_name => NULL
338 ,to_name => NULL);
339 IF (l_quantity < 0) THEN
340 RAISE UOM_CONVERSION_ERROR;
341 END IF;
342 ELSE
343 l_quantity := p_product_qty;
344 END IF;
345 FOR get_rec IN get_val_item(p_organization_id, l_quantity) LOOP
346 x_return_status := FND_API.G_RET_STS_SUCCESS;
347 i := i + 1;
348 x_recipe_validity_out(i).recipe_validity_rule_id := get_rec.recipe_validity_rule_id;
349 x_recipe_validity_out(i).recipe_id := get_rec.recipe_id;
350 x_recipe_validity_out(i).inventory_item_id := get_rec.inventory_item_id;
351 x_recipe_validity_out(i).revision := get_rec.revision;
352 x_recipe_validity_out(i).recipe_use := get_rec.recipe_use;
353 x_recipe_validity_out(i).std_qty := get_rec.std_qty;
354 x_recipe_validity_out(i).organization_id := get_rec.organization_id;
355 x_recipe_validity_out(i).preference := get_rec.preference;
356 x_recipe_validity_out(i).start_date := get_rec.start_date;
357 x_recipe_validity_out(i).end_date := get_rec.end_date;
358 x_recipe_validity_out(i).min_qty := get_rec.min_qty;
359 x_recipe_validity_out(i).max_qty := get_rec.max_qty;
360 x_recipe_validity_out(i).std_qty := get_rec.std_qty;
361 x_recipe_validity_out(i).detail_uom := get_rec.detail_uom;
362 x_recipe_validity_out(i).inv_min_qty := get_rec.inv_min_qty;
363 x_recipe_validity_out(i).inv_max_qty := get_rec.inv_max_qty;
364 x_recipe_validity_out(i).validity_rule_status := get_rec.validity_rule_status;
365 END LOOP;
366 END IF;
367 -- standard call to get msge cnt, and if cnt is 1, get mesg info
368 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
369 EXCEPTION
370 WHEN NO_YIELD_TYPE_UM THEN
371 x_return_status := FND_API.G_RET_STS_ERROR;
372 FND_MESSAGE.SET_NAME('GMD', 'FM_SCALE_BAD_YIELD_TYPE');
373 FND_MSG_PUB.ADD;
374 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
375 P_data => x_msg_data);
376
377 WHEN GET_FORMULA_ERR THEN
378 x_return_status := FND_API.G_RET_STS_ERROR;
379 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
380 P_data => x_msg_data);
381
382 WHEN GET_TOTAL_QTY_ERR OR GET_OUTPUT_RATIO_ERR
383 OR GET_INGREDPROD_RATIO_ERR OR GET_BATCHFORMULA_RATIO_ERR
384 OR GET_CONTRIBUTING_QTY_ERR OR GET_INPUT_RATIO_ERR THEN
385 x_return_status := FND_API.G_RET_STS_ERROR;
386 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
387 P_data => x_msg_data);
388 WHEN UOM_CONVERSION_ERROR THEN
389 x_return_status := FND_API.G_RET_STS_ERROR;
390 gmd_fetch_validity_rules.uom_conversion_mesg(p_item_id => p_item_id,
391 p_from_um => p_uom,
392 p_to_um => l_item_uom);
393 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
394 P_data => x_msg_data);
395
396 WHEN FND_API.G_EXC_ERROR THEN
397 X_return_code := SQLCODE;
398 x_return_status := FND_API.G_RET_STS_ERROR;
399 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
400
401 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
402 X_return_code := SQLCODE;
403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
404 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
405
406 WHEN OTHERS THEN
407 X_return_code := SQLCODE;
408 x_return_status := FND_API.G_RET_STS_ERROR;
409 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
410 END get_validity_rules;
411
412 /*======================================================================
413 -- PROCEDURE :
414 -- get_output_ratio
415 --
416 -- DESCRIPTION:
417 -- This PL/SQL procedure is responsible for determining
418 -- the output ratio which is the ratio of the batch output
419 -- to the formula output when a total output qty is used as
420 -- the criteria for a validity rule.
421 --
422 -- REQUIREMENTS
423 --
424 -- SYNOPSIS:
425 -- get_output_ratio (X_formula_id, X_batch_output, X_yield_um,
426 -- X_formula_output, X_return_status, X_output_ratio);
427 --
428 --===================================================================== */
429 PROCEDURE get_output_ratio(p_formula_id IN NUMBER,
430 p_batch_output IN NUMBER,
431 p_yield_um IN VARCHAR2,
432 p_formula_output IN NUMBER,
433 x_return_status OUT NOCOPY VARCHAR2,
434 X_output_ratio OUT NOCOPY NUMBER) IS
435 CURSOR Cur_get_prods IS
436 SELECT inventory_item_id, qty, detail_uom, scale_type
437 FROM fm_matl_dtl
438 WHERE formula_id = p_formula_id
439 AND line_type IN (1,2);
440
441 l_batch_output NUMBER := 0;
442 l_formula_output NUMBER := 0;
443 l_conv_qty NUMBER;
444 l_total_fixed_qty NUMBER := 0;
445 X_item_id NUMBER;
446 X_item_um VARCHAR2(4);
447 UOM_CONVERSION_ERROR EXCEPTION;
448 BEGIN
449 x_return_status := FND_API.G_RET_STS_SUCCESS;
450 FOR get_rec IN Cur_get_prods LOOP
451 IF (get_rec.scale_type = 0) THEN
452 IF (get_rec.detail_uom <> p_yield_um) THEN
453 l_conv_qty := INV_CONVERT.inv_um_convert(item_id => get_rec.inventory_item_id
454 ,precision => 5
455 ,from_quantity => get_rec.qty
456 ,from_unit => get_rec.detail_uom
457 ,to_unit => p_yield_um
458 ,from_name => NULL
459 ,to_name => NULL);
460 IF (l_conv_qty < 0) THEN
461 X_item_id := get_rec.inventory_item_id;
462 X_item_um := get_rec.detail_uom;
463 RAISE UOM_CONVERSION_ERROR;
464 END IF;
465 l_total_fixed_qty := l_total_fixed_qty + l_conv_qty;
466 ELSE
467 l_total_fixed_qty := l_total_fixed_qty + get_rec.qty;
468 END IF;
469 END IF;
470 END LOOP;
471 l_batch_output := p_batch_output - l_total_fixed_qty;
472 l_formula_output := p_formula_output - l_total_fixed_qty;
473 X_output_ratio := l_batch_output/l_formula_output;
474 EXCEPTION
475 WHEN UOM_CONVERSION_ERROR THEN
476 x_return_status := FND_API.G_RET_STS_ERROR;
477 gmd_fetch_validity_rules.uom_conversion_mesg(p_item_id => X_item_id,
478 p_from_um => X_item_um,
479 p_to_um => p_yield_um);
480 END get_output_ratio;
481
482 /*======================================================================
483 -- PROCEDURE :
484 -- get_ingredprod_ratio
485 --
486 -- DESCRIPTION:
487 -- This PL/SQL procedure is responsible for determining
488 -- the ratio of the products to ingredients while trying
489 -- to determine validity rules based on total input qty.
490 --
491 -- REQUIREMENTS
492 --
493 -- SYNOPSIS:
494 -- get_ingredprod_ratio (X_formula_id, X_yield_um,
495 -- X_ingred_prod_ratio, X_status);
496 --
497 --===================================================================== */
498 PROCEDURE get_ingredprod_ratio(p_formula_id IN NUMBER,
499 p_yield_um IN VARCHAR2,
500 X_ingred_prod_ratio OUT NOCOPY NUMBER,
501 x_return_status OUT NOCOPY VARCHAR2) IS
502 CURSOR Cur_get_details(V_line_type NUMBER) IS
503 SELECT inventory_item_id, qty, detail_uom, scale_type, contribute_yield_ind
504 FROM fm_matl_dtl
505 WHERE formula_id = p_formula_id
506 AND line_type = V_line_type;
507 l_sum_prods NUMBER := 0;
508 l_sum_ingreds NUMBER := 0;
509 l_conv_qty NUMBER := 0;
510 X_item_id NUMBER;
511 X_item_um VARCHAR2(4);
512 UOM_CONVERSION_ERROR EXCEPTION;
513 BEGIN
514 x_return_status := FND_API.G_RET_STS_SUCCESS;
515 --Get sum of products in yield UM.
516 FOR get_rec IN Cur_get_details(1) LOOP
517 IF (get_rec.detail_uom <> p_yield_um) THEN
518 l_conv_qty := INV_CONVERT.inv_um_convert(item_id => get_rec.inventory_item_id
519 ,precision => 5
520 ,from_quantity => get_rec.qty
521 ,from_unit => get_rec.detail_uom
522 ,to_unit => p_yield_um
523 ,from_name => NULL
524 ,to_name => NULL);
525 IF (l_conv_qty < 0) THEN
526 X_item_id := get_rec.inventory_item_id;
527 X_item_um := get_rec.detail_uom;
528 RAISE UOM_CONVERSION_ERROR;
529 END IF;
530 l_sum_prods := l_sum_prods + l_conv_qty;
531 ELSE
532 l_sum_prods := l_sum_prods + get_rec.qty;
533 END IF;
534 END LOOP;
535 --Get sum of ingredients in yield UM contributing to yield.
536 FOR get_rec IN Cur_get_details(-1) LOOP
537 IF (get_rec.contribute_yield_ind = 'Y') THEN
538 IF (get_rec.detail_uom <> p_yield_um) THEN
539 l_conv_qty := INV_CONVERT.inv_um_convert(item_id => get_rec.inventory_item_id
540 ,precision => 5
541 ,from_quantity => get_rec.qty
542 ,from_unit => get_rec.detail_uom
543 ,to_unit => p_yield_um
544 ,from_name => NULL
545 ,to_name => NULL);
546 IF (l_conv_qty < 0) THEN
547 X_item_id := get_rec.inventory_item_id;
548 X_item_um := get_rec.detail_uom;
549 RAISE UOM_CONVERSION_ERROR;
550 END IF;
551 l_sum_ingreds := l_sum_ingreds + l_conv_qty;
552 ELSE
553 l_sum_ingreds := l_sum_ingreds + get_rec.qty;
554 END IF;
555 END IF;
556 END LOOP;
557 --Get ratio and return.
558 X_ingred_prod_ratio := l_sum_prods/l_sum_ingreds;
559 EXCEPTION
560 WHEN UOM_CONVERSION_ERROR THEN
561 x_return_status := FND_API.G_RET_STS_ERROR;
562 gmd_fetch_validity_rules.uom_conversion_mesg(p_item_id => X_item_id,
563 p_from_um => X_item_um,
564 p_to_um => p_yield_um);
565 END get_ingredprod_ratio;
566
567 /*======================================================================
568 -- PROCEDURE :
569 -- get_batchformula_ratio
570 --
571 -- DESCRIPTION:
572 -- This PL/SQL procedure is responsible for determining
573 -- the ratio of the batch input qty to the formula input qty
574 -- while determining validity rules based on total input qty.
575 --
576 -- REQUIREMENTS
577 --
578 -- SYNOPSIS:
579 -- get_batchformula_ratio (X_formula_id, X_batch_input, X_yield_um,
580 -- X_formula_input, X_batchformula_ratio,
581 -- X_status);
582 --
583 --===================================================================== */
584 PROCEDURE get_batchformula_ratio(p_formula_id IN NUMBER,
585 p_batch_input IN NUMBER,
586 p_yield_um IN VARCHAR2,
587 p_formula_input IN NUMBER,
588 X_batchformula_ratio OUT NOCOPY NUMBER,
589 X_return_status OUT NOCOPY VARCHAR2) IS
590 CURSOR Cur_get_ingreds IS
591 SELECT inventory_item_id, qty, detail_uom, scale_type
592 FROM fm_matl_dtl
593 WHERE formula_id = p_formula_id
594 AND line_type = -1;
595 CURSOR Cur_get_total_input IS
596 SELECT total_input_qty, yield_uom
597 FROM fm_form_mst
598 WHERE formula_id = p_formula_id;
599 l_formula_input NUMBER := 0;
600 l_fixed_ingred NUMBER := 0;
601 l_batch_input NUMBER := 0;
602 l_conv_qty NUMBER := 0;
603 X_item_id NUMBER;
604 X_item_um VARCHAR2(4);
605 UOM_CONVERSION_ERROR EXCEPTION;
606 BEGIN
607 x_return_status := FND_API.G_RET_STS_SUCCESS;
608 FOR get_rec IN Cur_get_ingreds LOOP
609 IF (get_rec.scale_type = 0) THEN
610 IF (get_rec.detail_uom <> p_yield_um) THEN
611 l_conv_qty := INV_CONVERT.inv_um_convert(item_id => get_rec.inventory_item_id
612 ,precision => 5
613 ,from_quantity => get_rec.qty
614 ,from_unit => get_rec.detail_uom
615 ,to_unit => p_yield_um
616 ,from_name => NULL
617 ,to_name => NULL);
618 IF (l_conv_qty < 0) THEN
619 X_item_id := get_rec.inventory_item_id;
620 X_item_um := get_rec.detail_uom;
621 RAISE UOM_CONVERSION_ERROR;
622 END IF;
623 l_fixed_ingred := l_fixed_ingred + l_conv_qty;
624 ELSE
625 l_fixed_ingred := l_fixed_ingred + get_rec.qty;
626 END IF;
627 END IF;
628 END LOOP;
629 l_batch_input := p_batch_input - l_fixed_ingred;
630 l_formula_input := p_formula_input - l_fixed_ingred;
631 X_batchformula_ratio := l_batch_input / l_formula_input;
632 EXCEPTION
633 WHEN UOM_CONVERSION_ERROR THEN
634 x_return_status := FND_API.G_RET_STS_ERROR;
635 gmd_fetch_validity_rules.uom_conversion_mesg(p_item_id => X_item_id,
636 p_from_um => X_item_um,
637 p_to_um => p_yield_um);
638 END get_batchformula_ratio;
639
640 /*======================================================================
641 -- PROCEDURE :
642 -- get_contributing_qty
643 --
644 -- DESCRIPTION:
645 -- This PL/SQL procedure is responsible for determining
646 -- the actual contributing qty of the formula.
647 --
648 -- REQUIREMENTS
649 --
650 -- SYNOPSIS:
651 -- get_contributing_qty (X_formula_id, X_recipe_id,
652 -- X_formula_batch_ratio, X_yield_um,
653 -- X_formula_input, X_ratio, X_status);
654 -- Shyam 06/05/01 Modified call to process loss calc
655 --===================================================================== */
656 PROCEDURE get_contributing_qty(p_formula_id IN NUMBER,
657 p_recipe_id IN NUMBER,
658 p_batchformula_ratio IN NUMBER,
659 p_yield_um IN VARCHAR2,
660 X_contributing_qty OUT NOCOPY NUMBER,
661 X_return_status OUT NOCOPY VARCHAR2) IS
662 CURSOR Cur_get_ingreds IS
663 SELECT inventory_item_id, qty, detail_uom, scale_type, contribute_yield_ind
664 FROM fm_matl_dtl
665 WHERE formula_id = p_formula_id
666 AND line_type = -1;
667 l_conv_qty NUMBER := 0;
668 l_process_loss NUMBER := 0;
669 X_item_id NUMBER;
670 X_item_um VARCHAR2(4);
671 X_status VARCHAR2(100);
672 l_process_rec gmd_common_val.process_loss_rec;
673 l_recipe_theo_loss NUMBER := 0;
674 l_msg_data varchar2(240);
675 l_msg_count number := 0;
676 UOM_CONVERSION_ERROR EXCEPTION;
677 PROCESS_LOSS_ERR EXCEPTION;
678 BEGIN
679 -- Initialize variable to 0.
680 X_contributing_qty := 0;
681
682 x_return_status := FND_API.G_RET_STS_SUCCESS;
683 /* Loop through ingredients and determine total contributing qty */
684 FOR get_rec IN Cur_get_ingreds LOOP
685 IF (get_rec.contribute_yield_ind = 'Y') THEN
686 /* Convert all ingredient values to yield UM and determine contributing qty */
687 IF (get_rec.detail_uom <> p_yield_um) THEN
688 l_conv_qty := INV_CONVERT.inv_um_convert(item_id => get_rec.inventory_item_id
689 ,precision => 5
690 ,from_quantity => get_rec.qty
691 ,from_unit => get_rec.detail_uom
692 ,to_unit => p_yield_um
693 ,from_name => NULL
694 ,to_name => NULL);
695 IF (l_conv_qty < 0) THEN
696 X_item_id := get_rec.inventory_item_id;
697 X_item_um := get_rec.detail_uom;
698 RAISE UOM_CONVERSION_ERROR;
699 END IF;
700 ELSE
701 l_conv_qty := get_rec.qty;
702 END IF;
703 /* If ingredient scalable multiply by ratio and calculate contributing qty */
704 IF (get_rec.scale_type = 1) THEN
705 X_contributing_qty := X_contributing_qty + (l_conv_qty * p_batchformula_ratio);
706 ELSE
707 X_contributing_qty := X_contributing_qty + l_conv_qty;
708 END IF;
709 END IF;
710 END LOOP;
711 /* Get process loss for this qty */
712 l_process_rec.qty := X_contributing_qty;
713 l_process_rec.recipe_id := p_recipe_id;
714 gmd_common_val.calculate_process_loss(process_loss => l_process_rec,
715 Entity_type => 'RECIPE',
716 x_process_loss => l_process_loss,
717 x_msg_count => l_msg_count,
718 x_msg_data => l_msg_data,
719 x_return_status => X_status,
720 X_RECIPE_THEO_LOSS => l_recipe_theo_loss);
721
722 IF (X_status <> FND_API.G_RET_STS_SUCCESS) THEN
723 RAISE PROCESS_LOSS_ERR;
724 END IF;
725 X_contributing_qty := X_contributing_qty * (100 - l_process_loss);
726 EXCEPTION
727 WHEN UOM_CONVERSION_ERROR THEN
728 x_return_status := FND_API.G_RET_STS_ERROR;
729 gmd_fetch_validity_rules.uom_conversion_mesg(p_item_id => X_item_id,
730 p_from_um => X_item_um,
731 p_to_um => p_yield_um);
732 END get_contributing_qty;
733
734 /*======================================================================
735 -- PROCEDURE :
736 -- get_input_ratio
737 --
738 -- DESCRIPTION:
739 -- This PL/SQL procedure is responsible for determining
740 -- the actual ratio of product for the total input qty.
741 --
742 -- REQUIREMENTS
743 --
744 -- SYNOPSIS:
745 -- get_contributing_qty (X_formula_id, X_contributing_qty, X_yield_um,
746 -- X_formula_output, X_output_ratio, X_status);
747 --
748 --===================================================================== */
749 PROCEDURE get_input_ratio(p_formula_id IN NUMBER,
750 p_contributing_qty IN NUMBER,
751 p_yield_um IN VARCHAR2,
752 p_formula_output IN NUMBER,
753 X_output_ratio OUT NOCOPY NUMBER,
754 X_return_status OUT NOCOPY VARCHAR2) IS
755 CURSOR Cur_get_prods IS
756 SELECT inventory_item_id, qty, detail_uom, scale_type
757 FROM fm_matl_dtl
758 WHERE formula_id = p_formula_id
759 AND line_type = 1;
760 l_contributing_qty NUMBER := 0;
761 l_formula_output NUMBER := 0;
762 l_conv_qty NUMBER := 0;
763 l_fixed_prod NUMBER := 0;
764 X_item_id NUMBER;
765 X_item_um VARCHAR2(4);
766 UOM_CONVERSION_ERROR EXCEPTION;
767 BEGIN
768 FOR get_rec IN Cur_get_prods LOOP
769 IF (get_rec.scale_type = 0) THEN
770 IF (get_rec.detail_uom <> p_yield_um) THEN
771 l_conv_qty := INV_CONVERT.inv_um_convert(item_id => get_rec.inventory_item_id
772 ,precision => 5
773 ,from_quantity => get_rec.qty
774 ,from_unit => get_rec.detail_uom
775 ,to_unit => p_yield_um
776 ,from_name => NULL
777 ,to_name => NULL);
778 IF (l_conv_qty < 0) THEN
779 X_item_id := get_rec.inventory_item_id;
780 X_item_um := get_rec.detail_uom;
781 RAISE UOM_CONVERSION_ERROR;
782 END IF;
783 l_fixed_prod := l_fixed_prod + l_conv_qty;
784 ELSE
785 l_fixed_prod := l_fixed_prod + get_rec.qty;
786 END IF;
787 END IF;
788 END LOOP;
789 l_contributing_qty := p_contributing_qty - l_fixed_prod;
790 l_formula_output := P_formula_output - l_fixed_prod;
791 X_output_ratio := l_contributing_qty / l_formula_output;
792 EXCEPTION
793 WHEN UOM_CONVERSION_ERROR THEN
794 x_return_status := FND_API.G_RET_STS_ERROR;
795 gmd_fetch_validity_rules.uom_conversion_mesg(p_item_id => X_item_id,
796 p_from_um => X_item_um,
797 p_to_um => p_yield_um);
798 END get_input_ratio;
799
800 /*======================================================================
801 -- PROCEDURE :
802 -- uom_conversion_mesg
803 --
804 -- DESCRIPTION:
805 -- This PL/SQL procedure is responsible for showing
806 -- the the message about uom conversion errors.
807 --
808 -- REQUIREMENTS
809 --
810 -- SYNOPSIS:
811 -- uom_conversion_mesg (X_item_id, X_from_um, X_to_um);
812 --
813 --===================================================================== */
814 PROCEDURE uom_conversion_mesg(p_item_id IN NUMBER,
815 p_from_um IN VARCHAR2,
816 p_to_um IN VARCHAR2) IS
817 CURSOR Cur_get_item IS
818 SELECT concatenated_segments
819 FROM mtl_system_items_kfv
820 WHERE inventory_item_id = p_item_id;
821 X_item_no VARCHAR2(32);
822 BEGIN
823 OPEN Cur_get_item;
824 FETCH Cur_get_item INTO X_item_no;
825 CLOSE Cur_get_item;
826 FND_MESSAGE.SET_NAME('GMI', 'IC_API_UOM_CONVERSION_ERROR');
827 FND_MESSAGE.SET_TOKEN('ITEM_NO', X_item_no);
828 FND_MESSAGE.SET_TOKEN('FROM_UOM', p_from_um);
829 FND_MESSAGE.SET_TOKEN('TO_UOM', p_to_um);
830 FND_MSG_PUB.ADD;
831 END uom_conversion_mesg;
832
833 END gmd_fetch_validity_rules;