SQL Query to get User Accounts Statuses

Version: Oracle Identity Manager 11.1.1.5.0
Description: Given here is a SQL query to get the statuses of each user's accounts (resource object instances).

Query to get the statuses of all resource object instances in OIM.
Given below are important tables used in the query. USR = contains data on all OIM Users
OST = contains the possible statuses for each object in OIM
OBJ = objects include resources and organizations
OBI = object instances
OIU = object instances to users
SVR = IT resource

SELECT
    USR.USR_LOGIN,
    USR.USR_KEY,
    OST.OST_STATUS,
    OBJ.OBJ_NAME,
    OIU.OBI_KEY
FROM OST INNER JOIN
     (OBJ INNER JOIN
            (OBI INNER JOIN 
                (USR INNER JOIN OIU ON OIU.USR_KEY = USR.USR_KEY)
            ON OBI.OBI_KEY = OIU.OBI_KEY)
    ON OBJ.OBJ_KEY = OBI.OBJ_KEY)
ON OST.OST_KEY = OIU.OST_KEY;

Query to get the statuses of each instances for a specific resource object in OIM. This includes the IT resources which can be useful when a resource object has multiple IT resources.

ACCEPT formTableName varchar prompt 'Enter form table name: ';
SELECT
    USR.USR_LOGIN,
    USR.USR_KEY,
    OST.OST_STATUS,
    OBJ.OBJ_NAME,
    SVR.SVR_NAME,
    OIU.OBI_KEY
FROM SVR INNER JOIN
    ("&formTableName" INNER JOIN
        (OST INNER JOIN
            (OBJ INNER JOIN
                (OBI INNER JOIN
                    (USR INNER JOIN OIU ON OIU.USR_KEY = USR.USR_KEY)
                ON OBI.OBI_KEY = OIU.OBI_KEY)
            ON OBJ.OBJ_KEY = OBI.OBJ_KEY)
        ON OST.OST_KEY = OIU.OST_KEY)
    ON "&formTableName".ORC_KEY = OIU.ORC_KEY)
ON "&formTableName"."&formTableName._SERVER" = SVR.SVR_KEY;