Recently we encountered problems with cases that are related to a composite that somehow got corrupted in the MDS. This was possibly due to a failing deployment, which suggests a bug, since you don't want to have composite artifacts of an earlier version removed only after a succesful deployment of the new version.

The particular composite was listed in the deployed composites list, but it lacked the artifacts and therefor marked as invalid. Using the tip in this blog I was able to remove the composite. In fact it comes down to do an export of the MDS, openup the zip and browse to the folder ‘deployed—composites’. This contains an xml file named ‘deployed-composites.xml’. This gives a summary of deployed composites. Delete the entry of the composite you want to have removed. Besides the xml file, there is a folder per soa-partition with a sub-folder per composite containing the artefacts. In our case however the particular composite was stated in the deployed-composites.xml but did not have a folder here. If the folder consists removed it. Pack the zip again (or if you use a tool like TotalCommander, 7-Zip, WinRar or the like, you could do the removal directly from the tool. The resulting zip can then be imported. After a server restart the composite version is removed.

So far so good, however, we still had the composite instances and the cases. Well the flow-instances  can be purged from Enterprise Manager. It turned out however that we still had cases in the database, without flow instances. You could check this with the query:

select count(*) from cm_case cse join sca_partition ptn on ptn.id = cse.sca_partition_id where not exists (select fie.flow_id from sca_flow_instance fie where fie.flow_id = cse.flow_id);

This got me 213 cases. Possibly this is kind of designed, however we could get not into the caselist in the workspace without errors anymore. The caselist was empty, but we got a list of errors.
In the diagnostics log we got errors like:

 
[2015-01-19T14:34:37.764+01:00] [bpm_server1] [ERROR] [] [oracle.bpm.casemgmt.instance] [tid: [ACTIVE].ExecuteThread: '14' for queue: 'weblogic.kernel.Default (self-tuning)'] [userId: hans] [ecid: 60f546bd-50c5-445a-9a6d-1a72f364d5a9-00004adc,0] [APP: soa-infra] [DSID: 0000Kg2NM3f5uX05zzS4yW1KjBH400000x] Exception[[
BPM-72802

Missing resource bundle in project.
The resource bundle {2} was not found in case project identified by metadata Id default/{composite-name}!1.1-MA/{case-name} for nl locale .
Create a resource bundle for nl locale in project and re-deploy the project to server.

 at oracle.bpm.casemgmt.resource.Casei18nUtil.getCaseResourceBundle(Casei18nUtil.java:142)
 at oracle.bpm.casemgmt.resource.Casei18nUtil.getValue(Casei18nUtil.java:55)
 at oracle.bpm.casemgmt.resource.Casei18nUtil.getValue(Casei18nUtil.java:40)
 at sun.reflect.GeneratedMethodAccessor1371.invoke(Unknown Source)
...

I apparently have cases that lack composite instances and I indeed removed the composite artifacts, because of the invalid composite.

To solve this I created the following delete script:

declare
  cursor c_cse 
  is select cse.case_id 
     ,      cse.flow_id
     ,      cse.sca_partition_id
     ,      cse.partition_date
     ,      ptn.name partition_name
     from cm_case cse 
     join sca_partition ptn on ptn.id = cse.sca_partition_id
     where not exists ( select flow_id from sca_flow_instance fie where fie.flow_id = cse.flow_id);
   --  and flow_id = 20006;
  procedure pl(p_text in varchar2)
  is
  begin
    dbms_output.put_line(p_text);
  end;
  procedure del_case_header(p_flow_id in number)
  is
    cursor c_hdr 
    is SELECT ID,
              CASE_NUMBER,
              TITLE,
              SHORT_SUMMARY,
              LONG_SUMMARY,
              CASE_NAMESPACE,
              CASE_DEFINITION_ID,
              CASE_DEFINITION_NAME,
              composite_name, 
              composite_version
        FROM CM_CASE_HEADER hdr
        where hdr.flow_id = p_flow_id;
    r_hdr c_hdr%rowtype;
  begin
   open c_hdr;
   fetch c_hdr into r_hdr;
   close c_hdr;
    pl('Delete case header for flow_id '||p_flow_id||' case_number '||r_hdr.case_number||', from definition: '||r_hdr.CASE_DEFINITION_NAME||', composite_name: '||r_hdr.composite_name ||', composite_version: '||r_hdr.composite_version||'.');
    delete from cm_case_header where flow_id = p_flow_id;
  end;
  procedure del_case_data(p_flow_id in number)
  is
    l_count number;
  begin
    select count(*) into l_count from cm_case_data where flow_id = p_flow_id;
    pl('Delete '||l_count||' case_data rows for flow_id '||p_flow_id||'.');
    delete from cm_case_data where flow_id =  p_flow_id;
  end;
  procedure del_case_object(p_flow_id in number)
  is
    l_count number;
  begin
    select count(*) into l_count from cm_case_object where flow_id =  p_flow_id;
    pl('Delete '||l_count||' case_object rows for flow_id '||p_flow_id||'.');
    delete from cm_case_object where flow_id =  p_flow_id;
  end;
  procedure del_case_document(p_flow_id in number)
  is
    l_count number;
  begin
    select count(*) into l_count from cm_case_document where flow_id =  p_flow_id;
    pl('Delete '||l_count||' case_document rows for flow_id '||p_flow_id||'.');
    delete from cm_case_document where flow_id =  p_flow_id;
  end;
  procedure del_case_milestones(p_flow_id in number)
  is
    l_count number;
  begin
    select count(*) into l_count from cm_case_milestone where flow_id =  p_flow_id;
    pl('Delete '||l_count||' case_milestone rows for flow_id '||p_flow_id||'.');
    delete from cm_case_milestone where flow_id =  p_flow_id;
  end;
  procedure del_case_events(p_flow_id in number)
  is
    l_count number;
  begin
    select count(*) into l_count from cm_case_event where flow_id =  p_flow_id;
    pl('Delete '||l_count||' case_event rows for flow_id '||p_flow_id||'.');
    delete from cm_case_event where flow_id =  p_flow_id;
  end;
  procedure del_case_flex_fields(p_flow_id in number)
  is
    l_count number;
  begin
    select count(*) into l_count from cm_case_flex_field where flow_id =  p_flow_id;
    pl('Delete '||l_count||' case_flex_field rows for flow_id '||p_flow_id||'.');
    delete from cm_case_flex_field where flow_id =  p_flow_id;
  end;
   procedure del_case_stake_holders(p_flow_id in number)
  is
    l_count number;
  begin
    select count(*) into l_count from cm_case_stake_holder where flow_id =  p_flow_id;
    pl('Delete '||l_count||' case_stake_holder rows for flow_id '||p_flow_id||'.');
    delete from cm_case_stake_holder where flow_id =  p_flow_id;
  end;
  procedure del_case_stake_holder_members(p_flow_id in number)
  is
    l_count number;
  begin
    select count(*) into l_count from cm_case_stake_holder_member where flow_id =  p_flow_id;
    pl('Delete '||l_count||' case_stake_holder_member rows for flow_id '||p_flow_id||'.');
    delete from cm_case_stake_holder_member where flow_id =  p_flow_id;
  end;
  procedure del_case_comments(p_flow_id in number)
  is
    l_count number;
  begin
    select count(*) into l_count from cm_comment where flow_id =  p_flow_id;
    pl('Delete '||l_count||' case_comment rows for flow_id '||p_flow_id||'.');
    delete from cm_comment where flow_id =  p_flow_id;
  end; 
  procedure del_fabric_header_property(p_flow_id in number)
  is
    l_count number;
  begin
    select count(*) into l_count from cm_fabric_header_property where flow_id =  p_flow_id;
    pl('Delete '||l_count||' fabric_header_property rows for flow_id '||p_flow_id||'.');
    delete from cm_fabric_header_property where flow_id =  p_flow_id;
  end;
  procedure del_linked_cases(p_flow_id in number)
  is
    l_count number;
  begin
    select count(*) into l_count from cm_linked_case where flow_id =  p_flow_id;
    pl('Delete '||l_count||' linked_case rows for flow_id '||p_flow_id||'.');
    delete from cm_linked_case where flow_id =  p_flow_id;
  end; 
  procedure del_property_bag(p_flow_id in number)
  is
    l_count number;
  begin
    select count(*) into l_count from cm_property_bag where flow_id =  p_flow_id;
    pl('Delete '||l_count||' property_bag rows for flow_id '||p_flow_id||'.');
    delete from cm_property_bag where flow_id =  p_flow_id;
  end; 
  procedure del_translation(p_flow_id in number)
  is
    l_count number;
  begin
    select count(*) into l_count from cm_translation where flow_id =  p_flow_id;
    pl('Delete '||l_count||' translation  rows for flow_id '||p_flow_id||'.');
    delete from cm_translation where flow_id =  p_flow_id;
  end; 
  procedure del_case(p_flow_id in number)
  is
    l_count number;
  begin
    select count(*) into l_count from cm_case where flow_id =  p_flow_id;
    pl('Delete '||l_count||' case rows for flow_id '||p_flow_id||'.');
    delete from cm_case where flow_id =  p_flow_id;
  end;
begin
  pl('Purge cases without SCA flow');
  for r_cse in c_cse loop
    pl(r_cse.case_id || ' - ' || r_cse.flow_id|| ' - '||r_cse.partition_name||' - '||r_cse.partition_date);
    del_case_header( r_cse.flow_id);
    del_case_data( r_cse.flow_id);
    del_case_object( r_cse.flow_id);
    del_case_document( r_cse.flow_id);
    del_case_milestones( r_cse.flow_id);
    del_case_events( r_cse.flow_id);
    del_case_flex_fields( r_cse.flow_id);
    del_case_stake_holders( r_cse.flow_id);
    del_case_stake_holder_members( r_cse.flow_id);
    del_case_comments( r_cse.flow_id);
    del_fabric_header_property( r_cse.flow_id);
    del_linked_cases( r_cse.flow_id);
    del_property_bag( r_cse.flow_id);
    del_translation( r_cse.flow_id);
    del_case( r_cse.flow_id);    
  end loop;
end;
/

A few remarks:

• Use this at your own risk: make sure you have database backup. I must admit I actually didn't (shame), but I have to mention it.
• This script is created by analysing the datamodel of SOA BPM 12c. It is not supported by Oracle of course. I won't take any responsibility of damage caused by using this script (of course). See my advise to take a backup in the previous bullet.
• I assume that it is as designed that cases aren't deleted at purging of the instances: a case is an administrative entity containing data and audit-logs that are related to customer interacts.You might adapt the driving query with an extra where-condition to only select the cases relating to the removed composites.
• Case definitions are not removed.
• Case documents are not removed from WebCenter Content, only the register in the CM_CASE_DOCUMENT table.

To remove case definitions use something like:

create table cm_case_definition_bck as
select * from cm_case_definition
where composite_name = '{your composite}'
and composite_version != '{composite version you want to keep}';

delete from cm_case_definition
where composite_name = '{your composite}'
and composite_version != '{composite version you want to keep}';;