if exists ( select * from sys.all_objects ao where ao.name = 'sp_GetSchetMtrOtMoGIS' ) DROP PROCEDURE [dbo].[sp_GetSchetMtrOtMoGIS]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_GetSchetMtrOtMoGIS](@SchetID uniqueidentifier)
as
begin

Declare @VERSION varchar(5),@year int, @month int, @filename varchar(50), @SD_Z int, @SUMV decimal(17,2), @SUMP decimal(17,2), @SANK_MEK decimal(17,2), @SANK_MEE decimal(17,2), @SANK_EKMP decimal(17,2)

SELECT TOP 1 @VERSION = s.[VERSION], @year = s.[YEAR], @month = s.[MONTH], @filename = s.[FILENAME]
,@SD_Z = count(*), @SUMV = sum(ssa.SUMV), @SUMP = sum(ssa.SUMP), @SANK_MEK = sum(ssa.SANK_MEK), @SANK_MEE = sum(ssa.SANK_MEE), @SANK_EKMP = sum(ssa.SANK_EKMP)
FROM [IES].T_SCHET s with(nolock)
join ies.T_SCHET_ZAP sz with(nolock) on sz.Schet = s.SchetID and sz.IsDelete = 0 
join [IES].[T_SCHET_SLUCH_ACCOMPLISHED] ssa with(nolock) on ssa.SchetZap = sz.SchetZapID and ssa.IsDelete = 0 
where s.SchetID = @SchetID
and not exists(select * from ies.T_SCHET_ACT_ITEM ai with(nolock) where ai.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID and ai.IsAddToAct = 1)
group by s.[VERSION], s.[YEAR], s.[MONTH], s.[FILENAME]

SELECT tt.[name], tt.[XML]
FROM (
select @filename + '.xml' as [name],
'<?xml version="1.0" encoding="Windows-1251"?>'
+ (select 
--ZGLV
@VERSION AS 'ZGLV/VERSION' 
, CONVERT(VARCHAR(10), t1.DATA, 126) AS 'ZGLV/DATA'
, t1.FILENAME AS 'ZGLV/FILENAME'
, @SD_Z 'ZGLV/SD_Z'
--SCHET
,t1.CODE AS 'SCHET/CODE'
,t1.CODE_MO AS 'SCHET/CODE_MO'
,t1.YEAR AS 'SCHET/YEAR'
,t1.MONTH AS 'SCHET/MONTH'
,t1.NSCHET AS 'SCHET/NSCHET'
,CONVERT(VARCHAR(10), t1.DSCHET, 126) AS 'SCHET/DSCHET'
,t1.PLAT AS 'SCHET/PLAT'
,@SUMV AS 'SCHET/SUMMAV'
,t1.COMENTS  AS 'SCHET/COMENTS'
,@SUMP AS 'SCHET/SUMMAP'
,@SANK_MEK AS 'SCHET/SANK_MEK'
,@SANK_MEE AS 'SCHET/SANK_MEE'
,@SANK_EKMP AS 'SCHET/SANK_EKMP'
,(select top 1 d.IDDT from [IES].T_V016_DISPT d where d.V016ID = t1.DISP)  AS 'SCHET/DISP'
,( 
	SELECT 
	    t2.N_ZAP	AS 'N_ZAP'
		,t2.PR_NOV	AS 'PR_NOV'       
	   -- PACIENT
	    ,t2.ID_PAC AS 'PACIENT/ID_PAC'
		,t2.VPOLIS as 'PACIENT/VPOLIS'
		,t2.SPOLIS AS 'PACIENT/SPOLIS'
		,t2.NPOLIS AS 'PACIENT/NPOLIS'
		,t2.ENP AS 'PACIENT/ENP'
	    ,t2.ST_OKATO AS 'PACIENT/ST_OKATO'
	    ,t2.SMO AS 'PACIENT/SMO'
		,t2.SMO_OGRN AS 'PACIENT/SMO_OGRN'
		,t2.SMO_OK AS 'PACIENT/SMO_OK'
		,t2.SMO_NAM AS 'PACIENT/SMO_NAM'
		,t2.INV AS 'PACIENT/INV'
		,t2.MSE as 'PACIENT/MSE'
		,t2.NOVOR AS 'PACIENT/NOVOR'
		,t2.VNOV_D AS 'PACIENT/VNOV_D'  
	   ,(
			SELECT
				ssa.IDCASE as 'IDCASE'
				,ssa.USL_OK AS 'USL_OK'
				,ssa.VIDPOM AS 'VIDPOM'
				,ssa.FOR_POM AS 'FOR_POM'
				,ssa.NPR_MO AS 'NPR_MO'
				,CONVERT(VARCHAR(10),ssa.NPR_DATE,126) AS 'NPR_DATE'
				,ssa.LPU AS 'LPU'
				,ssa.VBR AS 'VBR'
				,CONVERT(VARCHAR(10),ssa.DATE_Z_1,126) AS 'DATE_Z_1'
				,CONVERT(VARCHAR(10),ssa.DATE_Z_2,126) AS 'DATE_Z_2'
				,ssa.P_OTK as 'P_OTK'
				,ssa.KD_Z AS 'KD_Z'
				,ssa.VNOV_M AS 'VNOV_M'
				,ssa.RSLT AS 'RSLT'
				,ssa.RSLT_D AS 'RSLT_D'
				,ssa.ISHOD AS 'ISHOD'
				,case when ssa.OS_SLUCH='' then null else ssa.OS_SLUCH end AS 'OS_SLUCH'
				,ssa.VB_P AS 'VB_P'
			   ,(
					SELECT 
						t3.SL_ID as 'SL_ID'
						,t3.VID_HMP AS 'VID_HMP'
						,(select top 1 v019.IDHM from ies.T_V019_VMP_METHOD v019 with(nolock) where v019.V019VmpMethodID = t3.METOD_HMP) AS 'METOD_HMP'
						,t3.LPU_1 AS 'LPU_1'
						,t3.PODR AS 'PODR'
						,t3.PROFIL AS 'PROFIL'
						,(select top 1 v020.idk_pr from ies.t_v020_bed_profile v020 with(nolock) where t3.profil_k = v020.v020bedprofileid) AS 'PROFIL_K'
						,t3.DET AS 'DET'
						,CONVERT(VARCHAR(10),t3.TAL_D,126) AS 'TAL_D'
						,t3.TAL_NUM AS 'TAL_NUM'
						,CONVERT(VARCHAR(10),t3.TAL_P,126) AS 'TAL_P'
						,(select top 1 v025.idpc from ies.t_v025_kpc v025 with(nolock) where v025.v025kpcid = t3.p_cel) as 'P_CEL'
						,t3.NHISTORY AS 'NHISTORY'
						,t3.P_PER AS 'P_PER'
						,CONVERT(VARCHAR(10),t3.DATE_1,126) AS 'DATE_1'
						,CONVERT(VARCHAR(10),t3.DATE_2,126) AS 'DATE_2'
						,t3.KD AS 'KD'
						,t3.WEI as 'WEI'
						,t3.DS0 AS 'DS0'
						,t3.DS1 AS 'DS1'
						,t3.DS1_PR AS 'DS1_PR'
						,(
							SELECT t4.MKB AS 'DS2'
							FROM [IES].T_SCHET_SLUCH_DS t4
							WHERE t4.SchetSluch = t3.SchetSluchID and t4.MKBType = 0 and t4.pr_ds2_n is null
							FOR
							XML PATH(''),
							TYPE
						)
						,( 
							SELECT t4.MKB	AS 'DS3'
							FROM [IES].T_SCHET_SLUCH_DS t4
							WHERE t4.SchetSluch = t3.SchetSluchID and t4.MKBType = 1
							FOR
							XML PATH(''),
							TYPE
						)
						,(select top 1 v027.idcz from ies.t_v027_c_zab v027 with(nolock) where v027.v027czabid = t3.c_zab) as 'C_ZAB'
						,t3.DS_ONK as 'DS_ONK'
						,t3.PR_D_N AS 'PR_D_N'
						,t3.DN as 'DN'
						,t3.CODE_MES1 AS 'CODE_MES1'
						,t3.CODE_MES2 AS 'CODE_MES2'
						,( 
							SELECT t4.MKB AS 'DS2'
							, t4.DS2_PR as 'DS2_PR'
							, t4.PR_DS2_N as 'PR_DS2_N'
							FROM [IES].T_SCHET_SLUCH_DS t4
							WHERE t4.SchetSluch = t3.SchetSluchID and t4.MKBType = 0  and t4.pr_ds2_n is not null
							FOR
							XML PATH('DS2_N'),
							TYPE
						)
						,(
							SELECT
								naz.NAZ_N as 'NAZ_N'
								,naz.NAZR as 'NAZ_R'
								,naz.NAZ_IDDOKT as 'NAZ_IDDOKT'
								--,(select top 1 v021.code_spec from ies.t_v021_med_spec v021 with(nolock) where v021.idspec =naz.NAZ_IDDOKT) as 'NAZ_IDDOKT'  
								,naz.NAZ_SP as 'NAZ_SP'
								--,(select top 1 v021.code_spec from ies.t_v021_med_spec v021 with(nolock) where v021.idspec =naz.NAZ_SP) as 'NAZ_SP'  
								,naz.NAZ_V as 'NAZ_V'
								,naz.NAZ_USL as 'NAZ_USL'
								,CONVERT(VARCHAR(10), naz.NAPR_DATE, 126) as 'NAPR_DATE'
								,naz.NAPR_MO as 'NAPR_MO'
								,naz.NAZ_PMP as 'NAZ_PMP'
								,(select top 1 v020.IDK_PR from ies.T_V020_BED_PROFILE v020 with(nolock) where v020.V020BedProfileID = naz.NAZ_PK) as 'NAZ_PK'
							FROM ies.T_SCHET_SLUCH_NAZ naz
							where naz.SchetSluch = t3.SchetSluchID
							FOR
							XML PATH('NAZ'),
							TYPE
						)
						
						,(
							SELECT
								CONVERT(VARCHAR(10), napr.NAPR_DATE, 126) as 'NAPR_DATE'
								,napr.NAPR_MO as 'NAPR_MO'
								,napr.NAPR_V as 'NAPR_V'
								,napr.MET_ISSL as 'MET_ISSL'
								,napr.NAPR_USL as 'NAPR_USL'
							FROM [IES].[T_SCHET_USL_NAPR] napr
							WHERE napr.SchetSluch = t3.SchetSluchID

							FOR
							XML PATH('NAPR'),
							TYPE
						)
						,(
							SELECT
								(select top 1 n019.id_cons from ies.t_n019_onk_cons n019 with(nolock) where n019.n019onkconsid = cons.n019onkcons) as 'PR_CONS'
								,CONVERT(VARCHAR(10), cons.DT_CONS, 126) as 'DT_CONS'
							FROM [IES].[T_SCHET_SLUCH_CONS] cons
							WHERE cons.SchetSluch = t3.SchetSluchID

							FOR
							XML PATH('CONS'),
							TYPE
						)	
						,(
							SELECT 
							(select top 1 ID_REAS  from [IES].[T_N018_ONK_REAS] with(nolock) where onk.DS1_T = [N018OnkReasID]  ) as 'DS1_T',
							(select top 1 ID_st  from [IES].[T_N002_STADIUM] with(nolock) where onk.STAD = [N002StadiumID]  ) as 'STAD',
							(select top 1 ID_T  from [IES].[T_N003_TUMOR] with(nolock) where onk.ONK_T = [N003TumorID]  ) as 'ONK_T',
							(select top 1 ID_N from [IES].[T_N004_NODUS] with(nolock) where onk.ONK_N = [N004NodusID] ) as 'ONK_N',
							(select top 1 ID_M  from [IES].[T_N005_METASTASIS] with(nolock) where onk.ONK_M = [N005MetastasisID] ) as 'ONK_M',
							onk.MTSTZ as 'MTSTZ'
							,onk.SOD as 'SOD'
							,onk.K_FR as 'K_FR'
							,onk.WEI as 'WEI'
							,onk.HEI as 'HEI'
							,onk.BSA as 'BSA'
								--B_DIAG
								,(
									SELECT
										 CONVERT(VARCHAR(10),onkDiagSL.DIAG_DATE,126) as 'DIAG_DATE'
										,onkDiagSL.DIAG_TIP as 'DIAG_TIP'
										,onkDiagSL.DIAG_CODE as 'DIAG_CODE'
										,onkDiagSL.DIAG_RSLT as 'DIAG_RSLT'
										,onkDiagSL.REC_RSLT as 'REC_RSLT'
									FROM [IES].T_SCHET_SLUCH_ONK_DIAG onkDiagSL
									where onkDiagSL.SchetSluchOnk = onk.SchetSluchOnkID

									FOR
									XML PATH('B_DIAG'),
									TYPE
								)
								,(--B_PROT
									SELECT
										(select top 1 n001.ID_PrOt from ies.T_N001_PrOt n001 with(nolock) where n001.N001PrOtID = onkProtSL.PROT) as 'PROT'
										,CONVERT(VARCHAR(10),onkProtSL.D_PROT,126) as 'D_PROT'
									FROM [IES].T_SCHET_SLUCH_ONK_PROT onkProtSL
									where onkProtSL.SchetSluchOnk = onk.SchetSluchOnkID

									FOR
									XML PATH('B_PROT'),
									TYPE
								)
								,(  
									SELECT 
									(select top 1 ID_TLech  from [IES].[T_N013_TREAT_TYPE] with(nolock) where suo.USL_TIP = [N013TreatTypeID]  ) as 'USL_TIP',
									(select top 1 [ID_THir]  from [IES].[T_N014_SURG_TREAT] with(nolock) where suo.HIR_TIP = [N014SurgTreatID]  ) as 'HIR_TIP',
									(select top 1 ID_TLek_L  from [IES].[T_N015_DRUG_THERAPY_LINES] with(nolock) where suo.LEK_TIP_L = [N015DrugTherapyLinesID]  ) as 'LEK_TIP_L',
									(select top 1 ID_TLek_V from [IES].[T_N016_DRUG_THERAPY_CYCLES] with(nolock) where suo.LEK_TIP_V = [N016DrugTherapyCyclesID]  ) as 'LEK_TIP_V',
									(
										SELECT 
										(select top 1 ID_LEKP from [IES].[T_N020_ONK_LEKP] with(nolock) where N020OnkLekpID = [REGNUM])as 'REGNUM'
										,(select top 1 v024.IDDKK from ies.T_V024_DOP_KR v024 with(nolock) where v024.V024DopKrID = lek.V024DopKr) as 'CODE_SH'
										,(
											select 
											CONVERT(VARCHAR(10),dt.DATE_INJ,126) as 'DATE_INJ'
											from ies.T_SCHET_USL_ONK_LEK_PR_DATE dt
											where dt.SchetUslOnkLekPr = lek.SchetUslOnkLekPrID
											FOR
											XML PATH(''),
											TYPE
										) 
										FROM ies.T_SCHET_USL_ONK_LEK_PR lek
										where lek.SchetUslOnk = suo.SchetUslOnkID
										FOR
										XML PATH('LEK_PR'),
										TYPE
									)
									, suo.PPTR as 'PPTR'
									,(select top 1 ID_TLuch  from [IES].[T_N017_RADIATION_THERAPY_TYPES] with(nolock) where suo.luch_tip = [N017RadiationTherapyTypesID]) as 'LUCH_TIP'
									FROM [IES].[T_SCHET_USL_ONK] suo	
									WHERE onk.SchetSluchOnkID = suo.SchetSluchOnk 
			
									FOR
									XML PATH('ONK_USL'),
									TYPE
								)
								 
							FROM IES.T_SCHET_SLUCH_ONK onk
							where onk.SchetSluch = t3.SchetSluchID
							FOR
							XML PATH('ONK_SL'),
							TYPE
						)	
						,(
							SELECT
								ksg.N_KSG as 'N_KSG'
								,ksg.VER_KSG as 'VER_KSG'
								,ksg.KSG_PG as 'KSG_PG'
								,(select top 1 K_KPG from [IES].T_V026_KPG with(nolock) where V026KpgID = ksg.N_KPG) as 'N_KPG'
								,ksg.KOEF_Z as 'KOEF_Z'
								,ksg.KOEF_UP as 'KOEF_UP'
								,ksg.BZTSZ as 'BZTSZ'
								,ksg.KOEF_D as 'KOEF_D'
								,ksg.KOEF_U as 'KOEF_U'
								,(
									select 
									(select top 1 v024.iddkk from ies.t_v024_dop_kr v024 with(nolock) where cr.v024dopkr = v024.v024dopkrid) as 'CRIT'
									from ies.T_KSG_CRIT cr
									where cr.Ksg = ksg.KsgID
									FOR
									XML PATH(''),
									TYPE
								) 
								,ksg.SL_K as 'SL_K'
								,ksg.IT_SL as 'IT_SL'
								,(
									SELECT kslp.KOEF_TYPE as 'IDSL'
										,kslp.KOEF as 'Z_SL'
									FROM [IES].T_KSLP kslp
									WHERE kslp.Ksg = ksg.KsgID 
									FOR
									XML PATH('SL_KOEF'),
									TYPE
								)
							FROM [IES].T_KSG ksg 
							WHERE ksg.SchetSluch = t3.SchetSluchID
							FOR
							XML PATH('KSG_KPG'),
							TYPE
						)
						,t3.REAB as 'REAB'
						,(select top 1 v021.code_spec from ies.t_v021_med_spec v021 with(nolock) where v021.idspec =t3.prvs3) as 'PRVS'                                               
						,t3.VERS_SPEC as 'VERS_SPEC'
						,t3.IDDOKT AS 'IDDOKT'
						,t3.ED_COL AS 'ED_COL'
						,t3.TARIF AS 'TARIF'
						,t3.SUMV as 'SUM_M'
						,(
							SELECT CONVERT(VARCHAR(10), md.DATA_INJ, 126) AS 'DATA_INJ'
								,(
								SELECT TOP 1 V032.ScheDrugGrCd
								FROM ies.T_V032_DRUG_GR_REG V032 with(nolock)
								WHERE V032.V032DrugGrRegID = md.CODE_SH
								) AS 'CODE_SH'
								,(SELECT TOP 1 ID_LEKP
								FROM [IES].[T_N020_ONK_LEKP] with(nolock)
								WHERE N020OnkLekpID = [REGNUM]
								) AS 'REGNUM'
								,md.COD_MARK AS 'COD_MARK'
								,(select 
										(select top 1 eut.ID from [IES].T_EHR_UNIT_TYPE eut with(nolock) where md.[ED_IZM] = eut.EhrUnitTypeID) as 'ED_IZM'
										,md.DOSE_INJ as 'DOSE_INJ'
										,(select top 1 eut.ID from [IES].T_EHR_MET_INTRO eut with(nolock) where md.METHOD_INJ = eut.EhrMetIntroID)  as 'METHOD_INJ'
										,md.COL_INJ AS 'COL_INJ'
									FOR XML PATH('LEK_DOSE')
									,TYPE
								)
							FROM [IES].T_SCHET_SLUCH_LEK_PR md
							WHERE md.SchetSluch = t3.SchetSluchID
							FOR XML PATH('LEK_PR')
							,TYPE
						)
						,(  	SELECT 
									 t4.IDSERV AS 'IDSERV'
									,t4.LPU AS 'LPU'
									,t4.LPU_1 AS 'LPU_1'
									,t4.PODR AS 'PODR'
									,t4.PROFIL AS 'PROFIL'
									,t4.VID_VME AS 'VID_VME'
									,t4.DET AS 'DET'
									,CONVERT(VARCHAR(10),t4.DATE_IN,126) AS 'DATE_IN'
									,CONVERT(VARCHAR(10),t4.DATE_OUT,126) AS 'DATE_OUT'
									,t4.DS AS 'DS'
									,t4.P_OTK AS 'P_OTK'
									,t4.CODE_USL AS 'CODE_USL'
									,t4.KOL_USL AS 'KOL_USL'
									,t4.TARIF AS 'TARIF'
									,t4.SUMV_USL AS 'SUMV_USL'
									,(select top 1 v021.code_spec from ies.t_v021_med_spec v021 with(nolock) where v021.idspec =t3.prvs3) as 'PRVS'   
									,CODE_MD AS 'CODE_MD'
									,(
										SELECT CONVERT(VARCHAR(10), md.DATE_MED, 126) AS 'DATE_MED'
											,md.CODE_MEDDEV AS 'CODE_MEDDEV'
											,md.NUMBER_SER AS 'NUMBER_SER'
										FROM [IES].T_SCHET_USL_MED_DEV md
										WHERE md.SchetUsl = t4.SchetUslID
										FOR XML PATH('MED_DEV')
										,TYPE
									)
									,(SELECT 
											 mr.MR_N AS 'MR_N'
											 ,(select top 1 v021.code_spec from ies.t_v021_med_spec v021 with(nolock) where v021.idspec =mr.prvs) as 'PRVS' 
											 ,mr.CODE_MD AS 'CODE_MD'
										FROM [IES].T_MR_USL_N mr		
										WHERE mr.SchetUsl = t4.SchetUslID			
										FOR
										XML PATH('MR_USL_N'),
										TYPE
									)						
									,NPL AS 'NPL'
									,COMENTU AS 'COMENTU'
								FROM [IES].T_SCHET_USL t4	
								WHERE t4.SchetSluch = t3.SchetSluchID
								FOR
								XML PATH('USL'),
								TYPE
							)
						--,case when t1.type_ = 562 then t3.COMENTSL else [dbo].[fn_GetErrorsText](t3.SchetSluchID)end AS 'COMENTSL'
						,t3.COMENTSL AS 'COMENTSL'
					FROM [IES].T_SCHET_SLUCH t3		
					WHERE ssa.SchetSluchAccomplishedID = t3.SchetSluchAccomplished and t3.isdelete = 0
					FOR
					XML PATH('SL'),
					TYPE
				)
				,ssa.IDSP AS 'IDSP'
				,ssa.SUMV AS 'SUMV'
				,ssa.OPLATA AS 'OPLATA'
				,ssa.SUMP AS 'SUMP'
				,(  
					SELECT doubled.ExpertiseErrorID AS 'S_CODE'
						,case when doubled.id > 1 then 0 else ssa.sank_mek end AS 'S_SUM'
						,1 AS 'S_TIP'
						,(select
							ss.SL_ID as 'SL_ID'
							from ies.T_SCHET_SLUCH ss
							where ss.SchetSluchAccomplished=ssa.SchetSluchAccomplishedID
							FOR
							XML PATH(''),
							TYPE)
						,isnull((select top 1 f014.Kod from ies.T_F014_DENY_REASON f014 with(nolock) where f014.F014DenyReasonID = doubled.F014DenyReason), 53) AS 'S_OSN'
						,CONVERT(VARCHAR(10),doubled.createdate,126) as 'DATE_ACT'
						,doubled.Number as 'NUM_ACT'
						,doubled.Name AS 'S_COM'
						,1 as 'S_IST'
					FROM 
					(
						select aa.ExpertiseErrorID, aa.F014DenyReason, aa.FOMScode, aa.Name, sa.createdate, sa.number
							, ROW_NUMBER() OVER (PARTITION BY aa.ExpertiseErrorID, aa.FOMScode, aa.Name order by aa.FOMScode) as [ids] 
							, ROW_NUMBER() OVER (PARTITION BY t5.SchetSluchAccomplished order by aa.FOMScode) as [id], t5.SchetSluchAccomplished 
						FROM [IES].T_ACT_ITEM_ERROR t4
						JOIN [IES].T_SCHET_ACT_ITEM t5 ON t5.SchetActItemID = t4.SchetActItem
						JOIN ies.T_SCHET_ACT sa on t5.schetact = sa.schetactid 	
						cross apply(select top 1 t6.ExpertiseErrorID, t6.F014DenyReason, t6.FOMScode, t6.Name from [IES].T_EXPERTISE_ERROR t6 where t6.ExpertiseErrorID = t4.ExpertiseError)aa
						WHERE t5.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID AND t5.IsAddToAct = 1
						group by aa.ExpertiseErrorID, aa.F014DenyReason, aa.FOMScode, aa.Name, t5.SchetSluchAccomplished, sa.createdate, sa.number
					) doubled
					where doubled.Ids = 1
					FOR
					XML PATH('SANK'),
					TYPE
				)
				,(--sank
					SELECT
							t5.S_CODE AS 'S_CODE'
							,t5.S_SUM AS 'S_SUM'
							,t5.S_TIP AS 'S_TIP'
							,(select
									ss.SL_ID as 'SL_ID'
									from ies.T_SCHET_SLUCH ss
									where t5.SchetSluchAccomplished=ssa.SchetSluchAccomplishedID and ss.SchetSluchID = t5.SchetSluch
									FOR
									XML PATH(''),
									TYPE)
							,(select top 1 f014.kod from ies.t_f014_deny_reason f014 with(nolock) where f014.f014denyreasonid = t5.s_osn) as 'S_OSN'
							,CONVERT(VARCHAR(10),t5.DATE_ACT,126) as 'DATE_ACT'
							,t5.NUM_ACT as 'NUM_ACT'
							,(select
								ssse.F004Expert as 'CODE_EXP'
								FROM ies.T_SCHET_SLUCH_SANK_EXP ssse
								where ssse.SchetSluchSank = t5.SchetSluchSankID
							FOR
							XML PATH(''),
							TYPE
							)
							,t5.S_COM AS 'S_COM'
							,t5.S_IST AS 'S_IST'
						FROM [IES].T_SCHET_SLUCH_SANK t5
						WHERE t5.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID
						FOR
						XML PATH('SANK'),
						TYPE
				)
				,ssa.SANK_IT as 'SANK_IT'
			FROM [IES].[T_SCHET_SLUCH_ACCOMPLISHED] ssa
			where ssa.SchetZap = t2.SchetZapID and ssa.IsDelete = 0 
			and not exists(select * from ies.T_SCHET_ACT_ITEM ai where ai.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID and ai.IsAddToAct = 1)
			FOR
			XML PATH('Z_SL'),
			TYPE
		)
	
	FROM [IES].T_SCHET_ZAP t2
	JOIN [IES].T_SCHET s on (s.SchetID = t2.Schet)
	join [IES].[T_SCHET_SLUCH_ACCOMPLISHED] ssa on ssa.SchetZap = t2.SchetZapID
	WHERE t2.Schet = t1.SchetID and t2.IsDelete = 0
	and not exists(select * from ies.T_SCHET_ACT_ITEM ai where ai.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID and ai.IsAddToAct = 1)
	FOR
	XML PATH('ZAP'),
	TYPE
)
FROM [IES].T_SCHET t1
WHERE t1.SChetID = @SchetID
FOR XML PATH(''),
ROOT('ZL_LIST')
) as [xml]
,1 as [order]

union

select (case when left(@filename,1) in ('H', 'D') then 'L' + SUBSTRING(@filename, 2, len(@filename))  + '.xml' else 'L' + @filename + '.xml' end)  as [name],
'<?xml version="1.0" encoding="Windows-1251"?>'
+ (select 
--ZGLV
case when (@year >= 2020 or(@year=2019 and @month>=11)) then '3.2' else @version end AS 'ZGLV/VERSION' 
, CONVERT(VARCHAR(10), t1.DATA, 126) AS 'ZGLV/DATA'
, case when left(@filename,1) in ('H', 'D') then 'L' + SUBSTRING(@filename, 2, len(@filename))  + '.xml' else 'L' + @filename + '.xml' end AS 'ZGLV/FILENAME'
, t1.FILENAME AS 'ZGLV/FILENAME1'
--PERS
,( 
	SELECT DISTINCT
	    t2.ID_PAC AS 'ID_PAC'
	    ,t2.FAM AS 'FAM'
	    ,t2.IM AS 'IM'
		,t2.OT AS 'OT'
		,t2.W AS 'W'
		,CONVERT(VARCHAR(10), t2.DR	,126) AS 'DR'
		,cast(( 
					SELECT Split AS 'DOST'				   
					FROM [dbo].fn_Split(t2.DOST,',')					
					FOR
					XML PATH(''),
					TYPE
				) as varchar(max))
		,t2.TEL as 'TEL'
		,t2.FAM_P AS 'FAM_P'
		,t2.IM_P AS 'IM_P'
		,t2.OT_P AS 'OT_P'
		,t2.W_P AS 'W_P'
		,CONVERT(VARCHAR(10),t2.DR_P, 126) AS 'DR_P'
		,cast(( 
					SELECT Split AS 'DOST_P'				   
					FROM [dbo].fn_Split(t2.DOST_P,',')					
					FOR
					XML PATH(''),
					TYPE
				) as varchar(max))
		,t2.MR AS 'MR'
		,t2.DOCTYPE AS 'DOCTYPE'
		,t2.DOCSER AS 'DOCSER'
		,t2.DOCNUM AS 'DOCNUM'
		,CONVERT(VARCHAR(10),t2.DOCDATE, 126) as 'DOCDATE'
		,t2.DOCORG as 'DOCORG'
		,t2.SNILS AS 'SNILS'
		,t2.OKATOG AS 'OKATOG'
		,t2.OKATOP AS 'OKATOP'
		,t2.COMENTP AS 'COMENTP'  	
	FROM [IES].T_SCHET_ZAP t2 
	join [IES].[T_SCHET_SLUCH_ACCOMPLISHED] ssa on ssa.SchetZap = t2.SchetZapID
	WHERE t2.Schet = t1.SchetID and t2.IsDelete = 0
	and not exists(select * from ies.T_SCHET_ACT_ITEM ai where ai.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID and ai.IsAddToAct = 1)
	FOR
	XML PATH('PERS'),
	TYPE
)
FROM [IES].T_SCHET t1
WHERE t1.SChetID = @SchetID
FOR XML PATH(''),
ROOT('PERS_LIST')
) as [xml]
,2 as [order]
) tt
ORDER BY tt.[order]

end