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