VIEW: APPS.ZX_WEBEXP_CLASSIFICATIONS_V

Source

View Text - Preformatted

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' 

View Text - HTML Formatted

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'