SELECT
tcc.org_id ORG_ID,
COALESCE(rvl.tax_rate_name, lkp.meaning, lkp.lookup_code) MEANING,
COALESCE(rvl.description, rvl.tax_rate_name, lkp.description, lkp.meaning, lkp.lookup_code) DESCRIPTION,
tcc.tax_classification_code LOOKUP_CODE,
lkp.lookup_type LOOKUP_TYPE,
lkp.leaf_node LEAF_NODE,
lkp.enabled_flag ENABLED_FLAG,
nvl(tcc.effective_from, lkp.start_date_active) START_DATE_ACTIVE,
COALESCE(Least(tcc.effective_to,lkp.end_date_active), tcc.effective_to, lkp.end_date_active) END_DATE_ACTIVE,
tcc.tax_type TAX_TYPE,
tcc.tax_class TAX_CLASS
FROM
(
select
DECODE(CREATED_BY,-1087,NVL(substrb(TAG,1,50),LOOKUP_CODE),LOOKUP_CODE) lookup_code,
meaning,
description,
enabled_flag,
start_date_active,
end_date_active,
lookup_type,
leaf_node
from
fnd_lookup_values
where
LANGUAGE = userenv('LANG')
and lookup_type = 'ZX_OUTPUT_CLASSIFICATIONS'
and security_group_id = 0
and view_application_id = 0
) lkp,
zx_id_tcc_mapping_all tcc,
zx_rates_vl rvl
WHERE
tcc.tax_classification_code = lkp.lookup_code
AND tcc.tax_rate_code_id = rvl.tax_rate_id(+)
AND tcc.active_flag = 'Y'
AND (tcc.tax_class = 'OUTPUT' or tcc.tax_class is NULL)
SELECT
TCC.ORG_ID ORG_ID
,
COALESCE(RVL.TAX_RATE_NAME
, LKP.MEANING
, LKP.LOOKUP_CODE) MEANING
,
COALESCE(RVL.DESCRIPTION
, RVL.TAX_RATE_NAME
, LKP.DESCRIPTION
, LKP.MEANING
, LKP.LOOKUP_CODE) DESCRIPTION
,
TCC.TAX_CLASSIFICATION_CODE LOOKUP_CODE
,
LKP.LOOKUP_TYPE LOOKUP_TYPE
,
LKP.LEAF_NODE LEAF_NODE
,
LKP.ENABLED_FLAG ENABLED_FLAG
,
NVL(TCC.EFFECTIVE_FROM
, LKP.START_DATE_ACTIVE) START_DATE_ACTIVE
,
COALESCE(LEAST(TCC.EFFECTIVE_TO
, LKP.END_DATE_ACTIVE)
, TCC.EFFECTIVE_TO
, LKP.END_DATE_ACTIVE) END_DATE_ACTIVE
,
TCC.TAX_TYPE TAX_TYPE
,
TCC.TAX_CLASS TAX_CLASS
FROM
(
SELECT
DECODE(CREATED_BY
, -1087
, NVL(SUBSTRB(TAG
, 1
, 50)
, LOOKUP_CODE)
, LOOKUP_CODE) LOOKUP_CODE
,
MEANING
,
DESCRIPTION
,
ENABLED_FLAG
,
START_DATE_ACTIVE
,
END_DATE_ACTIVE
,
LOOKUP_TYPE
,
LEAF_NODE
FROM
FND_LOOKUP_VALUES
WHERE
LANGUAGE = USERENV('LANG')
AND LOOKUP_TYPE = 'ZX_OUTPUT_CLASSIFICATIONS'
AND SECURITY_GROUP_ID = 0
AND VIEW_APPLICATION_ID = 0
) LKP
,
ZX_ID_TCC_MAPPING_ALL TCC
,
ZX_RATES_VL RVL
WHERE
TCC.TAX_CLASSIFICATION_CODE = LKP.LOOKUP_CODE
AND TCC.TAX_RATE_CODE_ID = RVL.TAX_RATE_ID(+)
AND TCC.ACTIVE_FLAG = 'Y'
AND (TCC.TAX_CLASS = 'OUTPUT' OR TCC.TAX_CLASS IS NULL)
|
|
|