[Home] [Help]
PACKAGE BODY: APPS.GME_PENDING_PRODUCT_LOTS_DBL
Source
1 PACKAGE BODY gme_pending_product_lots_dbl AS
2 /* $Header: GMEVGPLB.pls 120.1 2006/05/03 12:00:07 creddy noship $ */
3
4 /* Global Variables */
5 g_table_name VARCHAR2 (80) DEFAULT 'GME_PENDING_PRODUCT_LOTS';
6 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
7 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_PENDING_PRODUCT_LOTS_DBL';
8
9 /*=========================================================================
10 | Copyright (c) 2001 Oracle Corporation |
11 | TVP, Reading, England |
12 | All rights reserved |
13 ===========================================================================
14 | FILENAME |
15 | GMEVGPLB.pls |
16 | |
17 | DESCRIPTION |
18 | |
19 | Body of package gme_pending_product_lots_dbl |
20 | |
21 | NOTES |
22 | |
23 | HISTORY |
24 | |
25 | June 2005 Created |
26 | |
27 | - insert_row |
28 | - fetch_row |
29 | - update_row |
30 | - delete_row |
31 | - lock_row |
32 | |
33 =========================================================================*/
34
35 /*==========================================================================
36 | FUNCTION NAME |
37 | insert_row |
38 | |
39 | TYPE |
40 | Private |
41 | |
42 | USAGE |
43 | insert_row will insert a row in gme_pending_product_lots |
44 | |
45 | DESCRIPTION |
46 | insert_row will insert a row in gme_pending_product_lots |
47 | |
48 | PARAMETERS |
49 | p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE |
50 | x_pending_product_lots_rec IN OUT NOCOPY gme_pending_product_lots%ROWTYPE |
51 | |
52 | RETURNS |
53 | BOOLEAN |
54 | |
55 | HISTORY |
56 | |
57 ==========================================================================*/
58 FUNCTION insert_row
59 (p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
60 ,x_pending_product_lots_rec IN OUT NOCOPY gme_pending_product_lots%ROWTYPE) RETURN BOOLEAN IS
61
62 l_api_name CONSTANT VARCHAR2 (30) := 'insert_row';
63
64 BEGIN
65
66 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
67 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
68 END IF;
69
70 x_pending_product_lots_rec := p_pending_product_lots_rec;
71
72 x_pending_product_lots_rec.last_update_date := gme_common_pvt.g_timestamp;
73 x_pending_product_lots_rec.last_updated_by := gme_common_pvt.g_user_ident;
74 x_pending_product_lots_rec.last_update_login := gme_common_pvt.g_login_id;
75
76 x_pending_product_lots_rec.creation_date := gme_common_pvt.g_timestamp;
77 x_pending_product_lots_rec.created_by := gme_common_pvt.g_user_ident;
78
79 SELECT apps.gme_pending_product_lots_s.nextval
80 INTO x_pending_product_lots_rec.PENDING_PRODUCT_LOT_ID
81 FROM sys.dual;
82
83 INSERT INTO gme_pending_product_lots
84 (PENDING_PRODUCT_LOT_ID
85 ,SEQUENCE
86 ,BATCH_ID
87 ,MATERIAL_DETAIL_ID
88 ,REVISION
89 ,LOT_NUMBER
90 ,QUANTITY
91 ,SECONDARY_QUANTITY
92 ,REASON_ID
93 ,CREATION_DATE
94 ,CREATED_BY
95 ,LAST_UPDATE_DATE
96 ,LAST_UPDATED_BY
97 ,LAST_UPDATE_LOGIN)
98 VALUES (x_pending_product_lots_rec.PENDING_PRODUCT_LOT_ID
99 ,x_pending_product_lots_rec.SEQUENCE
100 ,x_pending_product_lots_rec.BATCH_ID
101 ,x_pending_product_lots_rec.MATERIAL_DETAIL_ID
102 ,x_pending_product_lots_rec.REVISION
103 ,x_pending_product_lots_rec.LOT_NUMBER
104 ,x_pending_product_lots_rec.QUANTITY
105 ,x_pending_product_lots_rec.SECONDARY_QUANTITY
106 ,x_pending_product_lots_rec.REASON_ID
107 ,x_pending_product_lots_rec.CREATION_DATE
108 ,x_pending_product_lots_rec.CREATED_BY
109 ,x_pending_product_lots_rec.LAST_UPDATE_DATE
110 ,x_pending_product_lots_rec.LAST_UPDATED_BY
111 ,x_pending_product_lots_rec.LAST_UPDATE_LOGIN);
112
113 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
114 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
115 END IF;
116
117 IF SQL%FOUND THEN
118 RETURN TRUE;
119 ELSE
120 x_pending_product_lots_rec.PENDING_PRODUCT_LOT_ID := NULL;
121 RETURN FALSE;
122 END IF;
123
124 EXCEPTION
125 WHEN OTHERS THEN
126 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
127 IF g_debug <= gme_debug.g_log_procedure THEN
128 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
129 END IF;
130
131 x_pending_product_lots_rec.PENDING_PRODUCT_LOT_ID := NULL;
132
133 RETURN FALSE;
134 END insert_row;
135
136 /*==========================================================================
137 | FUNCTION NAME |
138 | fetch_row |
139 | |
140 | TYPE |
141 | Private |
142 | |
143 | USAGE |
144 | fetch_row will fetch a row in gme_pending_product_lots |
145 | |
146 | DESCRIPTION |
147 | fetch_row will fetch a row in gme_pending_product_lots |
148 | |
149 | PARAMETERS |
150 | p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE |
151 | x_pending_product_lots_rec IN OUT NOCOPY gme_pending_product_lots%ROWTYPE |
152 | |
153 | RETURNS |
154 | BOOLEAN |
155 | |
156 | HISTORY |
157 | |
158 ==========================================================================*/
159
160 FUNCTION fetch_row
161 (p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
162 ,x_pending_product_lots_rec IN OUT NOCOPY gme_pending_product_lots%ROWTYPE) RETURN BOOLEAN IS
163
164 l_pp_lot_id NUMBER;
165 l_matl_dtl_id NUMBER;
166 l_sequ NUMBER;
167
168 l_api_name CONSTANT VARCHAR2 (30) := 'fetch_row';
169 BEGIN
170
171 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
172 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
173 END IF;
174
175 l_pp_lot_id := p_pending_product_lots_rec.pending_product_lot_id;
176 l_matl_dtl_id := p_pending_product_lots_rec.material_detail_id;
177 l_sequ := p_pending_product_lots_rec.sequence;
178
179 IF (l_pp_lot_id IS NOT NULL) THEN
180 SELECT *
181 INTO x_pending_product_lots_rec
182 FROM gme_pending_product_lots
183 WHERE pending_product_lot_id = l_pp_lot_id;
184 ELSIF (l_matl_dtl_id IS NOT NULL) AND
185 (l_sequ IS NOT NULL) THEN
186 SELECT *
187 INTO x_pending_product_lots_rec
188 FROM gme_pending_product_lots
189 WHERE material_detail_id = l_matl_dtl_id
190 AND sequence = l_sequ;
191 ELSE
192 gme_common_pvt.log_message ('GME_NO_KEYS'
193 ,'TABLE_NAME'
194 ,g_table_name);
195 x_pending_product_lots_rec.PENDING_PRODUCT_LOT_ID := NULL;
196 RETURN FALSE;
197 END IF;
198
199 IF g_debug <= gme_debug.g_log_procedure THEN
200 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
201 END IF;
202
203 RETURN TRUE;
204
205 EXCEPTION
206 WHEN NO_DATA_FOUND THEN
207 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
208 ,'TABLE_NAME'
209 ,g_table_name);
210 x_pending_product_lots_rec.PENDING_PRODUCT_LOT_ID := NULL;
211 RETURN FALSE;
212 WHEN OTHERS THEN
213 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
214 IF g_debug <= gme_debug.g_log_procedure THEN
215 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
216 END IF;
217
218 x_pending_product_lots_rec.PENDING_PRODUCT_LOT_ID := NULL;
219
220 RETURN FALSE;
221 END fetch_row;
222
223 /*==========================================================================
224 | FUNCTION NAME |
225 | delete_row |
226 | |
227 | TYPE |
228 | Private |
229 | |
230 | USAGE |
231 | delete_row will delete a row in gme_pending_product_lots |
232 | |
233 | DESCRIPTION |
234 | delete_row will delete a row in gme_pending_product_lots |
235 | |
236 | PARAMETERS |
237 | p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE |
238 | |
239 | RETURNS |
240 | BOOLEAN |
241 | |
242 | HISTORY |
243 | |
244 ==========================================================================*/
245
246 FUNCTION delete_row (p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE) RETURN BOOLEAN IS
247
248 l_api_name CONSTANT VARCHAR2 (30) := 'delete_row';
249
250 locked_by_other_user EXCEPTION;
251 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
252
253 l_dummy NUMBER (5) := 0;
254 l_pp_lot_id NUMBER;
255 l_matl_dtl_id NUMBER;
256 l_sequ NUMBER;
257
258 BEGIN
259
260 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
261 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
262 END IF;
263
264 l_pp_lot_id := p_pending_product_lots_rec.pending_product_lot_id;
265 l_matl_dtl_id := p_pending_product_lots_rec.material_detail_id;
266 l_sequ := p_pending_product_lots_rec.sequence;
267
268 IF (l_pp_lot_id IS NOT NULL) THEN
269 SELECT 1
270 INTO l_dummy
271 FROM gme_pending_product_lots
272 WHERE pending_product_lot_id = l_pp_lot_id
273 FOR UPDATE NOWAIT;
274
275 DELETE
276 FROM gme_pending_product_lots
277 WHERE pending_product_lot_id = l_pp_lot_id;
278
279 ELSIF (l_matl_dtl_id IS NOT NULL) AND
280 (l_sequ IS NOT NULL) THEN
281 SELECT 1
282 INTO l_dummy
283 FROM gme_pending_product_lots
284 WHERE material_detail_id = l_matl_dtl_id
285 AND sequence = l_sequ
286 FOR UPDATE NOWAIT;
287
288 DELETE
289 FROM gme_pending_product_lots
290 WHERE material_detail_id = l_matl_dtl_id
291 AND sequence = l_sequ;
292 ELSE
293 gme_common_pvt.log_message ('GME_NO_KEYS'
294 ,'TABLE_NAME'
295 ,g_table_name);
296 RETURN FALSE;
297 END IF;
298
299 IF (SQL%FOUND) THEN
300 RETURN TRUE;
301 ELSE
302 IF l_dummy = 0 THEN
303 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
304 ,'TABLE_NAME'
305 ,g_table_name);
306 ELSE
307 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
308 ,'TABLE_NAME'
309 ,g_table_name);
310 RETURN FALSE;
311 END IF;
312 END IF;
313
314 IF g_debug <= gme_debug.g_log_procedure THEN
315 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
316 END IF;
317
318 RETURN TRUE;
319 EXCEPTION
320 WHEN NO_DATA_FOUND THEN
321 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
322 ,'TABLE_NAME'
323 ,g_table_name);
324 RETURN FALSE;
325 WHEN locked_by_other_user THEN
326 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
327 ,'TABLE_NAME'
328 ,g_table_name
329 ,'RECORD'
330 ,'PendingProductLots'
331 ,'KEY'
332 ,p_pending_product_lots_rec.pending_product_lot_id);
333 RETURN FALSE;
334 WHEN OTHERS THEN
335 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
336 IF g_debug <= gme_debug.g_log_procedure THEN
337 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
338 END IF;
339
340 RETURN FALSE;
341 END delete_row;
342
343 /*==========================================================================
344 | FUNCTION NAME |
345 | update_row |
346 | |
347 | TYPE |
348 | Private |
349 | |
350 | USAGE |
351 | update_row will update a row in gme_pending_product_lots |
352 | |
353 | DESCRIPTION |
354 | update_row will update a row in gme_pending_product_lots |
355 | |
356 | PARAMETERS |
357 | p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE |
358 | |
359 | RETURNS |
360 | BOOLEAN |
361 | |
362 | HISTORY |
363 | |
364 ==========================================================================*/
365 FUNCTION update_row (p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE) RETURN BOOLEAN IS
366 l_dummy NUMBER := 0;
367
368 locked_by_other_user EXCEPTION;
369 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
370 l_api_name CONSTANT VARCHAR2 (30) := 'update_row';
371
372 l_pp_lot_id NUMBER;
373 l_matl_dtl_id NUMBER;
374 l_sequ NUMBER;
375 l_pp_lot_rec gme_pending_product_lots%ROWTYPE;
376
377 BEGIN
378
379 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
380 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
381 END IF;
382
383 l_pp_lot_id := p_pending_product_lots_rec.pending_product_lot_id;
384 l_matl_dtl_id := p_pending_product_lots_rec.material_detail_id;
385 l_sequ := p_pending_product_lots_rec.sequence;
386
387 l_pp_lot_rec := p_pending_product_lots_rec;
388
389 l_pp_lot_rec.last_update_date := gme_common_pvt.g_timestamp;
390 l_pp_lot_rec.last_updated_by := gme_common_pvt.g_user_ident;
391 l_pp_lot_rec.last_update_login := gme_common_pvt.g_login_id;
392
393 IF (l_pp_lot_id IS NOT NULL) THEN
394 SELECT 1
395 INTO l_dummy
396 FROM gme_pending_product_lots
397 WHERE pending_product_lot_id = l_pp_lot_id
398 FOR UPDATE NOWAIT;
399 /* Bug 5193154 added lot number*/
400 UPDATE gme_pending_product_lots
401 SET SEQUENCE = l_pp_lot_rec.SEQUENCE
402 ,LOT_NUMBER = l_pp_lot_rec.LOT_NUMBER
403 ,REVISION = l_pp_lot_rec.REVISION
404 ,QUANTITY = l_pp_lot_rec.QUANTITY
405 ,SECONDARY_QUANTITY = l_pp_lot_rec.SECONDARY_QUANTITY
406 ,REASON_ID = l_pp_lot_rec.REASON_ID
407 ,LAST_UPDATE_DATE = l_pp_lot_rec.LAST_UPDATE_DATE
408 ,LAST_UPDATED_BY = l_pp_lot_rec.LAST_UPDATED_BY
409 ,LAST_UPDATE_LOGIN = l_pp_lot_rec.LAST_UPDATE_LOGIN
410 WHERE pending_product_lot_id = l_pp_lot_id
411 AND last_update_date = p_pending_product_lots_rec.last_update_date;
412
413 ELSIF (l_matl_dtl_id IS NOT NULL) AND
414 (l_sequ IS NOT NULL) THEN
415 SELECT 1
416 INTO l_dummy
417 FROM gme_pending_product_lots
418 WHERE material_detail_id = l_matl_dtl_id
419 AND sequence = l_sequ
420 FOR UPDATE NOWAIT;
421
422 UPDATE gme_pending_product_lots
423 SET SEQUENCE = l_pp_lot_rec.SEQUENCE
424 ,REVISION = l_pp_lot_rec.REVISION
425 ,QUANTITY = l_pp_lot_rec.QUANTITY
426 ,SECONDARY_QUANTITY = l_pp_lot_rec.SECONDARY_QUANTITY
427 ,REASON_ID = l_pp_lot_rec.REASON_ID
428 ,LAST_UPDATE_DATE = l_pp_lot_rec.LAST_UPDATE_DATE
429 ,LAST_UPDATED_BY = l_pp_lot_rec.LAST_UPDATED_BY
430 ,LAST_UPDATE_LOGIN = l_pp_lot_rec.LAST_UPDATE_LOGIN
431 WHERE material_detail_id = l_matl_dtl_id
432 AND sequence = l_sequ
433 AND last_update_date = p_pending_product_lots_rec.last_update_date;
434 ELSE
435 gme_common_pvt.log_message ('GME_NO_KEYS'
436 ,'TABLE_NAME'
437 ,g_table_name);
438 RETURN FALSE;
439 END IF;
440
441 IF g_debug <= gme_debug.g_log_procedure THEN
442 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
443 END IF;
444
445 IF SQL%ROWCOUNT <> 0 THEN
446 RETURN TRUE;
447 ELSE
448 RAISE NO_DATA_FOUND;
449 END IF;
450
451 RETURN TRUE;
452 EXCEPTION
453 WHEN NO_DATA_FOUND THEN
454 IF l_dummy = 0 THEN
455 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
456 ,'TABLE_NAME'
457 ,g_table_name);
458 ELSE
459 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
460 ,'TABLE_NAME'
461 ,g_table_name);
462 END IF;
463 RETURN FALSE;
464 WHEN locked_by_other_user THEN
465 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
466 ,'TABLE_NAME'
467 ,g_table_name
468 ,'RECORD'
469 ,'PendingProductLots'
470 ,'KEY'
471 ,p_pending_product_lots_rec.pending_product_lot_id);
472 RETURN FALSE;
473 WHEN OTHERS THEN
474 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
475 IF g_debug <= gme_debug.g_log_procedure THEN
476 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
477 END IF;
478
479 RETURN FALSE;
480 END update_row;
481
482 /*==========================================================================
483 | FUNCTION NAME |
484 | lock_row |
485 | |
486 | TYPE |
487 | Private |
488 | |
489 | USAGE |
490 | lock_row will lock a row in gme_pending_product_lots |
491 | |
492 | DESCRIPTION |
493 | lock_row will lock a row in gme_pending_product_lots |
494 | |
495 | PARAMETERS |
496 | p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE |
497 | |
498 | RETURNS |
499 | BOOLEAN |
500 | |
501 | HISTORY |
502 | |
503 ==========================================================================*/
504 FUNCTION lock_row (p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE) RETURN BOOLEAN IS
505 l_dummy NUMBER := 0;
506
507 locked_by_other_user EXCEPTION;
508 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
509 l_api_name CONSTANT VARCHAR2 (30) := 'lock_row';
510
511 l_pp_lot_id NUMBER;
512 l_matl_dtl_id NUMBER;
513 l_sequ NUMBER;
514
515 BEGIN
516
517 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
518 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
519 END IF;
520
521 l_pp_lot_id := p_pending_product_lots_rec.pending_product_lot_id;
522 l_matl_dtl_id := p_pending_product_lots_rec.material_detail_id;
523 l_sequ := p_pending_product_lots_rec.sequence;
524
525 IF (l_pp_lot_id IS NOT NULL) THEN
526 SELECT 1
527 INTO l_dummy
528 FROM gme_pending_product_lots
529 WHERE pending_product_lot_id = l_pp_lot_id
530 FOR UPDATE NOWAIT;
531
532 ELSIF (l_matl_dtl_id IS NOT NULL) AND
533 (l_sequ IS NOT NULL) THEN
534 SELECT 1
535 INTO l_dummy
536 FROM gme_pending_product_lots
537 WHERE material_detail_id = l_matl_dtl_id
538 AND sequence = l_sequ
539 FOR UPDATE NOWAIT;
540 ELSE
541 gme_common_pvt.log_message ('GME_NO_KEYS'
542 ,'TABLE_NAME'
543 ,g_table_name);
544 RETURN FALSE;
545 END IF;
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 RETURN TRUE;
552 EXCEPTION
553 WHEN app_exception.record_lock_exception THEN
554 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
555 ,'TABLE_NAME'
556 ,g_table_name
557 ,'RECORD'
558 ,'PendingProductLots'
559 ,'KEY'
560 ,p_pending_product_lots_rec.pending_product_lot_id);
561 RETURN FALSE;
562 WHEN OTHERS THEN
563 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
564 IF g_debug <= gme_debug.g_log_procedure THEN
565 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
566 END IF;
567
568 RETURN FALSE;
569 END lock_row;
570 END gme_pending_product_lots_dbl;