1 PACKAGE BODY gme_release_batch_pvt AS
2 /* $Header: GMEVRLBB.pls 120.39.12020000.3 2012/07/26 15:44:17 gmurator ship $ */
3
4 G_DEBUG VARCHAR2(5) := FND_PROFILE.VALUE('AFLOG_LEVEL');
5
6 g_pkg_name VARCHAR2(30) := 'GME_RELEASE_BATCH_PVT';
7
8
9 PROCEDURE release_batch
10 (p_batch_header_rec IN gme_batch_header%ROWTYPE
11 ,p_phantom_product_id IN NUMBER DEFAULT NULL
12 ,p_yield IN BOOLEAN DEFAULT NULL
13 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
14 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
15 ,x_return_status OUT NOCOPY VARCHAR2) IS
16
17
18 CURSOR Cur_batch_ingredients(v_batch_id NUMBER) IS
19 SELECT *
20 FROM gme_material_details
21 WHERE batch_id = v_batch_id
22 AND line_type = gme_common_pvt.g_line_type_ing;
23
24 CURSOR Cur_associated_step(v_matl_dtl_id NUMBER) IS
25 SELECT s.*
26 FROM gme_batch_steps s, gme_batch_step_items item
27 WHERE s.batchstep_id = item.batchstep_id
28 AND item.material_detail_id = v_matl_dtl_id;
29
30 l_api_name CONSTANT VARCHAR2 (30) := 'RELEASE_BATCH';
31
32 l_step_rec gme_batch_steps%ROWTYPE;
33 l_matl_dtl_tab gme_common_pvt.material_details_tab;
34 l_matl_dtl_rec gme_material_details%ROWTYPE;
35 l_release_type NUMBER;
36 l_phantom_batch_rec gme_batch_header%ROWTYPE;
37 l_item_rec mtl_system_items_b%ROWTYPE;
38 l_consume BOOLEAN;
39 l_return_status VARCHAR2(1);
40
41 l_actual_qty NUMBER;
42
43 error_update_batch EXCEPTION;
44 error_process_ingredient EXCEPTION;
45 error_consume_material EXCEPTION;
46 error_update_row EXCEPTION;
47 error_yield_material EXCEPTION;
48 error_fetch_material EXCEPTION;
49 error_get_item EXCEPTION;
50 error_unexp_phantom EXCEPTION;
51
52 -- Bug 5903208
53 gmf_cost_failure EXCEPTION;
54 l_message_count NUMBER;
55 l_message_list VARCHAR2(2000);
56 l_tmp VARCHAR2(2000);
57
58 BEGIN
59 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
60 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
61 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batch_id='||p_batch_header_rec.batch_id);
62 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_phantom_product_id='||p_phantom_product_id);
63 IF p_yield IS NOT NULL AND p_yield THEN
64 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_yield=TRUE');
65 ELSIF p_yield IS NOT NULL THEN
66 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_yield=FALSE');
67 ELSE
68 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_yield is NULL');
69 END IF;
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 -- call for validate the batch for unexploded phantoms
78 check_unexploded_phantom(p_batch_id => x_batch_header_rec.batch_id
79 ,p_auto_by_step => 2
80 ,p_batchstep_id => null
81 ,x_return_status => l_return_status);
82
83 IF l_return_status <> fnd_api.g_ret_sts_success THEN
84 RAISE error_unexp_phantom;
85 END IF;
86 -- set batch status
87 x_batch_header_rec.batch_status := gme_common_pvt.g_batch_wip;
88
89 -- set actual start date...
90 -- this is expected to be populated and validated (from either user input or timestamp)
91 x_batch_header_rec.actual_start_date := p_batch_header_rec.actual_start_date;
92
93 -- Update the batch header
94 IF NOT gme_batch_header_dbl.update_row (p_batch_header => x_batch_header_rec) THEN
95 RAISE error_update_batch;
96 END IF;
97
98 -- Update WHO columns for output structure
99 x_batch_header_rec.last_updated_by := gme_common_pvt.g_user_ident;
100 x_batch_header_rec.last_update_date := gme_common_pvt.g_timestamp;
101 x_batch_header_rec.last_update_login := gme_common_pvt.g_login_id;
102
103 -- retrieve all ingredients, don't blindly exclude auto by step, because these can really be auto
104 -- if not associated to a step...
105 OPEN Cur_batch_ingredients(p_batch_header_rec.batch_id);
106 FETCH Cur_batch_ingredients BULK COLLECT INTO l_matl_dtl_tab;
107 CLOSE Cur_batch_ingredients;
108
109 FOR i IN 1..l_matl_dtl_tab.COUNT LOOP
110 l_matl_dtl_rec := l_matl_dtl_tab(i);
111
112 l_release_type := l_matl_dtl_rec.release_type;
113 IF l_release_type = gme_common_pvt.g_mtl_autobystep_release THEN
114 OPEN Cur_associated_step(l_matl_dtl_rec.material_detail_id);
115 FETCH Cur_associated_step INTO l_step_rec;
116 IF Cur_associated_step%NOTFOUND THEN
117 l_release_type := gme_common_pvt.g_mtl_auto_release;
118 END IF;
119 CLOSE Cur_associated_step;
120 END IF;
121
122 IF l_release_type <> gme_common_pvt.g_mtl_autobystep_release THEN
123 IF l_release_type = gme_common_pvt.g_mtl_auto_release THEN
124 l_consume := TRUE;
125 ELSE
126 l_consume := FALSE;
127 END IF;
128
129 process_ingredient
130 (p_material_detail_rec => l_matl_dtl_rec
131 ,p_consume => l_consume
132 ,p_trans_date => x_batch_header_rec.actual_start_date
133 ,p_update_inv_ind => x_batch_header_rec.update_inventory_ind
134 ,x_exception_material_tbl => x_exception_material_tbl
135 ,x_return_status => l_return_status);
136
137 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
138 x_return_status := l_return_status;
139 RAISE error_process_ingredient;
140 END IF;
141
142 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
143 x_return_status := gme_common_pvt.g_exceptions_err;
144 END IF;
145 END IF; -- IF l_release_type <> gme_common_pvt.g_mtl_autobystep_release THEN
146 END LOOP;
147
148 -- Yield the phantom product for this batch... that will also take care of the phantom ingredient
149 IF p_phantom_product_id IS NOT NULL THEN
150 IF NVL(p_yield, TRUE) THEN
151 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
152 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' yielding phantom product');
153 END IF;
154 l_matl_dtl_rec.material_detail_id := p_phantom_product_id;
155 IF NOT gme_material_details_dbl.fetch_row(l_matl_dtl_rec, l_matl_dtl_rec) THEN
156 RAISE error_fetch_material;
157 END IF;
158
159 -- l_matl_dtl_rec is the phantom product line
160
161 gme_material_detail_pvt.get_item_rec
162 (p_org_id => l_matl_dtl_rec.organization_id
163 ,p_item_id => l_matl_dtl_rec.inventory_item_id
164 ,x_item_rec => l_item_rec
165 ,x_return_status => l_return_status);
166
167 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
168 x_return_status := l_return_status;
169 RAISE error_get_item;
170 END IF;
171
172 IF p_batch_header_rec.update_inventory_ind = 'Y' AND
173 l_item_rec.mtl_transactions_enabled_flag = 'Y' THEN
174 IF g_debug <= gme_debug.g_log_procedure THEN
175 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' processing phantom product material_detail_id='||l_matl_dtl_rec.material_detail_id);
176 END IF;
177
178 gme_complete_batch_pvt.yield_material
179 (p_material_dtl_rec => l_matl_dtl_rec
180 ,p_yield_qty => l_matl_dtl_rec.plan_qty
181 ,p_trans_date => x_batch_header_rec.actual_start_date
182 ,p_item_rec => l_item_rec
183 ,p_force_unconsumed => fnd_api.g_true
184 ,x_exception_material_tbl => x_exception_material_tbl
185 ,x_actual_qty => l_actual_qty
186 ,x_return_status => l_return_status);
187
188 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
189 x_return_status := l_return_status;
190 RAISE error_yield_material;
191 END IF;
192
193 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
194 x_return_status := gme_common_pvt.g_exceptions_err;
195 END IF;
196
197 l_matl_dtl_rec.actual_qty := l_actual_qty;
198 ELSE
199 l_matl_dtl_rec.actual_qty := l_matl_dtl_rec.plan_qty;
200 END IF;
201 ELSE
202 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
203 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' NOT yielding phantom product; set actual to 0');
204 END IF;
205 l_matl_dtl_rec.actual_qty := 0;
206 END IF;
207
208 l_matl_dtl_rec.wip_plan_qty := l_matl_dtl_rec.plan_qty;
209
210 -- Update the phantom product
211 UPDATE gme_material_details
212 SET actual_qty = l_matl_dtl_rec.actual_qty,
213 wip_plan_qty = l_matl_dtl_rec.wip_plan_qty,
214 last_updated_by = gme_common_pvt.g_user_ident,
215 last_update_date = gme_common_pvt.g_timestamp,
216 last_update_login = gme_common_pvt.g_login_id
217 WHERE material_detail_id = l_matl_dtl_rec.material_detail_id;
218
219 -- Update the phantom ingredient actual_qty and WIP plan qty...
220 -- the transaction would have been taken care of
221 UPDATE gme_material_details
222 SET actual_qty = l_matl_dtl_rec.actual_qty,
223 wip_plan_qty = l_matl_dtl_rec.wip_plan_qty,
224 last_updated_by = gme_common_pvt.g_user_ident,
225 last_update_date = gme_common_pvt.g_timestamp,
226 last_update_login = gme_common_pvt.g_login_id
227 WHERE material_detail_id = l_matl_dtl_rec.phantom_line_id;
228
229 END IF;
230
231 UPDATE gme_material_details
232 SET wip_plan_qty = plan_qty
233 WHERE batch_id = p_batch_header_rec.batch_id
234 AND wip_plan_qty is NULL;
235
236 IF NOT gme_common_pvt.create_history
237 (p_batch_header_rec => x_batch_header_rec
238 ,p_original_status => gme_common_pvt.g_batch_pending
239 ,p_event_id => NVL(gme_common_pvt.g_transaction_header_id,-9999)) THEN
240 IF g_debug <= gme_debug.g_log_procedure THEN
241 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' create history returned error');
242 END IF;
243 END IF;
244
245
246 --
247 -- Bug 5903208 - Make call to GMF
248 --
249 -- Bug 12909216 - Do not call GMF if we have a phantom product for zero qty.
250 IF p_phantom_product_id IS NULL OR l_matl_dtl_rec.wip_plan_qty <> 0 THEN
251 GMF_VIB.Create_Batch_Requirements
252 ( p_api_version => 1.0,
253 p_init_msg_list => FND_API.G_FALSE,
254 p_batch_id => x_batch_header_rec.batch_id,
255 x_return_status => l_return_status, --Bug#6507649
256 x_msg_count => l_message_count,
257 x_msg_data => l_message_list);
258
259 IF l_return_status <> FND_API.G_RET_STS_SUCCESS --Bug#6507649 Rework
260 THEN
261 RAISE gmf_cost_failure;
262 END IF;
263 -- End Bug 5903208
264 END IF;
265
266 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
267 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name||' for batch_id= '||x_batch_header_rec.batch_id||' and x_return_status= '||l_return_status);
268 END IF;
269
270 EXCEPTION
271 WHEN gmf_cost_failure THEN
272 -- Bug 5903208
273 x_return_status := FND_API.G_RET_STS_ERROR;
274
275 WHEN error_update_batch OR error_update_row OR error_fetch_material THEN
276 /* Bug 5554841 No need to set messsage it is set by called APIs */
277 --gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
278 x_return_status := FND_API.g_ret_sts_unexp_error;
279 WHEN error_process_ingredient OR error_consume_material OR error_yield_material OR error_get_item THEN
280 NULL;
281 WHEN error_unexp_phantom THEN
282 gme_common_pvt.log_message ('PM_UNEXPLODED_PHANTOMS');
283 x_return_status := FND_API.G_RET_STS_ERROR;
284 WHEN OTHERS THEN
285 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
286 IF g_debug <= gme_debug.g_log_procedure THEN
287 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
288 END IF;
289 x_return_status := FND_API.g_ret_sts_unexp_error;
290 END release_batch;
291
292 PROCEDURE process_ingredient
293 (p_material_detail_rec IN gme_material_details%ROWTYPE
294 ,p_consume IN BOOLEAN
295 ,p_trans_date IN DATE
296 ,p_update_inv_ind IN VARCHAR2
297 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
298 ,x_return_status OUT NOCOPY VARCHAR2) IS
299
300
301 l_api_name CONSTANT VARCHAR2 (30) := 'process_ingredient';
302
303 l_matl_dtl_rec gme_material_details%ROWTYPE;
304 l_in_phantom_batch_rec gme_batch_header%ROWTYPE;
305 l_phantom_batch_rec gme_batch_header%ROWTYPE;
306 l_return_status VARCHAR2(1);
307 l_item_rec mtl_system_items_b%ROWTYPE;
308 l_actual_qty NUMBER;
309 l_update_matl BOOLEAN;
310
311 error_update_row EXCEPTION;
312 error_fetch_batch EXCEPTION;
313 error_release_batch EXCEPTION;
314 error_consume_material EXCEPTION;
315 error_get_item EXCEPTION;
316 error_dispense_non_reserve EXCEPTION;
317 BEGIN
318
319 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
320 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
321 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' material_detail_id='||p_material_detail_rec.material_detail_id);
322 END IF;
323
324 /* Set the return status to success initially */
325 x_return_status := FND_API.G_RET_STS_SUCCESS;
326
327 -- Process the ingredients...
328 -- 1) release phantom batch ingredient
329 -- 2) consume non phantom ingredient
330 -- 3) set wip plan qty
331
332 l_matl_dtl_rec := p_material_detail_rec;
333
334 -- if it's a phantom ingredient, then release the batch and pass the phantom line id
335 -- which will cause the phantom product to be yielded; don't consume this ingredient
336 -- because the ingredient will be taken care of with yield of the product... that's why
337 -- consume is in the else of following if statement...
338
339 -- release phantom batch
340 IF l_matl_dtl_rec.phantom_id IS NOT NULL THEN -- phantom -> release the phantom batch
341 l_phantom_batch_rec.batch_id := l_matl_dtl_rec.phantom_id;
342 IF NOT gme_batch_header_dbl.fetch_row(l_phantom_batch_rec, l_phantom_batch_rec) THEN
343 RAISE error_fetch_batch;
344 END IF;
345
346 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
347 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' found phantom ingredient material_detail_id='||l_matl_dtl_rec.material_detail_id||' and phantom batch_id= '||l_phantom_batch_rec.batch_id);
348 END IF;
349 -- pass in the phantom line id so that release batch will know to yield that product
350 l_in_phantom_batch_rec := l_phantom_batch_rec;
351 l_in_phantom_batch_rec.actual_start_date := p_trans_date;
352 release_batch
353 (p_batch_header_rec => l_in_phantom_batch_rec
354 ,p_phantom_product_id => l_matl_dtl_rec.phantom_line_id
355 ,p_yield => p_consume
356 ,x_batch_header_rec => l_phantom_batch_rec
357 ,x_return_status => l_return_status
358 ,x_exception_material_tbl => x_exception_material_tbl);
359
360 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
361 x_return_status := l_return_status;
362 RAISE error_release_batch;
363 END IF;
364
365 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
366 x_return_status := gme_common_pvt.g_exceptions_err;
367 END IF;
368
369 l_update_matl := FALSE;
370
371 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
372 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' after release_batch for phantom batch; it returned x_return_status='||x_return_status);
373 END IF;
374 ELSIF p_consume THEN
375 gme_material_detail_pvt.get_item_rec
376 (p_org_id => l_matl_dtl_rec.organization_id
377 ,p_item_id => l_matl_dtl_rec.inventory_item_id
378 ,x_item_rec => l_item_rec
379 ,x_return_status => l_return_status);
380
381 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
382 x_return_status := l_return_status;
383 RAISE error_get_item;
384 END IF;
385
386 IF p_update_inv_ind = 'Y' AND
387 l_item_rec.mtl_transactions_enabled_flag = 'Y' THEN
388 --Pawan Kumar bug 4742244 --
389 -- check for item which dispensable but non-reservable
390 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
391 gme_debug.put_line(g_pkg_name||'.'||l_api_name||'disp ind'||l_matl_dtl_rec.dispense_ind);
392 gme_debug.put_line(g_pkg_name||'.'||l_api_name||'reservable_type'||l_item_rec.reservable_type);
393 END IF;
394 IF nvl(l_matl_dtl_rec.dispense_ind, 'N' ) = 'Y' AND
395 l_item_rec.reservable_type <> 1 THEN
396 RAISE error_dispense_non_reserve;
397 END IF;
398
399 consume_material(p_material_dtl_rec => l_matl_dtl_rec
400 ,p_trans_date => p_trans_date
401 ,p_item_rec => l_item_rec
402 ,x_exception_material_tbl => x_exception_material_tbl
403 ,x_actual_qty => l_actual_qty
404 ,x_return_status => l_return_status);
405
406 l_matl_dtl_rec.actual_qty := l_actual_qty;
407
408 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
409 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' after consume_material; it returned actual_qty='||l_actual_qty);
410 END IF;
411
412 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
413 x_return_status := l_return_status;
414 RAISE error_consume_material;
415 END IF;
416
417 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
418 x_return_status := gme_common_pvt.g_exceptions_err;
419 END IF;
420 ELSE
421 l_matl_dtl_rec.actual_qty := l_matl_dtl_rec.plan_qty;
422 END IF;
423
424 l_update_matl := TRUE;
425
426 ELSE -- ELSIF p_consume
427 l_update_matl := TRUE;
428 l_matl_dtl_rec.actual_qty := 0;
429 END IF; -- IF l_matl_dtl_rec.phantom_id IS NOT NULL...
430
431 --Bug 8468926 To overwrite the wip plan qty if there is not already a value there
432 --IF l_update_matl THEN
433 IF l_update_matl and NVL( l_matl_dtl_rec.wip_plan_qty, 0) = 0 THEN
434 -- set WIP plan qty
435 l_matl_dtl_rec.wip_plan_qty := l_matl_dtl_rec.plan_qty;
436
437 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
438 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' before update_row; actual_qty='||l_matl_dtl_rec.actual_qty);
439 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' before update_row; wip_plan_qty='||l_matl_dtl_rec.wip_plan_qty);
440 END IF;
441
442 IF NOT gme_material_details_dbl.update_row (l_matl_dtl_rec) THEN
443 RAISE error_update_row;
444 END IF;
445 END IF;
446
447 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
448 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name||' with x_return_status= '||x_return_status);
449 END IF;
450
451 EXCEPTION
452 WHEN error_update_row OR error_fetch_batch THEN
453 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
454 x_return_status := FND_API.g_ret_sts_unexp_error;
455 WHEN error_release_batch OR error_consume_material OR error_get_item THEN
456 NULL;
457 WHEN error_dispense_non_reserve THEN
458 gme_common_pvt.log_message ('GME_DISPENSE_NON_RESERVE');
459 x_return_status := fnd_api.g_ret_sts_error;
460 WHEN OTHERS THEN
461 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
462 IF g_debug <= gme_debug.g_log_procedure THEN
463 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
464 END IF;
465 x_return_status := FND_API.g_ret_sts_unexp_error;
466 END process_ingredient;
467
468 -- Note: p_consume_qty is the target actual qty; for incr, it's also the target, not the incr
469 PROCEDURE consume_material(p_material_dtl_rec IN gme_material_details%ROWTYPE
470 ,p_consume_qty IN NUMBER := NULL
471 ,p_trans_date IN DATE := NULL
472 ,p_item_rec IN mtl_system_items_b%ROWTYPE
473 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
474 ,x_actual_qty OUT NOCOPY NUMBER
475 ,x_return_status OUT NOCOPY VARCHAR2) IS
476
477 l_api_name CONSTANT VARCHAR2 (30) := 'CONSUME_MATERIAL';
478
479 l_reservation_rec mtl_reservations%ROWTYPE;
480 l_reservation_tab gme_common_pvt.reservations_tab;
481 i NUMBER;
482 l_rsrv_type NUMBER;
483 l_start_actual_qty NUMBER;
484
485 l_PLR_tab gme_common_pvt.reservations_tab;
486 j NUMBER;
487 l_try_PLR BOOLEAN;
488 l_partial_rec mtl_reservations%ROWTYPE;
489 l_pending_mo_ind BOOLEAN := NULL;
490 l_pending_rsrv_ind BOOLEAN := NULL;
491
492 l_consume_qty NUMBER;
493 l_trans_date DATE;
494 l_subinv VARCHAR2(10);
495 l_locator_id NUMBER;
496 l_revision VARCHAR2(3);
497 l_return_status VARCHAR2(1);
498
499 l_qoh NUMBER;
500 l_rqoh NUMBER;
501 l_qr NUMBER;
502 l_qs NUMBER;
503 l_att NUMBER;
504 l_atr NUMBER;
505 l_sqoh NUMBER;
506 l_srqoh NUMBER;
507 l_sqr NUMBER;
508 l_sqs NUMBER;
509 l_satt NUMBER;
510 l_satr NUMBER;
511 l_msg_count NUMBER;
512 l_msg_data VARCHAR2(32767);
513
514 l_eff_locator_control NUMBER;
515
516 l_lot_orig_date DATE; -- Bug 12971020
517 error_bad_trans_date EXCEPTION;
518
519 CURSOR cur_get_item_revision(v_item_id NUMBER, v_org_id NUMBER) IS
520 SELECT revision
521 FROM mtl_item_revisions_b
522 WHERE inventory_item_id = v_item_id
523 AND organization_id = v_org_id
524 AND effectivity_date <= gme_common_pvt.g_timestamp
525 ORDER BY effectivity_date desc;
526
527 error_get_item EXCEPTION;
528 error_build_trxn EXCEPTION;
529 error_get_exception EXCEPTION;
530 error_convert_partial EXCEPTION;
531 error_unexpected EXCEPTION;
532 consume_done EXCEPTION;
533 error_get_reservations EXCEPTION;
534 no_consume_required EXCEPTION;
535
536 BEGIN
537
538 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
539 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
540 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' material_detail_id='||p_material_dtl_rec.material_detail_id);
541 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' dispense_ind='||p_material_dtl_rec.dispense_ind);
542 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_consume_qty='||p_consume_qty);
543 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_trans_date='||to_char(p_trans_date
544 ,'YYYY-MON-DD HH24:MI:SS'));
545 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' line_no='||p_material_dtl_rec.line_no);
546 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' line_type='||p_material_dtl_rec.line_type);
547 END IF;
548 /* Set the return status to success initially */
549 x_return_status := FND_API.G_RET_STS_SUCCESS;
550
551 -- set the output actual qty to it's current value...
552 x_actual_qty := p_material_dtl_rec.actual_qty;
553
554 -- following global is set only for migration purposes, where transactions need not be created,
555 IF gme_release_batch_pvt.g_bypass_txn_creation = 1 THEN
556 RAISE no_consume_required;
557 END IF;
558
559 l_start_actual_qty := x_actual_qty;
560
561 -- Couple of optimizations...
562 -- If consume from supply sub is set to Yes and there's no supply sub, then return with exceptions... can't do anything
563 -- If consume from supply sub is set to No and there's no supply sub, then consume DLR, and return with exceptions (if appl)
564 -- If reservable is set to No don't bother to retrieve the reservations... there aren't any...
565
566 IF gme_common_pvt.g_auto_consume_supply_sub_only = 1 THEN
567 IF p_material_dtl_rec.subinventory IS NULL THEN
568 l_pending_mo_ind := FALSE; -- can't have move order if sub is NULL
569 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
570 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' auto consume from supply sub is ON and subinv on material is NULL; cant consume anything; get exceptions');
571 END IF;
572
573 RAISE error_get_exception;
574 END IF;
575 END IF;
576
577 l_subinv := p_material_dtl_rec.subinventory;
578 l_locator_id := p_material_dtl_rec.locator_id;
579
580 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
581 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_subinv='||l_subinv);
582 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_locator_id='||l_locator_id);
583 END IF;
584
585 -- channges for GMO
586 gme_reservations_pvt.get_material_reservations
587 (p_organization_id => p_material_dtl_rec.organization_id
588 ,p_batch_id => p_material_dtl_rec.batch_id
589 ,p_material_detail_id => p_material_dtl_rec.material_detail_id
590 ,p_dispense_ind => nvl(p_material_dtl_rec.dispense_ind,'N')
591 ,x_return_status => l_return_status
592 ,x_reservations_tbl => l_reservation_tab);
593
594 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
595 x_return_status := l_return_status;
596 RAISE error_get_reservations;
597 END IF;
598
599 -- Bug 8468926 - Default consume qty properly.
600 -- l_consume_qty := NVL(p_consume_qty, p_material_dtl_rec.plan_qty);
601 IF NVL(p_material_dtl_rec.wip_plan_qty, 0) > 0 THEN
602 l_consume_qty := NVL(p_consume_qty, p_material_dtl_rec.wip_plan_qty);
603 ELSE
604 l_consume_qty := NVL(p_consume_qty, p_material_dtl_rec.plan_qty);
605 END IF;
606
607 l_trans_date := NVL(p_trans_date, gme_common_pvt.g_timestamp);
608
609 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
610 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_consume_qty='||l_consume_qty);
611 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_trans_date='||to_char(p_trans_date
612 ,'YYYY-MON-DD HH24:MI:SS'));
613 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
614 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' number of reservations='||l_reservation_tab.COUNT);
615 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' auto consume from supply sub='||gme_common_pvt.g_auto_consume_supply_sub_only);
616 END IF;
617
618 i := 1;
619 j := 1;
620
621 WHILE l_consume_qty > x_actual_qty AND i <= l_reservation_tab.COUNT LOOP
622 -- Consume all fully specified reservations and mark the Partial ones
623 l_reservation_rec := l_reservation_tab(i);
624 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
625 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' in reservation loop: reservation_id='||l_reservation_rec.reservation_id);
626 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' in reservation loop: reservation subinventory='||l_reservation_rec.subinventory_code);
627 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' in reservation loop: reservation external_source_line_id='||l_reservation_rec.external_source_line_id);
628 END IF;
629 /* Bug 5441643 Added NVL condition for location control code*/
630 l_rsrv_type := gme_reservations_pvt.reservation_fully_specified
631 (p_reservation_rec => l_reservation_rec
632 ,p_item_location_control => NVL(p_item_rec.location_control_code,1)
633 ,p_item_restrict_locators => p_item_rec.restrict_locators_code);
634
635 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
636 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' after call to gme_reservations_pvt.reservation_fully_specified: l_rsrv_type='||l_rsrv_type);
637 END IF;
638
639 IF l_rsrv_type = -1 THEN
640 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
641 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_rsrv_type passed back as -1 from gme_reservations_pvt.reservation_fully_specified');
642 RAISE error_unexpected;
643 ENd IF;
644 END IF;
645
646 -- Bug 12971020 - Let's make sure that trans date is after lot origination date.
647 IF (l_reservation_rec.lot_number IS NOT NULL) THEN
648 SELECT origination_date INTO l_lot_orig_date
649 FROM MTL_LOT_NUMBERS
650 WHERE INVENTORY_ITEM_ID = l_reservation_rec.inventory_item_id
651 AND LOT_NUMBER = l_reservation_rec.lot_number
652 AND ORGANIZATION_ID = l_reservation_rec.organization_id;
653
654 IF (p_trans_date < l_lot_orig_date) THEN
655 gme_common_pvt.log_message (p_product_code => 'INV',
656 p_message_code => 'INV_INT_TDATECODE');
657 RAISE error_bad_trans_date;
658 END IF;
659 END IF;
660
661 --
662 -- bug 12695713
663 -- Setting the revision before creating the txn
664 --
665 l_revision := NULL;
666 IF p_item_rec.revision_qty_control_code = 2 THEN -- under revision control
667 IF p_material_dtl_rec.revision IS NOT NULL THEN
668 l_revision := p_material_dtl_rec.revision;
669 ELSE
670 OPEN cur_get_item_revision(p_material_dtl_rec.inventory_item_id,
671 p_material_dtl_rec.organization_id);
672 FETCH cur_get_item_revision INTO l_revision;
673 CLOSE cur_get_item_revision;
674 END IF;
675 END IF; -- IF p_revision_qty_control_code = 2
676
677 IF l_rsrv_type IN (0, 2) THEN -- HLR or PLR
678 -- save these for later; if there's not enough DLR, PLR will be filled in and used
679 l_PLR_tab(j) := l_reservation_rec;
680 j := j + 1;
681 ELSE -- detailed level reservation
682 IF (gme_common_pvt.g_auto_consume_supply_sub_only = 0) OR
683 (gme_common_pvt.g_auto_consume_supply_sub_only = 1 AND
684 l_reservation_rec.subinventory_code = l_subinv) THEN
685 -- GMO Changes
686 IF ((NVL(p_material_dtl_rec.dispense_ind,'N') = 'Y' AND
687 l_reservation_rec.external_source_line_id IS NOT NULL ) OR
688 NVL(p_material_dtl_rec.dispense_ind,'N') = 'N' ) THEN
689 build_and_create_transaction
690 (p_rsrv_rec => l_reservation_rec
691 ,p_lot_divisible_flag => p_item_rec.lot_divisible_flag
692 ,p_dispense_ind => p_material_dtl_rec.dispense_ind
693 ,p_mtl_dtl_rec => p_material_dtl_rec
694 ,p_trans_date => l_trans_date
695 ,p_consume_qty => l_consume_qty
696 ,p_revision => l_revision -- NULL bug 12695713. Passing the revision
697 ,p_secondary_uom_code => p_item_rec.secondary_uom_code
698 ,x_actual_qty => x_actual_qty
699 ,x_return_status => l_return_status);
700
701 IF l_return_status NOT IN (gme_common_pvt.g_not_transactable, FND_API.G_RET_STS_SUCCESS) THEN
702 x_return_status := l_return_status;
703 RAISE error_build_trxn;
704 END IF;
705 END IF; -- p_material_dtl_rec.dispense_ind = 'Y'
706 END IF; -- IF (gme_common_pvt.g_auto_consume_supply_sub_only = 0) OR...
707 END IF; -- IF l_rsrv_type = ...
708 i := i + 1; -- move on to the next reservation
709 END LOOP;
710
711 IF x_actual_qty >= l_consume_qty THEN
712 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
713 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' consumption complete: x_actual_qty='||x_actual_qty||' and l_consume_qty='||l_consume_qty);
714 END IF;
715 -- done!
716 RAISE consume_done;
717 END IF;
718
719 IF (gme_common_pvt.g_auto_consume_supply_sub_only = 0 AND l_subinv IS NULL) THEN
720 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
721 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' exception; qty not met; consume from supply sub is FALSE and material subinv is NULL');
722 END IF;
723 RAISE error_get_exception;
724 END IF;
725
726 -- Changes for GMO
727 IF NVL(p_material_dtl_rec.dispense_ind, 'N') = 'Y' THEN
728 -- if you get to this point, raise exception; can't process PLR/HLR for dispensed records; nor
729 -- can you get available inventory; record must be dispensed to process it
730 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
731 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' dispensed item; get exceptions: x_actual_qty='||x_actual_qty||' and l_consume_qty='||l_consume_qty);
732 END IF;
733 RAISE error_get_exception;
734 END IF;
735
736 l_pending_mo_ind := gme_move_orders_pvt.pending_move_orders_exist
737 (p_organization_id => p_material_dtl_rec.organization_id
738 ,p_batch_id => p_material_dtl_rec.batch_id
739 ,p_material_detail_id => p_material_dtl_rec.material_detail_id);
740
741 IF p_item_rec.lot_control_code = 2 THEN -- lot control
742 IF gme_common_pvt.g_auto_consume_supply_sub_only = 1 THEN -- auto consume -> Yes
743 IF l_pending_mo_ind THEN
744 l_try_PLR := FALSE;
745 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
746 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' consume from supply sub ON; pending MO TRUE: l_try_PLR := FALSE; get batch exception');
747 END IF;
748 ELSE
749 l_try_PLR := TRUE;
750 END IF;
751 ELSE
752 l_try_PLR := TRUE;
753 END IF;
754
755 IF NOT l_try_PLR THEN
756 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
757 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' lot control item; get exceptions: x_actual_qty='||x_actual_qty||' and l_consume_qty='||l_consume_qty);
758 END IF;
759 RAISE error_get_exception;
760 END IF;
761 END IF;
762
763 -- at this point, it's a lot control item with demand not met and no pending move orders OR
764 -- a plain, revision or locator ctrl item
765 -- try to convert and consume Partial reservations
766
767 -- Bug 8277090 - Initialize loop counter.
768 i := 1;
769 WHILE l_consume_qty > x_actual_qty AND i <= l_PLR_tab.COUNT LOOP
770 -- try to convert PLR to DLR
771 l_partial_rec := l_PLR_tab(i);
772 gme_reservations_pvt.convert_partial_to_dlr
773 (p_reservation_rec => l_partial_rec
774 ,p_material_dtl_rec => p_material_dtl_rec
775 ,p_item_rec => p_item_rec
776 ,x_reservation_rec => l_reservation_rec
777 ,x_return_status => l_return_status);
778 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
779 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
780 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' could not convert partial to dlr for reservation id='||l_partial_rec.reservation_id||'; moving to next partial');
781 END IF;
782 ELSE
783 -- Bug 8277090 - Initialize locator_id properly if required.
784 IF l_reservation_rec.locator_id IS NULL AND
785 p_material_dtl_rec.locator_id IS NOT NULL THEN
786 l_reservation_rec.locator_id := p_material_dtl_rec.locator_id;
787 END IF;
788
789 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
790 gme_debug.put_line (g_pkg_name||'.'||l_api_name||'Calling query quantities for plr');
791 gme_debug.put_line ('inventory_item_id is '||p_material_dtl_rec.inventory_item_id);
792 gme_debug.put_line ('material_detail_id is '||p_material_dtl_rec.material_detail_id);
793 END IF;
794
795 -- Bug 13949475 - Let's see if there is enough inventory.
796 gme_transactions_pvt.query_quantities
797 (
798 p_api_version_number => 1
799 ,p_init_msg_lst => fnd_api.g_false
800 ,x_return_status => l_return_status
801 ,x_msg_count => l_msg_count
802 ,x_msg_data => l_msg_data
803 ,p_organization_id => p_material_dtl_rec.organization_id
804 ,p_inventory_item_id => p_material_dtl_rec.inventory_item_id
805 ,p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
806 ,p_is_serial_control => FALSE
807 ,p_grade_code => NULL
808 ,p_demand_source_type_id => gme_common_pvt.g_txn_source_type
809 ,p_demand_source_header_id => p_material_dtl_rec.batch_id
810 ,p_demand_source_line_id => p_material_dtl_rec.material_detail_id
811 ,p_demand_source_name => NULL
812 ,p_lot_expiration_date => NULL
813 ,p_revision => l_revision
814 ,p_lot_number => NULL
815 ,p_subinventory_code => l_reservation_rec.subinventory_code
816 ,p_locator_id => l_reservation_rec.locator_id
817 ,p_onhand_source => inv_quantity_tree_pvt.g_all_subs
818 ,x_qoh => l_qoh
819 ,x_rqoh => l_rqoh
820 ,x_qr => l_qr
821 ,x_qs => l_qs
822 ,x_att => l_att
823 ,x_atr => l_atr
824 ,x_sqoh => l_sqoh
825 ,x_srqoh => l_srqoh
826 ,x_sqr => l_sqr
827 ,x_sqs => l_sqs
828 ,x_satt => l_satt
829 ,x_satr => l_satr
830 ,p_transfer_subinventory_code => NULL
831 ,p_cost_group_id => NULL
832 ,p_lpn_id => NULL
833 ,p_transfer_locator_id => NULL
834 );
835
836 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
837 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities return status='||l_return_status);
838 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities att='||l_att);
839 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities qoh='||l_qoh);
840 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities item_id='||p_material_dtl_rec.inventory_item_id);
841 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities sub='||l_subinv);
842 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities loc='||l_reservation_rec.locator_id);
843 END IF;
844
845 -- Internal note - down the road this may need to be reconstructed like 7709971 below to consider allow neg inv.
846 IF l_return_status = FND_API.G_RET_STS_SUCCESS AND l_att > 0 THEN
847 build_and_create_transaction
848 (p_rsrv_rec => l_reservation_rec
849 ,p_lot_divisible_flag => p_item_rec.lot_divisible_flag
850 ,p_mtl_dtl_rec => p_material_dtl_rec
851 ,p_trans_date => l_trans_date
852 ,p_consume_qty => l_consume_qty
853 ,p_revision => l_revision -- NULL bug 12695713. Passing the revision
854 ,p_secondary_uom_code => p_item_rec.secondary_uom_code
855 ,x_actual_qty => x_actual_qty
856 ,x_return_status => l_return_status);
857
858 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
859 gme_debug.put_line ('after build and create');
860 END IF;
861
862 IF l_return_status NOT IN (gme_common_pvt.g_not_transactable, FND_API.G_RET_STS_SUCCESS) THEN
863 x_return_status := l_return_status;
864 RAISE error_build_trxn;
865 END IF;
866
867 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
868 gme_debug.put_line ('after build and create build is successful');
869 END IF;
870
871 END IF; -- IF l_return_status <> FND_API.G_RET_STS_SUCCESS AND l_att
872 END IF; -- IF l_return_status <> FND_API.G_RET_STS_SUCCESS for gme_reservations_pvt.convert_partial_to_dlr
873
874 i := i + 1; -- move on to the next partial reservation
875 END LOOP;
876
877 -- Bug 8277090 - See if we have satisfied consumption qty..
878 IF x_actual_qty >= l_consume_qty THEN
879 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
880 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' consumption complete: x_actual_qty='||x_actual_qty||' and l_consume_qty='||l_consume_qty);
881 END IF;
882 -- done!
883 RAISE consume_done;
884 END IF;
885
886 -- If it's lot control and the qty is still not satisfied, get exceptions;
887 IF p_item_rec.lot_control_code = 2 THEN -- lot control
888 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
889 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' lot control; DLR and PLR have been exhausted; get exception');
890 END IF;
891 RAISE error_get_exception;
892 END IF;
893
894 -- If it's plain, revision or locator, try to get from supply sub and supply locator
895 -- get qty tree rec in subinv/loc
896 --
897 -- bug 12695713
898 -- re-initializing the l_revision variable
899 --
900 l_revision := NULL;
901 IF p_item_rec.revision_qty_control_code = 2 THEN -- under revision control
902 IF p_material_dtl_rec.revision IS NOT NULL THEN
903 l_revision := p_material_dtl_rec.revision;
904 ELSE
905 OPEN cur_get_item_revision(p_material_dtl_rec.inventory_item_id,
906 p_material_dtl_rec.organization_id);
907 FETCH cur_get_item_revision INTO l_revision;
908 CLOSE cur_get_item_revision;
909 END IF;
910 END IF; -- IF p_revision_qty_control_code = 2
911
912 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
913 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_revision='||l_revision);
914 END IF;
915
916 IF l_locator_id IS NULL THEN
917 -- check if it's locator control, we need a locator...
918 /* Bug 5441643 Added NVL condition for location control code*/
919 l_eff_locator_control :=
920 gme_common_pvt.eff_locator_control
921 (p_organization_id => p_material_dtl_rec.organization_id
922 ,p_org_control => gme_common_pvt.g_org_locator_control
923 ,p_subinventory => p_material_dtl_rec.subinventory
924 ,p_item_control => NVL(p_item_rec.location_control_code,1)
925 ,p_item_loc_restrict => p_item_rec.restrict_locators_code
926 ,p_action => gme_common_pvt.g_ing_issue_txn_action);
927 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
928 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_eff_locator_control='||l_eff_locator_control);
929 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' organization_id='||p_material_dtl_rec.organization_id);
930 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' g_org_locator_control='||gme_common_pvt.g_org_locator_control);
931 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' subinventory='||p_material_dtl_rec.subinventory);
932 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' inventory_item_id='||p_item_rec.inventory_item_id);
933 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' location_control_code='||p_item_rec.location_control_code);
934 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' restrict_locators_code='||p_item_rec.restrict_locators_code);
935 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_action='||gme_common_pvt.g_ing_issue_txn_action);
936 END IF;
937 IF l_eff_locator_control <> 1 THEN
938 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
939 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' locator on material is NULL and material is eff locator control; cant get quantities from inventory; get exceptions');
940 END IF;
941 RAISE error_get_exception;
942 END IF;
943 END IF;
944
945 gme_transactions_pvt.query_quantities
946 (
947 p_api_version_number => 1
948 ,p_init_msg_lst => fnd_api.g_false
949 ,x_return_status => l_return_status
950 ,x_msg_count => l_msg_count
951 ,x_msg_data => l_msg_data
952 ,p_organization_id => p_material_dtl_rec.organization_id
953 ,p_inventory_item_id => p_material_dtl_rec.inventory_item_id
954 ,p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
955 ,p_is_serial_control => FALSE
956 ,p_grade_code => NULL
957 ,p_demand_source_type_id => gme_common_pvt.g_txn_source_type
958 ,p_demand_source_header_id => p_material_dtl_rec.batch_id
959 ,p_demand_source_line_id => p_material_dtl_rec.material_detail_id
960 ,p_demand_source_name => NULL
961 ,p_lot_expiration_date => NULL
962 ,p_revision => l_revision
963 ,p_lot_number => NULL
964 ,p_subinventory_code => l_subinv
965 ,p_locator_id => l_locator_id
966 ,p_onhand_source => inv_quantity_tree_pvt.g_all_subs
967 ,x_qoh => l_qoh
968 ,x_rqoh => l_rqoh
969 ,x_qr => l_qr
970 ,x_qs => l_qs
971 ,x_att => l_att
972 ,x_atr => l_atr
973 ,x_sqoh => l_sqoh
974 ,x_srqoh => l_srqoh
975 ,x_sqr => l_sqr
976 ,x_sqs => l_sqs
977 ,x_satt => l_satt
978 ,x_satr => l_satr
979 ,p_transfer_subinventory_code => NULL
980 ,p_cost_group_id => NULL
981 ,p_lpn_id => NULL
982 ,p_transfer_locator_id => NULL
983 );
984
985 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
986 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities return status='||l_return_status);
987 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities att='||l_att);
988 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities qoh='||l_qoh);
989 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities item_id='||p_material_dtl_rec.inventory_item_id);
990 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities sub='||l_subinv);
991 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities loc='||l_locator_id);
992 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities rev='||l_revision);
993 END IF;
994
995 -- Bug 7709971 - Restructure this condition to handle orgs that allow negative inventory.
996 -- g_allow_neg_inv: 2 means do not allow neg inv whereas 1 means allow it.
997 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
998 IF l_att > 0 AND gme_common_pvt.g_allow_neg_inv = 2 THEN
999 build_and_create_transaction
1000 (p_rsrv_rec => NULL
1001 ,p_subinv => l_subinv
1002 ,p_locator_id => l_locator_id
1003 ,p_att => l_att
1004 ,p_satt => l_satt
1005 ,p_primary_uom_code => p_item_rec.primary_uom_code
1006 ,p_mtl_dtl_rec => p_material_dtl_rec
1007 ,p_trans_date => l_trans_date
1008 ,p_consume_qty => l_consume_qty
1009 ,p_revision => l_revision
1010 ,p_secondary_uom_code => p_item_rec.secondary_uom_code
1011 ,x_actual_qty => x_actual_qty
1012 ,x_return_status => l_return_status);
1013
1014 IF l_return_status NOT IN (gme_common_pvt.g_not_transactable, FND_API.G_RET_STS_SUCCESS) THEN
1015 x_return_status := l_return_status;
1016 RAISE error_build_trxn;
1017 END IF;
1018 ELSIF gme_common_pvt.g_allow_neg_inv = 1 THEN
1019 -- If we are here, we are going to build a transaction based on the l_consume_qty
1020 -- even if this drives inventory negative or if inventory is already negative.
1021
1022 -- Let's set the secondary_qty to consumed if the user is working in secondary UOM.
1023 l_satt := NULL;
1024 IF (p_item_rec.secondary_uom_code = p_material_dtl_rec.dtl_um
1025 and p_item_rec.secondary_uom_code IS NOT NULL) THEN
1026 l_satt := l_consume_qty;
1027 END IF;
1028
1029 -- Introduced new value for p_called_by to be used by function being called.
1030 -- l_consume_qty is always in the detail uom.
1031 build_and_create_transaction
1032 (p_rsrv_rec => NULL
1033 ,p_subinv => l_subinv
1034 ,p_locator_id => l_locator_id
1035 ,p_att => l_consume_qty
1036 ,p_satt => l_satt
1037 ,p_primary_uom_code => p_item_rec.primary_uom_code
1038 ,p_mtl_dtl_rec => p_material_dtl_rec
1039 ,p_trans_date => l_trans_date
1040 ,p_consume_qty => l_consume_qty
1041 ,p_called_by => 'REL2'
1042 ,p_revision => l_revision
1043 ,p_secondary_uom_code => p_item_rec.secondary_uom_code
1044 ,x_actual_qty => x_actual_qty
1045 ,x_return_status => l_return_status);
1046 IF l_return_status NOT IN (gme_common_pvt.g_not_transactable, FND_API.G_RET_STS_SUCCESS) THEN
1047 x_return_status := l_return_status;
1048 RAISE error_build_trxn;
1049 END IF;
1050 -- done!
1051 RAISE consume_done;
1052 END IF;
1053 END IF;
1054
1055 IF x_actual_qty < l_consume_qty THEN
1056 RAISE error_get_exception;
1057 END IF;
1058
1059 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1060 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1061 END IF;
1062
1063 EXCEPTION
1064 WHEN error_build_trxn OR error_get_item OR error_convert_partial OR consume_done OR
1065 error_get_reservations OR no_consume_required THEN
1066 NULL;
1067 WHEN error_get_exception THEN
1068 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1069 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' exception block for get exceptions:');
1070 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
1071 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_start_actual_qty='||l_start_actual_qty);
1072 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_consume_qty='||l_consume_qty);
1073 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_exception_qty='||(l_consume_qty - x_actual_qty));
1074 END IF;
1075
1076 create_batch_exception
1077 (p_material_dtl_rec => p_material_dtl_rec
1078 ,p_pending_move_order_ind => l_pending_mo_ind
1079 ,p_pending_rsrv_ind => l_pending_rsrv_ind
1080 ,p_transacted_qty => x_actual_qty - l_start_actual_qty
1081 ,p_exception_qty => l_consume_qty - x_actual_qty
1082 ,p_force_unconsumed => fnd_api.g_true
1083 ,x_exception_material_tbl => x_exception_material_tbl
1084 ,x_return_status => x_return_status);
1085 WHEN error_bad_trans_date THEN
1086 x_return_status := FND_API.g_ret_sts_unexp_error;
1087 WHEN error_unexpected THEN
1088 x_return_status := FND_API.g_ret_sts_unexp_error;
1089 WHEN OTHERS THEN
1090 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1091 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
1092 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
1093 END IF;
1094 x_return_status := FND_API.g_ret_sts_unexp_error;
1095 END consume_material;
1096
1097 PROCEDURE build_and_create_transaction
1098 (p_rsrv_rec IN mtl_reservations%ROWTYPE
1099 ,p_lot_divisible_flag IN VARCHAR2 DEFAULT NULL -- required for lot non divisible
1100 ,p_dispense_ind IN VARCHAR2 DEFAULT NULL
1101 ,p_subinv IN VARCHAR2 DEFAULT NULL
1102 ,p_locator_id IN NUMBER DEFAULT NULL
1103 ,p_att IN NUMBER DEFAULT NULL
1104 ,p_satt IN NUMBER DEFAULT NULL
1105 ,p_primary_uom_code IN VARCHAR2 DEFAULT NULL
1106 ,p_mtl_dtl_rec IN gme_material_details%ROWTYPE
1107 ,p_trans_date IN DATE
1108 ,p_consume_qty IN NUMBER
1109 ,p_called_by IN VARCHAR2 DEFAULT 'REL'
1110 ,p_revision IN VARCHAR2 DEFAULT NULL
1111 ,p_secondary_uom_code IN VARCHAR2 DEFAULT NULL
1112 ,x_actual_qty IN OUT NOCOPY NUMBER
1113 ,x_return_status OUT NOCOPY VARCHAR2) IS
1114
1115 CURSOR item_no_cursor(v_inventory_item_id NUMBER,
1116 v_org_id NUMBER) IS
1117 SELECT concatenated_segments
1118 FROM mtl_system_items_kfv
1119 WHERE inventory_item_id = v_inventory_item_id
1120 AND organization_id = v_org_id;
1121
1122 l_item_no mtl_system_items_kfv.concatenated_segments%TYPE;
1123
1124 l_api_name CONSTANT VARCHAR2 (30) := 'build_and_create_transaction';
1125
1126 l_transaction_rec mtl_transactions_interface%ROWTYPE;
1127 l_lot_rec gme_common_pvt.mtl_trans_lots_inter_tbl;
1128 l_rsrv_mode BOOLEAN;
1129 l_trxn_qty NUMBER;
1130 l_dtl_qty NUMBER;
1131 l_prim_qty NUMBER;
1132 l_sec_qty NUMBER;
1133 l_whole_qty BOOLEAN;
1134 l_from_um VARCHAR2(3);
1135 l_to_um VARCHAR2(3);
1136 l_primary_um VARCHAR2(3);
1137 l_dtl_um VARCHAR2(3);
1138 l_return_status VARCHAR2(1);
1139 l_lot_divisible_flag VARCHAR2(1);
1140 --Bug 4899399
1141 l_msg_count NUMBER;
1142 l_msg_data VARCHAR2(32767);
1143 error_build_mmti EXCEPTION;
1144 error_get_dtl_qty EXCEPTION;
1145 error_create_trxn EXCEPTION;
1146 error_relieve_rsrv EXCEPTION;
1147 um_convert_error EXCEPTION;
1148 dispense_error EXCEPTION;
1149 BEGIN
1150 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1151 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1152 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_rsrv_rec.reservation_id='||p_rsrv_rec.reservation_id);
1153 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_rsrv_rec.lot_number='||p_rsrv_rec.lot_number);
1154 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_lot_divisible_flag='||p_lot_divisible_flag);
1155 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_subinv='||p_subinv);
1156 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_locator_id='||p_locator_id);
1157 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_att='||p_att);
1158 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_satt='||p_satt);
1159 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_primary_uom_code='||p_primary_uom_code);
1160 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_mtl_dtl_rec.material_detail_id='||p_mtl_dtl_rec.material_detail_id);
1161 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_mtl_dtl_rec.dtl_um='||p_mtl_dtl_rec.dtl_um);
1162 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_trans_date='||to_char(p_trans_date
1163 ,'YYYY-MON-DD HH24:MI:SS'));
1164 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_consume_qty='||p_consume_qty);
1165 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_revision='||p_revision);
1166 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_secondary_uom_code='||p_secondary_uom_code);
1167 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
1168 END IF;
1169
1170 /* Set the return status to success initially */
1171 x_return_status := FND_API.G_RET_STS_SUCCESS;
1172
1173 IF p_rsrv_rec.reservation_id IS NOT NULL THEN
1174 l_rsrv_mode := TRUE;
1175 ELSE
1176 l_rsrv_mode := FALSE;
1177 END IF;
1178
1179 IF l_rsrv_mode THEN
1180 constr_mmti_from_reservation
1181 (p_rsrv_rec => p_rsrv_rec
1182 ,x_mmti_rec => l_transaction_rec
1183 ,x_mmli_tbl => l_lot_rec
1184 ,x_return_status => x_return_status);
1185 --
1186 -- bug 12695713
1187 -- If the revision is null which would be becasue the Reservations on the
1188 -- GME batch form dont have the revision information, then set the revision
1189 -- passed
1190 --
1191 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after constr_mmti_from_reservation revision='||l_transaction_rec.revision);
1192 IF (l_transaction_rec.revision IS NULL) THEN
1193 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after constr_mmti_from_reservation revision is NULL so setting '||p_revision);
1194 l_transaction_rec.revision := p_revision;
1195 END IF;
1196 ELSE
1197 constr_mmti_from_qty_tree
1198 (p_mtl_dtl_rec => p_mtl_dtl_rec
1199 ,p_subinv => p_subinv
1200 ,p_locator_id => p_locator_id
1201 ,x_mmti_rec => l_transaction_rec
1202 ,x_return_status => x_return_status);
1203 l_transaction_rec.revision := p_revision;
1204 END IF;
1205
1206 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1207 RAISE error_build_mmti;
1208 END IF;
1209
1210 IF l_rsrv_mode THEN
1211 l_prim_qty := p_rsrv_rec.primary_reservation_quantity;
1212 l_sec_qty := p_rsrv_rec.secondary_reservation_quantity;
1213
1214 gme_reservations_pvt.get_reservation_dtl_qty
1215 (p_reservation_rec => p_rsrv_rec
1216 ,p_uom_code => p_mtl_dtl_rec.dtl_um
1217 ,x_qty => l_dtl_qty
1218 ,x_return_status => x_return_status);
1219
1220 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1221 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1222 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' gme_reservations_pvt.get_reservation_dtl_qty returned error');
1223 END IF;
1224
1225 RAISE error_get_dtl_qty;
1226 END IF;
1227
1228 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1229 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' get_reservation_dtl_qty returned l_dtl_qty='||l_dtl_qty);
1230 END IF;
1231 ELSE
1232 l_prim_qty := p_att;
1233 l_sec_qty := p_satt;
1234 l_dtl_qty := NULL;
1235
1236 l_primary_um := p_primary_uom_code;
1237 l_dtl_um := p_mtl_dtl_rec.dtl_um;
1238
1239 -- Bug 7709971 - Introduce this block to derive the values differently.
1240 IF (p_called_by = 'REL2') THEN
1241
1242 -- If we are here this means that p_att is in the dtl_uom which could be secondary.
1243 -- Note: p_att and p_satt will be the same value when detail line is in secondary uom.
1244 l_dtl_qty := p_att;
1245
1246 -- Let's derive secondary qty's if necessary.
1247 IF (p_secondary_uom_code IS NOT NULL) THEN
1248 -- If secondary qty is passed in then it means that user is working in secondary qty on the batch.
1249 IF (p_satt IS NULL) THEN
1250
1251 -- Bug 12813284 - Initialize variables for error message.
1252 l_from_um := l_dtl_um;
1253 l_to_um := p_secondary_uom_code;
1254
1255 -- We need to derive secondary from the dtl qty
1256 l_sec_qty := inv_convert.inv_um_convert
1257 (item_id => p_mtl_dtl_rec.inventory_item_id
1258 ,precision => gme_common_pvt.g_precision
1259 ,from_quantity => l_dtl_qty
1260 ,from_unit => l_dtl_um
1261 ,to_unit => p_secondary_uom_code
1262 ,from_name => NULL
1263 ,to_name => NULL);
1264
1265 -- Let's see if conversion went wrong.
1266 IF (NVL(l_sec_qty, 0) = -99999) THEN
1267 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1268 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' REL2 - PRIM TO SEC - inv_convert.inv_um_convert returned error');
1269 END IF;
1270 RAISE um_convert_error;
1271 END IF;
1272 ELSE
1273 -- Bug 12813284 - Initialize variables for error message.
1274 l_from_um := p_secondary_uom_code;
1275 l_to_um := l_dtl_um;
1276
1277 -- This means that we need to derive the dtl qty from the secondary qty.
1278 l_dtl_qty := inv_convert.inv_um_convert
1279 (item_id => p_mtl_dtl_rec.inventory_item_id
1280 ,precision => gme_common_pvt.g_precision
1281 ,from_quantity => l_sec_qty
1282 ,from_unit => p_secondary_uom_code
1283 ,to_unit => l_dtl_um
1284 ,from_name => NULL
1285 ,to_name => NULL);
1286
1287 -- Let's see if conversion went wrong.
1288 IF (l_dtl_qty = -99999) THEN
1289 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1290 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' REL2 - SEC TO PRIM - inv_convert.inv_um_convert returned error');
1291 END IF;
1292 RAISE um_convert_error;
1293 END IF;
1294 END IF;
1295 END IF;
1296
1297 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1298 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after call to inv_convert.inv_um_convert');
1299 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_dtl_qty= '||to_char(l_dtl_qty));
1300 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_sec_qty= '||to_char(l_sec_qty));
1301 END IF;
1302 -- We now have in hand both the dtl and secondary qty in the correct UOM.
1303
1304 l_prim_qty := l_dtl_qty;
1305 IF (l_primary_um <> l_dtl_um) THEN
1306 l_prim_qty := NULL;
1307 END IF;
1308 END IF; -- p_called_by = 'REL2'
1309
1310 -- Here we always have the secondary qty in the correct UOM if it is dual controlled.
1311 -- Also, we have either the primary qty or detail qty.
1312 -- We may have both if it came via REL2 code and primary and dtl uom are the same.
1313
1314 IF (l_prim_qty IS NULL) THEN
1315 -- Bug 7709971 - Do not do conversion unnecessarily.
1316 -- If the primary is NOT the same as the dtl uom then we are trying to calculate
1317 -- the primary qty since we already have the detail qty.
1318 l_prim_qty := l_dtl_qty;
1319 IF (l_primary_um <> l_dtl_um) THEN
1320
1321 -- Bug 12813284 - Initialize variables for error message.
1322 l_from_um := l_dtl_um;
1323 l_to_um := l_primary_um;
1324
1325 -- Bug 8741777 changed assignment from l_dtl_qty to l_prim_qty
1326 -- as it is conversion to primary qty
1327 l_prim_qty := inv_convert.inv_um_convert
1328 (item_id => p_mtl_dtl_rec.inventory_item_id
1329 ,precision => gme_common_pvt.g_precision
1330 ,from_quantity => l_dtl_qty
1331 ,from_unit => l_dtl_um
1332 ,to_unit => l_primary_um
1333 ,from_name => NULL
1334 ,to_name => NULL);
1335
1336 IF l_prim_qty = -99999 THEN
1337 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1338 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' DTL TO PRIM - inv_convert.inv_um_convert returned error');
1339 END IF;
1340 RAISE um_convert_error;
1341 END IF;
1342
1343 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1344 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' DTL TO PRIM - after call to inv_convert.inv_um_convert');
1345 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_prim_qty= '||to_char(l_prim_qty));
1346 END IF;
1347 END IF;
1348 END IF;
1349
1350 IF (l_dtl_qty IS NULL) THEN
1351 -- If the primary is NOT the same as the dtl uom then we are trying to calculate
1352 -- the detail qty since we already have the primary qty.
1353 l_dtl_qty := l_prim_qty;
1354 IF (l_primary_um <> l_dtl_um) THEN
1355
1356 -- Bug 12813284 - Initialize variables for error message.
1357 l_from_um := l_primary_um;
1358 l_to_um := l_dtl_um;
1359
1360 l_dtl_qty := inv_convert.inv_um_convert
1361 (item_id => p_mtl_dtl_rec.inventory_item_id
1362 ,precision => gme_common_pvt.g_precision
1363 ,from_quantity => l_prim_qty
1364 ,from_unit => l_primary_um
1365 ,to_unit => l_dtl_um
1366 ,from_name => NULL
1367 ,to_name => NULL);
1368
1369 IF l_dtl_qty = -99999 THEN
1370 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1371 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' PRIM TO DTL - inv_convert.inv_um_convert returned error');
1372 END IF;
1373 RAISE um_convert_error;
1374 END IF;
1375
1376 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1377 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after call to inv_convert.inv_um_convert');
1378 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_dtl_qty= '||to_char(l_dtl_qty));
1379 END IF;
1380 END IF;
1381 END IF;
1382 END IF;
1383
1384 IF p_rsrv_rec.lot_number IS NOT NULL AND
1385 NVL(p_lot_divisible_flag,'Y') = 'N' THEN
1386 l_whole_qty := TRUE;
1387 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1388 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_whole_qty = TRUE because lot indivisible item');
1389 END IF;
1390 ELSE
1391 l_whole_qty := FALSE;
1392 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1393 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_whole_qty = FALSE');
1394 END IF;
1395 END IF;
1396
1397 -- test again for dispensed items
1398 IF NOT l_whole_qty THEN
1399 IF NVL(p_dispense_ind,'N') = 'Y' THEN
1400 l_whole_qty := TRUE;
1401 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1402 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_whole_qty = TRUE because dispensed item');
1403 END IF;
1404 END IF;
1405 END IF;
1406
1407 /* Original code
1408 IF l_dtl_qty <= p_consume_qty - x_actual_qty OR l_whole_qty THEN
1409 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1410 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_trxn_qty := l_dtl_qty');
1411 END IF;
1412 l_trxn_qty := l_dtl_qty;
1413 ELSE
1414 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1415 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_trxn_qty := p_consume_qty - x_actual_qty');
1416 END IF;
1417 l_trxn_qty := p_consume_qty - x_actual_qty;
1418 l_prim_qty := NULL;
1419 l_sec_qty := NULL;
1420 END IF;
1421 */
1422
1423 -- Bug 6778968 - Restructured code to derive l_trxn_qty. This is the qty passed into the INV api
1424 -- to relieve the reservation and convert into a transaction. Also, this code is now usable not
1425 -- only for release batch but also the convert detail rservation api.
1426
1427 -- Default trxn_qty to the most likely value which is from release batch flow.
1428 l_trxn_qty := p_consume_qty - x_actual_qty;
1429 IF (p_called_by = 'CVT') THEN
1430 -- If being called from convert detail reservation api, reset the value to qty passed in.
1431 l_trxn_qty := p_consume_qty;
1432 END IF;
1433
1434 -- If the resrvation qty (l_dtl_qty) hass less than what is being requested consume all of it.
1435 IF l_dtl_qty <= l_trxn_qty OR l_whole_qty THEN
1436 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1437 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_trxn_qty := l_dtl_qty');
1438 END IF;
1439 l_trxn_qty := l_dtl_qty;
1440 ELSE
1441 -- Transaction qty is set above. Just set other two variables.
1442 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1443 IF (p_called_by = 'CVT') THEN
1444 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_trxn_qty := p_consume_qty');
1445 ELSE
1446 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_trxn_qty := p_consume_qty - x_actual_qty');
1447 END IF;
1448 END IF;
1449 l_prim_qty := NULL;
1450 l_sec_qty := NULL;
1451 END IF;
1452
1453 l_transaction_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
1454 l_transaction_rec.transaction_date := p_trans_date;
1455 l_transaction_rec.transaction_quantity := l_trxn_qty;
1456 l_transaction_rec.secondary_uom_code := p_secondary_uom_code;
1457
1458 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1459 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' transaction_date='||to_char(l_transaction_rec.transaction_date
1460 ,'YYYY-MON-DD HH24:MI:SS'));
1461 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' transaction_qty='||l_transaction_rec.transaction_quantity);
1462 END IF;
1463
1464 IF l_prim_qty IS NOT NULL THEN
1465 l_transaction_rec.primary_quantity := l_prim_qty;
1466 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1467 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' primary_qty is known:'||l_transaction_rec.primary_quantity);
1468 END IF;
1469 END IF;
1470 IF l_sec_qty IS NOT NULL THEN
1471 l_transaction_rec.secondary_transaction_quantity := l_sec_qty;
1472 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1473 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' secondary_transaction_quantity is known:'||l_transaction_rec.secondary_transaction_quantity);
1474 END IF;
1475 END IF;
1476
1477 l_transaction_rec.transaction_uom := p_mtl_dtl_rec.dtl_um;
1478
1479 -- if the item is dual, this should be passed in, if not dual, this should be NULL
1480 l_transaction_rec.secondary_uom_code := p_secondary_uom_code;
1481
1482 IF l_lot_rec.count > 0 THEN
1483 IF l_lot_rec(1).lot_number IS NOT NULL THEN
1484 l_lot_rec(1).transaction_quantity := l_transaction_rec.transaction_quantity;
1485 IF l_prim_qty IS NOT NULL THEN
1486 l_lot_rec(1).primary_quantity := l_prim_qty;
1487 END IF;
1488 IF l_sec_qty IS NOT NULL THEN
1489 l_lot_rec(1).secondary_transaction_quantity := l_sec_qty;
1490 END IF;
1491 END IF;
1492 END IF;
1493
1494 gme_transactions_pvt.create_material_txn
1495 (p_mmti_rec => l_transaction_rec
1496 ,p_mmli_tbl => l_lot_rec
1497 ,x_return_status => x_return_status);
1498
1499 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1500 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1501 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' gme_transactions_pvt.create_transaction returned '||x_return_status);
1502 END IF;
1503 RAISE error_create_trxn;
1504 END IF;
1505
1506 x_actual_qty := x_actual_qty + l_trxn_qty;
1507
1508 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1509 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
1510 END IF;
1511
1512 IF l_rsrv_mode THEN
1513 IF l_prim_qty IS NULL THEN
1514 -- need to consider lot conversion as well
1515 l_from_um := p_mtl_dtl_rec.dtl_um;
1516 l_to_um := p_rsrv_rec.primary_uom_code;
1517 l_prim_qty := inv_convert.inv_um_convert
1518 (item_id => p_mtl_dtl_rec.inventory_item_id
1519 ,precision => gme_common_pvt.g_precision
1520 ,from_quantity => l_trxn_qty
1521 ,from_unit => l_from_um
1522 ,to_unit => l_to_um
1523 ,from_name => NULL
1524 ,to_name => NULL);
1525 IF l_prim_qty = -99999 THEN
1526 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1527 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' rsrv_mode - DTL to PRIM - inv_convert.inv_um_convert returned error');
1528 END IF;
1529 RAISE um_convert_error;
1530 END IF;
1531 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1532 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' calculated prim qty for call to gme_reservations_pvt.relieve_reservation: l_prim_qty= '||to_char(l_prim_qty));
1533 END IF;
1534 END IF;
1535 gme_reservations_pvt.relieve_reservation
1536 (p_reservation_id => p_rsrv_rec.reservation_id
1537 ,p_prim_relieve_qty => l_prim_qty
1538 ,x_return_status => x_return_status);
1539
1540 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1541 RAISE error_relieve_rsrv;
1542 END IF;
1543
1544 -- Bug 4899399 - after relieving the reservation, informing the GMO about the transaction.
1545 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1546 gme_debug.put_line (g_pkg_name||'.'||l_api_name||'dispense_ind'||p_mtl_dtl_rec.dispense_ind);
1547 gme_debug.put_line (g_pkg_name||'.'||l_api_name||'trans type_id'||l_transaction_rec.transaction_type_id);
1548 gme_debug.put_line (g_pkg_name||'.'||l_api_name||'ext sour line_id'||p_rsrv_rec.external_source_line_id);
1549
1550 END IF;
1551 IF NVL(p_dispense_ind,'N') = 'Y' THEN
1552 IF l_transaction_rec.transaction_type_id = gme_common_pvt.g_ing_issue THEN
1553 -- For consume
1554 GMO_DISPENSE_GRP.CHANGE_DISPENSE_STATUS
1555 (p_api_version => 1.0,
1556 p_init_msg_list => 'F',
1557 p_commit => 'F',
1558 x_return_status => l_return_status,
1559 x_msg_count => l_msg_count,
1560 x_msg_data => l_msg_data,
1561 p_dispense_id => p_rsrv_rec.external_source_line_id,
1562 p_status_code => 'CNSUMED',
1563 p_transaction_id => null
1564 ) ;
1565 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1566 RAISE dispense_error;
1567 END IF;
1568 END IF;
1569 END IF;
1570 END IF;
1571
1572 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1573 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1574 END IF;
1575
1576
1577 EXCEPTION
1578 WHEN um_convert_error THEN
1579 OPEN item_no_cursor(p_mtl_dtl_rec.inventory_item_id, p_mtl_dtl_rec.organization_id);
1580 FETCH item_no_cursor INTO l_item_no;
1581 CLOSE item_no_cursor;
1582
1583 fnd_message.set_name ('GMI', 'IC_API_UOM_CONVERSION_ERROR');
1584 fnd_message.set_token ('ITEM_NO', l_item_no);
1585 fnd_message.set_token ('FROM_UOM',l_from_um);
1586 fnd_message.set_token ('TO_UOM', l_to_um);
1587 fnd_msg_pub.ADD;
1588 x_return_status := FND_API.g_ret_sts_error;
1589 WHEN error_create_trxn OR error_build_mmti OR error_get_dtl_qty OR error_relieve_rsrv THEN
1590 NULL;
1591 WHEN dispense_error THEN
1592 x_return_status := fnd_api.g_ret_sts_error;
1593 WHEN OTHERS THEN
1594 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1595 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
1596 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
1597 END IF;
1598 x_return_status := FND_API.g_ret_sts_unexp_error;
1599 END build_and_create_transaction;
1600
1601
1602
1603 PROCEDURE constr_mmti_from_reservation
1604 (p_rsrv_rec IN mtl_reservations%ROWTYPE
1605 ,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
1606 ,x_mmli_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_inter_tbl
1607 ,x_return_status OUT NOCOPY VARCHAR2) IS
1608
1609 l_api_name CONSTANT VARCHAR2 (30) := 'CONSTR_MMTI_FROM_RESERVATION';
1610 BEGIN
1611
1612 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1613 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1614 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' reservation_id='||p_rsrv_rec.reservation_id);
1615 END IF;
1616
1617 /* Initially let us assign the return status to success */
1618 x_return_status := FND_API.g_ret_sts_success;
1619
1620 -- consturct mtl_transactions_interface
1621 x_mmti_rec.transaction_source_id := p_rsrv_rec.demand_source_header_id; -- batch_id
1622 x_mmti_rec.trx_source_line_id := p_rsrv_rec.demand_source_line_id; -- material_detail_id
1623 x_mmti_rec.inventory_item_id := p_rsrv_rec.inventory_item_id;
1624 x_mmti_rec.organization_id := p_rsrv_rec.organization_id;
1625 x_mmti_rec.subinventory_code := p_rsrv_rec.subinventory_code;
1626 x_mmti_rec.locator_id := p_rsrv_rec.locator_id;
1627 x_mmti_rec.revision := p_rsrv_rec.revision;
1628
1629 x_mmti_rec.transaction_sequence_id := p_rsrv_rec.reservation_id;
1630
1631 -- channges for GMO
1632 x_mmti_rec.transaction_reference := p_rsrv_rec.external_source_line_id ;
1633 -- construct mtl_transaction_lots_interface
1634 IF p_rsrv_rec.lot_number IS NOT NULL THEN
1635 x_mmli_tbl(1).lot_number := p_rsrv_rec.lot_number;
1636 END IF;
1637 -- Bug 6437252 LPN Support
1638 IF p_rsrv_rec.lpn_id IS NOT NULL THEN
1639 x_mmti_rec.lpn_id := p_rsrv_rec.lpn_id;
1640 END IF;
1641 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1642 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1643 END IF;
1644
1645
1646 EXCEPTION
1647 WHEN FND_API.G_EXC_ERROR THEN
1648 x_return_status := FND_API.G_RET_STS_ERROR;
1649
1650 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1652
1653 WHEN OTHERS THEN
1654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1655 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME , l_api_name);
1656
1657 END constr_mmti_from_reservation;
1658
1659 PROCEDURE constr_mmti_from_qty_tree
1660 (p_mtl_dtl_rec IN gme_material_details%ROWTYPE
1661 ,p_subinv IN VARCHAR2
1662 ,p_locator_id IN NUMBER
1663 ,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
1664 ,x_return_status OUT NOCOPY VARCHAR2) IS
1665
1666 l_api_name CONSTANT VARCHAR2 (30) := 'CONSTR_MMTI_FROM_QTY_TREE';
1667 BEGIN
1668
1669 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1670 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1671 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' material_detail_id='||p_mtl_dtl_rec.material_detail_id);
1672 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_subinv='||p_subinv);
1673 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_locator_id='||p_locator_id);
1674 END IF;
1675
1676 /* Initially let us assign the return status to success */
1677 x_return_status := FND_API.g_ret_sts_success;
1678
1679 -- consturct mtl_transactions_interface
1680 x_mmti_rec.transaction_source_id := p_mtl_dtl_rec.batch_id;
1681 x_mmti_rec.trx_source_line_id := p_mtl_dtl_rec.material_detail_id;
1682 x_mmti_rec.inventory_item_id := p_mtl_dtl_rec.inventory_item_id;
1683 x_mmti_rec.organization_id := p_mtl_dtl_rec.organization_id;
1684 x_mmti_rec.subinventory_code := p_subinv;
1685 x_mmti_rec.locator_id := p_locator_id;
1686
1687 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1688 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1689 END IF;
1690
1691 EXCEPTION
1692 WHEN FND_API.G_EXC_ERROR THEN
1693 x_return_status := FND_API.G_RET_STS_ERROR;
1694
1695 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1697
1698 WHEN OTHERS THEN
1699 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1700 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME , l_api_name);
1701
1702 END constr_mmti_from_qty_tree;
1703
1704 PROCEDURE create_batch_exception
1705 (p_material_dtl_rec IN gme_material_details%ROWTYPE
1706 ,p_pending_move_order_ind IN BOOLEAN := NULL
1707 ,p_pending_rsrv_ind IN BOOLEAN := NULL
1708 ,p_transacted_qty IN NUMBER := NULL
1709 ,p_exception_qty IN NUMBER := NULL
1710 ,p_force_unconsumed IN VARCHAR2 := fnd_api.g_true
1711 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
1712 ,x_return_status OUT NOCOPY VARCHAR2) IS
1713
1714 l_api_name CONSTANT VARCHAR2 (30) := 'create_batch_exception';
1715
1716 i NUMBER;
1717
1718 l_pending_mo_ind BOOLEAN;
1719 l_pending_rsrv_ind BOOLEAN;
1720 l_display_unconsumed VARCHAR2(1);
1721 l_exceptions_rec gme_exceptions_gtmp%ROWTYPE;
1722
1723 error_insert_exceptions EXCEPTION;
1724 error_no_exception EXCEPTION;
1725 BEGIN
1726 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1727 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1728 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' material_detail_id='||p_material_dtl_rec.material_detail_id);
1729 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' line_no='||p_material_dtl_rec.line_no);
1730 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' line_type='||p_material_dtl_rec.line_type);
1731 IF p_material_dtl_rec.phantom_line_id IS NOT NULL THEN
1732 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' this is a PHANTOM');
1733 END IF;
1734
1735 IF p_pending_move_order_ind IS NULL THEN
1736 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_pending_move_order_ind IS NULL');
1737 ELSIF p_pending_move_order_ind THEN
1738 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_pending_move_order_ind = TRUE');
1739 ELSE
1740 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_pending_move_order_ind = FALSE');
1741 END IF;
1742
1743 IF p_pending_rsrv_ind IS NULL THEN
1744 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_pending_rsrv_ind IS NULL');
1745 ELSIF p_pending_rsrv_ind THEN
1746 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_pending_rsrv_ind = TRUE');
1747 ELSE
1748 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_pending_rsrv_ind = FALSE');
1749 END IF;
1750
1751 IF p_transacted_qty IS NULL THEN
1752 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_transacted_qty IS NULL');
1753 ELSE
1754 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_transacted_qty = '||p_transacted_qty);
1755 END IF;
1756
1757 IF p_exception_qty IS NULL THEN
1758 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_exception_qty IS NULL');
1759 ELSE
1760 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_exception_qty = '||p_exception_qty);
1761 END IF;
1762
1763 END IF;
1764
1765 x_return_status := FND_API.G_RET_STS_SUCCESS;
1766
1767 IF p_force_unconsumed = fnd_api.g_true THEN
1768 l_display_unconsumed := fnd_api.g_true;
1769 ELSIF p_force_unconsumed = fnd_api.g_false THEN
1770 IF gme_common_pvt.g_display_unconsumed_material = 1 THEN
1771 l_display_unconsumed := fnd_api.g_true;
1772 ELSE
1773 l_display_unconsumed := fnd_api.g_false;
1774 END IF;
1775 END IF;
1776
1777 IF p_material_dtl_rec.line_type = gme_common_pvt.g_line_type_ing AND p_material_dtl_rec.phantom_line_id IS NOT NULL THEN
1778 -- don't report the phantom ingredients; just return; phantom products will be reported
1779 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1780 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' called for phantom ingredient... returning, only report for phantom product');
1781 END IF;
1782 RAISE error_no_exception;
1783 END IF;
1784
1785 IF p_pending_move_order_ind IS NULL AND p_material_dtl_rec.line_type = gme_common_pvt.g_line_type_ing THEN
1786 l_pending_mo_ind := gme_move_orders_pvt.pending_move_orders_exist
1787 (p_organization_id => p_material_dtl_rec.organization_id
1788 ,p_batch_id => p_material_dtl_rec.batch_id
1789 ,p_material_detail_id => p_material_dtl_rec.material_detail_id);
1790 ELSE
1791 l_pending_mo_ind := NVL(p_pending_move_order_ind, FALSE);
1792 END IF;
1793
1794 IF p_pending_rsrv_ind IS NULL THEN
1795 IF p_material_dtl_rec.line_type = gme_common_pvt.g_line_type_ing THEN
1796 l_pending_rsrv_ind:= gme_reservations_pvt.pending_reservations_exist
1797 (p_organization_id => p_material_dtl_rec.organization_id
1798 ,p_batch_id => p_material_dtl_rec.batch_id
1799 ,p_material_detail_id => p_material_dtl_rec.material_detail_id);
1800 ELSE
1801 l_pending_rsrv_ind:= gme_pending_product_lots_pvt.pending_product_lot_exist
1802 (p_batch_id => p_material_dtl_rec.batch_id
1803 ,p_material_detail_id => p_material_dtl_rec.material_detail_id);
1804 END IF;
1805 ELSE
1806 l_pending_rsrv_ind := p_pending_rsrv_ind;
1807 END IF;
1808
1809 l_exceptions_rec.organization_id := p_material_dtl_rec.organization_id;
1810
1811 IF l_pending_mo_ind THEN
1812 l_exceptions_rec.pending_move_order_ind := 1;
1813 ELSE
1814 l_exceptions_rec.pending_move_order_ind := 0;
1815 END IF;
1816
1817 IF l_pending_rsrv_ind THEN
1818 l_exceptions_rec.pending_reservations_ind := 1;
1819 ELSE
1820 l_exceptions_rec.pending_reservations_ind := 0;
1821 END IF;
1822
1823 l_exceptions_rec.material_detail_id := p_material_dtl_rec.material_detail_id;
1824 l_exceptions_rec.batch_id := p_material_dtl_rec.batch_id;
1825 l_exceptions_rec.transacted_qty := NVL(p_transacted_qty, p_material_dtl_rec.actual_qty);
1826 l_exceptions_rec.exception_qty := NVL(p_exception_qty, p_material_dtl_rec.plan_qty - p_material_dtl_rec.actual_qty);
1827 l_exceptions_rec.exception_qty := ROUND(l_exceptions_rec.exception_qty, gme_common_pvt.g_precision);
1828
1829 IF l_pending_rsrv_ind OR l_pending_mo_ind OR
1830 (l_display_unconsumed = FND_API.g_true AND l_exceptions_rec.exception_qty > 0) OR
1831 -- next line is for negative IB
1832 (l_display_unconsumed = FND_API.g_true AND p_exception_qty < 0) THEN
1833 i := x_exception_material_tbl.COUNT + 1;
1834 x_exception_material_tbl(i) := l_exceptions_rec;
1835
1836 IF NOT gme_common_pvt.insert_exceptions(p_exception_rec => l_exceptions_rec) THEN
1837 RAISE error_insert_exceptions;
1838 END IF;
1839
1840 x_return_status := gme_common_pvt.g_exceptions_err;
1841 ELSE
1842 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1843 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' exception not found');
1844 END IF;
1845 END IF;
1846
1847 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1848 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name||' with return status= '||x_return_status);
1849 END IF;
1850 EXCEPTION
1851 WHEN error_insert_exceptions THEN
1852 x_return_status := FND_API.G_RET_STS_ERROR;
1853 WHEN error_no_exception THEN
1854 NULL;
1855 WHEN OTHERS THEN
1856 IF nvl(g_debug, gme_debug.g_log_unexpected + 1) <= gme_debug.g_log_unexpected THEN
1857 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
1858 END IF;
1859 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1860 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1861 END create_batch_exception;
1862
1863 /*************************************************************************************/
1864 /* p_auto_by_step Include auto by step */
1865 /* 0 = check for all but auto by step */
1866 /* 1 = check only auto by step */
1867 /* 2 = check all release types */
1868 /* p_batchstep_id used when p_auto_by_step is passed as value = 1 */
1869 /*************************************************************************************/
1870 PROCEDURE check_unexploded_phantom(p_batch_id IN NUMBER
1871 ,p_auto_by_step IN NUMBER
1872 ,p_batchstep_id IN NUMBER
1873 ,x_return_status OUT NOCOPY VARCHAR2) IS
1874
1875 CURSOR cur_get_phantom_ingred(v_batch_id NUMBER) IS
1876 SELECT *
1877 FROM gme_material_details
1878 WHERE batch_id = v_batch_id
1879 AND line_type = gme_common_pvt.g_line_type_ing
1880 AND phantom_type <> 0;
1881
1882 CURSOR Cur_associated_step(v_matl_dtl_id NUMBER) IS
1883 SELECT s.batchstep_id
1884 FROM gme_batch_steps s, gme_batch_step_items item
1885 WHERE s.batchstep_id = item.batchstep_id
1886 AND item.material_detail_id = v_matl_dtl_id;
1887
1888 l_api_name CONSTANT VARCHAR2 (30) := 'check_unexploded_phantom';
1889
1890 l_step_id NUMBER;
1891
1892 l_matl_dtl_tab gme_common_pvt.material_details_tab;
1893
1894 l_matl_dtl_id NUMBER;
1895 l_phantom_id NUMBER;
1896 l_release_type NUMBER;
1897
1898 error_unexp_phantom EXCEPTION;
1899 error_unexp_downstream EXCEPTION;
1900
1901 BEGIN
1902 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1903 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1904 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batch_id='||p_batch_id);
1905 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batchstep_id='||p_batchstep_id);
1906 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' mode='||p_auto_by_step);
1907 END IF;
1908
1909 x_return_status := FND_API.G_RET_STS_SUCCESS;
1910
1911 OPEN cur_get_phantom_ingred(p_batch_id);
1912 FETCH cur_get_phantom_ingred BULK COLLECT INTO l_matl_dtl_tab;
1913 CLOSE cur_get_phantom_ingred;
1914
1915 FOR i in 1..l_matl_dtl_tab.COUNT LOOP
1916 l_matl_dtl_id := l_matl_dtl_tab(i).material_detail_id;
1917 l_phantom_id := l_matl_dtl_tab(i).phantom_id;
1918 l_release_type := l_matl_dtl_tab(i).release_type;
1919
1920 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1921 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' phantom ingredient found: material_detail_id='||l_matl_dtl_id);
1922 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' partner phantom batch id: batch_id='||l_phantom_id);
1923 END IF;
1924
1925 IF p_auto_by_step IN (0,1) AND l_release_type = gme_common_pvt.g_mtl_autobystep_release THEN
1926 OPEN Cur_associated_step(l_matl_dtl_id);
1927 FETCH Cur_associated_step INTO l_step_id;
1928 IF Cur_associated_step%NOTFOUND THEN
1929 l_release_type := gme_common_pvt.g_mtl_auto_release;
1930 END IF;
1931 CLOSE Cur_associated_step;
1932 END IF;
1933
1934 IF ((p_auto_by_step = 0 AND l_release_type <> gme_common_pvt.g_mtl_autobystep_release) OR
1935 (p_auto_by_step = 1 AND
1936 l_step_id = p_batchstep_id AND
1937 l_release_type = gme_common_pvt.g_mtl_autobystep_release) OR
1938 (p_auto_by_step = 2)) THEN
1939 IF l_phantom_id IS NULL THEN
1940 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1941 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' phantom ingredient unexploded: material_detail_id='||l_matl_dtl_id);
1942 END IF;
1943 RAISE error_unexp_phantom;
1944 END IF;
1945
1946 -- check that the phantom batch doesn't have any unexploded phantoms...
1947 -- check for all release types in phantom batch
1948 check_unexploded_phantom(p_batch_id => l_phantom_id
1949 ,p_auto_by_step => 2
1950 ,p_batchstep_id => NULL
1951 ,x_return_status => x_return_status);
1952
1953 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1954 RAISE error_unexp_downstream;
1955 END IF;
1956 END IF;
1957 END LOOP;
1958
1959 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1960 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1961 END IF;
1962
1963 EXCEPTION
1964 WHEN error_unexp_phantom THEN
1965 gme_common_pvt.log_message ('PM_UNEXPLODED_PHANTOMS');
1966 x_return_status := FND_API.G_RET_STS_ERROR;
1967 WHEN error_unexp_downstream THEN
1968 NULL;
1969 WHEN OTHERS THEN
1970 IF nvl(g_debug, gme_debug.g_log_unexpected + 1) <= gme_debug.g_log_unexpected THEN
1971 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
1972 END IF;
1973 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1974 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1975 END check_unexploded_phantom;
1976
1977 PROCEDURE validate_batch_for_release (p_batch_header_rec IN gme_batch_header%ROWTYPE
1978 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
1979 ,x_return_status OUT NOCOPY VARCHAR2) IS
1980
1981 l_api_name CONSTANT VARCHAR2 (30) := 'validate_batch_for_release';
1982
1983 l_batch_header_rec gme_batch_header%ROWTYPE;
1984
1985 CURSOR cur_validity_rule(v_recipe_validity_rule_id NUMBER)
1986 IS
1987 SELECT *
1988 FROM gmd_recipe_validity_rules
1989 WHERE recipe_validity_rule_id = v_recipe_validity_rule_id;
1990
1991 CURSOR cur_validity_status_type(v_validity_rule_status VARCHAR2)
1992 IS
1993 SELECT status_type
1994 FROM gmd_status
1995 WHERE status_code=v_validity_rule_status;
1996
1997 l_validity_rule gmd_recipe_validity_rules%ROWTYPE;
1998 l_status_type GMD_STATUS.status_type%TYPE;
1999
2000 error_batch_type EXCEPTION;
2001 error_batch_status EXCEPTION;
2002 error_phantom EXCEPTION;
2003 error_future_date EXCEPTION;
2004 error_vr_not_found EXCEPTION;
2005 error_validity_status EXCEPTION;
2006 error_vr_dates EXCEPTION;
2007 error_validation EXCEPTION;
2008
2009 BEGIN
2010 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
2011 gme_debug.g_log_procedure THEN
2012 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
2013 || l_api_name);
2014 END IF;
2015
2016 x_return_status := FND_API.g_ret_sts_success;
2017
2018 -- set output structure
2019 x_batch_header_rec := p_batch_header_rec;
2020
2021 IF p_batch_header_rec.batch_type = gme_common_pvt.g_doc_type_fpo THEN
2022 RAISE error_batch_type;
2023 END IF;
2024
2025 IF p_batch_header_rec.batch_status <> gme_common_pvt.g_batch_pending THEN
2026 RAISE error_batch_status;
2027 END IF;
2028
2029 -- set actual start date if it's not passed
2030 IF p_batch_header_rec.actual_start_date IS NULL THEN
2031 x_batch_header_rec.actual_start_date := SYSDATE;
2032 ELSIF p_batch_header_rec.actual_cmplt_date > SYSDATE THEN
2033 RAISE error_future_date;
2034 END IF;
2035
2036 IF p_batch_header_rec.parentline_id IS NOT NULL THEN
2037 RAISE error_phantom;
2038 END IF;
2039
2040 -- check validity rule if it's not NULL; it would be NULL in case of LCF
2041 IF p_batch_header_rec.recipe_validity_rule_id IS NOT NULL THEN
2042 OPEN cur_validity_rule(p_batch_header_rec.recipe_validity_rule_id);
2043 FETCH cur_validity_rule INTO l_validity_rule;
2044 CLOSE cur_validity_rule;
2045
2046 IF l_validity_rule.recipe_validity_rule_id IS NULL THEN -- not found
2047 RAISE error_vr_not_found;
2048 ELSE
2049 -- following prevents user from releasing a pending batch
2050 -- if validity rule is ON_HOLD or OBSOLETE.
2051 OPEN cur_validity_status_type(l_validity_rule.validity_rule_status);
2052 FETCH cur_validity_status_type INTO l_status_type;
2053 CLOSE cur_validity_status_type;
2054
2055 IF l_status_type IN ('1000' ,'800') THEN
2056 RAISE error_validity_status;
2057 END IF;
2058 END IF; -- IF l_validity_rule.recipe_validity_rule_id IS NULL
2059
2060 /* IF l_validity_rule.start_date > x_batch_header_rec.actual_start_date OR
2061 (l_validity_rule.end_date IS NOT NULL AND
2062 l_validity_rule.end_date < x_batch_header_rec.actual_start_date) THEN
2063 RAISE error_vr_dates;
2064 END IF;*/
2065 --sunitha ch. Bug 5336007 aded call to check_validity_rule_dates and passed p_validate_plan_dates_ind=1
2066 --to validate planned start date against validate rule dates
2067 IF NOT gme_common_pvt.check_validity_rule_dates (
2068 p_validity_rule_id => p_batch_header_rec.recipe_validity_rule_id
2069 ,p_start_date => p_batch_header_rec.actual_start_date
2070 ,p_cmplt_date => p_batch_header_rec.actual_cmplt_date
2071 ,p_batch_header_rec => p_batch_header_rec
2072 ,p_validate_plan_dates_ind => 1) THEN
2073 x_return_status := fnd_api.g_ret_sts_error;
2074 RAISE error_vr_dates;
2075 END IF;
2076 -- End Bug 5336007
2077 END IF; -- IF p_batch_header_rec.recipe_validity_rule_id IS NOT NULL
2078
2079 gme_validate_flex_fld_pvt.validate_flex_batch_header
2080 (p_batch_header => p_batch_header_rec
2081 ,x_batch_header => x_batch_header_rec
2082 ,x_return_status => x_return_status);
2083
2084 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2085 RAISE error_validation;
2086 END IF;
2087
2088 check_unexploded_phantom(p_batch_id => p_batch_header_rec.batch_id
2089 ,p_auto_by_step => 0 -- all but auto by step ingredients
2090 ,p_batchstep_id => NULL
2091 ,x_return_status => x_return_status);
2092
2093 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2094 RAISE error_validation;
2095 END IF;
2096
2097 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
2098 gme_debug.g_log_procedure THEN
2099 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2100 END IF;
2101
2102 EXCEPTION
2103 WHEN error_validation THEN
2104 NULL;
2105 WHEN error_vr_dates THEN
2106 x_return_status := FND_API.G_RET_STS_ERROR;
2107 WHEN error_validity_status THEN
2108 gme_common_pvt.log_message ('GME_VALIDITY_OBSO_OR_ONHOLD');
2109 x_return_status := FND_API.G_RET_STS_ERROR;
2110 WHEN error_vr_not_found THEN
2111 gme_common_pvt.log_message ('GME_API_INVALID_VALIDITY');
2112 x_return_status := FND_API.G_RET_STS_ERROR;
2113 WHEN error_phantom THEN
2114 gme_common_pvt.log_message ('PM_INVALID_PHANTOM_ACTION');
2115 x_return_status := FND_API.G_RET_STS_ERROR;
2116 WHEN error_batch_type OR error_batch_status THEN
2117 gme_common_pvt.log_message('GME_API_INVALID_BATCH_REL');
2118 x_return_status := fnd_api.g_ret_sts_error;
2119 WHEN error_future_date THEN
2120 fnd_message.set_name ('GMA', 'SY_NOFUTUREDATE');
2121 fnd_msg_pub.ADD;
2122 x_return_status := fnd_api.g_ret_sts_error;
2123 WHEN OTHERS THEN
2124 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2125
2126 IF g_debug <= gme_debug.g_log_procedure THEN
2127 gme_debug.put_line ( 'Unexpected error: '
2128 || g_pkg_name
2129 || '.'
2130 || l_api_name
2131 || ': '
2132 || SQLERRM);
2133 END IF;
2134
2135 x_return_status := fnd_api.g_ret_sts_unexp_error;
2136 END validate_batch_for_release;
2137
2138 END gme_release_batch_pvt;
[Home] [Help]