1 PACKAGE BODY gme_complete_batch_pvt AS
2 /* $Header: GMEVCMBB.pls 120.22.12010000.2 2008/08/04 18:30:16 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 l_step_tab(i).actual_start_date := p_batch_header_rec.actual_start_date;
255 l_step_tab(i).actual_cmplt_date := p_batch_header_rec.actual_cmplt_date;
256 gme_complete_batch_step_pvt.complete_step_recursive
257 (p_batch_step_rec => l_step_tab(i)
258 ,p_batch_header_rec => p_batch_header_rec
259 ,x_batch_step_rec => l_batch_step_rec
260 ,x_exception_material_tbl => x_exception_material_tbl
261 ,x_return_status => l_return_status);
262
263 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
264 x_return_status := l_return_status;
265 RAISE error_complete_step_rec;
266 END IF;
267
268 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
269 x_return_status := gme_common_pvt.g_exceptions_err;
270 END IF;
271 END LOOP;
272
273 -- Complete any phantom batches that are not complete or closed
274 OPEN Cur_get_phantom_batch(p_batch_header_rec.batch_id);
275 FETCH Cur_get_phantom_batch BULK COLLECT INTO l_btch_hdr_tab;
276 CLOSE Cur_get_phantom_batch;
277
278 -- Complete any phantom batches...
279 FOR i IN 1..l_btch_hdr_tab.COUNT LOOP
280 l_btch_hdr_tab(i).actual_cmplt_date := x_batch_header_rec.actual_cmplt_date;
281
282 complete_batch
283 (p_batch_header_rec => l_btch_hdr_tab(i)
284 ,x_exception_material_tbl => x_exception_material_tbl
285 ,x_batch_header_rec => l_btch_hdr
286 ,x_return_status => l_return_status);
287
288 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
289 x_return_status := l_return_status;
290 RAISE error_complete_batch;
291 END IF;
292
293 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
294 x_return_status := gme_common_pvt.g_exceptions_err;
295 END IF;
296
297 END LOOP; -- FOR i IN 1..l_btch_hdr_tab.COUNT LOOP
298
299 -- examine all non-phantom ingredients to determine if there's an exception
300 -- phantom products are reported at the time of yield, so don't double report with phantom ingredient
301 -- all other products are reported in either process material (non auto yield) or yield_material (auto yield)
302 FOR i IN 1..l_matl_dtl_tab_ing.COUNT LOOP
303 l_matl_dtl_rec := l_matl_dtl_tab_ing(i);
304 IF l_matl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing THEN
305 -- don't check for unconsumed first because even if it's fully consumed,
306 -- but has reservation or MO, want to report it
307 --Bug#5296812 Added the call to the item record.Added the condition to check the inv update ind,and transaction ind.Start.
308 gme_material_detail_pvt.get_item_rec
309 (p_org_id => l_matl_dtl_rec.organization_id
310 ,p_item_id => l_matl_dtl_rec.inventory_item_id
311 ,x_item_rec => l_item_rec
312 ,x_return_status => l_return_status);
313 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
314 x_return_status := l_return_status;
315 RAISE error_get_item;
316 END IF;
317 IF p_batch_header_rec.update_inventory_ind = 'Y' AND
318 l_item_rec.mtl_transactions_enabled_flag = 'Y' THEN
319 --Bug#5296812 End.
320 l_exception_qty := l_matl_dtl_rec.wip_plan_qty - l_matl_dtl_rec.actual_qty;
321
322 IF l_exception_qty < 0 THEN
323 l_exception_qty := 0;
324 END IF;
325
326 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
327 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);
328 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' actual_qty='||l_matl_dtl_rec.actual_qty);
329 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' wip_plan_qty='||l_matl_dtl_rec.wip_plan_qty);
330 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' exception qty='||l_exception_qty);
331 END IF;
332
333 gme_release_batch_pvt.create_batch_exception
334 (p_material_dtl_rec => l_matl_dtl_rec
335 ,p_pending_move_order_ind => NULL -- don't know...allow to calculate
336 ,p_pending_rsrv_ind => NULL -- don't know...allow to calculate
337 ,p_transacted_qty => 0 -- only auto rel products are transacted.. this is for ing
338 ,p_exception_qty => l_exception_qty
339 ,p_force_unconsumed => fnd_api.g_false
340 ,x_exception_material_tbl => x_exception_material_tbl
341 ,x_return_status => l_return_status);
342
343 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
344 x_return_status := l_return_status;
345 RAISE error_complete_batch;
346 END IF;
347
348 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
349 x_return_status := gme_common_pvt.g_exceptions_err;
350 END IF;
351 END IF; --IF p_batch_header_rec.update_inventory_ind = 'Y'...
352 END IF;
353 END LOOP;
354
355 IF NOT gme_common_pvt.create_history
356 (p_batch_header_rec => p_batch_header_rec
357 ,p_original_status => gme_common_pvt.g_batch_wip
358 ,p_event_id => NVL(gme_common_pvt.g_transaction_header_id,-9999)) THEN
359 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
360 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' create history returned error');
361 END IF;
362 END IF;
363
364 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
365 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
366 END IF;
367
368 EXCEPTION
369 WHEN error_update_batch THEN
370 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
371 x_return_status := FND_API.g_ret_sts_unexp_error;
372 --Bug#5296812 Handling the raised exception error_get_item.
373 WHEN error_process_material OR error_complete_batch OR
374 error_complete_step_rec OR error_release_batch OR error_get_item THEN
375 NULL;
376 WHEN locked_by_other_user or batch_lines_locked THEN
377 gme_common_pvt.log_message (
378 'GME_RECORD_LOCKED',
379 'TABLE_NAME',
380 l_table_name
381 );
382 x_return_status := FND_API.G_RET_STS_ERROR;
383 WHEN OTHERS THEN
384 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
385 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
386 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
387 END IF;
388 x_return_status := FND_API.g_ret_sts_unexp_error;
389 END complete_batch;
390
391
392 PROCEDURE process_material
393 (p_material_detail_rec IN gme_material_details%ROWTYPE
394 ,p_yield IN BOOLEAN
395 ,p_trans_date IN DATE
396 ,p_update_inv_ind IN VARCHAR2
397 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
398 ,x_return_status OUT NOCOPY VARCHAR2) IS
399
400
401 l_api_name CONSTANT VARCHAR2 (30) := 'process_material';
402
403 l_matl_dtl_rec gme_material_details%ROWTYPE;
404 l_in_phantom_batch_rec gme_batch_header%ROWTYPE;
405 l_phantom_batch_rec gme_batch_header%ROWTYPE;
406 l_return_status VARCHAR2(1);
407 l_item_rec mtl_system_items_b%ROWTYPE;
408 l_actual_qty NUMBER;
409 l_start_actual_qty NUMBER;
410 l_exception_qty NUMBER;
411
412 error_fetch_batch EXCEPTION;
413 error_complete_batch EXCEPTION;
414 error_yield_material EXCEPTION;
415 error_update_row EXCEPTION;
416 error_get_item EXCEPTION;
417 error_batch_exception EXCEPTION;
418
419
420
421 BEGIN
422
423 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
424 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
425 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Processing material material_detail_id='||p_material_detail_rec.material_detail_id);
426 END IF;
427
428 /* Set the return status to success initially */
429 x_return_status := FND_API.G_RET_STS_SUCCESS;
430
431 -- Process the material
432 -- 1) complete product
433 -- 2) complete phantom batch for phantom ingredient
434
435 l_matl_dtl_rec := p_material_detail_rec;
436
437 -- if it's a phantom ingredient, then complete the phantom batch
438 -- which will cause the phantom product to be yielded (the passed in ingredient will be consumed in trxm mgr
439
440 -- complete phantom batch
441 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
442 l_phantom_batch_rec.batch_id := l_matl_dtl_rec.phantom_id;
443 IF NOT gme_batch_header_dbl.fetch_row(l_phantom_batch_rec, l_phantom_batch_rec) THEN
444 RAISE error_fetch_batch;
445 END IF;
446
447 IF l_phantom_batch_rec.batch_status IN (gme_common_pvt.g_batch_pending, gme_common_pvt.g_batch_wip) THEN
448 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
449 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' processing phantom ingredient material_detail_id='||l_matl_dtl_rec.material_detail_id);
450 END IF;
451 -- pass in the phantom line id so that release batch will know to yield that product
452 l_in_phantom_batch_rec := l_phantom_batch_rec;
453
454 l_in_phantom_batch_rec.actual_start_date := NVL(l_in_phantom_batch_rec.actual_start_date, p_trans_date);
455 l_in_phantom_batch_rec.actual_cmplt_date := p_trans_date;
456
457 complete_batch
458 (p_batch_header_rec => l_in_phantom_batch_rec
459 ,x_exception_material_tbl => x_exception_material_tbl
460 ,x_batch_header_rec => l_phantom_batch_rec
461 ,x_return_status => l_return_status);
462
463 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
464 x_return_status := l_return_status;
465 RAISE error_complete_batch;
466 END IF;
467
468 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
469 x_return_status := gme_common_pvt.g_exceptions_err;
470 END IF;
471 END IF; -- IF l_phantom_batch_rec.batch_status IN (gme_common_pvt.g_batch_pending, gme_common_pvt.g_batch_wip)
472 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
473 gme_material_detail_pvt.get_item_rec
474 (p_org_id => l_matl_dtl_rec.organization_id
475 ,p_item_id => l_matl_dtl_rec.inventory_item_id
476 ,x_item_rec => l_item_rec
477 ,x_return_status => l_return_status);
478
479 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
480 x_return_status := l_return_status;
481 RAISE error_get_item;
482 END IF;
483
484 IF p_update_inv_ind = 'Y' AND
485 l_item_rec.mtl_transactions_enabled_flag = 'Y' THEN
486 l_start_actual_qty := l_matl_dtl_rec.actual_qty;
487 yield_material(p_material_dtl_rec => l_matl_dtl_rec
488 ,p_yield_qty => NULL -- take the entire wip plan qty
489 ,p_trans_date => p_trans_date
490 ,p_item_rec => l_item_rec
491 ,p_force_unconsumed => fnd_api.g_false
492 ,x_exception_material_tbl => x_exception_material_tbl
493 ,x_actual_qty => l_actual_qty
494 ,x_return_status => l_return_status);
495
496 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
497 x_return_status := l_return_status;
498 RAISE error_yield_material;
499 END IF;
500
501 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
502 x_return_status := gme_common_pvt.g_exceptions_err;
503 END IF;
504
505 l_matl_dtl_rec.actual_qty := l_actual_qty;
506
507 -- if actual qty is met, we still need to see if there are pending product lots...
508 -- this is not required in yield material... it's a requirement of complete batch
509 IF l_matl_dtl_rec.actual_qty >= l_matl_dtl_rec.wip_plan_qty THEN
510 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
511 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);
512 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' actual_qty='||l_matl_dtl_rec.actual_qty);
513 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' wip_plan_qty='||l_matl_dtl_rec.wip_plan_qty);
514 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));
515 END IF;
516
517 gme_release_batch_pvt.create_batch_exception
518 (p_material_dtl_rec => l_matl_dtl_rec
519 ,p_pending_move_order_ind => FALSE -- product doesn't have MO
520 ,p_pending_rsrv_ind => NULL -- let proc figure out; for product, looks at pplot
521 ,p_transacted_qty => l_actual_qty - l_start_actual_qty
522 ,p_exception_qty => 0
523 ,p_force_unconsumed => fnd_api.g_false
524 ,x_exception_material_tbl => x_exception_material_tbl
525 ,x_return_status => l_return_status);
526
527 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
528 x_return_status := l_return_status;
529 RAISE error_yield_material;
530 END IF;
531
532 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
533 x_return_status := gme_common_pvt.g_exceptions_err;
534 END IF;
535 END IF;
536
537 ELSE
538 l_matl_dtl_rec.actual_qty := l_matl_dtl_rec.plan_qty;
539 END IF;
540 ELSE -- check for exception... this should be for all products that are not auto release
541 --Bug#5296812 Added the call to the item record.Added the condition to check the inv update ind,and transaction ind.
542 gme_material_detail_pvt.get_item_rec
543 (p_org_id => l_matl_dtl_rec.organization_id
544 ,p_item_id => l_matl_dtl_rec.inventory_item_id
545 ,x_item_rec => l_item_rec
546 ,x_return_status => l_return_status);
547 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
548 x_return_status := l_return_status;
549 RAISE error_get_item;
550 END IF;
551 IF p_update_inv_ind = 'Y' AND l_item_rec.mtl_transactions_enabled_flag = 'Y' THEN
552 --Bug#5296812 End.
553 l_exception_qty := l_matl_dtl_rec.wip_plan_qty - l_matl_dtl_rec.actual_qty;
554
555 IF l_exception_qty < 0 THEN
556 l_exception_qty := 0;
557 END IF;
558
559 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
560 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);
561 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' actual_qty='||l_matl_dtl_rec.actual_qty);
562 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' wip_plan_qty='||l_matl_dtl_rec.wip_plan_qty);
563 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' exception qty='||l_exception_qty);
564 END IF;
565
566 gme_release_batch_pvt.create_batch_exception
567 (p_material_dtl_rec => l_matl_dtl_rec
568 ,p_pending_move_order_ind => FALSE -- product doesn't have MO
569 ,p_pending_rsrv_ind => NULL -- let proc figure out; for product, looks at pplot
570 ,p_transacted_qty => 0 -- products other than auto yield don't get transacted in complete
571 ,p_exception_qty => l_exception_qty
572 ,p_force_unconsumed => fnd_api.g_false
573 ,x_exception_material_tbl => x_exception_material_tbl
574 ,x_return_status => l_return_status);
575
576 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
577 x_return_status := l_return_status;
578 RAISE error_batch_exception;
579 END IF;
580
581 IF l_return_status = gme_common_pvt.g_exceptions_err THEN
582 x_return_status := gme_common_pvt.g_exceptions_err;
583 END IF;
584 END IF;--IF p_update_inv_ind = 'Y' AND...
585 END IF; -- IF l_matl_dtl_rec.phantom_id IS NOT NULL...
586
587 -- set WIP plan qty
588 l_matl_dtl_rec.wip_plan_qty := l_matl_dtl_rec.plan_qty;
589
590 IF NOT gme_material_details_dbl.update_row (l_matl_dtl_rec) THEN
591 RAISE error_update_row;
592 END IF;
593
594 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
595 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
596 END IF;
597
598 EXCEPTION
599 WHEN error_fetch_batch OR error_update_row THEN
600 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
601 x_return_status := FND_API.g_ret_sts_unexp_error;
602 WHEN error_complete_batch OR error_yield_material OR error_get_item OR error_batch_exception THEN
603 NULL;
604 WHEN OTHERS THEN
605 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
606 IF g_debug <= gme_debug.g_log_procedure THEN
607 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
608 END IF;
609 x_return_status := FND_API.g_ret_sts_unexp_error;
610 END process_material;
611
612
613 -- Note: p_yield_qty is the target actual qty; for incr, it's also the target, not the incr
614 PROCEDURE yield_material(p_material_dtl_rec IN gme_material_details%ROWTYPE
615 ,p_yield_qty IN NUMBER
616 ,p_trans_date IN DATE
617 ,p_item_rec IN mtl_system_items_b%ROWTYPE
618 ,p_force_unconsumed IN VARCHAR2
619 ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
620 ,x_actual_qty OUT NOCOPY NUMBER
621 ,x_return_status OUT NOCOPY VARCHAR2) IS
622
623 l_api_name CONSTANT VARCHAR2 (30) := 'YIELD_MATERIAL';
624
625 l_pending_product_lot_tab gme_common_pvt.pending_lots_tab;
626 l_pp_lot_rec gme_pending_product_lots%ROWTYPE;
627 i NUMBER;
628
629 l_yield_qty NUMBER;
630 l_trans_date DATE;
631 l_subinv VARCHAR2(10);
632 l_locator_id NUMBER;
633 l_revision VARCHAR2(3);
634 l_eff_locator_control NUMBER;
635 l_start_actual_qty NUMBER;
636 l_return_status VARCHAR2(1);
637
638 CURSOR cur_get_item_revision(v_item_id NUMBER, v_org_id NUMBER) IS
639 SELECT revision
640 FROM mtl_item_revisions_b
641 WHERE inventory_item_id = v_item_id
642 AND organization_id = v_org_id
643 AND effectivity_date <= gme_common_pvt.g_timestamp
644 ORDER BY effectivity_date desc;
645
646 error_build_trxn EXCEPTION;
647 error_get_exception EXCEPTION;
648 error_nothing_to_yield EXCEPTION;
649 error_get_pplot EXCEPTION;
650 no_yield_required EXCEPTION;
651
652 BEGIN
653
654 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
655 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
656 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' material_detail_id='||p_material_dtl_rec.material_detail_id);
657 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_yield_qty='||p_yield_qty);
658 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_trans_date='||to_char(p_trans_date,
659 'YYYY-MON-DD HH24:MI:SS'));
660 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' line_no='||p_material_dtl_rec.line_no);
661 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' line_type='||p_material_dtl_rec.line_type);
662 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_force_unconsumed='||p_force_unconsumed);
663 END IF;
664
665 /* Set the return status to success initially */
666 x_return_status := FND_API.G_RET_STS_SUCCESS;
667
668 x_actual_qty := p_material_dtl_rec.actual_qty;
669
670 -- following global is set only for migration purposes, where transactions need not be created,
671 -- this will only be set for complete step; complete batch is not called from migrate; however
672 -- if this is needed for complete batch, logic will work there also
673 IF gme_release_batch_pvt.g_bypass_txn_creation = 1 THEN
674 RAISE no_yield_required;
675 END IF;
676 /*Pawan Kumar 08-25-2006 bug 5486066 added nvl for wip_plan_qty
677 during direct completion, the wip plan qty is also null
678 So this was not getting caught in l_yield_qty <= x_actual_qty and this was
679 sending transaction quantity as null to transactions where it was failing*/
680
681 l_yield_qty := NVL(p_yield_qty, nvl(p_material_dtl_rec.wip_plan_qty,p_material_dtl_rec.plan_qty));
682 l_trans_date := NVL(p_trans_date, gme_common_pvt.g_timestamp);
683
684 l_start_actual_qty := x_actual_qty;
685
686 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
687 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_yield_qty='||l_yield_qty);
688 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_trans_date='||to_char(l_trans_date, 'YYYY-MON-DD HH24:MI:SS'));
689 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
690 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_start_actual_qty='||l_start_actual_qty);
691 END IF;
692
693 IF l_yield_qty <= x_actual_qty THEN
694 -- this returns as success for now; there's nothing additional to yield
695 RAISE error_nothing_to_yield;
696 END IF;
697
698 IF p_material_dtl_rec.subinventory IS NULL THEN
699 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
700 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' subinv on material is NULL; cant yield anything; get exceptions');
701 END IF;
702 RAISE error_get_exception;
703 END IF;
704
705 IF p_material_dtl_rec.locator_id IS NULL THEN
706 -- check if it's locator control, we need a locator...
707 /* Bug 5441643 Added NVL condition for location control code*/
708 l_eff_locator_control :=
709 gme_common_pvt.eff_locator_control
710 (p_organization_id => p_material_dtl_rec.organization_id
711 ,p_org_control => gme_common_pvt.g_org_locator_control
712 ,p_subinventory => p_material_dtl_rec.subinventory
713 ,p_item_control => NVL(p_item_rec.location_control_code,1)
714 ,p_item_loc_restrict => p_item_rec.restrict_locators_code
715 ,p_action => gme_common_pvt.g_prod_comp_txn_action);
716 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
717 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_eff_locator_control='||l_eff_locator_control);
718 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' organization_id='||p_material_dtl_rec.organization_id);
719 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' g_org_locator_control='||gme_common_pvt.g_org_locator_control);
720 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' subinventory='||p_material_dtl_rec.subinventory);
721 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' inventory_item_id='||p_item_rec.inventory_item_id);
722 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' location_control_code='||p_item_rec.location_control_code);
723 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' restrict_locators_code='||p_item_rec.restrict_locators_code);
724 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_action='||gme_common_pvt.g_prod_comp_txn_action);
725 END IF;
726 IF l_eff_locator_control <> 1 THEN
727 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
728 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');
729 END IF;
730 RAISE error_get_exception;
731 END IF;
732 END IF;
733
734 l_subinv := p_material_dtl_rec.subinventory;
735 l_locator_id := p_material_dtl_rec.locator_id;
736
737 IF p_item_rec.revision_qty_control_code = 2 THEN -- under revision control
738 IF p_material_dtl_rec.revision IS NOT NULL THEN
739 l_revision := p_material_dtl_rec.revision;
740 ELSE
741 OPEN cur_get_item_revision(p_material_dtl_rec.inventory_item_id,
742 p_material_dtl_rec.organization_id);
743 FETCH cur_get_item_revision INTO l_revision;
744 CLOSE cur_get_item_revision;
745 END IF;
746 END IF; -- IF p_revision_qty_control_code = 2
747
748 IF p_item_rec.lot_control_code = 1 THEN -- not lot control
749 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
750 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' item not lot control');
751 END IF;
752
753 build_and_create_transaction
754 (p_mtl_dtl_rec => p_material_dtl_rec
755 ,p_pp_lot_rec => NULL
756 ,p_subinv => l_subinv
757 ,p_locator_id => l_locator_id
758 ,p_trans_date => l_trans_date
759 ,p_yield_qty => l_yield_qty
760 ,p_revision => l_revision
761 ,p_sec_uom_code => p_item_rec.secondary_uom_code
762 ,x_actual_qty => x_actual_qty
763 ,x_return_status => l_return_status);
764
765 IF l_return_status NOT IN (gme_common_pvt.g_not_transactable, FND_API.G_RET_STS_SUCCESS) THEN
766 x_return_status := l_return_status;
767 RAISE error_build_trxn;
768 END IF;
769 ELSE -- lot control... go to pending product lots
770 gme_pending_product_lots_pvt.get_pending_lot
771 (p_material_detail_id => p_material_dtl_rec.material_detail_id
772 ,x_return_status => l_return_status
773 ,x_pending_product_lot_tbl => l_pending_product_lot_tab);
774 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
775 x_return_status := l_return_status;
776 RAISE error_get_pplot;
777 END IF;
778
779 i := 1;
780
781 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
782 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_yield_qty='||l_yield_qty);
783 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
784 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' count from pplot='||l_pending_product_lot_tab.COUNT);
785 END IF;
786
787 WHILE l_yield_qty > x_actual_qty AND i <= l_pending_product_lot_tab.COUNT LOOP
788
789 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
790 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' pending lot loop i='||i);
791 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' pending lot loop x_actual_qty='||x_actual_qty);
792 END IF;
793
794 l_pp_lot_rec := l_pending_product_lot_tab(i);
795
796 build_and_create_transaction
797 (p_mtl_dtl_rec => p_material_dtl_rec
798 ,p_pp_lot_rec => l_pp_lot_rec
799 ,p_subinv => l_subinv
800 ,p_locator_id => l_locator_id
801 ,p_trans_date => l_trans_date
802 ,p_yield_qty => l_yield_qty
803 ,p_revision => l_revision
804 ,p_sec_uom_code => p_item_rec.secondary_uom_code
805 ,x_actual_qty => x_actual_qty
806 ,x_return_status => l_return_status);
807 IF l_return_status NOT IN (gme_common_pvt.g_not_transactable, FND_API.G_RET_STS_SUCCESS) THEN
808 x_return_status := l_return_status;
809 RAISE error_build_trxn;
810 END IF;
811
812 i := i + 1; -- move on to the next lot
813 END LOOP;
814 END IF; -- IF p_item_rec.lot_control_code = 1 THEN
815
816 IF x_actual_qty < l_yield_qty THEN
817 RAISE error_get_exception;
818 END IF;
819
820 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
821 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
822 END IF;
823
824
825 EXCEPTION
826 WHEN error_build_trxn OR error_get_pplot OR no_yield_required THEN
827 NULL;
828 WHEN error_nothing_to_yield THEN
829 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
830 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);
831 END IF;
832
833 WHEN error_get_exception THEN
834 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
835 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' exception block for get exceptions:');
836 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
837 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_start_actual_qty='||l_start_actual_qty);
838 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_yield_qty='||l_yield_qty);
839 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' exception qty='||(l_yield_qty - x_actual_qty));
840 END IF;
841
842 gme_release_batch_pvt.create_batch_exception
843 (p_material_dtl_rec => p_material_dtl_rec
844 ,p_pending_move_order_ind => FALSE
845 ,p_pending_rsrv_ind => NULL -- let proc figure out; for product, looks at pplot
846 ,p_transacted_qty => x_actual_qty - l_start_actual_qty
847 ,p_exception_qty => l_yield_qty - x_actual_qty
848 ,p_force_unconsumed => p_force_unconsumed
849 ,x_exception_material_tbl => x_exception_material_tbl
850 ,x_return_status => x_return_status);
851
852 WHEN OTHERS THEN
853 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
854 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
855 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
856 END IF;
857 x_return_status := FND_API.g_ret_sts_unexp_error;
858 END yield_material;
859
860 PROCEDURE build_and_create_transaction
861 (p_mtl_dtl_rec IN gme_material_details%ROWTYPE
862 ,p_pp_lot_rec IN gme_pending_product_lots%ROWTYPE
863 ,p_subinv IN VARCHAR2
864 ,p_locator_id IN NUMBER
865 ,p_trans_date IN DATE
866 ,p_yield_qty IN NUMBER
867 ,p_revision IN VARCHAR2 DEFAULT NULL
868 ,p_sec_uom_code IN VARCHAR2 DEFAULT NULL
869 ,x_actual_qty IN OUT NOCOPY NUMBER
870 ,x_return_status OUT NOCOPY VARCHAR2) IS
871
872 l_api_name CONSTANT VARCHAR2 (30) := 'build_and_create_transaction';
873
874 l_transaction_rec mtl_transactions_interface%ROWTYPE;
875 l_lot_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
876 l_trxn_qty NUMBER;
877 l_dtl_qty NUMBER;
878 l_sec_qty NUMBER;
879
880 error_build_mmti EXCEPTION;
881 error_create_trxn EXCEPTION;
882 error_relieve_pp_lot EXCEPTION;
883
884 BEGIN
885 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
886 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
887 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' material_detail_id='||p_mtl_dtl_rec.material_detail_id);
888 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_subinv='||p_subinv);
889 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_locator_id='||p_locator_id);
890 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_yield_qty='||p_yield_qty);
891 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_revision='||p_revision);
892 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_sec_uom_code='||p_sec_uom_code);
893 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
894 END IF;
895
896 /* Set the return status to success initially */
897 x_return_status := FND_API.G_RET_STS_SUCCESS;
898
899 constr_mmti(p_mtl_dtl_rec => p_mtl_dtl_rec
900 ,p_yield_qty => p_yield_qty
901 ,p_subinv => p_subinv
902 ,p_revision => p_revision
903 ,p_locator_id => p_locator_id
904 ,p_pp_lot_rec => p_pp_lot_rec
905 ,x_mmti_rec => l_transaction_rec
906 ,x_mmli_tbl => l_lot_tbl
907 ,x_sec_qty => l_sec_qty
908 ,x_dtl_qty => l_dtl_qty
909 ,x_return_status => x_return_status);
910
911 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
912 RAISE error_build_mmti;
913 END IF;
914
915 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
916 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after constr_mmti l_dtl_qty := '||l_dtl_qty);
917 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after constr_mmti l_sec_qty := '||l_sec_qty);
918 END IF;
919
920 -- Bug 7262112 - Added '=' to condition. No need to recompute secondary values if yielding the full amount.
921 IF l_dtl_qty <= p_yield_qty - x_actual_qty THEN
922 l_trxn_qty := l_dtl_qty;
923 ELSE
924 l_trxn_qty := p_yield_qty - x_actual_qty;
925 /* Bug 5256543 l_sec_qty was set to null that was incorrect it has to be recalculated from new l_trxn_qty */
926 IF (p_sec_uom_code IS NOT NULL) THEN
927 l_sec_qty := INV_CONVERT.inv_um_convert
928 (item_id => p_mtl_dtl_rec.inventory_item_id
929 ,lot_number => p_pp_lot_rec.lot_number
930 ,organization_id => p_mtl_dtl_rec.organization_id
931 ,PRECISION => gme_common_pvt.g_precision
932 ,from_quantity => l_trxn_qty
933 ,from_unit => p_mtl_dtl_rec.dtl_um
934 ,to_unit => p_sec_uom_code
935 ,from_name => NULL
936 ,to_name => NULL);
937 END IF;
938 END IF;
939
940 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
941 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' calculated l_trxn_qty := '||l_trxn_qty);
942 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' calculated l_sec_qty := '||l_sec_qty);
943 END IF;
944
945 IF p_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_prod THEN
946 l_transaction_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
947 ELSE
948 l_transaction_rec.transaction_type_id := gme_common_pvt.g_byprod_completion;
949 END IF;
950
951 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
952 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_transaction_rec.transaction_type_id := '||l_transaction_rec.transaction_type_id);
953 END IF;
954
955 l_transaction_rec.transaction_date := p_trans_date;
956 l_transaction_rec.transaction_quantity := l_trxn_qty;
957 l_transaction_rec.secondary_uom_code := p_sec_uom_code;
958
959 IF l_sec_qty IS NOT NULL THEN
960 l_transaction_rec.secondary_transaction_quantity := l_sec_qty;
961 END IF;
962
963 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
964 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' transaction_date='||to_char(l_transaction_rec.transaction_date
965 ,'YYYY-MON-DD HH24:MI:SS'));
966 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' transaction_qty='||l_transaction_rec.transaction_quantity);
967 END IF;
968
969 l_transaction_rec.transaction_uom := p_mtl_dtl_rec.dtl_um;
970
971 IF l_lot_tbl.count > 0 THEN
972 IF l_lot_tbl(1).lot_number IS NOT NULL THEN
973 l_lot_tbl(1).transaction_quantity := l_transaction_rec.transaction_quantity;
974 IF l_sec_qty IS NOT NULL THEN
975 l_lot_tbl(1).secondary_transaction_quantity := l_sec_qty;
976 END IF;
977 END IF;
978 END IF;
979
980 gme_transactions_pvt.create_material_txn
981 (p_mmti_rec => l_transaction_rec
982 ,p_mmli_tbl => l_lot_tbl
983 ,x_return_status => x_return_status);
984
985 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
986 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
987 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' gme_transactions_pvt.create_transaction returned error');
988 END IF;
989 RAISE error_create_trxn;
990 END IF;
991
992 x_actual_qty := x_actual_qty + l_trxn_qty;
993
994 -- If pending product lot, then decrease qty, entry remains if the qty goes to zero
995 IF p_pp_lot_rec.pending_product_lot_id IS NOT NULL THEN
996 gme_pending_product_lots_pvt.relieve_pending_lot
997 (p_pending_lot_id => p_pp_lot_rec.pending_product_lot_id
998 ,p_quantity => l_trxn_qty
999 ,p_secondary_quantity => l_sec_qty
1000 ,x_return_status => x_return_status);
1001
1002 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1003 RAISE error_relieve_pp_lot;
1004 END IF;
1005 END IF;
1006
1007 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1008 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1009 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' x_actual_qty='||x_actual_qty);
1010 END IF;
1011
1012 EXCEPTION
1013 WHEN error_create_trxn OR error_relieve_pp_lot OR error_build_mmti THEN
1014 NULL;
1015 WHEN OTHERS THEN
1016 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1017 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
1018 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
1019 END IF;
1020 x_return_status := FND_API.g_ret_sts_unexp_error;
1021 END build_and_create_transaction;
1022
1023 PROCEDURE constr_mmti
1024 (p_mtl_dtl_rec IN gme_material_details%ROWTYPE
1025 ,p_yield_qty IN NUMBER
1026 ,p_subinv IN VARCHAR2
1027 ,p_locator_id IN NUMBER
1028 ,p_revision IN VARCHAR2
1029 ,p_pp_lot_rec IN gme_pending_product_lots%ROWTYPE
1030 ,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
1031 ,x_mmli_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_inter_tbl
1032 ,x_sec_qty OUT NOCOPY NUMBER
1033 ,x_dtl_qty OUT NOCOPY NUMBER
1034 ,x_return_status OUT NOCOPY VARCHAR2) IS
1035
1036 l_api_name CONSTANT VARCHAR2 (30) := 'CONSTR_MMTI';
1037 BEGIN
1038
1039 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1040 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1041 END IF;
1042
1043 /* Initially let us assign the return status to success */
1044 x_return_status := FND_API.g_ret_sts_success;
1045
1046 -- consturct mtl_transactions_interface
1047 x_mmti_rec.transaction_source_id := p_mtl_dtl_rec.batch_id; -- batch_id
1048 x_mmti_rec.trx_source_line_id := p_mtl_dtl_rec.material_detail_id; -- material_detail_id
1049 x_mmti_rec.inventory_item_id := p_mtl_dtl_rec.inventory_item_id;
1050 x_mmti_rec.organization_id := p_mtl_dtl_rec.organization_id;
1051 x_mmti_rec.subinventory_code := p_subinv;
1052 x_mmti_rec.locator_id := p_locator_id;
1053 x_mmti_rec.revision := p_revision;
1054 x_mmti_rec.transaction_sequence_id := p_pp_lot_rec.pending_product_lot_id;
1055 x_dtl_qty := p_yield_qty;
1056 -- construct mtl_transaction_lots_interface
1057 IF p_pp_lot_rec.lot_number IS NOT NULL THEN
1058 x_mmli_tbl(1).lot_number := p_pp_lot_rec.lot_number;
1059 x_mmli_tbl(1).reason_id := p_pp_lot_rec.reason_id;
1060 /* Bug 5256543 Assign revision only if not null otherwise it will come from mtl dtl line */
1061 IF (p_pp_lot_rec.revision IS NOT NULL) THEN
1062 x_mmti_rec.revision := p_pp_lot_rec.revision;
1063 END IF;
1064 x_mmti_rec.reason_id := p_pp_lot_rec.reason_id;
1065 x_dtl_qty := p_pp_lot_rec.quantity;
1066 x_sec_qty := p_pp_lot_rec.secondary_quantity;
1067 END IF;
1068
1069 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1070 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1071 END IF;
1072
1073 EXCEPTION
1074 WHEN OTHERS THEN
1075 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1076 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
1077 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
1078 END IF;
1079 x_return_status := FND_API.g_ret_sts_unexp_error;
1080 END constr_mmti;
1081
1082 PROCEDURE validate_batch_for_complete (p_batch_header_rec IN gme_batch_header%ROWTYPE
1083 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
1084 ,x_return_status OUT NOCOPY VARCHAR2) IS
1085
1086 l_api_name CONSTANT VARCHAR2 (30) := 'validate_batch_for_complete';
1087
1088 CURSOR Cur_gme_batch_steps (v_batch_id NUMBER) IS
1089 SELECT count(1)
1090 FROM gme_batch_steps
1091 WHERE step_status NOT IN (gme_common_pvt.g_step_completed, gme_common_pvt.g_step_closed)
1092 AND batch_id = v_batch_id
1093 AND rownum = 1;
1094
1095 l_is_step NUMBER;
1096 l_batch_header_rec gme_batch_header%ROWTYPE;
1097 CURSOR cur_validity_rule(v_recipe_validity_rule_id NUMBER)
1098 IS
1099 SELECT *
1100 FROM gmd_recipe_validity_rules
1101 WHERE recipe_validity_rule_id = v_recipe_validity_rule_id;
1102
1103 CURSOR cur_validity_status_type(v_validity_rule_status VARCHAR2)
1104 IS
1105 SELECT status_type
1106 FROM gmd_status
1107 WHERE status_code=v_validity_rule_status;
1108
1109 l_validity_rule gmd_recipe_validity_rules%ROWTYPE;
1110 l_status_type GMD_STATUS.status_type%TYPE;
1111 error_vr_not_found EXCEPTION;
1112 error_validity_status EXCEPTION;
1113 error_batch_type EXCEPTION;
1114 error_batch_status EXCEPTION;
1115 error_phantom EXCEPTION;
1116 error_steps_not_complete EXCEPTION;
1117 error_cmplt_date EXCEPTION;
1118 error_future_date EXCEPTION;
1119 error_vr_dates EXCEPTION;
1120 BEGIN
1121 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
1122 gme_debug.g_log_procedure THEN
1123 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1124 || l_api_name);
1125 END IF;
1126
1127 x_return_status := FND_API.g_ret_sts_success;
1128
1129 -- set output structure
1130 x_batch_header_rec := p_batch_header_rec;
1131
1132 -- set actual complete date if it's not passed
1133 IF p_batch_header_rec.actual_cmplt_date IS NULL THEN
1134 x_batch_header_rec.actual_cmplt_date := SYSDATE;
1135 END IF;
1136
1137 IF p_batch_header_rec.batch_type = gme_common_pvt.g_doc_type_fpo THEN
1138 RAISE error_batch_type;
1139 END IF;
1140
1141 IF p_batch_header_rec.batch_status NOT IN
1142 (gme_common_pvt.g_batch_pending, gme_common_pvt.g_batch_wip) THEN
1143 RAISE error_batch_status;
1144 END IF;
1145
1146 IF p_batch_header_rec.parentline_id IS NOT NULL THEN
1147 RAISE error_phantom;
1148 END IF;
1149
1150 OPEN Cur_gme_batch_steps (p_batch_header_rec.batch_id);
1151 FETCH Cur_gme_batch_steps INTO l_is_step;
1152 CLOSE Cur_gme_batch_steps;
1153
1154 IF l_is_step > 1 THEN
1155 RAISE error_steps_not_complete;
1156 END IF;
1157 --Sunith ch.5404329 check validity rule if it's not NULL; it would be NULL in case of LCF
1158 IF p_batch_header_rec.recipe_validity_rule_id IS NOT NULL THEN
1159 OPEN cur_validity_rule(p_batch_header_rec.recipe_validity_rule_id);
1160 FETCH cur_validity_rule INTO l_validity_rule;
1161 CLOSE cur_validity_rule;
1162
1163 IF l_validity_rule.recipe_validity_rule_id IS NULL THEN -- not found
1164 RAISE error_vr_not_found;
1165 ELSE
1166 -- following prevents user from releasing a pending batch
1167 -- if validity rule is ON_HOLD or OBSOLETE.
1168 OPEN cur_validity_status_type(l_validity_rule.validity_rule_status);
1169 FETCH cur_validity_status_type INTO l_status_type;
1170 CLOSE cur_validity_status_type;
1171
1172 IF l_status_type IN ('1000' ,'800') THEN
1173 RAISE error_validity_status;
1174 END IF;
1175 END IF; -- IF l_validity_rule.recipe_validity_rule_id IS NULL
1176
1177 /* IF l_validity_rule.start_date > x_batch_header_rec.actual_start_date OR
1178 (l_validity_rule.end_date IS NOT NULL AND
1179 l_validity_rule.end_date < x_batch_header_rec.actual_start_date) THEN
1180 RAISE error_vr_dates;
1181 END IF;*/
1182 --sunitha ch. Bug 5336007 aded call to check_validity_rule_dates and passed p_validate_plan_dates_ind=1
1183 --to validate planned start date against validate rule dates
1184 IF NOT gme_common_pvt.check_validity_rule_dates (
1185 p_validity_rule_id => p_batch_header_rec.recipe_validity_rule_id
1186 ,p_start_date => p_batch_header_rec.actual_start_date
1187 ,p_cmplt_date => p_batch_header_rec.actual_cmplt_date
1188 ,p_batch_header_rec => p_batch_header_rec
1189 ,p_validate_plan_dates_ind => 1) THEN
1190 x_return_status := fnd_api.g_ret_sts_error;
1191 RAISE error_vr_dates;
1192 END IF;
1193 -- End Bug 5336007
1194 END IF; -- IF p_batch_header_rec.recipe_validity_rule_id IS NOT NULL
1195
1196 -- validate completion date with actual start date and current date
1197 IF x_batch_header_rec.actual_cmplt_date < x_batch_header_rec.actual_start_date THEN
1198 RAISE error_cmplt_date;
1199 ELSIF x_batch_header_rec.actual_cmplt_date > SYSDATE THEN
1200 RAISE error_future_date;
1201 END IF;
1202
1203 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
1204 gme_debug.g_log_procedure THEN
1205 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1206 END IF;
1207
1208 EXCEPTION
1209 WHEN error_phantom THEN
1210 gme_common_pvt.log_message ('PM_INVALID_PHANTOM_ACTION');
1211 x_return_status := FND_API.G_RET_STS_ERROR;
1212 WHEN error_batch_type OR error_batch_status THEN
1213 gme_common_pvt.log_message('GME_API_INVALID_BATCH_COMPL');
1214 x_return_status := fnd_api.g_ret_sts_error;
1215 WHEN error_steps_not_complete THEN
1216 gme_common_pvt.log_message('GME_STEPS_NOT_COMPLETE');
1217 x_return_status := fnd_api.g_ret_sts_error;
1218 WHEN error_cmplt_date THEN
1219 gme_common_pvt.log_message('GME_INVALID_DATE_RANGE'
1220 ,'DATE1','Completion date'
1221 ,'DATE2','Start date');
1222 x_return_status := fnd_api.g_ret_sts_error;
1223 WHEN error_future_date THEN
1224 fnd_message.set_name ('GMA', 'SY_NOFUTUREDATE');
1225 fnd_msg_pub.ADD;
1226 x_return_status := fnd_api.g_ret_sts_error;
1227 WHEN error_vr_dates THEN
1228 x_return_status := FND_API.G_RET_STS_ERROR;
1229 WHEN OTHERS THEN
1230 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1231
1232 IF g_debug <= gme_debug.g_log_procedure THEN
1233 gme_debug.put_line ( 'Unexpected error: '
1234 || g_pkg_name
1235 || '.'
1236 || l_api_name
1237 || ': '
1238 || SQLERRM);
1239 END IF;
1240
1241 x_return_status := fnd_api.g_ret_sts_unexp_error;
1242 END validate_batch_for_complete;
1243
1244 END gme_complete_batch_pvt;
[Home] [Help]