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