[Home] [Help]
PACKAGE BODY: APPS.AP_INVOICES_POST_PROCESS_PKG
Source
1 PACKAGE BODY AP_INVOICES_POST_PROCESS_PKG AS
2 /* $Header: apinvppb.pls 120.13.12010000.2 2009/01/28 05:29:09 dcshanmu ship $ */
3
4 -----------------------------------------------------------------------
5 -- Procedure create_holds
6 -- Creates invoice limit and vendor holds
7 -- Called for an invoice at POST_UPDATE and POST_INSERT
8 -----------------------------------------------------------------------
9 procedure create_holds (X_invoice_id IN number,
10 X_event IN varchar2 default 'UPDATE',
11 X_update_base IN varchar2 default 'N',
12 X_vendor_changed_flag IN varchar2 default 'N',
13 X_calling_sequence IN varchar2)
14 IS
15 current_calling_sequence VARCHAR2(2000);
16 debug_info VARCHAR2(100);
17 l_invoice_amount AP_INVOICES.invoice_amount%TYPE;
18 l_base_amount AP_INVOICES.base_amount%TYPE;
19 l_invoice_currency_code
20 AP_INVOICES.invoice_currency_code%TYPE;
21 l_invoice_amount_limit
22 PO_VENDOR_SITES.invoice_amount_limit%TYPE;
23 l_base_currency_code
24 AP_SYSTEM_PARAMETERS.base_currency_code%TYPE;
25 l_hold_future_payments_flag
26 PO_VENDOR_SITES.hold_future_payments_flag%TYPE;
27
28 -- perf bug 5052699 - below sql tuned so as to go to base tables
29
30 cursor invoice_cursor is
31 select AI.invoice_amount,
32 AI.base_amount,
33 AI.invoice_currency_code,
34 VS.invoice_amount_limit,
35 SP.base_currency_code,
36 nvl(VS.hold_future_payments_flag,'N')
37 from ap_invoices_all AI,
38 ap_batches_all AB,
39 ap_system_parameters_all SP,
40 po_vendor_sites VS
41 where AI.invoice_id = X_invoice_id
42 and AI.batch_id = AB.batch_id (+)
43 and AI.vendor_site_id = VS.vendor_site_id
44 and sp.org_id = ai.org_id
45 and sp.set_of_books_id = ai.set_of_books_id;
46 BEGIN
47
48 -- Update the calling sequence
49 --
50 current_calling_sequence :=
51 'AP_INVOICES_POST_PROCESS_PKG.create_holds<-'
52 ||X_calling_sequence;
53
54 open invoice_cursor;
55 fetch invoice_cursor
56 into l_invoice_amount,
57 l_base_amount,
58 l_invoice_currency_code,
59 l_invoice_amount_limit,
60 l_base_currency_code,
61 l_hold_future_payments_flag;
62 close invoice_cursor;
63
64 -- Insert amount hold if needed
65 if (l_invoice_amount_limit is not null) then
66
67 -- Compare the limit with the base_amount if the invoice
68 -- is foreign currency or the invoice_amount if the
69 -- invoice is base currency.
70 if ((l_invoice_currency_code = l_base_currency_code and
71 l_invoice_amount > l_invoice_amount_limit) or
72 (l_invoice_currency_code <> l_base_currency_code and
73 l_base_amount > l_invoice_amount_limit)) then
74 --
75 -- Allow hold creation if this is
76 -- (1) a newly created invoice or
77 -- (2) an updated invoice and either the vendor
78 -- has changed or the amount or base amount has changed
79 --
80 if (X_event = 'INSERT' or
81 (X_update_base = 'Y' or
82 X_vendor_changed_flag = 'Y')) then
83 ap_holds_pkg.insert_single_hold(
84 X_invoice_id,
85 'AMOUNT',
86 'INVOICE HOLD REASON',
87 '',
88 5,
89 current_calling_sequence);
90 end if;
91 else
92 -- Release the invoice amount hold if one exists
93 ap_holds_pkg.release_single_hold(
94 X_invoice_id,
95 'AMOUNT',
96 'AMOUNT LOWERED',
97 5,
98 current_calling_sequence);
99 end if;
100 end if;
101
102 -- Insert vendor hold if needed
103 if (l_hold_future_payments_flag = 'Y') then
104 --
105 -- Allow hold creation if this is
106 -- (1) a newly created invoice or
107 -- (2) an updated invoice and the vendor has changed
108 --
109 if (X_event = 'INSERT' or
110 X_vendor_changed_flag = 'Y') then
111
112 ap_holds_pkg.insert_single_hold(
113 X_invoice_id,
114 'VENDOR',
115 'INVOICE HOLD REASON',
116 '',
117 5,
118 current_calling_sequence);
119
120 end if;
121
122 else
123 -- Release the vendor hold if one exists
124 ap_holds_pkg.release_single_hold(
125 X_invoice_id,
126 'VENDOR',
127 'VENDOR UPDATED',
128 5,
129 current_calling_sequence);
130 end if;
131
132 EXCEPTION
133 WHEN OTHERS THEN
134 IF (SQLCODE <> -20001) THEN
135 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
136 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
137 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
138 current_calling_sequence);
139 FND_MESSAGE.SET_TOKEN('PARAMETERS',
140 'X_invoice_id = '||X_invoice_id
141 ||'X_event = '||X_event
142 ||'X_update_base = '||X_update_base
143 ||'X_vendor_changed_flag = '||X_vendor_changed_flag
144 );
145 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
146 END IF;
147 APP_EXCEPTION.RAISE_EXCEPTION;
148
149 END create_holds;
150
151
152 -----------------------------------------------------------------------
153 -- Procedure insert_children
154 -- Inserts child records into AP_HOLDS, AP_PAYMENT_SCHEDULES,
155 -- and AP_INVOICE_DISTRIBUTIONS
156 -- PRECONDITION: Called from PRE-UPDATE, POST-INSERT of INV_SUM_FOLDER
157 -----------------------------------------------------------------------
158 procedure insert_children (
159 X_invoice_id IN NUMBER,
160 X_Payment_Priority IN NUMBER,
161 X_Hold_count IN OUT NOCOPY NUMBER,
162 X_Line_count IN OUT NOCOPY NUMBER,
163 X_Line_Total IN OUT NOCOPY NUMBER,
164 X_calling_sequence IN VARCHAR2,
165 X_Sched_Hold_count IN OUT NOCOPY NUMBER) -- bug 5334577
166
167 IS
168 current_calling_sequence VARCHAR2(2000);
169 debug_info VARCHAR2(1000);
170 l_terms_id AP_INVOICES.terms_id%TYPE;
171 l_created_by AP_INVOICES.created_by%TYPE;
172 l_Last_Updated_By AP_INVOICES.Last_Updated_By%TYPE;
173 l_batch_id AP_INVOICES.batch_id%TYPE;
174 l_terms_date AP_INVOICES.terms_date%TYPE;
175 l_invoice_amount AP_INVOICES.invoice_amount%TYPE;
176 l_pay_curr_invoice_amount AP_INVOICES.invoice_amount%TYPE;
177 l_payment_cross_rate AP_INVOICES.payment_cross_rate%TYPE;
178 l_amt_applicable_to_discount
179 AP_INVOICES.amount_applicable_to_discount%TYPE;
180 l_payment_method_code
181 AP_INVOICES.payment_method_code%TYPE;
182 l_invoice_currency_code
183 AP_INVOICES.invoice_currency_code%TYPE;
184 l_payment_currency_code
185 AP_INVOICES.payment_currency_code%TYPE;
186 l_invoice_type_lookup_code
187 AP_INVOICES.invoice_type_lookup_code%TYPE;
188 l_batch_hold_lookup_code AP_BATCHES.hold_lookup_code%TYPE;
189 l_batch_hold_reason AP_BATCHES.hold_reason%TYPE;
190 l_vendor_id NUMBER;
191 l_vendor_site_id NUMBER;
192 l_invoice_date AP_INVOICES.invoice_date%TYPE;
193 l_error_code VARCHAR2(30);
194 l_msg_data VARCHAR2(30);
195 l_msg_application VARCHAR2(25);
196 l_msg_type VARCHAR2(25);
197 l_debug_context VARCHAR2(2000);
198 l_debug_info VARCHAR2(1000);
199
200 cursor invoice_cursor is
201 select AI.terms_id,
202 AI.last_updated_by,
203 AI.created_by,
204 AI.batch_id,
205 AI.terms_date,
206 AI.invoice_amount,
207 nvl(AI.pay_curr_invoice_amount, invoice_amount),
208 AI.payment_cross_rate,
209 AI.amount_applicable_to_discount,
210 AI.payment_method_code,
211 AI.invoice_currency_code,
212 AI.payment_currency_code,
213 AI.invoice_type_lookup_code,
214 AI.vendor_id,
215 AI.vendor_site_id,
216 AB.hold_lookup_code,
217 AB.hold_reason,
218 AI.invoice_date
219 from ap_invoices AI,
220 ap_batches AB
221 where AI.invoice_id = X_invoice_id
222 and AI.batch_id = AB.batch_id (+);
223
224 BEGIN
225
226 -- Update the calling sequence
227 --
228 current_calling_sequence :=
229 'AP_INVOICES_POST_PROCESS_PKG.insert_children<-'
230 ||X_calling_sequence;
231
232 -- Retrieve the values we need from the newly inserted
233 -- invoice so we can create the payment schedules
234 OPEN invoice_cursor;
235 FETCH invoice_cursor
236 INTO l_terms_id,
237 l_last_updated_by,
238 l_created_by,
239 l_batch_id,
240 l_terms_date,
241 l_invoice_amount,
242 l_pay_curr_invoice_amount,
243 l_payment_cross_rate,
244 l_amt_applicable_to_discount,
245 l_payment_method_code,
246 l_invoice_currency_code,
247 l_payment_currency_code,
248 l_invoice_type_lookup_code,
249 l_vendor_id,
250 l_vendor_site_id,
251 l_batch_hold_lookup_code,
252 l_batch_hold_reason,
253 l_invoice_date;
254 CLOSE invoice_cursor;
255
256 debug_info := 'Create Payment Schedules';
257
258 -- Create the payment schedules
259 AP_CREATE_PAY_SCHEDS_PKG.AP_Create_From_Terms(
260 X_invoice_id,
261 l_terms_id,
262 l_last_updated_by,
263 l_created_by,
264 X_payment_priority,
265 l_batch_id,
266 l_terms_date,
267 l_invoice_amount,
268 l_pay_curr_invoice_amount,
269 l_payment_cross_rate,
270 l_amt_applicable_to_discount,
271 l_payment_method_code,
272 l_invoice_currency_code,
273 l_payment_currency_code,
274 current_calling_sequence);
275
276 debug_info := 'Create batch hold';
277
278 -- Insert the batch-level hold if one exists
279 if (l_batch_hold_lookup_code is not null) then
280
281 ap_holds_pkg.insert_single_hold(
282 X_invoice_id,
283 l_batch_hold_lookup_code,
284 '',
285 l_batch_hold_reason,
286 '',
287 current_calling_sequence);
288
289 end if;
290
291 -- Get the new Lines and hold counts
292
293 debug_info := 'Select counts and sum of amounts from lines and holds';
294
295 select count(*)
296 into X_Hold_count
297 from ap_holds
298 where invoice_id = X_invoice_id
299 and release_lookup_code is null;
300
301 --bug 5334577
302 Select count(*)
303 into X_Sched_Hold_count
304 from ap_payment_schedules_all
305 where invoice_id = X_invoice_id
306 and hold_flag = 'Y';
307
308 select count(*)
309 into X_Line_count
310 from ap_invoice_lines
311 where invoice_id = X_invoice_id;
312
313 select sum(amount)
314 into X_Line_total
315 from ap_invoice_lines
316 where invoice_id = X_invoice_id;
317
318 EXCEPTION
319 WHEN OTHERS THEN
320 IF (SQLCODE <> -20001) THEN
321 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
322 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
323 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
324 current_calling_sequence);
325 FND_MESSAGE.SET_TOKEN('PARAMETERS',
326 ' X_Invoice_Id = ' ||TO_CHAR(X_invoice_id));
327 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
328 END IF;
329 APP_EXCEPTION.RAISE_EXCEPTION;
330
331 END insert_children;
332
333
334 -----------------------------------------------------------------------
335 -- Procedure invoice_pre_update
336 -- Checks to see if payment schedules should be recalculated.
337 -- Performs a liability adjustment on paid or partially paid invoices.
338 -- Determines whether match_status_flag's should be reset on all
339 -- distributions after the commit has occurred.
340 -- PRECONDITION: Called during PRE-UPDATE
341 -----------------------------------------------------------------------
342 procedure invoice_pre_update (
343 X_invoice_id IN number,
344 X_invoice_amount IN number,
345 X_payment_status_flag IN OUT NOCOPY varchar2,
346 X_invoice_type_lookup_code IN varchar2,
347 X_last_updated_by IN number,
348 X_accts_pay_ccid IN number,
349 X_terms_id IN number,
350 X_terms_date IN date,
351 X_discount_amount IN number,
352 X_exchange_rate_type IN varchar2,
353 X_exchange_date IN date,
354 X_exchange_rate IN number,
355 X_vendor_id IN number,
356 X_payment_method_code IN varchar2,
357 X_message1 IN OUT NOCOPY varchar2,
358 X_message2 IN OUT NOCOPY varchar2,
359 X_reset_match_status IN OUT NOCOPY varchar2,
360 X_vendor_changed_flag IN OUT NOCOPY varchar2,
361 X_recalc_pay_sched IN OUT NOCOPY varchar2,
362 X_liability_adjusted_flag IN OUT NOCOPY varchar2,
363 X_external_bank_account_id IN NUMBER, --bug 7714053
364 X_calling_sequence IN varchar2,
365 X_revalidate_ps IN OUT NOCOPY varchar2)
366 IS
367 current_calling_sequence VARCHAR2(2000);
368 debug_info VARCHAR2(100);
369
370 cursor liability_changed_cursor is
371 SELECT 'Y'
372 FROM ap_invoices AI,
373 financials_system_parameters FSP
374 WHERE invoice_id = X_invoice_id
375 AND (AI.accts_pay_code_combination_id <> X_accts_pay_ccid OR
376 --
377 -- The following have been added in order to
378 -- completely externalize the tests for match status
379 -- reset on the server. We want to reset the match
380 -- status flag if
381 -- Encumbrance is not on *AND*
382 -- One of the following columns' values has changed
383 --
384 -- (1) invoice_amount
385 -- (2) exchange_rate_type
386 -- (3) exchange_date
387 -- (4) exchange_rate
388 --
389 invoice_amount <> X_invoice_amount OR
390 nvl(AI.exchange_rate_type,'dummy') <>
391 nvl(X_exchange_rate_type,'dummy') OR
392 nvl(AI.exchange_date,sysdate-9000) <>
393 nvl(X_exchange_date,sysdate-9000) OR
394 nvl(AI.exchange_rate,-1) <> nvl(X_exchange_rate,-1))
395 AND FSP.purch_encumbrance_flag <> 'Y';
396
397 cursor vendor_changed_cursor is
398 SELECT 'Y'
399 FROM ap_invoices
400 WHERE vendor_id <> X_vendor_id
401 AND invoice_id = X_invoice_id;
402
403 cursor recalc_pay_sched_cursor is
404 --
405 -- Determine whether payment schedules should
406 -- be "recalculated"; that is, should we delete the
407 -- existing payment schedules and insert new
408 -- ones based on certain new invoice values.
409 -- Recalculate payment schedules if there are no
410 -- recorded payments or discounts (payment_status_flag = 'N')
411 -- and at least one of the following invoice values has changed
412 --
413 -- (1) invoice amount,
414 -- (2) terms,
415 -- (3) terms date,
416 -- (4) payment method (new for 10SC),
417 -- (5) amount applicable to discount
418 --
419 SELECT 'Y'
420 FROM ap_invoices AI
421 WHERE invoice_id = X_invoice_id
422 AND (AI.invoice_amount <> X_invoice_amount OR
423 AI.terms_id <> X_terms_id OR
424 AI.terms_date <> X_terms_date OR
425 AI.payment_method_code <>
426 X_payment_method_code OR
427 AI.amount_applicable_to_discount <> X_discount_amount OR --bug 7714053
428 AI.external_bank_account_id <> X_external_bank_account_id) --bug 7714053
429 AND X_payment_status_flag = 'N';
430
431 BEGIN
432
433 -- Update the calling sequence
434 --
435 current_calling_sequence :=
436 'AP_INVOICES_POST_PROCESS_PKG.invoice_pre_update<-'||X_calling_sequence;
437
438 -- Determine whether the vendor has changed
439 open vendor_changed_cursor;
440 fetch vendor_changed_cursor into X_vendor_changed_flag;
441 close vendor_changed_cursor;
442
443 -- If the user has changed the liability account and encumbrance
444 -- is off then we must reset the match status flag of unposted
445 -- distributions to N
446 open liability_changed_cursor;
447 fetch liability_changed_cursor into X_reset_match_status;
448 close liability_changed_cursor;
449
450 -- If the invoice is paid or partially paid then we made need
451 -- to alter the payment schedules if a liability adjustment
452 -- has been made.
453 if (X_payment_status_flag <> 'N') then
454 AP_PAYMENT_SCHEDULES_PKG.adjust_pay_schedule(
455 X_invoice_id,
456 X_invoice_amount,
457 X_payment_status_flag,
458 X_invoice_type_lookup_code,
459 X_last_updated_by,
460 X_message1,
461 X_message2,
462 X_reset_match_status,
463 X_liability_adjusted_flag,
464 current_calling_sequence,
465 'APXINWKB',
466 X_revalidate_ps);
467 X_recalc_pay_sched := 'N';
468 end if;
469
470 -- Do not need to recalc if all important fields are unchanged
471 open recalc_pay_sched_cursor;
472 fetch recalc_pay_sched_cursor into X_recalc_pay_sched;
473 close recalc_pay_sched_cursor;
474
475 EXCEPTION
476 WHEN OTHERS THEN
477 IF (SQLCODE <> -20001) THEN
478 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
479 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
480 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
481 current_calling_sequence);
482 FND_MESSAGE.SET_TOKEN('PARAMETERS',
483 'X_invoice_id = '||X_invoice_id
484 ||', X_invoice_amount = '||X_invoice_amount
485 ||', X_payment_status_flag = '||X_payment_status_flag
486 ||', X_invoice_type_lookup_code = '||X_invoice_type_lookup_code
487 ||', X_last_updated_by = ' ||X_last_updated_by
488 ||', X_accts_pay_ccid = ' ||X_accts_pay_ccid
489 ||', X_terms_id = ' ||X_terms_id
490 ||', X_terms_date = ' ||X_terms_date
491 ||', X_discount_amount = ' ||X_discount_amount
492 ||', X_message1 = ' ||X_message1
493 ||', X_message2 = ' ||X_message2
494 ||', X_reset_match_status = '||X_reset_match_status
495 ||', X_recalc_pay_sched = ' ||X_recalc_pay_sched
496 ||', X_liability_adjusted_flag = ' ||X_liability_adjusted_flag
497 );
498 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
499 END IF;
500 APP_EXCEPTION.RAISE_EXCEPTION;
501
502 END invoice_pre_update;
503
504 -----------------------------------------------------------------------
505 -- Procedure invoice_post_update
506 -- o Applies/releases invoice limit and vendor holds
507 -- o Recalculates payment schedules if necessary
508 -- PRECONDITION: Called during POST-UPDATE
509 -----------------------------------------------------------------------
510 procedure invoice_post_update (
511 X_invoice_id IN number,
512 X_payment_priority IN number,
513 X_recalc_pay_sched IN OUT NOCOPY varchar2,
514 X_Hold_count IN OUT NOCOPY number,
515 X_update_base IN varchar2,
516 X_vendor_changed_flag IN varchar2,
517 X_calling_sequence IN varchar2,
518 X_Sched_Hold_count IN OUT NOCOPY number) -- bug 5334577
519 IS
520 current_calling_sequence VARCHAR2(2000);
521 debug_info VARCHAR2(100);
522 l_terms_id AP_INVOICES.terms_id%TYPE;
523 l_created_by AP_INVOICES.created_by%TYPE;
524 l_Last_Updated_By AP_INVOICES.Last_Updated_By%TYPE;
525 l_batch_id AP_INVOICES.batch_id%TYPE;
526 l_terms_date AP_INVOICES.terms_date%TYPE;
527 l_invoice_amount AP_INVOICES.invoice_amount%TYPE;
528 l_pay_curr_invoice_amount AP_INVOICES.invoice_amount%TYPE;
529 l_payment_cross_rate AP_INVOICES.payment_cross_rate%TYPE;
530 l_amt_applicable_to_discount
531 AP_INVOICES.amount_applicable_to_discount%TYPE;
532 l_payment_method_code
533 AP_INVOICES.payment_method_code%TYPE;
534 l_invoice_currency_code
535 AP_INVOICES.invoice_currency_code%TYPE;
536 l_payment_currency_code
537 AP_INVOICES.payment_currency_code%TYPE;
538
539 -- bug 2663549 variables declared
540 l_awt_amount NUMBER;
541 l_inv_amt_remaining NUMBER;
542 l_gross_amount NUMBER;
543 -- end bug 2663549
544
545 cursor invoice_cursor is
546 select AI.terms_id,
547 AI.last_updated_by,
548 AI.created_by,
549 AI.batch_id,
550 AI.terms_date,
551 AI.invoice_amount,
552 nvl(AI.pay_curr_invoice_amount, AI.invoice_amount),
553 AI.payment_cross_rate,
554 AI.amount_applicable_to_discount,
555 AI.payment_method_code,
556 AI.invoice_currency_code,
557 AI.payment_currency_code
558 from ap_invoices AI
559 where AI.invoice_id = X_invoice_id;
560
561 BEGIN
562
563 -- Update the calling sequence
564 --
565 current_calling_sequence :=
566 'AP_INVOICES_POST_PROCESS_PKG.invoice_post_update<-'
567 ||X_calling_sequence;
568
569 -- Retrieve the values we need from the recently updated
570 -- invoice so we can create the payment schedules
571 open invoice_cursor;
572 fetch invoice_cursor into
573 l_terms_id,
574 l_last_updated_by,
575 l_created_by,
576 l_batch_id,
577 l_terms_date,
578 l_invoice_amount,
579 l_pay_curr_invoice_amount,
580 l_payment_cross_rate,
581 l_amt_applicable_to_discount,
582 l_payment_method_code,
583 l_invoice_currency_code,
584 l_payment_currency_code;
585 close invoice_cursor;
586
587 -- Get the new Distribution and hold counts
588
589 debug_info := 'Select count from AP_HOLDS';
590
591 select count(*)
592 into X_Hold_count
593 from ap_holds
594 where invoice_id = X_invoice_id
595 and release_lookup_code is null;
596
597 debug_info := 'Recalculate Payment Schedules: '||X_recalc_pay_sched;
598
599 if (X_recalc_pay_sched = 'Y') then
600 -- Create the payment schedules
601 AP_CREATE_PAY_SCHEDS_PKG.AP_Create_From_Terms(
602 X_invoice_id,
603 l_terms_id,
604 l_last_updated_by,
605 l_created_by,
606 X_payment_priority,
607 l_batch_id,
608 l_terms_date,
609 l_invoice_amount,
610 l_pay_curr_invoice_amount,
611 l_payment_cross_rate,
612 l_amt_applicable_to_discount,
613 l_payment_method_code,
614 l_invoice_currency_code,
615 l_payment_currency_code,
616 current_calling_sequence);
617
618 -- bug 2663549 amount_remaining should be adjusted for AWT amount
619 -- after payment_schedule has been recreated.
620 SELECT sum( nvl(amount, 0) )
621 INTO l_awt_amount
622 FROM ap_invoice_distributions
623 WHERE invoice_id = X_invoice_id
624 AND line_type_lookup_code = 'AWT';
625
626 SELECT sum(nvl(amount_remaining,0)), sum(nvl(gross_amount,0))
627 INTO l_inv_amt_remaining, l_gross_amount
628 FROM ap_payment_schedules
629 WHERE invoice_id = X_invoice_id;
630
631 --bug 5334577
632 Select count(*)
633 into X_Sched_Hold_count
634 from ap_payment_schedules_all
635 where invoice_id = X_invoice_id
636 and hold_flag = 'Y';
637
638 debug_info := ' Total Awt Amount: '||l_awt_amount||', '||'Invoice Amount Remaining: '||
639 l_inv_amt_remaining||', '||'Gross Amount: '||l_gross_amount;
640
641 --===================================================================
642 --Prorate the manual AWT against the invoice amount remaining
643 --===================================================================
644 if ((l_inv_amt_remaining <> 0) and (l_awt_amount is not null)) then
645
646 UPDATE ap_payment_schedules
647 SET amount_remaining = (amount_remaining +
648 ap_utilities_pkg.ap_round_currency(
649 (amount_remaining * (l_awt_amount/l_inv_amt_remaining)
650 * l_payment_cross_rate), l_payment_currency_code ) )
651 WHERE invoice_id = X_invoice_id;
652 elsif ((l_inv_amt_remaining = 0) and (l_awt_amount is not null)
653 and (l_gross_amount <> 0)) then /* Bug 5382525 */
654
655 UPDATE ap_payment_schedules
656 SET amount_remaining = (amount_remaining +
657 ap_utilities_pkg.ap_round_currency(
658 (gross_amount * (l_awt_amount/l_gross_amount)
659 * l_payment_cross_rate), l_payment_currency_code) ),
660 payment_status_flag = DECODE(payment_status_flag,
661 'Y','P',payment_status_flag)
662 WHERE invoice_id = X_invoice_id;
663
664 UPDATE ap_invoices
665 SET payment_status_flag = DECODE(payment_status_flag,
666 'Y','P',payment_status_flag)
667 WHERE invoice_id = X_invoice_id;
668 end if;
669 -- end bug 2663549
670 end if;
671
672 EXCEPTION
673 WHEN OTHERS THEN
674 IF (SQLCODE <> -20001) THEN
675 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
676 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
677 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
678 current_calling_sequence);
679 FND_MESSAGE.SET_TOKEN('PARAMETERS',
680 'X_invoice_id = ' ||X_invoice_id
681 ||', X_payment_priority = '||X_payment_priority
682 ||', X_recalc_pay_sched = '||X_recalc_pay_sched
683 ||', X_Hold_count = ' ||X_Hold_count
684 );
685 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
686 END IF;
687 APP_EXCEPTION.RAISE_EXCEPTION;
688
689 END invoice_post_update;
690
691 --Invoice Lines: Distributions, modified the procedure
692 -----------------------------------------------------------------------
693 -- Procedure post_forms_commit
694 -- o Calls distribution procedure which resets match status,
695 -- recalculates base, 1099 info, etc.
696 -- o Determines new invoice-level statuses
697 -- PRECONDITION: Called during POST-FORMS-COMMIT
698 -----------------------------------------------------------------------
699 procedure post_forms_commit
700 (X_invoice_id IN number,
701 X_Line_Number IN number,
702 X_type_1099 IN varchar2,
703 X_income_tax_region IN varchar2,
704 X_vendor_changed_flag IN OUT NOCOPY varchar2,
705 X_update_base IN OUT NOCOPY varchar2,
706 X_reset_match_status IN OUT NOCOPY varchar2,
707 X_update_occurred IN OUT NOCOPY varchar2,
708 X_approval_status_lookup_code IN OUT NOCOPY varchar2,
709 X_holds_count IN OUT NOCOPY number,
710 X_posting_flag IN OUT NOCOPY varchar2,
711 X_amount_paid IN OUT NOCOPY number,
712 X_highest_line_num IN OUT NOCOPY number,
713 X_line_total IN OUT NOCOPY number,
714 X_actual_invoice_count IN OUT NOCOPY number,
715 X_actual_invoice_total IN OUT NOCOPY number,
716 X_calling_sequence IN varchar2,
717 X_sched_holds_count IN OUT NOCOPY number) -- bug 5334577
718
719
720 IS
721
722 current_calling_sequence VARCHAR2(2000);
723 debug_info VARCHAR2(100);
724
725 CURSOR invoice_status_cursor is
726 select
727 AP_INVOICES_PKG.GET_APPROVAL_STATUS(
728 AI.INVOICE_ID,
729 AI.INVOICE_AMOUNT,
730 AI.PAYMENT_STATUS_FLAG,
731 AI.INVOICE_TYPE_LOOKUP_CODE),
732 AP_INVOICES_PKG.GET_HOLDS_COUNT(
733 AI.INVOICE_ID),
734 AP_INVOICES_PKG.GET_SCHED_HOLDS_COUNT( --bug 5334577
735 AI.INVOICE_ID),
736 AP_INVOICES_PKG.GET_POSTING_STATUS(
737 AI.INVOICE_ID),
738 AI.AMOUNT_PAID,
739 AP_INVOICES_PKG.GET_MAX_LINE_NUMBER(
740 AI.INVOICE_ID) + 1,
741 AP_INVOICES_UTILITY_PKG.GET_LINE_TOTAL(
742 AI.INVOICE_ID),
743 decode(AB.BATCH_ID,
744 '',null,
745 AP_BATCHES_PKG.GET_ACTUAL_INV_COUNT(
746 AB.BATCH_ID)),
747 decode(AB.BATCH_ID,
748 '',null,
749 AP_BATCHES_PKG.GET_ACTUAL_INV_AMOUNT(
750 AB.BATCH_ID))
751 from ap_invoices AI,
752 ap_batches_all AB --Bug: 6668692 : Added _all to table name
753 where AI.invoice_id = X_invoice_id
754 and AI.batch_id = AB.batch_id (+);
755 BEGIN
756
757 -- Update the calling sequence
758 --
759 current_calling_sequence :=
760 'AP_INVOICES_POST_PROCESS_PKG.post_forms_commit<-'||X_calling_sequence;
761
762 -- Update the invoice distributions if necessary
763 --
764 if (nvl(X_update_base,'N') = 'Y' or
765 nvl(X_reset_match_status,'N') = 'Y') then
766
767 ap_invoice_distributions_pkg.update_distributions
768 (X_invoice_id,
769 X_line_number,
770 X_type_1099,
771 X_income_tax_region,
772 X_vendor_changed_flag,
773 X_update_base,
774 X_reset_match_status,
775 X_update_occurred,
776 current_calling_sequence);
777
778 end if;
779
780 -- Determine the current invoice statuses
781 --
782
783 debug_info := 'Select invoice statuses from AP_INVOICES';
784
785 open invoice_status_cursor;
786 fetch invoice_status_cursor into X_approval_status_lookup_code,
787 X_holds_count,
788 X_sched_holds_count, --bug 5334577
789 X_posting_flag,
790 X_amount_paid,
791 X_highest_line_num,
792 X_line_total,
793 X_actual_invoice_count,
794 X_actual_invoice_total;
795 close invoice_status_cursor;
796
797 EXCEPTION
798 WHEN OTHERS THEN
799 IF (SQLCODE <> -20001) THEN
800 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
801 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
802 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
803 current_calling_sequence);
804 FND_MESSAGE.SET_TOKEN('PARAMETERS',
805 'X_invoice_id = ' ||X_invoice_id
806 ||', X_type_1099 = ' ||X_type_1099
807 ||', X_income_tax_region = ' ||X_income_tax_region
808 ||', X_vendor_changed_flag = ' ||X_vendor_changed_flag
809 ||', X_update_base = ' ||X_update_base
810 ||', X_reset_match_status = ' ||X_reset_match_status
811 ||', X_update_occurred = ' ||X_update_occurred
812 ||', X_approval_status_lookup_code = '||
813 X_approval_status_lookup_code
814 ||', X_holds_count = ' ||X_holds_count
815 ||', X_posting_flag = ' ||X_posting_flag
816 ||', X_amount_paid = ' ||X_amount_paid
817 ||', X_highest_line_num = ' ||X_highest_line_num
818 ||', X_actual_invoice_count = ' ||X_actual_invoice_count
819 ||', X_actual_invoice_total = ' ||X_actual_invoice_total
820 ||', X_line_total = ' ||X_Line_total );
821 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
822 END IF;
823 APP_EXCEPTION.RAISE_EXCEPTION;
824
825 END post_forms_commit;
826
827 -----------------------------------------------------------------------
828 -- Procedure Select_Summary calculates the initial value for the
829 -- batch (actual) total
830 --
831 -----------------------------------------------------------------------
832 PROCEDURE Select_Summary(X_Batch_ID IN NUMBER,
833 X_Total IN OUT NOCOPY NUMBER,
834 X_Total_Rtot_DB IN OUT NOCOPY NUMBER,
835 X_Calling_Sequence IN VARCHAR2)
836 IS
837 current_calling_sequence VARCHAR2(2000);
838 debug_info VARCHAR2(100);
839 BEGIN
840
841 -- Update the calling sequence
842 --
843 current_calling_sequence :=
844 'AP_INVOICES_POST_PROCESS_PKG.Select_Summary<-'||X_Calling_Sequence;
845
846 debug_info := 'Select from AP_INVOICES';
847
848 select sum(nvl(invoice_amount,0))
849 into X_Total
850 from ap_invoices
851 where Batch_ID = X_Batch_ID;
852
853 X_Total_Rtot_DB := X_Total;
854
855 EXCEPTION
856 WHEN OTHERS THEN
857 if (SQLCODE <> -20001) then
858 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
859 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
860 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
861 FND_MESSAGE.SET_TOKEN('PARAMETERS','Batch Id = '||X_Batch_ID
862 ||',Total = '||X_Total
863 ||',Total RTOT DB = '||
864 X_Total_Rtot_DB);
865 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
866 end if;
867 APP_EXCEPTION.RAISE_EXCEPTION;
868 END Select_Summary;
869
870 END AP_INVOICES_POST_PROCESS_PKG;