1 PACKAGE BODY gme_fpl_pvt AS
2 /* $Header: GMEVFPLB.pls 120.2.12010000.1 2008/11/06 03:43:02 srpuri noship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_FPL_PVT';
5
6 /*******************************************************/
7 /* Oracle Process Manufacturing Process Execution APIs */
8 /* */
9 /* File Name: GMEVFPLB.pls */
10 /* Contents: GME Fixed Process Loss procedures. */
11 /* HISTORY
12 Archana Mundhe 03-Apr-2008 Bug 6908375
13 Modified procedure apply_fixed_process_loss to
14 Raise error if new_input_qty is <=0
15 ***************************************************** */
16
17 PROCEDURE get_fixed_process_loss (
18 p_batch_id IN NUMBER DEFAULT NULL
19 ,p_validity_rule_id IN NUMBER
20 ,p_organization_id IN NUMBER DEFAULT NULL
21 ,x_fixed_process_loss OUT NOCOPY NUMBER
22 ,x_fixed_process_loss_uom OUT NOCOPY sy_uoms_mst.uom_code%TYPE
23 )
24 IS
25
26 l_api_name VARCHAR2 (30) := 'GET_FIXED_PROCESS_LOSS';
27 l_recipe_id NUMBER;
28 l_formula_id NUMBER;
29 l_routing_id NUMBER;
30 l_validity_rule_id NUMBER;
31 l_organization_id NUMBER;
32 l_routing_class gmd_routings_b.routing_class%TYPE;
33 expected_error EXCEPTION;
34
35 CURSOR cur_get_info (v_batch_id NUMBER)
36 IS
37 SELECT b.recipe_validity_rule_id, a.recipe_id, b.organization_id
38 ,b.routing_id, b.formula_id
39 FROM gme_batch_header b, gmd_recipe_validity_rules a
40 WHERE b.batch_id = v_batch_id
41 AND b.recipe_validity_rule_id = a.recipe_validity_rule_id;
42
43 CURSOR cur_get_info_from_validity (v_validity_rule_id NUMBER)
44 IS
45 SELECT r.recipe_id, r.routing_id, r.formula_id
46 FROM gmd_recipes_b r, gmd_recipe_validity_rules v
47 WHERE v.recipe_validity_rule_id = v_validity_rule_id
48 AND v.recipe_id = r.recipe_id;
49
50 CURSOR validity_process_loss_cursor (v_recipe_validity_rule_id NUMBER)
51 IS
52 SELECT fixed_process_loss, fixed_process_loss_uom
53 FROM gmd_recipe_validity_rules
54 WHERE recipe_validity_rule_id = NVL (v_recipe_validity_rule_id, -1);
55
56 CURSOR orgn_process_loss_cursor (v_recipe_id NUMBER, v_org_id NUMBER)
57 IS
58 SELECT fixed_process_loss, fixed_process_loss_uom
59 FROM gmd_recipe_process_loss
60 WHERE recipe_id = v_recipe_id AND organization_id = v_org_id;
61
62 CURSOR recipe_process_loss_cursor (v_recipe_id NUMBER)
63 IS
64 SELECT fixed_process_loss, fixed_process_loss_uom
65 FROM gmd_recipes
66 WHERE recipe_id = v_recipe_id;
67
68 CURSOR routing_process_loss_cursor (v_routing_id NUMBER)
69 IS
70 SELECT routing_class, fixed_process_loss, fixed_process_loss_uom
71 FROM gmd_routings_b
72 WHERE routing_id = v_routing_id;
73
74 CURSOR routing_cls_process_loss (v_routing_id NUMBER)
75 IS
76 SELECT b.fixed_process_loss, b.routing_class_uom
77 FROM gmd_routings_b a, gmd_routing_class_b b
78 WHERE a.routing_id = v_routing_id
79 AND a.routing_class = b.routing_class (+);
80
81 BEGIN
82 IF g_debug <= gme_debug.g_log_procedure THEN
83 gme_debug.put_line ('Entering1 api ' || g_pkg_name || '.'
84 || l_api_name);
85 END IF;
86
87 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
88 gme_debug.put_line (l_api_name || ': Parameters');
89 gme_debug.put_line ('p_batch_id:' || p_batch_id);
90 gme_debug.put_line ('p_validity_rule_id:' || p_validity_rule_id);
91 gme_debug.put_line ('p_organization_id:' || p_organization_id);
92 END IF;
93 l_organization_id := p_organization_id;
94 -- Use and validate the batch id if it is passed in.
95 IF (p_batch_id IS NOT NULL) THEN
96 OPEN cur_get_info (p_batch_id);
97
98 FETCH cur_get_info
99 INTO l_validity_rule_id, l_recipe_id, l_organization_id, l_routing_id ,l_formula_id;
100 CLOSE cur_get_info;
101 IF (l_recipe_id IS NULL AND l_validity_rule_id IS NOT NULL) THEN
102 gme_common_pvt.log_message ('GME_INVALID_BATCH');
103 RAISE expected_error;
104 END IF;
105 ELSIF p_validity_rule_id IS NOT NULL THEN
106 -- Validate recipe validity rule
107 l_validity_rule_id := p_validity_rule_id;
108
109 OPEN cur_get_info_from_validity (p_validity_rule_id);
110 FETCH cur_get_info_from_validity INTO l_recipe_id, l_routing_id, l_formula_id;
111 CLOSE cur_get_info_from_validity;
112
113 IF (l_recipe_id IS NULL) THEN
114 gme_common_pvt.log_message ('GME_API_INVALID_RULE');
115 RAISE expected_error;
116 END IF;
117 END IF;
118
119 -- Do not process LCF batch
120 IF (l_validity_rule_id IS NULL) THEN
121 RETURN;
122 END IF;
123
124 -- Determine the Fixed process loss at VR level.
125 OPEN validity_process_loss_cursor (l_validity_rule_id);
126 FETCH validity_process_loss_cursor INTO x_fixed_process_loss, x_fixed_process_loss_uom;
127 CLOSE validity_process_loss_cursor;
128
129 IF x_fixed_process_loss IS NOT NULL THEN
130 RETURN;
131 ELSIF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
132 gme_debug.put_line ( l_api_name || ': No process_loss at validity level');
133 END IF;
134
135 -- Determine FPL at recipe Org level
136 OPEN orgn_process_loss_cursor (l_recipe_id, l_organization_id);
137 FETCH orgn_process_loss_cursor INTO x_fixed_process_loss, x_fixed_process_loss_uom;
138 CLOSE orgn_process_loss_cursor;
139
140 IF x_fixed_process_loss IS NOT NULL THEN
141 return;
142 ELSIF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
143 gme_debug.put_line ( l_api_name || ': No fixed process_loss at orgn level');
144 END IF;
145
146 -- Determin FPL at Recipe level.
147 OPEN recipe_process_loss_cursor (l_recipe_id);
148 FETCH recipe_process_loss_cursor INTO x_fixed_process_loss, x_fixed_process_loss_uom;
149 CLOSE recipe_process_loss_cursor;
150 IF x_fixed_process_loss IS NOT NULL THEN
151 return;
152 ELSIF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
153 gme_debug.put_line ( l_api_name || 'No fixed process_loss at recipe level');
154 END IF;
155
156 -- Determine FPL at routing level.
157 IF (l_routing_id IS NOT NULL ) THEN
158 OPEN routing_process_loss_cursor (l_routing_id);
159 FETCH routing_process_loss_cursor INTO l_routing_class, x_fixed_process_loss,x_fixed_process_loss_uom ;
160 CLOSE routing_process_loss_cursor;
161 IF x_fixed_process_loss IS NOT NULL THEN
162 return;
163 ELSIF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
164 gme_debug.put_line ( l_api_name || 'No fixed process_loss at routing level');
165 END IF;
166 END IF;
167
168 -- Determine FPL at routing class level.
169 IF l_routing_class IS NOT NULL THEN
170 OPEN routing_cls_process_loss (l_routing_id);
171 FETCH routing_cls_process_loss INTO x_fixed_process_loss,x_fixed_process_loss_uom;
172 CLOSE routing_cls_process_loss;
173 IF x_fixed_process_loss IS NOT NULL THEN
174 return;
175 ELSIF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
176 gme_debug.put_line (l_api_name || ': No FPL at routing class ');
177 END IF;
178 END IF;
179
180 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
181 gme_debug.put_line (l_api_name || 'No Fixed Process Loss defined ');
182 END IF;
183
184 IF g_debug <= gme_debug.g_log_procedure THEN
185 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
186 END IF;
187
188 EXCEPTION
189 WHEN expected_error THEN
190 IF g_debug <= gme_debug.g_log_procedure THEN
191 gme_debug.put_line('expected ERROR in gme_fpl_pvt.get_fixed_process_loss');
192 END IF;
193 RETURN;
194 WHEN OTHERS THEN
195 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
196
197 IF g_debug <= gme_debug.g_log_unexpected THEN
198 gme_debug.put_line ( 'When others exception in '
199 || g_pkg_name
200 || '.'
201 || l_api_name
202 || ' Error is '
203 || SQLERRM);
204 END IF;
205
206 END get_fixed_process_loss;
207
208 /******* Procedure to apply fixed process loss **********/
209
210 PROCEDURE apply_fixed_process_loss (
211 p_batch_header_rec IN gme_batch_header%ROWTYPE
212 ,p_material_tbl IN gme_common_pvt.material_details_tab
213 ,p_organization_id IN NUMBER DEFAULT NULL
214 ,p_creation_mode IN VARCHAR2
215 ,p_called_from IN NUMBER DEFAULT 1 /*1 = Create Batch, 2 = Batch details */
216 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
217 ,x_material_tbl OUT NOCOPY gme_common_pvt.material_details_tab
218 ,x_return_status OUT NOCOPY VARCHAR2
219 )
220 IS
221 l_api_name VARCHAR2(30) := 'APPLY_FIXED_PROCESS_LOSS';
222 l_fixed_process_loss NUMBER;
223 l_fixed_process_loss_uom sy_uoms_mst.uom_code%TYPE;
224 l_item_uom sy_uoms_mst.uom_code%TYPE;
225 l_new_fpl NUMBER;
226 l_convert_dtl_uom NUMBER := 0;
227 l_convert_fpl_uom NUMBER := 0;
228 l_new_dtl_qty NUMBER;
229 TOTAL_OUTPUT_QTY NUMBER;
230 total_input_qty NUMBER;
231 new_input_qty NUMBER;
232 ACTUAL_OUTPUT_TOTAL NUMBER;
233 l_original_primary_qty NUMBER;
234 l_plan_qty NUMBER;
235 l_item_no VARCHAR2 (2000);
236 l_item_id NUMBER;
237 l_from_uom sy_uoms_mst.uom_code%TYPE;
238 l_to_uom sy_uoms_mst.uom_code%TYPE;
239 l_error_msg varchar2(4000);
240
241 conversion_failure EXCEPTION;
242 --Bug 6908375
243 fpl_setup_error EXCEPTION;
244
245 CURSOR get_item_primary_uom (v_inventory_item_id NUMBER, v_org_id NUMBER)
246 IS
247 SELECT primary_uom_code
248 FROM mtl_system_items_kfv
249 WHERE inventory_item_id = v_inventory_item_id
250 AND organization_id = v_org_id;
251
252 CURSOR cur_item_no (v_org_id NUMBER, v_inventory_item_id NUMBER)
253 IS
254 SELECT concatenated_segments
255 FROM mtl_system_items_kfv
256 WHERE organization_id = v_org_id
257 AND inventory_item_id = v_inventory_item_id;
258
259 BEGIN
260 IF g_debug <= gme_debug.g_log_procedure THEN
261 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
262 END IF;
263
264 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
265 gme_debug.put_line (l_api_name || ': Parameters');
266 gme_debug.put_line ('p_batch_id:' || p_batch_header_rec.batch_id);
267 gme_debug.put_line ('p_organization_id :' || p_organization_id);
268 END IF;
269
270 x_return_status := fnd_api.g_ret_sts_success;
271
272 -- Initialize output batch header
273 x_batch_header_rec := p_batch_header_rec;
274 x_material_tbl := p_material_tbl;
275
276 gme_fpl_pvt.get_fixed_process_loss
277 (p_batch_id => p_batch_header_rec.batch_id
278 ,p_validity_rule_id => p_batch_header_rec.recipe_validity_rule_id
279 ,p_organization_id => p_organization_id
280 ,x_fixed_process_loss => l_fixed_process_loss
281 ,x_fixed_process_loss_uom => l_fixed_process_loss_uom
282 );
283
284 IF l_fixed_process_loss IS NULL OR l_fixed_process_loss_uom IS NULL THEN
285 RETURN;
286 END IF;
287
288 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
289 gme_debug.put_line ('Fixed Process Loss :' || l_fixed_process_loss);
290 gme_debug.put_line ('Fixed Process Loss UOM :'||l_fixed_process_loss_uom);
291 gme_debug.put_line ('p material table count is :'||p_material_tbl.COUNT);
292 END IF;
293
294 --- Get Total of product qty's
295 total_output_qty :=0;
296 FOR i IN 1 .. p_material_tbl.COUNT
297 LOOP
298 IF ( p_material_tbl (i).line_type <> -1) THEN
299 l_new_dtl_qty := p_material_tbl(i).plan_qty;
300 -- IF detail uom is not same as Fixed Process Loss type uom then convert it to Fixed Process Loss uom
301 IF p_material_tbl(i).dtl_um <> l_fixed_process_loss_uom THEN
302 IF (g_debug = gme_debug.g_log_statement) THEN
303 gme_debug.put_line(' product dtl uom is ' || p_material_tbl(i).dtl_um);
304 gme_debug.put_line(' product plan qty is ' || p_material_tbl(i).plan_qty);
305 END IF;
306 l_new_dtl_qty := inv_convert.inv_um_convert
307 (item_id => p_material_tbl(i).inventory_item_id
308 ,PRECISION => gme_common_pvt.g_precision
309 ,from_quantity => p_material_tbl(i).plan_qty
310 ,from_unit => p_material_tbl(i).dtl_um
311 ,to_unit => l_fixed_process_loss_uom
312 ,from_name => NULL
313 ,to_name => NULL);
314 IF l_new_dtl_qty < 0 THEN
315 IF (g_debug = gme_debug.g_log_statement) THEN
316 gme_debug.put_line('Failed in UOM Conv from dtl_um to fixed process loss UOM ');
317 END IF;
318 l_item_id := p_material_tbl(i).inventory_item_id;
319 l_from_uom := p_material_tbl(i).dtl_um;
320 l_to_uom := l_fixed_process_loss_uom;
321 RAISE conversion_failure;
322 END IF;
323 END IF;
324 IF (g_debug = gme_debug.g_log_statement) THEN
325 gme_debug.put_line(' converted `product dtl uom is ' || l_new_dtl_qty);
326 END IF;
327 total_output_qty := NVL(total_output_qty,0) + l_new_dtl_qty;
328 END IF;
329 END LOOP;
330
331 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
332 gme_debug.put_line ('Total product qty :' || total_output_qty);
333 END IF;
334
335 --- Get Total of ingredient qty's that contribute to yield and scale type is not fixed
336 total_input_qty :=0;
337 FOR i IN 1 .. p_material_tbl.COUNT
338 LOOP
339 IF ( p_material_tbl (i).line_type = -1
340 AND p_material_tbl (i).contribute_yield_ind = 'Y'
341 AND p_material_tbl (i).scale_type <> 0 ) THEN
342 l_new_dtl_qty := p_material_tbl(i).plan_qty;
343 -- IF detail uom is not same as FM Yield type uom then convert it to FM Yield type uom
344 IF p_material_tbl(i).dtl_um <> l_fixed_process_loss_uom THEN
345 l_new_dtl_qty := inv_convert.inv_um_convert
346 (item_id => p_material_tbl(i).inventory_item_id
347 ,PRECISION => gme_common_pvt.g_precision
348 ,from_quantity => p_material_tbl(i).plan_qty
349 ,from_unit => p_material_tbl(i).dtl_um
350 ,to_unit => l_fixed_process_loss_uom
351 ,from_name => NULL
352 ,to_name => NULL);
353 IF l_new_dtl_qty < 0 THEN
354 IF (g_debug = gme_debug.g_log_statement) THEN
355 gme_debug.put_line('Failed in UOM Conv from dtl_um to fixed process loss UOM ');
356 END IF;
357 l_item_id := p_material_tbl(i).inventory_item_id;
358 l_from_uom := p_material_tbl(i).dtl_um;
359 l_to_uom := l_fixed_process_loss_uom;
360 RAISE conversion_failure;
361 END IF;
362 END IF;
363 total_input_qty := NVL(total_input_qty,0) + l_new_dtl_qty;
364 END IF;
365 END LOOP;
366
367 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
368 gme_debug.put_line ('Total ingredient qty contributing to yield :' || total_input_qty);
369 END IF;
370
371 IF (p_called_from = 1 AND p_creation_mode = 'INPUT') THEN
372 -- Calculate new total input qty after accounting for fixed process loss
373 New_input_qty := total_input_qty - l_fixed_process_loss;
374
375 Actual_output_total := (total_output_qty * new_input_qty)/total_input_qty;
376 -- Bug 6908375
377 -- Raise error if new_input_qty is <=0
378 IF NVL(New_input_qty,0) <= 0 THEN
379 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
380 gme_debug.put_line ('New total input :' || new_input_qty);
381 gme_debug.put_line ('returning');
382 END IF;
383 RAISE fpl_setup_error;
384 END IF;
385
386 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
387 gme_debug.put_line ('New total input after accounting for Fixed process loss :' || new_input_qty);
388 gme_debug.put_line ('Actual total output qty based on new input qty :' || Actual_output_total);
389 END IF;
390 END IF;
391
392 X_material_tbl := p_material_tbl;
393 -- Loop thru material detail lines.
394 FOR i IN 1 .. p_material_tbl.COUNT
395 LOOP
396 -- Process only the material details lines that contribute to yield and are not fixed scale type.
397 IF ( p_material_tbl (i).contribute_yield_ind = 'Y' AND p_material_tbl (i).scale_type <> 0) THEN
398
399 l_convert_dtl_uom := 0;
400
401 l_new_dtl_qty := p_material_tbl(i).plan_qty;
402
403 -- Verify if all material detail lines uom is same as the FM yield type uom.
404 -- If not convert plan qty to FM yield type uom.
405 IF p_material_tbl(i).dtl_um <> l_fixed_process_loss_uom THEN
406 l_new_dtl_qty := inv_convert.inv_um_convert
407 (item_id => p_material_tbl(i).inventory_item_id
408 ,PRECISION => gme_common_pvt.g_precision
409 ,from_quantity => p_material_tbl(i).plan_qty
410 ,from_unit => p_material_tbl(i).dtl_um
411 ,to_unit => l_fixed_process_loss_uom
412 ,from_name => NULL
413 ,to_name => NULL);
414 IF l_new_dtl_qty < 0 THEN
415 IF (g_debug = gme_debug.g_log_statement) THEN
416 gme_debug.put_line('Failed in UOM Conv from dtl_um to fixed process loss UOM ');
417 END IF;
418 l_item_id := p_material_tbl(i).inventory_item_id;
419 l_from_uom := p_material_tbl(i).dtl_um;
420 l_to_uom := l_fixed_process_loss_uom;
421 RAISE conversion_failure;
422 END IF;
423 l_convert_dtl_uom := 1;
424
425 END IF;
426 -- Apply fixed process loss to material detail qty.
427 -- If creation mode is recipe, output or porduct then inflate the ingredient qty.
428 IF(p_creation_mode IN ('RECIPE', 'PRODUCT', 'OUTPUT') OR p_called_from = 2) THEN
429 IF (p_material_tbl(i).line_type = -1) THEN
430 l_new_dtl_qty :=
431 l_new_dtl_qty +
432 l_new_dtl_qty * (l_fixed_process_loss/total_input_qty);
433 END IF;
434 -- If creation mode is input then decrease the product qty.
435 ELSIF (p_called_from = 1 AND p_creation_mode = 'INPUT') THEN
436 IF (p_material_tbl(i).line_type <> -1) THEN
437 l_new_dtl_qty :=
438 l_new_dtl_qty * (Actual_output_total/total_output_qty);
439 END IF;
440 END IF;
441
442 IF l_convert_dtl_uom = 1 THEN
443 -- Convert plan qty back to material detail uom.
444 l_plan_qty := inv_convert.inv_um_convert
445 (item_id => p_material_tbl(i).inventory_item_id
446 ,PRECISION => gme_common_pvt.g_precision
447 ,from_quantity => l_new_dtl_qty
448 ,from_unit => l_fixed_process_loss_uom
449 ,to_unit => p_material_tbl(i).dtl_um
450 ,from_name => NULL
451 ,to_name => NULL);
452 IF l_plan_qty < 0 THEN
453 IF (g_debug = gme_debug.g_log_statement) THEN
454 gme_debug.put_line('Failed in UOM Conv from fixed process loss UOM to detail uom ');
455 END IF;
456 l_item_id := p_material_tbl(i).inventory_item_id;
457 l_from_uom := l_fixed_process_loss_uom;
458 l_to_uom := p_material_tbl(i).dtl_um;
459 RAISE conversion_failure;
460 ELSE
461 x_material_tbl(i).plan_qty := ROUND(l_plan_qty,5);
462 END IF;
463 ELSE
464 x_material_tbl(i).plan_qty := ROUND(l_new_dtl_qty,5);
465 END IF;
466
467 -- Only during Creating a batch apply fixed process loss to original_qty and original_primary_qty.
468 IF (p_called_from = 1) THEN
469 x_material_tbl(i).original_qty := x_material_tbl(i).plan_qty;
470
471 -- Fetch item uom as Original primary qty is always in item uom.
472 open get_item_primary_uom( p_material_tbl(i).inventory_item_id, p_organization_id);
473 FETCH get_item_primary_uom INTO l_item_uom;
474 CLOSE get_item_primary_uom;
475
476 x_material_tbl(i).original_primary_qty := x_material_tbl(i).plan_qty;
477
478 -- If item uom is not same as detail uom, convert!
479 IF (l_item_uom <> p_material_tbl(i).dtl_um) THEN
480 l_original_primary_qty := inv_convert.inv_um_convert
481 (item_id => p_material_tbl(i).inventory_item_id
482 ,PRECISION => gme_common_pvt.g_precision
483 ,from_quantity => x_material_tbl(i).original_primary_qty
484 ,from_unit => p_material_tbl(i).dtl_um
485 ,to_unit => l_item_uom
486 ,from_name => NULL
487 ,to_name => NULL);
488 IF l_original_primary_qty < 0 THEN
489 IF (g_debug = gme_debug.g_log_statement) THEN
490 gme_debug.put_line('Failed in UOM Conv from detail uom to item uom ');
491 END IF;
492 l_item_id := p_material_tbl(i).inventory_item_id;
493 l_from_uom := p_material_tbl(i).dtl_um;
494 l_to_uom := l_item_uom;
495 RAISE conversion_failure;
496 ELSE
497 x_material_tbl(i).original_primary_qty := ROUND(l_original_primary_qty,5);
498 END IF;
499 END IF;
500 END IF; -- p_called_from = 1
501
502 END IF; -- IF ( p_material_tbl (i).contribute_yield_ind = 'Y' AND p_material_tbl (i).scale_type <> 0)
503 END LOOP; -- Loop thru material detail lines.
504
505 -- set the indicator on batch header.
506 x_batch_header_rec.fixed_process_loss_applied := 'Y';
507
508 EXCEPTION
509 WHEN conversion_failure THEN
510 OPEN cur_item_no (p_organization_id, l_item_id);
511
512 FETCH cur_item_no INTO l_item_no;
513
514 CLOSE cur_item_no;
515
516 x_return_status := fnd_api.g_ret_sts_error;
517 fnd_message.set_name ('GMI', 'IC_API_UOM_CONVERSION_ERROR');
518 fnd_message.set_token ('ITEM_NO', l_item_no);
519 fnd_message.set_token ('FROM_UOM', l_from_uom);
520 fnd_message.set_token ('TO_UOM', l_to_uom);
521 fnd_msg_pub.ADD;
522 -- Bug 6908375
523 WHEN fpl_setup_error THEN
524 x_return_status := fnd_api.g_ret_sts_error;
525 FND_MESSAGE.SET_NAME('GME','GME_FPL_SETUP_ERROR');
526 APP_EXCEPTION.RAISE_EXCEPTION;
527 END;
528
529 PROCEDURE FPL_batch_details (
530 p_batch_header_rec IN gme_batch_header%ROWTYPE
531 ,p_called_from IN NUMBER DEFAULT 1 /*1 = Create Batch, 2 = Batch details */
532 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
533 ,x_message_count OUT NOCOPY NUMBER
534 ,x_message_list OUT NOCOPY VARCHAR2
535 ,x_return_status OUT NOCOPY VARCHAR2
536 )
537 IS
538 CURSOR cur_get_matl (v_batch_id NUMBER) IS
539 SELECT *
540 FROM gme_material_details
541 WHERE batch_id = v_batch_id
542 ORDER BY line_no;
543 l_api_name CONSTANT VARCHAR2 (30) := 'FPL_batch_details';
544 x_batch_header_rec gme_batch_header%ROWTYPE;
545 l_material_tbl gme_common_pvt.material_details_tab;
546 x_material_tbl gme_common_pvt.material_details_tab;
547 i NUMBER := 0;
548 l_row_count NUMBER;
549 err_msg varchar2(2000);
550 Fixed_process_loss_failure EXCEPTION;
551 material_save_failed EXCEPTION;
552 setup_failure EXCEPTION;
553 error_update_batch EXCEPTION;
554 BEGIN
555 x_return_status := fnd_api.g_ret_sts_success;
556
557 IF (g_debug <> -1) THEN
558 gme_debug.log_initialize ('FixedProcessLoss');
559 END IF;
560
561 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
562 gme_debug.put_line ( g_pkg_name
563 || '.'
564 || l_api_name);
565 END IF;
566 gme_debug.put_line('batch id is ' || p_batch_header_rec.batch_id);
567 FOR l_rec IN cur_get_matl (p_batch_header_rec.batch_id) LOOP
568 i := i + 1;
569 l_material_tbl (i) := l_rec;
570 END LOOP;
571
572 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
573 gme_debug.put_line('before call to gme_fpl_pvt.apply_fixed_process_loss');
574 END IF;
575 -- Initialize output batch header
576 x_batch_header_rec := p_batch_header_rec;
577 x_material_tbl := l_material_tbl;
578 gme_fpl_pvt.apply_fixed_process_loss
579 (p_batch_header_rec => p_batch_header_rec
580 ,p_material_tbl => l_material_tbl
581 ,p_organization_id => p_batch_header_rec.organization_id
582 ,p_creation_mode => 'OUTPUT'
583 ,p_called_from => p_called_from
584 ,x_batch_header_rec => x_batch_header_rec
585 ,x_material_tbl => x_material_tbl
586 ,x_return_status => x_return_status
587 );
588 IF (g_debug = gme_debug.g_log_statement) THEN
589 gme_debug.put_line ('Return from Apply Fixed Proccess Loss is ' || x_return_status);
590 END IF;
591 IF x_return_status <> fnd_api.g_ret_sts_success THEN
592 RAISE Fixed_process_loss_failure;
593 END IF;
594 IF NOT gme_common_pvt.g_setup_done THEN
595 gme_common_pvt.g_setup_done :=
596 gme_common_pvt.setup (p_batch_header_rec.organization_id);
597
598 IF NOT gme_common_pvt.g_setup_done THEN
599 x_return_status := fnd_api.g_ret_sts_error;
600 RAISE setup_failure;
601 END IF;
602 END IF;
603 gme_common_pvt.set_timestamp;
604 -- Update the batch header
605 IF NOT gme_batch_header_dbl.update_row
606 (p_batch_header => x_batch_header_rec) THEN
607 RAISE error_update_batch;
608 END IF;
609
610 FOR i IN 1..x_material_tbl.COUNT LOOP
611 IF (g_debug = gme_debug.g_log_statement) THEN
612 gme_debug.put_line('Before calling gme_material_details_dbl.update_row');
613 END IF;
614 IF NOT (gme_material_details_dbl.update_row (x_material_tbl (i) )) THEN
615 RAISE material_save_failed;
616 ELSE
617 x_material_tbl (i).last_update_date := gme_common_pvt.g_timestamp;
618 x_material_tbl (i).last_updated_by := gme_common_pvt.g_user_ident;
619 x_material_tbl (i).last_update_login := gme_common_pvt.g_login_id;
620 END IF;
621 END LOOP;
622 EXCEPTION
623 WHEN Fixed_process_loss_failure THEN
624 IF (g_debug = gme_debug.g_log_statement) THEN
625 gme_debug.put_line ('Failed to apply FPL');
626 END IF;
627 WHEN error_update_batch THEN
628 x_return_status := FND_API.g_ret_sts_unexp_error;
629 WHEN material_save_failed THEN
630 IF (g_debug = gme_debug.g_log_statement) THEN
631 gme_debug.put_line ('Material save failed');
632 END IF;
633 x_return_status := fnd_api.g_ret_sts_error;
634 WHEN setup_failure THEN
635 --ROLLBACK TO SAVEPOINT create_batch;
636 --x_batch_header_rec := NULL;
637 gme_common_pvt.count_and_get (x_count => x_message_count
638 ,p_encoded => fnd_api.g_false
639 ,x_data => x_message_list);
640 x_return_status := fnd_api.g_ret_sts_error;
641 END ;
642
643 END gme_fpl_pvt;
[Home] [Help]