-- 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_GetReviewActivityDetails'
)
DROP PROCEDURE dbo.ServiceManager_Report_ActivityManagement_SP_GetReviewActivityDetails
GO
CREATE PROCEDURE dbo.ServiceManager_Report_ActivityManagement_SP_GetReviewActivityDetails
@LangCode nvarchar(16) = 'ENU',
@Id int = 0,
@IncludeDeleted bit = 0
INNER JOIN dbo.WorkItemDimvw WI
ON A.EntityDimKey = WI.EntityDimKey
INNER JOIN dbo.EntityManagedTypeFactvw EntityOfType
ON EntityOfType.EntityDimKey = A.EntityDimKey
AND (@IncludeDeleted = 1 OR EntityOfType.DeletedDate IS NULL )
INNER JOIN dbo.ManagedTypeDimvw EntityType
ON EntityType.ManagedTypeDimKey = EntityOfType.ManagedTypeDimKey
LEFT OUTER JOIN dbo.ReviewActivityDimvw RA
ON RA.EntityDimKey = A.EntityDimKey
LEFT OUTER JOIN dbo.ReviewActivityApprovalvw ApprovalEnum
ON ApprovalEnum.ReviewActivityApprovalId = RA.ApprovalCondition_ReviewActivityApprovalId
LEFT OUTER JOIN dbo.DisplayStringDimvw ApprovalEnumDS
ON ApprovalEnumDS.BaseManagedEntityId = ApprovalEnum.EnumTypeId
AND ApprovalEnumDS.LanguageCode = @LangCode
LEFT OUTER JOIN DisplayStringDimvw EntityTypeDS
ON EntityTypeDS.BaseManagedEntityId = EntityType.BaseManagedEntityId
AND EntityTypeDS.LanguageCode = @LangCode
LEFT OUTER JOIN dbo.ReviewActivityHasReviewerFactvw RelatedReviewer
ON RelatedReviewer.ActivityDimKey = A.ActivityDimKey
AND (@IncludeDeleted = 1 OR RelatedReviewer.DeletedDate IS NULL )
LEFT OUTER JOIN dbo.ReviewerDimvw Reviewer
ON RelatedReviewer.ReviewActivityHasReviewer_ReviewerDimKey = Reviewer.ReviewerDimKey
LEFT OUTER JOIN dbo.ReviewerVotedByUserFactvw votedByUserFct
on votedByUserFct.ReviewerDimKey = Reviewer.ReviewerDimKey
and (@IncludeDeleted = 1 OR votedByUserFct.DeletedDate IS NULL)
LEFT OUTER JOIN dbo.UserDimvw VotedByUser
ON VotedByUser.UserDimKey = votedByUserFct.ReviewerVotedByUser_UserDimKey
LEFT OUTER JOIN dbo.ReviewerIsUserFactvw reviewerIsUserFct
on reviewerIsUserFct.ReviewerDimKey = Reviewer.ReviewerDimKey
and (@IncludeDeleted = 1 OR reviewerIsUserFct.DeletedDate IS NULL)
LEFT OUTER JOIN dbo.UserDimvw ReviewerUser
ON ReviewerUser.UserDimKey = reviewerIsUserFct.ReviewerIsUser_UserDimKey
LEFT OUTER JOIN dbo.ReviewerDecisionvw DecisionEnum
on DecisionEnum.ReviewerDecisionId = Reviewer.Decision_ReviewerDecisionId
LEFT OUTER JOIN dbo.DisplayStringDimvw DecisionEnumDS
ON DecisionEnumDS.BaseManagedEntityId = DecisionEnum.EnumTypeId
AND DecisionEnumDS.LanguageCode = @LangCode
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.WorkItemContainsActivityFactvw WIAF
ON A.ActivityDimKey = WIAF.WorkItemContainsActivity_ActivityDimKey
AND (@IncludeDeleted = 1 OR WIAF.DeletedDate IS NULL)
LEFT OUTER JOIN dbo.WorkItemDimvw ParentWI
ON ParentWI.WorkItemDimKey = WIAF.WorkItemDimKey
Home