Useful SQL Queries – Workflow Processes exposed.

SELECT t5.name,
t3.name,
t3.proc_name,
t2.name,
t1.name,
t1.val,
t1.name,
t1.buscomp_fld_name,
t1.buscomp_name,
t1.input_flg,
t1.proc_prop_name,
t1.output_arg,
t1.val_type_cd

FROM siebel.s_wfr_stp_arg t1
INNER JOIN siebel.s_wfr_stp t2
ON t1.step_id = t2.row_id
INNER JOIN siebel.s_wfr_proc t3
ON t2.process_id = t3.row_id LEFT
OUTER JOIN siebel.s_project t4
ON t3.project_id = t4.row_id LEFT
OUTER JOIN siebel.s_repository t5
ON t1.repository_id = t5.row_id WHERE t5.row_id IN('1-55W2-1')
AND t3.proc_name LIKE '%GEAE Material Lead Form%'
--Update the Workflow Process Name
AND t3.status_cd = 'COMPLETED'
--Workflow Status
AND t2.name like '%Convert2Text%'
--Step Name
and t1.name like 'U%Path%'
--Argument Name
and t1.val like '%err%'
--Argument Value
ORDER BY t1.name;

Explanation of the variable used are as following

• t5.row_id: Row Id of the Repository in which the workflow process resides
• t3.proc_name: Name of Workflow Process
• t3.status_cd: Status of the Workflow Process
• t2.name: Name of the Step in Workflow Process
• t1.name: Name of the I/O Argument in Step
• t1.val: Value given in the I/O Argument

Examples of where clause

Details of workflow process name “Sample Workflow”

WHERE t5.row_id IN(’1-55W2-1′)
AND t3.proc_name LIKE ‘Sample Workflow’

Details of workflow process name “Sample Workflow” and status as Completed.

WHERE t5.row_id IN(’1-55W2-1′)
AND t3.proc_name LIKE ‘Sample Workflows’ 
–Update the Workflow Process Name
AND t3.status_cd = ‘COMPLETED’
–Workflow Status

Details of All the workflows with completed status and having a step name Unix

WHERE t5.row_id IN(’1-55W2-1′)
AND t3.status_cd = ‘COMPLETED’
AND t2.name like ‘Unix’

Hope this helps. If you have any questions don’t hesitate to drop comments. I will be more than happy to answer them.