Using Owner (last) vs. Scheduled job (Refresh of user assignment, OrgCharts, ..)

You may have come across to an issue when using both owner (last) and scheduler job (Refresh user assignment, OrgCharts, etc.). It is not recommended to use both, otherwise it can come to error behaviour of the system.

Owner (last)
As you know, using object action “Add owner (last)” adds owner to a request after assigning users to request which leads to having an access as an owner to this request and not having it assigned (not in My tasks).

And according to setup for owners on an object (visibility and editability of fields) this owner can edit the request. Owner cannot use WF actions but in general can edit accessible fields (even buttons).

Scheduler job - Refresh of user assignment, OrgCharts …
In Administration/Scheduler you can set a job that refreshes User assignment, OrgChart, Performance log and WF fail states. You have two options, either set it for all objects or just selected object.

In general this job takes care of:

  • Re-assigning requests according to current user access and setup (OrgCharts, Owners, etc.)
  • Refreshing OrgCharts on requests
  • Writing data to performance log
  • Moving requests that are not assigned to anyone to WF fail state (if it is set on WF)

Issue using both
Issue arises when you use both Owner (last) and mentioned scheduler job because this job re-assigns users to requests and does not take into account whether the added owner has it in My tasks (inbox) or not (last owner).

What happens is that user, as a last owner, has it assigned in his/her My tasks and can click on WF action, which after clicking closes the request detail and show error message.

What to do
To avoid this situation, it is suitable to configure a periodic that will handle the workflow maintenance of all relevant requests in progress of defined objects in respect with the use of Owner & Owner (Last) object actions and in a standard way of all requests in progress of all other objects.

First it is reasonable to configure a system object, which will contain only one request. This system object should be included in the base package configuration, where it is called Periodic run. Hence it should be included in most current Xeelo installations, however if this object is not included from an old base package instance, then please configure it accordingly.

The reason for this system object is to have only one request in the database that will serve as request for running the periodic that will be covered later in this comment.

SQL query to run on periodic
-- =================================================== --
-- Select objects where owner (normal or last) is used --
-- =================================================== --
-- normal = owner set on request by user with Actions/Add owner (which is a similar case like last owner)
-- last = owner set on request after assignment

drop table if exists #objectIDsOwners
create table #objectIDsOwners (
   ObjectID int
  )
insert into #objectIDsOwners
select distinct oa.ObjectID as ObjectID

from dbo.ObjectAction as oa with(nolock)
inner join dbo.WorkflowStepObjectAction as wsoa with(nolock)
    on (oa.objectActionID = wsoa.ObjectActionID)
inner join dbo.WorkflowStep as ws with(nolock)
	on (wsoa.WorkflowStepID = ws.WorkflowStepID)
inner join dbo.Workflow as w with(nolock)
	on (ws.WorkflowID = w.WorkflowID)

where oa.ObjectActionTypeCode in ('spRequestOwnerInsert','spRequestOwnerInsertLineData','spRequestOwnerInsertLast','spRequestOwnerInsertLineDataLast')
  and oa.isActive = 1 -- only active OAs with owner
  and wsoa.IsActive = 1 -- only active OAs with owner assigned on WF steps
  and ws.IsActive = 1 -- only active WF steps
  and w.IsActive = 1 -- only active WFs

-- ===================================================== --
-- Select last Requests from objects with assigned users --
-- ===================================================== --

drop table if exists #TempRequestInProgress
create table #TempRequestInProgress (
   	LastRequestID bigint
   ,UserID int
  )
      
insert into #TempRequestInProgress (LastRequestID, UserID)
select lr.LastRequestID as LastRequestID
      ,lra.UserID as UserID
      
from #objectIDsOwners as o
cross apply (
                  select LastRequestID as LastRequestID
                  from  dbo.fnRequestViewLastObject(o.ObjectID)
                  where LastCompletedRequestID != LastRequestID -- just requests in progress states
            ) as lr
cross apply (
  		     select rul.RequestID
  		           ,rul.UserID
  	             from dbo.RequestUserList as rul
  		     inner join dbo.Role as r
  		       on (rul.RoleID = r.RoleID and r.IsOwner = 1) -- only users assigned as owners
                     where rul.RequestID = lr.LastRequestID
            ) as lra

-- ===================================================== --
-- Remove owners/users who do not have requests assigned --
-- ===================================================== --
-- firstly insert records in temp table --

drop table if exists #tempRequestsLastOwner
create table #tempRequestsLastOwner(
  	RequestID bigint
   ,UserID int
  )

insert into #tempRequestsLastOwner
select distinct ro.RequestID as RequestID
		       ,ro.UserID as UserID
               
from dbo.RequestOwner as ro
left join #TempRequestInProgress as trip
	on (ro.RequestID = trip.LastRequestID and ro.UserID = trip.UserID and ro.IsActive = 1)
  
where trip.UserID is null -- null = owner does not have request assigned
and ro.RequestID in (select LastRequestID from #TempRequestInProgress) -- update only records in table RequestOwner table that are present in the temp table

-- Deactivation of last owners --

update ro
set ro.IsActive = 0 -- deactivate last Owner

from dbo.RequestOwner as ro
inner join #tempRequestsLastOwner as trlo
	on (ro.RequestID = trlo.RequestID and ro.UserID = trlo.UserID and ro.IsActive = 1)

-- =============================================================================================================== --
-- Run user refresh assignment, orgchart, wf fail, performance after removing owners who do not have reqs assigned --
-- =============================================================================================================== --

exec dbo.spRequestWorkflowRefreshAll

-- ========================================= --
-- Set back users as last owners on Requests --
-- ========================================= --

update ro2
set ro2.IsActive = 1 -- activate owners back (normal & last, who did not have requests assigned)

from dbo.RequestOwner as ro2
inner join #tempRequestsLastOwner as trlo
	on (ro2.RequestID = trlo.RequestID and ro2.UserID = trlo.UserID and ro2.IsActive = 0)

The logic of the periodic/SQL query is following:

  1. Define list of objects, where object action of type owner or owner (last) is used.
  2. Insert list of last requests in progress along with owners who are assigned to the requests of defined objects into temporary table.
  3. Insert list of owners who are not assigned to the list of requests (= last owners) into temporary table.
  4. Remove owners from the list of requests in table dbo.RequestOwner for defined objects who are not assigned.
  5. Run procedure dbo.spRequestWorkflowRefreshAll that will be executed on all requests in progress of all objects. This procedure includes and runs following procedures:
  • dbo.spRequestRefreshUserOrgChart
  • dbo.spRequestWorkflowUserCondition
  • dbo.spRequestWorkflowPerformance
  • dbo.spRequestWorkflowFail
  1. In the last step owners (last) will be added to requests of defined objects directly in table dbo.RequestOwner.

Once the periodic is configured it must be added to a scheduler. Best practice would be to add it into the same scheduler, which previously runned the workflow maintenance for all objects by standard scheduler line type.