Dynamic security profile gives us the option to use a single person security profile & single data role with the additional help of Area of Responsibility to provide access of employees to the user based on the attributes like a business unit, department, etc. Using the dynamic security profile is a recommended approach as this requires less maintenance compared to the security provided via traditional method based on security dimension. Especially helpful in the cases where the security must be provided based on location, department or business unit and there are tens and hundreds of these values.

Creating Dynamic Person Security Profile

In Functional Setup manager search for the task ‘Manage Person Security Profile’

Click on the ‘Create’ icon to create a new Person Security Profile

On the Create Person Security Profile page provide the name for the profile.

Under Custom Criteria check the checkbox ‘Secure by Custom Criteria’ and copy the below mentioned SQL in the SQL box and then click on the validate button to validate the sql. (Provide the responsibility type code which is used by you in your project, delivered code is HR_REP)

EXISTS(SELECT 1 FROM PER_ALL_ASSIGNMENTS_M ASG,PER_PERIODS_OF_SERVICE PS,PER_ASG_RESPONSIBILITIES RES WHERE ASG.ASSIGNMENT_TYPE IN('E','C','N','P') AND ASG.EFFECTIVE_LATEST_CHANGE='Y' AND SYSDATE < ASG.EFFECTIVE_END_DATE AND PS.PERIOD_OF_SERVICE_ID=ASG.PERIOD_OF_SERVICE_ID AND (ASG.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE','SUSPENDED') OR (ASG.ASSIGNMENT_STATUS_TYPE  IN ('INACTIVE') AND NOT EXISTS (SELECT 1 FROM PER_ALL_ASSIGNMENTS_M EXASG WHERE EXASG.ASSIGNMENT_TYPE IN('E','C','N','P') AND EXASG.EFFECTIVE_LATEST_CHANGE = 'Y' AND EXASG.PERSON_ID = ASG.PERSON_ID AND SYSDATE < EXASG.EFFECTIVE_END_DATE AND EXASG.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE','SUSPENDED')) AND PS.ACTUAL_TERMINATION_DATE = (SELECT MAX(ALLPS.ACTUAL_TERMINATION_DATE) FROM PER_PERIODS_OF_SERVICE ALLPS WHERE ALLPS.PERSON_ID = ASG.PERSON_ID AND ALLPS.ACTUAL_TERMINATION_DATE IS NOT NULL))) AND SYSDATE BETWEEN RES.START_DATE AND NVL(RES.END_DATE,SYSDATE) AND ASG.PERSON_ID=&TABLE_ALIAS.PERSON_ID AND RES.PERSON_ID=(SELECT HRC_SESSION_UTIL.GET_USER_PERSONID FROM DUAL) AND RES.RESPONSIBILITY_TYPE=**'XX_HRM'**AND ASG.BUSINESS_UNIT_ID=RES.BUSINESS_UNIT_ID)

Click on the Next button to preview the dynamic security profile.

Click Save and Close to create the dynamic person security profile.

Creating Data Role

In Functional Setup manager search for the task ‘Manage Data Role and Security Profiles’

On the Search page click on the ‘Create’ icon to create a new data role.

Enter the details in Data Role, Role Description and select an existing Job Role in most cases you will be building the data role on the delivered ‘Human Resource Specialist’ role. It is always advised to use a custom job role which is based on the delivered role because when you use custom job role you can add or remove the privileges from it whereas when you use the delivered job role then you cannot add or remove the privileges. (Privileges provides the access to the navigations)

Click OK on the warning if you get any.

On the create Data Role: Security Criteria page select the required values (In person security profile select the value created in the above step)

Click on review button then click on submit to save the page.

Upon submission the data role will be created and ready for our use. This can now be assigned to any person.

Assigning data role & Area of Responsibility to the user

Assign this Data Role to the user from Security Console.

Assign the Area of responsibility to the user.