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