[Home] [Help]
PACKAGE BODY: APPS.GME_CANCEL_BATCH_PVT
Source
1 PACKAGE BODY gme_cancel_batch_pvt AS
2 /* $Header: GMEVCCBB.pls 120.10.12020000.2 2013/01/10 16:25:51 gmurator ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_CANCEL_BATCH_PVT';
5
6 /*
7 REM *********************************************************************
8 REM *
9 REM * FILE: GMEVCCBB.pls
10 REM * PURPOSE: Package Body for the GME batch cancel api
11 REM * AUTHOR: Pawan Kumar, OPM Development
12 REM * DATE: 28th APRIL 2005
13 REM * HISTORY:
14 REM * ========
15 REM * Namit Singhi Bug#5411627. Removed the recursive call to purge_batch_exceptions,
16 REM * as recusive calls are already added to cancel_batch and terminate_batch APIs.
17
18 REM * G. Muratore 22-MAR-09 Bug 8312658 Rework of 5411627
19 REM * Reintroduced recursive logic conditionally for those actions that require it. This code
20 REM * was removed by bug 5411627 for cancel and terminate, but it is needed for complete batch.
21 REM * New parameter p_recursive added. 'R' value will initiate recursive logic.
22 REM * PROCEDURE: purge_batch_exceptions
23
24 REM * G. Muratore 04-OCT-10 Bug 10100973
25 REM * Remove orphan move order line records if they exist.
26 REM * PROCEDURE: cancel_batch
27
28 REM * G. Muratore 15-FEB-2011 Bug 11067065
29 REM * Remove any remaining open move order line records if they exist.
30
31 REM * G. Muratore 23-MAR-2011 Bug 11887412 - slight rework of 11067065.
32 REM * Do not delete invisible move orders if batch has been closed previously.
33
34 REM * G. Muratore 10-JAN-2013 Bug 16031581
35 REM * Load resource transactions into memory so that they get reversed.
36 REM * PROCEDURE: cancel_batch
37 REM **********************************************************************
38 */
39
40 /*================================================================================
41 Procedure
42 Cancel_Batch
43 Description
44 This particular procedure call cancel the batch.
45 Parameters
46 p_batch_header_rec The batch header row to identify the batch
47 p_validation_level Errors to skip before returning - Default 100
48 x_batch_header_rec The batch header row to identify the batch
49 x_message_count The number of messages in the message stack
50 x_message_list message stack where the api writes its messages
51 x_return_status outcome of the API call
52 S - Success
53 E - Error
54 U - Unexpected error
55
56 History
57 G. Muratore 04-OCT-10 Bug 10100973
58 Remove orphan move order line records if they exist.
59
60 G. Muratore 10-JAN-2013 Bug 16031581
61 Load resource transactions into memory so that they get reversed.
62 ================================================================================*/
63 PROCEDURE cancel_batch (
64 p_batch_header_rec IN gme_batch_header%ROWTYPE
65 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
66 ,x_return_status OUT NOCOPY VARCHAR2)
67 IS
68 CURSOR cur_get_steps (v_batch_id NUMBER)
69 IS
70 SELECT *
71 FROM gme_batch_steps
72 WHERE batch_id = v_batch_id
73 ORDER BY batchstep_id;
74
75 -- Bug 16031581 - Add variables so we can load resource transactions.
76 l_rsrc_cnt NUMBER;
77
78 /* Miscellaneous */
79 l_batch_step_rec gme_batch_steps%ROWTYPE;
80 l_in_batch_step_rec gme_batch_steps%ROWTYPE;
81 l_material_details_rec gme_material_details%ROWTYPE;
82 l_material_details_tab gme_common_pvt.material_details_tab;
83 l_batch_step_tab gme_common_pvt.steps_tab;
84 l_batch_header_rec gme_batch_header%ROWTYPE;
85 l_ph_batch_header_rec gme_batch_header%ROWTYPE;
86 l_rsrc_trans_count NUMBER;
87 l_message_count NUMBER; -- 4944024
88 l_message_data VARCHAR2(2000); -- 4944024
89 l_return_status VARCHAR2 (1);
90 l_api_name CONSTANT VARCHAR2 (30) := 'Cancel Batch';
91 batch_header_upd_err EXCEPTION;
92 batch_step_cancel_error EXCEPTION;
93 purge_exception_err EXCEPTION;
94 batch_step_fetch_err EXCEPTION;
95 batch_hist_insert_err EXCEPTION;
96 phantom_batch_cancel_error EXCEPTION;
97 material_details_fetch_err EXCEPTION;
98 batch_header_fetch_failure EXCEPTION;
99 reservation_delete_err EXCEPTION; -- 4944024
100 BEGIN
101 IF (g_debug <= gme_debug.g_log_procedure) THEN
102 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
103 || 'Entering');
104 END IF;
105
106 /* Set the success staus to success inititally*/
107 x_return_status := fnd_api.g_ret_sts_success;
108 x_batch_header_rec := p_batch_header_rec;
109 -- Now Examine the ingredient material rows :
110 l_material_details_rec.batch_id := x_batch_header_rec.batch_id;
111
112 IF (g_debug <= gme_debug.g_log_procedure) THEN
113 gme_debug.put_line ( g_pkg_name
114 || '.'
115 || l_api_name
116 || ':'
117 || 'Call fetch material tab');
118 END IF;
119
120 IF NOT (gme_material_details_dbl.fetch_tab
121 (p_material_detail => l_material_details_rec
122 ,x_material_detail => l_material_details_tab) ) THEN
123 RAISE material_details_fetch_err;
124 END IF;
125
126 FOR i IN 1 .. l_material_details_tab.COUNT LOOP
127 -- 4944024 BEGIN
128 -- Need to delete any reservations agains PROD supply
129 -- ===================================================
130 IF l_material_details_tab(i).line_type <> -1 THEN
131 IF (g_debug <= gme_debug.g_log_procedure) THEN
132 gme_debug.put_line ( g_pkg_name
133 || '.'
134 || l_api_name
135 || ':'
136 || ' Call delete_prod_supply_resv');
137 END IF;
138
139 gme_supply_res_pvt.delete_prod_supply_resv (
140 p_matl_dtl_rec => l_material_details_tab(i)
141 ,x_msg_count => l_message_count
142 ,x_msg_data => l_message_data
143 ,x_return_status => x_return_status);
144
145 IF (g_debug <= gme_debug.g_log_procedure) THEN
146 gme_debug.put_line ( g_pkg_name
147 || '.'
148 || l_api_name
149 || ':'
150 || ' Return from delete_prod_supply_resv indicates '
151 || x_return_status);
152 END IF;
153 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
154 RAISE reservation_delete_err;
155 END IF;
156 END IF;
157 -- 4944024 END
158
159 IF (g_debug <= gme_debug.g_log_procedure) THEN
160 gme_debug.put_line ( g_pkg_name
161 || '.'
162 || l_api_name
163 || ':'
164 || 'phantom_id='
165 || l_material_details_tab (i).phantom_id);
166 END IF;
167
168 -- IF PHANTOM_ID is not null,
169 -- THEN recurse from the start for each one.
170 IF (NVL (l_material_details_tab (i).phantom_id, 0) <> 0) THEN
171 IF (g_debug <= gme_debug.g_log_procedure) THEN
172 gme_debug.put_line ( g_pkg_name
173 || '.'
174 || l_api_name
175 || ':'
176 || 'calling cancel batch for phantom_id='
177 || l_material_details_tab (i).phantom_id);
178 END IF;
179
180 -- Recursive call to the private API.
181 l_batch_header_rec.batch_id :=
182 l_material_details_tab (i).phantom_id;
183 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header_rec
184 ,l_ph_batch_header_rec) ) THEN
185 RAISE batch_header_fetch_failure;
186 END IF;
187
188 gme_cancel_batch_pvt.cancel_batch
189 (p_batch_header_rec => l_ph_batch_header_rec
190 ,x_batch_header_rec => l_batch_header_rec
191 ,x_return_status => l_return_status);
192
193 IF l_return_status <> fnd_api.g_ret_sts_success THEN
194 RAISE phantom_batch_cancel_error;
195 END IF;
196 END IF;
197
198 -- Bug 10100973 - Let's remove Open orphan Move order line records if they exist.
199 DELETE FROM mtl_txn_request_lines l
200 WHERE organization_id = x_batch_header_rec.organization_id
201 AND transaction_source_type_id = gme_common_pvt.g_txn_source_type
202 AND txn_source_id = l_material_details_tab(i).batch_id
203 AND txn_source_line_id = l_material_details_tab(i).material_detail_id
204 AND line_status = 7
205 AND NOT EXISTS (SELECT 1
206 FROM mtl_txn_request_headers mtrh
207 WHERE mtrh.header_id = l.header_id)
208 AND NOT EXISTS (SELECT 1
209 FROM mtl_material_transactions_temp mmtt
210 where l.line_id = mmtt.move_order_line_id);
211 END LOOP;
212
213 -- Delete the Inventory transaction under specific conditions
214 IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
215 IF (g_debug <= gme_debug.g_log_procedure) THEN
216 gme_debug.put_line ( g_pkg_name
217 || '.'
218 || l_api_name
219 || ':'
220 || 'calling purge exceptions for batch_id='
221 || x_batch_header_rec.batch_id);
222 END IF;
223
224 -- delete all move-orders including invisble move orders
225 purge_batch_exceptions (p_batch_header_rec => x_batch_header_rec
226 ,p_delete_invis_mo => 'T'
227 ,p_delete_reservations => 'T'
228 ,p_delete_trans_pairs => 'T'
229 ,x_return_status => l_return_status);
230
231 IF l_return_status <> fnd_api.g_ret_sts_success THEN
232 RAISE purge_exception_err;
233 END IF;
234 END IF;
235
236 -- BUG 11067065 - Let's close any remaining open putaway MO line.
237 UPDATE MTL_TXN_REQUEST_Lines
238 SET line_status = 5
239 WHERE line_id in
240 (SELECT mtrl.line_id
241 FROM MTL_TXN_REQUEST_Lines mtrl, mtl_txn_request_headers mtrh
242 WHERE mtrl.TRANSACTION_SOURCE_TYPE_ID = 5
243 AND mtrl.LINE_STATUS = 7
244 AND mtrl.LPN_ID IS NOT NULL
245 AND mtrh.header_id = mtrl.header_id
246 AND mtrh.ORGANIZATION_ID = mtrl.organization_id
247 AND mtrl.txn_source_id = x_batch_header_rec.batch_id
248 AND mtrl.ORGANIZATION_ID = x_batch_header_rec.organization_id
249 AND mtrh.MOVE_ORDER_TYPE = 6);
250
251 -- Now Examine the batch step(POC data) :
252 IF (g_debug <= gme_debug.g_log_procedure) THEN
253 gme_debug.put_line ( g_pkg_name
254 || '.'
255 || l_api_name
256 || ':'
257 || 'Check routing_id/poc_ind='
258 || x_batch_header_rec.routing_id
259 || '/'
260 || x_batch_header_rec.poc_ind);
261 END IF;
262
263 IF x_batch_header_rec.poc_ind = 'Y' THEN
264 /* Now update the batch step status to Cancel (5) */
265 -- Get batch_step_ids bulk collected for batch
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 || 'routing exists');
272 END IF;
273
274 OPEN cur_get_steps (x_batch_header_rec.batch_id);
275
276 FETCH cur_get_steps
277 BULK COLLECT INTO l_batch_step_tab;
278
279 CLOSE cur_get_steps;
280
281 -- Bug 16031581 - Add call to reload resource transactions.
282 /* Load transactions in temporary table */
283 gme_trans_engine_util.load_rsrc_trans(p_batch_row => x_batch_header_rec
284 ,x_rsc_row_count => l_rsrc_cnt
285 ,x_return_status => l_return_status);
286
287 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
288 RAISE batch_step_cancel_error;
289 END IF;
290
291 FOR i IN 1 .. l_batch_step_tab.COUNT LOOP
292 IF (g_debug <= gme_debug.g_log_procedure) THEN
293 gme_debug.put_line ( g_pkg_name
294 || '.'
295 || l_api_name
296 || ':'
297 || 'calling cancel step for step_id='
298 || l_batch_step_tab (i).batchstep_id);
299 END IF;
300
301 /* Call Close step api to cancel the all steps */
302 -- This Proc should be written by someone else.
303 l_batch_step_rec := l_batch_step_tab (i);
304 l_in_batch_step_rec := l_batch_step_rec;
305 gme_cancel_step_pvt.cancel_step
306 (p_batch_step_rec => l_in_batch_step_rec
307 ,p_update_inventory_ind => x_batch_header_rec.update_inventory_ind
308 ,x_return_status => l_return_status
309 ,x_batch_step_rec => l_batch_step_rec);
310
311 IF l_return_status <> fnd_api.g_ret_sts_success THEN
312 RAISE batch_step_cancel_error;
313 END IF;
314 END LOOP;
315 END IF;
316
317 /* Set up the fields in output structure. */
318 x_batch_header_rec.batch_status := -1;
319
320 /* Update the batch status to the database */
321 IF (g_debug <= gme_debug.g_log_procedure) THEN
322 gme_debug.put_line ( g_pkg_name
323 || '.'
324 || l_api_name
325 || ':'
326 || 'Call batch UPDATE_ROW'
327 || x_batch_header_rec.batch_status);
328 END IF;
329
330 IF NOT (gme_batch_header_dbl.update_row (x_batch_header_rec) ) THEN
331 RAISE batch_header_upd_err;
332 END IF;
333
334 IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
335 -- Insert the event into the batch history table
336 IF NOT gme_common_pvt.create_history (x_batch_header_rec, -1) THEN
337 IF (g_debug <= gme_debug.g_log_procedure) THEN
338 gme_debug.put_line ( g_pkg_name
339 || '.'
340 || l_api_name
341 || ':'
342 || 'create history');
343 END IF;
344
345 RAISE batch_hist_insert_err;
346 END IF;
347 END IF;
348
349 /* Update the row who columns */
350 x_batch_header_rec.last_update_date := gme_common_pvt.g_timestamp;
351 x_batch_header_rec.last_updated_by := gme_common_pvt.g_user_ident;
352 x_batch_header_rec.last_update_login := gme_common_pvt.g_login_id;
353
354 IF (g_debug <= gme_debug.g_log_procedure) THEN
355 gme_debug.put_line ( g_pkg_name
356 || '.'
357 || l_api_name
358 || ':'
359 || 'Exiating cancel batch with status'
360 || x_return_status);
361 END IF;
362 EXCEPTION
363 WHEN purge_exception_err THEN
364 IF (g_debug <= gme_debug.g_log_procedure) THEN
365 gme_debug.put_line ( g_pkg_name
366 || '.'
367 || l_api_name
368 || ':'
369 || 'purge_exception_err');
370 END IF;
371
372 x_return_status := l_return_status;
373 WHEN material_details_fetch_err THEN
374 IF (g_debug <= gme_debug.g_log_procedure) THEN
375 gme_debug.put_line ( g_pkg_name
376 || '.'
377 || l_api_name
378 || ':'
379 || 'MATERIAL_FETCH_ERROR');
380 END IF;
381
382 x_return_status := fnd_api.g_ret_sts_error;
383 WHEN batch_header_fetch_failure THEN
384 IF (g_debug <= gme_debug.g_log_procedure) THEN
385 gme_debug.put_line ( g_pkg_name
386 || '.'
387 || l_api_name
388 || ':'
389 || 'BATCH_FETCH_ERROR');
390 END IF;
391
392 x_return_status := fnd_api.g_ret_sts_error;
393 WHEN phantom_batch_cancel_error THEN
394 IF (g_debug <= gme_debug.g_log_procedure) THEN
395 gme_debug.put_line ( g_pkg_name
396 || '.'
397 || l_api_name
398 || ':'
399 || 'PHANTOM_BATCH_CANCEL_ERROR');
400 END IF;
401
402 x_return_status := l_return_status;
403 WHEN batch_header_upd_err THEN
404 IF (g_debug <= gme_debug.g_log_procedure) THEN
405 gme_debug.put_line ( g_pkg_name
406 || '.'
407 || l_api_name
408 || ':'
409 || 'BATCH_HEADER_UPD_ERR');
410 END IF;
411
412 x_return_status := fnd_api.g_ret_sts_error;
413 gme_common_pvt.log_message ('GME_API_BATCH_HEADER_UPD_ERROR');
414 WHEN batch_hist_insert_err THEN
415 IF (g_debug <= gme_debug.g_log_procedure) THEN
416 gme_debug.put_line ( g_pkg_name
417 || '.'
418 || l_api_name
419 || ':'
420 || 'BATCH_HIST_INSERT_ERR.');
421 END IF;
422
423 x_return_status := fnd_api.g_ret_sts_error;
424 WHEN batch_step_fetch_err THEN
425 IF (g_debug <= gme_debug.g_log_procedure) THEN
426 gme_debug.put_line ( g_pkg_name
427 || '.'
428 || l_api_name
429 || ':'
430 || ' BATCH_STEP_FETCH_ERR.');
431 END IF;
432
433 x_return_status := fnd_api.g_ret_sts_error;
434 WHEN batch_step_cancel_error THEN
435 IF (g_debug <= gme_debug.g_log_procedure) THEN
436 gme_debug.put_line ( g_pkg_name
437 || '.'
438 || l_api_name
439 || ':'
440 || ' BATCH_STEP_CANCEL_ERROR.');
441 END IF;
442
443 x_return_status := l_return_status;
444 -- 4944024 BEGIN
445 WHEN reservation_delete_err THEN
446 IF (g_debug <= gme_debug.g_log_procedure) THEN
447 gme_debug.put_line ( g_pkg_name
448 || '.'
449 || l_api_name
450 || ':'
451 || ' DELETE_PROD_SUPPLY_RESV ERROR');
452 END IF;
453 -- 4944024 END
454 WHEN OTHERS THEN
455 IF g_debug <= gme_debug.g_log_unexpected THEN
456 gme_debug.put_line ( 'When others exception in '
457 || g_pkg_name
458 || '.'
459 || l_api_name
460 || ' Error is '
461 || SQLERRM);
462 END IF;
463
464 x_return_status := fnd_api.g_ret_sts_unexp_error;
465 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
466 END cancel_batch;
467
468
469
470 PROCEDURE purge_batch_exceptions (
471 p_batch_header_rec IN gme_batch_header%ROWTYPE
472 ,p_delete_invis_mo IN VARCHAR2 := 'F'
473 ,p_delete_reservations IN VARCHAR2 := 'F'
474 ,p_delete_trans_pairs IN VARCHAR2 := 'F'
475 ,p_recursive IN VARCHAR2 := 'N'
476 ,x_return_status OUT NOCOPY VARCHAR2)
477 IS
478
479 l_material_details_rec gme_material_details%ROWTYPE;
480 l_material_details_tab gme_common_pvt.material_details_tab;
481 l_batch_header_rec gme_batch_header%ROWTYPE;
482 l_ph_batch_header_rec gme_batch_header%ROWTYPE;
483 l_api_name CONSTANT VARCHAR2 (30) := 'purge_batch_exceptions';
484 delete_reservations_err EXCEPTION;
485 delete_move_order_err EXCEPTION;
486 delete_trans_pair_err EXCEPTION;
487 delete_pend_prod_lots_err EXCEPTION;
488 phantom_batch_purge_error EXCEPTION;
489 material_details_fetch_err EXCEPTION;
490 batch_header_fetch_failure EXCEPTION;
491 l_return_status VARCHAR2(1);
492
493 -- Bug 11887412 - introduce cursor and variable.
494 CURSOR cur_check_hist (v_batch_id NUMBER)
495 IS
496 SELECT count(*)
497 FROM gme_batch_history
498 WHERE batch_id = v_batch_id
499 AND new_status = 4;
500
501 l_hist_count NUMBER;
502 l_delete_invis_mo VARCHAR2(1);
503
504 BEGIN
505 -- Initially let us assign the return status to success
506 x_return_status := fnd_api.g_ret_sts_success;
507
508 IF (g_debug <= gme_debug.g_log_statement) THEN
509 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
510 || 'Entering');
511 END IF;
512
513 l_batch_header_rec := p_batch_header_rec;
514
515 IF (g_debug <= gme_debug.g_log_statement) THEN
516 gme_debug.put_line ( g_pkg_name
517 || '.'
518 || l_api_name
519 || ':'
520 || 'batch_id:'
521 || l_batch_header_rec.batch_id);
522 gme_debug.put_line ( g_pkg_name
523 || '.'
524 || l_api_name
525 || ':'
526 || 'organization_id:'
527 || l_batch_header_rec.organization_id);
528 END IF;
529 l_material_details_rec.batch_id := l_batch_header_rec.batch_id;
530
531 IF (g_debug <= gme_debug.g_log_procedure) THEN
532 gme_debug.put_line ( g_pkg_name
533 || '.'
534 || l_api_name
535 || ':'
536 || 'Call fetch material tab');
537 END IF;
538
539 IF NOT (gme_material_details_dbl.fetch_tab
540 (p_material_detail => l_material_details_rec
541 ,x_material_detail => l_material_details_tab) ) THEN
542 RAISE material_details_fetch_err;
543 END IF;
544
545 -- Bug 8312658 - Reintroduced recursive logic conditionally for those actions that require it here.
546 -- This code was removed by bug 5411627 for cancel and terminate, but it is needed for complete batch.
547 IF p_recursive = 'R' THEN
548 FOR i IN 1 .. l_material_details_tab.COUNT LOOP
549 IF (g_debug <= gme_debug.g_log_procedure) THEN
550 gme_debug.put_line ( g_pkg_name
551 || '.'
552 || l_api_name
553 || ':'
554 || 'phantom_id='
555 || l_material_details_tab (i).phantom_id);
556 END IF;
557 -- IF PHANTOM_ID is not null,
558 -- THEN recurse from the start for each one.
559 IF (NVL (l_material_details_tab (i).phantom_id, 0) <> 0) THEN
560 IF (g_debug <= gme_debug.g_log_procedure) THEN
561 gme_debug.put_line ( g_pkg_name
562 || '.'
563 || l_api_name
564 || ':'
565 || 'calling purge batch for phantom_id='
566 || l_material_details_tab (i).phantom_id);
567 END IF;
568
569 -- Recursive call to the private API.
570 l_ph_batch_header_rec.batch_id :=
571 l_material_details_tab (i).phantom_id;
572 IF NOT (gme_batch_header_dbl.fetch_row (l_ph_batch_header_rec
573 ,l_ph_batch_header_rec) ) THEN
574 RAISE batch_header_fetch_failure;
575 END IF;
576
577 purge_batch_exceptions (p_batch_header_rec => l_ph_batch_header_rec
578 ,p_delete_invis_mo => p_delete_invis_mo
579 ,p_delete_reservations => p_delete_reservations
580 ,p_delete_trans_pairs => p_delete_trans_pairs
581 ,x_return_status => l_return_status);
582
583 IF l_return_status <> fnd_api.g_ret_sts_success THEN
584 RAISE phantom_batch_purge_error;
585 END IF;
586 END IF;
587 END LOOP;
588 END IF; -- If p_recursive = 'R'
589
590 -- delete move orders
591 IF (g_debug <= gme_debug.g_log_statement) THEN
592 gme_debug.put_line ( g_pkg_name
593 || '.'
594 || l_api_name
595 || ':'
596 || 'deleting move orders for batch_id:'
597 || l_batch_header_rec.batch_id);
598 END IF;
599
600 -- Bug 11887412 - Do not delete invisible move orders if batch has been closed previously.
601 OPEN cur_check_hist (l_batch_header_rec.batch_id);
602 FETCH cur_check_hist INTO l_hist_count;
603 CLOSE cur_check_hist;
604
605 l_delete_invis_mo := p_delete_invis_mo;
606 IF l_hist_count > 0 THEN
607 l_delete_invis_mo := 'F';
608 END IF;
609
610 gme_move_orders_pvt.delete_batch_move_orders
611 (p_organization_id => l_batch_header_rec.organization_id
612 ,p_batch_id => l_batch_header_rec.batch_id
613 ,p_delete_invis => l_delete_invis_mo
614 ,x_return_status => l_return_status);
615
616 IF l_return_status <> fnd_api.g_ret_sts_success THEN
617 RAISE delete_move_order_err;
618 END IF;
619
620 IF p_delete_reservations = fnd_api.g_true THEN
621 -- delete all reservations
622 IF (g_debug <= gme_debug.g_log_statement) THEN
623 gme_debug.put_line ( g_pkg_name
624 || '.'
625 || l_api_name
626 || ':'
627 || 'deleting reservations for batch_id:'
628 || l_batch_header_rec.batch_id);
629 END IF;
630
631 gme_reservations_pvt.delete_batch_reservations
632 (p_organization_id => l_batch_header_rec.organization_id
633 ,p_batch_id => l_batch_header_rec.batch_id
634 ,x_return_status => l_return_status);
635
636 IF l_return_status <> fnd_api.g_ret_sts_success THEN
637 RAISE delete_reservations_err;
638 END IF;
639 END IF;
640
641 IF p_delete_trans_pairs = fnd_api.g_true THEN
642 IF (g_debug <= gme_debug.g_log_statement) THEN
643 gme_debug.put_line ( g_pkg_name
644 || '.'
645 || l_api_name
646 || ':'
647 || 'deleting trans pairs for batch_id:'
648 || l_batch_header_rec.batch_id);
649 END IF;
650
651 gme_transactions_pvt.purge_trans_pairs
652 (p_batch_id => l_batch_header_rec.batch_id
653 ,x_return_status => l_return_status);
654
655 IF l_return_status <> fnd_api.g_ret_sts_success THEN
656 RAISE delete_trans_pair_err;
657 END IF;
658 END IF;
659 IF (g_debug <= gme_debug.g_log_statement) THEN
660 gme_debug.put_line ( g_pkg_name
661 || '.'
662 || l_api_name
663 || ':'
664 || 'deleting pending product lots for batch_id:'
665 || l_batch_header_rec.batch_id);
666 END IF;
667
668 delete_pending_lots (p_batch_id => l_batch_header_rec.batch_id
669 ,x_return_status => l_return_status);
670
671 IF l_return_status <> fnd_api.g_ret_sts_success THEN
672 RAISE delete_pend_prod_lots_err;
673 END IF;
674
675 IF (g_debug <= gme_debug.g_log_statement) THEN
676 gme_debug.put_line ( g_pkg_name
677 || '.'
678 || l_api_name
679 || ':'
680 || 'Exiting with '
681 || x_return_status);
682 END IF;
683 EXCEPTION
684 WHEN material_details_fetch_err THEN
685 IF (g_debug <= gme_debug.g_log_procedure) THEN
686 gme_debug.put_line ( g_pkg_name
687 || '.'
688 || l_api_name
689 || ':'
690 || 'MATERIAL_FETCH_ERROR');
691 END IF;
692
693 x_return_status := fnd_api.g_ret_sts_error;
694 WHEN batch_header_fetch_failure THEN
695 IF (g_debug <= gme_debug.g_log_procedure) THEN
696 gme_debug.put_line ( g_pkg_name
697 || '.'
698 || l_api_name
699 || ':'
700 || 'BATCH_FETCH_ERROR');
701 END IF;
702
703 x_return_status := fnd_api.g_ret_sts_error;
704 WHEN phantom_batch_purge_error THEN
705 IF (g_debug <= gme_debug.g_log_procedure) THEN
706 gme_debug.put_line ( g_pkg_name
707 || '.'
708 || l_api_name
709 || ':'
710 || 'delete_move_order_err');
711 END IF;
712
713 x_return_status := l_return_status;
714 WHEN delete_move_order_err THEN
715 IF (g_debug <= gme_debug.g_log_procedure) THEN
716 gme_debug.put_line ( g_pkg_name
717 || '.'
718 || l_api_name
719 || ':'
720 || 'delete_move_order_err');
721 END IF;
722
723 x_return_status := l_return_status;
724 WHEN delete_reservations_err THEN
725 IF (g_debug <= gme_debug.g_log_procedure) THEN
726 gme_debug.put_line ( g_pkg_name
727 || '.'
728 || l_api_name
729 || ':'
730 || 'delete_reservations_err');
731 END IF;
732
733 x_return_status := l_return_status;
734 WHEN delete_trans_pair_err THEN
735 IF (g_debug <= gme_debug.g_log_procedure) THEN
736 gme_debug.put_line ( g_pkg_name
737 || '.'
738 || l_api_name
739 || ':'
740 || 'delete_trans_pair_err');
741 END IF;
742
743 x_return_status := l_return_status;
744 WHEN delete_pend_prod_lots_err THEN
745 IF (g_debug <= gme_debug.g_log_procedure) THEN
746 gme_debug.put_line ( g_pkg_name
747 || '.'
748 || l_api_name
749 || ':'
750 || 'delete_pend_prod_lots_err');
751 END IF;
752
753 x_return_status := l_return_status;
754 WHEN fnd_api.g_exc_error THEN
755 x_return_status := fnd_api.g_ret_sts_error;
756 WHEN fnd_api.g_exc_unexpected_error THEN
757 x_return_status := fnd_api.g_ret_sts_unexp_error;
758 WHEN OTHERS THEN
759 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
760 x_return_status := fnd_api.g_ret_sts_unexp_error;
761
762 IF g_debug <= gme_debug.g_log_unexpected THEN
763 gme_debug.put_line ( 'When others exception in '
764 || g_pkg_name
765 || '.'
766 || l_api_name
767 || ' Error is '
768 || SQLERRM);
769 END IF;
770
771 x_return_status := fnd_api.g_ret_sts_unexp_error;
772 END purge_batch_exceptions;
773
774 PROCEDURE delete_pending_lots (
775 p_batch_id IN NUMBER
776 ,p_material_detail_id IN NUMBER DEFAULT NULL
777 ,x_return_status OUT NOCOPY VARCHAR2)
778 IS
779 l_batch_id NUMBER;
780 l_material_detail_id NUMBER;
781 l_api_name CONSTANT VARCHAR2 (30) := 'delete_pending_lots';
782 l_return_status VARCHAR2 (1);
783 BEGIN
784 -- Initially let us assign the return status to success
785 x_return_status := fnd_api.g_ret_sts_success;
786
787 IF (g_debug <= gme_debug.g_log_statement) THEN
788 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
789 || 'Entering');
790 END IF;
791
792 IF p_batch_id IS NULL AND p_material_detail_id IS NULL THEN
793 -- may use required message
794 gme_common_pvt.log_message ('GME_INVALID_FIELD'
795 ,'FIELD'
796 ,'p_batch_id');
797 RAISE fnd_api.g_exc_error;
798 END IF;
799
800 l_batch_id := p_batch_id;
801 l_material_detail_id := p_material_detail_id;
802
803 IF (g_debug <= gme_debug.g_log_statement) THEN
804 gme_debug.put_line ( g_pkg_name
805 || '.'
806 || l_api_name
807 || ':'
808 || 'batch_id:'
809 || l_batch_id);
810 gme_debug.put_line ( g_pkg_name
811 || '.'
812 || l_api_name
813 || ':'
814 || 'material_detail_id:'
815 || l_material_detail_id);
816 END IF;
817
818 IF l_batch_id IS NOT NULL THEN
819 DELETE FROM gme_pending_product_lots
820 WHERE batch_id = l_batch_id;
821 ELSIF l_material_detail_id IS NOT NULL THEN
822 DELETE FROM gme_pending_product_lots
823 WHERE material_detail_id = l_material_detail_id;
824 ELSE
825 DELETE FROM gme_pending_product_lots
826 WHERE batch_id = l_batch_id
827 AND material_detail_id = l_material_detail_id;
828 END IF;
829 EXCEPTION
830 WHEN fnd_api.g_exc_error THEN
831 x_return_status := fnd_api.g_ret_sts_error;
832 WHEN fnd_api.g_exc_unexpected_error THEN
833 x_return_status := fnd_api.g_ret_sts_unexp_error;
834 WHEN OTHERS THEN
835 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
836 x_return_status := fnd_api.g_ret_sts_unexp_error;
837
838 IF g_debug <= gme_debug.g_log_unexpected THEN
839 gme_debug.put_line ( 'When others exception in '
840 || g_pkg_name
841 || '.'
842 || l_api_name
843 || ' Error is '
844 || SQLERRM);
845 END IF;
846 END delete_pending_lots;
847 END gme_cancel_batch_pvt;