[Home] [Help]
View: ZX_WEBEXP_CLASSIFICATIONS_V
View Text
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
,
LKP.LOOKUP_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
FND_LOOKUP_VALUES LKP
,
ZX_ID_TCC_MAPPING_ALL TCC
,
ZX_RATES_VL RVL
WHERE
TCC.TAX_CLASSIFICATION_CODE = LKP.LOOKUP_CODE
AND LKP.LANGUAGE = USERENV('LANG')
AND TCC.TAX_RATE_CODE_ID = NVL(RVL.SOURCE_ID
, RVL.TAX_RATE_ID)
AND TCC.TAX_CLASSIFICATION_CODE = RVL.TAX_RATE_CODE
AND LKP.LOOKUP_TYPE = 'ZX_WEB_EXP_TAX_CLASSIFICATIONS'
AND LKP.VIEW_APPLICATION_ID = 0
AND LKP.SECURITY_GROUP_ID = 0
AND NVL(TCC.TAX_TYPE
, 'XXX') <> 'TAX_GROUP'
AND TCC.ACTIVE_FLAG = 'Y'
AND NVL(TCC.TAX_CLASS
, 'INPUT') = 'INPUT'
AND NVL(RVL.TAX_CLASS
, 'INPUT') = 'INPUT'
UNION ALL
SELECT
TCC.ORG_ID ORG_ID
,
NVL(LKP.MEANING
, LKP.LOOKUP_CODE) MEANING
,
COALESCE(LKP.DESCRIPTION
, LKP.MEANING
, LKP.LOOKUP_CODE) DESCRIPTION
,
LKP.LOOKUP_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
FND_LOOKUP_VALUES LKP
,
ZX_ID_TCC_MAPPING_ALL TCC
WHERE
TCC.TAX_CLASSIFICATION_CODE = LKP.LOOKUP_CODE
AND LKP.LANGUAGE = USERENV('LANG')
AND LKP.LOOKUP_TYPE = 'ZX_WEB_EXP_TAX_CLASSIFICATIONS'
AND LKP.VIEW_APPLICATION_ID = 0
AND LKP.SECURITY_GROUP_ID = 0
AND TCC.ACTIVE_FLAG = 'Y'
AND TCC.TAX_TYPE = 'TAX_GROUP'
AND NVL(TCC.TAX_CLASS
, 'INPUT') = 'INPUT'