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