1 PACKAGE BODY AP_INVOICES_UTILITY_PKG AS
2 /* $Header: apinvutb.pls 120.79.12020000.6 2013/03/11 05:52:40 harsanan ship $ */
3
4 /* bug 10425573 start*/
5 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
7 G_MODULE_NAME CONSTANT VARCHAR2(50) :='AP.PLSQL.AP_INVOICES_UTILITIES_PKG';
8 /* bug 10425573 end */
9
10 /*=============================================================================
11 | FUNCTION - get_prepay_number
12 |
13 | DESCRIPTION
14 | returns the prepayment number that the prepayment distribution is
15 | associated with.
16 |
17 | KNOWN ISSUES:
18 |
19 | NOTES:
20 |
21 | MODIFICATION HISTORY
22 | Date Author Description of Change
23 |
24 *============================================================================*/
25
26 FUNCTION get_prepay_number (l_prepay_dist_id IN NUMBER)
27 RETURN VARCHAR2 IS
28 l_prepay_number VARCHAR2(50);
29
30 CURSOR c_prepay_number IS
31 SELECT invoice_num
32 FROM ap_invoices
33 WHERE invoice_id =
34 (SELECT invoice_id
35 FROM ap_invoice_distributions
36 WHERE invoice_distribution_id = l_prepay_dist_id);
37 BEGIN
38
39 OPEN c_prepay_number;
40 FETCH c_prepay_number
41 INTO l_prepay_number;
42 CLOSE c_prepay_number;
43
44 RETURN(l_prepay_number);
45
46 END get_prepay_number;
47
48 /*=============================================================================
49 | FUNCTION - get_prepay_dist_number
50 |
51 | DESCRIPTION
52 | Returns the distribution_line_number that the prepayment associated
53 | with.
54 |
55 | KNOWN ISSUES:
56 |
57 | NOTES:
58 |
59 | MODIFICATION HISTORY
60 | Date Author Description of Change
61 |
62 *============================================================================*/
63
64 FUNCTION get_prepay_dist_number (l_prepay_dist_id IN NUMBER)
65 RETURN VARCHAR2 IS
66 l_prepay_dist_number VARCHAR2(50);
67
68 CURSOR c_prepay_dist_number IS
69 SELECT distribution_line_number
70 FROM ap_invoice_distributions
71 WHERE invoice_distribution_id = l_prepay_dist_id;
72
73 BEGIN
74
75 OPEN c_prepay_dist_number;
76 FETCH c_prepay_dist_number
77 INTO l_prepay_dist_number;
78 CLOSE c_prepay_dist_number;
79
80 RETURN(l_prepay_dist_number);
81
82 END get_prepay_dist_number;
83
84 /*=============================================================================
85 | FUNCTION - get_distribution_total
86 |
87 | DESCRIPTION
88 | returns the total distribution amount for the invoice.
89 |
90 | KNOWN ISSUES:
91 |
92 | NOTES:
93 | 1. Bug 1121323. Excluding the tax on the prepayment from the
94 | distribution total.
95 | 2. Bug 1639039. Including the Prepayment and Prepayment Tax from
96 | the distribution total if the invoice_includes_prepay_flag is
97 | set to Y
98 |
99 | MODIFICATION HISTORY
100 | Date Author Description of Change
101 |
102 *============================================================================*/
103
104 FUNCTION get_distribution_total(
105 l_invoice_id IN NUMBER
106 )
107 RETURN NUMBER IS
108
109 distribution_total NUMBER := 0;
110 --Bugfix:3854385
111 l_Y VARCHAR2(1) := 'Y';
112
113 BEGIN
114
115 -- eTax Uptake. Modified to exclude REC_TAX and NONREC_TAX
116 -- distributions created for prepayment applications
117 SELECT SUM(NVL(aid.amount,0))
118 INTO distribution_total
119 FROM ap_invoice_distributions_all aid,
120 ap_invoice_lines_all ail
121 WHERE ail.invoice_id = l_invoice_id
122 AND aid.invoice_id = ail.invoice_id
123 AND aid.invoice_line_number = ail.line_number
124 AND ((aid.line_type_lookup_code NOT IN ('PREPAY', 'AWT')
125 AND aid.prepay_distribution_id IS NULL)
126 OR NVL(ail.invoice_includes_prepay_flag,'N') = l_y);
127
128
129 RETURN(distribution_total);
130
131 END get_distribution_total;
132
133
134 /*===========================================================================
135 | FUNCTION - get_posting_status
136 |
137 | DESCRIPTION
138 | returns the invoice posting status flag.
139 |
140 | KNOWN ISSUES:
141 |
142 | NOTES
143 | 'Y' - Posted
144 | 'N' - Unposted
145 | 'S' - Selected
146 | 'P' - Partially Posted
147 | ---------------------------------------------------------------------
148 | -- Declare cursor to establish the invoice-level posting flag
149 | --
150 | -- The first two selects simply look at the posting flags. The 'S'
151 | -- one means the invoice distributions are selected for accounting
152 | -- processing. The 'P' is to cover one specific case when some of
153 | -- the distributions are fully posting (Y) and some are unposting (N).
154 | -- The status should be partial (P).
155 | --
156 | -- MOAC. Use ap_invoice_distributions_all table instead of SO view
157 | -- since this procedure is called when policy context is not set to
158 | -- the corresponding OU for the invoice_id
159 |
160 | MODIFICATION HISTORY
161 | Date Author Description of Change
162 | 04-Mar-05 Yicao Rewrite the procedure for SLA project
163 *==========================================================================*/
164 FUNCTION get_posting_status(l_invoice_id IN NUMBER)
165 RETURN VARCHAR2 IS
166 invoice_posting_flag VARCHAR2(1);
167 distribution_posting_flag VARCHAR2(1);
168 l_cash_basis_flag VARCHAR2(1);
169 l_org_id AP_SYSTEM_PARAMETERS_ALL.ORG_ID%TYPE;
170
171
172 CURSOR posting_cursor IS
173 SELECT cash_posted_flag
174 FROM ap_invoice_distributions_all
175 WHERE invoice_id = l_invoice_id
176 AND l_cash_basis_flag = 'Y'
177 UNION
178 SELECT accrual_posted_flag
179 FROM ap_invoice_distributions_all
180 WHERE invoice_id = l_invoice_id
181 AND l_cash_basis_flag <>'Y'
182 UNION
183 SELECT 'P'
184 FROM ap_invoice_distributions_all
185 WHERE invoice_id = l_invoice_id
186 AND ((cash_posted_flag = 'Y' AND l_cash_basis_flag = 'Y')
187 OR
188 (accrual_posted_flag = 'Y' AND l_cash_basis_flag <> 'Y'))
189 AND EXISTS
190 (SELECT 'An N is also in the valid flags'
191 FROM ap_invoice_distributions_all
192 WHERE invoice_id = l_invoice_id
193 AND ((cash_posted_flag = 'N'
194 AND l_cash_basis_flag = 'Y')
195 OR
196 (accrual_posted_flag = 'N'
197 AND l_cash_basis_flag <> 'Y')) -- bug fix 6975868
198 UNION /*Added for bug 10039729*/
199 SELECT 'An N is also in the valid flags'
200 FROM ap_prepay_history_all
201 WHERE invoice_id = l_invoice_id
202 AND posted_flag = 'N'
203 AND transaction_type = 'PREPAYMENT APPLICATION ADJ'
204 UNION
205 SELECT 'An N is also in the valid flags'
206 FROM ap_self_assessed_tax_dist_all
207 WHERE invoice_id = l_invoice_id
208 AND ((cash_posted_flag = 'N'
209 AND l_cash_basis_flag = 'Y')
210 OR
211 (accrual_posted_flag = 'N'
212 AND l_cash_basis_flag <> 'Y'))
213 )
214 -- bug fix 6975868 begin
215 UNION
216 SELECT cash_posted_flag
217 FROM ap_self_assessed_tax_dist_all
218 WHERE invoice_id = l_invoice_id
219 AND l_cash_basis_flag = 'Y'
220 UNION
221 SELECT accrual_posted_flag
222 FROM ap_self_assessed_tax_dist_all
223 WHERE invoice_id = l_invoice_id
224 AND l_cash_basis_flag <>'Y'
225 UNION
226 SELECT 'P'
227 FROM ap_self_assessed_tax_dist_all
228 WHERE invoice_id = l_invoice_id
229 AND ((cash_posted_flag = 'Y'
230 AND l_cash_basis_flag = 'Y')
231 OR
232 (accrual_posted_flag = 'Y'
233 AND l_cash_basis_flag <> 'Y'))
234 AND EXISTS
235 (SELECT 'An N is also in the valid flags'
236 FROM ap_self_assessed_tax_dist_all
237 WHERE invoice_id = l_invoice_id
238 AND ((cash_posted_flag = 'N'
239 AND l_cash_basis_flag = 'Y')
240 OR
241 (accrual_posted_flag = 'N'
242 AND l_cash_basis_flag <> 'Y'))
243 UNION /*Added for bug 10039729*/
244 SELECT 'An N is also in the valid flags'
245 FROM ap_invoice_distributions_all
246 WHERE invoice_id = l_invoice_id
247 AND ((cash_posted_flag = 'N'
248 AND l_cash_basis_flag = 'Y')
249 OR
250 (accrual_posted_flag = 'N'
251 AND l_cash_basis_flag <> 'Y'))
252 UNION
253 SELECT 'An N is also in the valid flags'
254 FROM ap_prepay_history_all
255 WHERE invoice_id = l_invoice_id
256 AND posted_flag = 'N'
257 AND transaction_type = 'PREPAYMENT APPLICATION ADJ')
258 UNION
259 -- bug9440144
260 SELECT posted_flag
261 FROM ap_prepay_history_all
262 WHERE invoice_id = l_invoice_id
263 AND transaction_type = 'PREPAYMENT APPLICATION ADJ'
264 UNION /*Added for bug 10039729*/
265 SELECT 'P'
266 FROM ap_prepay_history_all
267 WHERE invoice_id = l_invoice_id
268 AND transaction_type = 'PREPAYMENT APPLICATION ADJ'
269 AND posted_flag = 'Y'
270 AND EXISTS
271 (SELECT 'An N is also in the valid flags'
272 FROM ap_invoice_distributions_all
273 WHERE invoice_id = l_invoice_id
274 AND ((cash_posted_flag = 'N'
275 AND l_cash_basis_flag = 'Y')
276 OR
277 (accrual_posted_flag = 'N'
278 AND l_cash_basis_flag <> 'Y'))
279 UNION
280 SELECT 'An N is also in the valid flags'
281 FROM ap_prepay_history_all
282 WHERE invoice_id = l_invoice_id
283 AND posted_flag = 'N'
284 AND transaction_type = 'PREPAYMENT APPLICATION ADJ'
285 UNION
286 SELECT 'An N is also in the valid flags'
287 FROM ap_self_assessed_tax_dist_all
288 WHERE invoice_id = l_invoice_id
289 AND ((cash_posted_flag = 'N'
290 AND l_cash_basis_flag = 'Y')
291 OR
292 (accrual_posted_flag = 'N'
293 AND l_cash_basis_flag <> 'Y'))
294 ); -- bug fix 6975868;
295
296
297 -- bug fix 6975868 end
298 BEGIN
299
300 /*-----------------------------------------------------------------+
301 | Get Accounting Methods |
302 | MOAC. Added org_id to select statement. |
303 +-----------------------------------------------------------------*/
304
305 SELECT nvl(sob.sla_ledger_cash_basis_flag, 'N'),
306 asp.org_id
307 INTO l_cash_basis_flag,
308 l_org_id
309 FROM ap_invoices_all ai,
310 ap_system_parameters_all asp,
311 gl_sets_of_books sob
312 WHERE ai.invoice_id = l_invoice_id
313 AND ai.org_id = asp.org_id
314 AND asp.set_of_books_id = sob.set_of_books_id;
315
316 invoice_posting_flag := 'X';
317
318 OPEN posting_cursor;
319
320 LOOP
321 FETCH posting_cursor INTO distribution_posting_flag;
322 EXIT WHEN posting_cursor%NOTFOUND;
323
324 IF (distribution_posting_flag = 'S') THEN
325 invoice_posting_flag := 'S';
326 ELSIF (distribution_posting_flag = 'P' AND
327 invoice_posting_flag <> 'S') THEN
328 invoice_posting_flag := 'P';
329 ELSIF (distribution_posting_flag = 'N' AND
330 invoice_posting_flag NOT IN ('S','P')) THEN
331 invoice_posting_flag := 'N';
332 ELSIF (distribution_posting_flag IS NULL) THEN
333 invoice_posting_flag := 'N';
334 END IF;
335
336 IF (invoice_posting_flag NOT IN ('S','P','N')) THEN
337 invoice_posting_flag := 'Y';
338 END IF;
339 END LOOP;
340 CLOSE posting_cursor;
341
342 if (invoice_posting_flag = 'X') then
343 invoice_posting_flag := 'N';
344 end if;
345
346 --bug6160540
347 if invoice_posting_flag = 'N' then
348
349 BEGIN
350 SELECT 'D'
351 INTO invoice_posting_flag
352 FROM ap_invoice_distributions_all AID,
353 xla_events XE
354 WHERE AID.invoice_id = l_invoice_id
355 AND AID.accounting_event_id = XE.event_id
356 AND ((AID.accrual_posted_flag = 'N' AND l_cash_basis_flag = 'N') OR
357 (AID.cash_posted_flag = 'N' AND l_cash_basis_flag = 'Y'))
358 AND XE.process_status_code = 'D'
359 AND rownum < 2;
360 EXCEPTION
361 WHEN OTHERS THEN
362 NULL;
363 END;
364
365 end if;
366
367 RETURN(invoice_posting_flag);
368 END get_posting_status;
369
370 /*=============================================================================
371 | FUNCTION - CHECK_UNIQUE
372 |
373 | DESCRIPTION
374 | Check if the invoice number within one vendor is unique.
375 |
376 | KNOWN ISSUES:
377 |
378 | NOTES
379 | MODIFICATION HISTORY
380 | Date Author Description of Change
381 |
382 *============================================================================*/
383
384 PROCEDURE CHECK_UNIQUE (
385 X_ROWID VARCHAR2,
386 X_INVOICE_NUM VARCHAR2,
387 X_VENDOR_ID NUMBER,
388 X_ORG_ID NUMBER, -- Bug 5407785
389 X_PARTY_SITE_ID NUMBER, /*Bug9105666*/
390 X_VENDOR_SITE_ID NUMBER, /*Bug9105666*/
391 X_calling_sequence IN VARCHAR2) IS
392
393 dummy_a number := 0;
394 dummy_b number := 0;
395 current_calling_sequence VARCHAR2(2000);
396 debug_info VARCHAR2(100);
397
398 BEGIN
399
400 current_calling_sequence := 'AP_INVOICES_UTILITY_PKG.CHECK_UNIQUE<-'||
401 X_calling_sequence;
402
403 debug_info := 'Count for same vendor_id,party_site_id and invoice_num'; /*Bug9105666*/
404
405 select count(1)
406 into dummy_a
407 from ap_invoices_all
408 where invoice_num = X_INVOICE_NUM
409 and vendor_id = X_VENDOR_ID
410 and org_id = X_ORG_ID -- Bug 5407785
411 AND (party_site_id = X_PARTY_SITE_ID /*Bug9105666*/
412 OR (party_site_id is null and X_PARTY_SITE_ID is null)) /*Bug9105666*/
413 and ((X_ROWID is null) or (rowid <> X_ROWID));
414
415 if (dummy_a >= 1) then
416 fnd_message.set_name('SQLAP','AP_ALL_DUPLICATE_VALUE');
417 app_exception.raise_exception;
418 end if;
419
420 debug_info := 'Count for same vendor_id,party_site_id invoice_num amount purged invoices'; /*Bug9105666*/
421
422 select count(1)
423 into dummy_b
424 from ap_history_invoices_all ahi,
425 ap_supplier_sites_all ass /*Bug9105666*/
426 where ahi.vendor_id = ass.vendor_id /*Bug9105666*/
427 and ahi.org_id = ass.org_id /*Bug9105666*/
428 and ahi.invoice_num = X_INVOICE_NUM
429 and ahi.vendor_id = X_VENDOR_ID -- Bug 5407785
430 and ahi.org_id = X_ORG_ID
431 AND (ass.party_site_id = X_PARTY_SITE_ID /*Bug9105666*/
432 OR (ass.party_site_id is null and X_PARTY_SITE_ID is null)); /*Bug9105666*/
433
434 if (dummy_b >= 1) then
435 fnd_message.set_name('SQLAP','AP_ALL_DUPLICATE_VALUE');
436 app_exception.raise_exception;
437 end if;
438
439
440 EXCEPTION
441 WHEN OTHERS THEN
442 IF (SQLCODE <> -20001) THEN
443 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
444 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
445 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
446 current_calling_sequence);
447 FND_MESSAGE.SET_TOKEN('PARAMETERS',
448 'X_Rowid = ' ||X_Rowid
449 ||', X_INVOICE_NUM = '||X_INVOICE_NUM
450 ||', X_VENDOR_ID = ' ||X_VENDOR_ID
451 );
452 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
453 END IF;
454 APP_EXCEPTION.RAISE_EXCEPTION;
455 end CHECK_UNIQUE;
456
457 /*=============================================================================
458 | procedure - CHECK_UNIQUE_VOUCHER_NUM
459 |
460 | DESCRIPTION
461 | Check if the invoice number within one vendor is unique.
462 |
463 | KNOWN ISSUES:
464 |
465 | NOTES
466 | MODIFICATION HISTORY
467 | Date Author Description of Change
468 |
469 *============================================================================*/
470
471 PROCEDURE CHECK_UNIQUE_VOUCHER_NUM (
472 X_ROWID VARCHAR2,
473 X_VOUCHER_NUM VARCHAR2,
474 X_calling_sequence IN VARCHAR2) IS
475
476 dummy number := 0;
477 current_calling_sequence VARCHAR2(2000);
478 debug_info VARCHAR2(100);
479
480 BEGIN
481
482 current_calling_sequence := 'AP_INVOICES_PKG.CHECK_UNIQUE_VOUCHER_NUM<-'
483 || X_calling_sequence;
484
485 debug_info := 'Count other invoices with same voucher num';
486
487 select count(1)
488 into dummy
489 from ap_invoices
490 where voucher_num = X_VOUCHER_NUM
491 and ((X_ROWID is null) or (rowid <> X_ROWID));
492
493 IF (dummy >= 1) THEN
494 fnd_message.set_name('SQLAP','AP_ALL_DUPLICATE_VALUE');
495 app_exception.raise_exception;
496 END IF;
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_Rowid = ' ||X_Rowid
507 ||', X_VOUCHER_NUM = '||X_VOUCHER_NUM);
508 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
509 END IF;
510 APP_EXCEPTION.RAISE_EXCEPTION;
511
512 END CHECK_UNIQUE_VOUCHER_NUM;
513
514 /*=============================================================================
515 | FUNCTION - get_approval_status
516 |
517 | DESCRIPTION
518 | returns the invoice approval status lookup code.
519 |
520 | KNOWN ISSUES:
521 |
522 | NOTES
523 | Invoices -'APPROVED'
524 | 'NEEDS REAPPROVAL'
525 | 'NEVER APPROVED'
526 | 'CANCELLED'
527 |
528 | Prepayments - 'AVAILABLE'
529 | 'CANCELLED'
530 | 'FULL'
531 | 'UNAPPROVED'
532 | 'UNPAID'
533 |
534 | MODIFICATION HISTORY
535 | Date Author Description of Change
536 |
537 *============================================================================*/
538
539 FUNCTION get_approval_status(
540 l_invoice_id IN NUMBER,
541 l_invoice_amount IN NUMBER,
542 l_payment_status_flag IN VARCHAR2,
543 l_invoice_type_lookup_code IN VARCHAR2)
544 RETURN VARCHAR2 IS
545
546 invoice_approval_status VARCHAR2(25);
547 invoice_approval_flag VARCHAR2(2);
548 distribution_approval_flag VARCHAR2(1);
549 encumbrance_flag VARCHAR2(1);
550 invoice_holds NUMBER;
551 cancelled_date DATE;
552 sum_distributions NUMBER;
553 dist_var_hold NUMBER;
554 match_flag_cnt NUMBER;
555 self_match_flag_cnt NUMBER; --Bug8223290
556 l_validated_cnt NUMBER;
557 l_org_id FINANCIALS_SYSTEM_PARAMS_ALL.ORG_ID%TYPE;
558 l_force_revalidation_flag VARCHAR2(1);
559 --Bugfix: 3854385
560 l_dist_variance VARCHAR2(20) := 'DIST VARIANCE';
561 l_line_variance VARCHAR2(20) := 'LINE VARIANCE';
562
563 --9503673
564 l_net_of_retainage_flag VARCHAR2(1);
565 l_retained_amt NUMBER := 0;
566 ---------------------------------------------------------------------
567 -- Declare cursor to establish the invoice-level approval flag
568 --
569 -- The first select simply looks at the match status flag for the
570 -- distributions. The rest is to cover one specific case when some
571 -- of the distributions are tested (T or A) and some are untested
572 -- (NULL). The status should be needs reapproval (N).
573 --
574 -- Bug 963755: Modified the approval_cursor below to select the records
575 -- correctly.
576
577 -- MOAC. Use the tables instead of the SO views in this function
578
579 -- bug6822570, changed the cursor to fetch
580 -- 'N', in place of NULL, as for match_status_flag
581 -- NULL and 'N' are the same.
582
583 -- bug 9078049 We will consider flag 'Z' for null values or new distributions
584 -- Flag 'N' will only be used for modified distributions
585 /* Bug 13608357 start */
586 N_flag_count NUMBER;
587 Z_flag_count NUMBER;
588 A_flag_count NUMBER;
589 T_flag_count NUMBER;
590
591 cursor status_cnt_cur(l_status varchar2) IS
592 select sum(l_count)
593 from
594 (
595 SELECT count(*) l_count
596 FROM ap_invoice_distributions_all
597 WHERE invoice_id = l_invoice_id
598 AND nvl(match_status_flag, 'Z') = l_status
599 UNION
600 SELECT count(*) l_count
601 FROM ap_self_Assessed_tax_dist_All
602 WHERE invoice_id = l_invoice_id
603 AND nvl(match_status_flag, 'Z') = l_status
604 );
605 /* Bug 13608357 end */
606 BEGIN
607
608 ---------------------------------------------------------------------
609 -- Get the encumbrance flag
610 -- MOAC. Included select from ap_invoices_all to get the org_id from
611 -- the invoice_id since it is unique
612
613
614
615 SELECT NVL(fsp.purch_encumbrance_flag,'N'),
616 ai.org_id,
617 ai.force_revalidation_flag,
618 NVL(ai.net_of_retainage_flag,'N') --9503673
619 INTO encumbrance_flag,
620 l_org_id,
621 l_force_revalidation_flag,
622 l_net_of_retainage_flag --9503673
623 FROM ap_invoices_all ai,
624 financials_system_params_all fsp
625 WHERE ai.invoice_id = l_invoice_id
626 AND ai.set_of_books_id = fsp.set_of_books_id
627 AND ai.org_id = fsp.org_id;
628
629 ---------------------------------------------------------------------
630 -- Get the number of holds for the invoice
631 --
632 SELECT count(*)
633 INTO invoice_holds
634 FROM ap_holds_all
635 WHERE invoice_id = l_invoice_id
636 AND release_lookup_code is NULL;
637
638 ---------------------------------------------------------------------
639 -- Bug 787373: Check if DIST VAR hold is placed on this invoice.
640 -- DIST VAR is a special case because it could be placed
641 -- when no distributions exist and in this case, the invoice
642 -- status should be NEEDS REAPPROVAL.
643 --
644 --Bugfix:4539514, added line_variance to the WHERE clause
645 SELECT count(*)
646 INTO dist_var_hold
647 FROM ap_holds_all
648 WHERE invoice_id = l_invoice_id
649 AND hold_lookup_code IN (l_dist_variance, l_line_variance)
650 AND release_lookup_code is NULL;
651
652 ---------------------------------------------------------------------
653 -- If invoice is cancelled, return 'CANCELLED'.
654 --
655 SELECT ai.cancelled_date
656 INTO cancelled_date
657 FROM ap_invoices_all ai
658 WHERE ai.invoice_id = l_invoice_id;
659
660 IF (cancelled_date IS NOT NULL) THEN
661 RETURN('CANCELLED');
662 END IF;
663
664 ---------------------------------------------------------------------
665 -- Bug 963755: Getting the count of distributions with
666 -- match_status_flag not null. We will open the approval_cursor
667 -- only if the count is more than 0.
668 --
669 SELECT count(*)
670 INTO match_flag_cnt
671 FROM ap_invoice_distributions_all aid
672 WHERE aid.invoice_id = l_invoice_id
673 AND aid.match_status_flag IS NOT NULL
674 AND rownum < 2;
675
676 SELECT count(*) --Bug8223290
677 INTO self_match_flag_cnt
678 FROM ap_self_assessed_tax_dist_all aid
679 WHERE aid.invoice_id = l_invoice_id
680 --AND aid.match_status_flag IS NOT NULL
681 AND rownum < 2;
682
683 ---------------------------------------------------------------------
684 -- Establish the invoice-level approval flag
685 --
686 -- Use the following ordering sequence to determine the invoice-level
687 -- approval flag:
688 -- 'Z' - Never Approved
689 -- 'N' - Needs Reapproval
690 -- 'T' - Tested
691 -- 'A' - Approved
692 -- '' - Never Approved (Old)
693 -- 'NA' - Never Approved (New per bug 6705321 - epajaril)
694 -- Handled the status 'NA' in the code (bug6822570)
695 -- 'X' - No Distributions Exist! --666401
696 --
697 -- Initialize invoice-level approval flag
698 --
699 invoice_approval_flag := 'X';
700
701 IF match_flag_cnt > 0 OR self_match_flag_cnt > 0 THEN --Bug8223290
702
703 /* Bug 13608357 Main change start*/
704 OPEN status_cnt_cur('N');
705 FETCH status_cnt_cur into N_flag_count;
706 CLOSE status_cnt_cur;
707
708 OPEN status_cnt_cur('Z');
709 FETCH status_cnt_cur into Z_flag_count;
710 CLOSE status_cnt_cur;
711
712 OPEN status_cnt_cur('T');
713 FETCH status_cnt_cur into T_flag_count;
714 CLOSE status_cnt_cur;
715
716 OPEN status_cnt_cur('A');
717 FETCH status_cnt_cur into A_flag_count;
718 CLOSE status_cnt_cur;
719
720 IF N_flag_count > 0 THEN invoice_approval_flag := 'N';
721 ELSIF(Z_flag_count > 0 and T_flag_count = 0 and A_flag_count = 0) THEN invoice_approval_flag := 'NA';
722 ELSIF(Z_flag_count > 0 and (T_flag_count > 0 or A_flag_count > 0)) THEN invoice_approval_flag := 'N';
723 ELSIF(T_flag_count > 0 and (A_flag_count = 0 or A_flag_count > 0)) THEN invoice_approval_flag := 'T';
724 ELSIF(A_flag_count > 0 ) THEN invoice_approval_flag := 'A';
725 ELSE invoice_approval_flag := 'X';
726 END IF;
727 /* Bug 13608357 Main change end*/
728 END IF; -- end of match_flag_cnt
729
730 --ETAX: Invwkb
731
732 -- bug6822570, validated that the condition is correct for the present
733 -- logic
734 IF l_force_revalidation_flag = 'Y' THEN
735 IF invoice_approval_flag NOT IN ('X','NA') THEN
736 invoice_approval_flag := 'N';
737 ELSE
738 IF match_flag_cnt > 0 THEN
739
740 SELECT count(*)
741 INTO l_validated_cnt
742 FROM ap_invoice_distributions_all aid
743 WHERE aid.invoice_id = l_invoice_id
744 AND aid.match_status_flag = 'N'
745 AND rownum < 2;
746
747 IF l_validated_cnt > 0 THEN
748 invoice_approval_flag := 'N';
749 END IF;
750
751 END IF;
752 END IF;
753 END IF;
754
755
756 --Bugfix:4745464, 4923489 (modified the IF condition)
757
758 -- bug6822570
759 -- Changed the condition since the Invoice Approval
760 -- Flag would never be NULL, and this check is required
761 -- only when the Invoice status is approved, and there
762 -- is no dist var hold
763
764 IF ((invoice_approval_flag IN ('A', 'T')) AND
765 (dist_var_hold = 0)) THEN
766
767 BEGIN
768
769 SELECT 'N'
770 INTO invoice_approval_flag
771 FROM ap_invoice_lines_all ail
772 WHERE ail.invoice_id = l_invoice_id
773 AND ail.amount <>
774 ( SELECT NVL(SUM(NVL(aid.amount,0)),0)
775 FROM ap_invoice_distributions_all aid
776 WHERE aid.invoice_id = ail.invoice_id
777 AND aid.invoice_line_number = ail.line_number
778 --bugfix:4959567
779 AND ( aid.line_type_lookup_code <> 'RETAINAGE'
780 OR (ail.line_type_lookup_code = 'RETAINAGE RELEASE' AND
781 aid.line_type_lookup_code = 'RETAINAGE') )
782 /*
783 AND (ail.line_type_lookup_code <> 'ITEM'
784 OR (aid.line_type_lookup_code <> 'PREPAY'
785 and aid.prepay_tax_parent_id IS NULL)
786 )
787 */
788 AND (AIL.line_type_lookup_code NOT IN ('ITEM', 'RETAINAGE RELEASE')
789 OR (AIL.line_type_lookup_code IN ('ITEM', 'RETAINAGE RELEASE')
790 AND (AID.prepay_distribution_id IS NULL
791 OR (AID.prepay_distribution_id IS NOT NULL
792 AND AID.line_type_lookup_code NOT IN ('PREPAY', 'REC_TAX', 'NONREC_TAX')))))
793 );
794
795 EXCEPTION WHEN OTHERS THEN
796 NULL;
797 END;
798
799 END IF;
800
801 -- bug6047348
802 -- Changed this condition also same as the above
803
804 IF ((invoice_approval_flag in ('A', 'T')) AND
805 (dist_var_hold = 0)) THEN
806
807 BEGIN
808
809 SELECT 'N'
810 INTO invoice_approval_flag
811 FROM ap_invoice_lines_all AIL, ap_invoices_all A
812 WHERE AIL.invoice_id = A.invoice_id
813 AND AIL.invoice_id = l_invoice_id
814 AND ((AIL.line_type_lookup_code <> 'TAX'
815 and (AIL.line_type_lookup_code NOT IN ('AWT','PREPAY')
816 or NVL(AIL.invoice_includes_prepay_flag,'N') = 'Y') OR
817 (AIL.line_type_lookup_code = 'TAX'
818 /* bug 5222316 */
819 and (AIL.prepay_invoice_id IS NULL
820 or (AIL.prepay_invoice_id is not null
821 and NVL(AIL.invoice_includes_prepay_flag, 'N') = 'Y')))))
822 -- and AIL.prepay_invoice_id IS NULL)))
823 GROUP BY A.invoice_id, A.invoice_amount, A.net_of_retainage_flag
824 HAVING A.invoice_amount <>
825 nvl(SUM(nvl(AIL.amount,0) + decode(A.net_of_retainage_flag,
826 'Y', nvl(AIL.retained_amount,0),0)),0);
827
828 EXCEPTION WHEN OTHERS THEN
829 NULL;
830 END;
831
832 END IF;
833
834
835 ---------------------------------------------------------------------
836 -- Bug 719322: Bug 719322 was created by the fix to bug 594189. Re-fix
837 -- for bug 594189 would fix bug 719322.
838
839 -- Re-fix for bug 594189
840 -- With encumbrance on, if after an invoice has been approved, the
841 -- user changes the invoice amount, then the invoice amount would
842 -- no longer match the sum of the distribution amounts. In this case,
843 -- the status should go to 'NEEDS REAPPROVAL'.
844
845 -- eTax Uptake. Use of prepay_distribution_id to determine
846 -- if the REC_TAX and NONREC_TAX distribution are related
847 -- to the prepayment application and should be included in the
848 -- total of the distributions if the invoice_includes_prepay_flag is
849 -- Y. Included ap_invoice_lines_all in select since the flag
850 -- at the dist level is obsolete.
851
852 IF (encumbrance_flag = 'Y') AND (invoice_approval_flag = 'A') THEN
853
854 SELECT NVL(SUM(nvl(aid.amount,0)), 0)
855 INTO sum_distributions
856 FROM ap_invoice_distributions_all aid,
857 ap_invoice_lines_all ail
858 WHERE ail.invoice_id = l_invoice_id
859 AND aid.invoice_id = ail.invoice_id
860 AND aid.invoice_line_number = ail.line_number
861 AND (aid.line_type_lookup_code <> 'RETAINAGE'
862 OR (ail.line_type_lookup_code = 'RETAINAGE RELEASE'
863 and aid.line_type_lookup_code = 'RETAINAGE') )
864 AND ((aid.line_type_lookup_code NOT IN ('AWT','PREPAY')
865 AND aid.prepay_distribution_id IS NULL)
866 OR NVL(ail.invoice_includes_prepay_flag,'N') = 'Y');
867
868 --Start 9503673
869 IF l_net_of_retainage_flag = 'Y' THEN
870 l_retained_amt := ABS(AP_INVOICES_UTILITY_PKG.Get_retained_Total(
871 l_invoice_id,l_org_id));
872 END IF;
873 --End 9503673
874
875 --Introduces l_retained_amt in below IF clause for bug#9503673
876 IF (l_invoice_amount + l_retained_amt <> sum_distributions) THEN
877 invoice_approval_flag := 'N';
878 END IF;
879 END IF; -- end of check encumbrance_flag
880
881 ---------------------------------------------------------------------
882 -- Derive the translated approval status from the approval flag
883 --
884 IF (encumbrance_flag = 'Y') THEN
885
886 IF (invoice_approval_flag = 'A' AND invoice_holds = 0) THEN
887 invoice_approval_status := 'APPROVED';
888 ELSIF ((invoice_approval_flag in ('A') AND invoice_holds > 0)
889 OR (invoice_approval_flag IN ('T','N'))) THEN
890 invoice_approval_status := 'NEEDS REAPPROVAL';
891 ELSIF (dist_var_hold >= 1) THEN
892 --It's assumed here that the user won't place this hold
893 --manually before approving. If he does, status will be
894 --NEEDS REAPPROVAL. dist_var_hold can result when there
895 --are no distributions or there are but amounts don't
896 --match. It can also happen when an invoice is created with
897 --no distributions, then approve the invoice, then create the
898 --distribution. So, in this case, although the match flag
899 --is null, we still want to see the status as NEEDS REAPPR.
900 invoice_approval_status := 'NEEDS REAPPROVAL';
901 -- bug6822570, removed the condition for the Invoice Approval flag
902 -- being NULL, and added the condition for 'NA'
903 ELSIF (invoice_approval_flag IN ('X','NA') AND dist_var_hold = 0) THEN
904 invoice_approval_status := 'NEVER APPROVED';
905 END IF;
906
907 ELSIF (encumbrance_flag = 'N') THEN
908 IF (invoice_approval_flag IN ('A','T') AND invoice_holds = 0) THEN
909 invoice_approval_status := 'APPROVED';
910 ELSIF ((invoice_approval_flag IN ('A','T') AND invoice_holds > 0) OR
911 (invoice_approval_flag = 'N')) THEN
912 invoice_approval_status := 'NEEDS REAPPROVAL';
913 ELSIF (dist_var_hold >= 1) THEN
914 invoice_approval_status := 'NEEDS REAPPROVAL';
915 -- bug6822570, removed the condition for the invoice approval flag
916 -- being NULL, and added the condition for 'NA'
917 ELSIF (invoice_approval_flag IN ('X','NA') AND dist_var_hold = 0) THEN
918 -- Bug 787373: A NULL flag indicate that APPROVAL has not
919 -- been run for this invoice, therefore, even if manual
920 -- holds exist, status should be NEVER APPROVED.
921 invoice_approval_status := 'NEVER APPROVED';
922 END IF;
923 END IF;
924
925 ---------------------------------------------------------------------
926 -- If this a prepayment, find the appropriate prepayment status
927 --
928 if (l_invoice_type_lookup_code = 'PREPAYMENT') then
929 if (invoice_approval_status = 'APPROVED') then
930 if (NVL(l_payment_status_flag , 'N') <> 'Y') then --bug6598052
931 invoice_approval_status := 'UNPAID';
932 else
933 -- This prepayment is paid
934 if (AP_INVOICES_UTILITY_PKG.get_prepay_amount_remaining(l_invoice_id) = 0) then
935 invoice_approval_status := 'FULL';
936 elsif (AP_INVOICES_UTILITY_PKG.get_prepayment_type(l_invoice_id) = 'PERMANENT') THEN
937 invoice_approval_status := 'PERMANENT';
938 else
939 invoice_approval_status := 'AVAILABLE';
940 end if; -- end of check AP_INVOICES_UTILITY_PKG call
941 end if; -- end of check l_payment_status_flag
942 elsif (invoice_approval_status = 'NEVER APPROVED') then
943 -- This prepayment in unapproved
944 invoice_approval_status := 'UNAPPROVED';
945 end if; -- end of invoice_approval_status
946 end if; -- end of l_invoice_type_lookup_code
947
948 RETURN(invoice_approval_status);
949 END get_approval_status;
950
951 /*===========================================================================
952 | FUNCTION - get_po_number
953 |
954 | DESCRIPTION
955 | returns the PO number matched to invoice, or
956 | with. the 'UNMATCHED' lookup code if not matched, or the
957 | 'ANY MULTIPLE'lookup code if matched to multiple POs.
958 | Because of Lines project, price correction, quantity correction should
959 | be taken into account on top op base match. The logic is based on the
960 | following assumptions:
961 | 1. po_header_id and po_line_location_id are populated for both
962 | receipt matching and po matching
963 | 2. it does not take CHARGES_TO_RECEIPT match type into account.
964 |
965 | KNOWN ISSUES:
966 |
967 | NOTES:
968 | Bug# 450052: Added GROUP BY and HAVING clauses to make sure that
969 | if all distributions matched to a PO have been reversed, it is not
970 | considered matched
971 |
972 | MODIFICATION HISTORY
973 | Date Author Description of Change
974 |
975 *==========================================================================*/
976
977 FUNCTION get_po_number(l_invoice_id IN NUMBER)
978 RETURN VARCHAR2 IS
979 po_number VARCHAR2(50) := 'UNMATCHED'; -- for CLM Bug 9503239
980 l_line_matched_amount NUMBER;
981 l_po_header_id NUMBER;
982 l_corrected_amount NUMBER;
983 l_invoice_type ap_invoices_all.invoice_type_lookup_code%TYPE; --7550789
984
985 /* Bug 4669905. Modified the Cursor */
986 CURSOR po_number_cursor IS
987 SELECT DISTINCT NVL(ph.CLM_DOCUMENT_NUMBER , ph.segment1), ph.po_header_id, -- for CLM Bug 9503239
988 NVL(SUM(L.amount),0)
989 FROM ap_invoice_lines_all L,
990 po_headers PH
991 WHERE L.invoice_id = l_invoice_id
992 AND L.po_header_id = PH.po_header_id
993 AND L.match_type IN ( 'PRICE_CORRECTION', 'QTY_CORRECTION',
994 'ITEM_TO_PO', 'ITEM_TO_RECEIPT', 'AMOUNT_CORRECTION',
995 'RETRO PRICE ADJUSTMENT','ITEM_TO_SERVICE_PO','ITEM_TO_SERVICE_RECEIPT') --Bug6931134
996 --added ITEM_TO_SERVICE_RECEIPT in bug 8891266
997 AND NVL (L.discarded_flag, 'N' ) <> 'Y'
998 AND NVL (L.cancelled_flag, 'N' ) <> 'Y'
999 GROUP BY PH.po_header_id, NVL(ph.CLM_DOCUMENT_NUMBER , ph.segment1) -- for CLM Bug 9503239
1000 HAVING ( NVL(SUM(L.amount), 0) <> 0 OR
1001 NVL(SUM(L.quantity_invoiced), 0) <> 0);
1002
1003 BEGIN
1004
1005 OPEN po_number_cursor;
1006 LOOP
1007 FETCH po_number_cursor
1008 INTO po_number, l_po_header_id,
1009 l_line_matched_amount;
1010 EXIT WHEN po_number_cursor%NOTFOUND;
1011
1012 --Added below Select for bug 7550789
1013
1014 SELECT invoice_type_lookup_code
1015 INTO l_invoice_type
1016 FROM ap_invoices_all
1017 WHERE invoice_id=l_invoice_id;
1018
1019 IF (po_number_cursor%ROWCOUNT > 1) THEN
1020 po_number := 'ANY MULTIPLE';
1021 EXIT;
1022 ELSE /* Bug 4669905 */
1023 SELECT NVL(SUM(AIL.amount), 0)
1024 INTO l_corrected_amount
1025 FROM ap_invoice_lines_all AIL
1026 WHERE corrected_inv_id = l_invoice_id
1027 AND po_header_id = l_po_header_id
1028 AND NVL( AIL.discarded_flag, 'N' ) <> 'Y'
1029 AND NVL( AIL.cancelled_flag, 'N' ) <> 'Y' ;
1030
1031 IF l_invoice_type IN ('CREDIT','DEBIT') THEN --bug7550789
1032 IF ((-1)*l_corrected_amount >= (-1)*l_line_matched_amount) THEN
1033 po_number := 'UNMATCHED';
1034 END IF;
1035 ELSE
1036 IF ((-1)*l_corrected_amount >= l_line_matched_amount) THEN
1037 po_number := 'UNMATCHED';
1038 END IF;
1039 END IF; --bug7550789
1040 END IF;
1041 END LOOP;
1042 CLOSE po_number_cursor;
1043
1044 RETURN(po_number);
1045
1046 END get_po_number;
1047
1048 /*=============================================================================
1049 | FUNCTION - get_release_number
1050 |
1051 | DESCRIPTION
1052 | returns the release number matched to invoice for a BLANKET PO, or
1053 | the 'UNMATCHED' lookup code if not matched or matched to a combination
1054 | of BLANKET/NON-BLANKET POs, or the 'ANY MULTIPLE' lookup code if
1055 | matched to multiple POs (all of which must be BLANKET).
1056 |
1057 | KNOWN ISSUES:
1058 |
1059 | NOTES:
1060 |
1061 | MODIFICATION HISTORY
1062 | Date Author Description of Change
1063 |
1064 *============================================================================*/
1065
1066 FUNCTION get_release_number(l_invoice_id IN NUMBER)
1067 RETURN VARCHAR2 IS
1068 po_release_number VARCHAR2(25) := 'UNMATCHED';
1069 l_shipment_type po_line_locations.shipment_type%TYPE;
1070
1071 CURSOR po_shipment_type_cursor IS
1072 SELECT DISTINCT(pll.shipment_type)
1073 FROM ap_invoice_lines L,
1074 po_line_locations PLL
1075 WHERE L.invoice_id = l_invoice_id
1076 AND NOT EXISTS (SELECT AIL.corrected_inv_id
1077 FROM ap_invoice_lines AIL
1078 WHERE NVL( AIL.discarded_flag, 'N' ) <> 'Y'
1079 AND NVL( AIL.cancelled_flag, 'N' ) <> 'Y'
1080 AND AIL.corrected_inv_id = L.invoice_id)
1081 AND L.po_line_location_id = PLL.line_location_id
1082 AND L.match_type IN ( 'PRICE_CORRECTION', 'QTY_CORRECTION',
1083 'ITEM_TO_PO', 'ITEM_TO_RECEIPT',
1084 'RETRO PRICE ADJUSTMENT')
1085 /*
1086 5000309 fbreslin: exclude line if discared or cancled
1087 */
1088 AND NVL (L.discarded_flag, 'N' ) <> 'Y'
1089 AND NVL (L.cancelled_flag, 'N' ) <> 'Y'
1090 GROUP BY PLL.shipment_type
1091 HAVING ( NVL(SUM(L.amount), 0) <> 0 OR
1092 NVL(SUM(L.quantity_invoiced), 0) <> 0);
1093
1094
1095 CURSOR po_release_number_cursor IS
1096 SELECT DISTINCT(PRL.release_num)
1097 FROM ap_invoice_lines L,
1098 po_line_locations PLL,
1099 po_releases PRL
1100 WHERE L.invoice_id = l_invoice_id
1101 AND NOT EXISTS (SELECT AIL.corrected_inv_id
1102 FROM ap_invoice_lines AIL
1103 WHERE NVL( AIL.discarded_flag, 'N' ) <> 'Y'
1104 AND NVL( AIL.cancelled_flag, 'N' ) <> 'Y'
1105 AND AIL.corrected_inv_id = L.invoice_id)
1106 AND L.po_line_location_id = PLL.line_location_id
1107 AND L.match_type IN ( 'PRICE_CORRECTION', 'QTY_CORRECTION',
1108 'ITEM_TO_PO', 'ITEM_TO_RECEIPT',
1109 'RETRO PRICE ADJUSTMENT')
1110 /*
1111 5000309 fbreslin: exclude line if discared or cancled
1112 */
1113 AND NVL (L.discarded_flag, 'N' ) <> 'Y'
1114 AND NVL (L.cancelled_flag, 'N' ) <> 'Y'
1115 AND PRL.po_release_id = PLL.po_release_id
1116 GROUP BY PRL.release_num
1117 HAVING ( NVL(SUM(L.amount), 0) <> 0 OR
1118 NVL(SUM(L.quantity_invoiced), 0) <> 0);
1119
1120 BEGIN
1121
1122 OPEN po_shipment_type_cursor;
1123 LOOP
1124 FETCH po_shipment_type_cursor INTO l_shipment_type;
1125 EXIT WHEN po_shipment_type_cursor%NOTFOUND;
1126
1127 IF (po_shipment_type_cursor%ROWCOUNT > 1) THEN
1128 po_release_number := NULL;
1129 EXIT;
1130 END IF;
1131 END LOOP;
1132 CLOSE po_shipment_type_cursor;
1133
1134 if (po_release_number is not NULL) then
1135 OPEN po_release_number_cursor;
1136 LOOP
1137 FETCH po_release_number_cursor INTO po_release_number;
1138 EXIT WHEN po_release_number_cursor%NOTFOUND;
1139 IF (po_release_number_cursor%ROWCOUNT > 1) THEN
1140 po_release_number := 'ANY MULTIPLE';
1141 EXIT;
1142 END IF;
1143 END LOOP;
1144 CLOSE po_release_number_cursor;
1145 else
1146 po_release_number := 'UNMATCHED';
1147 end if;
1148
1149 RETURN(po_release_number);
1150
1151 END get_release_number;
1152
1153 /*=============================================================================
1154 | FUNCTION - get_receipt_number
1155 |
1156 | DESCRIPTION
1157 | returns the receipt number matched to invoice, or the 'UNMATCHED'
1158 | lookup code if not matched, or the 'ANY MULTIPLE' lookup code if
1159 | matched to multiple receipts.
1160 |
1161 | KNOWN ISSUES:
1162 |
1163 | NOTES:
1164 |
1165 | MODIFICATION HISTORY
1166 | Date Author Description of Change
1167 |
1168 *============================================================================*/
1169
1170 FUNCTION get_receipt_number(l_invoice_id IN NUMBER)
1171 RETURN VARCHAR2 IS
1172 receipt_number RCV_SHIPMENT_HEADERS.RECEIPT_NUM%TYPE:= 'UNMATCHED'; --Bug 16413390
1173
1174 CURSOR receipt_number_cursor IS
1175 SELECT DISTINCT(rsh.receipt_num)
1176 FROM ap_invoice_lines L,
1177 rcv_transactions RTXN,
1178 rcv_shipment_headers RSH
1179 WHERE L.invoice_id = l_invoice_id
1180 AND NOT EXISTS (SELECT AIL.corrected_inv_id
1181 FROM ap_invoice_lines AIL
1182 WHERE NVL( AIL.discarded_flag, 'N' ) <> 'Y'
1183 AND NVL( AIL.cancelled_flag, 'N' ) <> 'Y'
1184 AND AIL.corrected_inv_id = L.invoice_id)
1185 AND L.rcv_transaction_id = RTXN.transaction_id
1186 AND RSH.shipment_header_id = RTXN.shipment_header_id
1187 AND L.match_type IN ( 'PRICE_CORRECTION', 'QTY_CORRECTION',
1188 'ITEM_TO_RECEIPT',
1189 'RETRO PRICE ADJUSTMENT')
1190 /*
1191 5000309 fbreslin: exclude line if discared or cancled
1192 */
1193 AND NVL (L.discarded_flag, 'N' ) <> 'Y'
1194 AND NVL (L.cancelled_flag, 'N' ) <> 'Y'
1195 GROUP BY rsh.shipment_header_id, rsh.receipt_num
1196 HAVING ( NVL(SUM(L.amount), 0) <> 0 OR
1197 NVL(SUM(L.quantity_invoiced), 0) <> 0);
1198
1199 BEGIN
1200
1201 OPEN receipt_number_cursor;
1202 LOOP
1203 FETCH receipt_number_cursor INTO receipt_number;
1204 EXIT WHEN receipt_number_cursor%NOTFOUND;
1205
1206 IF (receipt_number_cursor%ROWCOUNT > 1) THEN
1207 receipt_number := 'ANY MULTIPLE';
1208 EXIT;
1209 END IF;
1210
1211 END LOOP;
1212 CLOSE receipt_number_cursor;
1213
1214 RETURN(receipt_number);
1215 END get_receipt_number;
1216
1217 /*=============================================================================
1218 | FUNCTION - get_po_number_list
1219 |
1220 | DESCRIPTION
1221 | returns all the PO Numbers matched to this invoice (comma delimited)
1222 | or NULL if not matched.
1223 |
1224 | KNOWN ISSUES:
1225 |
1226 | NOTES:
1227 | Bug# 450052: Added GROUP BY and HAVING clauses to make sure that
1228 | if all distributions matched to a PO have been reversed, it is not
1229 | considered matched
1230 |
1231 | MODIFICATION HISTORY
1232 | Date Author Description of Change
1233 |
1234 *============================================================================*/
1235
1236 FUNCTION get_po_number_list(l_invoice_id IN NUMBER)
1237 RETURN VARCHAR2 IS
1238 po_number VARCHAR2(50); -- for CLM Bug 9503239
1239 po_number_list VARCHAR2(5000) := NULL; -- for CLM Bug 9503239
1240
1241 CURSOR po_number_cursor IS
1242 SELECT DISTINCT(NVL(ph.CLM_DOCUMENT_NUMBER , ph.segment1)) -- for CLM Bug 9503239
1243 FROM ap_invoice_lines L,
1244 po_headers PH
1245 WHERE L.invoice_id = l_invoice_id
1246 AND NOT EXISTS (SELECT AIL.corrected_inv_id
1247 FROM ap_invoice_lines AIL
1248 WHERE NVL( AIL.discarded_flag, 'N' ) <> 'Y'
1249 AND NVL( AIL.cancelled_flag, 'N' ) <> 'Y'
1250 AND AIL.corrected_inv_id = L.invoice_id)
1251 AND L.po_header_id = PH.po_header_id
1252 AND L.match_type IN ( 'PRICE_CORRECTION', 'QTY_CORRECTION',
1253 'ITEM_TO_PO', 'ITEM_TO_RECEIPT',
1254 'RETRO PRICE ADJUSTMENT')
1255 /*
1256 5000309 fbreslin: exclude line if discared or cancled
1257 */
1258 AND NVL (L.discarded_flag, 'N' ) <> 'Y'
1259 AND NVL (L.cancelled_flag, 'N' ) <> 'Y'
1260 GROUP BY PH.po_header_id, NVL(ph.CLM_DOCUMENT_NUMBER , ph.segment1) -- for CLM Bug 9503239
1261 HAVING ( NVL(SUM(L.amount), 0) <> 0 OR
1262 NVL(SUM(L.quantity_invoiced), 0) <> 0);
1263
1264 BEGIN
1265
1266 OPEN po_number_cursor;
1267 LOOP
1268 FETCH po_number_cursor INTO po_number;
1269 EXIT WHEN po_number_cursor%NOTFOUND;
1270 IF (po_number_list IS NOT NULL) THEN
1271 po_number_list := po_number_list || ', ';
1272 END IF;
1273 po_number_list := po_number_list || po_number;
1274
1275 END LOOP;
1276 CLOSE po_number_cursor;
1277
1278 RETURN(po_number_list);
1279
1280 END get_po_number_list;
1281
1282 /*=============================================================================
1283 | FUNCTION - get_amount_withheld
1284 |
1285 | DESCRIPTION
1286 | returns the AWT withheld amount on an invoice.
1287 |
1288 | KNOWN ISSUES:
1289 |
1290 | NOTES:
1291 |
1292 | MODIFICATION HISTORY
1293 | Date Author Description of Change
1294 |
1295 *============================================================================*/
1296
1297 FUNCTION get_amount_withheld(l_invoice_id IN NUMBER)
1298 RETURN NUMBER IS
1299 amount_withheld NUMBER := 0;
1300 BEGIN
1301
1302 select (0 - sum(nvl(amount,0)))
1303 into amount_withheld
1304 from ap_invoice_distributions_all /*Bug 16316552*/
1305 where invoice_id = l_invoice_id
1306 and line_type_lookup_code = 'AWT';
1307
1308 return(amount_withheld);
1309
1310 END get_amount_withheld;
1311
1312 /*=============================================================================
1313 | FUNCTION - get_prepaid_amount
1314 |
1315 | DESCRIPTION
1316 | rreturns the prepayment amount on on an invoice.
1317 |
1318 | KNOWN ISSUES:
1319 |
1320 | NOTES:
1321 |
1322 | MODIFICATION HISTORY
1323 | Date Author Description of Change
1324 |
1325 *============================================================================*/
1326
1327 FUNCTION get_prepaid_amount(l_invoice_id IN NUMBER)
1328 RETURN NUMBER IS
1329 l_prepaid_amount NUMBER := 0;
1330 BEGIN
1331 -- eTax Uptake. This function maybe obsolete in the future, but for
1332 -- now it should be consistent. Use the ap_prepay_utils_pkg API.
1333
1334 l_prepaid_amount := ap_prepay_utils_pkg.get_prepaid_amount(l_invoice_id);
1335
1336 return(l_prepaid_amount);
1337
1338 END get_prepaid_amount;
1339
1340
1341 /*=============================================================================
1342 | FUNCTION - get_notes_count
1343 |
1344 | DESCRIPTION
1345 | returns the number of notes associated with an invoice
1346 |
1347 | KNOWN ISSUES:
1348 |
1349 | NOTES:
1350 |
1351 | MODIFICATION HISTORY
1352 | Date Author Description of Change
1353 |
1354 *============================================================================*/
1355
1356 FUNCTION get_notes_count(l_invoice_id IN NUMBER)
1357 RETURN NUMBER IS
1358 notes_count NUMBER := 0;
1359 BEGIN
1360
1361 SELECT COUNT(*)
1362 INTO notes_count
1363 FROM po_note_references
1364 WHERE table_name = 'AP_INVOICES'
1365 AND foreign_id = l_invoice_id;
1366
1367 return(notes_count);
1368
1369 END get_notes_count;
1370
1371 /*=============================================================================
1372 | FUNCTION - get_holds_count
1373 |
1374 | DESCRIPTION
1375 | returns the number of unreleased holds placed on an invoice.
1376 |
1377 | KNOWN ISSUES:
1378 |
1379 | NOTES:
1380 |
1381 | MODIFICATION HISTORY
1382 | Date Author Description of Change
1383 |
1384 *============================================================================*/
1385
1386 FUNCTION get_holds_count(l_invoice_id IN NUMBER)
1387 RETURN NUMBER
1388 IS
1389 holds_count NUMBER := 0;
1390 BEGIN
1391
1392 SELECT COUNT(*)
1393 INTO holds_count
1394 FROM ap_holds
1395 WHERE release_lookup_code is null
1396 AND invoice_id = l_invoice_id;
1397
1398 RETURN (holds_count);
1399
1400 END get_holds_count;
1401
1402 /*=============================================================================
1403 | FUNCTION - get_sched_holds_count
1404 |
1405 | DESCRIPTION
1406 | returns the number of unreleased holds placed on an payment schedules.
1407 |
1408 | KNOWN ISSUES:
1409 |
1410 | NOTES:
1411 |
1412 | MODIFICATION HISTORY
1413 | Date Author Description of Change
1414 |
1415 *============================================================================*/
1416
1417 FUNCTION get_sched_holds_count(l_invoice_id IN NUMBER)
1418 RETURN NUMBER
1419 IS
1420 holds_count NUMBER := 0;
1421 BEGIN
1422
1423 SELECT COUNT(*)
1424 INTO holds_count
1425 FROM ap_payment_schedules_all
1426 WHERE hold_flag = 'Y'
1427 AND invoice_id = l_invoice_id;
1428
1429 RETURN (holds_count);
1430
1431 END get_sched_holds_count;
1432
1433 /*=============================================================================
1434 | FUNCTION - get_total_prepays
1435 |
1436 | DESCRIPTION
1437 | returns the total number of prepayments that exist for a vendor
1438 | (not fully applied, not permanent).We've declared a server-side
1439 | function that can be accessed from our invoices view so as to improve
1440 | performance when retrieving invoices in the Invoice Gateway.
1441 |
1442 | KNOWN ISSUES:
1443 |
1444 | NOTES:
1445 |
1446 | MODIFICATION HISTORY
1447 | Date Author Description of Change
1448 |
1449 *============================================================================*/
1450
1451 FUNCTION get_total_prepays(
1452 l_vendor_id IN NUMBER,
1453 l_org_id IN NUMBER)
1454 RETURN NUMBER
1455 IS
1456 prepay_count NUMBER := 0;
1457 BEGIN
1458
1459 SELECT COUNT(*)
1460 INTO prepay_count
1461 FROM ap_invoices ai
1462 WHERE vendor_id = l_vendor_id
1463 AND (( l_org_id IS NOT NULL AND
1464 ai.org_id = l_org_id)
1465 OR l_org_id IS NULL)
1466 AND invoice_type_lookup_code = 'PREPAYMENT'
1467 AND earliest_settlement_date IS NOT NULL
1468 AND AP_INVOICES_UTILITY_PKG.get_prepay_amount_remaining(ai.invoice_id) > 0;
1469
1470 RETURN(prepay_count);
1471
1472 END get_total_prepays;
1473
1474 /*=============================================================================
1475 | FUNCTION - get_available_prepays
1476 |
1477 | DESCRIPTION
1478 | returns the number of available prepayments to a vendor which can be
1479 | applied. We've declared a server-side function that can be accessed
1480 | from our invoices view so as to improve performance when retrieving
1481 | invoices in the Invoice Gateway.
1482 |
1483 | KNOWN ISSUES:
1484 |
1485 | NOTES:
1486 |
1487 | MODIFICATION HISTORY
1488 | Date Author Description of Change
1489 | 09-JAN-06 KGURUMUR Made changes for improving performance
1490 *============================================================================*/
1491
1492 FUNCTION get_available_prepays(
1493 l_vendor_id IN NUMBER,
1494 l_org_id IN NUMBER)
1495 RETURN NUMBER
1496 IS
1497 prepay_count NUMBER := 0;
1498 l_prepay_amount_remaining NUMBER:=0;
1499 /*Bug4579216
1500 Replaced the existing logic with a cursor defined for the same
1501 which just selects the prepayment invoices for the vendor.This
1502 is done for performance overheads.The comparison of earliest
1503 settlement date would be done with the cursor variable,also the
1504 earlier select statement which would call the get_total_prepays
1505 as a filter is removed and logic is implemented here as this would reduce the wait time*/
1506 CURSOR prepayment_invoices IS
1507 SELECT earliest_settlement_date,invoice_id
1508 from
1509 ap_invoices
1510 where vendor_id=l_vendor_id
1511 and invoice_type_lookup_code='PREPAYMENT'
1512 /*7015402*/
1513 and payment_status_flag = 'Y'
1514 and earliest_settlement_date is not null
1515 AND (( l_org_id IS NOT NULL AND
1516 org_id = l_org_id)
1517 OR l_org_id IS NULL);
1518
1519 BEGIN
1520 /*Bug 4579216*/
1521 for i in prepayment_invoices
1522 loop
1523 if(i.earliest_settlement_date<=(sysdate)) then
1524 l_prepay_amount_remaining:=0;
1525 l_prepay_amount_remaining:=
1526 AP_INVOICES_UTILITY_PKG.get_prepay_amount_remaining(i.invoice_id);
1527 if(l_prepay_amount_remaining>0) then
1528 prepay_count:=prepay_count+1;
1529 end if;
1530 end if;
1531 end loop;
1532 return(prepay_count);
1533
1534 END get_available_prepays;
1535
1536 /*=============================================================================
1537 | FUNCTION - get_encumbered_flag()
1538 |
1539 | DESCRIPTION
1540 | returns the invoice-level encumbrance status of an invoice.
1541 |
1542 | KNOWN ISSUES:
1543 |
1544 | NOTES:
1545 | ---------------------------------------------------------------------
1546 | -- Establish the invoice-level encumbrance flag.
1547 | -- Function will return one of the following statuses
1548 | --
1549 | -- 'Y' - Fully encumbered
1550 | -- 'P' - One or more distributions is
1551 | -- encumbered, but not all
1552 | -- 'N' - No distributions are encumbered
1553 | -- '' - Budgetary control disabled
1554 | --
1555 | ---------------------------------------------------------------------
1556 | -- Meaning of distribution encumbrance_flag:
1557 | -- Y: Regular line, has already been successfully encumbered by AP.
1558 | -- W: Regular line, has been encumbered in advisory mode even though
1559 | -- insufficient funds existed.
1560 | -- H: Line has not been encumbered yet, since it was put on hold.
1561 | -- N or Null : Line not yet seen by this code.
1562 | -- D: Same as Y for reversal distribution line.
1563 | -- X: Same as W for reversal distribution line.
1564 | -- P: Same as H for reversal distribution line.
1565 | -- R: Same as N for reversal distribution line.
1566 |
1567 | MODIFICATION HISTORY
1568 | Date Author Description of Change
1569 |
1570 *============================================================================*/
1571
1572 FUNCTION get_encumbered_flag(l_invoice_id IN NUMBER)
1573 RETURN VARCHAR2
1574 IS
1575 l_purch_encumbrance_flag VARCHAR2(1) := '';
1576 l_encumbered_flag VARCHAR2(1) := '';
1577 l_distribution_count number := 0;
1578 l_encumbered_count number := 0;
1579 l_org_id FINANCIALS_SYSTEM_PARAMS_ALL.ORG_ID%TYPE;
1580
1581 CURSOR encumbrance_flag_cursor is
1582 SELECT nvl(encumbered_flag,'N')
1583 FROM ap_invoice_distributions
1584 WHERE invoice_id = l_invoice_id;
1585
1586 /*7388641 - Checking encumbrance for invoice having just self
1587 assessed tax distributions, not a normal distributions and
1588 encumbrance is enabled */
1589
1590 CURSOR encumb_flag_in_self_tax_cursor is
1591 SELECT nvl(encumbered_flag,'N')
1592 FROM ap_self_assessed_tax_dist
1593 WHERE invoice_id = l_invoice_id;
1594
1595 BEGIN
1596
1597 SELECT NVL(fsp.purch_encumbrance_flag,'N'), ai.org_id
1598 INTO l_purch_encumbrance_flag, l_org_id
1599 FROM ap_invoices_all ai,
1600 financials_system_params_all fsp
1601 WHERE ai.invoice_id = l_invoice_id
1602 AND ai.org_id = fsp.org_id;
1603
1604 IF (l_purch_encumbrance_flag = 'N') THEN
1605 RETURN('');
1606 END IF;
1607
1608 OPEN encumbrance_flag_cursor;
1609 LOOP
1610 FETCH encumbrance_flag_cursor INTO l_encumbered_flag;
1611 EXIT WHEN encumbrance_flag_cursor%NOTFOUND;
1612 IF (l_encumbered_flag in ('Y','D','W','X')) THEN
1613 l_encumbered_count := l_encumbered_count + 1;
1614 END IF;
1615 l_distribution_count := l_distribution_count + 1;
1616 END LOOP;
1617
1618 /*7388641 Taking the count of encumbrance distributions
1619 if self assed tax distributions exists for invoice */
1620 OPEN encumb_flag_in_self_tax_cursor;
1621 LOOP
1622 FETCH encumb_flag_in_self_tax_cursor INTO l_encumbered_flag;
1623 EXIT WHEN encumb_flag_in_self_tax_cursor%NOTFOUND;
1624 IF (l_encumbered_flag in ('Y','D','W','X')) THEN
1625 l_encumbered_count := l_encumbered_count + 1;
1626 END IF;
1627 l_distribution_count := l_distribution_count + 1;
1628 END LOOP;
1629
1630 --End of 7388641
1631
1632 IF (l_encumbered_count > 0) THEN
1633 -- At least one distribution is encumbered
1634 IF (l_distribution_count = l_encumbered_count) THEN
1635 -- Invoice is fully encumbered
1636 RETURN('Y');
1637 ELSE
1638 -- Invoice is partially encumbered
1639 RETURN('P');
1640 END IF;
1641 ELSE
1642 -- No distributions are encumbered
1643 RETURN('N');
1644 END IF;
1645
1646 END get_encumbered_flag;
1647
1648 /*=============================================================================
1649 | FUNCTION - get_amount_hold_flag
1650 |
1651 | DESCRIPTION
1652 | returns a flag designating whether an invoice has unreleased amounts
1653 | holds We've declared a server-side function that can be accessed from
1654 | our invoices view so as to improve performance when retrieving invoices
1655 | in the Invoice Gateway.
1656 |
1657 | KNOWN ISSUES:
1658 |
1659 | NOTES:
1660 |
1661 | MODIFICATION HISTORY
1662 | Date Author Description of Change
1663 |
1664 *============================================================================*/
1665
1666 FUNCTION get_amount_hold_flag(l_invoice_id IN NUMBER)
1667 RETURN VARCHAR2
1668 IS
1669 l_amount_hold_flag VARCHAR2(1) := 'N';
1670 --Bugfix:3854385
1671 l_amount VARCHAR2(10) := 'AMOUNT';
1672
1673 cursor amount_hold_flag_cursor is
1674 SELECT 'Y'
1675 FROM ap_holds
1676 WHERE invoice_id = l_invoice_id
1677 AND hold_lookup_code = l_amount
1678 AND release_lookup_code IS NULL;
1679
1680 BEGIN
1681
1682 OPEN amount_hold_flag_cursor;
1683 FETCH amount_hold_flag_cursor INTO l_amount_hold_flag;
1684 CLOSE amount_hold_flag_cursor;
1685
1686 RETURN (l_amount_hold_flag);
1687
1688 END get_amount_hold_flag;
1689
1690 /*=============================================================================
1691 | FUNCTION - get_vendor_hold_flag
1692 |
1693 | DESCRIPTION
1694 | returns a flag designating whether an invoice has unreleased vendor
1695 | holds We've declared a server-side function that can be accessed from
1696 | our invoices view so as to improve performance when retrieving invoices
1697 | in the Invoice Gateway.
1698 |
1699 | KNOWN ISSUES:
1700 |
1701 | NOTES:
1702 |
1703 | MODIFICATION HISTORY
1704 | Date Author Description of Change
1705 |
1706 *============================================================================*/
1707 FUNCTION get_vendor_hold_flag(l_invoice_id IN NUMBER)
1708 RETURN VARCHAR2
1709 IS
1710 --Bugfix: 3854385
1711 l_vendor varchar2(20) := 'VENDOR';
1712 l_vendor_hold_flag VARCHAR2(1) := 'N';
1713
1714 cursor vendor_hold_flag_cursor is
1715 SELECT 'Y'
1716 FROM ap_holds
1717 WHERE invoice_id = l_invoice_id
1718 AND hold_lookup_code = l_vendor
1719 AND release_lookup_code IS NULL;
1720
1721 BEGIN
1722 OPEN vendor_hold_flag_cursor;
1723 FETCH vendor_hold_flag_cursor INTO l_vendor_hold_flag;
1724 CLOSE vendor_hold_flag_cursor;
1725
1726 RETURN (l_vendor_hold_flag);
1727
1728 END get_vendor_hold_flag;
1729
1730 /*=============================================================================
1731 | FUNCTION - get_similar_drcr_memo
1732 |
1733 | DESCRIPTION
1734 | returns the invoice_num of an credit/debit memo that has the same
1735 | vendor, vendor_site, currency, and amount as the debit/credit memo
1736 | being validated. If this is a CREDIT then look for a similar DEBIT memo
1737 | If this is a DEBIT then look for a similar CREDIT memo. This is to try
1738 | and catch the case when the user enters a DEBIT for some returned
1739 | goods and then the vendor sends a DEBIT memo for the same return
1740 |
1741 | KNOWN ISSUES:
1742 |
1743 | NOTES:
1744 |
1745 | MODIFICATION HISTORY
1746 | Date Author Description of Change
1747 |
1748 *============================================================================*/
1749
1750 FUNCTION get_similar_drcr_memo(
1751 P_vendor_id IN number,
1752 P_vendor_site_id IN number,
1753 P_invoice_amount IN number,
1754 P_invoice_type_lookup_code IN varchar2,
1755 P_invoice_currency_code IN varchar2,
1756 P_calling_sequence IN varchar2) RETURN varchar2
1757 IS
1758 CURSOR similar_memo_cursor IS
1759 SELECT invoice_num
1760 FROM ap_invoices
1761 WHERE vendor_id = P_vendor_id
1762 AND vendor_site_id = P_vendor_site_id
1763 AND invoice_amount = P_invoice_amount
1764 AND invoice_currency_code = P_invoice_currency_code
1765 AND invoice_type_lookup_code =
1766 DECODE(P_invoice_type_lookup_code,
1767 'CREDIT','DEBIT',
1768 'DEBIT','CREDIT');
1769
1770 l_invoice_num ap_invoices.invoice_num%TYPE;
1771 current_calling_sequence VARCHAR2(2000);
1772 debug_info VARCHAR2(100);
1773
1774 BEGIN
1775
1776 current_calling_sequence := 'AP_INVOICES_PKG.get_similar_drcr_memo<-'||
1777 P_calling_sequence;
1778
1779 debug_info := 'Open cursor similar_memo_cursor';
1780
1781 OPEN similar_memo_cursor;
1782 FETCH similar_memo_cursor
1783 INTO l_invoice_num;
1784
1785 debug_info := 'Close cursor similar_memo_cursor';
1786
1787 CLOSE similar_memo_cursor;
1788
1789 RETURN(l_invoice_num);
1790
1791 EXCEPTION
1792 WHEN OTHERS THEN
1793 IF (SQLCODE <> -20001) THEN
1794 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1795 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1796 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1797 current_calling_sequence);
1798 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1799 'P_vendor_id = ' ||P_vendor_id
1800 ||', P_vendor_site_id = ' ||P_vendor_site_id
1801 ||', P_invoice_amount = ' ||P_invoice_amount
1802 ||', P_invoice_type_lookup_code = '||P_invoice_type_lookup_code
1803 ||', P_invoice_currency_code = ' ||P_invoice_currency_code
1804 );
1805 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1806 END IF;
1807 APP_EXCEPTION.RAISE_EXCEPTION;
1808 END get_similar_drcr_memo;
1809
1810 /*=============================================================================
1811 | FUNCTION - eft_bank_details_exist
1812 |
1813 | DESCRIPTION
1814 | returns TRUE if the bank details needed for payment method EFT are
1815 | present for a particular vendor site. Function returns FALSE otherwise.
1816 |
1817 | KNOWN ISSUES:
1818 |
1819 | NOTES:
1820 |
1821 | MODIFICATION HISTORY
1822 | Date Author Description of Change
1823 |
1824 *============================================================================*/
1825
1826 FUNCTION eft_bank_details_exist (
1827 P_vendor_site_id IN number,
1828 P_calling_sequence IN varchar2) RETURN boolean
1829 IS
1830
1831 l_vendor_id number;
1832 l_ext_bank_acct_id number;
1833 current_calling_sequence VARCHAR2(2000);
1834 debug_info VARCHAR2(100);
1835
1836 BEGIN
1837
1838 current_calling_sequence := 'AP_INVOICES_Utility_PKG.eft_bank_details_exist<-'||
1839 P_calling_sequence;
1840
1841 debug_info := 'Call AP IBY API';
1842
1843 SELECT vendor_id
1844 INTO l_vendor_id
1845 FROM PO_VENDOR_SITES_ALL
1846 WHERE vendor_site_id = P_vendor_site_id;
1847
1848 l_ext_bank_acct_id := AP_IBY_UTILITY_PKG.Get_Default_Iby_Bank_Acct_Id
1849 (x_vendor_id => l_vendor_id,
1850 x_vendor_site_id => p_vendor_site_id,
1851 x_payment_function => NULL,
1852 x_org_id => NULL,
1853 x_currency_code => NULL,
1854 x_calling_sequence => 'Ap_Invoices_Utility_Pkg');
1855
1856 IF l_ext_bank_acct_id IS NOT NULL THEN
1857 RETURN True;
1858 ELSE
1859 RETURN False;
1860 END IF;
1861
1862 EXCEPTION
1863 WHEN OTHERS THEN
1864 IF (SQLCODE <> -20001) THEN
1865 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1866 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1867 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1868 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1869 'P_vendor_site_id = '||P_vendor_site_id);
1870 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1871 END IF;
1872 APP_EXCEPTION.RAISE_EXCEPTION;
1873 END eft_bank_details_exist;
1874
1875 /*=============================================================================
1876 | FUNCTION - eft_bank_curr_details_exist
1877 |
1878 | DESCRIPTION
1879 | returns TRUE if the bank details (including the matching currency code)
1880 | needed for payment method EFT are present for a particular vendor
1881 | site. Function returns FALSE otherwise.
1882 |
1883 | KNOWN ISSUES:
1884 |
1885 | NOTES:
1886 |
1887 | MODIFICATION HISTORY
1888 | Date Author Description of Change
1889 |
1890 *============================================================================*/
1891
1892 FUNCTION eft_bank_curr_details_exist (
1893 P_vendor_site_id IN number,
1894 P_currency_code IN varchar2,
1895 P_calling_sequence IN varchar2) RETURN boolean
1896 IS
1897
1898 l_vendor_id number;
1899 l_ext_bank_acct_id number;
1900 current_calling_sequence VARCHAR2(2000);
1901 debug_info VARCHAR2(100);
1902
1903 BEGIN
1904
1905 current_calling_sequence := 'AP_INVOICES_Utility_PKG.eft_bank_details_exist<-'||
1906 P_calling_sequence;
1907
1908 debug_info := 'Call AP IBY API';
1909
1910 SELECT vendor_id
1911 INTO l_vendor_id
1912 FROM PO_VENDOR_SITES_ALL
1913 WHERE vendor_site_id = P_vendor_site_id;
1914
1915 l_ext_bank_acct_id := AP_IBY_UTILITY_PKG.Get_Default_Iby_Bank_Acct_Id
1916 (x_vendor_id => l_vendor_id,
1917 x_vendor_site_id => p_vendor_site_id,
1918 x_payment_function => NULL,
1919 x_org_id => NULL,
1920 x_currency_code => NULL,
1921 x_calling_sequence => 'Ap_Invoices_Utility_Pkg');
1922
1923 IF l_ext_bank_acct_id IS NOT NULL THEN
1924 RETURN True;
1925 ELSE
1926 RETURN False;
1927 END IF;
1928
1929 EXCEPTION
1930 WHEN OTHERS THEN
1931 IF (SQLCODE <> -20001) THEN
1932 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1933 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1934 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1935 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1936 'P_vendor_site_id = '||P_vendor_site_id);
1937 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1938 END IF;
1939 APP_EXCEPTION.RAISE_EXCEPTION;
1940 END eft_bank_curr_details_exist;
1941
1942 -----------------------------------------------------------------------
1943 -- Function selected_for_payment_flag returns 'Y' if an invoice
1944 -- has been selected for payment; function returns 'N' otherwise.
1945 -----------------------------------------------------------------------
1946
1947 /*=============================================================================
1948 | FUNCTION - selected_for_payment_flag
1949 |
1950 | DESCRIPTION
1951 | returns 'Y' if an invoice has been selected for payment; function
1952 | returns 'N' otherwise.
1953 |
1954 | KNOWN ISSUES:
1955 |
1956 | NOTES:
1957 |
1958 | MODIFICATION HISTORY
1959 | Date Author Description of Change
1960 |
1961 *============================================================================*/
1962
1963 FUNCTION selected_for_payment_flag (P_invoice_id IN number)
1964 RETURN varchar2
1965 IS
1966 l_flag varchar2(1) := 'N';
1967 CURSOR selected_for_payment_cursor IS
1968 SELECT 'Y'
1969 FROM AP_SELECTED_INVOICES
1970 WHERE invoice_id = P_invoice_id
1971 UNION
1972 SELECT 'Y'
1973 FROM AP_PAYMENT_SCHEDULES_ALL
1974 WHERE invoice_id = P_invoice_id
1975 AND checkrun_id IS NOT NULL;
1976
1977 BEGIN
1978
1979 OPEN selected_for_payment_cursor;
1980 FETCH selected_for_payment_cursor
1981 INTO l_flag;
1982 CLOSE selected_for_payment_cursor;
1983
1984 RETURN(l_flag);
1985
1986 END selected_for_payment_flag;
1987
1988 /*=============================================================================
1989 | FUNCTION - get_discount_pay_dists_flag
1990 |
1991 | DESCRIPTION
1992 | returns 'Y' if there are any payment distributions associated with an
1993 | invoice which are of type DISCOUNT.
1994 |
1995 | KNOWN ISSUES:
1996 |
1997 | NOTES:
1998 |
1999 | MODIFICATION HISTORY
2000 | Date Author Description of Change
2001 |
2002 *============================================================================*/
2003
2004 FUNCTION get_discount_pay_dists_flag (P_invoice_id IN number)
2005 RETURN varchar2
2006 IS
2007 l_flag varchar2(1) := 'N';
2008
2009 CURSOR payment_cursor IS
2010 SELECT 'Y'
2011 FROM ap_invoice_payments
2012 WHERE invoice_id = P_invoice_id
2013 AND nvl(discount_taken,0) <> 0;
2014
2015 BEGIN
2016
2017 OPEN payment_cursor;
2018 FETCH payment_cursor
2019 INTO l_flag;
2020 CLOSE payment_cursor;
2021
2022 RETURN(l_flag);
2023
2024 END get_discount_pay_dists_flag;
2025
2026 /*=============================================================================
2027 | FUNCTION - get_unposted_void_payment
2028 |
2029 | DESCRIPTION
2030 | returns 'Y' if an invoice has an unposted payment which is linked to
2031 | a voided check AND either the Primary or Secondary set of books is
2032 | 'Cash'.
2033 |
2034 | KNOWN ISSUES:
2035 |
2036 | NOTES:
2037 |
2038 | MODIFICATION HISTORY
2039 | Date Author Description of Change
2040 |
2041 *============================================================================*/
2042 FUNCTION get_unposted_void_payment (P_invoice_id IN number)
2043 RETURN varchar2
2044 IS
2045 l_flag varchar2(1) := 'N';
2046 l_org_id AP_SYSTEM_PARAMETERS_ALL.ORG_ID%TYPE;
2047
2048 CURSOR payment_cursor IS
2049 SELECT 'Y', p.org_id
2050 FROM ap_invoice_payments p,
2051 ap_checks c,
2052 ap_system_parameters SP
2053 WHERE p.invoice_id = P_invoice_id
2054 AND p.org_id = sp.org_id
2055 AND nvl(p.cash_posted_flag,'N') <> 'Y'
2056 AND p.check_id = c.check_id
2057 AND c.void_date IS NOT NULL
2058 AND (sp.accounting_method_option = 'Cash' OR
2059 sp.secondary_accounting_method = 'Cash');
2060
2061 BEGIN
2062
2063 OPEN payment_cursor;
2064 FETCH payment_cursor
2065 INTO l_flag, l_org_id;
2066 CLOSE payment_cursor;
2067
2068 RETURN(l_flag);
2069
2070 END get_unposted_void_payment;
2071
2072 /*=============================================================================
2073 | FUNCTION - get_prepayments_applied_flag
2074 |
2075 | DESCRIPTION
2076 | returns 'Y' if an invoice has prepayments applied to it.
2077 |
2078 | KNOWN ISSUES:
2079 |
2080 | NOTES:
2081 |
2082 | MODIFICATION HISTORY
2083 | Date Author Description of Change
2084 |
2085 *============================================================================*/
2086
2087 FUNCTION get_prepayments_applied_flag (P_invoice_id IN number)
2088 RETURN varchar2
2089 IS
2090 l_flag varchar2(1) := 'N';
2091 BEGIN
2092
2093 IF ( sign (AP_INVOICES_UTILITY_PKG.get_prepay_amount_applied(
2094 P_invoice_id)) = 1 ) THEN
2095 l_flag := 'Y';
2096 ELSE
2097 l_flag := null;
2098 END IF;
2099
2100 RETURN (l_flag);
2101
2102 END get_prepayments_applied_flag;
2103
2104 /*=============================================================================
2105 | FUNCTION - get_payments_exist_flag
2106 |
2107 | DESCRIPTION
2108 | returns 'Y' if an invoice has corresponding records in
2109 | ap_invoice_payments
2110 |
2111 | KNOWN ISSUES:
2112 |
2113 | NOTES:
2114 |
2115 | MODIFICATION HISTORY
2116 | Date Author Description of Change
2117 |
2118 *============================================================================*/
2119
2120 FUNCTION get_payments_exist_flag (P_invoice_id IN number)
2121 RETURN varchar2
2122 IS
2123 l_flag varchar2(1) := 'N';
2124
2125 CURSOR payments_exist_cursor IS
2126 SELECT 'Y'
2127 FROM ap_invoice_payments
2128 WHERE invoice_id = P_invoice_id;
2129
2130 BEGIN
2131 OPEN payments_exist_cursor;
2132 FETCH payments_exist_cursor INTO l_flag;
2133 CLOSE payments_exist_cursor;
2134
2135 RETURN (l_flag);
2136
2137 END get_payments_exist_flag;
2138
2139 /*=============================================================================
2140 | FUNCTION - get_prepay_amount_applied
2141 |
2142 | DESCRIPTION
2143 | returns the sum of the applied prepayment amounts for a given
2144 | prepayment
2145 |
2146 | KNOWN ISSUES:
2147 |
2148 | NOTES:
2149 |
2150 | MODIFICATION HISTORY
2151 | Date Author Description of Change
2152 |
2153 *============================================================================*/
2154
2155 FUNCTION get_prepay_amount_applied (P_invoice_id IN number)
2156 RETURN number
2157 IS
2158 l_prepay_amount number := 0;
2159
2160 BEGIN
2161
2162 -- eTax Uptake. This function may be obsolete in the future.
2163 -- for now call ap_prepay_utils_pkg.
2164 l_prepay_amount :=
2165 AP_PREPAY_UTILS_PKG.get_prepay_amount_applied(P_invoice_id);
2166
2167 RETURN (l_prepay_amount);
2168
2169 END get_prepay_amount_applied;
2170
2171
2172 /*=============================================================================
2173 | FUNCTION - get_prepay_amount_remaining
2174 |
2175 | DESCRIPTION
2176 | returns the sum of the unapplied prepayment amounts for a given
2177 | prepayment
2178 |
2179 | KNOWN ISSUES:
2180 |
2181 | NOTES:
2182 | Bug 1029985. Including the tax on the prepayment when calculating
2183 | the prepay_amount_remaining.
2184 |
2185 | MODIFICATION HISTORY
2186 | Date Author Description of Change
2187 |
2188 *============================================================================*/
2189
2190 FUNCTION get_prepay_amount_remaining (P_invoice_id IN number)
2191 RETURN number
2192 IS
2193 l_prepay_amount_remaining NUMBER := 0;
2194
2195 BEGIN
2196 -- eTax Uptake. This function may be obsolete in the future.
2197 -- for now call ap_prepay_utils_pkg.
2198 l_prepay_amount_remaining :=
2199 AP_PREPAY_UTILS_PKG.get_prepay_amount_remaining(P_invoice_id);
2200
2201 RETURN(l_prepay_amount_remaining);
2202
2203 END get_prepay_amount_remaining;
2204
2205 ---------------------------------------------------------------------------
2206 -- Function get_prepay_amt_rem_set was created for bug 4413272
2207 -- The prepay amount remaining function was also required to take care
2208 -- of the settlement date while calculating the amount for iexpenses team
2209 -------------------------------------------------------------------------
2210
2211 FUNCTION get_prepay_amt_rem_set(P_invoice_id IN number)
2212 RETURN number
2213 IS
2214 l_prepay_amount_remaining number:=0;
2215 cursor c_prepay_amount_remaining IS
2216 SELECT SUM(nvl(prepay_amount_remaining,amount))
2217 FROM ap_invoice_distributions_all aid,ap_invoices_all ai
2218 WHERE aid.invoice_id = P_invoice_id
2219 AND aid.line_type_lookup_code IN ('ITEM','TAX')
2220 AND nvl(aid.reversal_flag,'N') <> 'Y'
2221 AND ai.invoice_id = P_invoice_id
2222 AND ai.invoice_type_lookup_code = 'PREPAYMENT'
2223 AND ai.earliest_settlement_date IS NOT NULL
2224 AND ai.earliest_settlement_date <= trunc(SYSDATE);
2225 BEGIN
2226 OPEN c_prepay_amount_remaining;
2227 FETCH c_prepay_amount_remaining INTO l_prepay_amount_remaining;
2228 CLOSE c_prepay_amount_remaining;
2229 RETURN(l_prepay_amount_remaining);
2230 END get_prepay_amt_rem_set;
2231
2232
2233 /*=============================================================================
2234 | FUNCTION - get_prepayment_type
2235 |
2236 | DESCRIPTION
2237 | returns whether prepayment is of type "PERMANENT' which cannot be
2238 | applied or 'TEMPORARY' which can be applied.
2239 |
2240 | KNOWN ISSUES:
2241 |
2242 | NOTES:
2243 |
2244 | MODIFICATION HISTORY
2245 | Date Author Description of Change
2246 |
2247 *============================================================================*/
2248
2249 FUNCTION get_prepayment_type (P_invoice_id IN number)
2250 RETURN varchar2
2251 IS
2252 l_prepayment_type VARCHAR2(9);
2253
2254 CURSOR c_prepayment_type IS
2255 SELECT decode(AI.EARLIEST_SETTLEMENT_DATE,null,'PERMANENT','TEMPORARY')
2256 FROM ap_invoices_all ai
2257 WHERE ai.invoice_id = P_invoice_id;
2258 BEGIN
2259
2260 OPEN c_prepayment_type;
2261 FETCH c_prepayment_type INTO l_prepayment_type;
2262 CLOSE c_prepayment_type;
2263
2264 RETURN(l_prepayment_type);
2265 END get_prepayment_type;
2266
2267 /*=============================================================================
2268 | FUNCTION - get_packet_id
2269 |
2270 | DESCRIPTION
2271 | returns the invoice-level packet_id. If only one unique packet_id
2272 | exists for all distributions on an invoice, that packet_id is the
2273 | invoice-level packet_id, otherwise there is none.
2274 |
2275 | KNOWN ISSUES:
2276 |
2277 | NOTES:
2278 |
2279 | MODIFICATION HISTORY
2280 | Date Author Description of Change
2281 |
2282 *============================================================================*/
2283
2284 FUNCTION get_packet_id (P_invoice_id IN number)
2285 RETURN number
2286 IS
2287 l_packet_id number := '';
2288
2289 cursor packet_id_cursor is
2290 select decode(count(distinct(packet_id)),1,max(packet_id),'')
2291 from ap_invoice_distributions
2292 where invoice_id = P_Invoice_Id
2293 and packet_id is not null;
2294
2295 BEGIN
2296 OPEN packet_id_cursor;
2297 FETCH packet_id_cursor INTO l_packet_id;
2298 CLOSE packet_id_cursor;
2299
2300 RETURN (l_packet_id);
2301
2302 END get_packet_id;
2303
2304 /*=============================================================================
2305 | FUNCTION - get_payment_status
2306 |
2307 | DESCRIPTION
2308 | will read through every line of the payment schedules to check the
2309 | payment_status_flag value. It will return 'Y' if it is fully paid.
2310 | Other values are 'N' and 'P'
2311 |
2312 | KNOWN ISSUES:
2313 |
2314 | NOTES:
2315 |
2316 | MODIFICATION HISTORY
2317 | Date Author Description of Change
2318 |
2319 *============================================================================*/
2320
2321 FUNCTION get_payment_status( p_invoice_id IN NUMBER )
2322 RETURN VARCHAR2
2323 IS
2324 l_return_val VARCHAR2(25);
2325 l_curr_ps_flag VARCHAR2(25);
2326 temp_ps_flag VARCHAR2(25);
2327 l_ps_count NUMBER := 0;
2328
2329 CURSOR c_select_payment_status (cv_invoice_id NUMBER ) IS
2330 SELECT payment_status_flag
2331 FROM ap_payment_schedules_all
2332 WHERE invoice_id = cv_invoice_id;
2333
2334 BEGIN
2335
2336 OPEN c_select_payment_status ( p_invoice_id );
2337 LOOP
2338 FETCH c_select_payment_status into temp_ps_flag;
2339 EXIT when c_select_payment_status%NOTFOUND;
2340 l_ps_count := l_ps_count +1;
2341
2342 IF ( l_ps_count = 1 ) THEN
2343 l_curr_ps_flag := temp_ps_flag;
2344 ELSE
2345 IF ( l_curr_ps_flag <> temp_ps_flag ) THEN
2346 l_curr_ps_flag := 'P';
2347 EXIT;
2348 ELSE
2349 l_curr_ps_flag := temp_ps_flag;
2350 END IF; -- END of l_curr_ps_flag check
2351 END IF; -- END of l_ps_count check
2352 END LOOP;
2353 CLOSE c_select_payment_status;
2354
2355 IF ( l_ps_count > 0 ) THEN
2356 l_return_val := l_curr_ps_flag;
2357 ELSE
2358 l_return_val := 'N';
2359 END IF;
2360 RETURN (l_return_val );
2361
2362 END get_payment_status;
2363
2364 /*=============================================================================
2365 | FUNCTION - is_inv_pmt_prepay_posted
2366 |
2367 | DESCRIPTION
2368 | returns TRUE if an invoice has been paid/prepaid and accounting has
2369 | been done for payment/reconciliation or prepayment accordingly.
2370 |
2371 | KNOWN ISSUES:
2372 |
2373 | NOTES:
2374 |
2375 | MODIFICATION HISTORY
2376 | Date Author Description of Change
2377 |
2378 *============================================================================*/
2379
2380 FUNCTION is_inv_pmt_prepay_posted(
2381 P_invoice_id IN NUMBER,
2382 P_org_id IN NUMBER,
2383 P_discount_taken IN NUMBER,
2384 P_prepaid_amount IN NUMBER,
2385 P_automatic_offsets_flag IN VARCHAR2,
2386 P_discount_dist_method IN VARCHAR2,
2387 P_payment_status_flag IN VARCHAR2)
2388 RETURN BOOLEAN
2389 IS
2390 l_count_pmt_posted NUMBER := 0;
2391 l_count_pmt_hist_posted NUMBER := 0;
2392 l_count_prepaid_posted NUMBER := 0;
2393 l_primary_acctg_method VARCHAR2(25);
2394 l_secondary_acctg_method VARCHAR2(25);
2395 l_org_id AP_SYSTEM_PARAMETERS_ALL.ORG_ID%TYPE;
2396 BEGIN
2397
2398 select asp.accounting_method_option,
2399 nvl(asp.secondary_accounting_method, 'None'),
2400 asp.org_id
2401 into l_primary_acctg_method,
2402 l_secondary_acctg_method,
2403 l_org_id
2404 from ap_system_parameters_all asp
2405 where asp.org_id = P_org_id;
2406
2407
2408 /*-----------------------------------------------------------------+
2409 | If the invoice has been fully or partially paid and any of the |
2410 | following is true, then check for accounting of the payment: |
2411 | 1. Auto offsets is on |
2412 | 2. Running cash basis |
2413 | 3. There was a discount and the discount method is other than |
2414 | system |
2415 +-----------------------------------------------------------------*/
2416
2417
2418 IF ((p_payment_status_flag <> 'N') AND
2419 ((nvl(p_automatic_offsets_flag, 'N') = 'Y') OR
2420 (l_primary_acctg_method = 'Cash') OR
2421 (l_secondary_acctg_method = 'Cash') OR
2422 ((nvl(p_discount_taken, 0) <> 0) AND
2423 (nvl(p_discount_dist_method, 'EXPENSE') <> 'SYSTEM')))) THEN
2424
2425 select count(*)
2426 into l_count_pmt_posted
2427 from ap_invoice_payments aip
2428 where aip.posted_flag = 'Y'
2429 and aip.invoice_id = p_invoice_id;
2430
2431 select count(*)
2432 into l_count_pmt_hist_posted
2433 from ap_payment_history aph
2434 where aph.posted_flag = 'Y'
2435 and aph.check_id in (select check_id
2436 from ap_invoice_payments aip
2437 where aip.invoice_id = p_invoice_id);
2438
2439 END IF;
2440
2441 /*-----------------------------------------------------------------+
2442 | If a prepayment has been applied against the invoice and |
2443 | any of the following is true, then check for accounting of |
2444 | the prepayment application: |
2445 | 1. Auto offsets is on |
2446 | 2. Running cash basis |
2447 +-----------------------------------------------------------------*/
2448
2449 IF ((nvl(p_prepaid_amount, 0) <> 0) AND
2450 (nvl(p_automatic_offsets_flag, 'N') = 'Y' OR
2451 l_primary_acctg_method = 'Cash' OR
2452 l_secondary_acctg_method = 'Cash')) THEN
2453
2454 select count(*)
2455 into l_count_prepaid_posted
2456 from ap_invoice_distributions aid
2457 where aid.posted_flag <> 'N'
2458 and aid.invoice_id = p_invoice_id
2459 and aid.line_type_lookup_code = 'PREPAY';
2460 END IF;
2461
2462 IF (l_count_pmt_posted <> 0 OR
2463 l_count_pmt_hist_posted <> 0 OR
2464 l_count_prepaid_posted <> 0) THEN
2465 RETURN TRUE;
2466 ELSE
2467 RETURN FALSE;
2468 END IF;
2469
2470 END is_inv_pmt_prepay_posted;
2471
2472
2473 /*=============================================================================
2474 | FUNCTION - get_pp_amt_applied_on_date
2475 |
2476 | DESCRIPTION
2477 | returns the sum of the applied prepayment amounts to an invoice by a
2478 | prepayment for a given date. This has been added to fix the bug 977563
2479 |
2480 | KNOWN ISSUES:
2481 |
2482 | NOTES:
2483 |
2484 | MODIFICATION HISTORY
2485 | Date Author Description of Change
2486 |
2487 *============================================================================*/
2488
2489 FUNCTION get_pp_amt_applied_on_date (
2490 P_invoice_id IN NUMBER,
2491 P_prepay_id IN NUMBER,
2492 P_application_date IN DATE)
2493 RETURN number
2494 IS
2495 l_prepay_amt_applied NUMBER := 0;
2496
2497 BEGIN
2498
2499 SELECT SUM(aid1.amount * -1)
2500 INTO l_prepay_amt_applied
2501 FROM ap_invoice_distributions aid1, ap_invoice_distributions aid2
2502 WHERE aid1.invoice_id = P_invoice_id
2503 AND aid1.line_type_lookup_code = 'PREPAY'
2504 AND aid1.prepay_distribution_id = aid2.invoice_distribution_id
2505 AND aid2.invoice_id = P_prepay_id
2506 AND aid2.last_update_date = P_application_date ;
2507
2508 RETURN (l_prepay_amt_applied);
2509
2510 END get_pp_amt_applied_on_date;
2511
2512 /*=============================================================================
2513 | FUNCTION - get_dist_count
2514 |
2515 | DESCRIPTION
2516 | returns the count of distributions available for the given invoice_id.
2517 |
2518 | KNOWN ISSUES:
2519 |
2520 | NOTES
2521 | The same function is added as an enhancement to the Key indicators
2522 | report. The bug for the same is 1728036.
2523 |
2524 | MODIFICATION HISTORY
2525 | Date Author Description of Change
2526 |
2527 *============================================================================*/
2528
2529 FUNCTION get_dist_count (p_invoice_id IN NUMBER)
2530 RETURN NUMBER
2531 IS
2532 l_count_distributions NUMBER;
2533 BEGIN
2534
2535 SELECT count(invoice_distribution_id)
2536 INTO l_count_distributions
2537 FROM ap_invoice_distributions
2538 WHERE invoice_id = p_invoice_id;
2539
2540 RETURN l_count_distributions;
2541
2542 EXCEPTION
2543 WHEN others THEN
2544 RETURN 0;
2545 END get_dist_count;
2546
2547
2548 /*=============================================================================
2549 | FUNCTION - get_amt_applied_per_prepay
2550 |
2551 | DESCRIPTION
2552 | returns the sum of the applied prepayment amounts to an invoice by a
2553 | prepayment. This has been added to do not use a new select statement in
2554 | the expense report import program.
2555 |
2556 | KNOWN ISSUES:
2557 |
2558 | NOTES:
2559 |
2560 | MODIFICATION HISTORY
2561 | Date Author Description of Change
2562 |
2563 *===========================================================================*/
2564
2565 FUNCTION get_amt_applied_per_prepay (
2566 P_invoice_id IN NUMBER,
2567 P_prepay_id IN NUMBER)
2568 RETURN number
2569 IS
2570 l_prepay_amt_applied NUMBER := 0;
2571
2572 BEGIN
2573
2574 SELECT SUM(aid1.amount * -1)
2575 INTO l_prepay_amt_applied
2576 FROM ap_invoice_distributions aid1, ap_invoice_distributions aid2
2577 WHERE aid1.invoice_id = P_invoice_id
2578 AND aid1.line_type_lookup_code = 'PREPAY'
2579 AND aid1.prepay_distribution_id = aid2.invoice_distribution_id
2580 AND aid2.invoice_id = P_prepay_id;
2581
2582 RETURN (l_prepay_amt_applied);
2583
2584 END get_amt_applied_per_prepay;
2585
2586 /*=============================================================================
2587 | FUNCTION - get_explines_count
2588 |
2589 | DESCRIPTION
2590 | added to get the count of expense report lines for a given expense
2591 | report header id. This function was added for the enhancement to the
2592 | key indicators report.
2593 |
2594 | KNOWN ISSUES:
2595 |
2596 | NOTES:
2597 | Bug 2298873 Code added by MSWAMINA.
2598 |
2599 | MODIFICATION HISTORY
2600 | Date Author Description of Change
2601 |
2602 *===========================================================================*/
2603 FUNCTION get_explines_count (p_expense_report_id IN NUMBER)
2604 RETURN NUMBER
2605 IS
2606 l_explines_count NUMBER;
2607 BEGIN
2608
2609 SELECT count(*)
2610 INTO l_explines_count
2611 FROM ap_expense_report_lines
2612 WHERE report_header_id = p_expense_report_id;
2613
2614 RETURN l_explines_count;
2615
2616 EXCEPTION
2617 WHEN OTHERS THEN
2618 l_explines_count := 0;
2619 RETURN l_explines_count;
2620 END get_explines_count;
2621
2622
2623 /*=============================================================================
2624 | FUNCTION - get_expense_type
2625 |
2626 | DESCRIPTION
2627 | added to decide whether the information is available in in expense
2628 | reports table as well as in ap invoices or only in ap invoices
2629 |
2630 | KNOWN ISSUES:
2631 |
2632 | NOTES
2633 | If the information is available in both the table we should get the
2634 | information from ap expense report headers, if not we should get the
2635 | information from ap invoices. This was added based on the requirement
2636 | from GSI and confirmed by lauren
2637 |
2638 | MODIFICATION HISTORY
2639 | Date Author Description of Change
2640 |
2641 *===========================================================================*/
2642
2643 FUNCTION get_expense_type (
2644 p_source in varchar2,
2645 p_invoice_id in number)
2646 RETURN varchar2
2647 IS
2648 l_return_type VARCHAR2(1);
2649 BEGIN
2650
2651 IF p_source IN ('XpenseXpress', 'SelfService') THEN
2652
2653 SELECT 'E'
2654 INTO l_return_type
2655 FROM ap_expense_report_headers aerh
2656 WHERE aerh.vouchno = p_invoice_id;
2657
2658 ELSE
2659
2660 l_return_type := 'I';
2661
2662 END IF;
2663
2664 RETURN l_return_type;
2665
2666 EXCEPTION
2667 WHEN NO_DATA_FOUND THEN
2668 l_return_type := 'I';
2669 RETURN l_return_type;
2670 WHEN OTHERS THEN
2671 l_return_type := 'I';
2672 RETURN l_return_type;
2673
2674 END get_expense_type;
2675
2676 /*=============================================================================
2677 | FUNCTION - get_max_inv_line_num
2678 |
2679 | DESCRIPTION
2680 | returns the highest line number of invoice lines belonging to
2681 | invoice P_invoice_id
2682 |
2683 | KNOWN ISSUES
2684 |
2685 | NOTES
2686 |
2687 | MODIFICATION HISTORY
2688 | Date Author Description of Change
2689 |
2690 *===========================================================================*/
2691
2692 FUNCTION GET_MAX_INV_LINE_NUM(P_invoice_id IN NUMBER)
2693 RETURN NUMBER
2694 IS
2695 l_max_inv_line_num NUMBER := 0;
2696 BEGIN
2697
2698 SELECT nvl( MAX(line_number),0 )
2699 INTO l_max_inv_line_num
2700 FROM ap_invoice_lines
2701 WHERE invoice_id = P_invoice_id;
2702
2703 RETURN (l_max_inv_line_num);
2704
2705 END GET_MAX_INV_LINE_NUM;
2706
2707
2708 /*=============================================================================
2709 | FUNCTION - get_line_total
2710 |
2711 | DESCRIPTION
2712 | returns the total invoice line amount for the invoice.
2713 |
2714 | KNOWN ISSUES:
2715 |
2716 | NOTES:
2717 |
2718 | MODIFICATION HISTORY
2719 | Date Author Description of Change
2720 |
2721 *===========================================================================*/
2722
2723 FUNCTION GET_LINE_TOTAL(P_invoice_id IN NUMBER)
2724 RETURN NUMBER
2725 IS
2726 line_total NUMBER := 0;
2727 BEGIN
2728
2729 -- eTax uptake. Included condition to know if a TAX line is
2730 -- Do not include prepayment application amount if the prepayment
2731 -- is not included in the invoice. (invoice_includes_prepay_flag = N)
2732
2733 SELECT SUM(NVL(amount,0))
2734 INTO line_total
2735 FROM ap_invoice_lines ail
2736 WHERE ail.invoice_id = p_invoice_id
2737 AND ((ail.line_type_lookup_code not in ('PREPAY','AWT') --Bug 7372061 Excluded 'AWT' amount from the total line amount.
2738 AND ail.prepay_invoice_id IS NULL
2739 AND ail.prepay_line_number IS NULL)
2740 OR nvl(ail.invoice_includes_prepay_flag,'N') = 'Y');
2741
2742 RETURN(line_total);
2743
2744 END GET_LINE_TOTAL;
2745
2746 /*=============================================================================
2747 | FUNCTION - ROUND_BASE_AMTS
2748 |
2749 | DESCRIPTION
2750 | returns the rounded base amount if there is any. it returns FALSE if
2751 | no rounding amount necessary, otherwise it returns TRUE.
2752 |
2753 | Business Assumption
2754 | 1. Called after base amount of all lines is populated
2755 | 2. Same exchange rate for all the lines
2756 | 3. It will be called by Primary ledger (AP) or Reporting ledger (MRC)
2757 | 4. Returns FALSE if sum of lines amount is different than invoice
2758 | amount, since in that case the rounding is meaningless.
2759 |
2760 | PARAMETERS
2761 | X_Invoice_Id - Invoice Id
2762 | X_Reporting_Ledger_Id - For ALC/MRC use only.
2763 | X_Rounded_Line_Numbers - returns the line numbers that can be adjusted
2764 | X_Rounded_Amt - rounded amount
2765 | X_Debug_Info - debug information
2766 | X_Debug_Context - error context
2767 | X_Calling_Sequence - debug usage
2768 |
2769 | KNOWN ISSUES:
2770 |
2771 | NOTES:
2772 |
2773 | MODIFICATION HISTORY
2774 | Date Author Description of Change
2775 | 19-MAY-2008 KPASIKAN modified for 6892789 to get the lines that
2776 | can be adjusted
2777 *============================================================================*/
2778
2779 FUNCTION round_base_amts(
2780 X_Invoice_Id IN NUMBER,
2781 X_Reporting_Ledger_Id IN NUMBER DEFAULT NULL,
2782 X_Rounded_Line_Numbers OUT NOCOPY inv_line_num_tab_type,
2783 X_Rounded_Amt OUT NOCOPY NUMBER,
2784 X_Debug_Info OUT NOCOPY VARCHAR2,
2785 X_Debug_Context OUT NOCOPY VARCHAR2,
2786 X_Calling_sequence IN VARCHAR2)
2787 RETURN BOOLEAN IS
2788 l_rounded_amt NUMBER := 0;
2789 l_rounded_line_numbers inv_line_num_tab_type;
2790 l_base_currency_code ap_system_parameters.base_currency_code%TYPE;
2791 l_base_amount ap_invoices.base_amount%TYPE;
2792 l_invoice_amount ap_invoices.invoice_amount%TYPE;
2793 l_invoice_currency_code ap_invoices.invoice_currency_code%TYPE;
2794 l_reporting_currency_code ap_invoices.invoice_currency_code%TYPE;
2795 l_sum_base_amt NUMBER;
2796 l_sum_amt NUMBER;
2797 l_sum_rpt_base_amt NUMBER;
2798
2799 current_calling_sequence VARCHAR2(2000);
2800 debug_info VARCHAR2(100);
2801
2802 cursor invoice_cursor is
2803 -- inv_base_amt/rep_base_amt
2804 SELECT decode(x_reporting_ledger_id, null, AI.base_amount, null),
2805 AI.invoice_amount, -- invoice amount
2806 AI.invoice_currency_code, -- invoice_currency_code
2807 ASP.base_currency_code -- base_currency_code
2808 FROM ap_invoices AI, ap_system_parameters ASP
2809 WHERE AI.invoice_id = X_invoice_id
2810 AND ASP.org_id = AI.org_id;
2811
2812 BEGIN
2813
2814 current_calling_sequence := 'AP_INVOICES_UTILITY_PKG - Round_Base_Amt ' ||
2815 X_calling_sequence;
2816
2817 -------------------------------------------------------------
2818 debug_info := 'Round_Base_Amt - Open cursor invoice_cursor';
2819 -------------------------------------------------------------
2820
2821 OPEN invoice_cursor;
2822 FETCH invoice_cursor
2823 INTO l_base_amount,
2824 l_invoice_amount,
2825 l_invoice_currency_code,
2826 l_base_currency_code;
2827 IF (invoice_cursor%NOTFOUND) THEN
2828 CLOSE invoice_cursor;
2829 RAISE NO_DATA_FOUND;
2830 END IF;
2831 CLOSE invoice_cursor;
2832
2833 IF (X_Reporting_Ledger_Id IS NULL) THEN
2834 --------------------------------------------------------------------
2835 debug_info := 'Round_base_amt Case 1 - Rounding for primary ledger';
2836 --------------------------------------------------------------------
2837
2838 IF (l_invoice_currency_code <> l_base_currency_code) THEN
2839 BEGIN
2840 SELECT SUM(base_amount), SUM(amount)
2841 INTO l_sum_base_amt, l_sum_amt
2842 FROM ap_invoice_lines AIL
2843 WHERE AIL.invoice_id = X_INVOICE_ID
2844 AND line_type_lookup_code <> 'AWT'
2845 AND (invoice_includes_prepay_flag = 'Y' OR
2846 line_type_lookup_code <> 'PREPAY');
2847 -- eTax: Tax lines that do not contribute to lines total
2848 -- should be excluded.
2849 END;
2850
2851 IF (l_sum_amt = l_invoice_amount) THEN
2852 l_rounded_amt := l_base_amount - l_sum_base_amt;
2853 ELSE
2854 X_ROUNDED_AMT := 0;
2855 X_Rounded_Line_Numbers.delete;
2856 X_debug_context := current_calling_sequence;
2857 X_debug_info := debug_info;
2858 RETURN(FALSE);
2859 END IF;
2860 ELSE
2861 ---------------------------------------------------------------------
2862 debug_info := 'Round_Base_Amt - same inv currency/base currency';
2863 ---------------------------------------------------------------------
2864 X_ROUNDED_AMT := 0;
2865 X_Rounded_Line_Numbers.delete;
2866 X_debug_context := current_calling_sequence;
2867 X_debug_info := debug_info;
2868 RETURN(FALSE);
2869 END IF; -- end of check currency for primary
2870
2871 ELSE
2872
2873 Null; -- Removed the code here due to MRC obsoletion
2874
2875 END IF; -- end of check x_reporting_ledger_id
2876
2877 IF (l_rounded_amt <> 0) THEN
2878 --------------------------------------------------------------------
2879 debug_info := 'Round_Base_Amt - round amt exists and find the line';
2880 --------------------------------------------------------------------
2881 BEGIN
2882
2883 SELECT ail1.line_number
2884 BULK COLLECT INTO l_Rounded_Line_Numbers
2885 FROM ap_invoice_lines ail1
2886 WHERE ail1.invoice_id = X_invoice_id
2887 AND ail1.amount <> 0
2888 AND LINE_TYPE_LOOKUP_CODE not in ('TAX', 'AWT') -- bug 9582952 --bug16090813
2889 AND (EXISTS
2890 (SELECT 'UNPOSTED'
2891 FROM ap_invoice_distributions D1
2892 WHERE D1.invoice_id = ail1.invoice_id
2893 AND D1.invoice_line_number = ail1.line_number
2894 AND NVL(D1.posted_flag, 'N') = 'N') OR
2895 (NOT EXISTS
2896 (SELECT 'X'
2897 FROM ap_invoice_distributions D2
2898 WHERE D2.invoice_id = ail1.invoice_id
2899 AND D2.invoice_line_number = ail1.line_number)))
2900 ORDER BY ail1.base_amount desc;
2901
2902 END;
2903
2904 X_ROUNDED_AMT := l_rounded_amt;
2905 X_Rounded_Line_Numbers := l_rounded_line_numbers;
2906 X_debug_context := current_calling_sequence;
2907 X_debug_info := debug_info;
2908 RETURN(TRUE);
2909 ELSE
2910 ---------------------------------------------------------------------
2911 debug_info := 'Round_Base_Amt - round_amt is 0 ';
2912 ---------------------------------------------------------------------
2913 X_ROUNDED_AMT := 0;
2914 X_Rounded_Line_Numbers.delete;
2915 X_debug_context := current_calling_sequence;
2916 X_debug_info := debug_info;
2917 RETURN(FALSE);
2918 END IF; -- end of check l_rounded_amt
2919
2920 EXCEPTION
2921 WHEN OTHERS THEN
2922 if (SQLCODE <> -20001) then
2923 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
2924 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
2925 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
2926 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2927 'Invoice Id = ' || X_Invoice_Id);
2928 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
2929 end if;
2930 debug_info := debug_info || 'Error occurred';
2931 X_debug_context := current_calling_sequence;
2932 X_debug_info := debug_info;
2933 Return(FALSE);
2934 END round_base_amts;
2935
2936 /*============================================================================
2937 | FUNCTION - Is_Inv_Credit_Referenced
2938 |
2939 | DESCRIPTION
2940 | Added to check if the invoice has a QUICK CREDIT invoice against it or
2941 | if this invoice has any active (non discard/non cancelled) corrections.
2942 |
2943 | KNOWN ISSUES
2944 |
2945 | NOTES
2946 |
2947 | MODIFICATION HISTORY
2948 | Date Author Description of Change
2949 |
2950 *===========================================================================*/
2951 FUNCTION Is_Inv_Credit_Referenced( P_invoice_id IN NUMBER )
2952 RETURN BOOLEAN
2953 IS
2954 l_retVal BOOLEAN := FALSE;
2955 l_active_count NUMBER;
2956 l_quick_credit_count NUMBER:=0;
2957 BEGIN
2958
2959 -- Perf bug 5173995 , removed count(*) from below 2 SQLs
2960 BEGIN
2961 SELECT 1
2962 INTO l_active_count
2963 FROM ap_invoice_lines AIL
2964 WHERE ( NVL( AIL.discarded_flag, 'N' ) <> 'Y' AND
2965 NVL( AIL.cancelled_flag, 'N' ) <> 'Y' )
2966 AND AIL.corrected_inv_id = p_invoice_id
2967 AND ROWNUM = 1 ;
2968 EXCEPTION
2969 WHEN NO_DATA_FOUND THEN
2970 l_active_count := 0;
2971 END;
2972
2973 BEGIN
2974 --bug 5475668
2975 if (P_invoice_id is not null) then
2976 SELECT 1
2977 INTO l_quick_credit_count
2978 FROM ap_invoices AI
2979 WHERE AI.credited_invoice_id = P_invoice_id
2980 AND NVL(AI.quick_credit, 'N') = 'Y'
2981 AND AI.cancelled_date is null
2982 AND ROWNUM = 1 ;
2983 end if;
2984 EXCEPTION
2985 WHEN NO_DATA_FOUND THEN
2986 l_quick_credit_count := 0;
2987 END;
2988
2989 IF ( l_active_count <> 0 or l_quick_credit_count <> 0 ) THEN
2990 l_retVal := TRUE;
2991 END IF;
2992
2993 RETURN l_retVal;
2994 EXCEPTION
2995 WHEN OTHERS THEN
2996 RETURN FALSE;
2997 END Is_Inv_Credit_Referenced;
2998
2999 /*=============================================================================
3000 | FUNCTION - Inv_With_PQ_Corrections
3001 |
3002 | DESCRIPTION
3003 | This function returns TRUE if the invoice contains price or quantity
3004 | corrections. It returns FALSE otherwise.
3005 |
3006 | PARAMETERS
3007 | P_Invoice_Id - Invoice Id
3008 | P_Calling_Sequence - debug usage
3009 |
3010 | KNOWN ISSUES:
3011 |
3012 | NOTES:
3013 |
3014 | MODIFICATION HISTORY
3015 | Date Author Description of Change
3016 | 30-JUL-2003 SYIDNER Creation
3017 |
3018 *============================================================================*/
3019
3020 FUNCTION Inv_With_PQ_Corrections(
3021 P_Invoice_Id IN NUMBER,
3022 P_Calling_sequence IN VARCHAR2) RETURN BOOLEAN
3023
3024 IS
3025 CURSOR Invoice_Validation IS
3026 SELECT i.invoice_id
3027 FROM ap_invoices_all i
3028 WHERE i.invoice_id = P_Invoice_Id
3029 AND EXISTS
3030 (SELECT il.invoice_id
3031 FROM ap_invoice_lines_all il
3032 WHERE il.invoice_id = i.invoice_id
3033 AND NVL(il.discarded_flag, 'N') <> 'Y'
3034 AND NVL(il.cancelled_flag, 'N') <> 'Y'
3035 AND il.match_type IN ('PRICE_CORRECTION',
3036 'QTY_CORRECTION'));
3037
3038 l_invoice_id ap_invoices_all.invoice_id%TYPE;
3039 current_calling_sequence VARCHAR2(4000);
3040 debug_info VARCHAR2(240);
3041 l_return_var BOOLEAN := FALSE;
3042
3043 BEGIN
3044 current_calling_sequence := 'AP_INVOICES_UTILITY_PKG - Inv_With_PQ_Corrections';
3045
3046 -------------------------------------------------------------
3047 debug_info := 'Inv_With_PQ_Corrections - Open cursor';
3048 -------------------------------------------------------------
3049 OPEN invoice_validation;
3050 FETCH invoice_validation INTO l_invoice_id;
3051 IF (invoice_validation%NOTFOUND) THEN
3052 CLOSE invoice_validation;
3053 l_invoice_id := null;
3054
3055 END IF;
3056
3057 IF ( invoice_validation%ISOPEN ) THEN
3058 CLOSE invoice_validation;
3059 END IF;
3060
3061 IF (l_invoice_id IS NOT NULL) THEN
3062 l_return_var := TRUE;
3063
3064 END IF;
3065
3066 RETURN l_return_var;
3067
3068 EXCEPTION
3069 WHEN OTHERS THEN
3070 if (SQLCODE <> -20001) then
3071 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
3072 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
3073 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
3074 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice Id = '||P_Invoice_Id);
3075 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
3076 end if;
3077 debug_info := debug_info || 'Error occurred';
3078
3079 IF ( invoice_validation%ISOPEN ) THEN
3080 CLOSE invoice_validation;
3081 END IF;
3082
3083 APP_EXCEPTION.RAISE_EXCEPTION;
3084
3085 END Inv_With_PQ_Corrections;
3086
3087 /*=============================================================================
3088 | FUNCTION - Inv_With_Prepayments
3089 |
3090 | DESCRIPTION
3091 | This function returns TRUE if the invoice contains prepayment applications.
3092 | It returns FALSE otherwise.
3093 |
3094 | PARAMETERS
3095 | X_Invoice_Id - Invoice Id
3096 | X_Calling_Sequence - debug usage
3097 |
3098 | KNOWN ISSUES:
3099 |
3100 | NOTES:
3101 |
3102 | MODIFICATION HISTORY
3103 | Date Author Description of Change
3104 | 30-JUL-2003 SYIDNER Creation
3105 |
3106 *============================================================================*/
3107
3108 FUNCTION Inv_With_Prepayments(
3109 P_Invoice_Id IN NUMBER,
3110 P_Calling_sequence IN VARCHAR2) RETURN BOOLEAN
3111
3112 IS
3113 CURSOR Invoice_Validation IS
3114 SELECT i.invoice_id
3115 FROM ap_invoices_all i
3116 WHERE i.invoice_id = P_Invoice_Id
3117 AND EXISTS
3118 (SELECT il.invoice_id
3119 FROM ap_invoice_lines_all il
3120 WHERE il.invoice_id = i.invoice_id
3121 AND il.line_type_lookup_code = 'PREPAY'
3122 AND NVL(il.discarded_flag, 'N') <> 'Y'
3123 AND NVL(il.cancelled_flag, 'N') <> 'Y');
3124
3125 l_invoice_id ap_invoices_all.invoice_id%TYPE;
3126 current_calling_sequence VARCHAR2(4000);
3127 debug_info VARCHAR2(240);
3128 l_return_var BOOLEAN := FALSE;
3129
3130 BEGIN
3131 current_calling_sequence := 'AP_INVOICES_UTILITY_PKG - Inv_With_Prepayments';
3132
3133 -------------------------------------------------------------
3134 debug_info := 'Inv_With_Prepayments - Open cursor';
3135 -------------------------------------------------------------
3136 OPEN invoice_validation;
3137 FETCH invoice_validation INTO l_invoice_id;
3138 IF (invoice_validation%NOTFOUND) THEN
3139 CLOSE invoice_validation;
3140 l_invoice_id := null;
3141
3142 END IF;
3143
3144 IF ( invoice_validation%ISOPEN ) THEN
3145 CLOSE invoice_validation;
3146 END IF;
3147
3148 IF (l_invoice_id IS NOT NULL) THEN
3149 l_return_var := TRUE;
3150
3151 END IF;
3152
3153 RETURN l_return_var;
3154
3155 EXCEPTION
3156 WHEN OTHERS THEN
3157 if (SQLCODE <> -20001) then
3158 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
3159 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
3160 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
3161 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice Id = '||P_Invoice_Id);
3162 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
3163 end if;
3164 debug_info := debug_info || 'Error occurred';
3165
3166 IF ( invoice_validation%ISOPEN ) THEN
3167 CLOSE invoice_validation;
3168 END IF;
3169
3170 APP_EXCEPTION.RAISE_EXCEPTION;
3171
3172 END Inv_With_Prepayments;
3173
3174 /*=============================================================================
3175 | FUNCTION - Invoice_Includes_Awt
3176 |
3177 | DESCRIPTION
3178 | This function returns TRUE if the invoice contains withholding tax.
3179 | It returns FALSE otherwise.
3180 |
3181 | PARAMETERS
3182 | X_Invoice_Id - Invoice Id
3183 | X_Calling_Sequence - debug usage
3184 |
3185 | KNOWN ISSUES:
3186 |
3187 | NOTES:
3188 |
3189 | MODIFICATION HISTORY
3190 | Date Author Description of Change
3191 | 30-JUL-2003 SYIDNER Creation
3192 |
3193 *============================================================================*/
3194
3195 FUNCTION Invoice_Includes_Awt(
3196 P_Invoice_Id IN NUMBER,
3197 P_Calling_sequence IN VARCHAR2) RETURN BOOLEAN
3198
3199 IS
3200 CURSOR Invoice_Validation IS
3201 SELECT i.invoice_id
3202 FROM ap_invoices_all i
3203 WHERE i.invoice_id = P_Invoice_Id
3204 AND EXISTS
3205 (SELECT il.invoice_id
3206 FROM ap_invoice_lines_all il
3207 WHERE il.invoice_id = i.invoice_id
3208 AND il.line_type_lookup_code = 'AWT'
3209 AND NVL(il.discarded_flag, 'N') <> 'Y'
3210 AND NVL(il.cancelled_flag, 'N') <> 'Y');
3211
3212 l_invoice_id ap_invoices_all.invoice_id%TYPE;
3213 current_calling_sequence VARCHAR2(4000);
3214 debug_info VARCHAR2(240);
3215 l_return_var BOOLEAN := FALSE;
3216
3217 BEGIN
3218 current_calling_sequence := 'AP_INVOICES_UTILITY_PKG - Invoice_Includes_Awt';
3219
3220 -------------------------------------------------------------
3221 debug_info := 'Invoice_Includes_Awt - Open cursor';
3222 -------------------------------------------------------------
3223 OPEN invoice_validation;
3224 FETCH invoice_validation INTO l_invoice_id;
3225 IF (invoice_validation%NOTFOUND) THEN
3226 CLOSE invoice_validation;
3227 l_invoice_id := null;
3228
3229 END IF;
3230
3231 IF ( invoice_validation%ISOPEN ) THEN
3232 CLOSE invoice_validation;
3233 END IF;
3234
3235 IF (l_invoice_id IS NOT NULL) THEN
3236 l_return_var := TRUE;
3237
3238 END IF;
3239
3240 RETURN l_return_var;
3241
3242 EXCEPTION
3243 WHEN OTHERS THEN
3244 if (SQLCODE <> -20001) then
3245 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
3246 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
3247 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
3248 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice Id = '||P_Invoice_Id);
3249 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
3250 end if;
3251 debug_info := debug_info || 'Error occurred';
3252
3253 IF ( invoice_validation%ISOPEN ) THEN
3254 CLOSE invoice_validation;
3255 END IF;
3256
3257 APP_EXCEPTION.RAISE_EXCEPTION;
3258
3259 END Invoice_Includes_Awt;
3260
3261 /*=============================================================================
3262 | FUNCTION - Inv_Matched_Finally_Closed_Po
3263 |
3264 | DESCRIPTION
3265 | This function returns TRUE if the invoice is matched to a finally closed
3266 | PO. It returns FALSE otherwise.
3267 |
3268 | PARAMETERS
3269 | X_Invoice_Id - Invoice Id
3270 | X_Calling_Sequence - debug usage
3271 |
3272 | KNOWN ISSUES:
3273 |
3274 | NOTES:
3275 |
3276 | MODIFICATION HISTORY
3277 | Date Author Description of Change
3278 | 15-DEC-2003 SYIDNER Creation
3279 |
3280 *============================================================================*/
3281 FUNCTION Inv_Matched_Finally_Closed_Po(
3282 P_Invoice_Id IN NUMBER,
3283 P_Calling_sequence IN VARCHAR2) RETURN BOOLEAN
3284
3285 IS
3286 CURSOR Invoice_Validation IS
3287 SELECT i.invoice_id
3288 FROM ap_invoices_all i
3289 WHERE i.invoice_id = P_Invoice_Id
3290 AND EXISTS
3291 (SELECT ail.invoice_id
3292 FROM ap_invoice_lines_all ail,
3293 po_line_locations_all pll
3294 WHERE ail.invoice_id = i.invoice_id
3295 AND ail.po_line_location_id = pll.line_location_id
3296 AND ail.org_id = pll.org_id
3297 AND pll.closed_code = 'FINALLY CLOSED');
3298
3299 l_invoice_id ap_invoices_all.invoice_id%TYPE;
3300 current_calling_sequence VARCHAR2(4000);
3301 debug_info VARCHAR2(240);
3302 l_return_var BOOLEAN := FALSE;
3303
3304 BEGIN
3305 current_calling_sequence := 'AP_INVOICES_UTILITY_PKG - Inv_Matched_Finally_Closed_Po';
3306
3307 ------------------------------------------------------------
3308 debug_info := 'Open cursor to verify if the invoice is '||
3309 'matched to a finally closed PO';
3310 -------------------------------------------------------------
3311 OPEN invoice_validation;
3312 FETCH invoice_validation INTO l_invoice_id;
3313 IF (invoice_validation%NOTFOUND) THEN
3314 CLOSE invoice_validation;
3315 l_invoice_id := null;
3316
3317 END IF;
3318
3319 IF ( invoice_validation%ISOPEN ) THEN
3320 CLOSE invoice_validation;
3321 END IF;
3322
3323 IF (l_invoice_id IS NOT NULL) THEN
3324 l_return_var := TRUE;
3325 END IF;
3326
3327 RETURN l_return_var;
3328
3329 EXCEPTION
3330 WHEN OTHERS THEN
3331 if (SQLCODE <> -20001) then
3332 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
3333 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
3334 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
3335 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice Id = '||P_Invoice_Id);
3336 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
3337 end if;
3338 debug_info := debug_info || 'Error occurred';
3339
3340 IF ( invoice_validation%ISOPEN ) THEN
3341 CLOSE invoice_validation;
3342 END IF;
3343
3344 APP_EXCEPTION.RAISE_EXCEPTION;
3345
3346 END Inv_Matched_Finally_Closed_Po;
3347
3348 --Invoice Lines: Distributions
3349 --Added the procedure to retrieve the max dist line number
3350 --for a particular invoice line.
3351 -----------------------------------------------------------------------
3352 -- Function get_max_dist_line_num returns the highest distribution line
3353 -- number of distributions belonging to invoice P_invoice_id for invoice line
3354 -- p_invoice_line_number.
3355 -----------------------------------------------------------------------
3356 FUNCTION get_max_dist_line_num (P_invoice_id IN number,
3357 P_invoice_line_number IN number) RETURN number
3358 IS
3359 l_max_dist_line_num NUMBER := 0;
3360 BEGIN
3361
3362 select nvl(max(distribution_line_number),0)
3363 into l_max_dist_line_num
3364 from ap_invoice_distributions
3365 where invoice_id = P_invoice_id
3366 and invoice_line_number = P_invoice_line_number;
3367
3368 return(l_max_dist_line_num);
3369
3370 END get_max_dist_line_num;
3371
3372
3373 ---------------------------------------------------------------------
3374 --ETAX: Invwkb
3375 --This function when provided with a invoice_id, will return the
3376 --corresponding invoice_number.
3377 ---------------------------------------------------------------------
3378 FUNCTION get_invoice_num (P_Invoice_Id IN Number) RETURN VARCHAR2 IS
3379 l_invoice_num VARCHAR2(50) := NULL;
3380 BEGIN
3381
3382 SELECT invoice_num
3383 INTO l_invoice_num
3384 FROM ap_invoices
3385 WHERE invoice_id = p_invoice_id;
3386
3387 RETURN(l_invoice_num);
3388
3389
3390 EXCEPTION WHEN OTHERS THEN
3391 RETURN(NULL);
3392
3393 END get_invoice_num;
3394
3395 /*=============================================================================
3396 | FUNCTION - get_retained_total
3397 |
3398 | DESCRIPTION
3399 | returns the total retained amount for the invoice.
3400 |
3401 | KNOWN ISSUES:
3402 |
3403 | NOTES:
3404 |
3405 | MODIFICATION HISTORY
3406 | Date Author Description of Change
3407 |
3408 *===========================================================================*/
3409
3410 FUNCTION GET_RETAINED_TOTAL(P_Invoice_Id IN NUMBER, P_Org_Id IN NUMBER)
3411 RETURN NUMBER
3412 IS
3413 retained_total NUMBER := 0;
3414 BEGIN
3415
3416 SELECT SUM(NVL(amount,0))
3417 INTO retained_total
3418 FROM ap_invoice_distributions_all aid
3419 WHERE aid.invoice_id = p_invoice_id
3420 AND aid.line_type_lookup_code = 'RETAINAGE'
3421 AND EXISTS
3422 (SELECT 'X' FROM ap_invoice_lines_all ail
3423 WHERE ail.invoice_id = p_invoice_id
3424 AND ail.line_number = aid.invoice_line_number
3425 AND ail.line_type_lookup_code <> 'RETAINAGE RELEASE');
3426
3427 return (retained_total);
3428
3429 END GET_RETAINED_TOTAL;
3430
3431 /*=============================================================================
3432 | FUNCTION - get_item_total
3433 |
3434 | DESCRIPTION
3435 | returns the total item amount
3436 |
3437 | KNOWN ISSUES:
3438 |
3439 | NOTES:
3440 |
3441 | MODIFICATION HISTORY
3442 | Date Author Description of Change
3443 |
3444 *============================================================================*/
3445
3446 FUNCTION Get_Item_Total(P_Invoice_Id IN NUMBER, P_Org_Id IN NUMBER)
3447 RETURN NUMBER IS
3448
3449 item_total NUMBER := 0;
3450
3451 BEGIN
3452
3453 select sum(nvl(amount,0)) - sum(nvl(included_tax_amount,0))
3454 into item_total
3455 from ap_invoice_lines_all
3456 where invoice_id = p_invoice_id
3457 and line_type_lookup_code IN ('ITEM','RETAINAGE RELEASE');
3458
3459 return(item_total);
3460
3461 END Get_Item_Total;
3462
3463 /*=============================================================================
3464 | FUNCTION - get_freight_total
3465 |
3466 | DESCRIPTION
3467 | returns the total item amount
3468 |
3469 | KNOWN ISSUES:
3470 |
3471 | NOTES:
3472 |
3473 | MODIFICATION HISTORY
3474 | Date Author Description of Change
3475 |
3476 *============================================================================*/
3477
3478 FUNCTION Get_Freight_Total(P_Invoice_Id IN NUMBER, P_Org_Id IN NUMBER)
3479 RETURN NUMBER IS
3480
3481 freight_total NUMBER := 0;
3482
3483 BEGIN
3484
3485 select sum(nvl(amount,0)) - sum(nvl(included_tax_amount,0))
3486 into freight_total
3487 from ap_invoice_lines_all
3488 where invoice_id = p_invoice_id
3489 and org_id = p_org_id
3490 and line_type_lookup_code = 'FREIGHT';
3491
3492 return(freight_total);
3493
3494 END Get_Freight_Total;
3495
3496
3497 /*=============================================================================
3498 | FUNCTION - get_misc_total
3499 |
3500 | DESCRIPTION
3501 | returns the total item amount
3502 |
3503 | KNOWN ISSUES:
3504 |
3505 | NOTES:
3506 |
3507 | MODIFICATION HISTORY
3508 | Date Author Description of Change
3509 |
3510 *============================================================================*/
3511
3512 FUNCTION Get_Misc_Total(P_Invoice_Id IN NUMBER, P_Org_Id IN NUMBER)
3513 RETURN NUMBER IS
3514
3515 misc_total NUMBER := 0;
3516
3517 BEGIN
3518
3519 select sum(nvl(amount,0)) - sum(nvl(included_tax_amount,0))
3520 into misc_total
3521 from ap_invoice_lines_all
3522 where invoice_id = p_invoice_id
3523 and org_id = p_org_id
3524 and line_type_lookup_code = 'MISCELLANEOUS';
3525
3526 return(misc_total);
3527
3528 END Get_Misc_Total;
3529
3530 /*=============================================================================
3531 | FUNCTION - get_prepay_app_total
3532 |
3533 | DESCRIPTION
3534 | returns the total prepayments applied including recoupments
3535 |
3536 | KNOWN ISSUES:
3537 |
3538 | NOTES:
3539 |
3540 | MODIFICATION HISTORY
3541 | Date Author Description of Change
3542 |
3543 *============================================================================*/
3544
3545 FUNCTION Get_Prepay_App_Total(P_Invoice_Id IN NUMBER, P_Org_Id IN NUMBER)
3546 RETURN NUMBER IS
3547
3548 prepay_app_total NUMBER := 0;
3549
3550 BEGIN
3551
3552 select sum(nvl(amount,0))
3553 into prepay_app_total
3554 from ap_invoice_distributions_all
3555 where invoice_id = p_invoice_id
3556 and org_id = p_org_id
3557 and line_type_lookup_code = 'PREPAY';
3558
3559 return(prepay_app_total);
3560
3561 END Get_Prepay_App_Total;
3562
3563 /*=============================================================================
3564 | FUNCTION - get_invoice_status
3565 |
3566 | DESCRIPTION
3567 | returns the invoice status lookup code.
3568 |
3569 | KNOWN ISSUES:
3570 |
3571 | NOTES
3572 | ISP Invoice Statuses
3573 | IN_PROCESS
3574 | UNSUBMITTED
3575 | IN_NEGOTIATION
3576 | CANCELLED
3577 |
3578 |
3579 | MODIFICATION HISTORY
3580 | Date Author Description of Change
3581 |
3582 *============================================================================*/
3583 -- Bug 5345946 XBuild7 Code Cleanup
3584 FUNCTION get_invoice_status(
3585 p_invoice_id IN NUMBER,
3586 p_invoice_amount IN NUMBER,
3587 p_payment_status_flag IN VARCHAR2,
3588 p_invoice_type_lookup_code IN VARCHAR2)
3589 RETURN VARCHAR2 IS
3590
3591 l_invoice_status VARCHAR2(25);
3592 l_approval_ready_flag VARCHAR2(1);
3593 l_cancelled_date DATE;
3594 l_negotiate_lines_count NUMBER;
3595 l_invoice_source VARCHAR2(25);
3596 l_invoice_type_lookup_code VARCHAR2(30);
3597 l_approval_status VARCHAR2(25);
3598 l_payment_status VARCHAR2(25);
3599 l_wfapproval_status VARCHAR2(25);
3600
3601 BEGIN
3602 --
3603 SELECT ai.cancelled_date,
3604 ai.approval_ready_flag,
3605 ai.invoice_type_lookup_code,
3606 ai.source,
3607 ai.wfapproval_status /* Added for Bug 11924642 */
3608 INTO l_cancelled_date,
3609 l_approval_ready_flag,
3610 l_invoice_type_lookup_code,
3611 l_invoice_source,
3612 l_wfapproval_status
3613 FROM ap_invoices_all ai
3614 WHERE ai.invoice_id = p_invoice_id
3615 AND ai.source = 'ISP';
3616
3617 -- If cancelled date is not null, return 'CANCELLED'
3618 --
3619 IF ( l_cancelled_date IS NOT NULL) THEN
3620 RETURN('CANCELLED');
3621 END IF;
3622
3623 -- If invoice is saved for later in ISP, return 'UNSUBMITTED'.
3624 -- Temporarily approval_ready_flag = 'S' in ap_invoices_all handles the
3625 -- the unsubmitted invoices.
3626 IF ( l_approval_ready_flag = 'S' ) THEN
3627 RETURN('UNSUBMITTED');
3628 END IF;
3629
3630
3631 -- If invoice is in negotiation, return 'IN_NEGOTIATION'.
3632 --
3633 IF ( l_approval_ready_flag <> 'S' ) THEN
3634
3635 IF (l_invoice_type_lookup_code = 'INVOICE REQUEST') THEN
3636
3637 SELECT count(*)
3638 INTO l_negotiate_lines_count
3639 FROM ap_apinv_approvers
3640 WHERE invoice_id = p_invoice_id
3641 AND approval_status = 'NEGOTIATE'
3642 AND rownum =1;
3643
3644 IF ( l_negotiate_lines_count > 0 ) THEN
3645 RETURN('IN_NEGOTIATION');
3646 END IF;
3647
3648 ELSE --- Standard, Credit-Memo or Prepayments
3649
3650 SELECT count(*)
3651 INTO l_negotiate_lines_count
3652 FROM ap_holds_all
3653 WHERE invoice_id = p_invoice_id
3654 AND wf_status = 'NEGOTIATE'
3655 AND rownum =1;
3656
3657 IF ( l_negotiate_lines_count > 0 ) THEN
3658 RETURN('IN_NEGOTIATION');
3659 END IF;
3660
3661 END IF;
3662 --
3663 END IF;
3664 --
3665 /* Bug 9654503 Added call to Procedure to get Validated and Approved Status */
3666 l_approval_status := get_approval_status(p_invoice_id,
3667 p_invoice_amount,
3668 p_payment_status_flag,
3669 l_invoice_type_lookup_code);
3670 IF ( l_approval_status = 'APPROVED' ) THEN
3671 RETURN('VALIDATED');
3672 END IF;
3673 l_payment_status := get_payment_status( p_invoice_id);
3674 IF ( l_payment_status = 'Y' ) THEN
3675 RETURN('PAID');
3676 END IF;
3677 /* Bug 11924642 Added Check for Rejected Status Invoice */
3678 IF ( l_wfapproval_status = 'REJECTED' AND l_invoice_type_lookup_code = 'INVOICE REQUEST') THEN
3679 RETURN('REJECTED');
3680 END IF;
3681
3682 RETURN('IN_PROCESS');
3683 --
3684 END get_invoice_status;
3685
3686 PROCEDURE get_bank_details(
3687 p_invoice_currency_code IN VARCHAR2,
3688 p_party_id IN NUMBER,
3689 p_party_site_id IN NUMBER,
3690 p_supplier_site_id IN NUMBER,
3691 p_org_id IN NUMBER,
3692 x_bank_account_name OUT NOCOPY VARCHAR2,
3693 x_bank_account_id OUT NOCOPY VARCHAR2,
3694 x_bank_account_number OUT NOCOPY VARCHAR2) IS
3695
3696 cursor c_get_bank_details is
3697 select t.bank_account_name,
3698 t.bank_account_id,
3699 t.bank_account_number
3700 from (
3701 SELECT b.bank_account_name,
3702 b.ext_bank_account_id bank_account_id,
3703 b.bank_account_number,
3704 rank() over (partition by ibyu.instrument_id, ibyu.instrument_type order by ibyu.instrument_payment_use_id) not_dup,
3705 ibypayee.supplier_site_id,/*bug 8345877*/
3706 ibypayee.party_site_id,/*bug 8345877*/
3707 ibypayee.org_id,/*bug 8345877*/
3708 ibyu.order_of_preference /*bug 8345877*/
3709 FROM IBY_PMT_INSTR_USES_ALL ibyu,
3710 IBY_EXT_BANK_ACCOUNTS_V b,
3711 IBY_EXTERNAL_PAYEES_ALL ibypayee
3712 WHERE ibyu.instrument_id = b.ext_bank_account_id
3713 AND ibyu.instrument_type = 'BANKACCOUNT'
3714 AND (b.currency_code = p_invoice_currency_code OR b.currency_code is null
3715 OR NVL(b.foreign_payment_use_flag,'N')='Y')
3716 AND ibyu.ext_pmt_party_id = ibypayee.ext_payee_id
3717 AND ibyu.payment_flow = 'DISBURSEMENTS'
3718 AND ibypayee.payment_function = 'PAYABLES_DISB'
3719 AND ibypayee.payee_party_id = p_party_id
3720 /*bug 9462285. Modified end_date condition */
3721 AND trunc(sysdate) between trunc(NVL(ibyu.start_date,sysdate-1)) AND trunc(decode(ibyu.end_date, null, sysdate+1, ibyu.end_date-1))
3722 AND trunc(sysdate) between trunc(NVL(b.start_date,sysdate-1)) AND trunc(decode(b.end_date, null, sysdate+1, b.end_date-1))
3723 AND (ibypayee.party_site_id is null OR ibypayee.party_site_id = p_party_site_id)
3724 AND (ibypayee.supplier_site_id is null OR ibypayee.supplier_site_id = p_supplier_site_id)
3725 AND (ibypayee.org_id is null OR
3726 (ibypayee.org_id = p_org_id AND ibypayee.org_type = 'OPERATING_UNIT'))) t
3727 where t.not_dup=1 /*bug 8345877*/
3728 order by t.supplier_site_id,
3729 t.party_site_id,
3730 t.org_id,
3731 t.order_of_preference/*bug 8345877*/;
3732
3733 BEGIN
3734
3735 OPEN c_get_bank_details;
3736 FETCH c_get_bank_details INTO x_bank_account_name, x_bank_account_id, x_bank_account_number;
3737 CLOSE c_get_bank_details;
3738
3739 EXCEPTION
3740 WHEN OTHERS THEN
3741 x_bank_account_name := NULL;
3742 x_bank_account_id := NULL;
3743 x_bank_account_number := NULL;
3744 END get_bank_details;
3745
3746 /*==========================================================================
3747 | FUNCTION - get_interface_po_number
3748 |
3749 | DESCRIPTION
3750 | returns the PO number for invoice to be displayed in the Quick
3751 | invoices form.
3752 | Added for the CLM Document Numbering Project Bug 9503239
3753 |
3754 | KNOWN ISSUES:
3755 |
3756 | NOTES:
3757 |
3758 |
3759 | MODIFICATION HISTORY
3760 | Date Author Description of Change
3761 |
3762 *==========================================================================*/
3763
3764 FUNCTION get_interface_po_number(p_po_number IN VARCHAR2,
3765 p_org_id IN NUMBER)
3766 RETURN VARCHAR2 IS
3767 l_po_number VARCHAR2(50) := NULL;
3768
3769 CURSOR int_po_number_cursor IS
3770 SELECT NVL(ph.clm_document_number, ph.segment1)
3771 FROM po_headers PH
3772 WHERE ph.segment1=p_po_number
3773 AND ph.org_id=p_org_id;
3774
3775 BEGIN
3776 IF p_po_number IS NULL THEN
3777 RETURN NULL;
3778 ELSE
3779 IF ap_clm_pvt_pkg.is_clm_installed = 'Y' THEN
3780
3781 OPEN int_po_number_cursor;
3782 FETCH int_po_number_cursor INTO l_po_number;
3783 CLOSE int_po_number_cursor;
3784
3785 RETURN(l_po_number);
3786 ELSE
3787 RETURN p_po_number;
3788 END IF; --if clm installed
3789 END IF; --if p_po_number is null
3790 END get_interface_po_number;
3791
3792 /* Bug 10425573 starts */
3793
3794 /*==========================================================================
3795 | FUNCTION - Update_Invoice_Description
3796 |
3797 | DESCRIPTION
3798 | API to update the Description of CREDIT MEMO
3799 |
3800 |
3801 |
3802 | KNOWN ISSUES:
3803 |
3804 | NOTES:
3805 |
3806 |
3807 | MODIFICATION HISTORY
3808 | Date Author Description of Change
3809 | 8-Jul-2011 anubagar Created the API
3810 *==========================================================================*/
3811
3812 FUNCTION Update_Invoice_Description ( p_invoice_id IN NUMBER,
3813 p_description IN VARCHAR2,
3814 p_calling_sequence IN VARCHAR2)
3815 RETURN BOOLEAN
3816 IS
3817 l_current_calling_sequence VARCHAR2(2000);
3818 l_debug_info VARCHAR2(500);
3819 l_success BOOLEAN := FALSE;
3820 l_invoice_type AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE%TYPE;
3821 l_api_name CONSTANT VARCHAR2(200) := 'Update_Invoice_Description';
3822 BEGIN
3823
3824 l_current_calling_sequence := 'AP_UTILITIES_PKG.Update_Description <- '||p_calling_sequence;
3825 l_debug_info := 'Getting the Invoice Type';
3826 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3827 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3828 END IF;
3829
3830 SELECT Invoice_type_lookup_code
3831 INTO l_invoice_type
3832 FROM ap_invoices_all
3833 WHERE invoice_id=p_invoice_id;
3834
3835 l_debug_info := 'Updating Invoice';
3836 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3837 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3838 END IF;
3839
3840 IF (l_invoice_type = 'CREDIT') THEN
3841
3842 UPDATE ap_invoices_all
3843 SET description = p_description
3844 WHERE invoice_id = p_invoice_id;
3845
3846 l_debug_info := 'Update Invoice Description Successfully';
3847 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3848 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3849 END IF;
3850 l_success := TRUE;
3851
3852 END IF;
3853
3854 RETURN (l_success);
3855
3856 EXCEPTION
3857 WHEN NO_DATA_FOUND THEN
3858 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice id = '||to_char(p_invoice_id) || ' is Not a Credit Memo');
3859
3860 WHEN OTHERS THEN
3861 IF (SQLCODE <> -20001) THEN
3862 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
3863 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
3864 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_current_calling_sequence);
3865 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice id = '||to_char(p_invoice_id));
3866 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
3867 END IF;
3868
3869 APP_EXCEPTION.RAISE_EXCEPTION;
3870 RETURN FALSE;
3871
3872 END Update_Invoice_Description;
3873
3874 /* Bug 10425573 ends */
3875
3876 --Introduced below 2 procedures for GTAS Project
3877 --Bug#13464635
3878
3879 FUNCTION FV_ENABLED RETURN BOOLEAN IS
3880 BEGIN
3881
3882 IF(FV_INSTALL.ENABLED)THEN
3883 RETURN TRUE;
3884 ELSE
3885 RETURN FALSE;
3886 END IF;
3887 END FV_ENABLED;
3888
3889 PROCEDURE CHECK_GDF_VALID(P_id IN NUMBER,
3890 P_calling_mode IN VARCHAR2,
3891 P_table_name IN VARCHAR2,
3892 P_hold_reject_exists_flag OUT NOCOPY VARCHAR2,
3893 P_return_code OUT NOCOPY VARCHAR2,
3894 P_calling_sequence IN VARCHAR2) IS
3895
3896 current_calling_sequence VARCHAR2(2000);
3897 debug_info VARCHAR2(100);
3898 l_return_status BOOLEAN;
3899 L_INVOICE_TYPE_LOOKUP_CODE AP_INVOICES.INVOICE_TYPE_LOOKUP_CODE%TYPE;
3900 l_api_name CONSTANT VARCHAR2(200) := 'CHECK_GDF_VALID';
3901 l_debug_info VARCHAR2(1000);
3902
3903 BEGIN
3904 current_calling_sequence := 'AP_INVOICES_Utility_PKG.CHECK_GDF_VALID<-'||
3905 P_calling_sequence;
3906
3907 P_hold_reject_exists_flag := 'N';
3908 P_return_code := NULL;
3909
3910 l_debug_info := 'Check FV_ENABLED';
3911 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3912 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3913 END IF;
3914
3915 IF (AP_INVOICES_UTILITY_PKG.FV_ENABLED) THEN
3916
3917 IF(P_calling_mode = 'INV_IMP')THEN
3918 SELECT
3919 (CASE
3920 WHEN INVOICE_TYPE_LOOKUP_CODE is null
3921 and invoice_amount >=0 then
3922 'STANDARD'
3923 WHEN INVOICE_TYPE_LOOKUP_CODE is null
3924 and invoice_amount <0 then
3925 'CREDIT'
3926 ELSE INVOICE_TYPE_LOOKUP_CODE
3927 END)
3928 INTO L_INVOICE_TYPE_LOOKUP_CODE
3929 FROM AP_INVOICES_INTERFACE
3930 /* Modified for bug#15906783 */
3931 WHERE INVOICE_ID in (SELECT INVOICE_ID
3932 FROM ap_invoice_lines_interface
3933 WHERE invoice_line_id = P_ID);
3934
3935
3936 ELSIF(P_calling_mode = 'INV_VLD')THEN
3937 SELECT INVOICE_TYPE_LOOKUP_CODE
3938 INTO L_INVOICE_TYPE_LOOKUP_CODE
3939 FROM AP_INVOICES
3940 WHERE INVOICE_ID = P_ID;
3941 END IF;
3942
3943 l_debug_info := 'CALL FV api for few types of invoices';
3944 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3945 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3946 END IF;
3947
3948 IF((L_INVOICE_TYPE_LOOKUP_CODE IN ('STANDARD','DEBIT','CREDIT','MIXED','PREPAYMENT')
3949 AND P_CALLING_MODE IN ('INV_VLD','INV_IMP'))
3950 OR P_CALLING_MODE='SUP_IMP')THEN
3951
3952 l_debug_info := 'Before call to FV_GTAS_UTILITY_PKG.AP_CHECK_GDF_VALID';
3953 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3954 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3955 END IF;
3956
3957 FV_GTAS_UTILITY_PKG.AP_CHECK_GDF_VALID(
3958 P_id, --invoice_id/interface_invoice_id,supplier_id
3959 P_calling_mode, -- invoice validation (INV_VLD)/ invoice import (INV_IMP)/ supplier import (SUP_IMP)
3960 P_table_name, -- On which table FV should query
3961 P_hold_reject_exists_flag, --to release/place hold and import/reject invoice
3962 P_return_code, --hold code /reject code/null if valid
3963 l_return_status); --procedure success without any exceptions
3964
3965 IF NOT (l_return_status)THEN
3966 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3967 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
3968 current_calling_sequence);
3969 FND_MESSAGE.SET_TOKEN('PARAMETERS',
3970 'P_id = ' ||P_id
3971 ||', P_calling_mode = '||P_calling_mode
3972 ||', P_table_name = ' ||P_table_name
3973 ||', P_hold_reject_exists_flag = ' ||P_hold_reject_exists_flag
3974 ||', P_return_code = ' ||P_return_code);
3975 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
3976 APP_EXCEPTION.RAISE_EXCEPTION;
3977 END IF;
3978
3979 END IF;
3980 END IF;
3981
3982 END CHECK_GDF_VALID;
3983
3984 --End Bug#13464635
3985
3986 END AP_INVOICES_UTILITY_PKG;
3987
[Home] [Help]