Master Data Management
Content WebApp Examples
The following examples can be used as reference when creating the Content WebApp.
- Examples for MDM to Call boa (Add/Edit/Delete) Functions
- Security Views for Binding Security by OrgUnits and Security that is not Bounded
- Role Status BY User Views
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