[Home] [Help]
375: FROM JAI_AP_TDS_DEFAULT jatd
376: WHERE jatd.INVOICE_ID = p_inv_id
377: --AND jatd.TDS_SECTION_CODE IS NOT NULL commented out by zhiwei.xin for bug 13731393 on 29-Feb-2012
378: AND NOT EXISTS ( SELECT 1
379: FROM JAI_AP_TDS_INV_TAXES jatit
380: WHERE jatit.INVOICE_ID = p_inv_id
381: AND jatit.INVOICE_DISTRIBUTION_ID = jatd.INVOICE_DISTRIBUTION_ID
382: AND NVL(jatit.TAX_CATEGORY_OVERRIDEN,'N') = 'Y');
383:
462: SELECT *
463: FROM JAI_CMN_TAXES_ALL
464: WHERE TAX_ID = cp_tax_id;
465:
466: -- cursor to check if record exists in JAI_AP_TDS_INV_TAXES
467: cursor c_check_rec_exists(cp_inv_dist_id number, cp_line_no number)
468: is
469: SELECT 'Y'
470: FROM JAI_AP_TDS_INV_TAXES
466: -- cursor to check if record exists in JAI_AP_TDS_INV_TAXES
467: cursor c_check_rec_exists(cp_inv_dist_id number, cp_line_no number)
468: is
469: SELECT 'Y'
470: FROM JAI_AP_TDS_INV_TAXES
471: WHERE INVOICE_ID = p_inv_id
472: AND INVOICE_DISTRIBUTION_ID = cp_inv_dist_id
473: AND TAX_LINE_NO = cp_line_no;
474:
776:
777: -- clear tables
778: if p_redefault_section_code = 'Y' then
779: delete from JAI_AP_TDS_DEFAULT where INVOICE_ID = p_inv_id;
780: delete from JAI_AP_TDS_INV_TAXES where INVOICE_ID = p_inv_id and NVL(TAX_CATEGORY_OVERRIDEN,'N') = 'N'; --Added tax_category_overriden by Chong for bug#16248896
781: end if;
782:
783: -- get vendor type
784: open c_get_vendor_info;
816: open c_get_regime_attr_value('TAX_CATEGORY');
817: fetch c_get_regime_attr_value into ln_tax_ctg_id;
818: close c_get_regime_attr_value;
819:
820: \* 2. insert data into jai_ap_tds_inv_taxes. *\
821: for tax_ctg_lines_rec IN c_get_tax_ctg_lines(ln_tax_ctg_id)
822: loop
823: ln_line_no := tax_ctg_lines_rec.LINE_NO;
824: open c_get_tax_code_details(tax_ctg_lines_rec.TAX_ID);
824: open c_get_tax_code_details(tax_ctg_lines_rec.TAX_ID);
825: fetch c_get_tax_code_details into lr_tax_code_details;
826: close c_get_tax_code_details;
827:
828: INSERT INTO JAI_AP_TDS_INV_TAXES
829: (TDS_INV_TAX_ID,
830: INVOICE_ID,
831: AMOUNT,
832: VENDOR_ID,
853: LAST_UPDATED_BY,
854: LAST_UPDATE_DATE,
855: LAST_UPDATE_LOGIN)
856: VALUES
857: (JAI_AP_TDS_INV_TAXES_S.NEXTVAL,
858: p_inv_id,
859: lr_tax_code_details.TAX_AMOUNT,
860: ln_vendor_id,
861: ln_vendor_site_id,
1982:
1983: -- 'Y' : exist, 'N' : not exist
1984: if NVL(lv_rec_exists, 'N') = 'Y' then
1985:
1986: UPDATE JAI_AP_TDS_INV_TAXES
1987: SET SECTION_TYPE = lr_tax_code_details.SECTION_TYPE,
1988: TAX_LINE_NO = tax_ctg_lines_rec.LINE_NO,
1989: TAX_TYPE = lr_tax_code_details.TAX_TYPE,
1990: TAX_CATEGORY_ID = tax_ctg_lines_rec.TAX_CATEGORY_ID,
2007: AND TAX_LINE_NO = tax_ctg_lines_rec.LINE_NO;
2008:
2009: else
2010:
2011: INSERT INTO JAI_AP_TDS_INV_TAXES
2012: (TDS_INV_TAX_ID,
2013: INVOICE_ID,
2014: INVOICE_DISTRIBUTION_ID,
2015: DISTRIBUTION_LINE_NUMBER,
2042: LAST_UPDATE_DATE,
2043: LAST_UPDATE_LOGIN,
2044: INVOICE_LINE_NUMBER)
2045: VALUES
2046: (JAI_AP_TDS_INV_TAXES_S.NEXTVAL,
2047: ap_tds_def_rec.INVOICE_ID,
2048: ap_tds_def_rec.INVOICE_DISTRIBUTION_ID,
2049: ap_tds_def_rec.DISTRIBUTION_LINE_NUMBER,
2050: --commented out by zhiwei.xin for bug 13731393 on 29-Feb-2012
2089: end if;
2090:
2091: end loop; -- c_get_tax_ctg_lines
2092:
2093: DELETE FROM JAI_AP_TDS_INV_TAXES
2094: WHERE INVOICE_ID = p_inv_id
2095: AND INVOICE_DISTRIBUTION_ID = ap_tds_def_rec.INVOICE_DISTRIBUTION_ID
2096: --Commented out by zhiwei.xin for bug 13741305 on 22-Feb-2012
2097: --AND TAX_LINE_NO > ln_line_no;
2100: if ln_tax_ctg_id is NULL AND
2101: ap_tds_def_rec.TAX_CATEGORY_ID is NULL AND
2102: ap_tds_def_rec.TDS_SECTION_CODE is not NULL then
2103:
2104: INSERT INTO JAI_AP_TDS_INV_TAXES
2105: (TDS_INV_TAX_ID,
2106: INVOICE_ID,
2107: INVOICE_DISTRIBUTION_ID,
2108: DISTRIBUTION_LINE_NUMBER,
2122: LAST_UPDATE_DATE,
2123: LAST_UPDATE_LOGIN,
2124: INVOICE_LINE_NUMBER)
2125: VALUES
2126: (JAI_AP_TDS_INV_TAXES_S.NEXTVAL,
2127: ap_tds_def_rec.INVOICE_ID,
2128: ap_tds_def_rec.INVOICE_DISTRIBUTION_ID,
2129: ap_tds_def_rec.DISTRIBUTION_LINE_NUMBER,
2130: ap_tds_def_rec.AMOUNT,