-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'ServiceManager_Report_ActivityManagement_SP_GetListOfManualActivities'
)
DROP PROCEDURE dbo.ServiceManager_Report_ActivityManagement_SP_GetListOfManualActivities
GO
DisplayStringDimvw.DisplayName ActivityTypeDisplayName
from
dbo.ActivityDimvw A
INNER JOIN
dbo.WorkItemDimvw WI
ON A.EntityDimKey = WI.EntityDimKey
LEFT OUTER JOIN
dbo.WorkItemAssignedToUserFactvw WIATU ON
WI.WorkItemDimKey = WIATU.WorkItemDimKey
AND (@IncludeDeleted = 1 OR WIATU.DeletedDate IS NULL)
LEFT OUTER JOIN
dbo.UserDimvw AS AssignedToUserDim ON
WIATU.WorkItemAssignedToUser_UserDimKey = AssignedToUserDim.UserDimKey
INNER JOIN dbo.EntityManagedTypeFactvw EntityOfType
ON EntityOfType.EntityDimKey = A.EntityDimKey
INNER JOIN dbo.ManagedTypeDimvw EntityType
ON EntityType.ManagedTypeDimKey = EntityOfType.ManagedTypeDimKey AND (EntityType.TypeName = 'System.WorkItem.Activity.ManualActivity')
INNER JOIN DisplayStringDimvw
ON DisplayStringDimvw.BaseManagedEntityId = EntityType.BaseManagedEntityId
AND DisplayStringDimvw.LanguageCode = 'ENU'
LEFT OUTER JOIN
(
Select
ParentWIFact.WorkItemContainsActivity_ActivityDimKey ContainedActivityKey,
ParentWI.Id,
COALESCE(ParentCR.ChangeRequestDimKey, ParentInc.IncidentDimKey, ParentRR.ReleaseRecordDimKey) AS ParentDimKey,
CASE WHEN ParentCR.EntityDimKey IS NOT NULL THEN 'System.WorkItem.ChangeRequest'
WHEN ParentInc.EntityDimKey IS NOT NULL THEN 'System.WorkItem.Incident'
WHEN ParentRR.EntityDimKey IS NOT NULL THEN 'System.WorkItem.ReleaseRecord'
ELSE '' END ParentType
from
dbo.WorkItemContainsActivityFactvw ParentWIFact
INNER JOIN
dbo.WorkItemDimvw ParentWI
ON ParentWI.WorkItemDimKey = ParentWIFact.WorkItemDimKey
LEFT OUTER JOIN
dbo.ChangeRequestDimvw ParentCR
ON ParentCR.EntityDimKey = ParentWI.EntityDimKey
LEFT OUTER JOIN
dbo.IncidentDimvw ParentInc
ON ParentInc.EntityDimKey = ParentWI.EntityDimKey
LEFT OUTER JOIN
dbo.ReleaseRecordDimvw ParentRR
ON ParentRR.EntityDimKey = ParentWI.EntityDimKey
WHERE (@IncludeDeleted = 1 OR ParentWIFact.DeletedDate IS NULL)
) ParentWI
ON ParentWI.ContainedActivityKey = A.ActivityDimKey
LEFT OUTER JOIN dbo.ActivityAreavw AreaEnum
ON AreaEnum.ActivityAreaId = A.Area_ActivityAreaId
LEFT OUTER JOIN dbo.DisplayStringDimvw AreaDS
ON AreaEnum.EnumTypeId=AreaDS.BaseManagedEntityId
AND AreaDS.LanguageCode = @LangCode
LEFT OUTER JOIN dbo.ActivityPriorityvw PriorityEnum
ON PriorityEnum.ActivityPriorityId = A.Priority_ActivityPriorityId
LEFT OUTER JOIN dbo.DisplayStringDimvw PriorityDS
ON PriorityEnum.EnumTypeId=PriorityDS.BaseManagedEntityId
AND PriorityDS.LanguageCode = @LangCode
LEFT OUTER JOIN dbo.ActivityStatusvw StatusEnum
ON StatusEnum.ActivityStatusId = A.Status_ActivityStatusId
LEFT OUTER JOIN dbo.DisplayStringDimvw StatusDS
ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId
AND StatusDS.LanguageCode = @LangCode
LEFT OUTER JOIN dbo.ActivityStagevw StageEnum
ON StageEnum.ActivityStageId = A.Stage_ActivityStageId
LEFT OUTER JOIN dbo.DisplayStringDimvw StageDS
ON StageEnum.EnumTypeId=StageDS.BaseManagedEntityId
AND StageDS.LanguageCode = @LangCode
LEFT OUTER JOIN WorkItemContainsActivityFactvw WIAF
ON A.ActivityDimKey = WIAF.WorkItemContainsActivity_ActivityDimKey
AND (@IncludeDeleted = 1 OR WIAF.DeletedDate IS NULL)
LEFT OUTER JOIN dbo.WorkItemAboutConfigItemFactvw AICF
ON AICF.WorkItemDimKey = WI.WorkItemDimKey
AND (@IncludeDeleted = 1 OR AICF.DeletedDate IS NULL)
LEFT OUTER JOIN dbo.ConfigItemDimvw ImpactedCI
ON ImpactedCI.ConfigItemDimKey = AICF.WorkItemAboutConfigItem_ConfigItemDimKey
LEFT OUTER JOIN dbo.ComputerDimvw ImpactedComputer
ON ImpactedCI.EntityDimKey = ImpactedComputer.EntityDimKey
LEFT OUTER JOIN dbo.ConfigItemOwnedByUserFactvw CompOwnerFact
ON CompOwnerFact.ConfigItemDimKey = ImpactedCI.ConfigItemDimKey
AND (@IncludeDeleted = 1 OR CompOwnerFact.DeletedDate IS NULL)
LEFT OUTER JOIN UserDimvw ComputerOwner
ON ComputerOwner.UserDimKey = CompOwnerFact.ConfigItemOwnedByUser_UserDimKey
WHERE
((@Name IS NULL) OR (A.Title LIKE @Name)) AND
(
(@Id IS NULL)
OR
(
A.Id IN
(
SELECT string FROM dbo.fn_CSVToTableString(ISNULL(@Id,''))
)
)
) AND
((@AssignedTo = 0) OR (@AssignedTo = AssignedToUserDim.UserDimKey)) AND
((@ScheduledStartFrom IS NULL) OR (A.ScheduledStartDate >= @ScheduledStartFrom)) AND
((@ScheduledStartTo IS NULL) OR (A.ScheduledStartDate < @ScheduledStartTo)) AND
((@ScheduledEndFrom IS NULL) OR (A.ScheduledEndDate >= @ScheduledEndFrom)) AND
((@ScheduledEndTo IS NULL) OR (A.ScheduledEndDate < @ScheduledEndTo)) AND
((@ActualStartFrom IS NULL) OR (A.ActualStartDate >= @ActualStartFrom)) AND
((@ActualStartTo IS NULL) OR (A.ActualStartDate < @ActualStartTo)) AND
((@ActualEndFrom IS NULL) OR (A.ActualEndDate >= @ActualEndFrom)) AND
((@ActualEndTo IS NULL) OR (A.ActualEndDate < @ActualEndTo)) AND
((A.CreatedDate >= @CreatedOnFrom) AND (A.CreatedDate < @CreatedOnTo)) AND
((-1 IN (Select value from @tableStatus)) OR (StatusEnum.ActivityStatusId IN (Select value from @tableStatus))) AND
((-1 IN (Select value from @tablePriority)) OR (PriorityEnum.ActivityPriorityId IN (Select value from @tablePriority))) AND
((-1 IN (Select value from @tableArea)) OR (AreaEnum.ActivityAreaId IN (Select value from @tableArea))) AND
((-1 IN (Select value from @tableStage)) OR (StageEnum.ActivityStageId IN (Select value from @tableStage))) AND
-- NO CI filtering required
((@CIName = 0)
OR
(WI.WorkItemDimKey IN
(
SELECT DISTINCT
AICFact.WorkItemDimKey
FROM
dbo.WorkItemAboutConfigItemFactvw AICFact
INNER JOIN dbo.ConfigItemDimvw CI
ON AICFact.WorkItemAboutConfigItem_ConfigItemDimKey = CI.ConfigItemDimKey
AND (@IncludeDeleted = 1 OR AICFact.DeletedDate IS NULL)
WHERE
((@CIName =0) OR (CI.ConfigItemDimKey = @CIName))
)
))
ORDER BY A.Id
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_ActivityManagement_SP_GetListOfManualActivities TO reportuser
GO
Home