VIEW: APPS.OE_INLINE_TWO_LEVEL_V

Source

View Text - Preformatted

SELECT CHILD1.LOCATION_STRUCTURE_ID LOCATION_STRUCTURE_ID , CHILD1.LOCATION_SEGMENT_ID CHILD_ID , CHILD1.LOCATION_SEGMENT_VALUE CHILD_VALUE , CHILD1.LOCATION_SEGMENT_USER_VALUE CHILD_USER_VALUE , CHILD1.LOCATION_SEGMENT_QUALIFIER CHILD_QUALIFIER , CHILD1.COUNTRY_CODE CHILD_COUNTRY_CODE , PARENT1.LOCATION_SEGMENT_ID PARENT_ID , PARENT1.LOCATION_SEGMENT_VALUE PARENT_VALUE , PARENT1.LOCATION_SEGMENT_USER_VALUE PARENT_USER_VALUE , PARENT1.LOCATION_STRUCTURE_ID PARENT_LOC_STRUCT_ID, PARENT1.LOCATION_SEGMENT_QUALIFIER PARENT_QUALIFIER FROM ( 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 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) ) ) ) CHILD1, ( 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 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) ) ) ) PARENT1 WHERE CHILD1.PARENT_SEGMENT_ID = PARENT1.LOCATION_SEGMENT_ID AND CHILD1.LOCATION_STRUCTURE_ID=PARENT1.LOCATION_STRUCTURE_ID UNION ALL SELECT CHILD2.LOCATION_STRUCTURE_ID LOCATION_STRUCTURE_ID , CHILD2.LOCATION_SEGMENT_ID CHILD_ID , CHILD2.LOCATION_SEGMENT_VALUE CHILD_VALUE , CHILD2.LOCATION_SEGMENT_USER_VALUE CHILD_USER_VALUE , CHILD2.LOCATION_SEGMENT_QUALIFIER CHILD_QUALIFIER , CHILD2.COUNTRY_CODE CHILD_COUNTRY_CODE , PARENT2.LOCATION_SEGMENT_ID PARENT_ID , PARENT2.LOCATION_SEGMENT_VALUE PARENT_VALUE , PARENT2.LOCATION_SEGMENT_USER_VALUE PARENT_USER_VALUE , PARENT2.LOCATION_STRUCTURE_ID PARENT_LOC_STRUCT_ID, PARENT2.LOCATION_SEGMENT_QUALIFIER PARENT_QUALIFIER FROM ( 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' ) CHILD2, ( 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' ) PARENT2 WHERE CHILD2.PARENT_SEGMENT_ID = PARENT2.LOCATION_SEGMENT_ID AND CHILD2.LOCATION_STRUCTURE_ID=PARENT2.LOCATION_STRUCTURE_ID

View Text - HTML Formatted

SELECT CHILD1.LOCATION_STRUCTURE_ID LOCATION_STRUCTURE_ID
, CHILD1.LOCATION_SEGMENT_ID CHILD_ID
, CHILD1.LOCATION_SEGMENT_VALUE CHILD_VALUE
, CHILD1.LOCATION_SEGMENT_USER_VALUE CHILD_USER_VALUE
, CHILD1.LOCATION_SEGMENT_QUALIFIER CHILD_QUALIFIER
, CHILD1.COUNTRY_CODE CHILD_COUNTRY_CODE
, PARENT1.LOCATION_SEGMENT_ID PARENT_ID
, PARENT1.LOCATION_SEGMENT_VALUE PARENT_VALUE
, PARENT1.LOCATION_SEGMENT_USER_VALUE PARENT_USER_VALUE
, PARENT1.LOCATION_STRUCTURE_ID PARENT_LOC_STRUCT_ID
, PARENT1.LOCATION_SEGMENT_QUALIFIER PARENT_QUALIFIER
FROM ( 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 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) ) ) ) CHILD1
, ( 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 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) ) ) ) PARENT1
WHERE CHILD1.PARENT_SEGMENT_ID = PARENT1.LOCATION_SEGMENT_ID
AND CHILD1.LOCATION_STRUCTURE_ID=PARENT1.LOCATION_STRUCTURE_ID UNION ALL SELECT CHILD2.LOCATION_STRUCTURE_ID LOCATION_STRUCTURE_ID
, CHILD2.LOCATION_SEGMENT_ID CHILD_ID
, CHILD2.LOCATION_SEGMENT_VALUE CHILD_VALUE
, CHILD2.LOCATION_SEGMENT_USER_VALUE CHILD_USER_VALUE
, CHILD2.LOCATION_SEGMENT_QUALIFIER CHILD_QUALIFIER
, CHILD2.COUNTRY_CODE CHILD_COUNTRY_CODE
, PARENT2.LOCATION_SEGMENT_ID PARENT_ID
, PARENT2.LOCATION_SEGMENT_VALUE PARENT_VALUE
, PARENT2.LOCATION_SEGMENT_USER_VALUE PARENT_USER_VALUE
, PARENT2.LOCATION_STRUCTURE_ID PARENT_LOC_STRUCT_ID
, PARENT2.LOCATION_SEGMENT_QUALIFIER PARENT_QUALIFIER
FROM ( 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' ) CHILD2
, ( 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' ) PARENT2
WHERE CHILD2.PARENT_SEGMENT_ID = PARENT2.LOCATION_SEGMENT_ID
AND CHILD2.LOCATION_STRUCTURE_ID=PARENT2.LOCATION_STRUCTURE_ID