select
tax_lines.tax_line_id,
tax_lines.internal_organization_id,
decode(jur_accounts1.tax_account_entity_id,NULL,JUR_ACCOUNTS.TAX_ACCOUNT_CCID,JUR_ACCOUNTS1.TAX_ACCOUNT_CCID),
decode(jur_accounts1.tax_account_entity_id,NULL,JUR_ACCOUNTS.INTERIM_TAX_CCID,JUR_ACCOUNTS1.INTERIM_TAX_CCID),
decode(jur_accounts1.tax_account_entity_id,NULL,JUR_ACCOUNTS.NON_REC_ACCOUNT_CCID,JUR_ACCOUNTS1.NON_REC_ACCOUNT_CCID),
decode(jur_accounts1.tax_account_entity_id,NULL,JUR_ACCOUNTS.ADJ_CCID,JUR_ACCOUNTS1.ADJ_CCID),
decode(jur_accounts1.tax_account_entity_id,NULL,JUR_ACCOUNTS.EDISC_CCID,JUR_ACCOUNTS1.EDISC_CCID),
decode(jur_accounts1.tax_account_entity_id,NULL,JUR_ACCOUNTS.UNEDISC_CCID ,JUR_ACCOUNTS1.UNEDISC_CCID),
decode(jur_accounts1.tax_account_entity_id,NULL,JUR_ACCOUNTS.FINCHRG_CCID ,JUR_ACCOUNTS1.FINCHRG_CCID),
decode(jur_accounts1.tax_account_entity_id,NULL,JUR_ACCOUNTS.ADJ_NON_REC_TAX_CCID,JUR_ACCOUNTS1.ADJ_NON_REC_TAX_CCID),
decode(jur_accounts1.tax_account_entity_id,NULL,JUR_ACCOUNTS.EDISC_NON_REC_TAX_CCID,JUR_ACCOUNTS1.EDISC_NON_REC_TAX_CCID),
decode(jur_accounts1.tax_account_entity_id,NULL,JUR_ACCOUNTS.UNEDISC_NON_REC_TAX_CCID,JUR_ACCOUNTS1.UNEDISC_NON_REC_TAX_CCID),
decode(jur_accounts1.tax_account_entity_id,NULL,JUR_ACCOUNTS.FINCHRG_NON_REC_TAX_CCID,JUR_ACCOUNTS1.FINCHRG_NON_REC_TAX_CCID),
decode(rate_accounts.tax_account_entity_id,NULL,TAX_ACCOUNTS.TAX_ACCOUNT_CCID,rate_accounts.TAX_ACCOUNT_CCID),
decode(rate_accounts.tax_account_entity_id,NULL,TAX_ACCOUNTS.INTERIM_TAX_CCID,rate_ACCOUNTS.INTERIM_TAX_CCID),
decode(rate_accounts.tax_account_entity_id,NULL,TAX_ACCOUNTS.NON_REC_ACCOUNT_CCID,rate_ACCOUNTS.NON_REC_ACCOUNT_CCID),
decode(rate_accounts.tax_account_entity_id,NULL,TAX_ACCOUNTS.ADJ_CCID,rate_ACCOUNTS.ADJ_CCID),
decode(rate_accounts.tax_account_entity_id,NULL,TAX_ACCOUNTS.EDISC_CCID,rate_ACCOUNTS.EDISC_CCID),
decode(rate_accounts.tax_account_entity_id,NULL,TAX_ACCOUNTS.UNEDISC_CCID,rate_ACCOUNTS.UNEDISC_CCID),
decode(rate_accounts.tax_account_entity_id,NULL,TAX_ACCOUNTS.FINCHRG_CCID,rate_ACCOUNTS.FINCHRG_CCID),
decode(rate_accounts.tax_account_entity_id,NULL,TAX_ACCOUNTS.ADJ_NON_REC_TAX_CCID,rate_ACCOUNTS.ADJ_NON_REC_TAX_CCID),
decode(rate_accounts.tax_account_entity_id,NULL,TAX_ACCOUNTS.EDISC_NON_REC_TAX_CCID,rate_ACCOUNTS.EDISC_NON_REC_TAX_CCID),
decode(rate_accounts.tax_account_entity_id,NULL,TAX_ACCOUNTS.UNEDISC_NON_REC_TAX_CCID,rate_ACCOUNTS.UNEDISC_NON_REC_TAX_CCID),
decode(rate_accounts.tax_account_entity_id,NULL,TAX_ACCOUNTS.FINCHRG_NON_REC_TAX_CCID,rate_ACCOUNTS.FINCHRG_NON_REC_TAX_CCID)
from zx_lines tax_lines,
zx_taxes_b taxes,
zx_Accounts tax_accounts,
zx_rates_b rates,
zx_accounts rate_accounts,
zx_jurisdictions_b jur,
zx_accounts jur_accounts,
zx_jurisdictions_b jur1,
zx_accounts jur_accounts1
where
tax_lines.tax_rate_id = rates.tax_rate_id
and rates.tax_rate_id = rate_Accounts.tax_account_entity_id(+)
and rate_accounts.tax_account_entity_code(+) = 'RATES'
-- and rate_Accounts.internal_organization_id(+) = tax_lines.internal_organization_id
AND tax_lines.tax_id = taxes.tax_id
AND taxes.tax_id(+) = tax_Accounts.tax_account_entity_id
AND tax_accounts.tax_account_entity_code(+) = 'TAXES'
AND tax_lines.tax_jurisdiction_id(+) = jur.tax_jurisdiction_id
AND jur_accounts.tax_account_entity_id (+) =
nvl(jur.TAX_ACCT_SRC_JURISDICT_ID, jur.tax_jurisdiction_id)
AND jur_accounts.tax_account_entity_code (+) = 'JURISDICTION'
and rates.tax_regime_code(+) = jur1.tax_regime_code
and rates.tax(+) = jur1.tax
and rates.tax_jurisdiction_code(+) = jur1.tax_jurisdiction_code
and jur_accounts1.tax_account_entity_code(+) = 'JURISDICTION'
and jur_accounts1.tax_account_entity_id (+) =
nvl(jur1.TAX_ACCT_SRC_JURISDICT_ID, jur1.tax_jurisdiction_id)
SELECT
TAX_LINES.TAX_LINE_ID
,
TAX_LINES.INTERNAL_ORGANIZATION_ID
,
DECODE(JUR_ACCOUNTS1.TAX_ACCOUNT_ENTITY_ID
, NULL
, JUR_ACCOUNTS.TAX_ACCOUNT_CCID
, JUR_ACCOUNTS1.TAX_ACCOUNT_CCID)
,
DECODE(JUR_ACCOUNTS1.TAX_ACCOUNT_ENTITY_ID
, NULL
, JUR_ACCOUNTS.INTERIM_TAX_CCID
, JUR_ACCOUNTS1.INTERIM_TAX_CCID)
,
DECODE(JUR_ACCOUNTS1.TAX_ACCOUNT_ENTITY_ID
, NULL
, JUR_ACCOUNTS.NON_REC_ACCOUNT_CCID
, JUR_ACCOUNTS1.NON_REC_ACCOUNT_CCID)
,
DECODE(JUR_ACCOUNTS1.TAX_ACCOUNT_ENTITY_ID
, NULL
, JUR_ACCOUNTS.ADJ_CCID
, JUR_ACCOUNTS1.ADJ_CCID)
,
DECODE(JUR_ACCOUNTS1.TAX_ACCOUNT_ENTITY_ID
, NULL
, JUR_ACCOUNTS.EDISC_CCID
, JUR_ACCOUNTS1.EDISC_CCID)
,
DECODE(JUR_ACCOUNTS1.TAX_ACCOUNT_ENTITY_ID
, NULL
, JUR_ACCOUNTS.UNEDISC_CCID
, JUR_ACCOUNTS1.UNEDISC_CCID)
,
DECODE(JUR_ACCOUNTS1.TAX_ACCOUNT_ENTITY_ID
, NULL
, JUR_ACCOUNTS.FINCHRG_CCID
, JUR_ACCOUNTS1.FINCHRG_CCID)
,
DECODE(JUR_ACCOUNTS1.TAX_ACCOUNT_ENTITY_ID
, NULL
, JUR_ACCOUNTS.ADJ_NON_REC_TAX_CCID
, JUR_ACCOUNTS1.ADJ_NON_REC_TAX_CCID)
,
DECODE(JUR_ACCOUNTS1.TAX_ACCOUNT_ENTITY_ID
, NULL
, JUR_ACCOUNTS.EDISC_NON_REC_TAX_CCID
, JUR_ACCOUNTS1.EDISC_NON_REC_TAX_CCID)
,
DECODE(JUR_ACCOUNTS1.TAX_ACCOUNT_ENTITY_ID
, NULL
, JUR_ACCOUNTS.UNEDISC_NON_REC_TAX_CCID
, JUR_ACCOUNTS1.UNEDISC_NON_REC_TAX_CCID)
,
DECODE(JUR_ACCOUNTS1.TAX_ACCOUNT_ENTITY_ID
, NULL
, JUR_ACCOUNTS.FINCHRG_NON_REC_TAX_CCID
, JUR_ACCOUNTS1.FINCHRG_NON_REC_TAX_CCID)
,
DECODE(RATE_ACCOUNTS.TAX_ACCOUNT_ENTITY_ID
, NULL
, TAX_ACCOUNTS.TAX_ACCOUNT_CCID
, RATE_ACCOUNTS.TAX_ACCOUNT_CCID)
,
DECODE(RATE_ACCOUNTS.TAX_ACCOUNT_ENTITY_ID
, NULL
, TAX_ACCOUNTS.INTERIM_TAX_CCID
, RATE_ACCOUNTS.INTERIM_TAX_CCID)
,
DECODE(RATE_ACCOUNTS.TAX_ACCOUNT_ENTITY_ID
, NULL
, TAX_ACCOUNTS.NON_REC_ACCOUNT_CCID
, RATE_ACCOUNTS.NON_REC_ACCOUNT_CCID)
,
DECODE(RATE_ACCOUNTS.TAX_ACCOUNT_ENTITY_ID
, NULL
, TAX_ACCOUNTS.ADJ_CCID
, RATE_ACCOUNTS.ADJ_CCID)
,
DECODE(RATE_ACCOUNTS.TAX_ACCOUNT_ENTITY_ID
, NULL
, TAX_ACCOUNTS.EDISC_CCID
, RATE_ACCOUNTS.EDISC_CCID)
,
DECODE(RATE_ACCOUNTS.TAX_ACCOUNT_ENTITY_ID
, NULL
, TAX_ACCOUNTS.UNEDISC_CCID
, RATE_ACCOUNTS.UNEDISC_CCID)
,
DECODE(RATE_ACCOUNTS.TAX_ACCOUNT_ENTITY_ID
, NULL
, TAX_ACCOUNTS.FINCHRG_CCID
, RATE_ACCOUNTS.FINCHRG_CCID)
,
DECODE(RATE_ACCOUNTS.TAX_ACCOUNT_ENTITY_ID
, NULL
, TAX_ACCOUNTS.ADJ_NON_REC_TAX_CCID
, RATE_ACCOUNTS.ADJ_NON_REC_TAX_CCID)
,
DECODE(RATE_ACCOUNTS.TAX_ACCOUNT_ENTITY_ID
, NULL
, TAX_ACCOUNTS.EDISC_NON_REC_TAX_CCID
, RATE_ACCOUNTS.EDISC_NON_REC_TAX_CCID)
,
DECODE(RATE_ACCOUNTS.TAX_ACCOUNT_ENTITY_ID
, NULL
, TAX_ACCOUNTS.UNEDISC_NON_REC_TAX_CCID
, RATE_ACCOUNTS.UNEDISC_NON_REC_TAX_CCID)
,
DECODE(RATE_ACCOUNTS.TAX_ACCOUNT_ENTITY_ID
, NULL
, TAX_ACCOUNTS.FINCHRG_NON_REC_TAX_CCID
, RATE_ACCOUNTS.FINCHRG_NON_REC_TAX_CCID)
FROM ZX_LINES TAX_LINES
,
ZX_TAXES_B TAXES
,
ZX_ACCOUNTS TAX_ACCOUNTS
,
ZX_RATES_B RATES
,
ZX_ACCOUNTS RATE_ACCOUNTS
,
ZX_JURISDICTIONS_B JUR
,
ZX_ACCOUNTS JUR_ACCOUNTS
,
ZX_JURISDICTIONS_B JUR1
,
ZX_ACCOUNTS JUR_ACCOUNTS1
WHERE
TAX_LINES.TAX_RATE_ID = RATES.TAX_RATE_ID
AND RATES.TAX_RATE_ID = RATE_ACCOUNTS.TAX_ACCOUNT_ENTITY_ID(+)
AND RATE_ACCOUNTS.TAX_ACCOUNT_ENTITY_CODE(+) = 'RATES'
--
AND RATE_ACCOUNTS.INTERNAL_ORGANIZATION_ID(+) = TAX_LINES.INTERNAL_ORGANIZATION_ID
AND TAX_LINES.TAX_ID = TAXES.TAX_ID
AND TAXES.TAX_ID(+) = TAX_ACCOUNTS.TAX_ACCOUNT_ENTITY_ID
AND TAX_ACCOUNTS.TAX_ACCOUNT_ENTITY_CODE(+) = 'TAXES'
AND TAX_LINES.TAX_JURISDICTION_ID(+) = JUR.TAX_JURISDICTION_ID
AND JUR_ACCOUNTS.TAX_ACCOUNT_ENTITY_ID (+) =
NVL(JUR.TAX_ACCT_SRC_JURISDICT_ID
, JUR.TAX_JURISDICTION_ID)
AND JUR_ACCOUNTS.TAX_ACCOUNT_ENTITY_CODE (+) = 'JURISDICTION'
AND RATES.TAX_REGIME_CODE(+) = JUR1.TAX_REGIME_CODE
AND RATES.TAX(+) = JUR1.TAX
AND RATES.TAX_JURISDICTION_CODE(+) = JUR1.TAX_JURISDICTION_CODE
AND JUR_ACCOUNTS1.TAX_ACCOUNT_ENTITY_CODE(+) = 'JURISDICTION'
AND JUR_ACCOUNTS1.TAX_ACCOUNT_ENTITY_ID (+) =
NVL(JUR1.TAX_ACCT_SRC_JURISDICT_ID
, JUR1.TAX_JURISDICTION_ID)
|
|
|