Can not Enable Authorization Init Block in OBIA 7.9.6.4 using OBIEE 12c

961460

Hi everyone!!

I try to migrate security from EBS to OBIEE 12c using the corresponding Authorization Init Block.

I use the following SQL

SELECT DISTINCT 'ROLES', RESPONSIBILITY_KEY

RESPONSIBILITY_NAME

FROM FND_USER, FND_USER_RESP_GROUPS, FND_RESPONSIBILITY

WHERE FND_USER.user_id=FND_USER_RESP_GROUPS.user_id

AND FND_USER_RESP_GROUPS.RESPONSIBILITY_ID = FND_RESPONSIBILITY.RESPONSIBILITY_ID

AND FND_USER_RESP_GROUPS.RESPONSIBILITY_APPLICATION_ID = FND_RESPONSIBILITY.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 UPPER(USER_NAME) = UPPER(':USER'))

It runs properly on SQLDeveloper, but somehow is not working on OBIEE.

The name of the roles are correct and matches those in EBS.

Is it support version 7.9.6.4 on OBIEE 12c?

What could be the problem?

Thanks