1 PACKAGE BODY AP_PAYMENT_SCHEDULES_PKG AS
2 /* $Header: apipascb.pls 120.7.12010000.7 2009/10/06 05:41:54 rseeta ship $ */
3
4 -----------------------------------------------------------------------
5 -- PROCEDURE adjust_pay_schedule adjusts the payment schedule of
6 -- a paid or partially paid invoice
7 --
8 -- PRECONDITION: Called from the invoice block in PRE-UPDATE via
9 -- stored procedure call ap_invoices_pkg.invoice_pre_update()
10 -----------------------------------------------------------------------
11 PROCEDURE adjust_pay_schedule (X_invoice_id IN number,
12 X_invoice_amount IN number,
13 X_payment_status_flag IN OUT NOCOPY varchar2,
14 X_invoice_type_lookup_code IN varchar2,
15 X_last_updated_by IN number,
16 X_message1 IN OUT NOCOPY varchar2,
17 X_message2 IN OUT NOCOPY varchar2,
18 X_reset_match_status IN OUT NOCOPY varchar2,
19 X_liability_adjusted_flag IN OUT NOCOPY varchar2,
20 X_calling_sequence IN varchar2,
21 X_calling_mode IN varchar2,
22 X_revalidate_ps IN OUT NOCOPY varchar2)
23 IS
24 current_calling_sequence VARCHAR2(2000);
25 debug_info VARCHAR2(100);
26 l_current_payment_num number;
27 l_current_amount_to_adjust number;
28 l_original_invoice_amount number;
29 l_net_amount_to_adjust number;
30 l_payment_num_to_add number;
31 l_amount_remaining number;
32 l_payment_status_flag ap_payment_schedules.payment_status_flag%TYPE;
33 l_allow_paid_invoice_adjust
34 ap_system_parameters.allow_paid_invoice_adjust%TYPE;
35 l_add_new_payment_schedule varchar2(1) := 'Y';
36 l_sum_ps_amount_remaining number;
37 l_payment_currency_code ap_invoices.payment_currency_code%TYPE;
38 l_invoice_currency_code ap_invoices.invoice_currency_code%TYPE;
39 l_payment_cross_rate ap_invoices.payment_cross_rate%TYPE;
40 l_pay_curr_invoice_amount ap_invoices.pay_curr_invoice_amount%TYPE;
41 l_pay_curr_orig_inv_amt ap_invoices.pay_curr_invoice_amount%TYPE;
42 l_pay_curr_net_amt_to_adj number;
43 l_inv_curr_sched_total number;
44
45 --8891266
46 l_recouped_amount number;
47 l_amount_paid NUMBER;
48
49 cursor message1_cursor is
50 SELECT 'AP_PAY_WARN_DISC_UPDATE'
51 FROM ap_payment_schedules
52 WHERE invoice_id = X_invoice_id
53 AND payment_num = l_current_payment_num
54 AND (NVL(discount_amount_available, 0) <> 0
55 OR NVL(second_disc_amt_available, 0) <> 0
56 OR NVL(third_disc_amt_available, 0) <> 0);
57
58 cursor invoice_cursor is
59 select AI.invoice_amount,
60 SP.allow_paid_invoice_adjust,
61 AI.invoice_currency_code,
62 AI.payment_currency_code,
63 AI.payment_cross_rate,
64 nvl(AI.amount_paid,0), --bug 8891266
65 nvl(AI.pay_curr_invoice_amount, AI.invoice_amount)
66 from ap_invoices AI,
67 ap_system_parameters SP
68 where invoice_id = X_invoice_id;
69
70 -- If we're adding a new payment schedule and not adjusting
71 -- an existing one, then the payment_status_flag of the
72 -- payment schedule we're copying from is irrelevant.
73 --
74 -- NOTE: The reason l_add_new_payment_schedule is declared as a
75 -- varchar2 and not a boolean is that a boolean can not
76 -- be evaluated in a cursor select statement.
77 --
78 cursor pay_sched_adjust_cursor is
79 SELECT payment_num,
80 amount_remaining,
81 payment_status_flag
82 FROM ap_payment_schedules
83 WHERE invoice_id = X_invoice_id
84 AND (l_add_new_payment_schedule='Y' OR
85 payment_status_flag <> 'Y')
86 ORDER BY due_date desc, payment_num desc;
87
88 cursor payment_num_to_add_cursor is
89 SELECT nvl((MAX(payment_num)+1),1)
90 FROM ap_payment_schedules
91 WHERE invoice_id = X_invoice_id;
92
93 cursor c_inv_curr_sched_total IS
94 SELECT SUM(nvl(inv_curr_gross_amount, gross_amount))
95 FROM ap_payment_schedules
96 WHERE invoice_id = X_Invoice_Id;
97
98 BEGIN
99 current_calling_sequence :=
100 'AP_PAYMENT_SCHEDULES_PKG.ADJUST_PAY_SCHEDULE<-' ||
101 X_calling_sequence;
102
103 -- Determine the original invoice_amount
104 -- The precondition of this procedure is that it is being called
105 -- in PRE-UPDATE mode; thus the new amount has not been saved
106 -- to the database.
107
108 debug_info := 'Determining proper payment schedule to adjust';
109
110 open invoice_cursor;
111 debug_info := 'Fetch cursor invoice_cursor';
112 fetch invoice_cursor into l_original_invoice_amount,
113 l_allow_paid_invoice_adjust,
114 l_invoice_currency_code,
115 l_payment_currency_code,
116 l_payment_cross_rate,
117 l_amount_paid, --bug 8891266
118 l_pay_curr_orig_inv_amt;
119 debug_info := 'Close cursor invoice_cursor';
120 close invoice_cursor;
121
122 l_pay_curr_invoice_amount := ap_utilities_pkg.ap_round_currency(
123 X_invoice_amount * l_payment_cross_rate,
124 l_payment_currency_code);
125 l_net_amount_to_adjust := X_invoice_amount - l_original_invoice_amount;
126 l_pay_curr_net_amt_to_adj := l_pay_curr_invoice_amount -
127 l_pay_curr_orig_inv_amt;
128
129 if (l_net_amount_to_adjust = 0) then
130 -- No need to alter payment schedules if we're not
131 -- adjusting the liability
132 --
133 X_liability_adjusted_flag := 'N';
134 return;
135 else
136 -- Make note of the fact that a liability adjustment will
137 -- be made. In the Invoice Workbench, we will requery the
138 -- payment schedules block if the liability_adjusted_flag = 'Y'.
139 --
140 X_liability_adjusted_flag := 'Y';
141 end if;
142
143 -- Although the Invoice Workbench enforces rules which prevent
144 -- updates to the invoice (liability) amount in certain cases,
145 -- we want to ensure that these rules are enforced server-side
146 -- as well.
147
148 --bug 8891266 fetching recouped amount
149 l_recouped_amount := AP_MATCHING_UTILS_PKG.Get_Inv_Line_Recouped_Amount
150 (P_Invoice_Id => X_invoice_id,
151 P_Invoice_Line_Number => Null);
152
153 if (ap_invoices_pkg.get_encumbered_flag(X_invoice_id) = 'Y') then
154 -- Cannot change the invoice_amount as it is encumbered
155 fnd_message.set_name('SQLAP','AP_INV_ALL_DISTS_ENCUMB');
156 app_exception.raise_exception;
157 elsif (ap_invoices_pkg.get_posting_status(X_invoice_id) = 'Y') then
158 -- Cannot change the invoice_amount as it is posted
159 fnd_message.set_name('SQLAP','AP_INV_ALL_DIST_POSTED');
160 app_exception.raise_exception;
161 elsif (ap_invoices_pkg.selected_for_payment_flag(
162 X_invoice_id) = 'Y') then
163 -- Cannot change the amount as it is selected for payment
164 fnd_message.set_name('SQLAP','AP_INV_SELECTED_INVOICE');
165 app_exception.raise_exception;
166 elsif (l_allow_paid_invoice_adjust <> 'Y'
167 and nvl(x_calling_mode, 'X') <> 'APXIIMPT'
168 --bug 8891266 added the below condition
169 and l_amount_paid <> (-1 * l_recouped_amount )) then
170 fnd_message.set_name('SQLAP','AP_DIST_NO_UPDATE_PAID');
171 app_exception.raise_exception;
172 end if;
173
174 -- Look at the sign of the adjustment and determine whether
175 -- an existing payment should be adjusted or a payment schedule
176 -- should be added.
177 --
178 if (X_payment_status_flag <> 'N' and
179 ((X_invoice_type_lookup_code in ('CREDIT','DEBIT') and
180 l_net_amount_to_adjust <= 0) or
181 (X_invoice_type_lookup_code not in ('CREDIT','DEBIT') and
182 l_net_amount_to_adjust >= 0))) then
183 --
184 -- Invoice is either paid or partially paid and the
185 -- the amount of the adjustment is greater than the
186 -- scheduled payment is absolute terms. In this case,
187 -- we can add a payment schedule
188 --
189 l_add_new_payment_schedule := 'Y';
190 else
191 --
192 -- We will be adjusting a payment schedule in this case
193 --
194 l_add_new_payment_schedule := 'N';
195 end if;
196
197 --
198 -- New for 10SC
199 --
200 -- Instead of restricting liability adjustment to a single
201 -- payment schedule, we will iteratively apply adjustment to all unpaid
202 -- and partially paid payment schedules in descending order
203 -- of due date until the adjustment is fully applied.
204 --
205 -- We will delete any unpaid payment schedules where an adjustment
206 -- will cause the gross amount to be reduced to zero.
207 --
208 -- If an adjustment to existing payment schedule is not planned,
209 -- ie, we will be adding a new one, then the first fetch from
210 -- pay_sched_adjust_cursor will yield the payment_num of the
211 -- payment schedule that we wish to base our new record on.
212 --
213 debug_info := 'Open pay_sched_adjust_cursor';
214 open pay_sched_adjust_cursor;
215
216 loop
217
218 debug_info := 'Fetch pay_sched_adjust_cursor';
219 fetch pay_sched_adjust_cursor into l_current_payment_num,
220 l_amount_remaining,
221 l_payment_status_flag;
222
223 -- Leave the cursor if we intend to create a new payment schedule
224 -- or if we've run out NOCOPY of payment schedules to process.
225 --
226 exit when (l_add_new_payment_schedule='Y' or
227 pay_sched_adjust_cursor%NOTFOUND);
228
229 -- For the current payment schedule, reduce the gross_amount
230 -- by the adjustment amount or its amount_remaining,
231 -- whichever is less.
232 --
233 if (ABS(l_amount_remaining) - ABS(l_pay_curr_net_amt_to_adj) >= 0) then
234 l_current_amount_to_adjust := l_pay_curr_net_amt_to_adj;
235 else
236 l_current_amount_to_adjust := (0 - l_amount_remaining);
237 end if;
238
239 -- If the adjustment is being made for the entire gross amount
240 -- of the payment schedule and the payment schedule is unpaid,
241 -- then delete the record, otherwise update it.
242 --
243 if ((l_amount_remaining + l_current_amount_to_adjust = 0) and
244 l_payment_status_flag not in ('Y','P')) then
245
246 debug_info := 'Delete AP_PAYMENT_SCHEDULES payment_num '||
247 l_current_payment_num;
248
249 delete from ap_payment_schedules
250 where invoice_id = X_invoice_id
251 and payment_num = l_current_payment_num;
252
253 else
254 --
255 -- Update the payment schedule.
256 --
257 -- NOTE: This is non-standard to have an update to another table
258 -- called from the pre_update trigger. We anticipate no problems
259 -- in this case because the table ap_invoices was marked already
260 -- when the commit was invoked. (See the update_liability trigger.)
261 -- Usual locking order is invoices then pay lines,
262 -- so this is consistent.
263 --
264 debug_info := 'Update AP_PAYMENT_SCHEDULES payment_num '||
265 l_current_payment_num;
266
267 UPDATE ap_payment_schedules
268 SET gross_amount = NVL(gross_amount, 0)+l_current_amount_to_adjust,
269 inv_curr_gross_amount = (
270 SELECT DECODE(F.minimum_accountable_unit,NULL,
271 ROUND( ((NVL(gross_amount, 0)+
272 l_current_amount_to_adjust)/
273 l_payment_cross_rate)
274 , F.precision),
275 ROUND( ((NVL(gross_amount, 0)+
276 l_current_amount_to_adjust)/
277 l_payment_cross_rate)
278 / F.minimum_accountable_unit)
279 * F.minimum_accountable_unit)
280 FROM fnd_currencies_vl F
281 WHERE F.currency_code = l_invoice_currency_code),
282 amount_remaining = NVL(amount_remaining, 0)
283 + l_current_amount_to_adjust,
284 payment_status_flag =
285 DECODE(NVL(amount_remaining, 0) +
286 l_current_amount_to_adjust,
287 NVL(gross_amount, 0) +
288 l_current_amount_to_adjust, 'N',
289 0, DECODE(X_invoice_amount,
290 0,'N',
291 'Y'),
292 'P')
293 WHERE invoice_id = X_invoice_id
294 AND payment_num = l_current_payment_num;
295
296
297 -- If message name is returned in to X_Message1 then
298 -- we know that the payment schedule line has a non-zero
299 -- discount which may need adjustment
300 -- Message to display is AP_PAY_WARN_DISC_UPDATE
301 --
302 if (X_Message1 is null) then
303 debug_info := 'Select from AP_PAYMENT_SCHEDULES';
304
305 open message1_cursor;
306 debug_info := 'Fetch message1_cursor';
307 fetch message1_cursor into X_Message1;
308 debug_info := 'Close message1_cursor';
309 close message1_cursor;
310 end if;
311 end if;
312
313 -- Reduce the Net Adjustment amount by the amount we're
314 -- applying in this adjustment.
315 --
316 l_pay_curr_net_amt_to_adj := l_pay_curr_net_amt_to_adj -
317 l_current_amount_to_adjust;
318
319 -- If the adjustment has been fully applied then exit the loop
320 exit when (l_pay_curr_net_amt_to_adj = 0);
321
322 end loop;
323
324 debug_info := 'Close pay_sched_adjust_cursor';
325 close pay_sched_adjust_cursor;
326
327 -- If the previous cursor retrieved no payment schedules,
328 -- fail the procedure and tell the user.
329 --
330 if (l_current_payment_num is null) then
331 -- Cannot find a payment schedule to adjust
332 FND_MESSAGE.Set_Name('SQLAP', 'AP_INV_NO_PAYMENT_SCHEDULE');
333 FND_MESSAGE.Set_Name('SQLAP', 'AP_PAY_NO_PAYMENT_SCHEDULE');
334 APP_EXCEPTION.Raise_Exception;
335 end if;
336
337 -- Adjust for any rounding errors that might have been introduced
338 -- for inv_curr_gross_amount
339
340 if (l_add_new_payment_schedule = 'N') then
341 debug_info := 'Open cursor c_inv_curr_sched_total';
342 OPEN c_inv_curr_sched_total;
343 debug_info := 'Fetch cursor c_inv_curr_sched_total';
344 FETCH c_inv_curr_sched_total INTO l_inv_curr_sched_total;
345 debug_info := 'Close cursor c_inv_curr_sched_total';
346 CLOSE c_inv_curr_sched_total;
347
348 -- Adjust inv_curr_gross_amount for rounding errors
349 --
350 IF (l_inv_curr_sched_total <> X_invoice_amount) THEN
351 --
352 debug_info:= 'Update ap_payment_schedules - set inv_curr_gross_amount';
353 UPDATE AP_PAYMENT_SCHEDULES
354 SET inv_curr_gross_amount = inv_curr_gross_amount
355 + X_Invoice_Amount
356 - l_inv_curr_sched_total
357 WHERE invoice_id = X_Invoice_Id
358 AND payment_num = (SELECT MAX(payment_num)
359 FROM ap_payment_schedules
360 WHERE invoice_id = X_Invoice_Id);
361 --
362 END IF;
363 end if;
364 --
365 -- Add the new payment schedule
366 --
367 if (l_add_new_payment_schedule = 'Y') then
368
369 -- Determine payment num of new payment schedule
370 debug_info := 'Open payment_num_to_add_cursor';
371 open payment_num_to_add_cursor;
372 debug_info := 'Fetch payment_num_to_add_cursor';
373 fetch payment_num_to_add_cursor into l_payment_num_to_add;
374 debug_info := 'Close payment_num_to_add_cursor';
375 close payment_num_to_add_cursor;
376
377 debug_info := 'Insert into AP_PAYMENT_SCHEDULES';
378
379 -- Insert the new payment schedule
380 INSERT INTO ap_payment_schedules(
381 invoice_id, payment_num, due_date,
382 last_update_date, last_updated_by,
383 last_update_login, creation_date, created_by,
384 payment_cross_rate,
385 gross_amount,inv_curr_gross_amount,amount_remaining,
386 payment_priority, hold_flag,
387 payment_status_flag, batch_id, payment_method_code,
388 external_bank_account_id,
389 org_id, --MOAC project
390 remittance_message1,
391 remittance_message2,
392 remittance_message3,
393 --third party payments
394 remit_to_supplier_name,
395 remit_to_supplier_id,
396 remit_to_supplier_site,
397 remit_to_supplier_site_id,
398 relationship_id
399 )
400 SELECT X_invoice_id, l_payment_num_to_add, P.due_date,
401 SYSDATE, X_last_updated_by,
402 null, SYSDATE, X_last_updated_by,
403 P.payment_cross_rate,
404 l_pay_curr_net_amt_to_adj,
405 l_net_amount_to_adjust,
406 l_pay_curr_net_amt_to_adj,
407 P.payment_priority, P.hold_flag, 'N', P.batch_id,
408 P.payment_method_code,
409 P.external_bank_account_id,
410 P.org_id, --MOAC project
411 p.remittance_message1,
412 p.remittance_message2,
413 p.remittance_message3,
414 --third party payments
415 p.remit_to_supplier_name,
416 p.remit_to_supplier_id,
417 p.remit_to_supplier_site,
418 p.remit_to_supplier_site_id,
419 p.relationship_id
420 FROM ap_payment_schedules P
421 WHERE P.invoice_id = X_invoice_id
422 AND P.payment_num = l_current_payment_num;
423
424 x_revalidate_ps := 'Y';
425
426
427
428 -- If encumbrance is on, then this invoice will already have dist
429 -- lines that need reapproval, so we can skip this. Plus, we don't
430 -- want to flip any 'A' flags to 'N' if encumbrance is on.
431 X_reset_match_status := 'Y';
432
433 else
434 --
435 -- Existing payment schedules were adjusted. Inform user.
436 --
437 X_Message2 := 'AP_PAY_WARN_SCHED_UPDATE';
438 end if;
439
440 --
441 -- Check if we need to change the payment_status_flag
442 --
443 SELECT sum(amount_remaining)
444 INTO l_sum_ps_amount_remaining
445 FROM ap_payment_schedules
446 WHERE invoice_id = X_invoice_id;
447
448 if (l_sum_ps_amount_remaining <> 0) then
449 X_payment_status_flag := 'P';
450 else
451 X_payment_status_flag := 'Y';
452 end if;
453
454
455 EXCEPTION
456 WHEN OTHERS THEN
457 IF (SQLCODE <> -20001) THEN
458 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
459 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
460 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
461 current_calling_sequence);
462 FND_MESSAGE.SET_TOKEN('PARAMETERS',
463 'X_invoice_id = ' ||X_invoice_id
464 ||', X_invoice_amount = ' ||X_invoice_amount
465 ||', X_payment_status_flag= '||X_payment_status_flag
466 ||', X_invoice_type_lookup_code = '||X_invoice_type_lookup_code
467 ||', X_last_updated_by = ' ||X_last_updated_by
468 ||', X_message1 = ' ||X_message1
469 ||', X_message2 = ' ||X_message2
470 ||', X_reset_match_status = '||X_reset_match_status
471 ||', X_liability_adjusted_flag = '||X_liability_adjusted_flag
472 );
473 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
474 END IF;
475 APP_EXCEPTION.RAISE_EXCEPTION;
476
477 END adjust_pay_schedule;
478
479 -----------------------------------------------------------------------
480 -- FUNCTION get_amt_withheld_per_sched, returns prorated withheld
481 -- per payment schedule of an invoice.
482 -- Function added for bug 3484292
483 -----------------------------------------------------------------------
484
485 FUNCTION get_amt_withheld_per_sched(X_invoice_id IN NUMBER,
486 X_gross_amount IN NUMBER,
487 X_currency_code IN VARCHAR2)
488 RETURN NUMBER
489 IS
490 l_wt_amt_to_subtract number :=0;
491 BEGIN
492 select nvl(ap_utilities_pkg.ap_round_currency(
493 ap_invoices_pkg.get_amount_withheld(ai.invoice_id)*
494 ai.payment_cross_rate,X_currency_code),0)*
495 X_gross_amount/decode(ai.pay_curr_invoice_amount, 0, 1,
496 nvl(ai.pay_curr_invoice_amount, 1))
497 into l_wt_amt_to_subtract
498 from ap_invoices ai
499 where ai.invoice_id=X_invoice_id;
500 return l_wt_amt_to_subtract;
501 END get_amt_withheld_per_sched;
502
503 -----------------------------------------------------------------------
504 -- FUNCTION get_discount_available computes the discount available
505 -- based on X_check_date
506 -----------------------------------------------------------------------
507 FUNCTION get_discount_available(X_invoice_id IN NUMBER,
508 X_payment_num IN NUMBER,
509 X_check_date IN DATE,
510 X_currency_code IN VARCHAR2)
511 RETURN NUMBER
512 IS
513 l_discount_available NUMBER;
514 l_wt_amt_to_subtract NUMBER := 0; --Bug 3484292
515 l_gross_amount NUMBER; -- BUG 3741934
516 BEGIN
517
518 -- bug 3484292 Added the select stmt below.
519 select nvl(ap_utilities_pkg.ap_round_currency(
520 ap_invoices_pkg.get_amount_withheld(ai.invoice_id)*
521 ai.payment_cross_rate,X_currency_code),0)*
522 aps.gross_amount/decode(ai.pay_curr_invoice_amount, 0, 1,
523 nvl(ai.pay_curr_invoice_amount, 1)),
524 aps.gross_amount -- BUG 3741934
525 into l_wt_amt_to_subtract, l_gross_amount
526 from ap_invoices ai,ap_payment_schedules aps
527 where ai.invoice_id=aps.invoice_id
528 and aps.payment_num=X_payment_num
529 and ai.invoice_id=X_invoice_id;
530
531 /*
532 BUG 3741934: Branch around the SQL that calculates the discount if the
533 Gross Amount of the payment schedule is equal to the withheld
534 amount.
535 */
536
537 IF l_wt_amt_to_subtract <> l_gross_amount
538 THEN
539 SELECT NVL(ap_utilities_pkg.ap_round_currency(
540 DECODE(gross_amount, 0, 0,
541 DECODE(air.always_take_disc_flag, 'Y', discount_amount_available, --Bug7717053, added the table alias
542 GREATEST(
543 DECODE(SIGN(X_check_date -
544 NVL(discount_date, sysdate-9000)),
545 1, 0, NVL(ABS(discount_amount_available), 0)),
546 DECODE(SIGN(X_check_date -
547 NVL(second_discount_date, sysdate-9000)),
548 1, 0, NVL(ABS(second_disc_amt_available), 0)),
549 DECODE(SIGN(X_check_date -
550 NVL(third_discount_date, sysdate-9000)),
551 1, 0, NVL(ABS(third_disc_amt_available),0))) * DECODE(SIGN(gross_amount),-1,-1,1) )
552 * (amount_remaining/DECODE(gross_amount, 0, 1, gross_amount-decode(asp.create_awt_dists_type,
553 'APPROVAL',
554 l_wt_amt_to_subtract,
555 0)))),
556 X_currency_code),0) --Bug7717053, added the decode
557 INTO l_discount_available
558 FROM ap_invoices_ready_to_pay_v air, ap_system_parameters_all asp
559 WHERE invoice_id = X_invoice_id
560 AND payment_num = X_payment_num
561 AND air.org_id = asp.org_id; --Added for bug #8506044;
562 ELSE l_discount_available := 0;
563 END IF;
564
565 RETURN l_discount_available;
566
567 END get_discount_available;
568
569 -----------------------------------------------------------------------
570 -- FUNCTION get_discount_date computes the discount date based on
571 -- X_check_date
572 -----------------------------------------------------------------------
573 FUNCTION get_discount_date(X_invoice_id IN NUMBER,
574 X_payment_num IN NUMBER,
575 X_check_date IN DATE)
576 RETURN DATE
577 IS
578 l_discount_date DATE;
579 BEGIN
580
581 SELECT DECODE(always_take_disc_flag, 'Y', due_date,
582 DECODE(SIGN(X_check_date - NVL(discount_date,sysdate-9000)-1),
583 -1, discount_date,
584 DECODE(SIGN(X_check_date - NVL(second_discount_date,sysdate-9000)-1),
585 -1, second_discount_date,
586 DECODE(SIGN(X_check_date - NVL(third_discount_date,sysdate-9000)-1),
587 -1, third_discount_date, due_date))))
588 INTO l_discount_date
589 FROM ap_invoices_ready_to_pay_v
590 WHERE invoice_id = X_invoice_id
591 AND payment_num = X_payment_num;
592
593 RETURN l_discount_date;
594
595 END get_discount_date;
596 PROCEDURE Lock_Row( X_Invoice_Id NUMBER,
597 X_Last_Updated_By NUMBER,
598 X_Last_Update_Date DATE,
599 X_Payment_Cross_Rate NUMBER,
600 X_Payment_Num NUMBER,
601 X_Amount_Remaining NUMBER,
602 X_Created_By NUMBER,
603 X_Creation_Date DATE,
604 X_Discount_Date DATE,
605 X_Due_Date DATE,
606 X_Future_Pay_Due_Date DATE,
607 X_Gross_Amount NUMBER,
608 X_Hold_Flag VARCHAR2,
609 X_iby_hold_reason VARCHAR2, /*bug 8893354 */
610 X_Last_Update_Login NUMBER,
611 X_Payment_Method_Lookup_Code VARCHAR2 default null,
612 X_payment_method_code varchar2,
613 X_Payment_Priority NUMBER,
614 X_Payment_Status_Flag VARCHAR2,
615 X_Second_Discount_Date DATE,
616 X_Third_Discount_Date DATE,
617 X_Batch_Id NUMBER,
618 X_Discount_Amount_Available NUMBER,
619 X_Second_Disc_Amt_Available NUMBER,
620 X_Third_Disc_Amt_Available NUMBER,
621 X_Attribute1 VARCHAR2,
622 X_Attribute10 VARCHAR2,
623 X_Attribute11 VARCHAR2,
624 X_Attribute12 VARCHAR2,
625 X_Attribute13 VARCHAR2,
626 X_Attribute14 VARCHAR2,
627 X_Attribute15 VARCHAR2,
628 X_Attribute2 VARCHAR2,
629 X_Attribute3 VARCHAR2,
630 X_Attribute4 VARCHAR2,
631 X_Attribute5 VARCHAR2,
632 X_Attribute6 VARCHAR2,
633 X_Attribute7 VARCHAR2,
634 X_Attribute8 VARCHAR2,
635 X_Attribute9 VARCHAR2,
636 X_Attribute_Category VARCHAR2,
637 X_Discount_Amount_Remaining NUMBER,
638 X_Global_Attribute_Category VARCHAR2,
639 X_Global_Attribute1 VARCHAR2,
640 X_Global_Attribute2 VARCHAR2,
641 X_Global_Attribute3 VARCHAR2,
642 X_Global_Attribute4 VARCHAR2,
643 X_Global_Attribute5 VARCHAR2,
644 X_Global_Attribute6 VARCHAR2,
645 X_Global_Attribute7 VARCHAR2,
646 X_Global_Attribute8 VARCHAR2,
647 X_Global_Attribute9 VARCHAR2,
648 X_Global_Attribute10 VARCHAR2,
649 X_Global_Attribute11 VARCHAR2,
650 X_Global_Attribute12 VARCHAR2,
651 X_Global_Attribute13 VARCHAR2,
652 X_Global_Attribute14 VARCHAR2,
653 X_Global_Attribute15 VARCHAR2,
654 X_Global_Attribute16 VARCHAR2,
655 X_Global_Attribute17 VARCHAR2,
656 X_Global_Attribute18 VARCHAR2,
657 X_Global_Attribute19 VARCHAR2,
658 X_Global_Attribute20 VARCHAR2,
659 X_External_Bank_Account_Id NUMBER,
660 X_Inv_Curr_Gross_Amount NUMBER,
661 X_Org_Id NUMBER,
662 X_Calling_Sequence IN VARCHAR2,
663 --Third Party Payments
664 X_Remit_To_Supplier_Name VARCHAR2,
665 X_Remit_To_Supplier_Id NUMBER,
666 X_Remit_To_Supplier_Site VARCHAR2,
667 X_Remit_To_Supplier_Site_Id NUMBER,
668 X_Relationship_Id NUMBER
669 ) IS
670 CURSOR C IS
671 SELECT *
672 FROM ap_payment_schedules
673 WHERE invoice_id = X_Invoice_Id
674 AND payment_num = X_Payment_Num
675 FOR UPDATE of invoice_id NOWAIT;
676 Recinfo C%ROWTYPE;
677
678 first_conditions BOOLEAN := TRUE;
679 current_calling_sequence VARCHAR2(2000);
680 debug_info VARCHAR2(100);
681
682 BEGIN
683 -- Update the calling sequence
684 --
685 current_calling_sequence :=
686 'AP_PAYMENT_SCHEDULES_PKG.LOCK_ROW<-'||X_calling_sequence;
687
688 debug_info := 'Open cursor C';
689 OPEN C;
690 debug_info := 'Fetch cursor C';
691 FETCH C INTO Recinfo;
692 if (C%NOTFOUND) then
693 debug_info := 'Close cursor C - ROW NOTFOUND';
694 CLOSE C;
695 RAISE NO_DATA_FOUND;
696 end if;
697 debug_info := 'Close cursor C';
698 CLOSE C;
699
700 first_conditions :=
701 ((Recinfo.Invoice_Id = X_Invoice_Id) OR
702 ((Recinfo.Invoice_Id IS NULL)
703 AND (X_Invoice_Id IS NULL)))
704 AND ((Recinfo.Last_Updated_By = X_Last_Updated_By) OR
705 ((Recinfo.Last_Updated_By IS NULL)
706 AND (X_Last_Updated_By IS NULL)))
707 -- Bug 2909797 AND ((Recinfo.Last_Update_Date = X_Last_Update_Date) OR
708 -- ((Recinfo.Last_Update_Date IS NULL)
709 -- AND (X_Last_Update_Date IS NULL)))
710 AND ((Recinfo.Payment_Cross_Rate = X_Payment_Cross_Rate) OR
711 ((Recinfo.Payment_Cross_Rate IS NULL)
712 AND (X_Payment_Cross_Rate IS NULL)))
713 AND ((Recinfo.Payment_Num = X_Payment_Num) OR
714 ((Recinfo.Payment_Num IS NULL)
715 AND (X_Payment_Num IS NULL)))
716 AND ((Recinfo.Amount_Remaining = X_Amount_Remaining) OR
717 ((Recinfo.Amount_Remaining IS NULL)
718 AND (X_Amount_Remaining IS NULL)))
719 AND ((Recinfo.Created_By = X_Created_By) OR
720 ((Recinfo.Created_By IS NULL)
721 AND (X_Created_By IS NULL)))
722 -- Bug 2909797 AND ((Recinfo.Creation_Date = X_Creation_Date) OR
723 -- ((Recinfo.Creation_Date IS NULL)
724 -- AND (X_Creation_Date IS NULL)))
725 AND ((Recinfo.Discount_Date = X_Discount_Date) OR
726 ((Recinfo.Discount_Date IS NULL)
727 AND (X_Discount_Date IS NULL)))
728 /* AND ((Recinfo.Due_Date = X_Due_Date) OR Commented for bug#8487514 */
729 AND ((trunc(Recinfo.Due_Date) = trunc(X_Due_Date)) OR /* Added for bug#8487514 */
730 ((Recinfo.Due_Date IS NULL)
731 AND (X_Due_Date IS NULL)))
732 AND ((Recinfo.Future_Pay_Due_Date = X_Future_Pay_Due_Date) OR
733 ((Recinfo.Future_Pay_Due_Date IS NULL)
734 AND (X_Future_Pay_Due_Date IS NULL)))
735 AND ((Recinfo.Gross_Amount = X_Gross_Amount) OR
736 ((Recinfo.Gross_Amount IS NULL)
737 AND (X_Gross_Amount IS NULL)))
738 AND ((Recinfo.Hold_Flag = X_Hold_Flag) OR
739 ((Recinfo.Hold_Flag IS NULL)
740 AND (X_Hold_Flag IS NULL)))
741 AND ((Recinfo.iby_hold_reason = X_iby_hold_reason) OR
742 ((Recinfo.iby_hold_reason IS NULL)
743 AND (X_iby_hold_reason IS NULL))) /*bug 8893354*/
744 AND ((Recinfo.Last_Update_Login = X_Last_Update_Login) OR
745 ((Recinfo.Last_Update_Login IS NULL)
746 AND (X_Last_Update_Login IS NULL)))
747 AND ((Recinfo.Payment_Method_Code = X_Payment_Method_Code) OR
748 ((Recinfo.Payment_Method_Code IS NULL)
749 AND (X_Payment_Method_Code IS NULL)))
750 AND ((Recinfo.Payment_Priority = X_Payment_Priority) OR
751 ((Recinfo.Payment_Priority IS NULL)
752 AND (X_Payment_Priority IS NULL)))
753 AND ((Recinfo.Payment_Status_Flag = X_Payment_Status_Flag) OR
754 ((Recinfo.Payment_Status_Flag IS NULL)
755 AND (X_Payment_Status_Flag IS NULL)))
756 AND ((Recinfo.Second_Discount_Date = X_Second_Discount_Date) OR
757 ((Recinfo.Second_Discount_Date IS NULL)
758 AND (X_Second_Discount_Date IS NULL)))
759 AND ((Recinfo.Third_Discount_Date = X_Third_Discount_Date) OR
760 ((Recinfo.Third_Discount_Date IS NULL)
761 AND (X_Third_Discount_Date IS NULL)))
762 AND ((Recinfo.Batch_Id = X_Batch_Id) OR
763 ((Recinfo.Batch_Id IS NULL)
764 AND (X_Batch_Id IS NULL)))
765 AND ((Recinfo.Discount_Amount_Available = X_Discount_Amount_Available) OR
766 ((Recinfo.Discount_Amount_Available IS NULL)
767 AND (X_Discount_Amount_Available IS NULL)))
768 AND ((Recinfo.Second_Disc_Amt_Available = X_Second_Disc_Amt_Available) OR
769 ((Recinfo.Second_Disc_Amt_Available IS NULL)
770 AND (X_Second_Disc_Amt_Available IS NULL)))
771 AND ((Recinfo.Third_Disc_Amt_Available = X_Third_Disc_Amt_Available) OR
772 ((Recinfo.Third_Disc_Amt_Available IS NULL)
773 AND (X_Third_Disc_Amt_Available IS NULL)))
774 AND ((Recinfo.Attribute1 = X_Attribute1) OR
775 ((Recinfo.Attribute1 IS NULL)
776 AND (X_Attribute1 IS NULL)))
777 AND ((Recinfo.Attribute10 = X_Attribute10) OR
778 ((Recinfo.Attribute10 IS NULL)
779 AND (X_Attribute10 IS NULL)))
780 AND ((Recinfo.Attribute11 = X_Attribute11) OR
781 ((Recinfo.Attribute11 IS NULL)
782 AND (X_Attribute11 IS NULL)))
783 AND ((Recinfo.Attribute12 = X_Attribute12) OR
784 ((Recinfo.Attribute12 IS NULL)
785 AND (X_Attribute12 IS NULL)))
786 AND ((Recinfo.Attribute13 = X_Attribute13) OR
787 ((Recinfo.Attribute13 IS NULL)
788 AND (X_Attribute13 IS NULL)))
789 AND ((Recinfo.Attribute14 = X_Attribute14) OR
790 ((Recinfo.Attribute14 IS NULL)
791 AND (X_Attribute14 IS NULL)))
792 AND ((Recinfo.Attribute15 = X_Attribute15) OR
793 ((Recinfo.Attribute15 IS NULL)
794 AND (X_Attribute15 IS NULL)))
795 AND ((Recinfo.Attribute2 = X_Attribute2) OR
796 ((Recinfo.Attribute2 IS NULL)
797 AND (X_Attribute2 IS NULL)))
798 AND ((Recinfo.Attribute3 = X_Attribute3) OR
799 ((Recinfo.Attribute3 IS NULL)
800 AND (X_Attribute3 IS NULL)))
801 AND ((Recinfo.Attribute4 = X_Attribute4) OR
802 ((Recinfo.Attribute4 IS NULL)
803 AND (X_Attribute4 IS NULL)))
804 AND ((Recinfo.Attribute5 = X_Attribute5) OR
805 ((Recinfo.Attribute5 IS NULL)
806 AND (X_Attribute5 IS NULL)))
807 AND ((Recinfo.Attribute6 = X_Attribute6) OR
808 ((Recinfo.Attribute6 IS NULL)
809 AND (X_Attribute6 IS NULL)))
810 AND ((Recinfo.Attribute7 = X_Attribute7) OR
811 ((Recinfo.Attribute7 IS NULL)
812 AND (X_Attribute7 IS NULL)))
813 AND ((Recinfo.Attribute8 = X_Attribute8) OR
814 ((Recinfo.Attribute8 IS NULL)
815 AND (X_Attribute8 IS NULL)))
816 AND ((Recinfo.Attribute9 = X_Attribute9) OR
817 ((Recinfo.Attribute9 IS NULL)
818 AND (X_Attribute9 IS NULL)))
819 AND ((Recinfo.Attribute_Category = X_Attribute_Category) OR
820 ((Recinfo.Attribute_Category IS NULL)
821 AND (X_Attribute_Category IS NULL)))
822 AND ((Recinfo.Discount_Amount_Remaining = X_Discount_Amount_Remaining) OR
823 ((Recinfo.Discount_Amount_Remaining IS NULL)
824 AND (X_Discount_Amount_Remaining IS NULL)))
825 -- Third party payments
826 AND ((Recinfo.Remit_To_Supplier_Name = X_Remit_To_Supplier_Name) OR
827 ((Recinfo.Remit_To_Supplier_Name IS NULL)
828 AND (X_Remit_To_Supplier_Name IS NULL)))
829 AND ((Recinfo.Remit_To_Supplier_Id = X_Remit_To_Supplier_Id) OR
830 ((Recinfo.Remit_To_Supplier_Id IS NULL)
831 AND (X_Remit_To_Supplier_Id IS NULL)))
832 AND ((Recinfo.Remit_To_Supplier_Site = X_Remit_To_Supplier_Site) OR
833 ((Recinfo.Remit_To_Supplier_Site IS NULL)
834 AND (X_Remit_To_Supplier_Site IS NULL)))
835 AND ((Recinfo.Remit_To_Supplier_Site_Id = X_Remit_To_Supplier_Site_Id) OR
836 ((Recinfo.Remit_To_Supplier_Site_Id IS NULL)
837 AND (X_Remit_To_Supplier_Site_Id IS NULL)))
838 AND ((Recinfo.Relationship_Id = X_Relationship_Id) OR
839 ((Recinfo.Relationship_Id IS NULL)
840 AND (X_Relationship_Id IS NULL)));
841
842 if (first_conditions
843 AND ((Recinfo.Global_Attribute_Category = X_Global_Attribute_Category) OR
844 ((Recinfo.Global_Attribute_Category IS NULL)
845 AND (X_Global_Attribute_Category IS NULL)))
846 AND ((Recinfo.Global_Attribute1 = X_Global_Attribute1) OR
847 ((Recinfo.Global_Attribute1 IS NULL)
848 AND (X_Global_Attribute1 IS NULL)))
849 AND ((Recinfo.Global_Attribute2 = X_Global_Attribute2) OR
850 ((Recinfo.Global_Attribute2 IS NULL)
851 AND (X_Global_Attribute2 IS NULL)))
852 AND ((Recinfo.Global_Attribute3 = X_Global_Attribute3) OR
853 ((Recinfo.Global_Attribute3 IS NULL)
854 AND (X_Global_Attribute3 IS NULL)))
855 AND ((Recinfo.Global_Attribute4 = X_Global_Attribute4) OR
856 ((Recinfo.Global_Attribute4 IS NULL)
857 AND (X_Global_Attribute4 IS NULL)))
858 AND ((Recinfo.Global_Attribute5 = X_Global_Attribute5) OR
859 ((Recinfo.Global_Attribute5 IS NULL)
860 AND (X_Global_Attribute5 IS NULL)))
861 AND ((Recinfo.Global_Attribute6 = X_Global_Attribute6) OR
862 ((Recinfo.Global_Attribute6 IS NULL)
863 AND (X_Global_Attribute6 IS NULL)))
864 AND ((Recinfo.Global_Attribute7 = X_Global_Attribute7) OR
865 ((Recinfo.Global_Attribute7 IS NULL)
866 AND (X_Global_Attribute7 IS NULL)))
867 AND ((Recinfo.Global_Attribute8 = X_Global_Attribute8) OR
868 ((Recinfo.Global_Attribute8 IS NULL)
869 AND (X_Global_Attribute8 IS NULL)))
870 AND ((Recinfo.Global_Attribute9 = X_Global_Attribute9) OR
871 ((Recinfo.Global_Attribute9 IS NULL)
872 AND (X_Global_Attribute9 IS NULL)))
873 AND ((Recinfo.Global_Attribute10 = X_Global_Attribute10) OR
874 ((Recinfo.Global_Attribute10 IS NULL)
875 AND (X_Global_Attribute10 IS NULL)))
876 AND ((Recinfo.Global_Attribute11 = X_Global_Attribute11) OR
877 ((Recinfo.Global_Attribute11 IS NULL)
878 AND (X_Global_Attribute11 IS NULL)))
879 AND ((Recinfo.Global_Attribute12 = X_Global_Attribute12) OR
880 ((Recinfo.Global_Attribute12 IS NULL)
881 AND (X_Global_Attribute12 IS NULL)))
882 AND ((Recinfo.Global_Attribute13 = X_Global_Attribute13) OR
883 ((Recinfo.Global_Attribute13 IS NULL)
884 AND (X_Global_Attribute13 IS NULL)))
885 AND ((Recinfo.Global_Attribute14 = X_Global_Attribute14) OR
886 ((Recinfo.Global_Attribute14 IS NULL)
887 AND (X_Global_Attribute14 IS NULL)))
888 AND ((Recinfo.Global_Attribute15 = X_Global_Attribute15) OR
889 ((Recinfo.Global_Attribute15 IS NULL)
890 AND (X_Global_Attribute15 IS NULL)))
891 AND ((Recinfo.Global_Attribute16 = X_Global_Attribute16) OR
892 ((Recinfo.Global_Attribute16 IS NULL)
893 AND (X_Global_Attribute16 IS NULL)))
894 AND ((Recinfo.Global_Attribute17 = X_Global_Attribute17) OR
895 ((Recinfo.Global_Attribute17 IS NULL)
896 AND (X_Global_Attribute17 IS NULL)))
897 AND ((Recinfo.Global_Attribute18 = X_Global_Attribute18) OR
898 ((Recinfo.Global_Attribute18 IS NULL)
899 AND (X_Global_Attribute18 IS NULL)))
900 AND ((Recinfo.Global_Attribute19 = X_Global_Attribute19) OR
901 ((Recinfo.Global_Attribute19 IS NULL)
902 AND (X_Global_Attribute19 IS NULL)))
903 AND ((Recinfo.Global_Attribute20 = X_Global_Attribute20) OR
904 ((Recinfo.Global_Attribute20 IS NULL)
905 AND (X_Global_Attribute20 IS NULL)))
906 AND ((Recinfo.External_Bank_Account_Id = X_External_Bank_Account_Id) OR
907 ((Recinfo.External_Bank_Account_Id IS NULL)
908 AND (X_External_Bank_Account_Id IS NULL)))
909 AND ((Recinfo.Inv_Curr_Gross_Amount = X_Inv_Curr_Gross_Amount) OR
910 ((Recinfo.Inv_Curr_Gross_Amount IS NULL)
911 AND (X_Inv_Curr_Gross_Amount IS NULL)))
912 AND ((Recinfo.Org_Id = X_Org_Id) OR
913 ((Recinfo.Org_Id IS NULL)
914 AND (X_Org_Id IS NULL)))
915 ) then
916 return;
917 else
918 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
919 APP_EXCEPTION.RAISE_EXCEPTION;
920 end if;
921
922 EXCEPTION
923 WHEN OTHERS THEN
924 IF (SQLCODE <> -20001) THEN
925 IF (SQLCODE = -54) THEN
926 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
927 ELSE
928 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
929 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
930 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
931 current_calling_sequence);
932 FND_MESSAGE.SET_TOKEN('PARAMETERS',
933 'X_Invoice_Id = '||X_Invoice_Id
934 ||', X_Payment_Num = '||X_Payment_Num
935 );
936 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
937 END IF;
938 END IF;
939 APP_EXCEPTION.RAISE_EXCEPTION;
940
941 END Lock_Row;
942
943 END AP_PAYMENT_SCHEDULES_PKG;
[Home] [Help]