DROP PROCEDURE [dbo].[HO_DatabaseDump]
GO

DROP PROCEDURE [dbo].[HO_DPT_BatchUpdate]
GO

DROP PROCEDURE [dbo].[HO_HistoryBatchMove_2]
GO

DROP PROCEDURE [dbo].[HO_HistoryBatchMove_Drug]
GO

DROP PROCEDURE [dbo].[HO_HistoryBatchMove_Two]
GO

DROP PROCEDURE [dbo].[HO_HistoryBatchMove_zy]
GO

DROP PROCEDURE [dbo].[HO_Rate_Contributions_Entry_AUTO]
GO

DROP PROCEDURE [dbo].[Clinic_VAJ1_Auto_Insert]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--存储过程.Clinic_VAJ1_Auto_Insert

--住院病人自动记账任务
--Alter 8058 2014.6.23
CREATE              PROC [dbo].[Clinic_VAJ1_Auto_Insert]
  @lDate Datetime = 0
  ,@lFlag tinyint = 1  --1= 此项目当天记过帐，自动记账中就不在记账；  0 = 不用区分
  ,@VAE01 int = 0 -- 用于入科时记账
AS
  --------------------判断是否为主数据库,如果为辅助数据库则直接退出
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
----------------------------
--对应开单医师、开单科室 按当前住院医师，住院科室	
--暂时离院的不自动记账
  DECLARE @BDate datetime, @EDate Datetime,@adate datetime

  If @lDate = 0  SET @lDate = Getdate()
  set @adate = GETDATE()
  set @BDate = Convert(varchar(10),@lDate,21)
  set @EDate = Convert(varchar(10),@lDate,21)+' 23:59:59'
	
  IF NOT EXISTS(SELECT * FROM BEO1 WHERE BEO06 <= @EDate) 
      RETURN -1
	--判断是否有需要自动记账的病人
    select * into #tmpVAE from VAE1 
	where (@VAE01 > 0 and VAE01 = @VAE01)
	  or(@VAE01 = 0 and VAE44 >=2 and VAE44<=3 and ISNULL(ABV01,'')<>'99' and VAE11 < @lDate) 
	  
  IF ((NOT EXISTS(SELECT a.*
                  FROM BEO1 a
                    JOIN BCK1 b ON a.BCK01=b.BCK01
                  WHERE b.BCK16 > @EDate ))
       AND (NOT EXISTS(SELECT a.*
                       FROM BEO1 a
                         JOIN #tmpVAE b ON a.VAA07 = b.VAE01))
       AND (NOT EXISTS(SELECT a.*
                       FROM BEO1 a
                         JOIN BBY1 b ON a.BBY01 = b.BBY01
                       WHERE b.BBY31 > @EDate )))		
      RETURN -1		
  				  
	--所有自动记账项目，一个病人一个单据
    CREATE TABLE #TmpA(uid int IDENTITY(1,1)
                       , VAE01 int
                       , BBY01 int
                       , BEO04 int
                       , BCK01 int
                       , VAA01 int
                       , BBY34 tinyint
                       , BBY25 numeric(18,4)
                       , VAJ25 numeric(18,4) default (1)
                       , VAJ39 tinyint default(0)
                       )		
	--病区护理 对应当天已记账的就不在继续记账
    INSERT INTO #TmpA(VAE01,BBY01,BEO04,BCK01,VAA01,BBY34,BBY25,VAJ39,VAJ25)
    SELECT a.VAE01,d.BBY01,b.BEO04,a.BCK01C,a.VAA01,d.BBY34,d.BBY25,0,1
    FROM #tmpVAE a
      JOIN BEO1 b ON a.BCK01C = b.BCK01
      JOIN BCK1 c ON b.BCK01 = c.BCK01
      JOIN BBY1 d ON a.AAG01 = d.BBY01
    WHERE b.BEO04 = -2 AND b.BEO06 <= @EDate AND c.BCK16 > @EDate AND isnull(a.AAG01,0)>0
        AND d.BBY31 > @EDate 
	Union All    --床位 对应当天已记账的就不在继续记账
    SELECT a.VAE01,d.BBY01,b.BEO04,d.BCK01A,a.VAA01,e.BBY34,e.BBY25,0,1
    FROM #tmpVAE a
      JOIN BEO1 b ON a.BCK01C = b.BCK01
      JOIN BCK1 c ON b.BCK01 = c.BCK01
      JOIN BCQ1 d ON a.BCQ04B = d.BCQ04 AND a.VAA01 = d.VAA01
      JOIN BBY1 e ON d.BBY01 = e.BBY01
    WHERE b.BEO04 = -1 AND b.BEO06 <= @EDate AND c.BCK16 > @EDate AND (d.BCQ13 = 1 OR d.BCQ13 = 3)
        AND e.BBY31 > @EDate
    UNION ALL    
    SELECT b1.VAE01,isnull(c2.BBY01,c1.BBY01),d1.BEO04,b1.BCK01C,b1.VAA01,isnull(c2.BBY34,c1.BBY34),isnull(c2.BBY25,c1.BBY25),0,1
    FROM BCQ1 a1
    JOIN #tmpVAE b1 ON a1.VAA01 = b1.VAA01 AND a1.BCK01A = b1.BCK01C
    JOIN BBY1 c1 ON c1.BBY01 = a1.BBY01  --case when ISNULL(a1.BBY01B,0) > 0 then a1.BBY01B else a1.BBY01 end
    JOIN BEO1 d1 ON b1.BCK01C = d1.BCK01
    left join BBY1 c2 on c2.BBY01 = a1.BBY01B
    WHERE a1.BCQ13 = 2 AND d1.BEO04 = -1
      AND EXISTS(SELECT a.*
                 FROM #tmpVAE a
                   JOIN BEO1 b ON a.BCK01C = b.BCK01
                   JOIN BCK1 c ON b.BCK01 = c.BCK01
                   JOIN BCQ1 d ON a.BCK01C = d.BCK01A AND a.BCQ04B = d.BCQ04 AND a.VAA01 = d.VAA01
                   JOIN BBY1 e ON d.BBY01 = e.BBY01
                 WHERE b1.VAE01 = a.VAE01 AND b.BEO04 = -1 AND b.BEO06 <= @EDate
                   AND c.BCK16 > @EDate AND d.BCQ13 = 1 AND e.BBY31 > @EDate) 
	Union All    --病区项目
    SELECT d.VAE01,c.BBY01,a.BEO04,d.BCK01C,d.VAA01,c.BBY34,c.BBY25,0,ISNULL(a.BEO08,1)
    FROM BEO1 a
    JOIN BCK1 b ON a.BCK01 = b.BCK01
    JOIN BBY1 c ON a.BBY01 = c.BBY01
    JOIN #tmpVAE d ON a.BCK01 = d.BCK01C
    WHERE a.BEO04>0 AND ISNULL(a.VAA07,0)=0 and a.BCK01 > 0 AND a.BEO06 <= @EDate AND b.BCK16 > @EDate And c.BBY31 > @EDate
	Union All     --个人项目
    SELECT c.VAE01,b.BBY01,a.BEO04,c.BCK01C,c.VAA01,b.BBY34,b.BBY25,0,ISNULL(a.BEO08,1)
    FROM BEO1 a
    JOIN BBY1 b ON a.BBY01 = b.BBY01
    JOIN #tmpVAE c ON a.VAA07 = c.VAE01
    WHERE a.BEO04>0 AND ISNULL(a.BCK01,0)=0 and a.VAA07 > 0 AND a.BEO06 <= @EDate AND b.BBY31 > @EDate
    Union All     --病人类别项目
    SELECT c.VAE01,b.BBY01,a.BEO04,c.BCK01C,c.VAA01,b.BBY34,b.BBY25,0,ISNULL(a.BEO08,1)
    FROM BEO1 a
    JOIN BBY1 b ON a.BBY01 = b.BBY01
    JOIN #tmpVAE c ON c.BDP02 = a.BDP02
    WHERE a.BEO04>0 AND ISNULL(a.BDP02,'')<>'' AND a.BEO06 <= @EDate AND b.BBY31 > @EDate
    if not Exists(Select * from #TmpA)
    BEGIN
      IF Object_id('tempdb..#TmpA') IS NOT NULL DROP TABLE #TmpA
      Return -1
    END

	--找出套餐项目
    INSERT INTO #TmpA(VAE01,BBY01,BEO04,BCK01,VAA01,BBY34,BBY25,VAJ25,VAJ39)
    select a.VAE01,b.BBY01B,a.BEO04,a.BCK01,a.VAA01,0,Case when b.BCR10 = 1 then c.BBY25 else b.BCR07 end,b.BCR04*a.VAJ25,0
    from #TmpA a
      join BCR1 b on a.BBY01 = b.BBY01A
      join BBY1 c on b.BBY01B = c.BBY01
    where a.BBY34 = 2 And c.BBY31 > @EDate and b.BCR09 > @EDate
    
    Delete From #TmpA Where BBY34 = 2
    --找出从属项目
    INSERT INTO #TmpA(VAE01,BBY01,BEO04,BCK01,VAA01,BBY34,BBY25,VAJ25,VAJ39)
    select a.VAE01,b.BBY01B,a.BEO04,a.BCK01,a.VAA01,0,c.BBY25,b.BCR04*a.VAJ25,0
    from #TmpA a
      join BCR1 b on a.BBY01 = b.BBY01A
      join BBY1 c on b.BBY01B = c.BBY01
    where a.BBY34 = 1 And c.BBY31 > @EDate
    
    update #TmpA set BBY34 = 0 where BBY34 = 1
    --删除存在只记一次的项目
    Delete a
    From #TmpA a
    Where a.BEO04 = 2
      and Exists(Select b.* from VAJ2 b 
                 where b.ACF01 = 2 and b.VAJ05 < 3 and ISNULL(b.VAJ27,0)=0
                   and a.VAE01 = b.VAA07 and a.BBY01 = b.BBY01)
    --删除当天已经记账的项目
    Delete a
    From #TmpA a
    Where a.BEO04 <> 2 and @lFlag = 1
      and Exists(Select b.* from VAJ2 b 
                 where b.ACF01 = 2 and b.VAK01 = 0 and b.VAJ05 < 3
                   and a.VAE01 = b.VAA07 and a.BBY01 = b.BBY01 and ISNULL(b.VAJ27,0)=0
                   and b.VAJ46 >= @BDate and b.VAJ46 <= @EDate)

    if not Exists(Select * from #TmpA)
    BEGIN
      IF Object_id('tempdb..#TmpA') IS NOT NULL DROP TABLE #TmpA
      Return -1
    END

	--开始记账	
    DECLARE @lVAE01 int,@lVAA01 int,@lVAI01 int,@lID int,@lStr varchar(8)   
    Declare @MaxUid int, @MinUid int, @mxUid int,@mnUid int

    SET @lStr = REPLICATE('0',8) --'00000000'
--存放病人当天记账数据
  Create Table #TmpB(uid int IDENTITY(1,1)
                       , VAE01 int
                       , BBY01 int
                       , BCK01 int
                       , VAA01 int
                       , BBY25 numeric(18,4)
                       , VAJ25 numeric(18,4)
                       , VAJ37 numeric(18,4)
                       , VAI01 int
                       , VAJ39 tinyint,VAJ01 int,VAF01 int default(0)
                       )	
  Create Table #Tmp_Pat(uid int IDENTITY(1,1)
                        , VAE01 int
                        , VAA01 int
                        , VAI01 int
                        , VAJ37 numeric(18,4) default (0)
                        , VAI04 Varchar(20))
  Create Table #Tmp_VBM(uid int IDENTITY(1,1), VAA01 int,VAJ37 numeric(18,4))
  Create Table #Tmp_VBMa(uid int IDENTITY(1,1), VAA01 int,VAA07 int,VAJ37 numeric(18,4))
  
  Insert Into #Tmp_Pat(VAE01,VAA01)
  SELECT DISTINCT VAE01,VAA01
  FROM #TmpA  
  ORDER BY VAE01 

  Insert Into #TmpB(VAE01,VAA01,BBY01,BCK01,BBY25,VAJ25,VAJ37,VAJ39)
  Select VAE01,VAA01,BBY01,BCK01,BBY25,VAJ25,BBY25 * VAJ25,VAJ39
  From #TmpA
  Order by VAE01 
  
  Select @MaxUid = Max(uid), @MinUid = Min(uid) from #Tmp_Pat
      Exec Core_NewIDEX 'VAI1','VAI01',@lVAI01 out,@MaxUid
      Set @lVAI01 = @lVAI01 - @MaxUid

      Update #Tmp_Pat Set VAI01 = Uid + @lVAI01
      Update #Tmp_Pat Set VAI04 = 'ZD'+substring(@lstr,1,8-len(VAI01))+cast(VAI01 AS varchar(10))
      Update a
      Set a.VAI01 = b.VAI01
      From #TmpB a
      join #Tmp_Pat b on a.VAE01 = b.VAE01        
      Select @mxUid = Max(uid), @mnUid = Min(uid) from #TmpB
      Exec Core_NewIDEX 'VAJ1','VAJ01',@lID out,@mxUid
      SET @lID = @lID - @mxUid  
      update #TmpB set VAJ01 = uid + @lID  
  --持续医嘱记账
  declare @editVdc tinyint
  set @editVdc = 0
  if (@VAE01 = 0) and exists(select a.* from VDC1 a 
      join VAF2 m on m.VAF01 = a.VAF01
      where ISNULL(a.VDC07,'1900-01-01')>'2000-01-01' and ISNULL(a.VDC08,'1900-01-01')<'2000-01-01' and a.VDC07 < @adate
        and m.VAF10 = 3 and a.VDC06 = 0 and exists(select * from #tmpVAE b where b.VAE01 = a.VAA07))
    set @editVdc = 1
  if @editVdc = 1                             
  begin
    select a.*,m.BCK01B,m.BBX01,cast(DATEDIFF(MINUTE,a.VDC07,@adate)/60.0 as numeric(18,1)) VDC11A 
    into #kbmVDC1
    from VDC1 a 
    join VAF2 m on m.VAF01 = a.VAF01
    where ISNULL(a.VDC07,'1900-01-01')>'2000-01-01' and ISNULL(a.VDC08,'1900-01-01')<'2000-01-01' and a.VDC07 < @adate
      and m.VAF10 = 3 and a.VDC06 = 0 and exists(select * from #tmpVAE b where b.VAE01 = a.VAA07)
    
    select IDENTITY(int,1,1) AS ID,s.*,s.VAJ25*s.BBY25 VAJ37,0 VAJ39,s1.BCE03C,s1.BCE02C,0 VAJ01,0 VAI01,s1.BCK01C,s1.BCK01D
      ,s1.BCQ04B,s1.ABC02
    into #kbmvaj
    from (
      select c.BBY01,c.BBY08,c.BDN01,a.BCK01B,a.VAF01,a.VDC11A*b.BDU04 VAJ25,c.BBY25,a.VAA01,a.VAA07    
      from #kbmVDC1 a
      join BDU1 b on b.BBX01 = a.BBX01
      join BBY1 c on c.BBY01 = b.BBY01          
      where c.BBY34 <> 2 and c.BBY31 > @aDate
      union all
      select e.BBY01,e.BBY08,e.BDN01,a.BCK01B,a.VAF01,a.VDC11A*b.BDU04*d.BCR04 VAJ25
        ,Case when d.BCR10 = 1 then e.BBY25 else d.BCR07 end BBY25,a.VAA01,a.VAA07
      from #kbmVDC1 a
      join BDU1 b on b.BBX01 = a.BBX01
      join BBY1 c on c.BBY01 = b.BBY01
      join BCR1 d on d.BBY01A = c.BBY01
      join BBY1 e on e.BBY01 = d.BBY01B
      where c.BBY34 = 2 and c.BBY31 > @aDate and e.BBY31 > @aDate and d.BCR09 > @aDate ) s
    join #tmpVAE s1 on s1.VAE01 = s.VAA07 
     
    if exists(select * from #kbmvaj) 
    begin
      select IDENTITY(int,1,1) AS ID,0 VAI01,CAST('' as varchar(20)) VAI04,s.*,b.BCE03C,b.BCE02C,b.BCK01C,b.BCK01D
      into #kbmvae
      from ( 
        select distinct a.vaa01,a.vaa07
        from #kbmvaj a ) s
      join #tmpVAE b on b.VAE01 = s.VAA07  
      
      Select @MaxUid = Max(id) from #kbmvae
      Exec Core_NewIDEX 'VAI1','VAI01',@lid out,@MaxUid
      Set @lid = @lid - @MaxUid
      Update #kbmvae Set VAI01 = id + @lid
      Update #kbmvae Set VAI04 = 'CX'+substring(@lstr,1,8-len(VAI01))+cast(VAI01 AS varchar(10))
    
      Select @MaxUid = Max(id) from #kbmvaj
      Exec Core_NewIDEX 'VAJ1','VAJ01',@lid out,@MaxUid
      Set @lid = @lid - @MaxUid
      Update #kbmvaj Set VAJ01 = id + @lid  
      update a set a.VAI01 = b.VAI01
      from #kbmvaj a
      join #kbmvae b on b.VAA07 = a.VAA07  
      
      insert into #Tmp_Pat(VAA01,VAE01,VAI01,VAI04)
      select VAA01,VAA07,VAI01,VAI04
      from #kbmvae
      
      Insert Into #TmpB(VAE01,VAA01,BBY01,BCK01,BBY25,VAJ25,VAJ37,VAJ39,VAJ01,VAI01,VAF01)
      select VAA07,VAA01,BBY01,BCK01B,BBY25,VAJ25,VAJ37,VAJ39,VAJ01,VAI01,VAF01
      from #kbmvaj
    end
    else
      set @editVdc = 0
  end

  BEGIN TRAN    
		--每个病人的帐
      INSERT INTO VAI2
        (VAI01,VAA01,VAA07,VAI04,VAI05,BCK01A,BCK01B,BCE02A,BCE03A,BCE02B,BCE03B,VAI12,VAI13,VAI14,VAI01A,VAI16,VAI17
         ,VAI18,CBM01,ACF01,VAK01)
      select a.VAI01,a.VAA01,a.VAE01,a.VAI04,'自动记账',b.BCK01C,b.BCK01D,b.BCE02C,b.BCE03C,b.BCE02C,b.BCE03C,@lDate,@lDate,'自动记账',0,1,1
         ,6,0,2,0
      FROM #Tmp_Pat a 
      join #tmpVAE b on a.VAE01 = b.VAE01      

      IF (@@ERROR > 0)
      begin
        if @@TRANCOUNT > 0 
           ROLLBACK TRAN
        IF Object_id('tempdb..#TmpA') IS NOT NULL DROP TABLE #TmpA
        IF Object_id('tempdb..#Tmp_Pat') IS NOT NULL DROP TABLE #Tmp_Pat
        IF Object_id('tempdb..#TmpB') IS NOT NULL DROP TABLE #TmpB
        IF Object_id('tempdb..#Tmp_VBM') IS NOT NULL DROP TABLE #Tmp_VBM
        RETURN 1
      end
      INSERT INTO VAJ2
        (VAJ01,VAA01,VAA07,VAJ04,VAJ05,ROWNR,VAJ09,VAI01,VAF01,VAK01,ACF01,VAJ15,BCK01A,BCK01B
        ,BDN01,BBY01,BCJ02,VAJ21,VAJ22,VAJ23,VAJ24,VAJ25,VAJ26,VAJ27,VAJ30,VAJ31
        ,VAJ32,VAJ33,VAJ34,VAJ35,VAJ36,VAJ37,VAJ38,VAJ39,BCE03A,BCK01C,BCE02B,BCE03B,VAJ46
        ,VAJ47,VAJ48,BCK01D,BCE03C,VAJ51,VAJ52,VAJ53,VAJ54,BCE02D,BCE03D,VAJ57,VAJ61,VAJ62
        ,VAJ01A,BCK01E,VAJ64,VAJ65,DSK01,VAJ67,BCE01E,BCE03E,BCK01F,BCQ04) 
      SELECT a.VAJ01,a1.VAA01,a1.VAE01,6,1,uid,0,a.VAI01,a.VAF01,0,2,0,a1.BCK01C,a1.BCK01D
        ,c.BDN01,a.BBY01,null,0,0,1,a.VAJ25,a.VAJ25,0,0
        ,CASE WHEN f1.BBY01 IS NOT NULL THEN f1.ACV08 
              WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN g1.ACV08
            ELSE 100 END AS VAJ30
        ,CASE WHEN f1.BBY01 IS NOT NULL THEN f1.ACV09 
              WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN g1.ACV09
            ELSE 100 END AS VAJ31
        ,a.BBY25,CASE WHEN f1.BBY01 IS NOT NULL THEN (a.BBY25*f1.ACV08)/f1.ACV09 
                      WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.BBY25*g1.ACV08)/g1.ACV09 
                    ELSE a.BBY25 END AS VAJ33
        ,1,c.BBY08,a.VAJ37
        ,CASE WHEN f1.BBY01 IS NOT NULL THEN (a.VAJ37*f1.ACV08)/f1.ACV09 
              WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.VAJ37*g1.ACV08)/g1.ACV09 
            ELSE a.VAJ37 END AS VAJ37
        ,CASE WHEN f1.BBY01 IS NOT NULL THEN (a.VAJ37*f1.ACV08)/f1.ACV09 
              WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.VAJ37*g1.ACV08)/g1.ACV09 
            ELSE a.VAJ37 END AS VAJ38
        ,a.VAJ39,a1.BCE03C,a1.BCK01D,a1.BCE02C,a1.BCE03C,@lDate
        ,@lDate,0,a.BCK01,null,@lDate,@lDate,1
        ,case when a.VAF01>0 then '医嘱持续自动记账项目' else '自动记账项目' end
        ,a1.BCE02C,a1.BCE03C
        ,case when a.VAF01>0 then '医嘱持续自动记账项目' else '自动记账项目' end
        ,CASE WHEN f1.BBY01 IS NOT NULL THEN (a.VAJ37*f1.ACV08)/f1.ACV09 
              WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.VAJ37*g1.ACV08)/g1.ACV09 
            ELSE a.VAJ37 END AS VAJ61,@lDate VAJ62
        ,0,0,@lDate,0,0,a.BBY25,m.BCE01,m.BCE03,ISNULL(n.BCK01A,0),a1.BCQ04B
      FROM #TmpB a
        JOIN BBY1 c ON a.BBY01 = c.BBY01
        join #tmpVAE a1 on a.VAE01 = a1.VAE01
        left join BCE1 m on m.BCE02 = a1.BCE02C
        left join BCQ1 n on n.VAA01 = a1.VAA01 and n.BCQ04 = a1.BCQ04B and n.BCQ13 in (1,3)
        LEFT JOIN (SELECT f.ABC02,f.BBY01,f.ACV06,f.ACV07,f.ACV08,f.ACV09,f.ACV10 
                   FROM ACV1 f 
                   WHERE f.BCH01 IS NULL) AS f1 ON a1.ABC02=f1.ABC02 AND c.BBY01=f1.BBY01
                       AND f1.ACV06<= a.VAJ37 AND f1.ACV07 >= a.VAJ37
        LEFT JOIN (SELECT g.ABC02,g.BCH01,g.ACV06,g.ACV07,g.ACV08,g.ACV09,g.ACV10 
                   FROM ACV1 g 
                   WHERE g.BBY01 IS NULL) AS g1 ON a1.ABC02=g1.ABC02 AND c.BCH01=g1.BCH01
                       AND NOT EXISTS(SELECT g2.BBY01 FROM ACV1 g2 
                                      WHERE g2.BCH01 IS NULL AND a1.ABC02=g2.ABC02 AND c.BBY01=g2.BBY01)
                       AND g1.ACV06 <= a.VAJ37 AND g1.ACV07 >= a.VAJ37
      IF (@@ERROR > 0)
      begin
        if @@TRANCOUNT > 0 
           ROLLBACK TRAN
        IF Object_id('tempdb..#TmpA') IS NOT NULL DROP TABLE #TmpA
        IF Object_id('tempdb..#Tmp_Pat') IS NOT NULL DROP TABLE #Tmp_Pat
        IF Object_id('tempdb..#TmpB') IS NOT NULL DROP TABLE #TmpB
        IF Object_id('tempdb..#Tmp_VBM') IS NOT NULL DROP TABLE #Tmp_VBM
        RETURN 2
      end
          
      Update a
      Set a.VAJ37 = c.FVAJ37
      From #Tmp_Pat a
        join (Select d.VAI01,isnull(Sum(b.VAJ37),0) FVAJ37
              From #Tmp_Pat d
                join VAJ2 b on d.VAI01 = b.VAI01
              Group By d.VAI01 ) c on a.VAI01 = c.VAI01

      Update a
      Set a.VAI22 = b.VAJ37,a.VAI23 = b.VAJ37
      From VAI2 a
        Join #Tmp_Pat b on a.VAI01 = b.VAI01

      IF (@@ERROR > 0)
      begin
        if @@TRANCOUNT > 0            ROLLBACK TRAN
        IF Object_id('tempdb..#TmpA') IS NOT NULL DROP TABLE #TmpA
        IF Object_id('tempdb..#Tmp_Pat') IS NOT NULL DROP TABLE #Tmp_Pat
        IF Object_id('tempdb..#TmpB') IS NOT NULL DROP TABLE #TmpB
        IF Object_id('tempdb..#Tmp_VBM') IS NOT NULL DROP TABLE #Tmp_VBM
        RETURN 3
      end
 
      Delete a From VAI2 a join #Tmp_Pat b on a.VAI01 = b.VAI01 where b.VAJ37 = 0
      Delete a From VAJ2 a join #Tmp_Pat b on a.VAI01 = b.VAI01 where b.VAJ37 = 0
      
      Delete From #Tmp_Pat Where VAJ37 = 0

      Declare @lVBM01 int
      Set @mxUid = 0
      --存在
      Update a 
      Set a.VBM05 = isnull(a.VBM05,0) + b.VAJ37,a.VBM06 = isnull(a.VBM06,0) + b.VAJ37 
      From VBM1 a
        join #Tmp_Pat b on a.VAA01 = b.VAA01
      Where a.VBM03 = 2
      
      Update a 
      Set a.VBM05 = isnull(a.VBM05,0) + b.VAJ37,a.VBM06 = isnull(a.VBM06,0) + b.VAJ37 
      From VBM2 a
        join #Tmp_Pat b on b.VAE01 = a.VAA07
      Where a.ACF01 = 2
      IF (@@ERROR > 0)
      begin
        if @@TRANCOUNT > 0 
           ROLLBACK TRAN
        IF Object_id('tempdb..#TmpA') IS NOT NULL DROP TABLE #TmpA
        IF Object_id('tempdb..#Tmp_Pat') IS NOT NULL DROP TABLE #Tmp_Pat
        IF Object_id('tempdb..#TmpB') IS NOT NULL DROP TABLE #TmpB
        IF Object_id('tempdb..#Tmp_VBM') IS NOT NULL DROP TABLE #Tmp_VBM
        RETURN 4
      end
      
      if Exists(Select a.* from #Tmp_Pat a where not Exists(Select b.* from VBM1 b where b.VBM03 = 2 and a.VAA01 = b.VAA01))
      begin
        Insert Into #Tmp_VBM(VAA01,VAJ37)
        Select a.VAA01,a.VAJ37
        from #Tmp_Pat a 
        where not Exists(Select b.* from VBM1 b where b.VBM03 = 2 and a.VAA01 = b.VAA01)
        
        Select @mxUid = Max(Uid) from #Tmp_VBM
    
        EXEC Core_NewIDEx 'VBM1','VBM01',@lVBM01 out ,@mxUid
        Set @lVBM01 = @lVBM01 - @mxUid
        
        Insert Into VBM1(VBM01,VAA01,VBM03,VBM04,VBM05,VBM06)
        Select Uid + @lVBM01,VAA01,2,0,VAJ37,VAJ37
        From #Tmp_VBM

        IF (@@ERROR > 0)
        begin
          if @@TRANCOUNT > 0 
            ROLLBACK TRAN
          IF Object_id('tempdb..#TmpA') IS NOT NULL DROP TABLE #TmpA
          IF Object_id('tempdb..#Tmp_Pat') IS NOT NULL DROP TABLE #Tmp_Pat
          IF Object_id('tempdb..#TmpB') IS NOT NULL DROP TABLE #TmpB
          IF Object_id('tempdb..#Tmp_VBM') IS NOT NULL DROP TABLE #Tmp_VBM
          RETURN 5
        end
      end    
      
      if Exists(Select a.* from #Tmp_Pat a where not Exists(Select b.* from VBM2 b where b.ACF01 = 2 and a.VAE01 = b.VAA07))
      begin
        Insert Into #Tmp_VBMa(VAA01,VAA07,VAJ37)
        Select a.VAA01,a.VAE01,a.VAJ37
        from #Tmp_Pat a 
        where not Exists(Select b.* from VBM2 b where b.ACF01 = 2 and a.VAE01 = b.VAA07)
        
        Select @mxUid = Max(Uid) from #Tmp_VBMa
    
        EXEC Core_NewIDEx 'VBM1','VBM01',@lVBM01 out ,@mxUid
        Set @lVBM01 = @lVBM01 - @mxUid
        
        Insert Into VBM2(VBM01,VAA01,VAA07,ACF01,VBM04,VBM05,VBM06)
        Select Uid + @lVBM01,VAA01,VAA07,2,0,VAJ37,VAJ37
        From #Tmp_VBMa

        IF (@@ERROR > 0)
        begin
          if @@TRANCOUNT > 0 
            ROLLBACK TRAN
          IF Object_id('tempdb..#TmpA') IS NOT NULL DROP TABLE #TmpA
          IF Object_id('tempdb..#Tmp_Pat') IS NOT NULL DROP TABLE #Tmp_Pat
          IF Object_id('tempdb..#TmpB') IS NOT NULL DROP TABLE #TmpB
          IF Object_id('tempdb..#Tmp_VBM') IS NOT NULL DROP TABLE #Tmp_VBM
          RETURN 5
        end
      end     
    if (@editVdc = 1) 
    begin
      update a
      set a.VDC07 = DATEADD(MINUTE,1,@adate),a.VDC08=null,a.VDC09 = b.VDC07,a.VDC10 = @adate,a.VDC11=b.VDC11A
      from VDC1 a
      join #kbmVDC1 b on b.VAA07 = a.VAA07 and b.VAF01 = a.VAF01
      where  exists(select * from #kbmVDC1)
    end
  COMMIT TRAN

    IF Object_id('tempdb..#TmpA') IS NOT NULL DROP TABLE #TmpA
    IF Object_id('tempdb..#Tmp_Pat') IS NOT NULL DROP TABLE #Tmp_Pat
    IF Object_id('tempdb..#TmpB') IS NOT NULL DROP TABLE #TmpB
    IF Object_id('tempdb..#Tmp_VBM') IS NOT NULL DROP TABLE #Tmp_VBM

 
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--===============================================================================
--Author     <...8058>
--ALTER Date <...2012.5.24>
--Description<...系统自动人员缴款存取过程,截至当前时间没缴款的全部缴款>
--History    <...2012.5.24对于操作员作废的票据，按时间段内缴款ID更新(FAC01)>
--===============================================================================
CREATE PROC [dbo].[HO_Rate_Contributions_Entry_AUTO]
 @aDate datetime = 0
AS 

  if @aDate = 0  set @aDate = GETDATE()  
  declare @kbmBCE table(uid int IDENTITY(1,1), BCE01 int,FAD02 varchar(20),FAD08 numeric(18,4),FAD01 int,FAD16 datetime)
  
  select VBL14,VBL13,VBL25,BCE01,VBL04,VBL27,isnull(VAK01,0) VAK01
  into #kbmVBL
  from VBL1 
  where ISNULL(VBL17,0)=0 and VBL19 <= @aDate --and ((VBL04 >= 2 and VBL04 <= 4) or (VBL04 = 5 and VBL27 = 1))
  union all
  SELECT '审批' VBL14,SUM(IBC15) VBL13,0 VBL25,BCE01,99 VBL04,99 VBL27,-1 VAK01
  FROM IBC1
  WHERE ISNULL(FAD01,0)=0 AND IBC19 <= @aDate
  group by BCE01
  
  insert into @kbmBCE(BCE01,FAD08)
  select a.BCE01,sum(ISNULL(a.VBL13,0) - ISNULL(VBL25,0))
  from #kbmVBL a
  where not exists(select * from VBX1 b where b.BCE01A = a.BCE01 and b.VBX03 = 1 and b.ACF01 = 1 and b.VBX04 = 2 and b.VBX14 <= @aDate )
  group by a.BCE01  
  
  if not exists(select * from @kbmBCE)
  begin
    if object_id('tempdb..#kbmVBL') is not null DROP TABLE #kbmVBL 
    return 0
  end
  
  declare @maxid int,@FAD01 int,@ErrId int
  select @maxid = MAX(uid) from @kbmBCE    
  exec @ErrId = Core_NewIDEx 'FAD1','FAD01',@FAD01 out,@maxid
  IF @ErrId > 0
  begin
    if object_id('tempdb..#kbmVBL') is not null DROP TABLE #kbmVBL 
    Return 1 
  end  
  set @FAD01 = @FAD01 - @maxid
  
  update @kbmBCE set FAD01 = @FAD01 + uid
  update @kbmBCE set FAD02 = 'F'+ SUBSTRING('000000' + Cast(FAD01 AS VARCHAR), Len('000000' + Cast(FAD01 AS VARCHAR)) - 6, 7)
  
  update a set a.FAD16 = b.aFAD10
  from @kbmBCE a
  join (select FAD04,max(FAD10) aFAD10 from FAD1 group by FAD04) b on b.FAD04 = a.BCE01
BEGIN TRAN 
      
      INSERT INTO FAD1(FAD01, FAD02, FAD01A, FAD04, BCK01, BBP02, FAD07, FAD08, FAD09, FAD10, BCE03A, FAD12,
         FDA13, BCE03B, FAD15 , FAD16)
      select FAD01,FAD02,0,a.BCE01,b.BCK01,'','',a.FAD08,'自动缴款',@aDate,b.BCE03,GETDATE()
        ,0,null,null,isnull(a.FAD16,@aDate)
      from @kbmBCE a
      join BCE1 b on b.BCE01 = a.BCE01
            
      IF @@ERROR > 0 --如果发生错误则回滚数据
      BEGIN
   	       IF @@TRANCOUNT > 0 ROLLBACK TRAN  
   	       if object_id('tempdb..#kbmVBL') is not null DROP TABLE #kbmVBL          
           Return 1
      END
      
      update a set a.VBL17 = b.FAD01,a.VBL25 = a.VBL13
      from VBL1 a
      join @kbmBCE b on b.BCE01 = a.BCE01
      where ISNULL(a.VBL17,0) = 0 and a.VBL19 <= @aDate
      
      update a set a.VAK18 = c.FAD01
      from VAK1 a
      join #kbmVBL b on b.VAK01 = a.VAK01
      join @kbmBCE c on c.BCE01 = b.BCE01
      where ISNULL(a.VAK18,0) = 0
      
      update a set a.FAC01 = -b.FAD01
      from FAB1 a
      join @kbmBCE b on b.BCE01 = a.BCE01
      where a.FAB04 = 6 and a.FAB08 <= @aDate
     
      update a set a.FAD01 = b.FAD01
      from IBC1 a
      join @kbmBCE b on b.BCE01 = a.BCE01
      where a.IBC19 <= @aDate and isnull(b.FAD01,0)= 0 
COMMIT TRAN 
if object_id('tempdb..#kbmVBL') is not null DROP TABLE #kbmVBL 


GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--存储过程把1表中对应住院数据转移到2表
--Alte 8058 2014.3.10
CREATE   PROCEDURE [dbo].[HO_HistoryBatchMove_zy]
AS
begin

  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('VAJ1','VAI1','VAF1','VBI1','CBM1','VBQ1','VBD1','VAO1')
    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','VAI2','VAF2','VBI2','CBM2','VBQ2','VBD2','VAO2')
          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('VAJ1','VAI1','VAF1','VBI1','CBM1','VBQ1','VBD1','VAO1')
    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','VAI2','VAF2','VBI2','CBM2','VBQ2','VBD2','VAO2')
          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     
  
 
  create table #ExVAI(VAI01 int primary key nonclustered, ACF01 tinyint, VAA07 int, VAJ05 tinyint)
  create table #ExVAO(VAO01 int primary key nonclustered, VAF51 int, CBM01 int, VAA07 int, ACF01 tinyint)
  create table #ExCBM(CBM01 int primary key nonclustered) 
  create table #ExVBQ(VBQ01 int primary key nonclustered) 
  create table #ExVAF(VAF01 int primary key nonclustered) 
    
  insert into #ExVAI(VAI01)
  select VAI01
  from VAI1
  where ACF01 = 2
  
  if not exists(select * from #ExVAI)
  begin
	return 0
  end

  insert into #ExVAO(VAO01)
  select VAO01 from  VAO1 where acf01 in (2,3)

  insert into #ExCBM(CBM01)
  select CBM01
  from CBM1
  where ACF01 = 2

  insert into #ExVBQ(VBQ01)
  select VBQ01 from VBQ1 where ACF01 = 2
  
  insert into #ExVAF(VAF01)
  select VAF01 from VAF1 where VAF04 = 2
  
begin tran  
--VAI
  insert into VAI2(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 )
  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
  from VAI1 a
  join #ExVAI b on b.VAI01 = a.VAI01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 5
  end  
--VAJ
  insert into VAJ2(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)
  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
  from VAJ1 a
  join #ExVAI b on b.VAI01 = a.VAI01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 7
  end
  
--CBM1
  insert into CBM2(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)
  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
  from CBM1 a
  join #ExCBM b on b.CBM01 = a.CBM01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 9
  end  
--VAF1
  insert into VAF2(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)
  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
  from VAF1 a
  join #ExVAF b on b.VAF01 = a.VAF01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 13
  end
    
--VBI1
  insert into VBI2(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)
  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
  from VBI1 a
  join #ExVAF b on b.VAF01 = a.VAF01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 15
  end
    
--VBQ1
  insert into VBQ2(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)
  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
  from VBQ1 a
  join #ExVBQ b on b.VBQ01 = a.VBQ01
  
  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 11
  end
  
--VBD1
  insert into VBD2(VBD01, VAF01, BBY01, VBD04, VBD05, VBD06, BCK01, VBD08, VBD09, VBD10 )
  select a.VBD01, a.VAF01, a.BBY01, a.VBD04, a.VBD05, a.VBD06, a.BCK01, a.VBD08, a.VBD09, a.VBD10
  from VBD1 a
  join #ExVAF b on b.VAF01 = a.VAF01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 17
  end    
--vao1
  insert into VAO2(VAO01, VAA01, VAA07, VAF01, ACF01, VAO06, VAO07, VAO08, VAO09, VAO10, VAO11, BAK01A, CAM01, BAK01B, VAO15, ABX01
      , VAO17, VAO18, VAO19, BCE03A, VAO21, BCE03B, VAO22, VAO24, VAO25, VAO26, IAA01, IAD03, BGP01, ADY01)
  select VAO01, VAA01, VAA07, VAF01, ACF01, VAO06, VAO07, VAO08, VAO09, VAO10, VAO11, BAK01A, CAM01, BAK01B, VAO15, ABX01
      , VAO17, VAO18, VAO19, BCE03A, VAO21, BCE03B, VAO22, VAO24, VAO25, VAO26, IAA01, IAD03, BGP01, ADY01
  from VAO1 
  where exists(select * from #ExVAO b where b.VAO01 = VAO1.VAO01)   
--删除数据 
  delete a
  from VAI1 a
  join #ExVAI b on b.VAI01 = a.VAI01

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

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

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

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

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

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

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

  delete a
  from VAO1 a
  join #ExVAO b on b.VAO01 = a.VAO01

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 20
  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


end


GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--存储过程.HO_HistoryBatchMove_Two

--alter 2012.8.9
--按审核时间，且已审核
CREATE proc [dbo].[HO_HistoryBatchMove_Two]
   @aDate datetime  = 0
as
  --------------------判断是否为主数据库,如果为辅助数据库则直接退出
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
----------------------------
--检验结果审核后，保留45天的数据，其它转移走
--保留在院病人的，结帐后的保留20天
  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('LAB1','LAC1')
    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('LAB2','LAC2')
          and b.type = 'U'
        group by b.name) b on b.name = a.fName
  
  if exists(select * from @tmpCols where fNum1 <> fNum2)
  begin
    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('LAB1','LAC1')
    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('LAB2','LAC2')
          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
    return 100
  end     
  
  if @aDate = 0 set @aDate = Getdate()
  declare @jyDate Datetime, @zyDate Datetime, @jyDay int,@zyDay int  
  
  set @jyDay = 45
  set @zyDay = 20
  
  set @jyDate = Convert(varchar(10), DATEADD(Day,-@jyDay,@aDate), 21)+' 00:00:00'
  set @zyDate = Convert(varchar(10), DATEADD(Day,-@zyDay,@aDate), 21)+' 00:00:00'
  
  create table #ExLAB(LAB01 int primary key nonclustered)

  insert into #ExLAB(LAB01)
  select LAB01
  from LAB1
  where ACF01 in (1,4,9) and LAB32 = 3 AND LAB53 <= @jyDate
  union all
  select a.LAB01
  from LAB1 a
  where a.ACF01 = 2 and a.LAB32 = 3 --AND a.LAB53 <= @zyDate
    and exists(select * from VAA1 b join VAE1 c on c.VAA01 = b.VAA01 
               where b.VAA04 = a.LAB09 and c.VAE44 = 5 and c.VAE26 < @zyDate and a.LAB53 <= c.VAE26)

  if not exists(select * from #ExLAB)
  begin
    if object_id('tempdb..#ExLAB') is not null drop table #ExLAB
    return 1
  end  
  
  if OBJECT_ID('tempdb..#tmpLAA1') is not null drop table #tmpLAA1
  select LAA01 into #tmpLAA1 from LAA1 where LAA28 < (GETDATE()-30)

begin tran

  INSERT into LAB2(LAB01, LAB02, VAA01, VAA07, VAP01, BCK01A, ABBRP, ABBRW, LAB09, VAA05, ABW01, LAB12, AAU01, LAB14, BDP02, LAB16, BES02
    , LAB18, ACF01, LAB20, LAB21, LAB22, LAB23, ABB02, BEY01A, BEY01B, BEW02, BCK01B, BEY07, LAB30, LAB31, LAB32, LAB33
    , LAB34, LAB35, LAB36, LAB37, BCE03, BCK01C, LAB40, VAF01, LAB42, LAB43, BCE03A, BCE03C, LAB46, BCE01D, BCE03D, BCE03E
    , LAB50, BCE01B, BCE03B, LAB53, LAB54, LAB55, LAB56, BET02, LAB58, LAB59, LAB60, LAB61, LAB62, LAB63, LAB64, BEX02
    , BCK01D, BAQ03,LAB01A)
  select A.LAB01, LAB02, VAA01, VAA07, VAP01, BCK01A, ABBRP, ABBRW, LAB09, VAA05, ABW01, LAB12, AAU01, LAB14, BDP02, LAB16, BES02
    , LAB18, ACF01, LAB20, LAB21, LAB22, LAB23, ABB02, BEY01A, BEY01B, BEW02, BCK01B, BEY07, LAB30, LAB31, LAB32, LAB33
    , LAB34, LAB35, LAB36, LAB37, BCE03, BCK01C, LAB40, VAF01, LAB42, LAB43, BCE03A, BCE03C, LAB46, BCE01D, BCE03D, BCE03E
    , LAB50, BCE01B, BCE03B, LAB53, LAB54, LAB55, LAB56, BET02, LAB58, LAB59, LAB60, LAB61, LAB62, LAB63, LAB64, BEX02
    , BCK01D, BAQ03,LAB01A
  from LAB1 a
  join #ExLAB b on b.LAB01 = a.LAB01  

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    if object_id('tempdb..#ExLAB') is not null drop table #ExLAB
    return 1
  end 

  delete a 
  from LAB1 a
  join #ExLAB b on b.LAB01 = a.LAB01   

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    if object_id('tempdb..#ExLAB') is not null drop table #ExLAB
    return 1
  end  

  insert into LAC2(LAC01, LAB01, BBX01, BBX01A, LAJ01, BFO01, BEY01, LAC08, LAC09, LAC10, LAC11, LAC12, LAC13, LAC14, LAC15, LAC16, LAC17 
    , LAC18, LAC19, BBY25, LAC21, BCE03A, LAC23, BCE03B, LAC25, LAC26, LAC27, ROWNR,LAC29,LAC30,VAF01 )
  select LAC01, a.LAB01, BBX01, BBX01A, LAJ01, BFO01, BEY01, LAC08, LAC09, LAC10, LAC11, LAC12, LAC13, LAC14, LAC15, LAC16, LAC17 
    , LAC18, LAC19, BBY25, LAC21, BCE03A, LAC23, BCE03B, LAC25, LAC26, LAC27, ROWNR,LAC29,LAC30,VAF01
  from LAC1 a
  join #ExLAB b on b.LAB01 = a.LAB01  

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    if object_id('tempdb..#ExLAB') is not null drop table #ExLAB
    return 1
  end  

  delete a
  from LAC1 a
  join #ExLAB b on b.LAB01 = a.LAB01  

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    if object_id('tempdb..#ExLAB') is not null drop table #ExLAB
    return 1
  end 

  --标本登记表  主子表数据  超过90天的转移
  INSERT INTO LAA1_2
  SELECT * FROM LAA1 A WHERE EXISTS(SELECT * FROM #tmpLAA1 B WHERE B.LAA01 = A.LAA01)
  INSERT INTO LAK1_2
  SELECT * FROM LAK1 A WHERE EXISTS(SELECT * FROM #tmpLAA1 B WHERE B.LAA01 = A.LAA01)

  delete from LAA1 WHERE EXISTS(SELECT * FROM #tmpLAA1 B WHERE B.LAA01 = LAA1.LAA01)
  delete from LAK1 WHERE EXISTS(SELECT * FROM #tmpLAA1 B WHERE B.LAA01 = LAK1.LAA01)

commit tran
if OBJECT_ID('tempdb..#tmpLAA1') is not null drop table #tmpLAA1
if object_id('tempdb..#ExLAB') is not null drop table #ExLAB



GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--存储过程.HO_HistoryBatchMove_Drug
--Alte 8058 2014.6.20
CREATE  PROCEDURE [dbo].[HO_HistoryBatchMove_Drug]
  @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
----------------------------
--更新医嘱提醒
  truncate table VBZ1

  InSert Into VBZ1(VAF01,ACF01,VAA07,VAF11,VAF10,VAA01,VAA04,VAA05,BCQ04,BCK01,VAF35) 
  select a.VAF01,2,a.VAF06,a.VAF11,a.VAF10,b.VAA01,c.VAA04,c.VAA05,b.BCQ04B,b.BCK01D,a.VAF35
  from VAF2 a
  join VAE1 b on b.VAE01 = a.VAF06
  join VAA1 c on c.VAA01 = b.VAA01
  where a.VAF04 = 2 and a.VAF32 = 0 and ((a.BDA01 < 'A')or(a.BDA01 >= 'A' and a.VAF01A = 0))
    and ((a.VAF10 = 1)or(a.VAF10 <= 8 and a.VAF11 = 1)) and b.VAE44 = 2 
--清理垃级数据
  delete from VAC4 where DFLAG = 1  
  delete from VAJT where DFLAG = 1  
  delete from VAFT where IsDel = 1  
  delete from FAE1 where DFLAG = 1   
  delete from DPT1_Log where sysdate < DATEADD(MONTH,-1,Getdate())
  delete from VBX1 where VBX22=1 and VBX03=2
  delete from VBX1 where VBX22=2 and VBX03=1
--药房门诊及住院病人发药数据三天前的都转走
--票据使用记录转移，对应领用记录使用完的全部转移走
  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('DPD1','DPD2','FAB1','FAC1','VAD1','FAF1')
    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('DPD1_2','DPD2_2','FAB2','FAC2','VAD2','FAF2')
          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('DPD1','DPD2','FAB1','FAC1','VAD1','FAF1')
    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('DPD1_2','DPD2_2','FAB2','FAC2','VAD2','FAF2')
          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 @yfDay int, @yfDate datetime, @ghDate datetime, @pjDay int,@pjDate datetime,@curdate datetime
    
  set @yfDay = 2 --药房
  set @pjDay = 60
  
  if @aDate = 0 set @aDate = Getdate()
  set @curdate = getdate()
  set @yfDate = Convert(varchar(10), DATEADD(Day,-@yfDay,@aDate), 21)+' 00:00:00'
  set @pjDate = Convert(varchar(10), DATEADD(Day,-@pjDay,@aDate), 21)+' 00:00:00'  
  set @ghDate = Convert(varchar(10), DATEADD(Day,-2,@aDate), 21)+' 00:00:00'

  Create table #tmpFAB(FAB01 int primary key,FAC01 int)
  create table #tmpVAD(VAD01 int primary key)
  create table #tmpFAF(FAF01 int primary key)

  select VAJ01  into #exVAJ
  from VAJ1
  union all
  select VAJ01
  from VAJ2
  
  select distinct a.BCK01,a.BillID  into #ExDPD_B
  from DPD2 a
  where not exists(select * from #exVAJ b where b.VAJ01 = a.VAJ01)
 
  if object_id('tempdb..#exVAJ') is not null drop table #exVAJ
  /*
  select c.BCK01,c.BillID  into #ExDPD_A
  from #ExDPD_B c
  where not exists(
    select a.BCK01,a.BillID
    from DPD2 a
    where not exists(select * from #ExDPD_B b where b.id = a.ID) and a.BCK01 = c.BCK01 and a.BillID = c.BillID ) 
  */
  if not exists(select * from #ExDPD_B)
  begin    
    --if object_id('tempdb..#ExDPD_A') is not null drop table #ExDPD_A
    if object_id('tempdb..#ExDPD_B') is not null drop table #ExDPD_B    
    return 0
  end  
  
  insert into #tmpFAB(FAB01,FAC01)
  select b.FAB01,b.FAC01
  from FAA1 a
  join FAB1 b on b.FAA01 = a.FAA01
  where a.FAA17 = 0 and b.FAB08 <= @pjDate

  insert into #tmpFAF(FAF01)
  select FAF01
  from FAF1
  where FAF12 <= @pjDate  

  insert into #tmpVAD(VAD01)
  select VAD01
  from VAD1
  where VAD05 >= 1 and VAD03 < @ghDate
  
begin tran

--DPD1
  insert into DPD1_2(BCK01, BillID
   , BillNO, DeptID, Accepter, Ratifier, Dispenser, Sender, MakeDate, Maker
   , Abstract, BillType, TradeType, ExpType, AuditingDate, Assessor, TradeID, TradeTerm
   , LPntTimes, GPntTimes, UserID, TerminalNO, TransferFlag, SysDate
   )
  select BCK01, BillID
   , BillNO, DeptID, Accepter, Ratifier, Dispenser, Sender, MakeDate, Maker
   , Abstract, BillType, TradeType, ExpType, AuditingDate, Assessor, TradeID, TradeTerm
   , LPntTimes, GPntTimes, UserID, TerminalNO, TransferFlag, SysDate
  from DPD1 a
  where exists(select * from #ExDPD_B b where b.bck01 = a.BCK01 and b.billid = a.BillID)

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 2
  end  
--DPD2
  insert into DPD2_2(ID, BCK01, BillID
   , xRowNum, BBY01, Unit, PackSize, BatchNo
   , Quantity, PurchasePrice, PurchaseAmount, RetailPrice, RetailAmount, CostPrice, CostAmount
   , VAA01, VAJ01, VAJ09, VAJ32, VAJ33, VAJ36, VAJ37, VAJ38, DPK_ID, Remark
  )
  select ID, BCK01, BillID
   , xRowNum, BBY01, Unit, PackSize, BatchNo
   , Quantity, PurchasePrice, PurchaseAmount, RetailPrice, RetailAmount, CostPrice, CostAmount
   , VAA01, VAJ01, VAJ09, VAJ32, VAJ33, VAJ36, VAJ37, VAJ38, DPK_ID, Remark
  from DPD2 a
  where exists(select * from #ExDPD_B b where b.bck01 = a.BCK01 and b.billid = a.BillID)

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 4
  end  
  --fab1
  insert into FAB2(FAB01, AAK01, FAB03, FAB04, FAB05, FAA01, FAC01, FAB08, BCE01, BCE03, BCE03B, FAB12, FAB13, FAB14,FAB15,FAB16,FAB17,FAB18)
  select a.FAB01, a.AAK01, a.FAB03, a.FAB04, a.FAB05, a.FAA01, a.FAC01, a.FAB08, a.BCE01, a.BCE03, a.BCE03B, a.FAB12, a.FAB13, a.FAB14
    ,a.FAB15,a.FAB16,a.FAB17,a.FAB18
  from FAB1 a
  join #tmpFAB b on b.FAB01 = a.FAB01
  
  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 6
  end  
  --fac1
  insert into FAC2(FAC01, FAC02, FAC03, FAC04 )
  select a.FAC01, a.FAC02, a.FAC03, a.FAC04
  from FAC1 a
  join (select distinct FAC01 from #tmpFAB) b on b.FAC01 = a.FAC01
  
  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 8
  end
--VAD1  
  insert into VAD2(VAD01, BCB01, VAD03, VAC01, VAD05, BCE03, ROWNR, ABW01, VAD10, AAU01, BAS02, VAD08
    , BCK03A, BCE03B, VAD15, BAO02, VAD17, VAD18, VAD19, VAD20, VAD21, VAD22, BCK01A, BCE01B)
  select a.VAD01, BCB01, VAD03, VAC01, VAD05, BCE03, ROWNR, ABW01, VAD10, AAU01, BAS02, VAD08
    , BCK03A, BCE03B, VAD15, BAO02, VAD17, VAD18, VAD19, VAD20, VAD21, VAD22, BCK01A, BCE01B
  from VAD1 a
  join #tmpVAD b on b.VAD01 = a.VAD01
  
  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 10
  end
 --FAF1  
  insert into FAF2(FAF01, FAF01A, FAF01B, VAA01, VAA07, ACF01, FAF07, FAF08, FAF09, FAF10, FAF11, FAF12, FAF13, FAF14, FAF15, FAF16, FAF17, FAF18, FAF19, FAF20, FAF21, FAF01C, FAF23, FAF24, FAF25, FAF26, FAF27, BCE01, BCE03, FAF30, BCE01B, BCE03B, FAF33, FAF34, FAF35, FAF36, FAF37, FAF38, FAF39)
  select         a.FAF01, FAF01A, FAF01B, VAA01, VAA07, ACF01, FAF07, FAF08, FAF09, FAF10, FAF11, FAF12, FAF13, FAF14, FAF15, FAF16, FAF17, FAF18, FAF19, FAF20, FAF21, FAF01C, FAF23, FAF24, FAF25, FAF26, FAF27, BCE01, BCE03, FAF30, BCE01B, BCE03B, FAF33, FAF34, FAF35, FAF36, FAF37, FAF38, FAF39 
  from FAF1 a
  join #tmpFAF b on b.FAF01 = a.FAF01
  
  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 12
  end 

  -----------------删除1表数据--------------
  delete 
  from DPD1   
  where exists(select * from #ExDPD_B d1 where d1.BCK01 = DPD1.BCK01 and d1.BillID = DPD1.BillID)
  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 3
  end
  delete 
  from DPD2 
  where exists(select * from #ExDPD_B d1 where d1.BCK01 = DPD2.BCK01 and d1.BillID = DPD2.BillID)
  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 5
  end  
  delete a
  from FAB1 a
  join #tmpFAB b on b.FAB01 = a.FAB01
  
  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 7
  end
  delete a
  from FAC1 a
  join (select distinct FAC01 from #tmpFAB) b on b.FAC01 = a.FAC01
  
  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 9
  end

  delete a
  from VAD1 a
  join #tmpVAD b on b.VAD01 = a.VAD01
  
  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 11
  end

  
  delete a
  from FAF1 a
  join #tmpFAF b on b.FAF01 = a.FAF01
  
  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 13
  end   
commit tran

--清除临时表
if object_id('tempdb..#ExDPD_A') is not null drop table #ExDPD_A
if object_id('tempdb..#ExDPD_B') is not null drop table #ExDPD_B
if object_id('tempdb..#tmpFAB') is not null drop table #tmpFAB
if object_id('tempdb..#tmpVAD') is not null drop table #tmpVAD
if object_id('tempdb..#tmpFAF') is not null drop table #tmpFAF
end







GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--存储过程.HO_HistoryBatchMove
--Alte 8058 2014.3.12  转移住院数据到离线表
CREATE  PROCEDURE [dbo].[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')
    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')
          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')
    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')
          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 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 #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 #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
  
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)
  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
  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 )
  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
  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)
  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
  from #hokbmVAJ a

  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)
  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
  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)
  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
  from #hokbmVAF a

  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)
  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
  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)
  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
  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 )
  select a.VBD01, a.VAF01, a.BBY01, a.VBD04, a.VBD05, a.VBD06, a.BCK01, a.VBD08, a.VBD09, a.VBD10
  from #hokbmVBD a

  if (@@ERROR <> 0)
  begin
    if @@trancount <> 0 rollback tran
    return 17
  end    
--VBG1
  insert into VBG2(VBG01, VAF01, VBG03, BCE03, VBG05, VBG06)
  select VBG01, VAF01, VBG03, BCE03, VBG05, VBG06
  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 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 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   
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


end


GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--存储过程.HO_DPT_BatchUpdate
--alter 2014.4.17
  CREATE proc [dbo].[HO_DPT_BatchUpdate]
    @aHour int = 12
    ,@aType tinyint = 1
    ,@aRate int = 100  --此值为百分比
    ,@aDay int = 7 --删除多少天前数量为零的记录
	,@iday int = 0 --待发药多少天过期，不能再发药，只能退费
  as
  --------------------判断是否为主数据库,如果为辅助数据库则直接退出
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
----------------------------
    --在院的只要没发药的全部计算, 门诊、住院退药部分除外
    --@aType: 
	--0 = 在给定时间内，门诊不分结帐还是没结帐只要没发药的都计算在内
    --1 = 往前推一天，即从昨天下午五点为分界线，门诊只计算五点以后的，不分结帐没结帐只要没发药的
    --2 = 门诊只处理结帐的，没结帐的一律不计算可用库存
    --3 = 给定时间内，门诊结帐没发药的全部计算，划价没结帐的按百分比计算可用库存
	declare @aDate Datetime, @bDate Datetime, @eDate datetime,@idate datetime
    set @aDate = GETDATE()
	
	if @iday>0
	begin
	  set @idate =  Convert(varchar(10), DATEADD(DAY,-@iday,@aDate), 21)
	  update VAJ1 set VAJ53 = 4 where VAJ47 <= @idate and  VAJ48 = 0 and VAJ53 = 0 and ACF01 = 1 and BDN01 >='1' and BDN01 <= '3' and VAK01>0
	end
    
    if dbo.GetSysParamValue(9999,9999,46)='1'
    begin
    --禁用预扣库存
      update DPT1 set SellQty = 0, Quantity = RefQty
      update a set a.BAL08 = isnull(b.BAL08,0)
      from DPT1 a
      join BAL1 b on b.BCK01 = a.BCK01 and b.BBY01 = a.BBY01
      
      return 0
    end    
    if object_id('tempdb..#tmpDPT') is not null drop table #tmpDPT
    if object_id('tempdb..#kbmDpt') is not null drop table #kbmDpt
    if object_id('tempdb..#kbmDptNo') is not null drop table #kbmDptNo    
    
    create table #tmpDPT(BCK01 int,BBY01 int,DSK01 int,VAJ25 numeric(18,6))
    
    if @aType = 1
    begin      
      set @bDate =  Convert(varchar(10), DATEADD(DAY,-1,@aDate), 21)+' 17:00:00'  
      set @eDate = Convert(varchar(10), DATEADD(DAY,-1,@aDate), 21)+' 17:00:00'  
      
      insert into #tmpDPT(BCK01,BBY01,VAJ25,DSK01)  
      select BCK01D, BBY01, SUM(VAJ25) VAJ25, DSK01
      from (      
        select BCK01D, BBY01,VAJ25,DSK01 
        from VAJ1
        where VAJ48 = 0 and VAJ53 = 0 and ACF01 <> 2 and BDN01 >='1' and BDN01 <= '3' --
          and VAJ05 <= 4 and VAJ47 >= @eDate  
        union all
        select BCK01D, BBY01,VAJ25,DSK01 
        from VAJ2
        where VAK01=0 and  VAJ48 = 0 and VAJ53 = 0 and ACF01 = 2 and BDN01 >='1' and BDN01 <= '3' --         
        ) s   
      group by s.BCK01D, s.BBY01,s.DSK01
    end
    
    if @aType = 0
    begin
      set @bDate =  Convert(varchar(13), DATEADD(HOUR,-@aHour,@aDate), 21)+':00:00'
      
      insert into #tmpDPT(BCK01,BBY01,DSK01,VAJ25)  
      select BCK01D, BBY01,DSK01, SUM(VAJ25) VAJ25 
      from (
        select BCK01D, BBY01,DSK01 ,VAJ25
        from VAJ1
        where VAJ48 = 0 and VAJ53 = 0 and ACF01 <>2 and BDN01 >='1' and BDN01 <= '3' -- 
          and VAJ05 <= 4 and VAJ47 >= @bDate
        union all
        select BCK01D, BBY01,DSK01 ,VAJ25
        from VAJ2
        where VAK01=0 and  VAJ48 = 0 and VAJ53 = 0 and ACF01 = 2 and BDN01 >='1' and BDN01 <= '3' --           
        ) s   
      group by s.BCK01D, s.BBY01,s.DSK01
    end
    
    if @aType = 2
    begin
      insert into #tmpDPT(BCK01,BBY01,DSK01,VAJ25)  
      select BCK01D, BBY01,DSK01, SUM(VAJ25) VAJ25 
      from (
        select BCK01D, BBY01,DSK01,VAJ25 
        from VAJ1
        where VAJ48 = 0 and VAJ53 = 0 and ACF01 <>2 and BDN01 >='1' and BDN01 <= '3' -- 
          and VAK01 >= 1
        union all
        select BCK01D, BBY01,DSK01,VAJ25 
        from VAJ2
        where VAK01=0 and  VAJ48 = 0 and VAJ53 = 0 and ACF01 = 2 and BDN01 >='1' and BDN01 <= '3' --           
        ) s   
      group by s.BCK01D, s.BBY01,s.DSK01
    end
    
    if @aType = 3
    begin
      set @bDate =  Convert(varchar(13), DATEADD(HOUR,-@aHour,@aDate), 21)+':00:00'
      
      insert into #tmpDPT(BCK01,BBY01,DSK01,VAJ25)  
      select BCK01D, BBY01,DSK01, SUM(VAJ25) VAJ25 
      from (
        select BCK01D, BBY01,DSK01,VAJ25 
        from VAJ1
        where VAJ48 = 0 and VAJ53 = 0 and ACF01 <>2 and BDN01 >='1' and BDN01 <= '3'  and VAK01 > 0
          and VAJ05 <= 4 and VAJ47 >= @bDate
        union all
        select BCK01D, BBY01,DSK01,ceiling(VAJ25*(@aRate / 100))
        from VAJ1
        where VAJ48 = 0 and VAJ53 = 0 and ACF01 <>2 and BDN01 >='1' and BDN01 <= '3'  and isnull(VAK01,0) = 0
          and VAJ05 <= 4 and VAJ47 >= @bDate  
        union all
        select BCK01D, BBY01,DSK01,VAJ25 
        from VAJ2
        where VAK01=0 and VAJ48 = 0 and VAJ53 = 0 and ACF01 = 2 and BDN01 >='1' and BDN01 <= '3'            
        ) s   
      group by s.BCK01D, s.BBY01,s.DSK01
    end
    
    if not exists(select * from #tmpDPT)
    begin
      if object_id('tempdb..#tmpDPT') is not null drop table #tmpDPT
      return 99
    end  
   update #tmpDPT set VAJ25 = 0 WHERE VAJ25 < 0
   
   select a.BCK01,a.BBY01,min(a.DSK_ID) DSK01
   into #kbmDpt
   from DPT1 a
   where a.Quantity > 0
   group by a.BCK01,a.BBY01 
   
   set @bDate =  Convert(varchar(13), DATEADD(DAY,-@aDay,@aDate), 21)+':00:00'
   
   select a.BCK01,a.BBY01,SUM(a.VAJ25) VAJ25
   into #kbmDptNo
   from #tmpDpt a
   where not exists(select * from DPT1 b where b.BCK01 = a.bck01 and b.BBY01 = a.bby01 and b.DSK_ID = a.dsk01)
   group by a.BCK01,a.BBY01  
  begin tran      
    update DPT1 set SellQty = 0,Quantity = RefQty
    
    update a
    set a.SellQty = b.VAJ25,a.Quantity = a.RefQty - b.VAJ25
    from DPT1 a
    join #tmpDPT b on b.BCK01 = a.BCK01 and a.BBY01 = b.BBY01 and b.DSK01 = a.DSK_ID
    
    if @@ERROR <> 0
    begin
      RollBack tran
      if object_id('tempdb..#tmpDPT') is not null drop table #tmpDPT
      Return 1
    end  
    
    update a
    set a.SellQty = a.SellQty + c.VAJ25,a.Quantity = a.Quantity - c.VAJ25
    from DPT1 a
    join #kbmDpt b on b.BCK01 = a.BCK01 and b.BBY01 = a.BBY01 and b.DSK01 = a.DSK_ID
    join #kbmDptNo c on c.BCK01 = b.BCK01 and c.BBY01 = b.BBY01
    
    if @@ERROR <> 0
    begin
      RollBack tran
      if object_id('tempdb..#tmpDPT') is not null drop table #tmpDPT
      Return 1
    end  
    
    delete from DPT1 
    where (SysDate < @bDate and Quantity = 0 and RefQty = 0)
      or not exists(select * from BAZ1 b where b.BCK01 = DPT1.BCK01 and b.BAU01 in ('51','52','53'))
    
    update a set a.BAL08 = isnull(b.BAL08,0)
    from DPT1 a
    join BAL1 b on b.BCK01 = a.BCK01 and b.BBY01 = a.BBY01
  commit tran
  if object_id('tempdb..#tmpDPT') is not null drop table #tmpDPT
  if object_id('tempdb..#kbmDpt') is not null drop table #kbmDpt
  if object_id('tempdb..#kbmDptNo') is not null drop table #kbmDptNo

	declare @BCK01 int
	select distinct bck01 into #bck from dpk1
	while exists(select * from #bck)
	begin
	select @BCK01=(select top 1 BCK01 from #bck)
	exec dps_dpt1_redo @BCK01
	delete #bck where BCK01=@BCK01
	end
	drop table #bck
    
    

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--存储过程.HO_DatabaseDump
CREATE PROC [dbo].[HO_DatabaseDump]
  @File varchar(255)--文件路径  
  , @Schema tinyint = 1--文件命名方案，样式1: yyyy_MM_dd_HHNNSS，样式2: weekday
  , @diff tinyint=0 --是否差异备份，0表示完全备份，1表示差异备份,3表示备份日志
AS
  --------------------判断是否为主数据库,如果为辅助数据库则直接退出
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
----------------------------
declare
  @Name varchar(255)
,@Filename varchar(255)--文件名
,@DBname varchar(255) --数据库名

set @Name = ''
set @Filename=''
set @DBname=DB_NAME()
--样式: yyyy_MM_dd_HHNNSS
  if @Schema = 1
    set @Name = @DBname+replace(convert(char(10), getdate(), 120), '-', '_')
      + '_' + replace(convert(char(8), getdate(), 108), ':', '')
  else
--样式: weekday
  if @Schema = 2
    set @Name = @DBname+convert(varchar(10), datepart(dw, getdate()))
  
  if @diff = 0 
  begin
  set @File = @File + '\' + @Name + '_full.bak'
  backup database @DBname to disk = @File with init
  end
  
  if @diff = 1
  begin
  set @File = @File + '\' + @Name + '_diff.bak'
  backup database @DBname to disk = @File with DIFFERENTIAL
  end

  if @diff in(2,3)
  begin
     set @File = @File + '\' + @Name + '_log.trn'
     set @Filename=@Name+'_log'
     BACKUP LOG @DBname TO  DISK = @file WITH  RETAINDAYS = 25, NOFORMAT, NOINIT,  NAME = @Filename, SKIP, REWIND, NOUNLOAD,  STATS = 10
   end



GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--存储过程.HO_DPT_BatchUpdate
--alter 2014.4.17
  ALTER proc [dbo].[HO_DPT_BatchUpdate]
    @aHour int = 12
    ,@aType tinyint = 1
    ,@aRate int = 100  --此值为百分比
    ,@aDay int = 7 --删除多少天前数量为零的记录
	,@iday int = 0 --待发药多少天过期，不能再发药，只能退费
  as
  --------------------判断是否为主数据库,如果为辅助数据库则直接退出
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
----------------------------
    --在院的只要没发药的全部计算, 门诊、住院退药部分除外
    --@aType: 
	--0 = 在给定时间内，门诊不分结帐还是没结帐只要没发药的都计算在内
    --1 = 往前推一天，即从昨天下午五点为分界线，门诊只计算五点以后的，不分结帐没结帐只要没发药的
    --2 = 门诊只处理结帐的，没结帐的一律不计算可用库存
    --3 = 给定时间内，门诊结帐没发药的全部计算，划价没结帐的按百分比计算可用库存
	declare @aDate Datetime, @bDate Datetime, @eDate datetime,@idate datetime
    set @aDate = GETDATE()
	
	if @iday>0
	begin
	  set @idate =  Convert(varchar(10), DATEADD(DAY,-@iday,@aDate), 21)
	  update VAJ1 set VAJ53 = 4 where VAJ47 <= @idate and  VAJ48 = 0 and VAJ53 = 0 and ACF01 = 1 and BDN01 >='1' and BDN01 <= '3' and VAK01>0
	end
	
	declare @BCK01 int
	select distinct bck01 into #bck from dpk1
	while exists(select * from #bck)
	begin
	select @BCK01=(select top 1 BCK01 from #bck)
	exec dps_dpt1_redo @BCK01
	delete #bck where BCK01=@BCK01
	end
	drop table #bck
    
    if dbo.GetSysParamValue(9999,9999,46)='1'
    begin
    --禁用预扣库存
      update DPT1 set SellQty = 0, Quantity = RefQty
      update a set a.BAL08 = isnull(b.BAL08,0)
      from DPT1 a
      join BAL1 b on b.BCK01 = a.BCK01 and b.BBY01 = a.BBY01
      
      return 0
    end    
    if object_id('tempdb..#tmpDPT') is not null drop table #tmpDPT
    if object_id('tempdb..#kbmDpt') is not null drop table #kbmDpt
    if object_id('tempdb..#kbmDptNo') is not null drop table #kbmDptNo    
    
    create table #tmpDPT(BCK01 int,BBY01 int,DSK01 int,VAJ25 numeric(18,6))
    
    if @aType = 1
    begin      
      set @bDate =  Convert(varchar(10), DATEADD(DAY,-1,@aDate), 21)+' 17:00:00'  
      set @eDate = Convert(varchar(10), DATEADD(DAY,-1,@aDate), 21)+' 17:00:00'  
      
      insert into #tmpDPT(BCK01,BBY01,VAJ25,DSK01)  
      select BCK01D, BBY01, SUM(VAJ25) VAJ25, DSK01
      from (      
        select BCK01D, BBY01,VAJ25,DSK01 
        from VAJ1
        where VAJ48 = 0 and VAJ53 = 0 and ACF01 <> 2 and BDN01 >='1' and BDN01 <= '3' --
          and VAJ05 <= 4 and VAJ47 >= @eDate  
        union all
        select BCK01D, BBY01,VAJ25,DSK01 
        from VAJ2
        where VAK01=0 and  VAJ48 = 0 and VAJ53 = 0 and ACF01 = 2 and BDN01 >='1' and BDN01 <= '3' --         
        ) s   
      group by s.BCK01D, s.BBY01,s.DSK01
    end
    
    if @aType = 0
    begin
      set @bDate =  Convert(varchar(13), DATEADD(HOUR,-@aHour,@aDate), 21)+':00:00'
      
      insert into #tmpDPT(BCK01,BBY01,DSK01,VAJ25)  
      select BCK01D, BBY01,DSK01, SUM(VAJ25) VAJ25 
      from (
        select BCK01D, BBY01,DSK01 ,VAJ25
        from VAJ1
        where VAJ48 = 0 and VAJ53 = 0 and ACF01 <>2 and BDN01 >='1' and BDN01 <= '3' -- 
          and VAJ05 <= 4 and VAJ47 >= @bDate
        union all
        select BCK01D, BBY01,DSK01 ,VAJ25
        from VAJ2
        where VAK01=0 and  VAJ48 = 0 and VAJ53 = 0 and ACF01 = 2 and BDN01 >='1' and BDN01 <= '3' --           
        ) s   
      group by s.BCK01D, s.BBY01,s.DSK01
    end
    
    if @aType = 2
    begin
      insert into #tmpDPT(BCK01,BBY01,DSK01,VAJ25)  
      select BCK01D, BBY01,DSK01, SUM(VAJ25) VAJ25 
      from (
        select BCK01D, BBY01,DSK01,VAJ25 
        from VAJ1
        where VAJ48 = 0 and VAJ53 = 0 and ACF01 <>2 and BDN01 >='1' and BDN01 <= '3' -- 
          and VAK01 >= 1
        union all
        select BCK01D, BBY01,DSK01,VAJ25 
        from VAJ2
        where VAK01=0 and  VAJ48 = 0 and VAJ53 = 0 and ACF01 = 2 and BDN01 >='1' and BDN01 <= '3' --           
        ) s   
      group by s.BCK01D, s.BBY01,s.DSK01
    end
    
    if @aType = 3
    begin
      set @bDate =  Convert(varchar(13), DATEADD(HOUR,-@aHour,@aDate), 21)+':00:00'
      
      insert into #tmpDPT(BCK01,BBY01,DSK01,VAJ25)  
      select BCK01D, BBY01,DSK01, SUM(VAJ25) VAJ25 
      from (
        select BCK01D, BBY01,DSK01,VAJ25 
        from VAJ1
        where VAJ48 = 0 and VAJ53 = 0 and ACF01 <>2 and BDN01 >='1' and BDN01 <= '3'  and VAK01 > 0
          and VAJ05 <= 4 and VAJ47 >= @bDate
        union all
        select BCK01D, BBY01,DSK01,ceiling(VAJ25*(@aRate / 100))
        from VAJ1
        where VAJ48 = 0 and VAJ53 = 0 and ACF01 <>2 and BDN01 >='1' and BDN01 <= '3'  and isnull(VAK01,0) = 0
          and VAJ05 <= 4 and VAJ47 >= @bDate  
        union all
        select BCK01D, BBY01,DSK01,VAJ25 
        from VAJ2
        where VAK01=0 and VAJ48 = 0 and VAJ53 = 0 and ACF01 = 2 and BDN01 >='1' and BDN01 <= '3'            
        ) s   
      group by s.BCK01D, s.BBY01,s.DSK01
    end
    
    if not exists(select * from #tmpDPT)
    begin
      if object_id('tempdb..#tmpDPT') is not null drop table #tmpDPT
      return 99
    end  
   update #tmpDPT set VAJ25 = 0 WHERE VAJ25 < 0
   
   select a.BCK01,a.BBY01,min(a.DSK_ID) DSK01
   into #kbmDpt
   from DPT1 a
   where a.Quantity > 0
   group by a.BCK01,a.BBY01 
   
   set @bDate =  Convert(varchar(13), DATEADD(DAY,-@aDay,@aDate), 21)+':00:00'
   
   select a.BCK01,a.BBY01,SUM(a.VAJ25) VAJ25
   into #kbmDptNo
   from #tmpDpt a
   where not exists(select * from DPT1 b where b.BCK01 = a.bck01 and b.BBY01 = a.bby01 and b.DSK_ID = a.dsk01)
   group by a.BCK01,a.BBY01  
  begin tran      
    update DPT1 set SellQty = 0,Quantity = RefQty
    
    update a
    set a.SellQty = b.VAJ25,a.Quantity = a.RefQty - b.VAJ25
    from DPT1 a
    join #tmpDPT b on b.BCK01 = a.BCK01 and a.BBY01 = b.BBY01 and b.DSK01 = a.DSK_ID
    
    if @@ERROR <> 0
    begin
      RollBack tran
      if object_id('tempdb..#tmpDPT') is not null drop table #tmpDPT
      Return 1
    end  
    
    update a
    set a.SellQty = a.SellQty + c.VAJ25,a.Quantity = a.Quantity - c.VAJ25
    from DPT1 a
    join #kbmDpt b on b.BCK01 = a.BCK01 and b.BBY01 = a.BBY01 and b.DSK01 = a.DSK_ID
    join #kbmDptNo c on c.BCK01 = b.BCK01 and c.BBY01 = b.BBY01
    
    if @@ERROR <> 0
    begin
      RollBack tran
      if object_id('tempdb..#tmpDPT') is not null drop table #tmpDPT
      Return 1
    end  
    
    delete from DPT1 
    where (SysDate < @bDate and Quantity = 0 and RefQty = 0)
      or not exists(select * from BAZ1 b where b.BCK01 = DPT1.BCK01 and b.BAU01 in ('51','52','53'))
    
    update a set a.BAL08 = isnull(b.BAL08,0)
    from DPT1 a
    join BAL1 b on b.BCK01 = a.BCK01 and b.BBY01 = a.BBY01
  commit tran
  if object_id('tempdb..#tmpDPT') is not null drop table #tmpDPT
  if object_id('tempdb..#kbmDpt') is not null drop table #kbmDpt
  if object_id('tempdb..#kbmDptNo') is not null drop table #kbmDptNo