Wednesday, August 31, 2016

Query to get the EBusiness Tax Details

SELECT z_reg1.tax_regime_id,
  z_reg1.tax_regime_code,
  z_reg1.country_code,
  z_reg1.tax_currency_code,
  z_tx2.Tax_id,
  z_tx2.Tax,
  z_tx2.tax_type_code,
  z_tx2.def_place_of_supply_type_code,
  z_tx2.zone_geography_type,
  z_tx2.def_primary_rec_rate_code, 
  NVL(hou.name,'Global Configuration Owner') OWNER,
  z_stat3.tax_status_code,
  z_stat3.default_status_flag  ,
  z_rate4.tax_rate_code,z_rate4.rate_type_code,z_rate4.rate_type_code,z_rate4.percentage_rate,z_rate4.active_flag,z_rate4.default_rate_flag,z_rate4.default_rec_rate_code
FROM apps.ZX_REGIMES_B z_reg1,
  apps.ZX_TAXES_B z_tx2,
  apps.zx_party_tax_profile z_prof,
  apps.hr_operating_units hou,
  apps.ZX_STATUS_B z_stat3,
  apps.ZX_RATES_B z_rate4
WHERE 1= 1
AND z_rate4.tax_regime_code = z_reg1.tax_regime_code
AND z_rate4.tax = z_tx2.tax
AND z_rate4.tax_status_code = z_stat3.tax_status_code
AND z_stat3.tax = z_tx2.tax
AND z_stat3.tax_regime_code = z_reg1.tax_regime_code
AND hou.organization_id(+) = z_prof.party_id
AND z_prof.party_tax_profile_id = z_tx2.content_owner_id
AND z_tx2.tax_regime_code = z_reg1.tax_regime_code
AND z_reg1.tax_regime_code <> 'VERTEX SALES TAX US'
AND sysdate BETWEEN NVL(z_reg1.effective_from,sysdate) AND NVL(z_reg1.effective_to,sysdate)
AND sysdate BETWEEN NVL(z_tx2.effective_from,sysdate) AND NVL(z_tx2.effective_to,sysdate)
AND sysdate BETWEEN NVL(z_stat3.effective_from,sysdate) AND NVL(z_stat3.effective_to,sysdate)
AND sysdate BETWEEN NVL(z_rate4.effective_from,sysdate) AND NVL(z_rate4.effective_to,sysdate)
order by z_reg1.country_code,z_reg1.tax_regime_code,z_tx2.Tax,z_stat3.tax_status_code, hou.name

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect