Oracle EBS EBTAX-Party Tax Profiles

Oracle EBS EBTAX-Party Tax Profiles

Tables:-
ZX_REGIMES_USAGES
ZX_SUBSCRIPTION_OPTIONS
ZX_FIRST_PARTY_ORGS_ALL_V
ZX_PARTY_TAX_PROFILE
ZX_REGIMES_VL
FND_TERRITORIES_VL

SQL Example:-

SELECT
xle.name  "Legal Entity"
,hou.name "Operating Unit"
,(SELECT lkp.meaning
  FROM APPS.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.party_name
          ,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 "Country Name"
        ,a.tax_regime_code
        ,a.tax_regime_name
        ,(SELECT lkp1.meaning
  FROM APPS.FND_LOOKUPS lkp1
  WHERE lkp1.lookup_type = 'ZX_SUBSCRIPTION_OPTION'
  AND lkp1.lookup_code   = ZSO.SUBSCRIPTION_OPTION_CODE
  AND SYSDATE BETWEEN lkp1.start_date_active AND NVL(lkp1.end_date_active, SYSDATE)
  AND NVL(lkp1.enabled_flag, 'N') = 'Y'
  ) "Config for Product Exceptions"
        ,(SELECT lkp1.meaning
  FROM APPS.FND_LOOKUPS lkp1
  WHERE lkp1.lookup_type = 'ZX_SUBSCRIPTION_OPTION'
  AND lkp1.lookup_code   = ZSO.EXCEPTION_OPTION_CODE
  AND SYSDATE BETWEEN lkp1.start_date_active AND NVL(lkp1.end_date_active, SYSDATE)
  AND NVL(lkp1.ENABLED_FLAG, 'N') = 'Y'
  ) "Config for Taxes Rules"
        ,a.effective_FROM "Effective From"
        ,a.effective_to "Effective To"
        ,ZSO.effective_FROM "Party Eff From"
        ,ZSO.effective_to "Party Eff To"
        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.XLE_ENTITY_PROFILES xle
  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 pty.party_type_code       =ptp.party_type_code
AND pty.party_tax_profile_id  =ptp.party_tax_profile_id
AND regm_usg.tax_regime_id    = a.tax_regime_id
AND a.country_code            = ter.territory_code(+)
AND ptp.party_id              = hou.organization_id
AND ptp.party_type_code       = 'OU'
AND xle.legal_entity_id       =hou.default_legal_context_id;