select
a.ROWID ROW_ID,
a.TAX_ID,
a.TAX,
a.CONTENT_OWNER_ID,
a.EFFECTIVE_FROM,
a.EFFECTIVE_TO,
a.TAX_REGIME_CODE,
a.TAX_TYPE_CODE,
a.ALLOW_MANUAL_ENTRY_FLAG,
a.ALLOW_TAX_OVERRIDE_FLAG,
a.MIN_TXBL_BSIS_THRSHLD,
a.MAX_TXBL_BSIS_THRSHLD,
a.MIN_TAX_RATE_THRSHLD,
a.MAX_TAX_RATE_THRSHLD,
a.MIN_TAX_AMT_THRSHLD,
a.MAX_TAX_AMT_THRSHLD,
a.COMPOUNDING_PRECEDENCE,
a.PERIOD_SET_NAME,
a.EXCHANGE_RATE_TYPE,
a.TAX_CURRENCY_CODE,
a.REP_TAX_AUTHORITY_ID,
a.COLL_TAX_AUTHORITY_ID,
a.TAX_PRECISION,
a.MINIMUM_ACCOUNTABLE_UNIT,
a.ROUNDING_RULE_CODE,
a.TAX_STATUS_RULE_FLAG,
a.TAX_RATE_RULE_FLAG,
a.DEF_PLACE_OF_SUPPLY_TYPE_CODE,
a.PLACE_OF_SUPPLY_RULE_FLAG,
a.DIRECT_RATE_RULE_FLAG,
a.APPLICABILITY_RULE_FLAG,
a.TAX_CALC_RULE_FLAG,
a.TXBL_BSIS_THRSHLD_FLAG,
a.TAX_RATE_THRSHLD_FLAG,
a.TAX_AMT_THRSHLD_FLAG,
a.TAXABLE_BASIS_RULE_FLAG,
a.DEF_INCLUSIVE_TAX_FLAG,
a.THRSHLD_GROUPING_LVL_CODE,
a.THRSHLD_CHK_TMPLT_CODE,
a.HAS_OTHER_JURISDICTIONS_FLAG,
a.ALLOW_EXEMPTIONS_FLAG,
a.ALLOW_EXCEPTIONS_FLAG,
a.ALLOW_RECOVERABILITY_FLAG,
a.DEF_TAX_CALC_FORMULA,
a.TAX_INCLUSIVE_OVERRIDE_FLAG,
a.DEF_TAXABLE_BASIS_FORMULA,
a.DEF_REGISTR_PARTY_TYPE_CODE,
a.REGISTRATION_TYPE_RULE_FLAG,
a.REPORTING_ONLY_FLAG,
a.AUTO_PRVN_FLAG,
a.LIVE_FOR_PROCESSING_FLAG,
a.HAS_DETAIL_TB_THRSHLD_FLAG,
a.HAS_TAX_DET_DATE_RULE_FLAG,
a.HAS_EXCH_RATE_DATE_RULE_FLAG,
a.HAS_TAX_POINT_DATE_RULE_FLAG,
a.PRINT_ON_INVOICE_FLAG,
a.USE_LEGAL_MSG_FLAG,
a.CALC_ONLY_FLAG,
a.PRIMARY_RECOVERY_TYPE_CODE,
a.PRIMARY_REC_TYPE_RULE_FLAG,
a.SECONDARY_RECOVERY_TYPE_CODE,
a.SECONDARY_REC_TYPE_RULE_FLAG,
a.PRIMARY_REC_RATE_DET_RULE_FLAG,
a.SEC_REC_RATE_DET_RULE_FLAG,
a.OFFSET_TAX_FLAG,
a.RECOVERY_RATE_OVERRIDE_FLAG,
a.ZONE_GEOGRAPHY_TYPE,
a.REGN_NUM_SAME_AS_LE_FLAG,
a.DEF_REC_SETTLEMENT_OPTION_CODE,
a.PARENT_GEOGRAPHY_TYPE,
a.PARENT_GEOGRAPHY_ID,
a.ALLOW_MASS_CREATE_FLAG,
a.APPLIED_AMT_HANDLING_FLAG,
a.SOURCE_TAX_FLAG,
a.DEF_PRIMARY_REC_RATE_CODE,
a.DEF_SECONDARY_REC_RATE_CODE,
a.ALLOW_DUP_REGN_NUM_FLAG,
a.TAX_ACCOUNT_SOURCE_TAX,
a.TAX_ACCOUNT_CREATE_METHOD_CODE,
a.TAX_EXMPT_SOURCE_TAX,
a.TAX_EXMPT_CR_METHOD_CODE,
a.OVERRIDE_GEOGRAPHY_TYPE,
a.LIVE_FOR_APPLICABILITY_FLAG,
a.OBJECT_VERSION_NUMBER,
a.CREATED_BY,
a.CREATION_DATE,
a.LAST_UPDATED_BY,
a.LAST_UPDATE_DATE,
a.LAST_UPDATE_LOGIN,
a.REQUEST_ID,
a.PROGRAM_APPLICATION_ID,
a.PROGRAM_ID,
a.PROGRAM_LOGIN_ID,
a.RECORD_TYPE_CODE,
a.ALLOW_ROUNDING_OVERRIDE_FLAG,
a.SPECIAL_INCLUSIVE_TAX_FLAG,
a.ATTRIBUTE1,
a.ATTRIBUTE2,
a.ATTRIBUTE3,
a.ATTRIBUTE4,
a.ATTRIBUTE5,
a.ATTRIBUTE6,
a.ATTRIBUTE7,
a.ATTRIBUTE8,
a.ATTRIBUTE9,
a.ATTRIBUTE10,
a.ATTRIBUTE11,
a.ATTRIBUTE12,
a.ATTRIBUTE13,
a.ATTRIBUTE14,
a.ATTRIBUTE15,
a.ATTRIBUTE_CATEGORY,
tl.TAX_FULL_NAME,
sd.first_pty_org_id SDCO_ID,
sd.effective_from SDEFF_FROM,
sd.effective_to SDEFF_TO,
a.APPLICABLE_BY_DEFAULT_FLAG,
a.LEGAL_REPORTING_STATUS_DEF_VAL
from ZX_TAXES_B a, ZX_TAXES_TL tl, zx_subscription_details sd
where a.tax_id = tl.tax_id
and tl.LANGUAGE = userenv('LANG')
and a.tax_regime_code = sd.tax_regime_code
and a.content_owner_id = sd.parent_first_pty_org_id
and (sd.view_options_code in ('NONE', 'VFC')
or
(sd.view_options_code = 'VFR'
and not exists ( select 1 /*+ no unnest*/ from zx_taxes_b b
where b.tax_regime_code = a.tax_regime_code
and b.tax = a.tax
and b.content_owner_id = sd.first_pty_org_id
)
)
)
SELECT
A.ROWID ROW_ID
,
A.TAX_ID
,
A.TAX
,
A.CONTENT_OWNER_ID
,
A.EFFECTIVE_FROM
,
A.EFFECTIVE_TO
,
A.TAX_REGIME_CODE
,
A.TAX_TYPE_CODE
,
A.ALLOW_MANUAL_ENTRY_FLAG
,
A.ALLOW_TAX_OVERRIDE_FLAG
,
A.MIN_TXBL_BSIS_THRSHLD
,
A.MAX_TXBL_BSIS_THRSHLD
,
A.MIN_TAX_RATE_THRSHLD
,
A.MAX_TAX_RATE_THRSHLD
,
A.MIN_TAX_AMT_THRSHLD
,
A.MAX_TAX_AMT_THRSHLD
,
A.COMPOUNDING_PRECEDENCE
,
A.PERIOD_SET_NAME
,
A.EXCHANGE_RATE_TYPE
,
A.TAX_CURRENCY_CODE
,
A.REP_TAX_AUTHORITY_ID
,
A.COLL_TAX_AUTHORITY_ID
,
A.TAX_PRECISION
,
A.MINIMUM_ACCOUNTABLE_UNIT
,
A.ROUNDING_RULE_CODE
,
A.TAX_STATUS_RULE_FLAG
,
A.TAX_RATE_RULE_FLAG
,
A.DEF_PLACE_OF_SUPPLY_TYPE_CODE
,
A.PLACE_OF_SUPPLY_RULE_FLAG
,
A.DIRECT_RATE_RULE_FLAG
,
A.APPLICABILITY_RULE_FLAG
,
A.TAX_CALC_RULE_FLAG
,
A.TXBL_BSIS_THRSHLD_FLAG
,
A.TAX_RATE_THRSHLD_FLAG
,
A.TAX_AMT_THRSHLD_FLAG
,
A.TAXABLE_BASIS_RULE_FLAG
,
A.DEF_INCLUSIVE_TAX_FLAG
,
A.THRSHLD_GROUPING_LVL_CODE
,
A.THRSHLD_CHK_TMPLT_CODE
,
A.HAS_OTHER_JURISDICTIONS_FLAG
,
A.ALLOW_EXEMPTIONS_FLAG
,
A.ALLOW_EXCEPTIONS_FLAG
,
A.ALLOW_RECOVERABILITY_FLAG
,
A.DEF_TAX_CALC_FORMULA
,
A.TAX_INCLUSIVE_OVERRIDE_FLAG
,
A.DEF_TAXABLE_BASIS_FORMULA
,
A.DEF_REGISTR_PARTY_TYPE_CODE
,
A.REGISTRATION_TYPE_RULE_FLAG
,
A.REPORTING_ONLY_FLAG
,
A.AUTO_PRVN_FLAG
,
A.LIVE_FOR_PROCESSING_FLAG
,
A.HAS_DETAIL_TB_THRSHLD_FLAG
,
A.HAS_TAX_DET_DATE_RULE_FLAG
,
A.HAS_EXCH_RATE_DATE_RULE_FLAG
,
A.HAS_TAX_POINT_DATE_RULE_FLAG
,
A.PRINT_ON_INVOICE_FLAG
,
A.USE_LEGAL_MSG_FLAG
,
A.CALC_ONLY_FLAG
,
A.PRIMARY_RECOVERY_TYPE_CODE
,
A.PRIMARY_REC_TYPE_RULE_FLAG
,
A.SECONDARY_RECOVERY_TYPE_CODE
,
A.SECONDARY_REC_TYPE_RULE_FLAG
,
A.PRIMARY_REC_RATE_DET_RULE_FLAG
,
A.SEC_REC_RATE_DET_RULE_FLAG
,
A.OFFSET_TAX_FLAG
,
A.RECOVERY_RATE_OVERRIDE_FLAG
,
A.ZONE_GEOGRAPHY_TYPE
,
A.REGN_NUM_SAME_AS_LE_FLAG
,
A.DEF_REC_SETTLEMENT_OPTION_CODE
,
A.PARENT_GEOGRAPHY_TYPE
,
A.PARENT_GEOGRAPHY_ID
,
A.ALLOW_MASS_CREATE_FLAG
,
A.APPLIED_AMT_HANDLING_FLAG
,
A.SOURCE_TAX_FLAG
,
A.DEF_PRIMARY_REC_RATE_CODE
,
A.DEF_SECONDARY_REC_RATE_CODE
,
A.ALLOW_DUP_REGN_NUM_FLAG
,
A.TAX_ACCOUNT_SOURCE_TAX
,
A.TAX_ACCOUNT_CREATE_METHOD_CODE
,
A.TAX_EXMPT_SOURCE_TAX
,
A.TAX_EXMPT_CR_METHOD_CODE
,
A.OVERRIDE_GEOGRAPHY_TYPE
,
A.LIVE_FOR_APPLICABILITY_FLAG
,
A.OBJECT_VERSION_NUMBER
,
A.CREATED_BY
,
A.CREATION_DATE
,
A.LAST_UPDATED_BY
,
A.LAST_UPDATE_DATE
,
A.LAST_UPDATE_LOGIN
,
A.REQUEST_ID
,
A.PROGRAM_APPLICATION_ID
,
A.PROGRAM_ID
,
A.PROGRAM_LOGIN_ID
,
A.RECORD_TYPE_CODE
,
A.ALLOW_ROUNDING_OVERRIDE_FLAG
,
A.SPECIAL_INCLUSIVE_TAX_FLAG
,
A.ATTRIBUTE1
,
A.ATTRIBUTE2
,
A.ATTRIBUTE3
,
A.ATTRIBUTE4
,
A.ATTRIBUTE5
,
A.ATTRIBUTE6
,
A.ATTRIBUTE7
,
A.ATTRIBUTE8
,
A.ATTRIBUTE9
,
A.ATTRIBUTE10
,
A.ATTRIBUTE11
,
A.ATTRIBUTE12
,
A.ATTRIBUTE13
,
A.ATTRIBUTE14
,
A.ATTRIBUTE15
,
A.ATTRIBUTE_CATEGORY
,
TL.TAX_FULL_NAME
,
SD.FIRST_PTY_ORG_ID SDCO_ID
,
SD.EFFECTIVE_FROM SDEFF_FROM
,
SD.EFFECTIVE_TO SDEFF_TO
,
A.APPLICABLE_BY_DEFAULT_FLAG
,
A.LEGAL_REPORTING_STATUS_DEF_VAL
FROM ZX_TAXES_B A
, ZX_TAXES_TL TL
, ZX_SUBSCRIPTION_DETAILS SD
WHERE A.TAX_ID = TL.TAX_ID
AND TL.LANGUAGE = USERENV('LANG')
AND A.TAX_REGIME_CODE = SD.TAX_REGIME_CODE
AND A.CONTENT_OWNER_ID = SD.PARENT_FIRST_PTY_ORG_ID
AND (SD.VIEW_OPTIONS_CODE IN ('NONE'
, 'VFC')
OR
(SD.VIEW_OPTIONS_CODE = 'VFR'
AND NOT EXISTS ( SELECT 1 /*+ NO UNNEST*/
FROM ZX_TAXES_B B
WHERE B.TAX_REGIME_CODE = A.TAX_REGIME_CODE
AND B.TAX = A.TAX
AND B.CONTENT_OWNER_ID = SD.FIRST_PTY_ORG_ID
)
)
)
|
|
|