Oracle BPM 12c SOAINFRA tables and query - part 1

I've come back on Oracle BPM for process instance recovery and similar. Following usefull schema of main tables and queryes. There is no official documentation on these tables,
No guarantee that these are flawless

The tables I would like to discuss in more detail are:
- sca_flow_instance
- cube_instance
- wftask
- sca_entity
- bpm_cube_process
- bpm_cube_activity

 sca_flow_instance

As the name already suggests, this table contains 1 entry for each flow instance. You might be interested in the following columns:

• flow_id
• title
• active_component_instances
• recoverable_faults
• created_time
• updated_time

A good star query for this table could be:

select sfi.flow_id
,      sfi.title
,      sfi.active_component_instances
,      sfi.recoverable_faults
,      sfi.created_time
,      sfi.updated_time
from  sca_flow_instance sfi
order by sfi.created_time

cube_instance

This table contains 1 entry for each component instance in the flow (e.g. bpmn, bpel). You might be interested in the following columns:

• flow_id
• composite_label (*)
• cpst_inst_created_time (**)
• composite_name
• composite_revision
• component_name
• componenttype
• state (of the component <== mention)
• creation_date (incl time)
• modify_date (incl time)
• conversation_id

(*) corresponds with the bpm_cube_process.scalabel
(**) equals sca_flow_instance.created_time

A good star query for this table could be:
 

select cis.flow_id
,      cis.componenttype
,      cis.component_name
,      cis.state
from   cube_instance cis
order by cis.flow_id

wftask

This table contains an entry for each open process activity and open or closed human activity. You might be interested in the following columns:

• flow_id
• instanceid
• processname
• accesskey (not for human tasks) (*)
• createddate
• updateddate
• (only in case of human tasks, the flex fields)
• componentname
• compositename (not for human tasks)
• conversationid
• componenttype (***)
• activityname
• activityid (****)
• component_instance_id (only for human tasks)
• state (*****)

(*) : the type of activity, e.g. USER_TASK, INCLUSIVE_GATEWAY, END_EVENT
(**) not for human tasks
(***) e.g. Workflow, BPMN
(****) Corresponds with the activityid of bpm_cube_activity. The user activity and its corresponding human task appear to have the same activityid. After the human task is completed, the user activity disappears but the human task is kept with an null state.
(*****) e.g. OPEN for running activities, ASSIGNED for running human tasks. Other states are ABORTED, PENDING_MIGRATION_SUSPENDED, ERRORED, etc.

A good star query for this table could be:

select wft.instanceid
,      wft.processname
,      wft.accesskey
,      wft.createddate
,      wft.updateddate
,      wft.componentname
,      wft.compositename
,      wft.conversationid
,      wft.componenttype
,      wft.activityname
,      wft.activityid
,      wft.component_instance_id
,      wft.state
from   wftask wft
where  wft.flow_id = :flowId 

--and  wft.instanceid = :instanceId

order by wft.updateddate

 sca_entity

This table contains an entry for each SCA entity (e.g. service, wire). The following column might be of use:

• id
• composite (name)
• label (corresponds with the scalabel of bpm_cube_process)

Query:

select sen.composite
,      sen.id
,      sen.label
from   sca_entity sen
where  sen.composite = :compositeName
order by sen.composite

bpm_cube_process

This table contains metadata. For each deployed composite it contains an entry for each BPM process. If 2 BPM processes in once composite: 2 entries. The following columns might be of use:

• domainname
• compositename
• revision
• processid
• processname
• scalabel
• compositedn
• creationdate (incl time)
• undeploydate
• migrationstatus (*)

(*) Values are LATEST, MIGRATED.

query:

select bcp.domainname
,      bcp.compositename
,      bcp.revision
,      bcp.processname
,      bcp.processid
,      bcp.scalabel
,      bcp.compositedn
,      bcp.creationdate
,      bcp.undeploydate
,      bcp.migrationstatus
from   bpm_cube_process bcp
where  bcp.compositename = :compositeName
order by bcp.processname
,        bcp.creationdate

bpm_cube_activity

This table contains metadata, There is an entry for each individual activity, event, and gateway of a bpmn process. The following column might be of use:

• processid (corresponds with the bpm_cube_process.processid)
• activityid
• activityname (technical, internal name can be found in the .bpmn source)
• activitytype (e.g. START_EVENT, SCRIPT_TASK, CALL_ACTIVITY, etc.)
• label (name as in the BPMN diagram) 

 

Obsolete Tables

The following table have become obsolete:

• bpm_activity
• bpm_activity_instance
• bpm_cube_activity_instance
• bpm_process

component_instanceThe composite_instance is still used, but more or less superseded by the sca_flow_instance (although the number of instances are not the same).

 

 

ref: 

my experience and this post