[Home] [Help]
1477: IF (l_base_currency_code <> l_invoice_currency_code) THEN
1478:
1479: SELECT SUM(amount)
1480: INTO l_dist_total
1481: FROM ap_invoice_distributions
1482: WHERE invoice_id = l_invoice_id
1483: AND ( (line_type_lookup_code NOT IN ('PREPAY','AWT') AND
1484: prepay_tax_parent_id IS NULL) OR
1485: (line_type_lookup_code = 'PREPAY' AND
1520: ---------------------------------------------------------------
1521:
1522: SELECT COUNT('X')
1523: INTO l_item_count
1524: FROM ap_invoice_distributions
1525: WHERE invoice_id = l_invoice_id AND
1526: line_type_lookup_code = 'ITEM';
1527:
1528: IF (l_dist_total = l_inv_amount) THEN
1528: IF (l_dist_total = l_inv_amount) THEN
1529:
1530: SELECT SUM(base_amount)
1531: INTO l_base_dist_total
1532: FROM ap_invoice_distributions
1533: WHERE invoice_id = l_invoice_id
1534: AND (
1535: (line_type_lookup_code NOT IN ('PREPAY','AWT') AND
1536: prepay_tax_parent_id IS NULL) OR
1560: l_debug_info := 'Adjust for rounding';
1561: ---------------------------------------------------------------
1562: IF (l_item_count > 0) THEN
1563: --Update ITEM Dists
1564: UPDATE ap_invoice_distributions
1565: SET base_amount = base_amount - (l_base_dist_total - l_inv_base_amount)
1566: WHERE invoice_id = l_invoice_id
1567: AND invoice_distribution_id = (
1568: SELECT MAX(AID1.invoice_distribution_id)
1565: SET base_amount = base_amount - (l_base_dist_total - l_inv_base_amount)
1566: WHERE invoice_id = l_invoice_id
1567: AND invoice_distribution_id = (
1568: SELECT MAX(AID1.invoice_distribution_id)
1569: FROM ap_invoice_distributions AID1
1570: WHERE AID1.invoice_id = l_invoice_id
1571: AND AID1.line_type_lookup_code = 'ITEM'
1572: /* Bug 3784909. Folowing two lines Added */
1573: AND NVL(AID1.reversal_flag, 'N') <> 'Y'
1573: AND NVL(AID1.reversal_flag, 'N') <> 'Y'
1574: AND NVL(AID1.posted_flag, 'N') = 'N'
1575: AND ABS(AID1.amount) = (
1576: SELECT MAX(ABS(AID2.amount))
1577: FROM ap_invoice_distributions AID2
1578: WHERE AID2.invoice_id = l_invoice_id
1579: AND AID2.line_type_lookup_code = 'ITEM'
1580: -- Bug 3784909. Folowing two lines Added
1581: AND NVL(AID2.reversal_flag, 'N') <> 'Y'
1581: AND NVL(AID2.reversal_flag, 'N') <> 'Y'
1582: AND NVL(AID2.posted_flag, 'N') = 'N'));
1583: ELSE
1584: -- Update FREIGHT or MISC Dists
1585: UPDATE ap_invoice_distributions
1586: SET base_amount = base_amount - (l_base_dist_total - l_inv_base_amount)
1587: WHERE invoice_id = l_invoice_id
1588: AND invoice_distribution_id = (
1589: SELECT MAX(AID3.invoice_distribution_id)
1586: SET base_amount = base_amount - (l_base_dist_total - l_inv_base_amount)
1587: WHERE invoice_id = l_invoice_id
1588: AND invoice_distribution_id = (
1589: SELECT MAX(AID3.invoice_distribution_id)
1590: FROM ap_invoice_distributions AID3
1591: WHERE AID3.invoice_id = l_invoice_id
1592: AND AID3.line_type_lookup_code
1593: IN ('FREIGHT','MISCELLANEOUS')
1594: AND
1596: NVL(AID3.reversal_flag, 'N') = 'N'
1597: AND NVL(AID3.posted_flag, 'N') = 'N'
1598: AND ABS(AID3.amount) = (
1599: SELECT MAX(ABS(AID4.amount))
1600: FROM ap_invoice_distributions AID4
1601: WHERE AID4.invoice_id = l_invoice_id
1602: AND AID4.line_type_lookup_code
1603: IN('FREIGHT','MISCELLANEOUS')
1604: --Bug 3784909. Folowing two lines Added
2002: -- the Prepayment Application Distributions (APAD)
2003: --
2004: SELECT count(*)
2005: INTO l_prepay_dist_count
2006: FROM ap_invoice_distributions_all
2007: WHERE Invoice_ID = l_invoice_id
2008: AND Line_Type_Lookup_Code = 'PREPAY'
2009: --AND Accounting_Event_ID IS NULL;
2010: AND NVL(Posted_Flag, 'N') <> 'Y'
2062: END IF;
2063: -- Bug 6681580
2064: select count(*)
2065: INTO l_encumbrance_exists
2066: FROM ap_invoice_distributions aid
2067: WHERE nvl(aid.encumbered_flag,'N') not in ('N','R') ----added check for 'R' due to bug 7264524
2068: AND aid.invoice_id = l_invoice_id;
2069:
2070: -- Bug 6681580
2125: /* SELECT 'Y'
2126: INTO l_lcm_used
2127: FROM DUAL
2128: WHERE EXISTS
2129: (SELECT 1 FROM AP_INVOICE_DISTRIBUTIONS aid, RCV_TRANSACTIONS rt
2130: WHERE aid.invoice_id = l_invoice_id
2131: AND aid.rcv_transaction_id = rt.transaction_id
2132: AND rt.lcm_shipment_line_id IS NOT NULL
2133: AND aid.match_status_flag = 'S'); */
2136: INTO l_lcm_used
2137: FROM DUAL
2138: WHERE EXISTS
2139: (SELECT 1
2140: FROM AP_INVOICE_DISTRIBUTIONS_ALL aid,
2141: RCV_TRANSACTIONS rt
2142: WHERE aid.invoice_id = l_invoice_id
2143: AND aid.rcv_transaction_id = rt.transaction_id
2144: AND rt.lcm_shipment_line_id IS NOT NULL
2146:
2147: UNION ALL
2148:
2149: SELECT 1
2150: FROM AP_INVOICE_DISTRIBUTIONS_ALL aid,
2151: RCV_TRANSACTIONS rt,
2152: AP_INVOICE_DISTRIBUTIONS_ALL aid2
2153: WHERE aid.invoice_id = l_invoice_id
2154: AND aid.corrected_invoice_dist_id = aid2.invoice_distribution_id
2148:
2149: SELECT 1
2150: FROM AP_INVOICE_DISTRIBUTIONS_ALL aid,
2151: RCV_TRANSACTIONS rt,
2152: AP_INVOICE_DISTRIBUTIONS_ALL aid2
2153: WHERE aid.invoice_id = l_invoice_id
2154: AND aid.corrected_invoice_dist_id = aid2.invoice_distribution_id
2155: AND aid2.rcv_transaction_id = rt.transaction_id
2156: AND rt.lcm_shipment_line_id IS NOT NULL
2483: END IF;
2484: END IF;
2485: END IF;
2486:
2487: AP_INVOICE_DISTRIBUTIONS_PKG.Make_Distributions_Permanent
2488: (P_Invoice_Id => p_invoice_id,
2489: P_Invoice_Line_Number => NULL,
2490: P_Calling_Sequence => 'Invoice Validation'); --Bug6653070
2491:
2873: IF (p_run_option = 'New') THEN
2874:
2875: SELECT count(*)
2876: INTO l_unapproved_dist_exists
2877: FROM ap_invoice_distributions_all
2878: WHERE invoice_id = p_invoice_id
2879: AND (nvl(match_status_flag, 'N')) = 'N'
2880: AND rownum = 1;
2881:
2886: FROM ap_invoice_lines_all L
2887: WHERE L.invoice_id = p_invoice_id
2888: AND L.amount <>
2889: (SELECT NVL(SUM(NVL(aid.amount,0)),0)
2890: FROM ap_invoice_distributions_all aid
2891: WHERE aid.invoice_id = L.invoice_id
2892: AND aid.invoice_line_number = L.line_number);
2893: END;
2894:
2973: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2974: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_debug_loc,l_debug_info);
2975: END IF;
2976:
2977: UPDATE ap_invoice_distributions_all D
2978: SET match_status_flag = 'S'
2979: WHERE NVL(match_status_flag, 'N') = 'N'
2980: AND NVL(D.posted_flag, 'N' ) = 'N' -- Bug 9777752
2981: AND D.invoice_id = p_invoice_id
3002: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3003: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_debug_loc,l_debug_info);
3004: END IF;
3005:
3006: UPDATE ap_invoice_distributions_all D
3007: SET match_status_flag = 'S'
3008: WHERE NVL(match_status_flag, '!') <> 'A'
3009: AND NVL(D.posted_flag, 'N' ) = 'N' -- Bug 9777752
3010: AND D.invoice_id = p_invoice_id;
3424: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3425: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_debug_loc,l_debug_info);
3426: END IF;
3427:
3428: l_success := AP_INVOICE_DISTRIBUTIONS_PKG.Insert_Charge_From_Alloc(
3429: X_invoice_id => p_inv_line_rec.invoice_id,
3430: X_line_number => p_inv_line_rec.line_number,
3431: X_Generate_Permanent => p_generate_permanent,
3432: X_Validate_Info => TRUE,
3491: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3492: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_debug_loc,l_debug_info);
3493: END IF;
3494:
3495: l_success := AP_INVOICE_DISTRIBUTIONS_PKG.Insert_AWT_Dist_From_Line(
3496: X_batch_id => p_batch_id,
3497: X_invoice_id => p_inv_line_rec.invoice_id,
3498: X_invoice_date => p_invoice_date,
3499: X_vendor_id => p_vendor_id,
3542: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3543: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_debug_loc,l_debug_info);
3544: END IF;
3545:
3546: l_success := AP_INVOICE_DISTRIBUTIONS_PKG.Insert_Single_Dist_From_Line(
3547: X_batch_id => p_batch_id,
3548: X_invoice_id => p_inv_line_rec.invoice_id,
3549: X_invoice_date => p_invoice_date,
3550: X_vendor_id => p_vendor_id,
4050: p_calling_sequence IN VARCHAR2) IS
4051:
4052: CURSOR Invalid_Dist_Acct_Cur IS
4053: SELECT D.dist_code_combination_id, D.accounting_date
4054: FROM ap_invoice_distributions D
4055: WHERE D.invoice_id = p_invoice_id
4056: AND D.posted_flag||'' in ('N', 'P')
4057: AND ((EXISTS (select 'x'
4058: from gl_code_combinations C
4083: AND c_acct_date BETWEEN NVL(a.start_date_active, c_acct_date)
4084: AND NVL(a.end_date_active, c_acct_date)
4085: );
4086:
4087: l_ccid AP_INVOICE_DISTRIBUTIONS_ALL.dist_code_combination_id%TYPE;
4088: l_accounting_date AP_INVOICE_DISTRIBUTIONS_ALL.accounting_date%TYPE;
4089: l_alt_exists VARCHAR2(50) := 'N';
4090: l_invalid_dist_ccid_exists VARCHAR2(1) := 'N';
4091: l_test_var VARCHAR2(50);
4084: AND NVL(a.end_date_active, c_acct_date)
4085: );
4086:
4087: l_ccid AP_INVOICE_DISTRIBUTIONS_ALL.dist_code_combination_id%TYPE;
4088: l_accounting_date AP_INVOICE_DISTRIBUTIONS_ALL.accounting_date%TYPE;
4089: l_alt_exists VARCHAR2(50) := 'N';
4090: l_invalid_dist_ccid_exists VARCHAR2(1) := 'N';
4091: l_test_var VARCHAR2(50);
4092: l_debug_loc VARCHAR2(30) := 'Check_Invalid_Dist';
4216:
4217: CURSOR PO_Required_Cur IS
4218: -- Perf bug 5058995
4219: -- Modify below SQL to go to base tables : AP_INVOICES_ALL,
4220: -- AP_INVOICE_DISTRIBUTIONS_ALL and
4221: -- AP_SUPPLIER_SITES(instead of po_vendor_sites)
4222: /* Added the Hint index(apd AP_INVOICE_DISTRIBUTIONS_U1) for bug#7270053 */
4223: SELECT 'PO REQUIRED'
4224: FROM ap_invoices_all api, ap_supplier_sites pov
4218: -- Perf bug 5058995
4219: -- Modify below SQL to go to base tables : AP_INVOICES_ALL,
4220: -- AP_INVOICE_DISTRIBUTIONS_ALL and
4221: -- AP_SUPPLIER_SITES(instead of po_vendor_sites)
4222: /* Added the Hint index(apd AP_INVOICE_DISTRIBUTIONS_U1) for bug#7270053 */
4223: SELECT 'PO REQUIRED'
4224: FROM ap_invoices_all api, ap_supplier_sites pov
4225: WHERE EXISTS (select /*+ index(apd AP_INVOICE_DISTRIBUTIONS_U1) */ 'X'
4226: from ap_invoice_distributions_all apd
4221: -- AP_SUPPLIER_SITES(instead of po_vendor_sites)
4222: /* Added the Hint index(apd AP_INVOICE_DISTRIBUTIONS_U1) for bug#7270053 */
4223: SELECT 'PO REQUIRED'
4224: FROM ap_invoices_all api, ap_supplier_sites pov
4225: WHERE EXISTS (select /*+ index(apd AP_INVOICE_DISTRIBUTIONS_U1) */ 'X'
4226: from ap_invoice_distributions_all apd
4227: where apd.invoice_id = api.invoice_id
4228: and apd.line_type_lookup_code in ( 'ITEM', 'ACCRUAL')
4229: and apd.po_distribution_id is null
4222: /* Added the Hint index(apd AP_INVOICE_DISTRIBUTIONS_U1) for bug#7270053 */
4223: SELECT 'PO REQUIRED'
4224: FROM ap_invoices_all api, ap_supplier_sites pov
4225: WHERE EXISTS (select /*+ index(apd AP_INVOICE_DISTRIBUTIONS_U1) */ 'X'
4226: from ap_invoice_distributions_all apd
4227: where apd.invoice_id = api.invoice_id
4228: and apd.line_type_lookup_code in ( 'ITEM', 'ACCRUAL')
4229: and apd.po_distribution_id is null
4230: and apd.pa_addition_flag <> 'T'
4546: SELECT 'Distribution needs to be verified. '
4547: FROM DUAL
4548: WHERE EXISTS (
4549: SELECT 'Dist Total <> Invoice Line Amount'
4550: FROM ap_invoice_lines_all AIL, ap_invoice_distributions_all D
4551: -- WHERE AIL.invoice_id = D.invoice_id
4552: WHERE AIL.invoice_id = D.invoice_id(+)
4553: AND AIL.line_number = nvl(p_invoice_line_number, AIL.line_number) --bug6661773
4554: AND AIL.invoice_id = p_invoice_id
4598: FROM ap_invoices_all ai
4599: WHERE ai.invoice_id = p_invoice_id;
4600:
4601: SELECT count(*) INTO l_dist_count
4602: FROM ap_invoice_distributions_all aid
4603: WHERE aid.invoice_id = p_invoice_id
4604: AND ((aid.line_type_lookup_code <> 'PREPAY'
4605: AND aid.prepay_tax_parent_id IS NULL)
4606: OR nvl(invoice_includes_prepay_flag,'N') = 'Y')
5075: WHERE AIL.invoice_id = p_invoice_id
5076: AND AIL.LINE_TYPE_LOOKUP_CODE not in ('TAX', 'AWT') -- bug 9582952 --bug16090813
5077: -- Bug 6621883
5078: AND (EXISTS ( SELECT 'NOT POSTED'
5079: FROM ap_invoice_distributions_all D
5080: WHERE D.invoice_id = AIL.invoice_id
5081: AND D.invoice_line_number = AIL.line_number
5082: AND NVL(D.posted_flag, 'N') = 'N' )
5083: OR NOT EXISTS (SELECT 'DIST DOES NOT EXIST'
5080: WHERE D.invoice_id = AIL.invoice_id
5081: AND D.invoice_line_number = AIL.line_number
5082: AND NVL(D.posted_flag, 'N') = 'N' )
5083: OR NOT EXISTS (SELECT 'DIST DOES NOT EXIST'
5084: FROM ap_invoice_distributions_all D1
5085: WHERE D1.invoice_id = AIL.invoice_id
5086: AND D1.invoice_line_number = AIL.line_number
5087: AND AIL.amount IS NOT NULL
5088: )
5228:
5229:
5230: l_round_amt_exists BOOLEAN := FALSE;
5231: l_rounded_amt NUMBER;
5232: l_rounded_dist_id ap_invoice_distributions.INVOICE_DISTRIBUTION_ID%TYPE;
5233: l_debug_loc VARCHAR2(30) := 'Dist_Base_Amount_Calculation';
5234: l_curr_calling_sequence VARCHAR2(2000);
5235: l_debug_info VARCHAR2(1000);
5236: l_debug_context VARCHAR2(2000);
5261: --Added the AND clause so as to not to overwrite the
5262: --base_amounts calculated on matched distributions
5263: --which either have an IPV or ERV or both, during the
5264: --earlier call to exec_matched_variance_checks.
5265: UPDATE AP_INVOICE_DISTRIBUTIONS
5266: SET base_amount = DECODE(p_base_currency_code, p_invoice_currency_code,
5267: NULL, ap_utilities_pkg.ap_round_currency(
5268: amount * p_invoice_exchange_rate,
5269: p_base_currency_code)),
5310:
5311: -- get the existing base amount for the selected distribution
5312: select base_amount, amount -- Bug 12686348: Added column amount
5313: INTO l_base_amt, l_amt -- Bug 12686348: Added column amount
5314: FROM AP_INVOICE_DISTRIBUTIONS
5315: WHERE invoice_id = p_invoice_id
5316: AND invoice_line_number = p_invoice_line_number
5317: AND invoice_distribution_id = l_round_dist_id_list(i);
5318:
5324: p_next_line_rounding_amt => l_rounded_amt,
5325: p_amount => l_amt); -- Bug 12686348
5326:
5327: -- update the calculatd base amount, rounding amount
5328: UPDATE AP_INVOICE_DISTRIBUTIONS
5329: SET base_amount = l_base_amt,
5330: rounding_amt = ABS( l_modified_dist_rounding_amt ),
5331: last_update_date = SYSDATE,
5332: last_updated_by = FND_GLOBAL.user_id,
5410: -------------------------------------------------
5411:
5412: SELECT count(*)
5413: INTO l_null_event_id
5414: FROM ap_invoice_distributions aid
5415: WHERE aid.invoice_id = P_invoice_id
5416: AND aid.accounting_event_id is NULL;
5417:
5418: if(l_null_event_id = 0) then
5782:
5783: CURSOR c_manual_awt_dist_segs is
5784: SELECT ap_utilities_pkg.get_auto_offsets_segments(
5785: aid.dist_code_combination_id)
5786: FROM ap_invoice_distributions aid
5787: WHERE aid.invoice_id = p_invoice_id
5788: AND aid.line_type_lookup_code = 'AWT'
5789: AND aid.awt_flag = 'M';
5790:
5795: -- in the invoice
5796: CURSOR c_non_awt_dists_segs is
5797: SELECT ap_utilities_pkg.get_auto_offsets_segments(
5798: aid.dist_code_combination_id)
5799: FROM ap_invoice_distributions_all aid, ap_invoice_lines_all ail
5800: WHERE ail.invoice_id = p_invoice_id
5801: AND ail.invoice_id = aid.invoice_id
5802: AND ail.line_number = aid.invoice_line_number
5803: AND ((aid.line_type_lookup_code not in ('AWT','PREPAY')
5890: p_calling_sequence;
5891:
5892: SELECT count(*)
5893: INTO l_matched_count
5894: FROM ap_invoice_distributions
5895: WHERE invoice_id = p_invoice_id
5896: AND po_distribution_id is not null
5897: AND ( line_type_lookup_code in ( 'ITEM', 'ACCRUAL', 'IPV')
5898: OR /*Bug 9242891: Added below conditions for considering retainage release inv and return matched status*/
6005: -- invoice has no manual withholding lines --
6006: ------------------------------------------------------------------------
6007: -- Perf bug 5058995
6008: -- Modify below SQL to go to base tables : AP_INVOICES_ALL and
6009: -- AP_INVOICE_DISTRIBUTIONS_ALL
6010: SELECT 'OK to call Withholding Routine',
6011: (AI.invoice_amount * NVL(AI.exchange_rate, 1)),
6012: AI.invoice_num
6013: INTO l_ok_to_withhold,
6015: l_invoice_num
6016: FROM ap_invoices_all AI
6017: WHERE AI.invoice_id = p_invoice_id
6018: AND EXISTS (SELECT 'At least 1 dist has an AWT Group'
6019: FROM ap_invoice_distributions_all AID1
6020: WHERE AID1.invoice_id = AI.invoice_id
6021: AND AID1.awt_group_id IS NOT NULL)
6022: AND NOT EXISTS (SELECT 'Unreleased System holds exist'
6023: FROM ap_holds AH,
6027: AND AH.hold_lookup_code <> 'AWT ERROR'
6028: AND AH.hold_lookup_code = AHC.hold_lookup_code
6029: AND AHC.user_releaseable_flag = 'N')
6030: AND NOT EXISTS (SELECT 'Manual AWT lines exist'
6031: FROM ap_invoice_distributions_all AID
6032: WHERE AID.invoice_id = AI.invoice_id
6033: AND AID.line_type_lookup_code = 'AWT'
6034: AND AID.awt_flag IN ('M', 'O'));
6035:
6034: AND AID.awt_flag IN ('M', 'O'));
6035:
6036: /* SELECT MAX(accounting_date)
6037: INTO l_withholding_date
6038: FROM ap_invoice_distributions
6039: WHERE invoice_id = p_invoice_id
6040: AND awt_group_id IS NOT NULL; */
6041: /* 5886500 */
6042: --bug 9293773
6099: ------------------------------------------------------------------------
6100:
6101: -- Perf bug 5058995
6102: -- Modify below SQL to go to base tables : AP_INVOICES_ALL and
6103: -- AP_INVOICE_DISTRIBUTIONS_ALL
6104: SELECT 'OK to call Withholding Routine',
6105: (AI.invoice_amount * NVL(AI.exchange_rate,1)),
6106: AI.invoice_num
6107: INTO l_ok_to_withhold,
6117: AND AH.hold_lookup_code <> 'AWT ERROR'
6118: AND AH.hold_lookup_code = AHC.hold_lookup_code
6119: AND AHC.user_releaseable_flag = 'N')
6120: AND NOT EXISTS (SELECT 'Manual AWT lines exist'
6121: FROM ap_invoice_distributions_all AID
6122: WHERE AID.invoice_id = AI.invoice_id
6123: AND AID.line_type_lookup_code = 'AWT'
6124: AND AID.awt_flag IN ('M', 'O'));
6125:
6124: AND AID.awt_flag IN ('M', 'O'));
6125:
6126: SELECT MAX(accounting_date)
6127: INTO l_withholding_date
6128: FROM ap_invoice_distributions
6129: WHERE invoice_id = p_invoice_id;
6130:
6131: EXCEPTION
6132: WHEN NO_DATA_FOUND THEN
6257: END IF;
6258: ------------------------------------------------------------
6259: --Used the base table insead of secured synonym bug 10171820
6260:
6261: UPDATE ap_invoice_distributions_all D
6262: SET match_status_flag = 'T',
6263: last_update_date = SYSDATE,
6264: last_updated_by = p_user_id,
6265: program_application_id = decode(fnd_global.prog_appl_id,
6293: --Bug6963908
6294:
6295:
6296: AP_DBI_PKG.Maintain_DBI_Summary
6297: (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
6298: p_operation => 'U',
6299: p_key_value1 => p_invoice_id,
6300: p_key_value_list => l_dbi_key_value_list1,
6301: p_calling_sequence => l_curr_calling_sequence);
6314:
6315: -- BUG 4340061
6316: -- For the AWT lines we have encumbered_flag set to N in invoice-dist.
6317: --Used the base table insead of secured synonym bug 10171820
6318: UPDATE ap_invoice_distributions_all D
6319: SET match_status_flag = 'A',
6320: packet_id = ''
6321: WHERE match_status_flag = 'T'
6322: AND D.invoice_id = p_invoice_id
7634: -------------------------------------------
7635:
7636: -- Fix for Bug #5107865. Replaced bulk update with single update for
7637: -- performance reasons.
7638: UPDATE ap_invoice_distributions_all id1
7639: SET (id1.total_dist_amount,
7640: id1.total_dist_base_amount) = (SELECT SUM(NVL(id2.amount,0)),
7641: SUM(NVL(id2.base_amount,0))
7642: FROM ap_invoice_distributions_all id2
7638: UPDATE ap_invoice_distributions_all id1
7639: SET (id1.total_dist_amount,
7640: id1.total_dist_base_amount) = (SELECT SUM(NVL(id2.amount,0)),
7641: SUM(NVL(id2.base_amount,0))
7642: FROM ap_invoice_distributions_all id2
7643: WHERE id2.invoice_distribution_id =
7644: id1.invoice_distribution_id
7645: OR id2.related_id =
7646: id1.invoice_distribution_id)
7706: l_pay_curr_code pay_currency,
7707: aid.last_updated_by user_id,
7708: aid.last_update_login last_update_login,
7709: l_curr_calling_sequence calling_sequence
7710: FROM ap_invoice_distributions aid,
7711: ap_invoice_lines ail
7712: WHERE aid.invoice_id = p_invoice_id
7713: AND ail.invoice_id = aid.invoice_id
7714: AND ail.line_number = aid.invoice_line_number
7719: AND aid.amount <> 0
7720: AND (aid.line_type_lookup_code = 'PREPAY'
7721: OR aid.charge_applicable_to_dist_id in
7722: (SELECT invoice_distribution_id
7723: FROM ap_invoice_distributions
7724: WHERE line_type_lookup_code = 'PREPAY'
7725: AND invoice_id = p_invoice_id))
7726: AND NVL(aid.invoice_includes_prepay_flag, 'N') <> 'Y';
7727:
7778: l_debug_info := 'Check if Prepayment Application Exists';
7779: ------------------------------------------------------------
7780: BEGIN
7781: --Bug8340784
7782: -- Query table ap_invoice_distributions instead of ap_invoice_lines
7783: -- This will include recoupment amount applied as prepayment
7784: SELECT 'Y'
7785: INTO l_prepayment_app_exists
7786: FROM ap_invoice_distributions aid
7782: -- Query table ap_invoice_distributions instead of ap_invoice_lines
7783: -- This will include recoupment amount applied as prepayment
7784: SELECT 'Y'
7785: INTO l_prepayment_app_exists
7786: FROM ap_invoice_distributions aid
7787: WHERE aid.invoice_id = p_invoice_id
7788: AND aid.prepay_distribution_id is not null
7789: AND rownum < 2;
7790: --End of Bug8340784
7938: --bugfix:5638734
7939: -- Get the exculusive tax amount for the prepay appln line.
7940: SELECT sum(aid.amount)
7941: INTO l_prepay_excl_tax_amt
7942: FROM ap_invoice_lines_all ail,ap_invoice_distributions_all aid
7943: WHERE ail.line_type_lookup_code='TAX'
7944: AND ail.invoice_id=p_invoice_id
7945: AND aid.invoice_id=ail.invoice_id
7946: AND aid.invoice_line_number=ail.line_number
8013: SELECT sum(amount),
8014: sum(base_amount)
8015: INTO l_tmp_invoice_amount,
8016: l_tmp_base_amount
8017: FROM ap_invoice_distributions_all
8018: WHERE invoice_id = p_invoice_id
8019: AND ((line_type_lookup_code NOT IN ('PREPAY', 'AWT')
8020: AND prepay_distribution_id IS NULL)
8021: OR nvl(invoice_includes_prepay_flag,'N') = 'Y');
8344: PROCEDURE Manual_Withhold_Tax(p_invoice_id IN NUMBER,
8345: p_last_updated_by IN NUMBER,
8346: p_last_update_login IN NUMBER,
8347: p_calling_sequence IN VARCHAR2) IS
8348: l_manual_awt_amount ap_invoice_distributions.amount%TYPE :=0;
8349: l_payment_cross_rate ap_invoices.payment_cross_rate%TYPE;
8350: l_pay_curr_code ap_invoices.payment_currency_code%TYPE;
8351: l_num_payments NUMBER := 0;
8352: l_invoice_type ap_invoices.invoice_type_lookup_code%TYPE;--1724924
8368:
8369: --Commented match_status_flag condition for bug#8947048
8370: SELECT sum( nvl(amount, 0) )
8371: INTO l_manual_awt_amount
8372: FROM ap_invoice_distributions
8373: WHERE invoice_id = p_invoice_id
8374: -- AND nvl(match_status_flag, 'N') in ('N','T') -- BUG 4340061
8375: AND line_type_lookup_code = 'AWT'
8376: AND awt_flag in ('M', 'O');
8466: CURSOR Update_payment_schedule IS
8467: SELECT payment_num,gross_amount,amount_remaining
8468: FROM ap_payment_schedules
8469: WHERE invoice_id=p_invoice_id;
8470: l_automatic_awt_amount ap_invoice_distributions.amount%TYPE :=0;
8471: l_payment_cross_rate ap_invoices.payment_cross_rate%TYPE;
8472: l_pay_curr_code ap_invoices.payment_currency_code%TYPE;
8473: l_num_payments NUMBER := 0;
8474: l_invoice_type ap_invoices.invoice_type_lookup_code%TYPE;
8485:
8486:
8487: SELECT (0 - sum(nvl(amount,0)))
8488: INTO l_automatic_awt_amount
8489: FROM ap_invoice_distributions
8490: WHERE invoice_id = p_invoice_id
8491: AND line_type_lookup_code = 'AWT'
8492: AND awt_flag = 'A';
8493:
8760:
8761: l_debug_info VARCHAR2(1000);
8762: l_curr_calling_sequence VARCHAR2(2000);
8763:
8764: TYPE Invoice_Dists_Tab_Type IS TABLE OF ap_invoice_distributions_all%ROWTYPE;
8765: l_dist_tab Invoice_Dists_Tab_Type := Invoice_Dists_Tab_Type();
8766:
8767: TYPE Expense_Report_Dists_Rec IS RECORD (
8768: Org_id ap_exp_report_dists_all.org_id%TYPE,
8951: l_dist_tab(i).batch_id := p_batch_id;
8952: l_dist_tab(i).invoice_id := p_invoice_line_rec.invoice_id;
8953: l_dist_tab(i).invoice_line_number := p_invoice_line_rec.line_number;
8954:
8955: SELECT ap_invoice_distributions_s.nextval
8956: INTO l_dist_tab(i).invoice_distribution_id
8957: FROM DUAL;
8958:
8959: l_dist_tab(i).line_type_lookup_code := 'ITEM';
9058:
9059: END IF; /* If l_exp_report_dists_tab.count > 0 */
9060:
9061: -------------------------------------------
9062: l_debug_info := 'Bulk Insert into ap_invoice_distributions';
9063: -- Print_Debug(l_api_name, l_debug_info);
9064: IF g_debug_mode = 'Y' THEN
9065: AP_Debug_Pkg.Print(g_debug_mode, l_debug_info );
9066: END IF;
9071: -------------------------------------------
9072: IF (nvl(l_dist_tab.count,0)<>0) THEN
9073:
9074: FORALL j IN l_dist_tab.first .. l_dist_tab.last
9075: INSERT INTO ap_invoice_distributions
9076: VALUES l_dist_tab(j);
9077:
9078: FOR j IN l_dist_tab.first .. l_dist_tab.last
9079: LOOP
9806: FROM DUAL
9807: WHERE AI.FORCE_REVALIDATION_FLAG = ''Y''
9808: UNION ALL
9809: SELECT 1
9810: FROM AP_INVOICE_DISTRIBUTIONS_ALL D,
9811: FINANCIALS_SYSTEM_PARAMS_ALL FSP
9812: WHERE D.INVOICE_ID = AI.INVOICE_ID
9813: AND FSP.ORG_ID = AI.ORG_ID
9814: AND FSP.SET_OF_BOOKS_ID = AI.SET_OF_BOOKS_ID
9863: (AIL.AMOUNT = 0 AND AIL.GENERATE_DISTS = ''Y'')) --8580790
9864: AND NOT EXISTS
9865: ( SELECT /*+ NO_UNNEST */
9866: ''distributed line''
9867: FROM AP_INVOICE_DISTRIBUTIONS_ALL D5
9868: WHERE D5.INVOICE_ID = AIL.INVOICE_ID
9869: AND D5.INVOICE_LINE_NUMBER = AIL.LINE_NUMBER
9870: )
9871: )
9870: )
9871: )
9872: AND NOT EXISTS
9873: ( SELECT /*+ no_push_subq */ ''Cancelled distributions''
9874: FROM AP_INVOICE_DISTRIBUTIONS_ALL D3
9875: WHERE D3.INVOICE_ID = AI.INVOICE_ID
9876: AND D3.CANCELLATION_FLAG = ''Y''
9877: ) ' ;
9878:
12340: p_calling_sequence => l_curr_calling_sequence );
12341:
12342: SELECT COUNT(*)
12343: INTO l_regenerate_dist
12344: FROM ap_invoice_distributions_all aid
12345: WHERE aid.invoice_id = t_inv_lines_table(i) .invoice_id
12346: AND aid.invoice_line_number = t_inv_lines_table(i) .line_number
12347: AND (NVL(posted_flag , 'N') <> 'N'
12348: OR NVL(encumbered_flag,'N') <>'N');
12603:
12604: l_debug_info := 'Before getting the count of encumbered distributions';
12605: SELECT count(*)
12606: INTO l_check_encumbrance
12607: FROM ap_invoice_distributions_all
12608: WHERE invoice_id = P_invoice_id
12609: AND nvl(encumbered_flag, 'N') NOT IN ('N','R')
12610: AND rownum < 2;
12611:
12719: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_debug_loc,l_debug_info);
12720: END IF;
12721: ----------------------------------------------------------------
12722:
12723: UPDATE AP_INVOICE_DISTRIBUTIONS AID
12724: SET Global_Attribute1 =(CASE
12725: WHEN (AID.PO_DISTRIBUTION_ID IS NOT NULL AND
12726: AID. Global_Attribute1 IS NULL)THEN
12727: (SELECT Global_Attribute1
12816:
12817: AP_INVOICES_UTILITY_PKG.CHECK_GDF_VALID
12818: (P_INVOICE_ID, --P_id
12819: 'INV_VLD', --P_calling_mode
12820: 'AP_INVOICE_DISTRIBUTIONS', --P_table_name
12821: l_hold_reject_exists_flag, --P_hold_reject_exists_flag
12822: l_return_code,
12823: l_curr_calling_sequence);
12824: