1. Tables and Views for Financials
  2. ZX_SCO_INPUT_RATES_V

ZX_SCO_INPUT_RATES_V

Details

  • Schema: FUSION

  • Object owner: FND

  • Object type: VIEW

Columns

Name

BU_ID

FIRST_PTY_ORG_ID

TAX_REGIME_CODE

TAX_REGIME_NAME

TAX

TAX_FULL_NAME

TAX_STATUS_CODE

TAX_STATUS_NAME

TAX_JURISDICTION_CODE

TAX_JURISDICTION_NAME

TAX_RATE_CODE

TAX_RATE_NAME

TAX_RATE

ACTIVE_FLAG

EFFECTIVE_FROM

EFFECTIVE_TO

REGIME_TYPE_FLAG

DISPLAY_SEQUENCE

Query

SQL_Statement

SELECT tcc.set_id bu_id,

sd.first_pty_org_id first_pty_org_id,

rt.tax_regime_code tax_regime_code,

rtl.tax_regime_name tax_regime_name,

rt.tax tax,

ttl.tax_full_name tax_full_name,

rt.tax_status_code tax_status_code,

stl.tax_status_name tax_status_name,

NULL tax_jurisdiction_code,

NULL tax_jurisdiction_name ,

rt.tax_rate_code tax_rate_code,

nvl(tl.tax_rate_name,rt.tax_rate_code) tax_rate_name,

decode(rt.rate_type_code,

'QUANTITY',rt.quantity_rate,

'PERCENTAGE',rt.percentage_rate,0) tax_rate,

rt.active_flag active_flag,

rt.effective_from effective_from,

rt.effective_to effective_to,

rg.REGIME_TYPE_FLAG,

tcc.DISPLAY_SEQUENCE

FROM zx_rates_b rt , FND_LOOKUP_VALUES_B tcc,

zx_party_tax_profile ptp,

zx_subscription_details sd, zx_rates_tl tl,

zx_regimes_b rg, zx_regimes_tl rtl,

zx_taxes_b tx, zx_taxes_tl ttl, zx_subscription_details sdtx,

zx_status_b st, zx_status_tl stl, zx_subscription_details sdst,

fnd_setid_assignments setid

,zx_subscription_options so

WHERE rt.tax_rate_code = tcc.lookup_code

AND tcc.LOOKUP_TYPE = 'ZX_INPUT_CLASSIFICATIONS'

AND tcc.enabled_flag = 'Y'

AND tcc.view_application_id = 2

AND tcc.set_id = ptp.party_id

AND ptp.party_tax_profile_id = sd.first_pty_org_id

AND rt.tax_regime_code = sd.tax_regime_code

AND rt.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 /*+ no unnest*/ 1 from zx_rates_b b

where b.tax_regime_code = rt.tax_regime_code

and b.tax = rt.tax

and b.tax_status_code = rt.tax_status_code

and b.tax_rate_code = rt.tax_rate_code

and b.content_owner_id = sd.first_pty_org_id

and b.rate_type_code = rt.rate_type_code

and (b.tax_class is NULL or b.tax_class =

'INPUT')

and b.active_flag = 'Y'

)

))

AND rt.rate_type_code <> 'RECOVERY'

AND (rt.tax_class IS NULL OR rt.tax_class = 'INPUT')

AND tl.tax_rate_id = rt.tax_rate_id

AND tl.LANGUAGE = USERENV('LANG')

AND rt.tax_regime_code = rg.tax_regime_code

AND rg.tax_regime_id = rtl.tax_regime_id

AND rtl.LANGUAGE = USERENV('LANG')

AND ptp.party_tax_profile_id = sdtx.first_pty_org_id

AND tx.tax_regime_code = sdtx.tax_regime_code

AND tx.content_owner_id = sdtx.parent_first_pty_org_id

AND (sdtx.view_options_code in ('NONE', 'VFC')

or

(sdtx.view_options_code = 'VFR'

and not exists ( select /*+ no unnest*/ 1 from zx_taxes_b b

where b.tax_regime_code = tx.tax_regime_code

and b.tax = tx.tax

and b.content_owner_id = sdtx.first_pty_org_id

and b.offset_tax_flag = 'N'

)

))

AND tx.tax_regime_code = rt.tax_regime_code

AND tx.tax = rt.tax

AND tx.offset_tax_flag <> 'Y'

AND tx.tax_id = ttl.tax_id

AND ttl.LANGUAGE = USERENV('LANG')

AND ptp.party_tax_profile_id = sdst.first_pty_org_id

and st.tax_regime_code = sdst.tax_regime_code

and st.content_owner_id = sdst.parent_first_pty_org_id

and (sdst.view_options_code in ('NONE', 'VFC')

or

(sdst.view_options_code = 'VFR'

and not exists ( select /*+ no unnest*/ 1 from zx_status_b b

where b.tax_regime_code = st.tax_regime_code

and b.tax = st.tax

and b.tax_status_code = st.tax_status_code

and b.content_owner_id = sdst.first_pty_org_id

)

))

AND st.tax_regime_code = rt.tax_regime_code

AND st.tax = rt.tax

AND st.tax_status_code = rt.tax_status_code

AND st.tax_status_id = stl.tax_status_id

AND stl.LANGUAGE = USERENV('LANG')

AND rt.tax_jurisdiction_code is NULL

AND tcc.set_id = setid.set_id

AND setid.reference_group_name = 'ZX_TAX_CLASSIFICATION_CODES'

AND setid.determinant_type = 'BU'

AND setid.determinant_value = ptp.party_id

AND sd.subscription_option_id = so.subscription_option_id

AND so.enabled_flag = 'Y'

UNION

SELECT tcc.set_id bu_id,

sd.first_pty_org_id first_pty_org_id,

rt.tax_regime_code tax_regime_code,

rtl.tax_regime_name tax_regime_name,

rt.tax tax,

ttl.tax_full_name tax_full_name,

rt.tax_status_code tax_status_code,

stl.tax_status_name tax_status_name,

rt.tax_jurisdiction_code tax_jurisdiction_code,

jtl.tax_jurisdiction_name tax_jurisdiction_name ,

rt.tax_rate_code tax_rate_code,

nvl(tl.tax_rate_name,rt.tax_rate_code) tax_rate_name,

decode(rt.rate_type_code,

'QUANTITY',rt.quantity_rate,

'PERCENTAGE',rt.percentage_rate,0) tax_rate,

rt.active_flag active_flag,

rt.effective_from effective_from,

rt.effective_to effective_to,

rg.REGIME_TYPE_FLAG,

tcc.DISPLAY_SEQUENCE

FROM zx_rates_b rt , FND_LOOKUP_VALUES_B tcc,

zx_party_tax_profile ptp,

zx_subscription_details sd, zx_rates_tl tl,

zx_regimes_b rg, zx_regimes_tl rtl,

zx_taxes_b tx, zx_taxes_tl ttl, zx_subscription_details sdtx,

zx_status_b st, zx_status_tl stl, zx_subscription_details sdst,

zx_jurisdictions_b jr, zx_jurisdictions_tl jtl,

fnd_setid_assignments setid

,zx_subscription_options so

WHERE rt.tax_rate_code = tcc.lookup_code

AND tcc.LOOKUP_TYPE = 'ZX_INPUT_CLASSIFICATIONS'

AND tcc.enabled_flag = 'Y'

AND tcc.view_application_id = 2

AND tcc.set_id = ptp.party_id

AND ptp.party_tax_profile_id = sd.first_pty_org_id

AND rt.tax_regime_code = sd.tax_regime_code

AND rt.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 /*+ no unnest*/ 1 from zx_rates_b b

where b.tax_regime_code = rt.tax_regime_code

and b.tax = rt.tax

and b.tax_status_code = rt.tax_status_code

and b.tax_rate_code = rt.tax_rate_code

and b.content_owner_id = sd.first_pty_org_id

and b.rate_type_code = rt.rate_type_code

and (b.tax_class is NULL or b.tax_class =

'INPUT')

and b.active_flag = 'Y'

)

))

AND rt.rate_type_code <> 'RECOVERY'

AND (rt.tax_class IS NULL OR rt.tax_class = 'INPUT')

AND tl.tax_rate_id = rt.tax_rate_id

AND tl.LANGUAGE = USERENV('LANG')

AND rt.tax_regime_code = rg.tax_regime_code

AND rg.tax_regime_id = rtl.tax_regime_id

AND rtl.LANGUAGE = USERENV('LANG')

AND ptp.party_tax_profile_id = sdtx.first_pty_org_id

AND tx.tax_regime_code = sdtx.tax_regime_code

AND tx.content_owner_id = sdtx.parent_first_pty_org_id

AND (sdtx.view_options_code in ('NONE', 'VFC')

or

(sdtx.view_options_code = 'VFR'

and not exists ( select /*+ no unnest*/ 1 from zx_taxes_b b

where b.tax_regime_code = tx.tax_regime_code

and b.tax = tx.tax

and b.content_owner_id = sdtx.first_pty_org_id

and b.offset_tax_flag = 'N'

)

))

AND tx.tax_regime_code = rt.tax_regime_code

AND tx.tax = rt.tax

AND tx.offset_tax_flag <> 'Y'

AND tx.tax_id = ttl.tax_id

AND ttl.LANGUAGE = USERENV('LANG')

AND ptp.party_tax_profile_id = sdst.first_pty_org_id

and st.tax_regime_code = sdst.tax_regime_code

and st.content_owner_id = sdst.parent_first_pty_org_id

and (sdst.view_options_code in ('NONE', 'VFC')

or

(sdst.view_options_code = 'VFR'

and not exists ( select /*+ no unnest*/ 1 from zx_status_b b

where b.tax_regime_code = st.tax_regime_code

and b.tax = st.tax

and b.tax_status_code = st.tax_status_code

and b.content_owner_id = sdst.first_pty_org_id

)

))

AND st.tax_regime_code = rt.tax_regime_code

AND st.tax = rt.tax

AND st.tax_status_code = rt.tax_status_code

AND st.tax_status_id = stl.tax_status_id

AND stl.LANGUAGE = USERENV('LANG')

AND rt.tax_jurisdiction_code is not NULL

AND jr.tax_regime_code = rt.tax_regime_code

AND jr.tax = rt.tax

AND jr.tax_jurisdiction_code = rt.tax_jurisdiction_code

AND jr.tax_jurisdiction_id = jtl.tax_jurisdiction_id

AND jtl.LANGUAGE = USERENV('LANG')

AND tcc.set_id = setid.set_id

AND setid.reference_group_name = 'ZX_TAX_CLASSIFICATION_CODES'

AND setid.determinant_type = 'BU'

AND setid.determinant_value = ptp.party_id

AND sd.subscription_option_id = so.subscription_option_id

AND so.enabled_flag = 'Y'