1 PACKAGE BODY gme_complete_batch_pvt AS
2 /* $Header: GMEVCMBB.pls 120.31.12020000.2 2012/07/26 15:29:35 gmurator ship $ */
3
4 G_DEBUG VARCHAR2(5) := FND_PROFILE.VALUE('AFLOG_LEVEL');
5
6 g_pkg_name VARCHAR2(30) := 'GME_COMPLETE_BATCH_PVT';
7
8
9 PROCEDURE complete_batch
10 (p_batch_header_rec IN gme_batch_header%ROWTYPE
11 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
12 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
13 ,x_return_status OUT NOCOPY VARCHAR2) IS
14
15
16
17
18 CURSOR Cur_associated_step(v_matl_dtl_id NUMBER) IS
19 SELECT step_status
20 FROM gme_batch_steps s, gme_batch_step_items item
21 WHERE s.batchstep_id = item.batchstep_id
22 AND item.material_detail_id = v_matl_dtl_id;
23
24 CURSOR Cur_get_step_to_complete(v_batch_id NUMBER) IS
25 SELECT *
26 FROM gme_batch_steps
27 WHERE batch_id = v_batch_id
28 AND step_status NOT IN (gme_common_pvt.g_step_completed, gme_common_pvt.g_step_closed);
29
30 CURSOR Cur_get_phantom_batch(v_batch_id NUMBER) IS
31 SELECT hdr.*
32 FROM gme_material_details dtl, gme_batch_header hdr
33 WHERE dtl.batch_id = v_batch_id
34 AND dtl.line_type = gme_common_pvt.g_line_type_ing
35 AND dtl.phantom_id IS NOT NULL
36 AND hdr.batch_id = dtl.phantom_id
37 AND hdr.batch_status NOT IN (gme_common_pvt.g_batch_completed, gme_common_pvt.g_batch_closed);
38
39 CURSOR cur_lock_batch_ingredients (v_batch_id NUMBER) IS
40 SELECT *
41 FROM gme_material_details
42 WHERE batch_id = v_batch_id
43 AND (line_type = gme_common_pvt.g_line_type_ing OR
44 (line_type = gme_common_pvt.g_line_type_prod AND phantom_line_id IS NOT NULL))
45 FOR UPDATE OF actual_qty NOWAIT;
46
47 CURSOR Cur_lock_batch_products(v_batch_id NUMBER) IS
48 SELECT *
49 FROM gme_material_details
50 WHERE batch_id = v_batch_id
51 AND line_type IN (gme_common_pvt.g_line_type_prod,gme_common_pvt.g_line_type_byprod)
52 AND phantom_line_id IS NULL -- no phantom products
53 FOR UPDATE OF actual_qty NOWAIT;
54
55 l_api_name CONSTANT VARCHAR2 (30) := 'COMPLETE_BATCH';
56 l_table_name CONSTANT VARCHAR2 (30) := 'gme_material_details';
57
58 l_step_status NUMBER;
59 l_matl_dtl_tab_ing gme_common_pvt.material_details_tab;
60 l_matl_dtl_tab gme_common_pvt.material_details_tab;
61 l_btch_hdr gme_batch_header%ROWTYPE;
62 l_btch_hdr_tab gme_common_pvt.batch_headers_tab;
63 l_matl_dtl gme_material_details%ROWTYPE;
64 l_matl_dtl_rec gme_material_details%ROWTYPE;
65 l_step_tab gme_common_pvt.steps_tab;
66 l_batch_step_rec gme_batch_steps%ROWTYPE;
67 l_yield_type NUMBER;
68 l_phantom_batch gme_batch_header%ROWTYPE;
69 l_phantom_batch_rec gme_batch_header%ROWTYPE;
70 l_item_rec mtl_system_items_b%ROWTYPE;
71 l_return_status VARCHAR2(1);
72 l_yield BOOLEAN;
73 l_exception_qty NUMBER;
74
75
76 locked_by_other_user EXCEPTION;
77 batch_lines_locked EXCEPTION;
78 --Bug#5296812
79 error_get_item EXCEPTION;
80 error_update_batch EXCEPTION;
81 error_process_material EXCEPTION;
82 error_complete_batch EXCEPTION;
83 error_complete_step_rec EXCEPTION;
84 error_release_batch EXCEPTION;
85
86 PRAGMA exception_init (locked_by_other_user, -54);
87
88 BEGIN
89 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
90 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
91 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Completing batch_id='||p_batch_header_rec.batch_id);
92 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' actual_cmplt_date='||to_char(p_batch_header_rec.actual_cmplt_date, 'YYYY-MON-DD HH24:MI:SS'));
93 END IF;
94
95 /* Set the return status to success initially */
96 x_return_status := FND_API.G_RET_STS_SUCCESS;
97
98 -- set output structure
99 x_batch_header_rec := p_batch_header_rec;
100
101 -- if the batch is pending, call release batch
102 IF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending THEN
103 -- call release batch
104 gme_release_batch_pvt.release_batch
105 (p_batch_header_rec => p_batch_header_rec
106 ,x_batch_header_rec => x_batch_header_rec
107 ,x_return_status => l_return_status
108 ,x_exception_material_tbl => x_exception_material_tbl);
109 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
110 x_return_status := l_return_status;
111 RAISE error_release_batch;
112 END IF;
113
114 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
115 x_return_status := gme_common_pvt.g_exceptions_err;
116 END IF;
117 END IF;
118
119 -- set batch status
120 x_batch_header_rec.batch_status := gme_common_pvt.g_batch_completed;
121
122 -- no need to set the actual completion date because it is expected to have been in p_batch_header_rec
123
124 -- Update the batch header
125 IF NOT gme_batch_header_dbl.update_row (p_batch_header => x_batch_header_rec) THEN
126 RAISE error_update_batch;
127 END IF;
128
129 -- Update WHO columns for output structure
130 x_batch_header_rec.last_updated_by := gme_common_pvt.g_user_ident;
131 x_batch_header_rec.last_update_date := gme_common_pvt.g_timestamp;
132 x_batch_header_rec.last_update_login := gme_common_pvt.g_login_id;
133
134 -- Lock all the ingredents and phantom product lines associated with the batch
135 OPEN cur_lock_batch_ingredients (x_batch_header_rec.batch_id);
136 FETCH cur_lock_batch_ingredients BULK COLLECT INTO l_matl_dtl_tab_ing;
137 IF SQLCODE = -54 THEN
138 CLOSE cur_lock_batch_ingredients;
139 RAISE batch_lines_locked;
140 END IF;
141 CLOSE cur_lock_batch_ingredients;
142
143 OPEN Cur_lock_batch_products (x_batch_header_rec.batch_id);
144 FETCH Cur_lock_batch_products BULK COLLECT INTO l_matl_dtl_tab;
145 IF SQLCODE = -54 THEN
146 CLOSE Cur_lock_batch_products;
147 RAISE batch_lines_locked;
148 END IF;
149 CLOSE Cur_lock_batch_products;
150
151 -- Process the products...
152 -- 1) yield auto yield products
153 -- 2) set wip plan qty
154
155 FOR i IN 1..l_matl_dtl_tab.COUNT LOOP
156 l_matl_dtl_rec := l_matl_dtl_tab(i);
157
158 l_yield_type := l_matl_dtl_rec.release_type;
159 IF l_yield_type = gme_common_pvt.g_mtl_autobystep_release THEN
160 OPEN Cur_associated_step(l_matl_dtl_rec.material_detail_id);
161 FETCH Cur_associated_step INTO l_step_status;
162 IF Cur_associated_step%NOTFOUND THEN
163 l_yield_type := gme_common_pvt.g_mtl_auto_release;
164 END IF;
165 CLOSE Cur_associated_step;
166 END IF;
167
168 IF l_matl_dtl_rec.line_type IN (gme_common_pvt.g_line_type_prod, gme_common_pvt.g_line_type_byprod) THEN
169 IF l_yield_type <> gme_common_pvt.g_mtl_autobystep_release THEN
170 IF l_yield_type = gme_common_pvt.g_mtl_auto_release THEN
171 l_yield := TRUE;
172 ELSE
173 l_yield := FALSE;
174 END IF;
175
176 process_material
177 (p_material_detail_rec => l_matl_dtl_rec
178 ,p_yield => l_yield
179 ,p_trans_date => x_batch_header_rec.actual_cmplt_date
180 ,p_update_inv_ind => x_batch_header_rec.update_inventory_ind
181 ,x_exception_material_tbl => x_exception_material_tbl
182 ,x_return_status => l_return_status);
183
184 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
185 x_return_status := l_return_status;
186 RAISE error_process_material;
187 END IF;
188
189 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
190 x_return_status := gme_common_pvt.g_exceptions_err;
191 END IF;
192 -- Pawan Kumar added this for bug 5109095
193 ELSE -- of l_yield_type <> gme_common_pvt.g_mtl_autobystep_release THEN
194 -- This will insert exception all completed step products and byprodcuts.
195 --Bug#5296812 Added the call to the item record.Added the condition to check the inv update ind,and transaction ind.Start
196 gme_material_detail_pvt.get_item_rec
197 (p_org_id => l_matl_dtl_rec.organization_id
198 ,p_item_id => l_matl_dtl_rec.inventory_item_id
199 ,x_item_rec => l_item_rec
200 ,x_return_status => l_return_status);
201 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
202 x_return_status := l_return_status;
203 RAISE error_get_item;
204 END IF;
205 IF p_batch_header_rec.update_inventory_ind = 'Y' AND l_item_rec.mtl_transactions_enabled_flag = 'Y' THEN
206 --Bug#5296812 End.
207 IF l_step_status = gme_common_pvt.g_step_completed THEN
208
209 l_exception_qty := l_matl_dtl_rec.wip_plan_qty - l_matl_dtl_rec.actual_qty;
210
211 IF l_exception_qty < 0 THEN
212 l_exception_qty := 0;
213 END IF;
214
215 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
216 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' calling create_batch_exception for material_detail_id='||l_matl_dtl_rec.material_detail_id);
217 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' actual_qty='||l_matl_dtl_rec.actual_qty);
218 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' wip_plan_qty='||l_matl_dtl_rec.wip_plan_qty);
219 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' exception qty='||l_exception_qty);
220 END IF;
221
222 gme_release_batch_pvt.create_batch_exception
223 (p_material_dtl_rec => l_matl_dtl_rec
224 ,p_pending_move_order_ind => NULL -- don't know...allow to calculate
225 ,p_pending_rsrv_ind => NULL -- don't know...allow to calculate
226 ,p_transacted_qty => 0 -- only auto rel products are transacted.. this is for ing
227 ,p_exception_qty => l_exception_qty
228 ,p_force_unconsumed => fnd_api.g_false
229 ,x_exception_material_tbl => x_exception_material_tbl
230 ,x_return_status => l_return_status);
231
232 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
233 x_return_status := l_return_status;
234 RAISE error_complete_batch;
235 END IF;
236
237 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
238 x_return_status := gme_common_pvt.g_exceptions_err;
239 END IF;
240
241 END IF;-- IF l_step_status = gme_common_pvt.g_step_completed THEN
242 END IF;--IF p_batch_header_rec.update_inventory_ind = 'Y'.....
243 -- Pawan Kumar added above for bug 5109095
244 END IF; -- IF l_yield_type <> gme_common_pvt.g_mtl_autobystep_release THEN
245 END IF; -- IF l_matl_dtl_rec.line_type IN (gme_common_pvt.g_line_type_prod, gme_common_pvt.g_line_type_byprod)
246 END LOOP;
247
248 -- Complete any steps that are not complete or closed
249 OPEN Cur_get_step_to_complete(p_batch_header_rec.batch_id);
250 FETCH Cur_get_step_to_complete BULK COLLECT INTO l_step_tab;
251 CLOSE Cur_get_step_to_complete;
252
253 FOR i in 1..l_step_tab.COUNT LOOP
254 -- Bug 7475553 - removing unconditional overwriting of step dates as it should consider
255 -- if the step is already released. The dates will now be set in complete step code.
256 -- l_step_tab(i).actual_start_date := p_batch_header_rec.actual_start_date;
257 -- l_step_tab(i).actual_cmplt_date := p_batch_header_rec.actual_cmplt_date;
258 -- Also, pass in x_batch_header_rec instead of p_batch_header_rec so that step
259 -- code logic works properly as it needs the proper batch_status value.
260 gme_complete_batch_step_pvt.complete_step_recursive
261 (p_batch_step_rec => l_step_tab(i)
262 ,p_batch_header_rec => x_batch_header_rec
263 ,x_batch_step_rec => l_batch_step_rec
264 ,x_exception_material_tbl => x_exception_material_tbl
265 ,x_return_status => l_return_status);
266
267 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
268 x_return_status := l_return_status;
269 RAISE error_complete_step_rec;
270 END IF;
271
272 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
273 x_return_status := gme_common_pvt.g_exceptions_err;
274 END IF;
275 END LOOP;
276
277 -- Complete any phantom batches that are not complete or closed
278 OPEN Cur_get_phantom_batch(p_batch_header_rec.batch_id);
279 FETCH Cur_get_phantom_batch BULK COLLECT INTO l_btch_hdr_tab;
280 CLOSE Cur_get_phantom_batch;
281
282 -- Complete any phantom batches...
283 FOR i IN 1..l_btch_hdr_tab.COUNT LOOP
284 l_btch_hdr_tab(i).actual_cmplt_date := x_batch_header_rec.actual_cmplt_date;
285
286 complete_batch
287 (p_batch_header_rec => l_btch_hdr_tab(i)
288 ,x_exception_material_tbl => x_exception_material_tbl
289 ,x_batch_header_rec => l_btch_hdr
290 ,x_return_status => l_return_status);
291
292 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
293 x_return_status := l_return_status;
294 RAISE error_complete_batch;
295 END IF;
296
297 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
298 x_return_status := gme_common_pvt.g_exceptions_err;
299 END IF;
300
301 END LOOP; -- FOR i IN 1..l_btch_hdr_tab.COUNT LOOP
302
303 -- examine all non-phantom ingredients to determine if there's an exception
304 -- phantom products are reported at the time of yield, so don't double report with phantom ingredient
305 -- all other products are reported in either process material (non auto yield) or yield_material (auto yield)
306 FOR i IN 1..l_matl_dtl_tab_ing.COUNT LOOP
307 l_matl_dtl_rec := l_matl_dtl_tab_ing(i);
308 IF l_matl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing THEN
309 -- don't check for unconsumed first because even if it's fully consumed,
310 -- but has reservation or MO, want to report it
311 --Bug#5296812 Added the call to the item record.Added the condition to check the inv update ind,and transaction ind.Start.
312 gme_material_detail_pvt.get_item_rec
313 (p_org_id => l_matl_dtl_rec.organization_id
314 ,p_item_id => l_matl_dtl_rec.inventory_item_id
315 ,x_item_rec => l_item_rec
316 ,x_return_status => l_return_status);
317 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
318 x_return_status := l_return_status;
319 RAISE error_get_item;
320 END IF;
321 IF p_batch_header_rec.update_inventory_ind = 'Y' AND
322 l_item_rec.mtl_transactions_enabled_flag = 'Y' THEN
323 --Bug#5296812 End.
324 l_exception_qty := l_matl_dtl_rec.wip_plan_qty - l_matl_dtl_rec.actual_qty;
325
326 IF l_exception_qty < 0 THEN
327 l_exception_qty := 0;
328 END IF;
329
330 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
331 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' calling create_batch_exception for material_detail_id='||l_matl_dtl_rec.material_detail_id);
332 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' actual_qty='||l_matl_dtl_rec.actual_qty);
333 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' wip_plan_qty='||l_matl_dtl_rec.wip_plan_qty);
334 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' exception qty='||l_exception_qty);
335 END IF;
336
337 gme_release_batch_pvt.create_batch_exception
338 (p_material_dtl_rec => l_matl_dtl_rec
339 ,p_pending_move_order_ind => NULL -- don't know...allow to calculate
340 ,p_pending_rsrv_ind => NULL -- don't know...allow to calculate
341 ,p_transacted_qty => 0 -- only auto rel products are transacted.. this is for ing
342 ,p_exception_qty => l_exception_qty
343 ,p_force_unconsumed => fnd_api.g_false
344 ,x_exception_material_tbl => x_exception_material_tbl
345 ,x_return_status => l_return_status);
346
347 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
348 x_return_status := l_return_status;
349 RAISE error_complete_batch;
350 END IF;
351
352 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
353 x_return_status := gme_common_pvt.g_exceptions_err;
354 END IF;
355 END IF; --IF p_batch_header_rec.update_inventory_ind = 'Y'...
356 END IF;
357 END LOOP;
358
359 -- Bug 12571877 - Pass in correct batch record.
360 IF NOT gme_common_pvt.create_history
361 -- (p_batch_header_rec => p_batch_header_rec
362 (p_batch_header_rec => x_batch_header_rec
363 ,p_original_status => gme_common_pvt.g_batch_wip
364 ,p_event_id => NVL(gme_common_pvt.g_transaction_header_id,-9999)) THEN
365 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
366 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' create history returned error');
367 END IF;
368 END IF;
369
370 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
371 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
372 END IF;
373
374 EXCEPTION
375 WHEN error_update_batch THEN
376 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
377 x_return_status := FND_API.g_ret_sts_unexp_error;
378 --Bug#5296812 Handling the raised exception error_get_item.
379 WHEN error_process_material OR error_complete_batch OR
380 error_complete_step_rec OR error_release_batch OR error_get_item THEN
381 NULL;
382 WHEN locked_by_other_user or batch_lines_locked THEN
383 gme_common_pvt.log_message (
384 'GME_RECORD_LOCKED',
385 'TABLE_NAME',
386 l_table_name
387 );
388 x_return_status := FND_API.G_RET_STS_ERROR;
389 WHEN OTHERS THEN
390 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
391 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
392 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
393 END IF;
394 x_return_status := FND_API.g_ret_sts_unexp_error;
395 END complete_batch;
396
397
398 PROCEDURE process_material
399 (p_material_detail_rec IN gme_material_details%ROWTYPE
400 ,p_yield IN BOOLEAN
401 ,p_trans_date IN DATE
402 ,p_update_inv_ind IN VARCHAR2
403 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
404 ,x_return_status OUT NOCOPY VARCHAR2) IS
405
406
407 l_api_name CONSTANT VARCHAR2 (30) := 'process_material';
408
409 l_matl_dtl_rec gme_material_details%ROWTYPE;
410 l_in_phantom_batch_rec gme_batch_header%ROWTYPE;
411 l_phantom_batch_rec gme_batch_header%ROWTYPE;
412 l_return_status VARCHAR2(1);
413 l_item_rec mtl_system_items_b%ROWTYPE;
414 l_actual_qty NUMBER;
415 l_start_actual_qty NUMBER;
416 l_exception_qty NUMBER;
417
418 error_fetch_batch EXCEPTION;
419 error_complete_batch EXCEPTION;
420 error_yield_material EXCEPTION;
421 error_update_row EXCEPTION;
422 error_get_item EXCEPTION;
423 error_batch_exception EXCEPTION;
424
425
426
427 BEGIN
428
429 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
430 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
431 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Processing material material_detail_id='||p_material_detail_rec.material_detail_id);
432 END IF;
433
434 /* Set the return status to success initially */
435 x_return_status := FND_API.G_RET_STS_SUCCESS;
436
437 -- Process the material
438 -- 1) complete product
439 -- 2) complete phantom batch for phantom ingredient
440
441 l_matl_dtl_rec := p_material_detail_rec;
442
443 -- if it's a phantom ingredient, then complete the phantom batch
444 -- which will cause the phantom product to be yielded (the passed in ingredient will be consumed in trxm mgr
445
446 -- complete phantom batch
447 IF l_matl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing AND l_matl_dtl_rec.phantom_id IS NOT NULL THEN -- phantom ingredient -> complete the phantom batch
448 l_phantom_batch_rec.batch_id := l_matl_dtl_rec.phantom_id;
449 IF NOT gme_batch_header_dbl.fetch_row(l_phantom_batch_rec, l_phantom_batch_rec) THEN
450 RAISE error_fetch_batch;
451 END IF;
452
453 IF l_phantom_batch_rec.batch_status IN (gme_common_pvt.g_batch_pending, gme_common_pvt.g_batch_wip) THEN
454 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
455 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' processing phantom ingredient material_detail_id='||l_matl_dtl_rec.material_detail_id);
456 END IF;
457 -- pass in the phantom line id so that release batch will know to yield that product
458 l_in_phantom_batch_rec := l_phantom_batch_rec;
459
460 l_in_phantom_batch_rec.actual_start_date := NVL(l_in_phantom_batch_rec.actual_start_date, p_trans_date);
461 l_in_phantom_batch_rec.actual_cmplt_date := p_trans_date;
462
463 complete_batch
464 (p_batch_header_rec => l_in_phantom_batch_rec
465 ,x_exception_material_tbl => x_exception_material_tbl
466 ,x_batch_header_rec => l_phantom_batch_rec
467 ,x_return_status => l_return_status);
468
469 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
470 x_return_status := l_return_status;
471 RAISE error_complete_batch;
472 END IF;
473
474 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
475 x_return_status := gme_common_pvt.g_exceptions_err;
476 END IF;
477 END IF; -- IF l_phantom_batch_rec.batch_status IN (gme_common_pvt.g_batch_pending, gme_common_pvt.g_batch_wip)
478 ELSIF l_matl_dtl_rec.line_type IN (gme_common_pvt.g_line_type_prod, gme_common_pvt.g_line_type_byprod) AND p_yield THEN
479 gme_material_detail_pvt.get_item_rec
480 (p_org_id => l_matl_dtl_rec.organization_id
481 ,p_item_id => l_matl_dtl_rec.inventory_item_id
482 ,x_item_rec => l_item_rec
483 ,x_return_status => l_return_status);
484
485 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
486 x_return_status := l_return_status;
487 RAISE error_get_item;
488 END IF;
489
490 IF p_update_inv_ind = 'Y' AND
491 l_item_rec.mtl_transactions_enabled_flag = 'Y' THEN
492 l_start_actual_qty := l_matl_dtl_rec.actual_qty;
493 yield_material(p_material_dtl_rec => l_matl_dtl_rec
494 ,p_yield_qty => NULL -- take the entire wip plan qty
495 ,p_trans_date => p_trans_date
496 ,p_item_rec => l_item_rec
497 ,p_force_unconsumed => fnd_api.g_false
498 ,x_exception_material_tbl => x_exception_material_tbl
499 ,x_actual_qty => l_actual_qty
500 ,x_return_status => l_return_status);
501
502 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
503 x_return_status := l_return_status;
504 RAISE error_yield_material;
505 END IF;
506
507 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
508 x_return_status := gme_common_pvt.g_exceptions_err;
509 END IF;
510
511 l_matl_dtl_rec.actual_qty := l_actual_qty;
512
513 -- if actual qty is met, we still need to see if there are pending product lots...
514 -- this is not required in yield material... it's a requirement of complete batch
515 IF l_matl_dtl_rec.actual_qty >= l_matl_dtl_rec.wip_plan_qty THEN
516 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
517 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' calling create_batch_exception after yield_material not for unyielded but for possibility of pplots for material_detail_id='||l_matl_dtl_rec.material_detail_id);
518 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' actual_qty='||l_matl_dtl_rec.actual_qty);
519 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' wip_plan_qty='||l_matl_dtl_rec.wip_plan_qty);
520 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' exception qty='||(l_matl_dtl_rec.actual_qty - l_matl_dtl_rec.wip_plan_qty));
521 END IF;
522
523 gme_release_batch_pvt.create_batch_exception
524 (p_material_dtl_rec => l_matl_dtl_rec
525 ,p_pending_move_order_ind => FALSE -- product doesn't have MO
526 ,p_pending_rsrv_ind => NULL -- let proc figure out; for product, looks at pplot
527 ,p_transacted_qty => l_actual_qty - l_start_actual_qty
528 ,p_exception_qty => 0
529 ,p_force_unconsumed => fnd_api.g_false
530 ,x_exception_material_tbl => x_exception_material_tbl
531 ,x_return_status => l_return_status);
532
533 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
534 x_return_status := l_return_status;
535 RAISE error_yield_material;
536 END IF;
537
538 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
539 x_return_status := gme_common_pvt.g_exceptions_err;
540 END IF;
541 END IF;
542
543 ELSE
544 l_matl_dtl_rec.actual_qty := l_matl_dtl_rec.plan_qty;
545 END IF;
546 ELSE -- check for exception... this should be for all products that are not auto release
547 --Bug#5296812 Added the call to the item record.Added the condition to check the inv update ind,and transaction ind.
548 gme_material_detail_pvt.get_item_rec
549 (p_org_id => l_matl_dtl_rec.organization_id
550 ,p_item_id => l_matl_dtl_rec.inventory_item_id
551 ,x_item_rec => l_item_rec
552 ,x_return_status => l_return_status);
553 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
554 x_return_status := l_return_status;
555 RAISE error_get_item;
556 END IF;
557 IF p_update_inv_ind = 'Y' AND l_item_rec.mtl_transactions_enabled_flag = 'Y' THEN
558 --Bug#5296812 End.
559 l_exception_qty := l_matl_dtl_rec.wip_plan_qty - l_matl_dtl_rec.actual_qty;
560
561 IF l_exception_qty < 0 THEN
562 l_exception_qty := 0;
563 END IF;
564
565 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
566 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' calling create_batch_exception for material_detail_id='||l_matl_dtl_rec.material_detail_id);
567 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' actual_qty='||l_matl_dtl_rec.actual_qty);
568 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' wip_plan_qty='||l_matl_dtl_rec.wip_plan_qty);
569 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' exception qty='||l_exception_qty);
570 END IF;
571
572 gme_release_batch_pvt.create_batch_exception
573 (p_material_dtl_rec => l_matl_dtl_rec
574 ,p_pending_move_order_ind => FALSE -- product doesn't have MO
575 ,p_pending_rsrv_ind => NULL -- let proc figure out; for product, looks at pplot
576 ,p_transacted_qty => 0 -- products other than auto yield don't get transacted in complete
577 ,p_exception_qty => l_exception_qty
578 ,p_force_unconsumed => fnd_api.g_false
579 ,x_exception_material_tbl => x_exception_material_tbl
580 ,x_return_status => l_return_status);
581
582 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
583 x_return_status := l_return_status;
584 RAISE error_batch_exception;
585 END IF;
586
587 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
588 x_return_status := gme_common_pvt.g_exceptions_err;
589 END IF;
590 END IF;--IF p_update_inv_ind = 'Y' AND...
591 END IF; -- IF l_matl_dtl_rec.phantom_id IS NOT NULL...
592
593 -- set WIP plan qty
594 -- Commented the below line for Bug 8483455
595 --l_matl_dtl_rec.wip_plan_qty := l_matl_dtl_rec.plan_qty;
596
597 IF NOT gme_material_details_dbl.update_row (l_matl_dtl_rec) THEN
598 RAISE error_update_row;
599 END IF;
600
601 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
602 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
603 END IF;
604
605 EXCEPTION
606 WHEN error_fetch_batch OR error_update_row THEN
607 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
608 x_return_status := FND_API.g_ret_sts_unexp_error;
609 WHEN error_complete_batch OR error_yield_material OR error_get_item OR error_batch_exception THEN
610 NULL;
611 WHEN OTHERS THEN
612 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
613 IF g_debug <= gme_debug.g_log_procedure THEN
614 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
615 END IF;
616 x_return_status := FND_API.g_ret_sts_unexp_error;
617 END process_material;
618
619
620 -- Note: p_yield_qty is the target actual qty; for incr, it's also the target, not the incr
621 PROCEDURE yield_material(p_material_dtl_rec IN gme_material_details%ROWTYPE
622 ,p_yield_qty IN NUMBER
623 ,p_trans_date IN DATE
624 ,p_item_rec IN mtl_system_items_b%ROWTYPE
625 ,p_force_unconsumed IN VARCHAR2
626 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
627 ,x_actual_qty OUT NOCOPY NUMBER
628 ,x_return_status OUT NOCOPY VARCHAR2) IS
629
630 l_api_name CONSTANT VARCHAR2 (30) := 'YIELD_MATERIAL';
631
632 l_pending_product_lot_tab gme_common_pvt.pending_lots_tab;
633 l_pp_lot_rec gme_pending_product_lots%ROWTYPE;
634 i NUMBER;
635
636 l_yield_qty NUMBER;
637 l_trans_date DATE;
638 l_subinv VARCHAR2(10);
639 l_locator_id NUMBER;
640 l_revision VARCHAR2(3);
641 l_eff_locator_control NUMBER;
642 l_start_actual_qty NUMBER;
643 l_return_status VARCHAR2(1);
644
645 CURSOR cur_get_item_revision(v_item_id NUMBER, v_org_id NUMBER) IS
646 SELECT revision
647 FROM mtl_item_revisions_b
648 WHERE inventory_item_id = v_item_id
649 AND organization_id = v_org_id
650 AND effectivity_date <= gme_common_pvt.g_timestamp
651 ORDER BY effectivity_date desc;
652
653 error_build_trxn EXCEPTION;
654 error_get_exception EXCEPTION;
655 error_nothing_to_yield EXCEPTION;
656 error_get_pplot EXCEPTION;
657 no_yield_required EXCEPTION;
658
659 BEGIN
660
661 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
662 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
663 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' material_detail_id='||p_material_dtl_rec.material_detail_id);
664 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_yield_qty='||p_yield_qty);
665 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_trans_date='||to_char(p_trans_date,
666 'YYYY-MON-DD HH24:MI:SS'));
667 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' line_no='||p_material_dtl_rec.line_no);
668 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' line_type='||p_material_dtl_rec.line_type);
669 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_force_unconsumed='||p_force_unconsumed);
670 END IF;
671
672 /* Set the return status to success initially */
673 x_return_status := FND_API.G_RET_STS_SUCCESS;
674
675 x_actual_qty := p_material_dtl_rec.actual_qty;
676
677 -- following global is set only for migration purposes, where transactions need not be created,
678 -- this will only be set for complete step; complete batch is not called from migrate; however
679 -- if this is needed for complete batch, logic will work there also
680 IF gme_release_batch_pvt.g_bypass_txn_creation = 1 THEN
681 RAISE no_yield_required;
682 END IF;
683 /*Pawan Kumar 08-25-2006 bug 5486066 added nvl for wip_plan_qty
684 during direct completion, the wip plan qty is also null
685 So this was not getting caught in l_yield_qty <= x_actual_qty and this was
686 sending transaction quantity as null to transactions where it was failing*/
687
688 l_yield_qty := NVL(p_yield_qty, nvl(p_material_dtl_rec.wip_plan_qty,p_material_dtl_rec.plan_qty));
689 l_trans_date := NVL(p_trans_date, gme_common_pvt.g_timestamp);
690
691 l_start_actual_qty := x_actual_qty;
692
693 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
694 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_yield_qty='||l_yield_qty);
695 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_trans_date='||to_char(l_trans_date, 'YYYY-MON-DD HH24:MI:SS'));
696 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
697 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_start_actual_qty='||l_start_actual_qty);
698 END IF;
699
700 IF l_yield_qty <= x_actual_qty THEN
701 -- this returns as success for now; there's nothing additional to yield
702 RAISE error_nothing_to_yield;
703 END IF;
704
705 IF p_material_dtl_rec.subinventory IS NULL THEN
706 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
707 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' subinv on material is NULL; cant yield anything; get exceptions');
708 END IF;
709 RAISE error_get_exception;
710 END IF;
711
712 IF p_material_dtl_rec.locator_id IS NULL THEN
713 -- check if it's locator control, we need a locator...
714 /* Bug 5441643 Added NVL condition for location control code*/
715 l_eff_locator_control :=
716 gme_common_pvt.eff_locator_control
717 (p_organization_id => p_material_dtl_rec.organization_id
718 ,p_org_control => gme_common_pvt.g_org_locator_control
719 ,p_subinventory => p_material_dtl_rec.subinventory
720 ,p_item_control => NVL(p_item_rec.location_control_code,1)
721 ,p_item_loc_restrict => p_item_rec.restrict_locators_code
722 ,p_action => gme_common_pvt.g_prod_comp_txn_action);
723 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
724 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_eff_locator_control='||l_eff_locator_control);
725 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' organization_id='||p_material_dtl_rec.organization_id);
726 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' g_org_locator_control='||gme_common_pvt.g_org_locator_control);
727 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' subinventory='||p_material_dtl_rec.subinventory);
728 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' inventory_item_id='||p_item_rec.inventory_item_id);
729 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' location_control_code='||p_item_rec.location_control_code);
730 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' restrict_locators_code='||p_item_rec.restrict_locators_code);
731 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_action='||gme_common_pvt.g_prod_comp_txn_action);
732 END IF;
733 IF l_eff_locator_control <> 1 THEN
734 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
735 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' locator on material is NULL and material is eff locator control; cant yield anything; get exceptions');
736 END IF;
737 RAISE error_get_exception;
738 END IF;
739 END IF;
740
741 l_subinv := p_material_dtl_rec.subinventory;
742 l_locator_id := p_material_dtl_rec.locator_id;
743
744 IF p_item_rec.revision_qty_control_code = 2 THEN -- under revision control
745 IF p_material_dtl_rec.revision IS NOT NULL THEN
746 l_revision := p_material_dtl_rec.revision;
747 ELSE
748 OPEN cur_get_item_revision(p_material_dtl_rec.inventory_item_id,
749 p_material_dtl_rec.organization_id);
750 FETCH cur_get_item_revision INTO l_revision;
751 CLOSE cur_get_item_revision;
752 END IF;
753 END IF; -- IF p_revision_qty_control_code = 2
754
755 IF p_item_rec.lot_control_code = 1 THEN -- not lot control
756 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
757 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' item not lot control');
758 END IF;
759
760 build_and_create_transaction
761 (p_mtl_dtl_rec => p_material_dtl_rec
762 ,p_pp_lot_rec => NULL
763 ,p_subinv => l_subinv
764 ,p_locator_id => l_locator_id
765 ,p_trans_date => l_trans_date
766 ,p_yield_qty => l_yield_qty
767 ,p_revision => l_revision
768 ,p_sec_uom_code => p_item_rec.secondary_uom_code
769 ,x_actual_qty => x_actual_qty
770 ,x_return_status => l_return_status);
771
772 IF l_return_status NOT IN (gme_common_pvt.g_not_transactable, FND_API.G_RET_STS_SUCCESS) THEN
773 x_return_status := l_return_status;
774 RAISE error_build_trxn;
775 END IF;
776 ELSE -- lot control... go to pending product lots
777 gme_pending_product_lots_pvt.get_pending_lot
778 (p_material_detail_id => p_material_dtl_rec.material_detail_id
779 ,x_return_status => l_return_status
780 ,x_pending_product_lot_tbl => l_pending_product_lot_tab);
781 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
782 x_return_status := l_return_status;
783 RAISE error_get_pplot;
784 END IF;
785
786 i := 1;
787
788 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
789 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_yield_qty='||l_yield_qty);
790 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
791 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' count from pplot='||l_pending_product_lot_tab.COUNT);
792 END IF;
793
794 WHILE l_yield_qty > x_actual_qty AND i <= l_pending_product_lot_tab.COUNT LOOP
795
796 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
797 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' pending lot loop i='||i);
798 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' pending lot loop x_actual_qty='||x_actual_qty);
799 END IF;
800
801 l_pp_lot_rec := l_pending_product_lot_tab(i);
802
803 build_and_create_transaction
804 (p_mtl_dtl_rec => p_material_dtl_rec
805 ,p_pp_lot_rec => l_pp_lot_rec
806 ,p_subinv => l_subinv
807 ,p_locator_id => l_locator_id
808 ,p_trans_date => l_trans_date
809 ,p_yield_qty => l_yield_qty
810 ,p_revision => l_revision
811 ,p_sec_uom_code => p_item_rec.secondary_uom_code
812 ,x_actual_qty => x_actual_qty
813 ,x_return_status => l_return_status);
814 IF l_return_status NOT IN (gme_common_pvt.g_not_transactable, FND_API.G_RET_STS_SUCCESS) THEN
815 x_return_status := l_return_status;
816 RAISE error_build_trxn;
817 END IF;
818
819 i := i + 1; -- move on to the next lot
820 END LOOP;
821 END IF; -- IF p_item_rec.lot_control_code = 1 THEN
822
823 IF x_actual_qty < l_yield_qty THEN
824 RAISE error_get_exception;
825 END IF;
826
827 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
828 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
829 END IF;
830
831
832 EXCEPTION
833 WHEN error_build_trxn OR error_get_pplot OR no_yield_required THEN
834 NULL;
835 WHEN error_nothing_to_yield THEN
836 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
837 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' exiting without yield because actual_qty= '||x_actual_qty||' and target yield qty = '||l_yield_qty);
838 END IF;
839
840 WHEN error_get_exception THEN
841 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
842 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' exception block for get exceptions:');
843 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
844 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_start_actual_qty='||l_start_actual_qty);
845 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_yield_qty='||l_yield_qty);
846 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' exception qty='||(l_yield_qty - x_actual_qty));
847 END IF;
848
849 gme_release_batch_pvt.create_batch_exception
850 (p_material_dtl_rec => p_material_dtl_rec
851 ,p_pending_move_order_ind => FALSE
852 ,p_pending_rsrv_ind => NULL -- let proc figure out; for product, looks at pplot
853 ,p_transacted_qty => x_actual_qty - l_start_actual_qty
854 ,p_exception_qty => l_yield_qty - x_actual_qty
855 ,p_force_unconsumed => p_force_unconsumed
856 ,x_exception_material_tbl => x_exception_material_tbl
857 ,x_return_status => x_return_status);
858
859 WHEN OTHERS THEN
860 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
861 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
862 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
863 END IF;
864 x_return_status := FND_API.g_ret_sts_unexp_error;
865 END yield_material;
866
867 PROCEDURE build_and_create_transaction
868 (p_mtl_dtl_rec IN gme_material_details%ROWTYPE
869 ,p_pp_lot_rec IN gme_pending_product_lots%ROWTYPE
870 ,p_subinv IN VARCHAR2
871 ,p_locator_id IN NUMBER
872 ,p_trans_date IN DATE
873 ,p_yield_qty IN NUMBER
874 ,p_revision IN VARCHAR2 DEFAULT NULL
875 ,p_sec_uom_code IN VARCHAR2 DEFAULT NULL
876 ,x_actual_qty IN OUT NOCOPY NUMBER
877 ,x_return_status OUT NOCOPY VARCHAR2) IS
878
879 l_api_name CONSTANT VARCHAR2 (30) := 'build_and_create_transaction';
880
881 l_transaction_rec mtl_transactions_interface%ROWTYPE;
882 l_lot_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
883 l_trxn_qty NUMBER;
884 l_dtl_qty NUMBER;
885 l_sec_qty NUMBER;
886
887 -- Bug 11725091 - Added following variable and cursor.
888 l_prim_qty NUMBER;
889 l_prim_uom VARCHAR2(3);
890 l_from_um VARCHAR2(3);
891 l_to_um VARCHAR2(3);
892 l_item_no mtl_system_items_kfv.concatenated_segments%TYPE;
893
894 um_convert_error EXCEPTION;
895
896 CURSOR item_no_cursor(v_inventory_item_id NUMBER,
897 v_org_id NUMBER) IS
898 SELECT concatenated_segments, primary_uom_code
899 FROM mtl_system_items_kfv
900 WHERE inventory_item_id = v_inventory_item_id
901 AND organization_id = v_org_id;
902
903 error_build_mmti EXCEPTION;
904 error_create_trxn EXCEPTION;
905 error_relieve_pp_lot EXCEPTION;
906
907 BEGIN
908 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
909 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
910 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' material_detail_id='||p_mtl_dtl_rec.material_detail_id);
911 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_subinv='||p_subinv);
912 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_locator_id='||p_locator_id);
913 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_yield_qty='||p_yield_qty);
914 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_revision='||p_revision);
915 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_sec_uom_code='||p_sec_uom_code);
916 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
917 END IF;
918
919 /* Set the return status to success initially */
920 x_return_status := FND_API.G_RET_STS_SUCCESS;
921
922 constr_mmti(p_mtl_dtl_rec => p_mtl_dtl_rec
923 ,p_yield_qty => p_yield_qty
924 ,p_subinv => p_subinv
925 ,p_revision => p_revision
926 ,p_locator_id => p_locator_id
927 ,p_pp_lot_rec => p_pp_lot_rec
928 ,x_mmti_rec => l_transaction_rec
929 ,x_mmli_tbl => l_lot_tbl
930 ,x_sec_qty => l_sec_qty
931 ,x_dtl_qty => l_dtl_qty
932 ,x_return_status => x_return_status);
933
934 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
935 RAISE error_build_mmti;
936 END IF;
937
938 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
939 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after constr_mmti l_dtl_qty := '||l_dtl_qty);
940 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after constr_mmti l_sec_qty := '||l_sec_qty);
941 END IF;
942
943 -- Bug 11725091 - Let's fetch this data right up front even though it may not be used.
944 OPEN item_no_cursor(p_mtl_dtl_rec.inventory_item_id, p_mtl_dtl_rec.organization_id);
945 FETCH item_no_cursor INTO l_item_no, l_prim_uom;
946 CLOSE item_no_cursor;
947
948 -- Bug 7262112 - Added '=' to condition. No need to recompute secondary values if yielding the full amount.
949 IF l_dtl_qty <= p_yield_qty - x_actual_qty THEN
950 l_trxn_qty := l_dtl_qty;
951 ELSE
952 l_trxn_qty := p_yield_qty - x_actual_qty;
953 /* Bug 5256543 l_sec_qty was set to null that was incorrect it has to be recalculated from new l_trxn_qty */
954 IF (p_sec_uom_code IS NOT NULL) THEN
955 l_sec_qty := INV_CONVERT.inv_um_convert
956 (item_id => p_mtl_dtl_rec.inventory_item_id
957 ,lot_number => p_pp_lot_rec.lot_number
958 ,organization_id => p_mtl_dtl_rec.organization_id
959 ,PRECISION => gme_common_pvt.g_precision
960 ,from_quantity => l_trxn_qty
961 ,from_unit => p_mtl_dtl_rec.dtl_um
962 ,to_unit => p_sec_uom_code
963 ,from_name => NULL
964 ,to_name => NULL);
965
966 IF l_sec_qty = -99999 THEN
967 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
968 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' inv_convert.inv_um_convert returned error');
969 END IF;
970 l_from_um := p_mtl_dtl_rec.dtl_um;
971 l_to_um := p_sec_uom_code;
972 RAISE um_convert_error;
973 END IF;
974 END IF;
975 END IF;
976
977 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
978 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' calculated l_trxn_qty := '||l_trxn_qty);
979 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' calculated l_sec_qty := '||l_sec_qty);
980 END IF;
981
982 IF p_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_prod THEN
983 l_transaction_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
984 ELSE
985 l_transaction_rec.transaction_type_id := gme_common_pvt.g_byprod_completion;
986 END IF;
987
988 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
989 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_transaction_rec.transaction_type_id := '||l_transaction_rec.transaction_type_id);
990 END IF;
991
992 l_transaction_rec.transaction_date := p_trans_date;
993 l_transaction_rec.transaction_quantity := l_trxn_qty;
994 l_transaction_rec.secondary_uom_code := p_sec_uom_code;
995
996 IF l_sec_qty IS NOT NULL THEN
997 l_transaction_rec.secondary_transaction_quantity := l_sec_qty;
998 END IF;
999
1000 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1001 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' transaction_date='||to_char(l_transaction_rec.transaction_date
1002 ,'YYYY-MON-DD HH24:MI:SS'));
1003 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' transaction_qty='||l_transaction_rec.transaction_quantity);
1004 END IF;
1005
1006 l_transaction_rec.transaction_uom := p_mtl_dtl_rec.dtl_um;
1007
1008 -- Bug 11725091 - Let's initialize primary qty also.
1009 l_prim_qty := l_trxn_qty;
1010 IF (p_mtl_dtl_rec.dtl_um <> l_prim_uom) THEN
1011 l_prim_qty := INV_CONVERT.inv_um_convert
1012 (item_id => p_mtl_dtl_rec.inventory_item_id
1013 ,lot_number => p_pp_lot_rec.lot_number
1014 ,organization_id => p_mtl_dtl_rec.organization_id
1015 ,PRECISION => gme_common_pvt.g_precision
1016 ,from_quantity => l_trxn_qty
1017 ,from_unit => p_mtl_dtl_rec.dtl_um
1018 ,to_unit => l_prim_uom
1019 ,from_name => NULL
1020 ,to_name => NULL);
1021
1022 IF l_prim_qty = -99999 THEN
1023 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1024 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' inv_convert.inv_um_convert returned error');
1025 END IF;
1026 l_from_um := p_mtl_dtl_rec.dtl_um;
1027 l_to_um := l_prim_uom;
1028 RAISE um_convert_error;
1029 END IF;
1030 END IF;
1031
1032 -- l_prim_qty will always have a value at this point.
1033 l_transaction_rec.primary_quantity := l_prim_qty;
1034 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1035 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' primary_qty is known:'||l_transaction_rec.primary_quantity);
1036 END IF;
1037
1038 IF l_lot_tbl.count > 0 THEN
1039 IF l_lot_tbl(1).lot_number IS NOT NULL THEN
1040 l_lot_tbl(1).transaction_quantity := l_transaction_rec.transaction_quantity;
1041 l_lot_tbl(1).primary_quantity := l_prim_qty;
1042 IF l_sec_qty IS NOT NULL THEN
1043 l_lot_tbl(1).secondary_transaction_quantity := l_sec_qty;
1044 END IF;
1045 END IF;
1046 END IF;
1047
1048 gme_transactions_pvt.create_material_txn
1049 (p_mmti_rec => l_transaction_rec
1050 ,p_mmli_tbl => l_lot_tbl
1051 ,x_return_status => x_return_status);
1052
1053 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1054 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1055 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' gme_transactions_pvt.create_transaction returned error');
1056 END IF;
1057 RAISE error_create_trxn;
1058 END IF;
1059
1060 x_actual_qty := x_actual_qty + l_trxn_qty;
1061
1062 -- If pending product lot, then decrease qty, entry remains if the qty goes to zero
1063 IF p_pp_lot_rec.pending_product_lot_id IS NOT NULL THEN
1064 gme_pending_product_lots_pvt.relieve_pending_lot
1065 (p_pending_lot_id => p_pp_lot_rec.pending_product_lot_id
1066 ,p_quantity => l_trxn_qty
1067 ,p_secondary_quantity => l_sec_qty
1068 ,x_return_status => x_return_status);
1069
1070 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1071 RAISE error_relieve_pp_lot;
1072 END IF;
1073 END IF;
1074
1075 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1076 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1077 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
1078 END IF;
1079
1080 EXCEPTION
1081 WHEN um_convert_error THEN
1082
1083 fnd_message.set_name ('GMI', 'IC_API_UOM_CONVERSION_ERROR');
1084 fnd_message.set_token ('ITEM_NO', l_item_no);
1085 fnd_message.set_token ('FROM_UOM',l_from_um);
1086 fnd_message.set_token ('TO_UOM', l_to_um);
1087 fnd_msg_pub.ADD;
1088 x_return_status := FND_API.g_ret_sts_error;
1089 WHEN error_create_trxn OR error_relieve_pp_lot OR error_build_mmti THEN
1090 NULL;
1091 WHEN OTHERS THEN
1092 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1093 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
1094 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
1095 END IF;
1096 x_return_status := FND_API.g_ret_sts_unexp_error;
1097 END build_and_create_transaction;
1098
1099 PROCEDURE constr_mmti
1100 (p_mtl_dtl_rec IN gme_material_details%ROWTYPE
1101 ,p_yield_qty IN NUMBER
1102 ,p_subinv IN VARCHAR2
1103 ,p_locator_id IN NUMBER
1104 ,p_revision IN VARCHAR2
1105 ,p_pp_lot_rec IN gme_pending_product_lots%ROWTYPE
1106 ,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
1107 ,x_mmli_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_inter_tbl
1108 ,x_sec_qty OUT NOCOPY NUMBER
1109 ,x_dtl_qty OUT NOCOPY NUMBER
1110 ,x_return_status OUT NOCOPY VARCHAR2) IS
1111
1112 l_api_name CONSTANT VARCHAR2 (30) := 'CONSTR_MMTI';
1113 BEGIN
1114
1115 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1116 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1117 END IF;
1118
1119 /* Initially let us assign the return status to success */
1120 x_return_status := FND_API.g_ret_sts_success;
1121
1122 -- consturct mtl_transactions_interface
1123 x_mmti_rec.transaction_source_id := p_mtl_dtl_rec.batch_id; -- batch_id
1124 x_mmti_rec.trx_source_line_id := p_mtl_dtl_rec.material_detail_id; -- material_detail_id
1125 x_mmti_rec.inventory_item_id := p_mtl_dtl_rec.inventory_item_id;
1126 x_mmti_rec.organization_id := p_mtl_dtl_rec.organization_id;
1127 x_mmti_rec.subinventory_code := p_subinv;
1128 x_mmti_rec.locator_id := p_locator_id;
1129 x_mmti_rec.revision := p_revision;
1130 x_mmti_rec.transaction_sequence_id := p_pp_lot_rec.pending_product_lot_id;
1131 x_dtl_qty := p_yield_qty;
1132 -- construct mtl_transaction_lots_interface
1133 IF p_pp_lot_rec.lot_number IS NOT NULL THEN
1134 x_mmli_tbl(1).lot_number := p_pp_lot_rec.lot_number;
1135 x_mmli_tbl(1).reason_id := p_pp_lot_rec.reason_id;
1136 /* Bug 5256543 Assign revision only if not null otherwise it will come from mtl dtl line */
1137 IF (p_pp_lot_rec.revision IS NOT NULL) THEN
1138 x_mmti_rec.revision := p_pp_lot_rec.revision;
1139 END IF;
1140 x_mmti_rec.reason_id := p_pp_lot_rec.reason_id;
1141 x_dtl_qty := p_pp_lot_rec.quantity;
1142 x_sec_qty := p_pp_lot_rec.secondary_quantity;
1143 END IF;
1144
1145 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1146 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1147 END IF;
1148
1149 EXCEPTION
1150 WHEN OTHERS THEN
1151 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1152 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
1153 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
1154 END IF;
1155 x_return_status := FND_API.g_ret_sts_unexp_error;
1156 END constr_mmti;
1157
1158 PROCEDURE validate_batch_for_complete (p_batch_header_rec IN gme_batch_header%ROWTYPE
1159 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
1160 ,x_return_status OUT NOCOPY VARCHAR2) IS
1161
1162 l_api_name CONSTANT VARCHAR2 (30) := 'validate_batch_for_complete';
1163
1164 CURSOR Cur_gme_batch_steps (v_batch_id NUMBER) IS
1165 SELECT count(1)
1166 FROM gme_batch_steps
1167 WHERE step_status NOT IN (gme_common_pvt.g_step_completed, gme_common_pvt.g_step_closed)
1168 AND batch_id = v_batch_id
1169 AND rownum = 1;
1170
1171 l_is_step NUMBER;
1172 l_batch_header_rec gme_batch_header%ROWTYPE;
1173 CURSOR cur_validity_rule(v_recipe_validity_rule_id NUMBER)
1174 IS
1175 SELECT *
1176 FROM gmd_recipe_validity_rules
1177 WHERE recipe_validity_rule_id = v_recipe_validity_rule_id;
1178
1179 CURSOR cur_validity_status_type(v_validity_rule_status VARCHAR2)
1180 IS
1181 SELECT status_type
1182 FROM gmd_status
1183 WHERE status_code=v_validity_rule_status;
1184
1185 l_validity_rule gmd_recipe_validity_rules%ROWTYPE;
1186 l_status_type GMD_STATUS.status_type%TYPE;
1187 error_vr_not_found EXCEPTION;
1188 error_validity_status EXCEPTION;
1189 error_batch_type EXCEPTION;
1190 error_batch_status EXCEPTION;
1191 error_phantom EXCEPTION;
1192 error_steps_not_complete EXCEPTION;
1193 error_cmplt_date EXCEPTION;
1194 error_future_date EXCEPTION;
1195 error_vr_dates EXCEPTION;
1196 BEGIN
1197 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
1198 gme_debug.g_log_procedure THEN
1199 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1200 || l_api_name);
1201 END IF;
1202
1203 x_return_status := FND_API.g_ret_sts_success;
1204
1205 -- set output structure
1206 x_batch_header_rec := p_batch_header_rec;
1207
1208 -- set actual complete date if it's not passed
1209 IF p_batch_header_rec.actual_cmplt_date IS NULL THEN
1210 x_batch_header_rec.actual_cmplt_date := SYSDATE;
1211 END IF;
1212
1213 IF p_batch_header_rec.batch_type = gme_common_pvt.g_doc_type_fpo THEN
1214 RAISE error_batch_type;
1215 END IF;
1216
1217 IF p_batch_header_rec.batch_status NOT IN
1218 (gme_common_pvt.g_batch_pending, gme_common_pvt.g_batch_wip) THEN
1219 RAISE error_batch_status;
1220 END IF;
1221
1222 IF p_batch_header_rec.parentline_id IS NOT NULL THEN
1223 RAISE error_phantom;
1224 END IF;
1225
1226 -- Bug 11855868 - Check steps only when enforce step dependency is on.
1227 IF p_batch_header_rec.enforce_step_dependency = 1 THEN
1228 OPEN Cur_gme_batch_steps (p_batch_header_rec.batch_id);
1229 FETCH Cur_gme_batch_steps INTO l_is_step;
1230 CLOSE Cur_gme_batch_steps;
1231
1232 -- Bug 11855868 - Changed condition to compare against zero.
1233 -- IF l_is_step > 1 THEN
1234 IF l_is_step > 0 THEN
1235 RAISE error_steps_not_complete;
1236 END IF;
1237 END IF;/*enforce_step_dependency = 1*/
1238
1239 --Sunith ch.5404329 check validity rule if it's not NULL; it would be NULL in case of LCF
1240 IF p_batch_header_rec.recipe_validity_rule_id IS NOT NULL THEN
1241 -- Bug 13004429 - This check should only be done for a pending batch.
1242 IF p_batch_header_rec.batch_status = 1 THEN
1243 OPEN cur_validity_rule(p_batch_header_rec.recipe_validity_rule_id);
1244 FETCH cur_validity_rule INTO l_validity_rule;
1245 CLOSE cur_validity_rule;
1246
1247 IF l_validity_rule.recipe_validity_rule_id IS NULL THEN -- not found
1248 RAISE error_vr_not_found;
1249 ELSE
1250 -- following prevents user from releasing a pending batch
1251 -- if validity rule is ON_HOLD or OBSOLETE.
1252 OPEN cur_validity_status_type(l_validity_rule.validity_rule_status);
1253 FETCH cur_validity_status_type INTO l_status_type;
1254 CLOSE cur_validity_status_type;
1255
1256 IF l_status_type IN ('1000' ,'800') THEN
1257 RAISE error_validity_status;
1258 END IF;
1259 END IF; -- IF l_validity_rule.recipe_validity_rule_id IS NULL
1260
1261 /* IF l_validity_rule.start_date > x_batch_header_rec.actual_start_date OR
1262 (l_validity_rule.end_date IS NOT NULL AND
1263 l_validity_rule.end_date < x_batch_header_rec.actual_start_date) THEN
1264 RAISE error_vr_dates;
1265 END IF;*/
1266 -- sunitha ch. Bug 5336007 aded call to check_validity_rule_dates and passed p_validate_plan_dates_ind = 1
1267 -- to validate planned start date against validate rule dates
1268 IF NOT gme_common_pvt.check_validity_rule_dates (
1269 p_validity_rule_id => p_batch_header_rec.recipe_validity_rule_id
1270 ,p_start_date => p_batch_header_rec.actual_start_date
1271 ,p_cmplt_date => p_batch_header_rec.actual_cmplt_date
1272 ,p_batch_header_rec => p_batch_header_rec
1273 ,p_validate_plan_dates_ind => 1) THEN
1274 x_return_status := fnd_api.g_ret_sts_error;
1275 RAISE error_vr_dates;
1276 END IF;
1277 -- End Bug 5336007
1278 END IF; -- p_batch_header_rec.batch_status = 1
1279 END IF; -- IF p_batch_header_rec.recipe_validity_rule_id IS NOT NULL
1280
1281 -- validate completion date with actual start date and current date
1282 IF x_batch_header_rec.actual_cmplt_date < x_batch_header_rec.actual_start_date THEN
1283 RAISE error_cmplt_date;
1284 ELSIF x_batch_header_rec.actual_cmplt_date > SYSDATE THEN
1285 RAISE error_future_date;
1286 END IF;
1287
1288 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
1289 gme_debug.g_log_procedure THEN
1290 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1291 END IF;
1292
1293 EXCEPTION
1294 WHEN error_phantom THEN
1295 gme_common_pvt.log_message ('PM_INVALID_PHANTOM_ACTION');
1296 x_return_status := FND_API.G_RET_STS_ERROR;
1297 WHEN error_batch_type OR error_batch_status THEN
1298 gme_common_pvt.log_message('GME_API_INVALID_BATCH_COMPL');
1299 x_return_status := fnd_api.g_ret_sts_error;
1300 WHEN error_steps_not_complete THEN
1301 gme_common_pvt.log_message('GME_STEPS_NOT_COMPLETE');
1302 x_return_status := fnd_api.g_ret_sts_error;
1303 WHEN error_cmplt_date THEN
1304 gme_common_pvt.log_message('GME_INVALID_DATE_RANGE'
1305 ,'DATE1','Completion date'
1306 ,'DATE2','Start date');
1307 x_return_status := fnd_api.g_ret_sts_error;
1308 WHEN error_future_date THEN
1309 fnd_message.set_name ('GMA', 'SY_NOFUTUREDATE');
1310 fnd_msg_pub.ADD;
1311 x_return_status := fnd_api.g_ret_sts_error;
1312 WHEN error_vr_dates THEN
1313 x_return_status := FND_API.G_RET_STS_ERROR;
1314 WHEN OTHERS THEN
1315 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1316
1317 IF g_debug <= gme_debug.g_log_procedure THEN
1318 gme_debug.put_line ( 'Unexpected error: '
1319 || g_pkg_name
1320 || '.'
1321 || l_api_name
1322 || ': '
1323 || SQLERRM);
1324 END IF;
1325
1326 x_return_status := fnd_api.g_ret_sts_unexp_error;
1327 END validate_batch_for_complete;
1328
1329 END gme_complete_batch_pvt;
[Home] [Help]