• Post category:Security
  • Post comments:0 Comments
  • Post last modified:March 17, 2022
  • Reading time:5 mins read
You are currently viewing How to fetch Person Security Profile Config info?
How to fetch Person Security Profile Config info?
- How to fetch Person Security Profile Config info?
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 LinkedInFacebook, and Twitter to get updated with the latest content.