[Home] [Help]
View: AMS_GEOAREA_SCR_V
Product: | AMS - Marketing |
Description: | This view returns geographic area details such as name, location and code. |
Implementation/DBA Data: |
APPS.AMS_GEOAREA_SCR_V
|
View Text
SELECT LOCATION_HIERARCHY_ID
, LOCATION_TYPE_CODE
, DECODE(LOCATION_TYPE_CODE
, 'AREA1'
, LH.AREA1_NAME
, 'AREA2'
, LH.AREA2_NAME
, 'COUNTRY'
, LH.COUNTRY_NAME
, 'CREGION'
, LH.COUNTRY_REGION_NAME
, 'STATE'
, LH.STATE_NAME
, 'SREGION'
, LH.STATE_REGION_NAME
, 'CITY'
, LH.CITY_NAME
, 'POSTAL_CODE'
, LH.POSTAL_CODE_START||'-'||LH.POSTAL_CODE_END) LOCATION_NAME
, DECODE(LOCATION_TYPE_CODE
, 'AREA2'
, LH.AREA1_NAME
, 'CREGION'
, LH.COUNTRY_NAME
, 'STATE'
, DECODE(LH.COUNTRY_REGION_NAME
, ''
, ''
, LH.COUNTRY_REGION_NAME||'
, ')||LH.COUNTRY_NAME
, 'SREGION'
, DECODE(LH.STATE_NAME
, ''
, ''
, LH.STATE_NAME||'
, ')|| DECODE(LH.COUNTRY_REGION_NAME
, ''
, ''
, LH.COUNTRY_REGION_NAME||'
, ')||LH.COUNTRY_NAME
, 'CITY'
, DECODE(LH.CITY_NAME
, ''
, ''
, LH.CITY_NAME||'
, ')|| DECODE(LH.STATE_NAME
, ''
, ''
, LH.STATE_NAME||'
, ')|| DECODE(LH.COUNTRY_REGION_NAME
, ''
, ''
, LH.COUNTRY_REGION_NAME||'
, ')||LH.COUNTRY_NAME
, 'POSTAL_CODE'
, DECODE(LH.CITY_NAME
, ''
, ''
, LH.CITY_NAME||'
, ')|| DECODE(LH.STATE_NAME
, ''
, ''
, LH.STATE_NAME||'
, ')|| DECODE(LH.COUNTRY_REGION_NAME
, ''
, ''
, LH.COUNTRY_REGION_NAME||'
, ')|| DECODE(LH.COUNTRY_NAME
, ''
, ''
, LH.COUNTRY_NAME||'
, ')|| LH.POSTAL_CODE_START ) LOCATION
FROM JTF_LOC_HIERARCHIES_VL LH
Columns
Name |
LOCATION_HIERARCHY_ID |
LOCATION_TYPE_CODE |
LOCATION_NAME |
LOCATION |