[Home] [Help]
PACKAGE BODY: APPS.GME_PENDING_PRODUCT_LOTS_PVT
Source
1 PACKAGE BODY gme_pending_product_lots_pvt AS
2 /* $Header: GMEVPPLB.pls 120.15.12010000.1 2008/07/25 10:31:19 appldev ship $ */
3
4 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
5 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_PENDING_PRODUCT_LOTS_PVT';
6
7 /*************************************************************************************************/
8 /* Oracle Process Manufacturing Process Execution APIs */
9 /* */
10 /* File Name: GMEVPPLB.pls */
11 /* Contents: GME pending lot related procedures. */
12 /* HISTORY: */
13 /* SivakumarG Bug#5186388 03-MAY-2006 */
14 /* Procedure relieve_pending_lots modified to delete the pending lots if transacting qty >= */
15 /* pending lot qty */
16 /* Namit Singhi Bug#5689035. Added procedure get_pnd_prod_lot_qty */
17 /*************************************************************************************************/
18
19 PROCEDURE get_pending_lot
20 (p_material_detail_id IN NUMBER
21 ,x_return_status OUT NOCOPY VARCHAR2
22 ,x_pending_product_lot_tbl OUT NOCOPY gme_common_pvt.pending_lots_tab) IS
23
24 CURSOR cur_get_lots (v_mtl_dtl_id NUMBER) IS
25 SELECT *
26 FROM gme_pending_product_lots
27 WHERE material_detail_id = v_mtl_dtl_id
28 ORDER BY sequence asc, lot_number asc;
29
30 l_api_name CONSTANT VARCHAR2 (30) := 'GET_PENDING_LOT';
31 BEGIN
32 IF g_debug <= gme_debug.g_log_procedure THEN
33 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
34 END IF;
35 x_return_status := FND_API.G_RET_STS_SUCCESS;
36
37 OPEN cur_get_lots(p_material_detail_id);
38 FETCH cur_get_lots BULK COLLECT INTO x_pending_product_lot_tbl;
39 CLOSE cur_get_lots;
40
41 IF g_debug <= gme_debug.g_log_procedure THEN
42 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
43 END IF;
44 EXCEPTION
45 WHEN OTHERS THEN
46 IF g_debug <= gme_debug.g_log_unexpected THEN
47 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
48 END IF;
49 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
50 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
51 END get_pending_lot;
52
53 PROCEDURE relieve_pending_lot
54 (p_pending_lot_id IN NUMBER
55 ,p_quantity IN NUMBER
56 ,p_secondary_quantity IN NUMBER := NULL
57 ,x_return_status OUT NOCOPY VARCHAR2) IS
58
59 CURSOR cur_get_pending_lot_qty (v_pending_lot_id NUMBER) IS
60 SELECT quantity, secondary_quantity
61 FROM gme_pending_product_lots
62 WHERE pending_product_lot_id = v_pending_lot_id;
63
64 l_qty NUMBER;
65 l_sec_qty NUMBER;
66 l_api_name CONSTANT VARCHAR2 (30) := 'RELIEVE_PENDING_LOT';
67 --Bug#5186388
68 l_pending_product_lots_rec gme_pending_product_lots%ROWTYPE;
69 l_return_status VARCHAR2(1);
70 error_delete_row EXCEPTION;
71 BEGIN
72 IF g_debug <= gme_debug.g_log_procedure THEN
73 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
74 END IF;
75 x_return_status := FND_API.G_RET_STS_SUCCESS;
76
77 OPEN cur_get_pending_lot_qty(p_pending_lot_id);
78 FETCH cur_get_pending_lot_qty INTO l_qty, l_sec_qty;
79 CLOSE cur_get_pending_lot_qty;
80
81 IF p_quantity >= l_qty THEN
82
83 /* Bug#5186388 if transacting qty is greater than pending lot qty then delete the lot
84 rather than updating to zero */
85 l_pending_product_lots_rec.pending_product_lot_id := p_pending_lot_id;
86 delete_pending_product_lot( p_pending_product_lots_rec => l_pending_product_lots_rec
87 ,x_return_status => l_return_status
88 );
89 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
90 RAISE error_delete_row;
91 END IF;
92
93 /*UPDATE gme_pending_product_lots
94 SET quantity = 0,
95 last_updated_by = gme_common_pvt.g_user_ident,
96 last_update_date = gme_common_pvt.g_timestamp,
97 last_update_login = gme_common_pvt.g_login_id
98 WHERE pending_product_lot_id = p_pending_lot_id;
99
100 IF l_sec_qty IS NOT NULL THEN
101 UPDATE gme_pending_product_lots
102 SET secondary_quantity = 0
103 WHERE pending_product_lot_id = p_pending_lot_id;
104 END IF; -- IF l_sec_qty IS NOT NULL THEN */
105 ELSE
106 UPDATE gme_pending_product_lots
107 SET quantity = quantity - p_quantity,
108 last_updated_by = gme_common_pvt.g_user_ident,
109 last_update_date = gme_common_pvt.g_timestamp,
110 last_update_login = gme_common_pvt.g_login_id
111 WHERE pending_product_lot_id = p_pending_lot_id;
112
113 IF l_sec_qty IS NOT NULL THEN
114 UPDATE gme_pending_product_lots
115 SET secondary_quantity = secondary_quantity - p_secondary_quantity
116 WHERE pending_product_lot_id = p_pending_lot_id;
117 END IF; -- IF l_sec_qty IS NOT NULL THEN
118 END IF; -- IF p_quantity >= l_qty THEN
119
120 IF g_debug <= gme_debug.g_log_procedure THEN
121 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
122 END IF;
123 EXCEPTION
124 --Bug#5186388
125 WHEN ERROR_DELETE_ROW THEN
126 x_return_status := l_return_status;
127 WHEN OTHERS THEN
128 IF g_debug <= gme_debug.g_log_unexpected THEN
129 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
130 END IF;
131 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
133 END relieve_pending_lot;
134
135 PROCEDURE create_product_lot
136 (p_organization_id IN NUMBER
137 ,p_inventory_item_id IN NUMBER
138 ,p_parent_lot IN mtl_lot_numbers.lot_number%TYPE := NULL
139 ,p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl
140 ,p_generate_lot IN VARCHAR2
141 ,p_generate_parent_lot IN VARCHAR2
142 /* nsinghi bug#4486074 Added the p_expiration_Date parameter. */
143 ,p_expiration_date IN mtl_lot_numbers.expiration_date%TYPE := NULL
144 ,x_mmli_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_inter_tbl
145 ,x_return_status OUT NOCOPY VARCHAR2) IS
146
147 l_parent_lot mtl_lot_numbers.lot_number%TYPE;
148 l_gen_lot mtl_lot_numbers.lot_number%TYPE;
149 l_in_lot_rec mtl_lot_numbers%ROWTYPE;
150 l_lot_rec mtl_lot_numbers%ROWTYPE;
151 l_null_lot_number BOOLEAN;
152
153 l_msg_count NUMBER;
154 l_msg_data VARCHAR2 (2000);
155
156 l_api_version NUMBER := 1.0;
157 l_source NUMBER;
158 l_row_id ROWID;
159 l_shelf_life_code NUMBER;
160 l_shelf_life_days NUMBER;
161
162 error_null_exp_dt EXCEPTION;
163 error_not_prod EXCEPTION;
164 error_get_item_rec EXCEPTION;
165 error_gen_lot_no_create EXCEPTION;
166 error_gen_lot EXCEPTION;
167 error_gen_parent_lot EXCEPTION;
168 error_lot_create EXCEPTION;
169 error_null_lots EXCEPTION;
170
171 /* nsinghi bug#4486074 Start */
172 CURSOR Cur_item_dtl IS
173 SELECT msi.shelf_life_code, msi.shelf_life_days
174 FROM mtl_system_items msi
175 WHERE msi.inventory_item_id = p_inventory_item_id
176 AND msi.organization_id = p_organization_id;
177 /* nsinghi bug#4486074 End */
178
179 l_api_name CONSTANT VARCHAR2 (30) := 'create_product_lot';
180 BEGIN
181
182 IF g_debug <= gme_debug.g_log_procedure THEN
183 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
184 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_organization_id= '||p_organization_id);
185 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_inventory_item_id= '||p_inventory_item_id);
186 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_parent_lot= '||p_parent_lot);
187 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' lot count= '||p_mmli_tbl.count);
188 FOR i in 1..p_mmli_tbl.count LOOP
189 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' lot number= '||p_mmli_tbl (i).lot_number);
190 END LOOP;
191 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_generate_lot= '||p_generate_lot);
192 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_generate_parent_lot= '||p_generate_parent_lot);
193 END IF;
194
195 x_return_status := FND_API.G_RET_STS_SUCCESS;
196
197 x_mmli_tbl := p_mmli_tbl;
198
199 IF p_generate_lot = fnd_api.g_false THEN
200 l_null_lot_number := FALSE;
201
202 FOR i in 1..x_mmli_tbl.count LOOP
203 IF x_mmli_tbl(i).lot_number IS NULL THEN
204 l_null_lot_number := TRUE;
205 END IF;
206 END LOOP;
207
208 IF l_null_lot_number THEN
209 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
210 gme_debug.put_line (g_pkg_name||'.'||l_api_name
211 ||' p_generate is false and there are null lot numbers');
212 END IF;
213 RAISE error_null_lots;
214 END IF;
215 END IF;
216
217 l_parent_lot := p_parent_lot;
218
219 IF p_generate_parent_lot = fnd_api.g_true AND p_parent_lot IS NULL THEN
220 l_parent_lot :=
221 inv_lot_api_pub.auto_gen_lot
222 (p_org_id => p_organization_id
223 ,p_inventory_item_id => p_inventory_item_id
224 ,p_lot_generation => NULL
225 ,p_lot_uniqueness => NULL
226 ,p_lot_prefix => NULL
227 ,p_zero_pad => NULL
228 ,p_lot_length => NULL
229 ,p_transaction_date => NULL
230 ,p_revision => NULL
231 ,p_subinventory_code => NULL
232 ,p_locator_id => NULL
233 ,p_transaction_type_id => NULL
234 ,p_transaction_action_id => NULL
235 ,p_transaction_source_type_id => NULL
236 ,p_lot_number => NULL
237 ,p_api_version => 1.0
238 ,p_init_msg_list => fnd_api.g_false
239 ,p_commit => fnd_api.g_false
240 ,p_validation_level => NULL
241 ,p_parent_lot_number => NULL
242 ,x_return_status => x_return_status
243 ,x_msg_count => l_msg_count
244 ,x_msg_data => l_msg_data);
245
246 IF x_return_status <> fnd_api.g_ret_sts_success THEN
247 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
248 gme_debug.put_line (g_pkg_name||'.'||l_api_name
249 ||'auto_gen_lot for parent returned '
250 || x_return_status);
251 END IF;
252 RAISE error_gen_parent_lot;
253 END IF;
254
255 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
256 gme_debug.put_line (g_pkg_name||'.'||l_api_name
257 ||'auto_gen_lot for parent'
258 || ':'
259 || 'l_parent_lot= '
260 || l_parent_lot);
261 END IF;
262 END IF; -- IF p_generate_parent_lot = fnd_api.g_true AND p_parent_lot IS NULL THEN
263
264 FOR i IN 1 .. x_mmli_tbl.COUNT LOOP
265 IF x_mmli_tbl (i).lot_number IS NULL THEN
266 x_mmli_tbl (i).lot_number := inv_lot_api_pub.auto_gen_lot
267 (p_org_id => p_organization_id
268 ,p_inventory_item_id => p_inventory_item_id
269 ,p_lot_generation => NULL
270 ,p_lot_uniqueness => NULL
271 ,p_lot_prefix => NULL
272 ,p_zero_pad => NULL
273 ,p_lot_length => NULL
274 ,p_transaction_date => NULL
275 ,p_revision => NULL
276 ,p_subinventory_code => NULL
277 ,p_locator_id => NULL
278 ,p_transaction_type_id => NULL
279 ,p_transaction_action_id => NULL
280 ,p_transaction_source_type_id => NULL
281 ,p_lot_number => NULL
282 ,p_api_version => 1.0
283 ,p_init_msg_list => fnd_api.g_false
284 ,p_commit => fnd_api.g_false
285 ,p_validation_level => NULL
286 ,p_parent_lot_number => l_parent_lot
287 ,x_return_status => x_return_status
288 ,x_msg_count => l_msg_count
289 ,x_msg_data => l_msg_data);
290
291 IF x_return_status <> fnd_api.g_ret_sts_success THEN
292 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
293 gme_debug.put_line (g_pkg_name||'.'||l_api_name
294 ||'auto_gen_lot'
295 || ':'
296 || 'l_gen_lot '
297 || x_return_status);
298 END IF;
299 RAISE error_gen_lot;
300 END IF;
301
302 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
303 gme_debug.put_line (g_pkg_name||'.'||l_api_name
304 ||'auto_gen_lot'
305 || ':'
306 || 'l_gen_lot= '
307 || l_gen_lot);
308 END IF;
309 END IF; -- IF x_mmli_tbl (i).lot_number IS NULL THEN
310
311 /* nsinghi bug#4486074 Start */
312
313 OPEN Cur_item_dtl;
314 FETCH Cur_item_dtl INTO l_shelf_life_code, l_shelf_life_days;
315 CLOSE Cur_item_dtl;
316
317 IF l_shelf_life_code = 2 THEN /* shelf life days */
318 l_in_lot_rec.expiration_date := SYSDATE + l_shelf_life_days;
319 ELSIF l_shelf_life_code = 4 THEN
320 IF p_expiration_date IS NULL THEN /* user-defined */
321 FND_MESSAGE.SET_NAME('INV','INV_NULL_EXPIRATION_DATE_EXP');
322 FND_MESSAGE.SET_TOKEN('PGM_NAME',g_pkg_name||'.'||l_api_name);
323 fnd_msg_pub.ADD;
324 RAISE error_null_exp_dt;
325 ELSE
326 l_in_lot_rec.expiration_date := p_expiration_date;
327 END IF;
328 END IF;
329
330 /* nsinghi bug#4486074 End */
331
332 l_in_lot_rec.parent_lot_number := l_parent_lot;
333 l_in_lot_rec.organization_id := p_organization_id;
334 l_in_lot_rec.inventory_item_id := p_inventory_item_id;
335 l_in_lot_rec.lot_number := x_mmli_tbl (i).lot_number;
336
337 inv_lot_api_pub.create_inv_lot
338 (x_return_status => x_return_status
339 ,x_msg_count => l_msg_count
340 ,x_msg_data => l_msg_data
341 ,x_row_id => l_row_id
342 ,x_lot_rec => l_lot_rec
343 ,p_lot_rec => l_in_lot_rec
344 ,p_source => l_source
345 ,p_api_version => l_api_version
346 ,p_init_msg_list => fnd_api.g_true
347 ,p_commit => fnd_api.g_false
348 ,p_validation_level => fnd_api.g_valid_level_full
349 ,p_origin_txn_id => 1);
350
351 IF x_return_status <> fnd_api.g_ret_sts_success THEN
352 RAISE error_lot_create;
353 END IF;
354
355 x_mmli_tbl (i).parent_lot_number := l_parent_lot;
356 END LOOP; -- FOR i IN 1 .. l_mmli_tbl.COUNT LOOP
357
358 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
359 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
360 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' lot count= '||p_mmli_tbl.count);
361 FOR i in 1..p_mmli_tbl.count LOOP
362 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' lot number= '||p_mmli_tbl (i).lot_number);
363 END LOOP;
364 END IF;
365
366 EXCEPTION
367 WHEN error_get_item_rec OR error_gen_parent_lot OR error_gen_lot THEN
368 NULL;
369 WHEN error_not_prod OR error_lot_create OR error_gen_lot_no_create OR error_null_lots OR error_null_exp_dt THEN
370 x_return_status := fnd_api.g_ret_sts_error;
371 WHEN OTHERS THEN
372 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
373 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
374 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
375 END IF;
376 x_return_status := FND_API.g_ret_sts_unexp_error;
377
378 END create_product_lot;
379
380 PROCEDURE create_pending_product_lot
381 (p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
382 ,x_pending_product_lots_rec OUT NOCOPY gme_pending_product_lots%ROWTYPE
383 ,x_return_status OUT NOCOPY VARCHAR2) IS
384
385 l_pp_lot_rec gme_pending_product_lots%ROWTYPE;
386 error_insert_row EXCEPTION;
387
388 l_api_name CONSTANT VARCHAR2 (30) := 'create_pending_product_lot';
389
390 BEGIN
391
392 IF g_debug <= gme_debug.g_log_procedure THEN
393 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
394 END IF;
395 x_return_status := FND_API.G_RET_STS_SUCCESS;
396
397 IF NOT gme_pending_product_lots_dbl.insert_row
398 (p_pending_product_lots_rec => p_pending_product_lots_rec
399 ,x_pending_product_lots_rec => l_pp_lot_rec) THEN
400 RAISE error_insert_row;
401 END IF;
402
403 x_pending_product_lots_rec := l_pp_lot_rec;
404
405 IF g_debug <= gme_debug.g_log_procedure THEN
406 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
407 END IF;
408
409 EXCEPTION
410 WHEN error_insert_row THEN
411 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
412 x_return_status := FND_API.g_ret_sts_unexp_error;
413 WHEN OTHERS THEN
414 IF g_debug <= gme_debug.g_log_unexpected THEN
415 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
416 END IF;
417 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
419 END create_pending_product_lot;
420
421 PROCEDURE update_pending_product_lot
422 (p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
423 ,x_pending_product_lots_rec OUT NOCOPY gme_pending_product_lots%ROWTYPE
424 ,x_return_status OUT NOCOPY VARCHAR2) IS
425
426 error_update_row EXCEPTION;
427 error_fetch_row EXCEPTION;
428 l_api_name CONSTANT VARCHAR2 (30) := 'update_pending_product_lot';
429
430 BEGIN
431
432 IF g_debug <= gme_debug.g_log_procedure THEN
433 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
434 END IF;
435 x_return_status := FND_API.G_RET_STS_SUCCESS;
436
437 IF NOT gme_pending_product_lots_dbl.update_row
438 (p_pending_product_lots_rec => p_pending_product_lots_rec) THEN
439 RAISE error_update_row;
440 END IF;
441
442 IF NOT gme_pending_product_lots_dbl.fetch_row
443 (p_pending_product_lots_rec => p_pending_product_lots_rec
444 ,x_pending_product_lots_rec => x_pending_product_lots_rec) THEN
445 RAISE error_fetch_row;
446 END IF;
447
448 IF g_debug <= gme_debug.g_log_procedure THEN
449 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
450 END IF;
451
452 EXCEPTION
453 WHEN error_update_row OR error_fetch_row THEN
454 -- error message set in fetch routine
455 x_return_status := fnd_api.g_ret_sts_unexp_error;
456 WHEN OTHERS THEN
457 IF g_debug <= gme_debug.g_log_unexpected THEN
458 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
459 END IF;
460 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
462 END update_pending_product_lot;
463
464 PROCEDURE delete_pending_product_lot
465 (p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
466 ,x_return_status OUT NOCOPY VARCHAR2) IS
467
468 error_delete_row EXCEPTION;
469 l_api_name CONSTANT VARCHAR2 (30) := 'delete_pending_product_lot';
470
471 BEGIN
472
473 IF g_debug <= gme_debug.g_log_procedure THEN
474 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
475 END IF;
476 x_return_status := FND_API.G_RET_STS_SUCCESS;
477
478 IF NOT gme_pending_product_lots_dbl.delete_row
479 (p_pending_product_lots_rec => p_pending_product_lots_rec) THEN
480 RAISE error_delete_row;
481 END IF;
482
483 IF g_debug <= gme_debug.g_log_procedure THEN
484 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
485 END IF;
486
487 EXCEPTION
488 WHEN error_delete_row THEN
489 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
490 x_return_status := FND_API.g_ret_sts_unexp_error;
491 WHEN OTHERS THEN
492 IF g_debug <= gme_debug.g_log_unexpected THEN
493 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
494 END IF;
495 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
497 END delete_pending_product_lot;
498
499 --Bug#5078853 created the following over loaded procedure
500 PROCEDURE delete_pending_product_lot
501 (p_material_detail_id IN NUMBER
502 ,x_return_status OUT NOCOPY VARCHAR2)
503 IS
504 CURSOR c_get_pending_lots IS
505 SELECT pending_product_lot_id
506 FROM gme_pending_product_lots
507 WHERE material_detail_id = p_material_detail_id;
508
509 l_api_name CONSTANT VARCHAR2 (30) := 'delete_pending_product_lot';
510 l_pending_product_lots_rec gme_pending_product_lots%ROWTYPE;
511
512 error_delete_row EXCEPTION;
513 BEGIN
514 IF g_debug <= gme_debug.g_log_procedure THEN
515 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
516 END IF;
517 x_return_status := FND_API.G_RET_STS_SUCCESS;
518
519 IF p_material_detail_id IS NOT NULL THEN
520 OPEN c_get_pending_lots;
521 LOOP
522 FETCH c_get_pending_lots INTO l_pending_product_lots_rec.pending_product_lot_id;
523 EXIT WHEN c_get_pending_lots%NOTFOUND;
524 --call dbl layer
525 IF NOT gme_pending_product_lots_dbl.delete_row
526 (p_pending_product_lots_rec => l_pending_product_lots_rec) THEN
527 CLOSE c_get_pending_lots;
528 RAISE error_delete_row;
529 END IF;
530 END LOOP;
531 CLOSE c_get_pending_lots;
532 END IF; /* p_material_detail_id IS NOT NULL*/
533
534 IF g_debug <= gme_debug.g_log_procedure THEN
535 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
536 END IF;
537
538 EXCEPTION
539 WHEN error_delete_row THEN
540 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
541 x_return_status := FND_API.g_ret_sts_unexp_error;
542 WHEN OTHERS THEN
543 IF g_debug <= gme_debug.g_log_unexpected THEN
544 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
545 END IF;
546 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
547 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
548 END delete_pending_product_lot;
549
550 PROCEDURE validate_material_for_create
551 (p_batch_header_rec IN gme_batch_header%ROWTYPE
552 ,p_material_detail_rec IN gme_material_details%ROWTYPE
553 ,x_return_status OUT NOCOPY VARCHAR2) IS
554
555 l_api_name CONSTANT VARCHAR2 (30) := 'validate_material_for_create';
556
557 l_item_rec mtl_system_items_b%ROWTYPE;
558
559 error_not_lot_control EXCEPTION;
560 error_no_lot_create EXCEPTION;
561 error_get_item_rec EXCEPTION;
562
563 BEGIN
564
565 IF g_debug <= gme_debug.g_log_procedure THEN
566 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
567 END IF;
568 x_return_status := FND_API.G_RET_STS_SUCCESS;
569
570 gme_material_detail_pvt.get_item_rec
571 (p_org_id => p_batch_header_rec.organization_id
572 ,p_item_id => p_material_detail_rec.inventory_item_id
573 ,x_item_rec => l_item_rec
574 ,x_return_status => x_return_status);
575
576 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
577 RAISE error_get_item_rec;
578 END IF;
579
580 IF l_item_rec.lot_control_code = 1 THEN
581 FND_MESSAGE.SET_NAME('INV','INV_NO_LOT_CONTROL');
582 FND_MESSAGE.SET_TOKEN('PGM_NAME',g_pkg_name||'.'||l_api_name);
583 fnd_msg_pub.ADD;
584 RAISE error_not_lot_control;
585 END IF;
586
587 IF p_batch_header_rec.update_inventory_ind = 'N' THEN
588 IF p_material_detail_rec.line_type <> gme_common_pvt.g_line_type_ing THEN
589 gme_common_pvt.log_message('GME_NO_LOT_CREATE');
590 RAISE error_no_lot_create;
591 END IF;
592 ELSE
593 IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
594 gme_common_pvt.log_message('GME_NO_LOT_CREATE');
595 RAISE error_no_lot_create;
596 END IF;
597 END IF;
598
599 IF g_debug <= gme_debug.g_log_procedure THEN
600 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
601 END IF;
602
603 EXCEPTION
604 WHEN error_not_lot_control OR error_no_lot_create THEN
605 x_return_status := fnd_api.g_ret_sts_error;
606 WHEN OTHERS THEN
607 IF g_debug <= gme_debug.g_log_unexpected THEN
608 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
609 END IF;
610 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
611 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
612 END validate_material_for_create;
613
614 FUNCTION get_last_sequence
615 (p_matl_dtl_id IN NUMBER
616 ,x_return_status OUT NOCOPY VARCHAR2)
617 RETURN NUMBER IS
618 CURSOR cur_get_sequ(v_dtl_id NUMBER) IS
619 SELECT max(sequence)
620 FROM gme_pending_product_lots
621 WHERE material_detail_id = v_dtl_id;
622
623 l_api_name CONSTANT VARCHAR2 (30) := 'get_last_sequence';
624
625 l_sequ NUMBER;
626
627 BEGIN
628 IF g_debug <= gme_debug.g_log_procedure THEN
629 gme_debug.put_line('Entering function '||g_pkg_name||'.'||l_api_name);
630 END IF;
631 x_return_status := FND_API.G_RET_STS_SUCCESS;
632
633 OPEN cur_get_sequ(p_matl_dtl_id);
634 FETCH cur_get_sequ INTO l_sequ;
635 CLOSE cur_get_sequ;
636
637 IF g_debug <= gme_debug.g_log_procedure THEN
638 gme_debug.put_line('Exiting function '||g_pkg_name||'.'||l_api_name);
639 END IF;
640
641 RETURN NVL(l_sequ, 0);
642
643 EXCEPTION
644 WHEN OTHERS THEN
645 IF g_debug <= gme_debug.g_log_unexpected THEN
646 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
647 END IF;
648 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
650 END get_last_sequence;
651
652 PROCEDURE validate_record_for_create
653 (p_material_detail_rec IN gme_material_details%ROWTYPE
654 ,p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
655 ,p_create_lot IN VARCHAR2
656 ,p_generate_lot IN VARCHAR2
657 ,p_generate_parent_lot IN VARCHAR2
658 ,p_parent_lot IN mtl_lot_numbers.lot_number%TYPE := NULL
659 /* nsinghi bug#4486074 Added the p_expiration_Date parameter. */
660 ,p_expiration_date IN mtl_lot_numbers.expiration_date%TYPE := NULL
661 ,x_pending_product_lots_rec OUT NOCOPY gme_pending_product_lots%ROWTYPE
662 ,x_return_status OUT NOCOPY VARCHAR2) IS
663
664 l_api_name CONSTANT VARCHAR2 (30) := 'validate_record_for_create';
665
666 error_validate EXCEPTION;
667 error_create_lot EXCEPTION;
668 error_get_item EXCEPTION;
669
670 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
671 l_in_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
672 l_lot_number mtl_lot_numbers.lot_number%TYPE;
673 l_dtl_qty NUMBER;
674 l_sec_qty NUMBER;
675 l_item_rec mtl_system_items_b%ROWTYPE;
676
677 l_sequence NUMBER;
678
679 BEGIN
680
681 IF g_debug <= gme_debug.g_log_procedure THEN
682 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
683 END IF;
684 x_return_status := FND_API.G_RET_STS_SUCCESS;
685
686 x_pending_product_lots_rec.batch_id := p_material_detail_rec.batch_id;
687 x_pending_product_lots_rec.material_detail_id := p_material_detail_rec.material_detail_id;
688
689 gme_material_detail_pvt.get_item_rec
690 (p_org_id => p_material_detail_rec.organization_id
691 ,p_item_id => p_material_detail_rec.inventory_item_id
692 ,x_item_rec => l_item_rec
693 ,x_return_status => x_return_status);
694
695 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
696 RAISE error_get_item;
697 END IF;
698
699 -- Validate following fields:
700 /*
701 SEQUENCE
702 REVISION
703 LOT_NUMBER
704 QUANTITY
705 SECONDARY_QUANTITY
706 REASON_ID
707 */
708
709 IF p_pending_product_lots_rec.sequence IS NULL THEN
710 l_sequence := get_last_sequence
711 (p_matl_dtl_id => p_material_detail_rec.material_detail_id
712 ,x_return_status => x_return_status);
713
714 l_sequence := l_sequence + g_sequence_increment;
715 ELSE
716 l_sequence := p_pending_product_lots_rec.sequence;
717 END IF;
718
719 IF NOT validate_sequence
720 (p_matl_dtl_rec => p_material_detail_rec
721 ,p_sequence => l_sequence
722 ,x_return_status => x_return_status) THEN
723 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
724 x_return_status := FND_API.G_RET_STS_ERROR;
725 END IF;
726 RAISE error_validate;
727 END IF;
728
729 x_pending_product_lots_rec.sequence := l_sequence;
730
731 IF NOT validate_revision
732 (p_item_rec => l_item_rec
733 ,p_revision => p_pending_product_lots_rec.revision
734 ,x_return_status => x_return_status) THEN
735 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
736 x_return_status := FND_API.G_RET_STS_ERROR;
737 END IF;
738 RAISE error_validate;
739 END IF;
740
741 x_pending_product_lots_rec.revision := p_pending_product_lots_rec.revision;
742
743 l_lot_number := p_pending_product_lots_rec.lot_number;
744
745 IF p_material_detail_rec.line_type <> gme_common_pvt.g_line_type_ing AND
746 p_create_lot = fnd_api.g_true THEN
747 l_in_mmli_tbl(1).lot_number := p_pending_product_lots_rec.lot_number;
748
749 create_product_lot
750 (p_organization_id => p_material_detail_rec.organization_id
751 ,p_inventory_item_id => p_material_detail_rec.inventory_item_id
752 ,p_parent_lot => p_parent_lot
753 ,p_mmli_tbl => l_in_mmli_tbl
754 ,p_generate_lot => p_generate_lot
755 ,p_generate_parent_lot => p_generate_parent_lot
756 /* nsinghi bug#4486074 Added the p_expiration_Date parameter. */
757 ,p_expiration_date => p_expiration_date
758 ,x_mmli_tbl => l_mmli_tbl
759 ,x_return_status => x_return_status);
760
761 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
762 RAISE error_create_lot;
763 END IF;
764
765 l_lot_number := l_mmli_tbl(1).lot_number;
766 END IF;
767
768 IF NOT validate_lot_number
769 (p_inv_item_id => p_material_detail_rec.inventory_item_id
770 ,p_org_id => p_material_detail_rec.organization_id
771 ,p_lot_number => l_lot_number
772 ,x_return_status => x_return_status) THEN
773 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
774 x_return_status := FND_API.G_RET_STS_ERROR;
775 END IF;
776 RAISE error_validate;
777 END IF;
778
779 x_pending_product_lots_rec.lot_number := l_lot_number;
780
781 IF NOT validate_reason_id
782 (p_reason_id => p_pending_product_lots_rec.reason_id
783 ,x_return_status => x_return_status) THEN
784 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
785 x_return_status := FND_API.G_RET_STS_ERROR;
786 END IF;
787 RAISE error_validate;
788 END IF;
789
790 x_pending_product_lots_rec.reason_id := p_pending_product_lots_rec.reason_id;
791
792 l_dtl_qty := p_pending_product_lots_rec.quantity;
793 l_sec_qty := p_pending_product_lots_rec.secondary_quantity;
794
795 IF NOT validate_quantities
796 (p_matl_dtl_rec => p_material_detail_rec
797 ,p_lot_number => x_pending_product_lots_rec.lot_number
798 ,p_revision => x_pending_product_lots_rec.revision
799 ,p_dtl_qty => l_dtl_qty
800 ,p_sec_qty => l_sec_qty
801 ,x_return_status => x_return_status) THEN
802 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
803 x_return_status := FND_API.G_RET_STS_ERROR;
804 END IF;
805 RAISE error_validate;
806 END IF;
807
808 x_pending_product_lots_rec.quantity := l_dtl_qty;
809 x_pending_product_lots_rec.secondary_quantity := l_sec_qty;
810
811 -- Generated
812 /*
813 PENDING_PRODUCT_LOT_ID
814 CREATION_DATE
815 CREATED_BY
816 LAST_UPDATE_DATE
817 LAST_UPDATED_BY
818 LAST_UPDATE_LOGIN
819 */
820
821 IF g_debug <= gme_debug.g_log_procedure THEN
822 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
823 END IF;
824
825 EXCEPTION
826 WHEN error_validate OR error_create_lot OR error_get_item THEN
827 NULL;
828 WHEN OTHERS THEN
829 IF g_debug <= gme_debug.g_log_unexpected THEN
830 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
831 END IF;
832 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
834 END validate_record_for_create;
835
836 PROCEDURE validate_material_for_update
837 (p_batch_header_rec IN gme_batch_header%ROWTYPE
838 ,p_material_detail_rec IN gme_material_details%ROWTYPE
839 ,x_return_status OUT NOCOPY VARCHAR2) IS
840
841 l_api_name CONSTANT VARCHAR2 (30) := 'validate_material_for_update';
842
843 BEGIN
844
845 IF g_debug <= gme_debug.g_log_procedure THEN
846 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
847 END IF;
848 x_return_status := FND_API.G_RET_STS_SUCCESS;
849
850 IF g_debug <= gme_debug.g_log_procedure THEN
851 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
852 END IF;
853
854 EXCEPTION
855 WHEN OTHERS THEN
856 IF g_debug <= gme_debug.g_log_unexpected THEN
857 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
858 END IF;
859 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
860 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
861 END validate_material_for_update;
862
863 PROCEDURE validate_record_for_update
864 (p_material_detail_rec IN gme_material_details%ROWTYPE
865 ,p_db_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
866 ,p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
867 ,x_pending_product_lots_rec OUT NOCOPY gme_pending_product_lots%ROWTYPE
868 ,x_return_status OUT NOCOPY VARCHAR2) IS
869
870 l_api_name CONSTANT VARCHAR2 (30) := 'validate_record_for_update';
871
872 l_pending_product_lots_rec gme_pending_product_lots%ROWTYPE;
873 l_db_pending_product_lots_rec gme_pending_product_lots%ROWTYPE;
874 l_in_pending_product_lots_rec gme_pending_product_lots%ROWTYPE;
875
876 l_dtl_qty NUMBER;
877 l_sec_qty NUMBER;
878
879 l_sequence NUMBER;
880
881 l_item_rec mtl_system_items_b%ROWTYPE;
882
883 error_validate EXCEPTION;
884 error_fetch_row EXCEPTION;
885 error_get_item EXCEPTION;
886
887 BEGIN
888
889 IF g_debug <= gme_debug.g_log_procedure THEN
890 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
891 END IF;
892 x_return_status := FND_API.G_RET_STS_SUCCESS;
893
894
895 IF p_db_pending_product_lots_rec.pending_product_lot_id IS NOT NULL THEN
896 l_db_pending_product_lots_rec := p_db_pending_product_lots_rec;
897
898 -- set sequence to that passed in because it may need to be updated
899 l_sequence := p_pending_product_lots_rec.sequence;
900 ELSE
901 l_in_pending_product_lots_rec := p_pending_product_lots_rec;
902 l_in_pending_product_lots_rec.material_detail_id := p_material_detail_rec.material_detail_id;
903 l_in_pending_product_lots_rec.batch_id := p_material_detail_rec.batch_id;
904
905 IF NOT gme_pending_product_lots_dbl.fetch_row
906 (p_pending_product_lots_rec => l_in_pending_product_lots_rec
907 ,x_pending_product_lots_rec => l_db_pending_product_lots_rec) THEN
908 RAISE error_fetch_row;
909 END IF;
910
911 -- sequence was used for retreival... not needed anymore, so NULL it out...
912 l_sequence := NULL;
913 END IF;
914
915 x_pending_product_lots_rec.pending_product_lot_id := l_db_pending_product_lots_rec.pending_product_lot_id;
916 x_pending_product_lots_rec.batch_id := l_db_pending_product_lots_rec.batch_id;
917 x_pending_product_lots_rec.material_detail_id := l_db_pending_product_lots_rec.material_detail_id;
918 x_pending_product_lots_rec.last_update_date := l_db_pending_product_lots_rec.last_update_date;
919 x_pending_product_lots_rec.last_update_login := l_db_pending_product_lots_rec.last_update_login;
920 x_pending_product_lots_rec.last_updated_by := l_db_pending_product_lots_rec.last_updated_by;
921
922 gme_material_detail_pvt.get_item_rec
923 (p_org_id => p_material_detail_rec.organization_id
924 ,p_item_id => p_material_detail_rec.inventory_item_id
925 ,x_item_rec => l_item_rec
926 ,x_return_status => x_return_status);
927
928 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
929 RAISE error_get_item;
930 END IF;
931
932 -- Validate following fields:
933 /*
934 SEQUENCE
935 REVISION
936 LOT_NUMBER
937 QUANTITY
938 SECONDARY_QUANTITY
939 REASON_ID
940 */
941
942 -- l_sequence is set above because if passed in for retrieval, then it shouldn't be
943 -- looked at for change, if pplot_id is passed in, then look at sequence for update
944
945 IF l_sequence = fnd_api.g_miss_num THEN
946 l_sequence := get_last_sequence
947 (p_matl_dtl_id => p_material_detail_rec.material_detail_id
948 ,x_return_status => x_return_status);
949 l_sequence := l_sequence + g_sequence_increment;
950 END IF;
951
952 IF l_sequence IS NOT NULL THEN
953 IF NOT validate_sequence
954 (p_matl_dtl_rec => p_material_detail_rec
955 ,p_sequence => l_sequence
956 ,x_return_status => x_return_status) THEN
957 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
958 x_return_status := FND_API.G_RET_STS_ERROR;
959 END IF;
960 RAISE error_validate;
961 END IF;
962 x_pending_product_lots_rec.sequence := l_sequence;
963 ELSE
964 x_pending_product_lots_rec.sequence := l_db_pending_product_lots_rec.sequence;
965 END IF;
966
967 IF p_pending_product_lots_rec.revision = fnd_api.g_miss_char THEN
968 x_pending_product_lots_rec.revision := NULL;
969 ELSIF p_pending_product_lots_rec.revision IS NOT NULL THEN
970 IF NOT validate_revision
971 (p_item_rec => l_item_rec
972 ,p_revision => p_pending_product_lots_rec.revision
973 ,x_return_status => x_return_status) THEN
974 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
975 x_return_status := FND_API.G_RET_STS_ERROR;
976 END IF;
977 RAISE error_validate;
978 END IF;
979
980 x_pending_product_lots_rec.revision := p_pending_product_lots_rec.revision;
981 ELSE
982 x_pending_product_lots_rec.revision := l_db_pending_product_lots_rec.revision;
983 END IF;
984
985 IF p_pending_product_lots_rec.lot_number = fnd_api.g_miss_char THEN
986 x_pending_product_lots_rec.lot_number := NULL;
987 IF NOT validate_lot_number
988 (p_inv_item_id => p_material_detail_rec.inventory_item_id
989 ,p_org_id => p_material_detail_rec.organization_id
990 ,p_lot_number => x_pending_product_lots_rec.lot_number
991 ,x_return_status => x_return_status) THEN
992 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
993 x_return_status := FND_API.G_RET_STS_ERROR;
994 END IF;
995 RAISE error_validate;
996 END IF;
997 ELSIF p_pending_product_lots_rec.lot_number IS NOT NULL THEN
998 IF NOT validate_lot_number
999 (p_inv_item_id => p_material_detail_rec.inventory_item_id
1000 ,p_org_id => p_material_detail_rec.organization_id
1001 ,p_lot_number => p_pending_product_lots_rec.lot_number
1002 ,x_return_status => x_return_status) THEN
1003 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1004 x_return_status := FND_API.G_RET_STS_ERROR;
1005 END IF;
1006 RAISE error_validate;
1007 END IF;
1008 x_pending_product_lots_rec.lot_number := p_pending_product_lots_rec.lot_number;
1009 ELSE
1010 x_pending_product_lots_rec.lot_number := l_db_pending_product_lots_rec.lot_number;
1011 END IF;
1012
1013 IF p_pending_product_lots_rec.reason_id = fnd_api.g_miss_num THEN
1014 x_pending_product_lots_rec.reason_id := NULL;
1015 ELSIF p_pending_product_lots_rec.reason_id IS NOT NULL THEN
1016 IF NOT validate_reason_id
1017 (p_reason_id => p_pending_product_lots_rec.reason_id
1018 ,x_return_status => x_return_status) THEN
1019 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1020 x_return_status := FND_API.G_RET_STS_ERROR;
1021 END IF;
1022 RAISE error_validate;
1023 END IF;
1024 x_pending_product_lots_rec.reason_id := p_pending_product_lots_rec.reason_id;
1025 ELSE
1026 x_pending_product_lots_rec.reason_id := l_db_pending_product_lots_rec.reason_id;
1027 END IF;
1028
1029 IF p_pending_product_lots_rec.quantity = fnd_api.g_miss_num THEN
1030 l_dtl_qty := NULL;
1031 ELSIF p_pending_product_lots_rec.quantity IS NOT NULL THEN
1032 l_dtl_qty := p_pending_product_lots_rec.quantity;
1033 ELSE
1034 l_dtl_qty := l_db_pending_product_lots_rec.quantity;
1035 END IF;
1036
1037 IF p_pending_product_lots_rec.secondary_quantity = fnd_api.g_miss_num THEN
1038 l_sec_qty := NULL;
1039 ELSIF p_pending_product_lots_rec.quantity IS NOT NULL THEN
1040 l_sec_qty := p_pending_product_lots_rec.secondary_quantity;
1041 ELSE
1042 l_sec_qty := l_db_pending_product_lots_rec.secondary_quantity;
1043 END IF;
1044
1045 IF NOT validate_quantities
1046 (p_matl_dtl_rec => p_material_detail_rec
1047 ,p_lot_number => x_pending_product_lots_rec.lot_number
1048 ,p_revision => x_pending_product_lots_rec.revision
1049 ,p_dtl_qty => l_dtl_qty
1050 ,p_sec_qty => l_sec_qty
1051 ,x_return_status => x_return_status) THEN
1052 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1053 x_return_status := FND_API.G_RET_STS_ERROR;
1054 END IF;
1055 RAISE error_validate;
1056 END IF;
1057
1058 x_pending_product_lots_rec.quantity := l_dtl_qty;
1059 x_pending_product_lots_rec.secondary_quantity := l_sec_qty;
1060
1061 IF g_debug <= gme_debug.g_log_procedure THEN
1062 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1063 END IF;
1064
1065 EXCEPTION
1066 WHEN error_validate OR error_get_item THEN
1067 NULL;
1068 WHEN error_fetch_row THEN
1069 -- error message set in fetch routine
1070 x_return_status := fnd_api.g_ret_sts_unexp_error;
1071 WHEN OTHERS THEN
1072 IF g_debug <= gme_debug.g_log_unexpected THEN
1073 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
1074 END IF;
1075 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1076 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1077 END validate_record_for_update;
1078
1079 PROCEDURE validate_material_for_delete
1080 (p_batch_header_rec IN gme_batch_header%ROWTYPE
1081 ,p_material_detail_rec IN gme_material_details%ROWTYPE
1082 ,x_return_status OUT NOCOPY VARCHAR2) IS
1083
1084 l_api_name CONSTANT VARCHAR2 (30) := 'validate_material_for_delete';
1085
1086 BEGIN
1087
1088 IF g_debug <= gme_debug.g_log_procedure THEN
1089 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1090 END IF;
1091 x_return_status := FND_API.G_RET_STS_SUCCESS;
1092
1093 IF g_debug <= gme_debug.g_log_procedure THEN
1094 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1095 END IF;
1096
1097 EXCEPTION
1098 WHEN OTHERS THEN
1099 IF g_debug <= gme_debug.g_log_unexpected THEN
1100 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
1101 END IF;
1102 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1104 END validate_material_for_delete;
1105
1106 PROCEDURE validate_record_for_delete
1107 (p_material_detail_rec IN gme_material_details%ROWTYPE
1108 ,p_db_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
1109 ,p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
1110 ,x_pending_product_lots_rec OUT NOCOPY gme_pending_product_lots%ROWTYPE
1111 ,x_return_status OUT NOCOPY VARCHAR2) IS
1112
1113 l_api_name CONSTANT VARCHAR2 (30) := 'validate_record_for_delete';
1114
1115 l_in_pending_product_lots_rec gme_pending_product_lots%ROWTYPE;
1116
1117 error_fetch_row EXCEPTION;
1118
1119 BEGIN
1120
1121 IF g_debug <= gme_debug.g_log_procedure THEN
1122 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1123 END IF;
1124 x_return_status := FND_API.G_RET_STS_SUCCESS;
1125
1126 IF p_db_pending_product_lots_rec.pending_product_lot_id IS NOT NULL THEN
1127 x_pending_product_lots_rec := p_db_pending_product_lots_rec;
1128 ELSE
1129 l_in_pending_product_lots_rec := p_pending_product_lots_rec;
1130 l_in_pending_product_lots_rec.material_detail_id := p_material_detail_rec.material_detail_id;
1131 l_in_pending_product_lots_rec.batch_id := p_material_detail_rec.batch_id;
1132
1133 IF NOT gme_pending_product_lots_dbl.fetch_row
1134 (p_pending_product_lots_rec => l_in_pending_product_lots_rec
1135 ,x_pending_product_lots_rec => x_pending_product_lots_rec) THEN
1136 RAISE error_fetch_row;
1137 END IF;
1138 END IF;
1139
1140 IF g_debug <= gme_debug.g_log_procedure THEN
1141 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1142 END IF;
1143
1144 EXCEPTION
1145 WHEN error_fetch_row THEN
1146 -- error message set in fetch routine
1147 x_return_status := fnd_api.g_ret_sts_unexp_error;
1148 WHEN OTHERS THEN
1149 IF g_debug <= gme_debug.g_log_unexpected THEN
1150 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
1151 END IF;
1152 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1154 END validate_record_for_delete;
1155
1156 -- Call this procedure at the record level because fields besides lot number are required
1157 FUNCTION validate_quantities
1158 (p_matl_dtl_rec IN gme_material_details%ROWTYPE
1159 ,p_lot_number IN VARCHAR2
1160 ,p_revision IN VARCHAR2
1161 ,p_dtl_qty IN OUT NOCOPY NUMBER
1162 ,p_sec_qty IN OUT NOCOPY NUMBER
1163 ,x_return_status OUT NOCOPY VARCHAR2)
1164
1165 RETURN BOOLEAN IS
1166 l_api_name CONSTANT VARCHAR2 (30) := 'validate_quantities';
1167 l_exists NUMBER;
1168 l_return BOOLEAN;
1169 l_transaction_type_id NUMBER;
1170 l_primary_uom_code VARCHAR2(3);
1171 l_primary_lot_qty NUMBER;
1172 l_secondary_uom_code VARCHAR2(3);
1173 l_secondary_lot_qty NUMBER;
1174
1175 l_return_status VARCHAR2(1);
1176 l_msg_data VARCHAR2(3000);
1177 l_msg_count NUMBER;
1178
1179 CURSOR cur_get_uom (v_item_id NUMBER, v_org_id NUMBER) IS
1180 SELECT primary_uom_code, secondary_uom_code
1181 FROM mtl_system_items_b
1182 WHERE inventory_item_id = v_item_id
1183 AND organization_id = v_org_id;
1184
1185 error_um_conv EXCEPTION;
1186 error_val_qties EXCEPTION;
1187
1188 BEGIN
1189
1190 IF g_debug <= gme_debug.g_log_procedure THEN
1191 gme_debug.put_line('Entering function '||g_pkg_name||'.'||l_api_name);
1192 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_matl_dtl_rec.material_detail_id='||p_matl_dtl_rec.material_detail_id);
1193 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_matl_dtl_rec.inventory_item_id='||p_matl_dtl_rec.inventory_item_id);
1194 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_matl_dtl_rec.organization_id='||p_matl_dtl_rec.organization_id);
1195 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_matl_dtl_rec.subinventory='||p_matl_dtl_rec.subinventory);
1196 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_matl_dtl_rec.locator_id='||p_matl_dtl_rec.locator_id);
1197 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_matl_dtl_rec.dtl_um='||p_matl_dtl_rec.dtl_um);
1198 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_lot_number='||p_lot_number);
1199 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_revision='||p_revision);
1200 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_dtl_qty='||p_dtl_qty);
1201 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_sec_qty='||p_sec_qty);
1202 END IF;
1203 x_return_status := FND_API.G_RET_STS_SUCCESS;
1204
1205 IF p_matl_dtl_rec.line_type = gme_common_pvt.g_line_type_prod THEN
1206 l_transaction_type_id := GME_COMMON_PVT.g_prod_completion;
1207 ELSIF p_matl_dtl_rec.line_type = gme_common_pvt.g_line_type_byprod THEN
1208 l_transaction_type_id := GME_COMMON_PVT.g_byprod_completion;
1209 ELSE
1210 l_transaction_type_id := GME_COMMON_PVT.g_ing_issue;
1211 END IF;
1212
1213 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1214 gme_debug.put_line ( g_pkg_name||'.'||l_api_name||' l_transaction_type_id='||l_transaction_type_id);
1215 END IF;
1216
1217 OPEN cur_get_uom(p_matl_dtl_rec.inventory_item_id, p_matl_dtl_rec.organization_id);
1218 FETCH cur_get_uom INTO l_primary_uom_code, l_secondary_uom_code;
1219 CLOSE cur_get_uom;
1220
1221 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1222 gme_debug.put_line ( g_pkg_name||'.'||l_api_name||' l_primary_uom_code='||l_primary_uom_code);
1223 gme_debug.put_line ( g_pkg_name||'.'||l_api_name||' l_secondary_uom_code='||l_secondary_uom_code);
1224 END IF;
1225
1226 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1227 gme_debug.put_line ( g_pkg_name||'.'||l_api_name||' l_primary_lot_qty='||l_primary_lot_qty);
1228 END IF;
1229
1230 l_return := INV_LOT_API_PUB.validate_quantities
1231 (p_api_version => 1.0
1232 ,p_init_msg_list => FND_API.G_FALSE
1233 ,p_transaction_type_id => l_transaction_type_id
1234 ,p_organization_id => p_matl_dtl_rec.organization_id
1235 ,p_inventory_item_id => p_matl_dtl_rec.inventory_item_id
1236 ,p_revision => p_revision
1237 ,p_subinventory_code => p_matl_dtl_rec.subinventory
1238 ,p_locator_id => p_matl_dtl_rec.locator_id
1239 ,p_lot_number => p_lot_number
1240 ,p_transaction_quantity => p_dtl_qty
1241 ,p_transaction_uom_code => p_matl_dtl_rec.dtl_um
1242 ,p_primary_quantity => l_primary_lot_qty
1243 ,p_primary_uom_code => l_primary_uom_code
1244 ,p_secondary_quantity => p_sec_qty
1245 ,p_secondary_uom_code => l_secondary_uom_code
1246 ,x_return_status => l_return_status
1247 ,x_msg_count => l_msg_count
1248 ,x_msg_data => l_msg_data);
1249
1250 IF NOT l_return OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1251 x_return_status := l_return_status;
1252 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1253 gme_debug.put_line ( g_pkg_name||'.'||l_api_name||' error returned from INV_LOT_API_PUB.validate_quantities with return status='||l_return_status);
1254 gme_debug.put_line ( g_pkg_name||'.'||l_api_name||' l_msg_count='||l_msg_count);
1255 gme_debug.put_line ( g_pkg_name||'.'||l_api_name||' l_msg_data='||l_msg_data);
1256 END IF;
1257 RAISE error_val_qties;
1258 END IF;
1259
1260 IF p_dtl_qty IS NULL THEN
1261 p_dtl_qty := INV_CONVERT.inv_um_convert
1262 (item_id => p_matl_dtl_rec.inventory_item_id
1263 ,lot_number => p_lot_number
1264 ,organization_id => p_matl_dtl_rec.organization_id
1265 ,precision => gme_common_pvt.g_precision
1266 ,from_quantity => l_primary_lot_qty
1267 ,from_unit => l_primary_uom_code
1268 ,to_unit => p_matl_dtl_rec.dtl_um
1269 ,from_name => NULL
1270 ,to_name => NULL);
1271
1272 IF p_dtl_qty = -99999 THEN
1273 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1274 gme_debug.put_line
1275 ( g_pkg_name
1276 || '.'
1277 || l_api_name
1278 || ' qty conversion failed for material detail'
1279 || p_matl_dtl_rec.material_detail_id);
1280 END IF;
1281 RAISE error_um_conv;
1282 END IF;
1283 END IF;
1284
1285 IF g_debug <= gme_debug.g_log_procedure THEN
1286 gme_debug.put_line('Exiting function '||g_pkg_name||'.'||l_api_name);
1287 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_dtl_qty='||p_dtl_qty);
1288 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_sec_qty='||p_sec_qty);
1289 END IF;
1290
1291 RETURN l_return;
1292
1293 EXCEPTION
1294 WHEN error_val_qties THEN
1295 RETURN FALSE;
1296 WHEN error_um_conv THEN
1297 FND_MESSAGE.SET_NAME('INV','INV_NO_CONVERSION_ERR');
1298 FND_MESSAGE.SET_TOKEN('PGM_NAME',g_pkg_name||'.'||l_api_name);
1299 fnd_msg_pub.ADD;
1300 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1301 RETURN FALSE;
1302 WHEN OTHERS THEN
1303 IF g_debug <= gme_debug.g_log_unexpected THEN
1304 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
1305 END IF;
1306 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1307 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1308 RETURN FALSE;
1309 END validate_quantities;
1310
1311 FUNCTION validate_lot_number (p_inv_item_id IN NUMBER
1312 ,p_org_id IN NUMBER
1313 ,p_lot_number IN VARCHAR2
1314 ,x_return_status OUT NOCOPY VARCHAR2)
1315
1316 RETURN BOOLEAN IS
1317 CURSOR check_lot_exists(v_item_id NUMBER
1318 ,v_org_id NUMBER
1319 ,v_lot_no VARCHAR2) IS
1320 SELECT count( 1 )
1321 FROM mtl_lot_numbers
1322 WHERE inventory_item_id = v_item_id
1323 AND organization_id = v_org_id
1324 AND lot_number = v_lot_no;
1325
1326
1327 l_api_name CONSTANT VARCHAR2 (30) := 'validate_lot_number';
1328 l_exists NUMBER;
1329 l_return BOOLEAN;
1330
1331 BEGIN
1332
1333 IF g_debug <= gme_debug.g_log_procedure THEN
1334 gme_debug.put_line('Entering function '||g_pkg_name||'.'||l_api_name);
1335 END IF;
1336 x_return_status := FND_API.G_RET_STS_SUCCESS;
1337
1338 OPEN check_lot_exists(p_inv_item_id, p_org_id, p_lot_number);
1339 FETCH check_lot_exists INTO l_exists;
1340 CLOSE check_lot_exists;
1341
1342 IF l_exists > 0 THEN
1343 l_return := TRUE;
1344 ELSE
1345 FND_MESSAGE.SET_NAME('INV','INV_LOT_NOT_EXISTS');
1346 FND_MESSAGE.SET_TOKEN('PGM_NAME',g_pkg_name||'.'||l_api_name);
1347 fnd_msg_pub.ADD;
1348 l_return := FALSE;
1349 END IF;
1350
1351 IF g_debug <= gme_debug.g_log_procedure THEN
1352 gme_debug.put_line('Exiting function '||g_pkg_name||'.'||l_api_name);
1353 END IF;
1354
1355 RETURN l_return;
1356
1357 EXCEPTION
1358 WHEN OTHERS THEN
1359 IF g_debug <= gme_debug.g_log_unexpected THEN
1360 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
1361 END IF;
1362 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1364 RETURN FALSE;
1365 END validate_lot_number;
1366
1367 FUNCTION validate_sequence (p_matl_dtl_rec IN gme_material_details%ROWTYPE
1368 ,p_sequence IN NUMBER
1369 ,x_return_status OUT NOCOPY VARCHAR2)
1370 RETURN BOOLEAN IS
1371
1372
1373 l_api_name CONSTANT VARCHAR2 (30) := 'validate_sequence';
1374
1375 CURSOR cur_is_sequence (v_matl_dtl_id NUMBER, v_sequ NUMBER) IS
1376 SELECT 1
1377 FROM gme_pending_product_lots
1378 WHERE material_detail_id = v_matl_dtl_id
1379 AND sequence = v_sequ;
1380
1381 l_return BOOLEAN;
1382 l_is_sequ NUMBER := 0;
1383
1384 error_validation EXCEPTION;
1385
1386 BEGIN
1387
1388 IF g_debug <= gme_debug.g_log_procedure THEN
1389 gme_debug.put_line('Entering function '||g_pkg_name||'.'||l_api_name);
1390 END IF;
1391 x_return_status := FND_API.G_RET_STS_SUCCESS;
1392
1393 IF p_sequence IS NULL THEN
1394 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
1395 ,'FIELD_NAME'
1396 ,'SEQUENCE');
1397 RAISE error_validation;
1398 END IF;
1399
1400 OPEN cur_is_sequence(p_matl_dtl_rec.material_detail_id, p_sequence);
1401 FETCH cur_is_sequence INTO l_is_sequ;
1402 CLOSE cur_is_sequence;
1403
1404 IF l_is_sequ = 1 THEN
1405 gme_common_pvt.log_message ('GME_SEQUENCE_DUP');
1406 l_return := FALSE;
1407 ELSE
1408 l_return := TRUE;
1409 END IF;
1410
1411 IF g_debug <= gme_debug.g_log_procedure THEN
1412 gme_debug.put_line('Exiting function '||g_pkg_name||'.'||l_api_name);
1413 END IF;
1414
1415 RETURN l_return;
1416
1417 EXCEPTION
1418 WHEN error_validation THEN
1419 return FALSE;
1420 WHEN OTHERS THEN
1421 IF g_debug <= gme_debug.g_log_unexpected THEN
1422 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
1423 END IF;
1424 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1425 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1426 RETURN FALSE;
1427 END validate_sequence;
1428
1429 FUNCTION validate_revision (p_item_rec IN mtl_system_items_b%ROWTYPE
1430 ,p_revision IN VARCHAR2
1431 ,x_return_status OUT NOCOPY VARCHAR2)
1432 RETURN BOOLEAN IS
1433
1434 l_api_name CONSTANT VARCHAR2 (30) := 'validate_revision';
1435
1436 l_return BOOLEAN;
1437
1438 error_get_item EXCEPTION;
1439
1440
1441 BEGIN
1442
1443 IF g_debug <= gme_debug.g_log_procedure THEN
1444 gme_debug.put_line('Entering function '||g_pkg_name||'.'||l_api_name);
1445 END IF;
1446 x_return_status := FND_API.G_RET_STS_SUCCESS;
1447 /* Bug 4866553 Corrected API call */
1448 gme_material_detail_pvt.validate_revision
1449 (p_revision => p_revision
1450 ,p_item_rec => p_item_rec
1451 ,x_return_status => x_return_status);
1452
1453 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1454 -- error message set in gme_material_detail_pvt.validate_revision
1455 l_return := FALSE;
1456 ELSE
1457 l_return := TRUE;
1458 END IF;
1459
1460 IF g_debug <= gme_debug.g_log_procedure THEN
1461 gme_debug.put_line('Exiting function '||g_pkg_name||'.'||l_api_name);
1462 END IF;
1463
1464 RETURN l_return;
1465
1466 EXCEPTION
1467 WHEN OTHERS THEN
1468 IF g_debug <= gme_debug.g_log_unexpected THEN
1469 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
1470 END IF;
1471 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1472 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1473 RETURN FALSE;
1474 END validate_revision;
1475
1476 FUNCTION validate_reason_id(p_reason_id IN NUMBER
1477 ,x_return_status OUT NOCOPY VARCHAR2)
1478 RETURN BOOLEAN IS
1479 l_api_name CONSTANT VARCHAR2 (30) := 'validate_reason_id';
1480
1481 l_is_reason NUMBER;
1482 l_return BOOLEAN;
1483
1484 CURSOR cur_is_reason (v_reason_id NUMBER) IS
1485 SELECT count(1)
1486 FROM mtl_transaction_reasons
1487 WHERE reason_id = v_reason_id
1488 AND NVL (disable_date, SYSDATE + 1) > SYSDATE;
1489
1490 BEGIN
1491 IF g_debug <= gme_debug.g_log_procedure THEN
1492 gme_debug.put_line('Entering function '||g_pkg_name||'.'||l_api_name);
1493 END IF;
1494 x_return_status := FND_API.G_RET_STS_SUCCESS;
1495
1496 IF p_reason_id IS NULL THEN
1497 -- NULL is valid...
1498 return TRUE;
1499 END IF;
1500
1501 OPEN cur_is_reason (p_reason_id);
1502 FETCH cur_is_reason INTO l_is_reason;
1503 CLOSE cur_is_reason;
1504
1505 IF l_is_reason = 0 THEN
1506 FND_MESSAGE.SET_NAME('INV','INV_INT_REACODE');
1507 FND_MESSAGE.SET_TOKEN('PGM_NAME',g_pkg_name||'.'||l_api_name);
1508 fnd_msg_pub.ADD;
1509 l_return := FALSE;
1510 ELSE
1511 l_return := TRUE;
1512 END IF;
1513
1514 IF g_debug <= gme_debug.g_log_procedure THEN
1515 gme_debug.put_line('Exiting function '||g_pkg_name||'.'||l_api_name);
1516 END IF;
1517
1518 RETURN l_return;
1519 EXCEPTION
1520 WHEN OTHERS THEN
1521 IF g_debug <= gme_debug.g_log_unexpected THEN
1522 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
1523 END IF;
1524 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1526 RETURN FALSE;
1527 END validate_reason_id;
1528
1529 FUNCTION pending_product_lot_exist
1530 (p_batch_id IN NUMBER
1531 ,p_material_detail_id IN NUMBER)
1532 RETURN BOOLEAN IS
1533 l_api_name CONSTANT VARCHAR2 (30) := 'pending_product_lot_exist';
1534
1535 l_return BOOLEAN;
1536 l_is_pplot NUMBER;
1537
1538 CURSOR cur_pp_lot_exist (v_batch_id NUMBER, v_matl_dtl_id NUMBER) IS
1539 SELECT 1
1540 FROM gme_pending_product_lots
1541 WHERE batch_id = v_batch_id
1542 AND material_detail_id = v_matl_dtl_id
1543 AND quantity <> 0
1544 AND rownum = 1;
1545
1546 BEGIN
1547 IF g_debug <= gme_debug.g_log_procedure THEN
1548 gme_debug.put_line('Entering function '||g_pkg_name||'.'||l_api_name);
1549 END IF;
1550
1551 OPEN cur_pp_lot_exist (p_batch_id, p_material_detail_id);
1552 FETCH cur_pp_lot_exist INTO l_is_pplot;
1553 CLOSE cur_pp_lot_exist;
1554
1555 IF l_is_pplot = 1 THEN
1556 l_return := TRUE;
1557 ELSE
1558 l_return := FALSE;
1559 END IF;
1560
1561 IF g_debug <= gme_debug.g_log_procedure THEN
1562 gme_debug.put_line('Exiting function '||g_pkg_name||'.'||l_api_name);
1563 END IF;
1564
1565 RETURN l_return;
1566 EXCEPTION
1567 WHEN OTHERS THEN
1568 IF g_debug <= gme_debug.g_log_unexpected THEN
1569 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
1570 END IF;
1571 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1572 RETURN FALSE;
1573 END pending_product_lot_exist;
1574
1575 -- nsinghi bug#5689035. Added this procedure.
1576 PROCEDURE get_pnd_prod_lot_qty (
1577 p_mtl_dtl_id IN NUMBER
1578 ,x_pnd_prod_lot_qty OUT NOCOPY NUMBER
1579 ,x_return_status OUT NOCOPY VARCHAR2)
1580 IS
1581 l_api_name CONSTANT VARCHAR2 (30) := 'get_pnd_prod_lot_qty';
1582 l_pnd_prod_lot_tbl gme_common_pvt.pending_lots_tab;
1583 get_pending_lot_error EXCEPTION;
1584
1585 BEGIN
1586 IF g_debug <= gme_debug.g_log_procedure THEN
1587 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1588 || l_api_name);
1589 END IF;
1590
1591 x_return_status := fnd_api.g_ret_sts_success;
1592 x_pnd_prod_lot_qty := 0;
1593
1594 get_pending_lot(p_material_detail_id => p_mtl_dtl_id
1595 ,x_return_status => x_return_status
1596 ,x_pending_product_lot_tbl => l_pnd_prod_lot_tbl);
1597
1598 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1599 RAISE get_pending_lot_error;
1600 END IF;
1601
1602 FOR i IN 1 .. l_pnd_prod_lot_tbl.COUNT LOOP
1603 x_pnd_prod_lot_qty := x_pnd_prod_lot_qty + l_pnd_prod_lot_tbl(i).quantity;
1604 END LOOP;
1605
1606 IF g_debug <= gme_debug.g_log_procedure THEN
1607 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1608 END IF;
1609 EXCEPTION
1610 WHEN get_pending_lot_error THEN
1611 x_return_status := fnd_api.g_ret_sts_unexp_error;
1612 WHEN OTHERS THEN
1613 IF g_debug <= gme_debug.g_log_unexpected THEN
1614 gme_debug.put_line ( 'When others exception in '
1615 || g_pkg_name
1616 || '.'
1617 || l_api_name
1618 || ' Error is '
1619 || SQLERRM);
1620 END IF;
1621
1622 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1623 x_return_status := fnd_api.g_ret_sts_unexp_error;
1624 END get_pnd_prod_lot_qty;
1625
1626 END gme_pending_product_lots_pvt;