Secured Resource - Unlock Active Items from "System" ID under certain error conditions

[DT23839]

The ability for Chorus Admin users to easily unlock specific objects that are locked to "System" would eliminate the need for self-hosted Clients to engage IT to release objects. Hosted Clients could save time/effort since they would not be required to open a ticket for SS&C Hosting to complete the unlock.

[Karen_Hunter]

Need additional information about the scenarios where work is remaining locked to System in an "Active" state and how unlocking the item from system would trigger the work restarting or advancing the work in the process. 

1. The Process Monitor is used to resume locked work from either an aborted or paused state. 
2. The Unlocker Tool in the Utilities workspace is used to unlock work remaining locked to non-System Userss

[doran.george]

SYSTEM locked worked items do NOT appear on Monitoring, only ABORTS.  We have built stand alone reports to find these, but to Karen's point, a ticket must be created once they are found to abort them manually.

[PaulSuh]

Here's the manual script we use to identify "locked by system" objects (those not aborted), that require the unlock sproc to first release the work object, which will then display in the aborted tab within monitor to resume. *those with a lock duration greater than a set timeframe can be considered locked in limbo.

USE AWD
select *
  From WD4U999S With (NoLock)
Where LOCKWORK_FLG='Y'
   And ASSIGNID='SYSTEM'
order by LOCK_DATTIM desc

[Karen_Hunter]

Note related item to change the object state to abort instead of unlocking the item. 

https://community.blueprism.com/idea/ability-to-manually-abort-a-work-object-in-chorus-bpm

Enhancement is prioritized subject to resourcing.  Target solution is to allow (qualified) Active work items that remain locked to system to be pushed to an aborted state, still locked to system.  Process Monitor can then be used to properly restart / resume the work item in a managed step generating the necessary update message events.

TBD:  What criteria can be securely used to "qualify" that the item is stuck and that there are no messages in queue waiting to be processed.

Feedback being monitored.  Thank you.

[MichelleGiannin]

****EDIT- Thank you so much Doran George for the query above. It is very very helpful!!! 

Thanks Paul S. for the idea.

We updated the query to build the work item key and then only show results locked to SYSTEM for atleast 60mins. 

Below is the query: 

Select TO_CHAR(CRDATTIM, 'yyyy-MM-dd-HH.mm.ssxff') || RECORDCD || CRNODE wiKey,
       CRDATTIM,
       ASSIGNID,
       ASSIGNED_BY,
       ASSIGN_DATTIM,
       LOCK_DATTIM
  from WD4U999S
where ASSIGNID = 'SYSTEM'
   and LOCKWORK_FLG = 'Y'
   and LOCK_DATTIM < CURRENT_TIMESTAMP - interval '60' minute

[doran.george]

What if the item is locked in SYSTEM queue but not locked USERID of SYSTEM?

What if you can truly check the state of the work object as being locked or aborted?

What if you want one report to show LOCK and ABORTED items?

What if you want to include Business Area and Worktype?

What if you want the same query to run on multiple environments without having to hard code environment name?

The query below does all the above with a 2 hour window (can be changed to 30 minutes), we having large batch processes that work objects can legitimately SYSTEM for longer than 30 minutes.

SELECT 

(SELECT sys_context('userenv','instance_name') AS ENVIRONMENT FROM DUAL) AS AWD_ENVIRONMENT, 

TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS CURRENT_DATE,

TO_CHAR(SYSDATE, 'HH24:MI:SS')  AS CURRENT_TIME ,

'ABORTED' AS SYSTEM_STATUS, 

TO_CHAR(WA4.KEY_TIMESTAMP , 'YYYY-MM-DD-hh24.mi.ss.FF') || WA4.RECORDCD || WA4.CRNODE AS AWD_KEY,

WA4.UNITCD,  WA4.WRKTYPE, WA4.STATCD,  WD4.ASSIGNID, WA4.QUEUECD, 

TO_CHAR(WA4.EVENT_DATTIM, 'YYYY-MM-DD-hh24.mi.ss.FF') AS EVENT_DATTIM

FROM AWD.WU7U999S WU7

JOIN AWD.WU8U999S WU8 ON (WU8.PROCESS_INST_ID = WU7.PROCESS_INST_ID) 

JOIN BI.WA4U999S WA4

ON ( WA4.KEY_TIMESTAMP   =   WU8.CRDATTIM

AND WA4.RECORDCD = WU8.RECORDCD

AND WA4.CRNODE = WU8.CRNODE  ) 

LEFT JOIN AWD.WD4U999S WD4 

ON (  WD4.CRDATTIM        =   WU8.CRDATTIM      

AND WD4.RECORDCD = WU8.RECORDCD

AND WD4.CRNODE = WU8.CRNODE   ) 

WHERE WU7.STATE = 5 

UNION ALL

SELECT 

(SELECT sys_context('userenv','instance_name') AS ENVIRONMENT FROM DUAL) AS AWD_ENVIRONMENT, 

TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS CURRENT_DATE,

TO_CHAR(SYSDATE, 'HH24:MI:SS')  AS CURRENT_TIME ,

'LOCKED' AS SYSTEM_STATUS,

TO_CHAR(WA4.KEY_TIMESTAMP , 'YYYY-MM-DD-hh24.mi.ss.FF') || WA4.RECORDCD || WA4.CRNODE AS AWD_KEY,

WA4.UNITCD,  WA4.WRKTYPE, WA4.STATCD,  WD4.ASSIGNID, WA4.QUEUECD, TO_CHAR(WA4.EVENT_DATTIM, 'YYYY-MM-DD-hh24.mi.ss.FF') AS EVENT_DATTIM

FROM 

(

SELECT WD4.CRDATTIM AS KEY_TIMESTAMP, WD4.RECORDCD, WD4.CRNODE

FROM AWD.WD4U999S WD4

WHERE WD4.ASSIGNID = 'SYSTEM'

UNION 

SELECT WA4.KEY_TIMESTAMP, WA4.RECORDCD, WA4.CRNODE

FROM BI.WA4U999S WA4 

WHERE WA4.QUEUECD = 'SYSTEM'

) SL

JOIN BI.WA4U999S WA4

ON ( WA4.KEY_TIMESTAMP   =   SL.KEY_TIMESTAMP

AND WA4.RECORDCD = SL.RECORDCD

AND WA4.CRNODE = SL.CRNODE  ) 

JOIN AWD.WU8U999S WU8 

ON ( WU8.CRDATTIM        =   WA4.KEY_TIMESTAMP

AND WU8.RECORDCD = WA4.RECORDCD

AND WU8.CRNODE = WA4.CRNODE  ) 

JOIN AWD.WU7U999S WU7 

ON ( WU7.PROCESS_INST_ID =   WU8.PROCESS_INST_ID) 

LEFT JOIN AWD.WD4U999S WD4 

ON (  WD4.CRDATTIM        =   WA4.KEY_TIMESTAMP

AND WD4.RECORDCD = WA4.RECORDCD

AND WD4.CRNODE = WA4.CRNODE ) 

WHERE   WU7.STATE = 2  

    AND WA4.EVENT_DATTIM < CURRENT_TIMESTAMP - INTERVAL '2' HOUR;