Tuesday, September 16, 2014

SQL to find all related items to a TRIRIGA Form

SELECT m.module_name Module, w.bo_type_name BO, w.wf_name Workflow,
 w.wf_template_version, wtn.Name TaskType,
 e.FORMULA Formula, ep.PARAM_STR Parameter
FROM TASK t, WF_TEMPLATE w, wf_lookup wl, IBS_MODULE m,
 expression e, expression_param ep, wf_task_name wtn
WHERE (ep.param_str LIKE '%fieldName=''triFormNameSY''%')
and e.formula like '%RealEstateLease%'
AND ep.FORMULA_ID = t.MAP_ID
AND e.ID = t.MAP_ID
AND t.wf_template_id = w.wf_template_id
AND t.version = w.wf_template_version
and w.wf_template_id = wl.wf_template_id
and w.wf_template_version = wl.wf_template_version
AND w.status_id IN (10,20) -- (Published, Revision-In-Progress)
AND m.MODULE_ID = w.BO_CLASS_TYPE_ID
AND t.TASK_TYPE = wtn.TASK_TYPE
ORDER BY Module, BO, Workflow, TaskType;