Oracle EBS EBTAX-EBTax Condition Sets

Oracle EBS EBTAX-EBTax Condition Sets

Tables:-
ZX_MCO_EO_RULES_V
ZX_PROCESS_RESULTS
ZX_CONDITION_GROUPS_VL
ZX_DET_FACTOR_TEMPL_VL
ZX_CONDITIONS
ZX_DETERMINING_FACTORS_VL
ZX_PARTY_TAX_PROFILE
ZX_SUBSCRIPTION_DETAILS

SQL Example:-

SELECT
b.CONDITION_GROUP_CODE "Tax Condition Set"
 ,c.CONDITION_GROUP_NAME  "Condition Set Name"
 ,RuleTLEO.DET_FACTOR_TEMPL_CODE  "Determining Factor Set"
 ,d.DET_FACTOR_TEMPL_NAME "Determining Factor Set Name"
 ,(select ft.TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL ft
  WHERE ft.TERRITORY_CODE=c.COUNTRY_CODE) "Country Name"
  ,c.enabled_flag
  ,(SELECT lkp4.meaning
  FROM FND_LOOKUPS lkp4
  WHERE lkp4.lookup_type ='ZX_DETERMINING_FACTOR_CLASS'
  AND lkp4.lookup_code   =e.DETERMINING_FACTOR_CLASS_CODE
  AND SYSDATE BETWEEN lkp4.START_DATE_ACTIVE AND NVL(lkp4.END_DATE_ACTIVE, SYSDATE)
  AND NVL(lkp4.ENABLED_FLAG, 'N') = 'Y'
  ) "Determining Factor Class"
  ,CASE
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'USER_DEFINED_GEOGRAPHY'
    OR e.DETERMINING_FACTOR_CLASS_CODE   = 'GEOGRAPHY'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_PLACE_OF_SUPPLY_TYPE', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'PARTY_FISCAL_CLASS'
    OR e.DETERMINING_FACTOR_CLASS_CODE   = 'PARTY_SITE_FISCAL_CLASS'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_GEO_PARTY_CQ', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'PARTY'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_PARTY_CQ', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'LEGAL_PARTY_FISCAL_CLASS'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_LEGAL_PARTY_CQ', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'REGISTRATION'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_REGISTRATION_CQ', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'ACCOUNTING_FLEXFIELD'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_ACCOUNT_FLEX_CQ', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'PRODUCT_GENERIC_CLASSIFICATION'
    OR e.DETERMINING_FACTOR_CLASS_CODE   = 'TRX_GENERIC_CLASSIFICATION'
    OR e.DETERMINING_FACTOR_CLASS_CODE   = 'DOCUMENT'
    OR e.DETERMINING_FACTOR_CLASS_CODE   = 'TRX_FISCAL_CLASS'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_PROD_TRX_CATEG', e.DETERMINING_FACTOR_CQ_CODE)
    ELSE NULL
  END "Class Qualifier"
  ,f.DETERMINING_FACTOR_NAME
  ,e.OPERATOR_CODE
  ,e.VALUE_LOW
  ,e.VALUE_HIGH
  ,e.IGNORE_FLAG
  FROM
  APPS.ZX_MCO_EO_RULES_V RuleTLEO,
  APPS.ZX_PROCESS_RESULTS b,
  APPS.ZX_CONDITION_GROUPS_VL c,
  APPS.ZX_DET_FACTOR_TEMPL_VL d,
  APPS.ZX_CONDITIONS e,
  APPS.ZX_DETERMINING_FACTORS_VL f,
  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 RegimeTLEO,
  APPS.ZX_TAXES_VL TaxTLEO
  WHERE 1=1
  AND RuleTLEO.CONTENT_OWNER_ID     =-99
AND RuleTLEO.TAX_RULE_ID            = b.TAX_RULE_ID
AND b.CONDITION_GROUP_ID            = c.CONDITION_GROUP_ID
AND c.CONDITION_GROUP_CODE          = e.CONDITION_GROUP_CODE
AND RuleTLEO.DET_FACTOR_TEMPL_CODE  = d.DET_FACTOR_TEMPL_CODE
AND e.DETERMINING_FACTOR_CLASS_CODE = f.DETERMINING_FACTOR_CLASS_CODE
AND e.DETERMINING_FACTOR_CODE       = f.DETERMINING_FACTOR_CODE
AND RuleTLEO. TAX_REGIME_CODE       = RegimeTLEO. TAX_REGIME_CODE
AND RuleTLEO. TAX                   = TaxTLEO.TAX
AND RuleTLEO.TAX_REGIME_CODE        = TaxTLEO.TAX_REGIME_CODE
AND zru.tax_regime_id               = RegimeTLEO.tax_regime_id
AND RegimeTLEO.tax_regime_code      = TaxTLEO.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);