1 PACKAGE BODY AP_INVOICE_LINES_UTILITY_PKG AS
2 /* $Header: apilnutb.pls 120.48.12020000.3 2013/02/13 23:28:37 tjbhatt ship $ */
3
4 /*=============================================================================
5 | FUNCTION - get_encumbered_flag()
6 |
7 | DESCRIPTION
8 | returns the invoice-level encumbrance status of an invoice.
9 | Establish the invoice line level encumbrance flag.
10 | Function will return one of the following statuses
11 | 'Y' - Fully encumbered
12 | 'P' - One or more distributions is encumbered, but not all
13 | 'N' - No distributions are encumbered
14 | '' - Budgetary control disabled
15 | PARAMETERS
16 | p_invoice_id - invoice id
17 | p_line_number - invoice line number
18 |
19 | NOTES
20 | -- Meaning of distribution encumbrance_flag:
21 | -- Y: Regular line, has already been successfully encumbered by AP.
22 | -- W: Regular line, has been encumbered in advisory mode even though
23 | -- insufficient funds existed.
24 | -- H: Line has not been encumbered yet, since it was put on hold.
25 | -- N or Null : Line not yet seen by this code.
26 | -- D: Same as Y for reversal distribution line.
27 | -- X: Same as W for reversal distribution line.
28 | -- P: Same as H for reversal distribution line.
29 | -- R: Same as N for reversal distribution line.
30 |
31 | MODIFICATION HISTORY
32 | Date Author Description of Change
33 |
34 *============================================================================*/
35
36 FUNCTION get_encumbered_flag(
37 p_invoice_id IN NUMBER,
38 p_line_number IN NUMBER )
39 RETURN VARCHAR2
40 IS
41 l_purch_encumbrance_flag VARCHAR2(1) := '';
42 l_encumbered_flag VARCHAR2(1) := '';
43 l_distribution_count number := 0;
44 l_encumbered_count number := 0;
45 l_org_id FINANCIALS_SYSTEM_PARAMS_ALL.ORG_ID%TYPE;
46
47 CURSOR encumbrance_flag_cursor is
48 SELECT nvl(encumbered_flag,'N')
49 FROM ap_invoice_distributions
50 WHERE invoice_id = p_invoice_id
51 AND invoice_line_number = p_line_number;
52 BEGIN
53
54
55 -- Added the IF condition for the bug 8763038
56 IF ( p_invoice_id IS NOT NULL ) THEN
57
58 SELECT NVL(fsp.purch_encumbrance_flag,'N'),
59 ai.org_id
60 INTO l_purch_encumbrance_flag,
61 l_org_id
62 FROM ap_invoices_all ai,
63 financials_system_params_all fsp
64 WHERE ai.invoice_id = p_invoice_id
65 AND ai.org_id = fsp.org_id;
66
67 IF (l_purch_encumbrance_flag = 'N') THEN
68 RETURN(NULL);
69 END IF;
70
71 OPEN encumbrance_flag_cursor;
72 LOOP
73 FETCH encumbrance_flag_cursor INTO l_encumbered_flag;
74 EXIT WHEN encumbrance_flag_cursor%NOTFOUND;
75 IF (l_encumbered_flag in ('Y','D', 'W','X')) THEN
76 l_encumbered_count := l_encumbered_count + 1;
77 END IF;
78 l_distribution_count := l_distribution_count + 1;
79 END LOOP;
80
81 IF (l_encumbered_count > 0) THEN
82 -- At least one distribution is encumbered
83 IF (l_distribution_count = l_encumbered_count) THEN
84 -- Invoice Line is fully encumbered
85 RETURN('Y');
86 ELSE
87 -- Invoice Line is partially encumbered
88 RETURN('P');
89 END IF;
90 ELSE
91 -- No distributions are encumbered
92 RETURN('N');
93 END IF;
94
95 ELSE
96
97 RETURN(NULL);
98
99 END IF; -- Bug 8763038
100
101 END get_encumbered_flag;
102
103
104 /*=============================================================================
105 | FUNCTION - get_posting_status
106 |
107 | DESCRIPTION
108 | returns the invoice line posting status.
109 |
110 | PARAMETER
111 | p_invoice_id - invoice id
112 | p_line_number - invoice line number
113 |
114 | NOTES
115 | 'Y' - Posted
116 | 'S' - Selected
117 | 'P' - Partial
118 | 'N' - Unposted
119 | ---------------------------------------------------------------------
120 | -- Declare cursor to establish the invoice-level posting flag
121 | --
122 | -- The first two selects simply look at the posting flags (cash and/or
123 | -- accrual) for the distributions. The rest is to cover one specific
124 | -- case when some of the distributions are fully posting (Y) and some
125 | -- are unposting (N). The status should be partial (P).
126 | --
127 | -- MOAC. Use ap_invoice_distributions_all table instead of SO view
128 | -- since this procedure is called when policy context is not set to
129 | -- the corresponding OU for the invoice_id
130 |
131 | MODIFICATION HISTORY
132 | Date Author Description of Change
133 | 28-MAY-04 yicao SLA Obsolescence: Remove some accounting
134 | related options
135 *============================================================================*/
136 FUNCTION get_posting_status(
137 p_invoice_id IN NUMBER,
138 p_line_number IN NUMBER )
139 RETURN VARCHAR2
140 IS
141
142 invoice_line_posting_flag VARCHAR2(1);
143 distribution_posting_flag VARCHAR2(1);
144 l_cash_basis_flag VARCHAR2(1);
145 l_org_id AP_SYSTEM_PARAMETERS_ALL.ORG_ID%TYPE;
146
147
148 CURSOR posting_cursor IS
149 SELECT cash_posted_flag
150 FROM ap_invoice_distributions_all
151 WHERE invoice_id = p_invoice_id
152 AND invoice_line_number = p_line_number
153 AND l_cash_basis_flag = 'Y'
154 UNION
155 SELECT accrual_posted_flag
156 FROM ap_invoice_distributions_all
157 WHERE invoice_id = p_invoice_id
158 AND invoice_line_number = p_line_number
159 AND l_cash_basis_flag <> 'Y'
160 UNION
161 SELECT 'P'
162 FROM ap_invoice_distributions_all
163 WHERE invoice_id = p_invoice_id
164 AND invoice_line_number = p_line_number
165 AND ( (cash_posted_flag = 'Y'
166 AND l_cash_basis_flag = 'Y')
167 OR
168 (accrual_posted_flag = 'Y'
169 AND l_cash_basis_flag <> 'Y'))
170 AND EXISTS
171 (SELECT 'An N is also in the valid flags'
172 FROM ap_invoice_distributions_all
173 WHERE invoice_id = p_invoice_id
174 AND invoice_line_number = p_line_number
175 AND ((cash_posted_flag = 'N'
176 AND l_cash_basis_flag = 'Y')
177 OR
178 (accrual_posted_flag = 'N'
179 AND l_cash_basis_flag <> 'Y')));
180
181 BEGIN
182
183 /*-----------------------------------------------------------------+
184 | Get Accounting Methods from gl_sets_of_books |
185 | l_cash_basis_flag: 'Y' --cash basis |
186 | 'N' --accrual basis |
187 | MOAC. Added org_id to select statement. |
188 +-----------------------------------------------------------------*/
189 BEGIN
190 SELECT nvl(sob.sla_ledger_cash_basis_flag, 'N'),
191 asp.org_id
192 INTO l_cash_basis_flag,
193 l_org_id
194 FROM ap_invoices_all ai,
195 ap_system_parameters_all asp,
196 gl_sets_of_books sob
197 WHERE ai.invoice_id = p_invoice_id
198 AND ai.org_id = asp.org_id
199 AND asp.set_of_books_id = sob.set_of_books_id;
200
201 EXCEPTION
202 WHEN OTHERS THEN
203 null;
204 END;
205 /*Added the above exception for Bug 14841063 */
206
207 invoice_line_posting_flag := 'X';
208
209 OPEN posting_cursor;
210
211 LOOP
212 FETCH posting_cursor INTO distribution_posting_flag;
213 EXIT WHEN posting_cursor%NOTFOUND;
214
215 IF (distribution_posting_flag = 'S') THEN
216 invoice_line_posting_flag := 'S';
217 ELSIF (distribution_posting_flag = 'P' AND
218 invoice_line_posting_flag <> 'S') THEN
219 invoice_line_posting_flag := 'P';
220 ELSIF (distribution_posting_flag = 'N' AND
221 invoice_line_posting_flag NOT IN ('S','P')) THEN
222 invoice_line_posting_flag := 'N';
223 ELSIF (invoice_line_posting_flag NOT IN ('S','P','N')) THEN
224 invoice_line_posting_flag := 'Y';
225 END IF;
226 END LOOP;
227 CLOSE posting_cursor;
228
229 if (invoice_line_posting_flag = 'X') then
230 invoice_line_posting_flag := 'N';
231 end if;
232
233 RETURN(invoice_line_posting_flag);
234
235
236 END get_posting_status;
237
238 /*============================================================================
239 | FUNCTION - get_approval_status
240 |
241 | DESCRIPTION
242 | returns the invoice line level approval status lookup code.
243 |
244 | PARAMETERS
245 | p_invoice_id - invoice id
246 | p_line_number - invoice line number
247 |
248 |
249 | NOTES
250 | Invoices Line -'APPROVED'
251 | 'NEEDS REAPPROVAL'
252 | 'NEVER APPROVED'
253 | 'CANCELLED'
254 |
255 | MODIFICATION HISTORY
256 | Date Author Description of Change
257 |
258 *============================================================================*/
259
260 FUNCTION get_approval_status(
261 p_invoice_id IN NUMBER,
262 p_line_number IN NUMBER)
263 RETURN VARCHAR2
264 IS
265
266 invoice_line_approval_status VARCHAR2(25);
267 invoice_line_approval_flag VARCHAR2(1);
268 distribution_approval_flag VARCHAR2(1);
269 encumbrance_flag VARCHAR2(1);
270 invoice_holds NUMBER;
271 sum_distributions NUMBER;
272 dist_var_hold NUMBER;
273 match_flag_cnt NUMBER;
274 l_cancelled_count NUMBER;
275 l_discarded_count NUMBER;
276 l_org_id FINANCIALS_SYSTEM_PARAMS_ALL.ORG_ID%TYPE;
277 ---------------------------------------------------------------------
278 -- Declare cursor to establish the invoice-level approval flag
279 --
280 -- The first select simply looks at the match status flag for the
281 -- distributions. The rest is to cover one specific case when some
282 -- of the distributions are tested (T or A) and some are untested
283 -- (NULL). The status should be needs reapproval (N).
284 --
285 CURSOR approval_cursor IS
286 SELECT nvl(match_status_flag, 'N')
287 FROM ap_invoice_distributions_all
288 WHERE invoice_id = p_invoice_id
289 AND invoice_line_number = p_line_number;
290
291 BEGIN
292
293 ---------------------------------------------------------------------
294 -- Get the encumbrance flag
295 -- MOAC. Included select from ap_invoices_all to get the org_id from
296 -- the invoice_id since it is unique
297
298 SELECT NVL(fsp.purch_encumbrance_flag,'N'), ai.org_id
299 INTO encumbrance_flag, l_org_id
300 FROM ap_invoices_all ai,
301 financials_system_params_all fsp
302 WHERE ai.invoice_id = p_invoice_id
303 AND ai.org_id = fsp.org_id;
304
305 ---------------------------------------------------------------------
306 -- Get the number of holds for the invoice
307 --
308 SELECT count(*)
309 INTO invoice_holds
310 FROM ap_holds_all
311 WHERE invoice_id = p_invoice_id
312 AND release_lookup_code is NULL;
313
314 ---------------------------------------------------------------------
315 -- Check if DIST VAR hold is placed on this invoice.
316 -- DIST VAR is a special case because it could be placed
317 -- when no distributions exist and in this case, the invoice
318 -- status should be NEEDS REAPPROVAL.
319 --
320 SELECT count(*)
321 INTO dist_var_hold
322 FROM ap_holds_all
323 WHERE invoice_id = p_invoice_id
324 AND hold_lookup_code = 'DIST VARIANCE'
325 AND release_lookup_code is NULL;
326
327 ---------------------------------------------------------------------
328 -- If invoice is cancelled, return 'CANCELLED'.
329 --
330 SELECT count(*)
331 INTO l_cancelled_count
332 FROM ap_invoice_lines
333 WHERE invoice_id = p_invoice_id
334 AND line_number = p_line_number
335 AND NVL(cancelled_flag, 'N' ) = 'Y';
336
337 IF ( l_cancelled_count > 0 ) THEN
338 RETURN('CANCELLED');
339 END IF;
340
341 ---------------------------------------------------------------------
342 -- Getting the count of distributions with
343 -- match_status_flag not null. We will open the approval_cursor
344 -- only if the count is more than 0.
345 --
346 SELECT count(*)
347 INTO match_flag_cnt
348 FROM ap_invoice_distributions_all aid
349 WHERE aid.invoice_id = p_invoice_id
350 AND aid.invoice_line_number = p_line_number
351 AND aid.match_status_flag IS NOT NULL
352 AND rownum < 2;
353
354 ---------------------------------------------------------------------
355 -- Establish the invoice line level approval flag
356 --
357 -- Use the following ordering sequence to determine the invoice-level
358 -- approval flag:
359 -- 'N' - Needs Reapproval
360 -- 'T' - Tested
361 -- 'A' - Approved
362 -- NULL - Never Approved
363 -- 'X' - No Distributions Exist
364 --
365 -- Initialize invoice line level approval flag
366 --
367 invoice_line_approval_flag := 'X';
368
369 IF match_flag_cnt > 0 THEN
370
371 OPEN approval_cursor;
372
373 LOOP
374 FETCH approval_cursor INTO distribution_approval_flag;
375 EXIT WHEN approval_cursor%NOTFOUND;
376
377 IF (distribution_approval_flag IS NULL) THEN
378 invoice_line_approval_flag := NULL;
379 ELSIF (distribution_approval_flag = 'N') THEN
380 invoice_line_approval_flag := 'N';
381 ELSIF (distribution_approval_flag = 'T' AND
382 (invoice_line_approval_flag <> 'N' or
383 invoice_line_approval_flag is null)) THEN
384 invoice_line_approval_flag := 'T';
385 ELSIF (distribution_approval_flag = 'A' AND
386 (invoice_line_approval_flag NOT IN ('N','T')
387 or invoice_line_approval_flag is null)) THEN
388 invoice_line_approval_flag := 'A';
389 END IF;
390
391 END LOOP;
392
393 CLOSE approval_cursor;
394 END IF; -- end of match_flag_cnt
395
396
397 ---------------------------------------------------------------------
398 -- Derive the translated approval status from the approval flag
399 --
400 IF (encumbrance_flag = 'Y') THEN
401
402 IF (invoice_line_approval_flag = 'A' AND invoice_holds = 0) THEN
403 invoice_line_approval_status := 'APPROVED';
404 ELSIF ((invoice_line_approval_flag in ('A') AND invoice_holds > 0)
405 OR (invoice_line_approval_flag IN ('T','N'))) THEN
406 invoice_line_approval_status := 'NEEDS REAPPROVAL';
407 ELSIF (dist_var_hold >= 1) THEN
408 --It's assumed here that the user won't place this hold
409 --manually before approving. If he does, status will be
410 --NEEDS REAPPROVAL. dist_var_hold can result when there
411 --are no distributions or there are but amounts don't
412 --match. It can also happen when an invoice is created with
413 --no distributions, then approve the invoice, then create the
414 --distribution. So, in this case, although the match flag
415 --is null, we still want to see the status as NEEDS REAPPR.
416 invoice_line_approval_status := 'NEEDS REAPPROVAL';
417 ELSIF (invoice_line_approval_flag is null
418 OR (invoice_line_approval_flag = 'X' AND dist_var_hold = 0 )) THEN
419 --Bug8414549: Undoing changes for bug8340784
420 --AND invoice_holds = 0)) THEN --Bug8340784
421 --Added invoice_holds = 0 to above condition
422 invoice_line_approval_status := 'NEVER APPROVED';
423 END IF;
424
425 ELSIF (encumbrance_flag = 'N') THEN
426 IF (invoice_line_approval_flag IN ('A','T') AND invoice_holds = 0) THEN
427 invoice_line_approval_status := 'APPROVED';
428 ELSIF ((invoice_line_approval_flag IN ('A','T') AND invoice_holds > 0)
429 OR
430 (invoice_line_approval_flag = 'N')) THEN
431 invoice_line_approval_status := 'NEEDS REAPPROVAL';
432 ELSIF (dist_var_hold >= 1) THEN
433 invoice_line_approval_status := 'NEEDS REAPPROVAL';
434 ELSIF (invoice_line_approval_flag is null
435 OR (invoice_line_approval_flag = 'X' AND dist_var_hold = 0
436 AND invoice_holds = 0)) THEN --Bug8340784
437 --Added invoice_holds = 0 to above condition
438 -- A NULL flag indicate that APPROVAL has not
439 -- been run for this invoice, therefore, even if manual
440 -- holds exist, status should be NEVER APPROVED.
441 invoice_line_approval_status := 'NEVER APPROVED';
442 END IF;
443 END IF;
444
445 RETURN(invoice_line_approval_status);
446 END get_approval_status;
447
448 /*=============================================================================
449 | Public PROCEDURE Is_Line_Discardable
450 |
451 | Check if the line is discardable
452 |
453 | PROGRAM FLOW
454 |
455 | 1. return FALSE - if discard flag is Y
456 | 2. return FALSE - if line contains distribution that does not have
457 | an OPEN reversal period name.
458 | 3. return FALSE - if line contain distributions which are PO/RCV
459 | matched whose reversal causes amount/qty billed less
460 | than 0
461 | 4. return FALSE - if line is final match
462 | 5. return FALSE - if line is referenced by an active correction
463 | 6. return FALSE - if line contains distributions witn invalid account
464 | 7. return FALSE - if line contains distributions refereced by active
465 | distributions which are not cancelled or reversed
466 | apply to FREIGHT/MISC allocated to Item Line
467 | 8. return FALSE - if line with outstanding allocation rule
468 | 9. return FALSE - if line is AWT line linked to AWT invoice
469 | 10. return FALSE - if prepayment line has been applied (same as Note 1)
470 | 14. return FALSE - if invoice is selected for payment
471 |
472 | NOTES
473 |
474 | 1. If line is the prepay application/unapplication - we handle the
475 | business rule on-line. Means from UI we will make sure that one
476 | PREPAY type line can not be discarded unless it is being fully
477 | unapplied.
478 |
479 | MODIFICATION HISTORY
480 | Date Author Description of Change
481 | 03/07/03 sfeng Created
482 |
483 *============================================================================*/
484
485 Function Is_Line_Discardable(
486 P_line_rec IN ap_invoice_lines%ROWTYPE,
487 P_error_code OUT NOCOPY VARCHAR2,
488 P_calling_sequence IN VARCHAR2) RETURN BOOLEAN
489
490 IS
491
492 l_po_dist_count NUMBER := 0;
493 l_rcv_dist_count NUMBER := 0; --Bug5000472
494 l_reference_count NUMBER := 0;
495 l_active_count NUMBER := 0;
496 l_quick_credit_count NUMBER := 0;
497 l_quick_credit_ref_count NUMBER := 0;
498 l_invalid_acct_count NUMBER := 0;
499 l_valid_alt_acct_exists NUMBER := 0; /* Added for bug#12555194 */
500 l_final_close_count NUMBER := 0;
501 l_pending_count NUMBER := 0;
502 l_count NUMBER := 0;
503
504 l_debug_info VARCHAR2(240);
505 l_curr_calling_sequence VARCHAR2(2000);
506
507 TYPE date_tab is TABLE OF DATE INDEX BY BINARY_INTEGER;
508 l_gl_date_list date_tab;
509 i BINARY_INTEGER := 1;
510 l_open_gl_date DATE :='';
511 l_open_period gl_period_statuses.period_name%TYPE := '';
512
513 l_prepay_amount_applied NUMBER := 0;
514 l_enc_enabled VARCHAR2(1); --bug6009101
515 l_po_not_approved VARCHAR2(1); --bug6009101
516 l_org_id ap_invoices_all.org_id%type; -- for bug 5936290
517 CURSOR dist_gl_date_Cur IS
518 SELECT accounting_date
519 FROM ap_invoice_distributions AID
520 WHERE AID.invoice_id = p_line_rec.invoice_id
521 AND AID.invoice_line_number = p_line_rec.line_number
522 AND NVL(AID.reversal_flag, 'N') <> 'Y';
523
524
525 BEGIN
526
527 l_curr_calling_sequence := 'AP_INVOICE_LINE_PKG.IS_Line_Discardable<-' ||
528 P_calling_sequence;
529
530 /*-----------------------------------------------------------------+
531 | Step 0 - If line is discarded, return FALSE |
532 +-----------------------------------------------------------------*/
533
534 l_debug_info := 'Check if line is already discarded';
535
536 IF ( NVL(p_line_rec.discarded_flag, 'N') = 'Y' ) THEN
537 p_error_code := 'AP_INV_LINE_ALREADY_DISCARDED';
538 RETURN FALSE;
539 END IF;
540
541 /*-----------------------------------------------------------------+
542 | Step 1 - If line is CANCELLED, can not be discarded, return |
543 | FALSE |
544 +-----------------------------------------------------------------*/
545
546 l_debug_info := 'Check if line is already cancelled';
547
548 IF ( NVL(p_line_rec.cancelled_flag, 'N') = 'Y' ) THEN
549 p_error_code := 'AP_INV_CANCELLED';
550 RETURN FALSE;
551 END IF;
552
553 /*-----------------------------------------------------------------+
554 | Step 2 - If line contains distribution which has no open |
555 | period, can not be discarded, return FALSE |
556 +-----------------------------------------------------------------*/
557
558 l_debug_info := 'Check if distribution in this line has open period';
559
560 OPEN dist_gl_date_Cur;
561 FETCH dist_gl_date_Cur
562 BULK COLLECT INTO l_gl_date_list;
563 CLOSE dist_gl_date_Cur;
564
565 -- For bug 5936290
566 -- we call ap_utilities_pkg.get_current_gl_date
567 -- and in ap_utilities_pkg.get_open_gl_date for getting the gl date and
568 -- period below.For both these procedures one parameter is org_id
569 -- and it's default value is mo_global.get_current_org_id.we do
570 -- were not passing the org_id in these procedures calls so
571 -- the org_id was getting picked up from mo_global.get_current_org_id
572 -- and it's coming null when the Invoice batch option is ON.
573 -- So now we are passing the org_id also in these two calls.
574
575 SELECT org_id
576 INTO l_org_id
577 FROM ap_invoices_all
578 WHERE invoice_id = p_line_rec.invoice_id;
579
580 FOR i in NVL(l_gl_date_list.FIRST,0)..NVL(l_gl_date_list.LAST,-1)
581 LOOP
582 l_open_period := ap_utilities_pkg.get_current_gl_date(l_gl_date_list(i),l_org_id); --added for bug 5936290
583
584 IF ( l_open_period IS NULL ) THEN
585 ap_utilities_pkg.get_open_gl_date(
586 l_gl_date_list(i),
587 l_open_period,
588 l_open_gl_date,
589 l_org_id); --added for bug 5936290
590 IF ( l_open_period IS NULL ) THEN
591 p_error_code := 'AP_DISCARD_NO_FUTURE_PERIODS';
592 RETURN FALSE;
593 END IF;
594 END IF;
595 END LOOP;
596
597 --Bug9871858 : Moved Step 3 and 3.1 after step 7.
598 /*-----------------------------------------------------------------+
599 | Step 4. If invoice is matched to a Finally Closed PO, return |
600 | FALSE |
601 +-----------------------------------------------------------------*/
602
603 l_debug_info := 'Check if invoice line is matched to a finally'
604 ||' closed PO shipment';
605
606 SELECT count(*)
607 INTO l_final_close_count
608 FROM ap_invoice_lines AIL,
609 po_line_locations PLL
610 WHERE AIL.invoice_id = p_line_rec.invoice_id
611 AND AIL.line_number = p_line_rec.line_number
612 AND AIL.po_line_location_id = PLL.line_location_id
613 AND PLL.closed_code = 'FINALLY CLOSED';
614
615 IF (l_final_close_count > 0) THEN
616 P_error_code := 'AP_INV_LINE_PO_FINALLY_CLOSED';
617 RETURN FALSE;
618 END IF;
619
620 /*-----------------------------------------------------------------+
621 | Step 4.1 If the encumbrance is on and the invoice is matched to
622 | to an unapproved PO, then do not allow discard.(bug6009101)
623 +-----------------------------------------------------------------*/
624
625 SELECT NVL(purch_encumbrance_flag,'N')
626 INTO l_enc_enabled
627 FROM financials_system_params_all FSP,
628 ap_invoices_all AI
629 WHERE AI.invoice_id = p_line_rec.invoice_id
630 AND FSP.org_id = AI.org_id;
631
632 if l_enc_enabled = 'Y' then
633
634 begin
635
636 select 'Y'
637 into l_po_not_approved
638 from po_headers POH
639 where POH.po_header_id = p_line_rec.po_header_id
640 and POH.approved_flag <> 'Y'; --bug6653070
641
642 EXCEPTION
643 WHEN OTHERS THEN
644 NULL;
645
646 end;
647
648 if l_po_not_approved = 'Y' then
649 p_error_code := 'AP_PO_UNRES_CANT_DISC_LINE';
650 return FALSE;
651 end if;
652 end if;
653
654
655 /*-----------------------------------------------------------------+
656 | Step 5. If invoice is a quick credit, it can be cancelled at |
657 | at header level. can not discard individual line. so |
658 | return FALSE; |
659 +-----------------------------------------------------------------*/
660 l_debug_info := 'Check if this invoice is a quick credit';
661
662 SELECT count(*)
663 INTO l_quick_credit_count
664 FROM ap_invoices AI
665 WHERE AI.invoice_id = p_line_rec.invoice_id
666 AND NVL(AI.quick_credit, 'N') = 'Y';
667
668 IF ( l_quick_credit_count > 0 ) THEN
669 P_error_code := 'AP_INV_IS_QUICK_CREDIT';
670 RETURN FALSE;
671 END IF;
672
673 /*-----------------------------------------------------------------+
674 | Step 6. Check If invoice line is actively referenced |
675 | If invoice line reference by an active |
676 | correction, return FALSE |
677 +-----------------------------------------------------------------*/
678 l_debug_info := 'Check if this line is refrenced by a correction';
679
680 SELECT count(*)
681 INTO l_active_count
682 FROM ap_invoice_lines AIL
683 WHERE NVL( AIL.discarded_flag, 'N' ) <> 'Y'
684 AND NVL( AIL.cancelled_flag, 'N' ) <> 'Y'
685 AND AIL.corrected_inv_id = p_line_rec.invoice_id
686 AND AIL.corrected_line_number = p_line_rec.line_number;
687
688 IF ( l_active_count > 0) THEN
689 P_error_code := 'AP_INV_LINE_REF_BY_CORRECTION';
690 RETURN FALSE;
691 END IF;
692
693 /*-----------------------------------------------------------------+
694 | Step 7. Check If invoice line is actively referenced |
695 | If one active quick credit is referencing this |
696 | invoice, return FALSE |
697 +-----------------------------------------------------------------*/
698 l_debug_info := 'Check if this line is a refreced by a quick credit';
699
700 -- Bug 5261908. Added rownum condition to improve performance
701 BEGIN
702 --bug 5475668 Added the if condition.
703 --bug 8208823 Added condition for line_type_lookup_code
704 if (p_line_rec.invoice_id is not NULL
705 AND p_line_rec.line_type_lookup_code <> 'PREPAY') then
706 SELECT 1
707 INTO l_quick_credit_ref_count
708 FROM ap_invoices AI
709 WHERE AI.credited_invoice_id = p_line_rec.invoice_id
710 AND NVL(AI.quick_credit, 'N') = 'Y'
711 AND AI.cancelled_date is null
712 AND Rownum = 1;
713 end if;
714 EXCEPTION
715 WHEN no_data_found THEN
716 NULL;
717 END;
718
719 IF (l_quick_credit_ref_count > 0 ) THEN
720 P_error_code := 'AP_INV_LINE_REF_BY_QCK_CREDIT';
721 RETURN FALSE;
722 END IF;
723
724 --Bug9871858: Moved Step 3 and 3.1 after step 7
725 /*-----------------------------------------------------------------+
726 | Step 3. if the quantity billed and amount on PO would be |
727 | reduced to less than zero then return FALSE |
728 | Always allow Reversal distributions to be cancelled |
729 +-----------------------------------------------------------------*/
730
731 l_debug_info := 'Check if quantity_billed on po_distribution is '
732 || 'would be reduced to < 0';
733 --Bug5000472 added condition on po distribution id and rcv_transaction_id
734 --and commented GROUP BY in sub queries
735
736 -- Modified the below select statment for the bug #6913924 to consider the
737 -- case when prepayment invoice matched to a PO and receipt and with
738 --different UOM for PO and receipt.
739
740 BEGIN
741 SELECT count(*)
742 INTO l_po_dist_count
743 FROM po_distributions_all POD,
744 ap_invoice_distributions AID,
745 ap_invoices ai,
746 po_line_locations PLL,
747 po_lines PL
748 WHERE POD.po_distribution_id = AID.po_distribution_id
749 AND POD.line_location_id = PLL.line_location_id
750 AND PLL.po_line_id = PL.po_line_id
751 AND AID.invoice_id = ai.invoice_id
752 AND AID.invoice_id = p_line_rec.invoice_id
753 AND POD.org_id = AID.org_id
754 AND AID.invoice_line_number = p_line_rec.line_number
755 AND NVL(AID.reversal_flag,'N')<>'Y'
756 AND aid.rcv_transaction_id is null --Bug5000472
757 HAVING (
758 (DECODE(ai.invoice_type_lookup_code,'PREPAYMENT',
759 SUM(distinct NVL(POD.quantity_financed, 0)), --bug15935245, added distinct keyword
760 SUM(distinct NVL(POD.quantity_billed, 0))) --bug15935245, added distinct keyword
761 -
762 SUM(round(decode(AID.dist_match_type,
763 'PRICE_CORRECTION', 0,
764 'AMOUNT_CORRECTION', 0,
765 'ITEM_TO_SERVICE_PO', 0,
766 'ITEM_TO_SERVICE_RECEIPT', 0,
767 nvl( AID.quantity_invoiced, 0 ) +
768 nvl( AID.corrected_quantity,0 )
769 ) *
770 po_uom_s.po_uom_convert(AID.matched_uom_lookup_code,
771 nvl(PLL.unit_meas_lookup_code,
772 PL.unit_meas_lookup_code),
773 PL.item_id), 15))
774 < 0)
775 OR (DECODE(ai.invoice_type_lookup_code,'PREPAYMENT',
776 SUM(distinct NVL(POD.amount_financed, 0)), --bug15935245, added distinct keyword
777 SUM(distinct NVL(POD.amount_billed, 0))) - --bug15935245, added distinct keyword
778 SUM(NVL(AID.amount, 0)) < 0 ))
779 GROUP BY ai.invoice_type_lookup_code,AID.po_distribution_id;
780
781 EXCEPTION
782 WHEN NO_DATA_FOUND THEN
783 l_po_dist_count := 0;
784 END;
785 -- end of changes for bug #6913924
786
787 IF (l_po_dist_count > 0 ) THEN
788 P_error_code := 'AP_INV_LINE_QTY_BILLED_NOT_NEG';
789 RETURN FALSE;
790 END IF;
791
792 --Bug5000472 Added the following block of code
793 /*-----------------------------------------------------------------+
794 | Step 3.1. if the quantity billed and amount on RCV would be |
795 | reduced to less than zero then return FALSE |
796 | Always allow Reversal distributions to be cancelled |
797 +-----------------------------------------------------------------*/
798
799 l_debug_info := 'Check if quantity_billed on rcv_transactions '
800 || 'would be reduced to < 0';
801
802 SELECT count(*)
803 INTO l_rcv_dist_count
804 FROM rcv_transactions RT,
805 ap_invoice_distributions_all AID
806 WHERE RT.transaction_id = AID.rcv_transaction_id
807 AND AID.invoice_id = p_line_rec.invoice_id
808 AND AID.invoice_line_number = p_line_rec.line_number
809 AND AID.rcv_transaction_id is not null
810 AND NVL(AID.reversal_flag,'N')<>'Y'
811 AND (NVL(rt.quantity_billed,0) <
812 (SELECT SUM(decode( AID1.dist_match_type,
813 'PRICE_CORRECTION', 0,
814 'AMOUNT_CORRECTION', 0,
815 'ITEM_TO_SERVICE_PO', 0,
816 'ITEM_TO_SERVICE_RECEIPT', 0,
817 nvl( AID1.corrected_quantity,0 ) +
818 nvl( AID1.quantity_invoiced,0 )
819 )
820 )
821 FROM ap_invoice_distributions_all aid1
822 WHERE aid1.invoice_id = aid.invoice_id
823 AND aid1.invoice_line_number = aid.invoice_line_number
824 AND aid1.rcv_transaction_id=aid.rcv_transaction_id
825 )
826 OR
827 NVL(rt.amount_billed,0) < (
828 SELECT SUM(DECODE(AID2.dist_match_type, --Bug11783854
829 'OTHER_TO_RECEIPT',0,NVL(AID2.amount,0)))
830 FROM ap_invoice_distributions_all aid2
831 WHERE aid2.invoice_id = aid.invoice_id
832 AND aid2.invoice_line_number = aid.invoice_line_number
833 AND aid2.rcv_transaction_id=aid.rcv_transaction_id
834 )
835 );
836
837 IF (l_rcv_dist_count > 0 ) THEN
838 P_error_code := 'AP_INV_LINE_QTY_BILLED_NOT_NEG';
839 RETURN FALSE;
840 END IF;
841 --Bug5000472 End
842 --End of bug9871858
843
844 /*-----------------------------------------------------------------+
845 | Step 8. If line contain distributions which has invalid account |
846 | return FALSE |
847 +-----------------------------------------------------------------*/
848
849 SELECT count(*)
850 INTO l_invalid_acct_count
851 FROM ap_invoice_distributions D
852 WHERE D.invoice_id = p_line_rec.invoice_id
853 AND D.invoice_line_number = p_line_rec.line_number
854 AND D.posted_flag IN ('N', 'P')
855 AND ((EXISTS (select 'x'
856 from gl_code_combinations C
857 where D.dist_code_combination_id = C.code_combination_id (+)
858 and (C.code_combination_id is null
859 or C.detail_posting_allowed_flag = 'N'
860 or C.start_date_active > D.accounting_date
861 or C.end_date_active < D.accounting_date
862 or C.template_id is not null
863 or C.enabled_flag <> 'Y'
864 or C.summary_flag <> 'N'
865 )))
866 OR (D.dist_code_combination_id = -1));
867
868 IF (l_invalid_acct_count <> 0) THEN
869
870 /* Added for bug#12555194 Start */
871 l_valid_alt_acct_exists := 0;
872
873 SELECT count(1)
874 INTO l_valid_alt_acct_exists
875 FROM ap_invoice_distributions aid
876 , gl_code_combinations glcc
877 WHERE aid.invoice_id = p_line_rec.invoice_id
878 AND aid.invoice_line_number = p_line_rec.line_number
879 AND aid.posted_flag IN ('N', 'P')
880 AND dist_code_combination_id = glcc.code_combination_id
881 AND glcc.alternate_code_combination_id IS NOT NULL
882 AND EXISTS
883 (
884 SELECT 'Account Valid'
885 FROM gl_code_combinations glcc1
886 WHERE glcc1.code_combination_id = glcc.alternate_code_combination_id
887 AND glcc1.enabled_flag = 'Y'
888 AND glcc1.detail_posting_allowed_flag = 'Y'
889 AND aid.accounting_date BETWEEN
890 NVL(glcc1.start_date_active, aid.accounting_date)
891 AND NVL(glcc1.end_date_active, aid.accounting_date)
892 );
893 IF l_valid_alt_acct_exists = 0 THEN
894 /* Added for bug#12555194 End */
895
896 P_error_code := 'AP_INV_LINE_INVALID_DIST_ACCT';
897 RETURN FALSE;
898 END IF; /* Added for bug#12555194 */
899 END IF;
900
901 /*-----------------------------------------------------------------+
902 | Step 9. If line contain distributions referenced by active |
903 | distributions, return FALSE. This applies to all the |
904 | non-charge lines which have active charges lines |
905 | allocated to themselves. In case that a charge |
906 | distribution's parent line is not a charge line but |
907 | and ITEM/ACCRUAL line, we should allow line to be |
908 | discarded |
909 +-----------------------------------------------------------------*/
910 -- Bug 5114543
911 -- Commented the following check to allow discard of item line
912 -- when it has allocated charges.
913 -- Bug 5386077. Recommenting again. Was checked incorrectly via bug 5000472 (120.20)
914
915 /*
916 SELECT count(*)
917 INTO l_reference_count
918 FROM ap_invoice_distributions AID
919 WHERE NVL(AID.cancellation_flag, 'N') <> 'Y'
920 AND NVL(AID.reversal_flag, 'N') <> 'Y'
921 AND AID.invoice_id = p_line_rec.invoice_id
922 AND AID.invoice_line_number <> p_line_rec.line_number
923 AND AID.charge_applicable_to_dist_id IS NOT NULL
924 AND AID.charge_applicable_to_dist_id IN
925 ( SELECT AID2.invoice_distribution_id
926 FROM ap_invoice_distributions AID2
927 WHERE AID2.invoice_id = p_line_rec.invoice_id
928 AND AID2.invoice_line_number = p_line_rec.line_number
929 AND NVL(AID2.cancellation_flag, 'N') <> 'Y'
930 AND NVL(AID2.reversal_flag, 'N') <> 'Y' );
931
932 IF ( l_reference_count <> 0) THEN
933 P_error_code := 'AP_INV_LINE_ACTIVE_DIST';
934 RETURN FALSE;
935 END IF;
936 */
937 /*------------------------------------------------------------------+
938 | Step 10. If this non-charge line contain active allocation rule |
939 | which is not yet applied, return FALSE |
940 +------------------------------------------------------------------*/
941 -- Bug 5114543
942 -- Commented the following check to allow discard of item line
943 -- when it has allocated charges.
944 -- Bug 5386077. Recommenting again. Was checked incorrectly via bug 5000472 (120.20).
945 /*
946 SELECT count(*)
947 INTO l_pending_count
948 FROM ap_allocation_rules AR,
949 ap_allocation_rule_lines ARL
950 WHERE AR.invoice_id = p_line_rec.invoice_id
951 AND AR.invoice_id = ARL.invoice_id
952 AND AR.chrg_invoice_line_number = ARL.chrg_invoice_line_number
953 AND ARL.to_invoice_line_number = p_line_rec.line_number
954 AND AR.status = 'PENDING';
955
956 IF ( l_pending_count <> 0) THEN
957 P_error_code := 'AP_INV_LINE_HAS_ALLOC_RULE';
958 RETURN FALSE;
959 END IF;
960 */
961 /*-----------------------------------------------------------------+
962 | Step 11. If line is AWT line which invoice is fully or |
963 | partially paid, return FALSE |
964 +-----------------------------------------------------------------*/
965
966 SELECT count(*)
967 INTO l_count
968 FROM ap_invoice_lines AIL,
969 ap_invoices AI
970 WHERE AIL.invoice_id = P_line_rec.invoice_id
971 AND AIL.line_number = P_line_rec.line_number
972 AND AIL.line_type_lookup_code = 'AWT'
973 /*bug12865213, commented exists clause
974 AND NOT EXISTS ( SELECT invoice_distribution_id
975 FROM ap_invoice_distributions aid
976 WHERE aid.invoice_id = AIL.invoice_id
977 AND aid.invoice_line_number = AIL.line_number
978 AND awt_flag = 'M' )
979 */
980 AND AI.invoice_id = AIL.invoice_id
981 AND AI.payment_status_flag in ('P', 'Y');
982
983 IF ( l_count <> 0) THEN
984 P_error_code := 'AP_INV_LINE_IS_AWT';
985 RETURN FALSE;
986 END IF;
987
988 /*--------------------------------------------------------------------+
989 | Step 12. If line has some or entire retained amount
990 | released, return FALSE
991 +--------------------------------------------------------------------*/
992
993 SELECT count(*)
994 INTO l_count
995 FROM ap_invoice_lines AIL
996 WHERE AIL.invoice_id = P_line_rec.invoice_id
997 AND AIL.line_number = P_line_rec.line_number
998 AND (ail.retained_amount IS NOT NULL AND
999 ail.retained_amount_remaining IS NOT NULL AND
1000 abs(ail.retained_amount) <> abs(ail.retained_amount_remaining));
1001
1002 IF ( l_count <> 0) THEN
1003 P_error_code := 'AP_INV_LINE_RELEASED';
1004 RETURN FALSE;
1005 END IF;
1006
1007 /*-----------------------------------------------------------------+
1008 | Step 13. Prepayment line cannot be discarded after prepayment |
1009 | is applied. If so, return FALSE (Bug #5114854) |
1010 +-----------------------------------------------------------------*/
1011 SELECT count(*)
1012 INTO l_count
1013 FROM ap_invoices_all ai
1014 WHERE invoice_id = p_line_rec.invoice_id
1015 AND invoice_type_lookup_code = 'PREPAYMENT';
1016
1017 IF ( l_count > 0 ) THEN
1018
1019 l_prepay_amount_applied :=
1020 ap_invoices_pkg.get_prepay_amount_applied(p_line_rec.invoice_id);
1021
1022 if (l_prepay_amount_applied <> 0) then
1023 p_error_code := 'AP_INV_DEL_APPLIED_PREPAY';
1024 RETURN FALSE;
1025 end if;
1026 END IF;
1027 /*-----------------------------------------------------------------+
1028 | Step 14. invoice is select for payment and payment is not done |
1029 | so, return FALSE (Bug #8366177) |
1030 +-----------------------------------------------------------------*/
1031 --bug 10012646
1032 select nvl(count(1),0)
1033 INTO l_count
1034 from ap_payment_schedules_all
1035 where invoice_id =p_line_rec.invoice_id
1036 and checkrun_id is not null
1037 and payment_status_flag <>'Y';
1038
1039 IF ( l_count > 0 ) THEN
1040 p_error_code := 'AP_INV_SELECTED_INVOICE';
1041 RETURN FALSE;
1042 end if;
1043
1044 P_error_code := null;
1045 RETURN TRUE;
1046
1047 EXCEPTION
1048 WHEN OTHERS THEN
1049 IF (SQLCODE <> -20001) THEN
1050 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1051 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1052 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1053 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1054 ' P_invoice_id = ' || p_line_rec.invoice_id
1055 ||' P_line_number = ' || p_line_rec.line_number );
1056 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1057 END IF;
1058
1059 IF ( dist_gl_date_Cur%ISOPEN ) THEN
1060 CLOSE dist_gl_date_Cur;
1061 END IF;
1062
1063 APP_EXCEPTION.RAISE_EXCEPTION;
1064
1065 END Is_Line_Discardable;
1066
1067 /*=============================================================================
1068 | Public FUNCTION Allocation_Exists
1069 |
1070 | Check if the line has allocation rules and lines associated with it.
1071 |
1072 | PROGRAM FLOW
1073 |
1074 | return TRUE - if allocation rules and lines exist
1075 | return FALSE - otherwise.
1076 |
1077 | MODIFICATION HISTORY
1078 | Date Author Description of Change
1079 | 03/10/13 bghose Created
1080 *============================================================================*/
1081
1082 FUNCTION Allocation_Exists (p_Invoice_Id Number,
1083 p_Line_Number Number,
1084 p_Calling_Sequence Varchar2) Return Boolean Is
1085 dummy number := 0;
1086 current_calling_sequence Varchar2(2000);
1087 debug_info Varchar2(100);
1088
1089 Begin
1090 -- Update the calling sequence
1091 --
1092 current_calling_sequence :=
1093 'AP_INVOICE_LINES_UTILITY_PKG.ALLOCATION_EXISTS<-'||p_Calling_Sequence;
1094
1095 debug_info := 'Select from ap_allocation_rules';
1096
1097 Select count(*)
1098 Into dummy
1099 From ap_allocation_rules AR,
1100 ap_allocation_rule_lines ARL
1101 Where AR.invoice_id = p_Invoice_Id
1102 And AR.invoice_id = ARL.invoice_id
1103 And AR.chrg_invoice_line_number = ARL.chrg_invoice_line_number
1104 And ARL.to_invoice_line_number = p_line_number;
1105
1106 If (dummy >= 1) Then
1107 return TRUE;
1108 End If;
1109
1110 return FALSE;
1111
1112 Exception
1113 WHEN OTHERS THEN
1114 If (SQLCODE <> -20001) Then
1115 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1116 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1117 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1118 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice Id = '||p_Invoice_id
1119 ||', line number = '|| p_Line_Number);
1120 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1121 End If;
1122 APP_EXCEPTION.RAISE_EXCEPTION;
1123 End Allocation_Exists;
1124
1125 /*=============================================================================
1126 | Public FUNCTION Inv_Reversed_Via_Qc
1127 |
1128 | Check if the invoice has been reversed via Qucik Credit.
1129 |
1130 | PROGRAM FLOW
1131 |
1132 | return TRUE - if reversed via Quick Credit
1133 | return FALSE - otherwise.
1134 |
1135 | MODIFICATION HISTORY
1136 | Date Author Description of Change
1137 | 03/10/13 bghose Created
1138 *=============================================================================*/
1139
1140 Function Inv_Reversed_Via_Qc (p_Invoice_Id Number,
1141 p_Calling_Sequence Varchar2) Return Boolean Is
1142 dummy number := 0;
1143 current_calling_sequence Varchar2(2000);
1144 debug_info Varchar2(100);
1145
1146 Begin
1147 -- Update the calling sequence
1148 --
1149 current_calling_sequence :=
1150 'AP_INVOICE_LINES_UTILITY_PKG.Inv_Reverse_Via_Qc<-'||p_Calling_Sequence;
1151
1152 debug_info := 'Select from ap_invoics_all';
1153
1154 -- Bug 5261908. Added rownum condition to improve performance
1155 BEGIN
1156 --bug 5475668 Added the if condition
1157 if (p_invoice_id is not null) then
1158 Select 1
1159 Into dummy
1160 From ap_invoices_all AI
1161 Where AI.credited_invoice_id = p_Invoice_Id
1162 AND NVL(AI.quick_credit, 'N') = 'Y'
1163 AND AI.cancelled_date is null
1164 AND Rownum = 1;
1165 end if;
1166 EXCEPTION
1167 WHEN no_data_found THEN
1168 dummy := 0;
1169 END;
1170
1171 If (dummy >= 1) Then
1172 return TRUE;
1173 End if;
1174
1175 return FALSE;
1176
1177 Exception
1178 WHEN OTHERS THEN
1179 If (SQLCODE <> -20001) Then
1180 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1181 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1182 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1183 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice Id = '||p_Invoice_id);
1184 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1185 End If;
1186 APP_EXCEPTION.RAISE_EXCEPTION;
1187 End Inv_Reversed_Via_Qc;
1188
1189 /*=============================================================================
1190 | Public FUNCTION Is_Line_Dists_Trans_FA
1191 |
1192 | Check if the line has associated distributions which has transfered to
1193 | FA.
1194 |
1195 | PROGRAM FLOW
1196 |
1197 | return TRUE - ifdistributions transferred to FA
1198 | return FALSE - otherwise.
1199 |
1200 | MODIFICATION HISTORY
1201 | Date Author Description of Change
1202 | 03/10/13 bghose Created
1203 *============================================================================*/
1204 FUNCTION Is_Line_Dists_Trans_FA (p_Invoice_Id Number,
1205 p_Line_Number Number,
1206 p_Calling_Sequence Varchar2) Return Boolean Is
1207 dummy number := 0;
1208 current_calling_sequence Varchar2(2000);
1209 debug_info Varchar2(100);
1210
1211 Begin
1212 -- Update the calling sequence
1213 --
1214 current_calling_sequence :=
1215 'AP_INVOICE_LINES_UTILITY_PKG.IS_LINE_DISTS_TRANS_FA<-'
1216 ||p_Calling_Sequence;
1217
1218 debug_info := 'Select from ap_invoice_distributions_all';
1219
1220 Select count(*)
1221 Into dummy
1222 From ap_invoice_distributions_all
1223 Where invoice_id = p_Invoice_Id
1224 And invoice_line_number = p_Line_Number
1225 And assets_addition_flag = 'Y';
1226
1227 If (dummy >= 1) Then
1228 return TRUE;
1229 End if;
1230
1231 return FALSE;
1232
1233 Exception
1234 WHEN OTHERS THEN
1235 If (SQLCODE <> -20001) Then
1236 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1237 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1238 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1239 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice Id = '||p_Invoice_id
1240 ||', line number = '|| p_Line_Number);
1241 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1242 End If;
1243 APP_EXCEPTION.RAISE_EXCEPTION;
1244 End Is_Line_Dists_Trans_FA;
1245
1246 /*=============================================================================
1247 | Public FUNCTION Line_Dists_Acct_Event_Created
1248 |
1249 | Check if the line has associated distributions accounting event created
1250 |
1251 | PROGRAM FLOW
1252 |
1253 | return TRUE - if distributions accounting event created
1254 | return FALSE - otherwise.
1255 |
1256 | MODIFICATION HISTORY
1257 | Date Author Description of Change
1258 | 03/10/13 bghose Created
1259 *============================================================================*/
1260
1261 FUNCTION Line_Dists_Acct_Event_Created (p_Invoice_Id Number,
1262 p_Line_Number Number,
1263 p_Calling_Sequence Varchar2) Return Boolean Is
1264 dummy number := 0;
1265 current_calling_sequence Varchar2(2000);
1266 debug_info Varchar2(100);
1267
1268 Begin
1269 -- Update the calling sequence
1270 --
1271 current_calling_sequence :=
1272 'AP_INVOICE_LINES_UTILITY_PKG.LINE_DISTS_ACCT_EVENT_CREATED<-'
1273 ||p_Calling_Sequence;
1274
1275 debug_info := 'Select from ap_invoice_distributions_all';
1276
1277 Select count(*)
1278 Into dummy
1279 From ap_invoice_distributions_all
1280 Where invoice_id = p_Invoice_Id
1281 And invoice_line_number = p_Line_Number
1282 And accounting_event_id Is Not Null;
1283
1284 If (dummy >= 1) Then
1285 return TRUE;
1286 End if;
1287
1288 return FALSE;
1289
1290 Exception
1291 WHEN OTHERS THEN
1292 If (SQLCODE <> -20001) Then
1293 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1294 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1295 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1296 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice Id = '||p_Invoice_id
1297 ||', line number = '|| p_Line_Number);
1298 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1299 End If;
1300 APP_EXCEPTION.RAISE_EXCEPTION;
1301 End Line_Dists_Acct_Event_Created;
1302
1303 /*=============================================================================
1304 | Public FUNCTION Line_Referred_By_Corr
1305 |
1306 | Check if the line has been referred by any correction
1307 |
1308 | PROGRAM FLOW
1309 |
1310 | return TRUE - if line has been referred by any correction
1311 | return FALSE - otherwise.
1312 |
1313 | MODIFICATION HISTORY
1314 | Date Author Description of Change
1315 | 03/10/13 bghose Created
1316 *============================================================================*/
1317
1318 FUNCTION Line_Referred_By_Corr (p_Invoice_Id Number,
1319 p_Line_Number Number,
1320 p_Calling_Sequence Varchar2) Return Boolean Is
1321 dummy number := 0;
1322 current_calling_sequence Varchar2(2000);
1323 debug_info Varchar2(100);
1324
1325 Begin
1326 -- Update the calling sequence
1327 --
1328 current_calling_sequence :=
1329 'AP_INVOICE_LINES_UTILITY_PKG.LINE_REFERRED_BY_CORR<-'
1330 ||p_Calling_Sequence;
1331
1332 debug_info := 'Select from ap_invoice_lines_all';
1333
1334 Select count(*)
1335 Into dummy
1336 From ap_invoice_lines_all AIL
1337 Where NVL(AIL.discarded_flag, 'N' ) <> 'Y'
1338 And NVL( AIL.cancelled_flag, 'N' ) <> 'Y'
1339 And AIL.corrected_inv_id = p_Invoice_Id
1340 And AIL.corrected_line_number = p_Line_Number;
1341
1342 If (dummy >= 1) Then
1343 return TRUE;
1344 End if;
1345
1346 return FALSE;
1347
1348 Exception
1349 WHEN OTHERS THEN
1350 If (SQLCODE <> -20001) Then
1351 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1352 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1353 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1354 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice Id = '||p_Invoice_id
1355 ||', line number = '|| p_Line_Number);
1356 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1357 End If;
1358 APP_EXCEPTION.RAISE_EXCEPTION;
1359 End Line_Referred_By_Corr;
1360
1361 /*=============================================================================
1362 | Public FUNCTION Line_Dists_Referred_By_Other
1363 |
1364 | Check if the particular invoice line contains distributions referenced
1365 | by active distributions
1366 |
1367 | PROGRAM FLOW
1368 |
1369 | return TRUE - if line has been referenced by active distributions
1370 | return FALSE - otherwise.
1371 |
1372 | MODIFICATION HISTORY
1373 | Date Author Description of Change
1374 | 03/10/13 bghose Created
1375 *============================================================================*/
1376
1377 FUNCTION Line_Dists_Referred_By_Other(p_Invoice_Id Number,
1378 p_Line_Number Number,
1379 p_Calling_Sequence Varchar2) Return Boolean Is
1380 dummy number := 0;
1381 current_calling_sequence Varchar2(2000);
1382 debug_info Varchar2(100);
1383
1384 Begin
1385 -- Update the calling sequence
1386 --
1387 current_calling_sequence :=
1388 'AP_INVOICE_LINES_UTILITY_PKG.Line_Dists_Referred_By_Other <-'||
1389 p_Calling_Sequence;
1390 debug_info := 'Select from ap_invoic_distributions_all';
1391
1392 Select count(*)
1393 Into dummy
1394 From ap_invoice_distributions_all AID
1395 Where NVL(AID.cancellation_flag, 'N') <> 'Y'
1396 And NVL(AID.reversal_flag, 'N') <> 'Y'
1397 And AID.invoice_id = p_invoice_id
1398 --Bug9323585 : Commented line to check for inclusive tax also
1399 --And AID.invoice_line_number <> p_line_number
1400 And AID.charge_applicable_to_dist_id IS NOT NULL
1401 And AID.charge_applicable_to_dist_id In
1402 (Select AID2.invoice_distribution_id
1403 From ap_invoice_distributions_all AID2
1404 Where AID2.invoice_id = p_Invoice_Id
1405 And AID2.invoice_line_number = p_Line_Number
1406 And NVL(AID2.cancellation_flag, 'N') <> 'Y'
1407 And NVL(AID2.reversal_flag, 'N') <> 'Y' );
1408
1409 If (dummy >= 1) Then
1410 return TRUE;
1411 End if;
1412
1413 return FALSE;
1414
1415 Exception
1416 WHEN OTHERS THEN
1417 If (SQLCODE <> -20001) Then
1418 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1419 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1420 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1421 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice Id = '||p_Invoice_id
1422 ||', line number = '|| p_Line_Number);
1423 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1424 End If;
1425 APP_EXCEPTION.RAISE_EXCEPTION;
1426 End Line_Dists_Referred_By_Other;
1427
1428 /*=============================================================================
1429 | Public FUNCTION Outstanding_Alloc_Exists
1430 |
1431 | Check if the particular invoice line contains outstanding allocation
1432 | rule exists (not yet applied)
1433 |
1434 | PROGRAM FLOW
1435 |
1436 | return TRUE - if line contains outstanding allocations
1437 | return FALSE - otherwise.
1438 |
1439 | MODIFICATION HISTORY
1440 | Date Author Description of Change
1441 | 03/10/13 bghose Created
1442 *============================================================================*/
1443
1444 FUNCTION Outstanding_Alloc_Exists (p_Invoice_Id Number,
1445 p_Line_Number Number,
1446 p_Calling_Sequence Varchar2) Return Boolean Is
1447 dummy number := 0;
1448 current_calling_sequence Varchar2(2000);
1449 debug_info Varchar2(100);
1450
1451 Begin
1452 -- Update the calling sequence
1453 --
1454 current_calling_sequence :=
1455 'AP_INVOICE_LINES_UTILITY_PKG.Outstanding_Alloc_Exists <-'||
1456 p_Calling_Sequence;
1457 debug_info := 'Select from ap_allocatin_rules';
1458
1459 Select count(*)
1460 Into dummy
1461 From ap_allocation_rules AR,
1462 ap_allocation_rule_lines ARL
1463 Where AR.invoice_id = p_Invoice_Id
1464 And AR.invoice_id = ARL.invoice_id (+)
1465 And AR.chrg_invoice_line_number = ARL.chrg_invoice_line_number (+)
1466 --Commented below condition for bug #9143555 and introduced new conditions
1467 -- And ARL.to_invoice_line_number (+) = p_line_number
1468 And AR.chrg_invoice_line_number = p_line_number
1469 And AR.status = 'PENDING';
1470
1471 If (dummy >= 1) Then
1472 return TRUE;
1473 End if;
1474
1475 return FALSE;
1476 Exception
1477 WHEN OTHERS THEN
1478 If (SQLCODE <> -20001) Then
1479 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1480 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1481 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1482 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice Id = '||p_Invoice_id
1483 ||', line number = '|| p_Line_Number);
1484 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1485 End If;
1486 APP_EXCEPTION.RAISE_EXCEPTION;
1487 End Outstanding_Alloc_Exists;
1488
1489 /*=============================================================================
1490 | Public FUNCTION Line_Dists_Trans_Pa
1491 |
1492 | Check if the particular invoice line contains project related
1493 | distributions
1494 |
1495 | PROGRAM FLOW
1496 |
1497 | return TRUE - if line has been referred by any correction
1498 | return FALSE - otherwise.
1499 |
1500 | MODIFICATION HISTORY
1501 | Date Author Description of Change
1502 | 03/10/13 bghose Created
1503 *============================================================================*/
1504
1505 FUNCTION Line_Dists_Trans_Pa (p_Invoice_Id Number,
1506 p_Line_Number Number,
1507 p_Calling_Sequence Varchar2) Return Boolean Is
1508 dummy number := 0;
1509 current_calling_sequence Varchar2(2000);
1510 debug_info Varchar2(100);
1511
1512 Begin
1513 -- Update the calling sequence
1514 --
1515 current_calling_sequence :=
1516 'AP_INVOICE_LINES_UTILITY_PKG.Line_Dists_Trans_PA <-'||
1517 p_Calling_Sequence;
1518 debug_info := 'Select from ap_invoic_distributions_all';
1519
1520 Select count(*)
1521 Into dummy
1522 From ap_invoice_distributions_all
1523 Where invoice_id = p_Invoice_Id
1524 And invoice_line_number = p_Line_Number
1525 And pa_addition_flag In ('T', 'Y', 'Z') ;
1526
1527 If (dummy >= 1) Then
1528 return TRUE;
1529 End if;
1530
1531 return FALSE;
1532
1533 Exception
1534 WHEN OTHERS THEN
1535 If (SQLCODE <> -20001) Then
1536 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1537 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1538 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1539 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice Id = '||p_Invoice_id
1540 ||', line number = '|| p_Line_Number);
1541 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1542 End If;
1543 APP_EXCEPTION.RAISE_EXCEPTION;
1544 End Line_Dists_Trans_Pa;
1545
1546 /*=============================================================================
1547 | Public FUNCTION Can_Line_Be_Deleted
1548 |
1549 | Check if the particular invoice line can be deleted
1550 |
1551 | PROGRAM FLOW
1552 |
1553 | return TRUE - if line can be deleted
1554 | return FALSE - otherwise and return error code.
1555 |
1556 | MODIFICATION HISTORY
1557 | Date Author Description of Change
1558 | 03/10/13 bghose Created
1559 *============================================================================*/
1560
1561 FUNCTION Can_Line_Be_Deleted (p_line_rec IN ap_invoice_lines%ROWTYPE,
1562 p_error_code OUT NOCOPY Varchar2,
1563 p_Calling_Sequence Varchar2) Return Boolean Is
1564 current_calling_sequence Varchar2(2000);
1565
1566 Begin
1567 -- Update the calling sequence
1568 --
1569 current_calling_sequence :=
1570 'AP_INVOICE_LINES_UTILITY_PKG.Can_Line_Be_Deleted <-'||
1571 p_Calling_Sequence;
1572
1573 If (Ap_Invoice_Lines_Utility_Pkg.Line_Dists_Acct_Event_Created
1574 (p_Line_Rec.Invoice_Id,
1575 p_Line_Rec.Line_Number,
1576 Current_calling_sequence) = TRUE) Then
1577 p_error_code := 'AP_INV_LINE_DELETE_VALIDATED';
1578 return False;
1579 ElsIf (Ap_Invoice_Lines_Utility_Pkg.Line_Referred_By_Corr
1580 (p_Line_Rec.Invoice_Id,
1581 p_Line_Rec.Line_Number,
1582 Current_calling_sequence) = TRUE) Then
1583 p_error_code := 'AP_INV_LINE_DELETE_CORR';
1584 return False;
1585 ElsIf (NVL(Ap_Invoice_Lines_Utility_Pkg.Get_Encumbered_Flag
1586 (p_Line_Rec.Invoice_Id,
1587 p_Line_Rec.Line_Number), 'N')
1588 In ('Y', 'P')) Then
1589 p_error_code := 'AP_INV_LINE_DELETE_ENCUMBERED';
1590 return False;
1591 ElsIf (Ap_Invoice_Lines_Utility_Pkg.Get_Posting_Status
1592 (p_Line_Rec.Invoice_Id,
1593 p_Line_Rec.Line_Number)
1594 In ('Y', 'P', 'S')) Then
1595 p_error_code := 'AP_INV_LINE_DELETE_ACCOUNTED';
1596 return False;
1597 ElsIf (Ap_Invoice_Lines_Utility_Pkg.Line_Dists_Trans_PA
1598 (p_Line_Rec.Invoice_Id,
1599 p_Line_Rec.Line_Number,
1600 Current_calling_sequence) = TRUE) Then
1601 p_error_code := 'AP_INV_LINE_DELETE_PA';
1602 return False;
1603 ElsIf (Ap_Invoice_Lines_Utility_Pkg.Line_Dists_Referred_By_Other
1604 (p_Line_Rec.Invoice_Id,
1605 p_Line_Rec.Line_Number,
1606 Current_calling_sequence) = TRUE) Then
1607 p_error_code := 'AP_INV_LINE_REF_BY_DISTS';
1608 return False;
1609 ElsIf (Ap_Invoice_Lines_Utility_Pkg.Outstanding_Alloc_Exists
1610 (p_Line_Rec.Invoice_Id,
1611 p_Line_Rec.Line_Number,
1612 Current_calling_sequence) = TRUE) Then
1613 p_error_code := 'AP_INV_LINE_HAS_ALLOC_RULE';
1614 return False;
1615 End If;
1616
1617 p_error_code := null;
1618 return TRUE;
1619
1620 Exception
1621 WHEN OTHERS THEN
1622 If (SQLCODE <> -20001) Then
1623 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1624 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1625 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1626 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice Id = '||
1627 p_line_rec.Invoice_id
1628 ||', line number = '|| p_line_rec.Line_Number);
1629 End If;
1630 APP_EXCEPTION.RAISE_EXCEPTION;
1631 End Can_Line_Be_Deleted;
1632
1633 /*=============================================================================
1634 | Public FUNCTION Get_Packet_Id
1635 |
1636 | Get the Packet Id for a line
1637 |
1638 | PROGRAM FLOW
1639 |
1640 |
1641 | MODIFICATION HISTORY
1642 | Date Author Description of Change
1643 | 03/10/13 bghose Created
1644 *============================================================================*/
1645
1646 FUNCTION Get_Packet_Id (p_invoice_id In Number,
1647 p_Line_Number In Number) Return Number Is
1648
1649 l_packet_id number := '';
1650 Cursor packet_id_cursor Is
1651 Select decode(count(distinct(packet_id)),1,max(packet_id),'')
1652 From ap_invoice_distributions
1653 Where invoice_id = p_Invoice_Id
1654 And invoice_line_number = p_Line_Number
1655 And packet_id is not null;
1656
1657 Begin
1658 Open packet_id_cursor;
1659 Fetch packet_id_cursor INTO l_packet_id;
1660 Close packet_id_cursor;
1661
1662 Return(l_packet_id);
1663 End get_packet_id;
1664
1665
1666 /*=============================================================================
1667 | FUNCTION - Is_Line_Fully_Distributed
1668 |
1669 | DESCRIPTION
1670 | This function returns TRUE if the line is completelly distributed.
1671 | It returns FALSE otherwise.
1672 |
1673 | PARAMETERS
1674 | P_Invoice_Id - Invoice Id
1675 | P_Line_number - line number
1676 | P_Calling_Sequence - debug usage
1677 |
1678 | KNOWN ISSUES:
1679 |
1680 | NOTES:
1681 |
1682 | MODIFICATION HISTORY
1683 | Date Author Description of Change
1684 | 30-JUL-2003 SYIDNER Creation
1685 |
1686 *============================================================================*/
1687
1688 FUNCTION Is_Line_Fully_Distributed(
1689 P_Invoice_Id IN NUMBER,
1690 P_Line_Number IN NUMBER,
1691 P_Calling_sequence IN VARCHAR2) RETURN BOOLEAN
1692
1693 IS
1694
1695 CURSOR Dist_Var_Cur IS
1696 SELECT 'Dist Total <> Invoice Line Amount'
1697 FROM ap_invoice_lines AIL, ap_invoice_distributions D
1698 WHERE AIL.invoice_id = D.invoice_id
1699 AND AIL.line_number = p_line_number
1700 AND AIL.invoice_id = p_invoice_id
1701 AND AIL.line_number = D.invoice_line_number
1702 AND (D.line_type_lookup_code <> 'RETAINAGE'
1703 OR (AIL.line_type_lookup_code = 'RETAINAGE RELEASE'
1704 and D.line_type_lookup_code = 'RETAINAGE'))
1705 AND (AIL.line_type_lookup_code <> 'ITEM'
1706 or (AIL.line_type_lookup_code = 'ITEM'
1707 and (D.prepay_distribution_id IS NULL
1708 or (D.prepay_distribution_id IS NOT NULL
1709 and D.line_type_lookup_code NOT IN ('PREPAY', 'REC_TAX', 'NONREC_TAX')))))
1710 GROUP BY AIL.invoice_id, AIL.line_number, AIL.amount
1711 HAVING AIL.amount <> nvl(SUM(nvl(D.amount,0)),0);
1712
1713 current_calling_sequence VARCHAR2(4000);
1714 debug_info VARCHAR2(240);
1715 l_test_var VARCHAR2(50);
1716
1717 BEGIN
1718 -------------------------------------------------------------
1719 current_calling_sequence := 'AP_INVOICE_LINES_UTILITY_PKG - Is_Line_Fully_Distributed';
1720 debug_info := 'Is_Line_Fully_Distributed - Open cursor';
1721 -------------------------------------------------------------
1722
1723 OPEN Dist_Var_Cur;
1724 FETCH Dist_Var_Cur
1725 INTO l_test_var;
1726 CLOSE Dist_Var_Cur;
1727
1728 RETURN (l_test_var IS NULL);
1729
1730 EXCEPTION
1731 WHEN OTHERS THEN
1732 IF (SQLCODE <> -20001) then
1733 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1734 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1735 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1736 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice Id = '||P_Invoice_Id);
1737 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1738 END IF;
1739
1740 IF (Dist_Var_Cur%ISOPEN) THEN
1741 CLOSE Dist_Var_Cur;
1742 END IF;
1743
1744 APP_EXCEPTION.RAISE_EXCEPTION;
1745
1746 END Is_Line_Fully_Distributed;
1747
1748 /*=============================================================================
1749 | FUNCTION - Is_PO_RCV_Amount_Exceeded
1750 |
1751 | DESCRIPTION
1752 | This function returns TRUE if the reversal of the line makes the
1753 | quantity or amount billed go below 0. It returns FALSE otherwise.
1754 |
1755 | PARAMETERS
1756 | P_Invoice_Id - Invoice Id
1757 | P_Line_Number - line number
1758 | P_Calling_Sequence - debug usage
1759 |
1760 | KNOWN ISSUES:
1761 |
1762 | NOTES:
1763 |
1764 | MODIFICATION HISTORY
1765 | Date Author Description of Change
1766 | 30-JUL-2003 SYIDNER Creation
1767 |
1768 *============================================================================*/
1769
1770 FUNCTION Is_PO_RCV_Amount_Exceeded(
1771 P_Invoice_Id IN NUMBER,
1772 P_Line_Number IN NUMBER,
1773 P_Calling_sequence IN VARCHAR2) RETURN BOOLEAN
1774
1775 IS
1776 CURSOR Invoice_Validation IS
1777 SELECT count(*)
1778 FROM po_distributions_all POD,
1779 ap_invoice_distributions AID
1780 WHERE POD.po_distribution_id = AID.po_distribution_id
1781 AND AID.invoice_id = P_Invoice_Id
1782 AND POD.org_id = AID.org_id
1783 AND AID.invoice_line_number = P_Line_Number
1784 AND NVL(AID.reversal_flag,'N')<>'Y'
1785 AND ( NVL(POD.quantity_billed, 0) -
1786 decode( AID.dist_match_type,
1787 'PRICE_CORRECTION', 0,
1788 'AMOUNT_CORRECTION', 0, /* Ampunt Based Matching */
1789 'ITEM_TO_SERVICE_PO', 0,
1790 'ITEM_TO_SERVICE_RECEIPT', 0,
1791 nvl( AID.corrected_quantity,0 ) +
1792 nvl( AID.quantity_invoiced,0 ) ) < 0
1793 OR
1794 NVL(POD.amount_billed, 0) - NVL(AID.amount, 0) < 0 );
1795
1796 l_invoice_id ap_invoices_all.invoice_id%TYPE;
1797 current_calling_sequence VARCHAR2(4000);
1798 debug_info VARCHAR2(240);
1799 l_po_dist_count NUMBER := 0;
1800 l_return_var BOOLEAN := FALSE;
1801
1802
1803 BEGIN
1804 current_calling_sequence := 'AP_INVOICE_LINES_UTILITY_PKG - Is_PO_RCV_Amount_Exceeded';
1805 -------------------------------------------------------------
1806 debug_info := 'Is_PO_RCV_Amount_Exceeded - Open cursor';
1807 -------------------------------------------------------------
1808
1809 OPEN invoice_validation;
1810 FETCH invoice_validation INTO l_po_dist_count;
1811 CLOSE invoice_validation;
1812
1813 -------------------------------------------------------------
1814 debug_info := 'Check if quantity_billed on po_distribution is
1815 brought to 0';
1816 -------------------------------------------------------------
1817 IF (l_po_dist_count > 0 ) THEN
1818 l_return_var := TRUE;
1819 END IF;
1820
1821 RETURN l_return_var;
1822
1823 EXCEPTION
1824 WHEN OTHERS THEN
1825 if (SQLCODE <> -20001) then
1826 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1827 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1828 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1829 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice Id = '||P_Invoice_Id);
1830 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1831 end if;
1832 debug_info := debug_info || 'Error occurred';
1833
1834 IF ( invoice_validation%ISOPEN ) THEN
1835 CLOSE invoice_validation;
1836 END IF;
1837
1838 APP_EXCEPTION.RAISE_EXCEPTION;
1839
1840 END Is_PO_RCV_Amount_Exceeded;
1841
1842 /*=============================================================================
1843 | Public FUNCTION Is_Invoice_Fully_Distributed
1844 |
1845 | Check if an invoice is fully distributed or not. An invoice is
1846 | fully distributed if all of its lines were distributed.
1847 |
1848 | PROGRAM FLOW
1849 |
1850 | return TRUE - if invoice is fully distributed
1851 | return FALSE - otherwise.
1852 |
1853 | MODIFICATION HISTORY
1854 | Date Author Description of Change
1855 | 24-FEB-2004 ISartawi Created
1856 *============================================================================*/
1857
1858 FUNCTION Is_Invoice_Fully_Distributed (
1859 P_invoice_id IN NUMBER) RETURN BOOLEAN
1860 IS
1861 l_count NUMBER;
1862 BEGIN
1863
1864 -- This function is used to determine if the invoice is fully
1865 -- distributed or not. The invoice is fully distributed if all
1866 -- its lines were distributed. In this case the line will have
1867 -- generate_dists = 'D'. If one line had generate_dists <> 'D'
1868 -- then the invoice is not fully distributed.
1869
1870 SELECT COUNT(*)
1871 INTO l_count
1872 FROM ap_invoice_lines
1873 WHERE invoice_id = p_invoice_id
1874 AND generate_dists <> 'D'
1875 AND ROWNUM = 1;
1876
1877 IF l_count > 0 THEN
1878 RETURN (FALSE); -- The Invoice is not fully distributed
1879 ELSE
1880 RETURN (TRUE); -- The Invoice is fully distributed
1881 END IF;
1882
1883 END Is_Invoice_Fully_Distributed;
1884
1885
1886 --Invoice Lines: Distributions
1887 /*=============================================================================
1888 | Public FUNCTION Pending_Alloc_Exists_Chrg_Line
1889 |
1890 | Check if the particular invoice charge line contains outstanding allocation
1891 | rule exists (not yest applied)
1892 |
1893 | PROGRAM FLOW
1894 |
1895 | return TRUE - if line contains outstanding allocations
1896 | return FALSE - otherwise.
1897 |
1898 | MODIFICATION HISTORY
1899 | Date Author Description of Change
1900 | 01/27/2004 surekha myadam Created
1901 *============================================================================*/
1902 FUNCTION Pending_Alloc_Exists_Chrg_Line
1903 (p_Invoice_Id Number,
1904 p_Line_Number Number,
1905 p_Calling_Sequence Varchar2) Return Boolean Is
1906 dummy number := 0;
1907 current_calling_sequence Varchar2(2000);
1908 debug_info Varchar2(100);
1909 Begin
1910 -- Update the calling sequence
1911 --
1912 current_calling_sequence :=
1913 'AP_INVOICE_LINES_UTILITY_PKG.Pending_Alloc_Exists_Chrg_Line <-'||
1914 p_Calling_Sequence;
1915 debug_info := 'Select from ap_allocatin_rules';
1916
1917 Select count(*)
1918 Into dummy
1919 From ap_allocation_rules AR
1920 Where AR.invoice_id = p_Invoice_Id
1921 And AR.chrg_invoice_line_number = p_line_number
1922 And AR.status = 'PENDING';
1923
1924 If (dummy >= 1) Then
1925 return TRUE;
1926 End if;
1927
1928 return FALSE;
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', 'Invoice Id = '||p_Invoice_id
1936 ||', line number = '|| p_Line_Number);
1937 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1938 End If;
1939 APP_EXCEPTION.RAISE_EXCEPTION;
1940 End Pending_Alloc_Exists_Chrg_Line;
1941
1942
1943 /*=============================================================================
1944 | Public FUNCTION Is_Line_a_Correction
1945 |
1946 | Check if the particular invoice line is correcting some other invoice line.
1947 |
1948 | PROGRAM FLOW
1949 |
1950 | return TRUE - if line is a correction
1951 | return FALSE - otherwise.
1952 |
1953 | MODIFICATION HISTORY
1954 | Date Author Description of Change
1955 | 01-JUL-2004 Surekha Myadam Created
1956 *============================================================================*/
1957 FUNCTION Is_Line_a_Correction(
1958 P_Invoice_Id IN NUMBER,
1959 P_Line_Number IN NUMBER,
1960 P_Calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
1961
1962 is_correction varchar2(1) := 'N';
1963 BEGIN
1964
1965 SELECT 'Y'
1966 INTO is_correction
1967 FROM ap_invoice_lines
1968 WHERE invoice_id = p_invoice_id
1969 AND line_number = p_line_number
1970 AND corrected_inv_id IS NOT NULL
1971 AND corrected_line_number IS NOT NULL;
1972
1973
1974 IF (is_correction = 'Y') THEN
1975 return (TRUE);
1976 ELSE
1977 return (FALSE);
1978 END IF;
1979
1980 EXCEPTION WHEN OTHERS THEN
1981 RETURN(FALSE);
1982
1983 END Is_Line_a_Correction;
1984
1985
1986
1987 /*=============================================================================
1988 | Public FUNCTION Line_Referred_By_Adjustment
1989 |
1990 | Check if the particular invoice line has been adjusted by PO Price Adjustment
1991 |
1992 | PROGRAM FLOW
1993 |
1994 | return TRUE - if line is adjusted by PO Price Adjustment
1995 | return FALSE - otherwise.
1996 |
1997 | MODIFICATION HISTORY
1998 | Date Author Description of Change
1999 | 01-JUL-2004 Surekha Myadam Created
2000 *============================================================================*/
2001 FUNCTION Line_Referred_By_Adjustment(
2002 P_Invoice_Id IN NUMBER,
2003 P_Line_Number IN NUMBER,
2004 P_Calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
2005 l_count NUMBER := 0;
2006 BEGIN
2007
2008 SELECT count(*)
2009 INTO l_count
2010 FROM ap_invoice_lines_all
2011 WHERE corrected_inv_id = p_invoice_id
2012 AND corrected_line_number = p_line_number
2013 AND line_type_lookup_code IN ('RETROITEM')
2014 AND line_source = 'PO PRICE ADJUSTMENT'
2015 AND match_type = 'RETRO PRICE ADJUSTMENT';
2016
2017
2018 IF (l_count > 0) THEN
2019 RETURN (TRUE);
2020 ELSE
2021 RETURN (FALSE);
2022 END IF;
2023
2024 END Line_Referred_By_Adjustment;
2025
2026
2027 /*=============================================================================
2028 | Public FUNCTION Is_Line_a_Adjustment
2029 |
2030 | Check if the particular invoice line has adjusted (po price adjust)
2031 | some other invoice line.
2032 |
2033 | PROGRAM FLOW
2034 |
2035 | return TRUE - if line is a po price adjustment line.
2036 | return FALSE - otherwise.
2037 |
2038 | MODIFICATION HISTORY
2039 | Date Author Description of Change
2040 | 01-JUL-2004 Surekha Myadam Created
2041 *============================================================================*/
2042 FUNCTION Is_Line_a_Adjustment(
2043 P_Invoice_Id IN NUMBER,
2044 P_Line_Number IN NUMBER,
2045 P_Calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
2046 is_po_price_adjustment VARCHAR2(1) := 'N';
2047 BEGIN
2048
2049 SELECT 'Y'
2050 INTO is_po_price_adjustment
2051 FROM ap_invoice_lines_all
2052 WHERE invoice_id = p_invoice_id
2053 AND line_number = p_line_number
2054 AND line_type_lookup_code = 'RETROITEM'
2055 AND line_source = 'PO PRICE ADJUSTMENT'
2056 AND match_type = 'RETRO PRICE ADJUSTMENT';
2057
2058 IF (is_po_price_adjustment = 'Y') THEN
2059 RETURN(TRUE);
2060 ELSE
2061 RETURN(FALSE);
2062 END IF;
2063
2064 EXCEPTION WHEN OTHERS THEN
2065 RETURN(FALSE);
2066
2067 END Is_Line_a_Adjustment;
2068
2069
2070 /*=============================================================================
2071 | Public FUNCTION Is_Line_a_Prepay
2072 |
2073 | Check if the particular invoice line is a prepayment application/unapplication
2074 | (Normally this can be identified by looking at the line_type_lookup_code
2075 | but from the place where this is called (etax windows) the line_type is not
2076 | available, hence need to code this function.)
2077 |
2078 | PROGRAM FLOW
2079 |
2080 | return TRUE - if line of type PREPAY.
2081 | return FALSE - otherwise.
2082 |
2083 | MODIFICATION HISTORY
2084 | Date Author Description of Change
2085 | 01-JUL-2004 Surekha Myadam Created
2086 *============================================================================*/
2087 FUNCTION Is_Line_a_Prepay(
2088 P_Invoice_Id IN NUMBER,
2089 P_Line_Number IN NUMBER,
2090 P_Calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
2091 is_prepayment varchar2(1) := 'N';
2092 BEGIN
2093
2094 SELECT 'Y'
2095 INTO is_prepayment
2096 FROM ap_invoice_lines
2097 WHERE invoice_id = p_invoice_id
2098 AND line_number = p_line_number
2099 AND line_type_lookup_code = 'PREPAY';
2100
2101 IF (is_prepayment = 'Y') THEN
2102 return (TRUE);
2103 ELSE
2104 return (FALSE);
2105 END IF;
2106
2107 EXCEPTION WHEN OTHERS THEN
2108
2109 return(FALSE);
2110
2111 END Is_Line_a_Prepay;
2112
2113 Function Get_Retained_Amount
2114 (p_line_location_id IN NUMBER,
2115 p_match_amount IN NUMBER) RETURN NUMBER IS
2116
2117 l_ret_status Varchar2(100);
2118 l_msg_data Varchar2(4000);
2119
2120 l_currency_code PO_HEADERS_ALL.currency_code%type;
2121
2122 l_line_loc_tab PO_TBL_NUMBER;
2123 l_line_loc_amt_tab PO_TBL_NUMBER;
2124 l_amt_to_retain_tab PO_TBL_NUMBER;
2125
2126 l_retained_amount Number;
2127
2128 Begin
2129
2130 If p_line_location_id Is Not Null Then
2131
2132 l_line_loc_tab := po_tbl_number();
2133 l_line_loc_tab.extend;
2134 l_line_loc_tab(l_line_loc_tab.last) := p_line_location_id;
2135
2136 l_line_loc_amt_tab := po_tbl_number();
2137 l_line_loc_amt_tab.extend;
2138 l_line_loc_amt_tab(l_line_loc_amt_tab.last) := p_match_amount;
2139
2140 -- bug6882900
2141 BEGIN
2142
2143 SELECT currency_code
2144 INTO l_currency_code
2145 FROM po_headers_all
2146 WHERE po_header_id IN
2147 (SELECT po_header_id
2148 FROM po_line_locations_all
2149 WHERE line_location_id = p_line_location_id)
2150 AND rownum < 2;
2151
2152 EXCEPTION
2153 WHEN OTHERS THEN
2154 NULL;
2155
2156 END;
2157
2158
2159 PO_AP_INVOICE_MATCH_GRP.get_amount_to_retain(
2160 p_api_version => 1.0
2161 , p_line_location_id_tbl => l_line_loc_tab
2162 , p_line_loc_match_amt_tbl => l_line_loc_amt_tab
2163 , x_return_status => l_ret_status
2164 , x_msg_data => l_msg_data
2165 , x_amount_to_retain_tbl => l_amt_to_retain_tab);
2166
2167 IF l_amt_to_retain_tab.count > 0 THEN
2168
2169 l_retained_amount := -1 * l_amt_to_retain_tab(l_amt_to_retain_tab.last);
2170
2171 END IF;
2172
2173 End If;
2174
2175 -- bug6882900
2176 Return (ap_utilities_pkg.ap_round_currency(l_retained_amount, l_currency_code));
2177
2178 End Get_Retained_Amount;
2179
2180 /* ==========================================================================================
2181 * Procedure manual_withhold_tax()
2182 * Objective update ap_payment_schedules.remaining_amount for manual entry
2183 * withholding lines
2184 * This procedire has been moved from payment schedules library since it did
2185 * not consider the
2186 * ap lines model
2187 * This PROCEDURE is added for Bug 6917289
2188 * =============================================================================================*/
2189 PROCEDURE Manual_Withhold_Tax(p_invoice_id IN number
2190 ,p_manual_withhold_amount IN number
2191 ) IS
2192
2193 l_inv_amt_remaining ap_payment_schedules.amount_remaining%TYPE := 0;
2194 l_gross_amount ap_payment_schedules.gross_amount%TYPE := 0;
2195 l_payment_cross_rate ap_invoices_all.payment_cross_rate%TYPE :=0;
2196 l_payment_currency_code ap_invoices_all.payment_currency_code%TYPE;
2197
2198 -- Debug variables
2199 l_debug_loc VARCHAR2(30) := 'Manual_Withhold_Tax';
2200 l_curr_calling_sequence VARCHAR2(2000);
2201 l_debug_info VARCHAR2(2000);
2202
2203
2204 BEGIN
2205
2206 l_curr_calling_sequence := 'AP_INVOICE_LINES_UTILITY_PKG.'||l_debug_loc;
2207
2208 SELECT nvl(payment_cross_rate,0), payment_currency_code
2209 INTO l_payment_cross_rate, l_payment_currency_code
2210 FROM ap_invoices_all
2211 WHERE invoice_id = p_invoice_id;
2212
2213 SELECT sum(nvl(amount_remaining,0)), sum(nvl(gross_amount,0))
2214 INTO l_inv_amt_remaining, l_gross_amount
2215 FROM ap_payment_schedules
2216 WHERE invoice_id = p_invoice_id;
2217
2218 l_debug_info := 'Updating payment schedules due a manual withholding tax';
2219
2220 IF ((l_inv_amt_remaining <> 0) AND (p_manual_withhold_amount is not null))
2221 THEN
2222 update ap_payment_schedules
2223 set amount_remaining = (amount_remaining +
2224 ap_utilities_pkg.ap_round_currency(
2225 (amount_remaining * (p_manual_withhold_amount/l_inv_amt_remaining)
2226 * l_payment_cross_rate), l_payment_currency_code))
2227 where invoice_id = p_invoice_id;
2228
2229 ELSIF ((l_inv_amt_remaining = 0) and (p_manual_withhold_amount is not null))
2230 THEN
2231 update ap_payment_schedules
2232 set amount_remaining = (amount_remaining +
2233 ap_utilities_pkg.ap_round_currency(
2234 (gross_amount * (p_manual_withhold_amount/l_gross_amount)
2235 * l_payment_cross_rate), l_payment_currency_code)),
2236 payment_status_flag = DECODE(payment_status_flag,'Y','P',payment_status_flag)
2237 where invoice_id = p_invoice_id;
2238
2239 update ap_invoices
2240 set payment_status_flag = DECODE(payment_status_flag,'Y','P',payment_status_flag)
2241 where invoice_id = p_invoice_id ;
2242
2243 END IF;
2244
2245 EXCEPTION
2246 WHEN NO_DATA_FOUND THEN
2247 NULL;
2248 WHEN OTHERS THEN
2249 IF (SQLCODE <> -20001) THEN
2250 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2251 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2252 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2253 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2254 'P_Invoice_Id = '|| p_invoice_id
2255 ||', p_manual_withhold_amount= '|| to_char(p_manual_withhold_amount));
2256 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2257 END IF;
2258
2259 APP_EXCEPTION.RAISE_EXCEPTION;
2260
2261 END Manual_Withhold_Tax;
2262
2263 /* ==================================================================================
2264 * Function get_awt_flag()
2265 * Objective Retrun the awt_flag for a given invoice_id and invoice_line_number
2266 * This function is called from the invoice lines library
2267 * Bug 6917289
2268 * ==================================================================================*/
2269
2270 FUNCTION get_awt_flag(
2271 p_invoice_id IN NUMBER,
2272 p_line_number IN NUMBER )
2273 RETURN VARCHAR2
2274 IS
2275 l_awt_flag ap_invoice_distributions_all.awt_flag%TYPE;
2276
2277 BEGIN
2278
2279 SELECT awt_flag
2280 INTO l_awt_flag
2281 FROM ap_invoice_distributions_all
2282 WHERE invoice_id = p_invoice_id
2283 AND invoice_line_number = p_line_number
2284 AND rownum = 1;
2285
2286 IF l_awt_flag is null THEN
2287 RETURN ('Z');
2288 ELSE
2289 RETURN (l_awt_flag);
2290 END IF;
2291
2292 EXCEPTION
2293 WHEN NO_DATA_FOUND THEN
2294 RETURN ('B');
2295 WHEN Others THEN
2296 RETURN ('Z');
2297 END get_awt_flag;
2298
2299 END AP_INVOICE_LINES_UTILITY_PKG;
[Home] [Help]