[Home] [Help]
642: is
643: PRAGMA AUTONOMOUS_TRANSACTION;
644: BEGIN
645: select org_id into p_org_id
646: from ap_invoices_all
647: where invoice_id = p_invoice_id;
648: EXCEPTION
649: WHEN NO_DATA_FOUND THEN
650: p_org_id := NULL;
949: WHERE (pan_no IN ('PANAPPLIED','PANNOTAVBL','PANINVALID')
950: OR (pan_no IS NOT NULL AND confirm_pan_flag IS NULL))
951: AND vendor_site_id = 0
952: AND vendor_id = (SELECT vendor_id
953: FROM ap_invoices_all
954: WHERE invoice_id = p_invoice_id
955: )
956: )
957: )
1036:
1037: /*START, Added by xiao for Bug#6596019*/
1038: cursor c_get_prepayment_amt(p_invoice_id number) is
1039: select amount_paid
1040: from ap_invoices_all
1041: where invoice_id = p_invoice_id;
1042:
1043: -- Modified by Jia for FP Bug#7368735, Begin
1044: ---------------------------------------------------------
1117: ln_threshold_slab_id_after number;
1118: ln_threshold_slab_id_single number;
1119: lv_generate_all_invoices varchar2(1);
1120: ln_threshold_trx_id number;
1121: lv_tds_invoice_num ap_invoices_all.invoice_num%type;
1122: lv_tds_cm_num ap_invoices_all.invoice_num%type;
1123:
1124: lv_pan_no JAI_AP_TDS_VENDOR_HDRS.pan_no%type;
1125: lv_tan_no jai_ap_tds_org_tan_v.org_tan_num %type; --rchandan for bug#4323338
1118: ln_threshold_slab_id_single number;
1119: lv_generate_all_invoices varchar2(1);
1120: ln_threshold_trx_id number;
1121: lv_tds_invoice_num ap_invoices_all.invoice_num%type;
1122: lv_tds_cm_num ap_invoices_all.invoice_num%type;
1123:
1124: lv_pan_no JAI_AP_TDS_VENDOR_HDRS.pan_no%type;
1125: lv_tan_no jai_ap_tds_org_tan_v.org_tan_num %type; --rchandan for bug#4323338
1126: lv_confirm_pan_flag JAI_AP_TDS_VENDOR_HDRS.confirm_pan_flag%type; /*Bug 11896260*/
1944: p_process_message out nocopy varchar2
1945: )
1946: is
1947:
1948: cursor c_ap_invoices_all(cp_invoice_id number) is
1949: select invoice_num,
1950: vendor_id,
1951: vendor_site_id,
1952: invoice_currency_code,
1962: nvl(exchange_rate, 1) exchange_rate,
1963: set_of_books_id,
1964: payment_method_code, -- Bug 7109056
1965: accts_pay_code_combination_id -- Bug 9759709
1966: from ap_invoices_all
1967: where invoice_id = cp_invoice_id;
1968:
1969: cursor c_po_vendor_sites_all(cp_vendor_id number, cp_vendor_site_id number) is
1970: select terms_id,
2007: select payment_priority
2008: from ap_payment_schedules_all
2009: where invoice_id = p_invoice_id;
2010:
2011: r_ap_invoices_all c_ap_invoices_all%rowtype;
2012: r_ja_in_tax_codes c_ja_in_tax_codes%rowtype;
2013: r_po_vendor_sites_all c_po_vendor_sites_all%rowtype;
2014: r_po_vendors c_po_vendors%rowtype;
2015: r_gl_sets_of_books c_gl_sets_of_books%rowtype;
2017:
2018:
2019: lv_source varchar2(30); --File.Sql.35 Cbabu := 'TDS';
2020:
2021: lv_invoice_to_tds_num ap_invoices_all.invoice_num%type;
2022: lv_invoice_to_vendor_num ap_invoices_all.invoice_num%type;
2023:
2024: lv_invoice_to_tds_type ap_invoices_all.invoice_type_lookup_code%type;
2025: lv_invoice_to_vendor_type ap_invoices_all.invoice_type_lookup_code%type;
2018:
2019: lv_source varchar2(30); --File.Sql.35 Cbabu := 'TDS';
2020:
2021: lv_invoice_to_tds_num ap_invoices_all.invoice_num%type;
2022: lv_invoice_to_vendor_num ap_invoices_all.invoice_num%type;
2023:
2024: lv_invoice_to_tds_type ap_invoices_all.invoice_type_lookup_code%type;
2025: lv_invoice_to_vendor_type ap_invoices_all.invoice_type_lookup_code%type;
2026:
2020:
2021: lv_invoice_to_tds_num ap_invoices_all.invoice_num%type;
2022: lv_invoice_to_vendor_num ap_invoices_all.invoice_num%type;
2023:
2024: lv_invoice_to_tds_type ap_invoices_all.invoice_type_lookup_code%type;
2025: lv_invoice_to_vendor_type ap_invoices_all.invoice_type_lookup_code%type;
2026:
2027: ln_invoice_to_tds_id ap_invoices_all.invoice_id%type;
2028: ln_invoice_to_vendor_id ap_invoices_all.invoice_id%type;
2021: lv_invoice_to_tds_num ap_invoices_all.invoice_num%type;
2022: lv_invoice_to_vendor_num ap_invoices_all.invoice_num%type;
2023:
2024: lv_invoice_to_tds_type ap_invoices_all.invoice_type_lookup_code%type;
2025: lv_invoice_to_vendor_type ap_invoices_all.invoice_type_lookup_code%type;
2026:
2027: ln_invoice_to_tds_id ap_invoices_all.invoice_id%type;
2028: ln_invoice_to_vendor_id ap_invoices_all.invoice_id%type;
2029:
2023:
2024: lv_invoice_to_tds_type ap_invoices_all.invoice_type_lookup_code%type;
2025: lv_invoice_to_vendor_type ap_invoices_all.invoice_type_lookup_code%type;
2026:
2027: ln_invoice_to_tds_id ap_invoices_all.invoice_id%type;
2028: ln_invoice_to_vendor_id ap_invoices_all.invoice_id%type;
2029:
2030: ln_invoice_to_tds_line_id ap_invoice_lines_interface.invoice_line_id%type;
2031: ln_invoice_to_vendor_line_id ap_invoice_lines_interface.invoice_line_id%type;
2024: lv_invoice_to_tds_type ap_invoices_all.invoice_type_lookup_code%type;
2025: lv_invoice_to_vendor_type ap_invoices_all.invoice_type_lookup_code%type;
2026:
2027: ln_invoice_to_tds_id ap_invoices_all.invoice_id%type;
2028: ln_invoice_to_vendor_id ap_invoices_all.invoice_id%type;
2029:
2030: ln_invoice_to_tds_line_id ap_invoice_lines_interface.invoice_line_id%type;
2031: ln_invoice_to_vendor_line_id ap_invoice_lines_interface.invoice_line_id%type;
2032:
2047: ld_accounting_date date;
2048: lv_open_period ap_invoice_distributions_all.period_name%type;
2049: ln_tax_amount number;
2050:
2051: lv_invoice_num ap_invoices_all.invoice_num%type;
2052: lv_source_attribute jai_ap_tds_invoices.source_attribute%TYPE ; --rchandan for bug#4428980
2053:
2054: ln_invoice_amount ap_invoices_all.invoice_amount%TYPE; --Added by Ramananda for Bug#4562801
2055:
2050:
2051: lv_invoice_num ap_invoices_all.invoice_num%type;
2052: lv_source_attribute jai_ap_tds_invoices.source_attribute%TYPE ; --rchandan for bug#4428980
2053:
2054: ln_invoice_amount ap_invoices_all.invoice_amount%TYPE; --Added by Ramananda for Bug#4562801
2055:
2056: lv_group_id VARCHAR2(80); --Added by Sanjikum for Bug#5131075(4722011)
2057:
2058: /* Bug 5722028. Added by Lakshmi Gopalsami
2093: p_process_message := 'TDS amount must be greater than 0 ';
2094: goto exit_from_procedure;
2095: end if;
2096:
2097: open c_ap_invoices_all(pn_invoice_id);
2098: fetch c_ap_invoices_all into r_ap_invoices_all;
2099: close c_ap_invoices_all;
2100:
2101: /*
2094: goto exit_from_procedure;
2095: end if;
2096:
2097: open c_ap_invoices_all(pn_invoice_id);
2098: fetch c_ap_invoices_all into r_ap_invoices_all;
2099: close c_ap_invoices_all;
2100:
2101: /*
2102: || moved this up from the under the cursor - c_po_vendor_sites_all by Ramananda for Bug#4562793
2095: end if;
2096:
2097: open c_ap_invoices_all(pn_invoice_id);
2098: fetch c_ap_invoices_all into r_ap_invoices_all;
2099: close c_ap_invoices_all;
2100:
2101: /*
2102: || moved this up from the under the cursor - c_po_vendor_sites_all by Ramananda for Bug#4562793
2103: */
2105: fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
2106: close c_ja_in_tax_codes;
2107:
2108: /*
2109: || open c_po_vendors(r_ap_invoices_all.vendor_id);
2110: || Commented the above and added the below by Ramananda for Bug#4562793
2111: */
2112: open c_po_vendors(r_ja_in_tax_codes.vendor_id);
2113: fetch c_po_vendors into r_po_vendors;
2113: fetch c_po_vendors into r_po_vendors;
2114: close c_po_vendors;
2115:
2116: /*
2117: || open c_po_vendor_sites_all(r_ap_invoices_all.vendor_id, r_ap_invoices_all.vendor_site_id);
2118: || Commented the above and added the below by sanjikum for Bug#4562793
2119: */
2120: open c_po_vendor_sites_all(r_ja_in_tax_codes.vendor_id, r_ja_in_tax_codes.vendor_site_id);
2121: fetch c_po_vendor_sites_all into r_po_vendor_sites_all;
2120: open c_po_vendor_sites_all(r_ja_in_tax_codes.vendor_id, r_ja_in_tax_codes.vendor_site_id);
2121: fetch c_po_vendor_sites_all into r_po_vendor_sites_all;
2122: close c_po_vendor_sites_all;
2123:
2124: open c_gl_sets_of_books(r_ap_invoices_all.set_of_books_id);
2125: fetch c_gl_sets_of_books into r_gl_sets_of_books;
2126: close c_gl_sets_of_books;
2127:
2128: /*Bug # 7410219 - Derive the Section Name*/
2160: open c_get_ja_in_ap_inv_id;
2161: fetch c_get_ja_in_ap_inv_id into lv_ja_in_ap_inv_id;
2162: close c_get_ja_in_ap_inv_id;
2163:
2164: lv_invoice_num := substr(r_ap_invoices_all.invoice_num, 1, 30);
2165:
2166: /* Invoice Numbers, type for the invoice pair that is being created */
2167: if ( (pv_tds_event = 'INVOICE VALIDATE') or (pv_tds_event like 'THRESHOLD TRANSITION%') ) then
2168:
2261: Added org_id to ap_utilities_pkg
2262: */
2263: lv_open_period:= ap_utilities_pkg.get_current_gl_date
2264: (pd_accounting_date,
2265: r_ap_invoices_all.org_id
2266: );
2267:
2268: /* Bug 4559756. Added by Lakshmi Gopalsami
2269: Added org_id to ap_utilities_pkg
2275: (
2276: pd_accounting_date,
2277: lv_open_period,
2278: ld_accounting_date,
2279: r_ap_invoices_all.org_id
2280: );
2281:
2282: if lv_open_period is null then
2283: p_process_flag := 'E';
2294: lv_group_id := to_char(pn_invoice_id)||pv_tds_event;
2295: pv_invoice_date := ld_accounting_date; --Added by Bgowrava for Bug#9186263
2296: ELSE
2297: lv_group_id := to_char(pn_invoice_id);
2298: pv_invoice_date := r_ap_invoices_all.invoice_date; --Added by Bgowrava for Bug#9186263
2299: END IF;
2300:
2301:
2302: /* Invoice to TDS Authority */
2360: p_voucher_num => lv_invoice_to_tds_num,
2361: --p_payment_method_lookup_code => lv_payment_method_lookup_code,
2362: --commented by Sanjikum for Bug#4482462
2363: p_pay_group_lookup_code => lv_pay_group_lookup_code,
2364: p_org_id => r_ap_invoices_all.org_id,
2365: p_attribute_category => 'India Original Invoice for TDS',
2366: p_attribute1 => pn_invoice_id,
2367: --added the below by Sanjikum for Bug#5131075(4722011)
2368: p_group_id => lv_group_id -- Bug# 6119216, changed to lv_group_id instead of to_char(p_invoice_id)
2407: /* Bug 12965614. Added by Avanija
2408: * Added the following condition to handle for prepayment unapplication for foreign currency invoices.
2409: */
2410: IF pv_tds_event = 'PREPAYMENT UNAPPLICATION' THEN --2
2411: IF r_ap_invoices_all.invoice_currency_code <> r_gl_sets_of_books.currency_code THEN --3
2412: ln_invoice_to_vendor_amount := round( ln_invoice_to_vendor_amount, g_inr_currency_rounding);
2413: IF trunc(pd_creation_date) >= trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
2414: ln_invoice_to_vendor_amount := get_rnded_value(ln_invoice_to_vendor_amount);
2415: END IF;
2412: ln_invoice_to_vendor_amount := round( ln_invoice_to_vendor_amount, g_inr_currency_rounding);
2413: IF trunc(pd_creation_date) >= trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
2414: ln_invoice_to_vendor_amount := get_rnded_value(ln_invoice_to_vendor_amount);
2415: END IF;
2416: ln_invoice_to_vendor_amount := round( ln_invoice_to_vendor_amount / r_ap_invoices_all.exchange_rate, g_fcy_currency_rounding);
2417: END IF; --3
2418:
2419: ELSE --2
2420:
2417: END IF; --3
2418:
2419: ELSE --2
2420:
2421: if r_ap_invoices_all.invoice_currency_code <> r_gl_sets_of_books.currency_code then
2422: /*START, Bgowrava for Bug#8995604 , Adding below IF statement to round the INR TDS CM invoice to
2423: the TDS rounding factor before converting it to a foreign currency CM*/
2424: ln_invoice_to_vendor_amount := round( ln_invoice_to_vendor_amount, g_inr_currency_rounding);
2425: IF trunc(pd_creation_date) >= trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date)
2426: and pn_threshold_grp_id is not null THEN
2427: ln_invoice_to_vendor_amount := get_rnded_value(ln_invoice_to_vendor_amount);
2428: END IF;
2429: /*END, Bgowrava for Bug#8995604 */
2430: ln_invoice_to_vendor_amount := round( ln_invoice_to_vendor_amount / r_ap_invoices_all.exchange_rate, g_fcy_currency_rounding);
2431: ELSE
2432: ln_invoice_to_vendor_amount := round( ln_invoice_to_vendor_amount, g_inr_currency_rounding);
2433: /*START, Bgowrava for Bug#8995604 , Adding below IF statement to round the INR TDS CM invoice to the TDS rounding factor*/
2434: IF trunc(pd_creation_date) >= trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date)
2450: fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value after round per setup - vendor invoice'||ln_invoice_to_vendor_amount);
2451: END IF; */ --commented by bgowrava for Bug#8995604
2452:
2453:
2454: if upper(r_ap_invoices_all.exchange_rate_type) = 'USER' then
2455: ln_exchange_rate := r_ap_invoices_all.exchange_rate;
2456: else
2457: ln_exchange_rate := null;
2458: end if;
2451: END IF; */ --commented by bgowrava for Bug#8995604
2452:
2453:
2454: if upper(r_ap_invoices_all.exchange_rate_type) = 'USER' then
2455: ln_exchange_rate := r_ap_invoices_all.exchange_rate;
2456: else
2457: ln_exchange_rate := null;
2458: end if;
2459:
2462: /*Bug 9759709 - Liability Account for the Credit Note must be fetched from the Standard Invoice
2463: If it is not present, then it needs to be defaulted from Vendor Site Setup. Passing NULL to
2464: parameter p_accts_pay_ccid in jai_ap_utils_pkg.insert_ap_inv_interface would cause it to default
2465: from setup*/
2466: if (r_ap_invoices_all.accts_pay_code_combination_id is not NULL) then
2467: ln_accts_pay_ccid := r_ap_invoices_all.accts_pay_code_combination_id;
2468: else
2469: ln_accts_pay_ccid := NULL;
2470: end if;
2463: If it is not present, then it needs to be defaulted from Vendor Site Setup. Passing NULL to
2464: parameter p_accts_pay_ccid in jai_ap_utils_pkg.insert_ap_inv_interface would cause it to default
2465: from setup*/
2466: if (r_ap_invoices_all.accts_pay_code_combination_id is not NULL) then
2467: ln_accts_pay_ccid := r_ap_invoices_all.accts_pay_code_combination_id;
2468: else
2469: ln_accts_pay_ccid := NULL;
2470: end if;
2471:
2476: p_jai_source => lv_this_procedure,
2477: p_invoice_id => ln_invoice_to_vendor_id,
2478: p_invoice_num => lv_invoice_to_vendor_num,
2479: p_invoice_type_lookup_code => lv_invoice_to_vendor_type,
2480: p_invoice_date => pv_invoice_date,--r_ap_invoices_all.invoice_date, --Modified to ld_accounting_date for Bug#9186263
2481: p_gl_date => ld_accounting_date,
2482: p_vendor_id => r_ap_invoices_all.vendor_id,
2483: p_vendor_site_id => r_ap_invoices_all.vendor_site_id,
2484: p_invoice_amount => ln_invoice_to_vendor_amount,
2478: p_invoice_num => lv_invoice_to_vendor_num,
2479: p_invoice_type_lookup_code => lv_invoice_to_vendor_type,
2480: p_invoice_date => pv_invoice_date,--r_ap_invoices_all.invoice_date, --Modified to ld_accounting_date for Bug#9186263
2481: p_gl_date => ld_accounting_date,
2482: p_vendor_id => r_ap_invoices_all.vendor_id,
2483: p_vendor_site_id => r_ap_invoices_all.vendor_site_id,
2484: p_invoice_amount => ln_invoice_to_vendor_amount,
2485: p_invoice_currency_code => r_ap_invoices_all.invoice_currency_code,
2486: p_exchange_rate => ln_exchange_rate,
2479: p_invoice_type_lookup_code => lv_invoice_to_vendor_type,
2480: p_invoice_date => pv_invoice_date,--r_ap_invoices_all.invoice_date, --Modified to ld_accounting_date for Bug#9186263
2481: p_gl_date => ld_accounting_date,
2482: p_vendor_id => r_ap_invoices_all.vendor_id,
2483: p_vendor_site_id => r_ap_invoices_all.vendor_site_id,
2484: p_invoice_amount => ln_invoice_to_vendor_amount,
2485: p_invoice_currency_code => r_ap_invoices_all.invoice_currency_code,
2486: p_exchange_rate => ln_exchange_rate,
2487: p_exchange_rate_type => r_ap_invoices_all.exchange_rate_type,
2481: p_gl_date => ld_accounting_date,
2482: p_vendor_id => r_ap_invoices_all.vendor_id,
2483: p_vendor_site_id => r_ap_invoices_all.vendor_site_id,
2484: p_invoice_amount => ln_invoice_to_vendor_amount,
2485: p_invoice_currency_code => r_ap_invoices_all.invoice_currency_code,
2486: p_exchange_rate => ln_exchange_rate,
2487: p_exchange_rate_type => r_ap_invoices_all.exchange_rate_type,
2488: p_exchange_date => r_ap_invoices_all.exchange_date,
2489: p_terms_id => r_ap_invoices_all.terms_id,
2483: p_vendor_site_id => r_ap_invoices_all.vendor_site_id,
2484: p_invoice_amount => ln_invoice_to_vendor_amount,
2485: p_invoice_currency_code => r_ap_invoices_all.invoice_currency_code,
2486: p_exchange_rate => ln_exchange_rate,
2487: p_exchange_rate_type => r_ap_invoices_all.exchange_rate_type,
2488: p_exchange_date => r_ap_invoices_all.exchange_date,
2489: p_terms_id => r_ap_invoices_all.terms_id,
2490: p_description => lv_invoice_to_vendor_num,
2491: p_last_update_date => sysdate,
2484: p_invoice_amount => ln_invoice_to_vendor_amount,
2485: p_invoice_currency_code => r_ap_invoices_all.invoice_currency_code,
2486: p_exchange_rate => ln_exchange_rate,
2487: p_exchange_rate_type => r_ap_invoices_all.exchange_rate_type,
2488: p_exchange_date => r_ap_invoices_all.exchange_date,
2489: p_terms_id => r_ap_invoices_all.terms_id,
2490: p_description => lv_invoice_to_vendor_num,
2491: p_last_update_date => sysdate,
2492: p_last_updated_by => fnd_global.user_id,
2485: p_invoice_currency_code => r_ap_invoices_all.invoice_currency_code,
2486: p_exchange_rate => ln_exchange_rate,
2487: p_exchange_rate_type => r_ap_invoices_all.exchange_rate_type,
2488: p_exchange_date => r_ap_invoices_all.exchange_date,
2489: p_terms_id => r_ap_invoices_all.terms_id,
2490: p_description => lv_invoice_to_vendor_num,
2491: p_last_update_date => sysdate,
2492: p_last_updated_by => fnd_global.user_id,
2493: p_last_update_login => fnd_global.login_id,
2495: p_created_by => fnd_global.user_id,
2496: p_source => lv_source,
2497: p_voucher_num => lv_invoice_to_vendor_num,
2498: -- Bug 7109056. Added by Lakshmi Gopalsami
2499: p_payment_method_code => r_ap_invoices_all.payment_method_code,
2500: --commented by Sanjikum for Bug#4482462
2501: p_pay_group_lookup_code => r_ap_invoices_all.pay_group_lookup_code,
2502: p_goods_received_date => r_ap_invoices_all.goods_received_date,
2503: p_invoice_received_date => r_ap_invoices_all.invoice_received_date,
2497: p_voucher_num => lv_invoice_to_vendor_num,
2498: -- Bug 7109056. Added by Lakshmi Gopalsami
2499: p_payment_method_code => r_ap_invoices_all.payment_method_code,
2500: --commented by Sanjikum for Bug#4482462
2501: p_pay_group_lookup_code => r_ap_invoices_all.pay_group_lookup_code,
2502: p_goods_received_date => r_ap_invoices_all.goods_received_date,
2503: p_invoice_received_date => r_ap_invoices_all.invoice_received_date,
2504: p_org_id => r_ap_invoices_all.org_id,
2505: p_attribute_category => 'India Original Invoice for TDS',
2498: -- Bug 7109056. Added by Lakshmi Gopalsami
2499: p_payment_method_code => r_ap_invoices_all.payment_method_code,
2500: --commented by Sanjikum for Bug#4482462
2501: p_pay_group_lookup_code => r_ap_invoices_all.pay_group_lookup_code,
2502: p_goods_received_date => r_ap_invoices_all.goods_received_date,
2503: p_invoice_received_date => r_ap_invoices_all.invoice_received_date,
2504: p_org_id => r_ap_invoices_all.org_id,
2505: p_attribute_category => 'India Original Invoice for TDS',
2506: p_attribute1 => pn_invoice_id,
2499: p_payment_method_code => r_ap_invoices_all.payment_method_code,
2500: --commented by Sanjikum for Bug#4482462
2501: p_pay_group_lookup_code => r_ap_invoices_all.pay_group_lookup_code,
2502: p_goods_received_date => r_ap_invoices_all.goods_received_date,
2503: p_invoice_received_date => r_ap_invoices_all.invoice_received_date,
2504: p_org_id => r_ap_invoices_all.org_id,
2505: p_attribute_category => 'India Original Invoice for TDS',
2506: p_attribute1 => pn_invoice_id,
2507: --commented the above and added the below by Sanjikum for Bug#5131075(4722011)
2500: --commented by Sanjikum for Bug#4482462
2501: p_pay_group_lookup_code => r_ap_invoices_all.pay_group_lookup_code,
2502: p_goods_received_date => r_ap_invoices_all.goods_received_date,
2503: p_invoice_received_date => r_ap_invoices_all.invoice_received_date,
2504: p_org_id => r_ap_invoices_all.org_id,
2505: p_attribute_category => 'India Original Invoice for TDS',
2506: p_attribute1 => pn_invoice_id,
2507: --commented the above and added the below by Sanjikum for Bug#5131075(4722011)
2508: p_group_id => lv_group_id,
2574: fnd_file.put_line(FND_FILE.LOG, 'tax rate'|| r_ja_in_tax_codes.tax_rate);
2575: fnd_file.put_line(FND_FILE.LOG, 'tds amt'||ln_invoice_to_tds_amount);
2576: fnd_file.put_line(FND_FILE.LOG, 'sec code '||r_ja_in_tax_codes.section_code);
2577: fnd_file.put_line(FND_FILE.LOG, 'stformtype '||r_ja_in_tax_codes.stform_type);
2578: fnd_file.put_line(FND_FILE.LOG, 'org id '|| r_ap_invoices_all.org_id);
2579: fnd_file.put_line(FND_FILE.LOG, 'src att'||lv_source_attribute);
2580:
2581: IF NVL(ln_invoice_amount,0) <> 0 THEN --Added the condition by bgowrava for Bug#4549019
2582: insert into JAI_AP_TDS_INVOICES
2612: r_ja_in_tax_codes.tax_rate,
2613: ln_invoice_to_tds_amount,
2614: r_ja_in_tax_codes.section_code,
2615: r_ja_in_tax_codes.stform_type,
2616: --r_ap_invoices_all.org_id,
2617: r_ap_invoices_all.org_id,
2618: lv_source_attribute, --rchandan for bug#4428980
2619: /* Ramananda for bug#4560109 during R12 Sanity Testing. Added the WHO columns */
2620: fnd_global.user_id ,
2613: ln_invoice_to_tds_amount,
2614: r_ja_in_tax_codes.section_code,
2615: r_ja_in_tax_codes.stform_type,
2616: --r_ap_invoices_all.org_id,
2617: r_ap_invoices_all.org_id,
2618: lv_source_attribute, --rchandan for bug#4428980
2619: /* Ramananda for bug#4560109 during R12 Sanity Testing. Added the WHO columns */
2620: fnd_global.user_id ,
2621: sysdate ,
2630: ln_tds_rnding_factor := 0;
2631: ln_tds_rnded_amt := pn_tax_amount;
2632: /* Added the below if condition for bug 12965614 */
2633: IF pv_tds_event = 'PREPAYMENT UNAPPLICATION' THEN --2
2634: IF r_ap_invoices_all.invoice_currency_code <> r_gl_sets_of_books.currency_code THEN --3
2635: ln_tds_rnded_amt := ROUND(pn_calc_tax_amount , g_inr_currency_rounding);
2636: IF (trunc(pd_creation_date) >= trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date)) THEN --4
2637: ln_tds_rnding_factor := jai_ap_tds_generation_pkg.gn_tds_rounding_factor;
2638: ln_tds_rnded_amt := get_rnded_value(ln_tds_rnded_amt);
2636: IF (trunc(pd_creation_date) >= trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date)) THEN --4
2637: ln_tds_rnding_factor := jai_ap_tds_generation_pkg.gn_tds_rounding_factor;
2638: ln_tds_rnded_amt := get_rnded_value(ln_tds_rnded_amt);
2639: END IF; --4
2640: ln_tds_rnded_amt := ROUND(pn_calc_tax_amount / r_ap_invoices_all.exchange_rate, g_fcy_currency_rounding);
2641: END IF; --3
2642: ELSE --2
2643: /*IF pv_tds_event NOT IN
2644: -- Bug 7280925. commented by Lakshmi Gopalsami ('INVOICE CANCEL',
2642: ELSE --2
2643: /*IF pv_tds_event NOT IN
2644: -- Bug 7280925. commented by Lakshmi Gopalsami ('INVOICE CANCEL',
2645: ('PREPAYMENT UNAPPLICATION') THEN */
2646: IF r_ap_invoices_all.invoice_currency_code = r_gl_sets_of_books.currency_code THEN
2647: ln_tds_rnded_amt := ROUND(pn_tax_amount , g_inr_currency_rounding);
2648: --moved this if-endif block from below to here for bug#8995604
2649: ELSE
2650: ln_tds_rnded_amt := ROUND(pn_tax_amount , g_inr_currency_rounding);
2659: ln_tds_rnding_factor := jai_ap_tds_generation_pkg.gn_tds_rounding_factor;
2660: ln_tds_rnded_amt := get_rnded_value(ln_tds_rnded_amt);
2661: fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value after round before insert into trxs'||ln_tds_rnded_amt);
2662: END IF;
2663: ln_tds_rnded_amt := ROUND(pn_tax_amount / r_ap_invoices_all.exchange_rate, g_fcy_currency_rounding);
2664: END IF ;
2665: fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value before round '||ln_tds_rnded_amt);
2666:
2667: END IF;
2725: lv_invoice_to_tds_num ,
2726: lv_invoice_to_tds_type ,
2727: r_gl_sets_of_books.currency_code ,
2728: ln_invoice_to_tds_amount ,
2729: r_ap_invoices_all.vendor_id ,
2730: r_ap_invoices_all.vendor_site_id ,
2731: lv_invoice_to_vendor_num ,
2732: lv_invoice_to_vendor_type ,
2733: r_ap_invoices_all.invoice_currency_code ,
2726: lv_invoice_to_tds_type ,
2727: r_gl_sets_of_books.currency_code ,
2728: ln_invoice_to_tds_amount ,
2729: r_ap_invoices_all.vendor_id ,
2730: r_ap_invoices_all.vendor_site_id ,
2731: lv_invoice_to_vendor_num ,
2732: lv_invoice_to_vendor_type ,
2733: r_ap_invoices_all.invoice_currency_code ,
2734: ln_invoice_to_vendor_amount ,
2729: r_ap_invoices_all.vendor_id ,
2730: r_ap_invoices_all.vendor_site_id ,
2731: lv_invoice_to_vendor_num ,
2732: lv_invoice_to_vendor_type ,
2733: r_ap_invoices_all.invoice_currency_code ,
2734: ln_invoice_to_vendor_amount ,
2735: r_ap_payment_schedules_all.payment_priority ,
2736: r_ap_invoices_all.exchange_rate ,
2737: fnd_global.user_id ,
2732: lv_invoice_to_vendor_type ,
2733: r_ap_invoices_all.invoice_currency_code ,
2734: ln_invoice_to_vendor_amount ,
2735: r_ap_payment_schedules_all.payment_priority ,
2736: r_ap_invoices_all.exchange_rate ,
2737: fnd_global.user_id ,
2738: sysdate ,
2739: fnd_global.user_id ,
2740: sysdate ,
2790: FROM jai_ap_tds_thgrp_audits jatta
2791: WHERE jatit.invoice_id = jatta.invoice_id
2792: AND (jatta.trx_invoice_amount = 0 OR jatta.trx_invoice_cancel_amount = 0)
2793: AND EXISTS (SELECT 1
2794: FROM ap_invoices_all aia
2795: WHERE aia.invoice_id = jatit.invoice_id
2796: AND aia.cancelled_date IS NOT NULL)
2797: )
2798: minus
2822: where threshold_grp_id = cp_threshold_grp_id;
2823:
2824: cursor c_jai_cancelled_amount(cp_invoice_id number) is
2825: select nvl(cancelled_amount,0)
2826: from ap_invoices_all
2827: where invoice_id=cp_invoice_id;
2828:
2829: cursor c_get_threshold(p_vendor_id number, p_vendor_site_id number, p_tds_section_code varchar2) is
2830: select threshold_hdr_id
2878: )
2879: )
2880: AND EXISTS /* check whether iinvoice is not cancelled*/
2881: ( SELECT invoice_id
2882: FROM ap_invoices_all ai
2883: WHERE ai.invoice_id = jatit.invoice_id
2884: AND ai.cancelled_Date IS NULL
2885: AND ai.cancelled_amount IS NULL
2886: );
2949: r_jai_ap_tds_thhold_taxes c_jai_ap_tds_thhold_taxes%rowtype;
2950: r_jai_ap_tds_thhold_grps c_jai_ap_tds_thhold_grps%rowtype;
2951: r_ja_in_tax_codes c_ja_in_tax_codes%rowtype;
2952: ln_thhold_transition_tax_amt number;
2953: lv_tds_invoice_num ap_invoices_all.invoice_num%type;
2954: lv_tds_cm_num ap_invoices_all.invoice_num%type;
2955: ln_threshold_trx_id number;
2956: ln_threshold_grp_audit_id number;
2957: ln_threshold_grp_id number;
2950: r_jai_ap_tds_thhold_grps c_jai_ap_tds_thhold_grps%rowtype;
2951: r_ja_in_tax_codes c_ja_in_tax_codes%rowtype;
2952: ln_thhold_transition_tax_amt number;
2953: lv_tds_invoice_num ap_invoices_all.invoice_num%type;
2954: lv_tds_cm_num ap_invoices_all.invoice_num%type;
2955: ln_threshold_trx_id number;
2956: ln_threshold_grp_audit_id number;
2957: ln_threshold_grp_id number;
2958:
3283: p_process_message out nocopy varchar2
3284: )
3285: is
3286:
3287: cursor c_ap_invoices_all(p_invoice_id number) is
3288: select vendor_id,
3289: vendor_site_id,
3290: org_id
3291: from ap_invoices_all
3287: cursor c_ap_invoices_all(p_invoice_id number) is
3288: select vendor_id,
3289: vendor_site_id,
3290: org_id
3291: from ap_invoices_all
3292: where invoice_id = p_invoice_id;
3293:
3294: cursor c_ja_in_po_vendor_sites(p_vendor_id number, p_vendor_site_id number) is
3295: select nvl( approved_invoice_flag, 'N' ) approved_invoice_flag
3297: where vendor_id = p_vendor_id
3298: and vendor_site_id = p_vendor_site_id;
3299:
3300:
3301: r_ap_invoices_all c_ap_invoices_all%rowtype;
3302:
3303: lb_result boolean;
3304: ln_import_request_id number;
3305: ln_approve_request_id number;
3320: /* Invoke payables open interface */
3321:
3322: lb_result := fnd_request.set_mode(true);
3323:
3324: open c_ap_invoices_all(p_invoice_id);
3325: fetch c_ap_invoices_all into r_ap_invoices_all;
3326: close c_ap_invoices_all;
3327:
3328: lv_batch_name := jai_ap_utils_pkg.get_tds_invoice_batch(p_invoice_id, r_ap_invoices_all.org_id); --Ramananda for bug#4584221 --added org_id parameter for Bug#9149941
3321:
3322: lb_result := fnd_request.set_mode(true);
3323:
3324: open c_ap_invoices_all(p_invoice_id);
3325: fetch c_ap_invoices_all into r_ap_invoices_all;
3326: close c_ap_invoices_all;
3327:
3328: lv_batch_name := jai_ap_utils_pkg.get_tds_invoice_batch(p_invoice_id, r_ap_invoices_all.org_id); --Ramananda for bug#4584221 --added org_id parameter for Bug#9149941
3329:
3322: lb_result := fnd_request.set_mode(true);
3323:
3324: open c_ap_invoices_all(p_invoice_id);
3325: fetch c_ap_invoices_all into r_ap_invoices_all;
3326: close c_ap_invoices_all;
3327:
3328: lv_batch_name := jai_ap_utils_pkg.get_tds_invoice_batch(p_invoice_id, r_ap_invoices_all.org_id); --Ramananda for bug#4584221 --added org_id parameter for Bug#9149941
3329:
3330: ln_import_request_id :=
3324: open c_ap_invoices_all(p_invoice_id);
3325: fetch c_ap_invoices_all into r_ap_invoices_all;
3326: close c_ap_invoices_all;
3327:
3328: lv_batch_name := jai_ap_utils_pkg.get_tds_invoice_batch(p_invoice_id, r_ap_invoices_all.org_id); --Ramananda for bug#4584221 --added org_id parameter for Bug#9149941
3329:
3330: ln_import_request_id :=
3331: fnd_request.submit_request
3332: (
3359: fnd_global.login_id
3360: );
3361:
3362: /* Get vendor and site for the invoice */
3363: /* open c_ap_invoices_all(p_invoice_id);
3364: fetch c_ap_invoices_all into r_ap_invoices_all;
3365: close c_ap_invoices_all;*/ --moved this code to the start of the procedure for Bug#9149941
3366:
3367: /* Check if Pre-approved TDS invoices setup has been set for the vendor */
3360: );
3361:
3362: /* Get vendor and site for the invoice */
3363: /* open c_ap_invoices_all(p_invoice_id);
3364: fetch c_ap_invoices_all into r_ap_invoices_all;
3365: close c_ap_invoices_all;*/ --moved this code to the start of the procedure for Bug#9149941
3366:
3367: /* Check if Pre-approved TDS invoices setup has been set for the vendor */
3368:
3361:
3362: /* Get vendor and site for the invoice */
3363: /* open c_ap_invoices_all(p_invoice_id);
3364: fetch c_ap_invoices_all into r_ap_invoices_all;
3365: close c_ap_invoices_all;*/ --moved this code to the start of the procedure for Bug#9149941
3366:
3367: /* Check if Pre-approved TDS invoices setup has been set for the vendor */
3368:
3369: /* Check for vendor and site */
3366:
3367: /* Check if Pre-approved TDS invoices setup has been set for the vendor */
3368:
3369: /* Check for vendor and site */
3370: open c_ja_in_po_vendor_sites(r_ap_invoices_all.vendor_id, r_ap_invoices_all.vendor_site_id);
3371: fetch c_ja_in_po_vendor_sites into lv_approved_invoice_flag;
3372: close c_ja_in_po_vendor_sites;
3373:
3374: if nvl(lv_approved_invoice_flag, 'N') <> 'Y' then
3372: close c_ja_in_po_vendor_sites;
3373:
3374: if nvl(lv_approved_invoice_flag, 'N') <> 'Y' then
3375: /* Pre-approved TDS invoice is not set for vendor and site, Check for vendor and null site */
3376: open c_ja_in_po_vendor_sites(r_ap_invoices_all.vendor_id, 0);
3377: fetch c_ja_in_po_vendor_sites into lv_approved_invoice_flag;
3378: close c_ja_in_po_vendor_sites;
3379: end if;
3380:
3397: sysdate,
3398: false,
3399: ln_import_request_id,
3400: p_invoice_id,
3401: r_ap_invoices_all.vendor_id,
3402: r_ap_invoices_all.vendor_site_id,
3403: p_start_thhold_trx_id
3404: );
3405:
3398: false,
3399: ln_import_request_id,
3400: p_invoice_id,
3401: r_ap_invoices_all.vendor_id,
3402: r_ap_invoices_all.vendor_site_id,
3403: p_start_thhold_trx_id
3404: );
3405:
3406:
3443:
3444: CURSOR c_jai_chk_tds_inv (p_invoice_id number) IS
3445: SELECT invoice_id, org_id,
3446: set_of_books_id -- bug 6819855. Added by Lakshmi Gopalsami
3447: FROM ap_invoices_all
3448: WHERE invoice_id = p_invoice_id;
3449:
3450: lb_request_status boolean;
3451: lv_phase varchar2(100);
3722: and vendor_site_id = p_vendor_site_id
3723: and invoice_to_vendor_id is null;
3724:
3725: ln_threshold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%type;
3726: ln_invoice_id ap_invoices_all.invoice_id%type;
3727:
3728:
3729: begin
3730:
4319:
4320: -- Bug 5722028. Added by Lakshmi Gopalsami
4321: cursor get_creation_date is
4322: select creation_date
4323: from ap_invoices_all
4324: where invoice_id = p_invoice_id;
4325: ld_creation_date DATE;
4326: -- End for bug 5722028.
4327:
4429: b.invoice_currency_code,
4430: b.exchange_rate,
4431: b.set_of_books_id
4432: FROM ap_invoice_distributions_all a,
4433: ap_invoices_all b
4434: WHERE a.invoice_id = b.invoice_id
4435: AND b.invoice_id = p_invoice_id
4436: AND a.line_type_lookup_code = 'PREPAY'
4437: AND b.source <> 'TDS'
4740: r_threshold_grp c_threshold_grp%ROWTYPE;
4741: v_si_flag VARCHAR2(1);
4742: v_pp_flag VARCHAR2(1);
4743: lv_tds_event jai_ap_tds_thhold_trxs.tds_event%TYPE;
4744: lv_tds_invoice_num ap_invoices_all.invoice_num%type;
4745: lv_tds_cm_num ap_invoices_all.invoice_num%type;
4746: ln_threshold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%TYPE;
4747: ln_threshold_grp_audit_id jai_ap_tds_thgrp_audits.threshold_grp_audit_id%TYPE;
4748: ln_threshold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
4741: v_si_flag VARCHAR2(1);
4742: v_pp_flag VARCHAR2(1);
4743: lv_tds_event jai_ap_tds_thhold_trxs.tds_event%TYPE;
4744: lv_tds_invoice_num ap_invoices_all.invoice_num%type;
4745: lv_tds_cm_num ap_invoices_all.invoice_num%type;
4746: ln_threshold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%TYPE;
4747: ln_threshold_grp_audit_id jai_ap_tds_thgrp_audits.threshold_grp_audit_id%TYPE;
4748: ln_threshold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
4749:
4766: )
4767: )
4768: AND EXISTS /* check whether iinvoice is not cancelled*/
4769: ( SELECT invoice_id
4770: FROM ap_invoices_all ai
4771: WHERE ai.invoice_id = jatit.invoice_id
4772: AND ai.cancelled_Date IS NULL
4773: AND ai.cancelled_amount IS NULL
4774: );