SQL Query to Fetch BPM Tasks Information

Shailender Thallam

4 years ago

We can use tables of FUSION_RUNTIME which stores information of BPM tasks for couple of months or until they are purged.

Note: Oracle has granted access to table under SOAINFRA only from Release 12.

/********************************************************* 
 *PURPOSE: SQL Query to extract BPM Tasks information    *
 *AUTHOR: Shailender Thallam 				 *
 *********************************************************/
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;

Reference: Access To SOA BPM Tables In BI Publisher (Doc ID 2275088.1) https://support.oracle.com/epmos/faces/DocContentDisplay?id=2275088.1

For Further Reading