[Home] [Help]
PACKAGE BODY: APPS.AP_CREATE_PAY_SCHEDS_PKG
Source
1 PACKAGE BODY AP_CREATE_PAY_SCHEDS_PKG AS
2 /* $Header: apschedb.pls 120.24.12020000.2 2012/07/14 21:01:42 mkmeda ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_CREATE_PAY_SCHEDS_PKG';
4 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
5 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
6 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
7 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
8 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
9 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
10 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
11
12 G_CURRENT_RUNTIME_LEVEL CONSTANT 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(100) := 'AP.PLSQL.AP_CREATE_PAY_SCHEDS_PKG.';
20
21 Function Calc_Due_Date(
22 p_terms_date IN DATE,
23 p_terms_id IN NUMBER,
24 p_calendar IN VARCHAR2,
25 p_sequence_num IN NUMBER,
26 p_calling_sequence IN VARCHAR2) RETURN DATE
27 IS
28 l_due_date DATE;
29 l_curr_calling_sequence VARCHAR2(2000);
30 l_api_name varchar(50);
31 l_debug_info varchar2(2000);
32 BEGIN
33
34 l_api_name := 'Calc_Due_Date';
35
36 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
37 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
38 'AP_CREATE_PAY_SCHEDS_PKG.Calc_Due_Date(+)');
39 END IF;
40
41 l_debug_info := 'Check if p_calendar is nulli p_calendar is '||p_calendar;
42 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
43 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
44 END IF;
45
46
47 IF (p_calendar IS NOT NULL) THEN
48 BEGIN
49 -- bug2639133 added truncate function
50 l_debug_info := 'Get due_date from ap_other_periods';
51 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
52 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
53 END IF;
54
55 SELECT due_date
56 INTO l_due_date
57 FROM ap_other_periods aop
58 WHERE aop.period_type = p_calendar
59 AND aop.module = 'PAYMENT TERMS'
60 AND TRUNC(P_Terms_Date) BETWEEN start_date AND end_date;
61 EXCEPTION
62 WHEN NO_DATA_FOUND THEN
63
64 -- Probably the calendar has not been defined?
65 -- In this case we set the due date to be the same as terms date
66
67 -- bug2639133 added truncate function
68 l_due_date := TRUNC(P_Terms_Date);
69
70 l_debug_info := 'in the exception handler l_due_date is '||l_due_date;
71 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
72 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
73 END IF;
74
75 END;
76
77 ELSE
78 l_debug_info := 'There is no calendar associated with the term line';
79 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
80 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
81 END IF;
82
83 -- bug2682782 deleted needless least function
84 SELECT NVL(fixed_date,
85 (DECODE(ap_terms_lines.due_days,
86 NULL,TO_DATE(TO_CHAR(LEAST(NVL(ap_terms_lines.due_day_of_month,32),
87 TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
88 NVL(ap_terms_lines.due_months_forward,0) +
89 DECODE(ap_terms.due_cutoff_day, NULL, 0,
90 DECODE(GREATEST(NVL(ap_terms.due_cutoff_day, 32),
91 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
92 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
93 , 1, 0)))), 'DD')))) || '-' ||
94 TO_CHAR(ADD_MONTHS(P_Terms_Date,
95 NVL(ap_terms_lines.due_months_forward,0) +
96 DECODE(ap_terms.due_cutoff_day, NULL, 0,
97 DECODE(GREATEST(NVL(ap_terms.due_cutoff_day, 32),
98 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
99 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
100 'MON-RR'),'DD-MON-RR'), /*bugfix:5647464 */
101 trunc(P_Terms_Date) /*bug 8522014*/ + NVL(ap_terms_lines.due_days,0))))
102 INTO l_due_date
103 FROM ap_terms,
104 ap_terms_lines
105 WHERE ap_terms.term_id = P_Terms_Id
106 AND ap_terms.term_id = ap_terms_lines.term_id
107 AND ap_terms_lines.sequence_num = p_sequence_num;
108 END IF;
109
110 l_debug_info := 'In the else part , l_due_date is '||l_due_date;
111 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
112 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
113 END IF;
114
115 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
116 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
117 'AP_CREATE_PAY_SCHEDS_PKG.Calc_Due_Date(-)');
118 END IF;
119
120
121 RETURN(l_due_date);
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 IF (SQLCODE <> -20001) THEN
126 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
127 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
128 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
129 FND_MESSAGE.SET_TOKEN('PARAMETERS',
130 'Terms id = '|| to_char(p_terms_id)
131 || 'Sequence num = ' || to_char(p_sequence_num));
132 END IF;
133 APP_EXCEPTION.RAISE_EXCEPTION;
134 END Calc_Due_Date;
135
136
137 --Bug 4256225
138 PROCEDURE Create_Payment_Schedules
139 (
140 P_Invoice_Id IN number,
141 P_Terms_Id IN number,
142 P_Last_Updated_By IN number,
143 P_Created_By IN number,
144 P_Payment_Priority IN number,
145 P_Batch_Id IN number,
146 P_Terms_Date IN date,
147 P_Invoice_Amount IN number,
148 P_Pay_Curr_Invoice_Amount IN number,
149 P_payment_cross_rate IN number,
150 P_Amount_For_Discount IN number,
151 P_Payment_Method IN varchar2,
152 P_Invoice_Currency IN varchar2,
153 P_Payment_currency IN varchar2,
154 P_calling_sequence IN varchar2
155 ) IS
156
157 l_payment_schedule_index BINARY_INTEGER := 0;
158
159 l_payment_cross_rate ap_payment_schedules.payment_cross_rate%TYPE;
160 l_sequence_num ap_terms_lines.sequence_num%TYPE := 0;
161 l_sign_due_amount ap_terms_lines.due_amount%TYPE;
162 l_sign_remaining_amount ap_terms_lines.due_amount%TYPE;
163 l_calendar ap_terms_lines.calendar%TYPE;
164 l_terms_calendar ap_terms_lines.calendar%TYPE;
165 l_due_date ap_other_periods.due_date%TYPE;
166 l_invoice_sign NUMBER;
167 l_pay_sched_total NUMBER := 0; -- 4537932
168 l_inv_curr_sched_total NUMBER;
169 l_remaining_amount ap_payment_schedules.amount_remaining%TYPE;
170 l_old_remaining_amount ap_payment_schedules.amount_remaining%TYPE;
171 l_ins_gross_amount ap_payment_schedules.gross_amount%TYPE;
172 l_last_line_flag BOOLEAN;
173 l_dummy VARCHAR2(200);
174 current_calling_sequence VARCHAR2(2000);
175 debug_info VARCHAR2(100);
176 l_amount_for_discount ap_invoices.amount_applicable_to_discount%TYPE;
177 l_invoice_type ap_invoices.invoice_type_lookup_code%TYPE;
178 l_min_acc_unit_pay_curr fnd_currencies.minimum_accountable_unit%TYPE;
179 l_precision_pay_curr fnd_currencies.precision%TYPE;
180 l_min_acc_unit_inv_curr fnd_currencies.minimum_accountable_unit%TYPE;
181 l_precision_inv_curr fnd_currencies.precision%TYPE;
182 l_dbi_key_value_list ap_dbi_pkg.r_dbi_key_value_arr;
183
184 l_payment_priority NUMBER; -- Added for Payment Request
185 l_vendor_site_id NUMBER;
186 l_hold_flag varchar2(1);
187
188 --Bug 7357218 Quick Pay and Dispute Resolution Project
189 --Introduced variables for discount calculation
190 l_disc_amt_by_percent NUMBER;
191 l_disc_amt_by_percent_2 NUMBER;
192 l_disc_amt_by_percent_3 NUMBER;
193 l_discount_amount NUMBER;
194 l_discount_amount_2 NUMBER;
195 l_discount_amount_3 NUMBER;
196 l_procedure_name CONSTANT VARCHAR2(30) := 'Create_Payment_Schedules';
197 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
198
199
200
201 T_INVOICE_ID INVOICE_ID;
202 T_PAYMENT_NUM PAYMENT_NUM;
203 T_DUE_DATE DUE_DATE;
204 T_DISCOUNT_DATE DISCOUNT_DATE;
205 T_SECOND_DISCOUNT_DATE SECOND_DISCOUNT_DATE;
206 T_THIRD_DISCOUNT_DATE THIRD_DISCOUNT_DATE;
207 T_LAST_UPDATE_DATE LAST_UPDATE_DATE;
208 T_LAST_UPDATED_BY LAST_UPDATED_BY;
209 T_LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN;
210 T_CREATION_DATE CREATION_DATE;
211 T_CREATED_BY CREATED_BY;
212 T_PAYMENT_CROSS_RATE PAYMENT_CROSS_RATE;
213 T_GROSS_AMOUNT GROSS_AMOUNT;
214 T_INV_CURR_GROSS_AMOUNT INV_CURR_GROSS_AMOUNT;
215 T_DISCOUNT_AMOUNT_AVAILABLE DISCOUNT_AMOUNT_AVAILABLE;
216 T_SECOND_DISC_AMT_AVAILABLE SECOND_DISC_AMT_AVAILABLE;
217 T_THIRD_DISC_AMT_AVAILABLE THIRD_DISC_AMT_AVAILABLE;
218 T_AMOUNT_REMAINING AMOUNT_REMAINING;
219 T_DISCOUNT_AMOUNT_REMAINING DISCOUNT_AMOUNT_REMAINING;
220 T_PAYMENT_PRIORITY PAYMENT_PRIORITY;
221 T_HOLD_FLAG HOLD_FLAG;
222 T_PAYMENT_STATUS_FLAG PAYMENT_STATUS_FLAG;
223 T_BATCH_ID BATCH_ID;
224 T_EXTERNAL_BANK_ACCOUNT_ID EXTERNAL_BANK_ACCOUNT_ID;
225 T_ORG_ID ORG_ID;
226
227 --4393358
228 T_PAYMENT_METHOD_CODE PAYMENT_METHOD_CODE;
229 T_REMITTANCE_MESSAGE1 REMITTANCE_MESSAGE1;
230 T_REMITTANCE_MESSAGE2 REMITTANCE_MESSAGE2;
231 T_REMITTANCE_MESSAGE3 REMITTANCE_MESSAGE3;
232
233 --Third party Payments
234 T_REMIT_TO_SUPPLIER_NAME REMIT_TO_SUPPLIER_NAME;
235 T_REMIT_TO_SUPPLIER_ID REMIT_TO_SUPPLIER_ID;
236 T_REMIT_TO_SUPPLIER_SITE REMIT_TO_SUPPLIER_SITE;
237 T_REMIT_TO_SUPPLIER_SITE_ID REMIT_TO_SUPPLIER_SITE_ID;
238 T_RELATIONSHIP_ID RELATIONSHIP_ID;
239 l_api_name varchar(50); --bug 8991699
240 l_debug_info varchar2(2000); --bug 8991699
241
242 CURSOR c_terms_percent IS
243 SELECT 'Terms are percent type'
244 FROM ap_terms_lines
245 WHERE term_id = P_Terms_Id
246 AND sequence_num = 1
247 AND due_percent IS NOT NULL;
248
249 CURSOR c_terms IS
250 SELECT calendar, sequence_num
251 FROM ap_terms_lines
252 WHERE term_id = p_terms_id
253 ORDER BY sequence_num;
254
255 CURSOR c_amounts IS
256 SELECT SIGN(ABS(P_Invoice_Amount))
257 , SIGN(due_amount)
258 , due_amount
259 , SIGN(ABS(l_remaining_amount) - ABS(due_amount))
260 , ABS(l_remaining_amount) - ABS(due_amount)
261 , calendar
262 FROM ap_terms_lines
263 WHERE term_id = P_Terms_Id
264 AND sequence_num = l_sequence_num;
265 --
266 BEGIN
267 -- Update the calling sequence
268 --
269 current_calling_sequence :=
270 'AP_CREATE_PAY_SCHEDS_PKG.Create_Payment_Schedules<-'||P_calling_sequence;
271
272 l_api_name := 'Create_Payment_Schedules'; --bug 8991699
273
274 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
275 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
276 'AP_CREATE_PAY_SCHEDS_PKG.Create_Payment_Schedules<-'||P_calling_sequence);
277 END IF; --bug 8991699
278
279
280 BEGIN
281 SELECT fc.minimum_accountable_unit,
282 fc.precision
283 INTO l_min_acc_unit_pay_curr,
284 l_precision_pay_curr
285 FROM fnd_currencies fc
286 WHERE fc.currency_code = P_Payment_Currency;
287 EXCEPTION
288 WHEN OTHERS THEN
289 NULL;
290 END;
291
292 -- Select precision and minimum_accountable_unit before loops
293 -- for invoice currency
294
295 BEGIN
296 SELECT fc.minimum_accountable_unit,
297 fc.precision
298 INTO l_min_acc_unit_inv_curr,
299 l_precision_inv_curr
300 FROM fnd_currencies fc
301 WHERE fc.currency_code = P_Invoice_Currency;
302 EXCEPTION
303 WHEN OTHERS THEN
304 NULL;
305 END;
306
307 SELECT invoice_type_lookup_code,
308 vendor_site_id
309 INTO l_invoice_type,
310 l_vendor_site_id
311 FROM ap_invoices
312 WHERE invoice_id = P_Invoice_Id;
313
314
315 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
316 l_log_msg := 'Invoice Type is '|| l_invoice_type;
317 FND_LOG.STRING(G_LEVEL_STATEMENT,
318 G_MODULE_NAME||l_procedure_name,
319 l_log_msg);
320 END IF;
321
322 -- Payment Request: Added the sql statement for payment request
323 IF l_invoice_type <> 'PAYMENT REQUEST' THEN
324
325 SELECT payment_priority
326 INTO l_payment_priority
327 FROM po_vendor_sites
328 WHERE vendor_site_id = l_vendor_site_id;
329
330 END IF;
331
332 l_debug_info := 'Before cursor c_terms_percent: P_Pay_Curr_Invoice_Amount->'||P_Pay_Curr_Invoice_Amount; --bug 8991699
333 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
334 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
335 END IF; --bug 8991699
336
337 debug_info := 'Open cursor c_terms_percent';
338 OPEN c_terms_percent;
339 debug_info := 'Fetch cursor c_terms_percent';
340 FETCH c_terms_percent INTO l_dummy;
341
342 l_payment_cross_rate := P_payment_cross_rate;
343
344 debug_info := 'Convert discount amount to payment currency';
345 l_amount_for_discount := ap_utilities_pkg.ap_round_currency(
346 P_Amount_For_Discount * P_Payment_Cross_Rate,
347 P_Payment_Currency);
348
349 IF c_terms_percent%NOTFOUND THEN
350 /* Terms type is Slab */
351
352 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
353 l_log_msg := 'Terms type is Slab';
354 FND_LOG.STRING(G_LEVEL_STATEMENT,
355 G_MODULE_NAME||l_procedure_name,
356 l_log_msg);
357 END IF;
358 --
359 l_remaining_amount := P_Pay_Curr_Invoice_Amount;
360 --
361 <<slab_loop>>
362 LOOP
363 l_sequence_num := l_sequence_num + 1;
364 l_old_remaining_amount := l_remaining_amount;
365 --
366 debug_info := 'Open cursor c_amounts';
367 OPEN c_amounts;
368 debug_info := 'Fetch cursor c_amounts';
369 FETCH c_amounts INTO l_invoice_sign
370 , l_sign_due_amount
371 , l_ins_gross_amount
372 , l_sign_remaining_amount
373 , l_remaining_amount
374 , l_calendar ;
375 debug_info := 'Close cursor c_amounts';
376 CLOSE c_amounts;
377
378 IF l_invoice_type in ('CREDIT','DEBIT') THEN
379 l_ins_gross_amount := 1 * l_ins_gross_amount;
380 l_remaining_amount := -1 * l_remaining_amount;
381
382 END IF;
383 --
384 IF (
385 (l_sign_remaining_amount <= 0)
386 OR
387 (l_invoice_sign <= 0)
388 OR
389 (l_sign_due_amount = 0)
390 ) THEN
391 l_ins_gross_amount := l_old_remaining_amount;
392 l_last_line_flag := TRUE;
393 END IF;
394
395 --
396 debug_info := 'Calculate Due Date - terms slab type';
397 l_due_date := Calc_Due_Date ( p_terms_date,
398 p_terms_id,
399 l_calendar,
400 l_sequence_num,
401 p_calling_sequence );
402
403 debug_info := 'Insert into ap_payment_schedules';
404 l_payment_schedule_index := l_payment_schedule_index + 1;
405
406 --Bug 7357218 Quick Pay and Dispute Resolution Project
407 debug_info := 'Calculating discount amounts by percent for slab type BEGIN';
408 SELECT DECODE(l_min_acc_unit_pay_curr,
409 NULL, ROUND( l_ins_gross_amount *
410 DECODE(P_Pay_Curr_Invoice_Amount, 0, 0, (l_amount_for_discount/
411 DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
412 P_Pay_Curr_Invoice_Amount))) *
413 NVL(ap_terms_lines.discount_percent,0)/100 ,l_precision_pay_curr),
414 ROUND(( l_ins_gross_amount *
415 DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
416 (l_amount_for_discount/
417 DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
418 P_Pay_Curr_Invoice_Amount))) *
419 NVL(ap_terms_lines.discount_percent,0)/100)
420 / l_min_acc_unit_pay_curr) * l_min_acc_unit_pay_curr)
421 , DECODE(l_min_acc_unit_pay_curr,
422 NULL, ROUND( l_ins_gross_amount *
423 DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
424 (l_amount_for_discount/
425 DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
426 P_Pay_Curr_Invoice_Amount))) *
427 NVL(ap_terms_lines.discount_percent_2,0)/100 ,l_precision_pay_curr),
428 ROUND(( l_ins_gross_amount *
429 DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
430 (l_amount_for_discount/
431 DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
432 P_Pay_Curr_Invoice_Amount))) *
433 NVL(ap_terms_lines.discount_percent_2,0)/100)
434 / l_min_acc_unit_pay_curr) * l_min_acc_unit_pay_curr)
435 , DECODE(l_min_acc_unit_pay_curr,
436 NULL, ROUND( l_ins_gross_amount *
437 DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
438 (l_amount_for_discount/
439 DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
440 P_Pay_Curr_Invoice_Amount))) *
441 NVL(ap_terms_lines.discount_percent_3,0)/100 ,l_precision_pay_curr),
442 ROUND(( l_ins_gross_amount *
443 DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
444 (l_amount_for_discount/
445 DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
446 P_Pay_Curr_Invoice_Amount))) *
447 NVL(ap_terms_lines.discount_percent_3,0)/100)
448 / l_min_acc_unit_pay_curr) * l_min_acc_unit_pay_curr),
449 discount_amount,
450 discount_amount_2,
451 discount_amount_3
452 INTO
453 l_disc_amt_by_percent, l_disc_amt_by_percent_2, l_disc_amt_by_percent_3,
454 l_discount_amount, l_discount_amount_2, l_discount_amount_3
455
456 FROM ap_terms
457 , ap_terms_lines
458 , ap_invoices ai
459 WHERE ap_terms.term_id = ap_terms_lines.term_id
460 AND ap_terms_lines.term_id = P_Terms_Id
461 AND ap_terms_lines.sequence_num = l_sequence_num
462 AND ai.Invoice_Id = P_Invoice_Id;
463
464 --Bug 7357218 Quick Pay and Dispute Resolution Project
465 --Calculating discount amounts by percent for slab type END
466
467
468 --Bug 7357218 Quick Pay and Dispute Resolution Project
469 debug_info := 'Making discount amount negative for credit/debit memos';
470 IF l_invoice_type in ('CREDIT','DEBIT') THEN
471 l_discount_amount := -1 * l_discount_amount;
472 l_discount_amount_2 := -1 * l_discount_amount_2;
473 l_discount_amount_3 := -1 * l_discount_amount_3;
474 END IF;
475
476
477 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
478 l_log_msg := 'Sequence:'|| l_sequence_num ||
479 ' Disc1 by percent:' || l_disc_amt_by_percent ||
480 ' Disc2 by percent:' || l_disc_amt_by_percent_2 ||
481 ' Disc3 by percent:' || l_disc_amt_by_percent_3 ||
482 ' Disc1 by amount:' || l_discount_amount ||
483 ' Disc2 by amount:' || l_discount_amount_2 ||
484 ' Disc3 by amount:' || l_discount_amount_3;
485 FND_LOG.STRING(G_LEVEL_STATEMENT,
486 G_MODULE_NAME||l_procedure_name,
487 l_log_msg);
488 END IF;
489
490 SELECT P_Invoice_Id,
491 l_sequence_num
492 , l_due_date
493 , DECODE(ap_terms_lines.discount_days,
494 NULL,
495 DECODE(ap_terms_lines.discount_day_of_month, NULL, NULL,
496 TO_DATE(TO_CHAR(LEAST(NVL(ap_terms_lines.discount_day_of_month,32),
497 TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS
498 (P_Terms_Date, NVL(ap_terms_lines.discount_months_forward,0) +
499 DECODE(ap_terms.due_cutoff_day, NULL, 0,
500 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
501 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
502 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
503 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
504 , 1, 0)))), 'DD')))) || '-' ||
505 TO_CHAR(ADD_MONTHS(P_Terms_Date,
506 NVL(ap_terms_lines.discount_months_forward,0) +
507 DECODE(ap_terms.due_cutoff_day, NULL, 0,
508 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
509 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
510 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
511 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
512 'MON-RR'),'DD-MON-RR')
513 ),
514 P_Terms_Date + NVL(ap_terms_lines.discount_days,0)
515 )
516 , DECODE(ap_terms_lines.discount_days_2,
517 NULL,DECODE(ap_terms_lines.discount_day_of_month_2,NULL,NULL,
518 TO_DATE(TO_CHAR(LEAST
519 (NVL(ap_terms_lines.discount_day_of_month_2,32),
520 TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
521 NVL(ap_terms_lines.discount_months_forward_2,0) +
522 DECODE(ap_terms.due_cutoff_day, NULL, 0,
523 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
524 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
525 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
526 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
527 , 1, 0)))), 'DD')))) || '-' ||
528 TO_CHAR(ADD_MONTHS(P_Terms_Date,
529 NVL(ap_terms_lines.discount_months_forward_2,0) +
530 DECODE(ap_terms.due_cutoff_day, NULL, 0,
531 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
532 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
533 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
534 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
535 'MON-RR'),'DD-MON-RR')), /*bugfix:5647464 */
536 P_Terms_Date + NVL(ap_terms_lines.discount_days_2,0))
537 , DECODE(ap_terms_lines.discount_days_3,
538 NULL, DECODE(ap_terms_lines.discount_day_of_month_3, NULL,
539 NULL,
540 TO_DATE(TO_CHAR(LEAST
541 (NVL(ap_terms_lines.discount_day_of_month_3,32),
542 TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
543 NVL(ap_terms_lines.discount_months_forward_3,0) +
544 DECODE(ap_terms.due_cutoff_day, NULL, 0,
545 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
546 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
547 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
548 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
549 , 1, 0)))), 'DD')))) || '-' ||
550 TO_CHAR(ADD_MONTHS(P_Terms_Date,
551 NVL(ap_terms_lines.discount_months_forward_3,0) +
552 DECODE(ap_terms.due_cutoff_day, NULL, 0,
553 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
554 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
555 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
556 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
557 'MON-RR'),'DD-MON-RR')), /*Bug14071766 : M0N to MON */
558 P_Terms_Date + NVL(ap_terms_lines.discount_days_3,0))
559 , SYSDATE
560 , P_Last_Updated_By
561 , NULL
562 , SYSDATE
563 , P_Created_By
564 , l_payment_cross_rate
565 , DECODE(l_min_acc_unit_pay_curr,
566 NULL, ROUND(l_ins_gross_amount,
567 l_precision_pay_curr),
568 ROUND(l_ins_gross_amount
569 /l_min_acc_unit_pay_curr)
570 * l_min_acc_unit_pay_curr)
571 ,NULL,
572
573 --Bug 7357218 Quick Pay and Dispute Resolution Project
574 --Considering absolute amount and criteria for all three discounts
575
576 CASE
577 WHEN discount_criteria IS NULL OR discount_criteria = 'H' THEN
578 CASE WHEN abs(nvl(l_discount_amount,0)) > abs(l_disc_amt_by_percent) THEN
579 l_discount_amount
580 ELSE l_disc_amt_by_percent
581 END
582 ELSE CASE WHEN abs(nvl(l_discount_amount,0)) < abs(l_disc_amt_by_percent) THEN
583 l_discount_amount
584 ELSE l_disc_amt_by_percent
585 END
586 END,
587 CASE
588 WHEN discount_criteria_2 IS NULL OR discount_criteria_2 = 'H' THEN
589 CASE WHEN abs(nvl(l_discount_amount_2,0)) > abs(l_disc_amt_by_percent_2) THEN
590 l_discount_amount_2
591 ELSE l_disc_amt_by_percent_2
592 END
593 ELSE CASE WHEN abs(nvl(l_discount_amount_2,0)) < abs(l_disc_amt_by_percent_2) THEN
594 l_discount_amount_2
595 ELSE l_disc_amt_by_percent_2
596 END
597 END,
598 CASE
599 WHEN discount_criteria_3 IS NULL OR discount_criteria_3 = 'H' THEN
600 CASE WHEN abs(nvl(l_discount_amount_3,0)) > abs(l_disc_amt_by_percent_3) THEN
601 l_discount_amount_3
602 ELSE l_disc_amt_by_percent_3
603 END
604 ELSE CASE WHEN abs(nvl(l_discount_amount_3,0)) < abs(l_disc_amt_by_percent_3) THEN
605 l_discount_amount_3
606 ELSE l_disc_amt_by_percent_3
607 END
608 END,
609
610 DECODE(l_min_acc_unit_pay_curr,
611 NULL, ROUND(l_ins_gross_amount,
612 l_precision_pay_curr),
613 ROUND(l_ins_gross_amount
614 /l_min_acc_unit_pay_curr)
615 * l_min_acc_unit_pay_curr)
616 , 0
617 , 'N'
618 , 'N'
619 , P_Batch_Id
620 , NVL(P_Payment_Method, 'CHECK')
621 , ai.external_bank_account_id --4393358
622 ,ai.org_id
623 ,ai.remittance_message1
624 ,ai.remittance_message2
625 ,ai.remittance_message3
626 --third party payments
627 ,ai.remit_to_supplier_name
628 ,ai.remit_to_supplier_id
629 ,ai.remit_to_supplier_site
630 ,ai.remit_to_supplier_site_id
631 ,ai.relationship_id
632 INTO
633 T_INVOICE_ID(l_payment_schedule_index),
634 T_PAYMENT_NUM(l_payment_schedule_index),
635 T_DUE_DATE(l_payment_schedule_index),
636 T_DISCOUNT_DATE(l_payment_schedule_index),
637 T_SECOND_DISCOUNT_DATE(l_payment_schedule_index),
638 T_THIRD_DISCOUNT_DATE(l_payment_schedule_index),
639 T_LAST_UPDATE_DATE(l_payment_schedule_index),
640 T_LAST_UPDATED_BY(l_payment_schedule_index),
641 T_LAST_UPDATE_LOGIN(l_payment_schedule_index),
642 T_CREATION_DATE(l_payment_schedule_index),
643 T_CREATED_BY(l_payment_schedule_index),
644 T_PAYMENT_CROSS_RATE(l_payment_schedule_index),
645 T_GROSS_AMOUNT(l_payment_schedule_index),
646 T_INV_CURR_GROSS_AMOUNT(l_payment_schedule_index),
647 T_DISCOUNT_AMOUNT_AVAILABLE(l_payment_schedule_index),
648 T_SECOND_DISC_AMT_AVAILABLE(l_payment_schedule_index),
649 T_THIRD_DISC_AMT_AVAILABLE(l_payment_schedule_index),
650 T_AMOUNT_REMAINING(l_payment_schedule_index),
651 T_DISCOUNT_AMOUNT_REMAINING(l_payment_schedule_index),
652 T_HOLD_FLAG(l_payment_schedule_index),
653 T_PAYMENT_STATUS_FLAG(l_payment_schedule_index),
654 T_BATCH_ID(l_payment_schedule_index),
655 T_PAYMENT_METHOD_CODE(l_payment_schedule_index),
656 T_EXTERNAL_BANK_ACCOUNT_ID(l_payment_schedule_index),
657 T_ORG_ID(l_payment_schedule_index),
658 T_REMITTANCE_MESSAGE1(l_payment_schedule_index),
659 T_REMITTANCE_MESSAGE2(l_payment_schedule_index),
660 T_REMITTANCE_MESSAGE3(l_payment_schedule_index),
661 --Third Party Payments
662 T_REMIT_TO_SUPPLIER_NAME(l_payment_schedule_index),
663 T_REMIT_TO_SUPPLIER_ID(l_payment_schedule_index),
664 T_REMIT_TO_SUPPLIER_SITE(l_payment_schedule_index),
665 T_REMIT_TO_SUPPLIER_SITE_ID(l_payment_schedule_index),
666 T_RELATIONSHIP_ID(l_payment_schedule_index)
667 FROM ap_terms
668 , ap_terms_lines
669 , ap_invoices ai
670 WHERE ap_terms.term_id = ap_terms_lines.term_id
671 AND ap_terms_lines.term_id = P_Terms_Id
672 AND ap_terms_lines.sequence_num = l_sequence_num
673 AND ai.Invoice_Id = P_Invoice_Id;
674
675 --
676 l_pay_sched_total := l_pay_sched_total +
677 t_gross_amount(l_payment_schedule_index);
678
679 l_debug_info := 'l_pay_sched_total->'||l_pay_sched_total; --bug 8991699
680 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
681 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
682 END IF; --bug 8991699
683
684 IF l_min_acc_unit_inv_curr IS NULL THEN
685
686 t_inv_curr_gross_amount(l_payment_schedule_index) :=
687 ROUND(
688 t_gross_amount(l_payment_schedule_index)/
689 P_Payment_Cross_Rate,
690 l_precision_inv_curr
691 );
692 ELSE
693 t_inv_curr_gross_amount(l_payment_schedule_index):=
694 (ROUND(
695 t_gross_amount(l_payment_schedule_index)/
696 P_Payment_Cross_Rate/
697 l_min_acc_unit_inv_curr)
698 * l_min_acc_unit_inv_curr
699 );
700 END IF;
701
702 l_debug_info := 't_inv_curr_gross_amount(l_payment_schedule_index)->'||t_inv_curr_gross_amount(l_payment_schedule_index); --bug 8991699
703 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
704 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
705 END IF; --bug 8991699
706
707 t_payment_priority(l_payment_schedule_index)
708 := nvl(P_Payment_Priority,l_payment_priority);
709
710 l_inv_curr_sched_total := l_inv_curr_sched_total +
711 t_inv_curr_gross_amount(l_payment_schedule_index);
712
713 IF t_discount_date(l_payment_schedule_index) IS NULL THEN
714 t_discount_amount_available(l_payment_schedule_index) := NULL;
715 END IF;
716
717 IF t_second_discount_date(l_payment_schedule_index) IS NULL THEN
718 t_second_disc_amt_available(l_payment_schedule_index) := NULL;
719 END IF;
720
721 IF t_third_discount_date(l_payment_schedule_index) IS NULL THEN
722 t_third_disc_amt_available(l_payment_schedule_index) := NULL;
723 END IF;
724
725 IF (l_last_line_flag = TRUE) THEN
726 EXIT;
727 END IF;
728
729 l_debug_info := 'l_inv_curr_sched_total->'||l_inv_curr_sched_total; --bug 8991699
730 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
731 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
732 END IF; --bug 8991699
733
734 END LOOP slab_loop;
735 --
736 ELSE
737 -- /* Terms type is Percent */
738
739 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
740 l_log_msg := 'Terms type is Percent';
741 FND_LOG.STRING(G_LEVEL_STATEMENT,
742 G_MODULE_NAME||l_procedure_name,
743 l_log_msg);
744 END IF;
745
746
747 OPEN c_terms;
748
749 LOOP
750 FETCH c_terms INTO l_terms_calendar, l_sequence_num;
751 EXIT WHEN c_terms%NOTFOUND;
752
753 l_debug_info := 'Calculate Due Date - terms type is percent'; --bug 8991699
754 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
755 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
756 END IF; --bug 8991699
757
758 l_due_date := Calc_Due_Date ( p_terms_date,
759 p_terms_id,
760 l_terms_calendar,
761 l_sequence_num,
762 p_calling_sequence);
763
764
765 debug_info := 'Insert into ap_payment_schedules : term type is percent';
766 l_payment_schedule_index := l_payment_schedule_index + 1;
767
768 --Bug 7357218 Quick Pay and Dispute Resolution Project
769 debug_info := 'Calculating discount amounts by percent for Percent type BEGIN';
770 SELECT DECODE(l_min_acc_unit_pay_curr,NULL,
771 ROUND( l_amount_for_discount *
772 NVL(ap_terms_lines.discount_percent,0)/100 *
773 NVL(ap_terms_lines.due_percent, 0)/100, l_precision_pay_curr),
774 ROUND(( l_amount_for_discount *
775 NVL(ap_terms_lines.discount_percent,0)/100 *
776 NVL(ap_terms_lines.due_percent, 0)/100)
777 / l_min_acc_unit_pay_curr)
778 * l_min_acc_unit_pay_curr)
779 , DECODE(l_min_acc_unit_pay_curr,NULL,
780 ROUND( l_amount_for_discount *
781 NVL(ap_terms_lines.discount_percent_2,0)/100 *
782 NVL(ap_terms_lines.due_percent, 0)/100, l_precision_pay_curr),
783 ROUND(( l_amount_for_discount *
784 NVL(ap_terms_lines.discount_percent_2,0)/100 *
785 NVL(ap_terms_lines.due_percent, 0)/100)
786 / l_min_acc_unit_pay_curr)*l_min_acc_unit_pay_curr)
787 , DECODE(l_min_acc_unit_pay_curr,NULL,
788 ROUND( l_amount_for_discount *
789 NVL(ap_terms_lines.discount_percent_3,0)/100 *
790 NVL(ap_terms_lines.due_percent, 0)/100, l_precision_pay_curr),
791 ROUND(( l_amount_for_discount *
792 NVL(ap_terms_lines.discount_percent_3,0)/100 *
793 NVL(ap_terms_lines.due_percent, 0)/100)
794 / l_min_acc_unit_pay_curr)*l_min_acc_unit_pay_curr),
795 discount_amount,
796 discount_amount_2,
797 discount_amount_3
798 INTO
799 l_disc_amt_by_percent, l_disc_amt_by_percent_2, l_disc_amt_by_percent_3,
800 l_discount_amount, l_discount_amount_2, l_discount_amount_3
801
802 FROM ap_terms,
803 ap_terms_lines,
804 ap_invoices ai
805 WHERE ap_terms.term_id = ap_terms_lines.term_id
806 AND ap_terms_lines.term_id = P_Terms_Id
807 AND ap_terms_lines.sequence_num = l_sequence_num
808 AND ai.invoice_id = P_Invoice_Id;
809
810 --Bug 7357218 Quick Pay and Dispute Resolution Project
811 --Calculating discount amounts by percent for Percent type END
812
813 --Bug 7357218 Quick Pay and Dispute Resolution Project
814 debug_info := 'Making discount amount negative for credit/debit memos';
815 IF l_invoice_type in ('CREDIT','DEBIT') THEN
816 l_discount_amount := -1 * l_discount_amount;
817 l_discount_amount_2 := -1 * l_discount_amount_2;
818 l_discount_amount_3 := -1 * l_discount_amount_3;
819 END IF;
820
821
822 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
823 l_log_msg := 'Sequence:'|| l_sequence_num ||
824 ' Disc1 by percent:' || l_disc_amt_by_percent ||
825 ' Disc2 by percent:' || l_disc_amt_by_percent_2 ||
826 ' Disc3 by percent:' || l_disc_amt_by_percent_3 ||
827 ' Disc1 by amount:' || l_discount_amount ||
828 ' Disc2 by amount:' || l_discount_amount_2 ||
829 ' Disc3 by amount:' || l_discount_amount_3;
830 FND_LOG.STRING(G_LEVEL_STATEMENT,
831 G_MODULE_NAME||l_procedure_name,
832 l_log_msg);
833 END IF;
834
835 SELECT P_Invoice_Id,l_sequence_num
836 , l_due_date
837 , DECODE(l_amount_for_discount, NULL, NULL,
838 DECODE(ap_terms_lines.discount_days,
839 NULL, DECODE(ap_terms_lines.discount_day_of_month, NULL, NULL,
840 TO_DATE(TO_CHAR(LEAST(NVL(ap_terms_lines.discount_day_of_month,32),
841 TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS
842 (P_Terms_Date,
843 NVL(ap_terms_lines.discount_months_forward,0) +
844 DECODE(ap_terms.due_cutoff_day, NULL, 0,
845 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
846 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
847 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
848 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
849 , 1, 0)))), 'DD')))) || '-' ||
850 TO_CHAR(ADD_MONTHS(P_Terms_Date,
851 NVL(ap_terms_lines.discount_months_forward,0) +
852 DECODE(ap_terms.due_cutoff_day, NULL, 0,
853 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
854 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
855 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
856 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
857 'MON-RR'),'DD-MON-RR')),
858 P_Terms_Date + NVL(ap_terms_lines.discount_days,0)))
859 , DECODE(l_amount_for_discount, NULL, NULL,
860 DECODE(ap_terms_lines.discount_days_2,
861 NULL,DECODE(ap_terms_lines.discount_day_of_month_2,NULL,NULL,
862 TO_DATE(TO_CHAR(LEAST(
863 NVL(ap_terms_lines.discount_day_of_month_2,32),
864 TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
865 NVL(ap_terms_lines.discount_months_forward_2,0) +
866 DECODE(ap_terms.due_cutoff_day, NULL, 0,
867 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
868 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
869 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
870 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
871 , 1, 0)))), 'DD')))) || '-' ||
872 TO_CHAR(ADD_MONTHS(P_Terms_Date,
873 NVL(ap_terms_lines.discount_months_forward_2,0) +
874 DECODE(ap_terms.due_cutoff_day, NULL, 0,
875 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
876 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
877 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
878 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
879 'MON-RR'),'DD-MON-RR')),
880 P_Terms_Date + NVL(ap_terms_lines.discount_days_2,0)))
881 , DECODE(l_amount_for_discount, NULL, NULL,
882 DECODE(ap_terms_lines.discount_days_3,
883 NULL,DECODE(ap_terms_lines.discount_day_of_month_3,NULL,NULL,
884 TO_DATE(TO_CHAR(LEAST(
885 NVL(ap_terms_lines.discount_day_of_month_3,32),
886 TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
887 NVL(ap_terms_lines.discount_months_forward_3,0) +
888 DECODE(ap_terms.due_cutoff_day, NULL, 0,
889 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
890 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
891 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
892 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
893 , 1, 0)))), 'DD')))) || '-' ||
894 TO_CHAR(ADD_MONTHS(P_Terms_Date,
895 NVL(ap_terms_lines.discount_months_forward_3,0) +
896 DECODE(ap_terms.due_cutoff_day, NULL, 0,
897 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
898 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
899 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
900 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
901 'MON-RR'),'DD-MON-RR')), /*Bug14071766 : M0N to MON */
902 P_Terms_Date + NVL(ap_terms_lines.discount_days_3,0)))
903 , SYSDATE
904 , P_Last_Updated_By
905 , NULL
906 , SYSDATE
907 , P_Created_By
908 , l_payment_cross_rate
909 , DECODE(l_min_acc_unit_pay_curr,NULL,
910 ROUND(P_Pay_Curr_Invoice_Amount *
911 NVL(ap_terms_lines.due_percent,0)/100,l_precision_pay_curr),
912 ROUND((P_Pay_Curr_Invoice_Amount *
913 NVL(ap_terms_lines.due_percent,0)/100)
914 / l_min_acc_unit_pay_curr)
915 * l_min_acc_unit_pay_curr)
916 , NULL ,
917
918 --Bug 7357218 Quick Pay and Dispute Resolution Project
919 --Considering absolute amount and criteria for all three discounts
920
921 CASE
922 WHEN discount_criteria IS NULL OR discount_criteria = 'H' THEN
923 CASE WHEN abs(nvl(l_discount_amount,0)) > abs(l_disc_amt_by_percent) THEN
924 l_discount_amount
925 ELSE l_disc_amt_by_percent
926 END
927 ELSE CASE WHEN abs(nvl(l_discount_amount,0)) < abs(l_disc_amt_by_percent) THEN
928 l_discount_amount
929 ELSE l_disc_amt_by_percent
930 END
931 END,
932 CASE
933 WHEN discount_criteria_2 IS NULL OR discount_criteria_2 = 'H' THEN
934 CASE WHEN abs(nvl(l_discount_amount_2,0)) > abs(l_disc_amt_by_percent_2) THEN
935 l_discount_amount_2
936 ELSE l_disc_amt_by_percent_2
937 END
938 ELSE CASE WHEN abs(nvl(l_discount_amount_2,0)) < abs(l_disc_amt_by_percent_2) THEN
939 l_discount_amount_2
940 ELSE l_disc_amt_by_percent_2
941 END
942 END,
943 CASE
944 WHEN discount_criteria_3 IS NULL OR discount_criteria_3 = 'H' THEN
945 CASE WHEN abs(nvl(l_discount_amount_3,0)) > abs(l_disc_amt_by_percent_3) THEN
946 l_discount_amount_3
947 ELSE l_disc_amt_by_percent_3
948 END
949 ELSE CASE WHEN abs(nvl(l_discount_amount_3,0)) < abs(l_disc_amt_by_percent_3) THEN
950 l_discount_amount_3
951 ELSE l_disc_amt_by_percent_3
952 END
953 END,
954
955 DECODE(l_min_acc_unit_pay_curr,NULL,
956 ROUND( P_Pay_Curr_Invoice_Amount *
957 NVL(ap_terms_lines.due_percent, 0)/100, l_precision_pay_curr),
958 ROUND(( P_Pay_Curr_Invoice_Amount *
959 NVL(ap_terms_lines.due_percent, 0)/100)
960 / l_min_acc_unit_pay_curr)*l_min_acc_unit_pay_curr)
961 , 0
962 , 'N'
963 , 'N'
964 , P_Batch_Id
965 , NVL(P_Payment_Method, 'CHECK')
966 ,ai.external_bank_account_id --4393358
967 ,ai.org_id
968 ,ai.remittance_message1
969 ,ai.remittance_message2
970 ,ai.remittance_message3
971 --third party payments
972 ,ai.remit_to_supplier_name
973 ,ai.remit_to_supplier_id
974 ,ai.remit_to_supplier_site
975 ,ai.remit_to_supplier_site_id
976 ,ai.relationship_id
977 INTO
978 T_INVOICE_ID(l_payment_schedule_index),
979 T_PAYMENT_NUM(l_payment_schedule_index),
980 T_DUE_DATE(l_payment_schedule_index),
981 T_DISCOUNT_DATE(l_payment_schedule_index),
982 T_SECOND_DISCOUNT_DATE(l_payment_schedule_index),
983 T_THIRD_DISCOUNT_DATE(l_payment_schedule_index),
984 T_LAST_UPDATE_DATE(l_payment_schedule_index),
985 T_LAST_UPDATED_BY(l_payment_schedule_index),
986 T_LAST_UPDATE_LOGIN(l_payment_schedule_index),
987 T_CREATION_DATE(l_payment_schedule_index),
988 T_CREATED_BY(l_payment_schedule_index),
989 T_PAYMENT_CROSS_RATE(l_payment_schedule_index),
990 T_GROSS_AMOUNT(l_payment_schedule_index),
991 T_INV_CURR_GROSS_AMOUNT(l_payment_schedule_index),
992 T_DISCOUNT_AMOUNT_AVAILABLE(l_payment_schedule_index),
993 T_SECOND_DISC_AMT_AVAILABLE(l_payment_schedule_index),
994 T_THIRD_DISC_AMT_AVAILABLE(l_payment_schedule_index),
995 T_AMOUNT_REMAINING(l_payment_schedule_index),
996 T_DISCOUNT_AMOUNT_REMAINING(l_payment_schedule_index),
997 T_HOLD_FLAG(l_payment_schedule_index),
998 T_PAYMENT_STATUS_FLAG(l_payment_schedule_index),
999 T_BATCH_ID(l_payment_schedule_index),
1000 T_PAYMENT_METHOD_CODE(l_payment_schedule_index),
1001 T_EXTERNAL_BANK_ACCOUNT_ID(l_payment_schedule_index),
1002 T_ORG_ID(l_payment_schedule_index),
1003 T_REMITTANCE_MESSAGE1(l_payment_schedule_index),
1004 T_REMITTANCE_MESSAGE2(l_payment_schedule_index),
1005 T_REMITTANCE_MESSAGE3(l_payment_schedule_index),
1006 --Third Party Payments
1007 T_REMIT_TO_SUPPLIER_NAME(l_payment_schedule_index),
1008 T_REMIT_TO_SUPPLIER_ID(l_payment_schedule_index),
1009 T_REMIT_TO_SUPPLIER_SITE(l_payment_schedule_index),
1010 T_REMIT_TO_SUPPLIER_SITE_ID(l_payment_schedule_index),
1011 T_RELATIONSHIP_ID(l_payment_schedule_index)
1012 FROM ap_terms
1013 , ap_terms_lines
1014 , ap_invoices ai
1015 WHERE ap_terms.term_id = ap_terms_lines.term_id
1016 AND ap_terms_lines.term_id = P_Terms_Id
1017 AND ap_terms_lines.sequence_num = l_sequence_num
1018 AND ai.invoice_id = P_Invoice_Id;
1019
1020 --
1021 l_pay_sched_total := l_pay_sched_total +
1022 t_gross_amount(l_payment_schedule_index);
1023
1024 l_debug_info := 'l_pay_sched_total2->'||l_pay_sched_total; --bug 8991699
1025 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1026 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1027 END IF; --bug 8991699
1028
1029 IF l_min_acc_unit_inv_curr IS NULL THEN
1030
1031 t_inv_curr_gross_amount(l_payment_schedule_index) :=
1032 ROUND(
1033 t_gross_amount(l_payment_schedule_index)/
1034 P_Payment_Cross_Rate,
1035 l_precision_inv_curr
1036 );
1037 ELSE
1038 t_inv_curr_gross_amount(l_payment_schedule_index) :=
1039 (ROUND(
1040 t_gross_amount(l_payment_schedule_index)/
1041 P_Payment_Cross_Rate/
1042 l_min_acc_unit_inv_curr)
1043 * l_min_acc_unit_inv_curr
1044 );
1045 END IF;
1046
1047 l_debug_info := 't_inv_curr_gross_amount(l_payment_schedule_index)2->'||t_inv_curr_gross_amount(l_payment_schedule_index); --bug 8991699
1048 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1049 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1050 END IF; --bug 8991699
1051
1052 t_payment_priority(l_payment_schedule_index)
1053 := nvl(P_Payment_Priority,l_payment_priority);
1054
1055 l_inv_curr_sched_total := l_inv_curr_sched_total +
1056 t_inv_curr_gross_amount(l_payment_schedule_index);
1057
1058 IF t_discount_date(l_payment_schedule_index) IS NULL THEN
1059 t_discount_amount_available(l_payment_schedule_index) := NULL;
1060 END IF;
1061
1062 IF t_second_discount_date(l_payment_schedule_index) IS NULL THEN
1063 t_second_disc_amt_available(l_payment_schedule_index) := NULL;
1064 END IF;
1065
1066 IF t_third_discount_date(l_payment_schedule_index) IS NULL THEN
1067 t_third_disc_amt_available(l_payment_schedule_index):= NULL;
1068 END IF;
1069
1070 l_debug_info := 'l_inv_curr_sched_total2->'||l_inv_curr_sched_total; --bug 8991699
1071 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1072 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1073 END IF; --bug 8991699
1074
1075 END LOOP;
1076
1077 debug_info := 'Close c_terms';
1078 CLOSE c_terms;
1079
1080 END IF;
1081
1082 debug_info := 'Close cursor c_terms_percent';
1083 CLOSE c_terms_percent;
1084
1085 -- Find out if there is any rounding?
1086 IF (l_pay_sched_total <> P_Pay_Curr_Invoice_Amount) THEN
1087 t_gross_amount(l_payment_schedule_index) :=
1088 t_gross_amount(l_payment_schedule_index) +
1089 (to_number(P_Pay_Curr_Invoice_Amount) -
1090 to_number(l_pay_sched_total));
1091
1092 t_amount_remaining(l_payment_schedule_index) :=
1093 t_amount_remaining(l_payment_schedule_index) +
1094 (to_number(P_Pay_Curr_Invoice_Amount) -
1095 to_number(l_pay_sched_total));
1096 END IF;
1097
1098 IF (l_inv_curr_sched_total <> P_Invoice_Amount) THEN
1099 t_inv_curr_gross_amount(l_payment_schedule_index) :=
1100 t_inv_curr_gross_amount(l_payment_schedule_index) +
1101 (to_number(P_Invoice_Amount) -
1102 to_number(l_inv_curr_sched_total));
1103
1104 END IF;
1105
1106 l_debug_info := 'Insert the Payment Schedule Lines into the table'; --bug 8991699
1107 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1108 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1109 END IF; --bug 8991699
1110
1111 -- Insert the Payment Schedule Lines into the table
1112 FORALL i IN 1..l_payment_schedule_index
1113 INSERT INTO ap_payment_schedules (
1114 INVOICE_ID,
1115 PAYMENT_NUM,
1116 DUE_DATE,
1117 DISCOUNT_DATE,
1118 SECOND_DISCOUNT_DATE,
1119 THIRD_DISCOUNT_DATE,
1120 LAST_UPDATE_DATE,
1121 LAST_UPDATED_BY,
1122 LAST_UPDATE_LOGIN,
1123 CREATION_DATE,
1124 CREATED_BY,
1125 PAYMENT_CROSS_RATE,
1126 GROSS_AMOUNT,
1127 INV_CURR_GROSS_AMOUNT,
1128 DISCOUNT_AMOUNT_AVAILABLE,
1129 SECOND_DISC_AMT_AVAILABLE,
1130 THIRD_DISC_AMT_AVAILABLE,
1131 AMOUNT_REMAINING,
1132 DISCOUNT_AMOUNT_REMAINING,
1133 PAYMENT_PRIORITY,
1134 HOLD_FLAG,
1135 PAYMENT_STATUS_FLAG,
1136 BATCH_ID,
1137 PAYMENT_METHOD_CODE,
1138 EXTERNAL_BANK_ACCOUNT_ID,
1139 ORG_ID,
1140 REMITTANCE_MESSAGE1,
1141 REMITTANCE_MESSAGE2,
1142 REMITTANCE_MESSAGE3,
1143 REMIT_TO_SUPPLIER_NAME,
1144 REMIT_TO_SUPPLIER_ID,
1145 REMIT_TO_SUPPLIER_SITE,
1146 REMIT_TO_SUPPLIER_SITE_ID,
1147 RELATIONSHIP_ID
1148 ) VALUES (
1149 T_INVOICE_ID(i),
1150 T_PAYMENT_NUM(i),
1151 T_DUE_DATE(i),
1152 T_DISCOUNT_DATE(i),
1153 T_SECOND_DISCOUNT_DATE(i),
1154 T_THIRD_DISCOUNT_DATE(i),
1155 T_LAST_UPDATE_DATE(i),
1156 T_LAST_UPDATED_BY(i),
1157 T_LAST_UPDATE_LOGIN(i),
1158 T_CREATION_DATE(i),
1159 T_CREATED_BY(i),
1160 T_PAYMENT_CROSS_RATE(i),
1161 T_GROSS_AMOUNT(i),
1162 T_INV_CURR_GROSS_AMOUNT(i),
1163 T_DISCOUNT_AMOUNT_AVAILABLE(i),
1164 T_SECOND_DISC_AMT_AVAILABLE(i),
1165 T_THIRD_DISC_AMT_AVAILABLE(i),
1166 T_AMOUNT_REMAINING(i),
1167 T_DISCOUNT_AMOUNT_REMAINING(i),
1168 T_PAYMENT_PRIORITY(i),
1169 T_HOLD_FLAG(i),
1170 T_PAYMENT_STATUS_FLAG(i),
1171 T_BATCH_ID(i),
1172 T_PAYMENT_METHOD_CODE(i),
1173 T_EXTERNAL_BANK_ACCOUNT_ID(i),
1174 T_ORG_ID(i),
1175 T_REMITTANCE_MESSAGE1(i),
1176 T_REMITTANCE_MESSAGE2(i),
1177 T_REMITTANCE_MESSAGE3(i),
1178 --Third Party Payments
1179 T_REMIT_TO_SUPPLIER_NAME(i),
1180 T_REMIT_TO_SUPPLIER_ID(i),
1181 T_REMIT_TO_SUPPLIER_SITE(i),
1182 T_REMIT_TO_SUPPLIER_SITE_ID(i),
1183 T_RELATIONSHIP_ID(i)
1184 )
1185 RETURNING payment_num
1186 BULK COLLECT INTO l_dbi_key_value_list;
1187
1188 AP_DBI_PKG.Maintain_DBI_Summary
1189 (p_table_name => 'AP_PAYMENT_SCHEDULES',
1190 p_operation => 'I',
1191 p_key_value1 => P_invoice_id,
1192 p_key_value_list => l_dbi_key_value_list,
1193 p_calling_sequence => current_calling_sequence);
1194
1195
1196 FOR j IN 1..l_payment_schedule_index loop
1197 ap_invoices_pkg.validate_docs_payable(T_INVOICE_ID(j),
1198 T_PAYMENT_NUM(j),
1199 l_hold_flag); --not used
1200 end loop;
1201
1202
1203 --
1204 EXCEPTION
1205 WHEN OTHERS THEN
1206 if (SQLCODE <> -20001) then
1207 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1208 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1209 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1210 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice_Id = '||P_Invoice_Id
1211 ||', Terms_Id = ' ||P_Terms_Id
1212 ||', Last_Updated_By = ' ||P_Last_Updated_By
1213 ||', Created_By = ' ||P_Created_By
1214 ||', Payment_Priority = ' ||P_Payment_Priority
1215 ||', Batch_Id = ' ||P_Batch_Id
1216 ||', Terms_Date = ' ||P_Terms_Date
1217 ||', Invoice_Amount = ' ||P_Invoice_Amount
1218 ||', Amount_for_discount = ' ||P_Amount_For_Discount
1219 ||', Payment_Method = ' ||P_Payment_Method
1220 ||', Currency = ' ||P_invoice_currency
1221 );
1222 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1223 end if;
1224 APP_EXCEPTION.RAISE_EXCEPTION;
1225
1226 END Create_Payment_Schedules;
1227
1228
1229 -- ===================================================================
1230
1231 PROCEDURE AP_Create_From_Terms(
1232 P_Invoice_Id IN NUMBER,
1233 P_Terms_Id IN NUMBER,
1234 P_Last_Updated_By IN NUMBER,
1235 P_Created_By IN NUMBER,
1236 P_Payment_Priority IN NUMBER,
1237 P_Batch_Id IN NUMBER,
1238 P_Terms_Date IN DATE,
1239 P_Invoice_Amount IN NUMBER,
1240 P_Pay_Curr_Invoice_Amount IN NUMBER,
1241 P_Payment_Cross_Rate IN NUMBER,
1242 P_Amount_For_Discount IN NUMBER,
1243 P_Payment_Method IN VARCHAR2,
1244 P_Invoice_Currency IN VARCHAR2,
1245 P_Payment_Currency IN VARCHAR2,
1246 P_calling_sequence IN VARCHAR2)
1247 IS
1248
1249 -- Following is how the input amounts are interpreted:
1250 -- Amount is in ....
1251 -- ====== ==========
1252 -- P_Invoice_amount invoice currency
1253 -- P_Pay_Curr_Invoice_Amount payment currency
1254 -- P_Amount_For_Discount invoice currency
1255 -- All amounts in AP_TERMS_LINES will be interpreted to be in the payment
1256 -- currency
1257
1258 l_payment_cross_rate ap_payment_schedules.payment_cross_rate%TYPE;
1259 l_sequence_num ap_terms_lines.sequence_num%TYPE := 0;
1260 l_sign_due_amount ap_terms_lines.due_amount%TYPE;
1261 l_sign_remaining_amount ap_terms_lines.due_amount%TYPE;
1262 l_calendar ap_terms_lines.calendar%TYPE; -- for payment terms
1263 l_terms_calendar ap_terms_lines.calendar%TYPE; -- for payment terms
1264 l_due_date ap_other_periods.due_date%TYPE; -- for payment terms
1265 l_invoice_sign NUMBER;
1266 l_pay_sched_total NUMBER;
1267 l_inv_curr_sched_total NUMBER;
1268 l_remaining_amount ap_payment_schedules.amount_remaining%TYPE;
1269 l_old_remaining_amount ap_payment_schedules.amount_remaining%TYPE;
1270 l_ins_gross_amount ap_payment_schedules.gross_amount%TYPE;
1271 l_last_line_flag BOOLEAN;
1272 l_dummy VARCHAR2(200);
1273 current_calling_sequence VARCHAR2(2000);
1274 debug_info VARCHAR2(100);
1275 l_amount_for_discount ap_invoices.amount_applicable_to_discount%TYPE;
1276 l_orig_ext_bank_acct_id number; /*bug 1274099*/
1277 l_orig_ext_bank_exists varchar2(1);
1278 --bug 2143298 Included local variable to store invoice type
1279 l_invoice_type ap_invoices.invoice_type_lookup_code%TYPE;
1280 l_Payment_Priority NUMBER(15):=NULL; /*Bug fix:1635550*/
1281
1282 -- Bug Fix: 1952122
1283 l_min_acc_unit_pay_curr fnd_currencies.minimum_accountable_unit%TYPE;
1284 l_precision_pay_curr fnd_currencies.precision%TYPE;
1285 l_min_acc_unit_inv_curr fnd_currencies.minimum_accountable_unit%TYPE;
1286 l_precision_inv_curr fnd_currencies.precision%TYPE;
1287 l_hold_flag varchar2(1);
1288 --Bug 4539462 DBI logging
1289 l_dbi_key_value_list1 ap_dbi_pkg.r_dbi_key_value_arr;
1290 l_dbi_key_value_list2 ap_dbi_pkg.r_dbi_key_value_arr;
1291
1292
1293 --Bug 7357218 Quick Pay and Dispute Resolution Project
1294 --Introduced variables for discount calculation
1295 l_disc_amt_by_percent NUMBER;
1296 l_disc_amt_by_percent_2 NUMBER;
1297 l_disc_amt_by_percent_3 NUMBER;
1298 l_discount_amount NUMBER;
1299 l_discount_amount_2 NUMBER;
1300 l_discount_amount_3 NUMBER;
1301 l_procedure_name CONSTANT VARCHAR2(30) := 'Ap_Create_From_Terms';
1302 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1303
1304 l_api_name varchar(50); --bug 8991699
1305 l_debug_info varchar2(2000); --bug 8991699
1306 l_retained_amount number; --bug 8991699
1307 l_net_of_ret_flag ap_invoices.net_of_retainage_flag%TYPE; --bug 8991699
1308
1309 CURSOR c_terms_percent IS
1310 SELECT 'Terms are percent type'
1311 FROM ap_terms_lines
1312 WHERE term_id = P_Terms_Id
1313 AND sequence_num = 1
1314 AND due_percent IS NOT NULL;
1315
1316 -- add cursor c_terms for calendar based payment terms
1317
1318 CURSOR c_terms IS
1319 SELECT calendar, sequence_num
1320 FROM ap_terms_lines
1321 WHERE term_id = p_terms_id
1322 ORDER BY sequence_num;
1323
1324 --bug 2143298 provides payment terms for credit/debit memo
1325 CURSOR c_amounts IS
1326 SELECT SIGN(ABS(P_Invoice_Amount)) ,
1327 SIGN(due_amount) ,
1328 due_amount ,
1329 SIGN(ABS(l_remaining_amount) - ABS(due_amount)) ,
1330 ABS(l_remaining_amount) - ABS(due_amount) ,
1331 calendar -- change for calendar based payment terms
1332 FROM ap_terms_lines
1333 WHERE term_id = P_Terms_Id
1334 AND sequence_num = l_sequence_num;
1335
1336 CURSOR c_shed_total IS
1337 SELECT SUM(gross_amount),
1338 SIGN(SUM(gross_amount))
1339 FROM ap_payment_schedules
1340 WHERE invoice_id = P_Invoice_Id;
1341
1342 CURSOR c_inv_curr_sched_total IS
1343 SELECT SUM(inv_curr_gross_amount),
1344 SIGN(SUM(inv_curr_gross_amount))
1345 FROM ap_payment_schedules
1346 WHERE invoice_id = P_Invoice_Id;
1347
1348
1349 -- if there is external bank account associated with the invoice associated
1350 -- with the payment schedule we are working on. We have to join
1351 -- using the vendor id because a user could have updated the vendor
1352 -- on the invoice.
1353
1354 CURSOR c_orig_bank_acct_vendor IS --BUG 1274099, checks at the vendor level
1355 SELECT ai.external_bank_account_id, 'Y' --modified for the bug 7261556/7375488
1356 FROM ap_payment_schedules aps,
1357 ap_invoices ai,
1358 --iby_payee_assigned_bankacct_v ipab, /* External Bank Uptake */
1359 po_vendors pv
1360 WHERE ai.invoice_id = p_invoice_id
1361 AND ai.vendor_id = pv.vendor_id -- changed for bug 9531288
1362 --AND pv.party_id = ipab.party_id(+)
1363 AND ai.invoice_id = aps.invoice_id
1364 --AND ipab.ext_bank_account_id = aps.external_bank_account_id
1365 AND EXISTS (SELECT 'x' FROM iby_payee_assigned_bankacct_v IPAB WHERE
1366 pv.party_id = ipab.party_id (+) AND ipab.ext_bank_account_id =
1367 aps.external_bank_account_id ) ;
1368
1369 -- bug 1274099 checks for the vendor site level
1370
1371 CURSOR c_orig_bank_acct_vend_site IS
1372 SELECT ai.external_bank_account_id, 'Y' --modified for the bug 7437597
1373 FROM ap_payment_schedules aps,
1374 ap_invoices ai,
1375 iby_payee_assigned_bankacct_v ipab, /* External Bank Uptake */
1376 po_vendors pv
1377 WHERE ai.invoice_id = p_invoice_id
1378 AND ai.vendor_id = pv.vendor_id
1379 AND pv.party_id = ipab.party_id(+)
1380 AND (ai.vendor_site_id = ipab.supplier_site_id
1381 OR (ipab.supplier_site_id IS NULL
1382 AND ipab.org_id = ai.org_id))
1383 AND ai.invoice_id = aps.invoice_id
1384 AND ipab.ext_bank_account_id = aps.external_bank_account_id;
1385
1386 BEGIN
1387 -- Update the calling sequence
1388 current_calling_sequence := 'AP_CREATE_PAY_SCHEDS_PKG.AP_Create_From_Terms<-'
1389 ||P_calling_sequence;
1390
1391 l_api_name := 'AP_Create_From_Terms';
1392
1393 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1394 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1395 'AP_CREATE_PAY_SCHEDS_PKG.AP_Create_From_Terms<-'||P_calling_sequence);
1396 END IF;
1397
1398 -- Bug Fix:1952122
1399 -- Select precision and minimum_accountable_unit before loops
1400 -- for payment currency
1401
1402 BEGIN
1403 SELECT fc.minimum_accountable_unit, fc.precision
1404 INTO l_min_acc_unit_pay_curr, l_precision_pay_curr
1405 FROM fnd_currencies fc
1406 WHERE fc.currency_code = P_Payment_Currency;
1407 END;
1408
1409 -- Select precision and minimum_accountable_unit before loops
1410 -- for invoice currency
1411
1412 BEGIN
1413 SELECT fc.minimum_accountable_unit, fc.precision
1414 INTO l_min_acc_unit_inv_curr, l_precision_inv_curr
1415 FROM fnd_currencies fc
1416 WHERE fc.currency_code = P_Invoice_Currency;
1417 END;
1418
1419 -- Bug Fix:1635550
1420 -- Added the following unit to the code so that when we recalculate
1421 -- the payment schedules whenever some fields at the invoice header are
1422 -- changed we repopulate the payment priority into all the records
1423 -- at the payment schedule level with value in the payment_priority
1424 -- of the first record
1425
1426 BEGIN
1427 SELECT payment_priority
1428 INTO l_Payment_Priority
1429 FROM ap_payment_schedules
1430 WHERE Invoice_id = P_invoice_id
1431 AND Payment_num=1;
1432
1433 EXCEPTION
1434 WHEN NO_Data_Found THEN
1435 NULL;
1436 END;
1437
1438 /* The code below was added as part of bug 1274099. We are checking
1439 to see if the queries in the 2 cursors defined above get data. If
1440 they do, then we need to store the external_bank_account_id in a
1441 variable. Later in the code we then insert in the payment schedule
1442 record. We have to do this because below the code fix for this bug
1443 we delete the orignal payment schedule and create a new one. */
1444
1445 OPEN c_orig_bank_acct_vendor;
1446 FETCH c_orig_bank_acct_vendor INTO
1447 l_orig_ext_bank_acct_id,
1448 l_orig_ext_bank_exists;
1449
1450 IF c_orig_bank_acct_vendor%NOTFOUND THEN
1451 OPEN c_orig_bank_acct_vend_site;
1452 FETCH c_orig_bank_acct_vend_site INTO
1453 l_orig_ext_bank_acct_id,
1454 l_orig_ext_bank_exists;
1455 CLOSE c_orig_bank_acct_vend_site;
1456 END IF;
1457 CLOSE c_orig_bank_acct_vendor;
1458
1459 -- Delete existing payment schedules since we're creating
1460 -- new payment schedules from payment terms
1461
1462 --Bug 4539462 get list of payment nums first
1463 SELECT payment_num
1464 BULK COLLECT INTO l_dbi_key_value_list1
1465 FROM AP_PAYMENT_SCHEDULES
1466 WHERE invoice_id = P_invoice_id;
1467
1468 debug_info := 'Delete from ap_payment_schedules';
1469 DELETE
1470 FROM ap_payment_schedules
1471 WHERE invoice_id = P_invoice_id;
1472
1473 --Bug 4539462 DBI logging
1474 AP_DBI_PKG.Maintain_DBI_Summary
1475 (p_table_name => 'AP_PAYMENT_SCHEDULES',
1476 p_operation => 'D',
1477 p_key_value1 => P_invoice_id,
1478 p_key_value_list => l_dbi_key_value_list1,
1479 p_calling_sequence => current_calling_sequence);
1480
1481 -- bug 2143298 keep track of invoice type to provide payment terms
1482 -- for credit/debit memo
1483
1484 SELECT invoice_type_lookup_code
1485 INTO l_invoice_type
1486 FROM ap_invoices
1487 WHERE invoice_id = P_Invoice_Id;
1488
1489
1490 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1491 l_log_msg := 'Invoice Type is '|| l_invoice_type;
1492 FND_LOG.STRING(G_LEVEL_STATEMENT,
1493 G_MODULE_NAME||l_procedure_name,
1494 l_log_msg);
1495 END IF;
1496
1497
1498
1499 debug_info := 'Open cursor c_terms_percent';
1500
1501 OPEN c_terms_percent;
1502 debug_info := 'Fetch cursor c_terms_percent';
1503 FETCH c_terms_percent INTO l_dummy;
1504
1505 -- Change for cross currency
1506 -- Set the payment cross rate and amount applicable to discount
1507
1508 l_payment_cross_rate := P_payment_cross_rate;
1509
1510 debug_info := 'Convert discount amount to payment currency';
1511 l_amount_for_discount := ap_utilities_pkg.ap_round_currency(
1512 P_Amount_For_Discount * P_Payment_Cross_Rate,
1513 P_Payment_Currency);
1514
1515 l_debug_info := 'Convert discount amount to payment currency->'||l_amount_for_discount||'P_Pay_Curr_Invoice_Amount->'||P_Pay_Curr_Invoice_Amount; --bug 8991699
1516 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1517 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1518 END IF; --bug 8991699
1519
1520 IF c_terms_percent%NOTFOUND THEN -- Terms type is Slab
1521 debug_info := 'c_terms_percent%NOTFOUND';
1522
1523
1524 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1525 l_log_msg := 'Terms type is Slab';
1526 FND_LOG.STRING(G_LEVEL_STATEMENT,
1527 G_MODULE_NAME||l_procedure_name,
1528 l_log_msg);
1529 END IF;
1530
1531 l_remaining_amount := P_Pay_Curr_Invoice_Amount;
1532
1533 <<slab_loop>>
1534 LOOP
1535 l_sequence_num := l_sequence_num + 1;
1536 l_old_remaining_amount := l_remaining_amount;
1537 --
1538 debug_info := 'Open cursor c_amounts';
1539 OPEN c_amounts;
1540 debug_info := 'Fetch cursor c_amounts';
1541 FETCH c_amounts INTO l_invoice_sign
1542 , l_sign_due_amount
1543 , l_ins_gross_amount
1544 , l_sign_remaining_amount
1545 , l_remaining_amount
1546 , l_calendar ; -- add for payment terms
1547 debug_info := 'Close cursor c_amounts';
1548 CLOSE c_amounts;
1549
1550
1551 -- bug 2143298 For a negative amount invoice the due amount
1552 -- should be reversed. Also the remaining amount also should be
1553 -- reversed because the due amount for the last line will be
1554 -- assigned the remaining amount.
1555
1556 IF l_invoice_type in ('CREDIT','DEBIT') THEN
1557 l_ins_gross_amount := 1 * l_ins_gross_amount;
1558 l_remaining_amount := -1 * l_remaining_amount;
1559 --Bug 8652612
1560 IF (l_ins_gross_amount > 0) THEN
1561 l_ins_gross_amount :=l_remaining_amount - l_ins_gross_amount ;
1562 l_last_line_flag := TRUE;
1563 END IF;
1564
1565 END IF;
1566 --
1567 IF (
1568 (l_sign_remaining_amount <= 0)
1569 OR
1570 (l_invoice_sign <= 0)
1571 OR
1572 (l_sign_due_amount = 0)) THEN
1573 l_ins_gross_amount := l_old_remaining_amount;
1574 l_last_line_flag := TRUE;
1575 END IF;
1576
1577 --
1578 -- Change for MSB Project
1579 -- If the invoice is created by recurring payments get appropriate
1580 -- bank account from there, if one exists. If it isn't a recurring
1581 -- payment get the site's primary supplier bank account for MSB.
1582
1583 l_debug_info := 'Calculate Due Date - terms slab type'; --bug 8991699
1584 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1585 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1586 END IF; --bug 8991699
1587
1588 l_due_date := Calc_Due_Date (
1589 p_terms_date,
1590 p_terms_id,
1591 l_calendar,
1592 l_sequence_num,
1593 p_calling_sequence );
1594
1595 --Bug 7357218 Quick Pay and Dispute Resolution Project
1596
1597 debug_info := 'Calculating discount amounts by percent for slab type BEGIN';
1598
1599 SELECT DECODE(l_min_acc_unit_pay_curr,
1600 NULL, ROUND( l_ins_gross_amount *
1601 DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
1602 (l_amount_for_discount/
1603 DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
1604 P_Pay_Curr_Invoice_Amount))) *
1605 NVL(ap_terms_lines.discount_percent,0)/100 ,
1606 l_precision_pay_curr),
1607 ROUND(( l_ins_gross_amount *
1608 DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
1609 (l_amount_for_discount/
1610 DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
1611 P_Pay_Curr_Invoice_Amount))) *
1612 NVL(ap_terms_lines.discount_percent,0)/100)
1613 / l_min_acc_unit_pay_curr) * l_min_acc_unit_pay_curr)
1614 , DECODE(l_min_acc_unit_pay_curr,
1615 NULL, ROUND( l_ins_gross_amount *
1616 DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
1617 (l_amount_for_discount/
1618 DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
1619 P_Pay_Curr_Invoice_Amount))) *
1620 NVL(ap_terms_lines.discount_percent_2,0)/100 ,
1621 l_precision_pay_curr),
1622 ROUND(( l_ins_gross_amount *
1623 DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
1624 (l_amount_for_discount/
1625 DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
1626 P_Pay_Curr_Invoice_Amount))) *
1627 NVL(ap_terms_lines.discount_percent_2,0)/100)
1628 / l_min_acc_unit_pay_curr) * l_min_acc_unit_pay_curr)
1629 , DECODE(l_min_acc_unit_pay_curr,
1630 NULL, ROUND( l_ins_gross_amount *
1631 DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
1632 (l_amount_for_discount/
1633 DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
1634 P_Pay_Curr_Invoice_Amount))) *
1635 NVL(ap_terms_lines.discount_percent_3,0)/100 ,
1636 l_precision_pay_curr),
1637 ROUND(( l_ins_gross_amount *
1638 DECODE(P_Pay_Curr_Invoice_Amount, 0, 0,
1639 (l_amount_for_discount/
1640 DECODE(P_Pay_Curr_Invoice_Amount, 0, 1,
1641 P_Pay_Curr_Invoice_Amount))) *
1642 NVL(ap_terms_lines.discount_percent_3,0)/100)
1643 / l_min_acc_unit_pay_curr) * l_min_acc_unit_pay_curr),
1644 discount_amount,
1645 discount_amount_2,
1646 discount_amount_3
1647 INTO
1648 l_disc_amt_by_percent, l_disc_amt_by_percent_2, l_disc_amt_by_percent_3,
1649 l_discount_amount, l_discount_amount_2, l_discount_amount_3
1650
1651 FROM ap_terms
1652 , ap_terms_lines
1653 , ap_invoices ai
1654 WHERE ap_terms.term_id = ap_terms_lines.term_id
1655 AND ap_terms_lines.term_id = P_Terms_Id
1656 AND ap_terms_lines.sequence_num = l_sequence_num
1657 AND ai.Invoice_Id = P_Invoice_Id;
1658
1659 --Bug 7357218 Quick Pay and Dispute Resolution Project
1660 --Calculating discount amounts by percent for slab type END
1661
1662 --Bug 7357218 Quick Pay and Dispute Resolution Project
1663 debug_info := 'Making discount amount negative for credit/debit memos';
1664 IF l_invoice_type in ('CREDIT','DEBIT') THEN
1665 l_discount_amount := -1 * l_discount_amount;
1666 l_discount_amount_2 := -1 * l_discount_amount_2;
1667 l_discount_amount_3 := -1 * l_discount_amount_3;
1668 END IF;
1669
1670
1671 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1672 l_log_msg := 'Sequence:'|| l_sequence_num ||
1673 ' Disc1 by percent:' || l_disc_amt_by_percent ||
1674 ' Disc2 by percent:' || l_disc_amt_by_percent_2 ||
1675 ' Disc3 by percent:' || l_disc_amt_by_percent_3 ||
1676 ' Disc1 by amount:' || l_discount_amount ||
1677 ' Disc2 by amount:' || l_discount_amount_2 ||
1678 ' Disc3 by amount:' || l_discount_amount_3;
1679 FND_LOG.STRING(G_LEVEL_STATEMENT,
1680 G_MODULE_NAME||l_procedure_name,
1681 l_log_msg);
1682 END IF;
1683
1684 debug_info := 'Insert into ap_payment_schedules';
1685 /*Bug 45632726.When we are recreating the payment schedules after deleting it
1686 * then the created-by and last-updated-by would be same i.e the person who
1687 * recreated
1688 * payment schedules and not the one who created the invoice*/
1689
1690 INSERT INTO ap_payment_schedules (
1691 invoice_id,
1692 payment_num,
1693 due_date,
1694 discount_date,
1695 second_discount_date,
1696 third_discount_date,
1697 last_update_date,
1698 last_updated_by,
1699 last_update_login,
1700 creation_date,
1701 created_by,
1702 payment_cross_rate,
1703 gross_amount,
1704 discount_amount_available,
1705 second_disc_amt_available,
1706 third_disc_amt_available,
1707 amount_remaining,
1708 discount_amount_remaining,
1709 payment_priority,
1710 hold_flag,
1711 payment_status_flag,
1712 batch_id,
1713 payment_method_code,
1714 external_bank_account_id,
1715 org_id,
1716 remittance_message1,
1717 remittance_message2,
1718 remittance_message3
1719 --third party payments
1720 ,remit_to_supplier_name
1721 ,remit_to_supplier_id
1722 ,remit_to_supplier_site
1723 ,remit_to_supplier_site_id
1724 ,relationship_id)
1725 SELECT
1726 P_Invoice_Id,
1727 l_sequence_num,
1728 l_due_date, -- change for payment terms
1729 DECODE(ap_terms_lines.discount_days,
1730 NULL, DECODE(ap_terms_lines.discount_day_of_month, NULL, NULL,
1731 TO_DATE(TO_CHAR(LEAST(NVL(ap_terms_lines.discount_day_of_month,32),
1732 TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS
1733 (P_Terms_Date, NVL(ap_terms_lines.discount_months_forward,0) +
1734 DECODE(ap_terms.due_cutoff_day, NULL, 0,
1735 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
1736 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
1737 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
1738 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
1739 , 1, 0)))), 'DD')))) || '-' ||
1740 TO_CHAR(ADD_MONTHS(P_Terms_Date,
1741 NVL(ap_terms_lines.discount_months_forward,0) +
1742 DECODE(ap_terms.due_cutoff_day, NULL, 0,
1743 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
1744 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
1745 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
1746 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))), 'MON-RR'),'DD-MON-RR')), /*bugfix:5647464 */
1747 P_Terms_Date + NVL(ap_terms_lines.discount_days,0)),
1748 DECODE(ap_terms_lines.discount_days_2,
1749 NULL,DECODE(ap_terms_lines.discount_day_of_month_2,NULL,NULL,
1750 TO_DATE(TO_CHAR(LEAST
1751 (NVL(ap_terms_lines.discount_day_of_month_2,32),
1752 TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
1753 NVL(ap_terms_lines.discount_months_forward_2,0) +
1754 DECODE(ap_terms.due_cutoff_day, NULL, 0,
1755 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
1756 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
1757 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
1758 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
1759 , 1, 0)))), 'DD')))) || '-' ||
1760 TO_CHAR(ADD_MONTHS(P_Terms_Date,
1761 NVL(ap_terms_lines.discount_months_forward_2,0) +
1762 DECODE(ap_terms.due_cutoff_day, NULL, 0,
1763 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
1764 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
1765 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
1766 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))), 'MON-RR'),'DD-MON-RR')), /*bugfix:5647464 */
1767 P_Terms_Date + NVL(ap_terms_lines.discount_days_2,0)),
1768 DECODE(ap_terms_lines.discount_days_3,
1769 NULL, DECODE(ap_terms_lines.discount_day_of_month_3, NULL,NULL,
1770 TO_DATE(TO_CHAR(LEAST
1771 (NVL(ap_terms_lines.discount_day_of_month_3,32),
1772 TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
1773 NVL(ap_terms_lines.discount_months_forward_3,0) +
1774 DECODE(ap_terms.due_cutoff_day, NULL, 0,
1775 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
1776 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
1777 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
1778 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
1779 , 1, 0)))), 'DD')))) || '-' ||
1780 TO_CHAR(ADD_MONTHS(P_Terms_Date,
1781 NVL(ap_terms_lines.discount_months_forward_3,0) +
1782 DECODE(ap_terms.due_cutoff_day, NULL, 0,
1783 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
1784 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
1785 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
1786 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))), 'MON-RR'),'DD-MON-RR')), /*bugfix:5647464 */
1787 P_Terms_Date + NVL(ap_terms_lines.discount_days_3,0)),
1788 SYSDATE,
1789 P_Last_Updated_By,
1790 NULL,
1791 SYSDATE,
1792 P_Last_Updated_By,--bug4563272
1793 l_payment_cross_rate,
1794 DECODE(l_min_acc_unit_pay_curr,
1795 NULL, ROUND(l_ins_gross_amount, l_precision_pay_curr),
1796 ROUND(l_ins_gross_amount /l_min_acc_unit_pay_curr)
1797 * l_min_acc_unit_pay_curr) ,
1798 --Bug 7357218 Quick Pay and Dispute Resolution Project
1799 --Considering absolute amount and criteria for all three discounts
1800
1801 CASE
1802 WHEN discount_criteria IS NULL OR discount_criteria = 'H' THEN
1803 CASE WHEN abs(nvl(l_discount_amount,0)) > abs(l_disc_amt_by_percent) THEN
1804 l_discount_amount
1805 ELSE l_disc_amt_by_percent
1806 END
1807 ELSE CASE WHEN abs(nvl(l_discount_amount,0)) < abs(l_disc_amt_by_percent) THEN
1808 l_discount_amount
1809 ELSE l_disc_amt_by_percent
1810 END
1811 END,
1812 CASE
1813 WHEN discount_criteria_2 IS NULL OR discount_criteria_2 = 'H' THEN
1814 CASE WHEN abs(nvl(l_discount_amount_2,0)) > abs(l_disc_amt_by_percent_2) THEN
1815 l_discount_amount_2
1816 ELSE l_disc_amt_by_percent_2
1817 END
1818 ELSE CASE WHEN abs(nvl(l_discount_amount_2,0)) < abs(l_disc_amt_by_percent_2) THEN
1819 l_discount_amount_2
1820 ELSE l_disc_amt_by_percent_2
1821 END
1822 END,
1823 CASE
1824 WHEN discount_criteria_3 IS NULL OR discount_criteria_3 = 'H' THEN
1825 CASE WHEN abs(nvl(l_discount_amount_3,0)) > abs(l_disc_amt_by_percent_3) THEN
1826 l_discount_amount_3
1827 ELSE l_disc_amt_by_percent_3
1828 END
1829 ELSE CASE WHEN abs(nvl(l_discount_amount_3,0)) < abs(l_disc_amt_by_percent_3) THEN
1830 l_discount_amount_3
1831 ELSE l_disc_amt_by_percent_3
1832 END
1833 END,
1834 DECODE(l_min_acc_unit_pay_curr,
1835 NULL, ROUND(l_ins_gross_amount, l_precision_pay_curr),
1836 ROUND(l_ins_gross_amount /l_min_acc_unit_pay_curr)
1837 * l_min_acc_unit_pay_curr),
1838 0,
1839 NVL(l_Payment_Priority,P_Payment_Priority),
1840 'N',
1841 'N',
1842 P_Batch_Id,
1843 NVL(P_Payment_Method, 'CHECK'),
1844 /*commented for bug 5332569
1845 DECODE(l_orig_ext_bank_exists, 'Y',
1846 l_orig_ext_bank_acct_id, --1274099
1847 ai.external_bank_account_id), --4393358
1848 */
1849
1850 -- Added for Bug 5332569 for inserting external_bank_account_id correctly
1851 DECODE(l_orig_ext_bank_exists,
1852 'Y', l_orig_ext_bank_acct_id,
1853 DECODE(ai.source,
1854 'RECURRING INVOICE', arp.external_bank_account_id,
1855 ai.external_bank_account_id)),
1856
1857 ai.org_id,
1858 ai.remittance_message1,
1859 ai.remittance_message2,
1860 ai.remittance_message3
1861 --third party payments
1862 ,ai.remit_to_supplier_name
1863 ,ai.remit_to_supplier_id
1864 ,ai.remit_to_supplier_site
1865 ,ai.remit_to_supplier_site_id
1866 ,ai.relationship_id
1867 FROM ap_terms,
1868 ap_terms_lines,
1869 ap_invoices ai,
1870 ap_recurring_payments arp --bug 5332569
1871 WHERE ap_terms.term_id = ap_terms_lines.term_id
1872 AND ap_terms_lines.term_id = P_Terms_Id
1873 AND ap_terms_lines.sequence_num = l_sequence_num
1874 AND ai.Invoice_Id = P_Invoice_Id
1875 AND ai.recurring_payment_id = arp.recurring_payment_id(+); --bug 5332569
1876
1877 l_debug_info := 'After Insert into ap_payment_schedules- term type is not percent'; --bug 8991699
1878 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1879 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info); --bug 8991699
1880 END IF;
1881
1882 --Bug 4539462 DBI logging
1883 AP_DBI_PKG.Maintain_DBI_Summary
1884 (p_table_name => 'AP_PAYMENT_SCHEDULES',
1885 p_operation => 'I',
1886 p_key_value1 => P_invoice_id,
1887 p_key_value2 => l_sequence_num,
1888 p_calling_sequence => current_calling_sequence);
1889
1890 -- If we are at the last line then break out NOCOPY the loop
1891
1892 IF (l_last_line_flag = TRUE) THEN
1893 EXIT;
1894 END IF;
1895 END LOOP slab_loop;
1896 --
1897 ELSE
1898 -- /* Terms type is Percent */
1899
1900 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1901 l_log_msg := 'Terms type is Percent';
1902 FND_LOG.STRING(G_LEVEL_STATEMENT,
1903 G_MODULE_NAME||l_procedure_name,
1904 l_log_msg);
1905 END IF;
1906
1907 OPEN c_terms;
1908 LOOP
1909 FETCH c_terms INTO l_terms_calendar,
1910 l_sequence_num;
1911 EXIT WHEN c_terms%NOTFOUND;
1912
1913 -- Change for MSB Project
1914 -- If the invoice is created by recurring payments get appropriate
1915 -- bank account from there, if one exists. If it isn't a recurring
1916 -- payment get the site's primary supplier bank account for MSB.
1917
1918 -- Terms type is Percent
1919
1920 l_debug_info := 'Calculate Due Date - terms type is percent'; --bug 8991699
1921 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1922 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1923 END IF; --bug 8991699
1924
1925 l_due_date := Calc_Due_Date (
1926 p_terms_date,
1927 p_terms_id,
1928 l_terms_calendar,
1929 l_sequence_num,
1930 p_calling_sequence); -- add for payment terms
1931
1932 debug_info := 'l_due_date'||to_char(l_due_date,'dd-mm-yyyy');
1933 --Bug 7357218 Quick Pay and Dispute Resolution Project
1934
1935 debug_info := 'Calculating discount amounts by percent for pecent type BEGIN';
1936
1937 SELECT DECODE(l_min_acc_unit_pay_curr,NULL,
1938 ROUND( l_amount_for_discount *
1939 NVL(ap_terms_lines.discount_percent,0)/100 *
1940 NVL(ap_terms_lines.due_percent, 0)/100,
1941 l_precision_pay_curr),
1942 ROUND(( l_amount_for_discount *
1943 NVL(ap_terms_lines.discount_percent,0)/100 *
1944 NVL(ap_terms_lines.due_percent, 0)/100)
1945 / l_min_acc_unit_pay_curr)
1946 * l_min_acc_unit_pay_curr)
1947 , DECODE(l_min_acc_unit_pay_curr,NULL,
1948 ROUND( l_amount_for_discount *
1949 NVL(ap_terms_lines.discount_percent_2,0)/100 *
1950 NVL(ap_terms_lines.due_percent, 0)/100,
1951 l_precision_pay_curr),
1952 ROUND(( l_amount_for_discount *
1953 NVL(ap_terms_lines.discount_percent_2,0)/100 *
1954 NVL(ap_terms_lines.due_percent, 0)/100)
1955 / l_min_acc_unit_pay_curr)
1956 * l_min_acc_unit_pay_curr)
1957 ,DECODE(l_min_acc_unit_pay_curr,NULL,
1958 ROUND( l_amount_for_discount *
1959 NVL(ap_terms_lines.discount_percent_3,0)/100 *
1960 NVL(ap_terms_lines.due_percent, 0)/100,
1961 l_precision_pay_curr),
1962 ROUND(( l_amount_for_discount *
1963 NVL(ap_terms_lines.discount_percent_3,0)/100 *
1964 NVL(ap_terms_lines.due_percent, 0)/100)
1965 / l_min_acc_unit_pay_curr)
1966 * l_min_acc_unit_pay_curr),
1967 discount_amount,
1968 discount_amount_2,
1969 discount_amount_3
1970 INTO
1971 l_disc_amt_by_percent, l_disc_amt_by_percent_2, l_disc_amt_by_percent_3,
1972 l_discount_amount, l_discount_amount_2, l_discount_amount_3
1973
1974 FROM ap_terms,
1975 ap_terms_lines,
1976 ap_invoices ai,
1977 ap_recurring_payments arp
1978 WHERE ap_terms.term_id = ap_terms_lines.term_id
1979 AND ap_terms_lines.term_id = P_Terms_Id
1980 AND ap_terms_lines.sequence_num = l_sequence_num
1981 AND ai.Invoice_Id = P_Invoice_Id
1982 AND ai.recurring_payment_id = arp.recurring_payment_id(+);
1983
1984 --Bug 7357218 Quick Pay and Dispute Resolution Project
1985 --Calculating discount amounts by percent for percent type END
1986
1987 --Bug 7357218 Quick Pay and Dispute Resolution Project
1988 debug_info := 'Making discount amount negative for credit/debit memos';
1989 IF l_invoice_type in ('CREDIT','DEBIT') THEN
1990 l_discount_amount := -1 * l_discount_amount;
1991 l_discount_amount_2 := -1 * l_discount_amount_2;
1992 l_discount_amount_3 := -1 * l_discount_amount_3;
1993 END IF;
1994
1995
1996 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1997 l_log_msg := 'Sequence:'|| l_sequence_num ||
1998 ' Disc1 by percent:' || l_disc_amt_by_percent ||
1999 ' Disc2 by percent:' || l_disc_amt_by_percent_2 ||
2000 ' Disc3 by percent:' || l_disc_amt_by_percent_3 ||
2001 ' Disc1 by amount:' || l_discount_amount ||
2002 ' Disc2 by amount:' || l_discount_amount_2 ||
2003 ' Disc3 by amount:' || l_discount_amount_3;
2004 FND_LOG.STRING(G_LEVEL_STATEMENT,
2005 G_MODULE_NAME||l_procedure_name,
2006 l_log_msg);
2007 END IF;
2008
2009 debug_info := 'Insert into ap_payment_schedules : term type is percent';
2010
2011
2012 INSERT INTO ap_payment_schedules (
2013 invoice_id,
2014 payment_num,
2015 due_date,
2016 discount_date,
2017 second_discount_date,
2018 third_discount_date,
2019 last_update_date,
2020 last_updated_by,
2021 last_update_login,
2022 creation_date,
2023 created_by,
2024 payment_cross_rate,
2025 gross_amount,
2026 discount_amount_available,
2027 second_disc_amt_available,
2028 third_disc_amt_available,
2029 amount_remaining,
2030 discount_amount_remaining,
2031 payment_priority,
2032 hold_flag,
2033 payment_status_flag,
2034 batch_id,
2035 payment_method_code,
2036 external_bank_account_id,
2037 org_id,
2038 remittance_message1,
2039 remittance_message2,
2040 remittance_message3
2041 --third party payments
2042 ,remit_to_supplier_name
2043 ,remit_to_supplier_id
2044 ,remit_to_supplier_site
2045 ,remit_to_supplier_site_id
2046 ,relationship_id)
2047 SELECT
2048 P_Invoice_Id,
2049 l_sequence_num, -- ap_terms_lines.sequence_num
2050 l_due_date, -- change for payment terms
2051 DECODE(l_amount_for_discount, NULL, NULL,
2052 DECODE(ap_terms_lines.discount_days, NULL,
2053 DECODE(ap_terms_lines.discount_day_of_month,
2054 NULL, NULL, TO_DATE(TO_CHAR(LEAST(NVL(
2055 ap_terms_lines.discount_day_of_month,32),
2056 TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS
2057 (P_Terms_Date,
2058 NVL(ap_terms_lines.discount_months_forward,0) +
2059 DECODE(ap_terms.due_cutoff_day, NULL, 0,
2060 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
2061 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
2062 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
2063 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
2064 , 1, 0)))), 'DD')))) || '-' ||
2065 TO_CHAR(ADD_MONTHS(P_Terms_Date,
2066 NVL(ap_terms_lines.discount_months_forward,0) +
2067 DECODE(ap_terms.due_cutoff_day, NULL, 0,
2068 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
2069 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
2070 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
2071 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
2072 'MON-RR'),'DD-MON-RR')), /*bugfix:5647464 */
2073 P_Terms_Date + NVL(ap_terms_lines.discount_days,0))),
2074 DECODE(l_amount_for_discount, NULL, NULL,
2075 DECODE(ap_terms_lines.discount_days_2,
2076 NULL,DECODE(ap_terms_lines.discount_day_of_month_2,NULL,NULL,
2077 TO_DATE(TO_CHAR(LEAST(
2078 NVL(ap_terms_lines.discount_day_of_month_2,32),
2079 TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
2080 NVL(ap_terms_lines.discount_months_forward_2,0) +
2081 DECODE(ap_terms.due_cutoff_day, NULL, 0,
2082 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
2083 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
2084 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
2085 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
2086 , 1, 0)))), 'DD')))) || '-' ||
2087 TO_CHAR(ADD_MONTHS(P_Terms_Date,
2088 NVL(ap_terms_lines.discount_months_forward_2,0) +
2089 DECODE(ap_terms.due_cutoff_day, NULL, 0,
2090 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
2091 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
2092 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
2093 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
2094 'MON-RR'),'DD-MON-RR')), /*bugfix:5647464 */
2095 P_Terms_Date + NVL(ap_terms_lines.discount_days_2,0))),
2096 DECODE(l_amount_for_discount, NULL, NULL,
2097 DECODE(ap_terms_lines.discount_days_3,
2098 NULL,DECODE(ap_terms_lines.discount_day_of_month_3,NULL,NULL,
2099 TO_DATE(TO_CHAR(LEAST(
2100 NVL(ap_terms_lines.discount_day_of_month_3,32),
2101 TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(P_Terms_Date,
2102 NVL(ap_terms_lines.discount_months_forward_3,0) +
2103 DECODE(ap_terms.due_cutoff_day, NULL, 0,
2104 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
2105 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date), 'DD'))),
2106 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
2107 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))
2108 , 1, 0)))), 'DD')))) || '-' ||
2109 TO_CHAR(ADD_MONTHS(P_Terms_Date,
2110 NVL(ap_terms_lines.discount_months_forward_3,0) +
2111 DECODE(ap_terms.due_cutoff_day, NULL, 0,
2112 DECODE(GREATEST(LEAST(NVL(ap_terms.due_cutoff_day, 32),
2113 TO_NUMBER(TO_CHAR(LAST_DAY(P_Terms_Date),'DD'))),
2114 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD'))),
2115 TO_NUMBER(TO_CHAR(P_Terms_Date, 'DD')), 1, 0))),
2116 'MON-RR'),'DD-MON-RR')), /*bugfix:5647464 */
2117 P_Terms_Date + NVL(ap_terms_lines.discount_days_3,0))),
2118 SYSDATE,
2119 P_Last_Updated_By,
2120 NULL,
2121 SYSDATE,
2122 P_Last_Updated_By,--bug4563272
2123 l_payment_cross_rate,
2124 DECODE(l_min_acc_unit_pay_curr,NULL,
2125 ROUND(P_Pay_Curr_Invoice_Amount *
2126 NVL(ap_terms_lines.due_percent,0)/100,
2127 l_precision_pay_curr),
2128 ROUND((P_Pay_Curr_Invoice_Amount *
2129 NVL(ap_terms_lines.due_percent,0)/100)
2130 / l_min_acc_unit_pay_curr)
2131 * l_min_acc_unit_pay_curr),
2132
2133 --Bug 7357218 Quick Pay and Dispute Resolution Project
2134 --Considering absolute amount and criteria for all three discounts
2135
2136 CASE
2137 WHEN discount_criteria IS NULL OR discount_criteria = 'H' THEN
2138 CASE WHEN abs(nvl(l_discount_amount,0)) > abs(l_disc_amt_by_percent) THEN
2139 l_discount_amount
2140 ELSE l_disc_amt_by_percent
2141 END
2142 ELSE CASE WHEN abs(nvl(l_discount_amount,0)) < abs(l_disc_amt_by_percent) THEN
2143 l_discount_amount
2144 ELSE l_disc_amt_by_percent
2145 END
2146 END,
2147 CASE
2148 WHEN discount_criteria_2 IS NULL OR discount_criteria_2 = 'H' THEN
2149 CASE WHEN abs(nvl(l_discount_amount_2,0)) > abs(l_disc_amt_by_percent_2) THEN
2150 l_discount_amount_2
2151 ELSE l_disc_amt_by_percent_2
2152 END
2153 ELSE CASE WHEN abs(nvl(l_discount_amount_2,0)) < abs(l_disc_amt_by_percent_2) THEN
2154 l_discount_amount_2
2155 ELSE l_disc_amt_by_percent_2
2156 END
2157 END,
2158 CASE
2159 WHEN discount_criteria_3 IS NULL OR discount_criteria_3 = 'H' THEN
2160 CASE WHEN abs(nvl(l_discount_amount_3,0)) > abs(l_disc_amt_by_percent_3) THEN
2161 l_discount_amount_3
2162 ELSE l_disc_amt_by_percent_3
2163 END
2164 ELSE CASE WHEN abs(nvl(l_discount_amount_3,0)) < abs(l_disc_amt_by_percent_3) THEN
2165 l_discount_amount_3
2166 ELSE l_disc_amt_by_percent_3
2167 END
2168 END,
2169
2170 DECODE(l_min_acc_unit_pay_curr,NULL,
2171 ROUND( P_Pay_Curr_Invoice_Amount *
2172 NVL(ap_terms_lines.due_percent, 0)/100,
2173 l_precision_pay_curr),
2174 ROUND(( P_Pay_Curr_Invoice_Amount *
2175 NVL(ap_terms_lines.due_percent, 0)/100)
2176 / l_min_acc_unit_pay_curr)
2177 * l_min_acc_unit_pay_curr),
2178 0,
2179 NVL(l_Payment_Priority,P_Payment_priority),
2180 'N',
2181 'N',
2182 P_Batch_Id,
2183 NVL(P_Payment_Method, 'CHECK'),
2184 /*commented for bug 5332569
2185 DECODE(l_orig_ext_bank_exists, 'Y',
2186 l_orig_ext_bank_acct_id, --1274099
2187 ai.external_bank_account_id), --4393358
2188 */
2189
2190 -- Added for Bug 5332569 for inserting external_bank_account_id correctly
2191 DECODE(l_orig_ext_bank_exists,
2192 'Y', l_orig_ext_bank_acct_id,
2193 DECODE(ai.source,
2194 'RECURRING INVOICE', arp.external_bank_account_id,
2195 ai.external_bank_account_id)),
2196
2197 ai.org_id,
2198 ai.remittance_message1,
2199 ai.remittance_message2,
2200 ai.remittance_message3
2201 --third party payments
2202 ,ai.remit_to_supplier_name
2203 ,ai.remit_to_supplier_id
2204 ,ai.remit_to_supplier_site
2205 ,ai.remit_to_supplier_site_id
2206 ,ai.relationship_id
2207 FROM ap_terms,
2208 ap_terms_lines,
2209 ap_invoices ai,
2210 ap_recurring_payments arp
2211 WHERE ap_terms.term_id = ap_terms_lines.term_id
2212 AND ap_terms_lines.term_id = P_Terms_Id
2213 AND ap_terms_lines.sequence_num = l_sequence_num
2214 AND ai.invoice_id = P_Invoice_Id
2215 AND ai.recurring_payment_id = arp.recurring_payment_id(+);
2216
2217 l_debug_info := 'l_due_date'||to_char(l_due_date,'dd-mm-yyyy')||'Insert into ap_payment_schedules : term type is percent';
2218 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2219 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2220 END IF;
2221
2222 --Bug 4539462 DBI logginG
2223 AP_DBI_PKG.Maintain_DBI_Summary
2224 (p_table_name => 'AP_PAYMENT_SCHEDULES',
2225 p_operation => 'I',
2226 p_key_value1 => P_invoice_id,
2227 p_key_value2 => l_sequence_num,
2228 p_calling_sequence => current_calling_sequence);
2229
2230 END LOOP;
2231
2232 debug_info := 'Close c_terms';
2233
2234 debug_info := 'Open cursor c_shed_total';
2235 OPEN c_shed_total;
2236 debug_info := 'Fetch cursor c_shed_total';
2237 FETCH c_shed_total INTO l_pay_sched_total,
2238 l_invoice_sign;
2239
2240 l_debug_info := 'Fetch cursor c_shed_total'||l_pay_sched_total||'P_Pay_Curr_Invoice_Amount->'||P_Pay_Curr_Invoice_Amount; --bug 8991699
2241 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2242 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2243 END IF; --bug 8991699
2244
2245 debug_info := 'Close cursor c_shed_total';
2246 CLOSE c_shed_total;
2247
2248 -- Adjust Payment Schedules for rounding errors
2249
2250 IF (l_pay_sched_total <> P_Pay_Curr_Invoice_Amount) THEN
2251
2252 l_debug_info := 'Update ap_payment_schedules - set gross_amount->P_Pay_Curr_Invoice_Amount:'||P_Pay_Curr_Invoice_Amount||
2253 'l_pay_sched_total->'||l_pay_sched_total; --bug 8991699
2254 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2255 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2256 END IF; --bug 8991699
2257
2258 UPDATE AP_PAYMENT_SCHEDULES
2259 SET gross_amount = gross_amount +
2260 TO_NUMBER(P_Pay_Curr_Invoice_Amount) -
2261 TO_NUMBER(l_pay_sched_total),
2262 amount_remaining = amount_remaining +
2263 TO_NUMBER(P_Pay_Curr_Invoice_Amount) -
2264 TO_NUMBER(l_pay_sched_total)
2265 WHERE invoice_id = P_Invoice_Id
2266 AND payment_num = (SELECT MAX(payment_num)
2267 FROM ap_payment_schedules
2268 WHERE invoice_id = P_Invoice_Id);
2269 END IF;
2270 END IF;
2271
2272 debug_info := 'Close cursor c_terms_percent';
2273 CLOSE c_terms_percent;
2274
2275 debug_info := 'Update ap_payment_schedules - set discount amounts';
2276
2277 UPDATE ap_payment_schedules
2278 SET discount_amount_available = DECODE(discount_date, '', '',
2279 discount_amount_available),
2280 second_disc_amt_available = DECODE(second_discount_date, '', '',
2281 second_disc_amt_available),
2282 third_disc_amt_available = DECODE(third_discount_date, '', '',
2283 third_disc_amt_available)
2284 WHERE invoice_id = P_Invoice_Id
2285 RETURNING payment_num
2286 BULK COLLECT INTO l_dbi_key_value_list2;
2287
2288 --Bug 4539462 DBI logging
2289 AP_DBI_PKG.Maintain_DBI_Summary
2290 (p_table_name => 'AP_PAYMENT_SCHEDULES',
2291 p_operation => 'U',
2292 p_key_value1 => P_invoice_id,
2293 p_key_value_list => l_dbi_key_value_list2,
2294 p_calling_sequence => current_calling_sequence);
2295
2296
2297 -- Change for cross currency
2298 -- Populate the inv_curr_gross_amount
2299
2300 l_debug_info := 'Update ap_payment_schedules - set inv_curr_gross_amount'; --bug 8991699
2301 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2302 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2303 END IF; --bug 8991699
2304
2305 IF l_min_acc_unit_inv_curr IS NULL THEN
2306 UPDATE ap_payment_schedules
2307 SET inv_curr_gross_amount = ROUND(gross_amount/P_Payment_Cross_Rate,
2308 l_precision_inv_curr)
2309 WHERE invoice_id = P_Invoice_Id;
2310 ELSE
2311 UPDATE ap_payment_schedules
2312 SET inv_curr_gross_amount = (ROUND(gross_amount/P_Payment_Cross_Rate
2313 /l_min_acc_unit_inv_curr)
2314 * l_min_acc_unit_inv_curr)
2315 WHERE invoice_id = P_Invoice_Id;
2316 END IF;
2317
2318 /*
2319 UPDATE ap_payment_schedules
2320 SET inv_curr_gross_amount = (
2321 SELECT DECODE(F.minimum_accountable_unit,NULL,
2322 ROUND( gross_amount / P_Payment_Cross_Rate
2323 , F.precision),
2324 ROUND( gross_amount / P_Payment_Cross_Rate
2325 /F.minimum_accountable_unit)
2326 * F.minimum_accountable_unit)
2327 FROM fnd_currencies F
2328 WHERE F.currency_code = P_Invoice_Currency)
2329 WHERE invoice_id = P_Invoice_Id;
2330 */
2331 --
2332 -- Change for cross currency
2333 -- Adjust inv_curr_gross_amount for rounding error
2334 --
2335 debug_info := 'Open cursor c_inv_curr_sched_total';
2336 OPEN c_inv_curr_sched_total;
2337 debug_info := 'Fetch cursor c_inv_curr_sched_total';
2338 FETCH c_inv_curr_sched_total INTO l_inv_curr_sched_total,
2339 l_invoice_sign;
2340 debug_info := 'Close cursor c_inv_curr_sched_total';
2341 CLOSE c_inv_curr_sched_total;
2342
2343
2344 l_debug_info := 'Update ap_payment_schedules - set inv_curr_gross_amount:P_Invoice_Amount->'||P_Invoice_Amount||
2345 'l_inv_curr_sched_total->'||l_inv_curr_sched_total; --bug 8991699
2346 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2347 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2348 END IF; --bug 8991699
2349
2350 --bug 8991699 begins
2351 l_retained_amount := nvl(abs(ap_invoices_utility_pkg.get_retained_total
2352 (P_Invoice_Id,NULL)),0);
2353
2354 Select nvl(net_of_retainage_flag,'N')
2355 into l_net_of_ret_flag
2356 from ap_invoices
2357 where invoice_id = P_Invoice_Id;
2358
2359 --This procedure is called either during the time of recalculating
2360 --schedules or when invoice amount is updated as per fix 8891266.
2361 --Hence l_inv_curr_sched_total will always be net of retainage at this
2362 --point. Thus adding retainage back so that the following update happens
2363 --only when there is actual rounding issue and due to which the amount
2364 --doesn't match with invoice amount.
2365
2366 If l_net_of_ret_flag = 'N' and l_retained_amount > 0 then
2367 l_inv_curr_sched_total := l_inv_curr_sched_total + l_retained_amount;
2368 END if;
2369 --bug 8991699 ends
2370
2371 -- Adjust inv_curr_gross_amount for rounding errors
2372
2373 IF (l_inv_curr_sched_total <> P_Invoice_Amount) THEN
2374
2375 debug_info := 'Update ap_payment_schedules - set inv_curr_gross_amount';
2376
2377 UPDATE AP_PAYMENT_SCHEDULES
2378 SET inv_curr_gross_amount = inv_curr_gross_amount +
2379 TO_NUMBER(P_Invoice_Amount) -
2380 TO_NUMBER(l_inv_curr_sched_total)
2381 WHERE invoice_id = P_Invoice_Id
2382 AND payment_num = (SELECT MAX(payment_num)
2383 FROM ap_payment_schedules
2384 WHERE invoice_id = P_Invoice_Id);
2385 END IF;
2386
2387
2388 ap_invoices_pkg.validate_docs_payable(p_INVOICE_ID,null,l_hold_flag);
2389
2390
2391
2392 EXCEPTION
2393 WHEN OTHERS THEN
2394 IF (SQLCODE <> -20001) THEN
2395 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
2396 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
2397 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
2398 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice_Id = '||P_Invoice_Id
2399 ||', Terms_Id = ' ||P_Terms_Id
2400 ||', Last_Updated_By = ' ||P_Last_Updated_By
2401 ||', Created_By = ' ||P_Created_By
2402 ||', Payment_Priority = ' ||P_Payment_Priority
2403 ||', Batch_Id = ' ||P_Batch_Id
2404 ||', Terms_Date = ' ||P_Terms_Date
2405 ||', Invoice_Amount = ' ||P_Invoice_Amount
2406 ||', Amount_for_discount = ' ||P_Amount_For_Discount
2407 ||', Payment_Method = ' ||P_Payment_Method
2408 ||', Currency = ' ||P_invoice_currency
2409 );
2410 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
2411 END IF;
2412 APP_EXCEPTION.RAISE_EXCEPTION;
2413
2414 END AP_Create_From_Terms;
2415
2416 END AP_CREATE_PAY_SCHEDS_PKG;