CLEAN UP DUPLICATED INSTANCES IN PROVISIONED STATUS

--Query to get Accounts which are duplicate and ACCOUNT_TYPE='other' 
SELECT USR.USR_KEY, OIU.OIU_KEY, OIU.ORC_KEY, OBJ.OBJ_KEY, OIU.ACCOUNT_TYPE, 
    OST.OST_STATUS FROM USR, OIU, OBI, OBJ, OST 
WHERE 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 ( USR.USR_KEY,OBJ.OBJ_KEY ) IN ( -- query to get duplicate user keys 
and object keys 
SELECT USR.USR_KEY, OBJ.OBJ_KEY FROM USR, OIU, OBI, OBJ, OST 
 WHERE 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') -- Current status of Account. 'Provisioned','Enabled' 
   AND OBJ.OBJ_NAME = 'LDAP User'  -- Object name 
   AND OIU.ACCOUNT_TYPE IN ('primary','other') 
   GROUP BY USR.USR_KEY, OBJ.OBJ_KEY HAVING COUNT(*) > 1 ) 
   AND OIU.ACCOUNT_TYPE = 'other';--Updating Provisioned State to Revoked state of Accounts which are duplicate 
   and ACCOUNT_TYPE='other' -- Change OBJ_KEY and OIU_KEY as per above select query result. 
UPDATE OIU SET OST_KEY = (SELECT OST_KEY FROM OST WHERE OBJ_KEY = 8 -- Pass the OBJ_KEY from above SQL query. 
   AND OST_STATUS = 'Revoked') WHERE OIU_KEY IN (67550,65351,67548); --Pass OIU_KEY of Above select query here. 

Query to remove Entitlements/Child data from Accounts which are going to be 
Moved to status Revoked. 
-- Step-1 : Get the parent SDK name for the selected Resource Object. 
SELECT SDK.SDK_KEY as  PARENT_KEY, SDK.SDK_NAME as PARENT_NAME, SDK.SDK_ACTIVE_VERSION as PARENT_VERSION 
FROM OBJ, PKG, TOS, SDK WHERE OBJ.OBJ_KEY = PKG.OBJ_KEY 

AND TOS.PKG_KEY = PKG.PKG_KEY

AND TOS.SDK_KEY = SDK.SDK_KEY

AND SDK.SDK_TYPE = 'P'

AND OBJ.OBJ_KEY = 7; --- Obj_key to get Child Table info. 
-- Step 2 Get the child table names for the selected Resource Object.
SELECT SDH.SDH_CHILD_KEY as CHILD_KEY, SDK.SDK_NAME as CHILD_NAME, SDK.SDK_ACTIVE_VERSION 
FROM SDH SDH, SDK SDK WHERE SDK.SDK_KEY = SDH.SDH_CHILD_KEY 

AND SDH_PARENT_KEY =?  -- Parent_key from above query 
AND SDH_PARENT_VERSION= ? ; -- parent active version value. 
-- Query to check Entitlement data in Parent and Child tables. 
 SELECT  * FROM  CHILD_NAME WHERE ORC_KEY IN (? ,?);   
 SELECT  * FROM  PARENT_NAME WHERE ORC_KEY IN (?,?);    
 --Delete query to remove entitlement from Accounts. 
 DELETE FROM /*+ PARALLEL */ CHILD_NAME  WHERE orc_key in (?,?); 
 DELETE FROM /*+ PARALLEL */ PARENT_NAME  WHERE orc_key in (?,?);