[Home] [Help]
190: gn_tds_rounding_factor
191: gd_tds_rounding_effective_date and function get_rnded_value
192: is created.
193:
194: updated jai_ap_tdS_inv_taxes and jai_ap_tds_thhold_grps
195: withe the rounded values. This is done in procedure
196: process_tds_at_inv_validate and maintain_thhold_grps.
197: In generate_tds_invoices derived the logic for rounding.
198: Added conditions in queries for fetching the taxable
335:
336: 46. 14-Jan-2010 Jia For FP Bug#7368735
337: Issue: This is a forward port bug for the Bug#7347096.
338: On attaching a wct tax alone, the error 'Error - Threshold is not defined for the applicable TDS section'
339: was getting thrown,this is due to the entry that gets created for TDS in jai_ap_tds_inv_taxes also with
340: no section codes, even though there is no distribution created which has a TDS tax.
341:
342: Fixed: Modified the cursor c_check_valid_tax in procedure process_tds_at_inv_validate to only loop through
343: the distributions which have either a default_section_code or actual_section_code defined.
348: the record missing from the 'India - TDS PAyment Review' report, because the main query of this report
349: is dependent on the value of threshold_hdr_id column of jai_ap_tds_thhold_trxs.
350:
351: Fixed: In cases where no default_section_code was specified in the vendor additional information,
352: the value for default_section_code in jai_ap_tds_inv_taxes was populated as null.
353: Hence during the execution of the cursor c_check_valid_tax it would fetch no records as the
354: value for actual_section_code is also null at this point of time. Hence modified this query to
355: be based on actual_tax_id and default_tax_id rather than the section code.
356:
398: Fix:Commented the actual_tax_id not null condition in the cursor c_get_taxes_to_generate_tds.
399: This would check for the threshold limit for both manually attached and defaulted tax codes.
400: 55. 26-May-2011 Bug 11880998
401: Description: In an Invoice is canceled without validation prior to Threshold Breach it results in negative lines
402: in jai_ap_tds_inv_taxes and the same corrupts the Threshold Transition Invoice
403: Fix: Since the Invoice that is canceled without validation never hits the Threshold the same
404: needs to be skipped when picking invoices for Threshold Transition.
405: Modified cursor c_jai_ap_no_tds_trx to skip invoices based on AUDIT entries.
406: When an Invoice is canceled without validation the Transaction amounts in Audit tables are zero.
406: When an Invoice is canceled without validation the Transaction amounts in Audit tables are zero.
407:
408: 56. 23-Aug-2011 mmurtuza for bug12858951
409: Description: TDS CALCULATED FOR SERVICE TAX LINES WHEN THRESHOLD CROSSED
410: Fix: Added the condtion nvl(consider_amt_for_tds, 'Y') = 'Y' in al the statements involving jai_ap_tds_inv_taxes table
411: in process_tds_at_inv_validate procedure to pick invoices for tds invoice generation.
412:
413: 57. 14-Oct-2011 amandali for bug 13070779
414: Description:Wrong taxable amount shown for prepayment application on unvalidated standard invoice.
481: --Addec by Jia for FP Bug#7431371, Begin
482: -------------------------------------------------------------------------------
483: cursor c_get_tax_code(p_invoice_id number, p_invoice_distribution_id number) is
484: select nvl(actual_tax_id, default_tax_id) tax_id
485: from jai_ap_tds_inv_taxes
486: where invoice_id = p_invoice_id
487: and invoice_distribution_id = p_invoice_distribution_id;
488:
489: cursor c_get_tax_rate(p_tax_id number) is
550: select sum(a.application_amount) application_amount, d.threshold_grp_id, d.invoice_id
551: from jai_ap_tds_prepayments a,
552: ap_invoice_distributions_all b,
553: ap_invoice_distributions_all c,
554: jai_ap_tds_inv_taxes d
555: where a.invoice_distribution_id_prepay = b.invoice_distribution_id
556: and b.prepay_distribution_id = c.invoice_distribution_id
557: and nvl(a.unapply_flag, 'N') <> 'Y'
558: and c.invoice_id = cp_invoice_id --modified by Xiao Lv for Bug#8513550, related 11i bug#8439276
573: and a.invoice_distribution_id_prepay = b.invoice_distribution_id
574: and prepay_distribution_id is not null
575: and nvl(a.unapply_flag, 'N') <> 'Y'
576: and exists (select 1
577: from jai_ap_tds_inv_taxes jatit, ap_invoice_distributions_all aida
578: where aida.invoice_distribution_id = b.prepay_distribution_id
579: and aida.invoice_id = jatit.invoice_id
580: and aida.invoice_distribution_id = jatit.invoice_distribution_id
581: and jatit.threshold_slab_id_single is not null
590: and line_type_lookup_code = 'PREPAY';
591:
592: cursor c_get_thhold_grp(cp_invoice_id number, cp_invoice_dist_id number) is
593: select threshold_grp_id
594: from jai_ap_tds_inv_taxes
595: where invoice_distribution_id = cp_invoice_dist_id;
596:
597: r_prepay_apply_amt c_prepay_apply_amt%rowtype;
598: lv_thhold_grp_id number;
670:
671: cursor c_check_not_validate(p_invoice_id number, p_section_type VARCHAR2 ) is
672: select count(tds_inv_tax_id) total_count, sum(decode(match_status_flag, 'A', 1, 0)) validated_a_count,
673: sum(decode(match_status_flag, 'T', 1, 0)) validated_t_count
674: from jai_ap_tds_inv_taxes
675: where invoice_id = p_invoice_id
676: -- Harshita for Bug 4870243
677: and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, invoice_line_number)
678: and invoice_distribution_id = nvl(p_invoice_distribution_id, invoice_distribution_id) -- Bug 6119216
684: where org_id = p_org_id;
685:
686:
687: /*select tds_inv_tax_id
688: from jai_ap_tds_inv_taxes
689: where invoice_id = p_invoice_id
690: and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, -9999)
691: and nvl(invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
692: and section_type = p_section_type; */
719: close c_fetch_po_encum;
720: end if;
721:
722: if p_invoice_distribution_id is not null and p_match_status_flag is not null then
723: update jai_ap_tds_inv_taxes
724: set match_status_flag = p_match_status_flag
725: where invoice_id = p_invoice_id
726: and invoice_distribution_id = p_invoice_distribution_id;
727: end if;
810: is
811:
812: cursor c_check_if_exists(p_invoice_id number) is
813: select count(tds_inv_tax_id)
814: from jai_ap_tds_inv_taxes
815: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
816: and invoice_id = p_invoice_id
817: and (actual_tax_id is not null or default_tax_id is not null);
818:
815: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
816: and invoice_id = p_invoice_id
817: and (actual_tax_id is not null or default_tax_id is not null);
818:
819: cursor c_check_if_processed(p_invoice_id number,p_process_status jai_ap_tds_inv_taxes.process_status%type) is
820: select count(tds_inv_tax_id)
821: from jai_ap_tds_inv_taxes
822: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
823: and invoice_id = p_invoice_id
817: and (actual_tax_id is not null or default_tax_id is not null);
818:
819: cursor c_check_if_processed(p_invoice_id number,p_process_status jai_ap_tds_inv_taxes.process_status%type) is
820: select count(tds_inv_tax_id)
821: from jai_ap_tds_inv_taxes
822: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
823: and invoice_id = p_invoice_id
824: and process_status = p_process_status;
825:
825:
826: cursor c_calculate_tax(p_invoice_id number) is
827: select tds_inv_tax_id, actual_tax_id,default_tax_id, amount, invoice_distribution_id,section_type --Xiao for Bug#7154864
828: /*Added Section Type - Bug 11070443*/
829: from jai_ap_tds_inv_taxes
830: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
831: and invoice_id = p_invoice_id
832: and (default_tax_id is not null or actual_tax_id is not null); --Xiao for Bug#7154864
833:
840: '. Setup needs modification.' tax_end_dated_message
841: from JAI_CMN_TAXES_ALL
842: where tax_id = p_tax_id;
843: /*Bug 5751783 - Selected non-rounded value for calculation*/
844: 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
845: select actual_section_code, (sum(amount*p_exchange_rate)-p_prepay_amt) invoice_amount, sum(calc_tax_amount) section_amount,
846: sum(tax_amount) tax_amount_orig
847: from jai_ap_tds_inv_taxes
848: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
843: /*Bug 5751783 - Selected non-rounded value for calculation*/
844: 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
845: select actual_section_code, (sum(amount*p_exchange_rate)-p_prepay_amt) invoice_amount, sum(calc_tax_amount) section_amount,
846: sum(tax_amount) tax_amount_orig
847: from jai_ap_tds_inv_taxes
848: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
849: and invoice_id = p_invoice_id
850: and section_type = p_section_type --rchandan for bug#4428980
851: and actual_section_code is not null
857: from JAI_AP_TDS_VNDR_TYPE_V
858: where vendor_id = p_vendor_id;
859:
860: cursor c_get_threshold
861: (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
862: select threshold_hdr_id
863: from JAI_AP_TDS_TH_VSITE_V
864: where vendor_id = p_vendor_id
865: and vendor_site_id = p_vendor_site_id
866: and section_type = p_section_type --rchandan for bug#4428980
867: and section_code = p_tds_section_code;
868:
869: cursor c_get_threshold_group
870: (p_vendor_id number, p_tan_no varchar2, p_pan_no varchar2, p_tds_section_code varchar2 , p_fin_year number,p_section_type jai_ap_tds_inv_taxes.section_type%type) IS --rchandan for bug#4428980
871: select threshold_grp_id
872: from jai_ap_tds_thhold_grps
873: where vendor_id = p_vendor_id
874: and section_type = p_section_type --rchandan for bug#4428980
925: /*Bug 5751783. Selected non-rounded value for calculation*/
926:
927: cursor c_get_taxes_to_generate_tds
928: (p_invoice_id number, p_tds_section_code varchar2, p_generate_all_invoices varchar2,
929: 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
930: select nvl(actual_tax_id,default_tax_id) actual_tax_id, --added nvl by Xiao for Bug#7154864
931: ( sum(amount*p_exchange_rate)-p_prepay_amt) taxable_amount, --Xiao for bug#6596019
932: sum(calc_tax_amount) tax_amount,
933: sum(tax_amount) tax_amount_orig
930: select nvl(actual_tax_id,default_tax_id) actual_tax_id, --added nvl by Xiao for Bug#7154864
931: ( sum(amount*p_exchange_rate)-p_prepay_amt) taxable_amount, --Xiao for bug#6596019
932: sum(calc_tax_amount) tax_amount,
933: sum(tax_amount) tax_amount_orig
934: from jai_ap_tds_inv_taxes
935: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
936: and invoice_id = p_invoice_id
937: and section_type = p_section_type --rchandan for bug#4428980
938: and actual_section_code = p_tds_section_code
1000: where set_of_books_id = cp_set_of_books_id;
1001:
1002: /*Bug 5751783. Selected non-rounded value for calculation*/
1003:
1004: 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
1005: select section_type,
1006: actual_tax_id,
1007: sum(amount*p_exchange_rate) taxable_amount,
1008: sum(calc_tax_amount) tax_amount,
1006: actual_tax_id,
1007: sum(amount*p_exchange_rate) taxable_amount,
1008: sum(calc_tax_amount) tax_amount,
1009: sum(tax_amount) tax_amount_orig
1010: from jai_ap_tds_inv_taxes
1011: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
1012: and invoice_id = p_invoice_id
1013: and section_type <> p_section_type --rchandan for bug#4428980
1014: and actual_tax_id is not null
1044: ---------------------------------------------------------
1045: /*
1046: cursor c_check_valid_tax(p_invoice_id number) is
1047: select actual_tax_id, default_tax_id
1048: from jai_ap_tds_inv_taxes
1049: where invoice_id = p_invoice_id
1050: and section_type = 'TDS_SECTION' ;
1051: */ --Commented by Jia for FP Bug#7368735
1052:
1054: ------------------------------------------------------------------------
1055: cursor c_check_valid_tax(p_invoice_id number) is
1056: /*
1057: select nvl(actual_section_code, default_section_code) section_code
1058: from jai_ap_tds_inv_taxes
1059: where section_type = 'TDS_SECTION'
1060: and invoice_id = p_invoice_id
1061: and (actual_section_code is not null or default_section_code is not null);
1062: */ --Commented by Jiaf or FP Bug#8278439
1060: and invoice_id = p_invoice_id
1061: and (actual_section_code is not null or default_section_code is not null);
1062: */ --Commented by Jiaf or FP Bug#8278439
1063: select jitc.section_code section_code
1064: from jai_cmn_taxes_all jitc, jai_ap_tds_inv_taxes jatit
1065: where jitc.tax_id = nvl(jatit.actual_tax_id, jatit.default_tax_id)
1066: and jatit.section_type = 'TDS_SECTION'
1067: and jatit.invoice_id = p_invoice_id
1068: and (actual_tax_id is not null or default_tax_id is not null);
1173:
1174:
1175: /* Update actual value from default value if actual is null for TDS section taxes only*/
1176: p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
1177: update jai_ap_tds_inv_taxes
1178: set actual_tax_id = default_tax_id
1179: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
1180: and invoice_id = p_invoice_id
1181: and actual_tax_id is null
1183: and section_type = lv_tds_section_type; --rchandan for bug#4428980
1184:
1185:
1186: /* Update processed for those cases where NO TDS has to be deducted for TDS section taxes only */
1187: update jai_ap_tds_inv_taxes
1188: set process_status = 'P'
1189: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
1190: and invoice_id = p_invoice_id
1191: and section_type = lv_tds_section_type --rchandan for bug#4428980
1378: /* bug 7280925. Added by Lakshmi Gopalsami
1379: * changed from ln_tmp_tds_amt to ln_tax_amount
1380: */
1381:
1382: update jai_ap_tds_inv_taxes
1383: set tax_amount = ln_tax_amount, -- ln_tmp_tds_amt, -- Bug 5722028
1384: actual_section_code = r_ja_in_tax_codes.section_code,
1385: calc_tax_amount = ln_tax_amount --Added by Bgowrava for bug#7154864
1386: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
1721: goto exit_from_procedure;
1722: END IF;
1723:
1724:
1725: /* Punch threshold_trx_id in jai_ap_tds_inv_taxes */
1726: update jai_ap_tds_inv_taxes
1727: set threshold_trx_id = ln_threshold_trx_id,
1728: threshold_slab_id_single = ln_threshold_slab_id_single
1729: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
1722: END IF;
1723:
1724:
1725: /* Punch threshold_trx_id in jai_ap_tds_inv_taxes */
1726: update jai_ap_tds_inv_taxes
1727: set threshold_trx_id = ln_threshold_trx_id,
1728: threshold_slab_id_single = ln_threshold_slab_id_single
1729: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
1730: and invoice_id = p_invoice_id
1746: end loop;
1747: /* Loop and generate invoices */
1748:
1749: p_codepath := jai_general_pkg.plot_codepath(25, p_codepath); /* 25 */
1750: update jai_ap_tds_inv_taxes
1751: set threshold_grp_id = ln_threshold_grp_id,
1752: threshold_hdr_id = ln_threshold_hdr_id,
1753: threshold_slab_id = ln_threshold_slab_id_after,
1754: process_status = 'P'
1850: end if;
1851:
1852: fnd_file.put_line(FND_FILE.LOG, '34. Start thhold trx id '|| ln_start_threshold_trx_id);
1853:
1854: /* Punch threshold_trx_id in jai_ap_tds_inv_taxes */
1855: update jai_ap_tds_inv_taxes
1856: set threshold_trx_id = ln_threshold_trx_id,
1857: process_status = 'P' /*Bug 4667681*/
1858: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
1851:
1852: fnd_file.put_line(FND_FILE.LOG, '34. Start thhold trx id '|| ln_start_threshold_trx_id);
1853:
1854: /* Punch threshold_trx_id in jai_ap_tds_inv_taxes */
1855: update jai_ap_tds_inv_taxes
1856: set threshold_trx_id = ln_threshold_trx_id,
1857: process_status = 'P' /*Bug 4667681*/
1858: where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
1859: and invoice_id = p_invoice_id
2780: and operating_unit_id = p_org_id;
2781:
2782: /* START, Added below cursors for Bgowrava for Bug#8254510*/
2783: cursor c_jai_ap_no_tds_trx(cp_threshold_grp_id number) is
2784: select jatit.invoice_id from jai_ap_tds_inv_taxes jatit
2785: where jatit.threshold_grp_id = cp_threshold_grp_id
2786: and jatit.tax_amount <> 0
2787: /*Bug 11880998 - Skip Invoices that are canceled without validation
2788: Such Invoices do not update Threshold Audits*/
2797: )
2798: minus
2799: /* select invoice_id from jai_ap_tds_thhold_trxs
2800: where threshold_grp_id = cp_threshold_grp_id; */ /* Commented and added below select for bug 14709400 */
2801: select invoice_id from jai_ap_tds_inv_taxes where invoice_id=p_invoice_id
2802: or (threshold_grp_id = cp_threshold_grp_id and nvl(threshold_slab_id_single,-99)<>-99 and threshold_trx_id is not null);
2803:
2804: /*Bug 13561970 - Modified cursor to get the number of records with THRESHOLD%. This would fetch both THRESHOLD TRANSITION and THRESHOLD ROLLBACK. Earlir it was THRESHOLD TRANSITION%*/
2805:
2809: and tds_event like 'THRESHOLD%';
2810:
2811: cursor c_jai_no_tds_trx_amt(cp_invoice_id number) is
2812: select sum(amount) amount, sum(tax_amount) tax_amount
2813: from jai_ap_tds_inv_taxes
2814: where invoice_id = cp_invoice_id
2815: and section_type='TDS_SECTION' /* Added for bug 14369937 */
2816: and actual_section_code is not null /* Added for bug 14369937 */
2817: group by invoice_id;
2864: /* Bug 5751783. Get the sum of invoice amount for which TDS is not calculated*/
2865: CURSOR get_tds_not_deducted ( cp_threshold_grp_id IN NUMBER )
2866: IS
2867: SELECT SUM (NVL (jatit.amount , 0 ) )
2868: FROM jai_ap_tds_inv_taxes jatit
2869: WHERE jatit.threshold_grp_id = cp_threshold_grp_id
2870: AND match_status_flag = 'A'
2871: AND jatit.process_status = 'P'
2872: AND jatit.tax_amount IS NOT NULL
2911: --Added by Xiao Lv for bug#8485691, related 11i bug#8439217, begin
2912: /*Bug 12434843(Patched in 11896260) - Fetch actual tax id for Threshold Transition. Use default only if actual tax id does not exist*/
2913: cursor c_default_tax_id(cp_invoice_id number) is
2914: select nvl(actual_tax_id, default_tax_id)
2915: from jai_ap_tds_inv_taxes
2916: where invoice_id = cp_invoice_id
2917: and section_type = 'TDS_SECTION'; /* Bug 15918300 */
2918: --and default_type = 'TAX'
2919: --and default_tax_id <> cp_tax_id;
2942: and tds_event like 'THRESHOLD ROLLBACK%';
2943:
2944: ln_rollback_available NUMBER;
2945: /*Ebd-Bug 9562876*/
2946: lv_codepath jai_ap_tds_inv_taxes.codepath%type;
2947:
2948:
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;
4561: IS
4562: CURSOR c_get_threshold_grp_id(p_prepay_distribution_id NUMBER)
4563: IS
4564: SELECT threshold_grp_id
4565: FROM jai_ap_tds_inv_taxes
4566: WHERE invoice_distribution_id = p_prepay_distribution_id
4567: AND section_type = 'TDS_SECTION';
4568:
4569: CURSOR c_get_threshold_grp_dtl(p_threshold_grp_id NUMBER)
4725: p_single_threshold_amt NUMBER)
4726: IS
4727: SELECT 'Y'
4728: FROM jai_ap_tds_thhold_trxs a,
4729: jai_ap_tds_inv_taxes b
4730: WHERE a.invoice_id = b.invoice_id
4731: AND b.invoice_distribution_id = p_invoice_distribution_id
4732: AND a.tds_event = 'INVOICE VALIDATE'
4733: AND a.taxable_amount >= p_single_threshold_amt;
4752: */
4753: CURSOR get_tds_not_deducted ( cp_threshold_grp_id IN NUMBER )
4754: IS
4755: SELECT SUM (NVL (jatit.amount , 0 ) )
4756: FROM jai_ap_tds_inv_taxes jatit
4757: WHERE jatit.threshold_grp_id = cp_threshold_grp_id
4758: AND match_status_flag = 'A'
4759: AND jatit.process_status = 'P'
4760: AND jatit.tax_amount IS NOT NULL