1 PACKAGE BODY GR_EXPLOSIONS AS
2 /*$Header: GRPXPLNB.pls 120.5 2006/05/17 07:32:32 pbamb noship $*/
3 /*
4 **
5 ** The procedure starts by checking the API version and the input variables, then
6 ** deletes all rows for the item in the tables GR_ITEM_CONCENTRATIONS and
7 ** GR_ITEM_CONC_DETAILS.
8 **
9 ** The formula effectivity table is read using the item code passed in. The
10 ** first search is for an effective regulatory formula. If this does not exist an
11 ** effective production formula is looked for. If this does not exist, the program exits
12 ** and reports an effectivity error. NOTE: Planning and costing formulas are ignored.
13 **
14 ** The top level of ingredients for the effective formula are read, converted to the
15 ** system unit of measure based on the primary unit of measure in for the UOM class
16 ** defined in the profile FM_YIELD_TYPE and accumulated to give a theoretical yield for
17 ** the formula.
18 **
19 ** Once the theoretical yield is calculated an iterative process is started.
20 ** The formula is read again, calculating the % of the ingredient against the
21 ** theoretical yield. If the ingredient is an intermediate, or is an ingredient with a
22 ** stand alone explosion, the item is stored for later processing.
23 ** As the % concentration of each ingredient is calculated, the % is accumulated in the
24 ** table GR_ITEM_CONCENTRATIONS and a separate row is written to GR_ITEM_CONC_DETAILS
25 ** for each individual formula detail line that adds into GR_ITEM_CONCENTRATIONS.
26 **
27 */
28 PROCEDURE OPM_410_MSDS_Formula
29 (p_commit IN VARCHAR2,
30 p_init_msg_list IN VARCHAR2,
31 p_validation_level IN NUMBER,
32 p_api_version IN NUMBER,
33 p_organization_id IN NUMBER,
34 p_inventory_item_id IN NUMBER,
35 p_session_id IN NUMBER,
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_msg_count OUT NOCOPY NUMBER,
38 x_msg_data OUT NOCOPY VARCHAR2)
39 IS
40
41 /*
42 ** Datastructures
43 */
44 /*L_EXPLOSION_LIST GR_EXPLOSIONS.t_explosion_list;*/
45
46 /*
47 ** Alpha Variables
48 */
49 L_CODE_BLOCK VARCHAR2(2000);
50 L_API_CALLED VARCHAR2(240);
51 L_ROWID VARCHAR2(18);
52 L_RETURN_STATUS VARCHAR2(1);
53 L_MSG_DATA VARCHAR2(2000);
54 L_COMMIT VARCHAR2(1) := 'F';
55 L_CALLED_BY_FORM VARCHAR2(1) := 'F';
56 L_KEY_EXISTS VARCHAR2(1);
57
58 L_API_NAME CONSTANT VARCHAR2(30) := 'Explode MSDS/Prodn Formula';
59
60 L_SYSTEM_UOM mtl_units_of_measure_tl.uom_code%TYPE;/*sy_uoms_typ.std_um%TYPE GK changes*/
61 L_SYSTEM_UOM_TYPE mtl_uom_classes_vl.uom_class%TYPE;/*sy_uoms_typ.um_type%TYPE;GK Changes*/
62 L_ITEM_CODE gr_item_general.item_code%TYPE;
63 L_ORGN_CODE sy_orgn_mst.orgn_code%TYPE;
64 --L_DEFAULT_ORGN sy_orgn_mst.orgn_code%TYPE;
65 /*
66 ** M Thomas 05-Feb-2002 BUG 1323951 Added the following Declaration of Variables
67 */
68 L_MASS_UOM mtl_units_of_measure_tl.uom_code%TYPE;/*sy_uoms_typ.std_um%TYPE;GK Changes*/
69 L_MASS_UOM_TYPE mtl_uom_classes_vl.uom_class%TYPE;/*sy_uoms_typ.um_type%TYPE;GK CHanges*/
70 /*
71 ** M Thomas 05-Feb-2002 BUG 1323951 End of the Declaration of Variables
72 */
73
74 --<Bug# 4687606 R12 INVCONV PBAMB - replacing profiles FM_YIELD_TYPE and LM$UOM_MASS_TYPE with gmd parameters>
75 l_parameter_name VARCHAR2(20);
76
77 /* M. Grosser 19-Feb-2002 BUG 1323951 - Added variable to build ingredient list */
78 L_REG_ITEM VARCHAR2(2);
79 L_STANDALONE VARCHAR2(2) := 'F';
80
81 /*
82 ** Numeric Variables
83 */
84 L_ORACLE_ERROR NUMBER;
85 L_CURRENT_RECORD NUMBER := 0;
86 L_FORMULA_USE NUMBER(5);
87 L_ITEM_YIELD NUMBER;
88 L_CURRENT_YIELD NUMBER;
89 L_ITEM_PERCENT NUMBER;
90 L_DET_ITEM_PERCENT NUMBER;
91 L_MAXIMUM_RECORD NUMBER := 1;
92 L_CONVERTED_QTY NUMBER;
93 L_API_VERSION CONSTANT NUMBER := 1.0;
94 L_MISSING_ING NUMBER DEFAULT 0;
95
96 /*
97 ** M Thomas 05-Feb-2002 BUG 1323951 Added the following Numeric Variables
98 */
99 L_ITEM_MASS NUMBER;
100 L_CURRENT_MASS NUMBER;
101 L_WT_PERCENT NUMBER;
102 L_INVENTORY_ITEM_ID NUMBER;
103 L_ORGANIZATION_ID NUMBER;
104 /*
105 ** M Thomas 05-Feb-2002 BUG 1323951 End of the changes to Numeric Variables
106 */
107
108
109 /*
110 ** Exceptions
111 */
112 UOM_CONVERSION_ERROR EXCEPTION;
113 INVALID_UOM_ERROR EXCEPTION;
114 FORMULA_SOURCE_ERROR EXCEPTION;
115 NO_EFFECTIVE_FORMULA_ERROR EXCEPTION;
116 INCOMPATIBLE_API_VERSION_ERROR EXCEPTION;
117 ITEM_TO_PRINT_ERROR EXCEPTION;
118 CONCENTRATION_DELETE_ERROR EXCEPTION;
119 ITEM_CONCENTRATION_ERROR EXCEPTION;
120 --<Bug# 4687606 R12 INVCONV PBAMB>
121 EX_GET_GMD_PARAM_EXCEPTION EXCEPTION;
122
123 /*
124 ** Define the cursors needed
125 **
126 ** Effective Formula
127 */
128
129 /* 26-Apr-2001 M. Grosser BUG 1755426 - Modified code to look at recipe and validity rules instead of
130 effectivity table - to support new GMD data model.
131 */
132
133 CURSOR c_get_effective_formula
134 IS
135 SELECT rvr.recipe_use,
136 rvr.inventory_item_id,
137 mst.formula_id,
138 mst.formula_no,
139 mst.formula_vers,
140 rcp.recipe_no,
141 rcp.recipe_version,
142 dtl.qty,
143 dtl.detail_uom item_um, --dtl.item_um
144 dtl.SCALE_ROUNDING_VARIANCE precision
145 FROM fm_form_mst_b mst,
146 gmd_recipe_validity_rules rvr,
147 gmd_status_b sts,
148 gmd_recipes_b rcp,
149 fm_matl_dtl dtl
150 WHERE rvr.validity_rule_status = sts.status_code
151 AND sts.status_type in ('400','700','900')
152 AND rvr.recipe_id = rcp.recipe_id
153 AND mst.formula_id = rcp.formula_id
154 AND dtl.formula_id = rcp.formula_id
155 AND (rvr.end_date IS NULL OR rvr.end_date >= g_current_date)
156 AND rvr.start_date <= g_current_date
157 AND rvr.recipe_use = l_formula_use
158 AND (rvr.organization_id IS NULL OR rvr.organization_id = l_organization_id) --rvr.orgn_code = l_organization_id)
159 AND dtl.line_type = 1
160 AND dtl.inventory_item_id = l_inventory_item_id
161 AND rvr.delete_mark = 0
162 AND mst.delete_mark = 0
163 AND rvr.inventory_item_id = l_inventory_item_id
164 ORDER BY rvr.organization_id asc, rvr.preference asc, sts.status_type desc;
165
166 EffectiveFormulaRecord c_get_effective_formula%ROWTYPE;
167 /*
168 ** Read the formula ingredient detail
169 */
170
171 /* M. Grosser 16-Apr-2001 BUG 1739085 - Added check for contributing to yield to see if item
172 should be included in the contribution percentage calculation.
173 */
174 CURSOR c_get_formula_detail
175 IS
176 SELECT dtl.inventory_item_id,
177 dtl.qty,
178 dtl.detail_uom item_um,
179 mtl.concatenated_segments item_no,
180 dtl.SCALE_ROUNDING_VARIANCE precision,
181 dtl.contribute_yield_ind
182 FROM mtl_system_items_kfv mtl,
183 fm_matl_dtl dtl
184 WHERE dtl.formula_id = EffectiveFormulaRecord.formula_id
185 AND dtl.line_type = -1
186 --AND dtl.organization_id = mtl.organization_id - Bug 5229785 do not join with detail organization since
187 AND mtl.organization_id = l_organization_id
188 AND dtl.inventory_item_id = mtl.inventory_item_id;
189 FormulaDetailRecord c_get_formula_detail%ROWTYPE;
190 /*
191 ** Get the item safety information
192 */
193
194 CURSOR c_get_item_safety
195 IS
196 SELECT ig1.inventory_item_id,
197 ig1.ingredient_flag,
198 ig1.explode_ingredient_flag,
199 ig1.actual_hazard
200 FROM gr_item_explosion_properties ig1
201 WHERE ig1.inventory_item_id = l_inventory_item_id
202 AND ig1.organization_id = l_organization_id;
203 ItemSafetyRecord c_get_item_safety%ROWTYPE;
204
205
206 /* GK - OM Integration Bug# 2286375
207 ** Get the system unit of measure
208 */
209 CURSOR c_get_system_uom (p_uom_type VARCHAR2)
210 IS
211 SELECT ut.uom_code
212 FROM mtl_uom_classes_vl uc, mtl_units_of_measure_tl ut
213 WHERE ut.uom_class = l_system_uom_type
214 AND ut.uom_class = uc.uom_class;
215 SystemUOM c_get_system_uom%ROWTYPE;
216
217 /*
218 ** Get the concentration rows
219 */
220 CURSOR c_get_concentrations
221 IS
222 SELECT product_item_id, ingredient_item_id, concentration_percentage
223 FROM gr_ingredient_concentrations
224 WHERE organization_id = l_organization_id
225 AND product_item_id = l_inventory_item_id;
226 ConcentrationsRecord c_get_concentrations%ROWTYPE;
227
228
229 BEGIN
230 l_code_block := 'Initialize';
231 /*
232 ** Initialize the message list if true
233 */
234 IF FND_API.To_Boolean(p_init_msg_list) THEN
235 FND_MSG_PUB.Initialize;
236 END IF;
237 /* Check the API version passed in matches the
238 ** internal API version.
239 */
240 IF NOT FND_API.Compatible_API_Call
241 (l_api_version,
242 p_api_version,
243 l_api_name,
244 g_pkg_name) THEN
245 RAISE Incompatible_API_Version_Error;
246 END IF;
247 /*
248 ** Set return status to successful
249 */
250 x_return_status := FND_API.G_RET_STS_SUCCESS;
251 /*
252 ** Check the item code has a value
253 */
254 IF (p_inventory_item_id IS NULL OR p_organization_id IS NULL) THEN
255 RAISE Item_To_Print_Error;
256 END IF;
257 /*
258 ** Check the item exists on item general and is a
259 ** production explosion.
260 */
261 /* M. Grosser 19-Feb-2002 BUG 1323951 - Added variable to build ingredient list */
262 l_ingredient_list.delete;
263 g_max_ingred := 0;
264
265 l_inventory_item_id := p_inventory_item_id;
266 l_organization_id := p_organization_id;
267
268 OPEN c_get_item_safety;
269 FETCH c_get_item_safety INTO ItemSafetyRecord;
270 IF c_get_item_safety%NOTFOUND THEN
271 -- CLOSE c_get_item_safety;
272 -- RAISE Item_To_Print_Error;
273 ItemSafetyRecord.inventory_item_id := p_inventory_item_id;
274 ItemSafetyRecord.ingredient_flag := 'N';
275 ItemSafetyRecord.explode_ingredient_flag := 'N';
276 ItemSafetyRecord.actual_hazard := 100;
277
278 END IF;
279 CLOSE c_get_item_safety;
280 /*
281 ** Now clear any rows from item concentrations and item
282 ** concentration details.
283 */
284 /* M. Grosser 19-Feb-2002 BUG 1323951 - Modified to run for standalone */
285 l_return_status := 'S';
286 GR_INGRED_CONC_DETAILS_PKG.Delete_Rows
287 (p_commit,
288 l_called_by_form,
289 p_organization_id,
290 p_inventory_item_id,
291 l_return_status,
292 l_oracle_error,
293 l_msg_data);
294
295 IF l_return_status <> 'S' THEN
296 RAISE Concentration_Delete_Error;
297 END IF;
298
299 l_return_status := 'S';
300 GR_INGRED_CONCENTRATIONS_PKG.Delete_Rows
301 (p_commit,
302 l_called_by_form,
303 p_organization_id,
304 p_inventory_item_id,
305 l_return_status,
306 l_oracle_error,
307 l_msg_data);
308 IF l_return_status <> 'S' THEN
309 RAISE Concentration_Delete_Error;
310 END IF;
311
312 /*
313 ** Get the system standard unit of measure
314 */
315 l_code_block := 'Get the system standard unit of measure';
316
317 --<Bug# 4687606 R12 INVCONV PBAMB - replacing profiles FM_YIELD_TYPE with gmd parameters>
318 GMD_API_GRP.FETCH_PARM_VALUES( p_organization_id,
319 'FM_YIELD_TYPE',
320 l_system_uom_type,
321 l_return_status);
322 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
323 l_parameter_name := 'FM_YIELD_TYPE';
324 RAISE EX_GET_GMD_PARAM_EXCEPTION;
325 END IF;
326
327 g_user_id := FND_GLOBAL.USER_ID;
328 -- l_default_orgn := FND_PROFILE.Value('GR_ORGN_DEFAULT');
329
330 /*
331 ** M. Thomas 05-Feb-2002 B1323951 Added the Input Parameter to the cursor c_get_system_uom
332 */
333
334 OPEN c_get_system_uom(l_system_uom_type);
335 /*
336 ** M. Thomas 05-Feb-2002 B1323951 End of the code changes to the cursor c_get_system_uom
337 */
338
339 FETCH c_get_system_uom INTO SystemUOM;
340 IF c_get_system_uom%NOTFOUND THEN
341 CLOSE c_get_system_uom;
342 RAISE Invalid_UOM_Error;
343 END IF;
344 /*l_system_uom := SystemUOM.std_um;GK CHANGES*/
345 l_system_uom := SystemUOM.uom_code;
346 CLOSE c_get_system_uom;
347
348 /*
349 ** M. Thomas 05-Feb-2002 B1323951 Added the following code to get the value for the System Mass
350 ** UOM Type
351 */
352
353 /* Get the value of the system mass uom type */
354 --<Bug# 4687606 R12 INVCONV PBAMB - replacing profiles LM$UOM_MASS_TYPE with gmd parameter>
355 GMD_API_GRP.FETCH_PARM_VALUES( p_organization_id,
356 'GMD_MASS_UM_TYPE',
357 l_mass_uom_type,
358 l_return_status);
359 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
360 l_parameter_name := 'GMD_MASS_UM_TYPE';
361 RAISE EX_GET_GMD_PARAM_EXCEPTION;
362 END IF;
363
364 /* If the mass uom type is not the same as the yield uom type, get the base mass uom */
365 IF l_mass_uom_type <> l_system_uom_type THEN
366 IF l_mass_uom_type IS NULL THEN
367 RAISE Invalid_UOM_Error;
368 END IF;
369
370 OPEN c_get_system_uom(l_mass_uom_type);
371 FETCH c_get_system_uom INTO SystemUOM;
372 IF c_get_system_uom%NOTFOUND THEN
373 CLOSE c_get_system_uom;
374 RAISE Invalid_UOM_Error;
375 END IF;
376 /*GK Changes B2286375*/
377 l_mass_uom := SystemUOM.uom_code;
378 CLOSE c_get_system_uom;
379 ELSE
380 l_mass_uom := l_system_uom;
381 END IF;
382
383 /*
384 ** M. Thomas 05-Feb-2002 B1323951 End of the code changes, to get the value for the System Mass
385 ** UOM Type
386 */
387
388
389 /*
390 ** Get the effective formula and calculate the yield
391 ** by summing the total of the top level ingredients
392 ** back to the system standard unit of measure.
393 */
394 l_code_block := 'Calculate the yield';
395 l_explosion_list(1).organization_id := p_organization_id;
396 l_explosion_list(1).inventory_item_id := p_inventory_item_id;
397 l_explosion_list(1).parent_formula := 0;
398 l_explosion_list(1).quantity := 100;
399 l_item_yield := 0;
400 l_current_record := l_current_record + 1;
401
402 WHILE l_maximum_record >= l_current_record LOOP
403 l_code_block := 'Search for the MSDS Formula';
404 l_current_yield := 0;
405 l_formula_use := 3;
406 l_inventory_item_id := l_explosion_list(l_current_record).inventory_item_id;
407
408 FND_FILE.PUT(FND_FILE.LOG,'Processing item:' || l_inventory_item_id);
409 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
410
411 IF c_get_item_safety%ISOPEN THEN
412 CLOSE c_get_item_safety;
413 END IF;
414
415 OPEN c_get_item_safety;
416 FETCH c_get_item_safety INTO ItemSafetyRecord;
417 IF c_get_item_safety%NOTFOUND THEN
418 -- FND_FILE.PUT(FND_FILE.LOG,' No safety information for:' || l_inventory_item_id);
419 -- FND_FILE.NEW_LINE(FND_FILE.LOG,1);
420 ItemSafetyRecord.inventory_item_id := l_inventory_item_id;
421 ItemSafetyRecord.ingredient_flag := 'Y';
422 ItemSafetyRecord.explode_ingredient_flag := 'N';
423 ItemSafetyRecord.actual_hazard := 100;
424 END IF;
425
426 CLOSE c_get_item_safety;
427
428
429 /*
430 ** Check the cursor is actually closed before trying to open
431 */
432
433 IF c_get_effective_formula%ISOPEN THEN
434 CLOSE c_get_effective_formula;
435 END IF;
436 /* Fix for B1323983 */
437 l_organization_id := p_organization_id; --l_default_orgn;
438 l_code_block := 'Trying for Regulatory Formula, for default organization '||l_orgn_code;
439 FND_FILE.PUT(FND_FILE.LOG, l_code_block);
440 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
441 OPEN c_get_effective_formula;
442 FETCH c_get_effective_formula INTO EffectiveFormulaRecord;
443 IF c_get_effective_formula%NOTFOUND THEN
444 CLOSE c_get_effective_formula;
445 l_code_block := 'No Regulatory Formula for default orgn, trying Regulatory formula without default ';
446 l_organization_id := NULL;
447 FND_FILE.PUT(FND_FILE.LOG, l_code_block);
448 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
449 OPEN c_get_effective_formula;
450 FETCH c_get_effective_formula INTO EffectiveFormulaRecord;
451 IF c_get_effective_formula%NOTFOUND THEN
452 CLOSE c_get_effective_formula;
453 l_code_block := 'No Regulatory Formula, trying Production formula for default organization '||l_organization_id;
454 l_organization_id := p_organization_id;
455 l_formula_use := 0;
456 FND_FILE.PUT(FND_FILE.LOG, l_code_block);
457 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
458 OPEN c_get_effective_formula;
459 FETCH c_get_effective_formula INTO EffectiveFormulaRecord;
460 IF c_get_effective_formula%NOTFOUND THEN
461 CLOSE c_get_effective_formula;
462 l_code_block := 'No production formula with default organization, trying Production formula without';
463 FND_FILE.PUT(FND_FILE.LOG, l_code_block);
464 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
465 l_organization_id := NULL;
466 OPEN c_get_effective_formula;
467 FETCH c_get_effective_formula INTO EffectiveFormulaRecord;
468 IF c_get_effective_formula%NOTFOUND THEN
469 CLOSE c_get_effective_formula;
470 RAISE No_Effective_Formula_Error;
471 END IF;
472 END IF;
473 END IF;
474 END IF;
475 CLOSE c_get_effective_formula;
476
477 g_formula_no := EffectiveFormulaRecord.formula_no;
478 g_formula_vers := EffectiveFormulaRecord.formula_vers;
479 g_recipe_no := EffectiveFormulaRecord.recipe_no;
480 g_recipe_vers := EffectiveFormulaRecord.recipe_version;
481
482 l_explosion_list(l_current_record).current_formula := EffectiveFormulaRecord.formula_id;
483 /*
484 ** Check the detail cursor is closed before trying to open
485 ** then read the top level and calculate the yield for the
486 ** formula.
487 */
488 l_code_block := 'Read the top level of detail';
489 FND_FILE.PUT(FND_FILE.LOG,'Calculating yield');
490 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
491 IF c_get_formula_detail%ISOPEN THEN
492 CLOSE c_get_formula_detail;
493 END IF;
494
495 OPEN c_get_formula_detail;
496 FETCH c_get_formula_detail INTO FormulaDetailRecord;
497 IF c_get_formula_detail%FOUND THEN
498 WHILE c_get_formula_detail%FOUND LOOP
499 IF FormulaDetailRecord.contribute_yield_ind <> 'N' THEN
500 IF l_system_uom <> FormulaDetailRecord.item_um THEN
501 l_code_block := 'Converting ingredient uom';
502 l_converted_qty := INV_CONVERT.inv_um_convert
503 (item_id => FormulaDetailRecord.inventory_item_id,
504 lot_number => NULL,
505 organization_id => p_organization_id,
506 precision => FormulaDetailRecord.precision,
507 from_quantity => FormulaDetailRecord.qty,
508 from_unit => FormulaDetailRecord.item_um,
509 to_unit => l_system_uom,
510 from_name => NULL,
511 to_name => NULL);
512 IF l_converted_qty < 0 THEN
513 RAISE UOM_Conversion_Error;
514 ELSE
515 l_converted_qty := ROUND(l_converted_qty, 9);
516 END IF;
517 ELSE
518 l_converted_qty := FormulaDetailRecord.qty;
519 END IF; -- Item um equal to system um
520
521 IF l_current_record = 1 THEN
522 l_item_yield := l_item_yield + l_converted_qty;
523 ELSE
524 l_current_yield := l_current_yield + l_converted_qty;
525 END IF;
526
527 /* If the yield type uom and the mass type uom are the same */
528 IF l_mass_uom = l_system_uom THEN
529 l_item_mass := l_item_yield;
530 l_current_mass := l_current_yield;
531 /* If they are not the same */
532 ELSE
533 /* The mass type uom is not equal to the item detail uom, perform conversion */
534 IF l_mass_uom <> FormulaDetailRecord.item_um THEN
535 l_code_block := 'Converting ingredient uom';
536 l_converted_qty := INV_CONVERT.inv_um_convert
537 (item_id => FormulaDetailRecord.inventory_item_id,
538 lot_number => NULL,
539 organization_id => p_organization_id,
540 precision => FormulaDetailRecord.precision,
541 from_quantity => FormulaDetailRecord.qty,
542 from_unit => FormulaDetailRecord.item_um,
543 to_unit => l_mass_uom,
544 from_name => NULL,
545 to_name => NULL);
546 IF l_converted_qty < 0 THEN
547 RAISE UOM_Conversion_Error;
548 ELSE
549 l_converted_qty := ROUND(l_converted_qty, 9);
550 END IF;
551 ELSE
552 /* The mass type uom is the same as the item detail uom */
553 l_converted_qty := FormulaDetailRecord.qty;
554 END IF;
555 IF l_current_record = 1 THEN
556 l_item_mass := l_item_mass + l_converted_qty;
557 ELSE
558 l_current_mass := l_current_mass + l_converted_qty;
559 END IF;
560 END IF; -- uoms the same
561 END IF; /* Contributes to yield */
562 FETCH c_get_formula_detail INTO FormulaDetailRecord;
563 END LOOP;
564 CLOSE c_get_formula_detail;
565 END IF;
566
567 FND_FILE.PUT(FND_FILE.LOG,'Yield is: ' || TO_CHAR(l_item_yield));
568 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
569 /*
570 ** Yield has been calculated, now go back and read all levels of
571 ** the formula to get the ingredient concentrations.
572 */
573 IF c_get_formula_detail%ISOPEN THEN
574 CLOSE c_get_formula_detail;
575 END IF;
576 OPEN c_get_formula_detail;
577 FETCH c_get_formula_detail INTO FormulaDetailRecord;
578 IF c_get_formula_detail%FOUND THEN
579 WHILE c_get_formula_detail%FOUND LOOP
580
581 /* M. Grosser 16-Apr-2001 BUG 1739085 - Added check for contributing to yield
582 to see if item should be included in the contribution
583 percentage calculation.
584 */
585 IF FormulaDetailRecord.contribute_yield_ind <> 'N' THEN
586 IF c_get_item_safety%ISOPEN THEN
587 CLOSE c_get_item_safety;
588 END IF;
589
590 l_inventory_item_id := FormulaDetailRecord.inventory_item_id;
591 FND_FILE.PUT(FND_FILE.LOG,' Processing ingredient: ' || l_inventory_item_id);
592 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
593
594 OPEN c_get_item_safety;
595 FETCH c_get_item_safety INTO ItemSafetyRecord;
596 IF c_get_item_safety%NOTFOUND THEN
597 -- CLOSE c_get_item_safety;
598 -- l_missing_ing := l_missing_ing + 1;
599 -- l_missing_ing_list(l_missing_ing) := l_item_code;
600 -- FND_FILE.PUT(FND_FILE.LOG,' No item safety for ' || l_inventory_item_id);
601 -- FND_FILE.NEW_LINE(FND_FILE.LOG,1);
602 ItemSafetyRecord.inventory_item_id := l_inventory_item_id;
603 ItemSafetyRecord.ingredient_flag := 'Y';
604 ItemSafetyRecord.explode_ingredient_flag := 'N';
605 ItemSafetyRecord.actual_hazard := 100;
606 END IF; /* If found in item safety */
607
608 IF l_system_uom <> FormulaDetailRecord.item_um THEN
609 l_converted_qty := INV_CONVERT.inv_um_convert
610 (item_id => FormulaDetailRecord.inventory_item_id,
611 lot_number => NULL,
612 organization_id => p_organization_id,
613 precision => FormulaDetailRecord.precision,
614 from_quantity => FormulaDetailRecord.qty,
615 from_unit => FormulaDetailRecord.item_um,
616 to_unit => l_system_uom,
617 from_name => NULL,
618 to_name => NULL);
619 IF l_converted_qty < 0 THEN
620 RAISE UOM_Conversion_Error;
621 END IF;
622 ELSE
623 l_converted_qty := FormulaDetailRecord.qty;
624 END IF;
625 /*
626 ** Calculate the percentage concentration of this line
627 */
628 IF l_item_yield = 0 THEN
629 l_item_yield := 100;
630 END IF;
631
632 IF l_current_yield = 0 THEN
633 l_current_yield := 100;
634 END IF;
635
636 IF l_current_record = 1 THEN
637 l_item_percent := (l_converted_qty / l_item_yield) * 100;
638 ELSE
639 l_item_percent := (((l_converted_qty / l_current_yield) * 100) / 100) *
640 l_explosion_list(l_current_record).quantity;
641 END IF;
642
643 /*
644 ** M. Thomas 05-Feb-2002 BUG 1323951 - Added code to calculate the Weight Percentage
645 */
646
647 /* If the yield type uom and the mass type uom are the same */
648 IF l_mass_uom = l_system_uom THEN
649 l_wt_percent := l_item_percent;
650 /* If they are not the same */
651 ELSE
652 /* The mass type uom is not equal to the item detail uom, perform conversion */
653 IF l_mass_uom <> FormulaDetailRecord.item_um THEN
654 l_code_block := 'Converting ingredient uom';
655 l_converted_qty := INV_CONVERT.inv_um_convert
656 (item_id => FormulaDetailRecord.inventory_item_id,
657 lot_number => NULL,
658 organization_id => p_organization_id,
659 precision => FormulaDetailRecord.precision,
660 from_quantity => FormulaDetailRecord.qty,
661 from_unit => FormulaDetailRecord.item_um,
662 to_unit => l_system_uom,
663 from_name => NULL,
664 to_name => NULL);
665 IF l_converted_qty < 0 THEN
666 RAISE UOM_Conversion_Error;
667 END IF;
668 ELSE
669 /* The mass type uom is the same as the item detail uom */
670 l_converted_qty := FormulaDetailRecord.qty;
671 END IF;
672
673 IF l_current_record = 1 THEN
674 l_wt_percent := (l_converted_qty / l_item_mass) * 100;
675 ELSE
676 l_wt_percent := (((l_converted_qty / l_current_mass) * 100) / 100) *
677 l_explosion_list(l_current_record).weight_pct;
678 END IF;
679 END IF;
680
681 /*
682 ** Now take the actual hazard into account
683 */
684 IF ItemSafetyRecord.ingredient_flag = 'Y' THEN
685 IF ItemSafetyRecord.actual_hazard > 0 AND
686 ItemSafetyRecord.actual_hazard < 100 THEN
687 l_item_percent := l_item_percent * (ItemSafetyRecord.actual_hazard / 100);
688 END IF;
689 END IF;
690 /*
691 ** If not an ingredient, or ingredient is to be exploded then
692 ** add to the work array, otherwise update the item concentration
693 ** tables.
694 */
695 /* M. Grosser 07-Mar-2002 BUG 1323951 - During testing found that code went into a loop if item is
696 not marked as an ingredient yet formula source says no formula. Modified
697 code to treat this item as an ingredient.
698 */
699 IF ((ItemSafetyRecord.ingredient_flag = 'N') OR
700 (ItemSafetyRecord.ingredient_flag = 'Y' AND
701 ItemSafetyRecord.explode_ingredient_flag = 'Y')) AND
702 (NOT check_circular_reference(p_organization_id, l_inventory_item_id, EffectiveFormulaRecord.formula_id, l_maximum_record)) THEN
703 l_code_block := ' Updating explosion plsql table';
704 l_maximum_record := l_maximum_record + 1;
705 l_explosion_list(l_maximum_record).organization_id := p_organization_id;
706 l_explosion_list(l_maximum_record).inventory_item_id := l_inventory_item_id;
707 l_explosion_list(l_maximum_record).quantity := l_item_percent;
708 l_explosion_list(l_maximum_record).weight_pct := l_wt_percent;
709 l_explosion_list(l_maximum_record).parent_formula := EffectiveFormulaRecord.formula_id;
710 FND_FILE.PUT(FND_FILE.LOG,l_code_block);
711 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
712 ELSE
713 add_to_ingredient_list(p_organization_id, l_inventory_item_id, l_item_percent, l_wt_percent);
714 IF l_standalone = 'F' THEN
715 process_concentrations
716 (p_organization_id,
717 p_inventory_item_id,
718 l_explosion_list(l_current_record).inventory_item_id,
719 ItemSafetyRecord.inventory_item_id,
720 ROUND(l_item_percent, 9),
721 l_current_record,
722 FormulaDetailRecord.item_um,
723 x_msg_count,
724 l_msg_data,
725 l_return_status);
726 END IF;
727 END IF; -- Add to concentrations tables
728 CLOSE c_get_item_safety;
729 -- ELSE /* NOT a regulatory item */
730 -- add_to_ingredient_list(p_organization_id, l_inventory_item_id, l_item_percent, l_wt_percent);
731 -- END IF; /* If this is a regulatory item */
732 END IF; /* If contributes to yield */
733 FETCH c_get_formula_detail INTO FormulaDetailRecord;
734 END LOOP;
735 END IF;
736 l_current_record := l_current_record + 1;
737 END LOOP;
738 EXCEPTION
739
740 WHEN Incompatible_API_Version_Error THEN
741 x_return_status := FND_API.G_RET_STS_ERROR;
742 FND_MESSAGE.SET_NAME('GR',
743 'GR_API_VERSION_ERROR');
744 FND_MESSAGE.SET_TOKEN('VERSION',
745 p_api_version,
746 FALSE);
747 X_msg_data := FND_MESSAGE.GET;
748 FND_FILE.PUT(FND_FILE.LOG,'API version error');
749 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
750
751 WHEN Item_To_Print_Error THEN
752 x_return_status := FND_API.G_RET_STS_ERROR;
753 FND_MESSAGE.SET_NAME('GR',
754 'GR_PRINT_ITEM_NULL');
755 X_msg_data := FND_MESSAGE.GET;
756 FND_FILE.PUT(FND_FILE.LOG,'Item code to explode is null');
757 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
758
759 WHEN Invalid_UOM_Error THEN
760 x_return_status := FND_API.G_RET_STS_ERROR;
761 FND_MESSAGE.SET_NAME('GR',
762 'GR_INVALID_UOM');
763 FND_MESSAGE.SET_TOKEN('UOM',
764 'System UOM',
765 FALSE);
766 X_msg_data := FND_MESSAGE.GET;
767 FND_FILE.PUT(FND_FILE.LOG,'Invalid system unit of measure');
768 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
769
770 WHEN No_Effective_Formula_Error THEN
771 x_return_status := FND_API.G_RET_STS_ERROR;
772 FND_MESSAGE.SET_NAME('GR',
773 'GR_NO_EFFECTIVE_FORMULA');
774 FND_MESSAGE.SET_TOKEN('ITEM',
775 l_item_code,
776 FALSE);
777 X_msg_data := FND_MESSAGE.GET;
778 FND_FILE.PUT(FND_FILE.LOG,'No effective formula for ' || l_item_code);
779 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
780
781 WHEN Item_Concentration_Error THEN
782 x_return_status := FND_API.G_RET_STS_ERROR;
783 FND_MESSAGE.SET_NAME('GR',
784 'GR_ITEM_CONCENTRATION');
785 FND_MESSAGE.SET_TOKEN('CODE',
786 l_item_code,
787 FALSE);
788 X_msg_data := FND_MESSAGE.GET;
789 FND_FILE.PUT(FND_FILE.LOG,'Error finding concentration details for ' || l_item_code);
790 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
791
792 WHEN Concentration_Delete_Error THEN
793 x_return_status := FND_API.G_RET_STS_ERROR;
794 FND_MESSAGE.SET_NAME('GR',
795 'GR_UNEXPECTED_ERROR');
796 FND_MESSAGE.SET_TOKEN('TEXT',
797 l_msg_data||sqlerrm,
798 FALSE);
799 X_msg_data := FND_MESSAGE.GET;
800 FND_FILE.PUT(FND_FILE.LOG,'Error clearing concentration detail tables');
801 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
802
803 WHEN UOM_CONVERSION_ERROR THEN
804 x_return_status := FND_API.G_RET_STS_ERROR;
805 FND_MESSAGE.SET_NAME('GMI',
806 'IC_API_UOM_CONVERSION_ERROR');
807 FND_MESSAGE.SET_TOKEN('FROM_UOM',
808 FormulaDetailRecord.item_um,
809 FALSE);
810 FND_MESSAGE.SET_TOKEN('TO_UOM',
811 l_system_uom,
812 FALSE);
813 FND_MESSAGE.SET_TOKEN('ITEM_NO',
814 FormulaDetailRecord.item_no,
815 FALSE);
816 X_msg_data := FND_MESSAGE.GET;
817 --<Bug# 4687606 R12 INVCONV PBAMB>
818 WHEN EX_GET_GMD_PARAM_EXCEPTION THEN
819 x_return_status := FND_API.G_RET_STS_ERROR;
820 fnd_message.set_name ('GMD', 'GMD_PARM_NOT_FOUND');
821 X_msg_data := FND_MESSAGE.GET;
822
823 FND_FILE.PUT(FND_FILE.LOG,'Error finding gmd_parameter value for ' || l_parameter_name);
824 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
825
826
827 WHEN OTHERS THEN
828 l_oracle_error := SQLCODE;
829 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
830 FND_MESSAGE.SET_NAME('GR',
831 'GR_UNEXPECTED_ERROR');
832 FND_MESSAGE.SET_TOKEN('TEXT',
833 l_code_block||sqlerrm,
834 FALSE);
835 X_msg_data := FND_MESSAGE.GET;
836 FND_FILE.PUT(FND_FILE.LOG,' Others '||sqlerrm);
837 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
838
839 END OPM_410_MSDS_Formula;
840 /*
841 **
842 ** Lab formula explosions will be supported in Release 12.0
843 **
844 */
845 PROCEDURE OPM_410_Lab_Formula
846 (p_commit IN VARCHAR2,
847 p_init_msg_list IN VARCHAR2,
848 p_validation_level IN NUMBER,
849 p_api_version IN NUMBER,
850 p_organization_id IN NUMBER,
851 p_inventory_item_id IN NUMBER,
852 p_session_id IN NUMBER,
853 x_return_status OUT NOCOPY VARCHAR2,
854 x_msg_count OUT NOCOPY NUMBER,
855 x_msg_data OUT NOCOPY VARCHAR2)
856 IS
857
858 /*
859 ** Alpha Variables
860 */
861 L_CODE_BLOCK VARCHAR2(2000);
862
863
864 /*
865 ** Numeric Variables
866 */
867 L_ORACLE_ERROR NUMBER;
868
869 BEGIN
870
871 l_code_block := NULL;
872
873 EXCEPTION
874
875 WHEN OTHERS THEN
876 l_oracle_error := SQLCODE;
877 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
878 FND_MESSAGE.SET_NAME('GR',
879 'GR_UNEXPECTED_ERROR');
880 FND_MESSAGE.SET_TOKEN('TEXT',
881 l_code_block||sqlerrm,
882 FALSE);
883 APP_EXCEPTION.Raise_Exception;
884
885 END OPM_410_Lab_Formula;
886 /*
887 **
888 ** This procedure calls the OPM_410_MSDS_Formula procedure. This is
889 ** because the data model between OPM 4.10 and 11i did not change in
890 ** any way that would impact the formula explosion calculations.
891 **
892 */
893 PROCEDURE OPM_11i_MSDS_Formula
894 (p_commit IN VARCHAR2,
895 p_init_msg_list IN VARCHAR2,
896 p_validation_level IN NUMBER,
897 p_api_version IN NUMBER,
898 p_organization_id IN NUMBER,
899 p_inventory_item_id IN NUMBER,
900 p_session_id IN NUMBER,
901 x_return_status OUT NOCOPY VARCHAR2,
902 x_msg_count OUT NOCOPY NUMBER,
903 x_msg_data OUT NOCOPY VARCHAR2)
904 IS
905
906 /*
907 ** Alpha Variables
908 */
909 L_CODE_BLOCK VARCHAR2(2000);
910 L_RETURN_STATUS VARCHAR2(1);
911
912 /*
913 ** Numeric Variables
914 */
915 L_ORACLE_ERROR NUMBER;
916
917 BEGIN
918
919 l_return_status := 'S';
920
921 OPM_410_MSDS_Formula
922 (p_commit,
923 p_init_msg_list,
924 p_validation_level,
925 p_api_version,
926 p_organization_id,
927 p_inventory_item_id,
928 p_session_id,
929 l_return_status,
930 x_msg_count,
931 x_msg_data);
932
933 IF l_return_status <> 'S' THEN
934 X_return_status := l_return_status;
935 END IF;
936
937 END OPM_11i_MSDS_Formula;
938 /*
939 **
940 ** Lab formula explosions will be supported in Release 12.0
941 **
942 */
943 PROCEDURE OPM_11i_Lab_Formula
944 (p_commit IN VARCHAR2,
945 p_init_msg_list IN VARCHAR2,
946 p_validation_level IN NUMBER,
947 p_api_version IN NUMBER,
948 p_organization_id IN NUMBER,
949 p_inventory_item_id IN NUMBER,
950 p_session_id IN NUMBER,
951 x_return_status OUT NOCOPY VARCHAR2,
952 x_msg_count OUT NOCOPY NUMBER,
953 x_msg_data OUT NOCOPY VARCHAR2)
954 IS
955
956 /*
957 ** Alpha Variables
958 */
959 L_CODE_BLOCK VARCHAR2(2000);
960
961 /*
962 ** Numeric Variables
963 */
964 L_ORACLE_ERROR NUMBER;
965
966 BEGIN
967
968 l_code_block := NULL;
969
970 EXCEPTION
971
972 WHEN OTHERS THEN
973 l_oracle_error := SQLCODE;
974 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
975 FND_MESSAGE.SET_NAME('GR',
976 'GR_UNEXPECTED_ERROR');
977 FND_MESSAGE.SET_TOKEN('TEXT',
978 l_code_block||sqlerrm,
979 FALSE);
980 APP_EXCEPTION.Raise_Exception;
981
982 END OPM_11i_Lab_Formula;
983 /*
984 ** This procedure is called from the EXCEPTION handlers
985 ** in other procedures. It is passed the message code,
986 ** token name and token value.
987 **
988 ** The procedure will then process the error message into
989 ** the message stack and then return to the calling routine.
990 ** The procedure assumes all messages used are in the
991 ** application id 'GR'.
992 **
993 */
994 PROCEDURE Handle_Error_Messages
995 (p_called_by_form IN VARCHAR2,
996 p_message_code IN VARCHAR2,
997 p_token_name IN VARCHAR2,
998 p_token_value IN VARCHAR2,
999 x_msg_count IN OUT NOCOPY NUMBER,
1000 x_msg_data IN OUT NOCOPY VARCHAR2,
1001 x_return_status OUT NOCOPY VARCHAR2)
1002 IS
1003
1004 BEGIN
1005
1006 x_return_status := FND_API.G_RET_STS_ERROR;
1007 FND_MESSAGE.SET_NAME('GR',
1008 p_message_code);
1009 IF p_token_name IS NOT NULL THEN
1010 FND_MESSAGE.SET_TOKEN(p_token_name,
1011 p_token_value,
1012 FALSE);
1013 END IF;
1014
1015 IF FND_API.To_Boolean(p_called_by_form) THEN
1016 APP_EXCEPTION.Raise_Exception;
1017 ELSE
1018 FND_MSG_PUB.Add;
1019 FND_MSG_PUB.Count_and_Get
1020 (p_count => x_msg_count,
1021 p_data => x_msg_data);
1022 END IF;
1023
1024 END Handle_Error_Messages;
1025
1026 FUNCTION check_circular_reference (p_organization_id IN NUMBER,
1027 p_inventory_item_id IN NUMBER,
1028 p_parent_formula NUMBER,
1029 p_max_record NUMBER) RETURN BOOLEAN IS
1030 NO_EFFECTIVE_FORMULA_ERROR EXCEPTION;
1031 l_formula_use NUMBER(5);
1032 /* 26-Apr-2001 M. Grosser BUG 1755426 - Modified code to look at recipe and validity rules instead of
1033 effectivity table - to support new GMD data model.
1034 */
1035 l_orgn_code sy_orgn_mst.orgn_code%TYPE;
1036 /*
1037 ** Define the cursors needed
1038 **
1039 ** Effective Formula
1040 */
1041
1042 CURSOR c_get_effective_formula
1043 IS
1044 SELECT mst.formula_id
1045 FROM fm_form_mst_b mst,
1046 gmd_recipe_validity_rules rvr,
1047 gmd_status_b sts,
1048 gmd_recipes_b rcp,
1049 fm_matl_dtl dtl,
1050 mtl_system_items mtl
1051 WHERE rvr.inventory_item_id = mtl.inventory_item_id
1052 AND rvr.validity_rule_status = sts.status_code
1053 AND sts.status_type in ('400','700','900')
1054 AND rvr.recipe_id = rcp.recipe_id
1055 AND mst.formula_id = rcp.formula_id
1056 AND dtl.formula_id = rcp.formula_id
1057 AND (rvr.end_date IS NULL OR rvr.end_date >= g_current_date)
1058 AND rvr.start_date <= g_current_date
1059 AND rvr.recipe_use = l_formula_use
1060 AND (rvr.organization_id IS NULL OR rvr.organization_id = p_organization_id)
1061 AND dtl.line_type = 1
1062 AND dtl.inventory_item_id = mtl.inventory_item_id
1063 AND mtl.inventory_item_id = p_inventory_item_id
1064 AND rvr.delete_mark = 0
1065 AND mst.delete_mark = 0
1066 ORDER BY rvr.organization_id asc, rvr.preference asc, sts.status_type desc;
1067
1068 X_formula_id NUMBER(15);
1069 X_validity_rule_id NUMBER(15);
1070 l_curr_record NUMBER DEFAULT p_max_record;
1071 l_parent_formula NUMBER(10) DEFAULT p_parent_formula;
1072
1073 BEGIN
1074 IF c_get_effective_formula%ISOPEN THEN
1075 CLOSE c_get_effective_formula;
1076 END IF;
1077 l_formula_use := 3;
1078 OPEN c_get_effective_formula;
1079 FETCH c_get_effective_formula INTO X_formula_id;
1080 IF c_get_effective_formula%NOTFOUND THEN
1081 CLOSE c_get_effective_formula;
1082 l_formula_use := 0;
1083 OPEN c_get_effective_formula;
1084 FETCH c_get_effective_formula INTO X_formula_id;
1085 IF c_get_effective_formula%NOTFOUND THEN
1086 CLOSE c_get_effective_formula;
1087 RAISE No_Effective_Formula_Error;
1088 END IF;
1089 END IF;
1090 CLOSE c_get_effective_formula;
1091
1092 IF l_curr_record = 1 THEN
1093 IF l_explosion_list(l_curr_record).current_formula = X_formula_id THEN
1094 RETURN(TRUE);
1095 END IF;
1096 END IF;
1097
1098 WHILE (l_curr_record > 0) OR
1099 ((l_curr_record <> 0) AND (l_explosion_list(l_curr_record).parent_formula = 0))
1100 LOOP
1101 IF NVL(l_explosion_list(l_curr_record).current_formula, 0) = l_parent_formula THEN
1102 IF l_explosion_list(l_curr_record).parent_formula = X_formula_id THEN
1103 RETURN(TRUE);
1104 ELSE
1105 l_parent_formula := l_explosion_list(l_curr_record).parent_formula;
1106 END IF;
1107 END IF;
1108 l_curr_record := l_curr_record - 1;
1109 END LOOP;
1110 RETURN(FALSE);
1111 EXCEPTION
1112 WHEN No_Effective_Formula_Error THEN
1113 RETURN(FALSE);
1114 END check_circular_reference;
1115
1116
1117 /*
1118 ** This procedure is used to insert or update the
1119 ** rows in the concentration tables.
1120 **
1121 */
1122 PROCEDURE process_concentrations
1123 (p_organization_id NUMBER,
1124 p_inventory_item_id IN NUMBER,
1125 p_explosion_item_id IN NUMBER,
1126 p_source_item_id IN NUMBER,
1127 p_item_percent IN NUMBER,
1128 p_current_record IN NUMBER,
1129 p_item_um IN VARCHAR2,
1130 x_msg_count IN OUT NOCOPY NUMBER,
1131 x_msg_data IN OUT NOCOPY VARCHAR2,
1132 x_return_status OUT NOCOPY VARCHAR2)
1133 IS
1134
1135 /*
1136 ** Alpha Variables
1137 */
1138 L_ROWID VARCHAR2(18);
1139 L_KEY_EXISTS VARCHAR2(1);
1140 L_CALLED_BY_FORM VARCHAR2(1) := 'F';
1141 L_RETURN_STATUS VARCHAR2(1);
1142 L_MSG_DATA VARCHAR2(2000);
1143 L_CODE_BLOCK VARCHAR2(2000);
1144
1145
1146 L_COMMIT VARCHAR2(1) := 'F';
1147
1148 /*
1149 ** Numeric Variables
1150 */
1151 L_ITEM_PERCENT NUMBER;
1152 L_DET_ITEM_PERCENT NUMBER;
1153
1154 L_ORACLE_ERROR NUMBER;
1155 L_CONVERTED_QTY NUMBER;
1156 L_API_VERSION CONSTANT NUMBER := 1.0;
1157
1158 /*
1159 ** Exceptions
1160 */
1161 CONCENTRATION_INSERT_ERROR EXCEPTION;
1162 CONCENTRATION_DELETE_ERROR EXCEPTION;
1163
1164 CURSOR c_get_item_conc
1165 IS
1166 SELECT ic.concentration_percentage
1167 FROM gr_ingredient_concentrations ic
1168 WHERE ic.rowid = l_rowid;
1169 ConcRecord c_get_item_conc%ROWTYPE;
1170
1171 CURSOR c_get_item_conc_details
1172 IS
1173 SELECT ic.work_concentration
1174 FROM gr_ingredient_conc_details ic
1175 WHERE ic.rowid = l_rowid;
1176 ConcDetailRecord c_get_item_conc_details%ROWTYPE;
1177
1178
1179 BEGIN
1180
1181 SAVEPOINT process_concentrations;
1182 l_item_percent := p_item_percent;
1183 l_det_item_percent := l_item_percent;
1184
1185 l_code_block := ' Updating Item Concentrations Table';
1186 FND_FILE.PUT(FND_FILE.LOG,l_code_block);
1187 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1188
1189 GR_INGRED_CONCENTRATIONS_PKG.Check_Primary_Key
1190 (p_organization_id,
1191 p_inventory_item_id,
1192 p_source_item_id,
1193 'F',
1194 l_rowid,
1195 l_key_exists);
1196 IF FND_API.To_Boolean(l_key_exists) THEN
1197 OPEN c_get_item_conc;
1198 FETCH c_get_item_conc INTO ConcRecord;
1199 CLOSE c_get_item_conc;
1200
1201 l_item_percent := l_item_percent + ConcRecord.concentration_percentage;
1202
1203 GR_INGRED_CONCENTRATIONS_PKG.Update_Row
1204 (l_commit,
1205 l_called_by_form,
1206 l_rowid,
1207 p_organization_id,
1208 p_inventory_item_id,
1209 p_source_item_id,
1210 l_item_percent,
1211 g_current_date,
1212 g_user_id,
1213 g_user_id,
1214 g_current_date,
1215 g_user_id,
1216 l_return_status,
1217 l_oracle_error,
1218 l_msg_data);
1219
1220 IF l_return_status <> 'S' THEN
1221 RAISE Concentration_Insert_Error;
1222 END IF;
1223 ELSE
1224 GR_INGRED_CONCENTRATIONS_PKG.Insert_Row
1225 (l_commit,
1226 l_called_by_form,
1227 p_organization_id,
1228 p_inventory_item_id,
1229 p_source_item_id,
1230 l_item_percent,
1231 g_current_date,
1232 g_user_id,
1233 g_user_id,
1234 g_current_date,
1235 g_user_id,
1236 l_rowid,
1237 l_return_status,
1238 l_oracle_error,
1239 l_msg_data);
1240
1241 IF l_return_status <> 'S' THEN
1242 RAISE Concentration_Insert_Error;
1243 END IF;
1244 END IF;
1245
1246 l_code_block := ' Updating Item Concentration Detail Table';
1247 FND_FILE.PUT(FND_FILE.LOG,l_code_block);
1248 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1249
1250 GR_INGRED_CONC_DETAILS_PKG.Check_Primary_Key
1251 (p_organization_id,
1252 p_inventory_item_id,
1253 p_source_item_id,
1254 p_explosion_item_id,
1255 'F',
1256 l_rowid,
1257 l_key_exists);
1258 IF FND_API.To_Boolean(l_key_exists) THEN
1259 OPEN c_get_item_conc_details;
1260 FETCH c_get_item_conc_details INTO ConcDetailRecord;
1261 l_item_percent := l_det_item_percent + ConcDetailRecord.work_concentration;
1262 CLOSE c_get_item_conc_details;
1263
1264 GR_INGRED_CONC_DETAILS_PKG.Update_Row
1265 (l_commit,
1266 l_called_by_form,
1267 l_rowid,
1268 p_organization_id,
1269 p_inventory_item_id,
1270 p_source_item_id,
1271 p_explosion_item_id,
1272 p_current_record,
1273 l_item_percent,
1274 p_item_um,
1275 0,
1276 l_return_status,
1277 l_oracle_error,
1278 l_msg_data);
1279 IF l_return_status <> 'S' THEN
1280 RAISE Concentration_Insert_Error;
1281 END IF;
1282 ELSE
1283 GR_INGRED_CONC_DETAILS_PKG.Insert_Row
1284 (l_commit,
1285 l_called_by_form,
1286 p_organization_id,
1287 p_inventory_item_id,
1288 p_source_item_id,
1289 p_explosion_item_id,
1290 p_current_record,
1291 l_item_percent,
1292 p_item_um,
1293 0,
1294 l_rowid,
1295 l_return_status,
1296 l_oracle_error,
1297 l_msg_data);
1298
1299 IF l_return_status <> 'S' THEN
1300 RAISE Concentration_Insert_Error;
1301 END IF;
1302 END IF;
1303
1304 EXCEPTION
1305 WHEN Concentration_Insert_Error THEN
1306 ROLLBACK TO SAVEPOINT process_concentrations;
1307
1308 x_return_status := FND_API.G_RET_STS_ERROR;
1309 FND_MESSAGE.SET_NAME('GR',
1310 'GR_UNEXPECTED_ERROR');
1311 FND_MESSAGE.SET_TOKEN('TEXT',
1312 l_msg_data||sqlerrm,
1313 FALSE);
1314 FND_MSG_PUB.Add;
1315 FND_MSG_PUB.Count_and_Get
1316 (p_count => x_msg_count,
1317 p_data => x_msg_data);
1318 WHEN OTHERS THEN
1319 l_oracle_error := SQLCODE;
1320 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
1321 FND_MESSAGE.SET_NAME('GR',
1322 'GR_UNEXPECTED_ERROR');
1323 FND_MESSAGE.SET_TOKEN('TEXT',
1324 l_code_block||sqlerrm,
1325 FALSE);
1326
1327 END process_concentrations;
1328
1329
1330 /* M. Grosser 07-Mar-2002 BUG 1323951 - Added savepoint and rollback because we don't want to effect
1331 exploded components when we build the ingredient list. Even though 'F'
1332 was sent in for commit, changes were being saved.
1333 */
1334 PROCEDURE build_explosion_list
1335 (p_commit IN VARCHAR2,
1336 p_init_msg_list IN VARCHAR2,
1337 p_validation_level IN NUMBER,
1338 p_api_version IN NUMBER,
1339 p_organization_id NUMBER,
1340 p_inventory_item_id IN NUMBER,
1341 p_session_id IN NUMBER,
1342 x_explosion_list OUT NOCOPY GR_EXPLOSIONS.t_explosion_list,
1343 x_return_status OUT NOCOPY VARCHAR2,
1344 x_msg_count OUT NOCOPY NUMBER,
1345 x_msg_data OUT NOCOPY VARCHAR2)
1346 IS
1347
1348 /*
1349 ** Alpha Variables
1350 */
1351 L_CODE_BLOCK VARCHAR2(2000);
1352 L_RETURN_STATUS VARCHAR2(1);
1353
1354 /*
1355 ** Numeric Variables
1356 */
1357 L_ORACLE_ERROR NUMBER;
1358
1359 BEGIN
1360
1361 l_return_status := 'S';
1362 SAVEPOINT build_explosion_list;
1363 OPM_11i_MSDS_Formula
1364 (p_commit,
1365 p_init_msg_list,
1366 p_validation_level,
1367 p_api_version,
1368 p_organization_id,
1369 p_inventory_item_id,
1370 p_session_id,
1371 l_return_status,
1372 x_msg_count,
1373 x_msg_data);
1374
1375 IF l_return_status <> 'S' THEN
1376 X_return_status := l_return_status;
1377 ELSE
1378 x_explosion_list := l_ingredient_list;
1379 END IF;
1380 ROLLBACK TO SAVEPOINT build_explosion_list;
1381
1382 END build_explosion_list;
1383
1384
1385 /*======================================================================
1386 -- PROCEDURE:
1387 -- add_ingredient_to_list
1388 --
1389 -- DESCRIPTION:
1390 -- This PL/SQL procedure is used to build a list of all of the
1391 -- ingredients of a product as well as their concentration and
1392 -- weight percents.
1393 --
1394 -- PARAMETERS:
1395 -- p_item_code IN VARCHAR2 - Item code of ingredient
1396 -- p_conc_percent IN NUMBER - The concentration percent of the ingredient
1397 -- p_wt_percent IN NUMBER - The weight percent of the ingredient
1398 --
1399 -- SYNOPSIS:
1400 -- add_ingredient_to_list(l_item_code,l_item_pct, l_wt_pct);
1401 --
1402 -- HISTORY
1403 -- M. Grosser 19-Feb-2002 BUG 1323951 - Created procedure
1404 --===================================================================== */
1405
1406 PROCEDURE add_to_ingredient_list
1407 ( p_organization_id IN NUMBER,
1408 p_inventory_item_id IN NUMBER,
1409 p_conc_percent IN NUMBER,
1410 p_wt_percent IN NUMBER)
1411 IS
1412
1413
1414 /* ------------- LOCAL VARIABLES ------------------- */
1415 l_ingred_found VARCHAR2(2) := 'F';
1416
1417 BEGIN
1418 IF g_max_ingred > 0 THEN
1419 /* Check to see if ingredient already appears in the list. If it does, add up the percentages */
1420 FOR i in 1..g_max_ingred LOOP
1421 IF p_inventory_item_id = l_ingredient_list(i).inventory_item_id THEN
1422 l_ingredient_list(i).quantity := l_ingredient_list(i).quantity + p_conc_percent;
1423 l_ingredient_list(i).weight_pct := l_ingredient_list(i).weight_pct + p_wt_percent;
1424 l_ingred_found := 'T';
1425 EXIT;
1426 END IF;
1427 END LOOP;
1428 END IF;
1429
1430 /* If it is not already in the list, add a new record */
1431 IF l_ingred_found = 'F' THEN
1432 g_max_ingred := g_max_ingred + 1;
1433 l_ingredient_list(g_max_ingred).organization_id := p_organization_id;
1434 l_ingredient_list(g_max_ingred).inventory_item_id := p_inventory_item_id;
1435 l_ingredient_list(g_max_ingred).quantity := p_conc_percent;
1436 l_ingredient_list(g_max_ingred).weight_pct := p_wt_percent;
1437 END IF;
1438
1439 END add_to_ingredient_list;
1440
1441
1442
1443 /*======================================================================
1444 -- PROCEDURE:
1445 -- OPM_MSDS_Formula_With_IDS
1446 --
1447 -- DESCRIPTION:
1448 -- This PL/SQL procedure is used to return the formula_no,
1449 -- formula_vers, recipe_no and formula_version along with the
1450 -- relevent explosion info.
1451 --
1452 -- PARAMETERS:
1453 -- p_commit IN VARCHAR2 - Issue a commmit, 'T'rue or 'F'alse
1454 -- p_init_msg_list IN VARCHAR2 - Initialize message list 'T'rue or 'F'alse
1455 -- p_validation_level IN NUMBER - Level of validation/error trapping
1456 -- p_api_version IN NUMBER - API version for compatibilty check
1457 -- p_item_code IN VARCHAR2 - Product to explode
1458 -- p_session_id IN NUMBER - Session id
1459 -- x_formula_no OUT NOCOPY VARCHAR2 - Formula no from effective formula
1460 -- x_formula_vers OUT NOCOPY NUMBER - Formula version from effective formula
1461 -- x_recipe_no OUT NOCOPY VARCHAR2 - Effective Recipe no
1462 -- x_recipe_vers OUT NOCOPY NUMBER - Effective Recipe version
1463 -- x_return_status OUT NOCOPY VARCHAR2 - Return status 'S'uccessful, Trapped 'E'rror, 'U'ntrapped Error
1464 -- x_msg_count OUT NOCOPY NUMBER - Number of error messages
1465 -- x_msg_data OUT NOCOPY VARCHAR2 - Text of error
1466 --
1467 -- SYNOPSIS:
1468 -- OPM_MSDS_Formula_With_IDS('F', 'F', 99, 1.0, l_item_code, g_session_id,
1469 -- l_formula_no, l_formula_vers, l_recipe_no, l_recipe_vers,
1470 -- l_return_status, l_msg_count, l_msg_data);
1471 --
1472 -- HISTORY
1473 -- Melanie Grosser 20-May-2003 BUG 2932007 - Document Management Phase I
1474 -- Created procedure
1475 --===================================================================== */
1476 PROCEDURE OPM_MSDS_Formula_With_IDS
1477 (p_commit IN VARCHAR2,
1478 p_init_msg_list IN VARCHAR2,
1479 p_validation_level IN NUMBER,
1480 p_api_version IN NUMBER,
1481 p_organization_id NUMBER,
1482 p_inventory_item_id IN NUMBER,
1483 p_session_id IN NUMBER,
1484 x_formula_no OUT NOCOPY VARCHAR2,
1485 x_formula_vers OUT NOCOPY NUMBER,
1486 x_recipe_no OUT NOCOPY VARCHAR2,
1487 x_recipe_vers OUT NOCOPY NUMBER,
1488 x_return_status OUT NOCOPY VARCHAR2,
1489 x_msg_count OUT NOCOPY NUMBER,
1490 x_msg_data OUT NOCOPY VARCHAR2)
1491 IS
1492
1493 /*
1494 ** Alpha Variables
1495 */
1496 L_CODE_BLOCK VARCHAR2(2000);
1497 L_RETURN_STATUS VARCHAR2(1);
1498
1499 /*
1500 ** Numeric Variables
1501 */
1502 L_ORACLE_ERROR NUMBER;
1503
1504 BEGIN
1505
1506 l_return_status := 'S';
1507 OPM_11i_MSDS_Formula
1508 (p_commit,
1509 p_init_msg_list,
1510 p_validation_level,
1511 p_api_version,
1512 p_organization_id,
1513 p_inventory_item_id,
1514 p_session_id,
1515 l_return_status,
1516 x_msg_count,
1517 x_msg_data);
1518
1519 x_formula_no := g_formula_no;
1520 x_formula_vers := g_formula_vers;
1521 x_recipe_no := g_recipe_no;
1522 x_recipe_vers := g_recipe_vers;
1523
1524 END OPM_MSDS_Formula_With_IDS;
1525
1526 END GR_EXPLOSIONS;
[Home] [Help]