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 tatus 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 (?,?);