Oracle EBS EBTAX-Document Classifications

Oracle EBS EBTAX-Document Classifications

Tables:-
ZX_FC_TYPES_VL
ZX_FC_CODES_DENORM_B

SQL Example:-

SELECT
a.CLASSIFICATION_TYPE_CODE FC_TYpe_Code
,a.CLASSIFICATION_TYPE_NAME FC_Type_Name
 ,a.CLASSIFICATION_TYPE_LEVEL_CODE Levels
 ,a.DELIMITER
 ,a.EFFECTIVE_FROM Type_EFF_FROM
 ,a.EFFECTIVE_TO Type_EFF_TO
 ,b.CLASSIFICATION_CODE FC_Code
 ,b.CLASSIFICATION_NAME FC_Name
 ,c.geography_name
 ,b.EFFECTIVE_FROM Code_EFF_FROM
 ,b.EFFECTIVE_TO Code_EFF_To
 FROM APPS.ZX_FC_TYPES_VL a ,
 APPS.ZX_FC_CODES_DENORM_B b,
 APPS.HZ_GEOGRAPHIES c,
 APPS.XLE_ENTITY_PROFILES d,
 APPS.HR_OPERATING_UNITS e
 WHERE 1=1 AND a.classification_type_code=a.classification_type_code
  and b.language='US'
  AND b.country_code = c.geography_code
 AND  c.geography_type   = 'COUNTRY'
 and c.geography_id =d.geography_id
 AND d.legal_entity_id=e.DEFAULT_LEGAL_CONTEXT_ID
 AND a.classification_type_code = 'DOCUMENT_SUBTYPE';

Oracle EBS EBTAX-Tax Statuses

Oracle EBS EBTAX-Tax Statuses

Tables:-
ZX_PARTY_TAX_PROFILE
ZX_SUBSCRIPTION_DETAILS
ZX_REGIMES_USAGES
ZX_MCO_EO_STATUS_V
ZX_REGIMES_VL

SQL Example:-

SELECT
 (SELECT ter.territory_short_name
  FROM fnd_territories_vl ter
  WHERE tax_regim.country_code = ter.territory_code(+)
  ) "Country Name"
,tax_stat.tax_regime_code
,tax_stat.tax
,tax_stat.tax_status_code
,tax_stat.tax_status_name
,tax_stat.content_owner_name
,tax_stat.effective_FROM
,tax_stat.effective_to
,tax_stat.default_status_flag
,tax_stat.default_flg_effective_FROM
,tax_stat.default_flg_effective_to
,tax_stat.allow_exemptions_flag
,tax_stat.allow_rate_override_flag
FROM APPS.ZX_PARTY_TAX_PROFILE PTP,
  APPS.ZX_SUBSCRIPTION_DETAILS ZSD,
  APPS.HR_OPERATING_UNITS HOU,
   APPS.ZX_REGIMES_USAGES ZRU,
  APPS.ZX_MCO_EO_STATUS_V TAX_STAT,
  APPS.ZX_REGIMES_VL TAX_REGIM
          WHERE 1=1 AND tax_stat.tax_regime_code  =tax_regim.tax_regime_code
AND tax_regim.tax_regime_code   =zru.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)
        AND hou.name= '&&';

Oracle EBS EBTAX-EBTAX Tax Rules

Oracle EBS EBTAX-EBTAX Tax Rules

Tables:-
ZX_RULES_VL
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
ZX_REGIMES_USAGES
ZX_REGIMES_VL
ZX_TAXES_VL

SQL Example:-

SELECT
DECODE(RuleTLEO.CONTENT_OWNER_ID,-99,'Global Configuration Owner',hou.name) "Configuration Owner"
,RuleTLEO.TAX_REGIME_CODE "Tax Regime Code"
,RuleTLEO.TAX "Tax"
,lkp.meaning  "Rule Type"
,RuleTLEO.DETERMINING_FACTOR_CQ_CODE  "Dflt Determine Tax Rate Rule"
,RuleTLEO.TAX_RULE_CODE "Rule Code"
,RuleTLEO.TAX_RULE_NAME "Rule Name"
,RuleTLEO.PRIORITY  "Rule Order"
,RuleTLEO.EFFECTIVE_FROM 
,RuleTLEO.EFFECTIVE_TO
,RuleTLEO.ENABLED_FLAG  "Enabled"
,b.TAX_STATUS_CODE  "Tax Status Code"
,(SELECT lkp2.MEANING
  FROM FND_LOOKUPS lkp2
  WHERE RuleTLEO.RECOVERY_TYPE_CODE = lkp2.LOOKUP_CODE
  AND lkp2.LOOKUP_TYPE              = 'ZX_RECOVERY_TYPES'
  AND SYSDATE BETWEEN NVL(lkp2.START_DATE_ACTIVE , SYSDATE) AND NVL(lkp2.END_DATE_ACTIVE, SYSDATE)
  AND lkp2.ENABLED_FLAG = 'Y'
  ) "Recovery Type"
  ,DECODE(RuleTLEO.TAX_EVENT_CLASS_CODE, NULL, DECODE(RuleTLEO.APPLICATION_ID,NULL,NULL,'EVENT_CLASS'),'TAX_EVENT_CLASS') "Event Class Category"
  ,DECODE(RuleTLEO.GEOGRAPHY_ID, NULL, 'No','Yes')  "Geography Specific Rule"
  ,RuleTLEO.DET_FACTOR_TEMPL_CODE "Determining Factor Set Code"
  ,d.DET_FACTOR_TEMPL_NAME  "Determining Factor Set Name"
  ,b.CONDITION_GROUP_CODE "Condition Set Code"
  ,c.CONDITION_GROUP_NAME "Name"
  ,b.priority "Order"
  ,DECODE(b.RESULT_TYPE_CODE, 'ADDRESS_TYPE',
  (SELECT MEANING
  FROM FND_LOOKUPS
  WHERE b.ALPHANUMERIC_RESULT = LOOKUP_CODE
  AND LOOKUP_TYPE             = 'ZX_PLACE_OF_SUPPLY_TYPE'
  AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, SYSDATE)
  AND NVL(ENABLED_FLAG, 'N') = 'Y'
  ), 'YES_NO',
  (SELECT MEANING
  FROM FND_LOOKUPS
  WHERE b.ALPHANUMERIC_RESULT = LOOKUP_CODE
  AND LOOKUP_TYPE             = 'ZX_YES_NO'
  AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, SYSDATE)
  AND NVL(ENABLED_FLAG, 'N') = 'Y'
  ), 'APPLICABILITY',
  (SELECT MEANING
  FROM FND_LOOKUPS
  WHERE b.ALPHANUMERIC_RESULT = LOOKUP_CODE
  AND LOOKUP_TYPE             = 'ZX_APPLICABILITY_TYPES'
  AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, SYSDATE)
  AND NVL(ENABLED_FLAG, 'N') = 'Y'
  ), b.ALPHANUMERIC_RESULT) "Result"
  ,b.enabled_flag "Condition Set Enable"
  ,(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  "Determining Factor Name"
  ,e.OPERATOR_CODE  "Operator"
  ,e.VALUE_LOW  "From Range"
  ,e.VALUE_HIGH "To Range"
  ,e.IGNORE_FLAG  "Ignore Condition"
  ,CASE WHEN RuleTLEO.service_type_code='DET_APPLICABLE_TAXES'
THEN DECODE(TaxTLEO.APPLICABLE_BY_DEFAULT_FLAG, 'Y', 'Applicable','Not Applicable')
WHEN RuleTLEO.service_type_code='CALCULATE_TAX_AMOUNTS'
THEN TaxTLEO.DEF_TAX_CALC_FORMULA
WHEN RuleTLEO.service_type_code='DET_PLACE_OF_SUPPLY'
THEN
(SELECT MEANING FROM FND_LOOKUPS WHERE LOOKUP_TYPE = 'ZX_PLACE_OF_SUPPLY_TYPE' AND LOOKUP_CODE =TaxTLEO.DEF_PLACE_OF_SUPPLY_TYPE_CODE)
WHEN RuleTLEO.service_type_code='DET_TAX_RATE'
THEN
(SELECT distinct TAX_RATE_CODE FROM ZX_MCO_LV_RATES_V R
 WHERE R.TAX_REGIME_CODE =RuleTLEO.TAX_REGIME_CODE AND R.TAX = RuleTLEO.TAX AND R.CONTENT_OWNER_ID =RuleTLEO.CONTENT_OWNER_ID AND Nvl(R.active_flag,'N') = 'Y'
 AND NVL(R.DEFAULT_RATE_FLAG,'N') = 'Y'
 AND R.TAX_STATUS_CODE =(SELECT distinct st.TAX_STATUS_CODE FROM ZX_MCO_LV_STATUS_V st  where st.TAX_REGIME_CODE =RuleTLEO.TAX_REGIME_CODE AND st.TAX = RuleTLEO.TAX and st.DEFAULT_STATUS_FLAG = 'Y' and st.CONTENT_OWNER_ID=RuleTLEO.CONTENT_OWNER_ID)
 )
WHEN RuleTLEO.service_type_code='DET_TAX_REGISTRATION'
THEN
(SELECT MEANING FROM FND_LOOKUPS WHERE LOOKUP_TYPE = 'ZX_REGISTRATION_CQ' AND LOOKUP_CODE =TaxTLEO.DEF_REGISTR_PARTY_TYPE_CODE)
WHEN RuleTLEO.service_type_code='DET_TAX_STATUS'
THEN
(SELECT distinct st.TAX_STATUS_CODE FROM ZX_MCO_LV_STATUS_V st  where st.TAX_REGIME_CODE =RuleTLEO.TAX_REGIME_CODE AND st.TAX = RuleTLEO.TAX and st.DEFAULT_STATUS_FLAG = 'Y' and st.CONTENT_OWNER_ID=RuleTLEO.CONTENT_OWNER_ID)
WHEN RuleTLEO.service_type_code='DET_TAXABLE_BASIS'
THEN TaxTLEO.DEF_TAXABLE_BASIS_FORMULA
END default_val
FROM APPS.ZX_RULES_VL 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,
  APPS.FND_LOOKUPS lkp WHERE 1=1 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 (RuleTLEO.CONTENT_OWNER_id=ptp.party_tax_profile_id OR RuleTLEO.CONTENT_OWNER_id=-99)
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)
AND RuleTLEO.service_type_code=lkp.LOOKUP_CODE
 AND  lkp.LOOKUP_TYPE    = 'ZX_SERVICE_TYPE_CODES'
AND lkp.LOOKUP_CODE NOT IN ('DET_TAX_REGIMES', 'TAX_LINES_DETERMINATION','DET_APPLICABLE_REGIMES','DET_OFFSET_TAX','DET_TAX_LINES','ROUNDING','DET_APPLICABLE_RECOVERY_TYPES', 'EXCEPTION', 'EXEMPTIONS')
AND SYSDATE BETWEEN lkp.START_DATE_ACTIVE AND NVL(lkp.END_DATE_ACTIVE, SYSDATE)
AND NVL(lkp.ENABLED_FLAG, 'N') = 'Y'
  AND hou.name= '&&';

Oracle EBS EBTAX-EBTAX Tax Rates

Oracle EBS EBTAX-EBTAX Tax Rates

Tables:-
ZX_RATES_VL
ZX_ACCOUNTS
ZX_RATES_B
AP_ZX_RATES_B

SQL Example:-

SELECT
 ratetleo.tax_regime_code
 ,ratetleo.tax
 ,ratetleo.tax_status_code
,ratetleo.tax_rate_name
,ratetleo.tax_rate_code
,ratetleo.tax_jurisdiction_code
,(SELECT DISTINCT a.content_owner_name
  FROM APPS.ZX_MCO_EO_RATES_V a
  WHERE a.tax_rate_id=ratetleo.tax_rate_id
  ) "Configuration Owner"
  ,ratetleo.rate_type_code
  ,ratetleo.percentage_rate
  ,ratetleo.effective_FROM
  ,ratetleo.effective_to
  ,ratetleo.active_flag
  ,ratetleo.vat_transaction_type_code "Tax Transaction Type"
  ,ratetleo.description "Tax Rate Description"
  ,ratetleo.default_rec_rate_code "Default Recovery Rate Code"
  ,ratetleo.recovery_type_code 
  ,ratetleo.recovery_rule_code
  ,ratetleo.def_rec_settlement_option_code
  ,ratetleo.offset_tax_rate_code
  ,ratetleo.offset_tax
  ,ratetleo.offset_status_code
  ,ratetleo.default_rate_flag
  ,ratetleo.default_flg_effective_FROM
  ,ratetleo.default_flg_effective_to
  ,ratetleo.inclusive_tax_flag
  ,ratetleo.tax_inclusive_override_flag
  ,ratetleo.allow_exemptions_flag
  ,ratetleo.allow_exceptions_flag
  ,ratetleo.allow_adhoc_tax_rate_flag
  ,ratetleo.adj_for_adhoc_amt_code
  ,ratetleo.taxable_basis_formula_code
  ,ratetleo.attribute1  "Tax Exempt"
  ,ratetleo.attribute9  "Auto Invoice-Duplicate Rates"
  ,ratetleo.attribute11 "Item Model"
  ,ratetleo.attribute10 "Tax Denomination"
  ,ap_ext.ATTRIBUTE1 TAX_CLASS
  ,ap_ext.ATTRIBUTE2 XCHANGE_PAY_WTH
  ,ap_ext.ATTRIBUTE3 TXN_TYPE
  ,ar_ext.ATTRIBUTE1 Tax_Class
  ,ar_ext.ATTRIBUTE2 Transaction_Type
  ,ar_ext.ATTRIBUTE3 Perception_Rate
  ,ar_ext.ATTRIBUTE4 IGV_Type
  ,ar_ext.ATTRIBUTE5 ISC_Type
  ,ar_ext.ATTRIBUTE6 Other_Concepts
  ,ar_ext.ATTRIBUTE7 Value_Type
  ,ar_ext.ATTRIBUTE8 Perception_Regimen
  FROM
  APPS.ZX_RATES_VL ratetleo,
  APPS.ZX_ACCOUNTS B,
  APPS.HR_OPERATING_UNITS HOU,
   APPS.ZX_RATES_B ar_ext,
  APPS.AP_ZX_RATES_B ap_ext
  WHERE 1=1 AND b.internal_organization_id = hou.organization_id
AND b.tax_account_entity_code  = 'RATES'
AND b.tax_account_entity_id    = ratetleo.tax_rate_id
AND ratetleo.active_flag = 'Y'
AND SYSDATE BETWEEN ratetleo.effective_FROM AND NVL(ratetleo.effective_to, SYSDATE)
AND RateTLEO.tax_rate_id=ap_ext.tax_rate_id(+)
AND RateTLEO.tax_rate_id=ar_ext.tax_rate_id(+) ; 

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;

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(+);

Oracle EBS EBTAX-Tax Rates Accounts

Oracle EBS EBTAX-Tax Rates Accounts

Tables:-
ZX_RATES_VL
ZX_ACCOUNTS
GL_LEDGERS
GL_CODE_COMBINATIONS_KFV

SQL Example:-

SELECT
hou.name  "Operationg Unit"
,zxr.tax_regime_code
,zxr.tax
,zxr.tax_status_code
,zxr.tax_rate_code
,zxr.tax_jurisdiction_code
,zxr.rate_type_code
,zxr.percentage_rate
,zxr.effective_FROM
,zxr.effective_to
,zxr.active_flag
,led.name "Ledger Name"
,gcc.concatenated_segments  "Liability Account"
FROM APPS.ZX_RATES_VL ZXR,
  APPS.ZX_ACCOUNTS B,
  APPS.HR_OPERATING_UNITS HOU,
APPS.GL_LEDGERS LED,
  APPS.GL_CODE_COMBINATIONS_KFV GCC WHERE 1=1 AND b.internal_organization_id = hou.organization_id
AND led.ledger_id                = hou.set_of_books_id
AND b.tax_account_entity_code    = 'RATES'
AND b.tax_account_entity_id      = zxr.tax_rate_id
AND gcc.code_combination_id      = b.tax_account_ccid
AND zxr.active_flag  = 'Y'
AND SYSDATE BETWEEN zxr.effective_FROM AND NVL(zxr.effective_to, SYSDATE); 

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) ;

Oracle EBS EBTAX-TAXES

Oracle EBS EBTAX-TAXES

Tables:-
ZX_PARTY_TAX_PROFILE
ZX_SUBSCRIPTION_DETAILS
ZX_REGIMES_USAGES
ZX_MCO_EO_TAXES_V
ZX_REPORT_CODES_ASSOC
Zx_Reporting_Types_VL
Zx_Reporting_Codes_VL

SQL Example:-

SELECT
taxtleo.tax_regime_code
,taxtleo.content_owner_name
,taxtleo.tax,taxtleo.tax_full_name
,taxtleo.tax_type_code
,taxtleo.effective_FROM
,taxtleo.effective_to
,taxtleo.live_for_processing_flag
,taxtleo.live_for_applicability_flag
,taxtleo.zone_geography_type
,taxtleo.parent_geography_type
,(SELECT hg.geography_name
  FROM HZ_GEOGRAPHIES hg
  WHERE taxtleo.parent_geography_id = hg.geography_id(+)
  ) "Parent Geography Name"
,taxtleo.override_geography_type
,taxtleo.tax_currency_code
,taxtleo.reporting_only_flag
,taxtleo.minimum_accountable_unit
,taxtleo.rounding_rule_code
,taxtleo.tax_precision
,(SELECT gdct.user_conversion_type
  FROM GL_DAILY_CONVERSION_TYPES gdct
  WHERE gdct.conversion_type(+) = taxtleo.exchange_rate_type
  ) "Exchange Rate"
,taxtleo.compounding_precedence
,(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 Auth"
,(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 Auth"
,DECODE(taxtleo.applied_amt_hANDling_flag,'P','Prorated','R','Recalculated')  "Applied Amount Handling"
,taxtleo.offset_tax_flag
,taxtleo.TAX_INCLUSIVE_OVERRIDE_FLAG
,taxtleo.ALLOW_ROUNDING_OVERRIDE_FLAG
,taxtleo.ALLOW_TAX_OVERRIDE_FLAG
,taxtleo.ALLOW_MANUAL_ENTRY_FLAG
,taxtleo.REGN_NUM_SAME_AS_LE_FLAG
,taxtleo.ALLOW_DUP_REGN_NUM_FLAG
,taxtleo.HAS_OTHER_JURISDICTIONS_FLAG
,taxtleo.ALLOW_MASS_CREATE_FLAG
,taxtleo.TAX_ACCOUNT_CREATE_METHOD_CODE
,taxtleo.TAX_ACCOUNT_SOURCE_TAX
,taxtleo.ALLOW_EXCEPTIONS_FLAG
,taxtleo.ALLOW_EXEMPTIONS_FLAG
,taxtleo.TAX_EXMPT_SOURCE_TAX
,taxtleo.TAX_EXMPT_CR_METHOD_CODE
,taxtleo.ALLOW_RECOVERABILITY_FLAG
,taxtleo.RECOVERY_RATE_OVERRIDE_FLAG
,taxtleo.PRIMARY_RECOVERY_TYPE_CODE
,taxtleo.PRIMARY_REC_RATE_DET_RULE_FLAG
,taxtleo.SEC_REC_RATE_DET_RULE_FLAG
,taxtleo.TAX_RATE_RULE_FLAG
,taxtleo.DEF_REC_SETTLEMENT_OPTION_CODE
,RepType.REPORTING_TYPE_CODE
,(SELECT lkp1.Meaning
  FROM Fnd_lookups lkp1
  WHERE lkp1.Lookup_type = 'ZX_REPORTING_DATA'
  AND lkp1.Lookup_code = RepType.reporting_type_datatype_code
  AND SYSDATE BETWEEN NVL(lkp1.START_DATE_ACTIVE, SYSDATE) AND NVL(lkp1.END_DATE_ACTIVE, SYSDATE)
  )
  ,DECODE(RepType.Reporting_Type_Datatype_Code,'TEXT',ReportCodesAssocEO.Reporting_Code_Char_Value,'YES_NO',
  ReportCodesAssocEO.Reporting_Code_Char_Value,'DATE',ReportCodesAssocEO.Reporting_Code_Date_Value,'NUMERIC_VALUE',
  TO_CHAR(ReportCodesAssocEO.Reporting_Code_Num_Value)),NVL2(RepCode.REPORTING_CODE_NAME, RepCode.REPORTING_CODE_NAME,
    (SELECT lkp.meaning
    FROM fnd_lookups lkp where lkp.lookup_type= 'ZX_YES_NO'
    AND lkp.lookup_code                      = ReportCodesAssocEO.REPORTING_CODE_CHAR_VALUE
AND SYSDATE BETWEEN lkp.START_DATE_ACTIVE
AND NVL(lkp.END_DATE_ACTIVE, SYSDATE)AND NVL(lkp.ENABLED_FLAG, 'N') = 'Y'
    AND RepType.Reporting_Type_Datatype_Code = 'YES_NO'
    ))  "RepType Description"
,ReportCodesAssocEO.EFFECTIVE_FROM  "RepType EFF From"
,ReportCodesAssocEO.EFFECTIVE_TO  "RepType EFF 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_MCO_EO_TAXES_V TAXTLEO ,
  ZX_REPORT_CODES_ASSOC ReportCodesAssocEO,
  Zx_Reporting_Types_VL RepType,
  Zx_Reporting_Codes_VL RepCode
  WHERE 1=1
  AND TaxTLEO.tax_regime_code       = zru.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 TAXTLEO.tax_id                =ReportCodesAssocEO.ENTITY_ID(+)
AND ReportCodesAssocEO.ENTITY_CODE(+)='ZX_TAXES'
AND ReportCodesAssocEO.Reporting_Type_Id = RepType.Reporting_Type_Id(+)
AND RepCode.Reporting_Type_Id(+)  = ReportCodesAssocEO.Reporting_Type_Id
AND RepCode.reporting_code_id(+)  = ReportCodesAssocEO.Reporting_Code_Id
AND zsd.parent_first_pty_org_id   = -99
AND SYSDATE BETWEEN zsd.effective_FROM AND NVL(zsd.effective_to,SYSDATE) ;

Oracle EBS EBTAX-EBTAX REPTYPE REPCODES

Oracle EBS EBTAX-EBTAX REPTYPE REPCODES

Tables:-
ZX_REPORTING_TYPES_VL
ZX_REPORTING_CODES_VL

SQL Example:-

SELECT
a.reporting_type_code
,a.reporting_type_name
,DECODE(a.legal_message_flag,'A','All','Y','Legal Justification Message Type','N','Tax Reporting Type',a.legal_message_flag) "Reporting Type Purpose"
,(SELECT lkp1.Meaning
  FROM Fnd_lookups lkp1
  WHERE lkp1.Lookup_type = 'ZX_REPORTING_DATA'
  AND lkp1.Lookup_code = a.reporting_type_datatype_code
  AND SYSDATE BETWEEN NVL(lkp1.START_DATE_ACTIVE, SYSDATE) AND NVL(lkp1.END_DATE_ACTIVE, SYSDATE)
  ) "Data Type"
  ,a.has_reporting_codes_flag
  ,a.tax_regime_code
  ,a.Tax
  ,a.max_length
  ,a.min_length
  ,a.effective_FROM "REP Type EFF From"
  ,a.effective_to "REP Type EFF TO"
  ,b.reporting_code_char_value
  ,b.reporting_code_name
  ,b.effective_FROM "REP CODE EFF FROM"
  ,b.effective_to "REP CODE EFF TO"
  FROM apps.ZX_REPORTING_TYPES_VL a,
apps.ZX_REPORTING_CODES_VL b WHERE 1=1 AND a.reporting_type_id =b.reporting_type_id(+);

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);

Oracle EBS EBTAX-EBTax Determining Factors

Oracle EBS EBTAX-EBTax Determining Factors

Tables:-
ZX_DETERMINING_FACTORS_VL

SQL Example:-

SELECT
Lookup.MEANING  "Determining Factor Class"
,zdfv.DETERMINING_FACTOR_NAME "Determining Factor Name"
,zdfv.DETERMINING_FACTOR_DESC "Description"
,CASE
    WHEN zdfv.tax_rules_flag = 'Y'
    THEN 'Tax Rules'
    WHEN zdfv.tax_regime_det_flag = 'Y'
    THEN 'Tax Regime Determination'
    WHEN zdfv.taxable_basis_flag = 'Y'
    THEN 'Taxable Basis Formula'
  END "Determining Factor Usage"
  FROM APPS.ZX_DETERMINING_FACTORS_VL zdfv,
  APPS.FND_LOOKUPS Lookup
  WHERE 1=1
  AND zdfv.DETERMINING_FACTOR_CLASS_CODE = Lookup.LOOKUP_CODE
AND Lookup.LOOKUP_TYPE                 = 'ZX_DETERMINING_FACTOR_CLASS'
AND Lookup.LOOKUP_CODE                <> 'DERIVED'
AND SYSDATE BETWEEN Lookup.START_DATE_ACTIVE AND NVL(Lookup.END_DATE_ACTIVE, SYSDATE)
AND NVL(Lookup.ENABLED_FLAG, 'N') = 'Y'
AND( zdfv.tax_rules_flag          = 'Y'
OR zdfv.tax_regime_det_flag       = 'Y'
OR zdfv.taxable_basis_flag        = 'Y');

Oracle EBS Latam Tax-Latin Tax Categories

Oracle EBS Latam Tax-Latin Tax Categories

Tables:-
JL_ZZ_AR_TX_CATEGRY_V

SQL Example:-

SELECT
hou.name  "Name"
,jzv.tax_category "Tax Category"
 ,TO_CHAR(jzv.start_date_active,'DD-MM-YYYY') "Effective From"
 ,jzv.description "Category Description"
 ,jzv.threshold_check_level_desc  "Threshold Check"
 ,jzv.threshold_check_grp_by_desc_nb  "Grouping Condition Type"
 ,jzv.threshold_check_grp_by_desc "Grouping Condition"
 ,jzv.org_tax_attribute_desc    "Org Determing Condition"
 ,jzv.cus_tax_attribute "Cust Determining Condtion"
 ,jzv.txn_tax_attribute "Trans Determining Condition"
 ,jzv.mandatory_in_class  "Mandatory In Class"
 ,jzv.tax_code 
 ,jzv.tax_rate 
 ,jzv.tax_authority_code
 ,jzv.print_flag
 FROM
 APPS.JL_ZZ_AR_TX_CATEGRY_V jzv
 , APPS.hr_operating_units hou
 WHERE 1=1
 AND jzv.org_id = hou.organization_id;