SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--存储过程.Nurse_Long_Advice_Send

--Alter 01682 2020.5.7  增加BAG56西成药允许按半片半只售卖时，VAj25的处理
--Alter 01682 2020.2.14 增加病情的自动调整
--Alter 01682 2020.1.2   VBD2的逻辑删除过滤
--Alter 01682 2019.11.29 启用参数后给药途径自动执行完成
--Alter 01682 2019.11.7 增加VEG12的保存
--Alter 01682 2019.10.28 扩展参数262启用后，数量计算的处理
--Alter 01682 2019.10.24 扩展参数260启用后，收费对照当天只收取一次的设置不检查是否已经结账
--Alter 01682 2019.8.31 启用扩展参数241后卫材预扣库存 
--Alter 01682 2019.9.9  增加往BTC1中插入数据
--Alter 01682 2019.8.16 不再从BAG1中取值
--Alter 8058 2020.5.23 增加 '当天按最大频次收取，后续频次超过会自动补收'
--Alter 01682 2020.3.24  DTC1部分修改
--Alter 01063 2019.7.3 外配处方调整
--护士工作站 长嘱发送 住院病人

ALTER  PROC [dbo].[Nurse_Long_Advice_Send]
  @lXml  nText
  , @lBCKxml nText --药房置换
  , @lSign smallint  --6:住院记账
  , @lOne  smallint  --0:发送为多张单据；1：发送为单张单据
  , @lBCE02 varchar(20)  --当前操作员编号
  , @lBCE03 varchar(20)  --当前操作员姓名
  , @lBCK01B int
  , @lEndDate datetime
  , @lFlag tinyint  --1:表示给药途径已结束时间计算 0: 否
  , @lOneDay tinyint --1:表示只执行当天的  2=护士确认停嘱执行
  , @lBCE01 int
  ,@ReVAIid varchar(256) = '' out
  ,@ReVafid_hz varchar(256) = '' out
  ,@ReVafid_zk varchar(256) = '' out
AS
 set @ReVAIid = ''
 set @ReVafid_hz = ''
 set @ReVafid_zk = ''
  DECLARE @iDOM int, @iret int
  Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @lXml

  if @iret > 0
  BEGIN
    exec sp_xml_removedocument @iDOM	
    RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @iret) with nowait
    return @iret
  END 
  
 -- IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz
  IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA
  IF Object_id('tempdb..#TmpVAJls') IS NOT NULL DROP TABLE #TmpVAJls
  IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD
  IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI

  select @lBCE02 = BCE02,@lBCE03 = BCE03 from BCE1 with(nolock) where BCE01 = @lBCE01
  --取得需要发送的医嘱id
  
  declare @TmpVAFcz Table(ID int IDENTITY(1,1),VAF01 int primary key, BDA01 varchar(2)
     ,VAF01A int, Rownr int, CBM01 int,VAF10 tinyint,BCK01B int, BCK01C int,BCK01D int
     ,BCE01A int,BCE02A varchar(20),BCE03A varchar(20),VAF32 tinyint,VAF58 tinyint
     ,ACF01 tinyint,VAF18 numeric(18,4),VAF21 numeric(18,4),VAA01 int,VAA07 int,VAP01 int
     ,BCK01A int,VAF35 tinyint,VAF22 varchar(1024),VBI13 tinyint,VAI01 int,VBI07 numeric(18,4)
     ,VAF36 datetime,BCK01F int,ABC02 varchar(20),VAF14 varchar(128),BBX01 int,VAF31 tinyint,VAJ57 varchar(128),VAF01B int
     ,VAF37 datetime,VAF38 datetime,VAF27 int,VAF28 tinyint,VAF29 varchar(4),VAF61 numeric(8,2),VBG01 int
     ,VAF62 numeric(8,2),aVAF38 datetime,VAF47 datetime,VAF50 datetime,BCE01D int,BCE03D varchar(20)
     ,BCE01E int,BCE03E varchar(20),BCE01F int,BCE03F varchar(20),BBX17 tinyint,VAF34 tinyint,VAF75 tinyint
     ,BCK01E int,VAF23 varchar(512),VAJ57B varchar(128),bck01c_br int,bck01d_br int,VAF15 varchar(256),VEH07 varchar(20),DSK01 int
	 ,VAF26 varchar(64),VEH09 datetime,VAF30 varchar(64),VEH26 tinyint, VEH27 tinyint,BAG56 tinyint )
     
  Declare @Tmp_BCK Table(BCK01 int, BCK01A int)   

  INSERT INTO @TmpVAFcz(VAF01,VAF62,BCE01E,BCE03E)
  SELECT VAF01,case when isnull(VAF62,0) < 0 then 0 else isnull(VAF62,0) end,BCE01E,BCE03E
  from OpenXml(@iDOM,'/Root/VAF1/Ie',8)
    WITH (VAF01 int     
     ,VAA01 int,VAF62 numeric(8,2),VAF11 tinyint,BCE01E int,BCE03E varchar(64))       
  where VAF11 = 1   
  
  exec sp_xml_removedocument @iDOM 
  If not exists(select * from @TmpVAFcz)or (@@ERROR <> 0)
  BEGIN
   RAISERROR('从文档中提取医嘱信息错误.', 16, 1) with nowait
   RETURN 2
  END     

  Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @lBCKxml

  if @iret > 0
  BEGIN
    exec sp_xml_removedocument @iDOM
    --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz
    IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA
    IF Object_id('tempdb..#TmpVAJls') IS NOT NULL DROP TABLE #TmpVAJls
    IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD
    IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI	
    RAISERROR('解析药房置换XML文档时发生错误，错误号: %d.', 16, 1, @iret) with nowait
    return @iret
  END  	
  
  INSERT INTO @Tmp_BCK(BCK01,BCK01A)
  SELECT BCK01,BCK01A
  from OpenXml(@iDOM,'/Root/BCK1/Ie',8)
        WITH (BCK01 int, BCK01A int)	
        
  exec sp_xml_removedocument @iDOM 
    
  If not exists(select * from @TmpVAFcz)
  BEGIN   
   --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz
   IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA
   IF Object_id('tempdb..#TmpVAJls') IS NOT NULL DROP TABLE #TmpVAJls
   IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD
   IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI
   RETURN 1
  END    
  
  UPDATE a
  SET a.BDA01 = b.BDA01,a.VAF01A = b.VAF01A,a.Rownr = b.ROWNR,a.CBM01 = b.CBM01,a.VAF10 = b.VAF10
    ,a.BCK01B = b.BCK01B,a.BCK01C = b.BCK01C,a.BCK01D = b.BCK01D,a.BCE02A = b.BCE02A,a.BCE03A = b.BCE03A
    ,a.VAF32 = b.VAF32,a.VAF58 = b.VAF58,a.ACF01 = b.VAF04,a.VAF18 = b.VAF18,a.BCE01A = b.BCE01A
    ,a.VAF21 = b.VAF21,a.VAA01 = b.VAA01,a.VAA07 = b.VAF06,a.VAP01 = b.VAF07,a.BCK01A = b.BCK01A
    ,a.VAF35 = b.VAF35,a.VAF22 = b.VAF22,a.VAF23 = b.VAF23 ,a.BCK01E = c.BCK01D   
    ,a.VAF36 = b.VAF36,a.BCK01F = c.BCK01C,a.ABC02 = c.ABC02,a.VAF14 = b.VAF14,a.BBX01 = b.BBX01
    ,a.VAF31 = b.VAF31 ,a.VAJ57 = d.BBX05,a.VAF01B = b.VAF01B,a.VAF38 = b.VAF38,a.VAF27 = b.VAF27
    ,a.VAF28 = b.VAF28, a.VAF29 = b.VAF29,a.VAF37 = b.VAF37,a.VAF61 = b.VAF61,a.VAF62 = case when @lOneDay = 2 then a.VAF62 else b.VAF62 end
    ,a.aVAF38 = b.VAF38,a.VAF47 = b.VAF47,a.VAF50 = case when @lOneDay = 2 then @lEndDate else  b.VAF50 end,a.BCE01D = b.BCE01D,a.BCE03D = b.BCE03D
    ,a.BCE01E =case when @lOneDay = 2 then a.BCE01E else b.BCE01E end
    ,a.BCE03E = case when @lOneDay = 2 then a.BCE03E else b.BCE03E end
    ,a.BCE01F = b.BCE01F,a.BCE03F = b.BCE03F,a.VAF34 = isnull(b.VAF34,0)
    ,a.VAF75 = b.VAF75,a.bck01c_br = c.BCK01C,a.bck01d_br = c.BCK01D,a.VBI13 = 0,a.VAF15=b.VAF15,a.DSK01=b.DSK01,a.VAF26=b.VAF26,a.VAF30=b.VAF30  
  FROM @TmpVAFcz a
  JOIN VAF2 b ON b.VAF01 = a.VAF01  
  JOIN VAE1 c with(nolock) on c.VAE01 = b.VAF06
  left join BBX1 d with(nolock) on d.BBX01 = b.VAF01B 

  Insert into @TmpVAFcz(VAF01, BDA01 ,VAF01A, Rownr, CBM01,VAF10,BCK01B , BCK01C ,BCK01D ,BCE02A ,BCE03A 
      ,VAF32 ,VAF58,ACF01,VAF18,VAF21,VAA01,VAA07,VAP01,BCK01A,VAF35,VAF22,VAF36,BCK01F,ABC02
      ,VAF14,BCE01A,BBX01,VAF31,VAJ57,VAF01B,VAF37,VAF38,VAF27,VAF28,VAF29,VAF61,VAF62,aVAF38
      ,VAF47,VAF50,BCE01D,BCE03D,BCE01E,BCE03E,BCE01F,BCE03F,VAF34,VAF75,BCK01E,VAF23,bck01c_br,bck01d_br,VBI13,VAF15,DSK01,VAF26)
  Select a.VAF01, a.BDA01 ,a.VAF01A, a.Rownr, a.CBM01,a.VAF10,a.BCK01B ,a.BCK01C ,a.BCK01D ,a.BCE02A 
    ,a.BCE03A,a.VAF32 ,a.VAF58,a.VAF04,a.VAF18,a.VAF21,a.VAA01,a.VAF06,a.VAF07,a.BCK01A,a.VAF35
    ,a.VAF22,a.VAF36,c.BCK01C,c.ABC02,a.VAF14,a.BCE01A,a.BBX01,a.VAF31,d.BBX05,a.VAF01B
    ,a.VAF37,a.VAF38,a.VAF27,a.VAF28,a.VAF29,a.VAF61,case when @lOneDay = 2 then b.VAF62 else a.VAF62 end ,a.VAF38
    ,a.VAF47,case when @lOneDay = 2 then @lEndDate else a.VAF50 end,a.BCE01D,a.BCE03D
    ,case when @lOneDay = 2 then b.BCE01E else a.BCE01E end,case when @lOneDay = 2 then b.BCE03E else a.BCE03E end
    ,a.BCE01F,a.BCE03F,a.VAF34,a.VAF75,c.BCK01D,a.VAF23,c.BCK01C,c.BCK01D,0,a.VAF15,a.DSK01,a.VAF26
  From @TmpVAFcz b
  Join VAF2 a on a.VAF01 = b.VAF01A
  join VAE1 c with(nolock) on c.VAE01 = a.VAF06
  left join BBX1 d with(nolock) on d.BBX01 = b.VAF01B 
  Where b.BDA01 >= '1' And b.BDA01 <= '2' And b.Rownr = 1 and a.vaf32=1 and a.vaf01a=0 
  Union All
  Select a.VAF01, a.BDA01 ,a.VAF01A, a.Rownr, a.CBM01,a.VAF10,a.BCK01B ,a.BCK01C ,a.BCK01D ,a.BCE02A 
    ,a.BCE03A,a.VAF32 ,a.VAF58,a.VAF04,a.VAF18,a.VAF21,a.VAA01,a.VAF06,a.VAF07,a.BCK01A,a.VAF35
    ,a.VAF22,a.VAF36,c.BCK01C,c.ABC02,a.VAF14,a.BCE01A,a.BBX01,a.VAF31,d.BBX05,a.VAF01B
    ,a.VAF37,a.VAF38,a.VAF27,a.VAF28,a.VAF29,a.VAF61,case when @lOneDay = 2 then b.VAF62 else a.VAF62 end,a.VAF38
    ,a.VAF47,case when @lOneDay = 2 then @lEndDate else a.VAF50 end,a.BCE01D,a.BCE03D
    ,case when @lOneDay = 2 then b.BCE01E else a.BCE01E end,case when @lOneDay = 2 then b.BCE03E else a.BCE03E end
    ,a.BCE01F,a.BCE03F,a.VAF34,a.VAF75,c.BCK01D,a.VAF23,c.BCK01C,c.BCK01D,0,a.VAF15,a.DSK01,a.VAF26
  From @TmpVAFcz b
  Join VAF2 a on a.VAF01A = b.VAF01
  join VAE1 c with(nolock) on a.VAF06 = c.VAE01 
  left join BBX1 d with(nolock) on d.BBX01 = b.VAF01B 
  Where b.BDA01 >='8' and a.VAF01A>0 and a.VAF32<>1 and a.BDA01>'2'
  IF @@ERROR <> 0
  begin
    --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz
    IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA
    IF Object_id('tempdb..#TmpVAJls') IS NOT NULL DROP TABLE #TmpVAJls
    IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD
    IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI
      RAISERROR('从数据中提取医嘱明细信息错误.', 16, 1) with nowait
      RETURN 2
  end  
  update a set a.VAF01B = b.BBX01,a.VAJ57B = '.'+b.BBX05
  from @TmpVAFcz a
  join BBX1 b with(nolock) on b.BBX01 = a.BBX01
  where a.BDA01 = 'L' and b.BBX12 = 1 and b.BDA01 = 'L' and a.VAF01A = 0

  declare @Para53 varchar(10),@para209 varchar(20),@para262 varchar(20),@para414 varchar(20)
  declare @Para140 varchar(10),@gpara90 varchar(10),@para429 varchar(20),@gPara121 varchar(10),@cfxz202 tinyint
  set @para209 = dbo.GetSysParamValue(100,105001,209)
  set @para262 = dbo.GetSysParamValue(100,105003,262)
  set @Para53 = dbo.GetSysParamValue(9999,9999,53)
  set @para414 = dbo.GetSysParamValue(100,105001,414)
  set @Para140 = dbo.GetSysParamValue(100,105001,140)
  set @gpara90 = dbo.GetSysParamValue(9999,9999,90)
  set @para429 = dbo.GetSysParamValue(100,105001,429)
  set @gPara121 = dbo.GetSysParamValue(9999,9999,121)
  set @cfxz202=0
  if exists(select * from BLP1 with(nolock) where BLP02=202 and BLP05=1)
      set @cfxz202=1
  if @para414='0' or @para414=''
    set @para414 = ' 09:00:00'
  else
    set @para414 = ' ' +@para414 
  if @Para53 = '1'
  begin
    update a set a.BBX17 = Case when a.BDA01 >= '1' and a.BDA01 <= '3' then 1 else ISNULL(b.BBX17,1) end
    from @TmpVAFcz a
    join BBX1 b with(nolock) on b.BBX01 = a.BBX01
    
    update @TmpVAFcz set BBX17 = 1 where BDA01='0'
  end
  else
  begin
    update @TmpVAFcz set BBX17 = 1
  end
  if @gPara121 = '1'
    update @TmpVAFcz set BBX17 = 3 where BDA01 >= '1' and BDA01 <= '3'
  --请假离院病人医嘱不计费
  update a set a.BBX17 = 9
  from @TmpVAFcz a
  join VAE1 b with(nolock) on b.VAE01 = a.VAA07
  where b.ABV01 = '99' and @para209 = '1'   
  
  if @para140 = '1' and exists(select * from @TmpVAFcz 
        where (BDA01='0' or BDA01 > '4') and (BCK01B=bck01c_br or BCK01B=bck01d_br))--and VAF32 =0 
  begin    
    update a set a.VBI13 = 1
    from @TmpVAFcz a
    join bbx1 b with(nolock) on b.BBX01=a.BBX01
    where (a.BDA01='0' or a.BDA01 > '4')  and (a.BCK01B=a.bck01c_br or a.BCK01B=a.bck01d_br) --and a.VAF32 =0
      and not (a.BDA01='Z' AND b.BBX13 ='5')
  end   
  
  Create TABLE #tmpBDA (VAF01 int,VAF01A int,VAF36 datetime,VAF38 datetime,FDay int,IsFirst int,VAF28 tinyint
     ,VAF29 varchar(4),BDA01 varchar(2),VEH07 varchar(20),VEH26 tinyint, VEH27 tinyint)  
  Create Table #TmpVAJls (ID int IDENTITY(1,1),VAJ01 int,VAA01 int,VAA07 int,ROWNR int,VAF01 int,ACF01 tinyint
      ,BCK01A int,BCK01B int,BDN01 varchar(2),BBY01 int,VAJ23 int,VAJ24 numeric(18,4),VAJ25 numeric(18,4)
      ,VAJ26 tinyint,VAJ27 int,BCK01C int,BCE02B varchar(20),BCE03B varchar(20),BCK01D int,VAJ53 tinyint
      ,VAJ54 varchar(255),VAJ57 varchar(1024),BCK01E int,VAI01 int,CBM01 int,ABF01 varchar(8)
      ,ABC02 varchar(20),VAJ34 numeric(18,4),VAJ59 numeric(18,4),VAJ39 tinyint,VAJ35 varchar(20)
      ,VAJ32 numeric(18,4),vaj30 numeric(9,4),vaj31 numeric(9,4),vaj33 numeric(18,4),vaj36 numeric(18,2)
      ,vaj37 numeric(18,2),vaj38 numeric(18,2),vaj61 numeric(18,2),VAJ01A int,DSK01 int,FAB03 varchar(20),ABC01 tinyint,BCK18 tinyint,VAJ40 numeric(18,4)
      ,VAJ64 datetime,BCE01E int,BCE03E varchar(20),BCK01F int,BCQ04 varchar(20),BAG46 numeric(18,4),VAJ72 tinyint default(0)
      ,VEG04 varchar(64),BDA01 varchar(2),pcVAF27 int,pcBDA01 varchar(2),pcVAJ25 numeric(18,4),pcflag int, pcVAF32 int,pcVAF26 varchar(64),pcBBX01 int
	  ,VAJ05 int ,jzVAF27 int)
    
  Create Table #tmpVAI (ID int IDENTITY(1,1), VAI01 int, VAI04 varchar(20),VAA01 int,VAA07 int
      ,BCK01B int,BCE02 varchar(20),BCE03 varchar(20),CBM01 int,ACF01 tinyint,VAI22 numeric(18,4),VAI23 numeric(18,4),BCE01C int,BCE03C varchar(20),VAI16 int)

  DECLARE @TpVAJTkc table(UID int IDENTITY(1,1),VAJ01 int,VAA01 int,VAA07 int,ROWNR int,VAF01 int,ACF01 tinyint
      ,BCK01A int,BCK01B int,BDN01 varchar(2),BBY01 int,VAJ23 int,VAJ24 numeric(18,4),VAJ25 numeric(18,4)
      ,VAJ26 tinyint,VAJ27 int,BCK01C int,BCE02B varchar(20),BCE03B varchar(20),BCK01D int,VAJ53 tinyint
      ,VAJ54 varchar(255),VAJ57 varchar(1024),BCK01E int,VAI01 int,CBM01 int,ABF01 varchar(8)
      ,ABC02 varchar(20),VAJ34 numeric(18,4),VAJ59 numeric(18,4),VAJ39 tinyint,VAJ35 varchar(20)
      ,VAJ32 numeric(18,4),vaj30 numeric(9,4),vaj31 numeric(9,4),vaj33 numeric(18,4),vaj36 numeric(18,2)
      ,vaj37 numeric(18,2),vaj38 numeric(18,2),vaj61 numeric(18,2),VAJ01A int,DSK01 int,FAB03 varchar(20),ABC01 tinyint,BCK18 tinyint,VAJ40 numeric(18,4)
      ,VAJ64 datetime,BCE01E int,BCE03E varchar(20),BCK01F int,BCQ04 varchar(20),BAG46 numeric(18,4),VAJ72 tinyint default(0)
      ,VEG04 varchar(64),BDA01 varchar(2),pcVAF27 int,pcBDA01 varchar(2),pcVAJ25 numeric(18,4),pcflag int, pcVAF32 int,pcVAF26 varchar(64),pcBBX01 int
	  ,VAJ05 int ,jzVAF27 int)
      
  DECLARE @BCKPara table(BCK01 int,parav varchar(10))
  DECLARE @kbDPT table(UID int IDENTITY(1,1),BCK01 int,BBY01 int,DSK01 int,quantity NUMERIC(18,4),ExpiryDate datetime,LSQty NUMERIC(18,4),aflag tinyint)
  DECLARE @kbMDT table(UID int IDENTITY(1,1),BCK01 int,BBY01 int,DSK01 int,quantity NUMERIC(18,4),ExpiryDate datetime,LSQty NUMERIC(18,4),aflag tinyint)
  DECLARE @kbDPTkc table(UID int,BCK01 int,BBY01 int,DSK01 int,quantity NUMERIC(18,4),VAJ32 NUMERIC(18,6),BAG23 NUMERIC(18,6),BDN01 varchar(10))
    
  Declare @tmpDPT Table(uid int IDENTITY(1,1),BCK01 int,BBY01 int,DSK01 int,VAJ25 numeric(18,4)) 
  Declare @tmpMDT Table(uid int IDENTITY(1,1),BCK01 int,BBY01 int,DSK01 int,VAJ25 numeric(18,4))   
  declare @para241 varchar(64),@gParam124 varchar(2),@gParam126 varchar(2),@gParam127 varchar(2)    
  Declare @EndDate DateTime, @aDate Datetime
  --DECLARE @lVBDid int, @lVBDMax int  
  declare @minID int,@maxID int,@minIDkc int,@minIDb int,@maxIDb int
  declare @BCK01 int,@BBY01 int,@VAJ25 numeric(18,4),@BDN01 varchar(10),@DSK01 int
  select @para241=BLP04 from BLP1 with(nolock) where BLP02=241	
  set @para241=isnull(@para241,0)   
  set @gParam124 = dbo.GetSysParamValue(9999,9999,124)
  set @gParam126 = dbo.GetSysParamValue(9999,9999,126)
  set @gParam127 = dbo.GetSysParamValue(9999,9999,127)	

  Declare @lStop tinyint, @eVAF38 varchar(20),@BBY31 datetime,@curDate datetime,@curtime varchar(20)
  declare @aVAIid int, @aVAIMax int, @aStr varchar(20), @smsg varchar(2560)
  
  Set @aStr = REPLICATE('0',10)
  set @curDate = GETDATE()
  set @aDate=getdate()
  set @curtime = substring(CONVERT(char(23),getdate(),121),11,13)
  set @BBY31 = Convert(varchar(10),@curDate,121)+' 23:59:59.999' 
  
  Set @eVAF38 = Convert(varchar(10),@lEndDate,21)
  set @smsg = ''
  
  update a set a.VEH07=ISNULL(b.VEH07,'')
    ,a.VEH26 = case when isnull(b.VEH26,0)=0 then 1 else b.VEH26 end
    ,a.VEH27 = case when isnull(b.VEH27,0)=0 then 1 else b.VEH27 end  
  from @TmpVAFcz a
  join VEH2 b with(nolock) on b.VAF01=a.VAF01

  set datefirst 1
  --执行天数、及日期
  Insert Into #tmpBDA(VAF01,VAF01A,VAF36,VAF38,FDay,IsFirst,VAF28,VAF29,BDA01,VEH07,VEH26,VEH27)
  SELECT a.VAF01,a.VAF01A
     ,CASE WHEN a.VAF38 < a.VAF36 THEN a.VAF36 
  	          ELSE case when (a.VAF29 <> 'D')OR(a.VAF29 = 'D' and a.VAF28 <= 1) then CONVERT(varchar(10),a.VAF38+1,21)+@para414
  	                   when a.VAF29 = 'D' and a.VAF28 >= 2 then CONVERT(varchar(10),a.VAF38+a.VAF28,21)+@para414 end
  	        END AS VAF36
	    ,CASE WHEN DATEDIFF(DAY,a.VAF36,a.VAF37) >= 0 AND a.VAF37<=@lEndDate THEN a.VAF37  
  	          ELSE case when (a.VAF29 <> 'D')OR(a.VAF29 = 'D' and a.VAF28 <= 1) then @lEndDate 
  	                   when a.VAF29 = 'D' and a.VAF28 >= 2 
  	                    then CONVERT(varchar(10),a.VAF36+Floor((cast(DATEDIFF(day,a.VAF36,@eVAF38) as numeric(18,2))*cast(isnull(a.VAF28,1) as numeric(18,2)))/cast(isnull(a.VAF28,1) as numeric(18,2))),21)+' 23:59:59'  end
  	        END AS VAF38,1 AS FDay	
  	 ,CASE WHEN isnull(a.VAF38,'1899-12-30')<=a.VAF36 THEN 1
  	       ELSE 0 END AS IsFirst,a.VAF28,a.VAF29,a.BDA01,a.VEH07,a.VEH26,a.VEH27 
  FROM @TmpVAFcz a

	
  IF @lOneDay = 1
    UPDATE #tmpBDA SET FDay = 1
  ELSE 
  begin 
    if @lOneDay = 2
      update #tmpBDA set FDay = 1 ,IsFirst = 2
    else
    begin 	    
      if Exists(Select * from #tmpBDA where DateDiff(Day,VAF36,VAF38)<0)
      begin
       -- IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz
        IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA
        IF Object_id('tempdb..#TmpVAJls') IS NOT NULL DROP TABLE #TmpVAJls
        IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD
        IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI
        RAISERROR('存在医嘱发送的终止时间小于起始时间，请返回重新选择过滤条件，在发送医嘱！', 16, 1) with nowait      
        RETURN 3
      end 
      UPDATE #tmpBDA SET FDay = DATEDIFF(day,VAF36,VAF38)+1 where VAF29 <> 'D' or (VAF29 = 'D' and VAF28 <= 1)       
      UPDATE #tmpBDA SET FDay = Floor((DATEDIFF(day,VAF36,VAF38)+VAF28)/VAF28) where VAF29 = 'D' and VAF28 >= 2 
      UPDATE #tmpBDA SET VAF38 = CONVERT(varchar(10),DATEADD(day,(FDay-1)*VAF28,VAF36),21)+' 23:59:59'  where VAF29 = 'D' and VAF28 >= 2   
      --2017.7.1 增加每周几次按指定礼拜几执行
       update a set a.FDay=(select fday from dbo.GetBDI1_BDI14(a.VAF01,a.VEH07,a.VAF36,@lEndDate,0))
         ,a.VAF38=(select fVAF38 from dbo.GetBDI1_BDI14(a.VAF01,a.VEH07,a.VAF36,@lEndDate,0))
         ,a.VAF36=(select fVAF36 from dbo.GetBDI1_BDI14(a.VAF01,a.VEH07,a.VAF36,@lEndDate,0))
       from #tmpBDA a
       where a.VEH07>'' and a.VAF28>0 and a.VAF29='D'       
    end 
  end 
  UPDATE a set a.IsFirst = 0
  from #tmpBDA a
  join @TmpVAFcz b on b.VAF01 = a.VAF01
  where @para262 = '0' and b.BDA01 >= '4' and b.BDA01 <> '8' and b.VAF32 = 0 and a.IsFirst <> 2

  update a set a.VAF34=0
  from @TmpVAFcz a 
  join #tmpBDA b on b.VAF01=a.VAF01
  join VEH2 c with(nolock) on c.VAF01=a.VAF01
  where a.VAF75=1 and a.VAF34=1 and DATEDIFF(day,c.VEH09,b.VAF36)>=0



  --提取医嘱计价信息  
  Create Table #tmpVBD(uid int IDENTITY(1,1),VBD01 int 
      ,VAF01 int ,BBY01 int ,VBD04 numeric(18,4) ,VBD05 numeric(18,4) ,VBD06 tinyint,BCK01 int,VBD08 tinyint ,VBD09 tinyint
      ,BDN01 varchar(2),VAJ32 numeric(18,6),VAJ24 numeric(18,4),VAJ25 numeric(18,4),VAJ23 tinyint,VAJ59 numeric(18,6),VAJ34 numeric(18,4)
      ,VAJ35 varchar(20),VBD12 numeric(18,4),VAJ64 datetime,BBY01A int,jzbs tinyint default(0),VAJ72 tinyint default(0),DSK01 int
	  ,pcVAJ25 numeric(18,4),pcVAF27 int,pcBDA01 varchar(2),jzVAF27 int  )       
  --执行记录
  create table #tmpVBI(ID int IDENTITY(1,1),VBI01 int,VAF01 int,VBI05 int,VBI06 int,VBI07 int,VBI10 datetime,VBI11 datetime,BCK01 int,VAA01 int,VAA07 int,VAP01 int
     ,VBI13 tinyint)      
  --判断药品禁用 
  if exists(select a.VAF01
        from @TmpVAFcz a
        join VBD2 b with(nolock) on b.VAF01 = a.VAF01   and  isnull(b.DFlag,0)=0  
        join BBY1 c with(nolock) on c.BBY01 = b.BBY01
        join #tmpBDA d on d.VAF01 = a.VAF01
        where b.VBD04 >= 0 and c.BBY31 <= @BBY31 and d.IsFirst <> 2
          and a.BDA01 >= '1' and a.BDA01 <= '3' and a.VAF58 <> 1) 
  begin
    select @smsg =@smsg +char(13)+(d.VAE95+'：'+ replace(isnull(c.BBY05,'')+isnull(c.BBY06,''),'%','％')) 
                 from @TmpVAFcz a
                 join VBD2 b with(nolock) on b.VAF01 = a.VAF01  and  isnull(b.DFlag,0)=0  
                 join BBY1 c with(nolock) on c.BBY01 = b.BBY01
				 join VAE1 d with(nolock) on a.VAA07 = d.VAE01
				 join #tmpBDA e on e.VAF01 = a.VAF01
                 where b.VBD04 >= 0 and c.BBY31 <= @BBY31 and e.IsFirst <> 2
                   and a.BDA01 >= '1' and a.BDA01 <= '3' and a.VAF58 <> 1 
  --  IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz
    IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA
    IF Object_id('tempdb..#TmpVAJls') IS NOT NULL DROP TABLE #TmpVAJls
    IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD
    IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI
    set @smsg =@smsg +char(13)+'--以上药品已过有效期或已禁用，不能继续使用.'
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 4
  end
  
  select b.*,a.DSK01 into #tmpvbdex
  from @TmpVAFcz a
  join VBD2 b with(nolock) on b.VAF01 = a.VAF01  and  isnull(b.DFlag,0)=0   
  where b.VBD04 >= 0 and (@gpara90 <> '1' or (@gpara90='1' and a.BDA01 > '3' and a.VAF32 <> 1 ))
  
  insert into #tmpVBD(VBD01,VAF01,BBY01,VBD04,VBD05,VBD06,BCK01,VBD08,VBD09,BDN01,VAJ32,VAJ35,VBD12,BBY01A,DSK01,jzVAF27 ) 
  select distinct s.VBD01,s.VAF01,s.BBY01,s.VBD04,s.VBD05,s.VBD06,s.BCK01,s.VBD08,s.VBD09,s.BDN01,s.BBY25,s.BBY08,s.VBD12,s.BBY01A,s.DSK01,s.VAF27
  from (   
    select b.VBD01,b.VAF01,b.BBY01,b.VBD04,b.VBD05,b.VBD06,b.BCK01,b.VBD08,b.VBD09,c.BDN01,c.BBY25,isnull(d.BBY08,c.BBY08) BBY08 ,b.VBD12
      ,a.VAF58,a.BDA01,a.VAF01A,a.BCK01B,a.BCK01E,a.BCK01F,b.BBY01A,a.DSK01,a.VAF27
    from @TmpVAFcz a
    join #tmpvbdex b on b.VAF01 = a.VAF01    
    join BBY1 c with(nolock) on c.BBY01 = b.BBY01
	LEFT Join VEH2 d with(nolock) on d.VAF01 = a.VAF01
    where c.BBY31 > @BBY31 
	/*	
    --2017.7.20 当套餐明细中包含的收费项目仍为套餐，这一段会造成重复收费
	and isnull(c.BBY34,0)<>2
	union all
    select b.VBD01,b.VAF01,e.BBY01,b.VBD04*d.BCR04,b.VBD05,b.VBD06,b.BCK01,b.VBD08,b.VBD09,e.BDN01
      ,Case when d.BCR10 = 1 then e.BBY25 else d.BCR07 end BBY25 ,e.BBY08,b.VBD12
      ,a.VAF58,a.BDA01,a.VAF01A,a.BCK01B,a.BCK01E,a.BCK01F,b.BBY01A
    from @TmpVAFcz a
    join #tmpvbdex b on b.VAF01 = a.VAF01     
    join BBY1 c on c.BBY01 = b.BBY01
    join BCR1 d on d.BBY01A = b.BBY01 
    join BBY1 e with(nolock) on e.BBY01 = d.BBY01B
    where c.BBY31 > @BBY31 and c.bby34=2 and d.BCR09 > @BBY31 and e.BBY31 > @BBY31 
	*/
	 ) s
  where ((s.BDA01 >= '1' and s.BDA01 <= '3' and s.VAF58 <> 1) or(s.BDA01='0')
      or (s.BDA01 >= '4' and s.VAF58 <> 2
        and (@para429='1' or (@para429<>'1' and not exists(SELECT e.VAF01 
                   FROM @TmpVAFcz e
                   JOIN BAZ1 f with(nolock) ON e.BCK01B = f.BCK01
                   WHERE e.BDA01 = 'S' And e.VAF01A = 0 And f.BAU01 = '03' AND f.ACF01 IN (2,3)
                       and (s.VAF01 = e.VAF01 or s.VAF01A = e.VAF01) )) )
        and (not (exists(select a1.VAF01
              from #tmpvbdex a1 
                join BBY1 b1 with(nolock) on a1.BBY01=b1.BBY01 
                join AAG1 c1 with(nolock) on b1.BCF01=c1.AAG01
                join @TmpVAFcz d1 on d1.VAF01 = a1.VAF01
              where d1.BDA01 = 'N' and b1.BDN01='N' and c1.AAG01<=7 and d1.VAF01=s.VAF01 and d1.VAP01 = 0) 
             and exists(select * from BEO1 f1 with(nolock) 
                        where (f1.BCK01 = s.BCK01B or (f1.BCK01 = s.BCK01F and s.BCK01B = s.BCK01E)) and f1.BEO04 = -2 and DateDiff(day,f1.BEO06,@curDate)>= 0)))               
                        ) )    
  order by s.VAF01 
  
  Declare @param260 int
  set @param260=0
  if exists(select * from BLP1 with(nolock) where BLP02=260 and BLP05=1)  
  set @param260=1 
                                                                  
  select a.vaa07,a.vaf01,a.bby01,a.vaj46,a.vaj64,a.ACF01,a.BDN01,isnull(f.VEG14,0) jzVEG14
  into #kbmVAJpg
  from VAJ2 a with(nolock)
  join VEG2 f with(nolock) on f.VAJ01=a.VAJ01
  join (select distinct b.VAA07,c.BBY01 
        from @TmpVAFcz b 
		join #tmpVBD c on c.VAF01 = b.VAF01 
        where  b.bda01 > '3'  and c.VBD09 in (2,7)) d on d.VAA07 = a.VAA07 and d.BBY01 = a.BBY01  --b.VAF32 = 1 and b.BDA01 = 'T'
  where a.ACF01 = 2 and a.VAJ05<3 and ((a.VAK01 = 0 and @param260=0) or (@param260=1) )

  select convert(varchar(10),a.VAJ64,120) fvaj64,a.bby01,a.VAA07,sum(a.jzVEG14) sVEG14
  into #tpVEGs
  from #kbmVAJpg a
  where exists(select c.vbd09 from @TmpVAFcz b 
		join #tmpVBD c on c.VAF01 = b.VAF01 
        where  b.bda01 > '3'  and c.VBD09 =7 and b.VAA07 = a.VAA07 and c.BBY01 = a.BBY01)
  group by convert(varchar(10),a.VAJ64,120),a.bby01,a.VAA07

  update a set A.jzVEG14= b.sVEG14
  from #kbmVAJpg a
  join #tpVEGs b on DateDiff(Day,a.VAJ64,b.fvaj64) = 0 and a.VAA07=b.VAA07 and a.BBY01=b.BBY01
 
  select a.BBY01,MAX(b.VAF27) sVAF27,0 VAF01
  into #kbmBDU10
  from #tmpVBD a
  join @TmpVAFcz b on b.VAF01 = a.VAF01
  where b.BDA01 = 'T' and b.VAF32 = 1 and a.VBD09 = 1
  group by a.BBY01      
  
  update m set m.VAF01 = n.svaf01
  from #kbmBDU10 m
  join (
    select a.BBY01,MIN(b.VAF01) svaf01
    from #tmpVBD a
    join #kbmBDU10 c on c.BBY01 = a.BBY01
    join @TmpVAFcz b on b.VAF01 = a.VAF01 and b.VAF27 = c.sVAF27
    where b.BDA01 = 'T' and b.VAF32 = 1 and a.VBD09 = 1 
    group by a.BBY01) n on n.BBY01 = m.BBY01
  
  delete a
  from #tmpVBD a
  join @TmpVAFcz b on b.VAF01 = a.VAF01
  join #kbmBDU10 c on c.bby01 = a.bby01
  where b.BDA01 = 'T' and b.VAF32 = 1 and a.VBD09 = 1
    and not exists(select * from #kbmBDU10 d where d.VAF01 = a.VAF01)

   
  declare @mDay int,@anum int   
  select a.VBD01,a.VAF01,b.BBX01,a.BBY01,b.bda01,a.bdn01,b.vaf32,a.VBD09,b.VAA07
    ,VAJ64 = case when c.IsFirst = 2 then c.VAF36 else case when DateDiff(Day,c.VAF36,c.VAF38) > 0 
              then  Convert(varchar(10),c.VAF38,21)+@curtime else c.VAF36 end end,0 jzbs
	,Case when b.BDA01='T' and b.VAF32=1 then (case when c.IsFirst=0 then b.VAF27 when c.IsFirst=1 then b.VAF61 else b.VAF62 end )
	     else (case when b.VAF27>0 then b.VAF27 else 1 end ) end jzVAF27 
  into #kbmjzbs               
  from #tmpVBD a
  join @TmpVAFcz b on b.VAF01 = a.VAF01
  join #tmpBDA c on c.VAF01 = b.VAF01
  join BAG1 e with(nolock) on e.BBY01 = a.BBY01
  where (b.BDA01 >= '4' and b.VAF32 = 1 and a.BDN01 <= '3')
    and ((c.IsFirst = 2 and b.VAF62 > 0) or (c.IsFirst <= 1 and c.FDay <= 1))    
  union all
  select a.VBD01,a.VAF01,b.BBX01,a.BBY01,b.bda01,a.bdn01,b.vaf32,a.VBD09,b.VAA07
    ,VAJ64 = case when c.IsFirst = 2 then c.VAF36 else case when DateDiff(Day,c.VAF36,c.VAF38) > 0 
        then  Convert(varchar(10),c.VAF38,21)+@curtime else c.VAF36 end end ,0 jzbs
	,Case when b.BDA01='T' and b.VAF32=1 then (case when c.IsFirst=0 then b.VAF27 when c.IsFirst=1 then b.VAF61 else b.VAF62 end )
	     else (case when b.VAF27>0 then b.VAF27 else 1 end ) end jzVAF27 
  from #tmpVBD a
  join @TmpVAFcz b on b.VAF01 = a.VAF01
  join #tmpBDA c on c.VAF01 = b.VAF01  
  where (b.BDA01 >= '4' and b.VAF32 = 1 and a.BDN01 >= '4')
    and ((c.IsFirst = 2 and b.VAF62 > 0) or (c.IsFirst <= 1 and c.FDay <= 1))   
    
  set @mDay = ISNULL((select MAX(b.Fday) from @TmpVAFcz a
                        join #tmpBDA b on b.VAF01 = a.VAF01
                       where b.IsFirst <= 1 and b.FDay >= 2),0)
  if @mDay >= 2
  begin
    set @anum = 1
    while @anum <= @mDay
    begin    
      insert into #kbmjzbs(VBD01,VAF01,BBX01,BBY01,bda01,bdn01,vaf32,vbd09,VAJ64,jzbs,VAA07,jzVAF27 )    
      select  a.VBD01,a.VAF01,b.BBX01,a.BBY01,b.bda01,a.bdn01,b.vaf32,a.VBD09
        ,VAJ64 = case when @anum > 1 then (case when b.VAF29 <> 'D' OR (b.VAF29 = 'D' and b.VAF28 <= 1 and c.VEH07<='') then Convert(varchar(10),(c.VAF36 + (@anum - 1)),21)+@curtime
                                           else case when c.VEH07>'' then (select s1.fvaf36 from dbo.GetBDI1_BDI14(c.VAF01,c.VEH07,c.VAF36,c.VAF38,1) s1 where s1.fday=@anum )
                                                     else Convert(varchar(10),(c.VAF36 + (@anum - 1)* b.VAF28),21)+@curtime end end )
                      else c.VAF36 end,0,b.VAA07,a.jzVAF27        
      from #tmpVBD a
      join @TmpVAFcz b on b.VAF01 = a.VAF01
      join #tmpBDA c on c.VAF01 = b.VAF01
      join BAG1 e with(nolock) on e.BBY01 = a.BBY01
      where (b.BDA01 >= '4' and b.VAF32 = 1 and a.BDN01 <= '3') and (c.IsFirst <= 1 and c.FDay >= 2 and c.FDay >= @anum)     
      union all
      select  a.VBD01,a.VAF01,b.BBX01,a.BBY01,b.bda01,a.bdn01,b.vaf32,a.VBD09
        ,VAJ64 = case when @anum > 1 then (case when b.VAF29 <> 'D' OR (b.VAF29 = 'D' and b.VAF28 <= 1 and c.VEH07<='') then Convert(varchar(10),(c.VAF36 + (@anum - 1)),21)+@curtime
                                           else case when c.VEH07>'' then (select s1.fvaf36 from dbo.GetBDI1_BDI14(c.VAF01,c.VEH07,c.VAF36,c.VAF38,1) s1 where s1.fday=@anum )
                                                     else Convert(varchar(10),(c.VAF36 + (@anum - 1)* b.VAF28),21)+@curtime end end )
                      else c.VAF36 end,0,b.VAA07,a.jzVAF27    
      from #tmpVBD a
      join @TmpVAFcz b on b.VAF01 = a.VAF01
      join #tmpBDA c on c.VAF01 = b.VAF01  
      where (b.BDA01 >= '4' and b.VAF32 = 1 and a.BDN01 >= '4')
        and (c.IsFirst <= 1 and c.FDay >= 2 and c.FDay >= @anum)
                    
      set @anum = @anum + 1
    end
  end 
  
  update a set a.jzbs = 9
  from #kbmjzbs a
  where  a.bda01 > '3'  and  a.VBD09 = 2  
    and Exists(select * from #kbmVAJpg d where d.VAA07 = a.VAA07 and d.BBY01 = a.BBY01 and DateDiff(Day,a.VAJ64,d.VAJ64) = 0) 
     --a.VAF32 = 1 and a.BDA01 = 'T' and
  select CONVERT(varchar(10),a.vaj64,121) vaj64,a.VAA07,a.bby01,MIN(a.vaf01) svaf01
  into #kbmvafbs
  from #kbmjzbs a
  where a.bda01 > '3'  and  a.VBD09 = 2 and a.jzbs = 0   
  group by CONVERT(varchar(10),a.vaj64,121),a.VAA07,a.bby01 
  
  update a set a.jzbs = 1,a.jzVAF27=1
  from #kbmjzbs a
  where a.bda01 > '3'  and  a.VBD09 = 2 and a.jzbs = 0 
    and Exists(select * from #kbmvafbs d where d.VAA07 = a.VAA07 and d.BBY01 = a.BBY01
       and DateDiff(Day,a.VAJ64,d.VAJ64) = 0 and d.svaf01 = a.VAF01) 
  
  update #kbmjzbs set jzbs=9 where jzbs <> 1 and vbd09 = 2  

  update a set a.jzbs=9
  from #kbmjzbs a
  where a.VBD09 = 7 and a.bda01 > '3' 
    and Exists(select * from #kbmVAJpg d where d.VAA07 = a.VAA07 and d.BBY01 = a.BBY01 and DateDiff(Day,a.VAJ64,d.VAJ64) = 0 and a.jzVAF27<=d.jzVEG14) 
    
  select CONVERT(varchar(10),a.vaj64,121) vaj64,a.VAA07,a.bby01,MAX(a.jzVAF27) mVAF27 
  into #kbmvbdbs
  from #kbmjzbs a
  where a.bda01 > '3'  and  a.VBD09 = 7 and a.jzbs = 0   
  group by CONVERT(varchar(10),a.vaj64,121),a.VAA07,a.bby01 

  select a.vaa07,a.VAJ64,a.BBY01,max(a.VAF01) mvaf01 
  into #kbjzVAF
  from #kbmvbdbs d 
  join #kbmjzbs a on d.VAA07 = a.VAA07 and d.BBY01 = a.BBY01
       and DateDiff(Day,a.VAJ64,d.VAJ64) = 0 and d.mVAF27 = a.jzVAF27 
  where a.bda01 > '3'  and a.VBD09=7 and a.jzbs=0
  group by a.vaa07,a.VAJ64,a.BBY01

  update a set a.jzbs = 1
  from #kbmjzbs a
  where a.bda01 > '3'  and  a.VBD09 = 7 and a.jzbs = 0 
    and Exists(select * from #kbjzVAF d where d.VAA07 = a.VAA07 and d.BBY01 = a.BBY01
       and DateDiff(Day,a.VAJ64,d.VAJ64) = 0 and d.mVAF01 = a.vaf01) 

  update #kbmjzbs set jzbs=9 where jzbs <> 1 and vbd09 = 7
  
  update a set a.jzVAF27= case when a.jzVAF27>d.jzVEG14 then a.jzVAF27-d.jzVEG14 else 0 end
  from #kbmjzbs a
  join #kbmVAJpg d on d.VAA07 = a.VAA07 and d.BBY01 = a.BBY01 and DateDiff(Day,a.VAJ64,d.VAJ64) = 0 and a.jzVAF27>d.jzVEG14
  where a.VBD09 = 7 and a.bda01 > '3' and a.jzbs=1

  update a set a.jzbs = 1
  from #kbmjzbs a
  where a.vbd09 = 3 and a.VAF32 = 1 and a.BDA01 = 'T' 
    and Exists(select * from #kbmjzbs d where d.VAA07 = a.VAA07 and d.bbx01 = a.bbx01 and d.vbd09 in (2,7) and d.jzbs = 9
       and DateDiff(Day,a.VAJ64,d.VAJ64) = 0 and d.vaf01 = a.VAF01) 
  update a set a.jzbs = 9,a.jzVAF27=a.jzVAF27-1
  from #kbmjzbs a
  where a.vbd09 = 3 and a.VAF32 = 1 and a.BDA01 = 'T' 
    and Exists(select * from #kbmjzbs d where d.VAA07 = a.VAA07 and d.bbx01 = a.bbx01 and d.vbd09 =2 and d.jzbs = 1
       and DateDiff(Day,a.VAJ64,d.VAJ64) = 0 and d.vaf01 = a.VAF01)  

  update a set a.jzbs = 9,a.jzVAF27=a.jzVAF27-(select top 1 d1.jzVAF27 from #kbmjzbs d1 where d1.VAA07 = a.VAA07 and d1.bbx01 = a.bbx01 and d1.vbd09 =7 and d1.jzbs = 1
       and DateDiff(Day,a.VAJ64,d1.VAJ64) = 0 and d1.vaf01 = a.VAF01 order by d1.jzVAF27 desc)  
  from #kbmjzbs a
  where a.vbd09 = 3 and a.VAF32 = 1 and a.BDA01 = 'T' 
    and Exists(select * from #kbmjzbs d where d.VAA07 = a.VAA07 and d.bbx01 = a.bbx01 and d.vbd09 =7 and d.jzbs = 1
       and DateDiff(Day,a.VAJ64,d.VAJ64) = 0 and d.vaf01 = a.VAF01)  

  update #kbmjzbs set jzbs=1 where jzbs <> 9 and vbd09 = 3     
  update #kbmjzbs set jzVAF27=0 where jzVAF27<0      
  if OBJECT_ID('tempdb..#kbmVAJpg') is not null drop table #kbmVAJpg                                        
  if OBJECT_ID('tempdb..#kbmBDU10') is not null drop table #kbmBDU10 
  --
  update a set a.VAF61 = b.VAF61 from  @TmpVAFcz a join @TmpVAFcz b on a.VAF01A= b.VAF01 where a.VAF01A>0  and b.BDA01 = '8'
  
  select * into #kbmVBD from #tmpVBD
  truncate table #tmpVBD
 
    update a set a.BAG56 = isnull(e.BQF03,0)
    from @TmpVAFcz a 
	join #kbmVBD f on f.VAF01 = a.VAF01 
    join BQG1 d with(nolock) on d.BBY01=f.BBY01
    join BQF1 e with(nolock) on e.BQF01=d.BQF01 
    where a.BDA01 >='1' and a.BDA01 <= '2' and  e.BQF01=6 and d.BQG04=1 

 declare @lBLP262 tinyint
 select @lBLP262= isnull(BLP05,0) from BLP1 with(nolock) where BLP02 = 262
 set @lBLP262=isnull(@lBLP262,0)

  insert into #tmpVBD(VBD01,VAF01,BBY01,VBD04,VBD05,VBD06,BCK01,VBD08,VBD09,BDN01,VAJ32,VAJ35,VBD12,VAJ25,VAJ23,VAJ34,VAJ59,VAJ64,bby01a,VAJ72,jzVAF27 )    
  select distinct a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12      
    ,VAJ25 = case when @lBLP262=0  then
	           (case when a.BDN01 <= '2' then
                       (  CASE WHEN isnull(b.BAG56,0)=1 then
					       (CASE WHEN c.IsFirst = 0 THEN case when b.VAF75=1 then dbo.GetClinicGetValueByBAG56(cast(dbo.GetClinicGetValueByBAG56(a.VBD04* c.FDay * b.VAF27)*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
					              else dbo.GetClinicGetValueByBAG56(cast(a.VBD04*isnull(f.VEH24,e.BAG07)* c.FDay * b.VAF27 AS NUMERIC(18,4))) end	                    
                            WHEN c.IsFirst = 1 then case when b.VAF75=1 THEN dbo.GetClinicGetValueByBAG56(cast(dbo.GetClinicGetValueByBAG56(a.VBD04*(b.VAF61+(c.FDay-1)*b.VAF27))*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
							      else dbo.GetClinicGetValueByBAG56(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*(b.VAF61+(c.FDay-1)*b.VAF27) AS NUMERIC(18,4))) end                   
                            WHEN c.IsFirst = 2 then case when b.VAF75=1 THEN  dbo.GetClinicGetValueByBAG56(cast(dbo.GetClinicGetValueByBAG56(a.VBD04*b.VAF62)*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
							    else dbo.GetClinicGetValueByBAG56(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*b.VAF62  AS NUMERIC(18,4))) end END)
					   else  
					       (CASE WHEN c.IsFirst = 0 THEN case when b.VAF75=1 then CEILING(cast(CEILING(a.VBD04* c.FDay * b.VAF27)*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
					              else CEILING(cast(a.VBD04*isnull(f.VEH24,e.BAG07)* c.FDay * b.VAF27 AS NUMERIC(18,4))) end	                    
                            WHEN c.IsFirst = 1 then case when b.VAF75=1 THEN CEILING(cast(CEILING(a.VBD04*(b.VAF61+(c.FDay-1)*b.VAF27))*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
							      else CEILING(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*(b.VAF61+(c.FDay-1)*b.VAF27) AS NUMERIC(18,4))) end                   
                            WHEN c.IsFirst = 2 then case when b.VAF75=1 THEN  CEILING(cast(CEILING(a.VBD04*b.VAF62)*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
							    else CEILING(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*b.VAF62  AS NUMERIC(18,4))) end END) end )
				   when a.BDN01 = '3' then
                       ( CASE WHEN c.IsFirst = 0 THEN case when b.VAF75=1 then CEILING(cast(CEILING(a.VBD04*c.FDay * b.VAF27)*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
					              else CEILING(cast(a.VBD04*isnull(f.VEH24,e.BAG07)* c.FDay * b.VAF27 AS NUMERIC(18,4))) end	                    
                            WHEN c.IsFirst = 1 then case when b.VAF75=1 THEN CEILING(cast(CEILING(a.VBD04*(b.VAF61+(c.FDay-1)*b.VAF27))*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
							      else CEILING(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*(b.VAF61+(c.FDay-1)*b.VAF27) AS NUMERIC(18,4))) end                   
                            WHEN c.IsFirst = 2 then case when b.VAF75=1 THEN  CEILING(cast(CEILING(b.VAF62)*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
							    else CEILING(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*b.VAF62  AS NUMERIC(18,4))) end END  )				
                   else ( CASE WHEN c.IsFirst = 0 THEN a.VBD04*isnull(f.VEH24,e.BAG07)*c.FDay	                    
                            WHEN c.IsFirst = 1 THEN a.VBD04*isnull(f.VEH24,e.BAG07)*c.FDay                   
                            WHEN c.IsFirst = 2 THEN a.VBD04*isnull(f.VEH24,e.BAG07)*c.FDay END )   end)        
			else (    case when f.VEH27=1 then 
								(case when a.BDN01 <= '2' then
									   (CASE WHEN isnull(b.BAG56,0)=1 then  
									       (CASE WHEN c.IsFirst = 0 THEN case when b.VAF75=1 then dbo.GetClinicGetValueByBAG56(cast(dbo.GetClinicGetValueByBAG56(a.VBD04* c.FDay * b.VAF27)*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
												  else dbo.GetClinicGetValueByBAG56(cast(a.VBD04*isnull(f.VEH24,e.BAG07)* c.FDay * b.VAF27 AS NUMERIC(18,4))) end	                    
											WHEN c.IsFirst = 1 then case when b.VAF75=1 THEN dbo.GetClinicGetValueByBAG56(cast(dbo.GetClinicGetValueByBAG56(a.VBD04*(b.VAF61+(c.FDay-1)*b.VAF27))*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
												  else dbo.GetClinicGetValueByBAG56(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*(b.VAF61+(c.FDay-1)*b.VAF27) AS NUMERIC(18,4))) end                   
											WHEN c.IsFirst = 2 then case when b.VAF75=1 THEN  dbo.GetClinicGetValueByBAG56(cast(dbo.GetClinicGetValueByBAG56(a.VBD04*b.VAF62)*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
												else dbo.GetClinicGetValueByBAG56(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*b.VAF62  AS NUMERIC(18,4))) end END)
										else (CASE WHEN c.IsFirst = 0 THEN case when b.VAF75=1 then dbo.GetClinicGetValueByBAG56(cast(dbo.GetClinicGetValueByBAG56(a.VBD04* c.FDay * b.VAF27)*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
												  else dbo.GetClinicGetValueByBAG56(cast(a.VBD04*isnull(f.VEH24,e.BAG07)* c.FDay * b.VAF27 AS NUMERIC(18,4))) end	                    
											WHEN c.IsFirst = 1 then case when b.VAF75=1 THEN dbo.GetClinicGetValueByBAG56(cast(dbo.GetClinicGetValueByBAG56(a.VBD04*(b.VAF61+(c.FDay-1)*b.VAF27))*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
												  else dbo.GetClinicGetValueByBAG56(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*(b.VAF61+(c.FDay-1)*b.VAF27) AS NUMERIC(18,4))) end                   
											WHEN c.IsFirst = 2 then case when b.VAF75=1 THEN  dbo.GetClinicGetValueByBAG56(cast(dbo.GetClinicGetValueByBAG56(a.VBD04*b.VAF62)*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
												else dbo.GetClinicGetValueByBAG56(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*b.VAF62  AS NUMERIC(18,4))) end END) end	 )
								   when a.BDN01 = '3' then
									   ( CASE WHEN c.IsFirst = 0 THEN case when b.VAF75=1 then CEILING(cast(CEILING(a.VBD04*c.FDay * b.VAF27)*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
												  else CEILING(cast(a.VBD04*isnull(f.VEH24,e.BAG07)* c.FDay * b.VAF27 AS NUMERIC(18,4))) end	                    
											WHEN c.IsFirst = 1 then case when b.VAF75=1 THEN CEILING(cast(CEILING(a.VBD04*(b.VAF61+(c.FDay-1)*b.VAF27))*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
												  else CEILING(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*(b.VAF61+(c.FDay-1)*b.VAF27) AS NUMERIC(18,4))) end                   
											WHEN c.IsFirst = 2 then case when b.VAF75=1 THEN  CEILING(cast(CEILING(b.VAF62)*isnull(f.VEH24,e.BAG07) AS NUMERIC(18,4)))
												else CEILING(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*b.VAF62  AS NUMERIC(18,4))) end END  )				
								   else ( CASE WHEN c.IsFirst = 0 THEN a.VBD04*isnull(f.VEH24,e.BAG07)*c.FDay	                    
											WHEN c.IsFirst = 1 THEN a.VBD04*isnull(f.VEH24,e.BAG07)*c.FDay                   
											WHEN c.IsFirst = 2 THEN a.VBD04*isnull(f.VEH24,e.BAG07)*c.FDay END )   end) 

						 else (	 case when a.BDN01 <= '2' then
								   ( CASE WHEN c.IsFirst = 0 THEN case when b.VAF75=1 then CEILING(cast(CEILING(a.VBD04* c.FDay * b.VAF27) AS NUMERIC(18,4)))
																  else CEILING(cast(a.VBD04* c.FDay * b.VAF27 AS NUMERIC(18,4))) end	                    
															WHEN c.IsFirst = 1 then case when b.VAF75=1 THEN CEILING(cast(CEILING(a.VBD04*(b.VAF61+(c.FDay-1)*b.VAF27)) AS NUMERIC(18,4)))
																  else CEILING(cast(a.VBD04*(b.VAF61+(c.FDay-1)*b.VAF27) AS NUMERIC(18,4))) end                   
															WHEN c.IsFirst = 2 then case when b.VAF75=1 THEN  CEILING(cast(CEILING(a.VBD04*b.VAF62) AS NUMERIC(18,4)))
																else CEILING(cast(a.VBD04*b.VAF62  AS NUMERIC(18,4))) end END  )
												   when a.BDN01 = '3' then
													   ( CASE WHEN c.IsFirst = 0 THEN case when b.VAF75=1 then CEILING(cast(CEILING(a.VBD04*c.FDay * b.VAF27) AS NUMERIC(18,4)))
																  else CEILING(cast(a.VBD04* c.FDay * b.VAF27 AS NUMERIC(18,4))) end	                    
															WHEN c.IsFirst = 1 then case when b.VAF75=1 THEN CEILING(cast(CEILING(a.VBD04*(b.VAF61+(c.FDay-1)*b.VAF27)) AS NUMERIC(18,4)))
																  else CEILING(cast(a.VBD04*(b.VAF61+(c.FDay-1)*b.VAF27) AS NUMERIC(18,4))) end                   
															WHEN c.IsFirst = 2 then case when b.VAF75=1 THEN  CEILING(cast(CEILING(b.VAF62) AS NUMERIC(18,4)))
																else CEILING(cast(a.VBD04*b.VAF62  AS NUMERIC(18,4))) end END  )				
												   else ( CASE WHEN c.IsFirst = 0 THEN a.VBD04*c.FDay	                    
															WHEN c.IsFirst = 1 THEN a.VBD04*c.FDay                   
															WHEN c.IsFirst = 2 THEN a.VBD04*c.FDay END )   end	)	end )	 end			                          
    ,VAJ23 = case when a.BDN01 = '3'  
                  then (CASE WHEN c.IsFirst = 0 THEN c.FDay*b.VAF21	                    
                                 WHEN c.IsFirst = 1 THEN (c.FDay)*b.VAF61                    
                                 WHEN c.IsFirst = 2 THEN b.VAF21*c.FDay
                               END) 
                 else 1 end   ,VAJ34 = isnull(f.VEH24,e.BAG07),0
    ,VAJ64 = case when c.IsFirst = 2 then c.VAF36 else case when DateDiff(Day,c.VAF36,c.VAF38) > 0 then  Convert(varchar(10),c.VAF38,21)+@curtime else c.VAF36 end end
    ,a.BBY01A,VAJ72 = case when c.IsFirst = 2 then 2 when c.IsFirst=1 then 1 else 0 end
	,CASE WHEN c.IsFirst = 0 THEN c.FDay * b.VAF27	                    
			WHEN c.IsFirst = 1 then b.VAF61+(c.FDay-1)*b.VAF27
			WHEN c.IsFirst = 2 then b.VAF62 END jzVAF27
  from #kbmVBD a
  join @TmpVAFcz b on b.VAF01 = a.VAF01
  join #tmpBDA c on c.VAF01 = b.VAF01
  join BAG1 e with(nolock) on e.BBY01 = a.BBY01
  LEFT join VEH2 f with(nolock) on f.VAF01 = a.VAF01
  where (b.BDA01 >= '1' and b.BDA01 <= '3' and a.BDN01 <= '3') 
    and ((c.IsFirst = 2 and b.VAF62 > 0) or (c.IsFirst <= 1 and ((c.FDay <= 1 and b.VAF75<>1)or(b.VAF75=1)))) 
  union all
  select s.VBD01,s.VAF01,s.BBY01,s.VBD04,s.VBD05,s.VBD06,s.BCK01,s.VBD08,s.VBD09,s.BDN01,s.VAJ32,s.VAJ35,s.VBD12  
      ,VAJ25 = case when s.BDN01 <= '2' then
                       (CASE WHEN isnull(s.BAG56,0)=1 then    
					       (CASE WHEN s.IsFirst = 0 THEN
                           case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.VAJ34* s.FDay AS NUMERIC(18,4)))
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then 
									dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.VAJ34* s.FDay*(select top 1 t.jzVAF27 from #kbmjzbs t 
									                               where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                                           and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then 
									dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.VAJ34* s.FDay*(select top 1 t.jzVAF27 from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9  order by t.jzVAF27 desc) AS NUMERIC(18,4))) 
                              else dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.VAJ34* s.FDay * s.VAF27 AS NUMERIC(18,4))) end                    
                            WHEN s.IsFirst = 1 THEN
                              case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.VAJ34 AS NUMERIC(18,4)))
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then 
									dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.VAJ34* (select top 1 t.jzVAF27 from #kbmjzbs t 
									                               where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                                           and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then 
								   dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.VAJ34*(select top 1 t.jzVAF27 from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc) AS NUMERIC(18,4))) 
                              else dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.VAJ34*s.VAF61 AS NUMERIC(18,4)))  end 
                            WHEN s.IsFirst = 2 THEN 
                              case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.VAJ34 AS NUMERIC(18,4)))
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then 
									dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.VAJ34* (select top 1 t.jzVAF27 from #kbmjzbs t 
									                               where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                                           and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then 
									dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.VAJ34*(select top 1 t.jzVAF27 from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc) AS NUMERIC(18,4))) 
                              else dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.VAJ34*s.VAF62 AS NUMERIC(18,4)))  end  END)
						else  
						 (CASE WHEN s.IsFirst = 0 THEN
                           case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then CEILING(cast(s.VBD04*s.VAJ34* s.FDay AS NUMERIC(18,4)))
								when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then CEILING(cast(s.VBD04*s.VAJ34*s.FDay* (select top 1 t.jzVAF27 from #kbmjzbs t 
									                               where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                                           and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then 
									CEILING(cast(s.VBD04*s.VAJ34* s.FDay*(select top 1 t.jzVAF27 from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc) AS NUMERIC(18,4))) 
                              else CEILING(cast(s.VBD04*s.VAJ34* s.FDay * s.VAF27 AS NUMERIC(18,4))) end                    
                            WHEN s.IsFirst = 1 THEN
                              case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then CEILING(cast(s.VBD04*s.VAJ34 AS NUMERIC(18,4)))
								when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then CEILING(cast(s.VBD04*s.VAJ34* (select top 1 t.jzVAF27 from #kbmjzbs t 
									                               where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                                           and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then 
									CEILING(cast(s.VBD04*s.VAJ34*(select top 1 t.jzVAF27 from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc) AS NUMERIC(18,4))) 
                              else CEILING(cast(s.VBD04*s.VAJ34*s.VAF61 AS NUMERIC(18,4)))  end 
                            WHEN s.IsFirst = 2 THEN 
                              case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then CEILING(cast(s.VBD04*s.VAJ34 AS NUMERIC(18,4)))
								when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then CEILING(cast(s.VBD04*s.VAJ34* (select top 1 t.jzVAF27 from #kbmjzbs t 
									                               where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                                           and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then 
									CEILING(cast(s.VBD04*s.VAJ34*(select top 1 t.jzVAF27 from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc) AS NUMERIC(18,4))) 
                              else CEILING(cast(s.VBD04*s.VAJ34*s.VAF62 AS NUMERIC(18,4)))  end  END) end   ) 
				 when s.BDN01 = '3' then
                       ( CASE WHEN s.IsFirst = 0 THEN
                           case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then CEILING(cast(s.VBD04*s.VAJ34* s.FDay AS NUMERIC(18,4)))
							   when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then CEILING(cast(s.VBD04*s.VAJ34*s.FDay* (select top 1 t.jzVAF27 from #kbmjzbs t 
									                               where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                                           and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then 
									CEILING(cast(s.VBD04*s.VAJ34* s.FDay*(select top 1 t.jzVAF27 from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc) AS NUMERIC(18,4))) 
                              else CEILING(cast(s.VBD04*s.VAJ34* s.FDay * s.VAF27 AS NUMERIC(18,4))) end                    
                            WHEN s.IsFirst = 1 THEN
                              case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then CEILING(cast(s.VBD04*s.VAJ34 AS NUMERIC(18,4)))
								when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then CEILING(cast(s.VBD04*s.VAJ34* (select top 1 t.jzVAF27 from #kbmjzbs t 
									                               where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                                           and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then 
									CEILING(cast(s.VBD04*s.VAJ34*(select top 1 t.jzVAF27 from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc) AS NUMERIC(18,4))) 
                              else CEILING(cast(s.VAJ34*s.VAF61 AS NUMERIC(18,4)))  end 
                            WHEN s.IsFirst = 2 THEN 
                              case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then CEILING(cast(s.VAJ34 AS NUMERIC(18,4)))
								when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then CEILING(cast(s.VBD04*s.VAJ34* (select top 1 t.jzVAF27 from #kbmjzbs t 
									                               where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                                           and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then
									 CEILING(cast(s.VBD04*s.VAJ34*(select top 1 t.jzVAF27 from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc) AS NUMERIC(18,4))) 
                              else CEILING(cast(s.VAJ34*s.VAF62 AS NUMERIC(18,4)))  end  END  ) 				
                  else ( CASE WHEN s.IsFirst = 0 THEN s.VBD04*s.VAJ34*s.FDay	                    
                            WHEN s.IsFirst = 1 THEN s.VBD04*s.VAJ34*s.FDay                   
                            WHEN s.IsFirst = 2 THEN s.VBD04*s.VAJ34*s.FDay END )   end                                  
      ,VAJ23 = case when s.BDN01 = '3'  
                  then (CASE WHEN s.IsFirst = 0 THEN s.FDay*s.VAF21	                    
                                 WHEN s.IsFirst = 1 THEN (s.FDay)*s.VAF61                    
                                 WHEN s.IsFirst = 2 THEN s.VAF21*s.FDay
                               END) 
                 else 1 end
     ,s.VAJ34A,s.VAJ59,s.VAJ64,s.BBY01A ,VAJ72 = case when s.IsFirst = 2 then 2 when s.IsFirst=1 then 1 else 0 end 
	 ,CASE WHEN s.IsFirst = 0 THEN
        case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                            and t.bby01 = s.bby01 and t.jzbs=1) then 1
            when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                            and t.bby01 = s.bby01 and t.jzbs=1) then (select top 1 t.jzVAF27 from #kbmjzbs t 
									            where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                        and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc)
            when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                            and t.bby01 = s.bby01 and t.jzbs=9) then (select top 1 t.jzVAF27 from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc)
            else s.VAF27 end                    
        WHEN s.IsFirst = 1 THEN
            case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                            and t.bby01 = s.bby01 and t.jzbs=1) then 1
            when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                            and t.bby01 = s.bby01 and t.jzbs=1) then (select top 1 t.jzVAF27 from #kbmjzbs t 
									            where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                        and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc)
            when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                            and t.bby01 = s.bby01 and t.jzbs=9) then (select top 1 t.jzVAF27 from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc)
            else s.VAF61  end 
        WHEN s.IsFirst = 2 THEN 
            case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                            and t.bby01 = s.bby01 and t.jzbs=1) then 1
            when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                            and t.bby01 = s.bby01 and t.jzbs=1) then (select top 1 t.jzVAF27 from #kbmjzbs t 
									            where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                        and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc)
            when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                            and t.bby01 = s.bby01 and t.jzbs=9) then (select top 1 t.jzVAF27 from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc)
            else s.VAF62  end  END jzVAF27            
  from (
    select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12 
      ,VAJ34 = case when @lBLP262=0 then  isnull(f.VEH24,e.BAG07) else 
	              case when f.VEH27=1 then isnull(f.VEH24,e.BAG07)  else  1  end  end
	  ,0 VAJ59
      ,VAJ64 = case when c.IsFirst = 2 then c.VAF36 else case when DateDiff(Day,c.VAF36,c.VAF38) > 0 then  Convert(varchar(10),c.VAF38,21)+@curtime else c.VAF36 end end
      ,a.BBY01A,c.IsFirst,c.FDay,b.VAF21,b.VAF61,b.VAF62,b.VAF27,b.BBX01,b.VAF18,VAJ34A=isnull(f.VEH24,e.BAG07),b.BAG56
    from #kbmVBD a
    join @TmpVAFcz b on b.VAF01 = a.VAF01
    join #tmpBDA c on c.VAF01 = b.VAF01
    join BAG1 e with(nolock) on e.BBY01 = a.BBY01
	LEFT join VEH2 f with(nolock) on f.VAF01 = a.VAF01
    where (b.BDA01 >= '4' and b.VAF32 = 1 and a.BDN01 <= '3')
      and ((c.IsFirst = 2 and b.VAF62 > 0) or (c.IsFirst <= 1 and c.FDay <= 1)) 
   ) s 
  where not exists(select * from #kbmjzbs s1 where s1.vbd09 in (2,7) and s1.jzbs=9 and s1.vaf01 = s.vaf01 and DateDiff(Day,s1.VAJ64,s.VAJ64) = 0 and s1.bby01 = s.bby01)
  union all  
  select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12    
    ,VAJ25 = case when b.BDA01 >= 'A' and b.BDA01 <> 'M' and c.IsFirst = 1 and ISNULL(a.VBD12,0) > 0 then 
                       (b.VAF61 * a.VBD12 + (c.FDay-1)*a.VBD04)                        
                   when b.BDA01 >= 'A' and b.BDA01 <> 'M' and c.IsFirst = 2 and ISNULL(a.VBD12,0) > 0 then 
                       b.VAF62 * a.VBD12     
                   else a.VBD04 * c.FDay end
    ,VAJ23 = 1 ,VAJ34 = isnull(f.VEH24,e.BAG07) ,0  
    ,VAJ64 = case when c.IsFirst = 2 then c.VAF36 else case when DateDiff(Day,c.VAF36,c.VAF38) > 0 then  Convert(varchar(10),c.VAF38,21)+@curtime else c.VAF36 end end 
    ,a.BBY01A,VAJ72 = case when c.IsFirst = 2 then 2 when c.IsFirst=1 then 1 else 0 end
	,CASE WHEN c.IsFirst = 0 THEN a.jzVAF27	                    
			WHEN c.IsFirst = 1 then a.jzVAF27
			WHEN c.IsFirst = 2 then b.VAF62 END jzVAF27
  from #kbmVBD a
  join @TmpVAFcz b on b.VAF01 = a.VAF01
  join #tmpBDA c on c.VAF01 = b.VAF01
  join BAG1 e with(nolock) on e.BBY01 = a.BBY01
  LEFT join VEH2 f with(nolock) on f.VAF01 = a.VAF01
  where (b.BDA01 >= '4' or b.BDA01='0') and b.VAF32 <> 1 and a.BDN01 <= '3'
    and ((c.IsFirst = 2 and b.VAF62 > 0) or (c.IsFirst <= 1 and c.FDay <= 1))   
  union all
  select s.VBD01,s.VAF01,s.BBY01,s.VBD04,s.VBD05,s.VBD06,s.BCK01,s.VBD08,s.VBD09,s.BDN01,s.VAJ32,s.VAJ35,s.VBD12  
    ,VAJ25 = Case when s.BDA01 = '8' 
                  then (CASE WHEN s.IsFirst = 0 THEN s.VAF61*s.VBD04*s.FDay                    
                             WHEN s.IsFirst = 1 THEN s.VAF61*s.VBD04*(s.FDay)                 
                             WHEN s.IsFirst = 2 THEN s.VAF61*s.VBD04*s.FDay  END )              
                 else (CASE WHEN s.IsFirst = 0 THEN
                          (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then s.VBD04*s.FDay
								when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then s.VBD04*s.FDay*(select top 1 t.jzVAF27 from #kbmjzbs t 
									            where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                        and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc)
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then s.VBD04*s.FDay*(select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc)
                              else s.VBD04*s.FDay*s.VAF27 end )
                            WHEN s.IsFirst = 1 THEN 
                              (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then s.VBD04
								when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then s.VBD04*(select top 1 t.jzVAF27 from #kbmjzbs t 
									            where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                        and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc)
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then s.VBD04*(select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc)
                              else s.VBD04*s.VAF61 end )
                            WHEN s.IsFirst = 2 THEN 
                               (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then s.VBD04
								 when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then s.VBD04*(select top 1 t.jzVAF27 from #kbmjzbs t 
									            where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                        and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc)
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then s.VBD04*(select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc)
                                else s.VBD04*s.VAF62 end )
                               END  )  end  
    ,s.VAJ23,s.VAJ34,s.VAJ59,s.VAJ64,s.BBY01A ,VAJ72 = case when s.IsFirst = 2 then 2 when s.IsFirst=1 then 1 else 0 end 
	,Case when s.BDA01 = '8' then s.VAF61             
                 else (CASE WHEN s.IsFirst = 0 THEN
                          (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then 1
								when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then (select top 1 t.jzVAF27 from #kbmjzbs t 
									            where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                        and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc)
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then (select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc)
                              else s.VAF27 end )
                            WHEN s.IsFirst = 1 THEN 
                              (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then 1
								when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then (select top 1 t.jzVAF27 from #kbmjzbs t 
									            where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                        and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc)
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then (select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc)
                              else s.VAF61 end )
                            WHEN s.IsFirst = 2 THEN 
                               (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then 1
								 when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then (select top 1 t.jzVAF27 from #kbmjzbs t 
									            where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                        and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc)
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then (select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc)
                                else s.VAF62 end )
                               END  )  end  jzVAF27
  from (
    select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12  
      ,VAJ23 = 1  ,VAJ34 = null,VAJ59 = a.VAJ32
      ,VAJ64 = case when c.IsFirst = 2 then c.VAF36 else case when DateDiff(Day,c.VAF36,c.VAF38) > 0 then  Convert(varchar(10),c.VAF38,21)+@curtime else c.VAF36 end end       
      ,a.BBY01A,c.IsFirst,c.FDay,b.VAF21,b.VAF61,b.VAF62,b.VAF27,b.BBX01,b.BDA01
    from #kbmVBD a
    join @TmpVAFcz b on b.VAF01 = a.VAF01
    join #tmpBDA c on c.VAF01 = b.VAF01  
    where (b.BDA01 >= '4' and b.VAF32 = 1 and a.BDN01 >= '4') and ((c.IsFirst = 2 and b.VAF62 > 0) or (c.IsFirst <= 1 and c.FDay <= 1)) ) s 
  where not exists(select * from #kbmjzbs s1 where s1.vbd09 in (2,7) and s1.jzbs=9 and s1.vaf01 = s.vaf01 and DateDiff(Day,s1.VAJ64,s.VAJ64) = 0 and s1.bby01 = s.bby01)   
  union all
  select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12    
    ,VAJ25 = Case when b.BDA01 = '8' 
                  then (CASE WHEN c.IsFirst = 0 THEN b.VAF61*a.VBD04*c.FDay                    
                             WHEN c.IsFirst = 1 THEN a.VBD04*(b.VAF61+(c.FDay-1)*b.VAF27)                  
                             WHEN c.IsFirst = 2 THEN b.VAF61*a.VBD04*c.FDay  END )              
                 else (case when a.VBD09 = 2 then 
                            (CASE WHEN c.IsFirst = 2 THEN a.VBD04
                                       else a.VBD04*c.FDay END)  
                        else (CASE WHEN c.IsFirst = 0 THEN a.VBD04*c.FDay*b.VAF27	                    
                                   WHEN c.IsFirst = 1 THEN a.VBD04*(b.VAF61+(c.FDay-1)*b.VAF27)                    
                                   WHEN c.IsFirst = 2 THEN a.VBD04*b.VAF62
                               END)
                       end )  end                          
    ,VAJ23 = 1  ,VAJ34 = null,VAJ59 = a.VAJ32
    ,VAJ64 = case when c.IsFirst = 2 then c.VAF36 else case when DateDiff(Day,c.VAF36,c.VAF38) > 0 then  Convert(varchar(10),c.VAF38,21)+@curtime else c.VAF36 end end       
    ,a.BBY01A,VAJ72 = case when c.IsFirst = 2 then 2 when c.IsFirst=1 then 1 else 0 end
	,CASE WHEN c.IsFirst = 0 THEN a.jzVAF27	                    
			WHEN c.IsFirst = 1 then a.jzVAF27
			WHEN c.IsFirst = 2 then b.VAF62 END jzVAF27
  from #kbmVBD a
  join @TmpVAFcz b on b.VAF01 = a.VAF01
  join #tmpBDA c on c.VAF01 = b.VAF01  
  where (b.BDA01>='1' and b.BDA01 <= '3' and a.BDN01 >= '4') and ((c.IsFirst = 2 and b.VAF62 > 0) or (c.IsFirst <= 1 and c.FDay <= 1))   
  union all
  select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12    
    ,VAJ25 =  case when d.BDA01 = '8' then 
	            (case when b.BDA01 >= 'A' and b.BDA01 <> 'M' and c.IsFirst = 1 and ISNULL(a.VBD12,0) > 0 then 
                       (b.VAF61*a.VBD04 + (c.FDay-1)*a.VBD04)                        
                  when b.BDA01 >= 'A' and b.BDA01 <> 'M' and c.IsFirst = 2 and ISNULL(a.VBD12,0) > 0 then 
                       (b.VAF62*a.VBD04  )   
                  when b.BDA01 in ('4','M') and c.IsFirst = 2 then b.VAF62 * a.VBD04
                   else a.VBD04 * c.FDay* b.VAF27 end )
	          else	   
	             (case when b.BDA01 >= 'A' and b.BDA01 <> 'M' and c.IsFirst = 1 and ISNULL(a.VBD12,0) > 0 then 
                       (b.VAF61 * a.VBD12 + (c.FDay-1)*a.VBD04)                        
                  when b.BDA01 >= 'A' and b.BDA01 <> 'M' and c.IsFirst = 2 and ISNULL(a.VBD12,0) > 0 then 
                       (b.VAF62 * a.VBD12 )   
                  when b.BDA01 in ('4','M') and c.IsFirst = 2 then b.VAF62 * a.VBD04
                   else a.VBD04 * c.FDay end) end                          
    ,VAJ23 = 1  ,VAJ34 = null,VAJ59 = a.VAJ32
    ,VAJ64 = case when c.IsFirst = 2 then c.VAF36 else case when DateDiff(Day,c.VAF36,c.VAF38) > 0 then  Convert(varchar(10),c.VAF38,21)+@curtime else c.VAF36 end end       
    ,a.BBY01A,VAJ72 = case when c.IsFirst = 2 then 2 when c.IsFirst=1 then 1 else 0 end
	,CASE WHEN c.IsFirst = 0 THEN a.jzVAF27	                    
			WHEN c.IsFirst = 1 then a.jzVAF27
			WHEN c.IsFirst = 2 then b.VAF62 END jzVAF27
  from #kbmVBD a
  join @TmpVAFcz b on b.VAF01 = a.VAF01
  join #tmpBDA c on c.VAF01 = b.VAF01  
  left join @TmpVAFcz d on d.VAF01 = b.VAF01A
  where ((b.BDA01 >= '4' or b.BDA01='0') and b.VAF32 <> 1 and a.BDN01 >= '4')  
    and ((c.IsFirst = 2 and b.VAF62 > 0) or (c.IsFirst <= 1 and c.FDay <= 1))  
   
  set @mDay = ISNULL((select MAX(b.Fday) from @TmpVAFcz a
                        join #tmpBDA b on b.VAF01 = a.VAF01
                       where b.IsFirst <= 1 and b.FDay >= 2),0)
  if @mDay >= 2
  begin
    set @anum = 1
    while @anum <= @mDay
    begin    
      insert into #tmpVBD(VBD01,VAF01,BBY01,VBD04,VBD05,VBD06,BCK01,VBD08,VBD09,BDN01,VAJ32,VAJ35,VBD12,VAJ25,VAJ23,VAJ34,VAJ59,VAJ64,BBY01A,VAJ72,jzVAF27 )    
      select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12
        ,VAJ25 = case when @lBLP262=0 then
		           (case when a.BDN01 <= '2' then
				        (CASE WHEN isnull(b.BAG56,0)=1 then 
                           ( CASE WHEN c.IsFirst = 1 and @anum = 1 THEN dbo.GetClinicGetValueByBAG56(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*b.VAF61 AS NUMERIC(18,4)))
                                else dbo.GetClinicGetValueByBAG56(cast(a.VBD04*isnull(f.VEH24,e.BAG07)* b.VAF27 AS NUMERIC(18,4)))  END  ) 
                        else  
                           ( CASE WHEN c.IsFirst = 1 and @anum = 1 THEN CEILING(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*b.VAF61 AS NUMERIC(18,4)))
                                else CEILING(cast(a.VBD04*isnull(f.VEH24,e.BAG07)* b.VAF27 AS NUMERIC(18,4)))  END  ) end  ) 
					  when a.BDN01 = '3' then
                           ( CASE WHEN c.IsFirst = 1 and @anum = 1 THEN CEILING(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*b.VAF61 AS NUMERIC(18,4)))
                                else CEILING(cast(a.VBD04*isnull(f.VEH24,e.BAG07)* b.VAF27 AS NUMERIC(18,4)))  END  ) 
                      else a.VBD04*isnull(f.VEH24,e.BAG07)  end)  
				 else  (    case when f.VEH27=1 then 
							   (case when a.BDN01 <= '2' then (CASE WHEN isnull(b.BAG56,0)=1 then    
												   ( CASE WHEN c.IsFirst = 1 and @anum = 1 THEN dbo.GetClinicGetValueByBAG56(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*b.VAF61 AS NUMERIC(18,4)))
														else dbo.GetClinicGetValueByBAG56(cast(a.VBD04*isnull(f.VEH24,e.BAG07)* b.VAF27 AS NUMERIC(18,4)))  END  ) 
                                           else ( CASE WHEN c.IsFirst = 1 and @anum = 1 THEN CEILING(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*b.VAF61 AS NUMERIC(18,4)))
														else CEILING(cast(a.VBD04*isnull(f.VEH24,e.BAG07)* b.VAF27 AS NUMERIC(18,4)))  END  )  end  )

							         when a.BDN01 = '3' then ( CASE WHEN c.IsFirst = 1 and @anum = 1 THEN CEILING(cast(a.VBD04*isnull(f.VEH24,e.BAG07)*b.VAF61 AS NUMERIC(18,4)))
														else CEILING(cast(a.VBD04*isnull(f.VEH24,e.BAG07)* b.VAF27 AS NUMERIC(18,4)))  END  ) 
									 else a.VBD04*isnull(f.VEH24,e.BAG07)  end)  
							else ( case when a.BDN01 <= '3' then ( CASE WHEN c.IsFirst = 1 and @anum = 1 THEN CEILING(cast(a.VBD04*b.VAF61 AS NUMERIC(18,4)))
														else CEILING(cast(a.VBD04* b.VAF27 AS NUMERIC(18,4)))  END  ) 
										else a.VBD04  end	)	end  )   end                               
        ,VAJ23 = case when a.BDN01 = '3' then b.VAF21 else 1 end ,VAJ34 = isnull(f.VEH24,e.BAG07),0
        ,VAJ64 = case when @anum > 1 then (case when b.VAF29 <> 'D' OR (b.VAF29 = 'D' and b.VAF28 <= 1 and c.VEH07<='') then Convert(varchar(10),(c.VAF36 + (@anum - 1)),21)+@curtime
                                           else case when c.VEH07>'' then (select Convert(varchar(10),s1.fvaf36,21)+@curtime from dbo.GetBDI1_BDI14(c.VAF01,c.VEH07,c.VAF36,c.VAF38,1) s1 where s1.fday=@anum )
                                                     else Convert(varchar(10),(c.VAF36 + (@anum - 1)* b.VAF28),21)+@curtime end end )
                      else c.VAF36 end,a.BBY01A ,VAJ72 = case when c.IsFirst=1 and @anum=1 then 1 else 0 end
	    ,CASE WHEN c.IsFirst = 1 and @anum=1 then b.VAF61 else a.jzVAF27 END jzVAF27        
      from #kbmVBD a
      join @TmpVAFcz b on b.VAF01 = a.VAF01
      join #tmpBDA c on c.VAF01 = b.VAF01
      join BAG1 e with(nolock) on e.BBY01 = a.BBY01
	  LEFT join VEH2 f with(nolock) on f.VAF01 = a.VAF01
      where (b.BDA01 >= '1' and b.BDA01 <= '3' and a.BDN01 <= '3')and c.FDay >= 2 and c.FDay >= @anum and b.VAF75<>1
        and ((c.IsFirst =0 )or (c.IsFirst = 1 and (@anum=1 or (@anum>=2 and b.vaf75=0)) ) )
      union all  
      select s.VBD01,s.VAF01,s.BBY01,s.VBD04,s.VBD05,s.VBD06,s.BCK01,s.VBD08,s.VBD09,s.BDN01,s.VAJ32,s.VAJ35,s.VBD12
        ,VAJ25 = case when s.BDN01 <= '2' then
		          (CASE WHEN isnull(s.BAG56,0)=1 then
                        ( CASE WHEN s.IsFirst = 1 and @anum = 1 THEN 
                                (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                         and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) 
                                        then dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.BAG07 AS NUMERIC(18,4)))
									  when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                         and t.vbd09=7 and t.bby01 = s.bby01 and t.jzbs=1) 
                                        then dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.BAG07*(select top 1 t.jzVAF27 from #kbmjzbs t 
									            where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                        and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                     when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                       and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9) 
                                     then dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.BAG07*(select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                     else dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.BAG07*s.VAF61 AS NUMERIC(18,4))) end ) 
                          else 
                            (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                         and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) 
                                      then dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.BAG07 AS NUMERIC(18,4)))
								  when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                         and t.vbd09=7 and t.bby01 = s.bby01 and t.jzbs=1) 
                                        then dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.BAG07*(select top 1 t.jzVAF27 from #kbmjzbs t 
									            where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                        and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                   when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                       and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9) 
                                     then dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.BAG07*(select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                   else dbo.GetClinicGetValueByBAG56(cast(s.VBD04*s.BAG07*s.VAF27 AS NUMERIC(18,4))) end ) 
                          END  ) 
					else 
					( CASE WHEN s.IsFirst = 1 and @anum = 1 THEN 
                                (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                         and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) 
                                        then CEILING(cast(s.VBD04*s.BAG07 AS NUMERIC(18,4)))
                                     when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                         and t.vbd09=7 and t.bby01 = s.bby01 and t.jzbs=1) 
                                        then CEILING(cast(s.VBD04*s.BAG07*(select top 1 t.jzVAF27 from #kbmjzbs t 
									            where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                        and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                     when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                       and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9) 
                                     then CEILING(cast(s.VBD04*s.BAG07*(select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                     else CEILING(cast(s.VBD04*s.BAG07*s.VAF61 AS NUMERIC(18,4))) end ) 
                          else 
                            (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                         and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) 
                                      then CEILING(cast(s.VBD04*s.BAG07 AS NUMERIC(18,4)))
                                  when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                         and t.vbd09=7 and t.bby01 = s.bby01 and t.jzbs=1) 
                                        then CEILING(cast(s.VBD04*s.BAG07*(select top 1 t.jzVAF27 from #kbmjzbs t 
									            where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                        and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
								  when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                       and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9) 
                                     then CEILING(cast(s.VBD04*s.BAG07*(select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                  else CEILING(cast(s.VBD04*s.BAG07*s.VAF27 AS NUMERIC(18,4))) end ) 
                          END  )  end  )
					  when s.BDN01 = '3' then
                        ( CASE WHEN s.IsFirst = 1 and @anum = 1 THEN 
                                (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                         and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) 
                                       then CEILING(cast(s.VBD04*s.BAG07 AS NUMERIC(18,4)))
									when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                         and t.vbd09=7 and t.bby01 = s.bby01 and t.jzbs=1) 
                                        then CEILING(cast(s.VBD04*s.BAG07*(select top 1 t.jzVAF27 from #kbmjzbs t 
									            where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                        and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                     when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                       and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9) 
                                     then CEILING(cast(s.VBD04*s.BAG07*(select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                     else CEILING(cast(s.VBD04*s.BAG07*s.VAF61 AS NUMERIC(18,4))) end ) 
                          else 
                            (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                         and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) 
                                      then CEILING(cast(s.VBD04*s.BAG07 AS NUMERIC(18,4)))
								when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                         and t.vbd09=7 and t.bby01 = s.bby01 and t.jzbs=1) 
                                        then CEILING(cast(s.VBD04*s.BAG07*(select top 1 t.jzVAF27 from #kbmjzbs t 
									            where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                                        and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                     when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
                                       and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9) 
                                        then CEILING(cast(s.VBD04*s.BAG07*(select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc) AS NUMERIC(18,4)))
                                     else CEILING(cast(s.VBD04*s.BAG07*s.VAF27 AS NUMERIC(18,4))) end ) 
                          END  ) 
                      else s.VBD04*s.BAG07  end   
        ,s.VAJ23,s.VAJ34,s.VAJ59,s.VAJ64,s.BBY01A ,VAJ72 = case when s.IsFirst=1 and @anum=1 then 1 else 0 end  
		,CASE WHEN s.IsFirst = 1 and @anum = 1 THEN 
												  (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
													 and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) then 1
												  when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
													 and t.vbd09=7 and t.bby01 = s.bby01 and t.jzbs=1) 
													then (select top 1 t.jzVAF27 from #kbmjzbs t 
															where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
																	and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc)
												 when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
												   and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9)  then (select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc)
												 else s.VAF61 end ) 
									  else 
										(case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
													 and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) 
												  then 1
											  when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
													 and t.vbd09=7 and t.bby01 = s.bby01 and t.jzbs=1) 
													then (select top 1 t.jzVAF27 from #kbmjzbs t 
															where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
																	and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc)
											   when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 
												   and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9)  then (select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc)
											   else s.VAF27 end ) END jzVAF27         
      from (
        select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12                                         
          ,VAJ23 = case when a.BDN01 = '3' then b.VAF21 else 1 end ,VAJ34 = isnull(f.VEH24,e.BAG07),0 VAJ59
          ,VAJ64 = case when @anum > 1 then (case when b.VAF29 <> 'D' OR (b.VAF29 = 'D' and b.VAF28 <= 1 and c.VEH07<='') then Convert(varchar(10),(c.VAF36 + (@anum - 1)),21)+@curtime
                                           else case when c.VEH07>'' then (select Convert(varchar(10),s1.fvaf36,21)+@curtime from dbo.GetBDI1_BDI14(c.VAF01,c.VEH07,c.VAF36,c.VAF38,1) s1 where s1.fday=@anum )
                                                     else Convert(varchar(10),(c.VAF36 + (@anum - 1)* b.VAF28),21)+@curtime end end )
                      else c.VAF36 end,a.BBY01A,c.IsFirst,c.FDay,b.VAF21,b.VAF61,b.VAF62,b.VAF27,b.BBX01,b.BDA01,
					  BAG07= case when @lBLP262=0 then isnull(f.VEH24,e.BAG07)  else
					         ( case when f.VEH27=1  then isnull(f.VEH24,e.BAG07) else 1 end ) end ,b.BAG56       
        from #kbmVBD a
        join @TmpVAFcz b on b.VAF01 = a.VAF01
        join #tmpBDA c on c.VAF01 = b.VAF01
        join BAG1 e with(nolock) on e.BBY01 = a.BBY01
		LEFT join VEH2 f with(nolock) on f.VAF01 = a.VAF01
        where (b.BDA01 >= '4' and b.VAF32 = 1 and a.BDN01 <= '3') and (c.IsFirst <= 1 and c.FDay >= 2 and c.FDay >= @anum) ) s
      where not exists(select * from #kbmjzbs s1 where s1.vbd09 in (2,7) and s1.jzbs=9 and s1.vaf01 = s.vaf01 and DateDiff(Day,s1.VAJ64,s.VAJ64) = 0 and s1.bby01 = s.bby01)       
      union all 
      select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12    
        ,VAJ25 = case when b.BDA01 >= 'A' and b.BDA01 <> 'M' and c.IsFirst = 1  and @anum = 1 and ISNULL(a.VBD12,0) > 0 then 
                           (b.VAF61 * a.VBD12) else a.VBD04 end
        ,VAJ23 = 1 ,VAJ34 = isnull(f.VEH24,e.BAG07),0  
        ,VAJ64 = case when @anum > 1 then (case when b.VAF29 <> 'D' OR (b.VAF29 = 'D' and b.VAF28 <= 1 and c.VEH07<='') then Convert(varchar(10),(c.VAF36 + (@anum - 1)),21)+@curtime
                                           else case when c.VEH07>'' then (select Convert(varchar(10),s1.fvaf36,21)+@curtime from dbo.GetBDI1_BDI14(c.VAF01,c.VEH07,c.VAF36,c.VAF38,1) s1 where s1.fday=@anum )
                                                     else Convert(varchar(10),(c.VAF36 + (@anum - 1)* b.VAF28),21)+@curtime end end )
                      else c.VAF36 end ,a.BBY01A ,VAJ72 = case when c.IsFirst=1 and @anum=1 then 1 else 0 end  ,CASE WHEN c.IsFirst = 1 and @anum=1 then b.VAF61 else a.jzVAF27 END jzVAF27                
      from #kbmVBD a
      join @TmpVAFcz b on b.VAF01 = a.VAF01
      join #tmpBDA c on c.VAF01 = b.VAF01
      join BAG1 e with(nolock) on e.BBY01 = a.BBY01
	  LEFT join VEH2 f with(nolock) on f.VAF01 = a.VAF01
      where (b.BDA01 >= '4' or b.BDA01='0') and b.VAF32 <> 1 and a.BDN01 <= '3'  and (c.IsFirst <= 1 and c.FDay >= 2 and c.FDay >= @anum)
      union all
      select s.VBD01,s.VAF01,s.BBY01,s.VBD04,s.VBD05,s.VBD06,s.BCK01,s.VBD08,s.VBD09,s.BDN01,s.VAJ32,s.VAJ35,s.VBD12
        ,VAJ25 = case when s.BDA01 = '8' then s.vbd04  
                   else (case WHEN s.IsFirst = 1 and @anum = 1 THEN
                            (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then s.VBD04
								when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then s.VBD04*(select top 1 t.jzVAF27 from #kbmjzbs t 
															where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
																	and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc)
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then s.VBD04*(select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc) 
                              else s.VBD04*s.VAF61 end )  
                          else (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then s.VBD04
								 when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then s.VBD04*(select top 1 t.jzVAF27 from #kbmjzbs t 
															where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
																	and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc)
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then s.VBD04*(select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc) 
                              else s.VBD04*s.VAF27 end )  
                          END)
                   end         
        ,s.VAJ23,s.VAJ34,s.VAJ59,s.VAJ64,s.BBY01A ,VAJ72 = case when s.IsFirst=1 and @anum=1 then 1 else 0 end  
		,case when s.BDA01 = '8' then 1  
                   else (case WHEN s.IsFirst = 1 and @anum = 1 THEN
                            (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then 1
								when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then (select top 1 t.jzVAF27 from #kbmjzbs t 
															where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
																	and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc)
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then (select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc) 
                              else s.VAF61 end )  
                          else (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2
                                             and t.bby01 = s.bby01 and t.jzbs=1) then 1
								 when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
                                             and t.bby01 = s.bby01 and t.jzbs=1) then (select top 1 t.jzVAF27 from #kbmjzbs t 
															where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=7
																	and t.bby01 = s.bby01 and t.jzbs=1 order by t.jzVAF27 desc)
                                when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                             and t.bby01 = s.bby01 and t.jzbs=9) then (select top 1 t.jzVAF27 from #kbmjzbs t 
											   where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3
                                                 and t.bby01 = s.bby01 and t.jzbs=9 order by t.jzVAF27 desc)
                              else s.VAF27 end )  
                          END)
                   end   jzVAF27             
      from (
        select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12 
          ,VAJ23 = 1  ,VAJ34 = null,VAJ59 = a.VAJ32
          ,VAJ64 = case when @anum > 1 then (case when b.VAF29 <> 'D' OR (b.VAF29 = 'D' and b.VAF28 <= 1 and c.VEH07<='') then Convert(varchar(10),(c.VAF36 + (@anum - 1)),21)+@curtime
                                           else case when c.VEH07>'' then (select Convert(varchar(10),s1.fvaf36,21)+@curtime from dbo.GetBDI1_BDI14(c.VAF01,c.VEH07,c.VAF36,c.VAF38,1) s1 where s1.fday=@anum )
                                                     else Convert(varchar(10),(c.VAF36 + (@anum - 1)* b.VAF28),21)+@curtime end end )
                      else c.VAF36 end 
          ,a.BBY01A,c.IsFirst,c.FDay,b.VAF21,b.VAF61,b.VAF62,b.VAF27,b.BBX01,b.BDA01
        from #kbmVBD a
        join @TmpVAFcz b on b.VAF01 = a.VAF01
        join #tmpBDA c on c.VAF01 = b.VAF01  
        where (b.BDA01 >= '4' and b.VAF32 = 1 and a.BDN01 >= '4') and (c.IsFirst <= 1 and c.FDay >= 2 and c.FDay >= @anum) ) s
      where not exists(select * from #kbmjzbs s1 where s1.vbd09 in(2,7) and s1.jzbs=9 and s1.vaf01 = s.vaf01 and DateDiff(Day,s1.VAJ64,s.VAJ64) = 0 and s1.bby01 = s.bby01)     
      union all
      select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12    
        ,VAJ25 = Case when b.BDA01 = '8' then a.VBD04            
                     else (case when a.VBD09 = 2 then a.VBD04
                            else (CASE WHEN c.IsFirst = 1 and @anum = 1 THEN a.VBD04*b.VAF61 else a.VBD04*b.VAF27 END)  end )  end                           
        ,VAJ23 = 1  ,VAJ34 = null,VAJ59 = a.VAJ32
        ,VAJ64 = case when @anum > 1 then (case when b.VAF29 <> 'D' OR (b.VAF29 = 'D' and b.VAF28 <= 1 and c.VEH07<='') then Convert(varchar(10),(c.VAF36 + (@anum - 1)),21)+@curtime
                                           else case when c.VEH07>'' then (select Convert(varchar(10),s1.fvaf36,21)+@curtime from dbo.GetBDI1_BDI14(c.VAF01,c.VEH07,c.VAF36,c.VAF38,1) s1 where s1.fday=@anum )
                                                     else Convert(varchar(10),(c.VAF36 + (@anum - 1)* b.VAF28),21)+@curtime end end )
                      else c.VAF36 end ,a.BBY01A ,VAJ72 = case when c.IsFirst=1 and @anum=1 then 1 else 0 end 
		,CASE WHEN c.IsFirst = 1 and @anum=1 then b.VAF61 else a.jzVAF27 END jzVAF27                
      from #kbmVBD a
      join @TmpVAFcz b on b.VAF01 = a.VAF01
      join #tmpBDA c on c.VAF01 = b.VAF01  
      where (b.BDA01>='1' and b.BDA01 <= '3' and a.BDN01 >= '4') and (c.IsFirst <= 1 and c.FDay >= 2 and c.FDay >= @anum)  
      union all
      select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12    
        ,VAJ25 = case when b.BDA01 >= 'A' and b.BDA01 <> 'M' and c.IsFirst = 1 and @anum = 1 and ISNULL(a.VBD12,0) > 0 then 
                           (b.VAF61 * a.VBD12) else a.VBD04 end                          
        ,VAJ23 = 1  ,VAJ34 = null,VAJ59 = a.VAJ32
        ,VAJ64 = case when @anum > 1 then (case when b.VAF29 <> 'D' OR (b.VAF29 = 'D' and b.VAF28 <= 1 and c.VEH07<='') then Convert(varchar(10),(c.VAF36 + (@anum - 1)),21)+@curtime
                                           else case when c.VEH07>'' then (select Convert(varchar(10),s1.fvaf36,21)+@curtime from dbo.GetBDI1_BDI14(c.VAF01,c.VEH07,c.VAF36,c.VAF38,1) s1 where s1.fday=@anum )
                                                     else Convert(varchar(10),(c.VAF36 + (@anum - 1)* b.VAF28),21)+@curtime end end )
                      else c.VAF36 end ,a.BBY01A,VAJ72 = case when c.IsFirst=1 and @anum=1 then 1 else 0 end  
	   ,CASE WHEN c.IsFirst = 1 and @anum=1 then b.VAF61 else a.jzVAF27 END jzVAF27                
      from #kbmVBD a
      join @TmpVAFcz b on b.VAF01 = a.VAF01
      join #tmpBDA c on c.VAF01 = b.VAF01  
      where ((b.BDA01 >= '4' or b.BDA01='0') and b.VAF32 <> 1 and a.BDN01 >= '4')  
        and (c.IsFirst <= 1 and c.FDay >= 2 and c.FDay >= @anum)
              
      set @anum = @anum + 1
    end
  end   


  --判断没有计价内容药品医嘱，不允许发送
  if @gpara90 <> '1' and 
      exists(select * from @TmpVAFcz a
       where a.VAF58 <> 1 and a.BDA01 >='1' and a.BDA01 <= '3' and a.vaf21 > 0.00001 and (@lOneDay <> 2 or (@lOneDay = 2 and a.VAF62 > 0))
          and not exists(select * from #tmpVBD b where b.VAF01 = a.VAF01))
  begin
    set @smsg = (select top 1 a.VAF22 as fname
                 from @TmpVAFcz a
                 where a.VAF58 <> 1 and a.BDA01 >='1' and a.BDA01 <= '3' and (@lOneDay <> 2 or (@lOneDay = 2 and a.VAF62 > 0))
                   and not exists(select * from #tmpVBD b where b.VAF01 = a.VAF01))
    --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz
    IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA
    IF Object_id('tempdb..#TmpVAJls') IS NOT NULL DROP TABLE #TmpVAJls
    IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD
    IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI
    set @smsg = '【'+@smsg+'】此医嘱没有计价内容，不能发送，请先联系管理员.'
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 6
  end
  
  delete from #tmpVBD where isnull(VAJ25,0) <= 0 
  delete a
  from #tmpVBD a
  join @TmpVAFcz b on b.VAF01 = a.VAF01
  where (b.BBX17 = 9) or (b.BDA01 >= '1' and b.BDA01 <= '2' and b.VAF75 = 1 and b.VAF34 = 1)


  if exists(select * from @TmpVAFcz where BDA01 >= '1' and BDA01 <= '3')
    and exists(select * from @Tmp_BCK)
  begin
    update a set a.BCK01 = b.BCK01A
    from #tmpVBD a
    join @Tmp_BCK b on b.BCK01 = a.BCK01
    join @TmpVAFcz c on c.VAF01 = a.VAF01
    where c.BDA01 >= '1' and c.BDA01 <= '3' and a.BDN01 <= '3'    
  end  
  --**自动拆分库存批次
  Insert Into @TpVAJTkc(VAA01,VAA07,Rownr,VAF01,ACF01,BCK01A,BCK01B,BDN01,BBY01,VAJ23,VAJ24,VAJ25
      ,VAJ26,VAJ27,BCK01C,BCE02B,BCE03B,BCK01D,VAJ53,VAJ54,VAJ57,BCK01E,CBM01,ABF01,ABC02,VAJ39
      ,VAJ32,VAJ01A,VAJ34,VAJ35,VAJ59,ABC01,BCK18,VAJ64,BCK01F,BCQ04,VAJ72,BDA01,DSK01,jzVAF27)
  select a.VAA01,a.VAA07,a.Rownr,a.VAF01,a.ACF01,a.BCK01F,a.BCK01A,b.BDN01,b.BBY01,b.VAJ23,a.VAF18,b.VAJ25
    ,a.VAF35,a.VAP01,a.BCK01C,a.BCE02A,a.BCE03A,b.BCK01
    ,case when a.VBI13=1 and (b.BCK01 = a.bck01c_br or b.bck01 = a.bck01d_br) and b.BDN01 not in ('1','2','3','4','M') then 1 else 0 end VAJ53
    ,case when @lOneDay = 2 then '长嘱确认停止' else '长嘱发送' end VAJ54
    ,case when b.BBY01A > 0 or a.VAF01B >0 then isnull(a.VAJ57,'')+'.'+isnull(d.BBY05,'')+ISNULL(a.VAJ57B,'') else a.VAJ57 end
    ,a.BCK01D,a.CBM01,c.ABF01,a.ABC02,a.VAF31 ,b.VAJ32,a.VAF01B,b.VAJ34,b.VAJ35,b.VAJ59,0,9 ,b.VAJ64,0,'' ,b.VAJ72,a.BDA01,a.DSK01 ,b.jzVAF27             
  from @TmpVAFcz a
  join #tmpVBD b on b.VAF01 = a.VAF01
  join BBY1 c with(nolock) on c.BBY01 = b.BBY01    
  left join BBY1 d with(nolock) on d.BBY01 = b.BBY01A  

    
  UPDATE a set a.ABC01 = 1 
  from @TpVAJTkc a
  join ABC1 b with(nolock) on b.ABC02 = a.ABC02
  where b.ACF01 in (2,3) and DATEDIFF(DAY,b.ABC12,@curDate)>=0 and DATEDIFF(DAY,@curDate,b.ABC13)>= 0 
    and (isnull(b.ABC05,0) = 0 or(isnull(b.ABC05,0) = 1 and exists(select * from ACW1 c with(nolock) where c.ABC02 = b.ABC02 and a.BCK01B = c.BCK01)))
  
  update a set a.BCK18 = case when ISNULL(f1.BBY01,0)<> 0 then f1.ACV10
                              when ISNULL(f1.BBY01,0)= 0 and ISNULL(g1.BCH01,'') <> '' then g1.ACV10 else a.BCK18 end
  FROM @TpVAJTkc a
  JOIN BBY1 c with(nolock) ON a.BBY01 = c.BBY01
  LEFT JOIN (SELECT f.ABC02,f.BBY01,f.ACV10 FROM ACV1 f with(nolock) 
             WHERE f.BCH01 IS NULL and ISNULL(f.ACV10,9)<>9) AS f1 ON a.ABC02=f1.ABC02 AND a.BBY01=f1.BBY01                   
  LEFT JOIN (SELECT g.ABC02,g.BCH01,g.ACV10 FROM ACV1 g with(nolock) 
             WHERE g.BBY01 IS NULL and ISNULL(g.ACV10,9)<>9) AS g1 ON a.ABC02=g1.ABC02 AND c.BCH01=g1.BCH01
                 AND NOT EXISTS(SELECT * FROM ACV1 g2 with(nolock) WHERE g2.BCH01 IS NULL AND a.ABC02=g2.ABC02 AND a.BBY01=g2.BBY01)                   
  where a.ABC01 = 1 and a.BDN01 >= '1' and a.BDN01 <= '3'
    
  update a set a.BCK18 = isnull(c.BCK18,0)
  from @TpVAJTkc a   
  join BCK1 c with(nolock) on c.BCK01 = a.BCK01D  
  where a.BDN01 >='1' and a.BDN01 <= '3' and a.BCK18 = 9
  
  update a set a.FAB03 = isnull(d.VEH23,b.BDG02B),a.VAJ59 = case when isnull(b.BAG23,0) > 0.000001 then b.BAG23 else c.BBY25 end
  from @TpVAJTkc a
  join BAG1 b with(nolock) on b.BBY01 = a.BBY01
  join BBY1 c with(nolock) on c.BBY01 = a.BBY01
  LEFT Join VEH2 d with(nolock) on d.VAF01 = a.VAF01
  where a.BDN01 >= '1' and a.BDN01 <= '3'
  
    update a 
    set a.VAJ32 = case when a.BCK18 = 1 and isnull(b.BAG23,0) > 0.000001 then b.BAG23 else a.VAJ32 end
    from @TpVAJTkc a
    join BAG1 b with(nolock) on b.BBY01 = a.BBY01
    where a.BDN01 >='1' and a.BDN01 <= '3'
	--根据参数选择批次
    insert into @BCKPara(BCK01,parav)
    select a.BCK01,dbo.GetOneParamValue_ext(100,111001,1,a.BCK01)
    from (select DISTINCT BCK01D BCK01 from @TpVAJTkc where BDN01>='1' and BDN01<='3' ) a
    
    select a.* into #kbmDpt
    from DPT1 a
    join (select distinct BCK01D BCK01,BBY01
          from @TpVAJTkc 
          where BDN01 >='1' and BDN01 <= '3') b on a.BCK01 = b.BCK01 and b.BBY01 = a.BBY01
    where a.Quantity > 0.0001 and a.RefQty > 0.0001 and a.BAL08=0 and a.ExpiryDate>@aDate    
    --定价不要启用拆分参数	
	 if @gParam126='1'
	 begin   
	   --增加库存判断
     set @smsg=''
      select @smsg=c.BBY05+' 当前库存'+cast(isnull(b.vaj25,0) as varchar)+' 记账库存'++cast(isnull(a.fvaf21,0) as varchar)
      from (select BCK01D,BBY01,sum(vaj25) fvaf21
       from @TpVAJTkc where BDN01<='3' group by BCK01D,BBY01) a
	  join (select BCK01,BBY01,SUM(Quantity) vaj25 from #kbmDpt group by BCK01,BBY01) b on a.BCK01D = b.BCK01 and b.BBY01 = a.BBY01
	  join BBY1 c with(nolock) on c.BBY01=a.BBY01
	  where a.fvaf21-b.vaj25>0.00001
      if @smsg>''
      begin
        set @smsg=replace(isnull(@smsg,''),'%','％')+',库存已不足.' 
        RAISERROR(@smsg, 16, 1) with nowait
       IF Object_id('tempdb..#TmpVAJts') IS NOT NULL DROP TABLE #TmpVAJts
       IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD
       IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI
       IF Object_id('tempdb..#TmpVAI') IS NOT NULL DROP TABLE #TmpVAI
	   IF Object_id('tempdb..#kbmDpt') IS NOT NULL DROP TABLE #kbmDpt
       RETURN 7
     end
	   update @TpVAJTkc set DSK01=0 where BDN01<='3'
	   if exists(select a.DSK01 from @TpVAJTkc a join @BCKPara b on b.bck01= a.BCK01D 
				  where b.parav='0' and isnull(a.DSK01,0)=0 and a.BDN01 >='1' and a.BDN01 <= '3')
	   BEGIN
		 insert into @kbDPT(BCK01,BBY01,DSK01,quantity,LSQty,aflag)
		 select a.BCK01,a.BBY01,a.DSK_ID,a.Quantity,0,0
		 from #kbmDpt a
		 join (select BCK01D,BBY01,sum(vaj25) fvaf21
		   from @TpVAJTkc 
		   where BDN01 >='1' and BDN01 <= '3'
		   group by BCK01D,BBY01) b on b.BCK01D = a.BCK01 and b.BBY01 = a.BBY01
		 where a.Quantity > 0.000001 and a.RefQty > 0.000001 and a.ExpiryDate>@adate
		   and EXISTS(select * from @BCKPara c where c.BCK01=a.BCK01 and c.parav='0')
		 ORDER by a.BCK01,a.BBY01,a.DSK_ID ASC     
	   END
	   if exists(select a.DSK01 from @TpVAJTkc a join @BCKPara b on b.bck01= a.BCK01D 
			  where b.parav='1' and isnull(a.DSK01,0)=0 and a.BDN01 >='1' and a.BDN01 <= '3')
	   BEGIN
		 insert into @kbDPT(BCK01,BBY01,DSK01,quantity,LSQty,aflag)
		 select a.BCK01,a.BBY01,a.DSK_ID,a.Quantity,0,0
		 from #kbmDpt a
		 join (select BCK01D,BBY01,sum(vaj25) fvaf21
		   from @TpVAJTkc 
		   where BDN01 >='1' and BDN01 <= '3'
		   group by BCK01D,BBY01) b on b.BCK01D = a.BCK01 and b.BBY01 = a.BBY01
		 where a.Quantity > 0.000001 and a.RefQty > 0.000001 and a.ExpiryDate>@adate
		   and EXISTS(select * from @BCKPara c where c.BCK01=a.BCK01 and c.parav='1')
		 ORDER by a.BCK01,a.BBY01,a.ExpiryDate ASC
	   END
	   if exists(select a.DSK01 from @TpVAJTkc a join @BCKPara b on a.BCK01D= b.bck01 
		   where b.parav='2' and isnull(a.DSK01,0)=0 and a.BDN01 >='1' and a.BDN01 <= '3')
	   BEGIN
		 insert into @kbDPT(BCK01,BBY01,DSK01,quantity,LSQty,aflag)
		 select a.BCK01,a.BBY01,a.DSK_ID,a.Quantity,0,0
		 from #kbmDpt a
		 join (select BCK01D,BBY01,sum(vaj25) fvaf21
		   from @TpVAJTkc 
		   where BDN01 >='1' and BDN01 <= '3'
		   group by BCK01D,BBY01) b on b.BCK01D = a.BCK01 and b.BBY01 = a.BBY01
		 where a.Quantity > 0.000001 and a.RefQty > 0.000001 and a.ExpiryDate>@adate
		   and EXISTS(select * from @BCKPara c where c.BCK01=a.BCK01 and c.parav='2')
		 ORDER by a.BCK01,a.BBY01,a.DSK_ID DESC
	   END   
	   --循环查找库存 	   
	   select @minID=min(UID),@maxID=max(UID) from @kbDPT
	   select @minIDb=min(UID),@maxIDb=max(UID) from @TpVAJTkc      
	   while @minIDb<=@maxIDb
	   begin
		 select @BCK01=BCK01D,@BBY01=BBY01,@VAJ25=VAJ25,@BDN01=BDN01 from @TpVAJTkc where UID=@minIDb
		 if @BDN01>='1' and @BDN01<='3'
		 begin
		   set @minIDkc=@minID
		   while @minIDkc<=@maxID and @VAJ25>0.0000001
		   begin
			 if exists(select * from @kbDPT where BCK01=@BCK01 and BBY01=@BBY01 and UID=@minIDkc and aflag=0 and quantity- LSQty>0.00001)
			 begin
			   if exists(select * from @kbDPT where BCK01=@BCK01 and BBY01=@BBY01 and UID=@minIDkc and aflag=0 and ((quantity- LSQty)-@VAJ25>=0)) 
			   begin 
				 insert into @kbDPTkc(UID,BCK01,BBY01,DSK01,quantity,BDN01)
				 select @minIDb,BCK01,BBY01,DSK01,@VAJ25 ,@BDN01
				 from @kbDPT 
				 where UID=@minIDkc

				 update @kbDPT set LSQty=LSQty+ @VAJ25 where UID=@minIDkc
				 set @minIDkc=@maxID
			   end
			   else begin
				 insert into @kbDPTkc(UID,BCK01,BBY01,DSK01,quantity,BDN01)
				 select @minIDb,BCK01,BBY01,DSK01,quantity- LSQty ,@BDN01
				 from @kbDPT 
				 where UID=@minIDkc
             
				 select @VAJ25=@VAJ25-(quantity- LSQty) from @kbDPT where UID=@minIDkc
				 update @kbDPT set LSQty=quantity,aflag=1 where UID=@minIDkc
			   end
			 end  --aflag=0
			 set @minIDkc=@minIDkc+1
		   end
		 end
		 set @minIDb=@minIDb+1
	   end  --end while
   
	   update a 
	   set a.VAJ32 = case when e.BBY19=0 then e.BBY25 else case when b.BCK18 = 1 then c.PurchasePrice else c.PresellPrice end end
		 ,a.BAG23 = case when e.BBY19=0 then d.BAG23 else c.PurchasePrice end
	   from @kbDPTkc a
	   join @TpVAJTkc b on b.UID=a.UID
	   join DPK1 c on c.BCK01 = a.BCK01 and c.BBY01 = a.BBY01 and c.DSK_ID = a.DSK01
	   join BAG1 d with(nolock) on d.BBY01=a.BBY01
	   join BBY1 e with(nolock) on e.BBY01=a.BBY01
	   where a.BDN01 >='1' and a.BDN01 <= '3' 
	 end
	 else BEGIN
	   insert into @kbDPT(BCK01,BBY01,DSK01)
	  select a.BCK01,a.BBY01,min(a.DSK_ID) DSK01
	  from #kbmDpt a
	  join (select BCK01D,BBY01,sum(vaj25) fvaf21
		   from @TpVAJTkc 
		   where BDN01 >='1' and BDN01 <= '3'
		   group by BCK01D,BBY01 ) b on b.BCK01D = a.BCK01 and b.BBY01 = a.BBY01
	  where a.Quantity > 0.000001 and a.RefQty > 0.000001 and a.ExpiryDate>@adate
		and EXISTS(select * from @BCKPara c where c.BCK01=a.BCK01 and c.parav='0')
	  group by a.BCK01,a.BBY01 
  
	  insert into @kbDPT(BCK01,BBY01,ExpiryDate)
	  select a.BCK01,a.BBY01,min(ExpiryDate) ExpiryDate
	  from #kbmDpt a
	  join (select BCK01D,BBY01,sum(vaj25) fvaf21
		   from @TpVAJTkc 
		   where BDN01 >='1' and BDN01 <= '3'
		   group by BCK01D,BBY01) b on b.BCK01D = a.BCK01 and b.BBY01 = a.BBY01
	  where a.Quantity > 0.000001 and a.RefQty > 0.000001 and a.ExpiryDate>@adate
		and EXISTS(select * from @BCKPara c where c.BCK01=a.BCK01 and c.parav='1')
	  group by a.BCK01,a.BBY01 

	  update b set b.DSK01=a.DSK_ID
	  from @kbDPT b
	  join #kbmDpt a on b.BCK01=a.BCK01 and b.BBY01=a.BBY01 and a.ExpiryDate=b.ExpiryDate
	  where a.Quantity > 0.000001 and a.RefQty > 0.000001 and a.ExpiryDate>@adate
		and EXISTS(select * from @BCKPara c where c.BCK01=a.BCK01 and c.parav='1')

	  insert into @kbDPT(BCK01,BBY01,DSK01)
	  select a.BCK01,a.BBY01,max(a.DSK_ID) DSK01
	  from #kbmDpt a
	  join (select BCK01D,BBY01,sum(vaj25) fvaf21
		   from @TpVAJTkc 
		   where BDN01 >='1' and BDN01 <= '3'
		   group by BCK01D,BBY01) b on b.BCK01D = a.BCK01 and b.BBY01 = a.BBY01
	  where a.Quantity > 0.000001 and a.RefQty > 0.000001 and a.ExpiryDate>@adate
		and EXISTS(select * from @BCKPara c where c.BCK01=a.BCK01 and c.parav='2')
	  group by a.BCK01,a.BBY01 
  
	  update a 
	  set a.VAJ32 = case when a.BCK18 = 1 and isnull(b.BAG23,0) > 0.000001 then b.BAG23 else c.BBY25 end ,a.VAJ59 = b.BAG23
		,a.DSK01 = case when a.DSK01>0 then a.DSK01 else isnull(d.DSK01,0) end
	  from @TpVAJTkc a
	  join BAG1 b with(nolock) on b.BBY01 = a.BBY01
	  join BBY1 c with(nolock) on c.BBY01=a.BBY01
	  left join @kbDPT d on d.BCK01 = a.BCK01D and d.BBY01 = a.BBY01    
	  where a.BDN01 >='1' and a.BDN01 <= '3' 

	  update a 
	  set a.VAJ32 = case when a.BCK18 = 1 then c.PurchasePrice else c.PresellPrice end,a.VAJ59 = c.PurchasePrice
	  from @TpVAJTkc a
	  join DPK1 c on c.BCK01 = a.BCK01D and c.BBY01 = a.BBY01 and c.DSK_ID = a.DSK01
	  join BBY1 b with(nolock) on b.BBY01=a.BBY01
	  where a.BDN01 >='1' and a.BDN01 <= '3' and (b.BBY19 = 1 or (b.BBY19 = 0 and a.BCK18 = 1))

	   insert into @kbDPTkc(UID,BCK01,BBY01,DSK01,quantity,VAJ32,BAG23)
	   select UID,BCK01D,BBY01,DSK01,VAJ25,a.VAJ32,a.VAJ59
	   from @TpVAJTkc a  
	   where a.BDN01 >='1' and a.BDN01 <= '3'
	 END

	if @gParam124 = '1'
	begin
	  update a 
	  set a.VAJ32 = c.PresellPrice,  a.VAJ59 =c.PurchasePrice
	  from @TpVAJTkc a
	  join MDK1 c on c.BCK01 = a.BCK01D and c.BBY01 = a.BBY01 and c.MSK_ID = a.DSK01
	  where (a.BDN01 ='4' or  a.BDN01 = 'M') 
	end 
	--定价不要启用拆分参数
	 if @gParam127='1' and exists(select a.DSK01 from @TpVAJTkc a where a.BDN01 in ('4','M'))
	 begin  
	   update @TpVAJTkc set DSK01=0 where BDN01 in ('4','M')
	   select a.* into #kbmMdt
	  from MDT1 a
	  join (select BCK01D,BBY01,sum(vaj25) fvaf21
		   from @TpVAJTkc 
		   where BDN01 in ('4','M')
		   group by BCK01D,BBY01) b on b.BCK01D = a.BCK01 and b.BBY01 = a.BBY01
	  where a.Quantity > 0.000001 and a.RefQty > 0.000001 and a.ExpiryDate>@adate    
	  --增加库存判断
     set @smsg=''
      select @smsg=c.BBY05+' 当前库存'+cast(isnull(b.vaj25,0) as varchar)+' 记账库存'++cast(isnull(a.fvaf21,0) as varchar)
      from (select BCK01D,BBY01,sum(vaj25) fvaf21
       from @TpVAJTkc where BDN01 in ('4','M') group by BCK01D,BBY01) a
	   join BBY1 c with(nolock) on c.BBY01=a.BBY01
	   left join (select BCK01,BBY01,SUM(Quantity) vaj25 from #kbmMdt group by BCK01,BBY01) b on a.BCK01D = b.BCK01 and b.BBY01 = a.BBY01	  
	  where a.fvaf21-isnull(b.vaj25,0)>0.00001
      if @smsg>''
      begin
        set @smsg=replace(isnull(@smsg,''),'%','％')+',库存已不足.' 
        RAISERROR(@smsg, 16, 1) with nowait
       IF Object_id('tempdb..#TmpVAJts') IS NOT NULL DROP TABLE #TmpVAJts
       IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD
       IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI
       IF Object_id('tempdb..#TmpVAI') IS NOT NULL DROP TABLE #TmpVAI
	   IF Object_id('tempdb..#kbmMdt') IS NOT NULL DROP TABLE #kbmMdt
       RETURN 7
     end
	   if exists(select a.DSK01 from @TpVAJTkc a where isnull(a.DSK01,0)=0 and a.BDN01 in ('4','M'))
	   BEGIN
		 insert into @kbMDT(BCK01,BBY01,DSK01,quantity,LSQty,aflag)
		 select a.BCK01,a.BBY01,a.MSK_ID,a.Quantity,0,0
		 from #kbmMdt a
		 join (select BCK01D,BBY01,sum(vaj25) fvaf21
		   from @TpVAJTkc 
		   where BDN01 in ('4','M')
		   group by BCK01D,BBY01) b on b.BCK01D = a.BCK01 and b.BBY01 = a.BBY01
		 where a.Quantity > 0.000001 and a.RefQty > 0.000001 and a.ExpiryDate>@adate
		 ORDER by a.BCK01,a.BBY01,a.MSK_ID ASC     
	   END   
	   --循环查找库存 
	   select @minID=min(UID),@maxID=max(UID) from @kbMDT
	   select @minIDb=min(UID),@maxIDb=max(UID) from @TpVAJTkc
	     
	   while @minIDb<=@maxIDb
	   begin
		 select @BCK01=BCK01D,@BBY01=BBY01,@VAJ25=VAJ25,@BDN01=BDN01 from @TpVAJTkc where UID=@minIDb
		 if @BDN01='4' or @BDN01='M'
		 begin
		   set @minIDkc=@minID
		   while @minIDkc<=@maxID and @VAJ25>0.0000001
		   begin
			 if exists(select * from @kbMDT where BCK01=@BCK01 and BBY01=@BBY01 and UID=@minIDkc and aflag=0 and quantity- LSQty>0.00001)
			 begin
			   if exists(select * from @kbMDT where BCK01=@BCK01 and BBY01=@BBY01 and UID=@minIDkc and aflag=0 and ((quantity- LSQty)-@VAJ25>=0)) 
			   begin 
				 insert into @kbDPTkc(UID,BCK01,BBY01,DSK01,quantity,BDN01)
				 select @minIDb,BCK01,BBY01,DSK01,@VAJ25 ,@BDN01
				 from @kbMDT 
				 where UID=@minIDkc

				 update @kbMDT set LSQty=LSQty+ @VAJ25 where UID=@minIDkc

				 set @minIDkc=@maxID
			   end
			   else begin
				 insert into @kbDPTkc(UID,BCK01,BBY01,DSK01,quantity,BDN01)
				 select @minIDb,BCK01,BBY01,DSK01,quantity- LSQty ,@BDN01
				 from @kbMDT 
				 where UID=@minIDkc
	             
				 select @VAJ25=@VAJ25-(quantity- LSQty) from @kbMDT where UID=@minIDkc

				 update @kbMDT set LSQty=quantity,aflag=1 where UID=@minIDkc
			   end
			 end  --aflag=0
			 set @minIDkc=@minIDkc+1
		   end
		 end
		 set @minIDb=@minIDb+1
	   end  --end while	   
	   update a 
	   set a.VAJ32 = c.PresellPrice,a.BAG23 = c.PurchasePrice
	   from @kbDPTkc a
	   join MDK1 c on c.BCK01 = a.BCK01 and c.BBY01 = a.BBY01 and c.MSK_ID = a.DSK01	  
	   where a.BDN01 in ('4','M')
	 end   --拆分批次完毕
 --把拆分后的数据插入到 	
	Insert Into #TmpVAJls(VAA01,VAA07,Rownr,VAF01,ACF01,BCK01A,BCK01B,BDN01,BBY01,VAJ23,VAJ24,VAJ25
      ,VAJ26,VAJ27,BCK01C,BCE02B,BCE03B,BCK01D,VAJ53,VAJ54,VAJ57,BCK01E,CBM01,ABF01,ABC02,VAJ39
      ,VAJ32,VAJ01A,VAJ34,VAJ35,VAJ59,ABC01,BCK18,VAJ64,BCK01F,BCQ04,VAJ72,BDA01,DSK01,jzVAF27)
    select a.VAA01,a.VAA07,a.Rownr,a.VAF01,a.ACF01,a.BCK01A,a.BCK01B,a.BDN01,a.BBY01,a.VAJ23,a.VAJ24,isnull(b.quantity,a.VAJ25) VAJ25
      ,a.VAJ26,a.VAJ27,a.BCK01C,a.BCE02B,a.BCE03B,a.BCK01D,a.VAJ53,a.VAJ54,a.VAJ57,a.BCK01E,a.CBM01,a.ABF01,a.ABC02,a.VAJ39
      ,isnull(b.VAJ32,a.VAJ32) VAJ32,a.VAJ01A,a.VAJ34,a.VAJ35,isnull(b.BAG23,a.VAJ59) VAJ59,a.ABC01,a.BCK18,a.VAJ64,a.BCK01F,a.BCQ04,a.VAJ72,a.BDA01
	  ,isnull(b.DSK01,a.DSK01) DSK01,a.jzVAF27
    from @TpVAJTKc a
	left join @kbDPTkc b on b.UID=a.UID  
    
    update a
    set a.VAJ30 = 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
        ,a.VAJ31 = 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 
        ,a.VAJ33 = CASE WHEN f1.BBY01 IS NOT NULL  THEN (a.VAJ32*f1.ACV08)/f1.ACV09 
               WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.VAJ32*g1.ACV08)/g1.ACV09 
             ELSE a.VAJ32 END 
       ,a.VAJ36 = a.VAJ25 * a.VAJ32 
       ,a.VAJ37 = CASE WHEN f1.BBY01 IS NOT NULL  THEN (a.VAJ25 * a.VAJ32 * f1.ACV08)/f1.ACV09 
               WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.VAJ25 * a.VAJ32 * g1.ACV08)/g1.ACV09 
             ELSE a.VAJ25 * a.VAJ32 END
       ,a.VAJ38 = CASE WHEN f1.BBY01 IS NOT NULL  THEN (a.VAJ25 * a.VAJ32 * f1.ACV08)/f1.ACV09 
               WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.VAJ25 * a.VAJ32 * g1.ACV08)/g1.ACV09 
             ELSE a.VAJ25 * a.VAJ32 END 
       ,a.VAJ61 = CASE WHEN f1.BBY01 IS NOT NULL  THEN (a.VAJ25 * a.VAJ32 * f1.ACV08)/f1.ACV09 
               WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.VAJ25*a.VAJ32*g1.ACV08)/g1.ACV09 
             ELSE a.VAJ25*a.VAJ32 END                         
    FROM #TmpVAJls a
      JOIN BBY1 c with(nolock) ON a.BBY01 = c.BBY01
      LEFT JOIN (SELECT f.ABC02,f.BBY01,f.ACV06,f.ACV07,f.ACV08,f.ACV09,f.ACV10 
                 FROM ACV1 f with(nolock) 
                 WHERE f.BCH01 IS NULL) AS f1 ON a.ABC02=f1.ABC02 AND a.BBY01=f1.BBY01 and a.ABC01 = 1 
                     AND f1.ACV06<= (a.VAJ32*a.VAJ25) AND f1.ACV07>=(a.VAJ32*a.VAJ25)    
      LEFT JOIN (SELECT g.ABC02,g.BCH01,g.ACV06,g.ACV07,g.ACV08,g.ACV09,g.ACV10 
                 FROM ACV1 g with(nolock) 
                 WHERE g.BBY01 IS NULL) AS g1 ON a.ABC02=g1.ABC02 AND c.BCH01=g1.BCH01 and a.ABC01 = 1 
                     AND NOT EXISTS(SELECT g2.BBY01 
                                    FROM ACV1 g2 with(nolock) 
                                    WHERE g2.BCH01 IS NULL AND a.ABC02=g2.ABC02 AND a.BBY01=g2.BBY01)
                     AND g1.ACV06<= (a.VAJ32*a.VAJ25) AND g1.ACV07>=(a.VAJ32*a.VAJ25)  
  --   
  insert into #tmpVAI(VAA01,VAA07,BCK01B,BCE02,BCE03,CBM01,ACF01,VAI22,VAI23,BCE01C,BCE03C)                   
  select b.VAA01,b.VAA07,b.BCK01C,c.BCE02,c.BCE03,a.CBM01,2,a.FVAJ36,a.FVAJ38,e.BCE01,e.BCE03
  from (select e.CBM01,(e.FVAJ36 - ISNULL(f.FVAJ36,0)) FVAJ36 ,(e.FVAJ38 - ISNULL(f.FVAJ38,0)) FVAJ38
        from (select CBM01,SUM(vaj36) FVAJ36,SUM(vaj38) FVAJ38 from #TmpVAJls group by CBM01) e
        left join (select m.CBM01,SUM(m.vaj36) FVAJ36,SUM(m.vaj38) FVAJ38 
                   from #TmpVAJls m 
                   where exists(select * from @TmpVAFcz n where n.VAF01 = m.VAF01 and n.BBX17 = 3)
                   group by m.CBM01) f on f.CBM01 = e.CBM01 )a
  join CBM2 b with(nolock) on b.CBM01 = a.CBM01
  join VAE1 d with(nolock) on d.VAE01 = b.VAA07
  left join BCE1 c with(nolock) on c.BCE01 = b.BCE01
  left join BCE1 e with(nolock) on e.BCE02 = d.BCE02C
  
  Select @aVAIMax = Max(ID) From #tmpVAI
  set @aVAIMax = ISNULL(@aVAIMax,0)
  Exec Core_NewIDEX 'VAI1','VAI01',@aVAIid out, @aVAIMax
  
  Set @aVAIid = @aVAIid - @aVAIMax
      
  Update #tmpVAI
  Set VAI01 = @aVAIid + ID, VAI04 = 'CZ'+substring(@aStr,1,10 -len(@aVAIid + ID))+cast((@aVAIid + ID) AS varchar)	
  
  update a set a.VAI01 = b.VAI01,a.BCE01E=b.BCE01C,a.BCE03E = b.BCE03C
  from #TmpVAJls a
  join #tmpVAI b on b.CBM01 = a.CBM01
  
  update a set a.VAI01 = b.VAI01
  from @TmpVAFcz a
  join #tmpVAI b on b.CBM01 = a.CBM01
  
    update a set a.BCK01F = c.BCK01A,a.BCQ04 = b.BCQ04B
    From #TmpVAJls a
    join VAE1 b with(nolock) on b.VAE01 = a.VAA07      
    join BCQ1 c with(nolock) on c.VAA01 = b.VAA01 and c.BCQ04 = b.BCQ04B and c.BCQ13 in (1,3)
    
    if Exists(Select * from #TmpVAJls where isnull(VAI01,0)=0)
    begin
      --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz
      IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA
      IF Object_id('tempdb..#TmpVAJls') IS NOT NULL DROP TABLE #TmpVAJls
      IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD
      IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI
      RAISERROR('费用明细中单据ID不能为空值.', 16, 1) with nowait      
      RETURN 5
    end
   select @ReVAIid = case when @ReVAIid = '' then cast(VAI01 as varchar) else @ReVAIid+','+cast(VAI01 as varchar) end 
   from #tmpVAI  where isnull(VAI01,'')<>''
   
   select @ReVafid_hz = case when @ReVafid_hz = '' then cast(a.VAF01 as varchar) else @ReVafid_hz+','+cast(a.VAF01 as varchar) end 
   from @TmpVAFcz a
   join bbx1 b with(nolock) on b.BBX01=a.BBX01
   where a.BDA01='Z' and b.BBX13 ='7'
   
   select @ReVafid_zk = case when @ReVafid_zk = '' then cast(a.VAF01 as varchar) else @ReVafid_zk+','+cast(a.VAF01 as varchar) end 
   from @TmpVAFcz a
   join bbx1 b with(nolock) on b.BBX01=a.BBX01
   where a.BDA01='Z' and b.BBX13 ='5'
   
  Select @aVAIid = 0, @aVAIMax = 0

  Select @aVAIMax = Max(ID) From #TmpVAJls
  set @aVAIMax = ISNULL(@aVAIMax,0)
  Exec Core_NewIDEX 'VAJ1','VAJ01',@aVAIid out, @aVAIMax
  Set @aVAIid = @aVAIid - @aVAIMax
  
  Update #TmpVAJls Set VAJ01 = @aVAIid + ID,BAG46 = 0
  
  Select @aVAIid = 0, @aVAIMax = 0
  Select @aVAIMax = Max(ID) From @TmpVAFcz
  set @aVAIMax = ISNULL(@aVAIMax,0)
  Exec Core_NewIDEX 'VBG1','VBG01',@aVAIid out, @aVAIMax
  Set @aVAIid = @aVAIid - @aVAIMax
  Update @TmpVAFcz Set VBG01 = @aVAIid + ID 
  
  update a
  SET a.VAF10 = 9, a.BCE03E =case when a.BCE01E>0 then a.BCE03E else @lBCE03 end
    ,a.BCE01E =case when a.BCE01E > 0 then a.BCE01E else @lBCE01 end
    ,a.BCE03F = @lBCE03,a.BCE01F = @lBCE01, a.VAF50 = case when @lOneDay = 2 then @lEndDate else @curDate end
  from @TmpVAFcz a
  join #tmpBDA b on b.VAF01 = a.VAF01
  where b.IsFirst = 2  

  update a
  SET a.VAF10 = 8,a.aVAF38 = a.VAF37 ,a.BCE03D = a.BCE03A,a.BCE01D = a.BCE01A,a.BCE03E = @lBCE03,a.BCE01E = @lBCE01
    ,a.VAF47 = case when @curDate > a.VAF37 then @curDate else a.VAF37 end
  from @TmpVAFcz a
  join #tmpBDA b on b.VAF01 = a.VAF01
  where b.IsFirst <= 1 and datediff(day,a.vaf36,a.vaf37) >= 0 AND a.VAF37 <= b.VAF38
  
  update a
  SET a.VAF10 = 3,a.aVAF38 = b.VAF38
  from @TmpVAFcz a
  join #tmpBDA b on b.VAF01 = a.VAF01
  where b.IsFirst <= 1 and ((datediff(day,a.vaf36,a.vaf37) < 0)
          OR(datediff(day,a.vaf36,a.vaf37) >= 0 AND a.VAF37 > b.VAF38))  
  --******  
  insert into #tmpVBI(VBI05,VBI06,VBI07,VBI10,VBI11,BCK01,VAF01,VAA01,VAA07,VAP01,VBI13)
  select a.VAI01,a.Rownr,case when a.BDA01 >= 'A' and a.VAF32 = 0 then a.VAF21 
                           else case when b.IsFirst = 2 then a.VAF62 
                             when b.IsFirst = 1 and (a.bda01 in ('1','2') or (a.bda01 = 'T' and a.vaf32=1)) then a.VAF61 else a.VAF27 end  end
    ,case when b.IsFirst = 2 then b.VAF36 
       else case when DateDiff(Day,b.VAF36,b.VAF38) > 0 then  Convert(varchar(10),b.VAF38,21)+@para414
               else b.VAF36 end end
    ,b.VAF38,a.BCK01B,a.VAF01,a.VAA01,a.VAA07,a.VAP01,a.VBI13
  from @TmpVAFcz a
  join #tmpBDA b on b.VAF01 = a.VAF01
  where ((b.IsFirst = 2 and a.VAF62 > 0.00001) or (b.IsFirst <= 1 and b.FDay <= 1)) and a.BBX17 <> 9
    
  set @mDay = ISNULL((select MAX(b.Fday) from @TmpVAFcz a
                        join #tmpBDA b on b.VAF01 = a.VAF01
                       where b.IsFirst <= 1 and b.FDay >= 2),0)                  
  if @mDay >= 2
  begin
    set @anum = 1
    while @anum <= @mDay
    begin    
      insert into #tmpVBI(VBI05,VBI06,VBI07,VBI10,VBI11,BCK01,VAF01,VAA01,VAA07,VAP01,VBI13)
      select a.VAI01,a.Rownr,case when a.BDA01 >= 'A' and a.VAF32 = 0 then a.VAF21 else case when b.IsFirst = 1 and @anum = 1 then a.VAF61 else a.VAF27 end end
        ,case when @anum > 1 then (case when a.VAF29 <> 'D' OR (a.VAF29 = 'D' and a.VAF28 <= 1 and b.VEH07<='') then Convert(varchar(10),(b.VAF36 + (@anum - 1)),21)+@para414
                                    else case when b.VEH07>'' then (select Convert(varchar(10),s1.fvaf36,21)+@para414 from dbo.GetBDI1_BDI14(b.VAF01,b.VEH07,b.VAF36,b.VAF38,1) s1 where s1.fday=@anum )
                                                     else Convert(varchar(10),(b.VAF36 + (@anum - 1)* a.VAF28),21)+@para414 end end )
              else b.VAF36 end
        ,Convert(varchar(10),(case when @anum > 1 then case when a.VAF29 <> 'D' OR (a.VAF29 = 'D' and a.VAF28 <= 1 and b.VEH07<='') then Convert(varchar(10),(b.VAF36 + (@anum - 1)),21)+@para414
                                    else case when b.VEH07>'' then (select Convert(varchar(10),s1.fvaf36,21)+@para414 from dbo.GetBDI1_BDI14(b.VAF01,b.VEH07,b.VAF36,b.VAF38,1) s1 where s1.fday=@anum )
                                                     else Convert(varchar(10),(b.VAF36 + (@anum - 1)* a.VAF28),21)+@para414 end end 
              else b.VAF36 end),21)+' 23:59:00'  
        ,a.BCK01B,a.VAF01,a.VAA01,a.VAA07,a.VAP01,a.VBI13
      from @TmpVAFcz a
      join #tmpBDA b on b.VAF01 = a.VAF01
      where (b.IsFirst <= 1 and b.FDay >= 2 and b.FDay >= @anum) and a.BBX17 <> 9 
      
      set @anum = @anum + 1
    end
  end 

  Select @aVAIid = 0, @aVAIMax = 0
  Select @aVAIMax = Max(ID) From #tmpVBI
  set @aVAIMax = ISNULL(@aVAIMax,0)
  Exec Core_NewIDEX 'VBI1','VBI01',@aVAIid out, @aVAIMax
  Set @aVAIid = @aVAIid - @aVAIMax
  Update #tmpVBI Set VBI01 = @aVAIid + ID   
  --***
  declare @kbmVAT table(ID int IDENTITY(1,1),VAT01 int,VAF01 int)
  Declare @tmpVAV Table(ID int IDENTITY(1,1),VAT01 int,VAV01 int,VAV05 varchar(128),BBX01 int,VAV03 tinyint,VAV04 tinyint,VAV09 tinyint,ACI02 varchar(30))  
  if exists(SELECT a.VAF01 
            FROM @TmpVAFcz a
            JOIN BAZ1 b with(nolock) ON a.BCK01B = b.BCK01
            WHERE a.BDA01 = 'S' and a.BBX17 <> 9 and a.VAF01A = 0 AND b.BAU01 = '03' AND b.ACF01 IN (2,3))
  begin
    insert into @kbmVAT(VAF01)
    select a.VAF01
    FROM @TmpVAFcz a
    WHERE a.BDA01 = 'S' and a.VAF01A = 0 and a.BBX17 <> 9
     and exists(select * from BAZ1 b with(nolock) where b.BCK01 = a.BCK01B AND b.BAU01 = '03' AND b.ACF01 IN (2,3) )    
    
    Select @aVAIid = 0, @aVAIMax = 0
    Select @aVAIMax = Max(ID) From @kbmVAT
    set @aVAIMax = ISNULL(@aVAIMax,0)
    EXEC Core_NewIDEx 'CBM1','CBM01',@aVAIid out, @aVAIMax
    Set @aVAIid = @aVAIid - @aVAIMax
    Update @kbmVAT Set VAT01 = @aVAIid + ID
    
    insert into @tmpVAV(VAT01,VAV05,BBX01,VAV03,VAV04,VAV09,ACI02)
    select a.VAT01,c.BBX05,b.BBX01,1,0,0,''
    from @kbmVAT a
    join @TmpVAFcz b on b.VAF01 = a.VAF01
    join BBX1 c with(nolock) on c.BBX01 = b.BBX01
    where b.BDA01 = 'S'
    union all
    select a.VAT01,c.BBX05,b.BBX01,0,0,0,''
    from @kbmVAT a
    join @TmpVAFcz b on b.VAF01A = a.VAF01
    join BBX1 c with(nolock) on c.BBX01 = b.BBX01
    where b.VAF01A > 0 and c.BDA01 ='S' and b.VAF15<=''
    union all
    select a.VAT01,c.BBX05,b.BBX01,0,0,1,d.ACI02
    from @kbmVAT a
    join @TmpVAFcz b on b.VAF01A = a.VAF01
    join BBX1 c with(nolock) on c.BBX01 = b.BBX01
    left join ACI1 d with(nolock) on d.ACI01 = c.BBX13
    where b.VAF01A > 0 and c.BDA01 ='A'
    
    Select @aVAIid = 0, @aVAIMax = 0  
    Select @aVAIMax = MAX(ID) From @tmpVAV
    EXEC Core_NewIDEx 'VAV1','VAV01',@aVAIid out, @aVAIMax
    Set @aVAIid = @aVAIid - @aVAIMax
    UPDATE @tmpVAV set VAV01 = @aVAIid + ID
  end     
  --*****
  insert into @tmpDPT(BCK01,BBY01,DSK01,VAJ25)
  select a.BCK01D,a.BBY01,a.DSK01,SUM(a.VAJ25)
  from #TmpVAJls a 
  where a.BDN01 >= '1' and a.BDN01 <= '3'
  group by a.BCK01D,a.BBY01,a.DSK01 

  if @para241=1
  begin
	  insert into @tmpMDT(BCK01,BBY01,DSK01,VAJ25)
	  select a.BCK01D,a.BBY01,a.DSK01,SUM(a.VAJ25)
	  from #TmpVAJls a 
	  where a.BDN01 in ( '4','M')  
	  group by a.BCK01D,a.BBY01,a.DSK01 
  end              
  --判断已发送医嘱
  if Exists(select a.VAF01 FROM @TmpVAFcz a, VAF2 b WHERE a.VAF01=b.VAF01 AND b.VAF10 <> 3 and @lOneDay <= 1)
  begin
    --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz
    IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA
    IF Object_id('tempdb..#TmpVAJls') IS NOT NULL DROP TABLE #TmpVAJls
    IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD
    IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI
    RAISERROR('已有医嘱被作废或停止，请返回重新选择过滤条件，在发送医嘱！', 16, 1) with nowait
    RETURN 2
  end
  
  if exists(select a.VAF01 from @TmpVAFcz a join VAF2 b on b.VAF01 = a.VAF01 where DATEDIFF(day,@lEndDate,b.VAF38) >= 0 and @lOneDay <= 1)
  begin
   -- IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz
    IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA
    IF Object_id('tempdb..#TmpVAJls') IS NOT NULL DROP TABLE #TmpVAJls
    IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD
    IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI
    RAISERROR('已有医嘱被作废或已执行，请返回重新选择过滤条件，再发送医嘱！', 16, 1) with nowait
    RETURN 3
  end
  --药品如果禁售，就不允许在发药
  if exists(select a.BBY01 from #tmpVBD a join BAL1 b with(nolock) on b.BCK01 = a.BCK01 and b.BBY01 = a.BBY01 where b.BAL08 = 1)
  begin
    set @smsg = (select top 1 c.BBY05 from #tmpVBD a join BAL1 b with(nolock) on b.BCK01 = a.BCK01 and b.BBY01 = a.BBY01 join BBY1 c on a.BBY01 = c.BBY01 where b.BAL08 = 1)
    --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz
    IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA
    IF Object_id('tempdb..#TmpVAJls') IS NOT NULL DROP TABLE #TmpVAJls
    IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD
    IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI
    set @smsg = '【'+@smsg+'】药品药房已经禁售.'
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 4
  end
  --记录虚存日志
  if @cfxz202=1
  begin
    select c.BBY01,c.BCK01,d.RefQty,d.SellQty,d.LSQty,c.VAJ25,0 DSK_ID,0 PurchasePrice,0 PresellPrice
    into #tmpDpt1log
    from (select a.BCK01D BCK01,a.BBY01,sum(a.VAJ25) VAJ25
        from #TmpVAJls a
        where a.BDN01 >= '1' and a.BDN01 <= '3' 
        group by a.BCK01D,a.BBY01 ) c
    left join (select BCK01,BBY01,SUM(Quantity) LSQty,sum(RefQty) RefQty,sum(SellQty) SellQty
        from DPT1
        group by BCK01,BBY01) d on c.BCK01 = d.BCK01 and c.BBY01 = d.BBY01
    union all
    select a.BBY01,b.BCK01,b.RefQty,b.SellQty,b.Quantity,a.vaj25,a.DSK01,b.PurchasePrice,b.PresellPrice
    from #TmpVAJls a
    left join DPT1 b on b.BBY01 = a.BBY01 and b.BCK01 = a.BCK01D and a.DSK01 = b.DSK_ID
    where a.BDN01 >= '1' and a.BDN01 <= '3' and a.DSK01 > 0 
  end
  Declare @aVAF38 numeric(18,4),@VAA01 int,@VAA07 int
  declare @gpara69 varchar(10),@vaj47 datetime
  set @VAA01 = ISNULL((select top 1 VAA01 from #tmpVAI),0)
  set @VAA07 = ISNULL((select top 1 VAA07 from #tmpVAI),0)
  set @aVAF38 = (select SUM(VAI23) FVAI23 from #tmpVAI  a  	                  
				   where not exists( select * from
	                   #TmpVAJls b 
					   join @TmpVAFcz c on b.CBM01 = c.CBM01 And b.VAF01 = c.VAF01
				        where a.VAI01 = b.VAI01 and c.VAF58=5)
				   )    

  update b
  SET b.VEH09= dateadd(day,floor((a.VAJ25 * d.BAG03)/(b.VAF18 * b.VAF27)),c.VAF36 )
  from @TmpVAFcz b
  join #tmpBDA c on b.VAF01 = c.VAF01
  join #TmpVAJls a on b.VAF01 = a.VAF01  
  join BAG1 d with(nolock) on d.BBY01=a.BBY01
  LEFT join VEH2 f on f.VAF01 = a.VAF01
  where b.BDA01 >='1' and b.BDA01 <= '2' and b.VAF75=1 and b.VAF34 = 0 and b.VAF10=3 

  update a set a.VEG04 = b.VEH02
  from #TmpVAJls a
  LEFT join VEH2 b on b.VAF01 = a.VAF01
  update a set a.VAJ53=2
  from #TmpVAJls a
  join (select c.BBY01 from blp1 b with(nolock) join bby1 c with(nolock) on c.BBY01=b.blp03
        where b.blp02=93 and b.blp03>'') d on d.bby01=a.bby01
  where a.BDN01<='3'         
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,VAI22,VAI23,BCE01C,BCE03C)
  SELECT VAI01,VAA01,VAA07,VAI04,'住院长嘱发送' ,0,BCK01B,BCE02,BCE03,@lBCE02,@lBCE03,@curDate
      ,@curDate, '住院长嘱发送' ,0,1,3,@lSign,CBM01,ACF01,VAI22,VAI23,BCE01C,BCE03C
  FROM #tmpVAI       
  
  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,BCE03A,BCK01C,BCE02B,BCE03B,VAJ46
      ,VAJ47,VAJ48,BCK01D,BCE02C,BCE03C,VAJ51,VAJ52,VAJ53,VAJ54,BCE02D,BCE03D,VAJ57,VAJ59,VAJ61,VAJ62,BCK01E,VAJ39,VAJ01A,DSK01,FAB03,VAJ64
      ,VAJ67,BCE01E,BCE03E,BCK01F,BCQ04,VAJ72) 
  SELECT a.VAJ01,a.VAA01,a.VAA07,@lSign,1,a.ROWNR,0,a.VAI01,a.VAF01,0,a.ACF01,0,a.BCK01A,a.BCK01B
      ,a.BDN01,a.BBY01,null,0,0,a.VAJ23,a.VAJ24,a.VAJ25, a.VAJ26,a.VAJ27,a.VAJ30 ,a.VAJ31
      ,a.VAJ32,a.VAJ33,a.VAJ34,a.VAJ35,a.VAJ36,a.VAJ37,a.VAJ38 ,a.BCE03B,a.BCK01C,a.BCE02B,a.BCE03B,@curdate
      ,@curDate,0,a.BCK01D,case when a.VAJ53=1 then @lBCE02 else null end BCE02C
         ,case when a.VAJ53=1 then @lBCE03 else null end BCE03C,case when a.VAJ53=1 then a.VAJ64 else null end VAJ51
         ,case when a.VAJ53=1 then @curDate else null end VAJ52,a.VAJ53
      ,a.VAJ54,@lBCE02,@lBCE03,a.VAJ57,a.VAJ59,a.VAJ61,@curDate, a.BCK01E,a.VAJ39,a.VAJ01A,a.DSK01,FAB03,a.VAJ64
      ,a.VAJ32,a.BCE01E,a.BCE03E,a.BCK01F,a.BCQ04,a.VAJ72
  FROM #TmpVAJls a
  where exists(select * from @TmpVAFcz b where b.VAF01 = a.VAF01 and b.BBX17 < 3 and VAF58 <> 5)

insert into DTC1 (VAJ01,DTB01,VAA01,VAA07,VAJ04,VAJ05,VAJ09,VAI01,VAF01,BDN01,ROWNR,BBY01,VAJ23,VAJ24,VAJ25,VAJ32,VAJ33
       ,VAJ34,VAJ35,VAJ36,VAJ37,VAJ38,BCK01A,BCK01B,BCK01C,BCK01D
       ,BCK01E,BCE03A,BCE02B,BCE03B,BCE03C,VAJ46,VAJ47,VAJ48,VAJ51,VAJ53,VAJ54)
SELECT  b.VAJ01,0,b.VAA01,b.VAA07,b.VAJ04,b.VAJ05,b.VAJ09,b.VAI01,b.VAF01,b.BDN01,b.ROWNR,b.BBY01,b.VAJ23,b.VAJ24,b.VAJ25
       ,b.VAJ32,b.VAJ33,b.VAJ34,b.VAJ35,b.VAJ36,b.VAJ37,b.VAJ38,b.BCK01A
       ,b.BCK01B,b.BCK01C,b.BCK01D,b.BCK01E,b.BCE03A,b.BCE02B,b.BCE03B,b.BCE03C,b.VAJ46,b.VAJ47,b.VAJ48,b.VAJ51,b.VAJ53,b.VAJ54 
from #TmpVAJls a
join VAJ2 b on  a.VAJ01= b.VAJ01
where a.BDN01<='3'

  --VAF58外配处方
  INSERT INTO VAJ9(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,BCE03A,BCK01C,BCE02B,BCE03B,VAJ46
      ,VAJ47,VAJ48,BCK01D,BCE02C,BCE03C,VAJ51,VAJ52,VAJ53,VAJ54,BCE02D,BCE03D,VAJ57,VAJ59,VAJ61,VAJ62,BCK01E,VAJ39,VAJ01A,DSK01,FAB03,VAJ64
      ,VAJ67,BCE01E,BCE03E,BCK01F,BCQ04,VAJ72) 
  SELECT a.VAJ01,a.VAA01,a.VAA07,@lSign,1,a.ROWNR,0,a.VAI01,a.VAF01,0,a.ACF01,0,a.BCK01A,a.BCK01B
      ,a.BDN01,a.BBY01,null,0,90,a.VAJ23,a.VAJ24,a.VAJ25, a.VAJ26,a.VAJ27,a.VAJ30 ,a.VAJ31
      ,a.VAJ32,a.VAJ33,a.VAJ34,a.VAJ35,a.VAJ36,a.VAJ37,a.VAJ38 ,a.BCE03B,a.BCK01C,a.BCE02B,a.BCE03B,@curdate
      ,@curDate,0,a.BCK01D,case when a.VAJ53=1 then @lBCE02 else null end BCE02C
         ,case when a.VAJ53=1 then @lBCE03 else null end BCE03C,case when a.VAJ53=1 then a.VAJ64 else null end VAJ51
         ,case when a.VAJ53=1 then @curDate else null end VAJ52,a.VAJ53
      ,a.VAJ54,@lBCE02,@lBCE03,a.VAJ57,a.VAJ59,a.VAJ61,@curDate, a.BCK01E,a.VAJ39,a.VAJ01A,a.DSK01,FAB03,a.VAJ64
      ,a.VAJ32,a.BCE01E,a.BCE03E,a.BCK01F,a.BCQ04,a.VAJ72
  FROM #TmpVAJls a
  where exists(select * from @TmpVAFcz b where b.VAF01 = a.VAF01 and b.BBX17 < 3 and VAF58 = 5)
  
  INSERT INTO VAJT(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,BCE03A,BCK01C,BCE02B,BCE03B,VAJ46
      ,VAJ47,VAJ48,BCK01D,BCE03C,VAJ51,VAJ52,VAJ53,VAJ54,BCE02D,BCE03D,VAJ57,VAJ59,VAJ61,VAJ62,BCK01E,VAJ39,VAJ01A,DSK01,FAB03,VAJ64
      ,VAJ67,BCE01E,BCE03E,BCK01F,BCQ04,VAJ72) 
  SELECT a.VAJ01,a.VAA01,a.VAA07,@lSign,1,a.ROWNR,0,a.VAI01,a.VAF01,0,a.ACF01,0,a.BCK01A,a.BCK01B
      ,a.BDN01,a.BBY01,null,0,0,a.VAJ23,a.VAJ24,a.VAJ25, a.VAJ26,a.VAJ27,a.VAJ30 ,a.VAJ31
      ,a.VAJ32,a.VAJ33,a.VAJ34,a.VAJ35,a.VAJ36,a.VAJ37,a.VAJ38 ,a.BCE03B,a.BCK01C,a.BCE02B,a.BCE03B,@curDate
      ,@curDate,0,a.BCK01D,null,null,null,0,a.VAJ54,@lBCE02,@lBCE03,a.VAJ57,a.VAJ59,a.VAJ61,@curDate, a.BCK01E,a.VAJ39,a.VAJ01A,a.DSK01,FAB03,a.VAJ64
      ,a.VAJ32,a.BCE01E,a.BCE03E,a.BCK01F,a.BCQ04,a.VAJ72
  FROM #TmpVAJls a
  where exists(select * from @TmpVAFcz b where b.VAF01 = a.VAF01 and b.BBX17 = 3)
  
  if @@ERROR <> 0
  begin
    RollBack tran
    --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz
    IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA
    IF Object_id('tempdb..#TmpVAJls') IS NOT NULL DROP TABLE #TmpVAJls
    IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD
    IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI
    RAISERROR('插入费用记录时错误.', 16, 1) with nowait
    return 2
  end 
  insert into VEG2(VAJ01,VEG02,VEG03,VEG04,VEG12,VEG14) 
  select VAJ01,0,VAJ25,VEG04,VAJ25,jzVAF27
  from #TmpVAJls
--******
--获取参数  自动拆零药品第一次执行后，系统不在自动计算用药时间
declare  @para475 int
set @para475 = dbo.GetSysParamValue(100,105001,475)
if @para475=1 
begin
  update a
  SET a.VAF10 = b.VAF10, a.BCE03E = b.BCE03E,a.BCE01E = b.BCE01E,a.BCE03F = b.BCE03F,a.BCE01F = b.BCE01F, a.VAF50 = b.VAF50, a.VAF62 = b.VAF62
     ,a.VAF38 = b.aVAF38,a.VAF47 = b.VAF47,a.BCE01D = b.BCE01D,a.BCE03D = b.BCE03D
     ,a.VAF34 = case when b.BDA01 >='1' and b.BDA01 <= '2' and b.VAF75=1 and b.VAF34 = 0  and isnull(b.VAF38,'1899-12-30')<=b.VAF36  then 1 else a.VAF34 end
     ,a.VAF33 = case when b.VAF10 = 8 or a.VAF33=2 then 2 else 1 end
  from VAF2 a
  join @TmpVAFcz b on b.VAF01 = a.VAF01
end
else
begin
  update a
  SET a.VAF10 = b.VAF10, a.BCE03E = b.BCE03E,a.BCE01E = b.BCE01E,a.BCE03F = b.BCE03F,a.BCE01F = b.BCE01F, a.VAF50 = b.VAF50, a.VAF62 = b.VAF62
     ,a.VAF38 = b.aVAF38,a.VAF47 = b.VAF47,a.BCE01D = b.BCE01D,a.BCE03D = b.BCE03D
     ,a.VAF34 = case when b.BDA01 >='1' and b.BDA01 <= '2' and b.VAF75=1 and b.VAF34 = 0 then 1 else a.VAF34 end
     ,a.VAF33 = case when b.VAF10 = 8 or a.VAF33=2 then 2 else 1 end
  from VAF2 a
  join @TmpVAFcz b on b.VAF01 = a.VAF01
end

  update a
  SET a.VEH09=b.VEH09
  from VEH2 a
  join @TmpVAFcz b on b.VAF01 = a.VAF01
  where b.BDA01 >='1' and b.BDA01 <= '2' and b.VAF75=1 and b.VAF34 = 0 and b.VAF10=3
  
  if @@ERROR <> 0
  begin
    RollBack tran
   -- IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz
    IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA
    IF Object_id('tempdb..#TmpVAJls') IS NOT NULL DROP TABLE #TmpVAJls
    IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD
    IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI
    RAISERROR('修改医嘱记录时错误.', 16, 1) with nowait
    return 4
  end    
  
  INSERT INTO VBI2(VBI01,VAF01,VBI03,VBI04,VBI05,VBI06,VBI07,BCE03A,VBI09,VBI10,VBI11,VBI13,BCK01,VBI15,BCE01A
    ,VBI29,ACF01,VAA01,VAA07,VAP01,VBI35,VBI12,VBI24)
  SELECT VBI01,VAF01,VBI01,@lSign,VBI05,VBI06,VBI07,@lBCE03,@curDate,VBI10,VBI11,VBI13,BCK01,2,@lBCE01
    ,0,2,VAA01,VAA07,VAP01,VBI07,case when VBI13=1 then VBI10 else null end VBI12
     ,case when VBI13=1 then '发送医嘱自动执行,操作人：'+@lbce03 else '' end VBI24
  FROM #tmpVBI
  
  if @@ERROR <> 0
  begin
    RollBack tran
   -- IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz
    IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA
    IF Object_id('tempdb..#TmpVAJls') IS NOT NULL DROP TABLE #TmpVAJls
    IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD
    IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI
    RAISERROR('插入执行记录时错误.', 16, 1) with nowait
    return 5
  end
  
  insert into VBG1(VBG01,VAF01,VBG03,BCE03,VBG05,VBG06)
  select VBG01,VAF01,VAF10,@lBCE03,@curDate,case when VAF10 = 3 then '长嘱执行' when VAF10 = 8 then '长嘱执行自动停止' when VAF10 = 9 then '长嘱确认停嘱' end
  from @TmpVAFcz
  
  declare @VAE01 int,@lAAG01 int
  select @VAE01 = VAA07 from @TmpVAFcz
  
  if exists(select a.* 
            from @TmpVAFcz d
            join VBD2 a on a.VAF01 = d.VAF01 and  isnull(a.DFlag,0)=0
            join BBY1 b on b.BBY01 = a.BBY01
            join AAG1 c on c.AAG01 = b.BCF01              
            where d.BDA01 = 'N' and d.BBX17 <> 9 and d.VAF01A = 0 and b.BDN01='N' and c.AAG01<=7 and d.VAP01 = 0   )
  begin
    set @lAAG01 =(select top 1 a.BBY01 
                  from @TmpVAFcz d
                  join VBD2 a on a.VAF01 = d.VAF01 and  isnull(a.DFlag,0)=0
                  join BBY1 b on b.BBY01 = a.BBY01
                  join AAG1 c on c.AAG01 = b.BCF01              
                  where d.BDA01 = 'N' and d.BBX17 <> 9 and d.VAF01A = 0 and b.BDN01='N' and c.AAG01<=7 and d.VAP01 = 0  
                  order by d.VAF36 desc)		
				
    if not exists(select * from VAE1 where AAG01=@lAAG01 and VAE01= @VAE01)
    BEGIN	
	  if @lOneDay<>2 
	  begin
	    update VAE1 set AAG01=@lAAG01 where VAE01 = @VAE01
	  end					     		
      UPDATE VBO1 
      SET BCE03E = @lBCE03,VBO19 = @curDate,VBO20 = @curDate,VBO21 = 6
      WHERE VAA07 = @VAE01 AND isnull(VBO21,0)=0 AND VBO19 IS NULL	
				
      EXEC Core_NewIDEx 'VBO1','VBO01',@aVAIid out
      INSERT INTO VBO1
        (VBO01,VAA01,VAA07,VBO04,VBO05,VBO06,BCK01A,BCK01B,BBY01A,BBY01B,BCQ04
         ,BCE03A,BCE03B,ABO01,BCE01,BCE03F,VBO21)        
      SELECT @aVAIid,a.VAA01,a.VAE01,@curDate,@curDate,6,a.BCK01C,a.BCK01D,a.AAG01,b.BBY01,a.BCQ04B
         ,a.BCE03B,a.BCE03C,a.ABO01,@lBCE01,@lBCE03,0
      FROM VAE1 a
      JOIN BCQ1 b ON a.BCQ04B = b.BCQ04	and b.VAA01=a.VAA01 and a.BCK01C=b.BCK01A		
      WHERE a.VAE01 = @VAE01 		
    END
  end  

  if not exists(select * from BLP1 where BLP02=140) 
  begin
    update c
    set c.VAE22 =1
    from VAE1 c
    where exists(select * from (
      select a.VAA07 from @TmpVAFcz a join BBX1 b on b.BBX01 = a.BBX01 where a.BDA01='Z' and b.BBX13='9'
      union all
      select VAA07 from @TmpVAFcz where (PATINDEX('病危',VAF22) > 0 or PATINDEX('危重',VAF22) > 0)
      ) s where s.VAA07 = c.VAE01)
      
    update c
    set c.VAE22 = 2
    from VAE1 c
    where exists(select * from (
      select VAA07 from @TmpVAFcz where PATINDEX('病重',VAF22) > 0 
      ) s where s.VAA07 = c.VAE01)
  end  

  --发送手术医嘱到手术记录表
  if exists(SELECT a.* 
            FROM @TmpVAFcz a
            JOIN BAZ1 b ON a.BCK01B = b.BCK01
            WHERE a.BDA01 = 'S' and a.BBX17 <> 9 and a.VAF01A = 0 AND b.BAU01 = '03' AND b.ACF01 IN (2,3))
  begin    
    INSERT INTO CBM2
      (CBM01,VAA01,VAA07,VAP01,ACF01,CBM06,CBM07,CBM08,CBM09,BCK01A,BCK01B,BCK01C,BCE01,BCE03,CBM15,CBM16)
    SELECT d.VAT01,a.VAA01,a.VAA07,a.VAP01,a.ACF01,3,99,d.VAT01,'手术记录单',c.BCK01C,a.BCK01A,a.BCK01C,b.BCE01,b.BCE03,@curDate,a.VAF36
    FROM @kbmVAT d
    join @TmpVAFcz a on a.VAF01 = d.VAF01
    JOIN BCE1 b ON b.BCE02 = a.BCE02A
    JOIN VAE1 c ON c.VAE01 = a.VAA07			
  
    INSERT INTO VAT1(VAT01, VAA01, VAA07, VAT04, ACF01, VAF01, VAT08
       ,VAT16, BBX01, ACI02, BCK01,VAT33,AAB01,ACH01)
    SELECT d.VAT01,a.VAA01,a.VAa07,0,a.ACF01,a.VAF01
      ,case when isnull(a.vaf14,'')=''  or a.vaf14='0000-00-00 00:00' then dateadd(day,1,@curDate) else a.vaf14 end VAF14
      ,b.VAF22, b.BBX01,b3.ACI02 ,a.BCK01B,a.VAF23,c1.AAB01,f1.ACH01
    FROM @kbmVAT d
    join @TmpVAFcz a on a.VAF01 = d.VAF01
    LEFT JOIN @TmpVAFcz b ON b.VAF01A = b.VAF01 AND b.BDA01 = 'A'
    LEFT JOIN BBX1 b2 ON b.BBX01 = b2.BBX01
    LEFT JOIN ACI1 b3 ON b2.BBX13 = b3.ACI01  
    left join VBF1 c on c.VAF01 = d.VAF01 and c.VBF03 = '切口等级'
    left join AAB1 c1 on c1.AAB02 = c.VBF07  
	left join VBF1 f on f.VAF01 = d.VAF01 and f.VBF03 = '手术等级'
	left join ACH1 f1 on f1.ACH04 = f.VBF07
    
    insert into VAV1(VAV01,VAT01,VAV03,VAV04,VAV05,BAK01,BBX01,ACI02,VAV09)
    select VAV01,VAT01,VAV03,VAV04,VAV05,0,BBX01,ACI02,VAV09
    from @tmpVAV
  end 
  insert into VBX1(ACF01,VBX03,VBX04,VBX05,VBX06,VBX07,BCK01A,BCK01B,BCK01C,BCK01D
    ,BCE01A,BCE03A,VBX14,VAA01,VAA07,VBX17,VBX18,VBX19,VBX20,VBX21,VBX22)
  select distinct 2,2,1,1,f.BBX20,b.VAI04,a.BCK01A,a.BCK01B,a.BCK01E,a.BCK01D
    ,@lBCE01,@lBCE03,@curDate,a.VAA01,a.VAA07,0,a.VAI01,0,'护士站长嘱发送药品医嘱',0,0
  FROM #TmpVAJls a
  JOIN BBY1 c ON a.BBY01 = c.BBY01
  join #tmpVAI b on b.VAI01 = a.VAI01
  join @TmpVAFcz d on d.VAF01 = a.VAF01
  join @TmpVAFcz e on e.VAF01 = d.VAF01A
  join BBX1 f on f.BBX01 = e.BBX01
  where c.BDN01 <= '3'    
  
  update a set a.VAF10 = b.VAF10
  from VBZ1 a
  join @TmpVAFcz b on b.VAF01 = a.VAF01  
  
  update a set a.VCL19=1
  From VCL1 a
  Join @TmpVAFcz b on b.VAF01 = a.VCL08
  where a.ACF01=2 and a.VCL02=1 
  
  --病人余额
  if @VAA01 > 0 and @aVAF38 > 0
  begin             
    exec Nurse_VBM1_Update 2,0,@VAA01,@aVAF38,2,@VAA07  
  end  
COMMIT TRAN    
--库存处理
   if exists(select * from @tmpDPT)
   begin
     select @aVAIid = MIN(uid),@aVAIMax = MAX(uid) from @tmpDPT
     while @aVAIid <= @aVAIMax
     begin
       select @BCK01 = BCK01,@BBY01 = BBY01,@DSK01 = ISNULL(DSK01,0),@VAJ25 = VAJ25 from @tmpDPT where uid = @aVAIid
       exec DPS_DPT1_Update @BCK01,@BBY01,@DSK01,@VAJ25
       
       set @aVAIid = @aVAIid + 1
     end
   end

--卫材库存处理
  if @para241=1
  begin
	   if exists(select * from @tmpMDT)
	   begin
		 select @aVAIid = MIN(uid),@aVAIMax = MAX(uid) from @tmpMDT
		 while @aVAIid <= @aVAIMax
		 begin
		   select @BCK01 = BCK01,@BBY01 = BBY01,@DSK01 = ISNULL(DSK01,0),@VAJ25 = VAJ25 from @tmpMDT where uid = @aVAIid
		   exec MDS_MDT1_Update @BCK01,@BBY01,@DSK01,@VAJ25
		   set @aVAIid = @aVAIid + 1
		 end
	   end
  end

   --tiao shi ku cun
 if @cfxz202=1
 begin
  insert into DPT1_log(BBY01,BCK01,RefQty,SellQty,Quantity,SysDate,BCE01,Bce03,ACF01,VAJ25,DSK_ID, PurchasePrice, PresellPrice)
  select BBY01,BCK01,RefQty,SellQty,LSQty,@curDate,@lBCE01,@lbce03,2,VAJ25,DSK_ID, PurchasePrice, PresellPrice
  from #tmpDpt1log  
end
  --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz
  IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA
  IF Object_id('tempdb..#TmpVAJls') IS NOT NULL DROP TABLE #TmpVAJls
  IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD
  IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI