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