[Home] [Help]
PACKAGE BODY: APPS.ARP_PROC_TRANSACTION_HISTORY
Source
1 PACKAGE BODY ARP_PROC_TRANSACTION_HISTORY AS
2 /* $Header: ARTETRHB.pls 120.10.12010000.3 2008/08/03 08:23:09 vavenugo 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 SUBTYPE ae_doc_rec_type IS arp_acct_main.ae_doc_rec_type;
14
15 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
16
17 FUNCTION find_prev_accounted_id(p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE) RETURN NUMBER;
18
19 PROCEDURE insert_BR_ps(
20 p_trh_rec IN ar_transaction_history%ROWTYPE,
21 p_ps_id OUT NOCOPY ar_payment_schedules.payment_schedule_id%TYPE);
22
23 PROCEDURE calculate_BR_amounts(
24 p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE,
25 p_amount OUT NOCOPY NUMBER,
26 p_acctd_amount OUT NOCOPY NUMBER,
27 p_line_amount OUT NOCOPY NUMBER,
28 p_tax_amount OUT NOCOPY NUMBER,
29 p_freight_amount OUT NOCOPY NUMBER,
30 p_charges_amount OUT NOCOPY NUMBER);
31
32 FUNCTION previous_history_exists(p_customer_trx_id ra_customer_trx.customer_trx_id%TYPE) RETURN BOOLEAN;
33
34 /*===========================================================================+
35 | PROCEDURE |
36 | insert_transaction_history |
37 | |
38 | DESCRIPTION |
39 | Inserts a record into ar_transaction_history for bills receivable |
40 | transaction. If this is not the first history record the previous |
41 | record flags are updated. If this is the completion record |
42 | payment schedule is created for the BR. MRC information is created |
43 | if MRC functionality is enabled |
44 | |
45 | SCOPE - PUBLIC |
46 | |
47 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
48 | arp_util.debug |
49 | |
50 | ARGUMENTS : IN: p_trh_rec |
51 | OUT: p_transaction_history_id |
52 | IN/ OUT: |
53 | |
54 | RETURNS : NONE |
55 | |
56 | MODIFICATION HISTORY |
57 | 28-MAR-2000 Jani Rautiainen Created |
58 | |
59 +===========================================================================*/
60
61 PROCEDURE insert_transaction_history(p_trh_rec IN OUT NOCOPY ar_transaction_history%rowtype,
62 p_transaction_history_id OUT NOCOPY ar_transaction_history.transaction_history_id%type,
63 p_move_deferred_tax IN VARCHAR2 DEFAULT 'N') IS
64
65 l_ae_doc_rec ae_doc_rec_type;
66 l_old_trh_rec ar_transaction_history%ROWTYPE;
67 l_old_acctd_trh_rec ar_transaction_history%ROWTYPE;
68 l_prev_acctd_id ar_transaction_history.transaction_history_id%TYPE;
69 l_ps_id ar_payment_schedules.payment_schedule_id%TYPE;
70 --Bug# 2750340
71 l_xla_ev_rec ARP_XLA_EVENTS.XLA_EVENTS_TYPE;
72
73 /*Bug 7299779 -- vavenugo */
74 /* Variables declared for updating events */
75 l_event_source_info xla_events_pub_pkg.t_event_source_info;
76 l_event_id NUMBER;
77 l_security xla_events_pub_pkg.t_security;
78 l_event_count NUMBER;
79 l_trh_count NUMBER;
80
81 cursor update_event (p_trx_id ra_customer_trx.customer_trx_id%TYPE)
82 is
83 select distinct trh.event_id
84 from ar_transaction_history_all trh
85 where trh.customer_trx_id = p_trx_id and
86 trh.event_id is not null and /* This condition is to make sure that null events are not selected for update. Null events get inserted in TRH when fields like maturity date are updated */
87 not exists
88 ( select 'Y'
89 from ar_transaction_history_all trh_sub
90 where trh_sub.customer_trx_id = p_trx_id and
91 trh_sub.postable_flag ='Y' and
92 trh_sub.event_id = trh.event_id
93 );
94
95
96
97 BEGIN
98
99 IF PG_DEBUG in ('Y', 'C') THEN
100 arp_util.debug( 'arp_proc_transaction_history.insert_transaction_history()+');
101 END IF;
102
103 /*------------------------------------------------+
104 | All the validation and defaulting is done in |
105 | the BR API, so none is done in the entity |
106 | handler. |
107 +------------------------------------------------*/
108
109 /*--------------------------------------------------+
110 | Lock rows in other tables that reference this |
111 | customer_trx_id |
112 +--------------------------------------------------*/
113 arp_process_br_header.lock_transaction(p_trh_rec.customer_trx_id);
114
115 /*----------------------------------------+
116 | fetch previous record if one exists |
117 +----------------------------------------*/
118 IF previous_history_exists(p_trh_rec.customer_trx_id) THEN
119
120 l_old_trh_rec.customer_trx_id := p_trh_rec.customer_trx_id;
121 ARP_TRANSACTION_HISTORY_PKG.fetch_f_trx_id(l_old_trh_rec);
122
123 END IF;
124
125 /*--------------------------------------------------+
126 | If this is not the first history record, update |
127 | the previous records current flags |
128 +--------------------------------------------------*/
129
130 IF l_old_trh_rec.transaction_history_id IS NOT NULL THEN
131
132 /*-----------------------------------------------------------------+
133 | set the prv_trx_history_id to point to the previous record |
134 +-----------------------------------------------------------------*/
135 p_trh_rec.prv_trx_history_id := l_old_trh_rec.transaction_history_id;
136
137 /*--------------------------------------------------+
138 | If the new record created is postable update the |
139 | current_accounted_flag of previous record to 'N' |
140 +--------------------------------------------------*/
141
142 IF NVL(p_trh_rec.postable_flag,'N') = 'Y' THEN
143
144 /*--------------------------------------------------------------------+
145 | Check whether the previous record was the current accounted record |
146 +--------------------------------------------------------------------*/
147
148 IF NVL(l_old_trh_rec.current_accounted_flag,'N') = 'N' THEN
149 /*--------------------------------------------------------------------------+
150 | Previous record was not the accounted record, so find the correct record |
151 +--------------------------------------------------------------------------*/
152 l_prev_acctd_id := find_prev_accounted_id(l_old_trh_rec.customer_trx_id);
153
154 /*------------------------------------------+
155 | Update previous accounted history record |
156 | if one exists. If one does not exist this|
157 | we are inserting the first accounted row |
158 +------------------------------------------*/
159 IF l_prev_acctd_id is not null THEN
160
161 /*-----------------------------------------+
162 | Initialize the record with dummy values |
163 +-----------------------------------------*/
164 ARP_TRANSACTION_HISTORY_PKG.set_to_dummy(l_old_acctd_trh_rec);
165
166 /*----------------------------+
167 | Set the flag to be updated |
168 +----------------------------*/
169 l_old_acctd_trh_rec.current_accounted_flag := 'N';
170
171 /*--------------------------------------+
172 | Update the previous accounted record |
173 +--------------------------------------*/
174 ARP_PROC_TRANSACTION_HISTORY.update_transaction_history(l_old_acctd_trh_rec,
175 l_prev_acctd_id);
176
177 ELSE
178
179 /*-----------------------------------------+
180 | This is the first posted record |
181 +-----------------------------------------*/
182 p_trh_rec.first_posted_record_flag := 'Y';
183
184 END IF;
185 ELSE
186 /*------------------------------------------------------------------------+
187 | Previous record was the accounted record, so set the flag to update it |
188 +------------------------------------------------------------------------*/
189 l_old_trh_rec.current_accounted_flag := 'N';
190
191 END IF;
192
193 END IF;
194
195 /*------------------------------------------------------------+
196 | Update the current record indicator of the previous record |
197 +------------------------------------------------------------*/
198 l_old_trh_rec.current_record_flag := 'N';
199
200 /*-----------------------------+
201 | Update the previous record |
202 +-----------------------------*/
203 ARP_PROC_TRANSACTION_HISTORY.update_transaction_history(l_old_trh_rec,
204 l_old_trh_rec.transaction_history_id);
205
206 END IF;
207
208 /*---------------------------------------------------------------+
209 | Payment schedule needs to be created if the BR was completed |
210 +---------------------------------------------------------------*/
211 IF p_trh_rec.status = 'PENDING_REMITTANCE' AND p_trh_rec.event in ('COMPLETED','ACCEPTED') THEN
212
213 /*--------------------------+
214 | Create payment schedule |
215 +--------------------------*/
216 arp_proc_transaction_history.insert_BR_ps(p_trh_rec,l_ps_id);
217
218 END IF;
219
220 /*----------------------+
221 | call table-handler |
222 +----------------------*/
223
224 arp_transaction_history_pkg.insert_p(p_trh_rec, p_transaction_history_id);
225
226
227
228 /*--------------------------------------------+
229 | Call MRC logic to create MRC rows |
230 | This needs to be done before accounting |
231 | since the MRC trigger on ar_distributions |
232 | table expects for the MRC row to exists |
233 +--------------------------------------------*/
234
235 --{BUG#4301323
236 -- ARP_PROC_TRANSACTION_HISTORY.insert_mrc_transaction_hist(p_trh_rec,
237 -- p_transaction_history_id);
238 --}
239
240 --Bug# 2750340
241 /*--------------------------------------------+
242 | Need to call ARP XLA to create or update |
243 | the life cycle of a TH |
244 | This routine is called by all the TH WB |
245 | And it is a central place for TH lifecycle|
246 +--------------------------------------------*/
247 arp_standard.debug('p_transaction_history_id :'||p_transaction_history_id);
248
249 l_xla_ev_rec.xla_from_doc_id := p_transaction_history_id;
250 l_xla_ev_rec.xla_to_doc_id := p_transaction_history_id;
251 l_xla_ev_rec.xla_doc_table := 'TRH';
252 l_xla_ev_rec.xla_mode := 'O';
253 l_xla_ev_rec.xla_call := 'B';
254
255 -- Now call the stored program
256 arp_xla_events.create_events(l_xla_ev_rec);
257
258
259
260
261 /*----------------------------------------------------------------------+
262 | call accounting engine if needed. Accounting engine creates the |
263 | accounting in ar_distributions table, it also calls auto accounting |
264 | and deferred tax if needed |
265 +----------------------------------------------------------------------*/
266
267 IF NVL(p_trh_rec.postable_flag,'N') = 'Y' THEN
268
269 l_ae_doc_rec.document_type := 'BILLS_RECEIVABLE';
270 l_ae_doc_rec.document_id := p_transaction_history_id;
271 l_ae_doc_rec.accounting_entity_level := 'ONE';
272 l_ae_doc_rec.source_table := 'TH';
273 l_ae_doc_rec.source_id := p_transaction_history_id;
274 l_ae_doc_rec.event := p_trh_rec.event;
275 l_ae_doc_rec.deferred_tax := p_move_deferred_tax;
276 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
277
278 ELSIF (p_trh_rec.status = 'INCOMPLETE' AND l_old_trh_rec.status = 'PENDING_REMITTANCE') THEN
279
280 /*----------------------------------------------------------+
281 | If the Bills was incompleted we need to delete |
282 | the accounting for previous PENDING_REMITTANCE |
283 | record since This scenario is only possible if |
284 | the BR does not have any activities or reclassifications |
285 | on it. The validation is done in the calling |
286 | functionality |
287 +----------------------------------------------------------*/
288
289 /*----------------------------------------------------------+
290 | Since the previous history is not necessarily postable |
291 | accounting needs to be reversed on the previout accounted|
292 | transaction history record |
293 +----------------------------------------------------------*/
294 l_prev_acctd_id := find_prev_accounted_id(l_old_trh_rec.customer_trx_id);
295
296 l_ae_doc_rec.document_type := 'BILLS_RECEIVABLE';
297 l_ae_doc_rec.document_id := l_prev_acctd_id;
298 l_ae_doc_rec.accounting_entity_level := 'ONE';
299 l_ae_doc_rec.source_table := 'TH';
300 l_ae_doc_rec.source_id := l_prev_acctd_id;
301 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
302
303
304 /*----------------------------------------------------------+
305 | If the Bills was incompleted we need to update also the |
306 | the other flags from the previous PENDING_REMITTANCE |
307 | record since the accounting engine will delete the |
308 | accounting rows for it. This scenario is only possible if|
309 | the BR does not have any activities or reclassifications |
310 | on it. The validation is done in the calling |
311 | functionality |
312 +----------------------------------------------------------*/
313
314 /*-----------------------------------------+
315 | Initialize the record with dummy values |
316 +-----------------------------------------*/
317 ARP_TRANSACTION_HISTORY_PKG.set_to_dummy(l_old_acctd_trh_rec);
318
319 /*----------------------------+
320 | Set the flag to be updated |
321 +----------------------------*/
322 l_old_acctd_trh_rec.current_accounted_flag := 'N';
323 l_old_acctd_trh_rec.postable_flag := 'N';
324 l_old_acctd_trh_rec.first_posted_record_flag := 'N';
325
326 /*--------------------------------------+
327 | Update the previous accounted record |
328 +--------------------------------------*/
329 ARP_PROC_TRANSACTION_HISTORY.update_transaction_history(l_old_acctd_trh_rec,
330 l_prev_acctd_id);
331
332
333 END IF;
334
335
336
337 /* Bug 7299779 (refer bug for description of fix) - vavenugo */
338
339 /* Identify and update the events only when the status changes from INCOMPLETE TO PENDING_REMITTANCE */
340 IF (p_trh_rec.status = 'PENDING_REMITTANCE' AND l_old_trh_rec.status = 'INCOMPLETE') THEN
341
342 select count(distinct trh.event_id)
343 into l_event_count
344 from ar_transaction_history_all trh
345 where trh.customer_trx_id = p_trh_rec.customer_trx_id and
346 not exists
347 ( select 'Y'
348 from ar_transaction_history_all trh_sub
349 where trh_sub.customer_trx_id = p_trh_rec.customer_trx_id and
350 trh_sub.postable_flag ='Y' and
351 trh_sub.event_id = trh.event_id
352 );
353
354 If l_event_count > 0 then
355
356 /* Values selected to populate the IN parameters of the xla procedure */
357 select xet.legal_entity_id legal_entity_id,
358 trx.SET_OF_BOOKS_ID set_of_books_id,
359 xet.entity_code entity_code
360 into
361 l_event_source_info.legal_entity_id,
362 l_event_source_info.ledger_id,
363 l_event_source_info.entity_type_code
364 from
365 ra_customer_trx trx ,
366 xla_transaction_entities_upg xet
367 where trx.customer_trx_id = p_trh_rec.customer_trx_id
368 and trx.customer_trx_id = xet.source_id_int_1
369 and xet.entity_code ='BILLS_RECEIVABLE'
370 AND xet.application_id = 222
371 AND trx.SET_OF_BOOKS_ID = xet.LEDGER_ID;
372
373 l_event_source_info.application_id := 222;
374 l_event_source_info.source_id_int_1 := p_trh_rec.customer_trx_id;
375 l_security.security_id_int_1 := p_trh_rec.org_id;
376
377 /* Open the cursor containing the events to be updated and call the xla procedure for every event */
378
379 open update_event (p_trh_rec.customer_trx_id);
380 loop
381 fetch update_event into l_event_id;
382 exit when update_event%NOTFOUND;
383 xla_events_pub_pkg.update_event
384 ( p_event_source_info => l_event_source_info,
385 p_event_id => l_event_id,
386 p_event_status_code => 'N',
387 p_valuation_method => null,
388 p_security_context => l_security );
389 end loop;
390 end if;
391
392 END IF;
393
394 /* End Bug 7299779 - vavenugo */
395
396 IF PG_DEBUG in ('Y', 'C') THEN
397 arp_util.debug( 'arp_proc_transaction_history.insert_transaction_history()-');
398 END IF;
399
400 EXCEPTION
401 WHEN OTHERS THEN
402 IF PG_DEBUG in ('Y', 'C') THEN
403 arp_util.debug( 'EXCEPTION: arp_proc_transaction_history.insert_transaction_history()');
404 END IF;
405 RAISE;
406
407 END insert_transaction_history;
408
409 /*===========================================================================+
410 | PROCEDURE |
411 | update_transaction_history |
412 | |
413 | DESCRIPTION |
414 | Updates a record into ar_transaction_history for bills receivable |
415 | transaction. MRC information is updated if MRC functionality is enabled|
416 | |
417 | SCOPE - PUBLIC |
418 | |
419 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
420 | arp_util.debug |
421 | |
422 | ARGUMENTS : IN: |
423 | OUT: |
424 | IN/ OUT: p_trx_rec |
425 | |
426 | RETURNS : NONE |
427 | |
428 | NOTES |
429 | |
430 | MODIFICATION HISTORY |
431 | 28-MAR-2000 Jani Rautiainen Created |
432 | |
433 +===========================================================================*/
434
435 PROCEDURE update_transaction_history(p_trh_rec IN OUT NOCOPY ar_transaction_history%rowtype,
436 p_transaction_history_id IN ar_transaction_history.transaction_history_id%TYPE) IS
437
438 CURSOR trh_customer_trx_cur IS
439 SELECT customer_trx_id
440 FROM ar_transaction_history
441 WHERE transaction_history_id = p_transaction_history_id;
442
443 trh_customer_trx_rec trh_customer_trx_cur%ROWTYPE;
444 l_customer_trx_id ar_transaction_history.customer_trx_id%TYPE;
445 BEGIN
446
447 IF PG_DEBUG in ('Y', 'C') THEN
448 arp_util.debug( 'arp_proc_transaction_history.update_transaction_history()+');
449 END IF;
450
451 /*------------------------------------------------+
452 | All the validation and defaulting is done in |
453 | the BR API, so none is done in the entity |
454 | handler. |
455 +------------------------------------------------*/
456
457 /*------------------------------------------------+
458 | If customer_trx_id is not passed we need to |
459 | fetch it. |
460 +------------------------------------------------*/
461 IF p_trh_rec.customer_trx_id IS NULL OR p_trh_rec.customer_trx_id = AR_NUMBER_DUMMY THEN
462
463 OPEN trh_customer_trx_cur;
464 FETCH trh_customer_trx_cur INTO trh_customer_trx_rec;
465 CLOSE trh_customer_trx_cur;
466
467 l_customer_trx_id := trh_customer_trx_rec.customer_trx_id;
468
469 ELSE
470
471 l_customer_trx_id := p_trh_rec.customer_trx_id;
472
473 END IF;
474
475 /*-----------------------------------------------------------------+
476 | Lock rows in other tables that reference this customer_trx_id |
477 +-----------------------------------------------------------------*/
478 arp_process_br_header.lock_transaction(l_customer_trx_id);
479
480 /*----------------------+
481 | call table-handler |
482 +----------------------*/
483
484 arp_transaction_history_pkg.update_p(p_trh_rec, p_transaction_history_id);
485
486 /*-------------------------------------+
487 | None of MRC columns can be updated |
488 +-------------------------------------*/
489
490 IF PG_DEBUG in ('Y', 'C') THEN
491 arp_util.debug( 'arp_proc_transaction_history.update_transaction_history()-');
492 END IF;
493
494 EXCEPTION
495 WHEN OTHERS THEN
496 IF PG_DEBUG in ('Y', 'C') THEN
497 arp_util.debug( 'EXCEPTION: arp_proc_transaction_history.update_transaction_history()');
498 END IF;
499 RAISE;
500
501 END update_transaction_history;
502
503 /*===========================================================================+
504 | PROCEDURE |
505 | delete_transaction_history |
506 | |
507 | DESCRIPTION |
508 | Deletes row from ar_transaction_history for Bills Receivable |
509 | Transaction. MRC information is deleted if MRC functionality is enabled|
510 | |
511 | SCOPE - PUBLIC |
512 | |
513 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
514 | arp_util.debug |
515 | |
516 | ARGUMENTS : IN: p_transaction_history_id |
517 | OUT: |
518 | IN/ OUT: |
519 | |
520 | RETURNS : NONE |
521 | |
522 | NOTES |
523 | |
524 | MODIFICATION HISTORY |
525 | 28-MAR-2000 Jani Rautiainen Created |
526 | |
527 +===========================================================================*/
528 PROCEDURE delete_transaction_history(p_transaction_history_id IN ar_transaction_history.transaction_history_id%TYPE) IS
529
530 BEGIN
531
532 IF PG_DEBUG in ('Y', 'C') THEN
533 arp_util.debug( 'arp_proc_transaction_history.delete_transaction_history()+');
534 END IF;
535
536 /*--------------------------------+
537 | lock history record |
538 +-------------------------------*/
539 arp_transaction_history_pkg.lock_p(p_trh_id => p_transaction_history_id);
540
541 /*--------------------------------+
542 | delete the history record |
543 +-------------------------------*/
544 arp_transaction_history_pkg.delete_p(p_trh_id => p_transaction_history_id);
545
546 /*---------------------------------------------------+
547 | Delete all accounting related to the history row |
548 +---------------------------------------------------*/
549 arp_proc_transaction_history.delete_transaction_hist_dist(p_transaction_history_id);
550
551 /*------------------------------------------+
552 | Call MRC logic to delete MRC rows |
553 +------------------------------------------*/
554 --{BUG#4301323
555 -- ARP_PROC_TRANSACTION_HISTORY.delete_mrc_transaction_hist(p_transaction_history_id);
556 --}
557
558 --Bug# 2750340
559 arp_xla_events.delete_event( p_document_id => p_transaction_history_id,
560 p_doc_table => 'TRH');
561
562
563 IF PG_DEBUG in ('Y', 'C') THEN
564 arp_util.debug( 'arp_proc_transaction_history.delete_transaction_history()-');
565 END IF;
566
567 EXCEPTION
568 WHEN OTHERS THEN
569 IF PG_DEBUG in ('Y', 'C') THEN
570 arp_util.debug( 'EXCEPTION: arp_proc_transaction_history.delete_transaction_history()');
571 END IF;
572 RAISE;
573
574 END delete_transaction_history;
575
576 /*===========================================================================+
577 | PROCEDURE |
578 | delete_transaction_history |
579 | |
580 | DESCRIPTION |
581 | Deletes distribution rows from ar_distributions for given transaction |
582 | history record. |
583 | |
584 | SCOPE - PUBLIC |
585 | |
586 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
587 | arp_util.debug |
588 | |
589 | ARGUMENTS : IN: p_transaction_history_id |
590 | OUT: |
591 | IN/ OUT: |
592 | |
593 | RETURNS : NONE |
594 | |
595 | NOTES |
596 | |
597 | MODIFICATION HISTORY |
598 | 28-MAR-2000 Jani Rautiainen Created |
599 | 10-MAY-2000 Debbie Jancis Added call to delete from |
600 | ar_distributions after call to delete |
601 | using table handler since that will |
602 | not be modified to handle MRC ar |
603 | distributions deletes. Added call to |
604 | ar_mrc_engine for processing. |
605 +===========================================================================*/
606 PROCEDURE delete_transaction_hist_dist(p_transaction_history_id IN ar_transaction_history.transaction_history_id%TYPE) IS
607
608 /*----------------------------------------+
609 | Cursor for distribution records |
610 | related to the transaction history |
611 +----------------------------------------*/
612 /* Start FP Bug 5741803 chng the defination to check for source table */
613 CURSOR distribution_cur IS
614 select dist.line_id
615 from ar_distributions dist
616 where source_id = p_transaction_history_id
617 and source_table = 'TH';
618 /* End FP Bug 5741803 SPDIXIT */
619 distribution_rec distribution_cur%ROWTYPE;
620
621 BEGIN
622
623 IF PG_DEBUG in ('Y', 'C') THEN
624 arp_util.debug( 'arp_proc_transaction_history.delete_transaction_hist_dist()+');
625 END IF;
626
627 /*----------------------------------------+
628 | Loop through all distribution records |
629 | related to the transaction history |
630 +----------------------------------------*/
631
632 FOR distribution_rec IN distribution_cur LOOP
633
634 /*-----------------------------+
635 | Delete distribution record |
636 +-----------------------------*/
637 arp_distributions_pkg.delete_p(distribution_rec.line_id);
638
639 END LOOP;
640
641 IF PG_DEBUG in ('Y', 'C') THEN
642 arp_util.debug( 'arp_proc_transaction_history.delete_transaction_hist_dist()-');
643 END IF;
644
645 EXCEPTION
646 WHEN OTHERS THEN
647 IF PG_DEBUG in ('Y', 'C') THEN
648 arp_util.debug( 'EXCEPTION: arp_proc_transaction_history.delete_transaction_hist_dist()');
649 END IF;
650 RAISE;
651
652 END delete_transaction_hist_dist;
653
654 /*===========================================================================+
655 | PROCEDURE |
656 | find_prev_accounted_id |
657 | |
658 | DESCRIPTION |
659 | Finds previous accounted record. Note that this will return NULL |
660 | if previous accounted record does not exist. |
661 | |
662 | SCOPE - PUBLIC |
663 | |
664 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
665 | arp_util.debug |
666 | |
667 | ARGUMENTS : IN: p_customer_trx_id |
668 | OUT: |
669 | IN/ OUT: |
670 | |
671 | RETURNS : NONE |
672 | |
673 | NOTES |
674 | |
675 | MODIFICATION HISTORY |
676 | 28-MAR-2000 Jani Rautiainen Created |
677 | |
678 +===========================================================================*/
679 FUNCTION find_prev_accounted_id(p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE) RETURN NUMBER IS
680
681 /*----------------------------------+
682 | Cursor for finding the previous |
683 | accounted transaction history |
684 +----------------------------------*/
685 CURSOR prev_acctd_trh_cur IS
686 select transaction_history_id
687 from ar_transaction_history
688 where current_accounted_flag = 'Y'
689 and customer_trx_id = p_customer_trx_id;
690
691 prev_acctd_trh_rec prev_acctd_trh_cur%ROWTYPE;
692
693 BEGIN
694
695 IF PG_DEBUG in ('Y', 'C') THEN
696 arp_util.debug('arp_proc_transaction_history.find_prev_accounted_id()+');
697 END IF;
698
699 /*---------------------------------------------------+
700 | Fetch the previous accounted transaction history |
701 +---------------------------------------------------*/
702 OPEN prev_acctd_trh_cur;
703 FETCH prev_acctd_trh_cur INTO prev_acctd_trh_rec;
704 CLOSE prev_acctd_trh_cur;
705
706 RETURN prev_acctd_trh_rec.transaction_history_id;
707
708 IF PG_DEBUG in ('Y', 'C') THEN
709 arp_util.debug('arp_proc_transaction_history.find_prev_accounted_id()-');
710 END IF;
711
712 EXCEPTION
713 WHEN OTHERS THEN
714 IF PG_DEBUG in ('Y', 'C') THEN
715 arp_util.debug('EXCEPTION: arp_proc_transaction_history.find_prev_accounted_id()');
716 END IF;
717 RAISE;
718
719 END find_prev_accounted_id;
720
721 /*===========================================================================+
722 | PROCEDURE |
723 | insert_BR_ps |
724 | |
725 | DESCRIPTION |
726 | Inserts an payment schedule for Bills Receivable |
727 | |
728 | SCOPE - PUBLIC |
729 | |
730 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
731 | arp_util.debug |
732 | |
733 | ARGUMENTS : IN: p_trh_rec - Record containing the transaction history |
734 | recor being created. |
735 | OUT: p_ps_id - PS id of the record created |
736 | IN/ OUT: |
737 | |
738 | RETURNS : NONE |
739 | |
740 | NOTES |
741 | |
742 | MODIFICATION HISTORY |
743 | 28-MAR-2000 Jani Rautiainen Created |
744 | 25-MAY-2005 V Crisostomo SSA-R12 : add org_id |
745 | |
746 +===========================================================================*/
747 PROCEDURE insert_BR_ps(
748 p_trh_rec IN ar_transaction_history%ROWTYPE,
749 p_ps_id OUT NOCOPY ar_payment_schedules.payment_schedule_id%TYPE) IS
750
751 l_trx_rec ra_customer_trx%ROWTYPE;
752 l_ps_rec ar_payment_schedules%ROWTYPE;
753 l_ps_id ar_payment_schedules.payment_schedule_id%TYPE;
754
755 BEGIN
756
757 IF PG_DEBUG in ('Y', 'C') THEN
758 arp_standard.debug('arp_proc_transaction_history.insert_BR_ps()+');
759 END IF;
760
761 /*------------------------------------+
762 | Fetch the transaction information |
763 +------------------------------------*/
764 arp_ct_pkg.fetch_p(l_trx_rec, p_trh_rec.customer_trx_id);
765
766
767 /*-------------------------------------------------------------------------+
768 | fill record columns with data from transaction and transaction history |
769 +-------------------------------------------------------------------------*/
770 l_ps_rec.due_date := l_trx_rec.term_due_date;
771 l_ps_rec.gl_date := p_trh_rec.gl_date;
772 l_ps_rec.gl_date_closed := TO_DATE('12/31/4712','MM/DD/YYYY');
773 l_ps_rec.actual_date_closed := TO_DATE('12/31/4712','MM/DD/YYYY');
774 l_ps_rec.trx_date := l_trx_rec.trx_date;
775 l_ps_rec.number_of_due_dates := 1;
776 l_ps_rec.org_id := l_trx_rec.org_id;
777
778 /*-------------------------------------------------------------------------+
779 | Calculate the payment schedule amount using shadow adjustment created |
780 | against the assignments. |
781 +-------------------------------------------------------------------------*/
782 arp_proc_transaction_history.calculate_BR_amounts(
783 l_trx_rec.customer_trx_id,
784 l_ps_rec.amount_due_original,
785 l_ps_rec.acctd_amount_due_remaining,
786 l_ps_rec.amount_line_items_original,
787 l_ps_rec.tax_original,
788 l_ps_rec.freight_original,
789 l_ps_rec.receivables_charges_charged);
790
791 l_ps_rec.amount_due_remaining := l_ps_rec.amount_due_original;
792 l_ps_rec.amount_line_items_remaining := l_ps_rec.amount_line_items_original;
793 l_ps_rec.tax_remaining := l_ps_rec.tax_original;
794 l_ps_rec.freight_remaining := l_ps_rec.freight_original;
795 l_ps_rec.receivables_charges_remaining := l_ps_rec.receivables_charges_charged;
796 l_ps_rec.amount_applied := NULL;
797 l_ps_rec.amount_credited := NULL;
798
799 l_ps_rec.status := 'OP';
800 l_ps_rec.class := 'BR';
801 l_ps_rec.trx_number := l_trx_rec.trx_number;
802 l_ps_rec.cust_trx_type_id := l_trx_rec.cust_trx_type_id;
803 l_ps_rec.customer_id := l_trx_rec.drawee_id;
804 l_ps_rec.customer_site_use_id := l_trx_rec.drawee_site_use_id;
805 l_ps_rec.customer_trx_id := l_trx_rec.customer_trx_id;
806 l_ps_rec.invoice_currency_code := l_trx_rec.invoice_currency_code;
807 l_ps_rec.exchange_rate_type := l_trx_rec.exchange_rate_type;
808 l_ps_rec.exchange_rate := l_trx_rec.exchange_rate;
809 l_ps_rec.exchange_date := l_trx_rec.exchange_date;
810 l_ps_rec.term_id := NULL;
811 l_ps_rec.terms_sequence_number := 1;
812
813 /*--------------------------------------------+
814 | insert record into payment schedule table |
815 +--------------------------------------------*/
816 arp_ps_pkg.insert_p(l_ps_rec, l_ps_id);
817
818 p_ps_id := l_ps_id;
819
820 IF PG_DEBUG in ('Y', 'C') THEN
821 arp_standard.debug('arp_proc_transaction_history.insert_BR_ps()-');
822 END IF;
823
824 EXCEPTION
825 WHEN OTHERS THEN
826 IF PG_DEBUG in ('Y', 'C') THEN
827 arp_standard.debug('EXCEPTION: arp_proc_transaction_history.insert_BR_ps()');
828 END IF;
829 RAISE;
830
831 END insert_BR_ps;
832
833 /*===========================================================================+
834 | PROCEDURE |
835 | calculate_BR_amounts |
836 | |
837 | DESCRIPTION |
838 | Calculate the payment schedule amount using shadow adjustment created |
839 | against the assignments. |
840 | |
841 | SCOPE - PUBLIC |
842 | |
843 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
844 | arp_util.debug |
845 | |
846 | ARGUMENTS : IN: p_customer_trx_id - Transaction ID |
847 | |
848 | OUT: p_amount - Sum of adjustments |
849 | p_acctd_amount - Sum of adjustments in functional |
850 | currency. |
851 | p_line_amount - Sum of adjustment line amounts |
852 | p_tax_amount - Sum of adjustment tax amounts |
853 | p_freight_amount - Sum of adjustment freight amounts |
854 | p_charges_amount - Sum of adjustment charges amounts |
855 | IN/ OUT: |
856 | |
857 | RETURNS : NONE |
858 | |
859 | MODIFICATION HISTORY |
860 | 28-MAR-2000 Jani Rautiainen Created |
861 | |
862 +===========================================================================*/
863 PROCEDURE calculate_BR_amounts(
864 p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE,
865 p_amount OUT NOCOPY NUMBER,
866 p_acctd_amount OUT NOCOPY NUMBER,
867 p_line_amount OUT NOCOPY NUMBER,
868 p_tax_amount OUT NOCOPY NUMBER,
869 p_freight_amount OUT NOCOPY NUMBER,
870 p_charges_amount OUT NOCOPY NUMBER) IS
871
872 /*----------------------------------------+
873 | Cursor for totals on the adjustments. |
874 | The currency and exchange rate has to |
875 | be exactly the same on all assignments|
876 | so the totals can be summed together |
877 +----------------------------------------*/
878 CURSOR br_amounts_cur IS
879 SELECT sum(nvl(amount,0)) total_amount,
880 sum(nvl(acctd_amount,0)) total_acctd_amount,
881 sum(nvl(line_adjusted,0)) total_line,
882 sum(nvl(freight_adjusted,0)) total_freight,
883 sum(nvl(tax_adjusted,0)) total_tax,
884 sum(nvl(receivables_charges_adjusted,0)) total_charges
885 FROM ra_customer_trx_lines ctl,
886 ar_adjustments adj
887 WHERE ctl.customer_trx_id = p_customer_trx_id
888 AND adj.adjustment_id = ctl.br_adjustment_id;
889
890 br_amounts_rec br_amounts_cur%ROWTYPE;
891
892 BEGIN
893
894 IF PG_DEBUG in ('Y', 'C') THEN
895 arp_standard.debug('arp_proc_transaction_history.calculate_BR_amounts()+');
896 END IF;
897
898 /*---------------------------------------+
899 | Fetch the totals on the adjustments |
900 +---------------------------------------*/
901 OPEN br_amounts_cur;
902 FETCH br_amounts_cur INTO br_amounts_rec;
903 CLOSE br_amounts_cur;
904
905 /*------------------+
906 | Return results |
907 +------------------*/
908 p_amount := -nvl(br_amounts_rec.total_amount,0);
909 p_acctd_amount := -nvl(br_amounts_rec.total_acctd_amount,0);
910 p_line_amount := -nvl(br_amounts_rec.total_line,0);
911 p_tax_amount := -nvl(br_amounts_rec.total_tax,0);
912 p_freight_amount := -nvl(br_amounts_rec.total_freight,0);
913 p_charges_amount := -nvl(br_amounts_rec.total_charges,0);
914
915 IF PG_DEBUG in ('Y', 'C') THEN
916 arp_standard.debug('arp_proc_transaction_history.calculate_BR_amounts()-');
917 END IF;
918
919 EXCEPTION
920 WHEN OTHERS THEN
921 IF PG_DEBUG in ('Y', 'C') THEN
922 arp_standard.debug('EXCEPTION: arp_proc_transaction_history.calculate_BR_amounts()');
923 END IF;
924 RAISE;
925
926 END calculate_BR_amounts;
927
928
929
930
931 /*===========================================================================+
932 | PROCEDURE |
933 | previous_history_exists |
934 | |
935 | DESCRIPTION |
936 | This function is used to find out NOCOPY whether an previous transaction |
937 | history record exists. |
938 | |
939 | SCOPE - PUBLIC |
940 | |
941 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
942 | arp_util.debug |
943 | |
944 | ARGUMENTS : IN: p_customer_trx_id |
945 | OUT: |
946 | IN/ OUT: |
947 | |
948 | RETURNS : TRUE - If a previous transaction history record exists |
949 | FALSE - If previous transaction history record does not exist|
950 | |
951 | NOTES |
952 | |
953 | MODIFICATION HISTORY |
954 | 28-MAR-2000 Jani Rautiainen Created |
955 | |
956 +===========================================================================*/
957 FUNCTION previous_history_exists(p_customer_trx_id ra_customer_trx.customer_trx_id%TYPE) RETURN BOOLEAN IS
958
959 CURSOR previous_history_exists_cur IS
960 SELECT 'exists'
961 FROM ar_transaction_history
962 WHERE customer_trx_id = p_customer_trx_id
963 AND current_record_flag = 'Y';
964
965 previous_history_exists_rec previous_history_exists_cur%ROWTYPE;
966
967 BEGIN
968 IF PG_DEBUG in ('Y', 'C') THEN
969 arp_util.debug('ARP_PROC_TRANSACTION_HISTORY.previous_history_exists()+');
970 END IF;
971
972 OPEN previous_history_exists_cur;
973 FETCH previous_history_exists_cur INTO previous_history_exists_rec;
974
975 IF previous_history_exists_cur%NOTFOUND THEN
976 CLOSE previous_history_exists_cur;
977 RETURN FALSE;
978 END IF;
979
980 CLOSE previous_history_exists_cur;
981
982 IF PG_DEBUG in ('Y', 'C') THEN
983 arp_util.debug('ARP_PROC_TRANSACTION_HISTORY.previous_history_exists()-');
984 END IF;
985
986 RETURN TRUE;
987
988 END previous_history_exists;
989
990 /*===========================================================================+
991 | PROCEDURE |
992 | create_trh_for_receipt_act |
993 | |
994 | DESCRIPTION |
995 | This procedure creates the CLOSED / UNPAID / PENDING_REMITTANCE record |
996 | when an action takes place on a receipt applied to BR transaction |
997 | This should only be used if receipt application / unapplication /reversal|
998 | does not cause reclassification accounting on the BR. This is intended |
999 | to be used from the RWB when doing actions on a receipt applied to a BR |
1000 | This is also called from the BR Housekeeper program to create the BR |
1001 | CLOSED record, since no reclassification happens in that case. |
1002 | |
1003 | SCOPE - PUBLIC |
1004 | |
1005 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1006 | arp_util.debug |
1007 | |
1008 | ARGUMENTS : IN: l_old_ps_rec Image of the payment schedule before |
1009 | receipt impacted it. |
1010 | p_app_rec The APP record which impacted BR trx |
1011 | p_called_from Information from where called from |
1012 | OUT: |
1013 | IN/ OUT: |
1014 | |
1015 | RETURNS : NONE |
1016 | |
1017 | NOTES |
1018 | |
1019 | MODIFICATION HISTORY |
1020 | 19-JUL-2000 Jani Rautiainen Created |
1021 | |
1022 +===========================================================================*/
1023 procedure create_trh_for_receipt_act(p_old_ps_rec IN ar_payment_schedules%ROWTYPE,
1024 p_app_rec IN ar_receivable_applications%ROWTYPE,
1025 p_called_from IN VARCHAR2) IS
1026
1027 l_new_ps_rec ar_payment_schedules%ROWTYPE;
1028 l_trh_rec ar_transaction_history%ROWTYPE;
1029 l_transaction_history_id ar_transaction_history.transaction_history_id%TYPE;
1030
1031 BEGIN
1032 IF PG_DEBUG in ('Y', 'C') THEN
1033 arp_util.debug('ARP_PROC_TRANSACTION_HISTORY.create_trh_for_receipt_act()+');
1034 END IF;
1035
1036 /*--------------------------------------------+
1037 | Initialize the transaction history record |
1038 | with common information |
1039 +--------------------------------------------*/
1040 l_trh_rec.customer_trx_id := p_old_ps_rec.customer_trx_id;
1041 l_trh_rec.current_record_flag := 'Y';
1042 l_trh_rec.current_accounted_flag := 'N';
1043 l_trh_rec.postable_flag := 'N';
1044 l_trh_rec.first_posted_record_flag := 'N';
1045 l_trh_rec.posting_control_id := -3;
1046 l_trh_rec.gl_posted_date := NULL;
1047 l_trh_rec.created_from := p_called_from;
1048 l_trh_rec.trx_date := p_app_rec.apply_date;
1049 l_trh_rec.gl_date := p_app_rec.gl_date;
1050 l_trh_rec.comments := p_app_rec.comments;
1051 l_trh_rec.maturity_date := p_old_ps_rec.due_date;
1052 l_trh_rec.batch_id := NULL;
1053
1054 /*--------------------------------------------------+
1055 | Fetch the updated image of the payment schedule |
1056 +--------------------------------------------------*/
1057 arp_ps_pkg.fetch_p( p_old_ps_rec.payment_schedule_id, l_new_ps_rec );
1058
1059 IF PG_DEBUG in ('Y', 'C') THEN
1060 arp_util.debug('create_trh_for_receipt_act: ' || 'old_status = '||p_old_ps_rec.status);
1061 arp_util.debug('create_trh_for_receipt_act: ' || 'new_status = '||l_new_ps_rec.status);
1062 END IF;
1063
1064 IF NVL(p_old_ps_rec.status,'OP') = 'OP' AND NVL(l_new_ps_rec.status,'OP') = 'CL'
1065 OR NVL(p_old_ps_rec.status,'OP') = 'CL' AND NVL(l_new_ps_rec.status,'OP') = 'OP'
1066 THEN
1067
1068 IF NVL(p_old_ps_rec.status,'OP') = 'CL' AND NVL(l_new_ps_rec.status,'OP') = 'OP' THEN
1069
1070 /*-----------------------------------------------------------------+
1071 | Payment schedule was closed, check whether BR transaction has |
1072 | passed the maturity date |
1073 +-----------------------------------------------------------------*/
1074 IF trunc(p_old_ps_rec.due_date) >= trunc(SYSDATE) THEN
1075
1076 /*-----------------------------------------------------------+
1077 | BR has NOT passed maturity date, so record created with |
1078 | PENDING_REMITTANCE / UNPAID |
1079 +-----------------------------------------------------------*/
1080 l_trh_rec.status := 'PENDING_REMITTANCE';
1081 l_trh_rec.event := 'UNPAID';
1082
1083 ELSE
1084
1085 /*-------------------------------------------------------+
1086 | BR has passed maturity date, so record created with |
1087 | PENDING_REMITTANCE / UNPAID |
1088 +-------------------------------------------------------*/
1089 l_trh_rec.status := 'UNPAID';
1090 l_trh_rec.event := 'UNPAID';
1091
1092 END IF;
1093
1094 ELSIF NVL(p_old_ps_rec.status,'OP') = 'OP' AND NVL(l_new_ps_rec.status,'OP') = 'CL' THEN
1095
1096 /*-----------------------------------------------------------------+
1097 | Payment schedule was opened, create transaction history record |
1098 +-----------------------------------------------------------------*/
1099
1100 l_trh_rec.status := 'CLOSED';
1101 l_trh_rec.event := 'CLOSED';
1102
1103 END IF;
1104
1105 /*------------------------------------+
1106 | Cannot insert NULL into trx_date |
1107 +------------------------------------*/
1108 IF l_trh_rec.trx_date IS NULL THEN
1109
1110 l_trh_rec.trx_date := SYSDATE;
1111
1112 END IF;
1113
1114 /*-------------------------------------------+
1115 | Call entity handler to create the record |
1116 +-------------------------------------------*/
1117 IF PG_DEBUG in ('Y', 'C') THEN
1118 arp_util.debug('create_trh_for_receipt_act: ' || 'l_trh_rec.customer_trx_id = '||to_char(l_trh_rec.customer_trx_id));
1119 arp_util.debug('create_trh_for_receipt_act: ' || 'l_trh_rec.current_record_flag = '||l_trh_rec.current_record_flag);
1120 arp_util.debug('create_trh_for_receipt_act: ' || 'l_trh_rec.current_accounted_flag = '||l_trh_rec.current_accounted_flag);
1121 arp_util.debug('create_trh_for_receipt_act: ' || 'l_trh_rec.postable_flag = '||l_trh_rec.postable_flag);
1122 arp_util.debug('create_trh_for_receipt_act: ' || 'l_trh_rec.first_posted_record_flag = '||l_trh_rec.first_posted_record_flag);
1123 arp_util.debug('create_trh_for_receipt_act: ' || 'l_trh_rec.posting_control_id = '||to_char(l_trh_rec.posting_control_id));
1124 arp_util.debug('create_trh_for_receipt_act: ' || 'l_trh_rec.gl_posted_date = '||to_char(l_trh_rec.gl_posted_date));
1125 arp_util.debug('create_trh_for_receipt_act: ' || 'l_trh_rec.created_from = '||l_trh_rec.created_from);
1126 arp_util.debug('create_trh_for_receipt_act: ' || 'l_trh_rec.trx_date = '||to_char(l_trh_rec.trx_date));
1127 arp_util.debug('create_trh_for_receipt_act: ' || 'l_trh_rec.gl_date = '||to_char(l_trh_rec.gl_date));
1128 arp_util.debug('create_trh_for_receipt_act: ' || 'l_trh_rec.comments = '||l_trh_rec.comments);
1129 arp_util.debug('create_trh_for_receipt_act: ' || 'l_trh_rec.maturity_date = '||to_char(l_trh_rec.maturity_date));
1130 arp_util.debug('create_trh_for_receipt_act: ' || 'l_trh_rec.batch_id = '||to_char(l_trh_rec.batch_id));
1131 arp_util.debug('create_trh_for_receipt_act: ' || 'l_trh_rec.status = '||l_trh_rec.status);
1132 arp_util.debug('create_trh_for_receipt_act: ' || 'l_trh_rec.event = '||l_trh_rec.event);
1133 arp_util.debug('create_trh_for_receipt_act: ' || 'l_trh_rec.trx_date = '||to_char(l_trh_rec.trx_date));
1134 END IF;
1135
1136 ARP_PROC_TRANSACTION_HISTORY.insert_transaction_history(l_trh_rec, l_transaction_history_id);
1137
1138 END IF;
1139 IF PG_DEBUG in ('Y', 'C') THEN
1140 arp_util.debug('ARP_PROC_TRANSACTION_HISTORY.create_trh_for_receipt_act()+');
1141 END IF;
1142
1143 EXCEPTION
1144 WHEN OTHERS THEN
1145 IF PG_DEBUG in ('Y', 'C') THEN
1146 arp_util.debug('ARP_PROC_TRANSACTION_HISTORY.create_trh_for_receipt_act');
1147 END IF;
1148 RAISE;
1149
1150 END create_trh_for_receipt_act;
1151
1152 END ARP_PROC_TRANSACTION_HISTORY;