VIEW: APPS.AMS_ACT_GEO_AREAS_V

Source

View Text - Preformatted

SELECT AGEO.ACTIVITY_GEO_AREA_ID, AGEO.OBJECT_VERSION_NUMBER, AGEO.ARC_ACT_GEO_AREA_USED_BY, AGEO.ACT_GEO_AREA_USED_BY_ID, AGEO.GEO_AREA_TYPE_CODE, LTYP.LOCATION_TYPE_NAME, AGEO.GEO_HIERARCHY_ID, DECODE(AGEO.GEO_AREA_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) GEO_AREA_NAME, DECODE(AGEO.GEO_AREA_TYPE_CODE, 'AREA2', LH.AREA1_NAME, 'CREGION', LH.COUNTRY_NAME, 'STATE', LH.COUNTRY_REGION_NAME||','||LH.COUNTRY_NAME, 'SREGION', LH.STATE_NAME||','||LH.COUNTRY_REGION_NAME||','||LH.COUNTRY_NAME, 'CITY', LH.CITY_NAME||','||LH.STATE_NAME||','||LH.COUNTRY_REGION_NAME||','||LH.COUNTRY_NAME, 'POSTAL_CODE', LH.CITY_NAME||','||LH.STATE_NAME||','||LH.COUNTRY_REGION_NAME||','||LH.COUNTRY_NAME) GEO_LOCATION FROM AMS_ACT_GEO_AREAS AGEO, JTF_LOC_TYPES_VL LTYP, JTF_LOC_HIERARCHIES_VL LH WHERE AGEO.GEO_AREA_TYPE_CODE = LTYP.LOCATION_TYPE_CODE and AGEO.GEO_HIERARCHY_ID = LH.LOCATION_HIERARCHY_ID

View Text - HTML Formatted

SELECT AGEO.ACTIVITY_GEO_AREA_ID
, AGEO.OBJECT_VERSION_NUMBER
, AGEO.ARC_ACT_GEO_AREA_USED_BY
, AGEO.ACT_GEO_AREA_USED_BY_ID
, AGEO.GEO_AREA_TYPE_CODE
, LTYP.LOCATION_TYPE_NAME
, AGEO.GEO_HIERARCHY_ID
, DECODE(AGEO.GEO_AREA_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) GEO_AREA_NAME
, DECODE(AGEO.GEO_AREA_TYPE_CODE
, 'AREA2'
, LH.AREA1_NAME
, 'CREGION'
, LH.COUNTRY_NAME
, 'STATE'
, LH.COUNTRY_REGION_NAME||'
, '||LH.COUNTRY_NAME
, 'SREGION'
, LH.STATE_NAME||'
, '||LH.COUNTRY_REGION_NAME||'
, '||LH.COUNTRY_NAME
, 'CITY'
, LH.CITY_NAME||'
, '||LH.STATE_NAME||'
, '||LH.COUNTRY_REGION_NAME||'
, '||LH.COUNTRY_NAME
, 'POSTAL_CODE'
, LH.CITY_NAME||'
, '||LH.STATE_NAME||'
, '||LH.COUNTRY_REGION_NAME||'
, '||LH.COUNTRY_NAME) GEO_LOCATION
FROM AMS_ACT_GEO_AREAS AGEO
, JTF_LOC_TYPES_VL LTYP
, JTF_LOC_HIERARCHIES_VL LH
WHERE AGEO.GEO_AREA_TYPE_CODE = LTYP.LOCATION_TYPE_CODE
AND AGEO.GEO_HIERARCHY_ID = LH.LOCATION_HIERARCHY_ID