--- Please follow the below comments to insert SQL statements.
--- Description: This query will detect and track when WF solutions are executed on a CommServe

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 202
DECLARE @surveyXML NVARCHAR(MAX)

--------- BEGIN SURVEY QUERY ---------
DECLARE @watermark int
SELECT @watermark = CAST(value as int) FROM GXGlobalParam WHERE name = 'apss_solution_workflow_watermark'
IF @watermark IS NULL BEGIN
	SET @watermark = DATEDIFF(SECOND, '1/1/1970', GETUTCDATE()-7)
END

SELECT otherOpName AS WorkflowName, 
SUBSTRING(otherOpName, 
			CHARINDEX('_', otherOpName, 0) + 1, 
			(CHARINDEX('_', otherOpName, CHARINDEX('_', otherOpName, 0) + 1) - CHARINDEX('_', otherOpName, 0) - 1))
AS SolutionId, servEnd
INTO #Temp01
FROM JMAdminJobStatsTable 
WHERE otherOpName like 'apss[_][0-9]%' AND status = 1 
AND (CHARINDEX('_', otherOpName, CHARINDEX('_', otherOpName, 0) + 1) - CHARINDEX('_', otherOpName, 0) - 1) > 0
AND servEnd > @watermark

DECLARE @newWatermark int
SELECT @newWatermark = MAX(servEnd) FROM #Temp01

IF @newWatermark IS NOT NULL BEGIN
	EXEC QS_SetKeyIntoGlobalParamTbl 'apss_solution_workflow_watermark', 'Y', @newWatermark
END

DECLARE @wfSolutionDetectionXML XML = (

	SELECT WorkflowName, SolutionId, DATEADD(SECOND, servEnd, '1/1/1970') AS SolutionAppliedTimestamp 
	FROM #Temp01
	
	FOR XML PATH('APSSQuery')
)

DROP TABLE #Temp01

SET @surveyXML = CAST (@wfSolutionDetectionXML AS nvarchar(max) )

--------- END SURVEY QUERY ---------

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)
SET NOCOUNT OFF

--------- END - GENERATED CODE ---------