Oracle EBS EBTAX-EBTAX TAX REGIME

Oracle EBS EBTAX-EBTAX TAX REGIME

Tables:-
ZX_REGIMES_USAGES
ZX_SUBSCRIPTION_OPTIONS
ZX_FIRST_PARTY_ORGS_ALL_V
ZX_PARTY_TAX_PROFILE
ZX_REGIMES_VL
FND_TERRITORIES_VL
HR_OPERATING_UNITS
HZ_PARTIES
HZ_PARTIES
ZX_PARTY_TAX_PROFILE
ZX_PARTY_TAX_PROFILE
GL_DAILY_CONVERSION_TYPES

SQL Example:-

SELECT
hou.name  "Operating Unit"
,(SELECT A.name FROM APPS.XLE_ENTITY_PROFILES A
WHERE a.legal_entity_id=hou.default_legal_context_id) "Legal Entity"
,a.tax_regime_code
,a.tax_regime_name
,a.country_or_group_code
,a.parent_regime_code
,ter.territory_short_name
,a.effective_FROM
,a.effective_to
,a.allow_recoverability_flag
,a.allow_exemptions_flag
,a.tax_inclusive_override_flag
,a.allow_exceptions_flag
,a.allow_rounding_override_flag
,a.tax_currency_code
,c.user_conversion_type
,a.minimum_accountable_unit
,a.rounding_rule_code
,a.tax_precision
,a.def_inclusive_tax_flag
,a.exchange_rate_type
,a.has_other_jurisdictions_flag
,a.def_rec_settlement_option_code
,a.use_legal_msg_flag
,a.tax_account_precedence_code
,a.regn_num_same_as_le_flag
,a.cross_regime_compounding_flag
,a.regime_precedence
,a.has_sub_regime_flag
,a.country_code
,hz_p1.party_name "Collecting Auth"
,hz_p2.party_name "Reporting Auth"
,pty.party_name "Party Name"
,(SELECT lkp.meaning
  FROM FND_LOOKUPS LKP
  WHERE lkp.lookup_type = 'ZX_PTP_PARTY_TYPE'
  AND lkp.lookup_code   = pty.party_type_code
  AND SYSDATE BETWEEN lkp.start_date_active AND NVL(lkp.end_date_active, SYSDATE)
  AND NVL(lkp.enabled_flag, 'N') = 'Y'
  ) "Party Type"
          ,pty.address_line_1
  || ' '
  || pty.address_line_2
  || ' '
  || pty.address_line_3
  || ' '
  || pty.town_or_city
  || ' '
  || pty.region_1
  || ' '
  || pty. region_2
  || ' '
  || pty.postal_code  "Address"
        ,ter.territory_short_name "Party Country"
        ,zso.subscription_option_code "Config for Product Exception"
        ,zso.exception_option_code  "Config for Taxes Rules"
        ,zso.effective_FROM "Party Effective From"
        ,zso.effective_to "Party Effective To"
        ,zso.enabled_flag   "Enabled"
        FROM  APPS.ZX_REGIMES_USAGES regm_usg,
  APPS.ZX_SUBSCRIPTION_OPTIONS ZSO,
  APPS.ZX_FIRST_PARTY_ORGS_ALL_V pty,
  APPS.ZX_PARTY_TAX_PROFILE ptp,
  APPS.ZX_REGIMES_VL a,
  APPS.FND_TERRITORIES_VL ter,
  APPS.HR_OPERATING_UNITS hou,
  APPS.HZ_PARTIES hz_P1,
  APPS.HZ_PARTIES hz_P2,
  APPS.ZX_PARTY_TAX_PROFILE ZPTP1,
  APPS.ZX_PARTY_TAX_PROFILE ZPTP2,
  APPS.GL_DAILY_CONVERSION_TYPES c
        WHERE 1=1 AND ZSO.regime_usage_id     = regm_usg.regime_usage_id
AND regm_usg.first_pty_org_id = pty.party_tax_profile_id
AND regm_usg.first_pty_org_id = ptp.party_tax_profile_id
AND regm_usg.tax_regime_id    = a.tax_regime_id
AND a.country_code          = ter.territory_code(+)
AND a.rep_tax_authority_id  = zptp2.party_tax_profile_id(+)
AND zptp2.party_id          = hz_P2.party_id (+)
AND a.coll_tax_authority_id = zptp1.party_tax_profile_id(+)
AND zptp1.party_id          = hz_p1.party_id (+)
AND c.conversion_type (+) = a.exchange_rate_type
AND ptp.party_id          = hou.organization_id(+);