Configure Agents with OBIEE 12c and EBS Cookie Integration

1. Create a new session initialization block called 'EBS Initialize Session Cookies'

2. Click on 'Edit Data Source' and input the default Initialization String as:

select '-1', '0' from dual;

3. Select the Oracle EBS OLTP Connection Pool

4. Press 'Edit Target Data' button and create 2 variables: ICX_SESSION_COOKIE and ACF

5. Define session variable: ICX_SESSION_COOKIE:

6. Check the "Enable any user to set the value" checkbox

7. Check the 'Security Sensitive" checkbox

8. Set the Default Initializer to:

'-1'

9. Define session variable: ACF

10. Check the "Enable any user to set the value" checkbox

11. Check the "Security Sensitive" checkbox

12. Set the Default Initializer to:

'0'

13. Redefine the 'Execute on connect' query for the Oracle EBS OLTP Connection Pools as follows:

Declare /* valueof (NQ_SESSION.ACF) */ v_icx_session_cookie varchar2(100) := 'valueof(NQ_SESSION.ICX_SESSION_COOKIE)'; begin if v_icx_session_cookie <> '-1' then APP_SESSION.validate_icx_session(v_icx_session_cookie); end if; end;

14. Update the session variable initialization block 'EBS Security Context' query to be:

select FND_GLOBAL.RESP_ID,
FND_GLOBAL.RESP_APPL_ID,
FND_GLOBAL.SECURITY_GROUP_ID,
FND_GLOBAL.RESP_NAME,
FND_GLOBAL.USER_ID,
FND_GLOBAL.EMPLOYEE_ID,
NVL(FND_GLOBAL.USER_NAME,'IMPERSONATOR') from dual
where NVL(FND_GLOBAL.USER_NAME,'IMPERSONATOR') != 'GUEST'
NQS_PASSWORD_CLAUSE(and FND_GLOBAL.USER_NAME != 'GUEST')NQS_PASSWORD_CLAUSE

15. Restart OBIEE services.