[Home] [Help]
PACKAGE BODY: APPS.AP_ACCTG_PREPAY_DIST_PKG
Source
1 PACKAGE BODY AP_ACCTG_PREPAY_DIST_PKG AS
2 /* $Header: appredib.pls 120.20.12010000.6 2009/01/09 07:08:14 mkmeda ship $ */
3
4 G_Total_Dist_Amt NUMBER := 0;
5 G_Total_Prorated_Amt NUMBER := 0;
6 G_Total_Tax_Diff_Amt NUMBER := 0;
7
8 -- Logging Infra
9 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
10 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
11 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
12 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
13 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
14 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
15 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
16 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'AP.PLSQL.AP_ACCTG_PREPAY_DIST_PKG.';
17 -- Logging Infra
18
19
20 -------------------------------------------------------------------------------
21 -- PROCEDURE UPDATE_GAIN_LOSS_IND
22 -- The purpose of this procedure is to update the gain_loss_indicator on the
23 -- prepay history table based on the exchange rates of prepayment transactions.
24 --
25 --------------------------------------------------------------------------------
26 PROCEDURE Update_Gain_Loss_Ind
27 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
28 ,P_Calling_Sequence IN VARCHAR2
29 ) IS
30
31 l_curr_calling_sequence VARCHAR2(2000);
32
33 -- Logging Infra:
34 l_procedure_name CONSTANT VARCHAR2(30) := 'Update_Gain_Loss_Ind';
35 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
36
37 BEGIN
38
39 l_curr_calling_sequence := 'AP_Acctg_Prepay_Dist_Pkg.Update_Gain_Loss_Ind<- ' ||
40 p_calling_sequence;
41
42
43 -- Logging Infra: Setting up runtime level
44 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
45
46 -- Logging Infra: Procedure level
47 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
48 l_log_msg := 'Begin of procedure '|| l_procedure_name;
49 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
50 END IF;
51
52 UPDATE AP_Prepay_History_All APH
53 SET Gain_Loss_Indicator =
54 (SELECT DECODE(APH.Transaction_Type, 'PREPAYMENT APPLIED',
55 DECODE(SIGN(SUM(APAD.Base_Amount - APAD.Base_Amt_At_Prepay_XRate)),
56 -1, 'G', 1, 'L', NULL),
57 'PREPAYMENT UNAPPLIED',
58 DECODE(SIGN(SUM(APAD.Base_Amount - APAD.Base_Amt_At_Prepay_XRate)),
59 1, 'G', -1, 'L', NULL))
60 FROM AP_Prepay_App_Dists APAD
61 WHERE APAD.Prepay_History_ID = APH.Prepay_History_ID
62 AND APAD.Accounting_Event_ID = p_xla_event_rec.event_id)
63 WHERE APH.Accounting_Event_ID = p_xla_event_rec.event_id;
64
65 -- Logging Infra: Procedure level
66 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
67 l_log_msg := 'End of procedure '|| l_procedure_name;
68 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
69 END IF;
70
71
72 EXCEPTION
73 WHEN OTHERS THEN
74 IF (SQLCODE <> -20001) THEN
75 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
76 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
77 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
78 END IF;
79 APP_EXCEPTION.RAISE_EXCEPTION;
80
81 END Update_Gain_Loss_Ind;
82
83
84 -------------------------------------------------------------------------------
85 -- PROCEDURE Prepay_Hist_Insert
86 -- The purpose of this procedure is to insert the prepayment history info
87 -- into the prepayment history table
88 --
89 --------------------------------------------------------------------------------
90 -- Bug 4996808. Inserting the prepay headers instead of in the accounting events
91 -- procedure
92 PROCEDURE Prepay_Hist_Insert
93 (P_Invoice_ID IN NUMBER
94 ,P_Calling_Sequence IN VARCHAR2
95 ) IS
96
97 l_curr_calling_sequence VARCHAR2(2000);
98 l_line_amount NUMBER;
99 l_transaction_type VARCHAR2(30);
100 l_prepay_invoice_id NUMBER;
101 l_prepay_line_number NUMBER;
102 l_org_id NUMBER;
103 l_invoice_line_number NUMBER;
104 l_accounting_date DATE;
105 l_related_prepay_app_event_id NUMBER;
106 l_group_number NUMBER;
107
108 -- Logging:
109 l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Hist_Insert';
110 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
111
112
113 CURSOR c_prepay_history IS
114 SELECT AIL.Line_Number,
115 AIL.Amount Amount,
116 AIL.Prepay_Invoice_ID,
117 AIL.Prepay_Line_Number,
118 AIL.Org_ID,
119 AID.Accounting_Date,
120 -- 6718967
121 DECODE(NVL(AID.Parent_Reversal_ID,-99), -99, 1, 2) Group_Number
122 FROM AP_Invoice_Lines_ALL AIL,
123 AP_Invoice_Distributions_All AID
124 WHERE AIL.Invoice_ID = p_invoice_id
125 AND AIL.Line_Type_Lookup_Code = 'PREPAY'
126 AND AIL.Invoice_ID = AID.Invoice_ID
127 AND AIL.Line_Number = AID.Invoice_Line_Number
128 AND AID.Accounting_Event_ID IS NULL
129 GROUP BY AIL.Invoice_ID, AIL.Line_Number, AIL.Amount, AIL.Prepay_Invoice_ID,
130 AIL.Prepay_Line_Number, AIL.Org_ID, AID.Accounting_Date,
131 -- 6718967
132 DECODE(NVL(AID.Parent_Reversal_ID,-99), -99, 1, 2)
133 UNION
134 SELECT AID.Invoice_Line_Number,
135 SUM(AID.Amount) Amount,
136 AIL1.Invoice_ID,
137 AIL1.Line_Number,
138 AIL1.Org_ID,
139 AID.Accounting_Date,
140 -- 6718967
141 DECODE(NVL(AID.Parent_Reversal_ID,-99), -99, 1, 2) Group_Number
142 FROM AP_Invoice_Lines AIL,
143 AP_Invoice_Distributions AID,
144 AP_Invoice_Lines AIL1,
145 AP_Invoice_Distributions AID1
146 WHERE AID.Invoice_ID = p_invoice_id
147 AND AID.Line_Type_Lookup_Code = 'PREPAY'
148 AND AID.Invoice_ID = AIL.Invoice_ID
149 AND AID.Invoice_Line_Number = AIL.Line_Number
150 AND AIL.Line_Type_Lookup_Code <> 'PREPAY'
151 AND AID.Accounting_Event_ID IS NULL
152 AND AID.Prepay_Distribution_ID = AID1.Invoice_Distribution_ID
153 AND AIL1.Invoice_ID = AID1.Invoice_ID
154 AND AIL1.Line_Number = AID1.Invoice_Line_Number
155 GROUP BY AIL1.Invoice_ID, AIL1.Line_Number, AIL1.Org_ID,
156 AID.Invoice_Line_Number, AID.Accounting_Date,
157 -- 6718967
158 DECODE(NVL(AID.Parent_Reversal_ID,-99), -99, 1, 2);
159
160
161 BEGIN
162
163 l_curr_calling_sequence := p_calling_sequence ||
164 ' -> AP_ACCTG_PREPAY_DISTS_PKG.PREPAY_HIST_INSERT';
165
166 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
167
168 l_log_msg :='Begin of procedure '||l_procedure_name;
169 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
170 FND_LOG.STRING(G_LEVEL_PROCEDURE,
171 G_MODULE_NAME || l_procedure_name,
172 l_log_msg);
173 END IF;
174
175 OPEN c_prepay_history;
176 LOOP
177 FETCH c_prepay_history INTO l_invoice_line_number,
178 l_line_amount, l_prepay_invoice_id, l_prepay_line_number,
179 l_org_id, l_accounting_date, l_group_number;
180 EXIT WHEN c_prepay_history%NOTFOUND OR
181 c_prepay_history%NOTFOUND IS NULL;
182
183 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
184 l_log_msg := 'CUR: C_Prepay_History: prepay_invoice_id = '||
185 l_prepay_invoice_id
186 || ' Prepay_Line_Number = ' || l_prepay_line_number
187 || ' Invoice_Line_Number = ' ||l_invoice_line_number;
188 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
189 END IF;
190
191
192 BEGIN
193
194 SELECT min(accounting_Event_id)
195 INTO l_related_prepay_app_event_id
196 FROM AP_INVOICE_DISTRIBUTIONS AID
197 WHERE AID.line_type_lookup_code = 'PREPAY'
198 AND nvl(posted_flag,'N') = 'Y'
199 AND nvl(AID.amount,0) < 0
200 AND AID.invoice_id = P_invoice_id
201 AND AID.invoice_line_number = l_invoice_line_number;
202
203
204 EXCEPTION
205 WHEN NO_DATA_FOUND THEN
206 l_related_prepay_app_event_id:= null;
207
208 END;
209
210 -- Bug 6718967. Added group number to identify if it is
211 -- prepayment applied or unapplied.
212 IF l_group_number = 1 THEN
213 l_transaction_type := 'PREPAYMENT APPLIED';
214 ELSE
215 l_transaction_type := 'PREPAYMENT UNAPPLIED';
216 END IF;
217
218
219 INSERT INTO AP_PREPAY_HISTORY_ALL
220 (PREPAY_HISTORY_ID
221 ,PREPAY_INVOICE_ID
222 ,PREPAY_LINE_NUM
223 ,ACCOUNTING_EVENT_ID
224 ,HISTORICAL_FLAG
225 ,INVOICE_ID
226 ,INVOICE_LINE_NUMBER
227 ,ACCOUNTING_DATE
228 ,INVOICE_ADJUSTMENT_EVENT_ID
229 ,ORG_ID
230 ,POSTED_FLAG
231 ,RELATED_PREPAY_APP_EVENT_ID
232 ,TRANSACTION_TYPE
233 ,LAST_UPDATED_BY
234 ,LAST_UPDATE_DATE
235 ,LAST_UPDATE_LOGIN
236 ,CREATED_BY
237 ,CREATION_DATE
238 ,PROGRAM_APPLICATION_ID
239 ,PROGRAM_ID
240 ,PROGRAM_UPDATE_DATE
241 ,REQUEST_ID)
242 VALUES (AP_PREPAY_HISTORY_S.nextval
243 ,l_prepay_invoice_id
244 ,l_prepay_line_number
245 ,NULL
246 ,'N'
247 ,p_invoice_id
248 ,l_invoice_line_number
249 ,l_accounting_date
250 ,NULL
251 ,l_org_id
252 ,'N'
253 ,l_related_prepay_app_event_id
254 ,l_transaction_type
255 ,FND_GLOBAL.user_id
256 ,sysdate
257 ,FND_GLOBAL.login_id
258 ,FND_GLOBAL.user_id
259 ,sysdate
260 ,null
261 ,null
262 ,null
263 ,null);
264
265 END LOOP;
266 CLOSE c_prepay_history;
267
268 l_log_msg :='End of procedure '||l_procedure_name;
269
270 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
271 FND_LOG.STRING(G_LEVEL_PROCEDURE,
272 G_MODULE_NAME || l_procedure_name,
273 l_log_msg);
274 END IF;
275
276 END Prepay_Hist_Insert;
277
278
279
280 -------------------------------------------------------------------------------
281 -- PROCEDURE Prepay_Dist_Appl
282 -- The purpose of this procedure is to prorate the prepayment application
283 -- amount for all the distributions of the invoice that the prepayment is applied
284 -- and generate the prepayment application distributions.
285 --
286 --------------------------------------------------------------------------------
287 PROCEDURE Prepay_Dist_Appl
288 (P_Invoice_ID IN NUMBER
289 ,P_Calling_Sequence IN VARCHAR2
290 ) IS
291
292 l_curr_calling_sequence VARCHAR2(2000);
293 l_total_paid_amt NUMBER;
294 l_final_payment BOOLEAN := FALSE;
295
296 l_pay_hist_rec ap_accounting_pay_pkg.r_pay_hist_info;
297 l_clr_hist_rec ap_accounting_pay_pkg.r_pay_hist_info;
298 l_inv_rec ap_accounting_pay_pkg.r_invoices_info;
299 l_prepay_inv_rec ap_accounting_pay_pkg.r_invoices_info;
300 l_inv_dist_rec ap_accounting_pay_pkg.r_inv_dist_info;
301 l_prepay_hist_rec r_prepay_hist_info;
302 l_prepay_dist_rec r_prepay_dist_info;
303
304 -- Logging Infra:
305 l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Dist_Appl';
306 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
307
308 -- BUG # 7688509
309 -- condition: historical_flag =Y
310 -- and event is 'INVOICE ADJUSTMENT'
311 -- and ap_system_parameter.auto_offset_flag ='N'
312 -- and sum of the distributions in the invoice adjustment event is 0
313 b_generate_prepay_dist BOOLEAN;
314 l_sum_per_event NUMBER;
315 l_dist_count_per_event NUMBER;
316
317 CURSOR c_sum_per_event(p_acct_event_id NUMBER) IS
318 SELECT SUM(amount), count(1)
319 FROM ap_invoice_distributions_all aid,
320 xla_events evnt,
321 xla_ae_headers xah,
322 ap_system_parameters_all asp
323 WHERE aid.accounting_event_id = p_acct_event_id
324 AND aid.accounting_event_id = evnt.event_id
325 AND evnt.event_type_code in ('INVOICE ADJUSTED', 'CREDIT MEMO ADJUSTED',
326 'DEBIT MEMO ADJUSTED')
327 AND evnt.event_id = xah.event_id
328 AND xah.upg_batch_id IS NOT NULL
329 AND aid.org_id = asp.org_id
330 AND asp.automatic_offsets_flag = 'N'
331 AND aid.historical_flag = 'Y'
332 AND evnt.application_id=200;
333
334 BEGIN
335
336 l_curr_calling_sequence := 'AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Appl<- ' ||
337 p_calling_sequence;
338
339 -- Logging Infra: Setting up runtime level
340 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
341
342 -- Logging Infra: Procedure level
343 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
344 l_log_msg := 'Begin of procedure '|| l_procedure_name;
345 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
346 END IF;
347
348 -- Bug Fix 5634515
349 -- deleting previous unprocessed prepayment history records for invoice
350 delete_hist_dists(P_Invoice_ID,
351 l_curr_calling_sequence);
352
353 -- Logging Infra: Procedure level
354 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
355 l_log_msg := 'calling procedure Prepay_Hist_Insert ';
356 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
357 END IF;
358
359 /* Bug 4996808. Inserting into the prepayment history table */
360 Prepay_Hist_Insert (P_Invoice_ID,
361 l_curr_calling_sequence);
362
363
364 /* Get the prepayment history header info */
365 OPEN Prepay_History(P_Invoice_ID);
366 LOOP
367 FETCH Prepay_History INTO l_prepay_hist_rec;
368 EXIT WHEN Prepay_History%NOTFOUND OR
369 Prepay_History%NOTFOUND IS NULL;
370
371
372
373 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
374 l_log_msg := 'CUR: Prepay_History: prepay_history_id = '||
375 l_prepay_hist_rec.prepay_history_id
376 || ' Prepay_Invoice_ID = ' || l_prepay_hist_rec.Prepay_Invoice_ID
377 || ' Invoice_ID = ' ||l_prepay_hist_rec.Invoice_ID;
378 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
379 END IF;
380
381
382 /* Get the standard invoice header info */
383 OPEN Invoice_Header(P_Invoice_ID);
384 FETCH Invoice_Header INTO l_inv_rec;
385 CLOSE Invoice_Header;
386
387
388 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
389 l_log_msg := 'CUR: Invoice_Header: Invoice_ID = '|| l_prepay_hist_rec.invoice_id;
390 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
391 END IF;
392
393
394 /* Get the prepayment invoice header info */
395 OPEN Invoice_Header(l_prepay_hist_rec.prepay_invoice_id);
396 FETCH Invoice_Header INTO l_prepay_inv_rec;
397 CLOSE Invoice_Header;
398
399
400 /* Get the payment history info */
401 OPEN Payment_History
402 (l_prepay_hist_rec.prepay_invoice_id,
403 'PAYMENT CREATED');
404 FETCH Payment_History INTO l_pay_hist_rec;
405 CLOSE Payment_History;
406
407
408 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
409 l_log_msg := 'CUR: Payment_History for payment: Payment_History_ID = '||
410 l_pay_hist_rec.payment_history_id;
411 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
412 END IF;
413
414
415 /* Get the clearing payment history info */
416 OPEN Payment_History
417 (l_prepay_hist_rec.prepay_invoice_id,
418 'PAYMENT CLEARING');
419 FETCH Payment_History INTO l_clr_hist_rec;
420 CLOSE Payment_History;
421
422
423 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
424 l_log_msg := 'CUR: Payment_History for clearing: Payment_History_ID = '||
425 l_clr_hist_rec.payment_history_id;
426 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
427 END IF;
428
429
430 /* Get the prepay distributions for this event */
431 OPEN Prepay_Dists(P_Invoice_ID,
432 l_prepay_hist_rec.invoice_line_number,
433 l_prepay_hist_rec.accounting_date,
434 l_prepay_hist_rec.prepay_history_id);
435 LOOP
436
437 FETCH Prepay_Dists INTO l_prepay_dist_rec;
438 EXIT WHEN Prepay_Dists%NOTFOUND OR
439 Prepay_Dists%NOTFOUND IS NULL;
440
441
442 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
443 l_log_msg := 'CUR: Prepay_Dists: Invoice_ID = '||l_prepay_dist_rec.invoice_id
444 ||' Invoice_Distribution_ID = '||l_prepay_dist_rec.invoice_distribution_id
445 ||' Prepay_Distribution_ID = '||l_prepay_dist_rec.prepay_distribution_id;
446 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
447 END IF;
448
449
450 IF l_prepay_dist_rec.parent_reversal_id IS NOT NULL THEN
451
452 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
453 l_log_msg := 'CUR: Prepay_Dists: Invoice_Distribution_ID = '
454 ||l_prepay_dist_rec.invoice_distribution_id
455 ||' Parent_Reversal_ID = '||l_prepay_dist_rec.parent_reversal_id;
456 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
457 END IF;
458
459 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
460 l_log_msg := 'Calling procedure Prepay_Dist_Reverse for prepay dist: '
461 || l_prepay_dist_rec.invoice_distribution_id;
462 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
463 END IF;
464
465
466 /* Creating prepayment appl dists for unapplication by reversing the prepay appl
467 distributions */
468 Prepay_Dist_Reverse
469 (l_prepay_hist_rec,
470 l_prepay_dist_rec.parent_reversal_id,
471 NULL, -- p_xla_event_rec
472 NULL, -- p_inv_reversal_id
473 -- Bug 7134020
474 NULL, -- p_inv_dist_id
475 l_prepay_dist_rec.invoice_distribution_id,
476 l_curr_calling_sequence);
477
478
479 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
480 l_log_msg := 'Procedure Prepay_Dist_Reverse executed';
481 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
482 END IF;
483
484 ELSE
485
486 /* Check if the invoice is fully paid */
487 l_final_payment := AP_Accounting_Pay_Pkg.Is_Final_Payment
488 (l_inv_rec,
489 NULL, -- Payment Amount
490 NULL, -- Discount Amount
491 l_prepay_dist_rec.amount,
492 'PAYMENT CREATED',
493 l_curr_calling_sequence);
494
495 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
496 IF l_final_payment THEN
497 l_log_msg := 'Final payment of Invoice_ID '||l_prepay_dist_rec.invoice_id;
498 ELSE
499 l_log_msg := 'Not final payment of Invoice_ID '||l_prepay_dist_rec.invoice_id;
500 END IF;
501 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
502 END IF;
503
504
505 OPEN Invoice_Dists(p_invoice_id);
506 LOOP
507
508 FETCH Invoice_Dists INTO l_inv_dist_rec;
509 EXIT WHEN Invoice_Dists%NOTFOUND OR
510 Invoice_Dists%NOTFOUND IS NULL;
511
512 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
513 l_log_msg := 'CUR: Invoice_Dists: Invoice_Distribution_ID = '
514 ||l_inv_dist_rec.invoice_distribution_id;
515 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
516 END IF;
517
518
519 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
520 l_log_msg := 'Calling procedure Prepay_Dist_Proc for dist: '
521 || l_inv_dist_rec.invoice_distribution_id;
522 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
523 END IF;
524
525 -- BUG # 7688509
526 -- condition: historical_flag =Y
527 -- and event is 'INVOICE ADJUSTED'
528 -- and ap_system_parameter.auto_offset_flag ='N'
529 -- and sum of the distributions in the invoice adjustment event is 0
530
531 b_generate_prepay_dist := TRUE;
532 IF l_inv_dist_rec.historical_flag ='Y' THEN
533 OPEN c_sum_per_event(l_inv_dist_rec.accounting_event_id);
534 FETCH c_sum_per_event into l_sum_per_event, l_dist_count_per_event;
535
536 -- > 0 case is to handled the case that only 1 line in adjustment event and itself amount is 0
537 If l_dist_count_per_event > 0 AND l_sum_per_event = 0 THEN
538 b_generate_prepay_dist := FALSE;
539 END IF;
540
541 CLOSE c_sum_per_event;
542
543 END IF;
544
545 -- Prorate only those awt distributions that were created during the invoice time
546 -- modified the if condition for bug # 7688509
547 IF l_inv_dist_rec.awt_invoice_payment_id IS NULL and b_generate_prepay_dist THEN
548 Prepay_Dist_Proc
549 (l_pay_hist_rec,
550 l_clr_hist_rec,
551 l_inv_rec,
552 l_prepay_inv_rec,
553 l_prepay_hist_rec,
554 l_prepay_dist_rec,
555 l_inv_dist_rec,
556 NULL, -- p_xla_event_rec
557 'A',
558 l_final_payment,
559 l_curr_calling_sequence);
560 END IF;
561
562
563 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
564 l_log_msg := 'Procedure Prepay_Dist_Proc executed';
565 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
566 END IF;
567
568 END LOOP;
569 CLOSE Invoice_Dists;
570
571 G_Total_Dist_Amt := 0;
572 G_Total_Prorated_Amt := 0;
573 G_Total_Tax_Diff_Amt := 0;
574
575
576 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
577 l_log_msg := 'Calling procedure P_Acctg_Pay_Round_Pkg.Do_Rounding for Invoice_ID: '
578 || l_inv_rec.invoice_id;
579 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
580 END IF;
581
582 -- bug 7611160
583 SELECT asp.base_currency_code
584 INTO ap_accounting_pay_pkg.g_base_currency_code
585 FROM ap_system_parameters_all asp,
586 ap_invoices_all ai
587 WHERE asp.org_id = ai.org_id
588 AND ai.invoice_id = l_inv_rec.invoice_id;
589
590 AP_Acctg_Pay_Round_Pkg.Do_Rounding
591 (NULL, -- p_xla_event_rec
592 l_pay_hist_rec,
593 l_clr_hist_rec,
594 l_inv_rec,
595 NULL, -- l_inv_pay_rec
596 l_prepay_inv_rec,
597 l_prepay_hist_rec,
598 l_prepay_dist_rec,
599 l_curr_calling_sequence);
600
601 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
602 l_log_msg := 'Procedure P_Acctg_Pay_Round_Pkg.Do_Rounding executed';
603 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
604 END IF;
605
606 END IF;
607 END LOOP;
608 CLOSE Prepay_Dists;
609
610 END LOOP;
611 CLOSE Prepay_History;
612
613 -- Logging Infra: Procedure level
614 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
615 l_log_msg := 'End of procedure '|| l_procedure_name;
616 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
617 END IF;
618
619
620 EXCEPTION
621 WHEN OTHERS THEN
622 IF (SQLCODE <> -20001) THEN
623 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
624 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
625 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
626 END IF;
627 APP_EXCEPTION.RAISE_EXCEPTION;
628
629 END Prepay_Dist_Appl;
630
631
632 -------------------------------------------------------------------------------
633 -- PROCEDURE Prepay_Dist_Cascade_Adj
634 -- The purpose of this procedure is to prorate the prepayment amount for all the
635 -- distributions of the invoice that has been adjusted and generate the
636 -- prepayment application payment distributions.
637 --
638 --------------------------------------------------------------------------------
639 PROCEDURE Prepay_Dist_Cascade_Adj
640 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
641 ,P_Calling_Sequence IN VARCHAR2
642 ) IS
643
644 l_curr_calling_sequence VARCHAR2(2000);
645 l_inv_adj_amount NUMBER := 0;
646 l_sum_prepaid_amount NUMBER := 0;
647 l_sum_tax_diff_amount NUMBER := 0;
648
649 l_pay_hist_rec ap_accounting_pay_pkg.r_pay_hist_info;
650 l_clr_hist_rec ap_accounting_pay_pkg.r_pay_hist_info;
651 l_prepay_inv_rec ap_accounting_pay_pkg.r_invoices_info;
652 l_inv_rec ap_accounting_pay_pkg.r_invoices_info;
653 l_prepay_hist_rec r_prepay_hist_info;
654 l_prepay_dist_rec r_prepay_dist_info;
655 l_inv_dist_rec ap_accounting_pay_pkg.r_inv_dist_info;
656
657 --7488981
658 l_prepay_dist_cnt NUMBER;
659
660 -- Logging Infra:
661 l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Dist_Cascade_Adj';
662 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
663
664 -- Bug 6698125. Added adj cursor to get the prepay history record
665 -- related to prepayment adjustment type events.
666 CURSOR Prepay_History_Adj
667 (P_Invoice_ID NUMBER,
668 P_Event_ID NUMBER
669 ) IS
670 SELECT APH.Prepay_History_ID,
671 APH.Prepay_Invoice_ID,
672 APH.Invoice_ID,
673 APH.Invoice_Line_Number,
674 APH.Transaction_Type,
675 APH.Accounting_Date,
676 APH.Invoice_Adjustment_Event_ID,
677 APH.Related_Prepay_App_Event_ID
678 FROM AP_Prepay_History_All APH
679 WHERE APH.Invoice_ID = P_Invoice_ID
680 AND APH.Accounting_Event_ID = P_Event_ID;
681
682 CURSOR Inv_Adj_Dists
683 (P_Event_ID NUMBER
684 ,P_Invoice_ID NUMBER) IS
685 SELECT Distinct AID.Invoice_Distribution_ID,
686 AID.Line_Type_Lookup_Code,
687 AID.Amount,
688 AID.Base_Amount,
689 AID.PO_Distribution_ID,
690 AID.RCV_Transaction_ID,
691 NVL(AID.Reversal_Flag,'N'),
692 AID.Parent_Reversal_ID,
693 AID.AWT_Related_ID,
694 AID.AWT_Invoice_Payment_ID,
695 AID.Quantity_Variance,
696 AID.Base_Quantity_Variance,
697 AID.Amount_Variance,
698 AID.Base_Amount_Variance,
699 AID.Historical_Flag, -- bug fix 6674279
700 AID.Accounting_Event_Id -- bug fix 6674279
701 FROM AP_Invoice_Distributions_All AID,
702 AP_Prepay_App_Dists APAD,
703 Financials_System_Params_All FSP
704 WHERE AID.Invoice_ID = P_Invoice_ID
705 AND NVL(AID.Reversal_Flag,'N') <> 'Y'
706 AND NVL(AID.Accounting_Event_ID,-99) <> P_Event_ID
707 AND APAD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
708 AND FSP.Org_ID = AID.Org_ID
709 AND ((NVL(FSP.Purch_Encumbrance_Flag,'N') = 'N'
710 AND AID.Match_Status_Flag IN ('T','A'))
711 OR
712 ((NVL(FSP.Purch_Encumbrance_Flag,'N') = 'Y'
713 AND AID.Match_Status_Flag = 'A')));
714
715 CURSOR Prepay_Adj_Dists
716 (P_Event_ID NUMBER,
717 P_Prepay_History_ID NUMBER
718 ) IS
719 (SELECT AID.Invoice_ID,
720 AID.Invoice_Distribution_ID,
721 AID.Line_Type_Lookup_Code,
722 AID.Amount,
723 AID.Base_Amount,
724 AID.Accounting_Event_ID,
725 AID.Prepay_Distribution_ID,
726 AID.Prepay_Tax_Diff_Amount,
727 AID.Parent_Reversal_ID
728 FROM AP_Invoice_Distributions_All AID
729 WHERE Accounting_Event_ID = P_Event_ID
730 AND EXISTS (SELECT 'Prepay History'
731 FROM AP_Prepay_History_All APH,
732 AP_Invoice_Distributions_All AID1
733 WHERE APH.Prepay_History_ID = P_Prepay_History_ID
734 AND AID1.Invoice_Distribution_ID = AID.Prepay_Distribution_ID
735 AND AID1.Invoice_ID = APH.Prepay_Invoice_ID
736 AND AID1.Invoice_Line_Number = APH.Prepay_Line_Num)
737 UNION ALL
738 SELECT AID.Invoice_ID,
739 AID.Invoice_Distribution_ID,
740 AID.Line_Type_Lookup_Code,
741 AID.Amount,
742 AID.Base_Amount,
743 AID.Accounting_Event_ID,
744 AID.Prepay_Distribution_ID,
745 AID.Prepay_Tax_Diff_Amount,
746 AID.Parent_Reversal_ID
747 FROM AP_Invoice_Distributions_All AID
748 WHERE Line_Type_Lookup_Code IN ( 'NONREC_TAX','REC_TAX')
749 AND Accounting_Event_ID = P_Event_ID
750 AND Charge_Applicable_To_Dist_ID IN
751 (SELECT AID1.Invoice_Distribution_ID
752 FROM AP_Invoice_Distributions_All AID1
753 WHERE Line_Type_Lookup_Code = 'PREPAY'
754 AND Accounting_Event_ID = P_Event_ID
755 AND EXISTS (SELECT 'Prepay History'
756 FROM AP_Prepay_History_All APH,
757 AP_Invoice_Distributions_All AID2
758 WHERE APH.Prepay_History_ID = P_Prepay_History_ID
759 AND AID2.Invoice_Distribution_ID = AID1.Prepay_Distribution_ID
760 AND AID2.Invoice_ID = APH.Prepay_Invoice_ID
761 AND AID2.Invoice_Line_Number = APH.Prepay_Line_Num)));
762
763
764
765 BEGIN
766
767 l_curr_calling_sequence := 'AP_Acctg_Pay_Dist_Pkg.Prepay_Dist_Cascade_Adj<- ' ||
768 p_calling_sequence;
769
770 -- Logging Infra: Procedure level
771 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
772 l_log_msg := 'Begin of procedure '|| l_procedure_name;
773 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
774 END IF;
775
776
777 /* Get the prepayment history header info */
778 OPEN Prepay_History_Adj(P_XLA_Event_Rec.Source_id_int_1,
779 P_XLA_Event_Rec.Event_ID);
780 FETCH Prepay_History_Adj INTO l_prepay_hist_rec;
781 CLOSE Prepay_History_Adj;
782
783 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
784 l_log_msg := 'CUR: Prepay_History: prepay_history_id = '||
785 l_prepay_hist_rec.prepay_history_id
786 || ' Prepay_Invoice_ID = ' || l_prepay_hist_rec.Prepay_Invoice_ID
787 || ' Invoice_ID = ' ||l_prepay_hist_rec.Invoice_ID
788 || ' Related_Event_ID = ' ||l_prepay_hist_rec.related_prepay_app_event_id
789 || ' Inv_Adj_Event_ID = ' ||l_prepay_hist_rec.invoice_adjustment_event_id;
790 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
791 END IF;
792
793
794 /* Get the standard invoice header info */
795 OPEN Invoice_Header(P_XLA_Event_Rec.source_id_int_1);
796 FETCH Invoice_Header INTO l_inv_rec;
797 CLOSE Invoice_Header;
798
799
800 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
801 l_log_msg := 'CUR: Invoice_Header: Invoice_ID = '|| l_prepay_hist_rec.invoice_id;
802 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
803 END IF;
804
805
806 /* Get the prepayment invoice header info */
807 OPEN Invoice_Header(l_prepay_hist_rec.prepay_invoice_id);
808 FETCH Invoice_Header INTO l_prepay_inv_rec;
809 CLOSE Invoice_Header;
810
811 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
812 l_log_msg := 'CUR: Prepay Invoice_Header: Invoice_ID = '|| l_prepay_inv_rec.invoice_id;
813 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
814 END IF;
815
816
817 /* Get the payment history info */
818 OPEN Payment_History
819 (l_prepay_hist_rec.prepay_invoice_id,
820 'PAYMENT CREATED');
821 FETCH Payment_History INTO l_pay_hist_rec;
822 CLOSE Payment_History;
823
824
825 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
826 l_log_msg := 'CUR: Payment_History for payment: Payment_History_ID = '||
827 l_pay_hist_rec.payment_history_id;
828 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
829 END IF;
830
831
832 /* Get the clearing payment history info */
833 OPEN Payment_History
834 (l_prepay_hist_rec.prepay_invoice_id,
835 'PAYMENT CLEARING');
836 FETCH Payment_History INTO l_clr_hist_rec;
837 CLOSE Payment_History;
838
839
840 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
841 l_log_msg := 'CUR: Payment_History for clearing: Payment_History_ID = '||
842 l_clr_hist_rec.payment_history_id;
843 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
844 END IF;
845
846
847 /* Get the prepay dists based on the related event id */
848 OPEN Prepay_Adj_Dists(l_prepay_hist_rec.related_prepay_app_event_id,
849 l_prepay_hist_rec.prepay_history_id);
850 LOOP
851
852 FETCH Prepay_Adj_Dists INTO l_prepay_dist_rec;
853 EXIT WHEN Prepay_Adj_Dists%NOTFOUND OR
854 Prepay_Adj_Dists%NOTFOUND IS NULL;
855
856
857 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
858 l_log_msg := 'CUR: Prepay_Dists: Invoice_ID = '||l_prepay_dist_rec.invoice_id
859 ||' Invoice_Distribution_ID = '||l_prepay_dist_rec.invoice_distribution_id
860 ||' Prepay_Distribution_ID = '||l_prepay_dist_rec.prepay_distribution_id;
861 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
862 END IF;
863
864
865 OPEN Invoice_Dists(l_prepay_hist_rec.invoice_id,
866 l_prepay_hist_rec.invoice_adjustment_event_id);
867 LOOP
868
869 FETCH Invoice_Dists INTO l_inv_dist_rec;
870 EXIT WHEN Invoice_Dists%NOTFOUND OR
871 Invoice_Dists%NOTFOUND IS NULL;
872
873
874 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
875 l_log_msg := 'CUR: Invoice_Dists: Invoice_Distribution_ID = '
876 ||l_inv_dist_rec.invoice_distribution_id
877 || ' Reversal_Flag = ' ||l_inv_dist_rec.reversal_flag;
878 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
879 END IF;
880
881 -- in bug 7488981 call to prepay_dist_reverse was made with null parent_reversal_id
882 -- therefore the following check is added to check that
883
884 IF l_inv_dist_rec.parent_reversal_id IS NOT NULL THEN
885
886 SELECT count(*)
887 INTO l_prepay_dist_cnt
888 FROM ap_prepay_app_dists
889 WHERE invoice_distribution_id = l_inv_dist_rec.parent_reversal_id;
890
891 END IF;
892
893 IF l_inv_dist_rec.reversal_flag = 'Y' AND
894 l_prepay_dist_cnt > 0 THEN
895
896
897 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
898 l_log_msg := 'Calling procedure Prepay_Dist_Reverse for dist: '
899 || l_inv_dist_rec.invoice_distribution_id;
900 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
901 END IF;
902
903 Prepay_Dist_Reverse
904 (l_prepay_hist_rec,
905 NULL, -- p_prepay_reversal_id
906 p_xla_event_rec, -- Bug 6698125
907 l_inv_dist_rec.parent_reversal_id,
908 l_inv_dist_rec.invoice_distribution_id, -- Bug 7134020
909 l_prepay_dist_rec.invoice_distribution_id,
910 l_curr_calling_sequence);
911
912 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
913 l_log_msg := 'Procedure Prepay_Dist_Reverse executed';
914 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
915 END IF;
916
917 ELSE
918
919 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
920 l_log_msg := 'Calling procedure Prepay_Dist_Proc for dist: '
921 || l_inv_dist_rec.invoice_distribution_id;
922 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
923 END IF;
924
925 -- Prorate only those awt distributions that were created during the invoice time
926 IF (l_inv_dist_rec.awt_invoice_payment_id IS NULL) THEN
927 Prepay_Dist_Proc
928 (l_pay_hist_rec,
929 l_clr_hist_rec,
930 l_inv_rec,
931 l_prepay_inv_rec,
932 l_prepay_hist_rec,
933 l_prepay_dist_rec,
934 l_inv_dist_rec,
935 p_xla_event_rec, -- Bug 6698125
936 'C',
937 NULL,
938 l_curr_calling_sequence);
939 END IF;
940
941 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
942 l_log_msg := 'Procedure Prepay_Dist_Proc executed';
943 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
944 END IF;
945
946 END IF;
947
948 END LOOP;
949 CLOSE Invoice_Dists;
950
951
952 SELECT SUM(AID.Amount)
953 INTO l_inv_adj_amount
954 FROM AP_Invoice_Distributions_All AID
955 WHERE AID.Accounting_Event_ID = l_prepay_hist_rec.invoice_adjustment_event_id;
956
957 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
958 l_log_msg := 'l_inv_adj_amount = ' ||l_inv_adj_amount;
959 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
960 END IF;
961
962 /* Check if there is any change to the invoice liability. If there is
963 a change then we need to adjust the payment hist distributions for the
964 old invoice distributions */
965 IF l_inv_adj_amount <> 0 THEN
966
967 OPEN Inv_Adj_Dists(l_prepay_hist_rec.invoice_adjustment_event_id,
968 l_inv_rec.invoice_id);
969 LOOP
970
971 FETCH Inv_Adj_Dists INTO l_inv_dist_rec;
972 EXIT WHEN Inv_Adj_Dists%NOTFOUND OR
973 Inv_Adj_Dists%NOTFOUND IS NULL;
974
975
976 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
977 l_log_msg := 'CUR: Inv_Adj_Dists: Invoice_Distribution_ID = '
978 ||l_inv_dist_rec.invoice_distribution_id;
979 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
980 END IF;
981
982 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
983 l_log_msg := 'Calling procedure Prepay_Dist_Proc for dist: '
984 ||l_inv_dist_rec.invoice_distribution_id;
985 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
986 END IF;
987
988 Prepay_Dist_Proc(l_pay_hist_rec,
989 l_clr_hist_rec,
990 l_inv_rec,
991 l_prepay_inv_rec,
992 l_prepay_hist_rec,
993 l_prepay_dist_rec,
994 l_inv_dist_rec,
995 p_xla_event_rec, -- Bug 6698125
996 'C',
997 NULL,
998 l_curr_calling_sequence);
999
1000 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1001 l_log_msg := 'Procedure Prepay_Dist_Proc executed';
1002 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1003 END IF;
1004
1005
1006 END LOOP;
1007 CLOSE Inv_Adj_Dists;
1008 END IF;
1009
1010
1011 -- joined with ap_invoice_distributions_all for the performance issue 7235352
1012 SELECT /*+ leading(aid) */ SUM(DECODE(APAD.Prepay_Dist_Lookup_Code, 'PREPAY APPL', APAD.Amount,
1013 'PREPAY APPL REC TAX', APAD.Amount,
1014 'PREPAY APPL NONREC TAX', APAD.Amount, 0)),
1015 SUM(DECODE(APAD.Prepay_Dist_Lookup_Code, 'TAX DIFF', APAD.Amount, 0))
1016 INTO l_sum_prepaid_amount,
1017 l_sum_tax_diff_amount
1018 FROM AP_Prepay_App_Dists APAD,
1019 ap_invoice_distributions_all aid
1020 WHERE APAD.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
1021 AND apad.invoice_distribution_id = aid.invoice_distribution_id
1022 AND aid.invoice_id = l_prepay_dist_rec.invoice_id;
1023
1024
1025 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1026 l_log_msg := 'Updating the prorated prepaid amounts';
1027 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1028 END IF;
1029
1030
1031 /* Updating the prorated prepaid amounts for any rounding */
1032 UPDATE AP_Prepay_App_Dists APAD
1033 SET APAD.Amount = APAD.Amount - NVL(l_sum_prepaid_amount,0) + l_prepay_dist_rec.amount
1034 WHERE APAD.Invoice_Distribution_ID =
1035 (SELECT MAX(APAD1.Invoice_Distribution_ID)
1036 FROM AP_Prepay_App_Dists APAD1
1037 WHERE APAD1.Accounting_Event_ID = p_xla_event_rec.event_id
1038 AND APAD1.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
1039 AND APAD1.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
1040 'PREPAY APPL NONREC TAX')
1041 AND ABS(APAD1.Amount) =
1042 (SELECT MAX(APAD2.Amount)
1043 FROM AP_Prepay_App_Dists APAD2
1044 WHERE APAD2.Accounting_Event_ID = p_xla_event_rec.event_id
1045 AND APAD2.Prepay_App_Distribution_ID
1046 = l_prepay_dist_rec.invoice_distribution_id
1047 AND APAD2.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
1048 'PREPAY APPL NONREC TAX')))
1049 AND APAD.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
1050 'PREPAY APPL NONREC TAX')
1051 AND APAD.Accounting_Event_ID = p_xla_event_rec.event_id
1052 AND APAD.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id;
1053
1054
1055 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1056 l_log_msg := 'Updating the prorated tax diff amounts';
1057 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1058 END IF;
1059
1060 /* Updating the prorated tax diff amounts for any rounding */
1061 IF (l_prepay_dist_rec.prepay_tax_diff_amount <> 0) THEN
1062
1063 UPDATE AP_Prepay_App_Dists APAD
1064 SET APAD.Amount = APAD.Amount - NVL(l_sum_tax_diff_amount,0)
1065 + l_prepay_dist_rec.prepay_tax_diff_amount
1066 WHERE APAD.Invoice_Distribution_ID =
1067 (SELECT MAX(APAD1.Invoice_Distribution_ID)
1068 FROM AP_Prepay_App_Dists APAD1
1069 WHERE APAD1.Accounting_Event_ID = p_xla_event_rec.event_id
1070 AND APAD1.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
1071 AND APAD1.Prepay_Dist_Lookup_Code = 'TAX DIFF'
1072 AND ABS(APAD1.Amount) =
1073 (SELECT MAX(APAD2.Amount)
1074 FROM AP_Prepay_App_Dists APAD2
1075 WHERE APAD2.Accounting_Event_ID = p_xla_event_rec.event_id
1076 AND APAD2.Prepay_App_Distribution_ID
1077 = l_prepay_dist_rec.invoice_distribution_id
1078 AND APAD2.Prepay_Dist_Lookup_Code = 'TAX DIFF'))
1079 AND APAD.Prepay_Dist_Lookup_Code = 'TAX DIFF'
1080 AND APAD.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
1081 AND APAD.Accounting_Event_ID = p_xla_event_rec.event_id;
1082
1083 END IF;
1084
1085
1086 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1087 l_log_msg := 'Calling procedure P_Acctg_Pay_Round_Pkg.Do_Rounding for Invoice_ID: '
1088 || l_inv_rec.invoice_id;
1089 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1090 END IF;
1091
1092 AP_Acctg_Pay_Round_Pkg.Do_Rounding
1093 (NULL, --p_xla_event_rec,
1094 l_pay_hist_rec,
1095 l_clr_hist_rec,
1096 l_inv_rec,
1097 NULL, -- l_inv_pay_rec
1098 l_prepay_inv_rec,
1099 l_prepay_hist_rec,
1100 l_prepay_dist_rec,
1101 l_curr_calling_sequence);
1102
1103 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1104 l_log_msg := 'Procedure P_Acctg_Pay_Round_Pkg.Do_Rounding executed';
1105 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1106 END IF;
1107
1108
1109 END LOOP;
1110 CLOSE Prepay_Adj_Dists;
1111
1112 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1113 l_log_msg := 'Calling procedure AP_Acctg_Prepay_Dist_Pkg.Update_Gain_Loss_Ind';
1114 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1115 END IF;
1116
1117 AP_Acctg_Prepay_Dist_Pkg.Update_Gain_Loss_Ind
1118 (p_xla_event_rec,
1119 l_curr_calling_sequence);
1120
1121 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1122 l_log_msg := 'Procedure AP_Acctg_Prepay_Dist_Pkg.Updated_Gain_Loss_Ind executed';
1123 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1124 END IF;
1125
1126
1127 EXCEPTION
1128 WHEN OTHERS THEN
1129 IF (SQLCODE <> -20001) THEN
1130 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1131 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1132 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1133 END IF;
1134 APP_EXCEPTION.RAISE_EXCEPTION;
1135
1136 END Prepay_Dist_Cascade_Adj;
1137
1138
1139
1140 ---------------------------------------------------------------------
1141 -- Procedure Prepay_Dist_Proc
1142 -- This procedure prorates the prepayment application amounts for each
1143 -- distribution and inserts the calculated values into prepayment
1144 -- application distribution table
1145 -- Also calculates ERV
1146 ---------------------------------------------------------------------
1147 -- Bug 6698125. Added p_xla_event_rec parameter
1148 PROCEDURE Prepay_Dist_Proc
1149 (p_pay_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
1150 ,p_clr_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
1151 ,p_inv_rec IN ap_accounting_pay_pkg.r_invoices_info
1152 ,p_prepay_inv_rec IN ap_accounting_pay_pkg.r_invoices_info
1153 ,p_prepay_hist_rec IN r_prepay_hist_info
1154 ,p_prepay_dist_rec IN r_prepay_dist_info
1155 ,p_inv_dist_rec IN ap_accounting_pay_pkg.r_inv_dist_info
1156 ,p_xla_event_rec IN ap_accounting_pay_pkg.r_xla_event_info
1157 ,p_calc_mode IN VARCHAR2
1158 ,p_final_payment IN BOOLEAN
1159 ,p_calling_sequence IN VARCHAR2
1160 ) IS
1161
1162
1163 l_curr_calling_sequence VARCHAR2(2000);
1164 l_dist_amount NUMBER;
1165 l_prorated_amount NUMBER;
1166 l_prorated_base_amount NUMBER;
1167 l_inv_dist_amount NUMBER;
1168 l_prorated_pay_amt NUMBER;
1169 l_prorated_clr_amt NUMBER;
1170 l_total_paid_amt NUMBER;
1171 l_total_prepaid_amt NUMBER;
1172 l_total_inv_dist_amt NUMBER;
1173 l_total_bank_curr_amt NUMBER;
1174 l_total_dist_amount NUMBER;
1175
1176 l_qty_variance NUMBER;
1177 l_base_qty_variance NUMBER;
1178 l_amt_variance NUMBER;
1179 l_base_amt_variance NUMBER;
1180
1181 l_pad_rec AP_PREPAY_APP_DISTS%ROWTYPE;
1182
1183 -- Logging Infra:
1184 l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Dist_Proc';
1185 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1186
1187
1188 BEGIN
1189
1190 l_curr_calling_sequence := 'AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Proc<- ' ||
1191 p_calling_sequence;
1192
1193
1194 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1195 l_log_msg := 'Begin of procedure '|| l_procedure_name;
1196 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1197 END IF;
1198
1199
1200 SELECT SUM(NVL(AID.Amount,0))
1201 INTO l_total_dist_amount
1202 FROM AP_Invoice_Distributions_All AID
1203 WHERE AID.Invoice_ID = p_inv_rec.invoice_id
1204 AND AID.Line_Type_Lookup_Code <> 'PREPAY'
1205 AND AID.Prepay_Distribution_ID IS NULL
1206 AND AID.Prepay_Tax_Parent_ID IS NULL -- For tax dists created in R11.5
1207 AND AID.AWT_Invoice_Payment_ID IS NULL
1208 AND NVL(AID.Cancellation_Flag,'N') <> 'Y' -- BUG 6513956
1209 --bug fix 6909150
1210 AND NOT EXISTS (SELECT 1
1211 FROM xla_events
1212 WHERE event_id = AID.accounting_event_id
1213 AND event_type_code IN ('INVOICE CANCELLED',
1214 'CREDIT MEMO CANCELLED',
1215 'DEBIT MEMO CANCELLED'));
1216
1217 g_total_dist_amt := g_total_dist_amt + p_inv_dist_rec.amount;
1218
1219 IF (p_calc_mode = 'A') THEN
1220
1221 -- If this payment is a final payment for the invoice then we should make sure
1222 -- that the sum of prepay appl dists amount should be equal to the distribution
1223 -- total. This way the liability is fully relieved.
1224 IF p_final_payment = TRUE THEN
1225
1226 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1227 l_log_msg := 'Calling procedure AP_Accounting_Pay_Pkg.Get_Pay_Sum';
1228 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1229 END IF;
1230
1231 AP_Accounting_Pay_Pkg.Get_Pay_Sum
1232 (p_inv_dist_rec.invoice_distribution_id,
1233 'PAYMENT CREATED',
1234 l_total_paid_amt,
1235 l_total_inv_dist_amt,
1236 l_total_bank_curr_amt,
1237 l_curr_calling_sequence);
1238
1239
1240 l_total_prepaid_amt := AP_Accounting_Pay_Pkg.Get_Prepay_Sum
1241 (p_inv_dist_rec.invoice_distribution_id,
1242 l_curr_calling_sequence);
1243
1244
1245 -- Converting the distribution and prepaid amount into payment currency for
1246 -- cross currency invoices.
1247 IF (p_inv_rec.invoice_currency_code <> p_inv_rec.payment_currency_code) THEN
1248
1249 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1250 l_log_msg := 'Invoice curr diff than payment curr';
1251 l_log_msg := l_log_msg || ' Converting l_total_paid_amt to invoice curr';
1252 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1253 END IF;
1254
1255 l_total_paid_amt := GL_Currency_API.Convert_Amount(
1256 p_inv_rec.payment_currency_code,
1257 p_inv_rec.invoice_currency_code,
1258 p_inv_rec.payment_cross_rate_date,
1259 'EMU FIXED',
1260 l_total_paid_amt);
1261
1262 END IF;
1263
1264
1265 /* If this payment is a final payment then we should make sure that the
1266 distributed payment amount equals the distribution amount. This way the
1267 the liability for the distribution is relieved completely */
1268 l_prorated_amount := -1 * (p_inv_dist_rec.amount - l_total_paid_amt +
1269 l_total_prepaid_amt);
1270
1271 ELSE
1272
1273 IF g_total_dist_amt = l_total_dist_amount THEN -- last dist rec
1274
1275 -- To avoid rounding, massage the last (biggest) line
1276 l_prorated_amount := p_prepay_dist_rec.amount - g_total_prorated_amt;
1277 ELSE
1278
1279 IF p_inv_rec.invoice_amount = 0 THEN
1280 l_prorated_amount := 0;
1281
1282 ELSE
1283
1284 IF (p_inv_dist_rec.line_type_lookup_code = 'AWT') THEN
1285 l_prorated_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
1286 (p_prepay_dist_rec.amount * (-1*p_inv_dist_rec.amount)
1287 / l_total_dist_amount,
1288 p_inv_rec.invoice_currency_code);
1289 ELSE
1290 l_prorated_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
1291 (p_prepay_dist_rec.amount * p_inv_dist_rec.amount
1292 / p_inv_rec.invoice_amount,
1293 p_inv_rec.invoice_currency_code);
1294 END IF; -- IF AWT line type
1295
1296 END IF;
1297 END IF;
1298
1299 END IF;
1300
1301 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1302 l_log_msg := 'Value of l_prorated_amount = '|| l_prorated_amount;
1303 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1304 END IF;
1305
1306
1307 IF (p_inv_dist_rec.line_type_lookup_code <> 'AWT') THEN
1308 g_total_prorated_amt := g_total_prorated_amt + l_prorated_amount;
1309 END IF;
1310
1311
1312 /* If this is a cascade event then we will create new payment distributions
1313 for the existing invoice distributions that have already been distributed to
1314 this payment in order to adjust the payments as a result of adjusting the
1315 invoice */
1316 ELSE
1317
1318 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1319 l_log_msg := 'Calculating prorated amount for cascade adjustment';
1320 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1321 END IF;
1322
1323 IF p_inv_rec.invoice_amount = 0 THEN
1324 l_prorated_amount := 0;
1325 ELSE
1326
1327 -- In case of cascade events we will recalculate the prorated amount and subtract
1328 -- this amount from the already calculated amount previously so that this would
1329 -- give us the amount that needs to be adjusted
1330 l_prorated_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
1331 (((p_inv_dist_rec.amount * p_prepay_dist_rec.amount)
1332 / p_inv_rec.invoice_amount)
1333 - AP_Accounting_Pay_Pkg.get_casc_prepay_sum
1334 (p_inv_dist_rec.invoice_distribution_id,
1335 p_prepay_dist_rec.invoice_distribution_id,
1336 l_curr_calling_sequence),
1337 p_inv_rec.invoice_currency_code);
1338
1339 END IF;
1340 END IF;
1341
1342
1343 -- Populate prepay appl dist rec
1344
1345 l_pad_rec.prepay_history_id := p_prepay_hist_rec.prepay_history_id;
1346
1347 IF p_inv_dist_rec.line_type_lookup_code = 'AWT' THEN
1348 l_pad_rec.prepay_dist_lookup_code := 'AWT';
1349 l_pad_rec.awt_related_id := p_inv_dist_rec.awt_related_id;
1350 ELSIF p_prepay_dist_rec.line_type_lookup_code = 'NONREC_TAX' THEN
1351 l_pad_rec.prepay_dist_lookup_code := 'PREPAY APPL NONREC TAX';
1352 ELSIF p_prepay_dist_rec.line_type_lookup_code = 'REC_TAX' THEN
1353 l_pad_rec.prepay_dist_lookup_code := 'PREPAY APPL REC TAX';
1354 ELSE
1355 l_pad_rec.prepay_dist_lookup_code := 'PREPAY APPL';
1356 END IF;
1357
1358 l_pad_rec.invoice_distribution_id := p_inv_dist_rec.invoice_distribution_id;
1359 l_pad_rec.prepay_app_distribution_id := p_prepay_dist_rec.invoice_distribution_id;
1360 l_pad_rec.accounting_event_id := p_xla_event_rec.event_id;
1361
1362 l_pad_rec.amount := l_prorated_amount;
1363
1364 l_pad_rec.prepay_exchange_date := p_prepay_inv_rec.exchange_date;
1365 l_pad_rec.prepay_pay_exchange_date := p_pay_hist_rec.pmt_to_base_xrate_date;
1366 l_pad_rec.prepay_clr_exchange_date := p_clr_hist_rec.bank_to_base_xrate_date;
1367
1368 l_pad_rec.prepay_exchange_rate := p_prepay_inv_rec.exchange_rate;
1369 l_pad_rec.prepay_pay_exchange_rate := p_pay_hist_rec.pmt_to_base_xrate;
1370 l_pad_rec.prepay_clr_exchange_rate := p_clr_hist_rec.bank_to_base_xrate;
1371
1372 l_pad_rec.prepay_exchange_rate_type := p_prepay_inv_rec.exchange_rate_type;
1373 l_pad_rec.prepay_pay_exchange_rate_type := p_pay_hist_rec.pmt_to_base_xrate_type;
1374 l_pad_rec.prepay_clr_exchange_rate_type := p_clr_hist_rec.bank_to_base_xrate_type;
1375
1376
1377 l_pad_rec.base_amt_at_prepay_xrate := AP_Accounting_Pay_Pkg.Get_Base_Amount
1378 (l_prorated_amount,
1379 p_prepay_inv_rec.invoice_currency_code,
1380 p_inv_rec.base_currency_code,
1381 p_prepay_inv_rec.exchange_rate_type,
1382 p_prepay_inv_rec.exchange_date,
1383 p_prepay_inv_rec.exchange_rate,
1384 l_curr_calling_sequence);
1385
1386
1387 IF (p_inv_rec.invoice_currency_code <> p_pay_hist_rec.pmt_currency_code) THEN
1388 l_prorated_pay_amt := AP_UTILITIES_PKG.AP_Round_Currency(
1389 l_prorated_amount * p_inv_rec.payment_cross_rate,
1390 p_pay_hist_rec.pmt_currency_code);
1391 ELSE
1392 l_prorated_pay_amt := l_prorated_amount;
1393 END IF;
1394
1395 l_pad_rec.base_amt_at_prepay_pay_xrate := AP_Accounting_Pay_Pkg.Get_Base_Amount
1396 (l_prorated_pay_amt,
1397 p_pay_hist_rec.pmt_currency_code,
1398 p_inv_rec.base_currency_code,
1399 p_pay_hist_rec.pmt_to_base_xrate_type,
1400 p_pay_hist_rec.pmt_to_base_xrate_date,
1401 p_pay_hist_rec.pmt_to_base_xrate,
1402 l_curr_calling_sequence);
1403
1404 IF (p_clr_hist_rec.pmt_currency_code <> p_clr_hist_rec.bank_currency_code) THEN
1405
1406 l_prorated_clr_amt := AP_UTILITIES_PKG.AP_Round_Currency(
1407 l_prorated_pay_amt * p_clr_hist_rec.pmt_to_base_xrate,
1408 p_pay_hist_rec.bank_currency_code);
1409 ELSE
1410 l_prorated_clr_amt := l_prorated_pay_amt;
1411 END IF;
1412
1413 l_pad_rec.base_amt_at_prepay_clr_xrate := AP_Accounting_Pay_Pkg.Get_Base_Amount
1414 (l_prorated_clr_amt,
1415 p_clr_hist_rec.bank_currency_code,
1416 p_inv_rec.base_currency_code,
1417 p_clr_hist_rec.bank_to_base_xrate_type,
1418 p_clr_hist_rec.bank_to_base_xrate_date,
1419 p_clr_hist_rec.bank_to_base_xrate,
1420 l_curr_calling_sequence);
1421
1422
1423 l_pad_rec.base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
1424 (l_prorated_amount,
1425 p_inv_rec.invoice_currency_code,
1426 p_inv_rec.base_currency_code,
1427 p_inv_rec.exchange_rate_type,
1428 p_inv_rec.exchange_date,
1429 p_inv_rec.exchange_rate,
1430 l_curr_calling_sequence);
1431
1432
1433 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1434 l_log_msg := 'Calling procedure Prepay_Dist_Insert';
1435 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1436 END IF;
1437
1438
1439 IF p_inv_dist_rec.quantity_variance IS NOT NULL THEN
1440
1441 IF p_inv_dist_rec.amount = 0 THEN
1442 l_qty_variance := 0;
1443 ELSE
1444 l_qty_variance := AP_Utilities_PKG.AP_Round_Currency(
1445 ((p_inv_dist_rec.quantity_variance * l_prorated_amount) /
1446 p_inv_dist_rec.amount),
1447 p_inv_rec.invoice_currency_code);
1448 END IF;
1449
1450 IF p_inv_dist_rec.base_amount = 0 THEN
1451 l_base_qty_variance := 0;
1452 ELSE
1453 l_base_qty_variance := AP_Utilities_PKG.AP_Round_Currency(
1454 ((p_inv_dist_rec.base_quantity_variance
1455 * l_pad_rec.base_amount)
1456 / p_inv_dist_rec.base_amount),
1457 p_inv_rec.base_currency_code);
1458
1459 END IF;
1460 END IF;
1461
1462 IF p_inv_dist_rec.amount_variance IS NOT NULL THEN
1463
1464 IF p_inv_dist_rec.amount = 0 THEN
1465 l_amt_variance := 0;
1466 ELSE
1467 l_amt_variance := AP_Utilities_PKG.AP_Round_Currency(
1468 ((p_inv_dist_rec.amount_variance * l_prorated_amount) /
1469 p_inv_dist_rec.amount),
1470 p_inv_rec.invoice_currency_code);
1471 END IF;
1472
1473 IF p_inv_dist_rec.base_amount = 0 THEN
1474 l_base_amt_variance := 0;
1475 ELSE
1476 l_base_amt_variance := AP_Utilities_PKG.AP_Round_Currency(
1477 ((p_inv_dist_rec.base_amount_variance
1478 * l_pad_rec.base_amount)
1479 / p_inv_dist_rec.base_amount),
1480 p_inv_rec.base_currency_code);
1481 END IF;
1482 END IF;
1483
1484 l_pad_rec.quantity_variance := l_qty_variance;
1485 l_pad_rec.invoice_base_qty_variance := l_base_qty_variance;
1486 l_pad_rec.amount_variance := l_amt_variance;
1487 l_pad_rec.invoice_base_amt_variance := l_base_amt_variance;
1488
1489
1490 Prepay_Dist_Insert
1491 (l_pad_rec,
1492 l_curr_calling_sequence);
1493
1494 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1495 l_log_msg := 'Procedure Prepay_Dist_Insert executed';
1496 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1497 END IF;
1498
1499
1500 IF (p_prepay_dist_rec.prepay_tax_diff_amount <> 0) THEN
1501
1502
1503 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1504 l_log_msg := 'Calling procedure Prepay_Dist_Tax_Diff';
1505 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1506 END IF;
1507
1508 -- Creating the tax diff distributions
1509 Prepay_Dist_Tax_Diff
1510 (p_pay_hist_rec,
1511 p_clr_hist_rec,
1512 p_inv_rec,
1513 p_prepay_inv_rec,
1514 p_prepay_hist_rec,
1515 p_prepay_dist_rec,
1516 p_inv_dist_rec,
1517 p_calc_mode,
1518 l_curr_calling_sequence);
1519
1520 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1521 l_log_msg := 'Procedure Prepay_Dist_Tax_Diff executed';
1522 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1523 END IF;
1524
1525
1526 END IF;
1527
1528
1529 IF (p_inv_dist_rec.po_distribution_id IS NOT NULL AND
1530 p_inv_rec.invoice_currency_code <> p_inv_rec.base_currency_code) THEN
1531
1532 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1533 l_log_msg := 'Calling procedure Prepay_Dist_ERV';
1534 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1535 END IF;
1536
1537 -- Creating ERV distributions
1538 Prepay_Dist_ERV
1539 (p_pay_hist_rec,
1540 p_clr_hist_rec,
1541 p_inv_rec,
1542 p_prepay_inv_rec,
1543 p_prepay_hist_rec,
1544 p_prepay_dist_rec,
1545 p_inv_dist_rec,
1546 l_prorated_amount,
1547 l_curr_calling_sequence);
1548
1549 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1550 l_log_msg := 'Procedure Prepay_Dist_ERV executed';
1551 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1552 END IF;
1553
1554 END IF;
1555
1556 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1557 l_log_msg := 'Procedure Prepay_Dist_Insert executed';
1558 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1559 END IF;
1560
1561 EXCEPTION
1562 WHEN OTHERS THEN
1563 IF (SQLCODE <> -20001) THEN
1564 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1565 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1566 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1567 END IF;
1568 APP_EXCEPTION.RAISE_EXCEPTION;
1569
1570 END Prepay_Dist_Proc;
1571
1572
1573
1574 ---------------------------------------------------------------------
1575 -- Procedure Prepay_Dist_Tax_Diff
1576 -- This procedure prorates the tax difference amounts for each
1577 -- distribution and inserts the calculated values into prepayment
1578 -- application distribution table
1579 ---------------------------------------------------------------------
1580
1581 PROCEDURE Prepay_Dist_Tax_Diff
1582 (p_pay_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
1583 ,p_clr_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
1584 ,p_inv_rec IN ap_accounting_pay_pkg.r_invoices_info
1585 ,p_prepay_inv_rec IN ap_accounting_pay_pkg.r_invoices_info
1586 ,p_prepay_hist_rec IN r_prepay_hist_info
1587 ,p_prepay_dist_rec IN r_prepay_dist_info
1588 ,p_inv_dist_rec IN ap_accounting_pay_pkg.r_inv_dist_info
1589 ,p_calc_mode IN VARCHAR2
1590 ,p_calling_sequence IN VARCHAR2
1591 ) IS
1592
1593
1594 l_curr_calling_sequence VARCHAR2(2000);
1595 l_prorated_amount NUMBER;
1596 l_prorated_pay_amt NUMBER;
1597 l_prorated_clr_amt NUMBER;
1598
1599 l_pad_rec AP_PREPAY_APP_DISTS%ROWTYPE;
1600
1601 -- Logging Infra:
1602 l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Dist_Tax_Diff';
1603 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1604
1605
1606 BEGIN
1607
1608 l_curr_calling_sequence := 'AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Tax_Diff<- ' ||
1609 p_calling_sequence;
1610
1611 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1612 l_log_msg := 'Begin of procedure '|| l_procedure_name;
1613 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1614 END IF;
1615
1616
1617 IF p_calc_mode = 'A' THEN
1618 IF g_total_dist_amt = p_inv_rec.invoice_amount THEN -- last dist rec
1619
1620 -- To avoid rounding, massage the last (biggest) line
1621 l_prorated_amount := p_prepay_dist_rec.prepay_tax_diff_amount - g_total_tax_diff_amt;
1622 ELSE
1623
1624 IF p_inv_rec.invoice_amount = 0 THEN
1625 l_prorated_amount := 0;
1626
1627 ELSE
1628 l_prorated_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
1629 (p_prepay_dist_rec.prepay_tax_diff_amount * p_inv_dist_rec.amount
1630 / p_inv_rec.invoice_amount,
1631 p_inv_rec.invoice_currency_code);
1632
1633 END IF;
1634 END IF;
1635
1636 ELSE
1637
1638 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1639 l_log_msg := 'Calculating prorated amount for cascade adjustment';
1640 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1641 END IF;
1642
1643 IF p_inv_rec.invoice_amount = 0 THEN
1644 l_prorated_amount := 0;
1645 ELSE
1646
1647 -- In case of cascade events we will recalculate the prorated amount and subtract
1648 -- this amount from the already calculated amount previously so that this would
1649 -- give us the amount that needs to be adjusted
1650 l_prorated_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
1651 (((p_inv_dist_rec.amount * p_prepay_dist_rec.prepay_tax_diff_amount)
1652 / p_inv_rec.invoice_amount)
1653 - AP_Accounting_Pay_Pkg.get_casc_tax_diff_sum
1654 (p_inv_dist_rec.invoice_distribution_id,
1655 p_prepay_dist_rec.invoice_distribution_id,
1656 l_curr_calling_sequence),
1657 p_inv_rec.invoice_currency_code);
1658
1659 END IF;
1660 END IF;
1661
1662 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1663 l_log_msg := 'Value for l_prorated_amount = '|| l_prorated_amount;
1664 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1665 END IF;
1666
1667
1668 g_total_tax_diff_amt := g_total_tax_diff_amt + l_prorated_amount;
1669
1670
1671 -- Populate prepay appl dist rec
1672
1673 l_pad_rec.prepay_history_id := p_prepay_hist_rec.prepay_history_id;
1674 l_pad_rec.prepay_dist_lookup_code := 'TAX DIFF';
1675 l_pad_rec.invoice_distribution_id := p_inv_dist_rec.invoice_distribution_id;
1676 l_pad_rec.prepay_app_distribution_id := p_prepay_dist_rec.invoice_distribution_id;
1677 l_pad_rec.accounting_event_id := p_prepay_dist_rec.accounting_event_id;
1678
1679 l_pad_rec.amount := l_prorated_amount;
1680
1681 l_pad_rec.prepay_exchange_date := p_prepay_inv_rec.exchange_date;
1682 l_pad_rec.prepay_pay_exchange_date := p_pay_hist_rec.pmt_to_base_xrate_date;
1683 l_pad_rec.prepay_clr_exchange_date := p_clr_hist_rec.bank_to_base_xrate_date;
1684
1685 l_pad_rec.prepay_exchange_rate := p_prepay_inv_rec.exchange_rate;
1686 l_pad_rec.prepay_pay_exchange_rate := p_pay_hist_rec.pmt_to_base_xrate;
1687 l_pad_rec.prepay_clr_exchange_rate := p_clr_hist_rec.bank_to_base_xrate;
1688
1689 l_pad_rec.prepay_exchange_rate_type := p_prepay_inv_rec.exchange_rate_type;
1690 l_pad_rec.prepay_pay_exchange_rate_type := p_pay_hist_rec.pmt_to_base_xrate_type;
1691 l_pad_rec.prepay_clr_exchange_rate_type := p_clr_hist_rec.bank_to_base_xrate_type;
1692
1693
1694 l_pad_rec.base_amt_at_prepay_xrate := AP_Accounting_Pay_Pkg.Get_Base_Amount
1695 (l_prorated_amount,
1696 p_prepay_inv_rec.invoice_currency_code,
1697 p_inv_rec.base_currency_code,
1698 p_prepay_inv_rec.exchange_rate_type,
1699 p_prepay_inv_rec.exchange_date,
1700 p_prepay_inv_rec.exchange_rate,
1701 l_curr_calling_sequence);
1702
1703 IF (p_inv_rec.invoice_currency_code <> p_pay_hist_rec.pmt_currency_code) THEN
1704 l_prorated_pay_amt := l_prorated_amount * p_inv_rec.payment_cross_rate;
1705 ELSE
1706 l_prorated_pay_amt := l_prorated_amount;
1707 END IF;
1708
1709
1710 l_pad_rec.base_amt_at_prepay_pay_xrate := AP_Accounting_Pay_Pkg.Get_Base_Amount
1711 (l_prorated_pay_amt,
1712 p_pay_hist_rec.pmt_currency_code,
1713 p_inv_rec.base_currency_code,
1714 p_pay_hist_rec.pmt_to_base_xrate_type,
1715 p_pay_hist_rec.pmt_to_base_xrate_date,
1716 p_pay_hist_rec.pmt_to_base_xrate,
1717 l_curr_calling_sequence);
1718
1719 IF (p_clr_hist_rec.pmt_currency_code <> p_clr_hist_rec.bank_currency_code) THEN
1720
1721 l_prorated_clr_amt := AP_UTILITIES_PKG.AP_Round_Currency(
1722 l_prorated_pay_amt * p_clr_hist_rec.pmt_to_base_xrate,
1723 p_pay_hist_rec.bank_currency_code);
1724 ELSE
1725 l_prorated_clr_amt := l_prorated_pay_amt;
1726 END IF;
1727
1728 l_pad_rec.base_amt_at_prepay_clr_xrate := AP_Accounting_Pay_Pkg.Get_Base_Amount
1729 (l_prorated_clr_amt,
1730 p_clr_hist_rec.bank_currency_code,
1731 p_inv_rec.base_currency_code,
1732 p_clr_hist_rec.bank_to_base_xrate_type,
1733 p_clr_hist_rec.bank_to_base_xrate_date,
1734 p_clr_hist_rec.bank_to_base_xrate,
1735 l_curr_calling_sequence);
1736
1737
1738 l_pad_rec.base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
1739 (l_prorated_amount,
1740 p_inv_rec.invoice_currency_code,
1741 p_inv_rec.base_currency_code,
1742 p_inv_rec.exchange_rate_type,
1743 p_inv_rec.exchange_date,
1744 p_inv_rec.exchange_rate,
1745 l_curr_calling_sequence);
1746
1747
1748 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1749 l_log_msg := 'Calling procedure Prepay_Dist_Insert';
1750 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1751 END IF;
1752
1753 Prepay_Dist_Insert
1754 (l_pad_rec,
1755 l_curr_calling_sequence);
1756
1757
1758 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1759 l_log_msg := 'Procedure Prepay_Dist_Insert executed';
1760 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1761 END IF;
1762
1763 EXCEPTION
1764 WHEN OTHERS THEN
1765 IF (SQLCODE <> -20001) THEN
1766 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1767 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1768 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1769 END IF;
1770 APP_EXCEPTION.RAISE_EXCEPTION;
1771
1772 END Prepay_Dist_Tax_Diff;
1773
1774
1775
1776
1777 ---------------------------------------------------------------------
1778 -- Procedure Prepay_Dist_ERV
1779 -- This procedure calculates the ERV base amounts for the ERV distributions
1780 -- and inserts the calculated values into prepay appl payment dists table
1781 ---------------------------------------------------------------------
1782
1783 PROCEDURE Prepay_Dist_ERV
1784 (p_pay_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
1785 ,p_clr_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
1786 ,p_inv_rec IN ap_accounting_pay_pkg.r_invoices_info
1787 ,p_prepay_inv_rec IN ap_accounting_pay_pkg.r_invoices_info
1788 ,p_prepay_hist_rec IN r_prepay_hist_info
1789 ,p_prepay_dist_rec IN r_prepay_dist_info
1790 ,p_inv_dist_rec IN ap_accounting_pay_pkg.r_inv_dist_info
1791 ,p_prorated_amount IN NUMBER
1792 ,p_calling_sequence IN VARCHAR2
1793 ) IS
1794
1795 l_curr_calling_sequence VARCHAR2(2000);
1796 l_po_exchange_rate NUMBER;
1797 l_po_pay_exchange_rate NUMBER;
1798 l_pay_erv_amount NUMBER;
1799 l_clr_erv_amount NUMBER;
1800 l_inv_erv_amount NUMBER;
1801 l_pad_rec AP_PREPAY_APP_DISTS%ROWTYPE;
1802
1803 -- Logging Infra:
1804 l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Dist_ERV';
1805 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1806
1807 BEGIN
1808
1809
1810 l_curr_calling_sequence := 'AP_ACCTG_PREPAY_DIST_PKG.PrePay_Dist_ERV<- ' ||
1811 p_calling_sequence;
1812
1813
1814 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1815 l_log_msg := 'Begin of procedure '|| l_procedure_name;
1816 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1817 END IF;
1818
1819
1820 IF p_inv_dist_rec.rcv_transaction_id IS NOT NULL THEN
1821
1822 SELECT Currency_Conversion_Rate
1823 INTO l_po_exchange_rate
1824 FROM rcv_transactions
1825 WHERE transaction_id = p_inv_dist_rec.rcv_transaction_id;
1826
1827 ELSE
1828
1829 SELECT Rate
1830 INTO l_po_exchange_rate
1831 FROM PO_Distributions_All
1832 WHERE PO_Distribution_ID = p_inv_dist_rec.PO_Distribution_ID;
1833
1834 END IF;
1835
1836 IF p_inv_rec.invoice_currency_code <> p_inv_rec.payment_currency_code THEN
1837 l_po_pay_exchange_rate := l_po_exchange_rate / p_inv_rec.payment_cross_rate;
1838 ELSE
1839 l_po_pay_exchange_rate := l_po_exchange_rate;
1840 END IF;
1841
1842
1843 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1844 l_log_msg := 'Value of l_po_pay_exchange_rate = '||l_po_pay_exchange_rate;
1845 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1846 END IF;
1847
1848
1849 /* For Cash Basis ERV is Difference between Payment Exchange Rate and
1850 either Receipt Exchange rate or PO distributions exchange rate */
1851
1852 l_pay_erv_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
1853 (p_pay_hist_rec.pmt_to_base_xrate - l_po_pay_exchange_rate) *
1854 p_prorated_amount, p_pay_hist_rec.pmt_currency_code);
1855
1856
1857 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1858 l_log_msg := 'Value of l_pay_erv_amount = '||l_pay_erv_amount;
1859 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1860 END IF;
1861
1862
1863 /* If the payment accounting is at the at the clearing time, then ERV should be
1864 calculated based on the difference between Prepay payment clearing exchange rate
1865 and either Receipt Exchange rate or PO distributions exchange rate */
1866
1867 IF p_clr_hist_rec.pmt_currency_code IS NOT NULL THEN -- Bug 5701788.
1868 l_clr_erv_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
1869 (p_clr_hist_rec.pmt_to_base_xrate - l_po_pay_exchange_rate) *
1870 p_inv_dist_rec.amount, p_clr_hist_rec.pmt_currency_code);
1871 END IF;
1872
1873 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1874 l_log_msg := 'Value of l_clr_erv_amount = '||l_clr_erv_amount;
1875 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1876 END IF;
1877
1878
1879 /* In order to back out the encumbrance entries correctly during cash basis
1880 we need to calculate ERV based on the difference between the Invoice
1881 Exchange Rate and either Receipt Exchange rate or PO distributions
1882 exchange rate. This calculated ERV amount will be stored in the
1883 invoice_dist_base_amount column */
1884
1885 l_inv_erv_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
1886 (p_inv_rec.exchange_rate - l_po_exchange_rate) *
1887 p_prorated_amount, p_inv_rec.invoice_currency_code);
1888
1889 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1890 l_log_msg := 'Value of l_inv_erv_amount = '||l_inv_erv_amount;
1891 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1892 END IF;
1893
1894
1895 IF (p_inv_dist_rec.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX')) THEN
1896 l_pad_rec.prepay_dist_lookup_code := 'TAX EXCHANGE RATE VARIANCE';
1897 ELSE
1898 l_pad_rec.prepay_dist_lookup_code := 'EXCHANGE RATE VARIANCE';
1899 END IF;
1900
1901 l_pad_rec.prepay_history_id := p_prepay_hist_rec.prepay_history_id;
1902 l_pad_rec.invoice_distribution_id := p_inv_dist_rec.invoice_distribution_id;
1903 l_pad_rec.prepay_app_distribution_id := p_prepay_dist_rec.invoice_distribution_id;
1904 l_pad_rec.accounting_event_id := p_prepay_dist_rec.accounting_event_id;
1905
1906 l_pad_rec.amount := 0;
1907
1908 l_pad_rec.prepay_exchange_date := p_prepay_inv_rec.exchange_date;
1909 l_pad_rec.prepay_pay_exchange_date := p_pay_hist_rec.pmt_to_base_xrate_date;
1910 l_pad_rec.prepay_clr_exchange_date := p_clr_hist_rec.bank_to_base_xrate_date;
1911
1912 l_pad_rec.prepay_exchange_rate := p_prepay_inv_rec.exchange_rate;
1913 l_pad_rec.prepay_pay_exchange_rate := p_pay_hist_rec.pmt_to_base_xrate;
1914 l_pad_rec.prepay_clr_exchange_rate := p_clr_hist_rec.bank_to_base_xrate;
1915
1916 l_pad_rec.prepay_exchange_rate_type := p_prepay_inv_rec.exchange_rate_type;
1917 l_pad_rec.prepay_pay_exchange_rate_type := p_pay_hist_rec.pmt_to_base_xrate_type;
1918 l_pad_rec.prepay_clr_exchange_rate_type := p_clr_hist_rec.bank_to_base_xrate_type;
1919
1920
1921 l_pad_rec.base_amt_at_prepay_xrate := 0;
1922 l_pad_rec.base_amt_at_prepay_pay_xrate := l_pay_erv_amount;
1923 l_pad_rec.base_amt_at_prepay_clr_xrate := l_clr_erv_amount;
1924 l_pad_rec.base_amount := 0;
1925
1926 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1927 l_log_msg := 'Calling procedure Prepay_Dist_Insert';
1928 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1929 END IF;
1930
1931 Prepay_Dist_Insert
1932 (l_pad_rec,
1933 l_curr_calling_sequence);
1934
1935 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1936 l_log_msg := 'Procedure Prepay_Dist_Insert executed';
1937 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1938 END IF;
1939
1940
1941 EXCEPTION
1942 WHEN OTHERS THEN
1943 IF (SQLCODE <> -20001) THEN
1944 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1945 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1946 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1947 END IF;
1948 APP_EXCEPTION.RAISE_EXCEPTION;
1949
1950 END Prepay_Dist_ERV;
1951
1952
1953
1954 ---------------------------------------------------------------------
1955 -- Procedure Prepay_Dist_Reverse
1956 -- This procedure reverses the prepayment application payment distributions
1957 -- of the prepayment unapplications.
1958 --
1959 ---------------------------------------------------------------------
1960 -- Bug 6698125. Added p_xla_event_rec parameter
1961 -- Bug 7134020. Added p_inv_dist_id parameter
1962 PROCEDURE Prepay_Dist_Reverse
1963 (p_prepay_hist_rec IN r_prepay_hist_info
1964 ,p_prepay_reversal_id IN NUMBER
1965 ,P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
1966 ,p_inv_reversal_id IN NUMBER
1967 ,p_inv_dist_id IN NUMBER
1968 ,p_prepay_inv_dist_id IN NUMBER
1969 ,p_calling_sequence IN VARCHAR2
1970 ) IS
1971
1972 l_curr_calling_sequence VARCHAR2(2000);
1973
1974 -- Logging Infra:
1975 l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Dist_Reverse';
1976 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1977
1978
1979 BEGIN
1980
1981 l_curr_calling_sequence := 'AP_ACCTG_PAY_DIST_PKG.Prepay_Dist_Reverse<-' ||
1982 p_calling_sequence;
1983
1984 -- Logging Infra: Procedure level
1985 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1986 l_log_msg := 'Begin of procedure '|| l_procedure_name;
1987 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1988 END IF;
1989
1990 -- Bug 6698125. Added if condition to correctly reverse the prepay app
1991 -- distributions based on if reversed for prepayment unapplication or
1992 -- prepayment application adjusted events.
1993 IF p_prepay_reversal_id IS NOT NULL THEN
1994 INSERT INTO AP_Prepay_App_Dists
1995 (Prepay_App_Dist_ID,
1996 Prepay_Dist_Lookup_Code,
1997 Invoice_Distribution_ID,
1998 Prepay_App_Distribution_ID,
1999 Accounting_Event_ID,
2000 Prepay_History_ID,
2001 Prepay_Exchange_Date,
2002 Prepay_Pay_Exchange_Date,
2003 Prepay_Clr_Exchange_Date,
2004 Prepay_Exchange_Rate,
2005 Prepay_Pay_Exchange_Rate,
2006 Prepay_Clr_Exchange_Rate,
2007 Prepay_Exchange_Rate_Type,
2008 Prepay_Pay_Exchange_Rate_Type,
2009 Prepay_Clr_Exchange_Rate_Type,
2010 Reversed_Prepay_App_Dist_ID,
2011 Amount,
2012 Base_Amt_At_Prepay_XRate,
2013 Base_Amt_At_Prepay_Pay_XRate,
2014 Base_Amt_At_Prepay_Clr_XRate,
2015 Base_Amount,
2016 AWT_Related_ID,
2017 PA_Addition_Flag,
2018 Quantity_Variance,
2019 Invoice_Base_Qty_Variance,
2020 Amount_Variance,
2021 Invoice_Base_Amt_Variance,
2022 Created_By,
2023 Creation_Date,
2024 Last_Update_Date,
2025 Last_Updated_By,
2026 Last_Update_Login,
2027 Program_Application_ID,
2028 Program_ID,
2029 Program_Update_Date,
2030 Request_ID
2031 )
2032 SELECT AP_Prepay_App_Dists_S.nextval,
2033 APAD.Prepay_Dist_Lookup_Code,
2034 APAD.Invoice_Distribution_ID,
2035 p_prepay_inv_dist_id,
2036 NULL, --p_xla_event_rec.event_id,
2037 p_prepay_hist_rec.prepay_history_id,
2038 APAD.Prepay_Exchange_Date,
2039 APAD.Prepay_Pay_Exchange_Date,
2040 APAD.Prepay_Clr_Exchange_Date,
2041 APAD.Prepay_Exchange_Rate,
2042 APAD.Prepay_Pay_Exchange_Rate,
2043 APAD.Prepay_Clr_Exchange_Rate,
2044 APAD.Prepay_Exchange_Rate_Type,
2045 APAD.Prepay_Pay_Exchange_Rate_Type,
2046 APAD.Prepay_Clr_Exchange_Rate_Type,
2047 APAD.Prepay_App_Dist_ID,
2048 -1 * APAD.Amount,
2049 -1 * APAD.Base_Amt_At_Prepay_XRate,
2050 -1 * APAD.Base_Amt_At_Prepay_Pay_XRate,
2051 -1 * APAD.Base_Amt_At_Prepay_Clr_XRate,
2052 -1 * APAD.Base_Amount,
2053 APAD.AWT_Related_ID,
2054 'N',
2055 APAD.Quantity_Variance,
2056 APAD.Invoice_Base_Qty_Variance,
2057 APAD.Amount_Variance,
2058 APAD.Invoice_Base_Amt_Variance,
2059 FND_GLOBAL.User_ID,
2060 SYSDATE,
2061 SYSDATE,
2062 FND_GLOBAL.User_ID,
2063 FND_GLOBAL.User_ID,
2064 FND_GLOBAL.Prog_Appl_ID,
2065 FND_GLOBAL.Conc_Program_ID,
2066 SYSDATE,
2067 FND_GLOBAL.Conc_Request_ID
2068 FROM AP_Prepay_App_Dists APAD
2069 WHERE Prepay_App_Distribution_ID = P_Prepay_Reversal_ID;
2070
2071 ELSIF p_inv_reversal_id IS NOT NULL THEN
2072
2073 INSERT INTO AP_Prepay_App_Dists
2074 (Prepay_App_Dist_ID,
2075 Prepay_Dist_Lookup_Code,
2076 Invoice_Distribution_ID,
2077 Prepay_App_Distribution_ID,
2078 Accounting_Event_ID,
2079 Prepay_History_ID,
2080 Prepay_Exchange_Date,
2081 Prepay_Pay_Exchange_Date,
2082 Prepay_Clr_Exchange_Date,
2083 Prepay_Exchange_Rate,
2084 Prepay_Pay_Exchange_Rate,
2085 Prepay_Clr_Exchange_Rate,
2086 Prepay_Exchange_Rate_Type,
2087 Prepay_Pay_Exchange_Rate_Type,
2088 Prepay_Clr_Exchange_Rate_Type,
2089 Reversed_Prepay_App_Dist_ID,
2090 Amount,
2091 Base_Amt_At_Prepay_XRate,
2092 Base_Amt_At_Prepay_Pay_XRate,
2093 Base_Amt_At_Prepay_Clr_XRate,
2094 Base_Amount,
2095 AWT_Related_ID,
2096 PA_Addition_Flag,
2097 Quantity_Variance,
2098 Invoice_Base_Qty_Variance,
2099 Amount_Variance,
2100 Invoice_Base_Amt_Variance,
2101 Created_By,
2102 Creation_Date,
2103 Last_Update_Date,
2104 Last_Updated_By,
2105 Last_Update_Login,
2106 Program_Application_ID,
2107 Program_ID,
2108 Program_Update_Date,
2109 Request_ID
2110 )
2111 SELECT AP_Prepay_App_Dists_S.nextval,
2112 APAD.Prepay_Dist_Lookup_Code,
2113 p_inv_dist_id, -- Bug 7134020
2114 APAD.Prepay_App_Distribution_ID,
2115 p_xla_event_rec.event_id,
2116 p_prepay_hist_rec.prepay_history_id,
2117 APAD.Prepay_Exchange_Date,
2118 APAD.Prepay_Pay_Exchange_Date,
2119 APAD.Prepay_Clr_Exchange_Date,
2120 APAD.Prepay_Exchange_Rate,
2121 APAD.Prepay_Pay_Exchange_Rate,
2122 APAD.Prepay_Clr_Exchange_Rate,
2123 APAD.Prepay_Exchange_Rate_Type,
2124 APAD.Prepay_Pay_Exchange_Rate_Type,
2125 APAD.Prepay_Clr_Exchange_Rate_Type,
2126 APAD.Prepay_App_Dist_ID,
2127 -1 * APAD.Amount,
2128 -1 * APAD.Base_Amt_At_Prepay_XRate,
2129 -1 * APAD.Base_Amt_At_Prepay_Pay_XRate,
2130 -1 * APAD.Base_Amt_At_Prepay_Clr_XRate,
2131 -1 * APAD.Base_Amount,
2132 APAD.AWT_Related_ID,
2133 'N',
2134 APAD.Quantity_Variance,
2135 APAD.Invoice_Base_Qty_Variance,
2136 APAD.Amount_Variance,
2137 APAD.Invoice_Base_Amt_Variance,
2138 FND_GLOBAL.User_ID,
2139 SYSDATE,
2140 SYSDATE,
2141 FND_GLOBAL.User_ID,
2142 FND_GLOBAL.User_ID,
2143 FND_GLOBAL.Prog_Appl_ID,
2144 FND_GLOBAL.Conc_Program_ID,
2145 SYSDATE,
2146 FND_GLOBAL.Conc_Request_ID
2147 FROM AP_Prepay_App_Dists APAD
2148 WHERE Invoice_Distribution_ID = P_Inv_Reversal_ID;
2149
2150 END IF;
2151
2152 -- Logging Infra: Procedure level
2153 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2154 l_log_msg := 'End of procedure '|| l_procedure_name;
2155 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
2156 END IF;
2157
2158
2159 EXCEPTION
2160 WHEN OTHERS THEN
2161 IF (SQLCODE <> -20001) THEN
2162 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2163 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2164 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2165 END IF;
2166 APP_EXCEPTION.RAISE_EXCEPTION;
2167
2168 END Prepay_Dist_Reverse;
2169
2170
2171
2172 ----------------------------------------------------------------------------------
2173 -- PROCEDURE Prepay_Dist_Insert
2174 -- This procedure is used to insert the prepay application payment distributions
2175 -- into the ap_prepay_app_dists table
2176 ----------------------------------------------------------------------------------
2177
2178 PROCEDURE Prepay_Dist_Insert
2179 (P_PAD_Rec IN AP_PREPAY_APP_DISTS%ROWTYPE
2180 ,P_Calling_Sequence IN VARCHAR2
2181 ) IS
2182
2183 l_curr_calling_sequence VARCHAR2(2000);
2184
2185 -- Logging Infra:
2186 l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Dist_Insert';
2187 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2188
2189 BEGIN
2190
2191 l_curr_calling_sequence := 'AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Insert<- ' ||
2192 P_Calling_Sequence;
2193
2194 -- Logging Infra: Procedure level
2195 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2196 l_log_msg := 'Begin of procedure '|| l_procedure_name;
2197 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2198 END IF;
2199
2200
2201 INSERT INTO AP_Prepay_App_Dists
2202 (Prepay_App_Dist_ID,
2203 Prepay_Dist_Lookup_Code,
2204 Invoice_Distribution_ID,
2205 Prepay_App_Distribution_ID,
2206 Accounting_Event_ID,
2207 Prepay_History_ID,
2208 Prepay_Exchange_Date,
2209 Prepay_Pay_Exchange_Date,
2210 Prepay_Clr_Exchange_Date,
2211 Prepay_Exchange_Rate,
2212 Prepay_Pay_Exchange_Rate,
2213 Prepay_Clr_Exchange_Rate,
2214 Prepay_Exchange_Rate_Type,
2215 Prepay_Pay_Exchange_Rate_Type,
2216 Prepay_Clr_Exchange_Rate_Type,
2217 Reversed_Prepay_App_Dist_ID,
2218 Amount,
2219 Base_Amt_At_Prepay_XRate,
2220 Base_Amt_At_Prepay_Pay_XRate,
2221 Base_Amt_At_Prepay_Clr_XRate,
2222 Base_Amount,
2223 AWT_Related_ID,
2224 PA_Addition_Flag,
2225 Quantity_Variance,
2226 Invoice_Base_Qty_Variance,
2227 Amount_Variance,
2228 Invoice_Base_Amt_Variance,
2229 Created_By,
2230 Creation_Date,
2231 Last_Update_Date,
2232 Last_Updated_By,
2233 Last_Update_Login,
2234 Program_Application_ID,
2235 Program_ID,
2236 Program_Update_Date,
2237 Request_ID
2238 )
2239 VALUES (AP_Prepay_App_Dists_S.nextval,
2240 P_PAD_Rec.Prepay_Dist_Lookup_Code,
2241 P_PAD_Rec.Invoice_Distribution_ID,
2242 P_PAD_Rec.Prepay_App_Distribution_ID,
2243 P_PAD_Rec.Accounting_Event_ID,
2244 P_PAD_Rec.Prepay_History_ID,
2245 P_PAD_Rec.Prepay_Exchange_Date,
2246 P_PAD_Rec.Prepay_Pay_Exchange_Date,
2247 P_PAD_Rec.Prepay_Clr_Exchange_Date,
2248 P_PAD_Rec.Prepay_Exchange_Rate,
2249 P_PAD_Rec.Prepay_Pay_Exchange_Rate,
2250 P_PAD_Rec.Prepay_Clr_Exchange_Rate,
2251 P_PAD_Rec.Prepay_Exchange_Rate_Type,
2252 P_PAD_Rec.Prepay_Pay_Exchange_Rate_Type,
2253 P_PAD_Rec.Prepay_Clr_Exchange_Rate_Type,
2254 P_PAD_Rec.Reversed_Prepay_App_Dist_ID,
2255 P_PAD_Rec.Amount,
2256 P_PAD_Rec.Base_Amt_At_Prepay_XRate,
2257 P_PAD_Rec.Base_Amt_At_Prepay_Pay_XRate,
2258 P_PAD_Rec.Base_Amt_At_Prepay_Clr_XRate,
2259 P_PAD_Rec.Base_Amount,
2260 P_PAD_Rec.AWT_Related_ID,
2261 'N',
2262 P_PAD_Rec.Quantity_Variance,
2263 P_PAD_Rec.Invoice_Base_Qty_Variance,
2264 P_PAD_Rec.Amount_Variance,
2265 P_PAD_Rec.Invoice_Base_Amt_Variance,
2266 FND_GLOBAL.User_ID,
2267 SYSDATE,
2268 SYSDATE,
2269 FND_GLOBAL.User_ID,
2270 FND_GLOBAL.User_ID,
2271 FND_GLOBAL.Prog_Appl_ID,
2272 FND_GLOBAL.Conc_Program_ID,
2273 SYSDATE,
2274 FND_GLOBAL.Conc_Request_ID
2275 );
2276
2277 -- Logging Infra: Procedure level
2278 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2279 l_log_msg := 'End of procedure '|| l_procedure_name;
2280 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
2281 END IF;
2282
2283
2284 EXCEPTION
2285 WHEN OTHERS THEN
2286 IF (SQLCODE <> -20001) THEN
2287 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2288 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2289 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2290 END IF;
2291 APP_EXCEPTION.RAISE_EXCEPTION;
2292
2293 END Prepay_Dist_Insert;
2294
2295 --Bug5373620 Added following procedure
2296 -------------------------------------------------------------------------------
2297 -- PROCEDURE Delete_Hist_Dists
2298 -- Procedure to delete the Prepay history distributions and prepayment
2299 -- application distributions.
2300 --
2301 --------------------------------------------------------------------------------
2302 PROCEDURE Delete_Hist_Dists
2303 (P_invoice_id IN NUMBER,
2304 P_Calling_Sequence IN VARCHAR2
2305 ) IS
2306
2307 l_curr_calling_sequence VARCHAR2(2000);
2308
2309 -- Logging Infra:
2310 l_procedure_name CONSTANT VARCHAR2(30) := 'Delete_Hist_Dists';
2311 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2312
2313 BEGIN
2314
2315 l_curr_calling_sequence := 'AP_Acctg_Prepay_Dist_Pkg.Delete_hist_dists<- ' ||
2316 p_calling_sequence;
2317
2318 -- Logging Infra: Procedure level
2319 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2320 l_log_msg := 'Begin of procedure '|| l_procedure_name;
2321 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2322 END IF;
2323
2324
2325
2326
2327
2328 -- Bug fix 5634515
2329 -- rewrite the query to delete the correct prepay application dist record.
2330 --DELETE FROM AP_Prepay_App_Dists apad
2331 --WHERE prepay_history_id in (
2332 -- select aph.prepay_history_id
2333 -- from ap_prepay_history_all aph,ap_prepay_app_dists apad1
2334 -- where aph.prepay_history_id=apad1.prepay_history_id
2335 -- and aph.accounting_event_id is null
2336 -- and aph.bc_event_id is not null
2337 -- and aph.invoice_id=p_invoice_id
2338 -- and not exists(select 1 from ap_invoice_distributions_all aid
2339 -- where aid.invoice_distribution_id=
2340 -- apad1.prepay_app_distribution_id
2341 -- and nvl(aid.encumbered_flag,'N')='Y'));
2342
2343
2344 --- delete from AP_Prepay_history_all is placed after delete from AP_Prepay_App_Dists
2345 --- due to bug 7264479
2346
2347 DELETE FROM AP_Prepay_App_Dists apad
2348 WHERE prepay_history_id in (
2349 select aph.prepay_history_id
2350 from ap_prepay_history_all aph,ap_prepay_app_dists apad1
2351 where aph.prepay_history_id=apad1.prepay_history_id
2352 and aph.accounting_event_id is null
2353 and (aph.bc_event_id is null or
2354 (aph.bc_event_id is not null
2355 and aph.invoice_id=p_invoice_id
2356 and not exists(select 1 from ap_invoice_distributions_all aid
2357 -- Bug fix 5634515
2358 -- replace invoice_distribution_id with prepay_app_distribution_id
2359 -- since the original one can only derive other invoice lines except
2360 -- prepayment application line.
2361 where aid.invoice_distribution_id=
2362 apad1.prepay_app_distribution_id
2363 --apad1.invoice_distribution_id
2364 and nvl(aid.encumbered_flag,'N')='Y'))));
2365
2366
2367 -- Bug fix 5634515
2368 -- rewrite the query to delete the correct prepay history record.
2369 --DELETE FROM AP_Prepay_history_all aph
2370 --WHERE Accounting_Event_ID is null
2371 -- AND bc_event_id is not null
2372 -- AND Invoice_id =p_invoice_id
2373 -- AND not exists(select 1 from AP_Prepay_App_Dists apad,
2374 -- ap_invoice_distributions_all aid
2375 -- where aid.invoice_distribution_id=
2376 -- apad.invoice_distribution_id
2377 -- and aph.prepay_history_id=apad.prepay_history_id
2378 -- and nvl(encumbered_flag,'N')='Y');
2379
2380 DELETE FROM AP_Prepay_history_all aph
2381 WHERE Accounting_Event_ID is null
2382 AND (bc_event_id is null or
2383 (bc_event_id is not null
2384 AND Invoice_id = p_invoice_id
2385 AND not exists(select 1 from AP_Prepay_App_Dists apad,
2386 ap_invoice_distributions_all aid
2387 -- Bug fix 5634515
2388 -- replace invoice_distribution_id with prepay_app_distribution_id
2389 -- since the original one can only derive other invoice lines except
2390 -- prepayment application line.
2391 where aid.invoice_distribution_id= apad.prepay_app_distribution_id
2392 --apad.invoice_distribution_id
2393 and aph.prepay_history_id=apad.prepay_history_id
2394 and nvl(encumbered_flag,'N')='Y')));
2395
2396 EXCEPTION
2397
2398 WHEN OTHERS THEN
2399 IF (SQLCODE <> -20001) THEN
2400 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2401 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2402 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2403 END IF;
2404 APP_EXCEPTION.RAISE_EXCEPTION;
2405
2406 END Delete_Hist_Dists;
2407 END AP_ACCTG_PREPAY_DIST_PKG;