Not as easy as a single SELECT query.
The point is: do your workflows use worklets?
If not, things are easy, for this case I think I can send you a query to get you all the sessions and mappings used in whichever workflows.
If yes, you will have to go a more complex road, but I'll describe that only upon request (because it needs to recursively scan parts of the repository, and that's not really easy to do in SQL).
However, for the command tasks things are more complicated. I well recall 2013 when I was tasked to retrieve all command tasks for thousands of workflows, and in the end I had to scan the XML exports of all those workflows; I didn't succeed in finding a query to get them from the repository itself.
So what I have done at that time was to build a script and a workflow; the script would export all the requested workflows to XML file and parse these XML exports for the command tasks. Tedious but at least this works safely.
2 of 2 people found this helpful
You can use the Metaquery tool to fetch this information. As Nico suggested, it is not in a single place, but Metaquery offer the option to :
- list workflow with associated sessions and maps ( the option "workflow session mapping" under "Workflow")
- list command tasks and their commands ( the option "Commands in command task").
Metaquery is a free download available on the ftp site in the folder /updates/Metaquery.
1 of 1 people found this helpful
Here's the query I use for command tasks matching a certain string (you can of course omit that condition, and I've commented it out below).
Naturally I can't 100% guarantee the results of this are correct.
/* Find command tasks with a particular command string */
select s.subj_name folder, wf.task_name workflow, decode(c.ref_obj_type, 2, 'Pre session', 3, 'Post session success', 4, 'Post session failure', 'Standalone') task_type,
nvl(ti.instance_name, decode(t.is_reusable, 1, t.task_name || '(' || cti.instance_name || ')', cti.instance_name)) task_name,
t.is_reusable, tvl.exec_order, tvl.val_name cmd_name, tvl.line_no, tvl.pm_value cmd_string
from opb_task_val_list tvl
inner join opb_task t on (tvl.task_id = t.task_id and tvl.version_number = t.version_number)
inner join opb_subject s on tvl.subject_id = s.subj_id
left outer join opb_task_inst ti on (t.task_id = ti.task_id and t.version_number = ti.version_number)
left outer join opb_component c on (t.task_id = c.ref_obj_id and (c.ref_version_number = t.version_number or t.is_reusable = 1))
left outer join opb_task_inst cti on (c.task_id = cti.task_id and c.version_number = cti.version_number)
inner join opb_task wf on nvl(ti.workflow_id, cti.workflow_id) = wf.task_id
where tvl.task_type = 58 /* command task */ and tvl.component_type = 0
--and (tvl.pm_value like '%gpg%')
order by folder, workflow, task_name, exec_order, line_no;
I hope this is helpful for you.
I should note that in particular, "workflow" should really be "workflow or worklet". I haven't tried to get the real workflow name, just the structure containing either the standalone command task or the session with the pre- or post-session command task. But I feel this is more useful anyway - if, for example, a command task in a worklet needs updating, it's the worklet name you need to know to update it; you don't need to see which workflows it is used in.
Thanks for all your responses.