1 PACKAGE BODY AP_ACCOUNTING_PAY_PKG AS
2 /* $Header: apacpayb.pls 120.47.12020000.3 2012/10/04 06:34:19 rseeta ship $ */
3
4 -- Logging Infra
5 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'AP.PLSQL.AP_ACCOUNTING_PAY_PKG.';
13 -- Logging Infra
14
18 -- Dists Generator for creating Payment and Prepay Appl dists. Single point of
15 -------------------------------------------------------------------------------
16 -- PROCEDURE Do_Pay_Accounting
17 -- Selects Payment Events for processing. Calls the Payment Dists and Prepay Appl
19 -- entry for Payment processing.
20 --
21 --------------------------------------------------------------------------------
22 PROCEDURE Do_Pay_Accounting
23 (P_Calling_Sequence IN VARCHAR2
24 ) IS
25
26 l_xla_event_rec r_xla_event_info;
27 l_curr_calling_sequence VARCHAR2(2000);
28 l_check_curr_code ap_checks_all.currency_code%type; --8288996
29 l_budgetary_control_flag VARCHAR2(1);
30 l_exc_data_mismatch EXCEPTION; --bug 9936620
31
32 -- bug9716573, added budgetary control flag to the cursor
33 -- bug11772495, removed budgetary control flag from the cursor
34 -- bug10412623, added entity_id, ledger_id to the cursor
35 -- bug12918263, removed entity_id, ledger_id to the cursor
36
37 CURSOR xla_events_cur IS
38 SELECT Event_ID,
39 Event_Type_Code,
40 Event_Date,
41 Event_Number,
42 Event_Status_Code,
43 Entity_Code,
44 Source_ID_Int_1
45 FROM XLA_Events_GT
46 WHERE (Entity_Code = 'AP_PAYMENTS'
47 OR Event_Type_Code IN ('PREPAYMENT APPLIED',
48 'PREPAYMENT UNAPPLIED',
49 'PREPAYMENT APPLICATION ADJ'))
50 AND Event_Status_Code <> 'N'
51 ORDER BY Entity_id, --Bug 9784405
52 Event_Number; --Bug 9784405
53
54 -- Logging Infra:
55 l_ledger_id NUMBER;
56 l_entity_id NUMBER;
57 l_procedure_name CONSTANT VARCHAR2(30) := 'Do_Pay_Accounting';
58 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
59
60 BEGIN
61
62 l_curr_calling_sequence := 'AP_Accounting_Pay_Pkg.Do_Pay_Accounting<- ' ||
63 p_calling_sequence;
64
65 -- Logging Infra: Setting up runtime level
66 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
67
68 -- Logging Infra: Procedure level
69 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
70 l_log_msg := 'Begin of procedure '|| l_procedure_name;
71 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
72 END IF;
73
74
75 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
76 l_log_msg := 'Calling procedure Prorate_Historical_Dists to create '||
77 'Prepay App dists for the historical non Accounted Dists';
78 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
79 END IF;
80
81 Prorate_Historical_Dists(l_curr_calling_sequence);
82
83 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
84 l_log_msg := 'Calling procedure AP_Acctg_Pay_Dist_Pkg.Primary_Pay_Events';
85 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
86 END IF;
87
88 -- We need to delete the payment hist distributions and prepay appl hist distributions
89 -- which were created during the draft mode of the accounting process
90 -------------------------------------------------------------------------------
91
92 Delete_Hist_Dists (l_curr_calling_sequence);
93
94
95 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
96 l_log_msg := 'Procedure AP_Acctg_Pay_Dist_Pkg.Primary_Pay_Events executed';
97 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
98 END IF;
99
100 OPEN xla_events_cur;
101 LOOP
102 BEGIN
103 FETCH xla_events_cur INTO l_xla_event_rec;
104 EXIT WHEN xla_events_cur%NOTFOUND OR
105 xla_events_cur%NOTFOUND IS NULL;
106
107
108 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
109 l_log_msg := 'CUR: xla_events_cur: entity_code = '|| l_xla_event_rec.entity_code
110 || ' document_id = ' || l_xla_event_rec.source_id_int_1;
111 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
112 END IF;
113
114 -- Get the base currency code into global variable
115 IF (l_xla_event_rec.entity_code = 'AP_PAYMENTS') THEN
116 BEGIN
117 SELECT ASP.Base_Currency_Code, AC.Currency_Code --8288996
118 INTO g_base_currency_code, l_check_curr_code
119 FROM AP_System_Parameters_All ASP,
120 AP_Checks_All AC
121 WHERE AC.Check_ID = l_xla_event_rec.source_id_int_1
122 AND AC.Org_ID = ASP.Org_ID;
123 EXCEPTION
124 WHEN NO_DATA_FOUND THEN
125 RAISE_APPLICATION_ERROR(-20100, 'check_id_mismatch'); --bug 9936620
126 END;
127 ELSE
128
129 SELECT ASP.Base_Currency_Code
130 INTO g_base_currency_code
131 FROM AP_System_Parameters_All ASP,
132 AP_Invoices_All AI
133 WHERE AI.Invoice_ID = l_xla_event_rec.source_id_int_1
134 AND AI.Org_ID = ASP.Org_ID;
135
136 END IF;
137
138
139 -- Based on the event type calling the appropriate event procedures
140 -- to create payment and prepayment distributions.
141 IF (l_xla_event_rec.event_type_code IN ('PAYMENT CREATED',
142 'PAYMENT MATURED',
143 'PAYMENT CLEARED',
144 'REFUND RECORDED')) THEN
145
146 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
147 l_log_msg := 'Calling procedure AP_Acctg_Pay_Dist_Pkg.Primary_Pay_Events';
148 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
149 END IF;
150
151
152 AP_Acctg_Pay_Dist_Pkg.Primary_Pay_Events
153 (l_xla_event_rec,
154 l_curr_calling_sequence);
155
156
157 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
158 l_log_msg := 'Procedure AP_Acctg_Pay_Dist_Pkg.Primary_Pay_Events executed';
159 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
160 END IF;
161
162 ELSIF l_xla_event_rec.event_type_code IN ('MANUAL PAYMENT ADJUSTED',
163 'MANUAL REFUND ADJUSTED',
164 'UPGRADED MANUAL PMT ADJUSTED') THEN
165
166 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
167 l_log_msg := 'Calling procedure AP_Acctg_Pay_Dist_Pkg.Manual_Pay_Adj_Events';
168 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
169 END IF;
170
171 AP_Acctg_Pay_Dist_Pkg.Manual_Pay_Adj_Events
172 (l_xla_event_rec,
173 l_curr_calling_sequence);
174
175 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
176 l_log_msg := 'Procedure AP_Acctg_Pay_Dist_Pkg.Manual_Pay_Adj_Events executed';
177 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
178 END IF;
179
180 ELSIF l_xla_event_rec.event_type_code IN ('PAYMENT ADJUSTED',
181 'PAYMENT MATURITY ADJUSTED',
182 'PAYMENT CLEARING ADJUSTED',
183 'REFUND ADJUSTED') THEN
184
185 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
186 l_log_msg := 'Calling procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Cascade_Adj_Events';
187 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
188 END IF;
189
190 AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Cascade_Adj_Events
191 (l_xla_event_rec,
192 l_curr_calling_sequence);
193
194 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
195 l_log_msg := 'Procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Cascade_Adj_Events executed';
196 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
197 END IF;
198
199 ELSIF l_xla_event_rec.event_type_code IN ('PAYMENT CANCELLED',
200 'PAYMENT MATURITY REVERSED',
201 'PAYMENT UNCLEARED',
202 'REFUND CANCELLED') THEN
203
204 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
205 l_log_msg := 'Calling procedure AP_Acctg_Pay_Dist_Pkg.Cancel_Primary_Pay_Events';
206 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
207 END IF;
208
209 AP_Acctg_Pay_Dist_Pkg.Cancel_Primary_Pay_Events
210 (l_xla_event_rec,
211 l_curr_calling_sequence);
212
213
214 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
215 l_log_msg := 'Procedure AP_Acctg_Pay_Dist_Pkg.Cancel_Primary_Pay_Events executed';
216 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
217 END IF;
218
219 ELSIF l_xla_event_rec.event_type_code IN ('PREPAYMENT APPLICATION ADJ') THEN
220
221 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
222 l_log_msg := 'Calling procedure AP_Acctg_Prepay_Dist_Pkg.Prepay_Dist_Cascade_Adj';
223 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
224 END IF;
225
226 AP_Acctg_Prepay_Dist_Pkg.Prepay_Dist_Cascade_Adj
227 (l_xla_event_rec,
228 l_curr_calling_sequence);
229
230 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
231 l_log_msg := 'Procedure AP_Acctg_Prepay_Dist_Pkg.Prepay_Dist_Cascade_Adj executed';
232 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
233 END IF;
234
235 -- bug9716573
236 -- added the condition of budgetary control flag to ensure that the
237 -- update of gain/loss indicator fires only for Non budgetary control
238 -- events.
239 --
240 ELSIF l_xla_event_rec.event_type_code IN ('PREPAYMENT APPLIED',
241 'PREPAYMENT UNAPPLIED') THEN
242 -- bug11772495, reverting the fix for bug9716573
243 -- adding the code below to skip executing Update_Gain_Loss_Ind
244 -- for the budgetary control events
245 --
246 BEGIN
247 SELECT nvl(xe.budgetary_control_flag, 'N') --BUG12594203
248 INTO l_budgetary_control_flag
249 FROM xla_events xe
250 WHERE xe.application_id = 200
251 AND xe.event_id = l_xla_event_rec.event_id;
252
253 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
254 l_log_msg := 'Budgetary Control flag for the Event_ID: '||l_xla_event_rec.event_id||
255 ' is: '||l_budgetary_control_flag;
256 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
257 END IF;
258 EXCEPTION
259 WHEN OTHERS THEN
260 l_budgetary_control_flag := 'N';
261 END;
262
263 IF l_budgetary_control_flag = 'N' THEN
264
265 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
266 l_log_msg := 'Calling procedure AP_Acctg_Prepay_Dist_Pkg.Update_Gain_Loss_Ind';
267 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
268 END IF;
269
270 AP_Acctg_Prepay_Dist_Pkg.Update_Gain_Loss_Ind
271 (l_xla_event_rec,
272 l_curr_calling_sequence);
273
274 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
275 l_log_msg := 'Procedure AP_Acctg_Prepay_Dist_Pkg.Updated_Gain_Loss_Ind executed';
276 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
277 END IF;
278
279 END IF;
280
281 END IF;
282
283 -- Added 8288996
284 --bug 9495694, uncommented payment_cleared from the following condition
285 IF (l_xla_event_rec.event_type_code IN ('PAYMENT CREATED', 'PAYMENT CLEARED')
286 --'PAYMENT MATURED')
287 AND g_base_currency_code <> l_check_curr_code ) THEN
288 /* Restricting the Fix only to Payment Created
289 When ever customers reported for Payment Maturity or Payment Clearing
290 then just remove the conditions 1=2. Becuase of huge JLT changes now the
291 fix is restricted to Payment Created. When ever the contions 1=2 are removed
292 please make sure that JLT's are also Handeled */
293 --Bug 8670681
294
295 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
296 l_log_msg := 'Calling procedure AP_ACCTG_PAY_ROUND_PKG.Final_Cash';
297 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
298 END IF;
299
300 AP_ACCTG_PAY_ROUND_PKG.Final_Cash
301 (l_xla_event_rec,
302 l_curr_calling_sequence);
303
304 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
305 l_log_msg := 'Procedure procedure AP_ACCTG_PAY_ROUND_PKG.Final_Cash executed';
306 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
307 END IF;
308
309 END IF; --8288996 ends
310 EXCEPTION
311 WHEN OTHERS THEN
312 IF SQLCODE = -20100 THEN
313 --10412623, inserting error into xla_accounting_errors
314 --Bug 12918263
315
316 SELECT DISTINCT xeg.entity_id
317 , xeg.ledger_id
318 INTO l_entity_id
319 , l_ledger_id
320 FROM xla_events_gt xeg
321 WHERE xeg.event_id = l_xla_event_rec.event_id
322 AND xeg.application_id = 200;
323
324 XLA_ACCOUNTING_ERR_PKG.build_message('SQLAP',
325 'AP_ACCTG_EVENT_SKIPPED',
326 l_entity_id,
327 l_xla_event_rec.event_id,
328 l_ledger_id,
329 NULL,
330 NULL,
331 xla_accounting_pkg.g_parent_request_id);
332
333 l_log_msg := 'Check_id '||l_xla_event_rec.source_id_int_1||
334 ' will not be accounted due to error: '|| SQLERRM;
335
336 fnd_file.put_line(FND_FILE.LOG, l_log_msg);
337 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
338 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
339 END IF;
340 END IF;
341 END; --end bug 9936620
342 END LOOP;
343 CLOSE xla_events_cur;
344
345 -- Logging Infra: Procedure level
346 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
347 l_log_msg := 'End of procedure '|| l_procedure_name;
348 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
349 END IF;
350
351 -- Commenting out the commit since the commit is issued during the post processing of the
352 -- accounting process
353 -- COMMIT;
354
355 EXCEPTION
356
357 WHEN OTHERS THEN
358 IF (SQLCODE <> -20001) THEN
359 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
360 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
361 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
362 END IF;
363 APP_EXCEPTION.RAISE_EXCEPTION;
364
365 END Do_Pay_Accounting;
366
367
368 -------------------------------------------------------------------------------
369 -- PROCEDURE Delete_Hist_Dists
370 -- Procedure to delete the payment history distributions and prepayment
371 -- application distributions.
372 --
373 --------------------------------------------------------------------------------
374 PROCEDURE Delete_Hist_Dists
375 (P_Calling_Sequence IN VARCHAR2
376 ) IS
377
378 l_curr_calling_sequence VARCHAR2(2000);
379
380 -- Logging Infra:
381 l_procedure_name CONSTANT VARCHAR2(30) := 'Do_Pay_Accounting';
382 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
383
384 BEGIN
385
386 l_curr_calling_sequence := 'AP_Accounting_Pay_Pkg.Do_Pay_Accounting<- ' ||
387 p_calling_sequence;
388
389 -- Logging Infra: Procedure level
390 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
391 l_log_msg := 'Begin of procedure '|| l_procedure_name;
392 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
393 END IF;
394
395
396 -- Bug 5098657. Added the where condition for both the delete statements
397 DELETE FROM AP_Payment_Hist_Dists
398 WHERE Accounting_Event_ID IN
399 (SELECT Event_ID
400 FROM XLA_Events_GT
401 WHERE Entity_Code = 'AP_PAYMENTS');
402
403 DELETE FROM AP_Prepay_App_Dists
404 WHERE Accounting_Event_ID IN
405 (SELECT Event_ID
406 FROM XLA_Events_GT
407 WHERE Event_Type_Code IN ('PREPAYMENT APPLICATION ADJ'));
408
409
410 EXCEPTION
411
412 WHEN OTHERS THEN
413 IF (SQLCODE <> -20001) THEN
414 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
415 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
416 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
417 END IF;
418 APP_EXCEPTION.RAISE_EXCEPTION;
419
420 END Delete_Hist_Dists;
421
422
423
424 -------------------------------------------------------------------------------
425 -- Function Get_Casc_Pay_Sum
426 -- This function gets the sum of the payment amount from the payment history
427 -- distributions for the given invoice distribution which will be used for
428 -- payment cascase events
429 --
430 --------------------------------------------------------------------------------
431 FUNCTION Get_Casc_Pay_Sum
432 (P_Invoice_Distribution_ID IN NUMBER
433 ,P_Related_Event_ID IN NUMBER
434 ,P_Invoice_Payment_ID IN NUMBER
435 ,P_Calling_Sequence IN VARCHAR2
436 ) RETURN NUMBER IS
437
438 l_curr_calling_sequence VARCHAR2(2000);
439 l_pay_sum NUMBER;
440
441 BEGIN
442
443 l_curr_calling_sequence := 'AP_ACCOUNTING_PAY_PKG.Get_Casc_Pay_Sum<- ' ||
444 P_Calling_Sequence;
445
446
447
448 SELECT SUM(APHD.Amount)
449 INTO l_pay_sum
450 FROM AP_Payment_Hist_Dists APHD,
451 AP_Payment_History_All APH
452 WHERE APHD.Invoice_Distribution_ID = P_Invoice_Distribution_ID
453 AND APHD.Invoice_Payment_ID = P_Invoice_Payment_ID
454 AND APH.Related_Event_ID = P_Related_Event_ID
455 AND APHD.Payment_History_ID = APH.Payment_History_ID
456 AND APH.Posted_Flag <> 'N' -- changed for bug 7560247
457 AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'AWT'); --bug 9495429
458
459 RETURN NVL(l_pay_sum,0);
460
461 END Get_Casc_Pay_Sum;
462
463
464 -------------------------------------------------------------------------------
465 -- Function Get_Casc_Inv_Dist_Sum
466 -- This function gets the sum of the paid amount in invoice currency from the
467 -- payment history distributions for the given invoice distribution which will
468 -- be used for payment cascase events
469 --
470 --------------------------------------------------------------------------------
471 FUNCTION Get_Casc_Inv_Dist_Sum
472 (P_Invoice_Distribution_ID IN NUMBER
473 ,P_Related_Event_ID IN NUMBER
474 ,P_Invoice_Payment_ID IN NUMBER
475 ,P_Calling_Sequence IN VARCHAR2
476 ) RETURN NUMBER IS
477
478 l_curr_calling_sequence VARCHAR2(2000);
479 l_inv_dist_sum NUMBER;
480
481 BEGIN
482
483 l_curr_calling_sequence := 'AP_ACCOUNTING_PAY_PKG.Get_Casc_Pay_Sum<- ' ||
484 P_Calling_Sequence;
485
486
487
488 SELECT SUM(APHD.Invoice_Dist_Amount)
489 INTO l_inv_dist_sum
490 FROM AP_Payment_Hist_Dists APHD,
491 AP_Payment_History_All APH
492 WHERE APHD.Invoice_Distribution_ID = P_Invoice_Distribution_ID
493 AND APHD.Invoice_Payment_ID = P_Invoice_Payment_ID
494 AND APH.Related_Event_ID = P_Related_Event_ID
495 AND APHD.Payment_History_ID = APH.Payment_History_ID
496 AND APH.Posted_Flag <> 'N' -- changed for bug 7560247
497 AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'AWT'); --bug 9495429
498
499 RETURN NVL(l_inv_dist_sum,0);
500
501 END Get_Casc_Inv_Dist_Sum;
502
503
504
505 -------------------------------------------------------------------------------
506 -- Function Get_Casc_Bank_Curr_Sum
507 -- This function gets the sum of the paid amount in the bank currency from the
508 -- payment history distributions for the given invoice distribution which will
509 -- be used for payment cascase events
510 --
511 --------------------------------------------------------------------------------
512 FUNCTION Get_Casc_Bank_Curr_Sum
513 (P_Invoice_Distribution_ID IN NUMBER
514 ,P_Related_Event_ID IN NUMBER
515 ,P_Invoice_Payment_ID IN NUMBER
516 ,P_Calling_Sequence IN VARCHAR2
517 ) RETURN NUMBER IS
518
519 l_curr_calling_sequence VARCHAR2(2000);
520 l_bank_curr_sum NUMBER;
521
522 BEGIN
523
524 l_curr_calling_sequence := 'AP_ACCOUNTING_PAY_PKG.Get_Casc_Pay_Sum<- ' ||
525 P_Calling_Sequence;
526
527
528 SELECT SUM(APHD.Bank_Curr_Amount)
529 INTO l_bank_curr_sum
530 FROM AP_Payment_Hist_Dists APHD,
531 AP_Payment_History_All APH
532 WHERE APHD.Invoice_Distribution_ID = P_Invoice_Distribution_ID
533 AND APHD.Invoice_Payment_ID = P_Invoice_Payment_ID
534 AND APH.Related_Event_ID = P_Related_Event_ID
535 AND APHD.Payment_History_ID = APH.Payment_History_ID
536 AND APH.Posted_Flag <> 'N' -- changed for bug 7560247
537 AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'AWT'); --bug 9495429
538
539 RETURN NVL(l_bank_curr_sum,0);
540
541 END Get_Casc_Bank_Curr_Sum;
542
543
544
545 -------------------------------------------------------------------------------
546 -- Function Get_Casc_Prepay_Sum
547 -- This function gets the sum of the prepayment amount from the prepay appl payment
548 -- distributions for the given invoice distribution which will be used for
549 -- prepayment appl cascase events
550 --
551 --------------------------------------------------------------------------------
552 FUNCTION Get_Casc_Prepay_Sum
553 (P_Invoice_Distribution_ID IN NUMBER
554 ,P_Prepay_App_Dist_ID IN NUMBER
555 ,P_Calling_Sequence IN VARCHAR2
556 ) RETURN NUMBER IS
557
558 l_curr_calling_sequence VARCHAR2(2000);
559 l_prepay_sum NUMBER;
560
561 BEGIN
562
563 l_curr_calling_sequence := 'AP_ACCOUNTING_PAY_PKG.Get_Casc_Prepay_Sum<- ' ||
564 P_Calling_Sequence;
565
566
567 SELECT SUM(APAD.Amount)
568 INTO l_prepay_sum
569 FROM AP_Prepay_App_Dists APAD
570 WHERE APAD.Invoice_Distribution_ID = P_Invoice_Distribution_ID
571 AND APAD.Prepay_App_Distribution_ID = P_Prepay_App_Dist_ID
572 AND APAD.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
573 'PREPAY APPL NONREC TAX', 'AWT',
574 'EXCHANGE RATE VARIANCE');
575
576 RETURN NVL(l_prepay_sum,0);
577
578 END Get_Casc_Prepay_Sum;
579
580
581 -------------------------------------------------------------------------------
582 -- Function Get_Casc_Tax_Diff_Sum
583 -- This function gets the sum of the tax diff amount from the prepay appl payment
584 -- distributions for the given invoice distribution which will be used for
585 -- prepayment appl cascase events
586 --
587 --------------------------------------------------------------------------------
588 FUNCTION Get_Casc_Tax_Diff_Sum
589 (P_Invoice_Distribution_ID IN NUMBER
590 ,P_Prepay_App_Dist_ID IN NUMBER
591 ,P_Calling_Sequence IN VARCHAR2
592 ) RETURN NUMBER IS
593
594 l_curr_calling_sequence VARCHAR2(2000);
595 l_tax_diff_sum NUMBER;
596
597 BEGIN
598
599 l_curr_calling_sequence := 'AP_ACCOUNTING_PAY_PKG.Get_Casc_Prepay_Sum<- ' ||
600 P_Calling_Sequence;
601
602
603 SELECT SUM(APAD.Amount)
604 INTO l_tax_diff_sum
605 FROM AP_Prepay_App_Dists APAD
606 WHERE APAD.Invoice_Distribution_ID = P_Invoice_Distribution_ID
607 AND APAD.Prepay_App_Distribution_ID = P_Prepay_App_Dist_ID
608 AND APAD.Prepay_Dist_Lookup_Code IN ('TAX DIFF');
609
610 RETURN NVL(l_tax_diff_sum,0);
611
612 END Get_Casc_Tax_Diff_Sum;
613
614
615
616 -------------------------------------------------------------------------------
617 -- Function Get_Casc_Discount_Sum
618 -- This function gets the sum of the discount amounts from the payment history
619 -- distributions for the given invoice distribution which will be used for
620 -- payment cascase events
621 --
622 --------------------------------------------------------------------------------
623 FUNCTION Get_Casc_Discount_Sum
624 (P_Invoice_Distribution_ID IN NUMBER
625 ,P_Related_Event_ID IN NUMBER
626 ,P_Invoice_Payment_ID IN NUMBER
627 ,P_Calling_Sequence IN VARCHAR2
628 ) RETURN NUMBER IS
629
630 l_curr_calling_sequence VARCHAR2(2000);
631 l_discount_sum NUMBER;
632
633 BEGIN
634
635 l_curr_calling_sequence := 'AP_ACCOUNTING_PAY_PKG.Get_Casc_Discount_Sum<- ' ||
636 P_Calling_Sequence;
637
638
639 SELECT SUM(APHD.Amount)
640 INTO l_discount_sum
641 FROM AP_Payment_Hist_Dists APHD,
642 AP_Payment_History_All APH
643 WHERE APHD.Invoice_Distribution_ID = P_Invoice_Distribution_ID
644 AND APHD.Invoice_Payment_ID = P_Invoice_Payment_ID
645 AND APH.Related_Event_ID = P_Related_Event_ID
646 AND APHD.Payment_History_ID = APH.Payment_History_ID
647 AND APH.Posted_Flag <> 'N' -- changed for bug 7560247
648 AND Pay_Dist_Lookup_Code = 'DISCOUNT';
649
650 RETURN NVL(l_discount_sum,0);
651
652 END Get_Casc_Discount_Sum;
653
654
655 -------------------------------------------------------------------------------
656 -- Function Get_Casc_Inv_Dist_Disc_Sum
657 -- This function gets the sum of the discount amounts from the payment history
658 -- distributions for the given invoice distribution which will be used for
659 -- payment cascase events
660 --
661 --------------------------------------------------------------------------------
662 FUNCTION Get_Casc_Inv_Dist_Disc_Sum
663 (P_Invoice_Distribution_ID IN NUMBER
664 ,P_Related_Event_ID IN NUMBER
665 ,P_Invoice_Payment_ID IN NUMBER
666 ,P_Calling_Sequence IN VARCHAR2
667 ) RETURN NUMBER IS
668
669 l_curr_calling_sequence VARCHAR2(2000);
670 l_discount_sum NUMBER;
671
672 BEGIN
673
674 l_curr_calling_sequence := 'AP_ACCOUNTING_PAY_PKG.Get_Casc_Discount_Sum<- ' ||
675 P_Calling_Sequence;
676
677
678 SELECT SUM(APHD.Invoice_Dist_Amount)
679 INTO l_discount_sum
680 FROM AP_Payment_Hist_Dists APHD,
681 AP_Payment_History_All APH
682 WHERE APHD.Invoice_Distribution_ID = P_Invoice_Distribution_ID
683 AND APHD.Invoice_Payment_ID = P_Invoice_Payment_ID
684 AND APH.Related_Event_ID = P_Related_Event_ID
685 AND APHD.Payment_History_ID = APH.Payment_History_ID
686 AND APH.Posted_Flag <> 'N' -- changed for bug 7560247
687 AND Pay_Dist_Lookup_Code = 'DISCOUNT';
688
689 RETURN NVL(l_discount_sum,0);
690
691 END Get_Casc_Inv_Dist_Disc_Sum;
692
693
694
695 -------------------------------------------------------------------------------
696 -- Function Get_Casc_Bank_Curr_Disc_Sum
697 -- This function gets the sum of the discount amounts from the payment history
698 -- distributions for the given invoice distribution which will be used for
699 -- payment cascase events
700 --
701 --------------------------------------------------------------------------------
702 FUNCTION Get_Casc_Bank_Curr_Disc_Sum
703 (P_Invoice_Distribution_ID IN NUMBER
704 ,P_Related_Event_ID IN NUMBER
705 ,P_Invoice_Payment_ID IN NUMBER
706 ,P_Calling_Sequence IN VARCHAR2
707 ) RETURN NUMBER IS
708
709 l_curr_calling_sequence VARCHAR2(2000);
710 l_discount_sum NUMBER;
711
712 BEGIN
713
714 l_curr_calling_sequence := 'AP_ACCOUNTING_PAY_PKG.Get_Casc_Discount_Sum<- ' ||
715 P_Calling_Sequence;
716
717
718 SELECT SUM(APHD.Bank_Curr_Amount)
719 INTO l_discount_sum
720 FROM AP_Payment_Hist_Dists APHD,
721 AP_Payment_History_All APH
722 WHERE APHD.Invoice_Distribution_ID = P_Invoice_Distribution_ID
723 AND APHD.Invoice_Payment_ID = P_Invoice_Payment_ID
724 AND APH.Related_Event_ID = P_Related_Event_ID
725 AND APHD.Payment_History_ID = APH.Payment_History_ID
726 AND APH.Posted_Flag <> 'N' -- changed for bug 7560247
727 AND Pay_Dist_Lookup_Code = 'DISCOUNT';
728
729 RETURN NVL(l_discount_sum,0);
730
731 END Get_Casc_Bank_Curr_Disc_Sum;
732
733
734
735 -------------------------------------------------------------------------------
736 -- Procedure Get_Pay_Sum
737 -- This procedure gets the sum of the payment amount from the payment history
738 -- distributions for the given invoice distribution
739 -- Modified history
740 -- 1. for bug 5570002, modify the condition of APH.posted_flag to "Y"
741 --------------------------------------------------------------------------------
742 PROCEDURE Get_Pay_Sum
743 (P_Invoice_Distribution_ID IN NUMBER
744 ,P_Transaction_Type IN VARCHAR2
745 ,P_Payment_Sum OUT NOCOPY NUMBER
746 ,P_Inv_Dist_Sum OUT NOCOPY NUMBER
747 ,P_Bank_Curr_Sum OUT NOCOPY NUMBER
748 ,P_Calling_Sequence IN VARCHAR2
749 ) IS
750
751 l_curr_calling_sequence VARCHAR2(2000);
752 l_pay_sum NUMBER;
753 l_inv_dist_sum NUMBER;
754 l_bank_curr_sum NUMBER;
755
756 BEGIN
757
758 l_curr_calling_sequence := 'AP_ACCOUNTING_PAY_PKG.Get_Pay_Sum<- ' ||
759 P_Calling_Sequence;
760
761 IF (P_Transaction_Type IN ('PAYMENT CLEARED', 'PAYMENT CLEARING ADJUSTED')) THEN
762
763 SELECT SUM(APHD.Amount),
764 SUM(APHD.Invoice_Dist_Amount),
765 SUM(APHD.Bank_Curr_Amount)
766 INTO l_pay_sum,
767 l_inv_dist_sum,
768 l_bank_curr_sum
769 FROM AP_Payment_Hist_Dists APHD,
770 AP_Payment_History_All APH
771 WHERE APHD.Invoice_Distribution_ID in ( /*bug8882706*/
772 select p_invoice_distribution_id from dual
773 union
774 -- awt distributions which are applied on the p_invoice_distribution_id
775 select distinct aid_awt.invoice_distribution_id
776 from ap_invoice_distributions_all aid_awt,
777 ap_invoice_distributions_all aid_item
778 where 1=1
779 and aid_item.invoice_distribution_id = p_invoice_distribution_id
780 and aid_item.line_type_lookup_code <> 'AWT'
781 and aid_awt.invoice_id = aid_item.invoice_id
782 and aid_awt.awt_related_id = aid_item.invoice_distribution_id
783 and aid_awt.line_type_lookup_code = 'AWT'
784 )
785 AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT', 'AWT')
786 AND NVL(APH.Posted_Flag, 'N') IN ('Y', 'S') --bug 7614480, added status 'S'
787 AND APH.Payment_History_ID = APHD.Payment_History_ID
788 AND APH.Transaction_Type IN ('PAYMENT CLEARING', 'PAYMENT UNCLEARING',
789 'PAYMENT CLEARING ADJUSTED')
790
791 AND NOT EXISTS
792 (SELECT 'Event Reversed'
793 FROM AP_PAYMENT_HISTORY_ALL APH_REL
794 WHERE APH_REL.check_id = APH.check_id --bug9282163
795 AND NVL(APH_REL.RELATED_EVENT_ID, APH_REL.ACCOUNTING_EVENT_ID) =
796 NVL(APH.RELATED_EVENT_ID, APH.ACCOUNTING_EVENT_ID)
797 AND APH_REL.REV_PMT_HIST_ID IS NOT NULL)
798 /*Bug 13908641*/
799 AND NOT EXISTS
800 (SELECT 'Event Reversed'
801 FROM AP_PAYMENT_HISTORY_ALL APH_REL
802 WHERE APH_REL.check_id = APH.check_id --bug9282163
803 AND APH_REL.REV_PMT_HIST_ID = APH.PAYMENT_HISTORY_ID
804 AND APH_REL.REV_PMT_HIST_ID IS NOT NULL);
805 --bug8975671, reversed entries and their reversals shouldn't be considered
806 ELSIF (P_Transaction_Type IN ('PAYMENT MATURED', 'PAYMENT MATURITY ADJUSTED')) THEN
807
808 SELECT SUM(APHD.Amount),
809 SUM(APHD.Invoice_Dist_Amount),
810 SUM(APHD.Bank_Curr_Amount)
811 INTO l_pay_sum,
812 l_inv_dist_sum,
813 l_bank_curr_sum
814 FROM AP_Payment_Hist_Dists APHD,
815 AP_Payment_History_All APH
816 WHERE APHD.Invoice_Distribution_ID in ( /*bug8882706*/
817 select p_invoice_distribution_id from dual
818 union
819 -- awt distributions which are applied on p_invoice_distribution_id
820 select distinct aid_awt.invoice_distribution_id
821 from ap_invoice_distributions_all aid_awt,
822 ap_invoice_distributions_all aid_item
823 where 1=1
824 and aid_item.invoice_distribution_id = p_invoice_distribution_id
825 and aid_item.line_type_lookup_code <> 'AWT'
826 and aid_awt.invoice_id = aid_item.invoice_id
827 and aid_awt.awt_related_id = aid_item.invoice_distribution_id
828 and aid_awt.line_type_lookup_code = 'AWT'
829 )
830 AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT', 'AWT') -- bug8882706
831 AND NVL(APH.Posted_Flag, 'N') IN ('Y', 'S') --bug 7614480, added status 'S'
832 AND APH.Payment_History_ID = APHD.Payment_History_ID
833 AND APH.Transaction_Type IN ('PAYMENT MATURITY', 'PAYMENT MATURITY REVERSED',
834 'PAYMENT MATURITY ADJUSTED')
835 AND NOT EXISTS
836 (SELECT 'Event Reversed'
837 FROM AP_PAYMENT_HISTORY_ALL APH_REL
838 WHERE APH_REL.check_id = APH.check_id --bug9282163
839 AND NVL(APH_REL.RELATED_EVENT_ID, APH_REL.ACCOUNTING_EVENT_ID) =
840 NVL(APH.RELATED_EVENT_ID, APH.ACCOUNTING_EVENT_ID)
841 AND APH_REL.REV_PMT_HIST_ID IS NOT NULL)
842 /*Bug 13908641*/
843 AND NOT EXISTS
844 (SELECT 'Event Reversed'
845 FROM AP_PAYMENT_HISTORY_ALL APH_REL
846 WHERE APH_REL.check_id = APH.check_id --bug9282163
847 AND APH_REL.REV_PMT_HIST_ID = APH.PAYMENT_HISTORY_ID
848 AND APH_REL.REV_PMT_HIST_ID IS NOT NULL);
849 --bug8975671, reversed entries and their reversals shouldn't be considered
850 ELSE
851
852 SELECT SUM(APHD.Amount),
853 SUM(APHD.Invoice_Dist_Amount),
854 SUM(APHD.Bank_Curr_Amount)
855 INTO l_pay_sum,
856 l_inv_dist_sum,
857 l_bank_curr_sum
858 FROM AP_Payment_Hist_Dists APHD,
859 AP_Payment_History_All APH
860 WHERE APHD.Invoice_Distribution_ID in ( /*bug 8882706*/
861 select p_invoice_distribution_id from dual
862 union
863 -- awt distributions which are applied on p_invoice_distribution_id
864 select distinct aid_awt.invoice_distribution_id
865 from ap_invoice_distributions_all aid_awt,
866 ap_invoice_distributions_all aid_item
867 where 1=1
868 and aid_item.invoice_distribution_id = p_invoice_distribution_id
869 and aid_item.line_type_lookup_code <> 'AWT'
870 and aid_awt.invoice_id = aid_item.invoice_id
871 and aid_awt.awt_related_id = aid_item.invoice_distribution_id and aid_awt.line_type_lookup_code = 'AWT'
872 )
873 AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT', 'AWT') -- bug8882706
874 AND NVL(APH.Posted_Flag, 'N') IN ('Y', 'S') --bug 7614480, added status 'S'
875 AND APH.Payment_History_ID = APHD.Payment_History_ID
876 AND APH.Transaction_Type IN ('PAYMENT CREATED', 'PAYMENT CANCELLED', 'PAYMENT ADJUSTED',
877 'MANUAL PAYMENT ADJUSTED', 'UPGRADED MANUAL PMT ADJUSTED',
878 'REFUND RECORDED', 'REFUND ADJUSTED', 'REFUND CANCELLED',
879 'MANUAL REFUND ADJUSTED')
880 AND NOT EXISTS
881 (SELECT 'Event Reversed'
882 FROM AP_PAYMENT_HISTORY_ALL APH_REL
883 WHERE APH_REL.check_id = APH.check_id --bug9282163
884 AND NVL(APH_REL.RELATED_EVENT_ID, APH_REL.ACCOUNTING_EVENT_ID) =
885 NVL(APH.RELATED_EVENT_ID, APH.ACCOUNTING_EVENT_ID)
886 AND APH_REL.REV_PMT_HIST_ID IS NOT NULL)
887 /*Bug 13908641*/
888 AND NOT EXISTS
889 (SELECT 'Event Reversed'
890 FROM AP_PAYMENT_HISTORY_ALL APH_REL
891 WHERE APH_REL.check_id = APH.check_id --bug9282163
892 AND APH_REL.REV_PMT_HIST_ID = APH.PAYMENT_HISTORY_ID
893 AND APH_REL.REV_PMT_HIST_ID IS NOT NULL);
894 --bug8975671, reversed entries and their reversals shouldn't be considered
895 END IF;
896
897 p_payment_sum := NVL(l_pay_sum,0);
898 p_inv_dist_sum := NVL(l_inv_dist_sum,0);
899 p_bank_curr_sum := NVL(l_bank_curr_sum,0);
900
901 EXCEPTION
902
903 WHEN OTHERS THEN
904 IF (SQLCODE <> -20001) THEN
905 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
906 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
907 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
908 END IF;
909 APP_EXCEPTION.RAISE_EXCEPTION;
910
911 END Get_Pay_Sum;
912
913
914
915 -------------------------------------------------------------------------------
916 -- Procedure Get_Pay_Base_Sum
917 -- This procedure gets the sum of the payment amount from the payment history
918 -- distributions for the given invoice distribution
919 -- Added For Bug 9282465
920 --------------------------------------------------------------------------------
921 PROCEDURE Get_Pay_Base_Sum
922 (P_Invoice_Distribution_ID IN NUMBER
923 ,P_Transaction_Type IN VARCHAR2
924 ,P_Payment_Sum OUT NOCOPY NUMBER
925 ,P_Inv_Dist_Sum OUT NOCOPY NUMBER
926 ,P_Bank_Curr_Sum OUT NOCOPY NUMBER
927 ,P_Calling_Sequence IN VARCHAR2
928 ) IS
929
930 l_curr_calling_sequence VARCHAR2(2000);
931 l_pay_sum NUMBER;
932 l_inv_dist_sum NUMBER;
933 l_bank_curr_sum NUMBER;
934
935 BEGIN
936
937 l_curr_calling_sequence := 'AP_ACCOUNTING_PAY_PKG.Get_Pay_Base_Sum<- ' ||
938 P_Calling_Sequence;
939
940 IF (P_Transaction_Type IN ('PAYMENT CLEARED', 'PAYMENT CLEARING ADJUSTED')) THEN
941
942 SELECT SUM( APHD.Paid_Base_Amount )
943 , SUM( APHD.Invoice_Dist_Base_Amount )
944 , SUM( APHD.Cleared_Base_Amount )
945 INTO l_pay_sum
946 , l_inv_dist_sum
947 , l_bank_curr_sum
948 FROM AP_Payment_Hist_Dists APHD
949 , AP_Payment_History_All APH
950 WHERE APHD.Invoice_Distribution_ID IN
951 (SELECT p_invoice_distribution_id
952 FROM dual
953 UNION
954 SELECT DISTINCT aid_awt.invoice_distribution_id
955 FROM ap_invoice_distributions_all aid_awt
956 , ap_invoice_distributions_all aid_item
957 WHERE 1 = 1
958 AND aid_item.invoice_distribution_id = p_invoice_distribution_id
959 AND aid_item.line_type_lookup_code <> 'AWT'
960 AND aid_awt.invoice_id = aid_item.invoice_id
961 AND aid_awt.awt_related_id =
962 aid_item.invoice_distribution_id
963 AND aid_awt.line_type_lookup_code = 'AWT'
964 )
965 AND APHD.Pay_Dist_Lookup_Code IN( 'CASH', 'DISCOUNT', 'AWT' )
966 AND NVL( APH.Posted_Flag, 'N' ) IN( 'Y', 'S' )
967 AND APH.Payment_History_ID = APHD.Payment_History_ID
968 AND APH.Transaction_Type IN( 'PAYMENT CLEARING',
969 'PAYMENT UNCLEARING', 'PAYMENT CLEARING ADJUSTED' )
970 AND NOT EXISTS
971 (SELECT 'Event Reversed'
972 FROM AP_PAYMENT_HISTORY_ALL APH_REL
973 WHERE APH_REL.check_id = APH.check_id
974 AND NVL( APH_REL.RELATED_EVENT_ID, APH_REL.ACCOUNTING_EVENT_ID )
975 = NVL( APH.RELATED_EVENT_ID, APH.ACCOUNTING_EVENT_ID )
976 AND APH_REL.REV_PMT_HIST_ID IS NOT NULL
977 );
978 ELSIF (P_Transaction_Type IN ('PAYMENT MATURED', 'PAYMENT MATURITY ADJUSTED')) THEN
979
980 SELECT SUM( APHD.Paid_Base_Amount )
981 , SUM( APHD.Invoice_Dist_Base_Amount )
982 , SUM( APHD.Cleared_Base_Amount )
983 INTO l_pay_sum
984 , l_inv_dist_sum
985 , l_bank_curr_sum
986 FROM AP_Payment_Hist_Dists APHD
987 , AP_Payment_History_All APH
988 WHERE APHD.Invoice_Distribution_ID IN
989 (SELECT p_invoice_distribution_id
990 FROM dual
991 UNION
992 SELECT DISTINCT aid_awt.invoice_distribution_id
993 FROM ap_invoice_distributions_all aid_awt
994 , ap_invoice_distributions_all aid_item
995 WHERE 1 = 1
996 AND aid_item.invoice_distribution_id = p_invoice_distribution_id
997 AND aid_item.line_type_lookup_code <> 'AWT'
998 AND aid_awt.invoice_id = aid_item.invoice_id
999 AND aid_awt.awt_related_id =
1000 aid_item.invoice_distribution_id
1001 AND aid_awt.line_type_lookup_code = 'AWT'
1002 )
1003 AND APHD.Pay_Dist_Lookup_Code IN( 'CASH', 'DISCOUNT', 'AWT' )
1004 AND NVL( APH.Posted_Flag, 'N' ) IN( 'Y', 'S' )
1005 AND APH.Payment_History_ID = APHD.Payment_History_ID
1006 AND APH.Transaction_Type IN( 'PAYMENT MATURITY',
1007 'PAYMENT MATURITY REVERSED', 'PAYMENT MATURITY ADJUSTED' )
1008 AND NOT EXISTS
1009 (SELECT 'Event Reversed'
1010 FROM AP_PAYMENT_HISTORY_ALL APH_REL
1011 WHERE APH_REL.check_id = APH.check_id
1012 AND NVL( APH_REL.RELATED_EVENT_ID, APH_REL.ACCOUNTING_EVENT_ID )
1013 = NVL( APH.RELATED_EVENT_ID, APH.ACCOUNTING_EVENT_ID )
1014 AND APH_REL.REV_PMT_HIST_ID IS NOT NULL
1015 );
1016 ELSE
1017
1018 SELECT SUM( APHD.Paid_Base_Amount )
1019 , SUM( APHD.Invoice_Dist_Base_Amount )
1020 , SUM( APHD.Cleared_Base_Amount )
1021 INTO l_pay_sum
1022 , l_inv_dist_sum
1023 , l_bank_curr_sum
1024 FROM AP_Payment_Hist_Dists APHD
1025 , AP_Payment_History_All APH
1026 WHERE APHD.Invoice_Distribution_ID IN
1027 (SELECT p_invoice_distribution_id
1028 FROM dual
1029 UNION
1030 SELECT DISTINCT aid_awt.invoice_distribution_id
1031 FROM ap_invoice_distributions_all aid_awt
1032 , ap_invoice_distributions_all aid_item
1033 WHERE 1 = 1
1034 AND aid_item.invoice_distribution_id = p_invoice_distribution_id
1035 AND aid_item.line_type_lookup_code <> 'AWT'
1036 AND aid_awt.invoice_id = aid_item.invoice_id
1037 AND aid_awt.awt_related_id =
1038 aid_item.invoice_distribution_id
1039 AND aid_awt.line_type_lookup_code = 'AWT'
1040 )
1041 AND APHD.Pay_Dist_Lookup_Code IN( 'CASH', 'DISCOUNT', 'AWT' )
1042 AND NVL( APH.Posted_Flag, 'N' ) IN( 'Y', 'S' )
1043 AND APH.Payment_History_ID = APHD.Payment_History_ID
1044 AND APH.Transaction_Type IN( 'PAYMENT CREATED', 'PAYMENT CANCELLED'
1045 , 'PAYMENT ADJUSTED', 'MANUAL PAYMENT ADJUSTED',
1046 'UPGRADED MANUAL PMT ADJUSTED', 'REFUND RECORDED', 'REFUND ADJUSTED',
1047 'REFUND CANCELLED', 'MANUAL REFUND ADJUSTED' )
1048 AND NOT EXISTS
1049 (SELECT 'Event Reversed'
1050 FROM AP_PAYMENT_HISTORY_ALL APH_REL
1051 WHERE APH_REL.check_id = APH.check_id
1052 AND NVL( APH_REL.RELATED_EVENT_ID, APH_REL.ACCOUNTING_EVENT_ID )
1053 = NVL( APH.RELATED_EVENT_ID, APH.ACCOUNTING_EVENT_ID )
1054 AND APH_REL.REV_PMT_HIST_ID IS NOT NULL
1055 );
1056 END IF;
1057
1058 p_payment_sum := NVL(l_pay_sum,0);
1059 p_inv_dist_sum := NVL(l_inv_dist_sum,0);
1060 p_bank_curr_sum := NVL(l_bank_curr_sum,0);
1061
1062 EXCEPTION
1063
1064 WHEN OTHERS THEN
1065 IF (SQLCODE <> -20001) THEN
1066 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1067 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1068 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1069 END IF;
1070 APP_EXCEPTION.RAISE_EXCEPTION;
1071
1072 END Get_Pay_Base_Sum;
1073
1074
1075 -------------------------------------------------------------------------------
1076 -- Function Get_Prepay_Sum
1077 -- This function gets the sum of the prepaid amount from the prepay appl payment
1078 -- distributions for the given invoice distribution
1079 --
1080 --------------------------------------------------------------------------------
1081 FUNCTION Get_Prepay_Sum
1082 ( P_Invoice_Distribution_ID IN NUMBER
1083 ,P_Calling_Sequence IN VARCHAR2
1084 ) RETURN NUMBER IS
1085
1086 l_curr_calling_sequence VARCHAR2(2000);
1087 l_prepay_sum NUMBER;
1088
1089 BEGIN
1090
1091 l_curr_calling_sequence := 'AP_ACCOUNTING_PAY_PKG.Get_Prepay_Sum<- ' ||
1092 P_Calling_Sequence;
1093
1094
1095 SELECT SUM(APAD.Amount)
1096 INTO l_prepay_sum
1097 FROM AP_Prepay_App_Dists APAD,
1098 AP_Invoice_Distributions_All AID
1099 WHERE APAD.Invoice_Distribution_ID in ( /*bug 8882706*/
1100 select p_invoice_distribution_id from dual
1101 union
1102 /* awt distributions which are applied on the p_invoice_distribution_id*/
1103 select distinct aid_awt.invoice_distribution_id
1104 from ap_invoice_distributions_all aid_awt,
1105 ap_invoice_distributions_all aid_item
1106 where 1=1
1107 and aid_item.invoice_distribution_id = p_invoice_distribution_id
1108 and aid_item.line_type_lookup_code <> 'AWT'
1109 and aid_awt.invoice_id = aid_item.invoice_id
1110 and aid_awt.awt_related_id = aid_item.invoice_distribution_id
1111 and aid_awt.line_type_lookup_code = 'AWT'
1112 )
1113 AND APAD.Prepay_App_Distribution_ID = AID.Invoice_Distribution_ID
1114 AND NVL(AID.Reversal_Flag, 'N') <> 'Y' --bug9322001
1115 AND APAD.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
1116 'PREPAY APPL NONREC TAX', 'AWT',
1117 'EXCHANGE RATE VARIANCE');
1118
1119 RETURN NVL(l_prepay_sum,0);
1120
1121 END Get_Prepay_Sum;
1122
1123
1124 -------------------------------------------------------------------------------
1125 -- Procedure Get_Prepay_Base_Sum
1126 -- This Procedure gets the sum of the prepaid amounts from the
1127 -- prepay appl payment distributions for the given invoice distribution
1128 -- Added For Bug 9282465
1129 --------------------------------------------------------------------------------
1130 PROCEDURE Get_Prepay_Base_Sum
1131 (P_Invoice_Distribution_ID IN NUMBER
1132 ,P_Paid_Base_Sum OUT NOCOPY NUMBER
1133 ,P_Inv_Dist_Base_Sum OUT NOCOPY NUMBER
1134 ,P_Clr_Base_Curr_Sum OUT NOCOPY NUMBER
1135 ,P_Calling_Sequence IN VARCHAR2)IS
1136
1137 l_curr_calling_sequence VARCHAR2(2000);
1138
1139 BEGIN
1140
1141 l_curr_calling_sequence := 'AP_ACCOUNTING_PAY_PKG.Get_Prepay_Base_Sum<- ' ||
1142 P_Calling_Sequence;
1143
1144 SELECT SUM( APAD.Base_Amount )
1145 , SUM( APAD.Base_Amt_At_Prepay_XRate )
1146 , SUM( APAD.Base_Amt_At_Prepay_Clr_XRate )
1147 INTO P_Inv_Dist_Base_Sum
1148 , P_Paid_Base_Sum
1149 , P_Clr_Base_Curr_Sum
1150 FROM AP_Prepay_App_Dists APAD
1151 WHERE APAD.Invoice_Distribution_ID IN
1152 ( SELECT p_invoice_distribution_id FROM dual
1153 UNION
1154 SELECT DISTINCT aid_awt.invoice_distribution_id
1155 FROM ap_invoice_distributions_all aid_awt
1156 , ap_invoice_distributions_all aid_item
1157 WHERE 1 = 1
1158 AND aid_item.invoice_distribution_id = p_invoice_distribution_id
1159 AND aid_item.line_type_lookup_code <> 'AWT'
1160 AND aid_awt.invoice_id = aid_item.invoice_id
1161 AND aid_awt.awt_related_id = aid_item.invoice_distribution_id
1162 AND aid_awt.line_type_lookup_code = 'AWT'
1163 )
1164 AND APAD.Prepay_Dist_Lookup_Code IN( 'PREPAY APPL', 'PREPAY APPL REC TAX',
1165 'PREPAY APPL NONREC TAX', 'AWT', 'EXCHANGE RATE VARIANCE' );
1166 EXCEPTION
1167
1168 WHEN OTHERS THEN
1169 IF (SQLCODE <> -20001) THEN
1170 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1171 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1172 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1173 END IF;
1174 APP_EXCEPTION.RAISE_EXCEPTION;
1175
1176 END Get_Prepay_Base_Sum;
1177
1178
1179 -------------------------------------------------------------------------------
1180 -- Function Is_Final_Payment
1181 -- Function to check if this payment is the final payment for the given
1182 -- invoice.
1183 -- bug 5623129 Note
1184 -- 1.added more debug message
1185 -- 2. P_Transaction_Type should match to event type.
1186 -- The payment history transaction type is different from event type
1187 -- 3. add AND APH.Posted_Flag = 'Y' to get accounted paid amount
1188 -- bug 9495429, removed cascade and cancellation transaction_types
1189 --------------------------------------------------------------------------------
1190 FUNCTION Is_Final_Payment
1191 (P_Inv_Rec IN r_invoices_info
1192 ,P_Payment_Amount IN NUMBER
1193 ,P_Discount_Amount IN NUMBER
1194 ,P_Prepay_Amount IN NUMBER
1195 ,P_Transaction_Type IN VARCHAR2
1196 ,P_calling_sequence IN VARCHAR2
1197 ) RETURN BOOLEAN IS
1198
1199 l_paid_acctd_amt NUMBER;
1200 l_prepaid_acctd_amt NUMBER;
1201 l_total_paid_amt NUMBER;
1202 l_total_prepaid_amt NUMBER;
1203 l_final_payment BOOLEAN := FALSE;
1204 l_inv_inc_prepay_tot NUMBER; --bug8613795
1205 l_inv_inc_prepay_pay NUMBER; --bug8613795
1206 l_curr_calling_sequence VARCHAR2(2000);
1207 l_total_awt NUMBER; --Bug 9166188
1208
1209 l_procedure_name CONSTANT VARCHAR2(30) := 'is_final_payment';
1210 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1211
1212 BEGIN
1213
1214
1215 l_curr_calling_sequence := 'AP_ACCOUNTING_PAY_PKG.Is_Final_Payment<-' ||
1216 P_Calling_Sequence;
1217
1218 -- add this call for Bug10183934
1219 IF NOT Is_Upgrade_Proration_Exists(
1220 P_Rounding_Method => G_FINAL_PAY
1221 ,P_Inv_Rec => p_inv_rec
1222 ,P_Invoice_Payment_Id => NULL
1223 ,P_Prepay_Appl_Distribution_Id => NULL
1224 ,P_Prepay_Distribution_Id => NULL
1225 ,P_Transaction_Type => p_transaction_type
1226 ,P_Calling_Sequence => l_curr_calling_sequence
1227 )
1228 THEN
1229
1230 /* We need to get the paid amount for a particular transaction type
1231 as payment hist dists stores paid amounts for all types of
1232 payment events. */
1233
1234
1235
1236 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1237 l_log_msg := 'Begin of is_ainal_payment function call and passin parameters are' ||
1238 'P_Payment_Amount=' || P_Payment_Amount ||
1239 'P_Discount_Amount=' ||P_Discount_Amount ||
1240 'P_Prepay_Amount =' || P_Prepay_Amount ||
1241 'P_Transaction_Type =' || P_Transaction_Type;
1242 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1243 END IF;
1244
1245
1246 IF (P_Transaction_Type IN ('PAYMENT CLEARED')) THEN
1247
1248 /* Getting the sum of payment distributions to check if this is the final
1249 payment */
1250
1251 -------------------------------------------------------------------------
1252 -- bug 5570002
1253 -- 1. Take out the Exchange rate variance consideration
1254 -- Because for entered amount, it is 0 always
1255 -- 2. comment out the "APH.posted_flag" <> 'N' and
1256 -- later change to "APH.posted_flag" = 'Y'
1257 -------------------------------------------------------------------------
1258
1259 SELECT SUM(--DECODE(APHD.Pay_Dist_Lookup_Code,
1260 --'EXCHANGE RATE VARIANCE', -1 * APHD.Amount,
1261 APHD.Amount)
1262 INTO l_paid_acctd_amt
1263 FROM AP_Payment_Hist_Dists APHD,
1264 AP_Invoice_Distributions_All AID,
1265 AP_Payment_History_All APH
1266 WHERE AID.Invoice_ID = p_inv_rec.invoice_id
1267 AND AID.Invoice_Distribution_ID = APHD.Invoice_Distribution_ID
1268 AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT') --bug 9265516, removed 'AWT'
1269 AND APH.Posted_Flag IN ('Y', 'S') --bug 7614480, added status 'S'
1270 AND APH.Payment_History_ID = APHD.Payment_History_ID
1271 AND APH.Transaction_Type IN ('PAYMENT CLEARING')
1272 AND NOT EXISTS(SELECT 'reversed event'
1273 FROM AP_PAYMENT_HISTORY_ALL APH_REV
1274 WHERE APH_REV.check_id = APH.check_id --bug9282163
1275 AND nvl(aph_rev.related_event_id, aph_rev.accounting_event_id)
1276 = nvl(aph.related_event_id, aph.accounting_event_id)
1277 AND aph_rev.rev_pmt_hist_id IS NOT NULL); --bug 7614480, added not exists
1278
1279
1280 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1281 l_log_msg := 'transaction type is payment clearing and ' ||
1282 'l_paid_acctd_amt=' || l_paid_acctd_amt;
1283 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1284 END IF;
1285
1286 ELSIF (P_Transaction_Type IN ('PAYMENT MATURED')) THEN
1287
1288 /* Getting the sum of payment distributions to check if this is the final
1289 payment */
1290 SELECT SUM(--DECODE(APHD.Pay_Dist_Lookup_Code,
1291 --'EXCHANGE RATE VARIANCE', -1 * APHD.Amount,
1292 APHD.Amount)
1293 INTO l_paid_acctd_amt
1294 FROM AP_Payment_Hist_Dists APHD,
1295 AP_Invoice_Distributions_All AID,
1296 AP_Payment_History_All APH
1297 WHERE AID.Invoice_ID = p_inv_rec.invoice_id
1298 AND AID.Invoice_Distribution_ID = APHD.Invoice_Distribution_ID
1299 AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT') --bug 9265516, removed 'AWT'
1300 AND APH.Posted_Flag IN ('Y', 'S') --bug 7614480, added status 'S'
1301 AND APH.Payment_History_ID = APHD.Payment_History_ID
1302 AND APH.Transaction_Type IN ('PAYMENT MATURITY')
1303 AND NOT EXISTS(SELECT 'reversed event'
1304 FROM AP_PAYMENT_HISTORY_ALL APH_REV
1305 WHERE APH_REV.check_id = APH.check_id --bug9282163
1306 AND nvl(aph_rev.related_event_id, aph_rev.accounting_event_id)
1307 = nvl(aph.related_event_id, aph.accounting_event_id)
1308 AND aph_rev.rev_pmt_hist_id IS NOT NULL); --bug 7614480, added not exists
1309
1310 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1311 l_log_msg := 'transaction type is payment matruity and ' ||
1312 'l_paid_acctd_amt=' || l_paid_acctd_amt;
1313 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1314 END IF;
1315
1316
1317 ELSE
1318
1319 /* Getting the sum of payment distributions to check if this is the final
1320 payment */
1321 SELECT SUM(--DECODE(APHD.Pay_Dist_Lookup_Code,
1322 --'EXCHANGE RATE VARIANCE', -1 * APHD.Amount,
1323 APHD.Amount)
1324 INTO l_paid_acctd_amt
1325 FROM AP_Payment_Hist_Dists APHD,
1326 AP_Invoice_Distributions_All AID,
1327 AP_Payment_History_All APH,
1328 AP_INVOICE_PAYMENTS_ALL AIP
1329 WHERE AID.Invoice_ID = p_inv_rec.invoice_id
1330 AND AID.Invoice_Distribution_ID = APHD.Invoice_Distribution_ID
1331 AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT') --bug 9265516, removed 'AWT'
1332 AND APH.Posted_Flag IN ('Y', 'S') --bug 7614480, added status 'S'
1333 AND APH.Payment_History_ID = APHD.Payment_History_ID
1334 AND APH.Transaction_Type IN ('PAYMENT CREATED', 'MANUAL PAYMENT ADJUSTED',
1335 'UPGRADED MANUAL PMT ADJUSTED', 'REFUND RECORDED',
1336 'MANUAL REFUND ADJUSTED')
1337 AND aphd.invoice_payment_id = aip.invoice_payment_id
1338 AND aip.invoice_id = aid.invoice_id
1339 AND aip.check_id = aph.check_id
1340 AND nvl(aip.reversal_flag, 'N') <> 'Y'; --bug 7614480, added not exists
1341
1342 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1343 l_log_msg := 'transaction type is payment created or others ' ||
1344 'l_paid_acctd_amt=' || l_paid_acctd_amt;
1345 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1346 END IF;
1347
1348 END IF;
1349
1350
1351 /* Get the total prepaid amount from the ap_prepay_app_dists table */
1352 /* bug9322001, changed the where clause to remove conditions on accounting */
1353 /* events, and checked only for reversal flag on the Prepay Application dists */
1354 SELECT SUM(APAD.Amount)
1355 INTO l_prepaid_acctd_amt
1356 FROM AP_Prepay_App_Dists APAD,
1357 AP_Invoice_Distributions_All AID,
1358 AP_PREPAY_HISTORY_ALL APPH
1359 WHERE AID.Invoice_ID = p_inv_rec.invoice_id
1360 AND AID.Invoice_Distribution_ID = APAD.Invoice_Distribution_ID
1361 AND APAD.prepay_history_id = APPH.PREPAY_HISTORY_ID
1362 AND APAD.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
1363 'PREPAY APPL NONREC TAX') --bug 9265516, removed 'AWT'
1364 AND NOT EXISTS( SELECT 'reversed prepay application'
1365 FROM ap_invoice_distributions_all aidp
1366 WHERE aidp.invoice_distribution_id = APAD.prepay_app_distribution_id
1367 AND aidp.reversal_flag = 'Y'); --bug 7614480, added not exists
1368
1369
1370 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1371 l_log_msg := 'there is a prepay application and ' ||
1372 'l_prepaid_acctd_amt =' || l_prepaid_acctd_amt;
1373 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1374 END IF;
1375
1376
1377 IF (p_inv_rec.invoice_currency_code <> p_inv_rec.payment_currency_code) THEN
1378
1379 l_total_prepaid_amt := GL_Currency_API.Convert_Amount(
1380 p_inv_rec.invoice_currency_code,
1381 p_inv_rec.payment_currency_code,
1382 p_inv_rec.payment_cross_rate_date,
1383 'EMU FIXED',
1384 NVL(l_prepaid_acctd_amt,0)
1385 + NVL(p_prepay_amount,0));
1386
1387 ELSE
1388
1389 l_total_prepaid_amt := NVL(l_prepaid_acctd_amt,0) + NVL(p_prepay_amount,0);
1390
1391 END IF;
1392
1393 -- bug8613795
1394
1395 SELECT NVL(SUM(AID.amount), 0)
1396 INTO l_inv_inc_prepay_tot
1397 FROM ap_invoice_distributions_all AID
1398 WHERE AID.invoice_id = p_inv_rec.invoice_id
1399 AND AID.line_type_lookup_code IN ('PREPAY','REC_TAX','NONREC_TAX')
1400 AND AID.prepay_distribution_id IS NOT NULL
1401 AND AID.invoice_includes_prepay_flag = 'Y';
1402 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1403 l_log_msg := 'Total amount of distributions having invoice_includes_prepay_flag as Y ' ||
1404 'l_inv_inc_prepay_pay =' || l_inv_inc_prepay_pay;
1405 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1406 END IF;
1407
1408 IF (p_inv_rec.invoice_currency_code <> p_inv_rec.payment_currency_code) THEN
1409
1410 l_inv_inc_prepay_pay := GL_Currency_API.Convert_Amount(
1411 p_inv_rec.invoice_currency_code,
1412 p_inv_rec.payment_currency_code,
1413 p_inv_rec.payment_cross_rate_date,
1414 'EMU FIXED',
1415 l_inv_inc_prepay_tot);
1416 /* Bug 13373457*/
1417 ELSE
1418 l_inv_inc_prepay_pay := l_inv_inc_prepay_tot;
1419 END IF;
1420
1421 --Bug 9166188
1422
1423 SELECT nvl(sum(amount),0) into l_total_awt
1424 FROM ap_invoice_distributions_all aid
1425 WHERE aid.invoice_id= p_inv_rec.invoice_id
1426 AND aid.line_type_lookup_code ='AWT';
1427
1428 IF (p_inv_rec.invoice_currency_code <> p_inv_rec.payment_currency_code) THEN
1429
1430 l_total_awt := GL_Currency_API.Convert_Amount(
1431 p_inv_rec.invoice_currency_code,
1432 p_inv_rec.payment_currency_code,
1433 p_inv_rec.payment_cross_rate_date,
1434 'EMU FIXED',
1435 l_total_awt);
1436 END IF;
1437
1438 --Bug 9166188
1439
1440
1441
1442 l_total_paid_amt := NVL(l_paid_acctd_amt,0) + NVL(p_payment_amount,0)
1443 + NVL(p_discount_amount,0);
1444
1445
1446 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1447 l_log_msg := 'Now total paid amount = l_paid_acctd_amt + p_payment_amount + p_discount_amount and' ||
1448 ' l_total_paid_amt =' || l_total_paid_amt ||
1449 'compare invoice amount either with ' ||
1450 'p_inv_rec.pay_curr_invoice_amount' || p_inv_rec.pay_curr_invoice_amount ||
1451 'p_inv_rec.invoice_amount ' || p_inv_rec.invoice_amount ||
1452 'l_total_awt '||l_total_awt;
1453
1454 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1455 END IF;
1456
1457 --bug8613795
1458 --Bug 9166188
1459 IF (nvl(p_inv_rec.pay_curr_invoice_amount, p_inv_rec.invoice_amount) -
1460 nvl(l_inv_inc_prepay_pay,0) + nvl(l_total_awt,0)
1461 = nvl(l_total_paid_amt,0) - nvl(l_total_prepaid_amt,0)) THEN
1462
1463 l_final_payment := TRUE;
1464
1465 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1466 l_log_msg := 'This is a final payment after comparison';
1467 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1468 END IF;
1469
1470 END IF;
1471
1472 END IF; -- Is_Upgrade_Proration_Exists
1473
1474 RETURN l_final_payment;
1475
1476 END Is_Final_Payment;
1477
1478 -------------------------------------------------------------------------------
1479 -- FUNCTION Get_Base_Amount RETURN NUMBER
1480 -- Converts the given amount to base amount depending on the exchange rate type
1481
1482 -- Parameters
1483 ----------
1484 -- Amount - Amount to convert
1485 -- Currency_Code - Currency code to convert from
1486 -- Base_Currency_Code - Currency Code to convert to
1487 -- Exchange_Rate_Type - Type of exchange rate
1488 -- Exchange_Rate_Date - Date the conversion is happening
1489 -- Exchange_Rate - The Exchange rate between the two currencies
1490 -- bug 5623129 note
1491 -- 1. add more debug message
1492 -------------------------------------------------------------------------------
1493 FUNCTION Get_Base_Amount
1494 (P_amount IN NUMBER
1495 ,P_currency_code IN VARCHAR2
1496 ,P_base_currency_code IN VARCHAR2
1497 ,P_exchange_rate_type IN VARCHAR2
1498 ,P_exchange_rate_date IN DATE
1499 ,P_exchange_rate IN NUMBER
1500 ,P_calling_sequence IN VARCHAR2
1501 ) RETURN NUMBER IS
1502
1503 l_base_amount NUMBER := 0 ;
1504 l_curr_calling_sequence VARCHAR2(2000);
1505
1506 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_Base_Amount';
1507 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1508
1509 BEGIN
1510
1511 l_curr_calling_sequence := 'AP_ACCOUNTING_PAY_PKG.Get_Base_Amount<-'
1512 || P_calling_sequence;
1513
1514
1515
1516 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1517 l_log_msg := 'Begin of get_base_amount and parameters are' ||
1518 'p_amount=' || nvl(p_amount, 0) ||
1519 'P_currency_code =' || P_currency_code ||
1520 'P_base_currency_code =' || P_base_currency_code ||
1521 'P_exchange_rate_type =' || P_exchange_rate_type ||
1522 'P_exchange_rate_date =' || P_exchange_rate_date ||
1523 'P_exchange_rate =' || P_exchange_rate ;
1524 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1525 END IF;
1526
1527 IF ( P_currency_code = P_base_currency_code ) THEN
1528
1529 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1530 l_log_msg := 'base currency code = transaction currency code';
1531 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1532
1533 END IF;
1534
1535 l_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(P_amount,
1536 P_base_currency_code);
1537
1538 ELSIF ( P_exchange_rate_type <> 'User'
1539 AND GL_Currency_API.Is_Fixed_Rate(P_currency_code,
1540 P_base_currency_code,
1541 P_exchange_rate_date) = 'Y' ) THEN
1542
1543 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1544
1545 l_log_msg := 'exchange rate type is not user and it is a fixed rate';
1546 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1547
1548 END IF;
1549
1550 l_base_amount := GL_Currency_API.Convert_Amount(P_currency_code,
1551 P_base_currency_code,
1552 P_exchange_rate_date,
1553 P_exchange_rate_type,
1554 P_amount) ;
1555 ELSE
1556
1557 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1558
1559 l_log_msg := 'not a fix rate, and not a same currency code';
1560 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1561
1562 END IF;
1563
1564
1565 l_base_amount := AP_Utilities_Pkg.AP_Round_Currency
1566 (P_amount * NVL(P_exchange_rate, 1),
1567 P_base_currency_code) ;
1568
1569 END IF;
1570
1571 RETURN l_base_amount ;
1572
1573 EXCEPTION
1574 WHEN GL_CURRENCY_API.NO_RATE THEN
1575 RAISE_APPLICATION_ERROR(-20010, 'Could not find fixed rate between'
1576 || P_currency_code || ' and ' || P_base_currency_code || ' on '
1577 || to_char(P_exchange_rate_date) );
1578
1579 END Get_Base_Amount;
1580
1581 -- bug9256922
1582 PROCEDURE Prorate_Historical_Dists (P_calling_sequence VARCHAR2) IS
1583
1584 CURSOR Aprvd_UnPrtd_His_Inv IS
1585 SELECT ai.invoice_id
1586 FROM ap_invoice_distributions_all aid,
1587 ap_invoices_all ai,
1588 xla_events_gt xe,
1589 xla_events xle,
1590 financials_system_params_all fsp
1591 WHERE xe.event_type_code IN('PREPAYMENT APPLIED', 'PREPAYMENT UNAPPLIED')
1592 AND xe.event_status_code NOT IN('N', 'P')
1593 AND aid.accounting_event_id = xe.event_id
1594 AND aid.prepay_distribution_id IS NOT NULL
1595 AND aid.invoice_id = ai.invoice_id
1596 AND aid.org_id = fsp.org_id
1597 AND EXISTS
1598 (SELECT 1
1599 FROM gl_period_statuses glps
1600 WHERE glps.application_id = 200
1601 AND glps.set_of_books_id = ai.set_of_books_id
1602 AND nvl(glps.adjustment_period_flag, 'N') = 'N'
1603 AND ai.gl_date BETWEEN glps.start_date
1604 AND glps.end_date
1605 AND glps.migration_status_code = 'U')
1606 AND xle.application_id = 200
1607 AND xle.event_id = xe.event_id
1608 AND xle.upg_batch_id IS NOT NULL
1609 AND xle.upg_batch_id <> -9999
1610 AND ((fsp.purch_encumbrance_flag = 'Y' AND
1611 aid.match_status_flag = 'A') OR
1612 (fsp.purch_encumbrance_flag = 'N' AND
1613 aid.match_status_flag IN ('A','T')))
1614 AND nvl(aid.posted_flag, 'N') <> 'Y'
1615 AND aid.historical_flag = 'Y';
1616
1617
1618 -- bug 12686836,
1619 -- the R12 logic for creation of the prepay app dists does not
1620 -- prorate the prepayment application or unapplication distributions
1621 -- which are encumbered.
1622 --
1623 -- In case an 11i historical prepayment application or unapplication
1624 -- encumbered but unaccounted invoice distribution is upgraded to R12,
1625 -- this API is unable to create the ap_prepay_history_all/ap_prepay_app_dists
1626 -- data for those prepayment application and unapplication distributions.
1627 --
1628 -- Logic will be added to momentarily set the encumbered flag to 'N',
1629 -- before calling prepay_dist_appl, after that the encumbered flag would
1630 -- be reset to the original value
1631 --
1632 CURSOR prepay_dist_encumbered(p_invoice_id NUMBER) IS
1633 SELECT aid.invoice_distribution_id
1634 FROM ap_invoice_distributions_all aid
1635 WHERE aid.invoice_id = p_invoice_id
1636 AND aid.line_type_lookup_code IN ('PREPAY','REC_TAX','NONREC_TAX')
1637 AND (aid.prepay_distribution_id IS NOT NULL OR
1638 aid.prepay_tax_parent_id IS NOT NULL)
1639 AND NVL(aid.posted_flag, 'N') <> 'Y'
1640 AND aid.encumbered_flag = 'Y'
1641 AND aid.historical_flag = 'Y'
1642 AND aid.bc_event_id IS NULL;
1643
1644
1645 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1646 l_inv_id_tab NUM_TAB;
1647 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1648 l_procedure_name CONSTANT VARCHAR2(30) := 'Prorate_Historical_Dists';
1649 --bug12686836
1650 l_prepay_dist_tab NUM_TAB;
1651 l_invoice_id AP_INVOICES_ALL.Invoice_ID%TYPE;
1652 l_rowcount NUMBER;
1653 l_debug_info LONG;
1654 l_curr_calling_sequence VARCHAR2(2000);
1655
1656 BEGIN
1657
1658 l_curr_calling_sequence := 'AP_ACCOUNTING_PAY_PKG.Get_Base_Amount<-'
1659 || P_calling_sequence;
1660
1661 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1662 l_log_msg := 'Begin of the Procedure Prorate_Historical_Dists';
1663 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1664 END IF;
1665
1666 l_debug_info := 'before the loop for invoices begins';
1667 OPEN Aprvd_UnPrtd_His_Inv;
1668 LOOP
1669 FETCH Aprvd_UnPrtd_His_Inv
1670 BULK COLLECT INTO l_inv_id_tab LIMIT 1000;
1671
1672
1673 IF l_inv_id_tab.COUNT > 0 THEN
1674
1675 l_debug_info := 'more than one invoice fetched for processing';
1676 FOR i IN l_inv_id_tab.FIRST..l_inv_id_tab.LAST LOOP
1677
1678 BEGIN
1679
1680 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1681 l_log_msg := 'Processing the Invoice_id '||l_inv_id_tab(i);
1682 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1683 END IF;
1684
1685 l_debug_info := 'processing the invoice_id '||l_inv_id_tab(i);
1686 l_invoice_id := l_inv_id_tab(i);
1687
1688 --bug12686836
1689 l_debug_info := 'buffering all the encumbered prepayment application and '||
1690 'unapplication distributions into l_prepay_dist_tab ';
1691 OPEN prepay_dist_encumbered(l_invoice_id);
1692 FETCH prepay_dist_encumbered BULK COLLECT INTO l_prepay_dist_tab;
1693 CLOSE prepay_dist_encumbered;
1694
1695 l_debug_info := 'before the savepoint and call to prepay dist appl';
1696
1697 SAVEPOINT Before_Invoice;
1698
1699 l_debug_info := 'Updating the encumbered flag on the invoice distributions '||
1700 'table to ''N'' ';
1701 --bug12686836
1702 FORALL j IN l_prepay_dist_tab.FIRST..l_prepay_dist_tab.LAST
1703 UPDATE ap_invoice_distributions_all AID
1704 SET aid.encumbered_flag = 'N'
1705 WHERE aid.invoice_id = l_invoice_id
1706 AND aid.invoice_distribution_id = l_prepay_dist_tab(j);
1707
1708 l_rowcount := SQL%ROWCOUNT;
1709 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1710 l_log_msg := 'Number of records updated to not encumbered '||l_rowcount;
1711 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1712 END IF;
1713
1714
1715 Ap_Acctg_Prepay_Dist_Pkg.Prepay_Dist_Appl
1716 (l_invoice_id,
1717 l_curr_calling_sequence);
1718
1719 l_debug_info := 'after the call to prepay dist appl, next restoring '||
1720 'encumbered flag for all the historical encumbered '||
1721 'prepayment application and unapplication distributions';
1722
1723 --bug12686836
1724 FORALL j IN l_prepay_dist_tab.FIRST..l_prepay_dist_tab.LAST
1725 UPDATE ap_invoice_distributions_all AID
1726 SET aid.encumbered_flag = 'Y'
1727 WHERE aid.invoice_id = l_invoice_id
1728 AND aid.invoice_distribution_id = l_prepay_dist_tab(j);
1729
1730 l_rowcount := SQL%ROWCOUNT;
1731 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1732 l_log_msg := 'Number of records updated back to encumbered '||l_rowcount;
1733 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1734 END IF;
1735
1736
1737 EXCEPTION
1738 WHEN OTHERS THEN
1739 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1740 l_log_msg := 'Encountered an Exception '||SQLERRM||
1741 'while Processing the Invoice_id '||l_inv_id_tab(i);
1742 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1743 END IF;
1744 ROLLBACK TO Before_Invoice;
1745 END;
1746
1747 END LOOP;
1748 END IF;
1749 EXIT WHEN Aprvd_UnPrtd_His_Inv%NOTFOUND;
1750 END LOOP;
1751 CLOSE Aprvd_UnPrtd_His_Inv;
1752
1753 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1754 l_log_msg := 'End of the Procedure Prorate_Historical_Dists';
1755 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1756 END IF;
1757
1758
1759 EXCEPTION
1760 WHEN OTHERS THEN
1761 IF (SQLCODE <> -20001) THEN
1762 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1763 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1764 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1765 END IF;
1766 APP_EXCEPTION.RAISE_EXCEPTION;
1767 END;
1768
1769 -------------------------------------------------------------------------------
1770 -- Function Is_Final_Event
1771 -- Function to check if the current event is the final pay/prepay event for the invoice
1772 --
1773 -- 1. check if related unaccounted events(other checks or prepayments for the same invoice)
1774 -- exist that haven't been picked up for accounting
1775 -- 2. if not, then if p_xla_event_rec.event_id IS NULL then it's a prepayment application case,
1776 -- as only in the case of invoice validation event_id is stamped after rounding, so
1777 -- get the maximum distribution for rounding
1778 -- 3. if p_xla_event_rec.event_id IS NOT NULL then get the maximum accounting_event_id
1779 -- from among the related payment, payment adjusted and the prepay adjustment events for rounding
1780 --------------------------------------------------------------------------------
1781
1782 FUNCTION Is_Final_Event
1783 (p_inv_rec IN r_invoices_info
1784 ,p_xla_event_rec IN r_xla_event_info
1785 ,p_prepay_app_dist_id IN AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID%TYPE
1786 ,P_calling_sequence IN VARCHAR2
1787 ) RETURN BOOLEAN IS
1788
1789 l_final_event BOOLEAN := FALSE;
1790 l_unacctg_events_exist VARCHAR2(1) := 'Y';
1791 l_max_prepay_app_dist_id AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID%TYPE;
1792 l_acctg_event_id XLA_EVENTS.EVENT_ID%TYPE;
1793
1794 l_curr_calling_sequence VARCHAR2(2000);
1795
1796 l_procedure_name CONSTANT VARCHAR2(30) := 'is_final_event';
1797 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1798
1799 BEGIN
1800
1801 l_curr_calling_sequence := 'AP_ACCOUNTING_PAY_PKG.Is_Final_Payment<-' ||
1802 P_Calling_Sequence;
1803
1804 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1805 l_log_msg := 'Begin of is_final_event function call and parameters are' ||
1806 'p_inv_rec.invoice_id =' || p_inv_rec.invoice_id ||
1807 'p_xla_event_rec.event_type_code =' || p_xla_event_rec.event_type_code ||
1808 'p_prepay_app_dist_id =' || p_prepay_app_dist_id;
1809 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1810 END IF;
1811
1812 SELECT DECODE(COUNT(*), 0, 'N', 'Y')
1813 INTO l_unacctg_events_exist
1814 FROM DUAL
1815 WHERE EXISTS(
1816 SELECT 'unreversed, unaccounted payment not selected for accounting'
1817 FROM ap_payment_history_all aph
1818 , ap_invoice_payments_all aip
1819 , xla_event_types_b xet
1820 , xla_event_types_b xet_rel
1821 , xla_events xe
1822 WHERE aip.invoice_id = p_inv_rec.invoice_id
1823 AND aph.check_id = aip.check_id
1824 AND xe.event_id = aph.accounting_event_id
1825 AND xet.event_type_code = p_xla_event_rec.event_type_code
1826 AND xet_rel.event_class_code = xet.event_class_code
1827 AND xet_rel.event_type_code = xe.event_type_code
1828 AND xet.application_id = 200
1829 AND xet_rel.application_id = 200
1830 AND xe.application_id = 200
1831 AND aph.posted_flag = 'N'
1832 AND NOT EXISTS(
1833 SELECT 'reversed event'
1834 FROM ap_payment_history_all aph_rev
1835 WHERE nvl(aph_rev.related_event_id, aph_rev.accounting_event_id)
1836 = nvl(aph.related_event_id, aph.accounting_event_id)
1837 AND aph_rev.check_id = aph.check_id /* bug12909730 */
1838 AND aph_rev.rev_pmt_hist_id IS NOT NULL)
1839 UNION
1840 -- prepay application
1841 SELECT 'unreversed, unvalidated prepayment application yet to be validated'
1842 FROM ap_invoice_distributions_all aid,
1843 ap_invoices_all ai --added for bug13334090
1844 WHERE aid.invoice_id = p_inv_rec.invoice_id
1845 --AND nvl(aid.match_status_flag, 'N') = 'N' -- commented for bug13334090
1846 AND aid.invoice_id = ai.invoice_id
1847 AND AP_INVOICES_UTILITY_PKG.get_approval_status(ai.invoice_id,
1848 ai.invoice_amount,
1849 ai.payment_status_flag,
1850 ai.invoice_type_lookup_code)
1851 NOT IN ('APPROVED', 'CANCELLED', 'AVAILABLE', 'FULL') -- added for bug13334090
1852 AND aid.prepay_distribution_id IS NOT NULL
1853 AND nvl(aid.reversal_flag, 'N') <> 'Y'
1854 AND nvl(aid.encumbered_flag, 'N') <> 'Y' -- added for bug13334090
1855 AND aid.posted_flag <> 'Y' -- added for bug13334090
1856 AND p_xla_event_rec.event_id IS NOT NULL -- added for bug13334090
1857 UNION
1858 -- prepay application adjustment
1859 SELECT 'unreversed, unaccounted prepay adjustment not selected for accounting'
1860 FROM ap_prepay_history_all apph
1861 WHERE apph.posted_flag = 'N'
1862 AND apph.invoice_id = p_inv_rec.invoice_id
1863 AND apph.invoice_adjustment_event_id IS NOT NULL
1864 AND NOT EXISTS(
1865 SELECT 'reversed event'
1866 FROM ap_invoice_distributions_all aid_rel
1867 WHERE aid_rel.invoice_id = apph.invoice_id
1868 AND aid_rel.accounting_event_id = apph.related_prepay_app_event_id
1869 AND aid_rel.reversal_flag = 'Y'));
1870
1871 IF l_unacctg_events_exist = 'N' THEN
1872 IF p_xla_event_rec.event_id IS NULL THEN -- for prepayment application events
1873 SELECT MAX(AID.INVOICE_DISTRIBUTION_ID)
1874 INTO l_max_prepay_app_dist_id
1875 FROM ap_invoice_distributions_all aid
1876 WHERE aid.invoice_id = p_inv_rec.invoice_id
1877 --bug13334090 - commented below and added conditions on posted_flag and enc flag
1878 --AND aid.match_status_flag = 'S'
1879 AND aid.posted_flag <> 'Y'
1880 AND NVL(aid.encumbered_flag, 'N') <> 'Y'
1881 AND aid.prepay_distribution_id IS NOT NULL
1882 AND NVL(reversal_flag, 'N') <> 'Y';
1883
1884 ELSE -- payments, prepay application adj
1885 SELECT MAX(accounting_event_id)
1886 INTO l_acctg_event_id
1887 FROM
1888 (
1889 -- payment
1890 SELECT MAX(aph.accounting_event_id) accounting_event_id
1891 FROM ap_payment_history_all aph
1892 , ap_invoice_payments_all aip
1893 , xla_event_types_b xet
1894 , xla_event_types_b xet_rel
1895 , xla_events xe
1896 , xla_events_gt xgt
1897 WHERE aip.invoice_id = p_inv_rec.invoice_id
1898 AND aph.check_id = aip.check_id
1899 AND xe.event_id = aph.accounting_event_id
1900 AND xet.event_type_code = p_xla_event_rec.event_type_code
1901 AND xet_rel.event_class_code = xet.event_class_code
1902 AND xet_rel.event_type_code = xe.event_type_code
1903 AND xgt.event_id = xe.event_id
1904 AND xet.application_id = 200
1905 AND xet_rel.application_id = 200
1906 AND xe.application_id = 200
1907 AND aph.posted_flag = 'S'
1908 AND NOT EXISTS(
1909 SELECT 'reversed event'
1910 FROM ap_payment_history_all aph_rev
1911 WHERE nvl(aph_rev.related_event_id, aph_rev.accounting_event_id)
1912 = nvl(aph.related_event_id, aph.accounting_event_id)
1913 AND aph_rev.check_id = aph.check_id /* bug12909730 */
1914 AND aph_rev.rev_pmt_hist_id IS NOT NULL)
1915 UNION
1916 -- prepay adjustment
1917 SELECT MAX(apph.accounting_event_id) accounting_event_id
1918 FROM ap_prepay_history_all apph,
1919 xla_events_gt xgt
1920 WHERE xgt.event_id = apph.accounting_event_id
1921 AND apph.posted_flag = 'S'
1922 AND apph.invoice_id = p_inv_rec.invoice_id
1923 AND apph.invoice_adjustment_event_id IS NOT NULL
1924 AND NOT EXISTS(
1925 SELECT 'reversed event'
1926 FROM ap_invoice_distributions_all aid_rel
1927 WHERE aid_rel.invoice_id = apph.invoice_id
1928 AND aid_rel.accounting_event_id = apph.related_prepay_app_event_id
1929 AND aid_rel.reversal_flag = 'Y'));
1930 END IF;
1931 ELSE
1932 -- final payment rounding shouldn't be executed and return
1933 RETURN FALSE;
1934 END IF;
1935
1936 IF p_xla_event_rec.event_id = l_acctg_event_id
1937 OR p_prepay_app_dist_id = l_max_prepay_app_dist_id THEN
1938
1939 l_final_event := TRUE;
1940 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1941 l_log_msg := 'Final event='||p_xla_event_rec.event_id||
1942 ' OR Final prepay dist='||p_prepay_app_dist_id;
1943 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1944 END IF;
1945 ELSE
1946
1947 l_final_event := FALSE;
1948 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1949 l_log_msg := 'Event ='||p_xla_event_rec.event_id||' is NOT final event'||
1950 ' OR prepay dist='||p_prepay_app_dist_id||' is NOT final prepay dist';
1951 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1952 END IF;
1953 END IF;
1954
1955 RETURN l_final_event;
1956
1957 END Is_Final_Event;
1958
1959 /* Bug10183934 - added below procedure to check if there is any upgrade proration
1960 that particular rounding method is depending on*/
1961 FUNCTION Is_Upgrade_Proration_Exists
1962 ( P_Rounding_Method IN VARCHAR2
1963 ,P_Inv_Rec IN r_invoices_info
1964 ,P_Invoice_Payment_Id IN NUMBER
1965 ,P_Prepay_Appl_Distribution_Id IN NUMBER
1966 ,P_Prepay_Distribution_Id IN NUMBER
1967 ,P_Transaction_Type IN VARCHAR2
1968 ,P_Calling_Sequence IN VARCHAR2
1969 ) RETURN BOOLEAN IS
1970
1971 l_procedure_name CONSTANT VARCHAR2(30) := 'is_upgrade_proration_exists';
1972
1973 l_curr_calling_sequence VARCHAR2(2000);
1974 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1975
1976 l_upg_appl_actg_for_prep_dist NUMBER;
1977 l_upg_pay_actg_for_inv NUMBER;
1978 l_upg_appl_actg_for_inv NUMBER;
1979 l_upg_pay_all_actg_for_inv NUMBER;
1980
1981 BEGIN
1982
1983 l_curr_calling_sequence := 'AP_ACCOUNTING_PAY_PKG.is_upgrade_proration_exists<-' ||P_Calling_Sequence;
1984
1985 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1986 l_log_msg := 'Check is_upgrade_proration_exists for : '|| P_Rounding_Method;
1987 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1988 END IF;
1989
1990
1991 IF P_Rounding_Method=G_FINAL_APPL THEN
1992
1993 -- check for non-reversed, upgrade prepay application accounting w.r.t prepay_distribution_id
1994 select count(1)
1995 into l_upg_appl_actg_for_prep_dist
1996 from ap_invoice_distributions_all aid,
1997 xla_ae_headers xah
1998 where aid.prepay_distribution_id = p_prepay_distribution_id
1999 and nvl(aid.reversal_flag, 'N') <> 'Y'
2000 and aid.accounting_event_id = xah.event_id
2001 and aid.set_of_books_id = xah.ledger_id
2002 and xah.event_type_code IN ('PREPAYMENT APPLIED', 'PREPAYMENT UNAPPLIED')
2003 and xah.accounting_entry_status_code = 'F'
2004 and xah.upg_batch_id is not null
2005 and xah.upg_batch_id <> -9999
2006 and xah.application_id = 200
2007 and rownum = 1;
2008
2009 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2010 l_log_msg := 'l_upg_appl_actg_for_prep_dist = ' ||l_upg_appl_actg_for_prep_dist;
2011 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
2012 END IF;
2013
2014 IF l_upg_appl_actg_for_prep_dist = 0 THEN
2015 return FALSE;
2016 ELSE
2017 return TRUE;
2018 END IF;
2019
2020 ELSIF P_Rounding_Method=G_FINAL_PAY THEN
2021
2022 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2023 l_log_msg := 'p_transaction_type = ' ||p_transaction_type;
2024 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
2025 END IF;
2026
2027 IF (p_transaction_type IN ('PAYMENT CLEARED', 'PAYMENT CLEARING ADJUSTED')) THEN
2028
2029 -- check for non-reversed, upgrade payment clearing accounting w.r.t invoice_id
2030 select count(1)
2031 into l_upg_pay_actg_for_inv
2032 from ap_payment_history_all aph,
2033 ap_system_parameters_all asp,
2034 xla_ae_headers xah
2035 where aph.check_id in
2036 (select aip.check_id
2037 from ap_invoice_payments_all aip
2038 where aip.invoice_id = p_inv_rec.invoice_id
2039 and nvl(aip.reversal_flag, 'N') <> 'Y'
2040 )
2041 and aph.transaction_type IN ('PAYMENT CLEARING',
2042 'PAYMENT UNCLEARING',
2043 'PAYMENT CLEARING ADJUSTED')
2044 and not exists (select 'event reversed'
2045 from ap_payment_history_all aph_rel
2046 where aph_rel.check_id = aph.check_id
2047 and nvl(aph_rel.related_event_id, aph_rel.accounting_event_id) =
2048 nvl(aph.related_event_id, aph.accounting_event_id)
2049 and aph_rel.rev_pmt_hist_id is not null)
2050 and aph.org_id = asp.org_id
2051 and aph.accounting_event_id = xah.event_id
2052 and xah.ledger_id = asp.set_of_books_id
2053 and xah.accounting_entry_status_code = 'F'
2054 and xah.upg_batch_id is not null
2055 and xah.upg_batch_id <> -9999
2056 and xah.application_id = 200
2057 and rownum = 1;
2058
2059 ELSIF (p_transaction_type IN ('PAYMENT MATURED', 'PAYMENT MATURITY ADJUSTED')) THEN
2060
2061 -- check for non-reversed, upgrade payment maturity accounting w.r.t invoice_id
2062 select count(1)
2063 into l_upg_pay_actg_for_inv
2064 from ap_payment_history_all aph,
2065 ap_system_parameters_all asp,
2066 xla_ae_headers xah
2067 where aph.check_id in
2068 (select aip.check_id
2069 from ap_invoice_payments_all aip
2070 where aip.invoice_id = p_inv_rec.invoice_id
2071 and nvl(aip.reversal_flag, 'N') <> 'Y'
2072 )
2073 and aph.transaction_type IN ('PAYMENT MATURITY',
2074 'PAYMENT MATURITY REVERSED',
2075 'PAYMENT MATURITY ADJUSTED')
2076 and not exists (select 'event reversed'
2077 from ap_payment_history_all aph_rel
2078 where aph_rel.check_id = aph.check_id
2079 and nvl(aph_rel.related_event_id, aph_rel.accounting_event_id) =
2080 nvl(aph.related_event_id, aph.accounting_event_id)
2081 and aph_rel.rev_pmt_hist_id is not null)
2082 and aph.org_id = asp.org_id
2083 and aph.accounting_event_id = xah.event_id
2084 and xah.ledger_id = asp.set_of_books_id
2085 and xah.accounting_entry_status_code = 'F'
2086 and xah.upg_batch_id is not null
2087 and xah.upg_batch_id <> -9999
2088 and xah.application_id = 200
2089 and rownum = 1;
2090
2091 ELSE
2092
2093 -- check for non-reversed, upgrade payment accounting w.r.t invoice_id
2094 select count(1)
2095 into l_upg_pay_actg_for_inv
2096 from ap_payment_history_all aph,
2097 ap_system_parameters_all asp,
2098 xla_ae_headers xah
2099 where aph.check_id in
2100 (select aip.check_id
2101 from ap_invoice_payments_all aip
2102 where aip.invoice_id = p_inv_rec.invoice_id
2103 and nvl(aip.reversal_flag, 'N') <> 'Y'
2104 )
2105 and aph.transaction_type IN ('PAYMENT CREATED', 'PAYMENT CANCELLED', 'PAYMENT ADJUSTED',
2106 'MANUAL PAYMENT ADJUSTED', 'UPGRADED MANUAL PMT ADJUSTED',
2107 'REFUND RECORDED', 'REFUND ADJUSTED', 'REFUND CANCELLED',
2108 'MANUAL REFUND ADJUSTED')
2109 and not exists (select 'event reversed'
2110 from ap_payment_history_all aph_rel
2111 where aph_rel.check_id = aph.check_id
2112 and nvl(aph_rel.related_event_id, aph_rel.accounting_event_id) =
2113 nvl(aph.related_event_id, aph.accounting_event_id)
2114 and aph_rel.rev_pmt_hist_id is not null)
2115 and aph.org_id = asp.org_id
2116 and aph.accounting_event_id = xah.event_id
2117 and xah.ledger_id = asp.set_of_books_id
2118 and xah.accounting_entry_status_code = 'F'
2119 and xah.upg_batch_id is not null
2120 and xah.upg_batch_id <> -9999
2121 and xah.application_id = 200
2122 and rownum = 1;
2123
2124 END IF;
2125
2126 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2127 l_log_msg := 'l_upg_pay_actg_for_inv = ' ||l_upg_pay_actg_for_inv;
2128 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
2129 END IF;
2130
2131 -- check for non-reversed, upgrade prepay application accounting w.r.t invoice_id
2132 select count(1)
2133 into l_upg_appl_actg_for_inv
2134 from ap_invoice_distributions_all aid,
2135 xla_ae_headers xah
2136 where aid.invoice_id = p_inv_rec.invoice_id
2137 and nvl(aid.reversal_flag, 'N') <> 'Y'
2138 and aid.accounting_event_id = xah.event_id
2139 and aid.set_of_books_id = xah.ledger_id
2140 and xah.event_type_code IN ('PREPAYMENT APPLIED', 'PREPAYMENT UNAPPLIED')
2141 and xah.accounting_entry_status_code = 'F'
2142 and xah.upg_batch_id is not null
2143 and xah.upg_batch_id <> -9999
2144 and xah.application_id = 200
2145 and rownum = 1;
2146
2147 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2148 l_log_msg := 'l_upg_appl_actg_for_inv = ' ||l_upg_appl_actg_for_inv;
2149 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
2150 END IF;
2151
2152 IF l_upg_pay_actg_for_inv = 0 and l_upg_appl_actg_for_inv = 0 then
2153 return FALSE;
2154 ELSE
2155 return TRUE;
2156 END IF;
2157
2158 ELSIF P_Rounding_Method=G_COMPARE_PAY THEN
2159 -- check for upgrade payment accounting w.r.t invoice_payment_id
2160 select count(1)
2161 into l_upg_pay_all_actg_for_inv
2162 from ap_payment_history_all aph,
2163 ap_system_parameters_all asp,
2164 xla_ae_headers xah
2165 where aph.check_id in
2166 (select aip.check_id
2167 from ap_invoice_payments_all aip
2168 where aip.invoice_id = p_invoice_payment_id
2169 )
2170 and aph.org_id = asp.org_id
2171 and aph.accounting_event_id = xah.event_id
2172 and xah.ledger_id = asp.set_of_books_id
2173 and xah.accounting_entry_status_code = 'F'
2174 and xah.upg_batch_id is not null
2175 and xah.upg_batch_id <> -9999
2176 and xah.application_id = 200
2177 and rownum = 1;
2178
2179 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2180 l_log_msg := 'l_upg_pay_all_actg_for_inv = ' ||l_upg_pay_all_actg_for_inv;
2181 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
2182 END IF;
2183
2184 IF l_upg_pay_all_actg_for_inv = 0 then
2185 return FALSE;
2186 ELSE
2187 return TRUE;
2188 END IF;
2189
2190 ELSIF P_Rounding_Method=G_TOTAL_PAY THEN
2191
2192 return FALSE;
2193
2194 ELSIF P_Rounding_Method=G_TOTAL_APPL THEN
2195
2196 return FALSE;
2197
2198 END IF;
2199
2200 END Is_Upgrade_Proration_Exists;
2201
2202 END AP_ACCOUNTING_PAY_PKG;
[Home] [Help]