Sql scripts to know roles assigned to FND user for viewing IB OAF screen

--To check User is linked to which all roles in CRM

select b.* ,A.PRINCIPAL_NAME--into count1
  from apps.jtf_auth_principal_maps c,
       apps.jtf_auth_principals_b a,
       apps.jtf_auth_domains_b d,
       apps.jtf_auth_principals_b b
  where a.principal_name LIKE 'SF%' and a.is_user_flag=1
    and a.jtf_auth_principal_id=c.jtf_auth_principal_id
    --and b.principal_name=U_ROLE_NAME
    and b.is_user_flag=0
    and b.jtf_auth_principal_id=c.jtf_auth_parent_principal_id
    and d.domain_name='CRM_DOMAIN'

Here Principal Name  is FND user name .

--TO CHECK IF user has been assigned any roles or not
In order to View IB Jsp page through  Oracle Installed Base Admin , given user should have CSI_NORMAL_USER role assigned , else while trying to invoke page we will receive error .

To check if user has been assigned to role or not we can run the SQL as below .

SELECT principal_name
  FROM jtf_auth_principals_b
 WHERE jtf_auth_principal_id IN
          (SELECT jtf_auth_principal_id
             FROM JTF_AUTH_PRINCIPAL_MAPS
            WHERE jtf_auth_parent_principal_id IN
                     (SELECT jtf_auth_principal_id
                        FROM jtf_auth_principals_b
                       WHERE PRINCIPAL_NAME = 'CSI_NORMAL_USER'))