[Home] [Help]
PACKAGE BODY: APPS.GME_UNRELEASE_BATCH_PVT
Source
1 PACKAGE BODY gme_unrelease_batch_pvt AS
2 /* $Header: GMEVURBB.pls 120.23.12020000.2 2012/07/26 15:47:58 gmurator 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
134 -- 8672422 - We need to refetch step here and make sure it's not already processed
135 -- as unrelease step also processes dependent steps.
136 IF (gme_batch_steps_dbl.fetch_row (l_batch_step_rec, l_in_batch_step_rec)) THEN
137 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
138 gme_debug.put_line ('In DB date is '||TO_CHAR(l_in_batch_step_rec.last_update_date, 'DD-MON-YYYY HH24:MI:SS'));
139 gme_debug.put_line ('In step status is '||l_in_batch_step_rec.step_status);
140 END IF;
141 --IF l_in_batch_step_rec.step_status = 1 THEN
142 -- If the step is already pending go to next step.
143 --continue;
144 --END IF;
145 END IF;
146
147 gme_unrelease_step_pvt.unrelease_step
148 (p_batch_step_rec => l_in_batch_step_rec
149 ,p_update_inventory_ind => x_batch_header_rec.update_inventory_ind
150 ,p_create_resv_pend_lots => NULL
151 ,p_from_unrelease_batch => 1
152 ,x_batch_step_rec => l_batch_step_rec
153 ,x_return_status => x_return_status);
154
155 IF x_return_status <> fnd_api.g_ret_sts_success THEN
156 RAISE error_unrelease_step;
157 END IF;
158 END LOOP; -- FOR i IN 1 .. l_batch_step_tbl.COUNT LOOP
159
160
161 --
162 -- Bug 5903208 - GMF Call to delete batch requirements
163 --
164 GMF_VIB.Delete_Batch_Requirements
165 ( p_api_version => 1.0,
166 p_init_msg_list => FND_API.G_FALSE,
167 p_batch_id => x_batch_header_rec.batch_id,
168 x_return_status => x_return_status,
169 x_msg_count => l_message_count,
170 x_msg_data => l_message_list);
171
172 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
173 THEN
174 RAISE gmf_cost_failure;
175 END IF;
176 -- End Bug 5903208
177
178 -- Bug 11846735 - Do not pass in a value for event_id.
179 IF NOT gme_common_pvt.create_history
180 (p_batch_header_rec => x_batch_header_rec
181 ,p_original_status => gme_common_pvt.g_batch_wip) THEN
182 -- ,p_event_id => gme_common_pvt.g_transaction_header_id) THEN
183 IF g_debug <= gme_debug.g_log_procedure THEN
184 gme_debug.put_line ( g_pkg_name
185 || '.'
186 || l_api_name
187 || ' create history returned error');
188 END IF;
189 END IF;
190
191 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
192 gme_debug.g_log_procedure THEN
193 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
194 END IF;
195 EXCEPTION
196 WHEN gmf_cost_failure THEN
197 -- Bug 5043868
198 x_return_status := FND_API.G_RET_STS_ERROR;
199
200 WHEN error_mtls_locked THEN
201 gme_common_pvt.log_message ('GME_API_BATCH_LINES_LOCKED');
202 x_return_status := FND_API.G_RET_STS_ERROR;
203 WHEN error_update_row THEN
204 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
205 ,SQLERRM);
206 x_return_status := fnd_api.g_ret_sts_unexp_error;
207 WHEN error_unrelease_matl OR error_unrelease_step THEN
208 NULL;
209 WHEN OTHERS THEN
210 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
211
212 IF g_debug <= gme_debug.g_log_procedure THEN
213 gme_debug.put_line ( 'Unexpected error: '
214 || g_pkg_name
215 || '.'
216 || l_api_name
217 || ': '
218 || SQLERRM);
219 END IF;
220
221 x_return_status := fnd_api.g_ret_sts_unexp_error;
222 END unrelease_batch;
223
224 PROCEDURE unrelease_material (
225 p_material_detail_rec IN gme_material_details%ROWTYPE
226 ,p_update_inventory_ind IN VARCHAR2
227 ,p_create_resv_pend_lots IN NUMBER
228 ,p_from_batch IN BOOLEAN
229 ,x_return_status OUT NOCOPY VARCHAR2)
230 IS
231 l_api_name CONSTANT VARCHAR2 (30) := 'unrelease_material';
232 l_phantom_batch_rec gme_batch_header%ROWTYPE;
233 l_in_phantom_batch_rec gme_batch_header%ROWTYPE;
234 l_item_rec mtl_system_items_b%ROWTYPE;
235 l_material_detail_rec gme_material_details%ROWTYPE;
236
237 l_exception_material_tbl gme_common_pvt.exceptions_tab;
238 l_actual_qty NUMBER;
239
240 error_unrelease_batch EXCEPTION;
241 error_fetch_batch EXCEPTION;
242 error_get_item EXCEPTION;
243 error_revert_material EXCEPTION;
244 error_update_row EXCEPTION;
245 BEGIN
246 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
247 gme_debug.g_log_procedure THEN
248 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
249 || l_api_name);
250 gme_debug.put_line ( g_pkg_name
251 || '.'
252 || l_api_name
253 || ' unrelease material material_detail_id='
254 || p_material_detail_rec.material_detail_id);
255 END IF;
256
257 -- Set the return status to success initially
258 x_return_status := fnd_api.g_ret_sts_success;
259 l_material_detail_rec := p_material_detail_rec;
260
261 IF l_material_detail_rec.phantom_id IS NOT NULL THEN
262 -- phantom -> unrelease the phantom batch
263 l_phantom_batch_rec.batch_id := l_material_detail_rec.phantom_id;
264
265 IF NOT gme_batch_header_dbl.fetch_row (l_phantom_batch_rec
266 ,l_phantom_batch_rec) THEN
267 RAISE error_fetch_batch;
268 END IF;
269
270 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
271 gme_debug.put_line
272 ( g_pkg_name
273 || '.'
274 || l_api_name
275 || ' found phantom ingredient material_detail_id='
276 || l_material_detail_rec.material_detail_id);
277 END IF;
278
279 IF l_phantom_batch_rec.batch_status = gme_common_pvt.g_batch_wip THEN
280 l_in_phantom_batch_rec := l_phantom_batch_rec;
281 unrelease_batch
282 (p_batch_header_rec => l_in_phantom_batch_rec
283 ,p_create_resv_pend_lots => p_create_resv_pend_lots
284 ,x_batch_header_rec => l_phantom_batch_rec
285 ,x_return_status => x_return_status);
286
287 IF x_return_status <> fnd_api.g_ret_sts_success THEN
288 RAISE error_unrelease_batch;
289 END IF;
290 END IF;
291 -- IF l_phantom_batch_rec.batch_status = gme_common_pvt.g_batch_wip THEN
292 ELSE
293 -- not a phantom ingredient;
294 -- phantom ingredient trxn will be deleted when phantom product is processed
295 gme_material_detail_pvt.get_item_rec
296 (p_org_id => l_material_detail_rec.organization_id
297 ,p_item_id => l_material_detail_rec.inventory_item_id
298 ,x_item_rec => l_item_rec
299 ,x_return_status => x_return_status);
300
301 IF x_return_status <> fnd_api.g_ret_sts_success THEN
302 RAISE error_get_item;
303 END IF;
304
305 IF p_update_inventory_ind = 'Y'
306 AND l_item_rec.mtl_transactions_enabled_flag = 'Y'
307 AND l_material_detail_rec.actual_qty <> 0 THEN
308 -- delete all transactions for this material
309
310 revert_material_full
311 (p_material_detail_rec => l_material_detail_rec
312 ,p_create_resv_pend_lots => p_create_resv_pend_lots
313 ,p_ignore_transactable => FALSE
314 ,x_actual_qty => l_actual_qty
315 ,x_exception_material_tbl => l_exception_material_tbl
316 ,x_return_status => x_return_status);
317
318 -- here, there's no need to look at l_actual_qty and l_exception_material_tbl
319 -- because if l_actual_qty is anything other than 0, we would get back error in
320 -- return status and raise an exception; also, l_exception_material_tbl won't
321 -- contain anything because of same reason; if the transactions can't be reversed,
322 -- it's an error here
323
324 IF x_return_status <> fnd_api.g_ret_sts_success THEN
325 RAISE error_revert_material;
326 END IF;
327 END IF; -- IF x_batch_header_rec.update_inventory_ind = 'Y' AND
328 END IF; -- IF l_material_detail_rec.phantom_id IS NOT NULL THEN
329
330 l_material_detail_rec.actual_qty := 0;
331
332 IF p_from_batch THEN
333 l_material_detail_rec.wip_plan_qty := NULL;
334 END IF;
335
336 IF NOT gme_material_details_dbl.update_row (l_material_detail_rec) THEN
337 RAISE error_update_row;
338 END IF;
339
340 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
341 gme_debug.g_log_procedure THEN
342 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
343 END IF;
344 EXCEPTION
345 WHEN error_update_row OR error_fetch_batch THEN
346 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
347 ,SQLERRM);
348 x_return_status := fnd_api.g_ret_sts_unexp_error;
349 WHEN error_get_item OR error_unrelease_batch OR error_revert_material THEN
350 NULL;
351 WHEN OTHERS THEN
352 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
353
354 IF g_debug <= gme_debug.g_log_procedure THEN
355 gme_debug.put_line ( 'Unexpected error: '
356 || g_pkg_name
357 || '.'
358 || l_api_name
359 || ': '
360 || SQLERRM);
361 END IF;
362
363 x_return_status := fnd_api.g_ret_sts_unexp_error;
364 END unrelease_material;
365
366 PROCEDURE revert_material_full (
367 p_material_detail_rec IN gme_material_details%ROWTYPE
368 ,p_create_resv_pend_lots IN NUMBER
369 ,p_ignore_transactable IN BOOLEAN DEFAULT FALSE
370 ,x_actual_qty OUT NOCOPY NUMBER
371 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
372 ,x_return_status OUT NOCOPY VARCHAR2)
373 IS
374 l_api_name CONSTANT VARCHAR2 (30) := 'revert_material_full';
375 l_mmt_tbl gme_common_pvt.mtl_mat_tran_tbl;
376 l_mmt_rec mtl_material_transactions%ROWTYPE;
377 l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
378 l_sequence NUMBER;
379 l_pplot_rec gme_pending_product_lots%ROWTYPE;
380 l_out_pplot_rec gme_pending_product_lots%ROWTYPE;
381 l_return_status VARCHAR2(1);
382
383 -- Bug 13017256
384 l_trans_date DATE;
385
386 error_get_trans EXCEPTION;
387 error_del_trans EXCEPTION;
388 l_txn_hdr_tbl_cnt NUMBER; -- nsinghi bug#5176319
389 /* Bug 5021522 Added cursor */
390 /* Bug 5754914 Get reservable_type */
391 CURSOR Cur_item_details(v_organization_id NUMBER,
392 v_inventory_item_id NUMBER) IS
393 SELECT i.lot_control_code, i.concatenated_segments, i.inventory_item_id, i.reservable_type
394 FROM mtl_system_items_kfv i
395 WHERE i.organization_id = v_organization_id
396 AND i.inventory_item_id = v_inventory_item_id;
397 l_item_rec Cur_item_details%ROWTYPE;
398
399 -- Bug 12836004
400 l_lpn_subinv VARCHAR2(100);
401 l_lpn_no VARCHAR2(100);
402 l_lpn_loc NUMBER;
403 l_lpn_context NUMBER;
404
405 BEGIN
406 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
407 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'|| l_api_name);
408 gme_debug.put_line (g_pkg_name|| '.'|| l_api_name|| ' material_detail_id='|| p_material_detail_rec.material_detail_id);
409 gme_debug.put_line (g_pkg_name|| '.'|| l_api_name|| ' p_create_resv_pend_lots='|| p_create_resv_pend_lots);
410 IF p_ignore_transactable THEN
411 gme_debug.put_line (g_pkg_name|| '.'|| l_api_name|| ' p_ignore_transactable IS TRUE');
412 ELSE
413 gme_debug.put_line (g_pkg_name|| '.'|| l_api_name|| ' p_ignore_transactable IS FALSE');
414 END IF;
415 END IF;
416
417 -- Set the return status to success initially
418 x_return_status := fnd_api.g_ret_sts_success;
419 gme_transactions_pvt.get_mat_trans
420 (p_mat_det_id => p_material_detail_rec.material_detail_id
421 ,p_batch_id => p_material_detail_rec.batch_id
422 ,x_mmt_tbl => l_mmt_tbl
423 ,x_return_status => x_return_status);
424
425 IF x_return_status <> fnd_api.g_ret_sts_success THEN
426 RAISE error_get_trans;
427 END IF;
428
429 -- Bug 13017256 - Let's initialize the variable with the user entered date.
430 l_trans_date := NULL;
431 IF gme_common_pvt.g_ib_timestamp_set > 0 THEN
432 l_trans_date := NVL(gme_common_pvt.g_ib_timestamp_date, gme_common_pvt.g_timestamp);
433 END IF;
434
435 x_actual_qty := p_material_detail_rec.actual_qty;
436 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
437 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name || ' actual_qty = '||x_actual_qty);
438 END IF;
439
440 FOR i IN 1 .. l_mmt_tbl.COUNT LOOP
441 l_mmt_rec := l_mmt_tbl (i);
442 /* Bug 5021522 Added logic to get item details */
443 IF (NVL(l_item_rec.inventory_item_id,0) <> l_mmt_rec.inventory_item_id) THEN
444 OPEN Cur_item_details(l_mmt_rec.organization_id, l_mmt_rec.inventory_item_id);
445 FETCH Cur_item_details INTO l_item_rec;
446 CLOSE Cur_item_details;
447 END IF;
448 /* End Bug 5021522 */
449 IF (l_item_rec.lot_control_code = 2) THEN
450 gme_transactions_pvt.get_lot_trans
451 (p_transaction_id => l_mmt_rec.transaction_id
452 ,x_mmln_tbl => l_mmln_tbl
453 ,x_return_status => x_return_status);
454 IF x_return_status <> fnd_api.g_ret_sts_success THEN
455 RAISE error_get_trans;
456 END IF;
457 END IF;
458
459 /* Bug 5021522 Added logic to see if inventory will go negative when opposite txn is created */
460 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
461 -- Bug 8607365 - No need to do negative inventory checking for phantom prods or ingredients.
462 IF (NVL(p_material_detail_rec.phantom_line_id, 0) = 0) THEN
463 IF check_inv_negative(p_mmt_rec => l_mmt_rec,
464 p_mmln_tbl => l_mmln_tbl,
465 p_item_no => l_item_rec.concatenated_segments) THEN
466 RAISE fnd_api.g_exc_error;
467 END IF;
468 END IF;
469 END IF;
470
471 /* Bug 12836004 Added logic to see if LPN would get associated with a diff locator if an opp txn is created */
472 IF (g_debug <= gme_debug.g_log_statement) THEN
473 gme_debug.put_line ( g_pkg_name
474 || '.'
475 || l_api_name
476 || ':'
477 || 'Checking the current the LPN subinv and loc for lpn :'||l_mmt_rec.lpn_id);
478 END IF;
479
480 IF (l_mmt_rec.lpn_id IS NOT NULL AND
481 l_mmt_rec.subinventory_code IS NOT NULL AND
482 GME_TRANSACTIONS_PVT.check_lpn_subinv_loc
483 (p_lpn_id => l_mmt_rec.lpn_id,
484 p_in_subinv => l_mmt_rec.subinventory_code,
485 p_in_locid => l_mmt_rec.locator_id,
486 x_out_subinv => l_lpn_subinv,
487 x_out_locId => l_lpn_loc,
488 x_out_lpnno => l_lpn_no,
489 x_context => l_lpn_context) = FALSE) THEN
490 -- The current Lpn locator is diff. WMS functionality doesnt allow an LPN to have an onhand in 2 diff locators.
491 -- So raise an error.
492 IF (g_debug <= gme_debug.g_log_statement) THEN
493 gme_debug.put_line ( g_pkg_name
494 || '.'
495 || l_api_name
496 || ':'
497 || 'Raising an error ');
498
499 gme_debug.put_line ( g_pkg_name
500 || '.'
501 || l_api_name
502 || ':'
503 || 'Original txn subinv :'
504 || l_mmt_rec.subinventory_code);
505
506 gme_debug.put_line ( g_pkg_name
507 || '.'
508 || l_api_name
509 || ':'
510 || 'Original txn Locator id :'
511 || l_mmt_rec.locator_id);
512
513 gme_debug.put_line ( g_pkg_name
514 || '.'
515 || l_api_name
516 || ':'
517 || 'Current LPN subinv : '
518 || l_lpn_subinv);
519
520 gme_debug.put_line ( g_pkg_name
521 || '.'
522 || l_api_name
523 || ':'
524 || 'Current LPN locator id '
525 || l_lpn_loc);
526 END IF;
527
528 gme_common_pvt.log_message
529 ( p_message_code => 'GME_LPN_LOC_MISMATCH'
530 ,p_product_code => 'GME'
531 ,p_token1_name => 'ITEM_NO'
532 ,p_token1_value => l_item_rec.concatenated_segments
533 ,p_token2_name => 'LPN_NO'
534 ,p_token2_value => l_lpn_no
535 );
536 RAISE fnd_api.g_exc_error;
537 END IF;
538
539 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
540 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name||
541 ' calling gme_transactions_pvt.delete_material_txn with trxn_id= '||l_mmt_rec.transaction_id);
542 END IF;
543
544 -- Bug 13017256 - Let's pass in user entered date. It will only get
545 -- used if necessary. This should only be set during negative IB.
546
547 -- delete this transaction, reduce the qty to decrement
548 gme_transactions_pvt.delete_material_txn
549 (p_transaction_id => l_mmt_rec.transaction_id
550 ,p_trans_date => l_trans_date
551 ,p_txns_pair => NULL
552 ,x_return_status => x_return_status);
553
554 IF x_return_status <> fnd_api.g_ret_sts_success THEN
555 IF x_return_status = gme_common_pvt.g_not_transactable AND p_ignore_transactable THEN
556 -- don't do anything... move to the next...
557 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
558 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name
559 || ' gme_transactions_pvt.delete_material_txn returned '||l_return_status
560 || ' but p_ignore_transactable is set to TRUE; so moving to the next transaction');
561 END IF;
562 ELSE
563 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
564 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name
565 || ' gme_transactions_pvt.delete_material_txn returned '||l_return_status
566 || ' but p_ignore_transactable is set to FALSE; raising exception');
567 END IF;
568 RAISE error_del_trans;
569 END IF;
570 ELSE -- delete was successful; recreate resv / pplot if requested
571
572 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
573 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name || ' delete loop; actual_qty = '||x_actual_qty);
574 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name || ' delete loop; trans qty fromm mmt = '||l_mmt_rec.transaction_quantity);
575 END IF;
576 -- Pawan Kumar bug 5483071 added following if condition
577 IF (p_material_detail_rec.line_type = -1 ) THEN
578 x_actual_qty := x_actual_qty + (l_mmt_rec.transaction_quantity);
579 ELSE
580 x_actual_qty := x_actual_qty - (l_mmt_rec.transaction_quantity) ;
581 END IF;
582
583 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
584 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name || 'after trxn delete loop; actual_qty = '||x_actual_qty);
585 END IF;
586
587 IF p_create_resv_pend_lots = 1 THEN
588 -- nsinghi bug#5176319. Do not already create reservation. Reservation will be created in gme_post_process after onhand is increased due to wip return.
589 /* Bug 5754914 Added reservable_type condition */
590 IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing AND l_item_rec.reservable_type = 1 THEN
591
592 l_txn_hdr_tbl_cnt := gme_common_pvt.g_mat_txn_hdr_tbl.COUNT;
593 gme_common_pvt.g_mat_txn_hdr_tbl(l_txn_hdr_tbl_cnt).txn_header_id := l_mmt_rec.transaction_id;
594 gme_common_pvt.g_mat_txn_hdr_tbl(l_txn_hdr_tbl_cnt).material_dtl_id := p_material_detail_rec.material_detail_id;
595
596 -- gme_common_pvt.g_txn_hdr_tbl_cnt := gme_common_pvt.g_txn_hdr_tbl_cnt + 1;
597
598 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
599 gme_debug.put_line ( g_pkg_name
600 || '.'
601 || l_api_name
602 || ':'
603 ||'gme_common_pvt.g_mat_txn_hdr_tbl('
604 ||l_txn_hdr_tbl_cnt||') = '
605 || l_mmt_rec.transaction_id);
606 END IF;
607 ELSE -- product or by-product
608 create_resv_pplot
609 (p_material_detail_rec => p_material_detail_rec
610 ,p_mmt_rec => l_mmt_rec
611 ,p_mmln_tbl => l_mmln_tbl
612 ,x_return_status => l_return_status);
613 END IF; -- IF l_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing
614
615 -- don't error out if the recreate fails
616 END IF;
617 END IF;
618 END LOOP; -- FOR i IN 1 .. l_mmt_tbl.COUNT LOOP
619
620 IF x_actual_qty <> 0 THEN
621 -- create batch exception
622 gme_release_batch_pvt.create_batch_exception
623 (p_material_dtl_rec => p_material_detail_rec
624 ,p_pending_move_order_ind => NULL
625 ,p_pending_rsrv_ind => NULL
626 ,p_transacted_qty => p_material_detail_rec.actual_qty - x_actual_qty
627 ,p_exception_qty => x_actual_qty
628 ,p_force_unconsumed => fnd_api.g_true
629 ,x_exception_material_tbl => x_exception_material_tbl
630 ,x_return_status => x_return_status);
631 END IF;
632
633 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
634 gme_debug.g_log_procedure THEN
635 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);
636 END IF;
637 EXCEPTION
638 WHEN fnd_api.g_exc_error THEN
639 x_return_status := fnd_api.g_ret_sts_error;
640 WHEN error_get_trans OR error_del_trans THEN
641 NULL;
642 WHEN OTHERS THEN
643 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
644
645 IF g_debug <= gme_debug.g_log_procedure THEN
646 gme_debug.put_line ( 'Unexpected error: '
647 || g_pkg_name
648 || '.'
649 || l_api_name
650 || ': '
651 || SQLERRM);
652 END IF;
653
654 x_return_status := fnd_api.g_ret_sts_unexp_error;
655 END revert_material_full;
656
657 -- nsinghi bug#5176319. Created this proc. It will create ingredient reservation during batch/step unrelease.
658 -- Bug 6997483 01-May-2008 Archana Mundhe Added parameter transaction_id.
659 -- The transaction_id will be pased by GME_transactions_PVT.gme_post_process
660 -- and is the ingredient return/reversal transaction id.
661 PROCEDURE create_matl_resv_pplot (
662 p_material_dtl_id IN NUMBER,
663 p_transaction_id IN NUMBER,
664 x_return_status OUT NOCOPY VARCHAR2)
665 IS
666 l_mat_dtl_rec gme_material_details%ROWTYPE;
667 l_mmt_rec mtl_material_transactions%ROWTYPE;
668 l_new_mmt_rec mtl_material_transactions%ROWTYPE; -- Bug 6997483
669 l_mmln_rec gme_common_pvt.mtl_trans_lots_num_tbl;
670 l_trans_hdr_id NUMBER;
671 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_MATL_RESV_PPLOT';
672 -- Bug 6997483
673 l_new_transaction_id NUMBER;
674 CURSOR cur_get_transaction (v_transaction_id NUMBER)
675 IS
676 SELECT *
677 FROM mtl_material_transactions mmt
678 WHERE transaction_id = v_transaction_id;
679 BEGIN
680 IF (NVL (g_debug, 0) IN (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
681 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':' || 'Entering');
682 END IF;
683
684 x_return_status := fnd_api.g_ret_sts_success;
685
686 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
687 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ':'||'p_material_dtl_id = '
688 ||p_material_dtl_id);
689 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ':'||'gme_common_pvt.g_mat_txn_hdr_tbl.COUNT = '
690 ||gme_common_pvt.g_mat_txn_hdr_tbl.COUNT);
691 END IF;
692
693 IF gme_common_pvt.g_mat_txn_hdr_tbl.COUNT > 0 THEN
694 FOR cnt IN gme_common_pvt.g_mat_txn_hdr_tbl.FIRST..gme_common_pvt.g_mat_txn_hdr_tbl.LAST
695 LOOP
696
697 IF gme_common_pvt.g_mat_txn_hdr_tbl(cnt).material_dtl_id = p_material_dtl_id THEN
698
699 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
700 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ':'||'gme_common_pvt.g_mat_txn_hdr_tbl('||cnt||').txn_header_id = '
701 ||gme_common_pvt.g_mat_txn_hdr_tbl(cnt).txn_header_id);
702 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ':'||'gme_common_pvt.g_mat_txn_hdr_tbl('||cnt||').material_dtl_id = '
703 ||gme_common_pvt.g_mat_txn_hdr_tbl(cnt).material_dtl_id);
704 END IF;
705
706 l_mat_dtl_rec.material_detail_id := gme_common_pvt.g_mat_txn_hdr_tbl(cnt).material_dtl_id;
707
708 IF NOT gme_material_details_dbl.fetch_row
709 (p_material_detail => l_mat_dtl_rec
710 ,x_material_detail => l_mat_dtl_rec) THEN
711 RAISE fnd_api.g_exc_error;
712 END IF;
713
714 gme_transactions_pvt.get_mmt_transactions (
715 p_transaction_id => gme_common_pvt.g_mat_txn_hdr_tbl(cnt).txn_header_id
716 ,x_mmt_rec => l_mmt_rec
717 ,x_mmln_tbl => l_mmln_rec
718 ,x_return_status => x_return_status);
719
720 IF l_mat_dtl_rec.line_type = gme_common_pvt.g_line_type_ing THEN
721 -- Bug 6997483
722 -- Get the mmt details for the ing return/reversal transaction.
723 l_new_transaction_id := p_transaction_id;
724 OPEN cur_get_transaction (l_new_transaction_id);
725 FETCH cur_get_transaction INTO l_new_mmt_rec;
726 CLOSE cur_get_transaction;
727
728 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
729 gme_debug.put_line('Ing issue transaction id is ' || l_mmt_rec.transaction_id);
730 gme_debug.put_line('Ing Return transaction id is ' || l_new_mmt_rec.transaction_id);
731 gme_debug.put_line('source line id is' || l_new_mmt_rec.source_line_id);
732 END IF;
733 -- Bug 6997483
734 -- Call create_resv_pplot only of the source_line_id of the ing return/reversal transaction
735 -- matches the transaction_id of the original ing issue transaction.
736 -- This will avoid creating multiple reservations during unrelease.
737
738 IF l_mmt_rec.transaction_id = l_new_mmt_rec.source_line_id THEN
739
740 create_resv_pplot (
741 p_material_detail_rec => l_mat_dtl_rec
742 ,p_mmt_rec => l_mmt_rec
743 ,p_mmln_tbl => l_mmln_rec
744 ,x_return_status => x_return_status);
745 END IF;
746 END IF; -- IF l_mat_dtl_rec.line_type = gme_common_pvt.g_line_type_ing THEN
747 END IF;
748 END LOOP;
749 END IF;
750 END create_matl_resv_pplot;
751
752 PROCEDURE create_resv_pplot (
753 p_material_detail_rec IN gme_material_details%ROWTYPE
754 ,p_mmt_rec IN mtl_material_transactions%ROWTYPE
755 ,p_mmln_tbl IN gme_common_pvt.mtl_trans_lots_num_tbl
756 ,x_return_status OUT NOCOPY VARCHAR2)
757 IS
758 l_api_name CONSTANT VARCHAR2 (30) := 'create_resv_pplot';
759
760 l_mmt_rec mtl_material_transactions%ROWTYPE;
761 l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
762 l_material_detail_rec gme_material_details%ROWTYPE;
763
764 l_sequence NUMBER;
765 l_pplot_rec gme_pending_product_lots%ROWTYPE;
766 l_out_pplot_rec gme_pending_product_lots%ROWTYPE;
767 l_return_status VARCHAR2(1);
768
769 BEGIN
770 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
771 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'|| l_api_name);
772 END IF;
773
774 -- Set the return status to success initially
775 x_return_status := fnd_api.g_ret_sts_success;
776
777 l_mmt_rec := p_mmt_rec;
778 l_mmln_tbl := p_mmln_tbl;
779 l_material_detail_rec := p_material_detail_rec;
780
781 IF l_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
782 l_material_detail_rec.revision := l_mmt_rec.revision;
783 -- following loop will execute if this is lot control item
784 FOR j in 1 .. l_mmln_tbl.count LOOP
785 -- put following if condition in for negative IB, could be passing 0 quantity lots;
786 IF l_mmln_tbl (j).transaction_quantity <> 0 THEN
787 gme_reservations_pvt.create_material_reservation
788 (p_matl_dtl_rec => l_material_detail_rec
789 ,p_resv_qty => ABS(l_mmln_tbl (j).transaction_quantity)
790 ,p_sec_resv_qty => ABS(l_mmln_tbl (j).secondary_transaction_quantity)
791 ,p_resv_um => l_mmt_rec.transaction_uom
792 ,p_subinventory => l_mmt_rec.subinventory_code
793 ,p_locator_id => l_mmt_rec.locator_id
794 ,p_lpn_id => l_mmt_rec.lpn_id -- Bug 6437252
795 ,p_lot_number => l_mmln_tbl (j).lot_number
796 ,x_return_status => l_return_status);
797 IF l_return_status <> fnd_api.g_ret_sts_success THEN
798 x_return_status := l_return_status;
799 -- don't error out if the reservation was not created... just set the return status
800 END IF;
801 END IF;
802 END LOOP;
803 -- following if block will execute if not lot control
804 IF l_mmln_tbl.count = 0 THEN
805 gme_reservations_pvt.create_material_reservation
806 (p_matl_dtl_rec => l_material_detail_rec
807 ,p_resv_qty => ABS(l_mmt_rec.transaction_quantity)
808 ,p_sec_resv_qty => ABS(l_mmt_rec.secondary_transaction_quantity)
809 ,p_resv_um => l_mmt_rec.transaction_uom
810 ,p_subinventory => l_mmt_rec.subinventory_code
811 ,p_locator_id => l_mmt_rec.locator_id
812 ,p_lot_number => NULL
813 ,x_return_status => l_return_status);
814 IF l_return_status <> fnd_api.g_ret_sts_success THEN
815 x_return_status := l_return_status;
816 -- don't error out if the reservation was not created... just set the return status
817 END IF;
818 END IF;
819 ELSE -- product or by-product
820 -- only need to recreate if this is lot control; not lot control; nothing to recreate
821 -- also, if the transaction was in a different subinventory (then that on the material),
822 -- that information will be lost, because the lots are being recreated, and when the
823 -- transaction is constructed with these recreated pending product lots, the subinventory
824 -- on the material will be used. (since pplots does not carry subinv)
825 FOR j in 1 .. l_mmln_tbl.count LOOP
826 l_pplot_rec.batch_id := l_material_detail_rec.batch_id;
827 l_pplot_rec.material_detail_id := l_material_detail_rec.material_detail_id;
828 -- don't pass sequence... let it be assigned
829 l_pplot_rec.revision := l_mmt_rec.revision;
830 l_pplot_rec.quantity := ABS(l_mmln_tbl (j).transaction_quantity);
831 l_pplot_rec.secondary_quantity := l_mmln_tbl (j).secondary_transaction_quantity;
832 l_pplot_rec.reason_id := l_mmln_tbl (j).reason_id;
833 l_pplot_rec.lot_number := l_mmln_tbl (j).lot_number;
834
835 l_sequence := gme_pending_product_lots_pvt.get_last_sequence
836 (p_matl_dtl_id => l_pplot_rec.material_detail_id
837 ,x_return_status => l_return_status);
838 IF NVL (g_debug, -1) <= gme_debug.g_log_statement THEN
839 gme_debug.put_line (g_pkg_name || '.' || l_api_name||' return_status from lot_qty '|| l_mmln_tbl (j).transaction_quantity);
840 gme_debug.put_line (g_pkg_name || '.' || l_api_name||' return_status from get_sequence '||l_return_status);
841 END IF;
842 l_sequence := l_sequence + gme_pending_product_lots_pvt.g_sequence_increment;
843 l_pplot_rec.sequence := l_sequence;
844
845 gme_pending_product_lots_pvt.create_pending_product_lot
846 (p_pending_product_lots_rec => l_pplot_rec
847 ,x_pending_product_lots_rec => l_out_pplot_rec
848 ,x_return_status => l_return_status);
849 IF l_return_status <> fnd_api.g_ret_sts_success THEN
850 x_return_status := l_return_status;
851 -- don't error out if the reservation was not created... just set the return status
852 END IF;
853 END LOOP;
854 END IF; -- IF l_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing
855
856 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
857 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name|| ' with return_status='||x_return_status);
858 END IF;
859
860 EXCEPTION
861 WHEN OTHERS THEN
862 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
863
864 IF g_debug <= gme_debug.g_log_procedure THEN
865 gme_debug.put_line ( 'Unexpected error: '
866 || g_pkg_name
867 || '.'
868 || l_api_name
869 || ': '
870 || SQLERRM);
871 END IF;
872
873 x_return_status := fnd_api.g_ret_sts_unexp_error;
874 END create_resv_pplot;
875
876 PROCEDURE validate_batch_for_unrelease
877 (p_batch_hdr_rec IN gme_batch_header%ROWTYPE
878 ,x_return_status OUT NOCOPY VARCHAR2) IS
879
880 l_api_name CONSTANT VARCHAR2 (30) := 'validate_batch_for_unrelease';
881
882 CURSOR cur_is_step_status_valid (v_batch_id NUMBER) IS
883 SELECT count(1)
884 FROM gme_batch_steps
885 WHERE step_status NOT IN (gme_common_pvt.g_step_pending, gme_common_pvt.g_step_wip)
886 AND batch_id = v_batch_id;
887
888 l_is_step_status_valid NUMBER;
889
890 error_batch_type EXCEPTION;
891 error_batch_status EXCEPTION;
892 error_step_status EXCEPTION;
893 error_phantom EXCEPTION;
894
895 BEGIN
896 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
897 gme_debug.g_log_procedure THEN
898 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
899 || l_api_name);
900 END IF;
901
902 IF p_batch_hdr_rec.batch_type = gme_common_pvt.g_doc_type_fpo THEN
903 RAISE error_batch_type;
904 END IF;
905
906 IF p_batch_hdr_rec.parentline_id IS NOT NULL THEN
907 RAISE error_phantom;
908 END IF;
909
910 IF p_batch_hdr_rec.batch_status <> gme_common_pvt.g_batch_wip THEN
911 RAISE error_batch_status;
912 END IF;
913
914 OPEN cur_is_step_status_valid(p_batch_hdr_rec.batch_id);
915 FETCH cur_is_step_status_valid INTO l_is_step_status_valid;
916 CLOSE cur_is_step_status_valid;
917
918 IF l_is_step_status_valid > 0 THEN
919 RAISE error_step_status;
920 END IF;
921
922 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
923 gme_debug.g_log_procedure THEN
924 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
925 END IF;
926 EXCEPTION
927 WHEN error_phantom THEN
928 gme_common_pvt.log_message ('PM_INVALID_PHANTOM_ACTION');
929 x_return_status := FND_API.G_RET_STS_ERROR;
930 WHEN error_batch_type OR error_batch_status THEN
931 gme_common_pvt.log_message('GME_API_INVALID_BATCH_UNREL');
932 x_return_status := fnd_api.g_ret_sts_error;
933 WHEN error_step_status THEN
934 gme_common_pvt.log_message('GME_API_INVALID_STEP_UNREL');
935 x_return_status := fnd_api.g_ret_sts_error;
936 WHEN OTHERS THEN
937 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
938
939 IF g_debug <= gme_debug.g_log_procedure THEN
940 gme_debug.put_line ( 'Unexpected error: '
941 || g_pkg_name
942 || '.'
943 || l_api_name
944 || ': '
945 || SQLERRM);
946 END IF;
947
948 x_return_status := fnd_api.g_ret_sts_unexp_error;
949 END validate_batch_for_unrelease;
950
951 /* Bug 5021522 added function RETURNS TRUE if inv will go negative and org control does not allow it */
952 FUNCTION check_inv_negative(p_mmt_rec IN mtl_material_transactions%ROWTYPE,
953 p_mmln_tbl IN gme_common_pvt.mtl_trans_lots_num_tbl,
954 p_org_neg_control IN NUMBER DEFAULT gme_common_pvt.g_allow_neg_inv,
955 p_item_no IN VARCHAR2) RETURN BOOLEAN IS
956 l_api_name CONSTANT VARCHAR2(30) := 'check_inv_negative';
957 l_return_status VARCHAR2(1);
958 l_msg_data VARCHAR2(2000);
959 l_msg_cnt NUMBER;
960 l_qoh NUMBER;
961 l_rqoh NUMBER;
962 l_qr NUMBER;
963 l_qs NUMBER;
964 l_att NUMBER;
965 l_atr NUMBER;
966 l_sqoh NUMBER;
967 l_srqoh NUMBER;
968 l_sqr NUMBER;
969 l_sqs NUMBER;
970 l_satt NUMBER;
971 l_satr NUMBER;
972 BEGIN
973 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
974 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'|| l_api_name);
975 END IF;
976 IF (p_mmln_tbl.COUNT > 0) THEN
977 FOR i IN 1..p_mmln_tbl.COUNT LOOP
978 gme_transactions_pvt.query_quantities(x_return_status => l_return_status,
979 x_msg_count => l_msg_cnt,
980 x_msg_data => l_msg_data,
981 p_organization_id => p_mmt_rec.organization_id,
982 p_inventory_item_id => p_mmt_rec.inventory_item_id,
983 p_tree_mode => gme_common_pvt.g_tree_transaction_mode,
984 p_grade_code => NULL,
985 p_revision => p_mmt_rec.revision,
986 p_lot_number => p_mmln_tbl(i).lot_number,
987 p_subinventory_code => p_mmt_rec.subinventory_code,
988 p_locator_id => p_mmt_rec.locator_id,
989 x_qoh => l_qoh,
990 x_rqoh => l_rqoh,
991 x_qr => l_qr,
992 x_qs => l_qs,
993 x_att => l_att,
994 x_atr => l_atr,
995 x_sqoh => l_sqoh,
996 x_srqoh => l_srqoh,
997 x_sqr => l_sqr,
998 x_sqs => l_sqs,
999 x_satt => l_satt,
1000 x_satr => l_satr);
1001 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1002 gme_debug.put_line('NVL(l_att,0) = '||NVL(l_att,0));
1003 gme_debug.put_line('NVL(ABS(p_mmln_tbl(i).primary_quantity),0) = '||NVL(ABS(p_mmln_tbl(i).primary_quantity),0));
1004 END IF;
1005 IF (NVL(l_att,0) < NVL(ABS(p_mmln_tbl(i).primary_quantity),0)) THEN
1006 IF (p_org_neg_control = 2) THEN --org does not allow negative inventory
1007 gme_common_pvt.log_message
1008 ( p_message_code => 'GME_ITEM_NEG_INVENTORY'
1009 ,p_product_code => 'GME'
1010 ,p_token1_name => 'ITEM_NO'
1011 ,p_token1_value => p_item_no
1012 );
1013 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1014 gme_debug.put_line('error. onhand will be driven negative for lot '||p_mmln_tbl(i).lot_number||' which the org does not allow');
1015 END IF;
1016 RETURN TRUE;
1017 ELSIF (p_org_neg_control = 1) --org allows negative inventory
1018 AND (l_qr > 0) THEN
1019 gme_common_pvt.log_message
1020 ( p_message_code => 'GME_NEG_INV_WHEN_RSRVTNS_EXIST'
1021 ,p_product_code => 'GME'
1022 ,p_token1_name => 'ITEM_NO'
1023 ,p_token1_value => p_item_no
1024 );
1025 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1026 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');
1027 END IF;
1028 RETURN TRUE;
1029 END IF;
1030 END IF;
1031 END LOOP;
1032 ELSE
1033 gme_transactions_pvt.query_quantities(x_return_status => l_return_status,
1034 x_msg_count => l_msg_cnt,
1035 x_msg_data => l_msg_data,
1036 p_organization_id => p_mmt_rec.organization_id,
1037 p_inventory_item_id => p_mmt_rec.inventory_item_id,
1038 p_tree_mode => gme_common_pvt.g_tree_transaction_mode,
1039 p_grade_code => NULL,
1040 p_revision => p_mmt_rec.revision,
1041 p_lot_number => NULL,
1042 p_subinventory_code => p_mmt_rec.subinventory_code,
1043 p_locator_id => p_mmt_rec.locator_id,
1044 x_qoh => l_qoh,
1045 x_rqoh => l_rqoh,
1046 x_qr => l_qr,
1047 x_qs => l_qs,
1048 x_att => l_att,
1049 x_atr => l_atr,
1050 x_sqoh => l_sqoh,
1051 x_srqoh => l_srqoh,
1052 x_sqr => l_sqr,
1053 x_sqs => l_sqs,
1054 x_satt => l_satt,
1055 x_satr => l_satr);
1056 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1057 gme_debug.put_line('NVL(l_att,0) = '||NVL(l_att,0));
1058 gme_debug.put_line('NVL(ABS(p_mmt_rec.primary_quantity),0) = '||NVL(ABS(p_mmt_rec.primary_quantity),0));
1059 END IF;
1060 IF (NVL(l_att,0) < NVL(ABS(p_mmt_rec.primary_quantity),0)) THEN
1061 IF (p_org_neg_control = 2) THEN --org does not allow negative inventory
1062 gme_common_pvt.log_message
1063 ( p_message_code => 'GME_ITEM_NEG_INVENTORY'
1064 ,p_product_code => 'GME'
1065 ,p_token1_name => 'ITEM_NO'
1066 ,p_token1_value => p_item_no
1067 );
1068 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1069 gme_debug.put_line('error. onhand will be driven negative which the org does not allow');
1070 END IF;
1071 RETURN TRUE;
1072 ELSIF (p_org_neg_control = 1) --org allows negative inventory
1073 AND (l_qr > 0) THEN
1074 gme_common_pvt.log_message
1075 ( p_message_code => 'GME_NEG_INV_WHEN_RSRVTNS_EXIST'
1076 ,p_product_code => 'GME'
1077 ,p_token1_name => 'ITEM_NO'
1078 ,p_token1_value => p_item_no
1079 );
1080 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1081 gme_debug.put_line('error. onhand will be driven negative which org allows but there are reservations');
1082 END IF;
1083 RETURN TRUE;
1084 END IF;
1085 END IF;
1086 END IF;
1087 RETURN FALSE;
1088 EXCEPTION
1089 WHEN OTHERS THEN
1090 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1091 IF g_debug <= gme_debug.g_log_procedure THEN
1092 gme_debug.put_line('Unexpected error: '|| g_pkg_name|| '.'|| l_api_name|| ': '|| SQLERRM);
1093 END IF;
1094 RETURN FALSE;
1095 END check_inv_negative;
1096
1097 END gme_unrelease_batch_pvt;