While creating an HR Security Profile, You have many options.

One of the option is to create a custom Security Profile. There are several pieces to the custom security code that is used.  Below you will see the custom code for a security profile and an explanation of what the profile is designed to do. We will then break out each part and explain what it does.

Name: GLB EMEA ABC Excl HR JP2

Explanation: Access to the ABC division in the EMEA business Group excluding HR AND access to all employees at locations JP2 .

(select greatest(date_start,sysdate)

 from hr.per_all_assignments_f paf,

 hr.per_periods_of_service ppos

 where paf.period_of_service_id=ppos.period_of_service_id

  and paf.assignment_id=assignment.assignment_id

 and date_start between paf.effective_start_date and paf.effective_end_date)

 between assignment.effective_start_date and assignment.effective_end_date

and (select greatest(date_start,sysdate)

 from hr.per_all_assignments_f paf,

 hr.per_periods_of_service ppos

 where paf.period_of_service_id=ppos.period_of_service_id

 and paf.assignment_id=assignment.assignment_id

 and date_start between paf.effective_start_date and paf.effective_end_date)

  between PERSON.effective_start_date and PERSON.effective_end_date

and (((ASSIGNMENT.ass_attribute1 = 'N' or ASSIGNMENT.ass_attribute1 is null)

and ASSIGNMENT.business_group_id = 9119 --This is the BG Id for EMEA

and ((ASSIGNMENT.organization_id in (select hou.organization_id from

 hr_all_organization_units hou,

 pay_cost_allocation_keyflex pcak 

 where hou.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id

  and pcak.segment4 in

('4','25','35','90','90','30')))

or (ASSIGNMENT.location_id in (select hl.location_id from

 hr_locations_all hl

 where hl.location_code in ('JP2'))))) or (ASSIGNMENT.business_group_id = 0 and PERSON.office_number = 'XYZ'))

Part I

(select greatest(date_start,sysdate)

 from hr.per_all_assignments_f paf,

 hr.per_periods_of_service ppos

 where paf.period_of_service_id=ppos.period_of_service_id

 and paf.assignment_id=assignment.assignment_id

 and date_start between paf.effective_start_date and paf.effective_end_date)

 between assignment.effective_start_date and assignment.effective_end_date

and (select greatest(date_start,sysdate)

 from hr.per_all_assignments_f paf,

 hr.per_periods_of_service ppos

 where paf.period_of_service_id=ppos.period_of_service_id

 and paf.assignment_id=assignment.assignment_id

 and date_start between paf.effective_start_date and paf.effective_end_date)

 between PERSON.effective_start_date and PERSON.effective_end_date

This piece of code allows the user to see future-dated changes that fit into the profile. This piece of a code is a MUST to begin any custom security profile.

Part II

and (((ASSIGNMENT.ass_attribute1 = 'N' or ASSIGNMENT.ass_attribute1 is null)

This piece of code excludes records that have the ‘HR Employee’ flag set to [Yes]. In this example the HR Employee flag is in assignment attribute1.

Part III

and ASSIGNMENT.business_group_id = 9119

This grants specific access to the Business Group or Groups. If you need to grant access to multiple business groups you would write this piece as follows.

 and ASSIGNMENT.business_group_id in (9110,9112)

Part IV

and ((ASSIGNMENT.organization_id in (select hou.organization_id from

 hr_all_organization_units hou,

  pay_cost_allocation_keyflex pcak 

 where hou.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id

 and pcak.segment4 in

('......')))

This grants specific access to the Divisions to which the user has access. Changing the list of division numbers changes the access.

Part V

or (ASSIGNMENT.location_id in (select hl.location_id from

 hr_locations_all hl

 where hl.location_code in ('JP2'))))) 

This grants additional access to the employees in the listed locations.

Part VI

or (ASSIGNMENT.business_group_id = 0 and PERSON.office_number = 'HR'))

This allows the user to access records by looking at the record in the Setup Business Group. This piece of a code is a MUST for every Global profile.