/****** Object:  Schema [mig_config]    Script Date: 06-11-2021 18:49:27 ******/
CREATE SCHEMA [mig_config]
GO
/****** Object:  Schema [mig_director]    Script Date: 06-11-2021 18:49:27 ******/
CREATE SCHEMA [mig_director]
GO
/****** Object:  Schema [mig_project]    Script Date: 06-11-2021 18:49:27 ******/
CREATE SCHEMA [mig_project]
GO
/****** Object:  Schema [mig_tracking]    Script Date: 06-11-2021 18:49:27 ******/
CREATE SCHEMA [mig_tracking]
GO
/****** Object:  Schema [mig_translation]    Script Date: 06-11-2021 18:49:27 ******/
CREATE SCHEMA [mig_translation]
GO
/****** Object:  Schema [mig_utility]    Script Date: 06-11-2021 18:49:27 ******/
CREATE SCHEMA [mig_utility]
GO
/****** Object:  Table [mig_project].[AdditionalText]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_project].[AdditionalText](
	[AdditionalTextId] [bigint] IDENTITY(1,1) NOT NULL,
	[ProjectID] [uniqueidentifier] NOT NULL,
	[EventID] [uniqueidentifier] NOT NULL,
	[Sequence] [int] NOT NULL,
	[TextID] [uniqueidentifier] NOT NULL,
	[RunDate] [datetime] NOT NULL,
	[IsPresent] [bit] NOT NULL,
 CONSTRAINT [PK_AdditionalText] PRIMARY KEY CLUSTERED 
(
	[AdditionalTextId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [mig_project].[BusinessEntity]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_project].[BusinessEntity](
	[BusinessEntityID] [uniqueidentifier] NOT NULL,
	[BusinessEntityName] [nvarchar](50) NOT NULL,
	[TeamAnchor] [nvarchar](128) NULL,
 CONSTRAINT [PK_BusinessEntity] PRIMARY KEY CLUSTERED 
(
	[BusinessEntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [mig_project].[Entity]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_project].[Entity](
	[EntityID] [uniqueidentifier] NOT NULL,
	[BusinessEntityID] [uniqueidentifier] NOT NULL,
	[EntityName] [nvarchar](128) NOT NULL,
	[Ignore] [bit] NOT NULL,
	[Present] [bit] NOT NULL,
 CONSTRAINT [PK_Entity] PRIMARY KEY CLUSTERED 
(
	[EntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [mig_project].[LanguageText]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_project].[LanguageText](
	[TextID] [uniqueidentifier] NOT NULL,
	[IsoCode639] [nchar](2) NOT NULL,
	[Text] [nvarchar](max) NULL,
	[RunDate] [datetime] NOT NULL,
 CONSTRAINT [PK_LanguageText] PRIMARY KEY CLUSTERED 
(
	[TextID] ASC,
	[IsoCode639] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [mig_project].[Parameter]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_project].[Parameter](
	[ParameterName] [nvarchar](50) NOT NULL,
	[Application] [nvarchar](50) NOT NULL,
	[IsReadOnly] [bit] NOT NULL,
	[ParameterValue] [xml] NOT NULL,
 CONSTRAINT [PK_Parameter] PRIMARY KEY CLUSTERED 
(
	[ParameterName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [mig_project].[Partition]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_project].[Partition](
	[PartitionValue] [nvarchar](50) NOT NULL,
	[Label] [nvarchar](50) NOT NULL,
	[RunDate] [datetime] NULL,
 CONSTRAINT [PK_Partition] PRIMARY KEY CLUSTERED 
(
	[PartitionValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [mig_tracking].[Comment]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_tracking].[Comment](
	[CommentId] [int] IDENTITY(1,1) NOT NULL,
	[MessageID] [uniqueidentifier] NOT NULL,
	[EntityID] [uniqueidentifier] NOT NULL,
	[PartitionValue] [nvarchar](50) NULL,
	[AddedBy] [nvarchar](128) NOT NULL,
	[AddedDate] [datetime] NOT NULL,
	[EditedDate] [datetime] NULL,
	[Text] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Comment] PRIMARY KEY CLUSTERED 
(
	[CommentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [mig_tracking].[DerivedMessage]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_tracking].[DerivedMessage](
	[MessageID] [uniqueidentifier] NOT NULL,
	[EntityID] [uniqueidentifier] NOT NULL,
	[ParentMessageID] [uniqueidentifier] NOT NULL,
	[ParentEntityID] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_DerivedMessage] PRIMARY KEY CLUSTERED 
(
	[MessageID] ASC,
	[EntityID] ASC,
	[ParentMessageID] ASC,
	[ParentEntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [mig_tracking].[EntityEventAnchor]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_tracking].[EntityEventAnchor](
	[MessageID] [uniqueidentifier] NOT NULL,
	[EntityID] [uniqueidentifier] NOT NULL,
	[WorkLevel] [int] NULL,
	[TeamAnchor] [nvarchar](128) NULL,
 CONSTRAINT [PK_EntityEventAnchor] PRIMARY KEY CLUSTERED 
(
	[MessageID] ASC,
	[EntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [mig_tracking].[EntityEventState]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_tracking].[EntityEventState](
	[TrackID] [uniqueidentifier] NOT NULL,
	[MessageID] [uniqueidentifier] NOT NULL,
	[EntityID] [uniqueidentifier] NOT NULL,
	[Time] [datetime2](7) NOT NULL,
	[UserState] [int] NOT NULL,
 CONSTRAINT [PK_EntityEventState] PRIMARY KEY CLUSTERED 
(
	[TrackID] ASC,
	[MessageID] ASC,
	[EntityID] ASC,
	[Time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [mig_tracking].[EntityEventTag]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_tracking].[EntityEventTag](
	[MessageID] [uniqueidentifier] NOT NULL,
	[EntityID] [uniqueidentifier] NOT NULL,
	[TagId] [int] NOT NULL,
	[TagUsageId] [int] NOT NULL,
 CONSTRAINT [PK_EventEventTag] PRIMARY KEY CLUSTERED 
(
	[MessageID] ASC,
	[EntityID] ASC,
	[TagId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [mig_tracking].[EventMessage]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_tracking].[EventMessage](
	[MessageID] [uniqueidentifier] NOT NULL,
	[EventType] [char](1) NOT NULL,
	[Engine] [tinyint] NOT NULL,
	[Step] [tinyint] NOT NULL,
	[Disposition] [tinyint] NOT NULL,
	[Impact] [tinyint] NOT NULL,
	[Receiver] [tinyint] NOT NULL,
	[EventID] [uniqueidentifier] NULL,
	[EventCode] [varchar](10) NULL,
	[TextID] [uniqueidentifier] NULL,
	[MessageFmt] [nvarchar](max) NULL,
	[RunDate] [datetime] NOT NULL,
 CONSTRAINT [PK_EventMessage] PRIMARY KEY CLUSTERED 
(
	[MessageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [mig_tracking].[Log]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_tracking].[Log](
	[LogId] [bigint] IDENTITY(1,1) NOT NULL,
	[TrackID] [uniqueidentifier] NOT NULL,
	[MessageID] [uniqueidentifier] NOT NULL,
	[EntityID] [uniqueidentifier] NOT NULL,
	[PartitionValue] [nvarchar](50) NULL,
	[LogType] [int] NOT NULL,
	[LogTime] [datetime2](7) NOT NULL,
	[LogUser] [nvarchar](128) NOT NULL,
	[Property] [nvarchar](128) NOT NULL,
	[Before] [nvarchar](max) NULL,
	[After] [nvarchar](max) NULL,
 CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED 
(
	[LogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [mig_tracking].[PartitionEventState]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_tracking].[PartitionEventState](
	[TrackID] [uniqueidentifier] NOT NULL,
	[MessageID] [uniqueidentifier] NOT NULL,
	[EntityID] [uniqueidentifier] NOT NULL,
	[PartitionValue] [nvarchar](50) NOT NULL,
	[Time] [datetime2](7) NOT NULL,
	[UserState] [int] NOT NULL,
 CONSTRAINT [PK_PartitionEventState] PRIMARY KEY CLUSTERED 
(
	[TrackID] ASC,
	[MessageID] ASC,
	[EntityID] ASC,
	[PartitionValue] ASC,
	[Time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [mig_tracking].[PartitionEventUser]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_tracking].[PartitionEventUser](
	[MessageID] [uniqueidentifier] NOT NULL,
	[EntityID] [uniqueidentifier] NOT NULL,
	[PartitionValue] [nvarchar](50) NOT NULL,
	[ExternalUser] [nvarchar](128) NULL,
 CONSTRAINT [PK_PartitionEventUser] PRIMARY KEY CLUSTERED 
(
	[MessageID] ASC,
	[EntityID] ASC,
	[PartitionValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [mig_translation].[TranslationValueset]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [mig_translation].[TranslationValueset](
	[ValuesetID] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
	[Present] [bit] NOT NULL,
	[Ignore] [bit] NOT NULL,
	[DisplayTextID] [uniqueidentifier] NULL,
	[DescriptionTextID] [uniqueidentifier] NULL,
 CONSTRAINT [PK_TranslationValueset] PRIMARY KEY CLUSTERED 
(
	[ValuesetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Index [IX_AdditionalText]    Script Date: 06-11-2021 18:49:27 ******/
CREATE NONCLUSTERED INDEX [IX_AdditionalText] ON [mig_project].[AdditionalText]
(
	[EventID] ASC,
	[IsPresent] ASC
)
INCLUDE([TextID],[Sequence]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IX_Comment_EntityID]    Script Date: 06-11-2021 18:49:27 ******/
CREATE NONCLUSTERED INDEX [IX_Comment_EntityID] ON [mig_tracking].[Comment]
(
	[MessageID] ASC,
	[EntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IX_Comment_PartitionValue]    Script Date: 06-11-2021 18:49:27 ******/
CREATE NONCLUSTERED INDEX [IX_Comment_PartitionValue] ON [mig_tracking].[Comment]
(
	[MessageID] ASC,
	[EntityID] ASC,
	[PartitionValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IX_EventMessage_User]    Script Date: 06-11-2021 18:49:27 ******/
CREATE NONCLUSTERED INDEX [IX_EventMessage_User] ON [mig_tracking].[EventMessage]
(
	[EventType] ASC,
	[Engine] ASC,
	[Step] ASC,
	[Disposition] ASC,
	[Impact] ASC,
	[Receiver] ASC,
	[EventID] ASC,
	[TextID] ASC,
	[EventCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IX_Log_1]    Script Date: 06-11-2021 18:49:27 ******/
CREATE NONCLUSTERED INDEX [IX_Log_1] ON [mig_tracking].[Log]
(
	[TrackID] ASC,
	[MessageID] ASC,
	[EntityID] ASC,
	[LogId] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IX_Log_2]    Script Date: 06-11-2021 18:49:27 ******/
CREATE NONCLUSTERED INDEX [IX_Log_2] ON [mig_tracking].[Log]
(
	[TrackID] ASC,
	[MessageID] ASC,
	[EntityID] ASC,
	[PartitionValue] ASC,
	[LogId] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [mig_project].[Entity] ADD  CONSTRAINT [DF_Entity_Ignore]  DEFAULT ((0)) FOR [Ignore]
GO
ALTER TABLE [mig_project].[Entity] ADD  CONSTRAINT [DF_Entity_Present]  DEFAULT ((1)) FOR [Present]
GO
ALTER TABLE [mig_project].[Parameter] ADD  CONSTRAINT [DF_Parameter_Application]  DEFAULT ((1)) FOR [Application]
GO
ALTER TABLE [mig_project].[Parameter] ADD  CONSTRAINT [DF_Parameter_IsReadOnly]  DEFAULT ((1)) FOR [IsReadOnly]
GO
ALTER TABLE [mig_tracking].[EntityEventAnchor] ADD  CONSTRAINT [DF_EntityEventAnchor_WorkLevel]  DEFAULT ((0)) FOR [WorkLevel]
GO
ALTER TABLE [mig_tracking].[EventMessage] ADD  CONSTRAINT [DF_EventMessage_MessageID]  DEFAULT (newid()) FOR [MessageID]
GO
ALTER TABLE [mig_translation].[TranslationValueset] ADD  CONSTRAINT [DF_TranslationValueset_Ignore]  DEFAULT ((0)) FOR [Ignore]
GO
/****** Object:  StoredProcedure [mig_director].[AdditionalText_Insert]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [mig_director].[AdditionalText_Insert]
	@xml xml
,   @projectID uniqueidentifier
,	@runDate datetime
as
begin
	declare @addTxt table([EventID] uniqueidentifier, [Sequence] int, [TextID] uniqueidentifier)

	;with [addTxt] as (
		select
			evt.id.value('@id', 'uniqueidentifier') as [eventID]
		,   txt.id.value('@id', 'uniqueidentifier') as [textID]
		from
			@xml.nodes('/*/event') AS evt(id)
		cross apply
			evt.id.nodes('*') AS txt(id)
	)
	insert @addTxt
	select
		[eventID]
	,   row_number() over(partition by eventID order by (select 1))
	,   [textID]
	from [addTxt]

	begin transaction

	update [mig_project].[AdditionalText]
	set
		[IsPresent] = 0
	where
		[ProjectID] = @projectID

	update [mig_project].[AdditionalText]
	set
		[Sequence] = A.[Sequence]
	,   [IsPresent] = 1
	,   [RunDate] = @runDate
	from
		@addTxt as A
		left outer join [mig_project].[AdditionalText] as B
		on
			A.[EventID] = B.[EventID]
		and A.[TextID] = B.[TextID]

	insert [mig_project].[AdditionalText] (
		[ProjectID]
	,   [EventID]
	,   [Sequence]
	,   [TextID]
	,   [IsPresent]
	,   [RunDate]
	)
	select
		@projectID
	,   A.[EventID]
	,   A.[Sequence]
	,   A.[TextID]
	,   1
	,   @runDate
	from
		@addTxt as A
		left outer join [mig_project].[AdditionalText] as B
		on
			A.[EventID] = B.[EventID]
		and A.[TextID] = B.[TextID]
	where
		B.[EventID] is null

	commit
end
GO
/****** Object:  StoredProcedure [mig_director].[BusinessEntity_SetAnchor]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[BusinessEntity_SetAnchor] 
	@businessEntityID uniqueidentifier
,	@teamAnchor nvarchar(128)
,	@teamAnchorName nvarchar(256) = null
,	@user nvarchar(128)
as
	update [mig_project].[BusinessEntity]
	set
		[TeamAnchor] = @teamAnchor
	where
		[BusinessEntityID] = @businessEntityID

	-- Clean up EntityEventState
	declare @cleaned table([MessageID] uniqueidentifier, [EntityID] uniqueidentifier)

	update [mig_tracking].[EntityEventAnchor]
	set
		[TeamAnchor] = null
	output deleted.[MessageID], deleted.[EntityID] into @cleaned
	from
		[mig_tracking].[EntityEventAnchor] as A
		inner join [mig_project].[Entity] as B
			on A.[EntityID] = B.[EntityID]
		inner join [mig_project].[BusinessEntity] as C
			on 
				B.[BusinessEntityID] = C.[BusinessEntityID]
			and	C.[TeamAnchor] = A.[TeamAnchor]
	where
		A.[TeamAnchor] is not null
	and	C.[BusinessEntityID] = @businessEntityID

	declare @now datetime = getdate()

	insert [mig_tracking].[Log] (
		[TrackID]
	,	[MessageID]
	,	[EntityID]
	,	[PartitionValue]
	,	[LogType]
	,	[LogTime]
	,	[LogUser]
	,	[Property]
	,	[Before]
	,	[After]
	)
	select
		'00000000-0000-0000-0000-000000000000'
	,	[MessageID]
	,	[EntityID]
	,	null
	,	2 -- Removed
	,	@now
	,	@user
	,	'Team Anchor'
	,	isnull(@teamAnchorName, 'Specified')
	,	'Default (Team Anchor for entity was changed to ' + isnull(@teamAnchorName, 'the specified user') + ')'
	from @cleaned
GO
/****** Object:  StoredProcedure [mig_director].[Entity_ResetPresent]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [mig_director].[Entity_ResetPresent]
as
	update [mig_project].[Entity]
	set
		[Present] = 0
GO
/****** Object:  StoredProcedure [mig_director].[Entity_SetOutOfScope]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [mig_director].[Entity_SetOutOfScope]
(
	@entityID uniqueidentifier
,	@ignore bit
) as
	update [mig_project].[Entity]
	set 
		[Ignore] = @ignore
	where
		[EntityID] = @entityID
GO
/****** Object:  StoredProcedure [mig_director].[Entity_Synchronize]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [mig_director].[Entity_Synchronize]
(
  @businessEntityID uniqueidentifier
, @businessEntityName nvarchar(50)
, @entityID uniqueidentifier
, @entityName nvarchar(256)
)
as
  update [mig_project].[Entity]
  set 
		[EntityName] = @entityName 
	, [BusinessEntityID] = @businessEntityID
	, [Present] = 1
  where 
		[EntityID] = @entityID

	if 0 = @@rowCount
	begin
	insert into [mig_project].[Entity] (
			[EntityID]
		, [BusinessEntityID]
		, [EntityName]
		) values (
			@entityID
		, @businessEntityID
		, @entityName
		)
	end

  update [mig_project].[BusinessEntity]
  set 
		[BusinessEntityName] = @businessEntityName 
  where 
		[BusinessEntityID] = @businessEntityID

	if 0 = @@rowCount
	begin
	insert into [mig_project].[BusinessEntity] (
			[BusinessEntityID]
		, [BusinessEntityName]
		) values (
			@businessEntityID
		, @businessentityName
		)
	end
GO
/****** Object:  StoredProcedure [mig_director].[EntityHierarchy_List]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[EntityHierarchy_List] 
AS
  select 
	[BusinessEntityID]
  , [BusinessEntityName]
  , isnull([TeamAnchor], '') as [TeamAnchorId]
  from
	[mig_project].[BusinessEntity]
  order by [BusinessEntityName]

  select
	[EntityID]
  , [BusinessEntityID]
  , [EntityName]
	, [Ignore] 
  from 
	[mig_project].[Entity]
  order by [EntityName]
GO
/****** Object:  StoredProcedure [mig_director].[LanguageText_Insert]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[LanguageText_Insert] 
	@textID uniqueidentifier
, @xml xml
, @runDate datetime
as
	declare @texts table([IsoCode639] char(2), [Text] nvarchar(max))

	;with 
		txtXml as (
			select T.c.query('.') as [Txt] 
			from @xml.nodes('/*/text') T(c)
		)
	, txt as (
		select
			[Txt].value('(*/@language)[1]', 'char(2)') as [IsoCode639] 
		,	[Txt].value('(*)[1]', 'nvarchar(max)') as [Text]
		from txtXml
		)
	insert @texts
	select
	  [IsoCode639]
	, [Text]
	from
		txt 

	insert [mig_project].[LanguageText]
	select
		@textID
	, A.[IsoCode639]
	, A.[Text]
	, @runDate
	from
		@texts as A
		left outer join [mig_project].[LanguageText] as B
		on
			A.[IsoCode639] = B.[IsoCode639]
		and B.[TextID] = @textID
	where
		B.[TextID] is null

	update [mig_project].[LanguageText]
	set
		[Text] = B.[Text]
	,   [RunDate] = @runDate
	from 
		[mig_project].[LanguageText] as A
		inner join @texts as B
		on
			A.[IsoCode639] = B.[IsoCode639]
		and A.[TextID] = @textID
GO
/****** Object:  StoredProcedure [mig_director].[Parameter_List]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[Parameter_List] 
AS
  select 
	ParameterName
  , ParameterValue
  from
	mig_project.Parameter
  where 
	  [Application] = 'Director'
  and IsReadOnly = 0

GO
/****** Object:  StoredProcedure [mig_director].[Parameter_Set]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[Parameter_Set] 
(
  @parameterName nvarchar(50)
, @parameterValue xml
)
AS

  update 
	mig_project.Parameter
  set 
	ParameterValue = @parameterValue
  where 
	ParameterName = @parameterName

	if @@rowcount = 0
	begin
		insert into mig_project.Parameter (
			ParameterName
		, Application
		, IsReadOnly
		, ParameterValue
		) values (
			@parameterName
		, 'RunTracking'
		,	1
		, @parameterValue
		)
	end

GO
/****** Object:  StoredProcedure [mig_director].[Partition_List]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[Partition_List] 
AS

  select 
	[PartitionValue]
  , [Label]
  from 
	[mig_project].[Partition]
GO
/****** Object:  StoredProcedure [mig_director].[Partition_Synchronize]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[Partition_Synchronize] 
	@xml xml  
, @runDate datetime
as
	declare @partitions table([PartitionValue] varchar(50)) 

	;with 
		[xml] as (
			select T.c.query('.') as [PartitionEl]  
			from @xml.nodes('/*/partitionValue') T(c)
		)
		insert @partitions
		select
			[PartitionEl].value('(*)[1]', 'varchar(50)') 
		from [xml]

	-- Insert new
	insert [mig_project].[Partition](
	[PartitionValue]
  , [Label]
  , [RunDate])
	select 
		A.[PartitionValue]
	, A.[PartitionValue]
	, @runDate
	from 
		@partitions as A
		left outer join [mig_project].[Partition] as B
			on A.[PartitionValue] = B.[PartitionValue]
	where 
		B.[PartitionValue] is null
	
	-- Delete obsolete
	delete from [mig_project].[Partition]
	from 
		[mig_project].[Partition] as A
		left outer join @partitions as B 
			ON A.[PartitionValue] = B.[PartitionValue]
	where 
		B.[PartitionValue] is null;
GO
/****** Object:  StoredProcedure [mig_director].[PartitionValue_Remove]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[PartitionValue_Remove] 
(
  @partitionValue varchar(50)  
)
AS
  delete from 
	[mig_project].[Partition]
  where
	[PartitionValue] = @partitionValue
GO
/****** Object:  StoredProcedure [mig_director].[PartitionValue_Set]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_director].[PartitionValue_Set] 
(
  @partitionValue nvarchar(50) 
, @label nvarchar(50) 
)
AS

  update [mig_project].[Partition]
  set
	Label = @label
  where
	[PartitionValue] = @partitionValue 

  if 0 = @@rowcount
	insert [mig_project].[Partition] (
	  [PartitionValue]
	, [Label]
	) values (
	  @partitionValue
	, @label
	)
GO
/****** Object:  StoredProcedure [mig_tracking].[Event_List]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_tracking].[Event_List] 
as
	select 
		[MessageID]
	,   [EventType]
	,	[Step] 
	,	[Engine] 
	,   [Disposition]
	,	[Impact]
	,   [Receiver] 
	,   [EventID]
	,   [MessageFmt] 
	from [mig_tracking].[EventMessage]
GO
/****** Object:  StoredProcedure [mig_tracking].[EventSystem_InsertOrUpdate]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_tracking].[EventSystem_InsertOrUpdate] 
	@engine tinyint
,   @step tinyint
,   @disposition tinyint 
,   @impact tinyint 
,   @receiver tinyint
,   @message varchar(max) 
,   @runDate datetime
as
	------------------------------------------------------------------------------------------------------
	-- IMPORTANT: The 'if exists' below determines the values on the event that constitutes the key for
	-- a MessageID. This is used in the Portal to map from the EventSystem in the Migration DB to a 
	-- the MessageID in the Project DB. If these keys are changed in this Stored Procedure, it is vital
	-- to reflect the same changes in MigFx.Migration.Api.Infrastructure\Domain\MessageKey.cs
	------------------------------------------------------------------------------------------------------
	if not exists (
		select 1
		from [mig_tracking].[EventMessage]
		where
			[EventType] = 'S'
		and	[Engine] = @engine
		and	[Step] = @step
		and [Disposition] = @disposition
		and [Impact] = @impact
		and [Receiver] = @receiver
		and [MessageFmt] = @message
	)
	begin
		insert [mig_tracking].[EventMessage] (
			[MessageID]
		,   [EventType]
		,   [Engine]
		,   [Step]
		,   [Disposition]
		,	[Impact]
		,   [Receiver]
		,   [MessageFmt]
		,   [RunDate]
		) values ( 
			newid()
		,   'S'
		,   @engine
		,   @step
		,   @disposition
		,   @impact
		,   @receiver
		,   @message
		,   @runDate
		)
	end
GO
/****** Object:  StoredProcedure [mig_tracking].[EventUser_InsertOrUpdate]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_tracking].[EventUser_InsertOrUpdate] 
	@eventId uniqueidentifier
,	@textId uniqueidentifier
,   @engine tinyint
,   @step tinyint
,   @disposition tinyint 
,   @impact tinyint 
,   @receiver tinyint
,   @eventCode varchar(50) 
,   @runDate datetime
as
	------------------------------------------------------------------------------------------------------
	-- The Disposition, Impact and Receiver are part of the criteria for updating an existing row. This is
	-- because of the ability in Studio to override Disposition, Impact or Receiver when an Event is used
	------------------------------------------------------------------------------------------------------

	------------------------------------------------------------------------------------------------------
	-- IMPORTANT: The 'if exists' below determines the values on the event that constitutes the key for
	-- a MessageID. This is used in the Portal to map from the EventUser in the Migration DB to a 
	-- the MessageID in the Project DB. If these keys are changed in this Stored Procedure, it is vital
	-- to reflect the same changes in MigFx.Migration.Api.Infrastructure\Domain\MessageKey.cs
	------------------------------------------------------------------------------------------------------
	if exists(
		select 1 
		from [mig_tracking].[EventMessage]
		where
			[EventType] = 'U'
		and	[Engine] = @engine
		and	[Step] = @step
		and [Disposition] = @disposition
		and [Impact] = @impact
		and [Receiver] = @receiver
		and [EventID] = @eventId
	)
	begin
		update [mig_tracking].[EventMessage]
		set
			[TextID] = @textId
		,   [EventCode] = @eventCode
		,   [RunDate] = @runDate
		where
			[EventType] = 'U'
		and	[Engine] = @engine
		and	[Step] = @step
		and [Disposition] = @disposition
		and [Impact] = @impact
		and [Receiver] = @receiver
		and [EventID] = @eventId
		and ([TextID] <> @textID or [EventCode] <> @eventCode)
	end
	else
	begin
		insert [mig_tracking].[EventMessage] (
			[MessageID]
		,   [EventType]
		,   [Engine]
		,   [Step]
		,   [Disposition]
		,	[Impact]
		,   [Receiver]
		,   [EventID]
		,   [EventCode]
		,   [TextID]
		,   [RunDate]
		) values ( 
			newid()
		,   'U'
		,   @engine
		,   @step
		,   @disposition
		,   @impact 
		,   @receiver
		,   @eventId
		,   @eventCode
		,   @textId
		,   @runDate
		)
	end
GO
/****** Object:  StoredProcedure [mig_tracking].[UpdateStates]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [mig_tracking].[UpdateStates]
	@xml xml
,   @user varchar(256)
,   @trackID uniqueidentifier
,	@runDate datetime2
as
	declare @occurring table(
		[MessageID] uniqueidentifier not null
	,	[EntityID] uniqueidentifier not null
	,	[PartitionValue] nvarchar(50) not null
	)

	declare @updatedStates table(
		[MessageID] uniqueidentifier not null
	,	[EntityID] uniqueidentifier not null
	,	[PartitionValue] nvarchar(50) null
	)

	insert @occurring
	select 
		list.R.value('m[1]', 'uniqueidentifier') 
	,	list.R.value('e[1]', 'uniqueidentifier') 
	,	list.R.value('p[1]', 'nvarchar(50)') 
	from @xml.nodes('/*/c') as list(R)

	-- Initial EntityEventAnchor to register WorkLevel and TeamAnchor for new events
	;with
		msg as (
			select distinct
			[MessageID]
		,	[EntityID]
			from 
				@occurring
		)
	,	anchor as (
			select 
				B.[EntityID]
			,	A.[TeamAnchor]
			from
				[mig_project].[BusinessEntity] as A
				inner join [mig_project].[Entity] as B
				on
					A.[BusinessEntityID] = B.[BusinessEntityID]
		)
	insert [mig_tracking].[EntityEventAnchor] (
		[MessageID]
	,	[EntityID]
	,	[WorkLevel]
	,	[TeamAnchor]
	)
	select 
		A.[MessageID]
	,	A.[EntityID]
	,	case C.[Receiver]
			when 1 then 1 -- 1:Migration -> 1:Team
			else 0 -- 0:Source and 2:Target -> 0:Delegate
		end
	,	B.[TeamAnchor]
	from
		msg as A
		inner join anchor as B
		on
			A.[EntityID] = B.[EntityID]
		inner join [mig_tracking].[EventMessage] as C
		on
			A.[MessageID] = C.[MessageID]
		left outer join [mig_tracking].[EntityEventAnchor] as D
		on
			A.[MessageID] = D.[MessageID]
		and A.[EntityID] = D.[EntityID]
	where
		D.[MessageID] is null -- Only if the EntityEventAnchor does not exist
	and ( 
			-- Only add rows if necessary (not default values)
			B.[TeamAnchor] is not null
		or	C.[Receiver] in (0, 2) -- 0:Source, 2:Target
	)

	-- PartitionEventState
	;with
		[resolved] as (
			select
				A.[MessageID] 
			,	A.[EntityID] 
			,	A.[PartitionValue]
			,	A.[UserState]
			from
				[mig_tracking].[PartitionEventState] as A
			where
				A.[TrackID] = @trackID
			and A.[UserState] = 2 -- Resolved
			and	A.[Time] = (
				select max(B.[Time])
				from [mig_tracking].[PartitionEventState] as B
				where
					A.[TrackID] = B.[TrackID]
				and	A.[MessageID] = B.[MessageID]
				and A.[EntityID] = B.[EntityID]
				and A.[PartitionValue] = B.[PartitionValue]
				)
		)
	insert [mig_tracking].[PartitionEventState]
	output inserted.[MessageID], inserted.[EntityID], inserted.[PartitionValue] into @updatedStates
	select
		@trackID
	,	A.[MessageID]
	,	A.[EntityID]
	,	A.[PartitionValue]
	,	@runDate
	,	1 -- Regression
	from
		[resolved] as A
		inner join @occurring as B
		on
			A.[MessageID] = B.[MessageID]
		and	A.[EntityID] = B.[EntityID]
		and	A.[PartitionValue] = B.[PartitionValue]

	-- EntityEventState
	;with
		[entities] as (
			select
				[MessageID] 
			,	[EntityID] 
			from @occurring
			group by
				[MessageID] 
			,	[EntityID] 
		)
	,	[delegated] as (
			select
				[MessageID] 
			,	[EntityID] 
			from	
				[mig_tracking].[EntityEventAnchor]
			where
				[WorkLevel] = 0 -- Delegate (inverted and left outer join, because the default (no EntityEventAnchor row) is WorkLevel.Team)
		)
	,	[resolved] as (
			select
				A.[MessageID] 
			,	A.[EntityID] 
			,	A.[UserState]
			from
				[mig_tracking].[EntityEventState] as A
				left outer join [delegated] as B
				on
					A.[MessageID] = B.[MessageID]
				and	A.[EntityID] = B.[EntityID]
			where
				A.[TrackID] = @trackID
			and B.[MessageID] is null
			and A.[UserState] = 2 -- Resolved
			and	A.[Time] = (
				select max(B.[Time])
				from [mig_tracking].[EntityEventState] as B
				where
					A.[TrackID] = B.[TrackID]
				and	A.[MessageID] = B.[MessageID]
				and A.[EntityID] = B.[EntityID]
				)
		)
	insert [mig_tracking].[EntityEventState]
	output inserted.[MessageID], inserted.[EntityID], null into @updatedStates
	select
		@trackID
	,	A.[MessageID]
	,	A.[EntityID]
	,	@runDate
	,	1 -- Regression
	from
		[resolved] as A
		inner join [entities] as B
		on
			A.[MessageID] = B.[MessageID]
		and	A.[EntityID] = B.[EntityID]

	-- Log the state changes
	insert [mig_tracking].[Log] (
		[TrackID]
	,	[MessageID]
	,	[EntityID]
	,	[PartitionValue]
	,	[LogType]
	,	[LogTime]
	,	[LogUser]
	,	[Property]
	,	[Before]
	,	[After]
	)
	select
		@trackID
	,	[MessageID]
	,   [EntityID]
	,   [PartitionValue]
	,   1 -- Modified
	,   @runDate
	,   @user
	,   case
			when [PartitionValue] is null then 'Team State'
			else 'User State'
		end
	,   'Resolved'
	,   'Regression'
	from
		@updatedStates
GO
/****** Object:  StoredProcedure [mig_translation].[TranslationValueset_Update]    Script Date: 06-11-2021 18:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mig_translation].[TranslationValueset_Update] 
	@valuesetXml xml 
	-- The xml is constructed by stored procedure [mig_translation].[ListValuesets] in the migration db
	--
	-- Xml sample (displayTextID and descriptionTextId are optional)
	--<xml>
	--	<valueset>
	--		<id>7eef8e2d-0429-4786-9949-4f207d372ceb</id>
	--		<name>TranslateBankUnits</name>
	--		<displayTextID>7eef8e2d-0429-4786-9949-4f207d372ceb</displayTextID>
	--		<descriptionTextID>a5411722-071c-43ac-9963-2d4a6d6055c3</descriptionTextID>
	--	</valueset>
	--	<valueset>
	--		<id>12a1f01e-38b2-438c-882c-1f2f6b133bf8</id>
	--		<name>TranslateSegments</name>
	--	</valueset>
	--	<smartText id="7eef8e2d-0429-4786-9949-4f207d372ceb" projectId="9770707c-b2f8-430d-bb7c-fd5d585ce18a">
	--		<text language="EN">BankUnits display name</text>
	--	</smartText>
	--	<smartText id="a5411722-071c-43ac-9963-2d4a6d6055c3" projectId="9770707c-b2f8-430d-bb7c-fd5d585ce18a">
	--		<text language="EN">BankUnits decription</text>
	--	</smartText>
	--</xml>
as
	-- Update Translation Valuesets
	merge [mig_translation].[TranslationValueset] as tgt 
	using (
		select
			[VsElement].value('(*/id)[1]', 'uniqueidentifier') as [ValuesetID]
		,	[VsElement].value('(*/name)[1]', 'nvarchar(max)') as [Name]
		,	[VsElement].value('(*/displayTextID)[1]', 'uniqueidentifier') as [DisplayTextID]
		,	[VsElement].value('(*/descriptionTextID)[1]', 'uniqueidentifier') as [DescriptionTextID]
		from (
				select T.c.query('.') as [VsElement]  
				from @valuesetXml.nodes('/*/valueset') T(c)
			) as X
	) as src
	on 
		tgt.[ValuesetID] = src.[ValuesetID]
	when matched then 
		update set 
			tgt.[Present] = 1
		,	tgt.[DisplayTextID] = src.[DisplayTextID]
		,	tgt.[DescriptionTextID] = src.[DescriptionTextID]
		,	tgt.[Name] = src.[Name]
	when not matched then 
		insert (
			[ValuesetID]
		,	[Name]
		,	[Present]
		,	[DisplayTextID]
		,	[DescriptionTextID]
		) values (
			src.[ValuesetID]
		,	src.[Name]
		,	1
		,	src.[DisplayTextID]
		,	src.[DescriptionTextID]
		)
	when not matched by source then 
		update 
			set tgt.[Present] = 0;

	-- Update LanguageTexts used by Translation Valuesets
	declare @textID uniqueidentifier
	declare @xml xml
	declare @runDate datetime2 = sysdatetime()
		
	declare csr cursor for 
		select
			[VsElement].value('(*/@id)[1]', 'uniqueidentifier') as [ItemID]
		,	[VsElement] as [Xml]
		from (
				select T.c.query('.') as [VsElement]  
				from @valuesetXml.nodes('/*/smartText') T(c)
			) as X

	open csr

	fetch next from csr into @textID, @xml

	while @@fetch_status = 0
	begin
		exec [mig_director].[LanguageText_Insert] @textID, @xml, @runDate

		fetch next from csr into @textID, @xml
	end

	close csr
	deallocate csr
GO