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