EPA_TaskboxExtension GetActivities input parameter AdditionalWhereClauses syntax

EPA_TaskboxExtension GetActivities input parameter AdditionalWhereClauses syntax

  

In the EPA_Taskbox Extension  there us a action GetActivities which provides a input parameter AdditionalWhereClauses.

The description states: 


Aditional query conditions to narrow the result.
It has to start with the ' AND ' string.

I'm assuming that GetActivities queries the Activity table, and that the syntax would be the same as for advanced queries  but I had no luck in finding the correct syntax for this input parameter:


The options i tried:

" AND {Activity}.[Name] = 'CheckChangeEmail'" result: Incorrect syntax near '}'.
" AND Activity.Name = 'CheckChangeEmail'" result The multi-part identifier "Activity.Name" could not be bound.
" AND Name = 'CheckChangeEmail'" result Ambiguous column name 'Name'
" AND 1=1 " result get all my activities unfilterd (this was just an idiot check)


Solution

I used profiling on the database to get the executed query. 

The OSSYS_BPM_ACTIVITY has an alias act. Therfore the correct syntax is:

" AND Act.Name = 'CheckChangeEmail'"

The full query is: 

SELECT TOP(@BatchSize) q.Id, q.Espace_Id, q.Tenant_Id, tenant.name as Tenant_Name, q.Activity_Id, act.Process_Id, q.Process_Def_Id, q.Data_Id, q.Enqueue_Time, q.Next_Run
FROM OSSYS_BPM_EVENT_QUEUE q WITH (UPDLOCK,ROWLOCK,READPAST)
LEFT JOIN OSSYS_ESPACE_RUNTIME runtime ON runtime.ESPACE_ID = q.ESPACE_ID
LEFT JOIN OSSYS_TENANT tenant ON q.Tenant_Id = tenant.Id
LEFT JOIN OSSYS_BPM_ACTIVITY act ON q.Activity_Id = act.Id
LEFT JOIN OSSYS_BPM_PROCESS process ON act.Process_Id = process.Id
INNER JOIN @ActiveProcess processDef ON (process.Process_Def_Id = processDef.Id OR q.Process_Def_Id = processDef.Id)
    WHERE (runtime.Id IS NULL OR runtime.Disabled = 0)
AND (tenant.id IS NULL OR tenant.is_active = 1)
AND (act.id IS NULL OR ((act.is_running_since IS NULL Or act.is_running_since = @NullDate) AND process.Status_Id = 1))
AND q.Next_Run <= GETDATE()
AND Dequeue_Time IS NULL
AND q.Espace_Id in (select ID from @ActiveEspace)
AND (@ActivityId = 0 OR q.Activity_Id = @ActivityId)
ORDER BY q.Next_Run ASC

Solution