SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Asamco BV, Alex & Roozbeh
-- Create date: 12/11/2021
-- Description: Creates WF, WF Steps, WF Members, User Groups.
-- Note: requires Create User Group procedure as well!
-- =============================================
CREATE OR ALTER PROCEDURE _as_Create_EPO_FullWorkflow
-- Add the parameters for the stored procedure here
@WFCode varchar(100),
@WFName varchar(250),
@NrOfApprovers int = 3
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
/*Usage:
exec _as_Create_EPO_FullWorkflow 'PR6 - ADM','PR - ADMINISTRATION AND FINANCE ',3
*/
/*Steps:
1) Create WF
2) Create user groups
3) Create Steps (status)
4) Create WF-Steps (members)
*/
DECLARE @WFID int, @GroupCode varchar(100), @GroupName varchar(100), @GroupID int, @WFStepID int,@StepCode varchar(20), @StepName varchar(50)
DECLARE @WFIDs TABLE (ID int)
-----------------------------------CREATING WORKFLOW--------------------------
SELECT @WFID = (SELECT idWorkflow FROM _btblCMWorkflow W WHERE W.cName = @WFCode)
IF COALESCE(@WFID,0) = 0
BEGIN
INSERT INTO _btblCMWorkflow (cName, cDescription, bPOWorkflow,_btblCMWorkflow_iBranchID)
OUTPUT inserted.idWorkflow INTO @WFIDs (ID)
VALUES (@WFCode, @WFName, 1,0); --to check: poWorkflow always?
SELECT @WFID = (SELECT TOP 1 ID FROM @WFIDs)
END
ELSE
BEGIN
UPDATE _btblCMWorkflow
SET cDescription = @WFName, bPOWorkflow = 1
WHERE idWorkflow = @WFID
END
-----------------------------------END OF: CREATING WORKFLOW--------------------------
-----------------------------------CREATING INCIDENT TYPE GROUP--------------------------
DECLARE @ITCIDs TABLE(ID int)
DECLARE @ITCID int
DECLARE @EOGroupName varchar(20) = 'EO Groups'
IF NOT EXISTS (SELECT * FROM _rtblIncidentCat WHERE cDescription = @EOGroupName)
BEGIN
INSERT INTO _rtblIncidentCat (cDescription,_rtblIncidentCat_iBranchID) VALUES (@EOGroupName,0)
END
SELECT @ITCID = (SELECT idIncidentCat FROM _rtblIncidentCat WHERE cDescription = @EOGroupName)
-----------------------------------END OF: CREATING INCIDENT TYPE GROUP--------------------------
-----------------------------------CREATING INCIDENT TYPE--------------------------
DECLARE @IncidentTypeID int
SELECT @IncidentTypeID = COALESCE((SELECT idIncidentType FROM _rtblIncidentType W WHERE W.cDescription = @WFCode),0)
EXECUTE @IncidentTypeID = [dbo].[_bspIncidentType]
@idIncidentType = @IncidentTypeID
,@iBranchID = 0
,@cDescription = @WFCode
,@iEscGroupID = 0
,@bAllowOverride = 1
,@bRequireContract = 0
,@iIncidentTypeGroupID = @ITCID
,@iWorkflowID = @WFID
,@bAllowOverrideIncidentType = 1
,@bPOIncidentType = 1
,@cDefaultOutline = ''
,@bActive = 1;
SELECT @IncidentTypeID = (SELECT idIncidentType FROM _rtblIncidentType W WHERE W.cDescription = @WFCode)
-----------------------------------END OF:CREATING INCIDENT TYPE--------------------------
-----------------------------------CREATING USER GROUPS--------------------------
-----1: For Requester:
SELECT @GroupCode = CONCAT('EO ',@WFCode, ' - Requesters')
SELECT @GroupName = CONCAT('EO ',@WFCode, ' - Requesters Group ')
EXEC @GroupID = [_as_AddUpdateUserGroup] @cGroupName = @GroupCode, @cDescription = @GroupName, @cComments = 'EPO - Auto-generated group'
--CREATE WORKFLOW STEPS:
SET @StepCode = @WFCode + RIGHT(CONCAT('00', 1),2)
SET @StepName = @WFCode + RIGHT(CONCAT('00', 1),2)
EXEC @WFStepID = [_as_AddUpdateWFStep_Status] @StepCode, @StepName
-- Insert members to the workflow
INSERT INTO _btblCMWorkflowMembers
(
iWorkflowID,
iAgentID,
cAgentType,
iWorkflowStatusID,
iSequenceNo,
bAllowReject,
bAllowCloseAfterReject,
bPOApprove
) VALUES
(@WFID, @GroupID, 'G',@WFStepID, 0,1,1,1)
-----2: For all the approval steps
DECLARE @i_UG int = 1
WHILE @i_UG <= @NrOfApprovers
BEGIN
----CREATE USER GROUP FOR APPROVERS HERE:
SELECT @GroupCode = CONCAT('EO ',@WFCode, ' - Approval ', @i_UG)
SELECT @GroupName = CONCAT('EO ',@WFCode, ' - Approval Group ', @i_UG)
EXEC @GroupID = [_as_AddUpdateUserGroup] @cGroupName = @GroupCode, @cDescription = @GroupName, @cComments = 'EPO - Auto-generated group'
--CREATE WORKFLOW STEPS:
SET @StepCode = @WFCode + RIGHT(CONCAT('00', @i_UG+1),2)
SET @StepName = @WFCode + RIGHT(CONCAT('00', @i_UG+1),2)
EXEC @WFStepID = [_as_AddUpdateWFStep_Status] @StepCode, @StepName
--add to WF steps:
-- Insert members to the workflow
INSERT INTO _btblCMWorkflowMembers
(
iWorkflowID,
iAgentID,
cAgentType,
iWorkflowStatusID,
iSequenceNo,
bAllowReject,
bAllowCloseAfterReject,
bPOApprove
) VALUES
(@WFID, @GroupID, 'G',@WFStepID, @i_UG,1,1,1)
SET @i_UG = @i_UG + 1
END--WHILE @i_UG <= @NrOfApprovers
END --stored proc
GO
-----------------------------------CREATE WF STATUS CODE--------------------------
CREATE or alter Procedure [dbo].[_as_AddUpdateWFStep_Status]
@WFStepCode varchar(20),
@WFStepName varchar(50)
AS
BEGIN
DECLARE @WFID int
DECLARE @WFIDs TABLE(ID int)
SELECT @WFID = (SELECT idWorkflowStatus FROM _btblCMWorkflowStatus C WHERE C.cStatusCode = @WFStepCode)
IF COALESCE(@WFID,0) = 0
BEGIN --insert
INSERT INTO [dbo].[_btblCMWorkflowStatus]
([cStatusCode]
,[cDescription]
,[_btblCMWorkflowStatus_iBranchID]
)
OUTPUT inserted.idWorkflowStatus INTO @WFIDs (ID)
VALUES
(@WFStepCode
,@WFStepName
,0)
SELECT @WFID = (SELECT TOP 1 ID FROM @WFIDs)
END
ELSE --update
BEGIN
UPDATE [_btblCMWorkflowStatus]
SET cStatusCode = @WFStepCode
,cDescription = @WFStepName
WHERE idWorkflowStatus = @WFID
END
print concat('WFID from proc:[_as_AddUpdateWFStep_Status] is ',@WFID)
RETURN @WFID
END
-----------------------------------END OF: CREATE WF STATUS CODE--------------------------
-----------------------------------------------------------
----------START CREATING PROCS:
-----------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE or alter Procedure [dbo].[_as_AddUpdateUserGroup]
@cGroupName varchar(30),
@cDescription varchar(50),
@cComments varchar(1024) = '',
@bCanAssign bit = 1,
@iAssignRule int = 1,
@iAssignAgent int=0,
@bUseDefaultTree bit=0,
@bCBGrpAllVisible bit=0,
@bCBGrpNoneVisible bit=0,
@bJRGrpAllVisible bit=0,
@bJRGrpNoneVisible bit=0,
@bCBGrpAgentVisible bit=1,
@bJRGrpAgentVisible bit=1,
@iPOAuthType int=1,
@iPOIncidentTypeID int=0,
@bPOExclusive bit=1,
@fPOLimit float=0,
@bPOUseDefaults bit=0
--@_rtblAgentGroups_iBranchID int,
--@idPOSMenuSetup int
AS
BEGIN
Declare @idAgentsGroups int
Select @idAgentsGroups = (Coalesce((Select IdAgentGroups from _rtblAgentgroups where cGroupName =@cGroupName),0))
IF @idAgentsGroups = 0
BEGIN
Print 'Group doesnt exists so we create it'
DECLARE @T TABLE (
idAgentGroups int )
--BEGIN INSERT
INSERT INTO [dbo]._rtblAgentgroups
(
cGroupName,
cDescription,
cComments,
bCanAssign,
iAssignRule,
iAssignAgent,
bUseDefaultTree,
bCBGrpAllVisible,
bCBGrpNoneVisible,
bJRGrpAllVisible,
bJRGrpNoneVisible,
bCBGrpAgentVisible,
bJRGrpAgentVisible,
iPOAuthType,
iPOIncidentTypeID,
bPOExclusive,
fPOLimit,
bPOUseDefaults,
_rtblAgentGroups_iBranchID,
idPOSMenuSetup
)
OUTPUT INSERTED.idAgentGroups INTO @T -- Out
VALUES(
@cGroupName,
@cDescription,
@cComments,
@bCanAssign,
@iAssignRule,
@iAssignAgent,
@bUseDefaultTree,
@bCBGrpAllVisible,
@bCBGrpNoneVisible,
@bJRGrpAllVisible,
@bJRGrpNoneVisible,
@bCBGrpAgentVisible,
@bJRGrpAgentVisible,
@iPOAuthType,
@iPOIncidentTypeID,
@bPOExclusive,
@fPOLimit,
@bPOUseDefaults,
0, --@_rtblAgentGroups_iBranchID,
0--@idPOSMenuSetup
)
--END INSERT
return (Select idAgentGroups from @T)
END -- END Insert check
ELSE
BEGIN
Print 'Group already exists so we need to update it'
Update [dbo]._rtblAgentgroups
Set
cGroupName= @cGroupName,
cDescription= @cDescription,
cComments= @cComments,
bCanAssign= @bCanAssign,
iAssignRule= @iAssignRule,
iAssignAgent= @iAssignAgent,
bUseDefaultTree= @bUseDefaultTree,
bCBGrpAllVisible= @bCBGrpAllVisible,
bCBGrpNoneVisible= @bCBGrpNoneVisible,
bJRGrpAllVisible= @bJRGrpAllVisible,
bJRGrpNoneVisible= @bJRGrpNoneVisible,
bCBGrpAgentVisible= @bCBGrpAgentVisible,
bJRGrpAgentVisible= @bJRGrpAgentVisible,
iPOAuthType= @iPOAuthType,
iPOIncidentTypeID= @iPOIncidentTypeID,
bPOExclusive= @bPOExclusive,
fPOLimit= @fPOLimit,
bPOUseDefaults= @bPOUseDefaults--,
--_rtblAgentGroups_iBranchID= @_rtblAgentGroups_iBranchID,
--idPOSMenuSetup= @idPOSMenuSetup
WHERE
IdAgentGroups = @idAgentsGroups
return @idAgentsGroups
END -- END Update check
END