[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_MISC_RECEIPTS
Source
1 PACKAGE BODY ARP_PROCESS_MISC_RECEIPTS AS
2 /* $Header: ARREMTRB.pls 120.23.12020000.3 2012/07/25 09:34:05 riqi ship $ */
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4
5 /* declare subtype for VAT changes */
6 SUBTYPE l_ae_doc_rec_type IS arp_acct_main.ae_doc_rec_type ;
7 --
8
9 /* ---------------------- Public functions -------------------------------- */
10
11 FUNCTION revision RETURN VARCHAR2 IS
12 BEGIN
13
14 RETURN '$Revision: 120.23.12020000.3 $';
15
16 END revision;
17
18 /*===========================================================================+
19 | PROCEDURE |
20 | update_misc_receipt |
21 | |
22 | DESCRIPTION |
23 | Entity handler that updates miscelleanous transactions. |
24 | |
25 | SCOPE - PUBLIC |
26 | |
27 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
28 | |
29 | NOTES |
30 | |
31 | MODIFICATION HISTORY |
32 | |
33 | 09-OCT-95 OSTEINME created |
34 | 13-NOV-96 OSTEINME added parameter anticipated_clearing_date |
35 | for CE enhancement. |
36 | NOTE: This version of the file is not longer |
37 | compatible with 10.6 and prod15! |
38 | 30-DEC-96 OSTEINME added global descriptive flexfield parameters|
39 | 04-DEC-97 KLAWRANC Bug #590256. Modified call to |
40 | calc_acctd_amount. Now passes NULL for the |
41 | currency code parameter, therefore the acctd |
42 | amount will be calculated based on the |
43 | functional currency. |
44 | 04-FEB-98 KLAWRANC Bug #546677. Added check for amount change |
45 | before updating distributions table. |
46 | 21-MAY-98 KTANG For all calls to calc_acctd_amount which |
47 | calculates header accounted amounts, if the |
48 | exchange_rate_type is not user, call |
49 | gl_currency_api.convert_amount instead. This |
50 | is for triangulation. |
51 | 26-JUL-99 GJWANG Do not call accounting routine for update if |
52 | misc rec has been posted |
53 | 26-AUG-99 GJWANG Bug 923425: check posting only on the current|
54 | cash receipt history |
55 | 09-MAY-02 RKADER Bug #2322468. Rate adjustment fail when |
56 | rate type is changed from 'User' to another |
57 | rate type. |
58 | 26-SEP-02 RKADER Bug #2561342: The GL date should not be |
59 | updated for the history record while a Misc |
60 | receipt is updated
61 | 14-OCT-04 JBECKETT Bug 3911642: Check for unposted entries |
62 | is on ar_misc_cash_distributions not |
63 | ar_cash_receipt_history as rows from the |
64 | former are deleted/recreated. |
65 | 20-MAY-05 JBECKETT Added p_legal_entity_id for R12 LE uptake |
66 | 14-OCT-11 Jianchao Chi Bug fix 7422644: Add the logic to store the |
67 | update for the reference customer. |
68 +===========================================================================*/
69
70
71 PROCEDURE update_misc_receipt(
72 p_cash_receipt_id IN NUMBER,
73 p_batch_id IN NUMBER,
74 p_currency_code IN VARCHAR2,
75 p_amount IN NUMBER,
76 p_receivables_trx_id IN NUMBER,
77 p_misc_payment_source IN VARCHAR2,
78 p_receipt_number IN VARCHAR2,
79 p_receipt_date IN DATE,
80 p_gl_date IN DATE,
81 p_comments IN VARCHAR2,
82 p_exchange_rate_type IN VARCHAR2,
83 p_exchange_rate IN NUMBER,
84 p_exchange_date IN DATE,
85 p_attribute_category IN VARCHAR2,
86 p_attribute1 IN VARCHAR2,
87 p_attribute2 IN VARCHAR2,
88 p_attribute3 IN VARCHAR2,
89 p_attribute4 IN VARCHAR2,
90 p_attribute5 IN VARCHAR2,
91 p_attribute6 IN VARCHAR2,
92 p_attribute7 IN VARCHAR2,
93 p_attribute8 IN VARCHAR2,
94 p_attribute9 IN VARCHAR2,
95 p_attribute10 IN VARCHAR2,
96 p_attribute11 IN VARCHAR2,
97 p_attribute12 IN VARCHAR2,
98 p_attribute13 IN VARCHAR2,
99 p_attribute14 IN VARCHAR2,
100 p_attribute15 IN VARCHAR2,
101 p_remittance_bank_account_id IN NUMBER,
102 p_deposit_date IN DATE,
103 p_receipt_method_id IN NUMBER,
104 p_doc_sequence_value IN NUMBER,
105 p_doc_sequence_id IN NUMBER,
106 p_distribution_set_id IN NUMBER,
107 p_reference_type IN VARCHAR2,
108 p_reference_id IN NUMBER,
109 p_vat_tax_id IN NUMBER,
110 p_ussgl_transaction_code IN VARCHAR2,
111 -- ******* Rate Adjustment parameters: ********
112 p_rate_adjust_gl_date IN DATE,
113 p_new_exchange_date IN DATE,
114 p_new_exchange_rate IN NUMBER,
115 p_new_exchange_rate_type IN VARCHAR2,
116 p_gain_loss IN NUMBER,
117 p_exchange_rate_attr_cat IN VARCHAR2,
118 p_exchange_rate_attr1 IN VARCHAR2,
119 p_exchange_rate_attr2 IN VARCHAR2,
120 p_exchange_rate_attr3 IN VARCHAR2,
121 p_exchange_rate_attr4 IN VARCHAR2,
122 p_exchange_rate_attr5 IN VARCHAR2,
123 p_exchange_rate_attr6 IN VARCHAR2,
124 p_exchange_rate_attr7 IN VARCHAR2,
125 p_exchange_rate_attr8 IN VARCHAR2,
126 p_exchange_rate_attr9 IN VARCHAR2,
127 p_exchange_rate_attr10 IN VARCHAR2,
128 p_exchange_rate_attr11 IN VARCHAR2,
129 p_exchange_rate_attr12 IN VARCHAR2,
130 p_exchange_rate_attr13 IN VARCHAR2,
131 p_exchange_rate_attr14 IN VARCHAR2,
132 p_exchange_rate_attr15 IN VARCHAR2,
133 --
134 -- ********* Reversal Info ***********
135 --
136 p_reversal_date IN DATE,
137 p_reversal_gl_date IN DATE,
138 p_reversal_category IN VARCHAR2,
139 p_reversal_comments IN VARCHAR2,
140 p_reversal_reason_code IN VARCHAR2,
141 --
142 -- ********* CashBook Expected Date (new in 10.7) ******
143 --
144 p_anticipated_clearing_date IN DATE,
145 --
146 -- ******* Global Flexfield parameters *******
147 --
148 p_global_attribute1 IN VARCHAR2,
149 p_global_attribute2 IN VARCHAR2,
150 p_global_attribute3 IN VARCHAR2,
151 p_global_attribute4 IN VARCHAR2,
152 p_global_attribute5 IN VARCHAR2,
153 p_global_attribute6 IN VARCHAR2,
154 p_global_attribute7 IN VARCHAR2,
155 p_global_attribute8 IN VARCHAR2,
156 p_global_attribute9 IN VARCHAR2,
157 p_global_attribute10 IN VARCHAR2,
158 p_global_attribute11 IN VARCHAR2,
159 p_global_attribute12 IN VARCHAR2,
160 p_global_attribute13 IN VARCHAR2,
161 p_global_attribute14 IN VARCHAR2,
162 p_global_attribute15 IN VARCHAR2,
163 p_global_attribute16 IN VARCHAR2,
164 p_global_attribute17 IN VARCHAR2,
165 p_global_attribute18 IN VARCHAR2,
166 p_global_attribute19 IN VARCHAR2,
167 p_global_attribute20 IN VARCHAR2,
168 p_global_attribute_category IN VARCHAR2,
169 --
170 --
171 --
172 -- ******* Receipt State/Status Return information ******
173 --
174 p_new_state OUT NOCOPY VARCHAR2,
175 p_new_state_dsp OUT NOCOPY VARCHAR2,
176 p_new_status OUT NOCOPY VARCHAR2,
177 p_new_status_dsp OUT NOCOPY VARCHAR2,
178 --
179 p_form_name IN varchar2,
180 p_form_version IN varchar2,
181 p_tax_rate IN NUMBER,
182 p_gl_tax_acct IN VARCHAR2, /* Bug fix 2300268 */
183 p_legal_entity_id IN NUMBER ) IS
184
185 l_cr_rec ar_cash_receipts%ROWTYPE;
186 l_crh_rec ar_cash_receipt_history%ROWTYPE;
187 l_dist_rec ar_distributions%ROWTYPE;
188 l_acctd_amount ar_cash_receipt_history.acctd_amount%TYPE;
189 l_ccid ar_cash_receipt_history.account_code_combination_id%TYPE;
190 l_override_dummy ar_cash_receipts.override_remit_account_flag%TYPE;
191 l_dummy NUMBER;
192 l_source_type ar_distributions.source_type%TYPE;
193 l_creation_status ar_cash_receipt_history.status%TYPE;
194 l_old_distribution_set_id ar_cash_receipts.distribution_set_id%TYPE;
195 l_dist_set_changed_flag BOOLEAN;
196 l_amount_changed_flag BOOLEAN;
197 l_gl_date_changed_flag BOOLEAN;
198 l_receipt_date_changed_flag BOOLEAN;
199 l_old_receivables_trx_id ar_receivables_trx.receivables_trx_id%TYPE;
200 l_rev_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
201 l_ae_doc_rec l_ae_doc_rec_type;
202 l_posted ar_cash_receipt_history.posting_control_id%TYPE;
203 l_unposted_count NUMBER;
204
205 BEGIN
206
207 IF PG_DEBUG in ('Y', 'C') THEN
208 arp_debug.debug('arp_process_misc_receipts.update_misc_receipt()+');
209 arp_debug.debug('update_misc_receipt: ' || '*****TAX RATE ' || TO_CHAR(p_tax_rate));
210 END IF;
211
212 -- fetch and lock existing records from database for update
213
214 l_cr_rec.cash_receipt_id := p_cash_receipt_id;
215 arp_cash_receipts_pkg.nowaitlock_fetch_p(l_cr_rec);
216
217 -- store old distribution_set_id and receivables_trx_id to allow
218 -- for creation of new distribution records if necessary
219
220 l_old_distribution_set_id := l_cr_rec.distribution_set_id;
221 l_old_receivables_trx_id := l_cr_rec.receivables_trx_id;
222
223 -- determine if amount or acctd amount have changed:
224
225 IF (l_cr_rec.amount <> p_amount) THEN
226 l_amount_changed_flag := TRUE;
227 ELSE
228 l_amount_changed_flag := FALSE;
229 END IF;
230
231 -- determine if receipt date has changed:
232
233 IF (l_cr_rec.receipt_date <> p_receipt_date) THEN
234 l_receipt_date_changed_flag := TRUE;
235 IF PG_DEBUG in ('Y', 'C') THEN
236 arp_debug.debug( 'Receipt Date has changed. Old: ' || to_char(l_cr_rec.receipt_date, 'DD-MON-YYYY') || ' New: ' || to_char(p_receipt_date));
237 END IF;
238 ELSE
239 l_receipt_date_changed_flag := FALSE;
240 END IF;
241
242 -- get history record:
243
244 l_crh_rec.cash_receipt_id := p_cash_receipt_id;
245 arp_cr_history_pkg.nowaitlock_fetch_f_cr_id(l_crh_rec);
246
247 -- determine if gl date was changed:
248
249 IF (l_crh_rec.gl_date <> p_gl_date) THEN
250 l_gl_date_changed_flag := TRUE;
251 IF PG_DEBUG in ('Y', 'C') THEN
252 arp_debug.debug( 'GL Date has changed. Old: ' || to_char(l_crh_rec.gl_date, 'DD-MON-YYYY') || ' New: ' || to_char(p_gl_date));
253 END IF;
254
255 ELSE
256 l_gl_date_changed_flag := FALSE;
257 END IF;
258
259 -- get context info based on payment method and remittance bank id
260
261
262 arp_cr_util.get_creation_info(p_receipt_method_id,
263 p_remittance_bank_account_id,
264 l_creation_status,
265 l_source_type,
266 l_ccid,
267 l_override_dummy);
268
269 -- calculate accounted amount
270 -- Changes for triangulation: If exchange rate type is not user, call
271 -- GL API to calculate accounted amount
272 /* Bug 2322468 : Added the OR condition*/
273 IF (p_exchange_rate_type = 'User') OR
274 (l_cr_rec.exchange_rate_type = 'User') THEN
275 arp_util.calc_acctd_amount( NULL,
276 NULL,
277 NULL,
278 l_cr_rec.exchange_rate,
279 '+',
280 p_amount,
281 l_acctd_amount,
282 0,
283 l_dummy,
284 l_dummy,
285 l_dummy);
286 ELSE
287 l_acctd_amount := gl_currency_api.convert_amount(
288 arp_global.set_of_books_id,
289 l_cr_rec.currency_code,
290 l_cr_rec.exchange_date,
291 l_cr_rec.exchange_rate_type,
292 p_amount);
293 END IF;
294
295 -- update cash receipt record:
296 IF PG_DEBUG in ('Y', 'C') THEN
297 arp_debug.debug('*****BEGIN UPDATE cash_receipt_record ');
298 END IF;
299
300 l_cr_rec.amount := p_amount;
301 l_cr_rec.receivables_trx_id := p_receivables_trx_id;
302 l_cr_rec.misc_payment_source := p_misc_payment_source;
303 l_cr_rec.receipt_number := p_receipt_number;
304 l_cr_rec.receipt_date := p_receipt_date;
305 l_cr_rec.comments := p_comments;
306 l_cr_rec.attribute_category := p_attribute_category;
307 l_cr_rec.attribute1 := p_attribute1;
308 l_cr_rec.attribute2 := p_attribute2;
309 l_cr_rec.attribute3 := p_attribute3;
310 l_cr_rec.attribute4 := p_attribute4;
311 l_cr_rec.attribute5 := p_attribute5;
312 l_cr_rec.attribute6 := p_attribute6;
313 l_cr_rec.attribute7 := p_attribute7;
314 l_cr_rec.attribute8 := p_attribute8;
315 l_cr_rec.attribute9 := p_attribute9;
316 l_cr_rec.attribute10 := p_attribute10;
317 l_cr_rec.attribute11 := p_attribute11;
318 l_cr_rec.attribute12 := p_attribute12;
319 l_cr_rec.attribute13 := p_attribute13;
320 l_cr_rec.attribute14 := p_attribute14;
321 l_cr_rec.attribute15 := p_attribute15;
322 l_cr_rec.remit_bank_acct_use_id := p_remittance_bank_account_id;
323 l_cr_rec.deposit_date := p_deposit_date;
324 l_cr_rec.distribution_set_id := p_distribution_set_id;
325 l_cr_rec.reference_id := p_reference_id;
326 l_cr_rec.reference_type := p_reference_type;
327 l_cr_rec.vat_tax_id := p_vat_tax_id;
328 l_cr_rec.ussgl_transaction_code := p_ussgl_transaction_code;
329 --VAT change begin: update tax_rate when tax_treatment changed
330 l_cr_rec.tax_rate := p_tax_rate;
331 --VAT change end:
332 l_cr_rec.anticipated_clearing_date := p_anticipated_clearing_date;
333
334 l_cr_rec.global_attribute1 := p_global_attribute1;
335 l_cr_rec.global_attribute2 := p_global_attribute2;
336 l_cr_rec.global_attribute3 := p_global_attribute3;
337 l_cr_rec.global_attribute4 := p_global_attribute4;
338 l_cr_rec.global_attribute5 := p_global_attribute5;
339 l_cr_rec.global_attribute6 := p_global_attribute6;
340 l_cr_rec.global_attribute7 := p_global_attribute7;
341 l_cr_rec.global_attribute8 := p_global_attribute8;
342 l_cr_rec.global_attribute9 := p_global_attribute9;
343 l_cr_rec.global_attribute10 := p_global_attribute10;
344 l_cr_rec.global_attribute11 := p_global_attribute11;
345 l_cr_rec.global_attribute12 := p_global_attribute12;
346 l_cr_rec.global_attribute13 := p_global_attribute13;
347 l_cr_rec.global_attribute14 := p_global_attribute14;
348 l_cr_rec.global_attribute15 := p_global_attribute15;
349 l_cr_rec.global_attribute16 := p_global_attribute16;
350 l_cr_rec.global_attribute17 := p_global_attribute17;
351 l_cr_rec.global_attribute18 := p_global_attribute18;
352 l_cr_rec.global_attribute19 := p_global_attribute19;
353 l_cr_rec.global_attribute20 := p_global_attribute20;
354 l_cr_rec.global_attribute_category := p_global_attribute_category;
355 l_cr_rec.legal_entity_id := p_legal_entity_id;
356 IF PG_DEBUG in ('Y', 'C') THEN
357 arp_debug.debug(' *********AFTER UPDATE CR record: tax_rate' || TO_CHAR(l_cr_rec.tax_rate));
358 END IF;
359
360 --Start bug 7422644, get the related reference customer information
361 IF p_reference_type = 'RECEIPT' AND p_reference_id IS NOT NULL THEN
362 BEGIN
363 select pay_from_customer,
364 customer_bank_account_id,
365 customer_site_use_id,
366 payment_server_order_num,
367 approval_code
368 into l_cr_rec.pay_from_customer,
369 l_cr_rec.customer_bank_account_id,
370 l_cr_rec.customer_site_use_id,
371 l_cr_rec.payment_server_order_num,
372 l_cr_rec.approval_code
373 from ar_cash_receipts
374 where cash_receipt_id = p_reference_id;
375
376 EXCEPTION
377 WHEN no_data_found THEN
378 FND_MESSAGE.Set_Name('AR', 'AR_RAPI_REFERENCE_ID_INVALID');
379 APP_EXCEPTION.Raise_Exception;
380 END;
381
382 END IF;
383 --End bug 7422644, get the related reference customer information
384
385 arp_cash_receipts_pkg.update_p(l_cr_rec);
386
387
388 -- update cash receipt history record:
389
390 l_crh_rec.amount := p_amount;
391 l_crh_rec.acctd_amount := l_acctd_amount;
392 /* Bug fix 2561342
393 The GL Date of the current record should not be updated in the
394 CRH table. Commented out NOCOPY the following line.
395 l_crh_rec.gl_date := p_gl_date; */
396 l_crh_rec.trx_date := p_receipt_date;
397 l_crh_rec.account_code_combination_id := l_ccid;
398
399 arp_cr_history_pkg.update_p(l_crh_rec);
400
401
402 -- update distributions table
403 -- Will only want to do this if the amount of the receipt
404 -- has changed. Update of the amount is not permitted
405 -- if the receipt has changed states, is posted etc.
406
407 IF l_amount_changed_flag THEN
408
409 /* Bug 1301583 : lock ar_distribution row only if an update
410 needs to be done
411
412 Bug 1494541 : lock the row before setting new values for
413 l_dist_rec fields
414 */
415
416 arp_distributions_pkg.nowaitlock_fetch_pk(
417 l_crh_rec.cash_receipt_history_id,
418 'CRH',
419 l_source_type,
420 l_dist_rec);
421
422 l_dist_rec.code_combination_id:= l_ccid;
423
424 IF (p_amount < 0) THEN
425 l_dist_rec.amount_dr := NULL;
426 l_dist_rec.amount_cr := - p_amount;
427 ELSE
428 l_dist_rec.amount_dr := p_amount;
429 l_dist_rec.amount_cr := NULL;
430 END IF;
431
432 IF (l_acctd_amount < 0) THEN
433 l_dist_rec.acctd_amount_dr := NULL;
434 l_dist_rec.acctd_amount_cr := - l_acctd_amount;
435 ELSE
436 l_dist_rec.acctd_amount_dr := l_acctd_amount;
437 l_dist_rec.acctd_amount_cr := NULL;
438 END IF;
439
440 arp_distributions_pkg.update_p(l_dist_rec);
441
442 /* need to insert records into the MRC table. Calling new
443 mrc engine */
444
445 ar_mrc_engine2.maintain_mrc_data2(
446 p_event_mode => 'UPDATE',
447 p_table_name => 'AR_DISTRIBUTIONS',
448 p_mode => 'SINGLE',
449 p_key_value => l_dist_rec.line_id,
450 p_row_info => l_dist_rec);
451
452 END IF;
453
454 -- update misc distribution records if necessary
455 /* Bugfix 2753644. Passed p_gl_tax_acct as parameter to the
456 procedure update_misc_dist */
457
458 arp_proc_rct_util.update_misc_dist(
459 p_cash_receipt_id,
460 p_amount,
461 l_acctd_amount,
462 l_amount_changed_flag,
463 p_distribution_set_id,
464 p_receivables_trx_id,
465 l_old_distribution_set_id,
466 l_old_receivables_trx_id,
467 p_gl_date,
468 l_gl_date_changed_flag,
469 p_currency_code,
470 p_exchange_rate,
471 p_receipt_date,
472 l_receipt_date_changed_flag,
473 p_gl_tax_acct);
474
475 -- Check if Misc Receipt has been posted before calling accounting entry library
476 /* Bug 3911642 - check for unposted rows should be in
477 ar_misc_cash_distributions as it is this accounting that is deleted/
478 recreated. */
479 SELECT count(*)
480 INTO l_unposted_count
481 FROM ar_misc_cash_distributions
482 WHERE cash_receipt_id = p_cash_receipt_id
483 AND posting_control_id = -3
484 AND reversal_gl_date IS NULL;
485
486 -- 941243 Do not call accounting routine if receipt amount has changed (it
487 -- has already done in update_misc_dist
488
489 IF ( (l_unposted_count > 0) AND (l_amount_changed_flag = FALSE) ) THEN
490
491 -- Do not call accounting routine if receipt is 0 and needs to
492 -- be reversed as there are no accouting entries when receipt is
493 -- 0
494
495 IF ((p_reversal_date IS NULL) AND (p_amount <> 0) ) THEN
496
497 -- Call accounting entry library begins
498 IF PG_DEBUG in ('Y', 'C') THEN
499 arp_debug.debug( 'Update Misc Cash Receipt start () +');
500 END IF;
501
502 l_ae_doc_rec.document_type := 'RECEIPT';
503 l_ae_doc_rec.document_id := l_cr_rec.cash_receipt_id;
504 l_ae_doc_rec.accounting_entity_level := 'ONE';
505 l_ae_doc_rec.source_table := 'MCD';
506 l_ae_doc_rec.source_id := '';
507 l_ae_doc_rec.gl_tax_acct := p_gl_tax_acct; /* Bug fix 2300268 */
508
509 -- calling accounting entry library
510
511 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
512 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
513
514 IF PG_DEBUG in ('Y', 'C') THEN
515 arp_debug.debug( 'Update Misc Cash Receipt start () -');
516 END IF;
517 END IF;
518 END IF;
519
520 -- check if receipt needs to be rate-adjusted:
521
522 IF (p_rate_adjust_gl_date IS NOT NULL) THEN
523 arp_proc_rct_util.rate_adjust(
524 p_cash_receipt_id,
525 p_rate_adjust_gl_date,
526 p_new_exchange_date,
527 p_new_exchange_rate,
528 p_new_exchange_rate_type,
529 l_cr_rec.exchange_date,
530 l_cr_rec.exchange_rate,
531 l_cr_rec.exchange_rate_type,
532 p_gain_loss,
533 p_exchange_rate_attr_cat,
534 p_exchange_rate_attr1,
535 p_exchange_rate_attr2,
536 p_exchange_rate_attr3,
537 p_exchange_rate_attr4,
538 p_exchange_rate_attr5,
539 p_exchange_rate_attr6,
540 p_exchange_rate_attr7,
541 p_exchange_rate_attr8,
542 p_exchange_rate_attr9,
543 p_exchange_rate_attr10,
544 p_exchange_rate_attr11,
545 p_exchange_rate_attr12,
546 p_exchange_rate_attr13,
547 p_exchange_rate_attr14,
548 p_exchange_rate_attr15);
549 END IF;
550
551 -- check if receipt needs to be reversed:
552
553 IF (p_reversal_date IS NOT NULL AND
554 l_cr_rec.reversal_date IS NULL) THEN
555 IF PG_DEBUG in ('Y', 'C') THEN
556 arp_debug.debug( 'Regular Reversal required.');
557 END IF;
558
559 arp_reverse_receipt.reverse(
560 l_cr_rec.cash_receipt_id,
561 p_reversal_category,
562 p_reversal_gl_date,
563 p_reversal_date,
564 p_reversal_reason_code,
565 p_reversal_comments,
566 NULL, -- clear_batch_id
567 p_attribute_category,
568 p_attribute1,
569 p_attribute2,
570 p_attribute3,
571 p_attribute4,
572 p_attribute5,
573 p_attribute6,
574 p_attribute7,
575 p_attribute8,
576 p_attribute9,
577 p_attribute10,
578 p_attribute11,
579 p_attribute12,
580 p_attribute13,
581 p_attribute14,
582 p_attribute15,
583 p_form_name,
584 p_form_version,
585 l_rev_crh_id);
586 --
587 -- VAT: reversal is done in ARREREVB.pls: arp_reverse_receipt.reverse
588
589 END IF;
590
591
592 -- update batch status
593
594 IF (p_batch_id IS NOT NULL) THEN
595 arp_rw_batches_check_pkg.update_batch_status(
596 p_batch_id);
597 END IF;
598
599 -- determine receipt's new state and status and return it to form:
600 -- Bug no 968903 SRAJASEK Modified the sql statement to retrieve the data
601 -- from the base tables rather than the ar_cash_receipt_v view for
602 -- performance reasons
603
604 /* SELECT receipt_status,
605 receipt_status_dsp,
606 state,
607 state_dsp
608 INTO p_new_status,
609 p_new_status_dsp,
610 p_new_state,
611 p_new_state_dsp
612 FROM AR_CASH_RECEIPTS_V
613 WHERE cash_receipt_id = p_cash_receipt_id; */
614
615 SELECT cr.status,
616 l_cr_status.meaning,
617 crh_current.status ,
618 l_crh_status.meaning
619 INTO p_new_status,
620 p_new_status_dsp,
621 p_new_state,
622 p_new_state_dsp
623 FROM
624 ar_cash_receipt_history crh_current,
625 ar_cash_receipts cr,
626 ar_lookups l_cr_status,
627 ar_lookups l_crh_status
628 WHERE
629 cr.cash_receipt_id = p_cash_receipt_id
630 AND l_cr_status.lookup_type = 'CHECK_STATUS'
631 AND l_cr_status.lookup_code = cr.status
632 AND l_crh_status.lookup_type = 'RECEIPT_CREATION_STATUS'
633 AND l_crh_status.lookup_code = crh_current.status
634 AND crh_current.cash_receipt_id = cr.cash_receipt_id
635 AND crh_current.current_record_flag = 'Y';
636
637
638 EXCEPTION
639 WHEN OTHERS THEN
640 IF PG_DEBUG in ('Y', 'C') THEN
641 arp_debug.debug('EXCEPTION: arp_process_misc_receipts.update_misc_receipt');
642 END IF;
643 RAISE;
644
645 IF PG_DEBUG in ('Y', 'C') THEN
646 arp_debug.debug('arp_process_misc_receipts.update_misc_receipt()-');
647 END IF;
648
649 END update_misc_receipt;
650
651
652 /*===========================================================================+
653 | PROCEDURE |
654 | insert_misc_receipt |
655 | |
656 | DESCRIPTION |
657 | Creates a new misc receipt |
658 | |
659 | SCOPE - PRIVATE |
660 | |
661 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
662 | |
663 | ARGUMENTS |
664 | IN: |
665 | OUT: |
666 | |
667 | RETURNS |
668 | |
669 | NOTES |
670 | |
671 | MODIFICATION HISTORY |
672 | |
673 | 19-SEP-95 OSTEINME created |
674 | 13-NOV-96 OSTEINME added parameter anticipated_clearing_date to |
675 | insert, update, and lock procedures for CE |
676 | enhancement. |
677 | NOTE: This version of the file is not longer |
678 | compatible with 10.6 and prod15! |
679 | 04-DEC-97 KLAWRANC Bug #590256. Modified call to |
680 | calc_acctd_amount. Now passes NULL for the |
681 | currency code parameter, therefore the acctd |
682 | amount will be calculated based on the |
683 | functional currency. |
684 | 21-MAY-98 KTANG For all calls to calc_acctd_amount which |
685 | calculates header accounted amounts, if the |
686 | exchange_rate_type is not user, call |
687 | gl_currency_api.convert_amount instead. This |
688 | is for triangulation. |
689 | |
690 | 27-NOV-98 GJWANG Added parameter tax_rate when insert |
691 | 20-MAY-05 J Beckett Added p_legal_entity_id for R12 LE uptake |
692 | 04-JAN-07 M Raymond 5728628 - Added logic to default LE if
693 | it is passed in as null
694 +===========================================================================*/
695
696
697 PROCEDURE insert_misc_receipt(
698 p_currency_code IN VARCHAR2,
699 p_amount IN NUMBER,
700 p_receivables_trx_id IN NUMBER,
701 p_misc_payment_source IN VARCHAR2,
702 p_receipt_number IN VARCHAR2,
703 p_receipt_date IN DATE,
704 p_gl_date IN DATE,
705 p_comments IN VARCHAR2,
706 p_exchange_rate_type IN VARCHAR2,
707 p_exchange_rate IN NUMBER,
708 p_exchange_date IN DATE,
709 p_batch_id IN NUMBER,
710 p_attribute_category IN VARCHAR2,
711 p_attribute1 IN VARCHAR2,
712 p_attribute2 IN VARCHAR2,
713 p_attribute3 IN VARCHAR2,
714 p_attribute4 IN VARCHAR2,
715 p_attribute5 IN VARCHAR2,
716 p_attribute6 IN VARCHAR2,
717 p_attribute7 IN VARCHAR2,
718 p_attribute8 IN VARCHAR2,
719 p_attribute9 IN VARCHAR2,
720 p_attribute10 IN VARCHAR2,
721 p_attribute11 IN VARCHAR2,
722 p_attribute12 IN VARCHAR2,
723 p_attribute13 IN VARCHAR2,
724 p_attribute14 IN VARCHAR2,
725 p_attribute15 IN VARCHAR2,
726 p_remittance_bank_account_id IN NUMBER,
727 p_deposit_date IN DATE,
728 p_receipt_method_id IN NUMBER,
729 p_doc_sequence_value IN NUMBER,
730 p_doc_sequence_id IN NUMBER,
731 p_distribution_set_id IN NUMBER,
732 p_reference_type IN VARCHAR2,
733 p_reference_id IN NUMBER,
734 p_vat_tax_id IN NUMBER,
735 p_ussgl_transaction_code IN VARCHAR2,
736 p_anticipated_clearing_date IN DATE,
737 --
738 -- ******* Global Flexfield parameters *******
739 --
740 p_global_attribute1 IN VARCHAR2,
741 p_global_attribute2 IN VARCHAR2,
742 p_global_attribute3 IN VARCHAR2,
743 p_global_attribute4 IN VARCHAR2,
744 p_global_attribute5 IN VARCHAR2,
745 p_global_attribute6 IN VARCHAR2,
746 p_global_attribute7 IN VARCHAR2,
747 p_global_attribute8 IN VARCHAR2,
748 p_global_attribute9 IN VARCHAR2,
749 p_global_attribute10 IN VARCHAR2,
750 p_global_attribute11 IN VARCHAR2,
751 p_global_attribute12 IN VARCHAR2,
752 p_global_attribute13 IN VARCHAR2,
753 p_global_attribute14 IN VARCHAR2,
754 p_global_attribute15 IN VARCHAR2,
755 p_global_attribute16 IN VARCHAR2,
756 p_global_attribute17 IN VARCHAR2,
757 p_global_attribute18 IN VARCHAR2,
758 p_global_attribute19 IN VARCHAR2,
759 p_global_attribute20 IN VARCHAR2,
760 p_global_attribute_category IN VARCHAR2,
761 p_cr_id OUT NOCOPY NUMBER,
762 p_row_id OUT NOCOPY VARCHAR2,
763 --
764 p_form_name IN varchar2,
765 p_form_version IN varchar2,
766 p_tax_rate IN NUMBER,
767 p_gl_tax_acct IN VARCHAR2 , /* Bug fix 2300268 */
768 p_crh_id OUT NOCOPY NUMBER, /* Bug fix 2742388 */
769 p_legal_entity_id IN NUMBER,
770 p_payment_trxn_extension_id IN ar_cash_receipts.payment_trxn_extension_id%TYPE,
771 p_postmark_date IN DATE /* Bug Fix 13084383 */
772 ) IS
773 l_creation_status ar_cash_receipt_history.status%TYPE;
774 l_cr_rec ar_cash_receipts%ROWTYPE;
775 l_crh_rec ar_cash_receipt_history%ROWTYPE;
776 l_ccid ar_cash_receipt_history.account_code_combination_id%TYPE;
777 l_source_type ar_distributions.source_type%TYPE;
778 l_override_remit_account_flag
779 ar_receipt_method_accounts.override_remit_account_flag%TYPE;
780 l_acctd_amount ar_cash_receipt_history.acctd_amount%TYPE;
781 l_dummy NUMBER;
782 l_ae_doc_rec l_ae_doc_rec_type;
783 l_called_from_api varchar2(1);
784 l_legal_entity_id NUMBER;
785
786 BEGIN
787
788 IF PG_DEBUG in ('Y', 'C') THEN
789 arp_debug.debug('arp_process_misc_receipts.insert_misc_receipt()+');
790 END IF;
791
792 -- check if calling form is compatible with entity handler
793
794 -- ??????
795
796 -- receipt record needs to be validated:
797
798 -- val_insert_cr_rec(p_cr_rec); ????
799
800 -- lock related records:
801
802 -- ??????
803
804 --Add for bug 12804388
805 IF (mo_global.get_current_org_id <> arp_standard.sysparm.org_id
806 AND mo_global.get_access_mode = 'S')THEN
807 arp_standard.INIT_STANDARD(mo_global.get_current_org_id);
808 arp_global.INIT_GLOBAL(mo_global.get_current_org_id);
809 END IF;
810
811 -- determine creation state (approved, confirmed, remitted, cleared)
812 -- of receipt based on payment method, as well as code combination
813 -- id's. Also get set of books id.
814
815 arp_cr_util.get_creation_info(p_receipt_method_id,
816 p_remittance_bank_account_id,
817 l_creation_status,
818 l_source_type,
819 l_ccid,
820 l_override_remit_account_flag);
821
822 IF PG_DEBUG in ('Y', 'C') THEN
823 arp_debug.debug( 'Creation status = ' || l_creation_status);
824 arp_debug.debug( 'Source Type = ' || l_source_type);
825 arp_debug.debug( 'ccid = ' || l_ccid);
826 END IF;
827
828 -- create ar_cash_receipt record:
829
830 --APANDIT:get the addln information if the reference is a RECEIPT
831 --this is added for the credit card refund functionality.
832
833 IF p_reference_type = 'RECEIPT' AND
834 p_reference_id IS NOT NULL
835 THEN
836 BEGIN
837 select pay_from_customer,
838 customer_bank_account_id,
839 customer_site_use_id,
840 payment_server_order_num,
841 approval_code
842 into l_cr_rec.pay_from_customer,
843 l_cr_rec.customer_bank_account_id,
844 l_cr_rec.customer_site_use_id,
845 l_cr_rec.payment_server_order_num,
846 l_cr_rec.approval_code
847 from ar_cash_receipts
848 where cash_receipt_id = p_reference_id;
849
850
851 EXCEPTION
852 WHEN no_data_found THEN
853 FND_MESSAGE.Set_Name('AR', 'AR_RAPI_REFERENCE_ID_INVALID');
854 APP_EXCEPTION.Raise_Exception;
855 END;
856
857 END IF;
858
859 /* Bug 4112494 - Get customer details for CM refund */
860 IF p_reference_type = 'CREDIT_MEMO' AND
861 p_reference_id IS NOT NULL
862 THEN
863 BEGIN
864 select bill_to_customer_id,
865 customer_bank_account_id,
866 bill_to_site_use_id
867 into l_cr_rec.pay_from_customer,
868 l_cr_rec.customer_bank_account_id,
869 l_cr_rec.customer_site_use_id
870 from ra_customer_trx
871 where customer_trx_id = p_reference_id;
872
873
874 EXCEPTION
875 WHEN no_data_found THEN
876 FND_MESSAGE.Set_Name('AR', 'AR_RAPI_REFERENCE_ID_INVALID');
877 APP_EXCEPTION.Raise_Exception;
878 END;
879
880 END IF;
881
882 /* 5728628 - default LE if parameter is passed as null */
883 IF p_legal_entity_id IS NULL
884 THEN
885 l_legal_entity_id := ar_receipt_lib_pvt.get_legal_entity(
886 p_remittance_bank_account_id);
887 IF PG_DEBUG in ('Y', 'C') THEN
888 arp_debug.debug('p_legal_entity_id is NULL, defaulting from ar_receipt_lib_pvt');
889 arp_debug.debug('l_legal_entity_id = ' || l_legal_entity_id);
890 END IF;
891 ELSE
892 l_legal_entity_id := p_legal_entity_id;
893 IF PG_DEBUG in ('Y', 'C') THEN
894 arp_debug.debug('l_legal_entity_id = ' || l_legal_entity_id);
895 END IF;
896 END IF;
897
898 l_cr_rec.amount := p_amount;
899 l_cr_rec.currency_code := p_currency_code;
900 l_cr_rec.receivables_trx_id := p_receivables_trx_id;
901 l_cr_rec.misc_payment_source := p_misc_payment_source;
902 l_cr_rec.status := 'APP';
903 l_cr_rec.type := 'MISC';
904 l_cr_rec.receipt_number := p_receipt_number;
905 l_cr_rec.receipt_date := p_receipt_date;
906 l_cr_rec.comments := p_comments;
907 l_cr_rec.exchange_rate_type := p_exchange_rate_type;
908 l_cr_rec.exchange_rate := p_exchange_rate;
909 l_cr_rec.exchange_date := p_exchange_date;
910 l_cr_rec.attribute_category := p_attribute_category;
911 l_cr_rec.attribute1 := p_attribute1;
912 l_cr_rec.attribute2 := p_attribute2;
913 l_cr_rec.attribute3 := p_attribute3;
914 l_cr_rec.attribute4 := p_attribute4;
915 l_cr_rec.attribute5 := p_attribute5;
916 l_cr_rec.attribute6 := p_attribute6;
917 l_cr_rec.attribute7 := p_attribute7;
918 l_cr_rec.attribute8 := p_attribute8;
919 l_cr_rec.attribute9 := p_attribute9;
920 l_cr_rec.attribute10 := p_attribute10;
921 l_cr_rec.attribute11 := p_attribute11;
922 l_cr_rec.attribute12 := p_attribute12;
923 l_cr_rec.attribute13 := p_attribute13;
924 l_cr_rec.attribute14 := p_attribute14;
925 l_cr_rec.attribute15 := p_attribute15;
926 l_cr_rec.remit_bank_acct_use_id := p_remittance_bank_account_id;
927 l_cr_rec.confirmed_flag := 'Y';
928 l_cr_rec.deposit_date := p_deposit_date;
929 l_cr_rec.receipt_method_id := p_receipt_method_id;
930 l_cr_rec.doc_sequence_value := p_doc_sequence_value;
931 l_cr_rec.doc_sequence_id := p_doc_sequence_id;
932 l_cr_rec.distribution_set_id := p_distribution_set_id;
933 l_cr_rec.override_remit_account_flag := l_override_remit_account_flag;
934 l_cr_rec.reference_id := p_reference_id;
935 l_cr_rec.reference_type := p_reference_type;
936 l_cr_rec.vat_tax_id := p_vat_tax_id;
937 l_cr_rec.ussgl_transaction_code := p_ussgl_transaction_code;
938 --VAT change
939 l_cr_rec.tax_rate := p_tax_rate;
940 --
941 l_cr_rec.anticipated_clearing_date := p_anticipated_clearing_date;
942
943
944 l_cr_rec.global_attribute1 := p_global_attribute1;
945 l_cr_rec.global_attribute2 := p_global_attribute2;
946 l_cr_rec.global_attribute3 := p_global_attribute3;
947 l_cr_rec.global_attribute4 := p_global_attribute4;
948 l_cr_rec.global_attribute5 := p_global_attribute5;
949 l_cr_rec.global_attribute6 := p_global_attribute6;
950 l_cr_rec.global_attribute7 := p_global_attribute7;
951 l_cr_rec.global_attribute8 := p_global_attribute8;
952 l_cr_rec.global_attribute9 := p_global_attribute9;
953 l_cr_rec.global_attribute10 := p_global_attribute10;
954 l_cr_rec.global_attribute11 := p_global_attribute11;
955 l_cr_rec.global_attribute12 := p_global_attribute12;
956 l_cr_rec.global_attribute13 := p_global_attribute13;
957 l_cr_rec.global_attribute14 := p_global_attribute14;
958 l_cr_rec.global_attribute15 := p_global_attribute15;
959 l_cr_rec.global_attribute16 := p_global_attribute16;
960 l_cr_rec.global_attribute17 := p_global_attribute17;
961 l_cr_rec.global_attribute18 := p_global_attribute18;
962 l_cr_rec.global_attribute19 := p_global_attribute19;
963 l_cr_rec.global_attribute20 := p_global_attribute20;
964 l_cr_rec.global_attribute_category := p_global_attribute_category;
965 l_cr_rec.legal_entity_id := l_legal_entity_id; /* R12 LE uptake */
966 l_cr_rec.payment_trxn_extension_id := p_payment_trxn_extension_id ; /* BICHATTE PAYMENT UPTAKE */
967 l_cr_rec.postmark_date := p_postmark_date; /* Bug Fix 13084383 */
968
969 IF PG_DEBUG in ('Y', 'C') THEN
970 arp_debug.debug( 'Anticipated_clearing_date = ' || p_anticipated_clearing_date);
971 END IF;
972
973 arp_cash_receipts_pkg.insert_p(l_cr_rec);
974 p_cr_id := l_cr_rec.cash_receipt_id; -- return cash receipt id
975
976 -- get the ROWID out NOCOPY parameter:
977
978 SELECT rowid
979 INTO p_row_id
980 FROM ar_cash_receipts
981 WHERE cash_receipt_id = l_cr_rec.cash_receipt_id;
982
983 -- determine accounted amount
984 -- Changes for triangulation: If exchange rate type is not user, call
985 -- GL API to calculate accounted amount
986 IF (p_exchange_rate_type = 'User') THEN
987 arp_util.calc_acctd_amount( NULL,
988 NULL,
989 NULL,
990 l_cr_rec.exchange_rate,
991 '+',
992 l_cr_rec.amount,
993 l_acctd_amount,
994 0,
995 l_dummy,
996 l_dummy,
997 l_dummy);
998 ELSE
999 l_acctd_amount := gl_currency_api.convert_amount(
1000 arp_global.set_of_books_id,
1001 l_cr_rec.currency_code,
1002 l_cr_rec.exchange_date,
1003 l_cr_rec.exchange_rate_type,
1004 l_cr_rec.amount);
1005 END IF;
1006
1007 -- create related misc receipt history record
1008
1009
1010 arp_proc_rct_util.insert_crh_rec(
1011 l_cr_rec,
1012 l_cr_rec.amount,
1013 l_acctd_amount,
1014 NULL,
1015 NULL,
1016 p_gl_date,
1017 l_creation_status,
1018 p_batch_id,
1019 l_ccid,
1020 NULL,
1021 l_crh_rec);
1022
1023 /* Bug fix 2742388 */
1024 p_crh_id := l_crh_rec.cash_receipt_history_id;
1025
1026 arp_proc_rct_util.insert_dist_rec(
1027 l_cr_rec.amount,
1028 l_acctd_amount,
1029 l_crh_rec.cash_receipt_history_id,
1030 l_source_type,
1031 l_ccid );
1032
1033 -- create misc distribution records if necessary:
1034
1035 arp_proc_rct_util.insert_misc_dist(
1036 l_cr_rec.cash_receipt_id,
1037 p_gl_date,
1038 p_amount,
1039 p_currency_code,
1040 p_exchange_rate,
1041 l_acctd_amount,
1042 p_receipt_date,
1043 p_receivables_trx_id,
1044 p_distribution_set_id,
1045 p_ussgl_transaction_code);
1046
1047 -- Call accounting entry library begins
1048 -- added code to check whether amount is 0
1049
1050 /* Bug 2272461
1051 The MISCCASH record is not created in ar_distributions table
1052 if the receipt amount is zero. Commented out NOCOPY the IF condition.
1053 If (p_amount <> 0) THEN */
1054
1055 IF PG_DEBUG in ('Y', 'C') THEN
1056 arp_debug.debug( 'Create Misc Cash Receipt start () +');
1057 END IF;
1058
1059 l_ae_doc_rec.document_type := 'RECEIPT';
1060 l_ae_doc_rec.document_id := l_cr_rec.cash_receipt_id;
1061 l_ae_doc_rec.accounting_entity_level := 'ONE';
1062 l_ae_doc_rec.source_table := 'MCD';
1063 l_ae_doc_rec.source_id := '';
1064 l_ae_doc_rec.gl_tax_acct := p_gl_tax_acct; /* Bug fix 2300268 */
1065
1066 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1067
1068 IF PG_DEBUG in ('Y', 'C') THEN
1069 arp_debug.debug( 'Create Misc Cash Receipt start () -');
1070 END IF;
1071 /*END IF; */
1072
1073 /* Bug fix 4910860 */
1074 IF nvl(p_form_name,'RAPI') = 'RAPI' THEN
1075 l_called_from_api := 'Y';
1076 ELSE
1077 l_called_from_api := 'N';
1078 END IF;
1079 arp_balance_check.Check_Recp_Balance(l_cr_rec.cash_receipt_id,NULL,l_called_from_api);
1080
1081 -- update batch status
1082
1083 IF (p_batch_id IS NOT NULL) THEN
1084 arp_rw_batches_check_pkg.update_batch_status(
1085 p_batch_id);
1086 END IF;
1087
1088 IF PG_DEBUG in ('Y', 'C') THEN
1089 arp_debug.debug('arp_process_misc_receipts.insert_misc_receipt()-');
1090 END IF;
1091
1092 EXCEPTION
1093 WHEN OTHERS THEN
1094 IF PG_DEBUG in ('Y', 'C') THEN
1095 arp_debug.debug('Exception in insert_misc_receipt');
1096 END IF;
1097 RAISE;
1098
1099 END insert_misc_receipt;
1100
1101
1102
1103 END ARP_PROCESS_MISC_RECEIPTS;