SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--存储过程.HO_HistoryBatchMove
--Alte 8058 2021.4.2 转移住院数据到离线表
ALTER  PROCEDURE [HO_HistoryBatchMove_2]
  @aDate datetime  = 0
AS
begin
  --------------------判断是否为主数据库,如果为辅助数据库则直接退出
DECLARE @preferredReplica int=1
if exists(select * from master.sys.sysobjects where name='fn_hadr_is_primary_replica')
	SET @preferredReplica =  isnull(master.sys.fn_hadr_is_primary_replica(DB_NAME()),1)
IF (@preferredReplica = 0)
BEGIN
	return 0
END
----------------------------
--费用明细中没发药的数据不转移
--住院病人两天前收费员缴款结帐的出院病人数据转移走  保留结帐31天内的数据

  declare @tmpCols Table(fName varchar(20),fNum1 int,fNum2 int)
  
  insert into @tmpCols(fName,fNum1,fNum2)
  select Case when SUBSTRING(b.name,1,1)='D' then upper(SUBSTRING(b.name,1,4)) else upper(SUBSTRING(b.name,1,3)) end fname
    ,COUNT(1) FCOUNT,0
  from syscolumns a
  join sysobjects b on b.id = a.id
  where b.name in('VAJ2','VAI2','VAK1','VBL1','VAF2','VBI2','CBM2','VBQ2','VBD2','VBG1','VBV1','VEG2','VEH2')
    and b.type = 'U'
  group by b.name
    
  update a
  set a.fNum2 = b.FCOUNT
  from @tmpCols a
  join (select Case when SUBSTRING(b.name,1,1)='D' then upper(SUBSTRING(b.name,1,4)) else upper(SUBSTRING(b.name,1,3)) end name ,COUNT(1) FCOUNT
        from syscolumns a
        join sysobjects b on b.id = a.id
        where b.name in('VAJ2_2','VAI2_2','VAK2','VBL2','VAF2_2','VBI2_2','CBM2_2','VBQ2_2','VBD2_2','VBG2','VBV2','VEG2_2','VEH2_2')
          and b.type = 'U'
        group by b.name) b on b.name = a.fName
  
  if exists(select * from @tmpCols where fNum1 <> fNum2)
  begin
    RAISERROR('存在表1和表2字段不一致的问题.',16,1) with nowait
    return 99
  end
  
  declare @tmpColLen Table(fName varchar(20),fColN varchar(20),uType1 smallint,ulength1 smallint,uPrec1 smallint,uScale1 int
                       ,uType2 smallint,ulength2 smallint,uPrec2 smallint,uScale2 int)
  
  insert into @tmpColLen(fName,fColN,uType1,ulength1,uPrec1,uScale1,uType2,ulength2,uPrec2,uScale2)
  select Case when SUBSTRING(b.name,1,1)='D' then upper(SUBSTRING(b.name,1,4)) else upper(SUBSTRING(b.name,1,3)) end fname
    ,a.name,a.xusertype,a.length,a.prec,a.scale,0,0,0,0
  from syscolumns a
  join sysobjects b on b.id = a.id
  where b.name in('VAJ2','VAI2','VAK1','VBL1','VAF2','VBI2','CBM2','VBQ2','VBD2','VBG1','VBV1','VEG2','VEH2')
    and b.type = 'U'
    
  update a
  set a.uType2 = b.xusertype,a.ulength2 = b.length,a.uPrec2 = b.prec,a.uScale2 = b.scale
  from @tmpColLen a
  join (select Case when SUBSTRING(b.name,1,1)='D' then upper(SUBSTRING(b.name,1,4)) else upper(SUBSTRING(b.name,1,3)) end name,a.name ColN
          ,a.xusertype,a.length,a.prec,a.scale
        from syscolumns a
        join sysobjects b on b.id = a.id
        where b.name in('VAJ2_2','VAI2_2','VAK2','VBL2','VAF2_2','VBI2_2','CBM2_2','VBQ2_2','VBD2_2','VBG2','VBV2','VEG2_2','VEH2_2')
          and b.type = 'U'
       ) b on b.name = a.fName and b.ColN = a.fColN 
  if exists(select * from @tmpColLen where uType1 <> uType2 or ulength1 <> ulength2 or uPrec1 <> uPrec2 or uScale1 <> uScale2 )
  begin
    RAISERROR('存在表1和表2中对应字段类型不一致的问题.',16,1) with nowait
    return 100
  end     
  
  declare @mzDay int, @zyDay int, @yfDay int, @mzDate datetime, @zyDate datetime, @bgDate datetime

  set @zyDay = 31 --住院
  
  if @aDate = 0 set @aDate = Getdate()

  set @mzDate = Convert(varchar(10), DATEADD(Day,-@mzDay,@aDate), 21)+' 00:00:00'
  set @zyDate = Convert(varchar(10), DATEADD(Day,-@zyDay,@aDate), 21)+' 00:00:00'
  
  set @bgDate = Convert(varchar(10), DATEADD(Day,-1,@aDate), 21)+' 00:00:00'

  create table #ExVAK(VAK01 int primary key nonclustered, VAA07 int, ACF01 tinyint)
  create table #ExVAI(VAI01 int primary key nonclustered, ACF01 tinyint, VAA07 int, VAJ05 tinyint)
  create table #ExVAF(VAF01 int primary key nonclustered, VAF51 int, CBM01 int, VAA07 int, ACF01 tinyint)
  create table #ExCBM(CBM01 int primary key nonclustered)
  create table #ExVAE(VAE01 int primary key nonclustered)
  
  insert into #ExVAK(VAK01,VAA07,ACF01)
  select top 1000 VAK01,VAA07,2
  from VAK1 a
  join VAE1 b on b.VAE01 = a.VAA07
  where b.VAE44 = 5 And a.VAK18 >= 1 And a.VAK06 >= 3 And a.VAK13 <= @zyDate
   -- and not exists(select * from VAJ2 c where c.VAK01 = a.VAK01 and c.BDN01 <= '3' and c.VAJ53 = 0)
    and not exists(select * from VBL1 d where d.VAK01 = a.VAK01 and d.VBL17 = 0)  

  if not exists(select * from #ExVAK)
  begin
    if object_id('tempdb..#ExVAK') is not null drop table #ExVAK
    if object_id('tempdb..#ExVAI') is not null drop table #ExVAI
    if object_id('tempdb..#ExVAF') is not null drop table #ExVAF
    if object_id('tempdb..#ExCBM') is not null drop table #ExCBM
    if object_id('tempdb..#ExVAE') is not null drop table #ExVAE
    return 0
  end

  insert into #ExVAI(VAI01,ACF01,VAA07,VAJ05)
  select distinct a.VAI01,b.ACF01,a.VAA07,0
  from VAJ2 a
  join #ExVAK b on a.VAK01 = b.VAK01
  union all
  select a.VAI01,a.ACF01,a.VAA07,9
  from VAI2 a
  where a.VAI16 = 9
    and not exists(select * from VAJ2 b where b.VAI01 = a.VAI01 and b.VAK01 > 0)
  
  if (@@ERROR <> 0)
  begin   
    return 1
  end

  insert into #ExVAE(VAE01)
  select distinct VAA07
  from #ExVAK
  where ACF01 = 2

  insert into #ExCBM(CBM01)
  select distinct a.CBM01
  from VAF2 a
  join #ExVAE b on a.VAF06 = b.VAE01
  where a.VAF04 = 2

  insert into #ExVAF(VAF01,VAF51,CBM01,VAA07,ACF01)
  select b.VAF01,b.VAF51,b.CBM01,b.VAF06,b.VAF04
  from #ExCBM a
  join VAF2 b on a.CBM01 = b.CBM01
  
  select a.* into #hokbmVAK
  from VAK1 a
  join #ExVAK b on b.VAK01 = a.VAK01
  
  select a.* into #hokbmVBL
  from VBL1 a
  join #ExVAK b on b.VAK01 = a.VAK01
  
  select a.* into #hokbmVAI
  from VAI2 a
  join #ExVAI b on b.VAI01 = a.VAI01
  
  select a.* into #hokbmVAJ
  from VAJ2 a
  join #ExVAI b on b.VAI01 = a.VAI01

  select a.* into #hokbmVEG
  from VEG2 a
  join #hokbmVAJ b on b.VAJ01=a.VAJ01
  
  select a.* into #hokbmCBM
  from CBM2 a
  join #ExCBM b on b.CBM01 = a.CBM01
  
  select a.* into #hokbmVAF
  from VAF2 a
  join #ExVAF b on b.VAF01 = a.VAF01

  select a.* into #hokbmVEH
  from VEH2 a
  join #ExVAF b on b.VAF01 = a.VAF01
  
  select a.* into #hokbmVBI
  from VBI2 a
  join #ExVAF b on b.VAF01 = a.VAF01
  
  select a.* into #hokbmVBQ
  from VBQ2 a
  where Exists(select * from #ExVAF b where a.VBQ01 = b.VAF51)
    or exists(select * from #hokbmCBM c where c.VBQ01 = a.VBQ01)
  select a.* into #hokbmVBD
  from VBD2 a
  join #ExVAF b on b.VAF01 = a.VAF01
  
  select * into #hokbmVBG
  from VBG1
  where VBG05 < @bgDate

  select a.* into #hokbmVBV
  from VBV1 a
  where exists(select * from #ExVAF b where b.VAF01 = a.VAF01)
  or not exists(select 1 from VAF2 where VAF01 =a.VAF01) 

  
begin tran
--VAK1
  insert into VAK2(VAK01, VAA01, VAA07, VAI04, FAB03, VAK06, VAK07, VAK08, VAK09, VAK10, BCE02A, BCE03A, 
     VAK13, VAK01A, VAK15, VAK16, VAK17, VAK18, VAK19, VAK20, VAK21, PNRCP, VAK23, VAK24, BCE01A,VAK26,VAK27
     ,VAK28,FAB03A,PNRCPA,BCE03B,FAC01,SFLAG,SCF01,BDP02,VAK36)
  select a.VAK01, a.VAA01, a.VAA07, a.VAI04, a.FAB03, a.VAK06, a.VAK07, a.VAK08, a.VAK09, a.VAK10, a.BCE02A, a.BCE03A, 
     a.VAK13, a.VAK01A, a.VAK15, a.VAK16, a.VAK17, a.VAK18, a.VAK19, a.VAK20, a.VAK21, a.PNRCP, a.VAK23, a.VAK24, a.BCE01A
     ,a.VAK26,a.VAK27,a.VAK28,a.FAB03A,a.PNRCPA,a.BCE03B,a.FAC01,a.SFLAG,a.SCF01,a.BDP02,a.VAK36
  from #hokbmVAK a

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 1
  end
  
--VBL1
  insert into VBL2(VBL01, VBL02, VBL03, VBL04, VBL05, VAA01, VAA07, BCK01, BAQ03, BBO02, VBL11, VBL12, VBL13,
   VBL14, VBL15, VAK01, VBL17, VBL18, VBL19, VBL01A, BCE01, BCE02, BCE03, VBL24, VBL25, VBL26, 
   VBL27, PNRCP, VBL29, VBU01, VCI01,VBL32,FAF01,VBL01B,VBL35)
  select a.VBL01, a.VBL02, a.VBL03, a.VBL04, a.VBL05, a.VAA01,a.VAA07, a.BCK01, a.BAQ03, a.BBO02, a.VBL11
    , a.VBL12, a.VBL13, a.VBL14, a.VBL15, a.VAK01, a.VBL17, a.VBL18, a.VBL19, a.VBL01A, a.BCE01, a.BCE02
    , a.BCE03, a.VBL24, a.VBL25, a.VBL26, a.VBL27, a.PNRCP, a.VBL29, a.VBU01, a.VCI01,a.VBL32,a.FAF01,a.VBL01B,a.VBL35
  from #hokbmVBL a

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 3
  end  
  
--VAI
  insert into VAI2_2(VAI01, VAA01, VAA07, VAI04, VAI05, BCK01A, BCK01B, BCE02A, BCE03A, BCE02B, BCE03B,
   VAI12, VAI13, VAI14, VAI01A, VAI16, VAI17, VAI18, CBM01, ACF01, VAI22, VAI23, VAI24,VAK01,VAI25
   ,VAI01B,VAI01C,BCE01C,BCE03C,BCE01D,BCE03D,VAP01,IAI03,VAI01D,VAI35,SCF01,VAI37 )
  select a.VAI01, a.VAA01, a.VAA07, a.VAI04, a.VAI05, a.BCK01A, a.BCK01B, a.BCE02A, a.BCE03A, a.BCE02B
   ,a.BCE03B, a.VAI12, a.VAI13, a.VAI14, a.VAI01A, a.VAI16, a.VAI17, a.VAI18, a.CBM01, a.ACF01, a.VAI22
   , a.VAI23, a.VAI24,a.VAK01,a.VAI25,a.VAI01B,a.VAI01C,a.BCE01C,a.BCE03C,a.BCE01D,a.BCE03D,a.VAP01,a.IAI03 
   ,a.VAI01D,a.VAI35,a.SCF01,a.VAI37
  from #hokbmVAI a

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 5
  end  
--VAJ
  insert into VAJ2_2(VAJ01, VAA01, VAA07, VAJ04, VAJ05, ROWNR, VAJ01A, VAJ01B, VAJ09, 
    VAJ10, VAI01, VAF01, VAK01, ACF01, VAJ15, BCK01A, BCK01B, BDN01, BBY01,
    BCJ02, VAJ21, VAJ22, VAJ23, VAJ24, VAJ25, VAJ26, VAJ27, VAJ28, VAJ29, 
    VAJ30, VAJ31, VAJ32, VAJ33, VAJ34, VAJ35, VAJ36, VAJ37, VAJ38, VAJ39, 
    VAJ40, VAJ41, BCE03A, BCK01C, BCE02B, BCE03B, VAJ46, VAJ47, VAJ48, BCK01D, 
    BCE03C, VAJ51, VAJ52, VAJ53, VAJ54, BCE02D, BCE03D, VAJ57, FAB03, VAJ59, 
    BCE02C, VAJ61, VAJ62, BCK01E, VAJ64,VAJ65,DSK01,VAJ67,BCE01E,BCE03E,BCK01F,BCQ04,VAJ72,VAJ73,VAJ74
	,BCE03F,VAJ76,VAJ77,VAJ78,VAJ79,VAJ80)
  select a.VAJ01, a.VAA01, a.VAA07, a.VAJ04, a.VAJ05, a.ROWNR, a.VAJ01A, a.VAJ01B, a.VAJ09
    ,a.VAJ10, a.VAI01, a.VAF01, a.VAK01, a.ACF01, a.VAJ15, a.BCK01A, a.BCK01B, a.BDN01, a.BBY01
    ,a.BCJ02, a.VAJ21, a.VAJ22, a.VAJ23, a.VAJ24, a.VAJ25, a.VAJ26, a.VAJ27, a.VAJ28, a.VAJ29
    ,a.VAJ30, a.VAJ31, a.VAJ32, a.VAJ33, a.VAJ34, a.VAJ35, a.VAJ36, a.VAJ37, a.VAJ38, a.VAJ39
    ,a.VAJ40, a.VAJ41, a.BCE03A, a.BCK01C, a.BCE02B, a.BCE03B, a.VAJ46, a.VAJ47, a.VAJ48, a.BCK01D
    ,a.BCE03C, a.VAJ51, a.VAJ52, a.VAJ53, a.VAJ54, a.BCE02D, a.BCE03D, a.VAJ57, a.FAB03, a.VAJ59
    ,a.BCE02C, a.VAJ61, a.VAJ62, a.BCK01E, a.VAJ64,a.VAJ65,a.DSK01,a.VAJ67,a.BCE01E,a.BCE03E,a.BCK01F
	,a.BCQ04,a.VAJ72,a.VAJ73,a.VAJ74,a.BCE03F,a.VAJ76,a.VAJ77,a.VAJ78,a.VAJ79,a.VAJ80
  from #hokbmVAJ a

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 7
  end
--VEG2
  insert INTO VEG2_2(VAJ01,VEG02,VEG03,VEG04,VEB01,VEG06,VEG07,VEG08,VEG09,VEG10,VEG11,VEG12,VEG13,VEG14,VEG15)  
  SELECT VAJ01,VEG02,VEG03,VEG04,VEB01,VEG06,VEG07,VEG08,VEG09,VEG10,VEG11,VEG12,VEG13,VEG14,VEG15
  from #hokbmVEG

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 7
  end   
--CBM1
  insert into CBM2_2(CBM01, VAA01, VAA07, VAP01, ACF01, CBM06, CBM07
    , CBM08, CBM09, BCK01A, BCK01B, BCK01C, BCE01, BCE03
    , CBM15, CBM16, CBM17, CBM18, CBM19, PRNCP, CBM21,BJW02,VCY01,VBQ01,IAI03
    ,BCE01A,CBM27,CBM28,CBM29,CBM30,CBM31,CBM32,CBM33,CBM34,CBM35,CBM36,CBM37,BCE01B,BCE03B,DFlag,CBM41,BQS02,VFX21)
  select a.CBM01, a.VAA01, a.VAA07, a.VAP01, a.ACF01, a.CBM06, a.CBM07
    , a.CBM08, a.CBM09, a.BCK01A, a.BCK01B, a.BCK01C, a.BCE01, a.BCE03
    , a.CBM15, a.CBM16, a.CBM17, a.CBM18, a.CBM19, a.PRNCP, a.CBM21,a.BJW02,a.VCY01,a.VBQ01,a.IAI03
    ,a.BCE01A,a.CBM27,a.CBM28,a.CBM29,a.CBM30,a.CBM31,a.CBM32,a.CBM33,a.CBM34,a.CBM35,a.CBM36,a.CBM37
	,a.BCE01B,a.BCE03B,a.DFlag,a.CBM41,a.BQS02,a.VFX21
  from #hokbmCBM a

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 9
  end  
--VAF1
  insert into VAF2_2(VAF01, VAF01A, VAF01B, VAF04, VAA01, VAF06, VAF07, BCK01A, ROWNR, VAF10, VAF11, BDA01, BBX01
    , VAF14, VAF15, BBY01, VAF17, VAF18, VAF19, VAF20, VAF21, VAF22, VAF23, BCK01B, VAF25, VAF26
    , VAF27, VAF28, VAF29, VAF30, VAF31, VAF32, VAF33, VAF34, VAF35, VAF36, VAF37, VAF38, BCK01C
    , BCE02A, BCE03A, VAF42, BCE03B, BCE03C, VAF45, BCE03D, VAF47, BCE03E, BCE03F, VAF50, VAF51
    , VAF52, VAF53, VAF54, VAF55, CBM01, BCK01D, VAF58, VAF59, VAF60, VAF61, VAF62, BCE01A
    , BCE01B, BCE01C, BCE01D, BCE01E, BCE01F,BCE01G,BCE03G,VAF71,DSK01,VAF01C,VAF74,VAF75,BCE01H,BCE03H,BIW02,Crypt,DFlag)
  select a.VAF01, a.VAF01A, a.VAF01B, a.VAF04, a.VAA01, a.VAF06, a.VAF07, a.BCK01A, a.ROWNR, a.VAF10
    , a.VAF11, a.BDA01, a.BBX01, a.VAF14, a.VAF15, a.BBY01, a.VAF17, a.VAF18, a.VAF19, a.VAF20, a.VAF21
    , a.VAF22, a.VAF23, a.BCK01B, a.VAF25, a.VAF26, a.VAF27, a.VAF28, a.VAF29, a.VAF30, a.VAF31, a.VAF32
    , a.VAF33, a.VAF34, a.VAF35, a.VAF36, a.VAF37, a.VAF38, a.BCK01C, a.BCE02A, a.BCE03A, a.VAF42, a.BCE03B
    , a.BCE03C, a.VAF45, a.BCE03D, a.VAF47, a.BCE03E, a.BCE03F, a.VAF50, a.VAF51, a.VAF52, a.VAF53, a.VAF54
    , a.VAF55, a.CBM01, a.BCK01D, a.VAF58, a.VAF59, a.VAF60, a.VAF61, a.VAF62, a.BCE01A
    , a.BCE01B, a.BCE01C, a.BCE01D, a.BCE01E, a.BCE01F,a.BCE01G,a.BCE03G,a.VAF71,a.DSK01,a.VAF01C
    ,a.VAF74,a.VAF75,a.BCE01H,a.BCE03H,a.BIW02,a.Crypt,a.DFlag
  from #hokbmVAF a

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 13
  end
--VEH2_2
  insert into VEH2_2(VAF01,VEH02,VEB01,VEH04,VEH05,VEC01,VEH07,VEH08,VEH09,VEH10,BCE03A,BCE01A,BCE03B
    ,BCE01B,VEH15,BPT01,BPT02,VEH18,VEH19,BAG03,BDG02,BBY08,VEH23,VEH24,ATTRS,VEH26,VEH27,VEH28,VEH29
    ,VEH30,VEH31,BCE01C,BCE03C,VEH34,VEH35,VEH36,VEH37,VEH38,VEH39,VEH40)    
  select VAF01,VEH02,VEB01,VEH04,VEH05,VEC01,VEH07,VEH08,VEH09,VEH10,BCE03A,BCE01A,BCE03B
    ,BCE01B,VEH15,BPT01,BPT02,VEH18,VEH19,BAG03,BDG02,BBY08,VEH23,VEH24,ATTRS,VEH26,VEH27,VEH28,VEH29
    ,VEH30,VEH31,BCE01C,BCE03C,VEH34,VEH35,VEH36,VEH37,VEH38,VEH39,VEH40
  from #hokbmVEH 
  
  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 13
  end
--VBI1
  insert into VBI2_2(VBI01, VAF01, VBI03, VBI04, VBI05, VBI06, VBI07, BCE03A, VBI09, VBI10, VBI11, VBI12 
    , VBI13, BCK01, VBI15, BEE03, VBI17, VBI18, BCE03B, VBI20, VBI21, VBI22, VBI23, VBI24 
    , BCE03C, VBI26, VBI27, BCE03D, VBI29, BCE01A, ACF01, VAA01, VAA07, VAP01,VBI35,VBI36,BCE01E,BCE03E,VBI39,VBI40)
  select a.VBI01, a.VAF01, a.VBI03, a.VBI04, a.VBI05, a.VBI06, a.VBI07, a.BCE03A, a.VBI09, a.VBI10, a.VBI11
    , a.VBI12, a.VBI13, a.BCK01, a.VBI15, a.BEE03, a.VBI17, a.VBI18, a.BCE03B, a.VBI20, a.VBI21, a.VBI22
    , a.VBI23, a.VBI24, a.BCE03C, a.VBI26, a.VBI27, a.BCE03D, a.VBI29, a.BCE01A, a.ACF01, a.VAA01, a.VAA07
    , a.VAP01,a.VBI35,a.VBI36,a.BCE01E,a.BCE03E,a.VBI39,a.VBI40
  from #hokbmVBI a

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 15
  end
    
--VBQ1
  insert into VBQ2_2(VBQ01, VBQ02, VAA01, VAA07, VAP01, ACF01, VBQ07, BCK01A, BCK01B, BCQ04, BCE03A
    , VBQ12, BCE03B, VBQ14, VBQ15, BES02, BEL01, ABB02, VBQ19, BCK01C, BCE03C, VBQ22, VBQ23
    , VBQ24, PRNCP, VBQ26, VBQ27, VBQ28, VBQ29, VBQ30, VBQ31, BCE01A,BCE03D,VBQ34,BDA01,VBQ36,VBQ37
	,VBQ38,VBQ39,VBQ01A)
  select a.VBQ01, a.VBQ02, a.VAA01, a.VAA07, a.VAP01, a.ACF01, a.VBQ07, a.BCK01A, a.BCK01B, a.BCQ04, a.BCE03A
    , a.VBQ12, a.BCE03B, a.VBQ14, a.VBQ15, a.BES02, a.BEL01, a.ABB02, a.VBQ19, a.BCK01C, a.BCE03C, a.VBQ22
    , a.VBQ23, a.VBQ24, a.PRNCP, a.VBQ26, a.VBQ27, a.VBQ28, a.VBQ29, a.VBQ30, a.VBQ31, a.BCE01A,a.BCE03D
    ,a.VBQ34,a.BDA01,a.VBQ36,a.VBQ37,VBQ38,VBQ39,VBQ01A
  from #hokbmVBQ a
  
  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 11
  end
  
--VBD1
  insert into VBD2_2(VBD01, VAF01, BBY01, VBD04, VBD05, VBD06, BCK01, VBD08, VBD09, VBD10,VBD11,VBD12,BBY01A,VBD14,BCE01,BCE03,DFlag,VBD18,VBD19 )
  select a.VBD01, a.VAF01, a.BBY01, a.VBD04, a.VBD05, a.VBD06, a.BCK01, a.VBD08, a.VBD09, a.VBD10
    ,a.VBD11,a.VBD12,a.BBY01A,a.VBD14,a.BCE01,a.BCE03,DFlag,a.VBD18,a.VBD19
  from #hokbmVBD a

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 17
  end   
  --VBV1
  INSERT INTO VBV2(VBV01, VAF01, VBV03, VBV04, VBV05, VAA01, VAA07, VBV08, VAF11, VBV10, VBV11, BCE03, BCK01, VBV14, VBV15, VBV16, VBV17, VBV18, VBV19, VBV20)
  select VBV01, VAF01, VBV03, VBV04, VBV05, VAA01, VAA07, VBV08, VAF11, VBV10, VBV11, BCE03, BCK01, VBV14, VBV15, VBV16, VBV17, VBV18, VBV19, VBV20
  from #hokbmVBV
--VBG1
  insert into VBG2(VBG01, VAF01, VBG03, BCE03, VBG05, VBG06,VAF01A)
  select VBG01, VAF01, VBG03, BCE03, VBG05, VBG06,VAF01A
  from #hokbmVBG

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 19
  end
--删除数据 
  delete a
  from VAI2 a
  join #hokbmVAI b on b.VAI01 = a.VAI01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 6
  end
  
  delete a
  from VAJ2 a
  join #hokbmVAJ b on b.VAJ01 = a.VAJ01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 8
  end
  
  delete a
  from VEG2 a
  join #hokbmVEG b on b.VAJ01 = a.VAJ01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 8
  end  
 
  delete a
  from VAK1 a
  join #hokbmVAK b on a.VAK01 = b.VAK01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 2
  end
  
  delete a
  from VBL1 a
  join #hokbmVBL b on b.VBL01 = a.VBL01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 4
  end
  
  delete a
  from CBM2 a
  join #hokbmCBM b on b.CBM01 = a.CBM01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 10
  end
  
  delete a
  from VAF2 a
  join #hokbmVAF b on b.VAF01 = a.VAF01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 14
  end
  
  delete a
  from VEH2 a
  join #hokbmVEH b on b.VAF01 = a.VAF01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 14
  end

  delete a
  from VBI2 a
  join #hokbmVBI b on b.VBI01 = a.VBI01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 16
  end
  
  delete a
  from VBQ2 a
  join #hokbmVBQ b on b.VBQ01 = a.VBQ01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 12
  end
  delete a
  from VBD2 a
  join #hokbmVBD b on b.VBD01 = a.VBD01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 18
  end

  delete a
  from VBG1 a
  join #hokbmVBG b on b.VBG01 = a.VBG01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 20
  end   

  delete a
  from VBV1 a
  join #hokbmVBV b on b.VBV01 = a.VBV01
  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 21
  end   
commit tran

--清除临时表
if object_id('tempdb..#ExVAK') is not null drop table #ExVAK
if object_id('tempdb..#ExVAI') is not null drop table #ExVAI
if object_id('tempdb..#ExVAF') is not null drop table #ExVAF
if object_id('tempdb..#ExCBM') is not null drop table #ExCBM
if object_id('tempdb..#ExVAE') is not null drop table #ExVAE

if object_id('tempdb..#hokbmVAK') is not null drop table #hokbmVAK
if object_id('tempdb..#hokbmVAI') is not null drop table #hokbmVAI
if object_id('tempdb..#hokbmVAF') is not null drop table #hokbmVAF
if object_id('tempdb..#hokbmCBM') is not null drop table #hokbmCBM
if object_id('tempdb..#hokbmVBL') is not null drop table #hokbmVBL
if object_id('tempdb..#hokbmVAJ') is not null drop table #hokbmVAJ
if object_id('tempdb..#hokbmVBI') is not null drop table #hokbmVBI
if object_id('tempdb..#hokbmVBD') is not null drop table #hokbmVBD
if object_id('tempdb..#hokbmVBQ') is not null drop table #hokbmVBQ
if object_id('tempdb..#hokbmVBG') is not null drop table #hokbmVBG
if object_id('tempdb..#hokbmVEH') is not null drop table #hokbmVEH
if object_id('tempdb..#hokbmVEG') is not null drop table #hokbmVEG


end