In one of the most recent BI Application (OBIA) projects, there is a requirement to read Organization based security from EBS and apply this on BI Applications. To this end, I found that there is a simple view available in EBS which returns all the Organizations that a user has access to. This is ORG_ACCESS_V.
Using this view, you can use a row-wise initialized variable to apply security. An example of the SQL used in the initialization block is below:
SELECT SEC_ORG_CODE var_name, org.organization_code var_val
FROM
apps.fnd_user_resp_groups_direct furg,
applsys.fnd_user fu,
apps.org_access_v org
WHERE
furg.user_id = fu.user_id
AND furg.responsibility_id = org.responsibility_id
AND furg.start_date < SYSDATE
AND NVL (furg.end_date, SYSDATE) >= SYSDATE
AND (fu.user_name) = UPPER(':USER') --OBIEE User
The EBS version I have tried this on is R12.2.
.svg)
.svg)