View: OE_INLINE_TWO_LEVEL_V

Product:	ONT - Order Management
Description:	Used in Add Customer. Based on table ar_location_values.
Implementation/DBA Data:	APPS.OE_INLINE_TWO_LEVEL_V

View Text

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

Columns