MSC_REGION_ZONES_TCA_V

Details

• Schema: FUSION

• Object owner: MSC

• Object type: VIEW

Columns

Name
PARENT_ZONE_ID CHILD_ID START_DATE END_DATE REGION_LEVEL

Query

SQL_Statement

SELECT HREL.SUBJECT_ID PARENT_ZONE_ID, HREL.OBJECT_ID CHILD_ID, HREL.START_DATE, HREL.END_DATE, DECODE(HGEO2.GEOGRAPHY_USE,'MASTER_REF',(SELECT REGION_LEVEL FROM MSC_REGIONS_TCA_V WHERE REGION_ID = HREL.OBJECT_ID) , NULL) REGION_LEVEL FROM HZ_GEOGRAPHIES HGEO1, HZ_GEOGRAPHIES HGEO2, HZ_RELATIONSHIPS HREL WHERE HGEO1.GEOGRAPHY_USE = HREL.RELATIONSHIP_TYPE AND HGEO1.GEOGRAPHY_ID = HREL.SUBJECT_ID AND HGEO2.GEOGRAPHY_ID = HREL.OBJECT_ID AND HGEO1.GEOGRAPHY_TYPE = HREL.SUBJECT_TYPE AND HGEO2.GEOGRAPHY_TYPE = HREL.OBJECT_TYPE AND HGEO1.GEOGRAPHY_USE IN ('SHIPPING','TM') AND HGEO2.GEOGRAPHY_USE IN ('MASTER_REF') AND HREL.SUBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES' AND HREL.OBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES' AND HREL.DIRECTIONAL_FLAG = 'F' UNION SELECT HREL.OBJECT_ID PARENT_ZONE_ID, HREL.SUBJECT_ID CHILD_ID, HREL.START_DATE, HREL.END_DATE, DECODE(HGEO1.GEOGRAPHY_USE,'MASTER_REF',(SELECT REGION_LEVEL FROM MSC_REGIONS_TCA_V WHERE REGION_ID = HREL.OBJECT_ID) , NULL) REGION_LEVEL FROM HZ_GEOGRAPHIES HGEO1, HZ_GEOGRAPHIES HGEO2, HZ_RELATIONSHIPS HREL WHERE HGEO1.GEOGRAPHY_USE = HREL.RELATIONSHIP_TYPE AND HGEO1.GEOGRAPHY_ID = HREL.SUBJECT_ID AND HGEO2.GEOGRAPHY_ID = HREL.OBJECT_ID AND HGEO1.GEOGRAPHY_TYPE = HREL.SUBJECT_TYPE AND HGEO2.GEOGRAPHY_TYPE = HREL.OBJECT_TYPE AND HGEO1.GEOGRAPHY_USE IN ('MASTER_REF') AND HGEO2.GEOGRAPHY_USE IN ('SHIPPING','TM') AND HREL.SUBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES' AND HREL.OBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES' AND HREL.DIRECTIONAL_FLAG = 'B'