[Home] [Help]
10: This Package is created for enhancement to capture all the eTDS format, data Requirements in different procedures
11:
12: 2 13/04/2004 Vijay Shankar for Bug# 3603545 (also fixed 3567864), Version: 619.2
13: TDS and Base Taxable amounts are not populated correctly in
14: JAI_AP_ETDS_T table for Vendor Prepayment Invoices
15: TDS Amount is wrongly populated as tds_tax_rate is used instead of
16: tds_tax_rate/100 to calculated tds amount of prepayment applied amount.
17: Base amount is not at all reduced to the extent of prepayment applied which is resolved with this bug.
18:
48: 9. 17-Aug-2005 Ramananda for bug#4555466 during R12 Sanity Testing. File Version 120.2
49: Instead of fnd_common_lookups table, ja_lookups table is being referred
50:
51: 10 07/12/2005 Hjujjuru for the bug 4866533 File version 120.3
52: added the who columns in the insert into tables JAI_AP_ETDS_REQUESTS and JAI_AP_ETDS_T.
53: Dependencies Due to this bug:-
54: None
55:
56: 11 23/12/2005 Hjujjuru for Bug 4889272, File Version 120.4
83: Report has been modified to generate Form 27A along with eTDS Quarterly Returns.
84: Added a call to the Concurrent JAINTDSA in the quarterly_returns procedure.
85:
86:
87: Added the update clause to modify the taxabale_amount of JAI_AP_ETDS_T
88: based on the taxable_amount of jai_ap_tds_thhold_trxs table.
89: In case of threshold transition and rollback transactions, the
90: taxable amount should be 0 for the differential invoice that has been created.
91: Hence, implemented this change.
132: Added sh_cess_rate in cursor c_tax_rates and defined related variables. Included ln_sh_cess_amt in ln_cess_amt.
133:
134: 23. 24-Oct-2008 Bgowrava for bug#7485031, File version 115.14.6017.12
135: Added code to use the include_flag and exclude_flag while determining the sections which need to be considered to be
136: inserted into the table JAI_AP_ETDS_T for the current execution. Also included code changes for bug 6281440
137:
138: 24. 06-Aug-2007 Forward Port Bug 6329774
139: Changed the challan date to base_invoice_date while printing deductee details.
140:
488:
489: -- added, Harshita for Bug 4525089
490:
491: lv_cert_issue_date DATE ;
492: ln_tds_amt JAI_AP_ETDS_T.amt_of_tds%TYPE;
493: ln_surcharge_amt JAI_AP_ETDS_T.amt_of_surcharge%TYPE;
494: ln_cess_amt JAI_AP_ETDS_T.amt_of_cess%TYPE;
495: -- ended, Harshita for Bug 4525089
496:
489: -- added, Harshita for Bug 4525089
490:
491: lv_cert_issue_date DATE ;
492: ln_tds_amt JAI_AP_ETDS_T.amt_of_tds%TYPE;
493: ln_surcharge_amt JAI_AP_ETDS_T.amt_of_surcharge%TYPE;
494: ln_cess_amt JAI_AP_ETDS_T.amt_of_cess%TYPE;
495: -- ended, Harshita for Bug 4525089
496:
497: --Date 11-05-2007 by Sacsethi for bug 5647248
490:
491: lv_cert_issue_date DATE ;
492: ln_tds_amt JAI_AP_ETDS_T.amt_of_tds%TYPE;
493: ln_surcharge_amt JAI_AP_ETDS_T.amt_of_surcharge%TYPE;
494: ln_cess_amt JAI_AP_ETDS_T.amt_of_cess%TYPE;
495: -- ended, Harshita for Bug 4525089
496:
497: --Date 11-05-2007 by Sacsethi for bug 5647248
498: -- start 5647248
548: WHERE invoice_id = p_invoice_id;
549: CURSOR c_threshold_tran_inv (p_threshold_grp_id IN NUMBER)
550: IS
551: SELECT tds_invoice_id, tds_check_id, tds_tax_id, tds_amount
552: FROM jai_ap_etds_t
553: WHERE tds_invoice_id = (SELECT MAX(invoice_to_tds_authority_id)
554: FROM jai_ap_tds_thhold_trxs
555: WHERE threshold_grp_id = p_threshold_grp_id
556: AND tds_event LIKE 'THRESHOLD TRANSITION%'
759: lv_exclude_flag := 'N';
760: END IF;
761: -- end 5647248
762:
763: INSERT INTO JAI_AP_ETDS_T (
764: batch_id,
765: base_invoice_id,
766: tds_invoice_id,
767: tds_invoice_num,
812: ( lv_exclude_flag = 'N' or ( lv_exclude_flag = 'Y'
813: and upper(a.tds_section) not in ( lv_exclude(1),lv_exclude(2),lv_exclude(3),lv_exclude(4),lv_exclude(5),lv_exclude(6),lv_exclude(7),lv_exclude(8),lv_exclude(9),lv_exclude(10)) ) );
814: --Added above two conditions by Bgowrava for bug#7485031
815: \* ER: 13514846 - Zero tax rate lower rate records *\
816: INSERT INTO JAI_AP_ETDS_T (
817: batch_id,
818: base_invoice_id,
819: tds_invoice_id,
820: tds_invoice_num,
907: where lookup_type in ('JAI_TDS_SECTION_ZERO_RATE_197', 'JAI_TDS_SECTION_ZERO_RATE_197A'))
908: group by jatit.invoice_id, nvl(jatit.actual_section_code, jatit.default_section_code), nvl(jatit.actual_tax_id, jatit.default_tax_id);
909: \* ER : 13514846 -end*\
910: \* Bug#10315928 - Start *\
911: INSERT INTO JAI_AP_ETDS_T (
912: batch_id,
913: base_invoice_id,
914: tds_invoice_id,
915: tds_invoice_num,
1000: and aida.match_status_flag in ('A','T') \*10408793 - Unvalidated Invoices must not be picked*\
1001: and base_invoices.org_id = p_organization_id
1002: and base_invoices.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
1003: and not exists (select '1' from jai_ap_tds_thhold_trxs jattt where jattt.invoice_id = base_invoices.invoice_id) \*Bug 10408793 - TDS should not be deducted*\
1004: and not exists (select '1' from jai_ap_etds_t where base_invoice_id=base_invoices.invoice_id and batch_id=p_batch_id) \* Added by Avanija for FVU3.6 *\
1005: --and base_invoices.gl_date between p_tds_inv_from_date and p_tds_inv_to_date
1006: and aida.accounting_date between p_tds_inv_from_date and p_tds_inv_to_date \* Changed by Avanija for FVU3.6 *\
1007: and
1008: ( lv_include_flag = 'N' or ( lv_include_flag = 'Y'
1072: and aida.invoice_id = base_invoices.invoice_id
1073: and base_invoices.org_id = p_organization_id
1074: and base_invoices.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
1075: and not exists (select '1' from jai_ap_tds_thhold_trxs jattt where jattt.invoice_id = base_invoices.invoice_id) \*Bug 10408793 - TDS should not be deducted*\
1076: and not exists (select '1' from jai_ap_etds_t where base_invoice_id=base_invoices.invoice_id and batch_id=p_batch_id) \* Added by Avanija for FVU3.6 *\
1077: --and base_invoices.gl_date between p_tds_inv_from_date and p_tds_inv_to_date
1078: and aida.accounting_date between p_tds_inv_from_date and p_tds_inv_to_date \* Changed by Avanija for FVU3.6 *\
1079: and lv_exclude_flag = 'Y'
1080: ;
1082: \* Bug#10315928 - End *\
1083:
1084:
1085: v_statement_id := '3';
1086: \* -------- following fields of JAI_AP_ETDS_T needs to be populated now
1087: Invoice Distributions - prepayment_amount_applied
1088: Payment Details - tds_check_id, challan_num, challan_date, bank_branch_code
1089: --------------------------------- *\
1090: \*11896260 - Changes for FVU 3.1 - Start*\
1091: \*Need to pick Invoices created before Threshold was breached and Prepayment not applied*\
1092: FOR c_get_threshold_grp IN (SELECT threshold_grp_id
1093: FROM jai_ap_tds_inv_taxes
1094: WHERE invoice_id IN (SELECT base_invoice_id
1095: FROM jai_ap_etds_t
1096: WHERE batch_id = p_batch_id
1097: AND EXISTS (SELECT 1
1098: FROM jai_ap_tds_thhold_trxs jatit_1
1099: WHERE jatit_1.invoice_to_tds_authority_id = tds_invoice_id
1108: FETCH c_chk_threshold_rollback INTO r_chk_threshold_rollback;
1109: CLOSE c_chk_threshold_rollback;
1110:
1111: EXIT WHEN r_chk_threshold_rollback.invoice_to_tds_authority_id IS NOT NULL;
1112: INSERT INTO JAI_AP_ETDS_T (
1113: batch_id,
1114: base_invoice_id,
1115: tds_invoice_id,
1116: tds_invoice_num,
1198: AND aida.prepay_amount_remaining IS NULL
1199: AND aia.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
1200: AND aia.org_id = p_organization_id
1201: AND NOT EXISTS (SELECT 1
1202: FROM JAI_AP_ETDS_T
1203: WHERE base_invoice_id = jatit.invoice_id
1204: AND batch_id = p_batch_id
1205: )
1206: AND ( lv_include_flag = 'N' or ( lv_include_flag = 'Y' and upper(nvl(jatit.actual_section_code, jatit.default_section_code))
1208: AND ( lv_exclude_flag = 'N' or ( lv_exclude_flag = 'Y' and upper(nvl(jatit.actual_section_code, jatit.default_section_code))
1209: not in ( lv_exclude(1),lv_exclude(2),lv_exclude(3),lv_exclude(4),lv_exclude(5),lv_exclude(6),lv_exclude(7),lv_exclude(8),lv_exclude(9),lv_exclude(10), '94F')));
1210:
1211: \*Need to pick Invoices created before Threshold was breached and Prepayment applied*\
1212: INSERT INTO JAI_AP_ETDS_T (
1213: batch_id,
1214: base_invoice_id,
1215: tds_invoice_id,
1216: tds_invoice_num,
1298: AND aida.match_status_flag in ('A','T')
1299: AND aia.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT')
1300: AND aia.org_id = p_organization_id
1301: AND NOT EXISTS (SELECT 1
1302: FROM JAI_AP_ETDS_T
1303: WHERE base_invoice_id = jatit.invoice_id
1304: AND batch_id = p_batch_id
1305: )
1306: AND ( lv_include_flag = 'N' or ( lv_include_flag = 'Y' and upper(nvl(jatit.actual_section_code, jatit.default_section_code))
1337: ln_sh_cess_amount := round((r_threshold_tran_inv.tds_amount * r_tax_rates.sh_cess_rate/r_tax_rates.tax_rate),2);
1338: ln_cess_amount := round((r_threshold_tran_inv.tds_amount * r_tax_rates.cess_rate/r_tax_rates.tax_rate),2) + ln_sh_cess_amount;
1339: ln_tds_amount := r_threshold_tran_inv.tds_amount - NVL(ln_surcharge_amount,0) - NVL(ln_cess_amount,0) ;
1340: \*Prorate TDS Amount of Threshold Transition invoice among the Invoices prior to Threshold*\
1341: UPDATE jai_ap_etds_t
1342: SET tds_check_id = r_threshold_tran_inv.tds_check_id,
1343: challan_num = r_tds_payment_check_id.challan_num,
1344: challan_date = r_tds_payment_check_id.challan_date,
1345: check_number = r_tds_payment_check_id.check_number,
1359: ------------------------------------------------
1360: --Added by Zhiwei for Bug#15840480 FVU3.6 end
1361: );
1362:
1363: DELETE jai_ap_etds_t
1364: WHERE batch_id = p_batch_id
1365: AND EXISTS (SELECT 1
1366: FROM jai_ap_tds_thhold_trxs
1367: WHERE invoice_to_tds_authority_id = tds_invoice_id
1374: Prepayment is greater than Standard Invoice
1375: + Base Taxable amount of Standard Invoice must be equal the amount unpaid if GL Date of Prepayment is less than Standard Invoice
1376: + Similar logic applies to Prepayment too*\
1377: FOR c_update_amount IN (SELECT jaet.rowid row_id, jaet.*
1378: FROM JAI_AP_ETDS_T jaet
1379: WHERE tds_invoice_id = -9998
1380: AND batch_id = p_batch_id)
1381: LOOP
1382: ln_calculated_inv_amt := 0;
1405: fnd_file.put_line(FND_FILE.LOG, 'PP ln_remain_amt: ' || ln_remain_amt);
1406: ln_calculated_inv_amt := ln_calculated_inv_amt + nvl(ln_remain_amt, 0);
1407: END IF; \*IF c_update_amount.base_invoice_type_lookup_code = 'STANDARD' THEN*\
1408: fnd_file.put_line(FND_FILE.LOG, 'ln_calculated_inv_amt: ' || ln_calculated_inv_amt);
1409: UPDATE JAI_AP_ETDS_T
1410: SET base_taxabale_amount = ln_calculated_inv_amt,
1411: tds_invoice_id = -9999,
1412: amt_of_tds = ((amt_of_tds*ln_calculated_inv_amt)/base_taxabale_amount),
1413: amt_of_surcharge = ((amt_of_surcharge*ln_calculated_inv_amt)/base_taxabale_amount),
1418: \*11896260 - Changes for FVU 3.1 - End*\
1419:
1420: FOR dtl IN (select a.rowid row_id, a.*,
1421: b.vendor_id vendor_id, b.vendor_site_id vendor_site_id, b.invoice_type_lookup_code inv_type
1422: from JAI_AP_ETDS_T a, ap_invoices_all b
1423: where a.batch_id = p_batch_id and a.base_invoice_id = b.invoice_id
1424: and a.tds_invoice_id not in ( -9999, -9997) ) \* Added Condition for Bug#10315928 *\ \* ER: 13514846 *\
1425: LOOP
1426:
1572: v_challan_num := substr(v_challan_num, 1,25);
1573:
1574: v_statement_id := '3h';
1575: \* following lines of UPDATE commented by Vijay Shankar for Bug# 3567864 and modified as below UPDATE st
1576: UPDATE JAI_AP_ETDS_T
1577: SET tds_amount = round(tds_amount - v_prepayment_amount_applied*dtl.tds_tax_rate/100, 2),
1578: *\
1579: --Added by Xiao Lv for bug#7662155 on 06-Jan-2010, begin
1580:
1598: ln_tds_amt := dtl.tds_amount - NVL(ln_surcharge_amt,0) - NVL(ln_cess_amt,0) ; -- added NVL, Harshita
1599: -- ended, Harshita for Bug 4525089
1600: END IF; --r_get_tds_inv.threshold_trx_id IS NOT NULL --add by Xiao Lv for bug#7662155
1601:
1602: UPDATE JAI_AP_ETDS_T
1603: SET tds_amount = round(tds_amount - v_prepayment_amount_applied*dtl.tds_tax_rate/100, 2),
1604: base_taxabale_amount = base_taxabale_amount - v_prepayment_amount_applied,
1605: base_vendor_id = dtl.vendor_id,
1606: base_vendor_site_id = dtl.vendor_site_id,
1629: --Modified by Xiao Lv for bug#7662155, begin
1630: \*
1631: --Date 11-05-2007 by Sacsethi for bug 5647248
1632: -- start 5647248
1633: update jai_ap_etds_t a
1634: set base_taxabale_amount =
1635: ( select nvl(taxable_amount,0)
1636: from JAI_AP_TDS_THHOLD_TRXS b
1637: where b.invoice_to_tds_authority_id = a.tds_invoice_id )
1650: * Updates records which are available in
1651: * jai_ap_tds_thhold_trxs *\
1652:
1653: \*Bug 12415388 - Update Base Taxable amount to the portion of Check amount*\
1654: UPDATE jai_ap_etds_t a
1655: SET base_taxabale_amount =
1656: ( SELECT decode(b.tds_event,
1657: 'SURCHARGE_CALCULATE',
1658: 0,
1669: * but considered for calculating taxable_basis
1670: * for threshold transition or rollback.
1671: *\
1672:
1673: UPDATE jai_ap_etds_t a
1674: SET base_taxabale_amount = 0
1675: WHERE a.batch_id = p_batch_id
1676: AND base_taxabale_amount IS NULL;
1677:
1739:
1740: -- added, Harshita for Bug 4525089
1741:
1742: lv_cert_issue_date DATE ;
1743: ln_tds_amt JAI_AP_ETDS_T.amt_of_tds%TYPE;
1744: ln_surcharge_amt JAI_AP_ETDS_T.amt_of_surcharge%TYPE;
1745: ln_cess_amt JAI_AP_ETDS_T.amt_of_cess%TYPE;
1746: -- ended, Harshita for Bug 4525089
1747:
1740: -- added, Harshita for Bug 4525089
1741:
1742: lv_cert_issue_date DATE ;
1743: ln_tds_amt JAI_AP_ETDS_T.amt_of_tds%TYPE;
1744: ln_surcharge_amt JAI_AP_ETDS_T.amt_of_surcharge%TYPE;
1745: ln_cess_amt JAI_AP_ETDS_T.amt_of_cess%TYPE;
1746: -- ended, Harshita for Bug 4525089
1747:
1748: --Date 11-05-2007 by Sacsethi for bug 5647248
1741:
1742: lv_cert_issue_date DATE ;
1743: ln_tds_amt JAI_AP_ETDS_T.amt_of_tds%TYPE;
1744: ln_surcharge_amt JAI_AP_ETDS_T.amt_of_surcharge%TYPE;
1745: ln_cess_amt JAI_AP_ETDS_T.amt_of_cess%TYPE;
1746: -- ended, Harshita for Bug 4525089
1747:
1748: --Date 11-05-2007 by Sacsethi for bug 5647248
1749: -- start 5647248
1799: WHERE invoice_id = p_invoice_id;
1800: CURSOR c_threshold_tran_inv (p_threshold_grp_id IN NUMBER)
1801: IS
1802: SELECT tds_invoice_id, tds_check_id, tds_tax_id, tds_amount
1803: FROM jai_ap_etds_t
1804: WHERE tds_invoice_id = (SELECT MAX(invoice_to_tds_authority_id)
1805: FROM jai_ap_tds_thhold_trxs
1806: WHERE threshold_grp_id = p_threshold_grp_id
1807: AND tds_event LIKE 'THRESHOLD TRANSITION%'
2019: FETCH c_get_tds_regime_info INTO r_get_tds_regime_info;
2020: CLOSE c_get_tds_regime_info;
2021:
2022: --1).Invoices attracting TDS and paid to Tax Authority
2023: INSERT INTO JAI_AP_ETDS_T (
2024: batch_id,
2025: base_invoice_id,
2026: tds_invoice_id,
2027: tds_invoice_num,
2112: and upper(a.section_code) not in ( lv_exclude(1),lv_exclude(2),lv_exclude(3),lv_exclude(4),lv_exclude(5)
2113: ,lv_exclude(6),lv_exclude(7),lv_exclude(8),lv_exclude(9),lv_exclude(10))))
2114: ;
2115:
2116: jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME || l_api_name, 'After insert into JAI_AP_ETDS_T batch_id:' || p_batch_id);
2117: --2). Update payments infomation for paid TDS transactions (inserted from setp 1)
2118: --Update payment status and tds cess, sh tds cess, surcharge amount for paid transacations
2119: FOR dtl IN (select a.rowid row_id
2120: ,a.*
2120: ,a.*
2121: ,b.vendor_id vendor_id
2122: ,b.vendor_site_id vendor_site_id
2123: ,b.invoice_type_lookup_code inv_type
2124: from JAI_AP_ETDS_T a
2125: ,ap_invoices_all b
2126: where a.batch_id = p_batch_id
2127: and a.base_invoice_id = b.invoice_id
2128: and a.tds_invoice_id not in ( -9999, -9997) )
2280: v_challan_num := substr(v_challan_num, 1,25);
2281:
2282: v_statement_id := '3h';
2283: /* following lines of UPDATE commented by Vijay Shankar for Bug# 3567864 and modified as below UPDATE st
2284: UPDATE JAI_AP_ETDS_T
2285: SET tds_amount = round(tds_amount - v_prepayment_amount_applied*dtl.tds_tax_rate/100, 2),
2286: */
2287: --Added by Xiao Lv for bug#7662155 on 06-Jan-2010, begin
2288: OPEN c_get_tds_inv_det(dtl.tds_invoice_id);
2316: END IF;
2317: END LOOP;
2318: END IF; --r_get_tds_inv.threshold_trx_id IS NOT NULL --add by Xiao Lv for bug#7662155
2319:
2320: UPDATE JAI_AP_ETDS_T
2321: SET --tds_amount = round(tds_amount - v_prepayment_amount_applied*dtl.tds_tax_rate/100, 2), --Updated for bug16898321, Sum surcharge amount into tds_amount 20130602
2322: tds_amount = round(ln_tds_amt + ln_cess_amt + ln_sh_cess_amt + ln_surcharge_amt - v_prepayment_amount_applied*dtl.tds_tax_rate/100, 2),
2323: base_taxabale_amount = base_taxabale_amount - v_prepayment_amount_applied,
2324: base_vendor_id = dtl.vendor_id,
2348: --Modified by Xiao Lv for bug#7662155, begin
2349: /*
2350: --Date 11-05-2007 by Sacsethi for bug 5647248
2351: -- start 5647248
2352: update jai_ap_etds_t a
2353: set base_taxabale_amount =
2354: ( select nvl(taxable_amount,0)
2355: from JAI_AP_TDS_THHOLD_TRXS b
2356: where b.invoice_to_tds_authority_id = a.tds_invoice_id )
2369: * Updates records which are available in
2370: * jai_ap_tds_thhold_trxs */
2371:
2372: /*Bug 12415388 - Update Base Taxable amount to the portion of Check amount*/
2373: UPDATE jai_ap_etds_t a
2374: SET base_taxabale_amount =
2375: ( SELECT decode(b.tds_event,
2376: 'SURCHARGE_CALCULATE',
2377: 0,
2387: * are not available in jai_ap_tds_thhold_trxs
2388: * but considered for calculating taxable_basis
2389: * for threshold transition or rollback.
2390: */
2391: UPDATE jai_ap_etds_t a
2392: SET base_taxabale_amount = 0
2393: WHERE a.batch_id = p_batch_id
2394: AND base_taxabale_amount IS NULL;
2395:
5117:
5118:
5119: /* CURSOR c_challan_records(p_batch_id IN NUMBER) IS
5120: select *
5121: from JAI_AP_ETDS_T a
5122: where a.batch_id = p_batch_id
5123: and a.consider_for_challan=1
5124: FOR UPDATE OF challan_line_num;
5125:
5129: lv_dummy_date DATE;
5130:
5131: /* CURSOR c_challan_records(p_batch_id IN NUMBER) IS
5132: SELECT tds_section, bank_branch_code, challan_num, challan_date, sum(tds_amount) tds_amount
5133: FROM JAI_AP_ETDS_T a
5134: WHERE a.batch_id = p_batch_id
5135: AND a.consider_for_challan=1
5136: GROUP BY tds_section, bank_branch_code, challan_num, challan_date;*/
5137:
5144: sum(tds_amount) tds_amount,
5145: sum(amt_of_tds) amt_of_tds,
5146: sum(amt_of_surcharge) amt_of_surcharge,
5147: sum(amt_of_cess) amt_of_cess
5148: from JAI_AP_ETDS_T a
5149: where a.batch_id = p_batch_id
5150: and a.consider_for_challan=1
5151: and a.tds_invoice_id <> -9999 -- Bug#10315928
5152: group by NVL(tds_section,'No Section'), NVL(bank_branch_code,'No Bank Branch'),
5169: tds_vendor_classification /* Uncommented by Avanija for FVU3.6 */
5170: --decode(tds_vendor_classification,NULL,NULL,'TRANS/SOFTW') tds_vendor_classification /* Avanija for FVU3.6 */
5171: ------------------------------------------------------------
5172: --Changed by Zhhou for bug#15962641 20121205 end
5173: from jai_ap_etds_t a
5174: where a.batch_id = p_batch_id
5175: and a.consider_for_challan=1
5176: group by NVL(tds_section,'No Section'), NVL(bank_branch_code,'No Bank Branch'),
5177: NVL(challan_num,'No Challan Number'), NVL(challan_date,lv_dummy_date),
5188: /*end 10315928 */
5189:
5190: /*CURSOR c_deductee_records(p_batch_id IN NUMBER) IS
5191: SELECT *
5192: FROM JAI_AP_ETDS_T a
5193: WHERE a.batch_id = p_batch_id
5194: AND a.consider_for_challan=1
5195: FOR UPDATE OF deductee_line_num;*/
5196:
5213: sum(amt_of_surcharge) amt_of_surcharge,
5214: sum(amt_of_cess) amt_of_cess,
5215: sum(base_taxabale_amount) base_taxabale_amount,
5216: sum(tds_amount) tds_amount
5217: from JAI_AP_ETDS_T a
5218: where a.batch_id = p_batch_id
5219: and a.consider_for_challan=1
5220: and (a.tds_invoice_id <> -9999 /* Bug#10315928 */
5221: OR (a.tds_invoice_id = -9999 and (a.tds_vendor_classification not in ( 'Transporter', 'Software') OR a.tds_vendor_classification IS NULL))) /*11896260*/
5245: sum(amt_of_surcharge) amt_of_surcharge,
5246: sum(amt_of_cess) amt_of_cess,
5247: sum(base_taxabale_amount) base_taxabale_amount,
5248: sum(tds_amount) tds_amount
5249: from JAI_AP_ETDS_T a
5250: where a.batch_id = p_batch_id
5251: and a.consider_for_challan=1
5252: and a.tds_invoice_id = -9997
5253: and challan_line_num = NVL(p_challan_line_num, challan_line_num)
5294: sum(amt_of_surcharge) amt_of_surcharge,
5295: sum(amt_of_cess) amt_of_cess,
5296: sum(base_taxabale_amount) base_taxabale_amount,
5297: sum(tds_amount) tds_amount
5298: from jai_ap_etds_t a
5299: where a.batch_id = p_batch_id
5300: and a.consider_for_challan=1
5301: and a.tds_invoice_id = -9999
5302: and challan_line_num = NVL(p_challan_line_num, challan_line_num)
5364: --Added out by Chong for eTDS ER bug#16414088 20130330 End
5365: /*
5366: CURSOR c_deductee_cnt(p_batch_id IN NUMBER , p_check_number IN NUMBER ) IS
5367: select sum ( count( base_vendor_id ) ) -- distinct removed the distinct
5368: from JAI_AP_ETDS_T
5369: WHERE batch_id = p_batch_id
5370: and nvl(tds_section, 'No Section') = nvl(cd.tds_section, 'No Section')
5371: and nvl(challan_num,'No Challan Number') = nvl(cd.challan_num, 'No Challan Number')
5372: and nvl(challan_date, lv_dummy_date) = nvl(cd.challan_date, lv_dummy_date )
5383: p_challan_num IN varchar2,
5384: p_challan_date IN DATE,
5385: p_bank_branch_code IN VARCHAR2) IS
5386: select sum ( count( distinct base_invoice_id ) )
5387: from JAI_AP_ETDS_T
5388: WHERE batch_id = p_batch_id
5389: and nvl(tds_section, 'No Section') = nvl(p_tds_section, 'No Section')
5390: and nvl(challan_num,'No Challan Number') = nvl(p_challan_num, 'No Challan Number')
5391: and nvl(challan_date, lv_dummy_date) = nvl(p_challan_date, lv_dummy_date )
5397: having sum(amt_of_tds) >0 /* Added having clause for Bug 13323753 */
5398: /* ER: 13514846 */
5399: union
5400: select sum ( count( distinct base_invoice_id ) )
5401: from JAI_AP_ETDS_T
5402: WHERE batch_id = p_batch_id
5403: and nvl(tds_section, 'No Section') = nvl(p_tds_section, 'No Section')
5404: and nvl(challan_num,'No Challan Number') = nvl(p_challan_num, 'No Challan Number')
5405: and nvl(challan_date, lv_dummy_date) = nvl(p_challan_date, lv_dummy_date )
5410: ;
5411: /*Bug 11896260 - Start*/
5412: CURSOR c_deductee_cnt_bt(p_batch_id IN NUMBER/* p_check_number IN NUMBER - Commented for Bug 13323753 */ ) IS
5413: select sum ( count( distinct base_invoice_id ) )
5414: from JAI_AP_ETDS_T
5415: WHERE batch_id = p_batch_id
5416: and nvl(tds_section, 'No Section') = nvl(cd.tds_section, 'No Section')
5417: and nvl(challan_num,'No Challan Number') = nvl(cd.challan_num, 'No Challan Number')
5418: and nvl(challan_date, lv_dummy_date) = nvl(cd.challan_date, lv_dummy_date )
5437: --Changed by Zhiwei for Bug#15840480 FVU3.6 end
5438: --CURSOR c_deductee_cnt_trans(p_batch_id IN NUMBER)--Added by Zhhou for bug#15962641 20121205 /* Commented by Avanija for FVU3.6 */
5439: is
5440: select sum ( count( distinct base_vendor_id ) )
5441: from jai_ap_etds_t
5442: WHERE batch_id = p_batch_id
5443: -- and nvl(tds_section, 'No Section') = nvl('SEC. 194(C)', 'No Section')
5444: --and nvl(tds_section, 'No Section') =decode(pv_vendor_classification, 'Transporter', 'SEC. 194(C)','SEC. 194(J)') /* Avanija for FVU3.6 */ /* Commented for bug 15996337 */
5445: and consider_for_challan=1
5469: WHERE invoice_id = cp_invoice_id;
5470:
5471: ld_tds_gl_date date;
5472:
5473: CURSOR c_get_errors(cp_batch_id JAI_AP_ETDS_T.batch_id%TYPE ) IS
5474: Select Error_Message from jai_ap_etds_errors_t
5475: where batch_id = cp_batch_id ;
5476:
5477: -- File Header Variables
5960: END IF ;
5961:
5962: END IF ;
5963:
5964: UPDATE JAI_AP_ETDS_T
5965: SET deductee_line_num = v_line_number
5966: WHERE batch_id = v_batch_id
5967: and consider_for_challan=1
5968: and challan_line_num = dd.challan_line_num
6154: END IF ;
6155:
6156: END IF ;
6157:
6158: UPDATE jai_ap_etds_t
6159: SET deductee_line_num = v_line_number
6160: WHERE batch_id = v_batch_id
6161: and consider_for_challan=1
6162: and tds_invoice_id = -9999
6344:
6345: -- v_quart_form_number := '26Q';
6346: v_quart_form_number := p_form_number; --Date 11-05-2007 by Sacsethi for bug 5647248
6347:
6348: SELECT JAI_AP_ETDS_T_S.nextval INTO v_batch_id FROM DUAL;
6349:
6350: IF NVL(p_action,'X') <> 'V' THEN
6351: IF NVL(p_generate_headers,'X') = 'Y' or NVL(p_action,'X') = 'H' THEN
6352: jai_ap_tds_etds_pkg.v_debug_pad_char := ' ';
6668: v_financial_year := to_char(v_start_date, 'YYYY')||to_char(v_end_date, 'YY');
6669: v_assessment_year := to_char(add_months(v_start_date,12), 'YYYY')||to_char(add_months(v_end_date,12), 'YY');
6670:
6671: /*SELECT count(1), sum(tds_amount)
6672: FROM JAI_AP_ETDS_T
6673: WHERE batch_id = v_batch_id AND consider_for_challan=1;*/
6674:
6675: -- bug#3708878. Above select was changed to the select below.
6676: select count(1), sum(tds_amt)
6677: INTO v_challan_cnt, v_totTaxDeductedAsPerChallan
6678: from
6679: (
6680: select tds_section, bank_branch_code, challan_num, challan_date, sum(tds_amount) tds_amt
6681: from JAI_AP_ETDS_T
6682: WHERE batch_id = v_batch_id AND consider_for_challan=1
6683: group by tds_section, bank_branch_code, challan_num, challan_date
6684: );
6685:
6683: group by tds_section, bank_branch_code, challan_num, challan_date
6684: );
6685:
6686: SELECT count(1), sum(tds_amount) INTO v_deductee_cnt, v_totTaxDeductedAsPerDeductee
6687: FROM JAI_AP_ETDS_T
6688: WHERE batch_id = v_batch_id AND consider_for_deductee=1
6689: AND ((tds_invoice_id not in ( -9999, -9997)) /* Bug#10315928 */ /* ER :13514846 */
6690: OR (tds_invoice_id = -9999 AND (tds_vendor_classification IS NULL
6691: --Changed by Zhhou for bug#15962641 20121205 begin
7081: END IF ;
7082:
7083:
7084:
7085: -- UPDATE JAI_AP_ETDS_T SET challan_line_num = v_line_number WHERE CURRENT OF c_challan_records;
7086: -- bug#3708878. update needed to be changed as for update of cannot be used with group by
7087: UPDATE JAI_AP_ETDS_T
7088: SET challan_line_num = v_line_number
7089: WHERE batch_id = v_batch_id
7083:
7084:
7085: -- UPDATE JAI_AP_ETDS_T SET challan_line_num = v_line_number WHERE CURRENT OF c_challan_records;
7086: -- bug#3708878. update needed to be changed as for update of cannot be used with group by
7087: UPDATE JAI_AP_ETDS_T
7088: SET challan_line_num = v_line_number
7089: WHERE batch_id = v_batch_id
7090: and nvl(tds_section, 'No Section') = nvl(cd.tds_section, 'No Section')
7091: and nvl(challan_num, 'No Challan Number') = nvl(cd.challan_num, 'No Challan Number')
7284: ) ;
7285: END IF ;
7286: END IF ;
7287:
7288: UPDATE jai_ap_etds_t
7289: SET challan_line_num = v_line_number
7290: WHERE batch_id = v_batch_id
7291: --and nvl(tds_section, 'No Section') = decode(cd.tds_vendor_classification,'Transporter','SEC. 194(C)', 'SEC. 194(J)') /*Avanija for FVU3.6 */
7292: and nvl(tds_section, 'No Section') = cd.tds_section /*Commented above and added the AND for tds section for bug 15996337 */