[Home] [Help]
PACKAGE BODY: APPS.AP_PAY_INVOICE_PKG
Source
1 PACKAGE BODY AP_PAY_INVOICE_PKG AS
2 /*$Header: apayinvb.pls 120.11 2005/12/28 09:22:08 pranpaul noship $*/
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
1034 BEGIN
1035 -- Update the calling sequence
1036 --
1037 current_calling_sequence := 'ap_pay_update_payment_schedule<-'||P_calling_sequence;
1038
1039 if (P_PAYMENT_MODE = 'PAY') then
1040
1041 if (P_REPLACE_FLAG = 'N') then
1042
1043 debug_info := 'Update ap_payment_schedules (pay)';
1044
1045 UPDATE ap_payment_schedules
1046 SET amount_remaining = amount_remaining - P_amount -
1047 NVL(P_discount_taken, 0),
1048 discount_amount_remaining = 0,
1049 payment_status_flag = DECODE(amount_remaining -
1050 P_amount -
1051 NVL(P_discount_taken, 0),
1052 0, 'Y',
1053 amount_remaining, payment_status_flag,
1054 'P'),
1055 last_update_date = P_last_update_date,
1056 last_updated_by = P_last_updated_by
1057 WHERE invoice_id = P_invoice_id
1058 AND payment_num = P_payment_num;
1059
1060 elsif (P_REPLACE_FLAG = 'Y') then
1061
1062 debug_info := 'Update ap_payment_schedules (reissue)';
1063
1064 UPDATE ap_payment_schedules
1065 SET last_update_date = P_last_update_date,
1066 last_updated_by = P_last_updated_by
1067 WHERE invoice_id = P_invoice_id
1068 AND payment_num = P_payment_num;
1069
1070 end if;
1071
1072 elsif (P_PAYMENT_MODE = 'REV') then
1073
1074 if (P_REPLACE_FLAG = 'N') then
1075
1076 debug_info := 'Update ap_payment_schedules (reverse, non-prepayment)';
1077
1078 UPDATE ap_payment_schedules APS
1079 SET (amount_remaining
1080 , discount_amount_remaining
1081 , payment_status_flag
1082 , last_update_date
1083 , last_updated_by)
1084 = (SELECT APS.amount_remaining + SUM(AIP.amount)
1085 + SUM(NVL(AIP.discount_taken,0))
1086 , 0
1087 , DECODE(AI.amount_paid,SUM(AIP.amount),'N','P')
1088 -- 2182168 modified the decode statement to compare amount_paid to amount cancelled
1089 , P_last_update_date
1090 , P_last_updated_by
1091 FROM ap_invoice_payments AIP,ap_invoices AI --bug2182168 added ap_invoices AI
1092 WHERE AIP.invoice_id = P_invoice_id
1093 AND AIP.payment_num = P_payment_num
1094 AND AIP.check_id = P_check_id
1095 AND AI.invoice_id=P_invoice_id --bug2182168 added condition
1096 GROUP BY AIP.invoice_id
1097 , AIP.payment_num
1098 , APS.gross_amount
1099 , APS.amount_remaining
1100 , APS.discount_amount_remaining
1101 , AI.amount_paid --bug2182168 added amount_paid in group by clause
1102 )
1103 WHERE (invoice_id, payment_num) IN
1104 (SELECT P_invoice_id
1105 , P_payment_num
1106 FROM ap_invoices AI
1107 WHERE AI.invoice_id = P_invoice_id
1108 AND AI.invoice_type_lookup_code <> 'PREPAYMENT');
1109
1110 --
1111 --Bug 992128
1112 --Split the UPDATE into two. First for non-prepayment as above
1113 --and next for PREPAYMENT as below
1114 --
1115
1116 debug_info := 'Update ap_payment_schedules (reverse, prepayment)';
1117
1118 UPDATE ap_payment_schedules APS
1119 SET (amount_remaining
1120 , payment_status_flag
1121 , last_update_date
1122 , last_updated_by)
1123 = (SELECT SUM(AIP.amount) + SUM(NVL(AIP.discount_taken, 0))
1124 , 'N'
1125 , P_last_update_date
1126 , P_last_updated_by
1127 FROM ap_invoice_payments AIP
1128 WHERE AIP.invoice_id = P_invoice_id
1129 AND AIP.check_id = P_check_id
1130 GROUP BY AIP.invoice_id)
1131 WHERE payment_num = P_payment_num -- Bug 4701565
1132 AND (invoice_id) IN
1133 (SELECT P_invoice_id
1134 FROM ap_invoices AI
1135 WHERE AI.invoice_id = P_invoice_id
1136 AND AI.invoice_type_lookup_code = 'PREPAYMENT');
1137
1138 elsif (P_REPLACE_FLAG = 'Y') then
1139
1140 debug_info := 'Update ap_payment_schedules (replace)';
1141
1142 UPDATE ap_payment_schedules
1143 SET last_update_date = P_last_update_date,
1144 last_updated_by = P_last_updated_by
1145 WHERE invoice_id = P_invoice_id
1146 AND payment_num = P_payment_num;
1147
1148 end if;
1149 end if;
1150
1151 EXCEPTION
1152 WHEN OTHERS then
1153
1154 if (SQLCODE <> -20001 ) then
1155 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1156 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1157 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1158 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(P_invoice_id)
1159 ||', Payment_num = '||TO_CHAR(P_payment_num)
1160 ||', Check_id = '||TO_CHAR(P_check_id)
1161 ||', Amount = '||TO_CHAR(P_amount)
1162 ||', discount_taken = '||TO_CHAR(P_discount_taken)
1163 ||', Last_update_by = '||TO_CHAR(P_last_updated_by)
1164 ||', Last_update_date = '||TO_CHAR(P_last_update_date)
1165 ||', payment_dists_flag = '||P_payment_dists_flag
1166 ||', payment_mode = '||P_payment_mode
1167 ||', replace_flag = '||P_replace_flag);
1168 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1169 end if;
1170
1171 APP_EXCEPTION.RAISE_EXCEPTION;
1172
1173 END ap_pay_update_payment_schedule;
1174
1175 --------------------------------------------------------------------------
1176 -- This Function is used by quick checks to determine and update the check
1177 -- amount.
1178 -------------------------------------------------------------------------
1179 FUNCTION ap_pay_update_check_amount(x_check_id IN NUMBER)
1180 RETURN NUMBER
1181 IS
1182 check_amount NUMBER;
1183 l_debug_info VARCHAR2(100);
1184
1185 BEGIN
1186
1187 SELECT sum(amount)
1188 INTO check_amount
1189 FROM ap_invoice_payments aip
1190 WHERE aip.check_id = x_check_id;
1191
1192 RETURN(check_amount);
1193
1194 EXCEPTION
1195 WHEN NO_DATA_FOUND THEN RETURN(0);
1196
1197 UPDATE ap_checks ac
1198 set amount = check_amount
1199 where ac.check_id = x_check_id;
1200
1201 END ap_pay_update_check_amount;
1202
1203
1204 /****************************************************************
1205 The procedure was created due to bugs 467167, 661795 .
1206 It is used to update the ap_invoices table and ap_payment_schedules
1207 table when reversing an invoice payment. It is called from the
1208 payment workbench.
1209 ******************************************************************/
1210
1211 PROCEDURE ap_inv_pay_update_invoices (
1212 P_org_invoice_pay_id NUMBER,
1213 P_invoice_id NUMBER,
1214 P_payment_line_number NUMBER,
1215 P_last_update_date DATE,
1216 P_last_updated_by NUMBER,
1217 P_calling_sequence VARCHAR2) IS
1218 current_calling_sequence VARCHAR2(2000);
1219 debug_info VARCHAR2(100);
1220 p_amount NUMBER;
1221 p_discount NUMBER;
1222
1223
1224 Begin
1225
1226 -- Update the calling sequence
1227 --
1228 current_calling_sequence := 'ap_inv_pay_update_invoices<-'||P_calling_sequence;
1229
1230 -- Get amount_from org_invoice_pay_id
1231 --
1232 SELECT amount, discount_taken
1233 INTO p_amount, p_discount
1234 FROM ap_invoice_payments
1235 WHERE invoice_payment_id = p_org_invoice_pay_id;
1236
1237
1238 -- Bug 1544895 - The update statement for AP_INVOICES that appears below the update
1239 -- statment for ap_pyment_schedules has been moved from here to after the update
1240 -- statment for ap_pyment_schedules in this procedure.
1241
1242
1243 -- Update ap_payment_schedules
1244 --
1245 UPDATE ap_payment_schedules
1246 SET amount_remaining = amount_remaining + P_amount +
1247 nvl(P_discount,0),
1248 discount_amount_remaining = 0,
1249 payment_status_flag = DECODE(amount_remaining +
1250 P_amount +
1251 NVL(P_discount, 0),
1252 0, 'Y',
1253 gross_amount, 'N',
1254 'P'),
1255 last_update_date = P_last_update_date,
1256 last_updated_by = P_last_updated_by
1257 WHERE invoice_id = P_invoice_id
1258 AND payment_num = P_payment_line_number;
1259
1260 -- Bug 1544895 - Moved the following update statement so that it inserts values
1261 -- in AP_INVOICES after ap_payment_schedules is updates. This will cause
1262 -- the payment_status_flag in AP_INVOICES to have the correct value.
1263
1264 -- Update ap_invoices
1265
1266 UPDATE ap_invoices
1267 SET amount_paid = nvl(amount_paid,0) - P_amount ,
1268 discount_amount_taken =
1269 nvl(discount_amount_taken,0) - nvl(P_discount,0) ,
1270 payment_status_flag = AP_INVOICES_UTILITY_PKG.get_payment_status( P_invoice_id ),
1271 last_update_date = P_last_update_date,
1272 last_updated_by = P_last_updated_by
1273 WHERE invoice_id = P_invoice_id;
1274
1275 --Bug 4539462 DBI logging
1276 AP_DBI_PKG.Maintain_DBI_Summary
1277 (p_table_name => 'AP_INVOICES',
1278 p_operation => 'U',
1279 p_key_value1 => P_invoice_id,
1280 p_calling_sequence => current_calling_sequence);
1281
1282
1283 Exception
1284 WHEN OTHERS then
1285
1286 if (SQLCODE <> -20001 ) then
1287 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1288 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1289 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1290 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(P_invoice_id)
1291 ||', Payment_line_number = '||TO_CHAR(P_payment_line_number)
1292 ||', Amount = '||TO_CHAR(P_amount)
1293 ||', discount_taken = '||TO_CHAR(P_discount)
1294 ||', Last_update_by = '||TO_CHAR(P_last_updated_by)
1295 ||', Last_update_date = '||TO_CHAR(P_last_update_date));
1296
1297 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1298 end if;
1299
1300 APP_EXCEPTION.RAISE_EXCEPTION;
1301
1302 END ap_inv_pay_update_invoices;
1303
1304 END AP_PAY_INVOICE_PKG;