[Home] [Help]
134: l_dset_line_attribute12 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE12%TYPE;
135: l_dset_line_attribute13 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE13%TYPE;
136: l_dset_line_attribute14 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE14%TYPE;
137: l_dset_line_attribute15 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE15%TYPE;
138: l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
139: l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
140: l_dist_set_percent_number NUMBER;
141: l_running_total_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE := 0;
142: l_running_total_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE := 0;
135: l_dset_line_attribute13 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE13%TYPE;
136: l_dset_line_attribute14 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE14%TYPE;
137: l_dset_line_attribute15 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE15%TYPE;
138: l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
139: l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
140: l_dist_set_percent_number NUMBER;
141: l_running_total_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE := 0;
142: l_running_total_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE := 0;
143: l_running_total_base_amt AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE := 0;
137: l_dset_line_attribute15 AP_DISTRIBUTION_SET_LINES.ATTRIBUTE15%TYPE;
138: l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
139: l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
140: l_dist_set_percent_number NUMBER;
141: l_running_total_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE := 0;
142: l_running_total_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE := 0;
143: l_running_total_base_amt AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE := 0;
144: l_invoice_currency_code AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE;
145: l_status VARCHAR2(10);
138: l_dist_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE;
139: l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
140: l_dist_set_percent_number NUMBER;
141: l_running_total_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE := 0;
142: l_running_total_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE := 0;
143: l_running_total_base_amt AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE := 0;
144: l_invoice_currency_code AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE;
145: l_status VARCHAR2(10);
146: l_industry VARCHAR2(10);
139: l_dist_base_amount AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE;
140: l_dist_set_percent_number NUMBER;
141: l_running_total_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE := 0;
142: l_running_total_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE := 0;
143: l_running_total_base_amt AP_INVOICE_DISTRIBUTIONS.BASE_AMOUNT%TYPE := 0;
144: l_invoice_currency_code AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE;
145: l_status VARCHAR2(10);
146: l_industry VARCHAR2(10);
147: l_set_of_books_id AP_INVOICES.SET_OF_BOOKS_ID%TYPE;
146: l_industry VARCHAR2(10);
147: l_set_of_books_id AP_INVOICES.SET_OF_BOOKS_ID%TYPE;
148: l_pa_installed VARCHAR2(1);
149:
150: l_max_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE := 0;
151: l_max_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE := 0;
152: l_max_i BINARY_INTEGER := 0;
153: l_max_pa_qty_i BINARY_INTEGER := 0;
154: i BINARY_INTEGER := 0;
147: l_set_of_books_id AP_INVOICES.SET_OF_BOOKS_ID%TYPE;
148: l_pa_installed VARCHAR2(1);
149:
150: l_max_amount AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE := 0;
151: l_max_pa_quantity AP_INVOICE_DISTRIBUTIONS.PA_QUANTITY%TYPE := 0;
152: l_max_i BINARY_INTEGER := 0;
153: l_max_pa_qty_i BINARY_INTEGER := 0;
154: i BINARY_INTEGER := 0;
155: l_msg_application VARCHAR2(25);
169: l_rounding_exists VARCHAR2(1) := 'N';
170: l_rounding_pa_qty_exists VARCHAR2(1) := 'N';
171: --For bug2938770
172: l_invoice_type_lookup_code ap_invoices.invoice_type_lookup_code%TYPE;
173: l_prepay_dist_code_ccid ap_invoice_distributions.dist_code_combination_id%TYPE;
174: --bugfix:5725904
175: l_sys_link_function VARCHAR2(2);
176: l_message_text FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
177: l_api_name VARCHAR2(50);
1027:
1028: -----------------------------------------------------------------------
1029: -- FUNCTION insert_from_dist_set validates the distribution_set info
1030: -- and generates distributions
1031: -- by calling ap_invoice_distributions_pkg.insert_from_dist_set.
1032: --
1033: FUNCTION Insert_From_Dist_Set(
1034: X_invoice_id IN NUMBER,
1035: X_line_number IN NUMBER DEFAULT NULL,
1544: -- them.
1545: --------------------------------------------------------------
1546: IF (X_Generate_Dists = 'Y' ) then
1547:
1548: debug_info := 'Calling AP_INVOICE_DISTRIBUTIONS_PKG.Insert_From_Dist_Set';
1549: IF ( NOT (AP_INVOICE_DISTRIBUTIONS_PKG.Insert_From_Dist_Set(
1550: l_batch_id,
1551: X_invoice_id,
1552: X_line_number,
1545: --------------------------------------------------------------
1546: IF (X_Generate_Dists = 'Y' ) then
1547:
1548: debug_info := 'Calling AP_INVOICE_DISTRIBUTIONS_PKG.Insert_From_Dist_Set';
1549: IF ( NOT (AP_INVOICE_DISTRIBUTIONS_PKG.Insert_From_Dist_Set(
1550: l_batch_id,
1551: X_invoice_id,
1552: X_line_number,
1553: y_dist_tab,
1603: BEGIN
1604:
1605: SELECT nvl(max(distribution_line_number),0)
1606: INTO l_max_dist_line_num
1607: FROM ap_invoice_distributions_all -- Bug 7195488 Moac synonym replaced
1608: WHERE invoice_id = X_invoice_id
1609: AND invoice_line_number = X_line_number;
1610:
1611: RETURN(l_max_dist_line_num);
1708: --Bug 5555622 and recoupment, and the related tax distributions
1709: BEGIN
1710: SELECT SUM(base_amount)
1711: INTO l_sum_base_amt
1712: FROM ap_invoice_distributions AID
1713: WHERE AID.invoice_id = X_INVOICE_ID
1714: AND AID.invoice_line_number = X_LINE_NUMBER
1715: AND AID.line_type_lookup_code NOT IN ('RETAINAGE', 'PREPAY')
1716: AND AID.charge_applicable_to_dist_id NOT IN
1714: AND AID.invoice_line_number = X_LINE_NUMBER
1715: AND AID.line_type_lookup_code NOT IN ('RETAINAGE', 'PREPAY')
1716: AND AID.charge_applicable_to_dist_id NOT IN
1717: (SELECT AID1.invoice_distribution_id
1718: FROM ap_invoice_distributions AID1
1719: WHERE AID1.line_type_lookup_code IN ('RETAINAGE', 'PREPAY')
1720: AND AID1.invoice_id = X_INVOICE_ID
1721: AND AID1.invoice_line_number = X_LINE_NUMBER);
1722:
1747: BEGIN
1748: --bugfix:4625349
1749: SELECT invoice_distribution_id
1750: BULK COLLECT INTO l_round_dist_id_list
1751: FROM AP_INVOICE_DISTRIBUTIONS aid1
1752: WHERE aid1.invoice_id = X_INVOICE_ID
1753: AND aid1.invoice_line_number = X_LINE_NUMBER
1754: AND nvl(aid1.posted_flag, 'N') = 'N'
1755: AND NVL(aid1.match_status_flag, 'N') IN ('N', 'S')
1840: l_debug_info VARCHAR2(4000);
1841: l_curr_calling_sequence VARCHAR2(4000);
1842:
1843: l_po_distribution_id
1844: ap_invoice_distributions.po_distribution_id%TYPE;
1845:
1846: l_distribution_count NUMBER := 0;
1847: l_sum_matched_qty NUMBER := 0;
1848: l_sum_matched_amt NUMBER := 0;
1845:
1846: l_distribution_count NUMBER := 0;
1847: l_sum_matched_qty NUMBER := 0;
1848: l_sum_matched_amt NUMBER := 0;
1849: l_dist_type_lookup_code ap_invoice_distributions_all.line_type_lookup_code%TYPE;
1850: l_matching_basis po_line_locations_all.matching_basis%TYPE;
1851: i NUMBER := 1;
1852: l_max_line_num
1853: ap_invoice_distributions.distribution_line_number%TYPE;
1849: l_dist_type_lookup_code ap_invoice_distributions_all.line_type_lookup_code%TYPE;
1850: l_matching_basis po_line_locations_all.matching_basis%TYPE;
1851: i NUMBER := 1;
1852: l_max_line_num
1853: ap_invoice_distributions.distribution_line_number%TYPE;
1854: l_matched_uom VARCHAR2(30);
1855: l_key_value_list ap_dbi_pkg.r_dbi_key_value_arr;
1856: l_key_value_list2 ap_dbi_pkg.r_dbi_key_value_arr;
1857: l_key_value_list3 ap_dbi_pkg.r_dbi_key_value_arr;
1854: l_matched_uom VARCHAR2(30);
1855: l_key_value_list ap_dbi_pkg.r_dbi_key_value_arr;
1856: l_key_value_list2 ap_dbi_pkg.r_dbi_key_value_arr;
1857: l_key_value_list3 ap_dbi_pkg.r_dbi_key_value_arr;
1858: l_global_attr_category ap_invoice_distributions.global_attribute_category%TYPE;
1859: l_open_gl_date DATE;
1860: l_result_string VARCHAR2(4000);
1861: l_loop_counter BINARY_INTEGER;
1862: l_awt_success VARCHAR2(2000);
1921: --Added below cursor for bug#8928639
1922:
1923: CURSOR prepay_inv_cur IS
1924: SELECT distinct aid1.invoice_id,aid1.invoice_line_number
1925: FROM ap_invoice_distributions_all AID, --Inv dists
1926: ap_invoice_distributions_all AID1 --prepay dists
1927: WHERE aid.invoice_id = p_line_rec.invoice_id
1928: AND aid.invoice_line_number = p_line_rec.line_number
1929: AND aid1.invoice_distribution_id = aid.prepay_distribution_id;
1922:
1923: CURSOR prepay_inv_cur IS
1924: SELECT distinct aid1.invoice_id,aid1.invoice_line_number
1925: FROM ap_invoice_distributions_all AID, --Inv dists
1926: ap_invoice_distributions_all AID1 --prepay dists
1927: WHERE aid.invoice_id = p_line_rec.invoice_id
1928: AND aid.invoice_line_number = p_line_rec.line_number
1929: AND aid1.invoice_distribution_id = aid.prepay_distribution_id;
1930:
1942: aid.line_type_lookup_code,
1943: pll.matching_basis,
1944: aid1.invoice_id prepay_invoice_id,
1945: aid1.invoice_line_number prepay_line_number
1946: FROM ap_invoice_distributions_all AID ,
1947: po_line_locations pll,
1948: ap_invoice_distributions_all AID1
1949: WHERE aid.invoice_id = p_line_rec.invoice_id
1950: AND aid.invoice_line_number = p_line_rec.line_number
1944: aid1.invoice_id prepay_invoice_id,
1945: aid1.invoice_line_number prepay_line_number
1946: FROM ap_invoice_distributions_all AID ,
1947: po_line_locations pll,
1948: ap_invoice_distributions_all AID1
1949: WHERE aid.invoice_id = p_line_rec.invoice_id
1950: AND aid.invoice_line_number = p_line_rec.line_number
1951: --Contract Payments: Added the 'PREPAY' to the clause
1952: AND aid.line_type_lookup_code in ('ITEM','ACCRUAL', 'IPV','ERV','PREPAY','RETAINAGE')
1970: AND arl.chrg_invoice_line_number = ail.line_number
1971: and ail.line_type_lookup_code IN ('FREIGHT','MISCELLANEOUS')
1972: AND exists
1973: (select aid.invoice_line_number
1974: from ap_invoice_distributions_all aid
1975: where aid.invoice_id = ail.invoice_id
1976: and aid.invoice_line_number = ail.line_number);
1977:
1978: l_chrg_line_rec ap_invoice_lines_all%rowtype;
1988: 'ITEM_TO_SERVICE_PO', 0,
1989: 'ITEM_TO_SERVICE_RECEIPT', 0,
1990: NVL(AID.corrected_quantity, 0) + NVL(AID.quantity_invoiced,0))) quantity
1991: from ap_invoice_lines_all ail
1992: ,ap_invoice_distributions_all aid
1993: ,po_distributions_all pod
1994: ,po_line_locations_all pll
1995: where ail.invoice_id = p_line_rec.invoice_id
1996: and ail.line_number = p_line_rec.line_number
2010:
2011: -- bug 5572121
2012: cursor dist_debug_cur is
2013: Select *
2014: FROM ap_invoice_distributions_all aid
2015: WHERE aid.invoice_id = p_line_rec.invoice_id;
2016:
2017: --bugfix:5638822
2018: l_recouped_amount ap_invoices_all.invoice_amount%type;
2020: l_error_message varchar2(4000);
2021: l_payment_currency_code ap_invoices_all.payment_currency_code%TYPE;
2022: l_amount_remaining number;
2023:
2024: TYPE r_line_number_arr IS TABLE OF ap_invoice_distributions.distribution_line_number%TYPE; /*Bug 9841966*/
2025: TYPE r_ppay_incl_arr IS TABLE OF ap_invoice_lines_all.invoice_includes_prepay_flag%TYPE; /*Bug 9841966*/
2026:
2027: --bugfix:5697764
2028: l_tax_line_number r_line_number_arr; --number; /*Bug 9841966: Commented old datatype and changed it to pl/sql array*/
2038: l_invoice_rec AP_APPROVAL_PKG.Invoice_Rec;
2039: l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
2040:
2041: -- Bug 8623061 - Start
2042: l_award_id ap_invoice_distributions_all.award_id%TYPE;
2043:
2044: cursor c_distribution_list_cur is
2045: SELECT *
2046: FROM ap_invoice_distributions_all aid
2042: l_award_id ap_invoice_distributions_all.award_id%TYPE;
2043:
2044: cursor c_distribution_list_cur is
2045: SELECT *
2046: FROM ap_invoice_distributions_all aid
2047: WHERE aid.parent_reversal_id IS NOT NULL
2048: AND aid.invoice_id = p_line_rec.invoice_id
2049: AND aid.invoice_line_number = p_line_rec.line_number
2050: AND aid.reversal_flag = 'Y'
2192: tax_dist.award_id) award_id,
2193: NULL credit_card_trx_id,
2194: tax_dist.dist_match_type dist_match_type,
2195: tax_dist.rcv_transaction_id rcv_transaction_id,
2196: ap_invoice_distributions_s.NEXTVAL invoice_distribution_id,
2197: tax_dist.invoice_distribution_id parent_reversal_id,
2198: tax_dist.tax_recoverable_flag tax_recoverable_flag,
2199: NULL merchant_document_number,
2200: NULL merchant_name,
2221: zx_dist.trx_line_dist_id charge_applicable_to_dist_id,
2222: NULL corrected_quantity,
2223: DECODE( tax_dist.related_id, NULL, NULL,
2224: tax_dist.invoice_distribution_id,
2225: ap_invoice_distributions_s.CURRVAL, NULL) related_id,
2226:
2227: NULL asset_book_type_code,
2228: NULL asset_category_id,
2229: tax_dist.distribution_class distribution_class,
2240: tax_dist.summary_tax_line_id summary_tax_line_id,
2241: 'N' rcv_charge_addition_flag,
2242: (-1)*tax_dist.prepay_tax_diff_amount prepay_tax_diff_amount,
2243: tax_dist.pay_awt_group_id pay_awt_group_id
2244: from ap_invoice_distributions_all tax_dist,
2245: ap_invoice_distributions_all item_dist,
2246: zx_rec_nrec_dist zx_dist
2247: where tax_dist.invoice_id = c_invoice_id
2248: and tax_dist.invoice_id = zx_dist.trx_id
2241: 'N' rcv_charge_addition_flag,
2242: (-1)*tax_dist.prepay_tax_diff_amount prepay_tax_diff_amount,
2243: tax_dist.pay_awt_group_id pay_awt_group_id
2244: from ap_invoice_distributions_all tax_dist,
2245: ap_invoice_distributions_all item_dist,
2246: zx_rec_nrec_dist zx_dist
2247: where tax_dist.invoice_id = c_invoice_id
2248: and tax_dist.invoice_id = zx_dist.trx_id
2249: and zx_dist.application_id = 200
2258: and item_dist.invoice_line_number = c_item_line_number
2259: and item_dist.line_type_lookup_code IN ('FREIGHT','MISCELLANEOUS')
2260: and zx_dist.reverse_flag = 'Y'
2261: and not exists(select detail_tax_dist_id
2262: from ap_invoice_distributions aid
2263: where aid.invoice_id = c_invoice_id
2264: and aid.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id);
2265:
2266: l_reverse_tax_dist c_reverse_tax_dist_1%rowtype ;
2399: tax_dist.award_id) award_id,
2400: NULL credit_card_trx_id,
2401: tax_dist.dist_match_type dist_match_type,
2402: tax_dist.rcv_transaction_id rcv_transaction_id,
2403: ap_invoice_distributions_s.NEXTVAL invoice_distribution_id,
2404: tax_dist.invoice_distribution_id parent_reversal_id,
2405: tax_dist.tax_recoverable_flag tax_recoverable_flag,
2406: NULL merchant_document_number,
2407: NULL merchant_name,
2428: zx_dist.trx_line_dist_id charge_applicable_to_dist_id,
2429: NULL corrected_quantity,
2430: DECODE( tax_dist.related_id, NULL, NULL,
2431: tax_dist.invoice_distribution_id,
2432: ap_invoice_distributions_s.CURRVAL, NULL) related_id,
2433:
2434: NULL asset_book_type_code,
2435: NULL asset_category_id,
2436: tax_dist.distribution_class distribution_class,
2449: zx_dist.self_assessed_flag self_assessed_flag,
2450: tax_dist.self_assessed_tax_liab_ccid self_assessed_tax_liab_ccid,
2451: (-1)*tax_dist.prepay_tax_diff_amount prepay_tax_diff_amount
2452: from ap_self_assessed_tax_dist_all tax_dist,
2453: ap_invoice_distributions_all item_dist,
2454: zx_rec_nrec_dist zx_dist
2455: where tax_dist.invoice_id = c_invoice_id
2456: and tax_dist.invoice_id = zx_dist.trx_id
2457: and zx_dist.application_id = 200
2587: IF ( l_awt_success = 'SUCCESS' ) THEN
2588: IF nvl(l_cancel_proactive_flag,'N') ='C' THEN --if proactive cancel profile set
2589: --9100425
2590: For I in(select invoice_distribution_id
2591: from ap_invoice_distributions aid1
2592: where aid1.invoice_id=P_line_rec.invoice_id
2593: and aid1.line_type_lookup_code='AWT'
2594: and aid1.awt_flag='A'
2595: and aid1.parent_reversal_id is null --original dist
2593: and aid1.line_type_lookup_code='AWT'
2594: and aid1.awt_flag='A'
2595: and aid1.parent_reversal_id is null --original dist
2596: --for original dists there is no reversal dist created
2597: and ( not exists (select 1 from ap_invoice_distributions aid2
2598: where aid1.invoice_id=aid2.invoice_id
2599: and aid2.invoice_id=P_line_rec.invoice_id
2600: and aid2.invoice_line_number=aid1.invoice_line_number
2601: and aid2.parent_reversal_id =aid1.invoice_distribution_id)
2599: and aid2.invoice_id=P_line_rec.invoice_id
2600: and aid2.invoice_line_number=aid1.invoice_line_number
2601: and aid2.parent_reversal_id =aid1.invoice_distribution_id)
2602: --the reversal dist does not reverse the amount correctly
2603: or exists (select 1 from ap_invoice_distributions aid2
2604: where aid1.invoice_id=aid2.invoice_id
2605: and aid2.invoice_id=P_line_rec.invoice_id
2606: and aid2.invoice_line_number=aid1.invoice_line_number
2607: and aid2.parent_reversal_id =aid1.invoice_distribution_id
2723:
2724: l_itm_dist_count := 0;
2725: -- query to get the Distributions for that standard invoice
2726: SELECT COUNT(*) INTO l_itm_dist_count
2727: FROM ap_invoice_distributions_all aid
2728: WHERE aid.invoice_id = p_line_rec.invoice_id
2729: AND aid.prepay_distribution_id is not null
2730: AND NOT EXISTS (SELECT 1
2731: FROM ap_invoice_distributions_all item
2727: FROM ap_invoice_distributions_all aid
2728: WHERE aid.invoice_id = p_line_rec.invoice_id
2729: AND aid.prepay_distribution_id is not null
2730: AND NOT EXISTS (SELECT 1
2731: FROM ap_invoice_distributions_all item
2732: WHERE item.invoice_id = p_line_rec.invoice_id
2733: AND item.prepay_distribution_id is null)
2734: AND ROWNUM =1;
2735:
2814: IF nvl(l_cancel_proactive_flag,'N') ='C' and NVL(P_calling_mode,'DISCARD') = 'DISCARD' THEN
2815: --proactive cancel check profile on
2816:
2817: For I in(select invoice_distribution_id
2818: from ap_invoice_distributions aid1
2819: where aid1.invoice_id=l_chrg_line_rec.invoice_id
2820: and aid1.invoice_line_number=l_chrg_line_rec.line_number --P_line_rec. line_number
2821: and aid1.parent_reversal_id is null --original dist
2822: and ((line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV')
2825: )
2826: and (line_type_lookup_code <> 'AWT' OR
2827: (line_type_lookup_code = 'AWT' AND awt_flag <> 'A'))
2828: --for original dists there is no reversal dist created
2829: and ( not exists (select 1 from ap_invoice_distributions aid2
2830: where aid1.invoice_id=aid2.invoice_id
2831: and aid2.invoice_id=l_chrg_line_rec.invoice_id
2832: and aid2.invoice_line_number=l_chrg_line_rec.line_number --P_line_rec.line_number
2833: and aid2.parent_reversal_id =aid1.invoice_distribution_id)
2831: and aid2.invoice_id=l_chrg_line_rec.invoice_id
2832: and aid2.invoice_line_number=l_chrg_line_rec.line_number --P_line_rec.line_number
2833: and aid2.parent_reversal_id =aid1.invoice_distribution_id)
2834: --the reversal dist does not reverse the amount correctly
2835: or exists (select 1 from ap_invoice_distributions aid2
2836: where aid1.invoice_id=aid2.invoice_id
2837: and aid2.invoice_id=l_chrg_line_rec.invoice_id
2838: and aid2.invoice_line_number=l_chrg_line_rec.line_number --P_line_rec.line_number
2839: and aid2.parent_reversal_id =aid1.invoice_distribution_id
2900:
2901: IF l_reverse_dist_count>0 THEN
2902:
2903: ---------------------------------------------------------------------------------------------------
2904: l_debug_info := 'Inserting reverse entries into ap_invoice_distributions_all after line discard';
2905: Print (l_api_name,l_debug_info);
2906: ---------------------------------------------------------------------------------------------------
2907: OPEN c_reverse_tax_dist_1(l_chrg_line_rec.invoice_id,
2908: l_chrg_line_rec.line_number);
2910: FETCH c_reverse_tax_dist_1
2911: INTO l_reverse_tax_dist;
2912: EXIT WHEN c_reverse_tax_dist_1%NOTFOUND;
2913:
2914: INSERT INTO ap_invoice_distributions_all (
2915: accounting_date,
2916: accrual_posted_flag,
2917: assets_addition_flag,
2918: assets_tracking_flag,
3621: l_debug_info := 'Update reversal_flag';
3622: Print (l_api_name,l_debug_info);
3623: -----------------------------------------------------------------
3624:
3625: UPDATE ap_invoice_distributions_all aid
3626: SET reversal_flag = (select reverse_flag
3627: from zx_rec_nrec_dist zx
3628: where zx.rec_nrec_tax_dist_id = aid.detail_tax_dist_id)
3629: WHERE aid.invoice_id = p_line_rec.invoice_id
3633: l_debug_info := 'Update related_flag';
3634: Print (l_api_name,l_debug_info);
3635: -----------------------------------------------------------------
3636:
3637: UPDATE ap_invoice_distributions aid
3638: SET aid.related_id =
3639: (SELECT invoice_distribution_id
3640: FROM ap_invoice_distributions_all aid1
3641: WHERE aid1.invoice_id = aid.invoice_id
3636:
3637: UPDATE ap_invoice_distributions aid
3638: SET aid.related_id =
3639: (SELECT invoice_distribution_id
3640: FROM ap_invoice_distributions_all aid1
3641: WHERE aid1.invoice_id = aid.invoice_id
3642: AND aid1.invoice_line_number = aid.invoice_line_number
3643: AND aid1.parent_reversal_id =
3644: (SELECT related_id
3641: WHERE aid1.invoice_id = aid.invoice_id
3642: AND aid1.invoice_line_number = aid.invoice_line_number
3643: AND aid1.parent_reversal_id =
3644: (SELECT related_id
3645: FROM ap_invoice_distributions_all aid2
3646: WHERE aid2.invoice_id = aid.invoice_id
3647: AND aid2.invoice_line_number = aid.invoice_line_number
3648: AND aid2.invoice_distribution_id = aid.parent_reversal_id)
3649: )
3749: (p_invoice_id => p_line_rec.invoice_id,
3750: p_calling_sequence => l_curr_calling_sequence);
3751:
3752:
3753: UPDATE ap_invoice_distributions aid
3754: SET aid.encumbered_flag = 'R'
3755: WHERE aid.invoice_id = p_line_rec.invoice_id
3756: AND aid.invoice_line_number = p_line_rec.line_number
3757: AND nvl(aid.encumbered_flag,'N') IN ('N','H','P')
4194: --Prepay distributions , but not the TAX related to ITEM dists, since DETERMINE_RECOVERY
4195: --will take care of the TAX on ITEM dists.
4196: SELECT count(*)
4197: INTO l_distribution_count
4198: FROM ap_invoice_distributions_all
4199: WHERE invoice_id = p_line_rec.invoice_id
4200: AND invoice_line_number = p_line_rec.line_number
4201: AND ((line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV') and
4202: prepay_distribution_id IS NULL) OR
4257: --consideration 'Prepay' and its related Tax distributions.
4258:
4259: -- Removed the below HAVING clause, for the 7376114, to allow zero amount ITEM lines to be reversed.
4260:
4261: INSERT INTO ap_invoice_distributions_all(
4262: invoice_id,
4263: invoice_line_number,
4264: dist_code_combination_id,
4265: invoice_distribution_id,
4403: (SELECT
4404: Invoice_Id, -- invoice_id
4405: Invoice_Line_Number, -- invoice_line_number
4406: Dist_Code_Combination_Id, -- dist_code_combination_id
4407: ap_invoice_distributions_s.NEXTVAL, -- distribution_id
4408: sysdate, -- last_update_date
4409: p_Last_Updated_By, -- last_updated_by
4410: /* Bug 5584997, Getting the accounting_date from line rec
4411: --Bug9345786. Commented the following code.
4562: charge_applicable_to_dist_id, -- charge_applicable_to_dist_id
4563: corrected_invoice_dist_id, -- corrected_invoice_dist_id
4564: DECODE( related_id, NULL, NULL,
4565: invoice_distribution_id,
4566: ap_invoice_distributions_s.CURRVAL,
4567: --bugfix:4921399
4568: NULL ), -- related_id
4569: asset_book_type_code, -- asset_book_type_code
4570: asset_category_id, -- asset_category_id
4576: awt_related_id, -- Bug 8745752
4577: retained_invoice_dist_id, -- Bug 8824235
4578: pay_awt_group_id, /* Bug 9821980 */
4579: 'N' /*Bug#11067286*/
4580: FROM ap_invoice_distributions_all
4581: WHERE invoice_id = p_line_rec.invoice_id
4582: AND invoice_line_number = p_line_rec.line_number
4583: AND line_type_lookup_code NOT IN
4584: ('REC_TAX', 'NONREC_TAX', 'TRV', 'TERV', 'TIPV')
4585: AND (line_type_lookup_code <> 'AWT' OR
4586: (line_type_lookup_code = 'AWT' AND awt_flag <> 'A'))
4587: AND dist_code_combination_id
4588: IN (SELECT dist_code_combination_id
4589: FROM ap_invoice_distributions_all
4590: WHERE invoice_id = p_line_rec.invoice_id
4591: AND invoice_line_number = p_line_rec.line_number
4592: GROUP BY dist_code_combination_id,
4593: po_distribution_id,
4605: rcv_transaction_id) -- Bug 4159731
4606: AND nvl(po_distribution_id,-99) IN
4607: (SELECT
4608: NVL(po_distribution_id, -99)
4609: FROM ap_invoice_distributions_all
4610: WHERE invoice_id = p_line_rec.invoice_id
4611: AND invoice_line_number = p_line_rec.line_number
4612: GROUP BY dist_code_combination_id,
4613: po_distribution_id,
4628: ) ;
4629:
4630:
4631: --bugfix:4921399
4632: UPDATE ap_invoice_distributions aid
4633: SET aid.related_id =
4634: (SELECT invoice_distribution_id
4635: FROM ap_invoice_distributions aid1
4636: WHERE aid1.invoice_id = aid.invoice_id
4631: --bugfix:4921399
4632: UPDATE ap_invoice_distributions aid
4633: SET aid.related_id =
4634: (SELECT invoice_distribution_id
4635: FROM ap_invoice_distributions aid1
4636: WHERE aid1.invoice_id = aid.invoice_id
4637: AND aid1.invoice_line_number = aid.invoice_line_number
4638: AND aid1.parent_reversal_id =
4639: (SELECT related_id
4636: WHERE aid1.invoice_id = aid.invoice_id
4637: AND aid1.invoice_line_number = aid.invoice_line_number
4638: AND aid1.parent_reversal_id =
4639: (SELECT related_id
4640: FROM ap_invoice_distributions aid2
4641: WHERE aid2.invoice_id = aid.invoice_id
4642: AND aid2.invoice_line_number = aid.invoice_line_number
4643: AND aid2.invoice_distribution_id = aid.parent_reversal_id)
4644: )
4648: AND aid.invoice_line_number = p_line_rec.line_number
4649: AND aid.reversal_flag = 'Y';
4650:
4651: -- Updating Retained_Amount_Remaining 8824235
4652: UPDATE ap_invoice_distributions_all aid
4653: SET aid.retained_amount_remaining = aid.retained_amount_remaining -
4654: NVL((SELECT sum(d2.amount)
4655: FROM ap_invoice_distributions_all d2
4656: WHERE d2.parent_reversal_id is not null
4651: -- Updating Retained_Amount_Remaining 8824235
4652: UPDATE ap_invoice_distributions_all aid
4653: SET aid.retained_amount_remaining = aid.retained_amount_remaining -
4654: NVL((SELECT sum(d2.amount)
4655: FROM ap_invoice_distributions_all d2
4656: WHERE d2.parent_reversal_id is not null
4657: AND d2.reversal_flag = 'Y'
4658: AND d2.invoice_id = p_line_rec.invoice_id
4659: AND d2.invoice_line_number = p_line_rec.line_number
4660: AND d2.match_status_flag = 'N'
4661: AND d2.retained_invoice_dist_id = aid.invoice_distribution_id), 0)
4662: WHERE invoice_distribution_id in
4663: (SELECT DISTINCT retained_invoice_dist_id
4664: FROM ap_invoice_distributions_all d3
4665: WHERE d3.reversal_flag = 'Y'
4666: AND d3.invoice_id = p_line_rec.invoice_id
4667: AND d3.invoice_line_number = p_line_rec.line_number
4668: AND d3.parent_reversal_id is not null
4705:
4706: --bugfix:4542556
4707: SELECT invoice_distribution_id,prepay_distribution_id
4708: BULK COLLECT INTO l_key_value_list,l_key_value_list3
4709: FROM ap_invoice_distributions
4710: WHERE invoice_id = p_line_rec.invoice_id
4711: AND invoice_line_number = p_line_rec.line_number
4712: AND line_type_lookup_code = 'PREPAY'
4713: AND nvl(reversal_flag,'N') = 'Y'
4738: +-----------------------------------------------------------------*/
4739:
4740: l_debug_info := 'Set reversal_flag to Y for existing distributions';
4741:
4742: UPDATE ap_invoice_distributions
4743: SET reversal_flag = 'Y'
4744: WHERE invoice_id = p_line_rec.invoice_id
4745: AND invoice_line_number = p_line_rec.line_number;
4746:
4801: IF (nvl(p_calling_mode,'DISCARD') = 'UNAPPLY_PREPAY') THEN
4802:
4803: SELECT aid.invoice_line_number,nvl(ail1.invoice_includes_prepay_flag,'N')
4804: BULK COLLECT INTO l_tax_line_number,l_prepay_included /*Bug 9841966: Change to bulk collect*/
4805: FROM ap_invoice_distributions aid, --tax dists
4806: ap_invoice_lines ail1, --item line
4807: ap_invoice_distributions aid1 --item distributions
4808: WHERE ail1.invoice_id = aid1.invoice_id
4809: AND ail1.invoice_id = p_line_rec.invoice_id
4803: SELECT aid.invoice_line_number,nvl(ail1.invoice_includes_prepay_flag,'N')
4804: BULK COLLECT INTO l_tax_line_number,l_prepay_included /*Bug 9841966: Change to bulk collect*/
4805: FROM ap_invoice_distributions aid, --tax dists
4806: ap_invoice_lines ail1, --item line
4807: ap_invoice_distributions aid1 --item distributions
4808: WHERE ail1.invoice_id = aid1.invoice_id
4809: AND ail1.invoice_id = p_line_rec.invoice_id
4810: AND ail1.line_number = p_line_rec.line_number
4811: AND aid.invoice_id = aid1.invoice_id
4847: --Bug 7526679
4848: -- The query is modified to select the total amount of latest reversed tax distributions.
4849: SELECT sum(aid1.amount)
4850: INTO l_unapplied_tax_amount
4851: FROM ap_invoice_distributions_all aid1,
4852: ap_invoice_distributions_all aid2
4853: WHERE aid1.invoice_id = p_line_rec.invoice_id
4854: AND aid1.invoice_id = aid2.invoice_id
4855: AND aid1.invoice_line_number = l_tax_line_number(l_loop_counter) /*Bug 9841966: added loop counter*/
4848: -- The query is modified to select the total amount of latest reversed tax distributions.
4849: SELECT sum(aid1.amount)
4850: INTO l_unapplied_tax_amount
4851: FROM ap_invoice_distributions_all aid1,
4852: ap_invoice_distributions_all aid2
4853: WHERE aid1.invoice_id = p_line_rec.invoice_id
4854: AND aid1.invoice_id = aid2.invoice_id
4855: AND aid1.invoice_line_number = l_tax_line_number(l_loop_counter) /*Bug 9841966: added loop counter*/
4856: AND aid2.invoice_line_number = p_line_rec.line_number
4919: BEGIN
4920: If (nvl(l_cancel_proactive_flag,'N') ='C') and (NVL( P_calling_mode,'DISCARD') = 'DISCARD') THEN
4921:
4922: For I in(select invoice_distribution_id
4923: from ap_invoice_distributions aid1
4924: where aid1.invoice_id=P_line_rec.invoice_id
4925: and aid1.invoice_line_number=P_line_rec. line_number
4926: and aid1.parent_reversal_id is null --original dist
4927: --for original dists there is no reversal dist created
4924: where aid1.invoice_id=P_line_rec.invoice_id
4925: and aid1.invoice_line_number=P_line_rec. line_number
4926: and aid1.parent_reversal_id is null --original dist
4927: --for original dists there is no reversal dist created
4928: and ( not exists (select 1 from ap_invoice_distributions aid2
4929: where aid1.invoice_id=aid2.invoice_id
4930: and aid2.invoice_id=P_line_rec.invoice_id
4931: and aid2.invoice_line_number=P_line_rec.line_number
4932: and aid2.parent_reversal_id =aid1.invoice_distribution_id)
4930: and aid2.invoice_id=P_line_rec.invoice_id
4931: and aid2.invoice_line_number=P_line_rec.line_number
4932: and aid2.parent_reversal_id =aid1.invoice_distribution_id)
4933: --the reversal dist does not reverse the amount correctly
4934: or exists (select 1 from ap_invoice_distributions aid2
4935: where aid1.invoice_id=aid2.invoice_id
4936: and aid2.invoice_id=P_line_rec.invoice_id
4937: and aid2.invoice_line_number=P_line_rec.line_number
4938: and aid2.parent_reversal_id =aid1.invoice_distribution_id
4938: and aid2.parent_reversal_id =aid1.invoice_distribution_id
4939: and -1 * aid2.amount <> aid1.amount))
4940: UNION
4941: select invoice_distribution_id
4942: from ap_invoice_distributions aid1
4943: where aid1.invoice_id=P_line_rec.invoice_id
4944: and aid1.charge_applicable_to_dist_id in (
4945: select aid2.invoice_distribution_id from ap_invoice_distributions aid2
4946: where aid2.invoice_id=P_line_rec.invoice_id
4941: select invoice_distribution_id
4942: from ap_invoice_distributions aid1
4943: where aid1.invoice_id=P_line_rec.invoice_id
4944: and aid1.charge_applicable_to_dist_id in (
4945: select aid2.invoice_distribution_id from ap_invoice_distributions aid2
4946: where aid2.invoice_id=P_line_rec.invoice_id
4947: and aid2.invoice_line_number=P_line_rec.line_number)
4948: and aid1.parent_reversal_id is null --original dist
4949: and aid1.line_type_lookup_code not in('MISCELLANEOUS','FREIGHT')
4947: and aid2.invoice_line_number=P_line_rec.line_number)
4948: and aid1.parent_reversal_id is null --original dist
4949: and aid1.line_type_lookup_code not in('MISCELLANEOUS','FREIGHT')
4950: --for original dists there is no reversal dist created
4951: and ( not exists (select 1 from ap_invoice_distributions aid2
4952: where aid1.invoice_id=aid2.invoice_id
4953: and aid2.invoice_id=P_line_rec.invoice_id
4954: and aid2.parent_reversal_id =aid1.invoice_distribution_id)
4955: --the reversal dist does not reverse the amount correctly
4952: where aid1.invoice_id=aid2.invoice_id
4953: and aid2.invoice_id=P_line_rec.invoice_id
4954: and aid2.parent_reversal_id =aid1.invoice_distribution_id)
4955: --the reversal dist does not reverse the amount correctly
4956: or exists (select 1 from ap_invoice_distributions aid2
4957: where aid1.invoice_id=aid2.invoice_id
4958: and aid2.invoice_id=P_line_rec.invoice_id
4959: and aid2.parent_reversal_id =aid1.invoice_distribution_id
4960: and -1 * aid2.amount <> aid1.amount)))
5091:
5092: l_curr_calling_sequence VARCHAR2(2000);
5093: l_debug_info VARCHAR2(240);
5094: l_api_name VARCHAR2(50);
5095: l_max_line_num ap_invoice_distributions.distribution_line_number%TYPE;
5096: l_tax_distributions_exist BOOLEAN := FALSE ; --Bug14383132
5097: l_self_assess_tax_dist_exist BOOLEAN := FALSE ; --Bug14383132
5098:
5099: BEGIN
5108: p_inv_line_rec.line_number);
5109:
5110: --Bug 8733916
5111:
5112: UPDATE ap_invoice_distributions aid
5113: SET aid.encumbered_flag = 'R'
5114: WHERE aid.invoice_id = p_inv_line_rec.invoice_id
5115: AND aid.invoice_line_number = p_inv_line_rec.line_number
5116: AND nvl(aid.match_status_flag,'N') <> 'A'
5126: l_debug_info := 'Insert reverse charge distributions';
5127: Print (l_api_name,l_debug_info);
5128: ------------------------------------------------------
5129:
5130: INSERT INTO ap_invoice_distributions_all(
5131: invoice_id,
5132: invoice_line_number,
5133: dist_code_combination_id,
5134: invoice_distribution_id,
5270: (SELECT
5271: Invoice_Id, -- invoice_id
5272: Invoice_Line_Number, -- invoice_line_number
5273: Dist_Code_Combination_Id, -- dist_code_combination_id
5274: ap_invoice_distributions_s.NEXTVAL, -- distribution_id
5275: sysdate, -- last_update_date
5276: fnd_global.user_id, -- last_updated_by
5277: /* Bug 5584997, Getting the accounting_date from line rec */
5278: DECODE(P_calling_mode,'UNAPPLY_PREPAY',p_inv_line_rec.accounting_date,
5409: charge_applicable_to_dist_id, -- charge_applicable_to_dist_id
5410: corrected_invoice_dist_id, -- corrected_invoice_dist_id
5411: DECODE( related_id, NULL, NULL,
5412: invoice_distribution_id,
5413: ap_invoice_distributions_s.CURRVAL,
5414: --bugfix:4921399
5415: NULL ), -- related_id
5416: asset_book_type_code, -- asset_book_type_code
5417: asset_category_id, -- asset_category_id
5421: intended_use, -- intended_use
5422: 'N', -- rcv_charge_addition_flag
5423: pay_awt_group_id, /* Bug 9821980 */
5424: 'Y'
5425: FROM ap_invoice_distributions_all
5426: WHERE invoice_id = p_inv_line_rec.invoice_id
5427: AND invoice_line_number = p_inv_line_rec.line_number
5428: AND (reversal_flag is null
5429: or reversal_flag = 'N')
5441: Print (l_api_name,l_debug_info);
5442: ---------------------------------------------------------------
5443: --Bug8733916
5444:
5445: UPDATE ap_invoice_distributions
5446: SET reversal_flag = 'Y'
5447: WHERE invoice_id = p_inv_line_rec.invoice_id
5448: AND invoice_line_number = p_inv_line_rec.line_number
5449: AND line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV');
5512: zx_dist.trx_level_type reversed_trx_level_type,
5513: zx_dist.trx_line_id reversed_trx_line_id,
5514: zx_dist.trx_line_dist_id reversed_trx_line_dist_id,
5515: zx_dist.tax_line_id reversed_tax_line_id
5516: from ap_invoice_distributions_all item_dist,
5517: ap_invoice_distributions_all tax_dist,
5518: ap_invoice_distributions_all reverse_dist,
5519: zx_rec_nrec_dist zx_dist
5520: where tax_dist.invoice_id = p_inv_line_rec.invoice_id
5513: zx_dist.trx_line_id reversed_trx_line_id,
5514: zx_dist.trx_line_dist_id reversed_trx_line_dist_id,
5515: zx_dist.tax_line_id reversed_tax_line_id
5516: from ap_invoice_distributions_all item_dist,
5517: ap_invoice_distributions_all tax_dist,
5518: ap_invoice_distributions_all reverse_dist,
5519: zx_rec_nrec_dist zx_dist
5520: where tax_dist.invoice_id = p_inv_line_rec.invoice_id
5521: and tax_dist.invoice_id = item_dist.invoice_id
5514: zx_dist.trx_line_dist_id reversed_trx_line_dist_id,
5515: zx_dist.tax_line_id reversed_tax_line_id
5516: from ap_invoice_distributions_all item_dist,
5517: ap_invoice_distributions_all tax_dist,
5518: ap_invoice_distributions_all reverse_dist,
5519: zx_rec_nrec_dist zx_dist
5520: where tax_dist.invoice_id = p_inv_line_rec.invoice_id
5521: and tax_dist.invoice_id = item_dist.invoice_id
5522: and tax_dist.charge_applicable_to_dist_id = item_dist.invoice_distribution_id
5575: zx_dist.trx_level_type reversed_trx_level_type,
5576: zx_dist.trx_line_id reversed_trx_line_id,
5577: zx_dist.trx_line_dist_id reversed_trx_line_dist_id,
5578: zx_dist.tax_line_id reversed_tax_line_id
5579: from ap_invoice_distributions_all item_dist,
5580: ap_self_assessed_tax_dist_all tax_dist,
5581: ap_invoice_distributions_all reverse_dist,
5582: zx_rec_nrec_dist zx_dist
5583: where tax_dist.invoice_id = p_inv_line_rec.invoice_id
5577: zx_dist.trx_line_dist_id reversed_trx_line_dist_id,
5578: zx_dist.tax_line_id reversed_tax_line_id
5579: from ap_invoice_distributions_all item_dist,
5580: ap_self_assessed_tax_dist_all tax_dist,
5581: ap_invoice_distributions_all reverse_dist,
5582: zx_rec_nrec_dist zx_dist
5583: where tax_dist.invoice_id = p_inv_line_rec.invoice_id
5584: and tax_dist.invoice_id = item_dist.invoice_id
5585: and tax_dist.charge_applicable_to_dist_id = item_dist.invoice_distribution_id
5636: --------------------------------------------------------------------------------------------
5637:
5638: SELECT 'Tax Distributions Exist'
5639: INTO l_dummy
5640: FROM ap_invoice_distributions_all item_dist,
5641: ap_invoice_distributions_all tax_dist
5642: WHERE tax_dist.invoice_id = p_invoice_id
5643: and tax_dist.invoice_id = item_dist.invoice_id
5644: and tax_dist.charge_applicable_to_dist_id = item_dist.invoice_distribution_id
5637:
5638: SELECT 'Tax Distributions Exist'
5639: INTO l_dummy
5640: FROM ap_invoice_distributions_all item_dist,
5641: ap_invoice_distributions_all tax_dist
5642: WHERE tax_dist.invoice_id = p_invoice_id
5643: and tax_dist.invoice_id = item_dist.invoice_id
5644: and tax_dist.charge_applicable_to_dist_id = item_dist.invoice_distribution_id
5645: and tax_dist.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX', 'TIPV', 'TRV', 'TERV')