Workflow Queries

1.Query to know the Policy by which required workflow is fired.

select c.name as policy_name, d.DEFAULT_VALUE as workflow_name
from S_ACTION_DEFN a, S_ESCL_ACTION b, S_ESCL_RULE c,S_ACTION_ARG d
where a.row_ID = d.action_id
and b.action_id = a.row_ID
and c.row_id = b.RULE_ID
and d.DEFAULT_VALUE = 'Account Notify Children'


2.To know all Steps of a workflow

SELECT P.NAME PROCESS, S.NAME STEP --A.NAME ARG,A.VAL_TYPE_CD VAL_TYPE, A.VAL VAL
FROM S_WF_STEP P, S_WF_STEP S
WHERE P.TYPE_CD = 'PROCESS'
AND P.STATUS_CD = 'ACTIVE'
AND P.ROW_ID = S.PROCESS_ID
AND P.NAME = 'Status Change' --- WF Name


3.To Know All Workflows and Step in which particular Business Service is called

select B.NAME PROCESS_NAME , A.NAME PROCESS_STEP, A.SERVICE_NAME BUSINESS_SERVICE , A.METHOD_NAME METHOD
from S_WF_STEP A , S_WF_STEP B
where A.PROCESS_ID = B.ROW_ID
and A.SERVICE_NAME = 'Change Subscriber Status' -- Business Service Name
and B.STATUS_CD = 'ACTIVE'
order by 1,2,3


4.To Know the workflow Process and step which uses particular method of business Service

select b.name as process_name , a.name as process_step, a.service_name , a.method_name
from S_WF_STEP a , s_wf_step b
where a.process_id = b.row_id and
a.method_name = 'Change Subscriber Status' -- Name of the Bus Service Method
and b.status_cd = 'ACTIVE'
order by 1,2,3


5.To know all workflows and steps which uses particular workflow process as a subprocess

select P.NAME MAIN_PROCESS, S.NAME PROCESS_STEP , s.method_name SUB_PROCESS
from S_WF_STEP p, S_WF_STEP S
where P.TYPE_CD = 'PROCESS'
AND P.STATUS_CD = 'ACTIVE'
and s.process_id = p.row_id
and s.type_cd = 'SUB_PROCESS'
and s.method_name = 'Check Latest Order' -- Name of Subsprocess



6.Query to know All the input arguments passed to the method of particular Business Service through particular workflow

SELECT P.NAME PROCESS, S.NAME STEP, s.service_name BS_NAME , s.method_name , A.NAME ARGUMENT ,A.VAL_TYPE_CD VALUE_TYPE, A.VAL VALUE
FROM S_WF_STEP P, S_WF_STEP S, S_WF_STEP_ARG A
WHERE P.NAME = 'Account Online Status Update' -- Name of Workflow
AND P.TYPE_CD = 'PROCESS'
AND P.STATUS_CD = 'ACTIVE'
AND P.ROW_ID = S.PROCESS_ID
AND S.ROW_ID = A.STEP_ID
AND A.INPUT_FLG = 'I'
AND s.service_name is not null
AND s.method_name is not null
ORDER BY 1,2,3,4,5