Oracle EBS EBTAX-Tax Jurisdictions

Oracle EBS EBTAX-Tax Jurisdictions

Tables:-
ZX_PARTY_TAX_PROFILE
ZX_SUBSCRIPTION_DETAILS
ZX_REGIMES_USAGES
ZX_REGIMES_VL
ZX_MCO_EO_TAXES_V
ZX_JURISDICTIONS_VL

SQL Example:-

SELECT
hou.name
,jurisdictiontleo.tax_jurisdiction_code
 ,jurisdictiontleo.tax_jurisdiction_name
 ,jurisdictiontleo.tax_regime_code
 ,jurisdictiontleo.tax
 ,taxtleo.parent_geography_type
 ,(SELECT hg.geography_name
  FROM HZ_GEOGRAPHIES hg
  WHERE taxtleo.parent_geography_id = hg.geography_id(+)
  ) "Parent Geography Name"
,taxtleo.zone_geography_type
,jurisdictiontleo.effective_FROM
,jurisdictiontleo.effective_to
,jurisdictiontleo.inner_city_jurisdiction_flag
,(SELECT hg1.geography_name
  FROM HZ_GEOGRAPHIES hg1
  WHERE jurisdictiontleo.zone_geography_id = hg1.geography_id(+)
  ) "Geography Name"
,JurisdictionTLEO.PRECEDENCE_LEVEL
,(SELECT DISTINCT collauth.party_name
  FROM HZ_PARTIES collauth,
ZX_PARTY_TAX_PROFILE collptp
  WHERE collptp.party_id            = collauth.party_id(+)
  AND taxtleo.coll_tax_authority_id = collptp.party_tax_profile_id(+)
  ) "Collecting Tax Authority"
,(SELECT DISTINCT repauth.party_name
  FROM HZ_PARTIES repauth,
ZX_PARTY_TAX_PROFILE repptp
  WHERE repptp.party_id            = repauth.party_id(+)
  AND taxtleo.rep_tax_authority_id = repptp.party_tax_profile_id(+)
  ) "Reporting Tax Authority"
,jurisdictiontleo.default_jurisdiction_flag
,jurisdictiontleo.default_flg_effective_FROM
,jurisdictiontleo.default_flg_effective_to
FROM
APPS.ZX_PARTY_TAX_PROFILE PTP,
 APPS.ZX_SUBSCRIPTION_DETAILS ZSD,
APPS.HR_OPERATING_UNITS HOU,
 APPS.ZX_REGIMES_USAGES ZRU,
 APPS.ZX_REGIMES_VL TAX_REGIM,
 APPS.ZX_MCO_EO_TAXES_V TAXTLEO,
 APPS.ZX_JURISDICTIONS_VL JURISDICTIONTLEO
WHERE 1=1 AND zru.tax_regime_code       =tax_regim.tax_regime_code
AND tax_regim.tax_regime_code   =taxtleo.tax_regime_code
AND taxtleo.tax                 = jurisdictiontleo.tax
AND taxtleo.tax_regime_code     = jurisdictiontleo.tax_regime_code
AND tax_regim.tax_regime_code   = jurisdictiontleo.tax_regime_code
AND ptp.party_type_code         = 'OU'
AND ptp.party_id                = hou.organization_id
AND zru.first_pty_org_id        = ptp.party_tax_profile_id
AND zru.first_pty_org_id        = zsd.first_pty_org_id
AND zsd.tax_regime_code         = zru.tax_regime_code
AND zsd.parent_first_pty_org_id = -99
AND SYSDATE BETWEEN zsd.effective_FROM AND NVL(zsd.effective_to,SYSDATE) ;