IAM Works

1. ORG_USER_MEMBERSHIPS --- Users Organizations details The below query give the list of users belongs to organization XXXXX select USR_LOGIN,USR_DISPLAY_NAME,ACT_NAME as "ORGANIZATION" from ORG_USER_MEMBERSHIPS,usr,act where ORG_USER_MEMBERSHIPS.act_key = act.act_key and usr.usr_key =ORG_USER_MEMBERSHIPS.usr_key and ACT_NAME = 'XXXXX' order by ACT_NAME desc; 2. USR -  User and Users Manager login in single query select u.USR_KEY,u.USR_LOGIN,u.USR_DISPLAY_NAME, u1.USR_KEY as "Manager USR_KEY",u1.USR_LOGIN as "Manager Login",u1.USR_DISPLAY_NAME as "Manager DisplayName" from usr u join usr u1 on u1.USR_KEY = u.USR_MANAGER_KEY --where u.usr_login ='XXXX'; for XXXX user manager details 3. USR -- The below query gives you list of users whose manager is XXXXXXX select u1.USR_KEY,u1.USR_LOGIN,u1.USR_DISPLAY_NAME,u.USR_KEY as "Manager Key",u.USR_LOGIN as "Manager Login",u.USR_DISPLAY_NAME as ...

Below query helps us in getting User login and Manager login in single query Basically in OIM USR table we have user user key, user login and manager key fields, so if we want User login and Manager login in the single query we can use below query. select u.USR_KEY,u.USR_LOGIN,u.USR_DISPLAY_NAME, u1.USR_KEY as "Manager USR_KEY",u1.USR_LOGIN as "Manager Login",u1.USR_DISPLAY_NAME as "Manager DisplayName" from usr u join usr u1 on u1.USR_KEY = u.USR_MANAGER_KEY --where u.usr_login ='User login  Id';