[Home] [Help]
PACKAGE BODY: APPS.GME_SCALE_BATCH_PVT
Source
1 PACKAGE BODY gme_scale_batch_pvt AS
2 /* $Header: GMEVSCBB.pls 120.10.12010000.3 2009/06/17 09:14:30 apmishra ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 /************************************************************
5 REM* Oracle Process Manufacturing Process Execution APIs *
6 REM* *
7 REM* File Name: GMEVSCBB.pls *
8 REM* Contents: Private layer for Scale batch API *
9 REM* Author: Oracle *
10 REM* Date: January 2001 *
11 REM* 10Feb05 Navin Sinha *
12 REM* Made changes for GME_Scale_Batch_Theoretical_Yield_TD.*
13 REM* 15Dec05 SivakumarG *
14 REM* p_qty_type is replaced by 1 during phantom batch *
15 REM* scaling *
16 REM* Namit S bug#5674398 12-DEC-2006 *
17 REM* Passing the substitution effective date that is fetched*
18 REM* based on the parameter Ingredient Substitution Date to *
19 REM* the parameter, pDate of the procedure, *
20 REM* get_substitute_line_item in scale_batch overloaded procedure. *
21 REM* 8515551 While making a call for the reschedule batch procedure
22 REM* 8515551assigns planned completion date as null, hence the scaling removes
23 REM* 8515551resource transactions and hence putting the condition not to make it NULL for WIP cases
24 ************************************************************/
25
26 /* Global variables */
27 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_SCALE_BATCH_PVT';
28
29 PROCEDURE scale_batch (
30 p_batch_header_rec IN gme_batch_header%ROWTYPE
31 ,p_scale_factor IN NUMBER
32 ,p_primaries IN VARCHAR2
33 ,p_qty_type IN NUMBER
34 ,p_validity_rule_id IN NUMBER
35 ,p_enforce_vldt_check IN VARCHAR2
36 ,p_recalc_dates IN VARCHAR2
37 ,p_use_workday_cal IN VARCHAR2
38 ,p_contiguity_override IN VARCHAR2
39 ,x_exception_material_tbl OUT NOCOPY gme_common_pvt.exceptions_tab
40 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
41 ,x_return_status OUT NOCOPY VARCHAR2)
42 IS
43 CURSOR cur_get_matl (v_batch_id NUMBER)
44 IS
45 SELECT *
46 FROM gme_material_details
47 WHERE batch_id = v_batch_id
48 ORDER BY line_no;
49
50 CURSOR cur_get_batch_status (v_batch_id NUMBER)
51 IS
52 SELECT batch_status
53 FROM gme_batch_header
54 WHERE batch_id = v_batch_id;
55
56 CURSOR cur_get_step_status (v_material_detail_id NUMBER)
57 IS
58 SELECT s.batchstep_no, step_status
59 FROM gme_batch_steps s, gme_batch_step_items i
60 WHERE s.batchstep_id = i.batchstep_id
61 AND i.material_detail_id = v_material_detail_id;
62
63 CURSOR cur_check_zero_qty_line (v_batch_id NUMBER, v_batch_status NUMBER)
64 IS
65 SELECT 1
66 FROM gme_material_details
67 WHERE batch_id = v_batch_id
68 GROUP BY line_type
69 HAVING SUM (DECODE (v_batch_status, 1, plan_qty, 2, wip_plan_qty) ) =
70 0
71 AND line_type IN (1, -1);
72
73 l_api_name CONSTANT VARCHAR2 (30) := 'SCALE_BATCH';
74 l_material_tab gme_common_pvt.material_details_tab;
75 x_material_tbl gme_common_pvt.material_details_tab;
76 i NUMBER := 0;
77 l_batch_status NUMBER;
78 l_step_no NUMBER;
79 l_step_status NUMBER (5);
80 l_dummy NUMBER;
81 sum_qty_zero EXCEPTION;
82 step_closed_err EXCEPTION;
83 BEGIN
84 x_return_status := fnd_api.g_ret_sts_success;
85
86 -- fetch batch status
87 OPEN cur_get_batch_status (p_batch_header_rec.batch_id);
88
89 FETCH cur_get_batch_status
90 INTO l_batch_status;
91 CLOSE cur_get_batch_status;
92
93 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
94 gme_debug.put_line ('Batch status is' || l_batch_status );
95 END IF;
96
97 -- check if sum of total qty of prod or ing is 0
98 OPEN cur_check_zero_qty_line (p_batch_header_rec.batch_id
99 ,l_batch_status);
100
101 FETCH cur_check_zero_qty_line
102 INTO l_dummy;
103
104 IF cur_check_zero_qty_line%FOUND THEN
105 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
106 gme_debug.put_line ('SCALE Batch : sum of either line type is 0');
107 END IF;
108
109 gme_common_pvt.log_message ('GME_SUM_QTY_ZERO');
110
111 CLOSE cur_check_zero_qty_line;
112
113 RAISE sum_qty_zero;
114 END IF;
115
116 CLOSE cur_check_zero_qty_line;
117
118 FOR l_rec IN cur_get_matl (p_batch_header_rec.batch_id) LOOP
119 /* If any of the steps associated with the material line is closed */
120 IF l_rec.line_type = -1 AND l_rec.scale_type <> 0 THEN
121 OPEN cur_get_step_status (l_rec.material_detail_id);
122
123 FETCH cur_get_step_status
124 INTO l_step_no, l_step_status;
125
126 CLOSE cur_get_step_status;
127
128 IF NVL (l_step_status, 0) = 4 THEN
129 gme_common_pvt.log_message ('GME_STEP_CLOSED_ERR'
130 ,'STEP_NO'
131 ,l_step_no);
132 RAISE step_closed_err;
133 END IF;
134 END IF; /* IF l_rec.line_type = -1 AND */
135
136 i := i + 1;
137 l_material_tab (i) := l_rec;
138 END LOOP;
139
140 gme_scale_batch_pvt.scale_batch
141 (p_batch_header_rec => p_batch_header_rec
142 ,p_material_tbl => l_material_tab
143 ,p_scale_factor => p_scale_factor
144 ,p_primaries => p_primaries
145 ,p_qty_type => p_qty_type
146 ,p_validity_rule_id => p_validity_rule_id
147 ,p_enforce_vldt_check => p_enforce_vldt_check
148 ,p_recalc_dates => p_recalc_dates
149 , -- Navin
150 p_use_workday_cal => p_use_workday_cal
151 ,p_contiguity_override => p_contiguity_override
152 ,x_exception_material_tbl => x_exception_material_tbl
153 ,x_batch_header_rec => x_batch_header_rec
154 , -- Navin
155 x_material_tbl => x_material_tbl
156 ,x_return_status => x_return_status);
157 EXCEPTION
158 WHEN sum_qty_zero OR step_closed_err THEN
159 x_return_status := fnd_api.g_ret_sts_error;
160 WHEN OTHERS THEN
161 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
162 x_return_status := fnd_api.g_ret_sts_unexp_error;
163 END scale_batch;
164
165 /* This procedure is over-loaded, so that user can
166 either call it with Just the batch_header or
167 bach_header and material_details table
168 p_batch_header_rec should have all the columns populated
169 */
170 PROCEDURE scale_batch (
171 p_batch_header_rec IN gme_batch_header%ROWTYPE
172 ,p_material_tbl IN gme_common_pvt.material_details_tab
173 ,p_scale_factor IN NUMBER
174 ,p_primaries IN VARCHAR2
175 ,p_qty_type IN NUMBER
176 ,p_validity_rule_id IN NUMBER
177 ,p_enforce_vldt_check IN VARCHAR2
178 ,p_recalc_dates IN VARCHAR2
179 ,p_use_workday_cal IN VARCHAR2
180 ,p_contiguity_override IN VARCHAR2
181 ,x_exception_material_tbl OUT NOCOPY gme_common_pvt.exceptions_tab
182 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
183 ,x_material_tbl OUT NOCOPY gme_common_pvt.material_details_tab
184 ,x_return_status OUT NOCOPY VARCHAR2)
185 IS
186 /* Local variables */
187 l_api_name CONSTANT VARCHAR2 (30) := 'SCALE_BATCH';
188 l_row_count NUMBER;
189 l_recipe_validity_rule gmd_recipe_validity_rules%ROWTYPE;
190 l_scale_tab gmd_common_scale.scale_tab;
191 x_scale_tab gmd_common_scale.scale_tab;
192 l_batch_header gme_batch_header%ROWTYPE;
193 l_resch_batch_header_rec gme_batch_header%ROWTYPE;
194 l_in_batch_header gme_batch_header%ROWTYPE;
195 l_batch_step gme_batch_steps%ROWTYPE;
196 l_in_batch_step gme_batch_steps%ROWTYPE;
197 l_return_status VARCHAR2 (1);
198 l_return_status_1 VARCHAR2 (1);
199 l_return_status_2 VARCHAR2 (1);
200 l_count NUMBER;
201 l_item_id NUMBER (10);
202 l_scale_factor NUMBER;
203 l_cnt NUMBER := 0;
204 l_from_uom sy_uoms_mst.um_code%TYPE;
205 l_to_uom sy_uoms_mst.um_code%TYPE;
206 l_item_no ic_item_mst.item_no%TYPE;
207 l_routing_scale_factor NUMBER;
208 before_scale_qty NUMBER;
209 after_scale_qty NUMBER;
210 l_list VARCHAR2 (2000);
211 l_total_prim_prod_qty NUMBER := 0;
212 l_temp_qty NUMBER := 0;
213 l_mat_row_count NUMBER;
214 l_rsc_row_count NUMBER;
215 l_validity_rule_id NUMBER;
216 l_enforce_vldt_check VARCHAR2 (1);
217 l_is_charge_associated NUMBER;
218 l_use_workday_cal VARCHAR2 (1);
219 l_reserved_qty NUMBER;
220 l_material_details_qty NUMBER;
221 l_new_mvord_qty NUMBER;
222 l_actual_reserved_qty NUMBER;
223 l_actual_reserved_mvord_qty NUMBER;
224 l_required_qty NUMBER;
225 l_actual_qty NUMBER;
226 l_exception_qty NUMBER;
227 l_index NUMBER := 0;
228 l_ingr_req_date DATE; --nsinghi bug#5674398
229 -- Exceptions --
230 invalid_status EXCEPTION;
231 invalid_validity_rule EXCEPTION;
232 expected_error EXCEPTION;
233 load_trans_failed EXCEPTION;
234 batch_fetch_error EXCEPTION;
235 error_upd_default_tran EXCEPTION;
236 uom_conversion_error EXCEPTION;
237 scale_batch_failed EXCEPTION;
238 scale_phant_fail EXCEPTION;
239 error_updating_steps EXCEPTION;
240 batch_step_fetch_err EXCEPTION;
241 trans_qty_err EXCEPTION;
242 material_save_failed EXCEPTION;
243 scale_step_and_rsrc_error EXCEPTION;
244 clear_chg_error EXCEPTION;
245 reschedule_batch_fail EXCEPTION;
246 reduce_reservation_fail EXCEPTION; -- 4944024
247
248 --FPBug#4351032
249 l_formula_tbl GMDFMVAL_PUB.formula_detail_tbl;
250
251 -- Database cursors for various tables
252 CURSOR recipe_validity_rule_cursor (v_validity_rule_id IN NUMBER)
253 IS
254 SELECT *
255 FROM gmd_recipe_validity_rules
256 WHERE recipe_validity_rule_id = v_validity_rule_id;
257
258 --Cursor to get status type of the validity_rule_status.
259 CURSOR cur_validity_status_type (v_validity_rule_status VARCHAR2)
260 IS
261 SELECT status_type
262 FROM gmd_status
263 WHERE status_code = v_validity_rule_status;
264
265 l_status_type gmd_status.status_type%TYPE;
266
267 CURSOR cur_item_no (v_item_id NUMBER, v_org_id NUMBER)
268 IS
269 SELECT concatenated_segments
270 FROM mtl_system_items_kfv
271 WHERE inventory_item_id = v_item_id AND organization_id = v_org_id;
272
273 CURSOR cur_get_formula_matl (v_formulaline_id NUMBER)
274 IS
275 SELECT qty, scale_type, scale_multiple, scale_rounding_variance
276 ,rounding_direction, contribute_yield_ind, inventory_item_id
277 ,detail_uom, line_no, line_type
278 FROM fm_matl_dtl
279 WHERE formulaline_id = v_formulaline_id;
280
281 CURSOR cur_get_matl (v_batch_id NUMBER)
282 IS
283 SELECT *
284 FROM gme_material_details
285 WHERE batch_id = v_batch_id
286 ORDER BY line_no;
287
288 CURSOR cur_item_step_asso (v_batch_id NUMBER)
289 IS
290 SELECT DISTINCT batchstep_id
291 FROM gme_batch_step_items
292 WHERE batch_id = v_batch_id;
293
294 CURSOR cur_is_charge_associated (v_batch_id NUMBER)
295 IS
296 SELECT 1
297 FROM DUAL
298 WHERE EXISTS (SELECT 1
299 FROM gme_batch_step_charges
300 WHERE batch_id = v_batch_id);
301 BEGIN
302 x_return_status := fnd_api.g_ret_sts_success;
303
304 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
305 gme_debug.put_line ('Scale factor is ' || p_scale_factor);
306 END IF;
307
308 --Deleting the charges associated with the step on batch scaling.
309 OPEN cur_is_charge_associated (p_batch_header_rec.batch_id);
310
311 FETCH cur_is_charge_associated
312 INTO l_is_charge_associated;
313
314 IF cur_is_charge_associated%FOUND THEN
315 CLOSE cur_is_charge_associated;
316
317 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
318 gme_debug.put_line ( g_pkg_name
319 || '.'
320 || l_api_name
321 || 'before call to clear charges procedure');
322 END IF;
323
324 gme_batch_step_chg_pvt.clear_charges
325 (p_batch_id => p_batch_header_rec.batch_id
326 ,x_return_status => l_return_status);
327
328 IF l_return_status <> x_return_status THEN
329 RAISE clear_chg_error;
330 END IF;
331 ELSE
332 CLOSE cur_is_charge_associated;
333 END IF;
334
335 FOR i IN 1 .. p_material_tbl.COUNT LOOP
336 IF p_qty_type = 0 THEN
337 IF p_material_tbl (i).formulaline_id IS NOT NULL THEN
338 /* This loop is not needed really. We will always get only one record. */
339 --FPBug#4351032 Begin
340 --nsinghi Bug#5674398 Pass the ingredient substitution date rather than the batch plan start date
341 -- for pDate parameter
342 l_ingr_req_date := gme_api_grp.get_ingr_sub_date(p_material_tbl(i).batch_id,p_material_tbl(i).material_detail_id);
343
344 gmdfmval_pub.get_substitute_line_item(
345 pFormulaline_id => p_material_tbl(i).formulaline_id
346 ,pItem_id => p_material_tbl(i).inventory_item_id
347 ,pQty => p_material_tbl(i).plan_qty
348 ,pUom => p_material_tbl(i).dtl_um
349 ,pScale_multiple => p_material_tbl(i).scale_multiple
350 ,pDate => l_ingr_req_date
351 ,xFormulaDetail_tbl => l_formula_tbl);
352
353 IF g_debug <= gme_debug.g_log_statement THEN
354 gme_debug.put_line('Orig '||p_material_tbl(i).formulaline_id||' '||p_material_tbl(i).inventory_item_id||' '||
355 p_material_tbl(i).plan_qty ||' '||p_material_tbl(i).dtl_um);
356
357 gme_debug.put_line('New '||p_material_tbl(i).formulaline_id||' '||l_formula_tbl(1).item_id||' '||
358 l_formula_tbl(1).qty ||' '||l_formula_tbl(1).item_um);
359 END IF;
360
361 IF p_material_tbl(i).inventory_item_id <> l_formula_tbl(1).inventory_item_id THEN
362 l_scale_tab (i).qty := 0;
363 ELSE
364 l_scale_tab (i).qty := l_formula_tbl(1).qty;
365 END IF;
366
367 l_scale_tab (i).scale_multiple := l_formula_tbl(1).scale_multiple;
368 l_scale_tab (i).detail_uom := l_formula_tbl(1).detail_uom;
369 l_scale_tab (i).scale_rounding_variance :=
370 p_material_tbl(i).scale_rounding_variance;
371 l_scale_tab (i).rounding_direction :=
372 p_material_tbl(i).rounding_direction;
373 l_scale_tab (i).scale_type := p_material_tbl(i).scale_type;
374 l_scale_tab (i).contribute_yield_ind :=
375 p_material_tbl(i).contribute_yield_ind;
376 l_scale_tab (i).inventory_item_id := p_material_tbl(i).inventory_item_id;
377 l_scale_tab (i).line_type := p_material_tbl(i).line_type;
378 l_scale_tab (i).line_no := p_material_tbl(i).line_no;
379 --siva FPBug#4351032 End /* cur_get_formula_matl */
380 ELSE
381 /* This line is added in the batch, not available in the formula */
382 l_scale_tab (i).qty := 0;
383 l_scale_tab (i).detail_uom := p_material_tbl (i).dtl_um;
384 l_scale_tab (i).scale_type := p_material_tbl (i).scale_type;
385 l_scale_tab (i).contribute_yield_ind :=
386 p_material_tbl (i).contribute_yield_ind;
387 l_scale_tab (i).scale_multiple :=
388 p_material_tbl (i).scale_multiple;
389 l_scale_tab (i).scale_rounding_variance :=
390 p_material_tbl (i).scale_rounding_variance;
391 l_scale_tab (i).rounding_direction :=
392 p_material_tbl (i).rounding_direction;
393 l_scale_tab (i).line_no := p_material_tbl (i).line_no;
394 l_scale_tab (i).line_type := p_material_tbl (i).line_type;
395 l_scale_tab (i).inventory_item_id :=
396 p_material_tbl (i).inventory_item_id;
397 END IF;
398 ELSE /* p_type = 1 */
399 IF p_batch_header_rec.batch_status = 0 /* NEW */
400 OR p_batch_header_rec.batch_status = 1 /* PENDING */ THEN
401 l_scale_tab (i).qty := p_material_tbl (i).plan_qty;
402 ELSIF p_batch_header_rec.batch_status = 2 /* WIP */ THEN
403 l_scale_tab (i).qty := p_material_tbl (i).wip_plan_qty;
404 ELSE
405 gme_common_pvt.log_message ('INVALID_BATCH_STATUS'
406 ,'PROCESS'
407 ,'Scaling');
408 RAISE invalid_status;
409 END IF;
410
411 l_scale_tab (i).detail_uom := p_material_tbl (i).dtl_um;
412 l_scale_tab (i).scale_type := p_material_tbl (i).scale_type;
413 l_scale_tab (i).contribute_yield_ind :=
414 p_material_tbl (i).contribute_yield_ind;
415 l_scale_tab (i).scale_multiple :=
416 p_material_tbl (i).scale_multiple;
417 l_scale_tab (i).scale_rounding_variance :=
418 p_material_tbl (i).scale_rounding_variance;
419 l_scale_tab (i).rounding_direction :=
420 p_material_tbl (i).rounding_direction;
421 l_scale_tab (i).line_no := p_material_tbl (i).line_no;
422 l_scale_tab (i).line_type := p_material_tbl (i).line_type;
423 l_scale_tab (i).inventory_item_id :=
424 p_material_tbl (i).inventory_item_id;
425 END IF; /* p_qty_type = 0 */
426 END LOOP; /* p_material_tbl.COUNT LOOP */
427
428 x_material_tbl := p_material_tbl;
429 l_row_count := p_material_tbl.COUNT;
430 -- Get validity_rule row for batch
431 /* LCF Changes */
432 l_validity_rule_id :=
433 NVL (p_batch_header_rec.recipe_validity_rule_id, p_validity_rule_id);
434
435 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
436 gme_debug.put_line ( g_pkg_name
437 || '.'
438 || l_api_name
439 || 'Validity Rule ID '
440 || l_validity_rule_id);
441 END IF;
442
443 IF l_validity_rule_id IS NOT NULL THEN
444 l_enforce_vldt_check := NVL (p_enforce_vldt_check, 'T');
445
446 OPEN recipe_validity_rule_cursor (l_validity_rule_id);
447
448 FETCH recipe_validity_rule_cursor
449 INTO l_recipe_validity_rule;
450
451 CLOSE recipe_validity_rule_cursor;
452
453 IF l_recipe_validity_rule.recipe_validity_rule_id IS NULL
454 OR l_recipe_validity_rule.delete_mark = 1 THEN
455 -- Report error that the rule passed in is invalid
456 -- This is a fatal error and there is no point continuing
457 gme_common_pvt.log_message ('GME_API_INVALID_RULE');
458 RAISE expected_error;
459 ELSE
460 --Prevent user from scaling a pending batch whose
461 --validity rule is either obsoleted or put on hold. But user is allowed
462 --to scale a WIP batch, a message informing user that validity rule is
463 --obsoleted/hold is logged though.
464 OPEN cur_validity_status_type
465 (l_recipe_validity_rule.validity_rule_status);
466
467 FETCH cur_validity_status_type
468 INTO l_status_type;
469
470 CLOSE cur_validity_status_type;
471
472 IF l_status_type IN ('1000', '800') THEN
473 IF p_batch_header_rec.batch_status = 1
474 AND p_batch_header_rec.batch_type = 0 THEN
475 gme_common_pvt.log_message ('GME_VALIDITY_OBSO_OR_ONHOLD');
476 RAISE expected_error;
477 ELSIF p_batch_header_rec.batch_status = 2
478 AND p_batch_header_rec.batch_type = 0 THEN
479 gme_common_pvt.log_message ('GME_VALIDITY_OBSO_OR_ONHOLD1');
480 END IF;
481 END IF;
482 END IF;
483 ELSE
484 l_enforce_vldt_check := 'F';
485
486 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
487 gme_debug.put_line
488 ( g_pkg_name
489 || '.'
490 || l_api_name
491 || 'Validity Rule ID NOT FOUND. This must be LCF batch');
492 END IF;
493 END IF; /* l_validity_rule_id IS NOT NULL */
494
495 -- call for getting the total_output qty before the scaling.
496 IF p_batch_header_rec.batch_status <> 0
497 AND p_batch_header_rec.poc_ind = 'Y' THEN
498 before_scale_qty :=
499 gme_scale_batch_pvt.get_total_qty (p_material_tbl
500 ,p_batch_header_rec);
501 END IF;
502
503 gmd_common_scale.scale (p_scale_tab => l_scale_tab
504 ,p_orgn_id => p_batch_header_rec.organization_id
505 ,p_scale_factor => p_scale_factor
506 ,p_primaries => p_primaries
507 ,x_scale_tab => x_scale_tab
508 ,x_return_status => x_return_status);
509 l_total_prim_prod_qty := 0;
510
511 IF x_return_status = fnd_api.g_ret_sts_success THEN
512 FOR l_row_number IN 1 .. l_row_count LOOP
513 /* Check that scaled quantity is within validity rules */
514 IF l_enforce_vldt_check = 'T' THEN
515 IF p_material_tbl (l_row_number).inventory_item_id =
516 l_recipe_validity_rule.inventory_item_id
517 AND p_material_tbl (l_row_number).line_type = 1 THEN
518 /* Primary Product */
519 /* Convert QTY to validity rule UOM */
520 IF p_material_tbl (l_row_number).dtl_um =
521 l_recipe_validity_rule.item_um THEN
522 l_total_prim_prod_qty :=
523 l_total_prim_prod_qty
524 + x_scale_tab (l_row_number).qty;
525 ELSE
526 l_temp_qty :=
527 inv_convert.inv_um_convert
528 (p_material_tbl (l_row_number).inventory_item_id
529 ,5
530 ,x_scale_tab (l_row_number).qty
531 ,p_material_tbl (l_row_number).dtl_um
532 ,l_recipe_validity_rule.detail_uom
533 ,NULL
534 ,NULL);
535
536 IF l_temp_qty < 0 THEN
537 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
538 gme_debug.put_line
539 ('Failed in UOM Conv from formula product UOM to routing UOM');
540 END IF;
541
542 l_item_id :=
543 p_material_tbl (l_row_number).inventory_item_id;
544 l_from_uom := p_material_tbl (l_row_number).dtl_um;
545 l_to_uom := l_recipe_validity_rule.detail_uom;
546 RAISE uom_conversion_error;
547 ELSE
548 l_total_prim_prod_qty :=
549 l_total_prim_prod_qty + l_temp_qty;
550 END IF; /* l_temp_qty < 0 */
551 END IF;
552 /* p_material_details (l_row_number).item_um = l_recipe_validity_rule.item_um */
553 END IF;
554 /* p_material_tbl (l_row_number).item_id = l_recipe_validity_rule.item_id */
555 END IF; /* l_enforce_vldt_check = TRUE */
556
557 -- Round the qtys to 5 decimal places.
558 IF p_batch_header_rec.batch_status = 0 /* NEW */ THEN
559 x_material_tbl (l_row_number).original_qty :=
560 ROUND (x_scale_tab (l_row_number).qty, 5);
561 x_material_tbl (l_row_number).plan_qty :=
562 ROUND (x_scale_tab (l_row_number).qty, 5);
563 ELSIF p_batch_header_rec.batch_status = 1 /* PENDING */ THEN
564 x_material_tbl (l_row_number).plan_qty :=
565 ROUND (x_scale_tab (l_row_number).qty, 5);
566 ELSE
567 x_material_tbl (l_row_number).wip_plan_qty :=
568 ROUND (x_scale_tab (l_row_number).qty, 5);
569 END IF;
570
571 /* If scaling is not called from create batch */
572 IF p_batch_header_rec.batch_status <> 0 THEN
573 -- 4944024 BEGIN
574 -- If there is a decrease in anticipated yield,
575 -- then reservations associated to this supply
576 -- need to be decreased proportionately
577 -- ============================================
578 IF x_material_tbl(l_row_number).line_type <> -1 THEN
579 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
580 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Invoking relieve_prod_supply_resv ');
581 END IF;
582 gme_supply_res_pvt.relieve_prod_supply_resv
583 (p_matl_dtl_rec => x_material_tbl(l_row_number)
584 ,x_msg_count => l_count
585 ,x_msg_data => l_list
586 ,x_return_status => x_return_status);
587
588 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
589 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Relieve_Prod_Supply_Resv returns '||x_return_status);
590 END IF;
591
592 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
593 RAISE reduce_reservation_fail;
594 END IF;
595 END IF;
596 -- 4944024 END
597 IF NOT (gme_material_details_dbl.update_row
598 (x_material_tbl (l_row_number) ) ) THEN
599 RAISE material_save_failed;
600 ELSE
601 x_material_tbl (l_row_number).last_update_date :=
602 gme_common_pvt.g_timestamp;
603 x_material_tbl (l_row_number).last_updated_by :=
604 gme_common_pvt.g_user_ident;
605 x_material_tbl (l_row_number).last_update_login :=
606 gme_common_pvt.g_login_id;
607 END IF;
608 END IF;
609 END LOOP; /* l_row_number */
610
611 /* Check the total primary product qty */
612 IF l_enforce_vldt_check = 'T' THEN
613 IF l_total_prim_prod_qty > l_recipe_validity_rule.max_qty
614 OR l_total_prim_prod_qty < l_recipe_validity_rule.min_qty THEN
615 gme_common_pvt.log_message
616 ('GME_SCALED_QTY_EXCEED_LIMITS'
617 ,'SCALE_QTY'
618 , TO_CHAR (l_total_prim_prod_qty)
619 || ' '
620 || l_recipe_validity_rule.detail_uom
621 ,'MIN_QTY'
622 , TO_CHAR (l_recipe_validity_rule.min_qty)
623 || ' '
624 || l_recipe_validity_rule.detail_uom
625 ,'MAX_QTY'
626 , TO_CHAR (l_recipe_validity_rule.max_qty)
627 || ' '
628 || l_recipe_validity_rule.detail_uom);
629 RAISE invalid_validity_rule;
630 END IF;
631 END IF; /* l_enforce_vldt_check = TRUE */
632
633 -- call for getting the total_output qty after the scaling.
634 IF p_batch_header_rec.batch_status <> 0
635 AND p_batch_header_rec.poc_ind = 'Y' THEN
636 after_scale_qty :=
637 gme_scale_batch_pvt.get_total_qty (x_material_tbl
638 ,p_batch_header_rec);
639 END IF;
640
641 /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
642 l_batch_header.batch_id := p_material_tbl (1).batch_id;
643
644 /* If we are invoking the scale batch from the create batch api then */
645 /* we need not adjust the transactions as the create batch API will */
646 /* be doing the same - The batch id is not assigned to the material */
647 /* detail row when the scale batch is invoked in create batch API */
648 IF l_batch_header.batch_id IS NOT NULL THEN
649 IF NOT (gme_batch_header_dbl.fetch_row (p_batch_header_rec
650 ,l_batch_header) ) THEN
651 RAISE batch_fetch_error;
652 END IF;
653
654 x_batch_header_rec := l_batch_header;
655 /* loading material and resource transactions are done before deleting the
656 existing transactions in gme_inventory_txns_gtmp.
657 Previously this call was in trans_qty so loading is happening before deletion and api
658 is not working fine */
659 -- gme_trans_engine_util.load_mat_and_rsc_trans (
660 gme_trans_engine_util.load_rsrc_trans
661 (x_return_status => x_return_status
662 ,x_rsc_row_count => l_rsc_row_count
663 ,p_batch_row => p_batch_header_rec);
664
665 IF x_return_status <> fnd_api.g_ret_sts_success THEN
666 RAISE load_trans_failed;
667 END IF;
668
669 /* Update POC Data if steps are associated to a material line */
670 /* First fetch all the steps associated to a batch(all material lines) */
671 /* Checking for the new batch and batch with routing only */
672 /* we are using p_batch_header_rec for batch status to check whether batch has been created or not*/
673 IF p_batch_header_rec.batch_status <> 0
674 AND l_batch_header.poc_ind = 'Y' THEN
675 IF l_batch_header.automatic_step_calculation = 1 THEN
676 FOR l_cur_item_step_asso IN
677 cur_item_step_asso (l_batch_header.batch_id) LOOP
678 l_batch_step.batchstep_id :=
679 l_cur_item_step_asso.batchstep_id;
680 l_in_batch_step := l_batch_step;
681 gme_update_step_qty_pvt.update_step_qty
682 (l_in_batch_step
683 ,l_count
684 ,l_list
685 ,l_return_status
686 ,l_batch_step);
687
688 IF l_return_status <> x_return_status THEN
689 RAISE error_updating_steps;
690 END IF;
691
692 IF NOT (gme_batch_steps_dbl.fetch_row (l_batch_step
693 ,l_batch_step) ) THEN
694 RAISE batch_step_fetch_err;
695 END IF;
696 END LOOP; /* l_cur_item_step_asso */
697 ELSE /* l_batch_header.automatic_step_calculation <> 1 */
698 IF after_scale_qty IS NOT NULL
699 AND before_scale_qty IS NOT NULL THEN
700 l_routing_scale_factor :=
701 after_scale_qty / before_scale_qty;
702 ELSE
703 l_routing_scale_factor := 1;
704 END IF;
705
706 scale_step_and_rsrc
707 (p_batch_id => l_batch_header.batch_id
708 ,p_routing_scale_factor => l_routing_scale_factor
709 ,x_return_status => x_return_status);
710
711 IF x_return_status <> fnd_api.g_ret_sts_success THEN
712 RAISE scale_step_and_rsrc_error;
713 END IF;
714 END IF; /* automatic_step_calculation */
715 END IF; /* for new batch*/
716
717 /* Handling return status */
718 IF x_return_status <> fnd_api.g_ret_sts_success THEN
719 RAISE trans_qty_err;
720 END IF;
721
722 FOR line_index IN 1 .. x_material_tbl.COUNT LOOP
723 /* Now we have to scale the phantom batch if it exists
724 and also that the scale type is not FIXED for the line*/
725 IF NVL (x_material_tbl (line_index).phantom_id, 0) > 0
726 AND (x_material_tbl (line_index).scale_type <> 0) THEN
727 IF p_batch_header_rec.batch_status > 1 THEN
728 l_scale_factor :=
729 x_material_tbl (line_index).wip_plan_qty
730 / p_material_tbl (line_index).wip_plan_qty;
731 ELSE
732 l_scale_factor :=
733 x_material_tbl (line_index).plan_qty
734 / p_material_tbl (line_index).plan_qty;
735 END IF;
736
737 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
738 gme_debug.put_line
739 ( 'Calling scale phantom with Scale factor '
740 || l_scale_factor);
741 END IF;
742
743 l_in_batch_header.batch_id :=
744 x_material_tbl (line_index).phantom_id;
745
746 IF NOT (gme_batch_header_dbl.fetch_row (l_in_batch_header
747 ,l_batch_header) ) THEN
748 RAISE batch_fetch_error;
749 END IF;
750
751 x_batch_header_rec := l_batch_header;
752 /* FPBug#4648936
753 Phantom batch will be scaled based on plan quantity always */
754 gme_scale_batch_pvt.scale_batch
755 (p_batch_header_rec => l_batch_header
756 ,p_scale_factor => l_scale_factor
757 ,p_primaries => 'OUTPUTS'
758 ,p_qty_type => 1 --p_qty_type
759 ,p_validity_rule_id => l_batch_header.recipe_validity_rule_id
760 ,p_enforce_vldt_check => p_enforce_vldt_check
761 ,p_recalc_dates => p_recalc_dates
762 , -- Navin
763 p_use_workday_cal => p_use_workday_cal
764 ,p_contiguity_override => p_contiguity_override
765 ,x_exception_material_tbl => x_exception_material_tbl
766 ,x_batch_header_rec => x_batch_header_rec
767 , -- Navin
768 x_return_status => l_return_status);
769
770 /* Handling return status */
771 IF l_return_status <> fnd_api.g_ret_sts_success THEN
772 RAISE scale_phant_fail;
773 END IF;
774 END IF;
775 /* IF NVL (x_material_tbl (line_index).phantom_id, 0) > 0 AND */
776 END LOOP; /* FOR line_index IN 1 .. x_material_tbl.COUNT */
777 END IF; /* IF l_batch_header.batch_id IS NOT NULL THEN */
778 ELSE /* IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN */
779 RAISE scale_batch_failed;
780 END IF;
781
782 /****
783 START 10Feb05 Navin Sinha Made changes for GME_Scale_Batch_Theoretical_Yield_TD.
784 *****/
785 IF p_batch_header_rec.batch_status <> 0 THEN
786 IF p_recalc_dates = 'T' THEN
787 l_use_workday_cal := p_use_workday_cal;
788
789 IF gme_common_pvt.g_calendar_code IS NULL
790 AND p_use_workday_cal = fnd_api.g_true THEN
791 gme_common_pvt.log_message ('GME_NO_WORKDAY_CAL_ASSOCIATED');
792 -- Navin: PENDING: NEW MESSAGE TO BE REGISTERED. Workday calendar is not associated to the organization.
793 l_use_workday_cal := fnd_api.g_false;
794 -- workday_cal cannot be used.
795 END IF;
796 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
797 gme_debug.put_line('calling reschedule_batch'||p_batch_header_rec.batch_id);
798 END IF;
799
800 -- Call reschedule_batch for plan_start_date for the batch.
801 -- Pass the p_use_workday_cal and p_contiguity_override to it.
802 -- Navin: PENDING: Verify who is doing the cahnges to gme_api_reschedule_batch.reschedule_batch
803 l_resch_batch_header_rec := p_batch_header_rec ;
804 /*8515551 starts*/
805 IF (p_batch_header_rec.batch_status <> 2) THEN
806 l_resch_batch_header_rec.plan_cmplt_date := null ;
807 END IF;
808 /*8515551 ends*/
809 IF l_resch_batch_header_rec.parentline_id IS NULL THEN
810
811 gme_reschedule_batch_pvt.reschedule_batch
812
813 (p_batch_header_rec => l_resch_batch_header_rec
814 ,p_use_workday_cal => l_use_workday_cal
815 ,p_contiguity_override => p_contiguity_override
816 ,x_batch_header_rec => x_batch_header_rec
817 ,x_return_status => l_return_status);
818
819 -- Check return status and raise error if needed.
820 IF l_return_status <> fnd_api.g_ret_sts_success THEN
821 RAISE reschedule_batch_fail;
822 END IF;
823 END IF; /* parentline_id IS NOT NULL THEN */
824 END IF; /* IF P_recalc_dates = 1 THEN */
825
826 l_index := 0;
827 l_return_status_1 := NULL;
828 l_return_status_2 := NULL;
829
830 -- Get Material Details Quantity
831 -- Loop through all the records of x_material_tbl
832 FOR mtl_dtl_index IN 1 .. x_material_tbl.COUNT LOOP
833 -- Based on the batch status of pending or WIP, the plan_qty or wip_plan_qty will be considered.
834 IF p_batch_header_rec.batch_status = 1 /* Pending */ THEN
835 l_material_details_qty :=
836 NVL (x_material_tbl (mtl_dtl_index).plan_qty, 0);
837 ELSIF p_batch_header_rec.batch_status = 2 /* WIP */ THEN
838 l_material_details_qty :=
839 NVL (x_material_tbl (mtl_dtl_index).wip_plan_qty, 0);
840 END IF;
841 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
842 gme_debug.put_line('calling scale for mat'||to_char(x_material_tbl.COUNT));
843 END IF;
844 -- In case of scale down the batch, the user will be informed for the excess actual_qty
845 -- or reserved _qty compared to plan_qty or wip_plan_qty.
846 -- The return staus to be assigned in such case is: T- if actual_qty is in excess
847 -- OR N - if reserved_qty is in excess.
848
849 -- Check for actual_qty in case batch is scaled down.
850 l_actual_qty := 0;
851 l_actual_qty := NVL (x_material_tbl (mtl_dtl_index).actual_qty, 0);
852
853 IF l_actual_qty > l_material_details_qty
854 AND x_material_tbl (mtl_dtl_index).phantom_type = 0
855 AND x_material_tbl (mtl_dtl_index).line_type IN (-1, 1) THEN
856 -- only for ingredents and products, not for phantom
857 l_return_status_1 := 'T';
858 l_exception_qty := l_actual_qty - l_material_details_qty;
859 -- The gme_excpetions_tbl will be populated with the material_detail_line info.
860 -- The exception_qty will be excess of reserved_qty or actual_qty from the plan_qty or wip_plan_qty.
861 -- It is tbl, as we can have more than one material_detail line. It is populated only if exception exists.
862 -- Populat x_exception_material_tbl.
863 l_index := l_index + 1;
864 x_exception_material_tbl (l_index).organization_id :=
865 x_material_tbl (mtl_dtl_index).organization_id;
866 x_exception_material_tbl (l_index).material_detail_id :=
867 x_material_tbl (mtl_dtl_index).material_detail_id;
868 x_exception_material_tbl (l_index).transacted_qty :=
869 l_material_details_qty;
870 x_exception_material_tbl (l_index).exception_qty :=
871 l_exception_qty;
872 x_exception_material_tbl (l_index).batch_id :=
873 x_material_tbl (mtl_dtl_index).batch_id;
874 END IF; /* l_actual_qty > l_material_details_qty */
875
876 -- Check for reserved_qty in case batch is scaled down.
877 l_reserved_qty := 0;
878 /* call to gme_common_pvt.get_reserved_qty() once */
879
880 -- l_reserved_qty := gme_common_pvt.get_reserved_qty(); --- Pass proper parameters .. Check compilation error.
881 gme_reservations_pvt.get_reserved_qty(p_mtl_dtl_rec => x_material_tbl (mtl_dtl_index),
882 x_reserved_qty => l_reserved_qty,
883 x_return_status => l_return_status);
884
885 l_required_qty :=
886 NVL (l_material_details_qty, 0)
887 - NVL (l_actual_qty, 0);
888
889 IF l_reserved_qty > l_required_qty
890 AND x_material_tbl (mtl_dtl_index).phantom_type = 0
891 AND x_material_tbl (mtl_dtl_index).line_type = -1 THEN
892 -- only for ingredents, not for phantom
893 l_return_status_2 := 'N';
894 l_exception_qty := l_reserved_qty - l_required_qty;
895 -- The gme_excpetions_tbl will be populated with the material_detail_line info.
896 -- The exception_qty will be excess of reserved_qty or actual_qty from the plan_qty or wip_plan_qty.
897 -- It is tbl, as we can have more than one material_detail line. It is populated only if exception exists.
898 -- Populat x_exception_material_tbl.
899 l_index := l_index + 1;
900 x_exception_material_tbl (l_index).organization_id :=
901 x_material_tbl (mtl_dtl_index).organization_id;
902 x_exception_material_tbl (l_index).material_detail_id :=
903 x_material_tbl (mtl_dtl_index).material_detail_id;
904 x_exception_material_tbl (l_index).transacted_qty :=
905 l_material_details_qty;
906 x_exception_material_tbl (l_index).exception_qty :=
907 l_exception_qty;
908 x_exception_material_tbl (l_index).batch_id :=
909 x_material_tbl (mtl_dtl_index).batch_id;
910 -- Reset l_required_qty to zero because no additional move order quantity is required.
911 l_required_qty := 0;
912 ELSE /* l_reserved_qty < l_required_qty */
913 l_required_qty := l_required_qty - l_reserved_qty;
914 -- For Future usages.
915 END IF; /* l_reserved_qty > l_material_details_qty */
916 END LOOP; /* FOR mtl_dtl_index IN 1 .. x_material_tbl.COUNT */
917
918 -- Check return_status and populate the error message.
919 IF NVL (l_return_status_1, '*') = 'T' THEN
920 x_return_status := 'W';
921 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
922 gme_debug.put_line('l_return_status_1'||to_char(l_return_status_1));
923 gme_debug.put_line('x_return_status'||to_char(x_return_status));
924 END IF;
925 gme_common_pvt.log_message ('GME_ACTUAL_QTY_IS_IN_EXCESS');
926 -- Navin: PENDING: NEW MESSAGE TO BE REGISTERED.: Actual quantity is in excess.
927 END IF;
928
929 IF NVL (l_return_status_2, '*') = 'N' THEN
930 x_return_status := 'W';
931 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
932 gme_debug.put_line('l_return_status_2'||to_char(l_return_status_1));
933 gme_debug.put_line('x_return_status'||to_char(x_return_status));
934 END IF;
935 gme_common_pvt.log_message ('GME_RESERVED_QTY_IS_IN_EXCESS');
936 -- Navin: PENDING: NEW MESSAGE TO BE REGISTERED.: Reserved quantity is in excess.
937 END IF;
938
939 IF NVL (l_return_status_1, '*') = 'T'
940 AND NVL (l_return_status_2, '*') = 'N' THEN
941 x_return_status := 'W';
942 gme_common_pvt.log_message ('GME_ACTUAL_RESERV_QTY_EXCESS');
943 -- Navin: PENDING: NEW MESSAGE TO BE REGISTERED.: Actual or Reserved quantity is in excess.
944 END IF;
945 END IF; -- IF p_batch_header_rec.batch_status = 0
946 EXCEPTION
947 WHEN invalid_status OR expected_error THEN
948 x_return_status := fnd_api.g_ret_sts_error;
949 WHEN invalid_validity_rule OR scale_step_and_rsrc_error THEN
950 x_return_status := fnd_api.g_ret_sts_error;
951 WHEN batch_fetch_error THEN
952 x_return_status := fnd_api.g_ret_sts_error;
953 WHEN scale_phant_fail THEN
954 x_return_status := l_return_status;
955 WHEN scale_batch_failed OR load_trans_failed OR error_upd_default_tran OR error_updating_steps OR batch_step_fetch_err OR material_save_failed THEN
956 x_return_status := fnd_api.g_ret_sts_error;
957 WHEN clear_chg_error THEN
958 x_return_status := fnd_api.g_ret_sts_error;
959 WHEN reduce_reservation_fail THEN -- 4944024
960 x_return_status := fnd_api.g_ret_sts_error; -- 4944024
961 WHEN trans_qty_err THEN
962 gme_common_pvt.log_message ('GME_SCALE_UNMATCH_ALLOC');
963 WHEN uom_conversion_error THEN
964 OPEN cur_item_no (l_item_id, p_batch_header_rec.organization_id);
965
966 FETCH cur_item_no
967 INTO l_item_no;
968
969 CLOSE cur_item_no;
970
971 fnd_message.set_name ('GMI', 'IC_API_UOM_CONVERSION_ERROR');
972 fnd_message.set_token ('ITEM_NO', l_item_no);
973 fnd_message.set_token ('FROM_UOM', l_from_uom);
974 fnd_message.set_token ('TO_UOM', l_to_uom);
975 fnd_msg_pub.ADD;
976 WHEN reschedule_batch_fail THEN
977 x_return_status := 'C';
978 WHEN OTHERS THEN
979 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
980 x_return_status := fnd_api.g_ret_sts_unexp_error;
981 END scale_batch;
982
983 PROCEDURE theoretical_yield_batch (
984 p_batch_header_rec IN gme_batch_header%ROWTYPE
985 ,p_scale_factor IN NUMBER
986 ,x_return_status OUT NOCOPY VARCHAR2)
987 IS
988 /* Buffers for database reads/writes */
989 l_api_name CONSTANT VARCHAR2 (30) := 'THEORETICAL_YIELD_BATCH';
990 l_rec gme_material_details%ROWTYPE;
991 l_material_details gme_common_pvt.material_details_tab;
992 l_material_detail_ids gme_common_pvt.number_tab;
993 l_scale_tab gmd_common_scale.scale_tab;
994 l_in_scale_tab gmd_common_scale.scale_tab;
995 l_scale_factor NUMBER;
996 x_material_details gme_common_pvt.material_details_tab;
997 l_batch_header gme_batch_header%ROWTYPE;
998 l_routing_scale_factor NUMBER;
999 before_scale_qty NUMBER;
1000 after_scale_qty NUMBER;
1001 l_batch_step gme_batch_steps%ROWTYPE;
1002 l_in_batch_step gme_batch_steps%ROWTYPE;
1003 l_return_status VARCHAR2 (1);
1004 l_count NUMBER;
1005 l_list VARCHAR2 (2000);
1006 batch_fetch_error EXCEPTION;
1007 material_fetch_err EXCEPTION;
1008 trans_qty_err EXCEPTION;
1009 batch_step_fetch_err EXCEPTION;
1010 error_updating_steps EXCEPTION;
1011 material_save_failed EXCEPTION;
1012 scale_step_and_rsrc_error EXCEPTION;
1013 reschedule_batch_fail EXCEPTION;
1014
1015 CURSOR cur_batch_materials (v_batch_id NUMBER)
1016 IS
1017 SELECT material_detail_id
1018 FROM gme_material_details
1019 WHERE batch_id = v_batch_id
1020 ORDER BY line_no;
1021
1022 CURSOR cur_item_step_asso (v_batch_id NUMBER)
1023 IS
1024 SELECT DISTINCT batchstep_id
1025 FROM gme_batch_step_items
1026 WHERE batch_id = v_batch_id;
1027
1028 CURSOR cur_get_all_steps (v_batch_id NUMBER)
1029 IS
1030 SELECT batchstep_id
1031 FROM gme_batch_steps
1032 WHERE batch_id = v_batch_id;
1033
1034 CURSOR cur_get_step_status (v_material_detail_id NUMBER)
1035 IS
1036 SELECT s.batchstep_no, step_status
1037 FROM gme_batch_steps s, gme_batch_step_items i
1038 WHERE s.batchstep_id = i.batchstep_id
1039 AND i.material_detail_id = v_material_detail_id;
1040
1041 l_material_details_qty NUMBER;
1042 l_actual_qty NUMBER;
1043 l_exception_qty NUMBER;
1044 l_index NUMBER := 0;
1045 l_step_status NUMBER (5);
1046 l_step_no NUMBER (10);
1047 l_message_count NUMBER; -- 4944024
1048 l_message_list VARCHAR2 (2000); -- 4944024
1049 reduce_reservation_fail EXCEPTION; -- 4944024
1050 step_closed_err EXCEPTION;
1051 BEGIN
1052 -- gme_api_grp.set_timestamp;
1053 l_scale_factor := p_scale_factor;
1054
1055 /* Set the savepoint before proceeding */
1056 IF (NOT gme_batch_header_dbl.fetch_row (p_batch_header_rec
1057 ,l_batch_header) ) THEN
1058 RAISE batch_fetch_error;
1059 END IF;
1060
1061 x_return_status := fnd_api.g_ret_sts_success;
1062
1063 OPEN cur_batch_materials (p_batch_header_rec.batch_id);
1064
1065 FETCH cur_batch_materials
1066 BULK COLLECT INTO l_material_detail_ids;
1067
1068 CLOSE cur_batch_materials;
1069
1070 FOR i IN 1 .. l_material_detail_ids.COUNT LOOP
1071 l_rec.material_detail_id := l_material_detail_ids (i);
1072
1073 IF (NOT gme_material_details_dbl.fetch_row (l_rec, l_rec) ) THEN
1074 RAISE material_fetch_err;
1075 END IF;
1076
1077 /* Protect the user from performing theoretical yield */
1078 /* a batch if any of the steps associated with the material line is closed */
1079 IF l_rec.line_type <> -1 THEN
1080 /* Lets check if the step associated with the line is closed */
1081 OPEN cur_get_step_status (l_rec.material_detail_id);
1082
1083 FETCH cur_get_step_status
1084 INTO l_step_no, l_step_status;
1085
1086 CLOSE cur_get_step_status;
1087
1088 IF NVL (l_step_status, 0) = 4 THEN
1089 gme_common_pvt.log_message ('GME_STEP_CLOSED_ERR'
1090 ,'STEP_NO'
1091 ,l_step_no);
1092 RAISE step_closed_err;
1093 END IF; /* IF NVL(l_step_status, 0) = 4 */
1094 END IF; /* IF l_rec.line_type <> -1 */
1095
1096 l_material_details (i) := l_rec;
1097 l_scale_tab (i).line_no := l_rec.line_no;
1098 l_scale_tab (i).line_type := l_rec.line_type;
1099 l_scale_tab (i).inventory_item_id := l_rec.inventory_item_id;
1100
1101 IF l_batch_header.batch_status = 1 THEN
1102 l_scale_tab (i).qty := l_rec.plan_qty;
1103 ELSE
1104 l_scale_tab (i).qty := l_rec.wip_plan_qty;
1105 END IF;
1106
1107 l_scale_tab (i).detail_uom := l_rec.dtl_um;
1108 l_scale_tab (i).scale_type := l_rec.scale_type;
1109 l_scale_tab (i).contribute_yield_ind := l_rec.contribute_yield_ind;
1110 l_scale_tab (i).scale_multiple := l_rec.scale_multiple;
1111 l_scale_tab (i).scale_rounding_variance :=
1112 l_rec.scale_rounding_variance;
1113 l_scale_tab (i).rounding_direction := l_rec.rounding_direction;
1114 END LOOP;
1115
1116 IF l_batch_header.batch_status <> 0 AND l_batch_header.poc_ind = 'Y' THEN
1117 before_scale_qty :=
1118 gme_scale_batch_pvt.get_total_qty (l_material_details
1119 ,l_batch_header);
1120 END IF;
1121
1122 l_in_scale_tab := l_scale_tab;
1123 gmd_common_scale.theoretical_yield
1124 (p_scale_tab => l_in_scale_tab
1125 ,p_orgn_id => p_batch_header_rec.organization_id
1126 ,x_scale_tab => l_scale_tab
1127 ,p_scale_factor => l_scale_factor
1128 ,x_return_status => x_return_status);
1129
1130 IF x_return_status = fnd_api.g_ret_sts_success THEN
1131 x_material_details := l_material_details;
1132
1133 FOR i IN 1 .. l_material_details.COUNT LOOP
1134 IF l_batch_header.batch_status = 1 THEN
1135 x_material_details (i).plan_qty := l_scale_tab (i).qty;
1136 ELSE
1137 x_material_details (i).wip_plan_qty := l_scale_tab (i).qty;
1138 END IF;
1139
1140 IF NOT (gme_material_details_dbl.update_row
1141 (x_material_details (i) ) ) THEN
1142 RAISE material_save_failed;
1143 ELSE
1144 x_material_details (i).last_update_date :=
1145 gme_common_pvt.g_timestamp;
1146 x_material_details (i).last_updated_by :=
1147 gme_common_pvt.g_user_ident;
1148 x_material_details (i).last_update_login :=
1149 gme_common_pvt.g_login_id;
1150 END IF;
1151 -- 4944024 BEGIN
1152 -- If there is a decrease in anticipated yield,
1153 -- then reservations associated to this supply
1154 -- need to be decreased
1155 -- ===========================================
1156 IF x_material_details(i).line_type <> -1 THEN
1157 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1158 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Invoking relieve_prod_supply_resv ');
1159 END IF;
1160 gme_supply_res_pvt.relieve_prod_supply_resv
1161 (p_matl_dtl_rec => x_material_details(i)
1162 ,x_msg_count => l_message_count
1163 ,x_msg_data => l_message_list
1164 ,x_return_status => x_return_status);
1165
1166 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1167 RAISE reduce_reservation_fail;
1168 END IF;
1169 END IF;
1170 -- 4944024 END
1171 END LOOP;
1172
1173 IF l_batch_header.batch_status <> 0 AND l_batch_header.poc_ind = 'Y' THEN
1174 after_scale_qty :=
1175 gme_scale_batch_pvt.get_total_qty (x_material_details
1176 ,l_batch_header);
1177 END IF;
1178
1179 IF l_batch_header.batch_status <> 0 AND l_batch_header.poc_ind = 'Y' THEN
1180 IF l_batch_header.automatic_step_calculation = 1 THEN
1181 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1182 gme_debug.put_line ('GME:THEOR: Batch has ASQC');
1183 END IF;
1184
1185 FOR l_cur_item_step_asso IN
1186 cur_item_step_asso (l_batch_header.batch_id) LOOP
1187 l_batch_step.batchstep_id :=
1188 l_cur_item_step_asso.batchstep_id;
1189 l_in_batch_step := l_batch_step;
1190 gme_update_step_qty_pvt.update_step_qty (l_in_batch_step
1191 ,l_count
1192 ,l_list
1193 ,l_return_status
1194 ,l_batch_step);
1195
1196 IF l_return_status <> x_return_status THEN
1197 RAISE error_updating_steps;
1198 END IF;
1199
1200 IF NOT (gme_batch_steps_dbl.fetch_row (l_batch_step
1201 ,l_batch_step) ) THEN
1202 RAISE batch_step_fetch_err;
1203 END IF;
1204 END LOOP;
1205 ELSE
1206 IF after_scale_qty IS NOT NULL
1207 AND before_scale_qty IS NOT NULL THEN
1208 l_routing_scale_factor :=
1209 after_scale_qty / before_scale_qty;
1210 ELSE
1211 l_routing_scale_factor := 1;
1212 END IF;
1213
1214 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1215 gme_debug.put_line
1216 ( 'GME_THEOR:NOT ASQC:Routing scale factor '
1217 || l_routing_scale_factor);
1218 END IF;
1219
1220 scale_step_and_rsrc
1221 (p_batch_id => l_batch_header.batch_id
1222 ,p_routing_scale_factor => l_routing_scale_factor
1223 ,x_return_status => x_return_status);
1224
1225 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1226 RAISE scale_step_and_rsrc_error;
1227 END IF;
1228 END IF; /* automatic_step_calculation */
1229 END IF; /* for new batch*/
1230
1231 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1232 RAISE trans_qty_err;
1233 END IF;
1234 END IF;
1235
1236 /****
1237 START 17Feb05 Navin Sinha Made changes for GME_Scale_Batch_Theoretical_Yield_TD.
1238 *****/
1239 l_index := 0;
1240 l_return_status := NULL;
1241
1242 -- Loop through all the records of x_material_details
1243 FOR mtl_dtl_index IN 1 .. x_material_details.COUNT LOOP
1244 -- Based on the batch status of pending or WIP, the plan_qty or wip_plan_qty will be considered.
1245 IF p_batch_header_rec.batch_status = 1 THEN -- Pending
1246 l_material_details_qty :=
1247 NVL (x_material_details (mtl_dtl_index).plan_qty, 0);
1248 ELSIF p_batch_header_rec.batch_status = 2 THEN -- WIP
1249 l_material_details_qty :=
1250 NVL (x_material_details (mtl_dtl_index).wip_plan_qty, 0);
1251 END IF;
1252
1253 -- In case of scale down the batch, the user will be informed for the excess actual_qty
1254 -- compared to plan_qty or wip_plan_qty.
1255 -- The return staus to be assigned in such case is: T- if actual_qty is in excess.
1256 l_actual_qty := 0;
1257 l_actual_qty :=
1258 NVL (x_material_details (mtl_dtl_index).actual_qty, 0);
1259
1260 IF l_actual_qty > l_material_details_qty
1261 AND x_material_details (mtl_dtl_index).line_type = 1 THEN
1262 -- only for products
1263 l_return_status := 'T';
1264 END IF; /* l_actual_qty > l_material_details_qty */
1265 END LOOP; /* FOR mtl_dtl_index IN 1 .. x_material_details.COUNT */
1266
1267 IF NVL (l_return_status, '*') = 'T' THEN
1268 x_return_status := l_return_status;
1269 gme_common_pvt.log_message ('GME_ACTUAL_QTY_IS_IN_EXCESS');
1270 -- Navin: PENDING: NEW MESSAGE TO BE REGISTERED.: Actual quantity is in excess.
1271 END IF;
1272 /****
1273 END 17Feb05 Navin Sinha Made changes for GME_Scale_Batch_Theoretical_Yield_TD.
1274 *****/
1275 EXCEPTION
1276 WHEN batch_fetch_error OR material_fetch_err OR material_save_failed OR batch_step_fetch_err THEN
1277 x_return_status := fnd_api.g_ret_sts_error;
1278 WHEN step_closed_err OR scale_step_and_rsrc_error OR error_updating_steps THEN
1279 x_return_status := fnd_api.g_ret_sts_error;
1280 WHEN trans_qty_err THEN
1281 gme_common_pvt.log_message ('GME_THEOYLD_UNMATCH_ALLOC');
1282 WHEN reduce_reservation_fail THEN -- 4944024
1283 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1284 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Failure after relieve_prod_supply_resv ');
1285 END IF;
1286 WHEN OTHERS THEN
1287 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1288 x_return_status := fnd_api.g_ret_sts_unexp_error;
1289 END theoretical_yield_batch;
1290
1291 FUNCTION get_total_qty (
1292 p_material_tab IN gme_common_pvt.material_details_tab
1293 ,p_batch_header_rec IN gme_batch_header%ROWTYPE)
1294 RETURN NUMBER
1295 IS
1296 CURSOR get_rout_uom (v_batch_id NUMBER)
1297 IS
1298 SELECT routing_uom
1299 FROM gmd_routings_b
1300 WHERE routing_id = (SELECT routing_id
1301 FROM gme_batch_header
1302 WHERE batch_id = v_batch_id);
1303
1304 CURSOR cur_item_no (v_item_id NUMBER, v_org_id NUMBER)
1305 IS
1306 SELECT concatenated_segments
1307 FROM mtl_system_items_kfv
1308 WHERE inventory_item_id = v_item_id AND organization_id = v_org_id;
1309
1310 l_api_name CONSTANT VARCHAR2 (30) := 'GET_TOTAL_QTY';
1311 l_uom sy_uoms_mst.um_code%TYPE;
1312 l_qty NUMBER := 0;
1313 l_item_id NUMBER;
1314 l_item_no VARCHAR2 (32);
1315 l_from_uom sy_uoms_mst.um_code%TYPE;
1316 l_to_uom sy_uoms_mst.um_code%TYPE;
1317 l_rout_um sy_uoms_mst.um_code%TYPE;
1318 x_return_status VARCHAR2 (1);
1319 conversion_failure EXCEPTION;
1320 l_plan_wipplan_qty NUMBER := 0;
1321 l_temp_qty NUMBER;
1322 l_um_type sy_uoms_typ.um_type%TYPE;
1323 bad_yield_type EXCEPTION;
1324 l_batch_header gme_batch_header%ROWTYPE;
1325 BEGIN
1326 x_return_status := fnd_api.g_ret_sts_success;
1327 --l_um_type := fnd_profile.VALUE ('FM_YIELD_TYPE');
1328 l_batch_header := p_batch_header_rec;
1329
1330 OPEN get_rout_uom (l_batch_header.batch_id);
1331
1332 FETCH get_rout_uom
1333 INTO l_rout_um;
1334
1335 CLOSE get_rout_uom;
1336
1337 FOR l_row_count IN 1 .. p_material_tab.COUNT LOOP
1338 IF p_material_tab (l_row_count).line_type > 0 THEN
1339 IF p_batch_header_rec.batch_status = 1 THEN
1340 l_plan_wipplan_qty := p_material_tab (l_row_count).plan_qty;
1341 ELSE
1342 l_plan_wipplan_qty :=
1343 p_material_tab (l_row_count).wip_plan_qty;
1344 END IF;
1345
1346 IF p_material_tab (l_row_count).dtl_um = l_rout_um THEN
1347 l_qty := l_qty + l_plan_wipplan_qty;
1348 ELSE
1349 l_temp_qty :=
1350 inv_convert.inv_um_convert
1351 (p_material_tab (l_row_count).inventory_item_id
1352 ,5
1353 ,l_plan_wipplan_qty
1354 ,p_material_tab (l_row_count).dtl_um
1355 ,l_rout_um
1356 ,NULL
1357 ,NULL);
1358
1359 IF l_temp_qty < 0 THEN
1360 l_item_id := p_material_tab (l_row_count).inventory_item_id;
1361 l_from_uom := p_material_tab (l_row_count).dtl_um;
1362 l_to_uom := l_rout_um;
1363 RAISE conversion_failure;
1364 ELSE
1365 l_qty := l_qty + l_temp_qty;
1366 END IF;
1367 END IF;
1368 END IF;
1369 END LOOP;
1370
1371 RETURN l_qty;
1372 EXCEPTION
1373 WHEN conversion_failure THEN
1374 IF l_item_no IS NULL THEN
1375 OPEN cur_item_no (l_item_id, p_batch_header_rec.organization_id);
1376
1377 FETCH cur_item_no
1378 INTO l_item_no;
1379
1380 CLOSE cur_item_no;
1381 END IF;
1382
1383 fnd_message.set_name ('GMI', 'IC_API_UOM_CONVERSION_ERROR');
1384 fnd_message.set_token ('ITEM_NO', l_item_no);
1385 fnd_message.set_token ('FROM_UOM', l_from_uom);
1386 fnd_message.set_token ('TO_UOM', l_to_uom);
1387 fnd_msg_pub.ADD;
1388
1389 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1390 gme_debug.put_line
1391 ( 'SCALE BATCH:Get_total_qty:Conversion error for '
1392 || l_item_no
1393 || ' From '
1394 || l_from_uom
1395 || ' To '
1396 || l_to_uom);
1397 END IF;
1398 WHEN OTHERS THEN
1399 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1400
1401 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1402 gme_debug.put_line (SQLERRM);
1403 END IF;
1404
1405 x_return_status := 'U';
1406 END get_total_qty;
1407
1408 PROCEDURE scale_step_and_rsrc (
1409 p_batch_id IN gme_batch_header.batch_id%TYPE
1410 ,p_routing_scale_factor IN NUMBER
1411 ,x_return_status OUT NOCOPY VARCHAR2)
1412 IS
1413 /* Local variables */
1414 l_api_name CONSTANT VARCHAR2 (30) := 'SCALE_STEP_AND_RSRC';
1415 l_auto_step_calc NUMBER (5);
1416 l_batch_step gme_batch_steps%ROWTYPE;
1417 l_in_batch_step gme_batch_steps%ROWTYPE;
1418 l_batchstep_resource_ids gme_common_pvt.number_tab;
1419 l_gme_batchstep_resources gme_batch_step_resources%ROWTYPE;
1420 l_gme_resource_txns_gtmp gme_resource_txns_gtmp%ROWTYPE;
1421 l_return_status VARCHAR2 (1);
1422 l_message_count NUMBER;
1423 l_message_list VARCHAR2 (2000);
1424 l_charge NUMBER;
1425 l_prev_charge NUMBER;
1426 l_update_inventory_ind VARCHAR2 (1);
1427 batch_step_fetch_err EXCEPTION;
1428 error_updating_steps EXCEPTION;
1429 fetch_res_error EXCEPTION;
1430 update_res_error EXCEPTION;
1431 fetch_res_txn_error EXCEPTION;
1432 update_res_txn_error EXCEPTION;
1433 error_updating_step_qty EXCEPTION;
1434 activity_rsrc_fetch_error EXCEPTION;
1435
1436 CURSOR cur_get_batch_info
1437 IS
1438 SELECT automatic_step_calculation, update_inventory_ind
1439 FROM gme_batch_header
1440 WHERE batch_id = p_batch_id;
1441
1442 CURSOR cur_get_all_steps (v_batch_id NUMBER)
1443 IS
1444 SELECT batchstep_id
1445 FROM gme_batch_steps
1446 WHERE batch_id = v_batch_id;
1447
1448 CURSOR cur_get_res (v_step_id gme_batch_steps.batchstep_id%TYPE)
1449 IS
1450 SELECT batchstep_resource_id
1451 FROM gme_batch_step_resources
1452 WHERE batchstep_id = v_step_id AND scale_type <> 0;
1453
1454 CURSOR cur_get_res_txns (
1455 v_batch_id gme_batch_header.batch_id%TYPE
1456 ,v_res_id gme_batch_step_resources.batchstep_resource_id%TYPE)
1457 IS
1458 SELECT poc_trans_id
1459 FROM gme_resource_txns_gtmp
1460 WHERE doc_id = v_batch_id AND line_id = v_res_id;
1461
1462 l_cur_get_all_steps cur_get_all_steps%ROWTYPE;
1463 l_cur_get_res cur_get_res%ROWTYPE;
1464 l_cur_get_res_txns cur_get_res_txns%ROWTYPE;
1465 BEGIN
1466 x_return_status := fnd_api.g_ret_sts_success;
1467
1468 OPEN cur_get_batch_info;
1469
1470 FETCH cur_get_batch_info
1471 INTO l_auto_step_calc, l_update_inventory_ind;
1472
1473 CLOSE cur_get_batch_info;
1474
1475 FOR l_cur_get_all_steps IN cur_get_all_steps (p_batch_id) LOOP
1476 l_batch_step.batchstep_id := l_cur_get_all_steps.batchstep_id;
1477
1478 IF NOT (gme_batch_steps_dbl.fetch_row (l_batch_step, l_batch_step) ) THEN
1479 RAISE batch_step_fetch_err;
1480 END IF;
1481
1482 -- Round the step qtys to 5 decimal places.
1483 /* We should scale the step quantities if the step is pending or if the */
1484 /* step is certified and the batch automatic step calculation is off */
1485 IF (l_batch_step.step_status = 1)
1486 OR (l_batch_step.step_status = 3 AND l_auto_step_calc = 0) THEN
1487 IF l_batch_step.step_status = 1 THEN
1488 l_batch_step.plan_step_qty :=
1489 ROUND (l_batch_step.plan_step_qty * p_routing_scale_factor
1490 ,5);
1491 l_batch_step.plan_mass_qty :=
1492 ROUND (l_batch_step.plan_mass_qty * p_routing_scale_factor
1493 ,5);
1494 l_batch_step.plan_volume_qty :=
1495 ROUND (l_batch_step.plan_volume_qty * p_routing_scale_factor
1496 ,5);
1497 ELSE
1498 l_batch_step.actual_step_qty :=
1499 ROUND (l_batch_step.actual_step_qty * p_routing_scale_factor
1500 ,5);
1501 l_batch_step.actual_mass_qty :=
1502 ROUND (l_batch_step.actual_mass_qty * p_routing_scale_factor
1503 ,5);
1504 l_batch_step.actual_volume_qty :=
1505 ROUND ( l_batch_step.actual_volume_qty
1506 * p_routing_scale_factor
1507 ,5);
1508 l_prev_charge := l_batch_step.actual_charges;
1509 /* Let us calculate actual charges based on the change to the qty */
1510 gme_update_step_qty_pvt.calc_charge
1511 (p_step_id => l_batch_step.batchstep_id
1512 ,p_mass_qty => l_batch_step.actual_mass_qty
1513 ,p_vol_qty => l_batch_step.actual_volume_qty
1514 ,x_charge => l_charge
1515 ,x_return_status => l_return_status);
1516
1517 IF l_return_status = x_return_status THEN
1518 l_batch_step.actual_charges := l_charge;
1519 END IF;
1520 END IF;
1521
1522 IF NOT (gme_batch_steps_dbl.update_row (l_batch_step) ) THEN
1523 RAISE error_updating_steps;
1524 END IF;
1525
1526 IF l_batch_step.step_status = 1 THEN
1527 l_in_batch_step := l_batch_step;
1528 gme_update_step_qty_pvt.update_step_qty
1529 (p_batch_step_rec => l_in_batch_step
1530 ,x_batch_step_rec => l_batch_step
1531 ,x_message_count => l_message_count
1532 ,x_message_list => l_message_list
1533 ,x_return_status => l_return_status
1534 ,p_routing_scale_factor => p_routing_scale_factor);
1535
1536 IF l_return_status <> x_return_status THEN
1537 RAISE error_updating_step_qty;
1538 END IF;
1539 ELSE
1540 /* We cannot invoke the update step qty API for certified non asqc steps */
1541 /* as it would not update the resources if the actuals already populated */
1542 /* Fetch all the resources associated with the step */
1543 OPEN cur_get_res (l_batch_step.batchstep_id);
1544
1545 FETCH cur_get_res
1546 BULK COLLECT INTO l_batchstep_resource_ids;
1547
1548 CLOSE cur_get_res;
1549
1550 /* Fetch all the activity resources details */
1551 FOR i IN 1 .. l_batchstep_resource_ids.COUNT LOOP
1552 l_gme_batchstep_resources.batchstep_resource_id :=
1553 l_batchstep_resource_ids (i);
1554
1555 IF NOT (gme_batch_step_resources_dbl.fetch_row
1556 (l_gme_batchstep_resources
1557 ,l_gme_batchstep_resources) ) THEN
1558 RAISE activity_rsrc_fetch_error;
1559 END IF;
1560
1561 -- Round the resource usage to 5 decimal places.
1562 l_gme_batchstep_resources.actual_rsrc_qty :=
1563 ROUND ( l_gme_batchstep_resources.actual_rsrc_qty
1564 * p_routing_scale_factor
1565 ,5);
1566
1567 IF l_gme_batchstep_resources.scale_type = 1 THEN
1568 l_gme_batchstep_resources.actual_rsrc_usage :=
1569 ROUND ( l_gme_batchstep_resources.actual_rsrc_usage
1570 * p_routing_scale_factor
1571 ,5);
1572 ELSIF (l_gme_batchstep_resources.scale_type = 2) THEN
1573 IF NVL (l_gme_batchstep_resources.actual_rsrc_usage
1574 ,0) > 0
1575 AND NVL (l_prev_charge, 0) > 0 THEN
1576 l_gme_batchstep_resources.actual_rsrc_usage :=
1577 ROUND
1578 ( ( ( l_gme_batchstep_resources.actual_rsrc_usage
1579 / l_prev_charge)
1580 * l_batch_step.actual_charges)
1581 ,5);
1582 END IF;
1583 END IF;
1584
1585 IF l_update_inventory_ind = 'Y' THEN
1586 gme_update_step_qty_pvt.adjust_actual_usage
1587 (p_batch_step_resources_rec => l_gme_batchstep_resources
1588 ,x_return_status => l_return_status);
1589
1590 IF l_return_status <> x_return_status THEN
1591 RAISE update_res_txn_error;
1592 END IF;
1593 END IF; /* IF l_update_inventory_ind = 'Y' */
1594
1595 IF NOT (gme_batch_step_resources_dbl.update_row
1596 (l_gme_batchstep_resources) ) THEN
1597 RAISE update_res_error;
1598 END IF;
1599 END LOOP; /* FOR i IN 1..l_batchstep_resource_ids.COUNT */
1600 END IF;
1601 END IF; /* IF l_batch_step.step_status = 1 THEN */
1602 END LOOP;
1603 EXCEPTION
1604 WHEN error_updating_steps OR activity_rsrc_fetch_error OR update_res_error OR batch_step_fetch_err THEN
1605 x_return_status := fnd_api.g_ret_sts_error;
1606 WHEN error_updating_step_qty OR update_res_txn_error THEN
1607 x_return_status := l_return_status;
1608 WHEN OTHERS THEN
1609 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1610
1611 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1612 gme_debug.put_line (SQLERRM);
1613 END IF;
1614
1615 x_return_status := fnd_api.g_ret_sts_unexp_error;
1616 END scale_step_and_rsrc;
1617 END gme_scale_batch_pvt;