
Use BUH

CREATE TABLE  [#tbSver1] (
	[Id1] [int] IDENTITY (1, 1) NOT NULL ,
	[idDoc] [int] NULL ,
	[IdPredpr] [int] NULL ,
	[NumDoc] [varchar] (50) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[DateDoc] [datetime] NULL ,
	[UNN] [varchar] (20) COLLATE SQL_Latin1_General_CP1251_CI_AS  NULL ,
	[NamePredpr] [varchar] (150) COLLATE SQL_Latin1_General_CP1251_CI_AS  NULL ,
	[IdPlat] [int] NULL ,
	[SumBezNDS] [decimal](18, 2) NULL ,
	[SumNDS] [decimal](18, 2) NULL ,
	[SumSNDS] [decimal](18, 2) NULL,
                   [Tara] [decimal](18, 2) NULL ,
	[VidDoc] [tinyint] NULL ,
                  IdUsl integer null,
                  IdTipDoc tinyint,
                  Sv bit null,
                  [NumDoc1] [varchar] (50) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
                  IdSchetNDS integer,
                  NamePredprOb varchar(150),
                  Seria varchar(25)


                   
) ON [PRIMARY]

CREATE TABLE  [#tbSver2] (
	[Id1] [int] IDENTITY (1, 1) NOT NULL ,
	[idDoc] [int] NULL ,
	[IdPredpr] [int] NULL ,
	[NumDoc] [varchar] (50) COLLATE SQL_Latin1_General_CP1251_CI_AS  NULL ,
	[DateDoc] [datetime] NULL ,
	[UNN] [varchar] (20) COLLATE SQL_Latin1_General_CP1251_CI_AS  NULL ,
	[NamePredpr] [varchar] (150) COLLATE SQL_Latin1_General_CP1251_CI_AS  NULL ,
	[IdPlat] [int] NULL ,
	[SumBezNDS] [decimal](18, 2) NULL ,
	[SumNDS] [decimal](18, 2) NULL ,
	[SumSNDS] [decimal](18, 2) NULL ,
                NSF varchar(30) COLLATE SQL_Latin1_General_CP1251_CI_AS  NULL ,
                IdTipDoc tinyint,
                Sv bit null,
                Number bigint,
                SumNDS10 decimal(18,2) null,
                SumNDS20 decimal(18,2) null,
                SumNDS25 decimal(18,2) null,
                SumNDSrst decimal(18,2) null,
                [NumDoc2] [varchar] (50) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
                [Seria] [varchar] (25) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
                [DocumentType] tinyint
)
ON [PRIMARY]

CREATE TABLE [#tbSver3] (
	[Id1] [int] IDENTITY (1, 1) NOT NULL ,
	[NSF] [varchar] (30) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[IdPlatsf] [int] NULL ,
	[NamePredprsf] [varchar] (150) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[UNNsf] [varchar] (20) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[NumDocsf] [varchar] (50) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[DateDocsf] [datetime] NULL ,
	[SumBezNDSsf] [decimal](18, 2) NULL ,
	[SumNDSsf] [decimal](18, 2) NULL ,
	[SumSNDSsf] [decimal](18, 2) NULL ,
	[IdPlat] [int] NULL ,
	[NamePredpr] [varchar] (150) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[UNN] [varchar] (20) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[NumDoc] [varchar] (50) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[DateDoc] [datetime] NULL ,
	[SumBezNDS] [decimal](18, 2) NULL ,
	[SumNDS] [decimal](18, 2) NULL ,
	[SumSNDS] [decimal](18, 2) NULL ,
                [Tara] [decimal](18, 2) NULL ,
	[Pr] [int] NULL ,
	[RasBezNDS] [Decimal] (18,2) NULL ,
	[RasNDS] [Decimal] (18,2) NULL ,
                IdDoc integer,
                VidDoc tinyint,
                 IdTipDoc tinyint,
                Ch bit not null default(0),
                Number bigint,
                IsTara bit default(0) not null,
                SumNDS10 decimal(18,2) null,
                SumNDS20 decimal(18,2) null,
                SumNDS25 decimal(18,2) null,
                SumNDSrst decimal(18,2) null,
                [NumDoc1] [varchar] (50) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL, 
                [NumDoc2] [varchar] (50) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
                IdSchetNDS integer,
                ChColor bit not null default(0),
                 NamePredprOb varchar(150),
                 Seria varchar(25),
                 SeriaSf varchar(25)



 

)
ON [PRIMARY]




Declare @FirstDate datetime,
              @SecDate datetime,
              @IdPlat integer,
              @MyIdPlat integer,
               @Tara decimal(18,2),
               @NDSZero bit ,
               @PrVib tinyint ,
               @InclNDs bit,
                @MyNamePlat varchar(150),
                @UNN varchar(9),   
                @IdOblpo integer,
                 @IsklTara bit




Set @NDSZero=0 -- :NDSZero -- 0 - не показывать, 1- показывать
Set @FirstDate='07.01.2019' -- :FirstDate  
Set @SecDate='09.30.2019' -- :SecDate   
Set @IdPlat=null -- :IdPlat  
Set @PrVib=0 -- :PrVib
Set @InclNDS=1-- :InclNDS
Set @IsklTara=1-- :InclNDS

Select @MyIdPlat=n.IdPredpr ,@MyNamePlat=sp.NamePredprShort,
@UNN=sp.UNN
from MainDataBase..tbNastrPredpr n
inner join MainDataBase..tbPredpr sp on n.IdPredpr=sp.IdPredpr

Select @IdOblpo=IdPredpr from MainDataBase..tbPredpr where UNN=@UNN and DopUNN='8000'

----24.02.2017
--Обработка сверенных , а затем аннулированных поставщиком

Declare @IdS int,@KS int


Select  Distinct @IdS=IdKt from tbSFProvNDS --кол-во субсчетов
set @KS= @@RowCount

--снятие флажка сверки у тов.док.

Update tbTovOt 
  set sv=0 
 where NSF in (
select              sp.UNN+'-'+cast(i.CompilingYear as varchar(4))+'-'+Right('000000000'+cast(i.Number as varchar(10)),10)
from MainDatabase..invoices i 
inner join MainDataBase..history h 
 on i.IdSender=h.IdSender and i.CompilingYear=h.CompilingYear and i.Number=h.Number
inner join MainDataBase..tbPredpr sp on i.IdSender=sp.IdPredpr
 where
sv=1 and invStatus in (6,7))

--снятие флажка сверки у бух.спр.

Update tbSpravka 
  set sv=0 
 where NSF in (
select              sp.UNN+'-'+cast(i.CompilingYear as varchar(4))+'-'+Right('000000000'+cast(i.Number as varchar(10)),10)
from MainDatabase..invoices i 
inner join MainDataBase..history h 
 on i.IdSender=h.IdSender and i.CompilingYear=h.CompilingYear and i.Number=h.Number
inner join MainDataBase..tbPredpr sp on i.IdSender=sp.IdPredpr
 where
sv=1 and invStatus in (6,7))

--удаление проводок по закр.НДС

if @KS>0

Delete from tbSpravka
 where SubString(NSF,3,25) in  (
-- where Right(NSF,25) in  (
select              sp.UNN+'-'+cast(i.CompilingYear as varchar(4))+'-'+Right('000000000'+cast(i.Number as varchar(10)),10)
from MainDatabase..invoices i 
inner join MainDataBase..history h 
 on i.IdSender=h.IdSender and i.CompilingYear=h.CompilingYear and i.Number=h.Number
inner join MainDataBase..tbPredpr sp on i.IdSender=sp.IdPredpr
 where  
sv=1 and invStatus in (6,7)
and not Exists(Select * from MainDataBase..tbZakrPeriod where month(Zakritie_Date)=month(tbSpravka.Mes) and Year(Zakritie_Date)=Year(tbSpravka.Mes) ))


--снятие флажка сверки у ЭСЧФ

Update MainDatabase..invoices
set sv=0
 from MainDatabase..invoices i 
inner join MainDataBase..history h 
 on i.IdSender=h.IdSender and i.CompilingYear=h.CompilingYear and i.Number=h.Number
 where  
i.sv=1 and invStatus in (6,7)


----24.02.2017


Insert  into #tbSver1
(IdDoc,IdPredpr,NumDoc,DateDoc,UNN,IdPlat,NamePredpr,SumBezNDS,VidDoc,IdTipDoc,Tara,NumDoc1,NamePredprOb,Seria)      --,SumNDS)

Select t.IdDoc ,t.idPredprPar,
(case when dbo.f_CheckNumber(t.NumDocTov)<>'' then dbo.f_CheckNumber(t.NumDocTov) else t.NumDocTov end),
t.DateDoc,spp.UNN,sp.IdPlat,spp.NamePredpr,
(case when t.IdTipDoc=9 then SUM(s.Summ)  else -SUM(s.Summ) end) ,1,t.IdTipDoc,
(case when IsNull(t.Tara1,0)=0 then IsNull(t.TaraR1,0) else IsNull(t.Tara1,0)  end) ,
t.NumDocTov,
spob.NamePredprShort,t.Seria

from tbTovOt t
inner join tbTovOtSh sh on t.IdTovOt=sh.IdTovOt
inner join MainDataBase..tbPredpr sp on t.IdPredprPar=sp.IdPredpr   -- ??? sp.IdPlat ???
inner join MainDataBase..tbPredpr spp on IsNull(sp.IdPlat,t.IdPredprPar)=spp.IdPredpr   -- ??? sp.IdPlat ???
inner join MainDataBase..tbPredpr spob on sh.IdPredprOb=spob.IdPredpr  
inner join tbSumTovOt s on t.IdDoc=s.IdDoc
inner join tbSFSverka sv on s.IdSchetSubschetDt=sv.IdSchetSubSchetDt and s.IdSchetSubschetKt=sv.IdSchetSubSchetKt 

where t.DateDoc between @FirstDate and @SecDate
            and IsNull(t.sv,0)=0
            and sv.Prizn=0
            and ((@IdPlat is not null and sp.IdPlat=@IdPlat) or (@IdPlat is null and sp.IdPlat<>@MyIdPlat))
group by t.IdDoc ,t.idPredprPar,t.NumDocTov,t.DateDoc,spp.UNN,sp.IdPlat,spp.NamePredpr,t.IdTipDoc, (case when IsNull(t.Tara1,0)=0 then IsNull(t.TaraR1,0) else IsNull(t.Tara1,0)  end),
t.NumDocTov,
spob.NamePredprShort,t.Seria

Update #tbSver1
  set SumNDS=(Select (case when IdTipDoc=9 then Sum(Summ)  else -Sum(Summ) end) from tbSumTovOt sn
                            Inner join  tbSFSverka svn on sn.IdSchetSubschetDt=svn.IdSchetSubSchetDt and sn.IdSchetSubschetKt=svn.IdSchetSubSchetKt 
                                               where sn.IdDoc=#tbSver1.IdDoc and svn.Prizn=1)
   where VidDoc=1

Select * from #tbSver1 order by NamePredpr,NumDoc 
--Подключение НДС из бух/спр в случае корректир.тов накл.бух справкой
----24.05.2017

if @InclNDS=1
begin
Update #tbSver1
  set SumNDS=SumNDS+IsNull((Select (case when IdTipDoc=9 then Sum(Summ)  else -Sum(Summ) end) from tbSpravka s
                            Inner join  tbSFSverka svn on s.IdSchetSubschetDt=svn.IdSchetSubSchetDt and s.IdSchetSubschetKt=svn.IdSchetSubSchetKt 
                                               where
 IsNull(s.NumDocPart,s.NumDoc)=#tbSver1.NumDoc 
 and s.Mes between @FirstDate and @SecDate 
 and s.IdPredpr=#tbSver1.IdPlat and s.DataDoc=#tbSver1.DateDoc
 and ((#tbSver1.IdTipDoc=9 and Substring(dbo.mrfGetKodSS(s.IdSchetSubSchetDt),1,2)='18') or
           (#tbSver1.IdTipDoc=10 and Substring(dbo.mrfGetKodSS(s.IdSchetSubSchetKt),1,2)='18')) 
and svn.Prizn=1),0)

--21.10.2019
Update #tbSver1
  set SumBezNDS=SumBezNDS+IsNull((Select (case when IdTipDoc=9 then Sum(Summ)  else -Sum(Summ) end) from tbSpravka s
--                            Inner join  tbSFSverka svn on s.IdSchetSubschetDt=svn.IdSchetSubSchetDt and s.IdSchetSubschetKt=svn.IdSchetSubSchetKt 
                                               where
 IsNull(s.NumDocPart,s.NumDoc)=#tbSver1.NumDoc 
 and s.Mes between @FirstDate and @SecDate 
 and s.IdPredpr=#tbSver1.IdPlat and s.DataDoc=#tbSver1.DateDoc
 and ((#tbSver1.IdTipDoc=9 and Substring(dbo.mrfGetKodSS(s.IdSchetSubSchetDt),1,2)='42' 
 and  Substring(dbo.mrfGetKodSS(s.IdSchetSubSchetKt),1,2)='60') 
or
   (#tbSver1.IdTipDoc=10 and Substring(dbo.mrfGetKodSS(s.IdSchetSubSchetKt),1,2)='42'
  and Substring(dbo.mrfGetKodSS(s.IdSchetSubSchetDt),1,2)='60' )) 
),0)

end


--03.04.2017
if @KS<>0
Update #tbSver1
--      set IdSchetNDS=(Select Distinct (case when Substring(dbo.mrfGetKodSS(sn.IdSchetSubSchetDt),1,2)='18' then sn.IdSchetSubSchetDt 
      set IdSchetNDS=(Select Min(case when Substring(dbo.mrfGetKodSS(sn.IdSchetSubSchetDt),1,2)='18' then sn.IdSchetSubSchetDt 
                                                         else sn.IdSchetSubSchetKt end) from tbSumTovOt sn
                            Inner join  tbSFSverka svn on sn.IdSchetSubschetDt=svn.IdSchetSubSchetDt and sn.IdSchetSubschetKt=svn.IdSchetSubSchetKt 
                                               where sn.IdDoc=#tbSver1.IdDoc and svn.Prizn=1 
 and (Substring(dbo.mrfGetKodSS(svn.IdSchetSubSchetDt),1,2)='18'  or Substring(dbo.mrfGetKodSS(svn.IdSchetSubSchetKt),1,2)='18'))
where VidDoc=1
 
                                                      

--19.12.2016

--15.11.2016  

Delete from  #tbSver1 where IsNull(SumNDS,0)=0 and abs(IsNull(SumBezNDS,0))=Tara
---   
 
Update #tbSver1
    set SumSNDS=IsNull(SumBezNDS,0)+IsNull(SumNDS,0)
   where VidDoc=1

Insert  into #tbSver1
(IdDoc,IdPredpr,NumDoc,DateDoc,UNN,IdPlat,NamePredpr,SumBezNDS,VidDoc,IdUsl,NumDoc1,NamePredprOb)      --,SumNDS)

Select min(s.NumZap),s.idPredpr,
--IsNull(IsNull(s.NumDocPart,s.NumDoc) ,'нет номера') as NumDoc,
(case when IsNumeric(IsNull(IsNull(s.NumDocPart,s.NumDoc) ,'нет номера'))=1
 then dbo.f_CheckNumber(IsNull(IsNull(s.NumDocPart,s.NumDoc) ,'нет номера')) 
 else IsNull(IsNull(s.NumDocPart,s.NumDoc) ,'нет номера')  end),  
IsNull(s.DataDocPart,s.DataDoc) as DateDoc,spp.UNN,sp.IdPlat,spp.NamePredpr,SUM(s.Summ) as SumBezNDS,7 as VidDoc,null
,IsNull(IsNull(s.NumDocPart,s.NumDoc) ,'нет номера') ,
@MyNamePlat
from tbSpravka  s
inner join MainDataBase..tbPredpr sp on s.IdPredpr=sp.IdPredpr   -- ??? sp.IdPlat ???
inner join MainDataBase..tbPredpr spp on IsNull(sp.IdPlat,s.IdPredpr)=spp.IdPredpr   -- ??? sp.IdPlat ???
inner join tbSFSverka sv on s.IdSchetSubschetDt=sv.IdSchetSubSchetDt and s.IdSchetSubschetKt=sv.IdSchetSubSchetKt 
where s.Mes between @FirstDate and  @SecDate
            and IsNull(s.IdPrizLog,0)<>15
            and s.PrizRej=1
            and sv.Prizn=0
            and sp.IdPlat not in (Select IdPredpr from MainDataBase..tbNastrPredpr)
            and ((@IdPlat is not null and sp.IdPlat=@IdPlat) or (@IdPlat is null and sp.IdPlat<>@MyIdPlat))
            and IsNull(Sv,0)=0 
            and IdNameUsl is null
group by 
--s.NumZap,
s.idPredpr,
--IsNull(IsNull(s.NumDocPart,s.NumDoc) ,'нет номера'),
(case when IsNumeric(IsNull(IsNull(s.NumDocPart,s.NumDoc) ,'нет номера'))=1
 then dbo.f_CheckNumber(IsNull(IsNull(s.NumDocPart,s.NumDoc) ,'нет номера')) 
 else IsNull(IsNull(s.NumDocPart,s.NumDoc) ,'нет номера')  end),  
IsNull(s.DataDocPart,s.DataDoc),spp.UNN,sp.IdPlat,spp.NamePredpr ,
IsNull(IsNull(s.NumDocPart,s.NumDoc) ,'нет номера') 
--order by IsNull(IsNull(s.NumDocPart,s.NumDoc) ,'нет номера')

update #tbSver1
   set SumNDS=(Select Sum(Summ) from tbSpravka sn
inner join tbSFSverka svn on sn.IdSchetSubschetDt=svn.IdSchetSubSchetDt and sn.IdSchetSubschetKt=svn.IdSchetSubSchetKt 
where #tbSver1.idPredpr=sn.IdPredpr and 
      #tbSver1.NumDoc=   
(case when IsNumeric(IsNull(IsNull(sn.NumDocPart,sn.NumDoc) ,'нет номера'))=1
 then dbo.f_CheckNumber(IsNull(IsNull(sn.NumDocPart,sn.NumDoc) ,'нет номера')) 
 else IsNull(IsNull(sn.NumDocPart,sn.NumDoc) ,'нет номера')  end) and 
      #tbSver1.DateDoc=IsNull(sn.DataDocPart,sn.DataDoc) and
      svn.Prizn=1)
 where  #tbSver1.VidDoc=7

--03.04.2017
if @KS<>0

update #tbSver1
--   set IdSchetNDS=(Select Distinct (case when Substring(dbo.mrfGetKodSS(sn.IdSchetSubSchetDt),1,2)='18' then sn.IdSchetSubSchetDt 
   set IdSchetNDS=(Select Min(case when Substring(dbo.mrfGetKodSS(sn.IdSchetSubSchetDt),1,2)='18' then sn.IdSchetSubSchetDt 
                                                         else sn.IdSchetSubSchetKt end)
 from tbSpravka sn
inner join tbSFSverka svn on sn.IdSchetSubschetDt=svn.IdSchetSubSchetDt and sn.IdSchetSubschetKt=svn.IdSchetSubSchetKt 
where #tbSver1.idPredpr=sn.IdPredpr and 
      #tbSver1.NumDoc=   
(case when IsNumeric(IsNull(IsNull(sn.NumDocPart,sn.NumDoc) ,'нет номера'))=1
 then dbo.f_CheckNumber(IsNull(IsNull(sn.NumDocPart,sn.NumDoc) ,'нет номера')) 
 else IsNull(IsNull(sn.NumDocPart,sn.NumDoc) ,'нет номера')  end) and 
      #tbSver1.DateDoc=IsNull(sn.DataDocPart,sn.DataDoc) and
      svn.Prizn=1
 and (Substring(dbo.mrfGetKodSS(svn.IdSchetSubSchetDt),1,2)='18'  or Substring(dbo.mrfGetKodSS(svn.IdSchetSubSchetKt),1,2)='18'))
 where  #tbSver1.VidDoc=7



--24.11.2016
--09.06.2017
If @NDSZero=0
Delete from #tbSver1 where IsNull(SumNDS,0)=0
--24.11.2016


Update #tbSver1
    set SumSNDS=IsNull(SumBezNDS,0)+IsNull(SumNDS,0)
 where  VidDoc in (6,7)

Update #tbSver1 set IdTipDoc=9 
where (SumBezNDS>0 or SumNDS>0) and  VidDoc in (6,7)

Update #tbSver1 set IdTipDoc=10 
where (SumBezNDS<0 or SumNDS<0) and  VidDoc in (6,7)



Insert  into #tbSver2
(Number,IdDoc,IdPredpr,NumDoc,DateDoc,UNN,IdPlat,NamePredpr,SumBezNDS,SumNDS,SumSNDS,NSF,NumDoc2,Seria,DocumentType)
Select i.Number,null ,d.idSender,
--13.02.2018
(case when IsNumeric(d.DocNumber)=1  then dbo.f_CheckNumber(d.DocNumber) else d.DocNumber end),
d.DocDate,sp.UNN,sp.IdPlat,sp.NamePredpr,i.TotalCost,i.TotalVat,i.TotalCostVat,
            sp.UNN+'-'+cast(i.CompilingYear as varchar(4))+'-'+Right('000000000'+cast(i.Number as varchar(10)),10),
d.DocNumber,d.Seria,i.DocumentType
from MainDataBase..Invoices i
inner join MainDataBase..Documents d 
 on i.IdSender=d.IdSender and i.CompilingYear=d.CompilingYear and i.Number=d.Number
inner join MainDataBase..tbPredpr sp on i.IdSender=sp.IdPredpr
inner join MainDataBase..history h 
 on i.IdSender=h.IdSender and i.CompilingYear=h.CompilingYear and i.Number=h.Number

where d.DocDate between  @FirstDate and @SecDate and IsNull(i.sv,0)=0
 and i.IdRecipient  in (@MyIdPlat,@IdOblpo) 
--  22.08.2019(Select IdPredpr from MainDataBase..tbNastrPredpr)   --13.08.2019
and i.IdSender not in (Select IdPredpr from MainDataBase..tbNastrPredpr) 
and ((@NDSZero=0 and TotalVat<>0) or (@NDSZero=1))
            and ((@IdPlat is not null and sp.IdPlat=@IdPlat) or (@IdPlat is null and sp.IdPlat<>@MyIdPlat))
and i.DocumentType<>2 and IsNull(h.InvStatus,99) not in (5,6,7,9)

union All

Select i.Number,null ,d.idSender,
--13.02.2018
(case when IsNumeric(d.DocNumber)=1  then dbo.f_CheckNumber(d.DocNumber) else d.DocNumber end),
d.DocDate,sp.UNN,sp.IdPlat,sp.NamePredpr,i.TotalCost,i.TotalVat,i.TotalCostVat,
            sp.UNN+'-'+cast(i.CompilingYear as varchar(4))+'-'+Right('000000000'+cast(i.Number as varchar(10)),10),
d.DocNumber,d.Seria,i.DocumentType
from MainDataBase..Invoices i
inner join MainDataBase..Documents d on i.IdSender=d.IdSender 
and d.CompilingYear=cast(SubString(i.Invoice,11,4) as int)
and d.Number=cast(SubString(i.Invoice,16,10) as bigInt)
inner join MainDataBase..Invoices i1 on i.IdSender=i1.IdSender 
and i1.CompilingYear=cast(SubString(i.Invoice,11,4) as int)
and i1.Number=cast(SubString(i.Invoice,16,10) as bigInt)
inner join MainDataBase..tbPredpr sp on i.IdSender=sp.IdPredpr
inner join MainDataBase..history h 
 on i.IdSender=h.IdSender 
and i.CompilingYear=h.CompilingYear 
and i.Number=h.Number

where d.DocDate between  @FirstDate and @SecDate and IsNull(i.sv,0)=0
and IsNull(i.IdRecipient,i1.IdRecipient)  in (@MyIdPlat,@IdOblpo)
--   22.08.2019(Select IdPredpr from MainDataBase..tbNastrPredpr) 
and i.IdSender not in (Select IdPredpr from MainDataBase..tbNastrPredpr) 
and ((@NDSZero=0 and i.TotalVat<>0) or (@NDSZero=1))
            and ((@IdPlat is not null and sp.IdPlat=@IdPlat) or (@IdPlat is null and sp.IdPlat<>@MyIdPlat))
and i.DocumentType=2 and IsNull(h.InvStatus,99) not in (5,6,7,9)


--12.01.2017
UPDATE    #tbSver2
SET              NumDoc = STUFF(NumDoc, CHARINDEX(' ', NumDoc), 1, '')
WHERE     (CHARINDEX(' ', NumDoc) <> 0)
--12.01.2017

--01.02.2017
Update MainDataBase..history 
  set InvStatus=7
from #tbSver2 s2 where s2.NSF in (Select invoice from MainDataBase..invoices where DocumentType=3 
   and DateTransaction between @FirstDate and @SecDate
   and sv=0 and TotalCostVat<>0)
and MainDataBase..history.IdSender=s2.IdPlat 
and MainDataBase..history.Number=s2.Number

 

--01.02.2017

--01.12.2016
Delete from #tbSver2
   where NSF in (Select invoice from MainDataBase..invoices where DocumentType=3 
   and DateTransaction between @FirstDate and @SecDate
   and sv=0 and TotalCostVat<>0)

--01.12.2016


Update #tbSver2 set  IdTipDoc=9 where SumBezNDS>0

Update #tbSver2 set  IdTipDoc=10 where SumBezNDS<0 

--Update #tbSver2
--  set NumDoc=dbo.f_CheckNumber(NumDoc)


Update #tbSver1 
set NumDoc= (case when IsNumeric(NumDoc)=1 then
(cast(cast (NumDoc as decimal(38,0)) as varchar(50)))
 else NumDoc end)   --where VidDoc=1

Update #tbSver2 
set NumDoc= (case when IsNumeric(NumDoc)=1 then
(cast(cast (NumDoc as decimal(38,0)) as varchar(50)))
 else NumDoc end)
--where IsNull(Seria,'')<>'' 
 

--21.01.2017

if @KS<>0
begin
Update #tbSver2
  set SumNDS10=(Select Sum(SummaVat) from MainDataBase..roster r
    inner join MainDataBase..documents d
             on  d.IdSender=r.IdSender and
                    d.CompilingYear=r.CompilingYear and
                    d.Number=r.Number
      where r.IdSender=#tbSver2.IdPlat 
--              and CompilingYear=Year(#tbSver2.DateDoc)
              and Year(d.docDate)=Year(#tbSver2.DateDoc)
              and r.Number=#tbSver2.Number and Rate=10 and IdRateType=1),
    

         SumNDS20=(Select Sum(SummaVat) from MainDataBase..roster r
    inner join MainDataBase..documents d
             on  d.IdSender=r.IdSender and
                    d.CompilingYear=r.CompilingYear and
                    d.Number=r.Number
      where r.IdSender=#tbSver2.IdPlat 
--              and CompilingYear=Year(#tbSver2.DateDoc)
              and Year(d.docDate)=Year(#tbSver2.DateDoc)
              and r.Number=#tbSver2.Number and Rate=20 and IdRateType=1),

--14.02.217
         SumNDS25=(Select Sum(SummaVat) from MainDataBase..roster r
    inner join MainDataBase..documents d
             on  d.IdSender=r.IdSender and
                    d.CompilingYear=r.CompilingYear and
                    d.Number=r.Number
      where r.IdSender=#tbSver2.IdPlat 
--              and CompilingYear=Year(#tbSver2.DateDoc)
              and Year(d.docDate)=Year(#tbSver2.DateDoc)
              and r.Number=#tbSver2.Number and Rate=25 and IdRateType=1),

--23.10.2019

         SumNDSrst=(Select Sum(SummaVat) from MainDataBase..roster r
    inner join MainDataBase..documents d
             on  d.IdSender=r.IdSender and
                    d.CompilingYear=r.CompilingYear and
                    d.Number=r.Number
      where r.IdSender=#tbSver2.IdPlat 
--              and CompilingYear=Year(#tbSver2.DateDoc)
              and Year(d.docDate)=Year(#tbSver2.DateDoc)
              and r.Number=#tbSver2.Number and IdRateType=4)

where #tbSver2.DocumentType<>2


--22.03.2017
Update #tbSver2
  set SumNDS10=(Select Sum(SummaVat) from MainDataBase..roster r
    inner join MainDataBase..invoices i
             on  i.IdSender=r.IdSender and
                    i.CompilingYear=r.CompilingYear and
                    i.Number=r.Number
    inner join MainDataBase..Documents d
            on i.IdSender=d.IdSender and 
--i.CompilingYear=d.CompilingYear and 
d.Number=cast(SubString(i.Invoice,16,10) as bigInt)
      where r.IdSender=#tbSver2.IdPlat 
--              and CompilingYear=Year(#tbSver2.DateDoc)
              and Year(d.docDate)=Year(#tbSver2.DateDoc)
              and r.Number=#tbSver2.Number and Rate=10 and IdRateType=1),
    

         SumNDS20=(Select Sum(SummaVat) from MainDataBase..roster r
    inner join MainDataBase..invoices i
             on  i.IdSender=r.IdSender and
                    i.CompilingYear=r.CompilingYear and
                    i.Number=r.Number
    inner join MainDataBase..Documents d
            on i.IdSender=d.IdSender and 
--i.CompilingYear=d.CompilingYear and 
d.Number=cast(SubString(i.Invoice,16,10) as bigInt)
      where r.IdSender=#tbSver2.IdPlat 
--              and CompilingYear=Year(#tbSver2.DateDoc)
              and Year(d.docDate)=Year(#tbSver2.DateDoc)
              and r.Number=#tbSver2.Number and Rate=20 and IdRateType=1),

         SumNDS25=(Select Sum(SummaVat) from MainDataBase..roster r
    inner join MainDataBase..invoices i
             on  i.IdSender=r.IdSender and
                    i.CompilingYear=r.CompilingYear and
                    i.Number=r.Number
    inner join MainDataBase..Documents d
            on i.IdSender=d.IdSender and 
--i.CompilingYear=d.CompilingYear and 
d.Number=cast(SubString(i.Invoice,16,10) as bigInt)
      where r.IdSender=#tbSver2.IdPlat 
--              and CompilingYear=Year(#tbSver2.DateDoc)
              and Year(d.docDate)=Year(#tbSver2.DateDoc)
              and r.Number=#tbSver2.Number and Rate=25 and IdRateType=1),

         SumNDSrst=(Select Sum(SummaVat) from MainDataBase..roster r
    inner join MainDataBase..invoices i
             on  i.IdSender=r.IdSender and
                    i.CompilingYear=r.CompilingYear and
                    i.Number=r.Number
    inner join MainDataBase..Documents d
            on i.IdSender=d.IdSender and 
--i.CompilingYear=d.CompilingYear and 
d.Number=cast(SubString(i.Invoice,16,10) as bigInt)
      where r.IdSender=#tbSver2.IdPlat 
--              and CompilingYear=Year(#tbSver2.DateDoc)
              and Year(d.docDate)=Year(#tbSver2.DateDoc)
              and r.Number=#tbSver2.Number and IdRateType=4)



where #tbSver2.DocumentType=2
end
--03.04.2017



Insert into #tbSver3 
(Number,NSF, IdPlatsf,NamePredprsf,UNNsf, NumDocsf, DateDocsf,
              SumBezNDSsf,
              SumNDSsf,SumSNDSsf,SeriaSf,
--NSFB,
              IdPlat, NamePredpr, UNN,NumDoc,DateDoc,
              SumBezNDS,
              SumNDS,SumSNDS, Pr,
               RasBezNDS, RasNDS,IdDoc,VidDoc,IdTipDoc,
               Tara,SumNDS10,SumNDS20,NumDoc1,NumDoc2,IdSchetNDS,SumNDS25,SumNDSrst,
                NamePredprOb,Seria)
Select s2.Number, s2.NSF,s2.IdPlat as IdPlatsf,s2.NamePredpr as NamePredprsf,s2.UNN as UNNsf,s2.NumDoc as NumDocsf,s2.DateDoc as DateDocsf,
              s2.SumBezNDS as SumBezNDSsf,
              s2.SumNDS as SumNDSsf,s2.SumSNDS as SumSNDSsf,s2.Seria,
--s2.NSF,
              s1.IdPlat as IdPlat,s1.NamePredpr as NamePredpr,s1.UNN as UNN,s1.NumDoc,s1.DateDoc,
              s1.SumBezNDS as SumBezNDS,
              s1.SumNDS as SumNDS,s1.SumSNDS as SumSNDS,null,
              null,null,s1.IdDoc,s1.VidDoc,IsNull(s2.IdTipDoc,s1.IdTipDoc),s1.Tara,
              s2.SumNDS10,s2.SumNDS20,s1.NumDoc1,s2.NumDoc2,s1.IdSchetNDS,s2.SumNDS25,SumNDSrst,
              s1.NamePredprOb,s1.Seria
from #tbSver2 s2
full outer join #tbSver1 s1
on s1.IdPlat=s2.IdPlat and s1.NumDoc=s2.NumDoc and s1.DateDoc=s2.DateDoc  and IsNull(s1.IdTipDoc,0)=IsNull(s2.IdTipDoc,0)
order by IsNull(s2.UNN,s1.UNN),IsNull(s2.NumDoc,s1.NumDoc)

if @PrVib=1

Delete from #tbSver3 
 where IdPlat is not null and IdPlat not in (Select IdPlat from #tbSver2)

if @PrVib=2

Delete from #tbSver3
 where  (IdPlat Is not null and NumDoc is not null and not Exists(Select * from #tbSver2 where IdPlat=#tbSver3.IdPlat and NumDoc=#tbSver3.NumDoc))
   or   (IdPlat Is  null and NumDoc is null and not Exists(Select * from #tbSver3  s3 where s3.IdPlat=#tbSver3.IdPlatsf and s3.NumDoc=#tbSver3.NumDocsf))

Update #tbSver3
  set Pr=1 
  where UNNsf  is null and NumDocsf is null and DateDocsf is null 

Update #tbSver3
  set Pr=0 
  where UNN  is null and NumDoc is null and DateDoc is null 



------------------25.05.2017
Update  #tbSver3
   set RasBezNDS=( Select Sum( IsNull(SumBezNDSsf,0))-Sum(Distinct IsNull(SumBezNDS,0)) from #tbSver3 s3
            where IsNull(#tbSver3.NumDocsf,#tbSver3.NumDoc)=IsNull(s3.NumDocsf,s3.NumDoc) and 
                         IsNull(#tbSver3.IdPlatsf,#tbSver3.IdPlat)=IsNull(s3.IdPlatsf,s3.IdPlat)and #tbSver3.IdTipDoc=s3.IdTipDoc
 Group  by IsNull(s3.NumDocsf,s3.NumDoc),IsNull(s3.IdPlatsf,s3.IdPlat) 
having Count(Distinct  IsNull(SumBezNDSsf,0)) >=1 and Count(Distinct  IsNull(SumBezNDS,0)) =1  )
--where VidDoc=1
where #tbSver3.IdTipDoc=9

Update  #tbSver3
   set RasBezNDS=( Select Sum( Distinct IsNull(SumBezNDSsf,0))-Sum( IsNull(SumBezNDS,0)) from #tbSver3 s3
            where IsNull(#tbSver3.NumDocsf,#tbSver3.NumDoc)=IsNull(s3.NumDocsf,s3.NumDoc) and 
                         IsNull(#tbSver3.IdPlatsf,#tbSver3.IdPlat)=IsNull(s3.IdPlatsf,s3.IdPlat)and #tbSver3.IdTipDoc=s3.IdTipDoc
 Group  by IsNull(s3.NumDocsf,s3.NumDoc),IsNull(s3.IdPlatsf,s3.IdPlat)
having Count(Distinct  IsNull(SumBezNDSsf,0)) =1 and Count(Distinct  IsNull(SumBezNDS,0))> =1  )
where #tbSver3.IdTipDoc=9 and #tbSver3.RasBezNDS is null

Update  #tbSver3
   set RasBezNDS=( Select Sum(Distinct IsNull(SumBezNDSsf,0))-Sum(Distinct IsNull(SumBezNDS,0)) from #tbSver3 s3
            where IsNull(#tbSver3.NumDocsf,#tbSver3.NumDoc)=IsNull(s3.NumDocsf,s3.NumDoc) and 
                         IsNull(#tbSver3.IdPlatsf,#tbSver3.IdPlat)=IsNull(s3.IdPlatsf,s3.IdPlat)and #tbSver3.IdTipDoc=s3.IdTipDoc
 Group  by IsNull(s3.NumDocsf,s3.NumDoc),IsNull(s3.IdPlatsf,s3.IdPlat)
 having Count(Distinct  IsNull(SumBezNDSsf,0)) >1 and Count(Distinct  IsNull(SumBezNDS,0)) >1  )
where #tbSver3.IdTipDoc=9 and #tbSver3.RasBezNDS is null



Update  #tbSver3
   set RasNDS=( Select Sum( IsNull(SumNDSsf,0))-Sum(Distinct IsNull(SumNDS,0)) from #tbSver3 s3
            where IsNull(#tbSver3.NumDocsf,#tbSver3.NumDoc)=IsNull(s3.NumDocsf,s3.NumDoc) and 
                         IsNull(#tbSver3.IdPlatsf,#tbSver3.IdPlat)=IsNull(s3.IdPlatsf,s3.IdPlat)and #tbSver3.IdTipDoc=s3.IdTipDoc
 Group  by IsNull(s3.NumDocsf,s3.NumDoc),IsNull(s3.IdPlatsf,s3.IdPlat) 
having Count(Distinct  IsNull(SumNDSsf,0)) >=1 and Count(Distinct  IsNull(SumNDS,0)) =1  )
where #tbSver3.IdTipDoc=9

Update  #tbSver3
   set RasNDS=( Select Sum( Distinct IsNull(SumNDSsf,0))-Sum( IsNull(SumNDS,0)) from #tbSver3 s3
            where IsNull(#tbSver3.NumDocsf,#tbSver3.NumDoc)=IsNull(s3.NumDocsf,s3.NumDoc) and 
                         IsNull(#tbSver3.IdPlatsf,#tbSver3.IdPlat)=IsNull(s3.IdPlatsf,s3.IdPlat)and #tbSver3.IdTipDoc=s3.IdTipDoc
 Group  by IsNull(s3.NumDocsf,s3.NumDoc),IsNull(s3.IdPlatsf,s3.IdPlat) 
having Count(Distinct  IsNull(SumNDSsf,0)) =1 and Count(Distinct  IsNull(SumNDS,0))> =1  )
where #tbSver3.IdTipDoc=9 and #tbSver3.RasNDS is null

Update  #tbSver3
   set RasNDS=( Select Sum(Distinct IsNull(SumNDSsf,0))-Sum(Distinct IsNull(SumNDS,0)) from #tbSver3 s3
            where IsNull(#tbSver3.NumDocsf,#tbSver3.NumDoc)=IsNull(s3.NumDocsf,s3.NumDoc) and 
                         IsNull(#tbSver3.IdPlatsf,#tbSver3.IdPlat)=IsNull(s3.IdPlatsf,s3.IdPlat)and #tbSver3.IdTipDoc=s3.IdTipDoc
 Group  by IsNull(s3.NumDocsf,s3.NumDoc),IsNull(s3.IdPlatsf,s3.IdPlat) 
having Count(Distinct  IsNull(SumNDSsf,0)) >1 and Count(Distinct  IsNull(SumNDS,0)) > 1  )
where #tbSver3.IdTipDoc=9 and #tbSver3.RasNDS is null


Update  #tbSver3
   set RasBezNDS=( Select Sum( IsNull(SumBezNDSsf,0))-Sum(Distinct IsNull(SumBezNDS,0)) from #tbSver3 s3
            where IsNull(#tbSver3.NumDocsf,#tbSver3.NumDoc)=IsNull(s3.NumDocsf,s3.NumDoc) and 
                         IsNull(#tbSver3.IdPlatsf,#tbSver3.IdPlat)=IsNull(s3.IdPlatsf,s3.IdPlat)and #tbSver3.IdTipDoc=s3.IdTipDoc
 Group  by IsNull(s3.NumDocsf,s3.NumDoc),IsNull(s3.IdPlatsf,s3.IdPlat) 
having Count(Distinct  IsNull(SumBezNDSsf,0)) >=1 and Count(Distinct  IsNull(SumBezNDS,0)) =1  )
where #tbSver3.IdTipDoc=10

Update  #tbSver3
   set RasBezNDS=( Select Sum( Distinct IsNull(SumBezNDSsf,0))-Sum( IsNull(SumBezNDS,0)) from #tbSver3 s3
            where IsNull(#tbSver3.NumDocsf,#tbSver3.NumDoc)=IsNull(s3.NumDocsf,s3.NumDoc) and 
                         IsNull(#tbSver3.IdPlatsf,#tbSver3.IdPlat)=IsNull(s3.IdPlatsf,s3.IdPlat)and #tbSver3.IdTipDoc=s3.IdTipDoc
 Group  by IsNull(s3.NumDocsf,s3.NumDoc),IsNull(s3.IdPlatsf,s3.IdPlat) 
having Count(Distinct  IsNull(SumBezNDSsf,0)) =1 and Count(Distinct  IsNull(SumBezNDS,0))> =1  )
where #tbSver3.IdTipDoc=10 and #tbSver3.RasBezNDS is null

Update  #tbSver3
   set RasBezNDS=( Select Sum(Distinct IsNull(SumBezNDSsf,0))-Sum(Distinct IsNull(SumBezNDS,0)) from #tbSver3 s3
            where IsNull(#tbSver3.NumDocsf,#tbSver3.NumDoc)=IsNull(s3.NumDocsf,s3.NumDoc) and 
                         IsNull(#tbSver3.IdPlatsf,#tbSver3.IdPlat)=IsNull(s3.IdPlatsf,s3.IdPlat)and #tbSver3.IdTipDoc=s3.IdTipDoc
 Group  by IsNull(s3.NumDocsf,s3.NumDoc),IsNull(s3.IdPlatsf,s3.IdPlat) 
having Count(Distinct  IsNull(SumBezNDSsf,0)) >1 and Count(Distinct  IsNull(SumBezNDS,0)) > 1  )
where #tbSver3.IdTipDoc=10 and #tbSver3.RasBezNDS is null
	
Update  #tbSver3
   set RasNDS=( Select Sum( IsNull(SumNDSsf,0))-Sum(Distinct IsNull(SumNDS,0)) from #tbSver3 s3
            where IsNull(#tbSver3.NumDocsf,#tbSver3.NumDoc)=IsNull(s3.NumDocsf,s3.NumDoc) and 
                         IsNull(#tbSver3.IdPlatsf,#tbSver3.IdPlat)=IsNull(s3.IdPlatsf,s3.IdPlat)and #tbSver3.IdTipDoc=s3.IdTipDoc
 Group  by IsNull(s3.NumDocsf,s3.NumDoc),IsNull(s3.IdPlatsf,s3.IdPlat) 
having Count(Distinct  IsNull(SumNDSsf,0)) >=1 and Count(Distinct  IsNull(SumNDS,0)) =1  )
where #tbSver3.IdTipDoc=10

Update  #tbSver3
   set RasNDS=( Select Sum( Distinct IsNull(SumNDSsf,0))-Sum( IsNull(SumNDS,0)) from #tbSver3 s3
            where IsNull(#tbSver3.NumDocsf,#tbSver3.NumDoc)=IsNull(s3.NumDocsf,s3.NumDoc) and 
                         IsNull(#tbSver3.IdPlatsf,#tbSver3.IdPlat)=IsNull(s3.IdPlatsf,s3.IdPlat)and #tbSver3.IdTipDoc=s3.IdTipDoc
 Group  by IsNull(s3.NumDocsf,s3.NumDoc),IsNull(s3.IdPlatsf,s3.IdPlat) 
having Count(Distinct  IsNull(SumNDSsf,0)) =1 and Count(Distinct  IsNull(SumNDS,0))> =1  )
where #tbSver3.IdTipDoc=10 and #tbSver3.RasNDS is null

Update  #tbSver3
   set RasNDS=( Select Sum(Distinct IsNull(SumNDSsf,0))-Sum(Distinct IsNull(SumNDS,0)) from #tbSver3 s3
            where IsNull(#tbSver3.NumDocsf,#tbSver3.NumDoc)=IsNull(s3.NumDocsf,s3.NumDoc) and 
                         IsNull(#tbSver3.IdPlatsf,#tbSver3.IdPlat)=IsNull(s3.IdPlatsf,s3.IdPlat)and #tbSver3.IdTipDoc=s3.IdTipDoc
 Group  by IsNull(s3.NumDocsf,s3.NumDoc),IsNull(s3.IdPlatsf,s3.IdPlat) 
having Count(Distinct  IsNull(SumNDSsf,0)) >1 and Count(Distinct  IsNull(SumNDS,0)) >1  )
where #tbSver3.IdTipDoc=10 and #tbSver3.RasNDS is null


---------------------------25.05.2017

Update  #tbSver3
   set RasBezNDS=null where RasBezNDS=0

Update  #tbSver3
   set RasNDS=null where RasNDS=0



--24.10.2019
-------------------------
-----------------------
if @IsklTara =1
begin
Update #tbSver3
   set SumBezNDS=SumBezNDS-Tara, 
          SumSNDS=SumSNDS-Tara,
          RasBezNDS=RasBezNDS+Tara

where 
--IsNull(RasNDS,0)=0 and 
IsNull(RasBezNDS,0)<>0  --and abs(RasBezNDS)=Tara
--    and IsNull(SumSNDSsf,0)<>0 and IsNull(SumSNDS,0)<>0
        and  VidDoc=1 and SumBezNDS>0

Update #tbSver3
   set SumBezNDS=SumBezNDS+Tara, 
          SumSNDS=SumSNDS+Tara,
          RasBezNDS=RasBezNDS-Tara

where 
-- IsNull(RasNDS,0)=0 and
 IsNull(RasBezNDS,0)<>0 --and abs(RasBezNDS)=Tara
--    and IsNull(SumSNDSsf,0)<>0 and IsNull(SumSNDS,0)<>0
        and  VidDoc=1 and SumBezNDS<0

/*
Update #tbSver3
   set RasBezNDS=null ,
         IsTara=1
where  Isnull(NumDocsf,NumDoc) in (Select Isnull(NumDocsf,NumDoc) from #tbSver3 
           where
--       IsNull(RasNDS,0)=0 and
       IsNull(RasBezNDS,0)<>0 and abs(RasBezNDS)=Tara and Tara<>0
    and IsNull(SumSNDSsf,0)<>0 and IsNull(SumSNDS,0)<>0
        and  VidDoc=1 and IsNull(IdPlatsf,IdPlat)=IsNull(#tbSver3.IdPlatsf,#tbSver3.IdPlat))
*/
end
--

--

--02.12.2016


Select IsNull(IdPlatsf,IdPlat) as IdPlat,IsNull(NumDocSf,NumDoc) as NumDoc,
Sum(Distinct IsNull(RasBezNDS,0)) as RasBezNDS,
Sum(Distinct IsNull(RasNDS,0)) as RasNDS 
into #tbSver4 
from #tbSver3 
   where IsNull(RasBezNDS,0)<>0 and IsNull(RasNDS,0)<>0
--and IsNull(VidDoc,1)=1
 group by IsNull(IdPlatsf,IdPlat),IsNull(NumDocSf,NumDoc)
having (Count(*)>1 and( Max(IsNull(Pr,0))=1 and Min(IsNull(Pr,0))=0) or (Max(IsNull(VidDoc,0))=1 and Min(IsNull(VidDoc,0))=0))

Update #tbSver3 set RasNDS=null,RasBezNDS=null
 where IsNull(NumDocsf,NumDoc) in (Select NumDoc from #tbSver4 
 where RasNDS=0 and RasBezNDS=0 
and IdPlat=IsNull(#tbSver3.IdPlatsf,#tbSver3.IdPlat)) 
--and IsNull(VidDoc,1)=1

Drop Table #tbSver4

--
Select  Max(Number) as Number,IdPlatSf,NumDocsf,DateDocSf,SumBezNDSsf,SumNDSsf
into #tbSver5
from #tbSver3 where IsNull(pr,0)=0   and IsNull(RasBezNDS,0)=0 and IsNull(RasNDS,0)=0
Group by IdPlatSf,NumDocsf,DateDocSf,SumBezNDSsf,SumNDSsf
having Count(*)>1

---18.11.2016    Update

Update #tbSver3
   set IdPlat=null,
         NamePredpr=null,
         UNN=null,
         NumDoc=null,
         DateDoc=null,
         RasBezNDS=SumBezNDSsf,
         RasNDS=SumNDSsf,
         SumBezNDS=null,
         SumNDS=null,
         SumSNDS=null,
         Tara=null,
          pr=0,
          IdDoc=null,
         VidDoc=null
    where NumDocSf in (Select NumDocsf from #tbSver5 where IdPlatsf=#tbSver3.IdPlatsf and DateDocsf=#tbSver3.DateDocSf and #tbSver3.Number<Number
   and SumBezNDSsf=#tbSver3.SumBezNDSsf and SumNDSsf=#tbSver3.SumNDSsf)
/*
Update #tbSver3
     set RasBezNDS=IsNull(SumBezNDSsf,0)-IsNull(SumBezNDS,0),
            RasNDS=IsNull(SumNDSsf,0)-IsNull(SumNDS,0)
    where NumDocSf in (Select NumDocsf from #tbSver5 where IdPlatsf=#tbSver3.IdPlatsf and DateDocsf=#tbSver3.DateDocSf and #tbSver3.Number=Number
   and SumBezNDSsf=#tbSver3.SumBezNDSsf and SumNDSsf=#tbSver3.SumNDSsf)
--where SumBezNDS is not null or SumNDS is not null
*/
Drop Table #tbSver5



Select  Max(Number) as Number,IdPlat,NumDoc,DateDoc,SumBezNDS,SumNDS
 into #tbSver6
from #tbSver3 where pr is null  and VidDoc=1 and VidDoc=1 and IsNull(RasBezNDS,0)=0 and IsNull(RasNDS,0)=0
Group by IdPlat,NumDoc,DateDoc,SumBezNDS,SumNDS
having Count(*)>1

---18.11.2016    Update

Update #tbSver3
   set IdPlat=null,
         NamePredpr=null,
         UNN=null,
         NumDoc=null,
         DateDoc=null,
--         RasBezNDS=SumBezNDSsf,
--         RasNDS=SumNDSsf,
        SumSNDS=null,
        SumBezNDS=null,
        SumNDS=null,
         Tara=null,
          pr=0,
          IdDoc=null,
         VidDoc=null
     where NumDoc in (Select NumDoc  from #tbSver6 where IdPlat=#tbSver3.IdPlat and DateDoc=#tbSver3.DateDoc and #tbSver3.Number<Number
   and SumBezNDS=#tbSver3.SumBezNDS and SumNDS=#tbSver3.SumNDS)
/*
Update #tbSver3
     set RasBezNDS=IsNull(SumBezNDSsf,0)-IsNull(SumBezNDS,0),
            RasNDS=IsNull(SumNDSsf,0)-IsNull(SumNDS,0)
     where NumDoc in (Select NumDoc  from #tbSver6 where IdPlat=#tbSver3.IdPlat and DateDoc=#tbSver3.DateDoc and #tbSver3.Number=Number
   and SumBezNDS=#tbSver3.SumBezNDS and SumNDS=#tbSver3.SumNDS)
*/
--where SumBezNDS is not null or SumNDS is not null


Drop Table #tbSver6

--21.11.2016


Select  Max(IdDoc) as IdDoc,IdPlatSf,NumDocsf,DateDocSf,SumBezNDSsf,SumNDSsf
 into #tbSver7
from #tbSver3 where pr is null  --and VidDoc=1 -- and IsNull(RasBezNDS,0)=0 and IsNull(RasNDS,0)=0
Group by IdPlatSf,NumDocsf,DateDocSf,SumBezNDSsf,SumNDSsf
having Count(*)>1

---18.11.2016    Update

Update #tbSver3
   set IdPlatsf=null,
         NamePredprsf=null,
         UNN=null,
         NumDocsf=null,
         DateDocsf=null,
        SumBezNDSsf=null,
        SumNDSsf=null,
        SumSNDSsf=null,
       NSF=null,
       pr=0
     where NumDocSf in (Select NumDocsf from #tbSver7 where IdPlatsf=#tbSver3.IdPlatsf and DateDocsf=#tbSver3.DateDocSf and #tbSver3.IdDoc<IdDoc
   and SumBezNDSsf=#tbSver3.SumBezNDSsf and SumNDSsf=#tbSver3.SumNDSsf)


Drop Table #tbSver7


Update  #tbSver3
   set RasBezNDS=null where RasBezNDS=0

Update  #tbSver3
   set RasNDS=null where RasNDS=0

Update  #tbSver3
  set Ch=1 where IsNull(RasBezNDS,0)= 0 and IsNull(RasNDS,0)=0
and NSF is not null

--16.12.2016
if @KS<>0
Update #tbSver3 
   set NumDoc1=(Select Min(s3.NumDoc1)
--   set NumDoc1=(Select Distinct s3.NumDoc1
--   set NumDoc1=(Select coalesce(null,s3.NumDoc1)
     from #tbSver3 s3 where
                #tbSver3.NumDocsf=s3.NumDoc  and #tbSver3.IdPlatsf=s3.IdPlat )--   and s3.NSF is null ) --  and IdPlatsf=637
where NSF is not null and NumDoc1 is null --and ch=1


--16.12.2016
--09.02.2017

if @KS=1
Update #tbSver3 
   set IdSchetNDS=@IdS
where NSF is not null and IdSchetNDS is null  

if @KS>1


--03.04.2017
Update #tbSver3 
--   set IdSchetNDS=(Select Distinct s3.IdSchetNDS
   set IdSchetNDS=(Select Min(s3.IdSchetNDS)
     from #tbSver3 s3 where
                #tbSver3.NumDocsf=s3.NumDoc  and #tbSver3.IdPlatsf=s3.IdPlat ) --  and s3.NSF is null ) --  and IdPlatsf=637
where NSF is not null and IdSchetNDS is null  --and ch=1

------20.12.2016


--16.10.2017

Select Max(Id1) as Id1,NSF,IdPlatsf,NumDocsf,DateDocsf,SumBezNDSsf,SumNDSsf,SumSNDSsf --,Count(*)as c
into #tbSver8
from #tbSver3
where
IdPlat is null and NumDoc is null and DateDoc is null and SumBezNDS is null and SumNDS is null 
and ch=1
group by NSF,IdPlatsf,NumDocsf,DateDocsf,SumBezNDSsf,SumNDSsf,SumSNDSsf
having Count(*)>1

Delete from #tbSver3 
where Id1 in (Select s3.Id1 from #tbSver3 s3 inner join #tbSver8 s8 on s3.NSF=s8.NSF and s3.IdPlatsf=s8.IdPlatsf and 
s3.NumDocsf=s8.NumDocsf and s3.DateDocsf=s8.DateDocsf and s3.SumBezNDSsf=s8.SumBezNDSsf and s3.SumNDSsf=s8.SumNDSsf and s3.Id1<s8.Id1 and
s3.IdPlat is null and s3.NumDoc is null and s3.DateDoc is null and s3.SumBezNDS is null and s3.SumNDS is null 
and s3.ch=1)
Drop Table #tbSver8

Select  @Tara=Sum(case when IdTipDoc=9 then Tara else -Tara end)  from #tbSver3 where   Tara<>0-- IsTara =1
Select @Tara as 'Tara'



Select * from #tbSver3  where Tara <>0 --where IsTara=1--where SumNDSRst is not null
/*
Select Id1,ch,NSF,IsNull(NamePredpr,NamePredprsf) as NamePredpr, IsNull(UNN,UNNsf) as UNN ,IsNull(NumDoc,NumDocsf) as NumDoc,IsNull(DateDoc,DateDocsf) as DateDoc,
SumBezNDSsf,SumBezNDS,SumNDSsf,SumNDS,SumSNDSsf,SumSNDS,RasBezNDS,RasNDS,chColor,NamePredprOb,Seria,SeriaSf
from #tbSver3
order by IsNull(NamePredpr,NamePredprsf),
len(IsNull(NumDoc,NumDocsf)),IsNull(NumDoc,NumDocsf),Pr
*/

/*
Drop Table #tbSver1
Drop Table #tbSver2
Drop Table #tbSver3

*/