View: PER_CAL_ORG_HIER_VALUES_V

Product:	PER - Human Resources
Description:	per_cal_org_hier_values_v
Implementation/DBA Data:	APPS.PER_CAL_ORG_HIER_VALUES_V

View Text

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)

Columns