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