1 PACKAGE BODY AP_PREPAY_UTILS_PKG AS
2 /*$Header: apprutlb.pls 120.10.12010000.4 2009/01/20 06:34:13 rseeta 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 CURSOR prepayment_invoices IS
312 SELECT earliest_settlement_date,invoice_id
313 from ap_invoices
314 where vendor_id=l_vendor_id
315 and invoice_type_lookup_code='PREPAYMENT'
316 and earliest_settlement_date is not null --bug7015402
317 AND ((l_org_id is not null and org_id = l_org_id) or l_org_id is null);
318
319 BEGIN
320
321 for i in prepayment_invoices
322 loop
323 l_prepay_amount_remaining:=0;
324 l_prepay_amount_remaining:=
325 AP_INVOICES_UTILITY_PKG.get_prepay_amount_remaining(i.invoice_id);
326 if(l_prepay_amount_remaining>0 ) then
327 prepay_count:=prepay_count+1;
328 end if;
329 end loop;
330 return(prepay_count);
331
332 END get_total_prepays;
333
334
335 FUNCTION get_available_prepays(
336 l_vendor_id IN NUMBER,
337 l_org_id IN NUMBER)
338 RETURN NUMBER
339 IS
340 prepay_count NUMBER := 0;
341 l_prepay_amount_remaining NUMBER:=0;
342
343 /*Bug 6841613
344 Replaced the existing logic with a cursor defined for the same
345 which just selects the prepayment invoices for the vendor.This
346 is done for performance overheads.The comparison of earliest
347 settlement date would be done with the cursor variable,also the
348 earlier select statement which would call the get_total_prepays
349 as a filter is removed and logic is implemented here as this
350 would reduce the wait time*/
351 CURSOR prepayment_invoices IS
352 SELECT earliest_settlement_date,invoice_id
353 from ap_invoices
354 where vendor_id=l_vendor_id
355 and invoice_type_lookup_code='PREPAYMENT'
356 /*bug 7015402*/
357 and payment_status_flag = 'Y'
358 and earliest_settlement_date is not null
359 AND ((l_org_id is not null and org_id = l_org_id) or l_org_id is null);
360 BEGIN
361
362 -- This Function returns the number of available prepayments to a vendor
363 -- which can be applied. We've declared a server-side function that can be
364 -- accessed from our invoices view so as to improve performance when
365 -- retrieving invoices in the Invoice Gateway.
366 for i in prepayment_invoices
367 loop
368 if(i.earliest_settlement_date<=(sysdate)) then
369 l_prepay_amount_remaining:=0;
370 l_prepay_amount_remaining:=
371 AP_INVOICES_UTILITY_PKG.get_prepay_amount_remaining(i.invoice_id);
372 if(l_prepay_amount_remaining>0 ) then
373 prepay_count:=prepay_count+1;
374 end if;
375 end if;
376 end loop;
377
378 RETURN(prepay_count);
379
380 END get_available_prepays;
381
382
383 FUNCTION get_prepay_amount_applied (P_invoice_id IN number)
384 RETURN number
385 IS
386 l_prepay_amount number := 0;
387 l_inv_type_lookup_code varchar2(30);
388
389 CURSOR prepay_cursor is
390 SELECT SUM(total_dist_amount -
391 NVL(prepay_amount_remaining, total_dist_amount))
392 FROM ap_invoice_distributions_all aid,
393 ap_invoice_lines_all ail
394 WHERE aid.invoice_id = P_invoice_id
395 AND aid.invoice_id = ail.invoice_id
396 AND aid.invoice_line_number = ail.line_number
397 AND ail.line_type_lookup_code <> 'TAX'
398 AND aid.line_type_lookup_code IN
399 ('ITEM', 'ACCRUAL', 'REC_TAX', 'NONREC_TAX')
400 -- No need to include variances since the total_dist_amount
401 -- includes the variances total and it is store in the
402 -- nonrec tax distribution.
403 AND NVL(reversal_flag,'N') <> 'Y';
404
405 CURSOR inv_prepay_cursor is
406 SELECT ABS(SUM(amount))
407 FROM ap_invoice_lines_all ail
408 WHERE ail.invoice_id = P_invoice_id
409 AND ail.line_type_lookup_code = 'PREPAY';
410
411 BEGIN
412
413 -- Returns the sum of the applied prepayment amounts for a given
414 -- prepayment or standard invoice.
415 -- Inclusive tax amount are included, exclusive are not.
416
417 SELECT ai.invoice_type_lookup_code
418 INTO l_inv_type_lookup_code
419 FROM ap_invoices ai
420 WHERE ai.invoice_id = P_invoice_id;
421
422 IF (l_inv_type_lookup_code = 'PREPAYMENT') THEN
423 OPEN prepay_cursor;
424 FETCH prepay_cursor INTO l_prepay_amount;
425 CLOSE prepay_cursor;
426 ELSE
427 OPEN inv_prepay_cursor;
428 FETCH inv_prepay_cursor INTO l_prepay_amount;
429 CLOSE inv_prepay_cursor;
430 END IF;
431
432 RETURN (l_prepay_amount);
433
434 END get_prepay_amount_applied;
435
436
437 FUNCTION get_prepay_amount_remaining (P_invoice_id IN number)
438 RETURN number
439 IS
440 l_prepay_amount_remaining NUMBER := 0;
441
442 -- Inclusive tax will be included in the prepay_amount_remaining
443 -- exclusive tax will not.
444 CURSOR c_prepay_amount_remaining IS
445 SELECT SUM(nvl(prepay_amount_remaining, total_dist_amount))
446 FROM ap_invoice_distributions_all aid,
447 ap_invoice_lines_all ail
448 WHERE aid.invoice_id = P_invoice_id
449 AND aid.invoice_id = ail.invoice_id
450 AND aid.invoice_line_number = ail.line_number
451 AND ail.line_type_lookup_code <> 'TAX'
452 -- We will only get REC_TAX and NONREC_TAX dist for the
453 -- inclusive case (parent line is not TAX)
454 AND NVL(ail.line_selected_for_appl_flag, 'N') <> 'Y'
455 AND aid.line_type_lookup_code IN
456 ('ITEM', 'ACCRUAL', 'REC_TAX', 'NONREC_TAX')
457 -- there is no need to include the tax variance distr
458 -- here since the prepay_amount_remaining and the
459 -- total_dist_amount will be including them and it will
460 -- be stored at the primary nonrec tax dist.
461 AND nvl(aid.reversal_flag,'N') <> 'Y';
462
463 BEGIN
464
465 -- Returns the sum of the unapplied prepayment amounts for a given
466 -- prepayment
467
468 OPEN c_prepay_amount_remaining;
469 FETCH c_prepay_amount_remaining INTO l_prepay_amount_remaining;
470 CLOSE c_prepay_amount_remaining;
471
472 RETURN(l_prepay_amount_remaining);
473
474 END get_prepay_amount_remaining;
475
476
477 FUNCTION get_prepayments_applied_flag (P_invoice_id IN number)
478 RETURN varchar2
479 IS
480 l_flag varchar2(1) := 'N';
481 BEGIN
482
483 -- Returns 'Y' if an invoice has prepayments applied to it
484
485 IF ( sign (AP_PREPAY_UTILS_PKG.get_prepay_amount_applied(
486 P_invoice_id)) = 1 ) THEN
487 l_flag := 'Y';
488 ELSE
489 l_flag := null;
490 END IF;
491
492 RETURN (l_flag);
493
494 END get_prepayments_applied_flag;
495
496
497 FUNCTION get_prepayment_type (P_invoice_id IN number)
498 RETURN varchar2
499 IS
500 l_prepayment_type VARCHAR2(9);
501
502 CURSOR c_prepayment_type IS
503 SELECT decode(AI.EARLIEST_SETTLEMENT_DATE,null,'PERMANENT','TEMPORARY')
504 FROM ap_invoices_all ai
505 WHERE ai.invoice_id = P_invoice_id;
506 BEGIN
507
508 -- Returns whether prepayment is of type "PERMANENT' which cannot be
509 -- applied or 'TEMPORARY' which can be applied.
510
511 OPEN c_prepayment_type;
512 FETCH c_prepayment_type INTO l_prepayment_type;
513 CLOSE c_prepayment_type;
514
515 RETURN(l_prepayment_type);
516
517 END get_prepayment_type;
518
519
520 FUNCTION get_pp_amt_applied_on_date (
521 P_invoice_id IN NUMBER,
522 P_prepay_id IN NUMBER,
523 P_application_date IN DATE)
524 RETURN number
525 IS
526 l_prepay_amt_applied NUMBER := 0;
527
528 BEGIN
529
530 -- This Function returns the sum of the applied prepayment amounts to
531 -- an invoice by a prepayment for a given date.
532 -- Tax inclusive amounts included, exclusive amounts are not.
533 -- This query is called from the Prepayment Remittance Notice
534 -- and it is required to show the applied amount at the rate of the
535 -- prepayment in the case there is a difference in the tax rate or
536 -- tax recovery rate
537
538 SELECT SUM((NVL(aid1.amount, 0) - NVL(aid1.prepay_tax_diff_amount, 0))* -1)
539 INTO l_prepay_amt_applied
540 FROM ap_invoice_distributions_all aid1,
541 ap_invoice_distributions_all aid2,
542 ap_invoice_lines_all ail
543 WHERE aid1.invoice_id = P_invoice_id
544 AND aid1.line_type_lookup_code IN ('PREPAY', 'REC_TAX', 'NONREC_TAX')
545 AND aid1.invoice_id = ail.invoice_id
546 AND aid1.invoice_line_number = ail.line_number
547 AND ail.line_type_lookup_code = 'PREPAY'
548 AND aid1.prepay_distribution_id = aid2.invoice_distribution_id
549 AND aid2.invoice_id = P_prepay_id
550 AND aid2.last_update_date = P_application_date ;
551
552 RETURN (l_prepay_amt_applied);
553
554 END get_pp_amt_applied_on_date;
555
556
557 FUNCTION get_amt_applied_per_prepay (
558 P_invoice_id IN NUMBER,
559 P_prepay_id IN NUMBER)
560 RETURN number
561 IS
562 l_prepay_amt_applied NUMBER := 0;
563
564 BEGIN
565
566 -- This Function returns the sum of the applied prepayment amounts to
567 -- an invoice by a prepayment. This has been added to do not use a
568 -- new select statement in the expense report import program.
569 -- eTax Uptake. Change this select to get the pp applied amt from the
570 -- lines table, not the distributions
571 -- Tax: inclusive included, exclusive is not.
572
573 SELECT SUM(ail.amount * -1)
574 INTO l_prepay_amt_applied
575 FROM ap_invoice_lines_all ail
576 WHERE ail.invoice_id = P_invoice_id
577 AND ail.line_type_lookup_code = 'PREPAY'
578 AND ail.prepay_invoice_id = P_prepay_id;
579
580 RETURN (l_prepay_amt_applied);
581
582 END get_amt_applied_per_prepay;
583
584 -- Check this should be obsoleted , because we have obsoleted the
585 -- stop_prepay_across_bal_seg option as a part of this project.
586
587 PROCEDURE Get_Prepay_Amount_Available(
588 X_Invoice_ID IN NUMBER,
589 X_Prepay_ID IN NUMBER,
590 X_Sob_Id IN NUMBER,
591 X_Balancing_Segment OUT NOCOPY VARCHAR2,
592 X_Prepay_Amount OUT NOCOPY NUMBER,
593 X_Invoice_Amount OUT NOCOPY NUMBER) IS
594
595 l_prepay_amount NUMBER;
596 l_invoice_amount NUMBER;
597 l_bal_segment VARCHAR2(30);
598
599 CURSOR c_prepay_dist IS
600 SELECT sum(nvl(prepay_amount_remaining,total_dist_amount)),
601 AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
602 aip.dist_code_combination_id, X_Sob_Id)
603 FROM ap_invoice_distributions aip
604 WHERE aip.invoice_id = X_Prepay_Id
605 AND aip.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
606 AND nvl(aip.reversal_flag,'N') <> 'Y'
607 AND nvl(aip.prepay_amount_remaining,amount) > 0
608 AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
609 aip.dist_code_combination_id, X_Sob_Id) IN
610 (SELECT AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
611 aid.dist_code_combination_id, X_Sob_Id)
612 FROM ap_invoice_distributions aid
613 WHERE aid.invoice_id = X_Invoice_ID)
614 GROUP BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
615 aip.dist_code_combination_id, X_Sob_Id)
616 ORDER BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
617 aip.dist_code_combination_id, X_Sob_Id);
618
619 BEGIN
620
621 -- Procedure to get the sum of distribution amount for a given invoice
622 -- and the sum of the distribution amount for a given prepayment
623
624 OPEN c_prepay_dist;
625 LOOP
626 FETCH c_prepay_dist into l_prepay_amount, l_bal_segment;
627 EXIT WHEN c_prepay_dist%NOTFOUND;
628
629 SELECT sum(amount)
630 INTO l_invoice_amount
631 FROM ap_invoice_distributions
632 WHERE invoice_id = X_Invoice_ID
633 AND line_type_lookup_code IN ('ITEM','PREPAY')
634 AND nvl(reversal_flag,'N') <> 'Y'
635 AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
636 dist_code_combination_id, X_Sob_Id)
637 = l_bal_segment;
638
639 IF l_invoice_amount <> 0 THEN
640 EXIT;
641 END IF;
642
643 END LOOP;
644 CLOSE c_prepay_dist;
645
646 X_Balancing_Segment := l_bal_segment;
647 X_Prepay_Amount := l_prepay_amount;
648 X_Invoice_Amount := l_invoice_amount;
649
650 END Get_Prepay_Amount_Available;
651
652 -- This function returns the remaining amount for an
653 -- ITEM line of the prepayment invoice not including tax
654 FUNCTION Get_Ln_Pp_AMT_Remaining_No_Tax(
655 P_invoice_id IN NUMBER,
656 P_line_number IN NUMBER) RETURN NUMBER
657 IS
658 l_prepay_amount_remaining NUMBER := 0;
659
660 BEGIN
661 SELECT SUM(nvl(prepay_amount_remaining,total_dist_amount))
662 INTO l_prepay_amount_remaining
663 FROM ap_invoice_distributions_all
664 WHERE invoice_id = p_invoice_id
665 AND invoice_line_number = p_line_number
666 AND line_type_lookup_code IN ('ITEM', 'ACCRUAL')
667 AND NVL(reversal_flag,'N') <> 'Y';
668
669 RETURN (l_prepay_amount_remaining);
670
671 EXCEPTION
672 WHEN OTHERS THEN
673 RETURN (l_prepay_amount_remaining);
674
675 END Get_Ln_Pp_AMT_Remaining_No_Tax;
676
677 -- This function will return the remaining amount
678 -- of inclusive tax for an ITEM line of a prepayment
679 -- invoice
680 FUNCTION Get_Inc_Tax_Pp_Amt_Remaining (
681 P_invoice_id IN NUMBER,
682 P_line_number IN NUMBER) RETURN NUMBER
683 IS
684 l_prepay_amount_remaining NUMBER := 0;
685
686 BEGIN
687 SELECT SUM(nvl(prepay_amount_remaining, total_dist_amount))
688 INTO l_prepay_amount_remaining
689 FROM ap_invoice_distributions_all
690 WHERE invoice_id = p_invoice_id
691 AND invoice_line_number = p_line_number
692 AND line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX',
693 'TIPV', 'TRV')
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_Inc_Tax_Pp_Amt_Remaining;
703
704 -- This function will return the exclusive tax
705 -- amount resulting from a prepayment application
706 FUNCTION Get_Exc_Tax_Amt_Applied (
707 X_Invoice_Id IN NUMBER,
708 X_prepay_invoice_id IN NUMBER,
709 X_prepay_Line_Number IN NUMBER) RETURN NUMBER
710 IS
711 l_exclusive_tax_amt_applied NUMBER := 0;
712
713 BEGIN
714 SELECT SUM(NVL(ail.amount, 0))
715 INTO l_exclusive_tax_amt_applied
716 FROM ap_invoice_lines_all ail
717 WHERE ail.invoice_id = X_invoice_id
718 AND ail.line_type_lookup_code = 'TAX'
719 AND NVL(ail.discarded_flag, 'N') <> 'Y'
720 AND NVL(ail.cancelled_flag, 'N') <> 'Y'
721 AND ail.prepay_invoice_id = X_prepay_invoice_id
722 AND ail.prepay_line_number = X_prepay_Line_Number;
723
724 RETURN (l_exclusive_tax_amt_applied);
725
726 EXCEPTION
727 WHEN OTHERS THEN
728 RETURN (l_exclusive_tax_amt_applied);
729
730 END Get_Exc_Tax_Amt_Applied;
731
732 -- This function will return the total of the invoice
733 -- unpaid amount not including exclusive taxes
734
735 FUNCTION Get_Invoice_Unpaid_Amount(
736 X_Invoice_Id IN NUMBER) RETURN NUMBER
737 IS
738 l_invoice_unpaid_amount NUMBER := 0;
739 l_inv_payment_status AP_INVOICES_ALL.payment_status_flag%TYPE;
740 l_invoice_currency_code AP_INVOICES_ALL.invoice_currency_code%TYPE;
741 l_payment_currency_code AP_INVOICES_ALL.payment_currency_code%TYPE;
742 l_payment_cross_rate_date AP_INVOICES_ALL.payment_cross_rate_date%TYPE;
743 l_payment_cross_rate_type AP_INVOICES_ALL.payment_cross_rate_type%TYPE;
744 l_invoice_total AP_INVOICES_ALL.invoice_amount%TYPE;
745 l_lines_total_no_exc_tax NUMBER := 0;
746 l_unpaid_amount NUMBER := 0;
747
748 CURSOR c_invoice_info IS
749 SELECT ai.payment_status_flag,
750 ai.invoice_currency_code,
751 ai.payment_currency_code,
752 ai.payment_cross_rate_date,
753 ai.payment_cross_rate_type,
754 NVL(ai.invoice_amount, 0)
755 FROM ap_invoices_all ai
756 WHERE ai.invoice_id = X_Invoice_Id;
757
758 /* Commented for bug 7506584
759 CURSOR c_invoice_lines_info IS
760 SELECT NVL(SUM(NVL(ail.amount,0)), 0)
761 FROM ap_invoice_lines_all ail
762 WHERE ail.invoice_id = X_Invoice_Id
763 AND ail.line_type_lookup_code <> 'TAX'
764 AND (ail.line_type_lookup_code <> 'PREPAY'
765 OR NVL(ail.invoice_includes_prepay_flag,'N') = 'Y'); */
766 --7506584
767 l_non_tax_lines NUMBER := 0;
768 l_tax_lines NUMBER := 0;
769 l_tax_prorated NUMBER := 0;
770 l_prep_applied NUMBER := 0;
771 l_item_lines_proration NUMBER := 0;
772 l_tax_lines_proration NUMBER := 0;
773 l_sum_checks_payment NUMBER := 0;
774 l_awt_lines NUMBER := 0;
775
776 BEGIN
777 -- Get invoice info
778
779 OPEN c_invoice_info;
780 FETCH c_invoice_info INTO
781 l_inv_payment_status,
782 l_invoice_currency_code,
783 l_payment_currency_code,
784 l_payment_cross_rate_date,
785 l_payment_cross_rate_type,
786 l_invoice_total;
787
788 IF(c_invoice_info % NOTFOUND) THEN
789 CLOSE c_invoice_info;
790 RAISE NO_DATA_FOUND;
791 END IF;
792
793 CLOSE c_invoice_info;
794
795 --bug 7506584 starts
796 SELECT SUM(nvl(aps.amount_remaining, 0))
797 INTO l_unpaid_amount
798 FROM ap_payment_schedules_all aps
799 WHERE aps.invoice_id = x_invoice_id;
800
801 SELECT SUM(decode(line_type_lookup_code, 'TAX', nvl(ail.amount, 0), 0)) l_tax_lines,
802 SUM(decode(line_type_lookup_code, 'TAX', 0, nvl(ail.amount, 0))) l_non_tax_lines,
803 SUM(decode(line_type_lookup_code, 'PREPAY', nvl(ail.amount, 0), 0)) l_prep_applied,
804 SUM(decode(line_type_lookup_code, 'AWT', nvl(ail.amount, 0), 0)) l_awt_lines
805 INTO l_tax_lines,
806 l_non_tax_lines,
807 l_prep_applied,
808 l_awt_lines
809 FROM ap_invoice_lines_all ail
810 WHERE ail.invoice_id = x_invoice_id;
811
812 SELECT nvl(SUM(nvl(amount, 0)), 0)
813 INTO l_item_lines_proration
814 FROM ap_invoice_lines_all ail
815 WHERE ail.invoice_id = x_invoice_id
816 AND ail.line_type_lookup_code <> 'TAX'
817 AND(ail.line_type_lookup_code <> 'PREPAY' OR nvl(ail.invoice_includes_prepay_flag, 'N') = 'Y');
818
819 SELECT nvl(SUM(nvl(amount, 0)), 0)
820 INTO l_tax_lines_proration
821 FROM ap_invoice_lines_all ail
822 WHERE ail.invoice_id = x_invoice_id
823 AND ail.line_type_lookup_code = 'TAX'
824 AND ail.amount > 0;
825
826 l_tax_lines_proration := ap_utilities_pkg.ap_round_currency(l_tax_lines_proration, l_invoice_currency_code);
827
828 --payments made by check
829 SELECT nvl(SUM(amount), 0)
830 INTO l_sum_checks_payment
831 FROM ap_invoice_payments_all
832 WHERE invoice_id = x_invoice_id;
833
834 IF(l_inv_payment_status = 'N') THEN
835
836 IF(l_tax_lines = 0) THEN
837 l_invoice_unpaid_amount := l_unpaid_amount;
838
839 ELSE
840
841 IF(l_unpaid_amount -l_tax_lines = l_non_tax_lines) THEN
842 l_invoice_unpaid_amount := l_non_tax_lines;
843
844 ELSE
845 --prorate tax
846 l_tax_prorated :=(l_invoice_total *l_tax_lines_proration) /(l_item_lines_proration + l_tax_lines_proration);
847 l_tax_prorated := ap_utilities_pkg.ap_round_currency(l_tax_prorated, l_invoice_currency_code);
848 l_invoice_unpaid_amount := l_invoice_total -l_tax_prorated + l_prep_applied -l_sum_checks_payment + l_awt_lines;
849
850 END IF;
851
852 END IF;
853
854 ELSE
855 -- Invoice has any partial payment
856
857 l_tax_prorated :=(l_invoice_total *l_tax_lines_proration) /(l_item_lines_proration + l_tax_lines_proration);
858 l_tax_prorated := ap_utilities_pkg.ap_round_currency(l_tax_prorated, l_invoice_currency_code);
859 l_invoice_unpaid_amount := l_invoice_total -l_tax_prorated + l_prep_applied -l_sum_checks_payment + l_awt_lines;
860
861 END IF;
862
863 --bug 7506584 ends
864
865 /* Commented for bug 7506584
866
867 --Bugfix:4554256, moved the cursor code from within the IF
868 --condition, to here, since l_lines_total_no_exc_tax
869 --is used both by IF and ELSE condition.
870 OPEN c_invoice_lines_info;
871 FETCH c_invoice_lines_info INTO l_lines_total_no_exc_tax;
872 CLOSE c_invoice_lines_info;
873
874 IF (l_inv_payment_status = 'N' ) THEN
875 -- This invoice has not been paid.
876 -- The unpaid amount is the total of lines
877 -- not including TAX lines
878 -- If no tax lines exist, the unpaid amount will be the
879 -- invoice total. The select will return 0 in the case
880 -- there are no lines
881
882 IF (l_lines_total_no_exc_tax = 0) THEN
883 l_invoice_unpaid_amount := l_invoice_total;
884
885 ELSE
886 l_invoice_unpaid_amount := l_lines_total_no_exc_tax;
887 END IF;
888
889 ELSE
890 -- Invoice has any partial payment
891
892 SELECT SUM(NVL(aps.amount_remaining, 0))
893 INTO l_unpaid_amount
894 FROM ap_payment_schedules_all aps
895 WHERE aps.invoice_id = X_Invoice_Id;
896
897 -- If the invoice and payment currencies are different
898 -- we need to convert to the invoice currency the amount
899 -- from payment schedules
900 IF (l_invoice_currency_code <> l_payment_currency_code) THEN
901 l_unpaid_amount := gl_currency_api.convert_amount(
902 l_payment_currency_code,
903 l_invoice_currency_code,
904 l_payment_cross_rate_date,
905 l_payment_cross_rate_type,
906 l_unpaid_amount);
907
908 END IF;
909
910 -- get the rounded proportion of the unpaid amount exclusive
911 -- of tax from the total unpaid amount
912 l_invoice_unpaid_amount := AP_Utilities_PKG.AP_Round_Currency (
913 l_lines_total_no_exc_tax*l_unpaid_amount/l_invoice_total,
914 l_invoice_currency_code);
915
916 END IF;
917 Commented for bug 7506584 */
918 RETURN(l_invoice_unpaid_amount);
919
920 EXCEPTION
921 WHEN OTHERS THEN
922 RETURN(l_invoice_unpaid_amount);
923
924 END Get_Invoice_Unpaid_Amount;
925
926
927 -- This function will return the total of the invoice
928 -- unpaid amount including exclusive taxes, added for
929 -- bug 6149363
930
931 FUNCTION Get_Inv_Tot_Unpaid_Amt(
932 X_Invoice_Id IN NUMBER) RETURN NUMBER
933 IS
934 l_invoice_unpaid_amount NUMBER := 0;
935 l_inv_payment_status AP_INVOICES_ALL.payment_status_flag%TYPE;
936 l_invoice_currency_code AP_INVOICES_ALL.invoice_currency_code%TYPE;
937 l_payment_currency_code AP_INVOICES_ALL.payment_currency_code%TYPE;
938 l_payment_cross_rate_date AP_INVOICES_ALL.payment_cross_rate_date%TYPE;
939 l_payment_cross_rate_type AP_INVOICES_ALL.payment_cross_rate_type%TYPE;
940 l_invoice_total AP_INVOICES_ALL.invoice_amount%TYPE;
941 l_lines_total_with_exc_tax NUMBER := 0;
942 l_unpaid_amount NUMBER := 0;
943
944 CURSOR c_invoice_info IS
945 SELECT ai.payment_status_flag,
946 ai.invoice_currency_code,
947 ai.payment_currency_code,
948 ai.payment_cross_rate_date,
949 ai.payment_cross_rate_type,
950 NVL(ai.invoice_amount, 0)
951 FROM ap_invoices_all ai
952 WHERE ai.invoice_id = X_Invoice_Id;
953
954 /* Commented for bug 7506584
955 CURSOR c_invoice_lines_info IS
956 SELECT NVL(SUM(NVL(ail.amount,0)), 0)
957 FROM ap_invoice_lines_all ail
958 WHERE ail.invoice_id = X_Invoice_Id
959 AND (ail.line_type_lookup_code <> 'PREPAY'
960 OR NVL(ail.invoice_includes_prepay_flag,'N') = 'Y');
961
962 */
963
964 BEGIN
965 -- Get invoice info
966
967 OPEN c_invoice_info;
968 FETCH c_invoice_info INTO
969 l_inv_payment_status,
970 l_invoice_currency_code,
971 l_payment_currency_code,
972 l_payment_cross_rate_date,
973 l_payment_cross_rate_type,
974 l_invoice_total;
975
976 IF(c_invoice_info % NOTFOUND) THEN
977 CLOSE c_invoice_info;
978 RAISE NO_DATA_FOUND;
979 END IF;
980
981 CLOSE c_invoice_info;
982
983 /* Commented for bug 7506584
984 --Bugfix:4554256, moved the cursor code from within the IF
985 --condition, to here, since l_lines_total_with_exc_tax
986 --is used both by IF and ELSE condition.
987 OPEN c_invoice_lines_info;
988 FETCH c_invoice_lines_info INTO l_lines_total_with_exc_tax;
989 CLOSE c_invoice_lines_info;
990
991 IF (l_inv_payment_status = 'N' ) THEN
992 -- This invoice has not been paid.
993 -- The unpaid amount is the total of lines
994 -- not including TAX lines
995 -- If no tax lines exist, the unpaid amount will be the
996 -- invoice total. The select will return 0 in the case
997 -- there are no lines
998
999 IF (l_lines_total_with_exc_tax = 0) THEN
1000 l_invoice_unpaid_amount := l_invoice_total;
1001
1002 ELSE
1003 l_invoice_unpaid_amount := l_lines_total_with_exc_tax;
1004 END IF;
1005
1006 ELSE
1007 -- Invoice has any partial payment
1008 */
1009
1010 SELECT SUM(nvl(aps.amount_remaining, 0))
1011 INTO l_unpaid_amount
1012 FROM ap_payment_schedules_all aps
1013 WHERE aps.invoice_id = x_invoice_id;
1014
1015 -- If the invoice and payment currencies are different
1016 -- we need to convert to the invoice currency the amount
1017 -- from payment schedules
1018
1019 IF(l_invoice_currency_code <> l_payment_currency_code) THEN
1020 l_unpaid_amount := gl_currency_api.convert_amount(
1021 l_payment_currency_code,
1022 l_invoice_currency_code,
1023 l_payment_cross_rate_date,
1024 l_payment_cross_rate_type,
1025 l_unpaid_amount);
1026
1027 END IF;
1028
1029 -- get the rounded proportion of the unpaid amount exclusive
1030 -- of tax from the total unpaid amount
1031
1032 /*Commented for bug 7506584
1033 l_invoice_unpaid_amount := AP_Utilities_PKG.AP_Round_Currency (
1034 l_lines_total_with_exc_tax*l_unpaid_amount/l_invoice_total,
1035 l_invoice_currency_code);
1036 */
1037 l_invoice_unpaid_amount := ap_utilities_pkg.ap_round_currency(l_unpaid_amount, l_invoice_currency_code);
1038
1039 --END IF;
1040
1041 RETURN(l_invoice_unpaid_amount);
1042
1043 EXCEPTION
1044 WHEN OTHERS THEN
1045 RETURN(l_invoice_unpaid_amount);
1046
1047 END Get_Inv_Tot_Unpaid_Amt;
1048
1049
1050 -- This function will return the total of the inclusive invoice
1051 -- unpaid amount
1052
1053 FUNCTION Get_Inclusive_Tax_Unpaid_Amt (
1054 X_Invoice_Id IN NUMBER) RETURN NUMBER
1055 IS
1056 l_inclusive_unpaid_amount NUMBER := 0;
1057 l_inv_payment_status AP_INVOICES_ALL.payment_status_flag%TYPE;
1058 l_invoice_currency_code AP_INVOICES_ALL.invoice_currency_code%TYPE;
1059 l_payment_currency_code AP_INVOICES_ALL.payment_currency_code%TYPE;
1060 l_payment_cross_rate_date AP_INVOICES_ALL.payment_cross_rate_date%TYPE;
1061 l_payment_cross_rate_type AP_INVOICES_ALL.payment_cross_rate_type%TYPE;
1062 l_invoice_total AP_INVOICES_ALL.invoice_amount%TYPE;
1063 l_inclusive_tax_total_lines NUMBER := 0;
1064
1065 l_unpaid_amount NUMBER := 0;
1066
1067 CURSOR c_invoice_info IS
1068 SELECT ai.payment_status_flag ,
1069 ai.invoice_currency_code,
1070 ai.payment_currency_code,
1071 ai.payment_cross_rate_date,
1072 ai.payment_cross_rate_type,
1073 NVL(ai.invoice_amount, 0)
1074 FROM ap_invoices_all ai
1075 WHERE ai.invoice_id = X_Invoice_Id;
1076
1077 CURSOR c_invoice_lines_info IS
1078 SELECT NVL(SUM(NVL(ail.included_tax_amount,0)), 0)
1079 FROM ap_invoice_lines_all ail
1080 WHERE ail.invoice_id = X_Invoice_Id
1081 AND (ail.line_type_lookup_code <> 'PREPAY'
1082 OR NVL(ail.invoice_includes_prepay_flag,'N') = 'Y');
1083
1084 BEGIN
1085
1086 -- Get invoice info
1087 OPEN c_invoice_info;
1088 FETCH c_invoice_info INTO
1089 l_inv_payment_status,
1090 l_invoice_currency_code,
1091 l_payment_currency_code,
1092 l_payment_cross_rate_date,
1093 l_payment_cross_rate_type,
1094 l_invoice_total;
1095
1096 IF (c_invoice_info%NOTFOUND) THEN
1097 CLOSE c_invoice_info;
1098 RAISE NO_DATA_FOUND;
1099
1100 END IF;
1101 CLOSE c_invoice_info;
1102
1103 IF (l_inv_payment_status = 'N' ) THEN
1104 -- This invoice has not been paid.
1105 -- The inclusive tax unpaid amount is the sum of included_tax_amount from
1106 -- the invoice lines. If tax has not been calculated yet, the included amt
1107 -- should be 0
1108
1109 OPEN c_invoice_lines_info;
1110 FETCH c_invoice_lines_info INTO l_inclusive_tax_total_lines;
1111 CLOSE c_invoice_lines_info;
1112
1113 l_inclusive_unpaid_amount := l_inclusive_tax_total_lines;
1114
1115 ELSE
1116 -- Invoice has any partial payment
1117
1118 SELECT SUM(NVL(aps.amount_remaining, 0))
1119 INTO l_unpaid_amount
1120 FROM ap_payment_schedules aps
1121 WHERE aps.invoice_id = X_Invoice_Id;
1122
1123 -- If the invoice and payment currencies are different
1124 -- we need to convert to the invoice currency the amount
1125 -- from payment schedules
1126 IF (l_invoice_currency_code <> l_payment_currency_code) THEN
1127 l_unpaid_amount := gl_currency_api.convert_amount(
1128 l_payment_currency_code,
1129 l_invoice_currency_code,
1130 l_payment_cross_rate_date,
1131 l_payment_cross_rate_type,
1132 l_unpaid_amount);
1133
1134 END IF;
1135
1136 -- get the proportion of the unpaid amount exclusive of tax from the
1137 -- total unpaid amount
1138 l_inclusive_unpaid_amount := AP_Utilities_PKG.AP_Round_Currency (
1139 l_inclusive_tax_total_lines*l_unpaid_amount/l_invoice_total,
1140 l_invoice_currency_code);
1141
1142
1143 END IF;
1144
1145 RETURN (l_inclusive_unpaid_amount);
1146
1147 EXCEPTION
1148 WHEN OTHERS THEN
1149 RETURN (l_inclusive_unpaid_amount);
1150
1151 END Get_Inclusive_Tax_Unpaid_Amt;
1152
1153 -- This function will return the total of the remaining
1154 -- inclusive tax amount for a distribution
1155 FUNCTION Get_Dist_Inclusive_Tax_Amt (
1156 X_Invoice_Id IN NUMBER,
1157 X_Line_Number IN NUMBER,
1158 X_Invoice_Dist_Id IN NUMBER) RETURN NUMBER
1159
1160 IS
1161 l_remaining_inc_tax_dist NUMBER := 0;
1162
1163 BEGIN
1164 SELECT NVL(SUM(NVL(prepay_amount_remaining, total_dist_amount)), 0)
1165 INTO l_remaining_inc_tax_dist
1166 FROM ap_invoice_distributions_all
1167 WHERE invoice_id = X_Invoice_Id
1168 AND invoice_line_number = X_Line_Number
1169 AND charge_applicable_to_dist_id = X_Invoice_Dist_Id
1170 AND line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX')
1171 AND NVL(reversal_flag,'N') <> 'Y';
1172 -- the total_dist_amount is store only in the primary distribution
1173 -- for prepayment invoices that means ITEM and NONREC TAX dist
1174
1175 RETURN (l_remaining_inc_tax_dist);
1176
1177 EXCEPTION
1178 WHEN OTHERS THEN
1179 RETURN (l_remaining_inc_tax_dist);
1180
1181 END Get_Dist_Inclusive_Tax_Amt;
1182
1183 END AP_PREPAY_UTILS_PKG;
[Home] [Help]