Master Data Management

Content WebApp Examples

The following examples can be used as reference when creating the Content WebApp.

NOTE: Users can add two columns, one containing the role owner and the other the SLA minutes late for the request roles, to the Requests page in the Content WebApp using an API view included with the Stewardship Tier. Include the view apiRequestAllOwnersMinutesLateSel.sql using an outer join in the Content WebApp’s Request page view to show Minutes Late and Role Owner for each request.

Examples for MDM to Call boa (Add/Edit/Delete) Functions

The bit flag “Exists” must always have a 0 or 1 value and depicts if the data already exists in the system(s) of record.

In the examples below, “ControlView” refers to any control view that the WebApp Designer wants to use. These are commonly the Data or Page Control Views that can be generated from the Scenario Role Task Page in MDM.  Refer to Create Tables and Views for Content WebApp Pages Overview for more information.

 

DGE.dbo.apiSecurityScenarioEdit([PageTable].Exists, ControlView.ScenarioID) * DGE.dbo.apiRoleEdit(ControlView.RoleID, ControlView.TaskID)

as boaEdit

 

DGE.dbo.apiSecurityScenarioDel(PageTable.Exists) * ISNULL(DGE.dbo.apiRoleEdit(ControlView.RoleID, ControlView.TaskID), 0)

as boaDel

 

ISNULL(DGE.dbo.apiSecurityScenarioEdit(pt.Exists, dbo.ttRequest.ScenarioID), 0) * ISNULL(DGE.dbo.apiRoleEdit(dcv.RoleID, dcv.TaskID), 0) AS boaAdd

Security Views for Binding Security by OrgUnits and Security that is not Bounded by any OrgUnits

NOTE: This view must be created prior to creating the Role Status BY User Views.

 

USE [DGE]

GO

 

/****** Object:  Table [dbo].[ttSecurityCacheOrgUnit2]    Script Date: 4/22/2016 9:06:07 AM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ttSecurityCacheOrgUnit2]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[ttSecurityCacheOrgUnit2](

              [UserID] [nvarchar](50) NOT NULL,

              [RoleID] [uniqueidentifier] NOT NULL,

              [OrgUnitValue] [nvarchar](50) NOT NULL,

              [ReadOnly] [bit] NOT NULL,

 CONSTRAINT [PK_ttSecurityCacheOrgUnit2] PRIMARY KEY CLUSTERED

(

              [RoleID] ASC,

              [UserID] ASC,

              [OrgUnitValue] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)

)

END

GO

 

/****** Object:  Table [dbo].[ttSecurityCacheOrgUnit3]    Script Date: 4/22/2016 9:06:07 AM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ttSecurityCacheOrgUnit3]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[ttSecurityCacheOrgUnit3](

              [UserID] [nvarchar](50) NOT NULL,

              [RoleID] [uniqueidentifier] NOT NULL,

              [OrgUnitValue] [nvarchar](50) NOT NULL,

              [ReadOnly] [bit] NOT NULL,

 CONSTRAINT [PK_ttSecurityCacheOrgUnit3] PRIMARY KEY CLUSTERED

(

              [RoleID] ASC,

              [UserID] ASC,

              [OrgUnitValue] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)

)

END

GO

 

/****** Object:  Table [dbo].[ttSecurityCacheOrgUnit1]    Script Date: 4/22/2016 9:06:07 AM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ttSecurityCacheOrgUnit1]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[ttSecurityCacheOrgUnit1](

              [UserID] [nvarchar](50) NOT NULL,

              [RoleID] [uniqueidentifier] NOT NULL,

              [OrgUnitValue] [nvarchar](50) NOT NULL,

              [ReadOnly] [bit] NOT NULL DEFAULT ((0)),

 CONSTRAINT [PK_ttSecurityCacheOrgUnit1] PRIMARY KEY CLUSTERED

(

              [RoleID] ASC,

              [UserID] ASC,

              [OrgUnitValue] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)

)

END

GO

 

/*NOTE:  Replace “dgeNameOfContentApp” below with the database name where these views need to be created. There should be only one occurrence.*/

 

USE [dgeNameOfContentApp]

GO

 

/****** Object:  View [dbo].[ttSecurityCacheOrgUnit3]    Script Date: 4/22/2016 9:06:07 AM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ttSecurityCacheOrgUnit3]'))

EXEC dbo.sp_executesql @statement = N'

 

CREATE VIEW [dbo].[ttSecurityCacheOrgUnit3]

AS

SELECT        UserID, RoleID, OrgUnitValue, ReadOnly

FROM            DGE.dbo.ttSecurityCacheOrgUnit3

 

 

'

GO

 

/****** Object:  View [dbo].[ttSecurityCacheOrgUnit2]    Script Date: 4/22/2016 9:06:07 AM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ttSecurityCacheOrgUnit2]'))

EXEC dbo.sp_executesql @statement = N'

CREATE VIEW [dbo].[ttSecurityCacheOrgUnit2]

AS

SELECT        UserID, RoleID, OrgUnitValue, ReadOnly

FROM            DGE.dbo.ttSecurityCacheOrgUnit2

 

'

GO

 

/****** Object:  View [dbo].[ttSecurityCacheOrgUnit1]    Script Date: 4/22/2016 9:06:07 AM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ttSecurityCacheOrgUnit1]'))

EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[ttSecurityCacheOrgUnit1]

AS

SELECT        UserID, RoleID, OrgUnitValue, ReadOnly

FROM            DGE.dbo.ttSecurityCacheOrgUnit1

'

GO

 

USE [DGE]

GO

 

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DF__ttSecurit__ReadO__0EF1425F]') AND type = 'D')

BEGIN

ALTER TABLE [dbo].[ttSecurityCacheOrgUnit2] ADD  DEFAULT ((0)) FOR [ReadOnly]

END

 

GO

 

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DF__ttSecurit__ReadO__0FE56698]') AND type = 'D')

BEGIN

ALTER TABLE [dbo].[ttSecurityCacheOrgUnit3] ADD  DEFAULT ((0)) FOR [ReadOnly]

END

 

GO

 

CREATE VIEW [dbo].[webSecurity_UnboundedSel]

AS

SELECT        RequestID, RoleID, UserID AS boaUserID, ReadOnly

FROM            dbo.ttRequestRoleUser

 

GO

 

 

CREATE VIEW [dbo].[webSecurity_BoundByOrgUnit1Sel]

AS

SELECT        dbo.ttRequestRoleUser.RequestID, dbo.ttSecurityCacheOrgUnit1.RoleID, dbo.ttSecurityCacheOrgUnit1.OrgUnitValue AS OrgUnit1, dbo.ttSecurityCacheOrgUnit1.UserID AS boaUserID,

                         dbo.ttSecurityCacheOrgUnit1.ReadOnly

FROM            dbo.ttSecurityCacheOrgUnit1 INNER JOIN

                         dbo.ttRequestRoleUser ON dbo.ttSecurityCacheOrgUnit1.RoleID = dbo.ttRequestRoleUser.RoleID AND dbo.ttSecurityCacheOrgUnit1.UserID = dbo.ttRequestRoleUser.UserID

 

GO

 

/****** Object:  View [dbo].[webSecurity_BoundByOrgUnit2Sel]    Script Date: 3/1/2016 10:39:11 PM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE VIEW [dbo].[webSecurity_BoundByOrgUnit2Sel]

AS

SELECT        dbo.ttRequestRoleUser.RequestID, dbo.ttSecurityCacheOrgUnit2.RoleID, dbo.ttSecurityCacheOrgUnit2.OrgUnitValue AS OrgUnit2, dbo.ttSecurityCacheOrgUnit2.UserID AS boaUserID,

                         dbo.ttSecurityCacheOrgUnit2.ReadOnly

FROM            dbo.ttSecurityCacheOrgUnit2 INNER JOIN

                         dbo.ttRequestRoleUser ON dbo.ttSecurityCacheOrgUnit2.RoleID = dbo.ttRequestRoleUser.RoleID AND dbo.ttSecurityCacheOrgUnit2.UserID = dbo.ttRequestRoleUser.UserID

 

GO

 

/****** Object:  View [dbo].[webSecurity_BoundByOrgUnit3Sel]    Script Date: 3/1/2016 10:39:11 PM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE VIEW [dbo].[webSecurity_BoundByOrgUnit3Sel]

AS

SELECT        dbo.ttRequestRoleUser.RequestID, dbo.ttSecurityCacheOrgUnit3.RoleID, dbo.ttSecurityCacheOrgUnit3.OrgUnitValue AS OrgUnit3, dbo.ttSecurityCacheOrgUnit3.UserID AS boaUserID,

                         dbo.ttSecurityCacheOrgUnit3.ReadOnly

FROM            dbo.ttSecurityCacheOrgUnit3 INNER JOIN

                         dbo.ttRequestRoleUser ON dbo.ttSecurityCacheOrgUnit3.UserID = dbo.ttRequestRoleUser.UserID AND dbo.ttSecurityCacheOrgUnit3.RoleID = dbo.ttRequestRoleUser.RoleID

 

GO

/*A Content Request page may need ScenarioID to be set on the Vertical View to drive configuration for scenario-based field controls on the Request page.  In this case, the Request page must have an INSERT MODE of “Horizontal Insert/Switch To Vertical” and the following Trigger that calls the above-suggested OnValidate procedure at the trigger level.*/

 

CREATE TRIGGER [dbo].[trgRequest_BusinessProcessScenarioIns] ON [dbo].[ttRequest] 

FOR INSERT 

AS 

DECLARE @BusinessProcessID uniqueidentifier 

DECLARE @ScenarioID uniqueidentifier

DECLARE @ContentRequestID uniqueidentifier

DECLARE @RequestID int 

DECLARE @boaUserID nvarchar(50)

IF UPDATE (ScenarioID) 

BEGIN 

SET @ScenarioID = ISNULL((SELECT ScenarioID FROM INSERTED), NULL) 

SET @ContentRequestID = (SELECT ContentRequestID FROM INSERTED)

SET @boaUserID = (SELECT AddedBy FROM INSERTED) 

IF (@ScenarioID IS NULL) 

BEGIN 

SET @BusinessProcessID = (SELECT BusinessProcessID FROM ttRequest WHEREContentRequestID =@ContentRequestID) 

 

EXEC webRequest_OnValidate_CreateRequest @ContentRequestID, @BusinessProcessID, @boaUserID

END

END

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