[Home] [Help]
95: --Addec by Jia for FP Bug#7431371, Begin
96: -------------------------------------------------------------------------------
97: cursor c_get_tax_code(p_invoice_id number, p_invoice_distribution_id number) is
98: select nvl(actual_tax_id, default_tax_id) tax_id
99: from jai_ap_tds_inv_taxes
100: where invoice_id = p_invoice_id
101: and invoice_distribution_id = p_invoice_distribution_id;
102:
103: cursor c_get_tax_rate(p_tax_id number) is
173: where invoice_id = cn_invoice_id
174: and invoice_distribution_id not in
175: (
176: select invoice_distribution_id
177: from JAI_AP_TDS_INV_TAXES
178: where invoice_id = cn_invoice_id
179: and threshold_transition is null
180: );
181:
206:
207: cursor c_chk_section_chg
208: is
209: select count(1)
210: from jai_ap_tds_inv_taxes tax
211: where tax.invoice_id = pn_invoice_id
212: and tax.actual_section_code not in
213: (
214:
247: cursor c_chk_amount_chg(cn_invoice_id number)
248: is
249: select dist.invoice_id, dist.INVOICE_DISTRIBUTION_ID,nvl(dist.amount-sum(tax.amount),0) diff
250: from AP_INVOICE_DISTRIBUTIONS_ALL dist,
251: JAI_AP_TDS_INV_TAXES tax
252: where dist.invoice_id = tax.invoice_id
253: and dist.INVOICE_DISTRIBUTION_ID = tax.INVOICE_DISTRIBUTION_ID
254: and tax.threshold_transition is null
255: and dist.invoice_id = cn_invoice_id
354:
355: cursor c_get_non_match_dist(cn_invoice_id number)
356: is
357: SELECT count(1)
358: FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA, JAI_AP_TDS_INV_TAXES JATIT
359: WHERE AIDA.INVOICE_ID = JATIT.INVOICE_ID
360: and aida.invoice_id = cn_invoice_id
361: AND AIDA.INVOICE_DISTRIBUTION_ID = JATIT.INVOICE_DISTRIBUTION_ID
362: AND AIDA.DIST_CODE_COMBINATION_ID <> JATIT.DIST_CODE_COMBINATION_ID
494: jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.BEGIN', G_PKG_NAME || ': '||l_api_name||'()+');
495:
496: lv_sql :=
497: 'SELECT INVOICE_ID, SUM (AMOUNT) ,ACTUAL_SECTION_CODE, TAX_CATEGORY_ID
498: FROM JAI_AP_TDS_INV_TAXES JATIT
499: WHERE JATIT.INVOICE_ID <> $$P_INVOICE_ID$$
500: AND (
501: ( JATIT.TAX_LINE_NO <> 0 and JATIT.TAX_CATEGORY_ID IS NOT NULL and JATIT.invoice_distribution_id <> 1 )
502: or
635: 'SELECT INVOICE_ID, SUM (AMOUNT), ACTUAL_SECTION_CODE, TAX_CATEGORY_ID
636: FROM
637: (
638: SELECT JATIT.INVOICE_ID, (JATIT.AMOUNT-NVL(JATP.APPLICATION_AMOUNT, 0)) AMOUNT, JATIT.ACTUAL_SECTION_CODE, JATIT.TAX_CATEGORY_ID
639: FROM JAI_AP_TDS_INV_TAXES JATIT, JAI_AP_TDS_PREPAYMENTS JATP
640: WHERE JATIT.INVOICE_ID <> $$P_INVOICE_ID$$
641: AND (
642: ( JATIT.TAX_LINE_NO <> 0 and JATIT.TAX_CATEGORY_ID IS NOT NULL and JATIT.invoice_distribution_id <> 1 )
643: or
754:
755: --Updated by Chong.Lei for bug#13787158 begin
756: -----------------------------------------------------------------------------------------------------
757: /* lv_sql := 'SELECT JATIT.INVOICE_ID, SUM (nvl(AIDA.PREPAY_AMOUNT_REMAINING,aida.amount)) AMOUNT, JATIT.ACTUAL_SECTION_CODE, JATIT.TAX_CATEGORY_ID
758: FROM JAI_AP_TDS_INV_TAXES JATIT, AP_INVOICE_DISTRIBUTIONS_ALL AIDA
759: WHERE JATIT.INVOICE_ID <> $$P_INVOICE_ID$$
760: AND (
761: ( JATIT.TAX_LINE_NO <> 0 and JATIT.TAX_CATEGORY_ID IS NOT NULL and JATIT.invoice_distribution_id <> 1 )
762: or
803: GROUP BY JATIT.INVOICE_ID, JATIT.ACTUAL_SECTION_CODE, JATIT.TAX_CATEGORY_ID';*/
804: --Updated by Chong.Lei for bug#14218068, Added left outer join for ap_invoice_distributions_all table to get prepayments invoice which never applied to any standard invoice.
805: lv_sql := 'SELECT JATIT.INVOICE_ID, DECODE(JATIT.INVOICE_ID ,$$pn_prepayment_inovice_id$$, SUM(JATIT.AMOUNT) - NVL(abs(SUM (AIDA.AMOUNT)),0) + $$pn_unapply_amount$$ ,SUM(JATIT.AMOUNT) - NVL(abs(SUM (AIDA.AMOUNT)),0)) AMOUNT
806: ,JATIT.ACTUAL_SECTION_CODE, JATIT.TAX_CATEGORY_ID
807: FROM JAI_AP_TDS_INV_TAXES JATIT, AP_INVOICE_DISTRIBUTIONS_ALL AIDA
808: WHERE JATIT.INVOICE_ID <> $$P_INVOICE_ID$$
809: AND JATIT.TAX_LINE_NO = 0
810: AND (JATIT.TAX_CATEGORY_ID IS NOT NULL or (JATIT.TAX_CATEGORY_ID IS NULL and JATIT.ACTUAL_TAX_ID IS NULL))
811: AND JATIT.ACTUAL_SECTION_CODE = ''$$cv_section_code$$''
914: and global_attribute1 is not null
915: and invoice_distribution_id in
916: (
917: select invoice_distribution_id
918: from jai_ap_tds_inv_taxes
919: where invoice_id = p_invoice_id
920: and actual_section_code = pv_section_code
921: )
922: )
930: cursor c_get_dist_prepay(p_invoice_id number)
931: is
932: select invoice_distribution_id prepay_distribution_id,
933: sum(amount) amount
934: from jai_ap_tds_inv_taxes
935: where invoice_distribution_id
936: in
937: (
938: select prepay_distribution_id
954: FROM ap_invoice_distributions_all
955: WHERE INVOICE_DISTRIBUTION_ID IN
956: (
957: SELECT invoice_distribution_id
958: from jai_ap_tds_inv_taxes
959: where invoice_distribution_id
960: in
961: (
962: select prepay_distribution_id
988: --Addec by Jia for FP Bug#7431371, Begin
989: -------------------------------------------------------------------------------
990: cursor c_get_tax_code(p_invoice_id number, p_invoice_distribution_id number) is
991: select nvl(actual_tax_id, default_tax_id) tax_id
992: from jai_ap_tds_inv_taxes
993: where invoice_id = p_invoice_id
994: and invoice_distribution_id = p_invoice_distribution_id;
995:
996: cursor c_get_tax_rate(p_tax_id number) is
1073: select sum(a.application_amount) application_amount, d.threshold_grp_id, d.invoice_id
1074: from jai_ap_tds_prepayments a,
1075: ap_invoice_distributions_all b,
1076: ap_invoice_distributions_all c,
1077: jai_ap_tds_inv_taxes d
1078: where a.invoice_distribution_id_prepay = b.invoice_distribution_id
1079: and b.prepay_distribution_id = c.invoice_distribution_id
1080: and nvl(a.unapply_flag, 'N') <> 'Y'
1081: and c.invoice_id = cp_invoice_id --modified by Xiao Lv for Bug#8513550, related 11i bug#8439276
1096: and a.invoice_distribution_id_prepay = b.invoice_distribution_id
1097: and prepay_distribution_id is not null
1098: and nvl(a.unapply_flag, 'N') <> 'Y'
1099: and exists (select 1
1100: from jai_ap_tds_inv_taxes jatit, ap_invoice_distributions_all aida
1101: where aida.invoice_distribution_id = b.prepay_distribution_id
1102: and aida.invoice_id = jatit.invoice_id
1103: and aida.invoice_distribution_id = jatit.invoice_distribution_id
1104: and jatit.threshold_slab_id_single is not null
1113: and line_type_lookup_code = 'PREPAY';
1114:
1115: cursor c_get_thhold_grp(cp_invoice_id number, cp_invoice_dist_id number) is
1116: select threshold_grp_id
1117: from jai_ap_tds_inv_taxes
1118: where invoice_distribution_id = cp_invoice_dist_id;
1119:
1120: r_prepay_apply_amt c_prepay_apply_amt%rowtype;
1121: lv_thhold_grp_id number;
1197:
1198: cursor c_check_not_validate(p_invoice_id number, p_section_type VARCHAR2 ) is
1199: select count(tds_inv_tax_id) total_count, sum(decode(match_status_flag, 'A', 1, 0)) validated_a_count,
1200: sum(decode(match_status_flag, 'T', 1, 0)) validated_t_count
1201: from jai_ap_tds_inv_taxes
1202: where invoice_id = p_invoice_id
1203: -- Harshita for Bug 4870243
1204: and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, invoice_line_number)
1205: and invoice_distribution_id = nvl(p_invoice_distribution_id, invoice_distribution_id) -- Bug 6119216
1211: where org_id = p_org_id;
1212:
1213:
1214: /*select tds_inv_tax_id
1215: from jai_ap_tds_inv_taxes
1216: where invoice_id = p_invoice_id
1217: and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, -9999)
1218: and nvl(invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
1219: and section_type = p_section_type; */
1247: close c_fetch_po_encum;
1248: end if;
1249:
1250: if p_invoice_distribution_id is not null and p_match_status_flag is not null then
1251: update jai_ap_tds_inv_taxes
1252: set match_status_flag = p_match_status_flag
1253: where invoice_id = p_invoice_id
1254: and invoice_distribution_id = p_invoice_distribution_id;
1255: end if;
1552: cursor c_get_threshold_xcep(
1553: p_vendor_id number,
1554: p_vendor_site_id number,
1555: p_tds_section_code varchar2,
1556: p_section_type jai_ap_tds_inv_taxes.section_type%type
1557: )
1558: IS
1559: select threshold_hdr_id
1560: from JAI_AP_TDS_TH_VSITE_V
1570: cursor c_get_threshold_normal(
1571: p_vendor_id number,
1572: p_vendor_site_id number,
1573: p_tds_section_code varchar2,
1574: p_section_type jai_ap_tds_inv_taxes.section_type%type
1575: )
1576: IS
1577: select threshold_hdr_id
1578: from JAI_AP_TDS_TH_VSITE_V
2237:
2238: )
2239: LOOP
2240: for rec_tds_taxes in(
2241: select * from jai_ap_tds_inv_taxes
2242: where invoice_id = rec_trxs.invoice_id
2243: and actual_tax_id is not null
2244: and threshold_trx_id is not null
2245: and not exists
2256: open get_tax_rate(rec_tds_taxes.actual_tax_id);
2257: fetch get_tax_rate into ln_tax_rate;
2258: close get_tax_rate;
2259:
2260: --Get Section Type, Section Code, Tax type, Tax Rate from JAI_AP_TDS_INV_TAXES or Description of Invoice Distribution
2261:
2262: \* cursor get_line_info(cn_invoice_distribution_id number)
2263: is
2264: select line. from ap_invoice_lines_all line, ap_invoice_distributions_all dist
2355: rec_dist_info.org_id, --get from ap_invoice_distributions_all.,--ORG_ID ,
2356: --lv_vendor_name, --get vendor name by id, --VENDOR_NAME ,
2357: --lv_vendor_site_name, --get vendor site code by id --VENDOR_SITE_CODE ,
2358: rec_tds_taxes.section_type, --get from jaji_ap_tds_inv_taxes,--SECTION_TYPE ,
2359: rec_tds_taxes.actual_section_code, --get from jai_ap_tds_inv_taxes, --SECTION_CODE ,
2360: --rec_tds_taxes.tax_type, --get from jai_ap_tds_inv_taxes, --TAX_TYPE ,
2361: --ln_tax_rate, --get from jai_ap_tds_inv_taxes,--TAX_RATE ,
2362: rec_challan.payment_amount, --TDS_PAID ,
2363: 'N',
2356: --lv_vendor_name, --get vendor name by id, --VENDOR_NAME ,
2357: --lv_vendor_site_name, --get vendor site code by id --VENDOR_SITE_CODE ,
2358: rec_tds_taxes.section_type, --get from jaji_ap_tds_inv_taxes,--SECTION_TYPE ,
2359: rec_tds_taxes.actual_section_code, --get from jai_ap_tds_inv_taxes, --SECTION_CODE ,
2360: --rec_tds_taxes.tax_type, --get from jai_ap_tds_inv_taxes, --TAX_TYPE ,
2361: --ln_tax_rate, --get from jai_ap_tds_inv_taxes,--TAX_RATE ,
2362: rec_challan.payment_amount, --TDS_PAID ,
2363: 'N',
2364: rec_challan.challan_no, --CHALLAN_NUMBER ,
2357: --lv_vendor_site_name, --get vendor site code by id --VENDOR_SITE_CODE ,
2358: rec_tds_taxes.section_type, --get from jaji_ap_tds_inv_taxes,--SECTION_TYPE ,
2359: rec_tds_taxes.actual_section_code, --get from jai_ap_tds_inv_taxes, --SECTION_CODE ,
2360: --rec_tds_taxes.tax_type, --get from jai_ap_tds_inv_taxes, --TAX_TYPE ,
2361: --ln_tax_rate, --get from jai_ap_tds_inv_taxes,--TAX_RATE ,
2362: rec_challan.payment_amount, --TDS_PAID ,
2363: 'N',
2364: rec_challan.challan_no, --CHALLAN_NUMBER ,
2365: rec_challan.bsr_code, --BSR_CODE ,
2363: 'N',
2364: rec_challan.challan_no, --CHALLAN_NUMBER ,
2365: rec_challan.bsr_code, --BSR_CODE ,
2366: rec_challan.payment_reference_id, --PAYMENT_REFERENCE_ID ,
2367: rec_tds_taxes.tax_amount, --get from jai_ap_tds_inv_taxes,--TAX_AMOUNT ,
2368: rec_tds_taxes.tax_amount, --get from jai_ap_tds_inv_taxes,--BASE_TAX_AMOUNT ,
2369: sysdate,
2370: fnd_global.user_id ,
2371: sysdate ,
2364: rec_challan.challan_no, --CHALLAN_NUMBER ,
2365: rec_challan.bsr_code, --BSR_CODE ,
2366: rec_challan.payment_reference_id, --PAYMENT_REFERENCE_ID ,
2367: rec_tds_taxes.tax_amount, --get from jai_ap_tds_inv_taxes,--TAX_AMOUNT ,
2368: rec_tds_taxes.tax_amount, --get from jai_ap_tds_inv_taxes,--BASE_TAX_AMOUNT ,
2369: sysdate,
2370: fnd_global.user_id ,
2371: sysdate ,
2372: fnd_global.login_id ,
2406: is
2407:
2408: cursor c_check_if_exists(p_invoice_id number) is
2409: select count(tds_inv_tax_id)
2410: from jai_ap_tds_inv_taxes
2411: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
2412: and invoice_id = p_invoice_id
2413: and actual_tax_id is not null
2414: and threshold_transition is null -- Added by zhiwei for bug#13359892
2413: and actual_tax_id is not null
2414: and threshold_transition is null -- Added by zhiwei for bug#13359892
2415: ;
2416:
2417: cursor c_check_if_processed(p_invoice_id number,p_process_status jai_ap_tds_inv_taxes.process_status%type) is
2418: select count(tds_inv_tax_id)
2419: from jai_ap_tds_inv_taxes
2420: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
2421: and invoice_id = p_invoice_id
2415: ;
2416:
2417: cursor c_check_if_processed(p_invoice_id number,p_process_status jai_ap_tds_inv_taxes.process_status%type) is
2418: select count(tds_inv_tax_id)
2419: from jai_ap_tds_inv_taxes
2420: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
2421: and invoice_id = p_invoice_id
2422: and process_status = p_process_status
2423: and threshold_transition is null -- Added by zhiwei for bug#13359892
2425:
2426: cursor c_calculate_tax(p_invoice_id number) is
2427: select tds_inv_tax_id, actual_tax_id, amount, invoice_distribution_id,section_type
2428: ,actual_section_code,rounded_amount --Added by Zhiwei for Bug#13359892
2429: from jai_ap_tds_inv_taxes
2430: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
2431: and invoice_id = p_invoice_id
2432: and actual_tax_id is not null
2433: and tax_line_no = 1;
2442: '. Setup needs modification.' tax_end_dated_message
2443: from JAI_CMN_TAXES_ALL
2444: where tax_id = p_tax_id;
2445: /*Bug 5751783 - Selected non-rounded value for calculation*/
2446: cursor c_for_each_tds_section(p_invoice_id number, p_exchange_rate number,p_section_type jai_ap_tds_inv_taxes.section_type%type, p_prepay_amt number) is--rchandan for bug#4428980 --add by xiao for bug#6596019
2447: select actual_section_code, (sum(amount*p_exchange_rate)-p_prepay_amt) invoice_amount, sum(calc_tax_amount) section_amount,
2448: sum(tax_amount) tax_amount_orig
2449: from jai_ap_tds_inv_taxes
2450: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
2445: /*Bug 5751783 - Selected non-rounded value for calculation*/
2446: cursor c_for_each_tds_section(p_invoice_id number, p_exchange_rate number,p_section_type jai_ap_tds_inv_taxes.section_type%type, p_prepay_amt number) is--rchandan for bug#4428980 --add by xiao for bug#6596019
2447: select actual_section_code, (sum(amount*p_exchange_rate)-p_prepay_amt) invoice_amount, sum(calc_tax_amount) section_amount,
2448: sum(tax_amount) tax_amount_orig
2449: from jai_ap_tds_inv_taxes
2450: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
2451: and invoice_id = p_invoice_id
2452: and section_type = p_section_type --rchandan for bug#4428980
2453: and actual_section_code is not null
2459: from JAI_AP_TDS_VNDR_TYPE_V
2460: where vendor_id = p_vendor_id;
2461:
2462: cursor c_get_threshold
2463: (p_vendor_id number, p_vendor_site_id number, p_tds_section_code varchar2,p_section_type jai_ap_tds_inv_taxes.section_type%type) IS --rchandan for bug#4428980
2464: select threshold_hdr_id
2465: from JAI_AP_TDS_TH_VSITE_V
2466: where vendor_id = p_vendor_id
2467: and vendor_site_id = p_vendor_site_id
2473: p_tan_no varchar2,
2474: p_pan_no varchar2,
2475: p_tds_section_code varchar2 ,
2476: p_fin_year number,
2477: p_section_type jai_ap_tds_inv_taxes.section_type%type)
2478: IS --rchandan for bug#4428980
2479: select threshold_grp_id
2480: from jai_ap_tds_thhold_grps
2481: where vendor_id = p_vendor_id
2539: /*Bug 5751783. Selected non-rounded value for calculation*/
2540:
2541: cursor c_get_taxes_to_generate_tds
2542: (p_invoice_id number, p_tds_section_code varchar2, p_generate_all_invoices varchar2,
2543: p_exchange_rate number, p_threshold_slab_id_single number,p_section_type jai_ap_tds_inv_taxes.section_type%type, p_prepay_amt number) IS --rchandan for bug#4428980--add by xiao for bug#6596019
2544: -- select nvl(actual_tax_id,default_tax_id) actual_tax_id, --added nvl by Xiao for Bug#7154864
2545: select tax_category_id ,
2546: ( sum(amount*p_exchange_rate)-p_prepay_amt) taxable_amount, --Xiao for bug#6596019
2547: --sum(calc_tax_amount) tax_amount,--Comment by Zhiwei on 20120111
2546: ( sum(amount*p_exchange_rate)-p_prepay_amt) taxable_amount, --Xiao for bug#6596019
2547: --sum(calc_tax_amount) tax_amount,--Comment by Zhiwei on 20120111
2548: sum(calc_tax_amount*p_exchange_rate) tax_amount,--Update by Zhiwei on 20120111
2549: sum(tax_amount) tax_amount_orig
2550: from jai_ap_tds_inv_taxes
2551: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
2552: and invoice_id = p_invoice_id
2553: and section_type = p_section_type --rchandan for bug#4428980
2554: and actual_section_code = p_tds_section_code
2649: where set_of_books_id = cp_set_of_books_id;
2650:
2651: /*Bug 5751783. Selected non-rounded value for calculation*/
2652:
2653: cursor c_get_non_tds_section_tax (p_invoice_id number, p_exchange_rate number,p_section_type jai_ap_tds_inv_taxes.section_type%type) IS --rchandan for bug#4428980
2654: select section_type,
2655: actual_tax_id,
2656: sum(amount*p_exchange_rate) taxable_amount,
2657: sum(calc_tax_amount) tax_amount,
2655: actual_tax_id,
2656: sum(amount*p_exchange_rate) taxable_amount,
2657: sum(calc_tax_amount) tax_amount,
2658: sum(tax_amount) tax_amount_orig
2659: from jai_ap_tds_inv_taxes
2660: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
2661: and invoice_id = p_invoice_id
2662: and section_type <> p_section_type --rchandan for bug#4428980
2663: and actual_tax_id is not null
2664: group by section_type, actual_tax_id;
2665:
2666: --Add by Zhiwei Hou on 20120111 begin
2667: -----------------------------------------------
2668: cursor c_get_non_tds_sec_tax_cat (p_invoice_id number, p_exchange_rate number,p_section_type jai_ap_tds_inv_taxes.section_type%type) IS --rchandan for bug#4428980
2669: select section_type,
2670: actual_section_code,
2671: tax_category_id,
2672: sum(amount*p_exchange_rate) taxable_amount,
2671: tax_category_id,
2672: sum(amount*p_exchange_rate) taxable_amount,
2673: sum(calc_tax_amount) tax_amount,
2674: sum(tax_amount) tax_amount_orig
2675: from jai_ap_tds_inv_taxes
2676: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
2677: and invoice_id = p_invoice_id
2678: and section_type <> p_section_type --rchandan for bug#4428980
2679: and tax_category_id is not null
2713: CURSOR c_check_valid_tax(p_invoice_id number)
2714: IS
2715: -- select jitc.section_code section_code --Commented by ChongLei for DTC ER 2011/12/29
2716: select jatit.actual_section_code section_code --Added by ChongLei for DTC ER 2011/12/29
2717: from jai_cmn_taxes_all jitc, jai_ap_tds_inv_taxes jatit
2718: where jitc.tax_id = jatit.actual_tax_id
2719: and jatit.section_type = 'TDS_SECTION'
2720: and jatit.invoice_id = p_invoice_id
2721: and jatit.actual_tax_id is not null
2809: SELECT section_type,
2810: actual_section_code section_code,
2811: tax_category_id,
2812: sum(amount) amount
2813: from jai_ap_tds_inv_taxes
2814: where invoice_id = p_invoice_id
2815: and actual_section_code is not null
2816: group by section_type, actual_section_code, tax_category_id;
2817:
2879:
2880: cursor c_for_each_tds_section_new(
2881: p_invoice_id number,
2882: p_exchange_rate number,
2883: p_section_type jai_ap_tds_inv_taxes.section_type%type,
2884: p_section_code jai_ap_tds_inv_taxes.actual_section_code%type,
2885: p_prepay_amt number) is
2886: select actual_section_code,
2887: (sum(amount*p_exchange_rate)-p_prepay_amt) invoice_amount,
2880: cursor c_for_each_tds_section_new(
2881: p_invoice_id number,
2882: p_exchange_rate number,
2883: p_section_type jai_ap_tds_inv_taxes.section_type%type,
2884: p_section_code jai_ap_tds_inv_taxes.actual_section_code%type,
2885: p_prepay_amt number) is
2886: select actual_section_code,
2887: (sum(amount*p_exchange_rate)-p_prepay_amt) invoice_amount,
2888: sum(calc_tax_amount) section_amount,
2887: (sum(amount*p_exchange_rate)-p_prepay_amt) invoice_amount,
2888: sum(calc_tax_amount) section_amount,
2889: sum(tax_amount) tax_amount_orig
2890: --,invoice_distribution_id
2891: from jai_ap_tds_inv_taxes
2892: where nvl(consider_amt_for_tds, 'Y') = 'Y'
2893: and invoice_id = p_invoice_id
2894: and section_type = p_section_type
2895: and actual_section_code is not null
2943: cursor c_get_threshold_xcep(
2944: p_vendor_id number,
2945: p_vendor_site_id number,
2946: p_tds_section_code varchar2,
2947: p_section_type jai_ap_tds_inv_taxes.section_type%type
2948: )
2949: IS
2950: select threshold_hdr_id
2951: from JAI_AP_TDS_TH_VSITE_V
2971: cursor c_get_threshold_normal(
2972: p_vendor_id number,
2973: p_vendor_site_id number,
2974: p_tds_section_code varchar2,
2975: p_section_type jai_ap_tds_inv_taxes.section_type%type
2976: )
2977: IS
2978: select threshold_hdr_id
2979: from JAI_AP_TDS_TH_VSITE_V
3021: p_tan_no varchar2,
3022: p_pan_no varchar2,
3023: p_tds_section_code varchar2 ,
3024: p_fin_year number,
3025: p_section_type jai_ap_tds_inv_taxes.section_type%type,
3026: p_thhold_hdr_id number
3027: )
3028: IS
3029: select threshold_grp_id
3041: p_tan_no varchar2,
3042: p_pan_no varchar2,
3043: p_tds_section_code varchar2 ,
3044: p_fin_year number,
3045: p_section_type jai_ap_tds_inv_taxes.section_type%type
3046: )
3047: IS
3048: select threshold_grp_id
3049: from jai_ap_tds_thhold_grps
3076: -----------------------------------------------------------------
3077: /*cursor get_non_match_dist(cn_invoice_id number)
3078: is
3079: SELECT count(1)
3080: FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA, JAI_AP_TDS_INV_TAXES JATIT
3081: WHERE AIDA.INVOICE_ID = JATIT.INVOICE_ID
3082: and aida.invoice_id = cn_invoice_id
3083: AND AIDA.INVOICE_DISTRIBUTION_ID = JATIT.INVOICE_DISTRIBUTION_ID
3084: AND AIDA.DIST_CODE_COMBINATION_ID <> JATIT.DIST_CODE_COMBINATION_ID
3088:
3089: cursor get_non_match_ven(cn_invoice_id number)
3090: is
3091: SELECT count(1)
3092: FROM AP_INVOICES_ALL AIA, JAI_AP_TDS_INV_TAXES JATIT
3093: WHERE AIA.INVOICE_ID = JATIT.INVOICE_ID
3094: and aia.invoice_id = cn_invoice_id
3095: AND ( AIA.VENDOR_ID <> JATIT.VENDOR_ID
3096: OR AIA.VENDOR_SITE_ID <> JATIT.VENDOR_SITE_ID) -- Updated for bug13735926 by Wenqiong on 21-FEB-2012
3236: --Added by Chong for Bug#13802244 2012/09/17 Start
3237: ---------------------------------------------------------------------------------
3238: IF p_call_from = G_CALL_FROM_ROLLBACK THEN
3239: --When call from threshold rollback, we want to process invoice which be rollbacked from cumulative slab.
3240: --For such records, there must existing data in jai_ap_tds_inv_taxes with process_status = P
3241: --We just skip the process_status check and redefault tax , check if fall into single slab.
3242: NULL;
3243: ELSE
3244: ---------------------------------------------------------------------------------
3295:
3296: /* Update actual value from default value if actual is null for TDS section taxes only*/
3297: p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
3298: --Commented by Zhiwei for Bug#13359892 on 20111123, removed logic for column Default_Tax_ID
3299: /*update jai_ap_tds_inv_taxes
3300: set actual_tax_id = default_tax_id
3301: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
3302: and invoice_id = p_invoice_id
3303: and actual_tax_id is null
3305: and section_type = lv_tds_section_type; --rchandan for bug#4428980
3306: */--Commented by Zhiwei for Bug#13359892 on 20111123
3307:
3308: /* Update processed for those cases where NO TDS has to be deducted for TDS section taxes only */
3309: update jai_ap_tds_inv_taxes
3310: set process_status = 'P'
3311: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
3312: and invoice_id = p_invoice_id
3313: and section_type = lv_tds_section_type; --rchandan for bug#4428980
3328: end if;
3329:
3330: --Added by Zhiwei Hou for bug#13767335 on 20120305 begin
3331: /*
3332: Previously, the Amount in JAI_AP_TDS_INV_TAXES is foreign currency, so we need to transfer to INR amount for passing GENERATE_DTC_INVOICES().
3333: Now, solution changed, Amount in table will be INR currency transfered in Defaultation, so for Generation, we don't
3334: need to transfer again. so set variable ln_exchange_rate to '1'
3335: */
3336: ln_exchange_rate := 1;--Added by Zhiwei Hou for bug#13767335 on 20120305.
3364: ---------------------------------------------------------------------------
3365: --Added by Chong for bug#15939571 20121210 End
3366:
3367: /*Zhiwei: comments*/
3368: --At this time , the Tax must have being in table jai_ap_tds_inv_taxes by Default logic.
3369: --Here should check Slab and Tax attached to Category is available.
3370:
3371: --ln_dtc_tax_cat_id := 10001;
3372:
3717: ppay_amt('ESSI_SECTION') := ln_prepayment_app_amt;--Section type level.
3718:
3719: FOR r_sum_sectionwise IN(
3720: SELECT SUM(AMOUNT) amount, ACTUAL_SECTION_CODE
3721: FROM JAI_AP_TDS_INV_TAXES
3722: WHERE INVOICE_ID = p_invoice_id
3723: AND ACTUAL_SECTION_CODE IS NOT NULL
3724: AND Threshold_TRANSITION IS NULL
3725: GROUP BY ACTUAL_SECTION_CODE
3736:
3737:
3738: for rec_update in (
3739: select *
3740: from jai_ap_tds_inv_taxes a
3741: where a.invoice_id = p_invoice_id
3742: AND a.threshold_transition IS NULL
3743: AND a.actual_section_code IS NOT NULL
3744: )
3743: AND a.actual_section_code IS NOT NULL
3744: )
3745: loop
3746:
3747: UPDATE jai_ap_tds_inv_taxes a
3748: SET a.rounded_amount = rec_update.amount * rounding_amt(rec_update.actual_section_code)/secwise_sum_amt(rec_update.actual_section_code)
3749: WHERE a.TDS_INV_TAX_ID = rec_update.TDS_INV_TAX_ID;
3750:
3751:
3865: -- added by zhiwei.xin for bug 13792748 on 09-Mar-2012 begin
3866: for rec_category in
3867: (
3868: select nvl(tax_category_id,-999) tax_category_id
3869: from jai_ap_tds_inv_taxes
3870: where section_type = 'TDS_SECTION'
3871: and invoice_id = p_invoice_id
3872: )
3873: loop
4641: goto exit_from_procedure;
4642: END IF;
4643:
4644:
4645: /* Punch threshold_trx_id in jai_ap_tds_inv_taxes */
4646: update jai_ap_tds_inv_taxes
4647: set threshold_trx_id = ln_threshold_trx_id,
4648: threshold_slab_id_single = ln_threshold_slab_id_single
4649: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
4642: END IF;
4643:
4644:
4645: /* Punch threshold_trx_id in jai_ap_tds_inv_taxes */
4646: update jai_ap_tds_inv_taxes
4647: set threshold_trx_id = ln_threshold_trx_id,
4648: threshold_slab_id_single = ln_threshold_slab_id_single
4649: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
4650: and invoice_id = p_invoice_id
4717: ----------------------------------------------------------------------
4718: --Added by Chong for bug#16414088 eTDS ER End
4719:
4720: p_codepath := jai_general_pkg.plot_codepath(25, p_codepath); /* 25 */
4721: update jai_ap_tds_inv_taxes
4722: set threshold_grp_id = ln_threshold_grp_id,
4723: threshold_hdr_id = ln_threshold_hdr_id,
4724: threshold_slab_id = ln_threshold_slab_id_after,
4725: process_status = 'P'
4759:
4760:
4761: for rec_in_tax in(
4762: select distinct invoice_distribution_id
4763: from jai_ap_tds_inv_taxes
4764: where invoice_id = p_invoice_id
4765: )
4766: loop
4767:
4856:
4857:
4858: for rec_in_tax in(
4859: select distinct invoice_distribution_id
4860: from jai_ap_tds_inv_taxes
4861: where invoice_id = p_invoice_id
4862: )
4863: loop
4864:
5002:
5003: END IF; --( ln_proc_level >= ln_dbg_level) ;
5004: --fnd_file.put_line(FND_FILE.LOG, '34. Start thhold trx id '|| ln_start_threshold_trx_id);
5005:
5006: /* Punch threshold_trx_id in jai_ap_tds_inv_taxes */
5007: update jai_ap_tds_inv_taxes
5008: set threshold_trx_id = ln_threshold_trx_id,
5009: process_status = 'P'
5010: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
5003: END IF; --( ln_proc_level >= ln_dbg_level) ;
5004: --fnd_file.put_line(FND_FILE.LOG, '34. Start thhold trx id '|| ln_start_threshold_trx_id);
5005:
5006: /* Punch threshold_trx_id in jai_ap_tds_inv_taxes */
5007: update jai_ap_tds_inv_taxes
5008: set threshold_trx_id = ln_threshold_trx_id,
5009: process_status = 'P'
5010: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
5011: and invoice_id = p_invoice_id
5120: )IS
5121: TYPE TRAN_RECORD IS RECORD (
5122: INVOICE_ID NUMBER,
5123: AMOUNT NUMBER,
5124: SECTION_CODE JAI_AP_TDS_INV_TAXES.ACTUAL_SECTION_CODE%TYPE,--Updated by Zhiwei Hou on 20120116
5125: TAX_CATEGORY_ID NUMBER
5126: );
5127: TYPE TRAN_TYPE IS TABLE OF TRAN_RECORD;
5128: TRAN_TAB TRAN_TYPE := TRAN_TYPE();
5192: and section_code = p_tds_section_code;
5193:
5194: cursor get_sum_amount(cn_invoice_id number)
5195: is
5196: select sum(tax_amount) from jai_ap_tds_inv_taxes
5197: where invoice_id = cn_invoice_id
5198: and invoice_distribution_id = 1
5199: and threshold_transition = 'Y';
5200:
5244: AND jattt.tds_rollbacked IS NULL
5245: /*Commented out by Chong for bug#15976304 20121212 Start
5246: AND NOT EXISTS(
5247: SELECT 1
5248: FROM JAI_AP_TDS_INV_TAXES jatit
5249: ,jai_cmn_taxes_all jcta
5250: WHERE jatit.invoice_id = jattt.invoice_id
5251: AND jcta.tax_id = jatit.actual_tax_id
5252: AND jcta.tax_type = 'TDS_SURCHARGE'
5281:
5282: ln_surcharge_amount number;
5283: ln_threshold_hdr_id number;
5284:
5285: lv_codepath jai_ap_tds_inv_taxes.codepath%type;
5286:
5287: --Add by Zhiwei Hou on 20120114 begin
5288: ----------------------------------------------------
5289: get_ref_cur_not_deduct ref_cur;
5384: END IF;
5385: ---------------------------------------------------------------------------
5386: --Added by Chong for bug#15939571 20121210 End
5387:
5388: delete from jai_ap_tds_inv_taxes
5389: where invoice_id = TRAN_TAB(i).invoice_id
5390: and invoice_distribution_id = 1;
5391:
5392: --Derive Vendor, Invoice Currency Code, Exchange Rate and Vendor Site ID from TRAN_TAB(i).INVOICE_ID
5419: fetch get_tax_info into lv_tax_type,lv_section_type_tax ;
5420: close get_tax_info;
5421:
5422: /*
5423: Note: Data is populated into JAI_AP_TDS_INV_TAXES to calculate Taxes using jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes.
5424: Invoice Distribution ID,
5425: Distribution Line Number
5426: and Line Number
5427: are populated as 1 for these Invoices
5425: Distribution Line Number
5426: and Line Number
5427: are populated as 1 for these Invoices
5428: */
5429: INSERT INTO JAI_AP_TDS_INV_TAXES
5430: (
5431: TDS_INV_TAX_ID,
5432: INVOICE_ID,
5433: INVOICE_DISTRIBUTION_ID,
5466: THRESHOLD_TRANSITION
5467: )
5468: VALUES
5469: (
5470: JAI_AP_TDS_INV_TAXES_S.NEXTVAL,
5471: TRAN_TAB(i).INVOICE_ID,
5472: 1,
5473: 1,
5474: 1,
5622: END IF;
5623:
5624: --Added by Zhiwei Hou on 20120118 begin
5625: ------------------------------------------------------------------------
5626: update jai_ap_tds_inv_taxes
5627: set threshold_trx_id = ln_threshold_trx_id,
5628: threshold_grp_id = ln_threshold_grp_id,
5629: threshold_hdr_id = ln_threshold_hdr_id,
5630: threshold_slab_id = p_threshold_slab_id,
5645: threshold_grp_id,
5646: threshold_hdr_id,
5647: threshold_slab_id,
5648: process_status
5649: from JAI_AP_TDS_INV_TAXES
5650: where invoice_id = TRAN_TAB(i).INVOICE_ID
5651: and nvl(consider_amt_for_tds, 'Y') = 'Y'
5652: and jai_distribution_id = 1
5653: and tax_line_no = 1
5656: and tax_category_id = nvl(TRAN_TAB(i).TAX_CATEGORY_ID, LN_TAX_CATEGORY_ID)
5657:
5658: )
5659: loop
5660: update JAI_AP_TDS_INV_TAXES
5661: set threshold_trx_id = rec_upd.threshold_trx_id,
5662: threshold_slab_id = rec_upd.threshold_slab_id,
5663: process_status = rec_upd.process_status
5664: where invoice_id = TRAN_TAB(i).INVOICE_ID
6911: ----------------------------------------
6912: cursor get_one_tax(cv_tax_type varchar2)
6913: is
6914: select actual_tax_id
6915: from jai_ap_tds_inv_taxes
6916: where invoice_id = pn_invoice_id
6917: and actual_section_code = pv_section_code
6918: and tax_category_id = pn_tax_category_id
6919: and section_type = pv_section_type --Added by Chong for bug#16371927
6921:
6922: cursor get_amt_for_type(cv_tax_type varchar2)
6923: is
6924: select sum(tax_amount)
6925: from jai_ap_tds_inv_taxes
6926: where invoice_id = pn_invoice_id
6927: and section_type = pv_section_type --Added by Zhiwei Hou on 20120111
6928: and actual_section_code = pv_section_code
6929: and tax_category_id = pn_tax_category_id
6937:
6938: cursor get_amt_for_all
6939: is
6940: select sum(tax_amount)
6941: from jai_ap_tds_inv_taxes
6942: where invoice_id = pn_invoice_id
6943: and section_type = pv_section_type --Added by Zhiwei Hou on 20120111
6944: and actual_section_code = pv_section_code
6945: and tax_category_id = pn_tax_category_id;
6946:
6947: cursor get_amt_for_dist(cn_distribution_id number)
6948: is
6949: select sum(amount)
6950: from jai_ap_tds_inv_taxes
6951: where invoice_id = pn_invoice_id
6952: and actual_section_code = pv_section_code
6953: and tax_category_id = pn_tax_category_id
6954: and invoice_distribution_id = cn_distribution_id;
6956:
6957: cursor get_amt_for_all_inv
6958: is
6959: select sum(amount)
6960: from jai_ap_tds_inv_taxes
6961: where invoice_id = pn_invoice_id
6962: and actual_section_code = pv_section_code
6963: and tax_category_id = pn_tax_category_id;
6964:
6979: --Add by Zhiwei on 20120111 begin
6980: cursor get_amt_for_type_curr(cv_tax_type varchar2)
6981: is
6982: select sum(func_tax_amount)
6983: from jai_ap_tds_inv_taxes
6984: where invoice_id = pn_invoice_id
6985: and section_type = pv_section_type --Added by Zhiwei Hou on 20120111
6986: and actual_section_code = pv_section_code
6987: and tax_category_id = pn_tax_category_id
6995:
6996: cursor get_amt_for_all_curr
6997: is
6998: select sum(func_tax_amount)
6999: from jai_ap_tds_inv_taxes
7000: where invoice_id = pn_invoice_id
7001: and section_type = pv_section_type --Added by Zhiwei Hou on 20120111
7002: and actual_section_code = pv_section_code
7003: and tax_category_id = pn_tax_category_id;
7035: from jai_cmn_taxes_all
7036: where tax_id in
7037: (
7038: select actual_tax_id
7039: from jai_ap_tds_inv_taxes
7040: where invoice_id = pn_invoice_id
7041: and section_type = pv_section_type --Added by Zhiwei Hou on 20120111
7042: and actual_section_code = pv_section_code
7043: and tax_category_id = pn_tax_category_id
7409: (*/
7410: select distinct tax_type
7411: from (
7412: select actual_tax_id ,tax_type,tax_line_no
7413: from jai_ap_tds_inv_taxes
7414: where invoice_id = pn_invoice_id
7415: and section_type = pv_section_type --Added by Zhiwei Hou on 20120111
7416: and actual_section_code = pv_section_code
7417: and tax_category_id = pn_tax_category_id
9028: p_section_type VARCHAR2,
9029: p_section_code VARCHAR2
9030: ) IS
9031: SELECT invoice_distribution_id
9032: FROM jai_ap_tds_inv_taxes
9033: WHERE invoice_id = p_invoice_id
9034: AND NVL(section_type, default_type) = p_section_type
9035: AND NVL(actual_section_code, default_section_code) = p_section_code
9036: AND invoice_distribution_id <> 1
9043: WHERE invoice_distribution_id = p_invoice_distribution_id;
9044: */
9045: CURSOR c_get_threshold_grp_id(p_prepay_distribution_id NUMBER) IS
9046: SELECT threshold_grp_id
9047: FROM jai_ap_tds_inv_taxes
9048: WHERE invoice_distribution_id = p_prepay_distribution_id
9049: AND section_type = 'TDS_SECTION';
9050:
9051: CURSOR c_get_threshold_grp_dtl(p_threshold_grp_id NUMBER) IS
9130: r_jai_ap_tds_thhold_slabs c_jai_ap_tds_thhold_slabs%ROWTYPE;
9131: r_get_tds_thhold_slabs_byNt c_get_tds_thhold_slabs_byNt%ROWTYPE;
9132: lv_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
9133: lv_multiple_rate_setup jai_ap_tds_thhold_hdrs.multiple_rate_setup%TYPE;
9134: lv_invoice_distribution_id jai_ap_tds_inv_taxes.invoice_distribution_id%TYPE;
9135: lv_set_of_book_id NUMBER;
9136: lv_legal_entity_id NUMBER;
9137: lv_natural_account_segment VARCHAR2(100);
9138: lv_natural_account jai_ap_tds_thhold_account.natural_account_value%TYPE;
9315: CURSOR c_tds_tax(cn_invoice_distribution_id NUMBER) IS
9316: SELECT tax_category_id,
9317: section_type,
9318: actual_section_code
9319: FROM JAI_AP_TDS_INV_TAXES
9320: WHERE INVOICE_DISTRIBUTION_ID = cn_invoice_distribution_id
9321: AND ROWNUM = 1;
9322:
9323: cursor c_get_tds_section_info(cn_threshold_grp_id NUMBER) is
9344: AND jattt.TDS_EVENT NOT IN (G_SURCHARGE_ROLLBACK) --Added by Chong.Lei for bug#13359892 at 20121008
9345: AND jattt.THRESHOLD_GRP_ID = cn_threshold_grp_id
9346: AND NOT EXISTS(
9347: SELECT 1
9348: FROM JAI_AP_TDS_INV_TAXES jatit
9349: WHERE jattt.THRESHOLD_TRX_ID = jatit.THRESHOLD_TRX_ID
9350: AND jatit.THRESHOLD_SLAB_ID_SINGLE IS NOT NULL
9351: )
9352: UNION ALL --Added 'ALL' by Chong.Lei for bug#13802244 at 20120308
9432: AND tds_section_code is not null
9433: AND aia.cancelled_date IS NULL
9434: AND NOT EXISTS(
9435: SELECT 1
9436: FROM jai_ap_tds_inv_taxes jatit
9437: WHERE jatit.invoice_id = jattt.invoice_id
9438: AND threshold_slab_id_single IS NOT NULL
9439: )
9440: ;
9454: --Assume there is only one surcharge tax in this tax category.
9455: CURSOR c_get_surcharge_tax_id(cp_threshold_grp_id NUMBER)
9456: IS
9457: SELECT jatit.actual_tax_id
9458: FROM jai_ap_tds_inv_taxes jatit
9459: WHERE jatit.threshold_grp_id = cp_threshold_grp_id
9460: AND actual_tax_id IS NOT NULL
9461: AND tax_type = 'TDS_SURCHARGE'
9462: ;
9467: ln_tax_category_id NUMBER;
9468: ln_threshold_trx_id NUMBER;
9469: lv_tds_invoice_num ap_invoices_all.invoice_num%TYPE;
9470: lv_cm_invoice_num ap_invoices_all.invoice_num%TYPE;
9471: lv_section_type JAI_AP_TDS_INV_TAXES.section_type%TYPE;
9472: lv_section_code JAI_AP_TDS_INV_TAXES.actual_section_code%TYPE;
9473: lv_prepay_invoice_num ap_invoices_all.invoice_num%TYPE;
9474: lv_prepay_invoice_id NUMBER;
9475: ln_threshold_grp_audit_id NUMBER;
9468: ln_threshold_trx_id NUMBER;
9469: lv_tds_invoice_num ap_invoices_all.invoice_num%TYPE;
9470: lv_cm_invoice_num ap_invoices_all.invoice_num%TYPE;
9471: lv_section_type JAI_AP_TDS_INV_TAXES.section_type%TYPE;
9472: lv_section_code JAI_AP_TDS_INV_TAXES.actual_section_code%TYPE;
9473: lv_prepay_invoice_num ap_invoices_all.invoice_num%TYPE;
9474: lv_prepay_invoice_id NUMBER;
9475: ln_threshold_grp_audit_id NUMBER;
9476: lv_codepath jai_ap_tds_inv_taxes.codepath%TYPE;
9472: lv_section_code JAI_AP_TDS_INV_TAXES.actual_section_code%TYPE;
9473: lv_prepay_invoice_num ap_invoices_all.invoice_num%TYPE;
9474: lv_prepay_invoice_id NUMBER;
9475: ln_threshold_grp_audit_id NUMBER;
9476: lv_codepath jai_ap_tds_inv_taxes.codepath%TYPE;
9477: r_ap_invoices_all_au c_ap_invoices_all%ROWTYPE;
9478: r_ap_invoices_all_cm c_ap_invoices_all%ROWTYPE;
9479: lb_return_value BOOLEAN;
9480: ld_accounting_date DATE;
9609: and TDS_EVENT NOT IN (G_SURCHARGE_ROLLBACK)
9610: and jattt.THRESHOLD_GRP_ID = p_threshold_grp_id
9611: and not exists(
9612: select 1
9613: from jai_ap_tds_inv_taxes in_inv
9614: where in_inv.threshold_grp_id = p_threshold_grp_id
9615: and in_inv.threshold_trx_id = jattt.threshold_trx_id
9616: and in_inv.threshold_slab_id_single is not null
9617: );
9643: CLOSE c_get_lines_acct_date;
9644: END IF;
9645:
9646: -- Call defaultation procedure to get single slab if beach single
9647: -- Calculate tax amount update jai_ap_tds_inv_taxes table, call generation procedure to create single TDS invoice.
9648: process_dtc_at_inv_validate
9649: ( p_invoice_id => cur_rec.invoice_id
9650: ,p_vendor_id => cur_rec.vendor_id
9651: ,p_vendor_site_id => cur_rec.vendor_site_id
9741: and TDS_EVENT LIKE G_SURCHARGE_CALCULATE
9742: and jattt.THRESHOLD_GRP_ID = p_threshold_grp_id
9743: and not exists(
9744: select 1
9745: from jai_ap_tds_inv_taxes in_inv
9746: where in_inv.threshold_grp_id = p_threshold_grp_id
9747: and in_inv.threshold_trx_id = jattt.threshold_trx_id
9748: and in_inv.threshold_slab_id_single is not null
9749: );
10102: and TDS_EVENT LIKE 'SURCHARGE_CALCULATE'
10103: and jattt.THRESHOLD_GRP_ID = p_threshold_grp_id
10104: and not exists(
10105: select 1
10106: from jai_ap_tds_inv_taxes in_inv
10107: where in_inv.threshold_grp_id = p_threshold_grp_id
10108: and in_inv.threshold_trx_id = jattt.threshold_trx_id
10109: and in_inv.threshold_slab_id_single is not null
10110: );
10122: AND jattt.tds_rollbacked IS NULL
10123: AND jattt.threshold_grp_id = p_threshold_grp_id
10124: AND EXISTS(
10125: SELECT 1
10126: FROM JAI_AP_TDS_INV_TAXES jatit
10127: ,jai_cmn_taxes_all jcta
10128: WHERE jatit.invoice_id = jattt.invoice_id
10129: AND jcta.tax_id = jatit.actual_tax_id
10130: AND jcta.tax_type = 'TDS_SURCHARGE'
10149: AND jattt.tds_rollbacked IS NULL
10150: AND jattt.threshold_grp_id = p_threshold_grp_id
10151: AND EXISTS(
10152: SELECT 1
10153: FROM JAI_AP_TDS_INV_TAXES jatit
10154: ,jai_cmn_taxes_all jcta
10155: WHERE jatit.invoice_id = jattt.invoice_id
10156: AND jcta.tax_id = jatit.actual_tax_id
10157: AND jcta.tax_type = 'TDS_SURCHARGE'
10342: jcta.tax_account_id,
10343: jcta.tax_id, --Added tax_id by Chong for bug#16414088 20130320
10344: jcta.section_type
10345: from jai_cmn_taxes_all jcta
10346: ,jai_ap_tds_inv_taxes jatit
10347: ,jai_ap_tds_thhold_trxs jattt
10348: where jatit.actual_tax_id = jcta.tax_id
10349: and jatit.threshold_trx_id = jattt.threshold_trx_id
10350: and jatit.threshold_grp_id = pn_threshold_grp_id
10441: --Get total amount of current group
10442: cursor get_total_amt
10443: is
10444: select nvl(sum(decode(jattt.tds_event,'PREPAYMENT APPLICATION',-jatit.tax_amount,jatit.tax_amount)),0)
10445: from jai_ap_tds_inv_taxes jatit
10446: ,jai_ap_tds_thhold_trxs jattt
10447: where jatit.threshold_trx_id = jattt.threshold_trx_id
10448: and jatit.threshold_grp_id = pn_threshold_grp_id
10449: and jattt.tds_rollbacked is null
10453: --cursor get_amt_for_tax_account(cn_tax_account jai_cmn_taxes_all.tax_account_id%type)
10454: cursor get_amt_for_tax_account(cn_tax_id jai_cmn_taxes_all.tax_id%type)--Change to tax_id by Chong for bug#16414088 20130320
10455: is
10456: select nvl(sum(decode(jattt.tds_event,'PREPAYMENT APPLICATION',-jatit.tax_amount,jatit.tax_amount)),0)
10457: from jai_ap_tds_inv_taxes jatit
10458: ,jai_ap_tds_thhold_trxs jattt
10459: ,jai_cmn_taxes_all jcta
10460: where jatit.threshold_trx_id = jattt.threshold_trx_id
10461: and jatit.actual_tax_id = jcta.tax_id
10714:
10715: for rec_tax in (
10716: --select distinct jcta.tax_account_id
10717: select distinct jcta.tax_id --Change to tax_id by Chong for bug#16414088 20130320
10718: from jai_ap_tds_inv_taxes jatit
10719: ,jai_ap_tds_thhold_trxs jattt
10720: ,jai_cmn_taxes_all jcta
10721: where jattt.threshold_trx_id = jatit.threshold_trx_id
10722: and jatit.actual_tax_id = jcta.tax_id