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 _version text;
declare _filename text;
declare _year int;
declare _month int;
declare _type int;
declare _items record;

begin

select s.version, upper(s.filename), s.type_, s.month, s.year into _version, _filename, _type, _month, _year from ies.t_schet s where s.schetid = "SchetID" limit 1;

create temp table tempresult as 	
	select tt.*
		from
		( 
		
		with pers as(
		select
			  sz.id_pac
			, sz.fam
			, sz.im
			, sz.ot
			, sz.w
			, sz.dr::date as dr
			, sz.dost
			, sz.tel
			, sz.fam_p
			, sz.im_p
			, sz.ot_p
			, sz.w_p
			, sz.dr_p::date as dr_p
			, sz.dost_p
			, sz.mr
			, sz.doctype
			, sz.docser
			, sz.docnum
			, sz.docdate
			, sz.docorg
			, sz.snils
	        , sz.okatog
	        , sz.okatop
	        , max(sz.comentp) as comentp
		from ies.t_schet_zap sz
		where sz.schet = "SchetID" 
		group by sz.id_pac, sz.fam, sz.im, sz.ot, sz.w, sz.dr, sz.fam_p, sz.im_p, sz.ot_p, sz.w_p, sz.dr_p, sz.mr
		,sz.doctype, sz.docser, sz.docnum, sz.docdate, sz.docorg, sz.snils, sz.okatog, sz.okatop, sz.dost, sz.dost_p, sz.tel) 
		
		select
			_filename + '.XML' as fname,
			'<?xml version="1.0" encoding="windows-1251"?>' ||
			upper(xmlforest(--zl_list
       			 xmlconcat (
            		xmlforest(--zglv
                	(
		                select xmlconcat
		                (
		                     xmlforest(sc.version),
		                     xmlforest(sc.data::date as data),
		                     xmlforest(_filename as filename),
		                     xmlforest(sc.sd_z as sd_z)   
		                )
		                from ies.t_schet sc
		                where sc.schetid = "SchetID" 
                	)
            as zglv),
            xmlforest(--schet
                (
                    select xmlconcat (
                          xmlforest(sc2.code)
                        , xmlforest(sc2.code_mo)
                        , xmlforest(sc2.year)
                        , xmlforest(sc2.month)
                        , xmlforest(sc2.nschet)
                        , xmlforest(sc2.dschet::date as dschet)
                        , xmlforest(sc2.plat)
                        , xmlforest(sc2.summav)
                        , xmlforest(sc2.coments)
                        , xmlforest(sc2.summap)
                        , xmlforest(sc2.sank_mek)
                        , xmlforest(sc2.sank_mee)
                        , xmlforest(sc2.sank_ekmp)
                        , xmlforest((select v016.iddt from ies.t_v016_dispt v016 where v016.v016id = sc2.disp limit 1) as disp)
                    )
                    from ies.t_schet sc2
                    where sc2.schetid = "SchetID" 
                )
            as schet),
            (--zap 
            select xmlagg(
                    xmlforest (
                        xmlconcat (
                          xmlforest(zap.n_zap)
                        , xmlforest(zap.pr_nov::int as pr_nov)
                        , xmlforest(--pacient
                            xmlconcat (
                                  xmlforest(zap.id_pac)
                                , xmlforest(zap.vpolis)
                                , xmlforest(zap.spolis)
                                , xmlforest(zap.npolis)
                                , xmlforest(zap.st_okato)
                                , xmlforest(zap.smo)
                                , xmlforest(zap.smo_ogrn)
                                , xmlforest(zap.smo_ok)
                                , xmlforest(zap.smo_nam)
                                , xmlforest(zap.inv)
                                , xmlforest(zap.mse)
                                , xmlforest(zap.novor)
                                , xmlforest(zap.vnov_d)
                            )
                          as pacient)
                        , xmlforest(--zsl
                            (select
                            xmlconcat (
                                  xmlforest(zsl.idcase)
                                , xmlforest(zsl.usl_ok)
                                , xmlforest(zsl.vidpom)
                                , xmlforest(zsl.for_pom)
                                , xmlforest(zsl.npr_mo)
                                , xmlforest(zsl.npr_date::date as npr_date)
                                , xmlforest(zsl.lpu)
                                , xmlforest(zsl.vbr::int as vbr)
                                , xmlforest(zsl.date_z_1::date as date_z_1)
                                , xmlforest(zsl.date_z_2::date as date_z_2)
                                , xmlforest(zsl.kd_z)
                                , xmlforest(zsl.vnov_m)
                                , xmlforest(zsl.rslt)
                                , xmlforest(zsl.p_otk::int as p_otk)
                                , xmlforest(zsl.rslt_d)
                                , xmlforest(zsl.ishod)
                                , xmlforest(zsl.os_sluch)
                                , xmlforest(zsl.vb_p)
                                ,(--sl
                                    select xmlagg (
                                        xmlforest (
                                            xmlconcat (
                                                  xmlforest(sl.sl_id)
                                                , xmlforest(sl.vid_hmp)
												 , xmlforest((select v019.idhm from ies.t_v019_vmp_method v019 where sl.metod_hmp = v019.v019vmpmethodid limit 1) as method_hmp)
                                                , xmlforest(sl.lpu_1)
                                                , xmlforest(sl.podr)
                                                , xmlforest(sl.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(sl.det)
                                                , xmlforest(sl.tal_d::date as tal_d)
                                                , xmlforest(sl.tal_num)
                                                , xmlforest(sl.tal_p::date as tal_p)
                                                , xmlforest((select v025.idpc from ies.t_v025_kpc v025 where v025.v025kpcid = sl.p_cel limit 1) as p_cel)
                                                , xmlforest(sl.nhistory)
                                                , xmlforest(sl.p_per)
                                                , xmlforest(sl.date_1::date as date_1)
                                                , xmlforest(sl.date_2::date as date_2)
                                                , xmlforest(sl.kd)
                                                , xmlforest(cast(sl.wei as decimal(4,1)) as wei)
                                                , xmlforest(sl.ds0)
                                                , xmlforest(sl.ds1)
                                                , xmlforest(sl.ds1_pr::int as 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(sl.ds_onk)
                                                , xmlforest(sl.pr_d_n)
                                                , xmlforest(sl.dn)                             
                                                , xmlforest(sl.code_mes1)
                                                , xmlforest(sl.code_mes2)
                                                ,(--ds2_n
                                                    select xmlagg(
                                                        xmlforest (
                                                            xmlconcat (
                                                                  xmlforest(ds.mkb as ds2)
                                                                , xmlforest(ds.ds2_pr::int as ds2_pr)
                                                                , xmlforest(ds.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.naz_n)
                                                                , xmlforest(naz.nazr as naz_r)
																, xmlforest(naz.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.naz_usl)
																, xmlforest(naz.napr_date::date as napr_date)
                                                                , xmlforest(naz.napr_mo)
                                                                , 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.napr_date::date as napr_date)
                                                                , xmlforest(napr.napr_mo)
                                                                , xmlforest(napr.napr_v)
                                                                , xmlforest(napr.met_issl)
                                                                , xmlforest(napr.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(cons.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(onksl.mtstz)
                                                        , xmlforest(onksl.sod)
                                                        , xmlforest(onksl.k_fr)
                                                        , xmlforest(cast(onksl.wei as decimal(4,1)) as wei)
                                                        , xmlforest(onksl.hei)
                                                        , xmlforest(onksl.bsa)
                                                        ,(--b_diag
	                                                    	select xmlagg(
	                                                        xmlforest (
	                                                            xmlconcat (
	                                                                  xmlforest(diag.diag_date::date as diag_date)
	                                                                , xmlforest(diag.diag_tip)
	                                                                , xmlforest(diag.diag_code)
	                                                                , xmlforest(diag.diag_rslt)
	                                                                , xmlforest(diag.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(prot.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 n021.id_lekp_ext from ies.t_n021_onk_lpsh n021 where lekpr.regnum_dop = n021.n021onklpshid limit 1) as regnum_dop)
					                                                                 ,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)
																					 ,(--inj
																						select xmlagg(
																						xmlforest (
																							xmlconcat (
																								  xmlforest(inj.date_inj::date as date_inj)
																								 ,xmlforest(inj.kv_inj)
																								 ,xmlforest(inj.kiz_inj)
																								 ,xmlforest(inj.s_inj)
																								 ,xmlforest(inj.sv_inj)
																								 ,xmlforest(inj.siz_inj)
																								 ,xmlforest(inj.red_inj)
																							)
																							as inj)
																						)
																						from ies.t_schet_usl_onk_lek_pr_inj inj
																						where lekpr.schetuslonklekprid = inj.schetuslonklekpr)
					                                                            )
					                                                        	as lek_pr)
						                                                    )
						                                                    from ies.t_schet_usl_onk_lek_pr lekpr
						                                                    where lekpr.schetuslonk = onkusl.schetuslonkid)
		                                                                , xmlforest(onkusl.pptr)
		                                                                , xmlforest((select n017.id_tluch from ies.t_n017_radiation_therapy_types n017 where n017.n017radiationtherapytypesid = onkusl.luch_tip 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(ksg.n_ksg)
                                                        , xmlforest(ksg.ver_ksg)
                                                        , xmlforest(ksg.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(ksg.koef_z)
                                                        , xmlforest(ksg.koef_up)
                                                        , xmlforest(ksg.bztsz)
                                                        , xmlforest(ksg.koef_d)
                                                        , xmlforest(ksg.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 v024.iddkk from ies.t_v024_dop_kr v024 where v024.v024dopkrid = ksg.dkk1 limit 1) as dkk1)
                                                        , xmlforest((select v024.iddkk from ies.t_v024_dop_kr v024 where v024.v024dopkrid = ksg.dkk2 limit 1) as dkk2)
                                                        , xmlforest(ksg.sl_k)
                                                        , xmlforest(ksg.it_sl)
                                                        ,(--sl_koef
	                                                    	select xmlagg(
		                                                        xmlforest (
		                                                            xmlconcat (
		                                                                  xmlforest(kslp.koef_type as idsl)
		                                                                , xmlforest(kslp.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(sl.reab)
                                                , xmlforest(coalesce((select v021.code_spec from ies.t_v021_med_spec v021 where v021.idspec = sl.prvs3 limit 1),sl.prvs3) as prvs)                                                
                                                , xmlforest(sl.vers_spec)
                                                , xmlforest(sl.iddokt)
                                                , xmlforest(sl.ed_col)
                                                , xmlforest(sl.tarif)
                                                , xmlforest(sl.sumv as sum_m)
                                                ,(--lek_pr
                                                	select xmlagg(
                                                	 xmlforest (
                                                            xmlconcat (
                                                                  xmlforest(lpr.data_inj::date as data_inj) 
                                                                  ,xmlforest((select v024.schedruggrcd from ies.t_v032_drug_gr_reg v024 where v024.v032druggrregid = lpr.code_sh limit 1) as code_sh)
                                                                  ,xmlforest((select n020.id_lekp from ies.t_n020_onk_lekp n020 where n020.n020onklekpid = lpr.regnum limit 1) as regnum)
                                                                  ,xmlforest(lpr.cod_mark)
	                                                          	   ,xmlforest (
	                                                    				xmlconcat (
	                                                    				 xmlforest((select v034.unitcode from ies.t_v034_unit_type v034 where lpr.ed_izm = v034.v034unittypeid limit 1) as ed_izm)
																		,xmlforest(lpr.dose_inj)
	                                                    				,xmlforest((select v034.unitcode from ies.t_v034_unit_type v034 where lpr.method_inj = v034.v034unittypeid limit 1) as method_inj)
																		,xmlforest(lpr.col_inj)
	                                                    				)
	                                                    				as lek_dose)
                                                                  	
                                                                  ) 
                                                              as lek_pr)
                                                	)
                                                	from ies.t_schet_sluch_lek_pr lpr
                                                	where lpr.schetsluch = sl.schetsluchid
                                                )
                                                ,(--usl
                                                    select xmlagg(
                                                        xmlforest (
                                                            xmlconcat (
                                                                  xmlforest(usl.idserv)
                                                                , xmlforest(usl.lpu)
                                                                , xmlforest(usl.lpu_1)
                                                                , xmlforest(usl.podr)
                                                                , xmlforest(usl.profil)
                                                                , xmlforest(usl.vid_vme)
                                                                , xmlforest(usl.det)
                                                                , xmlforest(usl.date_in::date as date_in)
                                                                , xmlforest(usl.date_out::date as date_out)
                                                                , xmlforest(usl.p_otk::int as p_otk)
                                                                , xmlforest(usl.ds)
                                                                , xmlforest(usl.code_usl)
                                                                , xmlforest(usl.kol_usl)
                                                                , xmlforest(usl.tarif)
                                                                , xmlforest(usl.sumv_usl)
																, xmlforest(coalesce((select v021.code_spec from ies.t_v021_med_spec v021 where v021.idspec = usl.prvs3 limit 1),usl.prvs3) as prvs)  
                                                                , xmlforest(usl.code_md)
                                                                , (--med_dev
                                                                	select xmlagg(
				                                                        xmlforest (
				                                                            xmlconcat (
				                                                            	 xmlforest(md.code_meddev)
				                                                            	,xmlforest(md.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.mr_n)
				                                                            	,xmlforest((select v021.code_spec from ies.t_v021_med_spec v021 where v021.idspec = mr.prvs limit 1) as prvs)
				                                                            	,xmlforest(mr.code_md)
				                                                            )
				                                                            as mr_usl_n)
			                                                            )
			                                                            from ies.t_mr_usl_n mr
			                                                            where mr.schetusl = usl.schetuslid
                                                            	)
																, xmlforest(npl)
																, xmlforest(comentu)
                                                            )
                                                        as usl)
                                                    )
                                                    from ies.t_schet_usl usl
                                                    where usl.schetsluch  = sl.schetsluchid and usl.isdelete = false
                                                )
                                                , xmlforest(sl.comentsl)    
                                            )
                                        as sl)
                                    )
                                    from ies.t_schet_sluch sl
                                    where sl.schetsluchaccomplished = zsl.schetsluchaccomplishedid and sl.isdelete = false
                                )
                                , xmlforest(zsl.idsp)
                                , xmlforest(zsl.sumv)
                                , xmlforest(zsl.oplata)
                                , xmlforest(zsl.sump)
                                ,(--sank
	                                select xmlagg(
	                                    xmlforest (
	                                        xmlconcat (
	                                              xmlforest(sank.s_code)
	                                            , xmlforest(sank.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(sank.date_act::date as date_act)
	                                            , xmlforest(sank.num_act)
	                                            , (select xmlagg(xmlforest(ex.f004expert as code_exp)) from ies.t_schet_sluch_sank_exp ex where ex.schetsluchsank = sank.schetsluchsankid)
	                                            , xmlforest(sank.s_com)
	                                            , xmlforest(sank.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(zsl.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(zsl.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(_filename,1) in ('H','D') then substring(_filename, 2, character_length(_filename)) else _filename end + '.XML')  as fname,
	'<?xml version="1.0" encoding="windows-1251"?>' ||
    upper(xmlforest(--pers_list
        xmlconcat (
            xmlforest(--zglv
                (
	                select xmlconcat (
	                      xmlforest(s3.version)
	                    , xmlforest(s3.data::date as data)
	                    , xmlforest((select 'L' + case when left(_filename,1) in ('H','D') then substring(_filename, 2, character_length(_filename)) else _filename end) as filename)
	                    , xmlforest(_filename as filename1)
	                )
	                from ies.t_schet s3
	                where s3.schetid  = "SchetID"      
                )
            as zglv)
            ,(--pers
                select xmlagg(
                    xmlforest (
                        xmlconcat (
                          xmlforest(p.id_pac)
                        , xmlforest(p.fam)
                        , xmlforest(p.im)
                        , xmlforest(p.ot)
                        , xmlforest(p.w)
                        , xmlforest(p.dr::date as dr)
                        , (select xmlagg 
							(xmlforest(dost)
							) from (select split as dost from fn_split(p.dost,',')) t_dost
						)
                        , xmlforest(p.tel)
                        , xmlforest(p.fam_p)
                        , xmlforest(p.im_p)
                        , xmlforest(p.ot_p)
                        , xmlforest(p.w_p)
                        , xmlforest(p.dr_p::date as dr_p)
                        , (select xmlagg 
							(xmlforest(dost_p)
							) from (select split as dost_p from fn_split(p.dost_p,',')) t_dost
						)
                        , xmlforest(p.mr)
                        , xmlforest(p.doctype)
                        , xmlforest(p.docser)
                        , xmlforest(p.docnum)
						, xmlforest(p.docdate)
						, xmlforest(p.docorg)
                        , xmlforest(p.snils)
                        , xmlforest(p.okatog)
                        , xmlforest(p.okatop)
                        , xmlforest(p.comentp)
                        )                 
                    as pers)
                )
                from pers p
            )
        )
		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$;