[Home] [Help]
87:
88: -- This would lock the selected lines for both the
89: -- online and the batch cases.
90:
91: UPDATE ap_invoice_lines
92: SET line_selected_for_appl_flag = 'Y',
93: prepay_appl_request_id = p_request_id
94: WHERE invoice_id = p_invoice_id
95: AND line_number = p_line_number;
109: P_line_number IN NUMBER) RETURN BOOLEAN
110: IS
111: PRAGMA AUTONOMOUS_TRANSACTION;
112: BEGIN
113: UPDATE ap_invoice_lines
114: SET line_selected_for_appl_flag = 'N',
115: prepay_appl_request_id = NULL
116: WHERE invoice_id = p_invoice_id
117: AND line_number = p_line_number;
134:
135: -- This would lock the selected lines for both the
136: -- online and the batch cases.
137:
138: UPDATE ap_invoice_lines
139: SET line_selected_for_appl_flag = 'N',
140: prepay_appl_request_id = NULL
141: WHERE ( prepay_appl_request_id = p_request_id
142: OR prepay_appl_request_id IS NULL)
163: SELECT NVL(line_selected_for_appl_flag,'N'),
164: prepay_appl_request_id
165: INTO l_already_selected_flag,
166: l_request_id
167: FROM ap_invoice_lines
168: WHERE invoice_id = p_invoice_id
169: AND line_number = p_line_number;
170:
171: IF l_already_selected_flag = 'Y' AND
263: --bug4944102. As part of Performance fic for invoice workbench
264: --fixed the following query
265: /* SELECT (0 - SUM(NVL(amount,0)))
266: INTO l_prepaid_amount
267: FROM ap_invoice_lines_all
268: WHERE invoice_id = l_invoice_id
269: AND line_type_lookup_code IN ('PREPAY', 'TAX')
270: AND NVL(invoice_includes_prepay_flag, 'N') = 'N' -- Bug 5675960. Added the NVL
271: AND nvl(prepay_invoice_id,-999)<>-999
273:
274: SELECT (0 - SUM(NVL(aid.amount,0)))
275: INTO l_prepaid_amount
276: FROM ap_invoice_distributions_all aid,
277: ap_invoice_lines_all ail
278: WHERE ail.invoice_id = l_invoice_id
279: AND ail.invoice_id = aid.invoice_id
280: AND ail.line_number = aid.invoice_line_number
281: AND aid.line_type_lookup_code = 'PREPAY'
389: CURSOR prepay_cursor is
390: SELECT SUM(total_dist_amount -
391: NVL(prepay_amount_remaining, total_dist_amount))
392: FROM ap_invoice_distributions_all aid,
393: ap_invoice_lines_all ail
394: WHERE aid.invoice_id = P_invoice_id
395: AND aid.invoice_id = ail.invoice_id
396: AND aid.invoice_line_number = ail.line_number
397: AND ail.line_type_lookup_code <> 'TAX'
403: AND NVL(reversal_flag,'N') <> 'Y';
404:
405: CURSOR inv_prepay_cursor is
406: SELECT ABS(SUM(amount))
407: FROM ap_invoice_lines_all ail
408: WHERE ail.invoice_id = P_invoice_id
409: AND ail.line_type_lookup_code = 'PREPAY';
410:
411: BEGIN
443: -- exclusive tax will not.
444: CURSOR c_prepay_amount_remaining IS
445: SELECT SUM(nvl(prepay_amount_remaining, total_dist_amount))
446: FROM ap_invoice_distributions_all aid,
447: ap_invoice_lines_all ail
448: WHERE aid.invoice_id = P_invoice_id
449: AND aid.invoice_id = ail.invoice_id
450: AND aid.invoice_line_number = ail.line_number
451: AND ail.line_type_lookup_code <> 'TAX'
538: SELECT SUM((NVL(aid1.amount, 0) - NVL(aid1.prepay_tax_diff_amount, 0))* -1)
539: INTO l_prepay_amt_applied
540: FROM ap_invoice_distributions_all aid1,
541: ap_invoice_distributions_all aid2,
542: ap_invoice_lines_all ail
543: WHERE aid1.invoice_id = P_invoice_id
544: AND aid1.line_type_lookup_code IN ('PREPAY', 'REC_TAX', 'NONREC_TAX')
545: AND aid1.invoice_id = ail.invoice_id
546: AND aid1.invoice_line_number = ail.line_number
571: -- Tax: inclusive included, exclusive is not.
572:
573: SELECT SUM(ail.amount * -1)
574: INTO l_prepay_amt_applied
575: FROM ap_invoice_lines_all ail
576: WHERE ail.invoice_id = P_invoice_id
577: AND ail.line_type_lookup_code = 'PREPAY'
578: AND ail.prepay_invoice_id = P_prepay_id;
579:
712:
713: BEGIN
714: SELECT SUM(NVL(ail.amount, 0))
715: INTO l_exclusive_tax_amt_applied
716: FROM ap_invoice_lines_all ail
717: WHERE ail.invoice_id = X_invoice_id
718: AND ail.line_type_lookup_code = 'TAX'
719: AND NVL(ail.discarded_flag, 'N') <> 'Y'
720: AND NVL(ail.cancelled_flag, 'N') <> 'Y'
757:
758: /* Commented for bug 7506584
759: CURSOR c_invoice_lines_info IS
760: SELECT NVL(SUM(NVL(ail.amount,0)), 0)
761: FROM ap_invoice_lines_all ail
762: WHERE ail.invoice_id = X_Invoice_Id
763: AND ail.line_type_lookup_code <> 'TAX'
764: AND (ail.line_type_lookup_code <> 'PREPAY'
765: OR NVL(ail.invoice_includes_prepay_flag,'N') = 'Y'); */
805: INTO l_tax_lines,
806: l_non_tax_lines,
807: l_prep_applied,
808: l_awt_lines
809: FROM ap_invoice_lines_all ail
810: WHERE ail.invoice_id = x_invoice_id;
811:
812: SELECT nvl(SUM(nvl(amount, 0)), 0)
813: INTO l_item_lines_proration
810: WHERE ail.invoice_id = x_invoice_id;
811:
812: SELECT nvl(SUM(nvl(amount, 0)), 0)
813: INTO l_item_lines_proration
814: FROM ap_invoice_lines_all ail
815: WHERE ail.invoice_id = x_invoice_id
816: AND ail.line_type_lookup_code <> 'TAX'
817: AND(ail.line_type_lookup_code <> 'PREPAY' OR nvl(ail.invoice_includes_prepay_flag, 'N') = 'Y');
818:
817: AND(ail.line_type_lookup_code <> 'PREPAY' OR nvl(ail.invoice_includes_prepay_flag, 'N') = 'Y');
818:
819: SELECT nvl(SUM(nvl(amount, 0)), 0)
820: INTO l_tax_lines_proration
821: FROM ap_invoice_lines_all ail
822: WHERE ail.invoice_id = x_invoice_id
823: AND ail.line_type_lookup_code = 'TAX'
824: AND ail.amount > 0;
825:
953:
954: /* Commented for bug 7506584
955: CURSOR c_invoice_lines_info IS
956: SELECT NVL(SUM(NVL(ail.amount,0)), 0)
957: FROM ap_invoice_lines_all ail
958: WHERE ail.invoice_id = X_Invoice_Id
959: AND (ail.line_type_lookup_code <> 'PREPAY'
960: OR NVL(ail.invoice_includes_prepay_flag,'N') = 'Y');
961:
1075: WHERE ai.invoice_id = X_Invoice_Id;
1076:
1077: CURSOR c_invoice_lines_info IS
1078: SELECT NVL(SUM(NVL(ail.included_tax_amount,0)), 0)
1079: FROM ap_invoice_lines_all ail
1080: WHERE ail.invoice_id = X_Invoice_Id
1081: AND (ail.line_type_lookup_code <> 'PREPAY'
1082: OR NVL(ail.invoice_includes_prepay_flag,'N') = 'Y');
1083: