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