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