

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/RptGetLastBackupJobs.sp] ---------- 

-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc. The copyright notice above does not evidence
--        any actual or intended publication of such source code.
--
-- ----------------------------------------------------------------------*/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/RptGetLastBackupJobs.sp,v $ $Id: RptGetLastBackupJobs.sp,v 1.1.2.26.4.2 2021/07/15 19:39:17 junlu Exp $";
-- Following Line Indicates new Class.  It should be identical to filename!
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='RptGetLastBackupJobs')
BEGIN
	print '>>> Drop Stored Procedure: RptGetLastBackupJobs <<<'
	drop procedure RptGetLastBackupJobs
END
IF EXISTS (select * from GxQscripts where name='RptGetLastBackupJobs')
	delete from GxQscripts where name = 'RptGetLastBackupJobs'
GO

IF EXISTS (select * from GXDBVersions where aliasname='RptGetLastBackupJobs')
	delete from GXDBVersions where aliasname = 'RptGetLastBackupJobs'
GO
print '... Creating Procedure: RptGetLastBackupJobs'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure RptGetLastBackupJobs
  @i_completed INT = 0,
  @i_fullJobsOnly INT = 0,
  @i_foreignJobs INT = 0,
  @i_localeId INT = 0,
  @i_beforeTime INT = 0
AS
SET NOCOUNT ON
/* This temp table is created by the caller
	CREATE TABLE #LastBackupJobs (
		clientId INT, appTypeId INT, appId INT, -- These columns are populated by the caller
		lastSuccJobId INT, lastSuccParentJobId INT, lastSuccJobCCId INT, lastSuccJobStart INT, lastSuccJobLevel INT,
		lastFailJobId INT, lastFailParentJobId INT, lastFailJobCCId INT, lastFailJobStart INT, lastFailJobLevel INT,
		lastFailJobStatus INT, failureErrorCode INT, failureReasons NVARCHAR(MAX)
	)
*/
IF @i_beforeTime = 0
   SET @i_beforeTime = 0x7FFFFFFF
DECLARE	@SnapJobsQualifySLA INT = 0
SELECT	@SnapJobsQualifySLA = longVal
FROM	APP_ComponentProp WITH (NOLOCK)
WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3303 AND modified = 0
DECLARE @SnapMetSLAIfBackupCopyDisabled INT = 0
SELECT	@SnapMetSLAIfBackupCopyDisabled = longVal
FROM	APP_ComponentProp WITH (NOLOCK)
WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3312 AND modified = 0
CREATE TABLE #SnapArchGroup (ArchGrpId INT, BackupCopyEnabled INT, SnapVaultExists INT)
IF	@SnapJobsQualifySLA = 0 AND @SnapMetSLAIfBackupCopyDisabled = 1
BEGIN
	INSERT	INTO #SnapArchGroup
	SELECT	AG.id, ISNULL(S.enabled, 0), 0
	FROM	archGroup AG WITH (NOLOCK)
			LEFT OUTER JOIN archMaterializeSnapshotProp S WITH (NOLOCK) ON AG.id = S.archGroupId
	WHERE	AG.defaultSnapCopy > 1
	UPDATE	T SET SnapVaultExists = 1
	FROM	#SnapArchGroup T
			INNER JOIN (
				SELECT	AG.id
				FROM	archGroup AG WITH (NOLOCK)
						INNER JOIN archGroupCopy AGC WITH (NOLOCK) ON AG.id = AGC.archGroupId
				WHERE	AGC.isActive = 1 AND AGC.isSnapCopy = 1 AND AGC.isMirrorCopy = 0 AND AGC.id <> AG.defaultSnapCopy
				GROUP BY AG.id
			) S ON T.ArchGrpId = S.id
END
IF @i_completed IN (1, 2)
BEGIN
	IF @i_foreignJobs = 0
	BEGIN
		UPDATE T SET lastSuccJobId = B.jobId, lastSuccJobCCId = B.commCellId,
			lastSuccJobStart = CASE WHEN B.servStartDate > T.lastSuccJobStart THEN B.servStartDate ELSE T.lastSuccJobStart END,
			lastSuccJobLevel = B.bkpLevel
		FROM #LastBackupJobs T
			INNER JOIN (
				SELECT J.appId, MAX(J.jobId) AS jobId
				FROM JMBkpStats J WITH (NOLOCK)
				INNER JOIN APP_iDAType I WITH (NOLOCK) ON I.type = J.appType AND I.type <> 106
				INNER JOIN #LastBackupJobs L ON J.appId = L.appId AND J.servStartDate < @i_beforeTime
				LEFT OUTER JOIN #SnapArchGroup SAG ON J.dataArchGrpId = SAG.ArchGrpId
				WHERE (J.bkpLevel IN (1, 1024, 32768) OR @i_fullJobsOnly = 0 AND J.bkpLevel IN (2, 4, 256))
					AND (J.opType IN (4, 14, 18, 30, 43, 76, 87, 91, 94, 97, 98, 101) OR J.opType IN (59,65)
					AND (@SnapJobsQualifySLA = 1 OR @SnapMetSLAIfBackupCopyDisabled = 1 AND ISNULL(SAG.BackupCopyEnabled,0) = 0 AND ISNULL(SAG.SnapVaultExists,0) = 0 OR J.jobIdActedOn = J.jobId))
					AND (J.displayStatus IN (1, 14) OR J.displayStatus = 3 AND I.isCWEjobValid = 1)
					AND J.commCellId = 2
				GROUP BY J.appId
			) S ON T.appId = S.appId
			INNER JOIN JMBkpStats B WITH (NOLOCK) ON S.jobId = B.jobId AND B.commCellId = 2
		WHERE T.appTypeId <> 106
		UPDATE	T SET appId = B.appId, lastSuccJobId = B.jobId, lastSuccParentJobId = B.jobId, lastSuccJobCCId = B.commCellId, lastSuccJobStart = B.servStartDate, lastSuccJobLevel = B.bkpLevel
		FROM	#LastBackupJobs T INNER JOIN (
				SELECT	V.vmClientId, MAX(J.jobId) AS jobId
				FROM	JMBkpStats J WITH (NOLOCK)
						INNER JOIN APP_VMProp V WITH (NOLOCK) ON J.jobId = V.jobId AND J.commCellId = V.commCellId
						INNER JOIN #LastBackupJobs L ON V.vmClientId = L.clientId AND L.appTypeId = 106
						LEFT OUTER JOIN #SnapArchGroup SAG ON J.dataArchGrpId = SAG.ArchGrpId
				WHERE	V.attrName = 'vmStatus' AND V.attrVal IN ('0', '3')
					AND (J.bkpLevel IN (1, 1024, 32768) OR @i_fullJobsOnly = 0 AND J.bkpLevel IN (2, 4, 256))
					AND (J.opType IN (4, 14, 18, 30, 43, 76, 87, 91, 94, 97, 98, 101) OR J.opType IN (59,65)
						AND (@SnapJobsQualifySLA = 1 OR J.jobIdActedOn IN (0, J.jobId)
							OR @SnapMetSLAIfBackupCopyDisabled = 1 AND ISNULL(SAG.BackupCopyEnabled,0) = 0 AND ISNULL(SAG.SnapVaultExists,0) = 0)
						)
					AND J.displayStatus IN (1, 3, 14)
					AND J.servStartDate < @i_beforeTime
					AND J.commCellId = 2
AND J.bkpAttributesEx & 0x4000000 = 0
				GROUP BY V.vmClientId
				) S ON T.clientId = S.vmClientId
				INNER JOIN JMBkpStats B WITH (NOLOCK) ON S.jobId = B.jobId AND B.commCellId = 2
		WHERE	T.appTypeId = 106
	END
	ELSE
	BEGIN
		UPDATE	T SET lastSuccJobId = S.jobId, lastSuccJobCCId = S.commCellId,
				lastSuccJobStart = CASE WHEN S.servStartDate > T.lastSuccJobStart THEN S.servStartDate ELSE T.lastSuccJobStart END, lastSuccJobLevel = S.bkpLevel
		FROM	#LastBackupJobs T INNER JOIN (
				SELECT	J.appId, J.jobId, J.commCellId, J.servStartDate, J.bkpLevel,
						ROW_NUMBER() OVER (PARTITION BY J.appId ORDER BY J.servStartDate DESC) AS rowId
				FROM	JMBkpStats J WITH (NOLOCK)
						INNER JOIN APP_iDAType I WITH (NOLOCK) ON I.type = J.appType AND I.type <> 106
						INNER JOIN #LastBackupJobs L ON J.appId = L.appId AND J.servStartDate < @i_beforeTime
						LEFT OUTER JOIN #SnapArchGroup SAG ON J.dataArchGrpId = SAG.ArchGrpId
				WHERE	(J.bkpLevel IN (1, 1024, 32768) OR @i_fullJobsOnly = 0 AND J.bkpLevel IN (2, 4, 256))
					AND (J.opType IN (4, 14, 18, 30, 43, 76, 87, 91, 94, 97, 98, 101) OR J.opType IN (59,65)
					AND (@SnapJobsQualifySLA = 1 OR @SnapMetSLAIfBackupCopyDisabled = 1 AND ISNULL(SAG.BackupCopyEnabled,0) = 0 AND ISNULL(SAG.SnapVaultExists,0) = 0 OR J.jobIdActedOn = J.jobId))
					AND (J.displayStatus IN (1, 14) OR J.displayStatus = 3 AND I.isCWEjobValid = 1)
					AND J.commCellId > 2
				) S ON T.appId = S.appId
		WHERE	T.appTypeId <> 106 AND S.rowId = 1
		UPDATE	T SET appId = S.appId, lastSuccJobId = S.jobId, lastSuccParentJobId = S.jobId, lastSuccJobCCId = S.commCellId, lastSuccJobStart = S.servStartDate, lastSuccJobLevel = S.bkpLevel
		FROM	#LastBackupJobs T INNER JOIN (
				SELECT	V.vmClientId, J.appId, J.jobId, J.commCellId, J.servStartDate, J.bkpLevel,
						ROW_NUMBER() OVER (PARTITION BY V.vmClientId ORDER BY J.servStartDate DESC) AS rowId
				FROM	JMBkpStats J WITH (NOLOCK)
						INNER JOIN APP_VMProp V WITH (NOLOCK) ON J.jobId = V.jobId AND J.commCellId = V.commCellId
						INNER JOIN #LastBackupJobs L ON V.vmClientId = L.clientId AND L.appTypeId = 106
						LEFT OUTER JOIN #SnapArchGroup SAG ON J.dataArchGrpId = SAG.ArchGrpId
				WHERE	V.attrName = 'vmStatus' AND V.attrVal IN ('0', '3')
					AND (J.bkpLevel IN (1, 1024, 32768) OR @i_fullJobsOnly = 0 AND J.bkpLevel IN (2, 4, 256))
					AND (J.opType IN (4, 14, 18, 30, 43, 76, 87, 91, 94, 97, 98, 101) OR J.opType IN (59,65)
						AND (@SnapJobsQualifySLA = 1 OR J.jobIdActedOn IN (0, J.jobId)
							OR @SnapMetSLAIfBackupCopyDisabled = 1 AND ISNULL(SAG.BackupCopyEnabled,0) = 0 AND ISNULL(SAG.SnapVaultExists,0) = 0)
						)
					AND J.displayStatus IN (1, 3, 14)
					AND J.servStartDate < @i_beforeTime
					AND J.commCellId > 2
AND J.bkpAttributesEx & 0x4000000 = 0
				) S ON T.clientId = S.vmClientId
		WHERE	T.appTypeId = 106 AND S.rowId = 1
	END
	-- For running VSA backup jobs that the individual VM jobs are complete.
	UPDATE	T SET appId = S.applicationId, lastSuccJobId = S.jobId, lastSuccParentJobId = S.jobId, lastSuccJobCCId = S.commCellId, lastSuccJobStart = S.jobStartTime, lastSuccJobLevel = S.bkpLevel
	FROM	#LastBackupJobs T INNER JOIN (
			SELECT	V.vmClientId, B.applicationId, J.jobId, J.commCellId, J.jobStartTime, B.bkpLevel,
					ROW_NUMBER() OVER (PARTITION BY V.vmClientId ORDER BY J.jobStartTime DESC) AS rowId
			FROM	JMJobInfo J WITH (NOLOCK)
					INNER JOIN JMBkpJobInfo B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commcellId
					INNER JOIN APP_VMProp V WITH (NOLOCK) ON J.jobId = V.jobId AND J.commCellId = V.commCellId
					INNER JOIN #LastBackupJobs L ON V.vmClientId = L.clientId AND L.appTypeId = 106
					LEFT OUTER JOIN #SnapArchGroup SAG ON B.currentPolicy = SAG.ArchGrpId
			WHERE	V.attrName = 'vmStatus' AND V.attrVal IN ('0', '3')
				AND (B.bkpLevel IN (1, 1024, 32768) OR @i_fullJobsOnly = 0 AND B.bkpLevel IN (2, 4, 256))
				AND (J.opType IN (4, 14, 18, 30, 43, 76, 87, 91, 94, 97, 98, 101) OR J.opType IN (59,65)
					AND (@SnapJobsQualifySLA = 1 OR B.jobIdActedOn IN (0, B.jobId)
						OR @SnapMetSLAIfBackupCopyDisabled = 1 AND ISNULL(SAG.BackupCopyEnabled,0) = 0 AND ISNULL(SAG.SnapVaultExists,0) = 0)
					)
				AND J.jobStartTime < @i_beforeTime
				AND (@i_foreignJobs = 0 AND J.commCellId = 2 OR @i_foreignJobs = 1 AND J.commCellId > 2)
			) S ON T.clientId = S.vmClientId
	WHERE	T.appTypeId = 106 AND S.rowId = 1
	IF @SnapJobsQualifySLA = 0
	BEGIN
		CREATE TABLE #ProtectedSnapJobs (clientId INT, appId INT, appType INT, jobId INT, parentJobId INT, commCellId INT, status INT, servStartDate INT, bkpLevel INT)
		INSERT	INTO #ProtectedSnapJobs
		SELECT	VM.vmClientId, B.appId, B.appType, B.jobId, B.jobId, B.commCellId, B.displayStatus, B.servStartDate, B.bkpLevel
		FROM	JMBkpStats B WITH (NOLOCK)
				INNER JOIN JMJobDataStats J WITH (NOLOCK) ON B.jobId = J.jobId AND B.commCellId = J.commCellId
				INNER JOIN archGroupCopy AGC WITH (NOLOCK) ON J.archGrpCopyId = AGC.id
				INNER JOIN (
					SELECT	V.vmClientId, V.jobId, V.commCellId, L.lastSuccJobStart
					FROM	APP_VMProp V WITH (NOLOCK)
							INNER JOIN #LastBackupJobs L ON V.vmClientId = L.clientId AND L.appTypeId = 106
					WHERE	V.attrName = 'vmStatus' AND V.attrVal IN ('0', '3')
					) VM ON B.jobId = VM.jobId AND B.commCellId = VM.commCellId
		WHERE	(B.bkpLevel IN (1, 1024, 32768) OR @i_fullJobsOnly = 0 AND B.bkpLevel IN (2, 4, 256))
			AND B.opType IN (59, 65) AND B.displayStatus IN (1, 3, 14)
			AND B.servStartDate < @i_beforeTime AND B.servStartDate > VM.lastSuccJobStart
			AND (@i_foreignJobs = 0 AND B.commCellId = 2 OR @i_foreignJobs = 1 AND B.commCellId > 2)
			AND (J.opType = 61 AND AGC.isSnapCopy = 0 OR
				 J.opType = 13 AND AGC.isSnapCopy = 1 AND AGC.isMirrorCopy = 0
				 AND AGC.id NOT IN (SELECT defaultSnapCopy FROM archGroup WITH (NOLOCK) WHERE id = AGC.archGroupId))
		GROUP BY VM.vmClientId, B.appId, B.appType, B.jobId, B.commCellId, B.displayStatus, B.servStartDate, B.bkpLevel
		HAVING MIN(J.status) = 100 AND MAX(J.status) = 100
		-- JMJobDataStats.jobId of IndexingV2 VM is child jobId
		UPDATE	T SET appId = JL.parentAppId, parentJobId = JL.parentJobId
		FROM	#ProtectedSnapJobs T
				INNER JOIN JMJobDataLink JL WITH (NOLOCK) ON T.jobId = JL.childJobId AND JL.linkType = 7
		WHERE	T.appType = 106 AND T.parentJobId <> JL.parentJobId
		INSERT	INTO #ProtectedSnapJobs
		SELECT	L.clientId, B.appId, B.appType, B.jobId, B.jobId, B.commCellId, B.displayStatus, B.servStartDate, B.bkpLevel
		FROM	JMBkpStats B WITH (NOLOCK)
				INNER JOIN JMJobDataStats J WITH (NOLOCK) ON B.jobId = J.jobId AND B.commCellId = J.commCellId
				INNER JOIN archGroupCopy AGC WITH (NOLOCK) ON J.archGrpCopyId = AGC.id
				INNER JOIN #LastBackupJobs L ON B.appId = L.appId AND L.appTypeId <> 106
		WHERE	(B.bkpLevel IN (1, 1024, 32768) OR @i_fullJobsOnly = 0 AND B.bkpLevel IN (2, 4, 256))
			AND B.opType IN (59, 65) AND B.displayStatus IN (1, 3, 14)
			AND B.servStartDate < @i_beforeTime AND B.servStartDate > L.lastSuccJobStart
			AND (@i_foreignJobs = 0 AND B.commCellId = 2 OR @i_foreignJobs = 1 AND B.commCellId > 2)
			AND (J.opType = 61 AND AGC.isSnapCopy = 0 OR
				 J.opType = 13 AND AGC.isSnapCopy = 1 AND AGC.isMirrorCopy = 0
				 AND AGC.id NOT IN (SELECT defaultSnapCopy FROM archGroup WITH (NOLOCK) WHERE id = AGC.archGroupId))
		GROUP BY L.clientId, B.appId, B.appType, B.jobId, B.commCellId, B.displayStatus, B.servStartDate, B.bkpLevel
		HAVING MIN(J.status) = 100 AND MAX(J.status) = 100
		DELETE	J
		FROM	#ProtectedSnapJobs J
				LEFT OUTER JOIN APP_iDAType I WITH (NOLOCK) ON I.type = J.appType AND I.isCWEjobValid = 1
		WHERE	J.appType <> 106 AND J.status = 3 AND I.type IS NULL
		UPDATE	T SET lastSuccJobId = S.jobId, lastSuccJobCCId = S.commCellId, lastSuccJobStart = S.servStartDate, lastSuccJobLevel = S.bkpLevel
		FROM	#LastBackupJobs T INNER JOIN (
				SELECT	clientId, appId, jobId, commCellId, servStartDate, bkpLevel,
						ROW_NUMBER() OVER (PARTITION BY clientId, appId ORDER BY servStartDate DESC) AS rowId
				FROM	#ProtectedSnapJobs
				) S ON T.clientId = S.clientId AND T.appId = S.appId
		WHERE	T.lastSuccJobStart < S.servStartDate AND S.rowId = 1
		DROP TABLE #ProtectedSnapJobs
	END
	IF EXISTS (SELECT clientId FROM #LastBackupJobs WHERE appTypeId = 106)
		UPDATE	T SET lastSuccJobId = JL.childJobId
		FROM	#LastBackupJobs T
				INNER JOIN APP_Application A WITH (NOLOCK) ON T.clientId = A.clientId
				INNER JOIN JMJobDataLink JL WITH (NOLOCK) ON T.lastSuccparentJobId = JL.parentJobId AND A.id = childAppid AND JL.linkType = 7
		WHERE	T.appTypeId = 106
	-- Consider "Nothing to backup" check for File System subclients
	UPDATE	T SET lastSuccJobStart = CASE WHEN T.lastSuccJobStart > 0 THEN T.lastSuccJobStart ELSE CAST(SP.attrVal AS INT) END,
				  lastSuccJobLevel = CASE WHEN T.lastSuccJobLevel > 0 THEN T.lastSuccJobLevel ELSE 2 END
	FROM	#LastBackupJobs T
			INNER JOIN APP_SubclientProp SP WITH (NOLOCK) ON T.appId = SP.componentNameId AND SP.attrName = 'Last Data Protected Time' AND SP.modified = 0
		AND SP.cs_attrName = CHECKSUM(N'Last Data Protected Time')
	WHERE	T.appTypeId <> 106
END
IF @i_completed IN (0, 2)
BEGIN
	UPDATE	T SET lastFailJobId = S.jobId, lastFailJobCCId = S.commCellId, lastFailJobStart = S.servStartDate,
			lastFailJobLevel = S.bkpLevel, lastFailJobStatus = S.displayStatus, failureErrorCode = S.failureErrorCode,
			failureReasons = dbo.NormalizeForXML(dbo.JMGetLocalizedMessageFunc(@i_localeId, S.failureReason))
	FROM	#LastBackupJobs T INNER JOIN (
			SELECT	J.appId, J.jobId, J.commCellId, J.opType, J.servStartDate, J.bkpLevel, J.displayStatus, J.failureErrorCode, J.failureReason,
					ROW_NUMBER() OVER (PARTITION BY J.appId ORDER BY J.servStartDate DESC) AS rowId
			FROM	JMBkpStats J WITH (NOLOCK)
					INNER JOIN APP_iDAType I WITH (NOLOCK) ON I.type = J.appType AND I.type <> 106
					INNER JOIN #LastBackupJobs L ON J.appId = L.appId AND J.servStartDate < @i_beforeTime
			WHERE	(J.bkpLevel IN (1, 1024, 32768) OR @i_fullJobsOnly = 0 AND J.bkpLevel IN (2, 4, 256))
				AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
				AND J.displayStatus NOT IN (1, 14) AND (J.displayStatus <> 3 OR I.isCWEjobValid = 0)
				AND (@i_foreignJobs = 0 AND J.commCellId = 2 OR @i_foreignJobs = 1 AND J.commCellId > 2)
			) S ON T.appId = S.appId
	WHERE	T.appTypeId <> 106 AND S.rowId = 1
	UPDATE	T SET appId = S.appId, lastFailJobId = S.jobId, lastFailParentJobId = S.jobId, lastFailJobCCId = S.commCellId, lastFailJobStart = S.servStartDate,
			lastFailJobLevel = S.bkpLevel, lastFailJobStatus = S.displayStatus, failureErrorCode = S.failureErrorCode,
			failureReasons = dbo.NormalizeForXML(dbo.JMGetLocalizedMessageFunc(@i_localeId, S.failureReason))
	FROM	#LastBackupJobs T INNER JOIN (
			SELECT	V.vmClientId, J.appId, J.jobId, J.commCellId, J.servStartDate, J.bkpLevel, J.displayStatus, J.failureErrorCode, J.failureReason,
					ROW_NUMBER() OVER (PARTITION BY V.vmClientId ORDER BY J.servStartDate DESC) AS rowId
			FROM	JMBkpStats J WITH (NOLOCK)
					INNER JOIN APP_VMProp V WITH (NOLOCK) ON J.jobId = V.jobId AND J.commCellId = V.commCellId
					INNER JOIN #LastBackupJobs L ON V.vmClientId = L.clientId AND L.appTypeId = 106
			WHERE	V.attrName = 'vmStatus' AND V.attrVal NOT IN ('0', '3')
				AND (J.bkpLevel IN (1, 1024, 32768) OR @i_fullJobsOnly = 0 AND J.bkpLevel IN (2, 4, 256))
				AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
				AND J.displayStatus NOT IN (1, 14)
				AND J.servStartDate < @i_beforeTime
				AND (@i_foreignJobs = 0 AND J.commCellId = 2 OR @i_foreignJobs = 1 AND J.commCellId > 2)
AND J.bkpAttributesEx & 0x4000000 = 0
			) S ON T.clientId = S.vmClientId
	WHERE	T.appTypeId = 106 AND S.rowId = 1
	UPDATE T SET failureReasons = dbo.NormalizeForXML(V.attrVal)
	FROM   #LastBackupJobs T INNER JOIN APP_VMProp V WITH (NOLOCK)
		ON V.jobId = T.lastFailParentJobId AND V.commCellId = T.lastFailJobCCId AND V.VMclientId = T.clientId AND V.attrName = 'vmFailureReason' AND V.attrVal <> ''
	IF EXISTS (SELECT 1 FROM #LastBackupJobs WHERE lastFailJobStatus = 9 AND failureErrorCode = 8)
	BEGIN
		CREATE TABLE #PrevJobsStillRun (
			appId INT, jobId INT, commCellId INT, servEndDate INT, status INT, failureReason NVARCHAR(MAX))
		INSERT	INTO #PrevJobsStillRun
		SELECT	appId, jobId, commCellId, NULL, 0,
				' - Previous job ' + CAST(jobId AS VARCHAR(20)) + ' is still running. '
					+ dbo.NormalizeForXML(dbo.JMGetLocalizedMessageFunc(@i_localeId, failureReason))
		FROM	(SELECT	L.appId, J.jobId, J.commCellId, J.failureReason,
						ROW_NUMBER() OVER (PARTITION BY L.appId ORDER BY J.jobStartTime DESC) AS rowId
				FROM	JMJobInfo J WITH (NOLOCK)
						INNER JOIN JMBkpJobInfo B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId
						INNER JOIN #LastBackupJobs L ON B.applicationId = L.appId AND J.jobStartTime < L.lastFailJobStart
				WHERE	L.lastFailJobStatus = 9 AND L.failureErrorCode = 8
					AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
					AND (@i_foreignJobs = 0 AND J.commCellId = 2 OR @i_foreignJobs = 1 AND J.commCellId > 2)
				) S
		WHERE	S.rowId = 1
		INSERT	INTO #PrevJobsStillRun
		SELECT	appId, jobId, commCellId, servEndDate, displayStatus, failureReason
		FROM
			(SELECT	J.appId, J.jobId, J.commCellId, J.servEndDate, J.displayStatus, J.failureReason,
					ROW_NUMBER() OVER (PARTITION BY J.appId ORDER BY J.servStartDate DESC) AS rowId
			FROM	JMBkpStats J WITH (NOLOCK)
					INNER JOIN #LastBackupJobs L ON J.appId = L.appId AND J.servStartDate < @i_beforeTime
					LEFT OUTER JOIN #PrevJobsStillRun P ON L.appId = P.appId
			WHERE	L.lastFailJobStatus = 9 AND L.failureErrorCode = 8
				AND P.appId IS NULL
				AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
				AND (J.displayStatus <> 9 OR J.failureErrorCode <> 8)
				AND (@i_foreignJobs = 0 AND J.commCellId = 2 OR @i_foreignJobs = 1 AND J.commCellId > 2)
				) S
		WHERE	S.rowId = 1
		UPDATE	#PrevJobsStillRun
		SET		failureReason = ' - Previous job ' + CAST(jobId AS VARCHAR(20)) + ' completed at '
				+ CONVERT(VARCHAR, dbo.UTCToCellLocalTime(dbo.GetDateTimeBig(servEndDate), 2), 20)
		WHERE	status IN (1, 3, 14)
		UPDATE	#PrevJobsStillRun
		SET		failureReason = ' - Previous job ' + CAST(jobId AS VARCHAR(20)) + ' failed at '
				+ CONVERT(VARCHAR, dbo.UTCToCellLocalTime(dbo.GetDateTimeBig(servEndDate), 2), 20)
				+ ' : ' + dbo.NormalizeForXML(dbo.JMGetLocalizedMessageFunc(@i_localeId, failureReason))
		WHERE	status NOT IN (0, 1, 3, 14)
		UPDATE	L SET failureReasons += P.failureReason
		FROM	#LastBackupJobs L
				INNER JOIN #PrevJobsStillRun P ON L.appId = P.appId
	END
	IF EXISTS (SELECT clientId FROM #LastBackupJobs WHERE appTypeId = 106)
		UPDATE	T SET lastFailJobId = JL.childJobId
		FROM	#LastBackupJobs T
				INNER JOIN APP_Application A WITH (NOLOCK) ON T.clientId = A.clientId
				INNER JOIN JMJobDataLink JL WITH (NOLOCK) ON T.lastFailParentJobId = JL.parentJobId AND A.id = childAppid AND JL.linkType = 7
		WHERE	T.appTypeId = 106
END
IF EXISTS (SELECT clientId FROM #LastBackupJobs WHERE appTypeId = 106 AND appId = 0)
BEGIN
	UPDATE	T SET appId = CP.attrVal
	FROM	#LastBackupJobs T INNER JOIN APP_ClientProp CP WITH (NOLOCK) ON T.clientId = CP.componentNameId
			AND CP.attrName = 'Last Backup Subclient' AND CP.modified = 0
	WHERE	T.appTypeId = 106 AND T.appId = 0
END
-- Forever running jobs with optype 116 (STATELESS_BACKUP)
IF EXISTS (SELECT * FROM JMJobInfo WITH (NOLOCK) WHERE opType = 116)
BEGIN
	UPDATE	T SET lastSuccJobId = J.jobId, lastSuccJobCCId = J.commCellId,
			lastSuccJobStart = CASE WHEN J.state = 1 AND J.lastUpdateFromClient = 0 THEN J.jobStartTime ELSE J.lastUpdateFromClient END,
			lastSuccJobLevel = B.bkpLevel
	FROM	#LastBackupJobs T
			INNER JOIN JMBkpJobInfo B WITH (NOLOCK) ON T.appId = B.applicationId
			INNER JOIN JMJobInfo J WITH (NOLOCK) ON B.jobId = J.jobId AND B.commCellId = J.commcellId AND J.opType = 116
	WHERE	(J.state = 1 OR J.lastUpdateFromClient > 0)
		AND (ISNULL(T.lastSuccJobStart, 0) = 0 OR J.lastUpdateFromClient > ISNULL(T.lastSuccJobStart, 0))
END
DROP TABLE #SnapArchGroup
SET NOCOUNT OFF
GO

IF EXISTS (select * from GxQscripts where name = 'RptGetLastBackupJobs')
	delete from GxQscripts where name = 'RptGetLastBackupJobs'
GO

IF EXISTS (select * from GXDBVersions where aliasname='RptGetLastBackupJobs')
	delete from GXDBVersions where aliasname = 'RptGetLastBackupJobs'
GO

insert into GXDBVersions values(2, 'RptGetLastBackupJobs',  'v1.1.2.26.4.2', 'RptGetLastBackupJobs', 'v1.1.2.26.4.2')
GO

