Question

Query return all business processes with specific user task

Chani Karel

11:48 Jun 30, 2022

Hi,

I'm looking for a way to get a list of all my custom business processes that include the auto generated page user task

And a seperate list for BPs that include the Send email task.

I would likw to get the idea of the query so I can get list any user task.

Any ideas?

Thanks,

Chani

1 comments

Aleksei Efimenko

11:02 Jul 01, 2022

Dear Chani,

Here is an example of such a query, feel free to change it to your needs.

WITH StringMd AS (
	SELECT	[UId],
			CAST([MetaData] AS VARCHAR(MAX)) MetaData
	FROM	[SysSchema] WITH(NOLOCK)
	WHERE	[ManagerName] = 'ProcessSchemaManager'
			AND DATALENGTH([MetaData]) > 0
),
ValidMD as (
	SELECT	[UId],
	CAST(IIF(substring(MetaData, 0, 2) = '{', MetaData, substring(MetaData, 4, datalength(MetaData) - 1)) as VARCHAR(MAX)) AS MetaData
	FROM	StringMd
)
SELECT	sub.[ProcessSchemaUId],
		UT.[Caption]
FROM	(SELECT
			[UId] as ProcessSchemaUId,
			JSON_VALUE(value, '$.BL7') AS [UserTaskSchemaUId]
		FROM	ValidMD
		CROSS APPLY OPENJSON([MetaData], 'lax $.MetaData.Schema.BK4')
		WHERE	JSON_VALUE(value, '$.BL1') = 'Terrasoft.Core.Process.ProcessSchemaUserTask'
		) sub
JOIN	[SysProcessUserTask] AS UT WITH(NOLOCK) ON sub.UserTaskSchemaUId = UT.SysUserTaskSchemaUId

Best regards, Alex.

Show all comments