BIP – Query to Extract Position FTE values
When position hierarchy is enabled in Cloud HCM, Manage Positions UI shows some dynamically populated fields along with Parent position.
For example:
Use the below query to extract above details:
SELECT Positions.name "Position Name"
,Positions.FTE "Position Current FTE"
,Positions.INCUMBENT_FTE "Current Incumbent FTE"
,(Positions.FTE - Positions.INCUMBENT_FTE) "Difference FTE"
FROM
(SELECT HAPFT.NAME,
HAPF.FTE,
(select SUM(PAWMF.VALUE)
from PER_ALL_ASSIGNMENTS_M PAAM,
PER_ASSIGN_WORK_MEASURES_F PAWMF
where 1=1
AND PAAM.POSITION_ID = HAPF.POSITION_ID
AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PAAM.ASSIGNMENT_ID = PAWMF.ASSIGNMENT_ID
AND PAWMF.UNIT = 'FTE') AS INCUMBENT_FTE
FROM HR_ALL_POSITIONS_F HAPF,
HR_ALL_POSITIONS_F_TL HAPFT
WHERE HAPF.POSITION_ID = HAPFT.POSITION_ID
AND USERENV('LANG') = HAPFT.LANGUAGE
AND TRUNC(SYSDATE) BETWEEN HAPF.EFFECTIVE_START_DATE AND HAPF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN HAPFT.EFFECTIVE_START_DATE AND HAPFT.EFFECTIVE_END_DATE
AND HAPFT.NAME IN('Test Position')
ORDER BY HAPFT.NAME ) Positions