[Home] [Help]
PACKAGE BODY: APPS.GME_UNRELEASE_BATCH_PVT
Source
1 PACKAGE BODY gme_unrelease_batch_pvt AS
2 /* $Header: GMEVURBB.pls 120.18.12010000.1 2008/07/25 10:32:21 appldev ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 /* Global Variables */
5 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_UNRELEASE_BATCH_PVT';
6
7 /*===========================================================================================
8 Procedure
9 unrelease_batch
10 Description
11 This particular procedure handles unreleasing of the batch.
12 Parameters
13 p_batch_header_rec The batch header record to unrelease
14 x_batch_header_rec The batch header out row that was unreleased
15 p_create_resv_pend_lots Indicates whether to recreate reservations/pending product lots
16
17 S - Success
18 E - Error
19 U - Unexpected error
20 History
21
22 =============================================================================================*/
23 PROCEDURE unrelease_batch (
24 p_batch_header_rec IN gme_batch_header%ROWTYPE
25 ,p_create_resv_pend_lots IN NUMBER
26 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
27 ,x_return_status OUT NOCOPY VARCHAR2)
28 IS
29 l_api_name CONSTANT VARCHAR2 (30) := 'unrelease_batch';
30
31 CURSOR cur_get_steps (v_batch_id NUMBER)
32 IS
33 SELECT *
34 FROM gme_batch_steps
35 WHERE batch_id = v_batch_id;
36
37 CURSOR cur_get_and_lock_mtls (v_batch_id NUMBER)
38 IS
39 SELECT *
40 FROM gme_material_details
41 WHERE batch_id = v_batch_id
42 FOR UPDATE OF actual_qty NOWAIT;
43
44 l_material_detail_tbl gme_common_pvt.material_details_tab;
45 l_material_detail_rec gme_material_details%ROWTYPE;
46 l_batch_step_tbl gme_common_pvt.steps_tab;
47 l_batch_step_rec gme_batch_steps%ROWTYPE;
48 l_in_batch_step_rec gme_batch_steps%ROWTYPE;
49
50 error_update_row EXCEPTION;
51 error_unrelease_matl EXCEPTION;
52 error_unrelease_step EXCEPTION;
53 error_mtls_locked EXCEPTION;
54
55 -- Bug 5903208
56 l_message_count NUMBER;
57 l_message_list VARCHAR2(2000);
58 gmf_cost_failure EXCEPTION;
59
60 BEGIN
61 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
62 gme_debug.g_log_procedure THEN
63 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
64 || l_api_name);
65 gme_debug.put_line ( g_pkg_name
66 || '.'
67 || l_api_name
68 || ' unreleasing batch_id='
69 || p_batch_header_rec.batch_id);
70 END IF;
71
72 /* Set the return status to success initially */
73 x_return_status := fnd_api.g_ret_sts_success;
74
75 -- set output structure
76 x_batch_header_rec := p_batch_header_rec;
77
78 -- Fetch and lock all the material lines of the batch
79 OPEN cur_get_and_lock_mtls (x_batch_header_rec.batch_id);
80 FETCH cur_get_and_lock_mtls
81 BULK COLLECT INTO l_material_detail_tbl;
82
83 IF SQLCODE = -54
84 THEN
85 CLOSE cur_get_and_lock_mtls;
86 RAISE error_mtls_locked;
87 END IF;
88 CLOSE cur_get_and_lock_mtls;
89
90 -- set batch status
91 x_batch_header_rec.batch_status := gme_common_pvt.g_batch_pending;
92 gme_common_pvt.g_batch_status_check := fnd_api.g_false;
93 -- set actual start date to NULL...
94 x_batch_header_rec.actual_start_date := NULL;
95
96 -- Update the batch header
97 IF NOT gme_batch_header_dbl.update_row
98 (p_batch_header => x_batch_header_rec) THEN
99 RAISE error_update_row;
100 END IF;
101
102 -- Update WHO columns for output structure
103 x_batch_header_rec.last_updated_by := gme_common_pvt.g_user_ident;
104 x_batch_header_rec.last_update_date := gme_common_pvt.g_timestamp;
105 x_batch_header_rec.last_update_login := gme_common_pvt.g_login_id;
106
107 FOR i IN 1 .. l_material_detail_tbl.COUNT LOOP
108 l_material_detail_rec := l_material_detail_tbl (i);
109 unrelease_material
110 (p_material_detail_rec => l_material_detail_rec
111 ,p_update_inventory_ind => x_batch_header_rec.update_inventory_ind
112 ,p_create_resv_pend_lots => p_create_resv_pend_lots
113 ,p_from_batch => TRUE
114 ,x_return_status => x_return_status);
115
116 IF x_return_status <> fnd_api.g_ret_sts_success THEN
117 RAISE error_unrelease_matl;
118 END IF;
119 END LOOP;
120
121 -- Fetch all the steps of the batch
122 OPEN cur_get_steps (x_batch_header_rec.batch_id);
123
124 FETCH cur_get_steps
125 BULK COLLECT INTO l_batch_step_tbl;
126
127 CLOSE cur_get_steps;
128
129 -- Unrelease steps associated with the batch
130 FOR i IN 1 .. l_batch_step_tbl.COUNT LOOP
131 l_batch_step_rec := l_batch_step_tbl (i);
132 l_in_batch_step_rec := l_batch_step_rec;
133 gme_unrelease_step_pvt.unrelease_step
134 (p_batch_step_rec => l_in_batch_step_rec
135 ,p_update_inventory_ind => x_batch_header_rec.update_inventory_ind
136 ,p_create_resv_pend_lots => NULL
137 ,p_from_unrelease_batch => 1
138 ,x_batch_step_rec => l_batch_step_rec
139 ,x_return_status => x_return_status);
140
141 IF x_return_status <> fnd_api.g_ret_sts_success THEN
142 RAISE error_unrelease_step;
143 END IF;
144 END LOOP; -- FOR i IN 1 .. l_batch_step_tbl.COUNT LOOP
145
146
147 --
148 -- Bug 5903208 - GMF Call to delete batch requirements
149 --
150 GMF_VIB.Delete_Batch_Requirements
151 ( p_api_version => 1.0,
152 p_init_msg_list => FND_API.G_FALSE,
153 p_batch_id => x_batch_header_rec.batch_id,
154 x_return_status => x_return_status,
155 x_msg_count => l_message_count,
156 x_msg_data => l_message_list);
157
158 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
159 THEN
160 RAISE gmf_cost_failure;
161 END IF;
162 -- End Bug 5903208
163
164 IF NOT gme_common_pvt.create_history
165 (p_batch_header_rec => x_batch_header_rec
166 ,p_original_status => gme_common_pvt.g_batch_wip
167 ,p_event_id => gme_common_pvt.g_transaction_header_id) THEN
168 IF g_debug <= gme_debug.g_log_procedure THEN
169 gme_debug.put_line ( g_pkg_name
170 || '.'
171 || l_api_name
172 || ' create history returned error');
173 END IF;
174 END IF;
175
176 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
177 gme_debug.g_log_procedure THEN
178 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
179 END IF;
180 EXCEPTION
181 WHEN gmf_cost_failure THEN
182 -- Bug 5043868
183 x_return_status := FND_API.G_RET_STS_ERROR;
184
185 WHEN error_mtls_locked THEN
186 gme_common_pvt.log_message ('GME_API_BATCH_LINES_LOCKED');
187 x_return_status := FND_API.G_RET_STS_ERROR;
188 WHEN error_update_row THEN
189 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
190 ,SQLERRM);
191 x_return_status := fnd_api.g_ret_sts_unexp_error;
192 WHEN error_unrelease_matl OR error_unrelease_step THEN
193 NULL;
194 WHEN OTHERS THEN
195 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
196
197 IF g_debug <= gme_debug.g_log_procedure THEN
198 gme_debug.put_line ( 'Unexpected error: '
199 || g_pkg_name
200 || '.'
201 || l_api_name
202 || ': '
203 || SQLERRM);
204 END IF;
205
206 x_return_status := fnd_api.g_ret_sts_unexp_error;
207 END unrelease_batch;
208
209 PROCEDURE unrelease_material (
210 p_material_detail_rec IN gme_material_details%ROWTYPE
211 ,p_update_inventory_ind IN VARCHAR2
212 ,p_create_resv_pend_lots IN NUMBER
213 ,p_from_batch IN BOOLEAN
214 ,x_return_status OUT NOCOPY VARCHAR2)
215 IS
216 l_api_name CONSTANT VARCHAR2 (30) := 'unrelease_material';
217 l_phantom_batch_rec gme_batch_header%ROWTYPE;
218 l_in_phantom_batch_rec gme_batch_header%ROWTYPE;
219 l_item_rec mtl_system_items_b%ROWTYPE;
220 l_material_detail_rec gme_material_details%ROWTYPE;
221
222 l_exception_material_tbl gme_common_pvt.exceptions_tab;
223 l_actual_qty NUMBER;
224
225 error_unrelease_batch EXCEPTION;
226 error_fetch_batch EXCEPTION;
227 error_get_item EXCEPTION;
228 error_revert_material EXCEPTION;
229 error_update_row EXCEPTION;
230 BEGIN
231 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
232 gme_debug.g_log_procedure THEN
233 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
234 || l_api_name);
235 gme_debug.put_line ( g_pkg_name
236 || '.'
237 || l_api_name
238 || ' unrelease material material_detail_id='
239 || p_material_detail_rec.material_detail_id);
240 END IF;
241
242 -- Set the return status to success initially
243 x_return_status := fnd_api.g_ret_sts_success;
244 l_material_detail_rec := p_material_detail_rec;
245
246 IF l_material_detail_rec.phantom_id IS NOT NULL THEN
247 -- phantom -> unrelease the phantom batch
248 l_phantom_batch_rec.batch_id := l_material_detail_rec.phantom_id;
249
250 IF NOT gme_batch_header_dbl.fetch_row (l_phantom_batch_rec
251 ,l_phantom_batch_rec) THEN
252 RAISE error_fetch_batch;
253 END IF;
254
255 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
256 gme_debug.put_line
257 ( g_pkg_name
258 || '.'
259 || l_api_name
260 || ' found phantom ingredient material_detail_id='
261 || l_material_detail_rec.material_detail_id);
262 END IF;
263
264 IF l_phantom_batch_rec.batch_status = gme_common_pvt.g_batch_wip THEN
265 l_in_phantom_batch_rec := l_phantom_batch_rec;
266 unrelease_batch
267 (p_batch_header_rec => l_in_phantom_batch_rec
268 ,p_create_resv_pend_lots => p_create_resv_pend_lots
269 ,x_batch_header_rec => l_phantom_batch_rec
270 ,x_return_status => x_return_status);
271
272 IF x_return_status <> fnd_api.g_ret_sts_success THEN
273 RAISE error_unrelease_batch;
274 END IF;
275 END IF;
276 -- IF l_phantom_batch_rec.batch_status = gme_common_pvt.g_batch_wip THEN
277 ELSE
278 -- not a phantom ingredient;
279 -- phantom ingredient trxn will be deleted when phantom product is processed
280 gme_material_detail_pvt.get_item_rec
281 (p_org_id => l_material_detail_rec.organization_id
282 ,p_item_id => l_material_detail_rec.inventory_item_id
283 ,x_item_rec => l_item_rec
284 ,x_return_status => x_return_status);
285
286 IF x_return_status <> fnd_api.g_ret_sts_success THEN
287 RAISE error_get_item;
288 END IF;
289
290 IF p_update_inventory_ind = 'Y'
291 AND l_item_rec.mtl_transactions_enabled_flag = 'Y'
292 AND l_material_detail_rec.actual_qty <> 0 THEN
293 -- delete all transactions for this material
294
295 revert_material_full
296 (p_material_detail_rec => l_material_detail_rec
297 ,p_create_resv_pend_lots => p_create_resv_pend_lots
298 ,p_ignore_transactable => FALSE
299 ,x_actual_qty => l_actual_qty
300 ,x_exception_material_tbl => l_exception_material_tbl
301 ,x_return_status => x_return_status);
302
303 -- here, there's no need to look at l_actual_qty and l_exception_material_tbl
304 -- because if l_actual_qty is anything other than 0, we would get back error in
305 -- return status and raise an exception; also, l_exception_material_tbl won't
306 -- contain anything because of same reason; if the transactions can't be reversed,
307 -- it's an error here
308
309 IF x_return_status <> fnd_api.g_ret_sts_success THEN
310 RAISE error_revert_material;
311 END IF;
312 END IF; -- IF x_batch_header_rec.update_inventory_ind = 'Y' AND
313 END IF; -- IF l_material_detail_rec.phantom_id IS NOT NULL THEN
314
315 l_material_detail_rec.actual_qty := 0;
316
317 IF p_from_batch THEN
318 l_material_detail_rec.wip_plan_qty := NULL;
319 END IF;
320
321 IF NOT gme_material_details_dbl.update_row (l_material_detail_rec) THEN
322 RAISE error_update_row;
323 END IF;
324
325 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
326 gme_debug.g_log_procedure THEN
327 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
328 END IF;
329 EXCEPTION
330 WHEN error_update_row OR error_fetch_batch THEN
331 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
332 ,SQLERRM);
333 x_return_status := fnd_api.g_ret_sts_unexp_error;
334 WHEN error_get_item OR error_unrelease_batch OR error_revert_material THEN
335 NULL;
336 WHEN OTHERS THEN
337 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
338
339 IF g_debug <= gme_debug.g_log_procedure THEN
340 gme_debug.put_line ( 'Unexpected error: '
341 || g_pkg_name
342 || '.'
343 || l_api_name
344 || ': '
345 || SQLERRM);
346 END IF;
347
348 x_return_status := fnd_api.g_ret_sts_unexp_error;
349 END unrelease_material;
350
351 PROCEDURE revert_material_full (
352 p_material_detail_rec IN gme_material_details%ROWTYPE
353 ,p_create_resv_pend_lots IN NUMBER
354 ,p_ignore_transactable IN BOOLEAN DEFAULT FALSE
355 ,x_actual_qty OUT NOCOPY NUMBER
356 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
357 ,x_return_status OUT NOCOPY VARCHAR2)
358 IS
359 l_api_name CONSTANT VARCHAR2 (30) := 'revert_material_full';
360 l_mmt_tbl gme_common_pvt.mtl_mat_tran_tbl;
361 l_mmt_rec mtl_material_transactions%ROWTYPE;
362 l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
363 l_sequence NUMBER;
364 l_pplot_rec gme_pending_product_lots%ROWTYPE;
365 l_out_pplot_rec gme_pending_product_lots%ROWTYPE;
366 l_return_status VARCHAR2(1);
367
368 error_get_trans EXCEPTION;
369 error_del_trans EXCEPTION;
370 l_txn_hdr_tbl_cnt NUMBER; -- nsinghi bug#5176319
371 /* Bug 5021522 Added cursor */
372 /* Bug 5754914 Get reservable_type */
373 CURSOR Cur_item_details(v_organization_id NUMBER,
374 v_inventory_item_id NUMBER) IS
375 SELECT i.lot_control_code, i.concatenated_segments, i.inventory_item_id, i.reservable_type
376 FROM mtl_system_items_kfv i
377 WHERE i.organization_id = v_organization_id
378 AND i.inventory_item_id = v_inventory_item_id;
379 l_item_rec Cur_item_details%ROWTYPE;
380 BEGIN
381 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
382 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'|| l_api_name);
383 gme_debug.put_line (g_pkg_name|| '.'|| l_api_name|| ' material_detail_id='|| p_material_detail_rec.material_detail_id);
384 gme_debug.put_line (g_pkg_name|| '.'|| l_api_name|| ' p_create_resv_pend_lots='|| p_create_resv_pend_lots);
385 IF p_ignore_transactable THEN
386 gme_debug.put_line (g_pkg_name|| '.'|| l_api_name|| ' p_ignore_transactable IS TRUE');
387 ELSE
388 gme_debug.put_line (g_pkg_name|| '.'|| l_api_name|| ' p_ignore_transactable IS FALSE');
389 END IF;
390 END IF;
391
392 -- Set the return status to success initially
393 x_return_status := fnd_api.g_ret_sts_success;
394 gme_transactions_pvt.get_mat_trans
395 (p_mat_det_id => p_material_detail_rec.material_detail_id
396 ,p_batch_id => p_material_detail_rec.batch_id
397 ,x_mmt_tbl => l_mmt_tbl
398 ,x_return_status => x_return_status);
399
400 IF x_return_status <> fnd_api.g_ret_sts_success THEN
401 RAISE error_get_trans;
402 END IF;
403
404 x_actual_qty := p_material_detail_rec.actual_qty;
405 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
406 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name || ' actual_qty = '||x_actual_qty);
407 END IF;
408
409 FOR i IN 1 .. l_mmt_tbl.COUNT LOOP
410 l_mmt_rec := l_mmt_tbl (i);
411 /* Bug 5021522 Added logic to get item details */
412 IF (NVL(l_item_rec.inventory_item_id,0) <> l_mmt_rec.inventory_item_id) THEN
413 OPEN Cur_item_details(l_mmt_rec.organization_id, l_mmt_rec.inventory_item_id);
414 FETCH Cur_item_details INTO l_item_rec;
415 CLOSE Cur_item_details;
416 END IF;
417 /* End Bug 5021522 */
418 IF (l_item_rec.lot_control_code = 2) THEN
419 gme_transactions_pvt.get_lot_trans
420 (p_transaction_id => l_mmt_rec.transaction_id
421 ,x_mmln_tbl => l_mmln_tbl
422 ,x_return_status => x_return_status);
423 IF x_return_status <> fnd_api.g_ret_sts_success THEN
424 RAISE error_get_trans;
425 END IF;
426 END IF;
427 /* Bug 5021522 Added logic to see if inventory will go negative when opposite txn is created */
428 IF (l_mmt_rec.transaction_type_id IN (gme_common_pvt.g_ing_return, gme_common_pvt.g_prod_completion, gme_common_pvt.g_byprod_completion)) THEN
429 IF check_inv_negative(p_mmt_rec => l_mmt_rec,
430 p_mmln_tbl => l_mmln_tbl,
431 p_item_no => l_item_rec.concatenated_segments) THEN
432 RAISE fnd_api.g_exc_error;
433 END IF;
434 END IF;
435 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
436 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name||
437 ' calling gme_transactions_pvt.delete_material_txn with trxn_id= '||l_mmt_rec.transaction_id);
438 END IF;
439
440 gme_transactions_pvt.delete_material_txn
441 (p_transaction_id => l_mmt_rec.transaction_id
442 ,p_txns_pair => NULL
443 ,x_return_status => x_return_status);
444
445 IF x_return_status <> fnd_api.g_ret_sts_success THEN
446 IF x_return_status = gme_common_pvt.g_not_transactable AND p_ignore_transactable THEN
447 -- don't do anything... move to the next...
448 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
449 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name
450 || ' gme_transactions_pvt.delete_material_txn returned '||l_return_status
451 || ' but p_ignore_transactable is set to TRUE; so moving to the next transaction');
452 END IF;
453 ELSE
454 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
455 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name
456 || ' gme_transactions_pvt.delete_material_txn returned '||l_return_status
457 || ' but p_ignore_transactable is set to FALSE; raising exception');
458 END IF;
459 RAISE error_del_trans;
460 END IF;
461 ELSE -- delete was successful; recreate resv / pplot if requested
462
463 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
464 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name || ' delete loop; actual_qty = '||x_actual_qty);
465 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name || ' delete loop; trans qty fromm mmt = '||l_mmt_rec.transaction_quantity);
466 END IF;
467 -- Pawan Kumar bug 5483071 added following if condition
468 IF (p_material_detail_rec.line_type = -1 ) THEN
469 x_actual_qty := x_actual_qty + (l_mmt_rec.transaction_quantity);
470 ELSE
471 x_actual_qty := x_actual_qty - (l_mmt_rec.transaction_quantity) ;
472 END IF;
473
474 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
475 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name || 'after trxn delete loop; actual_qty = '||x_actual_qty);
476 END IF;
477
478 IF p_create_resv_pend_lots = 1 THEN
479 -- nsinghi bug#5176319. Do not already create reservation. Reservation will be created in gme_post_process after onhand is increased due to wip return.
480 /* Bug 5754914 Added reservable_type condition */
481 IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing AND l_item_rec.reservable_type = 1 THEN
482
483 l_txn_hdr_tbl_cnt := gme_common_pvt.g_mat_txn_hdr_tbl.COUNT;
484 gme_common_pvt.g_mat_txn_hdr_tbl(l_txn_hdr_tbl_cnt).txn_header_id := l_mmt_rec.transaction_id;
485 gme_common_pvt.g_mat_txn_hdr_tbl(l_txn_hdr_tbl_cnt).material_dtl_id := p_material_detail_rec.material_detail_id;
486
487 -- gme_common_pvt.g_txn_hdr_tbl_cnt := gme_common_pvt.g_txn_hdr_tbl_cnt + 1;
488
489 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
490 gme_debug.put_line ( g_pkg_name
491 || '.'
492 || l_api_name
493 || ':'
494 ||'gme_common_pvt.g_mat_txn_hdr_tbl('
495 ||l_txn_hdr_tbl_cnt||') = '
496 || l_mmt_rec.transaction_id);
497 END IF;
498 ELSE -- product or by-product
499 create_resv_pplot
500 (p_material_detail_rec => p_material_detail_rec
501 ,p_mmt_rec => l_mmt_rec
502 ,p_mmln_tbl => l_mmln_tbl
503 ,x_return_status => l_return_status);
504 END IF; -- IF l_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing
505
506 -- don't error out if the recreate fails
507 END IF;
508 END IF;
509 END LOOP; -- FOR i IN 1 .. l_mmt_tbl.COUNT LOOP
510
511 IF x_actual_qty <> 0 THEN
512 -- create batch exception
513 gme_release_batch_pvt.create_batch_exception
514 (p_material_dtl_rec => p_material_detail_rec
515 ,p_pending_move_order_ind => NULL
516 ,p_pending_rsrv_ind => NULL
517 ,p_transacted_qty => p_material_detail_rec.actual_qty - x_actual_qty
518 ,p_exception_qty => x_actual_qty
519 ,p_force_unconsumed => fnd_api.g_true
520 ,x_exception_material_tbl => x_exception_material_tbl
521 ,x_return_status => x_return_status);
522 END IF;
523
524 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
525 gme_debug.g_log_procedure THEN
526 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name|| ' with return_status = '||x_return_status||' and x_actual_qty='||x_actual_qty);
527 END IF;
528 EXCEPTION
529 WHEN fnd_api.g_exc_error THEN
530 x_return_status := fnd_api.g_ret_sts_error;
531 WHEN error_get_trans OR error_del_trans THEN
532 NULL;
533 WHEN OTHERS THEN
534 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
535
536 IF g_debug <= gme_debug.g_log_procedure THEN
537 gme_debug.put_line ( 'Unexpected error: '
538 || g_pkg_name
539 || '.'
540 || l_api_name
541 || ': '
542 || SQLERRM);
543 END IF;
544
545 x_return_status := fnd_api.g_ret_sts_unexp_error;
546 END revert_material_full;
547
548 -- nsinghi bug#5176319. Created this proc. It will create ingredient reservation during batch/step unrelease.
549 -- Bug 6997483 01-May-2008 Archana Mundhe Added parameter transaction_id.
550 -- The transaction_id will be pased by GME_transactions_PVT.gme_post_process
551 -- and is the ingredient return/reversal transaction id.
552 PROCEDURE create_matl_resv_pplot (
553 p_material_dtl_id IN NUMBER,
554 p_transaction_id IN NUMBER,
555 x_return_status OUT NOCOPY VARCHAR2)
556 IS
557 l_mat_dtl_rec gme_material_details%ROWTYPE;
558 l_mmt_rec mtl_material_transactions%ROWTYPE;
559 l_new_mmt_rec mtl_material_transactions%ROWTYPE; -- Bug 6997483
560 l_mmln_rec gme_common_pvt.mtl_trans_lots_num_tbl;
561 l_trans_hdr_id NUMBER;
562 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_MATL_RESV_PPLOT';
563
564 -- Bug 6997483
565 l_new_transaction_id NUMBER;
566 CURSOR cur_get_transaction (v_transaction_id NUMBER)
567 IS
568 SELECT *
569 FROM mtl_material_transactions mmt
570 WHERE transaction_id = v_transaction_id;
571 BEGIN
572 IF (NVL (g_debug, 0) IN (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
573 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':' || 'Entering');
574 END IF;
575
576 x_return_status := fnd_api.g_ret_sts_success;
577
578 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
579 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ':'||'p_material_dtl_id = '
580 ||p_material_dtl_id);
581 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ':'||'gme_common_pvt.g_mat_txn_hdr_tbl.COUNT = '
582 ||gme_common_pvt.g_mat_txn_hdr_tbl.COUNT);
583 END IF;
584
585 IF gme_common_pvt.g_mat_txn_hdr_tbl.COUNT > 0 THEN
586 FOR cnt IN gme_common_pvt.g_mat_txn_hdr_tbl.FIRST..gme_common_pvt.g_mat_txn_hdr_tbl.LAST
587 LOOP
588
589 IF gme_common_pvt.g_mat_txn_hdr_tbl(cnt).material_dtl_id = p_material_dtl_id THEN
590
591 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
592 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ':'||'gme_common_pvt.g_mat_txn_hdr_tbl('||cnt||').txn_header_id = '
593 ||gme_common_pvt.g_mat_txn_hdr_tbl(cnt).txn_header_id);
594 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ':'||'gme_common_pvt.g_mat_txn_hdr_tbl('||cnt||').material_dtl_id = '
595 ||gme_common_pvt.g_mat_txn_hdr_tbl(cnt).material_dtl_id);
596 END IF;
597
598 l_mat_dtl_rec.material_detail_id := gme_common_pvt.g_mat_txn_hdr_tbl(cnt).material_dtl_id;
599
600 IF NOT gme_material_details_dbl.fetch_row
601 (p_material_detail => l_mat_dtl_rec
602 ,x_material_detail => l_mat_dtl_rec) THEN
603 RAISE fnd_api.g_exc_error;
604 END IF;
605
606
607 gme_transactions_pvt.get_mmt_transactions (
608 p_transaction_id => gme_common_pvt.g_mat_txn_hdr_tbl(cnt).txn_header_id
609 ,x_mmt_rec => l_mmt_rec
610 ,x_mmln_tbl => l_mmln_rec
611 ,x_return_status => x_return_status);
612
613 IF l_mat_dtl_rec.line_type = gme_common_pvt.g_line_type_ing THEN
614 -- Bug 6997483
615 -- Get the mmt details for the ing return/reversal transaction.
616 l_new_transaction_id := p_transaction_id;
617 OPEN cur_get_transaction (l_new_transaction_id);
618 FETCH cur_get_transaction INTO l_new_mmt_rec;
619 CLOSE cur_get_transaction;
620
621 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
622 gme_debug.put_line('Ing issue transaction id is ' || l_mmt_rec.transaction_id);
623 gme_debug.put_line('Ing Return transaction id is ' || l_new_mmt_rec.transaction_id);
624 gme_debug.put_line('source line id is' || l_new_mmt_rec.source_line_id);
625 END IF;
626 -- Bug 6997483
627 -- Call create_resv_pplot only of the source_line_id of the ing return/reversal transaction
628 -- matches the transaction_id of the original ing issue transaction.
629 -- This will avoid creating multiple reservations during unrelease.
630
631 IF l_mmt_rec.transaction_id = l_new_mmt_rec.source_line_id THEN
632 create_resv_pplot (
633 p_material_detail_rec => l_mat_dtl_rec
634 ,p_mmt_rec => l_mmt_rec
635 ,p_mmln_tbl => l_mmln_rec
636 ,x_return_status => x_return_status);
637 END IF;
638 END IF; -- IF l_mat_dtl_rec.line_type = gme_common_pvt.g_line_type_ing THEN
639 END IF;
640 END LOOP;
641 END IF;
642 END create_matl_resv_pplot;
643
644 PROCEDURE create_resv_pplot (
645 p_material_detail_rec IN gme_material_details%ROWTYPE
646 ,p_mmt_rec IN mtl_material_transactions%ROWTYPE
647 ,p_mmln_tbl IN gme_common_pvt.mtl_trans_lots_num_tbl
648 ,x_return_status OUT NOCOPY VARCHAR2)
649 IS
650 l_api_name CONSTANT VARCHAR2 (30) := 'create_resv_pplot';
651
652 l_mmt_rec mtl_material_transactions%ROWTYPE;
653 l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
654 l_material_detail_rec gme_material_details%ROWTYPE;
655
656 l_sequence NUMBER;
657 l_pplot_rec gme_pending_product_lots%ROWTYPE;
658 l_out_pplot_rec gme_pending_product_lots%ROWTYPE;
659 l_return_status VARCHAR2(1);
660
661 BEGIN
662 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
663 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'|| l_api_name);
664 END IF;
665
666 -- Set the return status to success initially
667 x_return_status := fnd_api.g_ret_sts_success;
668
669 l_mmt_rec := p_mmt_rec;
670 l_mmln_tbl := p_mmln_tbl;
671 l_material_detail_rec := p_material_detail_rec;
672
673 IF l_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
674 l_material_detail_rec.revision := l_mmt_rec.revision;
675 -- following loop will execute if this is lot control item
676 FOR j in 1 .. l_mmln_tbl.count LOOP
677 -- put following if condition in for negative IB, could be passing 0 quantity lots;
678 IF l_mmln_tbl (j).transaction_quantity <> 0 THEN
679 gme_reservations_pvt.create_material_reservation
680 (p_matl_dtl_rec => l_material_detail_rec
681 ,p_resv_qty => ABS(l_mmln_tbl (j).transaction_quantity)
682 ,p_sec_resv_qty => ABS(l_mmln_tbl (j).secondary_transaction_quantity)
683 ,p_resv_um => l_mmt_rec.transaction_uom
684 ,p_subinventory => l_mmt_rec.subinventory_code
685 ,p_locator_id => l_mmt_rec.locator_id
686 ,p_lot_number => l_mmln_tbl (j).lot_number
687 ,x_return_status => l_return_status);
688 IF l_return_status <> fnd_api.g_ret_sts_success THEN
689 x_return_status := l_return_status;
690 -- don't error out if the reservation was not created... just set the return status
691 END IF;
692 END IF;
693 END LOOP;
694 -- following if block will execute if not lot control
695 IF l_mmln_tbl.count = 0 THEN
696 gme_reservations_pvt.create_material_reservation
697 (p_matl_dtl_rec => l_material_detail_rec
698 ,p_resv_qty => ABS(l_mmt_rec.transaction_quantity)
699 ,p_sec_resv_qty => ABS(l_mmt_rec.secondary_transaction_quantity)
700 ,p_resv_um => l_mmt_rec.transaction_uom
701 ,p_subinventory => l_mmt_rec.subinventory_code
702 ,p_locator_id => l_mmt_rec.locator_id
703 ,p_lot_number => NULL
704 ,x_return_status => l_return_status);
705 IF l_return_status <> fnd_api.g_ret_sts_success THEN
706 x_return_status := l_return_status;
707 -- don't error out if the reservation was not created... just set the return status
708 END IF;
709 END IF;
710 ELSE -- product or by-product
711 -- only need to recreate if this is lot control; not lot control; nothing to recreate
712 -- also, if the transaction was in a different subinventory (then that on the material),
713 -- that information will be lost, because the lots are being recreated, and when the
714 -- transaction is constructed with these recreated pending product lots, the subinventory
715 -- on the material will be used. (since pplots does not carry subinv)
716 FOR j in 1 .. l_mmln_tbl.count LOOP
717 l_pplot_rec.batch_id := l_material_detail_rec.batch_id;
718 l_pplot_rec.material_detail_id := l_material_detail_rec.material_detail_id;
719 -- don't pass sequence... let it be assigned
720 l_pplot_rec.revision := l_mmt_rec.revision;
721 l_pplot_rec.quantity := ABS(l_mmln_tbl (j).transaction_quantity);
722 l_pplot_rec.secondary_quantity := l_mmln_tbl (j).secondary_transaction_quantity;
723 l_pplot_rec.reason_id := l_mmln_tbl (j).reason_id;
724 l_pplot_rec.lot_number := l_mmln_tbl (j).lot_number;
725
726 l_sequence := gme_pending_product_lots_pvt.get_last_sequence
727 (p_matl_dtl_id => l_pplot_rec.material_detail_id
728 ,x_return_status => l_return_status);
729 IF NVL (g_debug, -1) <= gme_debug.g_log_statement THEN
730 gme_debug.put_line (g_pkg_name || '.' || l_api_name||' return_status from lot_qty '|| l_mmln_tbl (j).transaction_quantity);
731 gme_debug.put_line (g_pkg_name || '.' || l_api_name||' return_status from get_sequence '||l_return_status);
732 END IF;
733 l_sequence := l_sequence + gme_pending_product_lots_pvt.g_sequence_increment;
734 l_pplot_rec.sequence := l_sequence;
735
736 gme_pending_product_lots_pvt.create_pending_product_lot
737 (p_pending_product_lots_rec => l_pplot_rec
738 ,x_pending_product_lots_rec => l_out_pplot_rec
739 ,x_return_status => l_return_status);
740 IF l_return_status <> fnd_api.g_ret_sts_success THEN
741 x_return_status := l_return_status;
742 -- don't error out if the reservation was not created... just set the return status
743 END IF;
744 END LOOP;
745 END IF; -- IF l_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing
746
747 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
748 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name|| ' with return_status='||x_return_status);
749 END IF;
750
751 EXCEPTION
752 WHEN OTHERS THEN
753 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
754
755 IF g_debug <= gme_debug.g_log_procedure THEN
756 gme_debug.put_line ( 'Unexpected error: '
757 || g_pkg_name
758 || '.'
759 || l_api_name
760 || ': '
761 || SQLERRM);
762 END IF;
763
764 x_return_status := fnd_api.g_ret_sts_unexp_error;
765 END create_resv_pplot;
766
767 PROCEDURE validate_batch_for_unrelease
768 (p_batch_hdr_rec IN gme_batch_header%ROWTYPE
769 ,x_return_status OUT NOCOPY VARCHAR2) IS
770
771 l_api_name CONSTANT VARCHAR2 (30) := 'validate_batch_for_unrelease';
772
773 CURSOR cur_is_step_status_valid (v_batch_id NUMBER) IS
774 SELECT count(1)
775 FROM gme_batch_steps
776 WHERE step_status NOT IN (gme_common_pvt.g_step_pending, gme_common_pvt.g_step_wip)
777 AND batch_id = v_batch_id;
778
779 l_is_step_status_valid NUMBER;
780
781 error_batch_type EXCEPTION;
782 error_batch_status EXCEPTION;
783 error_step_status EXCEPTION;
784 error_phantom EXCEPTION;
785
786 BEGIN
787 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
788 gme_debug.g_log_procedure THEN
789 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
790 || l_api_name);
791 END IF;
792
793 IF p_batch_hdr_rec.batch_type = gme_common_pvt.g_doc_type_fpo THEN
794 RAISE error_batch_type;
795 END IF;
796
797 IF p_batch_hdr_rec.parentline_id IS NOT NULL THEN
798 RAISE error_phantom;
799 END IF;
800
801 IF p_batch_hdr_rec.batch_status <> gme_common_pvt.g_batch_wip THEN
802 RAISE error_batch_status;
803 END IF;
804
805 OPEN cur_is_step_status_valid(p_batch_hdr_rec.batch_id);
806 FETCH cur_is_step_status_valid INTO l_is_step_status_valid;
807 CLOSE cur_is_step_status_valid;
808
809 IF l_is_step_status_valid > 0 THEN
810 RAISE error_step_status;
811 END IF;
812
813 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
814 gme_debug.g_log_procedure THEN
815 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
816 END IF;
817 EXCEPTION
818 WHEN error_phantom THEN
819 gme_common_pvt.log_message ('PM_INVALID_PHANTOM_ACTION');
820 x_return_status := FND_API.G_RET_STS_ERROR;
821 WHEN error_batch_type OR error_batch_status THEN
822 gme_common_pvt.log_message('GME_API_INVALID_BATCH_UNREL');
823 x_return_status := fnd_api.g_ret_sts_error;
824 WHEN error_step_status THEN
825 gme_common_pvt.log_message('GME_API_INVALID_STEP_UNREL');
826 x_return_status := fnd_api.g_ret_sts_error;
827 WHEN OTHERS THEN
828 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
829
830 IF g_debug <= gme_debug.g_log_procedure THEN
831 gme_debug.put_line ( 'Unexpected error: '
832 || g_pkg_name
833 || '.'
834 || l_api_name
835 || ': '
836 || SQLERRM);
837 END IF;
838
839 x_return_status := fnd_api.g_ret_sts_unexp_error;
840 END validate_batch_for_unrelease;
841
842 /* Bug 5021522 added function RETURNS TRUE if inv will go negative and org control does not allow it */
843 FUNCTION check_inv_negative(p_mmt_rec IN mtl_material_transactions%ROWTYPE,
844 p_mmln_tbl IN gme_common_pvt.mtl_trans_lots_num_tbl,
845 p_org_neg_control IN NUMBER DEFAULT gme_common_pvt.g_allow_neg_inv,
846 p_item_no IN VARCHAR2) RETURN BOOLEAN IS
847 l_api_name CONSTANT VARCHAR2(30) := 'check_inv_negative';
848 l_return_status VARCHAR2(1);
849 l_msg_data VARCHAR2(2000);
850 l_msg_cnt NUMBER;
851 l_qoh NUMBER;
852 l_rqoh NUMBER;
853 l_qr NUMBER;
854 l_qs NUMBER;
855 l_att NUMBER;
856 l_atr NUMBER;
857 l_sqoh NUMBER;
858 l_srqoh NUMBER;
859 l_sqr NUMBER;
860 l_sqs NUMBER;
861 l_satt NUMBER;
862 l_satr NUMBER;
863 BEGIN
864 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
865 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'|| l_api_name);
866 END IF;
867 IF (p_mmln_tbl.COUNT > 0) THEN
868 FOR i IN 1..p_mmln_tbl.COUNT LOOP
869 gme_transactions_pvt.query_quantities(x_return_status => l_return_status,
870 x_msg_count => l_msg_cnt,
871 x_msg_data => l_msg_data,
872 p_organization_id => p_mmt_rec.organization_id,
873 p_inventory_item_id => p_mmt_rec.inventory_item_id,
874 p_tree_mode => gme_common_pvt.g_tree_transaction_mode,
875 p_grade_code => NULL,
876 p_revision => p_mmt_rec.revision,
877 p_lot_number => p_mmln_tbl(i).lot_number,
878 p_subinventory_code => p_mmt_rec.subinventory_code,
879 p_locator_id => p_mmt_rec.locator_id,
880 x_qoh => l_qoh,
881 x_rqoh => l_rqoh,
882 x_qr => l_qr,
883 x_qs => l_qs,
884 x_att => l_att,
885 x_atr => l_atr,
886 x_sqoh => l_sqoh,
887 x_srqoh => l_srqoh,
888 x_sqr => l_sqr,
889 x_sqs => l_sqs,
890 x_satt => l_satt,
891 x_satr => l_satr);
892 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
893 gme_debug.put_line('NVL(l_att,0) = '||NVL(l_att,0));
894 gme_debug.put_line('NVL(ABS(p_mmln_tbl(i).primary_quantity),0) = '||NVL(ABS(p_mmln_tbl(i).primary_quantity),0));
895 END IF;
896 IF (NVL(l_att,0) < NVL(ABS(p_mmln_tbl(i).primary_quantity),0)) THEN
897 IF (p_org_neg_control = 2) THEN --org does not allow negative inventory
898 gme_common_pvt.log_message
899 ( p_message_code => 'GME_ITEM_NEG_INVENTORY'
900 ,p_product_code => 'GME'
901 ,p_token1_name => 'ITEM_NO'
902 ,p_token1_value => p_item_no
903 );
904 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
905 gme_debug.put_line('error. onhand will be driven negative for lot '||p_mmln_tbl(i).lot_number||' which the org does not allow');
906 END IF;
907 RETURN TRUE;
908 ELSIF (p_org_neg_control = 1) --org allows negative inventory
909 AND (l_qr > 0) THEN
910 gme_common_pvt.log_message
911 ( p_message_code => 'GME_NEG_INV_WHEN_RSRVTNS_EXIST'
912 ,p_product_code => 'GME'
913 ,p_token1_name => 'ITEM_NO'
914 ,p_token1_value => p_item_no
915 );
916 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
917 gme_debug.put_line('error. onhand will be driven negative for lot '||p_mmln_tbl(i).lot_number||' which org allows but there are reservations');
918 END IF;
919 RETURN TRUE;
920 END IF;
921 END IF;
922 END LOOP;
923 ELSE
924 gme_transactions_pvt.query_quantities(x_return_status => l_return_status,
925 x_msg_count => l_msg_cnt,
926 x_msg_data => l_msg_data,
927 p_organization_id => p_mmt_rec.organization_id,
928 p_inventory_item_id => p_mmt_rec.inventory_item_id,
929 p_tree_mode => gme_common_pvt.g_tree_transaction_mode,
930 p_grade_code => NULL,
931 p_revision => p_mmt_rec.revision,
932 p_lot_number => NULL,
933 p_subinventory_code => p_mmt_rec.subinventory_code,
934 p_locator_id => p_mmt_rec.locator_id,
935 x_qoh => l_qoh,
936 x_rqoh => l_rqoh,
937 x_qr => l_qr,
938 x_qs => l_qs,
939 x_att => l_att,
940 x_atr => l_atr,
941 x_sqoh => l_sqoh,
942 x_srqoh => l_srqoh,
943 x_sqr => l_sqr,
944 x_sqs => l_sqs,
945 x_satt => l_satt,
946 x_satr => l_satr);
947 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
948 gme_debug.put_line('NVL(l_att,0) = '||NVL(l_att,0));
949 gme_debug.put_line('NVL(ABS(p_mmt_rec.primary_quantity),0) = '||NVL(ABS(p_mmt_rec.primary_quantity),0));
950 END IF;
951 IF (NVL(l_att,0) < NVL(ABS(p_mmt_rec.primary_quantity),0)) THEN
952 IF (p_org_neg_control = 2) THEN --org does not allow negative inventory
953 gme_common_pvt.log_message
954 ( p_message_code => 'GME_ITEM_NEG_INVENTORY'
955 ,p_product_code => 'GME'
956 ,p_token1_name => 'ITEM_NO'
957 ,p_token1_value => p_item_no
958 );
959 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
960 gme_debug.put_line('error. onhand will be driven negative which the org does not allow');
961 END IF;
962 RETURN TRUE;
963 ELSIF (p_org_neg_control = 1) --org allows negative inventory
964 AND (l_qr > 0) THEN
965 gme_common_pvt.log_message
966 ( p_message_code => 'GME_NEG_INV_WHEN_RSRVTNS_EXIST'
967 ,p_product_code => 'GME'
968 ,p_token1_name => 'ITEM_NO'
969 ,p_token1_value => p_item_no
970 );
971 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
972 gme_debug.put_line('error. onhand will be driven negative which org allows but there are reservations');
973 END IF;
974 RETURN TRUE;
975 END IF;
976 END IF;
977 END IF;
978 RETURN FALSE;
979 EXCEPTION
980 WHEN OTHERS THEN
981 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
982 IF g_debug <= gme_debug.g_log_procedure THEN
983 gme_debug.put_line('Unexpected error: '|| g_pkg_name|| '.'|| l_api_name|| ': '|| SQLERRM);
984 END IF;
985 RETURN FALSE;
986 END check_inv_negative;
987
988 END gme_unrelease_batch_pvt;