VIEW: APPS.ZX_TAX_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,
   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(lookup_type, 'ZX_OUTPUT_CLASSIFICATIONS',
                      DECODE(CREATED_BY,-1087,NVL(substrb(TAG,1,50),LOOKUP_CODE), 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 IN ('ZX_OUTPUT_CLASSIFICATIONS',
                               'ZX_INPUT_CLASSIFICATIONS',
                               'ZX_WEB_EXP_TAX_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 = Decode(rvl.tax_class(+),'INPUT',Nvl(rvl.source_id(+),rvl.tax_rate_id(+)),rvl.tax_rate_id(+))
  AND tcc.active_flag = 'Y'

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
, 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(LOOKUP_TYPE
, 'ZX_OUTPUT_CLASSIFICATIONS'
, DECODE(CREATED_BY
, -1087
, NVL(SUBSTRB(TAG
, 1
, 50)
, LOOKUP_CODE)
, 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 IN ('ZX_OUTPUT_CLASSIFICATIONS'
, 'ZX_INPUT_CLASSIFICATIONS'
, 'ZX_WEB_EXP_TAX_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 = DECODE(RVL.TAX_CLASS(+)
, 'INPUT'
, NVL(RVL.SOURCE_ID(+)
, RVL.TAX_RATE_ID(+))
, RVL.TAX_RATE_ID(+))
AND TCC.ACTIVE_FLAG = 'Y'