I have a question (which I have also raised with Support on Metalink) regarding the use of 'dynamic' HR security profiles. OBI, out of the box, is only designed to handle HR security based upon PER_PERSON_LIST being populated by the Security List Maintenance process, but if you use the custom security code or a few other bits of the security profile, then the PER_PERSON_LIST table is not populated.

I think we will probably need to create a custom function to call the HR_SECURITY package, but I wondered if anyone else had come across this problem and had a working solution?

I have put further details below:

In OBI security we are using the initialization block 'HR Organizations' to restrict the view of employee data based upon the HR security profile - the following extract from the OBI Security setup document explains this in more detail...

2.4.6.2 Implementation Steps for HR Org-Based Security for Oracle EBS
The sequence for HR org-based security for Oracle EBS is described below:
1. When a user logs in to Oracle BI Applications, the session variable below is set
automatically.
USER (System variable)
2. The Oracle BI Server gets the HR organizations corresponding to the USER from
the following tables:
– FND_USER_RESP_GROUPS
– FND_USER
– PER_SECURITY_PROFILES
– PER_SEC_PROFILE_ASSIGNMENTS
– PER_PERSON_LIST
Note: Before the PER_PERSON_LIST table can be used, you must ensure that
you have run the Oracle EBS HRMS Security List Maintenance process.
– PER_ALL_ASSIGNMENTS_F
3. The following session variable is set automatically:
HR_ORG (Row-wise variable)
The initialization block 'HR Organizations', which sets the value for this variable,
is shown below.
Initialization block -- 'HR Organizations'
The initialization block 'HR Organizations' sets value for variable HR_ORG using
the following SQL. The actual SQL query differs depending on whether Multiple
Security Group (MSG) is set up or not.
The following SQL should be used when MSG is not in place:
SELECT
DISTINCT 'HR_ORG'
,TO_CHAR(SEC_DET.ORGANIZATION_ID)
FROM
(
SELECT
'HR_ORG',
ASG.ORGANIZATION_ID
FROM
FND_USER_RESP_GROUPS URP
,FND_USER USR
,PER_SECURITY_PROFILES PSEC
,PER_PERSON_LIST PER
,PER_ALL_ASSIGNMENTS_F ASG
WHERE
URP.START_DATE < TRUNC(SYSDATE)
AND (CASE WHEN URP.END_DATE IS NULL THEN TRUNC(SYSDATE) ELSE TO_DATE(URP.END_
DATE) END) >= TRUNC(SYSDATE)
AND USR.USER_NAME = ':USER'
AND USR.USER_ID = URP.USER_ID
AND TRUNC(SYSDATE)
BETWEEN URP.START_DATE AND NVL(URP.END_DATE, HR_GENERAL.END_OF_TIME)
AND PSEC.SECURITY_PROFILE_ID = FND_PROFILE.VALUE_SPECIFIC('PER_SECURITY_
PROFILE_ID', URP.USER_ID, URP.RESPONSIBILITY_ID, URP.RESPONSIBILITY_
Integrating Data Security for Oracle EBS
Integrating Security for Oracle BI Applications 2-19
APPLICATION_ID)
AND PER.SECURITY_PROFILE_ID = PSEC.SECURITY_PROFILE_ID
AND PER.PERSON_ID = ASG.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE
AND URP.RESPONSIBILITY_ID = DECODE(FND_GLOBAL.RESP_ID,
-1, URP.RESPONSIBILITY_ID,
NULL, URP.RESPONSIBILITY_ID,
FND_GLOBAL.RESP_ID)
UNION
SELECT DISTINCT 'HR_ORG',
ORGANIZATION_ID
FROM PER_ALL_ASSIGNMENTS_F ASG,
FND_USER USR
WHERE ASG.PERSON_ID = USR.EMPLOYEE_ID
AND USR.USER_NAME = ':USER'
AND TRUNC(SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE
AND ASG.PRIMARY_FLAG = 'Y'
) SEC_DET

As you will see from this document extract, this expects to use the table PER_PERSON_LIST. This is ok when you have a static security model and the PER_PERSON_LIST is populated by the Security List Maintenance process. In many cases that works for us, however, the majority of HR security profiles we use are dynamic, for example, we restrict access to data based upon the organization assigned to the employee. In EBS, the security is dynamically applied when the user logs in and the access is continually assessed by the HR_SECURITY package.

I should add also, that we use other HR Security Profile features such as restricted lists of organizations, payrolls, and the custom code restriction.

The problem is that when a user who normally accesses a dynamic security profile tries to access data through OBI, they currently do not see any employee data because there are no records in the PER_PERSON_LIST for their security profile