[Home] [Help]
1124: IF (l_base_currency_code <> l_invoice_currency_code) THEN
1125:
1126: SELECT SUM(amount)
1127: INTO l_dist_total
1128: FROM ap_invoice_distributions
1129: WHERE invoice_id = l_invoice_id
1130: AND ( (line_type_lookup_code NOT IN ('PREPAY','AWT') AND
1131: prepay_tax_parent_id IS NULL) OR
1132: (line_type_lookup_code = 'PREPAY' AND
1153: ---------------------------------------------------------------
1154:
1155: SELECT COUNT('X')
1156: INTO l_item_count
1157: FROM ap_invoice_distributions
1158: WHERE invoice_id = l_invoice_id AND
1159: line_type_lookup_code = 'ITEM';
1160:
1161: IF (l_dist_total = l_inv_amount) THEN
1161: IF (l_dist_total = l_inv_amount) THEN
1162:
1163: SELECT SUM(base_amount)
1164: INTO l_base_dist_total
1165: FROM ap_invoice_distributions
1166: WHERE invoice_id = l_invoice_id
1167: AND (
1168: (line_type_lookup_code NOT IN ('PREPAY','AWT') AND
1169: prepay_tax_parent_id IS NULL) OR
1186: l_debug_info := 'Adjust for rounding';
1187: ---------------------------------------------------------------
1188: IF (l_item_count > 0) THEN
1189: --Update ITEM Dists
1190: UPDATE ap_invoice_distributions
1191: SET base_amount = base_amount - (l_base_dist_total - l_inv_base_amount)
1192: WHERE invoice_id = l_invoice_id
1193: AND invoice_distribution_id = (
1194: SELECT MAX(AID1.invoice_distribution_id)
1191: SET base_amount = base_amount - (l_base_dist_total - l_inv_base_amount)
1192: WHERE invoice_id = l_invoice_id
1193: AND invoice_distribution_id = (
1194: SELECT MAX(AID1.invoice_distribution_id)
1195: FROM ap_invoice_distributions AID1
1196: WHERE AID1.invoice_id = l_invoice_id
1197: AND AID1.line_type_lookup_code = 'ITEM'
1198: /* Bug 3784909. Folowing two lines Added */
1199: AND NVL(AID1.reversal_flag, 'N') <> 'Y'
1199: AND NVL(AID1.reversal_flag, 'N') <> 'Y'
1200: AND NVL(AID1.posted_flag, 'N') = 'N'
1201: AND ABS(AID1.amount) = (
1202: SELECT MAX(ABS(AID2.amount))
1203: FROM ap_invoice_distributions AID2
1204: WHERE AID2.invoice_id = l_invoice_id
1205: AND AID2.line_type_lookup_code = 'ITEM'
1206: -- Bug 3784909. Folowing two lines Added
1207: AND NVL(AID2.reversal_flag, 'N') <> 'Y'
1207: AND NVL(AID2.reversal_flag, 'N') <> 'Y'
1208: AND NVL(AID2.posted_flag, 'N') = 'N'));
1209: ELSE
1210: -- Update FREIGHT or MISC Dists
1211: UPDATE ap_invoice_distributions
1212: SET base_amount = base_amount - (l_base_dist_total - l_inv_base_amount)
1213: WHERE invoice_id = l_invoice_id
1214: AND invoice_distribution_id = (
1215: SELECT MAX(AID3.invoice_distribution_id)
1212: SET base_amount = base_amount - (l_base_dist_total - l_inv_base_amount)
1213: WHERE invoice_id = l_invoice_id
1214: AND invoice_distribution_id = (
1215: SELECT MAX(AID3.invoice_distribution_id)
1216: FROM ap_invoice_distributions AID3
1217: WHERE AID3.invoice_id = l_invoice_id
1218: AND AID3.line_type_lookup_code
1219: IN ('FREIGHT','MISCELLANEOUS')
1220: AND
1222: NVL(AID3.reversal_flag, 'N') = 'N'
1223: AND NVL(AID3.posted_flag, 'N') = 'N'
1224: AND ABS(AID3.amount) = (
1225: SELECT MAX(ABS(AID4.amount))
1226: FROM ap_invoice_distributions AID4
1227: WHERE AID4.invoice_id = l_invoice_id
1228: AND AID4.line_type_lookup_code
1229: IN('FREIGHT','MISCELLANEOUS')
1230: --Bug 3784909. Folowing two lines Added
1460: Print_Debug(l_api_name, l_debug_info);
1461: ----------------------------------------------------------------
1462: SELECT count(*)
1463: INTO l_prepay_dist_count
1464: FROM ap_invoice_distributions_all
1465: WHERE Invoice_ID = l_invoice_id
1466: AND Line_Type_Lookup_Code = 'PREPAY'
1467: AND Accounting_Event_ID IS NULL;
1468:
1483: Print_Debug(l_api_name, l_debug_info);
1484: -- Bug 6681580
1485: select count(*)
1486: INTO l_encumbrance_exists
1487: FROM ap_invoice_distributions aid
1488: WHERE nvl(aid.encumbered_flag,'N') not in ('N','R') ----added check for 'R' due to bug 7264524
1489: AND aid.invoice_id = l_invoice_id;
1490:
1491: -- Bug 6681580
1518: SELECT 'Y'
1519: INTO l_lcm_used
1520: FROM DUAL
1521: WHERE EXISTS
1522: (SELECT 1 FROM AP_INVOICE_DISTRIBUTIONS aid, RCV_TRANSACTIONS rt
1523: WHERE aid.invoice_id = l_invoice_id
1524: AND aid.rcv_transaction_id = rt.transaction_id
1525: AND rt.lcm_shipment_line_id IS NOT NULL
1526: AND aid.match_status_flag = 'S');
1735: END IF;
1736: END IF;
1737: END IF;
1738:
1739: AP_INVOICE_DISTRIBUTIONS_PKG.Make_Distributions_Permanent
1740: (P_Invoice_Id => p_invoice_id,
1741: P_Invoice_Line_Number => NULL,
1742: P_Calling_Sequence => 'Invoice Validation'); --Bug6653070
1743:
2067: IF (p_run_option = 'New') THEN
2068:
2069: SELECT count(*)
2070: INTO l_unapproved_dist_exists
2071: FROM ap_invoice_distributions_all
2072: WHERE invoice_id = p_invoice_id
2073: AND (nvl(match_status_flag, 'N')) = 'N'
2074: AND rownum = 1;
2075:
2080: FROM ap_invoice_lines_all L
2081: WHERE L.invoice_id = p_invoice_id
2082: AND L.amount <>
2083: (SELECT NVL(SUM(NVL(aid.amount,0)),0)
2084: FROM ap_invoice_distributions_all aid
2085: WHERE aid.invoice_id = L.invoice_id
2086: AND aid.invoice_line_number = L.line_number);
2087: END;
2088:
2153:
2154: l_debug_info := 'Run Option: New: Set new distribution flag to S';
2155: Print_Debug (l_debug_loc,l_debug_info);
2156:
2157: UPDATE ap_invoice_distributions_all D
2158: SET match_status_flag = 'S'
2159: WHERE NVL(match_status_flag, 'N') = 'N'
2160: AND D.invoice_id = p_invoice_id
2161: AND D.invoice_line_number = p_line_number;
2173:
2174: l_debug_info := 'Run Option: All: Set new distribution flag to S';
2175: Print_Debug (l_debug_loc,l_debug_info);
2176:
2177: UPDATE ap_invoice_distributions_all D
2178: SET match_status_flag = 'S'
2179: WHERE NVL(match_status_flag, '!') <> 'A'
2180: AND D.invoice_id = p_invoice_id;
2181:
2544:
2545: l_debug_info := 'Execute_Dist_Generation_Check - charge line with an allocation rule';
2546: Print_Debug(l_debug_loc, l_debug_info);
2547:
2548: l_success := AP_INVOICE_DISTRIBUTIONS_PKG.Insert_Charge_From_Alloc(
2549: X_invoice_id => p_inv_line_rec.invoice_id,
2550: X_line_number => p_inv_line_rec.line_number,
2551: X_Generate_Permanent => p_generate_permanent,
2552: X_Validate_Info => TRUE,
2596:
2597: l_debug_info := 'Execute_Dist_Generation_Check - Insert_Single_Dist_From_Line';
2598: Print_Debug(l_debug_loc, l_debug_info);
2599:
2600: l_success := AP_INVOICE_DISTRIBUTIONS_PKG.Insert_Single_Dist_From_Line(
2601: X_batch_id => p_batch_id,
2602: X_invoice_id => p_inv_line_rec.invoice_id,
2603: X_invoice_date => p_invoice_date,
2604: X_vendor_id => p_vendor_id,
2947: p_calling_sequence IN VARCHAR2) IS
2948:
2949: CURSOR Invalid_Dist_Acct_Cur IS
2950: SELECT D.dist_code_combination_id, D.accounting_date
2951: FROM ap_invoice_distributions D
2952: WHERE D.invoice_id = p_invoice_id
2953: AND D.posted_flag||'' in ('N', 'P')
2954: AND ((EXISTS (select 'x'
2955: from gl_code_combinations C
2980: AND c_acct_date BETWEEN NVL(a.start_date_active, c_acct_date)
2981: AND NVL(a.end_date_active, c_acct_date)
2982: );
2983:
2984: l_ccid AP_INVOICE_DISTRIBUTIONS_ALL.dist_code_combination_id%TYPE;
2985: l_accounting_date AP_INVOICE_DISTRIBUTIONS_ALL.accounting_date%TYPE;
2986: l_alt_exists VARCHAR2(50) := 'N';
2987: l_invalid_dist_ccid_exists VARCHAR2(1) := 'N';
2988: l_test_var VARCHAR2(50);
2981: AND NVL(a.end_date_active, c_acct_date)
2982: );
2983:
2984: l_ccid AP_INVOICE_DISTRIBUTIONS_ALL.dist_code_combination_id%TYPE;
2985: l_accounting_date AP_INVOICE_DISTRIBUTIONS_ALL.accounting_date%TYPE;
2986: l_alt_exists VARCHAR2(50) := 'N';
2987: l_invalid_dist_ccid_exists VARCHAR2(1) := 'N';
2988: l_test_var VARCHAR2(50);
2989: l_debug_loc VARCHAR2(30) := 'Check_Invalid_Dist';
3113:
3114: CURSOR PO_Required_Cur IS
3115: -- Perf bug 5058995
3116: -- Modify below SQL to go to base tables : AP_INVOICES_ALL,
3117: -- AP_INVOICE_DISTRIBUTIONS_ALL and
3118: -- AP_SUPPLIER_SITES(instead of po_vendor_sites)
3119: /* Added the Hint index(apd AP_INVOICE_DISTRIBUTIONS_U1) for bug#7270053 */
3120: SELECT 'PO REQUIRED'
3121: FROM ap_invoices_all api, ap_supplier_sites pov
3115: -- Perf bug 5058995
3116: -- Modify below SQL to go to base tables : AP_INVOICES_ALL,
3117: -- AP_INVOICE_DISTRIBUTIONS_ALL and
3118: -- AP_SUPPLIER_SITES(instead of po_vendor_sites)
3119: /* Added the Hint index(apd AP_INVOICE_DISTRIBUTIONS_U1) for bug#7270053 */
3120: SELECT 'PO REQUIRED'
3121: FROM ap_invoices_all api, ap_supplier_sites pov
3122: WHERE EXISTS (select /*+ index(apd AP_INVOICE_DISTRIBUTIONS_U1) */ 'X'
3123: from ap_invoice_distributions_all apd
3118: -- AP_SUPPLIER_SITES(instead of po_vendor_sites)
3119: /* Added the Hint index(apd AP_INVOICE_DISTRIBUTIONS_U1) for bug#7270053 */
3120: SELECT 'PO REQUIRED'
3121: FROM ap_invoices_all api, ap_supplier_sites pov
3122: WHERE EXISTS (select /*+ index(apd AP_INVOICE_DISTRIBUTIONS_U1) */ 'X'
3123: from ap_invoice_distributions_all apd
3124: where apd.invoice_id = api.invoice_id
3125: and apd.line_type_lookup_code in ( 'ITEM', 'ACCRUAL')
3126: and apd.po_distribution_id is null
3119: /* Added the Hint index(apd AP_INVOICE_DISTRIBUTIONS_U1) for bug#7270053 */
3120: SELECT 'PO REQUIRED'
3121: FROM ap_invoices_all api, ap_supplier_sites pov
3122: WHERE EXISTS (select /*+ index(apd AP_INVOICE_DISTRIBUTIONS_U1) */ 'X'
3123: from ap_invoice_distributions_all apd
3124: where apd.invoice_id = api.invoice_id
3125: and apd.line_type_lookup_code in ( 'ITEM', 'ACCRUAL')
3126: and apd.po_distribution_id is null
3127: and apd.pa_addition_flag <> 'T'
3320: SELECT 'Distribution needs to be verified. '
3321: FROM DUAL
3322: WHERE EXISTS (
3323: SELECT 'Dist Total <> Invoice Line Amount'
3324: FROM ap_invoice_lines_all AIL, ap_invoice_distributions_all D
3325: -- WHERE AIL.invoice_id = D.invoice_id
3326: WHERE AIL.invoice_id = D.invoice_id(+)
3327: AND AIL.line_number = nvl(p_invoice_line_number, AIL.line_number) --bug6661773
3328: AND AIL.invoice_id = p_invoice_id
3372: FROM ap_invoices_all ai
3373: WHERE ai.invoice_id = p_invoice_id;
3374:
3375: SELECT count(*) INTO l_dist_count
3376: FROM ap_invoice_distributions_all aid
3377: WHERE aid.invoice_id = p_invoice_id
3378: AND ((aid.line_type_lookup_code <> 'PREPAY'
3379: AND aid.prepay_tax_parent_id IS NULL)
3380: OR nvl(invoice_includes_prepay_flag,'N') = 'Y')
3677: AIL.last_update_login = FND_GLOBAL.login_id
3678: WHERE AIL.invoice_id = p_invoice_id
3679: -- Bug 6621883
3680: AND (EXISTS ( SELECT 'NOT POSTED'
3681: FROM ap_invoice_distributions_all D
3682: WHERE D.invoice_id = AIL.invoice_id
3683: AND D.invoice_line_number = AIL.line_number
3684: AND NVL(D.posted_flag, 'N') = 'N' )
3685: OR NOT EXISTS (SELECT 'DIST DOES NOT EXIST'
3682: WHERE D.invoice_id = AIL.invoice_id
3683: AND D.invoice_line_number = AIL.line_number
3684: AND NVL(D.posted_flag, 'N') = 'N' )
3685: OR NOT EXISTS (SELECT 'DIST DOES NOT EXIST'
3686: FROM ap_invoice_distributions_all D1
3687: WHERE D1.invoice_id = AIL.invoice_id
3688: AND D1.invoice_line_number = AIL.line_number
3689: AND AIL.amount IS NOT NULL
3690: )
3813:
3814:
3815: l_round_amt_exists BOOLEAN := FALSE;
3816: l_rounded_amt NUMBER;
3817: l_rounded_dist_id ap_invoice_distributions.INVOICE_DISTRIBUTION_ID%TYPE;
3818: l_debug_loc VARCHAR2(30) := 'Dist_Base_Amount_Calculation';
3819: l_curr_calling_sequence VARCHAR2(2000);
3820: l_debug_info VARCHAR2(1000);
3821: l_debug_context VARCHAR2(2000);
3838: --Added the AND clause so as to not to overwrite the
3839: --base_amounts calculated on matched distributions
3840: --which either have an IPV or ERV or both, during the
3841: --earlier call to exec_matched_variance_checks.
3842: UPDATE AP_INVOICE_DISTRIBUTIONS
3843: SET base_amount = DECODE(p_base_currency_code, p_invoice_currency_code,
3844: NULL, ap_utilities_pkg.ap_round_currency(
3845: amount * p_invoice_exchange_rate,
3846: p_base_currency_code)),
3885:
3886: -- get the existing base amount for the selected distribution
3887: select base_amount
3888: INTO l_base_amt
3889: FROM AP_INVOICE_DISTRIBUTIONS
3890: WHERE invoice_id = p_invoice_id
3891: AND invoice_line_number = p_invoice_line_number
3892: AND invoice_distribution_id = l_round_dist_id_list(i);
3893:
3898: p_rounding_amt => l_modified_dist_rounding_amt,
3899: p_next_line_rounding_amt => l_rounded_amt);
3900:
3901: -- update the calculatd base amount, rounding amount
3902: UPDATE AP_INVOICE_DISTRIBUTIONS
3903: SET base_amount = l_base_amt,
3904: rounding_amt = ABS( l_modified_dist_rounding_amt ),
3905: last_update_date = SYSDATE,
3906: last_updated_by = FND_GLOBAL.user_id,
3975: -------------------------------------------------
3976:
3977: SELECT count(*)
3978: INTO l_null_event_id
3979: FROM ap_invoice_distributions aid
3980: WHERE aid.invoice_id = P_invoice_id
3981: AND aid.accounting_event_id is NULL;
3982:
3983:
4280:
4281: CURSOR c_manual_awt_dist_segs is
4282: SELECT ap_utilities_pkg.get_auto_offsets_segments(
4283: aid.dist_code_combination_id)
4284: FROM ap_invoice_distributions aid
4285: WHERE aid.invoice_id = p_invoice_id
4286: AND aid.line_type_lookup_code = 'AWT'
4287: AND aid.awt_flag = 'M';
4288:
4293: -- in the invoice
4294: CURSOR c_non_awt_dists_segs is
4295: SELECT ap_utilities_pkg.get_auto_offsets_segments(
4296: aid.dist_code_combination_id)
4297: FROM ap_invoice_distributions_all aid, ap_invoice_lines_all ail
4298: WHERE ail.invoice_id = p_invoice_id
4299: AND ail.invoice_id = aid.invoice_id
4300: AND ail.line_number = aid.invoice_line_number
4301: AND ((aid.line_type_lookup_code not in ('AWT','PREPAY')
4388: p_calling_sequence;
4389:
4390: SELECT count(*)
4391: INTO l_matched_count
4392: FROM ap_invoice_distributions
4393: WHERE invoice_id = p_invoice_id
4394: AND po_distribution_id is not null
4395: AND line_type_lookup_code in ( 'ITEM', 'ACCRUAL', 'IPV');
4396:
4498: -- invoice has no manual withholding lines --
4499: ------------------------------------------------------------------------
4500: -- Perf bug 5058995
4501: -- Modify below SQL to go to base tables : AP_INVOICES_ALL and
4502: -- AP_INVOICE_DISTRIBUTIONS_ALL
4503: SELECT 'OK to call Withholding Routine',
4504: (AI.invoice_amount * NVL(AI.exchange_rate, 1)),
4505: AI.invoice_num
4506: INTO l_ok_to_withhold,
4508: l_invoice_num
4509: FROM ap_invoices_all AI
4510: WHERE AI.invoice_id = p_invoice_id
4511: AND EXISTS (SELECT 'At least 1 dist has an AWT Group'
4512: FROM ap_invoice_distributions_all AID1
4513: WHERE AID1.invoice_id = AI.invoice_id
4514: AND AID1.awt_group_id IS NOT NULL)
4515: AND NOT EXISTS (SELECT 'Unreleased System holds exist'
4516: FROM ap_holds AH,
4520: AND AH.hold_lookup_code <> 'AWT ERROR'
4521: AND AH.hold_lookup_code = AHC.hold_lookup_code
4522: AND AHC.user_releaseable_flag = 'N')
4523: AND NOT EXISTS (SELECT 'Manual AWT lines exist'
4524: FROM ap_invoice_distributions_all AID
4525: WHERE AID.invoice_id = AI.invoice_id
4526: AND AID.line_type_lookup_code = 'AWT'
4527: AND AID.awt_flag IN ('M', 'O'));
4528:
4527: AND AID.awt_flag IN ('M', 'O'));
4528:
4529: /* SELECT MAX(accounting_date)
4530: INTO l_withholding_date
4531: FROM ap_invoice_distributions
4532: WHERE invoice_id = p_invoice_id
4533: AND awt_group_id IS NOT NULL; */
4534: /* 5886500 */
4535: SELECT invoice_date
4576: ------------------------------------------------------------------------
4577:
4578: -- Perf bug 5058995
4579: -- Modify below SQL to go to base tables : AP_INVOICES_ALL and
4580: -- AP_INVOICE_DISTRIBUTIONS_ALL
4581: SELECT 'OK to call Withholding Routine',
4582: (AI.invoice_amount * NVL(AI.exchange_rate,1)),
4583: AI.invoice_num
4584: INTO l_ok_to_withhold,
4594: AND AH.hold_lookup_code <> 'AWT ERROR'
4595: AND AH.hold_lookup_code = AHC.hold_lookup_code
4596: AND AHC.user_releaseable_flag = 'N')
4597: AND NOT EXISTS (SELECT 'Manual AWT lines exist'
4598: FROM ap_invoice_distributions_all AID
4599: WHERE AID.invoice_id = AI.invoice_id
4600: AND AID.line_type_lookup_code = 'AWT'
4601: AND AID.awt_flag IN ('M', 'O'));
4602:
4601: AND AID.awt_flag IN ('M', 'O'));
4602:
4603: SELECT MAX(accounting_date)
4604: INTO l_withholding_date
4605: FROM ap_invoice_distributions
4606: WHERE invoice_id = p_invoice_id;
4607:
4608: EXCEPTION
4609: WHEN NO_DATA_FOUND THEN
4726: l_debug_info := 'Set selected dists match_status_flag to tested';
4727: Print_Debug(l_debug_loc, l_debug_info);
4728: ------------------------------------------------------------
4729:
4730: UPDATE ap_invoice_distributions D
4731: SET match_status_flag = 'T',
4732: last_update_date = SYSDATE,
4733: last_updated_by = p_user_id,
4734: program_application_id = decode(fnd_global.prog_appl_id,
4762: --Bug6963908
4763:
4764:
4765: AP_DBI_PKG.Maintain_DBI_Summary
4766: (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
4767: p_operation => 'U',
4768: p_key_value1 => p_invoice_id,
4769: p_key_value_list => l_dbi_key_value_list1,
4770: p_calling_sequence => l_curr_calling_sequence);
4776:
4777: -- BUG 4340061
4778: -- For the AWT lines we have encumbered_flag set to N in invoice-dist.
4779:
4780: UPDATE ap_invoice_distributions D
4781: SET match_status_flag = 'A',
4782: packet_id = ''
4783: WHERE match_status_flag = 'T'
4784: AND D.invoice_id = p_invoice_id
5738: -------------------------------------------
5739:
5740: -- Fix for Bug #5107865. Replaced bulk update with single update for
5741: -- performance reasons.
5742: UPDATE ap_invoice_distributions_all id1
5743: SET (id1.total_dist_amount,
5744: id1.total_dist_base_amount) = (SELECT SUM(NVL(id2.amount,0)),
5745: SUM(NVL(id2.base_amount,0))
5746: FROM ap_invoice_distributions_all id2
5742: UPDATE ap_invoice_distributions_all id1
5743: SET (id1.total_dist_amount,
5744: id1.total_dist_base_amount) = (SELECT SUM(NVL(id2.amount,0)),
5745: SUM(NVL(id2.base_amount,0))
5746: FROM ap_invoice_distributions_all id2
5747: WHERE id2.invoice_distribution_id =
5748: id1.invoice_distribution_id
5749: OR id2.related_id =
5750: id1.invoice_distribution_id)
5803: l_pay_curr_code pay_currency,
5804: aid.last_updated_by user_id,
5805: aid.last_update_login last_update_login,
5806: l_curr_calling_sequence calling_sequence
5807: FROM ap_invoice_distributions aid,
5808: ap_invoice_lines ail
5809: WHERE aid.invoice_id = p_invoice_id
5810: AND ail.invoice_id = aid.invoice_id
5811: AND ail.line_number = aid.invoice_line_number
5814: AND aid.amount <> 0
5815: AND (aid.line_type_lookup_code = 'PREPAY'
5816: OR aid.charge_applicable_to_dist_id in
5817: (SELECT invoice_distribution_id
5818: FROM ap_invoice_distributions
5819: WHERE line_type_lookup_code = 'PREPAY'
5820: AND invoice_id = p_invoice_id))
5821: AND NVL(aid.invoice_includes_prepay_flag, 'N') <> 'Y';
5822:
5997: --bugfix:5638734
5998: -- Get the exculusive tax amount for the prepay appln line.
5999: SELECT sum(aid.amount)
6000: INTO l_prepay_excl_tax_amt
6001: FROM ap_invoice_lines_all ail,ap_invoice_distributions_all aid
6002: WHERE ail.line_type_lookup_code='TAX'
6003: AND ail.invoice_id=p_invoice_id
6004: AND aid.invoice_id=ail.invoice_id
6005: AND aid.invoice_line_number=ail.line_number
6063: SELECT sum(nvl(amount,0)),
6064: sum(nvl(base_amount,0))
6065: INTO l_tmp_invoice_amount,
6066: l_tmp_base_amount
6067: FROM ap_invoice_distributions_all
6068: WHERE invoice_id = p_invoice_id
6069: AND ((line_type_lookup_code NOT IN ('PREPAY', 'AWT')
6070: AND prepay_distribution_id IS NULL)
6071: OR nvl(invoice_includes_prepay_flag,'N') = 'Y');
6367: PROCEDURE Manual_Withhold_Tax(p_invoice_id IN NUMBER,
6368: p_last_updated_by IN NUMBER,
6369: p_last_update_login IN NUMBER,
6370: p_calling_sequence IN VARCHAR2) IS
6371: l_manual_awt_amount ap_invoice_distributions.amount%TYPE :=0;
6372: l_payment_cross_rate ap_invoices.payment_cross_rate%TYPE;
6373: l_pay_curr_code ap_invoices.payment_currency_code%TYPE;
6374: l_num_payments NUMBER := 0;
6375: l_invoice_type ap_invoices.invoice_type_lookup_code%TYPE;--1724924
6390: -- BUG 4340061 : For the lines which have been already been validated but the invoice has been placed on hold.
6391:
6392: SELECT sum( nvl(amount, 0) )
6393: INTO l_manual_awt_amount
6394: FROM ap_invoice_distributions
6395: WHERE invoice_id = p_invoice_id
6396: AND nvl(match_status_flag, 'N') in ('N','T') -- BUG 4340061
6397: AND line_type_lookup_code = 'AWT'
6398: AND awt_flag in ('M', 'O');
6488: CURSOR Update_payment_schedule IS
6489: SELECT payment_num,gross_amount,amount_remaining
6490: FROM ap_payment_schedules
6491: WHERE invoice_id=p_invoice_id;
6492: l_automatic_awt_amount ap_invoice_distributions.amount%TYPE :=0;
6493: l_payment_cross_rate ap_invoices.payment_cross_rate%TYPE;
6494: l_pay_curr_code ap_invoices.payment_currency_code%TYPE;
6495: l_num_payments NUMBER := 0;
6496: l_invoice_type ap_invoices.invoice_type_lookup_code%TYPE;
6507:
6508:
6509: SELECT (0 - sum(nvl(amount,0)))
6510: INTO l_automatic_awt_amount
6511: FROM ap_invoice_distributions
6512: WHERE invoice_id = p_invoice_id
6513: AND line_type_lookup_code = 'AWT'
6514: AND awt_flag = 'A';
6515:
6761:
6762: l_debug_info VARCHAR2(1000);
6763: l_curr_calling_sequence VARCHAR2(2000);
6764:
6765: TYPE Invoice_Dists_Tab_Type IS TABLE OF ap_invoice_distributions_all%ROWTYPE;
6766: l_dist_tab Invoice_Dists_Tab_Type := Invoice_Dists_Tab_Type();
6767:
6768: TYPE Expense_Report_Dists_Rec IS RECORD (
6769: Org_id ap_exp_report_dists_all.org_id%TYPE,
6898: l_dist_tab(i).batch_id := p_batch_id;
6899: l_dist_tab(i).invoice_id := p_invoice_line_rec.invoice_id;
6900: l_dist_tab(i).invoice_line_number := p_invoice_line_rec.line_number;
6901:
6902: SELECT ap_invoice_distributions_s.nextval
6903: INTO l_dist_tab(i).invoice_distribution_id
6904: FROM DUAL;
6905:
6906: l_dist_tab(i).line_type_lookup_code := 'ITEM';
6973:
6974: END IF; /* If l_exp_report_dists_tab.count > 0 */
6975:
6976: -------------------------------------------
6977: l_debug_info := 'Bulk Insert into ap_invoice_distributions';
6978: Print_Debug(l_api_name, l_debug_info);
6979: -------------------------------------------
6980: IF (nvl(l_dist_tab.count,0)<>0) THEN
6981:
6979: -------------------------------------------
6980: IF (nvl(l_dist_tab.count,0)<>0) THEN
6981:
6982: FORALL j IN l_dist_tab.first .. l_dist_tab.last
6983: INSERT INTO ap_invoice_distributions
6984: VALUES l_dist_tab(j);
6985:
6986: FOR j IN l_dist_tab.first .. l_dist_tab.last
6987: LOOP
7438: ' AND NOT (AI.PAYMENT_STATUS_FLAG = ''Y'' AND '||
7439: ' AI.HISTORICAL_FLAG = ''Y'') '||
7440: ' AND ((( ( EXISTS '||
7441: ' (SELECT 1 '||
7442: ' FROM AP_INVOICE_DISTRIBUTIONS D '||
7443: ' WHERE D.INVOICE_ID = AI.INVOICE_ID '||
7444: ' AND NVL(D.MATCH_STATUS_FLAG, ''N'') <> ''A''' ||
7445: ' ) '||
7446: ' OR EXISTS '||
7463: ' (AI.FORCE_REVALIDATION_FLAG = ''Y'')'||
7464: ' ) '||
7465: ' AND NOT EXISTS '||
7466: ' (SELECT ''Cancelled distributions'' '||
7467: ' FROM AP_INVOICE_DISTRIBUTIONS_ALL D2 '||
7468: ' WHERE D2.INVOICE_ID = AI.INVOICE_ID '||
7469: ' AND D2.CANCELLATION_FLAG = ''Y'''||
7470: ' )'||
7471: ' ) '||
7478: ' AND NVL(AIL.CANCELLED_FLAG, ''N'') <> ''Y'' '||
7479: ' AND NOT EXISTS '||
7480: ' (SELECT /*+ NO_UNNEST */ '||
7481: ' ''distributed line'' '||
7482: ' FROM AP_INVOICE_DISTRIBUTIONS_ALL D5 '||
7483: ' WHERE D5.INVOICE_ID = AIL.INVOICE_ID '||
7484: ' AND D5.INVOICE_LINE_NUMBER = AIL.LINE_NUMBER))) ' ;
7485: IF P_org_id IS NOT NULL THEN
7486: l_sql_stmt := l_sql_stmt|| 'AND AI.org_id = ' || l_org_id || ' ' ;