if exists ( select * from sys.procedures p where p.name = 'sp_T_EHR_TYPE_MEDICAL_DEVICES_load' ) DROP PROCEDURE [dbo].[sp_T_EHR_TYPE_MEDICAL_DEVICES_load]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_T_EHR_TYPE_MEDICAL_DEVICES_load]
 @Xml xml
 ,@FileName varchar(50) = ''
AS 
BEGIN

	select 
		 T.c.value('./ORDER[1]', 'int') AS [ORDER]
		,ISNULL(T.c.value('./EXISTENCE_NPA[1]', 'bit'), 0) AS EXISTENCE_NPA
		,TRY_CAST(LTRIM(RTRIM(T.c.value('./RZN[1]', 'varchar(500)'))) AS int) AS RZN
		,T.c.value('./PARENT[1]', 'varchar(500)') AS PARENT
		,T.c.value('./ID[1]', 'int') AS ID
		,ISNULL(T.c.value('./ACTUAL[1]', 'bit'), 0) AS ACTUAL
		,T.c.value('./NAME[1]', 'varchar(700)') AS NAME
		,CAST(NULL AS uniqueidentifier) AS PARENT_EhrTypeMedicalDevicesID
	into #implant_load
	from @xml.nodes('/book/entries/entry') T(c)
		INSERT INTO [IES].T_EHR_TYPE_MEDICAL_DEVICES
           ([ORDER]
           ,[EXISTENCE_NPA]
           ,[RZN]
           ,[PARENT]
           ,[ID]
           ,[ACTUAL]
           ,[NAME]
           ,EhrTypeMedicalDevicesID)
	select t1.[ORDER]
           ,t1.[EXISTENCE_NPA]
           ,t1.[RZN]
	       ,NULL
           ,t1.[ID]
           ,t1.[ACTUAL]
           ,t1.[NAME]
           ,newid()
	from #implant_load t1
	where not exists (select 1 from [IES].T_EHR_TYPE_MEDICAL_DEVICES t2 where t2.ID = t1.ID)
	
	update t1
	set t1.PARENT_EhrTypeMedicalDevicesID = t_parent.EhrTypeMedicalDevicesID
	from #implant_load t1
	inner join [IES].T_EHR_TYPE_MEDICAL_DEVICES t_parent on (t_parent.ID = TRY_CAST(LTRIM(RTRIM(t1.PARENT)) AS int))
	where t1.PARENT IS NOT NULL AND LTRIM(RTRIM(t1.PARENT)) <> ''
	
	update t2 
	set  t2.[ORDER] = t1.[ORDER]
		,t2.EXISTENCE_NPA = t1.EXISTENCE_NPA
		,t2.RZN = t1.RZN
		,t2.PARENT = t1.PARENT_EhrTypeMedicalDevicesID
		,t2.ACTUAL = t1.ACTUAL
		,t2.NAME = t1.NAME
	from #implant_load t1
	join [IES].T_EHR_TYPE_MEDICAL_DEVICES t2 on (t1.ID=t2.ID)

	update t2
	set t2.PARENT = t1.PARENT_EhrTypeMedicalDevicesID
	from #implant_load t1
	join [IES].T_EHR_TYPE_MEDICAL_DEVICES t2 on (t1.ID=t2.ID)
	where t1.PARENT_EhrTypeMedicalDevicesID IS NOT NULL
		AND t2.PARENT IS NULL
	
	drop table #implant_load
END