SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_ProcessHistory]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ProcessDefinition_ProcessHistory] ADD
[StepDefinitionVersion] [int] NOT NULL CONSTRAINT [DF_ProcessDefinition_ProcessHistory_StepDefinitionVersion] DEFAULT ((1)),
[StepsSvg] [nvarchar] (max) NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_Process]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ProcessDefinition_Process] ADD
[StepDefinitionVersion] [int] NOT NULL CONSTRAINT [DF_ProcessDefinition_Process_StepDefinitionVersion] DEFAULT ((1)),
[StepsSvg] [nvarchar] (max) NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_ProcessLink]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ProcessDefinition_ProcessLink] ADD
[SectionLinkTypeId] [int] NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[Review_LinksToWrongType]'
GO
CREATE PROCEDURE [dbo].[Review_LinksToWrongType] 
  @repositoryName nvarchar(256)
AS
BEGIN
   SET NOCOUNT ON;

 DECLARE @RepositoryId int
 SET @RepositoryId=dbo.ProcessDefinition_GetRepositoryId(@repositoryName)

 SELECT ProcessFrom.ProcessTitle AS Process, ProcessDefinition_ProcessLink.SectionName AS Section, 
 ProcessDefinition_ProcessLink.LinkText AS Link, '~/' + ProcessFrom.PrettyPostTitle AS link_Link, 
 LinkType.ProcessTypeName AS SectionLinkType, LinksToProcessType.ProcessTypeName  AS LinksToType
 FROM  ProcessDefinition_ProcessLink INNER JOIN
    ProcessDefinition_Process AS ProcessTo ON ProcessDefinition_ProcessLink.LinksToProcessId = ProcessTo.ProcessId INNER JOIN
    ProcessDefinition_Process AS ProcessFrom ON ProcessDefinition_ProcessLink.ProcessId = ProcessFrom.ProcessId INNER JOIN
    ProcessDefinition_ProcessType AS LinkType ON ProcessDefinition_ProcessLink.SectionLinkTypeId = LinkType.ProcessTypeId INNER JOIN
    ProcessDefinition_ProcessType AS LinksToProcessType ON ProcessTo.ProcessTypeId = LinksToProcessType.ProcessTypeId
 WHERE (LinkType.ProcessTypeName != LinksToProcessType.ProcessTypeName) AND (ProcessTo.ForeignId = @repositoryId)
 ORDER BY ProcessFrom.ProcessTitle,ProcessDefinition_ProcessLink.SectionName


END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProcessDefinition_SetStepsSvg]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_SetStepsSvg] 
 @processId int,
 @stepsSvg nvarchar(MAX)
AS
BEGIN
   SET NOCOUNT ON;

 UPDATE ProcessDefinition_Process 
 SET StepsSvg = @stepsSvg 
 WHERE ProcessId = @processId

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProcessDefinition_GetStepsSvg]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_GetStepsSvg] 
 @processId int
AS
BEGIN
   SET NOCOUNT ON;

 SELECT  StepsSvg  FROM 
 ProcessDefinition_Process 
 WHERE ProcessId = @processId

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_ProcessLinkHistory]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ProcessDefinition_ProcessLinkHistory] ADD
[SectionLinkTypeId] [int] NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_AddProcessLink]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_AddProcessLink]
 @processId int,
 @sectionName nvarchar(256),
 @linkUri nvarchar(1024),
 @fullLinkUri nvarchar(1024),
 @linksToProcessId int,
 @linkText nvarchar(1024),
 @linkType int,
 @linkUid nvarchar(50),
 @sectionDirection nvarchar(10),
 @sectionMultiplicity nvarchar(10),
 @sectionType nvarchar(128),
 @sectionLinkTypeId int
WITH ENCRYPTION AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @excludedProcessId int
 SET @excludedProcessId = NULL
 IF((@linkType & 1) > 0)
 BEGIN
  SET @excludedProcessId = @linksToProcessId
  SET @linksToProcessId = NULL  
 END

 INSERT INTO ProcessDefinition_ProcessLink
  (ProcessId, SectionName, LinkUri, LinksToProcessId, FullLinkUri, 
  LinkText, LinkType, LinkUid, SectionDirection, SectionMultiplicity, SectionType, SectionLinkTypeId, ExcludedLinksToProcessId)
 VALUES     (@processId,@sectionName,@linkUri,@linksToProcessId,@fullLinkUri,
  @linkText, @linkType, @linkUid, @sectionDirection, @sectionMultiplicity, @sectionType, @sectionLinkTypeId, @excludedProcessId)


END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[Account_DeleteUser]'
GO
CREATE PROCEDURE [dbo].[Account_DeleteUser] 
 @userId int
AS
BEGIN
   SET NOCOUNT ON;

 UPDATE Account_User SET IsDeleted=1 WHERE UserId=@userId


END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_CreateProcess]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_CreateProcess]
(
     @UserId int,
      @RepositoryName nvarchar(256),
      @ProcessDescription nvarchar(max),
      @ProcessTitle nvarchar(256),
  @PrettyPostTitle nvarchar(256),
      @IsPublished bit,
 @DescriptionSnippet nvarchar(1024),
 @ProcessTypeId int,
 @ProcessUId uniqueidentifier,
 @defnVersion int,
 @stepsSvg nvarchar(MAX)
)
WITH ENCRYPTION AS
BEGIN
 
               SET NOCOUNT ON;

 DECLARE @RepositoryId int
 SET @RepositoryId=dbo.ProcessDefinition_GetRepositoryId(@repositoryName)
 
 IF(@RepositoryId IS NOT NULL)
 BEGIN

  IF(@ProcessUId IS NULL)
   SET @ProcessUId = NewId()

     INSERT INTO ProcessDefinition_Process
      (
            ForeignType, 
            ForeignId, 
            DateCreated, 
            ProcessDescription, 
            ProcessTitle, 
   PrettyPostTitle,
            IsPublished,
   DescriptionSnippet,
   UserId,
   ProcessTypeId,
   ProcessUId,
   StepDefinitionVersion,
   StepsSvg
      ) VALUES (
            7, 
            @RepositoryId, 
            GetUtcDate(), 
            @ProcessDescription, 
            @ProcessTitle, 
   @PrettyPostTitle,
            @IsPublished,
   @DescriptionSnippet,
   @UserId,
   @ProcessTypeId,
   @ProcessUId,
   @defnVersion,
   @stepsSvg
      )

     DECLARE @newId int
     SET @newId = Scope_Identity()

   
 END


     RETURN @newId
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProcessDefinition_GetHistoryStepsSvg]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_GetHistoryStepsSvg] 
 @processHistoryId int
AS
BEGIN
   SET NOCOUNT ON;

 SELECT  StepsSvg  FROM 
 ProcessDefinition_ProcessHistory
 WHERE ProcessHistoryId = @processHistoryId

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_Step]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ProcessDefinition_Step] ADD
[bpmnJson] [nvarchar] (max) NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_AddProcessHistoryLink]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_AddProcessHistoryLink]
 @processHistoryId int,
 @processId int,
 @sectionName nvarchar(256),
 @linkUri nvarchar(1024),
 @fullLinkUri nvarchar(1024),
 @linksToProcessId int,
 @linkText nvarchar(1024),
 @linkType int,
 @linkUid nvarchar(50),
 @sectionDirection nvarchar(10),
 @sectionMultiplicity nvarchar(10),
 @sectionType nvarchar(128),
 @sectionLinkTypeId int,
 @MajorVersion int, 
 @MinorVersion int
WITH ENCRYPTION AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @excludedProcessId int
 SET @excludedProcessId = NULL
 IF((@linkType & 1) > 0)
 BEGIN
  SET @excludedProcessId = @linksToProcessId
  SET @linksToProcessId = NULL  
 END

 INSERT INTO ProcessDefinition_ProcessLinkHistory
  (ProcessId,ProcessHistoryId, SectionName, LinkUri, LinksToProcessId, FullLinkUri, 
  LinkText, LinkType, LinkUid, SectionDirection, SectionMultiplicity, SectionType, SectionLinkTypeId, ExcludedLinksToProcessId, MajorVersion, MinorVersion)
 VALUES     (@processId,@ProcessHistoryId,@sectionName,@linkUri,@linksToProcessId,@fullLinkUri,
  @linkText, @linkType, @linkUid, @sectionDirection, @sectionMultiplicity, @sectionType, @sectionLinkTypeId, @excludedProcessId, @MajorVersion, @MinorVersion)


END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_StepHistory]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ProcessDefinition_StepHistory] ADD
[bpmnJson] [nvarchar] (max) NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_IsInProcessStructure]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_IsInProcessStructure] 
 @ProcessId int
WITH ENCRYPTION AS
BEGIN
   SET NOCOUNT ON;
 DECLARE @checkId int
 SET @checkId=
 (
 SELECT    TOP(1) ProcessStepId
FROM         ProcessDefinition_Step
WHERE     (ParentProcessId = @processId)
 )

  if(@checkId IS NULL)
 BEGIN
  SET @checkId=
  (
  SELECT    TOP(1) ProcessStepId
  FROM         ProcessDefinition_Step
  WHERE     (ChildProcessId = @processId)
  )

 END

 SELECT CASE WHEN @checkId IS NULL THEN 0 ELSE 1 END AS IsInProcessStructure

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProcessDefinition_StepPredecessorHistory]'
GO
CREATE TABLE [dbo].[ProcessDefinition_StepPredecessorHistory]
(
[StepPredecessorHistoryId] [int] NOT NULL IDENTITY(1, 1),
[StepPredecessorId] [int] NOT NULL,
[ParentProcessId] [int] NOT NULL,
[ProcessStepId] [int] NOT NULL,
[StepUId] [uniqueidentifier] NOT NULL,
[FromProcessStepId] [int] NOT NULL,
[FromStepUId] [uniqueidentifier] NOT NULL,
[PredecessorText] [nvarchar] (250) NULL,
[ProcessHistoryId] [int] NOT NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_ProcessDefinition_StepPredecessorHistory] on [dbo].[ProcessDefinition_StepPredecessorHistory]'
GO
ALTER TABLE [dbo].[ProcessDefinition_StepPredecessorHistory] ADD CONSTRAINT [PK_ProcessDefinition_StepPredecessorHistory] PRIMARY KEY CLUSTERED  ([StepPredecessorHistoryId])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProcessDefinition_GetHistoryStepPredecessors]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_GetHistoryStepPredecessors] 
 @ProcessHistoryId int
AS
BEGIN
   SET NOCOUNT ON;



 SELECT StepPredecessorHistoryId, StepPredecessorId, ParentProcessId, ProcessStepId, StepUId, FromProcessStepId, FromStepUId, PredecessorText, ProcessHistoryId
 FROM  ProcessDefinition_StepPredecessorHistory 
 WHERE (ProcessDefinition_StepPredecessorHistory.ProcessHistoryId = @ProcessHistoryId) 
 



END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetLicensedUsers]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetLicensedUsers]
WITH ENCRYPTION AS
BEGIN
 
               SET NOCOUNT ON;


SELECT     ProcessDefinition_License.LicenseId, ProcessDefinition_License.Info, Account_User.UserId, Account_User.DisplayName, Account_User.MemberEmailAddress, 
                      Account_User.LicenseId AS UserLicenseId, ProcessDefinition_License.Feature, ProcessDefinition_License.ValidTo, 
                      CurrentLicensesInUse.Expiry AS CurrentLicenseExpiry, CurrentLicensesInUse.Info AS CurrentLicenseInfo
FROM         Account_User LEFT OUTER JOIN
                          (SELECT     ProcessDefinition_LicenseUser.UserId, ProcessDefinition_LicenseUser.LicenseId, ProcessDefinition_LicenseUser.Expiry, 
                                                   ProcessDefinition_License_1.Info
                            FROM          ProcessDefinition_LicenseUser INNER JOIN
                                                   ProcessDefinition_License AS ProcessDefinition_License_1 ON ProcessDefinition_LicenseUser.LicenseId = ProcessDefinition_License_1.LicenseId) 
                      AS CurrentLicensesInUse ON Account_User.UserId = CurrentLicensesInUse.UserId LEFT OUTER JOIN
                      ProcessDefinition_License ON Account_User.LicenseId = ProcessDefinition_License.LicenseId
       WHERE Account_User.IsDeleted=0
        ORDER BY Account_User.DisplayName


END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetTaskAssignments]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetTaskAssignments] 
 @taskId int 
WITH ENCRYPTION AS
BEGIN
   SET NOCOUNT ON;

SELECT     ProcessDefinition_ProjectPlanTaskAssignment.AssignedTaskId, ProcessDefinition_ProjectPlanTaskAssignment.AssignmentNote, 
                      ProcessDefinition_ProjectPlanTaskAssignment.TaskId, ProcessDefinition_ProjectPlanTaskAssignment.AssignedToId, 
                      ProcessDefinition_ProjectPlanTaskAssignment.AssignedDate, Account_User.DisplayName, Account_User.MemberEmailAddress
FROM         ProcessDefinition_ProjectPlanTaskAssignment INNER JOIN
                      Account_User ON ProcessDefinition_ProjectPlanTaskAssignment.AssignedToId = Account_User.UserId
WHERE     (ProcessDefinition_ProjectPlanTaskAssignment.TaskId = @taskId)

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProcessDefinition_GetStepDefinitionVersion]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_GetStepDefinitionVersion] 
 @ParentProcessId int,
 @useFrozen bit
AS
BEGIN
   SET NOCOUNT ON;

 
 DECLARE @CorrectProcessHistoryId int
 SELECT @CorrectProcessHistoryId =  ProcessHistoryId
 FROM ProcessDefinition_ProcessHistory
 WHERE   (ProcessDefinition_ProcessHistory.ProcessId = @ParentProcessId) AND
   ((@useFrozen = 1 AND IsFrozenVersion = 1) OR (@useFrozen = 0 AND IsLatestVersion = 1))

 SELECT StepDefinitionVersion
 FROM  ProcessDefinition_ProcessHistory
 WHERE ProcessHistoryId=@CorrectProcessHistoryId

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProcessDefinition_StepPredecessor]'
GO
CREATE TABLE [dbo].[ProcessDefinition_StepPredecessor]
(
[StepPredecessorId] [int] NOT NULL IDENTITY(1, 1),
[ParentProcessId] [int] NOT NULL,
[ProcessStepId] [int] NOT NULL,
[StepUId] [uniqueidentifier] NOT NULL,
[FromProcessStepId] [int] NOT NULL,
[FromStepUId] [uniqueidentifier] NOT NULL,
[PredecessorText] [nvarchar] (250) NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_ProcessDefinition_StepPredecessor] on [dbo].[ProcessDefinition_StepPredecessor]'
GO
ALTER TABLE [dbo].[ProcessDefinition_StepPredecessor] ADD CONSTRAINT [PK_ProcessDefinition_StepPredecessor] PRIMARY KEY CLUSTERED  ([StepPredecessorId])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_ClearSteps]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_ClearSteps] 
 @ParentProcessId int,
 @stepDefinitionVersion int
WITH ENCRYPTION AS
BEGIN
   SET NOCOUNT ON;


 DELETE FROM ProcessDefinition_Step
 WHERE     (ParentProcessId = @ParentProcessId)

 DELETE FROM ProcessDefinition_StepPredecessor
 WHERE     (ParentProcessId = @ParentProcessId)

  UPDATE ProcessDefinition_Process
 SET StepDefinitionVersion = @stepDefinitionVersion
 WHERE ProcessId = @ParentProcessId

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProcessDefinition_UpdateProcessPrettyTitle]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_UpdateProcessPrettyTitle]
(
     @ProcessId int,
      @PrettyPostTitle nvarchar(256)
)
AS
BEGIN
 
               SET NOCOUNT ON;

 UPDATE    ProcessDefinition_ProcessHistory
 SET             PrettyPostTitle=@PrettyPostTitle
 WHERE     (ProcessId = @ProcessId)

 UPDATE    ProcessDefinition_Process
 SET             PrettyPostTitle=@PrettyPostTitle
 WHERE     (ProcessId = @ProcessId)


END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetSteps]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetSteps] 
 @ParentProcessId int,
 @useFrozen bit
WITH ENCRYPTION AS
BEGIN
   SET NOCOUNT ON;

 

 DECLARE @CorrectProcessHistoryId int
 SELECT @CorrectProcessHistoryId =  ProcessHistoryId
 FROM ProcessDefinition_ProcessHistory
 WHERE   (ProcessDefinition_ProcessHistory.ProcessId = @ParentProcessId) AND
   ((@useFrozen = 1 AND IsFrozenVersion = 1) OR (@useFrozen = 0 AND IsLatestVersion = 1))

 SELECT     ISNULL(ProcessDefinition_StepHistory.StepName, FreezeCorrectProcesses.ProcessTitle) AS StepName, 
    ProcessDefinition_StepHistory.StepDescription, 
    ProcessDefinition_StepHistory.ChildProcessId, 
    ProcessDefinition_StepHistory.StepType, 
    ProcessDefinition_StepHistory.IterationDescription, 
    ProcessDefinition_StepHistory.OptionalDescription, 
    FreezeCorrectProcesses.PrettyPostTitle, 
    ProcessDefinition_StepHistory.ProcessStepId,
    AltGroup,
    FreezeCorrectProcesses.ProcessTypeId,
    ProcessDefinition_StepHistory.bpmnJson
 FROM ProcessDefinition_StepHistory LEFT OUTER JOIN
  (
   SELECT ProcessId, ProcessTitle, PrettyPostTitle, AltGroup, ProcessTypeId
   FROM ProcessDefinition_ProcessHistory
   WHERE   ((@useFrozen = 1 AND IsFrozenVersion = 1) OR (@useFrozen = 0 AND IsLatestVersion = 1))
  ) AS FreezeCorrectProcesses ON ProcessDefinition_StepHistory.ChildProcessId = FreezeCorrectProcesses.ProcessId 
 WHERE (ProcessDefinition_StepHistory.ProcessHistoryId = @CorrectProcessHistoryId)
 ORDER BY ProcessDefinition_StepHistory.StepPosition

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProcessDefinition_GetStepPredecessors]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_GetStepPredecessors] 
 @ParentProcessId int,
 @useFrozen bit
AS
BEGIN
   SET NOCOUNT ON;

 

 DECLARE @CorrectProcessHistoryId int
 SELECT @CorrectProcessHistoryId =  ProcessHistoryId
 FROM ProcessDefinition_ProcessHistory
 WHERE   (ProcessDefinition_ProcessHistory.ProcessId = @ParentProcessId) AND
   ((@useFrozen = 1 AND IsFrozenVersion = 1) OR (@useFrozen = 0 AND IsLatestVersion = 1))

 SELECT StepPredecessorHistoryId, StepPredecessorId, ParentProcessId, ProcessStepId, StepUId, FromProcessStepId, FromStepUId, PredecessorText, ProcessHistoryId
 FROM  ProcessDefinition_StepPredecessorHistory WHERE (ProcessHistoryId = @CorrectProcessHistoryId)


END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProcessDefinition_GetHistoryStepDefinitionVersion]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_GetHistoryStepDefinitionVersion] 
 @ProcessHistoryId int
AS
BEGIN
   SET NOCOUNT ON;


 SELECT StepDefinitionVersion
 FROM  ProcessDefinition_ProcessHistory
 WHERE ProcessHistoryId=@ProcessHistoryId

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetProjectMembers]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetProjectMembers]
 @projectId int
WITH ENCRYPTION AS
BEGIN
 
               SET NOCOUNT ON;

SELECT     ProcessDefinition_ProjectMember.ProjectMemberId, ProcessDefinition_ProjectMember.UserId, ProcessDefinition_ProjectMember.ProjectMemberRole, 
                      Account_User.DisplayName, Account_User.MemberEmailAddress, CAST('' AS varchar(256)) AS GravatarUrl, ProcessDefinition_ProjectMember.ForceFilteredView
FROM         ProcessDefinition_ProjectMember INNER JOIN
                      Account_User ON ProcessDefinition_ProjectMember.UserId = Account_User.UserId
WHERE     (ProcessDefinition_ProjectMember.ProjectId = @ProjectId) AND (Account_User.IsDeleted=0)

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[Report_ProjectLibraryMetrics]'
GO
ALTER PROCEDURE [dbo].[Report_ProjectLibraryMetrics] 
  @ProjectId int,
 @projectPlanId int
WITH ENCRYPTION AS
BEGIN
   SET NOCOUNT ON;

    

SELECT     ProcessDefinition_ProjectPlan.ProjectPlanName AS dd_ProjectPlanName, ParentTask.TaskName AS dd_ParentTaskName, 
ProcessDefinition_ProjectPlanTask.TaskName AS dd_TaskName, ProcessDefinition_ProcessHistory.MinorVersion AS Revision, 
'' AS tf_Section,
 '' AS ProcessTitle, Metadata_MetadataField.ScreenLabel AS dd_DataLabel, 
                      COALESCE (Metadata_Data.StringValue, CAST(Metadata_Data.IntValue AS varchar), CASE WHEN Metadata_Data.BoolValue = 1 THEN 'Yes' WHEN Metadata_Data.BoolValue = 0 THEN 'No' ELSE NULL END, 
                      CAST(Metadata_Data.DoubleValue AS varchar), CAST(Metadata_Data.DateValue AS varchar)) AS [Value],  Metadata_Data.DateChanged AS dtl_DateEntered, Account_User.DisplayName AS dd_UserName,
  '~/Project/{0:scramble,pPl}/Framed/{1}|'+CAST(ProcessDefinition_ProjectPlanTask.ProjectPlanId as nvarchar(12))+'|'+CAST(ProcessDefinition_ProjectPlanTask.ProjectPlanTaskId as nvarchar(12)) AS flink_Link
FROM         ProcessDefinition_ProjectPlan INNER JOIN
                      ProcessDefinition_ProjectPlanTask ON ProcessDefinition_ProjectPlan.ProjectPlanId = ProcessDefinition_ProjectPlanTask.ProjectPlanId INNER JOIN
                      Metadata_Data INNER JOIN
                      Metadata_MetadataField ON Metadata_Data.MetadataId = Metadata_MetadataField.MetadataId ON 
                      ProcessDefinition_ProjectPlanTask.ProjectPlanTaskId = Metadata_Data.ForeignId INNER JOIN
                      Account_User ON Metadata_Data.ChangedByUserId = Account_User.UserId INNER JOIN
                      ProcessDefinition_ProcessHistory ON ProcessDefinition_ProjectPlanTask.ProcessHistoryId = ProcessDefinition_ProcessHistory.ProcessHistoryId LEFT OUTER JOIN
                      ProcessDefinition_ProjectPlanTask AS ParentTask ON ProcessDefinition_ProjectPlanTask.ParentTaskId = ParentTask.ProjectPlanTaskId
WHERE     (Metadata_Data.ForeignType = 15) AND (ProcessDefinition_ProjectPlan.ProjectId = @ProjectId)
AND
( (ISNULL(@projectPlanId,0)=0) OR (ProcessDefinition_ProjectPlanTask.ProjectPlanId = @ProjectPlanId))

UNION
SELECT     ProcessDefinition_ProjectPlan.ProjectPlanName AS dd_ProjectPlanName, ParentTask.TaskName AS dd_ParentTaskName, 
                      ProcessDefinition_ProjectPlanTask.TaskName AS dd_TaskName, ProcessDefinition_ProcessHistory.MinorVersion AS Revision, 
      ProcessDefinition_ProcessLinkHistory.SectionName AS tf_Section,
                      ProcessDefinition_ProcessHistory.ProcessTitle, Metadata_MetadataField.ScreenLabel AS dd_DataLabel, COALESCE (Metadata_Data.StringValue, 
                      CAST(Metadata_Data.IntValue AS varchar), CASE WHEN Metadata_Data.BoolValue = 1 THEN 'Yes' WHEN Metadata_Data.BoolValue = 0 THEN 'No' ELSE NULL END, 
                      CAST(Metadata_Data.DoubleValue AS varchar), CAST(Metadata_Data.DateValue AS varchar)) AS Value, Metadata_Data.DateChanged AS dtl_DateEntered, 
                      Account_User.DisplayName AS dd_UserName, '~/Project/{0:scramble,pPl}/Framed/{1}|' + CAST(ProcessDefinition_ProjectPlanTask.ProjectPlanId AS nvarchar(12)) 
                      + '|' + CAST(ProcessDefinition_ProjectPlanTask.ProjectPlanTaskId AS nvarchar(12)) AS flink_Link 
FROM         ProcessDefinition_ProjectPlan INNER JOIN
                      ProcessDefinition_ProjectPlanTask ON ProcessDefinition_ProjectPlan.ProjectPlanId = ProcessDefinition_ProjectPlanTask.ProjectPlanId INNER JOIN
                      Account_User INNER JOIN
                      Metadata_Data INNER JOIN
                      Metadata_MetadataField ON Metadata_Data.MetadataId = Metadata_MetadataField.MetadataId ON 
                      Account_User.UserId = Metadata_Data.ChangedByUserId INNER JOIN
                      ProcessDefinition_ProjectPlanTaskLink ON Metadata_Data.ForeignId = ProcessDefinition_ProjectPlanTaskLink.ProjectPlanTaskLinkId INNER JOIN
                      ProcessDefinition_ProcessHistory ON ProcessDefinition_ProjectPlanTaskLink.LinksToProcessHistoryId = ProcessDefinition_ProcessHistory.ProcessHistoryId ON 
                      ProcessDefinition_ProjectPlanTask.ProjectPlanTaskId = ProcessDefinition_ProjectPlanTaskLink.TaskId INNER JOIN
                      ProcessDefinition_ProcessLinkHistory ON 
                      ProcessDefinition_ProjectPlanTaskLink.ProcessLinkHistoryId = ProcessDefinition_ProcessLinkHistory.ProcessLinkHistoryId LEFT OUTER JOIN
                      ProcessDefinition_ProjectPlanTask AS ParentTask ON ProcessDefinition_ProjectPlanTask.ParentTaskId = ParentTask.ProjectPlanTaskId
WHERE     (Metadata_Data.ForeignType = 14) AND (ProcessDefinition_ProjectPlan.ProjectId = @ProjectId)
AND
( (ISNULL(@projectPlanId,0)=0) OR (ProcessDefinition_ProjectPlanTask.ProjectPlanId = @ProjectPlanId))

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetMembers]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetMembers]
     @repositoryName nvarchar(256)
WITH ENCRYPTION AS
BEGIN
 
               SET NOCOUNT ON;

 DECLARE @RepositoryId int
 SET @RepositoryId=dbo.ProcessDefinition_GetRepositoryId(@repositoryName)
 

 SELECT     ProcessDefinition_Member.MemberId, ProcessDefinition_Member.UserId, ProcessDefinition_Member.MemberRole, Account_User.DisplayName,
        Account_User.MemberEmailAddress, CAST('' AS varchar(256)) AS GravatarUrl
 FROM         ProcessDefinition_Member INNER JOIN
        Account_User ON ProcessDefinition_Member.UserId = Account_User.UserId
 WHERE     (ProcessDefinition_Member.RepositoryId = @RepositoryId) AND (Account_User.IsDeleted=0)
 ORDER BY Account_User.MemberEmailAddress 

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_SavedProcess]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_SavedProcess] 
 @ProcessId int,
 @UserId int
WITH ENCRYPTION AS
BEGIN
   SET NOCOUNT ON;

 DECLARE @historyId int
 DECLARE @previousHistoryId int
 
 SELECT @previousHistoryId  = ProcessHistoryId 
 FROM ProcessDefinition_ProcessHistory
 WHERE  ProcessId = @ProcessId AND IsLatestVersion = 1
 
  DECLARE @IsFrozenVersion bit
 SET @IsFrozenVersion = 0

 DECLARE @DateFrozen datetime
 SELECT @DateFrozen=DateFrozen
  FROM         ProcessDefinition_Process
  WHERE     (ProcessId = @ProcessId)

 DECLARE @hasPreviouslyFrozen int
 SELECT @hasPreviouslyFrozen = COUNT(ProcessHistoryId) 
 FROM ProcessDefinition_ProcessHistory
 WHERE ProcessId=@ProcessId AND (NOT(DateFrozen IS NULL))

 IF(NOT(@DateFrozen IS NULL) OR (@hasPreviouslyFrozen = 0) )
 BEGIN
    
  UPDATE ProcessDefinition_ProcessHistory
   SET IsFrozenVersion = 0, IsLatestVersion = 0
   WHERE     (ProcessId = @ProcessId) 
  SET @IsFrozenVersion = 1
 END
 ELSE
 BEGIN
    UPDATE ProcessDefinition_ProcessHistory
   SET IsLatestVersion = 0
   WHERE     (ProcessId = @ProcessId) 
 END

  UPDATE ProcessDefinition_Process
 SET UserId =  @UserId
 WHERE (ProcessId = @ProcessId)

 INSERT INTO ProcessDefinition_ProcessHistory
        (ProcessId, ForeignType, ForeignId, DateCreated, ProcessDescription, ProcessTitle, IsPublished, DateUpdated, 
      DefaultTags,DescriptionSnippet, MajorVersion, MinorVersion, PrettyPostTitle, UserId, ProcessTypeId, DateFrozen, 
      IsFrozenVersion, IsLatestVersion, AltGroup, RelationshipGraph, IsDeleted,StepDefinitionVersion, StepsSvg)
 SELECT     ProcessId, ForeignType, ForeignId, DateCreated, ProcessDescription, ProcessTitle, IsPublished, DateUpdated, DefaultTags, DescriptionSnippet, MajorVersion, 
        MinorVersion, PrettyPostTitle, UserId, ProcessTypeId, DateFrozen, @IsFrozenVersion, 1, AltGroup, RelationshipGraph, 
        IsDeleted,StepDefinitionVersion, StepsSvg
 FROM         ProcessDefinition_Process
 WHERE     (ProcessId = @ProcessId)
 SET @historyId = Scope_Identity()

  INSERT INTO ProcessDefinition_StepHistory(ProcessStepId, ParentProcessId, StepName, StepDescription, ChildProcessId, StepPosition, 
  StepType, IterationDescription, OptionalDescription, MajorVersion, MinorVersion,ProcessHistoryId, bpmnJson)
 SELECT     ProcessDefinition_Step.ProcessStepId, ProcessDefinition_Step.ParentProcessId, ProcessDefinition_Step.StepName, ProcessDefinition_Step.StepDescription, 
        ProcessDefinition_Step.ChildProcessId, ProcessDefinition_Step.StepPosition, ProcessDefinition_Step.StepType, ProcessDefinition_Step.IterationDescription, 
        ProcessDefinition_Step.OptionalDescription, ProcessDefinition_Process.MajorVersion, ProcessDefinition_Process.MinorVersion,@historyId,
        bpmnJson
 FROM         ProcessDefinition_Step INNER JOIN
        ProcessDefinition_Process ON ProcessDefinition_Step.ParentProcessId = ProcessDefinition_Process.ProcessId
 WHERE     (ParentProcessId = @ProcessId)

  INSERT INTO ProcessDefinition_StepPredecessorHistory 
 (StepPredecessorId, ParentProcessId, ProcessStepId, StepUId, FromProcessStepId, FromStepUId, PredecessorText,ProcessHistoryId)
 SELECT StepPredecessorId, ParentProcessId, ProcessStepId, StepUId, FromProcessStepId, FromStepUId, PredecessorText,@historyId
 FROM  ProcessDefinition_StepPredecessor WHERE ParentProcessId=@ProcessId


  INSERT INTO ProcessDefinition_ProcessLinkHistory(ProcessId, SectionName, LinkUri, LinksToProcessId, FullLinkUri, LinkText, 
  MajorVersion, MinorVersion,ProcessHistoryId, LinkType, LinkUid, SectionDirection, SectionMultiplicity, SectionType, SectionLinkTypeId, ExcludedLinksToProcessId)
 SELECT     ProcessDefinition_ProcessLink.ProcessId, ProcessDefinition_ProcessLink.SectionName, 
        ProcessDefinition_ProcessLink.LinkUri, ProcessDefinition_ProcessLink.LinksToProcessId, ProcessDefinition_ProcessLink.FullLinkUri, 
        ProcessDefinition_ProcessLink.LinkText, ProcessDefinition_Process.MajorVersion, ProcessDefinition_Process.MinorVersion,@historyId,
       LinkType, LinkUid, SectionDirection, SectionMultiplicity, SectionType, SectionLinkTypeId, ExcludedLinksToProcessId
 FROM         ProcessDefinition_ProcessLink INNER JOIN
        ProcessDefinition_Process ON ProcessDefinition_ProcessLink.ProcessId = ProcessDefinition_Process.ProcessId
 WHERE     (ProcessDefinition_ProcessLink.ProcessId = @ProcessId)

  INSERT INTO dbo.Metadata_MetadataField( ForeignId, ForeignType, MetadataType, IsMandatory, Position, ScreenLabel, DefaultValue, AllowableValues, 
  CssClass, FormatInfo, ControlData, IsDeleted, UniqueId
 )
 SELECT     @historyId, 18, MetadataType, IsMandatory, Position, ScreenLabel, DefaultValue, AllowableValues, CssClass, FormatInfo, ControlData, Metadata_MetadataField.IsDeleted, UniqueId
 FROM         ProcessDefinition_Process INNER JOIN
        Metadata_MetadataField ON ProcessDefinition_Process.ProcessTypeId = Metadata_MetadataField.ForeignId
 WHERE     (Metadata_MetadataField.ForeignType = 17) AND (ProcessDefinition_Process.ProcessId = @ProcessId)  AND (Metadata_MetadataField.IsDeleted=0)

  INSERT INTO dbo.Metadata_MetadataField( ForeignId, ForeignType, MetadataType, IsMandatory, Position,  ScreenLabel, DefaultValue, AllowableValues, CssClass, 
  FormatInfo, ControlData, IsDeleted, UniqueId
 )
 SELECT     @historyId, 16, Metadata_MetadataField.MetadataType, Metadata_MetadataField.IsMandatory, Metadata_MetadataField.Position, 
        Metadata_MetadataField.ScreenLabel, Metadata_MetadataField.DefaultValue, Metadata_MetadataField.AllowableValues, Metadata_MetadataField.CssClass, 
        Metadata_MetadataField.FormatInfo, Metadata_MetadataField.ControlData, Metadata_MetadataField.IsDeleted, UniqueId                  
 FROM         Metadata_MetadataField 
 WHERE (Metadata_MetadataField.ForeignType = 7)  AND Metadata_MetadataField.ForeignId=@ProcessId AND (IsDeleted=0)

  INSERT INTO dbo.Metadata_MetadataField( ForeignId, ForeignType, MetadataType, IsMandatory, Position,  ScreenLabel, DefaultValue, AllowableValues, CssClass, 
  FormatInfo, ControlData, IsDeleted, UniqueId
 )
 SELECT     @historyId, 19, Metadata_MetadataField.MetadataType, Metadata_MetadataField.IsMandatory, Metadata_MetadataField.Position, 
        Metadata_MetadataField.ScreenLabel, Metadata_MetadataField.DefaultValue, Metadata_MetadataField.AllowableValues, Metadata_MetadataField.CssClass, 
        Metadata_MetadataField.FormatInfo, Metadata_MetadataField.ControlData, Metadata_MetadataField.IsDeleted, UniqueId                  
 FROM         Metadata_MetadataField 
 WHERE (Metadata_MetadataField.ForeignType = 10)  AND Metadata_MetadataField.ForeignId=@ProcessId AND (IsDeleted=0)
 
    INSERT INTO dbo.Metadata_Data([MetadataId] ,[ForeignId] ,[ForeignType],[StringValue]
      ,[IntValue]      ,[BoolValue]      ,[DoubleValue]      ,[DateValue]      ,[IsDeleted]
      ,[DateChanged]      ,[ChangedByUserId]
  )
  SELECT     Metadata_Data.MetadataId, 
  ProcessDefinition_ProcessLinkHistory.ProcessLinkHistoryId, 
   21, 
  Metadata_Data.StringValue, 
  Metadata_Data.IntValue, 
  Metadata_Data.BoolValue, 
  Metadata_Data.DoubleValue, 
        Metadata_Data.DateValue, 
  Metadata_Data.IsDeleted, 
  Metadata_Data.DateChanged, 
  Metadata_Data.ChangedByUserId
  FROM         Metadata_Data INNER JOIN
         ProcessDefinition_ProcessLinkData ON Metadata_Data.ForeignId = ProcessDefinition_ProcessLinkData.ProcessLinkDataId INNER JOIN
         ProcessDefinition_ProcessLinkHistory ON ProcessDefinition_ProcessLinkData.LinkUid = ProcessDefinition_ProcessLinkHistory.LinkUid
  WHERE     (Metadata_Data.ForeignType = 20) AND 
   (ProcessDefinition_ProcessLinkHistory.ProcessHistoryId = @historyId) AND
   (ProcessDefinition_ProcessLinkData.ProcessId = @processId)
    AND (IsDeleted=0)

  UPDATE Metadata_Data
 SET MetadataId = x.NewMetadataId
FROM (
SELECT     Metadata_Data.MetadataId AS OldMetadataId, NewMetadataId.MetadataId AS NewMetadataId
FROM         Metadata_Data INNER JOIN
                      Metadata_MetadataField ON Metadata_Data.MetadataId = Metadata_MetadataField.MetadataId INNER JOIN
                          (
SELECT     MetadataId, UniqueId
                            FROM          Metadata_MetadataField AS Metadata_MetadataField_1
                            WHERE      (ForeignId = @historyId) AND (ForeignType = 19)) AS NewMetadataId ON Metadata_MetadataField.UniqueId = NewMetadataId.UniqueId

WHERE     
Metadata_Data.ForeignType = 21  AND
Metadata_MetadataField.ForeignId = @previousHistoryId AND 
Metadata_MetadataField.ForeignType = 19 
) as x

WHERE Metadata_Data.MetadataId=x.OldMetadataId AND Metadata_Data.ForeignType = 21 


END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetTaggedProcessListByReposName]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetTaggedProcessListByReposName] 
 @repositoryName nvarchar(256),
 @useFrozen bit
WITH ENCRYPTION AS
BEGIN
   SET NOCOUNT ON;

 IF(@useFrozen = 0)
 BEGIN
  SELECT     ISNULL(Notes_Tag.TagName,'untagged') AS TagName, ISNULL(Notes_Tag.TagId,0) AS TagId, 
   ProcessDefinition_Process.ProcessId, ProcessDefinition_Process.ProcessTitle, ProcessDefinition_Process.PrettyPostTitle,ProcessDefinition_Process.ProcessTypeId
  FROM         ProcessDefinition_Repository INNER JOIN
         ProcessDefinition_Process ON ProcessDefinition_Repository.RepositoryId = ProcessDefinition_Process.ForeignId LEFT OUTER JOIN
         Notes_Tag INNER JOIN
         Notes_AssignedTag ON Notes_Tag.TagId = Notes_AssignedTag.TagId ON ProcessDefinition_Process.ProcessId = Notes_AssignedTag.ForeignId
  WHERE     (ProcessDefinition_Repository.RepositoryName = @repositoryName) AND IsDeleted=0
  ORDER BY Notes_Tag.TagName, ProcessDefinition_Process.ProcessTitle
 END
 ELSE
 BEGIN
  SELECT ISNULL(Notes_Tag.TagName, 'untagged') AS TagName, ISNULL(Notes_Tag.TagId, 0) AS TagId, FrozenProcesses.ProcessId, FrozenProcesses.ProcessTitle, FrozenProcesses.PrettyPostTitle, FrozenProcesses.ProcessTypeId
  FROM  ProcessDefinition_Repository INNER JOIN
      (SELECT ProcessId, ProcessTitle, PrettyPostTitle, ForeignId, ProcessTypeId
     FROM  ProcessDefinition_ProcessHistory
     WHERE (IsFrozenVersion = 1)) AS FrozenProcesses ON ProcessDefinition_Repository.RepositoryId = FrozenProcesses.ForeignId INNER JOIN
     ProcessDefinition_Process ON FrozenProcesses.ProcessId = ProcessDefinition_Process.ProcessId LEFT OUTER JOIN
     Notes_Tag INNER JOIN
     Notes_AssignedTag ON Notes_Tag.TagId = Notes_AssignedTag.TagId ON FrozenProcesses.ProcessId = Notes_AssignedTag.ForeignId
  WHERE (ProcessDefinition_Repository.RepositoryName = @repositoryName) AND (ProcessDefinition_Process.IsDeleted = 0)
  ORDER BY TagName, FrozenProcesses.ProcessTitle
 END
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetAllProcessHistories]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetAllProcessHistories]
(
     @repositoryName nvarchar(256)
)
WITH ENCRYPTION AS
BEGIN
 
               SET NOCOUNT ON;

 DECLARE @RepositoryId int
 SET @RepositoryId=dbo.ProcessDefinition_GetRepositoryId(@repositoryName) 

 SELECT ProcessHistoryId, ProcessDescription, ProcessId, IsLatestVersion, MajorVersion, MinorVersion, ProcessTitle
 FROM  ProcessDefinition_ProcessHistory
 WHERE (ForeignId = @RepositoryId)

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProcessDefinition_CreateStepPredecessor]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_CreateStepPredecessor] 
 @ParentProcessId int,
 @StepUId uniqueidentifier,
 @FromStepUId uniqueidentifier,
 @PredecessorText nvarchar(250)
AS
BEGIN
   SET NOCOUNT ON;

  DECLARE @stepId int
 DECLARE @fromStepId int

 SELECT @stepId = ProcessStepId FROM ProcessDefinition_Step WHERE StepUId = @StepUId
 SELECT @fromStepId = ProcessStepId FROM ProcessDefinition_Step WHERE StepUId = @FromStepUId

 INSERT INTO [ProcessDefinition_StepPredecessor]
           ([ParentProcessId]
           ,[ProcessStepId]
           ,[StepUId]
           ,[FromProcessStepId]
           ,[FromStepUId]
           ,[PredecessorText])
     VALUES (@ParentProcessId, @StepId, @StepUId, @FromStepId, @FromStepUId, @PredecessorText)

 RETURN Scope_Identity()
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetProcessXml]'
GO
ALTER FUNCTION [dbo].[ProcessDefinition_GetProcessXml] (
  @processId int 
)
RETURNS Xml
WITH ENCRYPTION AS
BEGIN

 DECLARE @latestHistoryId INT
 SELECT @latestHistoryId  = ProcessHistoryId 
 FROM ProcessDefinition_ProcessHistory
 WHERE  ProcessId = @ProcessId AND IsLatestVersion = 1

  DECLARE @RetValue Xml
 
 SET @RetValue = 
  ( SELECT     ProcessUId AS [id], 
  ProcessDescription AS [description],
  DateCreated AS [created],
  ProcessTitle AS [title],
  PrettyPostTitle AS [url],
  DateUpdated AS [updated],
  MinorVersion AS [revision],
  TypeUId AS [typeId],
  AltGroup AS [altGroup],
  ProcessDefinition_Process.IsDeleted AS [deleted],
  RelationshipGraph AS [relGraph],
  StepDefinitionVersion AS [defnVersion],
  StepsSvg AS [stepsSvg],

(SELECT     SectionName AS [section], 
  LinkUri AS [link],
  FullLinkUri as [fullLink],   LinkText as [text],
  (SELECT ProcessUId FROM ProcessDefinition_Process WHERE ProcessId=LinksToProcessId) AS [linkedProcessId]
      FROM         ProcessDefinition_ProcessLink
WHERE     (ProcessId = @processId)
FOR XML PATH('link'), ROOT('links'), ELEMENTS, type),
  
(SELECT StepUId AS [id],    
  StepName AS [name], 
  StepDescription AS [description],
  StepPosition as [position],
  (SELECT ProcessUId FROM ProcessDefinition_Process WHERE ProcessId=ChildProcessId) AS [stepProcessId],
        CASE WHEN (StepType & 2) != 0 THEN 'true' END AS [concurrent],
        CASE WHEN (StepType & 4) != 0 THEN 'true' END AS [optional],
        CASE WHEN (StepType & 8) != 0 THEN 'true' END AS [iterative],
  StepType AS [stepType],
  bpmnJson
FROM         ProcessDefinition_Step
WHERE     (ParentProcessId = @processId)
FOR XML PATH('step'), ROOT('steps'), ELEMENTS, type),

(SELECT StepUId AS [id],    
  FromStepUid AS [fromid], 
  PredecessorText AS [text]
FROM         ProcessDefinition_StepPredecessor
WHERE     (ParentProcessId = @processId)
FOR XML PATH('predecessor'), ROOT('predecessors'), ELEMENTS, type),

(SELECT     TagName AS [name]
FROM         Notes_Tag INNER JOIN
                      Notes_AssignedTag ON Notes_Tag.TagId = Notes_AssignedTag.TagId
WHERE     (Notes_AssignedTag.ForeignId = @processId)
FOR XML PATH('tag'), ROOT('tags'), ELEMENTS, type),


(SELECT UniqueId as [id],    
  ScreenLabel AS [label], 
  MetadataType AS [type],
  Position as [position],
  DefaultValue as [default],
  AllowableValues as [allowable],
  FormatInfo as [format],
  ControlData as [data]   
FROM         Metadata_MetadataField
WHERE     (ForeignId = @latestHistoryId AND ForeignType = 16)
FOR XML PATH('field'), ROOT('metadata'), ELEMENTS, type),

(SELECT UniqueId as [id],    
  ScreenLabel AS [label], 
  MetadataType AS [type],
  Position as [position],
  DefaultValue as [default],
  AllowableValues as [allowable],
  FormatInfo as [format],
  ControlData as [data]   
FROM         Metadata_MetadataField
WHERE     (ForeignId = @latestHistoryId AND ForeignType = 19)
FOR XML PATH('field'), ROOT('usageProperties'), ELEMENTS, type),


(
SELECT    Metadata_MetadataField.UniqueId as [id],
    Metadata_Data.StringValue as [stringvalue], 
    Metadata_Data.IntValue as [intvalue], 
    Metadata_Data.BoolValue as [boolvalue], 
    Metadata_Data.DoubleValue as [doublevalue], 
    Metadata_Data.DateValue as [datevalue], 
    Metadata_Data.DateChanged as [datechanged],
    ProcessDefinition_ProcessLinkHistory.LinkUid as [linkUid]
FROM          Metadata_Data INNER JOIN
                      Metadata_MetadataField ON Metadata_Data.MetadataId = Metadata_MetadataField.MetadataId INNER JOIN
                      ProcessDefinition_ProcessLinkHistory ON Metadata_Data.ForeignId = ProcessDefinition_ProcessLinkHistory.ProcessLinkHistoryId
  WHERE     (Metadata_Data.ForeignType = 21) AND 
   (ProcessDefinition_ProcessLinkHistory.ProcessHistoryId = @latestHistoryId ) AND Metadata_Data.IsDeleted =0
FOR XML PATH('field'), ROOT('usageData'), ELEMENTS, type)

 FROM         ProcessDefinition_Process INNER JOIN
                      ProcessDefinition_ProcessType ON ProcessDefinition_Process.ProcessTypeId = ProcessDefinition_ProcessType.ProcessTypeId
 WHERE     (ProcessId = @processId)
 FOR XML PATH('process'), ELEMENTS, type)

 RETURN @RetValue
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetTaggedProjectProcessListByReposName]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetTaggedProjectProcessListByReposName] 
 @repositoryName nvarchar(256),
 @projectPlanId int
WITH ENCRYPTION AS
BEGIN
   SET NOCOUNT ON;

 SELECT     ISNULL(Notes_Tag.TagName, 'untagged') AS TagName, ISNULL(Notes_Tag.TagId, 0) AS TagId, ProcessDefinition_ProcessHistory.ProcessId, 
        ProcessDefinition_ProcessHistory.ProcessTitle, ProcessDefinition_ProcessHistory.PrettyPostTitle
 FROM         ProcessDefinition_Repository INNER JOIN
        ProcessDefinition_ProcessHistory ON ProcessDefinition_Repository.RepositoryId = ProcessDefinition_ProcessHistory.ForeignId INNER JOIN
        ProcessDefinition_ProjectProcessVersion ON 
        ProcessDefinition_ProcessHistory.ProcessHistoryId = ProcessDefinition_ProjectProcessVersion.ProcessHistoryId LEFT OUTER JOIN
        Notes_Tag INNER JOIN
        Notes_AssignedTag ON Notes_Tag.TagId = Notes_AssignedTag.TagId ON ProcessDefinition_ProcessHistory.ProcessId = Notes_AssignedTag.ForeignId
 WHERE     (ProcessDefinition_Repository.RepositoryName = @repositoryName) AND (ProcessDefinition_ProjectProcessVersion.ProjectPlanId = @projectPlanId)
 AND ProcessDefinition_ProcessHistory.IsDeleted=0
 ORDER BY TagName, ProcessDefinition_ProcessHistory.ProcessTitle


END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_GetProcessesLinkUsage]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_GetProcessesLinkUsage] 
 @processId int
WITH ENCRYPTION AS
BEGIN
   SET NOCOUNT ON;

 SELECT     ProcessId, 
   SectionName, 
   LinkText, 
   ProcessTitle, 
   PrettyPostTitle, 
   DescriptionSnippet,
   ProcessTypeId,
   LinkType,
   SectionDirection
 FROM         (
 SELECT     ProcessDefinition_ProcessLink.ProcessId, 
    ProcessDefinition_ProcessLink.SectionName, 
    ProcessDefinition_ProcessLink.LinkText, 
    ProcessDefinition_Process.ProcessTitle, 
    ProcessDefinition_Process.PrettyPostTitle, 
    ProcessDefinition_Process.DescriptionSnippet,
    ProcessDefinition_Process.ProcessTypeId,
    LinkType, SectionDirection
 FROM         ProcessDefinition_ProcessLink INNER JOIN
        ProcessDefinition_Process ON ProcessDefinition_ProcessLink.ProcessId = ProcessDefinition_Process.ProcessId
 WHERE     (ProcessDefinition_ProcessLink.LinksToProcessId = @processId) AND (ProcessDefinition_Process.IsDeleted=0)
 GROUP BY ProcessDefinition_ProcessLink.ProcessId, ProcessDefinition_ProcessLink.SectionName, ProcessDefinition_ProcessLink.LinkText, 
        ProcessDefinition_Process.ProcessTitle, ProcessDefinition_Process.PrettyPostTitle, 
   ProcessDefinition_Process.DescriptionSnippet, ProcessTypeId, LinkType, SectionDirection
 UNION
SELECT     ProcessDefinition_Process.ProcessId, 'Steps' AS SectionName, ProcessDefinition_Step.StepName AS LinkText, ProcessDefinition_Process.ProcessTitle, 
                      ProcessDefinition_Process.PrettyPostTitle, ProcessDefinition_Process.DescriptionSnippet,
   ProcessDefinition_Process.ProcessTypeId, CASE WHEN ((ProcessDefinition_Step.StepType & 4) > 0) THEN 4 ELSE 0 END AS LinkType, '' AS SectionDirection
FROM         ProcessDefinition_Step INNER JOIN
                      ProcessDefinition_Process ON ProcessDefinition_Step.ParentProcessId = ProcessDefinition_Process.ProcessId
WHERE     (ProcessDefinition_Step.ChildProcessId = @processId) AND (ProcessDefinition_Process.IsDeleted=0)
GROUP BY ProcessDefinition_Process.ProcessId, ProcessDefinition_Step.StepName, ProcessDefinition_Process.ProcessTitle, ProcessDefinition_Process.PrettyPostTitle, 
                      ProcessDefinition_Process.DescriptionSnippet, ProcessTypeId, ProcessDefinition_Step.StepType
 ) AS Links

 ORDER BY Links.SectionName, Links.ProcessTitle

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProcessDefinition_GetProcessesForRename]'
GO
CREATE PROCEDURE [dbo].[ProcessDefinition_GetProcessesForRename]
     @repositoryName nvarchar(256)
AS
BEGIN
 
               SET NOCOUNT ON;

 DECLARE @RepositoryId int
 SET @RepositoryId=dbo.ProcessDefinition_GetRepositoryId(@repositoryName)
 
 SELECT ProcessId, ProcessTitle, PrettyPostTitle, ProcessDescription
 FROM  ProcessDefinition_Process
 WHERE (ForeignId = @RepositoryId)
 ORDER BY IsDeleted, ProcessId

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcessDefinition_CreateStep]'
GO
ALTER PROCEDURE [dbo].[ProcessDefinition_CreateStep] 
 @ParentProcessId int,
 @ChildProcessId int,
 @StepName nvarchar(256),
 @StepDescription nvarchar(max),
 @StepPosition int,
 @StepType int,
 @IterationDescription nvarchar(1024),
 @OptionalDescription  nvarchar(1024),
 @bpmnJson nvarchar(MAX)
WITH ENCRYPTION AS
BEGIN
   SET NOCOUNT ON;

 DECLARE @insertedTbl table( stepUid uniqueidentifier);

 INSERT INTO ProcessDefinition_Step
        (ParentProcessId, StepName,StepDescription, ChildProcessId, StepPosition, StepType, IterationDescription, 
        OptionalDescription, bpmnJson)
 OUTPUT INSERTED.StepUId INTO @insertedTbl
 VALUES     (@ParentProcessId,@StepName,@StepDescription,@ChildProcessId,@StepPosition,@StepType,@IterationDescription,
  @OptionalDescription, @bpmnJson)

 SELECT stepUId FROM @insertedTbl
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
	PRINT 'The database update failed'
END
GO