Query to get OIM Provsioned/provisioning Account details

1.  Query to get account details with USR_LOGIN as where condition.


SELECT OIU_KEY,
  OIU.ORC_KEY,
  USR.USR_KEY,
  USR_LOGIN,
  OBJ_NAME,
  OST_STATUS
FROM OIU,
  OST,
  OBI,
  OBJ,
  USR
WHERE OIU.USR_KEY=USR.USR_KEY
AND OIU.OST_KEY  =OST.OST_KEY
AND OIU.OBI_KEY  =OBI.OBI_KEY
AND OBJ.OBJ_KEY  =OBI.OBJ_KEY
AND USR.USR_LOGIN=UPPER('TUSER13')
ORDER BY OIU_KEY;


2.  Display all users accounts.

SELECT *
FROM USR,
  OBI,
  OIU,
  OBJ,
  OST OST
WHERE USR.USR_KEY =OIU.USR_KEY
AND OIU.OBI_KEY   =OBI.OBI_KEY
AND OIU.OST_KEY   = OST.OST_KEY
AND OBI.OBJ_KEY   =OBJ.OBJ_KEY;


3.  Query to get account details based on specif connectors.
Ex: OID Connector accounts

SELECT USR.USR_KEY,
  USR.USR_LOGIN,
  UD_OID_USR.UD_OID_USR_KEY,
  OST.OST_STATUS,
  OBJ.OBJ_NAME
FROM USR,
  UD_OID_USR,
  OIU,
  OBI,
  OBJ,
  OST,
  ORC
WHERE USR.USR_LOGIN =UD_OID_USR.UD_OID_USR_USERID -- matching condition needs to be extended based on recon profile
AND USR.USR_KEY     =OIU.USR_KEY
AND OIU.OBI_KEY     =OBI.OBI_KEY
AND OBI.OBJ_KEY     =OBJ.OBJ_KEY
AND OIU.OST_KEY     =OST.OST_KEY
AND OST.OST_STATUS IN ('Provisioned', 'Enabled', 'Disabled')
  --and obj.obj_name='OID User'
AND OIU.ORC_KEY=ORC.ORC_KEY
  --AND ORC.ORC_KEY=UD_OID_USR.ORC_KEY