Below is the sql query to find request justification for a user for already provisioned resource in OIM:
SELECT REQUEST.REQUEST_JUSTIFICATION FROM REQUEST
WHERE REQUEST_ID =(SELECT REQUEST_KEY FROM UD_ADUSER
WHERE UPPER(UD_ADUSER_USERID)=UPPER(?) //user login
AND ORC_KEY IN (SELECT OIU.ORC_KEY FROM OBJ,OBI,OIU,OST,USR
WHERE OBJ.OBJ_KEY=OBI.OBJ_KEY
AND OBI.OBI_KEY=OIU.OBI_KEY
AND OIU.USR_KEY=USR.USR_KEY
AND OIU.OST_KEY=OST.OST_KEY
AND OST.OBJ_KEY=OBJ.OBJ_KEY
AND OBJ.OBJ_NAME='Active Directory'
AND OST.OST_STATUS IN ('Enabled','Provisioned')));
