HDL – Sample HDL to load HCM Group Members

Oracle provides HCMGroup.dat which can be used to assign members against HCM Group. However, the business object has a major limitation (till release version 22A) that it can be used only for below two seeded Oracle HCM Groups:

• Manually Maintained Excluded Members
• Manually Maintained Audit Members

Below is the sample HDL file to add members to Audit HCM Group:

METADATA|HCMGroup|GrpMemberId|EffectiveStartDate|GrpId|MemberId|GroupCode|PersonNumber|SourceSystemOwner|SourceSystemId|GUID|EffectiveEndDate
MERGE|HCMGroup||2022/01/04|||MANUALLY MAINTAINED AUDIT MEMBERS|1014|HRC_SQLLOADER|MANUALLY MAINTAINED AUDIT MEMBERS_1014||

For any custom HCM Groups, this can’t be used. If you try to use any custom Group Code in the HDL file, you will receive below error:

Valid values for attribute GROUP CODE: MANUALLY MAINTAINED AUDIT MEMBERS,MANUALLY MAINTAINED EXCLUDED MEMBERS.

Query to extract members of an HCM Group:

SELECT hgt.group_name
      ,papf.person_id
      ,papf.person_number
      ,ppnf.full_name
      ,ppnf.last_name
      ,ppnf.first_name
      ,hgmf.effective_start_date
      ,hgmf.effective_end_date
  FROM PER_ALL_PEOPLE_F papf,
       PER_PERSON_NAMES_F ppnf,
       HWM_GRP_MEMBERS_F hgmf,
       HWM_GRPS_B hgb,
       HWM_GRPS_TL hgt
 WHERE hgb.grp_id = hgmf.grp_id
   AND hgb.grp_id = hgt.grp_id 
   AND hgt.language = 'US'
   AND papf.person_id = hgmf.member_id
   AND papf.person_id = ppnf.person_id
   AND ppnf.name_type ='GLOBAL'
   AND trunc(sysdate) BETWEEN hgmf.effective_start_date and hgmf.effective_end_date
   AND trunc(sysdate) BETWEEN ppnf.effective_start_date and ppnf.effective_end_date
ORDER BY 1,3