1 PACKAGE BODY AP_PREPAY_UTILS_PKG AS
2 /*$Header: apprutlb.pls 120.19 2011/09/14 21:52:31 pgayen ship $*/
3
4 FUNCTION Get_Line_Prepay_AMT_Remaining (
5 P_invoice_id IN NUMBER,
6 P_line_number IN NUMBER) RETURN NUMBER
7 IS
8 l_prepay_amount_remaining NUMBER := 0;
9
10 BEGIN
11 SELECT SUM(NVL(prepay_amount_remaining, total_dist_amount))
12 INTO l_prepay_amount_remaining
13 FROM ap_invoice_distributions_all
14 WHERE invoice_id = p_invoice_id
15 AND invoice_line_number = p_line_number
16 AND line_type_lookup_code IN
17 ('ITEM', 'ACCRUAL',
18 'REC_TAX', 'NONREC_TAX' )
19 AND NVL(reversal_flag,'N') <> 'Y';
20
21 RETURN (l_prepay_amount_remaining);
22
23 EXCEPTION
24 WHEN OTHERS THEN
25 RETURN (l_prepay_amount_remaining);
26
27 END Get_Line_Prepay_AMT_Remaining;
28
29
30 --This Function will return the amount_remaining for the Item Line
31 --on a Prepayment invoice, including the associated tax of that Item line.
32 --The tax could be exclusive or inclusive. For the exclusive case the
33 --tax distributions associated would be a separate line.
34
35 FUNCTION Get_Ln_Prep_AMT_Remain_Recoup (
36 P_invoice_id IN NUMBER,
37 P_line_number IN NUMBER) RETURN NUMBER IS
38 l_prepay_amount_remaining_item NUMBER;
39 l_prepay_amount_remaining_tax NUMBER;
40
41 BEGIN
42
43 l_prepay_amount_remaining_item := 0;
44 l_prepay_amount_remaining_tax := 0;
45
46 SELECT NVL(SUM(NVL(prepay_amount_remaining, total_dist_amount)),0)
47 INTO l_prepay_amount_remaining_item
48 FROM ap_invoice_distributions_all
49 WHERE invoice_id = p_invoice_id
50 AND invoice_line_number = p_line_number
51 AND line_type_lookup_code IN
52 ('ITEM', 'ACCRUAL')
53 --'REC_TAX', 'NONREC_TAX' ) --bugfix:5609186
54 AND NVL(reversal_flag,'N') <> 'Y';
55
56 --To get the exclusive tax amount tied to the Item line of Prepayment invoice.
57 /*
58 SELECT NVL(SUM(NVL(aid.prepay_amount_remaining, aid.total_dist_amount)),0)
59 INTO l_prepay_amount_remaining_tax
60 FROM ap_invoice_distributions_all aid, --Tax line
61 ap_invoice_distributions_all aid1 --Item line
62 WHERE aid1.invoice_id = p_invoice_id
63 AND aid1.invoice_line_number = p_line_number
64 AND aid.invoice_id = aid1.invoice_id
65 AND aid.charge_applicable_to_dist_id = aid1.invoice_distribution_id
66 AND aid.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
67 AND aid1.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
68 AND NVL(aid1.reversal_flag,'N') <> 'Y'
69 AND NVL(aid.reversal_flag,'N') <> 'Y'; */
70
71 RETURN (l_prepay_amount_remaining_item+l_prepay_amount_remaining_tax);
72
73 EXCEPTION
74 WHEN OTHERS THEN
75 RETURN (l_prepay_amount_remaining_item+l_prepay_amount_remaining_tax);
76
77 END Get_Ln_Prep_AMT_Remain_Recoup;
78
79
80 FUNCTION Lock_Line (
81 P_invoice_id IN NUMBER,
82 P_line_number IN NUMBER,
83 P_request_id IN NUMBER) RETURN BOOLEAN
84 IS
85 PRAGMA AUTONOMOUS_TRANSACTION;
86 BEGIN
87
88 -- This would lock the selected lines for both the
89 -- online and the batch cases.
90
91 UPDATE ap_invoice_lines
92 SET line_selected_for_appl_flag = 'Y',
93 prepay_appl_request_id = p_request_id
94 WHERE invoice_id = p_invoice_id
95 AND line_number = p_line_number;
96
97 COMMIT;
98
99 RETURN (TRUE);
100
101 EXCEPTION
102 WHEN OTHERS THEN
103 RETURN (FALSE);
104
105 END Lock_Line;
106
107 FUNCTION Unlock_Line (
108 P_invoice_id IN NUMBER,
109 P_line_number IN NUMBER) RETURN BOOLEAN
110 IS
111 PRAGMA AUTONOMOUS_TRANSACTION;
112 BEGIN
113 UPDATE ap_invoice_lines
114 SET line_selected_for_appl_flag = 'N',
115 prepay_appl_request_id = NULL
116 WHERE invoice_id = p_invoice_id
117 AND line_number = p_line_number;
118
119 COMMIT;
120
121 RETURN (TRUE);
122
123 EXCEPTION
124 WHEN OTHERS THEN
125
126 RETURN (FALSE);
127 END Unlock_Line;
128
129 FUNCTION Unlock_Locked_Lines (
130 P_request_id IN NUMBER) RETURN BOOLEAN
131 IS
132 PRAGMA AUTONOMOUS_TRANSACTION;
133 BEGIN
134
135 -- This would lock the selected lines for both the
136 -- online and the batch cases.
137
138 UPDATE ap_invoice_lines
139 SET line_selected_for_appl_flag = 'N',
140 prepay_appl_request_id = NULL
141 WHERE ( prepay_appl_request_id = p_request_id
142 OR prepay_appl_request_id IS NULL)
143 AND line_selected_for_appl_flag = 'Y';
144
145 COMMIT;
146
147 RETURN (TRUE);
148
149 EXCEPTION
150 WHEN OTHERS THEN
151
152 RETURN (FALSE);
153 END Unlock_Locked_Lines;
154
155 FUNCTION IS_Line_Locked (
156 P_invoice_id IN NUMBER,
157 P_line_number IN NUMBER,
158 P_request_id IN NUMBER) RETURN VARCHAR2
159 IS
160 l_already_selected_flag VARCHAR2(1);
161 l_request_id NUMBER;
162 BEGIN
163 SELECT NVL(line_selected_for_appl_flag,'N'),
164 prepay_appl_request_id
165 INTO l_already_selected_flag,
166 l_request_id
167 FROM ap_invoice_lines
168 WHERE invoice_id = p_invoice_id
169 AND line_number = p_line_number;
170
171 IF l_already_selected_flag = 'Y' AND
172 l_request_id IS NULL THEN
173 RETURN ('LOCKED');
174 END IF;
175
176 IF l_already_selected_flag = 'Y' AND
177 l_request_id IS NOT NULL THEN
178
179 IF l_request_id = P_request_id THEN
180 RETURN ('UNLOCKED');
181 ELSE
182 RETURN ('LOCKED');
183 END IF;
184
185 END IF;
186
187 IF l_already_selected_flag = 'N' THEN
188 RETURN ('UNLOCKED');
189 END IF;
190
191 EXCEPTION
192 WHEN OTHERS THEN
193 RETURN ('UNLOCKED');
194 -- Check the RETURN in the case of exception
195
196 END IS_Line_Locked;
197
198
199 FUNCTION get_prepay_number (l_prepay_dist_id IN NUMBER)
200 RETURN VARCHAR2
201 IS
202 l_prepay_number VARCHAR2(50);
203
204 CURSOR c_prepay_number IS
205 SELECT invoice_num
206 FROM ap_invoices_all ai,
207 ap_invoice_distributions_all aid
208 WHERE ai.invoice_id = aid.invoice_id
209 AND aid.invoice_distribution_id = l_prepay_dist_id;
210
211 BEGIN
212
213 -- This Function returns the prepayment number that the prepayment
214 -- distribution is associated with.
215
216 OPEN c_prepay_number;
217 FETCH c_prepay_number
218 INTO l_prepay_number;
219 CLOSE c_prepay_number;
220
221 RETURN(l_prepay_number);
222
223 END get_prepay_number;
224
225
226 FUNCTION get_prepay_dist_number (l_prepay_dist_id IN NUMBER)
227 RETURN VARCHAR2
228 IS
229 l_prepay_dist_number VARCHAR2(50);
230
231 CURSOR c_prepay_dist_number IS
232 SELECT distribution_line_number
233 FROM ap_invoice_distributions_all
234 WHERE invoice_distribution_id = l_prepay_dist_id;
235
236 BEGIN
237
238 -- This Function returns the distribution_line_number that the
239 -- prepayment associated with.
240
241 OPEN c_prepay_dist_number;
242 FETCH c_prepay_dist_number
243 INTO l_prepay_dist_number;
244 CLOSE c_prepay_dist_number;
245
246 RETURN(l_prepay_dist_number);
247
248 END get_prepay_dist_number;
249
250
251 FUNCTION get_prepaid_amount(l_invoice_id IN NUMBER)
252 RETURN NUMBER
253 IS
254 l_prepaid_amount NUMBER := 0;
255 BEGIN
256
257 -- This Function returns the prepaid amount on an STANDARD
258 -- invoice
259 -- eTax Uptake. This function was modified to use the lines
260 -- table instead of the distributions and include TAX lines in
261 -- the prepaid amount if tax is exclusive. In the inclusive
262 -- case the PREPAY line will include the tax amount
263 --bug4944102. As part of Performance fic for invoice workbench
264 --fixed the following query
265 /* SELECT (0 - SUM(NVL(amount,0)))
266 INTO l_prepaid_amount
267 FROM ap_invoice_lines_all
268 WHERE invoice_id = l_invoice_id
269 AND line_type_lookup_code IN ('PREPAY', 'TAX')
270 AND NVL(invoice_includes_prepay_flag, 'N') = 'N' -- Bug 5675960. Added the NVL
271 AND nvl(prepay_invoice_id,-999)<>-999
272 AND nvl(prepay_line_number,-999)<>-999 ; */
273
274 SELECT (0 - SUM(NVL(aid.amount,0)))
275 INTO l_prepaid_amount
276 FROM ap_invoice_distributions_all aid,
277 ap_invoice_lines_all ail
278 WHERE ail.invoice_id = l_invoice_id
279 AND ail.invoice_id = aid.invoice_id
280 AND ail.line_number = aid.invoice_line_number
281 AND aid.line_type_lookup_code = 'PREPAY'
282 AND aid.prepay_distribution_id IS NOT NULL
283 AND NVL(ail.invoice_includes_prepay_flag, 'N') = 'N';
284
285
286
287 RETURN(l_prepaid_amount);
288
289 END get_prepaid_amount;
290
291 -- This Function returns the total number of prepayments that exist for
292 -- a vendor (not fully applied, not permanent). We've declared a server-side
293 -- function that can be accessed from our invoices view so as to improve
294 -- performance when retrieving invoices in the Invoice Gateway.
295
296 FUNCTION get_total_prepays(
297 l_vendor_id IN NUMBER,
298 l_org_id IN NUMBER)
299 RETURN NUMBER
300 IS
301 prepay_count NUMBER := 0;
302 l_prepay_amount_remaining NUMBER:=0;
303 /*Bug 6841613
304 Replaced the existing logic with a cursor defined for the same
305 which just selects the prepayment invoices for the vendor.This
306 is done for performance overheads.The comparison of earliest
307 settlement date would be done with the cursor variable,also the
308 earlier select statement which would call the get_total_prepays
309 as a filter is removed and logic is implemented here as this
310 would reduce the wait time*/
311
312 /* Bug 9128633 Start -- Split the cursor prepayment_invoices into two
313 parts to handle the l_org_id not null and null conditions to improve
314 performance */
315
316 CURSOR prepayment_invoices IS
317 SELECT earliest_settlement_date,invoice_id
318 from ap_invoices
319 where vendor_id=l_vendor_id
320 and invoice_type_lookup_code='PREPAYMENT'
321 and earliest_settlement_date is not null; --bug7015402
322
323 CURSOR prepayment_invoices_org IS
324 SELECT earliest_settlement_date,invoice_id
325 from ap_invoices
326 where vendor_id=l_vendor_id
327 and invoice_type_lookup_code='PREPAYMENT'
328 and earliest_settlement_date is not null --bug7015402
329 AND org_id = l_org_id;
330
331 BEGIN
332
333 if l_org_id is null then
334 for i in prepayment_invoices
335 loop
336 l_prepay_amount_remaining:=0;
337 l_prepay_amount_remaining:=
338 AP_INVOICES_UTILITY_PKG.get_prepay_amount_remaining(i.invoice_id);
339 if(l_prepay_amount_remaining>0 ) then
340 prepay_count:=prepay_count+1;
341 end if;
342 end loop;
343 elsif l_org_id is not null then
344 for i in prepayment_invoices_org
345 loop
346 l_prepay_amount_remaining:=0;
347 l_prepay_amount_remaining:=
348 AP_INVOICES_UTILITY_PKG.get_prepay_amount_remaining(i.invoice_id);
349 if(l_prepay_amount_remaining>0 ) then
350 prepay_count:=prepay_count+1;
351 end if;
352 end loop;
353 end if;
354
355 /* Bug 9128633 End */
356
357 return(prepay_count);
358
359 END get_total_prepays;
360
361
362 FUNCTION get_available_prepays(
363 l_vendor_id IN NUMBER,
364 l_org_id IN NUMBER)
365 RETURN NUMBER
366 IS
367 prepay_count NUMBER := 0;
368 l_prepay_amount_remaining NUMBER:=0;
369
370 /*Bug 6841613
371 Replaced the existing logic with a cursor defined for the same
372 which just selects the prepayment invoices for the vendor.This
373 is done for performance overheads.The comparison of earliest
374 settlement date would be done with the cursor variable,also the
375 earlier select statement which would call the get_total_prepays
376 as a filter is removed and logic is implemented here as this
377 would reduce the wait time*/
378 CURSOR prepayment_invoices IS
379 SELECT earliest_settlement_date,invoice_id
380 from ap_invoices
381 where vendor_id=l_vendor_id
382 and invoice_type_lookup_code='PREPAYMENT'
383 /*bug 7015402*/
384 and payment_status_flag = 'Y'
385 and earliest_settlement_date is not null
386 AND ((l_org_id is not null and org_id = l_org_id) or l_org_id is null);
387 BEGIN
388
389 -- This Function returns the number of available prepayments to a vendor
390 -- which can be applied. We've declared a server-side function that can be
391 -- accessed from our invoices view so as to improve performance when
392 -- retrieving invoices in the Invoice Gateway.
393 for i in prepayment_invoices
394 loop
395 if(i.earliest_settlement_date<=(sysdate)) then
396 l_prepay_amount_remaining:=0;
397 l_prepay_amount_remaining:=
398 AP_INVOICES_UTILITY_PKG.get_prepay_amount_remaining(i.invoice_id);
399 if(l_prepay_amount_remaining>0 ) then
400 prepay_count:=prepay_count+1;
401 end if;
402 end if;
403 end loop;
404
405 RETURN(prepay_count);
406
407 END get_available_prepays;
408
409
410 FUNCTION get_prepay_amount_applied (P_invoice_id IN number)
411 RETURN number
412 IS
413 l_prepay_amount number := 0;
414 l_inv_type_lookup_code varchar2(30);
415
416 CURSOR prepay_cursor is
417 SELECT SUM(total_dist_amount -
418 NVL(prepay_amount_remaining, total_dist_amount))
419 FROM ap_invoice_distributions_all aid,
420 ap_invoice_lines_all ail
421 WHERE aid.invoice_id = P_invoice_id
422 AND aid.invoice_id = ail.invoice_id
423 AND aid.invoice_line_number = ail.line_number
424 AND ail.line_type_lookup_code <> 'TAX'
425 AND aid.line_type_lookup_code IN
426 ('ITEM', 'ACCRUAL', 'REC_TAX', 'NONREC_TAX')
427 -- No need to include variances since the total_dist_amount
428 -- includes the variances total and it is store in the
429 -- nonrec tax distribution.
430 AND NVL(reversal_flag,'N') <> 'Y';
431
432 CURSOR inv_prepay_cursor is
433 SELECT ABS(SUM(amount))
434 FROM ap_invoice_lines_all ail
435 WHERE ail.invoice_id = P_invoice_id
436 AND ail.line_type_lookup_code = 'PREPAY';
437
438 BEGIN
439
440 -- Returns the sum of the applied prepayment amounts for a given
441 -- prepayment or standard invoice.
442 -- Inclusive tax amount are included, exclusive are not.
443
444 SELECT ai.invoice_type_lookup_code
445 INTO l_inv_type_lookup_code
446 FROM ap_invoices ai
447 WHERE ai.invoice_id = P_invoice_id;
448
449 IF (l_inv_type_lookup_code = 'PREPAYMENT') THEN
450 OPEN prepay_cursor;
451 FETCH prepay_cursor INTO l_prepay_amount;
452 CLOSE prepay_cursor;
453 ELSE
454 OPEN inv_prepay_cursor;
455 FETCH inv_prepay_cursor INTO l_prepay_amount;
456 CLOSE inv_prepay_cursor;
457 END IF;
458
459 RETURN (l_prepay_amount);
460
461 END get_prepay_amount_applied;
462
463
464 FUNCTION get_prepay_amount_remaining (P_invoice_id IN number)
465 RETURN number
466 IS
467 l_prepay_amount_remaining NUMBER := 0;
468
469 -- Inclusive tax will be included in the prepay_amount_remaining
470 -- exclusive tax will not.
471 CURSOR c_prepay_amount_remaining IS
472 SELECT SUM(nvl(prepay_amount_remaining, total_dist_amount))
473 FROM ap_invoice_distributions_all aid,
474 ap_invoice_lines_all ail
475 WHERE aid.invoice_id = P_invoice_id
476 AND aid.invoice_id = ail.invoice_id
477 AND aid.invoice_line_number = ail.line_number
478 AND ail.line_type_lookup_code <> 'TAX'
479 -- We will only get REC_TAX and NONREC_TAX dist for the
480 -- inclusive case (parent line is not TAX)
481 AND NVL(ail.line_selected_for_appl_flag, 'N') <> 'Y'
482 AND aid.line_type_lookup_code IN
483 ('ITEM', 'ACCRUAL', 'REC_TAX', 'NONREC_TAX')
484 -- there is no need to include the tax variance distr
485 -- here since the prepay_amount_remaining and the
486 -- total_dist_amount will be including them and it will
487 -- be stored at the primary nonrec tax dist.
488 AND nvl(aid.reversal_flag,'N') <> 'Y';
489
490 BEGIN
491
492 -- Returns the sum of the unapplied prepayment amounts for a given
493 -- prepayment
494
495 OPEN c_prepay_amount_remaining;
496 FETCH c_prepay_amount_remaining INTO l_prepay_amount_remaining;
497 CLOSE c_prepay_amount_remaining;
498
499 RETURN(l_prepay_amount_remaining);
500
501 END get_prepay_amount_remaining;
502
503
504 FUNCTION get_prepayments_applied_flag (P_invoice_id IN number)
505 RETURN varchar2
506 IS
507 l_flag varchar2(1) := 'N';
508 BEGIN
509
510 -- Returns 'Y' if an invoice has prepayments applied to it
511
512 IF ( sign (AP_PREPAY_UTILS_PKG.get_prepay_amount_applied(
513 P_invoice_id)) = 1 ) THEN
514 l_flag := 'Y';
515 ELSE
516 l_flag := null;
517 END IF;
518
519 RETURN (l_flag);
520
521 END get_prepayments_applied_flag;
522
523
524 FUNCTION get_prepayment_type (P_invoice_id IN number)
525 RETURN varchar2
526 IS
527 l_prepayment_type VARCHAR2(9);
528
529 CURSOR c_prepayment_type IS
530 SELECT decode(AI.EARLIEST_SETTLEMENT_DATE,null,'PERMANENT','TEMPORARY')
531 FROM ap_invoices_all ai
532 WHERE ai.invoice_id = P_invoice_id;
533 BEGIN
534
535 -- Returns whether prepayment is of type "PERMANENT' which cannot be
536 -- applied or 'TEMPORARY' which can be applied.
537
538 OPEN c_prepayment_type;
539 FETCH c_prepayment_type INTO l_prepayment_type;
540 CLOSE c_prepayment_type;
541
542 RETURN(l_prepayment_type);
543
544 END get_prepayment_type;
545
546
547 FUNCTION get_pp_amt_applied_on_date (
548 P_invoice_id IN NUMBER,
549 P_prepay_id IN NUMBER,
550 P_application_date IN DATE)
551 RETURN number
552 IS
553 l_prepay_amt_applied NUMBER := 0;
554
555 BEGIN
556
557 -- This Function returns the sum of the applied prepayment amounts to
558 -- an invoice by a prepayment for a given date.
559 -- Tax inclusive amounts included, exclusive amounts are not.
560 -- This query is called from the Prepayment Remittance Notice
561 -- and it is required to show the applied amount at the rate of the
562 -- prepayment in the case there is a difference in the tax rate or
563 -- tax recovery rate
564
565 SELECT SUM((NVL(aid1.amount, 0) - NVL(aid1.prepay_tax_diff_amount, 0))* -1)
566 INTO l_prepay_amt_applied
567 FROM ap_invoice_distributions_all aid1,
568 ap_invoice_distributions_all aid2,
569 ap_invoice_lines_all ail
570 WHERE aid1.invoice_id = P_invoice_id
571 AND aid1.line_type_lookup_code IN ('PREPAY', 'REC_TAX', 'NONREC_TAX')
572 AND aid1.invoice_id = ail.invoice_id
573 AND aid1.invoice_line_number = ail.line_number
574 AND ail.line_type_lookup_code = 'PREPAY'
575 AND aid1.prepay_distribution_id = aid2.invoice_distribution_id
576 AND aid2.invoice_id = P_prepay_id
577 AND aid2.last_update_date = P_application_date ;
578
579 RETURN (l_prepay_amt_applied);
580
581 END get_pp_amt_applied_on_date;
582
583
584 FUNCTION get_amt_applied_per_prepay (
585 P_invoice_id IN NUMBER,
586 P_prepay_id IN NUMBER)
587 RETURN number
588 IS
589 l_prepay_amt_applied NUMBER := 0;
590
591 BEGIN
592
593 -- This Function returns the sum of the applied prepayment amounts to
594 -- an invoice by a prepayment. This has been added to do not use a
595 -- new select statement in the expense report import program.
596 -- eTax Uptake. Change this select to get the pp applied amt from the
597 -- lines table, not the distributions
598 -- Tax: inclusive included, exclusive is not.
599
600 SELECT SUM(ail.amount * -1)
601 INTO l_prepay_amt_applied
602 FROM ap_invoice_lines_all ail
603 WHERE ail.invoice_id = P_invoice_id
604 AND ail.line_type_lookup_code = 'PREPAY'
605 AND ail.prepay_invoice_id = P_prepay_id;
606
607 RETURN (l_prepay_amt_applied);
608
609 END get_amt_applied_per_prepay;
610
611 -- Check this should be obsoleted , because we have obsoleted the
612 -- stop_prepay_across_bal_seg option as a part of this project.
613
614 PROCEDURE Get_Prepay_Amount_Available(
615 X_Invoice_ID IN NUMBER,
616 X_Prepay_ID IN NUMBER,
617 X_Sob_Id IN NUMBER,
618 X_Balancing_Segment OUT NOCOPY VARCHAR2,
619 X_Prepay_Amount OUT NOCOPY NUMBER,
620 X_Invoice_Amount OUT NOCOPY NUMBER) IS
621
622 l_prepay_amount NUMBER;
623 l_invoice_amount NUMBER;
624 l_bal_segment VARCHAR2(30);
625
626 CURSOR c_prepay_dist IS
627 SELECT sum(nvl(prepay_amount_remaining,total_dist_amount)),
628 AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
629 aip.dist_code_combination_id, X_Sob_Id)
630 FROM ap_invoice_distributions aip
631 WHERE aip.invoice_id = X_Prepay_Id
632 AND aip.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
633 AND nvl(aip.reversal_flag,'N') <> 'Y'
634 AND nvl(aip.prepay_amount_remaining,amount) > 0
635 AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
636 aip.dist_code_combination_id, X_Sob_Id) IN
637 (SELECT AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
638 aid.dist_code_combination_id, X_Sob_Id)
639 FROM ap_invoice_distributions aid
640 WHERE aid.invoice_id = X_Invoice_ID)
641 GROUP BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
642 aip.dist_code_combination_id, X_Sob_Id)
643 ORDER BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
644 aip.dist_code_combination_id, X_Sob_Id);
645
646 BEGIN
647
648 -- Procedure to get the sum of distribution amount for a given invoice
649 -- and the sum of the distribution amount for a given prepayment
650
651 OPEN c_prepay_dist;
652 LOOP
653 FETCH c_prepay_dist into l_prepay_amount, l_bal_segment;
654 EXIT WHEN c_prepay_dist%NOTFOUND;
655
656 SELECT sum(amount)
657 INTO l_invoice_amount
658 FROM ap_invoice_distributions
659 WHERE invoice_id = X_Invoice_ID
660 AND line_type_lookup_code IN ('ITEM','PREPAY')
661 AND nvl(reversal_flag,'N') <> 'Y'
662 AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
663 dist_code_combination_id, X_Sob_Id)
664 = l_bal_segment;
665
666 IF l_invoice_amount <> 0 THEN
667 EXIT;
668 END IF;
669
670 END LOOP;
671 CLOSE c_prepay_dist;
672
673 X_Balancing_Segment := l_bal_segment;
674 X_Prepay_Amount := l_prepay_amount;
675 X_Invoice_Amount := l_invoice_amount;
676
677 END Get_Prepay_Amount_Available;
678
679 -- This function returns the remaining amount for an
680 -- ITEM line of the prepayment invoice not including tax
681 FUNCTION Get_Ln_Pp_AMT_Remaining_No_Tax(
682 P_invoice_id IN NUMBER,
683 P_line_number IN NUMBER) RETURN NUMBER
684 IS
685 l_prepay_amount_remaining NUMBER := 0;
686
687 BEGIN
688 SELECT SUM(nvl(prepay_amount_remaining,total_dist_amount))
689 INTO l_prepay_amount_remaining
690 FROM ap_invoice_distributions_all
691 WHERE invoice_id = p_invoice_id
692 AND invoice_line_number = p_line_number
693 AND line_type_lookup_code IN ('ITEM', 'ACCRUAL')
694 AND NVL(reversal_flag,'N') <> 'Y';
695
696 RETURN (l_prepay_amount_remaining);
697
698 EXCEPTION
699 WHEN OTHERS THEN
700 RETURN (l_prepay_amount_remaining);
701
702 END Get_Ln_Pp_AMT_Remaining_No_Tax;
703
704 -- This function will return the remaining amount
705 -- of inclusive tax for an ITEM line of a prepayment
706 -- invoice
707 FUNCTION Get_Inc_Tax_Pp_Amt_Remaining (
708 P_invoice_id IN NUMBER,
709 P_line_number IN NUMBER) RETURN NUMBER
710 IS
711 l_prepay_amount_remaining NUMBER := 0;
712
713 BEGIN
714 SELECT SUM(nvl(prepay_amount_remaining, total_dist_amount))
715 INTO l_prepay_amount_remaining
716 FROM ap_invoice_distributions_all
717 WHERE invoice_id = p_invoice_id
718 AND invoice_line_number = p_line_number
719 AND line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX',
720 'TIPV', 'TRV')
721 AND NVL(reversal_flag,'N') <> 'Y';
722
723 RETURN (l_prepay_amount_remaining);
724
725 EXCEPTION
726 WHEN OTHERS THEN
727 RETURN (l_prepay_amount_remaining);
728
729 END Get_Inc_Tax_Pp_Amt_Remaining;
730
731 -- This function will return the exclusive tax
732 -- amount resulting from a prepayment application
733 FUNCTION Get_Exc_Tax_Amt_Applied (
734 X_Invoice_Id IN NUMBER,
735 X_prepay_invoice_id IN NUMBER,
736 X_prepay_Line_Number IN NUMBER) RETURN NUMBER
737 IS
738 l_exclusive_tax_amt_applied NUMBER := 0;
739
740 BEGIN
741 SELECT SUM(NVL(ail.amount, 0))
742 INTO l_exclusive_tax_amt_applied
743 FROM ap_invoice_lines_all ail
744 WHERE ail.invoice_id = X_invoice_id
745 AND ail.line_type_lookup_code = 'TAX'
746 AND NVL(ail.discarded_flag, 'N') <> 'Y'
747 AND NVL(ail.cancelled_flag, 'N') <> 'Y'
748 AND ail.prepay_invoice_id = X_prepay_invoice_id
749 AND ail.prepay_line_number = X_prepay_Line_Number;
750
751 RETURN (l_exclusive_tax_amt_applied);
752
753 EXCEPTION
754 WHEN OTHERS THEN
755 RETURN (l_exclusive_tax_amt_applied);
756
757 END Get_Exc_Tax_Amt_Applied;
758
759 -- This function will return the total of the invoice
760 -- unpaid amount not including exclusive taxes
761
762 FUNCTION Get_Invoice_Unpaid_Amount(
763 X_Invoice_Id IN NUMBER) RETURN NUMBER
764 IS
765 l_invoice_unpaid_amount NUMBER := 0;
766 l_inv_payment_status AP_INVOICES_ALL.payment_status_flag%TYPE;
767 l_invoice_currency_code AP_INVOICES_ALL.invoice_currency_code%TYPE;
768 l_payment_currency_code AP_INVOICES_ALL.payment_currency_code%TYPE;
769 l_payment_cross_rate_date AP_INVOICES_ALL.payment_cross_rate_date%TYPE;
770 l_payment_cross_rate_type AP_INVOICES_ALL.payment_cross_rate_type%TYPE;
771 l_invoice_total AP_INVOICES_ALL.invoice_amount%TYPE;
772 l_lines_total_no_exc_tax NUMBER := 0;
773 l_unpaid_amount NUMBER := 0;
774
775 CURSOR c_invoice_info IS
776 SELECT ai.payment_status_flag,
777 ai.invoice_currency_code,
778 ai.payment_currency_code,
779 ai.payment_cross_rate_date,
780 ai.payment_cross_rate_type,
781 NVL(ai.invoice_amount, 0)
782 FROM ap_invoices_all ai
783 WHERE ai.invoice_id = X_Invoice_Id;
784
785 /* Commented for bug 7506584
786 CURSOR c_invoice_lines_info IS
787 SELECT NVL(SUM(NVL(ail.amount,0)), 0)
788 FROM ap_invoice_lines_all ail
789 WHERE ail.invoice_id = X_Invoice_Id
790 AND ail.line_type_lookup_code <> 'TAX'
791 AND (ail.line_type_lookup_code <> 'PREPAY'
792 OR NVL(ail.invoice_includes_prepay_flag,'N') = 'Y'); */
793 --7506584
794 l_non_tax_lines NUMBER := 0;
795 l_tax_lines NUMBER := 0;
796 l_tax_prorated NUMBER := 0;
797 l_prep_applied NUMBER := 0;
798 l_item_lines_proration NUMBER := 0;
799 l_tax_lines_proration NUMBER := 0;
800 l_sum_checks_payment NUMBER := 0;
801 l_awt_lines NUMBER := 0;
802 l_prorated_unpaid_amt NUMBER := 0; --bug 9338463
803 BEGIN
804 -- Get invoice info
805
806 OPEN c_invoice_info;
807 FETCH c_invoice_info INTO
808 l_inv_payment_status,
809 l_invoice_currency_code,
810 l_payment_currency_code,
811 l_payment_cross_rate_date,
812 l_payment_cross_rate_type,
813 l_invoice_total;
814
815 IF(c_invoice_info % NOTFOUND) THEN
816 CLOSE c_invoice_info;
817 RAISE NO_DATA_FOUND;
818 END IF;
819
820 CLOSE c_invoice_info;
821
822 --bug 7506584 starts
823 --unpaid amount in payment currency
824 SELECT SUM(nvl(aps.amount_remaining, 0))
825 INTO l_unpaid_amount
826 FROM ap_payment_schedules_all aps
827 WHERE aps.invoice_id = x_invoice_id;
828
829 /*Bug9338463 :comments - l_tax_lines and l_non_tax_lines though includes prepay it would
830 only be used when no payment or prepayment exists*/
831
832 SELECT SUM(decode(line_type_lookup_code, 'TAX', nvl(ail.amount, 0), 0)) l_tax_lines,
833 SUM(decode(line_type_lookup_code, 'TAX', 0, nvl(ail.amount, 0))) l_non_tax_lines,
834 --SUM(decode(line_type_lookup_code, 'PREPAY', decode(nvl(ail.invoice_includes_prepay_flag,'N'),'N',nvl(ail.amount, 0),0), 0)) l_prep_applied, /*Bug 9338463: prepay on invoice condition*/ /*Commented for bug 12742360*/
835 SUM(decode(line_type_lookup_code, 'AWT', nvl(ail.amount, 0), 0)) l_awt_lines
836 INTO l_tax_lines,
837 l_non_tax_lines,
838 --l_prep_applied, /*Commented for bug 12742360*/
839 l_awt_lines
840 FROM ap_invoice_lines_all ail
841 WHERE ail.invoice_id = x_invoice_id;
842
843 --bug 12742360 :Modified SQL to find the prepay applied amount that includes the prepay tax
844 -- commented code above that computes l_prep_applied
845
846 Select nvl(SUM(aid.amount),0)
847 into l_prep_applied
848 from ap_invoice_distributions_all aid,
849 ap_invoice_lines_all ail
850 Where ail.invoice_id = aid.invoice_id
851 and ail.line_number = aid.invoice_line_number
852 and nvl(ail.invoice_includes_prepay_flag,'N') = 'N'
853 and aid.prepay_distribution_id is not null
854 and ail.invoice_id = x_invoice_id;
855
856 --bug 12742360 end
857
858 --item amount without any tax, AWT or prepay
859
860 SELECT nvl(SUM(nvl(amount, 0)), 0)
861 INTO l_item_lines_proration
862 FROM ap_invoice_lines_all ail
863 WHERE ail.invoice_id = x_invoice_id
864 AND ail.line_type_lookup_code not in ('TAX','AWT') /*9338463: AWT should not be considered*/
865 AND(ail.line_type_lookup_code <> 'PREPAY' OR nvl(ail.invoice_includes_prepay_flag, 'N') = 'Y');
866
867 --tax amount that is part of the invoice and not any prepayment
868 SELECT nvl(SUM(nvl(amount, 0)), 0)
869 INTO l_tax_lines_proration
870 FROM ap_invoice_lines_all ail
871 WHERE ail.invoice_id = x_invoice_id
872 AND ail.line_type_lookup_code = 'TAX'
873 AND (ail.amount > 0 OR nvl(ail.invoice_includes_prepay_flag, 'N') = 'Y'); /*Bug 9338463: invoice includes prepay check added*/
874
875 --Bug 9338463 commented below code
876 --l_tax_lines_proration := ap_utilities_pkg.ap_round_currency(l_tax_lines_proration, l_invoice_currency_code);
877
878 --payments made by check
879 SELECT nvl(SUM(amount), 0)
880 INTO l_sum_checks_payment
881 FROM ap_invoice_payments_all
882 WHERE invoice_id = x_invoice_id;
883
884 /*Bug 9338463: convert unpaid amount and check amounts to invoice currency */
885 IF (l_invoice_currency_code <> l_payment_currency_code) THEN
886 l_sum_checks_payment := gl_currency_api.convert_amount(
887 l_payment_currency_code,
888 l_invoice_currency_code,
889 l_payment_cross_rate_date,
890 l_payment_cross_rate_type,
891 l_sum_checks_payment);
892
893 l_unpaid_amount := gl_currency_api.convert_amount(
894 l_payment_currency_code,
895 l_invoice_currency_code,
896 l_payment_cross_rate_date,
897 l_payment_cross_rate_type,
898 l_unpaid_amount);
899
900 END IF; /*Bug 9338463*/
901
902 --Commented for bug8921145
903 /*IF(l_inv_payment_status = 'N') THEN*/
904
905
906 /*bug 9338463: commented and recoded the complete logic below*/
907 /*IF(l_tax_lines = 0) THEN
908 l_invoice_unpaid_amount := l_unpaid_amount;
909
910 ELSE
911
912 IF(l_unpaid_amount -l_tax_lines = l_non_tax_lines) THEN
913 l_invoice_unpaid_amount := l_non_tax_lines;
914
915 ELSE
916 --prorate tax
917 l_tax_prorated :=(l_invoice_total *l_tax_lines_proration) /(l_item_lines_proration + l_tax_lines_proration);
918 l_tax_prorated := ap_utilities_pkg.ap_round_currency(l_tax_prorated, l_invoice_currency_code);
919 l_invoice_unpaid_amount := l_invoice_total -l_tax_prorated + l_prep_applied -l_sum_checks_payment + l_awt_lines;
920
921 END IF;
922
923 END IF; */ /*bug 9338463: commented and recoded the complete logic below*/
924
925
926 /*bug 9338463 begin*/
927 IF (l_inv_payment_status = 'Y') THEN
928 --For completely paid invoice unpaid amount should be 0
929 --and be taken from payment schedules
930 l_invoice_unpaid_amount := l_unpaid_amount;
931
932 ELSIF (l_inv_payment_status = 'N') THEN
933 --incase of unpaid invoices, deduct exclusive tax from
934 --unpaid amount (if exists) else return schedule unpaid amount
935 IF (l_tax_lines > 0)
936 AND (l_unpaid_amount -l_tax_lines = l_non_tax_lines) THEN
937 l_invoice_unpaid_amount := l_non_tax_lines;
938
939 ELSE
940 l_invoice_unpaid_amount := l_unpaid_amount;
941
942 END IF;
943
944 ELSE
945 --For partially paid invoice (either actual payment or prepay application)
946 --derive prorated unpaid amount excluding tax.
947 --prorate tax for partially paid invoice.
948 --This proration logic was implemented in 7506584 to take care of QA bug 7498687 and that bug.
949
950 l_tax_prorated := (l_tax_lines_proration /(l_item_lines_proration + l_tax_lines_proration));
951 l_prorated_unpaid_amt :=(l_invoice_total-l_sum_checks_payment + l_prep_applied + l_awt_lines) * (1-l_tax_prorated);
952 l_prorated_unpaid_amt := ap_utilities_pkg.ap_round_currency(l_prorated_unpaid_amt, l_invoice_currency_code);
953 l_invoice_unpaid_amount := l_prorated_unpaid_amt;
954
955 END IF; /*bug 9338463 end*/
956
957 --Commented ELSE block for bug8921145
958 /*ELSE
959 -- Invoice has any partial payment
960
961 l_tax_prorated :=(l_invoice_total *l_tax_lines_proration) /(l_item_lines_proration + l_tax_lines_proration);
962 l_tax_prorated := ap_utilities_pkg.ap_round_currency(l_tax_prorated, l_invoice_currency_code);
963 l_invoice_unpaid_amount := l_invoice_total -l_tax_prorated + l_prep_applied -l_sum_checks_payment + l_awt_lines;
964
965 END IF; */
966 --End of bug8921145
967 --bug 7506584 ends
968
969 /* Commented for bug 7506584
970
971 --Bugfix:4554256, moved the cursor code from within the IF
972 --condition, to here, since l_lines_total_no_exc_tax
973 --is used both by IF and ELSE condition.
974 OPEN c_invoice_lines_info;
975 FETCH c_invoice_lines_info INTO l_lines_total_no_exc_tax;
976 CLOSE c_invoice_lines_info;
977
978 IF (l_inv_payment_status = 'N' ) THEN
979 -- This invoice has not been paid.
980 -- The unpaid amount is the total of lines
981 -- not including TAX lines
982 -- If no tax lines exist, the unpaid amount will be the
983 -- invoice total. The select will return 0 in the case
984 -- there are no lines
985
986 IF (l_lines_total_no_exc_tax = 0) THEN
987 l_invoice_unpaid_amount := l_invoice_total;
988
989 ELSE
990 l_invoice_unpaid_amount := l_lines_total_no_exc_tax;
991 END IF;
992
993 ELSE
994 -- Invoice has any partial payment
995
996 SELECT SUM(NVL(aps.amount_remaining, 0))
997 INTO l_unpaid_amount
998 FROM ap_payment_schedules_all aps
999 WHERE aps.invoice_id = X_Invoice_Id;
1000
1001 -- If the invoice and payment currencies are different
1002 -- we need to convert to the invoice currency the amount
1003 -- from payment schedules
1004 IF (l_invoice_currency_code <> l_payment_currency_code) THEN
1005 l_unpaid_amount := gl_currency_api.convert_amount(
1006 l_payment_currency_code,
1007 l_invoice_currency_code,
1008 l_payment_cross_rate_date,
1009 l_payment_cross_rate_type,
1010 l_unpaid_amount);
1011
1012 END IF;
1013
1014 -- get the rounded proportion of the unpaid amount exclusive
1015 -- of tax from the total unpaid amount
1016 l_invoice_unpaid_amount := AP_Utilities_PKG.AP_Round_Currency (
1017 l_lines_total_no_exc_tax*l_unpaid_amount/l_invoice_total,
1018 l_invoice_currency_code);
1019
1020 END IF;
1021 Commented for bug 7506584 */
1022 RETURN(l_invoice_unpaid_amount);
1023
1024 EXCEPTION
1025 WHEN OTHERS THEN
1026 RETURN(l_invoice_unpaid_amount);
1027
1028 END Get_Invoice_Unpaid_Amount;
1029
1030
1031 -- This function will return the total of the invoice
1032 -- unpaid amount including exclusive taxes, added for
1033 -- bug 6149363
1034
1035 FUNCTION Get_Inv_Tot_Unpaid_Amt(
1036 X_Invoice_Id IN NUMBER) RETURN NUMBER
1037 IS
1038 l_invoice_unpaid_amount NUMBER := 0;
1039 l_inv_payment_status AP_INVOICES_ALL.payment_status_flag%TYPE;
1040 l_invoice_currency_code AP_INVOICES_ALL.invoice_currency_code%TYPE;
1041 l_payment_currency_code AP_INVOICES_ALL.payment_currency_code%TYPE;
1042 l_payment_cross_rate_date AP_INVOICES_ALL.payment_cross_rate_date%TYPE;
1043 l_payment_cross_rate_type AP_INVOICES_ALL.payment_cross_rate_type%TYPE;
1044 l_invoice_total AP_INVOICES_ALL.invoice_amount%TYPE;
1045 l_lines_total_with_exc_tax NUMBER := 0;
1046 l_unpaid_amount NUMBER := 0;
1047
1048 CURSOR c_invoice_info IS
1049 SELECT ai.payment_status_flag,
1050 ai.invoice_currency_code,
1051 ai.payment_currency_code,
1052 ai.payment_cross_rate_date,
1053 ai.payment_cross_rate_type,
1054 NVL(ai.invoice_amount, 0)
1055 FROM ap_invoices_all ai
1056 WHERE ai.invoice_id = X_Invoice_Id;
1057
1058 /* Commented for bug 7506584
1059 CURSOR c_invoice_lines_info IS
1060 SELECT NVL(SUM(NVL(ail.amount,0)), 0)
1061 FROM ap_invoice_lines_all ail
1062 WHERE ail.invoice_id = X_Invoice_Id
1063 AND (ail.line_type_lookup_code <> 'PREPAY'
1064 OR NVL(ail.invoice_includes_prepay_flag,'N') = 'Y');
1065
1066 */
1067
1068 BEGIN
1069 -- Get invoice info
1070
1071 OPEN c_invoice_info;
1072 FETCH c_invoice_info INTO
1073 l_inv_payment_status,
1074 l_invoice_currency_code,
1075 l_payment_currency_code,
1076 l_payment_cross_rate_date,
1077 l_payment_cross_rate_type,
1078 l_invoice_total;
1079
1080 IF(c_invoice_info % NOTFOUND) THEN
1081 CLOSE c_invoice_info;
1082 RAISE NO_DATA_FOUND;
1083 END IF;
1084
1085 CLOSE c_invoice_info;
1086
1087 /* Commented for bug 7506584
1088 --Bugfix:4554256, moved the cursor code from within the IF
1089 --condition, to here, since l_lines_total_with_exc_tax
1090 --is used both by IF and ELSE condition.
1091 OPEN c_invoice_lines_info;
1092 FETCH c_invoice_lines_info INTO l_lines_total_with_exc_tax;
1093 CLOSE c_invoice_lines_info;
1094
1095 IF (l_inv_payment_status = 'N' ) THEN
1096 -- This invoice has not been paid.
1097 -- The unpaid amount is the total of lines
1098 -- not including TAX lines
1099 -- If no tax lines exist, the unpaid amount will be the
1100 -- invoice total. The select will return 0 in the case
1101 -- there are no lines
1102
1103 IF (l_lines_total_with_exc_tax = 0) THEN
1104 l_invoice_unpaid_amount := l_invoice_total;
1105
1106 ELSE
1107 l_invoice_unpaid_amount := l_lines_total_with_exc_tax;
1108 END IF;
1109
1110 ELSE
1111 -- Invoice has any partial payment
1112 */
1113
1114 SELECT SUM(nvl(aps.amount_remaining, 0))
1115 INTO l_unpaid_amount
1116 FROM ap_payment_schedules_all aps
1117 WHERE aps.invoice_id = x_invoice_id;
1118
1119 -- If the invoice and payment currencies are different
1120 -- we need to convert to the invoice currency the amount
1121 -- from payment schedules
1122
1123 IF(l_invoice_currency_code <> l_payment_currency_code) THEN
1124 l_unpaid_amount := gl_currency_api.convert_amount(
1125 l_payment_currency_code,
1126 l_invoice_currency_code,
1127 l_payment_cross_rate_date,
1128 l_payment_cross_rate_type,
1129 l_unpaid_amount);
1130
1131 END IF;
1132
1133 -- get the rounded proportion of the unpaid amount exclusive
1134 -- of tax from the total unpaid amount
1135
1136 /*Commented for bug 7506584
1137 l_invoice_unpaid_amount := AP_Utilities_PKG.AP_Round_Currency (
1138 l_lines_total_with_exc_tax*l_unpaid_amount/l_invoice_total,
1139 l_invoice_currency_code);
1140 */
1141 l_invoice_unpaid_amount := ap_utilities_pkg.ap_round_currency(l_unpaid_amount, l_invoice_currency_code);
1142
1143 --END IF;
1144
1145 RETURN(l_invoice_unpaid_amount);
1146
1147 EXCEPTION
1148 WHEN OTHERS THEN
1149 RETURN(l_invoice_unpaid_amount);
1150
1151 END Get_Inv_Tot_Unpaid_Amt;
1152
1153
1154 -- This function will return the total of the inclusive invoice
1155 -- unpaid amount
1156
1157 FUNCTION Get_Inclusive_Tax_Unpaid_Amt (
1158 X_Invoice_Id IN NUMBER) RETURN NUMBER
1159 IS
1160 l_inclusive_unpaid_amount NUMBER := 0;
1161 l_inv_payment_status AP_INVOICES_ALL.payment_status_flag%TYPE;
1162 l_invoice_currency_code AP_INVOICES_ALL.invoice_currency_code%TYPE;
1163 l_payment_currency_code AP_INVOICES_ALL.payment_currency_code%TYPE;
1164 l_payment_cross_rate_date AP_INVOICES_ALL.payment_cross_rate_date%TYPE;
1165 l_payment_cross_rate_type AP_INVOICES_ALL.payment_cross_rate_type%TYPE;
1166 l_invoice_total AP_INVOICES_ALL.invoice_amount%TYPE;
1167 l_inclusive_tax_total_lines NUMBER := 0;
1168
1169 l_unpaid_amount NUMBER := 0;
1170
1171 CURSOR c_invoice_info IS
1172 SELECT ai.payment_status_flag ,
1173 ai.invoice_currency_code,
1174 ai.payment_currency_code,
1175 ai.payment_cross_rate_date,
1176 ai.payment_cross_rate_type,
1177 NVL(ai.invoice_amount, 0)
1178 FROM ap_invoices_all ai
1179 WHERE ai.invoice_id = X_Invoice_Id;
1180
1181 CURSOR c_invoice_lines_info IS
1182 SELECT NVL(SUM(NVL(ail.included_tax_amount,0)), 0)
1183 FROM ap_invoice_lines_all ail
1184 WHERE ail.invoice_id = X_Invoice_Id
1185 AND (ail.line_type_lookup_code <> 'PREPAY'
1186 OR NVL(ail.invoice_includes_prepay_flag,'N') = 'Y');
1187
1188 BEGIN
1189
1190 -- Get invoice info
1191 OPEN c_invoice_info;
1192 FETCH c_invoice_info INTO
1193 l_inv_payment_status,
1194 l_invoice_currency_code,
1195 l_payment_currency_code,
1196 l_payment_cross_rate_date,
1197 l_payment_cross_rate_type,
1198 l_invoice_total;
1199
1200 IF (c_invoice_info%NOTFOUND) THEN
1201 CLOSE c_invoice_info;
1202 RAISE NO_DATA_FOUND;
1203
1204 END IF;
1205 CLOSE c_invoice_info;
1206
1207 IF (l_inv_payment_status = 'N' ) THEN
1208 -- This invoice has not been paid.
1209 -- The inclusive tax unpaid amount is the sum of included_tax_amount from
1210 -- the invoice lines. If tax has not been calculated yet, the included amt
1211 -- should be 0
1212
1213 OPEN c_invoice_lines_info;
1214 FETCH c_invoice_lines_info INTO l_inclusive_tax_total_lines;
1215 CLOSE c_invoice_lines_info;
1216
1217 l_inclusive_unpaid_amount := l_inclusive_tax_total_lines;
1218
1219 ELSE
1220 -- Invoice has any partial payment
1221
1222 SELECT SUM(NVL(aps.amount_remaining, 0))
1223 INTO l_unpaid_amount
1224 FROM ap_payment_schedules aps
1225 WHERE aps.invoice_id = X_Invoice_Id;
1226
1227 -- If the invoice and payment currencies are different
1228 -- we need to convert to the invoice currency the amount
1229 -- from payment schedules
1230 IF (l_invoice_currency_code <> l_payment_currency_code) THEN
1231 l_unpaid_amount := gl_currency_api.convert_amount(
1232 l_payment_currency_code,
1233 l_invoice_currency_code,
1234 l_payment_cross_rate_date,
1235 l_payment_cross_rate_type,
1236 l_unpaid_amount);
1237
1238 END IF;
1239
1240 -- get the proportion of the unpaid amount exclusive of tax from the
1241 -- total unpaid amount
1242 l_inclusive_unpaid_amount := AP_Utilities_PKG.AP_Round_Currency (
1243 l_inclusive_tax_total_lines*l_unpaid_amount/l_invoice_total,
1244 l_invoice_currency_code);
1245
1246
1247 END IF;
1248
1249 RETURN (l_inclusive_unpaid_amount);
1250
1251 EXCEPTION
1252 WHEN OTHERS THEN
1253 RETURN (l_inclusive_unpaid_amount);
1254
1255 END Get_Inclusive_Tax_Unpaid_Amt;
1256
1257 -- This function will return the total of the remaining
1258 -- inclusive tax amount for a distribution
1259 FUNCTION Get_Dist_Inclusive_Tax_Amt (
1260 X_Invoice_Id IN NUMBER,
1261 X_Line_Number IN NUMBER,
1262 X_Invoice_Dist_Id IN NUMBER) RETURN NUMBER
1263
1264 IS
1265 l_remaining_inc_tax_dist NUMBER := 0;
1266
1267 BEGIN
1268 SELECT NVL(SUM(NVL(prepay_amount_remaining, total_dist_amount)), 0)
1269 INTO l_remaining_inc_tax_dist
1270 FROM ap_invoice_distributions_all
1271 WHERE invoice_id = X_Invoice_Id
1272 AND invoice_line_number = X_Line_Number
1273 AND charge_applicable_to_dist_id = X_Invoice_Dist_Id
1274 AND line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX')
1275 AND NVL(reversal_flag,'N') <> 'Y';
1276 -- the total_dist_amount is store only in the primary distribution
1277 -- for prepayment invoices that means ITEM and NONREC TAX dist
1278
1279 RETURN (l_remaining_inc_tax_dist);
1280
1281 EXCEPTION
1282 WHEN OTHERS THEN
1283 RETURN (l_remaining_inc_tax_dist);
1284
1285 END Get_Dist_Inclusive_Tax_Amt;
1286
1287 --Bug 8638881 begin
1288 --This function will return the invoice_includes_prepay_flag of
1289 --the applied prepayment line on standard invoice
1290 FUNCTION Get_pp_appl_inv_incl_pp_flag(
1291 X_Invoice_Id IN NUMBER,
1292 X_prepay_invoice_id IN NUMBER,
1293 X_prepay_Line_Number IN NUMBER DEFAULT NULL) RETURN VARCHAR2
1294 IS
1295 l_inv_incl_pp_flag ap_invoice_lines_all.invoice_includes_prepay_flag%type;
1296
1297 BEGIN
1298
1299 Select case
1300 when exists
1301 (select 1
1302 From ap_invoice_lines_all ail
1303 where ail.invoice_id = X_Invoice_Id
1304 and ail.line_type_lookup_code = 'PREPAY'
1305 and ail.prepay_invoice_id = X_prepay_invoice_id
1306 and ail.prepay_line_number = nvl(X_prepay_Line_Number,ail.prepay_line_number)
1307 and (ail.discarded_flag is null or ail.discarded_flag = 'N')
1308 and nvl(ail.invoice_includes_prepay_flag, 'N') = 'Y'
1309 and exists (select 'Prepay line dists amt remg exists'
1310 from ap_invoice_distributions_all aid
1311 where aid.invoice_id = ail.prepay_invoice_id
1312 and aid.invoice_line_number = ail.prepay_line_number
1313 and aid.prepay_amount_remaining > 0)) Then
1314 'Y'
1315 when exists
1316 (select 1
1317 From ap_invoice_lines_all ail
1318 where ail.invoice_id = X_Invoice_Id
1319 and ail.line_type_lookup_code = 'PREPAY'
1320 and ail.prepay_invoice_id = X_prepay_invoice_id
1321 and ail.prepay_line_number = nvl(X_prepay_Line_Number,ail.prepay_line_number)
1322 and (ail.discarded_flag is null or ail.discarded_flag = 'N')
1323 and nvl(ail.invoice_includes_prepay_flag, 'N') = 'N') then
1324 'N'
1325 else
1326 'X'
1327 end "INVOICE_INCLUDES_PREPAY_FLAG"
1328 INTO l_inv_incl_pp_flag
1329 from dual;
1330
1331 Return l_inv_incl_pp_flag;
1332
1333 EXCEPTION
1334 WHEN OTHERS THEN
1335 RETURN (NULL);
1336
1337 END Get_pp_appl_inv_incl_pp_flag; --Bug 8638881 end
1338
1339 END AP_PREPAY_UTILS_PKG;
[Home] [Help]