[Home] [Help]
PACKAGE BODY: APPS.GME_LAB_BATCH_LOTS_DBL
Source
1 PACKAGE BODY gme_lab_batch_lots_dbl AS
2 /* $Header: GMEVGLBB.pls 120.2 2006/02/03 07:23:27 svgonugu noship $ */
3
4 /* Global Variables */
5 g_table_name VARCHAR2 (80) DEFAULT 'GME_LAB_BATCH_LOTS_DBL';
6 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
7
8 /*============================================================================
9 | Copyright (c) 2001 Oracle Corporation
10 | TVP, Reading
11 | All rights reserved
12 =============================================================================
13 | FILENAME
14 | GMEVGLBB.pls
15 |
16 | DESCRIPTION
17 | This procedure is user to manipulate the GME_LAB_BATCH_LOTS table.
18 |
19 |
20 |
21 | NOTES
22 |
23 | HISTORY
24 | 12-MAR-01 Thomas Daniel Created
25 |
26 | - create_row
27 | - fetch_row
28 | - update_row
29 | - lock_row
30 |
31 |
32 =============================================================================
33 */
34
35 /* Api start of comments
36 +============================================================================
37 | FUNCTION NAME
38 | insert_row
39 |
40 | TYPE
41 | Private
42 | USAGE
43 | Insert_Row will insert a row in gme_lab_batch_lots
44 |
45 |
46 | DESCRIPTION
47 | Insert_Row will insert a row in gme_lab_batch_lots
48 |
49 |
50 |
51 | PARAMETERS
52 | p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE
53 | x_lab_batch_lots IN OUT NOCOPY gme_lab_batch_lots%ROWTYPE
54 |
55 | RETURNS
56 | BOOLEAN
57 | HISTORY
58 | 12-MAR-01 Thomas Daniel Created
59 |
60 |
61 |
62 +=============================================================================
63 Api end of comments
64 */
65 FUNCTION insert_row (
66 p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE
67 ,x_lab_batch_lots IN OUT NOCOPY gme_lab_batch_lots%ROWTYPE)
68 RETURN BOOLEAN
69 IS
70 BEGIN
71 x_lab_batch_lots := p_lab_batch_lots;
72
73 INSERT INTO gme_lab_batch_lots
74 (batch_id
75 ,material_detail_id
76 ,item_id, lot_id
77 ,qty, qty2
78 ,uom, uom2
79 ,creation_date, created_by
80 ,last_update_date, last_updated_by
81 ,last_update_login, attribute1
82 ,attribute2, attribute3
83 ,attribute4, attribute5
84 ,attribute6, attribute7
85 ,attribute8, attribute9
86 ,attribute10
87 ,attribute11
88 ,attribute12
89 ,attribute13
90 ,attribute14
91 ,attribute15
92 ,attribute16
93 ,attribute17
94 ,attribute18
95 ,attribute19
96 ,attribute20
97 ,attribute21
98 ,attribute22
99 ,attribute23
100 ,attribute24
101 ,attribute25
102 ,attribute26
103 ,attribute27
104 ,attribute28
105 ,attribute29
106 ,attribute30
107 ,attribute_category)
108 VALUES (x_lab_batch_lots.batch_id
109 ,x_lab_batch_lots.material_detail_id
110 ,x_lab_batch_lots.item_id, x_lab_batch_lots.lot_id
111 ,x_lab_batch_lots.qty, x_lab_batch_lots.qty2
112 ,x_lab_batch_lots.uom, x_lab_batch_lots.uom2
113 ,gme_common_pvt.g_timestamp, gme_common_pvt.g_user_ident
114 ,gme_common_pvt.g_timestamp, gme_common_pvt.g_user_ident
115 ,gme_common_pvt.g_login_id, x_lab_batch_lots.attribute1
116 ,x_lab_batch_lots.attribute2, x_lab_batch_lots.attribute3
117 ,x_lab_batch_lots.attribute4, x_lab_batch_lots.attribute5
118 ,x_lab_batch_lots.attribute6, x_lab_batch_lots.attribute7
119 ,x_lab_batch_lots.attribute8, x_lab_batch_lots.attribute9
120 ,x_lab_batch_lots.attribute10
121 ,x_lab_batch_lots.attribute11
122 ,x_lab_batch_lots.attribute12
123 ,x_lab_batch_lots.attribute13
124 ,x_lab_batch_lots.attribute14
125 ,x_lab_batch_lots.attribute15
126 ,x_lab_batch_lots.attribute16
127 ,x_lab_batch_lots.attribute17
128 ,x_lab_batch_lots.attribute18
129 ,x_lab_batch_lots.attribute19
130 ,x_lab_batch_lots.attribute20
131 ,x_lab_batch_lots.attribute21
132 ,x_lab_batch_lots.attribute22
133 ,x_lab_batch_lots.attribute23
134 ,x_lab_batch_lots.attribute24
135 ,x_lab_batch_lots.attribute25
136 ,x_lab_batch_lots.attribute26
137 ,x_lab_batch_lots.attribute27
138 ,x_lab_batch_lots.attribute28
139 ,x_lab_batch_lots.attribute29
140 ,x_lab_batch_lots.attribute30
141 ,x_lab_batch_lots.attribute_category);
142
143 RETURN TRUE;
144 EXCEPTION
145 WHEN OTHERS THEN
146 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
147 ,SQLERRM);
148 RETURN FALSE;
149 END insert_row;
150
151 /* Api start of comments
152 +============================================================================
153 | FUNCTION NAME
154 | fetch_row
155 |
156 | TYPE
157 | Private
158 | USAGE
159 | Fetch_Row will fetch a row in gme_lab_batch_lots
160 |
161 |
162 | DESCRIPTION
163 | Fetch_Row will fetch a row in gme_lab_batch_lots
164 |
165 |
166 |
167 | PARAMETERS
168 | p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE
169 | x_lab_batch_lots IN OUT NOCOPY gme_lab_batch_lots%ROWTYPE
170 |
171 | RETURNS
172 | BOOLEAN
173 | HISTORY
174 | 12-MAR-01 Thomas Daniel Created
175 |
176 |
177 |
178 +=============================================================================
179 Api end of comments
180 */
181 FUNCTION fetch_row (
182 p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE
183 ,x_lab_batch_lots IN OUT NOCOPY gme_lab_batch_lots%ROWTYPE)
184 RETURN BOOLEAN
185 IS
186 BEGIN
187 IF p_lab_batch_lots.batch_id IS NOT NULL
188 AND p_lab_batch_lots.material_detail_id IS NOT NULL
189 AND p_lab_batch_lots.item_id IS NOT NULL
190 AND p_lab_batch_lots.lot_id IS NOT NULL THEN
191 SELECT batch_id
192 ,material_detail_id
193 ,item_id, lot_id
194 ,qty, qty2
195 ,uom, uom2
196 ,creation_date, created_by
197 ,last_update_date
198 ,last_updated_by
199 ,last_update_login
200 ,attribute1, attribute2
201 ,attribute3, attribute4
202 ,attribute5, attribute6
203 ,attribute7, attribute8
204 ,attribute9, attribute10
205 ,attribute11, attribute12
206 ,attribute13, attribute14
207 ,attribute15, attribute16
208 ,attribute17, attribute18
209 ,attribute19, attribute20
210 ,attribute21, attribute22
211 ,attribute23, attribute24
212 ,attribute25, attribute26
213 ,attribute27, attribute28
214 ,attribute29, attribute30
215 ,attribute_category
216 INTO x_lab_batch_lots.batch_id
217 ,x_lab_batch_lots.material_detail_id
218 ,x_lab_batch_lots.item_id, x_lab_batch_lots.lot_id
219 ,x_lab_batch_lots.qty, x_lab_batch_lots.qty2
220 ,x_lab_batch_lots.uom, x_lab_batch_lots.uom2
221 ,x_lab_batch_lots.creation_date, x_lab_batch_lots.created_by
222 ,x_lab_batch_lots.last_update_date
223 ,x_lab_batch_lots.last_updated_by
224 ,x_lab_batch_lots.last_update_login
225 ,x_lab_batch_lots.attribute1, x_lab_batch_lots.attribute2
226 ,x_lab_batch_lots.attribute3, x_lab_batch_lots.attribute4
227 ,x_lab_batch_lots.attribute5, x_lab_batch_lots.attribute6
228 ,x_lab_batch_lots.attribute7, x_lab_batch_lots.attribute8
229 ,x_lab_batch_lots.attribute9, x_lab_batch_lots.attribute10
230 ,x_lab_batch_lots.attribute11, x_lab_batch_lots.attribute12
231 ,x_lab_batch_lots.attribute13, x_lab_batch_lots.attribute14
232 ,x_lab_batch_lots.attribute15, x_lab_batch_lots.attribute16
233 ,x_lab_batch_lots.attribute17, x_lab_batch_lots.attribute18
234 ,x_lab_batch_lots.attribute19, x_lab_batch_lots.attribute20
235 ,x_lab_batch_lots.attribute21, x_lab_batch_lots.attribute22
236 ,x_lab_batch_lots.attribute23, x_lab_batch_lots.attribute24
237 ,x_lab_batch_lots.attribute25, x_lab_batch_lots.attribute26
238 ,x_lab_batch_lots.attribute27, x_lab_batch_lots.attribute28
239 ,x_lab_batch_lots.attribute29, x_lab_batch_lots.attribute30
240 ,x_lab_batch_lots.attribute_category
241 FROM gme_lab_batch_lots
242 WHERE batch_id = p_lab_batch_lots.batch_id
243 AND material_detail_id = p_lab_batch_lots.material_detail_id
244 AND item_id = p_lab_batch_lots.item_id
245 AND lot_id = p_lab_batch_lots.lot_id;
246 ELSE
247 gme_common_pvt.log_message ('GME_NO_KEYS'
248 ,'TABLE_NAME'
249 ,g_table_name);
250 RETURN FALSE;
251 END IF;
252
253 RETURN TRUE;
254 EXCEPTION
255 WHEN NO_DATA_FOUND THEN
256 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
257 ,'TABLE_NAME'
258 ,g_table_name);
259 RETURN FALSE;
260 WHEN OTHERS THEN
261 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
262 ,SQLERRM);
263 RETURN FALSE;
264 END fetch_row;
265
266 /* Api start of comments
267 +============================================================================
268 | FUNCTION NAME
269 | delete_row
270 |
271 | TYPE
272 | Private
273 | USAGE
274 | Delete_Row will delete a row in gme_lab_batch_lots
275 |
276 |
277 | DESCRIPTION
278 | Delete_Row will delete a row in gme_lab_batch_lots
279 |
280 |
281 |
282 | PARAMETERS
283 | p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE
284 |
285 | RETURNS
286 | BOOLEAN
287 | HISTORY
288 | 12-MAR-01 Thomas Daniel Created
289 | 26-AUG-02 Bharati Satpute 2404126
290 | Added error message 'GME_RECORD_CHANGED'
291 |
292 |
293 +=============================================================================
294 Api end of comments
295 */
296 FUNCTION delete_row (p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE)
297 RETURN BOOLEAN
298 IS
299 l_dummy NUMBER (5) := 0;
300 locked_by_other_user EXCEPTION;
301 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
302 BEGIN
303 IF p_lab_batch_lots.batch_id IS NOT NULL
304 AND p_lab_batch_lots.material_detail_id IS NOT NULL
305 AND p_lab_batch_lots.item_id IS NOT NULL
306 AND p_lab_batch_lots.lot_id IS NOT NULL THEN
307 SELECT 1
308 INTO l_dummy
309 FROM gme_lab_batch_lots
310 WHERE batch_id = p_lab_batch_lots.batch_id
311 AND material_detail_id = p_lab_batch_lots.material_detail_id
312 AND item_id = p_lab_batch_lots.item_id
313 AND lot_id = p_lab_batch_lots.lot_id
314 FOR UPDATE NOWAIT;
315
316 DELETE FROM gme_lab_batch_lots
317 WHERE batch_id = p_lab_batch_lots.batch_id
318 AND material_detail_id = p_lab_batch_lots.material_detail_id
319 AND item_id = p_lab_batch_lots.item_id
320 AND lot_id = p_lab_batch_lots.lot_id;
321 ELSE
322 gme_common_pvt.log_message ('GME_NO_KEYS'
323 ,'TABLE_NAME'
324 ,g_table_name);
325 RETURN FALSE;
326 END IF;
327
328 IF SQL%FOUND THEN
329 RETURN TRUE;
330 ELSE
331 IF l_dummy = 0 THEN
332 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
333 ,'TABLE_NAME'
334 ,g_table_name);
335 ELSE
336 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
337 ,'TABLE_NAME'
338 ,g_table_name);
339 END IF;
340
341 RETURN FALSE;
342 END IF;
343 EXCEPTION
344 WHEN NO_DATA_FOUND THEN
345 IF l_dummy = 0 THEN
346 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
347 ,'TABLE_NAME'
348 ,g_table_name);
349 ELSE
350 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
351 ,'TABLE_NAME'
352 ,g_table_name);
353 END IF;
354
355 RETURN FALSE;
356 WHEN locked_by_other_user THEN
357 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
358 ,'TABLE_NAME'
359 ,g_table_name
360 ,'RECORD'
361 ,'Batch'
362 ,'KEY'
363 ,TO_CHAR (p_lab_batch_lots.batch_id) );
364 RETURN FALSE;
365 WHEN OTHERS THEN
366 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
367 ,SQLERRM);
368 RETURN FALSE;
369 END delete_row;
370
371 /* Api start of comments
372 +============================================================================
373 | FUNCTION NAME
374 | update_row
375 |
376 | TYPE
377 | Private
378 | USAGE
379 | Update_Row will update a row in gme_lab_batch_lots
380 |
381 |
382 | DESCRIPTION
383 | Update_Row will update a row in gme_lab_batch_lots
384 |
385 |
386 |
387 | PARAMETERS
388 | p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE
389 |
390 | RETURNS
391 | BOOLEAN
392 | HISTORY
393 | 12-MAR-01 Thomas Daniel Created
394 | 26-AUG-02 Bharati Satpute 2404126
395 | Added error message 'GME_RECORD_CHANGED'
396 |
397 +=============================================================================
398 Api end of comments
399 */
400 FUNCTION update_row (p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE)
401 RETURN BOOLEAN
402 IS
403 l_dummy NUMBER := 0;
404 locked_by_other_user EXCEPTION;
405 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
406 BEGIN
407 IF p_lab_batch_lots.batch_id IS NOT NULL
408 AND p_lab_batch_lots.material_detail_id IS NOT NULL
409 AND p_lab_batch_lots.item_id IS NOT NULL
410 AND p_lab_batch_lots.lot_id IS NOT NULL THEN
411 SELECT 1
412 INTO l_dummy
413 FROM gme_lab_batch_lots
414 WHERE batch_id = p_lab_batch_lots.batch_id
415 AND material_detail_id = p_lab_batch_lots.material_detail_id
416 AND item_id = p_lab_batch_lots.item_id
417 AND lot_id = p_lab_batch_lots.lot_id
418 FOR UPDATE NOWAIT;
419
420 UPDATE gme_lab_batch_lots
421 SET qty = p_lab_batch_lots.qty
422 ,qty2 = p_lab_batch_lots.qty2
423 ,uom = p_lab_batch_lots.uom
424 ,uom2 = p_lab_batch_lots.uom2
425 ,last_update_date = gme_common_pvt.g_timestamp
426 ,last_updated_by = gme_common_pvt.g_user_ident
427 ,last_update_login = gme_common_pvt.g_login_id
428 ,attribute1 = p_lab_batch_lots.attribute1
429 ,attribute2 = p_lab_batch_lots.attribute2
430 ,attribute3 = p_lab_batch_lots.attribute3
431 ,attribute4 = p_lab_batch_lots.attribute4
432 ,attribute5 = p_lab_batch_lots.attribute5
433 ,attribute6 = p_lab_batch_lots.attribute6
434 ,attribute7 = p_lab_batch_lots.attribute7
435 ,attribute8 = p_lab_batch_lots.attribute8
436 ,attribute9 = p_lab_batch_lots.attribute9
437 ,attribute10 = p_lab_batch_lots.attribute10
438 ,attribute11 = p_lab_batch_lots.attribute11
439 ,attribute12 = p_lab_batch_lots.attribute12
440 ,attribute13 = p_lab_batch_lots.attribute13
441 ,attribute14 = p_lab_batch_lots.attribute14
442 ,attribute15 = p_lab_batch_lots.attribute15
443 ,attribute16 = p_lab_batch_lots.attribute16
444 ,attribute17 = p_lab_batch_lots.attribute17
445 ,attribute18 = p_lab_batch_lots.attribute18
446 ,attribute19 = p_lab_batch_lots.attribute19
447 ,attribute20 = p_lab_batch_lots.attribute20
448 ,attribute21 = p_lab_batch_lots.attribute21
449 ,attribute22 = p_lab_batch_lots.attribute22
450 ,attribute23 = p_lab_batch_lots.attribute23
451 ,attribute24 = p_lab_batch_lots.attribute24
452 ,attribute25 = p_lab_batch_lots.attribute25
453 ,attribute26 = p_lab_batch_lots.attribute26
454 ,attribute27 = p_lab_batch_lots.attribute27
455 ,attribute28 = p_lab_batch_lots.attribute28
456 ,attribute29 = p_lab_batch_lots.attribute29
457 ,attribute30 = p_lab_batch_lots.attribute30
458 ,attribute_category = p_lab_batch_lots.attribute_category
459 WHERE batch_id = p_lab_batch_lots.batch_id
460 AND material_detail_id = p_lab_batch_lots.material_detail_id
461 AND item_id = p_lab_batch_lots.item_id
462 AND lot_id = p_lab_batch_lots.lot_id
463 AND last_update_date = p_lab_batch_lots.last_update_date;
464 ELSE
465 gme_common_pvt.log_message ('GME_NO_KEYS'
466 ,'TABLE_NAME'
467 ,g_table_name);
468 RETURN FALSE;
469 END IF;
470
471 IF SQL%FOUND THEN
472 RETURN TRUE;
473 ELSE
474 IF l_dummy = 0 THEN
475 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
476 ,'TABLE_NAME'
477 ,g_table_name);
478 ELSE
479 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
480 ,'TABLE_NAME'
481 ,g_table_name);
482 END IF;
483
484 RETURN FALSE;
485 END IF;
486 EXCEPTION
487 WHEN NO_DATA_FOUND THEN
488 IF l_dummy = 0 THEN
489 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
490 ,'TABLE_NAME'
491 ,g_table_name);
492 ELSE
493 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
494 ,'TABLE_NAME'
495 ,g_table_name);
496 END IF;
497
498 RETURN FALSE;
499 WHEN locked_by_other_user THEN
500 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
501 ,'TABLE_NAME'
502 ,g_table_name
503 ,'RECORD'
504 ,'Batch'
505 ,'KEY'
506 ,TO_CHAR (p_lab_batch_lots.batch_id) );
507 RETURN FALSE;
508 WHEN OTHERS THEN
509 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
510 ,SQLERRM);
511 RETURN FALSE;
512 END update_row;
513
514 /* Api start of comments
515 +============================================================================
516 | FUNCTION NAME
517 | lock_row
518 |
519 | TYPE
520 | Private
521 | USAGE
522 | Lock_Row will lock a row in gme_lab_batch_lots
523 |
524 |
525 | DESCRIPTION
526 | Lock_Row will lock a row in gme_lab_batch_lots
527 |
528 |
529 |
530 | PARAMETERS
531 | p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE
532 |
533 | RETURNS
534 | BOOLEAN
535 | HISTORY
536 | 12-MAR-01 Thomas Daniel Created
537 |
538 |
539 |
540 +=============================================================================
541 Api end of comments
542 */
543 FUNCTION lock_row (p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE)
544 RETURN BOOLEAN
545 IS
546 l_dummy NUMBER;
547 BEGIN
548 IF p_lab_batch_lots.batch_id IS NOT NULL
549 AND p_lab_batch_lots.material_detail_id IS NOT NULL
550 AND p_lab_batch_lots.item_id IS NOT NULL
551 AND p_lab_batch_lots.lot_id IS NOT NULL THEN
552 SELECT 1
553 INTO l_dummy
554 FROM gme_lab_batch_lots
555 WHERE batch_id = p_lab_batch_lots.batch_id
556 AND material_detail_id = p_lab_batch_lots.material_detail_id
557 AND item_id = p_lab_batch_lots.item_id
558 AND lot_id = p_lab_batch_lots.lot_id
559 FOR UPDATE NOWAIT;
560 END IF;
561
562 RETURN TRUE;
563 EXCEPTION
564 WHEN app_exception.record_lock_exception THEN
565 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
566 ,'TABLE_NAME'
567 ,g_table_name
568 ,'RECORD'
569 ,'Batch'
570 ,'KEY'
571 ,TO_CHAR (p_lab_batch_lots.batch_id) );
572 RETURN FALSE;
573 WHEN OTHERS THEN
574 RETURN FALSE;
575 END lock_row;
576
577 /* Api start of comments
578 +============================================================================
579 | FUNCTION NAME
580 | delete_lab_lots
581 |
582 | TYPE
583 | Private
584 | USAGE
585 | Delete_Lab_Lots will delete all the lot allocations.
586 |
587 |
588 | DESCRIPTION
589 | Delete_Lab_Lots will delete all the lot allocations either for a
590 | batch or for a material detail or for a combination of material
591 | detail and lot id.
592 |
593 | PARAMETERS
594 | p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE
595 | x_return_status IN OUT NOCOPY VARCHAR2
596 |
597 | HISTORY
598 | 13-NOV-01 Thomas Daniel Created
599 |
600 |
601 |
602 +=============================================================================
603 Api end of comments
604 */
605 PROCEDURE delete_lab_lots (
606 p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE
607 ,x_return_status IN OUT NOCOPY VARCHAR2)
608 IS
609 TYPE query_ref IS REF CURSOR;
610
611 get_lots query_ref;
612 l_where VARCHAR2 (2000);
613 l_cursor NUMBER (5);
614 l_lab_lots gme_lab_batch_lots%ROWTYPE;
615 BEGIN
616 /* Initialize API return status to sucess */
617 x_return_status := fnd_api.g_ret_sts_success;
618
619 -- Determine if any of the key values are present
620 IF ( (p_lab_batch_lots.lot_id IS NOT NULL)
621 AND (p_lab_batch_lots.material_detail_id IS NOT NULL) ) THEN
622 /*l_where :=
623 'material_detail_ID =:material_detail_id AND '
624 || 'lot_id = :lot_id'; */
625 l_cursor := 1;
626 ELSIF (p_lab_batch_lots.material_detail_id IS NOT NULL) THEN
627 --l_where := 'material_detail_id =:material_detail_id';
628 l_cursor := 2;
629 ELSIF (p_lab_batch_lots.batch_id IS NOT NULL) THEN
630 --l_where := 'BATCH_ID =:batch_id';
631 l_cursor := 3;
632 ELSE
633 gme_common_pvt.log_message ('GME_NO_KEYS'
634 ,'TABLE_NAME'
635 ,'GME_LAB_BATCH_LOTS');
636 RAISE fnd_api.g_exc_error;
637 END IF;
638
639 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
640 gme_debug.put_line ('Where Clause = ' || l_where);
641 END IF;
642
643 --FPBug#4998874 directly put the l_where value in the OPEN FOR statement.
644 IF l_cursor = 1 THEN
645 OPEN get_lots
646 FOR ' SELECT * FROM GME_LAB_BATCH_LOTS
647 WHERE material_detail_ID =:material_detail_id AND lot_id = :lot_id'
648 USING p_lab_batch_lots.material_detail_id, p_lab_batch_lots.lot_id;
649 ELSIF l_cursor = 2 THEN
650 OPEN get_lots
651 FOR ' SELECT * FROM GME_LAB_BATCH_LOTS
652 WHERE material_detail_id =:material_detail_id'
653 USING p_lab_batch_lots.material_detail_id;
654 ELSIF l_cursor = 3 THEN
655 OPEN get_lots
656 FOR ' SELECT * FROM GME_LAB_BATCH_LOTS
657 WHERE batch_id =:batch_id'
658 USING p_lab_batch_lots.batch_id;
659 END IF;
660
661 LOOP
662 FETCH get_lots
663 INTO l_lab_lots;
664
665 EXIT WHEN get_lots%NOTFOUND;
666
667 IF NOT gme_lab_batch_lots_dbl.delete_row
668 (p_lab_batch_lots => l_lab_lots) THEN
669 RAISE fnd_api.g_exc_error;
670 END IF;
671 END LOOP;
672
673 CLOSE get_lots;
674 EXCEPTION
675 WHEN fnd_api.g_exc_error THEN
676 x_return_status := fnd_api.g_ret_sts_error;
677 WHEN OTHERS THEN
678 x_return_status := fnd_api.g_ret_sts_unexp_error;
679 fnd_msg_pub.add_exc_msg ('GME_LAB_BATCH_LOTS_DBL'
680 ,'DELETE_LAB_LOTS');
681
682 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
683 gme_debug.put_line ('IN OTHERS ' || SQLERRM);
684 END IF;
685 END delete_lab_lots;
686 END gme_lab_batch_lots_dbl;