HR Security Profiles Data through SQL Query

By | December 17, 2016

Following is the full list of all HR Security Profiles

Query to get Full List of Security Profiles
Query to get Organization Based Security Profiles List
Query to get Payroll Based Security Profiles List
Query to get Custom Security Based Security Profiles List
–Query to get Others Security Profiles List

Oracle HRMS provides an option to create security profiles. These custom HR Security profiles control the access of users on data. Here you can find the list of all HR Custom security profiles.

Here you will find the query to get all HR security profiles through SQL Query

–Query to get Full List of Security Profiles

select
——Main Tab
SECURITY_PROFILE_ID,SECURITY_PROFILE_NAME
,’********Main Tab****’,BUSINESS_GROUP_NAME,VIEW_ALL_EMPLOYEES_FLAG,VIEW_ALL_CWK_FLAG,VIEW_ALL_APPLICANTS_FLAG,VIEW_ALL_CONTACTS_FLAG,VIEW_ALL_CANDIDATES_FLAG,VIEW_ALL_FLAG,ALLOW_GRANTED_USERS_FLAG,RESTRICT_ON_INDIVIDUAL_ASG
—-Organization Security Tab
,’********Org Sec Tab****’,ORG_SECURITY_MODE,ORGANIZATION_HIERARCHY,VIEW_ALL_ORGANIZATIONS_FLAG,TOP_ORGANIZATION,TOP_ORGANIZATION_METHOD,INCLUDE_TOP_ORGANIZATION_FLAG,EXCLUDE_BUSINESS_GROUPS_FLAG
——-Payroll Tab
,’********Payroll Tab****’ ,VIEW_ALL_PAYROLLS_FLAG,INCLUDE_EXCLUDE_PAYROLL_FLAG
——-Position Tab
,’********Position Tab****’,TOP_POSITION_METHOD ,VIEW_ALL_POSITIONS_FLAG ,POSITION_ID ,TOP_POSITION,INCLUDE_TOP_POSITION_FLAG
——supervisor Tab
,’********Supervisor Tab****’
,RESTRICT_BY_SUPERVISOR_FLAG ,SUPERVISOR_LEVELS ,EXCLUDE_SECONDARY_ASGS_FLAG ,EXCLUDE_PERSON_FLAG ,NAMED_PERSON ,NAMED_PERSON_ID ,CUSTOM_RESTRICTION_FLAG
——-Custom Security
,’********Custom Sec Tab****’ ,RESTRICTION_TEXT ,REQUEST_ID ,PROGRAM_APPLICATION_ID ,PROGRAM_ID ,PROGRAM_UPDATE_DATE
from PER_SECURITY_PROFILES_V

Back to top

–Query to get  Organization Based Security Profiles List

select
——Main Tab
–psp.SECURITY_PROFILE_ID,
SECURITY_PROFILE_NAME
,BUSINESS_GROUP_NAME
,pso.ORGANIZATION_NAME
,hr_general.decode_lookup(‘ORG_TYPE’,type) Org_Type
from PER_SECURITY_PROFILES_V psp,PER_SECURITY_ORGANIZATIONS_V pso, hr_all_organization_units haou
where 1=1
–and psp.SECURITY_PROFILE_NAME in ( ‘BHN LOB Audit’,’BHN Key User’)
and psp.SECURITY_PROFILE_ID=pso.SECURITY_PROFILE_ID
and pso.organization_id = haou.organization_id
order by 2,1

Back to top

–Query to get Payroll Based Security Profiles List

select
——Main Tab
SECURITY_PROFILE_NAME
,BUSINESS_GROUP_NAME
,pap.payroll_name
from PER_SECURITY_PROFILES_V psp,PAY_SECURITY_PAYROLLS pspy,pay_all_payrolls_f pap
where 1=1
and VIEW_ALL_PAYROLLS_FLAG = ‘N’
and pspy.payroll_id = pap.payroll_id
and pspy.business_group_id =pap.business_group_id
and trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
and psp.SECURITY_PROFILE_ID=pspy.SECURITY_PROFILE_ID

Back to top

–Query to get Custom Security Based Security Profiles List

select
——Main Tab
SECURITY_PROFILE_NAME
,BUSINESS_GROUP_NAME
,’********Custom Sec Tab****’ ,RESTRICTION_TEXT
from PER_SECURITY_PROFILES_V
where 1=1
–and SECURITY_PROFILE_NAME in ( ‘BHN LOB Audit’,’BHN Key User’)
and upper(ORG_SECURITY_MODE) = ‘NONE’
and VIEW_ALL_PAYROLLS_FLAG = ‘Y’
and RESTRICTION_TEXT is not null

Back to top

–Query to get Others Security Profiles List

select
——Main Tab
SECURITY_PROFILE_ID,SECURITY_PROFILE_NAME
,’********Main Tab****’,BUSINESS_GROUP_NAME,VIEW_ALL_EMPLOYEES_FLAG,VIEW_ALL_CWK_FLAG,VIEW_ALL_APPLICANTS_FLAG,VIEW_ALL_CONTACTS_FLAG,VIEW_ALL_CANDIDATES_FLAG,VIEW_ALL_FLAG,ALLOW_GRANTED_USERS_FLAG,RESTRICT_ON_INDIVIDUAL_ASG
—-Organization Security Tab
,’********Org Sec Tab****’,ORG_SECURITY_MODE,ORGANIZATION_HIERARCHY,VIEW_ALL_ORGANIZATIONS_FLAG,TOP_ORGANIZATION,TOP_ORGANIZATION_METHOD,INCLUDE_TOP_ORGANIZATION_FLAG,EXCLUDE_BUSINESS_GROUPS_FLAG
——-Payroll Tab
,’********Payroll Tab****’ ,VIEW_ALL_PAYROLLS_FLAG,INCLUDE_EXCLUDE_PAYROLL_FLAG
——-Position Tab
,’********Position Tab****’,TOP_POSITION_METHOD ,VIEW_ALL_POSITIONS_FLAG ,POSITION_ID ,TOP_POSITION,INCLUDE_TOP_POSITION_FLAG
——supervisor Tab
,’********Supervisor Tab****’
,RESTRICT_BY_SUPERVISOR_FLAG ,SUPERVISOR_LEVELS ,EXCLUDE_SECONDARY_ASGS_FLAG ,EXCLUDE_PERSON_FLAG ,NAMED_PERSON ,NAMED_PERSON_ID ,CUSTOM_RESTRICTION_FLAG
——-Custom Security
,’********Custom Sec Tab****’ ,RESTRICTION_TEXT ,REQUEST_ID ,PROGRAM_APPLICATION_ID ,PROGRAM_ID ,PROGRAM_UPDATE_DATE
from PER_SECURITY_PROFILES_V
where 1=1
–and SECURITY_PROFILE_NAME in ( ‘LSG LOB Audit’,’LSG Key User’)
and upper(ORG_SECURITY_MODE) = ‘NONE’
and VIEW_ALL_PAYROLLS_FLAG = ‘Y’
and RESTRICTION_TEXT is null

For more theory on HR Security Profiles, please refer this oracle link.

If you  have liked the contents in this post and you think it can be helpful to others, please share it at least once in your circle, in this way, you will join me in my cause to Learn Share and Grow. Come on, Lets Grow Together.

Related Posts

Free Online Test
Solved Issued of Empty Email Notification
Steps for creating custom form in Oracle Apps
How to create backend logging mechanism
Useful Apps SQL Queries Pool
Top 10 Apps Technical Interview Questions