[Home] [Help]
PACKAGE BODY: APPS.AP_PAY_INVOICE_PKG
Source
1 PACKAGE BODY AP_PAY_INVOICE_PKG AS
2 /*$Header: apayinvb.pls 120.17 2011/01/26 23:12:12 jaanders ship $*/
3 --
4 -- Declare Local procedures
5 --
6 PROCEDURE ap_pay_get_info(
7 P_invoice_id IN NUMBER,
8 P_check_id IN NUMBER,
9 P_payment_num IN NUMBER,
10 P_invoice_payment_id IN NUMBER,
11 P_old_invoice_payment_id IN NUMBER,
12 P_period_name IN OUT NOCOPY VARCHAR2,
13 P_accounting_date IN OUT NOCOPY DATE,
14 P_amount IN NUMBER,
15 P_discount_taken IN NUMBER,
16 P_discount_lost IN OUT NOCOPY NUMBER,
17 P_invoice_base_amount IN OUT NOCOPY NUMBER,
18 P_payment_base_amount IN OUT NOCOPY NUMBER,
19 P_set_of_books_id IN NUMBER,
20 P_currency_code IN VARCHAR2,
21 P_base_currency_code IN VARCHAR2,
22 P_exchange_rate IN NUMBER,
23 P_exchange_rate_type IN VARCHAR2,
24 P_exchange_date IN DATE,
25 P_ce_bank_acct_use_id IN NUMBER,
26 P_future_pay_posted_flag IN VARCHAR2,
27 P_gain_ccid IN OUT NOCOPY NUMBER,
28 P_loss_ccid IN OUT NOCOPY NUMBER,
29 P_payment_dists_flag IN VARCHAR2,
30 P_payment_mode IN VARCHAR2,
31 P_replace_flag IN VARCHAR2,
32 P_calling_sequence IN VARCHAR2,
33 P_last_update_date OUT NOCOPY DATE);
34
35
36 /*========================================================================
37 * Main Procedure:
38 +=============================================================================+
39 | Step | Description | Work for* |
40 +==========+====================================================+=============+
41 | Step 1: | Call ap_pay_get_info to get some needed | PAY/REV |
42 | | parameters | |
43 +----------+----------------------------------------------------+-------------+
44 | Step 2: | Call ap_pay_update_payment_schedule: | PAY |
45 | | | |
46 +----------+----------------------------------------------------+-------------+
47 | Step 3: | Call ap_pay_update_ap_invoices: | PAY |
48 | | | |
49 +----------+----------------------------------------------------+-------------+
50 | Step 4: | Call ap_pay_insert_invoice_payments: | PAY/REV |
51 | | | |
52 +----------+----------------------------------------------------+-------------+
53 *========================================================================*/
54 PROCEDURE ap_pay_invoice(
55 P_invoice_id IN NUMBER,
56 P_check_id IN NUMBER,
57 P_payment_num IN NUMBER,
58 P_invoice_payment_id IN NUMBER,
59 P_old_invoice_payment_id IN NUMBER Default NULL,
60 P_period_name IN VARCHAR2,
61 P_invoice_type IN VARCHAR2 Default NULL,
62 P_accounting_date IN DATE,
63 P_amount IN NUMBER,
64 P_discount_taken IN NUMBER,
65 P_discount_lost IN NUMBER Default NULL,
66 P_invoice_base_amount IN NUMBER Default NULL,
67 P_payment_base_amount IN NUMBER Default NULL,
68 P_accrual_posted_flag IN VARCHAR2,
69 P_cash_posted_flag IN VARCHAR2,
70 P_posted_flag IN VARCHAR2,
71 P_set_of_books_id IN NUMBER,
72 P_last_updated_by IN NUMBER,
73 P_last_update_login IN NUMBER Default NULL,
74 P_currency_code IN VARCHAR2 Default NULL,
75 P_base_currency_code IN VARCHAR2 Default NULL,
76 P_exchange_rate IN NUMBER Default NULL,
77 P_exchange_rate_type IN VARCHAR2 Default NULL,
78 P_exchange_date IN DATE Default NULL,
79 P_ce_bank_acct_use_id IN NUMBER Default NULL,
80 P_bank_account_num IN VARCHAR2 Default NULL,
81 P_bank_account_type IN VARCHAR2 Default NULL,
82 P_bank_num IN VARCHAR2 Default NULL,
83 P_future_pay_posted_flag IN VARCHAR2 Default NULL,
84 P_exclusive_payment_flag IN VARCHAR2 Default NULL,
85 P_accts_pay_ccid IN NUMBER Default NULL,
86 P_gain_ccid IN NUMBER Default NULL,
87 P_loss_ccid IN NUMBER Default NULL,
88 P_future_pay_ccid IN NUMBER Default NULL,
89 P_asset_ccid IN NUMBER Default NULL,
90 P_payment_dists_flag IN VARCHAR2 Default NULL,
91 P_payment_mode IN VARCHAR2 Default NULL,
92 P_replace_flag IN VARCHAR2 Default NULL,
93 P_attribute1 IN VARCHAR2 Default NULL,
94 P_attribute2 IN VARCHAR2 Default NULL,
95 P_attribute3 IN VARCHAR2 Default NULL,
96 P_attribute4 IN VARCHAR2 Default NULL,
97 P_attribute5 IN VARCHAR2 Default NULL,
98 P_attribute6 IN VARCHAR2 Default NULL,
99 P_attribute7 IN VARCHAR2 Default NULL,
100 P_attribute8 IN VARCHAR2 Default NULL,
101 P_attribute9 IN VARCHAR2 Default NULL,
102 P_attribute10 IN VARCHAR2 Default NULL,
103 P_attribute11 IN VARCHAR2 Default NULL,
104 P_attribute12 IN VARCHAR2 Default NULL,
105 P_attribute13 IN VARCHAR2 Default NULL,
106 P_attribute14 IN VARCHAR2 Default NULL,
107 P_attribute15 IN VARCHAR2 Default NULL,
108 P_attribute_category IN VARCHAR2 Default NULL,
109 P_global_attribute1 IN VARCHAR2 Default NULL,
110 P_global_attribute2 IN VARCHAR2 Default NULL,
111 P_global_attribute3 IN VARCHAR2 Default NULL,
112 P_global_attribute4 IN VARCHAR2 Default NULL,
113 P_global_attribute5 IN VARCHAR2 Default NULL,
114 P_global_attribute6 IN VARCHAR2 Default NULL,
115 P_global_attribute7 IN VARCHAR2 Default NULL,
116 P_global_attribute8 IN VARCHAR2 Default NULL,
117 P_global_attribute9 IN VARCHAR2 Default NULL,
118 P_global_attribute10 IN VARCHAR2 Default NULL,
119 P_global_attribute11 IN VARCHAR2 Default NULL,
120 P_global_attribute12 IN VARCHAR2 Default NULL,
121 P_global_attribute13 IN VARCHAR2 Default NULL,
122 P_global_attribute14 IN VARCHAR2 Default NULL,
123 P_global_attribute15 IN VARCHAR2 Default NULL,
124 P_global_attribute16 IN VARCHAR2 Default NULL,
125 P_global_attribute17 IN VARCHAR2 Default NULL,
126 P_global_attribute18 IN VARCHAR2 Default NULL,
127 P_global_attribute19 IN VARCHAR2 Default NULL,
128 P_global_attribute20 IN VARCHAR2 Default NULL,
129 P_global_attribute_category IN VARCHAR2 Default NULL,
130 P_calling_sequence IN VARCHAR2 Default NULL,
131 P_accounting_event_id IN NUMBER Default NULL,
132 P_org_id IN NUMBER Default NULL)
133 IS
134
135 current_calling_sequence VARCHAR2(2000);
136 C_last_update_date DATE;
137 C_discount_lost NUMBER;
138 C_invoice_base_amount NUMBER;
139 C_payment_base_amount NUMBER;
140 C_gain_ccid NUMBER;
141 C_loss_ccid NUMBER;
142 C_period_name VARCHAR2(15);
143 C_accounting_date DATE; --Bug #825450
144
145 BEGIN
146 -- Update the calling sequence
147 --
148 current_calling_sequence := 'AP_PAY_INVOICE_PKG.ap_pay_invoice<-'||P_calling_sequence;
149
150 --
151 -- Transfer some IN variables into local variables
152 C_discount_lost := P_discount_lost;
153 C_invoice_base_amount := P_invoice_base_amount;
154 C_payment_base_amount := P_payment_base_amount;
155 C_gain_ccid := P_gain_ccid;
156 C_loss_ccid := P_loss_ccid;
157 C_period_name := P_period_name;
158 C_accounting_date := P_accounting_date;
159
160 /*---------------------------------------------------------------------------
161 * -- Step 1 : case for all
162 * Call ap_pay_get_info :
163 *
164 *--------------------------------------------------------------------------*/
165 ap_pay_invoice_pkg.ap_pay_get_info(
166 P_invoice_id,
167 P_check_id,
168 P_payment_num,
169 P_invoice_payment_id,
170 P_old_invoice_payment_id,
171 C_period_name,
172 C_accounting_date,
173 P_amount,
174 P_discount_taken,
175 C_discount_lost,
176 C_invoice_base_amount,
177 C_payment_base_amount,
178 P_set_of_books_id,
179 P_currency_code,
180 P_base_currency_code,
181 P_exchange_rate,
182 P_exchange_rate_type,
183 P_exchange_date,
184 P_ce_bank_acct_use_id,
185 P_future_pay_posted_flag,
186 C_gain_ccid,
187 C_loss_ccid,
188 P_payment_dists_flag,
189 P_payment_mode,
190 P_replace_flag,
191 Current_calling_sequence,
192 C_last_update_date);
193
194 --
195 -- Don't do Step 2 and 3, if REV
196 --
197 if (P_PAYMENT_MODE = 'PAY') then
198
199 /*--------------------------------------------------------------------------
200 * -- Step 2 : case for all : Update AP_PAYMENT_SCHEDULES
201 * Call ap_pay_update_payment_schedules :
202 *
203 *--------------------------------------------------------------------------*/
204 ap_pay_invoice_pkg.ap_pay_update_payment_schedule(
205 P_invoice_id,
206 P_payment_num,
207 P_check_id,
208 P_amount,
209 P_discount_taken,
210 P_payment_dists_flag,
211 P_payment_mode,
212 P_replace_flag,
213 P_last_updated_by,
214 C_last_update_date,
215 Current_calling_sequence);
216
217 -- Fix for bug 893626:
218 -- In revision 115.11 of this file, there was an 'end if' here which
219 -- was causing the amount paid in ap_invoices to be updated twice
220 -- which resulted in the bug 893626.
221 -- As mentioned above in the comment, we should not do steps 2 and 3
222 -- for REV.
223 -- Moved the 'end if' after the ap_pay_update_ap_invoices procedure call.
224
225 /*--------------------------------------------------------------------------
226 * -- Step 3 : case for all: Update AP_INVOICES
227 * Call ap_pay_update_ap_invoices :
228 *
229 *--------------------------------------------------------------------------*/
230 ap_pay_invoice_pkg.ap_pay_update_ap_invoices(
231 P_invoice_id,
232 P_check_id,
233 P_amount,
234 P_discount_taken,
235 P_payment_dists_flag,
236 P_payment_mode,
237 P_replace_flag,
238 C_last_update_date,
239 P_last_updated_by,
240 Current_calling_sequence);
241
242 end if;
243
244 /*--------------------------------------------------------------------------
245 * -- Step 4 : case for all : Insert AP_INVOICE_PAYMENTS
246 * Call ap_pay_insert_invoice_payments :
247 *
248 *--------------------------------------------------------------------------*/
249 ap_pay_invoice_pkg.ap_pay_insert_invoice_payments(
250 P_invoice_id,
251 P_check_id,
252 P_payment_num,
253 P_invoice_payment_id,
254 P_old_invoice_payment_id,
255 C_period_name,
256 C_accounting_date,
257 P_amount,
258 P_discount_taken,
259 C_discount_lost,
260 C_invoice_base_amount,
261 C_payment_base_amount,
262 P_accrual_posted_flag,
263 P_cash_posted_flag,
264 P_posted_flag,
265 P_set_of_books_id,
266 P_last_updated_by,
267 P_last_update_login,
268 C_last_update_date,
269 P_currency_code,
270 P_base_currency_code,
271 P_exchange_rate,
272 P_exchange_rate_type,
273 P_exchange_date,
274 P_ce_bank_acct_use_id,
275 P_bank_account_num,
276 P_bank_account_type,
277 P_bank_num,
278 P_future_pay_posted_flag,
279 P_exclusive_payment_flag,
280 P_accts_pay_ccid,
281 C_gain_ccid,
282 C_loss_ccid,
283 P_future_pay_ccid,
284 P_asset_ccid,
285 P_payment_dists_flag,
286 P_payment_mode,
287 P_replace_flag,
288 P_attribute1,
289 P_attribute2,
290 P_attribute3,
291 P_attribute4,
292 P_attribute5,
293 P_attribute6,
294 P_attribute7,
295 P_attribute8,
296 P_attribute9,
297 P_attribute10,
298 P_attribute11,
299 P_attribute12,
300 P_attribute13,
301 P_attribute14,
302 P_attribute15,
303 P_attribute_category,
304 P_global_attribute1,
305 P_global_attribute2,
306 P_global_attribute3,
307 P_global_attribute4,
308 P_global_attribute5,
309 P_global_attribute6,
310 P_global_attribute7,
311 P_global_attribute8,
312 P_global_attribute9,
313 P_global_attribute10,
314 P_global_attribute11,
315 P_global_attribute12,
316 P_global_attribute13,
317 P_global_attribute14,
318 P_global_attribute15,
319 P_global_attribute16,
320 P_global_attribute17,
321 P_global_attribute18,
322 P_global_attribute19,
323 P_global_attribute20,
324 P_global_attribute_category,
325 Current_calling_sequence,
326 P_accounting_event_id,
327 P_org_id);
328
329 END ap_pay_invoice;
330
331
332
333 /*==========================================================================
334 This procedure is responsible for getting values from several different
335 database column.
336 *=====================================================================*/
337 PROCEDURE ap_pay_get_info(
338 P_invoice_id IN NUMBER,
339 P_check_id IN NUMBER,
340 P_payment_num IN NUMBER,
341 P_invoice_payment_id IN NUMBER,
342 P_old_invoice_payment_id IN NUMBER,
343 P_period_name IN OUT NOCOPY VARCHAR2,
344 P_accounting_date IN OUT NOCOPY DATE,
345 P_amount IN NUMBER,
346 P_discount_taken IN NUMBER,
347 P_discount_lost IN OUT NOCOPY NUMBER,
348 P_invoice_base_amount IN OUT NOCOPY NUMBER,
349 P_payment_base_amount IN OUT NOCOPY NUMBER,
350 P_set_of_books_id IN NUMBER,
351 P_currency_code IN VARCHAR2,
352 P_base_currency_code IN VARCHAR2,
353 P_exchange_rate IN NUMBER,
354 P_exchange_rate_type IN VARCHAR2,
355 P_exchange_date IN DATE,
356 P_ce_bank_acct_use_id IN NUMBER,
357 P_future_pay_posted_flag IN VARCHAR2,
358 P_gain_ccid IN OUT NOCOPY NUMBER,
359 P_loss_ccid IN OUT NOCOPY NUMBER,
360 P_payment_dists_flag IN VARCHAR2,
361 P_payment_mode IN VARCHAR2,
362 P_replace_flag IN VARCHAR2,
363 P_calling_sequence IN VARCHAR2,
364 P_last_update_date OUT NOCOPY DATE) IS
365
366 debug_info VARCHAR2(100);
367 current_calling_sequence VARCHAR2(2000);
368 PS_payment_cross_rate NUMBER;
369 AI_exchange_rate NUMBER;
370 PS_disc_amt_available NUMBER;
371 PS_gross_amount NUMBER;
372 C_inv_currency_code VARCHAR2(15);
373 AI_payment_cross_rate_date DATE;
374 AI_payment_cross_rate_type VARCHAR2(30);
375 AI_exchange_date DATE;
376 AI_exchange_rate_type VARCHAR2(30);
377 l_gl_date DATE;
378
379 BEGIN
380 -- Update the calling sequence
381 --
382 current_calling_sequence := 'ap_pay_get_info<-'||P_calling_sequence;
383
384 ------------------------------------------------
385 -- Case for all
386 -- Get period_name and gl_date if they are null
387 ---------------------------------------------------------------------
388
389 if (P_period_name IS NULL) then
390 BEGIN
391 debug_info := 'Get period_name';
392 SELECT G.period_name
393 INTO P_period_name
394 FROM gl_period_statuses G, ap_system_parameters P
395 WHERE G.application_id = 200
396 AND G.set_of_books_id = P.set_of_books_id
397 AND DECODE(P_accounting_date, '',
398 sysdate, P_accounting_date) between G.start_date and G.end_date
399 AND G.closing_status in ('O', 'F')
400 AND NVL(G.adjustment_period_flag, 'N') = 'N';
401 -- Bug 825450. Added select statement so that if the current period is
402 -- not 'open' or 'future-entry' then select the next such available period.
403 EXCEPTION WHEN NO_DATA_FOUND THEN
404 BEGIN
405 SELECT G.start_date, G.period_name
406 INTO l_gl_date, P_period_name
407 FROM gl_period_statuses G, ap_system_parameters P
408 WHERE G.application_id = 200
409 AND G.set_of_books_id = P.set_of_books_id
410 AND G.start_date = (SELECT min(G1.start_date)
411 FROM gl_period_statuses G1
412 WHERE G1.application_id = 200
413 AND G1.set_of_books_id = P.set_of_books_id
414 AND G1.start_date > DECODE(P_accounting_date, '',
415 sysdate, P_accounting_date)
416 AND G1.closing_status in ('O', 'F')
417 AND NVL(G1.adjustment_period_flag, 'N') = 'N'
418 )
419 AND G.closing_status in ('O', 'F')
420 AND NVL(G.adjustment_period_flag, 'N') = 'N';
421
422 P_accounting_date := l_gl_date;
423 END ;
424 END;
425
426 end if;
427
428 ------------------------------------------------
429 -- Case for all
430 -- Populate some required fields
431 ------------------------------------------------
432
433 P_last_update_date := sysdate;
434
435 ------------------------------------------------
436 -- Case for PAY
437 -- Get base_amount and gain loss CCID
438 ------------------------------------------------
439
440 -- Bug 590200: Need to populate the base amount cols if
441 -- either the inv or pay currency is not the same as the
442 -- base currency
443
444 debug_info := 'Get payment base amount';
445 SELECT PS.payment_cross_rate,
446 AI.payment_cross_rate_date,
447 AI.payment_cross_rate_type,
448 AI.exchange_rate,
449 AI.exchange_date,
450 AI.exchange_rate_type,
451 AI.invoice_currency_code
452 INTO PS_payment_cross_rate,
453 AI_payment_cross_rate_date,
454 AI_payment_cross_rate_type,
455 AI_exchange_rate,
456 AI_exchange_date,
457 AI_exchange_rate_type,
458 c_inv_currency_code
459 FROM ap_payment_schedules PS, ap_invoices AI
460 WHERE PS.invoice_id = P_invoice_id
461 AND PS.payment_num = P_payment_num
462 AND AI.invoice_id = P_invoice_id;
463
464 if ((P_currency_code = P_base_currency_code) AND
465 (c_inv_currency_code = P_base_currency_code) AND
466 (P_PAYMENT_MODE = 'PAY')) then
467 P_GAIN_CCID := '';
468 P_LOSS_CCID := '';
469 P_INVOICE_BASE_AMOUNT := '';
470 P_PAYMENT_BASE_AMOUNT := '';
471
472 elsif ((P_PAYMENT_MODE = 'PAY') AND
473 ((P_currency_code <> P_base_currency_code) OR
474 (c_inv_currency_code <> P_base_currency_code))) then
475
476 if (P_currency_code = P_base_currency_code) then
477 P_payment_base_amount := ap_utilities_pkg.ap_round_currency(
478 P_amount, P_base_currency_code);
479 else
480 if (p_exchange_rate_type = 'User') then
481 P_payment_base_amount := ap_utilities_pkg.ap_round_currency(
482 (P_amount
483 *P_exchange_rate),P_base_currency_code);
484 else
485 If (p_exchange_rate is not null) then
486 P_payment_base_amount :=
487 gl_currency_api.convert_amount (
488 p_currency_code,
489 p_base_currency_code,
490 p_exchange_date,
491 p_exchange_rate_type,
492 p_amount);
493 End if;
494 end if;
495 end if;
496
497 if (c_inv_currency_code = P_base_currency_code) then
498 P_invoice_base_amount :=
499 gl_currency_api.convert_amount (
500 p_currency_code,
501 p_base_currency_code,
502 AI_payment_cross_rate_date,
503 AI_payment_cross_rate_type,
504 P_amount);
505
506 else
507 if (AI_exchange_rate_type = 'User') then
508 P_invoice_base_amount := ap_utilities_pkg.ap_round_currency(
509 ((P_amount / PS_payment_cross_rate)
510 *AI_exchange_rate),P_base_currency_code);
511 else
512 -- techinically an invoice cannot reach this point without an
513 -- exchange rate...Adding the If stmt for any corner case missed.
514 If (AI_exchange_rate is not null) then
515
516 -- Bug fix: 969285
517 -- Commented the call to 'gl_currency_api' and added the
518 -- call to 'ap_utilities_pkg'
519
520 P_invoice_base_amount:= ap_utilities_pkg.ap_round_currency
521 (((P_amount/PS_payment_cross_rate)* AI_exchange_rate),
522 P_base_currency_code);
523
524 /* P_invoice_base_amount :=
525 gl_currency_api.convert_amount (
526 p_currency_code,
527 p_base_currency_code,
528 AI_exchange_date,
529 AI_exchange_rate_type,
530 P_amount); */
531 End if;
532 end if;
533 end if;
534
535 debug_info := 'Get CCID';
536 SELECT gain_code_combination_id, loss_code_combination_id
537 INTO P_gain_ccid,
538 P_loss_ccid
539 FROM ce_gl_accounts_ccid CGAC
540 WHERE CGAC.bank_acct_use_id = P_ce_bank_acct_use_id;
541
542 elsif (P_PAYMENT_MODE = 'REV') then
543
544 -- Fix for bug 905158
545 -- For reversals, we want negated base_amounts from the old invoice payment
546 --
547 SELECT DECODE(invoice_base_amount ,'','',
548 0-NVL(invoice_base_amount,0)),
549 DECODE(payment_base_amount ,'','',
550 0-NVL(payment_base_amount,0))
551 INTO P_invoice_base_amount, P_payment_base_amount
552 FROM ap_invoice_payments
553 WHERE invoice_payment_id = P_old_invoice_payment_id;
554
555 end if;
556
557
558 ------------------------------------------------
559 -- Case for PAY
560 -- Get discount_lost
561 ------------------------------------------------
562 if (P_PAYMENT_MODE = 'PAY') then
563 debug_info := 'Get discount lost';
564 SELECT greatest (nvl(PS.discount_amount_available,0),
565 nvl(PS.second_disc_amt_available,0),
566 nvl(PS.third_disc_amt_available,0)),
567 ps.gross_amount
568 INTO PS_disc_amt_available,
569 PS_gross_amount
570 FROM ap_payment_schedules ps
571 WHERE invoice_id = P_invoice_id
572 AND payment_num = P_payment_num;
573
574 --
575 -- Calculate the discount_loss
576 --
577 if (PS_gross_amount <> 0) then
578 P_discount_lost :=
579 ap_utilities_pkg.ap_round_currency(
580 (((P_amount + P_discount_taken)/PS_gross_amount *
581 PS_disc_amt_available) -
582 P_discount_taken), P_currency_code);
583 else
584 P_discount_lost := 0;
585 end if;
586 end if;
587
588
589 EXCEPTION
590
591 WHEN NO_DATA_FOUND then
592 if (debug_info = 'check void') then
593 FND_MESSAGE.SET_NAME('SQLAP', 'AP_INP_MUST_POST_VOID');
594 APP_EXCEPTION.RAISE_EXCEPTION;
595 elsif(debug_info ='Get period_name') then
596 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
597 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
598 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
599 FND_MESSAGE.SET_TOKEN('DEBUG_INFO','the GL_date(sysdate) is not in an open period');
600 APP_EXCEPTION.RAISE_EXCEPTION;
601 end if;
602
603 WHEN OTHERS then
604 if (SQLCODE <> -20001 ) then
605 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
606 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
607 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
608 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(P_invoice_id)
609 ||', Payment_num = '||TO_CHAR(P_payment_num)
610 ||', Check_id = '||TO_CHAR(P_check_id)
611 ||', Invoice_payment_id = '||TO_CHAR(P_invoice_payment_id)
612 ||', Old Invoice_payment_id = '||TO_CHAR(P_old_invoice_payment_id)
613 ||', Accounting_date = '||TO_CHAR(P_accounting_date)
614 ||', Period_name = '||P_period_name
615 ||', Amount = '||TO_CHAR(P_amount)
616 ||', discount_taken = '||TO_CHAR(P_discount_taken)
617 ||', discount_lost = '||TO_CHAR(P_discount_lost)
618 ||', invoice_base_amount = '||TO_CHAR(P_invoice_base_amount)
619 ||', payment_base_amount = '||TO_CHAR(P_payment_base_amount)
620 ||', set_of_books_id = '||TO_CHAR(P_set_of_books_id)
621 ||', currency_code = '||P_currency_code
622 ||', base_currency_code = '||P_base_currency_code
623 ||', exchange_rate = '||TO_CHAR(P_exchange_rate)
624 ||', exchange_rate_type = '||P_exchange_rate_type
625 ||', exchange_date = '||TO_CHAR(P_exchange_date)
626 ||', bank_account_id = '||TO_CHAR(P_ce_bank_acct_use_id)
627 ||', future_pay_posted_flag = '||P_future_pay_posted_flag
628 ||', payment_dists_flag = '||P_payment_dists_flag
629 ||', payment_mode = '||P_payment_mode
630 ||', replace_flag = '||P_replace_flag);
631 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
632 end if;
633
634 APP_EXCEPTION.RAISE_EXCEPTION;
635
636 END ap_pay_get_info;
637
638
639
640 /*=========================================================================
641 * update amount_paid, discount_amount_taken and payment_status_flag for
642 * ap_invoices Update AP_INVOICES
643 ==========================================================================*/
644 PROCEDURE ap_pay_update_ap_invoices(
645 P_invoice_id IN NUMBER,
646 P_check_id IN NUMBER,
647 P_amount IN NUMBER,
648 P_discount_taken IN NUMBER,
649 P_payment_dists_flag IN VARCHAR2,
650 P_payment_mode IN VARCHAR2,
651 P_replace_flag IN VARCHAR2,
652 P_last_update_date IN DATE,
653 P_last_updated_by IN NUMBER,
654 P_calling_sequence IN VARCHAR2) IS
655
656 debug_info VARCHAR2(100);
657 current_calling_sequence VARCHAR2(2000);
658
659 BEGIN
660 -- Update the calling sequence
661 --
662 current_calling_sequence := 'ap_pay_update_ap_invoices<-'||P_calling_sequence;
663
664 if (P_PAYMENT_MODE = 'PAY') then
665
666 if (P_REPLACE_FLAG = 'N') then
667
668 debug_info := 'Update ap_invoices (pay)';
669
670 UPDATE ap_invoices
671 SET amount_paid = NVL(amount_paid, 0) + NVL(P_amount, 0),
672 discount_amount_taken = NVL(discount_amount_taken, 0) +
673 NVL(P_discount_taken, 0),
674 payment_status_flag = AP_INVOICES_UTILITY_PKG.get_payment_status( P_invoice_id ),
675 last_update_date = P_last_update_date,
676 last_updated_by = P_last_updated_by
677 WHERE invoice_id = P_invoice_id;
678
679 --Bug 4539462 DBI logging
680 AP_DBI_PKG.Maintain_DBI_Summary
681 (p_table_name => 'AP_INVOICES',
682 p_operation => 'U',
683 p_key_value1 => P_invoice_id,
684 p_calling_sequence => current_calling_sequence);
685
686 elsif (P_REPLACE_FLAG = 'Y') then
687
688 debug_info := 'Update ap_invoices (reissue)';
689
690 UPDATE ap_invoices
691 SET last_update_date = P_last_update_date,
692 last_updated_by = P_last_updated_by
693 WHERE invoice_id = P_invoice_id;
694
695 end if;
696
697 elsif (P_PAYMENT_MODE = 'REV') then
698
699 if (P_REPLACE_FLAG = 'N') then
700
701 debug_info := 'Update ap_invoices (reverse)';
702
703 UPDATE ap_invoices AI
704 SET (amount_paid
705 , discount_amount_taken
706 , payment_status_flag
707 , last_update_date
708 , last_updated_by)
709 = (SELECT AI.amount_paid - SUM(AIP.amount)
710 , NVL(AI.discount_amount_taken,0) -
711 SUM(NVL(AIP.discount_taken,0))
712 , AP_INVOICES_UTILITY_PKG.get_payment_status( P_invoice_id )
713 , P_last_update_date
714 , P_last_updated_by
715 FROM ap_invoice_payments AIP
716 WHERE AIP.invoice_id = P_invoice_id
717 AND AIP.check_id = P_check_id
718 GROUP BY AI.invoice_id
719 , AI.amount_paid
720 , AI.discount_amount_taken
721 , AI.invoice_amount )
722 WHERE AI.invoice_id = P_invoice_id;
723
724 --Bug 4539462 DBI logging
725 AP_DBI_PKG.Maintain_DBI_Summary
726 (p_table_name => 'AP_INVOICES',
727 p_operation => 'U',
728 p_key_value1 => P_invoice_id,
729 p_calling_sequence => current_calling_sequence);
730
731 elsif (P_REPLACE_FLAG = 'Y') then
732
733 debug_info := 'Update ap_invoices (replace)';
734
735 UPDATE ap_invoices
736 SET last_update_date = P_last_update_date,
737 last_updated_by = P_last_updated_by
738 WHERE invoice_id = P_invoice_id;
739
740 end if;
741 end if;
742
743 EXCEPTION
744
745 WHEN OTHERS then
746
747 if (SQLCODE <> -20001 ) then
748 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
749 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
750 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
751 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(P_invoice_id)
752 ||', Check_id = '||TO_CHAR(P_check_id)
753 ||', Amount = '||TO_CHAR(P_amount)
754 ||', discount_taken = '||TO_CHAR(P_discount_taken)
755 ||', Last_updated_by = '||TO_CHAR(P_last_updated_by)
756 ||', Last_update_date = '||TO_CHAR(P_last_update_date)
757 ||', payment_dists_flag = '||P_payment_dists_flag
758 ||', payment_mode = '||P_payment_mode
759 ||', replace_flag = '||P_replace_flag);
760 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
761 end if;
762
763 APP_EXCEPTION.RAISE_EXCEPTION;
764
765 END ap_pay_update_ap_invoices;
766
767
768
769 /*========================================================================
770 *** Pubilic Function ***
771 ************************
772 * This function: Update AP_INVOICE_PAYMENTS
773 * Inserts a new invoice payment line
774 *========================================================================*/
775 PROCEDURE ap_pay_insert_invoice_payments(
776 P_invoice_id IN NUMBER,
777 P_check_id IN NUMBER,
778 P_payment_num IN NUMBER,
779 P_invoice_payment_id IN NUMBER,
780 P_old_invoice_payment_id IN NUMBER,
781 P_period_name IN VARCHAR2,
782 P_accounting_date IN DATE,
783 P_amount IN NUMBER,
784 P_discount_taken IN NUMBER,
785 P_discount_lost IN NUMBER,
786 P_invoice_base_amount IN NUMBER,
787 P_payment_base_amount IN NUMBER,
788 P_accrual_posted_flag IN VARCHAR2,
789 P_cash_posted_flag IN VARCHAR2,
790 P_posted_flag IN VARCHAR2,
791 P_set_of_books_id IN NUMBER,
792 P_last_updated_by IN NUMBER,
793 P_last_update_login IN NUMBER,
794 P_last_update_date IN DATE,
795 P_currency_code IN VARCHAR2,
796 P_base_currency_code IN VARCHAR2,
797 P_exchange_rate IN NUMBER,
798 P_exchange_rate_type IN VARCHAR2,
799 P_exchange_date IN DATE,
800 P_ce_bank_acct_use_id IN NUMBER,
801 P_bank_account_num IN VARCHAR2,
802 P_bank_account_type IN VARCHAR2,
803 P_bank_num IN VARCHAR2,
804 P_future_pay_posted_flag IN VARCHAR2,
805 P_exclusive_payment_flag IN VARCHAR2,
806 P_accts_pay_ccid IN NUMBER,
807 P_gain_ccid IN NUMBER,
808 P_loss_ccid IN NUMBER,
809 P_future_pay_ccid IN NUMBER,
810 P_asset_ccid IN NUMBER,
811 P_payment_dists_flag IN VARCHAR2,
812 P_payment_mode IN VARCHAR2,
813 P_replace_flag IN VARCHAR2,
814 P_attribute1 IN VARCHAR2,
815 P_attribute2 IN VARCHAR2,
816 P_attribute3 IN VARCHAR2,
817 P_attribute4 IN VARCHAR2,
818 P_attribute5 IN VARCHAR2,
819 P_attribute6 IN VARCHAR2,
820 P_attribute7 IN VARCHAR2,
821 P_attribute8 IN VARCHAR2,
822 P_attribute9 IN VARCHAR2,
823 P_attribute10 IN VARCHAR2,
824 P_attribute11 IN VARCHAR2,
825 P_attribute12 IN VARCHAR2,
826 P_attribute13 IN VARCHAR2,
827 P_attribute14 IN VARCHAR2,
828 P_attribute15 IN VARCHAR2,
829 P_attribute_category IN VARCHAR2,
830 P_global_attribute1 IN VARCHAR2 Default NULL,
831 P_global_attribute2 IN VARCHAR2 Default NULL,
832 P_global_attribute3 IN VARCHAR2 Default NULL,
833 P_global_attribute4 IN VARCHAR2 Default NULL,
834 P_global_attribute5 IN VARCHAR2 Default NULL,
835 P_global_attribute6 IN VARCHAR2 Default NULL,
836 P_global_attribute7 IN VARCHAR2 Default NULL,
837 P_global_attribute8 IN VARCHAR2 Default NULL,
838 P_global_attribute9 IN VARCHAR2 Default NULL,
839 P_global_attribute10 IN VARCHAR2 Default NULL,
840 P_global_attribute11 IN VARCHAR2 Default NULL,
841 P_global_attribute12 IN VARCHAR2 Default NULL,
842 P_global_attribute13 IN VARCHAR2 Default NULL,
843 P_global_attribute14 IN VARCHAR2 Default NULL,
844 P_global_attribute15 IN VARCHAR2 Default NULL,
845 P_global_attribute16 IN VARCHAR2 Default NULL,
846 P_global_attribute17 IN VARCHAR2 Default NULL,
847 P_global_attribute18 IN VARCHAR2 Default NULL,
848 P_global_attribute19 IN VARCHAR2 Default NULL,
849 P_global_attribute20 IN VARCHAR2 Default NULL,
850 P_global_attribute_category IN VARCHAR2 Default NULL,
851 P_calling_sequence IN VARCHAR2,
852 P_accounting_event_id IN NUMBER Default NULL,
853 P_org_id IN NUMBER Default NULL) IS
854
855 current_calling_sequence VARCHAR2(2000);
856 debug_info VARCHAR2(100);
857
858 BEGIN
859 -- Update the calling sequence
860 --
861 current_calling_sequence := 'AP_PAY_INVOICE_PKG.ap_pay_insert_invoice_payments<-'||P_calling_sequence;
862
863 debug_info := 'Insert ap_invoice_payments';
864 AP_AIP_TABLE_HANDLER_PKG.Insert_Row(
865 P_invoice_id,
866 P_check_id,
867 P_payment_num,
868 P_invoice_payment_id,
869 P_old_invoice_payment_id,
870 P_period_name,
871 P_accounting_date,
872 P_amount,
873 P_discount_taken,
874 P_discount_lost,
875 P_invoice_base_amount,
876 P_payment_base_amount,
877 P_accrual_posted_flag,
878 P_cash_posted_flag,
879 P_posted_flag,
880 P_set_of_books_id,
881 P_last_updated_by,
882 P_last_update_login,
883 P_last_update_date,
884 P_currency_code,
885 P_base_currency_code,
886 P_exchange_rate,
887 P_exchange_rate_type,
888 P_exchange_date,
889 P_ce_bank_acct_use_id,
890 P_bank_account_num,
891 P_bank_account_type,
892 P_bank_num,
893 P_future_pay_posted_flag,
894 P_exclusive_payment_flag,
895 P_accts_pay_ccid,
896 P_gain_ccid,
897 P_loss_ccid,
898 P_future_pay_ccid,
899 P_asset_ccid,
900 P_payment_dists_flag,
901 P_payment_mode,
902 P_replace_flag,
903 P_attribute1,
904 P_attribute2,
905 P_attribute3,
906 P_attribute4,
907 P_attribute5,
908 P_attribute6,
909 P_attribute7,
910 P_attribute8,
911 P_attribute9,
912 P_attribute10,
913 P_attribute11,
914 P_attribute12,
915 P_attribute13,
916 P_attribute14,
917 P_attribute15,
918 P_attribute_category,
919 P_global_attribute1,
920 P_global_attribute2,
921 P_global_attribute3,
922 P_global_attribute4,
923 P_global_attribute5,
924 P_global_attribute6,
925 P_global_attribute7,
926 P_global_attribute8,
927 P_global_attribute9,
928 P_global_attribute10,
929 P_global_attribute11,
930 P_global_attribute12,
931 P_global_attribute13,
932 P_global_attribute14,
933 P_global_attribute15,
934 P_global_attribute16,
935 P_global_attribute17,
936 P_global_attribute18,
937 P_global_attribute19,
938 P_global_attribute20,
939 P_global_attribute_category,
940 Current_calling_sequence,
941 P_accounting_event_id,
942 P_org_id);
943
944 EXCEPTION
945 WHEN OTHERS then
946
947 if (SQLCODE <> -20001 ) then
948 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
949 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
950 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
951 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(P_invoice_id)
952 ||', Payment_num = '||TO_CHAR(P_payment_num)
953 ||', Check_id = '||TO_CHAR(P_check_id)
954 ||', Invoice_payment_id = '||TO_CHAR(P_invoice_payment_id)
955 ||', Old Invoice_payment_id = '||TO_CHAR(P_old_invoice_payment_id)
956 ||', Accounting_date = '||TO_CHAR(P_accounting_date)
957 ||', Period_name = '||P_period_name
958 ||', Amount = '||TO_CHAR(P_amount)
959 ||', accrual_posted_flag = '||P_accrual_posted_flag
960 ||', cash_posted_flag = '||P_cash_posted_flag
961 ||', posted_flag = '||P_posted_flag
962 ||', discount_taken = '||TO_CHAR(P_discount_taken)
963 ||', discount_lost = '||TO_CHAR(P_discount_lost)
964 ||', invoice_base_amount = '||TO_CHAR(P_invoice_base_amount)
965 ||', payment_base_amount = '||TO_CHAR(P_payment_base_amount)
966 ||', set_of_books_id = '||TO_CHAR(P_set_of_books_id)
967 ||', currency_code = '||P_currency_code
968 ||', base_currency_code = '||P_base_currency_code
969 ||', exchange_rate = '||TO_CHAR(P_exchange_rate)
970 ||', exchange_rate_type = '||P_exchange_rate_type
971 ||', exchange_date = '||TO_CHAR(P_exchange_date)
972 ||', bank_account_id = '||TO_CHAR(P_ce_bank_acct_use_id)
973 ||', bank_account_num = '||P_bank_account_num
974 ||', bank_account_type = '||P_bank_account_type
975 ||', bank_num = '||P_bank_num
976 ||', future_pay_posted_flag = '||P_future_pay_posted_flag
977 ||', exclusive_payment_flag = '||P_exclusive_payment_flag
978 ||', accts_pay_ccid = '||TO_CHAR(P_accts_pay_ccid)
979 ||', gain_ccid = '||TO_CHAR(P_gain_ccid)
980 ||', loss_ccid = '||TO_CHAR(P_loss_ccid)
981 ||', future_pay_ccid= '||TO_CHAR(P_future_pay_ccid)
982 ||', asset_ccid = '||TO_CHAR(P_asset_ccid)
983 ||', attribute1 = '||P_attribute1
984 ||', attribute2 = '||P_attribute2
985 ||', attribute3 = '||P_attribute3
986 ||', attribute4 = '||P_attribute4
987 ||', attribute5 = '||P_attribute5
988 ||', attribute6 = '||P_attribute6
989 ||', attribute7 = '||P_attribute7
990 ||', attribute8 = '||P_attribute8
991 ||', attribute9 = '||P_attribute9
992 ||', attribute10 = '||P_attribute10
993 ||', attribute11 = '||P_attribute11
994 ||', attribute12 = '||P_attribute12
995 ||', attribute13 = '||P_attribute13
996 ||', attribute14 = '||P_attribute14
997 ||', attribute15 = '||P_attribute15
998 ||', attribute_category = '||P_attribute_category
999 ||', Last_update_by = '||TO_CHAR(P_last_updated_by)
1000 ||', Last_update_date = '||TO_CHAR(P_last_update_date)
1001 ||', Last_update_login = '||TO_CHAR(P_last_update_login)
1002 ||', payment_dists_flag = '||P_payment_dists_flag
1003 ||', payment_mode = '||P_payment_mode
1004 ||', replace_flag = '||P_replace_flag);
1005
1006 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1007 end if;
1008
1009 APP_EXCEPTION.RAISE_EXCEPTION;
1010
1011 end ap_pay_insert_invoice_payments;
1012
1013
1014
1015 /*========================================================================
1016 Update AP_PAYMENT_SCHEDULE
1017 *========================================================================*/
1018 PROCEDURE ap_pay_update_payment_schedule(
1019 P_invoice_id IN NUMBER,
1020 P_payment_num IN NUMBER,
1021 P_check_id IN NUMBER,
1022 P_amount IN NUMBER,
1023 P_discount_taken IN NUMBER,
1024 P_payment_dists_flag IN VARCHAR2,
1025 P_payment_mode IN VARCHAR2,
1026 P_replace_flag IN VARCHAR2,
1027 P_last_updated_by IN NUMBER,
1028 P_last_update_date IN DATE,
1029 P_calling_sequence IN VARCHAR2) IS
1030
1031 debug_info VARCHAR2(100);
1032 current_calling_sequence VARCHAR2(2000);
1033 l_pmt_status_flag AP_PAYMENT_SCHEDULES_ALL.payment_status_flag%TYPE ; -- Bug 8300099
1034 l_another_pmt VARCHAR2(1);
1035 l_prepay_applied VARCHAR2(1);
1036
1037 BEGIN
1038 -- Update the calling sequence
1039 --
1040 current_calling_sequence := 'ap_pay_update_payment_schedule<-'||P_calling_sequence;
1041
1042 if (P_PAYMENT_MODE = 'PAY') then
1043
1044 if (P_REPLACE_FLAG = 'N') then
1045
1046 debug_info := 'Update ap_payment_schedules (pay)';
1047
1048 UPDATE ap_payment_schedules
1049 SET amount_remaining = amount_remaining - P_amount -
1050 NVL(P_discount_taken, 0),
1051 discount_amount_remaining = 0,
1052 payment_status_flag = DECODE(amount_remaining -
1053 P_amount -
1054 NVL(P_discount_taken, 0),
1055 0, 'Y',
1056 amount_remaining, payment_status_flag,
1057 'P'),
1058 last_update_date = P_last_update_date,
1059 last_updated_by = P_last_updated_by
1060 WHERE invoice_id = P_invoice_id
1061 AND payment_num = P_payment_num;
1062
1063 elsif (P_REPLACE_FLAG = 'Y') then
1064
1065 debug_info := 'Update ap_payment_schedules (reissue)';
1066
1067 UPDATE ap_payment_schedules
1068 SET last_update_date = P_last_update_date,
1069 last_updated_by = P_last_updated_by
1070 WHERE invoice_id = P_invoice_id
1071 AND payment_num = P_payment_num;
1072
1073 end if;
1074
1075 elsif (P_PAYMENT_MODE = 'REV') then
1076
1077 if (P_REPLACE_FLAG = 'N') then
1078
1079 debug_info := 'Update ap_payment_schedules (reverse, non-prepayment)';
1080
1081 -- SELECT statement added by Bug 8300099
1082 /* Bug 10640186 begin */
1083 SELECT DECODE( NVL( SUM(AIP.amount), 0 ), 0, 'N', 'Y' )
1084 INTO l_another_pmt
1085 FROM ap_invoice_payments AIP
1086 WHERE AIP.invoice_id = p_invoice_id
1087 AND AIP.payment_num = p_payment_num
1088 AND AIP.check_id <> p_check_id ;
1089
1090
1091 IF (l_another_pmt = 'Y') THEN l_pmt_status_flag := 'P';
1092 ELSE
1093
1094 debug_info := 'Query for prepay applied';
1095 SELECT DECODE( NVL( SUM(AID.amount), 0 ), 0, 'N', 'Y' )
1096 INTO l_prepay_applied
1097 FROM ap_invoice_distributions AID
1098 WHERE AID.invoice_id = p_invoice_id
1099 AND AID.line_type_lookup_code = 'PREPAY';
1100
1101 /* This is written in separate steps so we can use the following
1102 statement to always set the l_pmt_status_flag to N when there
1103 is no prepayment applied (and no other pmt). This way we do
1104 not have to try to allocate invoice time awt for this case.
1105 */
1106 IF (l_prepay_applied = 'N') THEN l_pmt_status_flag := 'N';
1107 ELSE /* Not paid by another payment, but there is a prepayment applied */
1108
1109 /* The difference between gross amount and amount remaining for an
1110 unpaid schedule can have two components -- invoice time awt and
1111 prepament application.
1112 At this point there may or may not be invoice time awt but we
1113 don't need to check. If there is not then the decode stmt below
1114 will correctly set the pmt status flag to N or P.
1115
1116 If there is invoice time awt, then the pmt status flag will only be
1117 set to N if gross amount = amt remaining + payment amount + discount.
1118 It is possible the flag could be incorrectly set to P in a few cases:
1119 For example when gross amount <> amt remaining, there are multiple
1120 payment schedules, but the difference is allocated invoice time awt
1121 and not prepayment.
1122 However this is necessary given the architecture. (We cannot reliably
1123 determine how invoice time awt was originally allocated in this case.)
1124 */
1125
1126 debug_info := 'Compare gross amount to amount remaining plus payment';
1127 SELECT DECODE(APS.gross_amount, APS.amount_remaining
1128 + SUM(AIP.amount)
1129 + SUM(NVL(AIP.discount_taken,0)), 'N', 'P')
1130 INTO l_pmt_status_flag
1131 FROM ap_invoice_payments AIP
1132 , ap_payment_schedules APS
1133 WHERE AIP.invoice_id = P_invoice_id
1134 AND AIP.payment_num = P_payment_num
1135 AND AIP.check_id = P_check_id
1136 AND AIP.invoice_id = APS.invoice_id
1137 AND AIP.payment_num = APS.payment_num
1138 GROUP BY AIP.invoice_id
1139 , AIP.payment_num
1140 , APS.gross_amount
1141 , APS.amount_remaining;
1142
1143 END IF; /*l_prepay_applied = 'N' */
1144
1145 END IF; /*l_another_pmt = 'Y' */
1146 /* END bug 10640186 */
1147
1148 -- bug7353248 nvl added for aps.amount_remaining
1149
1150 UPDATE ap_payment_schedules APS
1151 SET (amount_remaining
1152 , discount_amount_remaining
1153 , payment_status_flag
1154 , last_update_date
1155 , last_updated_by)
1156 = (SELECT nvl(APS.amount_remaining,0) + SUM(AIP.amount)
1157 + SUM(NVL(AIP.discount_taken,0))
1158 , 0
1159 , l_pmt_status_flag
1160 /* Bug 8300099 : Commented the DECODE being used earlier
1161 DECODE(APS.gross_amount, APS.amount_remaining -- Bug 8300099 Commented the fix for 2182168
1162 + SUM(AIP.amount)
1163 + SUM(NVL(AIP.discount_taken,0)), 'N', 'P')/*DECODE(AI.amount_paid,SUM(AIP.amount),'N','P')*/
1164 -- 2182168 modified the decode statement to compare amount_paid to amount cancelled
1165 , P_last_update_date
1166 , P_last_updated_by
1167 FROM ap_invoice_payments AIP,ap_invoices AI --bug2182168 added ap_invoices AI
1168 WHERE AIP.invoice_id = P_invoice_id
1169 AND AIP.payment_num = P_payment_num
1170 AND AIP.check_id = P_check_id
1171 AND AI.invoice_id=P_invoice_id --bug2182168 added condition
1172 GROUP BY AIP.invoice_id
1173 , AIP.payment_num
1174 , APS.gross_amount
1175 , APS.amount_remaining
1176 , APS.discount_amount_remaining
1177 , AI.amount_paid --bug2182168 added amount_paid in group by clause
1178 )
1179 WHERE (invoice_id, payment_num) IN
1180 (SELECT P_invoice_id
1181 , P_payment_num
1182 FROM ap_invoices AI
1183 WHERE AI.invoice_id = P_invoice_id
1184 AND AI.invoice_type_lookup_code <> 'PREPAYMENT');
1185
1186 --
1187 --Bug 992128
1188 --Split the UPDATE into two. First for non-prepayment as above
1189 --and next for PREPAYMENT as below
1190 --
1191
1192 debug_info := 'Update ap_payment_schedules (reverse, prepayment)';
1193
1194 UPDATE ap_payment_schedules APS
1195 SET (amount_remaining
1196 , payment_status_flag
1197 , last_update_date
1198 , last_updated_by)
1199 /* Added for bug 10372009
1200 APS.amount_remaining should be added to the amount which is being reversed
1201 = (SELECT SUM(AIP.amount) + SUM(NVL(AIP.discount_taken, 0)) */
1202 = (SELECT nvl(APS.amount_remaining,0) + SUM(AIP.amount) +
1203 SUM(NVL(AIP.discount_taken, 0))
1204 /* Replaced N with payment status based on amount in AIP for bug 10372009
1205 , 'N' */
1206 , l_pmt_status_flag
1207 , P_last_update_date
1208 , P_last_updated_by
1209 FROM ap_invoice_payments AIP
1210 WHERE AIP.invoice_id = P_invoice_id
1211 AND AIP.check_id = P_check_id
1212 AND AIP.payment_num = APS.payment_num -- Bug 7184181
1213 GROUP BY AIP.invoice_id)
1214 WHERE payment_num = P_payment_num -- Bug 4701565
1215 AND (invoice_id) IN
1216 (SELECT P_invoice_id
1217 FROM ap_invoices AI
1218 WHERE AI.invoice_id = P_invoice_id
1219 AND AI.invoice_type_lookup_code = 'PREPAYMENT');
1220
1221 elsif (P_REPLACE_FLAG = 'Y') then
1222
1223 debug_info := 'Update ap_payment_schedules (replace)';
1224
1225 UPDATE ap_payment_schedules
1226 SET last_update_date = P_last_update_date,
1227 last_updated_by = P_last_updated_by
1228 WHERE invoice_id = P_invoice_id
1229 AND payment_num = P_payment_num;
1230
1231 end if;
1232 end if;
1233
1234 EXCEPTION
1235 WHEN OTHERS then
1236
1237 if (SQLCODE <> -20001 ) then
1238 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1239 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1240 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1241 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(P_invoice_id)
1242 ||', Payment_num = '||TO_CHAR(P_payment_num)
1243 ||', Check_id = '||TO_CHAR(P_check_id)
1244 ||', Amount = '||TO_CHAR(P_amount)
1245 ||', discount_taken = '||TO_CHAR(P_discount_taken)
1246 ||', Last_update_by = '||TO_CHAR(P_last_updated_by)
1247 ||', Last_update_date = '||TO_CHAR(P_last_update_date)
1248 ||', payment_dists_flag = '||P_payment_dists_flag
1249 ||', payment_mode = '||P_payment_mode
1250 ||', replace_flag = '||P_replace_flag);
1251 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1252 end if;
1253
1254 APP_EXCEPTION.RAISE_EXCEPTION;
1255
1256 END ap_pay_update_payment_schedule;
1257
1258 --------------------------------------------------------------------------
1259 -- This Function is used by quick checks to determine and update the check
1260 -- amount.
1261 -------------------------------------------------------------------------
1262 FUNCTION ap_pay_update_check_amount(x_check_id IN NUMBER)
1263 RETURN NUMBER
1264 IS
1265 check_amount NUMBER;
1266 l_debug_info VARCHAR2(100);
1267
1268 BEGIN
1269
1270 SELECT sum(amount)
1271 INTO check_amount
1272 FROM ap_invoice_payments aip
1273 WHERE aip.check_id = x_check_id;
1274
1275 RETURN(check_amount);
1276
1277 EXCEPTION
1278 WHEN NO_DATA_FOUND THEN RETURN(0);
1279
1280 UPDATE ap_checks ac
1281 set amount = check_amount
1282 where ac.check_id = x_check_id;
1283
1284 END ap_pay_update_check_amount;
1285
1286
1287 /****************************************************************
1288 The procedure was created due to bugs 467167, 661795 .
1289 It is used to update the ap_invoices table and ap_payment_schedules
1290 table when reversing an invoice payment. It is called from the
1291 payment workbench.
1292 ******************************************************************/
1293
1294 PROCEDURE ap_inv_pay_update_invoices (
1295 P_org_invoice_pay_id NUMBER,
1296 P_invoice_id NUMBER,
1297 P_payment_line_number NUMBER,
1298 P_last_update_date DATE,
1299 P_last_updated_by NUMBER,
1300 P_calling_sequence VARCHAR2) IS
1301 current_calling_sequence VARCHAR2(2000);
1302 debug_info VARCHAR2(100);
1303 p_amount NUMBER;
1304 p_discount NUMBER;
1305
1306
1307 Begin
1308
1309 -- Update the calling sequence
1310 --
1311 current_calling_sequence := 'ap_inv_pay_update_invoices<-'||P_calling_sequence;
1312
1313 -- Get amount_from org_invoice_pay_id
1314 --
1315 SELECT amount, discount_taken
1316 INTO p_amount, p_discount
1317 FROM ap_invoice_payments
1318 WHERE invoice_payment_id = p_org_invoice_pay_id;
1319
1320
1321 -- Bug 1544895 - The update statement for AP_INVOICES that appears below the update
1322 -- statment for ap_pyment_schedules has been moved from here to after the update
1323 -- statment for ap_pyment_schedules in this procedure.
1324
1325
1326 -- Update ap_payment_schedules
1327 --
1328 UPDATE ap_payment_schedules
1329 SET amount_remaining = amount_remaining + P_amount +
1330 nvl(P_discount,0),
1331 discount_amount_remaining = 0,
1332 payment_status_flag = DECODE(amount_remaining +
1333 P_amount +
1334 NVL(P_discount, 0),
1335 0, 'Y',
1336 gross_amount, 'N',
1337 'P'),
1338 last_update_date = P_last_update_date,
1339 last_updated_by = P_last_updated_by
1340 WHERE invoice_id = P_invoice_id
1341 AND payment_num = P_payment_line_number;
1342
1343 -- Bug 1544895 - Moved the following update statement so that it inserts values
1344 -- in AP_INVOICES after ap_payment_schedules is updates. This will cause
1345 -- the payment_status_flag in AP_INVOICES to have the correct value.
1346
1347 -- Update ap_invoices
1348
1349 UPDATE ap_invoices
1350 SET amount_paid = nvl(amount_paid,0) - P_amount ,
1351 discount_amount_taken =
1352 nvl(discount_amount_taken,0) - nvl(P_discount,0) ,
1353 payment_status_flag = AP_INVOICES_UTILITY_PKG.get_payment_status( P_invoice_id ),
1354 last_update_date = P_last_update_date,
1355 last_updated_by = P_last_updated_by
1356 WHERE invoice_id = P_invoice_id;
1357
1358 --Bug 4539462 DBI logging
1359 AP_DBI_PKG.Maintain_DBI_Summary
1360 (p_table_name => 'AP_INVOICES',
1361 p_operation => 'U',
1362 p_key_value1 => P_invoice_id,
1363 p_calling_sequence => current_calling_sequence);
1364
1365
1366 Exception
1367 WHEN OTHERS then
1368
1369 if (SQLCODE <> -20001 ) then
1370 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1371 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1372 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1373 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(P_invoice_id)
1374 ||', Payment_line_number = '||TO_CHAR(P_payment_line_number)
1375 ||', Amount = '||TO_CHAR(P_amount)
1376 ||', discount_taken = '||TO_CHAR(P_discount)
1377 ||', Last_update_by = '||TO_CHAR(P_last_updated_by)
1378 ||', Last_update_date = '||TO_CHAR(P_last_update_date));
1379
1380 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1381 end if;
1382
1383 APP_EXCEPTION.RAISE_EXCEPTION;
1384
1385 END ap_inv_pay_update_invoices;
1386
1387 END AP_PAY_INVOICE_PKG;