[Home] [Help]
PACKAGE BODY: APPS.ARP_PROC_RCT_UTIL
Source
1 PACKAGE BODY ARP_PROC_RCT_UTIL AS
2 /* $Header: ARRURGWB.pls 120.29 2007/10/04 13:38:57 spdixit ship $ */
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4 SUBTYPE l_ae_doc_rec_type IS arp_acct_main.ae_doc_rec_type ;
5
6 /* =======================================================================
7 | Global Data Types
8 * ======================================================================*/
9
10 -- ***************** BEGIN Private Procedures: **********************
11 -- ***************** END Private Procedures: **********************
12
13 FUNCTION revision RETURN VARCHAR2 IS
14 BEGIN
15
16 RETURN '$Revision: 120.29 $';
17
18 END revision;
19
20
21 /*===========================================================================+
22 | PROCEDURE |
23 | insert_ps_rec_cash |
24 | |
25 | DESCRIPTION |
26 | Inserts a payment schedule record for a cash receipt |
27 | |
28 | SCOPE - PRIVATE |
29 | |
30 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
31 | |
32 | ARGUMENTS |
33 | IN: |
34 | OUT: |
35 | |
36 | RETURNS |
37 | |
38 | NOTES |
39 | |
40 | MODIFICATION HISTORY |
41 | |
42 | 8-SEP-95 OSTEINME created |
43 | 12-JUL-96 OSTEINME now populates gl_date_closed with 12-31-4712 |
44 | to avoid problems if DB trigger is not |
45 | installed. Same for actual_date_closed. |
46 | |
47 +===========================================================================*/
48
49
50 Procedure insert_ps_rec_cash(
51 p_cr_rec IN ar_cash_receipts%ROWTYPE,
52 p_gl_date IN DATE,
53 p_maturity_date IN DATE,
54 p_acctd_amount IN
55 ar_payment_schedules.acctd_amount_due_remaining%TYPE,
56 p_ps_id OUT NOCOPY
57 ar_payment_schedules.payment_schedule_id%TYPE
58 ) IS
59
60 l_ps_rec ar_payment_schedules%ROWTYPE;
61 l_ps_id ar_payment_schedules.payment_schedule_id%TYPE;
62
63 l_status ar_cash_receipt_history.status%TYPE;
64 BEGIN
65
66 IF PG_DEBUG in ('Y', 'C') THEN
67 arp_standard.debug('arp_process_rct_util.insert_ps_rec_cash()+');
68 END IF;
69
70 -- fill record columns with data from cash receipt:
71
72 l_ps_rec.due_date := p_maturity_date;
73 l_ps_rec.gl_date := p_gl_date;
74 l_ps_rec.amount_due_original := -p_cr_rec.amount;
75 l_ps_rec.amount_due_remaining := -p_cr_rec.amount;
76 l_ps_rec.acctd_amount_due_remaining := -p_acctd_amount;
77 l_ps_rec.number_of_due_dates := 1;
78 l_ps_rec.status := 'OP';
79 l_ps_rec.invoice_currency_code := p_cr_rec.currency_code;
80 l_ps_rec.class := 'PMT';
81 l_ps_rec.cust_trx_type_id := NULL;
82 l_ps_rec.customer_id := p_cr_rec.pay_from_customer;
83 l_ps_rec.customer_site_use_id := p_cr_rec.customer_site_use_id;
84 l_ps_rec.cash_receipt_id := p_cr_rec.cash_receipt_id;
85 l_ps_rec.associated_cash_receipt_id := p_cr_rec.cash_receipt_id;
86 l_ps_rec.gl_date_closed := TO_DATE('12/31/4712','MM/DD/YYYY');
87 l_ps_rec.actual_date_closed := TO_DATE('12/31/4712','MM/DD/YYYY');
88 l_ps_rec.amount_applied := NULL;
89 l_ps_rec.exchange_rate_type := p_cr_rec.exchange_rate_type;
90 l_ps_rec.exchange_rate := p_cr_rec.exchange_rate;
91 l_ps_rec.exchange_date := p_cr_rec.exchange_date;
92 l_ps_rec.trx_number := p_cr_rec.receipt_number;
93 l_ps_rec.trx_date := p_cr_rec.receipt_date;
94
95 /* bug 5569488, set confirmed flag to N if the status is APPROVED */
96 select status
97 into l_status
98 from ar_cash_receipt_history
99 where cash_receipt_id = p_cr_rec.cash_receipt_id
100 and current_record_flag = 'Y' ;
101
102 IF l_status = 'APPROVED' THEN
103 l_ps_rec.receipt_confirmed_flag := 'N' ;
104 END IF ;
105
106 --insert record into payment schedule table:
107
108 arp_ps_pkg.insert_p(l_ps_rec, l_ps_id);
109
110 p_ps_id := l_ps_id;
111
112 IF PG_DEBUG in ('Y', 'C') THEN
113 arp_standard.debug('arp_process_rct_util.insert_ps_rec_cash()-');
114 END IF;
115
116 EXCEPTION
117 WHEN OTHERS THEN
118 IF PG_DEBUG in ('Y', 'C') THEN
119 arp_standard.debug('EXCEPTION: arp_process_rct_util.insert_ps_rec_cash()');
120 END IF;
121 RAISE;
122
123 END insert_ps_rec_cash;
124
125
126 /*===========================================================================+
127 | PROCEDURE |
128 | insert_crh_rec |
129 | |
130 | DESCRIPTION |
131 | Creates a new record in AR_CASH_RECEIPT_HISTORY for a new cash or |
132 | misc receipt. |
133 | |
134 | SCOPE - PRIVATE |
135 | |
136 | NOTES |
137 | |
138 | MODIFICATION HISTORY |
139 | |
140 | 08-SEP-95 OSTEINME created |
141 | 04-NOV-96 OSTEINME modified for Japan enhancements: |
142 | added new parameters for bank charges |
143 | |
144 +===========================================================================*/
145
146 PROCEDURE insert_crh_rec(
147 p_cr_rec IN ar_cash_receipts%ROWTYPE,
148 p_crh_amount IN ar_cash_receipt_history.amount%TYPE,
149 p_acctd_amount IN ar_cash_receipt_history.acctd_amount%TYPE,
150 p_factor_discount_amount IN
151 ar_cash_receipt_history.factor_discount_amount%TYPE,
152 p_acctd_factor_discount_amount IN
153 ar_cash_receipt_history.acctd_factor_discount_amount%TYPE,
154 p_gl_date IN DATE,
155 p_creation_status IN VARCHAR2,
156 p_batch_id IN ar_cash_receipt_history.batch_id%TYPE,
157 p_ccid IN
158 ar_cash_receipt_history.account_code_combination_id%TYPE,
159 p_bank_charges_ccid IN
160 ar_cash_receipt_history.bank_charge_account_ccid%TYPE,
161 p_crh_rec OUT NOCOPY ar_cash_receipt_history%ROWTYPE
162 ) IS
163
164 l_crh_rec ar_cash_receipt_history%ROWTYPE;
165 l_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
166 l_dummy NUMBER;
167 --Bug#2750340
168 l_xla_ev_rec arp_xla_events.xla_events_type;
169
170 BEGIN
171 arp_standard.debug('arp_process_rct_util.insert_crh_rec()+');
172
173 -- fill record columns with data from cash receipt:
174
175 l_crh_rec.amount := p_crh_amount;
176 l_crh_rec.acctd_amount := p_acctd_amount;
177 l_crh_rec.cash_receipt_id := p_cr_rec.cash_receipt_id;
178 l_crh_rec.factor_flag := 'N';
179
180 /* bug5569488, set the first_posted_record_flag and postable_flag to N,
181 for receipts which requires confirmation, else set to Y. */
182 IF p_creation_status = 'APPROVED' THEN
183 l_crh_rec.first_posted_record_flag := 'N' ;
184 l_crh_rec.postable_flag := 'N' ;
185 ELSE
186 l_crh_rec.first_posted_record_flag := 'Y' ;
187 l_crh_rec.postable_flag := 'Y' ;
188 END IF ;
189
190 l_crh_rec.gl_date := p_gl_date;
191 -- l_crh_rec.postable_flag := 'Y'; -- bug 5569488, commented and replaced above
192 l_crh_rec.status := p_creation_status;
193 l_crh_rec.trx_date := p_cr_rec.receipt_date;
194 l_crh_rec.acctd_factor_discount_amount:= p_acctd_factor_discount_amount;
195 l_crh_rec.factor_discount_amount := p_factor_discount_amount;
196 l_crh_rec.account_code_combination_id := p_ccid;
197 l_crh_rec.batch_id := p_batch_id;
198 l_crh_rec.current_record_flag := 'Y';
199 l_crh_rec.exchange_date := p_cr_rec.exchange_date;
200 l_crh_rec.exchange_rate := p_cr_rec.exchange_rate;
201 l_crh_rec.exchange_rate_type := p_cr_rec.exchange_rate_type;
202 l_crh_rec.gl_posted_date := NULL;
203 l_crh_rec.posting_control_id := -3;
204 l_crh_rec.reversal_cash_receipt_hist_id := NULL;
205 l_crh_rec.reversal_gl_date := NULL;
206 l_crh_rec.reversal_gl_posted_date := NULL;
207 l_crh_rec.reversal_posting_control_id := NULL;
208 l_crh_rec.request_id := NULL;
209 l_crh_rec.program_application_id := NULL;
210 l_crh_rec.program_id := NULL;
211 l_crh_rec.program_update_date := NULL;
212 l_crh_rec.created_from := 'ARRERGW';
213
214 -- populate bank charge ccid only if there is actually a bank
215 -- charge amount:
216
217 IF (p_factor_discount_amount IS NULL) THEN
218 l_crh_rec.bank_charge_account_ccid := NULL;
219 ELSE
220 l_crh_rec.bank_charge_account_ccid := p_bank_charges_ccid;
221 END IF;
222
223 arp_cr_history_pkg.insert_p(l_crh_rec, l_crh_id);
224
225 l_crh_rec.cash_receipt_history_id := l_crh_id;
226
227 p_crh_rec := l_crh_rec; -- return crh record
228
229 --Bug2750340
230 l_xla_ev_rec.xla_from_doc_id := p_cr_rec.cash_receipt_id;
231 l_xla_ev_rec.xla_to_doc_id := p_cr_rec.cash_receipt_id;
232 l_xla_ev_rec.xla_doc_table := 'CRH';
233 l_xla_ev_rec.xla_mode := 'O';
234 l_xla_ev_rec.xla_call := 'B';
235 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
236
237 arp_standard.debug('arp_process_rct_util.insert_crh_rec()-');
238
239 EXCEPTION
240 WHEN OTHERS THEN
241 arp_standard.debug('EXCEPTION: arp_process_rct_util.insert_crh_rec');
242 RAISE;
243
244 END;
245
246
247 /*===========================================================================+
248 | PROCEDURE |
249 | insert_ra_rec_cash |
250 | |
251 | DESCRIPTION |
252 | Creates a new record in AR_RECEIVABLE_APPLICATIONS table for a cash |
253 | receipt. |
254 | |
255 | SCOPE : PRIVATE |
256 | |
257 | NOTES |
258 | |
259 | MODIFICATION HISTORY |
260 | |
261 | 11-SEP-95 OSTEINME created |
262 | 13-MAY-97 KLAWRANC Bug fix #487513. |
263 | Added parameter p_reversal_gl_date. Reversal |
264 | GL Date must be set for all reversed |
265 | receivable applications records. Parameter |
266 | defaults to NULL, so that other packages that|
267 | call this procedure do not need to be |
268 | modified. |
269 | 03-SEP-97 OSTEINME Bug 547165: Changed |
270 | %type to %rowtype |
271 | |
272 | 14-APR-2000 Jani Rautiainen Added parameter p_called_from. This is needed|
273 | in the logic to decide whether first UNAPP |
274 | row is postable or not. In BR scenario when |
275 | Activity Application of Short Term Debt is |
276 | created the UNAPP rows are not postable. |
277 | This is an user requirement for BR. |
278 | The parameter is defaulted to NULL so no |
279 | impact for the existing functionality. |
280 | 03-Sep-2002 Debbie Jancis Modified for MRC trigger replacement |
281 | Added calls to AR_MRC_ENGINE3 for |
282 | for processing inserts into |
283 | AR_RECEIVABLE_APPLICATIONS |
284 | |
285 +===========================================================================*/
286
287 PROCEDURE insert_ra_rec_cash(
288 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
289 p_amount IN ar_cash_receipts.amount%TYPE,
290 p_apply_date IN DATE,
291 p_status IN ar_cash_receipts.status%TYPE,
292 p_acctd_amount IN
293 ar_receivable_applications.acctd_amount_applied_from%TYPE,
294 p_gl_date IN DATE,
295 p_ccid IN
296 ar_receivable_applications.code_combination_id%TYPE,
297 p_payment_schedule_id IN
298 ar_payment_schedules.payment_schedule_id%TYPE,
299 p_application_rule IN ar_receivable_applications.application_rule%TYPE,
300 p_reversal_gl_date IN DATE default null,
301 p_ra_id OUT NOCOPY ar_receivable_applications.receivable_application_id%TYPE,
302 p_called_from IN VARCHAR2 DEFAULT NULL -- jrautiai BR project
303 ) IS
304
305 l_ra_rec ar_receivable_applications%ROWTYPE;
306 l_ra_id ar_receivable_applications.receivable_application_id%TYPE;
307 --BUG#2750340
308 l_xla_ev_rec arp_xla_events.xla_events_type;
309
310 BEGIN
311
312 arp_standard.debug('arp_process_rct_util.insert_ra_rec_cash()+');
313
314 -- create new receivable applications record:
315
316 l_ra_rec.amount_applied := p_amount;
317 l_ra_rec.acctd_amount_applied_from := p_acctd_amount;
318 l_ra_rec.cash_receipt_id := p_cash_receipt_id;
319 l_ra_rec.gl_date := p_gl_date;
320 l_ra_rec.reversal_gl_date := p_reversal_gl_date;
321 l_ra_rec.apply_date := p_apply_date;
322 l_ra_rec.display := 'N';
323 l_ra_rec.application_type := 'CASH';
324 l_ra_rec.payment_schedule_id := p_payment_schedule_id;
325 l_ra_rec.posting_control_id := -3;
326 l_ra_rec.application_rule := p_application_rule;
327
328 /* 14-APR-2000 jrautiai BR implementation
329 * In this BR specific situation the first UNAPP row created is not POSTABLE
330 * see procedure description for more information */
331
332 IF nvl(p_called_from,'NONE') = 'BR_FACTORED_WITH_RECOURSE' THEN -- jrautiai BR project
333 l_ra_rec.postable := 'N';
334 END IF;
335
336 --bug 5298846 For Receipts which require confirmation the following needs to be set
337 IF nvl(p_called_from,'NONE') = 'AUTORECAPI' THEN -- jrautiai BR project
338 l_ra_rec.application_rule:= '97.0';
339 l_ra_rec.confirmed_flag := 'N';
340 END IF;
341
342 -- based on receipt status, set application rule:
343
344 l_ra_rec.status := p_status;
345 l_ra_rec.code_combination_id := p_ccid;
346
347 -- call table handler to insert record
348
349 arp_standard.debug('amount_applied = ' || to_char(l_ra_rec.amount_applied));
350 arp_standard.debug('acctd_amount_applied_from = ' || to_char(l_ra_rec.acctd_amount_applied_from));
351 arp_standard.debug('cash_receipt_id = ' || to_char(l_ra_rec.cash_receipt_id));
352 arp_standard.debug('gl_date = ' || to_char(l_ra_rec.gl_date));
353 arp_standard.debug('apply_date = ' || to_char(l_ra_rec.apply_date));
354 arp_standard.debug('display = ' || l_ra_rec.display);
355 arp_standard.debug('application_type = ' || l_ra_rec.application_type);
356 arp_standard.debug('payment_schedule_id = ' || to_char(l_ra_rec.payment_schedule_id));
357 arp_standard.debug('status = ' || l_ra_rec.status);
358 arp_standard.debug('ccid = ' || to_char(l_ra_rec.code_combination_id));
359 arp_standard.debug('sob id = ' || TO_CHAR(arp_global.set_of_books_id));
360 arp_standard.debug('application_rule = ' || TO_CHAR(l_ra_rec.application_rule));
361 arp_standard.debug('confirmed_flag = ' || TO_CHAR(l_ra_rec.confirmed_flag));
362 arp_standard.debug('p_called_from = ' || TO_CHAR(p_called_from));
363 arp_app_pkg.insert_p(l_ra_rec, l_ra_id);
364
365 p_ra_id := l_ra_id;
366
367 --
368 --Release 11.5 VAT changes, create receivable application accounting
369 --in ar_distributions. Note tax accounting must be created after a call
370 --to this function. It is due to this reason that the app id is returned
371 --as output to the parent procedure to enable a call to the tax accounting
372 --routine
373
374 -- CAll MRC ENGINE for Processing Insert into rec apps.
375 --{BUG#4301323
376 -- ar_mrc_engine3.insert_ra_rec_cash(
377 -- p_ra_id,
378 -- l_ra_rec,
379 -- l_ra_rec.cash_receipt_id,
380 -- p_amount,
381 -- l_ra_rec.payment_schedule_id);
382 --}
383 --BUG#2750340
384 l_xla_ev_rec.xla_from_doc_id := l_ra_id;
385 l_xla_ev_rec.xla_to_doc_id := l_ra_id;
386 l_xla_ev_rec.xla_doc_table := 'APP';
387 l_xla_ev_rec.xla_mode := 'O';
388 l_xla_ev_rec.xla_call := 'B';
389 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
390
391 arp_standard.debug('arp_process_rct_util.insert_ra_rec_cash()-');
392
393 END; -- insert_ra_rec_cash()
394
395
396 /*===========================================================================+
397 | PROCEDURE |
398 | insert_dist_rec |
399 | |
400 | DESCRIPTION |
401 | inserts ar_distributions record for a cash/misc receipt |
402 | |
403 | SCOPE - PRIVATE |
404 | |
405 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
406 | |
407 | ARGUMENTS |
408 | IN: |
409 | OUT: |
410 | |
411 | RETURNS |
412 | |
413 | NOTES |
414 | |
415 | MODIFICATION HISTORY |
416 | |
417 | 19-AUG-95 OSTEINME created |
418 | 05-Jan-98 DJancis added p_cr_id so we can get additional info |
419 | required for 11.5 VAT changes |
420 | |
421 +===========================================================================*/
422
423 PROCEDURE insert_dist_rec(
424 p_amount IN ar_cash_receipts.amount%TYPE,
425 p_acctd_amount IN ar_cash_receipt_history.acctd_amount%TYPE,
426 p_crh_id IN
427 ar_cash_receipt_history.cash_receipt_history_id%TYPE,
428 p_source_type IN ar_distributions.source_type%TYPE,
429 p_ccid IN ar_distributions.code_combination_id%TYPE
430 ) IS
431
432 l_dist_rec ar_distributions%ROWTYPE;
433 l_source_type ar_distributions.source_type%TYPE;
434 l_ccid ar_distributions.code_combination_id%TYPE;
435 l_dummy ar_distributions.line_id%TYPE;
436 l_cr_rec ar_cash_receipts%ROWTYPE;
437 l_crh_rec ar_cash_receipt_history%ROWTYPE;
438 --bug#2750340
439 l_xla_ev_rec arp_xla_events.xla_events_type;
440
441 BEGIN
442 arp_standard.debug('arp_process_rct_util.insert_dist_rec()+');
443
444 arp_standard.debug('-- getting infomation from cash receipt --');
445
446 -- Fetch the history record
447 arp_cr_history_pkg.fetch_p( p_crh_id, l_crh_rec );
448
449 -- Fetch the cash receipt record
450 l_cr_rec.cash_receipt_id := l_crh_rec.cash_receipt_id;
451 arp_cash_receipts_pkg.fetch_p( l_cr_rec );
452
453
454 -- 11.5 VAT changes:
455 l_dist_rec.currency_code := l_cr_rec.currency_code;
456 l_dist_rec.currency_conversion_rate := l_crh_rec.exchange_rate;
457 l_dist_rec.currency_conversion_type := l_crh_rec.exchange_rate_type;
458 l_dist_rec.currency_conversion_date := l_crh_rec.exchange_date;
459 l_dist_rec.third_party_id := l_cr_rec.pay_from_customer;
460 l_dist_rec.third_party_sub_id := l_cr_rec.customer_site_use_id;
461
462
463 l_dist_rec.source_id := p_crh_id;
464 l_dist_rec.source_table := 'CRH';
465 l_dist_rec.source_type := p_source_type;
466 l_dist_rec.code_combination_id:= p_ccid;
467 /* Bug 44188117 : Added the 'or' condition below */
468 IF (p_amount < 0) or (p_amount = 0 and p_acctd_amount < 0) THEN
469 l_dist_rec.amount_dr := NULL;
470 l_dist_rec.amount_cr := - p_amount;
471 l_dist_rec.acctd_amount_dr := NULL;
472 l_dist_rec.acctd_amount_cr := - p_acctd_amount;
473
474 ELSE
475 l_dist_rec.amount_dr := p_amount;
476 l_dist_rec.amount_cr := NULL;
477 l_dist_rec.acctd_amount_dr := p_acctd_amount;
478 l_dist_rec.acctd_amount_cr := NULL;
479
480 END IF;
481 /* Bug No 3635076 JVARKEY */
482 /* Commented out as same code is shifted to the above if loop IF (p_amount < 0) THEN.. */
483 /*IF (p_acctd_amount < 0) THEN
484 l_dist_rec.acctd_amount_dr := NULL;
485 l_dist_rec.acctd_amount_cr := - p_acctd_amount;
486 ELSE
487 l_dist_rec.acctd_amount_dr := p_acctd_amount;
488 l_dist_rec.acctd_amount_cr := NULL;
489 END IF;*/
490
491 arp_distributions_pkg.insert_p(l_dist_rec, l_dummy);
492
493 /* store l_dummy into the rec for use for mrc */
494 l_dist_rec.line_id := l_dummy;
495
496 /* need to insert records into the MRC table. Calling new
497 mrc engine */
498 --{BUG4301323
499 -- ar_mrc_engine2.maintain_mrc_data2(
500 -- p_event_mode => 'INSERT',
501 -- p_table_name => 'AR_DISTRIBUTIONS',
502 -- p_mode => 'SINGLE',
503 -- p_key_value => l_dist_rec.line_id,
504 -- p_row_info => l_dist_rec);
505 --}
506 --BUG#2750340
507 l_xla_ev_rec.xla_from_doc_id := l_crh_rec.cash_receipt_id;
508 l_xla_ev_rec.xla_to_doc_id := l_crh_rec.cash_receipt_id;
509 l_xla_ev_rec.xla_doc_table := 'CRH';
510 l_xla_ev_rec.xla_mode := 'O';
511 l_xla_ev_rec.xla_call := 'B';
512 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
513
514 arp_standard.debug('arp_process_rct_util.insert_dist_rec()-');
515
516 END; -- insert_dist_rec()
517
518
519 /*===========================================================================+
520 | PROCEDURE |
521 | round_mcd_recs |
522 | |
523 | DESCRIPTION |
524 | This function takes care of rounding errors in ar_misc_cash_distr. |
525 | |
526 | SCOPE - PRIVATE |
527 | |
528 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
529 | |
530 | ARGUMENTS |
531 | IN: |
532 | OUT: |
533 | |
534 | RETURNS |
535 | |
536 | NOTES |
537 | |
538 | MODIFICATION HISTORY |
539 | |
540 | 09-OCT-95 OSTEINME created |
541 | |
542 +===========================================================================*/
543
544 PROCEDURE round_mcd_recs(
545 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE
546 ) IS
547
548 l_rounding_diff NUMBER;
549 l_misc_cash_key_value_list gl_ca_utility_pkg.r_key_value_arr; /* MRC */
550
551 BEGIN
552
553 IF PG_DEBUG in ('Y', 'C') THEN
554 arp_standard.debug('arp_process_receipts.round_mcd_recs()+');
555 END IF;
556
557 SELECT ROUND(100 - sum(mcd.percent),3)
558 INTO l_rounding_diff
559 FROM ar_misc_cash_distributions mcd
560 WHERE mcd.cash_receipt_id = p_cash_receipt_id;
561
562 IF (l_rounding_diff <> 0) THEN
563 -- rounding error must be added to first record so that percent
564 -- values add up to 100
565 --
566 IF PG_DEBUG in ('Y', 'C') THEN
567 arp_standard.debug('round_mcd_recs: ' || 'Rounding difference = ' || TO_CHAR(l_rounding_diff));
568 END IF;
569 --
570 /*----------------------------------+
571 | Added bulk collect of misc cash |
572 | distribution id for use in MRC |
573 | engine for trigger replacement |
574 +----------------------------------*/
575
576 UPDATE ar_misc_cash_distributions mcd
577 SET mcd.percent = mcd.percent + l_rounding_diff
578 WHERE cash_receipt_id = p_cash_receipt_id
579 AND ROWNUM =1
580 RETURNING misc_cash_distribution_id
581 BULK COLLECT INTO l_misc_cash_key_value_list;
582 --
583
584 /*---------------------------------+
585 | Calling central MRC library |
586 | for MRC Integration |
587 +---------------------------------*/
588 /*BUG4301323
589 BEGIN
590 ar_mrc_engine.maintain_mrc_data(
591 p_event_mode => 'UPDATE',
592 p_table_name => 'AR_MISC_CASH_DISTRIBUTIONS',
593 p_mode => 'BATCH',
594 p_key_value_list => l_misc_cash_key_value_list);
595 EXCEPTION
596 WHEN OTHERS THEN
597 IF PG_DEBUG in ('Y', 'C') THEN
598 arp_util.debug('round_mcd_recs: ' || 'error updating ar_mc_misc_cash_dists');
599 arp_util.debug('round_mcd_recs: ' || 'SQLCODE = ' || SQLCODE || SQLERRM);
600 END IF;
601 APP_EXCEPTION.RAISE_EXCEPTION;
602 END;
603 */
604 END IF;
605
606 IF PG_DEBUG in ('Y', 'C') THEN
607 arp_standard.debug('arp_process_receipts.round_mcd_recs()-');
608 END IF;
609
610 END round_mcd_recs;
611
612
613 /*===========================================================================+
614 | PROCEDURE |
615 | insert_misc_dist |
616 | |
617 | DESCRIPTION |
618 | Inserts distributions for miscellaneous transactions |
619 | |
620 | SCOPE - PRIVATE |
621 | |
622 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
623 | |
624 | ARGUMENTS |
625 | IN: |
626 | OUT: |
627 | |
628 | RETURNS |
629 | |
630 | NOTES |
631 | |
632 | MODIFICATION HISTORY |
633 | |
634 | 21-SEP-95 OSTEINME created |
635 | 28-SEP-98 K.Murphy Bug #705078. Added code to validate the key |
636 | flex before the MISC distribution is |
637 | created. Checks both the enabled flag and |
638 | From/To dates. |
639 | 30-SEP-98 K.Murphy Cash Management Enhancement: Allow creation |
640 | of Misc Receipts with distribution set. |
641 | Added p_created_from parameter and included |
642 | "default NULL" for p_distribution_set_id. |
643 | Modified code so that the distribution set |
644 | is selected along with the cc id (as in the |
645 | form you can no longer choose a different |
646 | distribution set for a given activity. |
647 | 03-Mar-05 JASSING Added the code to check for the profile |
648 | option 'AR:Disable Receivable Activity |
649 | Balancing Segment Substitution' for the |
650 | misc receipt creation during Credit Card |
651 | Refunds. Bug fix 4025652. |
652 +===========================================================================*/
653
654
655 PROCEDURE insert_misc_dist(
656 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
657 p_gl_date IN ar_cash_receipt_history.gl_date%TYPE,
658 p_amount IN ar_cash_receipts.amount%TYPE,
659 p_currency_code IN ar_cash_receipts.currency_code%TYPE,
660 p_exchange_rate IN ar_cash_receipts.exchange_rate%TYPE,
661 p_acctd_amount IN ar_cash_receipt_history.acctd_amount%TYPE,
662 p_receipt_date IN ar_cash_receipts.receipt_date%TYPE,
663 p_receivables_trx_id IN ar_cash_receipts.receivables_trx_id%TYPE,
664 p_distribution_set_id IN ar_cash_receipts.distribution_set_id%TYPE default NULL,
665 p_ussgl_trx_code IN ar_cash_receipts.ussgl_transaction_code%TYPE default NULL,
666 p_created_from IN ar_misc_cash_distributions.created_from%TYPE default 'ARRERCT'
667 ) IS
668
669 l_trx_code_combination_id ar_receivables_trx.code_combination_id%TYPE;
670 l_distribution_set_id ar_receivables_trx.default_acctg_distribution_set%TYPE;
671 l_dummy ar_misc_cash_distributions.misc_cash_distribution_id%TYPE;
672 l_misc_cash_dist_rec ar_misc_cash_distributions%ROWTYPE;
673 l_misc_cash_key_value_list gl_ca_utility_pkg.r_key_value_arr; /* MRC */
674 l_crh_ccid ar_cash_receipt_history.account_code_combination_id%TYPE; /*Bug fix 4025652 */
675 l_type ar_receivables_trx.type%TYPE; /*4726219 */
676
677 l_xla_ev_rec arp_xla_events.xla_events_type;
678
679 CURSOR c_hist IS
680 SELECT cash_receipt_history_id
681 FROM ar_cash_receipt_history
682 WHERE current_record_flag = 'Y'
683 AND cash_receipt_id = p_cash_receipt_id;
684
685 l_crh_id NUMBER;
686
687 BEGIN
688
689 arp_standard.debug('arp_process_receipts.insert_misc_dist()+');
690
691 arp_standard.debug('ussgl_trx code = ' || p_ussgl_trx_code);
692 -- delete existing distributions records
693
694 /*----------------------------------+
695 | Added bulk collect of misc cash |
696 | distribution id for use in MRC |
697 | engine for trigger replacement |
698 +----------------------------------*/
699
700 DELETE ar_misc_cash_distributions
701 WHERE cash_receipt_id = p_cash_receipt_id
702 RETURNING misc_cash_distribution_id
703 BULK COLLECT INTO l_misc_cash_key_value_list;
704 --
705
706 /*---------------------------------+
707 | Calling central MRC library |
708 | for MRC Integration |
709 +---------------------------------*/
710 /*BUG4301323
711 BEGIN
712 ar_mrc_engine.maintain_mrc_data(
713 p_event_mode => 'DELETE',
714 p_table_name => 'AR_MISC_CASH_DISTRIBUTIONS',
715 p_mode => 'BATCH',
716 p_key_value_list => l_misc_cash_key_value_list);
717 EXCEPTION
718 WHEN OTHERS THEN
719 arp_util.debug('error deleting ar_mc_misc_cash_dists');
720 arp_util.debug('SQLCODE = ' || SQLCODE || SQLERRM);
721 APP_EXCEPTION.RAISE_EXCEPTION;
722 END;
723 */
724 -- check if receipt amount is zero. If yes, don't create any
725 -- distributions
726 -------------------------------------------------------------
727 -- Commented the following 'if' as part of bug fix 868448
728 ----------------------------------------------
729 --IF (p_amount <> 0) THEN
730
731 -- determine if distribution set or single account
732
733 -- Now selecting both the code combination id and the distribution set id.
734 -- Previously you could create a receipt with a different distribution set
735 -- for the choosen activity (i.e. other than the default). This is no
736 -- longer permitted. Cash Management have added the ability to create
737 -- Misc Receipts with distribution sets but we won't require them to pass
738 -- in the distribution set id as se will get it ourselves based on the
739 -- activity.
740 /* Bug4726219 */
741 SELECT rt.code_combination_id,
742 rt.default_acctg_distribution_set,
743 rt.type
744 INTO l_trx_code_combination_id,
745 l_distribution_set_id,
746 l_type
747 FROM ar_receivables_trx rt
748 WHERE rt.type in
749 ('MISCCASH', 'BANK_ERROR', 'CCREFUND', 'CM_REFUND','CC_CHARGEBACK')
750 AND NVL( rt.status, 'A' ) = 'A'
751 AND rt.RECEIVABLES_TRX_ID = p_receivables_trx_id;
752
753
754 IF (l_trx_code_combination_id IS NOT NULL) THEN
755
756 -- Default account ccid exists. Create misc cash distribution
757 -- record with 100% for this account
758
759 /* bug fix 4025652 */
760 select account_code_combination_id
761 into l_crh_ccid
762 from ar_cash_receipt_history
763 where cash_receipt_id = p_cash_receipt_id
764 and current_record_flag = 'Y';
765
766 /* -------------------------------------------------------------------+
767 | Balancing segment of ACTIVITY application should be replaced with |
768 | that of CRH record's CCID. |
769 +--------------------------------------------------------------------*/
770 /* Bug4726219 */
771 IF NVL(FND_PROFILE.value('AR_DISABLE_REC_ACTIVITY_BALSEG_SUBSTITUTION'),
772 'N') = 'N' AND l_type <> 'MISCCASH' THEN
773 arp_util.Substitute_Ccid(
774 p_coa_id => arp_global.chart_of_accounts_id,
775 p_original_ccid => l_trx_code_combination_id,
776 p_subs_ccid => l_crh_ccid,
777 p_actual_ccid => l_misc_cash_dist_rec.code_combination_id );
778 ELSE
779 l_misc_cash_dist_rec.code_combination_id := l_trx_code_combination_id;
780 END IF;
781
782 l_misc_cash_dist_rec.cash_receipt_id := p_cash_receipt_id;
783 l_misc_cash_dist_rec.gl_date := p_gl_date;
784 l_misc_cash_dist_rec.apply_date := p_receipt_date;
785 /* l_misc_cash_dist_rec.code_combination_id:= l_trx_code_combination_id; Bug fix 4025652 */
786 l_misc_cash_dist_rec.percent := 100;
787 l_misc_cash_dist_rec.amount := p_amount;
788 l_misc_cash_dist_rec.acctd_amount := p_acctd_amount;
789 l_misc_cash_dist_rec.posting_control_id := -3;
790 l_misc_cash_dist_rec.created_from := p_created_from;
791 l_misc_cash_dist_rec.set_of_books_id := arp_global.set_of_books_id;
792 OPEN c_hist;
793 FETCH c_hist INTO l_misc_cash_dist_rec.cash_receipt_history_id;
794 CLOSE c_hist;
795
796 -- Bug 2641258: USSGL TRX code not being defaulted
797 l_misc_cash_dist_rec.ussgl_transaction_code := p_ussgl_trx_code;
798
799 -- Bug fix #705078
800 -- Verify that account is valid before doing insert.
801 --
802 -- Using the flex field server APIs to do this ...
803 -- (note also that we are using the same error message
804 -- for each case.
805 --
806 -- Firstly, call fnd_flex_keyval.validate_ccid to populate
807 -- all of the relevant global variables.
808 --
809 IF fnd_flex_keyval.validate_ccid(
810 appl_short_name => 'SQLGL',
811 key_flex_code => 'GL#',
812 structure_number => arp_global.chart_of_accounts_id,
813 combination_id => l_trx_code_combination_id ) THEN
814 -- Secondly, check is the key flex is enabled.
815 -- Fix 1341201, Added rollback
816 IF not fnd_flex_keyval.enabled_flag THEN
817 rollback;
818 FND_MESSAGE.Set_Name('AR', 'AR_GL_ACCOUNT_INVALID');
819 APP_EXCEPTION.Raise_Exception;
820 -- Thirdly, check if the key flex is valid for this date.
821 --
822 ELSIF p_gl_date NOT between nvl(fnd_flex_keyval.start_date,p_gl_date)
823 and nvl(fnd_flex_keyval.end_date,p_gl_date) THEN
824 rollback;
825 FND_MESSAGE.Set_Name('AR', 'AR_GL_ACCOUNT_INVALID');
826 APP_EXCEPTION.Raise_Exception;
827 END IF;
828 END IF;
829
830 arp_misc_cash_dist_pkg.insert_p(l_misc_cash_dist_rec, l_dummy);
831
832
833 --
834 l_xla_ev_rec.xla_from_doc_id := p_cash_receipt_id;
835 l_xla_ev_rec.xla_to_doc_id := p_cash_receipt_id;
836 l_xla_ev_rec.xla_doc_table := 'MCD';
837 l_xla_ev_rec.xla_mode := 'O';
838 l_xla_ev_rec.xla_call := 'D';
839 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
840
841 ELSIF (l_distribution_set_id is not null) THEN
842
843 -- 941243: insert distributions records
844 -- do not create ar_misc_cash_distributions if receipt amount = 0
845 /* Bug fix 2272461
846 When the MISC receipt amount is changed to zero, the MISCCASH accounting
847 Record is not created in ar_distributions table. Commented out NOCOPY the condition
848 which check for the receipt amount before creating the record.
849 IF (p_amount <> 0) THEN */
850
851 create_mcd_recs(p_cash_receipt_id,
852 p_amount,
853 p_acctd_amount,
854 p_exchange_rate,
855 p_currency_code,
856 p_gl_date,
857 p_receipt_date,
858 p_distribution_set_id,
859 p_ussgl_trx_code);
860
861
862 /*END IF; */
863 END IF;
864
865 --END IF; -- p_amount <> 0
866 -------------------------------------------------------------
867 -- Commented out NOCOPY the above 'END IF' as part of bug fix 868448
868 --------------------------------------------------------------
869
870 arp_standard.debug('arp_process_receipts.insert_misc_dist()-');
871
872 END; -- arp_process_receipts.insert_misc_dist()
873
874
875
876 /*===========================================================================+
877 | PROCEDURE |
878 | update_misc_dist |
879 | |
880 | DESCRIPTION |
881 | updates distribution in ar_misc_cash_distributions |
882 | |
883 | SCOPE - PRIVATE |
884 | |
885 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
886 | |
887 | ARGUMENTS |
888 | IN: |
889 | OUT: |
890 | |
891 | RETURNS |
892 | |
893 | NOTES |
894 | |
895 | MODIFICATION HISTORY |
896 | |
897 | 22-SEP-95 OSTEINME created |
898 | 30-MAY-01 MRAMANAT Added an ELSIF condition to handle Case# 6 |
899 | to fix bug 1792989 |
900 | |
901 +===========================================================================*/
902
903 PROCEDURE update_misc_dist(
904 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
905 p_amount IN ar_cash_receipts.amount%TYPE,
906 p_acctd_amount IN ar_cash_receipt_history.acctd_amount%TYPE,
907 p_amount_changed_flag IN BOOLEAN,
908 p_distribution_set_id IN ar_cash_receipts.distribution_set_id%TYPE,
909 p_receivables_trx_id IN ar_cash_receipts.receivables_trx_id%TYPE,
910 p_old_distribution_set_id IN ar_cash_receipts.distribution_set_id%TYPE,
911 p_old_receivables_trx_id IN ar_cash_receipts.receivables_trx_id%TYPE,
912 p_gl_date IN ar_cash_receipt_history.gl_date%TYPE,
913 p_gl_date_changed_flag IN BOOLEAN,
914 p_currency_code IN ar_cash_receipts.currency_code%TYPE,
915 p_exchange_rate IN ar_cash_receipts.exchange_rate%TYPE,
916 p_receipt_date IN ar_cash_receipts.receipt_date%TYPE,
917 p_receipt_date_changed_flag IN BOOLEAN,
918 p_gl_tax_acct IN ar_distributions.code_combination_id%TYPE
919 ) IS
920
921 l_trx_code_combination_id ar_receivables_trx.code_combination_id%TYPE;
922 l_old_trx_code_combination_id ar_receivables_trx.code_combination_id%TYPE;
923 l_dummy ar_misc_cash_distributions.misc_cash_distribution_id%TYPE;
924 l_misc_cash_dist_rec ar_misc_cash_distributions%ROWTYPE;
925 l_old_recs_auto_flag BOOLEAN;
926 l_new_recs_auto_flag BOOLEAN;
927 l_count NUMBER;
928 l_posted ar_cash_receipt_history.posting_control_id%TYPE;
929 l_ae_doc_rec l_ae_doc_rec_type;
930 l_misc_cash_key_value_list gl_ca_utility_pkg.r_key_value_arr;
931 l_old_default_distribution_set NUMBER; --Bug 6416611
932 l_default_distribution_set NUMBER; --Bug 6416611
933 dummy varchar2(1); --Bug 6416611
934 BEGIN
935
936 IF PG_DEBUG in ('Y', 'C') THEN
937 arp_standard.debug('arp_process_receipts.update_misc_dist()+');
938 END IF;
939
940
941 -- The following cases need to be distinguished with regard to
942 -- an update of the distribution set and/or the amount:
943 --
944 -- 1. old distribution set : NULL
945 -- default ccid of activity: NULL
946 -- new distribution set : NULL
947 -- amount : unchanged
948 --
949 -- In this case the manually created distribution records
950 -- do not require any changes (trivial case).
951 --
952 -- 2. old distribution set : NULL
953 -- default ccid of activity: NULL
954 -- new distribution set : NULL
955 -- amount : changed
956 --
957 -- In this case the manually created distribution records
958 -- need to be updated. The percent values must remain
959 -- unchanged, but the corresponding amounts need to be
960 -- adapted to the new total amount.
961 --
962 -- 3. old distribution set : automatically created
963 -- new distribution set : NULL (manually created)
964 -- amount : changed or unchanged
965 --
966 -- Was:
967 -- The old (automatically created) distribution records must
968 -- be deleted. The distribution set must be entered
969 -- manually by the user.
970 --
971 -- As of 11/20/95: distribution window is semi-modal, and user
972 -- can no longer null out NOCOPY distribution set in receipt window.
973 -- Is:
974 -- The user has manually modified the distribution set in the
975 -- distributions window. Nothing needs to be done.
976 --
977 --
978 -- 4. old distribution set : NULL (manually created)
979 -- new distribution set : automatically created
980 -- amount : changed or unchanged
981 --
982 -- The old (manually created) distribution records must
983 -- be deleted; new records must be created according to
984 -- the new distribution_set/activity.
985 --
986 -- 5. old distribution set : automatically created
987 -- new distribution set : automatically created, but changed
988 -- amount : changed or unchanged
989 --
990 -- The old (automatically created) records must be deleted,
991 -- new records need to be created automatically based on
992 -- ccid or distribution set
993 --
994 -- 6. old distribution set : automatically created
995 -- new distribution set : unchanged
996 -- amount : changed
997 --
998 -- The old records must be deleted, new ones created for the
999 -- new amount.
1000 --
1001 -- 7. old distribution set : automatically created
1002 -- new distribution set : unchanged
1003 -- amount : unchanged
1004 --
1005 -- Trivial case -- no change required
1006 --
1007 -- 8. amount : 0 (zero)
1008 --
1009 -- super-trivial case: just delete existing distributions (if any)
1010 -- and exit.
1011 --
1012 -- ... here we go...
1013
1014 -- first determine if current (=old) set of distribution records is
1015 -- based on default account/distribution set
1016
1017 /* Bug fix 2272461
1018 When the MISC receipt amount is changed to zero, the MISCCASH accounting
1019 Record is not created in ar_distributions table. Commented out NOCOPY the portion
1020 of the code which handles the zero receipt amount separately.
1021 IF (p_amount = 0) THEN
1022 --
1023 -- Handle case 8 right away:
1024 --
1025 IF PG_DEBUG in ('Y', 'C') THEN
1026 arp_util.debug('update_misc_dist: ' || '=====> Case 8: amount is zero -- deleting applications');
1027 END IF;
1028
1029 SELECT count(mcd.misc_cash_distribution_id)
1030 INTO l_count
1031 FROM ar_misc_cash_distributions mcd
1032 WHERE mcd.cash_receipt_id = p_cash_receipt_id
1033 AND mcd.reversal_gl_date IS NULL --For rate adjustments picks up records with new rate
1034 AND mcd.posting_control_id = -3 --Not posted
1035 AND EXISTS (SELECT 'x'
1036 FROM ar_distributions ard
1037 WHERE ard.source_id = mcd.misc_cash_distribution_id
1038 AND ard.source_table = 'MCD');
1039
1040 IF PG_DEBUG in ('Y', 'C') THEN
1041 arp_standard.debug('update_misc_dist: ' || ' l_count ' || TO_CHAR(l_count));
1042 END IF;
1043
1044 IF (l_count > 0) THEN
1045 SELECT distinct posting_control_id
1046 INTO l_posted
1047 FROM ar_cash_receipt_history
1048 WHERE cash_receipt_id = p_cash_receipt_id
1049 AND current_record_flag = 'Y';
1050 END IF;
1051 l_ae_doc_rec.document_type := 'RECEIPT';
1052 l_ae_doc_rec.document_id := p_cash_receipt_id;
1053 l_ae_doc_rec.accounting_entity_level := 'ONE';
1054 l_ae_doc_rec.source_table := 'MCD';
1055 l_ae_doc_rec.source_id := '';
1056
1057 --
1058 IF (l_count > 0) then
1059 IF (l_posted = -3) then
1060 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
1061 END IF;
1062 END IF; */
1063 --
1064 /*----------------------------------+
1065 | Added bulk collect of misc cash |
1066 | distribution id for use in MRC |
1067 | engine for trigger replacement |
1068 +----------------------------------*/
1069
1070 /*DELETE ar_misc_cash_distributions
1071 WHERE cash_receipt_id = p_cash_receipt_id
1072 RETURNING misc_cash_distribution_id
1073 BULK COLLECT INTO l_misc_cash_key_value_list; */
1074
1075 /*---------------------------------+
1076 | Calling central MRC library |
1077 | for MRC Integration |
1078 +---------------------------------*/
1079
1080 /* ar_mrc_engine.maintain_mrc_data(
1081 p_event_mode => 'DELETE',
1082 p_table_name => 'AR_MISC_CASH_DISTRIBTIONS',
1083 p_mode => 'BATCH',
1084 p_key_value_list => l_misc_cash_key_value_list);
1085
1086 RETURN;
1087 END IF; */
1088
1089 IF (p_old_distribution_set_id IS NOT NULL) THEN
1090 l_old_recs_auto_flag := TRUE;
1091 ELSE
1092 --
1093 BEGIN
1094 /* For bug2221221 modified the query to handle when receivable
1095 activity is inactivated on the same date as the receipt date */
1096 /* Start Bug 6416611 - modified query to handle case of receivable activity
1097 with distribution set */
1098 SELECT rt.code_combination_id, rt.default_acctg_distribution_set
1099 INTO l_old_trx_code_combination_id, l_old_default_distribution_set
1100 FROM ar_receivables_trx rt
1101 WHERE rt.type in
1102 ('MISCCASH', 'BANK_ERROR', 'CCREFUND', 'CM_REFUND', 'CC_CHARGEBACK')
1103 -- AND ( (NVL( rt.status, 'A' ) = 'A') or (rt.end_date_active=p_receipt_date))
1104 AND rt.RECEIVABLES_TRX_ID = p_old_receivables_trx_id;
1105
1106 IF l_old_default_distribution_set IS NULL then
1107 SELECT 'x' INTO dummy from gl_code_combinations
1108 where code_combination_id = l_old_trx_code_combination_id
1109 AND ENABLED_FLAG='Y';
1110 END IF;
1111 EXCEPTION
1112 WHEN NO_DATA_FOUND THEN
1113 FND_MESSAGE.Set_Name('AR', 'AR_NO_ACTIVITY_FOUND');
1114 APP_EXCEPTION.Raise_Exception;
1115 END;
1116 --
1117 IF (l_old_trx_code_combination_id IS NOT NULL) THEN
1118 l_old_recs_auto_flag := TRUE;
1119 ELSE
1120 l_old_recs_auto_flag := FALSE;
1121 END IF;
1122 END IF;
1123 -- now determine if new set of distribution records is
1124 -- based on default account/distribution set
1125
1126 IF (p_distribution_set_id IS NOT NULL) THEN
1127 l_new_recs_auto_flag := TRUE;
1128 ELSE
1129 --
1130 BEGIN
1131 /* For bug2221221 modified the query to handle when receivable
1132 activity is inactivated on the same date as the receipt date */
1133 /* Start Bug 6416611 - modified query to handle case of receivable activity
1134 with distribution set */
1135 SELECT rt.code_combination_id, rt.default_acctg_distribution_set
1136 INTO l_trx_code_combination_id, l_default_distribution_set
1137 FROM ar_receivables_trx rt
1138 WHERE rt.type in
1139 ('MISCCASH', 'BANK_ERROR', 'CCREFUND', 'CM_REFUND' , 'CC_CHARGEBACK')
1140 -- AND ((NVL( rt.status, 'A' ) = 'A') or (rt.end_date_active=p_receipt_date))
1141 AND rt.RECEIVABLES_TRX_ID = p_receivables_trx_id;
1142
1143 IF l_default_distribution_set IS NULL then
1144 SELECT 'x' INTO dummy from gl_code_combinations
1145 where code_combination_id = l_trx_code_combination_id
1146 AND ENABLED_FLAG='Y';
1147 END IF;
1148 EXCEPTION
1149 WHEN NO_DATA_FOUND THEN
1150 FND_MESSAGE.Set_Name('AR', 'AR_NO_ACTIVITY_FOUND');
1151 APP_EXCEPTION.Raise_Exception;
1152 END;
1153 --
1154 IF (l_trx_code_combination_id IS NOT NULL) THEN
1155 l_new_recs_auto_flag := TRUE;
1156 ELSE
1157 l_new_recs_auto_flag := FALSE;
1158 END IF;
1159 END IF;
1160
1161 IF (l_new_recs_auto_flag) THEN
1162 IF PG_DEBUG in ('Y', 'C') THEN
1163 arp_standard.debug('update_misc_dist: ' || 'l_new_recs_auto_flag = TRUE');
1164 END IF;
1165 ELSE
1166 IF PG_DEBUG in ('Y', 'C') THEN
1167 arp_standard.debug('update_misc_dist: ' || 'l_new_recs_auto_flag = FALSE');
1168 END IF;
1169 END IF;
1170
1171 IF (l_old_recs_auto_flag) THEN
1172 IF PG_DEBUG in ('Y', 'C') THEN
1173 arp_standard.debug('update_misc_dist: ' || 'l_old_recs_auto_flag = TRUE');
1174 END IF;
1175 ELSE
1176 IF PG_DEBUG in ('Y', 'C') THEN
1177 arp_standard.debug('update_misc_dist: ' || 'l_old_recs_auto_flag = FALSE');
1178 END IF;
1179 END IF;
1180
1181 IF PG_DEBUG in ('Y', 'C') THEN
1182 arp_standard.debug('update_misc_dist: ' || 'p_distribution_set_id = ' ||
1183 to_char(p_distribution_set_id));
1184 arp_standard.debug('update_misc_dist: ' || 'p_old_distribution_set_id = ' ||
1185 to_char(p_old_distribution_set_id));
1186 arp_standard.debug('update_misc_dist: ' || 'l_trx_code_combination_id = ' ||
1187 to_char(l_trx_code_combination_id));
1188 arp_standard.debug('update_misc_dist: ' || 'l_old_trx_code_combination_id = ' ||
1189 to_char(l_old_trx_code_combination_id));
1190 END IF;
1191
1192
1193 --
1194 -- now handle different cases as outlined above:
1195 --
1196 IF ((p_amount_changed_flag = FALSE) AND
1197 ((l_old_recs_auto_flag = TRUE and l_new_recs_auto_flag = TRUE and
1198 (p_distribution_set_id = p_old_distribution_set_id OR
1199 l_trx_code_combination_id = l_old_trx_code_combination_id)) OR
1200 (l_old_recs_auto_flag = FALSE and l_new_recs_auto_flag = FALSE))) THEN
1201 --
1202 -- Handle cases 1 / 7:
1203 --
1204 -- check if gl_date or receipt date has changed. If one of them has
1205 -- changed, simply update the dates in all existing distribution records:
1206 --
1207 IF (p_receipt_date_changed_flag = TRUE OR
1208 p_gl_date_changed_flag = TRUE) THEN
1209 --
1210 IF PG_DEBUG in ('Y', 'C') THEN
1211 arp_util.debug('update_misc_dist: ' || '=====> Case 1/7: updating distributions with new date(s)');
1212 END IF;
1213 --
1214 /*----------------------------------+
1215 | Added bulk collect of misc cash |
1216 | distribution id for use in MRC |
1217 | engine for trigger replacement |
1218 +----------------------------------*/
1219 /* Bug 5980036 */
1220 UPDATE ar_misc_cash_distributions
1221 SET -- gl_date = p_gl_date,
1222 apply_date = p_receipt_date
1223 WHERE cash_receipt_id = p_cash_receipt_id
1224 RETURNING misc_cash_distribution_id
1225 BULK COLLECT INTO l_misc_cash_key_value_list;
1226
1227 /*---------------------------------+
1228 | Calling central MRC library |
1229 | for MRC Integration |
1230 +---------------------------------*/
1231 /*BUG4301323
1232 ar_mrc_engine.maintain_mrc_data(
1233 p_event_mode => 'UPDATE',
1234 p_table_name => 'AR_MISC_CASH_DISTRIBTIONS',
1235 p_mode => 'BATCH',
1236 p_key_value_list => l_misc_cash_key_value_list);
1237 */
1238 ELSE
1239 IF PG_DEBUG in ('Y', 'C') THEN
1240 arp_util.debug('update_misc_dist: ' || '=====> Case 1/7: no update required');
1241 END IF;
1242 END IF;
1243 --
1244 /* Bug 1792989. When only the receipt amount is changed with no change
1245 to receivable activity, then distribution accounting should not be
1246 affected and only the amounts should be updated in
1247 AR_MISC_CASH_DISTRIBUTIONS. Also Accounting engine is called to
1248 recreate accounting in AR_DISTRIBUTIONS for the new amounts.
1249 This is done by
1250 1. Calling the Accounting engine to delete the MCD records
1251 from AR_DISTRIBUTIONS for the passed cash_receipt_id.
1252 2. Update the amount in AR_MISC_CASH_DISTRIBUTIONS by calling
1253 procedure update_manual_dist.
1254 3. Calling the Accounting engine to recreate the MCD records
1255 taking the new amount into consideration.
1256 */
1257 /* bug 3324670 : modified the below ELSIF */
1258 ELSIF (p_amount_changed_flag = TRUE AND
1259 (l_old_recs_auto_flag = TRUE AND
1260 l_new_recs_auto_flag = TRUE AND
1261 (p_distribution_set_id = p_old_distribution_set_id OR
1262 l_trx_code_combination_id = l_old_trx_code_combination_id)) OR
1263 (l_old_recs_auto_flag = FALSE AND
1264 l_new_recs_auto_flag = FALSE)) THEN
1265
1266 IF PG_DEBUG in ('Y', 'C') THEN
1267 arp_util.debug('update_misc_dist: ' || '=====> Case 2: updating amounts');
1268 END IF;
1269 SELECT count(mcd.misc_cash_distribution_id)
1270 INTO l_count
1271 FROM ar_misc_cash_distributions mcd
1272 WHERE mcd.cash_receipt_id = p_cash_receipt_id
1273 AND mcd.reversal_gl_date IS NULL
1274 AND mcd.posting_control_id = -3
1275 AND EXISTS (SELECT 'x'
1276 FROM ar_distributions ard
1277 WHERE ard.source_id = mcd.misc_cash_distribution_id
1278 AND ard.source_table = 'MCD');
1279
1280 l_ae_doc_rec.document_type := 'RECEIPT';
1281 l_ae_doc_rec.document_id := p_cash_receipt_id;
1282 l_ae_doc_rec.accounting_entity_level := 'ONE';
1283 l_ae_doc_rec.source_table := 'MCD';
1284 l_ae_doc_rec.source_id := '';
1285 /* Bugfix 2753644. */
1286 l_ae_doc_rec.gl_tax_acct := p_gl_tax_acct;
1287
1288 IF (l_count > 0) then
1289 SELECT distinct posting_control_id
1290 INTO l_posted
1291 FROM ar_cash_receipt_history
1292 WHERE cash_receipt_id = p_cash_receipt_id
1293 AND current_record_flag = 'Y';
1294
1295 IF (l_posted = -3) THEN
1296
1297 -- Call accounting entry library begins
1298 IF PG_DEBUG in ('Y', 'C') THEN
1299 arp_standard.debug('update_misc_dist: ' || 'Delete Misc Cash Receipt start () +');
1300 END IF;
1301 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
1302 IF PG_DEBUG in ('Y', 'C') THEN
1303 arp_standard.debug('update_misc_dist: ' || 'Delete Misc Cash Receipt start () -');
1304 END IF;
1305 END IF;
1306 END IF;
1307
1308 update_manual_dist( p_cash_receipt_id,
1309 p_amount,
1310 p_acctd_amount,
1311 p_exchange_rate,
1312 p_currency_code,
1313 p_gl_date,
1314 p_receipt_date );
1315
1316 SELECT distinct posting_control_id
1317 INTO l_posted
1318 FROM ar_cash_receipt_history
1319 WHERE cash_receipt_id = p_cash_receipt_id
1320 AND current_record_flag = 'Y';
1321 IF (l_posted = -3) then
1322 IF PG_DEBUG in ('Y', 'C') THEN
1323 arp_standard.debug('update_misc_dist: ' || ' Create Acct Entry');
1324 END IF;
1325 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1326 END IF;
1327 --
1328 /* bug 3324670 : commenting below code.Case 2 has been handled Above */
1329 /*
1330 ELSIF (p_amount_changed_flag = TRUE AND
1331 l_old_recs_auto_flag = FALSE AND
1332 l_new_recs_auto_flag = TRUE) THEN
1333 --
1334 -- Handle case 2:
1335 --
1336 -- if distribution was and still is created manually, just update
1337 -- the amounts for each record.
1338 --
1339 IF PG_DEBUG in ('Y', 'C') THEN
1340 arp_util.debug('update_misc_dist: ' || '=====> Case 2: updating amounts');
1341 END IF;
1342 --
1343 update_manual_dist( p_cash_receipt_id,
1344 p_amount,
1345 p_acctd_amount,
1346 p_exchange_rate,
1347 p_currency_code,
1348 p_gl_date,
1349 p_receipt_date );
1350 --
1351 */
1352 ELSIF (l_old_recs_auto_flag = TRUE AND
1353 l_new_recs_auto_flag = FALSE) THEN
1354 --
1355 -- Handle case 3:
1356 --
1357 -- was:
1358 -- Delete old (automatically created records). User has to enter
1359 -- new records manually.
1360 --
1361 -- now: do nothing (see above)
1362 --
1363 /*
1364 ***** IF THIS EVER IS UNCOMMENTED, THEN A CALL TO THE MRC ENGINE IS
1365 REQUIRED.....
1366
1367 IF PG_DEBUG in ('Y', 'C') THEN
1368 arp_util.debug('update_misc_dist: ' || 'Case 3: deleting old records');
1369 END IF;
1370
1371 DELETE ar_misc_cash_distributions
1372 WHERE cash_receipt_id = p_cash_receipt_id;
1373
1374 */
1375 IF PG_DEBUG in ('Y', 'C') THEN
1376 arp_util.debug('update_misc_dist: ' || '=====> Case 3: do nothing');
1377 END IF;
1378 NULL;
1379
1380 ELSE
1381
1382 /* ((l_old_recs_auto_flag = FALSE AND
1383 l_new_recs_auto_flag = TRUE) OR
1384 (l_old_recs_auto_flag = TRUE AND
1385 l_new_recs_auto_flag = TRUE)) THEN
1386 --
1387 -- the above condition can obviously simplified, but was left the
1388 -- way it is to more closely match the conditions associated with
1389 -- cases 4, 5, and 6 (for maintenance purposes).
1390
1391 */
1392 --
1393 -- Handle case 4, 5, 6:
1394 --
1395 -- the old records need to be deleted (no matter how they were
1396 -- created, and new ones will created automatically based on the
1397 -- ccid or distribution set.
1398
1399 IF PG_DEBUG in ('Y', 'C') THEN
1400 arp_standard.debug('update_misc_dist: ' || ' =====> CASE 4, 5, 6');
1401 END IF;
1402
1403 SELECT count(mcd.misc_cash_distribution_id)
1404 INTO l_count
1405 FROM ar_misc_cash_distributions mcd
1406 WHERE mcd.cash_receipt_id = p_cash_receipt_id
1407 AND mcd.reversal_gl_date IS NULL --For rate adjustments picks up records with new rate
1408 AND mcd.posting_control_id = -3 --Not posted
1409 AND EXISTS (SELECT 'x'
1410 FROM ar_distributions ard
1411 WHERE ard.source_id = mcd.misc_cash_distribution_id
1412 AND ard.source_table = 'MCD');
1413
1414 IF PG_DEBUG in ('Y', 'C') THEN
1415 arp_standard.debug('update_misc_dist: ' || ' l_count ' || TO_CHAR(l_count));
1416 END IF;
1417
1418 l_ae_doc_rec.document_type := 'RECEIPT';
1419 l_ae_doc_rec.document_id := p_cash_receipt_id;
1420 l_ae_doc_rec.accounting_entity_level := 'ONE';
1421 l_ae_doc_rec.source_table := 'MCD';
1422 l_ae_doc_rec.source_id := '';
1423 /* Bugfix 2753644 */
1424 l_ae_doc_rec.gl_tax_acct := p_gl_tax_acct;
1425
1426 IF (l_count > 0) then
1427
1428 SELECT distinct posting_control_id
1429 INTO l_posted
1430 FROM ar_cash_receipt_history
1431 WHERE cash_receipt_id = p_cash_receipt_id
1432 AND current_record_flag = 'Y';
1433
1434 IF (l_posted = -3) THEN
1435
1436 -- Call accounting entry library begins
1437 IF PG_DEBUG in ('Y', 'C') THEN
1438 arp_standard.debug('update_misc_dist: ' || 'Delete Misc Cash Receipt start () +');
1439 END IF;
1440
1441 -- Calling accounting entry library
1442 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
1443 IF PG_DEBUG in ('Y', 'C') THEN
1444 arp_standard.debug('update_misc_dist: ' || 'Delete Misc Cash Receipt start () -');
1445 END IF;
1446 END IF;
1447 END IF;
1448
1449 insert_misc_dist( p_cash_receipt_id,
1450 p_gl_date,
1451 p_amount,
1452 p_currency_code,
1453 p_exchange_rate,
1454 p_acctd_amount,
1455 p_receipt_date,
1456 p_receivables_trx_id,
1457 p_distribution_set_id);
1458
1459 SELECT distinct posting_control_id
1460 INTO l_posted
1461 FROM ar_cash_receipt_history
1462 WHERE cash_receipt_id = p_cash_receipt_id
1463 AND current_record_flag = 'Y';
1464 IF (l_posted = -3) then
1465 IF PG_DEBUG in ('Y', 'C') THEN
1466 arp_standard.debug('update_misc_dist: ' || ' Create Acct Entry');
1467 END IF;
1468 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1469
1470 END IF;
1471 --
1472 END IF;
1473
1474 IF PG_DEBUG in ('Y', 'C') THEN
1475 arp_standard.debug('arp_process_receipts.update_misc_dist()-');
1476 END IF;
1477
1478 END update_misc_dist;
1479
1480 /*===========================================================================+
1481 | PROCEDURE |
1482 | create_mcd_recs |
1483 | |
1484 | DESCRIPTION |
1485 | creates distribution in ar_misc_cash_distributions based on a pre- |
1486 | defined distribution set. This function also takes care of possible |
1487 | rounding errors. |
1488 | |
1489 | SCOPE - PRIVATE |
1490 | |
1491 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1492 | |
1493 | ARGUMENTS |
1494 | IN: |
1495 | OUT: |
1496 | |
1497 | RETURNS |
1498 | |
1499 | NOTES |
1500 | |
1501 | MODIFICATION HISTORY |
1502 | |
1503 | 22-SEP-95 OSTEINME created |
1504 | 28-SEP-98 K.Murphy Bug #705078. Added code to validate the key |
1505 | flex before the MISC distributions are |
1506 | created. Checks both the enabled flag and |
1507 | From/To dates. |
1508 | Shuffled the code around a bit in order to do|
1509 | this. Changed the insert from "insert as |
1510 | select" to using a cursor. |
1511 | |
1512 +===========================================================================*/
1513
1514
1515 PROCEDURE create_mcd_recs(
1516 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
1517 p_amount IN ar_cash_receipts.amount%TYPE,
1518 p_acctd_amount IN ar_cash_receipt_history.acctd_amount%TYPE,
1519 p_exchange_rate IN ar_cash_receipts.exchange_rate%TYPE,
1520 p_currency_code IN ar_cash_receipts.currency_code%TYPE,
1521 p_gl_date IN ar_cash_receipt_history.gl_date%TYPE,
1522 p_receipt_date IN ar_cash_receipts.receipt_date%TYPE,
1523 p_distribution_set_id IN ar_cash_receipts.distribution_set_id%TYPE,
1524 p_ussgl_trx_code IN ar_cash_receipts.ussgl_transaction_code%TYPE
1525 ) IS
1526
1527 l_min_unit NUMBER;
1528 l_precision NUMBER;
1529 l_acctd_rounding_diff NUMBER;
1530 l_rounding_diff NUMBER;
1531 l_misc_cash_dist_id ar_misc_cash_distributions.misc_cash_distribution_id%TYPE; /* added for mrc changes */
1532 l_misc_cash_key_value_list gl_ca_utility_pkg.r_key_value_arr;
1533
1534 l_xla_ev_rec arp_xla_events.xla_events_type;
1535
1536 /* Bug fix 2843634
1537 Modified the cursor to take care of the situation where the receipt amount is zero */
1538
1539 CURSOR c_dist IS
1540 SELECT
1541 dist_code_combination_id,
1542 DECODE(p_amount,0,ROUND(percent_distribution,3),
1543 ROUND
1544 (
1545 ROUND((percent_distribution/100.0) * p_amount,3) * 100/
1546 p_amount,
1547 3
1548 )) percent,
1549 decode
1550 (
1551 l_min_unit, null,
1552 round(p_amount * percent_distribution/100,
1553 l_precision),
1554 round(p_amount * (percent_distribution/100)/l_min_unit)
1555 * l_min_unit
1556 ) amount,
1557 decode
1558 (
1559 arp_global.base_min_acc_unit, null,
1560 round((p_amount * percent_distribution/100) * nvl(p_exchange_rate,1),
1561 arp_global.base_precision),
1562 round(p_amount * (percent_distribution/100) * nvl(p_exchange_rate,1)
1563 / arp_global.base_precision) * arp_global.base_precision
1564 ) acctd_amount
1565 FROM
1566 ar_distribution_set_lines
1567 WHERE
1568 distribution_set_id = p_distribution_set_id;
1569
1570 CURSOR c_hist IS
1571 SELECT cash_receipt_history_id
1572 FROM ar_cash_receipt_history
1573 WHERE current_record_flag = 'Y'
1574 AND cash_receipt_id = p_cash_receipt_id;
1575
1576 l_crh_id NUMBER;
1577
1578 BEGIN
1579
1580 arp_standard.debug('arp_process_receipts.create_mcd_recs()+');
1581
1582 SELECT minimum_accountable_unit,precision
1583 INTO l_min_unit, l_precision
1584 FROM fnd_currencies
1585 WHERE currency_code = p_currency_code;
1586
1587 OPEN c_hist;
1588 FETCH c_hist INTO l_crh_id;
1589 CLOSE c_hist;
1590
1591
1592 FOR dist in c_dist LOOP
1593 -- Bug fix #705078
1594 -- Verify that account is valid before doing insert.
1595 --
1596 -- Using the flex field server APIs to do this ...
1597 -- (note also that we are using the same error message
1598 -- for each case.
1599 --
1600 -- Firstly, call fnd_flex_keyval.validate_ccid to populate
1601 -- all of the relevant global variables.
1602 --
1603 IF fnd_flex_keyval.validate_ccid(
1604 appl_short_name => 'SQLGL',
1605 key_flex_code => 'GL#',
1606 structure_number => arp_global.chart_of_accounts_id,
1607 combination_id => dist.dist_code_combination_id) THEN
1608 -- Secondly, check is the key flex is enabled.
1609 --
1610 IF not fnd_flex_keyval.enabled_flag THEN
1611 FND_MESSAGE.Set_Name('AR', 'AR_GL_ACCOUNT_INVALID');
1612 APP_EXCEPTION.Raise_Exception;
1613 -- Thirdly, check if the key flex is valid for this date.
1614 --
1615 ELSIF p_gl_date NOT between nvl(fnd_flex_keyval.start_date,p_gl_date)
1616 and nvl(fnd_flex_keyval.end_date,p_gl_date) THEN
1617 FND_MESSAGE.Set_Name('AR', 'AR_GL_ACCOUNT_INVALID');
1618 APP_EXCEPTION.Raise_Exception;
1619 END IF;
1620 END IF;
1621
1622 /* store the misc cash dist id for use in MRC call */
1623
1624 SELECT ar_misc_cash_distributions_s.nextval
1625 INTO l_misc_cash_dist_id
1626 FROM DUAL;
1627
1628 INSERT INTO ar_misc_cash_distributions (
1629 misc_cash_distribution_id,
1630 last_updated_by,
1631 last_update_date,
1632 created_by,
1633 creation_date,
1634 cash_receipt_id,
1635 gl_date,
1636 apply_date,
1637 code_combination_id,
1638 percent,
1639 amount,
1640 set_of_books_id,
1641 acctd_amount,
1642 posting_control_id,
1643 created_from,
1644 ussgl_transaction_code,org_id,
1645 cash_receipt_history_id)
1646 VALUES
1647 (
1648 l_misc_cash_dist_id,
1649 arp_global.user_id,
1650 arp_global.last_update_date,
1651 arp_global.created_by,
1652 arp_global.creation_date,
1653 p_cash_receipt_id,
1654 p_gl_date,
1655 p_receipt_date,
1656 dist.dist_code_combination_id,
1657 dist.percent,
1658 dist.amount,
1659 arp_global.set_of_books_id,
1660 dist.acctd_amount,
1661 -3,
1662 'ARRERCT',
1663 p_ussgl_trx_code, arp_standard.sysparm.org_id,
1664 l_crh_id);
1665
1666
1667 l_xla_ev_rec.xla_from_doc_id := p_cash_receipt_id;
1668 l_xla_ev_rec.xla_to_doc_id := p_cash_receipt_id;
1669 l_xla_ev_rec.xla_doc_table := 'MCD';
1670 l_xla_ev_rec.xla_mode := 'O';
1671 l_xla_ev_rec.xla_call := 'D';
1672 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
1673
1674
1675 /*---------------------------------+
1676 | Calling central MRC library |
1677 | for MRC Integration |
1678 +---------------------------------*/
1679 /*4301323
1680 ar_mrc_engine.maintain_mrc_data(
1681 p_event_mode => 'INSERT',
1682 p_table_name => 'AR_MISC_CASH_DISTRIBUTIONS',
1683 p_mode => 'SINGLE',
1684 p_key_value => l_misc_cash_dist_id );
1685 */
1686 END LOOP;
1687
1688 -- determine if there is a rounding error
1689
1690 SELECT NVL(p_amount, 0) -
1691 NVL(SUM
1692 (
1693 decode
1694 (
1695 l_min_unit, null,
1696 round(p_amount * percent_distribution/100,
1697 l_precision),
1698 round(p_amount*(percent_distribution/100)/l_min_unit)
1699 *l_min_unit
1700 )
1701 ),0)
1702 ,
1703 NVL(p_acctd_amount, 0) -
1704 NVL(SUM
1705 (
1706 decode
1707 (
1708 arp_global.base_min_acc_unit, null,
1709 round((p_amount * percent_distribution/100)
1710 * nvl(p_exchange_rate,1),
1711 arp_global.base_precision),
1712 round(p_amount * (percent_distribution/100)
1713 * nvl(p_exchange_rate ,1) /
1714 arp_global.base_precision)
1715 * arp_global.base_precision
1716 )
1717 ),0)
1718
1719 INTO l_rounding_diff,
1720 l_acctd_rounding_diff
1721 FROM ar_distribution_set_lines
1722 WHERE distribution_set_id = p_distribution_set_id;
1723
1724 arp_util.debug('Rounding error = ' || to_char(l_rounding_diff));
1725 arp_util.debug('Rounding error (acctd) = ' || to_char(l_acctd_rounding_diff));
1726
1727
1728 IF (l_acctd_rounding_diff <> 0 OR l_rounding_diff <>0) THEN
1729
1730 /*----------------------------------+
1731 | Added bulk collect of misc cash |
1732 | distribution id for use in MRC |
1733 | engine for trigger replacement |
1734 +----------------------------------*/
1735
1736 UPDATE ar_misc_cash_distributions
1737 SET amount = amount + l_rounding_diff,
1738 acctd_amount = acctd_amount + l_acctd_rounding_diff,
1739 percent = ROUND (
1740 (amount + l_rounding_diff)*100/p_amount,
1741 3 )
1742 WHERE cash_receipt_id = p_cash_receipt_id
1743 AND ROWNUM = 1
1744 RETURNING misc_cash_distribution_id
1745 BULK COLLECT INTO l_misc_cash_key_value_list;
1746
1747 /*---------------------------------+
1748 | Calling central MRC library |
1749 | for MRC Integration |
1750 +---------------------------------*/
1751 /*BUG4301323
1752 ar_mrc_engine.maintain_mrc_data(
1753 p_event_mode => 'UPDATE',
1754 p_table_name => 'AR_MISC_CASH_DISTRIBUTIONS',
1755 p_mode => 'BATCH',
1756 p_key_value_list => l_misc_cash_key_value_list);
1757 */
1758
1759 END IF;
1760
1761 -- round percentages if necessary
1762
1763 round_mcd_recs(p_cash_receipt_id);
1764
1765 arp_standard.debug('arp_process_receipts.create_mcd_recs()-');
1766
1767 END; -- create_mcd_recs()
1768
1769
1770 /*===========================================================================+
1771 | PROCEDURE |
1772 | update_manual_dist |
1773 | |
1774 | DESCRIPTION |
1775 | creates distribution in ar_misc_cash_distributions based on a pre- |
1776 | defined distribution set. This function also takes care of possible |
1777 | rounding errors. |
1778 | |
1779 | SCOPE - PRIVATE |
1780 | |
1781 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1782 | |
1783 | ARGUMENTS |
1784 | IN: |
1785 | OUT: |
1786 | |
1787 | RETURNS |
1788 | |
1789 | NOTES |
1790 | |
1791 | MODIFICATION HISTORY |
1792 | |
1793 | 22-SEP-95 OSTEINME created |
1794 | |
1795 +===========================================================================*/
1796
1797 PROCEDURE update_manual_dist(
1798 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
1799 p_amount IN ar_cash_receipts.amount%TYPE,
1800 p_acctd_amount IN ar_cash_receipt_history.acctd_amount%TYPE,
1801 p_exchange_rate IN ar_cash_receipts.exchange_rate%TYPE,
1802 p_currency_code IN ar_cash_receipts.currency_code%TYPE,
1803 p_gl_date IN ar_cash_receipt_history.gl_date%TYPE,
1804 p_receipt_date IN ar_cash_receipts.receipt_date%TYPE
1805 ) IS
1806
1807 l_min_unit NUMBER;
1808 l_precision NUMBER;
1809 l_acctd_rounding_diff NUMBER;
1810 l_rounding_diff NUMBER;
1811 l_misc_cash_key_value_list gl_ca_utility_pkg.r_key_value_arr;
1812
1813 BEGIN
1814
1815 arp_standard.debug('arp_process_receipts.update_manual_dist()+');
1816
1817 SELECT minimum_accountable_unit,precision
1818 INTO l_min_unit, l_precision
1819 FROM fnd_currencies
1820 WHERE currency_code = p_currency_code;
1821
1822 /*----------------------------------+
1823 | Added bulk collect of misc cash |
1824 | distribution id for use in MRC |
1825 | engine for trigger replacement |
1826 +----------------------------------*/
1827
1828 UPDATE ar_misc_cash_distributions
1829 SET gl_date = p_gl_date,
1830 apply_date = p_receipt_date,
1831 amount = decode
1832 (
1833 l_min_unit, null,
1834 round(p_amount * percent/100,
1835 l_precision),
1836 round(p_amount * (percent/100)/l_min_unit)
1837 * l_min_unit
1838 ),
1839 acctd_amount = decode
1840 (
1841 arp_global.base_min_acc_unit, null,
1842 round((p_amount * percent/100) * nvl(p_exchange_rate,1),
1843 arp_global.base_precision),
1844 round(p_amount * (percent/100) * nvl(p_exchange_rate,1)
1845 / arp_global.base_precision) * arp_global.base_precision
1846 ),
1847 last_updated_by = arp_global.user_id,
1848 last_update_date = arp_global.last_update_date
1849 WHERE cash_receipt_id = p_cash_receipt_id
1850 RETURNING misc_cash_distribution_id
1851 BULK COLLECT INTO l_misc_cash_key_value_list;
1852
1853 /*---------------------------------+
1854 | Calling central MRC library |
1855 | for MRC Integration |
1856 +---------------------------------*/
1857 /*BUG4301323
1858 ar_mrc_engine.maintain_mrc_data(
1859 p_event_mode => 'UPDATE',
1860 p_table_name => 'AR_MISC_CASH_DISTRIBUTIONS',
1861 p_mode => 'BATCH',
1862 p_key_value_list => l_misc_cash_key_value_list);
1863 */
1864 -- determine if there is a rounding error
1865
1866 SELECT NVL(p_amount, 0) -
1867 NVL(SUM
1868 (
1869 decode
1870 (
1871 l_min_unit, null,
1872 round(p_amount * percent/100,
1873 l_precision),
1874 round(p_amount*(percent/100)/l_min_unit)
1875 *l_min_unit
1876 )
1877 ),0)
1878 ,
1879 NVL(p_acctd_amount, 0) -
1880 NVL(SUM
1881 (
1882 decode
1883 (
1884 arp_global.base_min_acc_unit, null,
1885 round((p_amount * percent/100)
1886 * nvl(p_exchange_rate,1),
1887 arp_global.base_precision),
1888 round(p_amount * (percent/100)
1889 * nvl(p_exchange_rate ,1) /
1890 arp_global.base_precision)
1891 * arp_global.base_precision
1892 )
1893 ),0)
1894
1895 INTO l_rounding_diff,
1896 l_acctd_rounding_diff
1897 FROM ar_misc_cash_distributions
1898 WHERE cash_receipt_id = p_cash_receipt_id;
1899
1900 arp_util.debug('Rounding error = ' || to_char(l_rounding_diff));
1901 arp_util.debug('Rounding error (acctd) = ' || to_char(l_acctd_rounding_diff));
1902
1903
1904 IF (l_acctd_rounding_diff <> 0 OR l_rounding_diff <>0) THEN
1905
1906 /*----------------------------------+
1907 | Added bulk collect of misc cash |
1908 | distribution id for use in MRC |
1909 | engine for trigger replacement |
1910 +----------------------------------*/
1911 UPDATE ar_misc_cash_distributions
1912 SET amount = amount + l_rounding_diff,
1913 acctd_amount = acctd_amount + l_acctd_rounding_diff,
1914 percent = ROUND (
1915 (amount + l_rounding_diff)*100/p_amount,
1916 3 )
1917 WHERE cash_receipt_id = p_cash_receipt_id
1918 AND ROWNUM = 1
1919 RETURNING misc_cash_distribution_id
1920 BULK COLLECT INTO l_misc_cash_key_value_list;
1921
1922 /*---------------------------------+
1923 | Calling central MRC library |
1924 | for MRC Integration |
1925 +---------------------------------*/
1926 /*BUG4301323
1927 ar_mrc_engine.maintain_mrc_data(
1928 p_event_mode => 'UPDATE',
1929 p_table_name => 'AR_MISC_CASH_DISTRIBUTIONS',
1930 p_mode => 'BATCH',
1931 p_key_value_list => l_misc_cash_key_value_list);
1932 */
1933
1934 END IF;
1935
1936 -- round percent columns if necessary
1937
1938 round_mcd_recs(p_cash_receipt_id);
1939
1940 arp_standard.debug('arp_process_receipts.update_manual_dist()-');
1941
1942 END; -- update_manual_dist()
1943 --
1944
1945
1946 /*===========================================================================+
1947 | PROCEDURE |
1948 | rate_adjust |
1949 | |
1950 | DESCRIPTION |
1951 | This function inserts a record into the rate adjustments table by |
1952 | calling the rate adjustments table handler. The INSERT will cause |
1953 | a database trigger to fire, which will update related tables. |
1954 | This function is being called from update_cash_receipts and from |
1955 | update_misc_receipts. |
1956 | |
1957 | SCOPE - PRIVATE |
1958 | |
1959 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1960 | |
1961 | ARGUMENTS |
1962 | IN: |
1963 | OUT: |
1964 | |
1965 | RETURNS |
1966 | |
1967 | NOTES |
1968 | |
1969 | MODIFICATION HISTORY |
1970 | |
1971 | 26-JAN-96 OSTEINME created |
1972 | 08-AUG-97 KLAWRANC Added call to arp_rate_adj.main as the rate |
1973 | adjustments trigger has been removed in |
1974 | Release 11. |
1975 | |
1976 +===========================================================================*/
1977
1978 PROCEDURE rate_adjust(
1979 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
1980 p_rate_adjust_gl_date IN DATE,
1981 p_new_exchange_date IN DATE,
1982 p_new_exchange_rate IN ar_rate_adjustments.new_exchange_rate%TYPE,
1983 p_new_exchange_rate_type IN ar_rate_adjustments.new_exchange_rate_type%TYPE,
1984 p_old_exchange_date IN DATE,
1985 p_old_exchange_rate IN ar_rate_adjustments.old_exchange_rate%TYPE,
1986 p_old_exchange_rate_type IN ar_rate_adjustments.old_exchange_rate_type%TYPE,
1987 p_gain_loss IN ar_rate_adjustments.gain_loss%TYPE,
1988 p_exchange_rate_attr_cat IN ar_rate_adjustments.attribute_category%TYPE,
1989 p_exchange_rate_attr1 IN ar_rate_adjustments.attribute1%TYPE,
1990 p_exchange_rate_attr2 IN ar_rate_adjustments.attribute2%TYPE,
1991 p_exchange_rate_attr3 IN ar_rate_adjustments.attribute3%TYPE,
1992 p_exchange_rate_attr4 IN ar_rate_adjustments.attribute4%TYPE,
1993 p_exchange_rate_attr5 IN ar_rate_adjustments.attribute5%TYPE,
1994 p_exchange_rate_attr6 IN ar_rate_adjustments.attribute6%TYPE,
1995 p_exchange_rate_attr7 IN ar_rate_adjustments.attribute7%TYPE,
1996 p_exchange_rate_attr8 IN ar_rate_adjustments.attribute8%TYPE,
1997 p_exchange_rate_attr9 IN ar_rate_adjustments.attribute9%TYPE,
1998 p_exchange_rate_attr10 IN ar_rate_adjustments.attribute10%TYPE,
1999 p_exchange_rate_attr11 IN ar_rate_adjustments.attribute11%TYPE,
2000 p_exchange_rate_attr12 IN ar_rate_adjustments.attribute12%TYPE,
2001 p_exchange_rate_attr13 IN ar_rate_adjustments.attribute13%TYPE,
2002 p_exchange_rate_attr14 IN ar_rate_adjustments.attribute14%TYPE,
2003 p_exchange_rate_attr15 IN ar_rate_adjustments.attribute15%TYPE) IS
2004
2005 l_radj_rec ar_rate_adjustments%ROWTYPE;
2006 l_radj_id ar_rate_adjustments.rate_adjustment_id%TYPE;
2007 l_crh_id_out ar_cash_receipt_history.cash_receipt_history_id%TYPE;
2008
2009 BEGIN
2010 IF PG_DEBUG in ('Y', 'C') THEN
2011 arp_standard.debug('arp_process_rct_util.rate_adjust()+');
2012 END IF;
2013
2014 l_radj_rec.cash_receipt_id := p_cash_receipt_id;
2015 l_radj_rec.old_exchange_rate := p_old_exchange_rate;
2016 l_radj_rec.old_exchange_date := p_old_exchange_date;
2017 l_radj_rec.old_exchange_rate_type := p_old_exchange_rate_type;
2018 l_radj_rec.new_exchange_rate := p_new_exchange_rate;
2019 l_radj_rec.new_exchange_date := p_new_exchange_date;
2020 l_radj_rec.new_exchange_rate_type := p_new_exchange_rate_type;
2021 l_radj_rec.gain_loss := p_gain_loss;
2022 l_radj_rec.gl_date := p_rate_adjust_gl_date;
2023 l_radj_rec.attribute_category := p_exchange_rate_attr_cat;
2024 l_radj_rec.attribute1 := p_exchange_rate_attr1;
2025 l_radj_rec.attribute2 := p_exchange_rate_attr2;
2026 l_radj_rec.attribute3 := p_exchange_rate_attr3;
2027 l_radj_rec.attribute4 := p_exchange_rate_attr4;
2028 l_radj_rec.attribute5 := p_exchange_rate_attr5;
2029 l_radj_rec.attribute6 := p_exchange_rate_attr6;
2030 l_radj_rec.attribute7 := p_exchange_rate_attr7;
2031 l_radj_rec.attribute8 := p_exchange_rate_attr8;
2032 l_radj_rec.attribute9 := p_exchange_rate_attr9;
2033 l_radj_rec.attribute10 := p_exchange_rate_attr10;
2034 l_radj_rec.attribute11 := p_exchange_rate_attr11;
2035 l_radj_rec.attribute12 := p_exchange_rate_attr12;
2036 l_radj_rec.attribute13 := p_exchange_rate_attr13;
2037 l_radj_rec.attribute14 := p_exchange_rate_attr14;
2038 l_radj_rec.attribute15 := p_exchange_rate_attr15;
2039
2040 l_radj_rec.created_from := 'ARXCAACI';
2041
2042 arp_rate_adjustments_pkg.insert_p(
2043 l_radj_rec,
2044 l_radj_id);
2045
2046 -- Call procedure that performs the rate adjustment processing,
2047 -- e.g. updates receivable applications etc.
2048 arp_rate_adj.main(
2049 p_cash_receipt_id,
2050 p_new_exchange_date,
2051 p_new_exchange_rate,
2052 p_new_exchange_rate_type,
2053 p_rate_adjust_gl_date,
2054 arp_standard.profile.user_id,
2055 sysdate,
2056 arp_standard.profile.user_id,
2057 sysdate,
2058 arp_standard.profile.last_update_login,
2059 TRUE,
2060 l_crh_id_out);
2061
2062 IF PG_DEBUG in ('Y', 'C') THEN
2063 arp_standard.debug('arp_process_rct_util.rate_adjust()-');
2064 END IF;
2065
2066 EXCEPTION
2067 WHEN OTHERS THEN
2068 IF PG_DEBUG in ('Y', 'C') THEN
2069 arp_standard.debug('Exception in arp_process_rct_util.rate_adjust');
2070 END IF;
2071 RAISE;
2072
2073 END rate_adjust;
2074
2075
2076 /*===========================================================================+
2077 | PROCEDURE |
2078 | get_ccids |
2079 | |
2080 | DESCRIPTION |
2081 | determines the ccid's a given remittance bank account and |
2082 | payment method combination. |
2083 | |
2084 | SCOPE - PRIVATE |
2085 | |
2086 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2087 | |
2088 | ARGUMENTS |
2089 | IN: |
2090 | OUT: |
2091 | |
2092 | RETURNS |
2093 | |
2094 | NOTES |
2095 | |
2096 | MODIFICATION HISTORY |
2097 | |
2098 | 29-AUG-95 OSTEINME created |
2099 | |
2100 | 04-NOV-96 OSTEINME changed name from get_ra_ccid to get_ccids |
2101 | and added parameters to allow procedure to |
2102 | return all ccid's for the IN parameters |
2103 | 07-JAN-98 DJANCIS selected code combination id's for earned |
2104 | and unearnd ccids from |
2105 | ar_receivables_trx instead of |
2106 | ar_receipt_method_accounts for |
2107 | 11.5 VAT changes |
2108 | |
2109 +===========================================================================*/
2110
2111 PROCEDURE get_ccids(
2112 p_receipt_method_id IN NUMBER,
2113 p_remittance_bank_account_id IN NUMBER,
2114 p_unidentified_ccid OUT NOCOPY NUMBER,
2115 p_unapplied_ccid OUT NOCOPY NUMBER,
2116 p_on_account_ccid OUT NOCOPY NUMBER,
2117 p_earned_ccid OUT NOCOPY NUMBER,
2118 p_unearned_ccid OUT NOCOPY NUMBER,
2119 p_bank_charges_ccid OUT NOCOPY NUMBER,
2120 p_factor_ccid OUT NOCOPY NUMBER,
2121 p_confirmation_ccid OUT NOCOPY NUMBER,
2122 p_remittance_ccid OUT NOCOPY NUMBER,
2123 p_cash_ccid OUT NOCOPY NUMBER
2124 ) IS
2125 BEGIN
2126
2127 /* selected code combination id's for earned and unearnd ccids from
2128 ar_receivables_trx instead of ar_receipt_method_accounts for
2129 11.5 VAT changes */
2130
2131 SELECT
2132 rma.unidentified_ccid,
2133 rma.unapplied_ccid,
2134 rma.on_account_ccid,
2135 ed.code_combination_id, /* earned_ccid */
2136 uned.code_combination_id, /* unearned_ccid */
2137 rma.bank_charges_ccid,
2138 rma.factor_ccid,
2139 rma.receipt_clearing_ccid,
2140 rma.remittance_ccid,
2141 rma.cash_ccid
2142 INTO
2143 p_unidentified_ccid,
2144 p_unapplied_ccid,
2145 p_on_account_ccid,
2146 p_earned_ccid,
2147 p_unearned_ccid,
2148 p_bank_charges_ccid,
2149 p_factor_ccid,
2150 p_confirmation_ccid,
2151 p_remittance_ccid,
2152 p_cash_ccid
2153 FROM
2154 AR_RECEIPT_METHOD_ACCOUNTS rma,
2155 AR_RECEIVABLES_TRX ed,
2156 AR_RECEIVABLES_TRX uned
2157 WHERE remit_bank_acct_use_id = p_remittance_bank_account_id
2158 AND receipt_method_id = p_receipt_method_id
2159 AND rma.edisc_receivables_trx_id = ed.receivables_trx_id (+)
2160 AND rma.unedisc_receivables_trx_id = uned.receivables_trx_id (+);
2161
2162 EXCEPTION
2163 WHEN OTHERS THEN
2164 IF PG_DEBUG in ('Y', 'C') THEN
2165 arp_standard.debug('EXCEPTION: arp_process_receipts.get_ccids');
2166 END IF;
2167 RAISE;
2168
2169 END get_ccids;
2170
2171 /*===========================================================================+
2172 | PROCEDURE |
2173 | get_ps_rec |
2174 | |
2175 | DESCRIPTION |
2176 | gets the payment schedule record of a receipt given a cash_receipt_id |
2177 | |
2178 | SCOPE - PRIVATE |
2179 | |
2180 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2181 | |
2182 | ARGUMENTS |
2183 | IN: |
2184 | OUT: |
2185 | |
2186 | RETURNS |
2187 | |
2188 | NOTES |
2189 | |
2190 | MODIFICATION HISTORY |
2191 | |
2192 | 19-NOV-96 OSTEINME created |
2193 +===========================================================================*/
2194
2195 PROCEDURE get_ps_rec( p_cash_receipt_id IN NUMBER,
2196 p_ps_rec OUT NOCOPY ar_payment_schedules%ROWTYPE) IS
2197
2198 l_ps_id NUMBER;
2199
2200 BEGIN
2201
2202 SELECT payment_schedule_id
2203 INTO l_ps_id
2204 FROM ar_payment_schedules
2205 WHERE cash_receipt_id = p_cash_receipt_id;
2206
2207 -- the following should utimately be done with nowaitlock_fetch_p (but
2208 -- it didn't exist yet when this function was written and tested).
2209
2210 arp_ps_pkg.fetch_p( l_ps_id,
2211 p_ps_rec);
2212
2213 END get_ps_rec;
2214
2215 /*===========================================================================+
2216 | PROCEDURE |
2217 | update_dist_rec |
2218 | |
2219 | DESCRIPTION |
2220 | updates a record in AR_DISTRIBUTIONS table |
2221 | |
2222 | SCOPE - PRIVATE |
2223 | |
2224 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2225 | |
2226 | ARGUMENTS |
2227 | IN: |
2228 | OUT: |
2229 | |
2230 | RETURNS |
2231 | |
2232 | NOTES |
2233 | |
2234 | MODIFICATION HISTORY |
2235 | |
2236 | 19-NOV-96 OSTEINME created |
2237 +===========================================================================*/
2238
2239 PROCEDURE update_dist_rec( p_crh_id IN NUMBER,
2240 p_source_type IN ar_distributions.source_type%TYPE,
2241 p_amount IN NUMBER,
2242 p_acctd_amount IN NUMBER) IS
2243
2244 l_dist_rec AR_DISTRIBUTIONS%ROWTYPE;
2245 l_amount number;
2246 l_acctd_amount number;
2247 BEGIN
2248
2249 -- fetch existing distributions record for update:
2250
2251 arp_distributions_pkg.nowaitlock_fetch_pk(
2252 p_crh_id,
2253 'CRH',
2254 p_source_type,
2255 l_dist_rec);
2256
2257 /* Commented the following code for bug 2311742
2258 IF (p_amount < 0) THEN
2259 l_dist_rec.amount_dr := NULL;
2260 l_dist_rec.amount_cr := - p_amount;
2261 ELSE
2262 l_dist_rec.amount_dr := p_amount;
2263 l_dist_rec.amount_cr := NULL;
2264 END IF;
2265
2266 IF (p_acctd_amount < 0) THEN
2267 l_dist_rec.acctd_amount_dr := NULL;
2268 l_dist_rec.acctd_amount_cr := - p_acctd_amount;
2269 ELSE
2270 l_dist_rec.acctd_amount_dr := p_acctd_amount;
2271 l_dist_rec.acctd_amount_cr := NULL;
2272 END IF; */
2273
2274 /* Added the following code for bug 2311742 */
2275
2276 IF (l_dist_rec.amount_dr is not null) THEN
2277 l_amount := l_dist_rec.amount_dr + p_amount;
2278
2279 If (l_amount > 0) then
2280 l_dist_rec.amount_dr := l_amount;
2281 l_dist_rec.amount_cr := null;
2282 else
2283 l_dist_rec.amount_cr := - l_amount;
2284 l_dist_rec.amount_dr := null;
2285 End if;
2286 ELSE
2287 l_amount := l_dist_rec.amount_cr - p_amount;
2288
2289 If ( l_amount >= 0) then
2290 l_dist_rec.amount_dr := null;
2291 l_dist_rec.amount_cr := l_amount;
2292 Else
2293 l_dist_rec.amount_dr := - l_amount;
2294 l_dist_rec.amount_cr := null;
2295 End if;
2296 END IF;
2297
2298 IF (l_dist_rec.acctd_amount_dr is not null) THEN
2299 l_acctd_amount := l_dist_rec.acctd_amount_dr + p_acctd_amount;
2300
2301 If (l_acctd_amount > 0) then
2302 l_dist_rec.acctd_amount_dr := l_acctd_amount;
2303 l_dist_rec.acctd_amount_cr := null;
2304 Else
2305 l_dist_rec.acctd_amount_cr := - l_acctd_amount;
2306 l_dist_rec.acctd_amount_dr := null;
2307 End if;
2308
2309 ELSE
2310 l_acctd_amount := l_dist_rec.acctd_amount_cr - p_acctd_amount;
2311
2312 If (l_acctd_amount >= 0) then
2313 l_dist_rec.acctd_amount_dr := null;
2314 l_dist_rec.acctd_amount_cr := l_acctd_amount;
2315 Else
2316 l_dist_rec.acctd_amount_dr := - l_acctd_amount;
2317 l_dist_rec.acctd_amount_cr := null;
2318 End if;
2319 END IF;
2320 /* End of bug 2311742 */
2321
2322 arp_distributions_pkg.update_p(l_dist_rec);
2323
2324 /* need to update records into the MRC table. Calling new
2325 mrc engine */
2326 /*4301323
2327 ar_mrc_engine2.maintain_mrc_data2(
2328 p_event_mode => 'UPDATE',
2329 p_table_name => 'AR_DISTRIBUTIONS',
2330 p_mode => 'SINGLE',
2331 p_key_value => l_dist_rec.line_id,
2332 p_row_info => l_dist_rec);
2333 */
2334 END update_dist_rec;
2335 END ARP_PROC_RCT_UTIL;