[Home] [Help]
PACKAGE BODY: APPS.ARP_TRANSACTION_HISTORY_PKG
Source
1 PACKAGE BODY ARP_TRANSACTION_HISTORY_PKG AS
2 /*$Header: ARRITRHB.pls 120.8 2005/08/10 23:14:24 hyu ship $*/
3
4 /*--------------------------------------------------------+
5 | Dummy constants for use in update and lock operations |
6 +--------------------------------------------------------*/
7
8 AR_TEXT_DUMMY CONSTANT VARCHAR2(10) := '~~!@#$*&^';
9 AR_FLAG_DUMMY CONSTANT VARCHAR2(10) := '~';
10 AR_NUMBER_DUMMY CONSTANT NUMBER(15) := -999999999999999;
11 AR_DATE_DUMMY CONSTANT DATE := to_date(1, 'J');
12
13 /*-------------------------------------+
14 | WHO column values from ARP_GLOBAL |
15 +-------------------------------------*/
16
17 pg_request_id number;
18 pg_program_application_id number;
19 pg_program_id number;
20 pg_program_update_date date;
21 pg_last_updated_by number;
22 pg_last_update_date date;
23 pg_last_update_login number;
24 pg_set_of_books_id number;
25
26 /*===========================================================================+
27 | PROCEDURE |
28 | set_to_dummy |
29 | |
30 | DESCRIPTION |
31 | Returns transaction history record with values set to dummy values |
32 | |
33 | SCOPE - PUBLIC |
34 | |
35 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
36 | |
37 | ARGUMENTS : IN: NONE |
38 | OUT: p_trh_rec Transaction history record |
39 | RETURNS : |
40 | NOTES |
41 | MODIFICATION HISTORY - Created by Jani Rautiainen - 18-JAN-2000 |
42 | |
43 +===========================================================================*/
44 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
45
46 PROCEDURE set_to_dummy( p_trh_rec OUT NOCOPY AR_TRANSACTION_HISTORY%ROWTYPE ) IS
47 BEGIN
48
49 IF PG_DEBUG in ('Y', 'C') THEN
50 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.set_to_dummy()+' );
51 END IF;
52
53 p_trh_rec.customer_trx_id := AR_NUMBER_DUMMY;
54 p_trh_rec.status := AR_TEXT_DUMMY;
55 p_trh_rec.event := AR_TEXT_DUMMY;
56 p_trh_rec.batch_id := AR_NUMBER_DUMMY;
57 p_trh_rec.trx_date := AR_DATE_DUMMY;
58 p_trh_rec.gl_date := AR_DATE_DUMMY;
59 p_trh_rec.maturity_date := AR_DATE_DUMMY;
60 p_trh_rec.current_record_flag := AR_FLAG_DUMMY;
61 p_trh_rec.current_accounted_flag := AR_FLAG_DUMMY;
62 p_trh_rec.postable_flag := AR_FLAG_DUMMY;
63 p_trh_rec.first_posted_record_flag := AR_FLAG_DUMMY;
64 p_trh_rec.posting_control_id := AR_NUMBER_DUMMY;
65 p_trh_rec.gl_posted_date := AR_DATE_DUMMY;
66 p_trh_rec.prv_trx_history_id := AR_NUMBER_DUMMY;
67 p_trh_rec.created_from := AR_TEXT_DUMMY;
68 p_trh_rec.comments := AR_TEXT_DUMMY;
69 p_trh_rec.attribute_category := AR_TEXT_DUMMY;
70 p_trh_rec.attribute1 := AR_TEXT_DUMMY;
71 p_trh_rec.attribute2 := AR_TEXT_DUMMY;
72 p_trh_rec.attribute3 := AR_TEXT_DUMMY;
73 p_trh_rec.attribute4 := AR_TEXT_DUMMY;
74 p_trh_rec.attribute5 := AR_TEXT_DUMMY;
75 p_trh_rec.attribute6 := AR_TEXT_DUMMY;
76 p_trh_rec.attribute7 := AR_TEXT_DUMMY;
77 p_trh_rec.attribute8 := AR_TEXT_DUMMY;
78 p_trh_rec.attribute9 := AR_TEXT_DUMMY;
79 p_trh_rec.attribute10 := AR_TEXT_DUMMY;
80 p_trh_rec.attribute11 := AR_TEXT_DUMMY;
81 p_trh_rec.attribute12 := AR_TEXT_DUMMY;
82 p_trh_rec.attribute13 := AR_TEXT_DUMMY;
83 p_trh_rec.attribute14 := AR_TEXT_DUMMY;
84 p_trh_rec.attribute15 := AR_TEXT_DUMMY;
85
86 IF PG_DEBUG in ('Y', 'C') THEN
87 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.set_to_dummy()-' );
88 END IF;
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 IF PG_DEBUG in ('Y', 'C') THEN
93 arp_standard.debug( 'EXCEPTION: ARP_TRANSACTION_HISTORY_PKG.set_to_dummy' );
94 END IF;
95 RAISE;
96 END set_to_dummy;
97
98 /*===========================================================================+
99 | PROCEDURE |
100 | update_p |
101 | |
102 | DESCRIPTION |
103 | Updates transaction history record in DB with values given in the |
104 | parameter transaction record. |
105 | |
106 | SCOPE - PUBLIC |
110 | ARGUMENTS : IN: p_trh_rec Transaction history record |
107 | |
108 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
109 | |
111 | p_trh_id Transaction history id |
112 | OUT: NONE |
113 | RETURNS : |
114 | NOTES |
115 | MODIFICATION HISTORY - Created by Jani Rautiainen - 18-JAN-2000 |
116 | |
117 +===========================================================================*/
118 PROCEDURE update_p( p_trh_rec IN AR_TRANSACTION_HISTORY%ROWTYPE,
119 p_trh_id IN AR_TRANSACTION_HISTORY.transaction_history_id%TYPE ) IS
120 BEGIN
121 IF PG_DEBUG in ('Y', 'C') THEN
122 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.update_p()+' );
123 END IF;
124
125 UPDATE AR_TRANSACTION_HISTORY SET
126 customer_trx_id =
127 DECODE( p_trh_rec.customer_trx_id,
128 AR_NUMBER_DUMMY, customer_trx_id,
129 p_trh_rec.customer_trx_id ),
130 status =
131 DECODE( p_trh_rec.status,
132 AR_TEXT_DUMMY, status,
133 p_trh_rec.status ),
134 event =
135 DECODE( p_trh_rec.event,
136 AR_TEXT_DUMMY, event,
137 p_trh_rec.event ),
138 batch_id =
139 DECODE( p_trh_rec.batch_id,
140 AR_NUMBER_DUMMY, batch_id,
141 p_trh_rec.batch_id ),
142 trx_date =
143 DECODE( p_trh_rec.trx_date,
144 AR_DATE_DUMMY, trx_date,
145 p_trh_rec.trx_date ),
146 gl_date =
147 DECODE( p_trh_rec.gl_date,
148 AR_DATE_DUMMY, gl_date,
149 p_trh_rec.gl_date ),
150 maturity_date =
151 DECODE( p_trh_rec.maturity_date,
152 AR_DATE_DUMMY, maturity_date,
153 p_trh_rec.maturity_date),
154 current_record_flag =
155 DECODE( p_trh_rec.current_record_flag,
156 AR_FLAG_DUMMY, current_record_flag,
157 p_trh_rec.current_record_flag ),
158 current_accounted_flag =
159 DECODE( p_trh_rec.current_accounted_flag,
160 AR_FLAG_DUMMY, current_accounted_flag,
164 AR_FLAG_DUMMY, postable_flag,
161 p_trh_rec.current_accounted_flag ),
162 postable_flag =
163 DECODE( p_trh_rec.postable_flag,
165 p_trh_rec.postable_flag ),
166 first_posted_record_flag =
167 DECODE( p_trh_rec.first_posted_record_flag,
168 AR_FLAG_DUMMY, first_posted_record_flag,
169 p_trh_rec.first_posted_record_flag ),
170 posting_control_id =
171 DECODE( p_trh_rec.posting_control_id,
172 AR_NUMBER_DUMMY, posting_control_id,
173 p_trh_rec.posting_control_id ),
174 gl_posted_date =
175 DECODE( p_trh_rec.gl_posted_date,
176 AR_DATE_DUMMY, gl_posted_date,
177 p_trh_rec.gl_posted_date ),
178 prv_trx_history_id =
179 DECODE( p_trh_rec.prv_trx_history_id,
180 AR_NUMBER_DUMMY, prv_trx_history_id,
181 p_trh_rec.prv_trx_history_id ),
182 created_from =
183 DECODE( p_trh_rec.created_from,
184 AR_TEXT_DUMMY, created_from,
185 p_trh_rec.created_from ),
186 comments =
187 DECODE( p_trh_rec.comments,
188 AR_TEXT_DUMMY, comments,
189 p_trh_rec.comments ),
190 attribute_category =
191 DECODE( p_trh_rec.attribute_category,
192 AR_TEXT_DUMMY, attribute_category,
193 p_trh_rec.attribute_category ),
194 attribute1 =
195 DECODE( p_trh_rec.attribute1,
196 AR_TEXT_DUMMY, attribute1,
197 p_trh_rec.attribute1 ),
198 attribute2 =
199 DECODE( p_trh_rec.attribute2,
200 AR_TEXT_DUMMY, attribute2,
201 p_trh_rec.attribute2 ),
202 attribute3 =
203 DECODE( p_trh_rec.attribute3,
204 AR_TEXT_DUMMY, attribute3,
205 p_trh_rec.attribute3 ),
206 attribute4 =
207 DECODE( p_trh_rec.attribute4,
208 AR_TEXT_DUMMY, attribute4,
209 p_trh_rec.attribute4 ),
210 attribute5 =
211 DECODE( p_trh_rec.attribute5,
212 AR_TEXT_DUMMY, attribute5,
213 p_trh_rec.attribute5 ),
214 attribute6 =
215 DECODE( p_trh_rec.attribute6,
216 AR_TEXT_DUMMY, attribute6,
217 p_trh_rec.attribute6 ),
218 attribute7 =
219 DECODE( p_trh_rec.attribute7,
220 AR_TEXT_DUMMY, attribute7,
221 p_trh_rec.attribute7 ),
222 attribute8 =
223 DECODE( p_trh_rec.attribute8,
224 AR_TEXT_DUMMY, attribute8,
225 p_trh_rec.attribute8 ),
226 attribute9 =
227 DECODE( p_trh_rec.attribute9,
228 AR_TEXT_DUMMY, attribute9,
229 p_trh_rec.attribute9 ),
230 attribute10 =
231 DECODE( p_trh_rec.attribute10,
232 AR_TEXT_DUMMY, attribute10,
233 p_trh_rec.attribute10 ),
234 attribute11 =
235 DECODE( p_trh_rec.attribute11,
236 AR_TEXT_DUMMY, attribute11,
237 p_trh_rec.attribute11 ),
238 attribute12 =
239 DECODE( p_trh_rec.attribute12,
240 AR_TEXT_DUMMY, attribute12,
241 p_trh_rec.attribute12 ),
242 attribute13 =
243 DECODE( p_trh_rec.attribute13,
244 AR_TEXT_DUMMY, attribute13,
245 p_trh_rec.attribute13 ),
246 attribute14 =
247 DECODE( p_trh_rec.attribute14,
248 AR_TEXT_DUMMY, attribute14,
249 p_trh_rec.attribute14 ),
250 attribute15 =
251 DECODE( p_trh_rec.attribute15,
252 AR_TEXT_DUMMY, attribute15,
253 p_trh_rec.attribute15 ),
254 program_application_id = pg_program_application_id,
255 program_id = pg_program_id,
256 program_update_date = pg_program_update_date,
257 request_id = pg_request_id,
258 last_update_login = pg_last_update_login,
259 last_update_date = pg_last_update_date,
260 last_updated_by = pg_last_updated_by
261
262 WHERE transaction_history_id = p_trh_id;
263
264 IF PG_DEBUG in ('Y', 'C') THEN
265 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.update_p()-' );
269 WHEN OTHERS THEN
266 END IF;
267
268 EXCEPTION
270 IF PG_DEBUG in ('Y', 'C') THEN
271 arp_standard.debug( 'EXCEPTION: ARP_TRANSACTION_HISTORY_PKG.update_p' );
272 END IF;
273 RAISE;
274 END update_p;
275
276 /*===========================================================================+
277 | PROCEDURE |
278 | insert_p |
279 | |
280 | DESCRIPTION |
281 | Creates transaction history record in DB with values given in the |
282 | parameter transaction record. Returns the transaction_history_id of the |
283 | record that was created. |
284 | |
285 | SCOPE - PUBLIC |
286 | |
287 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
288 | |
289 | ARGUMENTS : IN: p_trh_rec Transaction history record |
290 | OUT: p_trh_id Transaction history id |
291 | RETURNS : |
292 | NOTES |
293 | MODIFICATION HISTORY - Created by Jani Rautiainen - 18-JAN-2000 |
294 | |
295 +===========================================================================*/
296 PROCEDURE insert_p(p_trh_rec IN AR_TRANSACTION_HISTORY%ROWTYPE,
297 p_trh_id OUT NOCOPY AR_TRANSACTION_HISTORY.transaction_history_id%TYPE ) IS
298
299 l_trh_id AR_TRANSACTION_HISTORY.transaction_history_id%TYPE;
300 BEGIN
301 IF PG_DEBUG in ('Y', 'C') THEN
302 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.insert_p()+' );
303 END IF;
304
305 SELECT AR_TRANSACTION_HISTORY_s.nextval
306 INTO l_trh_id
307 FROM dual;
308
309 INSERT INTO AR_TRANSACTION_HISTORY (
310 transaction_history_id,
311 customer_trx_id,
312 status,
313 event,
314 batch_id,
315 trx_date,
316 gl_date,
317 maturity_date,
318 current_record_flag,
319 current_accounted_flag,
320 postable_flag,
321 first_posted_record_flag,
322 posting_control_id,
323 gl_posted_date,
324 prv_trx_history_id,
325 created_from,
326 comments,
327 attribute_category,
328 attribute1,
329 attribute2,
330 attribute3,
331 attribute4,
332 attribute5,
333 attribute6,
334 attribute7,
335 attribute8,
336 attribute9,
337 attribute10,
338 attribute11,
339 attribute12,
340 attribute13,
341 attribute14,
342 attribute15,
343 program_application_id,
344 program_id,
345 program_update_date,
346 request_id,
347 creation_date,
348 created_by,
349 last_update_login,
350 last_update_date,
351 last_updated_by
352 ,org_id)
353 VALUES ( l_trh_id,
354 p_trh_rec.customer_trx_id,
355 p_trh_rec.status,
356 p_trh_rec.event,
357 p_trh_rec.batch_id,
358 p_trh_rec.trx_date,
359 p_trh_rec.gl_date,
360 p_trh_rec.maturity_date,
361 p_trh_rec.current_record_flag,
362 p_trh_rec.current_accounted_flag,
363 p_trh_rec.postable_flag,
364 p_trh_rec.first_posted_record_flag,
365 p_trh_rec.posting_control_id,
366 p_trh_rec.gl_posted_date,
367 p_trh_rec.prv_trx_history_id,
368 p_trh_rec.created_from,
369 p_trh_rec.comments,
370 p_trh_rec.attribute_category,
371 p_trh_rec.attribute1,
372 p_trh_rec.attribute2,
373 p_trh_rec.attribute3,
374 p_trh_rec.attribute4,
375 p_trh_rec.attribute5,
376 p_trh_rec.attribute6,
377 p_trh_rec.attribute7,
378 p_trh_rec.attribute8,
379 p_trh_rec.attribute9,
380 p_trh_rec.attribute10,
381 p_trh_rec.attribute11,
382 p_trh_rec.attribute12,
383 p_trh_rec.attribute13,
384 p_trh_rec.attribute14,
385 p_trh_rec.attribute15,
386 NVL( arp_standard.profile.program_application_id,p_trh_rec.program_application_id ),
390 SYSDATE,
387 NVL( arp_standard.profile.program_id,p_trh_rec.program_id ),
388 DECODE( arp_standard.profile.program_id,NULL, NULL, SYSDATE),
389 NVL( arp_standard.profile.request_id, p_trh_rec.request_id ),
391 arp_standard.profile.user_id,
392 NVL( arp_standard.profile.last_update_login,p_trh_rec.last_update_login ),
393 SYSDATE,
394 arp_standard.profile.user_id
395 ,arp_standard.sysparm.org_id /* SSA changes anuj */);
396
397 p_trh_id := l_trh_id;
398
399 IF PG_DEBUG in ('Y', 'C') THEN
400 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.insert_p()-' );
401 END IF;
402
403 EXCEPTION
404 WHEN OTHERS THEN
405 IF PG_DEBUG in ('Y', 'C') THEN
406 arp_standard.debug( 'EXCEPTION: ARP_TRANSACTION_HISTORY_PKG.insert_p' );
407 END IF;
408 RAISE;
409 END insert_p;
410
411 /*===========================================================================+
412 | PROCEDURE |
413 | delete_p |
414 | |
415 | DESCRIPTION |
416 | Deletes transaction history record from DB related to the transaction |
417 | history id given as parameter |
418 | |
419 | SCOPE - PUBLIC |
420 | |
421 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
422 | |
423 | ARGUMENTS : IN: p_trh_id Transaction history id |
424 | OUT: NONE |
425 | RETURNS : |
426 | NOTES |
427 | MODIFICATION HISTORY - Created by Jani Rautiainen - 18-JAN-2000 |
428 | |
429 +===========================================================================*/
430 PROCEDURE delete_p(p_trh_id IN AR_TRANSACTION_HISTORY.transaction_history_id%TYPE ) IS
431 BEGIN
432 IF PG_DEBUG in ('Y', 'C') THEN
433 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.delete_p()+' );
434 END IF;
435
436 DELETE FROM AR_TRANSACTION_HISTORY
437 WHERE transaction_history_id = p_trh_id;
438
439 IF PG_DEBUG in ('Y', 'C') THEN
440 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.delete_p()-' );
441 END IF;
442 EXCEPTION
443 WHEN OTHERS THEN
444 IF PG_DEBUG in ('Y', 'C') THEN
445 arp_standard.debug( 'EXCEPTION: ARP_TRANSACTION_HISTORY_PKG.delete_p' );
446 END IF;
447 RAISE;
448 END delete_p;
449
450 /*===========================================================================+
451 | PROCEDURE |
452 | delete_p |
453 | |
454 | DESCRIPTION |
455 | Deletes all transaction history records from DB related to the |
456 | transaction id given as parameter. |
457 | |
458 | SCOPE - PUBLIC |
459 | |
460 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
461 | |
462 | ARGUMENTS : IN: p_trx_id Transaction id |
463 | OUT: NONE |
464 | RETURNS : |
465 | NOTES |
466 | MODIFICATION HISTORY - Created by Jani Rautiainen - 18-JAN-2000 |
467 | |
468 +===========================================================================*/
469 PROCEDURE delete_p(p_trx_id IN ra_customer_trx.customer_trx_id%TYPE ) IS
470 BEGIN
471 IF PG_DEBUG in ('Y', 'C') THEN
472 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.delete_p()+' );
473 END IF;
474
475 DELETE FROM AR_TRANSACTION_HISTORY
476 WHERE customer_trx_id = p_trx_id;
477
478 IF PG_DEBUG in ('Y', 'C') THEN
479 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.delete_p()-' );
480 END IF;
481 EXCEPTION
482 WHEN OTHERS THEN
483 IF PG_DEBUG in ('Y', 'C') THEN
484 arp_standard.debug( 'EXCEPTION: ARP_TRANSACTION_HISTORY_PKG.delete_p' );
485 END IF;
486 RAISE;
487 END delete_p;
488
489 /*===========================================================================+
490 | PROCEDURE |
491 | lock_p |
492 | |
493 | DESCRIPTION |
494 | Locks transaction history record in DB related to the transaction |
495 | history id given as parameter. If the row is already locked this |
499 | |
496 | procedure will wait for it to be released. |
497 | |
498 | SCOPE - PUBLIC |
500 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
501 | |
502 | ARGUMENTS : IN: p_trh_id Transaction history id |
503 | OUT: NONE |
504 | RETURNS : |
505 | NOTES |
506 | MODIFICATION HISTORY - Created by Jani Rautiainen - 18-JAN-2000 |
507 | |
508 +===========================================================================*/
509 PROCEDURE lock_p(p_trh_id IN AR_TRANSACTION_HISTORY.transaction_history_id%TYPE ) IS
510 l_trh_id AR_TRANSACTION_HISTORY.transaction_history_id%TYPE;
511 BEGIN
512 IF PG_DEBUG in ('Y', 'C') THEN
513 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.lock_p()+' );
514 END IF;
515
516 SELECT transaction_history_id
517 INTO l_trh_id
518 FROM AR_TRANSACTION_HISTORY
519 WHERE transaction_history_id = p_trh_id
520 FOR UPDATE OF STATUS;
521
522 IF PG_DEBUG in ('Y', 'C') THEN
523 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.lock_p()-' );
524 END IF;
525 EXCEPTION
526 WHEN OTHERS THEN
527 IF PG_DEBUG in ('Y', 'C') THEN
528 arp_standard.debug( 'EXCEPTION: ARP_TRANSACTION_HISTORY_PKG.lock_p' );
529 END IF;
530 RAISE;
531 END lock_p;
532
533 /*===========================================================================+
534 | PROCEDURE |
535 | nowaitlock_p |
536 | |
537 | DESCRIPTION |
538 | Locks transaction history record in DB related to the transaction |
539 | history id given as parameter. If the row is already locked this |
540 | procedure will return an error. |
541 | |
542 | SCOPE - PUBLIC |
543 | |
544 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
545 | |
546 | ARGUMENTS : IN: p_trh_id Transaction history id |
547 | OUT: NONE |
548 | RETURNS : |
549 | NOTES |
550 | MODIFICATION HISTORY - Created by Jani Rautiainen - 18-JAN-2000 |
551 | |
552 +===========================================================================*/
553 PROCEDURE nowaitlock_p( p_trh_id IN AR_TRANSACTION_HISTORY.transaction_history_id%TYPE ) IS
554 l_trh_id AR_TRANSACTION_HISTORY.transaction_history_id%TYPE;
555 BEGIN
556 IF PG_DEBUG in ('Y', 'C') THEN
557 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.nowaitlock_p()+' );
558 END IF;
559
560 SELECT transaction_history_id
561 INTO l_trh_id
562 FROM AR_TRANSACTION_HISTORY
563 WHERE transaction_history_id = p_trh_id
564 FOR UPDATE OF status NOWAIT;
565
566 IF PG_DEBUG in ('Y', 'C') THEN
567 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.nowaitlock_p()-' );
568 END IF;
569 EXCEPTION
570 WHEN OTHERS THEN
571 IF PG_DEBUG in ('Y', 'C') THEN
572 arp_standard.debug( 'EXCEPTION: ARP_TRANSACTION_HISTORY_PKG.nowaitlock_p' );
573 END IF;
574 RAISE;
575 END nowaitlock_p;
576
577 /*===========================================================================+
578 | PROCEDURE |
579 | fetch_p |
580 | |
581 | DESCRIPTION |
582 | Fetches the transaction history record from DB related to transaction |
583 | history id given as parameter.
584 | |
585 | SCOPE - PUBLIC |
586 | |
587 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
588 | |
589 | ARGUMENTS : IN: p_trh_id Transaction history id |
590 | OUT: p_trh_rec Transaction history record |
591 | RETURNS : |
592 | NOTES |
593 | MODIFICATION HISTORY - Created by Jani Rautiainen - 18-JAN-2000 |
594 | |
595 +===========================================================================*/
596 PROCEDURE fetch_p( p_trh_id IN AR_TRANSACTION_HISTORY.transaction_history_id%TYPE,
600 IF PG_DEBUG in ('Y', 'C') THEN
597 p_trh_rec OUT NOCOPY AR_TRANSACTION_HISTORY%ROWTYPE ) IS
598 BEGIN
599
601 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.fetch_p()+' );
602 END IF;
603
604 SELECT *
605 INTO p_trh_rec
606 FROM AR_TRANSACTION_HISTORY
607 WHERE transaction_history_id = p_trh_id;
608 IF PG_DEBUG in ('Y', 'C') THEN
609 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.fetch_p()-' );
610 END IF;
611
612 EXCEPTION
613 WHEN OTHERS THEN
614 IF PG_DEBUG in ('Y', 'C') THEN
615 arp_standard.debug( 'EXCEPTION: ARP_TRANSACTION_HISTORY_PKG.fetch_p' );
616 END IF;
617 RAISE;
618 END fetch_p;
619
620 /*===========================================================================+
621 | PROCEDURE |
622 | fetch_f_trx_id |
623 | |
624 | DESCRIPTION |
625 | Fetches the current transaction history record from DB related to |
626 | transaction id given in record parameter. |
627 | |
628 | SCOPE - PUBLIC |
629 | |
630 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
631 | |
632 | ARGUMENTS : IN: p_trh_rec Transaction history record |
633 | OUT: p_trh_rec Transaction history record |
634 | RETURNS : |
635 | NOTES |
636 | MODIFICATION HISTORY - Created by Jani Rautiainen - 18-JAN-2000 |
637 | |
638 +===========================================================================*/
639 PROCEDURE fetch_f_trx_id( p_trh_rec IN OUT NOCOPY AR_TRANSACTION_HISTORY%ROWTYPE ) IS
640 BEGIN
641 IF PG_DEBUG in ('Y', 'C') THEN
642 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.fetch_f_trx_id()+' );
643 END IF;
644
645 SELECT *
646 INTO p_trh_rec
647 FROM AR_TRANSACTION_HISTORY
648 WHERE customer_trx_id = p_trh_rec.customer_trx_id AND
649 current_record_flag = 'Y';
650
651 IF PG_DEBUG in ('Y', 'C') THEN
652 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.fetch_f_trx_id()-' );
653 END IF;
654
655 EXCEPTION
656 WHEN OTHERS THEN
657 IF PG_DEBUG in ('Y', 'C') THEN
658 arp_standard.debug( 'EXCEPTION: ARP_TRANSACTION_HISTORY_PKG.fetch_f_trx_id' );
659 END IF;
660 RAISE;
661 END fetch_f_trx_id;
662
663 /*===========================================================================+
664 | PROCEDURE |
665 | lock_fetch_p |
666 | |
667 | DESCRIPTION |
668 | Locks and fetches the transaction history record from DB related to |
669 | transaction history id given in record parameter. If the row is already |
670 | locked this procedure will wait for it to be released. |
671 | |
672 | SCOPE - PUBLIC |
673 | |
674 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
675 | |
676 | ARGUMENTS : IN: p_trh_rec Transaction history record |
677 | OUT: p_trh_rec Transaction history record |
678 | RETURNS : |
679 | NOTES |
680 | MODIFICATION HISTORY - Created by Jani Rautiainen - 18-JAN-2000 |
681 | |
682 +===========================================================================*/
683 PROCEDURE lock_fetch_p(p_trh_rec IN OUT NOCOPY AR_TRANSACTION_HISTORY%ROWTYPE) IS
684 BEGIN
685 IF PG_DEBUG in ('Y', 'C') THEN
686 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.lock_fetch_p()+' );
687 END IF;
688
689 SELECT *
690 INTO p_trh_rec
691 FROM AR_TRANSACTION_HISTORY
692 WHERE transaction_history_id = p_trh_rec.transaction_history_id
693 FOR UPDATE OF status;
694
695 IF PG_DEBUG in ('Y', 'C') THEN
696 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.lock_fetch_p()-' );
697 END IF;
698
699 EXCEPTION
700 WHEN OTHERS THEN
701 IF PG_DEBUG in ('Y', 'C') THEN
702 arp_standard.debug( 'EXCEPTION: ARP_TRANSACTION_HISTORY_PKG.lock_fetch_p' );
703 END IF;
704 RAISE;
705 END lock_fetch_p;
706
707 /*===========================================================================+
708 | PROCEDURE |
709 | nowaitlock_fetch_p |
710 | |
714 | locked this procedure will return an error. |
711 | DESCRIPTION |
712 | Locks and fetches the transaction history record from DB related to |
713 | transaction history id given in record parameter. If the row is already |
715 | |
716 | SCOPE - PUBLIC |
717 | |
718 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
719 | |
720 | ARGUMENTS : IN: p_trh_rec Transaction history record |
721 | OUT: p_trh_rec Transaction history record |
722 | RETURNS : |
723 | NOTES |
724 | MODIFICATION HISTORY - Created by Jani Rautiainen - 18-JAN-2000 |
725 | |
726 +===========================================================================*/
727 PROCEDURE nowaitlock_fetch_p(p_trh_rec IN OUT NOCOPY AR_TRANSACTION_HISTORY%ROWTYPE) IS
728 BEGIN
729 IF PG_DEBUG in ('Y', 'C') THEN
730 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.nowaitlock_fetch_p()+' );
731 END IF;
732
733 SELECT *
734 INTO p_trh_rec
735 FROM AR_TRANSACTION_HISTORY
736 WHERE transaction_history_id = p_trh_rec.transaction_history_id
737 FOR UPDATE OF status NOWAIT;
738
739 IF PG_DEBUG in ('Y', 'C') THEN
740 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.nowaitlock_fetch_p()-' );
741 END IF;
742
743 EXCEPTION
744 WHEN OTHERS THEN
745 IF PG_DEBUG in ('Y', 'C') THEN
746 arp_standard.debug( 'EXCEPTION: ARP_TRANSACTION_HISTORY_PKG.nowaitlock_fetch_p' );
747 END IF;
748 RAISE;
749 END nowaitlock_fetch_p;
750
751 /*===========================================================================+
752 | PROCEDURE |
753 | lock_f_trx_id |
754 | |
755 | DESCRIPTION |
756 | Locks transaction history record in DB related to the transaction |
757 | id given as parameter. If the row is already locked this |
758 | procedure will wait for it to be released. |
759 | |
760 | SCOPE - PUBLIC |
761 | |
762 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
763 | |
764 | ARGUMENTS : IN: p_trx_id Transaction id |
765 | OUT: NONE |
766 | RETURNS : |
767 | NOTES |
768 | MODIFICATION HISTORY - Created by Jani Rautiainen - 18-JAN-2000 |
769 | |
770 +===========================================================================*/
771 PROCEDURE lock_f_trx_id(p_trx_id IN ra_customer_trx.customer_trx_id%TYPE ) IS
772
773 CURSOR lock_C IS
774 SELECT 'lock'
775 FROM AR_TRANSACTION_HISTORY
776 WHERE customer_trx_id = p_trx_id
777 FOR UPDATE OF status;
778
779 BEGIN
780 IF PG_DEBUG in ('Y', 'C') THEN
781 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.lock_f_trx_id()+' );
782 END IF;
783
784 OPEN lock_C;
785 CLOSE lock_C;
786
787 IF PG_DEBUG in ('Y', 'C') THEN
788 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.lock_f_trx_id()-' );
789 END IF;
790
791 EXCEPTION
792 WHEN OTHERS THEN
793 IF lock_C%ISOPEN THEN
794 CLOSE lock_C;
795 END IF;
796
797 IF PG_DEBUG in ('Y', 'C') THEN
798 arp_standard.debug( 'EXCEPTION: ARP_TRANSACTION_HISTORY_PKG.lock_f_trx_id' );
799 END IF;
800 RAISE;
801 END lock_f_trx_id;
802 /*===========================================================================+
803 | PROCEDURE |
804 | nowaitlock_f_trx_id |
805 | |
806 | DESCRIPTION |
807 | Locks transaction history record in DB related to the transaction |
808 | id given as parameter. If the row is already locked this |
809 | procedure will return an error. |
810 | |
811 | SCOPE - PUBLIC |
812 | |
813 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
814 | |
815 | ARGUMENTS : IN: p_trx_id Transaction id |
816 | OUT: NONE |
820 | |
817 | RETURNS : |
818 | NOTES |
819 | MODIFICATION HISTORY - Created by Jani Rautiainen - 18-JAN-2000 |
821 +===========================================================================*/
822 PROCEDURE nowaitlock_f_trx_id(p_trx_id IN ra_customer_trx.customer_trx_id%TYPE ) IS
823
824 CURSOR lock_C IS
825 SELECT 'lock'
826 FROM AR_TRANSACTION_HISTORY
827 WHERE customer_trx_id = p_trx_id
828 FOR UPDATE OF status NOWAIT;
829
830 BEGIN
831 IF PG_DEBUG in ('Y', 'C') THEN
832 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.nowaitlock_f_trx_id()+' );
833 END IF;
834
835 OPEN lock_C;
836 CLOSE lock_C;
837
838 IF PG_DEBUG in ('Y', 'C') THEN
839 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.nowaitlock_f_trx_id()-' );
840 END IF;
841
842 EXCEPTION
843 WHEN OTHERS THEN
844 IF lock_C%ISOPEN THEN
845 CLOSE lock_C;
846 END IF;
847
848 IF PG_DEBUG in ('Y', 'C') THEN
849 arp_standard.debug( 'EXCEPTION: ARP_TRANSACTION_HISTORY_PKG.nowaitlock_f_trx_id' );
850 END IF;
851 RAISE;
852 END nowaitlock_f_trx_id;
853
854 /*===========================================================================+
855 | PROCEDURE |
856 | lock_fetch_f_trx_id |
857 | |
858 | DESCRIPTION |
859 | Locks and fetches the current transaction history record from DB |
860 | related to transaction id given in record parameter. If the row is |
861 | already locked this procedure will wait for it to be released. |
862 | |
863 | SCOPE - PUBLIC |
864 | |
865 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
866 | |
867 | ARGUMENTS : IN: p_trh_rec Transaction history record |
868 | OUT: p_trh_rec Transaction history record |
869 | RETURNS : |
870 | NOTES |
871 | MODIFICATION HISTORY - Created by Jani Rautiainen - 18-JAN-2000 |
872 | |
873 +===========================================================================*/
874 PROCEDURE lock_fetch_f_trx_id(p_trh_rec IN OUT NOCOPY AR_TRANSACTION_HISTORY%ROWTYPE) IS
875 BEGIN
876 IF PG_DEBUG in ('Y', 'C') THEN
877 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.lock_fetch_f_trx_id()+' );
878 END IF;
879
880 SELECT *
881 INTO p_trh_rec
882 FROM AR_TRANSACTION_HISTORY
883 WHERE customer_trx_id = p_trh_rec.customer_trx_id
884 AND current_record_flag = 'Y'
885 FOR UPDATE OF status;
886
887 IF PG_DEBUG in ('Y', 'C') THEN
888 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.lock_fetch_f_trx_id()-' );
889 END IF;
890
891 EXCEPTION
892 WHEN OTHERS THEN
893 IF PG_DEBUG in ('Y', 'C') THEN
894 arp_standard.debug( 'EXCEPTION: ARP_TRANSACTION_HISTORY_PKG.lock_fetch_f_trx_id' );
895 END IF;
896 RAISE;
897 END lock_fetch_f_trx_id;
898 /*===========================================================================+
899 | PROCEDURE |
900 | nowaitlock_fetch_f_trx_id |
901 | |
902 | DESCRIPTION |
903 | Locks and fetches the current transaction history record from DB |
904 | related to transaction id given in record parameter. If the row is |
905 | already locked this procedure will return an error. |
906 | |
907 | SCOPE - PUBLIC |
908 | |
909 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
910 | |
911 | ARGUMENTS : IN: p_trh_rec Transaction history record |
912 | OUT: p_trh_rec Transaction history record |
913 | RETURNS : |
914 | NOTES |
915 | MODIFICATION HISTORY - Created by Jani Rautiainen - 18-JAN-2000 |
916 | |
917 +===========================================================================*/
918 PROCEDURE nowaitlock_fetch_f_trx_id(p_trh_rec IN OUT NOCOPY AR_TRANSACTION_HISTORY%ROWTYPE) IS
919 BEGIN
920 IF PG_DEBUG in ('Y', 'C') THEN
921 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.nowaitlock_fetch_f_trx_id()+' );
922 END IF;
923
924 SELECT *
925 INTO p_trh_rec
926 FROM AR_TRANSACTION_HISTORY
927 WHERE customer_trx_id = p_trh_rec.customer_trx_id
928 AND current_record_flag = 'Y'
929 FOR UPDATE OF status NOWAIT;
930
931 IF PG_DEBUG in ('Y', 'C') THEN
932 arp_standard.debug( 'ARP_TRANSACTION_HISTORY_PKG.nowaitlock_fetch_f_trx_id()-' );
933 END IF;
934
935 EXCEPTION
936 WHEN OTHERS THEN
937 IF PG_DEBUG in ('Y', 'C') THEN
938 arp_standard.debug( 'EXCEPTION: ARP_TRANSACTION_HISTORY_PKG.nowaitlock_fetch_f_trx_id' );
939 END IF;
940 RAISE;
941 END nowaitlock_fetch_f_trx_id;
942
943 BEGIN
944 /* Populate the package header level variables from global variables */
945 pg_request_id := arp_global.request_id;
946 pg_program_application_id := arp_global.program_application_id;
947 pg_program_id := arp_global.program_id;
948 pg_program_update_date := arp_global.program_update_date;
949 pg_last_updated_by := arp_global.last_updated_by;
950 pg_last_update_date := arp_global.last_update_date;
951 pg_last_update_login := arp_global.last_update_login;
952 pg_set_of_books_id := arp_global.set_of_books_id;
953
954 END ARP_TRANSACTION_HISTORY_PKG;