[Home] [Help]
View: AR_LOCATION_VALUES
View Text
SELECT
GEO.GEOGRAPHY_ID LOCATION_SEGMENT_ID
,
GEO.CREATED_BY CREATED_BY
,
GEO.CREATION_DATE CREATION_DATE
,
GEO.LAST_UPDATED_BY LAST_UPDATED_BY
,
GEO.LAST_UPDATE_DATE LAST_UPDATE_DATE
,
ATTR_CHILD.ID_FLEX_NUM LOCATION_STRUCTURE_ID
,
GEO.GEOGRAPHY_TYPE LOCATION_SEGMENT_QUALIFIER
,
GEO.GEOGRAPHY_NAME LOCATION_SEGMENT_VALUE
,
GEO.GEOGRAPHY_NAME LOCATION_SEGMENT_DESCRIPTION
,
REL.OBJECT_ID PARENT_SEGMENT_ID
,
GEO.REQUEST_ID REQUEST_ID
,
GEO.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
,
GEO.PROGRAM_ID PROGRAM_ID
,
NULL PROGRAM_UPDATE_DATE
,
GEO.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
,
GEO.ATTRIBUTE1 ATTRIBUTE1
,
GEO.ATTRIBUTE2 ATTRIBUTE2
,
GEO.ATTRIBUTE3 ATTRIBUTE3
,
GEO.ATTRIBUTE4 ATTRIBUTE4
,
GEO.ATTRIBUTE5 ATTRIBUTE5
,
GEO.ATTRIBUTE6 ATTRIBUTE6
,
GEO.ATTRIBUTE7 ATTRIBUTE7
,
GEO.ATTRIBUTE8 ATTRIBUTE8
,
GEO.ATTRIBUTE9 ATTRIBUTE9
,
GEO.ATTRIBUTE10 ATTRIBUTE10
,
GEO.ATTRIBUTE11 ATTRIBUTE11
,
GEO.ATTRIBUTE12 ATTRIBUTE12
,
GEO.ATTRIBUTE13 ATTRIBUTE13
,
GEO.ATTRIBUTE14 ATTRIBUTE14
,
GEO.ATTRIBUTE15 ATTRIBUTE15
,
GEO.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
,
GEO.GEOGRAPHY_NAME LOCATION_SEGMENT_USER_VALUE
,
GEO.COUNTRY_CODE COUNTRY_CODE
FROM HZ_GEOGRAPHIES GEO
, HZ_RELATIONSHIPS REL
,
FND_SEGMENT_ATTRIBUTE_VALUES ATTR_CHILD
,
FND_SEGMENT_ATTRIBUTE_VALUES ATTR_PARENT
,
FND_ID_FLEX_SEGMENTS SEG_CHILD
,
FND_ID_FLEX_SEGMENTS SEG_PARENT
WHERE GEO.GEOGRAPHY_ID = REL.SUBJECT_ID
AND REL.RELATIONSHIP_CODE = 'CHILD_OF'
AND GEO.GEOGRAPHY_TYPE = REL.SUBJECT_TYPE
AND GEO.GEOGRAPHY_USE = 'MASTER_REF'
AND REL.RELATIONSHIP_TYPE = 'MASTER_REF'
AND ATTR_CHILD.APPLICATION_ID = 222
AND ATTR_CHILD.ID_FLEX_CODE = 'RLOC'
AND ATTR_CHILD.SEGMENT_ATTRIBUTE_TYPE = REL.SUBJECT_TYPE
AND ATTR_CHILD.ATTRIBUTE_VALUE = 'Y'
AND SEG_CHILD.APPLICATION_ID = 222
AND SEG_CHILD.ID_FLEX_CODE = 'RLOC'
AND SEG_CHILD.ID_FLEX_NUM = ATTR_CHILD.ID_FLEX_NUM
AND SEG_CHILD.APPLICATION_COLUMN_NAME = ATTR_CHILD.APPLICATION_COLUMN_NAME
AND ATTR_PARENT.APPLICATION_ID = 222
AND ATTR_PARENT.ID_FLEX_CODE = 'RLOC'
AND ATTR_PARENT.ID_FLEX_NUM = ATTR_CHILD.ID_FLEX_NUM
AND DECODE(SEG_CHILD.SEGMENT_NUM
, 1
, 'COUNTRY'
,
ATTR_PARENT.SEGMENT_ATTRIBUTE_TYPE) = REL.OBJECT_TYPE
AND DECODE(SEG_CHILD.SEGMENT_NUM
, 1
, GEO.GEOGRAPHY_TYPE
, REL.OBJECT_TYPE) =
ATTR_PARENT.SEGMENT_ATTRIBUTE_TYPE
AND ATTR_PARENT.ATTRIBUTE_VALUE = 'Y'
AND SEG_PARENT.APPLICATION_ID = 222
AND SEG_PARENT.ID_FLEX_CODE = 'RLOC'
AND SEG_PARENT.ID_FLEX_NUM = ATTR_PARENT.ID_FLEX_NUM
AND SEG_PARENT.APPLICATION_COLUMN_NAME = ATTR_PARENT.APPLICATION_COLUMN_NAME
AND SEG_PARENT.SEGMENT_NUM IN
(SELECT MAX(SEG_PARENT1.SEGMENT_NUM)
FROM FND_ID_FLEX_SEGMENTS SEG_PARENT1
WHERE SEG_PARENT1.APPLICATION_ID = 222
AND SEG_PARENT1.ID_FLEX_CODE = 'RLOC'
AND SEG_PARENT1.ID_FLEX_NUM = SEG_PARENT.ID_FLEX_NUM
AND SEG_PARENT1.APPLICATION_COLUMN_NAME =
ATTR_PARENT.APPLICATION_COLUMN_NAME
AND (
(SEG_PARENT1.SEGMENT_NUM < SEG_CHILD.SEGMENT_NUM
AND SEG_CHILD.SEGMENT_NUM <> 1)
OR
(SEG_PARENT1.SEGMENT_NUM = SEG_CHILD.SEGMENT_NUM
AND SEG_CHILD.SEGMENT_NUM = 1)
)
)
UNION ALL
SELECT
GEO.GEOGRAPHY_ID LOCATION_SEGMENT_ID
,
GEO.CREATED_BY CREATED_BY
,
GEO.CREATION_DATE CREATION_DATE
,
GEO.LAST_UPDATED_BY LAST_UPDATED_BY
,
GEO.LAST_UPDATE_DATE LAST_UPDATE_DATE
,
-99 LOCATION_STRUCTURE_ID
,
GEO.GEOGRAPHY_TYPE LOCATION_SEGMENT_QUALIFIER
,
GEO.GEOGRAPHY_NAME LOCATION_SEGMENT_VALUE
,
GEO.GEOGRAPHY_NAME LOCATION_SEGMENT_DESCRIPTION
,
REL.OBJECT_ID PARENT_SEGMENT_ID
,
GEO.REQUEST_ID REQUEST_ID
,
GEO.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
,
GEO.PROGRAM_ID PROGRAM_ID
,
NULL PROGRAM_UPDATE_DATE
,
GEO.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
,
GEO.ATTRIBUTE1 ATTRIBUTE1
,
GEO.ATTRIBUTE2 ATTRIBUTE2
,
GEO.ATTRIBUTE3 ATTRIBUTE3
,
GEO.ATTRIBUTE4 ATTRIBUTE4
,
GEO.ATTRIBUTE5 ATTRIBUTE5
,
GEO.ATTRIBUTE6 ATTRIBUTE6
,
GEO.ATTRIBUTE7 ATTRIBUTE7
,
GEO.ATTRIBUTE8 ATTRIBUTE8
,
GEO.ATTRIBUTE9 ATTRIBUTE9
,
GEO.ATTRIBUTE10 ATTRIBUTE10
,
GEO.ATTRIBUTE11 ATTRIBUTE11
,
GEO.ATTRIBUTE12 ATTRIBUTE12
,
GEO.ATTRIBUTE13 ATTRIBUTE13
,
GEO.ATTRIBUTE14 ATTRIBUTE14
,
GEO.ATTRIBUTE15 ATTRIBUTE15
,
GEO.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
,
GEO.GEOGRAPHY_NAME LOCATION_SEGMENT_USER_VALUE
,
GEO.COUNTRY_CODE COUNTRY_CODE
FROM HZ_GEOGRAPHIES GEO
, HZ_RELATIONSHIPS REL
WHERE GEO.GEOGRAPHY_ID = REL.SUBJECT_ID
AND REL.RELATIONSHIP_CODE = 'CHILD_OF'
AND GEO.GEOGRAPHY_TYPE = REL.SUBJECT_TYPE
AND GEO.GEOGRAPHY_USE = 'MASTER_REF'
AND REL.RELATIONSHIP_TYPE = 'MASTER_REF'