1 PACKAGE BODY gme_update_step_qty_pvt AS
2 /* $Header: GMEVUSQB.pls 120.6.12000000.3 2007/01/26 23:08:42 snene ship $ */
3 g_debug VARCHAR2 (5)
4 := fnd_profile.VALUE ('AFLOG_LEVEL');
5 g_pkg_name CONSTANT VARCHAR2 (30) := 'gme_update_step_qty_pvt';
6 p_gme_calc_int_rsrc_usage NUMBER := NULL;
7
8 /*===========================================================================================
9 Procedure
10 update_step_qty
11 Description
12 This particular procedure call changes the current step qty and propogates it.
13 Parameters
14 p_batch_step_rec The batch step row to identify the step.
15 x_message_count The number of messages in the message stack
16 x_message_list message stack where the api writes its messages
17 x_return_status outcome of the API call
18 S - Success
19 E - Error
20 U - Unexpected error
21 =============================================================================================*/
22 PROCEDURE update_step_qty (
23 p_batch_step_rec IN gme_batch_steps%ROWTYPE
24 ,x_message_count OUT NOCOPY NUMBER
25 ,x_message_list OUT NOCOPY VARCHAR2
26 ,x_return_status OUT NOCOPY VARCHAR2
27 ,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
28 ,p_routing_scale_factor IN NUMBER DEFAULT NULL
29 ,p_backflush_factor IN NUMBER DEFAULT NULL
30 ,p_dependency_type IN NUMBER DEFAULT NULL
31 ,p_material_step_id IN NUMBER DEFAULT NULL)
32 IS
33 l_api_name CONSTANT VARCHAR2 (30) := 'update_step_qty';
34 /* Buffers for database reads/writes */
35 l_batch_header gme_batch_header%ROWTYPE;
36 l_batch_step gme_batch_steps%ROWTYPE;
37 l_step_tbl gmd_auto_step_calc.step_rec_tbl;
38 /* Exception definitions */
39 batch_step_fetch_error EXCEPTION;
40 invalid_step_status EXCEPTION;
41 batch_header_fetch_error EXCEPTION;
42 invalid_batch_status EXCEPTION;
43 auto_step_calc_error EXCEPTION;
44 calc_quantities_error EXCEPTION;
45 /* Local variables */
46 l_return_status VARCHAR2 (1);
47 l_rec NUMBER;
48
49 CURSOR dependent_steps (
50 v_batch_id gme_batch_header.batch_id%TYPE
51 ,v_batchstep_id gme_batch_steps.batchstep_id%TYPE)
52 IS
53 SELECT d.batchstep_id
54 FROM gme_batch_step_dependencies d
55 WHERE d.batch_id = v_batch_id
56 START WITH ( (d.batch_id = v_batch_id)
57 AND ( (v_batchstep_id IS NULL)
58 OR (dep_step_id = v_batchstep_id) ) )
59 CONNECT BY d.batch_id = PRIOR d.batch_id
60 AND d.dep_step_id = PRIOR d.batchstep_id
61 GROUP BY d.batchstep_id
62 --Bug# 5606246 Start
63 --ORDER BY MAX (LEVEL) ASC;
64 UNION
65 SELECT p_material_step_id from dual;
66 --Bug#5606246 end
67 BEGIN
68 IF g_debug <= gme_debug.g_log_procedure THEN
69 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
70 || l_api_name||' with batchstep_id '||p_batch_step_rec.batchstep_id);
71 END IF;
72
73 /* Set the savepoint before proceeding */
74 SAVEPOINT update_step_qty;
75 /* Set the return status to success initially */
76 x_return_status := fnd_api.g_ret_sts_success;
77 IF g_debug <= gme_debug.g_log_statement THEN
78 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
79 || l_api_name||'p_routing_scale_factor ' || p_routing_scale_factor);
80 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
81 || l_api_name||'p_backflush_factor ' || p_backflush_factor);
82 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
83 || l_api_name||'p_dependency_type ' || p_dependency_type);
84 END IF;
85
86 /* Initialize output batch step row */
87 IF NOT (gme_batch_steps_dbl.fetch_row (p_batch_step_rec
88 ,x_batch_step_rec) ) THEN
89 RAISE batch_step_fetch_error;
90 END IF;
91
92 /* The current Step Status must allow editing of step qty */
93 IF (x_batch_step_rec.step_status = gme_common_pvt.g_step_cancelled) THEN
94 RAISE invalid_step_status;
95 END IF;
96
97 l_batch_header.batch_id := x_batch_step_rec.batch_id;
98
99 /* Initialize local batch header */
100 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header, l_batch_header) ) THEN
101 RAISE batch_header_fetch_error;
102 END IF;
103 /* Load resource transactions in temp table */
104 gme_trans_engine_util.load_rsrc_trans
105 (p_batch_row => l_batch_header
106 ,x_rsc_row_count => l_rec
107 ,x_return_status => l_return_status);
108
109 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
110 RAISE fnd_api.g_exc_error;
111 END IF;
112 IF g_debug <= gme_debug.g_log_statement THEN
113 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
114 || l_api_name||': Resource transactions loaded '||l_rec);
115 END IF;
116 l_rec := 0;
117
118 /* The Batch must not be in Cancelled state to edit the step qty. */
119 IF (l_batch_header.batch_status = gme_common_pvt.g_batch_cancelled) THEN
120 RAISE invalid_batch_status;
121 END IF;
122
123 /* If automatic step qty calculation is set for the batch then */
124 /* invoke the ASQC routine to calculate the actual step qty */
125 IF l_batch_header.automatic_step_calculation = 1 THEN
126 /* If the step status is certified or WIP then we have to invoke the automatic */
127 /* step qty calculation for the whole batch as the transafer quantities would */
128 /* would affect the succeeding steps. Though the transfer quantities are not */
129 /* passed in WIP we have to calculate for the whole batch as this procedure */
130 /* might be called from uncertify batch wherein we have to decrease the */
131 /* succeeding step quantities. */
132 IF x_batch_step_rec.step_status IN
133 (gme_common_pvt.g_step_pending
134 ,gme_common_pvt.g_step_wip
135 ,gme_common_pvt.g_step_completed) THEN
136 gmd_auto_step_calc.calc_step_qty
137 (p_parent_id => l_batch_header.batch_id
138 ,p_step_tbl => l_step_tbl
139 ,p_msg_count => x_message_count
140 ,p_msg_stack => x_message_list
141 ,p_return_status => l_return_status
142 ,p_called_from_batch => 1
143 ,p_step_no => NULL
144 ,p_organization_id => l_batch_header.organization_id);
145 /* If the step status is not certified then we have to invoke the automatic */
146 /* step qty calculation only for the current step as it does not effect the */
147 /* succeeding step quantities. */
148 ELSE
149 gmd_auto_step_calc.calc_step_qty
150 (p_parent_id => l_batch_header.batch_id
151 ,p_step_tbl => l_step_tbl
152 ,p_msg_count => x_message_count
153 ,p_msg_stack => x_message_list
154 ,p_return_status => l_return_status
155 ,p_called_from_batch => 1
156 ,p_step_no => x_batch_step_rec.batchstep_no
157 ,p_organization_id => l_batch_header.organization_id);
158 END IF; /* IF x_batch_step_rec.step_status IN (2, 3) */
159
160 IF l_return_status <> x_return_status THEN
161 RAISE auto_step_calc_error;
162 END IF;
163
164 /* Get the record number for the current step in the step table */
165 l_rec :=
166 gmd_auto_step_calc.get_step_rec (x_batch_step_rec.batchstep_no
167 ,l_step_tbl);
168
169 /* If step status is not in pending then actual quantities have to */
170 /* be updated */
171 IF x_batch_step_rec.step_status > gme_common_pvt.g_step_pending THEN
172 x_batch_step_rec.actual_step_qty := l_step_tbl (l_rec).step_qty;
173 x_batch_step_rec.actual_mass_qty :=
174 l_step_tbl (l_rec).step_mass_qty;
175 x_batch_step_rec.actual_volume_qty :=
176 l_step_tbl (l_rec).step_vol_qty;
177 ELSE
178 x_batch_step_rec.plan_step_qty := l_step_tbl (l_rec).step_qty;
179 x_batch_step_rec.plan_mass_qty :=
180 l_step_tbl (l_rec).step_mass_qty;
181 x_batch_step_rec.plan_volume_qty :=
182 l_step_tbl (l_rec).step_vol_qty;
183 END IF;
184 /* If automatic step quantity calculation is not set for the current batch */
185 ELSE
186 IF x_batch_step_rec.step_status > gme_common_pvt.g_step_wip THEN
187 IF x_batch_step_rec.actual_step_qty IS NULL THEN
188 x_batch_step_rec.actual_step_qty :=
189 x_batch_step_rec.plan_step_qty;
190 x_batch_step_rec.actual_mass_qty :=
191 x_batch_step_rec.plan_mass_qty;
192 x_batch_step_rec.actual_volume_qty :=
193 x_batch_step_rec.plan_volume_qty;
194 /* If the actual step qty has been enetered by the user then we need */
195 /* to recalculate the step mass or volume quantities */
196 ELSE
197 calculate_mass_vol_qty (x_batch_step_rec);
198 END IF;
199 /* Bug 3545726 backflush actual qty based on factor */
200 ELSIF x_batch_step_rec.step_status = gme_common_pvt.g_step_wip
201 AND gme_common_pvt.g_backflush_rsrc_usg_ind = 1
202 AND p_backflush_factor IS NOT NULL THEN
203 x_batch_step_rec.actual_step_qty :=
204 NVL (x_batch_step_rec.actual_step_qty, 0)
205 + (x_batch_step_rec.plan_step_qty * p_backflush_factor);
206 x_batch_step_rec.actual_mass_qty :=
207 NVL (x_batch_step_rec.actual_mass_qty, 0)
208 + (x_batch_step_rec.plan_mass_qty * p_backflush_factor);
209 x_batch_step_rec.actual_volume_qty :=
210 NVL (x_batch_step_rec.actual_volume_qty, 0)
211 + (x_batch_step_rec.plan_volume_qty * p_backflush_factor);
212 /* If the plan step qty has been changed then we need */
213 /* to recalculate the step mass or volume quantities */
214 ELSE
215 calculate_mass_vol_qty (x_batch_step_rec);
216 END IF; /* IF x_batch_step_rec.step_status > 1 */
217 END IF; /* IF l_batch_header.automatic_step_calculation = 1 */
218
219 /* If the step status is pending the null the actual quantities */
220 IF x_batch_step_rec.step_status = gme_common_pvt.g_step_pending THEN
221 x_batch_step_rec.actual_step_qty := NULL;
222 x_batch_step_rec.actual_mass_qty := NULL;
223 x_batch_step_rec.actual_volume_qty := NULL;
224 END IF;
225
226 calculate_quantities (p_batch_hdr_rec => l_batch_header
227 ,p_batch_step_rec => x_batch_step_rec
228 ,x_return_status => l_return_status
229 ,p_routing_scale_factor => p_routing_scale_factor
230 ,p_backflush_factor => p_backflush_factor
231 ,p_dependency_type => p_dependency_type);
232
233 IF l_return_status <> x_return_status THEN
234 RAISE calc_quantities_error;
235 END IF; /* IF l_return_status <> x_return_status */
236
237 IF l_batch_header.automatic_step_calculation = 1 THEN
238 /* If the step status is certified or WIP then the transafer quantities */
239 /* would affect the succeeding steps. */
240 IF x_batch_step_rec.step_status IN
241 (gme_common_pvt.g_step_pending
242 ,gme_common_pvt.g_step_wip
243 ,gme_common_pvt.g_step_completed) THEN
244 /* We have to invoke the calculate quantities routine for all the */
245 /* succeeding steps. */
246 FOR dep_steps IN dependent_steps (l_batch_header.batch_id
247 ,l_batch_step.batchstep_id) LOOP
248 l_batch_step.batchstep_id := dep_steps.batchstep_id;
249 --Bug#5606089 Filtering out the null row.
250 IF l_batch_step.batchstep_id IS NOT NULL THEN
251 /* Initialize output batch step row */
252 IF NOT (gme_batch_steps_dbl.fetch_row (l_batch_step
253 ,l_batch_step) ) THEN
254 RAISE batch_step_fetch_error;
255 END IF;
256
257 /* We need to apply the transfer quantities if only the step status is in */
258 /* confirm with the dependent step status */
259 IF ( (x_batch_step_rec.step_status >
260 gme_common_pvt.g_step_pending)
261 AND (l_batch_step.step_status >
262 gme_common_pvt.g_step_pending) )
263 OR ( (x_batch_step_rec.step_status =
264 gme_common_pvt.g_step_pending)
265 AND (l_batch_step.step_status =
266 gme_common_pvt.g_step_pending) ) THEN
267 /* Get the record number for the current step in the step table */
268 l_rec :=
269 gmd_auto_step_calc.get_step_rec
270 (l_batch_step.batchstep_no
271 ,l_step_tbl);
272
273 /* If step status is not in pending then actual quantities have to */
274 /* be updated */
275 IF l_batch_step.step_status > gme_common_pvt.g_step_pending THEN
276 l_batch_step.actual_step_qty :=
277 l_step_tbl (l_rec).step_qty;
278 l_batch_step.actual_mass_qty :=
279 l_step_tbl (l_rec).step_mass_qty;
280 l_batch_step.actual_volume_qty :=
281 l_step_tbl (l_rec).step_vol_qty;
282 ELSE
283 l_batch_step.plan_step_qty :=
284 l_step_tbl (l_rec).step_qty;
285 l_batch_step.plan_mass_qty :=
286 l_step_tbl (l_rec).step_mass_qty;
287 l_batch_step.plan_volume_qty :=
288 l_step_tbl (l_rec).step_vol_qty;
289 END IF; /* IF l_batch_step.step_status > 1 */
290
291 calculate_quantities (p_batch_hdr_rec => l_batch_header
292 ,p_batch_step_rec => l_batch_step
293 ,x_return_status => l_return_status);
294
295 IF l_return_status <> x_return_status THEN
296 RAISE calc_quantities_error;
297 END IF; /* IF l_return_status <> x_return_status */
298 END IF;
299 END IF;--bUG#5606089
300 /* IF ((x_batch_step_rec.step_status > 1) AND (l_batch_step.step_status > 1)) */
301 END LOOP; /* FOR dep_steps*/
302 END IF; /* IF x_batch_step_rec.step_status IN (2, 3) */
303 END IF; /*IF l_batch_header.automatic_step_calculation = 1*/
304
305 IF g_debug <= gme_debug.g_log_procedure THEN
306 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
307 END IF;
308 EXCEPTION
309 WHEN batch_step_fetch_error THEN
310 ROLLBACK TO SAVEPOINT update_step_qty;
311 x_return_status := fnd_api.g_ret_sts_error;
312 fnd_msg_pub.count_and_get (p_count => x_message_count
313 ,p_data => x_message_list);
314 WHEN invalid_step_status THEN
315 ROLLBACK TO SAVEPOINT update_step_qty;
316 x_return_status := fnd_api.g_ret_sts_error;
317 gme_common_pvt.log_message ('GME_API_INV_STAT_STEP_EDIT');
318 fnd_msg_pub.count_and_get (p_count => x_message_count
319 ,p_data => x_message_list);
320 WHEN batch_header_fetch_error THEN
321 ROLLBACK TO SAVEPOINT update_step_qty;
322 x_return_status := fnd_api.g_ret_sts_error;
323 fnd_msg_pub.count_and_get (p_count => x_message_count
324 ,p_data => x_message_list);
325 WHEN invalid_batch_status THEN
326 ROLLBACK TO SAVEPOINT update_step_qty;
327 x_return_status := fnd_api.g_ret_sts_error;
328 gme_common_pvt.log_message ('GME_API_INV_BATCH_EDIT_STEP');
329 fnd_msg_pub.count_and_get (p_count => x_message_count
330 ,p_data => x_message_list);
331 WHEN auto_step_calc_error THEN
332 ROLLBACK TO SAVEPOINT update_step_qty;
333 x_return_status := l_return_status;
334 fnd_msg_pub.count_and_get (p_count => x_message_count
335 ,p_data => x_message_list);
336 WHEN calc_quantities_error THEN
337 ROLLBACK TO SAVEPOINT update_step_qty;
338 x_return_status := l_return_status;
339 fnd_msg_pub.count_and_get (p_count => x_message_count
340 ,p_data => x_message_list);
341 WHEN OTHERS THEN
342 IF g_debug <= gme_debug.g_log_unexpected THEN
343 gme_debug.put_line ( 'When others exception in '
344 || g_pkg_name
345 || '.'
346 || l_api_name
347 || ' Error is '
348 || SQLERRM);
349 END IF;
350
351 ROLLBACK TO SAVEPOINT edit_step_qty;
352 x_return_status := fnd_api.g_ret_sts_unexp_error;
353 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
354 fnd_msg_pub.count_and_get (p_count => x_message_count
355 ,p_data => x_message_list);
356 END update_step_qty;
357
358 /*===========================================================================================
359 Procedure
360 calculate_mass_vol_qty
361 Description
362 This particular procedure converts either the plan step qty or the actual step qty based
363 on the step status to its mass or volume quantities based on the step max capacity uom type.
364 Parameters
365 p_batch_step_rec The batch step row to identify the step.
366 =============================================================================================*/
367 PROCEDURE calculate_mass_vol_qty (
368 p_batch_step_rec IN OUT NOCOPY gme_batch_steps%ROWTYPE)
369 IS
370 /* Cursor definition */
371 CURSOR cur_get_uom
372 IS
373 SELECT uom_class, conversion_rate
374 FROM mtl_uom_conversions
375 WHERE uom_code = p_batch_step_rec.step_qty_um
376 AND inventory_item_id = 0;
377
378 /* Local Variables */
379 l_um_type mtl_uom_conversions.uom_class%TYPE;
380 l_std_factor NUMBER;
381 l_api_name CONSTANT VARCHAR2 (30) := 'calculate_mass_vol_qty';
382 BEGIN
383 IF g_debug <= gme_debug.g_log_procedure THEN
384 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
385 || l_api_name);
386 END IF;
387
388 OPEN cur_get_uom;
389
390 FETCH cur_get_uom
391 INTO l_um_type, l_std_factor;
392
393 CLOSE cur_get_uom;
394
395 IF l_um_type = gme_common_pvt.g_mass_um_type THEN
396 /* If step status is not in pending then actual quantities have to */
397 /* be updated */
398 IF p_batch_step_rec.step_status > gme_common_pvt.g_step_pending THEN
399 p_batch_step_rec.actual_mass_qty :=
400 NVL (p_batch_step_rec.actual_step_qty * l_std_factor, 0);
401 ELSE
402 p_batch_step_rec.plan_mass_qty :=
403 NVL (p_batch_step_rec.plan_step_qty * l_std_factor, 0);
404 END IF; /* IF p_batch_step_rec.step_status > 1 */
405 ELSIF l_um_type = gme_common_pvt.g_volume_um_type THEN
406 /* If step status is not in pending then actual quantities have to */
407 /* be updated */
408 IF p_batch_step_rec.step_status > gme_common_pvt.g_step_pending THEN
409 p_batch_step_rec.actual_volume_qty :=
410 NVL (p_batch_step_rec.actual_step_qty * l_std_factor, 0);
411 ELSE
412 p_batch_step_rec.plan_volume_qty :=
413 NVL (p_batch_step_rec.plan_step_qty * l_std_factor, 0);
414 END IF; /* IF p_batch_step_rec.step_status > 1 */
415 END IF; /* IF l_um_type = gme_common_pvt.g_mass_um_type */
416
417 IF g_debug <= gme_debug.g_log_procedure THEN
418 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
419 END IF;
420 EXCEPTION
421 WHEN OTHERS THEN
422 IF g_debug <= gme_debug.g_log_unexpected THEN
423 gme_debug.put_line ( 'When others exception in '
424 || g_pkg_name
425 || '.'
426 || l_api_name
427 || ' Error is '
428 || SQLERRM);
429 END IF;
430
431 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
432 END calculate_mass_vol_qty;
433
434 /*===========================================================================================
435 Procedure
436 calculate_quantities
437 Description
438 This particular procedure is used to calculate the resource quantities.
439 Parameters
440 p_batch_step_rec The batch step row to identify the step.
441 x_return_status outcome of the API call
442 S - Success
443 E - Error
444 U - Unexpected error
445 =============================================================================================*/
446 PROCEDURE calculate_quantities (
447 p_batch_hdr_rec IN gme_batch_header%ROWTYPE
448 ,p_batch_step_rec IN OUT NOCOPY gme_batch_steps%ROWTYPE
449 ,x_return_status OUT NOCOPY VARCHAR2
450 ,p_routing_scale_factor IN NUMBER DEFAULT NULL
451 ,p_backflush_factor IN NUMBER DEFAULT NULL
452 ,p_dependency_type IN NUMBER DEFAULT NULL)
453 IS
454 l_api_name CONSTANT VARCHAR2 (30) := 'calculate__quantities';
455 /* Exception definitions */
456 compute_resource_error EXCEPTION;
457 batch_step_upd_err EXCEPTION;
458 batch_step_fetch_err EXCEPTION;
459 error_calc_charge EXCEPTION;
460 /* Local variables */
461 l_return_status VARCHAR2 (1);
462 l_charges NUMBER;
463 l_orig_charges NUMBER;
464 l_charge_diff NUMBER;
465 BEGIN
466 IF g_debug <= gme_debug.g_log_procedure THEN
467 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
468 || l_api_name);
469 END IF;
470
471 /* Set the return status to success initially */
472 x_return_status := fnd_api.g_ret_sts_success;
473 /* Bug 3545726 store original charges */
474 l_orig_charges := NVL (p_batch_step_rec.actual_charges, 0);
475
476 IF p_batch_step_rec.step_status > gme_common_pvt.g_step_pending THEN
477 calc_charge (p_step_id => p_batch_step_rec.batchstep_id
478 ,p_mass_qty => p_batch_step_rec.actual_mass_qty
479 ,p_vol_qty => p_batch_step_rec.actual_volume_qty
480 ,x_charge => l_charges
481 ,x_return_status => l_return_status);
482
483 IF l_return_status <> x_return_status THEN
484 RAISE error_calc_charge;
485 ELSE
486 p_batch_step_rec.actual_charges := l_charges;
487 END IF;
488 ELSIF p_batch_step_rec.step_status = gme_common_pvt.g_step_pending THEN
489 calc_charge (p_step_id => p_batch_step_rec.batchstep_id
490 ,p_mass_qty => p_batch_step_rec.plan_mass_qty
491 ,p_vol_qty => p_batch_step_rec.plan_volume_qty
492 ,x_charge => l_charges
493 ,x_return_status => l_return_status);
494
495 IF l_return_status <> x_return_status THEN
496 RAISE error_calc_charge;
497 ELSE
498 p_batch_step_rec.plan_charges := l_charges;
499 p_batch_step_rec.actual_charges := NULL;
500 END IF;
501 END IF;
502
503 /* Now we have to compute the resource usages and quantities */
504 l_charge_diff :=
505 NVL (p_batch_step_rec.actual_charges, 0)
506 - l_orig_charges;
507 update_activities (p_batch_hdr_rec => p_batch_hdr_rec
508 ,p_batch_step_rec => p_batch_step_rec
509 ,x_return_status => l_return_status
510 ,p_routing_scale_factor => p_routing_scale_factor
511 ,p_backflush_factor => p_backflush_factor
512 ,p_charge_diff => l_charge_diff
513 ,p_dependency_type => p_dependency_type);
514
515 IF l_return_status <> x_return_status THEN
516 RAISE compute_resource_error;
517 END IF;
518
519 /* Now update the batch step to the database */
520 IF NOT (gme_batch_steps_dbl.update_row (p_batch_step_rec) ) THEN
521 RAISE batch_step_upd_err;
522 END IF;
523
524 IF NOT (gme_batch_steps_dbl.fetch_row (p_batch_step_rec
525 ,p_batch_step_rec) ) THEN
526 RAISE batch_step_fetch_err;
527 END IF;
528
529 IF g_debug <= gme_debug.g_log_procedure THEN
530 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
531 END IF;
532 EXCEPTION
533 WHEN compute_resource_error THEN
534 x_return_status := l_return_status;
535 WHEN batch_step_upd_err OR error_calc_charge OR batch_step_fetch_err THEN
536 x_return_status := fnd_api.g_ret_sts_error;
537 WHEN OTHERS THEN
538 IF g_debug <= gme_debug.g_log_unexpected THEN
539 gme_debug.put_line ( 'When others exception in '
540 || g_pkg_name
541 || '.'
542 || l_api_name
543 || ' Error is '
544 || SQLERRM);
545 END IF;
546
547 x_return_status := fnd_api.g_ret_sts_unexp_error;
548 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
549 END calculate_quantities;
550
551 /*===========================================================================================
552 Procedure
553 calc_charge
554 Description
555 This particular procedure is used to calculate the charges for the step.
556 Parameters
557 p_step_id The batch step id to identify the step.
558 p_mass_qty Step qty in mass uom type
559 p_vol_qty Step qty in volume uom type
560 x_charge Calculated charge returned to the calling program
561 x_return_status outcome of the API call
562 S - Success
563 E - Error
564 U - Unexpected error
565 Hisory
566 =============================================================================================*/
567 PROCEDURE calc_charge (
568 p_step_id IN gme_batch_steps.batchstep_id%TYPE
569 ,p_resources IN gme_batch_step_resources.resources%TYPE
570 DEFAULT NULL
571 ,p_mass_qty IN gme_batch_steps.plan_mass_qty%TYPE
572 ,p_vol_qty IN gme_batch_steps.plan_volume_qty%TYPE
573 ,p_step_qty IN NUMBER DEFAULT NULL --Bug#5231180
574 ,p_max_capacity IN NUMBER DEFAULT NULL --Bug#5231180
575 ,x_charge OUT NOCOPY gme_batch_steps.plan_charges%TYPE
576 ,x_return_status OUT NOCOPY VARCHAR2)
577 IS
578 x_step_no gme_batch_steps.batchstep_no%TYPE;
579 x_std_factor NUMBER;
580 x_um_type mtl_units_of_measure.uom_class%TYPE;
581 x_std_capacity gme_batch_steps.max_step_capacity%TYPE;
582 x_max_cap gme_batch_steps.max_step_capacity%TYPE;
583 x_max_cap_uom gme_batch_steps.max_step_capacity_um%TYPE;
584 x_step_qty gme_batch_steps.plan_step_qty%TYPE;
585 x_step_qty_uom gme_batch_steps.step_qty_um%TYPE;
586 x_temp_qty NUMBER;
587 x_tolerance NUMBER;
588 x_stdum_tolerance NUMBER;
589 pmass_qty_std NUMBER;
590 l_api_name CONSTANT VARCHAR2 (30) := 'calc_charge';
591
592 -- Cursor Definitions
593 CURSOR cur_get_max_capacity
594 IS
595 SELECT max_step_capacity, max_step_capacity_um, batchstep_no
596 ,plan_step_qty, step_qty_um
597 FROM gme_batch_steps
598 WHERE batchstep_id = p_step_id;
599
600 CURSOR cur_get_std_factor (v_um_code VARCHAR2)
601 IS
602 SELECT uom_class, conversion_rate
603 FROM mtl_uom_conversions
604 WHERE uom_code = v_um_code AND inventory_item_id = 0;
605
606 CURSOR cur_get_tolerance
607 IS
608 SELECT NVL (MIN (gbsr.capacity_tolerance), 0)
609 FROM gme_batch_steps gbs, gme_batch_step_resources gbsr
610 WHERE gbs.batchstep_id = gbsr.batchstep_id
611 AND gbsr.calculate_charges = 1
612 AND gbsr.batchstep_id = p_step_id;
613
614 CURSOR cur_get_resource_dates (
615 v_batchstep_id NUMBER
616 ,v_resources gme_batch_step_resources.resources%TYPE)
617 IS
618 SELECT plan_start_date, plan_cmplt_date
619 FROM gme_batch_step_resources
620 WHERE batchstep_id = v_batchstep_id AND resources = v_resources;
621
622 CURSOR cur_get_batch_id (v_batchstep_id NUMBER)
623 IS
624 SELECT batch_id
625 FROM gme_batch_steps
626 WHERE batchstep_id = v_batchstep_id;
627
628 CURSOR cur_get_scale_type (
629 v_resources gme_batch_step_resources.resources%TYPE)
630 IS
631 SELECT 1
632 FROM DUAL
633 WHERE EXISTS (
634 SELECT 1
635 FROM gme_batch_step_resources
636 WHERE batchstep_id = p_step_id
637 AND resources = v_resources
638 AND scale_type = 2);
639
640 l_scale_type gme_batch_step_resources.scale_type%TYPE;
641 l_remaining_quantity NUMBER;
642 l_batch_step_charges_in gme_batch_step_charges%ROWTYPE;
643 l_return_status VARCHAR2 (1);
644 l_charge gme_batch_steps.plan_charges%TYPE;
645 l_batch_id NUMBER;
646 l_rsrc_plan_start_date DATE;
647 l_rsrc_plan_cmplt_date DATE;
648 error_charge_insert EXCEPTION;
649 error_step_not_found EXCEPTION;
650 error_calc_charge_conv EXCEPTION;
651 error_max_cap_not_mass_vol EXCEPTION;
652 BEGIN
653 IF g_debug <= gme_debug.g_log_procedure THEN
654 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
655 || l_api_name);
656 END IF;
657
658 /* Initialize the return status to success */
659 x_return_status := fnd_api.g_ret_sts_success;
660 x_charge := 0;
661 l_remaining_quantity := -1;
662
663 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
664 gme_debug.put_line('pmassqty: '||p_mass_qty);
665 gme_debug.put_line('pvolqty: '||p_vol_qty);
666 END IF;
667
668 /* Get the max capacity and uom for the current step */
669 OPEN cur_get_max_capacity;
670
671 FETCH cur_get_max_capacity
672 INTO x_max_cap, x_max_cap_uom, x_step_no, x_step_qty, x_step_qty_uom;
673
674 IF cur_get_max_capacity%NOTFOUND THEN
675 RAISE error_step_not_found;
676 END IF;
677
678 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
679 gme_debug.put_line('Max capacity, UOM: '||x_max_cap||', '||x_max_cap_uom);
680 gme_debug.put_line('Step Qty, UOM: '||x_step_qty||', '||x_step_qty_uom);
681 END IF;
682
683 /* Bug#5231180 Begin if we pass step qty and max capacity use them otherwise get the values
684 from table. these will be passed from recalculate charges procedure */
685 x_step_qty := NVL(p_step_qty,x_step_qty);
686 x_max_cap := NVL(p_max_capacity,x_max_cap);
687
688 --added zero step qty check
689 IF nvl(x_step_qty ,0) = 0 THEN
690 x_charge := 0;
691 RETURN;
692 END IF;
693 --Bug#5231180 End
694
695 /*Defaulting x_charge to 1 if max_cap returned by cursor is null*/
696 IF x_max_cap IS NULL THEN
697 x_charge := 1;
698 RETURN;
699 END IF;
700
701 IF x_max_cap_uom IS NOT NULL THEN
702 /* Get the standard factor and uom type from the capacity uom */
703 OPEN cur_get_std_factor (x_max_cap_uom);
704
705 FETCH cur_get_std_factor
706 INTO x_um_type, x_std_factor;
707
708 CLOSE cur_get_std_factor;
709
710 x_std_capacity := x_max_cap * x_std_factor;
711
712 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
713 gme_debug.put_line('Standard Factor: '||x_std_factor);
714 gme_debug.put_line('Standard Capacity: '||x_std_capacity);
715 END IF;
716
717 --Bug2617151
718 OPEN cur_get_tolerance;
719
720 FETCH cur_get_tolerance
721 INTO x_tolerance;
722
723 CLOSE cur_get_tolerance;
724
725 x_stdum_tolerance := (x_tolerance * x_std_capacity) / 100;
726
727 -- Capacity will be in either mass or volume...
728 IF x_um_type = gme_common_pvt.g_mass_um_type THEN
729 IF (p_mass_qty IS NULL) THEN
730 RAISE error_calc_charge_conv;
731 END IF;
732
733 IF (MOD (p_mass_qty, x_std_capacity) > x_stdum_tolerance) THEN
734 x_charge := CEIL (p_mass_qty / x_std_capacity);
735 l_remaining_quantity := MOD (p_mass_qty, x_std_capacity);
736 ELSE
737 x_charge := TRUNC (p_mass_qty / x_std_capacity);
738 END IF;
739
740 ELSIF x_um_type = gme_common_pvt.g_volume_um_type THEN
741 IF (p_vol_qty IS NULL) THEN
742 RAISE error_calc_charge_conv;
743 END IF;
744
745 IF (MOD (p_vol_qty, x_std_capacity) > x_stdum_tolerance) THEN
746 x_charge := CEIL (p_vol_qty / x_std_capacity);
747 l_remaining_quantity := MOD (p_vol_qty, x_std_capacity);
748 ELSE
749 x_charge := TRUNC (p_vol_qty / x_std_capacity);
750 END IF;
751
752 ELSE
753 x_temp_qty :=
754 inv_convert.inv_um_convert
755 (item_id => 0
756 ,PRECISION => gme_common_pvt.g_precision
757 ,from_quantity => x_step_qty
758 ,from_unit => x_step_qty_uom
759 ,to_unit => x_max_cap_uom
760 ,from_name => NULL
761 ,to_name => NULL);
762
763 IF (x_temp_qty < 0) THEN
764 RAISE error_calc_charge_conv;
765 ELSE
766 /* Bug#5231180 Begin when we come here x_temp_qty is in the same uom as x_max_cap. x_tolerance will be in %
767 so now we have to calculate the % of x_max_cap like we did in the above case. replaced x_tolerance
768 with calculated x_stdum_tolerance in the following IF condition*/
769 x_stdum_tolerance := (x_tolerance * x_max_cap) / 100;
770 --calculate the factor
771 x_std_factor := x_temp_qty / x_step_qty ;
772 IF (MOD (x_temp_qty, x_max_cap) > x_stdum_tolerance) THEN
773 --Bug#5231180 End
774 x_charge := CEIL (x_temp_qty / x_max_cap);
775 --Rishi Varma bug 3307549 05/05/2004.
776 l_remaining_quantity := MOD (x_temp_qty, x_max_cap);
777 ELSE
778 x_charge := TRUNC (x_temp_qty / x_max_cap);
779 END IF;
780 END IF; /* if (x_temp_qty < 0) then */
781 END IF;
782 END IF; /*if x_max_cap_uom is not null then */
783
784 /*Checking the scale type of the resource before
785 populating the charges table*/
786 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
787 gme_debug.put_line ('p_resources is' || p_resources);
788 END IF;
789
790 IF p_resources IS NOT NULL THEN
791 OPEN cur_get_scale_type (p_resources);
792 FETCH cur_get_scale_type
793 INTO l_scale_type;
794
795 CLOSE cur_get_scale_type;
796
797 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
798 gme_debug.put_line ('l_scale_type is' || l_scale_type);
799 END IF;
800
801 IF l_scale_type = 1 THEN
802 --Generating the charge details and populating the gme_batch_step_charges table.
803 l_charge := x_charge;
804
805 OPEN cur_get_batch_id (p_step_id);
806
807 FETCH cur_get_batch_id
808 INTO l_batch_id;
809
810 CLOSE cur_get_batch_id;
811
812 --Converting back the remaining quantity to the step uom.
813 /* Bug#5231180 calculate remaining qty back to step uom. if l_remaining_qty is -1 then
814 there is not remaining qty in the calculation of charges */
815 IF x_std_factor <> 1 AND l_remaining_quantity <> -1 THEN
816 l_remaining_quantity := (l_remaining_quantity / x_std_factor);
817 END IF;
818
819 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
820 gme_debug.put_line ( 'l_remaining_quantity is'
821 || l_remaining_quantity);
822 END IF;
823
824 l_batch_step_charges_in.batch_id := l_batch_id;
825 l_batch_step_charges_in.batchstep_id := p_step_id;
826 l_batch_step_charges_in.resources := p_resources;
827 l_batch_step_charges_in.charge_quantity := x_max_cap;
828 l_batch_step_charges_in.plan_start_date := l_rsrc_plan_start_date;
829 l_batch_step_charges_in.plan_cmplt_date := l_rsrc_plan_cmplt_date;
830
831 --Rishi Varma B3718176 23-07-2004
832 gme_batch_step_chg_pvt.populate_charges_table
833 (p_batchstep_charges_in => l_batch_step_charges_in
834 ,p_no_of_charges => l_charge
835 ,p_remaining_quantity => l_remaining_quantity
836 ,x_return_status => l_return_status);
837
838 IF l_return_status <> fnd_api.g_ret_sts_success THEN
839 RAISE error_charge_insert;
840 END IF;
841 END IF; /*if l_scale_type = 1*/
842 END IF; /*if p_resource is not null*/
843
844 IF g_debug <= gme_debug.g_log_procedure THEN
845 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
846 END IF;
847 EXCEPTION
848 WHEN error_step_not_found THEN
849 x_return_status := fnd_api.g_ret_sts_error;
850 gme_common_pvt.log_message ('GME_BATCH_STEP_NOT_FOUND'
851 ,'STEP_ID'
852 ,p_step_id);
853 WHEN error_calc_charge_conv THEN
854 -- x_return_status := FND_API.G_RET_STS_ERROR;
855 gme_common_pvt.log_message ('GME_CALC_CHARGE_CONV_ERROR'
856 ,'STEP_NO'
857 ,x_step_no);
858 WHEN error_max_cap_not_mass_vol THEN
859 gme_common_pvt.log_message
860 ('GME_MAX_CAP_NOT_MASS_VOL_ERR'
861 ,'STEP_NO'
862 ,x_step_no
863 ,'MASS'
864 ,gme_common_pvt.g_mass_um_type
865 ,'VOLUME'
866 ,gme_common_pvt.g_volume_um_type);
867 WHEN error_charge_insert THEN
868 gme_common_pvt.log_message ('GME_INSERT_CHARGE_ERROR'
869 ,'STEP_NO'
870 ,x_step_no);
871 WHEN OTHERS THEN
872 IF g_debug <= gme_debug.g_log_unexpected THEN
873 gme_debug.put_line ( 'When others exception in '
874 || g_pkg_name
875 || '.'
876 || l_api_name
877 || ' Error is '
878 || SQLERRM);
879 END IF;
880
881 x_return_status := fnd_api.g_ret_sts_unexp_error;
882 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
883 END calc_charge;
884
885 /*===========================================================================================
886 Procedure
887 update_activities
888 Description
889 This particular procedure is used to update the activities associated with a batch step
890 Parameters
891 p_batch_hdr_rec Batch Header Record
892 p_batch_step_rec Batch Step Line
893 x_return_status outcome of the API call
894 S - Success
895 E - Error
896 U - Unexpected error
897 =============================================================================================*/
898 PROCEDURE update_activities (
899 p_batch_hdr_rec IN gme_batch_header%ROWTYPE
900 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE
901 ,x_return_status OUT NOCOPY VARCHAR2
902 ,p_routing_scale_factor IN NUMBER DEFAULT NULL
903 ,p_backflush_factor IN NUMBER DEFAULT NULL
904 ,p_charge_diff IN NUMBER
905 ,p_dependency_type IN NUMBER DEFAULT NULL)
906 IS
907 l_api_name CONSTANT VARCHAR2 (30) := 'update_activities';
908 /* Collections for details etc */
909 l_batchstep_activity_ids gme_common_pvt.number_tab;
910 l_gme_batchstep_activities gme_batch_step_activities%ROWTYPE;
911 l_gme_calc_int_rsrc_usage_char VARCHAR2 (10);
912 l_user_profile_option_name VARCHAR2 (100);
913 /* Local variables */
914 l_return_status VARCHAR2 (1);
915
916 CURSOR cur_step_activity_ids
917 IS
918 SELECT batchstep_activity_id
919 FROM gme_batch_step_activities
920 WHERE batch_id = p_batch_step_rec.batch_id
921 AND batchstep_id = p_batch_step_rec.batchstep_id
922 ORDER BY batchstep_id;
923
924 /* Exceptions */
925 step_activity_fetch_error EXCEPTION;
926 step_resource_upd_err EXCEPTION;
927 step_activity_upd_err EXCEPTION;
928 BEGIN
929 IF g_debug <= gme_debug.g_log_procedure THEN
930 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
931 || l_api_name);
932 END IF;
933
934 /* Initialize the return status to success */
935 x_return_status := fnd_api.g_ret_sts_success;
936
937 /* Fetch all the activities associated with the batch step */
938 OPEN cur_step_activity_ids;
939
940 FETCH cur_step_activity_ids
941 BULK COLLECT INTO l_batchstep_activity_ids;
942
943 CLOSE cur_step_activity_ids;
944
945 /* Fetch all the batch step activities details */
946 FOR i IN 1 .. l_batchstep_activity_ids.COUNT LOOP
947 l_gme_batchstep_activities.batchstep_activity_id :=
948 l_batchstep_activity_ids (i);
949
950 IF NOT (gme_batch_step_activities_dbl.fetch_row
951 (l_gme_batchstep_activities
952 ,l_gme_batchstep_activities) ) THEN
953 RAISE step_activity_fetch_error;
954 END IF;
955
956 /* If automatic step qty calculation is set for the batch then */
957 /* get the profile value for GME_CALC_INT_RSRC_USAGE to determine */
958 /* whether activity factor and later on resource qty and usage */
959 /* should be calculated for WIP step in ASQC batch */
960 IF p_batch_hdr_rec.automatic_step_calculation = 1 THEN
961 IF (p_gme_calc_int_rsrc_usage IS NULL) THEN
962 l_gme_calc_int_rsrc_usage_char := NULL;
963 l_gme_calc_int_rsrc_usage_char :=
964 gme_common_pvt.g_calc_interim_rsrc_usg_ind;
965 p_gme_calc_int_rsrc_usage := l_gme_calc_int_rsrc_usage_char;
966 l_gme_calc_int_rsrc_usage_char := NULL;
967 END IF;
968 ELSE
969 p_gme_calc_int_rsrc_usage := NULL;
970 END IF;
971
972 IF ( ( ( (p_batch_step_rec.step_status =
973 gme_common_pvt.g_step_wip)
974 AND (NVL (p_gme_calc_int_rsrc_usage, 0) = 1)
975 OR NVL (gme_common_pvt.g_backflush_rsrc_usg_ind, 0) =
976 1)
977 OR (p_batch_step_rec.step_status >
978 gme_common_pvt.g_step_wip) )
979 AND (NVL (l_gme_batchstep_activities.actual_activity_factor, 0) =
980 0) ) THEN
981 l_gme_batchstep_activities.actual_activity_factor :=
982 l_gme_batchstep_activities.plan_activity_factor;
983 ELSIF (p_batch_step_rec.step_status = gme_common_pvt.g_step_pending) THEN
984 l_gme_batchstep_activities.actual_activity_factor := NULL;
985 END IF;
986
987 /* Let us update all the resources attached to the activity */
988 gme_update_step_qty_pvt.update_resources
989 (p_batch_hdr_rec => p_batch_hdr_rec
990 ,p_batch_step_rec => p_batch_step_rec
991 ,p_batchstep_activities_rec => l_gme_batchstep_activities
992 ,x_return_status => l_return_status
993 ,p_routing_scale_factor => p_routing_scale_factor
994 ,p_backflush_factor => p_backflush_factor
995 ,p_charge_diff => p_charge_diff
996 ,p_dependency_type => p_dependency_type);
997
998 IF l_return_status <> x_return_status THEN
999 RAISE step_resource_upd_err;
1000 END IF;
1001
1002 /* Save the updated batch step activities row to the database */
1003 IF NOT (gme_batch_step_activities_dbl.update_row
1004 (l_gme_batchstep_activities) ) THEN
1005 RAISE step_activity_upd_err;
1006 END IF;
1007 END LOOP; /* FOR i IN 1..l_batchstep_activity_ids.COUNT LOOP */
1008
1009 IF g_debug <= gme_debug.g_log_procedure THEN
1010 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1011 END IF;
1012 EXCEPTION
1013 WHEN step_activity_fetch_error THEN
1014 x_return_status := fnd_api.g_ret_sts_error;
1015 WHEN step_resource_upd_err THEN
1016 x_return_status := l_return_status;
1017 WHEN step_activity_upd_err THEN
1018 x_return_status := fnd_api.g_ret_sts_error;
1019 WHEN VALUE_ERROR THEN
1020 x_return_status := fnd_api.g_ret_sts_error;
1021
1022 IF l_gme_calc_int_rsrc_usage_char IS NOT NULL THEN
1023 l_user_profile_option_name := gme_common_pvt.g_calc_interim_rsrc_usg_ind;
1024 /* SELECT user_profile_option_name
1025 INTO l_user_profile_option_name
1026 FROM fnd_profile_options_vl
1027 WHERE application_id = 553
1028 AND profile_option_name = 'GME_CALC_INT_RSRC_USAGE';*/
1029
1030 gme_common_pvt.log_message ('GME_INVALID_VALUE_PROFILE'
1031 ,'VALUE'
1032 ,l_gme_calc_int_rsrc_usage_char
1033 ,'PROFILE'
1034 ,l_user_profile_option_name);
1035 ELSE
1036 fnd_msg_pub.add_exc_msg ('gme_update_step_qty_pvt'
1037 ,'UPDATE_ACTIVITIES');
1038 END IF;
1039 WHEN OTHERS THEN
1040 IF g_debug <= gme_debug.g_log_unexpected THEN
1041 gme_debug.put_line ( 'When others exception in '
1042 || g_pkg_name
1043 || '.'
1044 || l_api_name
1045 || ' Error is '
1046 || SQLERRM);
1047 END IF;
1048
1049 x_return_status := fnd_api.g_ret_sts_unexp_error;
1050 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1051 END update_activities;
1052
1053 /*===========================================================================================
1054 Procedure
1055 update_resources
1056 Description
1057 This particular procedure is used to update the resources associated with a activity
1058 Parameters
1059 p_batch_hdr_rec Batch Header Record
1060 p_batch_step_rec Batch Step Line
1061 p_batchstep_activities_rec Batch Step Activity Line
1062 x_return_status outcome of the API call
1063 S - Success
1064 E - Error
1065 U - Unexpected error
1066 History
1067 =============================================================================================*/
1068 PROCEDURE update_resources (
1069 p_batch_hdr_rec IN gme_batch_header%ROWTYPE
1070 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE
1071 ,p_batchstep_activities_rec IN gme_batch_step_activities%ROWTYPE
1072 ,x_return_status OUT NOCOPY VARCHAR2
1073 ,p_routing_scale_factor IN NUMBER DEFAULT NULL
1074 ,p_backflush_factor IN NUMBER DEFAULT NULL
1075 ,p_charge_diff IN NUMBER DEFAULT NULL
1076 ,p_dependency_type IN NUMBER DEFAULT NULL)
1077 IS
1078 l_api_name CONSTANT VARCHAR2 (30) := 'update_resources';
1079 /* Collections for details etc */
1080 l_batchstep_resource_ids gme_common_pvt.number_tab;
1081 l_gme_batchstep_resources gme_batch_step_resources%ROWTYPE;
1082 l_resource_txns gme_resource_txns_gtmp%ROWTYPE;
1083 l_resource_tab gme_common_pvt.resource_transactions_tab;
1084 /* Local variables */
1085 l_sum_comp_usage NUMBER;
1086 l_override_usage NUMBER;
1087 l_actual_usage NUMBER;
1088 l_usage NUMBER;
1089 l_process_qty NUMBER;
1090 l_resource_usage NUMBER;
1091 l_alloc_usage NUMBER;
1092 l_prev_plan_actv_fact NUMBER;
1093 l_prev_actual_actv_fact NUMBER;
1094 l_prev_plan_charges NUMBER;
1095 l_prev_plan_step_qty NUMBER;
1096 l_return_status VARCHAR2 (1);
1097 l_allow_qty_below_cap NUMBER (5);
1098 l_copy_plan_to_actual NUMBER (5);
1099 /* Bug 2685645 added variable */
1100 l_doc_type VARCHAR2 (4);
1101 l_actual_resource_usage NUMBER;
1102
1103 CURSOR cur_step_resource_ids (v_batchstep_activity_id NUMBER)
1104 IS
1105 SELECT batchstep_resource_id
1106 FROM gme_batch_step_resources
1107 WHERE batchstep_activity_id = v_batchstep_activity_id;
1108
1109 CURSOR cur_sum_override_resource (
1110 v_batchstep_resource_id NUMBER
1111 ,v_doc_type VARCHAR2
1112 ,v_doc_id NUMBER)
1113 IS
1114 SELECT SUM (resource_usage)
1115 FROM gme_resource_txns
1116 WHERE line_id = v_batchstep_resource_id
1117 AND doc_type = v_doc_type
1118 AND doc_id = v_doc_id
1119 AND completed_ind = 1
1120 AND overrided_protected_ind = 'Y';
1121
1122 CURSOR cur_prev_actv_fact (v_batchstep_activity_id NUMBER)
1123 IS
1124 SELECT plan_activity_factor, actual_activity_factor
1125 FROM gme_batch_step_activities
1126 WHERE batchstep_activity_id = v_batchstep_activity_id;
1127
1128 CURSOR cur_prev_plan_charge
1129 IS
1130 SELECT plan_charges
1131 FROM gme_batch_steps
1132 WHERE batchstep_id = p_batch_step_rec.batchstep_id;
1133
1134 /* Exceptions */
1135 activity_resource_fetch_error EXCEPTION;
1136 resource_trans_ins_err EXCEPTION;
1137 step_resource_upd_err EXCEPTION;
1138 error_txns_update EXCEPTION;
1139 error_fetch_rsrc_tran EXCEPTION;
1140 qty_below_cap_error EXCEPTION;
1141 BEGIN
1142 IF g_debug <= gme_debug.g_log_procedure THEN
1143 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1144 || l_api_name);
1145 END IF;
1146
1147 /* Initialize the return status to success */
1148 x_return_status := fnd_api.g_ret_sts_success;
1149
1150 IF (p_batch_hdr_rec.batch_type = 0) THEN
1151 l_doc_type := 'PROD';
1152 ELSE
1153 l_doc_type := 'FPO';
1154 END IF;
1155
1156 l_allow_qty_below_cap :=
1157 NVL (gme_common_pvt.g_allow_qty_below_min_ind, 1);
1158 l_copy_plan_to_actual :=
1159 NVL (gme_common_pvt.g_def_actual_rsrc_usg_ind, 1);
1160
1161 /* Fetch all the resources associated with the activity */
1162 OPEN cur_step_resource_ids
1163 (p_batchstep_activities_rec.batchstep_activity_id);
1164
1165 FETCH cur_step_resource_ids
1166 BULK COLLECT INTO l_batchstep_resource_ids;
1167
1168 CLOSE cur_step_resource_ids;
1169
1170 /* Fetch all the activity resources details */
1171 FOR i IN 1 .. l_batchstep_resource_ids.COUNT LOOP
1172 l_gme_batchstep_resources.batchstep_resource_id :=
1173 l_batchstep_resource_ids (i);
1174
1175 IF NOT (gme_batch_step_resources_dbl.fetch_row
1176 (l_gme_batchstep_resources
1177 ,l_gme_batchstep_resources) ) THEN
1178 RAISE activity_resource_fetch_error;
1179 END IF;
1180
1181 IF (l_gme_batchstep_resources.actual_start_date IS NULL) THEN
1182 l_gme_batchstep_resources.actual_start_date :=
1183 p_batch_step_rec.actual_start_date;
1184 END IF;
1185
1186 /* Get the Operation resource qty and usage for the calculations */
1187 /* First check for any overriden process qty or resource usage */
1188 /* at the recipe level */
1189 l_process_qty := l_gme_batchstep_resources.original_rsrc_qty;
1190 l_resource_usage := l_gme_batchstep_resources.original_rsrc_usage;
1191
1192 /* If automatic step qty calculation is set for the batch then */
1193 IF p_batch_hdr_rec.automatic_step_calculation = 1 THEN
1194 /* If the step status is greater than pending then we have to calculate the actual quantities
1195 calculate resource qty and usage for WIP asqc batch if profile is set */
1196 IF ( ( (p_batch_step_rec.step_status =
1197 gme_common_pvt.g_step_wip)
1198 AND (NVL (p_gme_calc_int_rsrc_usage, 0) = 1) )
1199 OR (p_batch_step_rec.step_status > gme_common_pvt.g_step_wip) ) THEN
1200 IF l_process_qty <> 0 THEN
1201 l_gme_batchstep_resources.actual_rsrc_qty :=
1202 p_batch_step_rec.actual_step_qty
1203 * p_batchstep_activities_rec.actual_activity_factor;
1204 ELSE
1205 l_gme_batchstep_resources.actual_rsrc_qty := l_process_qty;
1206 END IF;
1207
1208 /* If the resource scale type is linear then */
1209 IF l_gme_batchstep_resources.scale_type = 1 THEN
1210 IF l_process_qty = 0 THEN
1211 l_actual_resource_usage := 0;
1212 ELSE
1213 l_actual_resource_usage :=
1214 ( (p_batch_step_rec.actual_step_qty
1215 / l_process_qty)
1216 * l_resource_usage)
1217 * p_batchstep_activities_rec.actual_activity_factor;
1218 END IF;
1219
1220 l_gme_batchstep_resources.actual_rsrc_usage :=
1221 l_actual_resource_usage;
1222 /* If the resource scale type is calculate by charges then */
1223 ELSIF l_gme_batchstep_resources.scale_type = 2 THEN
1224 l_gme_batchstep_resources.actual_rsrc_usage :=
1225 l_resource_usage
1226 * p_batch_step_rec.actual_charges
1227 * p_batchstep_activities_rec.actual_activity_factor;
1228 /* If the resource scale type is fixed then */
1229 ELSE
1230 l_gme_batchstep_resources.actual_rsrc_usage :=
1231 l_resource_usage
1232 * p_batchstep_activities_rec.actual_activity_factor;
1233 END IF; /*IF l_gme_batchstep_resources.scale_type = 1*/
1234
1235 /* Default the Actual Resource Count to the Planned Resource */
1236 /* Count if the Actual Resource Count is zero. */
1237 IF (NVL (l_gme_batchstep_resources.actual_rsrc_count, 0) =
1238 0)
1239 AND (p_batch_step_rec.step_status >
1240 gme_common_pvt.g_step_pending) THEN
1241 l_gme_batchstep_resources.actual_rsrc_count :=
1242 l_gme_batchstep_resources.plan_rsrc_count;
1243 END IF;
1244
1245 IF p_batch_step_rec.step_status = gme_common_pvt.g_step_wip THEN
1246 l_gme_batchstep_resources.plan_rsrc_count :=
1247 NVL (l_gme_batchstep_resources.plan_rsrc_count, 1);
1248 l_gme_batchstep_resources.plan_rsrc_usage :=
1249 NVL (l_gme_batchstep_resources.plan_rsrc_usage, 0);
1250 l_gme_batchstep_resources.plan_rsrc_qty :=
1251 NVL (l_gme_batchstep_resources.plan_rsrc_qty, 0);
1252 END IF;
1253 /* If the step status is pending then we have to update the plan quantities */
1254 ELSIF p_batch_step_rec.step_status = gme_common_pvt.g_step_pending THEN
1255 l_gme_batchstep_resources.plan_rsrc_qty :=
1256 p_batch_step_rec.plan_step_qty
1257 * p_batchstep_activities_rec.plan_activity_factor;
1258
1259 /* If the resource scale type is linear then */
1260 IF l_gme_batchstep_resources.scale_type = 1 THEN
1261 l_gme_batchstep_resources.plan_rsrc_usage :=
1262 ( (p_batch_step_rec.plan_step_qty / l_process_qty)
1263 * l_resource_usage)
1264 * p_batchstep_activities_rec.plan_activity_factor;
1265 /* If the resource scale type is calculate by charges then */
1266 ELSIF l_gme_batchstep_resources.scale_type = 2 THEN
1267 l_gme_batchstep_resources.plan_rsrc_usage :=
1268 l_resource_usage
1269 * p_batch_step_rec.plan_charges
1270 * p_batchstep_activities_rec.plan_activity_factor;
1271 /* If the resource scale type is fixed then */
1272 ELSE
1273 l_gme_batchstep_resources.plan_rsrc_usage :=
1274 l_resource_usage
1275 * p_batchstep_activities_rec.plan_activity_factor;
1276 END IF; /*IF l_gme_batchstep_resources.scale_type = 1*/
1277
1278 l_gme_batchstep_resources.actual_rsrc_usage := NULL;
1279 l_gme_batchstep_resources.actual_rsrc_qty := NULL;
1280 l_gme_batchstep_resources.actual_rsrc_count := NULL;
1281 END IF; /*IF p_batch_step_rec.step_status > 1*/
1282 ELSIF (p_batch_step_rec.step_status > gme_common_pvt.g_step_wip) THEN
1283 OPEN cur_prev_actv_fact
1284 (p_batchstep_activities_rec.batchstep_activity_id);
1285
1286 FETCH cur_prev_actv_fact
1287 INTO l_prev_plan_actv_fact, l_prev_actual_actv_fact;
1288
1289 CLOSE cur_prev_actv_fact;
1290
1291 /* copy plan to actual only if profile is set moved code calculating usage above */
1292 /* the calculation for process qty since calculation for process qty depends on usage */
1293 IF ( NVL (l_gme_batchstep_resources.actual_rsrc_usage, 0) = 0
1294 AND l_copy_plan_to_actual = 1) THEN
1295 /* If the resource scale type is linear then */
1296 IF l_gme_batchstep_resources.scale_type = 1 THEN
1297 IF p_batchstep_activities_rec.plan_activity_factor > 0 THEN
1298 l_gme_batchstep_resources.actual_rsrc_usage :=
1299 ( l_gme_batchstep_resources.plan_rsrc_usage
1300 / p_batchstep_activities_rec.plan_activity_factor)
1301 * p_batchstep_activities_rec.actual_activity_factor;
1302 /* BACKING OUTTHE FIX DECSION HAS TO BE MADE HOW TO IMPLEMENT */
1303 /* If actual_rsrc_qty exists then calculate usage based on that */
1304 /* IF (NVL(l_gme_batchstep_resources.actual_rsrc_qty, 0) > 0) THEN
1305 l_gme_batchstep_resources.actual_rsrc_usage := l_gme_batchstep_resources.actual_rsrc_usage *
1306 (l_gme_batchstep_resources.actual_rsrc_qty / l_gme_batchstep_resources.plan_rsrc_qty);
1307 END IF; */
1308 ELSE
1309 l_gme_batchstep_resources.actual_rsrc_usage :=
1310 l_gme_batchstep_resources.plan_rsrc_usage
1311 * p_batchstep_activities_rec.actual_activity_factor;
1312 END IF;
1313 /* If the resource scale type is calculate by charges then */
1314 ELSIF l_gme_batchstep_resources.scale_type = 2 THEN
1315 IF p_batch_step_rec.plan_charges <> 0 THEN
1316 IF p_batchstep_activities_rec.plan_activity_factor > 0 THEN
1317 l_gme_batchstep_resources.actual_rsrc_usage :=
1318 ( l_gme_batchstep_resources.plan_rsrc_usage
1319 / ( p_batchstep_activities_rec.plan_activity_factor
1320 * p_batch_step_rec.plan_charges) )
1321 * p_batchstep_activities_rec.actual_activity_factor
1322 * p_batch_step_rec.actual_charges;
1323 ELSE
1324 l_gme_batchstep_resources.actual_rsrc_usage :=
1325 ( l_gme_batchstep_resources.plan_rsrc_usage
1326 / p_batch_step_rec.plan_charges)
1327 * p_batchstep_activities_rec.actual_activity_factor
1328 * p_batch_step_rec.actual_charges;
1329 END IF;
1330 ELSE
1331 l_gme_batchstep_resources.actual_rsrc_usage := 0;
1332 END IF;
1333 /* If the resource scale type is fixed then */
1334 ELSE
1335 l_gme_batchstep_resources.actual_rsrc_usage :=
1336 l_gme_batchstep_resources.plan_rsrc_usage;
1337 END IF; /*IF l_gme_batchstep_resources.scale_type = 1*/
1338 /* copy plan to actual only if profile is set */
1339 ELSIF NVL (l_prev_actual_actv_fact, 0) > 0
1340 AND l_copy_plan_to_actual = 1 THEN
1341 l_gme_batchstep_resources.actual_rsrc_usage :=
1342 ( l_gme_batchstep_resources.actual_rsrc_usage
1343 / l_prev_actual_actv_fact)
1344 * p_batchstep_activities_rec.actual_activity_factor;
1345 END IF; /*IF (l_gme_batchstep_resources.actual_rsrc_usage = 0)*/
1346
1347 /* if at end of calculation usage is null assign zero */
1348 l_gme_batchstep_resources.actual_rsrc_usage :=
1349 NVL (l_gme_batchstep_resources.actual_rsrc_usage, 0);
1350
1351 IF (NVL (l_gme_batchstep_resources.actual_rsrc_qty, 0) = 0) THEN
1352 IF l_gme_batchstep_resources.scale_type = 0 THEN
1353 /* Bug 2506750 copy plan to actual only if profile is set and conditions satisfy */
1354 IF ( NVL (l_gme_batchstep_resources.actual_rsrc_usage, 0) >
1355 0
1356 OR ( NVL
1357 (l_gme_batchstep_resources.actual_rsrc_usage
1358 ,0) = 0
1359 AND l_copy_plan_to_actual = 1) ) THEN
1360 l_gme_batchstep_resources.actual_rsrc_qty :=
1361 l_gme_batchstep_resources.plan_rsrc_qty
1362 * p_batchstep_activities_rec.actual_activity_factor;
1363 END IF;
1364 /* copy plan to actual only if profile is set */
1365 ELSIF (l_copy_plan_to_actual = 1) THEN
1366 /* calculate actual_rsrc_qty from plan_rsrc_qty not from step */
1367 --l_gme_batchstep_resources.actual_rsrc_qty := p_batch_step_rec.actual_step_qty * p_batchstep_activities_rec.actual_activity_factor;
1368 IF NVL (l_prev_actual_actv_fact, 0) > 0 THEN
1369 l_gme_batchstep_resources.actual_rsrc_qty :=
1370 ( l_gme_batchstep_resources.plan_rsrc_qty
1371 * p_batchstep_activities_rec.actual_activity_factor)
1372 / l_prev_actual_actv_fact;
1373 ELSE
1374 l_gme_batchstep_resources.actual_rsrc_qty :=
1375 l_gme_batchstep_resources.plan_rsrc_qty
1376 * p_batchstep_activities_rec.actual_activity_factor;
1377 END IF;
1378 END IF;
1379 /* copy plan to actual only if profile is set and conditions satisfy */
1380 ELSIF NVL (l_prev_actual_actv_fact, 0) > 0
1381 AND l_copy_plan_to_actual = 1 THEN
1382 l_gme_batchstep_resources.actual_rsrc_qty :=
1383 ( l_gme_batchstep_resources.actual_rsrc_qty
1384 / l_prev_actual_actv_fact)
1385 * p_batchstep_activities_rec.actual_activity_factor;
1386 END IF; /*IF (l_gme_batchstep_resources.actual_rsrc_qty = 0)*/
1387
1388 /* if at end of calculation usage is null assign zero */
1389 l_gme_batchstep_resources.actual_rsrc_qty :=
1390 NVL (l_gme_batchstep_resources.actual_rsrc_qty, 0);
1391
1392 /* Default the Actual Resource Count to the Planned Resource */
1393 /* Count if the Actual Resource Count is zero. */
1394 IF (NVL (l_gme_batchstep_resources.actual_rsrc_count, 0) = 0) THEN
1395 l_gme_batchstep_resources.actual_rsrc_count :=
1396 l_gme_batchstep_resources.plan_rsrc_count;
1397 END IF;
1398
1399 /* (New Fix) if at end of calculation usage is null assign zero */
1400 l_gme_batchstep_resources.actual_rsrc_count :=
1401 NVL (l_gme_batchstep_resources.actual_rsrc_count, 1);
1402 ELSIF (p_batch_step_rec.step_status = gme_common_pvt.g_step_pending) THEN
1403 OPEN cur_prev_actv_fact
1404 (p_batchstep_activities_rec.batchstep_activity_id);
1405
1406 FETCH cur_prev_actv_fact
1407 INTO l_prev_plan_actv_fact, l_prev_actual_actv_fact;
1408
1409 CLOSE cur_prev_actv_fact;
1410
1411 OPEN cur_prev_plan_charge;
1412
1413 FETCH cur_prev_plan_charge
1414 INTO l_prev_plan_charges;
1415
1416 CLOSE cur_prev_plan_charge;
1417
1418 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1419 gme_debug.put_line ( ' plan rsrc qty:'
1420 || l_gme_batchstep_resources.plan_rsrc_qty
1421 || ' Plan actv:'
1422 || l_prev_plan_actv_fact);
1423 END IF;
1424
1425 /* Calculate resource qty correctly when scaling batch */
1426 IF ( p_routing_scale_factor IS NOT NULL
1427 AND l_gme_batchstep_resources.scale_type = 1) THEN
1428 l_gme_batchstep_resources.plan_rsrc_qty :=
1429 l_gme_batchstep_resources.plan_rsrc_qty
1430 * p_routing_scale_factor;
1431 l_gme_batchstep_resources.plan_rsrc_usage :=
1432 l_gme_batchstep_resources.plan_rsrc_usage
1433 * p_routing_scale_factor;
1434 END IF;
1435
1436 IF l_prev_plan_actv_fact > 0 THEN
1437 l_gme_batchstep_resources.plan_rsrc_qty :=
1438 ( l_gme_batchstep_resources.plan_rsrc_qty
1439 * p_batchstep_activities_rec.plan_activity_factor)
1440 / l_prev_plan_actv_fact;
1441 ELSE
1442 l_gme_batchstep_resources.plan_rsrc_qty :=
1443 l_gme_batchstep_resources.plan_rsrc_qty
1444 * p_batchstep_activities_rec.plan_activity_factor;
1445 END IF;
1446
1447 IF l_prev_plan_actv_fact > 0 THEN
1448 l_gme_batchstep_resources.plan_rsrc_usage :=
1449 ( l_gme_batchstep_resources.plan_rsrc_usage
1450 / l_prev_plan_actv_fact)
1451 * p_batchstep_activities_rec.plan_activity_factor;
1452 ELSE
1453 l_gme_batchstep_resources.plan_rsrc_usage :=
1454 l_gme_batchstep_resources.plan_rsrc_usage
1455 * p_batchstep_activities_rec.plan_activity_factor;
1456 END IF;
1457
1458 /* If the resource scale type is calculate by charges then */
1459 IF l_gme_batchstep_resources.scale_type = 2 THEN
1460 IF (NVL (l_prev_plan_charges, 0) > 0)
1461 AND (NVL (p_batch_step_rec.plan_charges, 0) > 0) THEN
1462 l_gme_batchstep_resources.plan_rsrc_usage :=
1463 ( l_gme_batchstep_resources.plan_rsrc_usage
1464 / l_prev_plan_charges)
1465 * p_batch_step_rec.plan_charges;
1466 ELSIF (NVL (p_batch_step_rec.plan_charges, 0) > 0) THEN
1467 l_gme_batchstep_resources.plan_rsrc_usage :=
1468 l_gme_batchstep_resources.plan_rsrc_usage
1469 * p_batch_step_rec.plan_charges;
1470 END IF;
1471 END IF; /* IF l_gme_batchstep_resources.scale_type = 2 */
1472
1473 /* set plan values accordingly if unrelease, let above calculations go through and then we will
1474 see if we need to put zero and count = 1 since count has to be greater than 0 */
1475 l_gme_batchstep_resources.plan_rsrc_count :=
1476 NVL (l_gme_batchstep_resources.plan_rsrc_count, 1);
1477 l_gme_batchstep_resources.plan_rsrc_usage :=
1478 NVL (l_gme_batchstep_resources.plan_rsrc_usage, 0);
1479 l_gme_batchstep_resources.plan_rsrc_qty :=
1480 NVL (l_gme_batchstep_resources.plan_rsrc_qty, 0);
1481 l_gme_batchstep_resources.actual_rsrc_usage := NULL;
1482 l_gme_batchstep_resources.actual_rsrc_qty := NULL;
1483 l_gme_batchstep_resources.actual_rsrc_count := NULL;
1484 ELSIF p_batch_step_rec.step_status = gme_common_pvt.g_step_wip THEN
1485 IF (p_backflush_factor IS NOT NULL) THEN
1486 IF l_gme_batchstep_resources.scale_type = 1 THEN
1487 /* Proportional resource scale type */
1488 IF (p_batchstep_activities_rec.plan_activity_factor > 0) THEN
1489 IF ( NVL (p_dependency_type, 1) = 1
1490 OR NVL (l_gme_batchstep_resources.actual_rsrc_usage
1491 ,0) = 0) THEN
1492 l_gme_batchstep_resources.actual_rsrc_usage :=
1493 NVL
1494 (l_gme_batchstep_resources.actual_rsrc_usage
1495 ,0)
1496 + ( ( l_gme_batchstep_resources.plan_rsrc_usage
1497 * p_batchstep_activities_rec.actual_activity_factor
1498 * p_backflush_factor)
1499 / (p_batchstep_activities_rec.plan_activity_factor) );
1500 END IF;
1501
1502 IF ( NVL (p_dependency_type, 1) = 1
1503 OR NVL (l_gme_batchstep_resources.actual_rsrc_qty, 0) =
1504 0) THEN
1505 l_gme_batchstep_resources.actual_rsrc_qty :=
1506 NVL (l_gme_batchstep_resources.actual_rsrc_qty
1507 ,0)
1508 + ( ( l_gme_batchstep_resources.plan_rsrc_qty
1509 * p_batchstep_activities_rec.actual_activity_factor
1510 * p_backflush_factor)
1511 / (p_batchstep_activities_rec.plan_activity_factor) );
1512 END IF;
1513 ELSE
1514 IF ( NVL (p_dependency_type, 1) = 1
1515 OR NVL (l_gme_batchstep_resources.actual_rsrc_usage
1516 ,0) = 0) THEN
1517 l_gme_batchstep_resources.actual_rsrc_usage :=
1518 NVL
1519 (l_gme_batchstep_resources.actual_rsrc_usage
1520 ,0)
1521 + ( l_gme_batchstep_resources.plan_rsrc_usage
1522 * p_batchstep_activities_rec.actual_activity_factor
1523 * p_backflush_factor);
1524 END IF;
1525
1526 IF ( NVL (p_dependency_type, 1) = 1
1527 OR NVL (l_gme_batchstep_resources.actual_rsrc_qty, 0) =
1528 0) THEN
1529 l_gme_batchstep_resources.actual_rsrc_qty :=
1530 NVL (l_gme_batchstep_resources.actual_rsrc_qty
1531 ,0)
1532 + ( l_gme_batchstep_resources.plan_rsrc_qty
1533 * p_batchstep_activities_rec.actual_activity_factor
1534 * p_backflush_factor);
1535 END IF;
1536 END IF;
1537
1538 IF (l_gme_batchstep_resources.actual_rsrc_usage < 0) THEN
1539 l_gme_batchstep_resources.actual_rsrc_usage := 0;
1540 END IF;
1541
1542 IF (l_gme_batchstep_resources.actual_rsrc_qty < 0) THEN
1543 l_gme_batchstep_resources.actual_rsrc_qty := 0;
1544 END IF;
1545 ELSIF l_gme_batchstep_resources.scale_type = 2 THEN
1546 /* By charge resource scale type */
1547 IF (p_batch_step_rec.plan_charges <> 0) THEN
1548 IF (p_batchstep_activities_rec.plan_activity_factor > 0) THEN
1549 IF ( NVL (p_dependency_type, 1) = 1
1550 OR NVL
1551 (l_gme_batchstep_resources.actual_rsrc_usage
1552 ,0) = 0) THEN
1553 l_gme_batchstep_resources.actual_rsrc_usage :=
1554 NVL
1555 (l_gme_batchstep_resources.actual_rsrc_usage
1556 ,0)
1557 + ( ( l_gme_batchstep_resources.plan_rsrc_usage
1558 * p_charge_diff
1559 * p_batchstep_activities_rec.actual_activity_factor)
1560 / ( p_batchstep_activities_rec.plan_activity_factor
1561 * p_batch_step_rec.plan_charges) );
1562 END IF;
1563
1564 IF ( NVL (p_dependency_type, 1) = 1
1565 OR NVL (l_gme_batchstep_resources.actual_rsrc_qty
1566 ,0) = 0) THEN
1567 l_gme_batchstep_resources.actual_rsrc_qty :=
1568 NVL
1569 (l_gme_batchstep_resources.actual_rsrc_qty
1570 ,0)
1571 + ( ( l_gme_batchstep_resources.plan_rsrc_qty
1572 * p_charge_diff
1573 * p_batchstep_activities_rec.actual_activity_factor)
1574 / ( p_batchstep_activities_rec.plan_activity_factor
1575 * p_batch_step_rec.plan_charges) );
1576 END IF;
1577 ELSE
1578 IF ( NVL (p_dependency_type, 1) = 1
1579 OR NVL
1580 (l_gme_batchstep_resources.actual_rsrc_usage
1581 ,0) = 0) THEN
1582 l_gme_batchstep_resources.actual_rsrc_usage :=
1583 NVL
1584 (l_gme_batchstep_resources.actual_rsrc_usage
1585 ,0)
1586 + ( ( l_gme_batchstep_resources.plan_rsrc_usage
1587 * p_charge_diff
1588 * p_batchstep_activities_rec.actual_activity_factor)
1589 / (p_batch_step_rec.plan_charges) );
1590 END IF;
1591
1592 IF ( NVL (p_dependency_type, 1) = 1
1593 OR NVL (l_gme_batchstep_resources.actual_rsrc_qty
1594 ,0) = 0) THEN
1595 l_gme_batchstep_resources.actual_rsrc_qty :=
1596 NVL
1597 (l_gme_batchstep_resources.actual_rsrc_qty
1598 ,0)
1599 + ( ( l_gme_batchstep_resources.plan_rsrc_qty
1600 * p_charge_diff
1601 * p_batchstep_activities_rec.actual_activity_factor)
1602 / (p_batch_step_rec.plan_charges) );
1603 END IF;
1604 END IF;
1605 END IF;
1606
1607 IF (l_gme_batchstep_resources.actual_rsrc_usage < 0) THEN
1608 l_gme_batchstep_resources.actual_rsrc_usage := 0;
1609 END IF;
1610
1611 IF (l_gme_batchstep_resources.actual_rsrc_qty < 0) THEN
1612 l_gme_batchstep_resources.actual_rsrc_qty := 0;
1613 END IF;
1614 ELSE /* Fixed resource scale type */
1615 l_gme_batchstep_resources.actual_rsrc_usage :=
1616 l_gme_batchstep_resources.plan_rsrc_usage;
1617 l_gme_batchstep_resources.actual_rsrc_qty :=
1618 l_gme_batchstep_resources.plan_rsrc_qty;
1619 END IF;
1620 END IF;
1621
1622 /* Default the Actual Resource Count to the Planned Resource */
1623 /* Count if the Actual Resource Count is Null. */
1624 IF ( NVL (l_gme_batchstep_resources.actual_rsrc_count, -1) = -1
1625 AND p_backflush_factor IS NOT NULL) THEN
1626 l_gme_batchstep_resources.actual_rsrc_count :=
1627 l_gme_batchstep_resources.plan_rsrc_count;
1628 END IF;
1629
1630 l_gme_batchstep_resources.plan_rsrc_count :=
1631 NVL (l_gme_batchstep_resources.plan_rsrc_count, 1);
1632 l_gme_batchstep_resources.plan_rsrc_usage :=
1633 NVL (l_gme_batchstep_resources.plan_rsrc_usage, 0);
1634 l_gme_batchstep_resources.plan_rsrc_qty :=
1635 NVL (l_gme_batchstep_resources.plan_rsrc_qty, 0);
1636 END IF; /* IF p_batch_hdr_rec.automatic_step_calculation = 1 */
1637
1638 /* We should check for the process qty going below the min capacity based on the profile */
1639 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1640 gme_debug.put_line
1641 ( ' Allow qty to go below capacity profile value:'
1642 || l_allow_qty_below_cap);
1643 END IF;
1644
1645 IF l_allow_qty_below_cap = 0 THEN
1646 IF gme_common_pvt.is_qty_below_capacity
1647 (p_batch_step_resources_rec => l_gme_batchstep_resources) THEN
1648 RAISE qty_below_cap_error;
1649 END IF;
1650 END IF; /* IF l_allow_qty_below_cap = 0 */
1651
1652 /* Only if the update inventory ind is set to 'Y' on the batch header */
1653 /* then only we will have resource transactions */
1654 IF p_batch_hdr_rec.update_inventory_ind = 'Y' THEN
1655 IF (p_batch_step_rec.step_status >
1656 gme_common_pvt.g_step_pending)
1657 AND (NVL (l_gme_batchstep_resources.actual_rsrc_usage, -1) >= 0) THEN
1658 IF p_batch_hdr_rec.automatic_step_calculation = 1 THEN
1659 OPEN cur_sum_override_resource
1660 (l_gme_batchstep_resources.batchstep_resource_id
1661 ,l_doc_type
1662 ,p_batch_hdr_rec.batch_id);
1663
1664 FETCH cur_sum_override_resource
1665 INTO l_override_usage;
1666
1667 CLOSE cur_sum_override_resource;
1668
1669 l_gme_batchstep_resources.actual_rsrc_usage :=
1670 l_gme_batchstep_resources.actual_rsrc_usage
1671 + NVL (l_override_usage, 0);
1672 END IF; /* IF p_batch_hdr_rec.automatic_step_calculation = 1 */
1673 /* if Actual Start Date and Actual End Date are NULL */
1674
1675 IF l_gme_batchstep_resources.actual_start_date IS NULL THEN
1676 l_gme_batchstep_resources.actual_start_date :=
1677 p_batch_step_rec.actual_start_date;
1678 END IF;
1679
1680 IF l_gme_batchstep_resources.actual_cmplt_date IS NULL THEN
1681 l_gme_batchstep_resources.actual_cmplt_date :=
1682 p_batch_step_rec.actual_cmplt_date;
1683 END IF;
1684
1685 adjust_actual_usage
1686 (p_batch_step_resources_rec => l_gme_batchstep_resources
1687 ,x_return_status => l_return_status);
1688
1689 IF l_return_status <> x_return_status THEN
1690 RAISE resource_trans_ins_err;
1691 END IF;
1692 /* we have to delete the actual transactions and create pending resource transactions */
1693 ELSIF ( p_batch_step_rec.step_status =
1694 gme_common_pvt.g_step_pending
1695 OR ( p_batch_hdr_rec.automatic_step_calculation = 1
1696 AND p_batch_step_rec.step_status =
1697 gme_common_pvt.g_step_wip) ) THEN
1698 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1699 gme_debug.put_line (' invoking pending usage');
1700 END IF;
1701
1702 adjust_pending_usage
1703 (p_batch_step_resources_rec => l_gme_batchstep_resources
1704 ,x_return_status => l_return_status);
1705
1706 IF l_return_status <> x_return_status THEN
1707 RAISE resource_trans_ins_err;
1708 END IF;
1709 END IF; /* IF (p_batch_step_rec.step_status > 1) AND */
1710 END IF; /* IF p_batch_header.update_inventory_ind = 'Y' */
1711
1712 l_gme_batchstep_resources.plan_rsrc_qty :=
1713 ROUND (l_gme_batchstep_resources.plan_rsrc_qty, 32);
1714 l_gme_batchstep_resources.actual_rsrc_qty :=
1715 ROUND (l_gme_batchstep_resources.actual_rsrc_qty, 32);
1716 l_gme_batchstep_resources.plan_rsrc_usage :=
1717 ROUND (l_gme_batchstep_resources.plan_rsrc_usage, 32);
1718 l_gme_batchstep_resources.actual_rsrc_usage :=
1719 ROUND (l_gme_batchstep_resources.actual_rsrc_usage, 32);
1720
1721 /* Save the updated batch step resources row to the database */
1722 IF NOT (gme_batch_step_resources_dbl.update_row
1723 (l_gme_batchstep_resources) ) THEN
1724 RAISE step_resource_upd_err;
1725 END IF;
1726 END LOOP; /* FOR i IN 1..l_batchstep_resource_ids.COUNT LOOP */
1727
1728 IF g_debug <= gme_debug.g_log_procedure THEN
1729 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1730 END IF;
1731 EXCEPTION
1732 WHEN activity_resource_fetch_error THEN
1733 x_return_status := fnd_api.g_ret_sts_error;
1734 WHEN resource_trans_ins_err THEN
1735 x_return_status := fnd_api.g_ret_sts_error;
1736 WHEN step_resource_upd_err THEN
1737 x_return_status := fnd_api.g_ret_sts_error;
1738 WHEN error_fetch_rsrc_tran THEN
1739 x_return_status := l_return_status;
1740 WHEN error_txns_update THEN
1741 x_return_status := fnd_api.g_ret_sts_error;
1742 WHEN qty_below_cap_error THEN
1743 x_return_status := fnd_api.g_ret_sts_error;
1744 gme_common_pvt.log_message ('GME_API_RSRC_QTY_BELOW_CAP'
1745 ,'RESOURCES'
1746 ,l_gme_batchstep_resources.resources);
1747 WHEN OTHERS THEN
1748 IF g_debug <= gme_debug.g_log_unexpected THEN
1749 gme_debug.put_line ( 'When others exception in '
1750 || g_pkg_name
1751 || '.'
1752 || l_api_name
1753 || ' Error is '
1754 || SQLERRM);
1755 END IF;
1756
1757 x_return_status := fnd_api.g_ret_sts_unexp_error;
1758 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1759 END update_resources;
1760
1761 /*===========================================================================================
1762 Procedure
1763 Build_Insert_Resource_Txn
1764 Description
1765 This particular procedure is used to build a resource transaction row based on the resource
1766 line row and the usage passed in and insert it.
1767 Parameters
1768 p_batch_hdr_rec Batch Header Row
1769 p_batchstep_resources Batch Step Resource Line
1770 p_usage Usage to be created.
1771 p_completed Build a completed or pending transaction.
1772 x_return_status outcome of the API call
1773 S - Success
1774 E - Error
1775 U - Unexpected error
1776 =============================================================================================*/
1777 PROCEDURE build_insert_resource_txn (
1778 p_batch_hdr_rec IN gme_batch_header%ROWTYPE
1779 ,p_batchstep_resource IN gme_batch_step_resources%ROWTYPE
1780 ,p_usage IN NUMBER
1781 ,p_completed IN NUMBER DEFAULT 1
1782 ,x_return_status OUT NOCOPY VARCHAR2)
1783 IS
1784 /* Local Variables */
1785 l_api_name CONSTANT VARCHAR2 (30) := 'Build_Insert_Resource_Txn';
1786
1787 /* Cursor Definitions */
1788 CURSOR cur_get_poc_trans_id
1789 IS
1790 SELECT gem5_poc_trans_id_s.NEXTVAL
1791 FROM SYS.DUAL;
1792
1793 /* Collections for details */
1794 l_ins_resource_row gme_resource_txns_gtmp%ROWTYPE;
1795 l_usage_hrs gme_batch_step_resources.plan_rsrc_usage%TYPE;
1796 l_return_status VARCHAR2 (1);
1797 /* Exceptions */
1798 resource_trans_ins_err EXCEPTION;
1799 -- p_tran_rec gmi_trans_engine_pub.ictran_rec;
1800 -- l_tran_rec_out gmi_trans_engine_pub.ictran_rec;
1801 BEGIN
1802 IF g_debug <= gme_debug.g_log_procedure THEN
1803 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1804 || l_api_name);
1805 END IF;
1806
1807 /* Initialize the return status to success */
1808 x_return_status := fnd_api.g_ret_sts_success;
1809
1810 /* Generate the surrogate key */
1811 OPEN cur_get_poc_trans_id;
1812
1813 FETCH cur_get_poc_trans_id
1814 INTO l_ins_resource_row.poc_trans_id;
1815
1816 CLOSE cur_get_poc_trans_id;
1817
1818 /* Assign the difference usage for posting a completed entry */
1819 l_ins_resource_row.resource_usage := ROUND (p_usage, 32);
1820 l_ins_resource_row.action_code := 'ADD';
1821 l_ins_resource_row.organization_id := p_batch_hdr_rec.organization_id;
1822
1823 /* The appropriate doc type should be assigned to the transaction */
1824 /* based on the batch type */
1825 IF p_batch_hdr_rec.batch_type = 10 THEN
1826 l_ins_resource_row.doc_type := 'FPO';
1827 ELSE
1828 l_ins_resource_row.doc_type := 'PROD';
1829 END IF;
1830
1831 l_ins_resource_row.doc_id := p_batch_hdr_rec.batch_id;
1832 l_ins_resource_row.line_type := 0;
1833 l_ins_resource_row.line_id := p_batchstep_resource.batchstep_resource_id;
1834 l_ins_resource_row.resources := p_batchstep_resource.resources;
1835 l_ins_resource_row.trans_um := p_batchstep_resource.usage_um;
1836
1837 IF p_completed = 1 THEN
1838 l_ins_resource_row.trans_date :=
1839 NVL (p_batchstep_resource.actual_start_date
1840 ,gme_common_pvt.g_timestamp);
1841 l_ins_resource_row.start_date :=
1842 NVL (p_batchstep_resource.actual_start_date
1843 ,gme_common_pvt.g_timestamp);
1844 gme_create_step_pvt.get_usage_in_hours
1845 (p_usage
1846 ,p_batchstep_resource.usage_um
1847 ,l_usage_hrs
1848 ,l_return_status);
1849
1850 IF l_return_status = 'S' THEN
1851 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1852 gme_debug.put_line ( 'l_return_status'
1853 || l_return_status
1854 || ' usage'
1855 || l_usage_hrs);
1856 END IF;
1857
1858 l_ins_resource_row.end_date :=
1859 (l_ins_resource_row.start_date + (l_usage_hrs / 24) );
1860
1861 IF (l_ins_resource_row.end_date > gme_common_pvt.g_timestamp) THEN
1862 l_ins_resource_row.end_date := gme_common_pvt.g_timestamp;
1863 END IF;
1864 ELSE
1865 l_ins_resource_row.end_date :=
1866 NVL (p_batchstep_resource.actual_cmplt_date
1867 ,l_ins_resource_row.start_date);
1868 -- this is not a error, only a warning so we are setting it back to sucess.
1869 l_return_status := 'S';
1870 END IF;
1871 ELSE
1872 l_ins_resource_row.trans_date :=
1873 NVL (p_batchstep_resource.plan_start_date
1874 ,gme_common_pvt.g_timestamp);
1875 l_ins_resource_row.start_date :=
1876 NVL (p_batchstep_resource.plan_start_date
1877 ,gme_common_pvt.g_timestamp);
1878 l_ins_resource_row.end_date :=
1879 NVL (p_batchstep_resource.plan_cmplt_date
1880 ,gme_common_pvt.g_timestamp);
1881 END IF;
1882
1883 l_ins_resource_row.completed_ind := p_completed;
1884 l_ins_resource_row.posted_ind := 0;
1885 l_ins_resource_row.overrided_protected_ind := 'N';
1886 l_ins_resource_row.delete_mark := 0;
1887
1888 /* p_tran_rec.organization_id := p_batch_hdr_rec.organization_id;
1889 p_tran_rec.whse_code := p_batch_hdr_rec.wip_whse_code;
1890 p_tran_rec.trans_date := l_ins_resource_row.trans_date;
1891 IF NOT gme_api_grp.close_period_check_flexible
1892 (p_tran_rec => p_tran_rec,
1893 x_tran_rec => l_tran_rec_out)
1894 THEN
1895 RAISE FND_API.g_exc_error;
1896 END IF;
1897 l_ins_resource_row.trans_date := l_tran_rec_out.trans_date;
1898 */
1899 IF NOT (gme_resource_txns_gtmp_dbl.insert_row (l_ins_resource_row
1900 ,l_ins_resource_row) ) THEN
1901 RAISE resource_trans_ins_err;
1902 END IF;
1903
1904 IF g_debug <= gme_debug.g_log_procedure THEN
1905 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1906 END IF;
1907 EXCEPTION
1908 WHEN resource_trans_ins_err THEN
1909 x_return_status := fnd_api.g_ret_sts_error;
1910 WHEN fnd_api.g_exc_error THEN
1911 x_return_status := fnd_api.g_ret_sts_error;
1912 WHEN OTHERS THEN
1913 IF g_debug <= gme_debug.g_log_unexpected THEN
1914 gme_debug.put_line ( 'When others exception in '
1915 || g_pkg_name
1916 || '.'
1917 || l_api_name
1918 || ' Error is '
1919 || SQLERRM);
1920 END IF;
1921
1922 x_return_status := fnd_api.g_ret_sts_unexp_error;
1923 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1924 END build_insert_resource_txn;
1925
1926 /*===========================================================================================
1927 Procedure
1928 adjust_pending_usage
1929 Description
1930 This particular procedure is used to adjust the pending usage to the plan resource usage
1931 of the resource
1932 Parameters
1933 p_batch_step_rec_resources Batch Step Resource Line
1934 x_return_status outcome of the API call
1935 S - Success
1936 E - Error
1937 U - Unexpected error
1938 =============================================================================================*/
1939 PROCEDURE adjust_pending_usage (
1940 p_batch_step_resources_rec IN gme_batch_step_resources%ROWTYPE
1941 ,x_return_status OUT NOCOPY VARCHAR2)
1942 IS
1943 CURSOR cur_sum_usage (v_batchstep_resource_id NUMBER)
1944 IS
1945 SELECT NVL (SUM (resource_usage), 0), COUNT (1)
1946 FROM gme_resource_txns_gtmp
1947 WHERE line_id = v_batchstep_resource_id AND completed_ind = 0;
1948
1949 l_api_name CONSTANT VARCHAR2 (30) := 'adjust_pending_usage';
1950 /* Collections for details etc */
1951 l_batch_hdr gme_batch_header%ROWTYPE;
1952 /* Local variables */
1953 l_alloc_usage NUMBER;
1954 l_tot_usage NUMBER;
1955 l_tran_count NUMBER;
1956 l_return_status VARCHAR2 (1);
1957 BEGIN
1958 IF g_debug <= gme_debug.g_log_procedure THEN
1959 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1960 || l_api_name);
1961 END IF;
1962
1963 /* Initialize return status to success */
1964 x_return_status := fnd_api.g_ret_sts_success;
1965
1966 /* Lets get the sum of pending usages to determine if their is a change */
1967 OPEN cur_sum_usage (p_batch_step_resources_rec.batchstep_resource_id);
1968
1969 FETCH cur_sum_usage
1970 INTO l_tot_usage, l_tran_count;
1971
1972 CLOSE cur_sum_usage;
1973
1974 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1975 gme_debug.put_line ( ' tot usage:'
1976 || l_tot_usage
1977 || ' count:'
1978 || l_tran_count);
1979 END IF;
1980
1981 IF (l_tot_usage <> p_batch_step_resources_rec.plan_rsrc_usage)
1982 OR (l_tran_count <> p_batch_step_resources_rec.plan_rsrc_count) THEN
1983 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1984 gme_debug.put_line
1985 ( ' Pending Plan Usage:'
1986 || TO_CHAR (p_batch_step_resources_rec.plan_rsrc_usage)
1987 || ' Plan Count:'
1988 || TO_CHAR (p_batch_step_resources_rec.plan_rsrc_count) );
1989 END IF;
1990
1991 /* Deletes all resource transactions for the current resource */
1992 gme_delete_batch_step_pvt.delete_resource_transactions
1993 (p_batch_step_resources_rec => p_batch_step_resources_rec
1994 ,x_return_status => l_return_status);
1995
1996 IF l_return_status <> x_return_status THEN
1997 RAISE fnd_api.g_exc_error;
1998 END IF;
1999
2000 l_alloc_usage :=
2001 p_batch_step_resources_rec.plan_rsrc_usage
2002 / p_batch_step_resources_rec.plan_rsrc_count;
2003
2004 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2005 gme_debug.put_line (' Alloc Usage:' || TO_CHAR (l_alloc_usage) );
2006 END IF;
2007
2008 l_batch_hdr.batch_id := p_batch_step_resources_rec.batch_id;
2009
2010 IF NOT gme_batch_header_dbl.fetch_row (p_batch_header => l_batch_hdr
2011 ,x_batch_header => l_batch_hdr) THEN
2012 RAISE fnd_api.g_exc_error;
2013 END IF;
2014
2015 FOR i IN 1 .. p_batch_step_resources_rec.plan_rsrc_count LOOP
2016 build_insert_resource_txn
2017 (p_batch_hdr_rec => l_batch_hdr
2018 ,p_batchstep_resource => p_batch_step_resources_rec
2019 ,p_usage => l_alloc_usage
2020 ,p_completed => 0
2021 ,x_return_status => l_return_status);
2022
2023 IF l_return_status <> x_return_status THEN
2024 RAISE fnd_api.g_exc_error;
2025 END IF;
2026 END LOOP; /* FOR i IN 1..l_gme_batchstep_resources.plan_rsrc_count */
2027 END IF;
2028 /* IF (l_tot_usage <> p_batch_step_resources_rec.plan_rsrc_usage) */
2029 EXCEPTION
2030 WHEN fnd_api.g_exc_error THEN
2031 x_return_status := fnd_api.g_ret_sts_error;
2032 WHEN OTHERS THEN
2033 IF g_debug <= gme_debug.g_log_unexpected THEN
2034 gme_debug.put_line ( 'When others exception in '
2035 || g_pkg_name
2036 || '.'
2037 || l_api_name
2038 || ' Error is '
2039 || SQLERRM);
2040 END IF;
2041
2042 x_return_status := fnd_api.g_ret_sts_unexp_error;
2043 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2044 END adjust_pending_usage;
2045
2046 /*===========================================================================================
2047 Procedure
2048 adjust_actual_usage
2049 Description
2050 This particular procedure is used to adjust the actual usage to the actual resource usage
2051 of the resource
2052
2053 The following steps identify the code in this procedure:
2054
2055 Step 1 : If their are any pending transactions then we have to either delete them or adjust
2056 them based on the step status.
2057
2058 Step 2 : Check for the total completed usage transactions if it equals to the resource
2059 line actual usage then we need not do any adjustment we can return.
2060
2061 Step 3 : If we are here then their is some adjustment we have to make. If the total usage is
2062 less than the actual resource usage then we perform step 4 else step 5.
2063
2064 Step 4 : If the total usage is less than the actual usage then, find the difference between
2065 actual resource usage and the total usage.
2066 Divide the difference amount with the actual resource count and post one transactions for
2067 the divided amount for each actual resource count.
2068
2069 Step 5 : If the actual usage is less than the total usage then, delete all the completed transactions
2070 which are not override protected and then subtract total override protected usage from the
2071 actual resource usage and then divide the difference amount with the actual resource count
2072 and post one transactions for the divided amount for each actual resource count.
2073 Parameters
2074 p_batch_step_resources_rec Batch Step Resource Line
2075 x_return_status outcome of the API call
2076 S - Success
2077 E - Error
2078 U - Unexpected error
2079 =============================================================================================*/
2080 PROCEDURE adjust_actual_usage (
2081 p_batch_step_resources_rec IN gme_batch_step_resources%ROWTYPE
2082 ,x_return_status OUT NOCOPY VARCHAR2)
2083 IS
2084 l_api_name CONSTANT VARCHAR2 (30) := 'adjust_actual_usage';
2085
2086 CURSOR cur_sum_comp_usage (v_batchstep_resource_id NUMBER)
2087 IS
2088 SELECT NVL (SUM (resource_usage), 0)
2089 FROM gme_resource_txns_gtmp
2090 WHERE line_id = v_batchstep_resource_id
2091 AND completed_ind = 1
2092 AND action_code <> 'DEL';
2093
2094 CURSOR cur_count_comp_usage (v_batchstep_resource_id NUMBER)
2095 IS
2096 SELECT COUNT (1)
2097 FROM gme_resource_txns_gtmp
2098 WHERE line_id = v_batchstep_resource_id
2099 AND completed_ind = 1
2100 AND NVL (overrided_protected_ind, 'N') <> 'Y'
2101 AND action_code <> 'DEL';
2102
2103 /* Collections for details etc */
2104 l_resource_txns gme_resource_txns_gtmp%ROWTYPE;
2105 l_resource_tab gme_common_pvt.resource_transactions_tab;
2106 l_batch_hdr gme_batch_header%ROWTYPE;
2107 /* Local variables */
2108 l_sum_comp_usage NUMBER;
2109 l_cnt_comp_usage NUMBER;
2110 l_override_usage NUMBER DEFAULT 0;
2111 l_actual_usage NUMBER;
2112 l_return_status VARCHAR2 (1);
2113 BEGIN
2114 IF g_debug <= gme_debug.g_log_procedure THEN
2115 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
2116 || l_api_name);
2117 END IF;
2118
2119 /* Initialize return status to success */
2120 x_return_status := fnd_api.g_ret_sts_success;
2121
2122 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2123 gme_debug.put_line ( ' Adjusting actual usage :'
2124 || p_batch_step_resources_rec.actual_rsrc_usage);
2125 END IF;
2126
2127 /* Lets fetch all the active resource transactions for the current resource */
2128 l_resource_txns.poc_trans_id := NULL;
2129 l_resource_txns.doc_id := NULL;
2130 l_resource_txns.line_id :=
2131 p_batch_step_resources_rec.batchstep_resource_id;
2132 gme_resource_engine_pvt.fetch_active_resources
2133 (p_resource_rec => l_resource_txns
2134 ,x_resource_tbl => l_resource_tab
2135 ,x_return_status => l_return_status);
2136
2137 IF l_return_status <> x_return_status THEN
2138 RAISE fnd_api.g_exc_error;
2139 END IF;
2140
2141 /* Step 1 : */
2142 gme_update_step_qty_pvt.reduce_pending_usage
2143 (p_batch_step_resources_rec => p_batch_step_resources_rec
2144 ,x_return_status => l_return_status);
2145
2146 IF l_return_status <> x_return_status THEN
2147 RAISE fnd_api.g_exc_error;
2148 END IF;
2149
2150 /* Calculate the sum of the Completed Transactions */
2151 /* for this Operation detail line. */
2152 OPEN cur_sum_comp_usage
2153 (p_batch_step_resources_rec.batchstep_resource_id);
2154
2155 FETCH cur_sum_comp_usage
2156 INTO l_sum_comp_usage;
2157
2158 IF cur_sum_comp_usage%NOTFOUND THEN
2159 l_sum_comp_usage := 0;
2160 END IF;
2161
2162 CLOSE cur_sum_comp_usage;
2163
2164 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2165 gme_debug.put_line
2166 ( ' Completed Usage:'
2167 || TO_CHAR (l_sum_comp_usage)
2168 || ' Actual Usage:'
2169 || TO_CHAR (p_batch_step_resources_rec.actual_rsrc_usage)
2170 || ' Resource:'
2171 || p_batch_step_resources_rec.resources);
2172 END IF;
2173
2174 OPEN cur_count_comp_usage
2175 (p_batch_step_resources_rec.batchstep_resource_id);
2176
2177 FETCH cur_count_comp_usage
2178 INTO l_cnt_comp_usage;
2179
2180 CLOSE cur_count_comp_usage;
2181
2182 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2183 gme_debug.put_line ( ' Completed Transaction Count:'
2184 || TO_CHAR (l_cnt_comp_usage) );
2185 END IF;
2186
2187 /* Step 2 : */
2188 IF l_sum_comp_usage <> p_batch_step_resources_rec.actual_rsrc_usage THEN
2189 l_batch_hdr.batch_id := p_batch_step_resources_rec.batch_id;
2190
2191 IF NOT gme_batch_header_dbl.fetch_row
2192 (p_batch_header => l_batch_hdr
2193 ,x_batch_header => l_batch_hdr) THEN
2194 RAISE fnd_api.g_exc_error;
2195 END IF;
2196
2197 /* Step 3 : */
2198 IF (l_sum_comp_usage < p_batch_step_resources_rec.actual_rsrc_usage) THEN
2199 l_actual_usage :=
2200 p_batch_step_resources_rec.actual_rsrc_usage
2201 - l_sum_comp_usage;
2202 l_actual_usage :=
2203 l_actual_usage / p_batch_step_resources_rec.actual_rsrc_count;
2204
2205 /* Step 4 : */
2206 FOR i IN 1 .. p_batch_step_resources_rec.actual_rsrc_count LOOP
2207 build_insert_resource_txn
2208 (p_batch_hdr_rec => l_batch_hdr
2209 ,p_batchstep_resource => p_batch_step_resources_rec
2210 ,p_usage => l_actual_usage
2211 ,p_completed => 1
2212 ,x_return_status => l_return_status);
2213
2214 IF l_return_status <> x_return_status THEN
2215 RAISE fnd_api.g_exc_error;
2216 END IF;
2217 END LOOP;
2218 /* FOR i IN 1..p_batch_step_resources_rec.actual_rsrc_count */
2219 ELSE
2220 /* Step 5 : */
2221
2222 /* Delete all the existing completed transactions */
2223 FOR i IN 1 .. l_resource_tab.COUNT LOOP
2224 IF (l_resource_tab (i).overrided_protected_ind <> 'Y')
2225 AND (l_resource_tab (i).completed_ind = 1) THEN
2226 l_resource_txns := l_resource_tab (i);
2227 gme_resource_engine_pvt.delete_resource_trans
2228 (p_tran_rec => l_resource_txns
2229 ,x_return_status => l_return_status);
2230
2231 IF l_return_status <> x_return_status THEN
2232 RAISE fnd_api.g_exc_error;
2233 END IF;
2234 ELSIF (l_resource_tab (i).overrided_protected_ind = 'Y') THEN
2235 l_override_usage :=
2236 l_override_usage + l_resource_tab (i).resource_usage;
2237 END IF;
2238 /* IF l_resource_tab(i).override_protected_ind <> 'Y' */
2239 END LOOP; /* FOR i IN 1..l_resource_tab.COUNT */
2240
2241 l_actual_usage :=
2242 p_batch_step_resources_rec.actual_rsrc_usage - l_override_usage;
2243 l_actual_usage :=
2244 l_actual_usage / p_batch_step_resources_rec.actual_rsrc_count;
2245
2246 FOR i IN 1 .. p_batch_step_resources_rec.actual_rsrc_count LOOP
2247 build_insert_resource_txn
2248 (p_batch_hdr_rec => l_batch_hdr
2249 ,p_batchstep_resource => p_batch_step_resources_rec
2250 ,p_usage => l_actual_usage
2251 ,p_completed => 1
2252 ,x_return_status => l_return_status);
2253
2254 IF l_return_status <> x_return_status THEN
2255 RAISE fnd_api.g_exc_error;
2256 END IF;
2257 END LOOP;
2258 /* FOR i IN 1..p_batch_step_resources_rec.actual_rsrc_count */
2259 END IF;
2260 /* IF l_sum_comp_usage < p_batch_step_resources_rec.actual_rsrc_usage */
2261 END IF;
2262 /* IF l_sum_comp_usage <> p_batch_step_resources_rec.actual_rsrc_usage */
2263
2264 IF g_debug <= gme_debug.g_log_procedure THEN
2265 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2266 END IF;
2267 EXCEPTION
2268 WHEN fnd_api.g_exc_error THEN
2269 x_return_status := fnd_api.g_ret_sts_error;
2270 WHEN OTHERS THEN
2271 IF g_debug <= gme_debug.g_log_unexpected THEN
2272 gme_debug.put_line ( 'When others exception in '
2273 || g_pkg_name
2274 || '.'
2275 || l_api_name
2276 || ' Error is '
2277 || SQLERRM);
2278 END IF;
2279
2280 x_return_status := fnd_api.g_ret_sts_unexp_error;
2281 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2282 END adjust_actual_usage;
2283
2284 /*===========================================================================================
2285 Procedure
2286 reduce_pending_usage
2287 Description
2288 This particular procedure is used to reduce the pending usage based on the actual allocations
2289 made.
2290 Parameters
2291 p_batch_step_resources_rec Batch Step Resource Line
2292 x_return_status outcome of the API call
2293 S - Success
2294 E - Error
2295 U - Unexpected error
2296 History
2297 Pawan Kumar for bug 2393432 Modified procedure reduce_pending_usage
2298 Namit Singhi. Bug#5609683. Restructuring of code when calculating pending usage.
2299 =============================================================================================*/
2300 PROCEDURE reduce_pending_usage (
2301 p_batch_step_resources_rec IN gme_batch_step_resources%ROWTYPE
2302 ,x_return_status OUT NOCOPY VARCHAR2)
2303 IS
2304 l_api_name CONSTANT VARCHAR2 (30) := 'reduce_pending_usage';
2305
2306 CURSOR cur_get_step_status (v_batchstep_id NUMBER)
2307 IS
2308 SELECT step_status
2309 FROM gme_batch_steps
2310 WHERE batchstep_id = v_batchstep_id;
2311
2312 --nsinghi bug#5609683 Modified the cursor to also query resource plan start date and changed cursor name
2313 CURSOR cur_get_rsrc_dtl (v_batchstep_resource_id NUMBER)
2314 IS
2315 SELECT actual_rsrc_usage, plan_start_date
2316 FROM gme_batch_step_resources
2317 WHERE batchstep_resource_id = v_batchstep_resource_id;
2318
2319 CURSOR cur_pend_count (v_batchstep_resource_id NUMBER)
2320 IS
2321 SELECT COUNT (1)
2322 FROM gme_resource_txns_gtmp
2323 WHERE line_id = v_batchstep_resource_id AND completed_ind = 0;
2324
2325 /* Collections for details etc */
2326 l_resource_txns gme_resource_txns_gtmp%ROWTYPE;
2327 l_resource_tab gme_common_pvt.resource_transactions_tab;
2328 /* Local variables */
2329 l_alloc_usage NUMBER;
2330 l_usage NUMBER;
2331 l_tot_usage NUMBER;
2332 l_tran_count NUMBER;
2333 l_return_status VARCHAR2 (1);
2334 l_step_status NUMBER (5);
2335 l_prev_usage NUMBER;
2336 l_rsrc_plan_strt_dt DATE;
2337 l_remaining_tran_count NUMBER;
2338 l_extra_usage_left NUMBER := 0;
2339 BEGIN
2340 IF g_debug <= gme_debug.g_log_procedure THEN
2341 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
2342 || l_api_name);
2343 END IF;
2344
2345 /* Initialize return status to success */
2346 x_return_status := fnd_api.g_ret_sts_success;
2347
2348 /* Let us check the status of the step */
2349 OPEN cur_get_step_status (p_batch_step_resources_rec.batchstep_id);
2350
2351 FETCH cur_get_step_status
2352 INTO l_step_status;
2353
2354 CLOSE cur_get_step_status;
2355
2356 /* Lets fetch all the active resource transactions for the current resource */
2357 l_resource_txns.poc_trans_id := NULL;
2358 l_resource_txns.doc_id := NULL;
2359 l_resource_txns.line_id :=
2360 p_batch_step_resources_rec.batchstep_resource_id;
2361
2362 --nsinghi bug#5609683
2363 --calling the procedure with additional parameter p_calling_mode.
2364 --For reducing pending resource usage, we need to fetch the resources in ascending order of resource usage.
2365 gme_resource_engine_pvt.fetch_active_resources
2366 (p_resource_rec => l_resource_txns
2367 ,p_calling_mode => 'REDUCE_USAGE'
2368 ,x_resource_tbl => l_resource_tab
2369 ,x_return_status => l_return_status);
2370
2371 IF l_return_status <> x_return_status THEN
2372 RAISE fnd_api.g_exc_error;
2373 END IF;
2374
2375 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2376 gme_debug.put_line(g_pkg_name||'.'||l_api_name||': Line ID '||l_resource_txns.line_id);
2377 gme_debug.put_line(g_pkg_name||'.'||l_api_name||': Resource Txns Count '||l_resource_tab.COUNT);
2378 gme_debug.put_line(g_pkg_name||'.'||l_api_name||': Step Status '||l_step_status);
2379 END IF;
2380
2381
2382 /* If the step is in WIP then we need to adjust the pending transactions */
2383 /* so lets calculate the usage to be adjusted */
2384 IF l_step_status = gme_common_pvt.g_step_wip THEN
2385 /* Lets get the previous actual usage */
2386
2387 OPEN cur_get_rsrc_dtl
2388 (p_batch_step_resources_rec.batchstep_resource_id);
2389
2390 FETCH cur_get_rsrc_dtl
2391 INTO l_prev_usage, l_rsrc_plan_strt_dt;
2392 CLOSE cur_get_rsrc_dtl;
2393
2394 /* Lets get the sum of pending usages and the count */
2395 OPEN cur_pend_count
2396 (p_batch_step_resources_rec.batchstep_resource_id);
2397
2398 FETCH cur_pend_count
2399 INTO l_tran_count;
2400
2401 CLOSE cur_pend_count;
2402
2403 IF l_prev_usage > p_batch_step_resources_rec.plan_rsrc_usage THEN
2404 l_alloc_usage :=
2405 NVL (p_batch_step_resources_rec.actual_rsrc_usage, 0)
2406 - p_batch_step_resources_rec.plan_rsrc_usage;
2407 ELSE
2408 l_alloc_usage :=
2409 NVL (p_batch_step_resources_rec.actual_rsrc_usage, 0)
2410 - NVL (l_prev_usage, 0);
2411 END IF;
2412
2413 IF l_tran_count > 0 THEN
2414 l_alloc_usage := l_alloc_usage / l_tran_count;
2415 END IF;
2416 END IF; /* IF l_step_status = 2 */
2417
2418 l_remaining_tran_count := l_tran_count;
2419 FOR i IN 1 .. l_resource_tab.COUNT LOOP
2420 IF l_resource_tab (i).completed_ind = 0 THEN
2421 l_resource_txns := l_resource_tab (i);
2422
2423 IF l_step_status = gme_common_pvt.g_step_wip THEN
2424 l_remaining_tran_count := l_remaining_tran_count - 1;
2425 l_usage := l_resource_txns.resource_usage - l_alloc_usage;
2426
2427 -- nsinghi bug#5609683
2428 /* Restructured the usage and strt date calculation based on following code Logic --
2429
2430 If there are multiple pending resource transactions with unequal quantities,
2431 when creating completed transactions with +ve qty, fetch the transactions in ascending
2432 order of resource usage. Deduct the usage from first transaction, if usage < 0, make the
2433 usage 0 and divide the remaining usage equally among the other remaining pending transactions.
2434
2435 If deleting an existing completed transaction (or reducing qty of existing completed trxn),
2436 increase the pending resource usage. If the start date calculated for pending rsrc txn based on
2437 new usage < rsrc planned strt dt, determine the time difference between the dates and add it
2438 equally among the other pending resource txns. Txn completion date will never be greater than
2439 rsrc planned completion date as we minus the rsrc usage from txn completion date and hence
2440 completion date remains unchanged.
2441 */
2442 IF l_usage < 0 THEN
2443 l_extra_usage_left := l_usage;
2444 l_resource_txns.resource_usage := 0;
2445 l_resource_txns.start_date := l_resource_txns.end_date;
2446 IF l_remaining_tran_count > 0 THEN
2447 --l_extra_usage_left would be negative so it should be subtracted from the alloc_usage
2448 l_alloc_usage := l_alloc_usage - (l_extra_usage_left/l_remaining_tran_count);
2449 END IF;
2450 ELSIF l_usage = 0 THEN
2451 l_resource_txns.resource_usage := l_usage;
2452 l_resource_txns.start_date := l_resource_txns.end_date
2453 - (l_resource_txns.resource_usage / 24);
2454 ELSIF l_usage > 0 THEN
2455 l_resource_txns.resource_usage := l_usage;
2456 l_resource_txns.start_date := l_resource_txns.end_date -
2457 (l_resource_txns.resource_usage / 24);
2458 IF l_resource_txns.start_date < l_rsrc_plan_strt_dt THEN
2459 l_extra_usage_left := ROUND(((l_rsrc_plan_strt_dt - l_resource_txns.start_date)*24), 5);
2460 l_resource_txns.start_date := l_rsrc_plan_strt_dt;
2461 l_resource_txns.resource_usage := l_usage - l_extra_usage_left;
2462 IF l_remaining_tran_count > 0 THEN
2463 l_alloc_usage := l_alloc_usage - (l_extra_usage_left/l_remaining_tran_count);
2464 END IF;
2465 END IF;
2466 END IF;
2467
2468 -- l_resource_txns.start_date :=
2469 -- l_resource_txns.end_date
2470 -- - (l_resource_txns.resource_usage / 24);
2471
2472 gme_resource_engine_pvt.update_resource_trans
2473 (p_tran_rec => l_resource_txns
2474 ,x_return_status => l_return_status);
2475
2476 IF l_return_status <> x_return_status THEN
2477 RAISE fnd_api.g_exc_error;
2478 END IF;
2479 ELSIF l_step_status > gme_common_pvt.g_step_wip THEN
2480 /* If the step is certified then their shouldn't be any further pending transactions */
2481 gme_resource_engine_pvt.delete_resource_trans
2482 (p_tran_rec => l_resource_txns
2483 ,x_return_status => l_return_status);
2484
2485 IF l_return_status <> x_return_status THEN
2486 RAISE fnd_api.g_exc_error;
2487 END IF;
2488 END IF; /* IF l_step_status = 2 */
2489 END IF; /* IF l_resource_tab(i).completed_ind = 0 */
2490 END LOOP; /* FOR i IN 1..l_resource_tab.COUNT */
2491
2492 IF g_debug <= gme_debug.g_log_procedure THEN
2493 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2494 END IF;
2495 EXCEPTION
2496 WHEN fnd_api.g_exc_error THEN
2497 x_return_status := l_return_status;
2498 WHEN OTHERS THEN
2499 IF g_debug <= gme_debug.g_log_unexpected THEN
2500 gme_debug.put_line ( 'When others exception in '
2501 || g_pkg_name
2502 || '.'
2503 || l_api_name
2504 || ' Error is '
2505 || SQLERRM);
2506 END IF;
2507
2508 x_return_status := fnd_api.g_ret_sts_unexp_error;
2509 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2510 END reduce_pending_usage;
2511
2512
2513 /*===========================================================================================
2514 Procedure
2515 recalculate_charges
2516 Description
2517 This particular procedure is used to recalculate charges when step qty is changed. And also used
2518 to calculate if there is change in capacity by resource addition/deletion/updation.
2519 Parameters
2520 p_batchstep_rec Batch Step Line
2521 x_batchstep_rec
2522 p_cal_type 'R' means change is due to Resource Add/Update/Delete
2523 'P' means change is due to operation
2524 x_return_status outcome of the API call
2525 S - Success
2526 E - Error
2527 U - Unexpected error
2528 HISTORY
2529 SivakumarG Bug#5231180
2530 Procedure Created
2531 =============================================================================================*/
2532 PROCEDURE recalculate_charges( p_batchstep_rec IN gme_batch_steps%ROWTYPE
2533 ,p_cal_type IN VARCHAR2
2534 ,x_batchstep_rec OUT NOCOPY gme_batch_steps%ROWTYPE
2535 ,x_return_status OUT NOCOPY VARCHAR2 )
2536 IS
2537 l_api_name VARCHAR2(30) := 'RECALCULATE_CHARGES';
2538
2539 CURSOR c_get_step_qty(v_step_id NUMBER) IS
2540 SELECT batchstep_id, step_status, plan_step_qty, actual_step_qty,
2541 max_step_capacity, max_step_capacity_um, step_qty_um
2542 FROM gme_batch_steps
2543 WHERE batchstep_id = v_step_id;
2544
2545 CURSOR c_get_step_resources(v_step_id NUMBER) IS
2546 SELECT resources
2547 FROM gme_batch_step_resources
2548 WHERE batchstep_id = v_step_id;
2549
2550 l_step_rec c_get_step_qty%ROWTYPE;
2551 l_resource gme_batch_step_resources.resources%TYPE;
2552 l_batch_header_rec gme_batch_header%ROWTYPE;
2553 l_in_step_qty NUMBER;
2554 l_step_qty NUMBER;
2555 l_charge NUMBER;
2556 l_mass_qty NUMBER;
2557 l_vol_qty NUMBER;
2558 i NUMBER;
2559 l_max_capacity NUMBER;
2560 l_return_status VARCHAR2(1);
2561 l_calc_step_qty BOOLEAN;
2562 l_uom VARCHAR2(3);
2563 l_gmd_resources gmd_recipe_fetch_pub.oprn_resc_tbl;
2564 l_step_resources gme_common_pvt.resources_tab;
2565
2566 error_batchstep_id EXCEPTION;
2567 error_in_calc_capacity EXCEPTION;
2568 error_calc_charge EXCEPTION;
2569 error_in_update_step EXCEPTION;
2570 invalid_value EXCEPTION;
2571 error_in_clear_charges EXCEPTION;
2572 BEGIN
2573 IF g_debug <= gme_debug.g_log_procedure THEN
2574 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'|| l_api_name);
2575 END IF;
2576
2577 x_return_status := fnd_api.g_ret_sts_success;
2578 x_batchstep_rec := p_batchstep_rec;
2579
2580 IF p_batchstep_rec.batchstep_id IS NULL THEN
2581 /* give error over here */
2582 RAISE error_batchstep_id;
2583 END IF;
2584
2585 /* validate the calculation type */
2586 IF p_cal_type NOT IN ('R','P') THEN
2587 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2588 gme_debug.put_line('Invalid value for p_cal_type');
2589 END IF;
2590 RAISE invalid_value;
2591 END IF;
2592
2593 OPEN c_get_step_qty(p_batchstep_rec.batchstep_id);
2594 FETCH c_get_step_qty INTO l_step_rec;
2595 CLOSE c_get_step_qty;
2596
2597 IF l_step_rec.step_status > gme_common_pvt.g_step_pending THEN
2598 l_step_qty := l_step_rec.actual_step_qty;
2599 l_in_step_qty := x_batchstep_rec.actual_step_qty;
2600 ELSE
2601 l_step_qty := l_step_rec.plan_step_qty;
2602 l_in_step_qty := x_batchstep_rec.plan_step_qty;
2603 END IF;
2604
2605 l_max_capacity := l_step_rec.max_step_capacity;
2606
2607 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2608 gme_debug.put_line('DB Step Qty: '||l_step_qty);
2609 gme_debug.put_line('Step Qty: '||l_in_step_qty);
2610 gme_debug.put_line('Max Capacity of the Step: '||l_max_capacity);
2611 END IF;
2612
2613 IF p_cal_type = 'P' THEN
2614 /* if the cal type P that means step update is triggering recalculate charges*/
2615 IF NVL(l_step_qty,0) <> NVL(l_in_step_qty,0) THEN
2616 l_calc_step_qty := TRUE;
2617 ELSE
2618 l_calc_step_qty := FALSE;
2619 END IF;
2620 ELSIF p_cal_type = 'R' THEN
2621 --NVL(l_max_capacity,0) <> NVL(p_batchstep_rec.max_step_capacity,0) THEN
2622 l_calc_step_qty := TRUE;
2623 END IF;
2624
2625 IF l_calc_step_qty THEN
2626
2627 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2628 gme_debug.put_line('Calculating the Charges');
2629 END IF;
2630
2631 /*if we come here then there is change in the step qty and we have to recalculate charges
2632 max step capacity uom and all */
2633 i := 1;
2634 OPEN c_get_step_resources(p_batchstep_rec.batchstep_id);
2635 LOOP
2636 FETCH c_get_step_resources INTO l_gmd_resources(i).resources;
2637 EXIT WHEN c_get_step_resources%NOTFOUND;
2638 i := i+1;
2639 END LOOP;
2640 CLOSE c_get_step_resources;
2641
2642 gme_insert_step_pvt.calc_max_capacity (
2643 p_recipe_rout_resc => l_gmd_resources
2644 ,p_step_qty_uom => l_step_rec.step_qty_um
2645 ,p_capacity_uom => l_uom
2646 ,p_max_capacity => x_batchstep_rec.max_step_capacity
2647 ,x_resource => l_resource
2648 ,x_return_status => l_return_status);
2649
2650 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2651 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2652 gme_debug.put_line('Error in calculating the max capacity');
2653 END IF;
2654 RAISE error_in_calc_capacity;
2655 END IF;
2656
2657 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2658 gme_debug.put_line('Calculated Max Step Qty: '||x_batchstep_rec.max_step_capacity);
2659 gme_debug.put_line('Capacity Resource : '||l_resource);
2660 END IF;
2661
2662 /*clear the charges */
2663 /*DELETE FROM gme_batch_step_charges
2664 WHERE batchstep_id = l_step_rec.batchstep_id; */
2665 gme_batch_step_chg_pvt.clear_charges(
2666 p_batch_id => x_batchstep_rec.batch_id
2667 ,p_batchstep_id => x_batchstep_rec.batchstep_id
2668 ,x_return_status => l_return_status);
2669
2670 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2671 RAISE error_in_clear_charges;
2672 END IF;
2673
2674 /*calculate mass qty and vol qty by calling routine */
2675 gme_update_step_qty_pvt.calculate_mass_vol_qty(
2676 p_batch_step_rec => x_batchstep_rec
2677 );
2678
2679 IF l_step_rec.step_status > gme_common_pvt.g_step_pending THEN
2680 l_mass_qty := x_batchstep_rec.actual_mass_qty;
2681 l_vol_qty := x_batchstep_rec.actual_volume_qty;
2682 ELSE
2683 l_mass_qty := x_batchstep_rec.plan_mass_qty;
2684 l_vol_qty := x_batchstep_rec.plan_volume_qty;
2685 END IF;
2686
2687 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2688 gme_debug.put_line('Mass Qty: '||l_mass_qty||' Volume Qty: '||l_vol_qty);
2689 END IF;
2690
2691 /* we have the max capacity and resource here */
2692 gme_update_step_qty_pvt.calc_charge (
2693 p_step_id => l_step_rec.batchstep_id
2694 ,p_resources => l_resource
2695 ,p_mass_qty => l_mass_qty
2696 ,p_vol_qty => l_vol_qty
2697 ,p_step_qty => l_in_step_qty
2698 ,p_max_capacity => x_batchstep_rec.max_step_capacity
2699 ,x_charge => l_charge
2700 ,x_return_status => l_return_status);
2701
2702 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2703 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2704 gme_debug.put_line('error in calc_charge');
2705 END IF;
2706 RAISE error_calc_charge;
2707 END IF;
2708
2709 IF l_step_rec.step_status > gme_common_pvt.g_step_pending THEN
2710 x_batchstep_rec.actual_charges := l_charge;
2711 ELSE
2712 x_batchstep_rec.plan_charges := l_charge;
2713 END IF;
2714 END IF; /*l_calc_step_qty */
2715 /* update the batch step record */
2716 IF NOT gme_batch_steps_dbl.update_row(p_batch_step => x_batchstep_rec) THEN
2717 RAISE error_in_update_step;
2718 END IF;
2719
2720 IF g_debug <= gme_debug.g_log_procedure THEN
2721 gme_debug.put_line ('Existing api ' || g_pkg_name || '.'|| l_api_name);
2722 END IF;
2723 EXCEPTION
2724 WHEN error_batchstep_id OR error_in_calc_capacity OR error_in_clear_charges THEN
2725 x_return_status := fnd_api.g_ret_sts_error;
2726 WHEN OTHERS THEN
2727 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2728 gme_debug.put_line(g_pkg_name || '.'|| l_api_name --> when others '
2729 || SQLERRM);
2730 END IF;
2731 x_return_status := fnd_api.g_ret_sts_unexp_error;
2732 END recalculate_charges;
2733
2734 END gme_update_step_qty_pvt;
[Home] [Help]