Script to Recover stale composite instances

declare 
  -- Script Parameters:
  
  -- Composite Name
  cmpstName VARCHAR2(200) := 'TestZComposite';
  -- Composite Version
  cmpstVersion VARCHAR(20) := '1.2';
  
  -- Composite old distinguished name
  old_composite_dn VARCHAR2(500) := 'default/TestZComposite!1.2*soa_87ad6db9-5a3e-4e9a-8940-f9b2885121b4';
  -- Composite new distinguished name (new mds label => soa_51ea029f-6b84-40b1-af8d-ba9a0fa22a97)
  new_composite_dn VARCHAR2(500) := 'default/TestZComposite!1.2*soa_51ea029f-6b84-40b1-af8d-ba9a0fa22a97';
  -- Composite new MDS label
  new_mds_label VARCHAR2(200) := 'soa_51ea029f-6b84-40b1-af8d-ba9a0fa22a97';  
  
begin
  -- Step 1
  -- update compositedn (new mds label) and reactivate stale composites 
  -- (state 64 = stale, state 0 = running)
  update composite_instance ci
  set ci.composite_dn = new_composite_dn,
      ci.state = CASE
                   WHEN (ci.state is not null and ci.state = 64) 
                     THEN 0
                     ELSE ci.state                    
                 END
  where ci.composite_dn = old_composite_dn; 
  
  -- Step 2
  -- update mds label and compositedn in triggered messages
  update dlv_message dlvm
    set dlvm.composite_label = new_mds_label,
        dlvm.res_subscriber = CASE
                                 WHEN dlvm.res_subscriber IS NOT NULL 
                                     THEN replace(dlvm.res_subscriber, 
                                                  old_composite_dn, 
                                                  new_composite_dn)
                                     ELSE dlvm.res_subscriber                            
                               END
  where dlvm.composite_name = cmpstName and 
        dlvm.composite_revision = cmpstVersion;
  
  -- Step 3
  -- reactivate callbacks and update mds label and compositedn 
  -- (state 3 = stale/cancelled, state 0 = unresolved)
  update dlv_subscription dlv 
  set dlv.composite_label = new_mds_label,
      dlv.subscriber_id = replace(dlv.subscriber_id, old_composite_dn, new_composite_dn),
      dlv.state = CASE
                   WHEN (dlv.state IS NOT NULL AND dlv.state = 3)
                       THEN 0
                       ELSE dlv.state             
                  END
  where dlv.composite_name = cmpstName and
        dlv.composite_revision = cmpstVersion;
  
  -- Step 4
  -- reactivate references and update compositedn 
  -- (state 0 = successful, state 6 = stale)
  update reference_instance t 
  set t.state = 0,
      t.composite_dn = new_composite_dn
  where t.composite_dn = old_composite_dn and t.state = 6;      
  
  -- Step 5
  -- reactivate bpel/bpmn processes and update compositedn 
  -- (state 1 = open running, state 9 = closed stale)
  update cube_instance cui 
  set   cui.composite_label = new_mds_label,
        cui.state = CASE
                       WHEN (cui.state IS NOT NULL AND cui.state = 9)
                           THEN 1
                           ELSE cui.state             
                     END
  where cui.composite_name = cmpstName and cui.composite_revision = cmpstVersion;
  
  -- Step 6
  -- reactivate human tasks and update compositedn
  UPDATE WFTASK TBL_NOTFS
    SET  TBL_NOTFS.STATE = CASE
                         WHEN (TBL_NOTFS.VERSIONREASON ='TASK_VERSION_REASON_INITIATED') 
                              THEN 'ASSIGNED'
                         WHEN (TBL_NOTFS.VERSIONREASON ='TASK_VERSION_REASON_WITHDRAWN') 
                              THEN 'WITHDRAWN'
                         WHEN (TBL_NOTFS.VERSIONREASON ='TASK_VERSION_REASON_REASSIGNED') 
                              THEN 'ASSIGNED'
                         WHEN (TBL_NOTFS.VERSIONREASON ='TASK_VERSION_REASON_ALERTED') 
                              THEN 'ALERTED'
                         WHEN (TBL_NOTFS.VERSIONREASON ='TASK_VERSION_REASON_SUSPENDED') 
                              THEN 'SUSPENDED'
                         WHEN (TBL_NOTFS.VERSIONREASON ='TASK_VERSION_REASON_ERROR') 
                              THEN 'ERRORED'
                         WHEN (TBL_NOTFS.VERSIONREASON ='TASK_VERSION_REASON_COMPLETED') 
                              THEN NULL
                         ELSE TBL_NOTFS.STATE                            
                       END,
         TBL_NOTFS.COMPOSITEDN = new_composite_dn,
         TBL_NOTFS.MDSLABEL = new_mds_label
   WHERE TBL_NOTFS.TASKDEFINITIONNAME is not null
     AND TBL_NOTFS.STATE = 'STALE'
     AND TBL_NOTFS.COMPOSITENAME= cmpstName
     and TBL_NOTFS.COMPOSITEVERSION = cmpstVersion;
  
end

Ref: 
http://middlewarebylink.wordpress.com/2012/10/29/how-to-recover-stale-composite-instances-and-their-components/
https://blogs.oracle.com/ateamsoab2b/entry/list_of_all_states_from