[Home] [Help]
PACKAGE BODY: APPS.GMD_VALIDITY_RULES
Source
1 PACKAGE BODY GMD_VALIDITY_RULES AS
2 /* $Header: GMDPRVRB.pls 120.16 2010/07/01 15:36:59 rnalla ship $ */
3
4 G_PKG_NAME VARCHAR2(32);
5 G_default_cost_mthd VARCHAR2(20);
6 G_cost_source_orgn_id NUMBER(15);
7 G_cost_source BINARY_INTEGER;
8
9 /*======================================================================
10 -- PROCEDURE :
11 -- get_validity_rules
12 --
13 -- DESCRIPTION:
14 -- This PL/SQL procedure is responsible for getting the
15 -- validity rules based on the input parameters.
16 --
17 -- REQUIREMENTS
18 --
19 -- SYNOPSIS:
20 -- get_validity_rules (1.0, X_init_msg_list, X_recipe_id, X_item_id,
21 -- X_orgn_code, X_product_qty, X_uom, X_recipe_use,
22 -- X_total_input, X_total_output, X_status,
23 -- X_return_status, X_msg_count, X_msg_data,
24 -- X_return_code, X_vr_table);
25 --
26 --
27 --===================================================================== */
28
29 PROCEDURE get_validity_rules(p_api_version IN NUMBER ,
30 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
31 p_recipe_no IN VARCHAR2 := NULL ,
32 p_recipe_version IN NUMBER := NULL ,
33 p_recipe_id IN NUMBER := NULL ,
34 p_total_input IN NUMBER := NULL ,
35 p_total_output IN NUMBER := NULL ,
36 p_formula_id IN NUMBER := NULL ,
37 p_item_id IN NUMBER := NULL ,
38 p_revision IN VARCHAR2 := NULL ,
39 p_item_no IN VARCHAR2 := NULL ,
40 p_product_qty IN NUMBER := NULL ,
41 p_uom IN VARCHAR2 := NULL ,
42 p_recipe_use IN VARCHAR2 := NULL ,
43 p_orgn_code IN VARCHAR2 := NULL ,
44 p_organization_id IN NUMBER := NULL ,
45 p_least_cost_validity IN VARCHAR2 := 'F' ,
46 p_start_date IN DATE := NULL ,
47 p_end_date IN DATE := NULL ,
48 p_status_type IN VARCHAR2 := NULL ,
49 p_validity_rule_id IN NUMBER := NULL ,
50 x_return_status OUT NOCOPY VARCHAR2 ,
51 x_msg_count OUT NOCOPY NUMBER ,
52 x_msg_data OUT NOCOPY VARCHAR2 ,
53 x_return_code OUT NOCOPY NUMBER ,
54 X_recipe_validity_out OUT NOCOPY recipe_validity_tbl) IS
55
56 -- local Variables
57 l_api_name VARCHAR2(30) := 'get_validity_rules';
58 l_api_version NUMBER := 1.0;
59 i NUMBER := 0;
60 l_uom VARCHAR2(4);
61
62 l_item_uom VARCHAR2(4);
63 l_line_um VARCHAR2(4);
64 l_quantity NUMBER;
65 l_item_qty NUMBER;
66 l_scale_type NUMBER;
67 l_msg_count NUMBER;
68 l_msg_data VARCHAR2(100);
69 l_return_code VARCHAR2(10);
70 l_yield_um VARCHAR2(4);
71 l_formula_id NUMBER;
72 l_formula_output NUMBER;
73 l_formula_input NUMBER;
74 l_total_output NUMBER;
75 l_total_input NUMBER;
76 l_output_ratio NUMBER;
77 l_ingred_prod_ratio NUMBER;
78 l_batchformula_ratio NUMBER;
79 l_contributing_qty NUMBER;
80
81 -- Bug 3818835
82 l_qty NUMBER;
83 l_form_qty NUMBER;
84 l_prod_id NUMBER;
85 l_prod_uom VARCHAR2(4);
86
87 l_uom_class VARCHAR2(25);
88
89 /* Cursor to get data based on recipe ID and input and output qty. */
90 CURSOR get_val IS
91 SELECT v.*
92 FROM gmd_recipe_validity_rules v, gmd_recipes r, gmd_status s
93 WHERE v.recipe_id = r.recipe_id
94 AND v.validity_rule_status = s.status_code
95 AND v.recipe_id = NVL(P_RECIPE_ID, v.recipe_id)
96 AND ( r.recipe_no = NVL(p_recipe_no, r.recipe_no) AND r.recipe_version = nvl(p_recipe_version, r.recipe_version) )
97 AND r.formula_id = NVL(p_formula_id, r.formula_id)
98 AND ( (p_status_type IS NULL AND s.status_type IN ( '700', '900'))
99 OR (p_status_type IS NOT NULL AND s.status_type = p_status_type) )
100 AND v.recipe_use IN (0,p_recipe_use)
101 AND ((v.organization_id = NVL(p_organization_id,v.organization_id))
102 OR (v.organization_id IS NULL) )
103 /* Bug 2690833 - Thomas Daniel */
104 /* Modified the following start and end date condtions to ensure that the date */
105 /* range validation is done properly */
106 AND ( (p_start_date IS NULL) or
107 ((start_date) <= (p_start_date) AND
108 (NVL(end_date, p_start_date)) >= (p_start_date)
109 )
110 )
111 AND ( (p_end_date IS NULL) OR
112 ((NVL(end_date,p_end_date)) >= (P_end_date) AND
113 (start_date) <= (p_end_date))
114 )
115 AND (p_validity_rule_id IS NULL OR
116 (p_validity_rule_id IS NOT NULL AND v.recipe_validity_rule_id = p_validity_rule_id))
117 AND v.delete_mark = 0
118 ORDER BY orgn_code,preference, recipe_use, s.status_type ;
119
120 /* Cursor to get data based on item. */
121 CURSOR get_val_item(l_quantity NUMBER) IS
122 SELECT v.*
123 FROM gmd_recipe_validity_rules v, gmd_recipes_b r, gmd_status_b s,
124 mtl_system_items_kfv I, fm_matl_dtl d
125 WHERE v.recipe_id = r.recipe_id
126 AND v.validity_rule_status = s.status_code
127 AND i.inventory_item_id = v.inventory_item_id
128 AND r.owner_organization_id = i.organization_id
129 AND (v.inventory_item_id = p_item_id)
130 -- bug 9747217 removed the or i.concatenated_segments = p_item_no condition from the above line.
131 AND (p_revision IS NULL OR (p_revision IS NOT NULL AND v.revision = p_revision))
132 AND (r.formula_id = NVL(p_formula_id, r.formula_id))
133 AND (inv_min_qty <= nvl(l_quantity,inv_min_qty) AND inv_max_qty >= nvl(l_quantity,inv_max_qty))
134 AND ((p_status_type is NULL) AND (s.status_type IN ( '700', '900'))
135 OR ( p_status_type is NOT NULL AND s.status_type = p_status_type))
136 AND v.recipe_use IN (0,p_recipe_use)
137 AND ((v.organization_id = NVL(p_organization_id,v.organization_id))
138 or (v.organization_id IS NULL) )
139 AND ( (p_start_date IS NULL) or
140 ((start_date) <= (p_start_date) AND
141 (NVL(end_date, p_start_date)) >= (p_start_date)
142 )
143 )
144 AND ( (p_end_date IS NULL) OR
145 ((NVL(end_date,p_end_date)) >= (P_end_date) AND
146 (start_date) <= (p_end_date))
147 )
148 AND (p_validity_rule_id IS NULL OR
149 (p_validity_rule_id IS NOT NULL AND v.recipe_validity_rule_id =
150 p_validity_rule_id))
151 AND v.delete_mark = 0
152 AND d.formula_id = r.formula_id
153 AND v.inventory_item_id = d.inventory_item_id
154 AND (p_revision IS NULL OR (p_revision IS NOT NULL AND d.revision = p_revision))
155 AND d.line_type = 1
156 ORDER BY orgn_code,preference, recipe_use, s.status_type ;
157
158 l_item_id NUMBER;
159
160 CURSOR get_item_id(p_item_no VARCHAR2) IS
161 SELECT inventory_item_id
162 FROM mtl_system_items_kfv
163 WHERE concatenated_segments = p_item_no;
164
165 -- NPD Conv.
166 CURSOR cur_item_uom(p_item_id NUMBER) IS
167 SELECT primary_uom_code
168 FROM mtl_system_items_b
169 WHERE inventory_item_id = p_item_id;
170
171 -- NPD Conv.
172 CURSOR Cur_std_um (p_uom_class VARCHAR2) IS
173 SELECT uom_code
174 FROM mtl_units_of_measure
175 WHERE uom_class = p_uom_class
176 AND base_uom_flag = 'Y';
177
178 -- NPD Conv.
179 CURSOR Cur_get_qty(V_item_id NUMBER) IS
180 SELECT qty, scale_type, detail_uom
181 FROM fm_matl_dtl
182 WHERE formula_id = l_formula_id
183 AND inventory_item_id = V_item_id
184 AND line_type = 1
185 ORDER BY line_no;
186
187
188 CURSOR Cur_get_recipe (V_recipe_no VARCHAR2, V_recipe_vers NUMBER) IS
189 SELECT recipe_id
190 FROM gmd_recipes_b
191 WHERE recipe_no = V_recipe_no
192 AND recipe_version = V_recipe_vers;
193
194 CURSOR Cur_get_orgn_code IS
195 SELECT organization_code
196 FROM mtl_parameters
197 WHERE organization_id = p_organization_id;
198
199 CURSOR Cur_get_VR IS
200 SELECT *
201 FROM GMD_VAL_RULE_GTMP;
202
203 CURSOR get_form_prod(l_formula_id NUMBER) IS
204 SELECT inventory_item_id, qty, detail_uom
205 FROM fm_matl_dtl
206 WHERE formula_id = l_formula_id
207 AND line_type = 1
208 AND line_no = 1;
209
210 CURSOR Cur_get_formula (V_recipe_id NUMBER) IS
211 SELECT formula_id
212 FROM gmd_recipes_b
213 WHERE recipe_id = V_recipe_id;
214
215
216 /* Exceptions */
217 NO_YIELD_TYPE_UM EXCEPTION;
218 GET_FORMULA_ERR EXCEPTION;
219 GET_TOTAL_QTY_ERR EXCEPTION;
220 GET_OUTPUT_RATIO_ERR EXCEPTION;
221 GET_INGREDPROD_RATIO_ERR EXCEPTION;
222 GET_BATCHFORMULA_RATIO_ERR EXCEPTION;
223 GET_CONTRIBUTING_QTY_ERR EXCEPTION;
224 GET_INPUT_RATIO_ERR EXCEPTION;
225 ITEM_UOM_CONV_ERR EXCEPTION;
226 UOM_CONVERSION_ERROR EXCEPTION;
227 ITEM_ORGN_MISSING EXCEPTION;
228 ITEM_NOT_FOUND_ERROR EXCEPTION;
229 GET_FORMULA_COST_ERR EXCEPTION;
230
231 l_recipe_id NUMBER;
232 l_orgn_code VARCHAR2(3);
233 l_total_cost NUMBER;
234 l_unit_cost NUMBER;
235 l_return_status VARCHAR2(10);
236 l_form_id NUMBER;
237
238 BEGIN
239 IF (NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
240 l_api_name, G_PKG_NAME)) THEN
241 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
242 END IF;
243 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
244 FND_MSG_PUB.initialize;
245 END IF;
246 X_return_status := FND_API.G_RET_STS_SUCCESS;
247
248 /* Bug No.6346013 - Start */
249
250 /* Delete from this table for any existing data */
251 DELETE FROM GMD_VAL_RULE_GTMP;
252
253 /* Bug No.6346013 - End */
254
255 -- NPD Convergence. Get FM_YIELD_TYPE profile value for the organization.
256 GMD_API_GRP.FETCH_PARM_VALUES(P_orgn_id => p_organization_id,
257 P_parm_name => 'FM_YIELD_TYPE',
258 P_parm_value => l_uom_class,
259 x_return_status => l_return_status);
260 /* Get yield type um */
261 OPEN Cur_std_um (l_uom_class);
262 FETCH Cur_std_um INTO l_yield_um;
263 IF (Cur_std_um%NOTFOUND) THEN
264 CLOSE Cur_std_um;
265 RAISE NO_YIELD_TYPE_UM;
266 END IF;
267 CLOSE Cur_std_um;
268
269 IF p_recipe_id IS NULL THEN
270 IF p_recipe_no IS NOT NULL AND
271 p_recipe_version IS NOT NULL THEN
272 OPEN Cur_get_recipe (p_recipe_no, p_recipe_version);
273 FETCH Cur_get_recipe INTO l_recipe_id;
274 CLOSE Cur_get_recipe;
275 END IF;
276 ELSE
277 l_recipe_id := p_recipe_id;
278 END IF;
279
280 /* Check for possible ways to get validity rules */
281 IF (l_recipe_id IS NOT NULL AND p_total_output IS NOT NULL OR
282 l_recipe_id IS NOT NULL AND p_total_input IS NOT NULL) THEN
283 /* Get the formula for this recipe */
284 OPEN Cur_get_formula (l_recipe_id);
285 FETCH Cur_get_formula INTO l_formula_id;
286 CLOSE Cur_get_formula;
287
288 -- S.Dulyk 1/8/02 added b/c calculate_total_qty wouldn't use p_uom
289 l_uom := p_uom;
290 gmd_common_val.calculate_total_qty(formula_id => l_formula_id,
291 x_product_qty => l_formula_output,
292 x_ingredient_qty => l_formula_input,
293 x_uom => l_uom,
294 x_return_status => l_return_status,
295 x_msg_count => l_msg_count,
296 x_msg_data => l_msg_data );
297 /*Bug 2962277 - Thomas Daniel */
298 /*The return status can be 'Q' from the above call for two reasons either */
299 /*the total input qty was not calculatable or the total output qty is not */
300 /*calculatable, we need to see the mode in which this procedure was invoked */
301 /*to determine if an error should be raised */
302 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) AND
303 (l_return_status <> 'Q') THEN
304 RAISE GET_TOTAL_QTY_ERR;
305 ELSIF l_return_status = 'Q' THEN
306 IF (p_total_output IS NOT NULL) AND
307 (l_formula_output IS NULL) THEN
308 /*This implies that the system cannot calculate the total output qty and */
309 /*the validity rules are being fetched based on total ouput then this should */
310 /*be raised as an error */
311 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERR_CALC_OUTPUT');
312 FND_MESSAGE.SET_TOKEN('UOM', l_uom);
313 FND_MSG_PUB.add;
314 RAISE GET_TOTAL_QTY_ERR;
315 ELSIF (p_total_input IS NOT NULL) AND
316 (l_formula_input IS NULL) THEN
317 /*This implies that the system cannot calculate the total input qty and */
318 /*the validity rules are being fetched based on total input then this should */
319 /*be raised as an error */
320 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERR_CALC_INPUT');
321 FND_MESSAGE.SET_TOKEN('UOM', l_uom);
322 FND_MSG_PUB.add;
323 RAISE GET_TOTAL_QTY_ERR;
324 END IF;
325 END IF;
326
327
328 IF (p_total_output IS NOT NULL) THEN
329
330 /* Try to get validity rules based on recipe ID and total output qty */
331 /* Get the ratio of the batch output qty to the ratio of the formula ouput qty */
332 gmd_validity_rules.get_output_ratio(p_formula_id => l_formula_id,
333 p_batch_output => p_total_output,
334 p_yield_um => l_uom,
335 p_formula_output => l_formula_output,
336 x_return_status => l_return_status,
337 X_output_ratio => l_output_ratio);
338 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
339 RAISE GET_OUTPUT_RATIO_ERR;
340 END IF;
341 ELSIF (p_total_input IS NOT NULL) THEN
342
343 /* Get the product to ingredient ratio for the formula */
344 gmd_validity_rules.get_ingredprod_ratio(p_formula_id => l_formula_id,
345 p_yield_um => l_uom,
346 x_return_status => l_return_status,
347 X_ingred_prod_ratio => l_ingred_prod_ratio);
348 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
349 RAISE GET_INGREDPROD_RATIO_ERR;
350 END IF;
351 /* Get the ratio of the batch input to the formula input */
352 gmd_validity_rules.get_batchformula_ratio(p_formula_id => l_formula_id,
353 p_batch_input => p_total_input,
354 p_yield_um => l_uom,
355 p_formula_input => l_formula_input,
356 x_return_status => l_return_status,
357 X_batchformula_ratio => l_batchformula_ratio);
358 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
359 RAISE GET_BATCHFORMULA_RATIO_ERR;
360 END IF;
361
362 /* Get the contributing qty of the formula */
363 gmd_validity_rules.get_contributing_qty(p_formula_id => l_formula_id,
364 p_recipe_id => l_recipe_id,
365 p_batchformula_ratio => l_batchformula_ratio,
366 p_yield_um => l_uom,
367 x_return_status => l_return_status,
368 X_contributing_qty => l_contributing_qty);
369 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
370 RAISE GET_CONTRIBUTING_QTY_ERR;
371 END IF;
372 /* Calculate actual contributing qty of formula */
373 l_contributing_qty := l_contributing_qty * l_ingred_prod_ratio;
374
375 /* Get the ratio of the product based on contributing qty */
376 gmd_validity_rules.get_input_ratio(p_formula_id => l_formula_id,
377 p_contributing_qty => l_contributing_qty,
378 p_yield_um => l_uom,
379 p_formula_output => l_formula_input,
380 x_return_status => l_return_status,
381 X_output_ratio => l_output_ratio);
382 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
383 RAISE GET_INPUT_RATIO_ERR;
384 END IF;
385 END IF;
386
387 /* Get all the possible validity rules and check if it can be used for this input/output qty */
388
389 FOR get_rec IN get_val LOOP
390 BEGIN
391 -- NPD Conv.
392 IF (p_orgn_code IS NOT NULL OR (p_orgn_code IS NULL AND p_organization_id IS NOT NULL)) THEN
393
394 IF p_orgn_code IS NULL THEN
395 OPEN Cur_get_orgn_code;
396 FETCH Cur_get_orgn_code INTO l_orgn_code;
397 CLOSE Cur_get_orgn_code;
398 ELSE
399 l_orgn_code := p_orgn_code;
400 END IF;
401 GMD_API_GRP.check_item_exists (p_formula_id => l_formula_id
402 ,p_organization_id => p_organization_id
403 ,p_orgn_code => l_orgn_code
404 ,x_return_status => l_return_status
405 ,p_Production_check => TRUE);
406 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
407 RAISE ITEM_ORGN_MISSING;
408 END IF;
409 END IF;
410 -- End NPD Conv.
411
412 OPEN Cur_get_qty(get_rec.inventory_item_id);
413 FETCH Cur_get_qty INTO l_item_qty, l_scale_type, l_line_um;
414 CLOSE Cur_get_qty;
415 IF (l_scale_type = 1) THEN
416 l_item_qty := l_item_qty * l_output_ratio;
417 IF (l_line_um <> get_rec.detail_uom) THEN
418
419 -- NPD Conv. Changed the call to INV_CONVERT.inv_um_convert from gmicuom.uom_conversion
420
421 l_item_qty := INV_CONVERT.inv_um_convert(item_id => get_rec.inventory_item_id
422 ,precision => 5
423 ,from_quantity => l_item_qty
424 ,from_unit => l_line_um
425 ,to_unit => get_rec.detail_uom
426 ,from_name => NULL
427 ,to_name => NULL);
428 IF l_item_qty < 0 THEN
429 RAISE UOM_CONVERSION_ERROR;
430 END IF;
431 /* gmicuom.icuomcv(get_rec.item_id, 0, l_item_qty, l_line_um, get_rec.item_um, l_item_qty); */
432 END IF;
433 IF (l_item_qty >= get_rec.min_qty AND l_item_qty <= get_rec.max_qty) THEN
434 IF p_least_cost_validity = 'T' THEN
435 GMD_VALIDITY_RULES.get_formula_cost (p_formula_id => l_formula_id
436 ,p_requested_qty => l_item_qty
437 ,p_requested_uom => get_rec.detail_uom
438 ,p_product_id => get_rec.inventory_item_id
439 ,p_organization_id => p_organization_id
440 ,X_unit_cost => l_unit_cost
441 ,X_total_cost => l_total_cost
442 ,X_return_status => l_return_status);
443 IF l_return_status <> FND_API.g_ret_sts_success THEN
444 RAISE GET_FORMULA_COST_ERR;
445 END IF;
446 END IF; /* IF p_least_cost_validity = 'T' */
447 GMD_VALIDITY_RULES.insert_val_temp_tbl(p_val_rec => get_rec
448 ,p_unit_cost => l_unit_cost
449 ,p_total_cost => l_total_cost);
450 END IF; /* IF (l_item_qty >= get_rec.min_qty AND l_item_qty <= get_rec.max_qty) */
451 END IF; /* IF (l_scale_type = 1) */
452 EXCEPTION
453 WHEN ITEM_ORGN_MISSING THEN
454 x_return_status := FND_API.G_RET_STS_ERROR;
455 END;
456 END LOOP;
457 ELSIF (p_item_id IS NOT NULL or p_item_no IS NOT NULL) THEN
458
459 /* Try to get validity rules based on Item */
460 OPEN cur_item_uom(p_item_id);
461 FETCH cur_item_uom INTO l_item_uom;
462 CLOSE cur_item_uom;
463
464 IF (p_uom <> l_item_uom) THEN
465 -- NPD Conv. Changed the call to INV_CONVERT.inv_um_convert from gmicuom.uom_conversion
466 l_quantity := ROUND(gmicuom.uom_conversion(p_item_id,0,p_product_qty, p_uom, l_item_uom, 0),9);
467
468 l_quantity := INV_CONVERT.inv_um_convert(item_id => p_item_id
469 ,precision => 5
470 ,from_quantity => p_product_qty
471 ,from_unit => p_uom
472 ,to_unit => l_item_uom
473 ,from_name => NULL
474 ,to_name => NULL);
475
476 IF (l_quantity < 0) THEN
477 RAISE UOM_CONVERSION_ERROR;
478 END IF;
479 ELSE
480 l_quantity := p_product_qty;
481 END IF;
482
483 /* Get item id if it is not passed in */
484 IF (p_item_id IS NOT NULL) THEN
485 l_item_id := p_item_id;
486 ELSIF (p_item_no IS NOT NULL) THEN
487 OPEN get_item_id(p_item_no);
488 FETCH get_item_id INTO l_item_id;
489 IF get_item_id%NOTFOUND THEN
490 CLOSE get_item_id;
491 RAISE ITEM_NOT_FOUND_ERROR;
492 END IF;
493 CLOSE get_item_id;
494 ELSE
495 RAISE ITEM_NOT_FOUND_ERROR;
496 END IF;
497
498 FOR get_rec IN get_val_item(l_quantity) LOOP
499 BEGIN
500 x_return_status := FND_API.G_RET_STS_SUCCESS;
501
502 /* Get the formula for this recipe */
503 OPEN Cur_get_formula (get_rec.recipe_id);
504 FETCH Cur_get_formula INTO l_formula_id;
505 CLOSE Cur_get_formula;
506
507 -- NPD Conv.
508 IF (p_orgn_code IS NOT NULL OR (p_orgn_code IS NULL AND p_organization_id IS NOT NULL)) THEN
509 IF p_orgn_code IS NULL THEN
510 OPEN Cur_get_orgn_code;
511 FETCH Cur_get_orgn_code INTO l_orgn_code;
512 CLOSE Cur_get_orgn_code;
513 ELSE
514 l_orgn_code := p_orgn_code;
515 END IF;
516 GMD_API_GRP.check_item_exists (p_formula_id => l_formula_id
517 ,p_organization_id => p_organization_id
518 ,p_orgn_code => p_orgn_code
519 ,x_return_status => l_return_status
520 ,p_Production_check => TRUE);
521 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
522 RAISE ITEM_ORGN_MISSING;
523 END IF;
524 END IF;
525 -- End NPD Conv.
526
527 IF p_least_cost_validity = 'T' THEN
528 GMD_VALIDITY_RULES.get_formula_cost (p_formula_id => l_formula_id
529 ,p_requested_qty => l_quantity
530 ,p_requested_uom => l_item_uom
531 ,p_product_id => get_rec.inventory_item_id
532 ,p_organization_id => p_organization_id
533 ,X_unit_cost => l_unit_cost
534 ,X_total_cost => l_total_cost
535 ,X_return_status => l_return_status);
536 IF l_return_status <> FND_API.g_ret_sts_success THEN
537 RAISE GET_FORMULA_COST_ERR;
538 END IF;
539 END IF; /* IF p_least_cost_validity = 'T' */
540
541 GMD_VALIDITY_RULES.insert_val_temp_tbl(p_val_rec => get_rec
542 ,p_unit_cost => l_unit_cost
543 ,p_total_cost => l_total_cost);
544 EXCEPTION
545 WHEN ITEM_ORGN_MISSING THEN
546 x_return_status := FND_API.G_RET_STS_ERROR;
547 END;
548 END LOOP;
549
550 ELSE
551 /* Try to get validity rules based on recipe ID */
552 -- Changed IF p_recipe_id NOT NULL to IF l_recipe_id IS NOT NULL as it fails when recipe no and vers
553 -- are passed instead of id
554 -- Bug 3818835 - Start
555 IF l_recipe_id IS NOT NULL THEN
556
557 -- Get the formula attached with the recipe
558 OPEN Cur_get_formula (l_recipe_id);
559 FETCH Cur_get_formula INTO l_formula_id;
560 CLOSE Cur_get_formula;
561
562 -- Get formula product quantity
563 OPEN get_form_prod(l_formula_id);
564 FETCH get_form_prod INTO l_prod_id,l_form_qty,l_prod_uom;
565 CLOSE get_form_prod;
566
567 /* Bug No.8643350 - START*/
568 OPEN cur_item_uom(l_prod_id);
569 FETCH cur_item_uom INTO l_item_uom;
570 CLOSE cur_item_uom;
571 /* Bug No.8643350 - END */
572
573 IF p_product_qty IS NOT NULL THEN -- Add Check to see if Prod Qty. is passed as NULL
574 -- check uom conversion here
575 -- NPD Conv. Changed the call to INV_CONVERT.inv_um_convert from gmicuom.uom_conversion
576 l_quantity := INV_CONVERT.inv_um_convert(item_id => p_item_id
577 ,precision => 5
578 ,from_quantity => p_product_qty
579 ,from_unit => p_uom
580 ,to_unit => l_item_uom
581 ,from_name => NULL
582 ,to_name => NULL);
583 IF (l_quantity < 0) THEN
584 RAISE UOM_CONVERSION_ERROR;
585 END IF;
586 ELSE
587 -- NPD Conv. Commented out below logic as ic_plnt_inv table is obsolete after conv.
588 /* Bug No.8643350 - Start */
589 IF (l_prod_uom <> l_item_uom) THEN
590 l_quantity := INV_CONVERT.inv_um_convert(item_id => l_prod_id
591 ,precision => 5
592 ,from_quantity => l_form_qty
593 ,from_unit => l_prod_uom
594 ,to_unit => l_item_uom
595 ,from_name => NULL
596 ,to_name => NULL);
597 IF (l_quantity < 0) THEN
598 RAISE UOM_CONVERSION_ERROR;
599 END IF;
600 ELSE
601 l_quantity := l_form_qty;
602 END IF;
603 /* Bug No.8643350 - END */
604 END IF;
605 END IF; /* IF l_recipe_id IS NOT NULL THEN */
606 -- Bug 3818835 - End
607
608 FOR get_rec IN get_val LOOP
609 BEGIN
610 x_return_status := FND_API.G_RET_STS_SUCCESS;
611
612 -- NPD Conv.
613 IF (p_orgn_code IS NOT NULL OR (p_orgn_code IS NULL AND p_organization_id IS NOT NULL)) THEN
614
615 IF p_orgn_code IS NULL THEN
616 OPEN Cur_get_orgn_code;
617 FETCH Cur_get_orgn_code INTO l_orgn_code;
618 CLOSE Cur_get_orgn_code;
619 ELSE
620 l_orgn_code := p_orgn_code;
621 END IF;
622 GMD_API_GRP.check_item_exists (p_formula_id => l_formula_id
623 ,p_organization_id => NULL
624 ,p_orgn_code => l_orgn_code
625 ,x_return_status => l_return_status
626 ,p_Production_check => TRUE);
627
628 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
629 RAISE ITEM_ORGN_MISSING;
630 END IF;
631 END IF;
632 -- End NPD Conv.
633
634 -- Bug 3818835
635 -- Select validity rule only if qty. is greater than min qty and lesser than max qty of validity rule.
636 IF (l_quantity >= get_rec.inv_min_qty AND l_quantity <= get_rec.inv_max_qty) THEN -- Bug #5211935 inv_min_qty , inv_max_qty instead of min_qty, max_qty
637
638 IF p_least_cost_validity = 'T' THEN
639 GMD_VALIDITY_RULES.get_formula_cost (p_formula_id => l_formula_id
640 ,p_requested_qty => l_quantity
641 ,p_requested_uom => get_rec.detail_uom
642 ,p_product_id => get_rec.inventory_item_id
643 ,p_organization_id => p_organization_id
644 ,X_unit_cost => l_unit_cost
645 ,X_total_cost => l_total_cost
646 ,X_return_status => l_return_status);
647 IF l_return_status <> FND_API.g_ret_sts_success THEN
648 RAISE GET_FORMULA_COST_ERR;
649 END IF;
650 END IF; /* IF p_least_cost_validity = 'T' */
651
652 GMD_VALIDITY_RULES.insert_val_temp_tbl(p_val_rec => get_rec
653 ,p_unit_cost => l_unit_cost
654 ,p_total_cost => l_total_cost);
655
656 END IF; /* IF (l_quantity >= get_rec.min_qty AND l_quantity <= get_rec.max_qty) */
657 EXCEPTION
658 WHEN ITEM_ORGN_MISSING THEN
659 x_return_status := FND_API.G_RET_STS_ERROR;
660 END;
661 END LOOP;
662 END IF;
663
664 i := 0;
665 FOR l_rec IN Cur_get_VR LOOP
666 i := i + 1;
667 x_recipe_validity_out(i).recipe_validity_rule_id := l_rec.recipe_validity_rule_id ;
668 x_recipe_validity_out(i).recipe_id := l_rec.recipe_id ;
669 x_recipe_validity_out(i).orgn_code := l_rec.orgn_code ;
670 x_recipe_validity_out(i).recipe_use := l_rec.recipe_use ;
671 x_recipe_validity_out(i).preference := l_rec.preference ;
672 x_recipe_validity_out(i).start_date := l_rec.start_date ;
673 x_recipe_validity_out(i).end_date := l_rec.end_date ;
674 x_recipe_validity_out(i).min_qty := l_rec.min_qty ;
675 x_recipe_validity_out(i).max_qty := l_rec.max_qty ;
676 x_recipe_validity_out(i).std_qty := l_rec.std_qty ;
677 x_recipe_validity_out(i).inv_min_qty := l_rec.inv_min_qty ;
678 x_recipe_validity_out(i).inv_max_qty := l_rec.inv_max_qty ;
679 x_recipe_validity_out(i).text_code := l_rec.text_code ;
680 x_recipe_validity_out(i).attribute_category := l_rec.attribute_category ;
681 x_recipe_validity_out(i).attribute1 := l_rec.attribute1 ;
682 x_recipe_validity_out(i).attribute2 := l_rec.attribute2 ;
683 x_recipe_validity_out(i).attribute3 := l_rec.attribute3 ;
684 x_recipe_validity_out(i).attribute4 := l_rec.attribute4 ;
685 x_recipe_validity_out(i).attribute5 := l_rec.attribute5 ;
686 x_recipe_validity_out(i).attribute6 := l_rec.attribute6 ;
687 x_recipe_validity_out(i).attribute7 := l_rec.attribute7 ;
688 x_recipe_validity_out(i).attribute8 := l_rec.attribute8 ;
689 x_recipe_validity_out(i).attribute9 := l_rec.attribute9 ;
690 x_recipe_validity_out(i).attribute10 := l_rec.attribute10 ;
691 x_recipe_validity_out(i).attribute11 := l_rec.attribute11;
692 x_recipe_validity_out(i).attribute12 := l_rec.attribute12 ;
693 x_recipe_validity_out(i).attribute13 := l_rec.attribute13 ;
694 x_recipe_validity_out(i).attribute14 := l_rec.attribute14 ;
695 x_recipe_validity_out(i).attribute15 := l_rec.attribute15 ;
696 x_recipe_validity_out(i).attribute16 := l_rec.attribute16 ;
697 x_recipe_validity_out(i).attribute17 := l_rec.attribute17 ;
698 x_recipe_validity_out(i).attribute18 := l_rec.attribute18 ;
699 x_recipe_validity_out(i).attribute19 := l_rec.attribute19 ;
700 x_recipe_validity_out(i).attribute20 := l_rec.attribute20 ;
701 x_recipe_validity_out(i).attribute21 := l_rec.attribute21 ;
702 x_recipe_validity_out(i).attribute22 := l_rec.attribute22 ;
703 x_recipe_validity_out(i).attribute23 := l_rec.attribute23 ;
704 x_recipe_validity_out(i).attribute24 := l_rec.attribute24 ;
705 x_recipe_validity_out(i).attribute25 := l_rec.attribute25 ;
706 x_recipe_validity_out(i).attribute26 := l_rec.attribute26 ;
707 x_recipe_validity_out(i).attribute27 := l_rec.attribute27 ;
708 x_recipe_validity_out(i).attribute28 := l_rec.attribute28 ;
709 x_recipe_validity_out(i).attribute29 := l_rec.attribute29 ;
710 x_recipe_validity_out(i).attribute30 := l_rec.attribute30 ;
711 x_recipe_validity_out(i).created_by := l_rec.created_by ;
712 x_recipe_validity_out(i).creation_date := l_rec.creation_date ;
713 x_recipe_validity_out(i).last_updated_by := l_rec.last_updated_by ;
714 x_recipe_validity_out(i).last_update_date := l_rec.last_update_date ;
715 x_recipe_validity_out(i).last_update_login := l_rec.last_update_login ;
716 x_recipe_validity_out(i).validity_rule_status := l_rec.validity_rule_status ;
717 x_recipe_validity_out(i).planned_process_loss := l_rec.planned_process_loss ;
718 x_recipe_validity_out(i).organization_id := l_rec.organization_id ;
719 x_recipe_validity_out(i).inventory_item_id := l_rec.inventory_item_id ;
720 x_recipe_validity_out(i).revision := l_rec.revision ;
721 x_recipe_validity_out(i).detail_uom := l_rec.detail_uom ;
722 x_recipe_validity_out(i).unit_cost := l_rec.unit_cost ;
723 x_recipe_validity_out(i).total_cost := l_rec.total_cost ;
724 END LOOP;
725
726 IF i > 0 THEN
727 X_return_status := Fnd_api.G_ret_sts_success;
728 END IF;
729
730 -- standard call to get msge cnt, and if cnt is 1, get mesg info
731 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
732 EXCEPTION
733 WHEN NO_YIELD_TYPE_UM THEN
734 x_return_status := FND_API.G_RET_STS_ERROR;
735 FND_MESSAGE.SET_NAME('GMD', 'FM_SCALE_BAD_YIELD_TYPE');
736 FND_MSG_PUB.ADD;
737 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
738 P_data => x_msg_data);
739 WHEN GET_FORMULA_COST_ERR THEN
740 x_return_status := FND_API.G_RET_STS_ERROR;
741 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
742 P_data => x_msg_data);
743 WHEN GET_FORMULA_ERR THEN
744 x_return_status := FND_API.G_RET_STS_ERROR;
745 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
746 P_data => x_msg_data);
747 WHEN GET_TOTAL_QTY_ERR OR GET_OUTPUT_RATIO_ERR
748 OR GET_INGREDPROD_RATIO_ERR OR GET_BATCHFORMULA_RATIO_ERR
749 OR GET_CONTRIBUTING_QTY_ERR OR GET_INPUT_RATIO_ERR THEN
750 x_return_status := FND_API.G_RET_STS_ERROR;
751 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
752 P_data => x_msg_data);
753 WHEN UOM_CONVERSION_ERROR THEN
754 x_return_status := FND_API.G_RET_STS_ERROR;
755 gmd_validity_rules.uom_conversion_mesg(p_item_id => p_item_id,
756 p_from_um => p_uom,
757 p_to_um => l_item_uom);
758 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
759 P_data => x_msg_data);
760
761 WHEN FND_API.G_EXC_ERROR THEN
762 X_return_code := SQLCODE;
763 x_return_status := FND_API.G_RET_STS_ERROR;
764 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
765
766 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
767 X_return_code := SQLCODE;
768 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
769 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
770
771 WHEN OTHERS THEN
772 X_return_code := SQLCODE;
773 x_return_status := FND_API.G_RET_STS_ERROR;
774 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
775 END get_validity_rules;
776
777 /*======================================================================
778 -- PROCEDURE :
779 -- get_output_ratio
780 --
781 -- DESCRIPTION:
782 -- This PL/SQL procedure is responsible for determining
783 -- the output ratio which is the ratio of the batch output
784 -- to the formula output when a total output qty is used as
785 -- the criteria for a validity rule.
786 --
787 -- REQUIREMENTS
788 --
789 -- SYNOPSIS:
790 -- get_output_ratio (X_formula_id, X_batch_output, X_yield_um,
791 -- X_formula_output, X_return_status, X_output_ratio);
792 --
793 --===================================================================== */
794 PROCEDURE get_output_ratio(p_formula_id IN NUMBER,
795 p_batch_output IN NUMBER,
796 p_yield_um IN VARCHAR2,
797 p_formula_output IN NUMBER,
798 x_return_status OUT NOCOPY VARCHAR2,
799 X_output_ratio OUT NOCOPY NUMBER) IS
800 CURSOR Cur_get_prods IS
801 SELECT inventory_item_id, qty, detail_uom, scale_type
802 FROM fm_matl_dtl
803 WHERE formula_id = p_formula_id
804 AND line_type IN (1,2);
805
806 l_batch_output NUMBER := 0;
807 l_formula_output NUMBER := 0;
808 l_conv_qty NUMBER := 0;
809 l_total_fixed_qty NUMBER := 0;
810 X_item_id NUMBER;
811 X_detail_uom VARCHAR2(4);
812 UOM_CONVERSION_ERROR EXCEPTION;
813
814 BEGIN
815 x_return_status := FND_API.G_RET_STS_SUCCESS;
816 FOR get_rec IN Cur_get_prods
817 LOOP
818 IF (get_rec.scale_type = 0) THEN
819 IF (get_rec.detail_uom <> p_yield_um) THEN
820
821 -- NPD Conv. Changed the call to INV_CONVERT.inv_um_convert from gmicuom.uom_conversion
822 l_conv_qty := INV_CONVERT.inv_um_convert(item_id => get_rec.inventory_item_id
823 ,precision => 5
824 ,from_quantity => get_rec.qty
825 ,from_unit => get_rec.detail_uom
826 ,to_unit => p_yield_um
827 ,from_name => NULL
828 ,to_name => NULL);
829 IF (l_conv_qty < 0) THEN
830 X_item_id := get_rec.inventory_item_id;
831 X_detail_uom := get_rec.detail_uom;
832 RAISE UOM_CONVERSION_ERROR;
833 END IF;
834 l_total_fixed_qty := l_total_fixed_qty + l_conv_qty;
835 ELSE
836 l_total_fixed_qty := l_total_fixed_qty + get_rec.qty;
837 END IF;
838 END IF;
839 END LOOP;
840
841 l_batch_output := p_batch_output - l_total_fixed_qty;
842 l_formula_output := p_formula_output - l_total_fixed_qty;
843 X_output_ratio := l_batch_output/l_formula_output;
844
845 EXCEPTION
846 WHEN UOM_CONVERSION_ERROR THEN
847 x_return_status := FND_API.G_RET_STS_ERROR;
848 gmd_validity_rules.uom_conversion_mesg(p_item_id => X_item_id,
849 p_from_um => X_detail_uom,
850 p_to_um => p_yield_um);
851 END get_output_ratio;
852
853 /*======================================================================
854 -- PROCEDURE :
855 -- get_ingredprod_ratio
856 --
857 -- DESCRIPTION:
858 -- This PL/SQL procedure is responsible for determining
859 -- the ratio of the products to ingredients while trying
860 -- to determine validity rules based on total input qty.
861 --
862 -- REQUIREMENTS
863 --
864 -- SYNOPSIS:
865 -- get_ingredprod_ratio (X_formula_id, X_yield_um,
866 -- X_ingred_prod_ratio, X_status);
867 --
868 --===================================================================== */
869 PROCEDURE get_ingredprod_ratio(p_formula_id IN NUMBER,
870 p_yield_um IN VARCHAR2,
871 X_ingred_prod_ratio OUT NOCOPY NUMBER,
872 x_return_status OUT NOCOPY VARCHAR2) IS
873 -- NPD Conv.
874 CURSOR Cur_get_details(V_line_type NUMBER) IS
875 SELECT inventory_item_id, qty, detail_uom, scale_type, contribute_yield_ind
876 FROM fm_matl_dtl
877 WHERE formula_id = p_formula_id
878 AND line_type = V_line_type;
879
880 l_sum_prods NUMBER := 0;
881 l_sum_ingreds NUMBER := 0;
882 l_conv_qty NUMBER := 0;
883 X_item_id NUMBER;
884 X_detail_uom VARCHAR2(4);
885 UOM_CONVERSION_ERROR EXCEPTION;
886 BEGIN
887 x_return_status := FND_API.G_RET_STS_SUCCESS;
888 --Get sum of products in yield UM.
889 FOR get_rec IN Cur_get_details(1)
890 LOOP
891 IF (get_rec.detail_uom <> p_yield_um) THEN
892 -- NPD Conv. Changed the call to INV_CONVERT.inv_um_convert from gmicuom.uom_conversion
893 l_conv_qty := INV_CONVERT.inv_um_convert(item_id => get_rec.inventory_item_id
894 ,precision => 5
895 ,from_quantity => get_rec.qty
896 ,from_unit => get_rec.detail_uom
897 ,to_unit => p_yield_um
898 ,from_name => NULL
899 ,to_name => NULL);
900
901 IF (l_conv_qty < 0) THEN
902 X_item_id := get_rec.inventory_item_id;
903 X_detail_uom := get_rec.detail_uom;
904 RAISE UOM_CONVERSION_ERROR;
905 END IF;
906 l_sum_prods := l_sum_prods + l_conv_qty;
907 ELSE
908 l_sum_prods := l_sum_prods + get_rec.qty;
909 END IF;
910 END LOOP;
911 --Get sum of ingredients in yield UM contributing to yield.
912 FOR get_rec IN Cur_get_details(-1)
913 LOOP
914 IF (get_rec.contribute_yield_ind = 'Y') THEN
915 IF (get_rec.detail_uom <> p_yield_um) THEN
916 -- NPD Conv. Changed the call to INV_CONVERT.inv_um_convert from gmicuom.uom_conversion
917 l_conv_qty := INV_CONVERT.inv_um_convert(item_id => get_rec.inventory_item_id
918 ,precision => 5
919 ,from_quantity => get_rec.qty
920 ,from_unit => get_rec.detail_uom
921 ,to_unit => p_yield_um
922 ,from_name => NULL
923 ,to_name => NULL);
924 IF (l_conv_qty < 0) THEN
925 X_item_id := get_rec.inventory_item_id;
926 X_detail_uom := get_rec.detail_uom;
927 RAISE UOM_CONVERSION_ERROR;
928 END IF;
929 l_sum_ingreds := l_sum_ingreds + l_conv_qty;
930 ELSE
931 l_sum_ingreds := l_sum_ingreds + get_rec.qty;
932 END IF;
933 END IF;
934 END LOOP;
935
936 --Get ratio and return.
937 X_ingred_prod_ratio := l_sum_prods/l_sum_ingreds;
938 EXCEPTION
939 WHEN UOM_CONVERSION_ERROR THEN
940 x_return_status := FND_API.G_RET_STS_ERROR;
941 gmd_validity_rules.uom_conversion_mesg(p_item_id => X_item_id,
942 p_from_um => X_detail_uom,
943 p_to_um => p_yield_um);
944 END get_ingredprod_ratio;
945
946 /*======================================================================
947 -- PROCEDURE :
948 -- get_batchformula_ratio
949 --
950 -- DESCRIPTION:
951 -- This PL/SQL procedure is responsible for determining
952 -- the ratio of the batch input qty to the formula input qty
953 -- while determining validity rules based on total input qty.
954 --
955 -- REQUIREMENTS
956 --
957 -- SYNOPSIS:
958 -- get_batchformula_ratio (X_formula_id, X_batch_input, X_yield_um,
959 -- X_formula_input, X_batchformula_ratio,
960 -- X_status);
961 --
962 --===================================================================== */
963 PROCEDURE get_batchformula_ratio(p_formula_id IN NUMBER,
964 p_batch_input IN NUMBER,
965 p_yield_um IN VARCHAR2,
966 p_formula_input IN NUMBER,
967 X_batchformula_ratio OUT NOCOPY NUMBER,
968 X_return_status OUT NOCOPY VARCHAR2) IS
969 CURSOR Cur_get_ingreds IS
970 -- NPD Conv.
971 SELECT inventory_item_id, qty, detail_uom, scale_type
972 FROM fm_matl_dtl
973 WHERE formula_id = p_formula_id
974 AND line_type = -1;
975
976 CURSOR Cur_get_total_input IS
977 SELECT total_input_qty, yield_uom
978 FROM fm_form_mst
979 WHERE formula_id = p_formula_id;
980 l_formula_input NUMBER := 0;
981 l_fixed_ingred NUMBER := 0;
982 l_batch_input NUMBER := 0;
983 l_conv_qty NUMBER := 0;
984 X_item_id NUMBER;
985 X_detail_uom VARCHAR2(4);
986 UOM_CONVERSION_ERROR EXCEPTION;
987 BEGIN
988 x_return_status := FND_API.G_RET_STS_SUCCESS;
989 FOR get_rec IN Cur_get_ingreds LOOP
990 IF (get_rec.scale_type = 0) THEN
991 IF (get_rec.detail_uom <> p_yield_um) THEN
992
993 -- NPD Conv. Changed the call to INV_CONVERT.inv_um_convert from gmicuom.uom_conversion
994 l_conv_qty := INV_CONVERT.inv_um_convert(item_id => get_rec.inventory_item_id
995 ,precision => 5
996 ,from_quantity => get_rec.qty
997 ,from_unit => get_rec.detail_uom
998 ,to_unit => p_yield_um
999 ,from_name => NULL
1000 ,to_name => NULL);
1001 IF (l_conv_qty < 0) THEN
1002 X_item_id := get_rec.inventory_item_id;
1003 X_detail_uom := get_rec.detail_uom;
1004 RAISE UOM_CONVERSION_ERROR;
1005 END IF;
1006 l_fixed_ingred := l_fixed_ingred + l_conv_qty;
1007 ELSE
1008 l_fixed_ingred := l_fixed_ingred + get_rec.qty;
1009 END IF;
1010 END IF;
1011 END LOOP;
1012 l_batch_input := p_batch_input - l_fixed_ingred;
1013 l_formula_input := p_formula_input - l_fixed_ingred;
1014 X_batchformula_ratio := l_batch_input / l_formula_input;
1015 EXCEPTION
1016 WHEN UOM_CONVERSION_ERROR THEN
1017 x_return_status := FND_API.G_RET_STS_ERROR;
1018 gmd_validity_rules.uom_conversion_mesg(p_item_id => X_item_id,
1019 p_from_um => X_detail_uom,
1020 p_to_um => p_yield_um);
1021 END get_batchformula_ratio;
1022
1023 /*======================================================================
1024 -- PROCEDURE :
1025 -- get_contibuting_qty
1026 --
1027 -- DESCRIPTION:
1028 -- This PL/SQL procedure is responsible for determining
1029 -- the actual contributing qty of the formula.
1030 --
1031 -- REQUIREMENTS
1032 --
1033 -- SYNOPSIS:
1034 -- get_contributing_qty (X_formula_id, X_recipe_id,
1035 -- X_formula_batch_ratio, X_yield_um,
1036 -- X_formula_input, X_ratio, X_status);
1037 --
1038 --===================================================================== */
1039 PROCEDURE get_contributing_qty(p_formula_id IN NUMBER,
1040 p_recipe_id IN NUMBER,
1041 p_batchformula_ratio IN NUMBER,
1042 p_yield_um IN VARCHAR2,
1043 X_contributing_qty OUT NOCOPY NUMBER,
1044 X_return_status OUT NOCOPY VARCHAR2) IS
1045 -- NPD Conv.
1046 CURSOR Cur_get_ingreds IS
1047 SELECT inventory_item_id, qty, detail_uom, scale_type, contribute_yield_ind
1048 FROM fm_matl_dtl
1049 WHERE formula_id = p_formula_id
1050 AND line_type = -1;
1051
1052 l_conv_qty NUMBER := 0;
1053 l_process_loss NUMBER := 0;
1054 l_theo_process_loss NUMBER := 0;
1055 l_msg_count Number := 0;
1056 l_msg_data Varchar2(240);
1057 X_item_id NUMBER;
1058 X_detail_uom VARCHAR2(4);
1059 X_status VARCHAR2(100);
1060 l_process_rec gmd_common_val.process_loss_rec;
1061 UOM_CONVERSION_ERROR EXCEPTION;
1062 PROCESS_LOSS_ERR EXCEPTION;
1063 BEGIN
1064 x_contributing_qty := 0;
1065 x_return_status := FND_API.G_RET_STS_SUCCESS;
1066 /* Loop through ingredients and determine total contributing qty */
1067 FOR get_rec IN Cur_get_ingreds LOOP
1068 IF (get_rec.contribute_yield_ind = 'Y') THEN
1069 /* Convert all ingredient values to yield UM and determine contributing qty */
1070 IF (get_rec.detail_uom <> p_yield_um) THEN
1071 -- NPD Conv. Changed the call to INV_CONVERT.inv_um_convert from gmicuom.uom_conversion
1072 l_conv_qty := INV_CONVERT.inv_um_convert(item_id => get_rec.inventory_item_id
1073 ,precision => 5
1074 ,from_quantity => get_rec.qty
1075 ,from_unit => get_rec.detail_uom
1076 ,to_unit => p_yield_um
1077 ,from_name => NULL
1078 ,to_name => NULL);
1079 IF (l_conv_qty < 0) THEN
1080 X_item_id := get_rec.inventory_item_id;
1081 X_detail_uom := get_rec.detail_uom;
1082 RAISE UOM_CONVERSION_ERROR;
1083 END IF;
1084 ELSE
1085 l_conv_qty := get_rec.qty;
1086 END IF;
1087 /* If ingredient scalable multiply by ratio and calculate contributing qty */
1088 IF (get_rec.scale_type = 1) THEN
1089 X_contributing_qty := X_contributing_qty + (l_conv_qty * p_batchformula_ratio);
1090 ELSE
1091 X_contributing_qty := X_contributing_qty + l_conv_qty;
1092 END IF;
1093 END IF;
1094 END LOOP;
1095 /* Get process loss for this qty */
1096 l_process_rec.qty := X_contributing_qty;
1097 l_process_rec.recipe_id := p_recipe_id;
1098 gmd_common_val.calculate_process_loss(process_loss => l_process_rec,
1099 Entity_type => 'RECIPE' ,
1100 x_recipe_theo_loss => l_theo_process_loss,
1101 x_process_loss => l_process_loss,
1102 x_return_status => X_status,
1103 x_msg_count => l_msg_count,
1104 x_msg_data => l_msg_data);
1105
1106 /* IF (X_status <> FND_API.G_RET_STS_SUCCESS) THEN
1107 RAISE PROCESS_LOSS_ERR;
1108 END IF;*/
1109 /* Shrikant : Added NVL and / 100 in the following equation */
1110 X_contributing_qty := X_contributing_qty * (100 - NVL(l_process_loss,0))/100;
1111 EXCEPTION
1112 WHEN UOM_CONVERSION_ERROR THEN
1113 x_return_status := FND_API.G_RET_STS_ERROR;
1114 gmd_validity_rules.uom_conversion_mesg(p_item_id => X_item_id,
1115 p_from_um => X_detail_uom,
1116 p_to_um => p_yield_um);
1117 END get_contributing_qty;
1118
1119 /*======================================================================
1120 -- PROCEDURE :
1121 -- get_input_ratio
1122 --
1123 -- DESCRIPTION:
1124 -- This PL/SQL procedure is responsible for determining
1125 -- the actual ratio of product for the total input qty.
1126 --
1127 -- REQUIREMENTS
1128 --
1129 -- SYNOPSIS:
1130 -- get_input_ratio (X_formula_id, X_contributing_qty, X_yield_um,
1131 -- X_formula_output, X_output_ratio, X_status);
1132 --
1133 --===================================================================== */
1134 PROCEDURE get_input_ratio(p_formula_id IN NUMBER,
1135 p_contributing_qty IN NUMBER,
1136 p_yield_um IN VARCHAR2,
1137 p_formula_output IN NUMBER,
1138 X_output_ratio OUT NOCOPY NUMBER,
1139 X_return_status OUT NOCOPY VARCHAR2) IS
1140 -- NPD Conv.
1141 CURSOR Cur_get_prods IS
1142 SELECT inventory_item_id, qty, detail_uom, scale_type
1143 FROM fm_matl_dtl
1144 WHERE formula_id = p_formula_id
1145 AND line_type = 1;
1146
1147 l_contributing_qty NUMBER := 0;
1148 l_formula_output NUMBER := 0;
1149 l_conv_qty NUMBER := 0;
1150 l_fixed_prod NUMBER := 0;
1151 X_item_id NUMBER ;
1152 X_detail_uom VARCHAR2(4);
1153 UOM_CONVERSION_ERROR EXCEPTION;
1154 BEGIN
1155 FOR get_rec IN Cur_get_prods LOOP
1156 IF (get_rec.scale_type = 0) THEN
1157 IF (get_rec.detail_uom <> p_yield_um) THEN
1158
1159 -- NPD Conv. Changed the call to INV_CONVERT.inv_um_convert from gmicuom.uom_conversion
1160 l_conv_qty := INV_CONVERT.inv_um_convert(item_id => get_rec.inventory_item_id
1161 ,precision => 5
1162 ,from_quantity => get_rec.qty
1163 ,from_unit => get_rec.detail_uom
1164 ,to_unit => p_yield_um
1165 ,from_name => NULL
1166 ,to_name => NULL);
1167 IF (l_conv_qty < 0) THEN
1168 X_item_id := get_rec.inventory_item_id;
1169 X_detail_uom := get_rec.detail_uom;
1170 RAISE UOM_CONVERSION_ERROR;
1171 END IF;
1172 l_fixed_prod := l_fixed_prod + l_conv_qty;
1173 ELSE
1174 l_fixed_prod := l_fixed_prod + get_rec.qty;
1175 END IF;
1176 END IF;
1177 END LOOP;
1178 l_contributing_qty := p_contributing_qty - l_fixed_prod;
1179 l_formula_output := P_formula_output - l_fixed_prod;
1180 X_output_ratio := l_contributing_qty / l_formula_output;
1181 EXCEPTION
1182 WHEN UOM_CONVERSION_ERROR THEN
1183 x_return_status := FND_API.G_RET_STS_ERROR;
1184 gmd_validity_rules.uom_conversion_mesg(p_item_id => X_item_id,
1185 p_from_um => X_detail_uom,
1186 p_to_um => p_yield_um);
1187 END get_input_ratio;
1188
1189 /*======================================================================
1190 -- PROCEDURE :
1191 -- uom_conversion_mesg
1192 --
1193 -- DESCRIPTION:
1194 -- This PL/SQL procedure is responsible for showing
1195 -- the the message about uom conversion errors.
1196 --
1197 -- REQUIREMENTS
1198 --
1199 -- SYNOPSIS:
1200 -- uom_conversion_mesg (X_item_id, X_from_um, X_to_um);
1201 --
1202 --===================================================================== */
1203 PROCEDURE uom_conversion_mesg(p_item_id IN NUMBER,
1204 p_from_um IN VARCHAR2,
1205 p_to_um IN VARCHAR2) IS
1206
1207 -- NPD Conv. Modified cursor to get concatenated segments for the item_id
1208 CURSOR Cur_get_item IS
1209 SELECT concatenated_segments
1210 FROM mtl_system_items_kfv
1211 WHERE inventory_item_id = p_item_id;
1212 X_item_no VARCHAR2(32);
1213 BEGIN
1214 OPEN Cur_get_item;
1215 FETCH Cur_get_item INTO X_item_no;
1216 CLOSE Cur_get_item;
1217 FND_MESSAGE.SET_NAME('GMI', 'IC_API_UOM_CONVERSION_ERROR');
1218 FND_MESSAGE.SET_TOKEN('ITEM_NO', X_item_no);
1219 FND_MESSAGE.SET_TOKEN('FROM_UOM', p_from_um);
1220 FND_MESSAGE.SET_TOKEN('TO_UOM', p_to_um);
1221 FND_MSG_PUB.ADD;
1222 END uom_conversion_mesg;
1223
1224
1225 /*======================================================================
1226 -- PROCEDURE :
1227 -- get_all_validity_rules
1228 --
1229 -- DESCRIPTION:
1230 -- This PL/SQL procedure is responsible for getting all the
1231 -- validity rules based on the input parameters.
1232 --
1233 -- REQUIREMENTS
1234 --
1235 -- SYNOPSIS:
1236 -- get_all_validity_rules (1.0, X_init_msg_list, X_recipe_id, X_item_id,
1237 -- X_return_status, X_msg_count, X_msg_data,
1238 -- X_return_code, X_vr_table);
1239 --
1240 -- HISTORY
1241 -- RajaSekhar 11-Jul-2002 BUG#2436355 Added to get all ( Planning,costing,
1242 -- prodoction etc) validity rules of all the statuses.
1243 --===================================================================== */
1244 /* Formatted on 2002/07/11 18:01 (RevealNet Formatter v4.4.0) */
1245 PROCEDURE Get_all_validity_rules (
1246 P_api_version IN NUMBER,
1247 P_init_msg_list IN VARCHAR2 := Fnd_api.G_false,
1248 P_recipe_id IN NUMBER := NULL,
1249 P_item_id IN NUMBER := NULL,
1250 p_revision IN VARCHAR2 := NULL,
1251 p_least_cost_validity IN VARCHAR2 := 'F',
1252 X_return_status OUT NOCOPY VARCHAR2,
1253 X_msg_count OUT NOCOPY NUMBER,
1254 X_msg_data OUT NOCOPY VARCHAR2,
1255 X_return_code OUT NOCOPY NUMBER,
1256 X_recipe_validity_out OUT NOCOPY Recipe_validity_tbl
1257 )
1258 IS
1259 -- local Variables
1260 L_api_name VARCHAR2 (30) := 'get_validity_rules';
1261 L_api_version NUMBER := 1.0;
1262 I NUMBER := 0;
1263 L_msg_count NUMBER;
1264 L_msg_data VARCHAR2 (100);
1265 L_return_status VARCHAR2(10);
1266 L_return_code VARCHAR2 (10);
1267
1268 l_unit_cost NUMBER;
1269 l_total_cost NUMBER;
1270 l_formula_id NUMBER;
1271 l_quantity NUMBER;
1272 l_uom VARCHAR2(3);
1273
1274 --Cursor to get data based on recipe ID
1275 CURSOR Get_val_recipe IS
1276 SELECT v.*
1277 FROM Gmd_recipe_validity_rules V, Gmd_recipes R, Gmd_status S
1278 WHERE V.Recipe_id = R.Recipe_id
1279 AND V.Validity_rule_status = S.Status_code
1280 AND V.Recipe_id = NVL (P_recipe_id, V.Recipe_id)
1281 AND v.delete_mark = 0
1282 ORDER BY R.Recipe_no,R.Recipe_version, V.Recipe_use,Orgn_code, Preference,S.Status_type;
1283
1284 --Cursor to get data based on item.
1285
1286 CURSOR Get_val_item IS
1287 SELECT V.*
1288 FROM Gmd_recipe_validity_rules V,
1289 Gmd_recipes R,
1290 Gmd_status S
1291 WHERE V.Recipe_id = R.Recipe_id
1292 AND V.Validity_rule_status = S.Status_code
1293 AND V.inventory_item_id = P_item_id
1294 AND (p_revision IS NULL OR (p_revision IS NOT NULL AND v.revision = p_revision))
1295 AND v.delete_mark = 0
1296 ORDER BY R.Recipe_no,R.Recipe_version, V.Recipe_use,Orgn_code, Preference,S.Status_type;
1297
1298 CURSOR Cur_get_VR IS
1299 SELECT *
1300 FROM GMD_VAL_RULE_GTMP;
1301
1302 CURSOR Cur_get_form_id (v_recipe_id NUMBER, V_inventory_item_id NUMBER) IS
1303 SELECT rcp.formula_id, SUM(qty), MAX(detail_uom)
1304 FROM gmd_recipes rcp, fm_matl_dtl d
1305 WHERE rcp.recipe_id = v_recipe_id
1306 AND rcp.formula_id = d.formula_id
1307 AND d.line_type = 1
1308 AND d.inventory_item_id = V_inventory_item_id;
1309
1310 GET_FORMULA_COST_ERR EXCEPTION;
1311
1312 BEGIN
1313 IF (NOT Fnd_api.Compatible_api_call ( L_api_version,
1314 P_api_version,
1315 L_api_name,
1316 G_pkg_name ))
1317 THEN
1318 RAISE Fnd_api.G_exc_unexpected_error;
1319 END IF;
1320
1321 IF (Fnd_api.To_boolean (P_init_msg_list))
1322 THEN
1323 Fnd_msg_pub.Initialize;
1324 END IF;
1325
1326 X_return_status := Fnd_api.G_ret_sts_success;
1327
1328 /* Delete from this table for any existing data */
1329 DELETE FROM GMD_VAL_RULE_GTMP;
1330
1331 IF (P_item_id IS NOT NULL) THEN
1332 FOR Get_rec IN Get_val_item LOOP
1333 X_return_status := Fnd_api.G_ret_sts_success;
1334
1335 IF p_least_cost_validity = 'T' THEN
1336 OPEN Cur_get_form_id (get_rec.recipe_id, get_rec.inventory_item_id);
1337 FETCH Cur_get_form_id INTO l_formula_id, l_quantity, l_uom;
1338 CLOSE Cur_get_form_id;
1339 IF (get_rec.organization_id IS NOT NULL) THEN
1340 GMD_VALIDITY_RULES.get_formula_cost (p_formula_id => l_formula_id
1341 ,p_requested_qty => l_quantity
1342 ,p_requested_uom => l_uom
1343 ,p_product_id => get_rec.inventory_item_id
1344 ,p_organization_id => get_rec.organization_id
1345 ,X_unit_cost => l_unit_cost
1346 ,X_total_cost => l_total_cost
1347 ,X_return_status => l_return_status);
1348 IF l_return_status <> FND_API.g_ret_sts_success THEN
1349 RAISE GET_FORMULA_COST_ERR;
1350 END IF;
1351 END IF;
1352 END IF; /* IF p_least_cost_validity = 'T' */
1353 GMD_VALIDITY_RULES.insert_val_temp_tbl(p_val_rec => get_rec
1354 ,p_unit_cost => l_unit_cost
1355 ,p_total_cost => l_total_cost);
1356 END LOOP;
1357 ELSE
1358 /* Try to get validity rules based on recipe ID */
1359 FOR Get_rec IN Get_val_recipe LOOP
1360 X_return_status := Fnd_api.G_ret_sts_success;
1361
1362 IF p_least_cost_validity = 'T' THEN
1363 OPEN Cur_get_form_id (get_rec.recipe_id, get_rec.inventory_item_id);
1364 FETCH Cur_get_form_id INTO l_formula_id, l_quantity, l_uom;
1365 CLOSE Cur_get_form_id;
1366 IF (get_rec.organization_id IS NOT NULL) THEN
1367 GMD_VALIDITY_RULES.get_formula_cost (p_formula_id => l_formula_id
1368 ,p_requested_qty => l_quantity
1369 ,p_requested_uom => l_uom
1370 ,p_product_id => get_rec.inventory_item_id
1371 ,p_organization_id => get_rec.organization_id
1372 ,X_unit_cost => l_unit_cost
1373 ,X_total_cost => l_total_cost
1374 ,X_return_status => l_return_status);
1375 IF l_return_status <> FND_API.g_ret_sts_success THEN
1376 RAISE GET_FORMULA_COST_ERR;
1377 END IF;
1378 END IF;
1379 END IF; /* IF p_least_cost_validity = 'T' */
1380 GMD_VALIDITY_RULES.insert_val_temp_tbl(p_val_rec => get_rec
1381 ,p_unit_cost => l_unit_cost
1382 ,p_total_cost => l_total_cost);
1383 END LOOP;
1384 END IF;
1385
1386 i := 0;
1387 FOR l_rec IN Cur_get_VR LOOP
1388 i := i + 1;
1389 x_recipe_validity_out(i).recipe_validity_rule_id := l_rec.recipe_validity_rule_id ;
1390 x_recipe_validity_out(i).recipe_id := l_rec.recipe_id ;
1391 x_recipe_validity_out(i).orgn_code := l_rec.orgn_code ;
1392 x_recipe_validity_out(i).recipe_use := l_rec.recipe_use ;
1393 x_recipe_validity_out(i).preference := l_rec.preference ;
1394 x_recipe_validity_out(i).start_date := l_rec.start_date ;
1395 x_recipe_validity_out(i).end_date := l_rec.end_date ;
1396 x_recipe_validity_out(i).min_qty := l_rec.min_qty ;
1397 x_recipe_validity_out(i).max_qty := l_rec.max_qty ;
1398 x_recipe_validity_out(i).std_qty := l_rec.std_qty ;
1399 x_recipe_validity_out(i).inv_min_qty := l_rec.inv_min_qty ;
1400 x_recipe_validity_out(i).inv_max_qty := l_rec.inv_max_qty ;
1401 x_recipe_validity_out(i).text_code := l_rec.text_code ;
1402 x_recipe_validity_out(i).attribute_category := l_rec.attribute_category ;
1403 x_recipe_validity_out(i).attribute1 := l_rec.attribute1 ;
1404 x_recipe_validity_out(i).attribute2 := l_rec.attribute2 ;
1405 x_recipe_validity_out(i).attribute3 := l_rec.attribute3 ;
1406 x_recipe_validity_out(i).attribute4 := l_rec.attribute4 ;
1407 x_recipe_validity_out(i).attribute5 := l_rec.attribute5 ;
1408 x_recipe_validity_out(i).attribute6 := l_rec.attribute6 ;
1409 x_recipe_validity_out(i).attribute7 := l_rec.attribute7 ;
1410 x_recipe_validity_out(i).attribute8 := l_rec.attribute8 ;
1411 x_recipe_validity_out(i).attribute9 := l_rec.attribute9 ;
1412 x_recipe_validity_out(i).attribute10 := l_rec.attribute10 ;
1413 x_recipe_validity_out(i).attribute11 := l_rec.attribute11;
1414 x_recipe_validity_out(i).attribute12 := l_rec.attribute12 ;
1415 x_recipe_validity_out(i).attribute13 := l_rec.attribute13 ;
1416 x_recipe_validity_out(i).attribute14 := l_rec.attribute14 ;
1417 x_recipe_validity_out(i).attribute15 := l_rec.attribute15 ;
1418 x_recipe_validity_out(i).attribute16 := l_rec.attribute16 ;
1419 x_recipe_validity_out(i).attribute17 := l_rec.attribute17 ;
1420 x_recipe_validity_out(i).attribute18 := l_rec.attribute18 ;
1421 x_recipe_validity_out(i).attribute19 := l_rec.attribute19 ;
1422 x_recipe_validity_out(i).attribute20 := l_rec.attribute20 ;
1423 x_recipe_validity_out(i).attribute21 := l_rec.attribute21 ;
1424 x_recipe_validity_out(i).attribute22 := l_rec.attribute22 ;
1425 x_recipe_validity_out(i).attribute23 := l_rec.attribute23 ;
1426 x_recipe_validity_out(i).attribute24 := l_rec.attribute24 ;
1427 x_recipe_validity_out(i).attribute25 := l_rec.attribute25 ;
1428 x_recipe_validity_out(i).attribute26 := l_rec.attribute26 ;
1429 x_recipe_validity_out(i).attribute27 := l_rec.attribute27 ;
1430 x_recipe_validity_out(i).attribute28 := l_rec.attribute28 ;
1431 x_recipe_validity_out(i).attribute29 := l_rec.attribute29 ;
1432 x_recipe_validity_out(i).attribute30 := l_rec.attribute30 ;
1433 x_recipe_validity_out(i).created_by := l_rec.created_by ;
1434 x_recipe_validity_out(i).creation_date := l_rec.creation_date ;
1435 x_recipe_validity_out(i).last_updated_by := l_rec.last_updated_by ;
1436 x_recipe_validity_out(i).last_update_date := l_rec.last_update_date ;
1437 x_recipe_validity_out(i).last_update_login := l_rec.last_update_login ;
1438 x_recipe_validity_out(i).validity_rule_status := l_rec.validity_rule_status ;
1439 x_recipe_validity_out(i).planned_process_loss := l_rec.planned_process_loss ;
1440 x_recipe_validity_out(i).organization_id := l_rec.organization_id ;
1441 x_recipe_validity_out(i).inventory_item_id := l_rec.inventory_item_id ;
1442 x_recipe_validity_out(i).revision := l_rec.revision ;
1443 x_recipe_validity_out(i).detail_uom := l_rec.detail_uom ;
1444 x_recipe_validity_out(i).unit_cost := l_rec.unit_cost ;
1445 x_recipe_validity_out(i).total_cost := l_rec.total_cost ;
1446 END LOOP;
1447
1448 -- standard call to get msge cnt, and if cnt is 1, get mesg info
1449 Fnd_msg_pub.Count_and_get (P_count => X_msg_count, P_data => X_msg_data);
1450
1451 EXCEPTION
1452 WHEN GET_FORMULA_COST_ERR THEN
1453 x_return_status := FND_API.G_RET_STS_ERROR;
1454 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
1455 P_data => x_msg_data);
1456 WHEN Fnd_api.G_exc_error THEN
1457 X_return_code := SQLCODE;
1458 X_return_status := Fnd_api.G_ret_sts_error;
1459 Fnd_msg_pub.Count_and_get ( P_count=> X_msg_count,
1460 P_data=> X_msg_data );
1461 WHEN Fnd_api.G_exc_unexpected_error THEN
1462 X_return_code := SQLCODE;
1463 X_return_status := Fnd_api.G_ret_sts_unexp_error;
1464 Fnd_msg_pub.Count_and_get ( P_count=> X_msg_count,
1465 P_data=> X_msg_data );
1466 WHEN OTHERS THEN
1467 X_return_code := SQLCODE;
1468 X_return_status := Fnd_api.G_ret_sts_error;
1469 Fnd_msg_pub.Count_and_get ( P_count=> X_msg_count,
1470 P_data=> X_msg_data );
1471 END Get_all_validity_rules;
1472
1473 /*======================================================================
1474 -- PROCEDURE :
1475 -- get_validity_scale_factor
1476 --
1477 -- DESCRIPTION:
1478 -- This PL/SQL procedure is responsible for deriving the validity rule
1479 -- scale factor based on the std qty and the formula product qty.
1480 --
1481 -- REQUIREMENTS
1482 --
1483 -- SYNOPSIS:
1484 -- get_validity_scale_factor (p_recipe_id, p_item_id, p_std_qty, p_std_um,
1485 -- x_scale_factor, x_return_status);
1486 --
1487 --
1488 --===================================================================== */
1489 PROCEDURE get_validity_scale_factor(p_recipe_id IN NUMBER ,
1490 p_item_id IN NUMBER ,
1491 p_std_qty IN NUMBER ,
1492 p_std_um IN VARCHAR2 ,
1493 x_scale_factor OUT NOCOPY NUMBER,
1494 x_return_status OUT NOCOPY VARCHAR2) IS
1495 CURSOR Cur_get_product_lines IS
1496 SELECT qty, detail_uom
1497 FROM gmd_recipes_b r, fm_matl_dtl d
1498 WHERE r.recipe_id = p_recipe_id
1499 AND r.formula_id = d.formula_id
1500 AND d.line_type = 1
1501 AND d.inventory_item_id = p_item_id;
1502 l_prod_rec Cur_get_product_lines%ROWTYPE;
1503 l_prod_qty NUMBER DEFAULT 0;
1504 l_temp_qty NUMBER;
1505
1506 ITEM_NOT_FOUND EXCEPTION;
1507 UOM_CONVERSION_ERR EXCEPTION;
1508 BEGIN
1509 /* Let us initialize the return status to success */
1510 x_return_status := FND_API.g_ret_sts_success;
1511
1512 /* Let us fetch the product quantities in the formula for the item passed in */
1513 OPEN Cur_get_product_lines;
1514 FETCH Cur_get_product_lines INTO l_prod_rec;
1515 IF Cur_get_product_lines%NOTFOUND THEN
1516 CLOSE Cur_get_product_lines;
1517 RAISE ITEM_NOT_FOUND;
1518 END IF;
1519 WHILE Cur_get_product_lines%FOUND
1520 LOOP
1521 IF l_prod_rec.detail_uom = p_std_um THEN
1522 l_prod_qty := l_prod_qty + l_prod_rec.qty;
1523 ELSE
1524 -- NPD Conv. Changed the call to INV_CONVERT.inv_um_convert from gmicuom.uom_conversion
1525 l_temp_qty := INV_CONVERT.inv_um_convert(item_id => p_item_id
1526 ,precision => 5
1527 ,from_quantity => l_prod_rec.qty
1528 ,from_unit => l_prod_rec.detail_uom
1529 ,to_unit => p_std_um
1530 ,from_name => NULL
1531 ,to_name => NULL);
1532 IF l_temp_qty < 0 THEN
1533 RAISE uom_conversion_err;
1534 ELSE
1535 l_prod_qty := l_prod_qty + l_temp_qty;
1536 END IF;
1537 END IF; /* IF l_prod_rec.item_um = p_std_um */
1538 FETCH Cur_get_product_lines INTO l_prod_rec;
1539 END LOOP; /* WHILE Cur_get_product_lines%FOUND */
1540 CLOSE Cur_get_product_lines;
1541
1542 /* OK, now we have the product qty let us evaluate the ratio */
1543 IF l_prod_qty > 0 THEN
1544 x_scale_factor := p_std_qty / l_prod_qty;
1545 ELSE
1546 x_scale_factor := p_std_qty;
1547 END IF;
1548 EXCEPTION
1549 WHEN item_not_found THEN
1550 x_return_status := FND_API.g_ret_sts_error;
1551 WHEN uom_conversion_err THEN
1552 x_return_status := FND_API.g_ret_sts_error;
1553 uom_conversion_mesg (p_item_id, l_prod_rec.detail_uom, p_std_um);
1554 WHEN OTHERS THEN
1555 x_return_status := FND_API.g_ret_sts_unexp_error;
1556 fnd_msg_pub.add_exc_msg ('GMD_VALIDITY_RULES', 'GET_VALIDITY_SCALE_FACTOR');
1557 END get_validity_scale_factor;
1558
1559 /*======================================================================
1560 -- PROCEDURE :
1561 -- get_validity_output_factor
1562 --
1563 -- DESCRIPTION:
1564 -- This PL/SQL procedure is responsible for deriving the validity rule
1565 -- scale factor based on the std qty and the formula product qty.
1566 --
1567 -- REQUIREMENTS
1568 --
1569 -- SYNOPSIS:
1570 -- get_validity_output_factor (p_recipe_id, p_item_id, p_std_qty, p_std_um,
1571 -- x_scale_factor, x_return_status);
1572 --
1573 --
1574 --===================================================================== */
1575 PROCEDURE get_validity_output_factor(p_recipe_id IN NUMBER ,
1576 p_item_id IN NUMBER ,
1577 p_std_qty IN NUMBER ,
1578 p_std_um IN VARCHAR2 ,
1579 x_scale_factor OUT NOCOPY NUMBER,
1580 x_return_status OUT NOCOPY VARCHAR2) IS
1581 CURSOR Cur_get_tot_qty IS
1582 SELECT f.formula_id, total_output_qty, yield_uom
1583 FROM fm_form_mst_b f, gmd_recipes_b r
1584 WHERE r.recipe_id = p_recipe_id
1585 AND r.formula_id = f.formula_id;
1586
1587 l_form_rec Cur_get_tot_qty%ROWTYPE;
1588 l_total_output_qty NUMBER;
1589 l_scaled_output_qty NUMBER;
1590 l_ing_qty NUMBER;
1591 l_temp_qty NUMBER;
1592 l_scale_factor NUMBER;
1593 l_msg_count NUMBER;
1594 l_msg_data VARCHAR2(2000);
1595 l_uom mtl_units_of_measure.unit_of_measure%TYPE;
1596
1597 BEGIN
1598 /* Let us initialize the return status to success */
1599 x_return_status := FND_API.g_ret_sts_success;
1600
1601 /* Lets get the scale factor between the validity std qty and the formula product qty */
1602 gmd_validity_rules.get_validity_scale_factor (p_recipe_id => p_recipe_id
1603 ,p_item_id => p_item_id
1604 ,p_std_qty => p_std_qty
1605 ,p_std_um => p_std_um
1606 ,x_scale_factor => l_scale_factor
1607 ,x_return_status => x_return_status);
1608
1609 OPEN Cur_get_tot_qty;
1610 FETCH Cur_get_tot_qty INTO l_form_rec;
1611 CLOSE Cur_get_tot_qty;
1612
1613 IF l_form_rec.total_output_qty IS NULL THEN
1614 /* If the total output qty was not calculated previously let us recalculate it */
1615 l_uom := p_std_um;
1616 GMD_COMMON_VAL.Calculate_total_qty(formula_id => l_form_rec.formula_id,
1617 x_product_qty => l_total_output_qty,
1618 x_ingredient_qty => l_ing_qty,
1619 x_uom => l_uom,
1620 x_return_status => x_return_status,
1621 x_msg_count => l_msg_count,
1622 x_msg_data => l_msg_data);
1623 ELSE
1624 l_total_output_qty := l_form_rec.total_output_qty;
1625 l_uom := l_form_rec.yield_uom;
1626 END IF;
1627
1628 /* Let us now fetch the total output qty based on the factor derived from std qty */
1629 GMD_COMMON_VAL.Calculate_total_qty(formula_id => l_form_rec.formula_id,
1630 x_product_qty => l_scaled_output_qty,
1631 x_ingredient_qty => l_ing_qty,
1632 x_uom => l_uom,
1633 x_return_status => x_return_status,
1634 x_msg_count => l_msg_count,
1635 x_msg_data => l_msg_data,
1636 p_scale_factor => l_scale_factor,
1637 p_primaries => 'OUTPUTS');
1638
1639 /* OK, now we have the scaled and the formula total qty let us evaluate the ratio */
1640 IF l_scaled_output_qty > 0 THEN
1641 x_scale_factor := l_scaled_output_qty / l_total_output_qty;
1642 ELSIF l_scaled_output_qty IS NOT NULL THEN
1643 x_scale_factor := l_scaled_output_qty;
1644 ELSE
1645 x_scale_factor := 1;
1646 END IF;
1647 EXCEPTION
1648 WHEN OTHERS THEN
1649 x_return_status := FND_API.g_ret_sts_unexp_error;
1650 fnd_msg_pub.add_exc_msg ('GMD_VALIDITY_RULES', 'GET_VALIDITY_OUTPUT_FACTOR');
1651 END get_validity_output_factor;
1652
1653 /*=================================================================================
1654 -- PROCEDURE :
1655 -- insert_val_temp_tbl
1656 --
1657 -- DESCRIPTION:
1658 -- This PL/SQL procedure is responsible for inserting the validity rule to the
1659 -- temp table.
1660 -- REQUIREMENTS
1661 --
1662 -- SYNOPSIS:
1663 -- insert_val_temp_tbl
1664 --
1665 -- HISTORY
1666 -- Thomas Daniel 16-Nov-2005 Created.
1667 --===================================================================== */
1668 PROCEDURE insert_val_temp_tbl (p_val_rec IN GMD_RECIPE_VALIDITY_RULES%ROWTYPE
1669 ,p_unit_cost IN NUMBER
1670 ,p_total_cost IN NUMBER) IS
1671 BEGIN
1672 INSERT INTO GMD_VAL_RULE_GTMP(
1673 recipe_validity_rule_id, recipe_id , orgn_code , recipe_use ,
1674 preference , start_date , end_date , min_qty ,
1675 max_qty , std_qty , inv_min_qty , inv_max_qty ,
1676 text_code , attribute_category , attribute1 , attribute2 ,
1677 attribute3 , attribute4 , attribute5 , attribute6 ,
1678 attribute7 , attribute8 , attribute9 , attribute10 ,
1679 attribute11 , attribute12 , attribute13 , attribute14 ,
1680 attribute15 , attribute16 , attribute17 , attribute18 ,
1681 attribute19 , attribute20 , attribute21 , attribute22 ,
1682 attribute23 , attribute24 , attribute25 , attribute26 ,
1683 attribute27 , attribute28 , attribute29 , attribute30 ,
1684 created_by , creation_date , last_updated_by , last_update_date ,
1685 last_update_login , validity_rule_status , planned_process_loss , organization_id ,
1686 inventory_item_id , revision , detail_uom , unit_cost ,
1687 total_cost , delete_mark)
1688 VALUES
1689 (
1690 p_val_rec.recipe_validity_rule_id, p_val_rec.recipe_id ,
1691 p_val_rec.orgn_code , p_val_rec.recipe_use ,
1692 p_val_rec.preference , p_val_rec.start_date ,
1693 p_val_rec.end_date , p_val_rec.min_qty ,
1694 p_val_rec.max_qty , p_val_rec.std_qty ,
1695 p_val_rec.inv_min_qty , p_val_rec.inv_max_qty ,
1696 p_val_rec.text_code , p_val_rec.attribute_category ,
1697 p_val_rec.attribute1 , p_val_rec.attribute2 ,
1698 p_val_rec.attribute3 , p_val_rec.attribute4 ,
1699 p_val_rec.attribute5 , p_val_rec.attribute6 ,
1700 p_val_rec.attribute7 , p_val_rec.attribute8 ,
1701 p_val_rec.attribute9 , p_val_rec.attribute10 ,
1702 p_val_rec.attribute11 , p_val_rec.attribute12 ,
1703 p_val_rec.attribute13 , p_val_rec.attribute14 ,
1704 p_val_rec.attribute15 , p_val_rec.attribute16 ,
1705 p_val_rec.attribute17 , p_val_rec.attribute18 ,
1706 p_val_rec.attribute19 , p_val_rec.attribute20 ,
1707 p_val_rec.attribute21 , p_val_rec.attribute22 ,
1708 p_val_rec.attribute23 , p_val_rec.attribute24 ,
1709 p_val_rec.attribute25 , p_val_rec.attribute26 ,
1710 p_val_rec.attribute27 , p_val_rec.attribute28 ,
1711 p_val_rec.attribute29 , p_val_rec.attribute30 ,
1712 p_val_rec.created_by , p_val_rec.creation_date ,
1713 p_val_rec.last_updated_by , p_val_rec.last_update_date ,
1714 p_val_rec.last_update_login , p_val_rec.validity_rule_status ,
1715 p_val_rec.planned_process_loss , p_val_rec.organization_id ,
1716 p_val_rec.inventory_item_id , p_val_rec.revision ,
1717 p_val_rec.detail_uom , p_unit_cost ,
1718 p_total_cost , p_val_rec.delete_mark);
1719 END insert_val_temp_tbl;
1720
1721
1722 /*=================================================================================
1723 -- PROCEDURE :
1724 -- get_formula_cost
1725 --
1726 -- DESCRIPTION:
1727 -- This PL/SQL procedure is responsible for scaling the formula appropriately
1728 -- and getting the cost for the formula.
1729 --
1730 -- REQUIREMENTS
1731 --
1732 -- SYNOPSIS:
1733 -- get_formula_cost
1734 --
1735 -- HISTORY
1736 -- Thomas Daniel 16-Nov-2005 Created.
1737 --===================================================================== */
1738 PROCEDURE Get_Formula_Cost (
1739 p_formula_id IN NUMBER,
1740 p_requested_qty IN NUMBER,
1741 p_requested_uom IN VARCHAR2,
1742 p_product_id IN NUMBER,
1743 p_organization_id IN NUMBER,
1744 X_unit_cost OUT NOCOPY NUMBER,
1745 X_total_cost OUT NOCOPY NUMBER,
1746 X_return_status OUT NOCOPY VARCHAR2) IS
1747
1748 CURSOR Cur_get_cost_method (v_orgn_id NUMBER) IS
1749 SELECT Cost_Type, cost_source
1750 FROM gmd_tech_parameters_b
1751 WHERE organization_id = v_orgn_id
1752 AND Default_cost_parameter = 1;
1753
1754 CURSOR Cur_get_lines IS
1755 SELECT *
1756 FROM fm_matl_dtl
1757 WHERE formula_id = p_formula_id
1758 ORDER BY line_type, line_no;
1759
1760 l_cost_mthd VARCHAR2(25);
1761 l_cost_source NUMBER(15);
1762 l_product_qty NUMBER := 0;
1763 l_product_uom VARCHAR2(3);
1764 l_scale_tab_in GMD_COMMON_SCALE.scale_tab;
1765 l_scale_tab_out GMD_COMMON_SCALE.scale_tab;
1766 l_count BINARY_INTEGER := 0;
1767 l_return_status VARCHAR2(1);
1768 l_return_id BINARY_INTEGER;
1769 l_cost NUMBER;
1770 l_ing_cost NUMBER;
1771 l_ing_qty NUMBER := 0;
1772 l_organization_id BINARY_INTEGER;
1773 l_msg_count BINARY_INTEGER;
1774 l_msg_data VARCHAR2(2000);
1775 l_cost_component_class_id BINARY_INTEGER;
1776 l_cost_analysis_code VARCHAR2(20);
1777 l_rows BINARY_INTEGER;
1778
1779 SCALE_ERROR EXCEPTION;
1780 BEGIN
1781 /* Initialize return status to success */
1782 X_return_status := FND_API.G_RET_STS_SUCCESS;
1783
1784 l_organization_id := P_organization_id;
1785
1786 /* Get the cost source organization for the organization passed in */
1787 IF G_cost_source_orgn_id IS NULL THEN
1788 GMD_API_GRP.FETCH_PARM_VALUES(P_orgn_id => l_organization_id,
1789 P_parm_name => 'GMD_COST_SOURCE_ORGN',
1790 P_parm_value => G_cost_source_orgn_id,
1791 x_return_status => l_return_status);
1792
1793 END IF;
1794 -- Get cost method in cost source orgn
1795
1796 IF G_default_cost_mthd IS NULL THEN
1797 OPEN Cur_get_cost_method(l_organization_id);
1798 FETCH Cur_get_cost_method INTO l_cost_mthd, l_cost_source;
1799 CLOSE Cur_get_cost_method;
1800 END IF;
1801
1802 IF l_cost_mthd IS NULL THEN
1803 OPEN Cur_get_cost_method(G_cost_source_orgn_id);
1804 FETCH Cur_get_cost_method INTO G_default_cost_mthd, G_cost_source;
1805 CLOSE Cur_get_cost_method;
1806 END IF;
1807
1808 X_unit_cost := 0;
1809 X_total_cost := 0;
1810
1811 FOR l_rec IN Cur_get_lines LOOP
1812 l_count := l_count + 1;
1813 l_scale_tab_in(l_count).line_no := l_rec.line_no;
1814 l_scale_tab_in(l_count).line_type := l_rec.line_type;
1815 l_scale_tab_in(l_count).inventory_item_id := l_rec.inventory_item_id;
1816 l_scale_tab_in(l_count).qty := l_rec.qty;
1817 l_scale_tab_in(l_count).detail_uom := l_rec.detail_uom;
1818 l_scale_tab_in(l_count).scale_type := l_rec.scale_type;
1819 l_scale_tab_in(l_count).contribute_yield_ind := l_rec.contribute_yield_ind;
1820 l_scale_tab_in(l_count).scale_multiple := l_rec.scale_multiple;
1821 l_scale_tab_in(l_count).scale_rounding_variance := l_rec.scale_rounding_variance;
1822 l_scale_tab_in(l_count).rounding_direction := l_rec.rounding_direction;
1823 IF (l_rec.line_type = 1) AND
1824 (p_product_id = l_rec.inventory_item_id) THEN
1825 l_product_qty := l_product_qty + l_rec.qty;
1826 l_product_uom := l_rec.detail_uom;
1827 END IF;
1828 END LOOP;
1829
1830 /* Lets check if we need to scale the formula based on the requested qty */
1831 IF (l_product_qty <> p_requested_qty) OR
1832 (p_requested_uom <> l_product_uom) THEN
1833 GMD_COMMON_SCALE.scale(p_scale_tab => l_scale_tab_in
1834 ,p_orgn_id => G_cost_source_orgn_id
1835 ,p_scale_factor => p_requested_qty / l_product_qty
1836 ,p_primaries => 'PRODUCT'
1837 ,x_scale_tab => l_scale_tab_out
1838 ,x_return_status => l_return_status);
1839 IF l_return_status <> FND_API.G_ret_sts_success THEN
1840 RAISE SCALE_ERROR;
1841 END IF;
1842 ELSE
1843 l_scale_tab_out := l_scale_tab_in;
1844 END IF;
1845
1846 -- Now lets loop through the scaled tab and calculate the total cost
1847 FOR i IN 1..l_scale_tab_out.COUNT LOOP
1848 -- Get cost for each ingredient
1849 IF l_scale_tab_out(i).line_type = -1 THEN
1850 GMD_LCF_FETCH_PKG.load_cost_values (V_orgn_id => l_organization_id
1851 ,V_inv_item_id => l_scale_tab_out(i).inventory_item_id
1852 ,V_cost_type => NVL(l_cost_mthd,G_default_cost_mthd )
1853 ,V_date => SYSDATE
1854 ,V_cost_orgn => NVL(G_cost_source_orgn_id, l_organization_id)
1855 ,V_source => NVL(l_cost_source,G_cost_source)
1856 ,X_value => l_cost);
1857 IF NVL(l_cost,0) > 0 THEN
1858 l_ing_cost := NVL(l_ing_cost,0) + NVL(l_cost, 0) * l_scale_tab_out(i).qty;
1859 END IF;
1860 l_ing_qty := l_ing_qty + l_scale_tab_out(i).qty;
1861 END IF;
1862 END LOOP;
1863 X_total_cost := l_ing_cost;
1864 IF l_ing_qty > 0 THEN
1865 X_unit_cost := l_ing_cost / l_ing_qty;
1866 END IF;
1867 EXCEPTION
1868 WHEN SCALE_ERROR THEN
1869 x_return_status := FND_API.G_RET_STS_ERROR;
1870 END Get_Formula_Cost;
1871
1872
1873 END GMD_VALIDITY_RULES;