Search for:
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
Jan 6 2023
HDL – Script to DELETE positions data

In your test environments, you may encounter issues where you want to DELETE positions data. You can use below script for that:

SELECT DATA_ROW
FROM (
SELECT 'METADATA|Position|PositionId|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner'  AS DATA_ROW
FROM DUAL
UNION all
select 'DELETE'||'|'||
       'Position' ||'|'||
       hapf.Position_Id||'|'||
       to_char(hapf.Effective_Start_Date,'RRRR/MM/DD')||'|'||
       to_char(hapf.Effective_End_Date,'RRRR/MM/DD') ||'|'||
      (select email_hrc.source_system_id
         from hrc_integration_key_map email_hrc
        WHERE hapf.Position_Id = email_hrc.surrogate_id) ||'|'||
      (select email_hrc.source_system_owner
         from hrc_integration_key_map email_hrc
        WHERE hapf.Position_Id = email_hrc.surrogate_id) AS DATA_ROW
  from hr_all_positions_f hapf
)
Mandeep Gupta 0 Comments
Dec 20 2021
BIP – Query to extract child positions for a logged in person’s position

Fusion HCM provides you the capability of using Position Trees as well as Position Hierarchies. In case you need to extract child position details based on position id of logged in person, you can make use of below query:

SELECT pphf.position_id
  FROM per_position_hierarchy_f pphf
 WHERE 1=1
  START WITH pphf.parent_position_id = NVL((select position_id from per_all_assignments_m paam
                                         where paam.assignment_type = 'E'
					   and paam.person_id = hrc_session_util.get_user_personid
					   and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
					   and paam.effective_latest_change = 'Y'
					   and paam.effective_sequence = 1),300000141634831)
CONNECT BY PRIOR pphf.parent_position_id = 	pphf.position_id
Mandeep Gupta 0 Comments