ServiceManager.Report.IncidentManagement.SP.GetIncidentDetails.Install (Resource)

Element properties:

Source Code:

<Resource ID="ServiceManager.Report.IncidentManagement.SP.GetIncidentDetails.Install" Accessibility="Public" FileName="ServiceManager.Report.IncidentManagement.SP.GetIncidentDetails.Install.sql"/>

File Content: ServiceManager.Report.IncidentManagement.SP.GetIncidentDetails.Install.sql

SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO





-- 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_IncidentManagement_SP_GetIncidentDetails' 


)


   DROP PROCEDURE dbo.ServiceManager_Report_IncidentManagement_SP_GetIncidentDetails


GO





CREATE PROCEDURE dbo.ServiceManager_Report_IncidentManagement_SP_GetIncidentDetails


	@LangCode nvarchar(16) = 'ENU',


	@IncidentId			int  = 0,


    @IncludeDeleted bit = 0


	


AS


BEGIN


  SET NOCOUNT ON





  DECLARE @Error int


  DECLARE @ExecError int


	  


	SELECT DISTINCT


	


	I.IncidentDimKey,


	WI.WorkItemDimKey,


	I.Id,


	I.CreatedDate,


	I.DisplayName,


	I.Description,


	


	AssignedToUser = AssignedToUser.DisplayName,


	CreatedBy = CreatedByUser.DisplayName,


	AffectedUser = AffectedUser.DisplayName,


	ResolvedByUser = ResolvedByUser.DisplayName,


	PrimaryOwner = PrimaryOwner.DisplayName,


	


	IncidentClassificationValue = 


	ISNULL(ClassificationEnumDS.DisplayName, ClassificationEnum.IncidentClassificationValue),


	


	IncidentSourceValue = 


	ISNULL(SourceEnumDS.DisplayName, SourceEnum.IncidentSourceValue),


	


	IncidentImpactValue = 


	ISNULL(ImpactEnumDS.DisplayName, ImpactEnum.IncidentImpactValue),


	


	IncidentUrgencyValue = 


	ISNULL(UrgencyEnumDS.DisplayName, UrgencyEnum.IncidentUrgencyValue),


	


	IncidentTierQueuesValue = 


	ISNULL(TierQueueEnumDS.DisplayName, TierQueueEnum.IncidentTierQueuesValue),


	


	IncidentStatusValue = 


	ISNULL(StatusEnumDS.DisplayName, StatusEnum.IncidentStatusValue),


	


	IncidentResolutionCategoryValue = 


	ISNULL(ResolutionCategoryEnumDS.DisplayName, ResolutionCategoryEnum.IncidentResolutionCategoryValue),


	


	I.Priority,


	I.ActualStartDate AS TimeAdded,


	I.Escalated,


	I.ContactMethod,


	I.ResolvedDate,


	I.ClosedDate,


	I.ScheduledStartDate,


	I.ScheduledEndDate,


	I.ActualStartDate,


	I.ActualEndDate,


	I.ResolutionDescription


	


	FROM 


	dbo.IncidentDimvw I





	Inner join


		dbo.WorkItemDimvw WI


		ON I.EntityDimKey = WI.EntityDimKey


		 


	Left Outer Join dbo.IncidentClassificationvw ClassificationEnum 


		ON I.Classification_IncidentClassificationId = ClassificationEnum.IncidentClassificationId


	


	Left Outer Join dbo.DisplayStringDimvw ClassificationEnumDS


		ON ClassificationEnum.EnumTypeId = ClassificationEnumDS.BaseManagedEntityId


		AND ClassificationEnumDS.LanguageCode = @LangCode


	


	Left Outer Join dbo.IncidentSourcevw SourceEnum 


		ON  I.Source_IncidentSourceId = SourceEnum.IncidentSourceId


			


	Left Outer Join dbo.DisplayStringDimvw SourceEnumDS


		ON SourceEnum.EnumTypeId = SourceEnumDS.BaseManagedEntityId


		AND SourceEnumDS.LanguageCode = @LangCode





	Left Outer Join dbo.IncidentImpactvw ImpactEnum


		ON  I.Impact_IncidentImpactId = ImpactEnum.IncidentImpactId





	Left Outer Join dbo.DisplayStringDimvw ImpactEnumDS


		ON ImpactEnum.EnumTypeId = ImpactEnumDS.BaseManagedEntityId


		AND ImpactEnumDS.LanguageCode = @LangCode


		


	Left Outer Join dbo.IncidentUrgencyvw UrgencyEnum


		ON  I.Urgency_IncidentUrgencyId = UrgencyEnum.IncidentUrgencyId





	Left Outer Join dbo.DisplayStringDimvw UrgencyEnumDS


		ON UrgencyEnum.EnumTypeId = UrgencyEnumDS.BaseManagedEntityId


		AND UrgencyEnumDS.LanguageCode = @LangCode





	Left Outer Join dbo.IncidentTierQueuesvw TierQueueEnum


		ON  I.TierQueue_IncidentTierQueuesId = TierQueueEnum.IncidentTierQueuesId


		


	Left Outer Join dbo.DisplayStringDimvw TierQueueEnumDS


		ON TierQueueEnum.EnumTypeId = TierQueueEnumDS.BaseManagedEntityId


		AND TierQueueEnumDS.LanguageCode = @LangCode	


		


	Left Outer Join dbo.IncidentStatusvw StatusEnum


		ON  I.Status_IncidentStatusId = StatusEnum.IncidentStatusId





	Left Outer Join dbo.DisplayStringDimvw StatusEnumDS


		ON StatusEnum.EnumTypeId = StatusEnumDS.BaseManagedEntityId


		AND StatusEnumDS.LanguageCode = @LangCode			





	Left Outer Join dbo.IncidentResolutionCategoryvw ResolutionCategoryEnum


		ON  I.ResolutionCategory_IncidentResolutionCategoryId = ResolutionCategoryEnum.IncidentResolutionCategoryId


				


	Left Outer Join dbo.DisplayStringDimvw ResolutionCategoryEnumDS


		ON ResolutionCategoryEnum.EnumTypeId = ResolutionCategoryEnumDS.BaseManagedEntityId


		AND ResolutionCategoryEnumDS.LanguageCode = @LangCode	


				


	Left Outer Join dbo.WorkItemAssignedToUserFactvw IATUFact 


		ON WI.WorkItemDimKey = IATUFact.WorkItemDimKey


		And (@IncludeDeleted = 1 OR IATUFact.DeletedDate IS NULL)


		


	Left Outer Join dbo.UserDimvw AssignedToUser 


		ON IATUFact.WorkItemAssignedToUser_UserDimKey = AssignedToUser.UserDimKey





	Left Outer Join dbo.WorkItemAffectedUserFactvw IHAUFact


		ON IHAUFact.WorkItemDimKey = WI.WorkItemDimKey


		And (@IncludeDeleted = 1 OR IHAUFact.DeletedDate IS NULL)





	Left Outer Join dbo.UserDimvw AffectedUser 


		ON IHAUFact.WorkItemAffectedUser_UserDimKey = AffectedUser.UserDimKey


		


	Left Outer Join dbo.WorkItemCreatedByUserFactvw ICBUFact


		ON ICBUFact.WorkItemDimKey = WI.WorkItemDimKey


		AND (@IncludeDeleted = 1 OR ICBUFact.DeletedDate IS NULL)





	Left Outer Join dbo.UserDimvw CreatedByUser 


		ON ICBUFact.WorkItemCreatedByUser_UserDimKey = CreatedByUser.UserDimKey


		


	Left Outer Join dbo.IncidentResolvedByUserFactvw IRBUFact


		ON IRBUFact.IncidentDimKey = I.IncidentDimKey


		AND (@IncludeDeleted = 1 OR IRBUFact.DeletedDate IS NULL)


		


	Left Outer Join dbo.UserDimvw ResolvedByUser 


		ON IRBUFact.TroubleTicketResolvedByUser_UserDimKey = ResolvedByUser.UserDimKey


		


	Left Outer Join IncidentHasPrimaryOwnerFactvw IHPOFact


		ON IHPOFact.IncidentDimKey = I.IncidentDimKey


		AND (@IncludeDeleted = 1 OR IHPOFact.DeletedDate IS NULL)





	Left Outer Join dbo.UserDimvw PrimaryOwner 


		ON IHPOFact.IncidentPrimaryOwner_UserDimKey = PrimaryOwner.UserDimKey


	


	WHERE I.IncidentDimKey = @IncidentId





UNION





Select 


--Temporary fix for v1 because workitemdim key can temporarily be empty even though incident exists


null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null,


null, null, null, null, null, null, null, null, null





Order by WorkItemDimKey DESC	 





  SET @Error = @@ERROR





QuitError:


  


  RETURN @Error


END


GO








GRANT EXECUTE ON dbo.ServiceManager_Report_IncidentManagement_SP_GetIncidentDetails TO reportuser


GO