JAnnotation: Oracle BPM 12c SOAINFRA tables and query

To fetch information based on State of instance, Composite name and approvers name

run below mention query:

SELECT (CASE WHEN c.STATE=1 THEN 'OPEN AND RUNNING'
WHEN c.STATE=2 THEN 'OPEN AND SUSPENDED'
WHEN c.STATE=3 THEN 'OPEN AND FAULTED'
WHEN c.STATE=4 THEN 'CLOSED AND PENDING'
WHEN c.STATE=5 THEN 'CLOSED AND COMPLETED'
WHEN c.STATE=6 THEN 'CLOSED AND FAUTED'
WHEN c.STATE=7 THEN 'CLOSED AND CANCELLED'
WHEN c.STATE=8 THEN 'CLOSED AND ABORTED'
WHEN c.STATE=9 THEN 'CLOSED AND STALE'
WHEN c.STATE=10 THEN 'NON-RECOVERABLE'
ELSE C.STATE || ''
END) AS STATE, w.compositename, w.updatedbydisplayname ,

count(*)AS NUM_OF_INSTANCES
FROM CUBE_INSTANCE C, WFTASK W
WHERE c.cmpst_id=w.compositeinstanceid
group by w.compositename, w.updatedbydisplayname,C.STATE;

The above query needs access to two tables “CUBE_INSTANCE” and “WFTASK”.

CUBE_INSTANCE: - The cube instance table stores the instance data for any composite instance. The CIKEY is the primary key of the table and this is the unique reference for a initiated instance. The STATE denotes whether the instance is stale/completed successfully/error. ‘Cmpst_id’ is the composite instance Id.
WFTASK: - This table is Metadata table for workflow tasks. ‘Compositename’ shows the name of the composite. ‘Updatedbydisplayname’ column shows the approver’s name.

Find by title or flow_id the “flow tree”.First column is the parent composite

select parent.COMPOSITE_NAME as ParentCMP,

cu.COMPOSITE_NAME,cu.COMPONENT_NAME,cu.STATE,

(CASE WHEN cu.STATE = 0 THEN 'INITIATED'
WHEN cu.STATE = 1 THEN 'OPEN AND RUNNING'
WHEN cu.STATE = 2 THEN 'OPEN AND SUSPENDED'
WHEN cu.STATE = 3 THEN 'OPEN AND FAULTED'
WHEN cu.STATE = 4 THEN 'CLOSED AND PENDING'
WHEN cu.STATE = 5 THEN 'CLOSED AND COMPLETED'
WHEN cu.STATE = 6 THEN 'CLOSED AND FAUTED'
WHEN cu.STATE = 7 THEN 'CLOSED AND CANCELLED'
WHEN cu.STATE = 8 THEN 'CLOSED AND ABORTED'
WHEN cu.STATE = 9 THEN 'CLOSED AND STALE'
WHEN cu.STATE = 10 THEN 'CLOSED_ROLLED_BACK'
ELSE cu.STATE || '' END) cuState,cu.CIKEY,
cu.COMPOSITE_REVISION,
cu.CREATION_DATE,
scf.ACTIVE_COMPONENT_INSTANCES,
scf.RECOVERABLE_FAULTS,
scf.TITLE,
parent.CIKEY as ParentCikey,
cu.FLOW_ID
from CUBE_INSTANCE cu,SCA_FLOW_INSTANCE scf,CUBE_INSTANCE parent
where
parent.CIKEY=to_number(REGEXP_SUBSTR(cu.PARENT_ID, '[^:]+$'))
and cu.FLOW_ID=scf.FLOW_ID
and cu.PARENT_ID is not null
and scf.title='GIVEYOURTITLEHERE'
-- and scf.flow_id='1234'
ORDER by cu.CREATION_DATE

Faulted composites

 SELECT scai.title FROM SCA_COMMON_FAULT scaf,SCA_FLOW_INSTANCE scai
where
scaf.flow_id=scai.flow_id and
scaf.FAULT_NAME = 'SystemFault'  and
scaf.CREATION_DATE > TO_DATE('2018-02-14 00:00:00', 'YYYY-MM-DD HH24:Mi:SS')
-- Different States:
-- 256 not recoverable
-- 2560 recovered
-- 512 recovery required RETRY/REPLAY/ABORT/CONTINUE
-- 3584 recovered retry
-- 3072 recovery required RETRY/ABORT/CONTINUE
-- 2304 AUTO RETRY
-- 756 recovery required RETRY/ABORT/CONTINUE transactionRollback
and scaf.state=512
--and scai.RECOVERABLE_FAULTS!=0
--and  scaf.COMPONENT_TYPE='bpel'
group by scai.title,scaf.flow_id

Count All Instances by state in human readable format, group by composite. (same as 11g)

SELECT (CASE
WHEN STATE=1 THEN 'OPEN AND RUNNING'
WHEN STATE=2 THEN 'OPEN AND SUSPENDED'
WHEN STATE=3 THEN 'OPEN AND FAULTED'
WHEN STATE=4 THEN 'CLOSED AND PENDING'
WHEN STATE=5 THEN 'CLOSED AND COMPLETED'
WHEN STATE=6 THEN 'CLOSED AND FAUTED'
WHEN STATE=7 THEN 'CLOSED AND CANCELLED'
WHEN STATE=8 THEN 'CLOSED AND ABORTED'
WHEN STATE=9 THEN 'CLOSED AND STALE'
WHEN STATE=10 THEN 'NON-RECOVERABLE'
ELSE STATE || '' END) AS STATE,COMPOSITE_NAME,

COUNT(*) AS NUM_OF_CUBE_INST

FROM CUBE_INSTANCE GROUP BY STATE,COMPOSITE_NAME

SQL Query to extract BPM Tasks information

SELECT
         fwt.tasknumber
       , fwt.assigneesdisplayname
       , fwt.assigneddate
       , fwt.outcome
       , fwt.title
       , fwt.taskdefinitionname
       , fwc.wfcomment
       , fwat.content
       , fwat.name AS attachmentname
       , fwat.description
       , fwat.attachmentsize
       , fwm.name
       , fwm.encoding
       , fwm.blobvalue
       , fwm.elementseq
FROM
         fa_fusion_soainfra.wftask                    fwt
       , fa_fusion_soainfra.wfassignee                fwa
       , fa_fusion_soainfra.wftaskhistory             fwh
       , fa_fusion_soainfra.wfcomments                fwc
       , fa_fusion_soainfra.wfattachment              fwat
       , fa_fusion_soainfra.wftaskassignmentstatistic fwst
       , fa_fusion_soainfra.wfmessageattribute        fwm
       , fa_fusion_soainfra.wfcollectiontarget        fwtg
WHERE
         1               =1
         AND fwt.taskid  = fwa.taskid
         AND fwt.taskid  = fwh.taskid
         AND fwa.taskid  = fwc.taskid(+)
         AND fwt.taskid  = fwat.taskid(+)
         AND fwat.taskid = fwst.taskid(+)
         AND fwt.taskid  = fwm.taskid
         AND fwt.taskid  = fwtg.taskid(+)
         --and fwt.tasknumber = '201200'
ORDER BY
         fwt.compositecreatedtime DESC;