[Home] [Help]
PACKAGE BODY: APPS.GME_RELEASE_BATCH_PVT
Source
1 PACKAGE BODY gme_release_batch_pvt AS
2 /* $Header: GMEVRLBB.pls 120.29.12010000.1 2008/07/25 10:31:31 appldev 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
250 GMF_VIB.Create_Batch_Requirements
251 ( p_api_version => 1.0,
252 p_init_msg_list => FND_API.G_FALSE,
253 p_batch_id => x_batch_header_rec.batch_id,
254 x_return_status => l_return_status, --Bug#6507649
255 x_msg_count => l_message_count,
256 x_msg_data => l_message_list);
257
258 IF l_return_status <> FND_API.G_RET_STS_SUCCESS --Bug#6507649 Rework
259 THEN
260 RAISE gmf_cost_failure;
261 END IF;
262 -- End Bug 5903208
263
264 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
265 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);
266 END IF;
267
268 EXCEPTION
269 WHEN gmf_cost_failure THEN
270 -- Bug 5903208
271 x_return_status := FND_API.G_RET_STS_ERROR;
272
273 WHEN error_update_batch OR error_update_row OR error_fetch_material THEN
274 /* Bug 5554841 No need to set messsage it is set by called APIs */
275 --gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
276 x_return_status := FND_API.g_ret_sts_unexp_error;
277 WHEN error_process_ingredient OR error_consume_material OR error_yield_material OR error_get_item THEN
278 NULL;
279 WHEN error_unexp_phantom THEN
280 gme_common_pvt.log_message ('PM_UNEXPLODED_PHANTOMS');
281 x_return_status := FND_API.G_RET_STS_ERROR;
282 WHEN OTHERS THEN
283 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
284 IF g_debug <= gme_debug.g_log_procedure THEN
285 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
286 END IF;
287 x_return_status := FND_API.g_ret_sts_unexp_error;
288 END release_batch;
289
290 PROCEDURE process_ingredient
291 (p_material_detail_rec IN gme_material_details%ROWTYPE
292 ,p_consume IN BOOLEAN
293 ,p_trans_date IN DATE
294 ,p_update_inv_ind IN VARCHAR2
295 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
296 ,x_return_status OUT NOCOPY VARCHAR2) IS
297
298
299 l_api_name CONSTANT VARCHAR2 (30) := 'process_ingredient';
300
301 l_matl_dtl_rec gme_material_details%ROWTYPE;
302 l_in_phantom_batch_rec gme_batch_header%ROWTYPE;
303 l_phantom_batch_rec gme_batch_header%ROWTYPE;
304 l_return_status VARCHAR2(1);
305 l_item_rec mtl_system_items_b%ROWTYPE;
306 l_actual_qty NUMBER;
307 l_update_matl BOOLEAN;
308
309 error_update_row EXCEPTION;
310 error_fetch_batch EXCEPTION;
311 error_release_batch EXCEPTION;
312 error_consume_material EXCEPTION;
313 error_get_item EXCEPTION;
314 error_dispense_non_reserve EXCEPTION;
315 BEGIN
316
317 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
318 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
319 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' material_detail_id='||p_material_detail_rec.material_detail_id);
320 END IF;
321
322 /* Set the return status to success initially */
323 x_return_status := FND_API.G_RET_STS_SUCCESS;
324
325 -- Process the ingredients...
326 -- 1) release phantom batch ingredient
327 -- 2) consume non phantom ingredient
328 -- 3) set wip plan qty
329
330 l_matl_dtl_rec := p_material_detail_rec;
331
332 -- if it's a phantom ingredient, then release the batch and pass the phantom line id
333 -- which will cause the phantom product to be yielded; don't consume this ingredient
334 -- because the ingredient will be taken care of with yield of the product... that's why
335 -- consume is in the else of following if statement...
336
337 -- release phantom batch
338 IF l_matl_dtl_rec.phantom_id IS NOT NULL THEN -- phantom -> release the phantom batch
339 l_phantom_batch_rec.batch_id := l_matl_dtl_rec.phantom_id;
340 IF NOT gme_batch_header_dbl.fetch_row(l_phantom_batch_rec, l_phantom_batch_rec) THEN
341 RAISE error_fetch_batch;
342 END IF;
343
344 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
345 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);
346 END IF;
347 -- pass in the phantom line id so that release batch will know to yield that product
348 l_in_phantom_batch_rec := l_phantom_batch_rec;
349 l_in_phantom_batch_rec.actual_start_date := p_trans_date;
350 release_batch
351 (p_batch_header_rec => l_in_phantom_batch_rec
352 ,p_phantom_product_id => l_matl_dtl_rec.phantom_line_id
353 ,p_yield => p_consume
354 ,x_batch_header_rec => l_phantom_batch_rec
355 ,x_return_status => l_return_status
356 ,x_exception_material_tbl => x_exception_material_tbl);
357
358 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
359 x_return_status := l_return_status;
360 RAISE error_release_batch;
361 END IF;
362
363 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
364 x_return_status := gme_common_pvt.g_exceptions_err;
365 END IF;
366
367 l_update_matl := FALSE;
368
369 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
370 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' after release_batch for phantom batch; it returned x_return_status='||x_return_status);
371 END IF;
372 ELSIF p_consume THEN
373 gme_material_detail_pvt.get_item_rec
374 (p_org_id => l_matl_dtl_rec.organization_id
375 ,p_item_id => l_matl_dtl_rec.inventory_item_id
376 ,x_item_rec => l_item_rec
377 ,x_return_status => l_return_status);
378
379 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
380 x_return_status := l_return_status;
381 RAISE error_get_item;
382 END IF;
383
384 IF p_update_inv_ind = 'Y' AND
385 l_item_rec.mtl_transactions_enabled_flag = 'Y' THEN
386 --Pawan Kumar bug 4742244 --
387 -- check for item which dispensable but non-reservable
388 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
389 gme_debug.put_line(g_pkg_name||'.'||l_api_name||'disp ind'||l_matl_dtl_rec.dispense_ind);
390 gme_debug.put_line(g_pkg_name||'.'||l_api_name||'reservable_type'||l_item_rec.reservable_type);
391 END IF;
392 IF nvl(l_matl_dtl_rec.dispense_ind, 'N' ) = 'Y' AND
393 l_item_rec.reservable_type <> 1 THEN
394 RAISE error_dispense_non_reserve;
395 END IF;
396
397 consume_material(p_material_dtl_rec => l_matl_dtl_rec
398 ,p_trans_date => p_trans_date
399 ,p_item_rec => l_item_rec
400 ,x_exception_material_tbl => x_exception_material_tbl
401 ,x_actual_qty => l_actual_qty
402 ,x_return_status => l_return_status);
403
404 l_matl_dtl_rec.actual_qty := l_actual_qty;
405
406 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
407 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' after consume_material; it returned actual_qty='||l_actual_qty);
408 END IF;
409
410 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
411 x_return_status := l_return_status;
412 RAISE error_consume_material;
413 END IF;
414
415 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
416 x_return_status := gme_common_pvt.g_exceptions_err;
417 END IF;
418 ELSE
419 l_matl_dtl_rec.actual_qty := l_matl_dtl_rec.plan_qty;
420 END IF;
421
422 l_update_matl := TRUE;
423
424 ELSE -- ELSIF p_consume
425 l_update_matl := TRUE;
426 l_matl_dtl_rec.actual_qty := 0;
427 END IF; -- IF l_matl_dtl_rec.phantom_id IS NOT NULL...
428
429 IF l_update_matl THEN
430 -- set WIP plan qty
431 l_matl_dtl_rec.wip_plan_qty := l_matl_dtl_rec.plan_qty;
432
433 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
434 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' before update_row; actual_qty='||l_matl_dtl_rec.actual_qty);
435 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' before update_row; wip_plan_qty='||l_matl_dtl_rec.wip_plan_qty);
436 END IF;
437
438 IF NOT gme_material_details_dbl.update_row (l_matl_dtl_rec) THEN
439 RAISE error_update_row;
440 END IF;
441 END IF;
442
443 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
444 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name||' with x_return_status= '||x_return_status);
445 END IF;
446
447 EXCEPTION
448 WHEN error_update_row OR error_fetch_batch THEN
449 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
450 x_return_status := FND_API.g_ret_sts_unexp_error;
451 WHEN error_release_batch OR error_consume_material OR error_get_item THEN
452 NULL;
453 WHEN error_dispense_non_reserve THEN
454 gme_common_pvt.log_message ('GME_DISPENSE_NON_RESERVE');
455 x_return_status := fnd_api.g_ret_sts_error;
456 WHEN OTHERS THEN
457 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
458 IF g_debug <= gme_debug.g_log_procedure THEN
459 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
460 END IF;
461 x_return_status := FND_API.g_ret_sts_unexp_error;
462 END process_ingredient;
463
464 -- Note: p_consume_qty is the target actual qty; for incr, it's also the target, not the incr
465 PROCEDURE consume_material(p_material_dtl_rec IN gme_material_details%ROWTYPE
466 ,p_consume_qty IN NUMBER := NULL
467 ,p_trans_date IN DATE := NULL
468 ,p_item_rec IN mtl_system_items_b%ROWTYPE
469 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
470 ,x_actual_qty OUT NOCOPY NUMBER
471 ,x_return_status OUT NOCOPY VARCHAR2) IS
472
473 l_api_name CONSTANT VARCHAR2 (30) := 'CONSUME_MATERIAL';
474
475 l_reservation_rec mtl_reservations%ROWTYPE;
476 l_reservation_tab gme_common_pvt.reservations_tab;
477 i NUMBER;
478 l_rsrv_type NUMBER;
479 l_start_actual_qty NUMBER;
480
481 l_PLR_tab gme_common_pvt.reservations_tab;
482 j NUMBER;
483 l_try_PLR BOOLEAN;
484 l_partial_rec mtl_reservations%ROWTYPE;
485 l_pending_mo_ind BOOLEAN := NULL;
486 l_pending_rsrv_ind BOOLEAN := NULL;
487
488 l_consume_qty NUMBER;
489 l_trans_date DATE;
490 l_subinv VARCHAR2(10);
491 l_locator_id NUMBER;
492 l_revision VARCHAR2(3);
493 l_return_status VARCHAR2(1);
494
495 l_qoh NUMBER;
496 l_rqoh NUMBER;
497 l_qr NUMBER;
498 l_qs NUMBER;
499 l_att NUMBER;
500 l_atr NUMBER;
501 l_sqoh NUMBER;
502 l_srqoh NUMBER;
503 l_sqr NUMBER;
504 l_sqs NUMBER;
505 l_satt NUMBER;
506 l_satr NUMBER;
507 l_msg_count NUMBER;
508 l_msg_data VARCHAR2(32767);
509
510 l_eff_locator_control NUMBER;
511
512 CURSOR cur_get_item_revision(v_item_id NUMBER, v_org_id NUMBER) IS
513 SELECT revision
514 FROM mtl_item_revisions_b
515 WHERE inventory_item_id = v_item_id
516 AND organization_id = v_org_id
517 AND effectivity_date <= gme_common_pvt.g_timestamp
518 ORDER BY effectivity_date desc;
519
520 error_get_item EXCEPTION;
521 error_build_trxn EXCEPTION;
522 error_get_exception EXCEPTION;
523 error_convert_partial EXCEPTION;
524 error_unexpected EXCEPTION;
525 consume_done EXCEPTION;
526 error_get_reservations EXCEPTION;
527 no_consume_required EXCEPTION;
528
529 BEGIN
530
531 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
532 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
533 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' material_detail_id='||p_material_dtl_rec.material_detail_id);
534 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' dispense_ind='||p_material_dtl_rec.dispense_ind);
535 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_consume_qty='||p_consume_qty);
536 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_trans_date='||to_char(p_trans_date
537 ,'YYYY-MON-DD HH24:MI:SS'));
538 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' line_no='||p_material_dtl_rec.line_no);
539 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' line_type='||p_material_dtl_rec.line_type);
540 END IF;
541 /* Set the return status to success initially */
542 x_return_status := FND_API.G_RET_STS_SUCCESS;
543
544 -- set the output actual qty to it's current value...
545 x_actual_qty := p_material_dtl_rec.actual_qty;
546
547 -- following global is set only for migration purposes, where transactions need not be created,
548 IF gme_release_batch_pvt.g_bypass_txn_creation = 1 THEN
549 RAISE no_consume_required;
550 END IF;
551
552 l_start_actual_qty := x_actual_qty;
553
554 -- Couple of optimizations...
555 -- If consume from supply sub is set to Yes and there's no supply sub, then return with exceptions... can't do anything
556 -- If consume from supply sub is set to No and there's no supply sub, then consume DLR, and return with exceptions (if appl)
557 -- If reservable is set to No don't bother to retrieve the reservations... there aren't any...
558
559 IF gme_common_pvt.g_auto_consume_supply_sub_only = 1 THEN
560 IF p_material_dtl_rec.subinventory IS NULL THEN
561 l_pending_mo_ind := FALSE; -- can't have move order if sub is NULL
562 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
563 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');
564 END IF;
565
566 RAISE error_get_exception;
567 END IF;
568 END IF;
569
570 l_subinv := p_material_dtl_rec.subinventory;
571 l_locator_id := p_material_dtl_rec.locator_id;
572
573 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
574 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_subinv='||l_subinv);
575 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_locator_id='||l_locator_id);
576 END IF;
577
578 -- channges for GMO
579 gme_reservations_pvt.get_material_reservations
580 (p_organization_id => p_material_dtl_rec.organization_id
581 ,p_batch_id => p_material_dtl_rec.batch_id
582 ,p_material_detail_id => p_material_dtl_rec.material_detail_id
583 ,p_dispense_ind => nvl(p_material_dtl_rec.dispense_ind,'N')
584 ,x_return_status => l_return_status
585 ,x_reservations_tbl => l_reservation_tab);
586
587 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
588 x_return_status := l_return_status;
589 RAISE error_get_reservations;
590 END IF;
591
592 l_consume_qty := NVL(p_consume_qty, p_material_dtl_rec.plan_qty);
593 l_trans_date := NVL(p_trans_date, gme_common_pvt.g_timestamp);
594
595 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
596 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_consume_qty='||l_consume_qty);
597 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_trans_date='||to_char(p_trans_date
598 ,'YYYY-MON-DD HH24:MI:SS'));
599 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
600 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' number of reservations='||l_reservation_tab.COUNT);
601 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' auto consume from supply sub='||gme_common_pvt.g_auto_consume_supply_sub_only);
602 END IF;
603
604 i := 1;
605 j := 1;
606
607 WHILE l_consume_qty > x_actual_qty AND i <= l_reservation_tab.COUNT LOOP
608 -- Consume all fully specified reservations and mark the Partial ones
609 l_reservation_rec := l_reservation_tab(i);
610 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
611 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' in reservation loop: reservation_id='||l_reservation_rec.reservation_id);
612 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' in reservation loop: reservation subinventory='||l_reservation_rec.subinventory_code);
613 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);
614 END IF;
615 /* Bug 5441643 Added NVL condition for location control code*/
616 l_rsrv_type := gme_reservations_pvt.reservation_fully_specified
617 (p_reservation_rec => l_reservation_rec
618 ,p_item_location_control => NVL(p_item_rec.location_control_code,1)
619 ,p_item_restrict_locators => p_item_rec.restrict_locators_code);
620 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
621 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);
622 END IF;
623 IF l_rsrv_type = -1 THEN
624 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
625 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_rsrv_type passed back as -1 from gme_reservations_pvt.reservation_fully_specified');
626 RAISE error_unexpected;
627 ENd IF;
628 END IF;
629
630 IF l_rsrv_type IN (0, 2) THEN -- HLR or PLR
631 -- save these for later; if there's not enough DLR, PLR will be filled in and used
632 l_PLR_tab(j) := l_reservation_rec;
633 j := j + 1;
634 ELSE -- detailed level reservation
635 IF (gme_common_pvt.g_auto_consume_supply_sub_only = 0) OR
636 (gme_common_pvt.g_auto_consume_supply_sub_only = 1 AND
637 l_reservation_rec.subinventory_code = l_subinv) THEN
638 -- GMO Changes
639 IF ((NVL(p_material_dtl_rec.dispense_ind,'N') = 'Y' AND
640 l_reservation_rec.external_source_line_id IS NOT NULL ) OR
641 NVL(p_material_dtl_rec.dispense_ind,'N') = 'N' ) THEN
642 build_and_create_transaction
643 (p_rsrv_rec => l_reservation_rec
644 ,p_lot_divisible_flag => p_item_rec.lot_divisible_flag
645 ,p_dispense_ind => p_material_dtl_rec.dispense_ind
646 ,p_mtl_dtl_rec => p_material_dtl_rec
647 ,p_trans_date => l_trans_date
648 ,p_consume_qty => l_consume_qty
649 ,p_revision => NULL
650 ,p_secondary_uom_code => p_item_rec.secondary_uom_code
651 ,x_actual_qty => x_actual_qty
652 ,x_return_status => l_return_status);
653
654 IF l_return_status NOT IN (gme_common_pvt.g_not_transactable, FND_API.G_RET_STS_SUCCESS) THEN
655 x_return_status := l_return_status;
656 RAISE error_build_trxn;
657 END IF;
658 END IF; -- p_material_dtl_rec.dispense_ind = 'Y'
659 END IF; -- IF (gme_common_pvt.g_auto_consume_supply_sub_only = 0) OR...
660 END IF; -- IF l_rsrv_type = ...
661 i := i + 1; -- move on to the next reservation
662 END LOOP;
663
664 IF x_actual_qty >= l_consume_qty THEN
665 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
666 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);
667 END IF;
668 -- done!
669 RAISE consume_done;
670 END IF;
671
672 IF (gme_common_pvt.g_auto_consume_supply_sub_only = 0 AND l_subinv IS NULL) THEN
673 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
674 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');
675 END IF;
676 RAISE error_get_exception;
677 END IF;
678
679 -- Changes for GMO
680 IF NVL(p_material_dtl_rec.dispense_ind, 'N') = 'Y' THEN
681 -- if you get to this point, raise exception; can't process PLR/HLR for dispensed records; nor
682 -- can you get available inventory; record must be dispensed to process it
683 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
684 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);
685 END IF;
686 RAISE error_get_exception;
687 END IF;
688
689 l_pending_mo_ind := gme_move_orders_pvt.pending_move_orders_exist
690 (p_organization_id => p_material_dtl_rec.organization_id
691 ,p_batch_id => p_material_dtl_rec.batch_id
692 ,p_material_detail_id => p_material_dtl_rec.material_detail_id);
693
694 IF p_item_rec.lot_control_code = 2 THEN -- lot control
695 IF gme_common_pvt.g_auto_consume_supply_sub_only = 1 THEN -- auto consume -> Yes
696 IF l_pending_mo_ind THEN
697 l_try_PLR := FALSE;
698 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
699 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');
700 END IF;
701 ELSE
702 l_try_PLR := TRUE;
703 END IF;
704 ELSE
705 l_try_PLR := TRUE;
706 END IF;
707
708 IF NOT l_try_PLR THEN
709 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
710 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);
711 END IF;
712 RAISE error_get_exception;
713 END IF;
714 END IF;
715
716 -- at this point, it's a lot control item with demand not met and no pending move orders OR
717 -- a plain, revision or locator ctrl item
718 -- try to convert and consume Partial reservations
719 WHILE l_consume_qty > x_actual_qty AND i <= l_PLR_tab.COUNT LOOP
720 -- try to convert PLR to DLR
721 l_partial_rec := l_PLR_tab(i);
722 gme_reservations_pvt.convert_partial_to_dlr
723 (p_reservation_rec => l_partial_rec
724 ,p_material_dtl_rec => p_material_dtl_rec
725 ,p_item_rec => p_item_rec
726 ,x_reservation_rec => l_reservation_rec
727 ,x_return_status => l_return_status);
728 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
729 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
730 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');
731 END IF;
732 ELSE
733 build_and_create_transaction
734 (p_rsrv_rec => l_reservation_rec
735 ,p_lot_divisible_flag => p_item_rec.lot_divisible_flag
736 ,p_mtl_dtl_rec => p_material_dtl_rec
737 ,p_trans_date => l_trans_date
738 ,p_consume_qty => l_consume_qty
739 ,p_revision => NULL
740 ,p_secondary_uom_code => p_item_rec.secondary_uom_code
741 ,x_actual_qty => x_actual_qty
742 ,x_return_status => l_return_status);
743 IF l_return_status NOT IN (gme_common_pvt.g_not_transactable, FND_API.G_RET_STS_SUCCESS) THEN
744 x_return_status := l_return_status;
745 RAISE error_build_trxn;
746 END IF;
747 END IF; -- IF l_return_status <> FND_API.G_RET_STS_SUCCESS for gme_reservations_pvt.convert_partial_to_dlr
748
749 i := i + 1; -- move on to the next partial reservation
750 END LOOP;
751
752 -- If it's lot control and the qty is still not satisfied, get exceptions;
753 IF p_item_rec.lot_control_code = 2 THEN -- lot control
754 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
755 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' lot control; DLR and PLR have been exhausted; get exception');
756 END IF;
757 RAISE error_get_exception;
758 END IF;
759
760 -- If it's plain, revision or locator, try to get from supply sub and supply locator
761 -- get qty tree rec in subinv/loc
762
763 IF p_item_rec.revision_qty_control_code = 2 THEN -- under revision control
764 IF p_material_dtl_rec.revision IS NOT NULL THEN
765 l_revision := p_material_dtl_rec.revision;
766 ELSE
767 OPEN cur_get_item_revision(p_material_dtl_rec.inventory_item_id,
768 p_material_dtl_rec.organization_id);
769 FETCH cur_get_item_revision INTO l_revision;
770 CLOSE cur_get_item_revision;
771 END IF;
772 END IF; -- IF p_revision_qty_control_code = 2
773
774 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
775 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_revision='||l_revision);
776 END IF;
777
778 IF l_locator_id IS NULL THEN
779 -- check if it's locator control, we need a locator...
780 /* Bug 5441643 Added NVL condition for location control code*/
781 l_eff_locator_control :=
782 gme_common_pvt.eff_locator_control
783 (p_organization_id => p_material_dtl_rec.organization_id
784 ,p_org_control => gme_common_pvt.g_org_locator_control
785 ,p_subinventory => p_material_dtl_rec.subinventory
786 ,p_item_control => NVL(p_item_rec.location_control_code,1)
787 ,p_item_loc_restrict => p_item_rec.restrict_locators_code
788 ,p_action => gme_common_pvt.g_ing_issue_txn_action);
789 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
790 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_eff_locator_control='||l_eff_locator_control);
791 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' organization_id='||p_material_dtl_rec.organization_id);
792 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' g_org_locator_control='||gme_common_pvt.g_org_locator_control);
793 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' subinventory='||p_material_dtl_rec.subinventory);
794 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' inventory_item_id='||p_item_rec.inventory_item_id);
795 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' location_control_code='||p_item_rec.location_control_code);
796 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' restrict_locators_code='||p_item_rec.restrict_locators_code);
797 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_action='||gme_common_pvt.g_ing_issue_txn_action);
798 END IF;
799 IF l_eff_locator_control <> 1 THEN
800 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
801 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');
802 END IF;
803 RAISE error_get_exception;
804 END IF;
805 END IF;
806
807 gme_transactions_pvt.query_quantities
808 (
809 p_api_version_number => 1
810 ,p_init_msg_lst => fnd_api.g_false
811 ,x_return_status => l_return_status
812 ,x_msg_count => l_msg_count
813 ,x_msg_data => l_msg_data
814 ,p_organization_id => p_material_dtl_rec.organization_id
815 ,p_inventory_item_id => p_material_dtl_rec.inventory_item_id
816 ,p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
817 ,p_is_serial_control => FALSE
818 ,p_grade_code => NULL
819 ,p_demand_source_type_id => gme_common_pvt.g_txn_source_type
820 ,p_demand_source_header_id => p_material_dtl_rec.batch_id
821 ,p_demand_source_line_id => p_material_dtl_rec.material_detail_id
822 ,p_demand_source_name => NULL
823 ,p_lot_expiration_date => NULL
824 ,p_revision => l_revision
825 ,p_lot_number => NULL
826 ,p_subinventory_code => l_subinv
827 ,p_locator_id => l_locator_id
828 ,p_onhand_source => inv_quantity_tree_pvt.g_all_subs
829 ,x_qoh => l_qoh
830 ,x_rqoh => l_rqoh
831 ,x_qr => l_qr
832 ,x_qs => l_qs
833 ,x_att => l_att
834 ,x_atr => l_atr
835 ,x_sqoh => l_sqoh
836 ,x_srqoh => l_srqoh
837 ,x_sqr => l_sqr
838 ,x_sqs => l_sqs
839 ,x_satt => l_satt
840 ,x_satr => l_satr
841 ,p_transfer_subinventory_code => NULL
842 ,p_cost_group_id => NULL
843 ,p_lpn_id => NULL
844 ,p_transfer_locator_id => NULL
845 );
846
847 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
848 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities return status='||l_return_status);
849 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities att='||l_att);
850 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities qoh='||l_qoh);
851 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities item_id='||p_material_dtl_rec.inventory_item_id);
852 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities sub='||l_subinv);
853 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities loc='||l_locator_id);
854 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after query quantities rev='||l_revision);
855 END IF;
856
857 IF l_att > 0 AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
858 build_and_create_transaction
859 (p_rsrv_rec => NULL
860 ,p_subinv => l_subinv
861 ,p_locator_id => l_locator_id
862 ,p_att => l_att
863 ,p_satt => l_satt
864 ,p_primary_uom_code => p_item_rec.primary_uom_code
865 ,p_mtl_dtl_rec => p_material_dtl_rec
866 ,p_trans_date => l_trans_date
867 ,p_consume_qty => l_consume_qty
868 ,p_revision => l_revision
869 ,p_secondary_uom_code => p_item_rec.secondary_uom_code
870 ,x_actual_qty => x_actual_qty
871 ,x_return_status => l_return_status);
872
873 IF l_return_status NOT IN (gme_common_pvt.g_not_transactable, FND_API.G_RET_STS_SUCCESS) THEN
874 x_return_status := l_return_status;
875 RAISE error_build_trxn;
876 END IF;
877 END IF;
878
879 IF x_actual_qty < l_consume_qty THEN
880 RAISE error_get_exception;
881 END IF;
882
883 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
884 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
885 END IF;
886
887 EXCEPTION
888 WHEN error_build_trxn OR error_get_item OR error_convert_partial OR consume_done OR
889 error_get_reservations OR no_consume_required THEN
890 NULL;
891 WHEN error_get_exception THEN
892 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
893 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' exception block for get exceptions:');
894 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
895 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_start_actual_qty='||l_start_actual_qty);
896 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_consume_qty='||l_consume_qty);
897 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_exception_qty='||(l_consume_qty - x_actual_qty));
898 END IF;
899
900 create_batch_exception
901 (p_material_dtl_rec => p_material_dtl_rec
902 ,p_pending_move_order_ind => l_pending_mo_ind
903 ,p_pending_rsrv_ind => l_pending_rsrv_ind
904 ,p_transacted_qty => x_actual_qty - l_start_actual_qty
905 ,p_exception_qty => l_consume_qty - x_actual_qty
906 ,p_force_unconsumed => fnd_api.g_true
907 ,x_exception_material_tbl => x_exception_material_tbl
908 ,x_return_status => x_return_status);
909 WHEN error_unexpected THEN
910 x_return_status := FND_API.g_ret_sts_unexp_error;
911 WHEN OTHERS THEN
912 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
913 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
914 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
915 END IF;
916 x_return_status := FND_API.g_ret_sts_unexp_error;
917 END consume_material;
918
919 PROCEDURE build_and_create_transaction
920 (p_rsrv_rec IN mtl_reservations%ROWTYPE
921 ,p_lot_divisible_flag IN VARCHAR2 DEFAULT NULL -- required for lot non divisible
922 ,p_dispense_ind IN VARCHAR2 DEFAULT NULL
923 ,p_subinv IN VARCHAR2 DEFAULT NULL
924 ,p_locator_id IN NUMBER DEFAULT NULL
925 ,p_att IN NUMBER DEFAULT NULL
926 ,p_satt IN NUMBER DEFAULT NULL
927 ,p_primary_uom_code IN VARCHAR2 DEFAULT NULL
928 ,p_mtl_dtl_rec IN gme_material_details%ROWTYPE
929 ,p_trans_date IN DATE
930 ,p_consume_qty IN NUMBER
931 ,p_revision IN VARCHAR2 DEFAULT NULL
932 ,p_secondary_uom_code IN VARCHAR2 DEFAULT NULL
933 ,x_actual_qty IN OUT NOCOPY NUMBER
934 ,x_return_status OUT NOCOPY VARCHAR2) IS
935
936 CURSOR item_no_cursor(v_inventory_item_id NUMBER,
937 v_org_id NUMBER) IS
938 SELECT concatenated_segments
939 FROM mtl_system_items_kfv
940 WHERE inventory_item_id = v_inventory_item_id
941 AND organization_id = v_org_id;
942
943 l_item_no mtl_system_items_kfv.concatenated_segments%TYPE;
944
945 l_api_name CONSTANT VARCHAR2 (30) := 'build_and_create_transaction';
946
947 l_transaction_rec mtl_transactions_interface%ROWTYPE;
948 l_lot_rec gme_common_pvt.mtl_trans_lots_inter_tbl;
949 l_rsrv_mode BOOLEAN;
950 l_trxn_qty NUMBER;
951 l_dtl_qty NUMBER;
952 l_prim_qty NUMBER;
953 l_sec_qty NUMBER;
954 l_whole_qty BOOLEAN;
955 l_from_um VARCHAR2(3);
956 l_to_um VARCHAR2(3);
957 l_return_status VARCHAR2(1);
958 l_lot_divisible_flag VARCHAR2(1);
959 --Bug 4899399
960 l_msg_count NUMBER;
961 l_msg_data VARCHAR2(32767);
962 error_build_mmti EXCEPTION;
963 error_get_dtl_qty EXCEPTION;
964 error_create_trxn EXCEPTION;
965 error_relieve_rsrv EXCEPTION;
966 um_convert_error EXCEPTION;
967 dispense_error EXCEPTION;
968 BEGIN
969 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
970 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
971 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_rsrv_rec.reservation_id='||p_rsrv_rec.reservation_id);
972 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_rsrv_rec.lot_number='||p_rsrv_rec.lot_number);
973 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_lot_divisible_flag='||p_lot_divisible_flag);
974 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_subinv='||p_subinv);
975 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_locator_id='||p_locator_id);
976 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_att='||p_att);
977 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_satt='||p_satt);
978 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_primary_uom_code='||p_primary_uom_code);
979 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_mtl_dtl_rec.material_detail_id='||p_mtl_dtl_rec.material_detail_id);
980 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_mtl_dtl_rec.dtl_um='||p_mtl_dtl_rec.dtl_um);
981 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_trans_date='||to_char(p_trans_date
982 ,'YYYY-MON-DD HH24:MI:SS'));
983 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_consume_qty='||p_consume_qty);
984 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_revision='||p_revision);
985 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' p_secondary_uom_code='||p_secondary_uom_code);
986 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
987 END IF;
988
989 /* Set the return status to success initially */
990 x_return_status := FND_API.G_RET_STS_SUCCESS;
991
992 IF p_rsrv_rec.reservation_id IS NOT NULL THEN
993 l_rsrv_mode := TRUE;
994 ELSE
995 l_rsrv_mode := FALSE;
996 END IF;
997
998 IF l_rsrv_mode THEN
999 constr_mmti_from_reservation
1000 (p_rsrv_rec => p_rsrv_rec
1001 ,x_mmti_rec => l_transaction_rec
1002 ,x_mmli_tbl => l_lot_rec
1003 ,x_return_status => x_return_status);
1004 ELSE
1005 constr_mmti_from_qty_tree
1006 (p_mtl_dtl_rec => p_mtl_dtl_rec
1007 ,p_subinv => p_subinv
1008 ,p_locator_id => p_locator_id
1009 ,x_mmti_rec => l_transaction_rec
1010 ,x_return_status => x_return_status);
1011 l_transaction_rec.revision := p_revision;
1012 END IF;
1013
1014 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1015 RAISE error_build_mmti;
1016 END IF;
1017
1018 IF l_rsrv_mode THEN
1019 l_prim_qty := p_rsrv_rec.primary_reservation_quantity;
1020 l_sec_qty := p_rsrv_rec.secondary_reservation_quantity;
1021
1022 gme_reservations_pvt.get_reservation_dtl_qty
1023 (p_reservation_rec => p_rsrv_rec
1024 ,p_uom_code => p_mtl_dtl_rec.dtl_um
1025 ,x_qty => l_dtl_qty
1026 ,x_return_status => x_return_status);
1027
1028 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1029 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1030 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' gme_reservations_pvt.get_reservation_dtl_qty returned error');
1031 END IF;
1032
1033 RAISE error_get_dtl_qty;
1034 END IF;
1035
1036 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1037 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' get_reservation_dtl_qty returned l_dtl_qty='||l_dtl_qty);
1038 END IF;
1039 ELSE
1040 l_prim_qty := p_att;
1041 l_sec_qty := p_satt;
1042
1043 l_from_um := p_primary_uom_code;
1044 l_to_um := p_mtl_dtl_rec.dtl_um;
1045
1046 l_dtl_qty := inv_convert.inv_um_convert
1047 (item_id => p_mtl_dtl_rec.inventory_item_id
1048 ,precision => gme_common_pvt.g_precision
1049 ,from_quantity => p_att
1050 ,from_unit => l_from_um
1051 ,to_unit => l_to_um
1052 ,from_name => NULL
1053 ,to_name => NULL);
1054 IF l_dtl_qty = -99999 THEN
1055 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1056 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' inv_convert.inv_um_convert returned error');
1057 END IF;
1058 RAISE um_convert_error;
1059 END IF;
1060 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1061 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after call to inv_convert.inv_um_convert');
1062 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_dtl_qty= '||to_char(l_dtl_qty));
1063 END IF;
1064 END IF;
1065
1066 IF p_rsrv_rec.lot_number IS NOT NULL AND
1067 NVL(p_lot_divisible_flag,'Y') = 'N' THEN
1068 l_whole_qty := TRUE;
1069 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1070 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_whole_qty = TRUE because lot indivisible item');
1071 END IF;
1072 ELSE
1073 l_whole_qty := FALSE;
1074 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1075 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_whole_qty = FALSE');
1076 END IF;
1077 END IF;
1078
1079 -- test again for dispensed items
1080 IF NOT l_whole_qty THEN
1081 IF NVL(p_dispense_ind,'N') = 'Y' THEN
1082 l_whole_qty := TRUE;
1083 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1084 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_whole_qty = TRUE because dispensed item');
1085 END IF;
1086 END IF;
1087 END IF;
1088
1089 IF l_dtl_qty <= p_consume_qty - x_actual_qty OR l_whole_qty THEN
1090 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1091 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_trxn_qty := l_dtl_qty');
1092 END IF;
1093 l_trxn_qty := l_dtl_qty;
1094 ELSE
1095 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1096 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_trxn_qty := p_consume_qty - x_actual_qty');
1097 END IF;
1098 l_trxn_qty := p_consume_qty - x_actual_qty;
1099 l_prim_qty := NULL;
1100 l_sec_qty := NULL;
1101 END IF;
1102
1103 l_transaction_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
1104 l_transaction_rec.transaction_date := p_trans_date;
1105 l_transaction_rec.transaction_quantity := l_trxn_qty;
1106 l_transaction_rec.secondary_uom_code := p_secondary_uom_code;
1107
1108 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1109 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' transaction_date='||to_char(l_transaction_rec.transaction_date
1110 ,'YYYY-MON-DD HH24:MI:SS'));
1111 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' transaction_qty='||l_transaction_rec.transaction_quantity);
1112 END IF;
1113
1114 IF l_prim_qty IS NOT NULL THEN
1115 l_transaction_rec.primary_quantity := l_prim_qty;
1116 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1117 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' primary_qty is known:'||l_transaction_rec.primary_quantity);
1118 END IF;
1119 END IF;
1120 IF l_sec_qty IS NOT NULL THEN
1121 l_transaction_rec.secondary_transaction_quantity := l_sec_qty;
1122 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1123 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' secondary_transaction_quantity is known:'||l_transaction_rec.secondary_transaction_quantity);
1124 END IF;
1125 END IF;
1126
1127 l_transaction_rec.transaction_uom := p_mtl_dtl_rec.dtl_um;
1128
1129 -- if the item is dual, this should be passed in, if not dual, this should be NULL
1130 l_transaction_rec.secondary_uom_code := p_secondary_uom_code;
1131
1132 IF l_lot_rec.count > 0 THEN
1133 IF l_lot_rec(1).lot_number IS NOT NULL THEN
1134 l_lot_rec(1).transaction_quantity := l_transaction_rec.transaction_quantity;
1135 IF l_prim_qty IS NOT NULL THEN
1136 l_lot_rec(1).primary_quantity := l_prim_qty;
1137 END IF;
1138 IF l_sec_qty IS NOT NULL THEN
1139 l_lot_rec(1).secondary_transaction_quantity := l_sec_qty;
1140 END IF;
1141 END IF;
1142 END IF;
1143
1144 gme_transactions_pvt.create_material_txn
1145 (p_mmti_rec => l_transaction_rec
1146 ,p_mmli_tbl => l_lot_rec
1147 ,x_return_status => x_return_status);
1148
1149 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1150 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1151 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' gme_transactions_pvt.create_transaction returned '||x_return_status);
1152 END IF;
1153 RAISE error_create_trxn;
1154 END IF;
1155
1156 x_actual_qty := x_actual_qty + l_trxn_qty;
1157
1158 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1159 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
1160 END IF;
1161
1162 IF l_rsrv_mode THEN
1163 IF l_prim_qty IS NULL THEN
1164 -- need to consider lot conversion as well
1165 l_from_um := p_mtl_dtl_rec.dtl_um;
1166 l_to_um := p_rsrv_rec.primary_uom_code;
1167 l_prim_qty := inv_convert.inv_um_convert
1168 (item_id => p_mtl_dtl_rec.inventory_item_id
1169 ,precision => gme_common_pvt.g_precision
1170 ,from_quantity => l_trxn_qty
1171 ,from_unit => l_from_um
1172 ,to_unit => l_to_um
1173 ,from_name => NULL
1174 ,to_name => NULL);
1175 IF l_prim_qty = -99999 THEN
1176 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1177 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' inv_convert.inv_um_convert returned error');
1178 END IF;
1179 RAISE um_convert_error;
1180 END IF;
1181 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1182 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));
1183 END IF;
1184 END IF;
1185 gme_reservations_pvt.relieve_reservation
1186 (p_reservation_id => p_rsrv_rec.reservation_id
1187 ,p_prim_relieve_qty => l_prim_qty
1188 ,x_return_status => x_return_status);
1189
1190 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1191 RAISE error_relieve_rsrv;
1192 END IF;
1193
1194 -- Bug 4899399 - after relieving the reservation, informing the GMO about the transaction.
1195 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1196 gme_debug.put_line (g_pkg_name||'.'||l_api_name||'dispense_ind'||p_mtl_dtl_rec.dispense_ind);
1197 gme_debug.put_line (g_pkg_name||'.'||l_api_name||'trans type_id'||l_transaction_rec.transaction_type_id);
1198 gme_debug.put_line (g_pkg_name||'.'||l_api_name||'ext sour line_id'||p_rsrv_rec.external_source_line_id);
1199
1200 END IF;
1201 IF NVL(p_dispense_ind,'N') = 'Y' THEN
1202 IF l_transaction_rec.transaction_type_id = gme_common_pvt.g_ing_issue THEN
1203 -- For consume
1204 GMO_DISPENSE_GRP.CHANGE_DISPENSE_STATUS
1205 (p_api_version => 1.0,
1206 p_init_msg_list => 'F',
1207 p_commit => 'F',
1208 x_return_status => l_return_status,
1209 x_msg_count => l_msg_count,
1210 x_msg_data => l_msg_data,
1211 p_dispense_id => p_rsrv_rec.external_source_line_id,
1212 p_status_code => 'CNSUMED',
1213 p_transaction_id => null
1214 ) ;
1215 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1216 RAISE dispense_error;
1217 END IF;
1218 END IF;
1219 END IF;
1220 END IF;
1221
1222 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1223 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1224 END IF;
1225
1226
1227 EXCEPTION
1228 WHEN um_convert_error THEN
1229 OPEN item_no_cursor(p_mtl_dtl_rec.inventory_item_id, p_mtl_dtl_rec.organization_id);
1230 FETCH item_no_cursor INTO l_item_no;
1231 CLOSE item_no_cursor;
1232
1233 fnd_message.set_name ('GMI', 'IC_API_UOM_CONVERSION_ERROR');
1234 fnd_message.set_token ('ITEM_NO', l_item_no);
1235 fnd_message.set_token ('FROM_UOM',l_from_um);
1236 fnd_message.set_token ('TO_UOM', l_to_um);
1237 fnd_msg_pub.ADD;
1238 x_return_status := FND_API.g_ret_sts_error;
1239 WHEN error_create_trxn OR error_build_mmti OR error_get_dtl_qty OR error_relieve_rsrv THEN
1240 NULL;
1241 WHEN dispense_error THEN
1242 x_return_status := fnd_api.g_ret_sts_error;
1243 WHEN OTHERS THEN
1244 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1245 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
1246 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
1247 END IF;
1248 x_return_status := FND_API.g_ret_sts_unexp_error;
1249 END build_and_create_transaction;
1250
1251
1252
1253 PROCEDURE constr_mmti_from_reservation
1254 (p_rsrv_rec IN mtl_reservations%ROWTYPE
1255 ,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
1256 ,x_mmli_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_inter_tbl
1257 ,x_return_status OUT NOCOPY VARCHAR2) IS
1258
1259 l_api_name CONSTANT VARCHAR2 (30) := 'CONSTR_MMTI_FROM_RESERVATION';
1260 BEGIN
1261
1262 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1263 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1264 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' reservation_id='||p_rsrv_rec.reservation_id);
1265 END IF;
1266
1267 /* Initially let us assign the return status to success */
1268 x_return_status := FND_API.g_ret_sts_success;
1269
1270 -- consturct mtl_transactions_interface
1271 x_mmti_rec.transaction_source_id := p_rsrv_rec.demand_source_header_id; -- batch_id
1272 x_mmti_rec.trx_source_line_id := p_rsrv_rec.demand_source_line_id; -- material_detail_id
1273 x_mmti_rec.inventory_item_id := p_rsrv_rec.inventory_item_id;
1274 x_mmti_rec.organization_id := p_rsrv_rec.organization_id;
1275 x_mmti_rec.subinventory_code := p_rsrv_rec.subinventory_code;
1276 x_mmti_rec.locator_id := p_rsrv_rec.locator_id;
1277 x_mmti_rec.revision := p_rsrv_rec.revision;
1278
1279 x_mmti_rec.transaction_sequence_id := p_rsrv_rec.reservation_id;
1280
1281 -- channges for GMO
1282 x_mmti_rec.transaction_reference := p_rsrv_rec.external_source_line_id ;
1283 -- construct mtl_transaction_lots_interface
1284 IF p_rsrv_rec.lot_number IS NOT NULL THEN
1285 x_mmli_tbl(1).lot_number := p_rsrv_rec.lot_number;
1286 END IF;
1287
1288 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1289 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1290 END IF;
1291
1292
1293 EXCEPTION
1294 WHEN FND_API.G_EXC_ERROR THEN
1295 x_return_status := FND_API.G_RET_STS_ERROR;
1296
1297 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1299
1300 WHEN OTHERS THEN
1301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1302 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME , l_api_name);
1303
1304 END constr_mmti_from_reservation;
1305
1306 PROCEDURE constr_mmti_from_qty_tree
1307 (p_mtl_dtl_rec IN gme_material_details%ROWTYPE
1308 ,p_subinv IN VARCHAR2
1309 ,p_locator_id IN NUMBER
1310 ,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
1311 ,x_return_status OUT NOCOPY VARCHAR2) IS
1312
1313 l_api_name CONSTANT VARCHAR2 (30) := 'CONSTR_MMTI_FROM_QTY_TREE';
1314 BEGIN
1315
1316 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1317 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1318 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' material_detail_id='||p_mtl_dtl_rec.material_detail_id);
1319 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_subinv='||p_subinv);
1320 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_locator_id='||p_locator_id);
1321 END IF;
1322
1323 /* Initially let us assign the return status to success */
1324 x_return_status := FND_API.g_ret_sts_success;
1325
1326 -- consturct mtl_transactions_interface
1327 x_mmti_rec.transaction_source_id := p_mtl_dtl_rec.batch_id;
1328 x_mmti_rec.trx_source_line_id := p_mtl_dtl_rec.material_detail_id;
1329 x_mmti_rec.inventory_item_id := p_mtl_dtl_rec.inventory_item_id;
1330 x_mmti_rec.organization_id := p_mtl_dtl_rec.organization_id;
1331 x_mmti_rec.subinventory_code := p_subinv;
1332 x_mmti_rec.locator_id := p_locator_id;
1333
1334 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1335 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1336 END IF;
1337
1338 EXCEPTION
1339 WHEN FND_API.G_EXC_ERROR THEN
1340 x_return_status := FND_API.G_RET_STS_ERROR;
1341
1342 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1344
1345 WHEN OTHERS THEN
1346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1347 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME , l_api_name);
1348
1349 END constr_mmti_from_qty_tree;
1350
1351 PROCEDURE create_batch_exception
1352 (p_material_dtl_rec IN gme_material_details%ROWTYPE
1353 ,p_pending_move_order_ind IN BOOLEAN := NULL
1354 ,p_pending_rsrv_ind IN BOOLEAN := NULL
1355 ,p_transacted_qty IN NUMBER := NULL
1356 ,p_exception_qty IN NUMBER := NULL
1357 ,p_force_unconsumed IN VARCHAR2 := fnd_api.g_true
1358 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
1359 ,x_return_status OUT NOCOPY VARCHAR2) IS
1360
1361 l_api_name CONSTANT VARCHAR2 (30) := 'create_batch_exception';
1362
1363 i NUMBER;
1364
1365 l_pending_mo_ind BOOLEAN;
1366 l_pending_rsrv_ind BOOLEAN;
1367 l_display_unconsumed VARCHAR2(1);
1368 l_exceptions_rec gme_exceptions_gtmp%ROWTYPE;
1369
1370 error_insert_exceptions EXCEPTION;
1371 error_no_exception EXCEPTION;
1372 BEGIN
1373 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1374 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1375 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' material_detail_id='||p_material_dtl_rec.material_detail_id);
1376 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' line_no='||p_material_dtl_rec.line_no);
1377 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' line_type='||p_material_dtl_rec.line_type);
1378 IF p_material_dtl_rec.phantom_line_id IS NOT NULL THEN
1379 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' this is a PHANTOM');
1380 END IF;
1381
1382 IF p_pending_move_order_ind IS NULL THEN
1383 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_pending_move_order_ind IS NULL');
1384 ELSIF p_pending_move_order_ind THEN
1385 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_pending_move_order_ind = TRUE');
1386 ELSE
1387 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_pending_move_order_ind = FALSE');
1388 END IF;
1389
1390 IF p_pending_rsrv_ind IS NULL THEN
1391 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_pending_rsrv_ind IS NULL');
1392 ELSIF p_pending_rsrv_ind THEN
1393 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_pending_rsrv_ind = TRUE');
1394 ELSE
1395 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_pending_rsrv_ind = FALSE');
1396 END IF;
1397
1398 IF p_transacted_qty IS NULL THEN
1399 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_transacted_qty IS NULL');
1400 ELSE
1401 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_transacted_qty = '||p_transacted_qty);
1402 END IF;
1403
1404 IF p_exception_qty IS NULL THEN
1405 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_exception_qty IS NULL');
1406 ELSE
1407 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_exception_qty = '||p_exception_qty);
1408 END IF;
1409
1410 END IF;
1411
1412 x_return_status := FND_API.G_RET_STS_SUCCESS;
1413
1414 IF p_force_unconsumed = fnd_api.g_true THEN
1415 l_display_unconsumed := fnd_api.g_true;
1416 ELSIF p_force_unconsumed = fnd_api.g_false THEN
1417 IF gme_common_pvt.g_display_unconsumed_material = 1 THEN
1418 l_display_unconsumed := fnd_api.g_true;
1419 ELSE
1420 l_display_unconsumed := fnd_api.g_false;
1421 END IF;
1422 END IF;
1423
1424 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
1425 -- don't report the phantom ingredients; just return; phantom products will be reported
1426 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1427 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' called for phantom ingredient... returning, only report for phantom product');
1428 END IF;
1429 RAISE error_no_exception;
1430 END IF;
1431
1432 IF p_pending_move_order_ind IS NULL AND p_material_dtl_rec.line_type = gme_common_pvt.g_line_type_ing THEN
1433 l_pending_mo_ind := gme_move_orders_pvt.pending_move_orders_exist
1434 (p_organization_id => p_material_dtl_rec.organization_id
1435 ,p_batch_id => p_material_dtl_rec.batch_id
1436 ,p_material_detail_id => p_material_dtl_rec.material_detail_id);
1437 ELSE
1438 l_pending_mo_ind := NVL(p_pending_move_order_ind, FALSE);
1439 END IF;
1440
1441 IF p_pending_rsrv_ind IS NULL THEN
1442 IF p_material_dtl_rec.line_type = gme_common_pvt.g_line_type_ing THEN
1443 l_pending_rsrv_ind:= gme_reservations_pvt.pending_reservations_exist
1444 (p_organization_id => p_material_dtl_rec.organization_id
1445 ,p_batch_id => p_material_dtl_rec.batch_id
1446 ,p_material_detail_id => p_material_dtl_rec.material_detail_id);
1447 ELSE
1448 l_pending_rsrv_ind:= gme_pending_product_lots_pvt.pending_product_lot_exist
1449 (p_batch_id => p_material_dtl_rec.batch_id
1450 ,p_material_detail_id => p_material_dtl_rec.material_detail_id);
1451 END IF;
1452 ELSE
1453 l_pending_rsrv_ind := p_pending_rsrv_ind;
1454 END IF;
1455
1456 l_exceptions_rec.organization_id := p_material_dtl_rec.organization_id;
1457
1458 IF l_pending_mo_ind THEN
1459 l_exceptions_rec.pending_move_order_ind := 1;
1460 ELSE
1461 l_exceptions_rec.pending_move_order_ind := 0;
1462 END IF;
1463
1464 IF l_pending_rsrv_ind THEN
1465 l_exceptions_rec.pending_reservations_ind := 1;
1466 ELSE
1467 l_exceptions_rec.pending_reservations_ind := 0;
1468 END IF;
1469
1470 l_exceptions_rec.material_detail_id := p_material_dtl_rec.material_detail_id;
1471 l_exceptions_rec.batch_id := p_material_dtl_rec.batch_id;
1472 l_exceptions_rec.transacted_qty := NVL(p_transacted_qty, p_material_dtl_rec.actual_qty);
1473 l_exceptions_rec.exception_qty := NVL(p_exception_qty, p_material_dtl_rec.plan_qty - p_material_dtl_rec.actual_qty);
1474 l_exceptions_rec.exception_qty := ROUND(l_exceptions_rec.exception_qty, gme_common_pvt.g_precision);
1475
1476 IF l_pending_rsrv_ind OR l_pending_mo_ind OR
1477 (l_display_unconsumed = FND_API.g_true AND l_exceptions_rec.exception_qty > 0) OR
1478 -- next line is for negative IB
1479 (l_display_unconsumed = FND_API.g_true AND p_exception_qty < 0) THEN
1480 i := x_exception_material_tbl.COUNT + 1;
1481 x_exception_material_tbl(i) := l_exceptions_rec;
1482
1483 IF NOT gme_common_pvt.insert_exceptions(p_exception_rec => l_exceptions_rec) THEN
1484 RAISE error_insert_exceptions;
1485 END IF;
1486
1487 x_return_status := gme_common_pvt.g_exceptions_err;
1488 ELSE
1489 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1490 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' exception not found');
1491 END IF;
1492 END IF;
1493
1494 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1495 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name||' with return status= '||x_return_status);
1496 END IF;
1497 EXCEPTION
1498 WHEN error_insert_exceptions THEN
1499 x_return_status := FND_API.G_RET_STS_ERROR;
1500 WHEN error_no_exception THEN
1501 NULL;
1502 WHEN OTHERS THEN
1503 IF nvl(g_debug, gme_debug.g_log_unexpected + 1) <= gme_debug.g_log_unexpected THEN
1504 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
1505 END IF;
1506 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1508 END create_batch_exception;
1509
1510 /*************************************************************************************/
1511 /* p_auto_by_step Include auto by step */
1512 /* 0 = check for all but auto by step */
1513 /* 1 = check only auto by step */
1514 /* 2 = check all release types */
1515 /* p_batchstep_id used when p_auto_by_step is passed as value = 1 */
1516 /*************************************************************************************/
1517 PROCEDURE check_unexploded_phantom(p_batch_id IN NUMBER
1518 ,p_auto_by_step IN NUMBER
1519 ,p_batchstep_id IN NUMBER
1520 ,x_return_status OUT NOCOPY VARCHAR2) IS
1521
1522 CURSOR cur_get_phantom_ingred(v_batch_id NUMBER) IS
1523 SELECT *
1524 FROM gme_material_details
1525 WHERE batch_id = v_batch_id
1526 AND line_type = gme_common_pvt.g_line_type_ing
1527 AND phantom_type <> 0;
1528
1529 CURSOR Cur_associated_step(v_matl_dtl_id NUMBER) IS
1530 SELECT s.batchstep_id
1531 FROM gme_batch_steps s, gme_batch_step_items item
1532 WHERE s.batchstep_id = item.batchstep_id
1533 AND item.material_detail_id = v_matl_dtl_id;
1534
1535 l_api_name CONSTANT VARCHAR2 (30) := 'check_unexploded_phantom';
1536
1537 l_step_id NUMBER;
1538
1539 l_matl_dtl_tab gme_common_pvt.material_details_tab;
1540
1541 l_matl_dtl_id NUMBER;
1542 l_phantom_id NUMBER;
1543 l_release_type NUMBER;
1544
1545 error_unexp_phantom EXCEPTION;
1546 error_unexp_downstream EXCEPTION;
1547
1548 BEGIN
1549 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1550 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1551 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batch_id='||p_batch_id);
1552 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batchstep_id='||p_batchstep_id);
1553 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' mode='||p_auto_by_step);
1554 END IF;
1555
1556 x_return_status := FND_API.G_RET_STS_SUCCESS;
1557
1558 OPEN cur_get_phantom_ingred(p_batch_id);
1559 FETCH cur_get_phantom_ingred BULK COLLECT INTO l_matl_dtl_tab;
1560 CLOSE cur_get_phantom_ingred;
1561
1562 FOR i in 1..l_matl_dtl_tab.COUNT LOOP
1563 l_matl_dtl_id := l_matl_dtl_tab(i).material_detail_id;
1564 l_phantom_id := l_matl_dtl_tab(i).phantom_id;
1565 l_release_type := l_matl_dtl_tab(i).release_type;
1566
1567 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1568 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' phantom ingredient found: material_detail_id='||l_matl_dtl_id);
1569 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' partner phantom batch id: batch_id='||l_phantom_id);
1570 END IF;
1571
1572 IF p_auto_by_step IN (0,1) AND l_release_type = gme_common_pvt.g_mtl_autobystep_release THEN
1573 OPEN Cur_associated_step(l_matl_dtl_id);
1574 FETCH Cur_associated_step INTO l_step_id;
1575 IF Cur_associated_step%NOTFOUND THEN
1576 l_release_type := gme_common_pvt.g_mtl_auto_release;
1577 END IF;
1578 CLOSE Cur_associated_step;
1579 END IF;
1580
1581 IF ((p_auto_by_step = 0 AND l_release_type <> gme_common_pvt.g_mtl_autobystep_release) OR
1582 (p_auto_by_step = 1 AND
1583 l_step_id = p_batchstep_id AND
1584 l_release_type = gme_common_pvt.g_mtl_autobystep_release) OR
1585 (p_auto_by_step = 2)) THEN
1586 IF l_phantom_id IS NULL THEN
1587 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1588 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' phantom ingredient unexploded: material_detail_id='||l_matl_dtl_id);
1589 END IF;
1590 RAISE error_unexp_phantom;
1591 END IF;
1592
1593 -- check that the phantom batch doesn't have any unexploded phantoms...
1594 -- check for all release types in phantom batch
1595 check_unexploded_phantom(p_batch_id => l_phantom_id
1596 ,p_auto_by_step => 2
1597 ,p_batchstep_id => NULL
1598 ,x_return_status => x_return_status);
1599
1600 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1601 RAISE error_unexp_downstream;
1602 END IF;
1603 END IF;
1604 END LOOP;
1605
1606 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1607 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1608 END IF;
1609
1610 EXCEPTION
1611 WHEN error_unexp_phantom THEN
1612 gme_common_pvt.log_message ('PM_UNEXPLODED_PHANTOMS');
1613 x_return_status := FND_API.G_RET_STS_ERROR;
1614 WHEN error_unexp_downstream THEN
1615 NULL;
1616 WHEN OTHERS THEN
1617 IF nvl(g_debug, gme_debug.g_log_unexpected + 1) <= gme_debug.g_log_unexpected THEN
1618 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
1619 END IF;
1620 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1621 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1622 END check_unexploded_phantom;
1623
1624 PROCEDURE validate_batch_for_release (p_batch_header_rec IN gme_batch_header%ROWTYPE
1625 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
1626 ,x_return_status OUT NOCOPY VARCHAR2) IS
1627
1628 l_api_name CONSTANT VARCHAR2 (30) := 'validate_batch_for_release';
1629
1630 l_batch_header_rec gme_batch_header%ROWTYPE;
1631
1632 CURSOR cur_validity_rule(v_recipe_validity_rule_id NUMBER)
1633 IS
1634 SELECT *
1635 FROM gmd_recipe_validity_rules
1636 WHERE recipe_validity_rule_id = v_recipe_validity_rule_id;
1637
1638 CURSOR cur_validity_status_type(v_validity_rule_status VARCHAR2)
1639 IS
1640 SELECT status_type
1641 FROM gmd_status
1642 WHERE status_code=v_validity_rule_status;
1643
1644 l_validity_rule gmd_recipe_validity_rules%ROWTYPE;
1645 l_status_type GMD_STATUS.status_type%TYPE;
1646
1647 error_batch_type EXCEPTION;
1648 error_batch_status EXCEPTION;
1649 error_phantom EXCEPTION;
1650 error_future_date EXCEPTION;
1651 error_vr_not_found EXCEPTION;
1652 error_validity_status EXCEPTION;
1653 error_vr_dates EXCEPTION;
1654 error_validation EXCEPTION;
1655
1656 BEGIN
1657 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
1658 gme_debug.g_log_procedure THEN
1659 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1660 || l_api_name);
1661 END IF;
1662
1663 x_return_status := FND_API.g_ret_sts_success;
1664
1665 -- set output structure
1666 x_batch_header_rec := p_batch_header_rec;
1667
1668 IF p_batch_header_rec.batch_type = gme_common_pvt.g_doc_type_fpo THEN
1669 RAISE error_batch_type;
1670 END IF;
1671
1672 IF p_batch_header_rec.batch_status <> gme_common_pvt.g_batch_pending THEN
1673 RAISE error_batch_status;
1674 END IF;
1675
1676 -- set actual start date if it's not passed
1677 IF p_batch_header_rec.actual_start_date IS NULL THEN
1678 x_batch_header_rec.actual_start_date := SYSDATE;
1679 ELSIF p_batch_header_rec.actual_cmplt_date > SYSDATE THEN
1680 RAISE error_future_date;
1681 END IF;
1682
1683 IF p_batch_header_rec.parentline_id IS NOT NULL THEN
1684 RAISE error_phantom;
1685 END IF;
1686
1687 -- check validity rule if it's not NULL; it would be NULL in case of LCF
1688 IF p_batch_header_rec.recipe_validity_rule_id IS NOT NULL THEN
1689 OPEN cur_validity_rule(p_batch_header_rec.recipe_validity_rule_id);
1690 FETCH cur_validity_rule INTO l_validity_rule;
1691 CLOSE cur_validity_rule;
1692
1693 IF l_validity_rule.recipe_validity_rule_id IS NULL THEN -- not found
1694 RAISE error_vr_not_found;
1695 ELSE
1696 -- following prevents user from releasing a pending batch
1697 -- if validity rule is ON_HOLD or OBSOLETE.
1698 OPEN cur_validity_status_type(l_validity_rule.validity_rule_status);
1699 FETCH cur_validity_status_type INTO l_status_type;
1700 CLOSE cur_validity_status_type;
1701
1702 IF l_status_type IN ('1000' ,'800') THEN
1703 RAISE error_validity_status;
1704 END IF;
1705 END IF; -- IF l_validity_rule.recipe_validity_rule_id IS NULL
1706
1707 /* IF l_validity_rule.start_date > x_batch_header_rec.actual_start_date OR
1708 (l_validity_rule.end_date IS NOT NULL AND
1709 l_validity_rule.end_date < x_batch_header_rec.actual_start_date) THEN
1710 RAISE error_vr_dates;
1711 END IF;*/
1712 --sunitha ch. Bug 5336007 aded call to check_validity_rule_dates and passed p_validate_plan_dates_ind=1
1713 --to validate planned start date against validate rule dates
1714 IF NOT gme_common_pvt.check_validity_rule_dates (
1715 p_validity_rule_id => p_batch_header_rec.recipe_validity_rule_id
1716 ,p_start_date => p_batch_header_rec.actual_start_date
1717 ,p_cmplt_date => p_batch_header_rec.actual_cmplt_date
1718 ,p_batch_header_rec => p_batch_header_rec
1719 ,p_validate_plan_dates_ind => 1) THEN
1720 x_return_status := fnd_api.g_ret_sts_error;
1721 RAISE error_vr_dates;
1722 END IF;
1723 -- End Bug 5336007
1724 END IF; -- IF p_batch_header_rec.recipe_validity_rule_id IS NOT NULL
1725
1726 gme_validate_flex_fld_pvt.validate_flex_batch_header
1727 (p_batch_header => p_batch_header_rec
1728 ,x_batch_header => x_batch_header_rec
1729 ,x_return_status => x_return_status);
1730
1731 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1732 RAISE error_validation;
1733 END IF;
1734
1735 check_unexploded_phantom(p_batch_id => p_batch_header_rec.batch_id
1736 ,p_auto_by_step => 0 -- all but auto by step ingredients
1737 ,p_batchstep_id => NULL
1738 ,x_return_status => x_return_status);
1739
1740 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1741 RAISE error_validation;
1742 END IF;
1743
1744 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
1745 gme_debug.g_log_procedure THEN
1746 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1747 END IF;
1748
1749 EXCEPTION
1750 WHEN error_validation THEN
1751 NULL;
1752 WHEN error_vr_dates THEN
1753 x_return_status := FND_API.G_RET_STS_ERROR;
1754 WHEN error_validity_status THEN
1755 gme_common_pvt.log_message ('GME_VALIDITY_OBSO_OR_ONHOLD');
1756 x_return_status := FND_API.G_RET_STS_ERROR;
1757 WHEN error_vr_not_found THEN
1758 gme_common_pvt.log_message ('GME_API_INVALID_VALIDITY');
1759 x_return_status := FND_API.G_RET_STS_ERROR;
1760 WHEN error_phantom THEN
1761 gme_common_pvt.log_message ('PM_INVALID_PHANTOM_ACTION');
1762 x_return_status := FND_API.G_RET_STS_ERROR;
1763 WHEN error_batch_type OR error_batch_status THEN
1764 gme_common_pvt.log_message('GME_API_INVALID_BATCH_REL');
1765 x_return_status := fnd_api.g_ret_sts_error;
1766 WHEN error_future_date THEN
1767 fnd_message.set_name ('GMA', 'SY_NOFUTUREDATE');
1768 fnd_msg_pub.ADD;
1769 x_return_status := fnd_api.g_ret_sts_error;
1770 WHEN OTHERS THEN
1771 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1772
1773 IF g_debug <= gme_debug.g_log_procedure THEN
1774 gme_debug.put_line ( 'Unexpected error: '
1775 || g_pkg_name
1776 || '.'
1777 || l_api_name
1778 || ': '
1779 || SQLERRM);
1780 END IF;
1781
1782 x_return_status := fnd_api.g_ret_sts_unexp_error;
1783 END validate_batch_for_release;
1784
1785 END gme_release_batch_pvt;