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