eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AR_LOCATION_VALUES

Source

View Text - Preformatted


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'

View Text - HTML Formatted

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'