[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.66.12020000.2 2012/07/12 09:47:39 pgayen 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 exec_final_payment VARCHAR2(1); --bug 7614480
44 l_max_prepay_app_dist_id AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID%TYPE; --bug 7614480
45
46 BEGIN
47
48 l_curr_calling_sequence := 'AP_Acctg_Pay_Round_Pkg.Do_Rounding<- ' ||
49 p_calling_sequence;
50
51 -- Logging Infra: Setting up runtime level
52 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
53
54 -- Logging Infra: Procedure level
55 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
56 l_log_msg := 'Begin of procedure '|| l_procedure_name;
57 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
58 END IF;
59
60
61 /* Check for rounding only if the invoice currency or payment currency is different than
62 the base currency */
63 IF (p_inv_rec.invoice_currency_code <> ap_accounting_pay_pkg.g_base_currency_code) OR
64 (p_inv_rec.payment_currency_code <> ap_accounting_pay_pkg.g_base_currency_code) THEN
65
66 IF p_prepay_dist_rec.invoice_distribution_id IS NOT NULL THEN
67
68 IF NOT AP_ACCOUNTING_PAY_PKG.Is_Upgrade_Proration_Exists(P_Rounding_Method => AP_ACCOUNTING_PAY_PKG.G_TOTAL_APPL
69 ,P_Inv_Rec => p_inv_rec
70 ,P_Invoice_Payment_id => NULL
71 ,P_Prepay_Appl_Distribution_Id => p_prepay_dist_rec.invoice_distribution_id
72 ,P_Prepay_Distribution_Id => NULL
73 ,P_Transaction_Type => p_xla_event_rec.event_type_code
74 ,P_Calling_Sequence => l_curr_calling_sequence
75 ) -- add this call for Bug10183934
76 THEN
77
78 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
79 l_log_msg := 'Calling procedure Total_Appl for dist: '
80 || p_prepay_dist_rec.invoice_distribution_id;
81 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
82 END IF;
83
84
85 -- Calculate the total application rounding
86 Total_Appl(p_xla_event_rec,
87 p_pay_hist_rec,
88 p_clr_hist_rec,
89 p_inv_rec,
90 p_prepay_inv_rec,
91 p_prepay_hist_rec,
92 p_prepay_dist_rec,
93 l_curr_calling_sequence);
94
95 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
96 l_log_msg := 'Procedure Total_Appl executed';
97 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
98 END IF;
99
100 END IF; -- AP_ACCOUNTING_PAY_PKG.Is_Upgrade_Proration_Exists
101
102 IF NOT AP_ACCOUNTING_PAY_PKG.Is_Upgrade_Proration_Exists(P_Rounding_Method => AP_ACCOUNTING_PAY_PKG.G_FINAL_APPL
103 ,P_Inv_Rec => p_inv_rec
104 ,P_Invoice_Payment_Id => NULL
105 ,P_Prepay_Appl_Distribution_Id => NULL
106 ,P_Prepay_Distribution_Id => p_prepay_dist_rec.prepay_distribution_id
107 ,P_Transaction_Type => p_xla_event_rec.event_type_code
108 ,P_Calling_Sequence => l_curr_calling_sequence
109 ) -- add this call for Bug10183934
110 THEN
111 /* Get the prepayment app dists amount that has already been accounted */
112 SELECT SUM(-1 * APAD.Amount)
113 INTO l_prepay_acctg_amt
114 FROM AP_Prepay_App_Dists APAD,
115 AP_Invoice_Distributions_All AID
116 WHERE APAD.Prepay_App_Distribution_ID = AID.Invoice_Distribution_ID
117 AND AID.Prepay_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id;
118
119
120 SELECT AID.Amount
121 INTO l_prepay_amt
122 FROM AP_Invoice_Distributions_All AID
123 WHERE AID.Invoice_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id;
124
125
126 -- Check for final application rounding only if this prepayment has been
127 -- fully applied
128 IF (l_prepay_acctg_amt = l_prepay_amt) THEN
129
130 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
131 l_log_msg := 'Calling procedure Final_Appl for prepay dist: '
132 || p_prepay_dist_rec.prepay_distribution_id;
133 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
134 END IF;
135
136 Final_Appl
137 (p_xla_event_rec,
138 p_pay_hist_rec,
139 p_clr_hist_rec,
140 p_inv_rec,
141 p_prepay_inv_rec,
142 p_prepay_hist_rec,
143 p_prepay_dist_rec,
144 l_curr_calling_sequence);
145
146 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
147 l_log_msg := 'Procedure Final_Appl executed';
148 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
149 END IF;
150
151 END IF;
152
153 END IF; -- AP_ACCOUNTING_PAY_PKG.Is_Upgrade_Proration_Exists
154
155 ELSE
156
157 IF NOT AP_ACCOUNTING_PAY_PKG.Is_Upgrade_Proration_Exists(P_Rounding_Method => AP_ACCOUNTING_PAY_PKG.G_TOTAL_PAY
158 ,P_Inv_Rec => p_inv_rec
159 ,P_Invoice_Payment_Id => p_inv_pay_rec.invoice_payment_id
160 ,P_Prepay_Appl_Distribution_Id => NULL
161 ,P_Prepay_Distribution_Id => NULL
162 ,P_Transaction_Type => p_xla_event_rec.event_type_code
163 ,P_Calling_Sequence => l_curr_calling_sequence
164 ) -- add this call for Bug10183934
165 THEN
166
167 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
168 l_log_msg := 'Calling procedure Total_Pay for invoice: '
169 || p_inv_rec.invoice_id;
170 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
171 END IF;
172
173
174 -- Calculate the total payment rounding
175 Total_Pay(p_xla_event_rec,
176 p_pay_hist_rec,
177 p_inv_rec,
178 p_inv_pay_rec,
179 l_curr_calling_sequence);
180
181 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
182 l_log_msg := 'Procedure Total_Pay executed';
183 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
184 END IF;
185
186 END IF;
187
188 IF NOT AP_ACCOUNTING_PAY_PKG.Is_Upgrade_Proration_Exists(P_Rounding_Method => AP_ACCOUNTING_PAY_PKG.G_COMPARE_PAY
189 ,P_Inv_Rec => p_inv_rec
190 ,P_Invoice_Payment_Id => p_inv_pay_rec.invoice_payment_id
191 ,P_Prepay_Appl_Distribution_Id => NULL
192 ,P_Prepay_Distribution_Id => NULL
193 ,P_Transaction_Type => p_xla_event_rec.event_type_code
194 ,P_Calling_Sequence => l_curr_calling_sequence
195 ) -- add this call for Bug10183934
196 THEN
197
198 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
199 l_log_msg := 'Calling procedure Compare_Pay for invoice: '
200 || p_inv_rec.invoice_id;
201 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
202 END IF;
203
204 -- Calculate the payment to maturity, payment to clearing and
205 -- maturity to clearing rounding
206 Compare_Pay(p_xla_event_rec,
207 p_pay_hist_rec,
208 p_inv_rec,
209 p_inv_pay_rec,
210 l_curr_calling_sequence);
211
212 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
213 l_log_msg := 'Procedure Compare_Pay executed';
214 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
215 END IF;
216
217 END IF; -- AP_ACCOUNTING_PAY_PKG.Is_Upgrade_Proration_Exists
218
219 END IF; -- if prepayment event type
220
221 /* Calculating the final payment rounding only when the invoice is fully paid */
222 /* bug 9108925 -- the logic to check if the current event is the final pay/prepay event
223 for the invoice has been moved to AP_ACCOUNTING_PAY_PKG, function Is_Final_Event for
224 common reference */
225 IF AP_Accounting_Pay_Pkg.Is_Final_Payment(p_inv_rec,
226 0, -- payment amt
227 0, -- discount taken
228 0, -- prepay amount
229 p_xla_event_rec.event_type_code,
230 l_curr_calling_sequence)
231 AND AP_ACCOUNTING_PAY_PKG.Is_Final_Event(p_inv_rec,
232 p_xla_event_rec,
233 P_Prepay_Dist_Rec.Invoice_Distribution_Id,
234 l_curr_calling_sequence) THEN
235
236 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
237 l_log_msg := 'Calling procedure Final_Pay for invoice: '
238 || p_inv_rec.invoice_id;
239 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
240 END IF;
241
242 --bug 9108925, commented the following, as is_final_event would take care of
243 --this condition
244
245 /* IF p_xla_event_rec.event_id = l_acctg_event_id
246 OR p_prepay_dist_rec.invoice_distribution_id = l_max_prepay_app_dist_id THEN */
247 -- bug 6600341 --bug 7614480, added the OR condition
248
249 -- Calculate the final payment rounding to relieve the liability on the
250 -- invoice fully.
251 Final_Pay(p_xla_event_rec,
252 p_pay_hist_rec,
253 p_clr_hist_rec,
254 p_inv_rec,
255 p_inv_pay_rec,
256 p_prepay_inv_rec,
257 p_prepay_hist_rec,
258 p_prepay_dist_rec,
259 l_curr_calling_sequence);
260
261 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
262 l_log_msg := 'Procedure Final_Pay executed';
263 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
264 END IF;
265
266 ELSE -- bug 6600341 contd
267 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
268 l_log_msg := 'Procedure Final_Pay for invoice not called: '
269 || p_inv_rec.invoice_id;
270 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,l_log_msg);
271 END IF;
272 -- END IF; -- bug 6600341 end
273 END IF;
274
275 END IF;
276
277 -- Logging Infra: Procedure level
278 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
279 l_log_msg := 'End of procedure '|| l_procedure_name;
280 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
281 END IF;
282
283
284 EXCEPTION
285 WHEN OTHERS THEN
286 IF (SQLCODE = -20100) THEN
287 RAISE_APPLICATION_ERROR(-20100, SQLERRM);
288 ELSIF (SQLCODE <> -20001) THEN
289 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
290 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
291 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
292 END IF;
293 APP_EXCEPTION.RAISE_EXCEPTION;
294 END Do_Rounding;
295
296
297
298 -------------------------------------------------------------------------------
299 -- PROCEDURE Final_Pay
300 -- This procedure calculates the rounding amount needed to relieve liability
301 -- when a final payment is made on a foreign currency invoice and creates
302 -- a final payment rounding if the amount is not fully relieved.
303 --
304 --------------------------------------------------------------------------------
305 PROCEDURE Final_Pay
306 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
307 ,P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
308 ,P_Clr_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
309 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
310 ,P_Inv_Pay_Rec IN ap_acctg_pay_dist_pkg.r_inv_pay_info
311 ,P_Prepay_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
312 ,P_Prepay_Hist_Rec IN AP_ACCTG_PREPAY_DIST_PKG.r_prepay_hist_info
313 ,P_Prepay_Dist_Rec IN AP_ACCTG_PREPAY_DIST_PKG.r_prepay_dist_info
314 ,P_Calling_Sequence IN VARCHAR2
315 ) IS
316
317 l_curr_calling_sequence VARCHAR2(2000);
318 l_sum_pay_dist_base_amt NUMBER;
319 l_sum_prepay_base_amt NUMBER;
323 -- Bug 5570002 - should exclude the TERV if ERV is excluded
320 l_total_dist_base_amt NUMBER;
321
322
324 -- Bug 7314656, added historical flag and accounting_event_id
325 CURSOR Invoice_Dists
326 (P_Invoice_ID IN NUMBER
327 ) IS
328 SELECT AID.Invoice_Distribution_ID,
329 AID.Line_Type_Lookup_Code,
330 AID.related_id,
331 AID.Amount,
332 AID.Base_Amount,
333 AID.Invoice_Id,
334 AID.accounting_event_id,
335 AID.historical_flag
336 FROM AP_Invoice_Distributions_All AID,
337 Financials_System_Params_All FSP
338 WHERE AID.Invoice_ID = p_invoice_id
339 AND AID.Line_Type_Lookup_Code NOT IN ('PREPAY', 'AWT', 'ERV', 'TERV')
340 AND AID.Prepay_Distribution_ID IS NULL
341 AND AID.Prepay_Tax_Parent_ID IS NULL -- For tax dists created in R11.5
342 AND AID.Org_ID = FSP.Org_ID
343 --Bug6511672
344 /*AND 'INVOICE CANCELLED' <> (SELECT event_type_code
345 FROM xla_events
346 WHERE event_id = AID.accounting_event_id)*/
347 --bug6614371
348 -- Bug 6712649. Added Credit and Debit memo cancelled
349 AND NOT EXISTS (SELECT 1
350 FROM xla_events
351 WHERE event_id = AID.accounting_event_id
352 AND event_type_code IN ('INVOICE CANCELLED', 'PREPAYMENT CANCELLED',
353 'CREDIT MEMO CANCELLED',
354 'DEBIT MEMO CANCELLED'));
355 /*AND ((NVL(FSP.Purch_Encumbrance_Flag,'N') = 'N'
356 AND AID.Match_Status_Flag IN ('T','A'))
357 OR
358 ((NVL(FSP.Purch_Encumbrance_Flag,'N') = 'Y'
359 AND AID.Match_Status_Flag = 'A')));*/ --bug 7614480
360
361
362 -- bug7314656, cursor added to check if the current
363 -- event is an adjustment event with a net balance 0
364 -- under no liability posting method.
365 CURSOR c_sum_per_event(p_acct_event_id NUMBER) IS
366 SELECT SUM(amount), count(1)
367 FROM ap_invoice_distributions_all aid,
368 xla_events evnt,
369 ap_system_parameters_all asp
370 WHERE aid.accounting_event_id = p_acct_event_id
371 AND aid.accounting_event_id = evnt.event_id
372 AND evnt.event_type_code IN ('INVOICE ADJUSTED',
373 'CREDIT MEMO ADJUSTED',
374 'DEBIT MEMO ADJUSTED',
375 'PREPAYMENT ADJUSTED') -- added for bug#9545528 and 12731687
376 AND aid.org_id = asp.org_id
377 AND automatic_offsets_flag = 'N'
378 AND aid.historical_flag = 'Y';
379
380 b_generate_pay_dist BOOLEAN;
381 l_sum_per_event NUMBER;
382 l_dist_count_per_event NUMBER;
383
384
385
386 l_max_prepay_rec AP_PREPAY_APP_DISTS%ROWTYPE;
387 l_pad_rec AP_PREPAY_APP_DISTS%ROWTYPE;
388 l_max_pay_rec AP_PAYMENT_HIST_DISTS%ROWTYPE;
389 l_pd_rec AP_PAYMENT_HIST_DISTS%ROWTYPE;
390 l_erv_base_amount AP_INVOICE_DISTRIBUTIONS_ALL.base_amount%type := 0;
391
392 -- Logging Infra:
393 l_procedure_name CONSTANT VARCHAR2(30) := 'Final_Pay';
394 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
395
396
397 BEGIN
398
399 l_curr_calling_sequence := 'AP_Acctg_Pay_Round_Pkg.Final_Pay<- ' ||
400 p_calling_sequence;
401
402 -- Logging Infra: Procedure level
403 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
404 l_log_msg := 'Begin of procedure '|| l_procedure_name;
405 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
406 END IF;
407
408
409 FOR l_inv_dist_rec IN Invoice_Dists(p_inv_rec.invoice_id)
410 LOOP
411
412 --bug7314656, added the check to see if the current event is
413 --a historical adjustment distribution, with the net balance
414 --as 0 under automatic offsets off condition
415 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
416 l_log_msg := 'Checking b_generate_pay_dist';
417 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
418 END IF;
419
420 b_generate_pay_dist := TRUE;
421 IF nvl(l_inv_dist_rec.historical_flag, 'N') ='Y' THEN
422 OPEN c_sum_per_event(l_inv_dist_rec.accounting_event_id);
423 FETCH c_sum_per_event into l_sum_per_event, l_dist_count_per_event;
424
425 -- > 0 case is to handled the case that only 1 line in adjustment event and itself amount is 0
426 If l_dist_count_per_event > 0 AND l_sum_per_event = 0 THEN
427 b_generate_pay_dist := FALSE;
428 END IF;
429
430 CLOSE c_sum_per_event;
431
432 END IF;
433
434 IF b_generate_pay_dist THEN
435 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
436 l_log_msg := 'b_generate_pay_dist = TRUE for Invoice Distribution : ' ||
437 l_inv_dist_rec.invoice_distribution_id;
438 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
439 END IF;
440 ELSE
441 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
442 l_log_msg := 'b_generate_pay_dist = FALSE(No error) for Invoice Distribution : ' ||
443 l_inv_dist_rec.invoice_distribution_id;
444 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
445 END IF;
446 END IF ;
447
448 --bug7314656, proceed to calculate the final payment rounding only
449 --if the current event is not an adjustment distribution with a net
450 --0 balance under automatic offsets off condition
451 IF b_generate_pay_dist then
452
456 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
453 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
454 l_log_msg := 'Withi CUR loop: Invoice_Dists: Invoice_Dist_ID = '||
455 l_inv_dist_rec.invoice_distribution_id;
457 END IF;
458
459
460 -- Get the base amount from the payment hist distributions table for
461 -- this invoice distribution.
462 IF (p_xla_event_rec.event_type_code IN
463 ('PAYMENT CLEARED', 'PAYMENT CLEARING ADJUSTED')) THEN
464
465 -- bug 5570002 modified add the tax erv
466 -- Bug 7138115. Added additional join conditions to improve performance
467 SELECT SUM(--DECODE(APHD.Pay_Dist_Lookup_Code,
468 -- 'EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
469 -- 'TAX EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
470 APHD.Invoice_Dist_Base_Amount)
471 INTO l_sum_pay_dist_base_amt
472 FROM AP_Payment_Hist_Dists APHD,
473 AP_Payment_History_All APH,
474 AP_Invoice_Payments_All AIP
475 WHERE ((APHD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
476 AND APHD.Pay_Dist_Lookup_Code IN
477 ('CASH', 'DISCOUNT', 'FINAL PAYMENT ROUNDING'))
478 OR (APHD.Pay_Dist_Lookup_Code='AWT'
479 AND APHD.AWT_Related_ID = l_inv_dist_rec.invoice_distribution_id))
480 AND AIP.Invoice_ID = p_inv_rec.invoice_id
481 AND AIP.Check_ID = APH.Check_ID
482 AND APH.Payment_History_ID = APHD.Payment_History_ID
483 AND AIP.Invoice_payment_id = APHD.Invoice_payment_id -- Bug 8722710
484 AND APH.Transaction_Type IN ('PAYMENT CLEARING', 'PAYMENT UNCLEARING',
485 'PAYMENT CLEARING ADJUSTED')
486 -- bug 9257606, ignore the event/payment if reversed
487 AND NVL(AIP.reversal_flag, 'N') <> 'Y'
488 AND NOT EXISTS (SELECT 'Event Reversed'
489 FROM Ap_Payment_History_All APH_REL
490 WHERE APH_REL.check_id = APH.check_id
491 AND NVL(APH_REL.related_event_id, APH_REL.accounting_event_id) =
492 NVL(APH.related_event_id, APH.accounting_event_id)
493 AND APH_REL.rev_pmt_hist_id IS NOT NULL);
494
495 ELSIF (p_xla_event_rec.event_type_code IN
496 ('PAYMENT MATURED', 'PAYMENT MATURITY ADJUSTED')) THEN
497
498 -- Bug 7138115. Added additional join conditions to improve performance
499 SELECT SUM(--DECODE(APHD.Pay_Dist_Lookup_Code,
500 -- 'EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
501 -- 'TAX EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
502 APHD.Invoice_Dist_Base_Amount)
503 INTO l_sum_pay_dist_base_amt
504 FROM AP_Payment_Hist_Dists APHD,
505 AP_Payment_History_All APH,
506 AP_Invoice_Payments_All AIP
507 WHERE ((APHD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
508 AND APHD.Pay_Dist_Lookup_Code IN
509 ('CASH', 'DISCOUNT', 'FINAL PAYMENT ROUNDING' ))
510 OR (APHD.Pay_Dist_Lookup_Code='AWT'
511 AND APHD.AWT_Related_ID = l_inv_dist_rec.invoice_distribution_id))
512 AND AIP.Invoice_ID = p_inv_rec.invoice_id
513 AND AIP.Check_ID = APH.Check_ID
514 AND APH.Payment_History_ID = APHD.Payment_History_ID
515 AND AIP.Invoice_payment_id = APHD.Invoice_payment_id -- Bug 8722710
516 AND APH.Transaction_Type IN ('PAYMENT MATURITY', 'PAYMENT MATURITY REVERSED',
517 'PAYMENT MATURITY ADJUSTED')
518 -- bug 9257606, ignore the event/payment if reversed
519 AND NVL(AIP.reversal_flag, 'N') <> 'Y'
520 AND NOT EXISTS (SELECT 'Event Reversed'
521 FROM Ap_Payment_History_All APH_REL
522 WHERE APH_REL.check_id = APH.check_id
523 AND NVL(APH_REL.related_event_id, APH_REL.accounting_event_id) =
524 NVL(APH.related_event_id, APH.accounting_event_id)
525 AND APH_REL.rev_pmt_hist_id IS NOT NULL);
526
527 ELSE
528 -- bug 5570002 modified need to consider Tax erv
529 -- Bug 7138115. Added additional join conditions to improve performance
530 SELECT SUM(--DECODE(APHD.Pay_Dist_Lookup_Code,
531 -- 'EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
532 -- 'TAX EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
533 APHD.Invoice_Dist_Base_Amount)
534 INTO l_sum_pay_dist_base_amt
535 FROM AP_Payment_Hist_Dists APHD,
536 AP_Payment_History_All APH,
537 AP_Invoice_Payments_All AIP
538 WHERE ((APHD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
539 AND APHD.Pay_Dist_Lookup_Code IN
540 ('CASH', 'DISCOUNT', 'FINAL PAYMENT ROUNDING' ))
541 OR (APHD.Pay_Dist_Lookup_Code='AWT'
542 AND APHD.AWT_Related_ID = l_inv_dist_rec.invoice_distribution_id))
543 AND AIP.Invoice_ID = p_inv_rec.invoice_id
544 AND AIP.Check_ID = APH.Check_ID
545 AND AIP.Invoice_payment_id = APHD.Invoice_payment_id -- Bug 8722710
546 AND APH.Payment_History_ID = APHD.Payment_History_ID
547 AND APH.Transaction_Type IN ('PAYMENT CREATED', 'PAYMENT CANCELLED', 'PAYMENT ADJUSTED',
551 -- bug 9257606, ignore the event/payment if reversed
548 'MANUAL PAYMENT ADJUSTED', 'UPGRADED MANUAL PMT ADJUSTED',
549 'REFUND RECORDED', 'REFUND ADJUSTED', 'REFUND CANCELLED',
550 'MANUAL REFUND ADJUSTED') --bug 10336668
552 AND NVL(AIP.reversal_flag, 'N') <> 'Y'
553 AND NOT EXISTS (SELECT 'Event Reversed'
554 FROM Ap_Payment_History_All APH_REL
555 WHERE APH_REL.check_id = APH.check_id
556 AND NVL(APH_REL.related_event_id, APH_REL.accounting_event_id) =
557 NVL(APH.related_event_id, APH.accounting_event_id)
558 AND APH_REL.rev_pmt_hist_id IS NOT NULL);
559
560 END IF;
561
562 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
563 l_log_msg := 'Value of l_sum_pay_dist_base_amt = '||l_sum_pay_dist_base_amt;
564 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
565 END IF;
566
567 -- Bug 7138115. Added additional join conditions to improve performance
568 -- bug 9257606, ignore the event, if reversed
569 -- bug 9920036, changed the exists clause added by 9257606, to make it
570 -- independent of accounting events
571 --
572 SELECT SUM(APAD.Base_Amount)
573 INTO l_sum_prepay_base_amt
574 FROM AP_Prepay_App_Dists APAD,
575 AP_Prepay_History_All APH
576 WHERE ((APAD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
577 AND APAD.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
578 'PREPAY APPL NONREC TAX'))
579 OR (APAD.AWT_Related_ID = l_inv_dist_rec.invoice_distribution_id
580 AND APAD.Prepay_Dist_Lookup_Code = 'AWT'))
581 AND APH.Invoice_ID = p_inv_rec.invoice_id
582 AND APH.Prepay_History_ID = APAD.Prepay_History_ID
583 AND NOT EXISTS (SELECT 'reversed'
584 FROM Ap_Invoice_Distributions_All AID
585 WHERE APAD.Prepay_App_Distribution_ID =
586 AID.Invoice_Distribution_ID
587 AND AID.reversal_flag = 'Y');
588
589 l_total_dist_base_amt := NVL(l_sum_pay_dist_base_amt,0) - NVL(l_sum_prepay_base_amt,0);
590
591 -- Check if the total of the accounted base amounts is equal to the base amount
592 -- for the distribution. If not create a final payment rounding to relieve
593 -- the liability completely.
594
595 ---------------------------------------------------------------------------------
596 -- bug 5570002
597 -- need to find the ERV/TERV amount of the invoice distribution and
598 -- exclude them from the invoide distrbution base amount
599 ---------------------------------------------------------------------------------
600
601 IF ( l_inv_dist_rec.line_type_lookup_code in ('ITEM', 'NONREC_TAX','ACCRUAL', 'TRV','IPV') --bug9398335 ,13043111(added IPV)
602 AND l_inv_dist_rec.related_id is not NULL ) THEN
603 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
604 l_log_msg := 'possible erv exists for invoice dist type =' ||
605 l_inv_dist_rec.line_type_lookup_code;
606 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
607 END IF;
608
609 BEGIN
610
611 SELECT NVL(base_amount, 0)
612 INTO l_erv_base_amount
613 FROM ap_invoice_distributions_all AID
614 WHERE AID.line_type_lookup_code in ('ERV', 'TERV')
615 AND AID.invoice_id = l_inv_dist_rec.invoice_id
616 AND AID.related_id = l_inv_dist_rec.invoice_distribution_id;
617 EXCEPTION
618 WHEN OTHERS THEN
619 l_erv_base_amount := 0;
620
621 END;
622
623 END IF;
624
625 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
626 l_log_msg := 'l_inv_dist_rec.base_amount = ' ||
627 l_inv_dist_rec.base_amount ||
628 'invoice dist ERV Amount = ' ||
629 l_erv_base_amount ||
630 'and l_total_dist_base_amt' ||
631 l_total_dist_base_amt;
632 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
633 END IF;
634
635
636 IF ( (l_inv_dist_rec.base_amount + l_erv_base_amount)
637 <> l_total_dist_base_amt) THEN
638
639 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
640 l_log_msg := 'Total of accounted base amt is not equal to base amount.'
641 || 'Creating a final payment rounding';
642 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
643 END IF;
644
645
646 /* If this is a prepayment type of event then insert the rounding distribution into
647 prepayment dists table. Otherwise insert into payment dists table */
648 IF (p_prepay_dist_rec.invoice_distribution_id IS NOT NULL) THEN
649
650 -- Get the prepay appl pay dists info for this distribution
651 --bug 7614480
652 /* SELECT APAD.*
653 INTO l_max_prepay_rec
654 FROM AP_Prepay_App_Dists APAD
655 WHERE APAD.Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id
656 AND APAD.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
657 AND APAD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
658 AND Rownum = 1;*/
659
663 l_pad_rec.accounting_event_id := p_xla_event_rec.event_id;
660 l_pad_rec.prepay_history_id := p_prepay_hist_rec.prepay_history_id;
661 --commented below for bug13334090 and added new sql to derive event id
662 IF p_xla_event_rec.event_id IS NOT NULL THEN
664 ELSE
665 IF p_prepay_hist_rec.prepay_history_id IS NOT NULL THEN
666 select accounting_event_id
667 into l_pad_rec.accounting_event_id
668 from ap_prepay_history_all
669 where prepay_history_id = p_prepay_hist_rec.prepay_history_id;
670 END IF;
671 END IF;
672
673 l_pad_rec.invoice_distribution_id := l_inv_dist_rec.invoice_distribution_id;
674 l_pad_rec.prepay_app_distribution_id := p_prepay_dist_rec.invoice_distribution_id;
675 l_pad_rec.prepay_dist_lookup_code := 'FINAL PAYMENT ROUNDING';
676
677 /*l_pad_rec.prepay_exchange_rate := l_max_prepay_rec.prepay_exchange_rate;
678 l_pad_rec.prepay_exchange_rate_type := l_max_prepay_rec.prepay_exchange_rate_type;
679 l_pad_rec.prepay_exchange_date := l_max_prepay_rec.prepay_exchange_date;
680 l_pad_rec.prepay_pay_exchange_rate := l_max_prepay_rec.prepay_pay_exchange_rate;
681 l_pad_rec.prepay_pay_exchange_rate_type := l_max_prepay_rec.prepay_pay_exchange_rate_type;
682 l_pad_rec.prepay_pay_exchange_date := l_max_prepay_rec.prepay_pay_exchange_date;
683 l_pad_rec.prepay_clr_exchange_rate := l_max_prepay_rec.prepay_clr_exchange_rate;
684 l_pad_rec.prepay_clr_exchange_rate_type := l_max_prepay_rec.prepay_clr_exchange_rate_type;
685 l_pad_rec.prepay_clr_exchange_date := l_max_prepay_rec.prepay_clr_exchange_date;
686 l_pad_rec.awt_related_id := l_max_prepay_rec.awt_related_id;*/ --bug 7614480
687
688 l_pad_rec.amount := 0;
689 l_pad_rec.base_amount := -(l_inv_dist_rec.base_amount + l_erv_base_amount - l_total_dist_base_amt); --bug 7614480 bug8889543
690 l_pad_rec.base_amt_at_prepay_xrate := 0;
691 l_pad_rec.base_amt_at_prepay_pay_xrate := 0;
692 l_pad_rec.base_amt_at_prepay_clr_xrate := 0;
693
694
695 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
696 l_log_msg := 'Calling procedure '||
697 'AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Insert';
698 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
699 END IF;
700
701 AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Insert
702 (l_pad_rec,
703 l_curr_calling_sequence);
704
705 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
706 l_log_msg := 'Procedure AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Insert '
707 || 'executed';
708 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
709 END IF;
710
711 ELSE
712
713 -- Get the payment hist info
714 /*SELECT APHD.*
715 INTO l_max_pay_rec
716 FROM AP_Payment_Hist_Dists APHD
717 WHERE APHD.Payment_History_ID = p_pay_hist_rec.payment_history_id
718 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
719 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
720 AND APHD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
721 AND Rownum = 1;*/ --bug 7614480
722
723 l_pd_rec.invoice_distribution_id := l_inv_dist_rec.invoice_distribution_id;
724 l_pd_rec.payment_history_id := p_pay_hist_rec.payment_history_id;
725 l_pd_rec.invoice_payment_id := p_inv_pay_rec.invoice_payment_id;
726 l_pd_rec.invoice_adjustment_event_id := p_pay_hist_rec.invoice_adjustment_event_id;
727 l_pd_rec.accounting_event_id := p_xla_event_rec.event_id;
728 l_pd_rec.pay_dist_lookup_code := 'FINAL PAYMENT ROUNDING';
729 --l_pd_rec.awt_related_id := l_max_pay_rec.awt_related_id; --bug 7614480
730
731 l_pd_rec.bank_curr_amount := 0;
732 l_pd_rec.invoice_dist_base_amount := l_inv_dist_rec.base_amount + l_erv_base_amount - l_total_dist_base_amt; --bug 8889543
733 l_pd_rec.amount := 0;
734 l_pd_rec.invoice_dist_amount := 0;
735 l_pd_rec.paid_base_amount := 0;
736 l_pd_rec.cleared_base_amount := 0;
737 l_pd_rec.matured_base_amount := 0;
738
739 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
740 l_log_msg := 'Calling procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert';
741 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
742 END IF;
743
744
745 AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert
746 (l_pd_rec,
747 l_curr_calling_sequence);
748
749 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
750 l_log_msg := 'Procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert executed';
751 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
752 END IF;
753
754
755 END IF;
756 END IF;
757 END IF;
758 l_erv_base_amount := 0; --added for bug 8910300 it shuld be zero after after every loop call
759 END LOOP;
760
761 -- Logging Infra: Procedure level
762 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
763 l_log_msg := 'End of procedure '|| l_procedure_name;
764 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
765 END IF;
766
767
771 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
768 EXCEPTION
769 WHEN OTHERS THEN
770 IF (SQLCODE <> -20001) THEN
772 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
773 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
774 END IF;
775 APP_EXCEPTION.RAISE_EXCEPTION;
776
777 END Final_Pay;
778
779
780 -------------------------------------------------------------------------------
781 -- PROCEDURE Total_Pay
782 -- This procedure calculates whether the payment his distribution records for
783 -- the event fully relieve the different base amounts and then create the
784 -- total payment rounding if the amounts are not fully relieved
785 --
786 --------------------------------------------------------------------------------
787 PROCEDURE Total_Pay
788 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
789 ,P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
790 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
791 ,P_Inv_Pay_Rec IN ap_acctg_pay_dist_pkg.r_inv_pay_info
792 ,P_Calling_Sequence IN VARCHAR2
793 ) IS
794
795 l_curr_calling_sequence VARCHAR2(2000);
796 l_payment_hist_id NUMBER;
797 l_invoice_dist_id NUMBER;
798 l_inv_adj_event_id NUMBER;
799
800 l_inv_rate_total_amt NUMBER := 0;
801 l_pay_rate_total_amt NUMBER := 0;
802 l_clr_rate_total_amt NUMBER := 0;
803 l_mat_rate_total_amt NUMBER := 0;
804 l_disc_pay_rate_total_amt NUMBER := 0;
805 l_disc_clr_rate_total_amt NUMBER := 0;
806 l_err_clr_rate_total_amt NUMBER := 0;
807 l_chrg_clr_rate_total_amt NUMBER := 0;
808
809 l_inv_rate_sum_amt NUMBER := 0;
810 l_pay_rate_sum_amt NUMBER := 0;
811 l_clr_rate_sum_amt NUMBER := 0;
812 l_mat_rate_sum_amt NUMBER := 0;
813 l_disc_pay_rate_sum_amt NUMBER := 0;
814 l_disc_clr_rate_sum_amt NUMBER := 0;
815 l_err_clr_rate_sum_amt NUMBER := 0;
816 l_chrg_clr_rate_sum_amt NUMBER := 0;
817
818 l_inv_rate_diff_amt NUMBER := 0;
819 l_pay_rate_diff_amt NUMBER := 0;
820 l_clr_rate_diff_amt NUMBER := 0;
821 l_mat_rate_diff_amt NUMBER := 0;
822 l_disc_pay_rate_diff_amt NUMBER := 0;
823 l_disc_clr_rate_diff_amt NUMBER := 0;
824 l_err_clr_rate_diff_amt NUMBER := 0;
825 l_chrg_clr_rate_diff_amt NUMBER := 0;
826
827 l_pd_rec AP_PAYMENT_HIST_DISTS%ROWTYPE;
828
829 -- Logging Infra:
830 l_procedure_name CONSTANT VARCHAR2(30) := 'Total_Pay';
831 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
832 l_inv_base_amt NUMBER := 0;--Bug6600117
833 l_inv_dist_diff_amt NUMBER := 0;
834 l_inv_amt NUMBER := 0;
835 l_pay_sum_amt NUMBER := 0;
836 l_inv_rate_sum_full_amt NUMBER := 0;
837 l_max_dist_id NUMBER;
838
839 l_clr_rate_rounding_amt NUMBER :=0;
840 l_mat_rate_rounding_amt NUMBER :=0;
841 l_pay_rate_rounding_amt NUMBER :=0;
842 l_disc_pay_rounding_amt NUMBER :=0;
843 l_disc_clr_rounding_amt NUMBER :=0;
844 l_err_clr_rounding_amt NUMBER :=0;
845 l_chrg_clr_rounding_amt NUMBER :=0;
846 l_inv_dist_rounding_amt NUMBER :=0;
847
848 -- bug 8403738
849 l_pre_clr_rate_rounding_amt NUMBER :=0;
850 l_pre_mat_rate_rounding_amt NUMBER :=0;
851 l_pre_pay_rate_rounding_amt NUMBER :=0;
852 l_pre_disc_pay_rounding_amt NUMBER :=0;
853 l_pre_disc_clr_rounding_amt NUMBER :=0;
854 l_pre_err_clr_rounding_amt NUMBER :=0;
855 l_pre_chrg_clr_rounding_amt NUMBER :=0;
856 l_pre_inv_dist_rounding_amt NUMBER :=0;
857
858 l_pay_hist_id NUMBER;
859 l_inv_dist_id NUMBER;
860 l_inv_dist_amt NUMBER :=0;
861 l_inv_dist_base_amt NUMBER :=0;
862 l_inv_mat_base_amt NUMBER :=0;
863 l_inv_paid_base_amt NUMBER :=0;
864 l_inv_clr_base_amt NUMBER :=0;
865
866 --vasvenka
867 l_inv_dist_amt_disc NUMBER :=0;
868 l_inv_dist_base_amt_disc NUMBER :=0;
869 l_inv_mat_base_amt_disc NUMBER :=0;
870 l_inv_paid_base_amt_disc NUMBER :=0;
871 l_inv_clr_base_amt_disc NUMBER :=0;
872 --vasvenka
873
874 l_rate_type AP_PAYMENT_HISTORY.Pmt_To_Base_XRate_Type%TYPE; --9849243
875 l_rate_date AP_PAYMENT_HISTORY.Pmt_To_Base_XRate_Date%TYPE; --9849243
876 l_rate AP_PAYMENT_HISTORY.Pmt_To_Base_XRate%TYPE; --9849243
877
878 BEGIN
879
880 l_curr_calling_sequence := 'AP_ACCTG_PAY_ROUND_PKG.Total_Pay<- ' ||
881 p_calling_sequence;
882
883 -- Logging Infra: Procedure level
884 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
885 l_log_msg := 'Begin of procedure '|| l_procedure_name;
886 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
887 END IF;
888
889 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
890 l_log_msg := 'query to fetch largest distribution' ||
891 'for xla event id ' || p_xla_event_rec.event_id ||
892 'for payment id ' || p_inv_pay_rec.Invoice_Payment_ID ||
893 'and invoice id ' || p_inv_rec.invoice_id;
894 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
895 END IF;
896 -- Get the max of the largest distribution for inserting the
897 -- total payment rounding
898 BEGIN
899 SELECT APHD.Payment_History_ID,
900 APHD.Invoice_Distribution_ID,
901 APHD.Invoice_Adjustment_Event_ID
902 INTO l_payment_hist_id,
903 l_invoice_dist_id,
904 l_inv_adj_event_id
908 FROM AP_Payment_Hist_Dists APHD1
905 FROM AP_Payment_Hist_Dists APHD
906 WHERE APHD.Invoice_Distribution_ID =
907 (SELECT MAX(APHD1.Invoice_Distribution_ID)
909 WHERE APHD1.Accounting_Event_ID = p_xla_event_rec.event_id
910 AND APHD1.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
911 AND APHD1.Invoice_Distribution_ID IN
912 (SELECT AID.Invoice_Distribution_ID
913 FROM AP_Invoice_Distributions_All AID
914 WHERE AID.Invoice_ID = p_inv_rec.invoice_id)
915 AND APHD1.Pay_Dist_Lookup_Code not IN('AWT') --8727277
916 AND ABS(APHD1.Amount) =
917 (SELECT MAX(ABS(APHD2.Amount))
918 FROM AP_Payment_Hist_Dists APHD2
919 WHERE APHD2.Accounting_Event_ID = p_xla_event_rec.event_id
920 AND APHD2.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
921 AND APHD2.Invoice_Distribution_ID IN
922 (SELECT AID.Invoice_Distribution_ID
923 FROM AP_Invoice_Distributions_All AID
924 WHERE AID.Invoice_ID = p_inv_rec.invoice_id)
925 AND APHD2.Pay_Dist_Lookup_Code not IN('AWT') --8727277
926 ))
927 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
928 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
929 AND Rownum = 1;
930 EXCEPTION
931 WHEN NO_DATA_FOUND THEN
932 RAISE_APPLICATION_ERROR(-20100, l_procedure_name ||
933 ' no_record_in_aphd_while_retrieving_max_dist');
934 END; --bug 9936620
935
936 l_pay_rate_total_amt := p_inv_pay_rec.payment_base_amount;
937 l_inv_rate_total_amt := p_inv_pay_rec.invoice_base_amount;
938
939 IF p_inv_pay_rec.discount_taken <> 0 THEN
940
941 l_disc_pay_rate_total_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
942 (p_inv_pay_rec.discount_taken,
943 p_pay_hist_rec.pmt_currency_code,
944 ap_accounting_pay_pkg.g_base_currency_code,
945 p_pay_hist_rec.pmt_to_base_xrate_type,
946 p_pay_hist_rec.pmt_to_base_xrate_date,
947 p_pay_hist_rec.pmt_to_base_xrate,
948 l_curr_calling_sequence);
949
950 END IF;
951
952 -- Get the sum of the base amounts for each line type from the payment hist dists.
953 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
954 l_log_msg := 'query to fetch the sum of base amounts' ||
955 'for related event id ' || p_pay_hist_rec.Related_Event_ID ||
956 'for payment id ' || p_inv_pay_rec.Invoice_Payment_ID ||
957 'and invoice id ' || p_inv_rec.invoice_id;
958 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
959 END IF;
960
961 SELECT SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'CASH', APHD.Invoice_Dist_Base_Amount, 0)),
962 SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'CASH', APHD.Paid_Base_Amount, 0)),
963 SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'CASH', APHD.Cleared_Base_Amount, 0)),
964 SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'CASH', APHD.Matured_Base_Amount, 0)),
965 SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'DISCOUNT', APHD.Paid_Base_Amount, 0)),
966 SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'DISCOUNT', APHD.Cleared_Base_Amount, 0)),
967 SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'BANK ERROR', APHD.Cleared_Base_Amount, 0)),
968 SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'BANK CHARGE', APHD.Cleared_Base_Amount, 0))
969 INTO l_inv_rate_sum_amt,
970 l_pay_rate_sum_amt,
971 l_clr_rate_sum_amt,
972 l_mat_rate_sum_amt,
973 l_disc_pay_rate_sum_amt,
974 l_disc_clr_rate_sum_amt,
975 l_err_clr_rate_sum_amt,
976 l_chrg_clr_rate_sum_amt
977 FROM AP_Payment_Hist_Dists APHD,
978 AP_Payment_History_All APH
979 WHERE APH.Related_Event_ID = p_pay_hist_rec.Related_Event_ID
980 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.Invoice_Payment_ID
981 AND APHD.Payment_History_ID = APH.Payment_History_ID
982 AND APHD.Invoice_Distribution_ID IN
983 (SELECT AID.Invoice_Distribution_ID
984 FROM AP_Invoice_Distributions_All AID
985 WHERE AID.Invoice_ID = p_inv_rec.invoice_id);
986
987 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
988 l_log_msg := ' sum of the base amounts for each line type obtained';
989 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
990 END IF;
991
992
993 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
994 l_log_msg := 'CASH lines Invoice_Dist_Base_Amount sum= '||
995 l_inv_rate_sum_amt ||
996 'CASH lines Paid_Base_Amount sum= ' ||
997 l_pay_rate_sum_amt ||
998 'CASH lines Cleared_Base_Amount sum= '||
999 l_clr_rate_sum_amt ||
1000 'CASH lines Matured_Base_Amount sum= '||
1001 l_mat_rate_sum_amt;
1002 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1003 END IF;
1004
1005 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1006 l_log_msg := 'DISCOUNT lines Paid_Base_Amount sum= ' ||
1007 l_disc_pay_rate_sum_amt ||
1008 'DISCOUNT lines Cleared_Base_Amount sum= '||
1009 l_disc_clr_rate_sum_amt;
1013 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1010 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1011 END IF;
1012
1014 l_log_msg := 'BANK ERROR Cleared_Base_Amount sum= ' ||
1015 l_err_clr_rate_sum_amt ;
1016 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1017 END IF;
1018
1019 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1020 l_log_msg := 'BANK CHARGE Cleared_Base_Amount sum= ' ||
1021 l_chrg_clr_rate_sum_amt ;
1022 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1023 END IF;
1024
1025
1026 /*Bug6600117 used below query to fetch the sum(amount) and sum(base_amount)
1027 from ap_payment_history and AP_Payment_Hist_dists tables to be
1028 used later in the calculation*/
1029 -- Bug 6649025
1030 SELECT sum(nvl(APHD.amount,0)),
1031 max(APHD.Invoice_Distribution_Id)
1032 ,SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'CASH', APHD.Invoice_Dist_Base_Amount,
1033 'AWT', APHD.Invoice_Dist_Base_Amount,
1034 'DISCOUNT', APHD.Invoice_Dist_Base_Amount, 0))
1035 INTO l_pay_sum_amt,l_max_dist_id,l_inv_rate_sum_full_amt
1036 FROM AP_PAYMENT_HIST_DISTS APHD,
1037 AP_PAYMENT_HISTORY_ALL APH
1038 WHERE
1039 APHD.PAYMENT_HISTORY_ID =APH.PAYMENT_HISTORY_ID
1040 ANd APHD.INVOICE_PAYMENT_ID =p_inv_pay_rec.Invoice_Payment_ID --6614295
1041 AND APHD.Invoice_Distribution_ID IN
1042 (SELECT AID.Invoice_Distribution_ID
1043 FROM AP_Invoice_Distributions_All AID
1044 WHERE AID.Invoice_ID = p_inv_rec.invoice_id);
1045
1046 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1047 l_log_msg := 'query to fetch the sum(amount) and sum(base_amount)' ||
1048 'from ap_payment_history and AP_Payment_Hist_dists tables executed' ||
1049 'for payment id ' || p_inv_pay_rec.Invoice_Payment_ID ||
1050 'and invoice id ' || p_inv_rec.invoice_id;
1051 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1052 END IF;
1053
1054 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1055 l_log_msg := 'l_pay_sum_amt ' || l_pay_sum_amt;
1056 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1057 END IF;
1058
1059 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1060 l_log_msg := 'l_max_dist_id ' || l_max_dist_id;
1061 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1062 END IF;
1063
1064
1065 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1066 l_log_msg := 'l_inv_rate_sum_full_amt ' || l_inv_rate_sum_full_amt;
1067 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1068 END IF;
1069
1070 SELECT sum(AI.invoice_amount) ,sum(AI.base_amount)
1071 INTO l_inv_amt,l_inv_base_amt
1072 FROM ap_invoices_all AI
1073 WHERE AI.invoice_id = p_inv_rec.invoice_id;
1074
1075 IF (p_xla_event_rec.event_type_code IN ('PAYMENT CLEARED',
1076 'PAYMENT CLEARING ADJUSTED')) THEN
1077
1078 l_clr_rate_total_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
1079 (p_inv_pay_rec.amount,
1080 p_pay_hist_rec.pmt_currency_code,
1081 ap_accounting_pay_pkg.g_base_currency_code,
1082 p_pay_hist_rec.pmt_to_base_xrate_type,
1083 p_pay_hist_rec.pmt_to_base_xrate_date,
1084 p_pay_hist_rec.pmt_to_base_xrate,
1085 l_curr_calling_sequence);
1086
1087
1088 IF p_inv_pay_rec.discount_taken <> 0 THEN
1089
1090 l_disc_clr_rate_total_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
1091 (p_inv_pay_rec.discount_taken,
1092 p_pay_hist_rec.pmt_currency_code,
1093 ap_accounting_pay_pkg.g_base_currency_code,
1094 p_pay_hist_rec.pmt_to_base_xrate_type,
1095 p_pay_hist_rec.pmt_to_base_xrate_date,
1096 p_pay_hist_rec.pmt_to_base_xrate,
1097 l_curr_calling_sequence);
1098 END IF;
1099
1100 IF p_pay_hist_rec.errors_bank_amount <> 0 THEN
1101
1102 l_err_clr_rate_total_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
1103 (p_pay_hist_rec.errors_bank_amount,
1104 p_pay_hist_rec.pmt_currency_code,
1105 ap_accounting_pay_pkg.g_base_currency_code,
1106 p_pay_hist_rec.pmt_to_base_xrate_type,
1107 p_pay_hist_rec.pmt_to_base_xrate_date,
1108 p_pay_hist_rec.pmt_to_base_xrate,
1109 l_curr_calling_sequence);
1110 END IF;
1111
1112 IF p_pay_hist_rec.charges_bank_amount <> 0 THEN
1113
1114 l_chrg_clr_rate_total_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
1115 (p_pay_hist_rec.charges_bank_amount,
1116 p_pay_hist_rec.pmt_currency_code,
1117 ap_accounting_pay_pkg.g_base_currency_code,
1118 p_pay_hist_rec.pmt_to_base_xrate_type,
1119 p_pay_hist_rec.pmt_to_base_xrate_date,
1123 END IF;
1120 p_pay_hist_rec.pmt_to_base_xrate,
1121 l_curr_calling_sequence);
1122
1124 END IF; --bug 9765359
1125
1126 --bug 9765359, replaced ELSIF with IF
1127 --added event_types CLEARED and CLEARING_ADJUSTED as matured_base_amount
1128 --is updated for both MATURITY and CLEARING transactions
1129 IF (p_xla_event_rec.event_type_code IN ('PAYMENT MATURED',
1130 'PAYMENT MATURITY ADJUSTED',
1131 'PAYMENT CLEARED',
1132 'PAYMENT CLEARING ADJUSTED')) THEN
1133
1134 --9849243 Mat Rate total amt should be calculated with Maturity Exchange Rate
1135 IF ( ap_accounting_pay_pkg.g_mat_to_base_xrate_type IS NOT NULL AND
1136 ap_accounting_pay_pkg.g_mat_to_base_xrate_date IS NOT NULL AND
1137 ap_accounting_pay_pkg.g_mat_to_base_xrate IS NOT NULL) THEN
1138
1139 l_rate_type := ap_accounting_pay_pkg.g_mat_to_base_xrate_type;
1140 l_rate_date := ap_accounting_pay_pkg.g_mat_to_base_xrate_date;
1141 l_rate := ap_accounting_pay_pkg.g_mat_to_base_xrate;
1142 ELSE
1143 l_rate_type := p_pay_hist_rec.pmt_to_base_xrate_type;
1144 l_rate_date := p_pay_hist_rec.pmt_to_base_xrate_date;
1145 l_rate := p_pay_hist_rec.pmt_to_base_xrate;
1146 END IF; --9849243 Ends
1147
1148 l_mat_rate_total_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
1149 (p_inv_pay_rec.amount,
1150 p_pay_hist_rec.pmt_currency_code,
1151 ap_accounting_pay_pkg.g_base_currency_code,
1152 l_rate_type, --9849243
1153 l_rate_date, --9849243
1154 l_rate, --9849243
1155 l_curr_calling_sequence);
1156
1157 END IF;
1158
1159 --bug 9765359,
1160 --1. removed 'ELSE' and 'END IF' surrounding the code for calculating
1161 -- l_pay_rate_total_amt and l_disc_pay_rate_total_amt as these need to be
1162 -- calculated for all types of transactions--CREATION, MATURITY and CLEARING
1163 --2. l_pay_rate_total_amt is assigned p_inv_pay_rec.payment_base_amount
1164 -- earlier in this procedure, reassignment is redundant
1165
1166 /*l_pay_rate_total_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
1167 (p_inv_pay_rec.amount,
1168 p_pay_hist_rec.pmt_currency_code,
1169 ap_accounting_pay_pkg.g_base_currency_code,
1170 p_pay_hist_rec.pmt_to_base_xrate_type,
1171 p_pay_hist_rec.pmt_to_base_xrate_date,
1172 p_pay_hist_rec.pmt_to_base_xrate,
1173 l_curr_calling_sequence);*/
1174
1175 IF p_inv_pay_rec.discount_taken <> 0 THEN
1176
1177 l_disc_pay_rate_total_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
1178 (p_inv_pay_rec.discount_taken,
1179 p_pay_hist_rec.pmt_currency_code,
1180 ap_accounting_pay_pkg.g_base_currency_code,
1181 p_pay_hist_rec.pmt_to_base_xrate_type,
1182 p_pay_hist_rec.pmt_to_base_xrate_date,
1183 p_pay_hist_rec.pmt_to_base_xrate,
1184 l_curr_calling_sequence);
1185
1186 END IF;
1187 --bug 9765359 end
1188
1189 l_pay_rate_diff_amt := l_pay_rate_total_amt - l_pay_rate_sum_amt;
1190
1191 /* If the exchange rates between the invoice and payment are same then the base
1192 amounts and rounding between the invoice and payment should be same */
1193 /*Bug6600117
1194 The fractional rounding amount is added to the maximun distribution amount
1195 in ap_invoice_distributions_all to balance them in respect to the header base amount.
1196 Same is not done while calculating invoice_dist_Base_Amount in AP_Payment_Hist_Dists
1197 table.This is giving rise to the unbalance accounting entries in accounting journal*/
1198
1199 IF (p_pay_hist_rec.pmt_to_base_xrate =
1200 p_inv_rec.exchange_rate / p_inv_rec.payment_cross_rate) THEN
1201 l_inv_rate_diff_amt := l_pay_rate_diff_amt;
1202 ELSE
1203 l_inv_rate_diff_amt := l_inv_rate_total_amt - l_inv_rate_sum_amt;
1204
1205 END IF;
1206
1207 /* Bug660017calculate the difference between AP_invoice_distribution
1208 base amount sum and AP_Payment_Hist_Dists base amount sum */
1209 l_inv_dist_diff_amt := l_inv_base_amt - l_inv_rate_sum_full_amt;
1210
1211 --Bug6600117
1212
1213 --If invoice is fully paid and there is fractional unbalance
1214 /* IF (l_pay_sum_amt = l_inv_amt) and NVL(l_inv_dist_diff_amt,0) <> 0 THEN
1215 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1216 l_log_msg := 'Updating rounding amount for l_inv_dist_diff_amt';
1217 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1218 END IF;
1219
1220 Bug660017 If the invoice is paid in full by this payment and there is a
1221 difference of amount then adjust the maximum distribution with the fractional
1222 amount*
1223 -- Bug 6649025
1224
1225 --Bug 7270829 - the same update has been done below , hence commenting out this code.
1226 UPDATE AP_Payment_Hist_Dists APHD
1227 SET APHD.invoice_dist_Base_Amount = APHD.invoice_dist_Base_Amount + NVL(l_inv_dist_diff_amt,0)
1228 WHERE APHD.Invoice_Distribution_ID = l_invoice_dist_id -- l_max_dist_id
1232 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1229 AND APHD.Pay_Dist_Lookup_Code IN ('CASH')
1230 AND APHD.Payment_History_ID = l_payment_hist_id
1231 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1233 AND APHD.PAYMENT_HIST_DIST_ID= (select max(APHD1.PAYMENT_HIST_DIST_ID)
1234 from AP_Payment_Hist_Dists APHD1
1235 where APHD1.invoice_distribution_id = l_invoice_dist_id); --l_max_dist_id);
1236
1237 --Bug 7270829
1238
1239 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1240 l_log_msg := 'Updated rounding amount for l_pay_rate_diff_amt';
1241 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1242 END IF;
1243 END IF;
1244
1245 If there is a difference between the total and sum amounts then we will insert
1246 the difference as the rounding amounts */
1247
1248 l_clr_rate_diff_amt := l_clr_rate_total_amt - l_clr_rate_sum_amt;
1249 l_mat_rate_diff_amt := l_mat_rate_total_amt - l_mat_rate_sum_amt;
1250 l_disc_pay_rate_diff_amt := l_disc_pay_rate_total_amt - l_disc_pay_rate_sum_amt;
1251 l_disc_clr_rate_diff_amt := l_disc_clr_rate_total_amt - l_disc_clr_rate_sum_amt;
1252 l_err_clr_rate_diff_amt := l_err_clr_rate_total_amt - l_err_clr_rate_sum_amt;
1253 l_chrg_clr_rate_diff_amt := l_chrg_clr_rate_total_amt - l_chrg_clr_rate_sum_amt;
1254
1255 -- Bug fix 6314128 Starts
1256 -- Handling the difference amount in l_inv_rate_diff_amt
1257 -- Rule: SUM(AP_PAYMENT_HIST_DISTS.INVOICE_DIST_BASE_AMOUNT) =
1258 -- AP_INVOICE_PAYMENTS_ALL.INVOICE_BASE_AMOUNT
1259
1260 /*Commenting out as added new code to avoid negative base amounts due to rounding Bug-7156680
1261 IF NVL(l_inv_rate_diff_amt,0) <> 0 THEN
1262
1263 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1264 l_log_msg := 'Updating rounding amount for l_inv_rate_diff_amt';
1265 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1266 END IF;
1267
1268 UPDATE AP_Payment_Hist_Dists APHD
1269 SET APHD.Invoice_Dist_Base_Amount =
1270 APHD.Invoice_Dist_Base_Amount + NVL(l_inv_rate_diff_amt,0),
1271 APHD.Rounding_Amt = l_inv_rate_diff_amt
1272 WHERE APHD.Payment_History_ID = l_payment_hist_id
1273 AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
1274 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1275 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1276 AND APHD.Pay_Dist_Lookup_Code = 'CASH';
1277
1278 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1279 l_log_msg := 'Updated rounding amount for l_inv_rate_diff_amt';
1280 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1281 END IF;
1282
1283
1284 END IF;
1285 -- BUG 6314128 ENDS;
1286
1287 -- for bug fix 5694577
1288 -- Added the event_type_code chack and rearranged the if statements
1289 IF NVL(l_clr_rate_diff_amt,0) <> 0 AND
1290 (p_xla_event_rec.event_type_code IN ('PAYMENT CLEARED',
1291 'PAYMENT CLEARING ADJUSTED'))
1292 THEN
1293
1294 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1295 l_log_msg := 'Updating rounding amount for l_clr_rate_diff_amt';
1296 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1297 END IF;
1298
1299
1300 UPDATE AP_Payment_Hist_Dists APHD
1301 SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount
1302 + NVL(l_clr_rate_diff_amt,0),
1303 APHD.Rounding_Amt = l_clr_rate_diff_amt
1304 WHERE APHD.Payment_History_ID = l_payment_hist_id
1305 AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
1306 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1307 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1308 AND APHD.Pay_Dist_Lookup_Code = 'CASH';
1309
1310
1311 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1312 l_log_msg := 'Updated rounding amount for l_clr_rate_diff_amt';
1313 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1314 END IF;
1315
1316 ELSIF NVL(l_mat_rate_diff_amt,0) <> 0 AND
1317 (p_xla_event_rec.event_type_code IN ('PAYMENT MATURED',
1318 'PAYMENT MATURITY ADJUSTED'))
1319 THEN
1320
1321 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1322 l_log_msg := 'Updating rounding amount for l_mat_rate_diff_amt';
1323 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1324 END IF;
1325
1326 UPDATE AP_Payment_Hist_Dists APHD
1327 SET APHD.Matured_Base_Amount = APHD.Matured_Base_Amount
1328 + NVL(l_mat_rate_diff_amt,0),
1329 APHD.Rounding_Amt = l_mat_rate_diff_amt
1330 WHERE APHD.Payment_History_ID = l_payment_hist_id
1331 AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
1332 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1333 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1334 AND APHD.Pay_Dist_Lookup_Code = 'CASH';
1335
1336
1337 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1338 l_log_msg := 'Updated rounding amount for l_mat_rate_diff_amt';
1339 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1340 END IF;
1341
1342 ELSIF NVL(l_pay_rate_diff_amt,0) <> 0 THEN
1343
1344 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1345 l_log_msg := 'Updating rounding amount for l_pay_rate_diff_amt';
1346 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1347 END IF;
1348
1352 APHD.Rounding_Amt = l_pay_rate_diff_amt
1349
1350 UPDATE AP_Payment_Hist_Dists APHD
1351 SET APHD.Paid_Base_Amount = APHD.Paid_Base_Amount + NVL(l_pay_rate_diff_amt,0),
1353 WHERE APHD.Payment_History_ID = l_payment_hist_id
1354 AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
1355 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1356 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1357 AND APHD.Pay_Dist_Lookup_Code = 'CASH';
1358
1359
1360 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1361 l_log_msg := 'Updated rounding amount for l_pay_rate_diff_amt';
1362 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1363 END IF;
1364
1365 END IF;
1366
1367 IF NVL(l_disc_pay_rate_diff_amt,0) <> 0 THEN
1368
1369 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1370 l_log_msg := 'Updating discount rounding amount for payment';
1371 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1372 END IF;
1373
1374
1375 UPDATE AP_Payment_Hist_Dists APHD
1376 SET APHD.Paid_Base_Amount = APHD.Paid_Base_Amount
1377 + NVL(l_disc_pay_rate_diff_amt,0),
1378 APHD.Rounding_Amt = l_disc_pay_rate_diff_amt
1379 WHERE APHD.Payment_History_ID = l_payment_hist_id
1380 AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
1381 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1382 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1383 AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
1384
1385
1386 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1387 l_log_msg := 'Updated discount rounding amount for payment';
1388 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1389 END IF;
1390
1391
1392 END IF;
1393
1394 IF NVL(l_disc_clr_rate_diff_amt,0) <> 0 THEN
1395
1396 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1397 l_log_msg := 'Updating discount rounding amount for clearing';
1398 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1399 END IF;
1400
1401
1402 UPDATE AP_Payment_Hist_Dists APHD
1403 SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount
1404 + NVL(l_disc_clr_rate_diff_amt,0),
1405 APHD.Rounding_Amt = l_disc_clr_rate_diff_amt
1406 WHERE APHD.Payment_History_ID = l_payment_hist_id
1407 AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
1408 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1409 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1410 AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
1411
1412
1413 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1414 l_log_msg := 'Updated discount rounding amount for clearing';
1415 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1416 END IF;
1417
1418
1419 END IF;
1420
1421
1422 IF NVL(l_err_clr_rate_diff_amt,0) <> 0 THEN
1423
1424 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1425 l_log_msg := 'Updating error rounding amount';
1426 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1427 END IF;
1428
1429
1430 UPDATE AP_Payment_Hist_Dists APHD
1431 SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount
1432 + NVL(l_err_clr_rate_diff_amt,0),
1433 APHD.Rounding_Amt = l_err_clr_rate_diff_amt
1434 WHERE APHD.Payment_History_ID = l_payment_hist_id
1435 AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
1436 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1437 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1438 AND APHD.Pay_Dist_Lookup_Code = 'BANK ERROR';
1439
1440
1441 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1442 l_log_msg := 'Updated error rounding amount';
1443 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1444 END IF;
1445
1446
1447 END IF;
1448
1449
1450 IF NVL(l_chrg_clr_rate_diff_amt,0) <> 0 THEN
1451
1452 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1453 l_log_msg := 'Updating charge rounding amount';
1454 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1455 END IF;
1456
1457
1458 UPDATE AP_Payment_Hist_Dists APHD
1459 SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount
1460 + NVL(l_chrg_clr_rate_diff_amt,0),
1461 APHD.Rounding_Amt = l_chrg_clr_rate_diff_amt
1462 WHERE APHD.Payment_History_ID = l_payment_hist_id
1463 AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
1464 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1465 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1466 AND APHD.Pay_Dist_Lookup_Code = 'BANK CHARGE';
1467
1468
1469 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1470 l_log_msg := 'Updated charge rounding amount';
1471 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1472 END IF;
1473
1474
1475 END IF;
1476 */
1477
1478 /*Bug 7156680
1479 Base amount becomes negative when the the rounding amount is larger than the
1480 distribution having maximum amount to avoid this split the rounding amount among the
1481 distributions such that the base amount is never allowed to become negative
1485
1482 The rounding amount is applied to a paritcular distribution if the base amount goes negative
1483 it is set to zero and the rest of the rounding amount is applied to the next distribution
1484 until the complete rounding amount is consumed*/
1486
1487 l_clr_rate_rounding_amt := NVL(l_clr_rate_diff_amt, 0);
1488 l_mat_rate_rounding_amt := NVL(l_mat_rate_diff_amt, 0);
1489 l_pay_rate_rounding_amt := NVL(l_pay_rate_diff_amt, 0);
1490 l_disc_pay_rounding_amt := NVL(l_disc_pay_rate_diff_amt, 0);
1491 l_disc_clr_rounding_amt := NVL(l_disc_clr_rate_diff_amt, 0);
1492 l_err_clr_rounding_amt := NVL(l_err_clr_rate_diff_amt, 0);
1493 l_chrg_clr_rounding_amt := NVL(l_chrg_clr_rate_diff_amt, 0);
1494 l_inv_dist_rounding_amt := NVL(l_inv_rate_diff_amt, 0);
1495 l_pay_hist_id := l_payment_hist_id;
1496 l_inv_dist_id := l_invoice_dist_id;
1497
1498 WHILE (l_clr_rate_rounding_amt <> 0 or l_mat_rate_rounding_amt <> 0 or
1499 l_disc_pay_rounding_amt <> 0 or l_disc_clr_rounding_amt <> 0 or
1500 l_err_clr_rounding_amt <> 0 or l_chrg_clr_rounding_amt <> 0 or
1501 l_inv_dist_rounding_amt <> 0
1502 or l_pay_rate_rounding_amt <> 0) LOOP -- bug 8725482
1503
1504 SELECT APHD.Invoice_Dist_Amount,
1505 APHD.Invoice_Dist_Base_Amount,
1506 APHD.matured_base_amount,
1507 APHD.paid_base_Amount,
1508 APHD.cleared_base_amount
1509 INTO l_inv_dist_amt,
1510 l_inv_dist_base_amt,
1511 l_inv_mat_base_amt,
1512 l_inv_paid_base_amt,
1513 l_inv_clr_base_amt
1514 FROM AP_Payment_Hist_Dists APHD
1515 WHERE APHD.Payment_History_ID = l_pay_hist_id
1516 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
1517 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1518 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1519 AND APHD.Pay_Dist_Lookup_Code = 'CASH';
1520
1521 --8449083
1522 l_pre_inv_dist_rounding_amt := l_inv_dist_rounding_amt;
1523 l_pre_clr_rate_rounding_amt := l_clr_rate_rounding_amt;
1524 l_pre_mat_rate_rounding_amt := l_mat_rate_rounding_amt;
1525 l_pre_pay_rate_rounding_amt := l_pay_rate_rounding_amt;
1526 l_pre_disc_pay_rounding_amt := l_disc_pay_rounding_amt;
1527 l_pre_disc_clr_rounding_amt := l_disc_clr_rounding_amt;
1528 l_pre_err_clr_rounding_amt := l_err_clr_rounding_amt;
1529 l_pre_chrg_clr_rounding_amt := l_chrg_clr_rounding_amt;
1530
1531
1532 IF NVL(l_inv_dist_rounding_amt, 0) <> 0 THEN --8449083
1533
1534 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1535 l_log_msg := 'Updating rounding amount for l_inv_rate_diff_amt';
1536 FND_LOG.STRING(G_LEVEL_STATEMENT,
1537 G_MODULE_NAME || l_procedure_name,
1538 l_log_msg);
1539 END IF;
1540
1541 IF (sign(l_inv_dist_base_amt + l_inv_dist_rounding_amt) <>
1542 sign(l_inv_dist_amt) and
1543 (l_inv_dist_base_amt + l_inv_dist_rounding_amt) <> 0) then
1544 -- bug 8403738
1545 l_pre_inv_dist_rounding_amt := l_inv_dist_rounding_amt;
1546 l_inv_dist_rounding_amt := l_inv_dist_base_amt +
1547 l_inv_dist_rounding_amt;
1548
1549 -- bug 8403738
1550 if sign(l_pre_inv_dist_rounding_amt) = sign(l_inv_dist_rounding_amt) AND
1551 abs(l_inv_dist_rounding_amt) >= abs(l_pre_inv_dist_rounding_amt)
1552 then
1553 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1554
1555 l_log_msg := 'Error : Entered into situation which will lead to infinite loop.'||
1556 'There might be some data corrption. Check the following transaction';
1557 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1558 l_log_msg);
1559
1560 l_log_msg := 'event_id = '||p_xla_event_rec.event_id||
1561 ', invoice_payment_id = '||p_inv_pay_rec.invoice_payment_id||
1562 ', invoice_distribution_id = '||l_inv_dist_id||
1563 ', payment_history_id = '||l_pay_hist_id;
1564 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1565 l_log_msg);
1566
1567 l_log_msg := 'l_inv_dist_rounding_amt = '||l_inv_dist_rounding_amt||
1568 'l_pre_inv_dist_rounding_amt = '||l_pre_inv_dist_rounding_amt;
1569 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1570 l_log_msg);
1571
1572 l_log_msg := 'rounding failed and exiting loop!!!';
1573 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1574 l_log_msg);
1575 END IF;
1576
1577 exit;
1578 end if;
1579
1580 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1581 l_log_msg := ' l_inv_dist_rounding_amt' ||
1582 to_char(l_inv_dist_rounding_amt);
1583 FND_LOG.STRING(G_LEVEL_STATEMENT,
1584 G_MODULE_NAME || l_procedure_name,
1585 l_log_msg);
1586 END IF;
1587 UPDATE AP_Payment_Hist_Dists APHD
1588 SET APHD.Invoice_Dist_Base_Amount = 0,
1589 APHD.Rounding_Amt = -sign(l_inv_dist_amt)*l_inv_dist_base_amt
1590 WHERE APHD.Payment_History_ID = l_pay_hist_id
1591 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
1592 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1593 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1594 AND APHD.Pay_Dist_Lookup_Code = 'CASH';
1595
1596 ELSE
1597 UPDATE AP_Payment_Hist_Dists APHD
1598 SET APHD.Invoice_Dist_Base_Amount = APHD.Invoice_Dist_Base_Amount +
1599 NVL(l_inv_dist_rounding_amt,
1603 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
1600 0),
1601 APHD.Rounding_Amt=l_inv_dist_rounding_amt
1602 WHERE APHD.Payment_History_ID = l_pay_hist_id
1604 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1605 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1606 AND APHD.Pay_Dist_Lookup_Code = 'CASH';
1607 l_inv_dist_rounding_amt := 0;
1608 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1609 l_log_msg := ' Rounding complete for l_inv_rate_diff_amt';
1610 FND_LOG.STRING(G_LEVEL_STATEMENT,
1611 G_MODULE_NAME || l_procedure_name,
1612 l_log_msg);
1613 END IF;
1614 END IF;
1615 /*End Changes BUG 7156680 Changes done to avoid negative rounding amount*/
1616 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1617 l_log_msg := 'Updated rounding amount for l_inv_rate_diff_amt';
1618 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1619 G_MODULE_NAME || l_procedure_name,
1620 l_log_msg);
1621 END IF;
1622 END IF;
1623
1624 /*End Changes BUG 7156680 Changes done to avoid negative rounding amount*/
1625
1626 -- BUG 6314128 ENDS;
1627
1628 -- for bug fix 5694577
1629 -- Added the event_type_code chack and rearranged the if statements
1630 IF NVL(l_clr_rate_rounding_amt, 0) <> 0 THEN --8449083
1631 /*AND (p_xla_event_rec.event_type_code IN
1632 ('PAYMENT CLEARED', 'PAYMENT CLEARING ADJUSTED')) THEN*/ --bug 8735895
1633
1634 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1635 l_log_msg := 'Updating rounding amount for l_clr_rate_diff_amt';
1636 FND_LOG.STRING(G_LEVEL_STATEMENT,
1637 G_MODULE_NAME || l_procedure_name,
1638 l_log_msg);
1639 END IF;
1640
1641 IF (sign(l_inv_clr_base_amt + l_clr_rate_rounding_amt) <>
1642 sign(l_inv_dist_amt) and
1643 (l_inv_clr_base_amt + l_clr_rate_rounding_amt) <> 0) then
1644 -- bug 8403738
1645 l_pre_clr_rate_rounding_amt := l_clr_rate_rounding_amt;
1646 l_clr_rate_rounding_amt := l_inv_clr_base_amt +
1647 l_clr_rate_rounding_amt;
1648
1649 -- bug 8403738
1650 if sign(l_pre_clr_rate_rounding_amt) = sign(l_clr_rate_rounding_amt)
1651 AND abs(l_clr_rate_rounding_amt) >= abs(l_pre_clr_rate_rounding_amt)
1652 then
1653 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1654
1655 l_log_msg := 'Error : Entered into situation which will lead to infinite loop.'||
1656 'There might be some data corrption. Check the following transaction';
1657 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1658 l_log_msg);
1659
1660 l_log_msg := 'event_id = '||p_xla_event_rec.event_id||
1661 ', invoice_payment_id = '||p_inv_pay_rec.invoice_payment_id||
1662 ', invoice_distribution_id = '||l_inv_dist_id||
1663 ', payment_history_id = '||l_pay_hist_id;
1664 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1665 l_log_msg);
1666
1667 l_log_msg := 'l_clr_rate_rounding_amt = '||l_clr_rate_rounding_amt||
1668 'l_pre_clr_rate_rounding_amt = '||l_pre_clr_rate_rounding_amt;
1669 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1670 l_log_msg);
1671
1672 l_log_msg := 'rounding failed and exiting loop!!!';
1673 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1674 l_log_msg);
1675 END IF;
1676
1677 exit;
1678 end if;
1679
1680 UPDATE AP_Payment_Hist_Dists APHD
1681 SET APHD.Cleared_Base_Amount = 0,
1682 APHD.Rounding_Amt=-sign(l_inv_clr_base_amt)*l_inv_clr_base_amt
1683 WHERE APHD.Payment_History_ID = l_pay_hist_id
1684 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
1685 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1686 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1687 AND APHD.Pay_Dist_Lookup_Code = 'CASH';
1688 ELSE
1689 UPDATE AP_Payment_Hist_Dists APHD
1690 SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount +
1691 NVL(l_clr_rate_rounding_amt, 0),
1692 APHD.Rounding_Amt=l_clr_rate_rounding_amt
1693 WHERE APHD.Payment_History_ID = l_pay_hist_id
1694 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
1695 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1696 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1697 AND APHD.Pay_Dist_Lookup_Code = 'CASH';
1698 l_clr_rate_rounding_amt := 0;
1699 END IF;
1700 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1701 l_log_msg := 'Updated rounding amount for l_clr_rate_diff_amt';
1702 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1703 G_MODULE_NAME || l_procedure_name,
1704 l_log_msg);
1705 END IF;
1706 END IF; --bug 8735895
1707
1708 IF NVL(l_mat_rate_rounding_amt, 0) <> 0 THEN --8449083
1709 /*AND (p_xla_event_rec.event_type_code IN
1710 ('PAYMENT MATURED', 'PAYMENT MATURITY ADJUSTED')) THEN*/ --bug 8735895
1711
1712 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1713 l_log_msg := 'Updating rounding amount for l_mat_rate_diff_amt';
1714 FND_LOG.STRING(G_LEVEL_STATEMENT,
1715 G_MODULE_NAME || l_procedure_name,
1716 l_log_msg);
1720 (l_mat_rate_rounding_amt + l_inv_mat_base_amt) <> 0) then
1717 END IF;
1718 IF (sign(l_mat_rate_rounding_amt + l_inv_mat_base_amt) <>
1719 sign(l_inv_dist_amt) and
1721 -- bug 8403738
1722 l_pre_mat_rate_rounding_amt := l_mat_rate_rounding_amt;
1723 l_mat_rate_rounding_amt := l_inv_mat_base_amt +
1724 l_mat_rate_rounding_amt;
1725
1726 -- bug 8403738
1727 if sign(l_pre_mat_rate_rounding_amt) = sign(l_mat_rate_rounding_amt)
1728 AND abs(l_mat_rate_rounding_amt) >= abs(l_pre_mat_rate_rounding_amt)
1729 then
1730 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1731
1732 l_log_msg := 'Error : Entered into situation which will lead to infinite loop.'||
1733 'There might be some data corrption. Check the following transaction';
1734 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1735 l_log_msg);
1736
1737 l_log_msg := 'event_id = '||p_xla_event_rec.event_id||
1738 ', invoice_payment_id = '||p_inv_pay_rec.invoice_payment_id||
1739 ', invoice_distribution_id = '||l_inv_dist_id||
1740 ', payment_history_id = '||l_pay_hist_id;
1741 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1742 l_log_msg);
1743
1744 l_log_msg := 'l_mat_rate_rounding_amt = '||l_mat_rate_rounding_amt||
1745 'l_pre_mat_rate_rounding_amt = '||l_pre_mat_rate_rounding_amt;
1746 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1747 l_log_msg);
1748
1749 l_log_msg := 'rounding failed and exiting loop!!!';
1750 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1751 l_log_msg);
1752 END IF;
1753
1754 exit;
1755 end if;
1756
1757 UPDATE AP_Payment_Hist_Dists APHD
1758 SET APHD.Matured_Base_Amount = 0,
1759 APHD.Rounding_Amt=-sign(l_inv_mat_base_amt )*l_inv_mat_base_amt
1760 WHERE APHD.Payment_History_ID = l_pay_hist_id
1761 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
1762 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1763 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1764 AND APHD.Pay_Dist_Lookup_Code = 'CASH';
1765 ELSE
1766 UPDATE AP_Payment_Hist_Dists APHD
1767 SET APHD.Matured_Base_Amount = APHD.Matured_Base_Amount +
1768 NVL(l_mat_rate_rounding_amt, 0),
1769 APHD.Rounding_Amt=l_mat_rate_rounding_amt
1770 WHERE APHD.Payment_History_ID = l_pay_hist_id
1771 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
1772 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1773 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1774 AND APHD.Pay_Dist_Lookup_Code = 'CASH';
1775 l_mat_rate_rounding_amt := 0;
1776 END IF;
1777
1778 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1779 l_log_msg := 'Updated rounding amount for l_mat_rate_diff_amt';
1780 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1781 G_MODULE_NAME || l_procedure_name,
1782 l_log_msg);
1783 END IF;
1784 END IF; --bug 8735895
1785
1786 IF NVL(l_pay_rate_rounding_amt, 0) <> 0 THEN --8449083
1787
1788 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1789 l_log_msg := 'Updating rounding amount for l_pay_rate_diff_amt';
1790 FND_LOG.STRING(G_LEVEL_STATEMENT,
1791 G_MODULE_NAME || l_procedure_name,
1792 l_log_msg);
1793 END IF;
1794 IF (sign(l_pay_rate_rounding_amt + l_inv_paid_base_amt) <>
1795 sign(l_inv_dist_amt) and
1796 (l_pay_rate_rounding_amt + l_inv_paid_base_amt) <> 0) then
1797 -- bug 8403738
1798 l_pre_pay_rate_rounding_amt := l_pay_rate_rounding_amt;
1799 l_pay_rate_rounding_amt := l_inv_paid_base_amt +
1800 l_pay_rate_rounding_amt;
1801
1802 -- bug 8403738
1803 if sign(l_pre_pay_rate_rounding_amt) = sign(l_pay_rate_rounding_amt)
1804 AND abs(l_pay_rate_rounding_amt) >= abs(l_pre_pay_rate_rounding_amt)
1805 then
1806 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1807
1808 l_log_msg := 'Error : Entered into situation which will lead to infinite loop.'||
1809 'There might be some data corrption. Check the following transaction';
1810 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1811 l_log_msg);
1812
1813 l_log_msg := 'event_id = '||p_xla_event_rec.event_id||
1814 ', invoice_payment_id = '||p_inv_pay_rec.invoice_payment_id||
1815 ', invoice_distribution_id = '||l_inv_dist_id||
1816 ', payment_history_id = '||l_pay_hist_id;
1817 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1818 l_log_msg);
1819
1820 l_log_msg := 'l_pay_rate_rounding_amt = '||l_pay_rate_rounding_amt||
1821 'l_pre_pay_rate_rounding_amt = '||l_pre_pay_rate_rounding_amt;
1822 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1823 l_log_msg);
1824
1825 l_log_msg := 'rounding failed and exiting loop!!!';
1826 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1827 l_log_msg);
1828 END IF;
1829
1830 exit;
1831 end if;
1832
1833 UPDATE AP_Payment_Hist_Dists APHD
1837 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
1834 SET APHD.Paid_Base_Amount = 0,
1835 APHD.rounding_amt=-sign(l_inv_paid_base_amt)*l_inv_paid_base_amt
1836 WHERE APHD.Payment_History_ID = l_pay_hist_id
1838 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1839 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1840 AND APHD.Pay_Dist_Lookup_Code = 'CASH';
1841 ELSE
1842 UPDATE AP_Payment_Hist_Dists APHD
1843 SET APHD.Paid_Base_Amount = APHD.Paid_Base_Amount +
1844 NVL(l_pay_rate_rounding_amt, 0),
1845 APHD.Rounding_amt=l_pay_rate_rounding_amt
1846 WHERE APHD.Payment_History_ID = l_pay_hist_id
1847 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
1848 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1849 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1850 AND APHD.Pay_Dist_Lookup_Code = 'CASH';
1851 l_pay_rate_rounding_amt := 0;
1852 END IF;
1853
1854 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1855 l_log_msg := 'Updated rounding amount for l_pay_rate_diff_amt';
1856 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1857 G_MODULE_NAME || l_procedure_name,
1858 l_log_msg);
1859 END IF;
1860
1861 END IF;
1862
1863 IF NVL(l_disc_pay_rounding_amt, 0) <> 0 THEN --8449083
1864 --vasvenka
1865 BEGIN
1866
1867 SELECT APHD.Invoice_Dist_Amount,
1868 APHD.Invoice_Dist_Base_Amount,
1869 APHD.matured_base_amount,
1870 APHD.paid_base_Amount,
1871 APHD.cleared_base_amount
1872 INTO l_inv_dist_amt_disc,
1873 l_inv_dist_base_amt_disc,
1874 l_inv_mat_base_amt_disc,
1875 l_inv_paid_base_amt_disc,
1876 l_inv_clr_base_amt_disc
1877 FROM AP_Payment_Hist_Dists APHD
1878 WHERE APHD.Payment_History_ID = l_pay_hist_id
1879 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
1880 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1881 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1882 AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
1883
1884 EXCEPTION WHEN OTHERS THEN
1885 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1886 l_log_msg := 'exception when select discount row amount values for payment'||SQLERRM;
1887 l_log_msg := l_log_msg||'Exiting loop';
1888 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,l_log_msg);
1889 END IF;
1890 EXIT;
1891 END;
1892
1893 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1894 l_log_msg := 'Updating discount rounding amount for payment';
1895 FND_LOG.STRING(G_LEVEL_STATEMENT,
1896 G_MODULE_NAME || l_procedure_name,
1897 l_log_msg);
1898 END IF;
1899 IF (sign(l_disc_pay_rounding_amt + l_inv_paid_base_amt_disc) <>
1900 sign(l_inv_dist_amt_disc) and
1901 (l_disc_pay_rounding_amt + l_inv_paid_base_amt_disc) <> 0) then
1902 -- bug 8403738
1903 l_pre_disc_pay_rounding_amt := l_disc_pay_rounding_amt;
1904 l_disc_pay_rounding_amt := l_inv_paid_base_amt_disc +
1905 l_disc_pay_rounding_amt;
1906
1907 -- bug 8403738
1908 if sign(l_pre_disc_pay_rounding_amt) = sign(l_disc_pay_rounding_amt)
1909 AND abs(l_disc_pay_rounding_amt) >= abs(l_pre_disc_pay_rounding_amt)
1910 then
1911 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1912
1913 l_log_msg := 'Error : Entered into situation which will lead to infinite loop.'||
1914 'There might be some data corrption. Check the following transaction';
1915 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1916 l_log_msg);
1917
1918 l_log_msg := 'event_id = '||p_xla_event_rec.event_id||
1919 ', invoice_payment_id = '||p_inv_pay_rec.invoice_payment_id||
1920 ', invoice_distribution_id = '||l_inv_dist_id||
1921 ', payment_history_id = '||l_pay_hist_id;
1922 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1923 l_log_msg);
1924
1925 l_log_msg := 'l_disc_pay_rounding_amt = '||l_disc_pay_rounding_amt||
1926 'l_pre_disc_pay_rounding_amt = '||l_pre_disc_pay_rounding_amt;
1927 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1928 l_log_msg);
1929
1930 l_log_msg := 'rounding failed and exiting loop!!!';
1931 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
1932 l_log_msg);
1933 END IF;
1934
1935 exit;
1936 end if;
1937
1938 UPDATE AP_Payment_Hist_Dists APHD
1939 SET APHD.Paid_Base_Amount = 0,
1940 APHD.Rounding_amt=-sign(l_inv_paid_base_amt_disc)*l_inv_paid_base_amt_disc
1941 WHERE APHD.Payment_History_ID = l_pay_hist_id
1942 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
1943 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1944 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1945 AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
1946 ELSE
1947 UPDATE AP_Payment_Hist_Dists APHD
1948 SET APHD.Paid_Base_Amount = APHD.Paid_Base_Amount +
1949 NVL(l_disc_pay_rounding_amt, 0),
1950 APHD.Rounding_amt=l_disc_pay_rounding_amt
1951 WHERE APHD.Payment_History_ID = l_pay_hist_id
1952 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
1956 l_disc_pay_rounding_amt := 0;
1953 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1954 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1955 AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
1957 END IF;
1958
1959 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1960 l_log_msg := 'Updated discount rounding amount for payment';
1961 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1962 G_MODULE_NAME || l_procedure_name,
1963 l_log_msg);
1964 END IF;
1965
1966 END IF;
1967
1968 IF NVL(l_disc_clr_rounding_amt, 0) <> 0 THEN --8449083
1969 --vasvenka
1970 BEGIN
1971
1972 SELECT APHD.Invoice_Dist_Amount,
1973 APHD.Invoice_Dist_Base_Amount,
1974 APHD.matured_base_amount,
1975 APHD.paid_base_Amount,
1976 APHD.cleared_base_amount
1977 INTO l_inv_dist_amt_disc,
1978 l_inv_dist_base_amt_disc,
1979 l_inv_mat_base_amt_disc,
1980 l_inv_paid_base_amt_disc,
1981 l_inv_clr_base_amt_disc
1982 FROM AP_Payment_Hist_Dists APHD
1983 WHERE APHD.Payment_History_ID = l_pay_hist_id
1984 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
1985 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
1986 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
1987 AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
1988
1989 EXCEPTION WHEN OTHERS THEN
1990 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1991 l_log_msg := 'exception when select discount row amount values for payment'||SQLERRM;
1992 l_log_msg := l_log_msg||'Exiting loop';
1993 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,l_log_msg);
1994 END IF;
1995 EXIT;
1996 END;
1997
1998 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1999 l_log_msg := 'Updating discount rounding amount for clearing';
2000 FND_LOG.STRING(G_LEVEL_STATEMENT,
2001 G_MODULE_NAME || l_procedure_name,
2002 l_log_msg);
2003 END IF;
2004 IF (sign(l_disc_clr_rounding_amt + l_inv_clr_base_amt_disc) <>
2005 sign(l_inv_dist_amt_disc) and
2006 (l_disc_clr_rounding_amt + l_inv_clr_base_amt_disc) <> 0) then
2007 -- bug 8403738
2008 l_pre_disc_clr_rounding_amt := l_disc_clr_rounding_amt;
2009 l_disc_clr_rounding_amt := l_inv_clr_base_amt_disc +
2010 l_disc_clr_rounding_amt;
2011
2012 -- bug 8403738
2013 if sign(l_pre_disc_clr_rounding_amt) = sign(l_disc_clr_rounding_amt)
2014 AND abs(l_disc_clr_rounding_amt) >= abs(l_pre_disc_clr_rounding_amt)
2015 then
2016 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2017
2018 l_log_msg := 'Error : Entered into situation which will lead to infinite loop.'||
2019 'There might be some data corrption. Check the following transaction';
2020 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
2021 l_log_msg);
2022
2023 l_log_msg := 'event_id = '||p_xla_event_rec.event_id||
2024 ', invoice_payment_id = '||p_inv_pay_rec.invoice_payment_id||
2025 ', invoice_distribution_id = '||l_inv_dist_id||
2026 ', payment_history_id = '||l_pay_hist_id;
2027 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
2028 l_log_msg);
2029
2030 l_log_msg := 'l_disc_clr_rounding_amt = '||l_disc_clr_rounding_amt||
2031 'l_pre_disc_clr_rounding_amt = '||l_pre_disc_clr_rounding_amt;
2032 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
2033 l_log_msg);
2034
2035 l_log_msg := 'rounding failed and exiting loop!!!';
2036 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
2037 l_log_msg);
2038 END IF;
2039
2040 exit;
2041 end if;
2042
2043 UPDATE AP_Payment_Hist_Dists APHD
2044 SET APHD.Cleared_Base_Amount = 0,
2045 APHD.Rounding_amt=-sign(l_inv_clr_base_amt_disc)*l_inv_clr_base_amt_disc
2046 WHERE APHD.Payment_History_ID = l_pay_hist_id
2047 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
2048 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
2049 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
2050 AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
2051 ELSE
2052 UPDATE AP_Payment_Hist_Dists APHD
2053 SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount +
2054 NVL(l_disc_clr_rounding_amt, 0),
2055 APHD.Rounding_Amt=l_disc_clr_rounding_amt
2056 WHERE APHD.Payment_History_ID = l_pay_hist_id
2057 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
2058 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
2059 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
2060 AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
2061 l_disc_clr_rounding_amt := 0;
2062 END IF;
2063
2064 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2065 l_log_msg := 'Updated discount rounding amount for clearing';
2066 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2067 G_MODULE_NAME || l_procedure_name,
2068 l_log_msg);
2069 END IF;
2070
2071 END IF;
2072
2073 IF NVL(l_err_clr_rounding_amt, 0) <> 0 THEN --8449083
2074
2075 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2076 l_log_msg := 'Updating error rounding amount';
2080 END IF;
2077 FND_LOG.STRING(G_LEVEL_STATEMENT,
2078 G_MODULE_NAME || l_procedure_name,
2079 l_log_msg);
2081
2082 IF (sign(l_err_clr_rounding_amt + l_inv_clr_base_amt) <>
2083 sign(l_inv_dist_amt) and
2084 (l_err_clr_rounding_amt + l_inv_clr_base_amt) <> 0) then
2085 -- bug 8403738
2086 l_pre_err_clr_rounding_amt := l_err_clr_rounding_amt;
2087 l_err_clr_rounding_amt := l_inv_clr_base_amt +
2088 l_err_clr_rounding_amt;
2089
2090 -- bug 8403738
2091 if sign(l_pre_err_clr_rounding_amt) = sign(l_err_clr_rounding_amt)
2092 AND abs(l_err_clr_rounding_amt) >= abs(l_pre_err_clr_rounding_amt)
2093 then
2094 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2095
2096 l_log_msg := 'Error : Entered into situation which will lead to infinite loop.'||
2097 'There might be some data corrption. Check the following transaction';
2098 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
2099 l_log_msg);
2100
2101 l_log_msg := 'event_id = '||p_xla_event_rec.event_id||
2102 ', invoice_payment_id = '||p_inv_pay_rec.invoice_payment_id||
2103 ', invoice_distribution_id = '||l_inv_dist_id||
2104 ', payment_history_id = '||l_pay_hist_id;
2105 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
2106 l_log_msg);
2107
2108 l_log_msg := 'l_err_clr_rounding_amt = '||l_err_clr_rounding_amt||
2109 'l_pre_err_clr_rounding_amt = '||l_pre_err_clr_rounding_amt;
2110 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
2111 l_log_msg);
2112
2113 l_log_msg := 'rounding failed and exiting loop!!!';
2114 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
2115 l_log_msg);
2116 END IF;
2117
2118 exit;
2119 end if;
2120
2121 UPDATE AP_Payment_Hist_Dists APHD
2122 SET APHD.Cleared_Base_Amount = 0,
2123 APHD.Rounding_amt=-sign(l_inv_clr_base_amt)*l_inv_clr_base_amt
2124 WHERE APHD.Payment_History_ID = l_pay_hist_id
2125 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
2126 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
2127 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
2128 AND APHD.Pay_Dist_Lookup_Code = 'BANK ERROR';
2129 ELSE
2130 UPDATE AP_Payment_Hist_Dists APHD
2131 SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount +
2132 NVL(l_err_clr_rounding_amt, 0),
2133 APHD.Rounding_amt=l_err_clr_rounding_amt
2134 WHERE APHD.Payment_History_ID = l_pay_hist_id
2135 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
2136 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
2137 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
2138 AND APHD.Pay_Dist_Lookup_Code = 'BANK ERROR';
2139 l_err_clr_rounding_amt := 0;
2140 END IF;
2141
2142 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2143 l_log_msg := 'Updated error rounding amount';
2144 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2145 G_MODULE_NAME || l_procedure_name,
2146 l_log_msg);
2147 END IF;
2148
2149 END IF;
2150
2151 IF NVL(l_chrg_clr_rounding_amt, 0) <> 0 THEN --8449083
2152
2153 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2154 l_log_msg := 'Updating charge rounding amount';
2155 FND_LOG.STRING(G_LEVEL_STATEMENT,
2156 G_MODULE_NAME || l_procedure_name,
2157 l_log_msg);
2158 END IF;
2159
2160 IF (sign(l_chrg_clr_rounding_amt + l_inv_clr_base_amt) <>
2161 sign(l_inv_dist_amt) and
2162 (l_chrg_clr_rounding_amt + l_inv_clr_base_amt) <> 0) then
2163 -- bug 8403738
2164 l_pre_chrg_clr_rounding_amt := l_chrg_clr_rounding_amt;
2165 l_chrg_clr_rounding_amt := l_inv_clr_base_amt +
2166 l_chrg_clr_rounding_amt;
2167
2168 -- bug 8403738
2169 if sign(l_pre_chrg_clr_rounding_amt) = sign(l_chrg_clr_rounding_amt)
2170 AND abs(l_chrg_clr_rounding_amt) >= abs(l_pre_chrg_clr_rounding_amt)
2171 then
2172 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2173
2174 l_log_msg := 'Error : Entered into situation which will lead to infinite loop.'||
2175 'There might be some data corrption. Check the following transaction';
2176 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
2177 l_log_msg);
2178
2179 l_log_msg := 'event_id = '||p_xla_event_rec.event_id||
2180 ', invoice_payment_id = '||p_inv_pay_rec.invoice_payment_id||
2181 ', invoice_distribution_id = '||l_inv_dist_id||
2182 ', payment_history_id = '||l_pay_hist_id;
2183 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
2184 l_log_msg);
2185
2186 l_log_msg := 'l_chrg_clr_rounding_amt = '||l_chrg_clr_rounding_amt||
2187 'l_pre_chrg_clr_rounding_amt = '||l_pre_chrg_clr_rounding_amt;
2188 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
2189 l_log_msg);
2190
2191 l_log_msg := 'rounding failed and exiting loop!!!';
2192 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
2193 l_log_msg);
2194 END IF;
2198
2195
2196 exit;
2197 end if;
2199 UPDATE AP_Payment_Hist_Dists APHD
2200 SET APHD.Cleared_Base_Amount = 0,
2201 APHD.Rounding_amt=-sign(l_inv_clr_base_amt)*l_inv_clr_base_amt
2202 WHERE APHD.Payment_History_ID = l_pay_hist_id
2203 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
2204 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
2205 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
2206 AND APHD.Pay_Dist_Lookup_Code = 'BANK CHARGE';
2207 ELSE
2208 UPDATE AP_Payment_Hist_Dists APHD
2209 SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount +
2210 NVL(l_chrg_clr_rounding_amt, 0),
2211 APHD.Rounding_amt=l_chrg_clr_rounding_amt
2212 WHERE APHD.Payment_History_ID = l_pay_hist_id
2213 AND APHD.Invoice_Distribution_ID = l_inv_dist_id
2214 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
2215 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
2216 AND APHD.Pay_Dist_Lookup_Code = 'BANK CHARGE';
2217 l_chrg_clr_rounding_amt := 0;
2218 END IF;
2219
2220 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2221 l_log_msg := 'Updated charge rounding amount';
2222 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2223 G_MODULE_NAME || l_procedure_name,
2224 l_log_msg);
2225 END IF;
2226
2227 END IF;
2228
2229 --8449083 added
2230 IF ( l_pre_inv_dist_rounding_amt <= l_inv_dist_rounding_amt OR
2231 l_pre_clr_rate_rounding_amt <= l_clr_rate_rounding_amt OR
2232 l_pre_mat_rate_rounding_amt <= l_mat_rate_rounding_amt OR
2233 l_pre_pay_rate_rounding_amt <= l_pay_rate_rounding_amt OR
2234 l_pre_disc_pay_rounding_amt <= l_disc_pay_rounding_amt OR
2235 l_pre_disc_clr_rounding_amt <= l_disc_clr_rounding_amt OR
2236 l_pre_err_clr_rounding_amt <= l_err_clr_rounding_amt OR
2237 l_pre_chrg_clr_rounding_amt <= l_chrg_clr_rounding_amt OR
2238 sign(l_pre_inv_dist_rounding_amt) <> sign(l_inv_dist_rounding_amt) OR
2239 sign(l_pre_clr_rate_rounding_amt) <> sign(l_clr_rate_rounding_amt) OR
2240 sign(l_pre_mat_rate_rounding_amt) <> sign(l_mat_rate_rounding_amt) OR
2241 sign(l_pre_pay_rate_rounding_amt) <> sign(l_pay_rate_rounding_amt) OR
2242 sign(l_pre_disc_pay_rounding_amt) <> sign(l_disc_pay_rounding_amt) OR
2243 sign(l_pre_disc_clr_rounding_amt) <> sign(l_disc_clr_rounding_amt) OR
2244 sign(l_pre_err_clr_rounding_amt) <> sign(l_err_clr_rounding_amt) OR
2245 sign(l_pre_chrg_clr_rounding_amt) <> sign(l_chrg_clr_rounding_amt) ) THEN
2246
2247 EXIT;
2248 END IF;
2249
2250
2251 --bug 8267525
2252 IF (l_clr_rate_rounding_amt <> 0 or l_mat_rate_rounding_amt <> 0 or
2253 l_disc_pay_rounding_amt <> 0 or l_disc_clr_rounding_amt <> 0 or
2254 l_err_clr_rounding_amt <> 0 or l_chrg_clr_rounding_amt <> 0 or
2255 l_inv_dist_rounding_amt <> 0) THEN
2256 BEGIN
2257 SELECT APHD.Payment_History_ID,
2258 APHD.Invoice_Distribution_ID
2259 INTO l_pay_hist_id, l_inv_dist_id
2260 FROM AP_Payment_Hist_Dists APHD
2261 WHERE APHD.Invoice_Distribution_ID =
2262 (SELECT MAX(APHD1.Invoice_Distribution_ID)
2263 FROM AP_Payment_Hist_Dists APHD1
2264 WHERE APHD1.Accounting_Event_ID = p_xla_event_rec.event_id
2265 AND APHD1.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
2266 AND APHD1.Rounding_Amt is NULL
2267 AND APHD1.Invoice_Distribution_ID IN
2268 (SELECT AID.Invoice_Distribution_ID
2269 FROM AP_Invoice_Distributions_All AID
2270 WHERE AID.Invoice_ID = p_inv_rec.invoice_id)
2271 AND ABS(APHD1.Amount) =
2272 (SELECT MAX(ABS(APHD2.Amount))
2273 FROM AP_Payment_Hist_Dists APHD2
2274 WHERE APHD2.Accounting_Event_ID = p_xla_event_rec.event_id
2275 AND APHD2.Invoice_Payment_ID =
2276 p_inv_pay_rec.invoice_payment_id
2277 AND APHD2.Rounding_Amt is NULL
2278 AND APHD2.Invoice_Distribution_ID IN
2279 (SELECT AID.Invoice_Distribution_ID
2280 FROM AP_Invoice_Distributions_All AID
2281 WHERE AID.Invoice_ID = p_inv_rec.invoice_id)))
2282 AND APHD.Rounding_Amt is NULL
2283 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
2284 AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
2285 AND Rownum = 1;
2286 EXCEPTION
2287 WHEN NO_DATA_FOUND THEN
2288 RAISE_APPLICATION_ERROR(-20100, l_procedure_name||
2289 ' no_record_in_aphd_while_retrieving_max_dist');
2290 END;
2291 END IF; --bug 8267525
2292 END LOOP;
2293
2294 -- Logging Infra: Procedure level
2295 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2296 l_log_msg := 'End of procedure '|| l_procedure_name;
2297 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
2298 END IF;
2299
2300
2301 EXCEPTION
2302 WHEN OTHERS THEN
2303 IF (SQLCODE = -20100) THEN
2304 RAISE_APPLICATION_ERROR(-20100, SQLERRM);
2305 ELSIF (SQLCODE <> -20001) THEN
2306 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2307 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2308 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2309 END IF;
2310 APP_EXCEPTION.RAISE_EXCEPTION;
2311
2312 END Total_Pay;
2313
2314
2315
2316 -------------------------------------------------------------------------------
2317 -- PROCEDURE Compare_Pay
2321 -- PAYMENT TO MATURITY ROUNDING
2318 -- This procedure calculates the rounding amount needed to relieve base
2319 -- amounts between events. The following types of rounding will be calculated:
2320 --
2322 -- PAYMENT TO CLEARING ROUNDING
2323 -- MATURITY TO CLEARING ROUNDING
2324 --
2325 --------------------------------------------------------------------------------
2326 PROCEDURE Compare_Pay
2327 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
2328 ,P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
2329 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
2330 ,P_Inv_Pay_Rec IN ap_acctg_pay_dist_pkg.r_inv_pay_info
2331 ,P_Calling_Sequence IN VARCHAR2
2332 ) IS
2333
2334 l_curr_calling_sequence VARCHAR2(2000);
2335 l_sum_pay_paid_base_amt NUMBER;
2336 l_sum_mat_paid_base_amt NUMBER;
2337 l_sum_clr_paid_base_amt NUMBER;
2338 l_sum_mat_mat_base_amt NUMBER;
2339 l_sum_clr_mat_base_amt NUMBER;
2340
2341 l_diff_mat_paid_base_amt NUMBER;
2342 l_diff_clr_paid_base_amt NUMBER;
2343 l_diff_clr_mat_base_amt NUMBER;
2344
2345
2346 l_max_pd_rec AP_PAYMENT_HIST_DISTS%ROWTYPE;
2347 l_pd_rec AP_PAYMENT_HIST_DISTS%ROWTYPE;
2348
2349 -- Logging Infra:
2350 l_procedure_name CONSTANT VARCHAR2(30) := 'Compare_Pay';
2351 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2352
2353 BEGIN
2354
2355 l_curr_calling_sequence := 'AP_ACCTG_PAY_ROUND_PKG.Compare_Pay<- ' ||
2356 P_Calling_Sequence;
2357
2358
2359 -- Logging Infra: Procedure level
2360 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2361 l_log_msg := 'Begin of procedure '|| l_procedure_name;
2362 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2363 END IF;
2364
2365
2366 -- Getting the max of the largest distribution for inserting the rounding
2367 -- distribution
2368 BEGIN
2369 SELECT APHD.*
2370 INTO l_max_pd_rec
2371 FROM AP_Payment_Hist_Dists APHD
2372 WHERE APHD.Invoice_Distribution_ID =
2373 (SELECT MAX(APHD1.Invoice_Distribution_ID)
2374 FROM AP_Payment_Hist_Dists APHD1
2375 WHERE APHD1.Accounting_Event_ID = p_xla_event_rec.event_id
2376 AND APHD1.Invoice_payment_id = P_Inv_Pay_Rec.Invoice_payment_id -- Bug 8722710
2377 AND APHD1.Invoice_Distribution_ID IN
2378 (SELECT AID.Invoice_Distribution_ID
2379 FROM AP_Invoice_Distributions_All AID
2380 WHERE AID.Invoice_ID = p_inv_rec.invoice_id)
2381 AND ABS(APHD1.Amount) =
2382 (SELECT MAX(ABS(APHD2.Amount))
2383 FROM AP_Payment_Hist_Dists APHD2
2384 WHERE APHD2.Accounting_Event_ID = p_xla_event_rec.event_id
2385 AND APHD2.Invoice_payment_id = P_Inv_Pay_Rec.Invoice_payment_id -- Bug 8722710
2386 AND APHD2.Invoice_Distribution_ID IN
2387 (SELECT AID.Invoice_Distribution_ID
2388 FROM AP_Invoice_Distributions_All AID
2389 WHERE AID.Invoice_ID = p_inv_rec.invoice_id)))
2390 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
2391 AND APHD.Invoice_payment_id = P_Inv_Pay_Rec.Invoice_payment_id -- Bug 8722710
2392 AND Rownum = 1;
2393 EXCEPTION
2394 WHEN NO_DATA_FOUND THEN
2395 RAISE_APPLICATION_ERROR(-20100, l_procedure_name||
2396 ' no_record_in_aphd_while_retrieving_max_dist');
2397 END; --bug 9936620
2398
2399 -- Get the paid base amounts for the payment event
2400 SELECT SUM(APHD.Paid_Base_Amount)
2401 INTO l_sum_pay_paid_base_amt
2402 FROM AP_Payment_Hist_Dists APHD,
2403 AP_Payment_History_All APH,
2404 AP_Payment_History_All APH1
2405 WHERE APH1.Payment_History_ID = ap_accounting_pay_pkg.g_pay_pmt_history_id
2406 AND APH.Related_Event_ID = APH1.Accounting_Event_ID
2407 AND APHD.Payment_History_ID = APH.Payment_History_ID
2408 AND APHD.Pay_Dist_Lookup_Code IN ('CASH')
2409 AND APHD.Invoice_payment_id = P_Inv_Pay_Rec.Invoice_payment_id -- Bug 8722710
2410 AND APHD.Invoice_Distribution_ID IN
2411 (SELECT AID.Invoice_Distribution_ID
2412 FROM AP_Invoice_Distributions_All AID
2413 WHERE AID.Invoice_ID = p_inv_rec.invoice_id);
2414
2415
2416 -- Get the paid base amounts for the payment maturity event
2417 SELECT SUM(APHD.Paid_Base_Amount),
2418 SUM(APHD.Matured_Base_Amount)
2419 INTO l_sum_mat_paid_base_amt,
2420 l_sum_mat_mat_base_amt
2421 FROM AP_Payment_Hist_Dists APHD,
2422 AP_Payment_History_All APH,
2423 AP_Payment_History_All APH1
2424 WHERE APH1.Payment_History_ID = ap_accounting_pay_pkg.g_mat_pmt_history_id
2425 AND APH.Related_Event_ID = APH1.Accounting_Event_ID
2426 AND APHD.Payment_History_ID = APH.Payment_History_ID
2427 AND APHD.Pay_Dist_Lookup_Code IN ('CASH')
2428 AND APHD.Invoice_payment_id = P_Inv_Pay_Rec.Invoice_payment_id -- Bug 8722710
2429 AND APHD.Invoice_Distribution_ID IN
2430 (SELECT AID.Invoice_Distribution_ID
2431 FROM AP_Invoice_Distributions_All AID
2432 WHERE AID.Invoice_ID = p_inv_rec.invoice_id);
2433
2434
2435 -- Get the paid base amounts for the payment clearing event
2436 -- Bug 6678474. Backing out the fix for bug 6621586 since it is not right fix.
2437 -- Here we are calculating the rounding difference for the same currency amounts
2438 -- but in prior events.
2442 SUM(APHD.Matured_Base_Amount)
2439 -- For eg. any difference between the paid base amount in payment created event
2440 -- and payment cleared event will be calculated as Payment to Clearing rounding
2441 SELECT SUM(APHD.Paid_Base_Amount),
2443 INTO l_sum_clr_paid_base_amt,
2444 l_sum_clr_mat_base_amt
2445 FROM AP_Payment_Hist_Dists APHD,
2446 AP_Payment_History_All APH,
2447 AP_Payment_History_All APH1
2448 WHERE APH1.Payment_History_ID = ap_accounting_pay_pkg.g_clr_pmt_history_id
2449 AND APH.Related_Event_ID = APH1.Accounting_Event_ID
2450 AND APHD.Payment_History_ID = APH.Payment_History_ID
2451 AND APHD.Pay_Dist_Lookup_Code IN ('CASH')
2452 AND APHD.Invoice_payment_id = P_Inv_Pay_Rec.Invoice_payment_id -- Bug 8722710
2453 AND APHD.Invoice_Distribution_ID IN
2454 (SELECT AID.Invoice_Distribution_ID
2455 FROM AP_Invoice_Distributions_All AID
2456 WHERE AID.Invoice_ID = p_inv_rec.invoice_id);
2457
2458 /* If there is any difference between the paid and maturity base amounts between
2459 this event and the prior event then we will insert the appropriate rounding
2460 distribution */
2461 l_diff_mat_paid_base_amt := l_sum_pay_paid_base_amt -
2462 NVL(l_sum_mat_paid_base_amt, l_sum_pay_paid_base_amt);
2463
2464 l_diff_clr_paid_base_amt := l_sum_pay_paid_base_amt -
2465 NVL(l_sum_clr_paid_base_amt, l_sum_pay_paid_base_amt);
2466
2467 l_diff_clr_mat_base_amt := NVL(l_sum_mat_mat_base_amt, l_sum_clr_mat_base_amt) -
2468 NVL(l_sum_clr_mat_base_amt, l_sum_mat_mat_base_amt);
2469
2470 l_pd_rec.invoice_distribution_id := l_max_pd_rec.invoice_distribution_id;
2471 l_pd_rec.payment_history_id := l_max_pd_rec.payment_history_id;
2472 l_pd_rec.invoice_payment_id := l_max_pd_rec.invoice_payment_id;
2473 l_pd_rec.invoice_adjustment_event_id := l_max_pd_rec.invoice_adjustment_event_id;
2474 l_pd_rec.accounting_event_id := p_xla_event_rec.event_id;
2475 l_pd_rec.awt_related_id := l_max_pd_rec.awt_related_id;
2476
2477 l_pd_rec.amount := 0;
2478 l_pd_rec.invoice_dist_amount := 0;
2479 l_pd_rec.bank_curr_amount := 0;
2480 l_pd_rec.invoice_dist_base_amount := 0;
2481
2482 IF l_diff_mat_paid_base_amt <> 0 THEN
2483
2484 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2485 l_log_msg := 'Inserting future payment rounding';
2486 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2487 END IF;
2488
2489
2490 l_pd_rec.pay_dist_lookup_code := 'FUTURE PAYMENT ROUNDING';
2491
2492 l_pd_rec.paid_base_amount := l_diff_mat_paid_base_amt;
2493 l_pd_rec.cleared_base_amount := 0;
2494 l_pd_rec.matured_base_amount := 0;
2495
2496 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2497 l_log_msg := 'Calling procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert';
2498 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2499 END IF;
2500
2501 AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert
2502 (l_pd_rec,
2503 l_curr_calling_sequence);
2504
2505 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2506 l_log_msg := 'Procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert executed';
2507 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2508 END IF;
2509
2510
2511 END IF;
2512
2513 IF l_diff_clr_paid_base_amt <> 0 THEN
2514
2515 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2516 l_log_msg := 'Inserting payment to clearing rounding';
2517 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2518 END IF;
2519
2520 l_pd_rec.pay_dist_lookup_code := 'PAYMENT TO CLEARING ROUNDING';
2521
2522 l_pd_rec.paid_base_amount := l_diff_clr_paid_base_amt;
2523 l_pd_rec.cleared_base_amount := 0;
2524 l_pd_rec.matured_base_amount := 0;
2525
2526 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2527 l_log_msg := 'Calling procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert';
2528 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2529 END IF;
2530
2531 AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert
2532 (l_pd_rec,
2533 l_curr_calling_sequence);
2534
2535 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2536 l_log_msg := 'Procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert executed';
2537 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2538 END IF;
2539
2540
2541 END IF;
2542
2543 IF l_diff_clr_mat_base_amt <> 0 THEN
2544
2545
2546 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2547 l_log_msg := 'Inserting maturity to clearing rounding';
2548 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2549 END IF;
2550
2551 l_pd_rec.pay_dist_lookup_code := 'MATURITY TO CLEARING ROUNDING';
2552
2553 l_pd_rec.paid_base_amount := 0;
2554 l_pd_rec.cleared_base_amount := 0;
2555 l_pd_rec.matured_base_amount := l_diff_clr_mat_base_amt;
2556
2557 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2558 l_log_msg := 'Calling procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert';
2559 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2560 END IF;
2561
2562 AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert
2563 (l_pd_rec,
2564 l_curr_calling_sequence);
2565
2566 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2570
2567 l_log_msg := 'Procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert executed';
2568 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2569 END IF;
2571
2572 END IF;
2573
2574 -- Logging Infra: Procedure level
2575 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2576 l_log_msg := 'End of procedure '|| l_procedure_name;
2577 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
2578 END IF;
2579
2580
2581 EXCEPTION
2582 WHEN OTHERS THEN
2583 IF (SQLCODE = -20100) THEN
2584 RAISE_APPLICATION_ERROR(-20100, SQLERRM);
2585 ELSIF (SQLCODE <> -20001) THEN
2586 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2587 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2588 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2589 END IF;
2590 APP_EXCEPTION.RAISE_EXCEPTION;
2591
2592 END Compare_Pay;
2593
2594
2595
2596 -------------------------------------------------------------------------------
2597 -- PROCEDURE Total_Appl
2598 -- This procedure calculates rounding to confirm that each prepayment
2599 -- application invoice distribution and its tax difference have been fully
2600 -- relieved by their corresponding records in APAD
2601 --
2602 --------------------------------------------------------------------------------
2603 PROCEDURE Total_Appl
2604 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
2605 ,P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
2606 ,P_Clr_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
2607 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
2608 ,P_Prepay_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
2609 ,P_Prepay_Hist_Rec IN AP_ACCTG_PREPAY_DIST_PKG.r_prepay_hist_info
2610 ,P_Prepay_Dist_Rec IN AP_ACCTG_PREPAY_DIST_PKG.r_prepay_dist_info
2611 ,P_Calling_Sequence IN VARCHAR2
2612 ) IS
2613
2614 l_curr_calling_sequence VARCHAR2(2000);
2615 l_prepay_pay_amt NUMBER;
2616 l_prepay_pay_tax_diff NUMBER;
2617
2618 l_tot_inv_rate_amt NUMBER;
2619 l_tot_prepay_rate_amt NUMBER;
2620 l_tot_prepay_pay_rate_amt NUMBER;
2621 l_tot_prepay_clr_rate_amt NUMBER;
2622 l_td_tot_inv_rate_amt NUMBER;
2623 l_td_tot_prepay_rate_amt NUMBER;
2624 l_td_tot_prepay_pay_rate_amt NUMBER;
2625 l_td_tot_prepay_clr_rate_amt NUMBER;
2626
2627 l_sum_inv_rate_amt NUMBER;
2628 l_sum_prepay_rate_amt NUMBER;
2629 l_sum_prepay_pay_rate_amt NUMBER;
2630 l_sum_prepay_clr_rate_amt NUMBER;
2631 l_td_sum_inv_rate_amt NUMBER;
2632 l_td_sum_prepay_rate_amt NUMBER;
2633 l_td_sum_prepay_pay_rate_amt NUMBER;
2634 l_td_sum_prepay_clr_rate_amt NUMBER;
2635
2636 l_diff_inv_rate_amt NUMBER;
2637 l_diff_prepay_rate_amt NUMBER;
2638 l_diff_prepay_pay_rate_amt NUMBER;
2639 l_diff_prepay_clr_rate_amt NUMBER;
2640 l_td_diff_inv_rate_amt NUMBER;
2641 l_td_diff_prepay_rate_amt NUMBER;
2642 l_td_diff_prepay_pay_rate_amt NUMBER;
2643 l_td_diff_prepay_clr_rate_amt NUMBER;
2644
2645 l_max_prepay_rec AP_PREPAY_APP_DISTS%ROWTYPE;
2646 l_pad_rec AP_PREPAY_APP_DISTS%ROWTYPE;
2647
2648 -- Logging Infra:
2649 l_procedure_name CONSTANT VARCHAR2(30) := 'Total_Appl';
2650 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2651
2652 BEGIN
2653
2654 l_curr_calling_sequence := 'AP_Acctg_Pay_Round_Pkg.Total_Appl<- ' ||
2655 p_calling_sequence;
2656
2657 -- Logging Infra: Procedure level
2658 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2659 l_log_msg := 'Begin of procedure '|| l_procedure_name;
2660 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2661 END IF;
2662
2663 -- Get the max of the largest distribution for inserting the rounding line
2664 BEGIN
2665 SELECT APAD.*
2666 INTO l_max_prepay_rec
2667 FROM AP_Prepay_App_Dists APAD
2668 WHERE Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id
2669 AND APAD.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
2670 AND Invoice_Distribution_ID =
2671 (SELECT MAX(APAD1.Invoice_Distribution_ID)
2672 FROM AP_Prepay_App_Dists APAD1
2673 WHERE APAD1.Prepay_App_Distribution_ID =
2674 p_prepay_dist_rec.invoice_distribution_id
2675 AND APAD1.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
2676 AND ABS(APAD1.Amount) =
2677 (SELECT MAX(ABS(APAD2.Amount))
2678 FROM AP_Prepay_App_Dists APAD2
2679 WHERE APAD2.Prepay_App_Distribution_ID =
2680 p_prepay_dist_rec.invoice_distribution_id
2681 AND APAD2.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id))
2682 AND Rownum = 1;
2683 EXCEPTION
2684 WHEN NO_DATA_FOUND THEN
2685 RAISE_APPLICATION_ERROR(-20100, l_procedure_name||
2686 ' no_record_in_APAD_while_retrieving_max_dist');
2687 END; --bug 9936620
2688
2689 l_tot_inv_rate_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
2690 (p_prepay_dist_rec.amount,
2691 p_inv_rec.invoice_currency_code,
2692 ap_accounting_pay_pkg.g_base_currency_code,
2693 p_inv_rec.exchange_rate_type,
2694 p_inv_rec.exchange_date,
2698
2695 p_inv_rec.exchange_rate,
2696 l_curr_calling_sequence);
2697
2699 l_tot_prepay_rate_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
2700 (p_prepay_dist_rec.amount,
2701 p_inv_rec.invoice_currency_code,
2702 ap_accounting_pay_pkg.g_base_currency_code,
2703 l_max_prepay_rec.prepay_exchange_rate_type,
2704 l_max_prepay_rec.prepay_exchange_date,
2705 l_max_prepay_rec.prepay_exchange_rate,
2706 l_curr_calling_sequence);
2707
2708
2709 -- Converting the prepay amount into payment currency
2710 IF (p_inv_rec.invoice_currency_code <> p_pay_hist_rec.pmt_currency_code) THEN
2711 l_prepay_pay_amt := p_prepay_dist_rec.amount * p_inv_rec.payment_cross_rate;
2712 l_prepay_pay_tax_diff := p_prepay_dist_rec.prepay_tax_diff_amount
2713 * p_inv_rec.payment_cross_rate;
2714 ELSE
2715 l_prepay_pay_amt := p_prepay_dist_rec.amount;
2716 l_prepay_pay_tax_diff := p_prepay_dist_rec.prepay_tax_diff_amount;
2717 END IF;
2718
2719
2720 l_tot_prepay_pay_rate_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
2721 (l_prepay_pay_amt,
2722 p_inv_rec.payment_currency_code,
2723 ap_accounting_pay_pkg.g_base_currency_code,
2724 l_max_prepay_rec.prepay_pay_exchange_rate_type,
2725 l_max_prepay_rec.prepay_pay_exchange_date,
2726 l_max_prepay_rec.prepay_pay_exchange_rate,
2727 l_curr_calling_sequence);
2728
2729 l_td_tot_inv_rate_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
2730 (p_prepay_dist_rec.prepay_tax_diff_amount,
2731 p_inv_rec.invoice_currency_code,
2732 ap_accounting_pay_pkg.g_base_currency_code,
2733 p_inv_rec.exchange_rate_type,
2734 p_inv_rec.exchange_date,
2735 p_inv_rec.exchange_rate,
2736 l_curr_calling_sequence);
2737
2738
2739 l_td_tot_prepay_rate_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
2740 (p_prepay_dist_rec.prepay_tax_diff_amount,
2741 p_inv_rec.invoice_currency_code,
2742 ap_accounting_pay_pkg.g_base_currency_code,
2743 l_max_prepay_rec.prepay_exchange_rate_type,
2744 l_max_prepay_rec.prepay_exchange_date,
2745 l_max_prepay_rec.prepay_exchange_rate,
2746 l_curr_calling_sequence);
2747
2748
2749 l_td_tot_prepay_pay_rate_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
2750 (l_prepay_pay_tax_diff,
2751 p_inv_rec.payment_currency_code,
2752 ap_accounting_pay_pkg.g_base_currency_code,
2753 l_max_prepay_rec.prepay_pay_exchange_rate_type,
2754 l_max_prepay_rec.prepay_pay_exchange_date,
2755 l_max_prepay_rec.prepay_pay_exchange_rate,
2756 l_curr_calling_sequence);
2757
2758
2759 IF l_max_prepay_rec.prepay_clr_exchange_rate IS NOT NULL THEN
2760
2761 l_tot_prepay_clr_rate_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
2762 (l_prepay_pay_amt,
2763 p_inv_rec.payment_currency_code,
2764 ap_accounting_pay_pkg.g_base_currency_code,
2765 l_max_prepay_rec.prepay_clr_exchange_rate_type,
2766 l_max_prepay_rec.prepay_clr_exchange_date,
2767 l_max_prepay_rec.prepay_clr_exchange_rate,
2768 l_curr_calling_sequence);
2769
2770 l_td_tot_prepay_clr_rate_amt := AP_Accounting_Pay_Pkg.Get_Base_Amount
2771 (l_prepay_pay_tax_diff,
2772 p_inv_rec.payment_currency_code,
2773 ap_accounting_pay_pkg.g_base_currency_code,
2774 l_max_prepay_rec.prepay_clr_exchange_rate_type,
2775 l_max_prepay_rec.prepay_clr_exchange_date,
2776 l_max_prepay_rec.prepay_clr_exchange_rate,
2777 l_curr_calling_sequence);
2778
2779 END IF;
2780
2781
2782 SELECT SUM(DECODE(Prepay_Dist_Lookup_Code, 'PREPAY APPL', Base_Amount,
2783 'PREPAY APPL REC TAX', Base_Amount, 'PREPAY APPL NONREC TAX', Base_Amount, 0)),
2784 SUM(DECODE(Prepay_Dist_Lookup_Code, 'PREPAY APPL', Base_Amt_At_Prepay_XRate,
2785 'PREPAY APPL REC TAX', Base_Amt_At_Prepay_XRate,
2786 'PREPAY APPL NONREC TAX', Base_Amt_At_Prepay_XRate, 0)),
2787 SUM(DECODE(Prepay_Dist_Lookup_Code, 'PREPAY APPL', Base_Amt_At_Prepay_Pay_XRate,
2788 'PREPAY APPL REC TAX', Base_Amt_At_Prepay_Pay_XRate,
2789 'PREPAY APPL NONREC TAX', Base_Amt_At_Prepay_Pay_XRate, 0)),
2790 SUM(DECODE(Prepay_Dist_Lookup_Code, 'PREPAY APPL', Base_Amt_At_Prepay_Clr_XRate,
2791 'PREPAY APPL REC TAX', Base_Amt_At_Prepay_Clr_XRate,
2792 'PREPAY APPL NONREC TAX', Base_Amt_At_Prepay_Clr_XRate, 0)),
2796 SUM(DECODE(Prepay_Dist_Lookup_Code, 'TAX DIFF', Base_Amt_At_Prepay_Clr_XRate, 0))
2793 SUM(DECODE(Prepay_Dist_Lookup_Code, 'TAX DIFF', Base_Amount, 0)),
2794 SUM(DECODE(Prepay_Dist_Lookup_Code, 'TAX DIFF', Base_Amt_At_Prepay_XRate, 0)),
2795 SUM(DECODE(Prepay_Dist_Lookup_Code, 'TAX DIFF', Base_Amt_At_Prepay_Pay_XRate, 0)),
2797 INTO l_sum_inv_rate_amt,
2798 l_sum_prepay_rate_amt,
2799 l_sum_prepay_pay_rate_amt,
2800 l_sum_prepay_clr_rate_amt,
2801 l_td_sum_inv_rate_amt,
2802 l_td_sum_prepay_rate_amt,
2803 l_td_sum_prepay_pay_rate_amt,
2804 l_td_sum_prepay_clr_rate_amt
2805 FROM AP_Prepay_App_Dists APAD
2806 WHERE APAD.Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id;
2807
2808
2809 /* If there is difference between the total and sum amounts then we will insert the
2810 difference as the rounding amounts */
2811
2812 l_diff_inv_rate_amt := NVL(l_tot_inv_rate_amt,l_sum_inv_rate_amt) - l_sum_inv_rate_amt;
2813 l_diff_prepay_rate_amt := NVL(l_tot_prepay_rate_amt,l_sum_prepay_rate_amt)
2814 - l_sum_prepay_rate_amt;
2815 l_diff_prepay_pay_rate_amt := NVL(l_tot_prepay_pay_rate_amt,l_sum_prepay_pay_rate_amt)
2816 - l_sum_prepay_pay_rate_amt;
2817 l_diff_prepay_clr_rate_amt := NVL(l_tot_prepay_clr_rate_amt,l_sum_prepay_clr_rate_amt)
2818 - l_sum_prepay_clr_rate_amt;
2819 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;
2820 l_td_diff_prepay_rate_amt := NVL(l_td_tot_prepay_rate_amt,l_td_sum_prepay_rate_amt)
2821 - l_td_sum_prepay_rate_amt;
2822 l_td_diff_prepay_pay_rate_amt := NVL(l_td_tot_prepay_pay_rate_amt,l_td_sum_prepay_pay_rate_amt)
2823 - l_td_sum_prepay_pay_rate_amt;
2824 l_td_diff_prepay_clr_rate_amt := NVL(l_td_tot_prepay_clr_rate_amt,l_td_sum_prepay_clr_rate_amt)
2825 - l_td_sum_prepay_clr_rate_amt;
2826
2827
2828 IF (l_diff_inv_rate_amt <> 0) OR (l_diff_prepay_rate_amt <> 0) OR
2829 (l_diff_prepay_pay_rate_amt <> 0) OR (l_diff_prepay_clr_rate_amt <> 0) THEN
2830
2831 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2832 l_log_msg := 'Updating prepay appl rounding amount';
2833 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2834 END IF;
2835
2836
2837 UPDATE AP_Prepay_App_Dists APPD
2838 SET Base_Amount = Base_Amount + NVL(l_diff_inv_rate_amt,0),
2839 Rounding_Amt = l_diff_inv_rate_amt,
2840 Base_Amt_At_Prepay_XRate = Base_Amt_At_Prepay_XRate
2841 + NVL(l_diff_prepay_rate_amt,0),
2842 Round_Amt_At_Prepay_XRate = l_diff_prepay_rate_amt,
2843 Base_Amt_At_Prepay_Pay_XRate = Base_Amt_At_Prepay_Pay_XRate
2844 + NVL(l_diff_prepay_pay_rate_amt,0),
2845 Round_Amt_At_Prepay_Pay_XRate = l_diff_prepay_pay_rate_amt,
2846 Base_Amt_At_Prepay_Clr_XRate = Base_Amt_At_Prepay_Clr_XRate
2847 + NVL(l_diff_prepay_clr_rate_amt,0),
2848 Round_Amt_At_Prepay_Clr_XRate = l_diff_prepay_clr_rate_amt
2849 WHERE Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
2850 AND Invoice_Distribution_ID = l_max_prepay_rec.invoice_distribution_id
2851 AND Prepay_App_Distribution_ID = l_max_prepay_rec.prepay_app_distribution_id
2852 -- AND Accounting_Event_ID = p_xla_event_rec.event_id
2853 AND Prepay_Dist_Lookup_Code IN ('PREPAY APPL','PREPAY APPL REC TAX',
2854 'PREPAY APPL NONREC TAX');
2855
2856
2857 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2858 l_log_msg := 'Updated prepay appl rounding amount';
2859 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2860 END IF;
2861
2862
2863 END IF;
2864
2865
2866 IF (l_td_diff_inv_rate_amt <> 0) OR (l_td_diff_prepay_rate_amt <> 0) OR
2867 (l_td_diff_prepay_pay_rate_amt <> 0) OR (l_td_diff_prepay_clr_rate_amt <> 0) THEN
2868
2869
2870 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2871 l_log_msg := 'Updating tax diff rounding amount';
2872 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2873 END IF;
2874
2875 UPDATE AP_Prepay_App_Dists APPD
2876 SET Base_Amount = Base_Amount + NVL(l_td_diff_inv_rate_amt,0),
2877 Rounding_Amt = l_td_diff_inv_rate_amt,
2878 Base_Amt_At_Prepay_XRate = Base_Amt_At_Prepay_XRate
2879 + NVL(l_td_diff_prepay_rate_amt,0),
2880 Round_Amt_At_Prepay_XRate = l_td_diff_prepay_rate_amt,
2881 Base_Amt_At_Prepay_Pay_XRate = Base_Amt_At_Prepay_Pay_XRate
2882 + NVL(l_td_diff_prepay_pay_rate_amt,0),
2883 Round_Amt_At_Prepay_Pay_XRate = l_td_diff_prepay_pay_rate_amt,
2884 Base_Amt_At_Prepay_Clr_XRate = Base_Amt_At_Prepay_Clr_XRate
2885 + NVL(l_td_diff_prepay_clr_rate_amt,0),
2886 Round_Amt_At_Prepay_Clr_XRate = l_td_diff_prepay_clr_rate_amt
2887 WHERE Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
2888 AND Invoice_Distribution_ID = l_max_prepay_rec.invoice_distribution_id
2889 AND Prepay_App_Distribution_ID = l_max_prepay_rec.prepay_app_distribution_id
2890 -- AND Accounting_Event_ID = p_xla_event_rec.event_id
2891 AND Prepay_Dist_Lookup_Code IN ('TAX DIFF');
2892
2893
2894 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2895 l_log_msg := 'Updating tax diff rounding amount';
2896 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2900 END IF;
2897 END IF;
2898
2899
2901
2902 -- Logging Infra: Procedure level
2903 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2904 l_log_msg := 'End of procedure '|| l_procedure_name;
2905 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
2906 END IF;
2907
2908
2909 EXCEPTION
2910 WHEN OTHERS THEN
2911 IF (SQLCODE = -20100) THEN
2912 RAISE_APPLICATION_ERROR(-20100, SQLERRM);
2913 ELSIF (SQLCODE <> -20001) THEN
2914 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2915 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2916 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2917 END IF;
2918 APP_EXCEPTION.RAISE_EXCEPTION;
2919
2920 END Total_Appl;
2921
2922
2923 -------------------------------------------------------------------------------
2924 -- PROCEDURE Final_Appl
2925 -- This procedure calculates the rounding amount to relieve the prepaid
2926 -- expense completely. This is calculated during final application of a
2927 -- prepayment distribution
2928 --
2929 --------------------------------------------------------------------------------
2930 PROCEDURE Final_Appl
2931 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
2932 ,P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
2933 ,P_Clr_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
2934 ,P_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
2935 ,P_Prepay_Inv_Rec IN ap_accounting_pay_pkg.r_invoices_info
2936 ,P_Prepay_Hist_Rec IN AP_ACCTG_PREPAY_DIST_PKG.r_prepay_hist_info
2937 ,P_Prepay_Dist_Rec IN AP_ACCTG_PREPAY_DIST_PKG.r_prepay_dist_info
2938 ,P_Calling_Sequence IN VARCHAR2
2939 ) IS
2940
2941 l_curr_calling_sequence VARCHAR2(2000);
2942 l_invrate_for_prepay AP_SYSTEM_PARAMETERS_ALL.INVRATE_FOR_PREPAY_TAX%TYPE;
2943
2944 l_sum_pay_base_amt NUMBER;
2945 l_sum_pay_paid_base_amt NUMBER;
2946 l_sum_pay_clrd_base_amt NUMBER;
2947
2948 l_sum_prepay_rate_amt NUMBER;
2949 l_sum_prepay_pay_rate_amt NUMBER;
2950 l_sum_prepay_clr_rate_amt NUMBER;
2951
2952 l_diff_prepay_rate_amt NUMBER;
2953 l_diff_prepay_pay_rate_amt NUMBER;
2954 l_diff_prepay_clr_rate_amt NUMBER;
2955
2956 l_max_prepay_rec AP_PREPAY_APP_DISTS%ROWTYPE;
2957 l_pad_rec AP_PREPAY_APP_DISTS%ROWTYPE;
2958
2959 -- Logging Infra:
2960 l_procedure_name CONSTANT VARCHAR2(30) := 'Final_Appl';
2961 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2962
2963 BEGIN
2964
2965 l_curr_calling_sequence := 'AP_ACCTG_PAY_ROUND_PKG.Final_Appl<- ' ||
2966 p_calling_sequence;
2967
2968 -- Logging Infra: Procedure level
2969 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2970 l_log_msg := 'Begin of procedure '|| l_procedure_name;
2971 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2972 END IF;
2973
2974 BEGIN
2975
2976 SELECT NVL(ASP.Invrate_for_prepay_tax, 'N')
2977 INTO l_invrate_for_prepay
2978 FROM ap_system_parameters_all ASP,
2979 ap_prepay_history_all APH
2980 WHERE ASP.Org_id = APH.Org_id
2981 AND APH.Prepay_history_id = P_Prepay_Hist_Rec.Prepay_History_ID;
2982
2983 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2984 l_log_msg := 'l_invrate_for_prepay is: '|| l_invrate_for_prepay||
2985 'and P_Prepay_Dist_Rec.Line_type_lookup_code is: '||
2986 P_Prepay_Dist_Rec.Line_type_lookup_code;
2987 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2988 END IF;
2989
2990 IF l_invrate_for_prepay = 'Y' AND P_Prepay_Dist_Rec.Line_type_lookup_code IN ('REC_TAX','NONREC_TAX') THEN
2991
2992 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2993 l_log_msg := 'Returning from the Final_Appl procedure';
2994 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2995 END IF;
2996
2997 RETURN;
2998 END IF;
2999
3000 EXCEPTION
3001 WHEN OTHERS THEN
3002 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3003 l_log_msg := 'Encountered an Exception '||SQLERRM||
3004 ' while checking the option Invrate_for_prepay_tax ';
3005 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3006 END IF;
3007 END;
3008
3009 -- Getting the max of the largest distribution for inserting the rounding dist
3010 BEGIN
3011 SELECT APAD.*
3012 INTO l_max_prepay_rec
3013 FROM AP_Prepay_App_Dists APAD
3014 WHERE Invoice_Distribution_ID IN
3015 (SELECT MAX(APAD1.Invoice_Distribution_ID)
3016 FROM AP_Prepay_App_Dists APAD1
3017 WHERE APAD1.Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id
3018 AND APAD1.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
3019 AND ABS(APAD1.Amount) =
3020 (SELECT MAX(ABS(APAD2.Amount))
3021 FROM AP_Prepay_App_Dists APAD2
3022 WHERE APAD2.Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id
3023 AND APAD2.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id))
3024 AND APAD.Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id
3025 AND APAD.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
3026 AND Rownum = 1;
3027 EXCEPTION
3028 WHEN NO_DATA_FOUND THEN
3029 RAISE_APPLICATION_ERROR(-20100, l_procedure_name||
3030 ' no_record_in_APAD_while_retrieving_max_dist');
3031 END; --bug 9936620
3032
3036 INTO l_sum_pay_paid_base_amt
3033 /* Bug 13791619
3034 -- Get the paid base amount for the payment event
3035 SELECT SUM(APHD.Paid_Base_Amount)
3037 FROM AP_Payment_Hist_Dists APHD,
3038 AP_Payment_History_All APH
3039 WHERE APH.Related_Event_ID = p_pay_hist_rec.Related_Event_ID
3040 AND APHD.Payment_History_ID = APH.Payment_History_ID
3041 AND Invoice_Distribution_ID IN
3042 (SELECT AID.Invoice_Distribution_ID
3043 FROM AP_Invoice_Distributions_All AID
3044 WHERE AID.Prepay_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id)
3045 AND Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT', 'AWT', 'TOTAL PAYMENT ROUNDING',
3046 'FINAL PAYMENT ROUNDING');
3047
3048 -- Get the cleared base amount for the payment clearing event
3049 SELECT SUM(APHD.Cleared_Base_Amount)
3050 INTO l_sum_pay_clrd_base_amt
3051 FROM AP_Payment_Hist_Dists APHD,
3052 AP_Payment_History_All APH
3053 WHERE APH.Related_Event_ID = p_clr_hist_rec.related_event_id
3054 AND APHD.Payment_History_ID = APH.Payment_History_ID
3055 AND Invoice_Distribution_ID IN
3056 (SELECT AID.Invoice_Distribution_ID
3057 FROM AP_Invoice_Distributions_All AID
3058 WHERE AID.Prepay_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id)
3059 AND Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT', 'AWT', 'TOTAL CLEARING ROUNDING',
3060 'FINAL PAYMENT ROUNDING');
3061 */
3062
3063 /* Bug 13791619 */
3064 -- Get the paid base amount for the payment event
3065 SELECT SUM(APHD.Paid_Base_Amount)
3066 INTO l_sum_pay_paid_base_amt
3067 FROM AP_Payment_Hist_Dists APHD,
3068 AP_Payment_History_All APH
3069 WHERE APH.Related_Event_ID = p_pay_hist_rec.Related_Event_ID
3070 AND APHD.Payment_History_ID = APH.Payment_History_ID
3071 AND Invoice_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id
3072 AND Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT', 'AWT', 'TOTAL PAYMENT ROUNDING',
3073 'FINAL PAYMENT ROUNDING');
3074
3075 -- Get the cleared base amount for the payment clearing event
3076 SELECT SUM(APHD.Cleared_Base_Amount)
3077 INTO l_sum_pay_clrd_base_amt
3078 FROM AP_Payment_Hist_Dists APHD,
3079 AP_Payment_History_All APH
3080 WHERE APH.Related_Event_ID = p_clr_hist_rec.related_event_id
3081 AND APHD.Payment_History_ID = APH.Payment_History_ID
3082 AND Invoice_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id
3083 AND Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT', 'AWT', 'TOTAL CLEARING ROUNDING',
3084 'FINAL PAYMENT ROUNDING');
3085
3086
3087
3088 -- Get the sum of the base amounts for the different prepayment xrates
3089 SELECT SUM(Base_Amt_At_Prepay_XRate),
3090 SUM(Base_Amt_At_Prepay_Pay_XRate),
3091 SUM(Base_Amt_At_Prepay_Clr_XRate)
3092 INTO l_sum_prepay_rate_amt,
3093 l_sum_prepay_pay_rate_amt,
3094 l_sum_prepay_clr_rate_amt
3095 FROM AP_Prepay_App_Dists
3096 WHERE Prepay_App_Distribution_ID IN
3097 (SELECT AID.Invoice_Distribution_ID
3098 FROM AP_Invoice_Distributions_All AID
3099 WHERE AID.Prepay_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id);
3100
3101
3102 -- bug9609272, added the corresponding ERV amount to the base amount of
3103 -- the Prepayment Invoice distribution (which is as per the PO exchange
3104 -- rate), so as to get a sum as per the Prepayment Invoice Exchange Rate
3105 --
3106 SELECT AID.Base_Amount +
3107 NVL((SELECT SUM(NVL(AID_erv.Base_Amount, 0))
3108 FROM AP_Invoice_Distributions_All AID_erv
3109 WHERE AID_erv.Invoice_id = AID.Invoice_id
3110 AND AID_erv.line_type_lookup_code IN ('ERV', 'TERV')
3111 AND AID_erv.related_id = AID.invoice_distribution_id), 0)
3112 INTO l_sum_pay_base_amt
3113 FROM AP_Invoice_Distributions_All AID
3114 WHERE AID.Invoice_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id;
3115
3116 l_diff_prepay_rate_amt := -(l_sum_pay_base_amt + l_sum_prepay_rate_amt); -- 8256981
3117 l_diff_prepay_pay_rate_amt := -(l_sum_pay_paid_base_amt + l_sum_prepay_pay_rate_amt); -- 8256981
3118 l_diff_prepay_clr_rate_amt := -(l_sum_pay_clrd_base_amt + l_sum_prepay_clr_rate_amt); -- 8256981
3119
3120
3121 l_pad_rec.prepay_history_id := p_prepay_hist_rec.prepay_history_id;
3122 l_pad_rec.accounting_event_id := p_xla_event_rec.event_id;
3123 l_pad_rec.invoice_distribution_id := l_max_prepay_rec.invoice_distribution_id;
3124 l_pad_rec.prepay_app_distribution_id := l_max_prepay_rec.prepay_app_distribution_id;
3125
3126 l_pad_rec.prepay_exchange_rate := l_max_prepay_rec.prepay_exchange_rate;
3127 l_pad_rec.prepay_exchange_rate_type := l_max_prepay_rec.prepay_exchange_rate_type;
3128 l_pad_rec.prepay_exchange_date := l_max_prepay_rec.prepay_exchange_date;
3129 l_pad_rec.prepay_pay_exchange_rate := l_max_prepay_rec.prepay_pay_exchange_rate;
3130 l_pad_rec.prepay_pay_exchange_rate_type := l_max_prepay_rec.prepay_pay_exchange_rate_type;
3131 l_pad_rec.prepay_pay_exchange_date := l_max_prepay_rec.prepay_pay_exchange_date;
3132 l_pad_rec.prepay_clr_exchange_rate := l_max_prepay_rec.prepay_clr_exchange_rate;
3133 l_pad_rec.prepay_clr_exchange_rate_type := l_max_prepay_rec.prepay_clr_exchange_rate_type;
3134 l_pad_rec.prepay_clr_exchange_date := l_max_prepay_rec.prepay_clr_exchange_date;
3135 l_pad_rec.awt_related_id := l_max_prepay_rec.awt_related_id;
3136
3137 l_pad_rec.amount := 0;
3138 l_pad_rec.base_amount := 0;
3139
3140 IF (l_diff_prepay_rate_amt <> 0) OR (l_diff_prepay_pay_rate_amt <> 0)
3141 OR (l_diff_prepay_clr_rate_amt <> 0) THEN
3142
3143 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3144 l_log_msg := 'Inserting final appl rounding dist';
3148
3145 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3146 END IF;
3147
3149 l_pad_rec.prepay_dist_lookup_code := 'FINAL APPL ROUNDING';
3150 l_pad_rec.base_amt_at_prepay_xrate := l_diff_prepay_rate_amt;
3151 l_pad_rec.base_amt_at_prepay_pay_xrate := l_diff_prepay_pay_rate_amt;
3152 l_pad_rec.base_amt_at_prepay_clr_xrate := l_diff_prepay_clr_rate_amt;
3153
3154
3155 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3156 l_log_msg := 'Calling procedure Prepay_Dist_Insert';
3157 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
3158 END IF;
3159
3160
3161 AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Insert
3162 (l_pad_rec,
3163 l_curr_calling_sequence);
3164
3165 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3166 l_log_msg := 'Procedure Prepay_Dist_Insert executed';
3167 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
3168 END IF;
3169
3170
3171 END IF;
3172
3173 -- Logging Infra: Procedure level
3174 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3175 l_log_msg := 'End of procedure '|| l_procedure_name;
3176 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
3177 END IF;
3178
3179
3180 EXCEPTION
3181 WHEN OTHERS THEN
3182 IF (SQLCODE = -20100) THEN
3183 RAISE_APPLICATION_ERROR(-20100, SQLERRM);
3184 ELSIF (SQLCODE <> -20001) THEN
3185 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3186 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3187 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
3188 END IF;
3189 APP_EXCEPTION.RAISE_EXCEPTION;
3190
3191 END Final_Appl;
3192
3193 -------------------------------------------------------------------------------
3194 -- Final_Cash
3195 -- This is procedure is to handle cash rounding where the
3196 -- ap_checks_all.base_amount and
3197 -- sum(ap_invoice_payments_all.payment_base_amount) are not
3198 -- Matching.
3199 -- Bug 8288996
3200 --
3201 --
3202 --------------------------------------------------------------------------------
3203 PROCEDURE Final_Cash
3204 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
3205 ,P_Calling_Sequence IN VARCHAR2
3206 ) IS
3207
3208 l_curr_calling_sequence VARCHAR2(2000);
3209 l_max_pd_rec AP_PAYMENT_HIST_DISTS%ROWTYPE;
3210 l_pd_rec AP_PAYMENT_HIST_DISTS%ROWTYPE;
3211 l_ac_base_amount NUMBER;
3212 l_transaction_type VARCHAR2(50);
3213 l_sum_cash_amt NUMBER;
3214 l_ac_amount NUMBER;
3215 l_do_cash_rounding NUMBER;
3216 l_procedure_name CONSTANT VARCHAR2(30) := 'Final_Cash';
3217 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3218 l_pay_hist_rec ap_accounting_pay_pkg.r_pay_hist_info;
3219 BEGIN
3220
3221 l_curr_calling_sequence := 'AP_ACCTG_PAY_ROUND_PKG.Final_Cash<- ' ||
3222 P_Calling_Sequence;
3223
3224
3225 -- Logging Infra: Procedure level
3226 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3227 l_log_msg := 'Begin of procedure '|| l_procedure_name;
3228 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
3229 END IF;
3230
3231
3232 SELECT count(*) into l_do_cash_rounding
3233 FROM ap_invoice_payments_all aip
3234 WHERE check_id = P_XLA_Event_Rec.source_id_int_1
3235 AND NOT EXISTS (SELECT 1
3236 FROM ap_payment_hist_dists aphd
3237 WHERE aphd.invoice_payment_id = aip.invoice_payment_id);
3238
3239 IF ( l_do_cash_rounding = 0) THEN -- Now Do the Cash Rounding
3240
3241
3242 -- Here to handle Cash Rounding
3243 Begin
3244
3245 /* bug 11906025 and 12900918 ,12911326 modified the derivation logic of l_ac_base_amount */
3246
3247 SELECT decode(aps.recon_accounting_flag, 'Y',coalesce(ac.cleared_base_amount,ac.base_amount, 0),nvl(base_amount,0)),
3248 decode(aps.recon_accounting_flag, 'Y', 'CLEARING',
3249 decode(ac.future_pay_due_date, NULL, 'CREATED', 'MATURITY')) type,
3250 ac.amount
3251 INTO l_ac_base_amount, l_transaction_type, l_ac_amount
3252 FROM ap_checks_all ac,
3253 ap_system_parameters_all aps,
3254 ap_invoice_payments_all aip
3255 WHERE ac.org_id = aps.org_id
3256 AND ac.check_id = P_XLA_Event_Rec.source_id_int_1
3257 AND aip.check_id = ac.check_id
3258 AND ac.currency_code <> aps.base_currency_code
3259 AND aip.reversal_inv_pmt_id IS NULL
3260 GROUP BY ac.check_id, ac.amount, ac.base_amount , ac.future_pay_due_date, aps.recon_accounting_flag,ac.cleared_base_amount
3261 HAVING ABS(nvl(ac.base_amount, 0) -SUM(nvl(aip.payment_base_amount, 0))) > 0
3262 AND nvl(ac.base_amount, 0) <> SUM(nvl(aip.payment_base_amount, 0));
3263
3264 SELECT SUM(DECODE(l_transaction_type, 'CLEARING' , nvl(cleared_base_amount,0),
3265 'MATURITY' , nvl(matured_base_amount,0),nvl(paid_base_amount,0)))
3266 INTO l_sum_cash_amt
3267 FROM ap_payment_hist_dists aphd,
3268 ap_payment_history_all aph
3269 WHERE aph.payment_history_id = aphd.payment_history_id
3270 AND aph.check_id = P_XLA_Event_Rec.source_id_int_1
3271 AND aph.accounting_event_id = P_XLA_Event_Rec.event_id
3272 AND aph.posted_flag <> 'Y'
3273 AND aphd.pay_dist_lookup_code in ( 'CASH'
3277 AND aph.transaction_type = DECODE(l_transaction_type, 'CLEARING' , 'PAYMENT CLEARING',
3274 , 'FINAL CASH ROUNDING'
3275 , 'BANK CHARGE'
3276 , 'BANK ERROR') -- Bug 13783723
3278 'MATURITY' , 'PAYMENT MATURITY','PAYMENT CREATED');
3279
3280 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3281 l_log_msg := 'l_ac_base_amount :'|| l_ac_base_amount||' l_sum_cash_amt :'|| l_sum_cash_amt;
3282 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
3283 END IF;
3284
3285 IF ( l_sum_cash_amt <> l_ac_base_amount AND l_sum_cash_amt <> 0) Then
3286 --Getting Payment History Header Details
3287 SELECT APH.Payment_History_ID,
3288 APH.Pmt_Currency_Code,
3289 APH.Bank_Currency_Code,
3290 APH.Bank_To_Base_XRate_Type,
3291 APH.Bank_To_Base_XRate_Date,
3292 NVL(APH.Bank_To_Base_XRate,1)
3293 INTO l_pay_hist_rec.Payment_History_ID,
3294 l_pay_hist_rec.Pmt_Currency_Code,
3295 l_pay_hist_rec.Bank_Currency_Code,
3296 l_pay_hist_rec.Bank_To_Base_XRate_Type,
3297 l_pay_hist_rec.Bank_To_Base_XRate_Date,
3298 l_pay_hist_rec.Bank_To_Base_XRate
3299 FROM AP_Payment_History_All APH
3300 WHERE APH.Accounting_Event_ID = p_xla_event_rec.event_id;
3301
3302
3303 -- Getting the distribution for inserting the rounding distribution
3304 BEGIN
3305 SELECT APHD.*
3306 INTO l_max_pd_rec
3307 FROM AP_Payment_Hist_Dists APHD
3308 WHERE APHD.Accounting_Event_ID = p_xla_event_rec.event_id
3309 AND APHD.pay_dist_lookup_code = 'CASH'
3310 AND Rownum = 1;
3311 EXCEPTION
3312 WHEN NO_DATA_FOUND THEN
3313 RAISE_APPLICATION_ERROR(-20100, l_procedure_name||
3314 ' no_record_in_APHD_while_retrieving_max_dist');
3315 END; --bug 9936620
3316
3317 l_pd_rec.invoice_distribution_id := l_max_pd_rec.invoice_distribution_id;
3318 l_pd_rec.payment_history_id := l_max_pd_rec.payment_history_id;
3319 l_pd_rec.invoice_payment_id := l_max_pd_rec.invoice_payment_id;
3320 l_pd_rec.invoice_adjustment_event_id := l_max_pd_rec.invoice_adjustment_event_id;
3321 l_pd_rec.accounting_event_id := p_xla_event_rec.event_id;
3322 l_pd_rec.amount := 0;
3323 l_pd_rec.invoice_dist_amount := 0;
3324 l_pd_rec.bank_curr_amount := 0;
3325 l_pd_rec.invoice_dist_base_amount := 0;
3326 l_pd_rec.pay_dist_lookup_code := 'FINAL CASH ROUNDING';
3327 l_pd_rec.paid_base_amount := 0;
3328 l_pd_rec.cleared_base_amount := 0;
3329 l_pd_rec.matured_base_amount := 0;
3330 /* Restricting the Fix only to Payment Created
3331 When ever customers reported for Payment Maturity or Payment Clearing
3332 then just remove the conditions 1=2. Becuase of huge JLT changes now the
3333 fix is restricted to Payment Created. When ever the contions 1=2 are removed
3334 please make sure that JLT's are also Handeled */
3335 --bug 9495694, removed condition 1=2 for clearing transaction_type and added
3336 --condition to not go for final cash rounding if clearing is in ledger_currency
3337
3338 IF l_transaction_type = 'CLEARING' THEN
3339 IF l_pay_hist_rec.bank_currency_code <> ap_accounting_pay_pkg.g_base_currency_code Then
3340 l_ac_base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
3341 (l_ac_amount,
3342 l_pay_hist_rec.pmt_currency_code,
3343 ap_accounting_pay_pkg.g_base_currency_code,
3344 l_pay_hist_rec.bank_to_base_xrate_type,
3345 l_pay_hist_rec.bank_to_base_xrate_date,
3346 l_pay_hist_rec.bank_to_base_xrate,
3347 l_curr_calling_sequence);
3348 END IF; --bug 9710257, added IF
3349
3350 l_pd_rec.cleared_base_amount := l_ac_base_amount - l_sum_cash_amt;
3351
3352 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3353 l_log_msg := 'l_pd_rec.cleared_base_amount :'|| l_pd_rec.cleared_base_amount;
3354 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
3355 END IF;
3356
3357 ELSIF ( l_transaction_type = 'MATURITY' AND 1=2) Then
3358 l_pd_rec.matured_base_amount := l_ac_base_amount - l_sum_cash_amt;
3359 ELSE --bug 8880820
3360 IF ( l_transaction_type = 'CREATED') THEN
3361 l_pd_rec.paid_base_amount := l_ac_base_amount - l_sum_cash_amt;
3362 END IF; -- l_transaction_type
3363 END IF; --bug 8880820
3364
3365 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3366 l_log_msg := 'Calling proc AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert';
3367 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
3368 END IF;
3369
3370 IF (nvl(l_pd_rec.paid_base_amount,0) <> 0 OR
3371 nvl(l_pd_rec.cleared_base_amount,0) <> 0 OR
3372 nvl(l_pd_rec.matured_base_amount,0) <> 0 ) Then
3373
3374 AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert
3375 (l_pd_rec,
3376 l_curr_calling_sequence);
3377
3378 End IF; -- nvl(l_pd_rec.paid_base_amount,0) <> 0 ....
3379
3383 END IF;
3380 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3381 l_log_msg := 'Proc AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert executed';
3382 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
3384
3385 END IF; -- l_sum_cash_amt <> l_ac_base_amount
3386
3387 EXCEPTION
3388 WHEN OTHERS THEN
3389 IF (SQLCODE = -20100) THEN
3390 RAISE_APPLICATION_ERROR(-20100, SQLERRM);
3391 ELSE
3392 NULL;
3393 END IF;
3394 END;
3395
3396 END IF; -- l_do_cash_rounding = 0
3397
3398 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3399 l_log_msg := 'End of procedure '|| l_procedure_name;
3400 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
3401 END IF;
3402
3403 EXCEPTION
3404 WHEN OTHERS THEN
3405 IF (SQLCODE = -20100) THEN
3406 RAISE_APPLICATION_ERROR(-20100, SQLERRM);
3407 ELSIF (SQLCODE <> -20001) THEN
3408 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3409 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3410 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
3411 END IF;
3412 APP_EXCEPTION.RAISE_EXCEPTION;
3413
3414 END Final_Cash;
3415
3416
3417
3418 END AP_ACCTG_PAY_ROUND_PKG;