[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
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:
249:
250: -- added, Harshita for Bug 4525089
251:
252: lv_cert_issue_date DATE ;
253: ln_tds_amt JAI_AP_ETDS_T.amt_of_tds%TYPE;
254: ln_surcharge_amt JAI_AP_ETDS_T.amt_of_surcharge%TYPE;
255: ln_cess_amt JAI_AP_ETDS_T.amt_of_cess%TYPE;
256: -- ended, Harshita for Bug 4525089
257:
250: -- added, Harshita for Bug 4525089
251:
252: lv_cert_issue_date DATE ;
253: ln_tds_amt JAI_AP_ETDS_T.amt_of_tds%TYPE;
254: ln_surcharge_amt JAI_AP_ETDS_T.amt_of_surcharge%TYPE;
255: ln_cess_amt JAI_AP_ETDS_T.amt_of_cess%TYPE;
256: -- ended, Harshita for Bug 4525089
257:
258: --Date 11-05-2007 by Sacsethi for bug 5647248
251:
252: lv_cert_issue_date DATE ;
253: ln_tds_amt JAI_AP_ETDS_T.amt_of_tds%TYPE;
254: ln_surcharge_amt JAI_AP_ETDS_T.amt_of_surcharge%TYPE;
255: ln_cess_amt JAI_AP_ETDS_T.amt_of_cess%TYPE;
256: -- ended, Harshita for Bug 4525089
257:
258: --Date 11-05-2007 by Sacsethi for bug 5647248
259: -- start 5647248
497:
498:
499:
500:
501: INSERT INTO JAI_AP_ETDS_T (
502: batch_id,
503: -- line_number,
504: base_invoice_id,
505: base_taxabale_amount,
556: ; --Added above two conditions by Bgowrava for bug#7485031
557:
558:
559: v_statement_id := '3';
560: /* -------- following fields of JAI_AP_ETDS_T needs to be populated now
561: Invoice Distributions - prepayment_amount_applied
562: Payment Details - tds_check_id, challan_num, challan_date, bank_branch_code
563: --------------------------------- */
564: FOR dtl IN (select a.rowid row_id, a.*,
562: Payment Details - tds_check_id, challan_num, challan_date, bank_branch_code
563: --------------------------------- */
564: FOR dtl IN (select a.rowid row_id, a.*,
565: b.vendor_id vendor_id, b.vendor_site_id vendor_site_id, b.invoice_type_lookup_code inv_type
566: from JAI_AP_ETDS_T a, ap_invoices_all b
567: where a.batch_id = p_batch_id and a.base_invoice_id = b.invoice_id)
568: LOOP
569: --added by csahoo for bug#5975168, start
570: ln_con_for_challan := 1;
713: v_challan_num := substr(v_challan_num, 1,25);
714:
715: v_statement_id := '3h';
716: /* following lines of UPDATE commented by Vijay Shankar for Bug# 3567864 and modified as below UPDATE st
717: UPDATE JAI_AP_ETDS_T
718: SET tds_amount = round(tds_amount - v_prepayment_amount_applied*dtl.tds_tax_rate/100, 2),
719: */
720:
721: -- added, Harshita for Bug 4525089
724: ln_cess_amt := round((dtl.tds_amount * ln_cess_rate/ln_tax_rate),2) + ln_sh_cess_amt ; --Added ln_sh_cess_amt by bgowrava for bug#6195027
725: ln_tds_amt := dtl.tds_amount - NVL(ln_surcharge_amt,0) - NVL(ln_cess_amt,0) ; -- added NVL, Harshita
726: -- ended, Harshita for Bug 4525089
727:
728: UPDATE JAI_AP_ETDS_T
729: SET tds_amount = round(tds_amount - v_prepayment_amount_applied*dtl.tds_tax_rate/100, 2),
730: base_taxabale_amount = base_taxabale_amount - v_prepayment_amount_applied,
731: base_vendor_id = dtl.vendor_id,
732: base_vendor_site_id = dtl.vendor_site_id,
753: END LOOP;
754:
755: --Date 11-05-2007 by Sacsethi for bug 5647248
756: -- start 5647248
757: update jai_ap_etds_t a
758: set base_taxabale_amount =
759: ( select nvl(taxable_amount,0)
760: from JAI_AP_TDS_THHOLD_TRXS b
761: where b.invoice_to_tds_authority_id = a.tds_invoice_id )
3180:
3181:
3182: /* CURSOR c_challan_records(p_batch_id IN NUMBER) IS
3183: select *
3184: from JAI_AP_ETDS_T a
3185: where a.batch_id = p_batch_id
3186: and a.consider_for_challan=1
3187: FOR UPDATE OF challan_line_num;
3188:
3192: lv_dummy_date DATE;
3193:
3194: /* CURSOR c_challan_records(p_batch_id IN NUMBER) IS
3195: SELECT tds_section, bank_branch_code, challan_num, challan_date, sum(tds_amount) tds_amount
3196: FROM JAI_AP_ETDS_T a
3197: WHERE a.batch_id = p_batch_id
3198: AND a.consider_for_challan=1
3199: GROUP BY tds_section, bank_branch_code, challan_num, challan_date;*/
3200:
3207: sum(tds_amount) tds_amount,
3208: sum(amt_of_tds) amt_of_tds,
3209: sum(amt_of_surcharge) amt_of_surcharge,
3210: sum(amt_of_cess) amt_of_cess
3211: from JAI_AP_ETDS_T a
3212: where a.batch_id = p_batch_id
3213: and a.consider_for_challan=1
3214: group by NVL(tds_section,'No Section'), NVL(bank_branch_code,'No Bank Branch'),
3215: NVL(challan_num,'No Challan Number'), NVL(challan_date,lv_dummy_date),
3218:
3219:
3220: /*CURSOR c_deductee_records(p_batch_id IN NUMBER) IS
3221: SELECT *
3222: FROM JAI_AP_ETDS_T a
3223: WHERE a.batch_id = p_batch_id
3224: AND a.consider_for_challan=1
3225: FOR UPDATE OF deductee_line_num;*/
3226:
3241: sum(amt_of_surcharge) amt_of_surcharge,
3242: sum(amt_of_cess) amt_of_cess,
3243: sum(base_taxabale_amount) base_taxabale_amount,
3244: sum(tds_amount) tds_amount
3245: from JAI_AP_ETDS_T a
3246: where a.batch_id = p_batch_id
3247: and a.consider_for_challan=1
3248: and challan_line_num = NVL(p_challan_line_num, challan_line_num)
3249: group by challan_line_num, base_vendor_id, base_vendor_site_id,tds_tax_id,
3265: WHERE tax_id = p_tax_id;
3266: /*
3267: CURSOR c_deductee_cnt(p_batch_id IN NUMBER , p_check_number IN NUMBER ) IS
3268: select sum ( count( base_vendor_id ) ) -- distinct removed the distinct
3269: from JAI_AP_ETDS_T
3270: WHERE batch_id = p_batch_id
3271: and nvl(tds_section, 'No Section') = nvl(cd.tds_section, 'No Section')
3272: and nvl(challan_num,'No Challan Number') = nvl(cd.challan_num, 'No Challan Number')
3273: and nvl(challan_date, lv_dummy_date) = nvl(cd.challan_date, lv_dummy_date )
3284: p_challan_num IN varchar2,
3285: p_challan_date IN DATE,
3286: p_bank_branch_code IN VARCHAR2) IS
3287: select sum ( count( distinct base_vendor_id ) )
3288: from JAI_AP_ETDS_T
3289: WHERE batch_id = p_batch_id
3290: and nvl(tds_section, 'No Section') = nvl(p_tds_section, 'No Section')
3291: and nvl(challan_num,'No Challan Number') = nvl(p_challan_num, 'No Challan Number')
3292: and nvl(challan_date, lv_dummy_date) = nvl(p_challan_date, lv_dummy_date )
3295: and check_number = p_check_number
3296: group by base_vendor_id, base_vendor_site_id,tds_tax_id ;
3297:
3298:
3299: CURSOR c_get_errors(cp_batch_id JAI_AP_ETDS_T.batch_id%TYPE ) IS
3300: Select Error_Message from jai_ap_etds_errors_t
3301: where batch_id = cp_batch_id ;
3302:
3303: -- File Header Variables
3678: END IF ;
3679:
3680: END IF ;
3681:
3682: UPDATE JAI_AP_ETDS_T
3683: SET deductee_line_num = v_line_number
3684: WHERE batch_id = v_batch_id
3685: and consider_for_challan=1
3686: and challan_line_num = dd.challan_line_num
3852:
3853: -- v_quart_form_number := '26Q';
3854: v_quart_form_number := p_form_number; --Date 11-05-2007 by Sacsethi for bug 5647248
3855:
3856: SELECT JAI_AP_ETDS_T_S.nextval INTO v_batch_id FROM DUAL;
3857:
3858: IF NVL(p_action,'X') <> 'V' THEN
3859: IF NVL(p_generate_headers,'X') = 'Y' or NVL(p_action,'X') = 'H' THEN
3860: jai_ap_tds_etds_pkg.v_debug_pad_char := ' ';
4087: v_financial_year := to_char(v_start_date, 'YYYY')||to_char(v_end_date, 'YY');
4088: v_assessment_year := to_char(add_months(v_start_date,12), 'YYYY')||to_char(add_months(v_end_date,12), 'YY');
4089:
4090: /*SELECT count(1), sum(tds_amount)
4091: FROM JAI_AP_ETDS_T
4092: WHERE batch_id = v_batch_id AND consider_for_challan=1;*/
4093:
4094: -- bug#3708878. Above select was changed to the select below.
4095: select count(1), sum(tds_amt)
4096: INTO v_challan_cnt, v_totTaxDeductedAsPerChallan
4097: from
4098: (
4099: select tds_section, bank_branch_code, challan_num, challan_date, sum(tds_amount) tds_amt
4100: from JAI_AP_ETDS_T
4101: WHERE batch_id = v_batch_id AND consider_for_challan=1
4102: group by tds_section, bank_branch_code, challan_num, challan_date
4103: );
4104:
4102: group by tds_section, bank_branch_code, challan_num, challan_date
4103: );
4104:
4105: SELECT count(1), sum(tds_amount) INTO v_deductee_cnt, v_totTaxDeductedAsPerDeductee
4106: FROM JAI_AP_ETDS_T
4107: WHERE batch_id = v_batch_id AND consider_for_deductee=1;
4108:
4109: IF p_action <> 'V' THEN
4110: FND_FILE.put_line(FND_FILE.log, 'Batch Header');
4414: END IF ;
4415:
4416:
4417:
4418: -- UPDATE JAI_AP_ETDS_T SET challan_line_num = v_line_number WHERE CURRENT OF c_challan_records;
4419: -- bug#3708878. update needed to be changed as for update of cannot be used with group by
4420: UPDATE JAI_AP_ETDS_T
4421: SET challan_line_num = v_line_number
4422: WHERE batch_id = v_batch_id
4416:
4417:
4418: -- UPDATE JAI_AP_ETDS_T SET challan_line_num = v_line_number WHERE CURRENT OF c_challan_records;
4419: -- bug#3708878. update needed to be changed as for update of cannot be used with group by
4420: UPDATE JAI_AP_ETDS_T
4421: SET challan_line_num = v_line_number
4422: WHERE batch_id = v_batch_id
4423: and ( ( tds_section IS NULL AND cd.tds_section IS NULL ) OR ( tds_section = cd.tds_section ) )--nvl(tds_section, 'No Section') = nvl(cd.tds_section, 'No Section')
4424: and ( (challan_num IS NULL AND cd.challan_num IS NULL ) OR ( challan_num = cd.challan_num ) ) --nvl(challan_num, 'No Challan Number') = nvl(cd.challan_num, 'No Challan Number')