[Home] [Help]
PACKAGE BODY: APPS.GMD_FORMULA_ANALYSIS
Source
1 PACKAGE BODY gmd_formula_analysis AS
2 /* $Header: GMDFANLB.pls 120.6.12000000.2 2007/09/13 07:21:34 kannavar ship $ */
3
4 G_PKG_NAME VARCHAR2(32);
5
6 /*======================================================================
7 -- PROCEDURE :
8 -- analyze_formula
9 --
10 -- DESCRIPTION:
11 -- This PL/SQL procedure is responsible for analyzing the
12 -- formula ingredient contribution.
13 --
14 -- REQUIREMENTS
15 --
16 -- SYNOPSIS:
17 -- analyze_formula(X_orgn_code, X_lab, X_formula_id, X_analysis_qty,
18 -- X_rep_um, X_explosion_rule, X_return_status,
19 -- X_msg_count, X_msg_data);
20 --
21 --===================================================================== */
22 PROCEDURE analyze_formula(err_buf OUT NOCOPY VARCHAR2,
23 ret_code OUT NOCOPY VARCHAR2,
24 p_organization_id IN NUMBER,
25 p_laboratory_id IN NUMBER,
26 p_formula_no IN VARCHAR2,
27 p_formula_vers IN NUMBER,
28 p_formula_id IN NUMBER,
29 p_analysis_qty IN NUMBER,
30 p_rep_um IN VARCHAR2,
31 p_explosion_rule IN NUMBER,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2) IS
35
36 -- NPD Conv.
37 CURSOR Cur_std_um(V_type VARCHAR2) IS
38 SELECT uom_code
39 FROM mtl_units_of_measure
40 WHERE uom_class = V_type
41 AND base_uom_flag = 'Y';
42
43 -- NPD Conv.
44 CURSOR Cur_prods_byprods IS
45 SELECT d.inventory_item_id, d.qty, d.detail_uom, i.concatenated_segments
46 FROM fm_matl_dtl d, mtl_system_items_kfv i
47 WHERE formula_id = p_formula_id
48 AND line_type <> -1
49 AND i.inventory_item_id = d.inventory_item_id;
50
51 X_user NUMBER := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
52 X_login_id NUMBER := TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'));
53 X_row NUMBER := 0;
54 X_item VARCHAR2(32);
55 l_orgn_code VARCHAR2(4);
56 X_from_um mtl_units_of_measure.uom_code%TYPE;
57 X_status VARCHAR2(32);
58 l_return_status VARCHAR2(10);
59 X_rec ing_rec;
60 X_scale_factor NUMBER := 0;
61 X_output_qty NUMBER := 0;
62 X_conv_qty NUMBER := 0;
63 NO_ORGN_CODE EXCEPTION;
64 NO_FORMULA EXCEPTION;
65 NO_ATTRIB_DATA EXCEPTION;
66 BAD_SYS_UOM EXCEPTION;
67 LOAD_DATA_ERR EXCEPTION;
68 LOAD_INGR_ERR EXCEPTION;
69 UOM_CONV_ERR EXCEPTION;
70
71 BEGIN
72 IF (p_organization_id IS NULL) THEN
73 RAISE NO_ORGN_CODE;
74 END IF;
75 IF (p_formula_id IS NULL) THEN
76 RAISE NO_FORMULA;
77 END IF;
78
79 -- NPD Convergence
80
81 GMD_API_GRP.FETCH_PARM_VALUES (P_orgn_id => p_organization_id ,
82 P_parm_name => 'GMD_MASS_UM_TYPE' ,
83 P_parm_value => P_mass_um_type ,
84 X_return_status => l_return_status );
85
86 GMD_API_GRP.FETCH_PARM_VALUES (P_orgn_id => p_organization_id ,
87 P_parm_name => 'GMD_VOLUME_UM_TYPE' ,
88 P_parm_value => P_vol_um_type ,
89 X_return_status => l_return_status );
90
91 /*P_mass_um_type := FND_PROFILE.VALUE('LM$UOM_MASS_TYPE');
92 P_vol_um_type := FND_PROFILE.VALUE('LM$UOM_VOLUME_TYPE');*/
93
94 P_density := FND_PROFILE.VALUE('LM$DENSITY');
95
96 FND_MESSAGE.SET_NAME('FND', 'FND_MESSAGE_TYPE_ERROR');
97 P_error := UPPER(FND_MESSAGE.GET)||' : ';
98 FND_MESSAGE.SET_NAME('FND', 'FND_MESSAGE_TYPE_WARNING');
99 P_warning := UPPER(FND_MESSAGE.GET)||' : ';
100
101 OPEN Cur_std_um(P_mass_um_type);
102 FETCH Cur_std_um INTO P_mass_um;
103 IF (Cur_std_um%NOTFOUND) THEN
104 CLOSE Cur_std_um;
105 RAISE BAD_SYS_UOM;
106 END IF;
107 CLOSE Cur_std_um;
108
109 OPEN Cur_std_um(P_vol_um_type);
110 FETCH Cur_std_um INTO P_vol_um;
111 IF (Cur_std_um%NOTFOUND) THEN
112 CLOSE Cur_std_um;
113 RAISE BAD_SYS_UOM;
114 END IF;
115 CLOSE Cur_std_um;
116
117 load_ingreds(p_formula_id => p_formula_id,
118 x_ing_tab => P_ingred_tab,
119 x_return_status => X_status);
120 IF (X_status <> 'S') THEN
121 RAISE LOAD_INGR_ERR;
122 END IF;
123
124 FOR get_rec IN Cur_prods_byprods LOOP
125 IF (get_rec.detail_uom <> p_rep_um) THEN
126 -- NPD Conv.
127 X_conv_qty := INV_CONVERT.inv_um_convert( item_id => get_rec.inventory_item_id
128 ,precision => 5
129 ,from_quantity => get_rec.qty
130 ,from_unit => get_rec.detail_uom
131 ,to_unit => p_rep_um
132 ,from_name => NULL
133 ,to_name => NULL );
134 IF (X_conv_qty < 0) THEN
135 X_item := get_rec.inventory_item_id;
136 X_from_um := get_rec.detail_uom;
137 RAISE UOM_CONV_ERR;
138 END IF;
139 ELSE
140 X_conv_qty := get_rec.qty;
141 END IF;
142 X_output_qty := X_output_qty + X_conv_qty;
143 END LOOP;
144 IF (X_output_qty > 0) THEN
145 X_scale_factor := ROUND((p_analysis_qty / X_output_qty),4);
146 ELSE
147 X_scale_factor := 1;
148 END IF;
149
150 /* Scale the ingredients and get back the values */
151 IF (X_scale_factor <> 1) THEN
152 scale_table(p_formula_id => p_formula_id,
153 p_orgn_id => p_organization_id,
154 p_scale_factor => X_scale_factor,
155 p_table => P_ingred_tab);
156 END IF;
157
158 /* Loop through ingredients and explode where necessary and populate final table. */
159 FOR i IN 1..P_ingred_tab.count LOOP
160 X_rec := P_ingred_tab(i);
161 P_form_tab(1).formula_id := p_formula_id;
162 check_explosion(p_formula_id => p_formula_id,
163 p_organization_id => p_organization_id,
164 p_laboratory_id => p_laboratory_id,
165 p_explosion_rule => p_explosion_rule,
166 p_rec => X_rec,
167 x_return_status => X_status);
168 IF (X_status <> 'S') THEN
169 RAISE LOAD_DATA_ERR;
170 END IF;
171 P_form_tab.delete;
172 END LOOP;
173
174 /* Calculate the percentages for weights and volumes. */
175 calc_percent(p_orgn_id => p_organization_id, x_return_status => X_status);
176 IF (X_status <> 'S') THEN
177 RAISE LOAD_DATA_ERR;
178 END IF;
179
180 /* Remove any previous analysis data for this formula and organization */
181 DELETE FROM gmd_formula_analysis_dtl
182 WHERE formula_id = p_formula_id
183 AND organization_id = p_organization_id
184 AND laboratory_id = p_laboratory_id;
185
186 DELETE FROM gmd_formula_analysis_hdr
187 WHERE formula_id = p_formula_id
188 AND organization_id = p_organization_id
189 AND laboratory_id = p_laboratory_id;
190
191 /* Insert records into tables. */
192 INSERT INTO gmd_formula_analysis_hdr
193 (formula_id, organization_id, laboratory_id, explosion_rule, created_by, creation_date,
194 last_updated_by, last_update_date, last_update_login, analysis_qty, analysis_hdr_uom)
195 VALUES (p_formula_id, p_organization_id, p_laboratory_id, p_explosion_rule, X_user, SYSDATE,
196 X_user, SYSDATE, X_login_id, p_analysis_qty, p_rep_um);
197
198 FOR i IN 1..P_dtl_tab.COUNT LOOP
199 INSERT INTO gmd_formula_analysis_dtl
200 (formula_id, organization_id, laboratory_id, inventory_item_id, technical_class, technical_sub_class,
201 direct_weight, direct_weight_percent, indirect_weight, indirect_weight_percent,
202 direct_volume, direct_volume_percent, indirect_volume, indirect_volume_percent)
203 VALUES (p_formula_id, p_organization_id, p_laboratory_id, P_dtl_tab(i).inventory_item_id, P_dtl_tab(i).technical_class,
204 P_dtl_tab(i).technical_sub_class, ROUND(P_dtl_tab(i).direct_weight,6),
205 ROUND(P_dtl_tab(i).direct_weight_percent,6), ROUND(P_dtl_tab(i).indirect_weight,6),
206 ROUND(P_dtl_tab(i).indirect_weight_percent,6), ROUND(P_dtl_tab(i).direct_volume,6),
207 ROUND(P_dtl_tab(i).direct_volume_percent,6), ROUND(P_dtl_tab(i).indirect_volume,6),
208 ROUND(P_dtl_tab(i).indirect_volume_percent,6));
209 END LOOP;
210 COMMIT WORK;
211 EXCEPTION
212 WHEN NO_ORGN_CODE THEN
213 FND_MESSAGE.SET_NAME('GMI', 'IC_ORGNCODERR');
214 FND_FILE.PUT(FND_FILE.LOG, P_error||FND_MESSAGE.GET);
215 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
216 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', NULL)) THEN
217 NULL;
218 END IF;
219 WHEN NO_FORMULA THEN
220 FND_MESSAGE.SET_NAME('GMD', 'QC_INVALID_FORMULA');
221 FND_FILE.PUT(FND_FILE.LOG, P_error||FND_MESSAGE.GET);
222 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
223 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', NULL)) THEN
224 NULL;
225 END IF;
226 WHEN BAD_SYS_UOM THEN
227 FND_MESSAGE.SET_NAME('GMD', 'LM_BAD_SYSTEM_UOMS');
228 FND_FILE.PUT(FND_FILE.LOG, P_error||FND_MESSAGE.GET);
229 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
230 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', NULL)) THEN
231 NULL;
232 END IF;
233 WHEN LOAD_DATA_ERR THEN
234 FND_FILE.PUT(FND_FILE.LOG, P_space||P_error||FND_MESSAGE.GET);
235 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
236 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', NULL)) THEN
237 NULL;
238 END IF;
239 WHEN LOAD_INGR_ERR THEN
240 FND_FILE.PUT(FND_FILE.LOG, P_space||P_error||FND_MESSAGE.GET);
241 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
242 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', NULL)) THEN
243 NULL;
244 END IF;
245 WHEN UOM_CONV_ERR THEN
246 FND_MESSAGE.SET_NAME('GMI', 'IC_API_UOM_CONVERSION_ERROR');
247 FND_MESSAGE.SET_TOKEN('ITEM_NO', X_item);
248 FND_MESSAGE.SET_TOKEN('FROM_UOM', X_from_um);
249 FND_MESSAGE.SET_TOKEN('TO_UOM', p_rep_um);
250 FND_FILE.PUT(FND_FILE.LOG, P_space||P_error||FND_MESSAGE.GET);
251 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
252 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', NULL)) THEN
253 NULL;
254 END IF;
255 WHEN OTHERS THEN
256 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
257 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
258 FND_FILE.PUT(FND_FILE.LOG, P_space||P_error||FND_MESSAGE.GET);
259 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
260 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', NULL)) THEN
261 NULL;
262 END IF;
263 END analyze_formula;
264
265 /*======================================================================
266 -- PROCEDURE :
267 -- calc_percent
268 --
269 -- DESCRIPTION:
270 -- This PL/SQL procedure is responsible for calculating the
271 -- weight/volume percentages.
272 --
273 -- REQUIREMENTS
274 --
275 -- SYNOPSIS:
276 -- calc_percent(X_return_status);
277 -- -- HISTORY
278 -- Kishore - Bug No.6051738 - Dt.13-09-2007
279 -- Added validation, not to consider the Item if qty is 0.
280 --===================================================================== */
281 PROCEDURE calc_percent(p_orgn_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2) IS
282 X_tot_wt NUMBER := 0;
283 X_tot_vol NUMBER := 0;
284 X_wts NUMBER := 0;
285 X_vol NUMBER := 0;
286 X_item VARCHAR2(32);
287
288 CURSOR Cur_get_class(V_item_id NUMBER, V_orgn_id NUMBER) IS
289 SELECT mc.segment1 technical_class, mc.segment2 technical_sub_class
290 FROM mtl_category_sets mcs, mtl_default_category_sets mdc, mtl_item_categories mic,
291 mtl_categories_b mc
292 WHERE mdc.functional_area_id = 16
293 AND mic.inventory_item_id = V_item_id
294 AND mic.organization_id = V_orgn_id
295 AND mic.category_set_id = mdc.category_set_id
296 AND mic.category_id = mc.category_id
297 AND mdc.category_set_id = mcs.category_set_id;
298
299 X_class Cur_get_class%ROWTYPE;
300 CURSOR Cur_get_item(V_item_id NUMBER) IS
301 SELECT concatenated_segments
302 FROM mtl_system_items_kfv
303 WHERE inventory_item_id = V_item_id;
304 NO_TECH_CLASS EXCEPTION;
305 NO_WT_OR_VOL EXCEPTION;
306
307 /* Bug No. 6057138 - Start */
308 X_orig_qty Number :=0;
309 CURSOR Cur_get_qty(V_item_id NUMBER, V_formulaid NUMBER, V_line_no NUMBER) IS
310 SELECT qty
311 FROM fm_matl_dtl
312 WHERE inventory_item_id = V_item_id and
313 line_no = V_line_no and
314 formula_id = V_formulaid ;
315 /* Bug No. 6057138 - End */
316
317 BEGIN
318 x_return_status := 'S';
319 /* Determine if any of items could not be converted to mass or volume */
320 /* Also if some items cannot be converted to mass and some to volume stop */
321 FOR i IN 1..P_dtl_tab.count LOOP
322 /* Bug No. 6057138 - Start */
323 OPEN Cur_get_qty(P_dtl_tab(i).inventory_item_id,P_dtl_tab(i).formula_id,P_dtl_tab(i).line_no);
324 FETCH Cur_get_qty INTO X_orig_qty;
325 CLOSE Cur_get_qty;
326 /* Bug No. 6057138 - End */
327 IF ((P_dtl_tab(i).direct_weight + P_dtl_tab(i).indirect_weight) = 0) AND X_orig_qty <> 0 THEN
328 X_wts := X_wts + 1;
329 END IF;
330 IF ((P_dtl_tab(i).direct_volume + P_dtl_tab(i).indirect_volume) = 0) AND X_orig_qty <> 0 THEN
331 X_vol := X_vol + 1;
332 END IF;
333 END LOOP;
334 IF (X_wts > 0 AND X_vol > 0) THEN
335 RAISE NO_WT_OR_VOL;
336 END IF;
337 /* Find the total weights and volumes */
338 FOR i IN 1..P_dtl_tab.count LOOP
339 IF (X_wts = 0) THEN
340 X_tot_wt := X_tot_wt + P_dtl_tab(i).direct_weight + P_dtl_tab(i).indirect_weight;
341 ELSE
342 P_dtl_tab(i).direct_weight := 0;
343 P_dtl_tab(i).indirect_weight := 0;
344 END IF;
345 IF (X_vol = 0) THEN
346 X_tot_vol := X_tot_vol + P_dtl_tab(i).direct_volume + P_dtl_tab(i).indirect_volume;
347 ELSE
348 P_dtl_tab(i).direct_volume := 0;
349 P_dtl_tab(i).indirect_volume := 0;
350 END IF;
351 /* Get technical class and sub class and populate */
352 OPEN Cur_get_class(P_dtl_tab(i).inventory_item_id, p_orgn_id);
353 FETCH Cur_get_class INTO X_class;
354 IF (Cur_get_class%NOTFOUND) THEN
355 CLOSE Cur_get_class;
356 OPEN Cur_get_item(P_dtl_tab(i).inventory_item_id);
357 FETCH Cur_get_item INTO X_item;
358 CLOSE Cur_get_item;
359 RAISE NO_TECH_CLASS;
360 END IF;
361 P_dtl_tab(i).technical_class := X_class.technical_class;
362 P_dtl_tab(i).technical_sub_class := X_class.technical_sub_class;
363 CLOSE Cur_get_class;
364 END LOOP;
365 FOR i IN 1..P_dtl_tab.count LOOP
366 IF (X_wts = 0) THEN
367 P_dtl_tab(i).direct_weight_percent := (P_dtl_tab(i).direct_weight / X_tot_wt) * 100;
368 P_dtl_tab(i).indirect_weight_percent := (P_dtl_tab(i).indirect_weight / X_tot_wt) * 100;
369 ELSE
370 P_dtl_tab(i).direct_weight_percent := 0;
371 P_dtl_tab(i).indirect_weight_percent := 0;
372 END IF;
373 IF (X_vol = 0) THEN
374 P_dtl_tab(i).direct_volume_percent := (P_dtl_tab(i).direct_volume / X_tot_vol) * 100;
375 P_dtl_tab(i).indirect_volume_percent := (P_dtl_tab(i).indirect_volume / X_tot_vol) * 100;
376 ELSE
377 P_dtl_tab(i).direct_volume_percent := 0;
378 P_dtl_tab(i).indirect_volume_percent := 0;
379 END IF;
380 END LOOP;
381 EXCEPTION
382 WHEN NO_TECH_CLASS THEN
383 x_return_status := 'E';
384 FND_MESSAGE.SET_NAME('GMD', 'GMD_NO_TECH_CLASS');
385 FND_MESSAGE.SET_TOKEN('ITEM_NO', X_item);
386 WHEN NO_WT_OR_VOL THEN
387 x_return_status := 'E';
388 FND_MESSAGE.SET_NAME('GMD', 'GMD_NO_MASS_VOL_CONV');
389 WHEN OTHERS THEN
390 x_return_status := 'E';
391 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
392 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
393 END calc_percent;
394
395 /*======================================================================
396 -- PROCEDURE :
397 -- check_explosion
398 --
399 -- DESCRIPTION:
400 -- This PL/SQL procedure is responsible for exploding the
401 -- ingredients if any intermediates are found.
402 --
403 -- REQUIREMENTS
404 --
405 -- SYNOPSIS:
406 -- check_explosion
407 --
408 --===================================================================== */
409 PROCEDURE check_explosion(p_formula_id NUMBER, p_organization_id NUMBER, p_laboratory_id IN NUMBER, p_rec IN OUT NOCOPY ing_rec,
410 p_explosion_rule NUMBER, x_return_status OUT NOCOPY VARCHAR2) IS
411 X_vrules_tab gmd_fetch_validity_rules.recipe_validity_tbl;
412 X_vrules_tab1 gmd_fetch_validity_rules.recipe_validity_tbl;
413 CURSOR Cur_product_qty(V_formula_id NUMBER, V_item_id NUMBER) IS
414 SELECT qty, detail_uom
415 FROM fm_matl_dtl
416 WHERE formula_id = V_formula_id
417 AND line_type = 1
418 AND inventory_item_id = V_item_id;
419 CURSOR Cur_get_formula(V_formula_id NUMBER) IS
420 SELECT formula_no, formula_vers
421 FROM fm_form_mst
422 WHERE formula_id = V_formula_id;
423 CURSOR get_orgn_code IS
424 SELECT organization_code
425 FROM org_access_view
426 WHERE organization_id = p_organization_id;
427
428 X_formula_id NUMBER;
429 X_formula_no VARCHAR2(32);
430 X_formula_vers NUMBER;
431 X_formula_sts VARCHAR2(10);
432 X_cur_form_no VARCHAR2(32);
433 X_cur_form_ver NUMBER;
434 X_expl_fm_id NUMBER;
435 X_row NUMBER := 0;
436 X_found NUMBER := 0;
437 X_prod_qty NUMBER;
438 X_conv_qty NUMBER;
439 X_scale_factor NUMBER;
440 X_item_um mtl_units_of_measure.uom_code%TYPE;
441 X_status VARCHAR2(20);
442 l_orgn_code VARCHAR2(3);
443 X_rec ing_rec;
444 X_ingred_table ing_tab;
445 LOAD_INGR_ERR EXCEPTION;
446 LOAD_DATA_ERR EXCEPTION;
447 UOM_CONV_ERR EXCEPTION;
448
449 BEGIN
450 x_return_status := 'S';
451 IF (p_rec.iaformula_id IS NOT NULL) THEN
452 X_expl_fm_id := p_rec.iaformula_id;
453 OPEN Cur_get_formula(p_rec.iaformula_id);
454 FETCH Cur_get_formula INTO X_formula_no, X_formula_vers;
455 CLOSE Cur_get_formula;
456 END IF;
457
458 IF p_organization_id IS NOT NULL THEN
459 OPEN get_orgn_code;
460 FETCH get_orgn_code INTO l_orgn_code;
461 CLOSE get_orgn_code;
462 END IF;
463
464 IF (X_expl_fm_id IS NULL) THEN
465 /* Get all validity rules based on organization, item and quantity */
466 try_validity_rules(p_item_id => p_rec.item_id,
467 p_organization_id => p_organization_id,
468 p_qty => p_rec.qty,
469 p_uom => p_rec.item_um,
470 X_vr_tbl => X_vrules_tab);
471 /* Get all validity rules based on item and quantity and global */
472 try_validity_rules(p_item_id => p_rec.item_id,
473 p_organization_id => NULL,
474 p_qty => p_rec.qty,
475 p_uom => p_rec.item_um,
476 X_vr_tbl => X_vrules_tab1);
477 /* Populate validity rules into one single table */
478 FOR i IN 1..X_vrules_tab1.count LOOP
479 X_vrules_tab(X_vrules_tab.count + 1) := X_vrules_tab1(i);
480 END LOOP;
481 X_vrules_tab1.delete;
482 /* Remove validity rules that are ON-HOLD or OBSOLETE and are of not Technical and Production Use */
483 FOR i IN 1..X_vrules_tab.count LOOP
484 IF (NOT(X_vrules_tab(i).recipe_use IN (0,4) AND
485 ((X_vrules_tab(i).validity_rule_status BETWEEN 400 AND 499) OR
486 (X_vrules_tab(i).validity_rule_status BETWEEN 700 AND 799) OR
487 (X_vrules_tab(i).validity_rule_status BETWEEN 900 AND 999)))) THEN
488 X_vrules_tab.delete(i);
489 END IF;
490 END LOOP;
491 IF (X_vrules_tab.count > 0) THEN
492 P_vrules_tab := X_vrules_tab;
493 X_found := 0;
494 /* Explosion rule on form */
495 IF (p_explosion_rule = 0) THEN
496 /* Use production Formulas */
497 IF (p_rec.exp_ind = 1) THEN
498 /* Experimental Item try to find a validity rule in Lab for technical use*/
499 get_valid_formula(p_recipe_use => 4,
500 p_vr_status => '4,9',
501 p_status => '4,9',
502 x_formula_id => X_expl_fm_id,
503 x_formula_no => X_formula_no,
504 x_formula_vers => X_formula_vers,
505 x_found => X_found);
506 IF (X_found = 0) THEN
507 X_expl_fm_id := NULL;
508 /* Experimental Item try to find a validity rule in Lab for production use*/
509 get_valid_formula(p_recipe_use => 0,
510 p_vr_status => '4,9',
511 p_status => '4,9',
512 x_formula_id => X_expl_fm_id,
513 x_formula_no => X_formula_no,
514 x_formula_vers => X_formula_vers,
515 x_found => X_found);
516 IF (X_found = 0) THEN
517 X_expl_fm_id := NULL;
518 END IF; /* Experimental Item try to find a validity rule in Lab for production use*/
519 END IF; /* Experimental Item try to find a validity rule in Lab for technical use*/
520 ELSE /* Experimental item check */
521 /* Item is not experimental find validity rule in technical use*/
522 get_valid_formula(p_recipe_use => 4,
523 p_vr_status => '7,9',
524 p_status => '7,9',
525 x_formula_id => X_expl_fm_id,
526 x_formula_no => X_formula_no,
527 x_formula_vers => X_formula_vers,
528 x_found => X_found);
529 IF (X_found = 0) THEN
530 X_expl_fm_id := NULL;
531 /* No formula for Technical use try for production use */
532 get_valid_formula(p_recipe_use => 0,
533 p_vr_status => '7,9',
534 p_status => '7,9',
535 x_formula_id => X_expl_fm_id,
536 x_formula_no => X_formula_no,
537 x_formula_vers => X_formula_vers,
538 x_found => X_found);
539 IF (X_found = 0) THEN
540 X_expl_fm_id := NULL;
541 END IF;
542 END IF;
543 END IF; /* Experimental item check */
544 ELSE /* Explosion rule check */
545 /* Use Laboratory Formulas */
546 /* Find a validity rule in lab and technical use */
547 get_valid_formula(p_recipe_use => 4,
548 p_vr_status => '4,9',
549 p_status => '4,7,9',
550 x_formula_id => X_expl_fm_id,
551 x_formula_no => X_formula_no,
552 x_formula_vers => X_formula_vers,
553 x_found => X_found);
554 IF (X_found = 0) THEN
555 X_expl_fm_id := NULL;
556 /* Find a validity rule in lab and production use */
557 get_valid_formula(p_recipe_use => 0,
558 p_vr_status => '4,9',
559 p_status => '4,7,9',
560 x_formula_id => X_expl_fm_id,
561 x_formula_no => X_formula_no,
562 x_formula_vers => X_formula_vers,
563 x_found => X_found);
564 IF (X_found = 0) THEN
565 X_expl_fm_id := NULL;
566 IF (p_rec.exp_ind <> 1) THEN
567 /* Find validity rule approved for general use in technical use */
568 get_valid_formula(p_recipe_use => 4,
569 p_vr_status => '7,9',
570 p_status => '7,9',
571 x_formula_id => X_expl_fm_id,
572 x_formula_no => X_formula_no,
573 x_formula_vers => X_formula_vers,
574 x_found => X_found);
575 IF (X_found = 0) THEN
576 X_expl_fm_id := NULL;
577 /* Find validity rule approved for general use in production use */
578 get_valid_formula(p_recipe_use => 0,
579 p_vr_status => '7,9',
580 p_status => '7,9',
581 x_formula_id => X_expl_fm_id,
582 x_formula_no => X_formula_no,
583 x_formula_vers => X_formula_vers,
584 x_found => X_found);
585 IF (X_found = 0) THEN
586 X_expl_fm_id := NULL;
587 END IF; /* Find validity rule approved for general use in production use */
588 END IF; /* Find validity rule approved for general use in technical use */
589 END IF; /* Experimental item check */
590 END IF; /* Find a validity rule in lab and production use */
591 END IF; /* Find a validity rule in lab and technical use */
592 END IF; /* explosion rule check */
593 END IF; /* No validity rules */
594 END IF; /* Explosion formula check */
595 X_found := 0;
596 IF (X_expl_fm_id IS NOT NULL) THEN
597 OPEN Cur_get_formula(p_rec.formula_id);
598 FETCH Cur_get_formula INTO X_cur_form_no, X_cur_form_ver;
599 CLOSE Cur_get_formula;
600 /* Determine circular references */
601 FOR i IN 1..P_form_tab.count LOOP
602 IF (P_form_tab(i).formula_id = X_expl_fm_id) THEN
603 FND_MESSAGE.SET_NAME('GMD', 'GMD_CIRCULAR_REFERENCE');
604 FND_MESSAGE.SET_TOKEN('ITEM_NO', p_rec.item_no);
605 FND_MESSAGE.SET_TOKEN('F1', X_cur_form_no);
606 FND_MESSAGE.SET_TOKEN('V1', TO_CHAR(X_cur_form_ver));
607 FND_FILE.PUT(FND_FILE.LOG, P_space||P_warning||FND_MESSAGE.GET);
608 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
609 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', NULL)) THEN
610 NULL;
611 END IF;
612 X_expl_fm_id := NULL;
613 X_found := 1;
614 EXIT;
615 END IF;
616 END LOOP;
617 IF (X_found = 0) THEN
618 P_form_tab(P_form_tab.count + 1).formula_id := p_rec.formula_id;
619 FND_MESSAGE.SET_NAME('GMD', 'GMD_EXPLOSION_FORMULA');
620 FND_MESSAGE.SET_TOKEN('ITEM_NO', p_rec.item_no);
621 FND_MESSAGE.SET_TOKEN('F1', X_cur_form_no);
622 FND_MESSAGE.SET_TOKEN('V1', TO_CHAR(X_cur_form_ver));
623 FND_MESSAGE.SET_TOKEN('F2', X_formula_no);
624 FND_MESSAGE.SET_TOKEN('V2', TO_CHAR(X_formula_vers));
625 FND_FILE.PUT(FND_FILE.LOG, P_space||FND_MESSAGE.GET);
626 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
627 P_space := P_space ||' ';
628 END IF;
629 END IF;
630 X_found := 0;
631 /* Processing over for this ingredient add/update it to final table if it is not being exploded */
632 IF (X_expl_fm_id IS NULL) THEN
633 /* Calculate the equivalent mass and volume qtys */
634 calc_mass_vol_qty(p_rec => p_rec,
635 p_organization_id => p_organization_id,
636 p_laboratory_id => p_laboratory_id,
637 x_return_status => X_status);
638 IF (X_status <> 'S') THEN
639 RAISE LOAD_DATA_ERR;
640 END IF;
641 FOR j IN 1..P_dtl_tab.count LOOP
642 /* Check in final table if this item exists and update direct qtys */
643 IF (p_rec.item_id = P_dtl_tab(j).item_id) THEN
644 IF (p_rec.formula_id = p_formula_id) THEN
645 P_dtl_tab(j).direct_weight := P_dtl_tab(j).direct_weight + p_rec.mass_qty;
646 P_dtl_tab(j).direct_volume := P_dtl_tab(j).direct_volume + p_rec.vol_qty;
647 ELSE
648 P_dtl_tab(j).indirect_weight := P_dtl_tab(j).indirect_weight + p_rec.mass_qty;
649 P_dtl_tab(j).indirect_volume := P_dtl_tab(j).indirect_volume + p_rec.vol_qty;
650 END IF;
651 X_found := 1;
652 EXIT;
653 END IF;
654 END LOOP;
655 IF (X_found = 0) THEN
656 /* Item was not found in the final table add it */
657 X_row := P_dtl_tab.count + 1;
658 P_dtl_tab(X_row).formula_id := p_rec.formula_id;
659 P_dtl_tab(X_row).orgn_code := l_orgn_code;
660 P_dtl_tab(X_row).organization_id := p_organization_id;
661 P_dtl_tab(X_row).inventory_item_id := p_rec.item_id;
662 P_dtl_tab(X_row).technical_class := p_rec.technical_class;
663 P_dtl_tab(X_row).technical_sub_class := p_rec.technical_sub_class;
664 P_dtl_tab(X_row).line_no := p_rec.line_no; /* Added in Bug No.6057138*/
665 IF (p_rec.formula_id = p_formula_id) THEN
666 P_dtl_tab(X_row).direct_weight := p_rec.mass_qty;
667 P_dtl_tab(X_row).direct_volume := p_rec.vol_qty;
668 P_dtl_tab(X_row).indirect_weight := 0;
669 P_dtl_tab(X_row).indirect_volume := 0;
670 ELSE
671 P_dtl_tab(X_row).indirect_weight := p_rec.mass_qty;
672 P_dtl_tab(X_row).indirect_volume := p_rec.vol_qty;
673 P_dtl_tab(X_row).direct_weight := 0;
674 P_dtl_tab(X_row).direct_volume := 0;
675 END IF;
676 END IF;
677 END IF;
678 IF (X_expl_fm_id IS NOT NULL) THEN
679 load_ingreds(p_formula_id => X_expl_fm_id,
680 x_ing_tab => X_ingred_table,
681 x_return_status => X_status);
682 IF (X_status <> 'S') THEN
683 RAISE LOAD_INGR_ERR;
684 END IF;
685 OPEN Cur_product_qty(X_expl_fm_id, p_rec.item_id);
686 FETCH Cur_product_qty INTO X_prod_qty, X_item_um;
687 CLOSE Cur_product_qty;
688 IF (X_item_um <> p_rec.item_um) THEN
689 --X_conv_qty := gmicuom.uom_conversion(p_rec.item_id, 0, X_prod_qty, X_item_um, p_rec.item_um, 0);
690 X_conv_qty := INV_CONVERT.inv_um_convert( item_id => p_rec.item_id
691 ,precision => 5
692 ,from_quantity => X_prod_qty
693 ,from_unit => X_item_um
694 ,to_unit => p_rec.item_um
695 ,from_name => NULL
696 ,to_name => NULL);
697 IF (X_conv_qty < 0) THEN
698 RAISE UOM_CONV_ERR;
699 END IF;
700 ELSE
701 X_conv_qty := X_prod_qty;
702 END IF;
703 X_scale_factor := ROUND(p_rec.qty / X_conv_qty,4);
704 /* Scale the ingredients and get back the values */
705 IF (X_scale_factor <> 1) THEN
706 scale_table(p_formula_id => X_expl_fm_id,
707 p_orgn_id => p_organization_id,
708 p_scale_factor => X_scale_factor,
709 p_table => X_ingred_table);
710 END IF;
711
712 FOR i IN 1..X_ingred_table.count LOOP
713 X_rec := X_ingred_table(i);
714 check_explosion(p_formula_id => p_formula_id,
715 p_organization_id => p_organization_id,
716 p_laboratory_id => p_laboratory_id,
717 p_explosion_rule => p_explosion_rule,
718 p_rec => X_rec,
719 x_return_status => X_status);
720 IF (X_status <> 'S') THEN
721 RAISE LOAD_DATA_ERR;
722 END IF;
723 END LOOP;
724 P_space := SUBSTR(P_space, 1, (LENGTH(P_space) - 2));
725 END IF;
726 OPEN Cur_get_formula(p_rec.formula_id);
727 FETCH Cur_get_formula INTO X_cur_form_no, X_cur_form_ver;
728 CLOSE Cur_get_formula;
729 IF (p_rec.formula_id = p_formula_id) THEN
730 P_space := NULL;
731 END IF;
732 FND_MESSAGE.SET_NAME('GMD', 'GMD_LAST_LEVEL');
733 FND_MESSAGE.SET_TOKEN('ITEM_NO', p_rec.item_no);
734 FND_MESSAGE.SET_TOKEN('F1', X_cur_form_no);
735 FND_MESSAGE.SET_TOKEN('V1', X_cur_form_ver);
736 FND_FILE.PUT(FND_FILE.LOG, P_space||FND_MESSAGE.GET);
737 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
738 EXCEPTION
739 WHEN LOAD_INGR_ERR THEN
740 x_return_status := 'E';
741 WHEN LOAD_DATA_ERR THEN
742 x_return_status := 'E';
743 WHEN UOM_CONV_ERR THEN
744 x_return_status := 'E';
745 FND_MESSAGE.SET_NAME('GMI', 'IC_API_UOM_CONVERSION_ERROR');
746 FND_MESSAGE.SET_TOKEN('ITEM_NO', p_rec.item_no);
747 FND_MESSAGE.SET_TOKEN('FROM_UOM', X_item_um);
748 FND_MESSAGE.SET_TOKEN('TO_UOM', p_rec.item_um);
749 WHEN OTHERS THEN
750 x_return_status := 'E';
751 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
752 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
753 END check_explosion;
754
755 /*======================================================================
756 -- PROCEDURE :
757 -- get_valid_formula
758 --
759 -- DESCRIPTION:
760 -- This PL/SQL procedure is responsible for getting the
761 -- formula which can be used for the explosion.
762 --
763 -- REQUIREMENTS
764 --
765 -- SYNOPSIS:
766 -- get_valid_formula(X_recipe_use, X_status, X_formula_id,
767 -- X_formula_no, X_formula_vers, X_found);
768 --
769 --===================================================================== */
770 PROCEDURE get_valid_formula(p_recipe_use NUMBER, p_vr_status VARCHAR2, p_status VARCHAR2, x_formula_id OUT NOCOPY NUMBER,
771 x_formula_no OUT NOCOPY VARCHAR2, x_formula_vers OUT NOCOPY NUMBER, x_found OUT NOCOPY NUMBER) IS
772 X_formula_rec fm_form_mst%ROWTYPE;
773 X_recipe_rec gmd_recipes%ROWTYPE;
774 BEGIN
775 x_found := 0;
776 FOR i IN 1..P_vrules_tab.count LOOP
777 IF (P_vrules_tab(i).recipe_use = p_recipe_use AND INSTR(p_vr_status,SUBSTR(P_vrules_tab(i).validity_rule_status,1,1)) > 0) THEN
778 get_recipe(p_recipe_id => P_vrules_tab(i).recipe_id,
779 x_recipe_rec => X_recipe_rec);
780 IF (INSTR(p_status,SUBSTR(X_recipe_rec.recipe_status,1,1)) > 0 AND X_recipe_rec.delete_mark = 0) THEN
781 get_formula(p_recipe_id => P_vrules_tab(i).recipe_id,
782 x_form_mst_rec => X_formula_rec);
783 IF (INSTR(p_status,SUBSTR(X_formula_rec.formula_status,1,1)) > 0 AND X_formula_rec.delete_mark = 0) THEN
784 x_found := 1;
785 x_formula_id := X_formula_rec.formula_id;
786 x_formula_no := X_formula_rec.formula_no;
787 x_formula_vers := X_formula_rec.formula_vers;
788 EXIT;
789 END IF;
790 END IF;
791 END IF;
792 END LOOP;
793 END get_valid_formula;
794
795 /*======================================================================
796 -- PROCEDURE :
797 -- scale_table
798 --
799 -- DESCRIPTION:
800 -- This PL/SQL procedure is responsible for scaling the
801 -- ingredient values to the required level.
802 --
803 -- REQUIREMENTS
804 --
805 -- SYNOPSIS:
806 -- scale_table(X_formula_id, X_scale_factor, X_table);
807 --
808 -- HISTORY:
809 -- 15-Sep-2004 Jeff Baird Bug #3890191 Added X_out_tab.
810 --
811 --===================================================================== */
812 PROCEDURE scale_table(p_formula_id NUMBER, p_orgn_id NUMBER, p_scale_factor NUMBER, p_table IN OUT NOCOPY ing_tab) IS
813 X_tab gmd_common_scale.fm_matl_dtl_tab;
814 X_out_tab gmd_common_scale.fm_matl_dtl_tab;
815 -- Bug #3890191 (JKB) Added X_out_tab above.
816 X_status VARCHAR2(10);
817 X_row NUMBER := 0;
818
819 --NPD Conv. Use inventory_item_id and detail_uom instead of item_id and item_um
820 CURSOR Cur_get_lines IS
821 SELECT line_no, line_type, inventory_item_id, qty, detail_uom, scale_type, contribute_yield_ind,
822 scale_multiple, scale_rounding_variance
823 FROM fm_matl_dtl
824 WHERE formula_id = p_formula_id
825 ORDER BY line_type, line_no;
826
827 BEGIN
828 FOR get_rec IN Cur_get_lines LOOP
829 X_row := X_row + 1;
830 X_tab(X_row).line_no := get_rec.line_no;
831 X_tab(X_row).line_type := get_rec.line_type;
832 X_tab(X_row).inventory_item_id := get_rec.inventory_item_id; --NPD Conv.
833 X_tab(X_row).qty := get_rec.qty;
834 X_tab(X_row).detail_uom := get_rec.detail_uom; --NPD Conv.
835 X_tab(X_row).scale_type := get_rec.scale_type;
836 X_tab(X_row).contribute_yield_ind := get_rec.contribute_yield_ind;
837 X_tab(X_row).scale_multiple := get_rec.scale_multiple;
838 X_tab(X_row).scale_rounding_variance := get_rec.scale_rounding_variance;
839 END LOOP;
840
841 gmd_common_scale.scale(p_fm_matl_dtl_tab => X_tab,
842 p_orgn_id => p_orgn_id,
843 p_scale_factor => p_scale_factor,
844 p_primaries => 'OUTPUTS',
845 x_fm_matl_dtl_tab => X_out_tab,
846 x_return_status => X_status);
847 -- Bug #3890191 (JKB) Added X_out_tab above.
848 FOR i IN 1..p_table.count LOOP
849 FOR j IN 1..X_out_tab.count LOOP
850 IF (X_out_tab(j).line_type = -1 AND p_table(i).line_no = X_out_tab(j).line_no) THEN
851 p_table(i).qty := X_out_tab(j).qty;
852 -- Bug #3890191 (JKB) Added X_out_tab above.
853 END IF;
854 END LOOP;
855 END LOOP;
856 END scale_table;
857
858 /*======================================================================
859 -- PROCEDURE :
860 -- try_validity_rules
861 --
862 -- DESCRIPTION:
863 -- This PL/SQL procedure is responsible for getting the
864 -- validity rules depending on the parameters.
865 --
866 -- REQUIREMENTS
867 --
868 -- SYNOPSIS:
869 -- try_validity_rules(X_item_id, X_organization_id, X_qty, X_uom, X_vr_tbl);
870 --
871 --===================================================================== */
872 PROCEDURE try_validity_rules(p_item_id NUMBER, p_organization_id NUMBER,
873 p_qty NUMBER, p_uom VARCHAR2,
874 X_vr_tbl OUT NOCOPY gmd_fetch_validity_rules.recipe_validity_tbl) IS
875 X_status VARCHAR2(10);
876 X_msg_cnt NUMBER;
877 X_msg_dat VARCHAR2(100);
878 X_ret_code NUMBER;
879 BEGIN
880 gmd_fetch_validity_rules.get_validity_rules(p_api_version => 1.0,
881 p_init_msg_list => 'F',
882 p_recipe_id => NULL,
883 p_item_id => p_item_id,
884 p_organization_id => p_organization_id,
885 p_product_qty => p_qty,
886 p_uom => p_uom,
887 p_recipe_use => NULL,
888 p_total_input => NULL,
889 p_total_output => NULL,
890 p_status => NULL,
891 x_return_status => X_status,
892 x_msg_count => X_msg_cnt,
893 x_msg_data => X_msg_dat,
894 x_return_code => X_ret_code,
895 X_recipe_validity_out => X_vr_tbl);
896 END try_validity_rules;
897
898 /*======================================================================
899 -- PROCEDURE :
900 -- load_ingreds
901 --
902 -- DESCRIPTION:
903 -- This PL/SQL procedure is responsible for populating the
904 -- ingredients for a particular formula.
905 --
906 -- REQUIREMENTS
907 --
908 -- SYNOPSIS:
909 -- load_ingreds
910 --
911 --===================================================================== */
912 PROCEDURE load_ingreds(p_formula_id NUMBER, x_ing_tab OUT NOCOPY ing_tab,
913 x_return_status OUT NOCOPY VARCHAR2) IS
914 CURSOR Cur_ingred_items IS
915 SELECT d.inventory_item_id, d.line_no, i.concatenated_segments, d.qty, d.detail_uom,
916 d.iaformula_id, i.eng_item_flag, d.formula_id, d.tpformula_id
917 FROM fm_matl_dtl d, mtl_system_items_kfv i
918 WHERE d.formula_id = p_formula_id
919 AND d.line_type = -1
920 AND i.inventory_item_id = d.inventory_item_id
921 AND d.organization_id = i.organization_id
922 ORDER BY d.line_no;
923 X_item VARCHAR2(32);
924 X_conv_qty NUMBER := 0;
925 X_row NUMBER := 0;
926 BEGIN
927 x_return_status := 'S';
928 FOR get_rec IN Cur_ingred_items LOOP
929 X_row := X_row + 1;
930 x_ing_tab(X_row).item_id := get_rec.inventory_item_id;
931 x_ing_tab(X_row).line_no := get_rec.line_no;
932 x_ing_tab(X_row).item_no := get_rec.concatenated_segments;
933 x_ing_tab(X_row).qty := get_rec.qty;
934 x_ing_tab(X_row).item_um := get_rec.detail_uom;
935 IF get_rec.eng_item_flag = 'Y' THEN
936 x_ing_tab(X_row).exp_ind := 1;
937 ELSE
938 x_ing_tab(X_row).exp_ind := 0;
939 END IF;
940 x_ing_tab(X_row).iaformula_id := get_rec.iaformula_id;
941 x_ing_tab(X_row).formula_id := get_rec.formula_id;
942 x_ing_tab(X_row).tpformula_id := get_rec.tpformula_id;
943 END LOOP;
944 END load_ingreds;
945
946 /*======================================================================
947 -- PROCEDURE :
948 -- calc_mass_vol_qty
949 --
950 -- DESCRIPTION:
951 -- This PL/SQL procedure is responsible for calculating the
952 -- mass and volume qtys in the table passed.
953 --
954 -- REQUIREMENTS
955 --
956 -- SYNOPSIS:
957 -- calc_mass_vol_qty(X_rec, X_orgn_code, X_status);
958 --
959 --===================================================================== */
960 PROCEDURE calc_mass_vol_qty(p_rec IN OUT NOCOPY ing_rec, p_organization_id IN NUMBER,
961 p_laboratory_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2) IS
962
963 CURSOR Cur_get_type(V_um_code VARCHAR2) IS
964 SELECT uom_class
965 FROM mtl_units_of_measure
966 WHERE uom_code = V_um_code;
967
968 X_conv_qty NUMBER;
969 X_density NUMBER;
970 X_item VARCHAR2(32);
971 X_status VARCHAR2(10);
972 X_from_um mtl_units_of_measure.uom_code%TYPE;
973 X_to_um mtl_units_of_measure.uom_code%TYPE;
974 X_um_type mtl_units_of_measure.uom_class%TYPE;
975 UOM_CONV_ERR EXCEPTION;
976 BAD_UOM_TYPE EXCEPTION;
977
978 BEGIN
979 x_return_status := 'S';
980
981 OPEN Cur_get_type(p_rec.item_um);
982 FETCH Cur_get_type INTO X_um_type;
983 CLOSE Cur_get_type;
984
985 get_density(p_rec, p_organization_id, p_laboratory_id, P_density, X_density, X_status);
986 IF (X_status = 'W') THEN
987 FND_FILE.PUT(FND_FILE.LOG, P_space||P_warning||FND_MESSAGE.GET);
988 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
989 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', NULL)) THEN
990 NULL;
991 END IF;
992 ELSIF (X_status <> 'S') THEN
993 x_return_status := 'E';
994 RETURN;
995 END IF;
996 IF (X_um_type = P_mass_um_type) THEN
997 /* Calculate qty in terms of mass UM */
998 IF (p_rec.item_um <> P_mass_um) THEN
999 -- X_conv_qty := gmicuom.uom_conversion(p_rec.item_id, 0, p_rec.qty, p_rec.item_um, P_mass_um, 0);
1000 X_conv_qty := INV_CONVERT.inv_um_convert( item_id => p_rec.item_id
1001 ,precision => 5
1002 ,from_quantity => p_rec.qty
1003 ,from_unit => p_rec.item_um
1004 ,to_unit => P_mass_um
1005 ,from_name => NULL
1006 ,to_name => NULL);
1007 IF (X_conv_qty < 0) THEN
1008 X_item := p_rec.item_no;
1009 X_from_um := p_rec.item_um;
1010 X_to_um := P_mass_um;
1011 RAISE UOM_CONV_ERR;
1012 END IF;
1013 ELSE
1014 X_conv_qty := p_rec.qty;
1015 END IF;
1016 p_rec.mass_qty := X_conv_qty;
1017 IF (NVL(X_density,0) = 0) THEN
1018 -- X_conv_qty := gmicuom.uom_conversion(p_rec.item_id, 0, p_rec.mass_qty, P_mass_um, P_vol_um, 0);
1019 X_conv_qty := INV_CONVERT.inv_um_convert( item_id => p_rec.item_id
1020 ,precision => 5
1021 ,from_quantity => p_rec.mass_qty
1022 ,from_unit => P_mass_um
1023 ,to_unit => P_vol_um
1024 ,from_name => NULL
1025 ,to_name => NULL);
1026 IF (X_conv_qty < 0) THEN
1027 FND_MESSAGE.SET_NAME('GMI', 'IC_API_UOM_CONVERSION_ERROR');
1028 FND_MESSAGE.SET_TOKEN('ITEM_NO', p_rec.item_no);
1029 FND_MESSAGE.SET_TOKEN('FROM_UOM', P_mass_um);
1030 FND_MESSAGE.SET_TOKEN('TO_UOM', P_vol_um);
1031 FND_FILE.PUT(FND_FILE.LOG, P_space||P_warning||FND_MESSAGE.GET);
1032 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1033 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', NULL)) THEN
1034 NULL;
1035 END IF;
1036 p_rec.vol_qty := 0;
1037 ELSE
1038 p_rec.vol_qty := X_conv_qty;
1039 END IF;
1040 ELSE
1041 p_rec.vol_qty := X_conv_qty / X_density;
1042 END IF;
1043 ELSIF (X_um_type = P_vol_um_type) THEN
1044 /* Calculate qty in terms of volume UM */
1045 IF (p_rec.item_um <> P_vol_um) THEN
1046 -- X_conv_qty := gmicuom.uom_conversion(p_rec.item_id, 0, p_rec.qty, p_rec.item_um, P_vol_um, 0);
1047 X_conv_qty := INV_CONVERT.inv_um_convert( item_id => p_rec.item_id
1048 ,precision => 5
1049 ,from_quantity => p_rec.qty
1050 ,from_unit => p_rec.item_um
1051 ,to_unit => P_vol_um
1052 ,from_name => NULL
1053 ,to_name => NULL);
1054 IF (X_conv_qty < 0) THEN
1055 X_item := p_rec.item_no;
1056 X_from_um := p_rec.item_um;
1057 X_to_um := P_vol_um;
1058 RAISE UOM_CONV_ERR;
1059 END IF;
1060 ELSE
1061 X_conv_qty := p_rec.qty;
1062 END IF;
1063 p_rec.vol_qty := X_conv_qty;
1064 IF (NVL(X_density,0) = 0) THEN
1065 -- X_conv_qty := gmicuom.uom_conversion(p_rec.item_id, 0, p_rec.vol_qty, P_vol_um, P_mass_um, 0);
1066 X_conv_qty := INV_CONVERT.inv_um_convert( item_id => p_rec.item_id
1067 ,precision => 5
1068 ,from_quantity => p_rec.vol_qty
1069 ,from_unit => P_vol_um
1070 ,to_unit => P_mass_um
1071 ,from_name => NULL
1072 ,to_name => NULL);
1073 IF (X_conv_qty < 0) THEN
1074
1075 FND_MESSAGE.SET_NAME('GMI', 'IC_API_UOM_CONVERSION_ERROR');
1076 FND_MESSAGE.SET_TOKEN('ITEM_NO', p_rec.item_no);
1077 FND_MESSAGE.SET_TOKEN('FROM_UOM', P_vol_um);
1078 FND_MESSAGE.SET_TOKEN('TO_UOM', P_mass_um);
1079 FND_FILE.PUT(FND_FILE.LOG, P_space||P_warning||FND_MESSAGE.GET);
1080 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1081 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', NULL)) THEN
1082 NULL;
1083 END IF;
1084 p_rec.mass_qty := 0;
1085 ELSE
1086 p_rec.mass_qty := X_conv_qty;
1087 END IF;
1088 ELSE
1089 p_rec.mass_qty := X_conv_qty * X_density;
1090 END IF;
1091 ELSE
1092 X_item := p_rec.item_no;
1093 X_from_um := p_rec.item_um;
1094 RAISE BAD_UOM_TYPE;
1095 END IF;
1096 EXCEPTION
1097 WHEN UOM_CONV_ERR THEN
1098 x_return_status := 'E';
1099 FND_MESSAGE.SET_NAME('GMI', 'IC_API_UOM_CONVERSION_ERROR');
1100 FND_MESSAGE.SET_TOKEN('ITEM_NO', X_item);
1101 FND_MESSAGE.SET_TOKEN('FROM_UOM', X_from_um);
1102 FND_MESSAGE.SET_TOKEN('TO_UOM', X_to_um);
1103 WHEN BAD_UOM_TYPE THEN
1104 x_return_status := 'E';
1105 FND_MESSAGE.SET_NAME('GMD', 'GMD_NON_MASS_VOL');
1106 FND_MESSAGE.SET_TOKEN('ITEM_NO', X_item);
1107 FND_MESSAGE.SET_TOKEN('UM', X_from_um);
1108 WHEN OTHERS THEN
1109 x_return_status := 'E';
1110 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1111 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1112 END calc_mass_vol_qty;
1113
1114 /*======================================================================
1115 -- PROCEDURE :
1116 -- get_formula
1117 --
1118 -- DESCRIPTION:
1119 -- This PL/SQL procedure is responsible for getting the
1120 -- formula for the recipe passed.
1121 --
1122 -- REQUIREMENTS
1123 --
1124 -- SYNOPSIS:
1125 -- get_formula(X_recipe_id, X_formula_id, X_formula_status,
1126 -- X_formula_no, X_formula_vers);
1127 --
1128 --===================================================================== */
1129 PROCEDURE get_formula(p_recipe_id IN NUMBER, x_form_mst_rec OUT NOCOPY fm_form_mst%ROWTYPE) IS
1130 CURSOR Cur_get_formula IS
1131 SELECT *
1132 FROM fm_form_mst
1133 WHERE formula_id = (SELECT formula_id
1134 FROM gmd_recipes
1135 WHERE recipe_id = p_recipe_id);
1136 BEGIN
1137 OPEN Cur_get_formula;
1138 FETCH Cur_get_formula INTO x_form_mst_rec;
1139 CLOSE Cur_get_formula;
1140 END get_formula;
1141
1142 /*======================================================================
1143 -- PROCEDURE :
1144 -- get_recipe
1145 --
1146 -- DESCRIPTION:
1147 -- This PL/SQL procedure is responsible for getting the
1148 -- the recipe details.
1149 --
1150 -- REQUIREMENTS
1151 --
1152 -- SYNOPSIS:
1153 -- get_recipe(X_recipe_id, X_recipe_status,
1154 -- X_recipe_no, X_recipe_vers);
1155 --
1156 --===================================================================== */
1157 PROCEDURE get_recipe(p_recipe_id IN NUMBER, x_recipe_rec OUT NOCOPY gmd_recipes%ROWTYPE) IS
1158 CURSOR Cur_get_recipe IS
1159 SELECT *
1160 FROM gmd_recipes
1161 WHERE recipe_id = p_recipe_id;
1162 BEGIN
1163 OPEN Cur_get_recipe;
1164 FETCH Cur_get_recipe INTO x_recipe_rec;
1165 CLOSE Cur_get_recipe;
1166 END get_recipe;
1167
1168 /*======================================================================
1169 -- PROCEDURE :
1170 -- get_density
1171 --
1172 -- DESCRIPTION:
1173 -- This PL/SQL procedure is responsible for getting the
1174 -- density value for the item and formula.
1175 --
1176 -- REQUIREMENTS
1177 --
1178 -- SYNOPSIS:
1179 -- get_density(X_ing_rec, X_orgn_code, X_tech_parm_name,
1180 -- X_value, X_status);
1181 --
1182 --===================================================================== */
1183 PROCEDURE get_density(p_ing_rec ing_rec, p_organization_id NUMBER, p_laboratory_id NUMBER, p_tech_parm_name VARCHAR2,
1184 x_value OUT NOCOPY NUMBER, x_return_status OUT NOCOPY VARCHAR2) IS
1185 X_temp NUMBER;
1186 X_orgn_id NUMBER;
1187 l_orgn_code VARCHAR2(3);
1188
1189 CURSOR Cur_check_orgn IS
1190 SELECT 1
1191 FROM org_access_view o, gmd_parameters_hdr p
1192 WHERE o.organization_id = p_organization_id
1193 AND o.organization_id = p.organization_id
1194 AND p.lab_ind = 1;
1195
1196 CURSOR Cur_formula_value(vOrgn_id NUMBER) IS
1197 SELECT num_data
1198 FROM lm_item_dat
1199 WHERE organization_id = vOrgn_id
1200 AND tech_parm_name = p_tech_parm_name
1201 AND formula_id = p_ing_rec.tpformula_id
1202 AND inventory_item_id = p_ing_rec.item_id;
1203 CURSOR Cur_item_value(vOrgn_id NUMBER) IS
1204 SELECT num_data
1205 FROM lm_item_dat
1206 WHERE organization_id = vOrgn_id
1207 AND tech_parm_name = p_tech_parm_name
1208 AND inventory_item_id = p_ing_rec.item_id;
1209
1210 CURSOR get_orgn_code IS
1211 SELECT organization_code
1212 FROM org_access_view
1213 WHERE organization_id = p_organization_id;
1214 BAD_DENSITY EXCEPTION;
1215 BEGIN
1216 x_return_status := 'S';
1217
1218 /* Determine if organization is a lab. If not get the default lab type for the user */
1219 OPEN Cur_check_orgn;
1220 FETCH Cur_check_orgn INTO X_temp;
1221 IF (Cur_check_orgn%NOTFOUND) THEN
1222 X_orgn_id := p_laboratory_id; -- FND_PROFILE.VALUE('GEMMS_DEFAULT_LAB_TYPE');
1223 ELSE
1224 X_orgn_id := p_organization_id;
1225 END IF;
1226 CLOSE Cur_check_orgn;
1227
1228 /* Try to get the value using tpformula_id */
1229 OPEN Cur_formula_value(X_orgn_id);
1230 FETCH Cur_formula_value INTO x_value;
1231 IF (Cur_formula_value%NOTFOUND) THEN
1232 -- Try to get the value based on lab and item
1233 OPEN Cur_item_value(X_orgn_id);
1234 FETCH Cur_item_value INTO x_value;
1235 IF (Cur_item_value%NOTFOUND) THEN
1236 CLOSE Cur_item_value;
1237 CLOSE Cur_formula_value;
1238 RAISE BAD_DENSITY;
1239 END IF;
1240 END IF;
1241 CLOSE Cur_item_value;
1242 CLOSE Cur_formula_value;
1243 EXCEPTION
1244 WHEN BAD_DENSITY THEN
1245 OPEN get_orgn_code;
1246 FETCH get_orgn_code INTO l_orgn_code;
1247 CLOSE get_orgn_code;
1248 x_return_status := 'W';
1249 FND_MESSAGE.SET_NAME('GMD', 'GMD_NO_DENSITY');
1250 FND_MESSAGE.SET_TOKEN('ITEM_NO', p_ing_rec.item_no);
1251 FND_MESSAGE.SET_TOKEN('ORGN', l_orgn_code);
1252 END get_density;
1253
1254 END gmd_formula_analysis;