[Home] [Help]
PACKAGE BODY: APPS.AP_ACCTG_PAY_ROUND_PKG
Source
1 PACKAGE BODY AP_ACCTG_PAY_ROUND_PKG AS
2 /* $Header: apacrndb.pls 120.22.12010000.4 2008/08/15 01:42:06 gagrawal ship $ */
3
4 -- Logging Infra
5 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'AP.PLSQL.AP_ACCTG_PAY_ROUND_PKG.';
13 -- Logging Infra
14
15 -------------------------------------------------------------------------------
16 -- PROCEDURE Do_Rounding
17 -- This procedure calls different rounding procedures based on the event type.
18 -- Single point of entry for the rounding calculations.
19 --
20 --------------------------------------------------------------------------------
21 PROCEDURE Do_Rounding
22 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
23 ,P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
24 ,P_Clr_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
25 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
26 ,P_Inv_Pay_Rec IN ap_acctg_pay_dist_pkg.r_inv_pay_info
27 ,P_Prepay_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
28 ,P_Prepay_Hist_Rec IN AP_ACCTG_PREPAY_DIST_PKG.r_prepay_hist_info
29 ,P_Prepay_Dist_Rec IN AP_ACCTG_PREPAY_DIST_PKG.r_prepay_dist_info
30 ,P_Calling_Sequence IN VARCHAR2
31 ) IS
32
33 l_curr_calling_sequence VARCHAR2(2000);
34 l_prepay_acctg_amt NUMBER;
35 l_prepay_amt NUMBER;
36
37 -- Logging Infra:
38 l_procedure_name CONSTANT VARCHAR2(30) := 'DO_Rounding';
39 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
40
41 -- bug 6600341
42 l_acctg_event_id XLA_EVENTS.EVENT_ID%TYPE;
43
44
45 BEGIN
46
47 l_curr_calling_sequence := 'AP_Acctg_Pay_Round_Pkg.Do_Rounding<- ' ||
48 p_calling_sequence;
49
50 -- Logging Infra: Setting up runtime level
51 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
52
53 -- Logging Infra: Procedure level
54 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
55 l_log_msg := 'Begin of procedure '|| l_procedure_name;
56 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
57 END IF;
58
59
60 /* Check for rounding only if the invoice currency or payment currency is different than
61 the base currency */
62 IF (p_inv_rec.invoice_currency_code <> ap_accounting_pay_pkg.g_base_currency_code) OR
63 (p_inv_rec.payment_currency_code <> ap_accounting_pay_pkg.g_base_currency_code) THEN
64
65 IF (p_prepay_dist_rec.invoice_distribution_id IS NOT NULL) THEN
66 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
67 l_log_msg := 'Calling procedure Total_Appl for dist: '
68 || p_prepay_dist_rec.invoice_distribution_id;
69 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
70 END IF;
71
72
73 -- Calculate the total application rounding
74 Total_Appl(p_xla_event_rec,
75 p_pay_hist_rec,
76 p_clr_hist_rec,
77 p_inv_rec,
78 p_prepay_inv_rec,
79 p_prepay_hist_rec,
80 p_prepay_dist_rec,
81 l_curr_calling_sequence);
82
83 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
84 l_log_msg := 'Procedure Total_Appl executed';
85 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
86 END IF;
87
88
89 /* Get the prepayment app dists amount that has already been accounted */
90 SELECT SUM(-1 * APAD.Amount)
91 INTO l_prepay_acctg_amt
92 FROM AP_Prepay_App_Dists APAD,
93 AP_Invoice_Distributions_All AID
94 WHERE APAD.Prepay_App_Distribution_ID = AID.Invoice_Distribution_ID
95 AND AID.Prepay_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id;
96
97
98 SELECT AID.Amount
99 INTO l_prepay_amt
100 FROM AP_Invoice_Distributions_All AID
101 WHERE AID.Invoice_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id;
102
103
104 -- Check for final application rounding only if this prepayment has been
105 -- fully applied
106 IF (l_prepay_acctg_amt = l_prepay_amt) THEN
107
108 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
109 l_log_msg := 'Calling procedure Final_Appl for prepay dist: '
110 || p_prepay_dist_rec.prepay_distribution_id;
111 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
112 END IF;
113
114 Final_Appl
115 (p_xla_event_rec,
116 p_pay_hist_rec,
117 p_clr_hist_rec,
118 p_inv_rec,
119 p_prepay_inv_rec,
120 p_prepay_hist_rec,
121 p_prepay_dist_rec,
122 l_curr_calling_sequence);
123
124 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
125 l_log_msg := 'Procedure Final_Appl executed';
126 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
127 END IF;
128
129 END IF;
130
131 ELSE
132
133 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
134 l_log_msg := 'Calling procedure Total_Pay for invoice: '
135 || p_inv_rec.invoice_id;
136 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
137 END IF;
138
139
140 -- Calculate the total payment rounding
141 Total_Pay(p_xla_event_rec,
142 p_pay_hist_rec,
143 p_inv_rec,
144 p_inv_pay_rec,
145 l_curr_calling_sequence);
146
147 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
148 l_log_msg := 'Procedure Total_Pay executed';
149 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
150 END IF;
151
152
153 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
154 l_log_msg := 'Calling procedure Compare_Pay for invoice: '
155 || p_inv_rec.invoice_id;
156 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
157 END IF;
158
159 -- Calculate the payment to maturity, payment to clearing and
160 -- maturity to clearing rounding
161 Compare_Pay(p_xla_event_rec,
162 p_pay_hist_rec,
163 p_inv_rec,
164 p_inv_pay_rec,
165 l_curr_calling_sequence);
166
167 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
168 l_log_msg := 'Procedure Compare_Pay executed';
169 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
170 END IF;
171
172
173 END IF; -- if prepayment event type
174
175
176 /* Calculating the final payment rounding only when the invoice is fully paid */
177 IF (AP_Accounting_Pay_Pkg.Is_Final_Payment(p_inv_rec,
178 0, -- payment amt
179 0, -- discount taken
180 0, -- prepay amount
181 p_xla_event_rec.event_type_code,
182 l_curr_calling_sequence)) THEN
183
184 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
185 l_log_msg := 'Calling procedure Final_Pay for invoice: '
186 || p_inv_rec.invoice_id;
187 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
188 END IF;
189
190 -- Bug 6600341
191 -- Final Payment Rounding should not be done if there are XLA Events
192 -- still pending processing
193
194 SELECT MAX(accounting_event_id)
195 INTO l_acctg_event_id
196 FROM ap_invoice_payments_all
197 WHERE invoice_id = p_inv_rec.invoice_id
198 AND NVL(accrual_posted_flag, 'N') = 'N';
199
200 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
201 l_log_msg := 'Fetched Acctg event id: '|| l_acctg_event_id
202 || ' , current XLA Event ID is: ' || p_xla_event_rec.event_id
203 || ' , Invoice ID: ' || p_inv_rec.invoice_id;
204 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
205 l_log_msg);
206 END IF;
207
208 IF p_xla_event_rec.event_id = l_acctg_event_id THEN -- bug 6600341
209
210 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
211 l_log_msg := 'Calling procedure Final_Pay for invoice: '
212 || p_inv_rec.invoice_id;
213 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
214 END IF;
215
216 -- Calculate the final payment rounding to relieve the liability on the
217 -- invoice fully.
218 Final_Pay(p_xla_event_rec,
219 p_pay_hist_rec,
220 p_clr_hist_rec,
221 p_inv_rec,
222 p_inv_pay_rec,
223 p_prepay_inv_rec,
224 p_prepay_hist_rec,
225 p_prepay_dist_rec,
226 l_curr_calling_sequence);
227
228 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
229 l_log_msg := 'Procedure Final_Pay executed';
230 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
231 END IF;
232
233 ELSE -- bug 6600341 contd
234 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
235 l_log_msg := 'Procedure Final_Pay for invoice not called: '
236 || p_inv_rec.invoice_id;
237 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
238 l_log_msg);
239 END IF;
240 END IF; -- bug 6600341 end
241
242 END IF;
243 END IF;
244
245 -- Logging Infra: Procedure level
246 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
247 l_log_msg := 'End of procedure '|| l_procedure_name;
248 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
249 END IF;
250
251
252 EXCEPTION
253 WHEN OTHERS THEN
254 IF (SQLCODE <> -20001) THEN
255 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
256 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
257 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
258 END IF;
259 APP_EXCEPTION.RAISE_EXCEPTION;
260
261 END Do_Rounding;
262
263
264
265 -------------------------------------------------------------------------------
266 -- PROCEDURE Final_Pay
267 -- This procedure calculates the rounding amount needed to relieve liability
268 -- when a final payment is made on a foreign currency invoice and creates
269 -- a final payment rounding if the amount is not fully relieved.
270 --
271 --------------------------------------------------------------------------------
272 PROCEDURE Final_Pay
273 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
274 ,P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
275 ,P_Clr_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
276 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
277 ,P_Inv_Pay_Rec IN ap_acctg_pay_dist_pkg.r_inv_pay_info
278 ,P_Prepay_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
279 ,P_Prepay_Hist_Rec IN AP_ACCTG_PREPAY_DIST_PKG.r_prepay_hist_info
280 ,P_Prepay_Dist_Rec IN AP_ACCTG_PREPAY_DIST_PKG.r_prepay_dist_info
281 ,P_Calling_Sequence IN VARCHAR2
282 ) IS
283
284 l_curr_calling_sequence VARCHAR2(2000);
285 l_sum_pay_dist_base_amt NUMBER;
286 l_sum_prepay_base_amt NUMBER;
287 l_total_dist_base_amt NUMBER;
288
289
290 -- Bug 5570002 - should exclude the TERV if ERV is excluded
291 -- Bug 7314656, added historical flag and accounting_event_id
292 CURSOR Invoice_Dists
293 (P_Invoice_ID IN NUMBER
294 ) IS
295 SELECT AID.Invoice_Distribution_ID,
296 AID.Line_Type_Lookup_Code,
297 AID.related_id,
298 AID.Amount,
299 AID.Base_Amount,
300 AID.Invoice_Id,
301 AID.accounting_event_id,
302 AID.historical_flag
303 FROM AP_Invoice_Distributions_All AID,
304 Financials_System_Params_All FSP
305 WHERE AID.Invoice_ID = p_invoice_id
306 AND AID.Line_Type_Lookup_Code NOT IN ('PREPAY', 'AWT', 'ERV', 'TERV')
307 AND AID.Prepay_Distribution_ID IS NULL
308 AND AID.Prepay_Tax_Parent_ID IS NULL -- For tax dists created in R11.5
309 AND AID.Org_ID = FSP.Org_ID
310 --Bug6511672
311 /*AND 'INVOICE CANCELLED' <> (SELECT event_type_code
312 FROM xla_events
313 WHERE event_id = AID.accounting_event_id)*/
314 --bug6614371
315 -- Bug 6712649. Added Credit and Debit memo cancelled
316 AND NOT EXISTS (SELECT 1
317 FROM xla_events
318 WHERE event_id = AID.accounting_event_id
319 AND event_type_code IN ('INVOICE CANCELLED', 'PREPAYMENT CANCELLED',
320 'CREDIT MEMO CANCELLED',
321 'DEBIT MEMO CANCELLED'))
322 AND ((NVL(FSP.Purch_Encumbrance_Flag,'N') = 'N'
323 AND AID.Match_Status_Flag IN ('T','A'))
324 OR
325 ((NVL(FSP.Purch_Encumbrance_Flag,'N') = 'Y'
326 AND AID.Match_Status_Flag = 'A')));
327
328
329 -- bug7314656, cursor added to check if the current
330 -- event is an adjustment event with a net balance 0
331 -- under no liability posting method.
332 CURSOR c_sum_per_event(p_acct_event_id NUMBER) IS
333 SELECT SUM(amount), count(1)
334 FROM ap_invoice_distributions_all aid,
335 xla_events evnt,
336 ap_system_parameters_all asp
337 WHERE aid.accounting_event_id = p_acct_event_id
338 AND aid.accounting_event_id = evnt.event_id
339 AND evnt.event_type_code='INVOICE ADJUSTED'
340 AND aid.org_id = asp.org_id
341 AND automatic_offsets_flag = 'N'
342 AND aid.historical_flag = 'Y';
343
344 b_generate_pay_dist BOOLEAN;
345 l_sum_per_event NUMBER;
346 l_dist_count_per_event NUMBER;
347
348
349
350 l_max_prepay_rec AP_PREPAY_APP_DISTS%ROWTYPE;
351 l_pad_rec AP_PREPAY_APP_DISTS%ROWTYPE;
352 l_max_pay_rec AP_PAYMENT_HIST_DISTS%ROWTYPE;
353 l_pd_rec AP_PAYMENT_HIST_DISTS%ROWTYPE;
354 l_erv_base_amount AP_INVOICE_DISTRIBUTIONS_ALL.base_amount%type := 0;
355
356 -- Logging Infra:
357 l_procedure_name CONSTANT VARCHAR2(30) := 'Final_Pay';
358 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
359
360
361 BEGIN
362
363 l_curr_calling_sequence := 'AP_Acctg_Pay_Round_Pkg.Final_Pay<- ' ||
364 p_calling_sequence;
365
366 -- Logging Infra: Procedure level
367 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
368 l_log_msg := 'Begin of procedure '|| l_procedure_name;
369 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
370 END IF;
371
372
373 FOR l_inv_dist_rec IN Invoice_Dists(p_inv_rec.invoice_id)
374 LOOP
375
376 --bug7314656, added the check to see if the current event is
377 --a historical adjustment distribution, with the net balance
378 --as 0 under automatic offsets off condition
379 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
380 l_log_msg := 'Checking b_generate_pay_dist';
381 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
382 END IF;
383
384 b_generate_pay_dist := TRUE;
385 IF nvl(l_inv_dist_rec.historical_flag, 'N') ='Y' THEN
386 OPEN c_sum_per_event(l_inv_dist_rec.accounting_event_id);
387 FETCH c_sum_per_event into l_sum_per_event, l_dist_count_per_event;
388
389 -- > 0 case is to handled the case that only 1 line in adjustment event and itself amount is 0
390 If l_dist_count_per_event > 0 AND l_sum_per_event = 0 THEN
391 b_generate_pay_dist := FALSE;
392 END IF;
393
394 CLOSE c_sum_per_event;
395
396 END IF;
397
398 IF b_generate_pay_dist THEN
399 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
400 l_log_msg := 'b_generate_pay_dist = TRUE for Invoice Distribution : ' ||
401 l_inv_dist_rec.invoice_distribution_id;
402 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
403 END IF;
404 ELSE
405 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
406 l_log_msg := 'b_generate_pay_dist = FALSE(No error) for Invoice Distribution : ' ||
407 l_inv_dist_rec.invoice_distribution_id;
408 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
409 END IF;
410 END IF ;
411
412 --bug7314656, proceed to calculate the final payment rounding only
413 --if the current event is not an adjustment distribution with a net
414 --0 balance under automatic offsets off condition
415 IF b_generate_pay_dist then
416
417 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
418 l_log_msg := 'Withi CUR loop: Invoice_Dists: Invoice_Dist_ID = '||
419 l_inv_dist_rec.invoice_distribution_id;
420 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
421 END IF;
422
423
424 -- Get the base amount from the payment hist distributions table for
425 -- this invoice distribution.
426 IF (p_xla_event_rec.event_type_code IN
427 ('PAYMENT CLEARED', 'PAYMENT CLEARING ADJUSTED')) THEN
428
429 -- bug 5570002 modified add the tax erv
430 -- Bug 7138115. Added additional join conditions to improve performance
431 SELECT SUM(--DECODE(APHD.Pay_Dist_Lookup_Code,
432 -- 'EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
433 -- 'TAX EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
434 APHD.Invoice_Dist_Base_Amount)
435 INTO l_sum_pay_dist_base_amt
436 FROM AP_Payment_Hist_Dists APHD,
437 AP_Payment_History_All APH,
438 AP_Invoice_Payments_All AIP
439 WHERE ((APHD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
440 AND APHD.Pay_Dist_Lookup_Code IN
441 ('CASH', 'DISCOUNT', 'FINAL PAYMENT ROUNDING'))
442 OR (APHD.Pay_Dist_Lookup_Code='AWT'
443 AND APHD.AWT_Related_ID = l_inv_dist_rec.invoice_distribution_id))
444 AND AIP.Invoice_ID = p_inv_rec.invoice_id
445 AND AIP.Check_ID = APH.Check_ID
446 AND APH.Payment_History_ID = APHD.Payment_History_ID
447 AND APH.Transaction_Type IN ('PAYMENT CLEARING', 'PAYMENT UNCLEARING',
448 'PAYMENT CLEARING ADJUSTED');
449
450 ELSIF (p_xla_event_rec.event_type_code IN
451 ('PAYMENT MATURED', 'PAYMENT MATURITY ADJUSTED')) THEN
452
453 -- Bug 7138115. Added additional join conditions to improve performance
454 SELECT SUM(--DECODE(APHD.Pay_Dist_Lookup_Code,
455 -- 'EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
456 -- 'TAX EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
457 APHD.Invoice_Dist_Base_Amount)
458 INTO l_sum_pay_dist_base_amt
459 FROM AP_Payment_Hist_Dists APHD,
460 AP_Payment_History_All APH,
461 AP_Invoice_Payments_All AIP
462 WHERE ((APHD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
463 AND APHD.Pay_Dist_Lookup_Code IN
464 ('CASH', 'DISCOUNT', 'FINAL PAYMENT ROUNDING' ))
465 OR (APHD.Pay_Dist_Lookup_Code='AWT'
466 AND APHD.AWT_Related_ID = l_inv_dist_rec.invoice_distribution_id))
467 AND AIP.Invoice_ID = p_inv_rec.invoice_id
468 AND AIP.Check_ID = APH.Check_ID
469 AND APH.Payment_History_ID = APHD.Payment_History_ID
470 AND APH.Transaction_Type IN ('PAYMENT MATURITY', 'PAYMENT MATURITY REVERSED',
471 'PAYMENT MATURITY ADJUSTED');
472
473 ELSE
474 -- bug 5570002 modified need to consider Tax erv
475 -- Bug 7138115. Added additional join conditions to improve performance
476 SELECT SUM(--DECODE(APHD.Pay_Dist_Lookup_Code,
477 -- 'EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
478 -- 'TAX EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
479 APHD.Invoice_Dist_Base_Amount)
480 INTO l_sum_pay_dist_base_amt
481 FROM AP_Payment_Hist_Dists APHD,
482 AP_Payment_History_All APH,
483 AP_Invoice_Payments_All AIP
484 WHERE ((APHD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
485 AND APHD.Pay_Dist_Lookup_Code IN
486 ('CASH', 'DISCOUNT', 'FINAL PAYMENT ROUNDING' ))
487 OR (APHD.Pay_Dist_Lookup_Code='AWT'
488 AND APHD.AWT_Related_ID = l_inv_dist_rec.invoice_distribution_id))
489 AND AIP.Invoice_ID = p_inv_rec.invoice_id
490 AND AIP.Check_ID = APH.Check_ID
491 AND APH.Payment_History_ID = APHD.Payment_History_ID
492 AND APH.Transaction_Type IN ('PAYMENT CREATED', 'PAYMENT CANCELLED', 'PAYMENT ADJUSTED',
493 'MANUAL PAYMENT ADJUSTED', 'UPGRADED MANUAL PMT ADJUSTED',
494 'REFUND RECORDED', 'REFUND ADJUSTED', 'REFUND CANCELLED');
495
496 END IF;
497
498 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
499 l_log_msg := 'Value of l_sum_pay_dist_base_amt = '||l_sum_pay_dist_base_amt;
500 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
501 END IF;
502
503 -- Bug 7138115. Added additional join conditions to improve performance
504 SELECT SUM(APAD.Base_Amount)
505 INTO l_sum_prepay_base_amt
506 FROM AP_Prepay_App_Dists APAD,
507 AP_Prepay_History_All APH
508 WHERE ((APAD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
509 AND APAD.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
510 'PREPAY APPL NONREC TAX'))
511 OR (APAD.AWT_Related_ID = l_inv_dist_rec.invoice_distribution_id
512 AND APAD.Prepay_Dist_Lookup_Code = 'AWT'))
513 AND APH.Invoice_ID = p_inv_rec.invoice_id
514 AND APH.Prepay_History_ID = APAD.Prepay_History_ID;
515
516 l_total_dist_base_amt := NVL(l_sum_pay_dist_base_amt,0) - NVL(l_sum_prepay_base_amt,0);
517
518 -- Check if the total of the accounted base amounts is equal to the base amount
519 -- for the distribution. If not create a final payment rounding to relieve
520 -- the liability completely.
521
522 ---------------------------------------------------------------------------------
523 -- bug 5570002
524 -- need to find the ERV/TERV amount of the invoice distribution and
525 -- exclude them from the invoide distrbution base amount
526 ---------------------------------------------------------------------------------
527
528 IF ( l_inv_dist_rec.line_type_lookup_code in ('ITEM', 'NONREC_TAX','ACCRUAL')
529 AND l_inv_dist_rec.related_id is not NULL ) THEN
530 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
531 l_log_msg := 'possible erv exists for invoice dist type =' ||
532 l_inv_dist_rec.line_type_lookup_code;
533 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
534 END IF;
535
536 BEGIN
537
538 SELECT NVL(base_amount, 0)
539 INTO l_erv_base_amount
540 FROM ap_invoice_distributions_all AID
541 WHERE AID.line_type_lookup_code in ('ERV', 'TERV')
542 AND AID.invoice_id = l_inv_dist_rec.invoice_id
543 AND AID.related_id = l_inv_dist_rec.invoice_distribution_id;
544 EXCEPTION
545 WHEN OTHERS THEN
546 l_erv_base_amount := 0;
547
548 END;
549
550 END IF;
551
552 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
553 l_log_msg := 'l_inv_dist_rec.base_amount = ' ||
554 l_inv_dist_rec.base_amount ||
555 'invoice dist ERV Amount = ' ||
556 l_erv_base_amount ||
557 'and l_total_dist_base_amt' ||
558 l_total_dist_base_amt;
559 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
560 END IF;
561
562
563 IF ( (l_inv_dist_rec.base_amount + l_erv_base_amount)
564 <> l_total_dist_base_amt) THEN
565
566 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
567 l_log_msg := 'Total of accounted base amt is not equal to base amount.'
568 || 'Creating a final payment rounding';
569 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
570 END IF;
571
572
573 /* If this is a prepayment type of event then insert the rounding distribution into
574 prepayment dists table. Otherwise insert into payment dists table */
575 IF (p_prepay_dist_rec.invoice_distribution_id IS NOT NULL) THEN
576
577 -- Get the prepay appl pay dists info for this distribution
578 SELECT APAD.*
579 INTO l_max_prepay_rec
580 FROM AP_Prepay_App_Dists APAD
581 WHERE APAD.Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id
582 AND APAD.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
583 AND APAD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
584 AND Rownum = 1;
585
586 l_pad_rec.prepay_history_id := p_prepay_hist_rec.prepay_history_id;
587 l_pad_rec.accounting_event_id := p_xla_event_rec.event_id;
588 l_pad_rec.invoice_distribution_id := l_inv_dist_rec.invoice_distribution_id;
589 l_pad_rec.prepay_app_distribution_id := p_prepay_dist_rec.invoice_distribution_id;
590 l_pad_rec.prepay_dist_lookup_code := 'FINAL PAYMENT ROUNDING';
591
592 l_pad_rec.prepay_exchange_rate := l_max_prepay_rec.prepay_exchange_rate;
593 l_pad_rec.prepay_exchange_rate_type := l_max_prepay_rec.prepay_exchange_rate_type;
594 l_pad_rec.prepay_exchange_date := l_max_prepay_rec.prepay_exchange_date;
595 l_pad_rec.prepay_pay_exchange_rate := l_max_prepay_rec.prepay_pay_exchange_rate;
596 l_pad_rec.prepay_pay_exchange_rate_type := l_max_prepay_rec.prepay_pay_exchange_rate_type;
597 l_pad_rec.prepay_pay_exchange_date := l_max_prepay_rec.prepay_pay_exchange_date;
598 l_pad_rec.prepay_clr_exchange_rate := l_max_prepay_rec.prepay_clr_exchange_rate;
599 l_pad_rec.prepay_clr_exchange_rate_type := l_max_prepay_rec.prepay_clr_exchange_rate_type;
600 l_pad_rec.prepay_clr_exchange_date := l_max_prepay_rec.prepay_clr_exchange_date;
601 l_pad_rec.awt_related_id := l_max_prepay_rec.awt_related_id;
602
603 l_pad_rec.amount := 0;
604 l_pad_rec.base_amount := l_inv_dist_rec.base_amount - l_total_dist_base_amt;
605 l_pad_rec.base_amt_at_prepay_xrate := 0;
606 l_pad_rec.base_amt_at_prepay_pay_xrate := 0;
607 l_pad_rec.base_amt_at_prepay_clr_xrate := 0;
608
609
610 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
611 l_log_msg := 'Calling procedure '||
612 'AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Insert';
613 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
614 END IF;
615
616 AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Insert
617 (l_pad_rec,
618 l_curr_calling_sequence);
619
620 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
621 l_log_msg := 'Procedure AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Insert '
622 || 'executed';
623 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
624 END IF;
625
626 ELSE
627
628 -- Get the payment hist info
629 SELECT APHD.*
630 INTO l_max_pay_rec
631 FROM AP_Payment_Hist_Dists APHD
632 WHERE APHD.Payment_History_ID = p_pay_hist_rec.payment_history_id
633 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
634 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
635 AND APHD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
636 AND Rownum = 1;
637
638 l_pd_rec.invoice_distribution_id := l_inv_dist_rec.invoice_distribution_id;
639 l_pd_rec.payment_history_id := l_max_pay_rec.payment_history_id;
640 l_pd_rec.invoice_payment_id := l_max_pay_rec.invoice_payment_id;
641 l_pd_rec.invoice_adjustment_event_id := l_max_pay_rec.invoice_adjustment_event_id;
642 l_pd_rec.accounting_event_id := p_xla_event_rec.event_id;
643 l_pd_rec.pay_dist_lookup_code := 'FINAL PAYMENT ROUNDING';
644 l_pd_rec.awt_related_id := l_max_pay_rec.awt_related_id;
645
646 l_pd_rec.bank_curr_amount := 0;
647 l_pd_rec.invoice_dist_base_amount := l_inv_dist_rec.base_amount - l_total_dist_base_amt;
648 l_pd_rec.amount := 0;
649 l_pd_rec.invoice_dist_amount := 0;
650 l_pd_rec.paid_base_amount := 0;
651 l_pd_rec.cleared_base_amount := 0;
652 l_pd_rec.matured_base_amount := 0;
653
654 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
655 l_log_msg := 'Calling procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert';
656 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
657 END IF;
658
659
660 AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert
661 (l_pd_rec,
662 l_curr_calling_sequence);
663
664 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
665 l_log_msg := 'Procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert executed';
666 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
667 END IF;
668
669
670 END IF;
671 END IF;
672 END IF;
673 END LOOP;
674
675 -- Logging Infra: Procedure level
676 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
677 l_log_msg := 'End of procedure '|| l_procedure_name;
678 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
679 END IF;
680
681
682 EXCEPTION
683 WHEN OTHERS THEN
684 IF (SQLCODE <> -20001) THEN
685 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
686 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
687 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
688 END IF;
689 APP_EXCEPTION.RAISE_EXCEPTION;
690
691 END Final_Pay;
692
693
694 -------------------------------------------------------------------------------
695 -- PROCEDURE Total_Pay
696 -- This procedure calculates whether the payment his distribution records for
697 -- the event fully relieve the different base amounts and then create the
698 -- total payment rounding if the amounts are not fully relieved
699 --
700 --------------------------------------------------------------------------------
701 PROCEDURE Total_Pay
702 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
703 ,P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
704 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
705 ,P_Inv_Pay_Rec IN ap_acctg_pay_dist_pkg.r_inv_pay_info
706 ,P_Calling_Sequence IN VARCHAR2
707 ) IS
708
709 l_curr_calling_sequence VARCHAR2(2000);
710 l_payment_hist_id NUMBER;
711 l_invoice_dist_id NUMBER;
712 l_inv_adj_event_id NUMBER;
713
714 l_inv_rate_total_amt NUMBER := 0;
715 l_pay_rate_total_amt NUMBER := 0;
716 l_clr_rate_total_amt NUMBER := 0;
717 l_mat_rate_total_amt NUMBER := 0;
718 l_disc_pay_rate_total_amt NUMBER := 0;
719 l_disc_clr_rate_total_amt NUMBER := 0;
720 l_err_clr_rate_total_amt NUMBER := 0;
721 l_chrg_clr_rate_total_amt NUMBER := 0;
722
723 l_inv_rate_sum_amt NUMBER := 0;
724 l_pay_rate_sum_amt NUMBER := 0;
725 l_clr_rate_sum_amt NUMBER := 0;
726 l_mat_rate_sum_amt NUMBER := 0;
727 l_disc_pay_rate_sum_amt NUMBER := 0;
728 l_disc_clr_rate_sum_amt NUMBER := 0;
729 l_err_clr_rate_sum_amt NUMBER := 0;
730 l_chrg_clr_rate_sum_amt NUMBER := 0;
731
732 l_inv_rate_diff_amt NUMBER := 0;
733 l_pay_rate_diff_amt NUMBER := 0;
734 l_clr_rate_diff_amt NUMBER := 0;
735 l_mat_rate_diff_amt NUMBER := 0;
736 l_disc_pay_rate_diff_amt NUMBER := 0;
737 l_disc_clr_rate_diff_amt NUMBER := 0;
738 l_err_clr_rate_diff_amt NUMBER := 0;
739 l_chrg_clr_rate_diff_amt NUMBER := 0;
740
741 l_pd_rec AP_PAYMENT_HIST_DISTS%ROWTYPE;
742
743 -- Logging Infra:
744 l_procedure_name CONSTANT VARCHAR2(30) := 'Total_Pay';
745 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
746 l_inv_base_amt NUMBER := 0;--Bug6600117
747 l_inv_dist_diff_amt NUMBER := 0;
748 l_inv_amt NUMBER := 0;
749 l_pay_sum_amt NUMBER := 0;
750 l_inv_rate_sum_full_amt NUMBER := 0;
751 l_max_dist_id NUMBER;
752
753
754 BEGIN
755
756 l_curr_calling_sequence := 'AP_ACCTG_PAY_ROUND_PKG.Total_Pay<- ' ||
757 p_calling_sequence;
758
759 -- Logging Infra: Procedure level
760 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
761 l_log_msg := 'Begin of procedure '|| l_procedure_name;
762 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
763 END IF;
764
765
766 -- Get the max of the largest distribution for inserting the
767 -- total payment rounding
768 SELECT APHD.Payment_History_ID,
769 APHD.Invoice_Distribution_ID,
770 APHD.Invoice_Adjustment_Event_ID
771 INTO l_payment_hist_id,
772 l_invoice_dist_id,
773 l_inv_adj_event_id
774 FROM AP_Payment_Hist_Dists APHD
775 WHERE APHD.Invoice_Distribution_ID =
776 (SELECT MAX(APHD1.Invoice_Distribution_ID)
777 FROM AP_Payment_Hist_Dists APHD1
778 WHERE APHD1.Accounting_Event_ID = p_xla_event_rec.event_id
779 AND APHD1.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
780 AND APHD1.Invoice_Distribution_ID IN
781 (SELECT AID.Invoice_Distribution_ID
782 FROM AP_Invoice_Distributions_All AID
783 WHERE AID.Invoice_ID = p_inv_rec.invoice_id)
784 AND ABS(APHD1.Amount) =
785 (SELECT MAX(ABS(APHD2.Amount))
786 FROM AP_Payment_Hist_Dists APHD2
787 WHERE APHD2.Accounting_Event_ID = p_xla_event_rec.event_id
788 AND APHD2.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
789 AND APHD2.Invoice_Distribution_ID IN
790 (SELECT AID.Invoice_Distribution_ID
791 FROM AP_Invoice_Distributions_All AID
792 WHERE AID.Invoice_ID = p_inv_rec.invoice_id)))
793 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
794 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
795 AND Rownum = 1;
796
797
798
799 l_pay_rate_total_amt := p_inv_pay_rec.payment_base_amount;
800 l_inv_rate_total_amt := p_inv_pay_rec.invoice_base_amount;
801
802 IF p_inv_pay_rec.discount_taken <> 0 THEN
803
804 l_disc_pay_rate_total_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
805 (p_inv_pay_rec.discount_taken,
806 p_pay_hist_rec.pmt_currency_code,
807 ap_accounting_pay_pkg.g_base_currency_code,
808 p_pay_hist_rec.pmt_to_base_xrate_type,
809 p_pay_hist_rec.pmt_to_base_xrate_date,
810 p_pay_hist_rec.pmt_to_base_xrate,
811 l_curr_calling_sequence);
812
813 END IF;
814
815 -- Get the sum of the base amounts for each line type from the payment hist dists.
816 SELECT SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'CASH', APHD.Invoice_Dist_Base_Amount, 0)),
817 SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'CASH', APHD.Paid_Base_Amount, 0)),
818 SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'CASH', APHD.Cleared_Base_Amount, 0)),
819 SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'CASH', APHD.Matured_Base_Amount, 0)),
820 SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'DISCOUNT', APHD.Paid_Base_Amount, 0)),
821 SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'DISCOUNT', APHD.Cleared_Base_Amount, 0)),
822 SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'BANK ERROR', APHD.Cleared_Base_Amount, 0)),
823 SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'BANK CHARGE', APHD.Cleared_Base_Amount, 0))
824 INTO l_inv_rate_sum_amt,
825 l_pay_rate_sum_amt,
826 l_clr_rate_sum_amt,
827 l_mat_rate_sum_amt,
828 l_disc_pay_rate_sum_amt,
829 l_disc_clr_rate_sum_amt,
830 l_err_clr_rate_sum_amt,
831 l_chrg_clr_rate_sum_amt
832 FROM AP_Payment_Hist_Dists APHD,
833 AP_Payment_History_All APH
834 WHERE APH.Related_Event_ID = p_pay_hist_rec.Related_Event_ID
835 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.Invoice_Payment_ID
836 AND APHD.Payment_History_ID = APH.Payment_History_ID
837 AND APHD.Invoice_Distribution_ID IN
838 (SELECT AID.Invoice_Distribution_ID
839 FROM AP_Invoice_Distributions_All AID
840 WHERE AID.Invoice_ID = p_inv_rec.invoice_id);
841
842
843 /*Bug6600117 used below query to fetch the sum(amount) and sum(base_amount)
844 from ap_payment_history and AP_Payment_Hist_dists tables to be
845 used later in the calculation*/
846 -- Bug 6649025
847 SELECT sum(nvl(APHD.amount,0)),
848 max(APHD.Invoice_Distribution_Id)
849 ,SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'CASH', APHD.Invoice_Dist_Base_Amount,
850 'AWT', APHD.Invoice_Dist_Base_Amount,
851 'DISCOUNT', APHD.Invoice_Dist_Base_Amount, 0))
852 INTO l_pay_sum_amt,l_max_dist_id,l_inv_rate_sum_full_amt
853 FROM AP_PAYMENT_HIST_DISTS APHD,
854 AP_PAYMENT_HISTORY_ALL APH
855 WHERE
856 APHD.PAYMENT_HISTORY_ID =APH.PAYMENT_HISTORY_ID
857 ANd APHD.INVOICE_PAYMENT_ID =p_inv_pay_rec.Invoice_Payment_ID --6614295
858 AND APHD.Invoice_Distribution_ID IN
859 (SELECT AID.Invoice_Distribution_ID
860 FROM AP_Invoice_Distributions_All AID
861 WHERE AID.Invoice_ID = p_inv_rec.invoice_id);
862
863 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
864 l_log_msg := 'l_inv_rate_sum_full_amt ' || l_inv_rate_sum_full_amt;
865 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
866 END IF;
867
868 SELECT sum(AI.invoice_amount) ,sum(AI.base_amount)
869 INTO l_inv_amt,l_inv_base_amt
870 FROM ap_invoices_all AI
871 WHERE AI.invoice_id = p_inv_rec.invoice_id;
872
873 IF (p_xla_event_rec.event_type_code IN ('PAYMENT CLEARED',
874 'PAYMENT CLEARING ADJUSTED')) THEN
875
876 l_clr_rate_total_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
877 (p_inv_pay_rec.amount,
878 p_pay_hist_rec.pmt_currency_code,
879 ap_accounting_pay_pkg.g_base_currency_code,
880 p_pay_hist_rec.pmt_to_base_xrate_type,
881 p_pay_hist_rec.pmt_to_base_xrate_date,
882 p_pay_hist_rec.pmt_to_base_xrate,
883 l_curr_calling_sequence);
884
885
886 IF p_inv_pay_rec.discount_taken <> 0 THEN
887
888 l_disc_clr_rate_total_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
889 (p_inv_pay_rec.discount_taken,
890 p_pay_hist_rec.pmt_currency_code,
891 ap_accounting_pay_pkg.g_base_currency_code,
892 p_pay_hist_rec.pmt_to_base_xrate_type,
893 p_pay_hist_rec.pmt_to_base_xrate_date,
894 p_pay_hist_rec.pmt_to_base_xrate,
895 l_curr_calling_sequence);
896 END IF;
897
898 IF p_pay_hist_rec.errors_bank_amount <> 0 THEN
899
900 l_err_clr_rate_total_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
901 (p_pay_hist_rec.errors_bank_amount,
902 p_pay_hist_rec.pmt_currency_code,
903 ap_accounting_pay_pkg.g_base_currency_code,
904 p_pay_hist_rec.pmt_to_base_xrate_type,
905 p_pay_hist_rec.pmt_to_base_xrate_date,
906 p_pay_hist_rec.pmt_to_base_xrate,
907 l_curr_calling_sequence);
908 END IF;
909
910 IF p_pay_hist_rec.charges_bank_amount <> 0 THEN
911
912 l_chrg_clr_rate_total_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
913 (p_pay_hist_rec.charges_bank_amount,
914 p_pay_hist_rec.pmt_currency_code,
915 ap_accounting_pay_pkg.g_base_currency_code,
916 p_pay_hist_rec.pmt_to_base_xrate_type,
917 p_pay_hist_rec.pmt_to_base_xrate_date,
918 p_pay_hist_rec.pmt_to_base_xrate,
919 l_curr_calling_sequence);
920
921 END IF;
922
923 ELSIF (p_xla_event_rec.event_type_code IN ('PAYMENT MATURED',
924 'PAYMENT MATURITY ADJUSTED')) THEN
925
926 l_mat_rate_total_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
927 (p_inv_pay_rec.amount,
928 p_pay_hist_rec.pmt_currency_code,
929 ap_accounting_pay_pkg.g_base_currency_code,
930 p_pay_hist_rec.pmt_to_base_xrate_type,
931 p_pay_hist_rec.pmt_to_base_xrate_date,
932 p_pay_hist_rec.pmt_to_base_xrate,
933 l_curr_calling_sequence);
934
935 ELSE
936
937 l_pay_rate_total_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
938 (p_inv_pay_rec.amount,
939 p_pay_hist_rec.pmt_currency_code,
940 ap_accounting_pay_pkg.g_base_currency_code,
941 p_pay_hist_rec.pmt_to_base_xrate_type,
942 p_pay_hist_rec.pmt_to_base_xrate_date,
943 p_pay_hist_rec.pmt_to_base_xrate,
944 l_curr_calling_sequence);
945
946 IF p_inv_pay_rec.discount_taken <> 0 THEN
947
948 l_disc_pay_rate_total_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
949 (p_inv_pay_rec.discount_taken,
950 p_pay_hist_rec.pmt_currency_code,
951 ap_accounting_pay_pkg.g_base_currency_code,
952 p_pay_hist_rec.pmt_to_base_xrate_type,
953 p_pay_hist_rec.pmt_to_base_xrate_date,
954 p_pay_hist_rec.pmt_to_base_xrate,
955 l_curr_calling_sequence);
956
957 END IF;
958
959 END IF;
960
961
962 l_pay_rate_diff_amt := l_pay_rate_total_amt - l_pay_rate_sum_amt;
963
964 /* If the exchange rates between the invoice and payment are same then the base
965 amounts and rounding between the invoice and payment should be same */
966 /*Bug6600117
967 The fractional rounding amount is added to the maximun distribution amount
968 in ap_invoice_distributions_all to balance them in respect to the header base amount.
969 Same is not done while calculating invoice_dist_Base_Amount in AP_Payment_Hist_Dists
970 table.This is giving rise to the unbalance accounting entries in accounting journal*/
971
972 IF (p_pay_hist_rec.pmt_to_base_xrate =
973 p_inv_rec.exchange_rate / p_inv_rec.payment_cross_rate) THEN
974 l_inv_rate_diff_amt := l_pay_rate_diff_amt;
975 ELSE
976 l_inv_rate_diff_amt := l_inv_rate_total_amt - l_inv_rate_sum_amt;
977
978 END IF;
979
980 /* Bug660017calculate the difference between AP_invoice_distribution
981 base amount sum and AP_Payment_Hist_Dists base amount sum */
982 l_inv_dist_diff_amt := l_inv_base_amt - l_inv_rate_sum_full_amt;
983
984 --Bug6600117
985
986 --If invoice is fully paid and there is fractional unbalance
987 IF (l_pay_sum_amt = l_inv_amt) and NVL(l_inv_dist_diff_amt,0) <> 0 THEN
988 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
989 l_log_msg := 'Updating rounding amount for l_inv_dist_diff_amt';
990 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
991 END IF;
992
993 /* Bug660017 If the invoice is paid in full by this payment and there is a
994 difference of amount then adjust the maximum distribution with the fractional
995 amount*/
996 -- Bug 6649025
997
998 --Bug 7270829 - the same update has been done below , hence commenting out this code.
999 /* UPDATE AP_Payment_Hist_Dists APHD
1000 SET APHD.invoice_dist_Base_Amount = APHD.invoice_dist_Base_Amount + NVL(l_inv_dist_diff_amt,0)
1001 WHERE APHD.Invoice_Distribution_ID = l_invoice_dist_id -- l_max_dist_id
1002 AND APHD.Pay_Dist_Lookup_Code IN ('CASH')
1003 AND APHD.Payment_History_ID = l_payment_hist_id
1004 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1005 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1006 AND APHD.PAYMENT_HIST_DIST_ID= (select max(APHD1.PAYMENT_HIST_DIST_ID)
1007 from AP_Payment_Hist_Dists APHD1
1008 where APHD1.invoice_distribution_id = l_invoice_dist_id); --l_max_dist_id);
1009 */
1010 --Bug 7270829
1011
1012 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1013 l_log_msg := 'Updated rounding amount for l_pay_rate_diff_amt';
1014 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1015 END IF;
1016 END IF;
1017
1018 /* If there is a difference between the total and sum amounts then we will insert
1019 the difference as the rounding amounts */
1020
1021 l_clr_rate_diff_amt := l_clr_rate_total_amt - l_clr_rate_sum_amt;
1022 l_mat_rate_diff_amt := l_mat_rate_total_amt - l_mat_rate_sum_amt;
1023 l_disc_pay_rate_diff_amt := l_disc_pay_rate_total_amt - l_disc_pay_rate_sum_amt;
1024 l_disc_clr_rate_diff_amt := l_disc_clr_rate_total_amt - l_disc_clr_rate_sum_amt;
1025 l_err_clr_rate_diff_amt := l_err_clr_rate_total_amt - l_err_clr_rate_sum_amt;
1026 l_chrg_clr_rate_diff_amt := l_chrg_clr_rate_total_amt - l_chrg_clr_rate_sum_amt;
1027
1028 -- Bug fix 6314128 Starts
1029 -- Handling the difference amount in l_inv_rate_diff_amt
1030 -- Rule: SUM(AP_PAYMENT_HIST_DISTS.INVOICE_DIST_BASE_AMOUNT) =
1031 -- AP_INVOICE_PAYMENTS_ALL.INVOICE_BASE_AMOUNT
1032
1033 IF NVL(l_inv_rate_diff_amt,0) <> 0 THEN
1034
1035 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1036 l_log_msg := 'Updating rounding amount for l_inv_rate_diff_amt';
1037 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1038 END IF;
1039
1040 UPDATE AP_Payment_Hist_Dists APHD
1041 SET APHD.Invoice_Dist_Base_Amount =
1042 APHD.Invoice_Dist_Base_Amount + NVL(l_inv_rate_diff_amt,0),
1043 APHD.Rounding_Amt = l_inv_rate_diff_amt
1044 WHERE APHD.Payment_History_ID = l_payment_hist_id
1045 AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
1046 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1047 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1048 AND APHD.Pay_Dist_Lookup_Code = 'CASH';
1049
1050 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1051 l_log_msg := 'Updated rounding amount for l_inv_rate_diff_amt';
1052 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1053 END IF;
1054
1055
1056 END IF;
1057 -- BUG 6314128 ENDS;
1058
1059 -- for bug fix 5694577
1060 -- Added the event_type_code chack and rearranged the if statements
1061 IF NVL(l_clr_rate_diff_amt,0) <> 0 AND
1062 (p_xla_event_rec.event_type_code IN ('PAYMENT CLEARED',
1063 'PAYMENT CLEARING ADJUSTED'))
1064 THEN
1065
1066 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1067 l_log_msg := 'Updating rounding amount for l_clr_rate_diff_amt';
1068 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1069 END IF;
1070
1071
1072 UPDATE AP_Payment_Hist_Dists APHD
1073 SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount
1074 + NVL(l_clr_rate_diff_amt,0),
1075 APHD.Rounding_Amt = l_clr_rate_diff_amt
1076 WHERE APHD.Payment_History_ID = l_payment_hist_id
1077 AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
1078 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1079 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1080 AND APHD.Pay_Dist_Lookup_Code = 'CASH';
1081
1082
1083 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1084 l_log_msg := 'Updated rounding amount for l_clr_rate_diff_amt';
1085 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1086 END IF;
1087
1088 ELSIF NVL(l_mat_rate_diff_amt,0) <> 0 AND
1089 (p_xla_event_rec.event_type_code IN ('PAYMENT MATURED',
1090 'PAYMENT MATURITY ADJUSTED'))
1091 THEN
1092
1093 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1094 l_log_msg := 'Updating rounding amount for l_mat_rate_diff_amt';
1095 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1096 END IF;
1097
1098 UPDATE AP_Payment_Hist_Dists APHD
1099 SET APHD.Matured_Base_Amount = APHD.Matured_Base_Amount
1100 + NVL(l_mat_rate_diff_amt,0),
1101 APHD.Rounding_Amt = l_mat_rate_diff_amt
1102 WHERE APHD.Payment_History_ID = l_payment_hist_id
1103 AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
1104 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1105 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1106 AND APHD.Pay_Dist_Lookup_Code = 'CASH';
1107
1108
1109 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1110 l_log_msg := 'Updated rounding amount for l_mat_rate_diff_amt';
1111 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1112 END IF;
1113
1114 ELSIF NVL(l_pay_rate_diff_amt,0) <> 0 THEN
1115
1116 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1117 l_log_msg := 'Updating rounding amount for l_pay_rate_diff_amt';
1118 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1119 END IF;
1120
1121
1122 UPDATE AP_Payment_Hist_Dists APHD
1123 SET APHD.Paid_Base_Amount = APHD.Paid_Base_Amount + NVL(l_pay_rate_diff_amt,0),
1124 APHD.Rounding_Amt = l_pay_rate_diff_amt
1125 WHERE APHD.Payment_History_ID = l_payment_hist_id
1126 AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
1127 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1128 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1129 AND APHD.Pay_Dist_Lookup_Code = 'CASH';
1130
1131
1132 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1133 l_log_msg := 'Updated rounding amount for l_pay_rate_diff_amt';
1134 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1135 END IF;
1136
1137 END IF;
1138
1139 IF NVL(l_disc_pay_rate_diff_amt,0) <> 0 THEN
1140
1141 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1142 l_log_msg := 'Updating discount rounding amount for payment';
1143 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1144 END IF;
1145
1146
1147 UPDATE AP_Payment_Hist_Dists APHD
1148 SET APHD.Paid_Base_Amount = APHD.Paid_Base_Amount
1149 + NVL(l_disc_pay_rate_diff_amt,0),
1150 APHD.Rounding_Amt = l_disc_pay_rate_diff_amt
1151 WHERE APHD.Payment_History_ID = l_payment_hist_id
1152 AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
1153 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1154 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1155 AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
1156
1157
1158 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1159 l_log_msg := 'Updated discount rounding amount for payment';
1160 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1161 END IF;
1162
1163
1164 END IF;
1165
1166 IF NVL(l_disc_clr_rate_diff_amt,0) <> 0 THEN
1167
1168 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1169 l_log_msg := 'Updating discount rounding amount for clearing';
1170 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1171 END IF;
1172
1173
1174 UPDATE AP_Payment_Hist_Dists APHD
1175 SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount
1176 + NVL(l_disc_clr_rate_diff_amt,0),
1177 APHD.Rounding_Amt = l_disc_clr_rate_diff_amt
1178 WHERE APHD.Payment_History_ID = l_payment_hist_id
1179 AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
1180 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1181 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1182 AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
1183
1184
1185 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1186 l_log_msg := 'Updated discount rounding amount for clearing';
1187 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1188 END IF;
1189
1190
1191 END IF;
1192
1193
1194 IF NVL(l_err_clr_rate_diff_amt,0) <> 0 THEN
1195
1196 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1197 l_log_msg := 'Updating error rounding amount';
1198 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1199 END IF;
1200
1201
1202 UPDATE AP_Payment_Hist_Dists APHD
1203 SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount
1204 + NVL(l_err_clr_rate_diff_amt,0),
1205 APHD.Rounding_Amt = l_err_clr_rate_diff_amt
1206 WHERE APHD.Payment_History_ID = l_payment_hist_id
1207 AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
1208 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1209 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1210 AND APHD.Pay_Dist_Lookup_Code = 'BANK ERROR';
1211
1212
1213 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1214 l_log_msg := 'Updated error rounding amount';
1215 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1216 END IF;
1217
1218
1219 END IF;
1220
1221
1222 IF NVL(l_chrg_clr_rate_diff_amt,0) <> 0 THEN
1223
1224 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1225 l_log_msg := 'Updating charge rounding amount';
1226 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1227 END IF;
1228
1229
1230 UPDATE AP_Payment_Hist_Dists APHD
1231 SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount
1232 + NVL(l_chrg_clr_rate_diff_amt,0),
1233 APHD.Rounding_Amt = l_chrg_clr_rate_diff_amt
1234 WHERE APHD.Payment_History_ID = l_payment_hist_id
1235 AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
1236 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1237 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1238 AND APHD.Pay_Dist_Lookup_Code = 'BANK CHARGE';
1239
1240
1241 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1242 l_log_msg := 'Updated charge rounding amount';
1243 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1244 END IF;
1245
1246
1247 END IF;
1248
1249 -- Logging Infra: Procedure level
1250 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1251 l_log_msg := 'End of procedure '|| l_procedure_name;
1252 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
1253 END IF;
1254
1255
1256 EXCEPTION
1257 WHEN OTHERS THEN
1258 IF (SQLCODE <> -20001) THEN
1259 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1260 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1261 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1262 END IF;
1263 APP_EXCEPTION.RAISE_EXCEPTION;
1264
1265 END Total_Pay;
1266
1267
1268
1269 -------------------------------------------------------------------------------
1270 -- PROCEDURE Compare_Pay
1271 -- This procedure calculates the rounding amount needed to relieve base
1272 -- amounts between events. The following types of rounding will be calculated:
1273 --
1274 -- PAYMENT TO MATURITY ROUNDING
1275 -- PAYMENT TO CLEARING ROUNDING
1276 -- MATURITY TO CLEARING ROUNDING
1277 --
1278 --------------------------------------------------------------------------------
1279 PROCEDURE Compare_Pay
1280 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
1281 ,P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
1282 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
1283 ,P_Inv_Pay_Rec IN ap_acctg_pay_dist_pkg.r_inv_pay_info
1284 ,P_Calling_Sequence IN VARCHAR2
1285 ) IS
1286
1287 l_curr_calling_sequence VARCHAR2(2000);
1288 l_sum_pay_paid_base_amt NUMBER;
1289 l_sum_mat_paid_base_amt NUMBER;
1290 l_sum_clr_paid_base_amt NUMBER;
1291 l_sum_mat_mat_base_amt NUMBER;
1292 l_sum_clr_mat_base_amt NUMBER;
1293
1294 l_diff_mat_paid_base_amt NUMBER;
1295 l_diff_clr_paid_base_amt NUMBER;
1296 l_diff_clr_mat_base_amt NUMBER;
1297
1298
1299 l_max_pd_rec AP_PAYMENT_HIST_DISTS%ROWTYPE;
1300 l_pd_rec AP_PAYMENT_HIST_DISTS%ROWTYPE;
1301
1302 -- Logging Infra:
1303 l_procedure_name CONSTANT VARCHAR2(30) := 'Compare_Pay';
1304 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1305
1306 BEGIN
1307
1308 l_curr_calling_sequence := 'AP_ACCTG_PAY_ROUND_PKG.Compare_Pay<- ' ||
1309 P_Calling_Sequence;
1310
1311
1312 -- Logging Infra: Procedure level
1313 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1314 l_log_msg := 'Begin of procedure '|| l_procedure_name;
1315 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1316 END IF;
1317
1318
1319 -- Getting the max of the largest distribution for inserting the rounding
1320 -- distribution
1321 SELECT APHD.*
1322 INTO l_max_pd_rec
1323 FROM AP_Payment_Hist_Dists APHD
1324 WHERE APHD.Invoice_Distribution_ID =
1325 (SELECT MAX(APHD1.Invoice_Distribution_ID)
1326 FROM AP_Payment_Hist_Dists APHD1
1327 WHERE APHD1.Accounting_Event_ID = p_xla_event_rec.event_id
1328 AND APHD1.Invoice_Distribution_ID IN
1329 (SELECT AID.Invoice_Distribution_ID
1330 FROM AP_Invoice_Distributions_All AID
1331 WHERE AID.Invoice_ID = p_inv_rec.invoice_id)
1332 AND ABS(APHD1.Amount) =
1333 (SELECT MAX(ABS(APHD2.Amount))
1334 FROM AP_Payment_Hist_Dists APHD2
1335 WHERE APHD2.Accounting_Event_ID = p_xla_event_rec.event_id
1336 AND APHD2.Invoice_Distribution_ID IN
1337 (SELECT AID.Invoice_Distribution_ID
1338 FROM AP_Invoice_Distributions_All AID
1339 WHERE AID.Invoice_ID = p_inv_rec.invoice_id)))
1340 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1341 AND Rownum = 1;
1342
1343
1344 -- Get the paid base amounts for the payment event
1345 SELECT SUM(APHD.Paid_Base_Amount)
1346 INTO l_sum_pay_paid_base_amt
1347 FROM AP_Payment_Hist_Dists APHD,
1348 AP_Payment_History_All APH,
1349 AP_Payment_History_All APH1
1350 WHERE APH1.Payment_History_ID = ap_accounting_pay_pkg.g_pay_pmt_history_id
1351 AND APH.Related_Event_ID = APH1.Accounting_Event_ID
1352 AND APHD.Payment_History_ID = APH.Payment_History_ID
1353 AND APHD.Pay_Dist_Lookup_Code IN ('CASH')
1354 AND APHD.Invoice_Distribution_ID IN
1355 (SELECT AID.Invoice_Distribution_ID
1356 FROM AP_Invoice_Distributions_All AID
1357 WHERE AID.Invoice_ID = p_inv_rec.invoice_id);
1358
1359
1360 -- Get the paid base amounts for the payment maturity event
1361 SELECT SUM(APHD.Paid_Base_Amount),
1362 SUM(APHD.Matured_Base_Amount)
1363 INTO l_sum_mat_paid_base_amt,
1364 l_sum_mat_mat_base_amt
1365 FROM AP_Payment_Hist_Dists APHD,
1366 AP_Payment_History_All APH,
1367 AP_Payment_History_All APH1
1368 WHERE APH1.Payment_History_ID = ap_accounting_pay_pkg.g_mat_pmt_history_id
1369 AND APH.Related_Event_ID = APH1.Accounting_Event_ID
1370 AND APHD.Payment_History_ID = APH.Payment_History_ID
1371 AND APHD.Pay_Dist_Lookup_Code IN ('CASH')
1372 AND APHD.Invoice_Distribution_ID IN
1373 (SELECT AID.Invoice_Distribution_ID
1374 FROM AP_Invoice_Distributions_All AID
1375 WHERE AID.Invoice_ID = p_inv_rec.invoice_id);
1376
1377
1378 -- Get the paid base amounts for the payment clearing event
1379 -- Bug 6678474. Backing out the fix for bug 6621586 since it is not right fix.
1380 -- Here we are calculating the rounding difference for the same currency amounts
1381 -- but in prior events.
1382 -- For eg. any difference between the paid base amount in payment created event
1383 -- and payment cleared event will be calculated as Payment to Clearing rounding
1384 SELECT SUM(APHD.Paid_Base_Amount),
1385 SUM(APHD.Matured_Base_Amount)
1386 INTO l_sum_clr_paid_base_amt,
1387 l_sum_clr_mat_base_amt
1388 FROM AP_Payment_Hist_Dists APHD,
1389 AP_Payment_History_All APH,
1390 AP_Payment_History_All APH1
1391 WHERE APH1.Payment_History_ID = ap_accounting_pay_pkg.g_clr_pmt_history_id
1392 AND APH.Related_Event_ID = APH1.Accounting_Event_ID
1393 AND APHD.Payment_History_ID = APH.Payment_History_ID
1394 AND APHD.Pay_Dist_Lookup_Code IN ('CASH')
1395 AND APHD.Invoice_Distribution_ID IN
1396 (SELECT AID.Invoice_Distribution_ID
1397 FROM AP_Invoice_Distributions_All AID
1398 WHERE AID.Invoice_ID = p_inv_rec.invoice_id);
1399
1400 /* If there is any difference between the paid and maturity base amounts between
1401 this event and the prior event then we will insert the appropriate rounding
1402 distribution */
1403 l_diff_mat_paid_base_amt := l_sum_pay_paid_base_amt -
1404 NVL(l_sum_mat_paid_base_amt, l_sum_pay_paid_base_amt);
1405
1406 l_diff_clr_paid_base_amt := l_sum_pay_paid_base_amt -
1407 NVL(l_sum_clr_paid_base_amt, l_sum_pay_paid_base_amt);
1408
1409 l_diff_clr_mat_base_amt := NVL(l_sum_mat_mat_base_amt, l_sum_clr_mat_base_amt) -
1410 NVL(l_sum_clr_mat_base_amt, l_sum_mat_mat_base_amt);
1411
1412 l_pd_rec.invoice_distribution_id := l_max_pd_rec.invoice_distribution_id;
1413 l_pd_rec.payment_history_id := l_max_pd_rec.payment_history_id;
1414 l_pd_rec.invoice_payment_id := l_max_pd_rec.invoice_payment_id;
1415 l_pd_rec.invoice_adjustment_event_id := l_max_pd_rec.invoice_adjustment_event_id;
1416 l_pd_rec.accounting_event_id := p_xla_event_rec.event_id;
1417 l_pd_rec.awt_related_id := l_max_pd_rec.awt_related_id;
1418
1419 l_pd_rec.amount := 0;
1420 l_pd_rec.invoice_dist_amount := 0;
1421 l_pd_rec.bank_curr_amount := 0;
1422 l_pd_rec.invoice_dist_base_amount := 0;
1423
1424 IF l_diff_mat_paid_base_amt <> 0 THEN
1425
1426 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1427 l_log_msg := 'Inserting future payment rounding';
1428 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1429 END IF;
1430
1431
1432 l_pd_rec.pay_dist_lookup_code := 'FUTURE PAYMENT ROUNDING';
1433
1434 l_pd_rec.paid_base_amount := l_diff_mat_paid_base_amt;
1435 l_pd_rec.cleared_base_amount := 0;
1436 l_pd_rec.matured_base_amount := 0;
1437
1438 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1439 l_log_msg := 'Calling procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert';
1440 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1441 END IF;
1442
1443 AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert
1444 (l_pd_rec,
1445 l_curr_calling_sequence);
1446
1447 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1448 l_log_msg := 'Procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert executed';
1449 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1450 END IF;
1451
1452
1453 END IF;
1454
1455 IF l_diff_clr_paid_base_amt <> 0 THEN
1456
1457 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1458 l_log_msg := 'Inserting payment to clearing rounding';
1459 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1460 END IF;
1461
1462 l_pd_rec.pay_dist_lookup_code := 'PAYMENT TO CLEARING ROUNDING';
1463
1464 l_pd_rec.paid_base_amount := l_diff_clr_paid_base_amt;
1465 l_pd_rec.cleared_base_amount := 0;
1466 l_pd_rec.matured_base_amount := 0;
1467
1468 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1469 l_log_msg := 'Calling procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert';
1470 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1471 END IF;
1472
1473 AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert
1474 (l_pd_rec,
1475 l_curr_calling_sequence);
1476
1477 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1478 l_log_msg := 'Procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert executed';
1479 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1480 END IF;
1481
1482
1483 END IF;
1484
1485 IF l_diff_clr_mat_base_amt <> 0 THEN
1486
1487
1488 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1489 l_log_msg := 'Inserting maturity to clearing rounding';
1490 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1491 END IF;
1492
1493 l_pd_rec.pay_dist_lookup_code := 'MATURITY TO CLEARING ROUNDING';
1494
1495 l_pd_rec.paid_base_amount := 0;
1496 l_pd_rec.cleared_base_amount := 0;
1497 l_pd_rec.matured_base_amount := l_diff_clr_mat_base_amt;
1498
1499 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1500 l_log_msg := 'Calling procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert';
1501 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1502 END IF;
1503
1504 AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert
1505 (l_pd_rec,
1506 l_curr_calling_sequence);
1507
1508 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1509 l_log_msg := 'Procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert executed';
1510 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1511 END IF;
1512
1513
1514 END IF;
1515
1516 -- Logging Infra: Procedure level
1517 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1518 l_log_msg := 'End of procedure '|| l_procedure_name;
1519 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
1520 END IF;
1521
1522
1523 EXCEPTION
1524 WHEN OTHERS THEN
1525 IF (SQLCODE <> -20001) THEN
1526 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1527 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1528 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1529 END IF;
1530 APP_EXCEPTION.RAISE_EXCEPTION;
1531
1532 END Compare_Pay;
1533
1534
1535
1536 -------------------------------------------------------------------------------
1537 -- PROCEDURE Total_Appl
1538 -- This procedure calculates rounding to confirm that each prepayment
1539 -- application invoice distribution and its tax difference have been fully
1540 -- relieved by their corresponding records in APAD
1541 --
1542 --------------------------------------------------------------------------------
1543 PROCEDURE Total_Appl
1544 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
1545 ,P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
1546 ,P_Clr_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
1547 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
1548 ,P_Prepay_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
1549 ,P_Prepay_Hist_Rec IN AP_ACCTG_PREPAY_DIST_PKG.r_prepay_hist_info
1550 ,P_Prepay_Dist_Rec IN AP_ACCTG_PREPAY_DIST_PKG.r_prepay_dist_info
1551 ,P_Calling_Sequence IN VARCHAR2
1552 ) IS
1553
1554 l_curr_calling_sequence VARCHAR2(2000);
1555 l_prepay_pay_amt NUMBER;
1556 l_prepay_pay_tax_diff NUMBER;
1557
1558 l_tot_inv_rate_amt NUMBER;
1559 l_tot_prepay_rate_amt NUMBER;
1560 l_tot_prepay_pay_rate_amt NUMBER;
1561 l_tot_prepay_clr_rate_amt NUMBER;
1562 l_td_tot_inv_rate_amt NUMBER;
1563 l_td_tot_prepay_rate_amt NUMBER;
1564 l_td_tot_prepay_pay_rate_amt NUMBER;
1565 l_td_tot_prepay_clr_rate_amt NUMBER;
1566
1567 l_sum_inv_rate_amt NUMBER;
1568 l_sum_prepay_rate_amt NUMBER;
1569 l_sum_prepay_pay_rate_amt NUMBER;
1570 l_sum_prepay_clr_rate_amt NUMBER;
1571 l_td_sum_inv_rate_amt NUMBER;
1572 l_td_sum_prepay_rate_amt NUMBER;
1573 l_td_sum_prepay_pay_rate_amt NUMBER;
1574 l_td_sum_prepay_clr_rate_amt NUMBER;
1575
1576 l_diff_inv_rate_amt NUMBER;
1577 l_diff_prepay_rate_amt NUMBER;
1578 l_diff_prepay_pay_rate_amt NUMBER;
1579 l_diff_prepay_clr_rate_amt NUMBER;
1580 l_td_diff_inv_rate_amt NUMBER;
1581 l_td_diff_prepay_rate_amt NUMBER;
1582 l_td_diff_prepay_pay_rate_amt NUMBER;
1583 l_td_diff_prepay_clr_rate_amt NUMBER;
1584
1585 l_max_prepay_rec AP_PREPAY_APP_DISTS%ROWTYPE;
1586 l_pad_rec AP_PREPAY_APP_DISTS%ROWTYPE;
1587
1588 -- Logging Infra:
1589 l_procedure_name CONSTANT VARCHAR2(30) := 'Total_Appl';
1590 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1591
1592 BEGIN
1593
1594 l_curr_calling_sequence := 'AP_Acctg_Pay_Round_Pkg.Total_Appl<- ' ||
1595 p_calling_sequence;
1596
1597 -- Logging Infra: Procedure level
1598 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1599 l_log_msg := 'Begin of procedure '|| l_procedure_name;
1600 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1601 END IF;
1602
1603
1604 -- Get the max of the largest distribution for inserting the rounding line
1605 SELECT APAD.*
1606 INTO l_max_prepay_rec
1607 FROM AP_Prepay_App_Dists APAD
1608 WHERE Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id
1609 AND APAD.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
1610 AND Invoice_Distribution_ID =
1611 (SELECT MAX(APAD1.Invoice_Distribution_ID)
1612 FROM AP_Prepay_App_Dists APAD1
1613 WHERE APAD1.Prepay_App_Distribution_ID =
1614 p_prepay_dist_rec.invoice_distribution_id
1615 AND APAD1.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
1616 AND ABS(APAD1.Amount) =
1617 (SELECT MAX(ABS(APAD2.Amount))
1618 FROM AP_Prepay_App_Dists APAD2
1619 WHERE APAD2.Prepay_App_Distribution_ID =
1620 p_prepay_dist_rec.invoice_distribution_id
1621 AND APAD2.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id))
1622 AND Rownum = 1;
1623
1624
1625 l_tot_inv_rate_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
1626 (p_prepay_dist_rec.amount,
1627 p_inv_rec.invoice_currency_code,
1628 ap_accounting_pay_pkg.g_base_currency_code,
1629 p_inv_rec.exchange_rate_type,
1630 p_inv_rec.exchange_date,
1631 p_inv_rec.exchange_rate,
1632 l_curr_calling_sequence);
1633
1634
1635 l_tot_prepay_rate_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
1636 (p_prepay_dist_rec.amount,
1637 p_inv_rec.invoice_currency_code,
1638 ap_accounting_pay_pkg.g_base_currency_code,
1639 l_max_prepay_rec.prepay_exchange_rate_type,
1640 l_max_prepay_rec.prepay_exchange_date,
1641 l_max_prepay_rec.prepay_exchange_rate,
1642 l_curr_calling_sequence);
1643
1644
1645 -- Converting the prepay amount into payment currency
1646 IF (p_inv_rec.invoice_currency_code <> p_pay_hist_rec.pmt_currency_code) THEN
1647 l_prepay_pay_amt := p_prepay_dist_rec.amount * p_inv_rec.payment_cross_rate;
1648 l_prepay_pay_tax_diff := p_prepay_dist_rec.prepay_tax_diff_amount
1649 * p_inv_rec.payment_cross_rate;
1650 ELSE
1651 l_prepay_pay_amt := p_prepay_dist_rec.amount;
1652 l_prepay_pay_tax_diff := p_prepay_dist_rec.prepay_tax_diff_amount;
1653 END IF;
1654
1655
1656 l_tot_prepay_pay_rate_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
1657 (l_prepay_pay_amt,
1658 p_inv_rec.payment_currency_code,
1659 ap_accounting_pay_pkg.g_base_currency_code,
1660 l_max_prepay_rec.prepay_pay_exchange_rate_type,
1661 l_max_prepay_rec.prepay_pay_exchange_date,
1662 l_max_prepay_rec.prepay_pay_exchange_rate,
1663 l_curr_calling_sequence);
1664
1665 l_td_tot_inv_rate_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
1666 (p_prepay_dist_rec.prepay_tax_diff_amount,
1667 p_inv_rec.invoice_currency_code,
1668 ap_accounting_pay_pkg.g_base_currency_code,
1669 p_inv_rec.exchange_rate_type,
1670 p_inv_rec.exchange_date,
1671 p_inv_rec.exchange_rate,
1672 l_curr_calling_sequence);
1673
1674
1675 l_td_tot_prepay_rate_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
1676 (p_prepay_dist_rec.prepay_tax_diff_amount,
1677 p_inv_rec.invoice_currency_code,
1678 ap_accounting_pay_pkg.g_base_currency_code,
1679 l_max_prepay_rec.prepay_exchange_rate_type,
1680 l_max_prepay_rec.prepay_exchange_date,
1681 l_max_prepay_rec.prepay_exchange_rate,
1682 l_curr_calling_sequence);
1683
1684
1685 l_td_tot_prepay_pay_rate_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
1686 (l_prepay_pay_tax_diff,
1687 p_inv_rec.payment_currency_code,
1688 ap_accounting_pay_pkg.g_base_currency_code,
1689 l_max_prepay_rec.prepay_pay_exchange_rate_type,
1690 l_max_prepay_rec.prepay_pay_exchange_date,
1691 l_max_prepay_rec.prepay_pay_exchange_rate,
1692 l_curr_calling_sequence);
1693
1694
1695 IF l_max_prepay_rec.prepay_clr_exchange_rate IS NOT NULL THEN
1696
1697 l_tot_prepay_clr_rate_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
1698 (l_prepay_pay_amt,
1699 p_inv_rec.payment_currency_code,
1700 ap_accounting_pay_pkg.g_base_currency_code,
1701 l_max_prepay_rec.prepay_clr_exchange_rate_type,
1702 l_max_prepay_rec.prepay_clr_exchange_date,
1703 l_max_prepay_rec.prepay_clr_exchange_rate,
1704 l_curr_calling_sequence);
1705
1706 l_td_tot_prepay_clr_rate_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
1707 (l_prepay_pay_tax_diff,
1708 p_inv_rec.payment_currency_code,
1709 ap_accounting_pay_pkg.g_base_currency_code,
1710 l_max_prepay_rec.prepay_clr_exchange_rate_type,
1711 l_max_prepay_rec.prepay_clr_exchange_date,
1712 l_max_prepay_rec.prepay_clr_exchange_rate,
1713 l_curr_calling_sequence);
1714
1715 END IF;
1716
1717
1718 SELECT SUM(DECODE(Prepay_Dist_Lookup_Code, 'PREPAY APPL', Base_Amount,
1719 'PREPAY APPL REC TAX', Base_Amount, 'PREPAY APPL NONREC TAX', Base_Amount, 0)),
1720 SUM(DECODE(Prepay_Dist_Lookup_Code, 'PREPAY APPL', Base_Amt_At_Prepay_XRate,
1721 'PREPAY APPL REC TAX', Base_Amt_At_Prepay_XRate,
1722 'PREPAY APPL NONREC TAX', Base_Amt_At_Prepay_XRate, 0)),
1723 SUM(DECODE(Prepay_Dist_Lookup_Code, 'PREPAY APPL', Base_Amt_At_Prepay_Pay_XRate,
1724 'PREPAY APPL REC TAX', Base_Amt_At_Prepay_Pay_XRate,
1725 'PREPAY APPL NONREC TAX', Base_Amt_At_Prepay_Pay_XRate, 0)),
1726 SUM(DECODE(Prepay_Dist_Lookup_Code, 'PREPAY APPL', Base_Amt_At_Prepay_Clr_XRate,
1727 'PREPAY APPL REC TAX', Base_Amt_At_Prepay_Clr_XRate,
1728 'PREPAY APPL NONREC TAX', Base_Amt_At_Prepay_Clr_XRate, 0)),
1729 SUM(DECODE(Prepay_Dist_Lookup_Code, 'TAX DIFF', Base_Amount, 0)),
1730 SUM(DECODE(Prepay_Dist_Lookup_Code, 'TAX DIFF', Base_Amt_At_Prepay_XRate, 0)),
1731 SUM(DECODE(Prepay_Dist_Lookup_Code, 'TAX DIFF', Base_Amt_At_Prepay_Pay_XRate, 0)),
1732 SUM(DECODE(Prepay_Dist_Lookup_Code, 'TAX DIFF', Base_Amt_At_Prepay_Clr_XRate, 0))
1733 INTO l_sum_inv_rate_amt,
1734 l_sum_prepay_rate_amt,
1735 l_sum_prepay_pay_rate_amt,
1736 l_sum_prepay_clr_rate_amt,
1737 l_td_sum_inv_rate_amt,
1738 l_td_sum_prepay_rate_amt,
1739 l_td_sum_prepay_pay_rate_amt,
1740 l_td_sum_prepay_clr_rate_amt
1741 FROM AP_Prepay_App_Dists APAD
1742 WHERE APAD.Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id;
1743
1744
1745 /* If there is difference between the total and sum amounts then we will insert the
1746 difference as the rounding amounts */
1747
1748 l_diff_inv_rate_amt := NVL(l_tot_inv_rate_amt,l_sum_inv_rate_amt) - l_sum_inv_rate_amt;
1749 l_diff_prepay_rate_amt := NVL(l_tot_prepay_rate_amt,l_sum_prepay_rate_amt)
1750 - l_sum_prepay_rate_amt;
1751 l_diff_prepay_pay_rate_amt := NVL(l_tot_prepay_pay_rate_amt,l_sum_prepay_pay_rate_amt)
1752 - l_sum_prepay_pay_rate_amt;
1753 l_diff_prepay_clr_rate_amt := NVL(l_tot_prepay_clr_rate_amt,l_sum_prepay_clr_rate_amt)
1754 - l_sum_prepay_clr_rate_amt;
1755 l_td_diff_inv_rate_amt := NVL(l_td_tot_inv_rate_amt,l_td_sum_inv_rate_amt) - l_td_sum_inv_rate_amt;
1756 l_td_diff_prepay_rate_amt := NVL(l_td_tot_prepay_rate_amt,l_td_sum_prepay_rate_amt)
1757 - l_td_sum_prepay_rate_amt;
1758 l_td_diff_prepay_pay_rate_amt := NVL(l_td_tot_prepay_pay_rate_amt,l_td_sum_prepay_pay_rate_amt)
1759 - l_td_sum_prepay_pay_rate_amt;
1760 l_td_diff_prepay_clr_rate_amt := NVL(l_td_tot_prepay_clr_rate_amt,l_td_sum_prepay_clr_rate_amt)
1761 - l_td_sum_prepay_clr_rate_amt;
1762
1763
1764 IF (l_diff_inv_rate_amt <> 0) OR (l_diff_prepay_rate_amt <> 0) OR
1765 (l_diff_prepay_pay_rate_amt <> 0) OR (l_diff_prepay_clr_rate_amt <> 0) THEN
1766
1767 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1768 l_log_msg := 'Updating prepay appl rounding amount';
1769 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1770 END IF;
1771
1772
1773 UPDATE AP_Prepay_App_Dists APPD
1774 SET Base_Amount = Base_Amount + NVL(l_diff_inv_rate_amt,0),
1775 Rounding_Amt = l_diff_inv_rate_amt,
1776 Base_Amt_At_Prepay_XRate = Base_Amt_At_Prepay_XRate
1777 + NVL(l_diff_prepay_rate_amt,0),
1778 Round_Amt_At_Prepay_XRate = l_diff_prepay_rate_amt,
1779 Base_Amt_At_Prepay_Pay_XRate = Base_Amt_At_Prepay_Pay_XRate
1780 + NVL(l_diff_prepay_pay_rate_amt,0),
1781 Round_Amt_At_Prepay_Pay_XRate = l_diff_prepay_pay_rate_amt,
1782 Base_Amt_At_Prepay_Clr_XRate = Base_Amt_At_Prepay_Clr_XRate
1783 + NVL(l_diff_prepay_clr_rate_amt,0),
1784 Round_Amt_At_Prepay_Clr_XRate = l_diff_prepay_clr_rate_amt
1785 WHERE Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
1786 AND Invoice_Distribution_ID = l_max_prepay_rec.invoice_distribution_id
1787 AND Prepay_App_Distribution_ID = l_max_prepay_rec.prepay_app_distribution_id
1788 -- AND Accounting_Event_ID = p_xla_event_rec.event_id
1789 AND Prepay_Dist_Lookup_Code IN ('PREPAY APPL','PREPAY APPL REC TAX',
1790 'PREPAY APPL NONREC TAX');
1791
1792
1793 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1794 l_log_msg := 'Updated prepay appl rounding amount';
1795 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1796 END IF;
1797
1798
1799 END IF;
1800
1801
1802 IF (l_td_diff_inv_rate_amt <> 0) OR (l_td_diff_prepay_rate_amt <> 0) OR
1803 (l_td_diff_prepay_pay_rate_amt <> 0) OR (l_td_diff_prepay_clr_rate_amt <> 0) THEN
1804
1805
1806 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1807 l_log_msg := 'Updating tax diff rounding amount';
1808 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1809 END IF;
1810
1811 UPDATE AP_Prepay_App_Dists APPD
1812 SET Base_Amount = Base_Amount + NVL(l_td_diff_inv_rate_amt,0),
1813 Rounding_Amt = l_td_diff_inv_rate_amt,
1814 Base_Amt_At_Prepay_XRate = Base_Amt_At_Prepay_XRate
1815 + NVL(l_td_diff_prepay_rate_amt,0),
1816 Round_Amt_At_Prepay_XRate = l_td_diff_prepay_rate_amt,
1817 Base_Amt_At_Prepay_Pay_XRate = Base_Amt_At_Prepay_Pay_XRate
1818 + NVL(l_td_diff_prepay_pay_rate_amt,0),
1819 Round_Amt_At_Prepay_Pay_XRate = l_td_diff_prepay_pay_rate_amt,
1820 Base_Amt_At_Prepay_Clr_XRate = Base_Amt_At_Prepay_Clr_XRate
1821 + NVL(l_td_diff_prepay_clr_rate_amt,0),
1822 Round_Amt_At_Prepay_Clr_XRate = l_td_diff_prepay_clr_rate_amt
1823 WHERE Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
1824 AND Invoice_Distribution_ID = l_max_prepay_rec.invoice_distribution_id
1825 AND Prepay_App_Distribution_ID = l_max_prepay_rec.prepay_app_distribution_id
1826 -- AND Accounting_Event_ID = p_xla_event_rec.event_id
1827 AND Prepay_Dist_Lookup_Code IN ('TAX DIFF');
1828
1829
1830 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1831 l_log_msg := 'Updating tax diff rounding amount';
1832 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1833 END IF;
1834
1835
1836 END IF;
1837
1838 -- Logging Infra: Procedure level
1839 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1840 l_log_msg := 'End of procedure '|| l_procedure_name;
1841 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
1842 END IF;
1843
1844
1845 EXCEPTION
1846 WHEN OTHERS THEN
1847 IF (SQLCODE <> -20001) THEN
1848 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1849 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1850 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1851 END IF;
1852 APP_EXCEPTION.RAISE_EXCEPTION;
1853
1854 END Total_Appl;
1855
1856
1857 -------------------------------------------------------------------------------
1858 -- PROCEDURE Final_Appl
1859 -- This procedure calculates the rounding amount to relieve the prepaid
1860 -- expense completely. This is calculated during final application of a
1861 -- prepayment distribution
1862 --
1863 --------------------------------------------------------------------------------
1864 PROCEDURE Final_Appl
1865 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
1866 ,P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
1867 ,P_Clr_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
1868 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
1869 ,P_Prepay_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
1870 ,P_Prepay_Hist_Rec IN AP_ACCTG_PREPAY_DIST_PKG.r_prepay_hist_info
1871 ,P_Prepay_Dist_Rec IN AP_ACCTG_PREPAY_DIST_PKG.r_prepay_dist_info
1872 ,P_Calling_Sequence IN VARCHAR2
1873 ) IS
1874
1875 l_curr_calling_sequence VARCHAR2(2000);
1876
1877 l_sum_pay_base_amt NUMBER;
1878 l_sum_pay_paid_base_amt NUMBER;
1879 l_sum_pay_clrd_base_amt NUMBER;
1880
1881 l_sum_prepay_rate_amt NUMBER;
1882 l_sum_prepay_pay_rate_amt NUMBER;
1883 l_sum_prepay_clr_rate_amt NUMBER;
1884
1885 l_diff_prepay_rate_amt NUMBER;
1886 l_diff_prepay_pay_rate_amt NUMBER;
1887 l_diff_prepay_clr_rate_amt NUMBER;
1888
1889 l_max_prepay_rec AP_PREPAY_APP_DISTS%ROWTYPE;
1890 l_pad_rec AP_PREPAY_APP_DISTS%ROWTYPE;
1891
1892 -- Logging Infra:
1893 l_procedure_name CONSTANT VARCHAR2(30) := 'Final_Appl';
1894 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1895
1896 BEGIN
1897
1898 l_curr_calling_sequence := 'AP_ACCTG_PAY_ROUND_PKG.Final_Appl<- ' ||
1899 p_calling_sequence;
1900
1901 -- Logging Infra: Procedure level
1902 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1903 l_log_msg := 'Begin of procedure '|| l_procedure_name;
1904 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1905 END IF;
1906
1907 -- Getting the max of the largest distribution for inserting the rounding dist
1908 SELECT APAD.*
1909 INTO l_max_prepay_rec
1910 FROM AP_Prepay_App_Dists APAD
1911 WHERE Invoice_Distribution_ID IN
1912 (SELECT MAX(APAD1.Invoice_Distribution_ID)
1913 FROM AP_Prepay_App_Dists APAD1
1914 WHERE APAD1.Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id
1915 AND APAD1.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
1916 AND ABS(APAD1.Amount) =
1917 (SELECT MAX(ABS(APAD2.Amount))
1918 FROM AP_Prepay_App_Dists APAD2
1919 WHERE APAD2.Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id
1920 AND APAD2.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id))
1921 AND APAD.Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id
1922 AND APAD.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
1923 AND Rownum = 1;
1924
1925
1926 -- Get the paid base amount for the payment event
1927 SELECT SUM(APHD.Paid_Base_Amount)
1928 INTO l_sum_pay_paid_base_amt
1929 FROM AP_Payment_Hist_Dists APHD,
1930 AP_Payment_History_All APH
1931 WHERE APH.Related_Event_ID = p_pay_hist_rec.Related_Event_ID
1932 AND APHD.Payment_History_ID = APH.Payment_History_ID
1933 AND Invoice_Distribution_ID IN
1934 (SELECT AID.Invoice_Distribution_ID
1935 FROM AP_Invoice_Distributions_All AID
1936 WHERE AID.Prepay_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id)
1937 AND Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT', 'AWT', 'TOTAL PAYMENT ROUNDING',
1938 'FINAL PAYMENT ROUNDING');
1939
1940
1941 -- Get the cleared base amount for the payment clearing event
1942 SELECT SUM(APHD.Cleared_Base_Amount)
1943 INTO l_sum_pay_clrd_base_amt
1944 FROM AP_Payment_Hist_Dists APHD,
1945 AP_Payment_History_All APH
1946 WHERE APH.Related_Event_ID = p_clr_hist_rec.related_event_id
1947 AND APHD.Payment_History_ID = APH.Payment_History_ID
1948 AND Invoice_Distribution_ID IN
1949 (SELECT AID.Invoice_Distribution_ID
1950 FROM AP_Invoice_Distributions_All AID
1951 WHERE AID.Prepay_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id)
1952 AND Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT', 'AWT', 'TOTAL CLEARING ROUNDING',
1953 'FINAL PAYMENT ROUNDING');
1954
1955
1956 -- Get the sum of the base amounts for the different prepayment xrates
1957 SELECT SUM(Base_Amt_At_Prepay_XRate),
1958 SUM(Base_Amt_At_Prepay_Pay_XRate),
1959 SUM(Base_Amt_At_Prepay_Clr_XRate)
1960 INTO l_sum_prepay_rate_amt,
1961 l_sum_prepay_pay_rate_amt,
1962 l_sum_prepay_clr_rate_amt
1963 FROM AP_Prepay_App_Dists
1964 WHERE Prepay_App_Distribution_ID IN
1965 (SELECT AID.Invoice_Distribution_ID
1966 FROM AP_Invoice_Distributions_All AID
1967 WHERE AID.Prepay_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id);
1968
1969
1970 SELECT AID.Base_Amount
1971 INTO l_sum_pay_base_amt
1972 FROM AP_Invoice_Distributions_All AID
1973 WHERE AID.Invoice_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id;
1974
1975
1976 l_diff_prepay_rate_amt := l_sum_pay_base_amt + l_sum_prepay_rate_amt;
1977 l_diff_prepay_pay_rate_amt := l_sum_pay_paid_base_amt + l_sum_prepay_pay_rate_amt;
1978 l_diff_prepay_clr_rate_amt := l_sum_pay_clrd_base_amt + l_sum_prepay_clr_rate_amt;
1979
1980
1981 l_pad_rec.prepay_history_id := p_prepay_hist_rec.prepay_history_id;
1982 l_pad_rec.accounting_event_id := p_xla_event_rec.event_id;
1983 l_pad_rec.invoice_distribution_id := l_max_prepay_rec.invoice_distribution_id;
1984 l_pad_rec.prepay_app_distribution_id := l_max_prepay_rec.prepay_app_distribution_id;
1985
1986 l_pad_rec.prepay_exchange_rate := l_max_prepay_rec.prepay_exchange_rate;
1987 l_pad_rec.prepay_exchange_rate_type := l_max_prepay_rec.prepay_exchange_rate_type;
1988 l_pad_rec.prepay_exchange_date := l_max_prepay_rec.prepay_exchange_date;
1989 l_pad_rec.prepay_pay_exchange_rate := l_max_prepay_rec.prepay_pay_exchange_rate;
1990 l_pad_rec.prepay_pay_exchange_rate_type := l_max_prepay_rec.prepay_pay_exchange_rate_type;
1991 l_pad_rec.prepay_pay_exchange_date := l_max_prepay_rec.prepay_pay_exchange_date;
1992 l_pad_rec.prepay_clr_exchange_rate := l_max_prepay_rec.prepay_clr_exchange_rate;
1993 l_pad_rec.prepay_clr_exchange_rate_type := l_max_prepay_rec.prepay_clr_exchange_rate_type;
1994 l_pad_rec.prepay_clr_exchange_date := l_max_prepay_rec.prepay_clr_exchange_date;
1995 l_pad_rec.awt_related_id := l_max_prepay_rec.awt_related_id;
1996
1997 l_pad_rec.amount := 0;
1998 l_pad_rec.base_amount := 0;
1999
2000 IF (l_diff_prepay_rate_amt <> 0) OR (l_diff_prepay_pay_rate_amt <> 0)
2001 OR (l_diff_prepay_clr_rate_amt <> 0) THEN
2002
2003 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2004 l_log_msg := 'Inserting final appl rounding dist';
2005 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2006 END IF;
2007
2008
2009 l_pad_rec.prepay_dist_lookup_code := 'FINAL APPL ROUNDING';
2010 l_pad_rec.base_amt_at_prepay_xrate := l_diff_prepay_rate_amt;
2011 l_pad_rec.base_amt_at_prepay_pay_xrate := l_diff_prepay_pay_rate_amt;
2012 l_pad_rec.base_amt_at_prepay_clr_xrate := l_diff_prepay_clr_rate_amt;
2013
2014
2015 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2016 l_log_msg := 'Calling procedure Prepay_Dist_Insert';
2017 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2018 END IF;
2019
2020
2021 AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Insert
2022 (l_pad_rec,
2023 l_curr_calling_sequence);
2024
2025 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2026 l_log_msg := 'Procedure Prepay_Dist_Insert executed';
2027 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2028 END IF;
2029
2030
2031 END IF;
2032
2033 -- Logging Infra: Procedure level
2034 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2035 l_log_msg := 'End of procedure '|| l_procedure_name;
2036 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
2037 END IF;
2038
2039
2040 EXCEPTION
2041 WHEN OTHERS THEN
2042 IF (SQLCODE <> -20001) THEN
2043 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2044 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2045 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2046 END IF;
2047 APP_EXCEPTION.RAISE_EXCEPTION;
2048
2049 END Final_Appl;
2050
2051
2052 END AP_ACCTG_PAY_ROUND_PKG;