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