4 /* Private procedures added for bug 2670619 */
1 PACKAGE BODY AR_CC_LOCKBOX AS
2 /* $Header: ARCCLOCB.pls 120.11.12020000.2 2012/10/12 11:32:49 aghoraka ship $ */
3
5 /*Bug 44509019 used NOCOPY hint to remove GSCC warnings file.sql.39*/
6 PROCEDURE calc_amt_applied_from_fmt (
7 p_currency_code IN VARCHAR2,
8 p_amount_applied IN ar_payments_interface.amount_applied1%type,
9 p_trans_to_receipt_rate IN ar_payments_interface.trans_to_receipt_rate1%type,
10 amount_applied_from OUT NOCOPY ar_payments_interface.amount_applied_from1%type,
11 p_format_amount IN VARCHAR2
12 );
13
14 PROCEDURE calc_amt_applied_fmt (
15 p_invoice_currency_code IN VARCHAR2,
16 p_amount_applied_from IN ar_payments_interface.amount_applied_from1%type,
17 p_trans_to_receipt_rate IN ar_payments_interface.trans_to_receipt_rate1%type,
18 amount_applied OUT NOCOPY ar_payments_interface.amount_applied1%type,
19 p_format_amount IN VARCHAR2
20 );
21 --
22
23 /*===========================================================================+
24 | PROCEDURE
25 | calc_cross_rate
26 | DESCRIPTION
27 | Populates the item "Rate". This is calculated when
28 | both the Amount Applied and Allocated Receipt Amount have both been
29 | entered. Calculated as Allocated Receipt Amount divided by the
30 | Amount Applied, i.e. it is the rate that the Amount Applied is
31 | multiplied by to get to the Allocated Receipt Amount.
32 |
33 | The majority of this procedure was copied from the GUI applications
34 | form. This is the routine that was used for the Release 11
35 | cross currency receipts enhancements. It is being expanded for
36 | cross currency lockbox/postbatch enhancements.
37 |
38 | We are using an interesting algorithm to get to the "final"
39 | or defaulted value. The ultimate aim is to default a rate that
40 | is the smallest possible number and we want to have a situation
41 | where
42 |
43 | rounded(amount_applied * trans_to_receipt_rate) =
44 | rounded(allocated_receipt_amount)
45 |
46 | where rounding in based on the receipt currency.
47 |
48 | For example:
49 | Amount Applied = 90CND
50 | Allocated Receipt Amount = 200USD
51 |
52 | Cross Currency Rate = 200/90 = 2.222 recurring
53 |
54 | The derived rate however will be 2.2222, as
55 | 2.2222 * 90 = 199.998 = 200 (rounded) which =
56 | Allocted Receipt Amount 200
57 |
58 | See notes in code to see how we actually do this!
59 |
60 | SCOPE - PUBLIC
61 |
62 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE
63 |
64 | ARGUMENTS : IN:
65 | amount_applied
66 | amount_applied_from
67 | p_inv_curr_code
68 | p_rec_curr_code
69 |
70 | IN OUT NOCOPY :
71 |
72 | OUT NOCOPY :
73 | trans_to_receipt_rate
74 |
75 | RETURNS :
76 |
77 | NOTES : since this module calculates the most rounded rate to
78 | convert the 2 numbers, it will also be used by postbatch
79 | to calculate the receipt_to_trans_rate which is just the
80 | inverse of the trans_to_receipt_rate or the rate that
81 | can be used to convert the receipt amount to the
82 | transaction amount. The difference is that the calling
83 | routine will pass the amount_applied_from as the amount
84 | applied and the amount_applied as the amount_applied_from
85 | MODIFICATION HISTORY:
86 | 10/22/1998 Debbie Jancis copied from ARXRWAPP and modified for use
87 | with cross currency lockbox.
88 | 12/07/99 RYELURI To fix bug numbers 1052313 and 1097549
89 | Added the statement ps.status = 'OP'
90 | and ps.class NOT IN ('PMT','GUAR')
91 | in populate populate_add_inv_details proc
92 | 04/14/00 RYELURI To fix bug number 1198572
93 | Modifications done in populate_add_inv..
94 | function in this package. Modifications
95 | consist of a) Check to see if the current
96 | invoice is duplicate transaction and if so
97 | all the payment schedules on the original
98 | transaction are closed. Otherwise Lockbox
99 | will give the msg that the transaction is
100 | a duplicate. b) If multiple payment terms
101 | exist, then receipt is applied to the
102 | minimum of the payment schedule that has
103 | status is open, if overapplication is allowed
104 | then status if of no concern.
105 | These modifications are done as part of Lock
106 | box enhancements to be able to apply receipts
110 | replaced with hz counterparts
107 | to invoices with Multiple payment terms.
108 | 10/26/00 Debbie Jancis Modified for tca uptake. Removed all
109 | references to ar/ra customer tables and
111 +---------------------------------------------------------------------------*/
112
113
114 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
115
116 PROCEDURE calc_cross_rate (
117 p_amount_applied IN ar_payments_interface.amount_applied1%TYPE,
118 p_amount_applied_from IN ar_payments_interface.amount_applied_from1%TYPE,
119 p_inv_curr_code IN ar_payments_interface.invoice_currency_code1%TYPE,
120 p_rec_curr_code IN ar_payments_interface.currency_code%TYPE,
121 p_cross_rate OUT NOCOPY NUMBER ) IS
122
123 ln_start_rounding NUMBER;
124 ln_amount_applied NUMBER;
125 ln_amount_applied_from NUMBER;
126 ln_test_trans_to_receipt_rate NUMBER;
127 ln_amount_applied_X_rate NUMBER;
128 ln_trans_to_receipt_rate NUMBER;
129 l_mau NUMBER;
130 l_precision NUMBER(1);
131 l_extended_precision NUMBER;
132
133
134 BEGIN
135
136 debug1( 'calc_cross_rate()+' );
137 debug1('p_amount_applied = ' || to_char(p_amount_applied));
138 debug1('p_amount_applied_from = ' || to_char(p_amount_applied_from));
139 debug1('p inv curr cord = ' || p_inv_curr_code);
140 debug1('p rec curr cord = ' || p_rec_curr_code);
141
142
143 /* If we are applying a value of zero, copy null into the Cross Currency
144 Rate item and exit the procedure. */
145
146 IF (p_amount_applied = 0 OR
147 p_amount_applied_from = 0) THEN
148 p_cross_rate := null;
149 debug1('amount_applied or amount_applied from = 0');
150 ELSE
151
152 /* Populate "starting" values */
153 debug1('populate starting values...');
154 fnd_currency.Get_Info(p_inv_curr_code,
155 l_precision,
156 l_extended_precision,
157 l_mau);
158 debug1('populate ln_amount_applied');
159 IF l_mau IS NOT NULL
160 THEN
161 ln_amount_applied := ROUND(p_amount_applied / l_mau) * l_mau;
162 ELSE
163 ln_amount_applied := ROUND(p_amount_applied, l_precision);
164 END IF;
165
166 fnd_currency.Get_Info(p_rec_curr_code,
167 l_precision,
168 l_extended_precision,
169 l_mau);
170 debug1('populate ln_amount_applied_from .. ');
171 IF l_mau IS NOT NULL
172 THEN
173 ln_amount_applied_from := ROUND(p_amount_applied_from / l_mau) * l_mau;
174 ELSE
175 ln_amount_applied_from := ROUND(p_amount_applied_from, l_precision);
176 END IF;
177
178 ln_trans_to_receipt_rate := p_amount_applied_from / p_amount_applied;
179 ln_amount_applied_X_rate := 0;
180
181
182 /* -----------------------------------------------------------------------
183
184 The following nightmare is as a result of the LOG number
185 function not being
186 available in PL/SQL version 1.0.
187 What we are trying to do is to work out NOCOPY is at what
188 point should we start
189 rounding the rate.
190 For example, if the start rate is 23.12345,
191 will want to start rounding at
192 -1, for a rate 123.12345, we need to start at -2 etc
193
194 As the LOG function is not available, we have to work
195 it out NOCOPY ourselves.
196 Assuming that the maximum rate that should ever be
197 calculated is 100000000000.
198 If it is ever greater than this an error message is
199 displayed indicating that
200 this is an unusally large rate and something must be wrong!
201 Going up to 100000000000 to cater for the "depression" times,
202 when it costs
203 a wheel barrow full of money just to buy a loaf of bread!
204
205 --------------------------------------------------------------*/
206
207 IF ln_trans_to_receipt_rate > 100000000000 THEN
208 FND_MESSAGE.SET_NAME('AR','AR_RW_CROSS_CC_RATE_LARGE');
209 app_exception.raise_exception;
210 ELSIF ln_trans_to_receipt_rate >= 10000000000 and ln_trans_to_receipt_rate <= 99999999999 THEN
211 ln_start_rounding := -10;
212 ELSIF ln_trans_to_receipt_rate >= 1000000000 and ln_trans_to_receipt_rate <= 9999999999 THEN
213 ln_start_rounding := -9;
214 ELSIF ln_trans_to_receipt_rate >= 100000000 and ln_trans_to_receipt_rate <= 999999999 THEN
215 ln_start_rounding := -8;
216 ELSIF ln_trans_to_receipt_rate >= 10000000 and ln_trans_to_receipt_rate <= 99999999 THEN
217 ln_start_rounding := -7;
218 ELSIF ln_trans_to_receipt_rate >= 1000000 and ln_trans_to_receipt_rate <= 9999999 THEN
219 ln_start_rounding := -6;
220 ELSIF ln_trans_to_receipt_rate >= 100000 and ln_trans_to_receipt_rate <= 999999 THEN
221 ln_start_rounding := -5;
222 ELSIF ln_trans_to_receipt_rate >= 10000 and ln_trans_to_receipt_rate <= 99999 THEN
223 ln_start_rounding := -4;
224 ELSIF ln_trans_to_receipt_rate >= 1000 and ln_trans_to_receipt_rate <= 9999 THEN
225 ln_start_rounding := -3;
226 ELSIF ln_trans_to_receipt_rate >= 100 and ln_trans_to_receipt_rate <= 999 THEN
227 ln_start_rounding := -2;
228 ELSIF ln_trans_to_receipt_rate >= 10 and ln_trans_to_receipt_rate <= 99 THEN
229 ln_start_rounding := -1;
230 ELSE
231 ln_start_rounding := 0;
232 END IF;
233
234 /* --------------------------------------------------------------------
238 rounded(allocated_receipt_amount)
235 Once we know at what point to start we can then round
236 this number until we reach a situation where
237 rounded(amount_applied * trans_to_receipt_rate) =
239
240 Only going to 32, as this is the maximum size of the
241 field (plus 10 before decimal).
242
243 -- Given our example:
244 -- Amount Applied = 90CND
245 -- Allocated Receipt Amount = 200USD
246 --
247 -- Start rate = 200/90 = 2.222 recurring
248 -- Start rounding at 0
249 -- 0 Try rate 2 ... round(2 * 90) = 180 <> 200
250 -- 1 Try rate 2.2 ... round(2.2 * 90) = 198 <> 200
251 -- 2 Try rate 2.22 ... round(2.22 * 90) = 199.80 <> 200 ...
252 getting closer though
253 -- 3 Try rate 2.222 ... round(2.222 * 90) = 199.98 <> 200 ... nearly there
254 -- 4 Try rate 2.2222 ... round(2.2222 * 90) = 200 ... made it!
255 --------------------------------------------------------------- */
256 FOR round_integer in ln_start_rounding .. 32
257 LOOP
258 ln_test_trans_to_receipt_rate := round(ln_trans_to_receipt_rate, round_integer);
259 IF ln_test_trans_to_receipt_rate = 0
260 THEN
261 null;
262 ELSE
263
264 fnd_currency.Get_Info(p_inv_curr_code,
265 l_precision,
266 l_extended_precision,
267 l_mau);
268 IF l_mau IS NOT NULL
269 THEN
270 ln_amount_applied_X_rate :=
271 ROUND((ln_amount_applied * ln_test_trans_to_receipt_rate)
272 / l_mau) * l_mau;
273 ELSE
274 ln_amount_applied_X_rate :=
275 ROUND((ln_amount_applied * ln_test_trans_to_receipt_rate),
276 l_precision);
277 END IF;
278
279 IF ln_amount_applied_X_rate = ln_amount_applied_from
280 THEN
281 exit;
282 END IF;
283 END IF;
284 END LOOP;
285
286 /* Copy the derived value into the item and set to valid. */
287 p_cross_rate := ln_test_trans_to_receipt_rate;
288
289 END IF; /* Either amounts zero. */
290
291 debug1 ('calc_cross_rate()-');
292
293 EXCEPTION
294 WHEN OTHERS THEN
295 debug1( 'Exception: calc_cross_rate()');
296 END calc_cross_rate;
297
298
299 /*===========================================================================+
300 | PROCEDURE
301 | populate_add_inv_details
302 | DESCRIPTION
303 | This procedure will populate additional details about an application
304 | if required. Because of the flexability that we allow in the
305 | transmission file for lockbox, a user can give us limited details
306 | of a cross currency /EURO application and we can calculate the
307 | additional columns if needed.
308 |
309 | For a Euro case, we assume that the user will give us either the
310 | amount_applied or amount_applied_from. Since it is a fixed rate,
311 | we can calculate the other amount from the fixed rate that we will
312 | get from GL.
313 |
314 | For a standard cross currency Case, we expect to have 2 of the
315 | following: amount_applied, amount_applied_from, or the
316 | trans_to_receipt_rate. If we only have one amount, and the
317 | profile is set to allow us to try to derive the rate from gl, and
318 | there is a rate for this cross currency case, we can derive the
319 | other amount value. If two amounts are given, we can calculate
320 | the rate.
321 |
322 | SCOPE - PUBLIC
323 |
324 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE
325 |
326 | ARGUMENTS : IN:
327 |
328 | IN OUT NOCOPY :
329 |
330 | OUT NOCOPY :
331 |
332 | RETURNS :
333 |
334 | NOTES
335 | MODIFICATION HISTORY:
336 | 10/26/1998 Debbie Jancis original
337 | 01/04/1999 Debbie Jancis Need to retrieve the receipt date from
338 | the receipt record (as it is not present
339 | on the overflow record). This assumes that
340 | there is only 1 receipt record with the same
341 | item number.
342 | 12/12/00 Shin Matsuda Bug 1519765.
343 | We should reject cross-currency receipt if
344 | only amount_applied of ammount_applied_from
345 | (not both) is provided, and trans_to_receipt
346 | rate is not provided, and not fixed rate,
347 | and default_exchange_rate_type is not
348 | defined.
349 | 12/12/2000 Debbie Jancis fixed bug 1513671: amount_applied was
350 | being rounded with the receipt precision.
351 | 01/19/2001 Shin Matsuda Bug 1585615. Unhandled exception if
352 | trans_to_receipt_rate is zero.
353 | Fixed the problem by regarding zero-rate
354 | as null.
355 | 05/21/2001 Debbie Jancis Added bill_to_flag in all queries to
356 | hz_cust_acct_relate to specifically check
357 | for a Bill_to Relationship instead of
358 | just the existance of a record in the table
359 | 10/20/2001 Chelvi B. Bug 2066679 Instead of updating
360 | invoice2_status with AR_PLB_CURRENCY_BAD
364 | Corrected all of them.
361 | invoice1_status was updated wrongly.
362 | 10/23/2001 Shin Matsuda There are some other places where it updates
363 | invoice1_status instead of invoiceN_status.
365 | 10/31/2001 Shin Matsuda Bug 2066392. Populate amount_appliedN with
366 | amount_applied_fromN if single currency
367 | application.
368 | 11/21/2001 Shin Matsuda Bug 2057282. Validate/populate currency
369 | and amount info only if l_tot_trxN is 1.
370 | 11/23/2001 Shin Matsuda Bug 2119596. Backed out NOCOPY the previous fix.
371 | Put exception clause for no_data_found case.
372 | 10/28/2002 Shin Matsuda Bug 2626005. Modified where clause of most
373 | update statements. We validate CC info row
374 | by row and we know rowid of the record, thus
375 | the where clause can be simple rowid=l_rowid
376 | 05/07/2003 Rahna Kader Bug 2926664.Modified the select statement
377 | which is used for setting AR_PLB_DUP_INV.
378 | Also modified the select statement which selects
379 | the invoice currency code and amount due
380 | remaining.
381 | 08/14/2003 H Yoshihara Bug 2980051. Replaced l_only_one_lb with
382 | l_no_batch_or_lb in get_app_info cursor
383 | to identify record correctly.
384 | 22/10/2003 SAPN Sarma Bug 3113104. Replaced the get_rate function with
385 | get_rate_sql function.Also, made a check for the
386 | exchange_rate_value that is returned.
387 +---------------------------------------------------------------------------*/
388
389
390 PROCEDURE populate_add_inv_details(
391 p_transmission_id IN VARCHAR2,
392 p_payment_rec_type IN VARCHAR2,
393 p_overflow_rec_type IN VARCHAR2,
394 p_item_num IN ar_payments_interface.item_number%type,
395 p_batch_name IN ar_payments_interface.batch_name%type,
396 p_lockbox_number IN ar_payments_interface.lockbox_number%type,
397 p_batches IN VARCHAR2,
398 p_only_one_lb IN VARCHAR2,
399 p_pay_unrelated_invoices IN VARCHAR2,
400 p_default_exchange_rate_type IN VARCHAR2,
401 enable_cross_currency IN VARCHAR2,
402 p_format_amount1 IN VARCHAR,
403 p_format_amount2 IN VARCHAR,
404 p_format_amount3 IN VARCHAR,
405 p_format_amount4 IN VARCHAR,
406 p_format_amount5 IN VARCHAR,
407 p_format_amount6 IN VARCHAR,
408 p_format_amount7 IN VARCHAR,
409 p_format_amount8 IN VARCHAR,
410 p_format_amount_applied_from1 IN VARCHAR,
411 p_format_amount_applied_from2 IN VARCHAR,
412 p_format_amount_applied_from3 IN VARCHAR,
413 p_format_amount_applied_from4 IN VARCHAR,
414 p_format_amount_applied_from5 IN VARCHAR,
415 p_format_amount_applied_from6 IN VARCHAR,
416 p_format_amount_applied_from7 IN VARCHAR,
417 p_format_amount_applied_from8 IN VARCHAR
418 ) IS
419 --
420 l_transmission_id VARCHAR2(50);
421 l_payment_rec_type VARCHAR2(3);
422 l_overflow_rec_type VARCHAR2(3);
423 l_item_num ar_payments_interface.item_number%type;
424 l_batch_name ar_payments_interface.batch_name%type;
425 l_lockbox_number ar_payments_interface.lockbox_number%type;
426 l_batches VARCHAR2(2);
427 l_only_one_lb VARCHAR2(2);
428 l_use_matching_date ar_lockboxes.use_matching_date%type;
429 l_lockbox_matching_option ar_lockboxes.lockbox_matching_option%type;
430 l_match_flag VARCHAR2(10);
431 l_rowid ROWID;
432 l_pay_unrelated_invoices VARCHAR2(2);
433 l_is_fixed_rate VARCHAR2(1);
434 l_default_exchange_rate_type VARCHAR2(31);
435 l_enable_cross_currency VARCHAR2(2);
436
437 --
438 l_format_amount1 VARCHAR2(2);
439 l_format_amount2 VARCHAR2(2);
440 l_format_amount3 VARCHAR2(2);
441 l_format_amount4 VARCHAR2(2);
442 l_format_amount5 VARCHAR2(2);
443 l_format_amount6 VARCHAR2(2);
444 l_format_amount7 VARCHAR2(2);
445 l_format_amount8 VARCHAR2(2);
446 l_format_amount_applied_from1 VARCHAR2(2);
447 l_format_amount_applied_from2 VARCHAR2(2);
448 l_format_amount_applied_from3 VARCHAR2(2);
449 l_format_amount_applied_from4 VARCHAR2(2);
450 l_format_amount_applied_from5 VARCHAR2(2);
451 l_format_amount_applied_from6 VARCHAR2(2);
452 l_format_amount_applied_from7 VARCHAR2(2);
453 l_format_amount_applied_from8 VARCHAR2(2);
454 --
455 l_mau NUMBER;
456 l_precision NUMBER(1);
457 l_extended_precision NUMBER;
458 --
459 -- bug fix 1198572
460 l_tot_trx1 NUMBER;
461 l_tot_trx2 NUMBER;
462 l_tot_trx3 NUMBER;
463 l_tot_trx4 NUMBER;
464 l_tot_trx5 NUMBER;
465 l_tot_trx6 NUMBER;
466 l_tot_trx7 NUMBER;
467 l_tot_trx8 NUMBER;
468
469 /* declare matching invoice numbers */
470 l_matching_number1 ar_payments_interface.invoice1%type;
471 l_matching_number2 ar_payments_interface.invoice2%type;
472 l_matching_number3 ar_payments_interface.invoice3%type;
473 l_matching_number4 ar_payments_interface.invoice4%type;
474 l_matching_number5 ar_payments_interface.invoice5%type;
475 l_matching_number6 ar_payments_interface.invoice6%type;
476 l_matching_number7 ar_payments_interface.invoice7%type;
480 l_trans_to_receipt_rate2 ar_payments_interface.trans_to_receipt_rate2%type;
477 l_matching_number8 ar_payments_interface.invoice8%type;
478 --
479 l_trans_to_receipt_rate1 ar_payments_interface.trans_to_receipt_rate1%type;
481 l_trans_to_receipt_rate3 ar_payments_interface.trans_to_receipt_rate3%type;
482 l_trans_to_receipt_rate4 ar_payments_interface.trans_to_receipt_rate4%type;
483 l_trans_to_receipt_rate5 ar_payments_interface.trans_to_receipt_rate5%type;
484 l_trans_to_receipt_rate6 ar_payments_interface.trans_to_receipt_rate6%type;
485 l_trans_to_receipt_rate7 ar_payments_interface.trans_to_receipt_rate7%type;
486 l_trans_to_receipt_rate8 ar_payments_interface.trans_to_receipt_rate8%type;
487 --
488 l_invoice_currency_code1 ar_payments_interface.invoice_currency_code1%type;
489 l_invoice_currency_code2 ar_payments_interface.invoice_currency_code2%type;
490 l_invoice_currency_code3 ar_payments_interface.invoice_currency_code3%type;
491 l_invoice_currency_code4 ar_payments_interface.invoice_currency_code4%type;
492 l_invoice_currency_code5 ar_payments_interface.invoice_currency_code5%type;
493 l_invoice_currency_code6 ar_payments_interface.invoice_currency_code6%type;
494 l_invoice_currency_code7 ar_payments_interface.invoice_currency_code7%type;
495 l_invoice_currency_code8 ar_payments_interface.invoice_currency_code8%type;
496 --
497 l_amount_applied1 ar_payments_interface.amount_applied1%type;
498 l_amount_applied2 ar_payments_interface.amount_applied2%type;
499 l_amount_applied3 ar_payments_interface.amount_applied3%type;
500 l_amount_applied4 ar_payments_interface.amount_applied4%type;
501 l_amount_applied5 ar_payments_interface.amount_applied5%type;
502 l_amount_applied6 ar_payments_interface.amount_applied6%type;
503 l_amount_applied7 ar_payments_interface.amount_applied7%type;
504 l_amount_applied8 ar_payments_interface.amount_applied8%type;
505 --
506 l_amount_applied_from1 ar_payments_interface.amount_applied_from1%type;
507 l_amount_applied_from2 ar_payments_interface.amount_applied_from2%type;
508 l_amount_applied_from3 ar_payments_interface.amount_applied_from3%type;
509 l_amount_applied_from4 ar_payments_interface.amount_applied_from4%type;
510 l_amount_applied_from5 ar_payments_interface.amount_applied_from5%type;
511 l_amount_applied_from6 ar_payments_interface.amount_applied_from6%type;
512 l_amount_applied_from7 ar_payments_interface.amount_applied_from7%type;
513 l_amount_applied_from8 ar_payments_interface.amount_applied_from8%type;
514 --
515 /* declare variable for currency code of the receipt */
516 l_currency_code ar_payments_interface.currency_code%type;
517 --
518 /* declare variables to hold the currency code from payment schedules */
519 ps_currency_code1 ar_payment_schedules.invoice_currency_code%type;
520 ps_currency_code2 ar_payment_schedules.invoice_currency_code%type;
521 ps_currency_code3 ar_payment_schedules.invoice_currency_code%type;
522 ps_currency_code4 ar_payment_schedules.invoice_currency_code%type;
523 ps_currency_code5 ar_payment_schedules.invoice_currency_code%type;
524 ps_currency_code6 ar_payment_schedules.invoice_currency_code%type;
525 ps_currency_code7 ar_payment_schedules.invoice_currency_code%type;
526 ps_currency_code8 ar_payment_schedules.invoice_currency_code%type;
527 --
528 /* Bug 883345: need to retrieve the amount_due_remaining of the transaction*/
529
530 trx_amt_due_rem1 ar_payment_schedules.amount_due_remaining%type;
531 trx_amt_due_rem2 ar_payment_schedules.amount_due_remaining%type;
532 trx_amt_due_rem3 ar_payment_schedules.amount_due_remaining%type;
533 trx_amt_due_rem4 ar_payment_schedules.amount_due_remaining%type;
534 trx_amt_due_rem5 ar_payment_schedules.amount_due_remaining%type;
535 trx_amt_due_rem6 ar_payment_schedules.amount_due_remaining%type;
536 trx_amt_due_rem7 ar_payment_schedules.amount_due_remaining%type;
537 trx_amt_due_rem8 ar_payment_schedules.amount_due_remaining%type;
538 amt_applied_from_tc ar_payments_interface.amount_applied_from1%type;
539
540 l_receipt_date ar_payments_interface.receipt_date%type;
541 --
542 /* temporary variables used to check if amount_applied, amount_applied_from
543 and trans_to_receipt rates are given */
544 l_temp_amt_applied ar_payments_interface.amount_applied1%type;
545 l_temp_amt_applied_from ar_payments_interface.amount_applied_from1%type;
546 l_temp_trans_to_receipt_rate ar_payments_interface.trans_to_receipt_rate1%type;
547 --
548 /* Added to fix Bug 1052313 */
549 l_resolved_matching1_date ar_payments_interface.resolved_matching1_date%type;
550 l_resolved_matching2_date ar_payments_interface.resolved_matching1_date%type;
551 l_resolved_matching3_date ar_payments_interface.resolved_matching1_date%type;
552 l_resolved_matching4_date ar_payments_interface.resolved_matching1_date%type;
553 l_resolved_matching5_date ar_payments_interface.resolved_matching1_date%type;
554 l_resolved_matching6_date ar_payments_interface.resolved_matching1_date%type;
555 l_resolved_matching7_date ar_payments_interface.resolved_matching1_date%type;
556 l_resolved_matching8_date ar_payments_interface.resolved_matching1_date%type;
557
558 l_resolved_matching1_inst ar_payments_interface.resolved_matching1_installment%type;
559 l_resolved_matching2_inst ar_payments_interface.resolved_matching1_installment%type;
560 l_resolved_matching3_inst ar_payments_interface.resolved_matching1_installment%type;
561 l_resolved_matching4_inst ar_payments_interface.resolved_matching1_installment%type;
562 l_resolved_matching5_inst ar_payments_interface.resolved_matching1_installment%type;
563 l_resolved_matching6_inst ar_payments_interface.resolved_matching1_installment%type;
564 l_resolved_matching7_inst ar_payments_interface.resolved_matching1_installment%type;
568
565 l_resolved_matching8_inst ar_payments_interface.resolved_matching1_installment%type;
566
567 l_customer_id ar_payments_interface.customer_id%type;
569 /* Bugfix 1732391 */
570 l_no_batch_or_lb varchar2(2);
571
572 /* To fix bug 2066392 */
573 l_unformat_amount ar_payments_interface.amount_applied1%type;
574 --
575
576 unexpected_program_error EXCEPTION;
577 --
578 --
579 /* Modified cusrsor definition for bug 1052313 and 1087549. Added columns 3,4,5 to the
580 select list */
581
582 /* Bug 1513671: modified cursor because amount_applied was being rounded
583 to precision of receipt currency */
584 /* Bug 2980051: Replaced l_only_one_lb with l_no_batch_or_lb */
585
586 CURSOR get_app_info IS
587 select
588 pi.rowid,
589 pi.resolved_matching_number1,
590 pi.resolved_matching1_date,
591 pi.resolved_matching1_installment,
592 pi.customer_id,
593 pi.trans_to_receipt_rate1,
594 pi.invoice_currency_code1,
595 pi.amount_applied1,
596 decode(l_format_amount_applied_from1,'Y',
597 round(pi.amount_applied_from1/power(10,fc.precision),
598 fc.precision),
599 pi.amount_applied_from1),
600 pi.resolved_matching_number2,
601 pi.resolved_matching2_date,
602 pi.resolved_matching2_installment,
603 pi.trans_to_receipt_rate2,
604 pi.invoice_currency_code2,
605 pi.amount_applied2,
606 decode(l_format_amount_applied_from2,'Y',
607 round(pi.amount_applied_from2/power(10,fc.precision),
608 fc.precision),
609 pi.amount_applied_from2),
610 pi.resolved_matching_number3,
611 pi.resolved_matching3_date,
612 pi.resolved_matching3_installment,
613 pi.trans_to_receipt_rate3,
614 pi.invoice_currency_code3,
615 pi.amount_applied3,
616 decode(l_format_amount_applied_from3,'Y',
617 round(pi.amount_applied_from3/power(10,fc.precision),
618 fc.precision),
619 pi.amount_applied_from3),
620 pi.resolved_matching_number4,
621 pi.resolved_matching4_date,
622 pi.resolved_matching4_installment,
623 pi.trans_to_receipt_rate4,
624 pi.invoice_currency_code4,
625 pi.amount_applied4,
626 decode(l_format_amount_applied_from4,'Y',
627 round(pi.amount_applied_from4/power(10,fc.precision),
628 fc.precision),
629 pi.amount_applied_from4),
630 pi.resolved_matching_number5,
631 pi.resolved_matching5_date,
632 pi.resolved_matching5_installment,
633 pi.trans_to_receipt_rate5,
634 pi.invoice_currency_code5,
635 pi.amount_applied5,
636 decode(l_format_amount_applied_from5,'Y',
637 round(pi.amount_applied_from5/power(10,fc.precision),
638 fc.precision),
639 pi.amount_applied_from5),
640 pi.resolved_matching_number6,
641 pi.resolved_matching6_date,
642 pi.resolved_matching6_installment,
643 pi.trans_to_receipt_rate6,
644 pi.invoice_currency_code6,
645 pi.amount_applied6,
646 decode(l_format_amount_applied_from6,'Y',
647 round(pi.amount_applied_from6/power(10,fc.precision),
648 fc.precision),
649 pi.amount_applied_from6),
650 pi.resolved_matching_number7,
651 pi.resolved_matching7_date,
652 pi.resolved_matching7_installment,
653 pi.trans_to_receipt_rate7,
654 pi.invoice_currency_code7,
655 pi.amount_applied7,
656 decode(l_format_amount_applied_from7,'Y',
657 round(pi.amount_applied_from7/power(10,fc.precision),
658 fc.precision),
659 pi.amount_applied_from7),
660 pi.resolved_matching_number8,
661 pi.resolved_matching8_date,
662 pi.resolved_matching8_installment,
663 pi.trans_to_receipt_rate8,
664 pi.invoice_currency_code8,
665 pi.amount_applied8,
666 decode(l_format_amount_applied_from8,'Y',
667 round(pi.amount_applied_from8/power(10,fc.precision),
668 fc.precision),
669 pi.amount_applied_from8),
670 pi.currency_code, /* currency code of the receipt */
671 pi.receipt_date
672 from ar_payments_interface pi, fnd_currencies fc
673 where pi.transmission_id = l_transmission_id
674 and pi.record_type||'' in ( l_payment_rec_type, l_overflow_rec_type )
675 and pi.item_number = l_item_num
676 and pi.currency_code = fc.currency_code
677 and ( pi.batch_name = l_batch_name
678 or
679 ( pi.lockbox_number = l_lockbox_number
680 and
681 l_batches = 'N'
682 )
683 or
684 l_no_batch_or_lb = 'Y'
685 );
686 --
687 BEGIN
688 l_format_amount1 := p_format_amount1;
689 l_format_amount2 := p_format_amount2;
690 l_format_amount3 := p_format_amount3;
691 l_format_amount4 := p_format_amount4;
692 l_format_amount5 := p_format_amount5;
693 l_format_amount6 := p_format_amount6;
694 l_format_amount7 := p_format_amount7;
695 l_format_amount8 := p_format_amount8;
696 l_format_amount_applied_from1 := p_format_amount_applied_from1;
697 l_format_amount_applied_from2 := p_format_amount_applied_from2;
701 l_format_amount_applied_from6 := p_format_amount_applied_from6;
698 l_format_amount_applied_from3 := p_format_amount_applied_from3;
699 l_format_amount_applied_from4 := p_format_amount_applied_from4;
700 l_format_amount_applied_from5 := p_format_amount_applied_from5;
702 l_format_amount_applied_from7 := p_format_amount_applied_from7;
703 l_format_amount_applied_from8 := p_format_amount_applied_from8;
704 --
705 -- Assign Variables to local values:
706 --
707 l_transmission_id := p_transmission_id;
708 l_payment_rec_type := p_payment_rec_type;
709 l_overflow_rec_type := p_overflow_rec_type;
710 l_item_num := p_item_num;
711 l_batch_name := p_batch_name;
712 l_lockbox_number := p_lockbox_number;
713 l_batches := p_batches;
714 l_only_one_lb := p_only_one_lb;
715 l_pay_unrelated_invoices := p_pay_unrelated_invoices;
716 l_default_exchange_rate_type := p_default_exchange_rate_type;
717 l_enable_cross_currency := enable_cross_currency;
718 /* Bugfix 1732391 */
719 IF l_batches = 'N' AND l_only_one_lb = 'Y' THEN
720 l_no_batch_or_lb := 'Y';
721 ELSE
722 l_no_batch_or_lb := 'N';
723 END IF;
724
725 --
726
727 OPEN get_app_info;
728 debug1('Opened cursor get_app_info.');
729 --
730 LOOP
731 FETCH get_app_info INTO
732 l_rowid,
733 l_matching_number1,
734 l_resolved_matching1_date,
735 l_resolved_matching1_inst,
736 l_customer_id,
737 l_trans_to_receipt_rate1,
738 l_invoice_currency_code1,
739 l_amount_applied1,
740 l_amount_applied_from1,
741 l_matching_number2,
742 l_resolved_matching2_date,
743 l_resolved_matching2_inst,
744 l_trans_to_receipt_rate2,
745 l_invoice_currency_code2,
746 l_amount_applied2,
747 l_amount_applied_from2,
748 l_matching_number3,
749 l_resolved_matching3_date,
750 l_resolved_matching3_inst,
751 l_trans_to_receipt_rate3,
752 l_invoice_currency_code3,
753 l_amount_applied3,
754 l_amount_applied_from3,
755 l_matching_number4,
756 l_resolved_matching4_date,
757 l_resolved_matching4_inst,
758 l_trans_to_receipt_rate4,
759 l_invoice_currency_code4,
760 l_amount_applied4,
761 l_amount_applied_from4,
762 l_matching_number5,
763 l_resolved_matching5_date,
764 l_resolved_matching5_inst,
765 l_trans_to_receipt_rate5,
766 l_invoice_currency_code5,
767 l_amount_applied5,
768 l_amount_applied_from5,
769 l_matching_number6,
770 l_resolved_matching6_date,
771 l_resolved_matching6_inst,
772 l_trans_to_receipt_rate6,
773 l_invoice_currency_code6,
774 l_amount_applied6,
775 l_amount_applied_from6,
776 l_matching_number7,
777 l_resolved_matching7_date,
778 l_resolved_matching7_inst,
779 l_trans_to_receipt_rate7,
780 l_invoice_currency_code7,
781 l_amount_applied7,
782 l_amount_applied_from7,
783 l_matching_number8,
784 l_resolved_matching8_date,
785 l_resolved_matching8_inst,
786 l_trans_to_receipt_rate8,
787 l_invoice_currency_code8,
788 l_amount_applied8,
789 l_amount_applied_from8,
790 l_currency_code,
791 l_receipt_date;
792
793 EXIT WHEN get_app_info%NOTFOUND;
794
795 /* debug messages */
796 debug1('l_matching number 1 ' || l_matching_number1);
797 debug1('l_matching number 2 ' || l_matching_number2);
798 debug1('receipt_date = ' || to_char(l_receipt_date));
799
800 /* Bug 1585615. Unhandled exception in calc_amt_applied if
801 * trans to receipt rate is zero. If the rate is zero,
802 * we'll treat it as it is not specified (null). */
803 IF (l_trans_to_receipt_rate1 = 0) THEN
804 l_trans_to_receipt_rate1 := NULL;
805 END IF;
806 IF (l_trans_to_receipt_rate2 = 0) THEN
807 l_trans_to_receipt_rate2 := NULL;
808 END IF;
809 IF (l_trans_to_receipt_rate3 = 0) THEN
810 l_trans_to_receipt_rate3 := NULL;
811 END IF;
812 IF (l_trans_to_receipt_rate4 = 0) THEN
813 l_trans_to_receipt_rate4 := NULL;
814 END IF;
815 IF (l_trans_to_receipt_rate5 = 0) THEN
816 l_trans_to_receipt_rate5 := NULL;
817 END IF;
818 IF (l_trans_to_receipt_rate6 = 0) THEN
819 l_trans_to_receipt_rate6 := NULL;
820 END IF;
821 IF (l_trans_to_receipt_rate7 = 0) THEN
822 l_trans_to_receipt_rate7 := NULL;
823 END IF;
824 IF (l_trans_to_receipt_rate8 = 0) THEN
825 l_trans_to_receipt_rate8 := NULL;
826 END IF;
827
828 IF (l_receipt_date IS NULL ) THEN
829 /* need to get receipt_date */
830 /* Bugfix 1732391. Replaced l_only_one_lockbox with l_no_batch_or_lb */
831 SELECT receipt_date
832 INTO l_receipt_date
833 FROM ar_payments_interface pi
834 WHERE pi.transmission_id = l_transmission_id
835 and pi.record_type||'' in ( l_payment_rec_type )
836 and pi.item_number = l_item_num
837 and ( pi.batch_name = l_batch_name
838 or
839 ( pi.lockbox_number = l_lockbox_number
840 and
841 l_batches = 'N'
842 )
843 or
844 l_no_batch_or_lb = 'Y'
845 );
846 END IF;
847
848 debug1('receipt_date = ' || to_char(l_receipt_date));
849
850 /* checking 1st trx_number */
851 debug1('invoice1 is not null = ' || l_matching_number1);
852 IF (l_matching_number1 is not NULL) THEN
853
854 /* added trx_amt_due_rem for bug 883345 */
855 /* Changed the where clause and added the exception for bug 1052313
856 and 1097549 */
857 /**************************************************************************
858 * Following SQL checks to see if there are more than one transactions
859 * having the same transaction number for the same customer. If the count
860 * of such transactions having sum(amt_due_remaining) > 0, then it implies
861 * that Lockbox will not know to which transaction the receipt should apply
862 * to.Lockbox should be able to apply receipts to duplicate invoices if and
863 * if all the payment terms on either of the duplicate transactions is
864 * closed. If there is only one transaction , then application should be
865 * done to the minimum of the payment schedule that has status 'OPEN'.
866 * The following logic has been provided for all of the eight invoices
867 * that may be applied through Lockbox in one transmission file.
868 **************************************************************************/
869 BEGIN
870 SELECT sum(count(distinct ps.customer_trx_id))
871 INTO l_tot_trx1
872 FROM ar_payment_schedules ps
873 WHERE ps.trx_number = l_matching_number1
874 AND ps.trx_date = l_resolved_matching1_date /* Bug fix 2926664 */
875 AND (EXISTS
876 (
877 select 'Exists from dual' from dual
878 where l_customer_id = ps.customer_id
879 union
880 select 'Exists from hz_cust_acct_relate'
881 from hz_cust_acct_relate rel
882 where rel.cust_account_id = l_customer_id
883 and related_cust_account_id = ps.customer_id
884 and rel.status = 'A'
885 and rel.bill_to_flag = 'Y'
886 union
887 select 'Exists ar_paying_relationships_v'
888 from ar_paying_relationships_v rel,
889 hz_cust_accounts acc
890 where rel.party_id = acc.party_id
891 and acc.cust_account_id = l_customer_id
892 and rel.related_cust_account_id = ps.customer_id
893 and l_receipt_date BETWEEN effective_start_date
894 AND effective_end_date
895 )
896 or
897 l_pay_unrelated_invoices = 'Y'
898 )
899 GROUP BY ps.customer_trx_id
900 HAVING sum(ps.amount_due_remaining) <> 0;
901
902 IF (l_tot_trx1 > 1) then
903 update ar_payments_interface pi
904 set invoice1_status = 'AR_PLB_DUP_INV'
905 where rowid = l_rowid;
906 goto END_1;
907 ELSE
908 SELECT invoice_currency_code,
909 amount_due_remaining
910 INTO ps_currency_code1,
911 trx_amt_due_rem1
912 FROM ar_payment_schedules ps,
913 ra_cust_trx_types tt
914 WHERE ps.trx_number = l_matching_number1
915 AND ps.status = decode(tt.allow_overapplication_flag,
916 'N', 'OP',
917 ps.status)
918 AND ps.class NOT IN ('PMT','GUAR')
919 AND ps.payment_schedule_id =
920 (select min(ps.payment_schedule_id)
921 from ar_payment_schedules ps,
922 ra_cust_trx_types tt
923 where ps.trx_number = l_matching_number1
924 and ps.trx_date = l_resolved_matching1_date /* Bug fix 2926664 */
925 and (EXISTS
926 (
927 select 'Exists from dual' from dual
928 where l_customer_id = ps.customer_id
929 union
930 select 'Exists from hz_cust_acct_relate'
931 from hz_cust_acct_relate rel
932 where rel.cust_account_id = l_customer_id
933 and related_cust_account_id = ps.customer_id
934 and rel.status = 'A'
935 and rel.bill_to_flag = 'Y'
936 union
937 select 'Exists ar_paying_relationships_v'
938 from ar_paying_relationships_v rel,
939 hz_cust_accounts acc
940 where rel.party_id = acc.party_id
941 and acc.cust_account_id = l_customer_id
942 and rel.related_cust_account_id = ps.customer_id
943 and l_receipt_date BETWEEN effective_start_date
944 AND effective_end_date
945 )
946 or
947 l_pay_unrelated_invoices = 'Y'
948 )
949 and ps.cust_trx_type_id = tt.cust_trx_type_id
950 and ps.class NOT IN ('PMT','GUAR')
951 and ps.status=decode(tt.allow_overapplication_flag,
952 'N', 'OP',
953 ps.status))
954 AND (EXISTS
955 (
956 select 'Exists from dual' from dual
957 where l_customer_id = ps.customer_id
958 union
959 select 'Exists from hz_cust_acct_relate'
960 from hz_cust_acct_relate rel
961 where rel.cust_account_id = l_customer_id
962 and related_cust_account_id = ps.customer_id
963 and rel.status = 'A'
964 and rel.bill_to_flag = 'Y'
965 union
966 select 'Exists ar_paying_relationships_v'
967 from ar_paying_relationships_v rel,
968 hz_cust_accounts acc
969 where rel.party_id = acc.party_id
970 and acc.cust_account_id = l_customer_id
971 and rel.related_cust_account_id = ps.customer_id
972 and l_receipt_date BETWEEN effective_start_date
973 AND effective_end_date
974 )
975 or
976 l_pay_unrelated_invoices = 'Y'
977 )
978 AND ps.cust_trx_type_id = tt.cust_trx_type_id;
979 END IF;
980 EXCEPTION
981 WHEN NO_DATA_FOUND THEN
982 goto END_1;
983 END;
984
985 debug1('currency code1 of invoice from the ps = ' || ps_currency_code1);
986 /********************************************************
987 * if transmission has null currency code use the one
988 * from Payment Schedules
989 *********************************************************/
990 IF (l_invoice_currency_code1 is NULL ) THEN
991 debug1('currency code is null.. setting = ' || ps_currency_code1);
992 l_invoice_currency_code1 := ps_currency_code1;
993
994 /* update ar_payment_interface to have the invoice currency_code */
995 UPDATE ar_payments_interface
996 SET invoice_currency_code1 = l_invoice_currency_code1
997 WHERE rowid = l_rowid ;
998 END IF; /* end if invoice currency code was null */
999
1000 /****************************************************************
1001 * check to see if the currency code matches or is was not included
1002 * in the transmission
1003 ****************************************************************/
1004 debug1('l_invoice_currency_code1 = ' || l_invoice_currency_code1);
1005 debug1('ps_currency_code = ' || ps_currency_code1);
1006
1007 IF (l_invoice_currency_code1 <> ps_currency_code1) then
1008 debug1('currency code give does not match payment schedules..');
1009 UPDATE AR_PAYMENTS_INTERFACE
1010 SET invoice1_status = 'AR_PLB_CURRENCY_BAD'
1011 WHERE rowid = l_rowid ;
1012 ELSE
1013 /* Bug:1513671 we know the invoice currency code so we can now format the
1014 amount applied if we need to */
1015
1016 IF (p_format_amount1 = 'Y') THEN
1017 fnd_currency.Get_Info(l_invoice_currency_code1,
1018 l_precision,
1019 l_extended_precision,
1020 l_mau);
1021 l_amount_applied1 := round(l_amount_applied1 / power(10, l_precision),
1022 l_precision);
1023 END IF;
1024
1025 /*************************************************************
1026 * if the currency code of the transaction does not equal the
1027 * currency code of the receipt, then check for cross currency
1028 * or Euro case
1029 **************************************************************/
1030 IF ( l_invoice_currency_code1 <> l_currency_code) THEN
1031 debug1('currency code of receipt does not match currency code of inv');
1032
1033 /***********************************************************
1034 * we need to check to see if we have cross currency
1035 * profile enabled or we are dealing with a fixed rate
1036 * currency
1037 ************************************************************/
1038
1039 l_is_fixed_rate := gl_currency_api.is_fixed_rate(
1040 l_currency_code, /*receipt currency */
1041 l_invoice_currency_code1, /* inv currency */
1042 nvl(l_receipt_date,sysdate));
1043
1044 debug1('is this a fixed rate = ' || l_is_fixed_rate);
1045 debug1('is cross curr enabled?? ' || l_enable_cross_currency);
1046
1047 IF ( (l_is_fixed_rate = 'Y') or
1048 (l_enable_cross_currency = 'Y')) THEN
1049 /* we have to make sure that all fields are populated */
1050
1051 IF ( (l_amount_applied_from1 IS NULL) or
1052 (l_amount_applied1 IS NULL) or
1053 (l_trans_to_receipt_rate1 IS NULL) ) THEN
1054
1055 /* we need to check the rate 1st. If both amounts columns are
1056 populated, then we calculate the rate. If one amount is
1057 missing and the rate is null, then we try to get the rate from
1058 GL based on the profile option */
1059
1060 IF ( l_trans_to_receipt_rate1 is NULL) THEN
1061 debug1('trans_to_receipt_rate1 is null');
1062 /* if neither amount is null then we calculate the rate */
1063
1064 debug1('amount applied = ' || to_char(l_amount_applied1));
1065 debug1('amount applied from = ' || to_char(l_amount_applied_from1));
1066 IF ( (l_amount_applied1 IS NOT NULL) and
1067 (l_amount_applied_from1 IS NOT NULL)) Then
1068
1069 /********************************************
1070 * if we have a fixed rate, we need to get the
1071 * rate from GL and verify the validity of the
1072 * 2 amount columns with the rate that we get
1073 **********************************************/
1074 IF (l_is_fixed_rate = 'Y') THEN
1075 /* get the fixed rate from GL */
1076 l_trans_to_receipt_rate1 :=
1077 gl_currency_api.get_rate(
1078 l_invoice_currency_code1,
1079 l_currency_code,
1080 l_receipt_date);
1081
1082 /*************************************************
1083 * if all fields are populated, we need to
1084 * check to make sure that they are logically
1085 * correct.
1086 ************************************************/
1087 calc_amt_applied_fmt(l_invoice_currency_code1,
1088 l_amount_applied_from1,
1089 l_trans_to_receipt_rate1,
1090 l_temp_amt_applied,
1091 'N');
1092
1093 calc_amt_applied_from_fmt(l_currency_code,
1094 l_amount_applied1,
1095 l_trans_to_receipt_rate1,
1096 l_temp_amt_applied_from,
1097 'N');
1098
1099 ar_cc_lockbox.calc_cross_rate(
1100 l_amount_applied1,
1101 l_amount_applied_from1,
1102 l_invoice_currency_code1,
1103 l_currency_code,
1104 l_temp_trans_to_receipt_rate);
1105 IF ( (l_temp_amt_applied = l_amount_applied1) OR
1106 (l_temp_amt_applied_from =
1107 l_amount_applied_from1) OR
1108 (l_temp_trans_to_receipt_rate =
1109 l_trans_to_receipt_rate1)) THEN
1110
1111 /********************************************
1112 * since one or more of the conditions are
1113 * true then we assume that everything is
1114 * fine and we can write the rate to the
1115 * database
1116 *********************************************/
1117 debug1('validation passed ' );
1118 UPDATE ar_payments_interface
1119 SET trans_to_receipt_rate1 =
1120 l_trans_to_receipt_rate1
1121 WHERE rowid = l_rowid;
1122 ELSE
1123 UPDATE AR_PAYMENTS_INTERFACE
1124 SET invoice1_status =
1125 'AR_PLB_CC_INVALID_VALUE'
1126 WHERE rowid = l_rowid;
1127 END IF;
1128
1129 ELSE
1130 /* calculate the least rate that would convert
1131 the items */
1132 ar_cc_lockbox.calc_cross_rate(
1133 l_amount_applied1,
1134 l_amount_applied_from1,
1135 l_invoice_currency_code1,
1136 l_currency_code,
1137 l_trans_to_receipt_rate1);
1138
1139 /* once the rate has been calculated, we need
1140 to write it to the table */
1141 UPDATE ar_payments_interface
1142 SET trans_to_receipt_rate1 =
1143 l_trans_to_receipt_rate1
1144 WHERE rowid = l_rowid;
1145 END IF;
1146
1147 ELSE
1148 /* need to derive the rate if possible*/
1149 debug1( 'need to derive rate ');
1150 IF (p_default_exchange_rate_type IS NOT NULL or
1151 l_is_fixed_rate = 'Y' ) THEN
1152 l_trans_to_receipt_rate1 :=
1153 gl_currency_api.get_rate_sql(
1154 l_invoice_currency_code1,
1155 l_currency_code,
1156 l_receipt_date,
1157 p_default_exchange_rate_type);
1158 debug1('calculated rate = ' || to_char(l_trans_to_receipt_rate1));
1159 /* if there is no rate in GL, there is nothing
1160 more we can do */
1161 IF (l_trans_to_receipt_rate1 < 0 ) THEN
1162
1163 UPDATE AR_PAYMENTS_INTERFACE
1164 SET invoice1_status = 'AR_PLB_NO_EXCHANGE_RATE'
1165 WHERE rowid = l_rowid;
1166 ELSE
1167 /* once the rate has been calculated, we need
1168 to write it to the table */
1169
1170 debug1('writing rate to database ' || to_char(l_trans_to_receipt_rate1));
1171
1172 UPDATE ar_payments_interface
1173 SET trans_to_receipt_rate1 =
1174 l_trans_to_receipt_rate1
1175 WHERE rowid = l_rowid;
1176
1177 /* finish the calculation because we have a rate*/
1178
1179 IF (l_amount_applied_from1 IS NULL) THEN
1180 calc_amt_applied_from_fmt(l_currency_code,
1181 l_amount_applied1,
1182 l_trans_to_receipt_rate1,
1183 l_amount_applied_from1,
1184 l_format_amount_applied_from1);
1185 update ar_payments_interface
1186 set amount_applied_from1 =
1187 l_amount_applied_from1
1188 where rowid = l_rowid;
1189
1190 ELSE
1191 /* calculate amount applied and save to db */
1192 calc_amt_applied_fmt(l_invoice_currency_code1,
1193 l_amount_applied_from1,
1194 l_trans_to_receipt_rate1,
1195 l_amount_applied1,
1196 l_format_amount1);
1197 debug1('calculated amt applied = ' || to_char(l_amount_applied1));
1198
1199 /* to deal with rounding errors that happen
1200 with fixed rate currencies, we need to
1201 take the amount_due_remaining of the trx
1202 convert it to the receipt currency and
1203 compare that value with the
1204 amount_applied_from original value
1205
1206 ADDED for BUG 883345 */
1207
1208 IF (l_is_fixed_rate = 'Y') THEN
1209
1210 calc_amt_applied_from_fmt(l_currency_code,
1211 trx_amt_due_rem1,
1212 l_trans_to_receipt_rate1,
1213 amt_applied_from_tc,
1214 'N');
1215
1216 debug1('amt applied from tc = ' || to_char(amt_applied_from_tc));
1217 debug1('amt_applied_from1 = ' || to_char(l_amount_applied_from1));
1218 debug1('amt_applied_from with out NOCOPY = ' || to_char(l_amount_applied_from1 * (10**l_precision) ));
1219
1220 IF (amt_applied_from_tc =
1221 l_amount_applied_from1) THEN
1222 IF (l_format_amount1 = 'Y') THEN
1223 fnd_currency.Get_Info(
1224 l_invoice_currency_code1,
1225 l_precision,
1226 l_extended_precision,
1227 l_mau);
1228
1229 l_amount_applied1 :=
1230 trx_amt_due_rem1 * (10**l_precision);
1231 ELSE
1232 l_amount_applied1 := trx_amt_due_rem1;
1233 END IF;
1234 END IF;
1235 END IF;
1236
1237 update ar_payments_interface
1238 set amount_applied1 =
1239 l_amount_applied1
1240 where rowid = l_rowid;
1241
1242 END IF; /* if amount_applied_From1 is null */
1243 END IF; /* if rate is null after it is calc */
1244 ELSE /* bug 1519765 */
1245 UPDATE AR_PAYMENTS_INTERFACE
1246 SET invoice1_status = 'AR_PLB_NO_EXCHANGE_RATE'
1247 WHERE rowid = l_rowid;
1248 END IF; /* if derive profile is not null */
1249 END IF;
1250
1251 /* we know that trans_to_receipt_rate1 is not null,
1252 therefore, one of the amount values must be null and
1253 we have to calculate it */
1254 ELSE
1255 /* is amount_applied_From null?? */
1256 IF (l_amount_applied_from1 IS NULL) THEN
1257 calc_amt_applied_from_fmt(l_currency_code,
1258 l_amount_applied1,
1259 l_trans_to_receipt_rate1,
1260 l_amount_applied_from1,
1261 l_format_amount_applied_from1);
1262 update ar_payments_interface
1263 set amount_applied_from1 =
1264 l_amount_applied_from1
1265 where rowid = l_rowid;
1266
1267 ELSE
1268 calc_amt_applied_fmt(l_invoice_currency_code1,
1269 l_amount_applied_from1,
1270 l_trans_to_receipt_rate1,
1271 l_amount_applied1,
1275 with fixed rate currencies, we need to
1272 l_format_amount1);
1273
1274 /* to deal with rounding errors that happen
1276 take the amount_due_remaining of the trx
1277 convert it to the receipt currency and
1278 compare that value with the
1279 amount_applied_from original value
1280
1281 ADDED for BUG 883345 */
1282
1283 IF (l_is_fixed_rate = 'Y') THEN
1284
1285 calc_amt_applied_from_fmt(l_currency_code,
1286 trx_amt_due_rem1,
1287 l_trans_to_receipt_rate1,
1288 amt_applied_from_tc, 'N');
1289
1290 debug1('amt applied from tc = ' || to_char(amt_applied_from_tc));
1291 debug1('amt_applied_from1 = ' || to_char(l_amount_applied_from1));
1292 debug1('amt_applied_from with out = ' || to_char(l_amount_applied_from1* (10**l_precision) ));
1293
1294 IF (amt_applied_from_tc =
1295 l_amount_applied_from1) THEN
1296 IF (l_format_amount1 = 'Y') THEN
1297 fnd_currency.Get_Info(
1298 l_invoice_currency_code1,
1299 l_precision,
1300 l_extended_precision,
1301 l_mau);
1302
1303 l_amount_applied1 :=
1304 trx_amt_due_rem1 * (10**l_precision);
1305 ELSE
1306 l_amount_applied1 := trx_amt_due_rem1;
1307 END IF;
1308 END IF;
1309 END IF;
1310
1311 UPDATE AR_PAYMENTS_INTERFACE
1312 SET amount_applied1 =
1313 l_amount_applied1
1314 WHERE rowid = l_rowid;
1315
1316 END IF;
1317 END IF; /* trans to receipt_rate 1 is null */
1318 ELSE
1319 /*************************************************
1320 * if all fields are populated, we need to
1321 * check to make sure that they are logically
1322 * correct.
1323 ***************************************************/
1324
1325 calc_amt_applied_fmt(l_invoice_currency_code1,
1326 l_amount_applied_from1,
1327 l_trans_to_receipt_rate1,
1328 l_temp_amt_applied,
1329 'N');
1330
1331 calc_amt_applied_from_fmt(l_currency_code,
1332 l_amount_applied1,
1333 l_trans_to_receipt_rate1,
1334 l_temp_amt_applied_from,
1335 'N');
1336
1337 ar_cc_lockbox.calc_cross_rate(
1338 l_amount_applied1,
1339 l_amount_applied_from1,
1340 l_invoice_currency_code1,
1341 l_currency_code,
1342 l_temp_trans_to_receipt_rate);
1343 IF ( (l_temp_amt_applied = l_amount_applied1) OR
1344 (l_temp_amt_applied_from =
1345 l_amount_applied_from1) OR
1346 (l_temp_trans_to_receipt_rate =
1347 l_trans_to_receipt_rate1)) THEN
1348
1349 /* since one or more of the conditions are true
1350 then we assume that everything is fine. */
1351 debug1('validation passed ' );
1352 ELSE
1353 UPDATE AR_PAYMENTS_INTERFACE
1354 SET invoice1_status = 'AR_PLB_CC_INVALID_VALUE'
1355 WHERE rowid = l_rowid;
1356 END IF;
1357
1358 END IF; /* if one of the 3 items is NULL */
1359 ELSE
1360 /*****************************************************
1361 currencies do not match, they are not fixed rate and
1362 cross currency enabled profile is not on.
1363 then set the status to be a currency conflict between
1364 the invoice and receipt currencies
1365 ***************************************************/
1366 UPDATE AR_PAYMENTS_INTERFACE
1367 SET invoice1_status = 'AR_PLB_CURR_CONFLICT'
1368 WHERE rowid = l_rowid;
1369 END IF;
1370 ELSE /* Bug 2066392. Single currency */
1371 IF l_amount_applied_from1 is not null THEN
1372 IF l_amount_applied1 is not null THEN
1373 IF l_amount_applied_from1 <> l_amount_applied1 THEN
1374 UPDATE AR_PAYMENTS_INTERFACE
1375 SET invoice1_status = 'AR_PLB_CC_INVALID_VALUE'
1376 WHERE rowid = l_rowid;
1377 END IF;
1378 ELSE
1379 IF l_format_amount1 = 'Y' THEN
1380 fnd_currency.Get_Info(l_invoice_currency_code1,
1381 l_precision,
1382 l_extended_precision,
1383 l_mau);
1384 l_unformat_amount := l_amount_applied_from1 * power(10, l_precision);
1385 ELSE
1386 l_unformat_amount := l_amount_applied_from1;
1387 END IF;
1388 UPDATE AR_PAYMENTS_INTERFACE
1392 END IF;
1389 SET amount_applied1 = l_unformat_amount
1390 WHERE rowid = l_rowid;
1391 END IF;
1393 END IF;
1394 END IF;
1395 END IF; /* if matching number 1 is not null */
1396 <<END_1>>
1397
1398 /**************** need to check matching number 2 *************/
1399
1400 /* checking 2st trx_number */
1401 debug1('invoice2 is not null ' || l_matching_number2);
1402 IF (l_matching_number2 IS NOT NULL) THEN
1403
1404 /* added trx_amt_due_rem2 for bug 883345 */
1405 /* Changed the where clause and added the exception for bug 1052313
1406 and 1097549 */
1407 BEGIN
1408 SELECT sum(count(distinct ps.customer_trx_id))
1409 INTO l_tot_trx2
1410 FROM ar_payment_schedules ps
1411 WHERE ps.trx_number = l_matching_number2
1412 AND ps.trx_date = l_resolved_matching2_date /* Bug fix 2926664 */
1413 AND (EXISTS
1414 (
1415 select 'Exists from dual' from dual
1416 where l_customer_id = ps.customer_id
1417 union
1418 select 'Exists from hz_cust_acct_relate'
1419 from hz_cust_acct_relate rel
1420 where rel.cust_account_id = l_customer_id
1421 and related_cust_account_id = ps.customer_id
1422 and rel.status = 'A'
1423 and rel.bill_to_flag = 'Y'
1424 union
1425 select 'Exists ar_paying_relationships_v'
1426 from ar_paying_relationships_v rel,
1427 hz_cust_accounts acc
1428 where rel.party_id = acc.party_id
1429 and acc.cust_account_id = l_customer_id
1430 and rel.related_cust_account_id = ps.customer_id
1431 and l_receipt_date BETWEEN effective_start_date
1432 AND effective_end_date
1433 )
1434 or
1435 l_pay_unrelated_invoices = 'Y'
1436 )
1437 GROUP BY ps.customer_trx_id
1438 HAVING sum(ps.amount_due_remaining) <> 0;
1439
1440 IF (l_tot_trx2 > 1) then
1441 update ar_payments_interface pi
1442 set invoice2_status = 'AR_PLB_DUP_INV'
1443 where rowid = l_rowid;
1444 goto END_2;
1445 ELSE
1446 SELECT invoice_currency_code,
1447 amount_due_remaining
1448 INTO ps_currency_code2,
1449 trx_amt_due_rem2
1450 FROM ar_payment_schedules ps,
1451 ra_cust_trx_types tt
1452 WHERE ps.trx_number = l_matching_number2
1453 AND ps.status = decode(tt.allow_overapplication_flag,
1454 'N', 'OP',
1455 ps.status)
1456 AND ps.class NOT IN ('PMT','GUAR')
1457 AND ps.payment_schedule_id =
1458 (select min(ps.payment_schedule_id)
1459 from ar_payment_schedules ps,
1460 ra_cust_trx_types tt
1461 where ps.trx_number = l_matching_number2
1462 and ps.trx_date = l_resolved_matching2_date /* Bug fix 2926664 */
1463 and (EXISTS /* subquery */
1464 (
1465 select 'Exists from dual' from dual
1466 where l_customer_id = ps.customer_id
1467 union
1468 select 'Exists from hz_cust_acct_relate'
1469 from hz_cust_acct_relate rel
1470 where rel.cust_account_id = l_customer_id
1471 and related_cust_account_id = ps.customer_id
1472 and rel.status = 'A'
1473 and rel.bill_to_flag = 'Y'
1474 union
1475 select 'Exists ar_paying_relationships_v'
1476 from ar_paying_relationships_v rel,
1477 hz_cust_accounts acc
1478 where rel.party_id = acc.party_id
1479 and acc.cust_account_id = l_customer_id
1480 and rel.related_cust_account_id = ps.customer_id
1481 and l_receipt_date BETWEEN effective_start_date
1482 AND effective_end_date
1483 )
1484 or
1485 l_pay_unrelated_invoices = 'Y'
1486 )
1487 and ps.cust_trx_type_id = tt.cust_trx_type_id
1488 and ps.class NOT IN ('PMT','GUAR')
1489 and ps.status=decode(tt.allow_overapplication_flag,
1490 'N' , 'OP',
1491 ps.status))
1492 and (EXISTS /* main query */
1493 (
1494 select 'Exists from dual' from dual
1495 where l_customer_id = ps.customer_id
1496 union
1497 select 'Exists from hz_cust_acct_relate'
1498 from hz_cust_acct_relate rel
1499 where rel.cust_account_id = l_customer_id
1500 and related_cust_account_id = ps.customer_id
1501 and rel.status = 'A'
1502 and rel.bill_to_flag = 'Y'
1503 union
1504 select 'Exists ar_paying_relationships_v'
1505 from ar_paying_relationships_v rel,
1509 and rel.related_cust_account_id = ps.customer_id
1506 hz_cust_accounts acc
1507 where rel.party_id = acc.party_id
1508 and acc.cust_account_id = l_customer_id
1510 and l_receipt_date BETWEEN effective_start_date
1511 AND effective_end_date
1512 )
1513 or
1514 l_pay_unrelated_invoices = 'Y'
1515 )
1516 AND ps.cust_trx_type_id = tt.cust_trx_type_id;
1517 END IF;
1518 EXCEPTION
1519 WHEN NO_DATA_FOUND THEN
1520 goto END_2;
1521 END;
1522
1523 debug1('currency code of ps = ' || ps_currency_code2);
1524 /********************************************************
1525 * if transmission has null currency code use the one
1526 * from Payment Schedules
1527 *********************************************************/
1528 IF (l_invoice_currency_code2 is NULL ) THEN
1529 debug1('currency code is null.. set equal to ps currency code');
1530 l_invoice_currency_code2 := ps_currency_code2;
1531
1532 UPDATE ar_payments_interface pi
1533 SET pi.invoice_currency_code2 = l_invoice_currency_code2
1534 WHERE rowid = l_rowid;
1535 END IF; /* end if invoice currency is NULL */
1536
1537 /****************************************************************
1538 * check to see if the currency code matches or is was not included
1539 * in the transmission
1540 ****************************************************************/
1541 debug1('l_invoice_currency_code2 = ' || l_invoice_currency_code2);
1542 debug1('ps_currency_code2 = ' || ps_currency_code2);
1543
1544 /* ------------------ Bug# 2066679 --------------------- */
1545
1546 IF (l_invoice_currency_code2 <> ps_currency_code2) then
1547 debug1('currency code given does not match payment schedules..');
1548 UPDATE AR_PAYMENTS_INTERFACE
1549 SET invoice2_status = 'AR_PLB_CURRENCY_BAD'
1550 WHERE rowid = l_rowid;
1551 ELSE
1552 /* Bug:1513671 we know the invoice currency code so we can now format the
1553 amount applied if we need to */
1554
1555 IF (p_format_amount2 = 'Y') THEN
1556 fnd_currency.Get_Info(l_invoice_currency_code2,
1557 l_precision,
1558 l_extended_precision,
1559 l_mau);
1560 l_amount_applied2 := round(l_amount_applied2 / power(10, l_precision),
1561 l_precision);
1562 END IF;
1563
1564 /*************************************************************
1565 * if the currency code of the transaction does not equal the
1566 * currency code of the receipt, then check for cross currency
1567 * or Euro case
1568 **************************************************************/
1569 IF ( l_invoice_currency_code2 <> l_currency_code) THEN
1570 debug1('currency code of receipt does not match currency code of inv');
1571
1572 /***********************************************************
1573 * we need to check to see if we have cross currency
1574 * profile enabled or we are dealing with a fixed rate
1575 * currency
1576 ************************************************************/
1577
1578 l_is_fixed_rate := gl_currency_api.is_fixed_rate(
1579 l_currency_code, /*receipt currency */
1580 l_invoice_currency_code2, /* inv currency */
1581 nvl(l_receipt_date,sysdate));
1582 debug1('is this a fixed rate = ' || l_is_fixed_rate);
1583 debug1('is cross curr enabled?? ' || l_enable_cross_currency);
1584 IF ( (l_is_fixed_rate = 'Y') or
1585 (l_enable_cross_currency = 'Y')) THEN
1586 /* we have to make sure that all fields are populated */
1587
1588 IF ( (l_amount_applied_from2 IS NULL) or
1589 (l_amount_applied2 IS NULL) or
1590 (l_trans_to_receipt_rate2 IS NULL) ) THEN
1591
1592 /* we need to check the rate 1st. If both amounts columns are
1593 populated, then we calculate the rate. If one amount is
1594 missing and the rate is null, then we try to get the rate from
1595 GL based on the profile option */
1596
1597 IF ( l_trans_to_receipt_rate2 is NULL) THEN
1598 debug1('trans_to_receipt_rate2 is Null');
1599 /* if neither amount is null then we calculate the rate */
1600 IF ( (l_amount_applied2 IS NOT NULL) and
1601 (l_amount_applied_from2 IS NOT NULL)) Then
1602
1603 /********************************************
1604 * if we have a fixed rate, we need to get the
1605 * rate from GL and verify the validity of the
1606 * 2 amount columns with the rate that we get
1607 **********************************************/
1608
1609 IF (l_is_fixed_rate = 'Y') THEN
1610 /* get the fixed rate from GL */
1611 l_trans_to_receipt_rate2 :=
1612 gl_currency_api.get_rate(
1613 l_invoice_currency_code2,
1614 l_currency_code,
1615 l_receipt_date);
1616 /*************************************************
1617 * if all fields are populated, we need to
1618 * check to make sure that they are logically
1622 calc_amt_applied_fmt(l_invoice_currency_code2,
1619 * correct.
1620 ************************************************/
1621
1623 l_amount_applied_from2,
1624 l_trans_to_receipt_rate2,
1625 l_temp_amt_applied,
1626 'N');
1627
1628 calc_amt_applied_from_fmt(l_currency_code,
1629 l_amount_applied2,
1630 l_trans_to_receipt_rate2,
1631 l_temp_amt_applied_from,
1632 'N');
1633
1634 ar_cc_lockbox.calc_cross_rate(
1635 l_amount_applied2,
1636 l_amount_applied_from2,
1637 l_invoice_currency_code2,
1638 l_currency_code,
1639 l_temp_trans_to_receipt_rate);
1640
1641 IF ( (l_temp_amt_applied = l_amount_applied2) OR
1642 (l_temp_amt_applied_from =
1643 l_amount_applied_from2) OR
1644 (l_temp_trans_to_receipt_rate =
1645 l_trans_to_receipt_rate2)) THEN
1646
1647 /********************************************
1648 * since one or more of the conditions are
1649 * true then we assume that everything is
1650 * fine and we can write the rate to the
1651 * database
1652 *********************************************/
1653 debug1('validation passed ' );
1654
1655 UPDATE ar_payments_interface
1656 SET trans_to_receipt_rate2 =
1657 l_trans_to_receipt_rate2
1658 WHERE rowid = l_rowid;
1659 ELSE
1660 UPDATE AR_PAYMENTS_INTERFACE
1661 SET invoice2_status =
1662 'AR_PLB_CC_INVALID_VALUE'
1663 WHERE rowid = l_rowid;
1664 END IF;
1665 ELSE
1666 /* calculate the least rate that would convert
1667 the items */
1668 ar_cc_lockbox.calc_cross_rate(
1669 l_amount_applied2,
1670 l_amount_applied_from2,
1671 l_invoice_currency_code2,
1672 l_currency_code,
1673 l_trans_to_receipt_rate2);
1674
1675 /* once the rate has been calculated, we need
1676 to write it to the table */
1677 UPDATE ar_payments_interface
1678 SET trans_to_receipt_rate2 =
1679 l_trans_to_receipt_rate2
1680 WHERE rowid = l_rowid;
1681 END IF;
1682
1683 ELSE
1684 /* need to derive the rate if possible*/
1685 IF (p_default_exchange_rate_type IS NOT NULL or
1686 l_is_fixed_rate = 'Y' ) THEN
1687 l_trans_to_receipt_rate2 :=
1688 gl_currency_api.get_rate_sql(
1689 l_invoice_currency_code2,
1690 l_currency_code,
1691 l_receipt_date,
1692 p_default_exchange_rate_type);
1693 debug1('calculated rate = ' || to_char(l_trans_to_receipt_rate2));
1694
1695 /* if there is no rate in GL, there is nothing
1696 more we can do */
1697 IF (l_trans_to_receipt_rate2 < 0) THEN
1698
1699 UPDATE AR_PAYMENTS_INTERFACE
1700 SET invoice2_status = 'AR_PLB_NO_EXCHANGE_RATE'
1701 WHERE rowid = l_rowid;
1702 ELSE
1703 /* once the rate has been calculated, we need
1704 to write it to the table */
1705
1706 debug1('writing rate to database ' || to_char(l_trans_to_receipt_rate2));
1707
1708 UPDATE ar_payments_interface
1709 SET trans_to_receipt_rate2 =
1710 l_trans_to_receipt_rate2
1711 WHERE rowid = l_rowid;
1712
1713 /* finish the calculation because we have a rate*/
1714 IF (l_amount_applied_from2 IS NULL) THEN
1715 calc_amt_applied_from_fmt(l_currency_code,
1716 l_amount_applied2,
1717 l_trans_to_receipt_rate2,
1718 l_amount_applied_from2,
1719 l_format_amount_applied_from2);
1720 /* once we calculate the value, we need
1721 to write it to the table */
1725 WHERE rowid = l_rowid;
1722 UPDATE ar_payments_interface
1723 SET amount_applied_from2 =
1724 l_amount_applied_from2
1726
1727 ELSE
1728 calc_amt_applied_fmt(l_invoice_currency_code2,
1729 l_amount_applied_from2,
1730 l_trans_to_receipt_rate2,
1731 l_amount_applied2,
1732 l_format_amount2);
1733
1734 /* to deal with rounding errors that happen
1735 with fixed rate currencies, we need to
1736 take the amount_due_remaining of the trx
1737 convert it to the receipt currency and
1738 compare that value with the
1739 amount_applied_from original value
1740
1741 ADDED for BUG 883345 */
1742
1743 IF (l_is_fixed_rate = 'Y') THEN
1744
1745 calc_amt_applied_from_fmt(l_currency_code,
1746 trx_amt_due_rem2,
1747 l_trans_to_receipt_rate2,
1748 amt_applied_from_tc,
1749 'N');
1750
1751 IF (amt_applied_from_tc =
1752 l_amount_applied_from2) THEN
1753 IF (l_format_amount2 = 'Y') THEN
1754 fnd_currency.Get_Info(
1755 l_invoice_currency_code2,
1756 l_precision,
1757 l_extended_precision,
1758 l_mau);
1759
1760 l_amount_applied2 :=
1761 trx_amt_due_rem2 * (10**l_precision);
1762 ELSE
1763 l_amount_applied2 := trx_amt_due_rem2;
1764 END IF;
1765 END IF;
1766 END IF;
1767
1768
1769 UPDATE ar_payments_interface
1770 SET amount_applied2 =
1771 l_amount_applied2
1772 WHERE rowid = l_rowid;
1773
1774 END IF; /* if amount_applied_From2 is null */
1775 END IF; /* if rate is null after it is calc */
1776 ELSE /* Bug 1519765 */
1777 UPDATE AR_PAYMENTS_INTERFACE
1778 SET invoice2_status = 'AR_PLB_NO_EXCHANGE_RATE'
1779 WHERE rowid = l_rowid;
1780 END IF; /* if derive profile is not null */
1781 END IF;
1782
1783 /* we know that trans_to_receipt_rate2 is not null,
1784 therefore, one of the amount values must be null and
1785 we have to calculate it */
1786 ELSE
1787 /* is amount_applied_From null?? */
1788 IF (l_amount_applied_from2 IS NULL) THEN
1789 calc_amt_applied_from_fmt(l_currency_code,
1790 l_amount_applied2,
1791 l_trans_to_receipt_rate2,
1792 l_amount_applied_from2,
1793 l_format_amount_applied_from2);
1794 UPDATE ar_payments_interface
1795 SET amount_applied_from2 =
1796 l_amount_applied_from2
1797 WHERE rowid = l_rowid;
1798
1799 ELSE
1800 calc_amt_applied_fmt(l_invoice_currency_code2,
1801 l_amount_applied_from2,
1802 l_trans_to_receipt_rate2,
1803 l_amount_applied2,
1804 l_format_amount2);
1805
1806
1807 /* to deal with rounding errors that happen
1808 with fixed rate currencies, we need to
1809 take the amount_due_remaining of the trx
1810 convert it to the receipt currency and
1811 compare that value with the
1812 amount_applied_from original value
1813
1814 ADDED for BUG 883345 */
1815
1816 IF (l_is_fixed_rate = 'Y') THEN
1817
1818 calc_amt_applied_from_fmt(l_currency_code,
1819 trx_amt_due_rem2,
1820 l_trans_to_receipt_rate2,
1821 amt_applied_from_tc,
1822 'N');
1823
1824 IF (amt_applied_from_tc =
1825 l_amount_applied_from2 ) THEN
1826 IF (l_format_amount2 = 'Y') THEN
1827 fnd_currency.Get_Info(
1828 l_invoice_currency_code2,
1829 l_precision,
1830 l_extended_precision,
1834 trx_amt_due_rem2 * (10**l_precision);
1831 l_mau);
1832
1833 l_amount_applied2 :=
1835
1836 ELSE
1837 l_amount_applied2 := trx_amt_due_rem2;
1838 END IF;
1839 END IF;
1840 END IF;
1841
1842
1843 UPDATE ar_payments_interface
1844 SET amount_applied2 =
1845 l_amount_applied2
1846 WHERE rowid = l_rowid;
1847
1848 END IF;
1849 END IF; /* trans to receipt_rate 2 is null */
1850 ELSE
1851 /*************************************************
1852 * if all fields are populated, we need to
1853 * check to make sure that they are logically
1854 * correct.
1855 ***************************************************/
1856
1857 calc_amt_applied_fmt(l_invoice_currency_code2,
1858 l_amount_applied_from2,
1859 l_trans_to_receipt_rate2,
1860 l_temp_amt_applied,
1861 'N');
1862
1863 calc_amt_applied_from_fmt(l_currency_code,
1864 l_amount_applied2,
1865 l_trans_to_receipt_rate2,
1866 l_temp_amt_applied_from,
1867 'N');
1868
1869 ar_cc_lockbox.calc_cross_rate(
1870 l_amount_applied2,
1871 l_amount_applied_from2,
1872 l_invoice_currency_code2,
1873 l_currency_code,
1874 l_temp_trans_to_receipt_rate);
1875 IF ( (l_temp_amt_applied = l_amount_applied2) OR
1876 (l_temp_amt_applied_from =
1877 l_amount_applied_from2) OR
1878 (l_temp_trans_to_receipt_rate =
1879 l_trans_to_receipt_rate2)) THEN
1880
1881 /* since one or more of the conditions are true
1882 then we assume that everything is fine. */
1883 debug1('validation passed ' );
1884 ELSE
1885 UPDATE AR_PAYMENTS_INTERFACE
1886 SET invoice2_status = 'AR_PLB_CC_INVALID_VALUE'
1887 WHERE rowid = l_rowid;
1888 END IF;
1889 END IF; /* if one of the 3 items is NULL */
1890 ELSE
1891 /*****************************************************
1892 currencies do not match, they are not fixed rate and
1893 cross currency enabled profile is not on.
1894 then set the status to be a currency conflict between
1895 the invoice and receipt currencies
1896 ***************************************************/
1897 UPDATE AR_PAYMENTS_INTERFACE
1898 SET invoice2_status = 'AR_PLB_CURR_CONFLICT'
1899 WHERE rowid = l_rowid;
1900 END IF;
1901 ELSE /* Bug 2066392. Single currency */
1902 IF l_amount_applied_from2 is not null THEN
1903 IF l_amount_applied2 is not null THEN
1904 IF l_amount_applied_from2 <> l_amount_applied2 THEN
1905 UPDATE AR_PAYMENTS_INTERFACE
1906 SET invoice2_status = 'AR_PLB_CC_INVALID_VALUE'
1907 WHERE rowid = l_rowid;
1908 END IF;
1909 ELSE
1910 IF l_format_amount2 = 'Y' THEN
1911 fnd_currency.Get_Info(l_invoice_currency_code2,
1912 l_precision,
1913 l_extended_precision,
1914 l_mau);
1915 l_unformat_amount := l_amount_applied_from2 * power(10, l_precision);
1916 ELSE
1917 l_unformat_amount := l_amount_applied_from2;
1918 END IF;
1919 UPDATE AR_PAYMENTS_INTERFACE
1920 SET amount_applied2 = l_unformat_amount
1921 WHERE rowid = l_rowid;
1922 END IF;
1923 END IF;
1924 END IF;
1925 END IF;
1926 END IF; /* if matching number 2 is not null */
1927 <<END_2>>
1928
1929 /************************** checking 3rd trx_number *************************/
1930
1931 debug1('invoice3 is not null = ' || l_matching_number3);
1932 IF (l_matching_number3 is not NULL) THEN
1933
1934 /* added trx_amt_due_remX for bug 883345 */
1935 /* Changed the where clause and added the exception for bug 1052313
1936 and 1097549 */
1937 BEGIN
1938 SELECT sum(count(distinct ps.customer_trx_id))
1939 INTO l_tot_trx3
1940 FROM ar_payment_schedules ps
1941 WHERE ps.trx_number = l_matching_number3
1942 AND ps.trx_date = l_resolved_matching3_date /* Bug fix 2926664 */
1943 AND (EXISTS
1944 (
1945 select 'Exists from dual' from dual
1946 where l_customer_id = ps.customer_id
1947 union
1948 select 'Exists from hz_cust_acct_relate'
1949 from hz_cust_acct_relate rel
1950 where rel.cust_account_id = l_customer_id
1951 and related_cust_account_id = ps.customer_id
1952 and rel.status = 'A'
1953 and rel.bill_to_flag = 'Y'
1954 union
1955 select 'Exists ar_paying_relationships_v'
1959 and acc.cust_account_id = l_customer_id
1956 from ar_paying_relationships_v rel,
1957 hz_cust_accounts acc
1958 where rel.party_id = acc.party_id
1960 and rel.related_cust_account_id = ps.customer_id
1961 and l_receipt_date BETWEEN effective_start_date
1962 AND effective_end_date
1963 )
1964 or
1965 l_pay_unrelated_invoices = 'Y'
1966 )
1967 GROUP BY ps.customer_trx_id
1968 HAVING sum(ps.amount_due_remaining) <> 0;
1969
1970 IF (l_tot_trx3 > 1) then
1971 update ar_payments_interface pi
1972 set invoice3_status = 'AR_PLB_DUP_INV'
1973 where rowid = l_rowid;
1974 goto END_3;
1975 ELSE
1976 SELECT invoice_currency_code,
1977 amount_due_remaining
1978 INTO ps_currency_code3,
1979 trx_amt_due_rem3
1980 FROM ar_payment_schedules ps,
1981 ra_cust_trx_types tt
1982 WHERE ps.trx_number = l_matching_number3
1983 AND ps.status = decode(tt.allow_overapplication_flag,
1984 'N', 'OP',
1985 ps.status)
1986 AND ps.class NOT IN ('PMT','GUAR')
1987 AND ps.payment_schedule_id =
1988 (select min(ps.payment_schedule_id)
1989 from ar_payment_schedules ps,
1990 ra_cust_trx_types tt
1991 where ps.trx_number = l_matching_number3
1992 and ps.trx_date = l_resolved_matching3_date /* Bug fix 2926664 */
1993 and (EXISTS
1994 (
1995 select 'Exists from dual' from dual
1996 where l_customer_id = ps.customer_id
1997 union
1998 select 'Exists from hz_cust_acct_relate'
1999 from hz_cust_acct_relate rel
2000 where rel.cust_account_id = l_customer_id
2001 and related_cust_account_id = ps.customer_id
2002 and rel.status = 'A'
2003 and rel.bill_to_flag = 'Y'
2004 union
2005 select 'Exists ar_paying_relationships_v'
2006 from ar_paying_relationships_v rel,
2007 hz_cust_accounts acc
2008 where rel.party_id = acc.party_id
2009 and acc.cust_account_id = l_customer_id
2010 and rel.related_cust_account_id = ps.customer_id
2011 and l_receipt_date BETWEEN effective_start_date
2012 AND effective_end_date
2013 )
2014 or
2015 l_pay_unrelated_invoices = 'Y'
2016 )
2017 and ps.cust_trx_type_id = tt.cust_trx_type_id
2018 and ps.class NOT IN ('PMT','GUAR')
2019 and ps.status=decode(tt.allow_overapplication_flag,
2020 'N','OP',
2021 ps.status))
2022 and (EXISTS
2023 (
2024 select 'Exists from dual' from dual
2025 where l_customer_id = ps.customer_id
2026 union
2027 select 'Exists from hz_cust_acct_relate'
2028 from hz_cust_acct_relate rel
2029 where rel.cust_account_id = l_customer_id
2030 and related_cust_account_id = ps.customer_id
2031 and rel.status = 'A'
2032 and rel.bill_to_flag = 'Y'
2033 union
2034 select 'Exists ar_paying_relationships_v'
2035 from ar_paying_relationships_v rel,
2036 hz_cust_accounts acc
2037 where rel.party_id = acc.party_id
2038 and acc.cust_account_id = l_customer_id
2039 and rel.related_cust_account_id = ps.customer_id
2040 and l_receipt_date BETWEEN effective_start_date
2041 AND effective_end_date
2042 )
2043 or
2044 l_pay_unrelated_invoices = 'Y'
2045 )
2046 AND ps.cust_trx_type_id = tt.cust_trx_type_id;
2047 END IF;
2048 EXCEPTION
2049 WHEN NO_DATA_FOUND THEN
2050 goto END_3;
2051 END;
2052
2053 debug1('currency code3 of invoice from the ps = ' || ps_currency_code3);
2054 /********************************************************
2055 * if transmission has null currency code use the one
2056 * from Payment Schedules
2057 *********************************************************/
2058 IF (l_invoice_currency_code3 is NULL ) THEN
2059 debug1('currency code is null.. setting = ' || ps_currency_code3);
2060 l_invoice_currency_code3 := ps_currency_code3;
2061
2062 /* update ar_payment_interface to have the invoice currency_code */
2063 UPDATE ar_payments_interface
2064 SET invoice_currency_code3 = l_invoice_currency_code3
2065 WHERE rowid = l_rowid;
2066 END IF; /* end if invoice currency code was null */
2067
2068 /****************************************************************
2069 * check to see if the currency code matches or is was not included
2070 * in the transmission
2071 ****************************************************************/
2075 IF (l_invoice_currency_code3 <> ps_currency_code3) then
2072 debug1('l_invoice_currency_code3 = ' || l_invoice_currency_code3);
2073 debug1('ps_currency_code = ' || ps_currency_code3);
2074
2076 debug1('currency code give does not match payment schedules..');
2077 UPDATE AR_PAYMENTS_INTERFACE
2078 SET invoice3_status = 'AR_PLB_CURRENCY_BAD'
2079 WHERE rowid = l_rowid;
2080 ELSE
2081 /* Bug:1513671 we know the invoice currency code so we can now format the
2082 amount applied if we need to */
2083
2084 IF (p_format_amount3 = 'Y') THEN
2085 fnd_currency.Get_Info(l_invoice_currency_code3,
2086 l_precision,
2087 l_extended_precision,
2088 l_mau);
2089 l_amount_applied3 := round(l_amount_applied3 / power(10, l_precision),
2090 l_precision);
2091 END IF;
2092
2093 /*************************************************************
2094 * if the currency code of the transaction does not equal the
2095 * currency code of the receipt, then check for cross currency
2096 * or Euro case
2097 **************************************************************/
2098 IF ( l_invoice_currency_code3 <> l_currency_code) THEN
2099 debug1('currency code of receipt does not match currency code of inv');
2100
2101 /***********************************************************
2102 * we need to check to see if we have cross currency
2103 * profile enabled or we are dealing with a fixed rate
2104 * currency
2105 ************************************************************/
2106
2107 l_is_fixed_rate := gl_currency_api.is_fixed_rate(
2108 l_currency_code, /*receipt currency */
2109 l_invoice_currency_code3, /* inv currency */
2110 nvl(l_receipt_date,sysdate));
2111
2112 debug1('is this a fixed rate = ' || l_is_fixed_rate);
2113 debug1('is cross curr enabled?? ' || l_enable_cross_currency);
2114
2115 IF ( (l_is_fixed_rate = 'Y') or
2116 (l_enable_cross_currency = 'Y')) THEN
2117 /* we have to make sure that all fields are populated */
2118
2119 IF ( (l_amount_applied_from3 IS NULL) or
2120 (l_amount_applied3 IS NULL) or
2121 (l_trans_to_receipt_rate3 IS NULL) ) THEN
2122
2123 /* we need to check the rate 1st. If both amounts columns are
2124 populated, then we calculate the rate. If one amount is
2125 missing and the rate is null, then we try to get the rate from
2126 GL based on the profile option */
2127
2128 IF ( l_trans_to_receipt_rate3 is NULL) THEN
2129 debug1('trans_to_receipt_rate3 is null');
2130 /* if neither amount is null then we calculate the rate */
2131
2132 debug1('amount applied = ' || to_char(l_amount_applied3));
2133 debug1('amount applied from = ' || to_char(l_amount_applied_from3));
2134 IF ( (l_amount_applied3 IS NOT NULL) and
2135 (l_amount_applied_from3 IS NOT NULL)) Then
2136
2137 /********************************************
2138 * if we have a fixed rate, we need to get the
2139 * rate from GL and verify the validity of the
2140 * 2 amount columns with the rate that we get
2141 **********************************************/
2142
2143 IF (l_is_fixed_rate = 'Y') THEN
2144 /* get the fixed rate from GL */
2145 l_trans_to_receipt_rate3 :=
2146 gl_currency_api.get_rate(
2147 l_invoice_currency_code3,
2148 l_currency_code,
2149 l_receipt_date);
2150
2151 /*************************************************
2152 * if all fields are populated, we need to
2153 * check to make sure that they are logically
2154 * correct.
2155 ************************************************/
2156 calc_amt_applied_fmt(l_invoice_currency_code3,
2157 l_amount_applied_from3,
2158 l_trans_to_receipt_rate3,
2159 l_temp_amt_applied,
2160 'N');
2161
2162 calc_amt_applied_from_fmt(l_currency_code,
2163 l_amount_applied3,
2164 l_trans_to_receipt_rate3,
2165 l_temp_amt_applied_from,
2166 'N');
2167
2168 ar_cc_lockbox.calc_cross_rate(
2169 l_amount_applied3,
2170 l_amount_applied_from3,
2171 l_invoice_currency_code3,
2172 l_currency_code,
2173 l_temp_trans_to_receipt_rate);
2174 IF ( (l_temp_amt_applied = l_amount_applied3) OR
2175 (l_temp_amt_applied_from =
2176 l_amount_applied_from3) OR
2177 (l_temp_trans_to_receipt_rate =
2181 * since one or more of the conditions are
2178 l_trans_to_receipt_rate3)) THEN
2179
2180 /********************************************
2182 * true then we assume that everything is
2183 * fine and we can write the rate to the
2184 * database
2185 *********************************************/
2186 debug1('validation passed ' );
2187 UPDATE ar_payments_interface
2188 SET trans_to_receipt_rate3 =
2189 l_trans_to_receipt_rate3
2190 WHERE rowid = l_rowid;
2191 ELSE
2192 UPDATE AR_PAYMENTS_INTERFACE
2193 SET invoice3_status =
2194 'AR_PLB_CC_INVALID_VALUE'
2195 WHERE rowid = l_rowid;
2196 END IF;
2197 ELSE
2198 /* calculate the least rate that would convert
2199 the items */
2200 ar_cc_lockbox.calc_cross_rate(
2201 l_amount_applied3,
2202 l_amount_applied_from3,
2203 l_invoice_currency_code3,
2204 l_currency_code,
2205 l_trans_to_receipt_rate3);
2206
2207 /* once the rate has been calculated, we need
2208 to write it to the table */
2209 UPDATE ar_payments_interface
2210 SET trans_to_receipt_rate3 =
2211 l_trans_to_receipt_rate3
2212 WHERE rowid = l_rowid;
2213 END IF;
2214
2215 ELSE
2216 /* need to derive the rate if possible*/
2217 debug1( 'need to derive rate ');
2218 IF (p_default_exchange_rate_type IS NOT NULL or
2219 l_is_fixed_rate = 'Y' ) THEN
2220 l_trans_to_receipt_rate3 :=
2221 gl_currency_api.get_rate_sql(
2222 l_invoice_currency_code3,
2223 l_currency_code,
2224 l_receipt_date,
2225 p_default_exchange_rate_type);
2226 debug1('calculated rate = ' || to_char(l_trans_to_receipt_rate3));
2227 /* if there is no rate in GL, there is nothing
2228 more we can do */
2229 IF (l_trans_to_receipt_rate3 < 0 ) THEN
2230
2231 UPDATE AR_PAYMENTS_INTERFACE
2232 SET invoice3_status = 'AR_PLB_NO_EXCHANGE_RATE'
2233 WHERE rowid = l_rowid;
2234
2235 ELSE
2236 /* once the rate has been calculated, we need
2237 to write it to the table */
2238
2239 debug1('writing rate to database ' || to_char(l_trans_to_receipt_rate3));
2240
2241 UPDATE ar_payments_interface
2242 SET trans_to_receipt_rate3 =
2243 l_trans_to_receipt_rate3
2244 WHERE rowid = l_rowid;
2245
2249 calc_amt_applied_from_fmt(l_currency_code,
2246 /* finish the calculation because we have a rate*/
2247
2248 IF (l_amount_applied_from3 IS NULL) THEN
2250 l_amount_applied3,
2251 l_trans_to_receipt_rate3,
2252 l_amount_applied_from3,
2253 l_format_amount_applied_from3);
2254
2255 update ar_payments_interface
2256 set amount_applied_from3 =
2257 l_amount_applied_from3
2258 where rowid = l_rowid;
2259
2260 ELSE
2261 /* calculate amount applied and save to db */
2262 calc_amt_applied_fmt(l_invoice_currency_code3,
2263 l_amount_applied_from3,
2264 l_trans_to_receipt_rate3,
2265 l_amount_applied3,
2266 l_format_amount3);
2267 debug1('calculated amt applied = ' || to_char(l_amount_applied3));
2268
2269 /* to deal with rounding errors that happen
2270 with fixed rate currencies, we need to
2271 take the amount_due_remaining of the trx
2272 convert it to the receipt currency and
2273 compare that value with the
2274 amount_applied_from original value
2275
2276 ADDED for BUG 883345 */
2277
2278 IF (l_is_fixed_rate = 'Y') THEN
2279
2280 calc_amt_applied_from_fmt(l_currency_code,
2281 trx_amt_due_rem3,
2282 l_trans_to_receipt_rate3,
2283 amt_applied_from_tc,
2284 'N');
2285
2286 IF (amt_applied_from_tc =
2290 fnd_currency.Get_Info(
2287 l_amount_applied_from3 ) THEN
2288 IF (l_format_amount3 = 'Y') THEN
2289
2291 l_invoice_currency_code3,
2292 l_precision,
2293 l_extended_precision,
2294 l_mau);
2295
2296 l_amount_applied3 :=
2297 trx_amt_due_rem3 * (10**l_precision);
2298 ELSE
2299 l_amount_applied3 := trx_amt_due_rem3 ;
2300 END IF;
2301 END IF;
2302 END IF;
2303
2304
2305 update ar_payments_interface
2306 set amount_applied3 =
2307 l_amount_applied3
2308 where rowid = l_rowid;
2309
2310 END IF; /* if amount_applied_From3 is null */
2311 END IF; /* if rate is null after it is calc */
2312 ELSE /* Bug 1519765 */
2313 UPDATE AR_PAYMENTS_INTERFACE
2314 SET invoice3_status = 'AR_PLB_NO_EXCHANGE_RATE'
2315 WHERE rowid = l_rowid;
2316 END IF; /* if derive profile is not null */
2317 END IF;
2318 /* we know that trans_to_receipt_rate3 is not null,
2319 therefore, one of the amount values must be null and
2320 we have to calculate it */
2321 ELSE
2322 /* is amount_applied_From null?? */
2323 IF (l_amount_applied_from3 IS NULL) THEN
2324 calc_amt_applied_from_fmt(l_currency_code,
2325 l_amount_applied3,
2326 l_trans_to_receipt_rate3,
2327 l_amount_applied_from3,
2328 l_format_amount_applied_from3);
2329 update ar_payments_interface
2330 set amount_applied_from3 =
2331 l_amount_applied_from3
2332 where rowid = l_rowid;
2333
2334 ELSE
2335 calc_amt_applied_fmt(l_invoice_currency_code3,
2336 l_amount_applied_from3,
2337 l_trans_to_receipt_rate3,
2338 l_amount_applied3,
2339 l_format_amount3);
2340
2341 /* to deal with rounding errors that happen
2342 with fixed rate currencies, we need to
2343 take the amount_due_remaining of the trx
2344 convert it to the receipt currency and
2345 compare that value with the
2346 amount_applied_from original value
2347
2348 ADDED for BUG 883345 */
2349
2350 IF (l_is_fixed_rate = 'Y') THEN
2351
2352 calc_amt_applied_from_fmt(l_currency_code,
2353 trx_amt_due_rem3,
2354 l_trans_to_receipt_rate3,
2355 amt_applied_from_tc,
2356 'N');
2357
2358 IF (amt_applied_from_tc =
2359 l_amount_applied_from3 ) THEN
2360 IF (l_format_amount3 = 'Y') THEN
2361
2362 fnd_currency.Get_Info(
2363 l_invoice_currency_code3,
2364 l_precision,
2365 l_extended_precision,
2366 l_mau);
2367
2368 l_amount_applied3 :=
2369 trx_amt_due_rem3 * (10**l_precision);
2370 ELSE
2371 l_amount_applied3 := trx_amt_due_rem3 ;
2372 END IF;
2373 END IF;
2374 END IF;
2375
2376 UPDATE AR_PAYMENTS_INTERFACE
2377 SET amount_applied3 =
2378 l_amount_applied3
2379 WHERE rowid = l_rowid;
2380
2381 END IF;
2382 END IF; /* trans to receipt_rate 3 is null */
2383 ELSE
2384 /*************************************************
2385 * if all fields are populated, we need to
2386 * check to make sure that they are logically
2387 * correct.
2388 ***************************************************/
2389
2390 calc_amt_applied_fmt(l_invoice_currency_code3,
2391 l_amount_applied_from3,
2392 l_trans_to_receipt_rate3,
2393 l_temp_amt_applied,
2394 'N');
2395
2396 calc_amt_applied_from_fmt(l_currency_code,
2397 l_amount_applied3,
2398 l_trans_to_receipt_rate3,
2399 l_temp_amt_applied_from,
2400 'N');
2401
2402 ar_cc_lockbox.calc_cross_rate(
2403 l_amount_applied3,
2404 l_amount_applied_from3,
2405 l_invoice_currency_code3,
2406 l_currency_code,
2407 l_temp_trans_to_receipt_rate);
2408 IF ( (l_temp_amt_applied = l_amount_applied3) OR
2409 (l_temp_amt_applied_from =
2410 l_amount_applied_from3) OR
2411 (l_temp_trans_to_receipt_rate =
2412 l_trans_to_receipt_rate3)) THEN
2413
2414 /* since one or more of the conditions are true
2415 then we assume that everything is fine. */
2416 debug1('validation passed ' );
2417 debug1('validation passed ' );
2418 ELSE
2419 UPDATE AR_PAYMENTS_INTERFACE
2420 SET invoice3_status = 'AR_PLB_CC_INVALID_VALUE'
2421 WHERE rowid = l_rowid;
2422 END IF;
2423
2424 END IF; /* if one of the 3 items is NULL */
2425 ELSE
2426 /*****************************************************
2427 currencies do not match, they are not fixed rate and
2428 cross currency enabled profile is not on.
2429 then set the status to be a currency conflict between
2430 the invoice and receipt currencies
2431 ***************************************************/
2432 UPDATE AR_PAYMENTS_INTERFACE
2433 SET invoice3_status = 'AR_PLB_CURR_CONFLICT'
2434 WHERE rowid = l_rowid;
2435 END IF;
2436 ELSE /* Bug 2066392. Single currency */
2437 IF l_amount_applied_from3 is not null THEN
2438 IF l_amount_applied3 is not null THEN
2439 IF l_amount_applied_from3 <> l_amount_applied3 THEN
2440 UPDATE AR_PAYMENTS_INTERFACE
2441 SET invoice3_status = 'AR_PLB_CC_INVALID_VALUE'
2442 WHERE rowid = l_rowid;
2443 END IF;
2444 ELSE
2445 IF l_format_amount3 = 'Y' THEN
2446 fnd_currency.Get_Info(l_invoice_currency_code3,
2447 l_precision,
2448 l_extended_precision,
2449 l_mau);
2450 l_unformat_amount := l_amount_applied_from3 * power(10, l_precision);
2451 ELSE
2452 l_unformat_amount := l_amount_applied_from3;
2453 END IF;
2454 UPDATE AR_PAYMENTS_INTERFACE
2455 SET amount_applied3 = l_unformat_amount
2456 WHERE rowid = l_rowid;
2457 END IF;
2458 END IF;
2459 END IF;
2460 END IF;
2461 END IF; /* if matching number 3 is not null */
2462 <<END_3>>
2463
2464 /**************** need to check matching number 4 *************/
2465
2466 /* checking 4th trx_number */
2467 debug1('invoice4 is not null = ' || l_matching_number4);
2468 IF (l_matching_number4 is not NULL) THEN
2469
2470 /* added trx_amt_due_remX for bug 883345 */
2471 /* Changed the where clause and added the exception for bug 1052313
2472 and 1097549 */
2473 BEGIN
2474 SELECT sum(count(distinct ps.customer_trx_id))
2475 INTO l_tot_trx4
2476 FROM ar_payment_schedules ps
2477 WHERE ps.trx_number = l_matching_number4
2478 AND ps.trx_date = l_resolved_matching4_date /* Bug fix 2926664 */
2479 AND (EXISTS
2480 (
2481 select 'Exists from dual' from dual
2482 where l_customer_id = ps.customer_id
2483 union
2484 select 'Exists from hz_cust_acct_relate'
2485 from hz_cust_acct_relate rel
2486 where rel.cust_account_id = l_customer_id
2487 and related_cust_account_id = ps.customer_id
2488 and rel.status = 'A'
2489 and rel.bill_to_flag = 'Y'
2490 union
2491 select 'Exists ar_paying_relationships_v'
2492 from ar_paying_relationships_v rel,
2493 hz_cust_accounts acc
2494 where rel.party_id = acc.party_id
2495 and acc.cust_account_id = l_customer_id
2496 and rel.related_cust_account_id = ps.customer_id
2497 and l_receipt_date BETWEEN effective_start_date
2498 AND effective_end_date
2499 )
2500 or
2501 l_pay_unrelated_invoices = 'Y'
2502 )
2503 GROUP BY ps.customer_trx_id
2504 HAVING sum(ps.amount_due_remaining) <> 0;
2505
2506 IF (l_tot_trx4 > 1) then
2507 update ar_payments_interface pi
2508 set invoice4_status = 'AR_PLB_DUP_INV'
2509 where rowid = l_rowid;
2510 goto END_4;
2511 ELSE
2512 SELECT invoice_currency_code,
2513 amount_due_remaining
2514 INTO ps_currency_code4,
2515 trx_amt_due_rem4
2516 FROM ar_payment_schedules ps,
2517 ra_cust_trx_types tt
2518 WHERE ps.trx_number = l_matching_number4
2519 AND ps.status = decode(tt.allow_overapplication_flag,
2520 'N', 'OP',
2521 ps.status)
2522 AND ps.class NOT IN ('PMT','GUAR')
2523 AND ps.payment_schedule_id =
2524 (select min(ps.payment_schedule_id)
2525 from ar_payment_schedules ps,
2526 ra_cust_trx_types tt
2527 where ps.trx_number = l_matching_number4
2528 and ps.trx_date = l_resolved_matching4_date /* Bug fix 2926664 */
2529 and (EXISTS
2530 (
2531 select 'Exists from dual' from dual
2532 where l_customer_id = ps.customer_id
2533 union
2534 select 'Exists from hz_cust_acct_relate'
2535 from hz_cust_acct_relate rel
2536 where rel.cust_account_id = l_customer_id
2537 and related_cust_account_id = ps.customer_id
2538 and rel.status = 'A'
2539 and rel.bill_to_flag = 'Y'
2540 union
2541 select 'Exists ar_paying_relationships_v'
2542 from ar_paying_relationships_v rel,
2543 hz_cust_accounts acc
2544 where rel.party_id = acc.party_id
2545 and acc.cust_account_id = l_customer_id
2546 and rel.related_cust_account_id = ps.customer_id
2547 and l_receipt_date BETWEEN effective_start_date
2548 AND effective_end_date
2549 )
2550 or
2551 l_pay_unrelated_invoices = 'Y'
2552 )
2553 and ps.cust_trx_type_id = tt.cust_trx_type_id
2557 ps.status))
2554 and ps.class NOT IN ('PMT','GUAR')
2555 and ps.status=decode(tt.allow_overapplication_flag,
2556 'N' , 'OP',
2558 and (EXISTS
2559 (
2560 select 'Exists from dual' from dual
2561 where l_customer_id = ps.customer_id
2562 union
2563 select 'Exists from hz_cust_acct_relate'
2564 from hz_cust_acct_relate rel
2565 where rel.cust_account_id = l_customer_id
2566 and related_cust_account_id = ps.customer_id
2567 and rel.status = 'A'
2568 and rel.bill_to_flag = 'Y'
2569 union
2570 select 'Exists ar_paying_relationships_v'
2571 from ar_paying_relationships_v rel,
2572 hz_cust_accounts acc
2573 where rel.party_id = acc.party_id
2574 and acc.cust_account_id = l_customer_id
2575 and rel.related_cust_account_id = ps.customer_id
2576 and l_receipt_date BETWEEN effective_start_date
2577 AND effective_end_date
2578 )
2579 or
2580 l_pay_unrelated_invoices = 'Y'
2581 )
2582 AND ps.cust_trx_type_id = tt.cust_trx_type_id;
2583 END IF;
2584 EXCEPTION
2585 WHEN NO_DATA_FOUND THEN
2586 goto END_4;
2587 END;
2588
2589 debug1('currency code1 of invoice from the ps = ' || ps_currency_code4);
2590 /********************************************************
2591 * if transmission has null currency code use the one
2592 * from Payment Schedules
2593 *********************************************************/
2594 IF (l_invoice_currency_code4 is NULL ) THEN
2598 /* update ar_payment_interface to have the invoice currency_code */
2595 debug1('currency code is null.. setting = ' || ps_currency_code4);
2596 l_invoice_currency_code4 := ps_currency_code4;
2597
2599 UPDATE ar_payments_interface
2600 SET invoice_currency_code4 = l_invoice_currency_code4
2601 WHERE rowid = l_rowid;
2602 END IF; /* end if invoice currency code was null */
2603
2604 /****************************************************************
2605 * check to see if the currency code matches or is was not included
2606 * in the transmission
2607 ****************************************************************/
2608 debug1('l_invoice_currency_code4 = ' || l_invoice_currency_code4);
2609 debug1('ps_currency_code = ' || ps_currency_code4);
2610
2611 IF (l_invoice_currency_code4 <> ps_currency_code4) then
2612 debug1('currency code give does not match payment schedules..');
2613 UPDATE AR_PAYMENTS_INTERFACE
2614 SET invoice4_status = 'AR_PLB_CURRENCY_BAD'
2615 WHERE rowid = l_rowid;
2616 ELSE
2617 /* Bug:1513671 we know the invoice currency code so we can now format the
2618 amount applied if we need to */
2619
2620 IF (p_format_amount4 = 'Y') THEN
2621 fnd_currency.Get_Info(l_invoice_currency_code4,
2622 l_precision,
2623 l_extended_precision,
2624 l_mau);
2625 l_amount_applied4 := round(l_amount_applied4 / power(10, l_precision),
2626 l_precision);
2627 END IF;
2628
2629 /*************************************************************
2630 * if the currency code of the transaction does not equal the
2631 * currency code of the receipt, then check for cross currency
2632 * or Euro case
2633 **************************************************************/
2634 IF ( l_invoice_currency_code4 <> l_currency_code) THEN
2635 debug1('currency code of receipt does not match currency code of inv');
2636
2637 /***********************************************************
2638 * we need to check to see if we have cross currency
2639 * profile enabled or we are dealing with a fixed rate
2640 * currency
2641 ************************************************************/
2642
2643 l_is_fixed_rate := gl_currency_api.is_fixed_rate(
2644 l_currency_code, /*receipt currency */
2645 l_invoice_currency_code4, /* inv currency */
2646 nvl(l_receipt_date,sysdate));
2647
2648 debug1('is this a fixed rate = ' || l_is_fixed_rate);
2649 debug1('is cross curr enabled?? ' || l_enable_cross_currency);
2650
2651 IF ( (l_is_fixed_rate = 'Y') or
2652 (l_enable_cross_currency = 'Y')) THEN
2653 /* we have to make sure that all fields are populated */
2654
2655 IF ( (l_amount_applied_from4 IS NULL) or
2656 (l_amount_applied4 IS NULL) or
2657 (l_trans_to_receipt_rate4 IS NULL) ) THEN
2658
2659 /* we need to check the rate 1st. If both amounts columns are
2660 populated, then we calculate the rate. If one amount is
2661 missing and the rate is null, then we try to get the rate from
2662 GL based on the profile option */
2663
2664 IF ( l_trans_to_receipt_rate4 is NULL) THEN
2665 debug1('trans_to_receipt_rate4 is null');
2666 /* if neither amount is null then we calculate the rate */
2667
2668 debug1('amount applied = ' || to_char(l_amount_applied1));
2669 debug1('amount applied from = ' || to_char(l_amount_applied_from4));
2670 IF ( (l_amount_applied4 IS NOT NULL) and
2671 (l_amount_applied_from4 IS NOT NULL)) Then
2672
2673 /********************************************
2674 * if we have a fixed rate, we need to get the
2675 * rate from GL and verify the validity of the
2676 * 2 amount columns with the rate that we get
2677 **********************************************/
2678 IF (l_is_fixed_rate = 'Y') THEN
2679 /* get the fixed rate from GL */
2680 l_trans_to_receipt_rate4 :=
2681 gl_currency_api.get_rate(
2685
2682 l_invoice_currency_code4,
2683 l_currency_code,
2684 l_receipt_date);
2686 /*************************************************
2687 * if all fields are populated, we need to
2688 * check to make sure that they are logically
2689 * correct.
2690 ************************************************/
2691 calc_amt_applied_fmt(l_invoice_currency_code4,
2692 l_amount_applied_from4,
2693 l_trans_to_receipt_rate4,
2694 l_temp_amt_applied,
2695 'N');
2696
2700 l_temp_amt_applied_from,
2697 calc_amt_applied_from_fmt(l_currency_code,
2698 l_amount_applied4,
2699 l_trans_to_receipt_rate4,
2701 'N');
2702
2703 ar_cc_lockbox.calc_cross_rate(
2704 l_amount_applied4,
2705 l_amount_applied_from4,
2706 l_invoice_currency_code4,
2707 l_currency_code,
2708 l_temp_trans_to_receipt_rate);
2709 IF ( (l_temp_amt_applied = l_amount_applied4) OR
2710 (l_temp_amt_applied_from =
2711 l_amount_applied_from4) OR
2712 (l_temp_trans_to_receipt_rate =
2713 l_trans_to_receipt_rate4)) THEN
2714
2715 /********************************************
2716 * since one or more of the conditions are
2717 * true then we assume that everything is
2718 * fine and we can write the rate to the
2719 * database
2720 *********************************************/
2721 debug1('validation passed ' );
2722 UPDATE ar_payments_interface
2723 SET trans_to_receipt_rate4 =
2724 l_trans_to_receipt_rate4
2725 WHERE rowid = l_rowid;
2726 ELSE
2727 UPDATE AR_PAYMENTS_INTERFACE
2728 SET invoice4_status =
2729 'AR_PLB_CC_INVALID_VALUE'
2730 WHERE rowid = l_rowid;
2731 END IF;
2732
2733 ELSE
2734 /* calculate the least rate that would convert
2735 the items */
2736 ar_cc_lockbox.calc_cross_rate(
2737 l_amount_applied4,
2738 l_amount_applied_from4,
2739 l_invoice_currency_code4,
2740 l_currency_code,
2741 l_trans_to_receipt_rate4);
2742
2743 /* once the rate has been calculated, we need
2744 to write it to the table */
2745 UPDATE ar_payments_interface
2746 SET trans_to_receipt_rate4 =
2747 l_trans_to_receipt_rate4
2748 WHERE rowid = l_rowid;
2749 END IF;
2750
2751 ELSE
2752 /* need to derive the rate if possible*/
2753 debug1( 'need to derive rate ');
2754 IF (p_default_exchange_rate_type IS NOT NULL or
2755 l_is_fixed_rate = 'Y' ) THEN
2756 l_trans_to_receipt_rate4 :=
2757 gl_currency_api.get_rate_sql(
2758 l_invoice_currency_code4,
2759 l_currency_code,
2760 l_receipt_date,
2761 p_default_exchange_rate_type);
2762 debug1('calculated rate = ' || to_char(l_trans_to_receipt_rate4));
2763 /* if there is no rate in GL, there is nothing
2764 more we can do */
2765 IF (l_trans_to_receipt_rate4 < 0 ) THEN
2766
2767 UPDATE AR_PAYMENTS_INTERFACE
2768 SET invoice4_status = 'AR_PLB_NO_EXCHANGE_RATE'
2769 WHERE rowid = l_rowid;
2770
2771 ELSE
2772 /* once the rate has been calculated, we need
2773 to write it to the table */
2774
2775 debug1('writing rate to database ' || to_char(l_trans_to_receipt_rate4));
2776
2777 UPDATE ar_payments_interface
2778 SET trans_to_receipt_rate4 =
2779 l_trans_to_receipt_rate4
2780 WHERE rowid = l_rowid;
2781
2782 /* finish the calculation because we have a rate*/
2783
2784 IF (l_amount_applied_from4 IS NULL) THEN
2785 calc_amt_applied_from_fmt(l_currency_code,
2786 l_amount_applied4,
2787 l_trans_to_receipt_rate4,
2788 l_amount_applied_from4,
2789 l_format_amount_applied_from4);
2790 update ar_payments_interface
2791 set amount_applied_from4 =
2792 l_amount_applied_from4
2793 where rowid = l_rowid;
2794
2795 ELSE
2799 l_trans_to_receipt_rate4,
2796 /* calculate amount applied and save to db */
2797 calc_amt_applied_fmt(l_invoice_currency_code4,
2798 l_amount_applied_from4,
2800 l_amount_applied4,
2801 l_format_amount4);
2802 debug1('calculated amt applied = ' || to_char(l_amount_applied4));
2803
2807 convert it to the receipt currency and
2804 /* to deal with rounding errors that happen
2805 with fixed rate currencies, we need to
2806 take the amount_due_remaining of the trx
2808 compare that value with the
2809 amount_applied_from original value
2810
2811 ADDED for BUG 883345 */
2812
2813 IF (l_is_fixed_rate = 'Y') THEN
2814
2815 calc_amt_applied_from_fmt(l_currency_code,
2816 trx_amt_due_rem4,
2817 l_trans_to_receipt_rate4,
2818 amt_applied_from_tc,
2819 'N');
2820
2821 IF (amt_applied_from_tc =
2822 l_amount_applied_from4 ) THEN
2823 IF (l_format_amount4 = 'Y') THEN
2824 fnd_currency.Get_Info(
2825 l_invoice_currency_code4,
2826 l_precision,
2827 l_extended_precision,
2828 l_mau);
2829
2830 l_amount_applied4 :=
2831 trx_amt_due_rem4 * (10**l_precision);
2832 ELSE
2833 l_amount_applied4 := trx_amt_due_rem4 ;
2834 END IF;
2835 END IF;
2836 END IF;
2837
2838
2839 update ar_payments_interface
2840 set amount_applied4 =
2841 l_amount_applied4
2842 where rowid = l_rowid;
2843
2844 END IF; /* if amount_applied_From4 is null */
2845 END IF; /* if rate is null after it is calc */
2846 ELSE /* Bug 1519765 */
2847 UPDATE AR_PAYMENTS_INTERFACE
2848 SET invoice4_status = 'AR_PLB_NO_EXCHANGE_RATE'
2849 WHERE rowid = l_rowid;
2850 END IF; /* if derive profile is not null */
2851 END IF;
2852
2853 /* we know that trans_to_receipt_rate4 is not null,
2854 therefore, one of the amount values must be null and
2855 we have to calculate it */
2856 ELSE
2857 /* is amount_applied_From null?? */
2858 IF (l_amount_applied_from4 IS NULL) THEN
2859 calc_amt_applied_from_fmt(l_currency_code,
2860 l_amount_applied4,
2861 l_trans_to_receipt_rate4,
2862 l_amount_applied_from4,
2863 l_format_amount_applied_from4);
2864 update ar_payments_interface
2865 set amount_applied_from4 =
2866 l_amount_applied_from4
2867 where rowid = l_rowid;
2868
2869 ELSE
2870 calc_amt_applied_fmt(l_invoice_currency_code4,
2871 l_amount_applied_from4,
2872 l_trans_to_receipt_rate4,
2873 l_amount_applied4,
2874 l_format_amount4);
2875
2876 /* to deal with rounding errors that happen
2877 with fixed rate currencies, we need to
2878 take the amount_due_remaining of the trx
2879 convert it to the receipt currency and
2880 compare that value with the
2881 amount_applied_from original value
2882
2883 ADDED for BUG 883345 */
2884
2885 IF (l_is_fixed_rate = 'Y') THEN
2886
2887 calc_amt_applied_from_fmt(l_currency_code,
2888 trx_amt_due_rem4,
2892 IF (amt_applied_from_tc =
2889 l_trans_to_receipt_rate4,
2890 amt_applied_from_tc, 'N');
2891
2893 l_amount_applied_from4 ) THEN
2894 IF (l_format_amount4 = 'Y') THEN
2895 fnd_currency.Get_Info(
2896 l_invoice_currency_code4,
2897 l_precision,
2898 l_extended_precision,
2899 l_mau);
2900
2901 l_amount_applied4 :=
2902 trx_amt_due_rem4 * (10**l_precision);
2903 ELSE
2904 l_amount_applied4 := trx_amt_due_rem4 ;
2905 END IF;
2906 END IF;
2907 END IF;
2908
2909 UPDATE AR_PAYMENTS_INTERFACE
2910 SET amount_applied4 =
2911 l_amount_applied4
2912 WHERE rowid = l_rowid;
2913
2914 END IF;
2915 END IF; /* trans to receipt_rate 4 is null */
2916 ELSE
2917 /*************************************************
2918 * if all fields are populated, we need to
2919 * check to make sure that they are logically
2920 * correct.
2921 ***************************************************/
2922
2923 calc_amt_applied_fmt(l_invoice_currency_code4,
2924 l_amount_applied_from4,
2925 l_trans_to_receipt_rate4,
2926 l_temp_amt_applied,
2927 'N');
2928
2929 calc_amt_applied_from_fmt(l_currency_code,
2930 l_amount_applied4,
2931 l_trans_to_receipt_rate4,
2932 l_temp_amt_applied_from,
2933 'N');
2934
2935 ar_cc_lockbox.calc_cross_rate(
2936 l_amount_applied4,
2937 l_amount_applied_from4,
2941 IF ( (l_temp_amt_applied = l_amount_applied4) OR
2938 l_invoice_currency_code4,
2939 l_currency_code,
2940 l_temp_trans_to_receipt_rate);
2942 (l_temp_amt_applied_from =
2943 l_amount_applied_from4) OR
2944 (l_temp_trans_to_receipt_rate =
2945 l_trans_to_receipt_rate4)) THEN
2946
2947 /* since one or more of the conditions are true
2948 then we assume that everything is fine. */
2949 debug1('validation passed ' );
2950 ELSE
2951 UPDATE AR_PAYMENTS_INTERFACE
2952 SET invoice4_status = 'AR_PLB_CC_INVALID_VALUE'
2953 WHERE rowid = l_rowid;
2954 END IF;
2955
2956 END IF; /* if one of the 3 items is NULL */
2957 ELSE
2958 /*****************************************************
2959 currencies do not match, they are not fixed rate and
2960 cross currency enabled profile is not on.
2961 then set the status to be a currency conflict between
2962 the invoice and receipt currencies
2963 ***************************************************/
2964 UPDATE AR_PAYMENTS_INTERFACE
2965 SET invoice4_status = 'AR_PLB_CURR_CONFLICT'
2966 WHERE rowid = l_rowid;
2967 END IF;
2968 ELSE /* Bug 2066392. Single currency */
2969 IF l_amount_applied_from4 is not null THEN
2970 IF l_amount_applied4 is not null THEN
2971 IF l_amount_applied_from4 <> l_amount_applied4 THEN
2972 UPDATE AR_PAYMENTS_INTERFACE
2973 SET invoice4_status = 'AR_PLB_CC_INVALID_VALUE'
2974 WHERE rowid = l_rowid;
2975 END IF;
2976 ELSE
2977 IF l_format_amount4 = 'Y' THEN
2978 fnd_currency.Get_Info(l_invoice_currency_code4,
2979 l_precision,
2980 l_extended_precision,
2981 l_mau);
2982 l_unformat_amount := l_amount_applied_from4 * power(10, l_precision);
2983 ELSE
2984 l_unformat_amount := l_amount_applied_from4;
2985 END IF;
2986 UPDATE AR_PAYMENTS_INTERFACE
2990 END IF;
2987 SET amount_applied4 = l_unformat_amount
2988 WHERE rowid = l_rowid;
2989 END IF;
2991 END IF;
2992 END IF;
2993 END IF; /* if matching number 4 is not null */
2994 <<END_4>>
2995
2996 /**************** need to check matching number 5 *************/
2997
2998 /* checking 5th trx_number */
2999 debug1('invoice5 is not null = ' || l_matching_number5);
3000 IF (l_matching_number5 is not NULL) THEN
3001
3002 /* added trx_amt_due_remX for bug 883345 */
3003 /* Changed the where clause and added the exception for bug 1052313
3004 and 1097549 */
3005 BEGIN
3006 SELECT sum(count(distinct ps.customer_trx_id))
3007 INTO l_tot_trx5
3008 FROM ar_payment_schedules ps
3009 WHERE ps.trx_number = l_matching_number5
3010 AND ps.trx_date = l_resolved_matching5_date /* Bug fix 2926664 */
3011 AND (EXISTS
3012 (
3013 select 'Exists from dual' from dual
3014 where l_customer_id = ps.customer_id
3015 union
3016 select 'Exists from hz_cust_acct_relate'
3017 from hz_cust_acct_relate rel
3018 where rel.cust_account_id = l_customer_id
3019 and related_cust_account_id = ps.customer_id
3020 and rel.status = 'A'
3021 and rel.bill_to_flag = 'Y'
3022 union
3023 select 'Exists ar_paying_relationships_v'
3024 from ar_paying_relationships_v rel,
3025 hz_cust_accounts acc
3026 where rel.party_id = acc.party_id
3027 and acc.cust_account_id = l_customer_id
3028 and rel.related_cust_account_id = ps.customer_id
3029 and l_receipt_date BETWEEN effective_start_date
3030 AND effective_end_date
3031 )
3032 or
3033 l_pay_unrelated_invoices = 'Y'
3034 )
3035 GROUP BY ps.customer_trx_id
3036 HAVING sum(ps.amount_due_remaining) <> 0;
3037
3038 IF (l_tot_trx5 > 1) then
3039 update ar_payments_interface pi
3040 set invoice5_status = 'AR_PLB_DUP_INV'
3041 where rowid = l_rowid;
3042 goto END_5;
3043 ELSE
3044 SELECT invoice_currency_code,
3045 amount_due_remaining
3046 INTO ps_currency_code5,
3047 trx_amt_due_rem5
3048 FROM ar_payment_schedules ps,
3049 ra_cust_trx_types tt
3050 WHERE ps.trx_number = l_matching_number5
3051 AND ps.status = decode(tt.allow_overapplication_flag,
3052 'N', 'OP',
3053 ps.status)
3054 AND ps.class NOT IN ('PMT','GUAR')
3055 AND ps.payment_schedule_id =
3056 (select min(ps.payment_schedule_id)
3057 from ar_payment_schedules ps,
3058 ra_cust_trx_types tt
3059 where ps.trx_number = l_matching_number5
3060 and ps.trx_date = l_resolved_matching5_date /* Bug fix 2926664 */
3061 and (EXISTS
3062 (
3063 select 'Exists from dual' from dual
3064 where l_customer_id = ps.customer_id
3065 union
3066 select 'Exists from hz_cust_acct_relate'
3067 from hz_cust_acct_relate rel
3068 where rel.cust_account_id = l_customer_id
3069 and related_cust_account_id = ps.customer_id
3070 and rel.status = 'A'
3071 and rel.bill_to_flag = 'Y'
3072 union
3073 select 'Exists ar_paying_relationships_v'
3077 and acc.cust_account_id = l_customer_id
3074 from ar_paying_relationships_v rel,
3075 hz_cust_accounts acc
3076 where rel.party_id = acc.party_id
3078 and rel.related_cust_account_id = ps.customer_id
3079 and l_receipt_date BETWEEN effective_start_date
3080 AND effective_end_date
3081 )
3082 or
3083 l_pay_unrelated_invoices = 'Y'
3084 )
3085 and ps.cust_trx_type_id = tt.cust_trx_type_id
3086 and ps.class NOT IN ('PMT','GUAR')
3087 and ps.status=decode(tt.allow_overapplication_flag,
3088 'N', 'OP',
3089 ps.status))
3090 and (EXISTS
3091 (
3092 select 'Exists from dual' from dual
3093 where l_customer_id = ps.customer_id
3094 union
3095 select 'Exists from hz_cust_acct_relate'
3096 from hz_cust_acct_relate rel
3097 where rel.cust_account_id = l_customer_id
3098 and related_cust_account_id = ps.customer_id
3099 and rel.status = 'A'
3100 and rel.bill_to_flag = 'Y'
3101 union
3102 select 'Exists ar_paying_relationships_v'
3103 from ar_paying_relationships_v rel,
3104 hz_cust_accounts acc
3105 where rel.party_id = acc.party_id
3106 and acc.cust_account_id = l_customer_id
3107 and rel.related_cust_account_id = ps.customer_id
3108 and l_receipt_date BETWEEN effective_start_date
3109 AND effective_end_date
3110 )
3111 or
3112 l_pay_unrelated_invoices = 'Y'
3113 )
3114 AND ps.cust_trx_type_id = tt.cust_trx_type_id;
3115 END IF;
3116 EXCEPTION
3117 WHEN NO_DATA_FOUND THEN
3118 goto END_5;
3119 END;
3120
3121 debug1('currency code5 of invoice from the ps = ' || ps_currency_code5);
3122 /********************************************************
3123 * if transmission has null currency code use the one
3124 * from Payment Schedules
3125 *********************************************************/
3126 IF (l_invoice_currency_code5 is NULL ) THEN
3127 debug1('currency code is null.. setting = ' || ps_currency_code5);
3128 l_invoice_currency_code5 := ps_currency_code5;
3129
3130 /* update ar_payment_interface to have the invoice currency_code */
3131 UPDATE ar_payments_interface
3132 SET invoice_currency_code5 = l_invoice_currency_code5
3133 WHERE rowid = l_rowid;
3134 END IF; /* end if invoice currency code was null */
3135
3136 /****************************************************************
3137 * check to see if the currency code matches or is was not included
3138 * in the transmission
3139 ****************************************************************/
3140 debug1('l_invoice_currency_code5 = ' || l_invoice_currency_code5);
3141 debug1('ps_currency_code = ' || ps_currency_code5);
3142
3143 IF (l_invoice_currency_code5 <> ps_currency_code5) then
3144 debug1('currency code give does not match payment schedules..');
3145 UPDATE AR_PAYMENTS_INTERFACE
3146 SET invoice5_status = 'AR_PLB_CURRENCY_BAD'
3147 WHERE rowid = l_rowid;
3148 ELSE
3149 /* Bug:1513671 we know the invoice currency code so we can now format the
3150 amount applied if we need to */
3151
3152 IF (p_format_amount5 = 'Y') THEN
3153 fnd_currency.Get_Info(l_invoice_currency_code5,
3154 l_precision,
3155 l_extended_precision,
3156 l_mau);
3160
3157 l_amount_applied5 := round(l_amount_applied5 / power(10, l_precision),
3158 l_precision);
3159 END IF;
3161 /*************************************************************
3162 * if the currency code of the transaction does not equal the
3163 * currency code of the receipt, then check for cross currency
3164 * or Euro case
3165 **************************************************************/
3166 IF ( l_invoice_currency_code5 <> l_currency_code) THEN
3167 debug1('currency code of receipt does not match currency code of inv');
3168
3169 /***********************************************************
3170 * we need to check to see if we have cross currency
3171 * profile enabled or we are dealing with a fixed rate
3172 * currency
3173 ************************************************************/
3174
3175 l_is_fixed_rate := gl_currency_api.is_fixed_rate(
3176 l_currency_code, /*receipt currency */
3177 l_invoice_currency_code5, /* inv currency */
3178 nvl(l_receipt_date,sysdate));
3179
3180 debug1('is this a fixed rate = ' || l_is_fixed_rate);
3181 debug1('is cross curr enabled?? ' || l_enable_cross_currency);
3182
3183 IF ( (l_is_fixed_rate = 'Y') or
3184 (l_enable_cross_currency = 'Y')) THEN
3185 /* we have to make sure that all fields are populated */
3186
3187 IF ( (l_amount_applied_from5 IS NULL) or
3188 (l_amount_applied5 IS NULL) or
3189 (l_trans_to_receipt_rate5 IS NULL) ) THEN
3190
3191 /* we need to check the rate 1st. If both amounts columns are
3192 populated, then we calculate the rate. If one amount is
3193 missing and the rate is null, then we try to get the rate from
3194 GL based on the profile option */
3195
3196 IF ( l_trans_to_receipt_rate5 is NULL) THEN
3197 debug1('trans_to_receipt_rate5 is null');
3198 /* if neither amount is null then we calculate the rate */
3199
3200 debug1('amount applied = ' || to_char(l_amount_applied5));
3201 debug1('amount applied from = ' || to_char(l_amount_applied_from5));
3202 IF ( (l_amount_applied5 IS NOT NULL) and
3203 (l_amount_applied_from5 IS NOT NULL)) Then
3204
3205 /********************************************
3209 **********************************************/
3206 * if we have a fixed rate, we need to get the
3207 * rate from GL and verify the validity of the
3208 * 2 amount columns with the rate that we get
3210 IF (l_is_fixed_rate = 'Y') THEN
3211 /* get the fixed rate from GL */
3212 l_trans_to_receipt_rate5 :=
3213 gl_currency_api.get_rate(
3214 l_invoice_currency_code5,
3215 l_currency_code,
3216 l_receipt_date);
3217
3218 /*************************************************
3219 * if all fields are populated, we need to
3220 * check to make sure that they are logically
3221 * correct.
3225 l_trans_to_receipt_rate5,
3222 ************************************************/
3223 calc_amt_applied_fmt(l_invoice_currency_code5,
3224 l_amount_applied_from5,
3226 l_temp_amt_applied,
3227 'N');
3228
3229 calc_amt_applied_from_fmt(l_currency_code,
3230 l_amount_applied5,
3231 l_trans_to_receipt_rate5,
3232 l_temp_amt_applied_from,
3233 'N');
3234
3235 ar_cc_lockbox.calc_cross_rate(
3236 l_amount_applied5,
3237 l_amount_applied_from5,
3238 l_invoice_currency_code5,
3239 l_currency_code,
3240 l_temp_trans_to_receipt_rate);
3241 IF ( (l_temp_amt_applied = l_amount_applied5) OR
3242 (l_temp_amt_applied_from =
3243 l_amount_applied_from5) OR
3244 (l_temp_trans_to_receipt_rate =
3245 l_trans_to_receipt_rate5)) THEN
3246
3247 /********************************************
3248 * since one or more of the conditions are
3249 * true then we assume that everything is
3250 * fine and we can write the rate to the
3251 * database
3252 *********************************************/
3253 debug1('validation passed ' );
3254 UPDATE ar_payments_interface
3255 SET trans_to_receipt_rate5 =
3256 l_trans_to_receipt_rate5
3257 WHERE rowid = l_rowid;
3258 ELSE
3259 UPDATE AR_PAYMENTS_INTERFACE
3260 SET invoice5_status =
3261 'AR_PLB_CC_INVALID_VALUE'
3262 WHERE rowid = l_rowid;
3263 END IF;
3264
3265 ELSE
3266 /* calculate the least rate that would convert
3267 the items */
3268 ar_cc_lockbox.calc_cross_rate(
3269 l_amount_applied5,
3270 l_amount_applied_from5,
3271 l_invoice_currency_code5,
3272 l_currency_code,
3273 l_trans_to_receipt_rate5);
3274
3275 /* once the rate has been calculated, we need
3276 to write it to the table */
3277 UPDATE ar_payments_interface
3278 SET trans_to_receipt_rate5 =
3279 l_trans_to_receipt_rate5
3280 WHERE rowid = l_rowid;
3281 END IF;
3282
3283 ELSE
3284 /* need to derive the rate if possible*/
3285 debug1( 'need to derive rate ');
3286 IF (p_default_exchange_rate_type IS NOT NULL or
3287 l_is_fixed_rate = 'Y' ) THEN
3288 l_trans_to_receipt_rate5 :=
3289 gl_currency_api.get_rate_sql(
3290 l_invoice_currency_code5,
3291 l_currency_code,
3292 l_receipt_date,
3293 p_default_exchange_rate_type);
3294 debug1('calculated rate = ' || to_char(l_trans_to_receipt_rate5));
3295 /* if there is no rate in GL, there is nothing
3296 more we can do */
3297 IF (l_trans_to_receipt_rate5 < 0 ) THEN
3298
3299 UPDATE AR_PAYMENTS_INTERFACE
3300 SET invoice5_status = 'AR_PLB_NO_EXCHANGE_RATE'
3301 WHERE rowid = l_rowid;
3302
3303 ELSE
3304 /* once the rate has been calculated, we need
3305 to write it to the table */
3306
3307 debug1('writing rate to database ' || to_char(l_trans_to_receipt_rate5));
3308
3309 UPDATE ar_payments_interface
3310 SET trans_to_receipt_rate5 =
3311 l_trans_to_receipt_rate5
3312 WHERE rowid = l_rowid;
3313
3314 /* finish the calculation because we have a rate*/
3315
3316 IF (l_amount_applied_from5 IS NULL) THEN
3317 calc_amt_applied_from_fmt(l_currency_code,
3318 l_amount_applied5,
3319 l_trans_to_receipt_rate5,
3320 l_amount_applied_from5,
3321 l_format_amount_applied_from5);
3325 where rowid = l_rowid;
3322 update ar_payments_interface
3323 set amount_applied_from5 =
3324 l_amount_applied_from5
3326
3327 ELSE
3328 /* calculate amount applied and save to db */
3329 calc_amt_applied_fmt(l_invoice_currency_code5,
3330 l_amount_applied_from5,
3331 l_trans_to_receipt_rate5,
3332 l_amount_applied5,
3333 l_format_amount5);
3334 debug1('calculated amt applied = ' || to_char(l_amount_applied5));
3335 /* to deal with rounding errors that happen
3336 with fixed rate currencies, we need to
3337 take the amount_due_remaining of the trx
3338 convert it to the receipt currency and
3339 compare that value with the
3340 amount_applied_from original value
3341
3342 ADDED for BUG 883345 */
3343
3344 IF (l_is_fixed_rate = 'Y') THEN
3345
3346 calc_amt_applied_from_fmt(l_currency_code,
3347 trx_amt_due_rem5,
3348 l_trans_to_receipt_rate5,
3349 amt_applied_from_tc,
3350 'N');
3351
3352 IF (amt_applied_from_tc =
3353 l_amount_applied_from5 ) THEN
3354 IF (l_format_amount5 = 'Y') THEN
3355 fnd_currency.Get_Info(
3356 l_invoice_currency_code5,
3357 l_precision,
3358 l_extended_precision,
3359 l_mau);
3360
3361 l_amount_applied5 :=
3362 trx_amt_due_rem5 * (10**l_precision);
3363 ELSE
3364 l_amount_applied5 := trx_amt_due_rem5 ;
3365 END IF;
3366 END IF;
3367 END IF;
3368
3369 update ar_payments_interface
3370 set amount_applied5 =
3371 l_amount_applied5
3372 where rowid = l_rowid;
3373
3374 END IF; /* if amount_applied_From5 is null */
3375 END IF; /* if rate is null after it is calc */
3376 ELSE /* Bug 1519765 */
3377 UPDATE AR_PAYMENTS_INTERFACE
3378 SET invoice5_status = 'AR_PLB_NO_EXCHANGE_RATE'
3379 WHERE rowid = l_rowid;
3380 END IF; /* if derive profile is not null */
3381 END IF;
3382
3383 /* we know that trans_to_receipt_rate5 is not null,
3384 therefore, one of the amount values must be null and
3385 we have to calculate it */
3386 ELSE
3387 /* is amount_applied_From null?? */
3388 IF (l_amount_applied_from5 IS NULL) THEN
3389 calc_amt_applied_from_fmt(l_currency_code,
3390 l_amount_applied5,
3391 l_trans_to_receipt_rate5,
3392 l_amount_applied_from5,
3393 l_format_amount_applied_from5);
3394 update ar_payments_interface
3395 set amount_applied_from5 =
3396 l_amount_applied_from5
3397 where rowid = l_rowid;
3398
3399 ELSE
3400 calc_amt_applied_fmt(l_invoice_currency_code5,
3401 l_amount_applied_from5,
3402 l_trans_to_receipt_rate5,
3403 l_amount_applied5,
3404 l_format_amount5);
3405
3406 /* to deal with rounding errors that happen
3407 with fixed rate currencies, we need to
3408 take the amount_due_remaining of the trx
3409 convert it to the receipt currency and
3410 compare that value with the
3411 amount_applied_from original value
3412
3413 ADDED for BUG 883345 */
3414
3418 trx_amt_due_rem5,
3415 IF (l_is_fixed_rate = 'Y') THEN
3416
3417 calc_amt_applied_from_fmt(l_currency_code,
3419 l_trans_to_receipt_rate5,
3420 amt_applied_from_tc,
3421 'N');
3422
3423 IF (amt_applied_from_tc =
3424 l_amount_applied_from5 ) THEN
3425 IF (l_format_amount5 = 'Y') THEN
3426 fnd_currency.Get_Info(
3427 l_invoice_currency_code5,
3428 l_precision,
3429 l_extended_precision,
3430 l_mau);
3431
3432 l_amount_applied5 :=
3433 trx_amt_due_rem5 * (10**l_precision);
3434 ELSE
3435 l_amount_applied5 := trx_amt_due_rem5 ;
3436 END IF;
3437 END IF;
3438 END IF;
3439
3440 UPDATE AR_PAYMENTS_INTERFACE
3441 SET amount_applied5 =
3442 l_amount_applied5
3443 WHERE rowid = l_rowid;
3444
3445 END IF;
3446 END IF; /* trans to receipt_rate 5 is null */
3447 ELSE
3448 /*************************************************
3449 * if all fields are populated, we need to
3453
3450 * check to make sure that they are logically
3451 * correct.
3452 ***************************************************/
3454 calc_amt_applied_fmt(l_invoice_currency_code5,
3455 l_amount_applied_from5,
3456 l_trans_to_receipt_rate5,
3457 l_temp_amt_applied,
3458 'N');
3459
3460 calc_amt_applied_from_fmt(l_currency_code,
3461 l_amount_applied5,
3462 l_trans_to_receipt_rate5,
3463 l_temp_amt_applied_from,
3464 'N');
3465
3466 ar_cc_lockbox.calc_cross_rate(
3467 l_amount_applied5,
3468 l_amount_applied_from5,
3469 l_invoice_currency_code5,
3470 l_currency_code,
3471 l_temp_trans_to_receipt_rate);
3472 IF ( (l_temp_amt_applied = l_amount_applied5) OR
3473 (l_temp_amt_applied_from =
3474 l_amount_applied_from5) OR
3475 (l_temp_trans_to_receipt_rate =
3476 l_trans_to_receipt_rate5)) THEN
3477
3478 /* since one or more of the conditions are true
3479 then we assume that everything is fine. */
3480 debug1('validation passed ' );
3481 ELSE
3482 UPDATE AR_PAYMENTS_INTERFACE
3483 SET invoice5_status = 'AR_PLB_CC_INVALID_VALUE'
3484 WHERE rowid = l_rowid;
3485 END IF;
3486
3487 END IF; /* if one of the 3 items is NULL */
3488 ELSE
3489 /*****************************************************
3490 currencies do not match, they are not fixed rate and
3491 cross currency enabled profile is not on.
3492 then set the status to be a currency conflict between
3493 the invoice and receipt currencies
3494 ***************************************************/
3495 UPDATE AR_PAYMENTS_INTERFACE
3496 SET invoice5_status = 'AR_PLB_CURR_CONFLICT'
3497 WHERE rowid = l_rowid;
3498 END IF;
3499 ELSE /* Bug 2066392. Single currency */
3500 IF l_amount_applied_from5 is not null THEN
3501 IF l_amount_applied5 is not null THEN
3502 IF l_amount_applied_from5 <> l_amount_applied5 THEN
3503 UPDATE AR_PAYMENTS_INTERFACE
3504 SET invoice5_status = 'AR_PLB_CC_INVALID_VALUE'
3505 WHERE rowid = l_rowid;
3506 END IF;
3507 ELSE
3508 IF l_format_amount5 = 'Y' THEN
3512 l_mau);
3509 fnd_currency.Get_Info(l_invoice_currency_code5,
3510 l_precision,
3511 l_extended_precision,
3513 l_unformat_amount := l_amount_applied_from5 * power(10, l_precision);
3514 ELSE
3515 l_unformat_amount := l_amount_applied_from5;
3516 END IF;
3517 UPDATE AR_PAYMENTS_INTERFACE
3518 SET amount_applied5 = l_unformat_amount
3519 WHERE rowid = l_rowid;
3520 END IF;
3521 END IF;
3522 END IF;
3523 END IF;
3524 END IF; /* if matching number 5 is not null */
3525 <<END_5>>
3526
3527 /**************** need to check matching number 6 *************/
3528
3529 /* checking 6th trx_number */
3530 debug1('invoice6 is not null = ' || l_matching_number6);
3531 IF (l_matching_number6 is not NULL) THEN
3532
3533 /* added trx_amt_due_remX for bug 883345 */
3534 /* Changed the where clause and added the exception for bug 1052313
3535 and 1097549 */
3536 BEGIN
3537 SELECT sum(count(distinct ps.customer_trx_id))
3538 INTO l_tot_trx6
3539 FROM ar_payment_schedules ps
3540 WHERE ps.trx_number = l_matching_number6
3541 AND ps.trx_date = l_resolved_matching6_date /* Bug fix 2926664 */
3542 AND (EXISTS
3543 (
3544 select 'Exists from dual' from dual
3545 where l_customer_id = ps.customer_id
3546 union
3547 select 'Exists from hz_cust_acct_relate'
3548 from hz_cust_acct_relate rel
3549 where rel.cust_account_id = l_customer_id
3550 and related_cust_account_id = ps.customer_id
3551 and rel.status = 'A'
3552 and rel.bill_to_flag = 'Y'
3553 union
3554 select 'Exists ar_paying_relationships_v'
3555 from ar_paying_relationships_v rel,
3556 hz_cust_accounts acc
3557 where rel.party_id = acc.party_id
3558 and acc.cust_account_id = l_customer_id
3559 and rel.related_cust_account_id = ps.customer_id
3560 and l_receipt_date BETWEEN effective_start_date
3561 AND effective_end_date
3562 )
3563 or
3564 l_pay_unrelated_invoices = 'Y'
3565 )
3566 GROUP BY ps.customer_trx_id
3567 HAVING sum(ps.amount_due_remaining) <> 0;
3568
3569 IF (l_tot_trx6 > 1) then
3570 update ar_payments_interface pi
3571 set invoice6_status = 'AR_PLB_DUP_INV'
3572 where rowid = l_rowid;
3573 goto END_6;
3574 ELSE
3575 SELECT invoice_currency_code,
3576 amount_due_remaining
3577 INTO ps_currency_code6,
3578 trx_amt_due_rem6
3579 FROM ar_payment_schedules ps,
3580 ra_cust_trx_types tt
3581 WHERE ps.trx_number = l_matching_number6
3582 AND ps.status = decode(tt.allow_overapplication_flag,
3583 'N', 'OP',
3584 ps.status)
3585 AND ps.class NOT IN ('PMT','GUAR')
3586 AND ps.payment_schedule_id =
3590 where ps.trx_number = l_matching_number6
3587 (select min(ps.payment_schedule_id)
3588 from ar_payment_schedules ps,
3589 ra_cust_trx_types tt
3594 select 'Exists from dual' from dual
3591 and ps.trx_date = l_resolved_matching6_date /* Bug fix 2926664 */
3592 and (EXISTS
3593 (
3595 where l_customer_id = ps.customer_id
3596 union
3597 select 'Exists from hz_cust_acct_relate'
3598 from hz_cust_acct_relate rel
3599 where rel.cust_account_id = l_customer_id
3600 and related_cust_account_id = ps.customer_id
3601 and rel.status = 'A'
3602 and rel.bill_to_flag = 'Y'
3603 union
3604 select 'Exists ar_paying_relationships_v'
3605 from ar_paying_relationships_v rel,
3606 hz_cust_accounts acc
3607 where rel.party_id = acc.party_id
3608 and acc.cust_account_id = l_customer_id
3609 and rel.related_cust_account_id = ps.customer_id
3610 and l_receipt_date BETWEEN effective_start_date
3611 AND effective_end_date
3612 )
3613 or
3614 l_pay_unrelated_invoices = 'Y'
3615 )
3616 and ps.cust_trx_type_id = tt.cust_trx_type_id
3617 and ps.class NOT IN ('PMT','GUAR')
3618 and ps.status=decode(tt.allow_overapplication_flag,
3619 'N', 'OP',
3620 ps.status))
3621 and (EXISTS
3622 (
3623 select 'Exists from dual' from dual
3624 where l_customer_id = ps.customer_id
3625 union
3626 select 'Exists from hz_cust_acct_relate'
3627 from hz_cust_acct_relate rel
3628 where rel.cust_account_id = l_customer_id
3629 and related_cust_account_id = ps.customer_id
3630 and rel.status = 'A'
3631 and rel.bill_to_flag = 'Y'
3632 union
3633 select 'Exists ar_paying_relationships_v'
3634 from ar_paying_relationships_v rel,
3635 hz_cust_accounts acc
3636 where rel.party_id = acc.party_id
3637 and acc.cust_account_id = l_customer_id
3638 and rel.related_cust_account_id = ps.customer_id
3639 and l_receipt_date BETWEEN effective_start_date
3640 AND effective_end_date
3641 )
3642 or
3643 l_pay_unrelated_invoices = 'Y'
3644 )
3645 AND ps.cust_trx_type_id = tt.cust_trx_type_id;
3646 END IF;
3647 EXCEPTION
3648 WHEN NO_DATA_FOUND THEN
3649 goto END_6;
3650 END;
3651
3652 debug1('currency code6 of invoice from the ps = ' || ps_currency_code6);
3653 /********************************************************
3654 * if transmission has null currency code use the one
3655 * from Payment Schedules
3656 *********************************************************/
3657 IF (l_invoice_currency_code6 is NULL ) THEN
3658 debug1('currency code is null.. setting = ' || ps_currency_code6);
3659 l_invoice_currency_code6 := ps_currency_code6;
3660
3661 /* update ar_payment_interface to have the invoice currency_code */
3662 UPDATE ar_payments_interface
3663 SET invoice_currency_code6 = l_invoice_currency_code6
3664 WHERE rowid = l_rowid;
3665 END IF; /* end if invoice currency code was null */
3666
3667 /****************************************************************
3668 * check to see if the currency code matches or is was not included
3669 * in the transmission
3670 ****************************************************************/
3671 debug1('l_invoice_currency_code6 = ' || l_invoice_currency_code6);
3672 debug1('ps_currency_code = ' || ps_currency_code6);
3673
3674 IF (l_invoice_currency_code6 <> ps_currency_code6) then
3675 debug1('currency code give does not match payment schedules..');
3676 UPDATE AR_PAYMENTS_INTERFACE
3680 /* Bug:1513671 we know the invoice currency code so we can now format the
3677 SET invoice6_status = 'AR_PLB_CURRENCY_BAD'
3678 WHERE rowid = l_rowid;
3679 ELSE
3681 amount applied if we need to */
3682
3683 IF (p_format_amount6 = 'Y') THEN
3684 fnd_currency.Get_Info(l_invoice_currency_code6,
3685 l_precision,
3686 l_extended_precision,
3687 l_mau);
3688 l_amount_applied6 := round(l_amount_applied6 / power(10, l_precision),
3689 l_precision);
3690 END IF;
3691
3692 /*************************************************************
3693 * if the currency code of the transaction does not equal the
3694 * currency code of the receipt, then check for cross currency
3695 * or Euro case
3696 **************************************************************/
3697 IF ( l_invoice_currency_code6 <> l_currency_code) THEN
3698 debug1('currency code of receipt does not match currency code of inv');
3699
3700 /***********************************************************
3701 * we need to check to see if we have cross currency
3702 * profile enabled or we are dealing with a fixed rate
3703 * currency
3704 ************************************************************/
3705
3706 l_is_fixed_rate := gl_currency_api.is_fixed_rate(
3707 l_currency_code, /*receipt currency */
3708 l_invoice_currency_code6, /* inv currency */
3709 nvl(l_receipt_date,sysdate));
3710
3711 debug1('is this a fixed rate = ' || l_is_fixed_rate);
3712 debug1('is cross curr enabled?? ' || l_enable_cross_currency);
3713
3714 IF ( (l_is_fixed_rate = 'Y') or
3715 (l_enable_cross_currency = 'Y')) THEN
3716 /* we have to make sure that all fields are populated */
3717
3718 IF ( (l_amount_applied_from6 IS NULL) or
3719 (l_amount_applied6 IS NULL) or
3723 populated, then we calculate the rate. If one amount is
3720 (l_trans_to_receipt_rate6 IS NULL) ) THEN
3721
3722 /* we need to check the rate 1st. If both amounts columns are
3724 missing and the rate is null, then we try to get the rate from
3725 GL based on the profile option */
3726
3727 IF ( l_trans_to_receipt_rate6 is NULL) THEN
3728 debug1('trans_to_receipt_rate6 is null');
3729 /* if neither amount is null then we calculate the rate */
3730
3731 debug1('amount applied = ' || to_char(l_amount_applied6));
3735
3732 debug1('amount applied from = ' || to_char(l_amount_applied_from6));
3733 IF ( (l_amount_applied6 IS NOT NULL) and
3734 (l_amount_applied_from6 IS NOT NULL)) Then
3736 /********************************************
3737 * if we have a fixed rate, we need to get the
3738 * rate from GL and verify the validity of the
3739 * 2 amount columns with the rate that we get
3740 **********************************************/
3741 IF (l_is_fixed_rate = 'Y') THEN
3742 /* get the fixed rate from GL */
3743 l_trans_to_receipt_rate6 :=
3744 gl_currency_api.get_rate(
3745 l_invoice_currency_code6,
3746 l_currency_code,
3747 l_receipt_date);
3748
3749 /*************************************************
3750 * if all fields are populated, we need to
3751 * check to make sure that they are logically
3752 * correct.
3753 ************************************************/
3754 calc_amt_applied_fmt(l_invoice_currency_code6,
3755 l_amount_applied_from6,
3756 l_trans_to_receipt_rate6,
3757 l_temp_amt_applied,
3758 'N');
3759
3760 calc_amt_applied_from_fmt(l_currency_code,
3761 l_amount_applied6,
3762 l_trans_to_receipt_rate6,
3763 l_temp_amt_applied_from,
3764 'N');
3765
3766 ar_cc_lockbox.calc_cross_rate(
3767 l_amount_applied6,
3768 l_amount_applied_from6,
3769 l_invoice_currency_code6,
3770 l_currency_code,
3771 l_temp_trans_to_receipt_rate);
3772 IF ( (l_temp_amt_applied = l_amount_applied6) OR
3773 (l_temp_amt_applied_from =
3774 l_amount_applied_from6) OR
3775 (l_temp_trans_to_receipt_rate =
3776 l_trans_to_receipt_rate6)) THEN
3777
3778 /********************************************
3779 * since one or more of the conditions are
3780 * true then we assume that everything is
3781 * fine and we can write the rate to the
3782 * database
3783 *********************************************/
3784 debug1('validation passed ' );
3785 UPDATE ar_payments_interface
3786 SET trans_to_receipt_rate6 =
3787 l_trans_to_receipt_rate6
3788 WHERE rowid = l_rowid;
3789 ELSE
3790 UPDATE AR_PAYMENTS_INTERFACE
3791 SET invoice6_status =
3792 'AR_PLB_CC_INVALID_VALUE'
3793 WHERE rowid = l_rowid;
3794 END IF;
3795
3796 ELSE
3797 /* calculate the least rate that would convert
3798 the items */
3799 ar_cc_lockbox.calc_cross_rate(
3800 l_amount_applied6,
3801 l_amount_applied_from6,
3802 l_invoice_currency_code6,
3803 l_currency_code,
3804 l_trans_to_receipt_rate6);
3805
3806 /* once the rate has been calculated, we need
3807 to write it to the table */
3808 UPDATE ar_payments_interface
3809 SET trans_to_receipt_rate6 =
3810 l_trans_to_receipt_rate6
3811 WHERE rowid = l_rowid;
3812 END IF;
3813
3814 ELSE
3815 /* need to derive the rate if possible*/
3816 debug1( 'need to derive rate ');
3817 IF (p_default_exchange_rate_type IS NOT NULL or
3818 l_is_fixed_rate = 'Y' ) THEN
3819 l_trans_to_receipt_rate6 :=
3820 gl_currency_api.get_rate_sql(
3821 l_invoice_currency_code6,
3822 l_currency_code,
3823 l_receipt_date,
3824 p_default_exchange_rate_type);
3825 debug1('calculated rate = ' || to_char(l_trans_to_receipt_rate6));
3826 /* if there is no rate in GL, there is nothing
3827 more we can do */
3831 SET invoice6_status = 'AR_PLB_NO_EXCHANGE_RATE'
3828 IF (l_trans_to_receipt_rate6 < 0 ) THEN
3829
3830 UPDATE AR_PAYMENTS_INTERFACE
3832 WHERE rowid = l_rowid;
3833
3834 ELSE
3835 /* once the rate has been calculated, we need
3839
3836 to write it to the table */
3837
3838 debug1('writing rate to database ' || to_char(l_trans_to_receipt_rate6));
3840 UPDATE ar_payments_interface
3841 SET trans_to_receipt_rate6 =
3842 l_trans_to_receipt_rate6
3843 WHERE rowid = l_rowid;
3844
3845 /* finish the calculation because we have a rate*/
3846
3847 IF (l_amount_applied_from6 IS NULL) THEN
3848 calc_amt_applied_from_fmt(l_currency_code,
3849 l_amount_applied6,
3850 l_trans_to_receipt_rate6,
3851 l_amount_applied_from6,
3852 l_format_amount_applied_from6);
3853 update ar_payments_interface
3854 set amount_applied_from6 =
3855 l_amount_applied_from6
3856 where rowid = l_rowid;
3857
3858 ELSE
3859 /* calculate amount applied and save to db */
3860 calc_amt_applied_fmt(l_invoice_currency_code6,
3861 l_amount_applied_from6,
3862 l_trans_to_receipt_rate6,
3863 l_amount_applied6,
3864 l_format_amount6);
3865 debug1('calculated amt applied = ' || to_char(l_amount_applied6));
3866 /* to deal with rounding errors that happen
3867 with fixed rate currencies, we need to
3868 take the amount_due_remaining of the trx
3869 convert it to the receipt currency and
3870 compare that value with the
3871 amount_applied_from original value
3872
3873 ADDED for BUG 883345 */
3874
3875 IF (l_is_fixed_rate = 'Y') THEN
3876
3877 calc_amt_applied_from_fmt(l_currency_code,
3878 trx_amt_due_rem6,
3879 l_trans_to_receipt_rate6,
3880 amt_applied_from_tc,
3881 'N');
3882
3883 IF (amt_applied_from_tc =
3884 l_amount_applied_from6 ) THEN
3885 IF (l_format_amount6 = 'Y') THEN
3886 fnd_currency.Get_Info(
3887 l_invoice_currency_code6,
3888 l_precision,
3889 l_extended_precision,
3890 l_mau);
3891
3892 l_amount_applied6 :=
3893 trx_amt_due_rem6 * (10**l_precision);
3894 ELSE
3895 l_amount_applied6 := trx_amt_due_rem6 ;
3896 END IF;
3897 END IF;
3898 END IF;
3899
3900 update ar_payments_interface
3901 set amount_applied6 =
3905 END IF; /* if amount_applied_From6 is null */
3902 l_amount_applied6
3903 where rowid = l_rowid;
3904
3906 END IF; /* if rate is null after it is calc */
3907 ELSE /* Bug 1519765 */
3908 UPDATE AR_PAYMENTS_INTERFACE
3909 SET invoice6_status = 'AR_PLB_NO_EXCHANGE_RATE'
3910 WHERE rowid = l_rowid;
3911 END IF; /* if derive profile is not null */
3912 END IF;
3913
3914 /* we know that trans_to_receipt_rate6 is not null,
3915 therefore, one of the amount values must be null and
3916 we have to calculate it */
3917 ELSE
3918 /* is amount_applied_From null?? */
3919 IF (l_amount_applied_from6 IS NULL) THEN
3920 calc_amt_applied_from_fmt(l_currency_code,
3921 l_amount_applied6,
3922 l_trans_to_receipt_rate6,
3923 l_amount_applied_from6,
3924 l_format_amount_applied_from6);
3925 update ar_payments_interface
3926 set amount_applied_from6 =
3927 l_amount_applied_from6
3928 where rowid = l_rowid;
3929
3930 ELSE
3931 calc_amt_applied_fmt(l_invoice_currency_code6,
3932 l_amount_applied_from6,
3933 l_trans_to_receipt_rate6,
3934 l_amount_applied6,
3935 l_format_amount6);
3936
3937 /* to deal with rounding errors that happen
3938 with fixed rate currencies, we need to
3939 take the amount_due_remaining of the trx
3940 convert it to the receipt currency and
3941 compare that value with the
3942 amount_applied_from original value
3943
3944 ADDED for BUG 883345 */
3945
3946 IF (l_is_fixed_rate = 'Y') THEN
3947
3948 calc_amt_applied_from_fmt(l_currency_code,
3949 trx_amt_due_rem6,
3950 l_trans_to_receipt_rate6,
3951 amt_applied_from_tc, 'N');
3952
3953 IF (amt_applied_from_tc =
3954 l_amount_applied_from6 ) THEN
3955 IF (l_format_amount6 = 'Y') THEN
3956 fnd_currency.Get_Info(
3957 l_invoice_currency_code6,
3958 l_precision,
3959 l_extended_precision,
3960 l_mau);
3961
3965 l_amount_applied6 := trx_amt_due_rem6 ;
3962 l_amount_applied6 :=
3963 trx_amt_due_rem6 * (10**l_precision);
3964 ELSE
3966 END IF;
3967 END IF;
3968 END IF;
3969
3970 UPDATE AR_PAYMENTS_INTERFACE
3971 SET amount_applied6 =
3972 l_amount_applied6
3973 WHERE rowid = l_rowid;
3974
3975 END IF;
3976 END IF; /* trans to receipt_rate 6 is null */
3977 ELSE
3978 /*************************************************
3979 * if all fields are populated, we need to
3980 * check to make sure that they are logically
3981 * correct.
3982 ***************************************************/
3983
3984 calc_amt_applied_fmt(l_invoice_currency_code6,
3985 l_amount_applied_from6,
3986 l_trans_to_receipt_rate6,
3987 l_temp_amt_applied,
3988 'N');
3989
3990 calc_amt_applied_from_fmt(l_currency_code,
3991 l_amount_applied6,
3992 l_trans_to_receipt_rate6,
3993 l_temp_amt_applied_from,
3994 'N');
3995
3999 l_invoice_currency_code6,
3996 ar_cc_lockbox.calc_cross_rate(
3997 l_amount_applied6,
3998 l_amount_applied_from6,
4000 l_currency_code,
4001 l_temp_trans_to_receipt_rate);
4002 IF ( (l_temp_amt_applied = l_amount_applied6) OR
4003 (l_temp_amt_applied_from =
4004 l_amount_applied_from6) OR
4005 (l_temp_trans_to_receipt_rate =
4006 l_trans_to_receipt_rate6)) THEN
4007
4008 /* since one or more of the conditions are true
4009 then we assume that everything is fine. */
4010 debug1('validation passed ' );
4011 ELSE
4012 UPDATE AR_PAYMENTS_INTERFACE
4013 SET invoice6_status = 'AR_PLB_CC_INVALID_VALUE'
4014 WHERE rowid = l_rowid;
4015 END IF;
4016
4017 END IF; /* if one of the 3 items is NULL */
4018 ELSE
4019 /*****************************************************
4020 currencies do not match, they are not fixed rate and
4021 cross currency enabled profile is not on.
4022 then set the status to be a currency conflict between
4023 the invoice and receipt currencies
4024 ***************************************************/
4025 UPDATE AR_PAYMENTS_INTERFACE
4026 SET invoice6_status = 'AR_PLB_CURR_CONFLICT'
4027 WHERE rowid = l_rowid;
4028 END IF;
4029 ELSE /* Bug 2066392. Single currency */
4030 IF l_amount_applied_from6 is not null THEN
4031 IF l_amount_applied6 is not null THEN
4032 IF l_amount_applied_from6 <> l_amount_applied6 THEN
4033 UPDATE AR_PAYMENTS_INTERFACE
4034 SET invoice6_status = 'AR_PLB_CC_INVALID_VALUE'
4035 WHERE rowid = l_rowid;
4036 END IF;
4037 ELSE
4038 IF l_format_amount6 = 'Y' THEN
4039 fnd_currency.Get_Info(l_invoice_currency_code6,
4040 l_precision,
4041 l_extended_precision,
4042 l_mau);
4043 l_unformat_amount := l_amount_applied_from6 * power(10, l_precision);
4044 ELSE
4045 l_unformat_amount := l_amount_applied_from6;
4046 END IF;
4047 UPDATE AR_PAYMENTS_INTERFACE
4048 SET amount_applied6 = l_unformat_amount
4049 WHERE rowid = l_rowid;
4050 END IF;
4051 END IF;
4052 END IF;
4053 END IF;
4054 END IF; /* if matching number 6 is not null */
4055 <<END_6>>
4056
4057 /**************** need to check matching number 7 *************/
4058
4059 /* checking 7th trx_number */
4060 debug1('invoice7 is not null = ' || l_matching_number7);
4061 IF (l_matching_number7 is not NULL) THEN
4062
4063 /* added trx_amt_due_remX for bug 883345 */
4064 /* Changed the where clause and added the exception for bug 1052313
4065 and 1097549 */
4066 BEGIN
4067 SELECT sum(count(distinct ps.customer_trx_id))
4068 INTO l_tot_trx7
4069 FROM ar_payment_schedules ps
4070 WHERE ps.trx_number = l_matching_number7
4071 AND ps.trx_date = l_resolved_matching7_date /* Bug fix 2926664 */
4072 AND (EXISTS
4073 (
4074 select 'Exists from dual' from dual
4075 where l_customer_id = ps.customer_id
4076 union
4077 select 'Exists from hz_cust_acct_relate'
4078 from hz_cust_acct_relate rel
4079 where rel.cust_account_id = l_customer_id
4080 and related_cust_account_id = ps.customer_id
4081 and rel.status = 'A'
4082 and rel.bill_to_flag = 'Y'
4083 union
4084 select 'Exists ar_paying_relationships_v'
4085 from ar_paying_relationships_v rel,
4086 hz_cust_accounts acc
4087 where rel.party_id = acc.party_id
4088 and acc.cust_account_id = l_customer_id
4089 and rel.related_cust_account_id = ps.customer_id
4090 and l_receipt_date BETWEEN effective_start_date
4091 AND effective_end_date
4092 )
4093 or
4094 l_pay_unrelated_invoices = 'Y'
4095 )
4099 IF (l_tot_trx7 > 1) then
4096 GROUP BY ps.customer_trx_id
4097 HAVING sum(ps.amount_due_remaining) <> 0;
4098
4100 update ar_payments_interface pi
4101 set invoice7_status = 'AR_PLB_DUP_INV'
4102 where rowid = l_rowid;
4103 goto END_7;
4104 ELSE
4105 SELECT invoice_currency_code,
4106 amount_due_remaining
4107 INTO ps_currency_code7,
4111 WHERE ps.trx_number = l_matching_number7
4108 trx_amt_due_rem7
4109 FROM ar_payment_schedules ps,
4110 ra_cust_trx_types tt
4112 AND ps.status = decode(tt.allow_overapplication_flag,
4113 'N', 'OP',
4114 ps.status)
4115 AND ps.class NOT IN ('PMT','GUAR')
4116 AND ps.payment_schedule_id =
4117 (select min(ps.payment_schedule_id)
4118 from ar_payment_schedules ps,
4119 ra_cust_trx_types tt
4120 where ps.trx_number = l_matching_number7
4121 and ps.trx_date = l_resolved_matching7_date /* Bug fix 2926664 */
4122 and (EXISTS
4123 (
4124 select 'Exists from dual' from dual
4125 where l_customer_id = ps.customer_id
4126 union
4127 select 'Exists from hz_cust_acct_relate'
4128 from hz_cust_acct_relate rel
4129 where rel.cust_account_id = l_customer_id
4130 and related_cust_account_id = ps.customer_id
4131 and rel.status = 'A'
4132 and rel.bill_to_flag = 'Y'
4133 union
4134 select 'Exists ar_paying_relationships_v'
4135 from ar_paying_relationships_v rel,
4136 hz_cust_accounts acc
4137 where rel.party_id = acc.party_id
4138 and acc.cust_account_id = l_customer_id
4139 and rel.related_cust_account_id = ps.customer_id
4140 and l_receipt_date BETWEEN effective_start_date
4141 AND effective_end_date
4142 )
4143 or
4144 l_pay_unrelated_invoices = 'Y'
4145 )
4146 and ps.cust_trx_type_id = tt.cust_trx_type_id
4147 and ps.class NOT IN ('PMT','GUAR')
4148 and ps.status=decode(tt.allow_overapplication_flag,
4149 'N' , 'OP',
4150 ps.status))
4151 and (EXISTS
4152 (
4153 select 'Exists from dual' from dual
4154 where l_customer_id = ps.customer_id
4155 union
4156 select 'Exists from hz_cust_acct_relate'
4157 from hz_cust_acct_relate rel
4158 where rel.cust_account_id = l_customer_id
4159 and related_cust_account_id = ps.customer_id
4160 and rel.status = 'A'
4161 and rel.bill_to_flag = 'Y'
4162 union
4163 select 'Exists ar_paying_relationships_v'
4164 from ar_paying_relationships_v rel,
4165 hz_cust_accounts acc
4166 where rel.party_id = acc.party_id
4167 and acc.cust_account_id = l_customer_id
4168 and rel.related_cust_account_id = ps.customer_id
4169 and l_receipt_date BETWEEN effective_start_date
4170 AND effective_end_date
4171 )
4172 or
4173 l_pay_unrelated_invoices = 'Y'
4174 )
4175 AND ps.cust_trx_type_id = tt.cust_trx_type_id;
4176 END IF;
4177 EXCEPTION
4178 WHEN NO_DATA_FOUND THEN
4179 goto END_7;
4180 END;
4181
4182 debug1('currency code7 of invoice from the ps = ' || ps_currency_code7);
4183 /********************************************************
4184 * if transmission has null currency code use the one
4185 * from Payment Schedules
4186 *********************************************************/
4187 IF (l_invoice_currency_code7 is NULL ) THEN
4191 /* update ar_payment_interface to have the invoice currency_code */
4188 debug1('currency code is null.. setting = ' || ps_currency_code7);
4189 l_invoice_currency_code7 := ps_currency_code7;
4190
4192 UPDATE ar_payments_interface
4193 SET invoice_currency_code7 = l_invoice_currency_code7
4194 WHERE rowid = l_rowid;
4195 END IF; /* end if invoice currency code was null */
4196
4197 /****************************************************************
4198 * check to see if the currency code matches or is was not included
4199 * in the transmission
4200 ****************************************************************/
4201 debug1('l_invoice_currency_code7 = ' || l_invoice_currency_code7);
4202 debug1('ps_currency_code = ' || ps_currency_code7);
4203
4204 IF (l_invoice_currency_code7 <> ps_currency_code7) then
4205 debug1('currency code give does not match payment schedules..');
4206 UPDATE AR_PAYMENTS_INTERFACE
4207 SET invoice7_status = 'AR_PLB_CURRENCY_BAD'
4208 WHERE rowid = l_rowid;
4209 ELSE
4210 /* Bug:1513671 we know the invoice currency code so we can now format the
4211 amount applied if we need to */
4212
4213 IF (p_format_amount7 = 'Y') THEN
4214 fnd_currency.Get_Info(l_invoice_currency_code7,
4215 l_precision,
4216 l_extended_precision,
4217 l_mau);
4218 l_amount_applied7 := round(l_amount_applied7 / power(10, l_precision),
4219 l_precision);
4220 END IF;
4221
4222 /*************************************************************
4223 * if the currency code of the transaction does not equal the
4224 * currency code of the receipt, then check for cross currency
4225 * or Euro case
4226 **************************************************************/
4227 IF ( l_invoice_currency_code7 <> l_currency_code) THEN
4228 debug1('currency code of receipt does not match currency code of inv');
4229
4230 /***********************************************************
4231 * we need to check to see if we have cross currency
4232 * profile enabled or we are dealing with a fixed rate
4233 * currency
4237 l_currency_code, /*receipt currency */
4234 ************************************************************/
4235
4236 l_is_fixed_rate := gl_currency_api.is_fixed_rate(
4238 l_invoice_currency_code7, /* inv currency */
4242 debug1('is cross curr enabled?? ' || l_enable_cross_currency);
4239 nvl(l_receipt_date,sysdate));
4240
4241 debug1('is this a fixed rate = ' || l_is_fixed_rate);
4243
4244 IF ( (l_is_fixed_rate = 'Y') or
4245 (l_enable_cross_currency = 'Y')) THEN
4246 /* we have to make sure that all fields are populated */
4247
4248 IF ( (l_amount_applied_from7 IS NULL) or
4249 (l_amount_applied7 IS NULL) or
4250 (l_trans_to_receipt_rate7 IS NULL) ) THEN
4251
4252 /* we need to check the rate 1st. If both amounts columns are
4253 populated, then we calculate the rate. If one amount is
4254 missing and the rate is null, then we try to get the rate from
4255 GL based on the profile option */
4256
4257 IF ( l_trans_to_receipt_rate7 is NULL) THEN
4258 debug1('trans_to_receipt_rate7 is null');
4259 /* if neither amount is null then we calculate the rate */
4260
4261 debug1('amount applied = ' || to_char(l_amount_applied7));
4262 debug1('amount applied from = ' || to_char(l_amount_applied_from7));
4263 IF ( (l_amount_applied7 IS NOT NULL) and
4264 (l_amount_applied_from7 IS NOT NULL)) Then
4265
4266 /********************************************
4267 * if we have a fixed rate, we need to get the
4268 * rate from GL and verify the validity of the
4269 * 2 amount columns with the rate that we get
4270 **********************************************/
4271 IF (l_is_fixed_rate = 'Y') THEN
4272 /* get the fixed rate from GL */
4273 l_trans_to_receipt_rate7 :=
4274 gl_currency_api.get_rate(
4275 l_invoice_currency_code7,
4276 l_currency_code,
4277 l_receipt_date);
4278
4279 /*************************************************
4280 * if all fields are populated, we need to
4281 * check to make sure that they are logically
4282 * correct.
4283 ************************************************/
4284 calc_amt_applied_fmt(l_invoice_currency_code7,
4285 l_amount_applied_from7,
4286 l_trans_to_receipt_rate7,
4287 l_temp_amt_applied,
4288 'N');
4289
4290 calc_amt_applied_from_fmt(l_currency_code,
4291 l_amount_applied7,
4292 l_trans_to_receipt_rate7,
4293 l_temp_amt_applied_from,
4294 'N');
4295
4296 ar_cc_lockbox.calc_cross_rate(
4297 l_amount_applied7,
4298 l_amount_applied_from7,
4299 l_invoice_currency_code7,
4300 l_currency_code,
4301 l_temp_trans_to_receipt_rate);
4302 IF ( (l_temp_amt_applied = l_amount_applied7) OR
4303 (l_temp_amt_applied_from =
4304 l_amount_applied_from7) OR
4305 (l_temp_trans_to_receipt_rate =
4306 l_trans_to_receipt_rate7)) THEN
4307
4308 /********************************************
4309 * since one or more of the conditions are
4310 * true then we assume that everything is
4311 * fine and we can write the rate to the
4312 * database
4313 *********************************************/
4314 debug1('validation passed ' );
4315 UPDATE ar_payments_interface
4316 SET trans_to_receipt_rate7 =
4317 l_trans_to_receipt_rate7
4318 WHERE rowid = l_rowid;
4319 ELSE
4320 UPDATE AR_PAYMENTS_INTERFACE
4321 SET invoice7_status =
4322 'AR_PLB_CC_INVALID_VALUE'
4323 WHERE rowid = l_rowid;
4324 END IF;
4325
4326 ELSE
4327 /* calculate the least rate that would convert
4328 the items */
4329 ar_cc_lockbox.calc_cross_rate(
4330 l_amount_applied7,
4331 l_amount_applied_from7,
4332 l_invoice_currency_code7,
4333 l_currency_code,
4334 l_trans_to_receipt_rate7);
4335
4336 /* once the rate has been calculated, we need
4337 to write it to the table */
4338 UPDATE ar_payments_interface
4342 END IF;
4339 SET trans_to_receipt_rate7 =
4340 l_trans_to_receipt_rate7
4341 WHERE rowid = l_rowid;
4343
4344 ELSE
4345 /* need to derive the rate if possible*/
4346 debug1( 'need to derive rate ');
4347 IF (p_default_exchange_rate_type IS NOT NULL or
4348 l_is_fixed_rate = 'Y' ) THEN
4349 l_trans_to_receipt_rate7 :=
4350 gl_currency_api.get_rate_sql(
4351 l_invoice_currency_code7,
4355 debug1('calculated rate = ' || to_char(l_trans_to_receipt_rate7));
4352 l_currency_code,
4353 l_receipt_date,
4354 p_default_exchange_rate_type);
4356 /* if there is no rate in GL, there is nothing
4357 more we can do */
4358 IF (l_trans_to_receipt_rate7 < 0 ) THEN
4359
4360 UPDATE AR_PAYMENTS_INTERFACE
4361 SET invoice7_status = 'AR_PLB_NO_EXCHANGE_RATE'
4362 WHERE rowid = l_rowid;
4363
4364 ELSE
4365 /* once the rate has been calculated, we need
4366 to write it to the table */
4367
4368 debug1('writing rate to database ' || to_char(l_trans_to_receipt_rate7));
4369
4370 UPDATE ar_payments_interface
4371 SET trans_to_receipt_rate7 =
4372 l_trans_to_receipt_rate7
4373 WHERE rowid = l_rowid;
4374
4375 /* finish the calculation because we have a rate*/
4376
4377 IF (l_amount_applied_from7 IS NULL) THEN
4378 calc_amt_applied_from_fmt(l_currency_code,
4379 l_amount_applied7,
4380 l_trans_to_receipt_rate7,
4381 l_amount_applied_from7,
4382 l_format_amount_applied_from7);
4383 update ar_payments_interface
4384 set amount_applied_from7 =
4385 l_amount_applied_from7
4386 where rowid = l_rowid;
4387
4388 ELSE
4389 /* calculate amount applied and save to db */
4390 calc_amt_applied_fmt(l_invoice_currency_code7,
4391 l_amount_applied_from7,
4392 l_trans_to_receipt_rate7,
4393 l_amount_applied7,
4394 l_format_amount7);
4395 debug1('calculated amt applied = ' || to_char(l_amount_applied7));
4396 /* to deal with rounding errors that happen
4397 with fixed rate currencies, we need to
4398 take the amount_due_remaining of the trx
4399 convert it to the receipt currency and
4400 compare that value with the
4401 amount_applied_from original value
4402
4403 ADDED for BUG 883345 */
4404
4405 IF (l_is_fixed_rate = 'Y') THEN
4406
4407 calc_amt_applied_from_fmt(l_currency_code,
4408 trx_amt_due_rem7,
4409 l_trans_to_receipt_rate7,
4410 amt_applied_from_tc,
4411 'N');
4412
4413 IF (amt_applied_from_tc =
4414 l_amount_applied_from7 ) THEN
4415 IF (l_format_amount7 = 'Y') THEN
4416 fnd_currency.Get_Info(
4417 l_invoice_currency_code7,
4418 l_precision,
4419 l_extended_precision,
4420 l_mau);
4421
4422 l_amount_applied7 :=
4423 trx_amt_due_rem7 * (10**l_precision);
4424 ELSE
4425 l_amount_applied7 := trx_amt_due_rem7 ;
4429
4426 END IF;
4427 END IF;
4428 END IF;
4430 update ar_payments_interface
4431 set amount_applied7 =
4432 l_amount_applied7
4433 where rowid = l_rowid;
4434
4435 END IF; /* if amount_applied_From7 is null */
4436 END IF; /* if rate is null after it is calc */
4437 ELSE /* Bug 1519765 */
4438 UPDATE AR_PAYMENTS_INTERFACE
4439 SET invoice7_status = 'AR_PLB_NO_EXCHANGE_RATE'
4440 WHERE rowid = l_rowid;
4441 END IF; /* if derive profile is not null */
4442 END IF;
4443
4444 /* we know that trans_to_receipt_rate7 is not null,
4445 therefore, one of the amount values must be null and
4446 we have to calculate it */
4447 ELSE
4448 /* is amount_applied_From null?? */
4449 IF (l_amount_applied_from7 IS NULL) THEN
4450 calc_amt_applied_from_fmt(l_currency_code,
4451 l_amount_applied7,
4452 l_trans_to_receipt_rate7,
4453 l_amount_applied_from7,
4454 l_format_amount_applied_from7);
4455 update ar_payments_interface
4456 set amount_applied_from7 =
4457 l_amount_applied_from7
4458 where rowid = l_rowid;
4459
4460 ELSE
4461 calc_amt_applied_fmt(l_invoice_currency_code7,
4462 l_amount_applied_from7,
4463 l_trans_to_receipt_rate7,
4464 l_amount_applied7,
4465 l_format_amount7);
4466
4467 /* to deal with rounding errors that happen
4468 with fixed rate currencies, we need to
4469 take the amount_due_remaining of the trx
4470 convert it to the receipt currency and
4471 compare that value with the
4472 amount_applied_from original value
4473
4474 ADDED for BUG 883345 */
4475
4479 trx_amt_due_rem7,
4476 IF (l_is_fixed_rate = 'Y') THEN
4477
4478 calc_amt_applied_from_fmt(l_currency_code,
4480 l_trans_to_receipt_rate7,
4481 amt_applied_from_tc,
4482 'N');
4483
4484 IF (amt_applied_from_tc =
4485 l_amount_applied_from7 ) THEN
4486 IF (l_format_amount7 = 'Y') THEN
4487 fnd_currency.Get_Info(
4488 l_invoice_currency_code7,
4489 l_precision,
4490 l_extended_precision,
4491 l_mau);
4492
4493 l_amount_applied7 :=
4494 trx_amt_due_rem7 * (10**l_precision);
4495 ELSE
4496 l_amount_applied7 := trx_amt_due_rem7 ;
4497 END IF;
4498 END IF;
4499 END IF;
4500
4501 UPDATE AR_PAYMENTS_INTERFACE
4502 SET amount_applied7 =
4503 l_amount_applied7
4504 WHERE rowid = l_rowid;
4505
4506 END IF;
4507 END IF; /* trans to receipt_rate 7 is null */
4508 ELSE
4512 * correct.
4509 /*************************************************
4510 * if all fields are populated, we need to
4511 * check to make sure that they are logically
4513 ***************************************************/
4514
4515 calc_amt_applied_fmt(l_invoice_currency_code7,
4516 l_amount_applied_from7,
4517 l_trans_to_receipt_rate7,
4518 l_temp_amt_applied,
4519 'N');
4520
4521 calc_amt_applied_from_fmt(l_currency_code,
4522 l_amount_applied7,
4523 l_trans_to_receipt_rate7,
4524 l_temp_amt_applied_from,
4525 'N');
4526
4527 ar_cc_lockbox.calc_cross_rate(
4528 l_amount_applied7,
4529 l_amount_applied_from7,
4530 l_invoice_currency_code7,
4531 l_currency_code,
4532 l_temp_trans_to_receipt_rate);
4533 IF ( (l_temp_amt_applied = l_amount_applied7) OR
4534 (l_temp_amt_applied_from =
4535 l_amount_applied_from7) OR
4536 (l_temp_trans_to_receipt_rate =
4537 l_trans_to_receipt_rate7)) THEN
4538
4539 /* since one or more of the conditions are true
4540 then we assume that everything is fine. */
4541 debug1('validation passed ' );
4542 ELSE
4543 UPDATE AR_PAYMENTS_INTERFACE
4544 SET invoice7_status = 'AR_PLB_CC_INVALID_VALUE'
4545 WHERE rowid = l_rowid;
4546 END IF;
4547
4548 END IF; /* if one of the 3 items is NULL */
4549 ELSE
4550 /*****************************************************
4551 currencies do not match, they are not fixed rate and
4552 cross currency enabled profile is not on.
4553 then set the status to be a currency conflict between
4554 the invoice and receipt currencies
4555 ***************************************************/
4556 UPDATE AR_PAYMENTS_INTERFACE
4557 SET invoice7_status = 'AR_PLB_CURR_CONFLICT'
4558 WHERE rowid = l_rowid;
4559 END IF;
4560 ELSE /* Bug 2066392. Single currency */
4561 IF l_amount_applied_from7 is not null THEN
4562 IF l_amount_applied7 is not null THEN
4563 IF l_amount_applied_from7 <> l_amount_applied7 THEN
4564 UPDATE AR_PAYMENTS_INTERFACE
4565 SET invoice7_status = 'AR_PLB_CC_INVALID_VALUE'
4566 WHERE rowid = l_rowid;
4567 END IF;
4568 ELSE
4569 IF l_format_amount7 = 'Y' THEN
4570 fnd_currency.Get_Info(l_invoice_currency_code7,
4571 l_precision,
4572 l_extended_precision,
4573 l_mau);
4574 l_unformat_amount := l_amount_applied_from7 * power(10, l_precision);
4575 ELSE
4576 l_unformat_amount := l_amount_applied_from7;
4577 END IF;
4578 UPDATE AR_PAYMENTS_INTERFACE
4579 SET amount_applied7 = l_unformat_amount
4580 WHERE rowid = l_rowid;
4581 END IF;
4582 END IF;
4583 END IF;
4584 END IF;
4585 END IF; /* if matching number 7 is not null */
4586 <<END_7>>
4587
4588 /**************** need to check matching number 8 *************/
4589
4590 /* checking 8th trx_number */
4591 debug1('invoice8 is not null = ' || l_matching_number8);
4592 IF (l_matching_number8 is not NULL) THEN
4593
4594 /* added trx_amt_due_remX for bug 883345 */
4595 /* Changed the where clause and added the exception for bug 1052313
4596 and 1097549 */
4597 BEGIN
4598 SELECT sum(count(distinct ps.customer_trx_id))
4599 INTO l_tot_trx8
4600 FROM ar_payment_schedules ps
4601 WHERE ps.trx_number = l_matching_number8
4602 AND ps.trx_date = l_resolved_matching8_date /* Bug fix 2926664 */
4603 AND (EXISTS
4604 (
4605 select 'Exists from dual' from dual
4606 where l_customer_id = ps.customer_id
4607 union
4608 select 'Exists from hz_cust_acct_relate'
4609 from hz_cust_acct_relate rel
4610 where rel.cust_account_id = l_customer_id
4611 and related_cust_account_id = ps.customer_id
4612 and rel.status = 'A'
4613 and rel.bill_to_flag = 'Y'
4614 union
4615 select 'Exists ar_paying_relationships_v'
4616 from ar_paying_relationships_v rel,
4620 and rel.related_cust_account_id = ps.customer_id
4617 hz_cust_accounts acc
4618 where rel.party_id = acc.party_id
4619 and acc.cust_account_id = l_customer_id
4621 and l_receipt_date BETWEEN effective_start_date
4622 AND effective_end_date
4623 )
4624 or
4625 l_pay_unrelated_invoices = 'Y'
4626 )
4627 GROUP BY ps.customer_trx_id
4628 HAVING sum(ps.amount_due_remaining) <> 0;
4629
4630 IF (l_tot_trx8 > 1) then
4631 update ar_payments_interface pi
4632 set invoice8_status = 'AR_PLB_DUP_INV'
4633 where rowid = l_rowid;
4634 goto END_8;
4635 ELSE
4636 SELECT invoice_currency_code,
4637 amount_due_remaining
4638 INTO ps_currency_code8,
4639 trx_amt_due_rem8
4640 FROM ar_payment_schedules ps,
4641 ra_cust_trx_types tt
4642 WHERE ps.trx_number = l_matching_number8
4643 AND ps.status = decode(tt.allow_overapplication_flag,
4644 'N', 'OP',
4645 ps.status)
4646 AND ps.class NOT IN ('PMT','GUAR')
4647 AND ps.payment_schedule_id =
4648 (select min(ps.payment_schedule_id)
4649 from ar_payment_schedules ps,
4650 ra_cust_trx_types tt
4651 where ps.trx_number = l_matching_number8
4652 and ps.trx_date = l_resolved_matching8_date /* Bug fix 2926664 */
4653 and (EXISTS
4654 (
4655 select 'Exists from dual' from dual
4656 where l_customer_id = ps.customer_id
4657 union
4658 select 'Exists from hz_cust_acct_relate'
4659 from hz_cust_acct_relate rel
4660 where rel.cust_account_id = l_customer_id
4661 and related_cust_account_id = ps.customer_id
4662 and rel.status = 'A'
4663 and rel.bill_to_flag = 'Y'
4664 union
4665 select 'Exists ar_paying_relationships_v'
4666 from ar_paying_relationships_v rel,
4667 hz_cust_accounts acc
4668 where rel.party_id = acc.party_id
4669 and acc.cust_account_id = l_customer_id
4670 and rel.related_cust_account_id = ps.customer_id
4671 and l_receipt_date BETWEEN effective_start_date
4672 AND effective_end_date
4673 )
4674 or
4675 l_pay_unrelated_invoices = 'Y'
4676 )
4677 and ps.cust_trx_type_id = tt.cust_trx_type_id
4678 and ps.class NOT IN ('PMT','GUAR')
4679 and ps.status=decode(tt.allow_overapplication_flag,
4680 'N', 'OP',
4681 ps.status))
4682 and (EXISTS
4683 (
4684 select 'Exists from dual' from dual
4685 where l_customer_id = ps.customer_id
4686 union
4687 select 'Exists from hz_cust_acct_relate'
4688 from hz_cust_acct_relate rel
4689 where rel.cust_account_id = l_customer_id
4690 and related_cust_account_id = ps.customer_id
4691 and rel.status = 'A'
4692 and rel.bill_to_flag = 'Y'
4693 union
4694 select 'Exists ar_paying_relationships_v'
4695 from ar_paying_relationships_v rel,
4696 hz_cust_accounts acc
4697 where rel.party_id = acc.party_id
4698 and acc.cust_account_id = l_customer_id
4699 and rel.related_cust_account_id = ps.customer_id
4700 and l_receipt_date BETWEEN effective_start_date
4701 AND effective_end_date
4702 )
4703 or
4704 l_pay_unrelated_invoices = 'Y'
4705 )
4706 AND ps.cust_trx_type_id = tt.cust_trx_type_id;
4707 END IF;
4708 EXCEPTION
4709 WHEN NO_DATA_FOUND THEN
4710 goto END_8;
4711 END;
4712
4713 debug1('currency code8 of invoice from the ps = ' || ps_currency_code8);
4714 /********************************************************
4715 * if transmission has null currency code use the one
4716 * from Payment Schedules
4717 *********************************************************/
4718 IF (l_invoice_currency_code8 is NULL ) THEN
4722 /* update ar_payment_interface to have the invoice currency_code */
4719 debug1('currency code is null.. setting = ' || ps_currency_code8);
4720 l_invoice_currency_code8 := ps_currency_code8;
4721
4723 UPDATE ar_payments_interface
4724 SET invoice_currency_code8 = l_invoice_currency_code8
4725 WHERE rowid = l_rowid;
4726 END IF; /* end if invoice currency code was null */
4727
4728 /****************************************************************
4729 * check to see if the currency code matches or is was not included
4730 * in the transmission
4731 ****************************************************************/
4732 debug1('l_invoice_currency_code8 = ' || l_invoice_currency_code8);
4733 debug1('ps_currency_code = ' || ps_currency_code8);
4734
4735 IF (l_invoice_currency_code8 <> ps_currency_code8) then
4736 debug1('currency code give does not match payment schedules..');
4737 UPDATE AR_PAYMENTS_INTERFACE
4738 SET invoice8_status = 'AR_PLB_CURRENCY_BAD'
4739 WHERE rowid = l_rowid;
4740 ELSE
4741 /* Bug:1513671 we know the invoice currency code so we can now format the
4742 amount applied if we need to */
4743
4744 IF (p_format_amount8 = 'Y') THEN
4745 fnd_currency.Get_Info(l_invoice_currency_code8,
4746 l_precision,
4747 l_extended_precision,
4748 l_mau);
4749 l_amount_applied8 := round(l_amount_applied8 / power(10, l_precision),
4750 l_precision);
4751 END IF;
4752
4753 /*************************************************************
4754 * if the currency code of the transaction does not equal the
4755 * currency code of the receipt, then check for cross currency
4756 * or Euro case
4757 **************************************************************/
4758 IF ( l_invoice_currency_code8 <> l_currency_code) THEN
4759 debug1('currency code of receipt does not match currency code of inv');
4760
4761 /***********************************************************
4762 * we need to check to see if we have cross currency
4763 * profile enabled or we are dealing with a fixed rate
4764 * currency
4765 ************************************************************/
4766
4767 l_is_fixed_rate := gl_currency_api.is_fixed_rate(
4768 l_currency_code, /*receipt currency */
4769 l_invoice_currency_code8, /* inv currency */
4770 nvl(l_receipt_date,sysdate));
4771
4772 debug1('is this a fixed rate = ' || l_is_fixed_rate);
4773 debug1('is cross curr enabled?? ' || l_enable_cross_currency);
4774
4775 IF ( (l_is_fixed_rate = 'Y') or
4776 (l_enable_cross_currency = 'Y')) THEN
4777 /* we have to make sure that all fields are populated */
4778
4779 IF ( (l_amount_applied_from8 IS NULL) or
4780 (l_amount_applied8 IS NULL) or
4781 (l_trans_to_receipt_rate8 IS NULL) ) THEN
4782
4783 /* we need to check the rate 1st. If both amounts columns are
4784 populated, then we calculate the rate. If one amount is
4785 missing and the rate is null, then we try to get the rate from
4786 GL based on the profile option */
4787
4788 IF ( l_trans_to_receipt_rate8 is NULL) THEN
4789 debug1('trans_to_receipt_rate8 is null');
4790 /* if neither amount is null then we calculate the rate */
4791
4792 debug1('amount applied = ' || to_char(l_amount_applied8));
4793 debug1('amount applied from = ' || to_char(l_amount_applied_from8));
4794 IF ( (l_amount_applied8 IS NOT NULL) and
4795 (l_amount_applied_from8 IS NOT NULL)) Then
4796
4797 /********************************************
4798 * if we have a fixed rate, we need to get the
4799 * rate from GL and verify the validity of the
4800 * 2 amount columns with the rate that we get
4801 **********************************************/
4802 IF (l_is_fixed_rate = 'Y') THEN
4803 /* get the fixed rate from GL */
4804 l_trans_to_receipt_rate8 :=
4805 gl_currency_api.get_rate(
4806 l_invoice_currency_code8,
4807 l_currency_code,
4808 l_receipt_date);
4809
4810 /*************************************************
4811 * if all fields are populated, we need to
4812 * check to make sure that they are logically
4813 * correct.
4814 ************************************************/
4815 calc_amt_applied_fmt(l_invoice_currency_code8,
4816 l_amount_applied_from8,
4817 l_trans_to_receipt_rate8,
4818 l_temp_amt_applied,
4819 'N');
4820
4821 calc_amt_applied_from_fmt(l_currency_code,
4822 l_amount_applied8,
4823 l_trans_to_receipt_rate8,
4824 l_temp_amt_applied_from,
4825 'N');
4826
4827 ar_cc_lockbox.calc_cross_rate(
4831 l_currency_code,
4828 l_amount_applied8,
4829 l_amount_applied_from8,
4830 l_invoice_currency_code8,
4832 l_temp_trans_to_receipt_rate);
4833 IF ( (l_temp_amt_applied = l_amount_applied8) OR
4834 (l_temp_amt_applied_from =
4835 l_amount_applied_from8) OR
4836 (l_temp_trans_to_receipt_rate =
4837 l_trans_to_receipt_rate8)) THEN
4838
4839 /********************************************
4840 * since one or more of the conditions are
4841 * true then we assume that everything is
4842 * fine and we can write the rate to the
4843 * database
4844 *********************************************/
4845 debug1('validation passed ' );
4846 UPDATE ar_payments_interface
4847 SET trans_to_receipt_rate8 =
4848 l_trans_to_receipt_rate8
4849 WHERE rowid = l_rowid;
4850 ELSE
4851 UPDATE AR_PAYMENTS_INTERFACE
4852 SET invoice8_status =
4853 'AR_PLB_CC_INVALID_VALUE'
4854 WHERE rowid = l_rowid;
4855 END IF;
4856
4857 ELSE
4858 /* calculate the least rate that would convert
4859 the items */
4860 ar_cc_lockbox.calc_cross_rate(
4861 l_amount_applied8,
4862 l_amount_applied_from8,
4863 l_invoice_currency_code8,
4864 l_currency_code,
4865 l_trans_to_receipt_rate8);
4866
4870 SET trans_to_receipt_rate8 =
4867 /* once the rate has been calculated, we need
4868 to write it to the table */
4869 UPDATE ar_payments_interface
4871 l_trans_to_receipt_rate8
4872 WHERE rowid = l_rowid;
4873 END IF;
4874
4875 ELSE
4876 /* need to derive the rate if possible*/
4877 debug1( 'need to derive rate ');
4878 IF (p_default_exchange_rate_type IS NOT NULL or
4879 l_is_fixed_rate = 'Y' ) THEN
4880 l_trans_to_receipt_rate8 :=
4881 gl_currency_api.get_rate_sql(
4882 l_invoice_currency_code8,
4883 l_currency_code,
4884 l_receipt_date,
4885 p_default_exchange_rate_type);
4886 debug1('calculated rate = ' || to_char(l_trans_to_receipt_rate8));
4887 /* if there is no rate in GL, there is nothing
4888 more we can do */
4889 IF (l_trans_to_receipt_rate8 < 0 ) THEN
4890
4891 UPDATE AR_PAYMENTS_INTERFACE
4892 SET invoice8_status = 'AR_PLB_NO_EXCHANGE_RATE'
4893 WHERE rowid = l_rowid;
4894
4895 ELSE
4896 /* once the rate has been calculated, we need
4897 to write it to the table */
4898
4899 debug1('writing rate to database ' || to_char(l_trans_to_receipt_rate8));
4900
4901 UPDATE ar_payments_interface
4902 SET trans_to_receipt_rate8 =
4903 l_trans_to_receipt_rate8
4904 WHERE rowid = l_rowid;
4905
4906 /* finish the calculation because we have a rate*/
4907
4908 IF (l_amount_applied_from8 IS NULL) THEN
4909 calc_amt_applied_from_fmt(l_currency_code,
4910 l_amount_applied8,
4911 l_trans_to_receipt_rate8,
4912 l_amount_applied_from8,
4913 l_format_amount_applied_from8);
4914 update ar_payments_interface
4915 set amount_applied_from8 =
4916 l_amount_applied_from8
4917 where rowid = l_rowid;
4918
4919 ELSE
4920 /* calculate amount applied and save to db */
4921 calc_amt_applied_fmt(l_invoice_currency_code8,
4922 l_amount_applied_from8,
4923 l_trans_to_receipt_rate8,
4924 l_amount_applied8,
4925 l_format_amount8);
4926 debug1('calculated amt applied = ' || to_char(l_amount_applied8));
4927
4928 /* to deal with rounding errors that happen
4929 with fixed rate currencies, we need to
4930 take the amount_due_remaining of the trx
4931 convert it to the receipt currency and
4932 compare that value with the
4933 amount_applied_from original value
4934
4935 ADDED for BUG 883345 */
4936
4937 IF (l_is_fixed_rate = 'Y') THEN
4938
4939 calc_amt_applied_from_fmt(l_currency_code,
4940 trx_amt_due_rem8,
4941 l_trans_to_receipt_rate8,
4942 amt_applied_from_tc,
4943 'N');
4944
4945 IF (amt_applied_from_tc =
4946 l_amount_applied_from8 ) THEN
4947 IF (l_format_amount8 = 'Y') THEN
4948 fnd_currency.Get_Info(
4949 l_invoice_currency_code8,
4950 l_precision,
4951 l_extended_precision,
4952 l_mau);
4953
4954 l_amount_applied8 :=
4955 trx_amt_due_rem8 * (10**l_precision);
4956 ELSE
4957 l_amount_applied8 := trx_amt_due_rem8 ;
4958 END IF;
4959 END IF;
4960 END IF;
4961
4962 update ar_payments_interface
4963 set amount_applied8 =
4964 l_amount_applied8
4965 where rowid = l_rowid;
4966
4967 END IF; /* if amount_applied_From8 is null */
4968 END IF; /* if rate is null after it is calc */
4969 ELSE /* Bug 1519765 */
4970 UPDATE AR_PAYMENTS_INTERFACE
4974 END IF;
4971 SET invoice8_status = 'AR_PLB_NO_EXCHANGE_RATE'
4972 WHERE rowid = l_rowid;
4973 END IF; /* if derive profile is not null */
4975
4976 /* we know that trans_to_receipt_rate8 is not null,
4977 therefore, one of the amount values must be null and
4978 we have to calculate it */
4979 ELSE
4980 /* is amount_applied_From null?? */
4981 IF (l_amount_applied_from8 IS NULL) THEN
4982 calc_amt_applied_from_fmt(l_currency_code,
4983 l_amount_applied8,
4984 l_trans_to_receipt_rate8,
4985 l_amount_applied_from8,
4986 l_format_amount_applied_from8);
4987 update ar_payments_interface
4988 set amount_applied_from8 =
4989 l_amount_applied_from8
4993 calc_amt_applied_fmt(l_invoice_currency_code8,
4990 where rowid = l_rowid;
4991
4992 ELSE
4994 l_amount_applied_from8,
4995 l_trans_to_receipt_rate8,
4996 l_amount_applied8,
4997 l_format_amount8);
4998
4999 /* to deal with rounding errors that happen
5000 with fixed rate currencies, we need to
5001 take the amount_due_remaining of the trx
5002 convert it to the receipt currency and
5003 compare that value with the
5004 amount_applied_from original value
5005
5006 ADDED for BUG 883345 */
5007
5008 IF (l_is_fixed_rate = 'Y') THEN
5009
5010 calc_amt_applied_from_fmt(l_currency_code,
5011 trx_amt_due_rem8,
5012 l_trans_to_receipt_rate8,
5013 amt_applied_from_tc,
5014 'N');
5015
5016 IF (amt_applied_from_tc =
5017 l_amount_applied_from8 ) THEN
5018 IF (l_format_amount8 = 'Y') THEN
5019 fnd_currency.Get_Info(
5020 l_invoice_currency_code8,
5021 l_precision,
5022 l_extended_precision,
5023 l_mau);
5024
5025 l_amount_applied8 :=
5026 trx_amt_due_rem8 * (10**l_precision);
5027 ELSE
5028 l_amount_applied8 := trx_amt_due_rem8 ;
5029 END IF;
5030 END IF;
5031 END IF;
5032
5033 UPDATE AR_PAYMENTS_INTERFACE
5034 SET amount_applied8 =
5035 l_amount_applied8
5036 WHERE rowid = l_rowid;
5037
5038 END IF;
5039 END IF; /* trans to receipt_rate 8 is null */
5040 ELSE
5041 /*************************************************
5042 * if all fields are populated, we need to
5043 * check to make sure that they are logically
5044 * correct.
5048 l_amount_applied_from8,
5045 ***************************************************/
5046
5047 calc_amt_applied_fmt(l_invoice_currency_code8,
5049 l_trans_to_receipt_rate8,
5050 l_temp_amt_applied,
5051 'N');
5052
5053 calc_amt_applied_from_fmt(l_currency_code,
5054 l_amount_applied8,
5055 l_trans_to_receipt_rate8,
5056 l_temp_amt_applied_from,
5057 'N');
5058
5059 ar_cc_lockbox.calc_cross_rate(
5060 l_amount_applied8,
5061 l_amount_applied_from8,
5062 l_invoice_currency_code8,
5063 l_currency_code,
5064 l_temp_trans_to_receipt_rate);
5065 IF ( (l_temp_amt_applied = l_amount_applied8) OR
5066 (l_temp_amt_applied_from =
5067 l_amount_applied_from8) OR
5068 (l_temp_trans_to_receipt_rate =
5069 l_trans_to_receipt_rate8)) THEN
5070
5071 /* since one or more of the conditions are true
5072 then we assume that everything is fine. */
5073 debug1('validation passed ' );
5074 ELSE
5075 UPDATE AR_PAYMENTS_INTERFACE
5076 SET invoice8_status = 'AR_PLB_CC_INVALID_VALUE'
5077 WHERE rowid = l_rowid;
5078 END IF;
5079
5080 END IF; /* if one of the 3 items is NULL */
5081 ELSE
5082 /*****************************************************
5083 currencies do not match, they are not fixed rate and
5084 cross currency enabled profile is not on.
5085 then set the status to be a currency conflict between
5086 the invoice and receipt currencies
5087 ***************************************************/
5088 UPDATE AR_PAYMENTS_INTERFACE
5089 SET invoice8_status = 'AR_PLB_CURR_CONFLICT'
5090 WHERE rowid = l_rowid;
5091 END IF;
5092 ELSE /* Bug 2066392. Single currency */
5093 IF l_amount_applied_from8 is not null THEN
5094 IF l_amount_applied8 is not null THEN
5095 IF l_amount_applied_from8 <> l_amount_applied8 THEN
5096 UPDATE AR_PAYMENTS_INTERFACE
5097 SET invoice8_status = 'AR_PLB_CC_INVALID_VALUE'
5098 WHERE rowid = l_rowid;
5099 END IF;
5100 ELSE
5101 IF l_format_amount8 = 'Y' THEN
5102 fnd_currency.Get_Info(l_invoice_currency_code8,
5103 l_precision,
5104 l_extended_precision,
5105 l_mau);
5106 l_unformat_amount := l_amount_applied_from8 * power(10, l_precision);
5107 ELSE
5108 l_unformat_amount := l_amount_applied_from8;
5109 END IF;
5110 UPDATE AR_PAYMENTS_INTERFACE
5111 SET amount_applied8 = l_unformat_amount
5112 WHERE rowid = l_rowid;
5113 END IF;
5114 END IF;
5115 END IF;
5116 END IF;
5117 END IF; /* if matching number 8 is not null */
5118 <<END_8>>
5119 null;
5120
5121 END LOOP;
5122 END populate_add_inv_details;
5123
5124
5125
5129 | DESCRIPTION
5126 /*===========================================================================+
5127 | PROCEDURE
5128 | calc_amt_applied_from
5130 | This procedure will calculate the amount_applied_from column given
5131 | a rate, an amount_applied, and a currency code
5132 |
5133 | SCOPE - PUBLIC
5134 |
5135 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE
5136 |
5137 | ARGUMENTS : IN: trans_to_receipt_rate
5141 | IN OUT NOCOPY :
5138 | currency_code
5139 | amount_applied (in currency of transaction).
5140 |
5142 |
5143 | OUT NOCOPY : amount_applied_from
5144 |
5145 | RETURNS :
5146 |
5147 | NOTES
5148 | MODIFICATION HISTORY:
5149 | 11/13/1998 Debbie Jancis original
5150 | 02/02/1999 Debbie Jancis Modified to have the amount_applied_from
5151 | without the decimal point because the
5152 | values stored in the interface table have
5153 | implied decimal places.
5154 +---------------------------------------------------------------------------*/
5155
5156 PROCEDURE calc_amt_applied_from(
5157 p_currency_code IN VARCHAR2,
5158 p_amount_applied IN ar_payments_interface.amount_applied1%type,
5159 p_trans_to_receipt_rate IN ar_payments_interface.trans_to_receipt_rate1%type,
5160 amount_applied_from OUT NOCOPY ar_payments_interface.amount_applied_from1%type
5161 ) IS
5162 --
5163 l_mau NUMBER;
5164 l_precision NUMBER(1);
5165 l_extended_precision NUMBER;
5166 --
5167
5168 BEGIN
5169 --
5170 debug1( 'calc_amt_applied_from() +' );
5171 debug1('p_amount_applied = ' || to_char(p_amount_applied));
5172 debug1('p_trans_to_receipt_rate = ' || to_char(p_trans_to_receipt_rate));
5173 debug1('p curr code = ' || p_currency_code);
5174
5175 fnd_currency.Get_Info(
5176 p_currency_code,
5177 l_precision,
5178 l_extended_precision,
5179 l_mau);
5180 IF (l_mau IS NOT NULL) THEN
5181 amount_applied_from :=
5182 ROUND((p_amount_applied *
5183 p_trans_to_receipt_rate) /
5184 l_mau) * l_mau;
5185 ELSE
5186 amount_applied_from :=
5187 ROUND((p_amount_applied *
5188 p_trans_to_receipt_rate),
5189 l_precision);
5190 END IF; /* l_mau is not null */
5191
5192 /* after amount_applied_from is calculated, we need to remove
5193 the decimal place since the value stored in the interim
5194 table and then transfered to the interface tables is stored
5195 with an implied decimal */
5196
5197 debug1('p_amount_applied_from = ' || to_char(amount_applied_from));
5198 amount_applied_from := amount_applied_from * 10 ** l_precision;
5199 debug1('p_amount_applied_from = ' || to_char(amount_applied_from));
5200 debug1( 'calc_amt_applied_from() -' );
5201
5202 END calc_amt_applied_from;
5203
5204 /*===========================================================================+
5205 | PROCEDURE
5206 | calc_amt_applied
5207 | DESCRIPTION
5208 | This procedure will calculate the amount_applied column given
5209 | a rate, an amount_applied_from, and a currency code
5210 |
5211 | SCOPE - PUBLIC
5212 |
5213 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE
5214 |
5215 | ARGUMENTS : IN: trans_to_receipt_rate
5216 | invoice_currency_code
5217 | amount_applied_from (in currency of receipt).
5218 |
5219 | IN OUT NOCOPY :
5220 |
5221 | OUT NOCOPY : amount_applied
5222 |
5223 | RETURNS :
5224 |
5225 | NOTES
5226 | MODIFICATION HISTORY:
5227 | 11/13/1998 Debbie Jancis original
5228 | 02/02/1999 Debbie Jancis Modified to have the amount_applied
5229 | without the decimal point because the
5230 | values stored in the interface table have
5231 | implied decimal places.
5232 +---------------------------------------------------------------------------*/
5233
5234 PROCEDURE calc_amt_applied(
5235 p_invoice_currency_code IN VARCHAR2,
5236 p_amount_applied_from IN ar_payments_interface.amount_applied_from1%type,
5237 p_trans_to_receipt_rate IN ar_payments_interface.trans_to_receipt_rate1%type,
5238 amount_applied OUT NOCOPY ar_payments_interface.amount_applied1%type
5242 l_mau NUMBER;
5239 ) IS
5240
5241 --
5243 l_precision NUMBER(1);
5244 l_extended_precision NUMBER;
5245 --
5246
5247 BEGIN
5248 debug1( 'calc_amt_applied() +' );
5249 debug1('p_amount_applied_from = ' || to_char(p_amount_applied_from));
5250 debug1('p_trans_to_receipt_rate = ' || to_char(p_trans_to_receipt_rate));
5251 debug1('p inv curr code = ' || p_invoice_currency_code);
5252
5253 fnd_currency.Get_Info(
5254 p_invoice_currency_code,
5255 l_precision,
5256 l_extended_precision,
5257 l_mau);
5258 IF (l_mau IS NOT NULL) THEN
5259 amount_applied :=
5260 ROUND((p_amount_applied_from /
5261 p_trans_to_receipt_rate) /
5262 l_mau) * l_mau;
5263 ELSE
5264 amount_applied:=
5265 ROUND((p_amount_applied_from /
5266 p_trans_to_receipt_rate),
5267 l_precision);
5268 END IF; /* l_mau is not null */
5269
5270 /* before we return to the calling routine, we need to remove the
5271 decimal place as values are stored in the interface tables with
5272 implied decimal places. */
5273
5274 amount_applied := amount_applied * 10 ** l_precision;
5275 debug1('p_amount_applied = ' || to_char(amount_applied));
5276 debug1( 'calc_amt_applied() -' );
5277
5278 END calc_amt_applied;
5279
5280
5281 /*----------------------------------------------------------------------------
5282 This procedure calls arp_util.debug for the string passed.
5283 Till arp_util.debug is changed to provide an option to write to a
5284 file, we can use this procedure to write to a file at the time of testing.
5285 Un comment lines calling fnd_file package and that will write to a file.
5286 Please change the directory name so that it does not raise any exception.
5287 ----------------------------------------------------------------------------*/
5288 PROCEDURE debug1(str IN VARCHAR2) IS
5289 -- myfile utl_file.file_type;
5290 -- dir_name varchar2(100);
5291 -- out_file_name varchar2(8);
5292 -- log_file_name varchar2(8);
5293 BEGIN
5294 --
5295 -- Check for the directory name.
5296 -- dir_name := '/sqlcom/log';
5297 -- log_file_name := 'ar.log';
5298 -- out_file_name := 'ar.out';
5299 -- myfile := utl_file.fopen(dir_name, out_file_name, 'a');
5303 IF PG_DEBUG in ('Y', 'C') THEN
5300 -- utl_file.put(myfile, str);
5301 -- utl_file.fclose(myfile);
5302 --
5304 arp_util.debug(str);
5305 END IF;
5306 END debug1;
5307
5308 /*************************************************************
5309 This procedure will populate all the temporary columns
5310 in the ar_payments_interface table with the original
5311 values in the table for the columns:
5312 amount_applied1-8, amount_applied_from1-8,
5313 invoice_currency_code1-8, and the trans_to_Receipt_rate1-8
5314 so that in the event of a problem with the validation
5315 stage, at a later point, the original values can be returned
5316 to the database so the user will be able to determine which
5317 values were included in the transmission and which columns
5318 were not.
5319 *************************************************************/
5320 PROCEDURE pop_temp_columns IS
5321 BEGIN
5322 update ar_payments_interface pi
5323 set tmp_amt_applied_from1 = pi.amount_applied_from1,
5324 tmp_amt_applied_from2 = pi.amount_applied_from2,
5325 tmp_amt_applied_from3 = pi.amount_applied_from3,
5326 tmp_amt_applied_from4 = pi.amount_applied_from4,
5327 tmp_amt_applied_from5 = pi.amount_applied_from5,
5328 tmp_amt_applied_from6 = pi.amount_applied_from6,
5329 tmp_amt_applied_from7 = pi.amount_applied_from7,
5330 tmp_amt_applied_from8 = pi.amount_applied_from8,
5331 tmp_amt_applied1 = pi.amount_applied1,
5332 tmp_amt_applied2 = pi.amount_applied2,
5333 tmp_amt_applied3 = pi.amount_applied3,
5334 tmp_amt_applied4 = pi.amount_applied4,
5335 tmp_amt_applied5 = pi.amount_applied5,
5336 tmp_amt_applied6 = pi.amount_applied6,
5337 tmp_amt_applied7 = pi.amount_applied7,
5338 tmp_amt_applied8 = pi.amount_applied8,
5339 tmp_inv_currency_code1 = pi.invoice_currency_code1,
5340 tmp_inv_currency_code2 = pi.invoice_currency_code2,
5341 tmp_inv_currency_code3 = pi.invoice_currency_code3,
5342 tmp_inv_currency_code4 = pi.invoice_currency_code4,
5343 tmp_inv_currency_code5 = pi.invoice_currency_code5,
5344 tmp_inv_currency_code6 = pi.invoice_currency_code6,
5345 tmp_inv_currency_code7 = pi.invoice_currency_code7,
5349 tmp_trans_to_rcpt_rate3 = pi.trans_to_receipt_rate3,
5346 tmp_inv_currency_code8 = pi.invoice_currency_code8,
5347 tmp_trans_to_rcpt_rate1 = pi.trans_to_receipt_rate1,
5348 tmp_trans_to_rcpt_rate2 = pi.trans_to_receipt_rate2,
5350 tmp_trans_to_rcpt_rate4 = pi.trans_to_receipt_rate4,
5351 tmp_trans_to_rcpt_rate5 = pi.trans_to_receipt_rate5,
5352 tmp_trans_to_rcpt_rate6 = pi.trans_to_receipt_rate6,
5353 tmp_trans_to_rcpt_rate7 = pi.trans_to_receipt_rate7,
5354 tmp_trans_to_rcpt_rate8 = pi.trans_to_receipt_rate8;
5355 END pop_temp_columns;
5356
5357 PROCEDURE restore_orig_values IS
5358
5359 BEGIN
5360 update ar_payments_interface
5361 set amount_applied_from1 = tmp_amt_applied_from1,
5362 amount_applied_from2 = tmp_amt_applied_from2,
5363 amount_applied_from3 = tmp_amt_applied_from3,
5364 amount_applied_from4 = tmp_amt_applied_from4,
5365 amount_applied_from5 = tmp_amt_applied_from5,
5366 amount_applied_from6 = tmp_amt_applied_from6,
5367 amount_applied_from7 = tmp_amt_applied_from7,
5368 amount_applied_from8 = tmp_amt_applied_from8,
5369 amount_applied1 = tmp_amt_applied1,
5370 amount_applied2 = tmp_amt_applied2,
5371 amount_applied3 = tmp_amt_applied3,
5372 amount_applied4 = tmp_amt_applied4,
5373 amount_applied5 = tmp_amt_applied5,
5374 amount_applied6 = tmp_amt_applied6,
5375 amount_applied7 = tmp_amt_applied7,
5376 amount_applied8 = tmp_amt_applied8,
5377 invoice_currency_code1 = tmp_inv_currency_code1,
5378 invoice_currency_code2 = tmp_inv_currency_code2,
5379 invoice_currency_code3 = tmp_inv_currency_code3,
5380 invoice_currency_code4 = tmp_inv_currency_code4,
5381 invoice_currency_code5 = tmp_inv_currency_code5,
5382 invoice_currency_code6 = tmp_inv_currency_code6,
5383 invoice_currency_code7 = tmp_inv_currency_code7,
5384 invoice_currency_code8 = tmp_inv_currency_code8,
5385 trans_to_receipt_rate1 = tmp_trans_to_rcpt_rate1,
5386 trans_to_receipt_rate2 = tmp_trans_to_rcpt_rate2,
5387 trans_to_receipt_rate3 = tmp_trans_to_rcpt_rate3,
5388 trans_to_receipt_rate4 = tmp_trans_to_rcpt_rate4,
5389 trans_to_receipt_rate5 = tmp_trans_to_rcpt_rate5,
5390 trans_to_receipt_rate6 = tmp_trans_to_rcpt_rate6,
5391 trans_to_receipt_rate7 = tmp_trans_to_rcpt_rate7,
5392 trans_to_receipt_rate8 = tmp_trans_to_rcpt_rate8
5393 where status <> 'AR_PLB_TRANSFERRED';
5394
5395 END restore_orig_values;
5396
5397
5398 PROCEDURE are_values_valid (
5399 p_invoice_currency_code IN VARCHAR2,
5400 p_amount_applied_from IN ar_payments_interface.amount_applied_from1%type,
5401 p_trans_to_receipt_rate IN ar_payments_interface.trans_to_receipt_rate1%type,
5402 p_amount_applied IN ar_payments_interface.amount_applied1%type,
5403 p_currency_code IN VARCHAR2,
5404 valid OUT NOCOPY VARCHAR2 ) IS
5405
5406 /* temporary variables used to check if amount_applied, amount_applied_from
5407 and trans_to_receipt rates are given */
5408 l_temp_amt_applied ar_payments_interface.amount_applied1%type;
5409 l_temp_amt_applied_from ar_payments_interface.amount_applied_from1%type;
5410 l_temp_trans_to_receipt_rate ar_payments_interface.trans_to_receipt_rate1%type;
5411
5412 BEGIN
5413 /*************************************************
5414 * if all fields are populated, we need to
5415 * check to make sure that they are logically
5416 * correct.
5417 ************************************************/
5418
5419 calc_amt_applied(p_invoice_currency_code,
5420 p_amount_applied_from,
5421 p_trans_to_receipt_rate,
5422 l_temp_amt_applied);
5423
5424 calc_amt_applied_from(p_currency_code,
5425 p_amount_applied,
5426 p_trans_to_receipt_rate,
5427 l_temp_amt_applied_from);
5428
5429 ar_cc_lockbox.calc_cross_rate(
5430 p_amount_applied,
5431 p_amount_applied_from,
5432 p_invoice_currency_code,
5433 p_currency_code,
5434 l_temp_trans_to_receipt_rate);
5435
5436 IF ( (l_temp_amt_applied = p_amount_applied) OR
5437 (l_temp_amt_applied_from = p_amount_applied_from) OR
5438 (l_temp_trans_to_receipt_rate = p_trans_to_receipt_rate)) THEN
5439 VALID := 'Y';
5440 ELSE
5441 VALID := 'N';
5442 END IF;
5443
5444 END are_values_valid;
5445
5451 | a rate, an amount_applied, and a currency code
5446 /*===========================================================================+
5447 | PROCEDURE
5448 | calc_amt_applied_from_fmt
5449 | DESCRIPTION
5450 | This procedure will calculate the amount_applied_from column given
5452 | For USD 100.00, return 10000 if format_amount is 'Y', otherwise
5453 | return 100.
5454 |
5455 | SCOPE - PRIVATE
5456 |
5457 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE
5458 |
5459 | ARGUMENTS : IN: trans_to_receipt_rate
5460 | currency_code
5461 | amount_applied (in currency of transaction).
5462 | format_amount (Y/N)
5463 |
5464 | IN OUT NOCOPY :
5465 |
5466 | OUT NOCOPY : amount_applied_from
5467 |
5468 | RETURNS :
5469 |
5470 | NOTES
5471 | MODIFICATION HISTORY:
5472 | 11/15/2002 Shin Matsuda Created
5473 +---------------------------------------------------------------------------*/
5474
5475 PROCEDURE calc_amt_applied_from_fmt(
5476 p_currency_code IN VARCHAR2,
5477 p_amount_applied IN ar_payments_interface.amount_applied1%type,
5478 p_trans_to_receipt_rate IN ar_payments_interface.trans_to_receipt_rate1%type,
5479 amount_applied_from OUT NOCOPY ar_payments_interface.amount_applied_from1%type,
5480 p_format_amount IN VARCHAR2
5481 ) IS
5482 --
5483 l_mau NUMBER;
5484 l_precision NUMBER(1);
5485 l_extended_precision NUMBER;
5486 --
5487
5488 BEGIN
5489 --
5490 debug1( 'calc_amt_applied_from_fmt() +' );
5491 debug1('p_amount_applied = ' || to_char(p_amount_applied));
5492 debug1('p_trans_to_receipt_rate = ' || to_char(p_trans_to_receipt_rate));
5493 debug1('p curr code = ' || p_currency_code);
5494
5495 fnd_currency.Get_Info(
5496 p_currency_code,
5497 l_precision,
5498 l_extended_precision,
5499 l_mau);
5500 IF (l_mau IS NOT NULL) THEN
5501 amount_applied_from :=
5502 ROUND((p_amount_applied *
5503 p_trans_to_receipt_rate) /
5504 l_mau) * l_mau;
5505 ELSE
5506 amount_applied_from :=
5507 ROUND((p_amount_applied *
5508 p_trans_to_receipt_rate),
5509 l_precision);
5510 END IF; /* l_mau is not null */
5511
5512 /* after amount_applied_from is calculated, we need to remove
5513 the decimal place since the value stored in the interim
5514 table and then transfered to the interface tables is stored
5515 with an implied decimal */
5516
5517 debug1('p_amount_applied_from = ' || to_char(amount_applied_from));
5518 IF p_format_amount = 'Y' THEN
5519 amount_applied_from := amount_applied_from * 10 ** l_precision;
5520 END IF;
5521 debug1('p_amount_applied_from = ' || to_char(amount_applied_from));
5522 debug1( 'calc_amt_applied_from_fmt() -' );
5523
5524 END calc_amt_applied_from_fmt;
5525
5526 /*===========================================================================+
5527 | PROCEDURE
5528 | calc_amt_applied_fmt
5529 | DESCRIPTION
5530 | This procedure will calculate the amount_applied column given
5531 | a rate, an amount_applied_from, and a currency code
5532 | For USD 100.00, return 10000 if format_amount is 'Y', otherwise
5533 | return 100.
5534 |
5535 | SCOPE - PRIVATE
5536 |
5537 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE
5538 |
5539 | ARGUMENTS : IN: trans_to_receipt_rate
5540 | invoice_currency_code
5541 | amount_applied_from (in currency of receipt).
5542 | format_amount (Y/N)
5543 |
5544 | IN OUT NOCOPY :
5545 |
5546 | OUT NOCOPY : amount_applied
5547 |
5548 | RETURNS :
5549 |
5550 | NOTES
5551 | MODIFICATION HISTORY:
5552 | 11/15/2002 Shin Matsuda Created
5553 +---------------------------------------------------------------------------*/
5554
5555 PROCEDURE calc_amt_applied_fmt(
5556 p_invoice_currency_code IN VARCHAR2,
5557 p_amount_applied_from IN ar_payments_interface.amount_applied_from1%type,
5558 p_trans_to_receipt_rate IN ar_payments_interface.trans_to_receipt_rate1%type,
5559 amount_applied OUT NOCOPY ar_payments_interface.amount_applied1%type,
5560 p_format_amount IN VARCHAR2
5561 ) IS
5562
5563 --
5564 l_mau NUMBER;
5565 l_precision NUMBER(1);
5566 l_extended_precision NUMBER;
5567 --
5568
5569 BEGIN
5570 debug1( 'calc_amt_applied_fmt() +' );
5571 debug1('p_amount_applied_from = ' || to_char(p_amount_applied_from));
5572 debug1('p_trans_to_receipt_rate = ' || to_char(p_trans_to_receipt_rate));
5573 debug1('p inv curr code = ' || p_invoice_currency_code);
5574
5575 fnd_currency.Get_Info(
5576 p_invoice_currency_code,
5577 l_precision,
5578 l_extended_precision,
5579 l_mau);
5580 IF (l_mau IS NOT NULL) THEN
5581 amount_applied :=
5582 ROUND((p_amount_applied_from /
5583 p_trans_to_receipt_rate) /
5584 l_mau) * l_mau;
5585 ELSE
5586 amount_applied:=
5587 ROUND((p_amount_applied_from /
5588 p_trans_to_receipt_rate),
5589 l_precision);
5593 decimal place as values are stored in the interface tables with
5590 END IF; /* l_mau is not null */
5591
5592 /* before we return to the calling routine, we need to remove the
5594 implied decimal places. */
5595
5596 IF p_format_amount = 'Y' THEN
5597 amount_applied := amount_applied * 10 ** l_precision;
5598 END IF;
5599 debug1('p_amount_applied = ' || to_char(amount_applied));
5600 debug1( 'calc_amt_applied_fmt() -' );
5601
5602 END calc_amt_applied_fmt;
5603
5604 END ar_cc_lockbox;
5605
5606
[Home] [Help]