-- 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_GetManualActivityDetails'
)
DROP PROCEDURE dbo.ServiceManager_Report_ActivityManagement_SP_GetManualActivityDetails
GO
CREATE PROCEDURE dbo.ServiceManager_Report_ActivityManagement_SP_GetManualActivityDetails
@LangCode nvarchar(16) = 'ENU',
@Id int = 0,
@IncludeDeleted bit = 0
INNER JOIN dbo.WorkItemDimvw WI
ON A.EntityDimKey = WI.EntityDimKey
INNER JOIN dbo.EntityManagedTypeFactvw
ON A.EntityDimKey = dbo.EntityManagedTypeFactvw.EntityDimKey
INNER JOIN dbo.ManagedTypeDimvw ActivityType
on dbo.EntityManagedTypeFactvw.ManagedTypeDimKey = ActivityType.ManagedTypeDimKey
LEFT OUTER JOIN DisplayStringDimvw ActivityTypeName
ON ActivityTypeName.BaseManagedEntityId = ActivityType.ManagedTypeId
AND ActivityTypeName.LanguageCode = @LangCode
LEFT OUTER JOIN dbo.WorkItemAssignedToUserFactvw AATUFact
ON AATUFact.WorkItemDimKey = WI.WorkItemDimKey
AND (@IncludeDeleted = 1 OR AATUFact.DeletedDate IS NULL )
LEFT OUTER JOIN dbo.UserDimvw AssignedTo
ON AssignedTo.UserDimKey = AATUFact.WorkItemAssignedToUser_UserDimKey
LEFT OUTER JOIN dbo.ActivityAreavw AreaEnum
ON AreaEnum.ActivityAreaId = A.Area_ActivityAreaId
LEFT OUTER JOIN dbo.DisplayStringDimvw AreaEnumDS
ON AreaEnumDS.BaseManagedEntityId = AreaEnum.EnumTypeId
AND AreaEnumDS.LanguageCode = @LangCode
LEFT OUTER JOIN dbo.ActivityPriorityvw PriorityEnum
ON PriorityEnum.ActivityPriorityId = A.Priority_ActivityPriorityId
LEFT OUTER JOIN dbo.DisplayStringDimvw PriorityEnumDS
ON PriorityEnumDS.BaseManagedEntityId = PriorityEnum.EnumTypeId
AND PriorityEnumDS.LanguageCode = @LangCode
LEFT OUTER JOIN dbo.ActivityStatusvw StatusEnum
ON StatusEnum.ActivityStatusId = A.Status_ActivityStatusId
LEFT OUTER JOIN dbo.DisplayStringDimvw StatusEnumDS
ON StatusEnumDS.BaseManagedEntityId = StatusEnum.EnumTypeId
AND StatusEnumDS.LanguageCode = @LangCode
LEFT OUTER JOIN dbo.ActivityStagevw StageEnum
ON StageEnum.ActivityStageId = A.Stage_ActivityStageId
LEFT OUTER JOIN dbo.DisplayStringDimvw StageEnumDS
ON StageEnumDS.BaseManagedEntityId = StageEnum.EnumTypeId
AND StageEnumDS.LanguageCode = @LangCode
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 AICF.WorkItemAboutConfigItem_ConfigItemDimKey = ImpactedCI.ConfigItemDimKey
AND ImpactedCI.IsDeleted = 0
LEFT OUTER JOIN dbo.ConfigItemObjectStatusvw ObjectStatusEnum
ON ObjectStatusEnum.ConfigItemObjectStatusId = ImpactedCI.ObjectStatus_ConfigItemObjectStatusId
LEFT OUTER JOIN dbo.DisplayStringDimvw ObjectStatusEnumDS
ON ObjectStatusEnumDS.BaseManagedEntityId = ObjectStatusEnum.EnumTypeId
AND ObjectStatusEnumDS.LanguageCode = @LangCode
LEFT OUTER JOIN EntityManagedTypeFactvw CITypeFct
ON ImpactedCI.EntityDimKey = CITypeFct.EntityDimKey
AND (@IncludeDeleted = 1 OR CITypeFct.DeletedDate IS NULL)
LEFT OUTER JOIN dbo.ManagedTypeDimvw ImpactedCIType
ON ImpactedCIType.ManagedTypeDimKey = CITypeFct.ManagedTypeDimKey
LEFT OUTER JOIN dbo.DisplayStringDimvw ImpactedCITypeDS
ON ImpactedCITypeDS.BaseManagedEntityId = ImpactedCIType.BaseManagedTypeId
AND ImpactedCITypeDS.LanguageCode = @LangCode
LEFT OUTER JOIN dbo.ConfigItemOwnedByUserFactvw CIOwnerFact
ON CIOwnerFact.ConfigItemDimKey = ImpactedCI.ConfigItemDimKey
AND (@IncludeDeleted = 1 OR CIOwnerFact.DeletedDate IS NULL)
LEFT OUTER JOIN UserDimvw CIOwner
ON CIOwner.UserDimKey = CIOwnerFact.ConfigItemOwnedByUser_UserDimKey
AND CIOwner.IsDeleted = 0
Home