OIM Frequently used sql queries

• Change the task status in OIM through sql query -  To change the status of "Create User" task to "C" where it's in rejected state in Active directory work flow then you can use the below query for reference :

Update SCH SET SCH.SCH_STATUS='C' where SCH.SCH_KEY =(

SELECT OSI.SCH_KEY

FROM OSI,SCH,STA,MIL,TOS,PKG,OIU,USR,OBJ,OST WHERE OSI.MIL_KEY=MIL.MIL_KEY

AND SCH.SCH_KEY=OSI.SCH_KEY

AND STA.STA_STATUS=SCH.SCH_STATUS

AND TOS.PKG_KEY=PKG.PKG_KEY

AND MIL.TOS_KEY=TOS.TOS_KEY

AND OIU.USR_KEY=USR.USR_KEY

AND OIU.OST_KEY=OST.OST_KEY

AND OST.OBJ_KEY=OBJ.OBJ_KEY

AND OSI.ORC_KEY=OIU.ORC_KEY

AND OBJ.OBJ_NAME='AD User'

AND STA.STA_BUCKET = 'Rejected'

AND MIL.MIL_NAME='Create User');

Note : It is not a recommended way to change the task status directly through SQL query.

• SQL query to find out all users who don't have particular resource provisioned :

SQL query to find out all users who don't have particular resource provisioned :
SELECT DISTINCT USR.USR_LOGIN, USR_STATUS FROM USR WHERE USR_STATUS ='Active' and USR.USR_KEY
NOT IN (SELECT Distinct OIU.USR_KEY FROM OIU where OIU.APP_INSTANCE_KEY=5);

Note - Change the app instance Key according to your need.

• SQL query to find out all users who don't have particular resource : With respect Resource object (e.g. OID)
  

SELECT USR_LOGIN FROM USR WHERE USR_LOGIN NOT IN (SELECT USR_LOGIN
FROM OST,OIU,OBJ,USR,OBI
WHERE OIU.OST_KEY = OST.OST_KEY AND OBJ.OBJ_KEY=OBI.OBJ_KEY
AND OIU.USR_KEY=USR.USR_KEY AND OST_STATUS IN ('PROVISIONED','DISABLED','ENABLED','PROVISIONING')
AND OIU.OBI_KEY=OBI.OBI_KEY AND OBJ.OBJ_NAME LIKE 'OID USER')

• SQL query to find out all process task count with respect to one object

SQL query to find out all process task count with respect to one object
SELECT COUNT(*) FROM MIL WHERE TOS_KEY=(SELECT TOS_KEY FROM TOS WHERE PKG_KEY=(SELECT PKG_KEY FROM PKG WHERE PKG_NAME='eBusiness Suite User'));

Note - Change the PKG_NAME as per need

• SQL query to find recon status based on sysdate

SELECT COUNT(RE_STATUS),RE_STATUS
FROM RECON_EVENTS WHERE TO_CHAR(RE_CREATE,'YYYY-MM-DD') = TO_CHAR(SYSDATE , 'YYYY-MM-DD') AND
(RE_STATUS  IN ('Data Validation Failed','Creation Succeeded','Update Succeeded','Event Received','No User Match Found') OR RE_STATUS IS NULL) GROUP BY RE_STATUS;

• Updating OIM Process Form Version of a resource object instance through SQL query
  

This query is used to update the form version in one shot. You can also use OIM's Form Version Control Utility to the same.

UPDATE $FORMNAME SET $FORMNAME._VERSION =
(SELECT SDK_LATEST_VERSION FROM sdk WHERE lower(SDK_NAME) = lower('$FORMNAME'));


Replace $FORMNAME with actual value e.g. UD_ADUSER.The subquery gets the latest process form version, which is the most recent process form version created, of a specific form. The SDK table contains information of every resource object's process form. The SDK_LATEST_VERSION column stores the actual latest process form version number.

• Changing the package name in OIM adapter via SQL query
  

Let say if you deploy a Java tasks with multiple methods in OIM and after sometime you feel that the package name in java class is not appropriate and you need to change that. So there are two ways to deploy jar again and do the mapping manually. But if you have DB access then after jar deployment you can do the mapping update via SQL query.

Get  all ADJ_KEY where the package name(let say : com.abc.xyz) is old.

SELECT ADJ_KEY FROM ADJ WHERE ADJ_API_NAME='com.abc.xyz';

then you have to update 3 columns.

- ADJ_METHOD_DISPLAY
- ADJ_API_NAME
- ADJ_CONSTRUCTOR

do the update query on these column on the basis of ADJ_KEY.