[Home] [Help]
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
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
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
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'
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'))
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
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
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
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
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
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:
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:
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'
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
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);
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
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
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: