[Home] [Help]
PACKAGE BODY: APPS.ARP_CONFIRMATION
Source
1 PACKAGE BODY ARP_CONFIRMATION AS
2 /* $Header: ARRECNFB.pls 120.15 2005/06/14 19:02:58 vcrisost ship $ */
3
4 /* =======================================================================
5 | Global Data Types
6 * ======================================================================*/
7 SUBTYPE ae_doc_rec_type IS arp_acct_main.ae_doc_rec_type;
8
9 --
10 -- Private procedures/functions used by this package (Declarations):
11 --
12
13 --
14 -- Public procedures/functions provided by this package:
15 --
16
17 /*===========================================================================+
18 | PROCEDURE |
19 | confirm |
20 | |
21 | DESCRIPTION |
22 | Entity handler interface function for Confirm operation in 10SC |
23 | |
24 | SCOPE - PUBLIC |
25 | |
26 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
27 | |
28 | ARGUMENTS |
29 | IN: |
30 | p_cr_id - cash receipt to be confirmed |
31 | p_confirm_gl_date - Confirm GL date |
32 | p_confirm_date - Confirm Date |
33 | p_module_name - Name of module that called this procedure |
34 | p_module_version - Version of the module that called this procedure |
35 | |
36 | OUT: |
37 | |
38 | RETURNS |
39 | |
40 | NOTES |
41 | |
42 | MODIFICATION HISTORY |
43 | |
44 | 18-AUG-95 OSTEINME created |
45 | 04-DEC-97 KLAWRANC Bug #590256. Modified call to |
46 | calc_acctd_amount. Now passes NULL for the |
47 | currency code parameter, therefore the acctd |
48 | amount will be calculated based on the |
49 | functional currency. |
50 | 21-MAY-98 KTANG For all calls to calc_acctd_amount which |
51 | calculates header accounted amounts, if the |
52 | exchange_rate_type is not user, call |
53 | gl_currency_api.convert_amount instead. This |
54 | is for triangulation. |
55 | |
56 +===========================================================================*/
57
58 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
59
60 PROCEDURE confirm(
61 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
62 p_confirm_gl_date IN DATE,
63 p_confirm_date IN DATE,
64 p_module_name IN VARCHAR2,
65 p_module_version IN VARCHAR2 ) IS
66
67 -- local variables:
68
69 l_acctd_amount NUMBER;
70 l_receipt_clearing_ccid
71 ar_receipt_method_accounts.receipt_clearing_ccid%TYPE;
72 l_dummy NUMBER;
73 l_cr_rec ar_cash_receipts%ROWTYPE;
74
75 BEGIN
76
77 IF PG_DEBUG in ('Y', 'C') THEN
78 arp_standard.debug('arp_confirmation.confirm()+');
79 arp_standard.debug('confirm: ' || '-- p_cr_id : ' || to_char(p_cr_id));
80 arp_standard.debug('-- p_confirm_gl_date: ' || to_char(p_confirm_gl_date));
81 arp_standard.debug('-- p_confirm_date : ' || to_char(p_confirm_date));
82 END IF;
83
84 -- validate IN parameters:
85
86 validate_in_parameters( p_cr_id,
87 p_confirm_gl_date,
88 p_confirm_date,
89 p_module_name);
90
91 IF PG_DEBUG in ('Y', 'C') THEN
92 arp_standard.debug('confirm: ' || '-- Parameters validated.');
93 END IF;
94
95 -- populate the ar_cash_receipts record from ar_cash_receipts table.
96 -- use ar_cash_receipt_id for selection.
97
98 l_cr_rec.cash_receipt_id := p_cr_id;
99 arp_cash_receipts_pkg.fetch_p(l_cr_rec);
100
101 IF PG_DEBUG in ('Y', 'C') THEN
102 arp_standard.debug('confirm: ' || '-- Cash Receipt fetched');
103 END IF;
104
105 -- get receipt clearing code combination id from ar_receipt_method_accounts
106
107 get_receipt_clearing_ccid(l_cr_rec, l_receipt_clearing_ccid);
108
109 IF PG_DEBUG in ('Y', 'C') THEN
110 arp_standard.debug('confirm: ' || '-- Receipt Clearing code combination fetched');
111 arp_standard.debug('confirm: ' || '-- receipt_clearing_ccid = '|| l_receipt_clearing_ccid);
112 END IF;
113
114 -- calculate accounted cash receipt amount
115 -- Changes for triangulation: If exchange rate type is not user, call
116 -- GL API to calculate accounted amount
117 IF (l_cr_rec.exchange_rate_type = 'User') THEN
118 arp_util.calc_acctd_amount( NULL,
119 NULL,
120 NULL,
121 l_cr_rec.exchange_rate,
122 '+',
123 l_cr_rec.amount,
124 l_acctd_amount,
125 0,
126 l_dummy,
127 l_dummy,
128 l_dummy);
129 ELSE
130 l_acctd_amount := gl_currency_api.convert_amount(
131 arp_global.set_of_books_id,
132 l_cr_rec.currency_code,
133 l_cr_rec.exchange_date,
134 l_cr_rec.exchange_rate_type,
135 l_cr_rec.amount);
136 END IF;
137
138 IF PG_DEBUG in ('Y', 'C') THEN
139 arp_standard.debug('confirm: ' || '-- Accounted Amount calculated:');
140 arp_standard.debug('confirm: ' || '-- Exchange Rate: ' || to_char(l_cr_rec.exchange_rate));
141 arp_standard.debug('confirm: ' || '-- Receipt Amount: ' || to_char(l_cr_rec.amount));
142 arp_standard.debug('confirm: ' || '-- Acctd Amount: ' || to_char(l_acctd_amount));
143 END IF;
144
145 -- update the ar_cash_receipt_history_table with a new record for
146 -- this receipt. This call will also create a new ar_distributions
147 -- record.
148
149 update_cr_history_confirm( l_cr_rec,
150 p_confirm_gl_date,
151 p_confirm_date,
152 l_acctd_amount,
153 l_receipt_clearing_ccid);
154
155 -- call do_confirm to process the individual applications and
156 -- update the payment schedule of the receipt.
157 -- Note: This functionality was grouped together in one function,
158 -- because it basically represents the functionality of the
159 -- confirm user exit in Rel. 10. Do_confirm is called from
160 -- the interface function execute_confirm.
161
162 do_confirm( l_cr_rec,
163 p_confirm_gl_date,
164 p_confirm_date,
165 l_acctd_amount);
166
167 IF PG_DEBUG in ('Y', 'C') THEN
168 arp_standard.debug('arp_confirmation.confirm()-');
169 END IF;
170
171 EXCEPTION
172 WHEN OTHERS THEN
173 IF PG_DEBUG in ('Y', 'C') THEN
174 arp_util.debug('EXCEPTION: arp_confirmation.confirm()');
175 END IF;
176 RAISE;
177
178 END; -- confirm()
179
180
181 /*===========================================================================+
182 | PROCEDURE |
183 | unconfirm |
184 | |
185 | DESCRIPTION |
186 | Entity handler interface function for Unconfirm operation in 10SC |
187 | |
188 | SCOPE - PUBLIC |
189 | |
190 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
191 | |
192 | ARGUMENTS |
193 | IN: |
194 | p_cr_id - ID of cash receipt to be unconfirmed |
195 | p_confirm_gl_date - Unconfirm GL date |
196 | p_confirm_date - Unconfirm Date |
197 | p_module_name - Name of module that called this procedure |
198 | p_module_version - Version of the module that called this procedure |
199 | |
200 | OUT: |
201 | |
202 | RETURNS |
203 | |
204 | NOTES |
205 | |
206 | MODIFICATION HISTORY |
207 | |
208 | 28-AUG-95 OSTEINME created |
209 | 04-DEC-97 KLAWRANC Bug #590256. Modified call to |
210 | calc_acctd_amount. Now passes NULL for the |
211 | currency code parameter, therefore the acctd |
212 | amount will be calculated based on the |
213 | functional currency. |
214 | |
215 +===========================================================================*/
216
217
218
219 PROCEDURE unconfirm(
220 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
221 p_confirm_gl_date IN DATE,
222 p_confirm_date IN DATE,
223 p_module_name IN VARCHAR2,
224 p_module_version IN VARCHAR2 ) IS
225
226 -- local variables:
227
228 l_cr_rec ar_cash_receipts%ROWTYPE;
229 l_acctd_amount NUMBER;
230 l_receipt_clearing_ccid
231 ar_receipt_method_accounts.receipt_clearing_ccid%TYPE;
232 l_batch_id ar_cash_receipt_history.batch_id%TYPE;
233 l_crh_id_rev
234 ar_cash_receipt_history.cash_receipt_history_id%TYPE;
235 l_dummy NUMBER; -- dummy variable
236
237 BEGIN
238
239 IF PG_DEBUG in ('Y', 'C') THEN
240 arp_standard.debug('arp_confirmation.unconfirm()+');
241 arp_standard.debug('confirm: ' || to_char(p_cr_id));
242 END IF;
243
244 -- validate IN parameters:
245
246 validate_in_parameters( p_cr_id,
247 p_confirm_gl_date,
248 p_confirm_date,
249 p_module_name);
250
251 -- populate the ar_cash_receipts record from ar_cash_receipts table.
252 -- use ar_cash_receipt_id for selection.
253
254 l_cr_rec.cash_receipt_id := p_cr_id;
255 arp_cash_receipts_pkg.fetch_p(l_cr_rec);
256
257 -- calculate accounted cash receipt amount
258 -- Changes for triangulation: If exchange rate type is not user, call
259 -- GL API to calculate accounted amount
260 IF (l_cr_rec.exchange_rate_type = 'User') THEN
261 arp_util.calc_acctd_amount( NULL,
262 NULL,
263 NULL,
264 l_cr_rec.exchange_rate,
265 '+',
266 l_cr_rec.amount,
267 l_acctd_amount,
268 0,
269 l_dummy,
270 l_dummy,
271 l_dummy);
272 ELSE
273 l_acctd_amount := gl_currency_api.convert_amount(
274 arp_global.set_of_books_id,
275 l_cr_rec.currency_code,
276 l_cr_rec.exchange_date,
277 l_cr_rec.exchange_rate_type,
278 l_cr_rec.amount);
279 END IF;
280
281 IF PG_DEBUG in ('Y', 'C') THEN
282 arp_standard.debug('confirm: ' || '-- Accounted Amount calculated:');
283 arp_standard.debug('confirm: ' || '-- Exchange Rate: ' || to_char(l_cr_rec.exchange_rate));
284 arp_standard.debug('confirm: ' || '-- Receipt Amount: ' || to_char(l_cr_rec.amount));
285 arp_standard.debug('confirm: ' || '-- Acctd Amount: ' || to_char(l_acctd_amount));
286 END IF;
287
288 -- update the ar_cash_receipt_history_table with a new record for
289 -- this receipt. This call will also create a new ar_distributions
290 -- record to reverse the 'confirm' record.
291
292 update_cr_history_unconfirm( l_cr_rec,
293 p_confirm_gl_date,
294 p_confirm_date,
295 l_acctd_amount,
296 l_batch_id,
297 l_crh_id_rev);
298
299 -- call do_unconfirm to process the individual applications and
300 -- update the payment schedule of the receipt.
301 -- Note: This functionality was grouped together in one function,
302 -- because it basically represents the functionality of the
303 -- confirm user exit in Rel. 10. Do_unconfirm is called from
304 -- the interface function execute_unconfirm.
305
306 do_unconfirm( l_cr_rec,
307 p_confirm_gl_date,
308 p_confirm_date,
309 l_acctd_amount,
310 l_batch_id);
311
312 IF PG_DEBUG in ('Y', 'C') THEN
313 arp_standard.debug('arp_confirmation.unconfirm()-');
314 END IF;
315
316 EXCEPTION
317 WHEN OTHERS THEN
318 IF PG_DEBUG in ('Y', 'C') THEN
319 arp_util.debug('EXCEPTION: arp_confirmation.unconfirm()');
320 END IF;
321 RAISE;
322
323 END; -- unconfirm()
324
325
326 /* Bug fix 872506 */
327 /*===========================================================================+
328 | PROCEDURE |
329 | confirm_batch |
330 | |
331 | DESCRIPTION |
332 | Entity handler interface function for Confirm receipt in batch level |
333 | operation |
334 | |
335 | SCOPE - PUBLIC |
336 | |
337 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
338 | |
339 | ARGUMENTS |
340 | IN: |
341 | p_batch_id - batch receipt to be confirmed |
342 | p_confirm_gl_date - Confirm GL date |
343 | p_confirm_date - Confirm Date |
344 | |
345 | OUT: |
346 | p_num_rec_confirmed - Number of receipts in the batch confirmed |
347 | p_num_rec_error - Number of receipts in the batch unconfirmed |
348 | |
349 | RETURNS |
350 | |
351 | NOTES |
352 | |
353 | MODIFICATION HISTORY |
354 | |
355 | 10-APR-2002 R Kader created |
356 | |
357 +===========================================================================*/
358
359 PROCEDURE confirm_batch(
360 p_batch_id IN NUMBER,
361 p_confirm_gl_date IN DATE,
362 p_confirm_date IN DATE,
363 p_num_rec_confirmed OUT NOCOPY NUMBER,
364 p_num_rec_error OUT NOCOPY NUMBER) IS
365
366 l_num_rec_confirmed NUMBER := 0;
367 l_num_rec_error NUMBER :=0;
368
369 BEGIN
370
371 -- Verify that batch is really an automatic batch:
372 -- 'type' must be CREATION
373
374 -- ...
375
376
377 DECLARE
378 CURSOR confirmCursor (auto_batch_id IN NUMBER) IS
379 SELECT cash_receipt_id
380 FROM AR_CASH_RECEIPT_HISTORY
381 WHERE current_record_flag = 'Y'
382 AND status='APPROVED'
383 AND batch_id = auto_batch_id;
384
385 l_cash_receipt_rec confirmCursor%ROWTYPE;
386
387 BEGIN
388 IF PG_DEBUG in ('Y', 'C') THEN
389 arp_standard.debug('arp_confirmation.confirm_batch()+');
390 END IF;
391
392 FOR l_cash_receipt_rec IN confirmCursor(p_batch_id) LOOP
393 BEGIN
394
395 SAVEPOINT ar_confirm_batch_sp;
396
397 UPDATE AR_CASH_RECEIPTS
398 SET confirmed_flag = 'Y'
399 WHERE cash_receipt_id = l_cash_receipt_rec.cash_receipt_id;
400
401 arp_confirmation.confirm(
402 l_cash_receipt_rec.cash_receipt_id,
403 p_confirm_gl_date,
404 p_confirm_date,
405 'ARXRWMAI',
406 '1x');
407
408 l_num_rec_confirmed := l_num_rec_confirmed + 1;
409
410 EXCEPTION
411 WHEN OTHERS THEN
412 IF PG_DEBUG in ('Y', 'C') THEN
413 arp_standard.debug('Exception in arp_confirmation.confirm_batch');
414 END IF;
415 ROLLBACK TO ar_confirm_batch_sp;
416 l_num_rec_error := l_num_rec_error + 1;
417 END;
418 END LOOP;
419 END;
420
421 p_num_rec_confirmed := l_num_rec_confirmed;
422 p_num_rec_error := l_num_rec_error;
423
424 IF PG_DEBUG in ('Y', 'C') THEN
425 arp_standard.debug('arp_confirmation.confirm_batch()-');
426 END IF;
427
428 END confirm_batch;
429
430 /*===========================================================================+
431 | PROCEDURE |
432 | confirm_receipt |
433 | |
434 | DESCRIPTION |
435 | API for Confirm receipt operation in 10SC |
436 | |
437 | SCOPE - PUBLIC |
438 | |
439 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
440 | |
441 | ARGUMENTS |
442 | IN: |
443 | p_cr_id - cash receipt to be confirmed |
444 | p_confirm_gl_date - Confirm GL date |
445 | p_confirm_date - Confirm Date |
446 | |
447 | OUT: |
448 | |
449 | RETURNS |
450 | |
451 | NOTES |
452 | |
453 | MODIFICATION HISTORY |
454 | |
455 | 12-APR-99 GJWANG created |
456 | |
457 +===========================================================================*/
458
459 PROCEDURE confirm_receipt(
460 p_cr_id IN NUMBER,
461 p_confirm_gl_date IN DATE,
462 p_confirm_date IN DATE) IS
463
464 l_status VARCHAR2(30);
465 l_confirmed_flag VARCHAR2(2);
466 BEGIN
467 IF PG_DEBUG in ('Y', 'C') THEN
468 arp_standard.debug('arp_confirmation.confirm_receipt()+');
469 arp_standard.debug('****** Begin Confirm Receipt ****** ');
470 arp_standard.debug('confirm: ' || '-- p_cr_id : ' || to_char(p_cr_id));
471 arp_standard.debug('-- p_confirm_gl_date: ' || to_char(p_confirm_gl_date));
472 arp_standard.debug('-- p_confirm_date : ' || to_char(p_confirm_date));
473 END IF;
474
475 IF arp_util.is_gl_date_valid(p_confirm_gl_date) THEN
476
477 SELECT crh.status, cr.confirmed_flag
478 INTO l_status, l_confirmed_flag
479 FROM AR_CASH_RECEIPTS cr,
480 AR_CASH_RECEIPT_HISTORY crh
481 WHERE cr.cash_receipt_id = crh.cash_receipt_id
482 AND cr.cash_receipt_id = p_cr_id;
483
484 IF (l_status = 'APPROVED') and (l_confirmed_flag = 'N') THEN
485 UPDATE AR_CASH_RECEIPTS
486 SET confirmed_flag = 'Y'
487 WHERE cash_receipt_id = p_cr_id;
488
489 arp_confirmation.confirm(
490 p_cr_id,
491 p_confirm_gl_date,
492 p_confirm_date,
493 'ARXRWMAI',
494 '1x');
495 IF PG_DEBUG in ('Y', 'C') THEN
496 arp_standard.debug('==> Receipt ' || to_char(p_cr_id) || ' confirmed');
497 END IF;
498
499 END IF;
500 IF PG_DEBUG in ('Y', 'C') THEN
501 arp_standard.debug('arp_confirmation.confirm_receipt: Invalid GL DATE ' || to_char(p_confirm_gl_date));
502 END IF;
503 END IF;
504 IF PG_DEBUG in ('Y', 'C') THEN
505 arp_standard.debug('arp_confirmation.confirm_receipt()-');
506 END IF;
507
508 EXCEPTION
509 WHEN OTHERS THEN
510 IF PG_DEBUG in ('Y', 'C') THEN
511 arp_standard.debug('EXCEPTION: arp_confirmation.confirm_receipt');
512 END IF;
513 RAISE;
514 END confirm_receipt; -- confirm_receipt()
515 /* End Bug fix 872506 */
516 --
517 -- Private procedures/functions used by this package (Code):
518 --
519
520
521
522 /*===========================================================================+
523 | PROCEDURE |
524 | do_confirm |
525 | |
526 | DESCRIPTION |
527 | Performs most of the steps needed to confirm a cash receipt: |
528 | for every application record of a given cash receipt |
529 | update associated invoice's payment schedule |
530 | update receivable_application |
531 | |
532 | SCOPE - PUBLIC |
533 | |
534 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
535 | |
536 | ARGUMENTS |
537 | IN: |
538 | p_cr_rec - cash receipt to be confirmed |
539 | p_confirm_gl_date - Unconfirm GL date |
540 | p_confirm_date - Unconfirm Date |
541 | p_acctd_amount - accounted receipt amount |
542 | |
543 | OUT: |
544 | |
545 | RETURNS |
546 | |
547 | NOTES |
548 | |
549 | MODIFICATION HISTORY |
550 | |
551 | 18-AUG-95 OSTEINME created |
552 | 04-DEC-97 KLAWRANC Bug #590256. Modified call to |
553 | calc_acctd_amount. Now passes NULL for the |
554 | currency code parameter, therefore the acctd |
555 | amount will be calculated based on the |
556 | functional currency. |
557 | 11-JAN-98 JGDABIR Bug 768935. Initially set |
558 | acctd_amount_applied_from to NULL. |
559 | |
560 +===========================================================================*/
561
562 PROCEDURE do_confirm(
563 p_cr_rec IN ar_cash_receipts%ROWTYPE,
564 p_confirm_gl_date IN DATE,
565 p_confirm_date IN DATE,
566 p_acctd_amount IN NUMBER
567 ) IS
568
569 -- Local variables:
570
571 l_dummy NUMBER;
572
573 l_inv_ps_rec ar_payment_schedules%ROWTYPE;
574 l_max_dates MaxDatesType; -- record type
575
576 l_line_applied NUMBER;
577 l_tax_applied NUMBER;
578 l_freight_applied NUMBER;
579 l_charges_applied NUMBER;
580 l_line_ediscounted NUMBER;
581 l_tax_ediscounted NUMBER;
582 l_freight_ediscounted NUMBER;
583 l_charges_ediscounted NUMBER;
584 l_line_uediscounted NUMBER;
585 l_tax_uediscounted NUMBER;
586 l_freight_uediscounted NUMBER;
587 l_charges_uediscounted NUMBER;
588 l_rule_set_id NUMBER;
589
590 l_apply_date DATE;
591 l_gl_date DATE;
592 l_cnf_gl_date DATE;
593 l_cnf_date DATE;
594
595 l_ao_flag ra_cust_trx_types.allow_overapplication_flag%TYPE;
596 l_nao_flag ra_cust_trx_types.natural_application_only_flag%TYPE;
597 l_creation_sign ra_cust_trx_types.creation_sign%TYPE;
598
599 l_acctd_app_amount_to
600 ar_receivable_applications.acctd_amount_applied_to%TYPE;
601 l_acctd_app_amount_from
602 ar_receivable_applications.acctd_amount_applied_from%TYPE;
603
604 l_ae_doc_rec ae_doc_rec_type;
605
606 l_app_id ar_receivable_applications.receivable_application_id%TYPE;
607
608
609 -- Following two parametes and the currency cursor is introduced By
610 -- RAM-C (ORASHID)
611
612 l_exchange_rate ra_customer_trx_all.exchange_rate%TYPE;
613 l_invoice_currency_code ra_customer_trx_all.invoice_currency_code%TYPE;
614
615 CURSOR currency (p_trx_id IN NUMBER) IS
616 SELECT invoice_currency_code,
617 exchange_rate
618 FROM ra_customer_trx_all
619 WHERE customer_trx_id = p_trx_id;
620
621
622 -- Define cursor for applications:
623
624 CURSOR ar_receivable_applications_C (
625 p_cr_id ar_cash_receipts.cash_receipt_id%TYPE
626 ) IS
627 SELECT *
628 FROM ar_receivable_applications
629 WHERE cash_receipt_id = p_cr_id
630 AND status = 'APP'
631 AND reversal_gl_date IS NULL;
632
633 BEGIN
634
635 arp_standard.debug('arp_confirmation.do_confirm()+');
636
637 -- initialize l_max_dates:
638
639 l_max_dates.max_trx_date := p_confirm_date;
640 l_max_dates.max_gl_date := p_confirm_gl_date;
641 l_max_dates.cnf_date := p_confirm_date;
642 l_max_dates.cnf_gl_date := p_confirm_gl_date;
643 l_max_dates.max_ra_apply_date := p_confirm_date;
644 l_max_dates.max_ra_gl_date := p_confirm_gl_date;
645
646 -- process every application record for the given cash receipt:
647
648 FOR l_ra_rec IN ar_receivable_applications_C(p_cr_rec.cash_receipt_id)
649 LOOP
650
651 -- Bug 768935: initially set l_acctd_app_amount_from to NULL,
652 -- let calc_acctd_amount calculate.
653
654 l_acctd_app_amount_from := NULL;
655
656 arp_standard.debug('-- Fetched ra record -- ra_id = '||
657 l_ra_rec.receivable_application_id);
658
659 -- get payment schedule of invoice for this application. This
660 -- is required to update the 'selected_for_receipt_batch_id' column.
661
662 arp_ps_pkg.fetch_p(l_ra_rec.applied_payment_schedule_id, l_inv_ps_rec);
663
664 arp_standard.debug('-- Fetched invoice ps record. ps_id = '||
665 to_char(l_ra_rec.applied_payment_schedule_id));
666
667 -- determine dates based on receivable_application and payment_schedule
668 -- record:
669
670 l_apply_date := GREATEST( p_confirm_date,
671 l_ra_rec.apply_date,
672 l_inv_ps_rec.trx_date);
673 l_gl_date := GREATEST( p_confirm_gl_date,
674 l_inv_ps_rec.gl_date);
675 l_cnf_gl_date := GREATEST( p_confirm_gl_date,
676 l_inv_ps_rec.gl_date);
677 l_cnf_date := GREATEST( p_confirm_date,
678 l_inv_ps_rec.trx_date);
679
680 -- update max_dates data structure:
681
682 handle_max_dates( l_max_dates,
683 l_gl_date,
684 l_apply_date,
685 p_confirm_date,
686 p_confirm_gl_date);
687
688 arp_standard.debug('-- determined max_dates');
689
690 -- check for violation of application rules (over-application,
691 -- creation sign, natural application).
692
693 get_application_flags( l_inv_ps_rec.cust_trx_type_id,
694 l_ao_flag,
695 l_nao_flag,
696 l_creation_sign);
697
698 arp_standard.debug('-- got application flags');
699
700 /*@ check_application_rules(l_ra_rec); */ -- ?????????????????????????
701
702 -- update invoice payment schedule to which this application record
703 -- is applied:
704 -- First set the 'selected_for_receipt_batch_id' to NULL as this
705 -- invoice is no longer selected (and potentially available for
706 -- another selection). Then call update_invoice_related_columns
707 -- to apply the application_amount to the invoice and update the
708 -- payment schedule in the database.
709
710 l_inv_ps_rec.selected_for_receipt_batch_id := NULL;
711
712
713 arp_ps_util.update_invoice_related_columns(
714 'CASH',
715 NULL, -- No ps_id
716 l_ra_rec.amount_applied,
717 0, -- discounts taken
718 0, -- discounts earned
719 l_cnf_date,
720 l_cnf_gl_date,
721 l_acctd_app_amount_to,
722 l_dummy,
723 l_dummy,
724 l_line_applied,
725 l_tax_applied,
726 l_freight_applied,
727 l_charges_applied,
728 l_line_ediscounted,
729 l_tax_ediscounted,
730 l_freight_ediscounted,
731 l_charges_ediscounted,
732 l_line_uediscounted,
733 l_tax_uediscounted,
734 l_freight_uediscounted,
735 l_charges_uediscounted,
736 l_rule_set_id,
737 l_inv_ps_rec);
738
739 arp_standard.debug('-- invoice ps updated.');
740 arp_standard.debug('-- l_acctd_app_amount_to = ' ||
741 to_char(l_acctd_app_amount_to));
742 arp_standard.debug('-- l_line_applied = ' || to_char(l_line_applied));
743 arp_standard.debug('-- l_tax_applied = ' || to_char(l_tax_applied));
744 arp_standard.debug('-- l_freight_applied = ' ||
745 to_char(l_freight_applied));
746 arp_standard.debug('-- l_charges_applied = ' ||
747 to_char(l_charges_applied));
748
749
750 -- calculate accounted amount for application (receipt side):
751
752 arp_util.calc_acctd_amount( NULL,
753 NULL,
754 NULL,
755 p_cr_rec.exchange_rate,
756 '+',
757 l_ra_rec.amount_applied,
758 l_acctd_app_amount_from,
759 0,
760 l_dummy,
761 l_dummy,
762 l_dummy);
763
764 arp_standard.debug('-- calculated acctd_app_amount_from = ' ||
765 to_char(l_acctd_app_amount_from));
766 arp_standard.debug('-- amount_applied for ra = '||
767 to_char(l_ra_rec.amount_applied));
768
769 -- Update receivable applications record. Use the return values
770 -- of the previous function call to fill the line, tax, freight,
771 -- and charges applied columns.
772
773 UPDATE ar_receivable_applications
774 SET confirmed_flag = 'Y',
775 postable = 'Y',
776 gl_date = l_max_dates.max_ra_gl_date,
777 apply_date = l_max_dates.max_ra_apply_date,
778 acctd_amount_applied_to = l_acctd_app_amount_to,
779 acctd_amount_applied_from = l_acctd_app_amount_from,
780 line_applied = l_line_applied,
781 tax_applied = l_tax_applied,
782 freight_applied = l_freight_applied,
783 receivables_charges_applied = l_charges_applied,
784 line_ediscounted = l_line_ediscounted,
785 tax_ediscounted = l_tax_ediscounted,
786 freight_ediscounted = l_freight_ediscounted,
787 charges_ediscounted = l_charges_ediscounted,
788 line_uediscounted = l_line_uediscounted,
789 tax_uediscounted = l_tax_uediscounted,
790 freight_uediscounted = l_freight_uediscounted,
791 charges_uediscounted = l_charges_uediscounted,
792 rule_set_id = l_rule_set_id,
793 last_update_date = TRUNC(SYSDATE),
794 last_updated_by = FND_GLOBAL.user_id
795 WHERE
796 receivable_application_id = l_ra_rec.receivable_application_id;
797
798 arp_standard.debug('-- ra record updated.');
799
800 -- call mrc to replicate the data
801 ar_mrc_engine3.confirm_ra_rec_update(
802 l_ra_rec.receivable_application_id);
803
804 arp_standard.debug('-- MRC ra record updated if necessary');
805
806 --
807 --Release 11.5 VAT changes, create the application accounting for
808 --confirmed APP record in ar_distributions. In this case we create
809 --the APP directly as only confirmed APP records have accounting created
810 --basically we dont require the module below to be called in update mode
811 --(delete + create)
812 --
813 l_ae_doc_rec.document_type := 'RECEIPT';
814 l_ae_doc_rec.document_id := p_cr_rec.cash_receipt_id;
815 l_ae_doc_rec.accounting_entity_level := 'ONE';
816 l_ae_doc_rec.source_table := 'RA';
817 l_ae_doc_rec.source_id := l_ra_rec.receivable_application_id; --id of APP record
818 l_ae_doc_rec.source_id_old := '';
819 l_ae_doc_rec.other_flag := '';
820
821 --Bug 1329091 - PS is updated before Accounting Engine Call
822 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
823 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
824
825 l_app_id := l_ra_rec.receivable_application_id;
826
827 -- RAM-C changes begin from this point onward.
828 --
829 -- call revenue management engine's receipt analyzer for revenue related
830 -- impact of this application.
831
832 arp_standard.debug( 'calling receipt_analyzer in application mode');
833
834 -- RAM-C changes begin.
835 --
836 -- get the invoice currency and the exchange rate
837 -- from ra_customer_trx_all given the customer_trx_id
838
839 OPEN currency(l_ra_rec.applied_customer_trx_id);
840 FETCH currency INTO l_invoice_currency_code, l_exchange_rate;
841 CLOSE currency;
842
843 ar_revenue_management_pvt.receipt_analyzer
844 (
845 p_mode =>
846 ar_revenue_management_pvt.c_receipt_application_mode,
847 p_customer_trx_id => l_ra_rec.applied_customer_trx_id,
848 p_acctd_amount_applied => l_acctd_app_amount_to,
849 p_exchange_rate => l_exchange_rate,
850 p_invoice_currency_code => l_invoice_currency_code,
851 p_tax_applied => l_tax_applied,
852 p_charges_applied => l_charges_applied,
853 p_freight_applied => l_freight_applied,
854 p_line_applied => l_line_applied,
855 p_gl_date => l_max_dates.max_ra_gl_date
856 );
857
858 arp_standard.debug( 'returned from receipt_analyzer');
859
860 -- RAM-C changes end at this point.
861
862 --apandit
863 --Bug 2641517 rase CR apply business event upon confirmation
864 arp_standard.debug( 'before raising the business event : Raise_CR_Apply_Event');
865 AR_BUS_EVENT_COVER.Raise_CR_Apply_Event(
866 l_ra_rec.receivable_application_id);
867 END LOOP;
868
869
870 -- update UNAPP record of the cash receipt in ar_receivable_applications:
871
872 modify_update_ra_rec( p_cr_rec.cash_receipt_id,
873 p_cr_rec.amount,
874 p_acctd_amount,
875 p_confirm_gl_date,
876 p_confirm_date);
877
878 -- update receipt payment schedule:
879 -- ????? VERIFY that dates are correct ?????
880
881 confirm_update_ps_rec( p_cr_rec,
882 l_max_dates.max_trx_date,
883 l_max_dates.max_gl_date);
884
885
886 -- create matching UNAPP records for APP records in
887 -- ar_receivable_applications (negative amounts).
888 -- as part of 11.5 changes, the APP id also needs to be passed
889 -- as UNAPP records are paired with their APP records
890
891 create_matching_unapp_records(p_cr_rec.cash_receipt_id, l_app_id);
892
893 arp_standard.debug('arp_confirmation.do_confirm()-');
894
895 EXCEPTION
896 WHEN NO_DATA_FOUND THEN
897 arp_util.debug('EXCEPTION: NO DATA FOUND, arp_confirmation.do_confirm()');
898 RAISE;
899
900 WHEN OTHERS THEN
901 arp_util.debug('EXCEPTION: arp_confirmation.do_confirm()');
902 RAISE;
903
904 END; -- do_confirm()
905
906
907
908 /*===========================================================================+
909 | PROCEDURE |
910 | do_unconfirm |
911 | |
912 | DESCRIPTION |
913 | Performs most of the steps needed to unconfirm a cash receipt: |
914 | for every application record of a given cash receipt |
915 | update associated invoice's payment schedule |
916 | update receivable_application |
917 | |
918 | SCOPE - PUBLIC |
919 | |
920 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
921 | |
922 | ARGUMENTS |
923 | IN: |
924 | p_cr_rec - cash receipt to be confirmed |
925 | p_confirm_gl_date - Unconfirm GL date |
926 | p_confirm_date - Unconfirm Date |
927 | p_acctd_amount - accounted receipt amount |
928 | p_batch_id - batch id for receipt batch (needed to update inv. |
929 | payment schedule) |
930 | |
931 | OUT: |
932 | |
933 | RETURNS |
934 | |
935 | NOTES |
936 | |
937 | MODIFICATION HISTORY |
938 | |
939 | 28-AUG-95 OSTEINME created |
940 | |
941 +===========================================================================*/
942
943
944
945 PROCEDURE do_unconfirm(
946 p_cr_rec IN ar_cash_receipts%ROWTYPE,
947 p_confirm_gl_date IN DATE,
948 p_confirm_date IN DATE,
949 p_acctd_amount IN NUMBER,
950 p_batch_id
951 IN ar_payment_schedules.selected_for_receipt_batch_id%TYPE
952 ) IS
953
954 -- Define cursor for applications:
955
956 CURSOR ar_receivable_applications_C (
957 p_cr_id ar_cash_receipts.cash_receipt_id%TYPE
958 ) IS
959 SELECT *
960 FROM ar_receivable_applications
961 WHERE cash_receipt_id = p_cr_id
962 AND status = 'APP'
963 AND reversal_gl_date IS NULL;
964
965 BEGIN
966
967 -- process every application record for the given cash receipt:
968
969 FOR l_app_rec IN ar_receivable_applications_C(p_cr_rec.cash_receipt_id)
970 LOOP
971
972 -- Update invoice payment schedule to which this application record
973 -- is applied. This step basically reverses the application of the
974 -- receipt to the invoice.
975
976
977 reverse_application_to_ps(
978 l_app_rec.receivable_application_id,
979 p_confirm_gl_date,
980 p_confirm_date,
981 p_batch_id);
982
983 END LOOP;
984
985
986 -- create reversing records in ar_receivable_applications
987
988 reverse_ra_recs( p_cr_rec,
989 p_confirm_gl_date,
990 p_confirm_date);
991
992 -- update receipt payment schedule:
993
994 unconfirm_update_ps_rec( p_cr_rec,
995 p_confirm_gl_date,
996 p_confirm_date);
997
998 EXCEPTION
999 WHEN OTHERS THEN
1000 arp_util.debug('EXCEPTION: arp_confirmation.do_unconfirm()');
1001 RAISE;
1002
1003 END; -- do_unconfirm()
1004
1005
1006
1007 /*===========================================================================+
1008 | PROCEDURE |
1009 | update_cr_history_conf |
1010 | |
1011 | DESCRIPTION |
1012 | Creates a new entry for the cash_receipt_history table. |
1013 | It will have the updated receipt amount and the status |
1014 | 'CONFIRMED'. Also creates an ar_distributions record for |
1015 | the new history record. |
1016 | |
1017 | SCOPE - PRIVATE |
1018 | |
1019 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1020 | |
1021 | ARGUMENTS |
1022 | IN: |
1023 | p_cr_rec - cash receipt for which the history entry |
1024 | is to be created |
1025 | p_confirm_gl_date - Unconfirm GL date |
1026 | p_confirm_date - Unconfirm Date |
1027 | p_acctd_amount - accounted cash receipt amount |
1028 | p_receipt_clearing_ccid - code combination id |
1029 | |
1030 | OUT: |
1031 | |
1032 | RETURNS |
1033 | |
1034 | NOTES |
1035 | |
1036 | MODIFICATION HISTORY |
1037 | |
1038 | 18-AUG-95 OSTEINME created |
1039 | |
1040 +===========================================================================*/
1041
1042
1043 PROCEDURE update_cr_history_confirm(
1044 p_cr_rec IN ar_cash_receipts%ROWTYPE,
1045 p_confirm_gl_date IN DATE,
1046 p_confirm_date IN DATE,
1047 p_acctd_amount IN NUMBER,
1048 p_receipt_clearing_ccid IN
1049 ar_receipt_method_accounts.receipt_clearing_ccid%TYPE
1050 ) IS
1051 --
1052 l_crh_rec_old ar_cash_receipt_history%ROWTYPE;
1053 l_crh_rec_new ar_cash_receipt_history%ROWTYPE;
1054 l_crh_id_new ar_cash_receipts.cash_receipt_id%TYPE;
1055 l_dist_rec ar_distributions%ROWTYPE;
1056 l_dist_line_id ar_distributions.line_id%TYPE;
1057
1058 --
1059 BEGIN
1060
1061 arp_standard.debug('arp_confirmation.update_cr_history_confirm()+');
1062
1063 -- fetch current record from ar_cash_receipt_history
1064
1065 arp_cr_history_pkg.fetch_f_crid(p_cr_rec.cash_receipt_id, l_crh_rec_old);
1066
1067 arp_standard.debug('-- current history record fetched. crh_id = '||
1068 to_char(l_crh_rec_old.cash_receipt_history_id));
1069
1070 -- update columns in current record
1071
1072 l_crh_rec_old.reversal_gl_date := p_confirm_gl_date;
1073 l_crh_rec_old.reversal_posting_control_id := -3;
1074 l_crh_rec_old.reversal_created_from := 'ARRECNF';
1075 l_crh_rec_old.current_record_flag := NULL;
1076 l_crh_rec_old.first_posted_record_flag := 'N';
1077
1078
1079 -- create new record:
1080
1081 l_crh_rec_new.amount := p_cr_rec.amount;
1082 l_crh_rec_new.acctd_amount := p_acctd_amount;
1083 l_crh_rec_new.cash_receipt_id := p_cr_rec.cash_receipt_id;
1084 l_crh_rec_new.factor_flag := 'N';
1085 l_crh_rec_new.first_posted_record_flag := 'Y';
1086 l_crh_rec_new.gl_date := p_confirm_gl_date;
1087 l_crh_rec_new.postable_flag := 'Y';
1088 l_crh_rec_new.posting_control_id := -3;
1089 l_crh_rec_new.status := 'CONFIRMED';
1090 l_crh_rec_new.trx_date := p_confirm_date;
1091 l_crh_rec_new.acctd_factor_discount_amount := NULL;
1092 l_crh_rec_new.account_code_combination_id := p_receipt_clearing_ccid;
1093 l_crh_rec_new.bank_charge_account_ccid := NULL;
1094 l_crh_rec_new.batch_id := NULL;
1095 l_crh_rec_new.current_record_flag := 'Y';
1096 l_crh_rec_new.exchange_date := p_cr_rec.exchange_date;
1097 l_crh_rec_new.exchange_rate := p_cr_rec.exchange_rate;
1098 l_crh_rec_new.exchange_rate_type := p_cr_rec.exchange_rate_type;
1099 l_crh_rec_new.factor_discount_amount := NULL;
1100 l_crh_rec_new.gl_posted_date := NULL;
1101 l_crh_rec_new.request_id := NULL;
1102 l_crh_rec_new.created_from := 'ARRECNF';
1103 l_crh_rec_new.prv_stat_cash_receipt_hist_id := l_crh_rec_old.cash_receipt_history_id;
1104
1105 -- insert new current record into cash receipt history table
1106
1107 arp_cr_history_pkg.insert_p(l_crh_rec_new, l_crh_id_new);
1108
1109 arp_standard.debug('-- new crh record inserted. crh_id = ' ||
1110 to_char(l_crh_id_new));
1111
1112 -- link new current record to previous current record and update the latter:
1113
1114 l_crh_rec_old.reversal_cash_receipt_hist_id := l_crh_id_new;
1115 arp_cr_history_pkg.update_p(l_crh_rec_old);
1116
1117 arp_standard.debug('-- previous record updated');
1118
1119 -- create ar_distributions record for new history record:
1120
1121 arp_standard.debug('-- ccid = ' || p_receipt_clearing_ccid);
1122 l_dist_rec.source_id := l_crh_id_new;
1123 l_dist_rec.source_table := 'CRH';
1124 l_dist_rec.source_type := 'CONFIRMATION';
1125 l_dist_rec.last_update_date := SYSDATE;
1126 l_dist_rec.last_updated_by := FND_GLOBAL.user_id;
1127 l_dist_rec.creation_date := SYSDATE;
1128 l_dist_rec.created_by := FND_GLOBAL.user_id;
1129 l_dist_rec.code_combination_id := p_receipt_clearing_ccid;
1130
1131 -- Populate additional value for 11.5 VAT project
1132 -- populate the exchange rate info from the crh record.
1133
1134 l_dist_rec.currency_code := p_cr_rec.currency_code;
1135 l_dist_rec.currency_conversion_rate := l_crh_rec_new.exchange_rate;
1136 l_dist_rec.currency_conversion_type := l_crh_rec_new.exchange_rate_type;
1137 l_dist_rec.currency_conversion_date := l_crh_rec_new.exchange_date;
1138 l_dist_rec.third_party_id := p_cr_rec.pay_from_customer;
1139 l_dist_rec.third_party_sub_id := p_cr_rec.customer_site_use_id;
1140
1141 IF p_cr_rec.amount < 0 THEN
1142 l_dist_rec.amount_dr := NULL;
1143 l_dist_rec.amount_cr := - p_cr_rec.amount;
1144 ELSE
1145 l_dist_rec.amount_dr := p_cr_rec.amount;
1146 l_dist_rec.amount_cr := NULL;
1147 END IF;
1148
1149 IF p_acctd_amount < 0 THEN
1150 l_dist_rec.acctd_amount_dr := NULL;
1151 l_dist_rec.acctd_amount_cr := -p_acctd_amount;
1152 ELSE
1153 l_dist_rec.acctd_amount_dr := p_acctd_amount;
1154 l_dist_rec.acctd_amount_cr := NULL;
1155 END IF;
1156
1157 arp_distributions_pkg.insert_p(l_dist_rec, l_dist_line_id);
1158
1159 /* need to insert records into the MRC table. Calling new
1160 mrc engine */
1161
1162 ar_mrc_engine2.maintain_mrc_data2(
1163 p_event_mode => 'INSERT',
1164 p_table_name => 'AR_DISTRIBUTIONS',
1165 p_mode => 'SINGLE',
1166 p_key_value => l_dist_line_id,
1167 p_row_info => l_dist_rec);
1168
1169 arp_standard.debug('-- distribution record inserted. dist_line_id = '||
1170 to_char(l_dist_line_id));
1171
1172 arp_standard.debug('update_cr_history_confirm()-');
1173
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 arp_util.debug('EXCEPTION: update_cr_history_confirm()');
1177 RAISE;
1178
1179 END; -- update_cr_history_confirm()
1180
1181
1182
1183 /*===========================================================================+
1184 | PROCEDURE |
1185 | confirm_update_ps_rec |
1186 | |
1187 | DESCRIPTION |
1188 | This function updates the payment schedule record for a cash receipt |
1189 | after all applications have been processed. It basically sets the |
1190 | amount_due_remaining to zero, the amount_due_original to the receipt |
1191 | amount, and the receipt_confirmed_flag to 'Y'. It also sets the |
1192 | closed flag and the closed date and gl date. |
1193 | |
1194 | SCOPE - PRIVATE |
1195 | |
1196 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1197 | |
1198 | ARGUMENTS |
1199 | IN: |
1200 | p_cr_rec receipt record |
1201 | p_closed_date closed date |
1202 | p_closed_gl_date closed gl date |
1203 | |
1204 | OUT: |
1205 | |
1206 | RETURNS |
1207 | |
1208 | NOTES |
1209 | |
1210 | MODIFICATION HISTORY |
1211 | |
1212 | 18-AUG-95 OSTEINME created |
1213 | |
1214 +===========================================================================*/
1215
1216 PROCEDURE confirm_update_ps_rec(
1217 p_cr_rec ar_cash_receipts%ROWTYPE,
1218 p_closed_date DATE,
1219 p_closed_gl_date DATE
1220 ) IS
1221
1222 l_receipt_ps_rec ar_payment_schedules%ROWTYPE;
1223 l_dummy NUMBER;
1224
1225 BEGIN
1226
1227 arp_standard.debug('arp_confirmation.confirm_update_ps_rec()+');
1228 -- Fetch receipt's payment schedule record:
1229
1230 SELECT *
1231 INTO l_receipt_ps_rec
1232 FROM ar_payment_schedules
1233 WHERE cash_receipt_id = p_cr_rec.cash_receipt_id;
1234
1235
1236 -- set confirmed flag to 'Y' to mark receipt as confirmed:
1237 l_receipt_ps_rec.receipt_confirmed_flag := 'Y';
1238
1239 -- Bug 1199703 : update ar_payment_schedules.gl_date when receipt is confirmed
1240 l_receipt_ps_rec.gl_date := p_closed_gl_date;
1241
1242 -- call utility handler routine to update payment schedule record:
1243
1244
1245 arp_ps_util.update_receipt_related_columns(
1246 NULL, -- no payment_schedule_id!
1247 p_cr_rec.amount,
1248 p_closed_date,
1249 p_closed_gl_date,
1250 l_dummy,
1251 l_receipt_ps_rec);
1252
1253 arp_standard.debug('arp_confirmation.confirm_update_ps_rec()-');
1254
1255 EXCEPTION
1256 WHEN OTHERS THEN
1257 arp_util.debug('EXCEPTION: arp_confirmation.confirm_update_ps_rec()');
1258 RAISE;
1259
1260 END; -- confirm_update_ps_rec()
1261
1262
1263 /*===========================================================================+
1264 | PROCEDURE |
1265 | modify_update_ra_rec |
1266 | |
1267 | DESCRIPTION |
1268 | This function updates the original UNAPP record for the cash receipt |
1269 | in ar_receivable_applications. |
1270 | It also determines the payment schedule id for the receipt, which is |
1271 | returned for future use. |
1272 | |
1273 | SCOPE - PRIVATE |
1274 | |
1275 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1276 | |
1277 | ARGUMENTS |
1278 | IN: |
1279 | p_cr_id - cash receipt id |
1280 | p_amount_applied - amount applied to invoices (= rec amount) |
1281 | p_acctd_amount_applied - accounted amount applied to invoices |
1282 | p_confirm_gl_date |
1283 | p_confirm_date |
1284 | |
1285 | OUT: |
1286 | |
1287 | RETURNS |
1288 | |
1289 | NOTES |
1290 | |
1291 | MODIFICATION HISTORY |
1292 | |
1293 | 18-AUG-95 OSTEINME created |
1294 | |
1295 +===========================================================================*/
1296
1297 PROCEDURE modify_update_ra_rec(
1298 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
1299 p_amount_applied IN NUMBER,
1300 p_acctd_amount_applied IN NUMBER,
1301 p_confirm_gl_date IN DATE,
1302 p_confirm_date IN DATE
1303 ) IS
1304
1305 l_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
1306 l_ae_doc_rec ae_doc_rec_type;
1307
1308 BEGIN
1309
1310 --
1311 --Release 11.5 VAT changes retrieve the unconfirmed UNAPP record as the application
1312 --id is required to create the accounting in the AR_DISTRIBUTIONS table
1313 --moved where clause from update to select
1314
1315 SELECT app.receivable_application_id
1316 INTO l_receivable_application_id
1317 FROM ar_receivable_applications app
1318 WHERE app.cash_receipt_id = p_cr_id
1319 AND app.status = 'UNAPP'
1320 AND app.confirmed_flag = 'N'
1321 AND app.reversal_gl_date IS NULL
1322 AND app.application_rule IN ('97.0', '40.0');
1323
1324 -- update record
1325
1326 arp_standard.debug('arp_confirmation.modify_update_ra_rec()+');
1327
1328 UPDATE ar_receivable_applications
1329 SET gl_date = p_confirm_gl_date,
1330 apply_date = p_confirm_date,
1331 amount_applied = p_amount_applied,
1332 acctd_amount_applied_from = p_acctd_amount_applied,
1333 confirmed_flag = 'Y',
1334 postable = 'Y',
1335 last_update_date = TRUNC(SYSDATE),
1336 last_updated_by = FND_GLOBAL.user_id
1337 WHERE receivable_application_id = l_receivable_application_id;
1338
1339 -- call mrc to replicate the data
1340 ar_mrc_engine3.confirm_ra_rec_update(
1341 l_receivable_application_id);
1342
1343 --
1344 --Release 11.5 VAT changes, create the application accounting for
1345 --confirmed UNAPP record in ar_distributions. In this case we create
1346 --the UNAPP directly as only confirmed UNAPP records have accounting created
1347 --
1348 l_ae_doc_rec.document_type := 'RECEIPT';
1349 l_ae_doc_rec.document_id := p_cr_id;
1350 l_ae_doc_rec.accounting_entity_level := 'ONE';
1351 l_ae_doc_rec.source_table := 'RA';
1352 l_ae_doc_rec.source_id := l_receivable_application_id; --id of UNAPP record
1353 l_ae_doc_rec.source_id_old := '';
1354 l_ae_doc_rec.other_flag := '';
1355 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1356
1357
1358 arp_standard.debug('arp_confirmation.modify_update_ra_rec()+');
1359
1360 EXCEPTION
1361 WHEN NO_DATA_FOUND THEN
1362 arp_util.debug('EXCEPTION: NO DATA FOUND, arp_confirmation.modify_update_ra_rec()');
1363 RAISE;
1364
1365 WHEN OTHERS THEN
1366 arp_util.debug('EXCEPTION: arp_confirmation.modify_update_ra_rec()');
1367 RAISE;
1368
1369 END; -- modify_update_ra_rec()
1370
1371
1372 /*===========================================================================+
1373 | PROCEDURE |
1374 | create_matching_unapp_records |
1375 | |
1376 | DESCRIPTION |
1377 | This function creates a set of UNAPP records in ar_receivable_appl. |
1378 | to debit the unapplied account. |
1379 | |
1380 | SCOPE - PRIVATE |
1381 | |
1382 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1383 | |
1384 | ARGUMENTS |
1385 | IN: |
1386 | p_cr_id - cash receipt id |
1387 | |
1388 | OUT: |
1389 | |
1390 | RETURNS |
1391 | |
1392 | NOTES |
1393 | |
1394 | MODIFICATION HISTORY |
1395 | |
1396 | 21-AUG-95 OSTEINME created |
1397 | 04-DEC-97 KLAWRANC Bug fix #567872. The corresponding UNAPP row|
1398 | should not have the acct_amount_applied_to |
1399 | populated. This applies to the trx APP row |
1400 | only. |
1401 | 03/01/01 RYELURI Bug Fix 1640890 |
1402 | 03-Sep-02 Debbie Jancis Modified for mrc trigger replacment. Added |
1403 | calls to ar_mrc_engine3 to process |
1404 | receivable apps data |
1405 +===========================================================================*/
1406
1407 PROCEDURE create_matching_unapp_records(
1408 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
1409 p_app_id IN ar_receivable_applications.receivable_application_id%TYPE
1410 ) IS
1411
1412 l_unapp_id ar_receivable_applications.receivable_application_id%TYPE;
1413 l_ae_doc_rec ae_doc_rec_type;
1414
1415 /* Bug Fix 1640890. Fix invloves creating the following cursor
1416 and inserting the UNAPP record and calling the accounting package
1417 for every record that the cursor fetches. This is necessary in the
1418 cases where there are multiple APP records for a given cash receipt, and
1419 in such cases the previous insert was failing with a Unique constraint
1420 voilation on the receivable application id.
1421 With this modification the p_app_id passed as a parameter is effectively
1422 unncessary, and instead using the rec_app_id of the APP record from the
1423 cursor to PAIR the UNAPP record in ar_distributions to the APP record correctly
1424 */
1425
1426 CURSOR get_rec_records IS
1427 SELECT app.receivable_application_id app_id,
1428 -app.acctd_amount_applied_from acctd_amt_app_from,
1429 -app.amount_applied amt_app,
1430 app.application_type app_type,
1431 app.apply_date app_date,
1432 unapp.code_combination_id unapp_cc_id,
1433 app.gl_date app_gl_date,
1434 app.payment_schedule_id app_ps_id,
1435 app.set_of_books_id app_sob,
1436 app.cash_receipt_id app_cr_id,
1437 app.comments app_comments,
1438 app.days_late app_days_late,
1439 app.org_id app_org_id
1440 FROM ar_receivable_applications app,
1441 ar_receivable_applications unapp
1442 WHERE app.cash_receipt_id = p_cr_id
1443 AND app.status||'' = 'APP'
1444 AND app.reversal_gl_date IS NULL
1445 AND app.cash_receipt_id = unapp.cash_receipt_id
1446 AND unapp.application_rule = '97.0'
1447 AND unapp.status||'' = 'UNAPP';
1448
1449 BEGIN
1450
1451 arp_standard.debug('arp_confirmation.create_matching_unapp_records()+');
1452
1453
1454 FOR l_unapp_rec in get_rec_records LOOP
1455
1456 --Retrieve sequence id for receivable application id of UNAPP record
1457 --Note as this procedure creates a single UNAPP record hence this kind
1458 --of select from dual for sequence id is done
1459
1460 SELECT ar_receivable_applications_s.nextval
1461 INTO l_unapp_id
1462 FROM dual;
1463
1464 --Insert negative UNAPP record for confirmed APP record
1465 INSERT INTO ar_receivable_applications (
1466 receivable_application_id,
1467 acctd_amount_applied_from,
1468 amount_applied,
1469 application_rule,
1470 application_type,
1471 apply_date,
1472 code_combination_id,
1473 created_by,
1474 creation_date,
1475 display,
1476 gl_date,
1477 last_updated_by,
1478 last_update_date,
1479 payment_schedule_id,
1480 set_of_books_id,
1481 status,
1482 acctd_amount_applied_to,
1483 acctd_earned_discount_taken,
1484 acctd_unearned_discount_taken,
1485 applied_customer_trx_id,
1486 applied_customer_trx_line_id,
1487 applied_payment_schedule_id,
1488 cash_receipt_id,
1489 comments,
1490 confirmed_flag,
1491 customer_trx_id,
1492 days_late,
1493 earned_discount_taken,
1494 freight_applied,
1495 gl_posted_date,
1496 last_update_login,
1497 line_applied,
1498 on_account_customer,
1499 postable,
1500 posting_control_id,
1501 cash_receipt_history_id,
1502 program_application_id,
1503 program_id,
1504 program_update_date,
1505 receivables_charges_applied,
1506 receivables_trx_id,
1507 request_id,
1508 tax_applied,
1509 unearned_discount_taken,
1510 unearned_discount_ccid,
1511 earned_discount_ccid,
1512 ussgl_transaction_code,
1513 attribute_category,
1514 attribute1,
1515 attribute2,
1516 attribute3,
1517 attribute4,
1518 attribute5,
1519 attribute6,
1520 attribute7,
1521 attribute8,
1522 attribute9,
1523 attribute10,
1524 attribute11,
1525 attribute12,
1526 attribute13,
1527 attribute14,
1528 attribute15,
1529 ussgl_transaction_code_context,
1530 reversal_gl_date,
1531 org_id
1532 )
1533 VALUES (
1534 l_unapp_id,
1535 l_unapp_rec.acctd_amt_app_from,
1536 l_unapp_rec.amt_app,
1537 '40.4',
1538 l_unapp_rec.app_type,
1539 l_unapp_rec.app_date,
1540 l_unapp_rec.unapp_cc_id,
1541 FND_GLOBAL.user_id,
1542 TRUNC(sysdate),
1543 'N',
1544 l_unapp_rec.app_gl_date,
1545 FND_GLOBAL.user_id,
1546 TRUNC(sysdate),
1547 l_unapp_rec.app_ps_id,
1548 l_unapp_rec.app_sob,
1549 'UNAPP',
1550 NULL,
1551 NULL,
1552 NULL,
1553 NULL,
1554 NULL,
1555 NULL,
1556 l_unapp_rec.app_cr_id,
1557 l_unapp_rec.app_comments,
1558 'Y',
1559 NULL,
1560 l_unapp_rec.app_days_late,
1561 NULL,
1562 NULL,
1563 NULL,
1564 NULL,
1565 NULL,
1566 NULL,
1567 'Y',
1568 -3,
1569 NULL,
1570 NULL,
1571 NULL,
1572 NULL,
1573 NULL,
1574 NULL,
1575 NULL,
1576 NULL,
1577 NULL,
1578 NULL,
1579 NULL,
1580 NULL,
1581 NULL,
1582 NULL,
1583 NULL,
1584 NULL,
1585 NULL,
1586 NULL,
1587 NULL,
1588 NULL,
1589 NULL,
1590 NULL,
1591 NULL,
1592 NULL,
1593 NULL,
1594 NULL,
1595 NULL,
1596 NULL,
1597 NULL,
1598 NULL,
1599 l_unapp_rec.app_org_id );
1600
1601 -- need to call mrc engine to process unapp records before calling
1602 -- the accting engine.
1603
1604 ar_mrc_engine3.create_matching_unapp_records(
1605 p_rec_app_id => l_unapp_rec.app_id,
1606 p_rec_unapp_id => l_unapp_id);
1607 --
1608 --Release 11.5 VAT changes, create the application accounting for
1609 --confirmed UNAPP record in ar_distributions. In this case we create
1610 --the UNAPP directly as only confirmed UNAPP records have accounting created
1611 --basically we dont require the module below to be called in update mode
1612 --(delete + create)
1613
1614 l_ae_doc_rec.document_type := 'RECEIPT';
1615 l_ae_doc_rec.document_id := p_cr_id;
1616 l_ae_doc_rec.accounting_entity_level := 'ONE';
1617 l_ae_doc_rec.source_table := 'RA';
1618 l_ae_doc_rec.source_id := l_unapp_id; --id of receivable UNAPP record
1619 l_ae_doc_rec.source_id_old := l_unapp_rec.app_id; /* Bug Fix 1640890 */
1620 l_ae_doc_rec.other_flag := 'PAIR';
1621 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1622
1623 END LOOP;
1624
1625 arp_standard.debug('arp_confirmation.create_matching_unapp_records()-');
1626
1627 EXCEPTION
1628 WHEN NO_DATA_FOUND THEN
1629 arp_util.debug('EXCEPTION: NO DATA FOUND arp_confirmation.create_matching_unapp_records()');
1630 RAISE;
1631
1632 WHEN OTHERS THEN
1633 arp_util.debug('EXCEPTION: arp_confirmation.create_matching_unapp_records()');
1634 RAISE;
1635
1636 END; -- create_matching_unapp_records()
1637
1638
1639 /*===========================================================================+
1640 | PROCEDURE |
1641 | get_receipt_clearing_ccid |
1642 | |
1643 | DESCRIPTION |
1644 | Determines the receipt clearing code combination id from the |
1645 | ar_receipt_method_accounts table. |
1646 | |
1647 | SCOPE - PRIVATE |
1648 | |
1649 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1650 | |
1651 | ARGUMENTS |
1652 | IN: |
1653 | p_cr_rec - cash receipt record |
1654 | OUT: |
1655 | p_receipt_clearing_ccid - ccid |
1656 | |
1657 | RETURNS |
1658 | |
1659 | NOTES |
1660 | |
1661 | MODIFICATION HISTORY |
1662 | |
1663 | 18-AUG-95 OSTEINME created |
1664 | |
1665 +===========================================================================*/
1666
1667 PROCEDURE get_receipt_clearing_ccid(
1668 p_cr_rec IN ar_cash_receipts%ROWTYPE,
1669 p_receipt_clearing_ccid OUT NOCOPY
1670 ar_receipt_method_accounts.receipt_clearing_ccid%TYPE
1671 ) IS
1672
1673 BEGIN
1674
1675 arp_standard.debug('arp_confirmation.get_receipt_clearing_ccid()+');
1676
1677 SELECT rma.receipt_clearing_ccid
1678 INTO p_receipt_clearing_ccid
1679 FROM ar_receipt_method_accounts rma
1680 WHERE rma.remit_bank_acct_use_id = p_cr_rec.remit_bank_acct_use_id
1681 AND rma.receipt_method_id = p_cr_rec.receipt_method_id;
1682
1683 arp_standard.debug('arp_confirmation.get_receipt_clearing_ccid()-');
1684
1685 EXCEPTION
1686 WHEN OTHERS THEN
1687 arp_util.debug('EXCEPTION: arp_confirmation.get_receipt_clearing_ccid');
1688 RAISE;
1689
1690 END; -- get_receipt_clearing_ccid()
1691
1692
1693 /*===========================================================================+
1694 | PROCEDURE |
1695 | update_cr_history_unconfirm |
1696 | |
1697 | DESCRIPTION |
1698 | Creates a new entry for the cash_receipt_history table. |
1699 | It will have the updated receipt amount and the status |
1700 | 'APPROVED'. Also creates an ar_distributions record for |
1701 | the new history record. |
1702 | |
1703 | SCOPE - PRIVATE |
1704 | |
1705 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1706 | |
1707 | ARGUMENTS |
1708 | IN: |
1709 | |
1710 | p_cr_rec - cash receipt for which the history entry |
1711 | is to be created |
1712 | p_confirm_gl_date - Unconfirm GL date |
1713 | p_confirm_date - Unconfirm Date |
1714 | p_acctd_amount - accounted cash receipt amount |
1715 | |
1716 | OUT: |
1717 | |
1718 | p_batch_id - batch id of cash receipt (from crh table) |
1719 | p_crh_id_rev - crh_id of record to be reversed |
1720 | |
1721 | RETURNS |
1722 | |
1723 | NOTES |
1724 | |
1725 | MODIFICATION HISTORY |
1726 | |
1727 | 24-AUG-95 OSTEINME created |
1728 | |
1729 +===========================================================================*/
1730
1731
1732 PROCEDURE update_cr_history_unconfirm(
1733 p_cr_rec IN ar_cash_receipts%ROWTYPE,
1734 p_confirm_gl_date IN DATE,
1735 p_confirm_date IN DATE,
1736 p_acctd_amount IN NUMBER,
1737 p_batch_id OUT NOCOPY ar_cash_receipt_history.batch_id%TYPE,
1738 p_crh_id_rev OUT NOCOPY
1739 ar_cash_receipt_history.cash_receipt_history_id%TYPE
1740 ) IS
1741
1742 l_crh_rec_old ar_cash_receipt_history%ROWTYPE;
1743 l_crh_rec_prev_stat ar_cash_receipt_history%ROWTYPE;
1744 l_crh_rec_new ar_cash_receipt_history%ROWTYPE;
1745 l_dist_rec ar_distributions%ROWTYPE;
1746 l_dist_line_id ar_distributions.line_id%TYPE;
1747 l_batch_id ar_cash_receipt_history.batch_id%TYPE;
1748 l_crh_id_rev ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1749 l_crh_id_new ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1750
1751 BEGIN
1752
1753 arp_standard.debug('arp_confirmation.update_cr_history_unconfirm()+');
1754
1755 -- fetch current record from ar_cash_receipt_history
1756
1757 arp_cr_history_pkg.fetch_f_crid(p_cr_rec.cash_receipt_id, l_crh_rec_old);
1758
1759 -- update columns in existing record
1760
1761 l_crh_rec_old.reversal_gl_date := p_confirm_gl_date;
1762 l_crh_rec_old.reversal_posting_control_id := -3;
1763 l_crh_rec_old.reversal_created_from := 'ARRECNF';
1764 l_crh_rec_old.current_record_flag := NULL;
1765 l_crh_rec_old.first_posted_record_flag := 'N';
1766 l_crh_rec_old.current_record_flag := NULL;
1767
1768 -- Fetch previous state record from ar_cash_receipt_history:
1769 -- The current record is not necessarily the one created by
1770 -- the confirm operation, because rate adjustments could have
1771 -- occured between confirmation and unconfirmation. In order
1772 -- to get the correct batch_id, we have to get it from the record
1773 -- that is pointed to by prv_stat_cash_receipt_hist_id in the current
1774 -- crh record.
1775
1776 arp_cr_history_pkg.fetch_p(l_crh_rec_old.prv_stat_cash_receipt_hist_id,
1777 l_crh_rec_prev_stat);
1778
1779 l_batch_id := l_crh_rec_prev_stat.batch_id;
1780 l_crh_id_rev := l_crh_rec_prev_stat.reversal_cash_receipt_hist_id;
1781
1782 -- create new record:
1783
1784 l_crh_rec_new.amount := p_cr_rec.amount;
1785 l_crh_rec_new.acctd_amount := p_acctd_amount;
1786 l_crh_rec_new.cash_receipt_id := p_cr_rec.cash_receipt_id;
1787 l_crh_rec_new.factor_flag := 'N';
1788 l_crh_rec_new.first_posted_record_flag := 'N';
1789 l_crh_rec_new.gl_date := p_confirm_gl_date;
1790 l_crh_rec_new.postable_flag := 'Y';
1791 l_crh_rec_new.posting_control_id := -3;
1792 l_crh_rec_new.status := 'APPROVED';
1793 l_crh_rec_new.trx_date := p_confirm_gl_date;
1794 l_crh_rec_new.acctd_factor_discount_amount := NULL;
1795 l_crh_rec_new.account_code_combination_id := NULL;
1796 l_crh_rec_new.bank_charge_account_ccid := NULL;
1797 l_crh_rec_new.batch_id := l_batch_id;
1798 l_crh_rec_new.current_record_flag := 'Y';
1799 l_crh_rec_new.exchange_date := p_cr_rec.exchange_date;
1800 l_crh_rec_new.exchange_rate := p_cr_rec.exchange_rate;
1801 l_crh_rec_new.exchange_rate_type := p_cr_rec.exchange_rate_type;
1802 l_crh_rec_new.factor_discount_amount := NULL;
1803 l_crh_rec_new.gl_posted_date := NULL;
1804 l_crh_rec_new.request_id := NULL;
1805 l_crh_rec_new.created_from := 'ARRECNF';
1806 l_crh_rec_new.prv_stat_cash_receipt_hist_id := l_crh_rec_old.cash_receipt_history_id;
1807
1808 -- insert new current record into cash receipt history table
1809
1810 arp_cr_history_pkg.insert_p(l_crh_rec_new, l_crh_id_new);
1811
1812 -- link new current record to previous current record and update the latter:
1813
1814 l_crh_rec_old.reversal_cash_receipt_hist_id := l_crh_id_new;
1815 arp_cr_history_pkg.update_p(l_crh_rec_old);
1816
1817
1818 -- create ar_distributions record for new history record.
1819 -- first fetch record that was created for the history table
1820 -- record to be reversed:
1821
1822 arp_distributions_pkg.fetch_pk(l_crh_id_rev,
1823 'CRH',
1824 'CONFIRMATION',
1825 l_dist_rec);
1826
1827 -- now update relevant columns:
1828
1829 l_dist_rec.source_id := l_crh_id_new;
1830 l_dist_rec.source_table := 'CRH';
1831 l_dist_rec.source_type := 'CONFIRMATION';
1832 l_dist_rec.last_update_date := SYSDATE;
1833 l_dist_rec.last_updated_by := FND_GLOBAL.user_id;
1834 l_dist_rec.creation_date := SYSDATE;
1835 l_dist_rec.created_by := FND_GLOBAL.user_id;
1836
1837 IF p_cr_rec.amount < 0 THEN
1838 l_dist_rec.amount_dr := -p_cr_rec.amount;
1839 l_dist_rec.amount_cr := NULL;
1840 ELSE
1841 l_dist_rec.amount_dr := NULL;
1842 l_dist_rec.amount_cr := p_cr_rec.amount;
1843 END IF;
1844
1845 IF p_acctd_amount < 0 THEN
1846 l_dist_rec.acctd_amount_dr := -p_acctd_amount;
1847 l_dist_rec.acctd_amount_cr := NULL;
1848 ELSE
1849 l_dist_rec.acctd_amount_dr := NULL;
1850 l_dist_rec.acctd_amount_cr := p_acctd_amount;
1851 END IF;
1852
1853 arp_distributions_pkg.insert_p(l_dist_rec, l_dist_line_id);
1854
1855 /* need to insert records into the MRC table. Calling new
1856 mrc engine */
1857
1858 ar_mrc_engine2.maintain_mrc_data2(
1859 p_event_mode => 'INSERT',
1860 p_table_name => 'AR_DISTRIBUTIONS',
1861 p_mode => 'SINGLE',
1862 p_key_value => l_dist_line_id,
1863 p_row_info => l_dist_rec);
1864
1865 -- prepare return variables:
1866
1867 p_batch_id := l_batch_id;
1868 p_crh_id_rev := l_crh_id_rev;
1869
1870 arp_standard.debug('arp_confirmation.update_cr_history_unconfirm()-');
1871
1872
1873 EXCEPTION
1874 WHEN OTHERS THEN
1875 arp_util.debug('EXCEPTION: arp_confirmation.update_cr_history_unconfirm()');
1876 RAISE;
1877
1878 END; -- update_cr_history_unconfirm()
1879
1880
1881
1882 /*===========================================================================+
1883 | PROCEDURE |
1884 | reverse_application_to_ps |
1885 | |
1886 | DESCRIPTION |
1887 | reverses the effect of an application to an invoice payment schedule. |
1888 | |
1889 | SCOPE - PRIVATE |
1890 | |
1891 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1892 | |
1893 | ARGUMENTS |
1894 | IN: |
1895 | OUT: |
1896 | |
1897 | RETURNS |
1898 | |
1899 | NOTES |
1900 | |
1901 | MODIFICATION HISTORY |
1902 | |
1903 | 01-SEP-95 OSTEINME created |
1904 | 08-NOV-01 Debbie Jancis Modified for mrc trigger elimination project |
1905 | added calls to ar_mrc_engine for updates to |
1906 | ar_payment_schedules. |
1907 +===========================================================================*/
1908
1909
1910 PROCEDURE reverse_application_to_ps(
1911 p_ra_id IN
1912 ar_receivable_applications.receivable_application_id%TYPE,
1913 p_confirm_gl_date IN DATE,
1914 p_confirm_date IN DATE,
1915 p_batch_id IN
1916 ar_payment_schedules.selected_for_receipt_batch_id%TYPE
1917 ) IS
1918
1919 l_ar_ps_key_value_list gl_ca_utility_pkg.r_key_value_arr;
1920
1921 BEGIN
1922
1923 arp_standard.debug('arp_confirmation.reverse_application_to_ps()+');
1924
1925 UPDATE
1926 ar_payment_schedules ps
1927 SET (
1928 status,
1929 gl_date_closed,
1930 actual_date_closed,
1931 amount_applied,
1932 amount_due_remaining,
1933 acctd_amount_due_remaining,
1934 amount_line_items_remaining,
1935 tax_remaining,
1936 freight_remaining,
1937 receivables_charges_remaining,
1938 selected_for_receipt_batch_id,
1939 last_updated_by,
1940 last_update_date,
1941 last_update_login) = (
1942 SELECT
1943 decode(ps2.amount_due_remaining + ra.amount_applied,0,'CL','OP'),
1944 decode(ps2.amount_due_remaining + ra.amount_applied,
1945 0,
1946 fnd_date.canonical_to_date(greatest(max(ra2.gl_date),
1947 nvl(max(decode(adj2.status,
1948 'A',adj2.gl_date,
1949 nvl(ps2.gl_date,
1950 ps2.trx_date))),
1951 nvl(ps2.gl_date,ps2.trx_date)),
1952 nvl(ps2.gl_date, ps2.trx_date))
1953 ),
1954 ''),
1955 decode(ps2.amount_due_remaining + ra.amount_applied,
1956 0,
1957 fnd_date.canonical_to_date(greatest(max(ra2.apply_date),
1958 nvl(max(decode(adj2.status,
1959 'A',adj2.apply_date,
1960 ps2.trx_date)),
1961 ps2.trx_date),
1962 ps2.trx_date)
1963 ),
1964 ''),
1965 nvl(ps2.amount_applied,0) - ra.amount_applied,
1966 ps2.amount_due_remaining + ra.amount_applied,
1967 ps2.acctd_amount_due_remaining + nvl(ra.acctd_amount_applied_to,0),
1968 nvl(ps2.amount_line_items_remaining,0) + nvl(ra.line_applied,0),
1969 nvl(ps2.tax_remaining,0) + nvl(ra.tax_applied,0),
1970 nvl(ps2.freight_remaining,0) + nvl(ra.freight_applied,0),
1971 nvl(ps2.receivables_charges_remaining,0) +
1972 nvl(ra.receivables_charges_applied,0),
1973 p_batch_id,
1974 FND_GLOBAL.user_id,
1975 trunc(sysdate),
1976 FND_GLOBAL.user_id
1977 FROM
1978 ar_receivable_applications ra,
1979 ar_payment_schedules ps2,
1980 ar_adjustments adj2,
1981 ar_receivable_applications ra2
1982 WHERE
1983 ra.receivable_application_id = p_ra_id
1984 AND ra.applied_payment_schedule_id = ps2.payment_schedule_id
1985 AND ps2.payment_schedule_id =ps.payment_schedule_id
1986 AND ps2.payment_schedule_id = adj2.payment_schedule_id(+)
1987 AND ps2.payment_schedule_id = ra2.applied_payment_schedule_id
1988 AND nvl(ra2.confirmed_flag,'Y')= 'Y'
1989 GROUP BY
1990 ps2.payment_schedule_id,
1991 ra2.applied_payment_schedule_id,
1992 adj2.payment_schedule_id,
1993 ps2.amount_due_remaining,
1994 ra.amount_applied,
1995 ps2.gl_date,
1996 ps2.trx_date,
1997 ps2.amount_applied,
1998 ps2.acctd_amount_due_remaining,
1999 ra.acctd_amount_applied_to,
2000 ps2.amount_line_items_remaining,
2001 ra.line_applied,
2002 ps2.tax_remaining,
2003 ra.tax_applied,
2004 ps2.freight_remaining,
2005 ra.freight_applied,
2006 ps2.receivables_charges_remaining,
2007 ra.receivables_charges_applied)
2008 WHERE ps.payment_schedule_id in ( SELECT
2009 ra3.applied_payment_schedule_id
2010 FROM
2011 ar_receivable_applications ra3
2012 WHERE
2013 ra3.receivable_application_id =
2014 p_ra_id)
2015 RETURNING ps.payment_schedule_id
2016 BULK COLLECT INTO l_ar_ps_key_value_list;
2017
2018 /*---------------------------------+
2019 | Calling central MRC library |
2020 | for MRC Integration |
2021 +---------------------------------*/
2022
2023 ar_mrc_engine.maintain_mrc_data(
2024 p_event_mode => 'UPDATE',
2025 p_table_name => 'AR_PAYMENT_SCHEDULES',
2026 p_mode => 'BATCH',
2027 p_key_value_list => l_ar_ps_key_value_list);
2028
2029 arp_standard.debug('arp_confirmation.reverse_application_to_ps()-');
2030
2031 EXCEPTION
2032 WHEN OTHERS THEN
2033 arp_util.debug('EXCEPTION: arp_confirmation.reverse_application_to_ps()');
2034 RAISE;
2035
2036 END; -- reverse_application_to_ps()
2037
2038
2039
2040 /*===========================================================================+
2041 | PROCEDURE |
2042 | reverse_ra_recs |
2043 | |
2044 | DESCRIPTION |
2045 | This function reverses existing receivable application records for |
2046 | the do_unconfirm() function. |
2047 | |
2048 | SCOPE - PRIVATE |
2049 | |
2050 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2051 | |
2052 | ARGUMENTS |
2053 | IN: |
2054 | OUT: |
2055 | |
2056 | RETURNS |
2057 | |
2058 | NOTES |
2059 | |
2060 | MODIFICATION HISTORY |
2061 | |
2062 | 01-SEP-95 OSTEINME created |
2063 | 03-SEP-02 Debbie Jancis Modified for mrc trigger replacment. |
2064 | added calls to mrc engine3 for processing |
2065 | receivable applications. |
2066 +===========================================================================*/
2067
2068 PROCEDURE reverse_ra_recs(
2069 p_cr_rec IN ar_cash_receipts%ROWTYPE,
2070 p_confirm_gl_date IN DATE,
2071 p_confirm_date IN DATE
2072 ) IS
2073 CURSOR get_app IS
2074 SELECT app.receivable_application_id old_app_id
2075 FROM ar_receivable_applications app
2076 WHERE app.cash_receipt_id = p_cr_rec.cash_receipt_id
2077 AND app.reversal_gl_date IS NULL
2078 ORDER BY decode(app.status,
2079 'APP' ,1,
2080 'ACC' ,2,
2081 'UNID' ,3,
2082 'UNAPP',4); --This ordering is required for pairing UNAPP with APP record
2083
2084 l_app_rec get_app%ROWTYPE;
2085 l_new_app_id ar_receivable_applications.receivable_application_id%TYPE;
2086 l_ae_doc_rec ae_doc_rec_type;
2087
2088 n_new_con_data new_con_data; /* to store values retrieved from bulk collect */
2089
2090 BEGIN
2091
2092 arp_standard.debug('arp_confirmation.reverse_ra_recs()+');
2093
2094 FOR l_app_rec IN get_app LOOP
2095
2096 --retrieve sequence
2097 SELECT ar_receivable_applications_s.nextval
2098 INTO l_new_app_id
2099 FROM dual;
2100
2101 --Create actual reversing apps
2102 INSERT INTO ar_receivable_applications
2103 (receivable_application_id,
2104 acctd_amount_applied_from,
2105 amount_applied,
2106 application_rule,
2107 application_type,
2108 apply_date,
2109 code_combination_id,
2110 created_by,
2111 creation_date,
2112 display,
2113 gl_date,
2114 last_updated_by,
2115 last_update_date,
2116 payment_schedule_id,
2117 set_of_books_id,
2118 status,
2119 acctd_amount_applied_to,
2120 acctd_earned_discount_taken,
2121 acctd_unearned_discount_taken,
2122 applied_customer_trx_id,
2123 applied_customer_trx_line_id,
2124 applied_payment_schedule_id,
2125 cash_receipt_id,
2126 comments,
2127 confirmed_flag,
2128 customer_trx_id,
2129 days_late,
2130 earned_discount_taken,
2131 freight_applied,
2132 gl_posted_date,
2133 last_update_login,
2134 line_applied,
2135 on_account_customer,
2136 postable,
2137 posting_control_id,
2138 cash_receipt_history_id,
2139 program_application_id,
2140 program_id,
2141 program_update_date,
2142 receivables_charges_applied,
2143 receivables_trx_id,
2144 request_id,
2145 tax_applied,
2146 unearned_discount_taken,
2147 unearned_discount_ccid,
2148 earned_discount_ccid,
2149 ussgl_transaction_code,
2150 attribute_category,
2151 attribute1,
2152 attribute2,
2153 attribute3,
2154 attribute4,
2155 attribute5,
2156 attribute6,
2157 attribute7,
2158 attribute8,
2159 attribute9,
2160 attribute10,
2161 attribute11,
2162 attribute12,
2163 attribute13,
2164 attribute14,
2165 attribute15,
2166 ussgl_transaction_code_context,
2167 reversal_gl_date,
2168 org_id
2169 )
2170 SELECT l_new_app_id,
2171 -acctd_amount_applied_from,
2172 -amount_applied,
2173 '40.2',
2174 application_type,
2175 p_confirm_gl_date,
2176 code_combination_id,
2177 FND_GLOBAL.user_id,
2178 TRUNC(SYSDATE),
2179 'N',
2180 p_confirm_gl_date,
2181 FND_GLOBAL.user_id,
2182 TRUNC(SYSDATE),
2183 payment_schedule_id,
2184 set_of_books_id,
2185 status,
2186 -acctd_amount_applied_to,
2187 -acctd_earned_discount_taken,
2188 -acctd_unearned_discount_taken,
2189 applied_customer_trx_id,
2190 applied_customer_trx_line_id,
2191 applied_payment_schedule_id,
2192 cash_receipt_id,
2193 comments,
2194 confirmed_flag,
2195 customer_trx_id,
2196 days_late,
2197 -earned_discount_taken,
2198 -freight_applied,
2199 NULL,
2200 last_update_login,
2201 -line_applied,
2202 on_account_customer,
2203 postable,
2204 -3,
2205 NULL,
2206 program_application_id,
2207 program_id,
2208 program_update_date,
2209 -receivables_charges_applied,
2210 receivables_trx_id,
2211 request_id,
2212 -tax_applied,
2213 -unearned_discount_taken,
2214 unearned_discount_ccid,
2215 earned_discount_ccid,
2216 ussgl_transaction_code,
2217 attribute_category,
2218 attribute1,
2219 attribute2,
2220 attribute3,
2221 attribute4,
2222 attribute5,
2223 attribute6,
2224 attribute7,
2225 attribute8,
2226 attribute9,
2227 attribute10,
2228 attribute11,
2229 attribute12,
2230 attribute13,
2231 attribute14,
2232 attribute15,
2233 ussgl_transaction_code_context,
2234 p_confirm_gl_date,
2235 org_id
2236 FROM ar_receivable_applications
2237 WHERE receivable_application_id = l_app_rec.old_app_id;
2238
2239 -- Call mrc engine to create the data in mc tables
2240 ar_mrc_engine3.reverse_ra_recs(
2241 p_orig_app_id => l_app_rec.old_app_id,
2242 p_new_app_id => l_new_app_id);
2243
2244 --apandit
2245 --Bug 2641517 rase business event for unapplication, we do not raise the
2246 --seperate unconfirm event as the unapplication takes care
2247 --of updating the summary tables.
2248 arp_standard.debug( 'before raising the business event : Raise_CR_UnApply_Event');
2249 AR_BUS_EVENT_COVER.Raise_CR_UnApply_Event( l_new_app_id);
2250
2251
2252 --
2253 --Release 11.5 VAT changes, reverse the application accounting for
2254 --confirmed records in ar_distributions.
2255 --
2256 l_ae_doc_rec.document_type := 'RECEIPT';
2257 l_ae_doc_rec.document_id := p_cr_rec.cash_receipt_id;
2258 l_ae_doc_rec.accounting_entity_level := 'ONE';
2259 l_ae_doc_rec.source_table := 'RA';
2260 l_ae_doc_rec.source_id := l_new_app_id; --new record
2261 l_ae_doc_rec.source_id_old := l_app_rec.old_app_id; --old record for reversal
2262 l_ae_doc_rec.other_flag := 'REVERSE';
2263
2264 --Bug 1329091 - PS is updated before Accounting Engine Call
2265 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
2266 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
2267
2268 END LOOP;
2269
2270 -- create new unconfirmed records from old confirmed records:
2271
2272 SELECT
2273 receivable_application_id,
2274 ar_receivable_applications_s.nextval,
2275 acctd_amount_applied_from,
2276 amount_applied,
2277 DECODE(status,
2278 'UNAPP', '40.0',
2279 '40.3'),
2280 application_type,
2281 p_confirm_gl_date,
2282 code_combination_id,
2283 FND_GLOBAL.user_id,
2284 TRUNC(SYSDATE),
2285 display,
2286 p_confirm_gl_date,
2287 FND_GLOBAL.user_id,
2288 TRUNC(SYSDATE),
2289 payment_schedule_id,
2290 set_of_books_id,
2291 status,
2292 acctd_amount_applied_to,
2293 DECODE(status,
2294 'UNAPP', NULL,
2295 acctd_earned_discount_taken),
2296 DECODE(status,
2297 'UNAPP', NULL,
2298 acctd_unearned_discount_taken),
2299 DECODE(status,
2300 'UNAPP', NULL,
2301 applied_customer_trx_id),
2302 DECODE(status,
2303 'UNAPP', NULL,
2304 applied_customer_trx_line_id),
2305 DECODE(status,
2306 'UNAPP', NULL,
2307 applied_payment_schedule_id),
2308 cash_receipt_id,
2309 comments,
2310 'N',
2311 customer_trx_id,
2312 days_late,
2313 DECODE(status,
2314 'UNAPP', NULL,
2315 earned_discount_taken),
2316 DECODE(status,
2317 'UNAPP', NULL,
2318 freight_applied),
2319 NULL,
2320 last_update_login,
2321 DECODE(status,
2322 'UNAPP', NULL,
2323 line_applied),
2324 on_account_customer,
2325 'N',
2326 -3,
2327 NULL,
2328 program_application_id,
2329 program_id,
2330 program_update_date,
2331 DECODE(status,
2332 'UNAPP', NULL,
2333 receivables_charges_applied),
2334 receivables_trx_id,
2335 request_id,
2336 DECODE(status,
2337 'UNAPP', NULL,
2338 tax_applied),
2339 DECODE(status,
2340 'UNAPP', NULL,
2341 unearned_discount_taken),
2342 unearned_discount_ccid,
2343 earned_discount_ccid,
2344 ussgl_transaction_code,
2345 attribute_category,
2346 attribute1,
2347 attribute2,
2348 attribute3,
2349 attribute4,
2350 attribute5,
2351 attribute6,
2352 attribute7,
2353 attribute8,
2354 attribute9,
2355 attribute10,
2356 attribute11,
2357 attribute12,
2358 attribute13,
2359 attribute14,
2360 attribute15,
2361 ussgl_transaction_code_context,
2362 NULL,
2363 org_id
2364 BULK COLLECT INTO
2365 n_new_con_data.l_old_rec_app_id,
2366 n_new_con_data.l_new_rec_app_id,
2367 n_new_con_data.l_acctd_amount_applied_from,
2368 n_new_con_data.l_amount_applied,
2369 n_new_con_data.l_application_rule,
2370 n_new_con_data.l_application_type,
2371 n_new_con_data.l_apply_date,
2372 n_new_con_data.l_code_combination_id,
2373 n_new_con_data.l_created_by,
2374 n_new_con_data.l_creation_date,
2375 n_new_con_data.l_display,
2376 n_new_con_data.l_gl_date,
2377 n_new_con_data.l_last_updated_by,
2378 n_new_con_data.l_last_update_date,
2379 n_new_con_data.l_payment_schedule_id,
2380 n_new_con_data.l_set_of_books_id,
2381 n_new_con_data.l_status,
2382 n_new_con_data.l_acctd_amount_applied_to,
2383 n_new_con_data.l_acctd_earned_discount_tkn,
2384 n_new_con_data.l_acctd_unearned_discount_tkn,
2385 n_new_con_data.l_applied_customer_trx_id,
2386 n_new_con_data.l_applied_customer_trx_line_id,
2387 n_new_con_data.l_applied_payment_schedule_id,
2388 n_new_con_data.l_cash_receipt_id,
2389 n_new_con_data.l_comments,
2390 n_new_con_data.l_confirmed_flag,
2391 n_new_con_data.l_customer_trx_id,
2392 n_new_con_data.l_days_late,
2393 n_new_con_data.l_earned_discount_taken,
2394 n_new_con_data.l_freight_applied,
2395 n_new_con_data.l_gl_posted_date,
2396 n_new_con_data.l_last_update_login,
2397 n_new_con_data.l_line_applied,
2398 n_new_con_data.l_on_account_customer,
2399 n_new_con_data.l_postable,
2400 n_new_con_data.l_posting_control_id,
2401 n_new_con_data.l_cash_receipt_history_id,
2402 n_new_con_data.l_program_application_id,
2403 n_new_con_data.l_program_id,
2404 n_new_con_data.l_program_update_date,
2405 n_new_con_data.l_receivables_charges_applied,
2406 n_new_con_data.l_receivables_trx_id,
2407 n_new_con_data.l_request_id,
2408 n_new_con_data.l_tax_applied,
2409 n_new_con_data.l_unearned_discount_taken,
2410 n_new_con_data.l_unearned_discount_ccid,
2411 n_new_con_data.l_earned_discount_ccid,
2412 n_new_con_data.l_ussgl_transaction_code,
2413 n_new_con_data.l_attribute_category,
2414 n_new_con_data.l_attribute1,
2415 n_new_con_data.l_attribute2,
2416 n_new_con_data.l_attribute3,
2417 n_new_con_data.l_attribute4,
2418 n_new_con_data.l_attribute5,
2419 n_new_con_data.l_attribute6,
2420 n_new_con_data.l_attribute7,
2421 n_new_con_data.l_attribute8,
2422 n_new_con_data.l_attribute9,
2423 n_new_con_data.l_attribute10,
2424 n_new_con_data.l_attribute11,
2425 n_new_con_data.l_attribute12,
2426 n_new_con_data.l_attribute13,
2427 n_new_con_data.l_attribute14,
2428 n_new_con_data.l_attribute15,
2429 n_new_con_data.l_ussgl_transaction_code_cntxt,
2430 n_new_con_data.l_reversal_gl_date,
2431 n_new_con_data.l_org_id
2432 FROM ar_receivable_applications
2433 WHERE cash_receipt_id = p_cr_rec.cash_receipt_id
2434 AND ( status = 'APP'
2435 OR
2436 ( status = 'UNAPP'
2437 AND application_rule in ('97.0', '40.0')
2438 )
2439 )
2440 AND reversal_gl_date IS NULL;
2441
2442
2443 -- MRC trigger replacement.. Do a bulk collect and pass
2444
2445 FORALL i IN 1..n_new_con_data.l_reversal_gl_date.COUNT
2446 INSERT INTO ar_receivable_applications
2447 (receivable_application_id,
2448 acctd_amount_applied_from,
2449 amount_applied,
2450 application_rule,
2451 application_type,
2452 apply_date,
2453 code_combination_id,
2454 created_by,
2455 creation_date,
2456 display,
2457 gl_date,
2458 last_updated_by,
2459 last_update_date,
2460 payment_schedule_id,
2461 set_of_books_id,
2462 status,
2463 acctd_amount_applied_to,
2464 acctd_earned_discount_taken,
2465 acctd_unearned_discount_taken,
2466 applied_customer_trx_id,
2467 applied_customer_trx_line_id,
2468 applied_payment_schedule_id,
2469 cash_receipt_id,
2470 comments,
2471 confirmed_flag,
2472 customer_trx_id,
2473 days_late,
2474 earned_discount_taken,
2475 freight_applied,
2476 gl_posted_date,
2477 last_update_login,
2478 line_applied,
2479 on_account_customer,
2480 postable,
2481 posting_control_id,
2482 cash_receipt_history_id,
2483 program_application_id,
2484 program_id,
2485 program_update_date,
2486 receivables_charges_applied,
2487 receivables_trx_id,
2488 request_id,
2489 tax_applied,
2490 unearned_discount_taken,
2491 unearned_discount_ccid,
2492 earned_discount_ccid,
2493 ussgl_transaction_code,
2494 attribute_category,
2495 attribute1,
2496 attribute2,
2497 attribute3,
2498 attribute4,
2499 attribute5,
2500 attribute6,
2501 attribute7,
2502 attribute8,
2503 attribute9,
2504 attribute10,
2505 attribute11,
2506 attribute12,
2507 attribute13,
2508 attribute14,
2509 attribute15,
2510 ussgl_transaction_code_context,
2511 reversal_gl_date,
2512 org_id
2513 )
2514 VALUES (
2515 n_new_con_data.l_new_rec_app_id(i),
2516 n_new_con_data.l_acctd_amount_applied_from(i),
2517 n_new_con_data.l_amount_applied(i),
2518 n_new_con_data.l_application_rule(i),
2519 n_new_con_data.l_application_type(i),
2520 n_new_con_data.l_apply_date(i),
2521 n_new_con_data.l_code_combination_id(i),
2522 n_new_con_data.l_created_by(i),
2523 n_new_con_data.l_creation_date(i),
2524 n_new_con_data.l_display(i),
2525 n_new_con_data.l_gl_date(i),
2526 n_new_con_data.l_last_updated_by(i),
2527 n_new_con_data.l_last_update_date(i),
2528 n_new_con_data.l_payment_schedule_id(i),
2529 n_new_con_data.l_set_of_books_id(i),
2530 n_new_con_data.l_status(i),
2531 n_new_con_data.l_acctd_amount_applied_to(i),
2532 n_new_con_data.l_acctd_earned_discount_tkn(i),
2533 n_new_con_data.l_acctd_unearned_discount_tkn(i),
2534 n_new_con_data.l_applied_customer_trx_id(i),
2535 n_new_con_data.l_applied_customer_trx_line_id(i),
2536 n_new_con_data.l_applied_payment_schedule_id(i),
2537 n_new_con_data.l_cash_receipt_id(i),
2538 n_new_con_data.l_comments(i),
2539 n_new_con_data.l_confirmed_flag(i),
2540 n_new_con_data.l_customer_trx_id(i),
2541 n_new_con_data.l_days_late(i),
2542 n_new_con_data.l_earned_discount_taken(i),
2543 n_new_con_data.l_freight_applied(i),
2544 n_new_con_data.l_gl_posted_date(i),
2545 n_new_con_data.l_last_update_login(i),
2546 n_new_con_data.l_line_applied(i),
2547 n_new_con_data.l_on_account_customer(i),
2548 n_new_con_data.l_postable(i),
2549 n_new_con_data.l_posting_control_id(i),
2550 n_new_con_data.l_cash_receipt_history_id(i),
2551 n_new_con_data.l_program_application_id(i),
2552 n_new_con_data.l_program_id(i),
2553 n_new_con_data.l_program_update_date(i),
2554 n_new_con_data.l_receivables_charges_applied(i),
2555 n_new_con_data.l_receivables_trx_id(i),
2556 n_new_con_data.l_request_id(i),
2557 n_new_con_data.l_tax_applied(i),
2558 n_new_con_data.l_unearned_discount_taken(i),
2559 n_new_con_data.l_unearned_discount_ccid(i),
2560 n_new_con_data.l_earned_discount_ccid(i),
2561 n_new_con_data.l_ussgl_transaction_code(i),
2562 n_new_con_data.l_attribute_category(i),
2563 n_new_con_data.l_attribute1(i),
2564 n_new_con_data.l_attribute2(i),
2565 n_new_con_data.l_attribute3(i),
2566 n_new_con_data.l_attribute4(i),
2567 n_new_con_data.l_attribute5(i),
2568 n_new_con_data.l_attribute6(i),
2569 n_new_con_data.l_attribute7(i),
2570 n_new_con_data.l_attribute8(i),
2571 n_new_con_data.l_attribute9(i),
2572 n_new_con_data.l_attribute10(i),
2573 n_new_con_data.l_attribute11(i),
2574 n_new_con_data.l_attribute12(i),
2575 n_new_con_data.l_attribute13(i),
2576 n_new_con_data.l_attribute14(i),
2577 n_new_con_data.l_attribute15(i),
2578 n_new_con_data.l_ussgl_transaction_code_cntxt(i),
2579 n_new_con_data.l_reversal_gl_date(i),
2580 n_new_con_data.l_org_id(i)
2581 );
2582
2583 -- Call mrc routine..
2584 ar_mrc_engine3.confirm_ra_rec_create(n_new_con_data);
2585
2586
2587 --In this case the accounting routine Create_Acct_Entry is not
2588 --called because new
2589 --records are unconfirmed
2590
2591 -- mark all old records as reversed
2592
2593 UPDATE ar_receivable_applications
2594 SET reversal_gl_date = p_confirm_gl_date,
2595 display = 'N',
2596 last_update_date = TRUNC(SYSDATE),
2597 last_updated_by = FND_GLOBAL.user_id
2598 WHERE cash_receipt_id = p_cr_rec.cash_receipt_id
2599 AND nvl(confirmed_flag,'Y') = 'Y'
2600 AND reversal_gl_date IS NULL;
2601
2602 arp_standard.debug('arp_confirmation.reverse_ra_recs()-');
2603
2604 EXCEPTION
2605 WHEN NO_DATA_FOUND THEN
2606 arp_util.debug('EXCEPTION: NO DATA FOUND, arp_confirmation.do_confirm()');
2607 RAISE;
2608
2609 WHEN OTHERS THEN
2610 arp_util.debug('EXCEPTION: arp_confirmation.do_confirm()');
2611 RAISE;
2612
2613 END; -- reverse_ra_recs()
2614
2615
2616
2617 /*===========================================================================+
2618 | PROCEDURE |
2619 | unconfirm_update_ps_rec |
2620 | |
2621 | DESCRIPTION |
2622 | This function updates the payment schedule record for a cash receipt |
2623 | after all applications have been processed. It basically sets the |
2624 | amount_due_remaining, the amount_due_original, and the |
2625 | receipt_confirmed_flag to 'N'. |
2626 | |
2627 | SCOPE - PRIVATE |
2628 | |
2629 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2630 | |
2631 | ARGUMENTS |
2632 | IN: |
2633 | p_cr_rec receipt record |
2634 | p_closed_date closed date |
2635 | p_closed_gl_date closed gl date |
2636 | |
2637 | OUT: |
2638 | |
2639 | RETURNS |
2640 | |
2641 | NOTES |
2642 | |
2643 | MODIFICATION HISTORY |
2644 | |
2645 | 01-SEP-95 OSTEINME created |
2646 | |
2647 +===========================================================================*/
2648
2649 PROCEDURE unconfirm_update_ps_rec(
2650 p_cr_rec ar_cash_receipts%ROWTYPE,
2651 p_closed_date DATE,
2652 p_closed_gl_date DATE
2653 ) IS
2654
2655 l_receipt_ps_rec ar_payment_schedules%ROWTYPE;
2656 l_dummy NUMBER;
2657
2658 BEGIN
2659
2660 arp_standard.debug('arp_confirmation.unconfirm_update_ps_rec()+');
2661 -- Fetch receipt's payment schedule record:
2662
2663 SELECT *
2664 INTO l_receipt_ps_rec
2665 FROM ar_payment_schedules
2666 WHERE cash_receipt_id = p_cr_rec.cash_receipt_id;
2667
2668
2669 -- set confirmed flag to 'N' to mark receipt as unconfirmed:
2670
2671 l_receipt_ps_rec.receipt_confirmed_flag := 'N';
2672
2673
2674 -- call utility handler routine to update payment schedule record:
2675
2676 arp_ps_util.update_receipt_related_columns(
2677 NULL, -- no payment_schedule_id!
2678 -p_cr_rec.amount,
2679 p_closed_date,
2680 p_closed_gl_date,
2681 l_dummy,
2682 l_receipt_ps_rec);
2683
2684 arp_standard.debug('arp_confirmation.unconfirm_update_ps_rec()-');
2685
2686 EXCEPTION
2687 WHEN NO_DATA_FOUND THEN
2688 arp_util.debug('EXCEPTION: NO DATA FOUND, arp_confirmation.unconfirm_update_ps_rec()');
2689 RAISE;
2690
2691 WHEN OTHERS THEN
2692 arp_util.debug('EXCEPTION: arp_confirmation.unconfirm_update_ps_rec()');
2693 RAISE;
2694
2695 END; -- unconfirm_update_ps_rec()
2696
2697
2698 /*===========================================================================+
2699 | PROCEDURE |
2700 | validate_in_parameters |
2701 | |
2702 | DESCRIPTION |
2703 | This function validates the correctness of the IN parameters for the |
2704 | confirm() and unconfirm() functions of the confirmation entity |
2705 | handler. |
2706 | |
2707 | SCOPE - PRIVATE |
2708 | |
2709 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2710 | |
2711 | ARGUMENTS |
2712 | IN: |
2713 | p_cr_id Cash receipt id |
2714 | p_confirm_gl_date Confirmation gl date |
2715 | p_confirm_date Confirmation date |
2716 | p_module_name Module name |
2717 | OUT: |
2718 | |
2719 | RETURNS: |
2720 | <none> |
2721 | |
2722 | NOTES |
2723 | |
2724 | MODIFICATION HISTORY |
2725 | |
2726 | 28-AUG-95 OSTEINME created |
2727 | |
2728 +===========================================================================*/
2729
2730 PROCEDURE validate_in_parameters(
2731 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
2732 p_confirm_gl_date IN DATE,
2733 p_confirm_date IN DATE,
2734 p_module_name IN VARCHAR2
2735 ) IS
2736
2737 BEGIN
2738
2739 arp_standard.debug('arp_confirmation.validate_in_parameters()+');
2740
2741 -- make sure none of the arguments is NULL:
2742
2743 IF (p_cr_id IS NULL) THEN
2744 arp_standard.debug('p_cr_id is NULL');
2745 FND_MESSAGE.set_name('AR','AR_ARGUEMENTS_FAIL');
2746 APP_EXCEPTION.raise_exception;
2747 END IF;
2748
2749 IF (p_confirm_gl_date IS NULL) THEN
2750 arp_standard.debug('p_confirm_gl_date is NULL');
2751 FND_MESSAGE.set_name('AR','AR_ARGUEMENTS_FAIL');
2752 APP_EXCEPTION.raise_exception;
2753 END IF;
2754
2755 IF (p_confirm_date IS NULL) THEN
2756 arp_standard.debug('p_confirm_date is NULL');
2757 FND_MESSAGE.set_name('AR','AR_ARGUEMENTS_FAIL');
2758 APP_EXCEPTION.raise_exception;
2759 END IF;
2760
2761 -- ???? validate dates any further ????
2762
2763 arp_standard.debug('arp_confirmation.validate_in_parameters()-');
2764
2765 EXCEPTION
2766 WHEN OTHERS THEN
2767 arp_standard.debug('EXCEPTION: arp_confirmation.validate_in_parameters');
2768 RAISE;
2769
2770 END; -- validate_in_parameters()
2771
2772
2773 /*===========================================================================+
2774 | PROCEDURE |
2775 | get_application_flags |
2776 | |
2777 | DESCRIPTION |
2778 | This procedure determines the application flags needed to validate |
2779 | an application. |
2780 | |
2781 | SCOPE - PRIVATE |
2782 | |
2783 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2784 | |
2785 | ARGUMENTS |
2786 | IN: |
2787 | p_cust_trx_type_id cust_trx_type_id from |
2788 | ar_payment_schedule of invoice |
2789 | OUT: |
2790 | p_ao_flag allow overapplication |
2791 | p_nao_flag natural application only |
2792 | p_creation_sign |
2793 | |
2794 | RETURNS |
2795 | |
2796 | NOTES |
2797 | |
2798 | MODIFICATION HISTORY |
2799 | |
2800 | 29-AUG-95 OSTEINME created |
2801 | |
2802 +===========================================================================*/
2803
2804 PROCEDURE get_application_flags(
2805 p_cust_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
2806 p_ao_flag OUT NOCOPY ra_cust_trx_types.allow_overapplication_flag%TYPE,
2807 p_nao_flag OUT NOCOPY ra_cust_trx_types.natural_application_only_flag%TYPE,
2808 p_creation_sign OUT NOCOPY ra_cust_trx_types.creation_sign%TYPE) IS
2809
2810 BEGIN
2811
2812 SELECT allow_overapplication_flag,
2813 natural_application_only_flag,
2814 creation_sign
2815 INTO p_ao_flag,
2816 p_nao_flag,
2817 p_creation_sign
2818 FROM ra_cust_trx_types
2819 WHERE cust_trx_type_id = p_cust_trx_type_id;
2820
2821 EXCEPTION
2822 WHEN NO_DATA_FOUND THEN
2823 arp_util.debug('EXCEPTION: NO DATA FOUND, arp_confirmation.get_application_flags()');
2824 RAISE;
2825
2826 WHEN OTHERS THEN
2827 arp_util.debug('EXCEPTION: arp_confirmation.get_application_flags()');
2828 RAISE;
2829
2830 END; -- get_application_flags()
2831
2832
2833 /*===========================================================================+
2834 | PROCEDURE |
2835 | handle_max_dates |
2836 | |
2837 | DESCRIPTION |
2838 | This function updates the MaxDatesType datastructure passed in. |
2839 | |
2840 | SCOPE - PRIVATE |
2841 | |
2842 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2843 | |
2844 | ARGUMENTS |
2845 | IN: |
2846 | p_max_dates MaxDatesType datastructure to be updated |
2847 | p_gl_date GL date |
2848 | p_apply_date Apply date |
2849 | p_confirm_date Confirm Date |
2850 | p_confirm_gl_date Confirm GL Date |
2851 | |
2852 | OUT: |
2853 | |
2854 | RETURNS |
2855 | |
2856 | NOTES |
2857 | |
2858 | MODIFICATION HISTORY |
2859 | |
2860 | 30-AUG-95 OSTEINME created |
2861 | |
2862 +===========================================================================*/
2863
2864 PROCEDURE handle_max_dates(
2865 p_max_dates IN OUT NOCOPY MaxDatesType,
2866 p_gl_date IN DATE,
2867 p_apply_date IN DATE,
2868 p_confirm_date IN DATE,
2869 p_confirm_gl_date IN DATE
2870 ) IS
2871
2872 BEGIN
2873
2874 p_max_dates.max_gl_date := GREATEST(p_max_dates.max_gl_date,
2875 p_gl_date);
2876 p_max_dates.max_ra_gl_date := GREATEST(p_confirm_gl_date,
2877 p_gl_date);
2878 p_max_dates.max_ra_apply_date := GREATEST(p_confirm_date,
2879 p_apply_date);
2880 p_max_dates.max_trx_date := GREATEST(p_max_dates.max_trx_date,
2881 p_apply_date);
2882 END; -- handle_max_dates()
2883
2884 END ARP_CONFIRMATION;