1. Tables and Views for SCM
  2. MSC_ATP_GEOGRAPHY_V

MSC_ATP_GEOGRAPHY_V

Details

  • Schema: FUSION

  • Object owner: MSC

  • Object type: VIEW

Columns

Name

GEOGRAPHY_ID

GEOGRAPHY_LEVEL

Query

SQL_Statement

SELECT geo.geography_id,

(10* (10 - SUBSTR(nvl(LVL.geography_element_column,'GEOGRAPHY_ELEMENT1'),18,18))) geography_level

FROM HZ_GEOGRAPHIES geo,

HZ_GEO_STRUCTURE_LEVELS LVL

WHERE geo.GEOGRAPHY_USE = 'MASTER_REF'

AND geo.COUNTRY_CODE = LVL.COUNTRY_CODE(+)

AND geo.GEOGRAPHY_TYPE = LVL.GEOGRAPHY_TYPE(+)

UNION ALL

SELECT geography_id,

91 geography_level

FROM HZ_GEOGRAPHIES

WHERE GEOGRAPHY_USE ='SHIPPING'

AND COUNTRY_CODE = 'WD'

UNION ALL

SELECT ZON.geography_id,

81 geography_level

FROM HZ_GEOGRAPHIES ZON ,

HZ_GEOGRAPHY_TYPES_VL ZONTYPE,

HZ_GEOGRAPHIES LIMITZON

WHERE ZON.GEOGRAPHY_USE ='SHIPPING'

AND ZON.COUNTRY_CODE != 'WD'

AND ZON.GEOGRAPHY_TYPE = ZONTYPE.GEOGRAPHY_TYPE

AND ZONTYPE.LIMITED_BY_GEOGRAPHY_ID = LIMITZON.GEOGRAPHY_ID

AND LIMITZON.GEOGRAPHY_USE = 'MASTER_REF'

AND LIMITZON.GEOGRAPHY_TYPE = 'COUNTRY'

UNION ALL

SELECT ZON.geography_id ,

(10* ((10 - SUBSTR(LVL.GEOGRAPHY_ELEMENT_COLUMN,18,18)) -1) + 1) geography_level

FROM HZ_GEOGRAPHIES ZON ,

HZ_GEOGRAPHY_TYPES_VL ZONTYPE,

HZ_GEOGRAPHIES LIMITZON ,

HZ_GEO_STRUCTURE_LEVELS LVL

WHERE ZON.GEOGRAPHY_USE ='SHIPPING'

AND ZON.COUNTRY_CODE !='WD'

AND ZON.GEOGRAPHY_TYPE = ZONTYPE.GEOGRAPHY_TYPE

AND ZONTYPE.LIMITED_BY_GEOGRAPHY_ID = LIMITZON.GEOGRAPHY_ID

AND LIMITZON.GEOGRAPHY_USE = 'MASTER_REF'

AND LIMITZON.GEOGRAPHY_TYPE != 'COUNTRY'

AND LIMITZON.COUNTRY_CODE = LVL.COUNTRY_CODE

AND LIMITZON.GEOGRAPHY_TYPE = LVL.GEOGRAPHY_TYPE