ADP (Vizual) HR.Net Development: Tips, Tricks and Work-Arounds

When workflows stall, they get stuck on the stage that caused the issue. Many times over it is useful to start the workflow from the start – a complete re-run.
But there is no way to do that in the UI – you can only unstall the workflow to start execution from the same stage. And that is not very useful.

Today I had to deal with a bunch of stalled workflows that had stalled stage removed (don’t ask me why – reasons!). And I did not fancy re-triggering these manually – these had to be done, otherwise I’d be loosing data. Long story short, here is a bit of SQL that will cause your stalled workflows to start from the first stage, not from the place where they got stuck:

update [dbo].[OC_WORKFLOWINSTANCESTACK]
set STAGEID = w.STARTSTAGE
from [dbo].[OC_WORKFLOWINSTANCES] wi
inner join [dbo].[OC_WORKFLOWINSTANCESTACK] s on s.INSTANCEID = wi.INSTANCEID
inner join dbo.OC_WORKFLOWS w on w.WORKFLOWID = wi.INITIALWORKFLOWID
where stalled = 1
and TRIGGERNAME='My failed trigger'


Workflow service is extremely useful and extremely fragile. About once a month I get a call from a customer with the problem that boils down to Workflow Service not operating.

Today I just had to see what is sitting in the workflow queue to know the problem. But looking on the workflow queue that is pending transaction is tricky. Here is SQL for that:

select
	pppf.folder_name as [Folder],
	ppf.folder_name as [Folder],
	pf.folder_name as [Folder],
	f.folder_name as [Folder],
    w.WorkflowName,
	wi.TriggerName,
	w.TABLENAME,
	wi.RECORDPK,
	wi.starteddatetime,
	wi.NEXTRUNDATE
from OC_WORKFLOWINSTANCES wi
	inner join OC_WORKFLOWS w on wi.INITIALWORKFLOWID=w.WORKFLOWID
    left join OC_ADMINCONSOLE_FOLDERS f on w.folderid = f.folder_id
	left join OC_ADMINCONSOLE_FOLDERS pf on f.parent_id = pf.folder_id
	left join OC_ADMINCONSOLE_FOLDERS ppf on pf.parent_id = ppf.folder_id
	left join OC_ADMINCONSOLE_FOLDERS pppf on ppf.parent_id = pppf.folder_id
where (wi.NEXTRUNDATE < getdate() or wi.NEXTRUNDATE is null)
	and w.ALLOWSTART=1
	and wi.stalled=0
order by wi.STARTEDDATETIME

And this will give you a count of workflows in the queue to be processed:

select count(*)
from OC_WORKFLOWINSTANCES wi
	inner join OC_WORKFLOWS w on wi.INITIALWORKFLOWID=w.WORKFLOWID
where (wi.NEXTRUNDATE < getdate() or wi.NEXTRUNDATE is null)
	and w.ALLOWSTART=1
	and wi.stalled=0

Sometimes I get support requests quoting an email received from HR.Net and asking to modify that email text or add more recipients. And then I need need to find that workflow containing the mailer that sent out that particular message. And given thousands of workflows, it can take days to find the exact workflow (unless you know exactly where to look for)

So I wrote this little SQL query that matches email subject to a workflow name/folder:

select 
	pppf.folder_name as [Folder],
	ppf.folder_name as [Folder],
	pf.folder_name as [Folder],
	f.folder_name as [Folder],
	flow.workflowname,
	flow.description,
	flow.allowstart
 from OC_WORKFLOWMAILER mailer
  inner join OC_WORKFLOWS flow on mailer.workflowid = flow.workflowid
  left join OC_ADMINCONSOLE_FOLDERS f on flow.folderid = f.folder_id
  left join OC_ADMINCONSOLE_FOLDERS pf on f.parent_id = pf.folder_id
  left join OC_ADMINCONSOLE_FOLDERS ppf on pf.parent_id = ppf.folder_id
  left join OC_ADMINCONSOLE_FOLDERS pppf on ppf.parent_id = pppf.folder_id
where mailer.htmlsubject like '%my subject%'