[Home] [Help]
PACKAGE BODY: APPS.GME_CANCEL_BATCH_PVT
Source
1 PACKAGE BODY gme_cancel_batch_pvt AS
2 /* $Header: GMEVCCBB.pls 120.6 2006/08/01 15:10:36 nsinghi noship $ */
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 REM **********************************************************************
18 */
19
20 /*================================================================================
21 Procedure
22 Cancel_Batch
23 Description
24 This particular procedure call cancel the batch.
25 Parameters
26 p_batch_header_rec The batch header row to identify the batch
27 p_validation_level Errors to skip before returning - Default 100
28 x_batch_header_rec The batch header row to identify the batch
29 x_message_count The number of messages in the message stack
30 x_message_list message stack where the api writes its messages
31 x_return_status outcome of the API call
32 S - Success
33 E - Error
34 U - Unexpected error
35 ================================================================================*/
36 PROCEDURE cancel_batch (
37 p_batch_header_rec IN gme_batch_header%ROWTYPE
38 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
39 ,x_return_status OUT NOCOPY VARCHAR2)
40 IS
41 CURSOR cur_get_steps (v_batch_id NUMBER)
42 IS
43 SELECT *
44 FROM gme_batch_steps
45 WHERE batch_id = v_batch_id
46 ORDER BY batchstep_id;
47
48 /* Miscellaneous */
49 l_batch_step_rec gme_batch_steps%ROWTYPE;
50 l_in_batch_step_rec gme_batch_steps%ROWTYPE;
51 l_material_details_rec gme_material_details%ROWTYPE;
52 l_material_details_tab gme_common_pvt.material_details_tab;
53 l_batch_step_tab gme_common_pvt.steps_tab;
54 l_batch_header_rec gme_batch_header%ROWTYPE;
55 l_ph_batch_header_rec gme_batch_header%ROWTYPE;
56 l_rsrc_trans_count NUMBER;
57 l_message_count NUMBER; -- 4944024
58 l_message_data VARCHAR2(2000); -- 4944024
59 l_return_status VARCHAR2 (1);
60 l_api_name CONSTANT VARCHAR2 (30) := 'Cancel Batch';
61 batch_header_upd_err EXCEPTION;
62 batch_step_cancel_error EXCEPTION;
63 purge_exception_err EXCEPTION;
64 batch_step_fetch_err EXCEPTION;
65 batch_hist_insert_err EXCEPTION;
66 phantom_batch_cancel_error EXCEPTION;
67 material_details_fetch_err EXCEPTION;
68 batch_header_fetch_failure EXCEPTION;
69 reservation_delete_err EXCEPTION; -- 4944024
70 BEGIN
71 IF (g_debug <= gme_debug.g_log_procedure) THEN
72 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
73 || 'Entering');
74 END IF;
75
76 /* Set the success staus to success inititally*/
77 x_return_status := fnd_api.g_ret_sts_success;
78 x_batch_header_rec := p_batch_header_rec;
79 -- Now Examine the ingredient material rows :
80 l_material_details_rec.batch_id := x_batch_header_rec.batch_id;
81
82 IF (g_debug <= gme_debug.g_log_procedure) THEN
83 gme_debug.put_line ( g_pkg_name
84 || '.'
85 || l_api_name
86 || ':'
87 || 'Call fetch material tab');
88 END IF;
89
90 IF NOT (gme_material_details_dbl.fetch_tab
91 (p_material_detail => l_material_details_rec
92 ,x_material_detail => l_material_details_tab) ) THEN
93 RAISE material_details_fetch_err;
94 END IF;
95
96 FOR i IN 1 .. l_material_details_tab.COUNT LOOP
97 -- 4944024 BEGIN
98 -- Need to delete any reservations agains PROD supply
99 -- ===================================================
100 IF l_material_details_tab(i).line_type <> -1 THEN
101 IF (g_debug <= gme_debug.g_log_procedure) THEN
102 gme_debug.put_line ( g_pkg_name
103 || '.'
104 || l_api_name
105 || ':'
106 || ' Call delete_prod_supply_resv');
107 END IF;
108
109 gme_supply_res_pvt.delete_prod_supply_resv (
110 p_matl_dtl_rec => l_material_details_tab(i)
111 ,x_msg_count => l_message_count
112 ,x_msg_data => l_message_data
113 ,x_return_status => x_return_status);
114
115 IF (g_debug <= gme_debug.g_log_procedure) THEN
116 gme_debug.put_line ( g_pkg_name
117 || '.'
118 || l_api_name
119 || ':'
120 || ' Return from delete_prod_supply_resv indicates '
121 || x_return_status);
122 END IF;
123 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
124 RAISE reservation_delete_err;
125 END IF;
126 END IF;
127 -- 4944024 END
128
129 IF (g_debug <= gme_debug.g_log_procedure) THEN
130 gme_debug.put_line ( g_pkg_name
131 || '.'
132 || l_api_name
133 || ':'
134 || 'phantom_id='
135 || l_material_details_tab (i).phantom_id);
136 END IF;
137 -- IF PHANTOM_ID is not null,
138 -- THEN recurse from the start for each one.
139 IF (NVL (l_material_details_tab (i).phantom_id, 0) <> 0) THEN
140 IF (g_debug <= gme_debug.g_log_procedure) THEN
141 gme_debug.put_line ( g_pkg_name
142 || '.'
143 || l_api_name
144 || ':'
145 || 'calling cancel batch for phantom_id='
146 || l_material_details_tab (i).phantom_id);
147 END IF;
148
149 -- Recursive call to the private API.
150 l_batch_header_rec.batch_id :=
151 l_material_details_tab (i).phantom_id;
152 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header_rec
153 ,l_ph_batch_header_rec) ) THEN
154 RAISE batch_header_fetch_failure;
155 END IF;
156
157 gme_cancel_batch_pvt.cancel_batch
158 (p_batch_header_rec => l_ph_batch_header_rec
159 ,x_batch_header_rec => l_batch_header_rec
160 ,x_return_status => l_return_status);
161
162 IF l_return_status <> fnd_api.g_ret_sts_success THEN
163 RAISE phantom_batch_cancel_error;
164 END IF;
165 END IF;
166 END LOOP;
167
168 -- Delete the Inventory transaction under specific conditions
169 IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
170 IF (g_debug <= gme_debug.g_log_procedure) THEN
171 gme_debug.put_line ( g_pkg_name
172 || '.'
173 || l_api_name
174 || ':'
175 || 'calling purge exceptions for batch_id='
176 || x_batch_header_rec.batch_id);
177 END IF;
178
179 -- delete all move-orders including invisble move orders
180 purge_batch_exceptions (p_batch_header_rec => x_batch_header_rec
181 ,p_delete_invis_mo => 'T'
182 ,p_delete_reservations => 'T'
183 ,p_delete_trans_pairs => 'T'
184 ,x_return_status => l_return_status);
185
186 IF l_return_status <> fnd_api.g_ret_sts_success THEN
187 RAISE purge_exception_err;
188 END IF;
189 END IF;
190
191 -- Now Examine the batch step(POC data) :
192 IF (g_debug <= gme_debug.g_log_procedure) THEN
193 gme_debug.put_line ( g_pkg_name
194 || '.'
195 || l_api_name
196 || ':'
197 || 'Check routing_id/poc_ind='
198 || x_batch_header_rec.routing_id
199 || '/'
200 || x_batch_header_rec.poc_ind);
201 END IF;
202
203 IF x_batch_header_rec.poc_ind = 'Y' THEN
204 /* Now update the batch step status to Cancel (5) */
205 -- Get batch_step_ids bulk collected for batch
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 || 'routing exists');
212 END IF;
213
214 OPEN cur_get_steps (x_batch_header_rec.batch_id);
215
216 FETCH cur_get_steps
217 BULK COLLECT INTO l_batch_step_tab;
218
219 CLOSE cur_get_steps;
220
221 FOR i IN 1 .. l_batch_step_tab.COUNT LOOP
222 IF (g_debug <= gme_debug.g_log_procedure) THEN
223 gme_debug.put_line ( g_pkg_name
224 || '.'
225 || l_api_name
226 || ':'
227 || 'calling cancel step for step_id='
228 || l_batch_step_tab (i).batchstep_id);
229 END IF;
230
231 /* Call Close step api to cancel the all steps */
232 -- This Proc should be written by someone else.
233 l_batch_step_rec := l_batch_step_tab (i);
234 l_in_batch_step_rec := l_batch_step_rec;
235 gme_cancel_step_pvt.cancel_step
236 (p_batch_step_rec => l_in_batch_step_rec
237 ,p_update_inventory_ind => x_batch_header_rec.update_inventory_ind
238 ,x_return_status => l_return_status
239 ,x_batch_step_rec => l_batch_step_rec);
240
241 IF l_return_status <> fnd_api.g_ret_sts_success THEN
242 RAISE batch_step_cancel_error;
243 END IF;
244 END LOOP;
245 END IF;
246
247 /* Set up the fields in output structure. */
248 x_batch_header_rec.batch_status := -1;
249
250 /* Update the batch status to the database */
251 IF (g_debug <= gme_debug.g_log_procedure) THEN
252 gme_debug.put_line ( g_pkg_name
253 || '.'
254 || l_api_name
255 || ':'
256 || 'Call batch UPDATE_ROW'
257 || x_batch_header_rec.batch_status);
258 END IF;
259
260 IF NOT (gme_batch_header_dbl.update_row (x_batch_header_rec) ) THEN
261 RAISE batch_header_upd_err;
262 END IF;
263
264 IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
265 -- Insert the event into the batch history table
266 IF NOT gme_common_pvt.create_history (x_batch_header_rec, -1) THEN
267 IF (g_debug <= gme_debug.g_log_procedure) THEN
268 gme_debug.put_line ( g_pkg_name
269 || '.'
270 || l_api_name
271 || ':'
272 || 'create history');
273 END IF;
274
275 RAISE batch_hist_insert_err;
276 END IF;
277 END IF;
278
279 /* Update the row who columns */
280 x_batch_header_rec.last_update_date := gme_common_pvt.g_timestamp;
281 x_batch_header_rec.last_updated_by := gme_common_pvt.g_user_ident;
282 x_batch_header_rec.last_update_login := gme_common_pvt.g_login_id;
283
284 IF (g_debug <= gme_debug.g_log_procedure) THEN
285 gme_debug.put_line ( g_pkg_name
286 || '.'
287 || l_api_name
288 || ':'
289 || 'Exiating cancel batch with status'
290 || x_return_status);
291 END IF;
292 EXCEPTION
293 WHEN purge_exception_err THEN
294 IF (g_debug <= gme_debug.g_log_procedure) THEN
295 gme_debug.put_line ( g_pkg_name
296 || '.'
297 || l_api_name
298 || ':'
299 || 'purge_exception_err');
300 END IF;
301
302 x_return_status := l_return_status;
303 WHEN material_details_fetch_err THEN
304 IF (g_debug <= gme_debug.g_log_procedure) THEN
305 gme_debug.put_line ( g_pkg_name
306 || '.'
307 || l_api_name
308 || ':'
309 || 'MATERIAL_FETCH_ERROR');
310 END IF;
311
312 x_return_status := fnd_api.g_ret_sts_error;
313 WHEN batch_header_fetch_failure THEN
314 IF (g_debug <= gme_debug.g_log_procedure) THEN
315 gme_debug.put_line ( g_pkg_name
316 || '.'
317 || l_api_name
318 || ':'
319 || 'BATCH_FETCH_ERROR');
320 END IF;
321
322 x_return_status := fnd_api.g_ret_sts_error;
323 WHEN phantom_batch_cancel_error THEN
324 IF (g_debug <= gme_debug.g_log_procedure) THEN
325 gme_debug.put_line ( g_pkg_name
326 || '.'
327 || l_api_name
328 || ':'
329 || 'PHANTOM_BATCH_CANCEL_ERROR');
330 END IF;
331
332 x_return_status := l_return_status;
333 WHEN batch_header_upd_err THEN
334 IF (g_debug <= gme_debug.g_log_procedure) THEN
335 gme_debug.put_line ( g_pkg_name
336 || '.'
337 || l_api_name
338 || ':'
339 || 'BATCH_HEADER_UPD_ERR');
340 END IF;
341
342 x_return_status := fnd_api.g_ret_sts_error;
343 gme_common_pvt.log_message ('GME_API_BATCH_HEADER_UPD_ERROR');
344 WHEN batch_hist_insert_err THEN
345 IF (g_debug <= gme_debug.g_log_procedure) THEN
346 gme_debug.put_line ( g_pkg_name
347 || '.'
348 || l_api_name
349 || ':'
350 || 'BATCH_HIST_INSERT_ERR.');
351 END IF;
352
353 x_return_status := fnd_api.g_ret_sts_error;
354 WHEN batch_step_fetch_err THEN
355 IF (g_debug <= gme_debug.g_log_procedure) THEN
356 gme_debug.put_line ( g_pkg_name
357 || '.'
358 || l_api_name
359 || ':'
360 || ' BATCH_STEP_FETCH_ERR.');
361 END IF;
362
363 x_return_status := fnd_api.g_ret_sts_error;
364 WHEN batch_step_cancel_error THEN
365 IF (g_debug <= gme_debug.g_log_procedure) THEN
366 gme_debug.put_line ( g_pkg_name
367 || '.'
368 || l_api_name
369 || ':'
370 || ' BATCH_STEP_CANCEL_ERROR.');
371 END IF;
372
373 x_return_status := l_return_status;
374 -- 4944024 BEGIN
375 WHEN reservation_delete_err THEN
376 IF (g_debug <= gme_debug.g_log_procedure) THEN
377 gme_debug.put_line ( g_pkg_name
378 || '.'
379 || l_api_name
380 || ':'
381 || ' DELETE_PROD_SUPPLY_RESV ERROR');
382 END IF;
383 -- 4944024 END
384 WHEN OTHERS THEN
385 IF g_debug <= gme_debug.g_log_unexpected THEN
386 gme_debug.put_line ( 'When others exception in '
387 || g_pkg_name
388 || '.'
389 || l_api_name
390 || ' Error is '
391 || SQLERRM);
392 END IF;
393
394 x_return_status := fnd_api.g_ret_sts_unexp_error;
395 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
396 END cancel_batch;
397
398
399
400 PROCEDURE purge_batch_exceptions (
401 p_batch_header_rec IN gme_batch_header%ROWTYPE
402 ,p_delete_invis_mo IN VARCHAR2 := 'F'
403 ,p_delete_reservations IN VARCHAR2 := 'F'
404 ,p_delete_trans_pairs IN VARCHAR2 := 'F'
405 ,x_return_status OUT NOCOPY VARCHAR2)
406 IS
407
408 l_material_details_rec gme_material_details%ROWTYPE;
409 l_material_details_tab gme_common_pvt.material_details_tab;
410 l_batch_header_rec gme_batch_header%ROWTYPE;
411 l_ph_batch_header_rec gme_batch_header%ROWTYPE;
412 l_api_name CONSTANT VARCHAR2 (30) := 'purge_batch_exceptions';
413 delete_reservations_err EXCEPTION;
414 delete_move_order_err EXCEPTION;
415 delete_trans_pair_err EXCEPTION;
416 delete_pend_prod_lots_err EXCEPTION;
417 phantom_batch_purge_error EXCEPTION;
418 material_details_fetch_err EXCEPTION;
419 batch_header_fetch_failure EXCEPTION;
420 l_return_status VARCHAR2(1);
421 BEGIN
422 -- Initially let us assign the return status to success
423 x_return_status := fnd_api.g_ret_sts_success;
424
425 IF (g_debug <= gme_debug.g_log_statement) THEN
426 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
427 || 'Entering');
428 END IF;
429
430 l_batch_header_rec := p_batch_header_rec;
431
432 IF (g_debug <= gme_debug.g_log_statement) THEN
433 gme_debug.put_line ( g_pkg_name
434 || '.'
435 || l_api_name
436 || ':'
437 || 'batch_id:'
438 || l_batch_header_rec.batch_id);
439 gme_debug.put_line ( g_pkg_name
440 || '.'
441 || l_api_name
442 || ':'
443 || 'organization_id:'
444 || l_batch_header_rec.organization_id);
445 END IF;
446 l_material_details_rec.batch_id := l_batch_header_rec.batch_id;
447
448 IF (g_debug <= gme_debug.g_log_procedure) THEN
449 gme_debug.put_line ( g_pkg_name
450 || '.'
451 || l_api_name
452 || ':'
453 || 'Call fetch material tab');
454 END IF;
455
456 IF NOT (gme_material_details_dbl.fetch_tab
457 (p_material_detail => l_material_details_rec
458 ,x_material_detail => l_material_details_tab) ) THEN
459 RAISE material_details_fetch_err;
460 END IF;
461
462 -- delete move orders
463 IF (g_debug <= gme_debug.g_log_statement) THEN
464 gme_debug.put_line ( g_pkg_name
465 || '.'
466 || l_api_name
467 || ':'
468 || 'deleting move orders for batch_id:'
469 || l_batch_header_rec.batch_id);
470 END IF;
471
472 gme_move_orders_pvt.delete_batch_move_orders
473 (p_organization_id => l_batch_header_rec.organization_id
474 ,p_batch_id => l_batch_header_rec.batch_id
475 ,p_delete_invis => p_delete_invis_mo
476 ,x_return_status => l_return_status);
477
478 IF l_return_status <> fnd_api.g_ret_sts_success THEN
479 RAISE delete_move_order_err;
480 END IF;
481
482 IF p_delete_reservations = fnd_api.g_true THEN
483 -- delete all reservations
484 IF (g_debug <= gme_debug.g_log_statement) THEN
485 gme_debug.put_line ( g_pkg_name
486 || '.'
487 || l_api_name
488 || ':'
489 || 'deleting reservations for batch_id:'
490 || l_batch_header_rec.batch_id);
491 END IF;
492
493 gme_reservations_pvt.delete_batch_reservations
494 (p_organization_id => l_batch_header_rec.organization_id
495 ,p_batch_id => l_batch_header_rec.batch_id
496 ,x_return_status => l_return_status);
497
498 IF l_return_status <> fnd_api.g_ret_sts_success THEN
499 RAISE delete_reservations_err;
500 END IF;
501 END IF;
502 IF p_delete_trans_pairs = fnd_api.g_true THEN
503 IF (g_debug <= gme_debug.g_log_statement) THEN
504 gme_debug.put_line ( g_pkg_name
505 || '.'
506 || l_api_name
507 || ':'
508 || 'deleting trans pairs for batch_id:'
509 || l_batch_header_rec.batch_id);
510 END IF;
511
512 gme_transactions_pvt.purge_trans_pairs
513 (p_batch_id => l_batch_header_rec.batch_id
514 ,x_return_status => l_return_status);
515
516 IF l_return_status <> fnd_api.g_ret_sts_success THEN
517 RAISE delete_trans_pair_err;
518 END IF;
519 END IF;
520 IF (g_debug <= gme_debug.g_log_statement) THEN
521 gme_debug.put_line ( g_pkg_name
522 || '.'
523 || l_api_name
524 || ':'
525 || 'deleting pending product lots for batch_id:'
526 || l_batch_header_rec.batch_id);
527 END IF;
528
529 delete_pending_lots (p_batch_id => l_batch_header_rec.batch_id
530 ,x_return_status => l_return_status);
531
532 IF l_return_status <> fnd_api.g_ret_sts_success THEN
533 RAISE delete_pend_prod_lots_err;
534 END IF;
535
536 IF (g_debug <= gme_debug.g_log_statement) THEN
537 gme_debug.put_line ( g_pkg_name
538 || '.'
539 || l_api_name
540 || ':'
541 || 'Exiting with '
542 || x_return_status);
543 END IF;
544 EXCEPTION
545 WHEN material_details_fetch_err THEN
546 IF (g_debug <= gme_debug.g_log_procedure) THEN
547 gme_debug.put_line ( g_pkg_name
548 || '.'
549 || l_api_name
550 || ':'
551 || 'MATERIAL_FETCH_ERROR');
552 END IF;
553
554 x_return_status := fnd_api.g_ret_sts_error;
555 WHEN batch_header_fetch_failure THEN
556 IF (g_debug <= gme_debug.g_log_procedure) THEN
557 gme_debug.put_line ( g_pkg_name
558 || '.'
559 || l_api_name
560 || ':'
561 || 'BATCH_FETCH_ERROR');
562 END IF;
563
564 x_return_status := fnd_api.g_ret_sts_error;
565 WHEN phantom_batch_purge_error THEN
566 IF (g_debug <= gme_debug.g_log_procedure) THEN
567 gme_debug.put_line ( g_pkg_name
568 || '.'
569 || l_api_name
570 || ':'
571 || 'delete_move_order_err');
572 END IF;
573
574 x_return_status := l_return_status;
575 WHEN delete_move_order_err THEN
576 IF (g_debug <= gme_debug.g_log_procedure) THEN
577 gme_debug.put_line ( g_pkg_name
578 || '.'
579 || l_api_name
580 || ':'
581 || 'delete_move_order_err');
582 END IF;
583
584 x_return_status := l_return_status;
585 WHEN delete_reservations_err THEN
586 IF (g_debug <= gme_debug.g_log_procedure) THEN
587 gme_debug.put_line ( g_pkg_name
588 || '.'
589 || l_api_name
590 || ':'
591 || 'delete_reservations_err');
592 END IF;
593
594 x_return_status := l_return_status;
595 WHEN delete_trans_pair_err THEN
596 IF (g_debug <= gme_debug.g_log_procedure) THEN
597 gme_debug.put_line ( g_pkg_name
598 || '.'
599 || l_api_name
600 || ':'
601 || 'delete_trans_pair_err');
602 END IF;
603
604 x_return_status := l_return_status;
605 WHEN delete_pend_prod_lots_err THEN
606 IF (g_debug <= gme_debug.g_log_procedure) THEN
607 gme_debug.put_line ( g_pkg_name
608 || '.'
609 || l_api_name
610 || ':'
611 || 'delete_pend_prod_lots_err');
612 END IF;
613
614 x_return_status := l_return_status;
615 WHEN fnd_api.g_exc_error THEN
616 x_return_status := fnd_api.g_ret_sts_error;
617 WHEN fnd_api.g_exc_unexpected_error THEN
618 x_return_status := fnd_api.g_ret_sts_unexp_error;
619 WHEN OTHERS THEN
620 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
621 x_return_status := fnd_api.g_ret_sts_unexp_error;
622
623 IF g_debug <= gme_debug.g_log_unexpected THEN
624 gme_debug.put_line ( 'When others exception in '
625 || g_pkg_name
626 || '.'
627 || l_api_name
628 || ' Error is '
629 || SQLERRM);
630 END IF;
631
632 x_return_status := fnd_api.g_ret_sts_unexp_error;
633 END purge_batch_exceptions;
634
635 PROCEDURE delete_pending_lots (
636 p_batch_id IN NUMBER
637 ,p_material_detail_id IN NUMBER DEFAULT NULL
638 ,x_return_status OUT NOCOPY VARCHAR2)
639 IS
640 l_batch_id NUMBER;
641 l_material_detail_id NUMBER;
642 l_api_name CONSTANT VARCHAR2 (30) := 'delete_pending_lots';
643 l_return_status VARCHAR2 (1);
644 BEGIN
645 -- Initially let us assign the return status to success
646 x_return_status := fnd_api.g_ret_sts_success;
647
648 IF (g_debug <= gme_debug.g_log_statement) THEN
649 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
650 || 'Entering');
651 END IF;
652
653 IF p_batch_id IS NULL AND p_material_detail_id IS NULL THEN
654 -- may use required message
655 gme_common_pvt.log_message ('GME_INVALID_FIELD'
656 ,'FIELD'
657 ,'p_batch_id');
658 RAISE fnd_api.g_exc_error;
659 END IF;
660
661 l_batch_id := p_batch_id;
662 l_material_detail_id := p_material_detail_id;
663
664 IF (g_debug <= gme_debug.g_log_statement) THEN
665 gme_debug.put_line ( g_pkg_name
666 || '.'
667 || l_api_name
668 || ':'
669 || 'batch_id:'
670 || l_batch_id);
671 gme_debug.put_line ( g_pkg_name
672 || '.'
673 || l_api_name
674 || ':'
675 || 'material_detail_id:'
676 || l_material_detail_id);
677 END IF;
678
679 IF l_batch_id IS NOT NULL THEN
680 DELETE FROM gme_pending_product_lots
681 WHERE batch_id = l_batch_id;
682 ELSIF l_material_detail_id IS NOT NULL THEN
683 DELETE FROM gme_pending_product_lots
684 WHERE material_detail_id = l_material_detail_id;
685 ELSE
686 DELETE FROM gme_pending_product_lots
687 WHERE batch_id = l_batch_id
688 AND material_detail_id = l_material_detail_id;
689 END IF;
690 EXCEPTION
691 WHEN fnd_api.g_exc_error THEN
692 x_return_status := fnd_api.g_ret_sts_error;
693 WHEN fnd_api.g_exc_unexpected_error THEN
694 x_return_status := fnd_api.g_ret_sts_unexp_error;
695 WHEN OTHERS THEN
696 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
697 x_return_status := fnd_api.g_ret_sts_unexp_error;
698
699 IF g_debug <= gme_debug.g_log_unexpected THEN
700 gme_debug.put_line ( 'When others exception in '
701 || g_pkg_name
702 || '.'
703 || l_api_name
704 || ' Error is '
705 || SQLERRM);
706 END IF;
707 END delete_pending_lots;
708 END gme_cancel_batch_pvt;