How to Migrate the Role Mappings from one instance to another using HDL?

Automatic Role Provisioning

A role is provisioned to a user automatically when at least one of the user’s assignments satisfies the conditions specified in the relevant role-mapping definition. The provisioning occurs when the assignment is either created or updated. For example, when a person is promoted to a management position, the line manager role is provisioned automatically to the person if an appropriate role mapping exists. Any change to a person’s assignment causes the person’s automatically provisioned roles to be reviewed and updated as necessary.

Role Deprovisioning

Automatically provisioned roles are deprovisioned automatically as soon as a user no longer satisfies the role-mapping conditions. For example, a line manager role that is provisioned to a user automatically is deprovisioned automatically when the user ceases to be a line manager.

Automatically provisioned roles can be deprovisioned manually at any time.

Manually provisioned roles are deprovisioned automatically only when all of the user’s work relationships are terminated; in all other circumstances, users retain manually provisioned roles until they are deprovisioned manually.

Role Mappings are defined using the task “Manage Role Provisioning Rules” from Setup and Maintenance area.

We will configure these rules in the Development POD and we have to either manually configure them in the TEST and PROD instances or we can migrate using the HDL. Using HDL is the preferred way as it will be complete within a matter of minutes without any chance of manual errors.

SQL Query to get the HDL file for migrating the Role Mapping

WITH data_query AS
(
SELECT prm.mapping_name
      ,TO_CHAR(prm.date_from, 'YYYY/MM/DD') date_from
      ,prm.system_person_type
      ,prm.manager_type
      ,prm.job_id
      ,job.job_code
      ,set1.set_code
      ,prm.position_id
      ,pos.position_code
      ,prm.assignment_status_type_id
      ,asg_sts.assignment_status_code
      ,prm.assignment_status
      ,prmr.role_id
      ,prd.role_common_name
      ,bu.bu_name
FROM   per_role_mappings        prm
      ,per_role_mapping_roles   prmr
      ,per_roles_dn             prd
      ,per_jobs_f_vl            job
      ,fnd_setid_sets_vl        set1
      ,hr_all_positions_f       pos
      ,per_assignment_status_types_vl asg_sts
      ,fun_all_business_units_v bu
WHERE  prm.role_mapping_id      = prmr.role_mapping_id
   AND prmr.role_id             = prd.role_id
   AND prm.job_id               = job.job_id(+)
   AND job.set_id               = set1.set_id(+)
   AND prm.position_id          = pos.position_id(+)
   AND pos.business_unit_id     = bu.bu_id(+)
   AND prm.assignment_status_type_id = asg_sts.assignment_status_type_id(+)
)

SELECT 'METADATA|RoleMapping|MappingName|DateFrom|JobSetCode|JobCode|PositionCode|PositionBusinessUnitName|SystemPersonType|AssignmentStatus|ManagerType' data_row
FROM   dual

UNION ALL

SELECT  DISTINCT
        'MERGE|RoleMapping|'
        || mapping_name
        || '|'
        || date_from
        || '|'
        || set_code
        || '|'
        || job_code
        || '|'
        || position_code
        || '|'
        || bu_name
        || '|'
        || system_person_type
        || '|'
        || assignment_status
        || '|'
        || manager_type
FROM    data_query

UNION ALL

SELECT 'METADATA|Role|MappingName|RoleCommonName|UseForAutoProvisioningFlag|RequestableFlag|SelfRequestableFlag'
FROM   dual

UNION ALL

SELECT  DISTINCT
        'MERGE|Role|'
        || mapping_name
        || '|'
        || role_common_name
        || '|'
        || 'Y'
        || '|'
        || 'N'
        || '|'
        || 'N'
FROM    data_query

Sample output will look like:

Sample HDL File

METADATA|RoleMapping|MappingName|DateFrom|JobSetCode|JobCode|PositionCode|PositionBusinessUnitName|SystemPersonType|AssignmentStatus|ManagerType
MERGE|RoleMapping|Pending Worker|2021/01/01|||||PWK|ACTIVE|
METADATA|Role|MappingName|RoleCommonName|UseForAutoProvisioningFlag|RequestableFlag|SelfRequestableFlag
MERGE|Role|Pending Worker|ORA_PER_PENDING_WORKER_ABSTRACT|Y|N|N

Once we get this output, copy it to text file and save as RoleMapping.dat, zip it and upload it.

If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.