[Home] [Help]
PACKAGE BODY: APPS.GME_TERMINATE_BATCH_PVT
Source
1 PACKAGE BODY gme_terminate_batch_pvt AS
2 /* $Header: GMEVTRBB.pls 120.5.12010000.2 2010/03/30 19:52:53 gmurator ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_TERMINATE_BATCH_PVT';
5
6 /*
7 REM *********************************************************************
8 REM *
9 REM * FILE: GMEVTRBB.pls
10 REM * PURPOSE: Package Body for the GME batch terminate api
11 REM * AUTHOR: Pawan Kumar
12 REM * DATE: 2 May 2005
13 REM * HISTORY:
14 REM * ========
15 REM *
16 REM * G. Muratore 30-Mar-2010 Bug 9478698
17 REM * Make call to GMO conditionally based on enhanced_pi_ind.
18 REM * PROCEDURE terminate_batch.
19 REM **********************************************************************
20 */
21
22 /*================================================================================
23 Procedure
24 Terminate_Batch
25 Description
26 This procedure terminates the batch.
27 Parameters
28 p_batch_header The batch header row to identify the batch
29 x_batch_header_rec The batch header row to identify the batch
30 x_return_status outcome of the API call
31 S - Success
32 E - Error
33 U - Unexpected error
34 History:
35 30-Mar-2010 G. Muratore Bug 9478698
36 Make call to GMO conditionally based on enhanced_pi_ind.
37 ================================================================================*/
38 PROCEDURE terminate_batch (
39 p_batch_header_rec IN gme_batch_header%ROWTYPE
40 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
41 ,x_return_status OUT NOCOPY VARCHAR2)
42 IS
43 /* Cursors */
44 CURSOR cur_get_wip_steps (v_batch_id NUMBER)
45 IS
46 SELECT *
47 FROM gme_batch_steps
48 WHERE batch_id = v_batch_id
49 AND step_status = 2 -- terminate only WIP steps
50 ORDER BY batchstep_id;
51
52 CURSOR cur_get_pending_steps (v_batch_id NUMBER)
53 IS
54 SELECT *
55 FROM gme_batch_steps
56 WHERE batch_id = v_batch_id
57 AND step_status = 1 -- cancel pending steps
58 ORDER BY batchstep_id;
59
60 /* Variable Declarations */
61 l_material_details gme_material_details%ROWTYPE;
62 l_material_details_tab gme_common_pvt.material_details_tab;
63 l_batch_header_rec gme_batch_header%ROWTYPE;
64 l_in_batch_header_rec gme_batch_header%ROWTYPE;
65 l_batch_step_rec gme_batch_steps%ROWTYPE;
66 l_in_batch_step_rec gme_batch_steps%ROWTYPE;
67 l_batch_step_ids gme_common_pvt.number_tab;
68 l_rsrc_trans_count NUMBER;
69 l_batch_step_tab gme_common_pvt.steps_tab;
70 m_batch_step_tab gme_common_pvt.steps_tab;
71 l_message_count NUMBER; -- 4944024
72 l_message_list VARCHAR2 (2000); -- 4944024
73 l_api_name CONSTANT VARCHAR2 (30) := 'Terminate Batch';
74 l_lock_status VARCHAR2(1);
75 l_locked_by_status VARCHAR2(1);
76 l_lock_allowed VARCHAR2(1);
77 /* Exceptions */
78 batch_header_fetch_error EXCEPTION;
79 invalid_batch_status EXCEPTION;
80 dep_material_details_fetch_err EXCEPTION;
81 error_load_trans EXCEPTION;
82 purge_exception_err EXCEPTION;
83 batch_header_upd_err EXCEPTION;
84 phantom_batch_terminate_error EXCEPTION;
85 phantom_batch_cancel_error EXCEPTION;
86 batch_hist_insert_err EXCEPTION;
87 delete_inv_txns_err EXCEPTION;
88 batch_step_terminate_error EXCEPTION;
89 batch_step_cancel_error EXCEPTION;
90 prod_supply_resv_err EXCEPTION;
91 gmo_lock_error EXCEPTION;
92
93
94 l_date DATE;
95 BEGIN
96 IF (g_debug <= gme_debug.g_log_procedure) THEN
97 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
98 || 'Entering');
99 END IF;
100
101 /* Set the success staus to success inititally*/
102 x_return_status := fnd_api.g_ret_sts_success;
103 /* Initialize output batch header */
104 x_batch_header_rec := p_batch_header_rec;
105 -- Now Examine the ingredient material rows :
106 l_material_details.batch_id := x_batch_header_rec.batch_id;
107
108 IF (g_debug <= gme_debug.g_log_procedure) THEN
109 gme_debug.put_line ( g_pkg_name
110 || '.'
111 || l_api_name
112 || ':'
113 || 'Call fetch_tab');
114 END IF;
115
116 IF NOT (gme_material_details_dbl.fetch_tab
117 (p_material_detail => l_material_details
118 ,x_material_detail => l_material_details_tab) ) THEN
119 RAISE dep_material_details_fetch_err;
120 END IF;
121
122 /* Load all the transactions and resources to the temporary table */
123 /* for the current batch if the update inventory ind is set for the batch */
124 IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
125 gme_trans_engine_util.load_rsrc_trans (x_batch_header_rec
126 ,l_rsrc_trans_count
127 ,x_return_status);
128
129 IF x_return_status <> fnd_api.g_ret_sts_success THEN
130 RAISE error_load_trans;
131 END IF;
132 END IF; /* IF x_batch_header_rec.update_inventory_ind = 'Y' */
133
134 IF (g_debug <= gme_debug.g_log_procedure) THEN
135 gme_debug.put_line ( g_pkg_name
136 || '.'
137 || l_api_name
138 || ':'
139 || 'After fetch_tab tab.count='
140 || l_material_details_tab.COUNT);
141 END IF;
142
143 FOR i IN 1 .. l_material_details_tab.COUNT LOOP
144 -- IF PHANTOM_ID is not null,
145 -- THEN either terminate or cancel the phantom batch based on the batch status
146 IF (g_debug <= gme_debug.g_log_procedure) THEN
147 gme_debug.put_line ( g_pkg_name
148 || '.'
149 || l_api_name
150 || ':'
151 || 'phantom_id='
152 || l_material_details_tab (i).phantom_id);
153 END IF;
154
155 IF (NVL (l_material_details_tab (i).phantom_id, 0) <> 0) THEN
156 l_batch_header_rec.batch_id :=
157 l_material_details_tab (i).phantom_id;
158
159 IF NOT (gme_batch_header_dbl.fetch_row
160 (p_batch_header => l_batch_header_rec
161 ,x_batch_header => l_in_batch_header_rec) ) THEN
162 RAISE batch_header_fetch_error;
163 END IF;
164
165 IF l_in_batch_header_rec.batch_status = 2 THEN
166 l_in_batch_header_rec.actual_cmplt_date :=
167 p_batch_header_rec.actual_cmplt_date;
168 -- Recursive call to private API.for terminate
169 gme_terminate_batch_pvt.terminate_batch
170 (p_batch_header_rec => l_in_batch_header_rec
171 ,x_batch_header_rec => l_batch_header_rec
172 ,x_return_status => x_return_status);
173
174 IF x_return_status <> fnd_api.g_ret_sts_success THEN
175 RAISE phantom_batch_terminate_error;
176 END IF;
177 ELSIF l_in_batch_header_rec.batch_status = 1 THEN
178 -- Call to cancel batch for the phantom
179 gme_cancel_batch_pvt.cancel_batch
180 (p_batch_header_rec => l_in_batch_header_rec
181 ,x_batch_header_rec => l_batch_header_rec
182 ,x_return_status => x_return_status);
183
184 IF x_return_status <> fnd_api.g_ret_sts_success THEN
185 RAISE phantom_batch_cancel_error;
186 END IF;
187 END IF; /* l_in_batch_header_rec.batch_status */
188 END IF; /* phantom_id <> 0 */
189 END LOOP;
190
191 -- Delete the Inventory transaction under specific conditions
192 IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
193 gme_cancel_batch_pvt.purge_batch_exceptions
194 (p_batch_header_rec => x_batch_header_rec
195 ,p_delete_invis_mo => 'F'
196 ,p_delete_reservations => 'T'
197 ,p_delete_trans_pairs => 'F'
198 ,x_return_status => x_return_status);
199
200 IF x_return_status <> fnd_api.g_ret_sts_success THEN
201 RAISE purge_exception_err;
202 END IF;
203 END IF;
204
205 -- Now Examine the batch step data
206 IF (g_debug <= gme_debug.g_log_procedure) THEN
207 gme_debug.put_line ( g_pkg_name
208 || '.'
209 || l_api_name
210 || ':'
211 || 'Check routing_id/poc_ind='
212 || x_batch_header_rec.routing_id
213 || '/'
214 || x_batch_header_rec.poc_ind);
215 END IF;
216
217 IF x_batch_header_rec.poc_ind = 'Y' THEN
218 /* Now update the batch step status to Terminate (5) */
219 -- Get batch_step_ids bulk collected for batch
220 IF (g_debug <= gme_debug.g_log_procedure) THEN
221 gme_debug.put_line ( g_pkg_name
222 || '.'
223 || l_api_name
224 || ':'
225 || 'After fetch_batch_steps return_status='
226 || x_return_status);
227 END IF;
228
229 OPEN cur_get_wip_steps (x_batch_header_rec.batch_id);
230
231 FETCH cur_get_wip_steps
232 BULK COLLECT INTO l_batch_step_tab;
233
234 CLOSE cur_get_wip_steps;
235
236 FOR i IN 1 .. l_batch_step_tab.COUNT LOOP
237
238 /* Call terminate step api to terminate the all steps */
239 l_batch_step_rec := l_batch_step_tab (i);
240 l_in_batch_step_rec := l_batch_step_rec;
241
242 -- Bug 9478698 - Call GMO only if enhanced_pi_ind is activated.
243 IF x_batch_header_rec.enhanced_pi_ind = 'Y' THEN
244
245 -- Pawan Kumar added for bug 5034336
246 -- before terminating the steps we need to check that they are locked
247 gmo_vbatch_grp.GET_ENTITY_LOCK_STATUS (
248 P_API_VERSION => 1.0,
249 P_INIT_MSG_LIST => FND_API.G_FALSE,
250 P_COMMIT => FND_API.G_FALSE,
251 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
252 X_RETURN_STATUS => x_return_status ,
253 X_MSG_COUNT => l_message_count,
254 X_MSG_DATA => l_message_list,
255 P_ENTITY_NAME => 'OPERATION',
256 P_ENTITY_KEY => l_in_batch_step_rec.batchstep_id,
257 P_REQUESTER => gme_common_pvt.g_user_ident,
258 X_LOCK_STATUS => l_lock_status,
259 X_LOCKED_BY_STATUS => l_locked_by_status,
260 X_LOCK_ALLOWED => l_lock_allowed);
261
262 IF x_return_status <> fnd_api.g_ret_sts_success THEN
263 RAISE gmo_lock_error;
264 END IF;
265
266 IF (g_debug <= gme_debug.g_log_procedure) THEN
267 gme_debug.put_line ( g_pkg_name
268 || '.'
269 || l_api_name
270 || ':'
271 || 'from gmo the lock_status='
272 || l_lock_status);
273 END IF;
274
275 IF l_lock_status = 'Y' THEN
276 gme_common_pvt.log_message ('GME_STEP_LOCK_ERROR');
277 RAISE gmo_lock_error;
278 END IF;
279 END IF;
280
281 gme_terminate_step_pvt.terminate_step
282 (p_batch_step_rec => l_in_batch_step_rec
283 ,p_update_inventory_ind => x_batch_header_rec.update_inventory_ind
284 ,p_actual_cmplt_date => p_batch_header_rec.actual_cmplt_date
285 ,x_return_status => x_return_status
286 ,x_batch_step_rec => l_batch_step_rec);
287
288 IF x_return_status <> fnd_api.g_ret_sts_success THEN
289 RAISE batch_step_terminate_error;
290 END IF;
291 END LOOP;
292
293 OPEN cur_get_pending_steps (x_batch_header_rec.batch_id);
294
295 FETCH cur_get_pending_steps
296 BULK COLLECT INTO m_batch_step_tab;
297
298 CLOSE cur_get_pending_steps;
299
300 FOR i IN 1 .. m_batch_step_tab.COUNT LOOP
301 /* Call cancel step api to cancel steps */
302 l_batch_step_rec := m_batch_step_tab (i);
303 l_in_batch_step_rec := l_batch_step_rec;
304 gme_cancel_step_pvt.cancel_step
305 (p_batch_step_rec => l_in_batch_step_rec
306 ,p_update_inventory_ind => x_batch_header_rec.update_inventory_ind
307 ,x_return_status => x_return_status
308 ,x_batch_step_rec => l_batch_step_rec);
309
310 IF x_return_status <> fnd_api.g_ret_sts_success THEN
311 RAISE batch_step_cancel_error;
312 END IF;
313 END LOOP;
314 END IF;
315
316 /* Set up the fields in output structure. */
317 x_batch_header_rec.terminated_ind := 1;
318 x_batch_header_rec.batch_status := 3;
319 x_batch_header_rec.actual_cmplt_date :=
320 p_batch_header_rec.actual_cmplt_date;
321
322 /* Update the batch step to the database */
323 IF (g_debug <= gme_debug.g_log_procedure) THEN
324 gme_debug.put_line ( g_pkg_name
325 || '.'
326 || l_api_name
327 || ':'
328 || 'Call BATCH UPDATE_ROW');
329 END IF;
330
331 IF NOT (gme_batch_header_dbl.update_row (x_batch_header_rec) ) THEN
332 RAISE batch_header_upd_err;
333 END IF;
334
335 /* Re-retrieve batch header */
336 IF NOT (gme_batch_header_dbl.fetch_row
337 (p_batch_header => p_batch_header_rec
338 ,x_batch_header => x_batch_header_rec) ) THEN
339 RAISE batch_header_fetch_error;
340 END IF;
341
342 -- 4944024 BEGIN
343 -- Delete any outstanding reservations against this batch as a supply source
344 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
345 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Invoking delete_prod_supply_resv for batch header' );
346 END IF;
347
348 l_batch_header_rec := x_batch_header_rec;
349 gme_supply_res_pvt.delete_batch_prod_supply_resv (
350 p_batch_header_rec => l_batch_header_rec
351 ,x_msg_count => l_message_count
352 ,x_msg_data => l_message_list
353 ,x_return_status => x_return_status);
354
355 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
356 RAISE prod_supply_resv_err;
357 END IF;
358 -- 4944024 END
359
360 -- B3297712 - abort any QM sample workflows for this batch.
361 abort_wf (p_type => 'GMDQMSMC'
362 ,p_item_id => x_batch_header_rec.batch_id);
363
364 IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
365 /* Insert the event into the batch history table */
366 IF NOT gme_common_pvt.create_history
367 (x_batch_header_rec
368 ,p_batch_header_rec.batch_status) THEN
369 RAISE batch_hist_insert_err;
370 END IF;
371 END IF;
372
373 IF (g_debug <= gme_debug.g_log_procedure) THEN
374 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
375 || 'Exiting');
376 END IF;
377 EXCEPTION
378 WHEN purge_exception_err THEN
379 IF (g_debug <= gme_debug.g_log_procedure) THEN
380 gme_debug.put_line ( g_pkg_name
381 || '.'
382 || l_api_name
383 || ':'
384 || ' purge_exception_err.');
385 END IF;
386
387 x_return_status := fnd_api.g_ret_sts_error;
388 -- 4944024 BEGIN
389 WHEN prod_supply_resv_err THEN
390 IF (g_debug <= gme_debug.g_log_procedure) THEN
391 gme_debug.put_line ( g_pkg_name
392 || '.'
393 || l_api_name
394 || ':'
395 || ' delete_batch_prod_supply_resv ERROR');
396 END IF;
397 -- 4944024 END
398 WHEN dep_material_details_fetch_err THEN
399 IF (g_debug <= gme_debug.g_log_procedure) THEN
400 gme_debug.put_line ( g_pkg_name
401 || '.'
402 || l_api_name
403 || ':'
404 || ' MATL_DETAILS_FETCH_ERROR.');
405 END IF;
406
407 x_return_status := fnd_api.g_ret_sts_error;
408 WHEN invalid_batch_status THEN
409 IF (g_debug <= gme_debug.g_log_procedure) THEN
410 gme_debug.put_line ( g_pkg_name
411 || '.'
412 || l_api_name
413 || ':'
414 || 'INVALID_BATCH_STATUS.');
415 END IF;
416
417 x_return_status := fnd_api.g_ret_sts_error;
418 gme_common_pvt.log_message ('GME_INVALID_BSTAT_TERM');
419 WHEN batch_header_upd_err THEN
420 IF (g_debug <= gme_debug.g_log_procedure) THEN
421 gme_debug.put_line ( g_pkg_name
422 || '.'
423 || l_api_name
424 || ':'
425 || 'BATCH_HEADER_UPD_ERR.');
426 END IF;
427
428 x_return_status := fnd_api.g_ret_sts_error;
429 gme_common_pvt.log_message ('GME_API_BATCH_HEADER_UPD_ERROR');
430 WHEN phantom_batch_terminate_error THEN
431 IF (g_debug <= gme_debug.g_log_procedure) THEN
432 gme_debug.put_line ( g_pkg_name
433 || '.'
434 || l_api_name
435 || ':'
436 || 'PHANTOM_BATCH_TERMINATE_ERROR.');
437 END IF;
438 WHEN phantom_batch_cancel_error THEN
439 IF (g_debug <= gme_debug.g_log_procedure) THEN
440 gme_debug.put_line ( g_pkg_name
441 || '.'
442 || l_api_name
443 || ':'
444 || 'PHANTOM_BATCH_CANCEL_ERROR.');
445 END IF;
446 WHEN gmo_lock_error THEN
447 IF (g_debug <= gme_debug.g_log_procedure) THEN
448 gme_debug.put_line ( g_pkg_name
449 || '.'
450 || l_api_name
451 || ':'
452 || 'GMO_LOCK_ERROR.');
453 END IF;
454 x_return_status := fnd_api.g_ret_sts_error;
455 WHEN batch_hist_insert_err THEN
456 IF (g_debug <= gme_debug.g_log_procedure) THEN
457 gme_debug.put_line ('BATCH_HIST_INSERT_ERR.');
458 END IF;
459
460 x_return_status := fnd_api.g_ret_sts_error;
461 WHEN delete_inv_txns_err THEN
462 IF (g_debug <= gme_debug.g_log_procedure) THEN
463 gme_debug.put_line ( g_pkg_name
464 || '.'
465 || l_api_name
466 || ':'
467 || 'DELETE_INV_TXNS_ERR.');
468 END IF;
469 WHEN batch_step_terminate_error THEN
470 IF (g_debug <= gme_debug.g_log_procedure) THEN
471 gme_debug.put_line ( g_pkg_name
472 || '.'
473 || l_api_name
474 || ':'
475 || 'BATCH_STEP_TERMINATE_ERROR.');
476 END IF;
477 WHEN batch_step_cancel_error THEN
478 IF (g_debug <= gme_debug.g_log_procedure) THEN
479 gme_debug.put_line ( g_pkg_name
480 || '.'
481 || l_api_name
482 || ':'
483 || ' BATCH_STEP_CANCEL_ERROR.');
484 END IF;
485 WHEN OTHERS THEN
486 IF g_debug <= gme_debug.g_log_unexpected THEN
487 gme_debug.put_line ( 'When others exception in '
488 || g_pkg_name
489 || '.'
490 || l_api_name
491 || ' Error is '
492 || SQLERRM);
493 END IF;
494
495 ROLLBACK TO SAVEPOINT cancel_batch_step;
496 x_return_status := fnd_api.g_ret_sts_unexp_error;
497 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
498 END terminate_batch;
499
500 /*===============================================================================
501 Function
502 abort_wf
503 Description
504 This procedure is used to abort the workflow that is passed in.
505
506 History
507 A. Newbury B3297712 created new procedure to abort requested workflow.
508
509 Parameters
510 p_type workflow keyword
511 p_item_id identifier of event to abort
512 ==================================================================================*/
513 PROCEDURE abort_wf (p_type IN VARCHAR2, p_item_id IN NUMBER)
514 IS
515 BEGIN
516 IF (g_debug <= gme_debug.g_log_procedure) THEN
517 gme_debug.put_line
518 ( 'before call to wf_engine.abortprocess with batch_id='
519 || TO_CHAR (p_item_id)
520 || ' type='
521 || p_type
522 ,gme_debug.g_log_procedure
523 ,'terminate_batch');
524 END IF;
525
526 /* Cancel workflow process */
527 wf_engine.abortprocess (itemtype => p_type, itemkey => p_item_id);
528 EXCEPTION
529 WHEN OTHERS THEN
530 IF (g_debug <= gme_debug.g_log_procedure) THEN
531 gme_debug.put_line
532 ( ' terminate_batch, error in abort_wf batch_id='
533 || TO_CHAR (p_item_id)
534 || ' type='
535 || p_type
536 ,gme_debug.g_log_error
537 ,'terminate_batch');
538 END IF;
539 END abort_wf;
540 END gme_terminate_batch_pvt;