Total Visits: 34
In this article, we will look into the SQL Query to fetch the Person Security Profile config info.
While Configuring the Person Security Profile, we have multiple options like access future record, access own record, secure by department, secure by legal employer, secure by payroll etc.
We can use the below query to extract the configuration that is setup in the application and review how it was configured.
SQL Query to extract the Person Security Profile Config
SELECT PPSF.NAME,
PPSF.DESCRIPTION,
DECODE(PPSF.ENABLED_FLAG, 'Y', 'Yes', 'N', 'No') ENABLED_FLAG,
DECODE(PPSF.ACCESS_TO_OWN_RECORD, 'Y', 'Yes', 'N', 'No') ACCESS_TO_OWN_RECORD,
DECODE(PPSF.INCLUDE_RELATED_CONTACTS, 'Y', 'Yes', 'N', 'No') INCLUDE_RELATED_CONTACTS,
DECODE(PPSF.INCLUDE_SHARED_PEOPLE_INFO, 'Y', 'Yes', 'N', 'No') INCLUDE_SHARED_PEOPLE_INFO,
DECODE(PPSF.VIEW_ALL, 'Y', 'Yes', 'N', 'No') VIEW_ALL,
DECODE(PPSF.INCLUDE_FUTURE_PERSONS, 'Y', 'Yes', 'N', 'No') INCLUDE_FUTURE_PERSONS,
DECODE(PPSF.SECURE_BY_PERSON_TYPE, 'Y', 'Yes', 'N', 'No') SECURE_BY_PERSON_TYPE,
(
SELECT PPT.USER_PERSON_TYPE
FROM PER_PERSON_SEC_PROF_PER_TYPES PPSP,
PER_PERSON_TYPES_TL PPT
WHERE PPSP.PERSON_SECURITY_PROFILE_ID = PPSF.PERSON_SECURITY_PROFILE_ID
AND PPT.PERSON_TYPE_ID = PPSP.PERSON_TYPE_ID
) PERSON_TYPE_NAME,
DECODE(PPSF.PRIMARY_ASSIGNMENT_ONLY, 'Y', 'Yes', 'N', 'No') PRIMARY_ASSIGNMENT_ONLY,
DECODE(PPSF.SECURE_BY_MGR_HIERARCHY, 'Y', 'Yes', 'N', 'No') SECURE_BY_MGR_HIERARCHY,
(
SELECT FLV.MEANING
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = 'PER_PRSN_SEC_PROF_MGR_TYPES'
AND FLV.LOOKUP_CODE = PPSF.MANAGER_TYPES
) MANAGER_TYPES,
(
SELECT FLV.MEANING
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = 'PER_SUPERVISOR_TYPE'
AND FLV.LOOKUP_CODE = PPSCMGRTYPE.MANAGER_TYPE_VALUE
) MANAGER_TYPE_VALUE,
DECODE(PPSF.PERSON_OR_ASSIGNMENT_LEVEL, 'P', 'Person', 'Assignment') P_A_LEVEL,
PPSF.MAX_LEVELS_IN_HIERARCHY,
(
SELECT FLV.MEANING
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = 'PER_PRSN_SEC_PROF_MGR_HIER_FOR'
AND FLV.LOOKUP_CODE = PPSF.EVAL_MGR_HIERARCHY_FOR
) MGR_HIERARCHY_CONTENT,
DECODE(PPSF.SECURE_BY_DEPARTMENT, 'Y', 'Yes', 'N', 'No') SECURE_BY_DEPARTMENT,
(
SELECT POSP.NAME
FROM PER_ORG_SECURITY_PROFILES POSP
WHERE POSP.ORG_SECURITY_PROFILE_ID = PPSF.DEPT_ORG_SECURITY_PROFILE_ID
) DEPT_NAME,
DECODE(PPSF.SECURE_BY_BUSINESS_UNIT, 'Y', 'Yes', 'N', 'No') SECURE_BY_BUSINESS_UNIT,
(
SELECT POSP.NAME
FROM PER_ORG_SECURITY_PROFILES POSP
WHERE POSP.ORG_SECURITY_PROFILE_ID = PPSF.BU_ORG_SECURITY_PROFILE_ID
) BU_NAME,
DECODE(PPSF.SECURE_BY_LEGAL_EMPLOYER, 'Y', 'Yes', 'N', 'No') SECURE_BY_LEGAL_EMPLOYER,
(
SELECT POSP.NAME
FROM PER_ORG_SECURITY_PROFILES POSP
WHERE POSP.ORG_SECURITY_PROFILE_ID = PPSF.LEG_EMP_ORG_SEC_PROFILE_ID
) LEG_EMP_NAME,
DECODE(PPSF.SECURE_BY_POSITION, 'Y', 'Yes', 'N', 'No') SECURE_BY_POSITION,
(
SELECT PPOS.NAME
FROM PER_POSITION_SECURITY_PROFILES PPOS
WHERE PPOS.POSITION_SECURITY_PROFILE_ID = PPSF.POSITION_SECURITY_PROFILE_ID
) POS_NAME,
DECODE(PPSF.SECURE_BY_PAYROLL, 'Y', 'Yes', 'N', 'No') SECURE_BY_PAYROLL,
(
SELECT PSEC.NAME
FROM PAY_PAY_SECURITY_PROFILES PSEC
WHERE PSEC.PAY_SECURITY_PROFILE_ID = PPSF.PAY_SECURITY_PROFILE_ID
) PAYROLL_NAME,
DECODE(PPSF.SECURE_BY_LDG, 'Y', 'Yes', 'N', 'No') SECURE_BY_LDG,
(
SELECT PLDG.NAME
FROM PER_LDG_SECURITY_PROFILES PLDG
WHERE PLDG.LDG_SECURITY_PROFILE_ID = PPSF.LDG_SECURITY_PROFILE_ID
) LDG_NAME,
DECODE(PPSF.SECURE_BY_GLOBAL_NAME_RANGE, 'Y', 'Yes', 'N', 'No') SECURE_BY_GLOBAL_NAME_RANGE,
PPSF.GLOBAL_NAME_RANGE_START,
PPSF.GLOBAL_NAME_RANGE_END,
DECODE(PPSF.SECURE_BY_CUSTOM_RESTRICTION, 'Y', 'Yes', 'N', 'No') SECURE_BY_CUSTOM_RESTRICTION,
PPSF.CUSTOM_RESTRICTION_TEXT,
(
SELECT FLV.MEANING
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = 'ORA_PER_PRSN_ASGS_TO_EVALUATE'
AND FLV.LOOKUP_CODE = PPSF.ASGS_TO_EVALUATE
) ASGS_TO_EVALUATE,
(
SELECT FLV.MEANING
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = 'ORA_PER_PRSN_SEC_PROF_PURPOSE'
AND FLV.LOOKUP_CODE = PPSF.PURPOSE
) PURPOSE,
DECODE(PPSF.SECURE_BY_AOR, 'Y', 'Yes', 'N', 'No') SECURE_BY_AOR,
PPSCPERTYPE.PERSON_TYPE_COLUMN,
PPSCPERTYPE.SYSTEM_PERSON_TYPE,
PPSCPERTYPE.ACCESS_TO,
(
SELECT PPTT.USER_PERSON_TYPE
FROM PER_PERSON_TYPES_TL PPTT,
PER_PERSON_TYPES PPT
WHERE PPT.SEEDED_PERSON_TYPE_KEY = PPSCPERTYPE.SYSTEM_PERSON_TYPE
AND PPTT.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPTT.LANGUAGE = 'US'
) USER_PERSON_TYPE,
(
SELECT FLV.MEANING
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = 'PER_PRSN_SEC_PROF_PER_TYPE'
AND FLV.LOOKUP_CODE = PPSCPERTYPE.PERSON_TYPE_COLUMN
) PERSON_TYPE,
(
SELECT FLV.MEANING
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = 'PER_PRSN_SEC_PROF_PER_TYP_ACCS'
AND FLV.LOOKUP_CODE = PPSCPERTYPE.ACCESS_TO
) ACCESS_TO_MEANING
FROM PER_PERSON_SECURITY_PROFILES PPSF,
PER_PERSON_SEC_PROF_MGR_TYPES PPSCMGRTYPE,
PER_PERSON_SEC_PROF_PER_TYPES PPSCPERTYPE
WHERE PPSF.PERSON_SECURITY_PROFILE_ID = PPSCMGRTYPE.PERSON_SECURITY_PROFILE_ID(+)
AND PPSF.PERSON_SECURITY_PROFILE_ID = PPSCPERTYPE.PERSON_SECURITY_PROFILE_ID(+)
If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.