[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.66 2011/12/13 10:16:01 kpasikan 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 G_Total_Inv_Amount NUMBER := 0; --Bug8244163
8 G_Total_Dist_Amount NUMBER := 0; --Bug8244163
9 G_Total_awt_amount NUMBER := 0; --Bug9106549
10
11 -- Logging Infra
12 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
13 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
14 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
15 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
16 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
17 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
18 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
19 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'AP.PLSQL.AP_ACCTG_PREPAY_DIST_PKG.';
20 -- Logging Infra
21
22
23 -------------------------------------------------------------------------------
24 -- PROCEDURE UPDATE_GAIN_LOSS_IND
25 -- The purpose of this procedure is to update the gain_loss_indicator on the
26 -- prepay history table based on the exchange rates of prepayment transactions.
27 --
28 --------------------------------------------------------------------------------
29 PROCEDURE Update_Gain_Loss_Ind
30 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
31 ,P_Calling_Sequence IN VARCHAR2
32 ) IS
33
34 l_curr_calling_sequence VARCHAR2(2000);
35
36 -- Logging Infra:
37 l_procedure_name CONSTANT VARCHAR2(30) := 'Update_Gain_Loss_Ind';
38 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
39 l_gain_loss_indicator ap_prepay_history_all.gain_loss_indicator%type;
40 l_gain_loss_indicator_parent ap_prepay_history_all.gain_loss_indicator%type; -- bug9175969
41 l_reversal_adj VARCHAR2(1);
42
43
44 BEGIN
45
46 l_curr_calling_sequence := 'AP_Acctg_Prepay_Dist_Pkg.Update_Gain_Loss_Ind<- ' ||
47 p_calling_sequence;
48
49
50 -- Logging Infra: Setting up runtime level
51 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
52
53 -- Logging Infra: Procedure level
54 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
55 l_log_msg := 'Begin of procedure '|| l_procedure_name;
56 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
57 END IF;
58
59
60 IF (P_XLA_Event_Rec.event_type_code ='PREPAYMENT APPLICATION ADJ') then -- bug9175969
61
62 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
63 l_log_msg := 'Before getting the Gain/Loss Indicator for the '||
64 'Related Prepayment Event ';
65 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_procedure_name, l_log_msg);
66 END IF;
67
68 BEGIN
69
70 l_gain_loss_indicator_parent := NULL;
71
72 SELECT aph1.Gain_Loss_Indicator
73 INTO l_gain_loss_indicator_parent
74 FROM AP_Prepay_History_All aph1,
75 AP_Prepay_History_All APH
76 WHERE aph1.invoice_id=aph.invoice_id
77 AND aph1.accounting_event_id = aph.related_prepay_app_event_id
78 AND aph.accounting_event_id = p_xla_event_rec.event_id
79 AND rownum=1;
80
81 EXCEPTION
82 WHEN OTHERS THEN
83 l_gain_loss_indicator_parent := NULL;
84 END;
85
86 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
87 l_log_msg := 'The Gain/Loss Indicator Fetched is '|| l_gain_loss_indicator_parent;
88 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_procedure_name, l_log_msg);
89 END IF;
90
91 IF l_gain_loss_indicator_parent IS NOT NULL THEN
92
93 BEGIN
94 l_reversal_adj := 'N';
95
96 SELECT 'Y'
97 INTO l_reversal_adj
98 FROM dual
99 WHERE EXISTS
100 (SELECT 1
101 FROM ap_prepay_history_all apph,
102 ap_prepay_app_dists apad,
103 ap_prepay_app_dists apad_rel,
104 ap_prepay_history_all apph_rel
105 WHERE apph.accounting_event_id = P_XLA_Event_Rec.Event_ID
106 AND apph.prepay_history_id = apad.prepay_history_id
107 AND apad.reversed_prepay_app_dist_id = apad_rel.prepay_app_dist_id
108 AND apad_rel.prepay_history_id = apph_rel.prepay_history_id
109 AND apph_rel.accounting_event_id = apph.related_prepay_app_event_id);
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 l_reversal_adj := 'N';
114 END;
115
116 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
117 l_log_msg := 'The value of l_reversal_adj is: '||l_reversal_adj;
118 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_procedure_name, l_log_msg);
119 END IF;
120
121 IF l_reversal_adj = 'N' THEN
122 l_gain_loss_indicator_parent := NULL;
123 END IF;
124
125 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
126 l_log_msg := 'Final value of l_gain_loss_indicator_parent '||l_gain_loss_indicator_parent;
127 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_procedure_name, l_log_msg);
128 END IF;
129
130 END IF;
131
132 END IF;
133
134 -- Added by abhsaxen for bug 9032498
135 --
136 UPDATE AP_Prepay_History_All APH
137 SET Gain_Loss_Indicator =
138 (SELECT DECODE(APH.Transaction_Type, 'PREPAYMENT APPLIED',
139 DECODE(SIGN(SUM(APAD.Base_Amount - APAD.Base_Amt_At_Prepay_XRate)),
140 -1, 'G', 1, 'L', NULL),
141 'PREPAYMENT UNAPPLIED',
142 DECODE(SIGN(SUM(APAD.Base_Amount - APAD.Base_Amt_At_Prepay_XRate)),
143 1, 'G', -1, 'L', NULL),
144 'PREPAYMENT APPLICATION ADJ',
145 DECODE(SIGN(SUM(APAD.Base_Amount - APAD.Base_Amt_At_Prepay_XRate)),
146 -1, 'G', 1, 'L',
147 0, l_gain_loss_indicator_parent)) -- bug9175969
148 FROM AP_Prepay_App_Dists APAD,
149 AP_System_Parameters_ALL ASP
150 WHERE ASP.Org_ID = APH.Org_ID
151 AND APAD.Prepay_History_ID = APH.Prepay_History_ID
152 AND APAD.Accounting_Event_ID = p_xla_event_rec.event_id
153 AND APAD.PREPAY_DIST_LOOKUP_CODE NOT IN ('FINAL PAYMENT ROUNDING',
154 'FINAL APPL ROUNDING') -- bug9716573
155 AND NOT (NVL(ASP.INVRATE_FOR_PREPAY_TAX, 'N') = 'Y' AND -- bug11651946
156 APAD.Prepay_Dist_Lookup_Code LIKE '%TAX%')
157 )
158 WHERE APH.Accounting_Event_ID = p_xla_event_rec.event_id;
159
160
161 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
162
163 --bug9464881
164 BEGIN
165
166 SELECT aph.gain_loss_indicator
167 INTO l_gain_loss_indicator
168 FROM ap_prepay_history_all aph
169 WHERE APH.Accounting_Event_ID = p_xla_event_rec.event_id;
170
171 l_log_msg := 'APH.Gain_Loss_Indicator: '|| nvl(l_gain_loss_indicator,'NULL');
172 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 l_log_msg := ' Encountered an Exception:'||SQLERRM||
177 ' while fetching the gain/loss indicator ';
178 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
179 END;
180
181 END IF;
182
183 -- Logging Infra: Procedure level
184 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
185 l_log_msg := 'End of procedure '|| l_procedure_name;
186 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
187 END IF;
188
189 EXCEPTION
190 WHEN OTHERS THEN
191 IF (SQLCODE <> -20001) THEN
192 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
193 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
194 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
195 END IF;
196 APP_EXCEPTION.RAISE_EXCEPTION;
197
198 END Update_Gain_Loss_Ind;
199
200
201 -------------------------------------------------------------------------------
202 -- PROCEDURE Prepay_Hist_Insert
203 -- The purpose of this procedure is to insert the prepayment history info
204 -- into the prepayment history table
205 --
206 --------------------------------------------------------------------------------
207 -- Bug 4996808. Inserting the prepay headers instead of in the accounting events
208 -- procedure
209 PROCEDURE Prepay_Hist_Insert
210 (P_Invoice_ID IN NUMBER
211 ,P_Calling_Sequence IN VARCHAR2
212 ) IS
213
214 l_curr_calling_sequence VARCHAR2(2000);
215 l_line_amount NUMBER;
216 l_transaction_type VARCHAR2(30);
217 l_prepay_invoice_id NUMBER;
218 l_prepay_line_number NUMBER;
219 l_accounting_event_id NUMBER; --bug9038462
220 l_org_id NUMBER;
221 l_invoice_line_number NUMBER;
222 l_accounting_date DATE;
223 l_related_prepay_app_event_id NUMBER;
224 l_group_number NUMBER;
225
226 -- Logging:
227 l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Hist_Insert';
228 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
229
230
231 -- bug9038462,
232 -- 1. changed both parts of the union to ensure
233 -- creation of a new Prepayment History record for an
234 -- Unencumbered and Unaccounted Prepayment Application or
235 -- Unapplication record
236 --
237 -- 2. fetched the accounting_event_id from the Invoice
238 -- distribution to be stamped on the Prepay History record
239 -- at the time of regeneration
240 --
241 CURSOR c_prepay_history IS
242 SELECT AIL.Line_Number,
243 AIL.Amount Amount,
244 AIL.Prepay_Invoice_ID,
245 AIL.Prepay_Line_Number,
246 AID.Accounting_Event_Id,
247 AIL.Org_ID,
248 AID.Accounting_Date,
249 -- 6718967
250 DECODE(NVL(AID.Parent_Reversal_ID,-99), -99, 1, 2) Group_Number
251 FROM AP_Invoice_Lines_ALL AIL,
252 AP_Invoice_Distributions_All AID
253 WHERE AIL.Invoice_ID = p_invoice_id
254 AND AIL.Line_Type_Lookup_Code = 'PREPAY'
255 AND AIL.Invoice_ID = AID.Invoice_ID
256 AND AIL.Line_Number = AID.Invoice_Line_Number
257 --AND AID.Accounting_Event_ID IS NULL
258 AND nvl(AID.posted_flag, 'N') <> 'Y'
259 AND nvl(AID.encumbered_flag, 'N') <> 'Y'
260 GROUP BY AIL.Invoice_ID, AIL.Line_Number, AIL.Amount, AIL.Prepay_Invoice_ID,
261 AIL.Prepay_Line_Number, AIL.Org_ID, AID.Accounting_Date,
262 AID.Accounting_Event_Id,
263 -- 6718967
264 DECODE(NVL(AID.Parent_Reversal_ID,-99), -99, 1, 2)
265 UNION
266 SELECT AID.Invoice_Line_Number,
267 SUM(AID.Amount) Amount,
268 AIL1.Invoice_ID,
269 AIL1.Line_Number,
270 AID.Accounting_Event_Id,
271 AIL1.Org_ID,
272 AID.Accounting_Date,
273 -- 6718967
274 DECODE(NVL(AID.Parent_Reversal_ID,-99), -99, 1, 2) Group_Number
275 FROM AP_Invoice_Lines AIL,
276 AP_Invoice_Distributions AID,
277 AP_Invoice_Lines AIL1,
278 AP_Invoice_Distributions AID1
279 WHERE AID.Invoice_ID = p_invoice_id
280 AND AID.Line_Type_Lookup_Code = 'PREPAY'
281 AND AID.Invoice_ID = AIL.Invoice_ID
282 AND AID.Invoice_Line_Number = AIL.Line_Number
283 AND AIL.Line_Type_Lookup_Code <> 'PREPAY'
284 --AND AID.Accounting_Event_ID IS NULL
285 AND NVL(AID.posted_flag, 'N') <> 'Y'
286 AND NVL(AID.encumbered_flag, 'N') <> 'Y'
287 AND AID.Prepay_Distribution_ID = AID1.Invoice_Distribution_ID
288 AND AIL1.Invoice_ID = AID1.Invoice_ID
289 AND AIL1.Line_Number = AID1.Invoice_Line_Number
290 GROUP BY AIL1.Invoice_ID, AIL1.Line_Number, AIL1.Org_ID,
291 AID.Invoice_Line_Number, AID.Accounting_Date,
292 AID.Accounting_Event_Id,
293 -- 6718967
294 DECODE(NVL(AID.Parent_Reversal_ID,-99), -99, 1, 2);
295
296
297 BEGIN
298
299 l_curr_calling_sequence := p_calling_sequence ||
300 ' -> AP_ACCTG_PREPAY_DISTS_PKG.PREPAY_HIST_INSERT';
301
302 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
303
304 l_log_msg :='Begin of procedure '||l_procedure_name;
305 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
306 FND_LOG.STRING(G_LEVEL_PROCEDURE,
307 G_MODULE_NAME || l_procedure_name,
308 l_log_msg);
309 END IF;
310
311 OPEN c_prepay_history;
312 LOOP
313 FETCH c_prepay_history INTO l_invoice_line_number,
314 l_line_amount, l_prepay_invoice_id, l_prepay_line_number,
315 l_accounting_event_id, l_org_id, l_accounting_date, l_group_number;
316 EXIT WHEN c_prepay_history%NOTFOUND OR
317 c_prepay_history%NOTFOUND IS NULL;
318
319 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
320 l_log_msg := 'CUR: C_Prepay_History: prepay_invoice_id = '||
321 l_prepay_invoice_id
322 || ' Prepay_Line_Number = ' || l_prepay_line_number
323 || ' Invoice_Line_Number = ' ||l_invoice_line_number;
324 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
325 END IF;
326
327
328 BEGIN
329
330 SELECT min(accounting_Event_id)
331 INTO l_related_prepay_app_event_id
332 FROM AP_INVOICE_DISTRIBUTIONS AID
333 WHERE AID.line_type_lookup_code = 'PREPAY'
334 AND nvl(posted_flag,'N') = 'Y'
335 AND nvl(AID.amount,0) < 0
336 AND AID.invoice_id = P_invoice_id
337 AND AID.invoice_line_number = l_invoice_line_number;
338
339
340 EXCEPTION
341 WHEN NO_DATA_FOUND THEN
342 l_related_prepay_app_event_id:= null;
343
344 END;
345
346 -- Bug 6718967. Added group number to identify if it is
347 -- prepayment applied or unapplied.
348 IF l_group_number = 1 THEN
349 l_transaction_type := 'PREPAYMENT APPLIED';
350 ELSE
351 l_transaction_type := 'PREPAYMENT UNAPPLIED';
352 END IF;
353
354
355 INSERT INTO AP_PREPAY_HISTORY_ALL
356 (PREPAY_HISTORY_ID
357 ,PREPAY_INVOICE_ID
358 ,PREPAY_LINE_NUM
359 ,ACCOUNTING_EVENT_ID
360 ,HISTORICAL_FLAG
361 ,INVOICE_ID
362 ,INVOICE_LINE_NUMBER
363 ,ACCOUNTING_DATE
364 ,INVOICE_ADJUSTMENT_EVENT_ID
365 ,ORG_ID
366 ,POSTED_FLAG
367 ,RELATED_PREPAY_APP_EVENT_ID
368 ,TRANSACTION_TYPE
369 ,LAST_UPDATED_BY
370 ,LAST_UPDATE_DATE
371 ,LAST_UPDATE_LOGIN
372 ,CREATED_BY
373 ,CREATION_DATE
374 ,PROGRAM_APPLICATION_ID
375 ,PROGRAM_ID
376 ,PROGRAM_UPDATE_DATE
377 ,REQUEST_ID)
378 VALUES (AP_PREPAY_HISTORY_S.nextval
379 ,l_prepay_invoice_id
380 ,l_prepay_line_number
381 ,l_accounting_event_id --bug9038462
382 ,'N'
383 ,p_invoice_id
384 ,l_invoice_line_number
385 ,l_accounting_date
386 ,NULL
387 ,l_org_id
388 ,'N'
389 ,l_related_prepay_app_event_id
390 ,l_transaction_type
391 ,FND_GLOBAL.user_id
392 ,sysdate
393 ,FND_GLOBAL.login_id
394 ,FND_GLOBAL.user_id
395 ,sysdate
396 ,null
397 ,null
398 ,null
399 ,null);
400
401 END LOOP;
402 CLOSE c_prepay_history;
403
404 l_log_msg :='End of procedure '||l_procedure_name;
405
406 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
407 FND_LOG.STRING(G_LEVEL_PROCEDURE,
408 G_MODULE_NAME || l_procedure_name,
409 l_log_msg);
410 END IF;
411
412 END Prepay_Hist_Insert;
413
414
415
416 -------------------------------------------------------------------------------
417 -- PROCEDURE Prepay_Dist_Appl
418 -- The purpose of this procedure is to prorate the prepayment application
419 -- amount for all the distributions of the invoice that the prepayment is applied
420 -- and generate the prepayment application distributions.
421 --
422 --------------------------------------------------------------------------------
423 PROCEDURE Prepay_Dist_Appl
424 (P_Invoice_ID IN NUMBER
425 ,P_Calling_Sequence IN VARCHAR2
426 ) IS
427 l_prepay_hist_id AP_PREPAY_HISTORY_ALL.Prepay_History_id%TYPE := -99;
428 l_accounting_event_id XLA_EVENTS.event_id%TYPE := -99;
429 l_historical_flag AP_PREPAY_HISTORY_ALL.Historical_flag%TYPE := 'N';
430 l_posted_flag AP_PREPAY_HISTORY_ALL.Posted_flag%TYPE := 'N';
431 l_upg_batch_id XLA_AE_HEADERS.Upg_Batch_Id%TYPE := -99;
432
433 l_curr_calling_sequence VARCHAR2(2000);
434 l_total_paid_amt NUMBER;
435 l_final_payment BOOLEAN := FALSE;
436
437 l_pay_hist_rec ap_accounting_pay_pkg.r_pay_hist_info;
438 l_clr_hist_rec ap_accounting_pay_pkg.r_pay_hist_info;
439 l_inv_rec ap_accounting_pay_pkg.r_invoices_info;
440 l_prepay_inv_rec ap_accounting_pay_pkg.r_invoices_info;
441 l_inv_dist_rec ap_accounting_pay_pkg.r_inv_dist_info;
442 l_prepay_hist_rec r_prepay_hist_info;
443 l_prepay_dist_rec r_prepay_dist_info;
444 l_payment_type_flag AP_CHECKS_ALL.payment_type_flag%TYPE;
445 l_pay_trx_type AP_PAYMENT_HISTORY_ALL.transaction_type%TYPE;
446
447 -- bug9492002
448 -- commenting below for bug10183934
449 --l_upg_pmt_hist NUMBER;
450 --l_upg_inv_pmts NUMBER;
451 --l_upg_prepay_app NUMBER;
452
453
454 -- Logging Infra:
455 l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Dist_Appl';
456 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
457
458 -- BUG # 7688509
459 -- condition: historical_flag =Y
460 -- and event is 'INVOICE ADJUSTMENT'
461 -- and ap_system_parameter.auto_offset_flag ='N'
462 -- and sum of the distributions in the invoice adjustment event is 0
463 b_generate_prepay_dist BOOLEAN;
464 l_sum_per_event NUMBER;
465 l_dist_count_per_event NUMBER;
466
467 CURSOR c_sum_per_event(p_acct_event_id NUMBER) IS
468 SELECT SUM(amount), count(1)
469 FROM ap_invoice_distributions_all aid,
470 xla_events evnt,
471 xla_ae_headers xah,
472 ap_system_parameters_all asp
473 WHERE aid.accounting_event_id = p_acct_event_id
474 AND aid.accounting_event_id = evnt.event_id
475 AND evnt.event_type_code in ('INVOICE ADJUSTED', 'CREDIT MEMO ADJUSTED',
476 'DEBIT MEMO ADJUSTED','PREPAYMENT ADJUSTED') -- 12731687
477 AND evnt.event_id = xah.event_id
478 AND xah.upg_batch_id IS NOT NULL
479 AND aid.org_id = asp.org_id
480 AND asp.automatic_offsets_flag = 'N'
481 AND aid.historical_flag = 'Y'
482 AND evnt.application_id=200;
483
484 BEGIN
485
486 l_curr_calling_sequence := 'AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Appl<- ' ||
487 p_calling_sequence;
488
489 -- Logging Infra: Setting up runtime level
490 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
491
492 -- Logging Infra: Procedure level
493 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
494 l_log_msg := 'Begin of procedure '|| l_procedure_name;
495 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
496 END IF;
497
498 -- Bug Fix 5634515
499 -- deleting previous unprocessed prepayment history records for invoice
500 delete_hist_dists(P_Invoice_ID,
501 l_curr_calling_sequence);
502
503 -- Logging Infra: Procedure level
504 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
505 l_log_msg := 'calling procedure Prepay_Hist_Insert ';
506 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
507 END IF;
508
509 /* Bug 4996808. Inserting into the prepayment history table */
510 Prepay_Hist_Insert (P_Invoice_ID,
511 l_curr_calling_sequence);
512
513
514 /* Get the prepayment history header info */
515 OPEN Prepay_History(P_Invoice_ID);
516 LOOP
517 FETCH Prepay_History INTO l_prepay_hist_rec;
518 EXIT WHEN Prepay_History%NOTFOUND OR
519 Prepay_History%NOTFOUND IS NULL;
520
521
522
523 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
524 l_log_msg := 'CUR: Prepay_History: prepay_history_id = '||
525 l_prepay_hist_rec.prepay_history_id
526 || ' Prepay_Invoice_ID = ' || l_prepay_hist_rec.Prepay_Invoice_ID
527 || ' Invoice_ID = ' ||l_prepay_hist_rec.Invoice_ID;
528 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
529 END IF;
530
531 -- 9322009, added the following code to recreate the incorrect dist
532 -- links for the upgraded prepayment application events, so the the
533 -- prepayment unapplication for the same created in R12 can get successfully
534 -- accounted
535 --
536
537 IF l_prepay_hist_rec.transaction_type = 'PREPAYMENT UNAPPLIED' AND
538 l_prepay_hist_rec.related_prepay_app_event_id IS NOT NULL THEN
539
540 BEGIN
541 SELECT APPH.Prepay_History_ID,
542 APPH.Accounting_Event_ID,
543 APPH.Posted_Flag,
544 NVL(APPH.Historical_Flag, 'N') Historical_Flag,
545 XAH.upg_batch_id
546 INTO l_prepay_hist_id,
547 l_accounting_event_id,
548 l_posted_flag,
549 l_historical_flag,
550 l_upg_batch_id
551 FROM ap_prepay_history_all APPH,
552 xla_ae_headers XAH,
553 ap_system_parameters_all ASP
554 WHERE APPH.Invoice_ID = P_Invoice_ID
555 AND APPH.accounting_event_id = l_prepay_hist_rec.related_prepay_app_event_id
556 AND XAH.application_id = 200
557 AND XAH.event_id = APPH.accounting_event_id
558 AND ASP.org_id = APPH.org_id
559 AND ASP.set_of_books_id = XAH.ledger_id;
560
561 EXCEPTION
562 WHEN OTHERS THEN
563 l_historical_flag := 'N';
564 END;
565
566
567 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
568 l_log_msg := 'Evaluating if the distribution links should be recreated :- '||
569 ' Prepay_History_Id :'||l_prepay_hist_id||
570 ' Accounting_Event_Id : '||l_accounting_event_id||
571 ' Posted_Flag : '||l_posted_flag||
572 ' historical_flag : '||l_historical_flag||
573 ' upg_batch_id : '||l_upg_batch_id;
574 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
575 END IF;
576
577
578
579 IF (l_historical_Flag = 'Y' AND
580 l_posted_flag = 'Y' AND
581 l_upg_batch_id IS NOT NULL AND
582 l_upg_batch_id <> -9999) THEN
583
584 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
585 l_log_msg := 'Proceeding to call the Upg_Dist_Links_Insert procedure';
586 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
587 END IF;
588
589 SAVEPOINT before_reupgrade;
590 BEGIN
591 Upg_Dist_Links_Insert
592 (P_Invoice_ID,
593 l_prepay_hist_id,
594 l_accounting_event_id,
595 l_curr_calling_sequence);
596 EXCEPTION
597 WHEN OTHERS THEN
598 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
599 l_log_msg := 'Upg_Dist_Links_Insert encountered exception '||SQLERRM;
600 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
601 END IF;
602
603 ROLLBACK TO before_reupgrade;
604 END;
605
606 END IF;
607 END IF;
608
609 /* Get the standard invoice header info */
610 OPEN Invoice_Header(P_Invoice_ID);
611 FETCH Invoice_Header INTO l_inv_rec;
612 CLOSE Invoice_Header;
613
614
615 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
616 l_log_msg := 'CUR: Invoice_Header: Invoice_ID = '|| l_prepay_hist_rec.invoice_id;
617 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
618 END IF;
619
620
621 /* Get the prepayment invoice header info */
622 OPEN Invoice_Header(l_prepay_hist_rec.prepay_invoice_id);
623 FETCH Invoice_Header INTO l_prepay_inv_rec;
624 CLOSE Invoice_Header;
625
626 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
627 l_log_msg := 'Check the Payment Type Flag on the Payment for Prepay Invoice_id '||
628 l_prepay_hist_rec.prepay_invoice_id;
629 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
630 END IF;
631
632 BEGIN
633
634 l_payment_type_flag := 'Q';
635
636 SELECT DISTINCT ac.payment_type_flag
637 INTO l_payment_type_flag
638 FROM ap_checks_all ac,
639 ap_invoice_payments_all aip
640 WHERE ac.check_id = aip.check_id
641 AND aip.invoice_id = l_prepay_hist_rec.prepay_invoice_id
642 AND rownum < 2;
643
644 EXCEPTION
645 WHEN OTHERS THEN
646 l_payment_type_flag := 'Q';
647 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
648 l_log_msg := ' Encountered an Exception '||SQLERRM||
649 ' when Fetching the Payment Type Flag';
650 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
651 END IF;
652
653 END;
654
655 IF l_payment_type_flag = 'R' THEN
656 l_pay_trx_type := 'REFUND RECORDED';
657 ELSE
658 l_pay_trx_type := 'PAYMENT CREATED';
659 END IF;
660
661 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
662 l_log_msg := 'Obtained l_pay_trx_type as '||l_pay_trx_type;
663 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
664 END IF;
665
666 /* Get the payment history info */
667 OPEN Payment_History
668 (l_prepay_hist_rec.prepay_invoice_id,
669 l_pay_trx_type);
670 FETCH Payment_History INTO l_pay_hist_rec;
671 CLOSE Payment_History;
672
673
674 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
675 l_log_msg := 'CUR: Payment_History for payment: Payment_History_ID = '||
676 l_pay_hist_rec.payment_history_id;
677 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
678 END IF;
679
680
681 /* Get the clearing payment history info */
682 OPEN Payment_History
683 (l_prepay_hist_rec.prepay_invoice_id,
684 'PAYMENT CLEARING');
685 FETCH Payment_History INTO l_clr_hist_rec;
686 CLOSE Payment_History;
687
688
689 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
690 l_log_msg := 'CUR: Payment_History for clearing: Payment_History_ID = '||
691 l_clr_hist_rec.payment_history_id;
692 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
693 END IF;
694
695
696 /* Get the prepay distributions for this event */
697 OPEN Prepay_Dists(P_Invoice_ID,
698 l_prepay_hist_rec.invoice_line_number,
699 l_prepay_hist_rec.accounting_date,
700 l_prepay_hist_rec.prepay_history_id);
701 LOOP
702
703 FETCH Prepay_Dists INTO l_prepay_dist_rec;
704 EXIT WHEN Prepay_Dists%NOTFOUND OR
705 Prepay_Dists%NOTFOUND IS NULL;
706
707
708 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
709 l_log_msg := 'CUR: Prepay_Dists: Invoice_ID = '||l_prepay_dist_rec.invoice_id
710 ||' Invoice_Distribution_ID = '||l_prepay_dist_rec.invoice_distribution_id
711 ||' Prepay_Distribution_ID = '||l_prepay_dist_rec.prepay_distribution_id;
712 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
713 END IF;
714
715
716 IF l_prepay_dist_rec.parent_reversal_id IS NOT NULL THEN
717
718 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
719 l_log_msg := 'CUR: Prepay_Dists: Invoice_Distribution_ID = '
720 ||l_prepay_dist_rec.invoice_distribution_id
721 ||' Parent_Reversal_ID = '||l_prepay_dist_rec.parent_reversal_id;
722 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
723 END IF;
724
725 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
726 l_log_msg := 'Calling procedure Prepay_Dist_Reverse for prepay dist: '
727 || l_prepay_dist_rec.invoice_distribution_id;
728 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
729 END IF;
730
731
732 /* Creating prepayment appl dists for unapplication by reversing the prepay appl
733 distributions */
734 Prepay_Dist_Reverse
735 (l_prepay_hist_rec,
736 l_prepay_dist_rec.parent_reversal_id,
737 NULL, -- p_xla_event_rec
738 NULL, -- p_inv_reversal_id
739 -- Bug 7134020
740 NULL, -- p_inv_dist_id
741 l_prepay_dist_rec.invoice_distribution_id,
742 l_curr_calling_sequence);
743
744
745 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
746 l_log_msg := 'Procedure Prepay_Dist_Reverse executed';
747 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
748 END IF;
749
750 ELSE
751
752 -- Bug 9492002, added the sql below to check if there are any upgraded
753 -- payments available for the invoice in consideration
754 --
755 -- commenting below for bug10183934
756 --SELECT count(*)
757 --INTO l_upg_inv_pmts
758 --FROM AP_Invoice_Payments_All AIP
759 --WHERE Invoice_ID = p_invoice_id
760 --AND EXISTS (SELECT 'Upg Payment'
761 -- FROM AP_Payment_History_All APH,
762 -- AP_System_Parameters_All ASP,
763 -- XLA_AE_Headers XAH
764 -- WHERE APH.Check_ID = AIP.Check_ID
765 -- AND APH.Historical_Flag = 'Y'
766 -- AND APH.Posted_Flag = 'Y'
767 -- AND APH.Org_id = ASP.Org_id
768 -- AND APH.Accounting_Event_ID = XAH.Event_ID
769 -- AND XAH.Ledger_id = ASP.Set_of_Books_ID
770 -- AND XAH.Application_ID = 200
771 -- AND XAH.Accounting_Entry_Status_Code = 'F'
772 -- AND XAH.Upg_Batch_ID <> -9999
773 -- AND XAH.Upg_Batch_ID IS NOT NULL
774 -- AND Rownum = 1);
775
776 -- Bug9492002, checking if there are any upgaded prepay applications
777 -- or Unapplication for the Invoice, which are Historical and have
778 -- been accounted in 11i
779 --
780 -- commenting below for bug10183934
781 --SELECT count(*)
782 -- INTO l_upg_prepay_app
783 -- FROM AP_Invoice_Distributions_ALL AID,
784 -- XLA_AE_Headers XAH
785 -- WHERE AID.accounting_event_id = XAH.Event_ID
786 -- AND XAH.Application_ID = 200
787 -- AND XAH.Event_type_Code IN ('PREPAYMENT APPLIED', 'PREPAYMENT UNAPPLIED')
788 -- AND XAH.Upg_batch_ID IS NOT NULL
789 -- AND XAH.Upg_batch_ID <> -9999
790 -- AND XAH.Ledger_ID = AID.Set_of_Books_ID
791 -- AND XAH.Accounting_Entry_Status_Code = 'F'
792 -- AND NVL(AID.Historical_Flag, 'N') = 'Y'
793 -- AND AID.Invoice_id = p_Invoice_id
794 -- AND rownum = 1;
795
796 -- Bug9492002, addded the below if clause so as to assign
797 -- a false status to the final payment check if there are
798 -- any upgraded payments or prepayment applications for the
799 -- standard invoice in consideration
800 --
801 -- commenting below condition for bug10183934
802 --IF l_upg_inv_pmts = 0 AND l_upg_prepay_app = 0 THEN
803
804 /* Check if the invoice is fully paid */
805 --bug 9108925, added the call to Is_Final_Event
806 IF AP_Accounting_Pay_Pkg.Is_Final_Payment
807 (l_inv_rec,
808 NULL, -- Payment Amount
809 NULL, -- Discount Amount
810 l_prepay_dist_rec.amount,
811 'PAYMENT CREATED',
812 l_curr_calling_sequence) THEN
813 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
814 l_log_msg := 'Final payment of Invoice_ID '||l_prepay_dist_rec.invoice_id;
815 END IF;
816 l_final_payment := AP_ACCOUNTING_PAY_PKG.Is_Final_Event
817 (l_inv_rec,
818 NULL, --p_xla_event_rec
819 l_prepay_dist_rec.invoice_distribution_id,
820 l_curr_calling_sequence);
821 ELSE
822 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
823 l_log_msg := 'Not Final payment of Invoice_ID '||l_prepay_dist_rec.invoice_id;
824 END IF;
825 l_final_payment := FALSE;
826 END IF;
827 -- ELSE
828 -- l_final_payment := FALSE;
829 -- END IF;
830
831 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
832 IF l_final_payment THEN
833 l_log_msg := 'Final pay/prepay event for Invoice_ID '||l_prepay_dist_rec.invoice_id;
834 ELSE
835 l_log_msg := 'Not final pay/prepay event for Invoice_ID '||l_prepay_dist_rec.invoice_id;
836 END IF;
837 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
838 END IF;
839
840 --8244163 This query exists 3 places in this package pls make sure that
841 --you are modifying in all the places
842 SELECT SUM(NVL(AID.Amount,0)),
843 SUM(DECODE(aid.line_type_lookup_code, 'AWT', 0, NVL(AID.Amount,0) ) ),
844 SUM(DECODE(aid.line_type_lookup_code, 'AWT', NVL(AID.Amount,0),0 ) )
845 INTO G_Total_Dist_amount,
846 G_Total_Inv_amount,
847 G_Total_awt_amount --Bug9106549
848 FROM AP_Invoice_Distributions_All AID
849 WHERE AID.Invoice_ID = p_invoice_id
850 AND AID.Line_Type_Lookup_Code <> 'PREPAY'
851 AND AID.Prepay_Distribution_ID IS NULL
852 AND AID.Prepay_Tax_Parent_ID IS NULL -- For tax dists created in R11.5
853 AND AID.AWT_Invoice_Payment_ID IS NULL
854 AND NVL(AID.Cancellation_Flag,'N') <> 'Y' -- BUG 6513956
855 AND NOT EXISTS (SELECT 1 --bug fix 6909150
856 FROM xla_events
857 WHERE event_id = AID.accounting_event_id
858 AND application_id = 200
859 AND event_type_code IN ('INVOICE CANCELLED',
860 'CREDIT MEMO CANCELLED',
861 'DEBIT MEMO CANCELLED'));
862
863 OPEN Invoice_Dists(p_invoice_id);
864 LOOP
865
866 FETCH Invoice_Dists INTO l_inv_dist_rec;
867 EXIT WHEN Invoice_Dists%NOTFOUND OR
868 Invoice_Dists%NOTFOUND IS NULL;
869
870 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
871 l_log_msg := 'CUR: Invoice_Dists: Invoice_Distribution_ID = '
872 ||l_inv_dist_rec.invoice_distribution_id;
873 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
874 END IF;
875
876
877 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
878 l_log_msg := 'Calling procedure Prepay_Dist_Proc for dist: '
879 || l_inv_dist_rec.invoice_distribution_id;
880 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
881 END IF;
882
883 -- BUG # 7688509
884 -- condition: historical_flag =Y
885 -- and event is 'INVOICE ADJUSTED'
886 -- and ap_system_parameter.auto_offset_flag ='N'
887 -- and sum of the distributions in the invoice adjustment event is 0
888
889 b_generate_prepay_dist := TRUE;
890 IF l_inv_dist_rec.historical_flag ='Y' THEN
891 OPEN c_sum_per_event(l_inv_dist_rec.accounting_event_id);
892 FETCH c_sum_per_event into l_sum_per_event, l_dist_count_per_event;
893
894 -- > 0 case is to handled the case that only 1 line in adjustment event and itself amount is 0
895 If l_dist_count_per_event > 0 AND l_sum_per_event = 0 THEN
896 b_generate_prepay_dist := FALSE;
897 END IF;
898
899 CLOSE c_sum_per_event;
900
901 END IF;
902
903 -- Prorate only those awt distributions that were created during the invoice time
904 -- modified the if condition for bug # 7688509
905 IF l_inv_dist_rec.awt_invoice_payment_id IS NULL and b_generate_prepay_dist THEN
906 Prepay_Dist_Proc
907 (l_pay_hist_rec,
908 l_clr_hist_rec,
909 l_inv_rec,
910 l_prepay_inv_rec,
911 l_prepay_hist_rec,
912 l_prepay_dist_rec,
913 l_inv_dist_rec,
914 NULL, -- p_xla_event_rec
915 'A',
916 l_final_payment,
917 l_curr_calling_sequence);
918 END IF;
919
920
921 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
922 l_log_msg := 'Procedure Prepay_Dist_Proc executed';
923 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
924 END IF;
925
926 END LOOP;
927 CLOSE Invoice_Dists;
928
929 G_Total_Dist_Amt := 0;
930 G_Total_Prorated_Amt := 0;
931 G_Total_Tax_Diff_Amt := 0;
932
933
934 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
935 l_log_msg := 'Calling procedure P_Acctg_Pay_Round_Pkg.Do_Rounding for Invoice_ID: '
936 || l_inv_rec.invoice_id;
937 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
938 END IF;
939
940 -- bug 7611160
941 SELECT asp.base_currency_code
942 INTO ap_accounting_pay_pkg.g_base_currency_code
943 FROM ap_system_parameters_all asp,
944 ap_invoices_all ai
945 WHERE asp.org_id = ai.org_id
946 AND ai.invoice_id = l_inv_rec.invoice_id;
947
948 -- Bug 9492002. Do not do rounding calculations if the invoice being paid
949 -- has an Upgraded Payment or Upgraded prepayment Applications
950 --
951 -- commenting below condition for bug10183934
952 --IF l_upg_inv_pmts = 0 AND l_upg_prepay_app = 0 THEN
953 AP_Acctg_Pay_Round_Pkg.Do_Rounding
954 (NULL, -- p_xla_event_rec
955 l_pay_hist_rec,
956 l_clr_hist_rec,
957 l_inv_rec,
958 NULL, -- l_inv_pay_rec
959 l_prepay_inv_rec,
960 l_prepay_hist_rec,
961 l_prepay_dist_rec,
962 l_curr_calling_sequence);
963
964 --END IF;
965 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
966 l_log_msg := 'Procedure P_Acctg_Pay_Round_Pkg.Do_Rounding executed';
967 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
968 END IF;
969
970 END IF;
971 END LOOP;
972 CLOSE Prepay_Dists;
973
974 END LOOP;
975 CLOSE Prepay_History;
976
977 -- Logging Infra: Procedure level
978 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
979 l_log_msg := 'End of procedure '|| l_procedure_name;
980 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
981 END IF;
982
983
984 EXCEPTION
985 WHEN OTHERS THEN
986 IF (SQLCODE <> -20001) THEN
987 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
988 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
989 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
990 END IF;
991 APP_EXCEPTION.RAISE_EXCEPTION;
992
993 END Prepay_Dist_Appl;
994
995
996 -------------------------------------------------------------------------------
997 -- PROCEDURE Prepay_Dist_Cascade_Adj
998 -- The purpose of this procedure is to prorate the prepayment amount for all the
999 -- distributions of the invoice that has been adjusted and generate the
1000 -- prepayment application payment distributions.
1001 --
1002 --------------------------------------------------------------------------------
1003 PROCEDURE Prepay_Dist_Cascade_Adj
1004 (P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
1005 ,P_Calling_Sequence IN VARCHAR2
1006 ) IS
1007
1008 l_curr_calling_sequence VARCHAR2(2000);
1009 l_inv_adj_amount NUMBER := 0;
1010 l_sum_prepaid_amount NUMBER := 0;
1011 l_sum_tax_diff_amount NUMBER := 0;
1012
1013 l_pay_hist_rec ap_accounting_pay_pkg.r_pay_hist_info;
1014 l_clr_hist_rec ap_accounting_pay_pkg.r_pay_hist_info;
1015 l_prepay_inv_rec ap_accounting_pay_pkg.r_invoices_info;
1016 l_inv_rec ap_accounting_pay_pkg.r_invoices_info;
1017 l_prepay_hist_rec r_prepay_hist_info;
1018 l_prepay_dist_rec r_prepay_dist_info;
1019 l_inv_dist_rec ap_accounting_pay_pkg.r_inv_dist_info;
1020 l_rounding_adjust_id NUMBER; --bug8201141
1021 --7488981
1022 l_prepay_dist_cnt NUMBER;
1023
1024 -- Logging Infra:
1025 l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Dist_Cascade_Adj';
1026 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1027
1028 -- Bug 6698125. Added adj cursor to get the prepay history record
1029 -- related to prepayment adjustment type events.
1030 CURSOR Prepay_History_Adj
1031 (P_Invoice_ID NUMBER,
1032 P_Event_ID NUMBER
1033 ) IS
1034 SELECT APH.Prepay_History_ID,
1035 APH.Prepay_Invoice_ID,
1036 APH.Invoice_ID,
1037 APH.Invoice_Line_Number,
1038 APH.Transaction_Type,
1039 APH.Accounting_Date,
1040 APH.Invoice_Adjustment_Event_ID,
1041 APH.Related_Prepay_App_Event_ID
1042 FROM AP_Prepay_History_All APH
1043 WHERE APH.Invoice_ID = P_Invoice_ID
1044 AND APH.Accounting_Event_ID = P_Event_ID;
1045
1046 CURSOR Inv_Adj_Dists
1047 (P_Event_ID NUMBER
1048 ,P_Invoice_ID NUMBER) IS
1049 SELECT Distinct AID.Invoice_Distribution_ID,
1050 AID.Line_Type_Lookup_Code,
1051 AID.Amount,
1052 AID.Base_Amount,
1053 AID.PO_Distribution_ID,
1054 AID.RCV_Transaction_ID,
1055 NVL(AID.Reversal_Flag,'N'),
1056 AID.Parent_Reversal_ID,
1057 AID.AWT_Related_ID,
1058 AID.AWT_Invoice_Payment_ID,
1059 AID.Quantity_Variance,
1060 AID.Base_Quantity_Variance,
1061 AID.Amount_Variance,
1062 AID.Base_Amount_Variance,
1063 AID.Historical_Flag, -- bug fix 6674279
1064 AID.Accounting_Event_Id -- bug fix 6674279
1065 FROM AP_Invoice_Distributions_All AID,
1066 AP_Prepay_App_Dists APAD,
1067 Financials_System_Params_All FSP
1068 WHERE AID.Invoice_ID = P_Invoice_ID
1069 AND NVL(AID.Reversal_Flag,'N') <> 'Y'
1070 AND NVL(AID.Accounting_Event_ID,-99) <> P_Event_ID
1071 AND APAD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
1072 AND FSP.Org_ID = AID.Org_ID
1073 AND ((NVL(FSP.Purch_Encumbrance_Flag,'N') = 'N'
1074 AND AID.Match_Status_Flag IN ('T','A'))
1075 OR
1076 ((NVL(FSP.Purch_Encumbrance_Flag,'N') = 'Y'
1077 AND AID.Match_Status_Flag = 'A')));
1078
1079 CURSOR Prepay_Adj_Dists
1080 (P_Event_ID NUMBER,
1081 P_Prepay_History_ID NUMBER
1082 ) IS
1083 (SELECT AID.Invoice_ID,
1084 AID.Invoice_Distribution_ID,
1085 AID.Line_Type_Lookup_Code,
1086 AID.Amount,
1087 AID.Base_Amount,
1088 AID.Accounting_Event_ID,
1089 AID.Prepay_Distribution_ID,
1090 AID.Prepay_Tax_Diff_Amount,
1091 AID.Parent_Reversal_ID
1092 FROM AP_Invoice_Distributions_All AID
1093 WHERE Accounting_Event_ID = P_Event_ID
1094 AND EXISTS (SELECT 'Prepay History'
1095 FROM AP_Prepay_History_All APH,
1096 AP_Invoice_Distributions_All AID1
1097 WHERE APH.Prepay_History_ID = P_Prepay_History_ID
1098 AND AID1.Invoice_Distribution_ID = AID.Prepay_Distribution_ID
1099 AND AID1.Invoice_ID = APH.Prepay_Invoice_ID
1100 AND AID1.Invoice_Line_Number = APH.Prepay_Line_Num)
1101 UNION ALL
1102 SELECT AID.Invoice_ID,
1103 AID.Invoice_Distribution_ID,
1104 AID.Line_Type_Lookup_Code,
1105 AID.Amount,
1106 AID.Base_Amount,
1107 AID.Accounting_Event_ID,
1108 AID.Prepay_Distribution_ID,
1109 AID.Prepay_Tax_Diff_Amount,
1110 AID.Parent_Reversal_ID
1111 FROM AP_Invoice_Distributions_All AID
1112 WHERE Line_Type_Lookup_Code IN ( 'NONREC_TAX','REC_TAX')
1113 AND Accounting_Event_ID = P_Event_ID
1114 AND Charge_Applicable_To_Dist_ID IN
1115 (SELECT AID1.Invoice_Distribution_ID
1116 FROM AP_Invoice_Distributions_All AID1
1117 WHERE Line_Type_Lookup_Code = 'PREPAY'
1118 AND Accounting_Event_ID = P_Event_ID
1119 AND EXISTS (SELECT 'Prepay History'
1120 FROM AP_Prepay_History_All APH,
1121 AP_Invoice_Distributions_All AID2
1122 WHERE APH.Prepay_History_ID = P_Prepay_History_ID
1123 AND AID2.Invoice_Distribution_ID = AID1.Prepay_Distribution_ID
1124 AND AID2.Invoice_ID = APH.Prepay_Invoice_ID
1125 AND AID2.Invoice_Line_Number = APH.Prepay_Line_Num)));
1126
1127
1128
1129 BEGIN
1130
1131 l_curr_calling_sequence := 'AP_Acctg_Pay_Dist_Pkg.Prepay_Dist_Cascade_Adj<- ' ||
1132 p_calling_sequence;
1133
1134 -- Logging Infra: Procedure level
1135 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1136 l_log_msg := 'Begin of procedure '|| l_procedure_name;
1137 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1138 END IF;
1139
1140
1141 /* Get the prepayment history header info */
1142 OPEN Prepay_History_Adj(P_XLA_Event_Rec.Source_id_int_1,
1143 P_XLA_Event_Rec.Event_ID);
1144 FETCH Prepay_History_Adj INTO l_prepay_hist_rec;
1145 CLOSE Prepay_History_Adj;
1146
1147 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1148 l_log_msg := 'CUR: Prepay_History: prepay_history_id = '||
1149 l_prepay_hist_rec.prepay_history_id
1150 || ' Prepay_Invoice_ID = ' || l_prepay_hist_rec.Prepay_Invoice_ID
1151 || ' Invoice_ID = ' ||l_prepay_hist_rec.Invoice_ID
1152 || ' Related_Event_ID = ' ||l_prepay_hist_rec.related_prepay_app_event_id
1153 || ' Inv_Adj_Event_ID = ' ||l_prepay_hist_rec.invoice_adjustment_event_id;
1154 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1155 END IF;
1156
1157
1158 /* Get the standard invoice header info */
1159 OPEN Invoice_Header(P_XLA_Event_Rec.source_id_int_1);
1160 FETCH Invoice_Header INTO l_inv_rec;
1161 CLOSE Invoice_Header;
1162
1163
1164 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1165 l_log_msg := 'CUR: Invoice_Header: Invoice_ID = '|| l_prepay_hist_rec.invoice_id;
1166 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1167 END IF;
1168
1169
1170 /* Get the prepayment invoice header info */
1171 OPEN Invoice_Header(l_prepay_hist_rec.prepay_invoice_id);
1172 FETCH Invoice_Header INTO l_prepay_inv_rec;
1173 CLOSE Invoice_Header;
1174
1175 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1176 l_log_msg := 'CUR: Prepay Invoice_Header: Invoice_ID = '|| l_prepay_inv_rec.invoice_id;
1177 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1178 END IF;
1179
1180
1181 /* Get the payment history info */
1182 OPEN Payment_History
1183 (l_prepay_hist_rec.prepay_invoice_id,
1184 'PAYMENT CREATED');
1185 FETCH Payment_History INTO l_pay_hist_rec;
1186 CLOSE Payment_History;
1187
1188
1189 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1190 l_log_msg := 'CUR: Payment_History for payment: Payment_History_ID = '||
1191 l_pay_hist_rec.payment_history_id;
1192 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1193 END IF;
1194
1195
1196 /* Get the clearing payment history info */
1197 OPEN Payment_History
1198 (l_prepay_hist_rec.prepay_invoice_id,
1199 'PAYMENT CLEARING');
1200 FETCH Payment_History INTO l_clr_hist_rec;
1201 CLOSE Payment_History;
1202
1203
1204 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1205 l_log_msg := 'CUR: Payment_History for clearing: Payment_History_ID = '||
1206 l_clr_hist_rec.payment_history_id;
1207 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1208 END IF;
1209
1210
1211 /* Get the prepay dists based on the related event id */
1212 OPEN Prepay_Adj_Dists(l_prepay_hist_rec.related_prepay_app_event_id,
1213 l_prepay_hist_rec.prepay_history_id);
1214 LOOP
1215
1216 FETCH Prepay_Adj_Dists INTO l_prepay_dist_rec;
1217 EXIT WHEN Prepay_Adj_Dists%NOTFOUND OR
1218 Prepay_Adj_Dists%NOTFOUND IS NULL;
1219
1220
1221 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1222 l_log_msg := 'CUR: Prepay_Dists: Invoice_ID = '||l_prepay_dist_rec.invoice_id
1223 ||' Invoice_Distribution_ID = '||l_prepay_dist_rec.invoice_distribution_id
1224 ||' Prepay_Distribution_ID = '||l_prepay_dist_rec.prepay_distribution_id;
1225 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1226 END IF;
1227 --8244163 This query exists 3 places in this package pls make sure that
1228 --you are modifying in all the places
1229 SELECT SUM(NVL(AID.Amount,0)),
1230 SUM(DECODE(aid.line_type_lookup_code, 'AWT', 0, NVL(AID.Amount,0) ) )
1231 INTO G_Total_Dist_amount,
1232 G_Total_Inv_amount
1233 FROM AP_Invoice_Distributions_All AID
1234 WHERE AID.Invoice_ID = l_prepay_hist_rec.invoice_id
1235 AND AID.Line_Type_Lookup_Code <> 'PREPAY'
1236 AND AID.Prepay_Distribution_ID IS NULL
1237 AND AID.Prepay_Tax_Parent_ID IS NULL -- For tax dists created in R11.5
1238 AND AID.AWT_Invoice_Payment_ID IS NULL
1239 AND NVL(AID.Cancellation_Flag,'N') <> 'Y' -- BUG 6513956
1240 AND NOT EXISTS (SELECT 1 --bug fix 6909150
1241 FROM xla_events
1242 WHERE event_id = AID.accounting_event_id
1243 AND application_id = 200
1244 AND event_type_code IN ('INVOICE CANCELLED',
1245 'CREDIT MEMO CANCELLED',
1246 'DEBIT MEMO CANCELLED'));
1247
1248 OPEN Invoice_Dists(l_prepay_hist_rec.invoice_id,
1249 l_prepay_hist_rec.invoice_adjustment_event_id);
1250 LOOP
1251
1252 FETCH Invoice_Dists INTO l_inv_dist_rec;
1253 EXIT WHEN Invoice_Dists%NOTFOUND OR
1254 Invoice_Dists%NOTFOUND IS NULL;
1255
1256
1257 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1258 l_log_msg := 'CUR: Invoice_Dists: Invoice_Distribution_ID = '
1259 ||l_inv_dist_rec.invoice_distribution_id
1260 || ' Reversal_Flag = ' ||l_inv_dist_rec.reversal_flag;
1261 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1262 END IF;
1263
1264 -- in bug 7488981 call to prepay_dist_reverse was made with null parent_reversal_id
1265 -- therefore the following check is added to check that
1266 --
1267 l_prepay_dist_cnt := 0; --7686421
1268 IF l_inv_dist_rec.parent_reversal_id IS NOT NULL THEN
1269
1270 SELECT count(*)
1271 INTO l_prepay_dist_cnt
1272 FROM ap_prepay_app_dists
1273 WHERE invoice_distribution_id = l_inv_dist_rec.parent_reversal_id;
1274
1275 END IF;
1276
1277 IF l_inv_dist_rec.reversal_flag = 'Y' AND
1278 l_prepay_dist_cnt > 0 THEN
1279
1280
1281 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1282 l_log_msg := 'Calling procedure Prepay_Dist_Reverse for dist: '
1283 || l_inv_dist_rec.invoice_distribution_id;
1284 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1285 END IF;
1286
1287 Prepay_Dist_Reverse
1288 (l_prepay_hist_rec,
1289 NULL, -- p_prepay_reversal_id
1290 p_xla_event_rec, -- Bug 6698125
1291 l_inv_dist_rec.parent_reversal_id,
1292 l_inv_dist_rec.invoice_distribution_id, -- Bug 7134020
1293 l_prepay_dist_rec.invoice_distribution_id,
1294 l_curr_calling_sequence);
1295
1296 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1297 l_log_msg := 'Procedure Prepay_Dist_Reverse executed';
1298 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1299 END IF;
1300
1301 ELSE
1302
1303 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1304 l_log_msg := 'Calling procedure Prepay_Dist_Proc for dist: '
1305 || l_inv_dist_rec.invoice_distribution_id;
1306 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1307 END IF;
1308
1309 -- Prorate only those awt distributions that were created during the invoice time
1310 IF (l_inv_dist_rec.awt_invoice_payment_id IS NULL) THEN
1311 Prepay_Dist_Proc
1312 (l_pay_hist_rec,
1313 l_clr_hist_rec,
1314 l_inv_rec,
1315 l_prepay_inv_rec,
1316 l_prepay_hist_rec,
1317 l_prepay_dist_rec,
1318 l_inv_dist_rec,
1319 p_xla_event_rec, -- Bug 6698125
1320 'C',
1321 NULL,
1322 l_curr_calling_sequence);
1323 END IF;
1324
1325 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1326 l_log_msg := 'Procedure Prepay_Dist_Proc executed';
1327 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1328 END IF;
1329
1330 END IF;
1331
1332 END LOOP;
1333 CLOSE Invoice_Dists;
1334
1335
1336 SELECT SUM(AID.Amount)
1337 INTO l_inv_adj_amount
1338 FROM AP_Invoice_Distributions_All AID
1339 WHERE AID.Accounting_Event_ID = l_prepay_hist_rec.invoice_adjustment_event_id;
1340
1341 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1342 l_log_msg := 'l_inv_adj_amount = ' ||l_inv_adj_amount;
1343 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1344 END IF;
1345
1346 /* Check if there is any change to the invoice liability. If there is
1347 a change then we need to adjust the payment hist distributions for the
1348 old invoice distributions */
1349
1350
1351 IF l_inv_adj_amount <> 0 THEN
1352
1353 --8244163 This query exists 3 places in this package pls make sure that
1354 --you are modifying in all the places
1355 SELECT SUM(NVL(AID.Amount,0)),
1356 SUM(DECODE(aid.line_type_lookup_code, 'AWT', 0, NVL(AID.Amount,0) ) )
1357 INTO G_Total_Dist_amount,
1358 G_Total_Inv_amount
1359 FROM AP_Invoice_Distributions_All AID
1360 WHERE AID.Invoice_ID = l_inv_rec.invoice_id
1361 AND AID.Line_Type_Lookup_Code <> 'PREPAY'
1362 AND AID.Prepay_Distribution_ID IS NULL
1363 AND AID.Prepay_Tax_Parent_ID IS NULL -- For tax dists created in R11.5
1364 AND AID.AWT_Invoice_Payment_ID IS NULL
1365 AND NVL(AID.Cancellation_Flag,'N') <> 'Y' -- BUG 6513956
1366 AND NOT EXISTS (SELECT 1 --bug fix 6909150
1367 FROM xla_events
1368 WHERE event_id = AID.accounting_event_id
1369 AND application_id = 200
1370 AND event_type_code IN ('INVOICE CANCELLED',
1371 'CREDIT MEMO CANCELLED',
1372 'DEBIT MEMO CANCELLED'));
1373
1374 OPEN Inv_Adj_Dists(l_prepay_hist_rec.invoice_adjustment_event_id,
1375 l_inv_rec.invoice_id);
1376 LOOP
1377
1378 FETCH Inv_Adj_Dists INTO l_inv_dist_rec;
1379 EXIT WHEN Inv_Adj_Dists%NOTFOUND OR
1380 Inv_Adj_Dists%NOTFOUND IS NULL;
1381
1382
1383 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1384 l_log_msg := 'CUR: Inv_Adj_Dists: Invoice_Distribution_ID = '
1385 ||l_inv_dist_rec.invoice_distribution_id;
1386 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1387 END IF;
1388
1389 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1390 l_log_msg := 'Calling procedure Prepay_Dist_Proc for dist: '
1391 ||l_inv_dist_rec.invoice_distribution_id;
1392 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1393 END IF;
1394
1395 Prepay_Dist_Proc(l_pay_hist_rec,
1396 l_clr_hist_rec,
1397 l_inv_rec,
1398 l_prepay_inv_rec,
1399 l_prepay_hist_rec,
1400 l_prepay_dist_rec,
1401 l_inv_dist_rec,
1402 p_xla_event_rec, -- Bug 6698125
1403 'C',
1404 NULL,
1405 l_curr_calling_sequence);
1406
1407 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1408 l_log_msg := 'Procedure Prepay_Dist_Proc executed';
1409 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1410 END IF;
1411
1412
1413 END LOOP;
1414 CLOSE Inv_Adj_Dists;
1415 END IF;
1416
1417 SELECT MAX(accounting_event_id) into l_rounding_adjust_id --8201141
1418 FROM ap_prepay_history_all apph
1419 WHERE transaction_type = 'PREPAYMENT APPLICATION ADJ'
1420 AND posted_flag <> 'Y'
1421 AND prepay_invoice_id = l_prepay_hist_rec.prepay_invoice_id
1422 AND invoice_id = l_prepay_hist_rec.invoice_id
1423 /* bug12858105 - start */
1424 AND EXISTS (SELECT 1
1425 FROM AP_Prepay_App_Dists APAD2
1426 WHERE 1=1
1427 AND APAD2.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
1428 AND APAD2.Prepay_Dist_Lookup_Code IN ('PREPAY APPL',
1429 'PREPAY APPL REC TAX',
1430 'PREPAY APPL NONREC TAX')
1431 AND APAD2.Reversed_Prepay_App_Dist_Id IS NULL
1432 AND NOT EXISTS (SELECT 1
1433 FROM ap_prepay_app_dists apad2_rev,
1434 ap_prepay_history_all apph_rev
1435 WHERE apad2_rev.reversed_prepay_app_dist_id = APAD2.prepay_app_dist_id
1436 AND apad2_rev.prepay_history_id = apph_rev.prepay_history_id
1437 AND apph_rev.invoice_id = apph.invoice_id)
1438 )
1439 /* bug12858105 - end */
1440 ;
1441
1442 IF ( l_rounding_adjust_id = p_xla_event_rec.event_id ) THEN
1443
1444 -- joined with ap_invoice_distributions_all for the performance issue 7235352
1445 SELECT /*+ leading(aid) */ SUM(DECODE(APAD.Prepay_Dist_Lookup_Code, 'PREPAY APPL', APAD.Amount,
1446 'PREPAY APPL REC TAX', APAD.Amount,
1447 'PREPAY APPL NONREC TAX', APAD.Amount, 0)),
1448 SUM(DECODE(APAD.Prepay_Dist_Lookup_Code, 'TAX DIFF', APAD.Amount, 0))
1449 INTO l_sum_prepaid_amount,
1450 l_sum_tax_diff_amount
1451 FROM AP_Prepay_App_Dists APAD,
1452 ap_invoice_distributions_all aid
1453 WHERE APAD.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
1454 AND apad.invoice_distribution_id = aid.invoice_distribution_id
1455 AND aid.invoice_id = l_prepay_dist_rec.invoice_id;
1456
1457
1458 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1459 l_log_msg := 'Updating the prorated prepaid amounts';
1460 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1461 END IF;
1462
1463 IF NVL(l_sum_prepaid_amount, 0) <> l_prepay_dist_rec.amount THEN -- added for bug12858105
1464
1465 -- bug 9240725
1466 IF(l_inv_rec.invoice_currency_code=ap_accounting_pay_pkg.g_base_currency_code) THEN
1467
1468 UPDATE AP_Prepay_App_Dists APAD
1469 SET APAD.Amount = APAD.Amount - NVL(l_sum_prepaid_amount,0) + l_prepay_dist_rec.amount,
1470 APAD.BASE_AMT_AT_PREPAY_XRATE = APAD.BASE_AMT_AT_PREPAY_XRATE - NVL(l_sum_prepaid_amount,0) + l_prepay_dist_rec.amount,
1471 APAD.BASE_AMT_AT_PREPAY_PAY_XRATE=APAD.BASE_AMT_AT_PREPAY_PAY_XRATE - NVL(l_sum_prepaid_amount,0) + l_prepay_dist_rec.amount,
1472 APAD.BASE_AMOUNT=APAD.BASE_AMOUNT - NVL(l_sum_prepaid_amount,0) + l_prepay_dist_rec.amount,
1473 APAD.BASE_AMT_AT_PREPAY_CLR_XRATE=APAD.BASE_AMT_AT_PREPAY_CLR_XRATE - NVL(l_sum_prepaid_amount,0) + l_prepay_dist_rec.amount, rounding_amt = nvl(l_sum_prepaid_amount, 0) + l_prepay_dist_rec.amount
1474 WHERE APAD.Invoice_Distribution_ID =
1475 (SELECT MAX(APAD1.Invoice_Distribution_ID)
1476 FROM AP_Prepay_App_Dists APAD1
1477 WHERE APAD1.Accounting_Event_ID = p_xla_event_rec.event_id
1478 AND APAD1.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
1479 AND APAD1.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
1480 'PREPAY APPL NONREC TAX')
1481 AND ABS(APAD1.Amount) =
1482 (SELECT MAX(ABS(APAD2.Amount)) -- added ABS for bug12858105
1483 FROM AP_Prepay_App_Dists APAD2
1484 WHERE APAD2.Accounting_Event_ID = p_xla_event_rec.event_id
1485 AND APAD2.Prepay_App_Distribution_ID
1486 = l_prepay_dist_rec.invoice_distribution_id
1487 AND APAD2.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
1488 'PREPAY APPL NONREC TAX')
1489 /* bug12858105 - start */
1490 AND APAD2.Reversed_Prepay_App_Dist_Id IS NULL
1491 AND NOT EXISTS (SELECT 1
1492 FROM ap_prepay_app_dists apad2_rev,
1493 ap_prepay_history_all apph
1494 WHERE apad2_rev.reversed_prepay_app_dist_id = APAD2.prepay_app_dist_id
1495 AND apad2_rev.prepay_history_id = apph.prepay_history_id
1496 AND apph.invoice_id = p_xla_event_rec.source_id_int_1)
1497 /* bug12858105 - end */
1498 )
1499 /* bug12858105 - start */
1500 AND APAD1.Reversed_Prepay_App_Dist_Id IS NULL
1501 AND NOT EXISTS (SELECT 1
1502 FROM ap_prepay_app_dists apad1_rev,
1503 ap_prepay_history_all apph
1504 WHERE apad1_rev.reversed_prepay_app_dist_id = APAD1.prepay_app_dist_id
1505 AND apad1_rev.prepay_history_id = apph.prepay_history_id
1506 AND apph.invoice_id = p_xla_event_rec.source_id_int_1)
1507 /* bug12858105 - end */
1508 )
1509 AND APAD.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
1510 'PREPAY APPL NONREC TAX')
1511 AND APAD.Accounting_Event_ID = p_xla_event_rec.event_id
1512 AND APAD.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id;
1513
1514
1515 ELSE
1516
1517 /* Updating the prorated prepaid amounts for any rounding */
1518 UPDATE AP_Prepay_App_Dists APAD
1519 SET APAD.Amount = APAD.Amount - NVL(l_sum_prepaid_amount,0) + l_prepay_dist_rec.amount
1520 WHERE APAD.Invoice_Distribution_ID =
1521 (SELECT MAX(APAD1.Invoice_Distribution_ID)
1522 FROM AP_Prepay_App_Dists APAD1
1523 WHERE APAD1.Accounting_Event_ID = p_xla_event_rec.event_id
1524 AND APAD1.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
1525 AND APAD1.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
1526 'PREPAY APPL NONREC TAX')
1527 AND ABS(APAD1.Amount) =
1528 (SELECT MAX(ABS(APAD2.Amount)) -- adding ABS for bug12858105
1529 FROM AP_Prepay_App_Dists APAD2
1530 WHERE APAD2.Accounting_Event_ID = p_xla_event_rec.event_id
1531 AND APAD2.Prepay_App_Distribution_ID
1532 = l_prepay_dist_rec.invoice_distribution_id
1533 AND APAD2.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
1534 'PREPAY APPL NONREC TAX')
1535 /* bug12858105 - start */
1536 AND APAD2.Reversed_Prepay_App_Dist_Id IS NULL
1537 AND NOT EXISTS (SELECT 1
1538 FROM ap_prepay_app_dists apad2_rev,
1539 ap_prepay_history_all apph
1540 WHERE apad2_rev.reversed_prepay_app_dist_id = APAD2.prepay_app_dist_id
1541 AND apad2_rev.prepay_history_id = apph.prepay_history_id
1542 AND apph.invoice_id = p_xla_event_rec.source_id_int_1)
1543 /* bug12858105 - end */
1544 )
1545 /* bug12858105 - start */
1546 AND APAD1.Reversed_Prepay_App_Dist_Id IS NULL
1547 AND NOT EXISTS (SELECT 1
1548 FROM ap_prepay_app_dists apad1_rev,
1549 ap_prepay_history_all apph
1550 WHERE apad1_rev.reversed_prepay_app_dist_id = APAD1.prepay_app_dist_id
1551 AND apad1_rev.prepay_history_id = apph.prepay_history_id
1552 AND apph.invoice_id = p_xla_event_rec.source_id_int_1)
1553 /* bug12858105 - end */
1554 )
1555 AND APAD.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
1556 'PREPAY APPL NONREC TAX')
1557 AND APAD.Accounting_Event_ID = p_xla_event_rec.event_id
1558 AND APAD.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id;
1559
1560 END IF;
1561
1562 END IF;-- NVL(l_sum_prepaid_amount, 0) <> l_prepay_dist_rec.amount
1563
1564 IF l_prepay_dist_rec.prepay_tax_diff_amount <> 0
1565 AND NVL(l_sum_tax_diff_amount,0) <> l_prepay_dist_rec.prepay_tax_diff_amount THEN -- added for bug12858105
1566
1567 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1568 l_log_msg := 'Updating the prorated tax diff amounts';
1569 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1570 END IF;
1571
1572
1573 /* Updating the prorated tax diff amounts for any rounding */
1574 /* -- commented for bug 12858105
1575 UPDATE AP_Prepay_App_Dists APAD
1576 SET APAD.Amount = APAD.Amount - NVL(l_sum_tax_diff_amount,0) + l_prepay_dist_rec.prepay_tax_diff_amount
1577 WHERE APAD.Invoice_Distribution_ID =
1578 (SELECT MAX(APAD1.Invoice_Distribution_ID)
1579 FROM AP_Prepay_App_Dists APAD1
1580 WHERE APAD1.Accounting_Event_ID = p_xla_event_rec.event_id
1581 AND APAD1.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
1582 AND APAD1.Prepay_Dist_Lookup_Code = 'TAX DIFF'
1583 AND ABS(APAD1.Amount) =
1584 (SELECT MAX(APAD2.Amount)
1585 FROM AP_Prepay_App_Dists APAD2
1586 WHERE APAD2.Accounting_Event_ID = p_xla_event_rec.event_id
1587 AND APAD2.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
1588 AND APAD2.Prepay_Dist_Lookup_Code = 'TAX DIFF'))
1589 AND APAD.Prepay_Dist_Lookup_Code = 'TAX DIFF'
1590 AND APAD.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
1591 AND APAD.Accounting_Event_ID = p_xla_event_rec.event_id; */
1592
1593 /* bug12858105 - start */
1594 IF(l_inv_rec.invoice_currency_code=ap_accounting_pay_pkg.g_base_currency_code) THEN
1595
1596 UPDATE AP_Prepay_App_Dists APAD
1597 SET APAD.Amount = APAD.Amount- NVL(l_sum_tax_diff_amount,0) + l_prepay_dist_rec.prepay_tax_diff_amount ,
1598 APAD.BASE_AMT_AT_PREPAY_XRATE = APAD.BASE_AMT_AT_PREPAY_XRATE
1599 - NVL(l_sum_tax_diff_amount,0) + l_prepay_dist_rec.prepay_tax_diff_amount,
1600 APAD.BASE_AMT_AT_PREPAY_PAY_XRATE=APAD.BASE_AMT_AT_PREPAY_PAY_XRATE
1601 - NVL(l_sum_tax_diff_amount,0) + l_prepay_dist_rec.prepay_tax_diff_amount,
1602 APAD.BASE_AMOUNT=APAD.BASE_AMOUNT
1603 - NVL(l_sum_tax_diff_amount,0) + l_prepay_dist_rec.prepay_tax_diff_amount,
1604 APAD.BASE_AMT_AT_PREPAY_CLR_XRATE=APAD.BASE_AMT_AT_PREPAY_CLR_XRATE
1605 - NVL(l_sum_tax_diff_amount,0) + l_prepay_dist_rec.prepay_tax_diff_amount
1606 WHERE APAD.Invoice_Distribution_ID =
1607 (SELECT MAX(APAD1.Invoice_Distribution_ID)
1608 FROM AP_Prepay_App_Dists APAD1
1609 WHERE APAD1.Accounting_Event_ID = p_xla_event_rec.event_id
1610 AND APAD1.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
1611 AND APAD1.Prepay_Dist_Lookup_Code IN ('TAX DIFF')
1612 AND ABS(APAD1.Amount) =
1613 (SELECT MAX(ABS(APAD2.Amount))
1614 FROM AP_Prepay_App_Dists APAD2
1615 WHERE APAD2.Accounting_Event_ID = p_xla_event_rec.event_id
1616 AND APAD2.Prepay_App_Distribution_ID
1617 = l_prepay_dist_rec.invoice_distribution_id
1618 AND APAD2.Prepay_Dist_Lookup_Code IN ('TAX DIFF')
1619 AND APAD2.Reversed_Prepay_App_Dist_Id IS NULL
1620 AND NOT EXISTS (SELECT 1
1621 FROM ap_prepay_app_dists apad2_rev,
1622 ap_prepay_history_all apph
1623 WHERE apad2_rev.reversed_prepay_app_dist_id = APAD2.prepay_app_dist_id
1624 AND apad2_rev.prepay_history_id = apph.prepay_history_id
1625 AND apph.invoice_id = p_xla_event_rec.source_id_int_1)
1626 )
1627 AND APAD1.Reversed_Prepay_App_Dist_Id IS NULL
1628 AND NOT EXISTS (SELECT 1
1629 FROM ap_prepay_app_dists apad1_rev,
1630 ap_prepay_history_all apph
1631 WHERE apad1_rev.reversed_prepay_app_dist_id = APAD1.prepay_app_dist_id
1632 AND apad1_rev.prepay_history_id = apph.prepay_history_id
1633 AND apph.invoice_id = p_xla_event_rec.source_id_int_1)
1634 )
1635 AND APAD.Prepay_Dist_Lookup_Code IN ('TAX DIFF')
1636 AND APAD.Accounting_Event_ID = p_xla_event_rec.event_id
1637 AND APAD.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id;
1638
1639
1640 ELSE
1641
1642 /* Updating the prorated prepaid amounts for any rounding */
1643 UPDATE AP_Prepay_App_Dists APAD
1644 SET APAD.Amount = APAD.Amount- NVL(l_sum_tax_diff_amount,0) + l_prepay_dist_rec.prepay_tax_diff_amount
1645 WHERE APAD.Invoice_Distribution_ID =
1646 (SELECT MAX(APAD1.Invoice_Distribution_ID)
1647 FROM AP_Prepay_App_Dists APAD1
1648 WHERE APAD1.Accounting_Event_ID = p_xla_event_rec.event_id
1649 AND APAD1.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
1650 AND APAD1.Prepay_Dist_Lookup_Code IN ('TAX DIFF')
1651 AND ABS(APAD1.Amount) =
1652 (SELECT MAX(ABS(APAD2.Amount))
1653 FROM AP_Prepay_App_Dists APAD2
1654 WHERE APAD2.Accounting_Event_ID = p_xla_event_rec.event_id
1655 AND APAD2.Prepay_App_Distribution_ID
1656 = l_prepay_dist_rec.invoice_distribution_id
1657 AND APAD2.Prepay_Dist_Lookup_Code IN ('TAX DIFF')
1658 AND APAD2.Reversed_Prepay_App_Dist_Id IS NULL
1659 AND NOT EXISTS (SELECT 1
1660 FROM ap_prepay_app_dists apad2_rev,
1661 ap_prepay_history_all apph
1662 WHERE apad2_rev.reversed_prepay_app_dist_id = APAD2.prepay_app_dist_id
1663 AND apad2_rev.prepay_history_id = apph.prepay_history_id
1664 AND apph.invoice_id = p_xla_event_rec.source_id_int_1)
1665 )
1666 AND APAD1.Reversed_Prepay_App_Dist_Id IS NULL
1667 AND NOT EXISTS (SELECT 1
1668 FROM ap_prepay_app_dists apad1_rev,
1669 ap_prepay_history_all apph
1670 WHERE apad1_rev.reversed_prepay_app_dist_id = APAD1.prepay_app_dist_id
1671 AND apad1_rev.prepay_history_id = apph.prepay_history_id
1672 AND apph.invoice_id = p_xla_event_rec.source_id_int_1)
1673 )
1674 AND APAD.Prepay_Dist_Lookup_Code IN ('TAX DIFF')
1675 AND APAD.Accounting_Event_ID = p_xla_event_rec.event_id
1676 AND APAD.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id;
1677
1678 END IF;
1679 /* bug12858105 - end */
1680
1681 END IF; -- NVL(l_sum_tax_diff_amount, 0) <> l_prepay_dist_rec.prepay_tax_diff_amount
1682
1683
1684 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1685 l_log_msg := 'Calling procedure P_Acctg_Pay_Round_Pkg.Do_Rounding for Invoice_ID: '
1686 || l_inv_rec.invoice_id;
1687 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1688 END IF;
1689
1690 AP_Acctg_Pay_Round_Pkg.Do_Rounding
1691 (NULL, --p_xla_event_rec,
1692 l_pay_hist_rec,
1693 l_clr_hist_rec,
1694 l_inv_rec,
1695 NULL, -- l_inv_pay_rec
1696 l_prepay_inv_rec,
1697 l_prepay_hist_rec,
1698 l_prepay_dist_rec,
1699 l_curr_calling_sequence);
1700
1701 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1702 l_log_msg := 'Procedure P_Acctg_Pay_Round_Pkg.Do_Rounding executed';
1703 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1704 END IF;
1705
1706 END IF; --l_rounding_adjust_id = p_xla_event_rec.event_id 8201141
1707
1708 END LOOP;
1709 CLOSE Prepay_Adj_Dists;
1710
1711 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1712 l_log_msg := 'Calling procedure AP_Acctg_Prepay_Dist_Pkg.Update_Gain_Loss_Ind';
1713 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1714 END IF;
1715
1716 AP_Acctg_Prepay_Dist_Pkg.Update_Gain_Loss_Ind
1717 (p_xla_event_rec,
1718 l_curr_calling_sequence);
1719
1720 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1721 l_log_msg := 'Procedure AP_Acctg_Prepay_Dist_Pkg.Updated_Gain_Loss_Ind executed';
1722 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1723 END IF;
1724
1725
1726 EXCEPTION
1727 WHEN OTHERS THEN
1728 IF (SQLCODE <> -20001) THEN
1729 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1730 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1731 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1732 END IF;
1733 APP_EXCEPTION.RAISE_EXCEPTION;
1734
1735 END Prepay_Dist_Cascade_Adj;
1736
1737
1738
1739 ---------------------------------------------------------------------
1740 -- Procedure Prepay_Dist_Proc
1741 -- This procedure prorates the prepayment application amounts for each
1742 -- distribution and inserts the calculated values into prepayment
1743 -- application distribution table
1744 -- Also calculates ERV
1745 ---------------------------------------------------------------------
1746 -- Bug 6698125. Added p_xla_event_rec parameter
1747 PROCEDURE Prepay_Dist_Proc
1748 (p_pay_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
1749 ,p_clr_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
1750 ,p_inv_rec IN ap_accounting_pay_pkg.r_invoices_info
1751 ,p_prepay_inv_rec IN ap_accounting_pay_pkg.r_invoices_info
1752 ,p_prepay_hist_rec IN r_prepay_hist_info
1753 ,p_prepay_dist_rec IN r_prepay_dist_info
1754 ,p_inv_dist_rec IN ap_accounting_pay_pkg.r_inv_dist_info
1755 ,p_xla_event_rec IN ap_accounting_pay_pkg.r_xla_event_info
1756 ,p_calc_mode IN VARCHAR2
1757 ,p_final_payment IN BOOLEAN
1758 ,p_calling_sequence IN VARCHAR2
1759 ) IS
1760
1761
1762 l_curr_calling_sequence VARCHAR2(2000);
1763 l_dist_amount NUMBER;
1764 l_prorated_amount NUMBER;
1765 l_prorated_base_amount NUMBER;
1766 l_inv_dist_amount NUMBER;
1767 l_prorated_pay_amt NUMBER;
1768 l_prorated_clr_amt NUMBER;
1769 l_total_paid_amt NUMBER;
1770 l_total_prepaid_amt NUMBER;
1771 l_total_inv_dist_amt NUMBER;
1772 l_total_bank_curr_amt NUMBER;
1773 l_total_dist_amount NUMBER;
1774 l_qty_variance NUMBER;
1775 l_base_qty_variance NUMBER;
1776 l_amt_variance NUMBER;
1777 l_base_amt_variance NUMBER;
1778 --l_awt_prorated_amt NUMBER; --8364229 --commenting for bug8882706
1779 l_pad_rec AP_PREPAY_APP_DISTS%ROWTYPE;
1780
1781 -- Logging Infra:
1782 l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Dist_Proc';
1783 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1784 l_total_inv_amount NUMBER; --Bug9106549
1785 l_total_awt_amount NUMBER; --Bug9106549
1786
1787
1788 BEGIN
1789
1790 l_curr_calling_sequence := 'AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Proc<- ' ||
1791 p_calling_sequence;
1792
1793
1794 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1795 l_log_msg := 'Begin of procedure '|| l_procedure_name;
1796 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1797 END IF;
1798 --Bug 8244163 Calculating l_total_inv_amt = total invoice amt with out AWT / PREPAY lines
1799 --Replacing p_inv_rec.invoice_amount with g_total_inv_amount. Because invoice_amount will be
1800 --adjusted when prepayment application is happened with option "prepayment on Invoice"
1801
1802 /* -- 8244163
1803 SELECT SUM(NVL(AID.Amount,0))
1804 INTO l_total_dist_amount
1805 FROM AP_Invoice_Distributions_All AID
1806 WHERE AID.Invoice_ID = p_inv_rec.invoice_id
1807 AND AID.Line_Type_Lookup_Code <> 'PREPAY'
1808 AND AID.Prepay_Distribution_ID IS NULL
1809 AND AID.Prepay_Tax_Parent_ID IS NULL -- For tax dists created in R11.5
1810 AND AID.AWT_Invoice_Payment_ID IS NULL
1811 AND NVL(AID.Cancellation_Flag,'N') <> 'Y' -- BUG 6513956
1812 --bug fix 6909150
1813 AND NOT EXISTS (SELECT 1
1814 FROM xla_events
1815 WHERE event_id = AID.accounting_event_id
1816 AND event_type_code IN ('INVOICE CANCELLED',
1817 'CREDIT MEMO CANCELLED',
1818 'DEBIT MEMO CANCELLED'));
1819 */
1820 l_total_dist_amount := g_total_dist_amount; --8244163
1821 l_total_inv_amount := G_Total_Inv_amount; --Bug9106549
1822 l_total_awt_amount := g_total_awt_amount; --Bug9106549
1823
1824 g_total_dist_amt := g_total_dist_amt + p_inv_dist_rec.amount;
1825
1826
1827 IF (p_calc_mode = 'A') THEN
1828
1829 -- If this payment is a final payment for the invoice then we should make sure
1830 -- that the sum of prepay appl dists amount should be equal to the distribution
1831 -- total. This way the liability is fully relieved.
1832 IF p_final_payment = TRUE THEN
1833
1834 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1835 l_log_msg := 'Calling procedure AP_Accounting_Pay_Pkg.Get_Pay_Sum';
1836 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1837 END IF;
1838
1839 AP_Accounting_Pay_Pkg.Get_Pay_Sum
1840 (p_inv_dist_rec.invoice_distribution_id,
1841 'PAYMENT CREATED',
1842 l_total_paid_amt,
1843 l_total_inv_dist_amt,
1844 l_total_bank_curr_amt,
1845 l_curr_calling_sequence);
1846
1847
1848 l_total_prepaid_amt := AP_Accounting_Pay_Pkg.Get_Prepay_Sum
1849 (p_inv_dist_rec.invoice_distribution_id,
1850 l_curr_calling_sequence);
1851
1852
1853 -- Converting the distribution and prepaid amount into payment currency for
1854 -- cross currency invoices.
1855 IF (p_inv_rec.invoice_currency_code <> p_inv_rec.payment_currency_code) THEN
1856
1857 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1858 l_log_msg := 'Invoice curr diff than payment curr';
1859 l_log_msg := l_log_msg || ' Converting l_total_paid_amt to invoice curr';
1860 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1861 END IF;
1862
1863 l_total_paid_amt := GL_Currency_API.Convert_Amount(
1864 p_inv_rec.payment_currency_code,
1865 p_inv_rec.invoice_currency_code,
1866 p_inv_rec.payment_cross_rate_date,
1867 'EMU FIXED',
1868 l_total_paid_amt);
1869
1870 END IF;
1871
1872
1873 /* If this payment is a final payment then we should make sure that the
1874 distributed payment amount equals the distribution amount. This way the
1875 the liability for the distribution is relieved completely */
1876
1877 IF (p_inv_dist_rec.line_type_lookup_code = 'AWT') THEN --8364229
1878 l_prorated_amount := -1 * (-1*p_inv_dist_rec.amount - l_total_paid_amt +
1879 l_total_prepaid_amt);
1880 ELSE
1881 --commenting out the following code for bug8882706 as the same will be handled
1882 --now in ap_accounting_pay_pkg.get_prepay_sum
1883 /*SELECT SUM(apad.amount) INTO l_awt_prorated_amt
1884 FROM ap_prepay_app_dists apad
1885 WHERE apad.prepay_dist_lookup_code = 'AWT'
1886 AND apad.awt_related_id = p_inv_dist_rec.invoice_distribution_id
1887 AND apad.invoice_distribution_id in
1888 (SELECT invoice_distribution_id
1889 FROM ap_invoice_distributions_all
1890 WHERE invoice_id = p_inv_rec.invoice_id
1891 AND line_type_lookup_code = 'AWT');
1892 */
1893 l_prorated_amount := -1 * (p_inv_dist_rec.amount - l_total_paid_amt +
1894 l_total_prepaid_amt );
1895 END IF; --p_inv_dist_rec.line_type_lookup_code = 'AWT' 8364229 ends
1896
1897 ELSE
1898
1899 IF g_total_dist_amt = l_total_dist_amount THEN -- last dist rec
1900
1901 -- To avoid rounding, massage the last (biggest) line
1902 l_prorated_amount := p_prepay_dist_rec.amount - g_total_prorated_amt;
1903 ELSE
1904
1905 IF g_total_inv_amount = 0 THEN --8244163
1906 l_prorated_amount := 0;
1907
1908 ELSE
1909
1910 IF (p_inv_dist_rec.line_type_lookup_code = 'AWT') THEN
1911 l_prorated_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
1912 (p_prepay_dist_rec.amount * (-1*p_inv_dist_rec.amount)
1913 / l_total_dist_amount,
1914 p_inv_rec.invoice_currency_code);
1915 ELSE
1916 /* l_prorated_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
1917 (p_prepay_dist_rec.amount * p_inv_dist_rec.amount
1918 / g_total_inv_amount, --8244163
1919 p_inv_rec.invoice_currency_code);
1920 */
1921 --Bug9106549
1922
1923 SELECT p_inv_dist_rec.amount
1924 / l_total_inv_amount
1925 * (p_prepay_dist_rec.amount
1926 - (
1927 l_total_awt_amount / l_total_dist_amount * p_prepay_dist_rec.amount
1928 )
1929 )
1930 +
1931 nvl(
1932 (select sum(amount) / l_total_dist_amount * p_prepay_dist_rec.amount
1933 from ap_invoice_distributions_all aid
1934 where aid.invoice_id=p_inv_rec.invoice_id
1935 and aid.awt_invoice_payment_id is null
1936 and aid.awt_related_id=p_inv_dist_rec.invoice_distribution_id
1937 ), 0)
1938 INTO l_prorated_amount
1939 from sys.dual ;
1940
1941 l_prorated_amount := ap_utilities_pkg.ap_round_currency(l_prorated_amount, p_inv_rec.invoice_currency_code);
1942
1943 END IF; -- IF AWT line type
1944
1945 END IF;
1946 END IF;
1947
1948 END IF;
1949
1950 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1951 l_log_msg := 'Value of l_prorated_amount = '|| l_prorated_amount;
1952 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1953 END IF;
1954
1955
1956 IF (p_inv_dist_rec.line_type_lookup_code <> 'AWT') THEN
1957 g_total_prorated_amt := g_total_prorated_amt + l_prorated_amount;
1958 END IF;
1959
1960
1961 /* If this is a cascade event then we will create new payment distributions
1962 for the existing invoice distributions that have already been distributed to
1963 this payment in order to adjust the payments as a result of adjusting the
1964 invoice */
1965 ELSE
1966
1967 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1968 l_log_msg := 'Calculating prorated amount for cascade adjustment';
1969 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1970 END IF;
1971
1972 IF g_total_inv_amount = 0 THEN --8244163
1973 l_prorated_amount := 0;
1974 ELSE
1975
1976 -- In case of cascade events we will recalculate the prorated amount and subtract
1977 -- this amount from the already calculated amount previously so that this would
1978 -- give us the amount that needs to be adjusted
1979 l_prorated_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
1980 (((p_inv_dist_rec.amount * p_prepay_dist_rec.amount)
1981 / g_total_inv_amount) --8244163
1982 - AP_Accounting_Pay_Pkg.get_casc_prepay_sum
1983 (p_inv_dist_rec.invoice_distribution_id,
1984 p_prepay_dist_rec.invoice_distribution_id,
1985 l_curr_calling_sequence),
1986 p_inv_rec.invoice_currency_code);
1987
1988 END IF;
1989 END IF;
1990
1991
1992 -- Populate prepay appl dist rec
1993
1994 l_pad_rec.prepay_history_id := p_prepay_hist_rec.prepay_history_id;
1995
1996 IF p_inv_dist_rec.line_type_lookup_code = 'AWT' THEN
1997 l_pad_rec.prepay_dist_lookup_code := 'AWT';
1998 l_pad_rec.awt_related_id := p_inv_dist_rec.awt_related_id;
1999 ELSIF p_prepay_dist_rec.line_type_lookup_code = 'NONREC_TAX' THEN
2000 l_pad_rec.prepay_dist_lookup_code := 'PREPAY APPL NONREC TAX';
2001 ELSIF p_prepay_dist_rec.line_type_lookup_code = 'REC_TAX' THEN
2002 l_pad_rec.prepay_dist_lookup_code := 'PREPAY APPL REC TAX';
2003 ELSE
2004 l_pad_rec.prepay_dist_lookup_code := 'PREPAY APPL';
2005 END IF;
2006
2007 l_pad_rec.invoice_distribution_id := p_inv_dist_rec.invoice_distribution_id;
2008 l_pad_rec.prepay_app_distribution_id := p_prepay_dist_rec.invoice_distribution_id;
2009
2010 -- bug9038462, added the below if condition to ensure that the APAD
2011 -- records have an appropriate Accounting_Event_id in case the
2012 -- Accounting_Event_id has been already generated, and the APAD
2013 -- and APH records are being regenerated
2014 --
2015 IF p_calc_mode = 'A' THEN
2016 l_pad_rec.accounting_event_id := p_prepay_dist_rec.accounting_event_id;
2017 ELSE
2018 l_pad_rec.accounting_event_id := p_xla_event_rec.event_id;
2019 END IF;
2020
2021
2022 l_pad_rec.amount := l_prorated_amount;
2023
2024 -- bug9271242, added the NVLs in derivation of exchange dates so
2025 -- as to ensure no difference between Item Expense and Prepaid
2026 -- Expense for Accounting Prepayment Applications
2027 --
2028 l_pad_rec.prepay_exchange_date := nvl(p_prepay_inv_rec.exchange_date,
2029 p_prepay_inv_rec.gl_date);
2030 l_pad_rec.prepay_pay_exchange_date := nvl(p_pay_hist_rec.pmt_to_base_xrate_date,
2031 p_pay_hist_rec.accounting_date);
2032 l_pad_rec.prepay_clr_exchange_date := nvl(p_clr_hist_rec.bank_to_base_xrate_date,
2033 p_clr_hist_rec.accounting_date);
2034
2035 l_pad_rec.prepay_exchange_rate := p_prepay_inv_rec.exchange_rate;
2036 l_pad_rec.prepay_pay_exchange_rate := p_pay_hist_rec.pmt_to_base_xrate;
2037 l_pad_rec.prepay_clr_exchange_rate := p_clr_hist_rec.bank_to_base_xrate;
2038
2039 l_pad_rec.prepay_exchange_rate_type := p_prepay_inv_rec.exchange_rate_type;
2040 l_pad_rec.prepay_pay_exchange_rate_type := p_pay_hist_rec.pmt_to_base_xrate_type;
2041 l_pad_rec.prepay_clr_exchange_rate_type := p_clr_hist_rec.bank_to_base_xrate_type;
2042
2043
2044 l_pad_rec.base_amt_at_prepay_xrate := AP_Accounting_Pay_Pkg.Get_Base_Amount
2045 (l_prorated_amount,
2046 p_prepay_inv_rec.invoice_currency_code,
2047 p_inv_rec.base_currency_code,
2048 p_prepay_inv_rec.exchange_rate_type,
2049 p_prepay_inv_rec.exchange_date,
2050 p_prepay_inv_rec.exchange_rate,
2051 l_curr_calling_sequence);
2052
2053
2054 IF (p_inv_rec.invoice_currency_code <> p_pay_hist_rec.pmt_currency_code) THEN
2055 l_prorated_pay_amt := AP_UTILITIES_PKG.AP_Round_Currency(
2056 l_prorated_amount * p_inv_rec.payment_cross_rate,
2057 p_pay_hist_rec.pmt_currency_code);
2058 ELSE
2059 l_prorated_pay_amt := l_prorated_amount;
2060 END IF;
2061
2062 l_pad_rec.base_amt_at_prepay_pay_xrate := AP_Accounting_Pay_Pkg.Get_Base_Amount
2063 (l_prorated_pay_amt,
2064 p_pay_hist_rec.pmt_currency_code,
2065 p_inv_rec.base_currency_code,
2066 p_pay_hist_rec.pmt_to_base_xrate_type,
2067 p_pay_hist_rec.pmt_to_base_xrate_date,
2068 p_pay_hist_rec.pmt_to_base_xrate,
2069 l_curr_calling_sequence);
2070
2071 IF (p_clr_hist_rec.pmt_currency_code <> p_clr_hist_rec.bank_currency_code) THEN
2072
2073 l_prorated_clr_amt := AP_UTILITIES_PKG.AP_Round_Currency(
2074 l_prorated_pay_amt * p_clr_hist_rec.pmt_to_base_xrate,
2075 p_pay_hist_rec.bank_currency_code);
2076 ELSE
2077 l_prorated_clr_amt := l_prorated_pay_amt;
2078 END IF;
2079
2080 l_pad_rec.base_amt_at_prepay_clr_xrate := AP_Accounting_Pay_Pkg.Get_Base_Amount
2081 (l_prorated_clr_amt,
2082 p_clr_hist_rec.bank_currency_code,
2083 p_inv_rec.base_currency_code,
2084 p_clr_hist_rec.bank_to_base_xrate_type,
2085 p_clr_hist_rec.bank_to_base_xrate_date,
2086 p_clr_hist_rec.bank_to_base_xrate,
2087 l_curr_calling_sequence);
2088
2089
2090 l_pad_rec.base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
2091 (l_prorated_amount,
2092 p_inv_rec.invoice_currency_code,
2093 p_inv_rec.base_currency_code,
2094 p_inv_rec.exchange_rate_type,
2095 p_inv_rec.exchange_date,
2096 p_inv_rec.exchange_rate,
2097 l_curr_calling_sequence);
2098
2099
2100 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2101 l_log_msg := 'Calling procedure Prepay_Dist_Insert';
2102 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2103 END IF;
2104
2105
2106 IF p_inv_dist_rec.quantity_variance IS NOT NULL THEN
2107
2108 IF p_inv_dist_rec.amount = 0 THEN
2109 l_qty_variance := 0;
2110 ELSE
2111 l_qty_variance := AP_Utilities_PKG.AP_Round_Currency(
2112 ((p_inv_dist_rec.quantity_variance * l_prorated_amount) /
2113 p_inv_dist_rec.amount),
2114 p_inv_rec.invoice_currency_code);
2115 END IF;
2116
2117 IF p_inv_dist_rec.base_amount = 0 THEN
2118 l_base_qty_variance := 0;
2119 ELSE
2120 l_base_qty_variance := AP_Utilities_PKG.AP_Round_Currency(
2121 ((p_inv_dist_rec.base_quantity_variance
2122 * l_pad_rec.base_amount)
2123 / p_inv_dist_rec.base_amount),
2124 p_inv_rec.base_currency_code);
2125
2126 END IF;
2127 END IF;
2128
2129 IF p_inv_dist_rec.amount_variance IS NOT NULL THEN
2130
2131 IF p_inv_dist_rec.amount = 0 THEN
2132 l_amt_variance := 0;
2133 ELSE
2134 l_amt_variance := AP_Utilities_PKG.AP_Round_Currency(
2135 ((p_inv_dist_rec.amount_variance * l_prorated_amount) /
2136 p_inv_dist_rec.amount),
2137 p_inv_rec.invoice_currency_code);
2138 END IF;
2139
2140 IF p_inv_dist_rec.base_amount = 0 THEN
2141 l_base_amt_variance := 0;
2142 ELSE
2143 l_base_amt_variance := AP_Utilities_PKG.AP_Round_Currency(
2144 ((p_inv_dist_rec.base_amount_variance
2145 * l_pad_rec.base_amount)
2146 / p_inv_dist_rec.base_amount),
2147 p_inv_rec.base_currency_code);
2148 END IF;
2149 END IF;
2150
2151 l_pad_rec.quantity_variance := l_qty_variance;
2152 l_pad_rec.invoice_base_qty_variance := l_base_qty_variance;
2153 l_pad_rec.amount_variance := l_amt_variance;
2154 l_pad_rec.invoice_base_amt_variance := l_base_amt_variance;
2155
2156
2157 Prepay_Dist_Insert
2158 (l_pad_rec,
2159 l_curr_calling_sequence);
2160
2161 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2162 l_log_msg := 'Procedure Prepay_Dist_Insert executed';
2163 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2164 END IF;
2165
2166
2167 IF (p_prepay_dist_rec.prepay_tax_diff_amount <> 0) THEN
2168
2169
2170 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2171 l_log_msg := 'Calling procedure Prepay_Dist_Tax_Diff';
2172 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2173 END IF;
2174
2175 -- Creating the tax diff distributions
2176 Prepay_Dist_Tax_Diff
2177 (p_pay_hist_rec,
2178 p_clr_hist_rec,
2179 p_inv_rec,
2180 p_prepay_inv_rec,
2181 p_prepay_hist_rec,
2182 p_prepay_dist_rec,
2183 p_inv_dist_rec,
2184 p_calc_mode,
2185 l_curr_calling_sequence);
2186
2187 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2188 l_log_msg := 'Procedure Prepay_Dist_Tax_Diff executed';
2189 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2190 END IF;
2191
2192
2193 END IF;
2194
2195
2196 IF (p_inv_dist_rec.po_distribution_id IS NOT NULL AND
2197 p_inv_rec.invoice_currency_code <> p_inv_rec.base_currency_code) THEN
2198
2199 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2200 l_log_msg := 'Calling procedure Prepay_Dist_ERV';
2201 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2202 END IF;
2203
2204 -- Creating ERV distributions
2205 Prepay_Dist_ERV
2206 (p_pay_hist_rec,
2207 p_clr_hist_rec,
2208 p_inv_rec,
2209 p_prepay_inv_rec,
2210 p_prepay_hist_rec,
2211 p_prepay_dist_rec,
2212 p_inv_dist_rec,
2213 l_prorated_amount,
2214 l_curr_calling_sequence);
2215
2216 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2217 l_log_msg := 'Procedure Prepay_Dist_ERV executed';
2218 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2219 END IF;
2220
2221 END IF;
2222
2223 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2224 l_log_msg := 'Procedure Prepay_Dist_Insert executed';
2225 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2226 END IF;
2227
2228 EXCEPTION
2229 WHEN OTHERS THEN
2230 IF (SQLCODE <> -20001) THEN
2231 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2232 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2233 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2234 END IF;
2235 APP_EXCEPTION.RAISE_EXCEPTION;
2236
2237 END Prepay_Dist_Proc;
2238
2239
2240
2241 ---------------------------------------------------------------------
2242 -- Procedure Prepay_Dist_Tax_Diff
2243 -- This procedure prorates the tax difference amounts for each
2244 -- distribution and inserts the calculated values into prepayment
2245 -- application distribution table
2246 ---------------------------------------------------------------------
2247
2248 PROCEDURE Prepay_Dist_Tax_Diff
2249 (p_pay_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
2250 ,p_clr_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
2251 ,p_inv_rec IN ap_accounting_pay_pkg.r_invoices_info
2252 ,p_prepay_inv_rec IN ap_accounting_pay_pkg.r_invoices_info
2253 ,p_prepay_hist_rec IN r_prepay_hist_info
2254 ,p_prepay_dist_rec IN r_prepay_dist_info
2255 ,p_inv_dist_rec IN ap_accounting_pay_pkg.r_inv_dist_info
2256 ,p_calc_mode IN VARCHAR2
2257 ,p_calling_sequence IN VARCHAR2
2258 ) IS
2259
2260
2261 l_curr_calling_sequence VARCHAR2(2000);
2262 l_prorated_amount NUMBER;
2263 l_prorated_pay_amt NUMBER;
2264 l_prorated_clr_amt NUMBER;
2265
2266 l_pad_rec AP_PREPAY_APP_DISTS%ROWTYPE;
2267
2268 -- Logging Infra:
2269 l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Dist_Tax_Diff';
2270 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2271
2272
2273 BEGIN
2274
2275 l_curr_calling_sequence := 'AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Tax_Diff<- ' ||
2276 p_calling_sequence;
2277
2278 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2279 l_log_msg := 'Begin of procedure '|| l_procedure_name;
2280 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2281 END IF;
2282
2283
2284 IF p_calc_mode = 'A' THEN
2285 IF g_total_dist_amt = g_total_inv_amount THEN -- last dist rec --8244163
2286
2287 -- To avoid rounding, massage the last (biggest) line
2288 l_prorated_amount := p_prepay_dist_rec.prepay_tax_diff_amount - g_total_tax_diff_amt;
2289 ELSE
2290
2291 IF g_total_inv_amount = 0 THEN --8244163
2292 l_prorated_amount := 0;
2293
2294 ELSE
2295 l_prorated_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
2296 (p_prepay_dist_rec.prepay_tax_diff_amount * p_inv_dist_rec.amount
2297 / g_total_inv_amount,
2298 p_inv_rec.invoice_currency_code);
2299
2300 END IF;
2301 END IF;
2302
2303 ELSE
2304
2305 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2306 l_log_msg := 'Calculating prorated amount for cascade adjustment';
2307 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2308 END IF;
2309
2310 IF g_total_inv_amount = 0 THEN
2311 l_prorated_amount := 0;
2312 ELSE
2313
2314 -- In case of cascade events we will recalculate the prorated amount and subtract
2315 -- this amount from the already calculated amount previously so that this would
2316 -- give us the amount that needs to be adjusted
2317 l_prorated_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY
2318 (((p_inv_dist_rec.amount * p_prepay_dist_rec.prepay_tax_diff_amount)
2319 / g_total_inv_amount)
2320 - AP_Accounting_Pay_Pkg.get_casc_tax_diff_sum
2321 (p_inv_dist_rec.invoice_distribution_id,
2322 p_prepay_dist_rec.invoice_distribution_id,
2323 l_curr_calling_sequence),
2324 p_inv_rec.invoice_currency_code);
2325
2326 END IF;
2327 END IF;
2328
2329 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2330 l_log_msg := 'Value for l_prorated_amount = '|| l_prorated_amount;
2331 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2332 END IF;
2333
2334
2335 g_total_tax_diff_amt := g_total_tax_diff_amt + l_prorated_amount;
2336
2337
2338 -- Populate prepay appl dist rec
2339
2340 l_pad_rec.prepay_history_id := p_prepay_hist_rec.prepay_history_id;
2341 l_pad_rec.prepay_dist_lookup_code := 'TAX DIFF';
2342 l_pad_rec.invoice_distribution_id := p_inv_dist_rec.invoice_distribution_id;
2343 l_pad_rec.prepay_app_distribution_id := p_prepay_dist_rec.invoice_distribution_id;
2344 l_pad_rec.accounting_event_id := p_prepay_dist_rec.accounting_event_id;
2345
2346 l_pad_rec.amount := l_prorated_amount;
2347
2348 l_pad_rec.prepay_exchange_date := p_prepay_inv_rec.exchange_date;
2349 l_pad_rec.prepay_pay_exchange_date := p_pay_hist_rec.pmt_to_base_xrate_date;
2350 l_pad_rec.prepay_clr_exchange_date := p_clr_hist_rec.bank_to_base_xrate_date;
2351
2352 l_pad_rec.prepay_exchange_rate := p_prepay_inv_rec.exchange_rate;
2353 l_pad_rec.prepay_pay_exchange_rate := p_pay_hist_rec.pmt_to_base_xrate;
2354 l_pad_rec.prepay_clr_exchange_rate := p_clr_hist_rec.bank_to_base_xrate;
2355
2356 l_pad_rec.prepay_exchange_rate_type := p_prepay_inv_rec.exchange_rate_type;
2357 l_pad_rec.prepay_pay_exchange_rate_type := p_pay_hist_rec.pmt_to_base_xrate_type;
2358 l_pad_rec.prepay_clr_exchange_rate_type := p_clr_hist_rec.bank_to_base_xrate_type;
2359
2360
2361 l_pad_rec.base_amt_at_prepay_xrate := AP_Accounting_Pay_Pkg.Get_Base_Amount
2362 (l_prorated_amount,
2363 p_prepay_inv_rec.invoice_currency_code,
2364 p_inv_rec.base_currency_code,
2365 p_prepay_inv_rec.exchange_rate_type,
2366 p_prepay_inv_rec.exchange_date,
2367 p_prepay_inv_rec.exchange_rate,
2368 l_curr_calling_sequence);
2369
2370 IF (p_inv_rec.invoice_currency_code <> p_pay_hist_rec.pmt_currency_code) THEN
2371 l_prorated_pay_amt := l_prorated_amount * p_inv_rec.payment_cross_rate;
2372 ELSE
2373 l_prorated_pay_amt := l_prorated_amount;
2374 END IF;
2375
2376
2377 l_pad_rec.base_amt_at_prepay_pay_xrate := AP_Accounting_Pay_Pkg.Get_Base_Amount
2378 (l_prorated_pay_amt,
2379 p_pay_hist_rec.pmt_currency_code,
2380 p_inv_rec.base_currency_code,
2381 p_pay_hist_rec.pmt_to_base_xrate_type,
2382 p_pay_hist_rec.pmt_to_base_xrate_date,
2383 p_pay_hist_rec.pmt_to_base_xrate,
2384 l_curr_calling_sequence);
2385
2386 IF (p_clr_hist_rec.pmt_currency_code <> p_clr_hist_rec.bank_currency_code) THEN
2387
2388 l_prorated_clr_amt := AP_UTILITIES_PKG.AP_Round_Currency(
2389 l_prorated_pay_amt * p_clr_hist_rec.pmt_to_base_xrate,
2390 p_pay_hist_rec.bank_currency_code);
2391 ELSE
2392 l_prorated_clr_amt := l_prorated_pay_amt;
2393 END IF;
2394
2395 l_pad_rec.base_amt_at_prepay_clr_xrate := AP_Accounting_Pay_Pkg.Get_Base_Amount
2396 (l_prorated_clr_amt,
2397 p_clr_hist_rec.bank_currency_code,
2398 p_inv_rec.base_currency_code,
2399 p_clr_hist_rec.bank_to_base_xrate_type,
2400 p_clr_hist_rec.bank_to_base_xrate_date,
2401 p_clr_hist_rec.bank_to_base_xrate,
2402 l_curr_calling_sequence);
2403
2404
2405 l_pad_rec.base_amount := AP_Accounting_Pay_Pkg.Get_Base_Amount
2406 (l_prorated_amount,
2407 p_inv_rec.invoice_currency_code,
2408 p_inv_rec.base_currency_code,
2409 p_inv_rec.exchange_rate_type,
2410 p_inv_rec.exchange_date,
2411 p_inv_rec.exchange_rate,
2412 l_curr_calling_sequence);
2413
2414
2415 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2416 l_log_msg := 'Calling procedure Prepay_Dist_Insert';
2417 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2418 END IF;
2419
2420 Prepay_Dist_Insert
2421 (l_pad_rec,
2422 l_curr_calling_sequence);
2423
2424
2425 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2426 l_log_msg := 'Procedure Prepay_Dist_Insert executed';
2427 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2428 END IF;
2429
2430 EXCEPTION
2431 WHEN OTHERS THEN
2432 IF (SQLCODE <> -20001) THEN
2433 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2434 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2435 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2436 END IF;
2437 APP_EXCEPTION.RAISE_EXCEPTION;
2438
2439 END Prepay_Dist_Tax_Diff;
2440
2441
2442
2443
2444 ---------------------------------------------------------------------
2445 -- Procedure Prepay_Dist_ERV
2446 -- This procedure calculates the ERV base amounts for the ERV distributions
2447 -- and inserts the calculated values into prepay appl payment dists table
2448 ---------------------------------------------------------------------
2449
2450 PROCEDURE Prepay_Dist_ERV
2451 (p_pay_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
2452 ,p_clr_hist_rec IN ap_accounting_pay_pkg.r_pay_hist_info
2453 ,p_inv_rec IN ap_accounting_pay_pkg.r_invoices_info
2454 ,p_prepay_inv_rec IN ap_accounting_pay_pkg.r_invoices_info
2455 ,p_prepay_hist_rec IN r_prepay_hist_info
2456 ,p_prepay_dist_rec IN r_prepay_dist_info
2457 ,p_inv_dist_rec IN ap_accounting_pay_pkg.r_inv_dist_info
2458 ,p_prorated_amount IN NUMBER
2459 ,p_calling_sequence IN VARCHAR2
2460 ) IS
2461
2462 l_curr_calling_sequence VARCHAR2(2000);
2463 l_po_exchange_rate NUMBER;
2464 l_po_pay_exchange_rate NUMBER;
2465 l_pay_erv_amount NUMBER;
2466 l_clr_erv_amount NUMBER;
2467 l_inv_erv_amount NUMBER;
2468 l_pad_rec AP_PREPAY_APP_DISTS%ROWTYPE;
2469
2470 -- Logging Infra:
2471 l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Dist_ERV';
2472 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2473
2474 BEGIN
2475
2476
2477 l_curr_calling_sequence := 'AP_ACCTG_PREPAY_DIST_PKG.PrePay_Dist_ERV<- ' ||
2478 p_calling_sequence;
2479
2480
2481 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2482 l_log_msg := 'Begin of procedure '|| l_procedure_name;
2483 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2484 END IF;
2485
2486
2487 IF p_inv_dist_rec.rcv_transaction_id IS NOT NULL THEN
2488
2489 SELECT Currency_Conversion_Rate
2490 INTO l_po_exchange_rate
2491 FROM rcv_transactions
2492 WHERE transaction_id = p_inv_dist_rec.rcv_transaction_id;
2493
2494 ELSE
2495
2496 SELECT Rate
2497 INTO l_po_exchange_rate
2498 FROM PO_Distributions_All
2499 WHERE PO_Distribution_ID = p_inv_dist_rec.PO_Distribution_ID;
2500
2501 END IF;
2502
2503 IF p_inv_rec.invoice_currency_code <> p_inv_rec.payment_currency_code THEN
2504 l_po_pay_exchange_rate := l_po_exchange_rate / p_inv_rec.payment_cross_rate;
2505 ELSE
2506 l_po_pay_exchange_rate := l_po_exchange_rate;
2507 END IF;
2508
2509
2510 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2511 l_log_msg := 'Value of l_po_pay_exchange_rate = '||l_po_pay_exchange_rate;
2512 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2513 END IF;
2514
2515
2516 /* For Cash Basis ERV is Difference between Payment Exchange Rate and
2517 either Receipt Exchange rate or PO distributions exchange rate */
2518
2519 l_pay_erv_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
2520 (p_pay_hist_rec.pmt_to_base_xrate - l_po_pay_exchange_rate) *
2521 p_prorated_amount, p_pay_hist_rec.pmt_currency_code);
2522
2523
2524 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2525 l_log_msg := 'Value of l_pay_erv_amount = '||l_pay_erv_amount;
2526 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2527 END IF;
2528
2529
2530 /* If the payment accounting is at the at the clearing time, then ERV should be
2531 calculated based on the difference between Prepay payment clearing exchange rate
2532 and either Receipt Exchange rate or PO distributions exchange rate */
2533
2534 IF p_clr_hist_rec.pmt_currency_code IS NOT NULL THEN -- Bug 5701788.
2535 l_clr_erv_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
2536 (p_clr_hist_rec.pmt_to_base_xrate - l_po_pay_exchange_rate) *
2537 p_inv_dist_rec.amount, p_clr_hist_rec.pmt_currency_code);
2538 END IF;
2539
2540 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2541 l_log_msg := 'Value of l_clr_erv_amount = '||l_clr_erv_amount;
2542 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2543 END IF;
2544
2545
2546 /* In order to back out the encumbrance entries correctly during cash basis
2547 we need to calculate ERV based on the difference between the Invoice
2548 Exchange Rate and either Receipt Exchange rate or PO distributions
2549 exchange rate. This calculated ERV amount will be stored in the
2550 invoice_dist_base_amount column */
2551
2552 l_inv_erv_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
2553 (p_inv_rec.exchange_rate - l_po_exchange_rate) *
2554 p_prorated_amount, p_inv_rec.invoice_currency_code);
2555
2556 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2557 l_log_msg := 'Value of l_inv_erv_amount = '||l_inv_erv_amount;
2558 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2559 END IF;
2560
2561
2562 IF (p_inv_dist_rec.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX')) THEN
2563 l_pad_rec.prepay_dist_lookup_code := 'TAX EXCHANGE RATE VARIANCE';
2564 ELSE
2565 l_pad_rec.prepay_dist_lookup_code := 'EXCHANGE RATE VARIANCE';
2566 END IF;
2567
2568 l_pad_rec.prepay_history_id := p_prepay_hist_rec.prepay_history_id;
2569 l_pad_rec.invoice_distribution_id := p_inv_dist_rec.invoice_distribution_id;
2570 l_pad_rec.prepay_app_distribution_id := p_prepay_dist_rec.invoice_distribution_id;
2571 l_pad_rec.accounting_event_id := p_prepay_dist_rec.accounting_event_id;
2572
2573 l_pad_rec.amount := 0;
2574
2575 l_pad_rec.prepay_exchange_date := p_prepay_inv_rec.exchange_date;
2576 l_pad_rec.prepay_pay_exchange_date := p_pay_hist_rec.pmt_to_base_xrate_date;
2577 l_pad_rec.prepay_clr_exchange_date := p_clr_hist_rec.bank_to_base_xrate_date;
2578
2579 l_pad_rec.prepay_exchange_rate := p_prepay_inv_rec.exchange_rate;
2580 l_pad_rec.prepay_pay_exchange_rate := p_pay_hist_rec.pmt_to_base_xrate;
2581 l_pad_rec.prepay_clr_exchange_rate := p_clr_hist_rec.bank_to_base_xrate;
2582
2583 l_pad_rec.prepay_exchange_rate_type := p_prepay_inv_rec.exchange_rate_type;
2584 l_pad_rec.prepay_pay_exchange_rate_type := p_pay_hist_rec.pmt_to_base_xrate_type;
2585 l_pad_rec.prepay_clr_exchange_rate_type := p_clr_hist_rec.bank_to_base_xrate_type;
2586
2587
2588 l_pad_rec.base_amt_at_prepay_xrate := 0;
2589 l_pad_rec.base_amt_at_prepay_pay_xrate := l_pay_erv_amount;
2590 l_pad_rec.base_amt_at_prepay_clr_xrate := l_clr_erv_amount;
2591 l_pad_rec.base_amount := 0;
2592
2593 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2594 l_log_msg := 'Calling procedure Prepay_Dist_Insert';
2595 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2596 END IF;
2597
2598 Prepay_Dist_Insert
2599 (l_pad_rec,
2600 l_curr_calling_sequence);
2601
2602 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2603 l_log_msg := 'Procedure Prepay_Dist_Insert executed';
2604 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2605 END IF;
2606
2607
2608 EXCEPTION
2609 WHEN OTHERS THEN
2610 IF (SQLCODE <> -20001) THEN
2611 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2612 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2613 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2614 END IF;
2615 APP_EXCEPTION.RAISE_EXCEPTION;
2616
2617 END Prepay_Dist_ERV;
2618
2619
2620
2621 ---------------------------------------------------------------------
2622 -- Procedure Prepay_Dist_Reverse
2623 -- This procedure reverses the prepayment application payment distributions
2624 -- of the prepayment unapplications.
2625 --
2626 ---------------------------------------------------------------------
2627 -- Bug 6698125. Added p_xla_event_rec parameter
2628 -- Bug 7134020. Added p_inv_dist_id parameter
2629 PROCEDURE Prepay_Dist_Reverse
2630 (p_prepay_hist_rec IN r_prepay_hist_info
2631 ,p_prepay_reversal_id IN NUMBER
2632 ,P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
2633 ,p_inv_reversal_id IN NUMBER
2634 ,p_inv_dist_id IN NUMBER
2635 ,p_prepay_inv_dist_id IN NUMBER
2636 ,p_calling_sequence IN VARCHAR2
2637 ) IS
2638
2639 l_curr_calling_sequence VARCHAR2(2000);
2640
2641 -- Logging Infra:
2642 l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Dist_Reverse';
2643 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2644
2645
2646 BEGIN
2647
2648 l_curr_calling_sequence := 'AP_ACCTG_PAY_DIST_PKG.Prepay_Dist_Reverse<-' ||
2649 p_calling_sequence;
2650
2651 -- Logging Infra: Procedure level
2652 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2653 l_log_msg := 'Begin of procedure '|| l_procedure_name;
2654 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2655 END IF;
2656
2657 -- Bug 6698125. Added if condition to correctly reverse the prepay app
2658 -- distributions based on if reversed for prepayment unapplication or
2659 -- prepayment application adjusted events.
2660
2661 IF p_prepay_reversal_id IS NOT NULL THEN
2662
2663 -- bug9038462, modified this Insert into apad which takes
2664 -- care of the Non-Cascade Prepayment reversals, to stamp
2665 -- an Accounting_event_id appropriately, if present on the
2666 -- corresponding Prepayment History Record
2667 --
2668
2669 INSERT INTO AP_Prepay_App_Dists
2670 (Prepay_App_Dist_ID,
2671 Prepay_Dist_Lookup_Code,
2672 Invoice_Distribution_ID,
2673 Prepay_App_Distribution_ID,
2674 Accounting_Event_ID,
2675 Prepay_History_ID,
2676 Prepay_Exchange_Date,
2677 Prepay_Pay_Exchange_Date,
2678 Prepay_Clr_Exchange_Date,
2679 Prepay_Exchange_Rate,
2680 Prepay_Pay_Exchange_Rate,
2681 Prepay_Clr_Exchange_Rate,
2682 Prepay_Exchange_Rate_Type,
2683 Prepay_Pay_Exchange_Rate_Type,
2684 Prepay_Clr_Exchange_Rate_Type,
2685 Reversed_Prepay_App_Dist_ID,
2686 Amount,
2687 Base_Amt_At_Prepay_XRate,
2688 Base_Amt_At_Prepay_Pay_XRate,
2689 Base_Amt_At_Prepay_Clr_XRate,
2690 Base_Amount,
2691 AWT_Related_ID,
2692 PA_Addition_Flag,
2693 Quantity_Variance,
2694 Invoice_Base_Qty_Variance,
2695 Amount_Variance,
2696 Invoice_Base_Amt_Variance,
2697 Created_By,
2698 Creation_Date,
2699 Last_Update_Date,
2700 Last_Updated_By,
2701 Last_Update_Login,
2702 Program_Application_ID,
2703 Program_ID,
2704 Program_Update_Date,
2705 Request_ID
2706 )
2707 SELECT AP_Prepay_App_Dists_S.nextval,
2708 APAD.Prepay_Dist_Lookup_Code,
2709 APAD.Invoice_Distribution_ID,
2710 p_prepay_inv_dist_id,
2711 xer.event_id, --p_xla_event_rec.event_id,
2712 p_prepay_hist_rec.prepay_history_id,
2713 APAD.Prepay_Exchange_Date,
2714 APAD.Prepay_Pay_Exchange_Date,
2715 APAD.Prepay_Clr_Exchange_Date,
2716 APAD.Prepay_Exchange_Rate,
2717 APAD.Prepay_Pay_Exchange_Rate,
2718 APAD.Prepay_Clr_Exchange_Rate,
2719 APAD.Prepay_Exchange_Rate_Type,
2720 APAD.Prepay_Pay_Exchange_Rate_Type,
2721 APAD.Prepay_Clr_Exchange_Rate_Type,
2722 APAD.Prepay_App_Dist_ID,
2723 -1 * APAD.Amount,
2724 -1 * APAD.Base_Amt_At_Prepay_XRate,
2725 -1 * APAD.Base_Amt_At_Prepay_Pay_XRate,
2726 -1 * APAD.Base_Amt_At_Prepay_Clr_XRate,
2727 -1 * APAD.Base_Amount,
2728 APAD.AWT_Related_ID,
2729 'N',
2730 APAD.Quantity_Variance,
2731 APAD.Invoice_Base_Qty_Variance,
2732 APAD.Amount_Variance,
2733 APAD.Invoice_Base_Amt_Variance,
2734 FND_GLOBAL.User_ID,
2735 SYSDATE,
2736 SYSDATE,
2737 FND_GLOBAL.User_ID,
2738 FND_GLOBAL.User_ID,
2739 FND_GLOBAL.Prog_Appl_ID,
2740 FND_GLOBAL.Conc_Program_ID,
2741 SYSDATE,
2742 FND_GLOBAL.Conc_Request_ID
2743 FROM AP_Prepay_App_Dists APAD,
2744 ap_prepay_history_all aph, --Bug 9112240
2745 ap_prepay_history_all aphr,
2746 xla_events xer
2747 WHERE apad.Prepay_App_Distribution_ID = P_Prepay_Reversal_ID
2748 AND apad.prepay_history_id = aph.prepay_history_id --Bug 9112240
2749 AND aphr.prepay_history_id = p_prepay_hist_rec.prepay_history_id
2750 AND aphr.accounting_event_id = xer.event_id(+)
2751 AND xer.application_id(+) = 200;
2752
2753 ELSIF p_inv_reversal_id IS NOT NULL THEN
2754
2755 INSERT INTO AP_Prepay_App_Dists
2756 (Prepay_App_Dist_ID,
2757 Prepay_Dist_Lookup_Code,
2758 Invoice_Distribution_ID,
2759 Prepay_App_Distribution_ID,
2760 Accounting_Event_ID,
2761 Prepay_History_ID,
2762 Prepay_Exchange_Date,
2763 Prepay_Pay_Exchange_Date,
2764 Prepay_Clr_Exchange_Date,
2765 Prepay_Exchange_Rate,
2766 Prepay_Pay_Exchange_Rate,
2767 Prepay_Clr_Exchange_Rate,
2768 Prepay_Exchange_Rate_Type,
2769 Prepay_Pay_Exchange_Rate_Type,
2770 Prepay_Clr_Exchange_Rate_Type,
2771 Reversed_Prepay_App_Dist_ID,
2772 Amount,
2773 Base_Amt_At_Prepay_XRate,
2774 Base_Amt_At_Prepay_Pay_XRate,
2775 Base_Amt_At_Prepay_Clr_XRate,
2776 Base_Amount,
2777 AWT_Related_ID,
2778 PA_Addition_Flag,
2779 Quantity_Variance,
2780 Invoice_Base_Qty_Variance,
2781 Amount_Variance,
2782 Invoice_Base_Amt_Variance,
2783 Created_By,
2784 Creation_Date,
2785 Last_Update_Date,
2786 Last_Updated_By,
2787 Last_Update_Login,
2788 Program_Application_ID,
2789 Program_ID,
2790 Program_Update_Date,
2791 Request_ID
2792 )
2793 SELECT AP_Prepay_App_Dists_S.nextval,
2794 APAD.Prepay_Dist_Lookup_Code,
2795 p_inv_dist_id, -- Bug 7134020
2796 APAD.Prepay_App_Distribution_ID,
2797 p_xla_event_rec.event_id,
2798 p_prepay_hist_rec.prepay_history_id,
2799 APAD.Prepay_Exchange_Date,
2800 APAD.Prepay_Pay_Exchange_Date,
2801 APAD.Prepay_Clr_Exchange_Date,
2802 APAD.Prepay_Exchange_Rate,
2803 APAD.Prepay_Pay_Exchange_Rate,
2804 APAD.Prepay_Clr_Exchange_Rate,
2805 APAD.Prepay_Exchange_Rate_Type,
2806 APAD.Prepay_Pay_Exchange_Rate_Type,
2807 APAD.Prepay_Clr_Exchange_Rate_Type,
2808 APAD.Prepay_App_Dist_ID,
2809 -1 * APAD.Amount,
2810 -1 * APAD.Base_Amt_At_Prepay_XRate,
2811 -1 * APAD.Base_Amt_At_Prepay_Pay_XRate,
2812 -1 * APAD.Base_Amt_At_Prepay_Clr_XRate,
2813 -1 * APAD.Base_Amount,
2814 APAD.AWT_Related_ID,
2815 'N',
2816 APAD.Quantity_Variance,
2817 APAD.Invoice_Base_Qty_Variance,
2818 APAD.Amount_Variance,
2819 APAD.Invoice_Base_Amt_Variance,
2820 FND_GLOBAL.User_ID,
2821 SYSDATE,
2822 SYSDATE,
2823 FND_GLOBAL.User_ID,
2824 FND_GLOBAL.User_ID,
2825 FND_GLOBAL.Prog_Appl_ID,
2826 FND_GLOBAL.Conc_Program_ID,
2827 SYSDATE,
2828 FND_GLOBAL.Conc_Request_ID
2829 FROM AP_Prepay_App_Dists APAD,
2830 ap_prepay_history_all aph --Bug 9112240
2831 WHERE apad.prepay_history_id = aph.prepay_history_id --Bug 9112240
2832 AND APAD.Prepay_App_Distribution_ID = nvl(p_prepay_inv_dist_id,APAD.Prepay_App_Distribution_ID) --7686421
2833 AND APAD.Invoice_Distribution_Id = p_inv_reversal_id --bug9440073
2834 /*AND APAD.Accounting_Event_Id = p_prepay_hist_rec.related_prepay_app_event_id; --bug9440073 */
2835 /*Bug 11872456*/
2836 AND nvl(aph.related_prepay_app_event_id, aph.accounting_event_id) = p_prepay_hist_rec.related_prepay_app_event_id;
2837
2838 END IF;
2839
2840 -- Logging Infra: Procedure level
2841 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2842 l_log_msg := 'End of procedure '|| l_procedure_name;
2843 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
2844 END IF;
2845
2846
2847 EXCEPTION
2848 WHEN OTHERS THEN
2849 IF (SQLCODE <> -20001) THEN
2850 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2851 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2852 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2853 END IF;
2854 APP_EXCEPTION.RAISE_EXCEPTION;
2855
2856 END Prepay_Dist_Reverse;
2857
2858
2859
2860 ----------------------------------------------------------------------------------
2861 -- PROCEDURE Prepay_Dist_Insert
2862 -- This procedure is used to insert the prepay application payment distributions
2863 -- into the ap_prepay_app_dists table
2864 ----------------------------------------------------------------------------------
2865
2866 PROCEDURE Prepay_Dist_Insert
2867 (P_PAD_Rec IN AP_PREPAY_APP_DISTS%ROWTYPE
2868 ,P_Calling_Sequence IN VARCHAR2
2869 ) IS
2870
2871 l_curr_calling_sequence VARCHAR2(2000);
2872
2873 -- Logging Infra:
2874 l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Dist_Insert';
2875 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2876
2877 BEGIN
2878
2879 l_curr_calling_sequence := 'AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Insert<- ' ||
2880 P_Calling_Sequence;
2881
2882 -- Logging Infra: Procedure level
2883 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2884 l_log_msg := 'Begin of procedure '|| l_procedure_name;
2885 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2886 END IF;
2887
2888
2889 INSERT INTO AP_Prepay_App_Dists
2890 (Prepay_App_Dist_ID,
2891 Prepay_Dist_Lookup_Code,
2892 Invoice_Distribution_ID,
2893 Prepay_App_Distribution_ID,
2894 Accounting_Event_ID,
2895 Prepay_History_ID,
2896 Prepay_Exchange_Date,
2897 Prepay_Pay_Exchange_Date,
2898 Prepay_Clr_Exchange_Date,
2899 Prepay_Exchange_Rate,
2900 Prepay_Pay_Exchange_Rate,
2901 Prepay_Clr_Exchange_Rate,
2902 Prepay_Exchange_Rate_Type,
2903 Prepay_Pay_Exchange_Rate_Type,
2904 Prepay_Clr_Exchange_Rate_Type,
2905 Reversed_Prepay_App_Dist_ID,
2906 Amount,
2907 Base_Amt_At_Prepay_XRate,
2908 Base_Amt_At_Prepay_Pay_XRate,
2909 Base_Amt_At_Prepay_Clr_XRate,
2910 Base_Amount,
2911 AWT_Related_ID,
2912 PA_Addition_Flag,
2913 Quantity_Variance,
2914 Invoice_Base_Qty_Variance,
2915 Amount_Variance,
2916 Invoice_Base_Amt_Variance,
2917 Created_By,
2918 Creation_Date,
2919 Last_Update_Date,
2920 Last_Updated_By,
2921 Last_Update_Login,
2922 Program_Application_ID,
2923 Program_ID,
2924 Program_Update_Date,
2925 Request_ID
2926 )
2927 VALUES (AP_Prepay_App_Dists_S.nextval,
2928 P_PAD_Rec.Prepay_Dist_Lookup_Code,
2929 P_PAD_Rec.Invoice_Distribution_ID,
2930 P_PAD_Rec.Prepay_App_Distribution_ID,
2931 P_PAD_Rec.Accounting_Event_ID,
2932 P_PAD_Rec.Prepay_History_ID,
2933 P_PAD_Rec.Prepay_Exchange_Date,
2934 P_PAD_Rec.Prepay_Pay_Exchange_Date,
2935 P_PAD_Rec.Prepay_Clr_Exchange_Date,
2936 P_PAD_Rec.Prepay_Exchange_Rate,
2937 P_PAD_Rec.Prepay_Pay_Exchange_Rate,
2938 P_PAD_Rec.Prepay_Clr_Exchange_Rate,
2939 P_PAD_Rec.Prepay_Exchange_Rate_Type,
2940 P_PAD_Rec.Prepay_Pay_Exchange_Rate_Type,
2941 P_PAD_Rec.Prepay_Clr_Exchange_Rate_Type,
2942 P_PAD_Rec.Reversed_Prepay_App_Dist_ID,
2943 P_PAD_Rec.Amount,
2944 P_PAD_Rec.Base_Amt_At_Prepay_XRate,
2945 P_PAD_Rec.Base_Amt_At_Prepay_Pay_XRate,
2946 P_PAD_Rec.Base_Amt_At_Prepay_Clr_XRate,
2947 P_PAD_Rec.Base_Amount,
2948 P_PAD_Rec.AWT_Related_ID,
2949 'N',
2950 P_PAD_Rec.Quantity_Variance,
2951 P_PAD_Rec.Invoice_Base_Qty_Variance,
2952 P_PAD_Rec.Amount_Variance,
2953 P_PAD_Rec.Invoice_Base_Amt_Variance,
2954 FND_GLOBAL.User_ID,
2955 SYSDATE,
2956 SYSDATE,
2957 FND_GLOBAL.User_ID,
2958 FND_GLOBAL.User_ID,
2959 FND_GLOBAL.Prog_Appl_ID,
2960 FND_GLOBAL.Conc_Program_ID,
2961 SYSDATE,
2962 FND_GLOBAL.Conc_Request_ID
2963 );
2964
2965 -- Logging Infra: Procedure level
2966 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2967 l_log_msg := 'End of procedure '|| l_procedure_name;
2968 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
2969 END IF;
2970
2971
2972 EXCEPTION
2973 WHEN OTHERS THEN
2974 IF (SQLCODE <> -20001) THEN
2975 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2976 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2977 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2978 END IF;
2979 APP_EXCEPTION.RAISE_EXCEPTION;
2980
2981 END Prepay_Dist_Insert;
2982
2983 --Bug5373620 Added following procedure
2984 -------------------------------------------------------------------------------
2985 -- PROCEDURE Delete_Hist_Dists
2986 -- Procedure to delete the Prepay history distributions and prepayment
2987 -- application distributions.
2988 --
2989 --
2990 -- bug9038462, rewrote the DELETE statements in the procedure to make
2991 -- sure of the regeneration of the Prepayment Application distributions
2992 -- if the corresponding Invoice distribution for prepayment application
2993 -- has not been posted or encumbered
2994 --
2995
2996 --------------------------------------------------------------------------------
2997 PROCEDURE Delete_Hist_Dists
2998 (P_invoice_id IN NUMBER,
2999 P_Calling_Sequence IN VARCHAR2
3000 ) IS
3001
3002 l_curr_calling_sequence VARCHAR2(2000);
3003
3004 -- Logging Infra:
3005 l_procedure_name CONSTANT VARCHAR2(30) := 'Delete_Hist_Dists';
3006 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3007
3008 BEGIN
3009
3010 l_curr_calling_sequence := 'AP_Acctg_Prepay_Dist_Pkg.Delete_hist_dists<- ' ||
3011 p_calling_sequence;
3012
3013 -- Logging Infra: Procedure level
3014 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3015 l_log_msg := 'Begin of procedure '|| l_procedure_name;
3016 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
3017 END IF;
3018
3019 -- Bug fix 5634515
3020 -- rewrite the query to delete the correct prepay application dist record.
3021
3022 -- delete from AP_Prepay_history_all is placed after delete from AP_Prepay_App_Dists
3023 -- due to bug 7264479
3024
3025 -- Bug fix 5634515
3026 -- rewrite the query to delete the correct prepay history record.
3027
3028 -- Logging Infra: Procedure level
3029 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3030 l_log_msg := 'Begin of procedure '|| l_procedure_name;
3031 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
3032 END IF;
3033
3034 -- bug9038462, the previous bug tags have been retained, but for the sake
3035 -- of code cleanliness, I am removing the old sqls used for deletions. Please
3036 -- refer to the prior versions if changes have to be compared
3037 --
3038 DELETE FROM ap_prepay_app_dists apad1
3039 WHERE apad1.prepay_history_id IN
3040 (SELECT apph.prepay_history_id
3041 FROM ap_prepay_history_all apph
3042 WHERE nvl(apph.posted_flag, 'N') <> 'Y'
3043 AND apph.invoice_id = p_invoice_id
3044 AND apph.transaction_type <> 'PREPAYMENT APPLICATION ADJ' --bug9973070
3045 AND NOT EXISTS
3046 (SELECT /*+ no_unnest */ 1 --bug12337556
3047 FROM ap_prepay_app_dists apad,
3048 ap_invoice_distributions_all aid
3049 WHERE apad.prepay_history_id = apph.prepay_history_id
3050 AND apad.prepay_app_distribution_id = aid.invoice_distribution_id
3051 AND (aid.posted_flag = 'Y' OR aid.encumbered_flag = 'Y')));
3052
3053 DELETE FROM ap_prepay_history_all apph1
3054 WHERE apph1.prepay_history_id IN
3055 (SELECT apph.prepay_history_id
3056 FROM ap_prepay_history_all apph
3057 WHERE nvl(apph.posted_flag, 'N') <> 'Y'
3058 AND apph.invoice_id = p_invoice_id
3059 AND apph.transaction_type <> 'PREPAYMENT APPLICATION ADJ' --bug9973070
3060 AND NOT EXISTS
3061 (SELECT /*+ no_unnest */ 1 --bug12337556
3062 FROM ap_prepay_app_dists apad,
3063 ap_invoice_distributions_all aid
3064 WHERE apad.prepay_history_id = apph.prepay_history_id
3065 AND apad.prepay_app_distribution_id = aid.invoice_distribution_id
3066 AND (aid.posted_flag = 'Y' OR aid.encumbered_flag = 'Y')));
3067
3068
3069 EXCEPTION
3070
3071 WHEN OTHERS THEN
3072 IF (SQLCODE <> -20001) THEN
3073 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3074 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3075 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
3076 END IF;
3077 APP_EXCEPTION.RAISE_EXCEPTION;
3078
3079 END Delete_Hist_Dists;
3080
3081
3082
3083 -- 9322009, added the following procedure to recreate the incorrect dist
3084 -- links for the upgraded prepayment application events, so the the
3085 -- prepayment unapplication for the same created in R12 can get successfully
3086 -- accounted
3087 --
3088 PROCEDURE Upg_Dist_Links_Insert
3089 (P_Invoice_ID IN NUMBER
3090 ,p_prepay_history_id IN NUMBER
3091 ,p_accounting_event_id IN NUMBER
3092 ,p_calling_sequence IN VARCHAR2
3093 ) IS
3094
3095 l_rowcount NUMBER;
3096 l_curr_calling_sequence VARCHAR2(2000);
3097
3098 -- Logging Infra:
3099 l_procedure_name CONSTANT VARCHAR2(30) := 'Upg_Dist_Links_Insert';
3100 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3101
3102
3103 BEGIN
3104
3105 l_curr_calling_sequence := 'AP_ACCTG_PREPAY_DIST_PKG.Upg_Dist_Links_Insert<- ' ||
3106 P_Calling_Sequence;
3107
3108
3109 -- Logging Infra: Procedure level
3110 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3111 l_log_msg := 'Begin of procedure '|| l_procedure_name;
3112 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||
3113 '.begin', l_log_msg);
3114 END IF;
3115
3116 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3117 l_log_msg := 'Deleting xla_distribution_links';
3118 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3119 END IF;
3120
3121 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3122 l_log_msg := ' Printing the details of the parameters '||
3123 ' P_Invoice_ID : '||P_Invoice_ID||
3124 ' P_Prepay_History_ID : '||P_Prepay_History_ID||
3125 ' P_Accounting_Event_ID : '||P_Accounting_Event_ID;
3126 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3127 END IF;
3128
3129 DELETE FROM xla_distribution_links
3130 WHERE application_id = 200
3131 AND ae_header_id IN
3132 (SELECT ae_header_id
3133 FROM xla_ae_headers aeh,
3134 ap_prepay_history_all aph
3135 WHERE aeh.event_id = aph.accounting_event_id
3136 AND aph.accounting_event_id = p_accounting_event_id
3137 AND aph.invoice_id = p_invoice_id
3138 AND aph.historical_flag = 'Y'
3139 AND aeh.upg_batch_id IS NOT NULL
3140 AND aeh.upg_batch_id <> -9999)
3141 AND upg_batch_id IS NOT NULL
3142 AND upg_batch_id <> -9999;
3143
3144 l_rowcount := SQL%ROWCOUNT;
3145
3146 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3147 l_log_msg := ' Number of XLA distribution LInks Deleted :'||l_rowcount||
3148 ' Now Inserting xla_distribution_links for event '||
3149 p_accounting_event_id;
3150 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3151 END IF;
3152
3153
3154 INSERT INTO XLA_Distribution_Links t1
3155 (APPLICATION_ID,
3156 EVENT_ID,
3157 AE_HEADER_ID,
3158 AE_LINE_NUM,
3159 SOURCE_DISTRIBUTION_TYPE,
3160 SOURCE_DISTRIBUTION_ID_NUM_1,
3161 STATISTICAL_AMOUNT,
3162 UNROUNDED_ENTERED_CR,
3163 UNROUNDED_ENTERED_DR,
3164 UNROUNDED_ACCOUNTED_CR,
3165 UNROUNDED_ACCOUNTED_DR,
3166 REF_AE_HEADER_ID,
3167 ACCOUNTING_LINE_CODE,
3168 ACCOUNTING_LINE_TYPE_CODE,
3169 MERGE_DUPLICATE_CODE,
3170 TEMP_LINE_NUM,
3171 REF_EVENT_ID,
3172 UPG_BATCH_ID,
3173 LINE_DEFINITION_OWNER_CODE,
3174 LINE_DEFINITION_CODE,
3175 EVENT_CLASS_CODE,
3176 EVENT_TYPE_CODE,
3177 APPLIED_TO_APPLICATION_ID,
3178 APPLIED_TO_ENTITY_ID,
3179 APPLIED_TO_DIST_ID_NUM_1,
3180 GAIN_OR_LOSS_REF )
3181 SELECT Application_ID,
3182 Accounting_Event_ID,
3183 AE_Header_ID,
3184 AE_Line_Num,
3185 Source_Distribution_Type,
3186 Source_Distribution_ID_Num_1,
3187 NULL Statistical_Amount,
3188 (CASE
3189 WHEN Line_Entered_Cr IS NOT NULL THEN
3190 Decode(Rank_Num, Dist_Count, (Line_Entered_Amt - Sum_Entered_Amt) +
3191 Entered_Amt, Entered_Amt)
3192 ELSE NULL
3193 END),
3194 (CASE
3195 WHEN Line_Entered_Dr IS NOT NULL THEN
3196 Decode(Rank_Num, Dist_Count, (Line_Entered_Amt - Sum_Entered_Amt) +
3197 Entered_Amt, Entered_Amt)
3198 ELSE NULL
3199 END),
3200 (CASE
3201 WHEN Line_Accounted_Cr IS NOT NULL THEN
3202 Decode(Rank_Num, Dist_Count, (Line_Accounted_Amt - Sum_Accounted_Amt) +
3203 Accounted_Amt, Accounted_Amt)
3204 ELSE NULL
3205 END),
3206 (CASE
3207 WHEN Line_Accounted_Dr IS NOT NULL THEN
3208 Decode(Rank_Num, Dist_Count, (Line_Accounted_Amt - Sum_Accounted_Amt) +
3209 Accounted_Amt, Accounted_Amt)
3210 ELSE NULL
3211 END),
3212 Ref_AE_Header_ID,
3213 Accounting_Line_Code,
3214 Accounting_Line_Type_Code,
3215 Merge_Duplicate_Code,
3216 Temp_Line_Num,
3217 Ref_Event_ID,
3218 Upg_Batch_ID,
3219 Line_Definition_Owner_Code,
3220 Line_Definition_Code,
3221 Event_Class_Code,
3222 Event_Type_Code,
3223 APPLIED_TO_APPLICATION_ID,
3224 APPLIED_TO_ENTITY_ID,
3225 APPLIED_TO_DIST_ID_NUM_1,
3226 GAIN_OR_LOSS_REF
3227 FROM
3228 (SELECT Application_ID,
3229 Accounting_Event_ID,
3230 AE_Header_ID,
3231 AE_Line_Num,
3232 Source_Distribution_Type,
3233 Source_Distribution_ID_Num_1,
3234 Statistical_Amount,
3235 Accounting_Line_Code,
3236 Accounting_Line_Type_Code,
3237 Merge_Duplicate_Code,
3238 Line_Entered_Cr,
3239 Line_Entered_Dr,
3240 Line_Accounted_Cr,
3241 Line_Accounted_Dr,
3242 Line_Entered_Amt,
3243 Line_Accounted_Amt,
3244 Dist_Count,
3245 Ref_AE_Header_ID,
3246 Temp_Line_Num,
3247 Ref_Event_ID,
3248 Upg_Batch_ID,
3249 Line_Definition_Owner_Code,
3250 Line_Definition_Code,
3251 Event_Class_Code,
3252 Event_Type_Code,
3253 APPLIED_TO_APPLICATION_ID,
3254 APPLIED_TO_ENTITY_ID,
3255 APPLIED_TO_DIST_ID_NUM_1,
3256 GAIN_OR_LOSS_REF,
3257 Rank_Num,
3258 DECODE(FC.Minimum_Accountable_Unit, NULL,
3259 ROUND((Line_Accounted_Amt * Dist_Base_Amount
3260 /DECODE(PDivisor_Acct_Amt, 0, 1, PDivisor_Acct_Amt)),
3261 FC.Precision),
3262 ROUND((Line_Accounted_Amt * Dist_Base_Amount
3263 / DECODE(PDivisor_Acct_Amt, 0, 1, PDivisor_Acct_Amt))
3264 /FC.Minimum_Accountable_Unit) * FC.Minimum_Accountable_Unit) Accounted_Amt,
3265 DECODE(FC.Minimum_Accountable_Unit, NULL,
3266 ROUND((Line_Entered_Amt * Dist_Amount
3267 / DECODE(PDivisor_Ent_Amt, 0 ,1, PDivisor_Ent_Amt)), FC.Precision),
3268 ROUND((Line_Entered_Amt * Dist_Amount
3269 / DECODE(PDivisor_Acct_Amt, 0 ,1, PDivisor_Ent_Amt))
3270 /FC.Minimum_Accountable_Unit) * FC.Minimum_Accountable_Unit) Entered_Amt,
3271 SUM(DECODE(FC.Minimum_Accountable_Unit, NULL,
3272 ROUND((Line_Accounted_Amt * Dist_Base_Amount
3273 / DECODE(PDivisor_Acct_Amt, 0, 1, PDivisor_Acct_Amt)),
3274 FC.Precision),
3275 ROUND((Line_Accounted_Amt * Dist_Base_Amount
3276 / DECODE(PDivisor_Acct_Amt, 0, 1, PDivisor_Acct_Amt))
3277 /FC.Minimum_Accountable_Unit) * FC.Minimum_Accountable_Unit))
3278 OVER (PARTITION BY Invoice_Id, Part_Key1, Part_Key2, AE_Line_Num)
3279 Sum_Accounted_Amt,
3280 SUM(DECODE(FC.Minimum_Accountable_Unit, NULL,
3281 ROUND((Line_Entered_Amt * Dist_Amount
3282 / DECODE(PDivisor_Ent_Amt, 0 ,1, PDivisor_Ent_Amt)), FC.Precision),
3283 ROUND((Line_Entered_Amt * Dist_Amount
3284 / DECODE(PDivisor_Ent_Amt, 0 ,1, PDivisor_Ent_Amt))
3285 /FC.Minimum_Accountable_Unit) * FC.Minimum_Accountable_Unit))
3286 OVER (PARTITION BY Invoice_Id, Part_Key1, Part_Key2, AE_Line_Num) Sum_Entered_Amt
3287 FROM( /*Bug 10016633 Added another wrapper query*/
3288 SELECT Application_ID,
3289 Invoice_Id,
3290 Base_Currency_Code,
3291 Accounting_Event_ID,
3292 AE_Header_ID,
3293 AE_Line_Num,
3294 Source_Distribution_Type,
3295 Source_Distribution_ID_Num_1,
3296 Statistical_Amount,
3297 Unrounded_Entered_Cr,
3298 Unrounded_Entered_Dr,
3299 Unrounded_Accounted_Cr,
3300 Unrounded_Accounted_Dr,
3301 Ref_AE_Header_ID,
3302 Accounting_Line_Code,
3303 Accounting_Line_Type_Code,
3304 Merge_Duplicate_Code,
3305 Line_Entered_Cr,
3306 Line_Entered_Dr,
3307 Line_Accounted_Cr,
3308 Line_Accounted_Dr,
3309 Line_Entered_Amt,
3310 Line_Accounted_Amt,
3311 Dist_Amount,
3312 Dist_Base_Amount,
3313 Dist_Count,
3314 PDivisor_Ent_Amt,
3315 PDivisor_Acct_Amt,
3316 Part_Key1,
3317 Part_Key2,
3318 /*Bug10016633 Moved temp_line_num logic here
3319 and using ROW_NUMBER() instead of RANK()*/
3320 ROW_NUMBER() OVER (PARTITION BY Invoice_ID,
3321 AE_Header_Id
3322 ORDER BY AE_Line_Num,
3323 Invoice_Distribution_ID,
3324 Source_Distribution_ID_Num_1,
3325 Prepay_Dist_Lookup_Code) Temp_Line_Num,
3326 Rank_Num,
3327 Ref_Event_ID,
3328 Upg_Batch_ID,
3329 Line_Definition_Owner_Code,
3330 Line_Definition_Code,
3331 Event_Class_Code,
3332 Event_Type_Code,
3333 APPLIED_TO_APPLICATION_ID,
3334 APPLIED_TO_ENTITY_ID,
3335 APPLIED_TO_DIST_ID_NUM_1,
3336 GAIN_OR_LOSS_REF
3337 FROM
3338 (
3339 SELECT 200 Application_ID,
3340 AI.Invoice_Id Invoice_Id,
3341 ASP.Base_Currency_Code Base_Currency_Code,
3342 AEH.Event_ID Accounting_Event_ID,
3343 AEH.AE_Header_ID AE_Header_ID,
3344 AEL.AE_Line_Num AE_Line_Num,
3345 'AP_PREPAY' Source_Distribution_Type,
3346 APAD.Prepay_App_Dist_ID Source_Distribution_ID_Num_1,
3347 0 Statistical_Amount,
3348 DECODE(SIGN(APAD.Amount), 1, APAD.Amount, NULL) Unrounded_Entered_Cr,
3349 DECODE(SIGN(APAD.Amount),-1, APAD.Amount, NULL) Unrounded_Entered_Dr,
3350 DECODE(SIGN(APAD.Base_Amount), 1, APAD.Base_Amount, NULL) Unrounded_Accounted_Cr,
3351 DECODE(SIGN(APAD.Base_Amount),-1, APAD.Base_Amount, NULL) Unrounded_Accounted_Dr,
3352 AEH.AE_Header_ID Ref_AE_Header_ID,
3353 DECODE(AEL.Accounting_Class_Code,
3354 'GAIN', 'AP_GAIN_PREPAY_APP',
3355 'LOSS', 'AP_LOSS_PREPAY_APP',
3356 'LIABILITY', 'AP_LIAB_PREPAY_APP',
3357 'PREPAID_EXPENSE','AP_PREPAID_EXP_ACCR_PREPAY_APP',
3358 'ROUNDING', 'AP_FINAL_PMT_ROUND_PREPAY_APP',
3359 'NRTAX', 'AP_NRTAX_PREPAY_PAY_RATE_APP',
3360 'RTAX', 'AP_RECOV_PREPAY_PAY_RATE_APP',
3361 'ACCRUAL', 'AP_ACCR_PREPAY_PAY_RATE_APP',
3362 'ITEM EXPENSE', 'AP_ITEM_PREPAY_PAY_RATE_APP',
3363 'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_PREPAY_PAY_RATE',
3364 'IPV', 'AP_IPV_PREPAY_PAY_RATE_APP',
3365 'NRTAX', 'AP_NRTAX_PREPAY_PAY_RATE_APP',
3366 'RTAX', 'AP_RECOV_PREPAY_PAY_RATE_APP',
3367 'FREIGHT', 'AP_FREIGHT_PREPAY_PAY_RATE_APP',
3368 'AP_ITEM_PREPAY_PAY_RATE_APP')
3369 Accounting_Line_Code,
3370 'S' Accounting_Line_Type_Code,
3371 'A' Merge_Duplicate_Code,
3372 AEL.Entered_Cr Line_Entered_Cr,
3373 AEL.Entered_Dr Line_Entered_Dr,
3374 AEL.Accounted_Cr Line_Accounted_Cr,
3375 AEL.Accounted_Dr Line_Accounted_Dr,
3376 NVL(AEL.Entered_Cr, AEL.Entered_Dr) Line_Entered_Amt,
3377 NVL(AEL.Accounted_Cr, AEL.Accounted_Dr) Line_Accounted_Amt,
3378 AID.Amount Dist_Amount,
3379 NVL(AID.Base_Amount, AID.Amount) Dist_Base_Amount,
3380 COUNT(*) OVER (PARTITION BY AI.Invoice_ID,
3381 AEH.AE_Header_Id,
3382 AEL.AE_Line_Num) Dist_Count,
3383 /* bug 12845564 - start */
3384 SUM(AID.Amount)
3385 OVER (PARTITION BY AI.Invoice_ID,
3386 AEH.ae_header_id,
3387 AEL.AE_Line_Num,
3388 AEL.Account_Overlay_Source_ID) PDivisor_Ent_Amt,
3389 SUM(NVL(AID.Base_Amount, AID.Amount))
3390 OVER (PARTITION BY AI.Invoice_ID,
3391 AEH.ae_header_id,
3392 AEL.AE_Line_Num,
3393 AEL.Account_Overlay_Source_ID) PDivisor_Acct_Amt,
3394 /* bug 12845564 - end */
3395 AI.Invoice_ID Part_Key1,
3396 NVL(AID.old_distribution_id, AID.Invoice_Distribution_ID) Part_Key2, -- bug 12845564
3397 RANK() OVER (PARTITION BY AI.Invoice_ID,
3398 AEH.AE_Header_Id,
3399 AEL.AE_Line_Num
3400 ORDER BY AEL.AE_Line_Num,
3401 APAD.Invoice_Distribution_ID,
3402 APAD.Prepay_App_Distribution_ID,
3403 APAD.Prepay_Dist_Lookup_Code) Rank_Num,
3404 AEH.Event_ID Ref_Event_ID,
3405 AEL.Upg_Batch_ID,
3406 'S' Line_Definition_Owner_Code,
3407 'ACCRUAL_INVOICES_ALL' Line_Definition_Code,
3408 'INVOICES' Event_Class_Code,
3409 'INVOICES_ALL' Event_Type_Code,
3410 DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,200, null) APPLIED_TO_APPLICATION_ID,
3411 DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,XTE.Entity_ID, null) APPLIED_TO_ENTITY_ID,
3412 DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,AID.Invoice_Distribution_ID, null) APPLIED_TO_DIST_ID_NUM_1,
3413 '-2222' GAIN_OR_LOSS_REF,
3414 APAD.Invoice_Distribution_ID,
3415 APAD.Prepay_Dist_Lookup_Code
3416 FROM AP_Invoices_All AI,
3417 AP_System_Parameters_All ASP,
3418 XLA_Transaction_Entities_upg XTE,
3419 XLA_Events XLE,
3420 AP_Prepay_App_Dists APAD,
3421 AP_Invoice_Distributions_All AID,
3422 XLA_AE_Headers AEH,
3423 XLA_AE_Lines AEL
3424 WHERE XLE.event_id = p_accounting_event_id
3425 AND AI.Org_Id = ASP.Org_Id
3426 AND AI.Invoice_ID = AID.Invoice_ID
3427 AND XTE.Application_ID = 200
3428 AND AI.Set_Of_Books_ID = XTE.Ledger_ID
3429 AND XTE.Entity_Code = 'AP_INVOICES'
3430 AND AI.Invoice_ID = NVL(XTE.Source_ID_Int_1,-99)
3431 AND XTE.Entity_ID = XLE.Entity_ID
3432 AND XLE.Application_ID = 200
3433 AND XLE.Event_Type_Code IN ('PREPAYMENT APPLIED',
3434 'PREPAYMENT UNAPPLIED')
3435 AND XLE.Event_ID = AEH.Event_ID
3436 AND AEH.Application_ID = 200
3437 AND AEL.AE_Header_ID = AEH.AE_Header_ID
3438 AND AEL.Application_ID = 200
3439 AND XLE.Event_ID = APAD.Accounting_Event_ID
3440 AND APAD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
3441 AND AEL.Account_Overlay_Source_ID IS NOT NULL
3442 AND AID.Old_Distribution_ID = AEL.Account_Overlay_Source_ID
3443 UNION
3444 SELECT 200 Application_ID,
3445 AI.Invoice_id Invoice_Id,
3446 ASP.Base_Currency_Code Base_Currency_Code,
3447 AEH.Event_ID Accounting_Event_ID,
3448 AEH.AE_Header_ID AE_Header_ID,
3449 AEL.AE_Line_Num AE_Line_Num,
3450 'AP_PREPAY' Source_Distribution_Type,
3451 APAD.Prepay_App_Dist_ID Source_Distribution_ID_Num_1,
3452 0 Statistical_Amount,
3453 DECODE(SIGN(APAD.Amount), 1, APAD.Amount, NULL) Unrounded_Entered_Cr,
3454 DECODE(SIGN(APAD.Amount),-1, APAD.Amount, NULL) Unrounded_Entered_Dr,
3455 DECODE(SIGN(APAD.Base_Amount), 1, APAD.Base_Amount, NULL) Unrounded_Accounted_Cr,
3456 DECODE(SIGN(APAD.Base_Amount),-1, APAD.Base_Amount, NULL) Unrounded_Accounted_Dr,
3457 AEH.AE_Header_ID Ref_AE_Header_ID,
3458 DECODE(AEL.Accounting_Class_Code,
3459 'GAIN', 'AP_GAIN_PREPAY_APP',
3460 'LOSS', 'AP_LOSS_PREPAY_APP',
3461 'LIABILITY', 'AP_LIAB_PREPAY_APP',
3462 'PREPAID_EXPENSE','AP_PREPAID_EXP_ACCR_PREPAY_APP',
3463 'ROUNDING', 'AP_FINAL_PMT_ROUND_PREPAY_APP',
3464 'NRTAX', 'AP_NRTAX_PREPAY_PAY_RATE_APP',
3465 'RTAX', 'AP_RECOV_PREPAY_PAY_RATE_APP',
3466 'ACCRUAL', 'AP_ACCR_PREPAY_PAY_RATE_APP',
3467 'ITEM EXPENSE', 'AP_ITEM_PREPAY_PAY_RATE_APP',
3468 'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_PREPAY_PAY_RATE',
3469 'IPV', 'AP_IPV_PREPAY_PAY_RATE_APP',
3470 'NRTAX', 'AP_NRTAX_PREPAY_PAY_RATE_APP',
3471 'RTAX', 'AP_RECOV_PREPAY_PAY_RATE_APP',
3472 'FREIGHT', 'AP_FREIGHT_PREPAY_PAY_RATE_APP',
3473 'AP_ITEM_PREPAY_PAY_RATE_APP')
3474 Accounting_Line_Code,
3475 'S' Accounting_Line_Type_Code,
3476 'A' Merge_Duplicate_Code,
3477 AEL.Entered_Cr Line_Entered_Cr,
3478 AEL.Entered_Dr Line_Entered_Dr,
3479 AEL.Accounted_Cr Line_Accounted_Cr,
3480 AEL.Accounted_Dr Line_Accounted_Dr,
3481 NVL(AEL.Entered_Cr, AEL.Entered_Dr) Line_Entered_Amt,
3482 NVL(AEL.Accounted_Cr, AEL.Accounted_Dr) Line_Accounted_Amt,
3483 AID.Amount Dist_Amount,
3484 NVL(AID.Base_Amount, AID.Amount) Dist_Base_Amount,
3485 COUNT(*) OVER (PARTITION BY AI.Invoice_ID,
3486 AEH.AE_Header_Id,
3487 AEL.AE_Line_Num) Dist_Count,
3488 SUM(AID.Amount)
3489 OVER (PARTITION BY AI.Invoice_ID,
3490 AEH.ae_header_id,
3491 AEL.AE_Line_Num) PDivisor_Ent_Amt,
3492 SUM(NVL(AID.Base_Amount, AID.Amount))
3493 OVER (PARTITION BY AI.Invoice_ID,
3494 AEH.ae_header_id,
3495 AEL.AE_Line_Num) PDivisor_Acct_Amt,
3496 AI.Invoice_ID Part_Key1,
3497 1 Part_Key2,
3498 RANK() OVER (PARTITION BY AI.Invoice_ID,
3499 AEH.AE_Header_Id,
3500 AEL.AE_Line_Num
3501 ORDER BY AEL.AE_Line_Num,
3502 APAD.Invoice_Distribution_ID,
3503 APAD.Prepay_App_Distribution_ID,
3504 APAD.Prepay_Dist_Lookup_Code) Rank_Num,
3505 AEH.Event_ID Ref_Event_ID,
3506 AEL.Upg_Batch_ID,
3507 'S' Line_Definition_Owner_Code,
3508 'ACCRUAL_INVOICES_ALL' Line_Definition_Code,
3509 'INVOICES' Event_Class_Code,
3510 'INVOICES_ALL' Event_Type_Code,
3511 DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,200, null) APPLIED_TO_APPLICATION_ID,
3512 DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,XTE.Entity_ID, null) APPLIED_TO_ENTITY_ID,
3513 DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,AID.Invoice_Distribution_ID, null)
3514 APPLIED_TO_DIST_ID_NUM_1,
3515 '-2222' GAIN_OR_LOSS_REF,
3516 APAD.Invoice_Distribution_ID,
3517 APAD.Prepay_Dist_Lookup_Code
3518 FROM AP_Invoices_All AI,
3519 AP_System_Parameters_All ASP,
3520 XLA_Transaction_Entities_upg XTE,
3521 XLA_Events XLE,
3522 AP_Prepay_App_Dists APAD,
3523 AP_Invoice_Distributions_All AID,
3524 XLA_AE_Headers AEH,
3525 XLA_AE_Lines AEL
3526 WHERE XLE.event_id = p_accounting_event_id
3527 AND AI.Org_Id = ASP.Org_id
3528 AND AI.Invoice_ID = AID.Invoice_ID
3529 AND XTE.Application_ID = 200
3530 AND AI.Set_Of_Books_ID = XTE.Ledger_ID
3531 AND XTE.Entity_Code = 'AP_INVOICES'
3532 AND AI.Invoice_ID = NVL(XTE.Source_ID_Int_1,-99)
3533 AND XTE.Entity_ID = XLE.Entity_ID
3534 AND XLE.Application_ID = 200
3535 AND XLE.Event_Type_Code IN ('PREPAYMENT APPLIED',
3536 'PREPAYMENT UNAPPLIED')
3537 AND XLE.Event_ID = AEH.Event_ID
3538 AND AEH.Application_ID = 200
3539 AND AEL.AE_Header_ID = AEH.AE_Header_ID
3540 AND AEL.Application_ID = 200
3541 AND XLE.Event_ID = APAD.Accounting_Event_ID
3542 AND APAD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
3543 AND AEL.Account_Overlay_Source_ID IS NULL)) v1,
3544 Fnd_Currencies FC
3545 WHERE FC.Currency_Code = v1.Base_Currency_Code) v2;
3546
3547 l_rowcount := SQL%ROWCOUNT;
3548
3549 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3550 l_log_msg := 'Number of records Inserted in XDL '||l_rowcount;
3551 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3552 END IF;
3553
3554 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3555 l_log_msg := 'End of procedure '||l_procedure_name;
3556 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end',l_log_msg);
3557 END IF;
3558
3559
3560 EXCEPTION
3561 WHEN OTHERS THEN
3562 IF (SQLCODE <> -20001) THEN
3563 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3564 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3565 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
3566 END IF;
3567 APP_EXCEPTION.RAISE_EXCEPTION;
3568
3569 END Upg_Dist_Links_Insert;
3570
3571 END AP_ACCTG_PREPAY_DIST_PKG;