IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_LoadSchet83Act]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_LoadSchet83Act]
GO

-- =============================================
-- Author:		(C) Vitacard System 2012
-- Create date: 2012-10-27
-- Description:	Формирует xml, по фед. счет-реестру
-- =============================================
CREATE PROCEDURE [dbo].[sp_LoadSchet83Act]  
    @Schet uniqueidentifier,
    @Act xml = null,
    @ActL xml = null,
    @ActV xml = null
AS
BEGIN

	if (@ActV is not null)
	begin

		SELECT 	cast(replace(replace(T.c.value('./N_ZAP[1]', 'varchar(8000)'),' ',''),' ','') AS INT) AS [N_ZAP]
				,T.c.value('./IDCASE[1]', 'int') AS [IDCASE]
				,T.c.value('./COMMENT[1]', 'varchar(8000)') AS [COMMENT]
		INTO #SLUCH_V
		FROM @ActV.nodes('/FLK_P/PR') T(c)

		update sluch set [Error] = v.[COMMENT]
		from [AKUZ].T_SCHET_SLUCH sluch
		join [AKUZ].T_SCHET_ZAP zap on zap.[SchetZapID] = sluch.[SchetZap]
		join #SLUCH_V v on v.[IDCASE] = sluch.[IDCASE] and v.[N_ZAP] = zap.[N_ZAP]
		where zap.Schet=@Schet

		drop table #SLUCH_V

		GOTO ExitLable
	end
	
	if (@Act is not null)
	begin
    declare @pack uniqueidentifier
	declare @packname varchar(128)
	declare @packprefix varchar(8)
	declare @calculate bit
	declare @disp varchar(3)
	SELECT
		@Schet AS [SchetID]
		,T.c.value('./ZGLV[1]/VERSION[1]', 'varchar(5)') AS [VERSION]
		,T.c.value('./ZGLV[1]/DATA[1]', 'datetime') AS [DATA]
		,T.c.value('./ZGLV[1]/FILENAME[1]', 'varchar(26)') AS [FILENAME] -- не всегда шлют
		,T.c.value('./ZGLV[1]/CALCULATE[1]', 'bit') AS [CALCULATE]

		,T.c.value('./SCHET[1]/CODE[1]', 'int') AS [CODE] -- не всегда шлют
		,T.c.value('./SCHET[1]/CODE_MO[1]', 'varchar(6)') AS [CODE_MO] -- не всегда шлют
		,T.c.value('./SCHET[1]/YEAR[1]', 'int') AS [YEAR]
		,T.c.value('./SCHET[1]/MONTH[1]', 'int') AS [MONTH]
		,T.c.value('./SCHET[1]/NSCHET[1]', 'varchar(15)') AS [NSCHET]
		,T.c.value('./SCHET[1]/DSCHET[1]', 'datetime') AS [DSCHET]
		,T.c.value('./SCHET[1]/PLAT[1]', 'varchar(5)') AS [PLAT]
		,T.c.value('./SCHET[1]/SUMMAV[1]', 'decimal(15,2)') AS [SUMMAV]
		,T.c.value('./SCHET[1]/SUMMAP[1]', 'decimal(15,2)') AS [SUMMAP]
		,T.c.value('./SCHET[1]/SANK_MEK[1]', 'decimal(15,2)') AS [SANK_MEK]
		,T.c.value('./SCHET[1]/SANK_MEE[1]', 'decimal(15,2)') AS [SANK_MEE]
		,T.c.value('./SCHET[1]/SANK_EKMP[1]', 'decimal(15,2)') AS [SANK_EKMP]
	into #SCHET
	FROM @Act.nodes('/ZL_LIST') T(c)


	---- заполняем переменные
	select	
		@pack = p.SchetPackID, 
		@packname = p.PackName, 
		@packprefix = p.PackNamePrefix, 
		@calculate = s.CALCULATE,
		@disp=p.DISP
	from [AKUZ].T_SCHET s1
	join [AKUZ].T_SCHET_PACK p on p.Schet = @Schet
	join #SCHET s on s1.[YEAR] = s.[YEAR] and s1.[MONTH] = s.[MONTH] and s1.[NSCHET] = s.[NSCHET] and s1.[DSCHET] = s.[DSCHET] 
		--and (s.PLAT = p.SMO or (s.PLAT is null and p.SMO is null)) -- убрала т.к. от фонда поступи ответ, что данное поле не предусмотрено в акте по форматам		
	where s1.SchetID = @Schet
		
	if (@pack is null)
	begin
		raiserror('Акт не соответствует реестру', 16, 10)
		goto ExitLable
	end

	if (select [VERSION] from #SCHET) = '3.0' or (select [VERSION] from #SCHET) = '3.1' or (select [VERSION] from #SCHET) = '3.05' or (select [VERSION] from #SCHET) = '3.1.2' or (select [VERSION] from #SCHET) = '3.2' or (select [VERSION] from #SCHET) = '5.0' or (select [VERSION] from #SCHET) = '5.1'
	begin -- ВЕРСИЯ 3.0
		if(@calculate = 1 or @calculate = 0 or @calculate is null)
		begin

			
			--- если уже раскидано по пакам, возвращаем
			if((select count(*) from [AKUZ].T_SCHET_PACK where Schet = @Schet) > 1)
			begin
				update zap
				set zap.SchetPack = @pack
				from [AKUZ].T_SCHET_ZAP zap
				where zap.Schet = @Schet and zap.SchetPack <> @pack

				delete 
				from [AKUZ].T_SCHET_PACK
				where Schet = @Schet and SchetPackID <> @pack
			end
			--return

			--- обновляем pack, zap, z_sl, sluch, usl

			-- zap
			SELECT
				T.c.value('./N_ZAP[1]', 'int') AS [N_ZAP],
				T.c.value('./PACIENT[1]/SMO[1]', 'varchar(5)') AS [SMO]
			INTO #ZAP1
			FROM @Act.nodes('/ZL_LIST/ZAP') T(c)

			update z1
			set z1.SMO = z2.SMO
			from [AKUZ].T_SCHET_ZAP z1
			join #ZAP1 z2 on z1.N_ZAP=z2.N_ZAP
			where z1.SchetPack = @pack and not (z2.SMO is null)

			drop table #ZAP1

			-- z_sl
			SELECT
			     T.c.value('./IDCASE[1]', 'int') AS [IDCASE]
				,T.c.value('./IDSP[1]', 'int') AS [IDSP]
				,T.c.value('./SUMV[1]', 'decimal(15,2)') AS [SUMV]
				,T.c.value('./OPLATA[1]', 'int') AS [OPLATA]
				,T.c.value('./SUMP[1]', 'decimal(15,2)') AS [SUMP]
				,T.c.value('./SANK_IT[1]', 'decimal(15,2)') AS [SANK_IT]
			INTO #Z_SL1
			FROM @Act.nodes('/ZL_LIST/ZAP/Z_SL') T(c)
			
			update z_sl
			set z_sl.[IDSP] = s.[IDSP],
				z_sl.[SUMV] = s.[SUMV],
				z_sl.[OPLATA] = s.[OPLATA],
				z_sl.[SUMP] = s.[SUMP],
				z_sl.[SANK_IT] = s.[SANK_IT]
			from [AKUZ].T_SCHET_Z_SL z_sl 
			join #Z_SL1 s on s.[IDCASE] = z_sl.[IDCASE]
			join [AKUZ].T_SCHET_ZAP sz on sz.SchetZapID = z_sl.SchetZap
			where sz.SchetPack = @pack

			-- sluch
			

				SELECT
					T.c.value('../IDCASE[1]', 'int') AS [IDCASE]
					,T.c.value('./SL_ID[1]', 'int') AS [SL_ID]
					,T.c.value('./KOEF_U[1]', 'decimal(15,2)') AS [KOEF_U]
				,T.c.value('./KOEF_Z[1]', 'decimal(15,2)') AS [KOEF_Z]
				,T.c.value('./KOEF_UP[1]', 'decimal(15,2)') AS [KOEF_UP]
							,T.c.value('./BZTSZ[1]', 'decimal(15,2)') AS [BZTSZ]
					,T.c.value('./KOEF_D[1]', 'decimal(15,2)') AS [KOEF_D]
					,T.c.value('./TARIF[1]', 'decimal(15,2)') AS [TARIF]
					,T.c.value('./SUM_M[1]', 'decimal(15,2)') AS [SUM_M]
					,T.c.value('./OPLATA[1]', 'int') AS [OPLATA]
					,T.c.value('../SANK[1]/S_COM[1]', 'varchar(8000)') AS [S_COM_FOND]
					,T.c.value('./SANK[1]/S_COM[1]', 'varchar(8000)') AS [S_COM_RITA]
				INTO #SLUCH1
				FROM @Act.nodes('/ZL_LIST/ZAP/Z_SL/SL') T(c)

			if (@calculate = 1) --если пришло от расчетного сервера
			begin


				update sluch
				set sluch.[TARIF] = s.[TARIF],
					sluch.[SUM_M] = s.[SUM_M],
					sluch.[OPLATA] = s.[OPLATA],
					sluch.[Error] = isnull(s.[S_COM_FOND], s.S_COM_RITA)
				from [AKUZ].T_SCHET_SLUCH sluch
				join [AKUZ].T_SCHET_Z_SL z_sl on z_sl.SchetZSlID = sluch.SchetZSl
				join #SLUCH1 s on s.[SL_ID] = sluch.[IDCASE] and s.[IDCASE] = z_sl.[IDCASE]
				join [AKUZ].T_SCHET_ZAP sz on sz.SchetZapID = sluch.SchetZap
				--join [AKUZ].T_SCHET_KSG_KPG ksg on ksg.SchetSluch = sluch.SchetSluchID
				where sz.SchetPack = @pack


				update ksg
				set
				ksg.[KOEF_U] = s.[KOEF_U],
				ksg.[KOEF_Z] = s.[KOEF_Z],
				ksg.[KOEF_UP] = s.[KOEF_UP],
				ksg.[BZTSZ] = s.[BZTSZ],
				ksg.[KOEF_D] = s.[KOEF_D]
				from [AKUZ].T_SCHET_KSG_KPG ksg
				join [AKUZ].T_SCHET_SLUCH sluch on ksg.SchetSluch = sluch.SchetSluchID
				join [AKUZ].T_SCHET_Z_SL z_sl on z_sl.SchetZSlID = sluch.SchetZSl
				join #SLUCH1 s on s.[SL_ID] = sluch.[IDCASE] and s.[IDCASE] = z_sl.[IDCASE]
				join [AKUZ].T_SCHET_ZAP sz on sz.SchetZapID = sluch.SchetZap
				where sz.SchetPack = @pack
				drop table #SLUCH1
			end 

			update z1
			set z1.Error = null
			from [AKUZ].T_SCHET_ZAP z1
			where z1.SchetPack = @pack
				and z1.Error is not null

			if (@calculate = 0 or @calculate is null) --если пришло из фонда
			begin

				update sluch
				set sluch.[Error] = isnull(s.[S_COM_FOND], s.S_COM_RITA)
				from [AKUZ].T_SCHET_SLUCH sluch
				join [AKUZ].T_SCHET_Z_SL z_sl on z_sl.SchetZSlID = sluch.SchetZSl
				join #SLUCH1 s on s.[SL_ID] = sluch.[IDCASE] and s.[IDCASE] = z_sl.[IDCASE]
				join [AKUZ].T_SCHET_ZAP sz on sz.SchetZapID = sluch.SchetZap
				--join [AKUZ].T_SCHET_KSG_KPG ksg on ksg.SchetSluch = sluch.SchetSluchID
				where sz.SchetPack = @pack

				-- заполняем ошибки в zap
			   update z1
			   set z1.Error =    (
					  SELECT ss.Error + ', '
					  FROM [AKUZ].T_SCHET_SLUCH ss
					  WHERE z1.SchetZapID = ss.SchetZap
					  FOR XML PATH('')
					 )
			   from [AKUZ].T_SCHET_ZAP z1
			   join akuz.T_SCHET_SLUCH sluch on z1.SchetZapID = sluch.SchetZap and (sluch.Error is not null and sluch.Error <> '')
			   where z1.SchetPack = @pack 

			end 

			






			-- usl
			SELECT
				T.c.value('../../IDCASE[1]', 'int') AS [IDCASE]
				,T.c.value('../SL_ID[1]', 'int') AS [SL_ID]
				,T.c.value('./IDSERV[1]', 'int') AS [IDSERV]
				,T.c.value('./TARIF[1]', 'decimal(15,2)') AS [TARIF]
				,T.c.value('./SUMV_USL[1]', 'decimal(15,2)') AS [SUMV_USL]
			INTO #USL1
			FROM @Act.nodes('/ZL_LIST/ZAP/Z_SL/SL/USL') T(c)
        
			update usl
			set usl.TARIF = u.[TARIF],
				usl.SUMV_USL = u.[SUMV_USL]
			from [AKUZ].T_SCHET_USL usl
			join [AKUZ].T_SCHET_SLUCH sluch on sluch.[SchetSluchID] = usl.[SchetSluch]
			join [AKUZ].T_SCHET_Z_SL z_sl on z_sl.SchetZSlID = sluch.SchetZSl
			join #USL1 u on u.[IDSERV] = usl.[IDSERV] and u.[SL_ID] = sluch.[IDCASE] and u.[IDCASE] = z_sl.[IDCASE]
			join [AKUZ].T_SCHET_ZAP sz on sz.SchetZapID = sluch.SchetZap
			where sz.SchetPack = @pack
		
			drop table #USL1

			-- sank
			SELECT
				T.c.value('../IDCASE[1]', 'int') AS [IDCASE]
				,T.c.value('../SL[1]/SL_ID[1]', 'int') AS [SL_ID]
				,T.c.value('./S_CODE[1]', 'varchar(36)') AS [S_CODE]
				,T.c.value('./S_SUM[1]', 'decimal(15,2)') AS [S_SUM]
				,T.c.value('./S_TIP[1]', 'int') AS [S_TIP]
				,T.c.value('./S_OSN[1]', 'int') AS [S_OSN]
				,T.c.value('./S_COM[1]', 'varchar(250)') AS [S_COM]
				,T.c.value('./S_IST[1]', 'int') AS [S_IST]
			INTO #SANK1
			FROM @Act.nodes('/ZL_LIST/ZAP/Z_SL/SANK') T(c)
        
			delete ssa
			from [AKUZ].T_SCHET_SANK ssa
			inner join [AKUZ].T_SCHET_SLUCH sluch
				on sluch.SchetSluchID = ssa.SchetSluch
			inner join [AKUZ].T_SCHET_ZAP sz 
				on sz.SchetZapID = sluch.SchetZap
				and sz.SchetPack = @pack

			insert [AKUZ].T_SCHET_SANK (SchetSankID,S_CODE,S_SUM,S_TIP,S_OSN,S_COM,S_IST,SchetSluch)
			select newid(), s.S_CODE,s.S_SUM,s.S_TIP,isnull(s.S_OSN,0),s.S_COM,s.S_IST,sluch.SchetSluchID
			from [AKUZ].T_SCHET_SLUCH sluch
			join [AKUZ].T_SCHET_Z_SL z_sl on z_sl.SchetZSlID = sluch.SchetZSl
			join #SANK1 s on s.[SL_ID] = sluch.[IDCASE] and s.[IDCASE] = z_sl.[IDCASE]
			join [AKUZ].T_SCHET_ZAP sz on sz.SchetZapID = sluch.SchetZap
			where sz.SchetPack = @pack
				and not exists (select * from [AKUZ].T_SCHET_SANK sk where sk.S_CODE = s.S_CODE and sk.SchetSluch = sluch.SchetSluchID)
				and (s.S_SUM <> 0.00 or s.S_COM is not null or S.S_OSN is not null)
		
			drop table #SANK1


			--- раскидываем по пакам

			--if (@calculate = 1) --если пришло от Риты
			--begin

				select zap.SMO as Code
				into #SMO1_1
				from [AKUZ].T_SCHET_ZAP zap
				where zap.Schet = @Schet and not (zap.SMO is null)
				group by zap.SMO

				select smo.Code as Code
				into #SMO1_2
				from #SMO1_1 smo
				where exists
					(select * 
					from [AKUZ].T_INSURANCE_COMPANY inscomp
					join [AKUZ].T_INSURER ins on ins.InsurerID = inscomp.Insurer
					where inscomp.InsuranceType = 0 and ins.SmoCod = smo.Code)

				drop table #SMO1_1

				insert into [AKUZ].T_SCHET_PACK(SchetPackID, PackName, PackNamePrefix, Schet, SMO,DISP)
				select newid(), 
					--(SUBSTRING(@packname,1,7) + 'S' + smo.Code + SUBSTRING(@packname, case when @calculate = 1 then 11 when @calculate = 0 then 15 end, len(@packname)-10)), 
					(SUBSTRING(@packname,1,7) + 'S' + smo.Code + right(@packname,len(@packname)-CHARINDEX('_',@packname)+1)), 
					@packprefix, 
					@Schet,
					smo.Code,
					@disp
				from #SMO1_2 smo

				drop table #SMO1_2

				update zap
				set zap.SchetPack = pack.SchetPackID
				from [AKUZ].T_SCHET_ZAP zap
					join [AKUZ].T_SCHET_PACK pack on pack.Schet = @Schet and pack.SMO = zap.SMO
				where zap.Schet = @Schet

			--end
			---- пересчитываем суммы

			--update p
			--	set p.SUMMAV = null
			--	   ,p.SUMMAP = null
			--	   ,p.SANK_MEK = null
			--	  ,p.SANK_MEE = null
			--	  ,p.SANK_EKMP = null
			--from [AKUZ].T_SCHET_PACK p
			--join #SCHET s on s.SchetID = p.Schet

			--update p
			--	set p.SUMMAV = s.SUMMAV
			--	   ,p.SUMMAP = s.SUMMAP
			--	   ,p.SANK_MEK = s.SANK_MEK
			--	   ,p.SANK_MEE = s.SANK_MEE
			--	   ,p.SANK_EKMP = s.SANK_EKMP
			--from [AKUZ].T_SCHET p
			--join #SCHET s on s.SchetID = p.SchetID
		update pack
		set pack.SUMMAV = (select sum(sl.SUMV) from [AKUZ].T_SCHET_Z_SL sl join [AKUZ].T_SCHET_ZAP sz on sl.SchetZap = sz.SchetZapID where sz.SchetPack = pack.SchetPackID),
			pack.SUMMAP = (select sum(sl.SUMP) from [AKUZ].T_SCHET_Z_SL sl join [AKUZ].T_SCHET_ZAP sz on sl.SchetZap = sz.SchetZapID where sz.SchetPack = pack.SchetPackID),
			pack.SANK_MEK = (select sum(sl.SANK_IT) from [AKUZ].T_SCHET_Z_SL sl join [AKUZ].T_SCHET_ZAP sz on sl.SchetZap = sz.SchetZapID where sz.SchetPack = pack.SchetPackID)
		from [AKUZ].T_SCHET_PACK pack
		where pack.Schet = @Schet

        --удаляется пак с пустыми суммами
		delete pack
		from [AKUZ].T_SCHET_PACK pack
		where pack.Schet = @Schet
			and pack.SUMMAV is null
			and pack.SUMMAP is null
			and pack.SANK_MEK is null

			update schet
				set schet.SUMMAV = s.SUMMAV
				   ,schet.SUMMAP = s.SUMMAP
				   ,schet.SANK_MEK = s.SANK_MEK
				   ,schet.SANK_MEE = s.SANK_MEE
				   ,schet.SANK_EKMP = s.SANK_EKMP
			from [AKUZ].T_SCHET schet
			join #SCHET s on s.SchetID = schet.SchetID

		end
		else
		begin 
			raiserror('Загрузка акта от страховой временно не доступно', 16, 10)
			goto ExitLable
		end
		
		---- пересчитываем суммы

		--update p
		--	set p.SUMMAV = s.SUMMAV
		--	   ,p.SUMMAP = s.SUMMAP
		--	   ,p.SANK_MEK = s.SANK_MEK
		--	   --,p.SANK_MEE = s.SANK_MEE
		--	   --,p.SANK_EKMP = s.SANK_EKMP
		--from [AKUZ].T_SCHET_PACK p
		--join #SCHET s on s.SchetID = p.Schet
		--where p.SchetPackID = @pack

		--update schet
		--	set schet.SUMMAV = (select sum(p.SUMMAV) from [AKUZ].T_SCHET_PACK p where p.Schet = @Schet)
		--	   ,schet.SUMMAP = (select sum(p.SUMMAP) from [AKUZ].T_SCHET_PACK p where p.Schet = @Schet)
		--	   ,schet.SANK_MEK = (select sum(p.SANK_MEK) from [AKUZ].T_SCHET_PACK p where p.Schet = @Schet)
		--	   --,schet.SANK_MEE = (select sum(p.SANK_MEE) from [AKUZ].T_SCHET_PACK p where p.Schet = @Schet)
		--	   --,schet.SANK_EKMP = (select sum(p.SANK_EKMP) from [AKUZ].T_SCHET_PACK p where p.Schet = @Schet)
		--from [AKUZ].T_SCHET schet
		--where schet.SchetID = @Schet
	end --if

	else begin -- ВЕРСИИ ДО 3.0

	---- обновления

		if(@calculate = 1)
		begin

			--- если уже раскидано по пакам, возвращаем
			if((select count(*) from [AKUZ].T_SCHET_PACK where Schet = @Schet) > 1)
			begin
				update zap
				set zap.SchetPack = @pack
				from [AKUZ].T_SCHET_ZAP zap
				where zap.Schet = @Schet and zap.SchetPack <> @pack

				delete 
				from [AKUZ].T_SCHET_PACK
				where Schet = @Schet and SchetPackID <> @pack
			end



			--- обновляем zap, sluch, usl
		
			SELECT
				T.c.value('./N_ZAP[1]', 'int') AS [N_ZAP],
				T.c.value('./PACIENT[1]/SMO[1]', 'varchar(5)') AS [SMO]
			INTO #ZAP2
			FROM @Act.nodes('/ZL_LIST/ZAP') T(c)

			update z1
			set z1.SMO = z2.SMO
			from [AKUZ].T_SCHET_ZAP z1
			join #ZAP2 z2 on z1.N_ZAP=z2.N_ZAP
			where z1.Schet = @Schet and not (z2.SMO is null)

			drop table #ZAP2

		
			SELECT
				T.c.value('../N_ZAP[1]', 'int') AS [N_ZAP]
				,T.c.value('./IDCASE[1]', 'int') AS [IDCASE]
				,T.c.value('./ED_COL[1]', 'decimal(5,2)') AS [ED_COL]
				,T.c.value('./IDSP[1]', 'int') AS [IDSP]
				,T.c.value('./TARIF[1]', 'decimal(15,2)') AS [TARIF]
				,T.c.value('./SUMV[1]', 'decimal(15,2)') AS [SUMV]
				,T.c.value('./SUMP[1]', 'decimal(15,2)') AS [SUMP]
				,T.c.value('./REFREASON[1]', 'int') AS [REFREASON]
				,T.c.value('./SANK_MEK[1]', 'decimal(15,2)') AS [SANK_MEK]
				,T.c.value('./SANK_IT[1]', 'decimal(15,2)') AS [SANK_IT]
				,T.c.value('./OPLATA[1]', 'int') AS [OPLATA]
				,T.c.value('./COMENTSL[1]', 'varchar(8000)') AS [COMENTSL]
			INTO #SLUCH2
			FROM @Act.nodes('/ZL_LIST/ZAP/SLUCH') T(c)

			update sluch
			set sluch.ED_COL = s.[ED_COL],
				sluch.IDSP = s.[IDSP],
				sluch.TARIF = s.[TARIF],
				sluch.SUMV = s.[SUMV],
				sluch.SUMP = s.[SUMP],
				sluch.[SANK_MEK] = isnull(s.[SANK_IT], s.[SANK_MEK]),
				sluch.[OPLATA] = s.[OPLATA],
				sluch.[Error] = s.[COMENTSL] --isnull(sluch.[Error], '') +
			from [AKUZ].T_SCHET_SLUCH sluch, #SLUCH2 s
			where s.[IDCASE] = sluch.[IDCASE]
			and exists(select * from [AKUZ].T_SCHET_ZAP sz where sluch.SchetZap = sz.SchetZapID and sz.SchetPack = @pack)
		
			SELECT
				T.c.value('../IDCASE[1]', 'int') AS [IDCASE]
				,T.c.value('./IDSERV[1]', 'int') AS [IDSERV]
				,T.c.value('./TARIF[1]', 'decimal(15,2)') AS [TARIF]
				,T.c.value('./SUMV_USL[1]', 'decimal(15,2)') AS [SUMV_USL]		     
			INTO #USL2
			FROM @Act.nodes('/ZL_LIST/ZAP/SLUCH/USL') T(c)
        
			update usl
			set usl.TARIF = u.[TARIF],
				usl.SUMV_USL = u.[SUMV_USL]
			from [AKUZ].T_SCHET_USL usl
			join #USL2 u on u.[IDSERV] = usl.[IDSERV]
			join [AKUZ].T_SCHET_SLUCH sluch on sluch.[IDCASE] = u.[IDCASE] and  sluch.[SchetSluchID] = usl.[SchetSluch]
			where exists(select * from [AKUZ].T_SCHET_ZAP sz where sluch.SchetZap = sz.SchetZapID and sz.SchetPack = @pack)
		
			drop table #USL2		
		
			delete t
			from [AKUZ].[T_SCHET_SLUCH_LINK_F014] t
			join [AKUZ].T_SCHET_SLUCH sluch on sluch.SchetSluchID = t.SchetSluch
			join [AKUZ].T_SCHET_ZAP sz on sz.SchetZapID = sluch.SchetZap
			where sz.SchetPack = @pack
			--where exists(select * from [AKUZ].T_SCHET_ZAP sz where sluch.SchetZap = sz.SchetZapID and sz.Schet = @Schet)
		

			INSERT INTO [AKUZ].[T_SCHET_SLUCH_LINK_F014] ([SchetSluchLinkF014ID], [SchetSluch], [F014DenyReason])
			select NEWID()
                , sluch.SchetSluchID
                , (select top (1) fdr.F014DenyReasonID
                    from AKUZ.F014_DENY_REASON fdr
                    where fdr.Kod = s.S_OSN
                    and fdr.DateBegin <= GETDATE()
				    and (fdr.DateEnd is null or
				        fdr.DateEnd >= GETDATE()))
			from #SLUCH2 s, [AKUZ].T_SCHET_SLUCH sluch
			where s.REFREASON is not null and sluch.IDCASE = s.IDCASE
				and exists(select * from [AKUZ].T_SCHET_ZAP sz where sluch.SchetZap = sz.SchetZapID and sz.SchetPack = @pack)
				--and exists(select * from [AKUZ].T_F014_DENY_REASON dr where dr.Kod = s.REFREASON)

			drop table #SLUCH2



			--- раскидываем по пакам

			select zap.SMO as Code
			into #SMO2_1
			from [AKUZ].T_SCHET_ZAP zap
			where zap.Schet = @Schet and not (zap.SMO is null)
			group by zap.SMO

			select smo.Code as Code
			into #SMO_2
			from #SMO_1 smo
			where exists
				(select * 
				from [AKUZ].T_INSURANCE_COMPANY inscomp
				join [AKUZ].T_INSURER ins on ins.InsurerID = inscomp.Insurer
				where inscomp.InsuranceType = 0 and ins.SmoCod = smo.Code)

			drop table #SMO2_1

			insert into [AKUZ].T_SCHET_PACK(SchetPackID, PackName, PackNamePrefix, Schet, SMO)
			select newid(), 
				(SUBSTRING(@packname,1,7) + 'S' + smo.Code + SUBSTRING(@packname, 11, len(@packname)-10)), 
				@packprefix, 
				@Schet,
				smo.Code
			from #SMO2_2 smo

			drop table #SMO2_2

			update zap
			set zap.SchetPack = pack.SchetPackID
			from [AKUZ].T_SCHET_ZAP zap
				join [AKUZ].T_SCHET_PACK pack on pack.Schet = @Schet and pack.SMO = zap.SMO
			where zap.Schet = @Schet

		end

		else

		begin 

			SELECT
				T.c.value('../N_ZAP[1]', 'int') AS [N_ZAP]
				,T.c.value('./IDCASE[1]', 'int') AS [IDCASE]
				,T.c.value('./SUMP[1]', 'decimal(15,2)') AS [SUMP]
				,T.c.value('./REFREASON[1]', 'int') AS [REFREASON]
				,T.c.value('./SANK_MEK[1]', 'decimal(15,2)') AS [SANK_MEK]
				,T.c.value('./SANK_IT[1]', 'decimal(15,2)') AS [SANK_IT]
				,T.c.value('./OPLATA[1]', 'int') AS [OPLATA]
				,T.c.value('./COMENTSL[1]', 'varchar(8000)') AS [COMENTSL]
			INTO #SLUCH22
			FROM @Act.nodes('/ZL_LIST/ZAP/SLUCH') T(c)

			update sluch
			set sluch.SUMP = s.[SUMP],
				sluch.[SANK_MEK] = isnull(s.[SANK_IT], s.[SANK_MEK]),
				sluch.[OPLATA] = s.[OPLATA],
				sluch.[Error] = s.[COMENTSL] --isnull(sluch.[Error], '') + 
			from [AKUZ].T_SCHET_SLUCH sluch, #SLUCH22 s
			where s.[IDCASE] = sluch.[IDCASE]
				and exists(select * from [AKUZ].T_SCHET_ZAP sz where sluch.SchetZap = sz.SchetZapID and sz.SchetPack = @pack)

			drop table #SLUCH22
		end

		
	---- пересчитываем суммы
	
		update pack
		set pack.SUMMAV = (select sum(sl.SUMV) from [AKUZ].T_SCHET_SLUCH sl join [AKUZ].T_SCHET_ZAP sz on sl.SchetZap = sz.SchetZapID where sz.SchetPack = pack.SchetPackID),
			pack.SUMMAP = (select sum(sl.SUMP) from [AKUZ].T_SCHET_SLUCH sl join [AKUZ].T_SCHET_ZAP sz on sl.SchetZap = sz.SchetZapID where sz.SchetPack = pack.SchetPackID),
			pack.SANK_MEK = (select sum(sl.SANK_MEK) from [AKUZ].T_SCHET_SLUCH sl join [AKUZ].T_SCHET_ZAP sz on sl.SchetZap = sz.SchetZapID where sz.SchetPack = pack.SchetPackID)
		from [AKUZ].T_SCHET_PACK pack
		where pack.Schet = @Schet

		update schet
		set schet.SUMMAV = (select sum(SUMMAV) from [AKUZ].T_SCHET_PACK p where p.Schet = schet.SchetID),
			schet.SUMMAP = (select sum(SUMMAP) from [AKUZ].T_SCHET_PACK p where p.Schet = schet.SchetID),
			schet.SANK_MEK = (select sum(SANK_MEK) from [AKUZ].T_SCHET_PACK p where p.Schet = schet.SchetID)
		from [AKUZ].T_SCHET schet
		where schet.SchetID = @Schet
	end --else

	end
ExitLable:
    drop table #SCHET

END