IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_BarcodeTemplateLoad]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_BarcodeTemplateLoad]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[stor].[sp_BarcodeTemplateLoad]') AND type in (N'P', N'PC'))
DROP PROCEDURE stor.[sp_BarcodeTemplateLoad]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[pharm].[sp_BarcodeTemplateLoad]') AND type in (N'P', N'PC'))
DROP PROCEDURE pharm.[sp_BarcodeTemplateLoad]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE pharm.[sp_BarcodeTemplateLoad]
	@id uniqueidentifier = null,
	@Xml xml
AS
BEGIN
	IF (@Xml is null)
	BEGIN
	SELECT @Xml = (
			select
			 bt.BarcodeTemplateID													as 'BarcodeTemplateID'
			,bt.Entity																as 'Entity'
			,bt.BeginDate															as 'BeginDate'
			,bt.EndDate																as 'EndDate'
			,bt.[Version]															as 'Version'
			,bt.TemplateName														as 'TemplateName'
			,bt.DataFormat															as 'DataFormat'
			,(
			select
					bta.BarcodeTemplateAttributeID									as 'BarcodeTemplateAttributeID'
					,bta.[Order]													as 'Order'
					,bta.Size														as 'Size'
					,bta.[Description]												as 'Description'
					,bta.LexemPathLink												as 'LexemPathLink'
					,bta.ValueType													as 'ValueType'
					,bta.[Name]														as 'Name'
					,bta.BarcodeTemplate											as 'BarcodeTemplate'
					,bta.BarcodeTemplateAttribute									as 'BarcodeTemplateAttribute'
					,(
						select
							aba.AdditionalBarcodeAttributeID						as 'AdditionalBarcodeAttributeID'
							,aba.[Name]												as 'Name'
							,aba.Lexem												as 'Lexem'
							,aba.BarcodeTemplateAttribute							as 'BarcodeTemplateAttribute'
						from PHARM.T_ADDITIONAL_BARCODE_ATTRIBUTE aba
						where aba.BarcodeTemplateAttribute = bta.BarcodeTemplateAttributeID
						FOR XML PATH('AdditionalBarcodeTemplate'),
						TYPE
					)					
					FROM PHARM.T_BARCODE_TEMPLATE_ATTRIBUTE bta
					where bta.BarcodeTemplate = bt.BarcodeTemplateID
					FOR XML PATH('BarcodeTemplateAttribute'),
					TYPE
					)
			FROM PHARM.T_BARCODE_TEMPLATE bt
			where bt.BarcodeTemplateID = @id
			FOR XML PATH(''),
			ROOT('BarcodeTemplate'))
			END
	ELSE
	    begin
		-- Generate table #T_BARCODE_TEMPLATE
		select
				T.c.value('./BarcodeTemplateID[1]', 'uniqueidentifier')				as [BarcodeTemplateID]
				,T.c.value('./Entity[1]', 'int')									as [Entity]
				,T.c.value('./BeginDate[1]', 'datetime')							as [BeginDate]
				,T.c.value('./EndDate[1]', 'datetime')								as [EndDate]
				,T.c.value('./Version[1]', 'int')									as [Version]
				,T.c.value('./TemplateName[1]', 'varchar(255)')						as [TemplateName]
				,T.c.value('./DataFormat[1]', 'int')								as [DataFormat]
			INTO #BARCODE_TEMPLATE
		from @Xml.nodes('BarcodeTemplate') T(c)

		-- INSERT [PHARM].T_BARCODE_TEMPLATE
		INSERT INTO PHARM.T_BARCODE_TEMPLATE
		([BarcodeTemplateID],[Version], [Entity], [TemplateName], [DataFormat], [BeginDate], [EndDate])
		SELECT
					bt.[BarcodeTemplateID]
					,bt.[Version]
					,bt.[Entity]
					,bt.[TemplateName]
					,bt.[DataFormat]
					,bt.[BeginDate]
					,bt.[EndDate]
		from #BARCODE_TEMPLATE bt
		where not exists (
			select *
			from PHARM.T_BARCODE_TEMPLATE bt1
			where bt.BarcodeTemplateID = bt1.BarcodeTemplateID 
			)

		-- Generate table #BARCODE_TEMPLATE_ATTRIBUTE
		select
				T.c.value('./BarcodeTemplateAttributeID[1]', 'uniqueidentifier')	as [BarcodeTemplateAttributeID]
				,T.c.value('./Order[1]', 'int')										as [Order]
				,T.c.value('./Size[1]', 'int')										as [Size]
				,T.c.value('./Description[1]', 'varchar(255)')						as [Description]
				,T.c.value('./LexemPathLink[1]', 'varchar(max)')					as [LexemPathLink]
				,T.c.value('./ValueType[1]', 'int')									as [ValueType]
				,T.c.value('./Name[1]', 'varchar(255)')								as [Name]
				,T.c.value('./BarcodeTemplate[1]', 'uniqueidentifier')				as [BarcodeTemplate]
				,T.c.value('./BarcodeTemplateAttribute[1]', 'uniqueidentifier')		as [BarcodeTemplateAttribute]
			INTO #BARCODE_TEMPLATE_ATTRIBUTE
		from @Xml.nodes('BarcodeTemplate/BarcodeTemplateAttribute') T(c)

		-- INSERT [PHARM].T_BARCODE_TEMPLATE_ATTRIBUTE
		INSERT INTO [PHARM].T_BARCODE_TEMPLATE_ATTRIBUTE
		([BarcodeTemplateAttributeID], [Order], [Size], [Description], [LexemPathLink], [ValueType], [Name], [BarcodeTemplate])
				SELECT
					bta.[BarcodeTemplateAttributeID]
					,bta.[Order]
					,bta.[Size]
					,bta.[Description]
					,bta.[LexemPathLink]
					,bta.[ValueType]
					,bta.[Name]
					,bta.[BarcodeTemplate]
		from #BARCODE_TEMPLATE_ATTRIBUTE bta
		where not exists (
			select *
			from PHARM.T_BARCODE_TEMPLATE_ATTRIBUTE bta1
			where bta.BarcodeTemplateAttributeID = bta1.BarcodeTemplateAttributeID
		) and bta.BarcodeTemplateAttribute is null
		INSERT INTO [PHARM].T_BARCODE_TEMPLATE_ATTRIBUTE
		([BarcodeTemplateAttributeID], [Order], [Size], [Description], [LexemPathLink], [ValueType], [Name], [BarcodeTemplate], [BarcodeTemplateAttribute])
				SELECT
					bta.[BarcodeTemplateAttributeID]
					,bta.[Order]
					,bta.[Size]
					,bta.[Description]
					,bta.[LexemPathLink]
					,bta.[ValueType]
					,bta.[Name]
					,bta.[BarcodeTemplate]
					,bta.[BarcodeTemplateAttribute]
		from #BARCODE_TEMPLATE_ATTRIBUTE bta
		where not exists (
			select *
			from PHARM.T_BARCODE_TEMPLATE_ATTRIBUTE bta1
			where bta.BarcodeTemplateAttributeID = bta1.BarcodeTemplateAttributeID
		) and bta.BarcodeTemplateAttribute is not null

		-- Generate table #ADDITIONAL_BARCODE_ATTRIBUTE
		select
				T.c.value('./AdditionalBarcodeAttributeID[1]', 'uniqueidentifier')	as [AdditionalBarcodeAttributeID]
				,T.c.value('./Name[1]', 'varchar(255)')										as [Name]
				,T.c.value('./Lexem[1]', 'varchar(max)')							as [Lexem]
				,T.c.value('./BarcodeTemplateAttribute[1]', 'uniqueidentifier')		as [BarcodeTemplateAttribute]
			INTO #ADDITIONAL_BARCODE_ATTRIBUTE
		from @Xml.nodes('BarcodeTemplate/BarcodeTemplateAttribute/AdditionalBarcodeTemplate') T(c)

		-- INSERT [PHARM].T_ADDITIONAL_BARCODE_ATTRIBUTE
		INSERT INTO [PHARM].T_ADDITIONAL_BARCODE_ATTRIBUTE
		([AdditionalBarcodeAttributeID], [Name], [Lexem], [BarcodeTemplateAttribute])
				SELECT
					aba.[AdditionalBarcodeAttributeID]
					,aba.[Name]
					,aba.[Lexem]
					,aba.[BarcodeTemplateAttribute]
		from #ADDITIONAL_BARCODE_ATTRIBUTE aba
		where not exists (
			select *
			from PHARM.T_ADDITIONAL_BARCODE_ATTRIBUTE aba1
			where aba.AdditionalBarcodeAttributeID = aba1.AdditionalBarcodeAttributeID
		)

		-- Drop table
		DROP TABLE #BARCODE_TEMPLATE
		DROP TABLE #BARCODE_TEMPLATE_ATTRIBUTE
		DROP TABLE #ADDITIONAL_BARCODE_ATTRIBUTE
	END
END