[Home] [Help]
PACKAGE BODY: APPS.AP_ACCTG_PAY_DIST_PKG
Source
1 PACKAGE BODY AP_ACCTG_PAY_DIST_PKG AS
2 /* $Header: appaydib.pls 120.28.12010000.17 2008/12/12 07:03:54 gkarampu ship $ */
3
4 G_Total_Dist_Amt NUMBER := 0;
5 G_Total_Prorated_Amt NUMBER := 0;
6 G_Total_Prorated_Disc_Amt NUMBER := 0;
7 G_Total_Inv_Dist_Amt NUMBER := 0;
8 G_Total_Inv_Dist_Disc_Amt NUMBER := 0;
9 G_Total_Bank_Curr_Amt NUMBER := 0;
10 G_Total_Bank_Curr_Disc_Amt NUMBER := 0;
11 G_Total_Dist_Amount NUMBER := 0; --BUG 7308385
12 G_Proration_Divisor NUMBER := 0; --BUG 7308385
13
14 -- Logging Infra
15 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
16 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
17 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
18 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
19 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
20 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
21 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
22 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'AP.PLSQL.AP_ACCTG_PAY_DIST_PKG.';
23 -- Logging Infra
24
25 -------------------------------------------------------------------------------
26 -- PROCEDURE UPDATE_GAIN_LOSS_IND
27 -- The purpose of this procedure is to update the gain_loss_indicator on the
28 -- payment history table based on the exchange rates of payment transactions.
29 --
30 --------------------------------------------------------------------------------
31 PROCEDURE Update_Gain_Loss_Ind
32 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
33 ,P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
34 ,P_Calling_Sequence IN VARCHAR2
35 ) IS
36
37 l_curr_calling_sequence VARCHAR2(2000);
38 l_pay_mat_count NUMBER;
39 l_when_to_account_gain_loss ap_system_parameters_all.when_to_account_gain_loss%TYPE;
40
41 -- Logging Infra:
42 l_procedure_name CONSTANT VARCHAR2(30) := 'Update_Gain_Loss_Ind';
43 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
44
45 BEGIN
46
47 l_curr_calling_sequence := 'AP_Acctg_Pay_Dist_Pkg.Update_Gain_Loss_Ind<- ' ||
48 p_calling_sequence;
49
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 ---Manual payment adusted event added in the list below for bug 7244022
61 --MANUAL PAYMENT ADJUSTED event is commented for bug 7445576
62
63 IF P_XLA_Event_Rec.Event_Type_Code IN ('PAYMENT CREATED', 'PAYMENT ADJUSTED', --'MANUAL PAYMENT ADJUSTED',
64 'REFUND ADJUSTED', 'REFUND RECORDED') THEN
65
66 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
67 l_log_msg := 'Updating Gain Loss Indicator for Events ' ||
68 P_XLA_Event_Rec.Event_Type_Code;
69 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
70 END IF;
71
72 UPDATE AP_Payment_History_All APH
73 SET Gain_Loss_Indicator =
74 (SELECT DECODE(SIGN(SUM(APHD.Invoice_Dist_Base_Amount - APHD.Paid_Base_Amount)),
75 1, 'G', -1, 'L', NULL)
76 FROM AP_Payment_Hist_Dists APHD
77 WHERE APHD.Payment_History_ID = APH.Payment_History_ID
78 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
79 --AND NVL(APHD.Reversal_Flag,'N') <> 'Y' --added for bug 7244022
80 --above condition commented for bug 7445576
81 )
82 WHERE APH.Payment_History_Id = p_pay_hist_rec.payment_history_id;
83
84 --added the following additional condition for bug 7445576
85 --reversal_flag condition should only be added for event type MANUAL PAYMENT ADJUSTED
86 --for MANUAL PAYMENT ADJUSTED event the accounting paradigm is accounting line reversal
87 --thus single loss or gain line gets created when PAYMENT CREATED has that
88 --this reversal line condition is used so that accounting gets balanced for the event type MANUAL PAYMENT ADJUSTED
89 ELSIF P_XLA_Event_Rec.Event_Type_Code = 'MANUAL PAYMENT ADJUSTED' THEN
90
91 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
92 l_log_msg := 'Updating Gain Loss Indicator for Events ' ||
93 P_XLA_Event_Rec.Event_Type_Code;
94 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
95 END IF;
96
97
98 UPDATE AP_Payment_History_All APH
99 SET Gain_Loss_Indicator =
100 (SELECT DECODE(SIGN(SUM(APHD.Invoice_Dist_Base_Amount - APHD.Paid_Base_Amount)),
101 1, 'G', -1, 'L', NULL)
102 FROM AP_Payment_Hist_Dists APHD
103 WHERE APHD.Payment_History_ID = APH.Payment_History_ID
104 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
105 AND NVL(APHD.Reversal_Flag,'N') <> 'Y'
106 )
107 WHERE APH.Payment_History_Id = p_pay_hist_rec.payment_history_id;
108
109
110 ELSIF P_XLA_Event_Rec.Event_Type_Code IN ('PAYMENT MATURED',
111 'PAYMENT MATURITY ADJUSTED') THEN
112
113 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
114 l_log_msg := 'Updating Gain Loss Indicator for Events: Event_ID = ' ||
115 P_XLA_Event_Rec.Event_ID;
116 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
117 END IF;
118
119 UPDATE AP_Payment_History_All APH
120 SET Gain_Loss_Indicator =
121 (SELECT DECODE(SIGN(SUM(APHD.Paid_Base_Amount - APHD.Matured_Base_Amount)),
122 1, 'G', -1, 'L', NULL)
123 FROM AP_Payment_Hist_Dists APHD
124 WHERE APHD.Payment_History_ID = APH.Payment_History_ID
125 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id)
126 WHERE APH.Payment_History_Id = p_pay_hist_rec.payment_history_id;
127
128 ELSIF P_XLA_Event_Rec.Event_Type_Code IN ('PAYMENT CLEARED',
129 'PAYMENT CLEARING ADJUSTED') THEN
130
131 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
132 l_log_msg := 'Updating Gain Loss Indicator for Events: Event_ID = ' ||
133 P_XLA_Event_Rec.Event_ID;
134 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
135 END IF;
136
137 SELECT count(*)
138 INTO l_pay_mat_count
139 FROM AP_Payment_History_All APH,
140 AP_Payment_History_All APH1
141 WHERE APH.Payment_History_Id = p_pay_hist_rec.payment_history_id
142 AND APH.Check_ID = APH1.Check_ID
143 AND APH.Transaction_Type IN ('PAYMENT CLEARING', 'PAYMENT CLEARING ADJUSTED')
144 AND APH1.Transaction_Type = 'PAYMENT MATURITY';
145
146 IF l_pay_mat_count > 0 THEN
147
148 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
149 l_log_msg := 'Updating Gain Loss between maturity and clearing events';
150 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
151 END IF;
152
153 UPDATE AP_Payment_History_All APH
154 SET Gain_Loss_Indicator =
155 (SELECT DECODE(SIGN(SUM(APHD.Matured_Base_Amount - APHD.Cleared_Base_Amount)),
156 1, 'G', -1, 'L', NULL)
157 FROM AP_Payment_Hist_Dists APHD
158 WHERE APHD.Payment_History_ID = APH.Payment_History_ID
159 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id)
160 WHERE APH.Payment_History_Id = p_pay_hist_rec.payment_history_id;
161 ELSE
162
163 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
164 l_log_msg := 'Updating Gain Loss between invoice/payment and clearing events';
165 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
166 END IF;
167
168 --bug 5257209
169
170 SELECT ASP.when_to_account_gain_loss
171 INTO l_when_to_account_gain_loss
172 FROM ap_system_parameters_all ASP,
173 AP_Payment_History_All APH
174 WHERE APH.org_id = ASP.org_id
175 AND APH.Payment_History_Id = p_pay_hist_rec.payment_history_id;
176
177 IF ( l_when_to_account_gain_loss IS NOT NULL AND
178 l_when_to_account_gain_loss = 'CLEARING ONLY' ) THEN
179
180 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
181 l_log_msg := 'Update Gain/Loss between invoice and clearing for gain/loss at clear only';
182 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
183 END IF;
184
185 -- Bug 6678474. Backing out the fix for bug 6621586 since it is incorrect.
186 UPDATE AP_Payment_History_All APH
187 SET Gain_Loss_Indicator =
188 (SELECT DECODE(SIGN(SUM(APHD.Invoice_Dist_Base_Amount - APHD.Cleared_Base_Amount)),
189 1, 'G', -1, 'L', NULL)
190 FROM AP_Payment_Hist_Dists APHD
191 WHERE APHD.Payment_History_ID = APH.Payment_History_ID
192 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id)
193 WHERE APH.Payment_History_Id = p_pay_hist_rec.payment_history_id;
194
195 ELSE
196
197 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
198 l_log_msg := 'Update Gain/Loss between payment and clearing for gain/loss at always';
199 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
200 END IF;
201
202 -- Bug 6678474. Backing out the fix for bug 6621586 since it is incorrect.
203 UPDATE AP_Payment_History_All APH
204 SET Gain_Loss_Indicator =
205 (SELECT DECODE(SIGN(SUM(APHD.Paid_Base_Amount - APHD.Cleared_Base_Amount)),
206 1, 'G', -1, 'L', NULL)
207 FROM AP_Payment_Hist_Dists APHD
208 WHERE APHD.Payment_History_ID = APH.Payment_History_ID
209 AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id)
210 WHERE APH.Payment_History_Id = p_pay_hist_rec.payment_history_id;
211
212 END IF;
213
214 END IF;
215
216 ELSIF P_XLA_Event_Rec.Event_Type_Code IN ('PAYMENT CANCELLED',
217 'PAYMENT MATURITY REVERSED',
218 'PAYMENT UNCLEARED',
219 'REFUND CANCELLED') THEN
220
221 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
222 l_log_msg := 'Updating Gain Loss Indicator for Events: Event_ID = ' ||
223 P_XLA_Event_Rec.Event_ID;
224 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
225 END IF;
226
227 UPDATE AP_Payment_History_All APH
228 SET APH.Gain_Loss_Indicator =
229 (SELECT Gain_Loss_Indicator
230 FROM AP_Payment_History_All APH1
231 WHERE APH1.Payment_History_ID = APH.Rev_Pmt_Hist_ID)
232 WHERE APH.Payment_History_Id = p_pay_hist_rec.payment_history_id;
233
234 END IF;
235
236 -- Logging Infra: Procedure level
237 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
238 l_log_msg := 'End of procedure '|| l_procedure_name;
239 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
240 END IF;
241
242
243 EXCEPTION
244 WHEN OTHERS THEN
245 IF (SQLCODE <> -20001) THEN
246 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
247 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
248 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
249 END IF;
250 APP_EXCEPTION.RAISE_EXCEPTION;
251
252 END Update_Gain_Loss_Ind;
253
254
255 -------------------------------------------------------------------------------
256 -- PROCEDURE Primary_Pay_Events
257 -- The purpose of this procedure is to prorate the payment amount for all the
258 -- distributions of the invoice and generate the payment history distribution.
259 --
260 --------------------------------------------------------------------------------
261 PROCEDURE Primary_Pay_Events
262 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
263 ,P_Calling_Sequence IN VARCHAR2
264 ) IS
265
266 l_curr_calling_sequence VARCHAR2(2000);
267 l_event_id NUMBER;
268 l_total_paid_amt NUMBER;
269 l_final_payment BOOLEAN := FALSE;
270 l_pay_history_id NUMBER;
271 l_mat_history_id NUMBER;
272
273 l_inv_pay_rec r_inv_pay_info;
274 l_pay_hist_rec ap_accounting_pay_pkg.r_pay_hist_info;
275 l_inv_rec ap_accounting_pay_pkg.r_invoices_info;
276 l_inv_dist_rec ap_accounting_pay_pkg.r_inv_dist_info;
277
278 -- Logging Infra:
279 l_procedure_name CONSTANT VARCHAR2(30) := 'Primary_Pay_Events';
280 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
281
282 -- bug fix 6674279
283 b_generate_pay_dist BOOLEAN;
284 l_sum_per_event NUMBER;
285 l_dist_count_per_event NUMBER;
286
287 -- bug 6900582
288 l_upg_pmt_hist NUMBER;
289 l_upg_inv_pmts NUMBER;
290 l_upg_event BOOLEAN;
291
292 -- condition: historical_flag =Y
293 -- and event is 'INVOICE ADJUSTMENT'
294 -- and ap_system_parameter.auto_offset_flag ='N'
295 -- and sum of the distributions in the invoice adjustment event is 0
296
297 CURSOR c_sum_per_event(p_acct_event_id NUMBER) IS
298 SELECT SUM(amount), count(1)
299 FROM ap_invoice_distributions_all aid,
300 xla_events evnt,
301 ap_system_parameters_all asp
302 WHERE aid.accounting_event_id = p_acct_event_id
303 AND aid.accounting_event_id = evnt.event_id
304 AND evnt.event_type_code in ('INVOICE ADJUSTED', 'CREDIT MEMO ADJUSTED',
305 'DEBIT MEMO ADJUSTED') --7630203
306 AND aid.org_id = asp.org_id
307 AND automatic_offsets_flag = 'N'
308 AND aid.historical_flag = 'Y';
309
310 BEGIN
311
312 l_curr_calling_sequence := 'AP_Acctg_Pay_Dist_Pkg.Primary_Pay_Events<- ' ||
313 p_calling_sequence;
314
315
316 -- Logging Infra: Setting up runtime level
317 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
318
319 -- Logging Infra: Procedure level
320 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
321 l_log_msg := 'Begin of procedure '|| l_procedure_name;
322 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
323 END IF;
324
325
326 OPEN Payment_History(P_XLA_Event_Rec.Event_ID);
327 FETCH Payment_History INTO l_pay_hist_rec;
328 CLOSE Payment_History;
329
330
331 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
332 l_log_msg := 'CUR: Payment_History: Payment_History_ID = '||
333 l_pay_hist_rec.payment_history_id ||
334 'and event type for payment is: ' ||
335 P_XLA_Event_Rec.Event_Type_Code;
336 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
337 END IF;
338
339 /* We need payment hist information for the prior events in order
340 to calculate the base amounts for the prior events using the
341 exchange rate info from the payment hist table */
342
343 IF (P_XLA_Event_Rec.Event_Type_Code IN ('PAYMENT MATURED')) THEN
344
345 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
346 l_log_msg := 'inside logic of payment matured event and event type'||
350
347 P_XLA_Event_Rec.Event_Type_Code;
348 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
349 END IF;
351 SELECT APH.Payment_History_ID,
352 APH.Pmt_To_Base_XRate_Type,
353 APH.Pmt_To_Base_XRate_Date,
354 APH.Pmt_To_Base_XRate
355 INTO l_pay_history_id,
356 ap_accounting_pay_pkg.g_pmt_to_base_xrate_type,
357 ap_accounting_pay_pkg.g_pmt_to_base_xrate_date,
358 ap_accounting_pay_pkg.g_pmt_to_base_xrate
359 FROM AP_Payment_History_All APH
360 WHERE APH.Payment_History_ID =
361 (SELECT MAX(APH1.Payment_History_ID)
362 FROM AP_Payment_History_All APH1
363 WHERE APH1.Check_ID = p_xla_event_rec.source_id_int_1
364 AND APH1.Transaction_Type IN ('PAYMENT CREATED', 'REFUND RECORDED'));
365
366
367 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
368 l_log_msg := 'Payment_History_ID for payment = '|| l_pay_history_id
369 || 'ap_accounting_pay_pkg.g_pmt_to_base_xrate_type'
370 || ap_accounting_pay_pkg.g_pmt_to_base_xrate_type
371 || 'ap_accounting_pay_pkg.g_pmt_to_base_xrate_date'
372 || ap_accounting_pay_pkg.g_pmt_to_base_xrate_date
373 || 'ap_accounting_pay_pkg.g_pmt_to_base_xrate'
374 || ap_accounting_pay_pkg.g_pmt_to_base_xrate;
375
376 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
377 END IF;
378
379 ap_accounting_pay_pkg.g_pay_pmt_history_id := l_pay_history_id;
380
381 ap_accounting_pay_pkg.g_mat_pmt_history_id := l_pay_hist_rec.payment_history_id;
382 ap_accounting_pay_pkg.g_mat_to_base_xrate_type :=
383 l_pay_hist_rec.pmt_to_base_xrate_type;
384 ap_accounting_pay_pkg.g_mat_to_base_xrate_date :=
385 l_pay_hist_rec.pmt_to_base_xrate_date;
386 ap_accounting_pay_pkg.g_mat_to_base_xrate := l_pay_hist_rec.pmt_to_base_xrate;
387
388 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
389 l_log_msg := 'After assign maturity related global variables and ' ||
390 'ap_accounting_pay_pkg.g_pay_pmt_history_id '||
391 ap_accounting_pay_pkg.g_pay_pmt_history_id ||
392 'ap_accounting_pay_pkg.g_mat_to_base_xrate_type' ||
393 ap_accounting_pay_pkg.g_mat_to_base_xrate_type ||
394 'ap_accounting_pay_pkg.g_mat_to_base_xrate_date' ||
395 ap_accounting_pay_pkg.g_mat_to_base_xrate_date ||
396 'ap_accounting_pay_pkg.g_mat_to_base_xrate' ||
397 ap_accounting_pay_pkg.g_mat_to_base_xrate;
398
399 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
400 END IF;
401
402 ELSIF (P_XLA_Event_Rec.Event_Type_Code IN ('PAYMENT CLEARED')) THEN
403
404 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
405 l_log_msg := 'inside logic of payment cleared event and event type'||
406 P_XLA_Event_Rec.Event_Type_Code;
407 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
408 END IF;
409
410 SELECT APH.Payment_History_ID,
411 APH.Pmt_To_Base_XRate_Type,
412 APH.Pmt_To_Base_XRate_Date,
413 APH.Pmt_To_Base_XRate
414 INTO l_pay_history_id,
415 ap_accounting_pay_pkg.g_pmt_to_base_xrate_type,
416 ap_accounting_pay_pkg.g_pmt_to_base_xrate_date,
417 ap_accounting_pay_pkg.g_pmt_to_base_xrate
418 FROM AP_Payment_History_All APH
419 WHERE APH.Payment_History_ID =
420 (SELECT MAX(APH1.Payment_History_ID)
421 FROM AP_Payment_History_All APH1
422 WHERE APH1.Check_ID = p_xla_event_rec.source_id_int_1
423 AND APH1.Transaction_Type IN ('PAYMENT CREATED', 'REFUND RECORDED'));
424
425 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
426 l_log_msg := 'Payment_History_ID for payment = '
427 || l_pay_history_id
428 || 'Payment_History_ID for maturity ='
429 || l_mat_history_id;
430 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
431 END IF;
432
433 SELECT MAX(APH.Payment_History_ID)
434 INTO l_mat_history_id
435 FROM AP_Payment_History_All APH
436 WHERE APH.Check_ID = p_xla_event_rec.source_id_int_1
437 AND APH.Transaction_Type IN ('PAYMENT MATURITY');
438
439
440 IF l_mat_history_id IS NOT NULL THEN
441
442 SELECT APH.Pmt_To_Base_XRate_Type,
443 APH.Pmt_To_Base_XRate_Date,
444 APH.Pmt_To_Base_XRate
445 INTO ap_accounting_pay_pkg.g_mat_to_base_xrate_type,
446 ap_accounting_pay_pkg.g_mat_to_base_xrate_date,
447 ap_accounting_pay_pkg.g_mat_to_base_xrate
448 FROM AP_Payment_History_All APH
449 WHERE APH.Payment_History_ID = l_mat_history_id;
450
451 END IF;
452
453
457 ap_accounting_pay_pkg.g_clr_pmt_history_id := l_pay_hist_rec.payment_history_id;
454 ap_accounting_pay_pkg.g_pay_pmt_history_id := l_pay_history_id;
455 ap_accounting_pay_pkg.g_mat_pmt_history_id := l_mat_history_id;
456
458 ap_accounting_pay_pkg.g_clr_to_base_xrate_type :=
459 l_pay_hist_rec.pmt_to_base_xrate_type;
460 ap_accounting_pay_pkg.g_clr_to_base_xrate_date :=
461 l_pay_hist_rec.pmt_to_base_xrate_date;
462 ap_accounting_pay_pkg.g_clr_to_base_xrate := l_pay_hist_rec.pmt_to_base_xrate;
463
464
465 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
466 l_log_msg := 'after set clearing global varaiables, they are'||
467 'ap_accounting_pay_pkg.g_pay_pmt_history_id=' ||
468 ap_accounting_pay_pkg.g_pay_pmt_history_id ||
469 'ap_accounting_pay_pkg.g_mat_pmt_history_id =' ||
470 ap_accounting_pay_pkg.g_mat_pmt_history_id ||
471 ' ap_accounting_pay_pkg.g_clr_pmt_history_id =' ||
472 ap_accounting_pay_pkg.g_clr_pmt_history_id ||
473 'ap_accounting_pay_pkg.g_clr_to_base_xrate_type=' ||
474 ap_accounting_pay_pkg.g_clr_to_base_xrate_type ||
475 'ap_accounting_pay_pkg.g_clr_to_base_xrate_date=' ||
476 ap_accounting_pay_pkg.g_clr_to_base_xrate_date ||
477 'ap_accounting_pay_pkg.g_clr_to_base_xrate=' ||
478 ap_accounting_pay_pkg.g_clr_to_base_xrate;
479
480 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
481 END IF;
482
483 ELSE
484
485 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
486 l_log_msg := 'inside logic of other payment event and event type'||
487 P_XLA_Event_Rec.Event_Type_Code;
488 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
489 END IF;
490
491 ap_accounting_pay_pkg.g_pay_pmt_history_id := l_pay_hist_rec.payment_history_id;
492 ap_accounting_pay_pkg.g_pmt_to_base_xrate_type :=
493 l_pay_hist_rec.pmt_to_base_xrate_type;
494 ap_accounting_pay_pkg.g_pmt_to_base_xrate_date :=
495 l_pay_hist_rec.pmt_to_base_xrate_date;
496 ap_accounting_pay_pkg.g_pmt_to_base_xrate := l_pay_hist_rec.pmt_to_base_xrate;
497
498 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
499 l_log_msg := 'Payment_History_ID for payment = '||
500 l_pay_hist_rec.payment_history_id;
501 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
502 END IF;
503
504 END IF;
505
506
507 -- Bug6900582
508 -- Get the count of historical payment history records for this check.
509 -- We should not do final and total rounding for payments that have been
510 -- upgraded to R12, since during upgrade the amounts are not populated for
511 -- some exchange rates in the ap_payment_hist_dists table and trying to
512 -- calculate the rounding for such payments will result in incorrect
513 -- accounting.
514
515 SELECT count(*)
516 INTO l_upg_pmt_hist
517 FROM AP_Payment_History_All
518 WHERE Check_ID = p_xla_event_rec.source_id_int_1
519 AND Historical_Flag = 'Y'
520 AND Posted_Flag = 'Y';
521
522
523 /* For Clearing and Maturity events we need to get all the invoice payments, but for the
524 Payment event we only need to get the invoice payments stamped with that event id */
525
526 IF (P_XLA_Event_Rec.Event_Type_Code IN ('PAYMENT CLEARED', 'PAYMENT MATURED')) THEN
527 OPEN Clrg_Invoice_Payments(P_XLA_Event_Rec.Source_ID_Int_1);
528 ELSE
529 OPEN Invoice_Payments(P_XLA_Event_Rec.Event_ID);
530 END IF;
531
532 LOOP
533
534 IF (P_XLA_Event_Rec.Event_Type_Code IN ('PAYMENT CLEARED', 'PAYMENT MATURED')) THEN
535 FETCH Clrg_Invoice_Payments INTO l_inv_pay_rec;
536 EXIT WHEN Clrg_Invoice_Payments%NOTFOUND OR
537 Clrg_Invoice_Payments%NOTFOUND IS NULL;
538
539 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
540 l_log_msg := 'loop through CUR: Clrg_Invoice_Payments: Invoice_ID = '||
541 l_inv_pay_rec.invoice_id;
542 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
543 END IF;
544
545 ELSE
546 FETCH Invoice_Payments INTO l_inv_pay_rec;
547 EXIT WHEN Invoice_Payments%NOTFOUND OR
548 Invoice_Payments%NOTFOUND IS NULL;
549
550 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
551 l_log_msg := 'loop through CUR: Invoice_Payments: Invoice_ID = '||
552 l_inv_pay_rec.invoice_id;
553 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
554 END IF;
555
556 END IF;
557
558
559 OPEN Invoice_Header(l_inv_pay_rec.invoice_id);
560 FETCH Invoice_Header INTO l_inv_rec;
561 CLOSE Invoice_Header;
562
563 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
567 END IF;
564 l_log_msg := 'inside payment cursor loop, after open CUR:Invoice_Header: Invoice_ID= '
565 || l_inv_rec.invoice_id;
566 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
568
569
570 -- Bug 6900582. While upgrading the payment hist dists table during R12
571 -- upgrade, the invoice, paid, matured and cleared base amounts are not
572 -- populated since the exchange rates are not available in a single table
573 -- and even if we can get exchange rates there is no guarantee that the
574 -- amounts are equal to the accounted amounts due to proration.
575
576 -- In order to fix bug 6900582, when there is a partial payment to an
577 -- invoice and the invoice has upgraded payments or when an upgraded
578 -- payment is matured or cleared, we will not calculate the final payment
579 -- rounding or any other roundings to avoid creating huge gain and loss.
580
581 -- If there is a rounding due to proration, instead of rounding the
582 -- the difference can go to gain loss account.
583 SELECT count(*)
584 INTO l_upg_inv_pmts
585 FROM AP_Invoice_Payments_All AIP
586 WHERE Invoice_ID = l_inv_pay_rec.invoice_id
587 AND EXISTS (SELECT 'Upg Payment'
588 FROM AP_Payment_History_All APH
589 WHERE APH.Check_ID = AIP.Check_ID
590 AND APH.Historical_Flag = 'Y'
591 AND APH.Posted_Flag = 'Y'
592 AND Rownum = 1);
593
594 IF l_upg_inv_pmts = 0 THEN
595 /* Check if the invoice is fully paid */
596 l_final_payment := AP_Accounting_Pay_Pkg.Is_Final_Payment
597 (l_inv_rec,
598 l_inv_pay_rec.amount,
599 l_inv_pay_rec.discount_taken,
600 0, -- prepay amount
601 p_xla_event_rec.event_type_code,
602 l_curr_calling_sequence);
603 -- bug7247744
604 ELSE
605 l_final_payment := FALSE;
606 END IF;
607
608 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
609 IF l_final_payment THEN
610 l_log_msg := 'Final payment of Invoice_ID '||l_inv_rec.invoice_id;
611 ELSE
612 l_log_msg := 'Not final payment of Invoice_ID '||l_inv_rec.invoice_id;
613 END IF;
614 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
615 END IF;
616
617 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
618 l_log_msg := 'Ready to open Invoice_Dists cursor after final payment check';
619 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
620 END IF;
621
622 -- Perfomance Fix 7308385
623 -- Same query is used 3 different places in Pkg. when
624 -- ever the query is modified pls do the same in reamining 2 places.
625 SELECT SUM(decode(aid.prepay_tax_parent_id, NULL, nvl(aid.amount, 0), 0)),
626 SUM(decode(aid.line_type_lookup_code, 'AWT', 0, nvl(aid.amount, 0)))
627 INTO G_Total_Dist_Amount , G_Proration_Divisor
628 FROM ap_invoice_distributions_all aid
629 WHERE aid.invoice_id = l_inv_pay_rec.invoice_id
630 AND aid.line_type_lookup_code <> 'PREPAY'
631 AND aid.prepay_distribution_id IS NULL
632 AND (aid.awt_invoice_payment_id IS NULL OR
633 aid.awt_invoice_payment_id = l_inv_pay_rec.invoice_payment_id) -- bug fix: 6725866
634 AND NOT EXISTS
635 (SELECT 1 FROM xla_events
636 WHERE event_id = aid.accounting_event_id
637 AND application_id = 200
638 AND event_type_code IN('INVOICE CANCELLED', 'PREPAYMENT CANCELLED',
639 'CREDIT MEMO CANCELLED', 'DEBIT MEMO CANCELLED'));
640
641 OPEN Invoice_Dists(l_inv_pay_rec.invoice_id);
642 LOOP
643
644 FETCH Invoice_Dists INTO l_inv_dist_rec;
645 EXIT WHEN Invoice_Dists%NOTFOUND OR
646 Invoice_Dists%NOTFOUND IS NULL;
647
648
649 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
650 l_log_msg := 'Loop start for cur Invoice_Dists : Invoice_Dists: Invoice_Distribution_ID = '
651 ||l_inv_dist_rec.invoice_distribution_id;
652 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
653 END IF;
654
655
656 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
657 l_log_msg := 'Calling procedure Pay_Dist_Proc for dist: '
658 || l_inv_dist_rec.invoice_distribution_id;
659 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
660 END IF;
661
662 -- bugfix 6674279
663 -- for upgraded invoice adjustment event, if the distributions in the adjustment event
664 -- have sum amount of 0, then don't create the payment distribution, this will avoid the
665 -- accounting failure when payment liability line using Business flow to derive the
666 -- accounting ccid from invoice liability as for such case in 11i, there is no liability
667 -- accounting line generated.
668
672 -- and sum of the distributions in the invoice adjustment event is 0
669 -- condition: historical_flag =Y
670 -- and event is 'INVOICE ADJUSTED'
671 -- and ap_system_parameter.auto_offset_flag ='N'
673
674 b_generate_pay_dist := TRUE;
675 IF l_inv_dist_rec.historical_flag ='Y' THEN
676 OPEN c_sum_per_event(l_inv_dist_rec.accounting_event_id);
677 FETCH c_sum_per_event into l_sum_per_event, l_dist_count_per_event;
678
679 -- > 0 case is to handled the case that only 1 line in adjustment event and itself amount is 0
680 If l_dist_count_per_event > 0 AND l_sum_per_event = 0 THEN
681 b_generate_pay_dist := FALSE;
682 END IF;
683
684 CLOSE c_sum_per_event;
685
686 END IF;
687
688 IF b_generate_pay_dist AND
689 ((l_inv_dist_rec.awt_invoice_payment_id IS NULL) OR
690 (l_inv_dist_rec.awt_invoice_payment_id = l_inv_pay_rec.invoice_payment_id)) THEN
691 -- Create awt distributions only when the awt is created during invoice time or
692 -- if the awt is created during the payment time then only those awt distributions
693 -- created during this payment
694 -- Create cash distribution lines
695 Pay_Dist_Proc(p_xla_event_rec,
696 l_inv_pay_rec,
697 l_pay_hist_rec,
698 l_inv_rec,
699 l_inv_dist_rec,
700 'P',
701 l_final_payment,
702 l_curr_calling_sequence);
703 END IF;
704
705
706 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
707 l_log_msg := 'Inside loop Invoice_Dists: After Procedure Pay_Dist_Proc executed';
708 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
709 END IF;
710
711 END LOOP;
712 CLOSE Invoice_Dists;
713
714 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
715 l_log_msg := 'cursor Invoice_Dists is closed ';
716 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
717 END IF;
718
719 G_Total_Dist_Amt := 0;
720 G_Total_Prorated_Amt := 0;
721 G_Total_Prorated_Disc_Amt := 0;
722 G_Total_Inv_Dist_Amt := 0;
723 G_Total_Inv_Dist_Disc_Amt := 0;
724 G_Total_Bank_Curr_Amt := 0;
725 G_Total_Bank_Curr_Disc_Amt := 0;
726
727
728 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
729 l_log_msg := 'Calling procedure P_Acctg_Pay_Round_Pkg.Do_Rounding for Invoice_ID: '
730 || l_inv_rec.invoice_id;
731 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
732 END IF;
733
734
735 -- Bug 6900582. Do not do rounding calculation if the payment is upgraded
736 -- or invoice has other upgraded payments
737 IF l_upg_inv_pmts = 0 AND l_upg_pmt_hist = 0 THEN
738 -- Create total and final rounding lines
739 AP_Acctg_Pay_Round_Pkg.Do_Rounding
740 (p_xla_event_rec,
741 l_pay_hist_rec,
742 NULL, -- clr hist rec
743 l_inv_rec,
744 l_inv_pay_rec,
745 NULL, -- prepay inv rec
746 NULL, -- prepay hist rec
747 NULL, -- prepay dist rec
748 l_curr_calling_sequence);
749
750 END IF;
751
752 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
753 l_log_msg := 'Procedure P_Acctg_Pay_Round_Pkg.Do_Rounding executed';
754 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
755 END IF;
756
757 END LOOP;
758
759 IF (P_XLA_Event_Rec.Event_Type_Code IN ('PAYMENT CLEARED', 'PAYMENT MATURED')) THEN
760 CLOSE Clrg_Invoice_Payments;
761 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
762 l_log_msg := 'after close cursor Clrg_Invoice_Payments ';
763 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
764 END IF;
765 ELSE
766 CLOSE Invoice_Payments;
767 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
768 l_log_msg := 'after close cursor Invoice_Payment ';
769 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
770 END IF;
771 END IF;
772
773
774 IF l_pay_hist_rec.Errors_Bank_Amount <> 0 THEN
775
776 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
777 l_log_msg := 'Calling procedure Pay_Dist_Err_Chrg for errors bank amount and '
778 || 'l_pay_hist_rec.Errors_Bank_Amount'
779 || l_pay_hist_rec.Errors_Bank_Amount;
780 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
781 END IF;
782
783
784 -- Create error distribution lines
785 --bug 5659368
786 Pay_Dist_Err_Chrg
787 ( p_xla_event_rec => p_xla_event_rec
788 ,p_pay_hist_rec => l_pay_hist_rec
792 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
789 ,p_distribute_mode => 'BANK_ERROR'
790 ,p_calling_sequence => l_curr_calling_sequence);
791
793 l_log_msg := 'Procedure Pay_Dist_Err_Chrg for errors bank amount executed';
794 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
795 END IF;
796
797 END IF;
798
799 IF l_pay_hist_rec.Charges_Bank_Amount <> 0 THEN
800
801 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
802 l_log_msg := 'Calling procedure Pay_Dist_Err_Chrg for charges bank amount =' ||
803 'l_pay_hist_rec.Charges_Bank_Amount' ||
804 l_pay_hist_rec.Charges_Bank_Amount;
805 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
806 END IF;
807
808
809 -- Create charge distribution lines
810 --bug 5659368
811
812 Pay_Dist_Err_Chrg
813 ( p_xla_event_rec => p_xla_event_rec
814 ,p_pay_hist_rec => l_pay_hist_rec
815 ,p_distribute_mode => 'BANK_CHARGE'
816 ,p_calling_sequence => l_curr_calling_sequence);
817
818 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
819 l_log_msg := 'Procedure Pay_Dist_Err_Chrg for charges bank amount executed';
820 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
821 END IF;
822
823 END IF;
824
825 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
826 l_log_msg := 'Calling procedure Update_Gain_Loss_Ind for payments';
827 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
828 END IF;
829
830 Update_Gain_Loss_Ind
831 (p_xla_event_rec,
832 l_pay_hist_rec,
833 l_curr_calling_sequence);
834
835 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
836 l_log_msg := 'Procedure Update_Gain_Loss_Ind executed';
837 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
838 END IF;
839
840 -- Logging Infra: Procedure level
841 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
842 l_log_msg := 'End of procedure '|| l_procedure_name;
843 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
844 END IF;
845
846
847 EXCEPTION
848 WHEN OTHERS THEN
849 IF (SQLCODE <> -20001) THEN
850 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
851 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
852 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
853 END IF;
854 APP_EXCEPTION.RAISE_EXCEPTION;
855
856 END Primary_Pay_Events;
857
858
859
860 -------------------------------------------------------------------------------
861 -- PROCEDURE Manual_Pay_Adj_Events
862 -- The purpose of this procedure is to prorate the payment amount for all the
863 -- distributions of the invoice for the manual adjustment event and
864 -- generate the payment history distribution.
865 --
866 --------------------------------------------------------------------------------
867 PROCEDURE Manual_Pay_Adj_Events
868 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
869 ,P_Calling_Sequence IN VARCHAR2
870 ) IS
871
872 l_curr_calling_sequence VARCHAR2(2000);
873 l_total_paid_amt NUMBER;
874 l_final_payment BOOLEAN := FALSE;
875
876 l_inv_pay_rec r_inv_pay_info;
877 l_pay_hist_rec ap_accounting_pay_pkg.r_pay_hist_info;
878 l_inv_rec ap_accounting_pay_pkg.r_invoices_info;
879 l_inv_dist_rec ap_accounting_pay_pkg.r_inv_dist_info;
880
881 -- Logging Infra:
882 l_procedure_name CONSTANT VARCHAR2(30) := 'Manual_Pay_Adj_Events';
883 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
884
885 BEGIN
886
887 l_curr_calling_sequence := 'AP_ACCTG_PAY_DIST_PKG.Manual_Pay_Adj_Events<-' ||
888 p_calling_sequence;
889
890 -- Logging Infra: Setting up runtime level
891 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
892
893 -- Logging Infra: Procedure level
894 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
895 l_log_msg := 'Begin of procedure '|| l_procedure_name;
896 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
897 END IF;
898
899
900 -- Get the payment hist info of the payment event
901 OPEN Payment_History(p_xla_event_rec.event_id);
902 FETCH Payment_History INTO l_pay_hist_rec;
903 CLOSE Payment_History;
904
905 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
906 l_log_msg := 'CUR: Payment_History: Payment_History_ID = '||
907 l_pay_hist_rec.payment_history_id;
908 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
909 END IF;
910
911
912 OPEN Invoice_Payments(p_xla_event_rec.event_id);
913 LOOP
914
915 Fetch Invoice_Payments INTO l_inv_pay_rec;
916 EXIT WHEN Invoice_Payments%NOTFOUND OR
917 Invoice_Payments%NOTFOUND IS NULL;
918
922 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
919 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
920 l_log_msg := 'CUR: Invoice_Payments: Invoice_ID = '||l_inv_pay_rec.invoice_id
921 || 'Reversal_Flag = '||l_inv_pay_rec.reversal_flag;
923 END IF;
924
925 /* If this invoice payment is a reversal the payment distributions will be created
926 by reversing the original distributions */
927 IF l_inv_pay_rec.reversal_flag = 'Y' THEN
928
929
930 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
931 l_log_msg := 'Calling procedure Pay_Dist_Reverse for invoice: '
932 || l_inv_pay_rec.invoice_id;
933 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
934 END IF;
935
936 -- Create payment hist distributions by reversing the
937 -- original payment hist dists.
938 Pay_Dist_Reverse
939 (p_xla_event_rec,
940 l_inv_pay_rec,
941 l_pay_hist_rec,
942 l_inv_pay_rec.reversal_inv_pmt_id,
943 NULL, -- related_Event_id
944 NULL, -- inv dist id
945 NULL, -- inv dist rec
946 l_curr_calling_sequence);
947
948 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
949 l_log_msg := 'Procedure Pay_Dist_Reverse executed';
950 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
951 END IF;
952
953 ELSE
954
955 OPEN Invoice_Header(l_inv_pay_rec.invoice_id);
956 FETCH Invoice_Header INTO l_inv_rec;
957 CLOSE Invoice_Header;
958
959
960 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
961 l_log_msg := 'CUR: Invoice_Header: Invoice_ID= '|| l_inv_rec.invoice_id;
962 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
963 END IF;
964
965
966 /* Check if the invoice is fully paid */
967 l_final_payment := AP_Accounting_Pay_Pkg.Is_Final_Payment
968 (l_inv_rec,
969 l_inv_pay_rec.amount,
970 l_inv_pay_rec.discount_taken,
971 0, -- prepay amount
972 p_xla_event_rec.event_type_code,
973 l_curr_calling_sequence);
974
975 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
976 IF l_final_payment THEN
977 l_log_msg := 'Final payment of Invoice_ID '||l_inv_rec.invoice_id;
978 ELSE
979 l_log_msg := 'Not final payment of Invoice_ID '||l_inv_rec.invoice_id;
980 END IF;
981 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
982 END IF;
983
984 -- Perfomance Fix 7308385
985 SELECT SUM(decode(aid.prepay_tax_parent_id, NULL, nvl(aid.amount, 0), 0)),
986 SUM(decode(aid.line_type_lookup_code, 'AWT', 0, nvl(aid.amount, 0)))
987 INTO G_Total_Dist_Amount , G_Proration_Divisor
988 FROM ap_invoice_distributions_all aid
989 WHERE aid.invoice_id = l_inv_pay_rec.invoice_id
990 AND aid.line_type_lookup_code <> 'PREPAY'
991 AND aid.prepay_distribution_id IS NULL
992 AND (aid.awt_invoice_payment_id IS NULL OR
993 aid.awt_invoice_payment_id = l_inv_pay_rec.invoice_payment_id) -- bug fix: 6725866
994 AND NOT EXISTS
995 (SELECT 1 FROM xla_events
996 WHERE event_id = aid.accounting_event_id
997 AND application_id = 200
998 AND event_type_code IN('INVOICE CANCELLED', 'PREPAYMENT CANCELLED',
999 'CREDIT MEMO CANCELLED', 'DEBIT MEMO CANCELLED'));
1000
1001 OPEN Invoice_Dists(l_inv_pay_rec.invoice_id);
1002 LOOP
1003
1004 FETCH Invoice_Dists INTO l_inv_dist_rec;
1005 EXIT WHEN Invoice_Dists%NOTFOUND OR
1006 Invoice_Dists%NOTFOUND IS NULL;
1007
1008 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1009 l_log_msg := 'CUR: Invoice_Dists: Invoice_Distribution_ID = '
1010 ||l_inv_dist_rec.invoice_distribution_id;
1011 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1012 END IF;
1013
1014
1015 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1016 l_log_msg := 'Calling procedure Pay_Dist_Proc for dist: '
1017 || l_inv_dist_rec.invoice_distribution_id;
1018 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1019 END IF;
1020
1021 -- Create awt distributions only when the awt is created during invoice time or
1022 -- if the awt is created during the payment time then only those awt distributions
1023 -- created during this payment
1024 IF (l_inv_dist_rec.awt_invoice_payment_id IS NULL) OR
1025 (l_inv_dist_rec.awt_invoice_payment_id = l_inv_pay_rec.invoice_payment_id) THEN
1026 -- Create cash distribution lines for the new invoice payment
1030 l_inv_rec,
1027 Pay_Dist_Proc(p_xla_event_rec,
1028 l_inv_pay_rec,
1029 l_pay_hist_rec,
1031 l_inv_dist_rec,
1032 'M',
1033 l_final_payment,
1034 l_curr_calling_sequence);
1035
1036 END IF;
1037
1038
1039 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1040 l_log_msg := 'Procedure Pay_Dist_Proc executed';
1041 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1042 END IF;
1043
1044
1045 END LOOP;
1046 CLOSE Invoice_Dists;
1047
1048 G_Total_Dist_Amt := 0;
1049 G_Total_Prorated_Amt := 0;
1050 G_Total_Prorated_Disc_Amt := 0;
1051 G_Total_Inv_Dist_Amt := 0;
1052 G_Total_Inv_Dist_Disc_Amt := 0;
1053 G_Total_Bank_Curr_Amt := 0;
1054 G_Total_Bank_Curr_Disc_Amt := 0;
1055
1056
1057 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1058 l_log_msg := 'Calling procedure P_Acctg_Pay_Round_Pkg.Do_Rounding for Invoice_ID: '
1059 || l_inv_rec.invoice_id;
1060 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1061 END IF;
1062
1063 -- Create total and final payment roundings
1064 AP_Acctg_Pay_Round_Pkg.Do_Rounding
1065 (p_xla_event_rec,
1066 l_pay_hist_rec,
1067 NULL, -- clr hist rec
1068 l_inv_rec,
1069 l_inv_pay_rec,
1070 NULL, -- prepay inv rec
1071 NULL, -- prepay hist rec
1072 NULL, -- prepay dist rec
1073 l_curr_calling_sequence);
1074
1075 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1076 l_log_msg := 'Procedure P_Acctg_Pay_Round_Pkg.Do_Rounding executed';
1077 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1078 END IF;
1079
1080
1081 END IF;
1082
1083 END LOOP;
1084 CLOSE Invoice_Payments;
1085
1086
1087
1088 IF l_pay_hist_rec.Errors_Bank_Amount <> 0 THEN
1089
1090 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1091 l_log_msg := 'Calling procedure Pay_Dist_Err_Chrg for errors bank amount';
1092 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1093 END IF;
1094
1095 --bug 5659368
1096 Pay_Dist_Err_Chrg
1097 ( p_xla_event_rec => p_xla_event_rec
1098 ,p_pay_hist_rec => l_pay_hist_rec
1099 ,p_distribute_mode => 'BANK_ERROR'
1100 ,p_calling_sequence => l_curr_calling_sequence);
1101
1102 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1103 l_log_msg := 'Procedure Pay_Dist_Err_Chrg for errors bank amount executed';
1104 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1105 END IF;
1106
1107 END IF;
1108
1109
1110 IF l_pay_hist_rec.Charges_Bank_Amount <> 0 THEN
1111
1112 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1113 l_log_msg := 'Calling procedure Pay_Dist_Err_Chrg for charges bank amount';
1114 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1115 END IF;
1116
1117 --bug 5659368
1118 Pay_Dist_Err_Chrg
1119 ( p_xla_event_rec => p_xla_event_rec
1120 ,p_pay_hist_rec => l_pay_hist_rec
1121 ,p_distribute_mode => 'BANK_CHARGE'
1122 ,p_calling_sequence => l_curr_calling_sequence);
1123
1124 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1125 l_log_msg := 'Procedure Pay_Dist_Err_Chrg for charges bank amount executed';
1126 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1127 END IF;
1128
1129 END IF;
1130
1131 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1132 l_log_msg := 'Calling procedure Update_Gain_Loss_Ind for payments';
1133 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1134 END IF;
1135
1136 Update_Gain_Loss_Ind
1137 (p_xla_event_rec,
1138 l_pay_hist_rec,
1139 l_curr_calling_sequence);
1140
1141 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1142 l_log_msg := 'Procedure Update_Gain_Loss_Ind executed';
1143 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1144 END IF;
1145
1146 -- Logging Infra: Procedure level
1147 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1148 l_log_msg := 'End of procedure '|| l_procedure_name;
1149 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
1150 END IF;
1151
1152
1153 EXCEPTION
1154 WHEN OTHERS THEN
1155 IF (SQLCODE <> -20001) THEN
1156 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1157 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1158 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1159 END IF;
1163 END Manual_Pay_Adj_Events;
1160 APP_EXCEPTION.RAISE_EXCEPTION;
1161
1162
1164
1165
1166
1167 -------------------------------------------------------------------------------
1168 -- PROCEDURE Cancel_Primary_Pay_Events
1169 -- The purpose of this procedure is to reverse the payment distributions
1170 -- for the payment transactions that have been cancelled, uncleared or
1171 -- unmatured and insert into the payment hist distribution table.
1172 --
1173 --------------------------------------------------------------------------------
1174 PROCEDURE Cancel_Primary_Pay_Events
1175 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
1176 ,P_Calling_Sequence IN VARCHAR2
1177 ) IS
1178
1179 l_curr_calling_sequence VARCHAR2(2000);
1180
1181 l_pay_hist_rec ap_accounting_pay_pkg.r_pay_hist_info;
1182
1183 -- Logging Infra:
1184 l_procedure_name CONSTANT VARCHAR2(30) := 'Cancel_Primary_Pay_Events';
1185 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1186
1187 BEGIN
1188
1189 l_curr_calling_sequence := 'AP_ACCTG_PAY_DIST_PKG.Cancel_Primary_Pay_Events<-' ||
1190 p_calling_sequence;
1191
1192 -- Logging Infra: Setting up runtime level
1193 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1194
1195 -- Logging Infra: Procedure level
1196 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1197 l_log_msg := 'Begin of procedure '|| l_procedure_name;
1198 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1199 END IF;
1200
1201
1202 OPEN Payment_History(p_xla_event_rec.event_id);
1203 FETCH Payment_History INTO l_pay_hist_rec;
1204 CLOSE Payment_History;
1205
1206 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1207 l_log_msg := 'CUR: Payment_History: Payment_History_ID = '||
1208 l_pay_hist_rec.payment_history_id;
1209 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1210 END IF;
1211
1212
1213 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1214 l_log_msg := 'Calling procedure Pay_Dist_Reverse for related event: '
1215 || l_pay_hist_rec.related_event_id;
1216 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1217 END IF;
1218
1219
1220 -- Create payment hist dists by reversing the original payment hist
1221 -- distributions
1222 Pay_Dist_Reverse
1223 (p_xla_event_rec,
1224 NULL,
1225 l_pay_hist_rec,
1226 NULL, -- reversal_inv_pmt_id,
1227 l_pay_hist_rec.related_event_id,
1228 NULL, -- invoice_dist_id
1229 NULL, -- inv_dist_rec
1230 l_curr_calling_sequence);
1231
1232 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1233 l_log_msg := 'Procedure Pay_Dist_Reverse executed';
1234 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1235 END IF;
1236
1237
1238 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1239 l_log_msg := 'Calling procedure Update_Gain_Loss_Ind for payments';
1240 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1241 END IF;
1242
1243 Update_Gain_Loss_Ind
1244 (p_xla_event_rec,
1245 l_pay_hist_rec,
1246 l_curr_calling_sequence);
1247
1248 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1249 l_log_msg := 'Procedure Update_Gain_Loss_Ind executed';
1250 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1251 END IF;
1252
1253
1254 EXCEPTION
1255 WHEN OTHERS THEN
1256 IF (SQLCODE <> -20001) THEN
1257 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1258 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1259 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1260 END IF;
1261 APP_EXCEPTION.RAISE_EXCEPTION;
1262
1263 END Cancel_Primary_Pay_Events;
1264
1265
1266 -------------------------------------------------------------------------------
1267 -- PROCEDURE Pay_Dist_Cascade_Adj_Events
1268 -- The purpose of this procedure is to prorate the payment amount for all the
1269 -- distributions of the invoice that has been adjusted and generate the
1270 -- payment history distribution.
1271 --
1272 --------------------------------------------------------------------------------
1273 PROCEDURE Pay_Dist_Cascade_Adj_Events
1274 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
1275 ,P_Calling_Sequence IN VARCHAR2
1276 ) IS
1277
1278 l_curr_calling_sequence VARCHAR2(2000);
1279 l_event_id NUMBER;
1280 l_inv_adj_amount NUMBER := 0;
1281 l_invoice_id NUMBER;
1282 l_sum_paid_amount NUMBER := 0;
1283 l_sum_disc_amount NUMBER := 0;
1284 l_sum_error_amount NUMBER := 0;
1285 l_sum_charge_amount NUMBER := 0;
1286 l_pay_history_id NUMBER;
1287 l_mat_history_id NUMBER;
1288
1289 l_inv_pay_rec r_inv_pay_info;
1290 l_pay_hist_rec ap_accounting_pay_pkg.r_pay_hist_info;
1291 l_inv_rec ap_accounting_pay_pkg.r_invoices_info;
1292 l_inv_dist_rec ap_accounting_pay_pkg.r_inv_dist_info;
1293
1297 CURSOR Inv_Adj_Dists
1294 l_pay_dist_cnt NUMBER;
1295 l_do_round NUMBER; --7454170 contains payment_history_id
1296
1298 (P_Event_ID NUMBER
1299 ,P_Invoice_ID NUMBER
1300 ,P_Related_Event_ID NUMBER) IS
1301 SELECT Distinct AID.Invoice_Distribution_ID,
1302 AID.Line_Type_Lookup_Code,
1303 AID.Amount,
1304 AID.Base_Amount,
1305 AID.PO_Distribution_ID,
1306 AID.RCV_Transaction_ID,
1307 NVL(AID.Reversal_Flag,'N'),
1308 AID.Parent_Reversal_ID,
1309 AID.AWT_Related_ID,
1310 AID.AWT_Invoice_Payment_ID,
1311 AID.Quantity_Variance,
1312 AID.Base_Quantity_Variance,
1313 AID.Amount_Variance,
1314 AID.Base_Amount_Variance,
1315 AID.historical_flag, -- bug fix 6674279
1316 AID.accounting_event_id -- bug fix 6674279
1317 FROM AP_Invoice_Distributions_All AID,
1318 AP_Payment_History_All APH,
1319 AP_Payment_Hist_Dists APHD
1320 WHERE AID.Invoice_ID = P_Invoice_ID
1321 AND NVL(AID.Reversal_Flag,'N') <> 'Y'
1322 AND AID.Invoice_Distribution_ID = APHD.Invoice_Distribution_ID
1323 AND APH.Payment_History_ID = APHD.Payment_History_ID
1324 AND APH.Related_Event_ID = P_Related_Event_ID
1325 AND NVL(AID.Accounting_Event_ID,-99) <> P_Event_ID
1326 AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'AWT');
1327
1328
1329 CURSOR Adj_Invoice_Payments
1330 (P_Check_ID NUMBER
1331 ,P_Invoice_ID NUMBER
1332 ) IS
1333 SELECT AIP.Invoice_ID,
1334 AIP.Invoice_Payment_ID,
1335 AIP.Amount,
1336 AIP.Discount_Taken,
1337 AIP.Payment_Base_Amount,
1338 AIP.Invoice_Base_Amount,
1339 AIP.Exchange_Rate_Type,
1340 AIP.Exchange_Date,
1341 AIP.Exchange_Rate,
1342 NVL(AIP.Reversal_Flag,'N'),
1343 AIP.Reversal_Inv_Pmt_ID
1344 FROM AP_Invoice_Payments_All AIP
1345 WHERE AIP.Check_ID = P_Check_ID
1346 AND AIP.Invoice_ID = P_Invoice_ID;
1347
1348 -- Logging Infra:
1349 l_procedure_name CONSTANT VARCHAR2(30) := 'Pay_Dist_Cascade_Adj_Events';
1350 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1351
1352
1353 BEGIN
1354
1355 l_curr_calling_sequence := 'AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Cascade_Adj_Events<- ' ||
1356 p_calling_sequence;
1357
1358 -- Logging Infra: Setting up runtime level
1359 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1360
1361 -- Logging Infra: Procedure level
1362 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1363 l_log_msg := 'Begin of procedure '|| l_procedure_name;
1364 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1365 END IF;
1366
1367
1368 OPEN Payment_History(p_xla_event_rec.event_id);
1369 FETCH Payment_History INTO l_pay_hist_rec;
1370 CLOSE Payment_History;
1371
1372
1373 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1374 l_log_msg := 'CUR: Payment_History: Payment_History_ID = '||
1375 l_pay_hist_rec.payment_history_id;
1376 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1377 END IF;
1378
1379
1380 /* We need payment hist information for the prior events in order
1381 to calculate the base amounts for the prior events using the
1382 exchange rate info from the payment hist table */
1383
1384 IF (P_XLA_Event_Rec.Event_Type_Code IN ('PAYMENT MATURITY ADJUSTED')) THEN
1385
1386 SELECT MAX(APH.Payment_History_ID)
1387 INTO l_pay_history_id
1388 FROM AP_Payment_History_All APH
1389 WHERE APH.Check_ID = p_xla_event_rec.source_id_int_1
1390 AND APH.Transaction_Type IN ('PAYMENT CREATED', 'REFUND RECORDED');
1391
1392 SELECT APH.Pmt_To_Base_XRate_Type,
1393 APH.Pmt_To_Base_XRate_Date,
1394 APH.Pmt_To_Base_XRate
1395 INTO ap_accounting_pay_pkg.g_pmt_to_base_xrate_type,
1396 ap_accounting_pay_pkg.g_pmt_to_base_xrate_date,
1397 ap_accounting_pay_pkg.g_pmt_to_base_xrate
1398 FROM AP_Payment_History_All APH
1399 WHERE APH.Payment_History_ID = l_pay_history_id;
1400
1401 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1402 l_log_msg := 'Payment_History_ID for payment = '|| l_pay_history_id;
1403 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1404 END IF;
1405
1406
1407 ap_accounting_pay_pkg.g_pay_pmt_history_id := l_pay_history_id;
1408
1409 ap_accounting_pay_pkg.g_mat_pmt_history_id := l_pay_hist_rec.payment_history_id;
1410 ap_accounting_pay_pkg.g_mat_to_base_xrate_type :=
1411 l_pay_hist_rec.pmt_to_base_xrate_type;
1412 ap_accounting_pay_pkg.g_mat_to_base_xrate_date :=
1413 l_pay_hist_rec.pmt_to_base_xrate_date;
1414 ap_accounting_pay_pkg.g_mat_to_base_xrate := l_pay_hist_rec.pmt_to_base_xrate;
1415
1416 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1417 l_log_msg := 'Payment_History_ID for maturity = '||
1418 l_pay_hist_rec.payment_history_id;
1419 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1423 ELSIF (P_XLA_Event_Rec.Event_Type_Code IN ('PAYMENT CLEARING ADJUSTED')) THEN
1420 END IF;
1421
1422
1424
1425 SELECT MAX(APH.Payment_History_ID)
1426 INTO l_pay_history_id
1427 FROM AP_Payment_History_All APH
1428 WHERE APH.Check_ID = p_xla_event_rec.source_id_int_1
1429 AND APH.Transaction_Type IN ('PAYMENT CREATED', 'REFUND RECORDED');
1430
1431 SELECT APH.Pmt_To_Base_XRate_Type,
1432 APH.Pmt_To_Base_XRate_Date,
1433 APH.Pmt_To_Base_XRate
1434 INTO ap_accounting_pay_pkg.g_pmt_to_base_xrate_type,
1435 ap_accounting_pay_pkg.g_pmt_to_base_xrate_date,
1436 ap_accounting_pay_pkg.g_pmt_to_base_xrate
1437 FROM AP_Payment_History_All APH
1438 WHERE APH.Payment_History_ID = l_pay_history_id;
1439
1440 SELECT MAX(APH.Payment_History_ID)
1441 INTO l_mat_history_id
1442 FROM AP_Payment_History_All APH
1443 WHERE APH.Check_ID = p_xla_event_rec.source_id_int_1
1444 AND APH.Transaction_Type IN ('PAYMENT MATURITY');
1445
1446
1447 IF l_mat_history_id IS NOT NULL THEN
1448
1449 SELECT APH.Pmt_To_Base_XRate_Type,
1450 APH.Pmt_To_Base_XRate_Date,
1451 APH.Pmt_To_Base_XRate
1452 INTO ap_accounting_pay_pkg.g_mat_to_base_xrate_type,
1453 ap_accounting_pay_pkg.g_mat_to_base_xrate_date,
1454 ap_accounting_pay_pkg.g_mat_to_base_xrate
1455 FROM AP_Payment_History_All APH
1456 WHERE APH.Payment_History_ID = l_mat_history_id;
1457
1458 END IF;
1459
1460 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1461 l_log_msg := 'Payment_History_ID for payment = '|| l_pay_history_id;
1462 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1463 END IF;
1464
1465 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1466 l_log_msg := 'Payment_History_ID for maturity = '|| l_mat_history_id;
1467 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1468 END IF;
1469
1470
1471 ap_accounting_pay_pkg.g_pay_pmt_history_id := l_pay_history_id;
1472 ap_accounting_pay_pkg.g_mat_pmt_history_id := l_mat_history_id;
1473
1474 ap_accounting_pay_pkg.g_clr_pmt_history_id := l_pay_hist_rec.payment_history_id;
1475 ap_accounting_pay_pkg.g_clr_to_base_xrate_type :=
1476 l_pay_hist_rec.pmt_to_base_xrate_type;
1477 ap_accounting_pay_pkg.g_clr_to_base_xrate_date :=
1478 l_pay_hist_rec.pmt_to_base_xrate_date;
1479 ap_accounting_pay_pkg.g_clr_to_base_xrate := l_pay_hist_rec.pmt_to_base_xrate;
1480
1481 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1482 l_log_msg := 'Payment_History_ID for clearing = '||
1483 l_pay_hist_rec.payment_history_id;
1484 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1485 END IF;
1486
1487 ELSE
1488
1489 ap_accounting_pay_pkg.g_pay_pmt_history_id := l_pay_hist_rec.payment_history_id;
1490 ap_accounting_pay_pkg.g_pmt_to_base_xrate_type :=
1491 l_pay_hist_rec.pmt_to_base_xrate_type;
1492 ap_accounting_pay_pkg.g_pmt_to_base_xrate_date :=
1493 l_pay_hist_rec.pmt_to_base_xrate_date;
1494 ap_accounting_pay_pkg.g_pmt_to_base_xrate := l_pay_hist_rec.pmt_to_base_xrate;
1495
1496 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1497 l_log_msg := 'Payment_History_ID for payment = '||
1498 l_pay_hist_rec.payment_history_id;
1499 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1500 END IF;
1501
1502
1503 END IF;
1504
1505
1506 SELECT AID.Invoice_ID
1507 INTO l_invoice_id
1508 FROM AP_Invoice_Distributions_All AID
1509 WHERE AID.Accounting_Event_ID = l_pay_hist_rec.invoice_adjustment_event_id
1510 AND Rownum = 1;
1511
1512 OPEN Invoice_Header(l_invoice_id);
1513 FETCH Invoice_Header INTO l_inv_rec;
1514 CLOSE Invoice_Header;
1515
1516
1517
1518 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1519 l_log_msg := 'CUR: Invoice_Header: Invoice_ID= '|| l_inv_rec.invoice_id;
1520 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1521 END IF;
1522
1523
1524 /* Get the invoice payments that need to be adjusted */
1525 OPEN Adj_Invoice_Payments(p_xla_event_rec.source_id_int_1,
1526 l_invoice_id);
1527 LOOP
1528
1529 Fetch Adj_Invoice_Payments INTO l_inv_pay_rec;
1530 EXIT WHEN Adj_Invoice_Payments%NOTFOUND OR
1531 Adj_Invoice_Payments%NOTFOUND IS NULL;
1532
1533
1534 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1535 l_log_msg := 'CUR: Invoice_Payments: Invoice_ID = '||
1536 l_inv_pay_rec.invoice_id;
1537 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1538 END IF;
1539
1540 -- Perfomance Fix 7308385
1541 SELECT SUM(decode(aid.prepay_tax_parent_id, NULL, nvl(aid.amount, 0), 0)),
1542 SUM(decode(aid.line_type_lookup_code, 'AWT', 0, nvl(aid.amount, 0)))
1546 AND aid.line_type_lookup_code <> 'PREPAY'
1543 INTO G_Total_Dist_Amount , G_Proration_Divisor
1544 FROM ap_invoice_distributions_all aid
1545 WHERE aid.invoice_id = l_inv_pay_rec.invoice_id
1547 AND aid.prepay_distribution_id IS NULL
1548 AND (aid.awt_invoice_payment_id IS NULL OR
1549 aid.awt_invoice_payment_id = l_inv_pay_rec.invoice_payment_id) -- bug fix: 6725866
1550 AND NOT EXISTS
1551 (SELECT 1 FROM xla_events
1552 WHERE event_id = aid.accounting_event_id
1553 AND application_id = 200
1554 AND event_type_code IN('INVOICE CANCELLED', 'PREPAYMENT CANCELLED',
1555 'CREDIT MEMO CANCELLED', 'DEBIT MEMO CANCELLED'));
1556
1557 -- Get the new or reversed invoice dists
1558 OPEN Invoice_Dists(l_invoice_id,
1559 l_pay_hist_rec.invoice_adjustment_event_id);
1560 LOOP
1561
1562 FETCH Invoice_Dists INTO l_inv_dist_rec;
1563 EXIT WHEN Invoice_Dists%NOTFOUND OR
1564 Invoice_Dists%NOTFOUND IS NULL;
1565
1566
1567 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1568 l_log_msg := 'CUR: Invoice_Dists: Invoice_Distribution_ID = '
1569 ||l_inv_dist_rec.invoice_distribution_id
1570 ||'Reversal_Flag = '||l_inv_dist_rec.reversal_flag;
1571 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1572 END IF;
1573
1574 -- Bug 7384943. Get the count of payment dists for the parent invoice dist
1575 l_pay_dist_cnt :=0; --7602927 Intialising
1576 IF l_inv_dist_rec.parent_reversal_id IS NOT NULL THEN
1577
1578 SELECT count(*)
1579 INTO l_pay_dist_cnt
1580 FROM ap_payment_hist_dists
1581 WHERE invoice_distribution_id = l_inv_dist_rec.parent_reversal_id;
1582
1583 END IF;
1584
1585 -- Bug 7384943. Call pay_dist_reverse only if there exists payment
1586 -- dists for the parent invoice dist otherwise create payment dists
1587 -- by calculating the prorated amounts
1588
1589 IF l_inv_dist_rec.reversal_flag = 'Y' AND
1590 l_inv_dist_rec.parent_reversal_id IS NOT NULL AND -- Bug 7602927
1591 l_pay_dist_cnt > 0 THEN
1592
1593 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1594 l_log_msg := 'Calling procedure Pay_Dist_Reverse for dist: '
1595 || l_inv_dist_rec.invoice_distribution_id;
1596 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1597 END IF;
1598
1599 Pay_Dist_Reverse
1600 (p_xla_event_rec,
1601 NULL, -- inv_pay_rec
1602 l_pay_hist_rec, -- pay_hist_rec
1603 NULL, -- reversal_inv_pmt_id,
1604 NULL, -- related_event_id,
1605 l_inv_dist_rec.parent_reversal_id, -- invoice_dist_id
1606 l_inv_dist_rec, -- Bug6887295
1607 l_curr_calling_sequence);
1608
1609 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1610 l_log_msg := 'Procedure Pay_Dist_Reverse executed';
1611 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1612 END IF;
1613
1614
1615 ELSE
1616
1617 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1618 l_log_msg := 'Calling procedure Pay_Dist_Proc for dist: '
1619 || l_inv_dist_rec.invoice_distribution_id;
1620 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1621 END IF;
1622
1623
1624 -- Create awt distributions only when the awt is created during invoice time or
1625 -- if the awt is created during the payment time then only those awt distributions
1626 -- created during this payment
1627 IF (l_inv_dist_rec.awt_invoice_payment_id IS NULL) OR
1628 (l_inv_dist_rec.awt_invoice_payment_id = l_inv_pay_rec.invoice_payment_id) THEN
1629 Pay_Dist_Proc(p_xla_event_rec,
1630 l_inv_pay_rec,
1631 l_pay_hist_rec,
1632 l_inv_rec,
1633 l_inv_dist_rec,
1634 'C',
1635 NULL,
1636 l_curr_calling_sequence);
1637
1638 END IF;
1639
1640 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1641 l_log_msg := 'Procedure Pay_Dist_Proc executed';
1642 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1643 END IF;
1644
1645
1646 END IF;
1647
1648 END LOOP;
1649 CLOSE Invoice_Dists;
1650
1651
1652 SELECT SUM(AID.Amount)
1653 INTO l_inv_adj_amount
1654 FROM AP_Invoice_Distributions_All AID
1655 WHERE AID.Accounting_Event_ID = l_pay_hist_rec.invoice_adjustment_event_id;
1656
1657
1661 END IF;
1658 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1659 l_log_msg := 'l_inv_adj_amount = ' || l_inv_adj_amount;
1660 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1662
1663 /* Check if there is any change to the invoice liability. If there is
1664 a change then we need to adjust the payment hist distributions for the
1665 old invoice distributions */
1666
1667 /* uncommenting the code for bug 7560247.
1668 For partially paid invoices are adjusted, for the payment's
1669 adjustment events, the values are populating wrongly.
1670 Due to commenting the below code, it is inserting the data
1671 in payment hist dist only for the adjusted distributions and not for all
1672 the distributions. But it should do for all the distributions */
1673
1674 --/* commented the code for bug 7147610
1675 -- For the Payment Adjustments we are populating the
1676 -- Payment Hists Dists in the cursor loop Invoice_Dists
1677 -- This Inv_Adj_Dists is not required.
1678 IF l_inv_adj_amount <> 0 THEN
1679
1680 OPEN Inv_Adj_Dists(l_pay_hist_rec.invoice_adjustment_event_id,
1681 l_inv_rec.invoice_id,
1682 l_pay_hist_rec.related_event_id);
1683 LOOP
1684
1685 FETCH Inv_Adj_Dists INTO l_inv_dist_rec;
1686 EXIT WHEN Inv_Adj_Dists%NOTFOUND OR
1687 Inv_Adj_Dists%NOTFOUND IS NULL;
1688
1689
1690 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1691 l_log_msg := 'CUR: Inv_Adj_Dists: Invoice_Distribution_ID = '
1692 ||l_inv_dist_rec.invoice_distribution_id;
1693 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1694 END IF;
1695
1696
1697 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1698 l_log_msg := 'Calling procedure Pay_Dist_Proc for dist: '
1699 || l_inv_dist_rec.invoice_distribution_id;
1700 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1701 END IF;
1702
1703 Pay_Dist_Proc(p_xla_event_rec,
1704 l_inv_pay_rec,
1705 l_pay_hist_rec,
1706 l_inv_rec,
1707 l_inv_dist_rec,
1708 'C',
1709 NULL,
1710 l_curr_calling_sequence);
1711
1712 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1713 l_log_msg := 'Procedure Pay_Dist_Proc executed';
1714 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1715 END IF;
1716
1717
1718 END LOOP;
1719 CLOSE Inv_Adj_Dists;
1720 END IF;
1721 /* code changes end for the bug 7560247 */
1722
1723 -- BUG 7454170 and BUG 7489271
1724 -- Calling rounding only once for all payment adjsument events
1725 -- for each invoice payment level.
1726
1727 SELECT max(aph2.payment_history_id) into l_do_round
1728 FROM ap_payment_history_all aph1,
1729 ap_payment_history_all aph2
1730 WHERE aph1.payment_history_id = l_pay_hist_rec.payment_history_id
1731 AND aph2.check_id = aph1.check_id
1732 AND aph2.posted_flag <> 'Y'
1733 AND aph1.posted_flag <> 'Y'
1734 AND aph1.transaction_type = aph2.transaction_type
1735 AND l_invoice_id = (SELECT invoice_id
1736 FROM ap_invoice_distributions_all d
1737 WHERE d.accounting_event_id = aph2.invoice_adjustment_event_id
1738 AND rownum = 1);
1739
1740 IF ( l_do_round = l_pay_hist_rec.payment_history_id ) Then
1741
1742
1743 SELECT SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'CASH', APHD.Amount, 0)),
1744 SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'DISCOUNT', APHD.Amount, 0)),
1745 SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'BANK ERROR', APHD.Amount, 0)),
1746 SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'BANK CHARGE', APHD.Amount, 0))
1747 INTO l_sum_paid_amount,
1748 l_sum_disc_amount,
1749 l_sum_error_amount,
1750 l_sum_charge_amount
1751 FROM AP_Payment_Hist_Dists APHD,
1752 AP_Invoice_Distributions_All AID,
1753 AP_Payment_History_All APH
1754 WHERE APH.Related_Event_ID = l_pay_hist_rec.related_event_id
1755 AND APHD.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
1756 AND APH.Payment_History_ID = APHD.Payment_History_ID
1757 AND APHD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
1758 AND AID.Invoice_ID = l_invoice_id;
1759
1760
1761 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1762 l_log_msg := 'Adjusting payment amount for technical rounding';
1763 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1764 END IF;
1765
1766
1767 /* Adjust the payment amount for technical rounding */
1768 UPDATE AP_Payment_Hist_Dists APD
1769 SET APD.Amount = APD.Amount - NVL(l_sum_paid_amount,0) + l_inv_pay_rec.amount
1770 WHERE APD.Invoice_Distribution_ID =
1771 (SELECT MAX(APD1.Invoice_Distribution_ID)
1772 FROM AP_Payment_Hist_Dists APD1
1776 AND ABS(APD1.Amount) =
1773 WHERE APD1.Accounting_Event_ID = p_xla_event_rec.event_id
1774 AND APD1.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
1775 AND APD1.Pay_Dist_Lookup_Code = 'CASH'
1777 (SELECT MAX(APD2.Amount)
1778 FROM AP_Payment_Hist_Dists APD2
1779 WHERE APD2.Accounting_Event_ID = p_xla_event_rec.event_id
1780 AND APD2.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
1781 AND APD2.Pay_Dist_Lookup_Code = 'CASH'))
1782 AND APD.Pay_Dist_Lookup_Code = 'CASH'
1783 AND APD.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
1784 AND APD.Payment_History_ID = l_pay_hist_rec.payment_history_id
1785 AND APD.Accounting_Event_ID = p_xla_event_rec.event_id;
1786
1787
1788 IF (l_inv_pay_rec.discount_taken <> 0) THEN
1789
1790 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1791 l_log_msg := 'Adjusting discount amount for technical rounding';
1792 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1793 END IF;
1794
1795
1796 /* Adjust the discount amount for technical rounding */
1797 UPDATE AP_Payment_Hist_Dists APD
1798 SET APD.Amount = APD.Amount - NVL(l_sum_disc_amount,0)
1799 + l_inv_pay_rec.discount_taken
1800 WHERE APD.Invoice_Distribution_ID =
1801 (SELECT MAX(APD1.Invoice_Distribution_ID)
1802 FROM AP_Payment_Hist_Dists APD1
1803 WHERE APD1.Accounting_Event_ID = p_xla_event_rec.event_id
1804 AND APD1.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
1805 AND APD1.Pay_Dist_Lookup_Code = 'DISCOUNT'
1806 AND ABS(APD1.Amount) =
1807 (SELECT MAX(APD2.Amount)
1808 FROM AP_Payment_Hist_Dists APD2
1809 WHERE APD2.Accounting_Event_ID = p_xla_event_rec.event_id
1810 AND APD2.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
1811 AND APD2.Pay_Dist_Lookup_Code = 'DISCOUNT'))
1812 AND APD.Pay_Dist_Lookup_Code = 'DISCOUNT'
1813 AND APD.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
1814 AND APD.Payment_History_ID = l_pay_hist_rec.payment_history_id
1815 AND APD.Accounting_Event_ID = p_xla_event_rec.event_id;
1816
1817 END IF;
1818
1819 IF (l_pay_hist_rec.errors_bank_amount <> 0) THEN
1820
1821 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1822 l_log_msg := 'Adjusting errors bank amount for technical rounding';
1823 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1824 END IF;
1825
1826
1827 /* Adjust the bank errors amount for technical rounding */
1828 UPDATE AP_Payment_Hist_Dists APD
1829 SET APD.Amount = APD.Amount - NVL(l_sum_error_amount,0)
1830 + l_pay_hist_rec.errors_bank_amount
1831 WHERE APD.Invoice_Distribution_ID =
1832 (SELECT MAX(APD1.Invoice_Distribution_ID)
1833 FROM AP_Payment_Hist_Dists APD1
1834 WHERE APD1.Accounting_Event_ID = p_xla_event_rec.event_id
1835 AND APD1.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
1836 AND APD1.Pay_Dist_Lookup_Code = 'BANK ERROR'
1837 AND ABS(APD1.Amount) =
1838 (SELECT MAX(APD2.Amount)
1839 FROM AP_Payment_Hist_Dists APD2
1840 WHERE APD2.Accounting_Event_ID = p_xla_event_rec.event_id
1841 AND APD2.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
1842 AND APD2.Pay_Dist_Lookup_Code = 'BANK ERROR'))
1843 AND APD.Pay_Dist_Lookup_Code = 'BANK ERROR'
1844 AND APD.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
1845 AND APD.Payment_History_ID = l_pay_hist_rec.payment_history_id
1846 AND APD.Accounting_Event_ID = p_xla_event_rec.event_id;
1847
1848 END IF;
1849
1850 IF (l_pay_hist_rec.charges_bank_amount <> 0) THEN
1851
1852
1853 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1854 l_log_msg := 'Adjusting charges bank amount for technical rounding';
1855 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1856 END IF;
1857
1858 /* Adjust the bank charges amount for technical rounding */
1859 UPDATE AP_Payment_Hist_Dists APD
1860 SET APD.Amount = APD.Amount - NVL(l_sum_charge_amount,0)
1861 + l_pay_hist_rec.charges_bank_amount
1862 WHERE APD.Invoice_Distribution_ID =
1863 (SELECT MAX(APD1.Invoice_Distribution_ID)
1864 FROM AP_Payment_Hist_Dists APD1
1865 WHERE APD1.Accounting_Event_ID = p_xla_event_rec.event_id
1866 AND APD1.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
1867 AND APD1.Pay_Dist_Lookup_Code = 'BANK CHARGE'
1868 AND ABS(APD1.Amount) =
1869 (SELECT MAX(APD2.Amount)
1870 FROM AP_Payment_Hist_Dists APD2
1874 AND APD.Pay_Dist_Lookup_Code = 'BANK CHARGE'
1871 WHERE APD2.Accounting_Event_ID = p_xla_event_rec.event_id
1872 AND APD2.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
1873 AND APD2.Pay_Dist_Lookup_Code = 'BANK CHARGE'))
1875 AND APD.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
1876 AND APD.Payment_History_ID = l_pay_hist_rec.payment_history_id
1877 AND APD.Accounting_Event_ID = p_xla_event_rec.event_id;
1878
1879 END IF;
1880
1881
1882 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1883 l_log_msg := 'Calling procedure P_Acctg_Pay_Round_Pkg.Do_Rounding for Invoice_ID: '
1884 || l_inv_rec.invoice_id;
1885 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1886 END IF;
1887
1888 -- Create total and final payment rounding lines
1889 AP_Acctg_Pay_Round_Pkg.Do_Rounding
1890 (p_xla_event_rec,
1891 l_pay_hist_rec,
1892 NULL, -- clr hist rec
1893 l_inv_rec,
1894 l_inv_pay_rec,
1895 NULL, -- prepay inv rec
1896 NULL, -- prepay hist rec
1897 NULL, -- prepay dist rec
1898 l_curr_calling_sequence);
1899
1900 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1901 l_log_msg := 'Procedure P_Acctg_Pay_Round_Pkg.Do_Rounding executed';
1902 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1903 END IF;
1904 END IF; --l_do_round = l_pay_hist_rec.payment_history_id 7489271
1905
1906 END LOOP;
1907 CLOSE Adj_Invoice_Payments;
1908
1909
1910 IF l_pay_hist_rec.Errors_Bank_Amount <> 0 THEN
1911
1912 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1913 l_log_msg := 'Calling procedure Pay_Dist_Err_Chrg for errors bank amount';
1914 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1915 END IF;
1916
1917 --bug 5659368
1918 Pay_Dist_Err_Chrg
1919 ( p_xla_event_rec => p_xla_event_rec
1920 ,p_pay_hist_rec => l_pay_hist_rec
1921 ,p_distribute_mode => 'BANK_ERROR'
1922 ,p_calling_sequence => l_curr_calling_sequence);
1923
1924 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1925 l_log_msg := 'Procedure Pay_Dist_Err_Chrg for errors bank amount executed';
1926 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1927 END IF;
1928
1929
1930 END IF;
1931
1932
1933 IF l_pay_hist_rec.Charges_Bank_Amount <> 0 THEN
1934
1935 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1936 l_log_msg := 'Calling procedure Pay_Dist_Err_Chrg for charges bank amount';
1937 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1938 END IF;
1939
1940 --bug 5659368
1941 Pay_Dist_Err_Chrg
1942 ( p_xla_event_rec => p_xla_event_rec
1943 ,p_pay_hist_rec => l_pay_hist_rec
1944 ,p_distribute_mode => 'BANK_CHARGE'
1945 ,p_calling_sequence => l_curr_calling_sequence);
1946
1947 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1948 l_log_msg := 'Procedure Pay_Dist_Err_Chrg for charges bank amount executed';
1949 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1950 END IF;
1951
1952 END IF;
1953
1954 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1955 l_log_msg := 'Calling procedure Update_Gain_Loss_Ind for payments';
1956 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1957 END IF;
1958
1959 Update_Gain_Loss_Ind
1960 (p_xla_event_rec,
1961 l_pay_hist_rec,
1962 l_curr_calling_sequence);
1963
1964 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1965 l_log_msg := 'Procedure Update_Gain_Loss_Ind executed';
1966 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1967 END IF;
1968
1969
1970 -- Logging Infra: Procedure level
1971 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1972 l_log_msg := 'End of procedure '|| l_procedure_name;
1973 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
1974 END IF;
1975
1976
1977 EXCEPTION
1978 WHEN OTHERS THEN
1979 IF (SQLCODE <> -20001) THEN
1980 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1981 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1982 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1983 END IF;
1984 APP_EXCEPTION.RAISE_EXCEPTION;
1985
1986 END Pay_Dist_Cascade_Adj_Events;
1987
1988 ---------------------------------------------------------------------
1989 -- Procedure Pay_Dist_Proc
1990 -- This procedure prorates the payment amounts for each distribution
1991 -- and inserts the calculated values into payment hist dists table
1992 -- Also calculates discounts and ERV
1993 ---------------------------------------------------------------------
1994
1995 PROCEDURE Pay_Dist_Proc
1996 (p_xla_event_rec IN ap_accounting_pay_pkg.r_xla_event_info
2000 ,p_inv_dist_rec IN ap_accounting_pay_pkg.r_inv_dist_info
1997 ,p_inv_pay_rec IN r_inv_pay_info
1998 ,p_pay_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
1999 ,p_inv_rec IN ap_accounting_pay_pkg.r_invoices_info
2001 ,p_calc_mode IN VARCHAR2
2002 ,p_final_payment IN BOOLEAN
2003 ,p_calling_sequence IN VARCHAR2
2004 ) IS
2005
2006
2007 l_curr_calling_sequence VARCHAR2(2000);
2008 l_dist_amt_pay_curr NUMBER;
2009 l_dist_amt_bank_curr NUMBER;
2010 l_pay_amount_inv_curr NUMBER;
2011 l_pay_amount_bank_curr NUMBER;
2012 l_prorated_amount NUMBER;
2013 l_prorated_base_amount NUMBER;
2014 l_inv_dist_amount NUMBER;
2015 l_bank_curr_amount NUMBER;
2016
2017 l_disc_pay_amount NUMBER := 0;
2018 l_disc_dist_amount NUMBER := 0;
2019 l_disc_bank_amount NUMBER := 0;
2020
2021 l_total_paid_amt NUMBER;
2022 l_total_prepaid_amt NUMBER;
2023 l_tot_paid_amt_inv_curr NUMBER;
2024 l_tot_paid_amt_bank_curr NUMBER;
2025 l_tot_prepaid_amt_pay_curr NUMBER;
2026 l_tot_prepaid_amt_bank_curr NUMBER;
2027 l_proration_divisor NUMBER;
2028 l_total_dist_amount NUMBER;
2029
2030 l_qty_variance NUMBER;
2031 l_base_qty_variance NUMBER;
2032 l_amt_variance NUMBER;
2033 l_base_amt_variance NUMBER;
2034
2035 l_pd_rec AP_PAYMENT_HIST_DISTS%ROWTYPE;
2036
2037 -- Logging Infra:
2038 l_procedure_name CONSTANT VARCHAR2(30) := 'Pay_Dist_Proc';
2039 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2040
2041 BEGIN
2042
2043 l_curr_calling_sequence := 'AP_ACCTG_PAY_DIST_PKG.Pay_Dist_Proc<- ' ||
2044 p_calling_sequence;
2045
2046 -- Logging Infra: Procedure level
2047 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2048 l_log_msg := 'Begin of procedure '|| l_procedure_name;
2049 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2050 END IF;
2051
2052
2053 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2054 l_log_msg := 'Parameters: Invoice_ID = '|| p_inv_rec.invoice_id
2055 ||'Invoice_Dist_ID = '|| p_inv_dist_rec.invoice_distribution_id;
2056 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2057 END IF;
2058
2059 /* Performance Fix 7308385 starts
2060 -- Selecting the distribution amount including the AWT distributions for
2061 -- prorating the AWT distributions.
2062 SELECT SUM(NVL(AID.Amount,0))
2063 INTO l_total_dist_amount
2064 FROM AP_Invoice_Distributions_All AID
2065 WHERE AID.Invoice_ID = p_inv_pay_rec.invoice_id
2066 AND AID.Line_Type_Lookup_Code <> 'PREPAY'
2067 AND AID.Prepay_Distribution_ID IS NULL
2068 AND AID.Prepay_Tax_Parent_ID IS NULL -- For tax dists created in R11.5
2069 AND (AID.AWT_Invoice_Payment_ID IS NULL
2070 OR AID.AWT_Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id)
2071 -- bug fix: 6725866
2072 AND NOT EXISTS (SELECT 1
2073 FROM xla_events
2074 WHERE event_id = AID.accounting_event_id
2075 AND application_id = 200 -- bug7281412
2076 AND event_type_code IN ('INVOICE CANCELLED',
2077 'PREPAYMENT CANCELLED',
2078 'CREDIT MEMO CANCELLED',
2079 'DEBIT MEMO CANCELLED'));
2080 */
2081 l_total_dist_amount := G_Total_Dist_Amount;
2082 -- Performance Fix 7308385 ends
2083
2084 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2085 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,
2086 'l_total_dist_amount: '||l_total_dist_amount||
2087 ' p_inv_pay_rec.invoice_payment_id: '||p_inv_pay_rec.invoice_payment_id);
2088 END IF;
2089
2090 IF p_inv_dist_rec.Line_Type_Lookup_Code = 'AWT' THEN
2091 l_proration_divisor := l_total_dist_amount;
2092 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2093 l_log_msg := 'p_inv_dist_rec.Line_Type_Lookup_Code = AWT' ||
2094 'including AWT and l_proration_divisor =' || NVL(l_proration_divisor,0);
2095 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2096 END IF;
2097
2098 ELSE
2099 --bug6147546
2100 --l_proration_divisor := p_inv_rec.invoice_amount;
2101 -- Bug 6712649. Added Credit and debit memo cancelled. Also added the
2102 -- line_type not in 'PREPAY' and 'AWT'
2103 /* Performance Fix 7308385 starts
2104 SELECT SUM(AID.amount)
2105 INTO l_proration_divisor
2106 FROM ap_invoice_distributions_all AID
2107 WHERE AID.invoice_id = p_inv_rec.invoice_id
2108 AND AID.Line_Type_Lookup_Code NOT IN ('PREPAY', 'AWT')
2109 AND AID.Prepay_Distribution_ID IS NULL
2110 AND NOT EXISTS (SELECT 1
2111 FROM xla_events
2112 WHERE event_id = AID.accounting_event_id
2113 AND application_id = 200 --bug 7281412
2114 AND event_type_code IN ('INVOICE CANCELLED',
2115 'PREPAYMENT CANCELLED',
2119 l_proration_divisor := G_Proration_Divisor;
2116 'CREDIT MEMO CANCELLED',
2117 'DEBIT MEMO CANCELLED'));
2118 */
2120 -- Performance Fix 7308385 ends
2121
2122
2123 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2124 l_log_msg := 'p_inv_dist_rec.Line_Type_Lookup_Code <> AWT' ||
2125 'exclude AWT and l_proration_divisor =' || NVL(l_proration_divisor,0);
2126 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2127 END IF;
2128 END IF;
2129
2130
2131 -- Converting the distribution amount into payment currency for
2132 -- cross currency invoices.
2133 IF (p_inv_rec.invoice_currency_code <> p_inv_rec.payment_currency_code) THEN
2134
2135 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2136 l_log_msg := 'this is cross currency';
2137 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2138 END IF;
2139 l_dist_amt_pay_curr := GL_Currency_API.Convert_Amount(
2140 p_inv_rec.invoice_currency_code,
2141 p_inv_rec.payment_currency_code,
2142 p_inv_rec.payment_cross_rate_date,
2143 'EMU FIXED',
2144 p_inv_dist_rec.amount);
2145
2146 l_pay_amount_inv_curr := GL_Currency_API.Convert_Amount(
2147 p_inv_rec.payment_currency_code,
2148 p_inv_rec.invoice_currency_code,
2149 p_inv_rec.payment_cross_rate_date,
2150 'EMU FIXED',
2151 p_inv_pay_rec.amount);
2152
2153 ELSE
2154
2155 l_dist_amt_pay_curr := p_inv_dist_rec.amount;
2156 l_pay_amount_inv_curr := p_inv_pay_rec.amount;
2157
2158 END IF;
2159
2160 IF (p_xla_event_rec.event_type_code IN ('PAYMENT CLEARED',
2161 'PAYMENT CLEARING ADJUSTED')) THEN
2162
2163 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2164 l_log_msg := 'Calculating payment and dist amt in bank currency for event type:'
2165 ||p_xla_event_rec.event_type_code;
2166 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2167 END IF;
2168
2169
2170 /* Converting the payment and distribution amount into bank currency */
2171 IF ( p_pay_hist_rec.pmt_currency_code <> p_pay_hist_rec.bank_currency_code
2172 AND p_pay_hist_rec.bank_currency_code is not NULL ) THEN
2173
2174 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2175 l_log_msg := '1. payment currency code <> bank_currency_code and' ||
2176 'p_pay_hist_rec.pmt_currency_code = ' || p_pay_hist_rec.pmt_currency_code ||
2177 'p_pay_hist_rec.bank_currency_code = ' || p_pay_hist_rec.bank_currency_code ||
2178 'p_inv_pay_rec.amount = ' || p_inv_pay_rec.amount ||
2179 'l_dist_amt_pay_curr =' || l_dist_amt_pay_curr ||
2180 'p_pay_hist_rec.pmt_to_base_xrate = ' || p_pay_hist_rec.pmt_to_base_xrate ||
2181 'p_pay_hist_rec.bank_to_base_xrate' || p_pay_hist_rec.bank_to_base_xrate;
2182 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2183 END IF;
2184
2185
2186 l_pay_amount_bank_curr := AP_Utilities_Pkg.AP_Round_Currency(
2187 p_inv_pay_rec.amount * p_pay_hist_rec.pmt_to_base_xrate
2188 /p_pay_hist_rec.bank_to_base_xrate,
2189 p_pay_hist_rec.bank_currency_code);
2190
2191 l_dist_amt_bank_curr := AP_Utilities_Pkg.AP_Round_Currency(
2192 l_dist_amt_pay_curr * p_pay_hist_rec.pmt_to_base_xrate
2193 /p_pay_hist_rec.bank_to_base_xrate,
2194 p_pay_hist_rec.bank_currency_code);
2195
2196 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2197 l_log_msg := 'after calculation and' ||
2198 'l_dist_amt_bank_curr = ' || l_dist_amt_bank_curr||
2199 'l_pay_amount_bank_curr = ' || l_pay_amount_bank_curr;
2200 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2201 END IF;
2202
2203 -- Added for bug fix 5694577
2204 ELSE -- p_pay_hist_rec.pmt_currency_code = p_pay_hist_rec.bank_currency_code
2205
2206 l_pay_amount_bank_curr := p_inv_pay_rec.amount;
2207 l_dist_amt_bank_curr := l_dist_amt_pay_curr;
2208
2209 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2210 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,
2211 'l_pay_amount_bank_curr = ' || l_pay_amount_bank_curr||
2212 'l_dist_amt_bank_curr = ' || l_dist_amt_bank_curr);
2213 END IF;
2214
2215 END IF; -- end of checking p_pay_hist_rec.pmt_currency_code <> p_pay_hist_rec.bank_currency_code
2216
2217 ELSE
2218
2219 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2220 l_log_msg := 'assign some bank related variables for other event type=>'
2221 ||p_xla_event_rec.event_type_code;
2225 l_pay_amount_bank_curr := p_inv_pay_rec.amount;
2222 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2223 END IF;
2224
2226 l_dist_amt_bank_curr := l_dist_amt_pay_curr;
2227
2228 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2229 l_log_msg := 'payment currency code = bank currency code for event type '||
2230 'l_dist_amt_bank_curr = ' || l_dist_amt_bank_curr||
2231 'l_pay_amount_bank_curr = ' || l_pay_amount_bank_curr;
2232 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2233 END IF;
2234
2235 END IF; -- end of check event type
2236
2237
2238 g_total_dist_amt := g_total_dist_amt + p_inv_dist_rec.amount;
2239
2240 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2241 l_log_msg := 'this run p_inv_dist_rec.amount = ' ||
2242 p_inv_dist_rec.amount ||
2243 'Up to now ->g_total_dist_amt = '||g_total_dist_amt;
2244 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2245 END IF;
2246
2247 /* We should distribute the discount amount first so that during the final payment
2248 this discount amount is also considered for adjusting the distribution */
2249 IF p_inv_pay_rec.Discount_Taken <> 0 and
2250 p_inv_dist_rec.Line_Type_Lookup_Code <> 'AWT' THEN
2251
2252 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2253 l_log_msg := 'Calling procedure Pay_Dist_Discount for dist: '
2254 || p_inv_dist_rec.invoice_distribution_id;
2255 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2256 END IF;
2257
2258 Pay_Dist_Discount
2259 (p_xla_event_rec,
2260 p_inv_pay_rec,
2261 p_pay_hist_rec,
2262 p_inv_rec,
2263 p_inv_dist_rec,
2264 p_calc_mode,
2265 l_disc_pay_amount,
2266 l_disc_dist_amount,
2267 l_disc_bank_amount,
2268 l_curr_calling_sequence);
2269
2270 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2271 l_log_msg := 'Procedure Pay_Dist_Discount executed and ' ||
2272 'p_disc_pay_amount =' || l_disc_pay_amount ||
2273 'p_disc_dist_amount ='|| l_disc_dist_amount ||
2274 'p_disc_bank_amount ='|| l_disc_bank_amount;
2275
2276 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2277 END IF;
2278 END IF;
2279
2280 IF (p_calc_mode IN ('P', 'M')) THEN
2281
2282 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2283 l_log_msg := 'calculation mode p_calc_mode =' || p_calc_mode ;
2284 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2285 END IF;
2286
2287 -- If this payment is a final payment for the invoice then we should make sure
2288 -- that the sum of payment distributions amount should be equal to the distribution
2289 -- total. This way the liability is fully relieved.
2290
2291 IF p_final_payment = TRUE THEN
2292
2293 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2294 l_log_msg := 'This is a final payment and now calling AP_Accounting_Pay_Pkg.Get_Pay_Sum';
2295 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2296 END IF;
2297
2298 AP_Accounting_Pay_Pkg.Get_Pay_Sum
2299 (p_inv_dist_rec.invoice_distribution_id,
2300 p_xla_event_rec.event_type_code,
2301 l_total_paid_amt,
2302 l_tot_paid_amt_inv_curr,
2303 l_tot_paid_amt_bank_curr,
2304 l_curr_calling_sequence);
2305
2306 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2307 l_log_msg := 'After Calling function AP_Accounting_Pay_Pkg.Get_Pay_Sum' ||
2308 'l_total_paid_amt==' || nvl(l_total_paid_amt,0) ||
2309 'l_tot_paid_amt_inv_curr=' || nvl(l_tot_paid_amt_inv_curr,0) ||
2310 'l_tot_paid_amt_bank_curr =' || nvl(l_tot_paid_amt_bank_curr,0);
2311 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2312 END IF;
2313
2314
2315 l_total_prepaid_amt := AP_Accounting_Pay_Pkg.Get_Prepay_Sum
2316 (p_inv_dist_rec.invoice_distribution_id,
2317 l_curr_calling_sequence);
2318
2319 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2320 l_log_msg := 'get pay sum and its amount = ' ||
2321 l_total_paid_amt ||
2322 'get prepay sum and its amount = '||
2323 l_total_prepaid_amt;
2324 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2325 END IF;
2326
2327
2328 -- Converting the distribution and prepaid amount into payment currency for
2329 -- cross currency invoices.
2330 IF (p_inv_rec.invoice_currency_code <> p_inv_rec.payment_currency_code) THEN
2331
2332 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2333 l_log_msg := 'Converting prepaid amount into payment currency';
2337 l_tot_prepaid_amt_pay_curr := GL_Currency_API.Convert_Amount(
2334 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2335 END IF;
2336
2338 p_inv_rec.invoice_currency_code,
2339 p_inv_rec.payment_currency_code,
2340 p_inv_rec.payment_cross_rate_date,
2341 'EMU FIXED',
2342 l_total_prepaid_amt);
2343
2344
2345 ELSE
2346 l_tot_prepaid_amt_pay_curr := l_total_prepaid_amt;
2347 END IF;
2348
2349 IF (p_xla_event_rec.event_type_code IN ('PAYMENT CLEARED',
2350 'PAYMENT CLEARING ADJUSTED')) THEN
2351
2352 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2353 l_log_msg := 'Converting prepaid amount into bank currency';
2354 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2355 END IF;
2356
2357 IF ( p_pay_hist_rec.pmt_currency_code <> p_pay_hist_rec.bank_currency_code
2358 AND p_pay_hist_rec.bank_currency_code is not NULL ) THEN
2359
2360 l_tot_prepaid_amt_bank_curr :=
2361 AP_Utilities_Pkg.AP_Round_Currency(
2362 l_tot_prepaid_amt_pay_curr * p_pay_hist_rec.pmt_to_base_xrate
2363 /p_pay_hist_rec.bank_to_base_xrate,
2364 p_pay_hist_rec.bank_currency_code);
2365
2366 ELSE
2367
2368 l_tot_prepaid_amt_bank_curr := l_tot_prepaid_amt_pay_curr;
2369 END IF;
2370 END IF;
2371
2372
2373 /* If this payment is a final payment then we should make sure that the
2374 distributed payment amount equals the distribution amount. This way the
2375 the liability for the distribution is relieved completely */
2376
2377 -- use NVL to make sure the following amt won't be NULL
2378 l_prorated_amount := NVL(l_dist_amt_pay_curr, 0) - NVL(l_total_paid_amt, 0) - NVL(l_disc_pay_amount, 0)
2379 + NVL(l_tot_prepaid_amt_pay_curr, 0);
2380 l_inv_dist_amount := NVL(p_inv_dist_rec.amount, 0) - NVL(l_tot_paid_amt_inv_curr, 0)
2381 - NVL(l_disc_dist_amount, 0) + NVL(l_total_prepaid_amt, 0);
2382 l_bank_curr_amount := NVL(l_dist_amt_bank_curr, 0) - NVL(l_tot_paid_amt_bank_curr, 0)
2383 - NVL(l_disc_bank_amount, 0) + NVL(l_tot_prepaid_amt_bank_curr, 0);
2384
2385 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2386 l_log_msg := 'Value for l_prorated_amount = ' || l_prorated_amount ||
2387 'Value for l_inv_dist_amount = ' || l_inv_dist_amount ||
2388 'Value for l_bank_curr_amount =' || l_bank_curr_amount;
2389
2390 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2391 END IF;
2392
2393 ELSE
2394
2395 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2396 l_log_msg := 'this is NOT a final payment';
2397 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2398 END IF;
2399
2400 IF g_total_dist_amt = l_total_dist_amount THEN -- last dist rec
2401
2402 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2403 l_log_msg := 'g_total_dist_amt equal l_total_dist_amount =' ||
2404 l_total_dist_amount;
2405 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2406 END IF;
2407
2408 -- To avoid rounding, massage the last (biggest) line
2409 l_prorated_amount := p_inv_pay_rec.amount - g_total_prorated_amt;
2410 l_inv_dist_amount := l_pay_amount_inv_curr - g_total_inv_dist_amt;
2411 -- bug 5638490
2412 l_bank_curr_amount := l_pay_amount_bank_curr - g_total_bank_curr_amt;
2413
2414 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2415 l_log_msg := 'Value for l_prorated_amount = ' || l_prorated_amount ||
2416 'Value for l_inv_dist_amoun = ' || l_inv_dist_amount ||
2417 'l_bank_curr_amount = ' || l_bank_curr_amount;
2418 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2419 END IF;
2420
2421
2422 ELSE
2423
2424 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2425 l_log_msg := 'This is not the last invoice distribution for proration';
2426 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2427 END IF;
2428
2429 IF NVL(l_proration_divisor,0) = 0 THEN
2430 l_prorated_amount := 0;
2431 l_inv_dist_amount := 0;
2432 l_bank_curr_amount := 0;
2433
2434 ELSE
2435
2436 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2437 l_log_msg := 'l_proration_divisor is not 0 it is =>'
2438 || l_proration_divisor;
2439 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2440 END IF;
2441
2445 IF p_inv_dist_rec.awt_invoice_payment_id IS NOT NULL THEN
2442 -- We do not need to prorate the AWT amounts for the AWT distributions
2443 -- that are created during payment time
2444
2446
2447 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2448 l_log_msg := 'AWT at payment time and should not prorate to awt pmt distribution';
2449 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2450 END IF;
2451
2452 l_prorated_amount := GL_Currency_API.Convert_Amount(
2453 p_inv_rec.invoice_currency_code,
2454 p_inv_rec.payment_currency_code,
2455 p_inv_rec.payment_cross_rate_date,
2456 'EMU FIXED',
2457 p_inv_dist_rec.amount);
2458
2459 l_inv_dist_amount := p_inv_dist_rec.amount;
2460
2461 IF ( p_pay_hist_rec.pmt_currency_code <> p_pay_hist_rec.bank_currency_code
2462 and p_pay_hist_rec.bank_currency_code is not null ) THEN
2463
2464 l_bank_curr_amount := AP_Utilities_Pkg.AP_Round_Currency(
2465 l_prorated_amount * p_pay_hist_rec.pmt_to_base_xrate
2466 /p_pay_hist_rec.bank_to_base_xrate,
2467 p_pay_hist_rec.bank_currency_code);
2468
2469 ELSE
2470
2471 l_bank_curr_amount := l_prorated_amount;
2472
2473 END IF;
2474
2475 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2476 l_log_msg := 'AWT at payment time and ' ||
2477 ' l_prorated_amount = ' || l_prorated_amount ||
2478 'l_bank_curr_amount = ' || l_bank_curr_amount;
2479 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2480 END IF;
2481
2482 ELSE
2483
2484 /* We need to calculate the payment amount and invoice dist amount
2485 seperately to avoid rounding when calculating the base amounts */
2486
2487 l_prorated_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
2488 (p_inv_pay_rec.amount * p_inv_dist_rec.amount
2489 / l_proration_divisor,
2490 p_pay_hist_rec.pmt_currency_code);
2491
2492 l_inv_dist_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
2493 (l_pay_amount_inv_curr * p_inv_dist_rec.amount
2494 / l_proration_divisor,
2495 p_inv_rec.invoice_currency_code);
2496
2497 -- bug 5638490
2498 IF ( p_pay_hist_rec.bank_currency_code is not null ) THEN
2499 l_bank_curr_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
2500 (l_pay_amount_bank_curr * p_inv_dist_rec.amount
2501 / l_proration_divisor,
2502 p_pay_hist_rec.bank_currency_code);
2503 ELSE
2504 l_bank_curr_amount := l_prorated_amount;
2505 END IF;
2506
2507 END IF; -- If AWT created at payment time
2508
2509 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2510 l_log_msg := 'Value for l_prorated_amount = ' || l_prorated_amount;
2511 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2512 END IF;
2513
2514
2515 END IF;
2516
2517 END IF;
2518 END IF; -- If final payment
2519
2520
2521 -- We should not include the AWT prorated amount int the total prorated amt used
2522 -- for the technical proration rounding
2523 IF p_inv_dist_rec.line_type_lookup_code <> 'AWT' THEN
2524 g_total_prorated_amt := g_total_prorated_amt + l_prorated_amount;
2525 g_total_inv_dist_amt := g_total_inv_dist_amt + l_inv_dist_amount;
2526 g_total_bank_curr_amt := g_total_bank_curr_amt + l_bank_curr_amount;
2527 END IF;
2528
2529 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2530 l_log_msg := 'After final payment check/process and' ||
2531 'g_total_prorated_amt = ' || nvl(g_total_prorated_amt,0) ||
2532 'g_total_inv_dist_amt = ' || nvl(g_total_inv_dist_amt,0) ||
2533 'g_total_bank_curr_amt = ' ||nvl(g_total_bank_curr_amt,0);
2534 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2535 END IF;
2536
2537
2538 /* If this is a cascade event then we will create new payment distributions
2539 for the existing invoice distributions that have already been distributed to
2540 this payment in order to adjust the payments as a result of adjusting the
2541 invoice */
2542 ELSE
2543
2544 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2545 l_log_msg := 'calculation mode p_calc_mode (cascade?) =' || p_calc_mode ;
2546 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2547 END IF;
2548
2552 l_bank_curr_amount := 0;
2549 IF NVL(l_proration_divisor, 0) = 0 THEN
2550 l_prorated_amount := 0;
2551 l_inv_dist_amount := 0;
2553
2554 ELSE
2555
2556 -- We do not need to prorate the AWT amounts for the AWT distributions
2557 -- that are created during payment time
2558 IF p_inv_dist_rec.awt_invoice_payment_id IS NOT NULL THEN
2559
2560 l_prorated_amount := GL_Currency_API.Convert_Amount(
2561 p_inv_rec.invoice_currency_code,
2562 p_inv_rec.payment_currency_code,
2563 p_inv_rec.payment_cross_rate_date,
2564 'EMU FIXED',
2565 p_inv_dist_rec.amount) -
2566 AP_Accounting_Pay_Pkg.get_casc_pay_sum
2567 (p_inv_dist_rec.invoice_distribution_id,
2568 p_pay_hist_rec.related_event_id,
2569 p_inv_pay_rec.invoice_payment_id,
2570 l_curr_calling_sequence);
2571
2572 l_inv_dist_amount := p_inv_dist_rec.amount -
2573 AP_Accounting_Pay_Pkg.get_casc_inv_dist_sum
2574 (p_inv_dist_rec.invoice_distribution_id,
2575 p_pay_hist_rec.related_event_id,
2576 p_inv_pay_rec.invoice_payment_id,
2577 l_curr_calling_sequence);
2578
2579 IF ( p_pay_hist_rec.pmt_currency_code <> p_pay_hist_rec.bank_currency_code and
2580 p_pay_hist_rec.bank_currency_code is not NULL ) THEN
2581
2582 l_bank_curr_amount := AP_Utilities_Pkg.AP_Round_Currency(
2583 l_prorated_amount * p_pay_hist_rec.pmt_to_base_xrate
2584 /p_pay_hist_rec.bank_to_base_xrate,
2585 p_pay_hist_rec.bank_currency_code) -
2586 AP_Accounting_Pay_Pkg.get_casc_bank_curr_sum(
2587 p_inv_dist_rec.invoice_distribution_id,
2588 p_pay_hist_rec.related_event_id,
2589 p_inv_pay_rec.invoice_payment_id,
2590 l_curr_calling_sequence);
2591
2592 ELSE
2593
2594 l_bank_curr_amount := l_prorated_amount -
2595 AP_Accounting_Pay_Pkg.get_casc_bank_curr_sum(
2596 p_inv_dist_rec.invoice_distribution_id,
2597 p_pay_hist_rec.related_event_id,
2598 p_inv_pay_rec.invoice_payment_id,
2599 l_curr_calling_sequence);
2600
2601 END IF;
2602
2603 ELSE
2604
2605 -- In case of cascade events we will recalculate the prorated amount and subtract
2606 -- this amount from the already calculated amount previously so that this would
2607 -- give us the amount that needs to be adjusted
2608 l_prorated_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
2609 (((p_inv_dist_rec.amount * p_inv_pay_rec.amount)
2610 / l_proration_divisor)
2611 - AP_Accounting_Pay_Pkg.get_casc_pay_sum
2612 (p_inv_dist_rec.invoice_distribution_id,
2613 p_pay_hist_rec.related_event_id,
2614 p_inv_pay_rec.invoice_payment_id,
2615 l_curr_calling_sequence),
2616 p_pay_hist_rec.pmt_currency_code);
2617
2618 l_inv_dist_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
2619 (((p_inv_dist_rec.amount * l_pay_amount_inv_curr)
2620 / l_proration_divisor)
2621 - AP_Accounting_Pay_Pkg.get_casc_inv_dist_sum
2622 (p_inv_dist_rec.invoice_distribution_id,
2623 p_pay_hist_rec.related_event_id,
2624 p_inv_pay_rec.invoice_payment_id,
2625 l_curr_calling_sequence),
2626 p_inv_rec.invoice_currency_code);
2627
2628 IF ( p_pay_hist_rec.bank_currency_code is not NULL ) THEN
2629
2630 l_bank_curr_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
2631 (((p_inv_dist_rec.amount * l_pay_amount_bank_curr)
2632 / l_proration_divisor)
2633 - AP_Accounting_Pay_Pkg.get_casc_bank_curr_sum
2634 (p_inv_dist_rec.invoice_distribution_id,
2635 p_pay_hist_rec.related_event_id,
2636 p_inv_pay_rec.invoice_payment_id,
2637 l_curr_calling_sequence),
2638 p_pay_hist_rec.bank_currency_code);
2642
2639 END IF;
2640
2641 END IF; -- If AWT created at payment time
2643 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2644 l_log_msg := 'Value for l_prorated_amount = ' || l_prorated_amount;
2645 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2646 END IF;
2647
2648 END IF;
2649 END IF; -- If calc_mode in ('P','M')
2650
2651 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2652 l_log_msg := 'Now calling AP_Accounting_Pay_Pkg.Get_Base_Amount before insert';
2653 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2654 END IF;
2655
2656 l_prorated_base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
2657 (l_prorated_amount,
2658 p_pay_hist_rec.pmt_currency_code,
2659 ap_accounting_pay_pkg.g_base_currency_code,
2660 p_pay_hist_rec.pmt_to_base_xrate_type,
2661 p_pay_hist_rec.pmt_to_base_xrate_date,
2662 p_pay_hist_rec.pmt_to_base_xrate,
2663 l_curr_calling_sequence);
2664
2665 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2666 l_log_msg := 'after call AP_Accounting_Pay_Pkg.Get_Base_Amoun and ' ||
2667 'l_prorated_base_amount=' || nvl(l_prorated_base_amount,0) ||
2668 'l_prorated_amount= ' || nvl(l_prorated_amount,0);
2669 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2670 END IF;
2671
2672 -- populate the payment distribution record
2673
2674 l_pd_rec.accounting_event_id := p_xla_event_rec.event_id;
2675 l_pd_rec.invoice_distribution_id := p_inv_dist_rec.invoice_distribution_id;
2676
2677 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2678 l_log_msg := 'Start to populate the l_pd_rec for event id' ||
2679 l_pd_rec.accounting_event_id;
2680 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2681 END IF;
2682
2683
2684 IF p_inv_dist_rec.line_type_lookup_code = 'AWT' THEN
2685 l_pd_rec.pay_dist_lookup_code := 'AWT';
2686 l_pd_rec.awt_related_id := p_inv_dist_rec.awt_related_id;
2687
2688 l_prorated_amount := -1 * l_prorated_amount;
2689 l_prorated_base_amount := -1 * l_prorated_base_amount;
2690 l_inv_dist_amount := -1 * l_inv_dist_amount;
2691 l_bank_curr_amount := -1 * l_bank_curr_amount;
2692 ELSE
2693 l_pd_rec.pay_dist_lookup_code := 'CASH';
2694 END IF;
2695
2696 l_pd_rec.amount := l_prorated_amount;
2697 l_pd_rec.payment_history_id := p_pay_hist_rec.payment_history_id;
2698 l_pd_rec.invoice_payment_id := p_inv_pay_rec.invoice_payment_id;
2699
2700 l_pd_rec.bank_curr_amount := l_bank_curr_amount;
2701
2702 IF p_xla_event_rec.event_type_code IN
2703 ('PAYMENT CLEARED', 'PAYMENT CLEARING ADJUSTED') THEN
2704
2705 l_pd_rec.cleared_base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
2706 (l_bank_curr_amount,
2707 p_pay_hist_rec.bank_currency_code,
2708 ap_accounting_pay_pkg.g_base_currency_code,
2709 p_pay_hist_rec.bank_to_base_xrate_type,
2710 p_pay_hist_rec.bank_to_base_xrate_date,
2711 p_pay_hist_rec.bank_to_base_xrate,
2712 l_curr_calling_sequence);
2713
2714 l_pd_rec.paid_base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
2715 (l_prorated_amount,
2716 p_pay_hist_rec.pmt_currency_code,
2717 ap_accounting_pay_pkg.g_base_currency_code,
2718 ap_accounting_pay_pkg.g_pmt_to_base_xrate_type,
2719 ap_accounting_pay_pkg.g_pmt_to_base_xrate_date,
2720 ap_accounting_pay_pkg.g_pmt_to_base_xrate,
2721 l_curr_calling_sequence);
2722
2723 IF ap_accounting_pay_pkg.g_mat_to_base_xrate IS NOT NULL THEN
2724
2725 l_pd_rec.matured_base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
2726 (l_prorated_amount,
2727 p_pay_hist_rec.pmt_currency_code,
2728 ap_accounting_pay_pkg.g_base_currency_code,
2729 ap_accounting_pay_pkg.g_mat_to_base_xrate_type,
2730 ap_accounting_pay_pkg.g_mat_to_base_xrate_date,
2731 ap_accounting_pay_pkg.g_mat_to_base_xrate,
2732 l_curr_calling_sequence);
2733 END IF ;
2734
2735 ELSIF p_xla_event_rec.event_type_code IN
2736 ('PAYMENT MATURED', 'PAYMENT MATURITY ADJUSTED') THEN
2737 l_pd_rec.matured_base_amount := l_prorated_base_amount;
2738
2739 l_pd_rec.paid_base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
2740 (l_prorated_amount,
2741 p_pay_hist_rec.pmt_currency_code,
2742 ap_accounting_pay_pkg.g_base_currency_code,
2746 l_curr_calling_sequence);
2743 ap_accounting_pay_pkg.g_pmt_to_base_xrate_type,
2744 ap_accounting_pay_pkg.g_pmt_to_base_xrate_date,
2745 ap_accounting_pay_pkg.g_pmt_to_base_xrate,
2747
2748 ELSE
2749 l_pd_rec.paid_base_amount := l_prorated_base_amount;
2750 END IF;
2751
2752 l_pd_rec.invoice_dist_amount := l_inv_dist_amount;
2753
2754
2755 /* If the exchange rates between the invoice and payment have not changed then
2756 the invoice and payment base amounts should be the same. Assigning the
2757 payment base amount to the invoice base amount instead of recalculating the
2758 invoice base amount */
2759
2760 IF (p_pay_hist_rec.pmt_to_base_xrate =
2761 p_inv_rec.exchange_rate / p_inv_rec.payment_cross_rate) THEN
2762
2763 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2764 l_log_msg := 'pmt to base rate = inv to base rate';
2765 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2766 END IF;
2767
2768 l_pd_rec.invoice_dist_base_amount := l_prorated_base_amount;
2769
2770 ELSE
2771
2772 l_pd_rec.invoice_dist_base_amount :=
2773 AP_Accounting_Pay_Pkg.Get_Base_Amount
2774 (l_inv_dist_amount,
2775 p_inv_rec.invoice_currency_code,
2776 ap_accounting_pay_pkg.g_base_currency_code,
2777 p_inv_rec.exchange_rate_type,
2778 p_inv_rec.exchange_date,
2779 p_inv_rec.exchange_rate,
2780 l_curr_calling_sequence);
2781
2782 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2783 l_log_msg := 'pmt to base rate <> inv to base rate and ' ||
2784 'l_pd_rec.invoice_dist_base_amount =' ||
2785 nvl(l_pd_rec.invoice_dist_base_amount,0) ||
2786 'l_prorated_base_amount =' || nvl(l_prorated_base_amount,0);
2787 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2788 END IF;
2789
2790 END IF;
2791
2792
2793 IF p_inv_dist_rec.quantity_variance IS NOT NULL THEN
2794
2795 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2796 l_log_msg := 'Invoice has quantity variance';
2797 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2798 END IF;
2799
2800 IF p_inv_dist_rec.amount = 0 THEN
2801 l_qty_variance := 0;
2802 ELSE
2803 l_qty_variance := AP_Utilities_PKG.AP_Round_Currency(
2804 ((p_inv_dist_rec.quantity_variance * l_inv_dist_amount) /
2805 p_inv_dist_rec.amount),
2806 p_inv_rec.invoice_currency_code);
2807 END IF;
2808
2809 IF p_inv_dist_rec.base_amount = 0 THEN
2810 l_base_qty_variance := 0;
2811 ELSE
2812 l_base_qty_variance := AP_Utilities_PKG.AP_Round_Currency(
2813 ((p_inv_dist_rec.base_quantity_variance
2814 * l_pd_rec.invoice_dist_base_amount)
2815 / p_inv_dist_rec.base_amount),
2816 ap_accounting_pay_pkg.g_base_currency_code);
2817 END IF;
2818 END IF;
2819
2820 IF p_inv_dist_rec.amount_variance IS NOT NULL THEN
2821
2822 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2823 l_log_msg := 'Invoice has amount variance';
2824 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2825 END IF;
2826
2827 IF p_inv_dist_rec.amount = 0 THEN
2828 l_amt_variance := 0;
2829 ELSE
2830 l_amt_variance := AP_Utilities_PKG.AP_Round_Currency(
2831 ((p_inv_dist_rec.amount_variance * l_inv_dist_amount) /
2832 p_inv_dist_rec.amount),
2833 p_inv_rec.invoice_currency_code);
2834 END IF;
2835
2836 IF p_inv_dist_rec.base_amount = 0 THEN
2837 l_base_amt_variance := 0;
2838 ELSE
2839 l_base_amt_variance := AP_Utilities_PKG.AP_Round_Currency(
2840 ((p_inv_dist_rec.base_amount_variance
2841 * l_pd_rec.invoice_dist_base_amount)
2842 / p_inv_dist_rec.base_amount),
2843 ap_accounting_pay_pkg.g_base_currency_code);
2844 END IF;
2845 END IF;
2846
2847 l_pd_rec.quantity_variance := l_qty_variance;
2848 l_pd_rec.invoice_base_qty_variance := l_base_qty_variance;
2849 l_pd_rec.amount_variance := l_amt_variance;
2850 l_pd_rec.invoice_base_amt_variance := l_base_amt_variance;
2851
2852
2853 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2854 l_log_msg := 'Calling procedure Pay_Dist_Insert';
2855 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2856 END IF;
2857
2858
2859 -- Insert the payment hist distribution
2860
2861 Pay_Dist_Insert
2862 (l_pd_rec,
2863 l_curr_calling_sequence);
2864
2868 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2865 --bug7446229
2866 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2867 l_log_msg := 'Procedure Pay_Dist_Insert executed';
2869 END IF;
2870
2871
2872 IF ((p_xla_event_rec.event_type_code NOT IN
2873 ('PAYMENT MATURED', 'PAYMENT MATURITY ADJUSTED')) AND
2874 (p_inv_rec.payment_currency_code <> ap_accounting_pay_pkg.g_base_currency_code) AND
2875 p_inv_dist_rec.po_distribution_id IS NOT NULL AND
2876 p_inv_dist_rec.line_type_lookup_code <> 'AWT') THEN
2877
2878 -----------------------------------------------------------------------------
2879 -- Bug 5570002
2880 -- The ERV/TERV calculated did not consider the discount portion as part
2881 -- of this payment, this will make a difference between the original
2882 -- invoice distribution base amount and the paid invoice base amount
2883 -- due to this, unnecessary big amount Final payment is created
2884 -----------------------------------------------------------------------------
2885
2886 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2887 l_log_msg := 'Calling procedure Pay_Dist_ERV for dist:' ||
2888 p_inv_dist_rec.invoice_distribution_id ||
2889 'and pmt l_prorated_amount =' || l_prorated_amount ||
2890 'and discount amout prorated = ' || l_disc_pay_amount ;
2891 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2892 END IF;
2893
2894 Pay_Dist_ERV
2895 (p_xla_event_rec,
2896 p_inv_pay_rec,
2897 p_pay_hist_rec,
2898 p_inv_rec,
2899 p_inv_dist_rec,
2900 l_prorated_amount + l_disc_pay_amount,
2901 l_curr_calling_sequence);
2902
2903 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2904 l_log_msg := 'Procedure Pay_Dist_ERV executed';
2905 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2906 END IF;
2907
2908
2909 END IF;
2910
2911 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2912 l_log_msg := 'End of pay_dist_proc';
2913 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2914 END IF;
2915
2916 EXCEPTION
2917 WHEN OTHERS THEN
2918 IF (SQLCODE <> -20001) THEN
2919 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2920 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2921 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2922 END IF;
2923 APP_EXCEPTION.RAISE_EXCEPTION;
2924
2925 END Pay_Dist_Proc;
2926
2927
2928 ---------------------------------------------------------------------
2929 -- Procedure Pay_Dist_Discount
2930 -- This procedure prorates the discount amounts for each distribution
2931 -- and inserts the calculated values into payment hist dists table
2932 ---------------------------------------------------------------------
2933
2934 PROCEDURE Pay_Dist_Discount
2935 (p_xla_event_rec IN ap_accounting_pay_pkg.r_xla_event_info
2936 ,p_inv_pay_rec IN r_inv_pay_info
2937 ,p_pay_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
2938 ,p_inv_rec IN ap_accounting_pay_pkg.r_invoices_info
2939 ,p_inv_dist_rec IN ap_accounting_pay_pkg.r_inv_dist_info
2940 ,p_calc_mode IN VARCHAR2
2941 ,p_disc_pay_amount IN OUT NOCOPY NUMBER
2942 ,p_disc_dist_amount IN OUT NOCOPY NUMBER
2943 ,p_disc_bank_amount IN OUT NOCOPY NUMBER
2944 ,p_calling_sequence IN VARCHAR2
2945 ) IS
2946
2947 l_curr_calling_sequence VARCHAR2(2000);
2948 l_invoice_amount NUMBER;
2949 l_prorated_disc_amt NUMBER;
2950 l_prorated_base_amount NUMBER;
2951 l_exclude_tax_from_disc VARCHAR2(1);
2952 l_exclude_frt_from_disc VARCHAR2(1);
2953 l_inv_dist_amount NUMBER;
2954 l_bank_curr_amount NUMBER;
2955 l_disc_amt_inv_curr NUMBER;
2956 l_disc_amt_bank_curr NUMBER;
2957
2958 l_pd_rec AP_PAYMENT_HIST_DISTS%ROWTYPE;
2959
2960 -- Logging Infra:
2961 l_procedure_name CONSTANT VARCHAR2(30) := 'Pay_Dist_Discount';
2962 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2963 l_total_dist_amount NUMBER; -- Added for bug 7577312
2964
2965 BEGIN
2966
2967
2968 l_curr_calling_sequence := 'AP_ACCTG_PAY_DIST_PKG.Pay_Dist_Discount<- ' ||
2969 p_calling_sequence;
2970
2971
2972 -- Logging Infra: Procedure level
2973 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2974 l_log_msg := 'Begin of procedure '|| l_procedure_name;
2975 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2976 END IF;
2977
2978
2979 l_exclude_tax_from_disc := p_inv_rec.Disc_Is_Inv_Less_Tax_Flag;
2980 l_exclude_frt_from_disc := p_inv_rec.exclude_freight_from_discount;
2981
2982 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2983 l_log_msg := 'l_exclude_tax_from_disc =' || l_exclude_tax_from_disc ||
2984 'l_exclude_frt_from_disc = ' || l_exclude_frt_from_disc;
2988 -- Bug 7577312: Assigning the value of the l_total_dist_amount
2985 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2986 END IF;
2987
2989 l_total_dist_amount := G_Total_Dist_Amount;
2990
2991 /* Get the remaining invoice amount to be paid. Exclude the Tax and
2992 Freight amounts based on the system options to exclude tax and freight
2993 from discount */
2994 SELECT GL_Currency_API.Convert_Amount(
2995 p_inv_rec.invoice_currency_code,
2996 p_inv_rec.payment_currency_code,
2997 p_inv_rec.payment_cross_rate_date,
2998 'EMU FIXED',
2999 SUM(NVL(AID.Amount,0)))
3000 INTO l_invoice_amount
3001 FROM AP_Invoice_Distributions_All AID
3002 WHERE AID.Invoice_ID = p_inv_pay_rec.Invoice_ID
3003 AND AID.Line_Type_Lookup_Code NOT IN ('PREPAY', 'AWT')
3004 AND AID.Prepay_Tax_Parent_ID IS NULL -- For tax dists created in R11.5
3005 AND AID.Prepay_Distribution_ID IS NULL
3006 AND ( l_exclude_tax_from_disc = 'Y' and
3007 AID.Line_Type_Lookup_Code NOT IN ('REC_TAX', 'NONREC_TAX') or
3008 nvl(l_exclude_tax_from_disc, 'N') = 'N' )
3009 AND AID.Line_Type_Lookup_Code <>
3010 DECODE(l_exclude_frt_from_disc, 'Y', 'FREIGHT', 'DUMMY')
3011 GROUP BY AID.Invoice_ID;
3012
3013
3014 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3015 l_log_msg := 'Total invoice amount excluding tax or freight = '||
3016 l_invoice_amount;
3017 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3018 END IF;
3019
3020
3021 /* If the options exclude_tax_from_disc and exclude_frt_from_disc
3022 are set to 'Y' then we do not need to distribution the payment
3023 to the Tax and Freight type of invoice distribution */
3024 IF (l_exclude_tax_from_disc = 'Y'
3025 AND ( p_inv_dist_rec.line_type_lookup_code = 'REC_TAX' or
3026 p_inv_dist_rec.line_type_lookup_code = 'NONREC_TAX') ) THEN
3027 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3028 l_log_msg := 'l_exclude_tax_from_disc= Y so this procedure do nothing for tax line';
3029 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3030 END IF;
3031
3032 RETURN;
3033
3034 ELSIF (l_exclude_frt_from_disc = 'Y'
3035 AND p_inv_dist_rec.line_type_lookup_code = 'FREIGHT') THEN
3036
3037 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3038 l_log_msg := 'l_exclude_frt_from_disc= Y so this procedure do nothing';
3039 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3040 END IF;
3041
3042 RETURN;
3043
3044 ELSE
3045
3046 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3047 l_log_msg := 'consider discount prorate to frieight';
3048 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3049 END IF;
3050
3051 IF (p_inv_rec.invoice_currency_code <> p_inv_rec.payment_currency_code) THEN
3052
3053 l_disc_amt_inv_curr := GL_Currency_API.Convert_Amount(
3054 p_inv_rec.payment_currency_code,
3055 p_inv_rec.invoice_currency_code,
3056 p_inv_rec.payment_cross_rate_date,
3057 'EMU FIXED',
3058 p_inv_pay_rec.discount_taken);
3059
3060 ELSE
3061
3062 l_disc_amt_inv_curr := p_inv_pay_rec.discount_taken;
3063
3064 END IF;
3065
3066 IF (p_xla_event_rec.event_type_code IN ('PAYMENT CLEARED',
3067 'PAYMENT CLEARING ADJUSTED')) THEN
3068
3069 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3070 l_log_msg := 'assigning the disc_amt_bank_curr for event type' ||
3071 p_xla_event_rec.event_type_code;
3072 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3073 END IF;
3074
3075 IF p_pay_hist_rec.pmt_currency_code <> p_pay_hist_rec.bank_currency_code THEN
3076
3077 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3078 l_log_msg := 'Converting discount amt into bank currency';
3079 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3080 END IF;
3081
3082
3083 l_disc_amt_bank_curr :=
3084 AP_Utilities_Pkg.AP_Round_Currency(
3085 p_inv_pay_rec.discount_taken * p_pay_hist_rec.pmt_to_base_xrate
3086 /p_pay_hist_rec.bank_to_base_xrate,
3087 p_pay_hist_rec.bank_currency_code);
3088
3089 ELSE
3090
3091 l_disc_amt_bank_curr := p_inv_pay_rec.discount_taken;
3092
3093 END IF;
3094
3095 ELSE
3096
3097 -- bug 5652032
3098 -- due to record were inserted into payment history.
3099 -- often we don't need to have bank currency and bank amount
3100 -- related information, we need to take care of this case
3101 -- when transaction type is not clearing/unclearing
3102
3106 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3103 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3104 l_log_msg := 'assigning the disc_amt_bank_curr for event type' ||
3105 p_xla_event_rec.event_type_code;
3107 END IF;
3108
3109 l_disc_amt_bank_curr := p_inv_pay_rec.discount_taken;
3110
3111 END IF;
3112
3113
3114 IF (p_calc_mode IN ('P','M')) THEN
3115
3116 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3117 l_log_msg := 'not cascade mode - p_calc_mode = ' || p_calc_mode;
3118 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3119 END IF;
3120
3121 -- Bug 7577312: Changed the logic here to refer to l_total_dist_amount
3122 IF g_total_dist_amt = l_total_dist_amount THEN -- last dist rec
3123
3124 -- To avoid rounding, massage the last (biggest) line
3125 l_prorated_disc_amt := p_inv_pay_rec.discount_taken - g_total_prorated_disc_amt;
3126 l_inv_dist_amount := l_disc_amt_inv_curr - g_total_inv_dist_disc_amt;
3127 l_bank_curr_amount := l_disc_amt_bank_curr - g_total_bank_curr_disc_amt;
3128
3129 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3130 l_log_msg := 'Value of l_prorated_disc_amt = '||l_prorated_disc_amt;
3131 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3132 END IF;
3133
3134
3135 ELSE
3136
3137 IF l_invoice_amount = 0 THEN
3138
3139 l_prorated_disc_amt := 0;
3140 l_inv_dist_amount := 0;
3141 l_bank_curr_amount := 0;
3142
3143 ELSE
3144
3145 l_prorated_disc_amt := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
3146 (p_inv_pay_rec.discount_taken * p_inv_dist_rec.amount
3147 / l_invoice_amount,
3148 p_pay_hist_rec.pmt_currency_code);
3149
3150 l_inv_dist_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
3151 (l_disc_amt_inv_curr * p_inv_dist_rec.amount
3152 / l_invoice_amount,
3153 p_inv_rec.invoice_currency_code);
3154
3155
3156 IF ( p_pay_hist_rec.bank_currency_code is not NULL ) THEN
3157 l_bank_curr_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
3158 (l_disc_amt_bank_curr * p_inv_dist_rec.amount
3159 / l_invoice_amount,
3160 p_pay_hist_rec.bank_currency_code);
3161
3162 END IF;
3163
3164 END IF;
3165
3166 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3167 l_log_msg := 'Value of l_prorated_disc_amt = '||l_prorated_disc_amt;
3168 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3169 END IF;
3170
3171 END IF;
3172
3173 g_total_prorated_disc_amt := g_total_prorated_disc_amt + l_prorated_disc_amt;
3174 g_total_inv_dist_disc_amt := g_total_inv_dist_disc_amt + l_inv_dist_amount;
3175 g_total_bank_curr_disc_amt := g_total_bank_curr_disc_amt + l_bank_curr_amount;
3176
3177 ELSE
3178
3179 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3180 l_log_msg := 'seems cascade mode - p_calc_mode = ' || p_calc_mode;
3181 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3182 END IF;
3183
3184 l_prorated_disc_amt := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
3185 (p_inv_pay_rec.discount_taken * p_inv_dist_rec.amount
3186 / l_invoice_amount,
3187 p_pay_hist_rec.pmt_currency_code)
3188 - ap_accounting_pay_pkg.get_casc_discount_sum
3189 (p_inv_dist_rec.invoice_distribution_id,
3190 p_pay_hist_rec.related_event_id,
3191 p_inv_pay_rec.invoice_payment_id,
3192 l_curr_calling_sequence);
3193
3194 l_inv_dist_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
3195 (l_disc_amt_inv_curr * p_inv_dist_rec.amount
3196 / l_invoice_amount,
3197 p_inv_rec.invoice_currency_code)
3198 - ap_accounting_pay_pkg.get_casc_inv_dist_disc_sum
3199 (p_inv_dist_rec.invoice_distribution_id,
3200 p_pay_hist_rec.related_event_id,
3201 p_inv_pay_rec.invoice_payment_id,
3202 l_curr_calling_sequence);
3203
3204 IF ( p_pay_hist_rec.bank_currency_code is not NULL ) THEN
3205 l_inv_dist_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
3206 (l_disc_amt_bank_curr * p_inv_dist_rec.amount
3207 / l_invoice_amount,
3211 p_pay_hist_rec.related_event_id,
3208 p_pay_hist_rec.bank_currency_code)
3209 - ap_accounting_pay_pkg.get_casc_bank_curr_disc_sum
3210 (p_inv_dist_rec.invoice_distribution_id,
3212 p_inv_pay_rec.invoice_payment_id,
3213 l_curr_calling_sequence);
3214 END IF;
3215
3216 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3217 l_log_msg := 'Value of l_prorated_disc_amt = '||l_prorated_disc_amt;
3218 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3219 END IF;
3220
3221
3222 END IF; -- If calc_mode in ('P,'M')
3223
3224 END IF;
3225
3226
3227 -- Populate payment dist rec
3228 l_pd_rec.accounting_event_id := p_xla_event_rec.event_id;
3229 l_pd_rec.pay_dist_lookup_code := 'DISCOUNT';
3230 l_pd_rec.invoice_distribution_id := p_inv_dist_rec.invoice_distribution_id;
3231 l_pd_rec.amount := l_prorated_disc_amt;
3232
3233 l_pd_rec.payment_history_id := p_pay_hist_rec.payment_history_id;
3234 l_pd_rec.invoice_payment_id := p_inv_pay_rec.invoice_payment_id;
3235
3236 l_pd_rec.bank_curr_amount := l_bank_curr_amount;
3237
3238 l_prorated_base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
3239 (l_prorated_disc_amt,
3240 p_pay_hist_rec.pmt_currency_code,
3241 ap_accounting_pay_pkg.g_base_currency_code,
3242 p_pay_hist_rec.pmt_to_base_xrate_type,
3243 p_pay_hist_rec.pmt_to_base_xrate_date,
3244 p_pay_hist_rec.pmt_to_base_xrate,
3245 l_curr_calling_sequence);
3246
3247 IF p_xla_event_rec.event_type_code IN
3248 ('PAYMENT CLEARED', 'PAYMENT CLEARING ADJUSTED') THEN
3249
3250 l_pd_rec.cleared_base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
3251 (l_bank_curr_amount,
3252 p_pay_hist_rec.bank_currency_code,
3253 ap_accounting_pay_pkg.g_base_currency_code,
3254 p_pay_hist_rec.bank_to_base_xrate_type,
3255 p_pay_hist_rec.bank_to_base_xrate_date,
3256 p_pay_hist_rec.bank_to_base_xrate,
3257 l_curr_calling_sequence);
3258
3259 l_pd_rec.paid_base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
3260 (l_prorated_disc_amt,
3261 p_pay_hist_rec.pmt_currency_code,
3262 ap_accounting_pay_pkg.g_base_currency_code,
3263 ap_accounting_pay_pkg.g_pmt_to_base_xrate_type,
3264 ap_accounting_pay_pkg.g_pmt_to_base_xrate_date,
3265 ap_accounting_pay_pkg.g_pmt_to_base_xrate,
3266 l_curr_calling_sequence);
3267
3268 IF ap_accounting_pay_pkg.g_mat_to_base_xrate IS NOT NULL THEN
3269
3270 l_pd_rec.matured_base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
3271 (l_prorated_disc_amt,
3272 p_pay_hist_rec.pmt_currency_code,
3273 ap_accounting_pay_pkg.g_base_currency_code,
3274 ap_accounting_pay_pkg.g_mat_to_base_xrate_type,
3275 ap_accounting_pay_pkg.g_mat_to_base_xrate_date,
3276 ap_accounting_pay_pkg.g_mat_to_base_xrate,
3277 l_curr_calling_sequence);
3278 END IF ;
3279
3280 ELSIF p_xla_event_rec.event_type_code IN
3281 ('PAYMENT MATURED', 'PAYMENT MATURITY ADJUSTED') THEN
3282 l_pd_rec.matured_base_amount := l_prorated_base_amount;
3283
3284 l_pd_rec.paid_base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
3285 (l_prorated_disc_amt,
3286 p_pay_hist_rec.pmt_currency_code,
3287 ap_accounting_pay_pkg.g_base_currency_code,
3288 ap_accounting_pay_pkg.g_pmt_to_base_xrate_type,
3289 ap_accounting_pay_pkg.g_pmt_to_base_xrate_date,
3290 ap_accounting_pay_pkg.g_pmt_to_base_xrate,
3291 l_curr_calling_sequence);
3292
3293 ELSE
3294 l_pd_rec.paid_base_amount := l_prorated_base_amount;
3295 END IF;
3296
3297
3298 l_pd_rec.invoice_dist_amount := l_inv_dist_amount;
3299
3300 /* If the exchange rates between the invoice and payment have not changed then
3301 the invoice and payment base amounts should be the same. Assigning the
3302 payment base amount to the invoice base amount instead of recalculating the
3303 invoice base amount */
3304
3305 IF (p_pay_hist_rec.pmt_to_base_xrate =
3306 p_inv_rec.exchange_rate / p_inv_rec.payment_cross_rate) THEN
3307
3311
3308 l_pd_rec.invoice_dist_base_amount := l_prorated_base_amount;
3309
3310 ELSE
3312 l_pd_rec.invoice_dist_base_amount :=
3313 AP_Accounting_Pay_Pkg.Get_Base_Amount
3314 (l_inv_dist_amount,
3315 p_inv_rec.invoice_currency_code,
3316 ap_accounting_pay_pkg.g_base_currency_code,
3317 p_inv_rec.exchange_rate_type,
3318 p_inv_rec.exchange_date,
3319 p_inv_rec.exchange_rate,
3320 l_curr_calling_sequence);
3321
3322 END IF;
3323
3324 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3325 l_log_msg := 'Calling procedure Pay_Dist_Insert';
3326 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
3327 END IF;
3328
3329
3330 Pay_Dist_Insert
3331 (l_pd_rec,
3332 l_curr_calling_sequence);
3333
3334
3335 p_disc_pay_amount := NVL(l_prorated_disc_amt,0);
3336 p_disc_dist_amount := NVL(l_inv_dist_amount,0);
3337 p_disc_bank_amount := NVL(l_bank_curr_amount,0);
3338
3339
3340 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3341 l_log_msg := 'Procedure Pay_Dist_Insert executed';
3342 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
3343 END IF;
3344
3345
3346 EXCEPTION
3347 WHEN OTHERS THEN
3348 IF (SQLCODE <> -20001) THEN
3349 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3350 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3351 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
3352 END IF;
3353 APP_EXCEPTION.RAISE_EXCEPTION;
3354
3355 END Pay_Dist_Discount;
3356
3357
3358 ---------------------------------------------------------------------
3359 -- Procedure Pay_Dist_ERV
3360 -- This procedure calculates the ERV base amounts for the ERV distributions
3361 -- and inserts the calculated values into payment hist dists table
3362 ---------------------------------------------------------------------
3363
3364 PROCEDURE Pay_Dist_ERV
3365 (p_xla_event_rec IN ap_accounting_pay_pkg.r_xla_event_info
3366 ,p_inv_pay_rec IN r_inv_pay_info
3367 ,p_pay_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
3368 ,p_inv_rec IN ap_accounting_pay_pkg.r_invoices_info
3369 ,p_inv_dist_rec IN ap_accounting_pay_pkg.r_inv_dist_info
3370 ,p_prorated_amount IN NUMBER
3371 ,p_calling_sequence IN VARCHAR2
3372 ) IS
3373
3374 l_curr_calling_sequence VARCHAR2(2000);
3375 l_po_exchange_rate NUMBER;
3376 l_po_pay_exchange_rate NUMBER;
3377 l_erv_amount NUMBER;
3378 l_inv_erv_amount NUMBER;
3379 l_pd_rec AP_PAYMENT_HIST_DISTS%ROWTYPE;
3380
3381 -- Logging Infra:
3382 l_procedure_name CONSTANT VARCHAR2(30) := 'Pay_Dist_ERV';
3383 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3384
3385 BEGIN
3386
3387
3388 l_curr_calling_sequence := 'AP_ACCTG_PAY_DIST_PKG.Pay_Dist_ERV<- ' ||
3389 p_calling_sequence;
3390
3391
3392 -- Logging Infra: Procedure level
3393 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3394 l_log_msg := 'Begin of procedure '|| l_procedure_name;
3395 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
3396 END IF;
3397
3398 IF p_inv_dist_rec.rcv_transaction_id IS NOT NULL THEN
3399
3400 SELECT Currency_Conversion_Rate
3401 INTO l_po_exchange_rate
3402 FROM rcv_transactions
3403 WHERE transaction_id = p_inv_dist_rec.rcv_transaction_id;
3404
3405 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3406 l_log_msg := 'receipt matching and exchange rate = ' ||
3407 l_po_exchange_rate;
3408 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
3409 END IF;
3410
3411 ELSE
3412
3413 SELECT Rate
3414 INTO l_po_exchange_rate
3415 FROM PO_Distributions_All
3416 WHERE PO_Distribution_ID = p_inv_dist_rec.PO_Distribution_ID;
3417
3418 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3419 l_log_msg := 'po matching and exchange rate = ' ||
3420 l_po_exchange_rate;
3421 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
3422 END IF;
3423
3424 END IF;
3425
3426 IF p_inv_rec.invoice_currency_code <> p_inv_rec.payment_currency_code THEN
3427
3428 l_po_pay_exchange_rate := l_po_exchange_rate / p_inv_rec.payment_cross_rate;
3429
3430 ELSE
3431 l_po_pay_exchange_rate := l_po_exchange_rate;
3432 END IF;
3433
3434 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3435 l_log_msg := 'Value of l_po_pay_exchange_rate = '||l_po_pay_exchange_rate ||
3436 'value of p_prorated_amount=' || p_prorated_amount;
3437 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3438 END IF;
3439
3440
3441 /* For Cash Basis ERV is Difference between Payment Exchange Rate and
3445 (p_pay_hist_rec.pmt_to_base_xrate - l_po_pay_exchange_rate) *
3442 either Receipt Exchange rate or PO distributions exchange rate */
3443
3444 l_erv_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
3446 p_prorated_amount, p_pay_hist_rec.pmt_currency_code);
3447
3448
3449 /* In order to back out the encumbrance entries correctly during cash basis
3450 we need to calculate ERV based on the difference between the Invoice
3451 Exchange Rate and either Receipt Exchange rate or PO distributions
3452 exchange rate. This calculated ERV amount will be stored in the
3453 invoice_dist_base_amount column */
3454
3455 l_inv_erv_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
3456 (p_inv_rec.exchange_rate - l_po_pay_exchange_rate) *
3457 p_prorated_amount, p_inv_rec.invoice_currency_code);
3458
3459
3460 -- Populate payment dist rec
3461 l_pd_rec.accounting_event_id := p_xla_event_rec.event_id;
3462
3463 IF (p_inv_dist_rec.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX')) THEN
3464 l_pd_rec.pay_dist_lookup_code := 'TAX EXCHANGE RATE VARIANCE';
3465 ELSE
3466 l_pd_rec.pay_dist_lookup_code := 'EXCHANGE RATE VARIANCE';
3467 END IF;
3468
3469 l_pd_rec.invoice_distribution_id := p_inv_dist_rec.invoice_distribution_id;
3470 l_pd_rec.amount := 0;
3471
3472 l_pd_rec.payment_history_id := p_pay_hist_rec.payment_history_id;
3473 l_pd_rec.invoice_payment_id := p_inv_pay_rec.invoice_payment_id;
3474 l_pd_rec.bank_curr_amount := 0;
3475 l_pd_rec.invoice_dist_amount := 0;
3476
3477 IF (p_xla_event_rec.event_type_code IN ('PAYMENT CLEARED',
3478 'PAYMENT CLEARING ADJUSTED')) THEN
3479 l_pd_rec.cleared_base_amount := l_erv_amount;
3480 ELSE
3481 l_pd_rec.paid_base_amount := l_erv_amount;
3482 END IF;
3483
3484 l_pd_rec.invoice_dist_base_amount := l_inv_erv_amount;
3485
3486 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3487 l_log_msg := 'Calling procedure Pay_Dist_Insert';
3488 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
3489 END IF;
3490
3491 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3492 l_log_msg := 'before callining erv/terv insert -' ||
3493 'l_pd_rec.invoice_dist_base_amount = ' ||
3494 l_pd_rec.invoice_dist_base_amount ||
3495 'l_pd_rec.paid_base_amount or cleared_base_amount =' ||
3496 l_erv_amount;
3497 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
3498 END IF;
3499
3500 Pay_Dist_Insert
3501 (l_pd_rec,
3502 l_curr_calling_sequence);
3503
3504 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3505 l_log_msg := 'Procedure Pay_Dist_Insert executed';
3506 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
3507 END IF;
3508
3509 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3510 l_log_msg := 'end of procedure Pay_Dist_ERV';
3511 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
3512 END IF;
3513
3514 EXCEPTION
3515 WHEN OTHERS THEN
3516 IF (SQLCODE <> -20001) THEN
3517 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3518 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3519 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
3520 END IF;
3521 APP_EXCEPTION.RAISE_EXCEPTION;
3522
3523 END Pay_Dist_ERV;
3524
3525
3526 ---------------------------------------------------------------------
3527 -- Procedure Pay_Dist_Err_Chrg
3528 -- This procedure prorates the errors and charge amounts for each distribution
3529 -- and inserts the calculated values into payment hist dists table
3530 ---------------------------------------------------------------------
3531
3532 PROCEDURE Pay_Dist_Err_Chrg
3533 (p_xla_event_rec IN ap_accounting_pay_pkg.r_xla_event_info
3534 ,p_pay_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
3535 ,p_distribute_mode IN VARCHAR2
3536 ,p_calling_sequence IN VARCHAR2
3537 ) IS
3538
3539 l_curr_calling_sequence VARCHAR2(2000);
3540 l_err_chrg_amount NUMBER;
3541 l_prorated_amt NUMBER;
3542 l_prorated_base_amount NUMBER;
3543 l_total_pay_amt NUMBER;
3544 l_pay_dist_type VARCHAR2(30);
3545 l_total_dist_amt NUMBER := 0;
3546 l_total_prorated_amt NUMBER := 0;
3547 l_inv_dist_amount NUMBER;
3548
3549 l_pd_rec AP_PAYMENT_HIST_DISTS%ROWTYPE;
3550
3551 CURSOR clearing_pay_dists
3552 (P_Event_ID NUMBER)
3553 IS
3554 SELECT Accounting_Event_ID,
3555 Invoice_Distribution_ID,
3556 Amount,
3557 Payment_History_ID,
3558 Invoice_Payment_ID
3559 FROM AP_Payment_Hist_Dists APHD
3560 WHERE APHD.Accounting_Event_ID = P_Event_ID
3561 AND APHD.Pay_Dist_Lookup_Code = 'CASH'
3562 ORDER BY Amount;
3563
3564 -- Logging Infra:
3565 l_procedure_name CONSTANT VARCHAR2(30) := 'Pay_Dist_Err_Chrg';
3566 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3567
3568
3569 BEGIN
3570
3571
3575 -- Logging Infra: Procedure level
3572 l_curr_calling_sequence := 'AP_ACCTG_PAY_DIST_PKG.Pay_Dist_Err_Chrg<- ' ||
3573 p_calling_sequence;
3574
3576 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3577 l_log_msg := 'Begin of procedure '|| l_procedure_name;
3578 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
3579 END IF;
3580
3581
3582 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3583 l_log_msg := 'Passing Parameters are ' ||
3584 'p_distribute_mode=' || p_distribute_mode ||
3585 'event_id = ' || p_xla_event_rec.event_id;
3586 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
3587 END IF;
3588
3589
3590 IF ( p_distribute_mode = 'BANK_ERROR' AND
3591 NVL(p_pay_hist_rec.errors_bank_amount,0) <> 0) THEN
3592
3593 l_err_chrg_amount := p_pay_hist_rec.errors_bank_amount;
3594 l_pay_dist_type := 'BANK ERROR';
3595
3596 ELSIF ( p_distribute_mode = 'BANK_CHARGE' AND
3597 NVL(p_pay_hist_rec.charges_bank_amount,0) <> 0 ) THEN
3598
3599 l_err_chrg_amount := p_pay_hist_rec.charges_bank_amount;
3600 l_pay_dist_type := 'BANK CHARGE';
3601
3602 ELSE
3603 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3604 l_log_msg := 'No need to prorate charge or error when amount=0 - return';
3605 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
3606 END IF;
3607 return;
3608 END IF;
3609
3610 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3611 l_log_msg := 'l_err_chrg_amount= ' || l_err_chrg_amount;
3612 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
3613 END IF;
3614
3615
3616 SELECT SUM(Amount)
3617 INTO l_total_pay_amt
3618 FROM AP_Payment_Hist_Dists
3619 WHERE Payment_History_ID = p_pay_hist_rec.payment_history_id
3620 AND Pay_Dist_Lookup_Code = 'CASH';
3621
3622
3623 FOR l_clr_rec IN clearing_pay_dists(p_xla_event_rec.event_id)
3624 LOOP
3625
3626 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3627 l_log_msg := 'CUR: clearing_pay_dists: Invoice_Dist_ID = '
3628 ||l_clr_rec.invoice_distribution_id;
3629 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3630 END IF;
3631
3632 l_total_dist_amt := l_total_dist_amt + l_clr_rec.amount;
3633
3634 IF l_total_dist_amt = l_total_pay_amt THEN
3635 l_prorated_amt := l_err_chrg_amount - l_total_prorated_amt;
3636 ELSE
3637 l_prorated_amt := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
3638 l_err_chrg_amount * l_clr_rec.amount
3639 / l_total_pay_amt, p_pay_hist_rec.pmt_currency_code);
3640 END IF;
3641
3642 l_total_prorated_amt := l_total_prorated_amt + l_prorated_amt;
3643
3644
3645 -- Populate payment dist rec
3646 l_pd_rec.accounting_event_id := p_xla_event_rec.event_id;
3647
3648 l_pd_rec.pay_dist_lookup_code := l_pay_dist_type;
3649 l_pd_rec.invoice_distribution_id := l_clr_rec.invoice_distribution_id;
3650 l_pd_rec.amount := l_prorated_amt;
3651
3652 l_pd_rec.payment_history_id := p_pay_hist_rec.payment_history_id;
3653 l_pd_rec.invoice_payment_id := l_clr_rec.invoice_payment_id;
3654
3655 l_pd_rec.bank_curr_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
3656 (l_prorated_amt,
3657 p_pay_hist_rec.bank_currency_code,
3658 ap_accounting_pay_pkg.g_base_currency_code,
3659 p_pay_hist_rec.bank_to_base_xrate_type,
3660 p_pay_hist_rec.bank_to_base_xrate_date,
3661 p_pay_hist_rec.bank_to_base_xrate,
3662 l_curr_calling_sequence);
3663
3664 -- Bug6901436. l_prorated_amount is in bank currency. Bank to base
3665 -- exchange rate details needs to be provided to Get_Base_Amount
3666 -- procedure.
3667
3668 /*l_prorated_base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
3669 -- (l_prorated_amt,
3670 -- p_pay_hist_rec.pmt_currency_code,
3671 -- ap_accounting_pay_pkg.g_base_currency_code,
3672 -- p_pay_hist_rec.pmt_to_base_xrate_type,
3673 -- p_pay_hist_rec.pmt_to_base_xrate_date,
3674 -- p_pay_hist_rec.pmt_to_base_xrate,
3675 -- l_curr_calling_sequence);*/
3676
3677 l_prorated_base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
3678 (l_prorated_amt,
3679 p_pay_hist_rec.bank_currency_code,
3680 ap_accounting_pay_pkg.g_base_currency_code,
3681 p_pay_hist_rec.bank_to_base_xrate_type,
3682 p_pay_hist_rec.bank_to_base_xrate_date,
3683 p_pay_hist_rec.bank_to_base_xrate,
3684 l_curr_calling_sequence);
3685
3686 -- End bug6901436
3690 l_pd_rec.cleared_base_amount := l_prorated_base_amount;
3687
3688 IF p_xla_event_rec.event_type_code IN
3689 ('PAYMENT CLEARED', 'PAYMENT CLEARING ADJUSTED') THEN
3691
3692 l_pd_rec.paid_base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
3693 (l_prorated_amt,
3694 p_pay_hist_rec.pmt_currency_code,
3695 ap_accounting_pay_pkg.g_base_currency_code,
3696 ap_accounting_pay_pkg.g_pmt_to_base_xrate_type,
3697 ap_accounting_pay_pkg.g_pmt_to_base_xrate_date,
3698 ap_accounting_pay_pkg.g_pmt_to_base_xrate,
3699 l_curr_calling_sequence);
3700
3701 IF ap_accounting_pay_pkg.g_mat_to_base_xrate IS NOT NULL THEN
3702
3703 l_pd_rec.matured_base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
3704 (l_prorated_amt,
3705 p_pay_hist_rec.pmt_currency_code,
3706 ap_accounting_pay_pkg.g_base_currency_code,
3707 ap_accounting_pay_pkg.g_mat_to_base_xrate_type,
3708 ap_accounting_pay_pkg.g_mat_to_base_xrate_date,
3709 ap_accounting_pay_pkg.g_mat_to_base_xrate,
3710 l_curr_calling_sequence);
3711 END IF ;
3712 ELSIF p_xla_event_rec.event_type_code IN
3713 ('PAYMENT MATURED', 'PAYMENT MATURITY ADJUSTED') THEN
3714 l_pd_rec.matured_base_amount := l_prorated_base_amount;
3715
3716 l_pd_rec.paid_base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
3717 (l_prorated_amt,
3718 p_pay_hist_rec.pmt_currency_code,
3719 ap_accounting_pay_pkg.g_base_currency_code,
3720 ap_accounting_pay_pkg.g_pmt_to_base_xrate_type,
3721 ap_accounting_pay_pkg.g_pmt_to_base_xrate_date,
3722 ap_accounting_pay_pkg.g_pmt_to_base_xrate,
3723 l_curr_calling_sequence);
3724
3725 ELSE
3726 l_pd_rec.paid_base_amount := l_prorated_base_amount;
3727 END IF;
3728
3729 l_pd_rec.invoice_dist_amount := NULL;
3730 l_pd_rec.invoice_dist_base_amount := NULL;
3731
3732
3733 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3734 l_log_msg := 'Calling procedure Pay_Dist_Insert';
3735 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
3736 END IF;
3737
3738 -- Insert the payment hist distribution
3739 Pay_Dist_Insert
3740 (l_pd_rec,
3741 l_curr_calling_sequence);
3742
3743 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3744 l_log_msg := 'Procedure Pay_Dist_Insert executed';
3745 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
3746 END IF;
3747
3748
3749 END LOOP;
3750
3751
3752 EXCEPTION
3753 WHEN OTHERS THEN
3754 IF (SQLCODE <> -20001) THEN
3755 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3756 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3757 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
3758 END IF;
3759 APP_EXCEPTION.RAISE_EXCEPTION;
3760
3761 END Pay_Dist_Err_Chrg;
3762
3763
3764
3765 ---------------------------------------------------------------------
3766 -- Procedure Pay_Dist_Reverse
3767 -- This procedure reverses the payment distributions of the invoice
3768 -- payments that have been reversed.
3769 --
3770 ---------------------------------------------------------------------
3771
3772 -- Bug 6887295. Added parameter p_inv_dist_rec
3773 PROCEDURE Pay_Dist_Reverse
3774 (p_xla_event_rec IN ap_accounting_pay_pkg.r_xla_event_info
3775 ,p_inv_pay_rec IN r_inv_pay_info
3776 ,p_pay_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
3777 ,p_reversal_inv_pmt_id IN NUMBER
3778 ,p_related_event_id IN NUMBER
3779 ,p_invoice_dist_id IN NUMBER
3780 ,p_inv_dist_rec IN ap_accounting_pay_pkg.r_inv_dist_info
3781 ,p_calling_sequence IN VARCHAR2
3782 ) IS
3783
3784 l_curr_calling_sequence VARCHAR2(2000);
3785
3786 -- Logging Infra:
3787 l_procedure_name CONSTANT VARCHAR2(30) := 'Pay_Dist_Reverse';
3788 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3789
3790 l_transaction_type AP_PAYMENT_HISTORY_ALL.TRANSACTION_TYPE%TYPE;
3791 l_payment_history_id AP_PAYMENT_HISTORY_ALL.PAYMENT_HISTORY_ID%TYPE;
3792 l_accounting_event_id AP_PAYMENT_HISTORY_ALL.ACCOUNTING_EVENT_ID%TYPE;
3793 l_historical_flag AP_PAYMENT_HISTORY_ALL.HISTORICAL_FLAG%TYPE;
3794
3795
3796 BEGIN
3797
3798 l_curr_calling_sequence := 'AP_ACCTG_PAY_DIST_PKG.Pay_Dist_Reverse<-' ||
3799 p_calling_sequence;
3800
3801
3802 -- Logging Infra: Procedure level
3806 END IF;
3803 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3804 l_log_msg := 'Begin of procedure '|| l_procedure_name;
3805 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
3807
3808 IF p_reversal_inv_pmt_id IS NOT NULL THEN
3809
3810
3811 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3812 l_log_msg := 'Reversing based on reversal_inv_pmt_id '||
3813 p_reversal_inv_pmt_id;
3814 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3815 END IF;
3816
3817 INSERT INTO ap_payment_hist_dists
3818 (Payment_Hist_Dist_ID,
3819 Accounting_Event_ID,
3820 Amount,
3821 Pay_Dist_Lookup_Code,
3822 Payment_History_ID,
3823 Invoice_Distribution_ID,
3824 Invoice_Payment_ID,
3825 Bank_Curr_Amount,
3826 Cleared_Base_Amount,
3827 Invoice_Dist_Amount,
3828 Invoice_Dist_Base_Amount,
3829 Invoice_Adjustment_Event_ID,
3830 Matured_Base_Amount,
3831 Paid_Base_Amount,
3832 Reversal_Flag,
3833 Reversed_Pay_Hist_Dist_ID,
3834 AWT_Related_ID,
3835 PA_Addition_Flag,
3836 Quantity_Variance,
3837 Invoice_Base_Qty_Variance,
3838 Amount_Variance,
3839 Invoice_Base_Amt_Variance,
3840 Created_By,
3841 Creation_Date,
3842 Last_Update_Date,
3843 Last_Updated_By,
3844 Last_Update_Login,
3845 Program_Application_ID,
3846 Program_ID,
3847 Program_Login_ID,
3848 Program_Update_Date,
3849 Request_ID
3850 )
3851 SELECT AP_Payment_Hist_Dists_S.nextval,
3852 p_xla_event_rec.event_id,
3853 -1 * APHD.Amount,
3854 APHD.Pay_Dist_Lookup_Code,
3855 p_pay_hist_rec.Payment_History_ID,
3856 APHD.Invoice_Distribution_ID,
3857 p_inv_pay_rec.invoice_payment_id,
3858 -1 * APHD.Bank_Curr_Amount,
3859 -1 * APHD.Cleared_Base_Amount,
3860 -1 * APHD.Invoice_Dist_Amount,
3861 -1 * APHD.Invoice_Dist_Base_Amount,
3862 APHD.Invoice_Adjustment_Event_ID,
3863 -1 * APHD.Matured_Base_Amount,
3864 -1 * APHD.Paid_Base_Amount,
3865 'Y',
3866 APHD.Payment_Hist_Dist_ID,
3867 APHD.AWT_Related_ID,
3868 'N',
3869 APHD.Quantity_Variance,
3870 APHD.Invoice_Base_Qty_Variance,
3871 APHD.Amount_Variance,
3872 APHD.Invoice_Base_Amt_Variance,
3873 FND_GLOBAL.User_ID,
3874 SYSDATE,
3875 SYSDATE,
3876 FND_GLOBAL.User_ID,
3877 FND_GLOBAL.User_ID,
3878 FND_GLOBAL.Prog_Appl_ID,
3879 FND_GLOBAL.Conc_Program_ID,
3880 NULL,
3881 SYSDATE,
3882 FND_GLOBAL.Conc_Request_ID
3883 FROM AP_Payment_Hist_Dists APHD,
3884 AP_Invoice_Payments_All AIP,
3885 AP_Invoice_Distributions_All AID
3886 WHERE AIP.Invoice_Payment_ID = p_reversal_inv_pmt_id
3887 AND AIP.Accounting_Event_ID = APHD.Accounting_Event_ID
3888 AND AIP.Invoice_ID = AID.Invoice_ID
3889 AND AID.Invoice_Distribution_ID = APHD.Invoice_Distribution_ID;
3890
3891
3892 ELSIF p_related_event_id IS NOT NULL THEN
3893
3894
3895 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3896 l_log_msg := 'Reversing based on related_event_id '||
3897 p_related_event_id;
3898 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3899 END IF;
3900
3901 -- Bug 5015973. We will insert the new invoice_payment_id only for the
3902 -- cancelled event.
3903 IF p_xla_event_rec.event_type_code IN ('PAYMENT CANCELLED',
3904 'REFUND CANCELLED') THEN
3905
3906 INSERT INTO ap_payment_hist_dists
3907 (Payment_Hist_Dist_ID,
3908 Accounting_Event_ID,
3909 Amount,
3910 Pay_Dist_Lookup_Code,
3911 Payment_History_ID,
3912 Invoice_Distribution_ID,
3913 Invoice_Payment_ID,
3914 Bank_Curr_Amount,
3915 Cleared_Base_Amount,
3916 Invoice_Dist_Amount,
3917 Invoice_Dist_Base_Amount,
3918 Invoice_Adjustment_Event_ID,
3919 Matured_Base_Amount,
3920 Paid_Base_Amount,
3921 Reversal_Flag,
3922 Reversed_Pay_Hist_Dist_ID,
3923 AWT_Related_ID,
3924 PA_Addition_Flag,
3925 Quantity_Variance,
3926 Invoice_Base_Qty_Variance,
3927 Amount_Variance,
3928 Invoice_Base_Amt_Variance,
3929 Created_By,
3930 Creation_Date,
3931 Last_Update_Date,
3932 Last_Updated_By,
3933 Last_Update_Login,
3934 Program_Application_ID,
3935 Program_ID,
3936 Program_Login_ID,
3940 SELECT AP_Payment_Hist_Dists_S.nextval,
3937 Program_Update_Date,
3938 Request_ID
3939 )
3941 p_xla_event_rec.event_id,
3942 -1 * APHD.Amount,
3943 APHD.Pay_Dist_Lookup_Code,
3944 p_pay_hist_rec.Payment_History_ID,
3945 APHD.Invoice_Distribution_ID,
3946 AIP.Invoice_Payment_ID,
3947 -1 * APHD.Bank_Curr_Amount,
3948 -1 * APHD.Cleared_Base_Amount,
3949 -1 * APHD.Invoice_Dist_Amount,
3950 -1 * APHD.Invoice_Dist_Base_Amount,
3951 APHD.Invoice_Adjustment_Event_ID,
3952 -1 * APHD.Matured_Base_Amount,
3953 -1 * APHD.Paid_Base_Amount,
3954 'Y',
3955 APHD.Payment_Hist_Dist_ID,
3956 APHD.AWT_Related_ID,
3957 'N',
3958 APHD.Quantity_Variance,
3959 APHD.Invoice_Base_Qty_Variance,
3960 APHD.Amount_Variance,
3961 APHD.Invoice_Base_Amt_Variance,
3962 FND_GLOBAL.User_ID,
3963 SYSDATE,
3964 SYSDATE,
3965 FND_GLOBAL.User_ID,
3966 FND_GLOBAL.User_ID,
3967 FND_GLOBAL.Prog_Appl_ID,
3968 FND_GLOBAL.Conc_Program_ID,
3969 NULL,
3970 SYSDATE,
3971 FND_GLOBAL.Conc_Request_ID
3972 FROM AP_Payment_Hist_Dists APHD,
3973 AP_Payment_History_All APH,
3974 AP_Invoice_Payments_All AIP,
3975 AP_Invoice_Distributions_All AID -- 6804379
3976 WHERE nvl(APH.Related_Event_ID, APH.Accounting_Event_ID) = p_related_event_id
3977 AND APHD.Payment_History_ID = APH.Payment_History_ID
3978 AND NVL(APHD.Reversal_Flag,'N') <> 'Y'
3979 AND AID.Invoice_Distribution_ID = APHD.Invoice_Distribution_ID
3980 AND AIP.Reversal_inv_pmt_id = APHD.invoice_payment_id --Bug 6881085
3981 AND AIP.Accounting_Event_ID = p_xla_event_rec.event_id
3982 AND AIP.Check_ID = APH.Check_ID -- Bug 6856694
3983 AND AIP.Invoice_ID = AID.Invoice_ID
3984 AND NOT EXISTS (SELECT 'Reversal Payment Dists'
3985 FROM AP_Payment_Hist_Dists APHD1
3986 WHERE APHD1.Reversed_Pay_Hist_Dist_ID
3987 = APHD.Payment_Hist_Dist_ID
3988 -- Bug 6856694
3989 AND APHD1.Invoice_Distribution_ID
3990 = APHD.Invoice_Distribution_ID);
3991
3992 ELSE
3993 INSERT INTO ap_payment_hist_dists
3994 (Payment_Hist_Dist_ID,
3995 Accounting_Event_ID,
3996 Amount,
3997 Pay_Dist_Lookup_Code,
3998 Payment_History_ID,
3999 Invoice_Distribution_ID,
4000 Invoice_Payment_ID,
4001 Bank_Curr_Amount,
4002 Cleared_Base_Amount,
4003 Invoice_Dist_Amount,
4004 Invoice_Dist_Base_Amount,
4005 Invoice_Adjustment_Event_ID,
4006 Matured_Base_Amount,
4007 Paid_Base_Amount,
4008 Reversal_Flag,
4009 Reversed_Pay_Hist_Dist_ID,
4010 AWT_Related_ID,
4011 PA_Addition_Flag,
4012 Quantity_Variance,
4013 Invoice_Base_Qty_Variance,
4014 Amount_Variance,
4015 Invoice_Base_Amt_Variance,
4016 Created_By,
4017 Creation_Date,
4018 Last_Update_Date,
4019 Last_Updated_By,
4020 Last_Update_Login,
4021 Program_Application_ID,
4022 Program_ID,
4023 Program_Login_ID,
4024 Program_Update_Date,
4025 Request_ID
4026 )
4027 SELECT AP_Payment_Hist_Dists_S.nextval,
4028 p_xla_event_rec.event_id,
4029 -1 * APHD.Amount,
4030 APHD.Pay_Dist_Lookup_Code,
4031 p_pay_hist_rec.Payment_History_ID,
4032 APHD.Invoice_Distribution_ID,
4033 APHD.Invoice_Payment_ID,
4034 -1 * APHD.Bank_Curr_Amount,
4035 -1 * APHD.Cleared_Base_Amount,
4036 -1 * APHD.Invoice_Dist_Amount,
4037 -1 * APHD.Invoice_Dist_Base_Amount,
4038 APHD.Invoice_Adjustment_Event_ID,
4039 -1 * APHD.Matured_Base_Amount,
4040 -1 * APHD.Paid_Base_Amount,
4041 'Y',
4042 APHD.Payment_Hist_Dist_ID,
4043 APHD.AWT_Related_ID,
4044 'N',
4045 APHD.Quantity_Variance,
4046 APHD.Invoice_Base_Qty_Variance,
4047 APHD.Amount_Variance,
4048 APHD.Invoice_Base_Amt_Variance,
4049 FND_GLOBAL.User_ID,
4050 SYSDATE,
4051 SYSDATE,
4052 FND_GLOBAL.User_ID,
4053 FND_GLOBAL.User_ID,
4054 FND_GLOBAL.Prog_Appl_ID,
4055 FND_GLOBAL.Conc_Program_ID,
4056 NULL,
4057 SYSDATE,
4058 FND_GLOBAL.Conc_Request_ID
4059 FROM AP_Payment_Hist_Dists APHD,
4060 AP_Payment_History_All APH -- 6804379
4061 WHERE APH.Check_ID = p_xla_event_rec.Source_ID_Int_1 -- Bug 6856694
4062 AND NVL(APH.Related_Event_ID, APH.Accounting_Event_ID) = p_related_event_id
4063 AND APHD.Payment_History_ID = APH.Payment_History_ID
4064 AND NVL(APHD.Reversal_Flag,'N') <> 'Y'
4065 AND NOT EXISTS (SELECT 'Reversal Payment Dists'
4066 FROM AP_Payment_Hist_Dists APHD1
4067 WHERE APHD1.Reversed_Pay_Hist_Dist_ID
4068 = APHD.Payment_Hist_Dist_ID
4069 -- Bug 6856694
4070 AND APHD1.Invoice_Distribution_ID
4071 = APHD.Invoice_Distribution_ID);
4072
4073 END IF;
4074
4075 ELSE
4076
4077 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4078 l_log_msg := 'Reversing based on invoice_distribution_id '||
4079 p_invoice_dist_id;
4080 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4081 END IF;
4082
4083 INSERT INTO ap_payment_hist_dists
4084 (Payment_Hist_Dist_ID,
4085 Accounting_Event_ID,
4086 Amount,
4087 Pay_Dist_Lookup_Code,
4088 Payment_History_ID,
4089 Invoice_Distribution_ID,
4090 Invoice_Payment_ID,
4091 Bank_Curr_Amount,
4092 Cleared_Base_Amount,
4093 Invoice_Dist_Amount,
4094 Invoice_Dist_Base_Amount,
4095 Invoice_Adjustment_Event_ID,
4096 Matured_Base_Amount,
4097 Paid_Base_Amount,
4098 Reversal_Flag,
4099 Reversed_Pay_Hist_Dist_ID,
4100 AWT_Related_ID,
4101 PA_Addition_Flag,
4102 Quantity_Variance,
4103 Invoice_Base_Qty_Variance,
4104 Amount_Variance,
4105 Invoice_Base_Amt_Variance,
4106 Created_By,
4107 Creation_Date,
4108 Last_Update_Date,
4109 Last_Updated_By,
4110 Last_Update_Login,
4111 Program_Application_ID,
4112 Program_ID,
4113 Program_Login_ID,
4114 Program_Update_Date,
4115 Request_ID
4116 )
4117 SELECT AP_Payment_Hist_Dists_S.nextval,
4118 p_xla_event_rec.event_id,
4119 -1 * APHD.Amount,
4120 APHD.Pay_Dist_Lookup_Code,
4121 p_pay_hist_rec.Payment_History_ID,
4122 p_inv_dist_rec.Invoice_Distribution_ID, -- Bug 6887295
4123 APHD.Invoice_Payment_ID,
4124 -1 * APHD.Bank_Curr_Amount,
4125 -1 * APHD.Cleared_Base_Amount,
4126 -1 * APHD.Invoice_Dist_Amount,
4127 -1 * APHD.Invoice_Dist_Base_Amount,
4128 p_pay_hist_rec.Invoice_Adjustment_Event_ID,
4129 -1 * APHD.Matured_Base_Amount,
4130 -1 * APHD.Paid_Base_Amount,
4131 'Y',
4132 APHD.Payment_Hist_Dist_ID,
4133 APHD.AWT_Related_ID,
4134 'N',
4135 APHD.Quantity_Variance,
4136 APHD.Invoice_Base_Qty_Variance,
4137 APHD.Amount_Variance,
4138 APHD.Invoice_Base_Amt_Variance,
4139 FND_GLOBAL.User_ID,
4140 SYSDATE,
4141 SYSDATE,
4142 FND_GLOBAL.User_ID,
4143 FND_GLOBAL.User_ID,
4144 FND_GLOBAL.Prog_Appl_ID,
4145 FND_GLOBAL.Conc_Program_ID,
4146 NULL,
4147 SYSDATE,
4148 FND_GLOBAL.Conc_Request_ID
4149 FROM AP_Payment_Hist_Dists APHD,
4150 AP_Payment_History_All APH
4151 WHERE APHD.Invoice_Distribution_ID = p_invoice_dist_id
4152 AND APHD.Payment_History_ID = APH.Payment_History_ID
4153 AND APH.Check_ID = p_xla_event_rec.source_id_int_1 -- Bug 6887295
4154 AND APH.Related_Event_ID = p_pay_hist_rec.related_event_id;
4155
4156 END IF;
4157
4158
4159 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4160 l_log_msg := 'Done reversing the payment dists';
4161 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4162 END IF;
4163
4164
4165 -- Bug 6839052. Payment Cancellation cannot account for upgraded payments
4166 -- since the amounts are not populated in the pay dists table or in the
4167 -- xla distribution links table and XLA depends on these amounts for
4168 -- creating reversal accounting.
4169 IF p_xla_event_rec.event_type_code IN
4170 ('PAYMENT CANCELLED','REFUND CANCELLED',
4171 'PAYMENT MATURITY REVERSED', 'PAYMENT UNCLEARED',
4172 'MANUAL PAYMENT ADJUSTED') THEN
4173
4174 IF p_xla_event_rec.event_type_code IN ('PAYMENT CANCELLED',
4175 'MANUAL PAYMENT ADJUSTED') THEN
4176 l_transaction_type := 'PAYMENT CREATED';
4177 ELSIF p_xla_event_rec.event_type_code = 'REFUND CANCELLED' THEN
4181 ELSIF p_xla_event_rec.event_type_code = 'PAYMENT MATURITY REVERSED' THEN
4178 l_transaction_type := 'REFUND RECORDED';
4179 ELSIF p_xla_event_rec.event_type_code = 'PAYMENT UNCLEARED' THEN
4180 l_transaction_type := 'PAYMENT CLEARING';
4182 l_transaction_type := 'PAYMENT MATURITY';
4183 END IF;
4184
4185 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4186 l_log_msg := 'Transaction Type based on the event type is '||
4187 l_transaction_type;
4188 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4189 END IF;
4190
4191 BEGIN
4192 SELECT Payment_History_ID,
4193 Accounting_Event_ID,
4194 NVL(Historical_Flag, 'N')
4195 INTO l_payment_history_id,
4196 l_accounting_event_id,
4197 l_historical_flag
4198 FROM ap_payment_history_all APH
4199 WHERE APH.Check_ID = p_xla_event_rec.source_id_int_1
4200 AND APH.Transaction_Type = l_transaction_type
4201 AND APH.Posted_Flag = 'Y';
4202
4203
4204 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4205 l_log_msg := 'Accounting Event ID of the related event '||
4206 l_accounting_event_id;
4207 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4208 END IF;
4209
4210 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4211 l_log_msg := 'Historical Flag of the related event '||
4212 l_historical_flag;
4213 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4214 END IF;
4215
4216 IF l_historical_flag = 'Y' THEN
4217
4218 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4219 l_log_msg := 'Calling procedure Upg_Dist_Link_Insert';
4220 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4221 END IF;
4222
4223 Upg_Dist_Links_Insert
4224 (p_xla_event_rec,
4225 l_payment_history_id,
4226 l_accounting_event_id,
4227 l_curr_calling_sequence);
4228
4229 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4230 l_log_msg := 'Procedure Upg_Dist_Link_Insert complete';
4231 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4232 END IF;
4233
4234 END IF;
4235
4236 EXCEPTION
4237 WHEN others THEN
4238 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4239 l_log_msg := 'Procedure Pay_Dist_Reverse raised exceptions';
4240 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4241 END IF;
4242 NULL;
4243 END;
4244
4245 END IF;
4246
4247 -- Logging Infra: Procedure level
4248 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4249 l_log_msg := 'End of procedure '|| l_procedure_name;
4250 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
4251 END IF;
4252
4253
4254 EXCEPTION
4255 WHEN OTHERS THEN
4256 IF (SQLCODE <> -20001) THEN
4257 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
4258 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
4259 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
4260 END IF;
4261 APP_EXCEPTION.RAISE_EXCEPTION;
4262
4263 END Pay_Dist_Reverse;
4264
4265
4266 ----------------------------------------------------------------------------------
4267 -- PROCEDURE Pay_Dist_Insert
4268 -- This procedure is used to insert the payment hist distributions into the
4269 -- ap_payment_hist_dists table
4270 ----------------------------------------------------------------------------------
4271
4272 PROCEDURE Pay_Dist_Insert
4273 (P_PD_Rec IN AP_PAYMENT_HIST_DISTS%ROWTYPE
4274 ,P_Calling_Sequence IN VARCHAR2
4275 ) IS
4276
4277 l_curr_calling_sequence VARCHAR2(2000);
4278
4279 -- Logging Infra:
4280 l_procedure_name CONSTANT VARCHAR2(30) := 'Pay_Dist_Insert';
4281 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4282
4283
4284 BEGIN
4285
4286 l_curr_calling_sequence := 'AP_ACCTG_PAY_DIST_PKG.Pay_Dist_Insert<- ' ||
4287 P_Calling_Sequence;
4288
4289
4290 -- Logging Infra: Procedure level
4291 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4292 l_log_msg := 'Begin of procedure '|| l_procedure_name;
4293 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
4294 END IF;
4295
4296
4297 INSERT INTO AP_Payment_Hist_Dists
4298 (Payment_Hist_Dist_ID,
4299 Accounting_Event_ID,
4300 Amount,
4301 Pay_Dist_Lookup_Code,
4302 Payment_History_ID,
4303 Invoice_Distribution_ID,
4304 Invoice_Payment_ID,
4305 Bank_Curr_Amount,
4306 Cleared_Base_Amount,
4307 Invoice_Dist_Amount,
4308 Invoice_Dist_Base_Amount,
4309 Invoice_Adjustment_Event_ID,
4310 Matured_Base_Amount,
4311 Paid_Base_Amount,
4312 Reversal_Flag,
4313 Reversed_Pay_Hist_Dist_ID,
4314 AWT_Related_ID,
4315 PA_Addition_Flag,
4316 Quantity_Variance,
4317 Invoice_Base_Qty_Variance,
4318 Amount_Variance,
4319 Invoice_Base_Amt_Variance,
4320 Created_By,
4321 Creation_Date,
4322 Last_Update_Date,
4326 Program_ID,
4323 Last_Updated_By,
4324 Last_Update_Login,
4325 Program_Application_ID,
4327 Program_Login_ID,
4328 Program_Update_Date,
4329 Request_ID
4330 )
4331 VALUES (AP_Payment_Hist_Dists_S.nextval,
4332 P_PD_Rec.Accounting_Event_ID,
4333 P_PD_Rec.Amount,
4334 P_PD_Rec.Pay_Dist_Lookup_Code,
4335 P_PD_Rec.Payment_History_ID,
4336 P_PD_Rec.Invoice_Distribution_ID,
4337 P_PD_Rec.Invoice_Payment_ID,
4338 P_PD_Rec.Bank_Curr_Amount,
4339 P_PD_Rec.Cleared_Base_Amount,
4340 P_PD_Rec.Invoice_Dist_Amount,
4341 P_PD_Rec.Invoice_Dist_Base_Amount,
4342 P_PD_Rec.Invoice_Adjustment_Event_ID,
4343 P_PD_Rec.Matured_Base_Amount,
4344 P_PD_Rec.Paid_Base_Amount,
4345 P_PD_Rec.Reversal_Flag,
4346 P_PD_Rec.Reversed_Pay_Hist_Dist_ID,
4347 P_PD_Rec.AWT_Related_ID,
4348 'N',
4349 P_PD_Rec.Quantity_Variance,
4350 P_PD_Rec.Invoice_Base_Qty_Variance,
4351 P_PD_Rec.Amount_Variance,
4352 P_PD_Rec.Invoice_Base_Amt_Variance,
4353 FND_GLOBAL.User_ID,
4354 SYSDATE,
4355 SYSDATE,
4356 FND_GLOBAL.User_ID,
4357 FND_GLOBAL.User_ID,
4358 FND_GLOBAL.Prog_Appl_ID,
4359 FND_GLOBAL.Conc_Program_ID,
4360 NULL,
4361 SYSDATE,
4362 FND_GLOBAL.Conc_Request_ID
4363 );
4364
4365 -- Logging Infra: Procedure level
4366 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4367 l_log_msg := 'End of procedure '|| l_procedure_name;
4368 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
4369 END IF;
4370
4371
4372 EXCEPTION
4373 WHEN OTHERS THEN
4374 IF (SQLCODE <> -20001) THEN
4375 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
4376 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
4377 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
4378 END IF;
4379 APP_EXCEPTION.RAISE_EXCEPTION;
4380
4381 END Pay_Dist_Insert;
4382
4383
4384
4385 -- Bug 6839052. Added this procedure to delete and recreate the distribution
4386 -- links by prorating the line amount to all the payment distributions
4387 -- so that the transaction and distribution reversals use these new
4388 -- distribution links with the right amounts
4389 ----------------------------------------------------------------------------------
4390 -- PROCEDURE Upg_Dist_Links_Insert
4391 -- This procedure is used to insert the payment hist distributions into the
4392 -- ap_payment_hist_dists table
4393 ----------------------------------------------------------------------------------
4394
4395 PROCEDURE Upg_Dist_Links_Insert
4396 (p_xla_event_rec IN ap_accounting_pay_pkg.r_xla_event_info
4397 ,p_payment_history_id IN NUMBER
4398 ,p_accounting_event_id IN NUMBER
4399 ,p_calling_sequence IN VARCHAR2
4400 ) IS
4401
4402 l_curr_calling_sequence VARCHAR2(2000);
4403
4404 -- Logging Infra:
4405 l_procedure_name CONSTANT VARCHAR2(30) := 'Upg_Dist_Links_Insert';
4406 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4407
4408
4409 BEGIN
4410
4411 l_curr_calling_sequence := 'AP_ACCTG_PAY_DIST_PKG.Upg_Dist_Links_Insert<- ' ||
4412 P_Calling_Sequence;
4413
4414
4415 -- Logging Infra: Procedure level
4416 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4417 l_log_msg := 'Begin of procedure '|| l_procedure_name;
4418 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||
4419 '.begin', l_log_msg);
4420 END IF;
4421
4422 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4423 l_log_msg := 'Deleting xla_distribution_links';
4424 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4425 END IF;
4426
4427 DELETE FROM xla_distribution_links
4428 WHERE application_id = 200
4429 AND ae_header_id IN
4430 (SELECT ae_header_id
4431 FROM xla_ae_headers aeh,
4432 ap_payment_history_all aph
4433 WHERE aeh.event_id = aph.accounting_event_id
4434 AND aph.accounting_event_id = p_accounting_event_id
4435 AND aph.check_id = p_xla_event_rec.source_id_int_1
4436 AND aph.historical_flag = 'Y'
4437 AND aeh.upg_batch_id IS NOT NULL)
4438 AND upg_batch_id IS NOT NULL;
4439
4440
4441 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4442 l_log_msg := 'Inserting xla_distribution_links for event '||
4443 p_accounting_event_id;
4444 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4445 END IF;
4446
4447
4448 INSERT INTO XLA_Distribution_Links t1
4449 (APPLICATION_ID,
4450 EVENT_ID,
4451 AE_HEADER_ID,
4452 AE_LINE_NUM,
4453 SOURCE_DISTRIBUTION_TYPE,
4454 SOURCE_DISTRIBUTION_ID_NUM_1,
4455 UNROUNDED_ENTERED_CR,
4456 UNROUNDED_ENTERED_DR,
4457 UNROUNDED_ACCOUNTED_CR,
4458 UNROUNDED_ACCOUNTED_DR,
4459 REF_AE_HEADER_ID,
4460 ACCOUNTING_LINE_CODE,
4461 ACCOUNTING_LINE_TYPE_CODE,
4462 MERGE_DUPLICATE_CODE,
4463 TEMP_LINE_NUM,
4464 REF_EVENT_ID,
4465 UPG_BATCH_ID,
4466 LINE_DEFINITION_OWNER_CODE,
4467 LINE_DEFINITION_CODE,
4468 EVENT_CLASS_CODE,
4469 EVENT_TYPE_CODE,
4470 --- changed for bug#7293021 start
4474 APPLIED_TO_DISTRIBUTION_TYPE
4471 APPLIED_TO_APPLICATION_ID,
4472 APPLIED_TO_ENTITY_ID,
4473 APPLIED_TO_DIST_ID_NUM_1,
4475 --- changed for bug#7293021 end
4476 )
4477 SELECT 200,
4478 Accounting_Event_ID,
4479 AE_Header_ID,
4480 AE_Line_Num,
4481 'AP_PMT_DIST',
4482 Source_Distribution_ID_Num_1,
4483 (CASE
4484 WHEN Line_Entered_Cr IS NOT NULL THEN
4485 Decode(Rank_Num, Dist_Count, (Line_Entered_Amt - Sum_Entered_Amt) +
4486 Entered_Amt, Entered_Amt)
4487 ELSE NULL
4488 END),
4489 (CASE
4490 WHEN Line_Entered_Dr IS NOT NULL THEN
4491 Decode(Rank_Num, Dist_Count, (Line_Entered_Amt - Sum_Entered_Amt) +
4492 Entered_Amt, Entered_Amt)
4493 ELSE NULL
4494 END),
4495 (CASE
4496 WHEN Line_Accounted_Cr IS NOT NULL THEN
4497 Decode(Rank_Num, Dist_Count, (Line_Accounted_Amt - Sum_Accounted_Amt) +
4498 Accounted_Amt, Accounted_Amt)
4499 ELSE NULL
4500 END),
4501 (CASE
4502 WHEN Line_Accounted_Dr IS NOT NULL THEN
4503 Decode(Rank_Num, Dist_Count, (Line_Accounted_Amt - Sum_Accounted_Amt) +
4504 Accounted_Amt, Accounted_Amt)
4505 ELSE NULL
4506 END),
4507 Ref_AE_Header_ID,
4508 (CASE
4509 WHEN Payment_Type_Flag = 'R' THEN
4510 DECODE(Accounting_Class_Code,
4511 'CASH_CLEARING', 'AP_CASH_CLEAR_REF', 'CASH', 'AP_CASH_REF',
4512 'ACCRUAL', 'AP_ACCRUAL_REF', 'DISCOUNT', 'AP_DISCOUNT_ACCR_REF',
4513 'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_REF',
4514 'ROUNDING', 'AP_FINAL_PMT_ROUNDING_REF',
4515 'GAIN', 'AP_GAIN_REF', 'FREIGHT', 'AP_FREIGHT_EXPENSE_REF',
4516 'IPV', 'AP_INV_PRICE_VAR_REF', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_REF',
4517 'LOSS', 'AP_LOSS_REF', 'LIABILITY', 'AP_LIAB_REF',
4518 'NRTAX', 'AP_NON_RECOV_TAX_REF',
4519 'PREPAID_EXPENSE', 'AP_PREPAID_EXP_REF', 'RTAX','AP_RECOV_TAX_REF',
4520 'AWT', 'AP_WITHHOLD_TAX_ACCR_REF')
4521 WHEN Transaction_Type = 'PAYMENT MATURITY' THEN
4522 DECODE(Accounting_Class_Code, 'FUTURE_DATED_PMT', 'AP_FUTURE_DATED_PMT_MAT',
4523 'CASH_CLEARING', 'AP_CASH_CLEAR_PMT_MAT',
4524 'CASH', 'AP_CASH_PMT_MAT', 'GAIN', 'AP_GAIN_PMT_MAT',
4525 'LOSS', 'AP_LOSS_PMT_MAT', 'ROUNDING', 'AP_FUTURE_PMT_ROUNDING_MAT')
4526 WHEN Transaction_Type IN ('PAYMENT CREATED', 'PAYMENT CANCELLED') THEN
4527 DECODE(Accounting_Class_Code, 'FUTURE_DATED_PMT', 'AP_FUTURE_DATED_PMT',
4528 'CASH_CLEARING', 'AP_CASH_CLEAR_PMT', 'CASH', 'AP_CASH_PMT',
4529 'ACCRUAL', 'AP_ACCRUAL_PMT', 'DISCOUNT', 'AP_DISCOUNT_ACCR_PMT',
4530 'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_PMT',
4531 'ROUNDING', 'AP_FINAL_PMT_ROUNDING_PMT',
4532 'GAIN', 'AP_GAIN_PMT', 'FREIGHT', 'AP_FREIGHT_EXPENSE_PMT',
4533 'IPV', 'AP_INV_PRICE_VAR_PMT', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_PMT',
4534 'LOSS', 'AP_LOSS_PMT', 'LIABILITY', 'AP_LIAB_PMT',
4535 'NRTAX', 'AP_NON_RECOV_TAX_PMT',
4536 'PREPAID_EXPENSE', 'AP_PREPAID_EXP_PMT', 'RTAX','AP_RECOV_TAX_PMT',
4537 'AWT', 'AP_WITHHOLD_TAX_ACCR_PMT')
4538 WHEN Transaction_Type IN ('PAYMENT CLEARING', 'PAYMENT UNCLEARING') THEN
4539 DECODE(Accounting_Class_Code, 'BANK_CHG', 'AP_BANK_CHARGES_CLEAR',
4540 'CASH_CLEARING', 'AP_CASH_CLEAR_CLEAR', 'CASH', 'AP_CASH_CLEAR',
4541 'ACCRUAL', 'AP_ACCRUAL_CLEAR', 'DISCOUNT', 'AP_DISCOUNT_ACCR_CLEAR',
4542 'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_CLEAR',
4543 'BANK_ERROR', 'AP_BANK_ERROR_CLEAR',
4544 'ROUNDING', 'AP_FINAL_PMT_ROUNDING_CLEAR',
4545 'GAIN', 'AP_GAIN_PMT_CLEAR', 'FREIGHT', 'AP_FREIGHT_EXPENSE_CLEAR',
4546 'IPV', 'AP_INV_PRICE_VAR_CLEAR', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_CLEAR',
4547 'LOSS', 'AP_LOSS_PMT_CLEAR', 'LIABILITY', 'AP_LIAB_CLEAR',
4548 'NRTAX', 'AP_NON_RECOV_TAX_CLEAR', 'RTAX','AP_RECOV_TAX_CLEAR',
4549 'AWT', 'AP_WITHHOLD_TAX_ACCR_CLEAR')
4550 END),
4551 'S',
4552 'N',
4553 Row_Number() OVER (PARTITION BY AE_Header_ID
4554 ORDER BY AE_Line_Num,
4555 Invoice_Distribution_ID,
4556 Invoice_Payment_ID,
4557 Payment_History_ID) Temp_Line_Num,
4558 Accounting_Event_ID,
4559 Upg_Batch_ID,
4560 'S',
4561 'ACCRUAL_PAYMENTS_ALL',
4562 'PAYMENTS',
4563 'PAYMENTS_ALL',
4564 -- changed for bug#7293021 start
4565 DECODE(Accounting_Class_Code, 'LIABILITY' ,200, null),
4566 DECODE(Accounting_Class_Code, 'LIABILITY' ,aid_Entity_id, null),
4567 DECODE(Accounting_Class_Code, 'LIABILITY' ,Invoice_Distribution_ID, null),
4568 'AP_INV_DIST'
4569 -- changed for bug#7293021 end
4570 FROM (
4571 SELECT Accounting_Event_ID,
4572 AE_Header_ID,
4573 AE_Line_Num,
4574 Line_Entered_Cr,
4575 Line_Entered_Dr,
4576 Line_Accounted_Cr,
4577 Line_Accounted_Dr,
4578 Invoice_Distribution_ID,
4579 Invoice_Payment_ID,
4580 Payment_History_ID,
4584 Line_Entered_Amt,
4581 Upg_Batch_ID,
4582 Base_Currency_Code,
4583 Source_Distribution_ID_Num_1,
4585 Line_Accounted_Amt,
4586 DECODE(FC.Minimum_Accountable_Unit, NULL,
4587 ROUND((Line_Accounted_Amt * Dist_Base_Amount
4588 / DECODE(PDivisor_Acct_Amt, 0, 1, PDivisor_Acct_Amt)),
4589 FC.Precision),
4590 ROUND((Line_Accounted_Amt * Dist_Base_Amount
4591 / DECODE(PDivisor_Acct_Amt, 0, 1, PDivisor_Acct_Amt))
4592 /FC.Minimum_Accountable_Unit) * FC.Minimum_Accountable_Unit) Accounted_Amt,
4593 DECODE(FC.Minimum_Accountable_Unit, NULL,
4594 ROUND((Line_Entered_Amt * Dist_Amount
4595 / DECODE(PDivisor_Ent_Amt, 0 ,1, PDivisor_Ent_Amt)), FC.Precision),
4596 ROUND((Line_Entered_Amt * Dist_Amount
4597 / DECODE(PDivisor_Acct_Amt, 0 ,1, PDivisor_Ent_Amt))
4598 /FC.Minimum_Accountable_Unit) * FC.Minimum_Accountable_Unit) Entered_Amt,
4599 Dist_Count,
4600 Rank_Num,
4601 SUM(DECODE(FC.Minimum_Accountable_Unit, NULL,
4602 ROUND((Line_Accounted_Amt * Dist_Base_Amount
4603 / DECODE(PDivisor_Acct_Amt, 0, 1, PDivisor_Acct_Amt)),
4604 FC.Precision),
4605 ROUND((Line_Accounted_Amt * Dist_Base_Amount
4606 / DECODE(PDivisor_Acct_Amt, 0, 1, PDivisor_Acct_Amt))
4607 /FC.Minimum_Accountable_Unit) * FC.Minimum_Accountable_Unit))
4608 OVER (PARTITION BY Check_ID, Part_Key1, Part_Key2, AE_Line_Num)
4609 Sum_Accounted_Amt,
4610 SUM(DECODE(FC.Minimum_Accountable_Unit, NULL,
4611 ROUND((Line_Entered_Amt * Dist_Amount
4612 / DECODE(PDivisor_Ent_Amt, 0 ,1, PDivisor_Ent_Amt)), FC.Precision),
4613 ROUND((Line_Entered_Amt * Dist_Amount
4614 / DECODE(PDivisor_Ent_Amt, 0 ,1, PDivisor_Ent_Amt))
4615 /FC.Minimum_Accountable_Unit) * FC.Minimum_Accountable_Unit))
4616 OVER (PARTITION BY Check_ID, Part_Key1, Part_Key2, AE_Line_Num) Sum_Entered_Amt,
4617 Ref_AE_Header_ID,
4618 Payment_Type_Flag,
4619 Transaction_Type,
4620 Accounting_Class_Code,
4621 aid_Entity_id -- changed for bug#7293021
4622 FROM (
4623 SELECT AC.Check_ID Check_ID,
4624 AEH.Event_ID Accounting_Event_ID,
4625 AEH.AE_Header_ID AE_Header_ID,
4626 AEL.AE_Line_Num AE_Line_Num,
4627 AEL.Entered_Cr Line_Entered_Cr,
4628 AEL.Entered_Dr Line_Entered_Dr,
4629 AEL.Accounted_Cr Line_Accounted_Cr,
4630 AEL.Accounted_Dr Line_Accounted_Dr,
4631 APHD.Invoice_Distribution_ID Invoice_Distribution_ID,
4632 APHD.Invoice_Payment_ID Invoice_Payment_ID,
4633 APHD.Payment_History_ID Payment_History_ID,
4634 AEL.Upg_Batch_ID Upg_Batch_ID,
4635 ASP.Base_Currency_Code Base_Currency_Code,
4636 APHD.Payment_Hist_Dist_ID Source_Distribution_ID_Num_1,
4637 NVL(AEL.Entered_Cr, AEL.Entered_Dr) Line_Entered_Amt,
4638 NVL(AEL.Accounted_Cr, AEL.Accounted_Dr) Line_Accounted_Amt,
4639 AID.Amount Dist_Amount,
4640 NVL(AID.Base_Amount, AID.Amount) Dist_Base_Amount,
4641 COUNT(*) OVER (PARTITION BY AI.Invoice_ID, AID1.Invoice_Distribution_ID,
4642 AEL.AE_Line_Num) Dist_Count,
4643 RANK() OVER (PARTITION BY AI.Invoice_ID, AID1.Invoice_Distribution_ID,
4644 AEL.AE_Line_Num
4645 ORDER BY AID.Amount, AID.Distribution_Line_Number) Rank_Num,
4646 AID1.Amount PDivisor_Ent_Amt,
4647 NVL(AID1.Base_Amount, AID1.Amount) PDivisor_Acct_Amt,
4648 AI.Invoice_ID Part_Key1,
4649 AID1.Invoice_Distribution_ID Part_Key2,
4650 AEH.AE_Header_ID Ref_AE_Header_ID,
4651 AC.Payment_Type_Flag Payment_Type_Flag,
4652 APH.Transaction_Type Transaction_Type,
4653 AEL.Accounting_Class_Code Accounting_Class_Code,
4654 aid_xe.entity_id aid_Entity_id
4655 FROM AP_Checks_All AC,
4656 AP_System_Parameters_All ASP,
4657 XLA_Transaction_Entities_Upg XTE,
4658 XLA_Events XLE,
4659 AP_Payment_History_All APH,
4660 XLA_AE_Headers AEH,
4661 XLA_AE_Lines AEL,
4662 AP_Inv_Dists_Source AID1,
4663 AP_Invoices_All AI,
4664 AP_Invoice_Distributions_All AID,
4665 AP_Payment_Hist_Dists APHD,
4666 xla_events aid_xe -- changed for bug#7293021
4667 WHERE AC.Check_ID = p_xla_event_rec.source_id_int_1
4668 AND AC.Org_ID = ASP.Org_ID
4669 AND XLE.Event_ID = p_accounting_event_id
4670 AND ASP.Set_Of_Books_ID = XTE.Ledger_ID
4671 AND XTE.Entity_Code = 'AP_PAYMENTS'
4672 AND AC.Check_ID = nvl(XTE.Source_ID_Int_1,-99)
4673 AND XTE.Application_ID = 200
4674 AND XTE.Entity_ID = XLE.Entity_ID
4675 AND XLE.Application_ID = 200
4676 AND XLE.Event_ID = AEH.Event_ID
4677 AND XLE.Upg_Batch_ID IS NOT NULL
4678 AND AEH.Application_ID = 200
4679 AND AEL.AE_Header_ID = AEH.AE_Header_ID
4680 AND AEL.Application_ID = 200
4681 AND XLE.Event_ID = APH.Accounting_Event_ID
4682 AND APH.Check_ID = AC.Check_ID
4683 AND APH.Payment_History_ID = p_payment_history_id
4684 AND APH.Payment_History_ID = APHD.Payment_History_ID
4685 AND APHD.Invoice_Payment_ID = DECODE(AEL.Source_Table, 'AP_INVOICE_PAYMENTS',
4686 AEL.Source_ID, APHD.Invoice_Payment_ID)
4687 AND APHD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
4688 AND AEL.Account_Overlay_Source_ID = AID1.Invoice_Distribution_ID
4689 AND AID1.Invoice_ID = AI.Invoice_ID
4690 AND AID1.Invoice_Distribution_ID = AID.Old_Distribution_ID
4691 AND aid_xe.application_id = 200 --- changed for bug#7293021
4692 AND aid_xe.event_id = aid.accounting_event_id
4693 UNION ALL
4694 SELECT AC.Check_ID Check_ID,
4695 AEH.Event_ID Accounting_Event_ID,
4696 AEH.AE_Header_ID AE_Header_ID,
4697 AEL.AE_Line_Num AE_Line_Num,
4698 AEL.Entered_Cr Line_Entered_Cr,
4699 AEL.Entered_Dr Line_Entered_Dr,
4700 AEL.Accounted_Cr Line_Accounted_Cr,
4701 AEL.Accounted_Dr Line_Accounted_Dr,
4702 APHD.Invoice_Distribution_ID Invoice_Distribution_ID,
4703 APHD.Invoice_Payment_ID Invoice_Payment_ID,
4704 APHD.Payment_History_ID Payment_History_ID,
4705 AEL.Upg_Batch_ID Upg_Batch_ID,
4706 ASP.Base_Currency_Code Base_Currency_Code,
4707 APHD.Payment_Hist_Dist_ID Source_Distribution_ID_Num_1,
4708 NVL(AEL.Entered_Cr, AEL.Entered_Dr) Line_Entered_Amt,
4709 NVL(AEL.Accounted_Cr, AEL.Accounted_Dr) Line_Accounted_Amt,
4710 AID.Amount Dist_Amount,
4711 NVL(AID.Base_Amount, AID.Amount) Dist_Base_Amount,
4712 COUNT(*) OVER (PARTITION BY AC.Check_ID,
4713 DECODE(AEL.Source_Table, 'AP_CHECKS', 1, AI.Invoice_ID),
4714 AEL.AE_Line_Num) Dist_Count,
4715 RANK() OVER (PARTITION BY AC.Check_ID,
4716 DECODE(AEL.Source_Table, 'AP_CHECKS', AC.Check_ID, AI.Invoice_ID),
4717 AEL.AE_Line_Num
4718 ORDER BY AID.Amount, AID.Distribution_Line_Number) Rank_Num,
4719 SUM(AID.Amount)
4720 OVER (PARTITION BY AC.Check_ID,
4721 DECODE(AEL.Source_Table, 'AP_CHECKS', 1, AI.Invoice_ID),
4722 AEL.AE_Line_Num) PDivisor_Ent_Amt,
4723 SUM(NVL(AID.Base_Amount, AID.Amount))
4724 OVER (PARTITION BY AC.Check_ID,
4725 DECODE(AEL.Source_Table, 'AP_CHECKS', 1, AI.Invoice_ID),
4726 AEL.AE_Line_Num) PDivisor_Acct_Amt,
4727 DECODE(AEL.Source_Table, 'AP_CHECKS', 1, AI.Invoice_ID) Part_Key1,
4728 1 Part_Key2,
4729 AEH.AE_Header_ID Ref_AE_Header_ID,
4730 AC.Payment_Type_Flag Payment_Type_Flag,
4731 APH.Transaction_Type Transaction_Type,
4732 AEL.Accounting_Class_Code Accounting_Class_Code,
4733 aid_xe.entity_id aid_Entity_id --- changed for bug#7293021
4734 FROM AP_Checks_All AC,
4735 AP_System_Parameters_All ASP,
4736 XLA_Transaction_Entities_Upg XTE,
4737 XLA_Events XLE,
4738 AP_Payment_History_All APH,
4739 XLA_AE_Headers AEH,
4740 XLA_AE_Lines AEL,
4741 AP_Payment_Hist_Dists APHD,
4742 AP_Invoice_Distributions_All AID,
4743 AP_Invoices_All AI,
4744 xla_events aid_xe -- changed for bug#7293021
4745 WHERE AC.Check_ID = p_xla_event_rec.source_id_int_1
4746 AND AC.Org_ID = ASP.Org_ID
4747 AND XLE.Event_ID = p_accounting_event_id
4748 AND ASP.Set_Of_Books_ID = XTE.Ledger_ID
4749 AND XTE.Entity_Code = 'AP_PAYMENTS'
4750 AND AC.Check_ID = nvl(XTE.Source_ID_Int_1,-99)
4751 AND XTE.Application_ID = 200
4752 AND XTE.Entity_ID = XLE.Entity_ID
4753 AND XLE.Application_ID = 200
4754 AND XLE.Event_ID = AEH.Event_ID
4755 AND XLE.Upg_Batch_ID IS NOT NULL
4756 AND AEH.Application_ID = 200
4757 AND AEL.AE_Header_ID = AEH.AE_Header_ID
4758 AND AEL.Application_ID = 200
4759 AND XLE.Event_ID = APH.Accounting_Event_ID
4760 AND APH.Check_ID = AC.Check_ID
4761 AND APH.Payment_History_ID = p_payment_history_id
4762 AND APH.Payment_History_ID = APHD.Payment_History_ID
4763 AND APHD.Invoice_Payment_ID = DECODE(AEL.Source_Table, 'AP_INVOICE_PAYMENTS',
4764 AEL.Source_ID, APHD.Invoice_Payment_ID)
4765 AND AEL.Account_Overlay_Source_ID IS NULL
4766 AND APHD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
4767 AND AI.Invoice_ID = AID.Invoice_ID
4768 AND aid_xe.application_id = 200 --- changed for bug#7293021
4769 AND aid_xe.event_id = aid.accounting_event_id
4770 ) ADL,
4771 FND_Currencies FC
4772 WHERE FC.Currency_Code = ADL.Base_Currency_Code);
4773
4774
4775 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4776 l_log_msg := 'Done inserting into xla_distribution_links';
4777 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,
4778 l_log_msg);
4779 END IF;
4780
4781
4782 -- Logging Infra: Procedure level
4783 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4784 l_log_msg := 'End of procedure '|| l_procedure_name;
4785 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end',
4786 l_log_msg);
4787 END IF;
4788
4789
4790 EXCEPTION
4791 WHEN OTHERS THEN
4792 IF (SQLCODE <> -20001) THEN
4793 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
4794 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
4795 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
4796 END IF;
4797 APP_EXCEPTION.RAISE_EXCEPTION;
4798
4799 END Upg_Dist_Links_Insert;
4800
4801
4802 END AP_ACCTG_PAY_DIST_PKG;