[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.7 2010/02/10 08:58:37 asansari 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_all AB --Bug8409056
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_payment_currency_code IN VARCHAR2, --Bug9294551
365 X_calling_sequence IN varchar2,
366 X_revalidate_ps IN OUT NOCOPY varchar2)
367 IS
368 current_calling_sequence VARCHAR2(2000);
369 debug_info VARCHAR2(100);
370
371 l_recouped_amount NUMBER; --bug8891266
372
373 cursor liability_changed_cursor is
374 SELECT 'Y'
375 FROM ap_invoices AI,
376 financials_system_parameters FSP
377 WHERE invoice_id = X_invoice_id
378 AND (AI.accts_pay_code_combination_id <> X_accts_pay_ccid OR
379 --
380 -- The following have been added in order to
381 -- completely externalize the tests for match status
382 -- reset on the server. We want to reset the match
383 -- status flag if
384 -- Encumbrance is not on *AND*
385 -- One of the following columns' values has changed
386 --
387 -- (1) invoice_amount
388 -- (2) exchange_rate_type
389 -- (3) exchange_date
390 -- (4) exchange_rate
391 --
392 invoice_amount <> X_invoice_amount OR
393 nvl(AI.exchange_rate_type,'dummy') <>
394 nvl(X_exchange_rate_type,'dummy') OR
395 nvl(AI.exchange_date,sysdate-9000) <>
396 nvl(X_exchange_date,sysdate-9000) OR
397 nvl(AI.exchange_rate,-1) <> nvl(X_exchange_rate,-1))
398 AND FSP.purch_encumbrance_flag <> 'Y';
399
400 cursor vendor_changed_cursor is
401 SELECT 'Y'
402 FROM ap_invoices
403 WHERE vendor_id <> X_vendor_id
404 AND invoice_id = X_invoice_id;
405
406 --bug 8891266 added cursor parameter here
407 --Bug9294551 : Recalculate payment schedules when payment currency code
408 -- changes. Added X_payment_currency_code check to cursor.
409 cursor recalc_pay_sched_cursor (l_recoup_amt number) is
410 --
411 -- Determine whether payment schedules should
412 -- be "recalculated"; that is, should we delete the
413 -- existing payment schedules and insert new
414 -- ones based on certain new invoice values.
415 -- Recalculate payment schedules if there are no
416 -- recorded payments or discounts (payment_status_flag = 'N')
417 -- and at least one of the following invoice values has changed
418 --
419 -- (1) invoice amount,
420 -- (2) terms,
421 -- (3) terms date,
422 -- (4) payment method (new for 10SC),
423 -- (5) amount applicable to discount
424 -- (6) payment currency code (Bug9294551)
425 SELECT 'Y'
426 FROM ap_invoices AI
427 WHERE invoice_id = X_invoice_id
428 AND (AI.invoice_amount <> X_invoice_amount OR
429 AI.terms_id <> X_terms_id OR
430 AI.terms_date <> X_terms_date OR
431 AI.payment_method_code <> X_payment_method_code OR
432 AI.payment_currency_code <> X_payment_currency_code OR -- Bug9294551
433 AI.amount_applicable_to_discount <> X_discount_amount /*OR --bug 7714053
434 AI.external_bank_account_id <> X_external_bank_account_id*/) --bug 7714053
435 -- commented above code as part of bug 8208495
436 AND (( X_payment_status_flag = 'N') OR
437 (X_payment_status_flag <> 'N' AND (-1*l_recoup_amt) = ai.amount_paid ));
438
439 --bug 8891266 also changed the last condition of Payment status flag
440
441 BEGIN
442
443 -- Update the calling sequence
444 --
445 current_calling_sequence :=
446 'AP_INVOICES_POST_PROCESS_PKG.invoice_pre_update<-'||X_calling_sequence;
447
448 -- Determine whether the vendor has changed
449 open vendor_changed_cursor;
450 fetch vendor_changed_cursor into X_vendor_changed_flag;
451 close vendor_changed_cursor;
452
453 -- If the user has changed the liability account and encumbrance
454 -- is off then we must reset the match status flag of unposted
455 -- distributions to N
456 open liability_changed_cursor;
457 fetch liability_changed_cursor into X_reset_match_status;
458 close liability_changed_cursor;
459
460 --Bug8891266 obtained the recouped added the check of recouped amount
461 -- so that payment schedules are not adjusted , they needs to be recalculated
462
463 l_recouped_amount := AP_MATCHING_UTILS_PKG.Get_Inv_Line_Recouped_Amount
464 (P_Invoice_Id => X_invoice_id,
465 P_Invoice_Line_Number => Null);
466
467 debug_info := 'l_recouped_amount obtained ';
468
469 -- If the invoice is paid or partially paid then we made need
470 -- to alter the payment schedules if a liability adjustment
471 -- has been made.
472
473 if (X_payment_status_flag <> 'N' AND nvl(l_recouped_amount,0) = 0) then
474
475 AP_PAYMENT_SCHEDULES_PKG.adjust_pay_schedule(
476 X_invoice_id,
477 X_invoice_amount,
478 X_payment_status_flag,
479 X_invoice_type_lookup_code,
480 X_last_updated_by,
481 X_message1,
482 X_message2,
483 X_reset_match_status,
484 X_liability_adjusted_flag,
485 current_calling_sequence,
486 'APXINWKB',
487 X_revalidate_ps);
488 X_recalc_pay_sched := 'N';
489 end if;
490
491 -- Do not need to recalc if all important fields are unchanged
492
493 --Bug8891266 added the l_recouped_amount paramter to cursor
494 open recalc_pay_sched_cursor(l_recouped_amount);
495 fetch recalc_pay_sched_cursor into X_recalc_pay_sched;
496 close recalc_pay_sched_cursor;
497
498 EXCEPTION
499 WHEN OTHERS THEN
500 IF (SQLCODE <> -20001) THEN
501 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
502 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
503 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
504 current_calling_sequence);
505 FND_MESSAGE.SET_TOKEN('PARAMETERS',
506 'X_invoice_id = '||X_invoice_id
507 ||', X_invoice_amount = '||X_invoice_amount
508 ||', X_payment_status_flag = '||X_payment_status_flag
509 ||', X_invoice_type_lookup_code = '||X_invoice_type_lookup_code
510 ||', X_last_updated_by = ' ||X_last_updated_by
511 ||', X_accts_pay_ccid = ' ||X_accts_pay_ccid
512 ||', X_terms_id = ' ||X_terms_id
513 ||', X_terms_date = ' ||X_terms_date
514 ||', X_discount_amount = ' ||X_discount_amount
515 ||', X_message1 = ' ||X_message1
516 ||', X_message2 = ' ||X_message2
517 ||', X_reset_match_status = '||X_reset_match_status
518 ||', X_recalc_pay_sched = ' ||X_recalc_pay_sched
519 ||', X_liability_adjusted_flag = ' ||X_liability_adjusted_flag
520 );
521 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
522 END IF;
523 APP_EXCEPTION.RAISE_EXCEPTION;
524
525 END invoice_pre_update;
526
527 -----------------------------------------------------------------------
528 -- Procedure invoice_post_update
529 -- o Applies/releases invoice limit and vendor holds
530 -- o Recalculates payment schedules if necessary
531 -- PRECONDITION: Called during POST-UPDATE
532 -----------------------------------------------------------------------
533 procedure invoice_post_update (
534 X_invoice_id IN number,
535 X_payment_priority IN number,
536 X_recalc_pay_sched IN OUT NOCOPY varchar2,
537 X_Hold_count IN OUT NOCOPY number,
538 X_update_base IN varchar2,
539 X_vendor_changed_flag IN varchar2,
540 X_calling_sequence IN varchar2,
541 X_Sched_Hold_count IN OUT NOCOPY number) -- bug 5334577
542 IS
543 current_calling_sequence VARCHAR2(2000);
544 debug_info VARCHAR2(100);
545 l_terms_id AP_INVOICES.terms_id%TYPE;
546 l_created_by AP_INVOICES.created_by%TYPE;
547 l_Last_Updated_By AP_INVOICES.Last_Updated_By%TYPE;
548 l_batch_id AP_INVOICES.batch_id%TYPE;
549 l_terms_date AP_INVOICES.terms_date%TYPE;
550 l_invoice_amount AP_INVOICES.invoice_amount%TYPE;
551 l_pay_curr_invoice_amount AP_INVOICES.invoice_amount%TYPE;
552 l_payment_cross_rate AP_INVOICES.payment_cross_rate%TYPE;
553 l_amt_applicable_to_discount
554 AP_INVOICES.amount_applicable_to_discount%TYPE;
555 l_payment_method_code
556 AP_INVOICES.payment_method_code%TYPE;
557 l_invoice_currency_code
558 AP_INVOICES.invoice_currency_code%TYPE;
559 l_payment_currency_code
560 AP_INVOICES.payment_currency_code%TYPE;
561
562 -- bug 2663549 variables declared
563 l_awt_amount NUMBER;
564 l_inv_amt_remaining NUMBER;
565 l_gross_amount NUMBER;
566 -- end bug 2663549
567
568 --Bug8891266
569
570 l_recouped_amount NUMBER;
571 l_po_number VARCHAR2(1000);
572
573 cursor invoice_cursor is
574 select AI.terms_id,
575 AI.last_updated_by,
576 AI.created_by,
577 AI.batch_id,
578 AI.terms_date,
579 AI.invoice_amount,
580 nvl(AI.pay_curr_invoice_amount, AI.invoice_amount),
581 AI.payment_cross_rate,
582 AI.amount_applicable_to_discount,
583 AI.payment_method_code,
584 AI.invoice_currency_code,
585 AI.payment_currency_code
586 from ap_invoices AI
587 where AI.invoice_id = X_invoice_id;
588
589 BEGIN
590
591 -- Update the calling sequence
592 --
593 current_calling_sequence :=
594 'AP_INVOICES_POST_PROCESS_PKG.invoice_post_update<-'
595 ||X_calling_sequence;
596
597 -- Retrieve the values we need from the recently updated
598 -- invoice so we can create the payment schedules
599 open invoice_cursor;
600 fetch invoice_cursor into
601 l_terms_id,
602 l_last_updated_by,
603 l_created_by,
604 l_batch_id,
605 l_terms_date,
606 l_invoice_amount,
607 l_pay_curr_invoice_amount,
608 l_payment_cross_rate,
609 l_amt_applicable_to_discount,
610 l_payment_method_code,
611 l_invoice_currency_code,
612 l_payment_currency_code;
613 close invoice_cursor;
614
615 -- Get the new Distribution and hold counts
616
617 debug_info := 'Select count from AP_HOLDS';
618
619 select count(*)
620 into X_Hold_count
621 from ap_holds
622 where invoice_id = X_invoice_id
623 and release_lookup_code is null;
624
625 debug_info := 'Recalculate Payment Schedules: '||X_recalc_pay_sched;
626
627 if (X_recalc_pay_sched = 'Y') then
628 -- Create the payment schedules
629 AP_CREATE_PAY_SCHEDS_PKG.AP_Create_From_Terms(
630 X_invoice_id,
631 l_terms_id,
632 l_last_updated_by,
633 l_created_by,
634 X_payment_priority,
635 l_batch_id,
636 l_terms_date,
637 l_invoice_amount,
638 l_pay_curr_invoice_amount,
639 l_payment_cross_rate,
640 l_amt_applicable_to_discount,
641 l_payment_method_code,
642 l_invoice_currency_code,
643 l_payment_currency_code,
644 current_calling_sequence);
645
646 -- bug 2663549 amount_remaining should be adjusted for AWT amount
647 -- after payment_schedule has been recreated.
648 SELECT sum( nvl(amount, 0) )
649 INTO l_awt_amount
650 FROM ap_invoice_lines -- bug 9255550
651 WHERE invoice_id = X_invoice_id
652 AND line_type_lookup_code = 'AWT';
653
654 SELECT sum(nvl(amount_remaining,0)), sum(nvl(gross_amount,0))
655 INTO l_inv_amt_remaining, l_gross_amount
656 FROM ap_payment_schedules
657 WHERE invoice_id = X_invoice_id;
658
659 --bug 5334577
660 Select count(*)
661 into X_Sched_Hold_count
662 from ap_payment_schedules_all
663 where invoice_id = X_invoice_id
664 and hold_flag = 'Y';
665
666 debug_info := ' Total Awt Amount: '||l_awt_amount||', '||'Invoice Amount Remaining: '||
667 l_inv_amt_remaining||', '||'Gross Amount: '||l_gross_amount;
668
669 --===================================================================
670 --Prorate the manual AWT against the invoice amount remaining
671 --===================================================================
672 if ((l_inv_amt_remaining <> 0) and (l_awt_amount is not null)) then
673
674 UPDATE ap_payment_schedules
675 SET amount_remaining = (amount_remaining +
676 ap_utilities_pkg.ap_round_currency(
677 (amount_remaining * (l_awt_amount/l_inv_amt_remaining)
678 * l_payment_cross_rate), l_payment_currency_code ) )
679 WHERE invoice_id = X_invoice_id;
680 elsif ((l_inv_amt_remaining = 0) and (l_awt_amount is not null)
681 and (l_gross_amount <> 0)) then /* Bug 5382525 */
682
683 UPDATE ap_payment_schedules
684 SET amount_remaining = (amount_remaining +
685 ap_utilities_pkg.ap_round_currency(
686 (gross_amount * (l_awt_amount/l_gross_amount)
687 * l_payment_cross_rate), l_payment_currency_code) ),
688 payment_status_flag = DECODE(payment_status_flag,
689 'Y','P',payment_status_flag)
690 WHERE invoice_id = X_invoice_id;
691
692 UPDATE ap_invoices
693 SET payment_status_flag = DECODE(payment_status_flag,
694 'Y','P',payment_status_flag)
695 WHERE invoice_id = X_invoice_id;
696 end if;
697 -- end bug 2663549
698
699 --Bug8891266
700 l_recouped_amount := AP_MATCHING_UTILS_PKG.Get_Inv_Line_Recouped_Amount
701 (P_Invoice_Id => X_invoice_id,
702 P_Invoice_Line_Number => Null);
703 l_po_number := AP_INVOICES_UTILITY_PKG.get_po_number(X_invoice_id);
704
705
706 debug_info := ' l_recouped_amount : '|| l_recouped_amount ||', '||'l_po_number: '||
707 l_po_number ;
708
709 if( l_po_number <> 'UNMATCHED' ) then
710 if( nvl(l_recouped_amount,0) <> 0) THEN
711 UPDATE ap_payment_schedules
712 SET amount_remaining = (amount_remaining +
713 ap_utilities_pkg.ap_round_currency(l_recouped_amount,
714 l_payment_currency_code) ),
715 payment_status_flag = DECODE(amount_remaining +
716 ap_utilities_pkg.ap_round_currency( l_recouped_amount,
717 l_payment_currency_code),
718 0,'Y',
719 gross_amount, 'N',
720 'P')
721 WHERE invoice_id = X_invoice_id;
722
723
724 end if;
725
726 end if;
727 --End of Bug8891266
728 end if;
729
730 EXCEPTION
731 WHEN OTHERS THEN
732 IF (SQLCODE <> -20001) THEN
733 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
734 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
735 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
736 current_calling_sequence);
737 FND_MESSAGE.SET_TOKEN('PARAMETERS',
738 'X_invoice_id = ' ||X_invoice_id
739 ||', X_payment_priority = '||X_payment_priority
740 ||', X_recalc_pay_sched = '||X_recalc_pay_sched
741 ||', X_Hold_count = ' ||X_Hold_count
742 );
743 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
744 END IF;
745 APP_EXCEPTION.RAISE_EXCEPTION;
746
747 END invoice_post_update;
748
749 --Invoice Lines: Distributions, modified the procedure
750 -----------------------------------------------------------------------
751 -- Procedure post_forms_commit
752 -- o Calls distribution procedure which resets match status,
753 -- recalculates base, 1099 info, etc.
754 -- o Determines new invoice-level statuses
755 -- PRECONDITION: Called during POST-FORMS-COMMIT
756 -----------------------------------------------------------------------
757 procedure post_forms_commit
758 (X_invoice_id IN number,
759 X_Line_Number IN number,
760 X_type_1099 IN varchar2,
761 X_income_tax_region IN varchar2,
762 X_vendor_changed_flag IN OUT NOCOPY varchar2,
763 X_update_base IN OUT NOCOPY varchar2,
764 X_reset_match_status IN OUT NOCOPY varchar2,
765 X_update_occurred IN OUT NOCOPY varchar2,
766 X_approval_status_lookup_code IN OUT NOCOPY varchar2,
767 X_holds_count IN OUT NOCOPY number,
768 X_posting_flag IN OUT NOCOPY varchar2,
769 X_amount_paid IN OUT NOCOPY number,
770 X_highest_line_num IN OUT NOCOPY number,
771 X_line_total IN OUT NOCOPY number,
772 X_actual_invoice_count IN OUT NOCOPY number,
773 X_actual_invoice_total IN OUT NOCOPY number,
774 X_calling_sequence IN varchar2,
775 X_sched_holds_count IN OUT NOCOPY number) -- bug 5334577
776
777
778 IS
779
780 current_calling_sequence VARCHAR2(2000);
781 debug_info VARCHAR2(100);
782
783 CURSOR invoice_status_cursor is
784 select
785 AP_INVOICES_PKG.GET_APPROVAL_STATUS(
786 AI.INVOICE_ID,
787 AI.INVOICE_AMOUNT,
788 AI.PAYMENT_STATUS_FLAG,
789 AI.INVOICE_TYPE_LOOKUP_CODE),
790 AP_INVOICES_PKG.GET_HOLDS_COUNT(
791 AI.INVOICE_ID),
792 AP_INVOICES_PKG.GET_SCHED_HOLDS_COUNT( --bug 5334577
793 AI.INVOICE_ID),
794 AP_INVOICES_PKG.GET_POSTING_STATUS(
795 AI.INVOICE_ID),
796 AI.AMOUNT_PAID,
797 AP_INVOICES_PKG.GET_MAX_LINE_NUMBER(
798 AI.INVOICE_ID) + 1,
799 AP_INVOICES_UTILITY_PKG.GET_LINE_TOTAL(
800 AI.INVOICE_ID),
801 decode(AB.BATCH_ID,
802 '',null,
803 AP_BATCHES_PKG.GET_ACTUAL_INV_COUNT(
804 AB.BATCH_ID)),
805 decode(AB.BATCH_ID,
806 '',null,
807 AP_BATCHES_PKG.GET_ACTUAL_INV_AMOUNT(
808 AB.BATCH_ID))
809 from ap_invoices AI,
810 ap_batches_all AB --Bug: 6668692 : Added _all to table name
811 where AI.invoice_id = X_invoice_id
812 and AI.batch_id = AB.batch_id (+);
813 BEGIN
814
815 -- Update the calling sequence
816 --
817 current_calling_sequence :=
818 'AP_INVOICES_POST_PROCESS_PKG.post_forms_commit<-'||X_calling_sequence;
819
820 -- Update the invoice distributions if necessary
821 --
822 if (nvl(X_update_base,'N') = 'Y' or
823 nvl(X_reset_match_status,'N') = 'Y') then
824
825 ap_invoice_distributions_pkg.update_distributions
826 (X_invoice_id,
827 X_line_number,
828 X_type_1099,
829 X_income_tax_region,
830 X_vendor_changed_flag,
831 X_update_base,
832 X_reset_match_status,
833 X_update_occurred,
834 current_calling_sequence);
835
836 end if;
837
838 -- Determine the current invoice statuses
839 --
840
841 debug_info := 'Select invoice statuses from AP_INVOICES';
842
843 open invoice_status_cursor;
844 fetch invoice_status_cursor into X_approval_status_lookup_code,
845 X_holds_count,
846 X_sched_holds_count, --bug 5334577
847 X_posting_flag,
848 X_amount_paid,
849 X_highest_line_num,
850 X_line_total,
851 X_actual_invoice_count,
852 X_actual_invoice_total;
853 close invoice_status_cursor;
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',
861 current_calling_sequence);
862 FND_MESSAGE.SET_TOKEN('PARAMETERS',
863 'X_invoice_id = ' ||X_invoice_id
864 ||', X_type_1099 = ' ||X_type_1099
865 ||', X_income_tax_region = ' ||X_income_tax_region
866 ||', X_vendor_changed_flag = ' ||X_vendor_changed_flag
867 ||', X_update_base = ' ||X_update_base
868 ||', X_reset_match_status = ' ||X_reset_match_status
869 ||', X_update_occurred = ' ||X_update_occurred
870 ||', X_approval_status_lookup_code = '||
871 X_approval_status_lookup_code
872 ||', X_holds_count = ' ||X_holds_count
873 ||', X_posting_flag = ' ||X_posting_flag
874 ||', X_amount_paid = ' ||X_amount_paid
875 ||', X_highest_line_num = ' ||X_highest_line_num
876 ||', X_actual_invoice_count = ' ||X_actual_invoice_count
877 ||', X_actual_invoice_total = ' ||X_actual_invoice_total
878 ||', X_line_total = ' ||X_Line_total );
879 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
880 END IF;
881 APP_EXCEPTION.RAISE_EXCEPTION;
882
883 END post_forms_commit;
884
885 -----------------------------------------------------------------------
886 -- Procedure Select_Summary calculates the initial value for the
887 -- batch (actual) total
888 --
889 -----------------------------------------------------------------------
890 PROCEDURE Select_Summary(X_Batch_ID IN NUMBER,
891 X_Total IN OUT NOCOPY NUMBER,
892 X_Total_Rtot_DB IN OUT NOCOPY NUMBER,
893 X_Calling_Sequence IN VARCHAR2)
894 IS
895 current_calling_sequence VARCHAR2(2000);
896 debug_info VARCHAR2(100);
897 BEGIN
898
899 -- Update the calling sequence
900 --
901 current_calling_sequence :=
902 'AP_INVOICES_POST_PROCESS_PKG.Select_Summary<-'||X_Calling_Sequence;
903
904 debug_info := 'Select from AP_INVOICES';
905
906 select sum(nvl(invoice_amount,0))
907 into X_Total
908 from ap_invoices
909 where Batch_ID = X_Batch_ID;
910
911 X_Total_Rtot_DB := X_Total;
912
913 EXCEPTION
914 WHEN OTHERS THEN
915 if (SQLCODE <> -20001) then
916 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
917 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
918 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
919 FND_MESSAGE.SET_TOKEN('PARAMETERS','Batch Id = '||X_Batch_ID
920 ||',Total = '||X_Total
921 ||',Total RTOT DB = '||
922 X_Total_Rtot_DB);
923 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
924 end if;
925 APP_EXCEPTION.RAISE_EXCEPTION;
926 END Select_Summary;
927
928 END AP_INVOICES_POST_PROCESS_PKG;