Cleanup the 'Provisioning' Accounts without touching 'Provisioned' Accounts

  Purpose:
  ========

This PL/SQL procedure will identity the users having both "provisioned" and "provisioning" accounts.
 Once such users are identified, It will cleanup the 'Provisioning' accounts without touching 'Provisioned' account.
 Customer will not see the duplicate account anymore on the UI.
 User can have one or more 'Provisioning' accounts.


Note :
 A) Access policy name (We don't touch other accounts provisioned by other AP).
 B) Resource Object name

Output :
It cleans up the records in UD Parent table, ORC, OIU,  OSI,OTI, OBI tables and Executed in a order to avoid referential integrity constraints. Also, we don't need to delete records in child table as there won't be any records because the account will be in 'Provisioning' status.

DECLARE
  V_SDKKEY SDK.SDK_KEY%type     := NULL;
  V_SDKNAME SDK.SDK_NAME%type   := null;
  V_ARRAY_USRKEYS OIM_TYP_NUMBERARR := OIM_TYP_NUMBERARR(); -- Will store USR_KEYS
  V_ARRAY_ORCKEYS OIM_TYP_NUMBERARR := OIM_TYP_NUMBERARR(); 
  V_ARRAY_OIUKEYS OIM_TYP_NUMBERARR := OIM_TYP_NUMBERARR(); 
    
  V_STRSQL varchar2(10000)      := null;                -- Will store the dynamic query
  V_OBJNAME OBJ.OBJ_NAME%type  := 'DC1';          --Resource object name
  V_APNAME POL.POL_NAME%type    := 'DCAcessPolicy';            --Access Policy name
  V_OBJKEY OBJ.OBJ_KEY%type     := null;                -- Will store the OBJ_KEY for the selected RO.
  V_POLKEY POL.POL_KEY%type    := null;
  V_USRKEY USR.USR_KEY%type    := null;
  V_ArrayKeys varchar2(2000)      := ''; 
begin

  -- Step-1 : Get the OBJ_KEY for the selected Resource Object.
  V_STRSQL := 'SELECT obj_key FROM obj WHERE obj_name= :1';
  dbms_output.put_line('v_strSql: ' || v_strSql);
  EXECUTE IMMEDIATE v_strSql INTO v_ObjKey USING V_OBJNAME;
  DBMS_OUTPUT.PUT_LINE(' Resource Object: ' || V_OBJNAME || ', OBJ_KEY is: ' || V_OBJKEY);
  
  
    -- Step-2 : Get the POL_KEY for the selected access policy having the configured

-- resource object .
  V_STRSQL := 'SELECT pol_key FROM pol WHERE pol_name= :1';
  dbms_output.put_line('v_strSql: ' || v_strSql);
  execute immediate V_STRSQL into V_POLKEY using V_APNAME;
  DBMS_OUTPUT.PUT_LINE(' Access Policy name is : ' || V_APNAME || ', POL_KEY is: ' || V_PolKey);
  
  
  -- Step-3 : Get the parent SDK name for the selected Resource Object.
  V_STRSQL := 'SELECT sdk.sdk_key, sdk.sdk_name 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 = :1';
  
  dbms_output.put_line('v_strSql: ' || v_strSql);
  EXECUTE IMMEDIATE v_strSql INTO v_SdkKey,v_SdkName USING v_ObjKey;
  DBMS_OUTPUT.PUT_LINE('Resource Object: ' || V_OBJNAME || ', Parent SDK_KEY is: ' || V_SDKKEY || ' and Parent SDK_NAME is: ' || V_SDKNAME);

  --Get the list of user having the duplicate account provisioned. 

--The users having both 'Provisioned'/'Enabled' and 'Provisioning' accounts.

SELECT USR_KEY BULK COLLECT INTO V_ARRAY_USRKEYS FROM (
SELECT OIU.USR_KEY from OIU,OBI,OBJ,OST, POL 
  where OIU.OBI_KEY = OBI.OBI_KEY  
  and OBI.OBJ_KEY =OBJ.OBJ_KEY
  and OBJ.OBJ_KEY = OST.OBJ_KEY  
  and OIU.OST_KEY =OST.OST_KEY 
  and OIU.POL_KEY = POL.POL_KEY 
  and  OBJ.OBJ_KEY =V_OBJKEY 
  and OIU.POL_KEY  = V_POLKEY   
  AND OST.OST_STATUS IN ('Provisioned', 'Enabled')
  INTERSECT
  SELECT OIU.USR_KEY  FROM OIU,OBI,OBJ,OST ,POL
  WHERE OIU.OBI_KEY   =OBI.OBI_KEY
  AND OBI.OBJ_KEY     =OBJ.OBJ_KEY
  AND OBJ.OBJ_KEY     =OST.OBJ_KEY
  AND OIU.OST_KEY     =OST.OST_KEY
  and OIU.POL_KEY     = POL.POL_KE
  and OIU.POL_KEY  =V_POLKEY
  and OBJ.OBJ_KEY    =V_OBJKEY
  AND OST.OST_STATUS IN ('Provisioning'));
  
 DBMS_OUTPUT.PUT_LINE('Executed the main query ');

 FOR IDX in 1 .. V_ARRAY_USRKEYS.COUNT
  LOOP
      V_USRKEY := V_ARRAY_USRKEYS(IDX);
      --For each the selected user, let's get the ORC_KEY and OIU_KEY for an account in 

--'provisioning' status
      
    V_STRSQL := 'select OIU.OIU_KEY,OIU.ORC_KEY  from OIU, OBI, OBJ, OST, POL 
         where OIU.OBI_KEY=OBI.OBI_KEY and OBI.OBJ_KEY=OBJ.OBJ_KEY
         and OBJ.OBJ_KEY=OST.OBJ_KEY and OIU.OST_KEY=OST.OST_KEY 
         and OIU.POL_KEY = POL.POL_KEY  and  OIU.POL_KEY  = ' || V_POLKEY || 
        ' AND OBJ.OBJ_KEY   = ' || V_OBJKEY || 
        ' and OST.OST_STATUS in (''Provisioning'')  and OIU.USR_KEY= ' || V_UsrKey;
       
         DBMS_OUTPUT.PUT_LINE('v_strSql: ' || V_STRSQL);
        execute immediate V_STRSQL bulk collect into V_ARRAY_OIUKEYS,V_ARRAY_ORCKEYS;
      
       DBMS_OUTPUT.PUT_LINE('User : ' || V_USRKEY || ' is having ' ||

 V_ARRAY_ORCKEYS.COUNT || ' Provisioning accounts. These accounts will be deleted now');
      
      FOR k in 1 .. V_ARRAY_ORCKEYS.COUNT
      LOOP
      DBMS_OUTPUT.PUT_LINE('V_ARRAY_ORCKEYS : ' || V_ARRAY_ORCKEYS(k));
          if (K != 1) then
              V_ARRAYKEYS := V_ARRAYKEYS || ''',''' || V_ARRAY_ORCKEYS(K) || '''';
                DBMS_OUTPUT.PUT_LINE('ORC KEYs: ' || V_ARRAYKEYS);
          ELSE
             V_ARRAYKEYS := V_ARRAYKEYS || '''' || V_ARRAY_ORCKEYS(K);
               DBMS_OUTPUT.PUT_LINE('ORC 1: ' || V_ARRAYKEYS);
          END IF;
       end LOOP;   
       
       
        DBMS_OUTPUT.PUT_LINE('ORC KEYs: ' || V_ARRAYKEYS);
        
  --delete from UD parent table. No need to delete the records in child table as the 

--entitltments will NOT be provisioned for an account which is 'Provisoning' status.
  
   V_STRSQL := 'DELETE FROM /*+ PARALLEL */' || V_SDKNAME || ' WHERE orc_key in (' || V_ARRAYKEYS || ') and pol_key=' || V_POLKEY;
   DBMS_OUTPUT.PUT_LINE('v_strSql: ' || V_STRSQL);
   execute immediate V_STRSQL;
    DBMS_OUTPUT.PUT_LINE(' Deleted the records in  table: ' || v_SdkName);
                 
  --Delete from OSI
    V_STRSQL := 'DELETE FROM /*+ PARALLEL */ OSI  WHERE orc_key in (' || V_ARRAYKEYS || ')';
     DBMS_OUTPUT.PUT_LINE('v_strSql: ' || V_STRSQL);
     execute immediate V_STRSQL;
     DBMS_OUTPUT.PUT_LINE(' Deleted the records in  table: OSI' );
    
    
    --Delete from oti
    V_STRSQL := 'DELETE FROM /*+ PARALLEL */ OTI  WHERE orc_key in (' || V_ARRAYKEYS || ') and OBJ_KEY=' || V_OBJKEY;
     DBMS_OUTPUT.PUT_LINE('v_strSql: ' || V_STRSQL);
     execute immediate V_STRSQL;
     DBMS_OUTPUT.PUT_LINE(' Deleted the records in  table: OTI');
    
    
      --Delete from obi
    V_STRSQL := 'DELETE FROM /*+ PARALLEL */ obi  WHERE orc_key in (' || V_ARRAYKEYS || ')';
    DBMS_OUTPUT.PUT_LINE('v_strSql: ' || V_STRSQL);
    execute immediate V_STRSQL;
   DBMS_OUTPUT.PUT_LINE(' Deleted the records in  table: OBI');
    
        --Delete from oiu
     V_STRSQL := 'DELETE FROM /*+ PARALLEL */ OIU  WHERE orc_key in (' || V_ARRAYKEYS || ')';
    DBMS_OUTPUT.PUT_LINE('v_strSql: ' || V_STRSQL);
    execute immediate V_STRSQL;
    DBMS_OUTPUT.PUT_LINE(' Deleted the records in  table: OIU');
   
   
          --Delete from orc
     V_STRSQL := 'DELETE FROM /*+ PARALLEL */ ORC  WHERE orc_key in (' || V_ARRAYKEYS || ')';
    DBMS_OUTPUT.PUT_LINE('v_strSql: ' || V_STRSQL);
    execute immediate V_STRSQL;
   DBMS_OUTPUT.PUT_LINE(' Deleted the records in  table: ORC ');
    Commit;
   DBMS_OUTPUT.PUT_LINE('******Completed the cleanup script*****: ');
  
  END LOOP;
END;