drop function if exists sp_getschetomsxml;
drop procedure if exists sp_getschetomsxml;
create procedure sp_getschetomsxml
(
	"SchetID" uuid default null::uuid,
    inout "result" jsonb default null::jsonb
)
language plpgsql
as $procedure$

declare ver text;
declare fname text;
declare yr int;
declare mnt int;
declare type_ int;
declare items record;

begin

select s.version, s.filename, s.type_, s.month, s.year into ver, fname, type_, mnt, yr from ies.t_schet s where s.schetid = "SchetID" limit 1;

create temp table tempResult as 	
	select tt.*
		from
		( 
		
		with PERS as(
		select
			ID_PAC
			, FAM
			, IM
			, OT
			, W
			, DR::date as DR
			, (select DOST from unnest(regexp_split_to_array(DOST::text, '[,]'))) as DOST
			, TEL
			, FAM_P
			, IM_P
			, OT_P
			, W_P
			, DR_P::date as DR_P
			, (select DOST_P from unnest(regexp_split_to_array(DOST_P::text, '[,]'))) as DOST_P
			, MR
			, DOCTYPE
			, DOCSER
			, DOCNUM
			, DOCDATE
			, DOCORG
			, (case when zap.SNILS='' then null else zap.snils end) as SNILS
	        , OKATOG
	        , OKATOP
	        , max(COMENTP) as COMENTP
		from ies.t_schet_zap zap
		where zap.schet = "SchetID" 
		group by zap.id_pac, zap.FAM, zap.IM, zap.OT, zap.W, zap.DR, zap.FAM_P, zap.IM_P, zap.OT_P, zap.W_P, zap.DR_P, zap.MR
		,zap.DOCTYPE, zap.DOCSER, zap.DOCNUM, zap.DOCDATE, zap.DOCORG, zap.SNILS, zap.OKATOG, zap.OKATOP, zap.DOST, zap.DOST_P, zap.TEL) 
		
		select
			fname + '.xml' as fname,
			'<?xml version="1.0" encoding="Windows-1251"?>' ||
			xmlforest(--zl_list
       			 xmlconcat (
            		xmlforest(--zglv
                	(
		                select xmlconcat
		                (
		                     xmlforest(VERSION),
		                     xmlforest(DATA::date as DATA),
		                     xmlforest(fname),
		                     xmlforest (SD_Z as SD_Z)   
		                )
		                from ies.t_schet s
		                where s.schetid  = "SchetID" 
                	)
            as ZGLV),
            xmlforest(--schet
                (
                    select xmlconcat (
                          xmlforest(CODE)
                        , xmlforest(CODE_MO)
                        , xmlforest(YEAR)
                        , xmlforest(MONTH)
                        , xmlforest(NSCHET)
                        , xmlforest(DSCHET::date as DSCHET)
                        , xmlforest(PLAT)
                        , xmlforest(SUMMAV)
                        , xmlforest(COMENTS)
                        , xmlforest(SUMMAP)
                        , xmlforest(s.sank_mek as SANK_MEK)
                        , xmlforest(s.sank_mee as SANK_MEE)
                        , xmlforest(s.sank_ekmp as SANK_EKMP)
                        , xmlforest((select v016.iddt from ies.t_v016_dispt v016 where v016.v016id = s.disp limit 1) as DISP)
                    )
                    from ies.t_schet s
                    where s.schetid  = "SchetID" 
                )
            as SCHET),
            (--zap 
            select xmlagg(
                    xmlforest (
                        xmlconcat (
                          xmlforest(N_ZAP)
                        , xmlforest(PR_NOV)
                        , xmlforest(--pacient
                            xmlconcat (
                                  xmlforest(ID_PAC)
                                , xmlforest(VPOLIS)
                                , xmlforest(SPOLIS)
                                , xmlforest(NPOLIS)
                                , xmlforest(ST_OKATO)
                                , xmlforest(SMO)
                                , xmlforest(SMO_OGRN)
                                , xmlforest(SMO_OK)
                                , xmlforest(SMO_NAM)
                                , xmlforest(INV)
                                , xmlforest(MSE)
                                , xmlforest(NOVOR)
                                , xmlforest(VNOV_D)
                            )
                          as PACIENT)
                        , xmlforest(--zsl
                            (select
                            xmlconcat (
                                  xmlforest(IDCASE)
                                , xmlforest(USL_OK)
                                , xmlforest(VIDPOM)
                                , xmlforest(FOR_POM)
                                , xmlforest(NPR_MO)
                                , xmlforest(NPR_DATE::date as NPR_DATE)
                                , xmlforest(LPU)
                                , xmlforest(VBR)
                                , xmlforest(DATE_Z_1::date as DATE_Z_1)
                                , xmlforest(DATE_Z_2::date as DATE_Z_2)
                                , xmlforest(KD_Z)
                                , xmlforest(VNOV_M)
                                , xmlforest(RSLT)
                                , xmlforest(P_OTK)
                                , xmlforest(RSLT_D)
                                , xmlforest(ISHOD)
                                , xmlforest(OS_SLUCH)
                                , xmlforest(VB_P)
                                ,(--sl
                                    select xmlagg (
                                        xmlforest (
                                            xmlconcat (
                                                  xmlforest(SL_ID)
                                                , xmlforest(VID_HMP)
                                                , xmlforest(METOD_HMP)
                                                , xmlforest(LPU_1)
                                                , xmlforest(PODR)
                                                , xmlforest(PROFIL)
                                                , xmlforest((select v020.idk_pr from ies.t_v020_bed_profile v020 where sl.profil_k = v020.v020bedprofileid limit 1) as PROFIL_K)
                                                , xmlforest(DET)
                                                , xmlforest(TAL_D::date as TAL_D)
                                                , xmlforest(TAL_NUM)
                                                , xmlforest(TAL_P)
                                                , xmlforest((select v025.idpc from ies.t_v025_kpc v025 where v025.v025kpcid = sl.p_cel limit 1) as P_CEL)
                                                , xmlforest(NHISTORY)
                                                , xmlforest(P_PER)
                                                , xmlforest(DATE_1::date as DATE_1)
                                                , xmlforest(DATE_2::date as DATE_2)
                                                , xmlforest(KD)
                                                , xmlforest(WEI)
                                                , xmlforest(DS0)
                                                , xmlforest(DS1)
                                                , xmlforest(DS1_PR)
                                                ,(select xmlagg(xmlforest(ds.mkb as DS2)) from ies.t_schet_sluch_ds ds where ds.schetsluch = sl.schetsluchid and ds.mkbtype = 0 and ds.pr_ds2_n is null)
                                                ,(select xmlagg(xmlforest(ds.mkb as DS3)) from ies.t_schet_sluch_ds ds where ds.schetsluch = sl.schetsluchid and ds.mkbtype = 1)
                                                , xmlforest((select v027.idcz from ies.t_v027_c_zab v027 where v027.v027czabid = sl.c_zab limit 1) as C_ZAB)
                                                , xmlforest(DS_ONK)
                                                , xmlforest(PR_D_N)
                                                , xmlforest(DN)                             
                                                , xmlforest(CODE_MES1)
                                                , xmlforest(CODE_MES2)
                                                ,(--ds2_n
                                                    select xmlagg(
                                                        xmlforest (
                                                            xmlconcat (
                                                                  xmlforest(mkb as DS2)
                                                                , xmlforest(DS2_PR)
                                                                , xmlforest(PR_DS2_N)
                                                            )
                                                        as DS2_N)
                                                    )
                                                    from ies.t_schet_sluch_ds ds
                                                    where ds.schetsluch  = sl.schetsluchid and ds.mkbtype = 0 and ds.pr_ds2_n is not null)
                                                ,(--naz
                                                    select xmlagg(
                                                        xmlforest (
                                                            xmlconcat (
                                                                  xmlforest(NAZ_N)
                                                                , xmlforest(NAZR as NAZ_R)
                                                                , xmlforest(NAZ_IDDOKT)
                                                                , xmlforest(NAZ_SP)
                                                                , xmlforest((select v029.idmet from ies.t_v029_met_issl v029 where v029.v029metisslid = naz.naz_v limit 1) as NAZ_V)
                                                                , xmlforest(NAZ_PMP)
                                                                , xmlforest((select v020.idk_pr from ies.t_v020_bed_profile v020 where naz.naz_pk = v020.v020bedprofileid limit 1) as NAZ_PK)
                                                            )
                                                        as NAZ)
                                                    )
                                                    from ies.t_schet_sluch_naz naz
                                                    where naz.schetsluch  = sl.schetsluchid)
                                                 ,(--napr
                                                    select xmlagg(
                                                        xmlforest (
                                                            xmlconcat (
                                                                  xmlforest(NAPR_DATE::date as NAPR_DATE)
                                                                , xmlforest(NAPR_MO)
                                                                , xmlforest(NAPR_V)
                                                                , xmlforest(MET_ISSL)
                                                                , xmlforest(NAPR_USL)
                                                            )
                                                        as NAPR)
                                                    )
                                                    from ies.t_schet_usl_napr napr
                                                    where napr.schetsluch  = sl.schetsluchid)
                                                 ,(--cons
                                                    select xmlagg(
                                                        xmlforest (
                                                            xmlconcat (
                                                                  xmlforest((select n019.id_cons from ies.t_n019_onk_cons n019 where n019.n019onkconsid = cons.n019onkcons limit 1) as PR_CONS)
                                                                , xmlforest(DT_CONS::date as DT_CONS)
                                                            )
                                                        as CONS)
                                                    )
                                                    from ies.t_schet_sluch_cons cons
                                                    where cons.schetsluch  = sl.schetsluchid)
                                                , xmlforest (--onk_sl
                                                    (select xmlconcat (
                                                          xmlforest((select n018.id_reas from ies.t_n018_onk_reas n018 where n018.n018onkreasid = onksl.ds1_t limit 1) as DS1_T)
                                                        , xmlforest((select n002.id_st from ies.t_n002_stadium n002 where n002.n002stadiumid = onksl.stad limit 1) as STAD)
                                                        , xmlforest((select n003.id_t from ies.t_n003_tumor n003 where n003.n003tumorid = onksl.onk_t limit 1) as ONK_T)
                                                        , xmlforest((select n004.id_n from ies.t_n004_nodus n004 where n004.n004nodusid = onksl.onk_n limit 1) as ONK_N)
                                                        , xmlforest((select n005.id_m from ies.t_n005_metastasis n005 where n005.n005metastasisid = onksl.onk_m limit 1) as ONK_M)
                                                        , xmlforest(MTSTZ)
                                                        , xmlforest(SOD)
                                                        , xmlforest(K_FR)
                                                        , xmlforest(WEI)
                                                        , xmlforest(HEI)
                                                        , xmlforest(BSA)
                                                        ,(--b_diag
	                                                    	select xmlagg(
	                                                        xmlforest (
	                                                            xmlconcat (
	                                                                  xmlforest(DIAG_DATE::date as DIAG_DATE)
	                                                                , xmlforest(DIAG_TIP)
	                                                                , xmlforest(DIAG_CODE)
	                                                                , xmlforest(DIAG_RSLT)
	                                                                , xmlforest(REC_RSLT)
	                                                            )
	                                                        	as B_DIAG)
		                                                    )
		                                                    from ies.t_schet_sluch_onk_diag diag
		                                                    where onksl.schetsluchonkid = diag.schetsluchonk)
		                                                 ,(--b_prot
	                                                    	select xmlagg(
	                                                        xmlforest (
	                                                            xmlconcat (
	                                                                  xmlforest((select n001.id_prot from ies.t_n001_prot n001 where n001.n001protid = prot.prot limit 1) as PROT)
	                                                                , xmlforest(D_PROT::date as D_PROT)
	                                                            )
	                                                        	as B_PROT)
		                                                    )
		                                                    from ies.t_schet_sluch_onk_prot prot
		                                                    where onksl.schetsluchonkid = prot.schetsluchonk)
		                                                   ,(--onk_usl
		                                                    	select xmlagg(
		                                                        xmlforest (
		                                                            xmlconcat (
		                                                                  xmlforest((select n013.id_tlech from ies.t_n013_treat_type n013 where n013.n013treattypeid = onkusl.usl_tip limit 1) as USL_TIP)
		                                                                , xmlforest((select n014.id_thir from ies.t_n014_surg_treat n014 where n014.n014surgtreatid = onkusl.hir_tip limit 1) as HIR_TIP)
		                                                                , xmlforest((select n015.id_tlek_l from ies.t_n015_drug_therapy_lines n015 where n015.n015drugtherapylinesid = onkusl.lek_tip_l limit 1) as LEK_TIP_L)
		                                                                , xmlforest((select n016.id_tlek_v from ies.t_n016_drug_therapy_cycles n016 where n016.n016drugtherapycyclesid = onkusl.lek_tip_v limit 1) as LEK_TIP_V)
		                                                                ,(--lek_pr
					                                                    	select xmlagg(
					                                                        xmlforest (
					                                                            xmlconcat (
					                                                                  xmlforest((select n020.id_lekp from ies.t_n020_onk_lekp n020 where lekpr.regnum = n020.n020onklekpid limit 1) as REGNUM)
					                                                                 ,xmlforest((select v024.iddkk from ies.t_v024_dop_kr v024 where lekpr.v024dopkr = v024.v024dopkrid limit 1) as CODE_SH)
					                                                                 ,(select xmlagg(xmlforest(dj.date_inj::date as DATE_INJ)) from ies.t_schet_usl_onk_lek_pr_date dj where dj.schetuslonklekpr = lekpr.schetuslonklekprid)
					                                                            )
					                                                        	as LEK_PR)
						                                                    )
						                                                    from ies.t_schet_usl_onk_lek_pr lekpr
						                                                    where lekpr.schetuslonk = onkusl.schetuslonkid)
		                                                                , xmlforest(PPTR)
		                                                                , xmlforest((select n017.id_tluch from ies.t_n017_radiation_therapy_types n017 where n017.n017radiationtherapytypesid = onkusl.luch_tip and n017.datebeg<=zsl.date_z_2 and (n017.dateend is null or n017.dateend>=zsl.date_z_2) limit 1) as LUCH_TIP)
		                                                            )
		                                                        	as ONK_USL)
			                                                    )
			                                                    from ies.t_schet_usl_onk onkusl
			                                                    where onksl.schetsluchonkid = onkusl.schetsluchonk)
                                                    )
                                                    from ies.t_schet_sluch_onk onksl
                                                    where onksl.schetsluch = sl.schetsluchid)
                                                as ONK_SL)
                                                , xmlforest (--ksg_kpg
                                                    (select xmlconcat (
                                                          xmlforest(N_KSG)
                                                        , xmlforest(VER_KSG)
                                                        , xmlforest(KSG_PG)
                                                        , xmlforest((select v026.k_kpg from ies.t_v026_kpg v026 where v026.v026kpgid = ksg.n_kpg limit 1) as N_KPG)
                                                        , xmlforest(KOEF_Z)
                                                        , xmlforest(KOEF_UP)
                                                        , xmlforest(BZTSZ)
                                                        , xmlforest(KOEF_D)
                                                        , xmlforest(KOEF_U)
                                                        ,(select 
                                                        	xmlagg(xmlforest(
	                                                        	(select v024.iddkk from ies.t_v024_dop_kr v024 where cr.v024dopkr = v024.v024dopkrid limit 1) as CRIT
                                                        	)) 
                                                        from ies.t_ksg_crit cr where cr.ksg = ksg.ksgid)
                                                        , xmlforest((select IDDKK from ies.T_V024_DOP_KR where V024DopKrID = ksg.DKK1 limit 1) as DKK1)
                                                        , xmlforest((select IDDKK from ies.T_V024_DOP_KR where V024DopKrID = ksg.DKK2 limit 1) as DKK2)
                                                        , xmlforest(SL_K)
                                                        , xmlforest(IT_SL)
                                                        ,(--sl_koef
	                                                    	select xmlagg(
		                                                        xmlforest (
		                                                            xmlconcat (
		                                                                  xmlforest(KOEF_TYPE as IDSL)
		                                                                , xmlforest(KOEF as Z_SL)
		                                                            )
		                                                        	as SL_KOEF)
			                                                    )
			                                                    from ies.t_kslp kslp
			                                                    where kslp.ksg = ksg.ksgid)
                                                    )
                                                    from ies.t_ksg ksg
                                                    where ksg.schetsluch = sl.schetsluchid)
                                                as KSG_KPG)
                                                , xmlforest((select v027.idcz from ies.t_v027_c_zab v027 where v027.v027czabid = sl.c_zab limit 1)as C_ZAB)
                                                , xmlforest(REAB)
                                                , xmlforest((select v021.code_spec from ies.t_v021_med_spec v021 where v021.idspec = sl.prvs3 limit 1) as PRVS)                                                
                                                , xmlforest(VERS_SPEC)
                                                , xmlforest(IDDOKT)
                                                , xmlforest(ED_COL)
                                                , xmlforest(TARIF)
                                                , xmlforest(SUMV as SUM_M)
                                                ,(--LEK_PR
                                                	select xmlagg(
                                                	 xmlforest (
                                                            xmlconcat (
                                                                  xmlforest(DATA_INJ::date as DATA_INJ) 
                                                                  ,xmlforest((select v024.ScheDrugGrCd from ies.T_V032_DRUG_GR_REG v024 where v024.V032DrugGrRegID = md.CODE_SH limit 1) as CODE_SH)
                                                                  ,xmlforest((select n020.ID_LEKP from ies.T_N020_ONK_LEKP n020 where n020.N020OnkLekpID = md.REGNUM limit 1) as REGNUM)
                                                                  ,xmlforest(COD_MARK)
	                                                          	  ,xmlforest (
	                                                    				xmlconcat (
	                                                    				xmlforest((select v034.UnitCode from IES.T_V034_UNIT_TYPE v034 where md.ED_IZM = v034.V034UnitTypeID limit 1)as ED_IZM)
	                                                    				,xmlforest((select v034.UnitCode from IES.T_V034_UNIT_TYPE v034 where md.METHOD_INJ = v034.V034UnitTypeID limit 1)as METHOD_INJ)
	                                                    				)
	                                                    				as LEK_DOSE)
                                                                  	
                                                                  ) 
                                                              as LEK_PR)
                                                	)
                                                	from ies.t_schet_sluch_lek_pr md
                                                	where md.schetsluch = sl.schetsluchid
                                                )
                                                ,(--usl
                                                    select xmlagg(
                                                        xmlforest (
                                                            xmlconcat (
                                                                  xmlforest(IDSERV)
                                                                , xmlforest(LPU)
                                                                , xmlforest(LPU_1)
                                                                , xmlforest(PODR)
                                                                , xmlforest(PROFIL)
                                                                , xmlforest(VID_VME)
                                                                , xmlforest(DET)
                                                                , xmlforest(DATE_IN::date as DATE_IN)
                                                                , xmlforest(DATE_OUT::date as DATE_OUT)
                                                                , xmlforest(P_OTK)
                                                                , xmlforest(DS)
                                                                , xmlforest(CODE_USL)
                                                                , xmlforest(KOL_USL)
                                                                , xmlforest(TARIF)
                                                                , xmlforest(SUMV_USL)
                                                                , (--MED_DEV
                                                                	select xmlagg(
				                                                        xmlforest (
				                                                            xmlconcat (
				                                                            	xmlforest(CODE_MEDDEV)
				                                                            	,xmlforest(NUMBER_SER)
				                                                            )
				                                                            as MED_DEV)
			                                                            )
			                                                            from ies.t_schet_usl_med_dev md
			                                                            where md.schetusl = usl.schetuslid
                                                            	)
                                                            	, (--MR_USL_N
                                                                	select xmlagg(
				                                                        xmlforest (
				                                                            xmlconcat (
				                                                            	xmlforest(MR_N)
				                                                            	,xmlforest((select v021.code_spec from ies.t_v021_med_spec v021 where v021.idspec = mr.prvs limit 1) as PRVS)
				                                                            	,xmlforest(CODE_MD)
				                                                            )
				                                                            as MR_USL_N)
			                                                            )
			                                                            from ies.T_MR_USL_N mr
			                                                            where mr.schetusl = usl.schetuslid
                                                            	)
                                                                , xmlforest((select v021.code_spec from ies.t_v021_med_spec v021 where v021.idspec=usl.prvs3 limit 1) as PRVS)
                                                                , xmlforest(CODE_MD)
                                                                -- , (--NAPR
                                                                	-- select xmlagg(
				                                                        -- xmlforest (
				                                                            -- xmlconcat (
				                                                            	-- xmlforest(NAPR_DATE)
				                                                            	-- ,xmlforest(NAPR_V)
				                                                            	-- ,xmlforest(MET_ISSL)
				                                                            	-- ,xmlforest(NAPR_USL)
				                                                            -- )
				                                                            -- as MR_USL_N)
			                                                            -- )
			                                                            -- from ies.t_schet_usl_napr sun
			                                                            -- where sun.schetusl = usl.schetuslid
                                                            	-- )
                                                            )
                                                        as USL)
                                                    )
                                                    from ies.t_schet_usl usl
                                                    where usl.schetsluch  = sl.schetsluchid and usl.isdelete = false
                                                )
                                                , xmlforest(COMENTSL)    
                                            )
                                        as SL)
                                    )
                                    from ies.t_schet_sluch sl
                                    where sl.schetsluchaccomplished = zsl.schetsluchaccomplishedid and sl.isdelete = false
                                )
                                , xmlforest(IDSP)
                                , xmlforest(SUMV)
                                , xmlforest(OPLATA)
                                , xmlforest(SUMP)
                                ,(--sank
	                                select xmlagg(
	                                    xmlforest (
	                                        xmlconcat (
	                                              xmlforest(S_CODE)
	                                            , xmlforest(S_SUM)
	                                            , xmlforest((select f006.idvid from ies.t_f006_control_type f006 where f006.f006controltypeid = sank.s_tip_f006 limit 1) as S_TIP)
	                                            , (select xmlagg(xmlforest(sl.sl_id as SL_ID)) from ies.t_schet_sluch sl where zsl.schetsluchaccomplishedid = sank.schetsluchaccomplished and sank.schetsluch = sl.schetsluchid)
	                                            , xmlforest((select f014.kod from ies.t_f014_deny_reason f014 where f014.f014denyreasonid = sank.s_osn limit 1) as S_OSN)
	                                            , xmlforest(DATE_ACT::date as DATE_ACT)
	                                            , xmlforest(NUM_ACT)
	                                            , (select xmlagg(xmlforest(ex.f004expert as CODE_EXP)) from ies.t_schet_sluch_sank_exp ex where ex.schetsluchsank = sank.schetsluchsankid)
	                                            , xmlforest(S_COM)
	                                            , xmlforest(S_IST)
	                                        )
	                                    as SANK)
	                                )
	                                from ies.t_schet_sluch_sank sank
	                                where sank.schetsluchaccomplished  = zsl.schetsluchaccomplishedid and sank.isdelete = false
                            	)
                            	,(--sank(schet_act_item)
	                                select xmlagg(
	                                    xmlforest (
	                                        xmlconcat (
	                                              xmlforest(dd.expertiseerrorid as S_CODE)
	                                            , xmlforest(SUMV as S_SUM)
	                                            , xmlforest(1 as S_TIP)
	                                            , (select xmlagg(xmlforest(sl.sl_id as SL_ID)) from ies.t_schet_sluch sl where zsl.schetsluchaccomplishedid = sl.schetsluchaccomplished)
	                                            , xmlforest((select f014.kod from ies.t_f014_deny_reason f014 where f014.f014denyreasonid = dd.f014denyreason limit 1) as S_OSN)
	                                            , xmlforest(dd.createdate::date as DATE_ACT)
	                                            , xmlforest(dd.number as NUM_ACT)
	                                            , xmlforest(dd.name as S_COM)
	                                            , xmlforest(1 as S_IST)
	                                        )
	                                    as SANK)
		                                )
		                                from(
											select ee.expertiseerrorid, ee.fomscode, ee.name, sa.createdate, sa.number, ee.f014denyreason
											, row_number() over (partition by ee.expertiseerrorid, ee.fomscode, ee.name order by ee.fomscode) as id --duplicates
											, row_number() over (partition by sai.schetsluchaccomplished order by ee.fomscode) as zsl, sai.schetsluchaccomplished
											from ies.t_act_item_error aie
											join ies.t_schet_act_item sai on sai.schetactitemid = aie.schetactitem 	
											join ies.t_schet_act sa on sai.schetact = sa.schetactid 
											join ies.t_expertise_error ee on ee.expertiseerrorid = aie.expertiseerror 
											where sai.schetsluchaccomplished = zsl.schetsluchaccomplishedid and sai.isaddtoact = true
											group by ee.expertiseerrorid, ee.fomscode, ee.name, sai.schetsluchaccomplished, sa.createdate, sa.number
										) dd
										where dd.id = 1
                            	)
                                , xmlforest(SANK_IT as SANK_IT)
                            )
                            from ies.t_schet_sluch_accomplished zsl
                            where zsl.schetzap  = zap.schetzapid and zsl.isdelete = false
                            )
                        as Z_SL)
                        )                       
                    as ZAP)
                )
                from ies.t_schet_zap zap
                where zap.schet = "SchetID" and zap.isdelete = false
            
        ))
            as ZL_LIST)::text as xml,
			1 as order
			
            union
			
    select (select 'L' + case when left(fname,1) in ('H,''D') then substring(fname, 2, character_length(fname)) else fname end + '.xml')  as fname,
	'<?xml version="1.0" encoding="Windows-1251"?>' ||
    xmlforest(--pers_list
        xmlconcat (
            xmlforest(--zglv
                (
	                select xmlconcat (
	                      xmlforest(VERSION as VERSION)
	                    , xmlforest(DATA::date as DATA)
	                    , xmlforest((select 'L' + case when left(fname,1) in ('H,''D') then substring(fname, 2, character_length(fname)) else fname end) as FILENAME)
	                    , xmlforest(fname as FILENAME1)
	                )
	                from ies.t_schet s
	                where s.schetid  = "SchetID"      
                )
            as ZGLV)
            ,(--pers
                select xmlagg(
                    xmlforest (
                        xmlconcat (
                          xmlforest(ID_PAC)
                        , xmlforest(FAM)
                        , xmlforest(IM)
                        , xmlforest(OT)
                        , xmlforest(W)
                        , xmlforest(DR::date as DR)
                        , (select xmlagg(xmlforest(DOST)) from unnest(regexp_split_to_array(DOST::text, '[,]')) as DOST)
                        , xmlforest(TEL)
                        , xmlforest(FAM_P)
                        , xmlforest(IM_P)
                        , xmlforest(OT_P)
                        , xmlforest(W_P)
                        , xmlforest(DR_P::date as DR_P)
                        , (select xmlagg(xmlforest(DOST_P)) from unnest(regexp_split_to_array(DOST_P::text, '[,]')) as DOST_P)
                        , xmlforest(MR)
                        , xmlforest(DOCTYPE)
                        , xmlforest(DOCSER)
                        , xmlforest(DOCNUM)
						, xmlforest(DOCDATE)
						, xmlforest(DOCORG)
                        , xmlforest(SNILS)
                        , xmlforest(OKATOG)
                        , xmlforest(OKATOP)
                        , xmlforest(COMENTP as COMENTP)
                        )                       
                    as PERS)
                )
                from PERS
            )
        )
    as PERS_LIST) ::text as xml,
	2 as order
            
		) tt 
		order by tt.order;
	
	FOR items IN SELECT * FROM tempResult LOOP
    RAISE NOTICE 'xml: % ,
	FileName: %', quote_ident(items.xml), quote_ident(items.fname);
	END LOOP;

	SELECT to_jsonb(array_agg(t)) FROM tempResult as t into "result";

	drop table if exists tempResult;
end;
$procedure$;