IF EXISTS (
		SELECT *
		FROM sys.procedures p
		WHERE p.name = 'SP_FMOSchetLoad'
		)
	DROP PROCEDURE [dbo].SP_FMOSchetLoad
GO

CREATE PROCEDURE [dbo].SP_FMOSchetLoad @SchetID UNIQUEIDENTIFIER
	,@xml XML
	,@type INT
	,@Worker UNIQUEIDENTIFIER
AS
BEGIN
	/*
	Особенность загрузки ФРМО:
	- она работает только с флагами IsAdditional, MultiFile, IgnoreFileCount
	- доступна только простая загрузка
	- создаётся SchetLoadHistory, но она не связывается с Schet (т.к. в одном zip-пакете несколько счетов нескольких МО)
	- после запуска блока диаграммы Загрузка счёта, остальные блоки не выполняются (совсем)
	*/
	SET NOCOUNT ON;

	PRINT 'DECLARE VARS' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	DECLARE @RaisErrorMsg VARCHAR(2047)
	DECLARE @FileName VARCHAR(300)
	DECLARE @FileNameTag VARCHAR(100)

	SET @SchetID = NEWID()

	PRINT 'PARSING block' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	BEGIN
		PRINT 'PARSING SCHET' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT T.c.value('./ZGLV[1]/VERSION[1]', 'varchar(5)') AS [VERSION]
			,convert(DATETIME, T.c.value('./ZGLV[1]/DATA[1]', 'varchar(19)'), 20) AS [DATA] --todo
			,T.c.value('./ZGLV[1]/FILENAME[1]', 'varchar(36)') AS [FILENAME]
			,T.c.value('./ZGLV[1]/SD_Z[1]', 'int') AS [SD_Z]
			,T.c.value('./OTPR[1]/CODE_MO[1]', 'varchar(8)') AS [CODE_MO]
			,(
				SELECT TOP 1 f003.MCOD
				FROM ies.T_F003_MO f003
				JOIN ies.T_F032_TRMO f032 ON f003.MCOD = f032.MCOD
				WHERE substring(f032.IDMO, 1, 8) = T.c.value('./OTPR[1]/CODE_MO[1]', 'varchar(8)')
				) AS [CODE_MO_F003]
			,T.c.value('./OTPR[1]/YEAR[1]', 'int') AS [YEAR]
			,T.c.value('./OTPR[1]/MONTH[1]', 'int') AS [MONTH]
			,T.c.value('./OTPR[1]/DAY[1]', 'int') AS [DAY]
			,T.c.value('./SCHET[1]/COMENTS[1]', 'varchar(250)') AS [COMENTS]
		INTO #TEMP_SCHET
		FROM @xml.nodes('/ZL_LIST') T(c)

		SELECT @FileNameTag = [Filename]
		FROM #TEMP_SCHET

		PRINT 'GET FMO_SCHET ENTITYID' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SET @type = (
				SELECT TOP 1 entityid
				FROM vclib.T_ENTITIES
				WHERE EntName = 'FMO_SCHET'
				)

		PRINT 'DELETE OLD SCHET' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		DECLARE @OldSchetId UNIQUEIDENTIFIER

		DECLARE cursor1 CURSOR
		FOR
		SELECT schetid
		FROM ies.t_schet t0
		WHERE isnull(t0.filename, '') = @FileNameTag
			AND type_ = @type
			AND IsDelete = 0

		OPEN cursor1

		FETCH NEXT
		FROM cursor1
		INTO @OldSchetId

		WHILE @@FETCH_STATUS = 0
		BEGIN
			--your code here
			EXEC sp_DropSchet @OldSchetId
				,@type
				,1

			--your code here
			FETCH NEXT
			FROM cursor1
			INTO @OldSchetId
		END

		CLOSE cursor1;

		DEALLOCATE cursor1;

		PRINT 'RESUME PARSING' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT NEWID() AS [SchetZapID]
			,T.c.value('./N_ZAP[1]', 'int') AS [N_ZAP]
			,T.c.value('./TYPE[1]', 'varchar(5)') AS FMO_TYPE
			,T.c.value('./PACIENT[1]/VPOLIS[1]', 'int') AS [VPOLIS]
			,T.c.value('./PACIENT[1]/ENP[1]', 'varchar(16)') AS [ENP]
			,T.c.value('./PACIENT[1]/SNPOLIS[1]', 'varchar(39)') AS [SNPOLIS]
			,T.c.value('./PACIENT[1]/SMO_OK[1]', 'varchar(5)') AS [SMO_OK]
			,T.c.value('./PACIENT[1]/SMO[1]', 'varchar(5)') AS [SMO]
			,T.c.value('./PACIENT[1]/W[1]', 'int') AS [W]
			,T.c.value('./PACIENT[1]/DR[1]', 'date') AS [DR]
			,T.c.value('./PACIENT[1]/NOVOR[1]', 'integer') AS [NOVOR]
			,T.c.value('./PACIENT[1]/NUM_CHILD[1]', 'integer') AS [NUM_CHILD]
			,nullif(T.c.value('./PACIENT[1]/FAM[1]', 'varchar(40)'), '') AS [FAM]
			,nullif(T.c.value('./PACIENT[1]/IM[1]', 'varchar(40)'), '') AS [IM]
			,nullif(T.c.value('./PACIENT[1]/OT[1]', 'varchar(40)'), '') AS [OT]
			,nullif(T.c.value('./PACIENT[1]/TEL[1]', 'varchar(100)'), '') AS [TEL]
			,nullif(T.c.value('./PACIENT[1]/MR[1]', 'varchar(100)'), '') AS [MR]
			,nullif(T.c.value('./PACIENT[1]/DOCTYPE[1]', 'int'), 0) AS [DOCTYPE]
			,nullif(T.c.value('./PACIENT[1]/DOCSER[1]', 'varchar(10)'), '') AS [DOCSER]
			,nullif(T.c.value('./PACIENT[1]/DOCNUM[1]', 'varchar(20)'), '') AS [DOCNUM]
			,nullif(T.c.value('./PACIENT[1]/DOCDATE[1]', 'date'), convert(DATETIME, '01.01.1900', 104)) AS DOCDATE
			,nullif(T.c.value('./PACIENT[1]/DOCORG[1]', 'varchar(1000)'), '') AS [DOCORG]
			,nullif(T.c.value('./PACIENT[1]/SNILS[1]', 'varchar(14)'), '') AS [SNILS]
			,nullif(T.c.value('./PACIENT[1]/OKATOG[1]', 'varchar(11)'), '') AS [OKATOG]
			,nullif(T.c.value('./PACIENT[1]/OKATOP[1]', 'varchar(11)'), '') AS [OKATOP]
			,nullif(T.c.value('./PACIENT[1]/COMENTP[1]', 'varchar(250)'), '') AS [COMENTP]
			,nullif(T.c.value('./PACIENT[1]/FAM_P[1]', 'varchar(40)'), '') AS [FAM_P]
			,nullif(T.c.value('./PACIENT[1]/IM_P[1]', 'varchar(40)'), '') AS [IM_P]
			,nullif(T.c.value('./PACIENT[1]/OT_P[1]', 'varchar(40)'), '') AS [OT_P]
			,nullif(T.c.value('./PACIENT[1]/W_P[1]', 'int'), '') AS [W_P]
			,nullif(T.c.value('./PACIENT[1]/DR_P[1]', 'date'), convert(DATETIME, '01.01.1900', 104)) AS [DR_P]
		INTO #TEMP_ZAP
		FROM @xml.nodes('/ZL_LIST/ZAP') T(c)

		SELECT T.c.value('../../N_ZAP[1]', 'int') AS [N_ZAP]
			,T.c.value('.', 'varchar(1)') AS [DOST]
		INTO #TEMP_DOST
		FROM @xml.nodes('/ZL_LIST/ZAP/PACIENT/DOST') T(c)

		SELECT T.c.value('../../N_ZAP[1]', 'int') AS [N_ZAP]
			,T.c.value('.', 'varchar(1)') AS [DOST_P]
		INTO #TEMP_DOST_P
		FROM @xml.nodes('/ZL_LIST/ZAP/PACIENT/DOST_P') T(c)

		PRINT 'PARSING NPR' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT NEWID() AS [NprID]
			,T.c.value('.././N_ZAP[1]', 'int') AS [N_ZAP]
			,T.c.value('./IDNPR[1]', 'varchar(36)') AS [IDNPR]
			,T.c.value('./IDNPR_MIS[1]', 'varchar(36)') AS [IDNPR_MIS]
			,CASE 
				WHEN LEN(T.c.value('./TAL_D[1]', 'varchar(10)')) = 10
					THEN T.c.value('./TAL_D[1]', 'datetime')
				ELSE NULL
				END AS TAL_D
			,T.c.value('./TAL_NUM[1]', 'varchar(20)') AS [TAL_NUM]
			,T.c.value('./NPR_MO[1]', 'varchar(8)') AS [NPR_MO]
			,(
				SELECT TOP 1 f003.MCOD
				FROM ies.T_F003_MO f003
				JOIN ies.T_F032_TRMO f032 ON f003.MCOD = f032.MCOD
				WHERE substring(f032.IDMO, 1, 8) = T.c.value('./NPR_MO[1]', 'varchar(8)')
				) AS [NPR_MO_F003]
			,nullif(T.c.value('./POVOD[1]', 'int'), 0) AS [POVOD]
			,T.c.value('./DS1[1]', 'varchar(10)') AS [DS1]
			,T.c.value('./USL_OK[1]', 'integer') AS [USL_OK]
			,T.c.value('./VIDPOM[1]', 'integer') AS [VIDPOM]
			,T.c.value('./FOR_POM[1]', 'integer') AS [FOR_POM]
			,T.c.value('./PROFIL[1]', 'integer') AS [PROFIL]
			,T.c.value('./GR_HMP[1]', 'integer') AS [GR_HMP]
			,T.c.value('./VID_HMP[1]', 'varchar(12)') AS [VID_HMP]
			,T.c.value('./RESH_HMP[1]', 'integer') AS [RESH_HMP]
			,T.c.value('./RESH[1]', 'integer') AS [RESH]
			,T.c.value('./PODR[1]', 'varchar(12)') AS [PODR]
			,T.c.value('./PODR_NAME[1]', 'varchar(100)') AS [PODR_NAME]
			,T.c.value('./PROFIL_K[1]', 'integer') AS [PROFIL_K]
			,T.c.value('./TAL_P[1]', 'date') AS [TAL_P] --todo
		INTO #TEMP_NPR
		FROM @xml.nodes('/ZL_LIST/ZAP/NPR') T(c)

		PRINT 'PARSING NPR DS' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT T.c.value('../../N_ZAP[1]', 'int') AS N_ZAP
			,T.c.value('../IDNPR[1]', 'varchar(36)') AS IDNPR
			,T.c.value('.', 'varchar(10)') AS DS
			,0 AS DsType
		INTO #TEMP_NPR_DS
		FROM @xml.nodes('/ZL_LIST/ZAP/NPR/DS2') T(c)

		INSERT INTO #TEMP_NPR_DS
		SELECT T.c.value('../../N_ZAP[1]', 'int') AS N_ZAP
			,T.c.value('../IDNPR[1]', 'varchar(36)') AS IDNPR
			,T.c.value('.', 'varchar(10)') AS DS
			,1 AS DsType
		FROM @xml.nodes('/ZL_LIST/ZAP/NPR/DS3') T(c)

		PRINT 'PARSING Z_SL' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT NEWID() AS [SchetSluchAccomplishedID]
			,T.c.value('../N_ZAP[1]', 'int') AS N_ZAP
			,T.c.value('./IDCASE[1]', 'varchar(36)') AS [IDCASE]
			,T.c.value('./IDCASE_LINK[1]', 'varchar(36)') AS [IDCASE_LINK]
			,T.c.value('./OSN_LINK[1]', 'varchar(3)') AS [OSN_LINK]
			,T.c.value('./IDCASE_MIS[1]', 'varchar(36)') AS [IDCASE_MIS]
			,T.c.value('./ADR_GAR[1]', 'varchar(36)') AS [ADR_GAR]
			,T.c.value('./ADR_NAME[1]', 'varchar(300)') AS [ADR_NAME]
			,T.c.value('./USL_OK[1]', 'integer') AS [USL_OK]
			,T.c.value('./VIDPOM[1]', 'integer') AS [VIDPOM]
			,T.c.value('./FOR_POM[1]', 'integer') AS [FOR_POM]
			,T.c.value('./DATE_Z_1[1]', 'date') AS [DATE_Z_1]
			,T.c.value('./DATE_Z_2[1]', 'date') AS [DATE_Z_2]
			,T.c.value('./KD_Z[1]', 'integer') AS [KD_Z]
			,iif(T.c.value('./WEI[1]', 'varchar(5)') = '', NULL, convert(DECIMAL(4, 1), T.c.value('./WEI[1]', 'varchar(5)'))) AS [WEI]
			,T.c.value('./RSLT[1]', 'integer') AS [RSLT]
			,T.c.value('./ISHOD[1]', 'integer') AS [ISHOD]
			,T.c.value('./IS_PRERV[1]', 'integer') AS [IS_PRERV]
			,T.c.value('./KOEF_PRERV[1]', 'decimal(7,5)') AS [KOEF_PRERV]
			,T.c.value('./KOEF_PRIV[1]', 'decimal(7,5)') AS [KOEF_PRIV]
			,iif(T.c.value('./KOEF_SPEC[1]', 'varchar(7)') = '', NULL, convert(DECIMAL(7, 2), T.c.value('./KOEF_SPEC[1]', 'varchar(7)'))) AS [KOEF_SPEC]
			,T.c.value('./SRED_NFZ[1]', 'decimal(7,2)') AS [SRED_NFZ]
			,T.c.value('./KOEF_D[1]', 'decimal(7,5)') AS [KOEF_D]
			,T.c.value('./SUMV[1]', 'decimal(17,2)') AS [SUMV]
		INTO #TEMP_Z_SLUCH
		FROM @xml.nodes('/ZL_LIST/ZAP/Z_SL') T(c)

		PRINT 'PARSING SL' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT NEWID() AS [SchetSluchID]
			,T.c.value('../.././N_ZAP[1]', 'int') AS [N_ZAP]
			,T.c.value('../IDCASE[1]', 'varchar(36)') AS [IDCASE]
			,T.c.value('./SL_ID[1]', 'varchar(36)') AS [SL_ID]
			,T.c.value('./PODR[1]', 'varchar(12)') AS [PODR]
			,T.c.value('./PODR_NAME[1]', 'varchar(300)') AS [PODR_NAME]
			,T.c.value('./OID_FRMO[1]', 'varchar(100)') AS [OID_FRMO]
			,T.c.value('./PROFIL[1]', 'integer') AS [PROFIL]
			,T.c.value('./PROFIL_K[1]', 'integer') AS [PROFIL_K]
			,T.c.value('./NHISTORY[1]', 'varchar(50)') AS [NHISTORY]
			,T.c.value('./DS_GR[1]', 'varchar(36)') AS [DS_GR]
			,T.c.value('./DS1[1]', 'varchar(10)') AS [DS1]
			,T.c.value('./ILLACT[1]', 'int') AS [ILLACT]
			,T.c.value('./ILLREASON[1]', 'int') AS [ILLREASON]
			,T.c.value('./C_ZAB[1]', 'integer') AS [C_ZAB]
			,T.c.value('./GR_HMP[1]', 'integer') AS [GR_HMP]
			,T.c.value('./VID_HMP[1]', 'varchar(12)') AS [VID_HMP]
			,T.c.value('./METOD_HMP[1]', 'integer') AS [METOD_HMP]
			,T.c.value('./MODEL_HMP[1]', 'integer') AS [MODEL_HMP]
			,T.c.value('./LECH_HMP[1]', 'integer') AS [LECH_HMP]
			,T.c.value('./DZP[1]', 'decimal(7,5)') AS [DZP]
			,T.c.value('./NFZ[1]', 'decimal(15,2)') AS [NFZ]
			,T.c.value('./TARIF[1]', 'decimal(15,2)') AS [TARIF]
			,T.c.value('./COMENTSL[1]', 'varchar(250)') AS [COMENTSL]
		INTO #TEMP_SLUCH
		FROM @xml.nodes('/ZL_LIST/ZAP/Z_SL/SL') T(c)

		PRINT 'PARSING USL' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT newid() AS [SchetUslID]
			,T.c.value('../../.././N_ZAP[1]', 'int') AS N_ZAP
			,T.c.value('../../IDCASE[1]', 'varchar(36)') AS [IDCASE]
			,T.c.value('../SL_ID[1]', 'varchar(36)') AS [SL_ID]
			,T.c.value('./IDSERV[1]', 'varchar(36)') AS [IDSERV]
			,T.c.value('./VID_VME[1]', 'varchar(15)') AS [VID_VME]
			,T.c.value('./COMENTU[1]', 'varchar(250)') AS [COMENTU]
		INTO #TEMP_USL
		FROM @xml.nodes('/ZL_LIST/ZAP/Z_SL/SL/USL') T(c)

		PRINT 'PARSING KSG_KPG' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT NEWID() AS [KsgID]
			,T.c.value('../../IDCASE[1]', 'varchar(36)') AS [IDCASE]
			,T.c.value('../SL_ID[1]', 'varchar(36)') AS [SL_ID]
			,T.c.value('./N_KSG[1]', 'varchar(20)') AS [N_KSG]
			,T.c.value('./GR[1]', 'varchar(10)') AS [GR]
			,T.c.value('./VER_KSG[1]', 'integer') AS [VER_KSG]
			,T.c.value('./KOEF_Z[1]', 'decimal(7,5)') AS [KOEF_Z]
			,T.c.value('./CRIT[1]', 'varchar(10)') AS [CRIT]
			,T.c.value('./SL_K[1]', 'integer') AS [SL_K]
			,T.c.value('./IT_SL[1]', 'decimal(6,5)') AS [IT_SL]
			,isnull(T.c.value('./NOT_ONK_SL[1]', 'bit'), 0) AS [NOT_ONK_SL]
		INTO #TEMP_KSG
		FROM @xml.nodes('/ZL_LIST/ZAP/Z_SL/SL/KSG_KPG') T(c)

		PRINT 'PARSING CRIT' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT T.c.value('../../../IDCASE[1]', 'varchar(36)') AS [IDCASE]
			,T.c.value('../../SL_ID[1]', 'varchar(36)') AS [SL_ID]
			,T.c.value('.', 'varchar(10)') AS [CRIT]
		INTO #TEMP_CRIT
		FROM @xml.nodes('/ZL_LIST/ZAP/Z_SL/SL/KSG_KPG/CRIT') T(c)

		PRINT 'PARSING SL_KOEF' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT NEWID() AS [KslpID]
			,T.c.value('../../../IDCASE[1]', 'varchar(36)') AS [IDCASE]
			,T.c.value('../../SL_ID[1]', 'varchar(36)') AS [SL_ID]
			,T.c.value('../N_KSG[1]', 'varchar(20)') AS [N_KSG]
			,T.c.value('./IDSL[1]', 'int') AS [IDSL]
			,T.c.value('./Z_SL[1]', 'decimal(6,5)') AS [Z_SL]
		INTO #TEMP_SL_KOEF
		FROM @xml.nodes('/ZL_LIST/ZAP/Z_SL/SL/KSG_KPG/SL_KOEF') T(c)

		PRINT 'PARSING SL/DS2' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT T.c.value('../../IDCASE[1]', 'varchar(36)') AS [IDCASE]
			,T.c.value('../SL_ID[1]', 'varchar(36)') AS [SL_ID]
			,T.c.value('.', 'varchar(10)') AS [DS]
			,cast(NULL AS INT) AS [DS2_PR]
			,cast(NULL AS INT) AS [PR_DS2_N]
			,0 AS DS_TYPE
		INTO #TEMP_DS
		FROM @xml.nodes('/ZL_LIST/ZAP/Z_SL/SL/DS2') T(c)

		PRINT 'PARSING SL/DS3' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO #TEMP_DS
		SELECT T.c.value('../../IDCASE[1]', 'varchar(36)') AS [IDCASE]
			,T.c.value('../SL_ID[1]', 'varchar(36)') AS [SL_ID]
			,T.c.value('.', 'varchar(10)') AS [DS]
			,cast(NULL AS INT) AS [DS2_PR]
			,T.c.value('./PR_DS2_N[1]', 'int') AS [PR_DS2_N]
			,1 AS DS_TYPE
		FROM @xml.nodes('/ZL_LIST/ZAP/Z_SL/SL/DS3') T(c)

		PRINT 'PARSING CONS' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT T.c.value('../../IDCASE[1]', 'varchar(36)') AS [IDCASE]
			,T.c.value('../SL_ID[1]', 'varchar(36)') AS [SL_ID]
			,T.c.value('./PR_CONS[1]', 'int') AS [PR_CONS]
			,T.c.value('./DT_CONS[1]', 'datetime') AS [DT_CONS]
		INTO #TEMP_CONS
		FROM @xml.nodes('/ZL_LIST/ZAP/Z_SL/SL/CONS') T(c)

		PRINT 'PARSING ONK_SL' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT NEWID() AS [SchetSluchOnkID]
			,T.c.value('../../IDCASE[1]', 'varchar(36)') AS [IDCASE]
			,T.c.value('../SL_ID[1]', 'varchar(36)') AS [SL_ID]
			,T.c.value('./DS1_T[1]', 'int') AS [DS1_T]
			,T.c.value('./STAD[1]', 'int') AS [STAD]
			,T.c.value('./ONK_T[1]', 'int') AS [ONK_T]
			,T.c.value('./ONK_N[1]', 'int') AS [ONK_N]
			,T.c.value('./ONK_M[1]', 'int') AS [ONK_M]
			,T.c.value('./MTSTZ[1]', 'int') AS [MTSTZ]
			,T.c.value('./SOD[1]', 'decimal (6,2)') AS [SOD]
			,T.c.value('./K_FR[1]', 'int') AS [K_FR]
			,T.c.value('./WEI[1]', 'decimal (4,1)') AS [WEI]
			,T.c.value('./HEI[1]', 'int') AS [HEI]
			,T.c.value('./BSA[1]', 'decimal (3,2)') AS [BSA]
		INTO #TEMP_ONK_SL
		FROM @xml.nodes('/ZL_LIST/ZAP/Z_SL/SL/ONK_SL') T(c)

		PRINT 'PARSING B_DIAG' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT NEWID() AS [SchetSluchOnkDiagID]
			,T.c.value('../../../IDCASE[1]', 'varchar(36)') AS [IDCASE]
			,T.c.value('../../SL_ID[1]', 'varchar(36)') AS [SL_ID]
			,T.c.value('./DIAG_DATE[1]', 'datetime') AS [DIAG_DATE]
			,T.c.value('./DIAG_TIP[1]', 'int') AS [DIAG_TIP]
			,T.c.value('./DIAG_CODE[1]', 'int') AS [DIAG_CODE]
			,T.c.value('./DIAG_RSLT[1]', 'int') AS [DIAG_RSLT]
			,T.c.value('./REC_RSLT[1]', 'int') AS [REC_RSLT]
		INTO #TEMP_B_DIAG
		FROM @xml.nodes('/ZL_LIST/ZAP/Z_SL/SL/ONK_SL/B_DIAG') T(c)

		PRINT 'PARSING B_PROT' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT NEWID() AS [SchetSluchOnkProtID]
			,T.c.value('../../../IDCASE[1]', 'varchar(36)') AS [IDCASE]
			,T.c.value('../../SL_ID[1]', 'varchar(36)') AS [SL_ID]
			,nullif(T.c.value('./PROT[1]', 'varchar(1)'), '') AS [PROT]
			,CASE 
				WHEN LEN(T.c.value('./D_PROT[1]', 'varchar(10)')) > 8
					THEN T.c.value('./D_PROT[1]', 'datetime')
				ELSE NULL
				END AS [D_PROT]
		INTO #TEMP_B_PROT
		FROM @xml.nodes('/ZL_LIST/ZAP/Z_SL/SL/ONK_SL/B_PROT') T(c)

		PRINT 'PARSING /ZL_LIST/ZAP/Z_SL/SL/ONK_SL/ONK_USL' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT NEWID() AS [SchetUslOnkID]
			,T.c.value('../../../.././N_ZAP[1]', 'int') AS [N_ZAP]
			,T.c.value('../../../IDCASE[1]', 'varchar(36)') AS [IDCASE]
			,T.c.value('../../SL_ID[1]', 'varchar(36)') AS [SL_ID]
			,T.c.value('../DS1_T[1]', 'int') AS [DS1_T]
			,T.c.value('./USL_TIP[1]', 'int') AS [USL_TIP]
			,T.c.value('./HIR_TIP[1]', 'int') AS [HIR_TIP]
			,T.c.value('./LEK_TIP_L[1]', 'int') AS [LEK_TIP_L]
			,T.c.value('./LEK_TIP_V[1]', 'int') AS [LEK_TIP_V]
			,T.c.value('./LUCH_TIP[1]', 'int') AS [LUCH_TIP]
			,iif(T.c.value('./PPTR[1]', 'varchar(1)') IS NULL, 0, 1) AS [PPTR]
			,T.c.query('./LEK_PR') AS [LEK_PR_XML_NODE]
		INTO #TEMP_ONK_USL
		FROM @xml.nodes('/ZL_LIST/ZAP/Z_SL/SL/ONK_SL/ONK_USL') T(c)

		PRINT 'PARSING /ZL_LIST/ZAP/Z_SL/SL/ONK_SL/ONK_USL/LEK_PR' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT newid() AS [LekPrID]
			,t2.loc.value('./REGNUM[1]', 'varchar(40)') AS [REGNUM]
			,t2.loc.value('./CODE_SH[1]', 'varchar(10)') AS [CODE_SH]
			,CASE 
				WHEN LEN(t2.loc.value('./DATE_INJ[1]', 'varchar(20)')) > 8
					THEN t2.loc.value('./DATE_INJ[1]', 'date')
				ELSE NULL
				END AS [DATE_INJ]
			,t2.loc.value('./DOSE_INJ[1]', 'varchar(20)') AS [DOSE_INJ]
			,[SchetUslOnkID]
		INTO #TEMP_LEK_PR
		FROM #TEMP_ONK_USL OU
		CROSS APPLY [LEK_PR_XML_NODE].nodes('/LEK_PR') AS T2(Loc)

		PRINT 'PARSING /ZL_LIST/ZAP/Z_SL/SL/LEK_PR' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		SELECT newid() AS [SchetSluchLekPrID]
			,T.c.value('../../IDCASE[1]', 'varchar(36)') AS [IDCASE]
			,T.c.value('../SL_ID[1]', 'varchar(36)') AS [SL_ID]
			,CASE 
				WHEN LEN(T.c.value('./DATA_INJ[1]', 'varchar(20)')) > 8
					THEN T.c.value('./DATA_INJ[1]', 'datetime')
				ELSE NULL
				END AS [DATA_INJ]
			,T.c.value('./CODE_SH[1]', 'varchar(10)') AS [CODE_SH]
			,T.c.value('./REGNUM[1]', 'varchar(6)') AS [REGNUM]
			,T.c.value('./COD_MARK[1]', 'varchar(100)') AS [COD_MARK]
			,T.c.value('./LEK_DOSE[1]/ED_IZM[1]', 'varchar(3)') AS [ED_IZM]
			,T.c.value('./DOSE_INJ[1]', 'decimal(7,2)') AS [DOSE_INJ]
			,T.c.value('./LEK_DOSE[1]/METHOD_INJ[1]', 'varchar(3)') AS [METHOD_INJ]
			,T.c.value('./LEK_DOSE[1]/COL_INJ[1]', 'int') AS [COL_INJ]
		INTO #TEMP_SCHET_SLUCH_LEK_PR
		FROM @xml.nodes('/ZL_LIST/ZAP/Z_SL/SL/LEK_PR') T(c)

		SELECT newid() AS [SchetUslMedDevID]
			,T.c.value('../../../IDCASE[1]', 'varchar(36)') AS [IDCASE]
			,T.c.value('../../SL_ID[1]', 'varchar(36)') AS [SL_ID]
			,T.c.value('../IDSERV[1]', 'varchar(36)') AS [IDSERV]
			,CASE 
				WHEN LEN(T.c.value('./DATE_MED[1]', 'varchar(20)')) > 8
					THEN T.c.value('./DATE_MED[1]', 'date')
				ELSE NULL
				END AS [DATE_MED]
			,T.c.value('./CODE_MEDDEV[1]', 'varchar(6)') AS [CODE_MEDDEV]
			,T.c.value('./NUMBER_SER[1]', 'varchar(25)') AS [NUMBER_SER]
		INTO #SCHET_USL_MED_DEV
		FROM @xml.nodes('/ZL_LIST/ZAP/Z_SL/SL/USL/MED_DEV') T(c)
	END

	PRINT 'СОБИРАЕМ DOST и DOST_P в одну строку' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	BEGIN
		SELECT t0.N_ZAP
			,Left(t0.rezult, Len(t0.rezult) - 1) AS DOST_AS_STRING
		INTO #dost
		FROM (
			SELECT DISTINCT N_ZAP AS N_ZAP
				,(
					SELECT cast(t1.DOST AS VARCHAR(1)) + ',' AS [text()]
					FROM #TEMP_DOST t1
					WHERE t1.N_ZAP = ST2.N_ZAP
					ORDER BY t1.N_ZAP
					FOR XML PATH('')
					) rezult
			FROM #TEMP_DOST ST2
			) t0

		SELECT t0.N_ZAP
			,Left(t0.rezult, Len(t0.rezult) - 1) AS DOST_P_AS_STRING
		INTO #DOST_P
		FROM (
			SELECT DISTINCT N_ZAP AS N_ZAP
				,(
					SELECT cast(t1.DOST_P AS VARCHAR(1)) + ',' AS [text()]
					FROM #TEMP_DOST_P t1
					WHERE t1.N_ZAP = ST2.N_ZAP
					ORDER BY t1.N_ZAP
					FOR XML PATH('')
					) rezult
			FROM #TEMP_DOST_P ST2
			) t0
	END

	PRINT 'CREATE FLK TABLE #ERRORS' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	CREATE TABLE #Errors (
		IM_POL VARCHAR(10)
		,ZN_POL VARCHAR(1000)
		,NSCHET VARCHAR(15)
		,BAS_EL VARCHAR(10)
		,N_ZAP INT
		,ID_PAC VARCHAR(36)
		,IDCASE VARCHAR(36)
		,SL_ID VARCHAR(36)
		,IDSERV VARCHAR(36)
		,OSHIB VARCHAR(20)
		,COMMENT VARCHAR(250)
		)

	PRINT 'q015 block' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	BEGIN
		--q015 begin
		--q015 preamb begin
		--q015 rules end
		--q015 end
		--custom rules begin
		INSERT INTO #Errors (
			IM_POL
			,ZN_POL
			,NSCHET
			,BAS_EL
			,N_ZAP
			,ID_PAC
			,IDCASE
			,SL_ID
			,IDSERV
			,OSHIB
			,COMMENT
			)
		SELECT 'CODE_MO'
			,t0.CODE_MO AS ZN_POL
			,NULL AS NSCHET
			,'OTPR'
			,NULL AS N_ZAP
			,NULL AS ID_PAC
			,NULL AS IDCASE
			,NULL AS SL_ID
			,NULL AS IDSERV
			,'902'
			,'Значение не найдено в справочнике F003'
		FROM #TEMP_SCHET t0
		WHERE t0.CODE_MO_F003 IS NULL

		INSERT INTO #Errors (
			IM_POL
			,ZN_POL
			,NSCHET
			,BAS_EL
			,N_ZAP
			,ID_PAC
			,IDCASE
			,SL_ID
			,IDSERV
			,OSHIB
			,COMMENT
			)
		SELECT 'NPR_MO'
			,t0.NPR_MO AS ZN_POL
			,NULL AS NSCHET
			,'NPR'
			,t0.N_ZAP AS N_ZAP
			,NULL AS ID_PAC
			,NULL AS IDCASE
			,NULL AS SL_ID
			,NULL AS IDSERV
			,'902'
			,'Значение не найдено в справочнике F003'
		FROM #TEMP_NPR t0
		WHERE t0.NPR_MO_F003 IS NULL
			--custom rules end
	END

	PRINT 'CHECK NO FLK' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	IF (
			SELECT COUNT(*)
			FROM #Errors
			) > 0
	BEGIN
		SELECT '<?xml version="1.0" encoding="Windows-1251"?>
' + (
				SELECT OSHIB AS 'OSHIB'
					,IM_POL AS 'IM_POL'
					,ZN_POL AS 'ZN_POL'
					,BAS_EL AS 'BAS_EL'
					,N_ZAP AS 'N_ZAP'
					,ID_PAC AS 'ID_PAC'
					,IDCASE AS 'IDCASE'
					,SL_ID AS 'SL_ID'
					,IDSERV AS 'IDSERV'
					,COMMENT AS 'COMMENT'
				FROM #Errors
				FOR XML PATH('PR')
					,ROOT('FLK_P')
				)
	END
	ELSE
	BEGIN
		PRINT 'INSERT BLOCK' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)
		PRINT 'INSERT SCHET' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO [IES].[T_SCHET] (
			[VERSION]
			,[DATA]
			,[DATA_TIME]
			,[FILENAME]
			,SD_Z
			,[CODE_MO]
			,[YEAR]
			,[MONTH]
			,[DAY]
			,[COMENTS]
			,[ReceivedDate]
			,[ReceivedTime]
			,[SchetID]
			,[type_]
			,[Status]
			,CODE
			,NSCHET
			,DSCHET
			,SchetKind
			,SUMMAV
			)
		SELECT [VERSION]
			,[DATA]
			,[DATA]
			,[FILENAME]
			,SD_Z
			,CODE_MO_F003
			,[YEAR]
			,[MONTH]
			,[DAY]
			,[COMENTS]
			,convert(DATE, getdate()) AS [ReceivedDate]
			,getdate() AS [ReceivedTime]
			,@SchetID AS SchetId
			,@type
			,0 AS [STATUS]
			,1 AS CODE
			,'не указан' AS NSCHET
			,getdate() AS DSCHET
			,0 AS SchetKind
			,isnull((
					SELECT sum(sumv)
					FROM #TEMP_Z_SLUCH
					), 0)
		FROM #TEMP_SCHET tsch

		-----------------T_SCHET_ZAP (PACIENT)-----------------------------
		CREATE NONCLUSTERED INDEX [IX_TEMP_ZAP_N_ZAP] ON #TEMP_ZAP ([N_ZAP] 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]

		PRINT 'INSERT SCHET_ZAP' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO [IES].[T_SCHET_ZAP] (
			[SchetZapID]
			,[Schet]
			,[N_ZAP]
			,FMO_TYPE
			,VPOLIS
			,[ENP]
			,[SNPOLIS]
			,[SMO_OK]
			,[SMO]
			,[W]
			,[DR]
			,[NOVOR]
			,NUM_CHILD
			,[FAM]
			,[IM]
			,[OT]
			,DOST
			,[TEL]
			,[MR]
			,[DOCTYPE]
			,[DOCSER]
			,[DOCNUM]
			,[DOCDATE]
			,[DOCORG]
			,[SNILS]
			,[OKATOG]
			,[OKATOP]
			,[COMENTP]
			,[FAM_P]
			,[IM_P]
			,[OT_P]
			,W_P
			,[DR_P]
			,DOST_P
			,type_
			)
		SELECT [SchetZapID]
			,@SchetID AS Schet
			,t0.[N_ZAP]
			,FMO_TYPE
			,VPOLIS
			,[ENP]
			,[SNPOLIS]
			,[SMO_OK]
			,(
				SELECT TOP 1 mo.SMOCOD
				FROM IES.T_F002_SMO mo
				WHERE mo.SMOCOD = [SMO]
					AND isnull(SMO, '') != ''
				) AS [SMO]
			,[W]
			,[DR]
			,[NOVOR]
			,NUM_CHILD
			,[FAM]
			,[IM]
			,[OT]
			,DOST.DOST_AS_STRING
			,[TEL]
			,[MR]
			,[DOCTYPE]
			,[DOCSER]
			,[DOCNUM]
			,[DOCDATE]
			,[DOCORG]
			,[SNILS]
			,[OKATOG]
			,[OKATOP]
			,[COMENTP]
			,[FAM_P]
			,[IM_P]
			,[OT_P]
			,W_P
			,[DR_P]
			,dost_p.DOST_P_AS_STRING
			,(
				SELECT TOP 1 EntityID
				FROM vclib.T_ENTITIES
				WHERE EntName = 'FMO_SCHET_ZAP'
				)
		FROM #TEMP_ZAP t0
		LEFT JOIN #dost dost ON dost.N_ZAP = t0.N_ZAP
		LEFT JOIN #dost_p dost_p ON dost_p.N_ZAP = t0.N_ZAP

		PRINT 'INSERT SCHET_SLUCH_ACCOMPLISHED' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO [IES].[T_SCHET_SLUCH_ACCOMPLISHED] (
			[SchetSluchAccomplishedID]
			,[SchetZap]
			,[IDCASE]
			,[IDCASE_LINK]
			,[OSN_LINK]
			,[IDCASE_MIS]
			,[ADR_GAR]
			,[ADR_NAME]
			,[USL_OK]
			,[VIDPOM]
			,[FOR_POM]
			,[DATE_Z_1]
			,[DATE_Z_2]
			,[KD_Z]
			,WEI
			,[RSLT]
			,[ISHOD]
			,[IS_PRERV]
			,[KOEF_PRERV]
			,[KOEF_PRIV]
			,[KOEF_SPEC]
			,[SRED_NFZ]
			,[KOEF_D]
			,[SUMV]
			,MedicalExpertiseStatus
			,type_
			)
		SELECT [SchetSluchAccomplishedID]
			,t2.[SchetZapID]
			,[IDCASE]
			,[IDCASE_LINK]
			,[OSN_LINK]
			,[IDCASE_MIS]
			,[ADR_GAR]
			,[ADR_NAME]
			,[USL_OK]
			,[VIDPOM]
			,[FOR_POM]
			,[DATE_Z_1]
			,[DATE_Z_2]
			,[KD_Z]
			,WEI
			,(
				SELECT TOP 1 v009.[IDRMP]
				FROM [IES].T_V009_RESULT v009
				WHERE v009.[IDRMP] = [RSLT]
				) AS RSLT
			,[ISHOD]
			,[IS_PRERV]
			,[KOEF_PRERV]
			,[KOEF_PRIV]
			,[KOEF_SPEC] AS [KOEF_SPEC]
			,[SRED_NFZ]
			,[KOEF_D]
			,[SUMV]
			,0 AS MedicalExpertiseStatus
			,(
				SELECT TOP 1 EntityID
				FROM VCLib.T_ENTITIES
				WHERE EntName = 'FMO_SCHET_SLUCH_ACCOMPLISHED'
				)
		FROM #TEMP_Z_SLUCH t1
		JOIN #TEMP_ZAP t2 ON t2.N_ZAP = t1.N_ZAP

		PRINT 'INSERT FMO_NPR' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO IES.T_FMO_NPR (
			FmoNprID
			,SchetZap
			,IDNPR
			,IDNPR_MIS
			,TAL_D
			,TAL_NUM
			,NPR_MO
			,POVOD
			,DS1
			,USL_OK
			,VIDPOM
			,FOR_POM
			,PROFIL
			,GR_HMP
			,VID_HMP
			,RESH_HMP
			,RESH
			,PODR
			,PODR_NAME
			,PROFIL_K
			,TAL_P
			)
		SELECT t0.NprId
			,t1.SchetZapId
			,t0.IDNPR
			,t0.IDNPR_MIS
			,t0.TAL_D
			,t0.TAL_NUM
			,t0.NPR_MO_F003
			,(
				SELECT TOP 1 FmoPovodId
				FROM ies.t_fmo_povod p
				WHERE p.Code = t0.POVOD
					AND t0.POVOD IS NOT NULL
				)
			,t0.DS1
			,t0.USL_OK
			,t0.VIDPOM
			,t0.FOR_POM
			,t0.PROFIL
			,t0.GR_HMP
			,t0.VID_HMP
			,(
				SELECT TOP 1 rhmp.FmoReshHmpID
				FROM ies.T_FMO_RESH_HMP rhmp
				WHERE rhmp.Code = t0.RESH_HMP
				) AS RESH_HMP
			,(
				SELECT TOP 1 rsmp.FmoReshSmpID
				FROM ies.T_FMO_RESH_SMP rsmp
				WHERE rsmp.Code = t0.RESH
				) AS RESH
			,t0.PODR
			,t0.PODR_NAME
			--		, PROFIL_K
			,(
				SELECT TOP 1 V020BedProfileId
				FROM IES.T_V020_BED_PROFILE
				WHERE convert(INT, t0.[PROFIL_K]) = IDK_PR
					AND DATEBEG <= t0.TAL_D
					AND (
						DATEEND IS NULL
						OR DATEEND >= t0.TAL_D
						)
				) AS [PROFIL_K]
			,t0.TAL_P
		FROM #TEMP_NPR t0
		JOIN #TEMP_ZAP t1 ON t0.N_ZAP = t1.N_ZAP

		PRINT 'INSERT NPR/DS' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		IF (
				EXISTS (
					SELECT *
					FROM INFORMATION_SCHEMA.TABLES
					WHERE TABLE_SCHEMA = 'ies'
						AND TABLE_NAME = 'T_FMO_DS'
					)
				)
		BEGIN
			--select * from [IES].T_FMO_DS
			INSERT INTO [IES].T_FMO_DS (
				FmoDsId
				,Ds
				,DsType
				,FmoNpr
				)
			SELECT NEWID()
				,t0.DS
				,t0.DsType
				,t1.NprID
			FROM #TEMP_NPR_DS t0
			JOIN #TEMP_NPR t1 ON t0.IDNPR = t1.IDNPR
				AND t0.N_ZAP = t1.N_ZAP
		END

		PRINT 'INSERT T_SCHET_SLUCH' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO [IES].[T_SCHET_SLUCH] (
			[SchetSluchID]
			,[SchetZap]
			,[SchetSluchAccomplished]
			,[type_]
			,[SL_ID]
			,[PODR]
			,[PODR_NAME]
			,[OID_FRMO]
			,[PROFIL]
			,[PROFIL_K]
			,[NHISTORY]
			,[DS_GR]
			,[DS1]
			,ILLACT
			,ILLREASON
			,[C_ZAB]
			,[GR_HMP]
			,[VID_HMP]
			,[METOD_HMP]
			,[MODEL_HMP]
			,[LECH_HMP]
			,[DZP]
			,[NFZ]
			,[TARIF]
			,[COMENTSL]
			--данные которых нет
			,DATE_1
			,DATE_2
			,SUMV
			,WEI
			)
		SELECT t1.[SchetSluchID]
			,t2.[SchetZapID]
			,t3.[SchetSluchAccomplishedID]
			,(
				SELECT TOP 1 EntityID
				FROM vclib.T_ENTITIES
				WHERE EntName = 'FMO_SCHET_SLUCH'
				)
			,[SL_ID]
			,[PODR]
			,[PODR_NAME]
			,[OID_FRMO]
			,[PROFIL]
			,(
				SELECT TOP 1 V020BedProfileId
				FROM IES.T_V020_BED_PROFILE
				WHERE convert(INT, t1.[PROFIL_K]) = IDK_PR
					AND DATEBEG <= t3.[DATE_Z_2]
					AND (
						DATEEND IS NULL
						OR DATEEND >= t3.[DATE_Z_2]
						)
				) AS [PROFIL_K]
			,[NHISTORY]
			,[DS_GR]
			,[DS1]
			,ILLACT
			,ILLREASON
			,(
				SELECT TOP 1 V027CZabID
				FROM ies.T_V027_C_ZAB zab
				WHERE IDCZ = t1.C_ZAB
					AND DATEBEG <= t3.[DATE_Z_2]
					AND (
						DATEEND IS NULL
						OR DATEEND >= t3.[DATE_Z_2]
						)
				) AS [C_ZAB]
			,[GR_HMP]
			,[VID_HMP]
			,(
				SELECT TOP 1 V019VmpMethodID
				FROM ies.T_V019_VMP_METHOD v019
				WHERE v019.IDHM = t1.METOD_HMP
				ORDER BY isnull(v019.dateend, getdate()) DESC
					,v019.datebeg DESC
				) AS [METOD_HMP]
			,(
				SELECT TOP 1 v022.V022ModpacID
				FROM ies.T_V022_MODPAC v022
				WHERE v022.IDMPAC = t1.MODEL_HMP
					AND DATEBEG <= DATE_Z_2
					AND (
						DATEEND IS NULL
						OR DATEEND >= DATE_Z_2
						)
				) AS MODEL_HMP
			,[LECH_HMP]
			,[DZP]
			,[NFZ]
			,[TARIF]
			,[COMENTSL]
			,DATE_Z_1
			,DATE_Z_2
			,t3.SUMV AS SUMV
			,t3.WEI
		FROM #TEMP_SLUCH t1
		JOIN #TEMP_ZAP t2 ON t2.N_ZAP = t1.N_ZAP
		JOIN #TEMP_Z_SLUCH t3 ON t1.IDCASE = t3.IDCASE

		PRINT 'INSERT KSG' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO [IES].[T_KSG] (
			KsgID
			,SchetSluch
			,[N_KSG]
			,[GR]
			,[VER_KSG]
			,[KOEF_Z]
			,[SL_K]
			,[IT_SL]
			,[NOT_ONK_SL]
			)
		SELECT [KsgID]
			,t1.SchetSluchID
			,[N_KSG]
			,[GR]
			,[VER_KSG]
			,[KOEF_Z]
			,[SL_K]
			,[IT_SL]
			,[NOT_ONK_SL]
		FROM #TEMP_KSG t0
		JOIN #TEMP_SLUCH t1 ON t0.[SL_ID] = t1.[SL_ID]
			AND t0.IDCASE = t1.IDCASE

		PRINT 'INSERT CRIT' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO [IES].[T_KSG_CRIT] (
			KsgCritID
			,Ksg
			,V024DopKr
			)
		SELECT newID()
			,t2.KsgID
			,(
				SELECT TOP 1 V024DopKrID
				FROM IES.T_V024_DOP_KR
				WHERE IDDKK = t1.CRIT
				) AS V024DopKr
		FROM #TEMP_CRIT t1
		JOIN #TEMP_KSG t2 ON t2.[SL_ID] = t1.[SL_ID]
			AND t1.IDCASE = t2.IDCASE
		JOIN #TEMP_SLUCH ts ON ts.[SL_ID] = t1.[SL_ID]
			AND t1.IDCASE = ts.IDCASE

		PRINT 'INSERT SL_KOEF' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO [IES].[T_KSLP] (
			KslpID
			,KOEF
			,OmsSchetSluch
			,KOEF_TYPE
			,Ksg
			) ----вопрос
		SELECT [KslpID]
			,t1.Z_SL
			,t2.[SchetSluchID]
			,t1.IDSL
			,t3.KsgID
		FROM #TEMP_SL_KOEF t1
		JOIN #TEMP_SLUCH t2 ON t2.[SL_ID] = t1.[SL_ID]
			AND t1.IDCASE = t2.IDCASE
		JOIN #TEMP_KSG t3 ON t2.[SL_ID] = t3.[SL_ID]
			AND t2.IDCASE = t3.IDCASE

		PRINT 'INSERT SCHET_USL' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO [IES].[T_SCHET_USL] (
			[SchetUslID]
			,[SchetSluch]
			,[type_]
			,[IDSERV]
			,[VID_VME]
			,[COMENTU]
			,[DATE_IN]
			,[DATE_OUT]
			,[SUMV_USL]
			)
		SELECT t1.[SchetUslID]
			,t2.[SchetSluchID]
			,710 --OMS_SCHET_USL
			,[IDSERV]
			,[VID_VME]
			,[COMENTU]
			,t3.DATE_Z_1
			,t3.DATE_Z_2
			,t3.SUMV
		FROM #TEMP_USL t1
		JOIN #TEMP_SLUCH t2 ON t2.[SL_ID] = t1.[SL_ID]
			AND t1.IDCASE = t2.IDCASE
		JOIN #TEMP_Z_SLUCH t3 ON (t3.IDCASE = t2.IDCASE)

		PRINT 'INSERT SL/DS' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO [IES].[T_SCHET_SLUCH_DS] (
			[SchetSluchDsID]
			,[SchetSluch]
			,[MKB]
			,[MKBType]
			,DS2_PR
			,PR_DS2_N
			)
		SELECT NEWID()
			,t2.[SchetSluchID]
			,t1.[DS]
			,DS_TYPE
			,DS2_PR
			,PR_DS2_N
		FROM #TEMP_DS t1
		JOIN #TEMP_SLUCH t2 ON t2.[SL_ID] = t1.[SL_ID]
			AND t1.IDCASE = t2.IDCASE

		PRINT 'INSERT ONK_SL' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO [IES].[T_SCHET_SLUCH_ONK] (
			SchetSluchOnkID
			,DS1_T
			,STAD
			,ONK_T
			,ONK_N
			,ONK_M
			,MTSTZ
			,SOD
			,SchetSluch
			,K_FR
			,WEI
			,HEI
			,BSA
			)
		SELECT SchetSluchOnkID
			,(
				SELECT top 1 n018.N018OnkReasID
				FROM ies.T_N018_ONK_REAS n018
				WHERE n018.ID_REAS = DS1_T
					AND DATEBEG <= DATE_Z_2
					AND (
						DATEEND IS NULL
						OR DATEEND >= DATE_Z_2
						)
				)
			,(
				SELECT TOP 1 tn002.[N002StadiumID]
				FROM [IES].[T_N002_STADIUM] tn002
				WHERE tn002.ID_st = t0.STAD
					AND DATEBEG <= DATE_Z_2
					AND (
						DATEEND IS NULL
						OR DATEEND >= DATE_Z_2
						)
				)
			,(
				SELECT TOP 1 tn003.[N003TumorID]
				FROM [IES].[T_N003_TUMOR] tn003
				WHERE tn003.[ID_T] = t0.ONK_T
					AND DATEBEG <= DATE_Z_2
					AND (
						DATEEND IS NULL
						OR DATEEND >= DATE_Z_2
						)
				)
			,(
				SELECT TOP 1 tn004.[N004NodusID]
				FROM [IES].[T_N004_NODUS] tn004
				WHERE tn004.[ID_N] = t0.ONK_N
					AND DATEBEG <= DATE_Z_2
					AND (
						DATEEND IS NULL
						OR DATEEND >= DATE_Z_2
						)
				)
			,(
				SELECT TOP 1 tn005.[N005MetastasisID]
				FROM [IES].[T_N005_METASTASIS] tn005
				WHERE tn005.[ID_M] = t0.ONK_M
					AND DATEBEG <= DATE_Z_2
					AND (
						DATEEND IS NULL
						OR DATEEND >= DATE_Z_2
						)
				)
			,[MTSTZ]
			,[SOD]
			,t1.SchetsluchID
			,K_FR
			,t0.WEI
			,HEI
			,BSA
		FROM #TEMP_ONK_SL t0
		JOIN #TEMP_SLUCH t1 ON (
				t0.IDCASE = t1.IDCASE
				AND t0.SL_ID = t1.SL_ID
				)
		JOIN #TEMP_Z_SLUCH t2 ON t1.IDCASE = t2.IDCASE

		PRINT 'INSERT CONS' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO [IES].[T_SCHET_SLUCH_CONS] (
			SchetSluchConsID
			,SchetSluch
			,N019OnkCons
			,DT_CONS
			)
		SELECT newid()
			,ts.SchetSluchID
			,(
				SELECT TOP 1 n019.N019OnkConsID
				FROM ies.T_N019_ONK_CONS n019
				WHERE n019.ID_CONS = tss.PR_CONS
				)
			,tss.DT_CONS
		FROM #TEMP_CONS tss
		JOIN #TEMP_SLUCH ts ON (
				tss.IDCASE = ts.IDCASE
				AND tss.SL_ID = ts.SL_ID
				)

		PRINT 'INSERT B_DIAG' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO [IES].[T_SCHET_SLUCH_ONK_DIAG] (
			SchetSluchOnkDiagID
			,SchetSluchOnk
			,DIAG_TIP
			,DIAG_CODE
			,DIAG_RSLT
			,DIAG_DATE
			,REC_RSLT
			)
		SELECT NEWID()
			,tsso.SchetSluchOnkID
			,[DIAG_TIP]
			,[DIAG_CODE]
			,[DIAG_RSLT]
			,DIAG_DATE
			,REC_RSLT
		FROM #TEMP_B_DIAG ssod
		JOIN #TEMP_ONK_SL tsso ON (
				tsso.IDCASE = ssod.IDCASE
				AND tsso.SL_ID = ssod.SL_ID
				)

		PRINT 'INSERT B_PROT' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO [IES].[T_SCHET_SLUCH_ONK_PROT] (
			SchetSluchOnkProtID
			,SchetSluchOnk
			,PROT
			,D_PROT
			)
		SELECT SchetSluchOnkProtID
			,t1.SchetSluchOnkID
			,(
				SELECT TOP 1 tn001.[N001PrOtID]
				FROM [IES].[T_N001_PrOt] tn001
				WHERE tn001.[ID_PrOt] = t0.PROT
				)
			--and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2))
			,D_PROT
		FROM #TEMP_B_PROT t0
		JOIN #TEMP_ONK_SL t1 ON (
				t1.IDCASE = t0.IDCASE
				AND t1.SL_ID = t0.SL_ID
				)

		PRINT 'INSERT ONK_USL' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO [IES].[T_SCHET_USL_ONK] (
			SchetUslOnkID
			,USL_TIP
			,HIR_TIP
			,LEK_TIP_L
			,LEK_TIP_V
			,[PPTR]
			,LUCH_TIP
			,SchetSluchOnk
			)
		SELECT SchetUslOnkID
			,(
				SELECT TOP 1 tn013.[N013TreatTypeID]
				FROM [IES].[T_N013_TREAT_TYPE] tn013
				WHERE tn013.[ID_TLech] = t0.USL_TIP
					AND DATEBEG <= DATE_Z_2
					AND (
						DATEEND IS NULL
						OR DATEEND >= DATE_Z_2
						)
				)
			,(
				SELECT TOP 1 tn014.[N014SurgTreatID]
				FROM [IES].[T_N014_SURG_TREAT] tn014
				WHERE tn014.[ID_THir] = t0.HIR_TIP
					AND DATEBEG <= DATE_Z_2
					AND (
						DATEEND IS NULL
						OR DATEEND >= DATE_Z_2
						)
				)
			,(
				SELECT TOP 1 tn015.[N015DrugTherapyLinesID]
				FROM [IES].[T_N015_DRUG_THERAPY_LINES] tn015
				WHERE tn015.[ID_TLek_L] = t0.LEK_TIP_L
					AND DATEBEG <= DATE_Z_2
					AND (
						DATEEND IS NULL
						OR DATEEND >= DATE_Z_2
						)
				)
			,(
				SELECT TOP 1 tn016.[N016DrugTherapyCyclesID]
				FROM [IES].[T_N016_DRUG_THERAPY_CYCLES] tn016
				WHERE tn016.[ID_TLek_V] = t0.LEK_TIP_V
					AND DATEBEG <= DATE_Z_2
					AND (
						DATEEND IS NULL
						OR DATEEND >= DATE_Z_2
						)
				)
			,[PPTR]
			,(
				SELECT TOP 1 tn017.[N017RadiationTherapyTypesID]
				FROM [IES].[T_N017_RADIATION_THERAPY_TYPES] tn017
				WHERE tn017.[ID_TLuch] = t0.LUCH_TIP
					AND DATEBEG <= DATE_Z_2
					AND (
						DATEEND IS NULL
						OR DATEEND >= DATE_Z_2
						)
				) AS LUCH_TIP
			,t1.SchetSluchOnkID
		FROM #TEMP_ONK_USL t0
		JOIN #TEMP_ONK_SL t1 ON (
				t0.IDCASE = t1.IDCASE
				AND t0.SL_ID = t1.SL_ID
				)
		JOIN #TEMP_Z_SLUCH t2 ON t0.IDCASE = t2.IDCASE

		PRINT 'INSERT LEK_PR' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO [IES].[T_SCHET_USL_ONK_LEK_PR] (
			[SchetUslOnkLekPrID]
			,[REGNUM]
			,[SchetUslOnk]
			,V024DopKr
			,DATE_INJ
			,DOSE_INJ
			)
		SELECT [LekPrID]
			,(
				SELECT TOP 1 N020OnkLekpID
				FROM [IES].[T_N020_ONK_LEKP] N020
				WHERE N020.ID_LEKP = [REGNUM]
				)
			,t0.SchetUslOnkID
			,(
				SELECT TOP 1 V024DopKrID
				FROM ies.T_V024_DOP_KR
				WHERE CODE_SH = IDDKK
				)
			,t0.DATE_INJ
			,t0.DOSE_INJ
		FROM #TEMP_LEK_PR t0

		--add 28042023
		INSERT INTO [IES].[T_SCHET_SLUCH_LEK_PR] (
			[SchetSluchLekPrID]
			,[SchetSluch]
			,[DATA_INJ]
			,[CODE_SH]
			,[REGNUM]
			,[COD_MARK]
			,[ED_IZM]
			,[DOSE_INJ]
			,[METHOD_INJ]
			,[COL_INJ]
			)
		SELECT pr.[SchetSluchLekPrID]
			,ts.SchetSluchID
			,pr.[DATA_INJ]
			,(
				SELECT TOP 1 v032.V032DrugGrRegID
				FROM ies.T_V032_DRUG_GR_REG v032
				WHERE pr.CODE_SH = v032.ScheDrugGrCd
					AND v032.DATEBEG <= t3.DATE_Z_2
					AND (
						v032.DATEEND IS NULL
						OR v032.DATEEND >= t3.DATE_Z_2
						)
				) AS [CODE_SH]
			,(
				SELECT TOP 1 N020.N020OnkLekpID
				FROM [IES].[T_N020_ONK_LEKP] N020
				WHERE N020.ID_LEKP = pr.[REGNUM]
					AND N020.DATEBEG <= t3.DATE_Z_2
					AND (
						N020.DATEEND IS NULL
						OR N020.DATEEND >= t3.DATE_Z_2
						)
				) AS [REGNUM]
			,pr.[COD_MARK]
			,(
				SELECT TOP 1 eut.EhrUnitTypeID
				FROM [IES].T_EHR_UNIT_TYPE eut
				WHERE pr.[ED_IZM] = eut.ID
				) AS [ED_IZM]
			,pr.[DOSE_INJ]
			,(
				SELECT TOP 1 emi.EhrMetIntroID
				FROM [IES].T_EHR_MET_INTRO emi
				WHERE pr.[METHOD_INJ] = emi.ID
				) AS [METHOD_INJ]
			,pr.[COL_INJ]
		FROM #TEMP_SCHET_SLUCH_LEK_PR pr
		JOIN #TEMP_SLUCH ts ON ts.[SL_ID] = pr.[SL_ID]
			AND ts.IDCASE = pr.IDCASE
		JOIN #TEMP_Z_SLUCH t3 ON (t3.IDCASE = ts.IDCASE)

		INSERT INTO [IES].[T_SCHET_USL_MED_DEV] (
			[SchetUslMedDevID]
			,[SchetUsl]
			,[DATE_MED]
			,[CODE_MEDDEV]
			,[NUMBER_SER]
			)
		SELECT md.[SchetUslMedDevID]
			,u.[SchetUslID]
			,md.[DATE_MED]
			,md.[CODE_MEDDEV]
			,md.[NUMBER_SER]
		FROM #SCHET_USL_MED_DEV md
		JOIN #TEMP_USL u ON u.[SL_ID] = md.[SL_ID]
			AND u.IDCASE = md.IDCASE
			AND u.IDSERV = md.IDSERV
		JOIN #TEMP_SLUCH ts ON ts.[SL_ID] = u.[SL_ID]
			AND ts.IDCASE = u.IDCASE
	END

	DROP TABLE #TEMP_SCHET

	DROP TABLE #TEMP_ZAP

	DROP TABLE #TEMP_NPR

	DROP TABLE #TEMP_NPR_DS

	DROP TABLE #TEMP_Z_SLUCH

	DROP TABLE #TEMP_SLUCH

	DROP TABLE #TEMP_USL

	DROP TABLE #TEMP_KSG

	DROP TABLE #TEMP_CRIT

	DROP TABLE #TEMP_SL_KOEF

	DROP TABLE #TEMP_DS

	DROP TABLE #TEMP_CONS

	DROP TABLE #TEMP_ONK_SL

	DROP TABLE #TEMP_B_DIAG

	DROP TABLE #TEMP_B_PROT

	DROP TABLE #TEMP_ONK_USL

	DROP TABLE #TEMP_LEK_PR

	DROP TABLE #TEMP_SCHET_SLUCH_LEK_PR

	DROP TABLE #SCHET_USL_MED_DEV

	DROP TABLE #Errors
END
