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.PER_CAL_ORG_HIER_VALUES_V

Source

View Text - Preformatted


SELECT OS.ORGANIZATION_STRUCTURE_ID ,OS.NAME ,OS.BUSINESS_GROUP_ID ,OSV.ORG_STRUCTURE_VERSION_ID ,OSV.VERSION_NUMBER ,OSE.ORG_STRUCTURE_ELEMENT_ID ,OSE.ORGANIZATION_ID_PARENT ,OSE.ORGANIZATION_ID_CHILD ,ORG.NAME ,ENV.CAL_ENTRY_VALUE_ID ,DECODE(ENV.CAL_ENTRY_VALUE_ID,null,'N','Y') ,ENV.PARENT_ENTRY_VALUE_ID ,ENV.OVERRIDE_NAME ,ENV.OVERRIDE_TYPE ,ENV.USAGE_FLAG ,hr_cal_entry_value_api.get_ele_level (OSE.ORG_STRUCTURE_ELEMENT_ID, OSE.ORG_STRUCTURE_VERSION_ID) FROM PER_ORGANIZATION_STRUCTURES OS, PER_ORG_STRUCTURE_VERSIONS OSV, PER_ORG_STRUCTURE_ELEMENTS OSE, HR_ALL_ORGANIZATION_UNITS ORG, PER_CAL_ENTRY_VALUES ENV WHERE OS.ORGANIZATION_STRUCTURE_ID = OSV.ORGANIZATION_STRUCTURE_ID AND OSV.ORG_STRUCTURE_VERSION_ID = HR_CAL_ENTRY_VALUE_API.get_g_current_osv_id AND OSV.ORG_STRUCTURE_VERSION_ID = OSE.ORG_STRUCTURE_VERSION_ID AND OSE.ORGANIZATION_ID_CHILD = ORG.ORGANIZATION_ID AND OSE.ORG_STRUCTURE_ELEMENT_ID = env.ORG_STRUCTURE_ELEMENT_ID (+) AND OSE.ORGANIZATION_ID_CHILD = env.ORGANIZATION_ID (+) AND ENV.CALENDAR_ENTRY_ID (+) = HR_CAL_ENTRY_VALUE_API.get_g_current_entry_id UNION ALL SELECT OS1.ORGANIZATION_STRUCTURE_ID ,OS1.NAME ,OS1.BUSINESS_GROUP_ID ,OSV1.ORG_STRUCTURE_VERSION_ID ,OSV1.VERSION_NUMBER ,OSE1.ORG_STRUCTURE_ELEMENT_ID ,-987123654 ,OSE1.ORGANIZATION_ID_PARENT ,ORG1.NAME ,ENV1.CAL_ENTRY_VALUE_ID ,DECODE (ENV1.CAL_ENTRY_VALUE_ID,null,'N','Y') ,ENV1.PARENT_ENTRY_VALUE_ID ,ENV1.OVERRIDE_NAME ,ENV1.OVERRIDE_TYPE ,ENV1.USAGE_FLAG ,'0..' FROM PER_ORGANIZATION_STRUCTURES OS1, PER_ORG_STRUCTURE_VERSIONS OSV1, PER_ORG_STRUCTURE_ELEMENTS OSE1, HR_ALL_ORGANIZATION_UNITS ORG1, PER_CAL_ENTRY_VALUES ENV1 WHERE OS1.ORGANIZATION_STRUCTURE_ID = OSV1.ORGANIZATION_STRUCTURE_ID AND OSV1.ORG_STRUCTURE_VERSION_ID = HR_CAL_ENTRY_VALUE_API.get_g_current_osv_id AND OSV1.ORG_STRUCTURE_VERSION_ID = OSE1.ORG_STRUCTURE_VERSION_ID AND OSE1.ORGANIZATION_ID_PARENT = ORG1.ORGANIZATION_ID AND OSE1.ORG_STRUCTURE_ELEMENT_ID = ENV1.ORG_STRUCTURE_ELEMENT_ID (+) AND OSE1.ORGANIZATION_ID_PARENT = ENV1.ORGANIZATION_ID (+) AND ENV1.CALENDAR_ENTRY_ID (+) = HR_CAL_ENTRY_VALUE_API.get_g_current_entry_id AND OSE1.ORG_STRUCTURE_ELEMENT_ID = (select min(ele.ORG_STRUCTURE_ELEMENT_ID) from per_org_structure_elements ele where ele.org_structure_version_id = HR_CAL_ENTRY_VALUE_API.get_g_current_osv_id and ele.ORGANIZATION_ID_PARENT not in ( select ele2.ORGANIZATION_ID_CHILD from per_org_structure_elements ele2 where ele2.org_structure_version_ID = HR_CAL_ENTRY_VALUE_API.get_g_current_osv_id) group by ele.ORGANIZATION_ID_PARENT)

View Text - HTML Formatted

SELECT OS.ORGANIZATION_STRUCTURE_ID
, OS.NAME
, OS.BUSINESS_GROUP_ID
, OSV.ORG_STRUCTURE_VERSION_ID
, OSV.VERSION_NUMBER
, OSE.ORG_STRUCTURE_ELEMENT_ID
, OSE.ORGANIZATION_ID_PARENT
, OSE.ORGANIZATION_ID_CHILD
, ORG.NAME
, ENV.CAL_ENTRY_VALUE_ID
, DECODE(ENV.CAL_ENTRY_VALUE_ID
, NULL
, 'N'
, 'Y')
, ENV.PARENT_ENTRY_VALUE_ID
, ENV.OVERRIDE_NAME
, ENV.OVERRIDE_TYPE
, ENV.USAGE_FLAG
, HR_CAL_ENTRY_VALUE_API.GET_ELE_LEVEL (OSE.ORG_STRUCTURE_ELEMENT_ID
, OSE.ORG_STRUCTURE_VERSION_ID)
FROM PER_ORGANIZATION_STRUCTURES OS
, PER_ORG_STRUCTURE_VERSIONS OSV
, PER_ORG_STRUCTURE_ELEMENTS OSE
, HR_ALL_ORGANIZATION_UNITS ORG
, PER_CAL_ENTRY_VALUES ENV
WHERE OS.ORGANIZATION_STRUCTURE_ID = OSV.ORGANIZATION_STRUCTURE_ID
AND OSV.ORG_STRUCTURE_VERSION_ID = HR_CAL_ENTRY_VALUE_API.GET_G_CURRENT_OSV_ID
AND OSV.ORG_STRUCTURE_VERSION_ID = OSE.ORG_STRUCTURE_VERSION_ID
AND OSE.ORGANIZATION_ID_CHILD = ORG.ORGANIZATION_ID
AND OSE.ORG_STRUCTURE_ELEMENT_ID = ENV.ORG_STRUCTURE_ELEMENT_ID (+)
AND OSE.ORGANIZATION_ID_CHILD = ENV.ORGANIZATION_ID (+)
AND ENV.CALENDAR_ENTRY_ID (+) = HR_CAL_ENTRY_VALUE_API.GET_G_CURRENT_ENTRY_ID UNION ALL SELECT OS1.ORGANIZATION_STRUCTURE_ID
, OS1.NAME
, OS1.BUSINESS_GROUP_ID
, OSV1.ORG_STRUCTURE_VERSION_ID
, OSV1.VERSION_NUMBER
, OSE1.ORG_STRUCTURE_ELEMENT_ID
, -987123654
, OSE1.ORGANIZATION_ID_PARENT
, ORG1.NAME
, ENV1.CAL_ENTRY_VALUE_ID
, DECODE (ENV1.CAL_ENTRY_VALUE_ID
, NULL
, 'N'
, 'Y')
, ENV1.PARENT_ENTRY_VALUE_ID
, ENV1.OVERRIDE_NAME
, ENV1.OVERRIDE_TYPE
, ENV1.USAGE_FLAG
, '0..'
FROM PER_ORGANIZATION_STRUCTURES OS1
, PER_ORG_STRUCTURE_VERSIONS OSV1
, PER_ORG_STRUCTURE_ELEMENTS OSE1
, HR_ALL_ORGANIZATION_UNITS ORG1
, PER_CAL_ENTRY_VALUES ENV1
WHERE OS1.ORGANIZATION_STRUCTURE_ID = OSV1.ORGANIZATION_STRUCTURE_ID
AND OSV1.ORG_STRUCTURE_VERSION_ID = HR_CAL_ENTRY_VALUE_API.GET_G_CURRENT_OSV_ID
AND OSV1.ORG_STRUCTURE_VERSION_ID = OSE1.ORG_STRUCTURE_VERSION_ID
AND OSE1.ORGANIZATION_ID_PARENT = ORG1.ORGANIZATION_ID
AND OSE1.ORG_STRUCTURE_ELEMENT_ID = ENV1.ORG_STRUCTURE_ELEMENT_ID (+)
AND OSE1.ORGANIZATION_ID_PARENT = ENV1.ORGANIZATION_ID (+)
AND ENV1.CALENDAR_ENTRY_ID (+) = HR_CAL_ENTRY_VALUE_API.GET_G_CURRENT_ENTRY_ID
AND OSE1.ORG_STRUCTURE_ELEMENT_ID = (SELECT MIN(ELE.ORG_STRUCTURE_ELEMENT_ID)
FROM PER_ORG_STRUCTURE_ELEMENTS ELE
WHERE ELE.ORG_STRUCTURE_VERSION_ID = HR_CAL_ENTRY_VALUE_API.GET_G_CURRENT_OSV_ID
AND ELE.ORGANIZATION_ID_PARENT NOT IN ( SELECT ELE2.ORGANIZATION_ID_CHILD
FROM PER_ORG_STRUCTURE_ELEMENTS ELE2
WHERE ELE2.ORG_STRUCTURE_VERSION_ID = HR_CAL_ENTRY_VALUE_API.GET_G_CURRENT_OSV_ID) GROUP BY ELE.ORGANIZATION_ID_PARENT)