dspConduct

Content WebApp Example: Role Status BY User Views

NOTE: The Unbounded Security view must be created prior to creating these views.

 

/****** Object:  View [dbo].[webRequest_RoleStatusByUser0Sel]    Script Date: 3/2/2016 10:47:00 PM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

 

CREATE VIEW [dbo].[webRequest_RoleStatusByUser0Sel]

AS

SELECT        dbo.ttRequest.RequestID, dbo.ttRequestRole.RoleID, dbo.ttRequestRoleSLA.DueDate, dbo.ttRequestRoleSLA.LastEvaluation

FROM            dbo.ttRequest INNER JOIN

                         dbo.ttRequestRole ON dbo.ttRequest.RequestID = dbo.ttRequestRole.RequestID INNER JOIN

                         dbo.ttRequestRoleSLA ON dbo.ttRequestRole.RequestID = dbo.ttRequestRoleSLA.RequestID AND dbo.ttRequestRole.RoleID = dbo.ttRequestRoleSLA.RoleID

WHERE        (dbo.ttRequestRole.DependencyComplete = 1) AND (dbo.ttRequestRole.Finished = 0)

 

 

GO

 

/****** Object:  View [dbo].[webRequest_RoleStatusByUser1Sel]    Script Date: 3/2/2016 10:47:00 PM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

 

CREATE VIEW [dbo].[webRequest_RoleStatusByUser1Sel]

AS

SELECT        dbo.webSecurity_UnboundedSel.RequestID, dbo.webSecurity_UnboundedSel.RoleID, dbo.webSecurity_UnboundedSel.boaUserID, CASE WHEN SubmittedOn IS NULL 

                         THEN '0' WHEN Finished = '1' THEN '1' WHEN Finished = '0' AND DependencyComplete = '0' THEN '2' WHEN Finished = '0' AND DependencyComplete = '1' AND 

                         NOT COALESCE (webRequest_RoleStatusByUser0Sel.DueDate, GETDATE()) < GETDATE() THEN '3' WHEN Finished = '0' AND DependencyComplete = '1' AND 

                         webRequest_RoleStatusByUser0Sel.DueDate < GETDATE() THEN '4' END AS RoleStatus

FROM            dbo.webSecurity_UnboundedSel INNER JOIN

                         dbo.ttRequestRole ON dbo.webSecurity_UnboundedSel.RequestID = dbo.ttRequestRole.RequestID AND dbo.webSecurity_UnboundedSel.RoleID = dbo.ttRequestRole.RoleID INNER JOIN

                         dbo.ttRequest ON dbo.ttRequestRole.RequestID = dbo.ttRequest.RequestID LEFT OUTER JOIN

                         dbo.webRequest_RoleStatusByUser0Sel ON dbo.ttRequestRole.RequestID = dbo.webRequest_RoleStatusByUser0Sel.RequestID AND dbo.ttRequestRole.RoleID = dbo.webRequest_RoleStatusByUser0Sel.RoleID

 

 

GO

 

/****** Object:  View [dbo].[webRequest_RoleStatusByUser2Sel]    Script Date: 3/2/2016 10:47:00 PM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

 

CREATE VIEW [dbo].[webRequest_RoleStatusByUser2Sel]

AS

SELECT        RequestID, boaUserID, MAX(CAST(RoleStatus AS int)) AS RoleStatus

FROM            dbo.webRequest_RoleStatusByUser1Sel

GROUP BY RequestID, boaUserID

 

 

GO

 

/****** Object:  View [dbo].[webRequest_RoleStatusByUser3Sel]    Script Date: 3/2/2016 10:47:00 PM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

 

CREATE VIEW [dbo].[webRequest_RoleStatusByUser3Sel]

AS

SELECT        RequestID, boaUserID, CASE WHEN RoleStatus = '1' THEN 'BlueCheck' WHEN RoleStatus = '3' THEN 'GreenPlus' WHEN RoleStatus = '4' THEN 'RedX' ELSE 'YellowYieldWithExclamation' END AS RS

FROM            dbo.webRequest_RoleStatusByUser2Sel

 

 

GO