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')