Remediate
Create Request Script
Run this script to create the stored procedure webRequest_Submit_RemediateJob_TargetIns.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[webRequest_CreateRequestForRemediateJobIns]
@boaUserID [nvarchar](50),
@JobAssetID [nvarchar](10)
AS
BEGIN
/* This Procedure is used for inserting new requests into ttRequest table in Content app.
*
* This procedure must be modified to populate the ttRequest table in the content application database.
* Be sure to set the AddedVia equal to the JobAssetID value as we do in the example below,
* this is required for tracking the state of Requests against the Quality report.
*
* A call to apiCreateRequest procedure in DGE should also be done to create the Request in MDM.
*
* DECLARE @SQL NVARCHAR(MAX),
* @BusinessProcessID NVARCHAR(MAX),
* @ScenarioID NVARCHAR(MAX),
* @WebAppID UNIQUEIDENTIFIER,
* @ContentRequestID UNIQUEIDENTIFIER,
* @RequestID int,
* @ParentRequestID int
*
* -- Check in DGE the ID's for the BP and Scenario to be inserted in ttRequest
* SELECT @BusinessProcessID = BP.[BusinessProcessID], @WebAppID = C.DefaultWebAppID, @ScenarioID = S.[ScenarioID]
* FROM [{@cb_TargetDbName}].[dbo].[ttCategory] C
* INNER JOIN [{@cb_TargetDbName}].[dbo].[ttBusinessProcess] BP ON C.[CategoryID] = BP.[CategoryID]
* INNER JOIN [{@cb_TargetDbName}].[dbo].[ttBusinessProcessScenario] BPS ON BP.[BusinessProcessID] = BPS.[BusinessProcessID]
* INNER JOIN [{@cb_TargetDbName}].[dbo].[ttScenario] S ON BPS.[ScenarioID] = S.[ScenarioID]
* WHERE C.Name = 'YourCategory' AND BP.NAME = 'Your Business Process' AND S.NAME = 'Your Scenario'
*
* -- Create new ContentRequestID
* SET @ContentRequestID = NEWID()
*
* --Insert values into the ttRequest Content App
* INSERT [dbo].[ttrequest]
* ([ContentRequestID],
* [BusinessProcessid],
* [ScenarioID],
* [Name1],
* [Approved],
* [RequestStatusId],
* [boastatus],
* [AddedBy],
* [AddedOn],
* [AddedVia])
* VALUES (@ContentRequestID,
* @BusinessProcessID,
* @ScenarioID,
* N'Request created from Remediate',
* 0,
* 0,
* 4,
* @boaUserID,
* GETDATE(),
* @JobAssetID)
*
* --Execute Procedure to create request in DGE
* EXECUTE {@cb_TargetDbName}..apiCreateRequest @BusinessProcessID, '', @boaUserID, @WebAppID, @NewRequestID = @RequestID OUTPUT
*
* SELECT @ParentRequestID = ParentRequestID,
* @ScenarioID = ScenarioID
* FROM {@cb_TargetDbName}..ttRequest
* WHERE RequestID = @RequestID
*
* --Update ttRequest table with the RequestID and ParentRequestID just generated
* UPDATE ttRequest
* SET RequestID = @RequestID,
* ParentRequestID = @ParentRequestID
* WHERE ContentRequestID = @ContentRequestID
* --Create a job that will execute the OnValidate event of the Content Request page.
*
* DECLARE @QueueID UNIQUEIDENTIFIER
* DECLARE @Title NVARCHAR(150)
* DECLARE @Group NVARCHAR(180)
* DECLARE @JobGUID UNIQUEIDENTIFIER
* DECLARE @JobID INT
* DECLARE @Event NVARCHAR(128)
* DECLARE @TaskID INT
* DECLARE @RequestPageID UNIQUEIDENTIFIER = '86a5a256-312f-43f3-a18e-7a602b3ea6d0' --Your Content App Request Page ID
*
*
* SET @QueueID = 'E8204DD3-64B2-41D1-BB51-A8248624C71C' --General Queue as the boaJobCreate process creates General jobs.
* SET @JobGUID = Newid()
* SET @Event = 'OnValidate'
* SET @Title = 'Remediate Request Creation For '
* + CONVERT(NVARCHAR(150), @RequestID)
*
*
* SET @Group = 'RemediateRequestFor'
* + CONVERT(NVARCHAR(150), @RequestID)
*
* EXEC {@cb_TargetSystemDbName}.dbo.boaJobCreate
* @QueueID,
* @WebAppID,
* @Title,
* @Group,
* 1,--Halt On Error True
* @JobGuid,
* @JobID OUTPUT
*
* EXEC {@cb_TargetSystemDbName}.dbo.boaJobAddEventTask
* @JobID,
* @RequestPageID,
* @Event,
* @TaskID OUTPUT
*
* --Add Parameters for the Event
* EXEC {@cb_TargetSystemDbName}.dbo.boaJobAddTaskParam
* @JobID,
* @TaskID,
* 'Key.ContentRequestID',
* @ContentRequestID
*
* --Start Job
* EXEC {@cb_TargetSystemDbName}.dbo.boaJobStart
* @JobID
*/
SELECT '' WHERE 1 = 0
END
GO
