TDS Threshold Setup SQL query in Oracle Apps r12

TDS Threshold Setup SQL query in Oracle Apps r12

This query helps to validate TDS threshold setup in Oracle Application. This query helps to debug the issues in the tds threshold setups in oracle apps. This query will give the detail TDS threshold details in oracle apps.

 

 

Select THDr.*
 from jai_party_regs JPR,
     ap_invoices_all inv,
     jai_party_reg_lines jprl,
     jai_regimes REG,
     JAI_REPORTING_ASSOCIATIONS Jrv,
     JAI_AP_TDS_THHOLD_HDRS THDR
Where inv.vendor_id = jpr.party_id
  and jpr.party_type_code = 'THIRD_PARTY_SITE'
  and jpr.site_flag = 'Y'
  and jpr.supplier_flag = 'Y'
  and jpr.party_site_id = inv.vendor_site_id
  and jpr.ORG_CLASSIFICATION_CODE IS NULL
  and JPR.party_reg_id = jprl.party_reg_id
  and jprl.regime_id = REg.regime_id
  and reg.regime_id = thdr.regime_id
  and THDR.section_code = JPRl.default_section_code
  and jrv.entity_id = JPR.party_reg_id
  and jrv.entity_source_table = 'JAI_PARTY_REGS'
  and jrv.regime_id = reg.regime_id
  and jrv.reporting_type_name = 'Vendor Type'
  and thdr.vendor_type_lookup_code = jrv.Reporting_code
  and inv.invoice_id = '&invoice_id'
Union
Select THDr.*
  from JAI_AP_TDS_THHOLD_HDRS THDR,
       jai_ap_wthld_inv_taxes JAW,
       AP_invoice_distributions_all aid
 where jaw.Threshold_hdr_id = thdr.threshold_hdr_id
   and aid.invoice_id = JAW.invoice_id
   and aid.invoice_id = '&invoice_id'
Union
Select THDr.*
  from jai_party_regs JPR,
       ap_invoices_all inv,
       jai_regimes REG,
       JAI_REPORTING_ASSOCIATIONS Jrv,
       JAI_AP_TDS_THHOLD_HDRS THDR,
       jai_ap_wthld_inv_taxes JAW
Where inv.vendor_id = jpr.party_id
  and jpr.party_type_code = 'THIRD_PARTY_SITE'
  and jpr.site_flag = 'Y'
  and jpr.supplier_flag = 'Y'
  and jpr.party_site_id = inv.vendor_site_id
  and jpr.ORG_CLASSIFICATION_CODE IS NULL
  and inv.invoice_id = JAW.invoice_id
  and reg.regime_id = thdr.regime_id
  and THDR.section_code = JAW.actual_section_code
  and jrv.entity_id = JPR.party_reg_id
  and jrv.entity_source_table = 'JAI_PARTY_REGS'
  and jrv.regime_id = reg.regime_id
  and jrv.reporting_type_name = 'Vendor Type'
  and thdr.vendor_type_lookup_code = jrv.Reporting_code
  and inv.invoice_id = '&invoice_id' ;

 

select *
  from JAI_AP_TDS_THHOLD_TYPE_SLABS
 where threshold_hdr_id in (
         select THDr.threshold_hdr_id
           from jai_party_regs JPR,
                ap_invoices_all inv,
                jai_party_reg_lines jprl,
                jai_regimes REG,
                JAI_REPORTING_ASSOCIATIONS Jrv,
                JAI_AP_TDS_THHOLD_HDRS THDR
          Where inv.vendor_id = jpr.party_id
            and jpr.party_type_code = 'THIRD_PARTY_SITE'
            and jpr.site_flag = 'Y'
            and jpr.supplier_flag = 'Y'
            and jpr.party_site_id = inv.vendor_site_id
            and jpr.ORG_CLASSIFICATION_CODE IS NULL
            and JPR.party_reg_id = jprl.party_reg_id
            and jprl.regime_id = REg.regime_id
            and reg.regime_id = thdr.regime_id
            and THDR.section_code = JPRl.default_section_code
            and jrv.entity_id = JPR.party_reg_id
            and jrv.entity_source_table = 'JAI_PARTY_REGS'
            and jrv.regime_id = reg.regime_id
            and jrv.reporting_type_name = 'Vendor Type'
            and thdr.vendor_type_lookup_code = jrv.Reporting_code
            and inv.invoice_id = '&invoice_id'
       Union
       select THDr.threshold_hdr_id
         from JAI_AP_TDS_THHOLD_HDRS THDR,
              jai_ap_wthld_inv_taxes JAW,
              AP_invoice_distributions_all aid
        where jaw.Threshold_hdr_id = thdr.threshold_hdr_id
          and aid.invoice_id = JAW.invoice_id
          and aid.invoice_id = '&invoice_id'
        union
        select THDr.threshold_hdr_id
          from jai_party_regs JPR,
               ap_invoices_all inv,
               jai_regimes REG,
               JAI_REPORTING_ASSOCIATIONS Jrv,
               JAI_AP_TDS_THHOLD_HDRS THDR,
               jai_ap_wthld_inv_taxes JAW
         Where inv.vendor_id = jpr.party_id
           and jpr.party_type_code = 'THIRD_PARTY_SITE'
           and jpr.site_flag = 'Y'
           and jpr.supplier_flag = 'Y'
           and jpr.party_site_id = inv.vendor_site_id
           and jpr.ORG_CLASSIFICATION_CODE IS NULL
           and inv.invoice_id = JAW.invoice_id
           and reg.regime_id = thdr.regime_id
           and THDR.section_code = JAW.actual_section_code
           and jrv.entity_id = JPR.party_reg_id
           and jrv.entity_source_table = 'JAI_PARTY_REGS'
           and jrv.regime_id = reg.regime_id
           and jrv.reporting_type_name = 'Vendor Type'
           and thdr.vendor_type_lookup_code = jrv.Reporting_code
           and inv.invoice_id = '&invoice_id') ;