Search for:
Sep 3 2023
BIP – Query to find workers with FTE < 1

Below SQL query can be used to find part-timers in HCM. Any worker where FTE is less than 1 is normally considered as part-timer:

select paam.assignment_number, pawmf.value
  from per_legal_employers ple
      ,per_periods_of_service ppos
      ,per_all_assignments_m paam
      ,PER_ASSIGN_WORK_MEASURES_F pawmf
 where ppos.legal_entity_id = ple.organization_id
   and paam.period_of_Service_id = ppos.period_of_Service_id
   and TRUNC(SYSDATE) between paam.effective_start_date AND paam.effective_end_date
   and TRUNC(SYSDATE) between pawmf.effective_start_date AND pawmf.effective_end_date
   and paam.assignment_status_type like 'ACTIVE%'
   and ple.name like 'XX%United%Stat%'
   and paam.assignment_id = pawmf.assignment_id
   and pawmf.unit = 'FTE'
   and  pawmf.value <1
   and paam.assignment_type ='E'
   and ppos.actual_termination_date is null
 order by 1
Mandeep Gupta 0 Comments
Aug 6 2023
BIP – Query to find list of same person having same position multiple times

On the responsive Position UI, the position screens shows the Pending Worker and Worker record as an incumbent and the FTE goes in negative.

Below SQL can be used to identify such persons:

SELECT paam.person_id, hapfv.name
  FROM per_all_assignments_m paam
      ,hr_all_positions_f_vl hapfv
 WHERE paam.position_id = hapfv.position_id
   AND paam.assignment_type NOT LIKE '%T'
   AND paam.assignment_status_type NOT LIKE 'INACTIVE'
   AND TRUNC(SYSDATE) BETWEEN hapfv.effective_start_date AND hapfv.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
  GROUP BY paam.person_id, hapfv.name
 HAVING COUNT(*) > 1
Mandeep Gupta 0 Comments
May 30 2021
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
Mandeep Gupta 0 Comments