Oracle® Business Intelligence Applications Installation and Configuration Guide > Integrated Security for Oracle BI Applications > Implementing Security With Oracle EBS >
Initialization Blocks
This section describes the initialization blocks available in Oracle BI Applications. Authorization Initialization Block
This initialization block sets values for the session variable GROUP. Out of the box, the SQL is tuned for Oracle's Siebel Applications, and the connection pool is Siebel OLTP. Select 'GROUP', R.NAME
from VALUEOF(TBO).S_RESP R, VALUEOF(TBO).S_PER_RESP P, VALUEOF(TBO).S_USER U
where U.LOGIN=Upper(':USER') and U.ROW_ID=P.PER_ID and P.RESP_ID=R.ROW_ID
UNION
select 'GROUP', CASE VALUEOF(NQ_SESSION.HIER_LEVEL)
WHEN 0 THEN 'Hierarchy Level (Base)'
when 1 then 'Hierarchy Level 1'
when 2 then 'Hierarchy Level 2'
when 3 then 'Hierarchy Level 3'
when 4 then 'Hierarchy Level 4'
when 5 then 'Hierarchy Level 5'
when 6 then 'Hierarchy Level 6'
when 7 then 'Hierarchy Level 7'
when 8 then 'Hierarchy Level 8'
When 9 then 'Hierarchy Level (Top)'
ELSE 'NOGROUP' END from VALUEOF(TBO).S_DUAL
To make it applicable for Oracle EBS, the administrator has to change the connection pool to Oracle EBS OLTP and change the SQL to the following: select DISTINCT RESPONSIBILITY_NAME from
FND_USER ,FND_USER_RESP_GROUPS, FND_RESPONSIBILITY_VL
where
FND_USER.user_id=FND_USER_RESP_GROUPS.user_id
and FND_USER_RESP_GROUPS.RESPONSIBILITY_ID = FND_RESPONSIBILITY_VL.RESPONSIBILITY_ID
AND FND_USER_RESP_GROUPS.RESPONSIBILITY_APPLICATION_ID = FND_RESPONSIBILITY_VL.APPLICATION_ID AND
FND_USER_RESP_GROUPS.START_DATE < SYSDATE and
(case WHEN FND_USER_RESP_GROUPS.END_DATE is null then SYSDATE else TO_DATE(FND_USER_RESP_GROUPS.end_Date) END) >= SYSDATE
and FND_USER.user_id = = (SELECT USER_ID FROM FND_USER WHERE USER_NAME = ':USER')
Operating Unit Org Initialization Block
The initialization block sets the value for the variable OU_ORG, using the following SQL: SELECT DISTINCT 'OU_ORG', TO_CHAR(PER_ORGANIZATION_LIST.ORGANIZATION_ID) FROM PER_ORGANIZATION_LIST, (SELECT FND_PROFILE.VALUE_SPECIFIC('XLA_MO_SECURITY_PROFILE_LEVEL', USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID) PROFILE_ID FROM (SELECT USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID FROM FND_USER_RESP_GROUPS WHERE START_DATE < SYSDATE AND (CASE WHEN END_DATE IS NULL THEN SYSDATE ELSE TO_DATE(END_DATE) END) >= SYSDATE AND USER_ID = (SELECT USER_ID FROM FND_USER WHERE USER_NAME = ':USER') ) ) WHERE PER_ORGANIZATION_LIST.SECURITY_PROFILE_ID = PROFILE_ID UNION SELECT DISTINCT 'OU_ORG', FND_PROFILE.VALUE_SPECIFIC('ORG_ID', USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID) ORGANIZATION_ID FROM (SELECT USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID FROM FND_USER_RESP_GROUPS WHERE START_DATE < SYSDATE AND (CASE WHEN END_DATE IS NULL THEN SYSDATE ELSE TO_DATE(END_DATE) END) >= SYSDATE AND USER_ID = (SELECT USER_ID FROM FND_USER WHERE USER_NAME = ':USER')
Inventory Organizations Initialization Block
The initialization block sets the value for the variable INV_ORG, using the following SQL: SELECT DISTINCT 'INV_ORG', BIS_ORGANIZATIONS_V.ID FROM FND_USER_RESP_GROUPS, BIS_ORGANIZATIONS_V WHERE FND_USER_RESP_GROUPS.RESPONSIBILITY_ID = BIS_ORGANIZATIONS_V.RESPONSIBILITY_ID AND FND_USER_RESP_GROUPS.START_DATE < SYSDATE AND (CASE WHEN FND_USER_RESP_GROUPS.END_DATE IS NULL THEN SYSDATE ELSE TO_DATE(FND_USER_RESP_GROUPS.END_DATE) END) >= SYSDATE AND FND_USER_RESP_GROUPS.USER_ID = (SELECT USER_ID FROM FND_USER WHERE USER_NAME = ':USER')
|