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