1 PACKAGE BODY ARP_PROC_RECEIPTS1 AS
2 /* $Header: ARRERG1B.pls 120.30.12020000.3 2012/07/24 09:26:20 kkikkise ship $ */
3
4 /* =======================================================================
5 | Global Data Types
6 * ======================================================================*/
7 SUBTYPE ae_doc_rec_type IS arp_acct_main.ae_doc_rec_type;
8 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
9
10 FUNCTION revision RETURN VARCHAR2 IS
11 BEGIN
12
13 RETURN '$Revision: 120.30.12020000.3 $';
14
15 END revision;
16
17
18 /*===========================================================================+
19 | PROCEDURE |
20 | update_cash_receipt |
21 | |
22 | DESCRIPTION |
23 | Entity handler that updates cash transactions. |
24 | |
25 | SCOPE - PRIVATE |
26 | |
27 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
28 | |
29 | ARGUMENTS |
30 | IN: |
31 | OUT: |
32 | |
33 | RETURNS |
34 | |
35 | NOTES |
36 | |
37 | MODIFICATION HISTORY |
38 | |
39 | 21-AUG-95 OSTEINME created |
40 | 15-MAR-96 OSTEINME removed calls to procedure |
41 | arp_cr_util.get_creation_info as they cause |
42 | problems if the receipt is created as |
43 | approved (in this case it doesn't have |
44 | entries in AR_DISTRIBUTIONS yet. |
45 | 05-APR-96 OSTEINME added functionality (and parameters) to |
46 | return receipt state and status info after |
47 | update. |
48 | 30-MAY-96 OSTEINME added parameter p_status for bug 370072 |
49 | 19-NOV-96 OSTEINME modified update_cash_receipts procedure to |
50 | support changing the amount even if apps |
51 | exist. |
52 | 27-DEC-96 OSTEINME added global flexfield parameters |
53 | 05-MAY-97 KLAWRANC Added section to determine a valid GL and |
54 | Reversal GL date for receivable applications.|
55 | This date is then used for update and |
56 | creation of application records. |
57 | 01-JUL-97 KLAWRANC Bug fixes #511576, 511312. |
58 | Use valid GL date when updating and creating |
59 | receipt records. |
60 | Corrected calculation of amount due remain |
61 | used to update receipt payment schedule row. |
62 | 29-AUG-97 KLAWRANC Bug fix #462056. |
63 | Added update of AP Bank Uses for MICR # |
64 | functionality. |
65 | 04-DEC-97 KLAWRANC Bug #590256. Modified calls to |
66 | calc_acctd_amount. Now passes NULL for the |
67 | currency code parameter, therefore the acctd |
68 | amount will be calculated based on the |
69 | functional currency.
70 | 20-FEB-97 KLAWRANC Bugs #616531, 625124, 625132. |
71 | Moved the update of receipt payment |
72 | schedule amounts to outside the |
73 | posted/not posted check. |
74 | Corrected the calculation of |
75 | l_crh_fda_delta and l_crh_acctd_fda_delta. |
76 | This did not use nvl in calculations which |
77 | caused an incorrect result if the bank |
78 | was initially null or changed to null. |
79 | Created a select statement to check for |
80 | the existance of a bank charges row in the |
81 | distributions table. This is used to |
82 | determine if a row needs to be created or |
83 | we can simply update the existing one. |
84 | If a bank charges row already exists and |
85 | bank charges is set to NULL, update the row |
86 | with zero amounts. |
87 | Included select statement to sum existing |
88 | bank charge DR and CR entries. This is used |
89 | to calculate the new DR and CR amounts |
90 | (taking into account previously posted rows).|
91 | 16-APR-1998 GJWANG Bug # 635872 Added condition checking whether|
92 | amount_due_remaining <> 0 and update cash |
93 | receipt status to UNAPP and payment schedule |
94 | status to UNAPP and payment schedule staus to|
95 | 'OP' and call populate_closed_dates to |
96 | determine correct gl_date_closed and |
97 | actual_date_closed |
98 | 21-MAY-1998 KTANG For all calls to calc_acctd_amount which |
99 | calculates header accounted amounts, if the |
100 | exchange_rate_type is not user, call |
101 | gl_currency_api.convert_amount instead. This |
102 | is for triangulation. |
103 | 29-JUL-1998 K.Murphy Bug #667450. Modified calculation of acctd |
104 | factor discount amount. |
105 | 28-MAY-1999 J Rautiainen Bug #894443. The status of unidentified |
106 | receipts cannot be changed to unapp. |
107 | 12-OCT-2001 R Muthuraman Bug #2024016. Rate adjustment fail when |
108 | rate type is changed from 'User' to another |
109 | rate type. |
110 | 25-03-2003 Ravi Sharma Bug 2855253.debit and credit mismatch when |
111 | the bank charges are updated. |
112 | 07-MAY-2003 Jon Beckett Bug 2946734 - moved claim update from |
113 | ARXRWRCT.pld |
114 | 30-JUL-2004 Jon Beckett Bug 3796142 - removed unnecessary calls to |
115 | gl_currency_api to convert receipt amount |
116 | to functional currency. Receipt rate used |
117 | instead of GL daily rate in all cases. |
118 | Currency conversion only performed if amount |
119 | has changed, otherwise acctd amount retrieved|
120 | from receipt history. |
121 | |
122 | 01-JUN-2006 Herve Yu BUG#4353362 Third Party Merge api uptake |
123 +===========================================================================*/
124
125
126 PROCEDURE update_cash_receipt(
127 p_cash_receipt_id IN NUMBER,
128 -- p_batch_id IN NUMBER,
129 p_status IN VARCHAR2,
130 p_currency_code IN VARCHAR2,
131 p_amount IN NUMBER,
132 p_pay_from_customer IN NUMBER,
133 p_receipt_number IN VARCHAR2,
134 p_receipt_date IN DATE,
135 p_gl_date IN DATE,
136 p_maturity_date IN DATE,
137 p_comments IN VARCHAR2,
138 p_exchange_rate_type IN VARCHAR2,
139 p_exchange_rate IN NUMBER,
140 p_exchange_date IN DATE,
141 p_attribute_category IN VARCHAR2,
142 p_attribute1 IN VARCHAR2,
143 p_attribute2 IN VARCHAR2,
144 p_attribute3 IN VARCHAR2,
145 p_attribute4 IN VARCHAR2,
146 p_attribute5 IN VARCHAR2,
147 p_attribute6 IN VARCHAR2,
148 p_attribute7 IN VARCHAR2,
149 p_attribute8 IN VARCHAR2,
150 p_attribute9 IN VARCHAR2,
151 p_attribute10 IN VARCHAR2,
152 p_attribute11 IN VARCHAR2,
153 p_attribute12 IN VARCHAR2,
154 p_attribute13 IN VARCHAR2,
155 p_attribute14 IN VARCHAR2,
156 p_attribute15 IN VARCHAR2,
157 p_override_remit_account_flag IN VARCHAR2,
158 p_remittance_bank_account_id IN NUMBER,
159 p_customer_bank_account_id IN NUMBER,
160 p_customer_site_use_id IN NUMBER,
161 p_customer_receipt_reference IN VARCHAR2,
162 p_factor_discount_amount IN NUMBER,
163 p_deposit_date IN DATE,
164 p_receipt_method_id IN NUMBER,
165 p_doc_sequence_value IN NUMBER,
166 p_doc_sequence_id IN NUMBER,
167 p_ussgl_transaction_code IN VARCHAR2,
168 p_vat_tax_id IN NUMBER,
169 --
170 p_confirm_date IN DATE,
171 p_confirm_gl_date IN DATE,
172 p_unconfirm_gl_date IN DATE,
173 p_postmark_date IN date, -- ARTA Changes
174 -- ******* Rate Adjustment parameters: ********
175 p_rate_adjust_gl_date IN DATE,
176 p_new_exchange_date IN DATE,
177 p_new_exchange_rate IN NUMBER,
178 p_new_exchange_rate_type IN VARCHAR2,
179 p_gain_loss IN NUMBER,
180 p_exchange_rate_attr_cat IN VARCHAR2,
181 p_exchange_rate_attr1 IN VARCHAR2,
182 p_exchange_rate_attr2 IN VARCHAR2,
183 p_exchange_rate_attr3 IN VARCHAR2,
184 p_exchange_rate_attr4 IN VARCHAR2,
185 p_exchange_rate_attr5 IN VARCHAR2,
186 p_exchange_rate_attr6 IN VARCHAR2,
187 p_exchange_rate_attr7 IN VARCHAR2,
188 p_exchange_rate_attr8 IN VARCHAR2,
189 p_exchange_rate_attr9 IN VARCHAR2,
190 p_exchange_rate_attr10 IN VARCHAR2,
191 p_exchange_rate_attr11 IN VARCHAR2,
192 p_exchange_rate_attr12 IN VARCHAR2,
193 p_exchange_rate_attr13 IN VARCHAR2,
194 p_exchange_rate_attr14 IN VARCHAR2,
195 p_exchange_rate_attr15 IN VARCHAR2,
196 --
197 -- ********* Reversal Info ***********
198 --
199 p_reversal_date IN DATE,
200 p_reversal_gl_date IN DATE,
201 p_reversal_category IN VARCHAR2,
202 p_reversal_comments IN VARCHAR2,
203 p_reversal_reason_code IN VARCHAR2,
204 p_dm_reversal_flag IN varchar2,
205 p_dm_cust_trx_type_id IN NUMBER,
206 p_dm_cust_trx_type IN VARCHAR2,
207 p_cc_id IN NUMBER,
208 p_dm_number OUT NOCOPY VARCHAR2,
209 p_dm_doc_sequence_value IN NUMBER,
210 p_dm_doc_sequence_id IN NUMBER,
211 p_tw_status IN OUT NOCOPY VARCHAR2,
212
213 p_anticipated_clearing_date IN DATE,
214 p_customer_bank_branch_id IN NUMBER,
215 --
216 -- ******* Global Flexfield parameters *******
217 --
218 p_global_attribute1 IN VARCHAR2,
219 p_global_attribute2 IN VARCHAR2,
220 p_global_attribute3 IN VARCHAR2,
221 p_global_attribute4 IN VARCHAR2,
222 p_global_attribute5 IN VARCHAR2,
223 p_global_attribute6 IN VARCHAR2,
224 p_global_attribute7 IN VARCHAR2,
225 p_global_attribute8 IN VARCHAR2,
226 p_global_attribute9 IN VARCHAR2,
227 p_global_attribute10 IN VARCHAR2,
228 p_global_attribute11 IN VARCHAR2,
229 p_global_attribute12 IN VARCHAR2,
230 p_global_attribute13 IN VARCHAR2,
231 p_global_attribute14 IN VARCHAR2,
232 p_global_attribute15 IN VARCHAR2,
233 p_global_attribute16 IN VARCHAR2,
234 p_global_attribute17 IN VARCHAR2,
235 p_global_attribute18 IN VARCHAR2,
236 p_global_attribute19 IN VARCHAR2,
237 p_global_attribute20 IN VARCHAR2,
238 p_global_attribute_category IN VARCHAR2,
239 --
240 -- ******* Notes Receivable Information *******
241 p_issuer_name IN VARCHAR2,
242 p_issue_date IN DATE,
243 p_issuer_bank_branch_id IN NUMBER,
244 --
245 -- ******* enhancement 2074220 *****************
246 p_application_notes IN VARCHAR2,
247 --
248 -- ******* Receipt State/Status Return information ******
249 --
250 p_new_state OUT NOCOPY VARCHAR2,
251 p_new_state_dsp OUT NOCOPY VARCHAR2,
252 p_new_status OUT NOCOPY VARCHAR2,
253 p_new_status_dsp OUT NOCOPY VARCHAR2,
254 --
255 --
256 -- ******* Form information ********
257 p_form_name IN VARCHAR2,
258 p_form_version IN VARCHAR2,
259 --
260 -- ******* Credit Card changes
261 p_payment_server_order_num IN VARCHAR2,
262 p_approval_code IN VARCHAR2,
263 p_legal_entity_id IN NUMBER default NULL,
264 p_payment_trxn_extension_id IN NUMBER default NULL, /* PAYMENT_UPTAKE */
265 p_automatch_set_id IN NUMBER DEFAULT NULL, /* ER Automatch Application */
266 p_autoapply_flag IN VARCHAR2 DEFAULT NULL
267
268 ) IS
269
270 l_cr_rec ar_cash_receipts%ROWTYPE;
271 l_crh_rec ar_cash_receipt_history%ROWTYPE;
272 l_crh_rec_new ar_cash_receipt_history%ROWTYPE;
273 l_ps_rec ar_payment_schedules%ROWTYPE;
274 l_dist_rec ar_distributions%ROWTYPE;
275 l_crh_id_new ar_cash_receipt_history.cash_receipt_history_id%TYPE;
276 l_ra_id_unapp ar_receivable_applications.receivable_application_id%TYPE;
277 l_ra_id_unid ar_receivable_applications.receivable_application_id%TYPE;
278 l_ae_doc_rec ae_doc_rec_type;
279
280 -- boolean flags:
281
282 l_cr_amount_changed_flag BOOLEAN := FALSE;
283 l_crh_fda_changed_flag BOOLEAN := FALSE;
284 l_crh_acctd_fda_changed_flag BOOLEAN := FALSE;
285 l_crh_rec_posted_flag BOOLEAN := FALSE;
286 l_crh_rec_gl_date_changed BOOLEAN := FALSE;
287 l_rct_identified_flag BOOLEAN := FALSE;
288 l_rct_unidentified_flag BOOLEAN := FALSE;
289
290 -- accounts:
291
292 l_crh_ccid NUMBER;
293 l_bank_charges_ccid NUMBER;
294 l_unidentified_ccid NUMBER;
295 l_unapplied_ccid NUMBER;
296 l_dummy_ccid NUMBER; -- dummy ccid parameter
297
298 -- amounts:
299
300 l_cr_acctd_amount_new NUMBER;
301 l_cr_acctd_amount_old NUMBER;
302 l_cr_amount_delta NUMBER;
303 l_cr_acctd_amount_delta NUMBER;
304 l_crh_amount_new NUMBER;
305 l_crh_amount_delta NUMBER;
306 l_crh_acctd_amount_new NUMBER;
307 l_crh_acctd_amount_delta NUMBER;
308 l_crh_acctd_fda_new NUMBER;
309 l_crh_acctd_fda_delta NUMBER;
310 l_crh_fda_delta NUMBER;
311 l_sum_fda_debits NUMBER;
312 l_sum_fda_credits NUMBER;
313 l_sum_acctd_fda_debits NUMBER;
314 l_sum_acctd_fda_credits NUMBER;
315
316 -- other stuff:
317
318 l_source_type ar_distributions.source_type%TYPE;
319 l_creation_status ar_cash_receipt_history.status%TYPE;
320 l_rev_crh_id ar_cash_receipt_history.reversal_cash_receipt_hist_id%TYPE;
321
322 -- dummy variables:
323
324 l_override_dummy ar_cash_receipts.override_remit_account_flag%TYPE;
325 l_number_dummy NUMBER;
326
327 -- GL date defaulting variables
328 l_error_message VARCHAR2(128);
329 l_defaulting_rule_used VARCHAR2(50);
330 l_valid_gl_date DATE;
331 error_defaulting_gl_date EXCEPTION;
332
333 l_bank_charges_row_exists VARCHAR2(1);
334 l_bcharge_row_on_current_crh VARCHAR2(1); /* Bug fix 3677912 */
335 l_dist_row_on_current_crh VARCHAR2(1); /* Bug fix 3677912 */
336
337
338 -- old stuff:
339 /*
340 l_source_type_old ar_distributions.source_type%TYPE;
341 l_creation_status_old ar_cash_receipt_history.status%TYPE;
342 l_acctd_amount_old ar_cash_receipt_history.acctd_amount%TYPE;
343 l_ccid_old ar_cash_receipt_history.account_code_combination_id%TYPE;
344 l_source_type_new ar_distributions.source_type%TYPE;
345 l_creation_status_new ar_cash_receipt_history.status%TYPE;
346 l_acctd_amount_new ar_cash_receipt_history.acctd_amount%TYPE;
347 l_ccid_new ar_cash_receipt_history.account_code_combination_id%TYPE;
348 l_ps_id ar_payment_schedules.payment_schedule_id%TYPE;
349 l_id_dummy NUMBER;
350 l_amount_changed BOOLEAN := FALSE;
351 l_rev_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
352 l_ra_unapp_ccid NUMBER;
353 l_ra_unid_ccid NUMBER;
354 l_ra_unapp_ccid_old NUMBER;
355 l_ra_unid_ccid_old NUMBER;
356
357 */
358
359 /* Bug8422361 - Variable Declaration - Start. */
360 l_no_of_accounts NUMBER;
361 l_no_of_other_accounts NUMBER;
362 l_no_of_other_party_accounts NUMBER;
363 l_bank_assign_flag BOOLEAN := FALSE;
364 l_party_id NUMBER;
365 l_api_version NUMBER := 1.0;
366 l_init_msg_list VARCHAR2(30) DEFAULT FND_API.G_TRUE;
367 l_joint_acct_owner_id NUMBER;
368 l_iby_return_status VARCHAR2(30);
369 l_iby_msg_count NUMBER;
370 l_iby_msg_data VARCHAR2(2000);
371 l_response_rec IBY_FNDCPT_COMMON_PUB.Result_rec_type;
372 l_instr_assign_id NUMBER;
373 /* Bug8422361 - Variable Declaration - End. */
374
375
376 /*added for the bug 2641517 */
377 l_rct_site_changed_flag BOOLEAN := FALSE;
378 l_rct_customer_changed_flag BOOLEAN := FALSE;
379 l_trx_sum_hist_rec AR_TRX_SUMMARY_HIST%rowtype;
380 l_history_id NUMBER;
381 l_return_status VARCHAR2(1);
382 l_msg_count NUMBER;
383 l_msg_data VARCHAR2(2000);
384
385 --{BUG#4353362
386 CURSOR cu_current_customer IS
387 SELECT pay_from_customer,
388 customer_site_use_id
389 FROM ar_cash_receipts
390 WHERE cash_receipt_id = p_cash_receipt_id;
391 l_current_customer_id NUMBER;
392 l_current_csu_id NUMBER;
393 x_errbuf VARCHAR2(2000);
394 x_retcode VARCHAR2(10);
395 x_event_ids xla_third_party_merge_pub.t_event_ids;
396 x_request_id NUMBER;
397 --}
398 BEGIN
399
400 IF PG_DEBUG in ('Y', 'C') THEN
401 arp_standard.debug('arp_process_receipts.update_cash_receipt()+');
402 END IF;
403
404 -- --------------------------------------------------------------
405 -- First fetch and lock existing records from database for update
406 -- --------------------------------------------------------------
407
408 -- get current cash_receipt_history record:
409
410 l_crh_rec.cash_receipt_id := p_cash_receipt_id;
411 arp_cr_history_pkg.nowaitlock_fetch_f_cr_id(l_crh_rec);
412
413 -- get cash receipt record:
414
415 l_cr_rec.cash_receipt_id := p_cash_receipt_id;
416 arp_cash_receipts_pkg.nowaitlock_fetch_p(l_cr_rec);
417
418 -- get payment schedule record for receipt:
419
420 arp_proc_rct_util.get_ps_rec(l_cr_rec.cash_receipt_id,
421 l_ps_rec);
422
423 --apandit
424 --Bug 2641517, populating the history rec.
425 l_trx_sum_hist_rec.cash_receipt_id := l_ps_rec.cash_receipt_id;
426 l_trx_sum_hist_rec.site_use_id := l_ps_rec.customer_site_use_id;
427 l_trx_sum_hist_rec.customer_id := l_ps_rec.customer_id;
428 l_trx_sum_hist_rec.currency_code := l_ps_rec.invoice_currency_code;
429 l_trx_sum_hist_rec.amount_due_original := l_ps_rec.amount_due_original;
430 l_trx_sum_hist_rec.amount_due_remaining := l_ps_rec.amount_due_remaining;
431 l_trx_sum_hist_rec.payment_schedule_id := l_ps_rec.payment_schedule_id;
432 l_trx_sum_hist_rec.trx_date := l_ps_rec.trx_date;
433
434 IF nvl(p_customer_site_use_id,0) <> nvl(l_ps_rec.customer_site_use_id,0)
435 THEN
436 l_rct_site_changed_flag := TRUE;
437 END IF;
438
439 IF p_pay_from_customer IS NOT NULL AND
440 l_ps_rec.customer_id IS NOT NULL AND
441 p_pay_from_customer <> l_ps_rec.customer_id THEN
442 l_rct_customer_changed_flag := TRUE;
443 END IF;
444
445 -- KML 05-13-97
446 -----------------------------------------------------
447 -- Determine a valid GL date for receipt and apps
448 -- use the receipt gl_date as a base
449 -- need to make sure that it is in a valid GL period
450 -----------------------------------------------------
451 IF (arp_util.validate_and_default_gl_date(
452 l_crh_rec.gl_date,
453 NULL,
454 NULL,
455 NULL,
456 NULL,
457 l_crh_rec.gl_date,
458 NULL,
459 NULL,
460 'N',
461 NULL,
462 arp_global.set_of_books_id,
463 222,
464 l_valid_gl_date,
465 l_defaulting_rule_used,
466 l_error_message) = TRUE) THEN
467 null;
468 ELSE
469 RAISE error_defaulting_gl_date;
470 END IF;
471
472 -- -------------------------------------------------------------
473 -- Now compare parameters with database values to find out NOCOPY what
474 -- has changed and what needs to be done. Depending on whether
475 -- the amounts have changed and whether the receipt was already
476 -- posted, more or less complicated things need to be done.
477 -- -------------------------------------------------------------
478
479 -- check if cr.amount has changed
480
481 IF (p_amount <> l_cr_rec.amount)
482 THEN
483 l_cr_amount_changed_flag := TRUE;
484 IF PG_DEBUG in ('Y', 'C') THEN
485 arp_standard.debug('update_cash_receipt: ' || 'l_cr_amount_changed_flag = TRUE');
486 END IF;
487 END IF;
488
489 -- check if the gl date of the receipt is still valid
490
491 IF PG_DEBUG in ('Y', 'C') THEN
492 arp_standard.debug('update_cash_receipt: ' || 'P_GL_DATE is:' || to_char(p_gl_date, 'DD-MM-YYYY:HH:SS'));
493 arp_standard.debug('update_cash_receipt: ' || 'l_valid_gl_date is:' || to_char(l_valid_gl_date, 'DD-MM-YYYY:HH:SS'));
494 END IF;
495
496 IF (p_gl_date <> l_valid_gl_date)
497 THEN
498 l_crh_rec_gl_date_changed := TRUE;
499 IF PG_DEBUG in ('Y', 'C') THEN
500 arp_standard.debug('update_cash_receipt: ' || 'l_crh_rec_gl_date_changed = TRUE');
501 END IF;
502 END IF;
503
504
505 -- check if crh.factor_discount_amount has changed
506
507 IF (NVL(p_factor_discount_amount,0) <>
508 NVL(l_crh_rec.factor_discount_amount,0))
509 THEN
510 l_crh_fda_changed_flag := TRUE;
511 IF PG_DEBUG in ('Y', 'C') THEN
512 arp_standard.debug('update_cash_receipt: ' || 'l_crh_fda_changed_flag = TRUE');
513 END IF;
514
515 END IF;
516
517 -- check if crh record was posted to GL
518
519 IF (l_crh_rec.posting_control_id <> -3)
520 THEN
521 l_crh_rec_posted_flag := TRUE;
522 IF PG_DEBUG in ('Y', 'C') THEN
523 arp_standard.debug('update_cash_receipt: ' || 'l_crh_rec_posted_flag = TRUE');
524 END IF;
525 END IF;
526
527 -- check if receipt was identified (UNID -> UNAPP)
528
529 IF (l_cr_rec.pay_from_customer IS NULL AND
530 p_pay_from_customer IS NOT NULL)
531 THEN
532 l_rct_identified_flag := TRUE;
533 IF PG_DEBUG in ('Y', 'C') THEN
534 arp_standard.debug('update_cash_receipt: ' || 'l_rct_identified_flag = TRUE');
535 END IF;
536 END IF;
537
538 -- check if receipt was "un-identified" (UNAPP -> UNID)
539
540 IF (l_cr_rec.pay_from_customer IS NOT NULL AND
541 p_pay_from_customer IS NULL)
542 THEN
543 l_rct_unidentified_flag := TRUE;
544 IF PG_DEBUG in ('Y', 'C') THEN
545 arp_standard.debug('update_cash_receipt: ' || 'l_rct_unidentified_flag = TRUE');
546 END IF;
547 END IF;
548
549 -- -------------------------------------------------------------
550 -- determine account code combination ids
551 -- -------------------------------------------------------------
552
553 arp_proc_rct_util.get_ccids(
554 p_receipt_method_id,
555 p_remittance_bank_account_id,
556 l_unidentified_ccid,
557 l_unapplied_ccid,
558 l_dummy_ccid, -- on account
559 l_dummy_ccid, -- earned ccid
560 l_dummy_ccid, -- unearned ccid
561 l_bank_charges_ccid,
562 l_dummy_ccid, -- factor ccid
563 l_dummy_ccid, -- confirmation_ccid
564 l_dummy_ccid, -- remittance ccid
565 l_dummy_ccid -- cash ccid
566 );
567
568 -- -------------------------------------------------------------
569 -- determine the source type and creation status
570 -- -------------------------------------------------------------
571
572 -- the source type is needed for AR_DISTRIBUTIONS records
573
574 arp_cr_util.get_creation_info(p_receipt_method_id,
575 p_remittance_bank_account_id,
576 l_creation_status,
577 l_source_type,
578 l_crh_ccid,
579 l_override_dummy);
580
581 -- -------------------------------------------------------------
582 -- determine the amounts
583 -- -------------------------------------------------------------
584
585 -- convert new cr.amount into functional currency:
586
587 /* Bug 3796142 - receipt exchange rate should be used for updates, but
588 only if amount has actually changed */
589 l_cr_acctd_amount_old := (NVL(l_crh_rec.acctd_amount,0) + NVL(l_crh_rec.acctd_factor_discount_amount,0));
590 IF (l_cr_amount_changed_flag) THEN
591 arp_util.calc_acctd_amount( NULL,
592 NULL,
593 NULL,
594 l_cr_rec.exchange_rate,
595 '+',
596 p_amount,
597 l_cr_acctd_amount_new,
598 0,
599 l_number_dummy,
600 l_number_dummy,
601 l_number_dummy);
602 ELSE
603 l_cr_acctd_amount_new := l_cr_acctd_amount_old;
604 END IF;
605
606 -- determine the new cash_receipt_history amount:
607
608 l_crh_amount_new := p_amount - NVL(p_factor_discount_amount,0);
609
610 /* Bug 3796142 - receipt exchange rate should be used for updates, but
611 only if amount has actually changed */
612
613 IF (l_cr_amount_changed_flag OR l_crh_fda_changed_flag) THEN
614 arp_util.calc_acctd_amount( NULL,
615 NULL,
616 NULL,
617 l_cr_rec.exchange_rate,
618 '+',
619 l_crh_amount_new,
620 l_crh_acctd_amount_new,
621 0,
622 l_number_dummy,
623 l_number_dummy,
624 l_number_dummy);
625 ELSE
626 l_crh_acctd_amount_new := l_crh_rec.acctd_amount;
627 END IF;
628
629 -- acctd_factor_discount_amount = triangulated(amount +
630 -- factor_discount_amount) -
631 -- triangulated(amount)
632
633 l_crh_acctd_fda_new := l_cr_acctd_amount_new -
634 l_crh_acctd_amount_new;
635
636 -- This will help us to identify the situation where the fda has not
637 -- changed but the acctd fda has as a result of a receipt amount change.
638
639 IF (NVL(l_crh_rec.acctd_factor_discount_amount,0) <>
640 NVL(l_crh_acctd_fda_new,0)) THEN
641 l_crh_acctd_fda_changed_flag := TRUE;
642 END IF;
643
644 -- determine deltas:
645
646 l_cr_amount_delta := p_amount -
647 l_cr_rec.amount;
648
649 l_cr_acctd_amount_delta := l_cr_acctd_amount_new -
650 l_cr_acctd_amount_old;
651
652 l_crh_amount_delta := l_crh_amount_new -
653 l_crh_rec.amount;
654
655 l_crh_acctd_amount_delta := l_crh_acctd_amount_new -
656 l_crh_rec.acctd_amount;
657
658 l_crh_fda_delta := p_factor_discount_amount -
659 l_crh_rec.factor_discount_amount;
660
661 l_crh_acctd_fda_delta := l_crh_acctd_fda_new -
662 l_crh_rec.acctd_factor_discount_amount;
663
664 --
665 -- Factor Discount Amount
666 -- Need to use NVL as the Bank Charges may be null or modified to
667 -- be null.
668 --
669 l_crh_fda_delta := nvl(p_factor_discount_amount,0) -
670 nvl(l_crh_rec.factor_discount_amount,0);
671
672 l_crh_acctd_fda_delta := nvl(l_crh_acctd_fda_new,0) -
673 nvl(l_crh_rec.acctd_factor_discount_amount,0);
674
675 IF PG_DEBUG in ('Y', 'C') THEN
676 arp_standard.debug('update_cash_receipt: ' || 'p_amount = '|| to_char(p_amount));
677 arp_standard.debug('update_cash_receipt: ' || 'l_crh_amount_new = '|| to_char(l_crh_amount_new));
678 arp_standard.debug('update_cash_receipt: ' || 'l_crh_acctd_amount_new = '|| to_char(l_crh_acctd_amount_new));
679 END IF;
680
681 -- -------------------------------------------------------------
682 -- handle changes in rct identification status
683 -- -------------------------------------------------------------
684
685 -- customer information: can be updated as long as receipt has no
686 -- applications. Form checks this; no check
687 -- required here.
688
689 -- check if receipt status changed from UNID to UNAPP (i.e, receipt is
690 -- now identified. In this case, the UNID record
691 -- needs to be reversed, and a new UNAPP record needs to be created.
692
693 IF (l_rct_identified_flag = TRUE) THEN
694
695 -- first reverse existing UNID record by setting reversal GL Date:
696
697 UPDATE ar_receivable_applications
698 SET reversal_gl_date = l_valid_gl_date
699 WHERE cash_receipt_id = p_cash_receipt_id
700 AND reversal_gl_date IS NULL
701 AND status = 'UNID';
702
703 -- now create matching UNID record with negative amount
704
705 arp_proc_rct_util.insert_ra_rec_cash(
706 p_cash_receipt_id,
707 -l_cr_rec.amount,
708 p_receipt_date,
709 'UNID',
710 -l_cr_acctd_amount_old,
711 l_valid_gl_date,
712 l_unidentified_ccid,
713 l_ps_rec.payment_schedule_id,
714 '60.5',
715 l_valid_gl_date,
716 l_ra_id_unid );
717
718 --
719 --Release 11.5 VAT changes, create UNID receivable application accounting
720 --in ar_distributions
721 --
722 l_ae_doc_rec.document_type := 'RECEIPT';
723 l_ae_doc_rec.document_id := p_cash_receipt_id;
724 l_ae_doc_rec.accounting_entity_level := 'ONE';
725 l_ae_doc_rec.source_table := 'RA';
726 l_ae_doc_rec.source_id := l_ra_id_unid;
727 l_ae_doc_rec.source_id_old := '';
728 l_ae_doc_rec.other_flag := '';
729 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
730
731 -- now create new UNAPP record for this receipt:
732
733 arp_proc_rct_util.insert_ra_rec_cash(
734 p_cash_receipt_id,
735 l_cr_rec.amount,
736 p_receipt_date,
737 'UNAPP',
738 l_cr_acctd_amount_old,
739 l_valid_gl_date,
740 l_unapplied_ccid,
741 l_ps_rec.payment_schedule_id,
742 '60.2',
743 '',
744 l_ra_id_unapp);
745
746 -- 6924942 - Start
747 update ar_receivable_applications
748 set include_in_accumulation ='N'
749 where cash_receipt_id = p_cash_receipt_id
750 and status = 'UNAPP';
751 -- 6924942 - End
752
753 --
754 --Release 11.5 VAT changes, create Paired UNAPP receivable application accounting
755 --in ar_distributions
756 --
757 l_ae_doc_rec.document_type := 'RECEIPT';
758 l_ae_doc_rec.document_id := p_cash_receipt_id;
759 l_ae_doc_rec.accounting_entity_level := 'ONE';
760 l_ae_doc_rec.source_table := 'RA';
761 l_ae_doc_rec.source_id := l_ra_id_unapp;
762 l_ae_doc_rec.source_id_old := l_ra_id_unid;
763 l_ae_doc_rec.other_flag := 'PAIR';
764
765 /* We need to set the third party id and sub id as the cash receipt
766 is updated later */
767 l_ae_doc_rec.miscel5 := p_pay_from_customer;
768 l_ae_doc_rec.miscel6 := p_customer_site_use_id;
769 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
770
771 -- also don't forget to set the pay_from_customer column in
772 -- ar_cash_receipts to new customer and the status of the receipt:
773
774 l_cr_rec.pay_from_customer := p_pay_from_customer;
775 l_cr_rec.status := 'UNAPP';
776
777 ELSIF (l_rct_unidentified_flag = TRUE) THEN
778
779 -- now take care of the case where the user NULL'ed out NOCOPY the customer
780 -- fields:
781
782 -- In this case, first reverse the UNAPP record and then create an
783 -- UNID record.
784
785 UPDATE ar_receivable_applications
786 SET reversal_gl_date = l_valid_gl_date
787 WHERE cash_receipt_id = p_cash_receipt_id
788 AND reversal_gl_date IS NULL
789 AND status = 'UNAPP';
790
791 -- now create matching UNAPP record with negative amount
792
793 arp_proc_rct_util.insert_ra_rec_cash(
794 p_cash_receipt_id,
795 -l_cr_rec.amount,
796 p_receipt_date,
797 'UNAPP',
798 -l_cr_acctd_amount_old,
799 l_valid_gl_date,
800 l_unapplied_ccid,
801 l_ps_rec.payment_schedule_id,
802 '60.3',
803 l_valid_gl_date,
804 l_ra_id_unapp);
805
806 -- now create new UNID record for this receipt:
807
808 arp_proc_rct_util.insert_ra_rec_cash(
809 p_cash_receipt_id,
810 l_cr_rec.amount,
811 p_receipt_date,
812 'UNID',
813 l_cr_acctd_amount_old,
814 l_valid_gl_date,
815 l_unidentified_ccid,
816 l_ps_rec.payment_schedule_id,
817 '60.4',
818 '',
819 l_ra_id_unid);
820
821 -- 6924942 - Start
822 update ar_receivable_applications
823 set include_in_accumulation ='N'
824 where cash_receipt_id = p_cash_receipt_id
825 and status = 'UNAPP';
826 -- 6924942 - End
827
828 --
829 --Release 11.5 VAT changes, create UNID receivable application accounting
830 --in ar_distributions
831 --
832 l_ae_doc_rec.document_type := 'RECEIPT';
833 l_ae_doc_rec.document_id := p_cash_receipt_id;
834 l_ae_doc_rec.accounting_entity_level := 'ONE';
835 l_ae_doc_rec.source_table := 'RA';
836 l_ae_doc_rec.source_id := l_ra_id_unid;
837 l_ae_doc_rec.source_id_old := '';
838 l_ae_doc_rec.other_flag := '';
839 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
840
841 --
842 --Release 11.5 VAT changes, create paired UNAPP receivable application accounting
843 --in ar_distributions
844 --
845 l_ae_doc_rec.document_type := 'RECEIPT';
846 l_ae_doc_rec.document_id := p_cash_receipt_id;
847 l_ae_doc_rec.accounting_entity_level := 'ONE';
848 l_ae_doc_rec.source_table := 'RA';
849 l_ae_doc_rec.source_id := l_ra_id_unapp;
850 l_ae_doc_rec.source_id_old := l_ra_id_unid;
851 l_ae_doc_rec.other_flag := 'PAIR';
852
853 /* In this case as the receipt is unidentified, the third party id
854 and sub id is from the cash receipt, so no need to pass these */
855 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
856
857 -- also don't forget to set the pay_from_customer column in
858 -- ar_cash_receipts to NULL and the status to UNID.
859
860 l_cr_rec.pay_from_customer := NULL;
861 l_cr_rec.status := 'UNID';
862
863 --
864 ELSIF (l_cr_rec.pay_from_customer IS NOT NULL AND
865 p_pay_from_customer IS NOT NULL) THEN
866 --
867 -- in this case the user has changed the customer; applications
868 -- do not exist (otherwise the form would not have allowed the
869 -- update). So just update the pay_from_customer column.
870
871 l_cr_rec.pay_from_customer := p_pay_from_customer;
872 --
873 END IF;
874
875 -- -------------------------------------------------------------
876 -- deal with amount changes
877 -- -------------------------------------------------------------
878
879 -- if the receipt status is APPROVED, we only update the amounts in
880 -- ar_cash_receipts, ar_cash_receipt_history, and ar_payment_schedules,
881 -- but not ar_receivable_applications, since this is being taken
882 -- care of by the applications form and its server-side code:
883
884
885 IF (l_crh_rec.status = 'APPROVED') THEN
886
887 l_cr_rec.amount := p_amount;
888 l_crh_rec.amount := p_amount;
889 l_crh_rec.acctd_amount := l_cr_acctd_amount_new;
890 l_ps_rec.amount_due_original := - p_amount;
891 l_ps_rec.amount_due_remaining := - p_amount;
892 l_ps_rec.acctd_amount_due_remaining := - l_cr_acctd_amount_new;
893
894 /* Bug fix 2964295
895 The cash_receipt_history record needs to be updated for APPROVED receipts */
896 arp_cr_history_pkg.update_p(l_crh_rec);
897
898 ELSE -- (l_crh_rec.status <> 'APPROVED')
899
900 IF (l_cr_amount_changed_flag = TRUE or l_crh_fda_changed_flag = TRUE) THEN
901
902 -- Update of the receipt payment schedule row and cr.amount is the same
903 -- regardless of the posting status of the receipt so we will do the
904 -- calculations now.
905
906 -- ps.amount_due_remaining is defined as:
907
908 -- ps.adr = - (unapplied amount + unid amount + on_account amount)
909 -- of course, the unapplied amount should be null if there is an
910 -- unidentified amount and vice versa.
911 -- Thus an amount update means that we take the previous ps.adr
912 -- and subtract (since adr is negative) the difference between
913 -- the new receipt amount and the old receipt amount.
914
915 IF PG_DEBUG in ('Y', 'C') THEN
916 arp_standard.debug('update_cash_receipt: ' || 'l_ps_rec.amount_due_remaining: ' || to_char(l_ps_rec.amount_due_remaining));
917 arp_standard.debug('update_cash_receipt: ' || 'p_amount: ' || to_char(p_amount));
918 arp_standard.debug('update_cash_receipt: ' || 'l_cr_rec.amount: ' || to_char(l_cr_rec.amount));
919 arp_standard.debug('update_cash_receipt: ' || 'l_cr_acctd_amount_delta: ' || to_char(l_cr_acctd_amount_delta));
920 END IF;
921
922 l_ps_rec.amount_due_remaining :=
923 l_ps_rec.amount_due_remaining -
924 (p_amount - l_cr_rec.amount) ;
925
926 l_ps_rec.acctd_amount_due_remaining :=
927 l_ps_rec.acctd_amount_due_remaining -
928 l_cr_acctd_amount_delta;
929
930 IF PG_DEBUG in ('Y', 'C') THEN
931 arp_standard.debug('update_cash_receipt: ' || 'l_ps_rec.amount_due_remaining: ' || to_char(l_ps_rec.amount_due_remaining));
932 arp_standard.debug('update_cash_receipt: ' || 'l_ps_rec.acctd_amount_due_remaining: ' || to_char(l_ps_rec.acctd_amount_due_remaining));
933 END IF;
934
935 l_ps_rec.amount_due_original := -p_amount;
936
937 l_cr_rec.amount := p_amount;
938
939 /* Bug 4294346 : In populating closed dates ,We should also consider the new RA
940 records which will be created to account for Difference in Receipt Amount */
941 /* arp_ps_util.populate_closed_dates( NULL, NULL, 'PMT', l_ps_rec ); */
942 IF ( NVL(l_ps_rec.amount_due_remaining,0)= 0) THEN
943 l_cr_rec.status := 'APP';
944 l_ps_rec.status := 'CL';
945 /* 28-MAY-1999 J Rautiainen
946 * The status of unidentified receipts cannot be changed to unapp.
947 * Bugfix for 894443 Start */
948 ELSIF (l_cr_rec.pay_from_customer IS NULL AND
949 p_pay_from_customer IS NULL) THEN
950 l_cr_rec.status := 'UNID';
951 l_ps_rec.status := 'OP';
952 /* Bugfix for 894443 end */
953 ELSE
954 l_cr_rec.status := 'UNAPP';
955 l_ps_rec.status := 'OP';
956 END IF;
957
958
959 IF (l_crh_rec_posted_flag = FALSE and l_crh_rec_gl_date_changed = FALSE ) THEN
960
961 -- amount changes are fairly straight-forward if the current
962 -- history record has not been posted yet and gl period is stil
963 -- valid. We just update the cash receipt history record and
964 -- distribution records.
965
966 IF (l_crh_amount_new <> l_crh_rec.amount) THEN
967 /* modified the parameter values passed for bug 2311742 */
968
969 /* Bug fix 3677912 */
970 /* The distribution record can be updated only if the current CRH record has
971 one record in ARD corresponding to the l_source_type. Else we need to create one */
972 BEGIN
973 select 'Y'
974 into l_dist_row_on_current_crh
975 from ar_distributions dis
976 where dis.source_id = l_crh_rec.cash_receipt_history_id
977 and dis.source_table = 'CRH'
978 and dis.source_type = l_source_type;
979 EXCEPTION
980 WHEN no_data_found THEN
981 l_dist_row_on_current_crh := 'N';
982 END;
983 IF PG_DEBUG in ('Y', 'C') THEN
984 arp_standard.debug('update_cash_receipt: '||'l_dist_row_on_current_crh : '||l_dist_row_on_current_crh);
985 END IF;
986 IF l_dist_row_on_current_crh = 'Y' THEN
987 arp_proc_rct_util.update_dist_rec(
988 l_crh_rec.cash_receipt_history_id,
989 l_source_type,
990 l_crh_amount_new - l_crh_rec.amount,
991 l_crh_acctd_amount_new - l_crh_rec.acctd_amount);
992 ELSE
993 IF PG_DEBUG in ('Y', 'C') THEN
994 arp_standard.debug('update_cash_receipt: ' || 'l_crh_amount_delta : ' || to_char(l_crh_amount_delta));
995 arp_standard.debug('update_cash_receipt: ' || 'l_crh_acctd_amount_delta : ' || to_char(l_crh_acctd_amount_delta));
996 arp_standard.debug('update_cash_receipt: ' || 'cash_receipt_history_id : ' || to_char(l_crh_rec.cash_receipt_history_id));
997 arp_standard.debug('update_cash_receipt: ' || 'l_source_type : ' || l_source_type);
998 arp_standard.debug('update_cash_receipt: ' || 'l_crh_ccid : ' || l_crh_ccid);
999 END IF;
1000
1001 arp_proc_rct_util.insert_dist_rec(l_crh_amount_delta,
1002 l_crh_acctd_amount_delta,
1003 l_crh_rec.cash_receipt_history_id,
1004 l_source_type,
1005 l_crh_ccid);
1006 END IF;
1007
1008 END IF;
1009
1010 -- Need to check the acctd fda flag. The fda may not have
1011 -- changed but the acctd fda may have been altered implicitly
1012 -- as a result of a receipt amount change.
1013
1014 IF (l_crh_fda_changed_flag = TRUE OR
1015 l_crh_acctd_fda_changed_flag = TRUE) THEN
1016
1017 --
1018 -- Check to see if a Bank Charges distribution row
1019 -- already exists.
1020 --
1021
1022 BEGIN
1023 /* Bug fix 3677912
1024 Bank charges record can be present for any CRH record */
1025 select 'Y'
1026 into l_bank_charges_row_exists
1027 from dual
1028 where exists (select crh.cash_receipt_history_id
1029 from ar_cash_receipt_history crh, ar_distributions dis
1030 where crh.cash_receipt_id = l_crh_rec.cash_receipt_id
1031 and dis.source_id = crh.cash_receipt_history_id
1032 and dis.source_table = 'CRH'
1033 and dis.source_type ='BANK_CHARGES');
1034 EXCEPTION
1035 WHEN no_data_found THEN
1036 l_bank_charges_row_exists := 'N';
1037 END;
1038
1039 /* Bug fix 3677912
1040 Check if the Bank Charge distribution exists for the current CRH record
1041 If not, we have to create it */
1042
1043 BEGIN
1044 select 'Y'
1045 into l_bcharge_row_on_current_crh
1046 from ar_distributions dis
1047 where dis.source_id = l_crh_rec.cash_receipt_history_id
1048 and dis.source_table = 'CRH'
1049 and dis.source_type = 'BANK_CHARGES';
1050 EXCEPTION
1051 WHEN no_data_found THEN
1052 l_bcharge_row_on_current_crh := 'N';
1053 END;
1054
1055 IF l_bank_charges_row_exists = 'Y' THEN
1056
1057 --
1058 -- If bank charge record existed before, then we can go ahead and
1059 -- update this record.
1060 --
1061 -- Firstly, we need to calculate what the updated amount should be.
1062 -- To do this we need to check if there are any existing (posted)
1063 -- distribution amounts for BANK_CHARGES.
1064 --
1065 -- Consider the following example:
1066 -- Cash Receipt: 'A'
1067 -- Posted Amount FDA Dist Source Amt DR Amt CR
1068 -- History 1: Y 10000 2000 BANK_CAHRGES 2000
1069 -- History 2: N 10000 1500 BANK_CHARGES 500
1070 --
1071 -- If the Bank Charges are modified from 1500 to 2500, the result should
1072 -- be a DR entry of 500 (Total DR of 1500) which will replace the CR entry
1073 -- of 500.
1074 --
1075 -- Resulting in:
1076 -- Cash Receipt: 'A'
1077 -- Posted Amount FDA Dist Source Amt DR Amt CR
1078 -- History 1: Y 10000 2000 BANK_CAHRGES 2000
1079 -- History 2: N 10000 2500 BANK_CHARGES 500
1080 --
1081 -- It is not sufficient to use the Factor Discount Amount for the current
1082 -- Cash Receipt History row as this doesn't take into account any prior/posted
1083 -- FDA amounts.
1084 --
1085
1086 select nvl(sum(dis.amount_dr),0),
1087 nvl(sum(dis.amount_cr),0),
1088 nvl(sum(dis.acctd_amount_dr),0),
1089 nvl(sum(dis.acctd_amount_cr),0)
1090 into l_sum_fda_debits,
1091 l_sum_fda_credits,
1092 l_sum_acctd_fda_debits,
1093 l_sum_acctd_fda_credits
1094 from ar_distributions dis
1095 where dis.source_id in
1096 (select crh.cash_receipt_history_id
1097 from ar_cash_receipt_history crh
1098 where crh.cash_receipt_id = p_cash_receipt_id ) /* Bug2855253 removed and crh.current_record_flag ='N' */
1099
1100 and dis.source_table = 'CRH'
1101 and dis.source_type = 'BANK_CHARGES';
1102
1103 IF PG_DEBUG in ('Y', 'C') THEN
1104 arp_standard.debug('update_cash_receipt: ' || 'p_factor_discount_amount: ' || to_char(p_factor_discount_amount));
1105 arp_standard.debug('update_cash_receipt: ' || 'l_sum_fda_debits : ' || to_char(l_sum_fda_debits));
1106 arp_standard.debug('update_cash_receipt: ' || 'l_sum_fda_credits: ' || to_char(l_sum_fda_credits));
1107 arp_standard.debug('update_cash_receipt: ' || 'l_sum_acctd_fda_debits: ' || to_char(l_sum_acctd_fda_debits));
1108 arp_standard.debug('update_cash_receipt: ' || 'l_sum_acctd_fda_credits: ' || to_char(l_sum_acctd_fda_credits));
1109 END IF;
1110
1111 -- Positive result will create a DR entry, negative a CR entry.
1112
1113 /* Bug fix 3677912 */
1114 IF l_bcharge_row_on_current_crh = 'Y' THEN
1115 arp_proc_rct_util.update_dist_rec(
1116 l_crh_rec.cash_receipt_history_id,
1117 'BANK_CHARGES',
1118 nvl(p_factor_discount_amount,0) - (l_sum_fda_debits - l_sum_fda_credits),
1119 nvl(l_crh_acctd_fda_new,0) - (l_sum_acctd_fda_debits - l_sum_acctd_fda_credits) );
1120 ELSE
1121 arp_proc_rct_util.insert_dist_rec(
1122 nvl(p_factor_discount_amount,0) - (l_sum_fda_debits - l_sum_fda_credits),
1123 nvl(l_crh_acctd_fda_new,0) - (l_sum_acctd_fda_debits - l_sum_acctd_fda_credits),
1124 l_crh_rec.cash_receipt_history_id,
1125 'BANK_CHARGES',
1126 l_bank_charges_ccid);
1127 END IF;
1128
1129 ELSE -- (NVL(l_crh_rec.factor_discount_amount,0) <= 0)
1130
1131 --
1132 -- If no bank charge record existed before, we need to create one.
1133 --
1134
1135 arp_proc_rct_util.insert_dist_rec(
1136 p_factor_discount_amount,
1137 l_crh_acctd_fda_new,
1138 l_crh_rec.cash_receipt_history_id,
1139 'BANK_CHARGES',
1140 l_bank_charges_ccid);
1141
1142 END IF;
1143
1144 END IF; -- (l_crh_fda_changed_flag = TRUE)
1145
1146 -- now update amount columns in l_cr_rec
1147
1148 l_crh_rec.amount := l_crh_amount_new;
1149 l_crh_rec.acctd_amount := l_crh_acctd_amount_new;
1150 l_crh_rec.factor_discount_amount := p_factor_discount_amount;
1151 l_crh_rec.acctd_factor_discount_amount := l_crh_acctd_fda_new;
1152
1153 -- populate the bank charge ccid only if there is a bank charge amount
1154
1155 IF (l_crh_rec.factor_discount_amount IS NOT NULL) THEN
1156 l_crh_rec.bank_charge_account_ccid := l_bank_charges_ccid;
1157 ELSE
1158 l_crh_rec.bank_charge_account_ccid := NULL;
1159 END IF;
1160
1161 arp_cr_history_pkg.update_p(l_crh_rec);
1162
1163 ELSE
1164
1165 -- Current cash receipt history record was posted.
1166 -- Date of the receipt is no longer valid
1167 -- That means we need to create a new cash receipt history
1168 -- record and distribution records for it.
1169
1170 -- make copy of cash receipt history record and null out/update
1171 -- columns that will be different in the new record.
1172
1173 l_crh_rec_new := l_crh_rec;
1174
1175 l_crh_rec_new.cash_receipt_history_id := NULL;
1176 l_crh_rec_new.posting_control_id := -3;
1177 l_crh_rec_new.gl_posted_date := NULL;
1178 -- #511576 Set the gl date for the new record.
1179 l_crh_rec_new.gl_date := l_valid_gl_date;
1180 l_crh_rec_new.first_posted_record_flag := 'N';
1181
1182 l_crh_rec_new.amount := l_crh_amount_new;
1183 l_crh_rec_new.acctd_amount := l_crh_acctd_amount_new;
1184 l_crh_rec_new.factor_discount_amount := p_factor_discount_amount;
1185 l_crh_rec_new.acctd_factor_discount_amount := l_crh_acctd_fda_new;
1186
1187 arp_cr_history_pkg.insert_p(l_crh_rec_new, l_crh_id_new);
1188
1189 l_crh_rec_new.cash_receipt_history_id := l_crh_id_new;
1190
1191 -- modify the previously current cash receipt history record:
1192
1193 l_crh_rec.current_record_flag := 'N';
1194 l_crh_rec.reversal_cash_receipt_hist_id := l_crh_id_new;
1195 -- #511576 Set the reversal gl date for the updated record.
1196 l_crh_rec.reversal_gl_date := l_valid_gl_date;
1197 l_crh_rec.reversal_created_from := 'ARRERG1B';
1198
1199 arp_cr_history_pkg.update_p(l_crh_rec);
1200
1201 -- from now on the new record is the current one:
1202
1203 l_crh_rec := l_crh_rec_new;
1204
1205 -- create new distributions for the new record. These
1206 -- distribution records are for the difference between the
1207 -- amounts and factor_discount_amounts in the old and new
1208 -- cash_receipt_history records.
1209
1210 IF (l_crh_amount_delta <> 0) THEN
1211
1212 IF PG_DEBUG in ('Y', 'C') THEN
1213 arp_standard.debug('update_cash_receipt: ' || 'l_crh_amount_delta : ' || to_char(l_crh_amount_delta));
1214 arp_standard.debug('update_cash_receipt: ' || 'l_crh_acctd_amount_delta : ' || to_char(l_crh_acctd_amount_delta));
1215 arp_standard.debug('update_cash_receipt: ' || 'cash_receipt_history_id : ' || to_char(l_crh_rec.cash_receipt_history_id));
1216 arp_standard.debug('update_cash_receipt: ' || 'l_source_type : ' || l_source_type);
1217 arp_standard.debug('update_cash_receipt: ' || 'l_crh_ccid : ' || l_crh_ccid);
1218 END IF;
1219
1220 arp_proc_rct_util.insert_dist_rec(l_crh_amount_delta,
1221 l_crh_acctd_amount_delta,
1222 l_crh_rec.cash_receipt_history_id,
1223 l_source_type,
1224 l_crh_ccid);
1225 END IF;
1226
1227 -- Need to check the acctd fda change also. The fda may not have
1228 -- changed but the acctd fda may have been altered implicitly
1229 -- as a result of a receipt amount change.
1230
1231 IF (l_crh_fda_delta <> 0 OR
1232 l_crh_acctd_fda_delta <> 0) THEN
1233
1234 IF PG_DEBUG in ('Y', 'C') THEN
1235 arp_standard.debug('update_cash_receipt: ' || 'l_crh_fda_delta : ' || to_char(l_crh_fda_delta));
1236 arp_standard.debug('update_cash_receipt: ' || 'l_crh_acctd_fda_delta : ' || to_char(l_crh_acctd_fda_delta));
1237 arp_standard.debug('update_cash_receipt: ' || 'cash_receipt_history_id : ' || to_char(l_crh_rec.cash_receipt_history_id));
1238 arp_standard.debug('update_cash_receipt: ' || 'l_crh_ccid : ' || l_bank_charges_ccid);
1239 END IF;
1240
1241 arp_proc_rct_util.insert_dist_rec(
1242 l_crh_fda_delta,
1243 l_crh_acctd_fda_delta,
1244 l_crh_rec.cash_receipt_history_id,
1245 'BANK_CHARGES',
1246 l_bank_charges_ccid);
1247 END IF;
1248
1249 END IF;
1250
1251 -- now create receivable applications record to account for
1252 -- the difference in the receipt amount:
1253
1254 IF (l_cr_rec.pay_from_customer IS NULL) THEN
1255
1256 -- receipt is unidentified; create UNID record in
1257 -- AR_RECEIVABLE_APPLICATIONS
1258
1259 arp_proc_rct_util.insert_ra_rec_cash(
1260 p_cash_receipt_id,
1261 l_cr_amount_delta,
1262 p_receipt_date,
1263 'UNID',
1264 l_cr_acctd_amount_delta,
1265 l_valid_gl_date,
1266 l_unidentified_ccid,
1267 l_ps_rec.payment_schedule_id,
1268 '60.4',
1269 '',
1270 l_ra_id_unid);
1271
1272 /*mrc trigger elimination project*/
1273 ar_mrc_engine3.update_ra_rec_cash_diff(
1274 p_rec_app_id => l_ra_id_unid,
1275 p_cash_receipt_id => p_cash_receipt_id,
1276 p_diff_amount => l_cr_amount_delta,
1277 p_old_rcpt_amount => l_cr_rec.amount,
1278 p_payment_schedule_id =>l_ps_rec.payment_schedule_id
1279 );
1280
1281 --
1282 --Release 11.5 VAT changes, create UNID receivable application accounting
1283 --in ar_distributions
1284 --
1285 l_ae_doc_rec.document_type := 'RECEIPT';
1286 l_ae_doc_rec.document_id := p_cash_receipt_id;
1287 l_ae_doc_rec.accounting_entity_level := 'ONE';
1288 l_ae_doc_rec.source_table := 'RA';
1289 l_ae_doc_rec.source_id := l_ra_id_unid;
1290 l_ae_doc_rec.source_id_old := '';
1291 l_ae_doc_rec.other_flag := '';
1292 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1293
1294 ELSE
1295
1296 -- receipt is identified; create UNAPP record in
1297 -- AR_RECEIVABLE_APPLICATIONS
1298
1299 arp_proc_rct_util.insert_ra_rec_cash(
1300 p_cash_receipt_id,
1301 l_cr_amount_delta,
1302 p_receipt_date,
1303 'UNAPP',
1304 l_cr_acctd_amount_delta,
1305 l_valid_gl_date,
1306 l_unapplied_ccid,
1307 l_ps_rec.payment_schedule_id,
1308 '60.2',
1309 '',
1310 l_ra_id_unapp);
1311
1312 -- 6924942 - Start
1313 update ar_receivable_applications
1314 set include_in_accumulation ='N'
1315 where cash_receipt_id = p_cash_receipt_id
1316 and status = 'UNAPP';
1317 -- 6924942 - End
1318
1319 --
1320 --Release 11.5 VAT changes, create UNAPP receivable application accounting
1321 --in ar_distributions
1322 --
1323 l_ae_doc_rec.document_type := 'RECEIPT';
1324 l_ae_doc_rec.document_id := p_cash_receipt_id;
1325 l_ae_doc_rec.accounting_entity_level := 'ONE';
1326 l_ae_doc_rec.source_table := 'RA';
1327 l_ae_doc_rec.source_id := l_ra_id_unapp;
1328 l_ae_doc_rec.source_id_old := '';
1329 l_ae_doc_rec.other_flag := '';
1330
1331 /* We need to set the third party id and sub id as the cash receipt
1332 is updated later */
1333 l_ae_doc_rec.miscel5 := p_pay_from_customer;
1334 l_ae_doc_rec.miscel6 := p_customer_site_use_id;
1335 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1336
1337 END IF;
1338
1339 arp_ps_util.populate_closed_dates( NULL, NULL, 'PMT', l_ps_rec ); /* Bug 4294346 */
1340
1341 END IF;
1342
1343 END IF;
1344
1345 -- -------------------------------------------------------------
1346 -- Now update columns that don't require any special logic
1347 -- -------------------------------------------------------------
1348
1349 -- update the 'easy stuff':
1350
1351 l_cr_rec.customer_bank_branch_id := p_customer_bank_branch_id;
1352 l_cr_rec.anticipated_clearing_date := p_anticipated_clearing_date;
1353 l_cr_rec.receipt_number := p_receipt_number;
1354 l_cr_rec.doc_sequence_value := p_doc_sequence_value;
1355 l_cr_rec.doc_sequence_id := p_doc_sequence_id;
1356 l_cr_rec.customer_site_use_id := p_customer_site_use_id;
1357 l_cr_rec.customer_receipt_reference := p_customer_receipt_reference;
1358 l_cr_rec.customer_bank_account_id := p_customer_bank_account_id;
1359 l_cr_rec.comments := p_comments; -- nvl(p_comments,l_cr_rec.comments); -- fix for bug 13958550
1360 l_cr_rec.attribute1 := p_attribute1;
1361 l_cr_rec.attribute2 := p_attribute2;
1362 l_cr_rec.attribute3 := p_attribute3;
1363 l_cr_rec.attribute4 := p_attribute4;
1364 l_cr_rec.attribute5 := p_attribute5;
1365 l_cr_rec.attribute6 := p_attribute6;
1366 l_cr_rec.attribute7 := p_attribute7;
1367 l_cr_rec.attribute8 := p_attribute8;
1368 l_cr_rec.attribute9 := p_attribute9;
1369 l_cr_rec.attribute10 := p_attribute10;
1370 l_cr_rec.attribute11 := p_attribute11;
1371 l_cr_rec.attribute12 := p_attribute12;
1372 l_cr_rec.attribute13 := p_attribute13;
1373 l_cr_rec.attribute14 := p_attribute14;
1374 l_cr_rec.attribute15 := p_attribute15;
1375 l_cr_rec.attribute_category := p_attribute_category;
1376 l_cr_rec.ussgl_transaction_code := p_ussgl_transaction_code;
1377 l_cr_rec.override_remit_account_flag := p_override_remit_account_flag;
1378 l_cr_rec.deposit_date := p_deposit_date;
1379 l_cr_rec.remit_bank_acct_use_id := p_remittance_bank_account_id;
1380 l_cr_rec.vat_tax_id := p_vat_tax_id;
1381
1382 l_cr_rec.global_attribute1 := p_global_attribute1;
1383 l_cr_rec.global_attribute2 := p_global_attribute2;
1384 l_cr_rec.global_attribute3 := p_global_attribute3;
1385 l_cr_rec.global_attribute4 := p_global_attribute4;
1386 l_cr_rec.global_attribute5 := p_global_attribute5;
1387 l_cr_rec.global_attribute6 := p_global_attribute6;
1388 l_cr_rec.global_attribute7 := p_global_attribute7;
1389 l_cr_rec.global_attribute8 := p_global_attribute8;
1390 l_cr_rec.global_attribute9 := p_global_attribute9;
1391 l_cr_rec.global_attribute10 := p_global_attribute10;
1392 l_cr_rec.global_attribute11 := p_global_attribute11;
1393 l_cr_rec.global_attribute12 := p_global_attribute12;
1394 l_cr_rec.global_attribute13 := p_global_attribute13;
1395 l_cr_rec.global_attribute14 := p_global_attribute14;
1396 l_cr_rec.global_attribute15 := p_global_attribute15;
1397 l_cr_rec.global_attribute16 := p_global_attribute16;
1398 l_cr_rec.global_attribute17 := p_global_attribute17;
1399 l_cr_rec.global_attribute18 := p_global_attribute18;
1400 l_cr_rec.global_attribute19 := p_global_attribute19;
1401 l_cr_rec.global_attribute20 := p_global_attribute20;
1402 l_cr_rec.global_attribute_category := p_global_attribute_category;
1403
1404 l_cr_rec.issuer_name := p_issuer_name;
1405 l_cr_rec.issue_date := p_issue_date;
1406 l_cr_rec.issuer_bank_branch_id := p_issuer_bank_branch_id;
1407
1408 -- Credit Card changes.
1409
1410 l_cr_rec.payment_server_order_num := p_payment_server_order_num;
1411 l_cr_rec.approval_code := p_approval_code;
1412
1413 -- ARTA Changes
1414 l_cr_rec.postmark_date := p_postmark_date;
1415
1416 -- Enhancement 2074220
1417 l_cr_rec.application_notes := p_application_notes;
1418
1419 -- LE
1420 l_cr_rec.legal_entity_id := p_legal_entity_id;
1421 -- PAYMENT_UPTAKE
1422 l_cr_rec.payment_trxn_extension_id := p_payment_trxn_extension_id;
1423 l_cr_rec.automatch_set_id := p_automatch_set_id; /* ER Automatch Application */
1424 l_cr_rec.autoapply_flag := p_autoapply_flag;
1425
1426
1427
1428 IF (l_crh_rec.status = 'APPROVED' AND
1429 p_status = 'CONFIRMED') THEN
1430 l_cr_rec.confirmed_flag := 'Y';
1431 ELSIF (l_crh_rec.status = 'CONFIRMED' AND
1432 p_status = 'APPROVED') THEN
1433 l_cr_rec.confirmed_flag := 'N';
1434 END IF;
1435
1436 -- update payment schedule customer columns:
1437
1438 l_ps_rec.customer_id := p_pay_from_customer;
1439 l_ps_rec.customer_site_use_id := p_customer_site_use_id;
1440
1441 -- set payment schedule due date
1442
1443 l_ps_rec.due_date := NVL(p_maturity_date, p_deposit_date);
1444
1445 IF PG_DEBUG in ('Y', 'C') THEN
1446 arp_standard.debug('Before Inserting CR/CRH record in update_cash_receipt.');
1447 END IF;
1448
1449 --{BUG#4353362
1450 OPEN cu_current_customer;
1451 FETCH cu_current_customer INTO l_current_customer_id, l_current_csu_id;
1452 IF cu_current_customer%FOUND THEN
1453 arp_acct_event_pkg.update_cr_dist
1454 ( p_ledger_id => arp_global.set_of_books_id
1455 ,p_source_id_int_1 => p_cash_receipt_id
1456 ,p_third_party_merge_date => l_valid_gl_date
1457 ,p_original_third_party_id => l_current_customer_id
1458 ,p_original_site_id => l_current_csu_id
1459 ,p_new_third_party_id => p_pay_from_customer
1460 ,p_new_site_id => p_customer_site_use_id
1461 ,p_create_update => 'U'
1462 ,p_entity_code => 'RECEIPTS'
1463 ,p_type_of_third_party_merge => 'PARTIAL'
1464 ,p_mapping_flag => 'N'
1465 ,p_execution_mode => 'SYNC'
1466 ,p_accounting_mode => 'F'
1467 ,p_transfer_to_gl_flag => 'Y'
1468 ,p_post_in_gl_flag => 'Y'
1469 ,p_third_party_type => 'C'
1470 ,x_errbuf => x_errbuf
1471 ,x_retcode => x_retcode
1472 ,x_event_ids => x_event_ids
1473 ,x_request_id => x_request_id);
1474 END IF;
1475 CLOSE cu_current_customer;
1476 --}
1477
1478
1479 -- update actual receipt record:
1480
1481 arp_cash_receipts_pkg.update_p(l_cr_rec);
1482
1483 /*
1484 -- update the history record:
1485
1486 -- this should've happened earlier if the amount changed.
1487
1488 arp_cr_history_pkg.update_p(l_crh_rec);
1489
1490 */
1491
1492 -- update payment schedule record:
1493
1494 arp_ps_pkg.update_p(l_ps_rec);
1495
1496 --apandit
1497 --Bug 2641517 creating a history record for the modification
1498 --and raising the business event.
1499 IF (l_cr_amount_changed_flag) OR
1500 (l_crh_fda_changed_flag) OR
1501 (l_rct_identified_flag) OR
1502 (l_rct_unidentified_flag) OR
1503 (l_rct_site_changed_flag) OR
1504 (l_rct_customer_changed_flag)
1505 THEN
1506 --Insert the history record
1507 AR_BUS_EVENT_COVER.p_insert_trx_sum_hist(l_trx_sum_hist_rec,
1508 l_history_id,
1509 'PMT',
1510 'MODIFY_PMT');
1511
1512 --Raise the business event
1513 AR_BUS_EVENT_COVER.Raise_Rcpt_Modify_Event(l_ps_rec.cash_receipt_id,
1514 l_ps_rec.payment_schedule_id,
1515 l_history_id);
1516 END IF;
1517
1518 -- check if receipt has been confirmed:
1519
1520 IF (l_crh_rec.status = 'APPROVED' AND
1521 p_status = 'CONFIRMED') THEN
1522
1523 arp_confirmation.confirm(
1524 p_cash_receipt_id,
1525 p_confirm_gl_date,
1526 p_confirm_date,
1527 p_form_name,
1528 p_form_version);
1529
1530 ELSIF (l_crh_rec.status = 'CONFIRMED' AND
1531 p_status = 'APPROVED') THEN
1532
1533 arp_confirmation.unconfirm(
1534 p_cash_receipt_id,
1535 p_unconfirm_gl_date,
1536 SYSDATE,
1537 p_form_name,
1538 p_form_version);
1539
1540 END IF;
1541
1542 IF (p_reversal_date IS NOT NULL AND
1543 l_cr_rec.reversal_date IS NULL) THEN
1544 IF PG_DEBUG in ('Y', 'C') THEN
1545 arp_standard.debug('update_cash_receipt: ' || 'Receipt needs to be reversed.');
1546 END IF;
1547 IF (p_dm_reversal_flag = 'Y') THEN
1548 IF PG_DEBUG in ('Y', 'C') THEN
1549 arp_standard.debug('update_cash_receipt: ' || 'Debit memo reversal required');
1550 END IF;
1551
1552 arp_reverse_receipt.debit_memo_reversal(
1553 l_cr_rec,
1554 p_cc_id,
1555 p_dm_cust_trx_type_id,
1556 p_dm_cust_trx_type,
1557 p_reversal_gl_date,
1558 p_reversal_date,
1559 p_reversal_category,
1560 p_reversal_reason_code,
1561 p_reversal_comments,
1562 p_attribute_category, p_attribute1,
1563 p_attribute2, p_attribute3, p_attribute4,
1564 p_attribute5, p_attribute6, p_attribute7,
1565 p_attribute8, p_attribute9, p_attribute10,
1566 p_attribute11, p_attribute12, p_attribute13,
1567 p_attribute14, p_attribute15,
1568 p_dm_number,
1569 p_dm_doc_sequence_value,
1570 p_dm_doc_sequence_id,
1571 p_tw_status,
1572 p_form_name,
1573 p_form_version);
1574 --apandit
1575 --Bug 2641517 Insert the history record and raising
1576 --the business event
1577 AR_BUS_EVENT_COVER.p_insert_trx_sum_hist(l_trx_sum_hist_rec,
1578 l_history_id,
1579 'PMT',
1580 'DM_REVERSE_PMT');
1581
1582 --Raise the business event
1583
1584 AR_BUS_EVENT_COVER.Raise_Rcpt_DMReverse_Event(l_ps_rec.cash_receipt_id,
1585 l_ps_rec.payment_schedule_id,
1586 l_history_id);
1587 ELSE
1588 IF PG_DEBUG in ('Y', 'C') THEN
1589 arp_standard.debug('update_cash_receipt: ' || 'Regular reversal required');
1590 END IF;
1591
1592 -- Bug 2946734 - update all claims on this receipt
1593 arp_reverse_receipt.cancel_claims(
1594 p_cr_id => l_cr_rec.cash_receipt_id
1595 , p_include_trx_claims => 'Y'
1596 , x_return_status => l_return_status
1597 , x_msg_count => l_msg_count
1598 , x_msg_data => l_msg_data);
1599 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1600 APP_EXCEPTION.raise_exception;
1601 END IF;
1602
1603 arp_reverse_receipt.reverse(
1604 l_cr_rec.cash_receipt_id,
1605 p_reversal_category,
1606 p_reversal_gl_date,
1607 p_reversal_date,
1608 p_reversal_reason_code,
1609 p_reversal_comments,
1610 NULL, -- clear_batch_id
1611 p_attribute_category,
1612 p_attribute1,
1613 p_attribute2,
1614 p_attribute3,
1615 p_attribute4,
1616 p_attribute5,
1617 p_attribute6,
1618 p_attribute7,
1619 p_attribute8,
1620 p_attribute9,
1621 p_attribute10,
1622 p_attribute11,
1623 p_attribute12,
1624 p_attribute13,
1625 p_attribute14,
1626 p_attribute15,
1627 p_form_name,
1628 p_form_version,
1629 l_rev_crh_id);
1630
1631 --apandit
1632 --Bug 2641517 Insert the history record and raising
1633 --the business event
1634 IF PG_DEBUG in ('Y', 'C') THEN
1635 arp_standard.debug('update_cash_receipt: ' || 'before creating the history rec for BusinessEvent');
1636 END IF;
1637 AR_BUS_EVENT_COVER.p_insert_trx_sum_hist(l_trx_sum_hist_rec,
1638 l_history_id,
1639 'PMT',
1640 'REVERSE_PMT');
1641
1642 --Raise the business event
1643 IF PG_DEBUG in ('Y', 'C') THEN
1644 arp_standard.debug('update_cash_receipt: ' || 'before raising the new business event');
1645 END IF;
1646 AR_BUS_EVENT_COVER.Raise_Rcpt_Reverse_Event(l_ps_rec.cash_receipt_id,
1647 l_ps_rec.payment_schedule_id,
1648 l_history_id);
1649 END IF;
1650
1651 END IF;
1652
1653 -- check if receipt needs to be rate-adjusted:
1654
1655 IF (p_rate_adjust_gl_date IS NOT NULL) THEN
1656 arp_proc_rct_util.rate_adjust(
1657 p_cash_receipt_id,
1658 p_rate_adjust_gl_date,
1659 p_new_exchange_date,
1660 p_new_exchange_rate,
1661 p_new_exchange_rate_type,
1662 l_cr_rec.exchange_date,
1663 l_cr_rec.exchange_rate,
1664 l_cr_rec.exchange_rate_type,
1665 p_gain_loss,
1666 p_exchange_rate_attr_cat,
1667 p_exchange_rate_attr1,
1668 p_exchange_rate_attr2,
1669 p_exchange_rate_attr3,
1670 p_exchange_rate_attr4,
1671 p_exchange_rate_attr5,
1672 p_exchange_rate_attr6,
1673 p_exchange_rate_attr7,
1674 p_exchange_rate_attr8,
1675 p_exchange_rate_attr9,
1676 p_exchange_rate_attr10,
1677 p_exchange_rate_attr11,
1678 p_exchange_rate_attr12,
1679 p_exchange_rate_attr13,
1680 p_exchange_rate_attr14,
1681 p_exchange_rate_attr15);
1682 END IF;
1683 IF PG_DEBUG in ('Y', 'C') THEN
1684 arp_standard.debug('arp_process_receipts.update_cash_receipt()+');
1685 END IF;
1686
1687
1688 /* Bug8422361 - Code Changes - Start.
1689 Added logic to assign the bank account present on an unidentified
1690 receipt created via lockbox to the customer once the receipt is
1691 identified from the Receipt UI. This functionality is restored
1692 back from 11i to R12, which is lost due to Payment Uptake.
1693 */
1694 IF p_pay_from_customer IS NOT NULL AND
1695 p_customer_bank_account_id IS NOT NULL AND
1696 l_rct_identified_flag = TRUE
1697 THEN
1698
1699 SELECT PARTY_ID INTO l_party_id
1700 FROM HZ_CUST_ACCOUNTS
1701 WHERE CUST_ACCOUNT_ID = p_pay_from_customer;
1702
1703 SELECT COUNT(*) INTO l_no_of_accounts
1704 FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V
1705 WHERE CUST_ACCOUNT_ID = p_pay_from_customer
1706 AND INSTRUMENT_ID = p_customer_bank_account_id;
1707
1708 IF PG_DEBUG in ('Y', 'C') THEN
1709 ARP_STANDARD.DEBUG('p_pay_from_customer :- ' || p_pay_from_customer );
1710 ARP_STANDARD.DEBUG('p_customer_bank_account_id :- ' || p_customer_bank_account_id );
1711 ARP_STANDARD.DEBUG('l_no_of_accounts :- ' || l_no_of_accounts );
1712 ARP_STANDARD.DEBUG('l_party_id :- ' || l_party_id );
1713 END IF;
1714
1715 IF l_no_of_accounts = 0 THEN
1716 SELECT COUNT(*) INTO l_no_of_other_accounts
1717 FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V
1718 WHERE NVL(CUST_ACCOUNT_ID, -99) <> p_pay_from_customer
1719 AND INSTRUMENT_ID = p_customer_bank_account_id;
1720
1721 IF PG_DEBUG in ('Y', 'C') THEN
1722 ARP_STANDARD.DEBUG('l_no_of_other_accounts :- ' || l_no_of_other_accounts );
1723 END IF;
1724
1725 IF l_no_of_other_accounts = 0 THEN
1726 l_bank_assign_flag := TRUE;
1727 ELSE
1728 SELECT COUNT(*) INTO l_no_of_other_party_accounts
1729 FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V
1730 WHERE PARTY_ID <> l_party_id
1731 AND INSTRUMENT_ID = p_customer_bank_account_id;
1732
1733 IF PG_DEBUG in ('Y', 'C') THEN
1734 ARP_STANDARD.DEBUG('l_no_of_other_party_accounts :- ' || l_no_of_other_party_accounts );
1735 END IF;
1736
1737 IF l_no_of_other_party_accounts = 0 THEN
1738 l_bank_assign_flag := TRUE;
1739 ELSE
1740
1741 UPDATE AR_CASH_RECEIPTS SET
1742 CUSTOMER_BANK_ACCOUNT_ID = NULL,
1743 APPLICATION_NOTES = (SELECT DISTINCT 'Removed Bank Account: '||BANK_NAME||'-'||ACCOUNT_NUMBER||
1744 ' from this receipt.'
1745 FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V
1746 WHERE INSTRUMENT_ID = p_customer_bank_account_id)
1747 WHERE CASH_RECEIPT_ID = p_cash_receipt_id;
1748
1749 END IF;
1750 END IF;
1751 END IF;
1752
1753 IF l_bank_assign_flag = TRUE THEN
1754
1755 IBY_EXT_BANKACCT_PUB.ADD_JOINT_ACCOUNT_OWNER(
1756 p_api_version => l_api_version,
1757 p_init_msg_list => l_init_msg_list,
1758 p_bank_account_id => p_customer_bank_account_id,
1759 p_acct_owner_party_id => l_party_id,
1760 x_joint_acct_owner_id => l_joint_acct_owner_id,
1761 x_return_status => l_iby_return_status,
1762 x_msg_count => l_msg_count,
1763 x_msg_data => l_msg_data,
1764 x_response => l_response_rec );
1765
1766 IF PG_DEBUG in ('Y', 'C') THEN
1767 ARP_STANDARD.DEBUG('IBY_EXT_BANKACCT_PUB.ADD_JOINT_ACCOUNT_OWNER: Return Status :- ' || l_iby_return_status);
1768 END IF;
1769
1770 IF l_iby_return_status = fnd_api.g_ret_sts_error OR
1771 l_iby_return_status = fnd_api.g_ret_sts_unexp_error THEN
1772
1773 ARP_STANDARD.DEBUG('Errors Reported By IBY Add Joint Account Owner API :-');
1774
1775 FOR i in 1..l_msg_count LOOP
1776 FND_MSG_PUB.GET(fnd_msg_pub.g_first, fnd_api.g_false, l_msg_data, l_msg_count);
1777 ARP_STANDARD.DEBUG(l_msg_data);
1778 END LOOP;
1779
1780 ELSE
1781 IF PG_DEBUG in ('Y', 'C') THEN
1782 ARP_STANDARD.DEBUG('Joint Account Owner Id :- ' || l_joint_acct_owner_id );
1783 END IF;
1784 END IF;
1785
1786 ARP_EXT_BANK_PKG.INSERT_ACCT_INSTR_ASSIGNMENT(
1787 p_party_id => l_party_id,
1788 p_customer_id => p_pay_from_customer,
1789 p_instr_id => p_customer_bank_account_id,
1790 p_assignment_start_date => p_receipt_date, --Add for bug 13801721
1791 x_instr_assign_id => l_instr_assign_id,
1792 x_return_status => l_iby_return_status );
1793
1794 IF PG_DEBUG in ('Y', 'C') THEN
1795 ARP_STANDARD.DEBUG('Instrument Assignment: l_instr_assign_id :- '||l_instr_assign_id);
1796 ARP_STANDARD.DEBUG('Instrument Assignment: l_iby_return_status :- '||l_iby_return_status);
1797 END IF;
1798
1799 END IF;
1800 END IF;
1801 /* Bug8422361 - Code Changes - End. */
1802
1803
1804 -- determine receipt's new state and status and return it to form:
1805
1806 -- Bug no 968913 SRAJASEK Modified the sql statement to retrieve the data
1807 -- from the base tables rather than the ar_cash_receipt_v view for
1808 -- performance reasons
1809
1810 SELECT cr.status,
1811 l_cr_status.meaning,
1812 crh_current.status ,
1813 l_crh_status.meaning
1814 INTO p_new_status,
1815 p_new_status_dsp,
1816 p_new_state,
1817 p_new_state_dsp
1818 FROM
1819 ar_cash_receipt_history crh_current,
1820 ar_cash_receipts cr,
1821 ar_lookups l_cr_status,
1822 ar_lookups l_crh_status
1823 WHERE
1824 cr.cash_receipt_id = p_cash_receipt_id
1825 AND l_cr_status.lookup_type = 'CHECK_STATUS'
1826 AND l_cr_status.lookup_code = cr.status
1827 AND l_crh_status.lookup_type = 'RECEIPT_CREATION_STATUS'
1828 AND l_crh_status.lookup_code = crh_current.status
1829 AND crh_current.cash_receipt_id = cr.cash_receipt_id
1830 AND crh_current.current_record_flag = 'Y';
1831
1832 EXCEPTION
1833 WHEN OTHERS THEN
1834 IF PG_DEBUG in ('Y', 'C') THEN
1835 arp_standard.debug('Exception in arp_process_receipts.update_cash_receipts');
1836 arp_standard.debug('update_cash_receipt: ' || 'p_cash_receipt_id = '|| to_char(p_cash_receipt_id));
1837 arp_standard.debug('update_cash_receipt: ' || 'p_amount = '|| to_char(p_amount));
1838 arp_standard.debug('update_cash_receipt: ' || 'p_factor_discount_amount = '|| to_char(p_factor_discount_amount));
1839 END IF;
1840 RAISE;
1841
1842 IF PG_DEBUG in ('Y', 'C') THEN
1843 arp_standard.debug('arp_process_rct_util.update_cash_receipts()-');
1844 END IF;
1845
1846 END update_cash_receipt;
1847
1848 END ARP_PROC_RECEIPTS1;
[Home] [Help]