--存储过程.Nurse_Temp_Advice_Send


--Alter 8058 2017.8.14
--护士工作站 临嘱医嘱送住院病人

ALTER                  PROC [dbo].[Nurse_Temp_Advice_Send]
  @lXml  nText
  , @lBCKxml nText --药房置换
  ,@lSign smallint  --6:住院记账
  ,@lBCE02 varchar(20)  --当前操作员编号
  ,@lBCE03 varchar(20)  --当前操作员姓名
  ,@lBCE01 int = 0
  ,@lSend tinyint = 0
  ,@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
    --取得需要发送的医嘱id
  declare @TmpVAFsztmp Table(ID int IDENTITY(1,1),VAF01 int primary key nonclustered, 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(60),BBX01 int,VAF31 tinyint
     ,VAJ57 varchar(128),VAF01B int,BBX17 tinyint,VAF23 varchar(128),VAF27 int,VAJ57B varchar(128)
     ,bck01c_br int,bck01d_br int,VAF15 varchar(30) )
  
  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..#TmpVAFsz') IS NOT NULL DROP TABLE #TmpVAFsz
  IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ
  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
  
  select @lBCE02 = BCE02,@lBCE03 = BCE03 from BCE1 where BCE01 = @lBCE01
  declare @IsNurse tinyint
  set @IsNurse = 0
  if exists(select * from BAA1 where BCE01 = 1 and AAA01 = '02')
    set @IsNurse = 1    

 --truncate table #TmpVAFsz 

  INSERT INTO @TmpVAFsztmp(VAF01,VAA01)
  SELECT VAF01,VAA01
  from OpenXml(@iDOM,'/Root/VAF1/Ie',8)
    WITH (VAF01 int ,VAA01 int,VAF11 tinyint)	
  where VAF11 = 2     
   
  If not exists(select * from @TmpVAFsztmp)or (@@ERROR <> 0)
  BEGIN
   exec sp_xml_removedocument @iDOM
   --IF Object_id('tempdb..#TmpVAFsz') IS NOT NULL DROP TABLE #TmpVAFsz
   IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ
   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
  -- RAISERROR('从文档中提取医嘱信息错误.', 16, 1) with nowait
   RETURN 2
  END

  --提取医嘱计价信息  aflag 1=折扣价
  Create Table #tmpVBD(ID int IDENTITY(1,1)
      ,VAF01 int
      ,BBY01 int
      ,VBD04 numeric(18,4) 
      ,VBD05 numeric(18,4) 
      ,VBD06 tinyint
      ,BCK01 int
      ,VBD08 tinyint
      ,VBD09 tinyint,DSK01 int,BAG23 numeric(18,4),BBY01A int,aflag tinyint )  
  
  IF @lSend = 1
  Begin
    Insert Into #tmpVBD(VAF01,BBY01,VBD04,VBD05,VBD06,BCK01,VBD08,VBD09,DSK01,BAG23,BBY01A,aflag)
    SELECT VAF01,BBY01,VBD04,BBY25,VBD06,BCK01,VBD08,VBD09,DSK01,BAG23,BBY01A,1
    from OpenXml(@iDOM,'/Root/VBD1/Ie',8)
      WITH
       (VAF01 int
        ,BBY01 int
        ,VBD04 numeric(18,4) 
        ,BBY25 numeric(18,4) 
        ,VBD06 tinyint
        ,BCK01 int
        ,VBD08 tinyint
        ,VBD09 tinyint ,DSK01 int,BAG23 numeric(18,4),BBY01A int   
       )	    
  End
  exec sp_xml_removedocument @iDOM
  Declare @Tmp_BCK Table(BCK01 int, BCK01A int) 
  Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @lBCKxml
  if @iret > 0
  BEGIN
    exec sp_xml_removedocument @iDOM	
   -- IF Object_id('tempdb..#TmpVAFsz') IS NOT NULL DROP TABLE #TmpVAFsz
    IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ
    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
    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   
  
  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.VBI07 = (Case When b.BDA01 >= '1' And b.BDA01 <= '2' then b.VAF17 * b.VAF27 Else b.VAF21 End)
    ,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.VAF27 = b.VAF27
    ,a.bck01c_br = c.BCK01C,a.bck01d_br = c.BCK01D,a.VBI13 = 0,a.VAF15=b.VAF15
  FROM @TmpVAFsztmp a
  JOIN VAF2 b with(nolock) ON b.VAF01 = a.VAF01  
  JOIN VAE1 c with(nolock) on c.VAE01 = b.VAF06
  left join BBX1 d on d.BBX01 = b.VAF01B 

  Insert into @TmpVAFsztmp(VAF01, BDA01 ,VAF01A, Rownr, CBM01,VAF10,BCK01B , BCK01C ,BCK01D ,BCE02A ,BCE03A 
      ,VAF32 ,VAF58,ACF01,VAF18,VAF21,VAA01,VAA07,VAP01,BCK01A,VAF35,VAF22,VBI07,VAF36,BCK01F,ABC02
      ,VAF14,BCE01A,BBX01,VAF31,VAJ57,VAF01B,VAF23,VAF27,bck01c_br,bck01d_br,VBI13,VAF15)
  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.VAF17 * a.VAF27,a.VAF36,c.BCK01C,c.ABC02,a.VAF14,a.BCE01A,a.BBX01,a.VAF31,d.BBX05,a.VAF01B
    ,a.VAF23,a.VAF27,c.BCK01C,c.BCK01D,0,a.VAF15
  From @TmpVAFsztmp b
  Join VAF2 a with(nolock) on a.VAF01 = b.VAF01A
  join VAE1 c with(nolock) on c.VAE01 = a.VAF06
  left join BBX1 d with(nolock) on d.BBX01 = a.VAF01B 
  Where b.BDA01 >= '1' And b.BDA01 <= '2' And b.Rownr = 1 and a.VAF32=1 
  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.VAF21,a.VAF36,c.BCK01C,c.ABC02,a.VAF14,a.BCE01A,a.BBX01,a.VAF31,d.BBX05,a.VAF01B
    ,a.VAF23,a.VAF27,c.BCK01C,c.BCK01D,0,a.VAF15
  From @TmpVAFsztmp b
  Join VAF2 a with(nolock) on a.VAF01A = b.VAF01
  join VAE1 c with(nolock) on a.VAF06 = c.VAE01 
  left join BBX1 d with(nolock) on d.BBX01 = a.VAF01B 
  Where b.BDA01 >='8' and a.VAF01A>0 and a.VAF32<>1
  
  IF @@ERROR <> 0
  begin
    --IF Object_id('tempdb..#TmpVAFsz') IS NOT NULL DROP TABLE #TmpVAFsz
      IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ
      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
      RAISERROR('从数据中提取医嘱明细信息错误.', 16, 1) with nowait
      RETURN 4
  end
  update a set a.VAF01B = b.BBX01,a.VAJ57B = '.'+b.BBX05
  from @TmpVAFsztmp 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 @sql varchar(512),@Para37 varchar(32),@Para53 varchar(10),@Para140 varchar(10),@gpara91 varchar(10),@para429 varchar(20)
  declare @cfxz_92 varchar(2)
  set @Para37 = dbo.GetSysParamValue(9999,9999,37)
  set @Para53 = dbo.GetSysParamValue(9999,9999,53)
  set @Para140 = dbo.GetSysParamValue(100,105001,140)
  set @gpara91 = dbo.GetSysParamValue(9999,9999,91)
  set @para429 = dbo.GetSysParamValue(100,105001,429)
  set @cfxz_92='0'
  if exists(select BLP02 from BLP1 where BLP02=92)
    set @cfxz_92='1'
  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 @TmpVAFsztmp a
    left join BBX1 b with(nolock) on b.BBX01 = a.BBX01
    
    update @TmpVAFsztmp set BBX17 = 1 where BDA01='0'
  end
  else
  begin
    update @TmpVAFsztmp set BBX17 = 1
  end   

  IF (@lSend = 1) and (((@Para37 <> '0') and (len(@Para37)>1)) or (@cfxz_92='1'))
  begin
    if (@Para37 <> '0') and (len(@Para37)>1)
    begin
	 --2017.2.20 变量无法传入exec中执行，故进行调整
      set @Para37=replace(@Para37,'''','')      
      delete a from #tmpVBD a join @TmpVAFsztmp b on b.VAF01 = a.VAF01 where not b.BDA01 in (select value  from dbo.split(@Para37,','))
      --exec(@sql) 
      delete from @TmpVAFsztmp where not BDA01 in (select value  from dbo.split(@Para37,','))
      --exec(@sql)
    end

    if @cfxz_92='1'
    begin
      delete b
      from @TmpVAFsztmp a   
      join #tmpVBD b on b.vaf01=a.vaf01 
      where not exists(SELECT e.* 
                   FROM @TmpVAFsztmp e
                   JOIN BAZ1 f ON e.BCK01B = f.BCK01
                   WHERE e.BDA01 = 'S' And e.VAF01A = 0 And f.BAU01 = '03' AND f.ACF01 IN (2,3)
                       and (a.VAF01 = e.VAF01 or a.VAF01A = e.VAF01) )
      delete a
      from @TmpVAFsztmp a   
      where not exists(SELECT e.* 
                   FROM @TmpVAFsztmp e
                   JOIN BAZ1 f ON e.BCK01B = f.BCK01
                   WHERE e.BDA01 = 'S' And e.VAF01A = 0 And f.BAU01 = '03' AND f.ACF01 IN (2,3)
                       and (a.VAF01 = e.VAF01 or a.VAF01A = e.VAF01) )                 
    end
    if not exists(select * from @TmpVAFsztmp)
    begin
     -- IF Object_id('tempdb..#TmpVAFsz') IS NOT NULL DROP TABLE #TmpVAFsz
      IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ
      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
      RETURN 4
    end
  end
  if @para140 = '1' and exists(select * from @TmpVAFsztmp 
        where (BDA01='0' or BDA01 > '4') and VAF32 =0 and (BCK01B=bck01c_br or BCK01B=bck01d_br))
  begin
    update a set a.VBI13 = 1
    from @TmpVAFsztmp a
    join bbx1 b with(nolock) on b.BBX01=a.BBX01
    where (a.BDA01='0' or a.BDA01 > '4') and a.VAF32 =0 and (a.BCK01B=a.bck01c_br or a.BCK01B=a.bck01d_br) 
      and not (a.BDA01='Z' AND b.BBX13 ='5')
  end
  
  Create Table #TmpVAJ (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),VAF22 varchar(1024),BCK01E int,VAI01 int,CBM01 int,ABF01 varchar(8)
      ,ABC02 varchar(20),FVAJ25 numeric(18,4),VAJ34 numeric(18,4),VAJ59 numeric(18,4),FVAJ24 numeric(18,4),VAJ39 tinyint
      ,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),BCE01E int,BCE03E varchar(20),BCK01F int,BCQ04 varchar(20),BAG46 numeric(18,4)
      ,VEG04 varchar(64),BDA01 varchar(2),aflag tinyint )
    
  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))
      
  Declare @tmpDPT Table(uid int IDENTITY(1,1),BCK01 int,BBY01 int,DSK01 int,VAJ25 numeric(18,4))    
    
  Declare @EndDate DateTime, @aDate Datetime
  DECLARE @lVBDid int, @lVBDMax int, @aVAIid int, @aVAIMax int, @aStr varchar(20)
  Declare @aError int, @smsg varchar(256)

  Set @aError = 0
  Set @aStr = REPLICATE('0',10)
  Set @aDate = GetDate()
  Set @EndDate = Convert(varchar(10),@aDate,121)+' 23:59:59.999'    
--提取所有医嘱计价信息
  IF @lSend = 0
  Begin
    InSert Into #tmpVBD(VAF01,BBY01,VBD04,VBD05,VBD06,BCK01,VBD08,VBD09,BBY01A,aflag)
    SELECT a.VAF01,b.BBY01,b.VBD04,b.VBD05,b.VBD06,b.BCK01,b.VBD08,b.VBD09,b.BBY01A,1
    From @TmpVAFsztmp a
    join VBD2 b with(nolock) on a.VAF01 = b.VAF01
    join BBY1 c with(nolock) on c.BBY01 = b.BBY01    
    Where b.VBD04 >= 0 and isnull(c.bby34,0)<>2 and c.BBY31 > @EndDate
    union all
    SELECT a.VAF01,e.BBY01,b.VBD04*d.BCR04,Case when d.BCR10 = 1 then c.BBY25 else d.BCR07 end BBY25
      ,b.VBD06,b.BCK01,b.VBD08,b.VBD09,b.BBY01A,0
    From @TmpVAFsztmp a
    join VBD2 b with(nolock) on a.VAF01 = b.VAF01    
    join BBY1 c with(nolock) on c.BBY01 = b.BBY01
    join BCR1 d with(nolock) on d.BBY01A = b.BBY01
    join BBY1 e with(nolock) on e.BBY01 = d.BBY01B
    Where b.VBD04 >= 0 and c.bby34=2 and c.BBY31 > @EndDate and d.BCR09 > @EndDate and e.BBY31 > @EndDate
  End
  
  if @gpara91 = '1'
  begin
    delete a
    from #tmpVBD a
    join @TmpVAFsztmp b on b.VAF01 = a.VAF01
    where (b.bda01 < '4' and b.bda01>='1') or (b.bda01 > '3' and b.VAF32 = 1)
  end

  --判断药品有效期禁用 
  if exists(select a.*
        from @TmpVAFsztmp a
        join #tmpVBD b on b.VAF01 = a.VAF01    
        join BBY1 c with(nolock) on c.BBY01 = b.BBY01
        where b.VBD04 >= 0 and c.BBY31 <= @EndDate
          and a.BDA01 >= '1' and a.BDA01 <= '3' and a.VAF58 <> 1 )
  begin
    set @smsg = (select top 1 (c.BBY04 +' ：'+ c.BBY05 ) as fname
                 from @TmpVAFsztmp a
                 join #tmpVBD b on b.VAF01 = a.VAF01    
                 join BBY1 c on c.BBY01 = b.BBY01
                 where b.VBD04 >= 0 and c.BBY31 <= @EndDate
                   and a.BDA01 >= '1' and a.BDA01 <= '3' and a.VAF58 <> 1)
   -- IF Object_id('tempdb..#TmpVAFsz') IS NOT NULL DROP TABLE #TmpVAFsz
    IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ
    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
    set @smsg = '【'+@smsg+'】此药品有效期已过，不能继续使用.'
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 4
  end
--判断没有计价内容药品医嘱，不允许校队  
  if @gpara91 <> '1' and exists(select *
    from @TmpVAFsztmp a
    where a.VAF58 <> 1 and a.BDA01 >='1' and a.BDA01 <= '3'
      and not exists(select * from #tmpVBD b where b.VAF01 = a.VAF01))
  begin
    set @smsg = (select top 1 a.VAF22 as fname
                 from @TmpVAFsztmp a
                 where a.VAF58 <> 1 and a.BDA01 >='1' and a.BDA01 <= '3'
                   and not exists(select * from #tmpVBD b where b.VAF01 = a.VAF01))
   -- IF Object_id('tempdb..#TmpVAFsz') IS NOT NULL DROP TABLE #TmpVAFsz
    IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ
    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
    set @smsg = '【'+@smsg+'】此医嘱没有计价内容，不能发送，请先联系管理员.'
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 6
  end
  
  if exists(select * from @TmpVAFsztmp 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 @TmpVAFsztmp c on c.VAF01 = a.VAF01
    join BBY1 d with(nolock) on d.BBY01 = a.BBY01
    where c.BDA01 >= '1' and c.BDA01 <= '3' and c.VAF58 <> 1 and d.BDN01 <= '3'
  end  
  --***
  select a.*
  into #kbmVAJpg
  from VAJ2 a with(nolock)
  join (select distinct b.VAA07,c.BBY01 
        from @TmpVAFsztmp b join #tmpVBD c on c.VAF01 = b.VAF01 
        where b.VAF32 = 1 and b.BDA01 = 'T' and c.VBD09 = 2) d on d.VAA07 = a.VAA07 and d.BBY01 = a.BBY01
  where a.ACF01 = 2 and a.VAK01 = 0      
  
  delete a1
  from #tmpVBD a1
  join @TmpVAFsztmp b1 on b1.VAF01 = a1.VAF01 
  where b1.VAF32 = 1 and b1.BDA01 = 'T' and a1.VBD09 = 3
   and exists(
    select a.VAF01,b.BBX01
    from #tmpVBD a
    join @TmpVAFsztmp b on b.VAF01 = a.VAF01 
    where a1.VAF01 = a.VAF01 and b1.BBX01=b.BBX01 and b.VAF32 = 1 and b.BDA01 = 'T' and a.VBD09 = 2
      and not Exists(select * from #kbmVAJpg d where d.VAA07 = b.VAA07 and d.BBY01 = a.BBY01 and DateDiff(Day,@aDate,d.VAJ46) = 0) )   
                            
  delete a
  from #tmpVBD a
  join @TmpVAFsztmp b on b.VAF01 = a.VAF01 
  where b.VAF32 = 1 and b.BDA01 = 'T' and a.VBD09 = 2
    and Exists(select * from #kbmVAJpg d where d.VAA07 = b.VAA07 and d.BBY01 = a.BBY01 and DateDiff(Day,@aDate,d.VAJ46) = 0)        
  
  select a.BBY01,MAX(b.VAF27) sVAF27,0 VAF01
  into #kbmBDU10
  from #tmpVBD a
  join @TmpVAFsztmp 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 @TmpVAFsztmp 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 @TmpVAFsztmp 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)
  if OBJECT_ID('tempdb..#kbmVAJpg') is not null drop table #kbmVAJpg                                        
  if OBJECT_ID('tempdb..#kbmBDU10') is not null drop table #kbmBDU10
  --**
  Insert Into #TmpVAJ(VAA01,VAA07,Rownr,VAF01,ACF01,BCK01A,BCK01B,BDN01,BBY01,VAJ23,VAJ24,VAJ25
      ,VAJ26,VAJ27,BCK01C,BCE02B,BCE03B,BCK01D,VAJ53,VAJ54,VAF22,BCK01E,CBM01,ABF01,ABC02,VAJ39,VAJ32,VAJ01A
      ,ABC01,BCK18,BCK01F,BCQ04,BDA01,aflag)
  Select a.VAA01,a.VAA07,a.Rownr,a.VAF01,a.ACF01,a.BCK01F,a.BCK01A,c.BDN01,b.BBY01
      ,Case When a.BDA01 = '3' Then a.VAF21 Else 1 End as VAJ23 ,a.VAF18,b.VBD04
      ,a.VAF35,a.VAP01,a.BCK01C,a.BCE02A,a.BCE03A,b.bck01,0,'临嘱发送'
      ,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.VBD05,a.VAF01B,0,9,0,'',a.BDA01,b.aflag
  From @TmpVAFsztmp a
  Join #tmpVBD b on a.VAF01 = b.VAF01
  Join BBY1 c with(nolock) on b.BBY01 = c.BBY01
  left join BBY1 d with(nolock) on d.BBY01 = b.BBY01A
  Where b.VBD04 > 0 And a.BDA01 >= '1' And a.BDA01 <= '3' And a.VAF58 <> 1
  Union All
  Select a.VAA01,a.VAA07,a.Rownr,a.VAF01,a.ACF01,a.BCK01F,a.BCK01A,c.BDN01,b.BBY01,1,1,b.VBD04
      ,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) then 1 else 0 end VAJ53
      ,'临嘱发送'
      ,case when b.BBY01A > 0 or a.VAF01B >0 then isnull(a.VAJ57,'')+'.'+isnull(d.BBY05,'')+isnull(a.VAJ57B,'') else a.VAJ57 end
      ,0,a.CBM01 ,c.ABF01,a.ABC02,a.VAF31,b.VBD05,a.VAF01B,0,9,0,'',a.BDA01,b.aflag
  From @TmpVAFsztmp a
  Join #tmpVBD b on a.VAF01 = b.VAF01
  Join BBY1 c with(nolock) on b.BBY01 = c.BBY01
  left join BBY1 d with(nolock) on d.BBY01 = b.BBY01A
  Where b.VBD04 > 0 And (a.BDA01 >= '4' or a.BDA01='0')
    And (@para429='1' or (@para429<>'1' and not exists(SELECT e.* 
                   FROM @TmpVAFsztmp e
                   JOIN BAZ1 f ON e.BCK01B = f.BCK01
                   WHERE e.BDA01 = 'S' And e.VAF01A = 0 And f.BAU01 = '03' AND f.ACF01 IN (2,3)
                       and (a.VAF01 = e.VAF01 or a.VAF01A = e.VAF01) )) )
  Order By a.CBM01,a.VAF01  

  IF Exists(Select * From #tmpVAJ)
  Begin  
    UPDATE a set a.ABC01 = 1 
    from #tmpVAJ a
    join ABC1 b on b.ABC02 = a.ABC02
    where b.ACF01 in (2,3) and DATEDIFF(DAY,b.ABC12,@aDate)>=0 and DATEDIFF(DAY,@aDate,b.ABC13)>= 0 
      and (isnull(b.ABC05,0) = 0 or(isnull(b.ABC05,0) = 1 and exists(select * from ACW1 c 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 #tmpVAJ a
    JOIN BBY1 c with(nolock) ON a.BBY01 = c.BBY01
    LEFT JOIN (SELECT f.ABC02,f.BBY01,f.ACV10 
               FROM ACV1 f 
               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 
               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 
                                  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 #tmpVAJ a   
    join BCK1 c on c.BCK01 = a.BCK01D  
    where a.BDN01 >='1' and a.BDN01 <= '3' and a.BCK18 = 9
    
    Insert Into #tmpVAI(VAA01,VAA07,BCE01C,BCE03C)    
    Select Distinct a.VAA01,a.VAA07,c.BCE01,c.BCE03
    From #tmpVAJ a
    join VAE1 b on b.VAE01 = a.VAA07
    left join BCE1 c on c.BCE02 = b.BCE02C
      
    Select @aVAIMax = Max(ID) From #tmpVAI
    Exec Core_NewIDEX 'VAI1','VAI01',@aVAIid out, @aVAIMax

    IF @aVAIid <= 0
    Begin
      --IF Object_id('tempdb..#TmpVAFsz') IS NOT NULL DROP TABLE #TmpVAFsz
      IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ
      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
      RAISERROR('获取收费单据id出现错误.', 16, 1) with nowait       
      RETURN 5
    End

    Set @aVAIid = @aVAIid - @aVAIMax
      
    Update #tmpVAI
    Set VAI01 = @aVAIid + ID, VAI04 = 'LZ'+substring(@aStr,1,10 -len(@aVAIid + ID))+cast((@aVAIid + ID) AS varchar),VAI22 = 0,VAI23 = 0      
    IF @@ERROR <> 0 
    Begin
     -- IF Object_id('tempdb..#TmpVAFsz') IS NOT NULL DROP TABLE #TmpVAFsz
      IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ
      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
      RAISERROR('获取收费单据号出现错误.', 16, 1) with nowait 
      RETURN 6
    End
      
    Update a
    Set a.VAI01 = b.VAI01,a.BCE01E = b.BCE01C,a.BCE03E = b.BCE03C
    From #tmpVAJ a, #tmpVAI b 
    Where a.VAA01 = b.VAA01 And a.VAA07 = b.VAA07  
  --插入费用明细
    Select @aVAIid = 0, @aVAIMax = 0

    Select @aVAIMax = Max(ID) From #tmpVAJ
    Exec Core_NewIDEX 'VAJ1','VAJ01',@aVAIid out, @aVAIMax

    IF @aVAIid <= 0
    Begin
     -- IF Object_id('tempdb..#TmpVAFsz') IS NOT NULL DROP TABLE #TmpVAFsz
      IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ
      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
      RAISERROR('获取收费单据id出现错误.', 16, 1) with nowait 
      RETURN 5
    End

    Set @aVAIid = @aVAIid - @aVAIMax 
  
    Update #tmpVAJ Set VAJ01 = @aVAIid + ID,BAG46  = 0

    Update a
    Set a.VAI01 = b.VAI01
    From @TmpVAFsztmp a
    join #tmpVAJ b on a.CBM01 = b.CBM01 And a.VAF01 = b.VAF01

    update a
    set a.VAI01 = b.VAI01
    from @TmpVAFsztmp a
    join @TmpVAFsztmp b on b.VAF01A = a.VAF01
    where b.VAF01A > 0 and a.VAF01A = 0 and b.VAI01 > 0 and isnull(a.VAI01,0) = 0

    Update a
    Set a.BCK01B = b.BCK01C,a.BCE02 = b.BCE02B,a.BCE03 = b.BCE03B,a.ACF01 = b.ACF01
    From #tmpVAI a
    Join #tmpVAJ b on a.VAI01 = b.VAI01

    Update a
    Set a.FVAJ25 = Case a.BDN01 When '3' Then cast(a.VAJ25 * c.BAG07 AS NUMERIC(18,4)) Else CEILING(CAST(a.VAJ25 * c.BAG07 AS NUMERIC(18,4))) End
       ,a.FVAJ24 = Case a.BDN01 When '3' Then cast(a.VAJ24 * c.BAG07 AS NUMERIC(18,4)) Else CEILING(CAST(a.VAJ25 * c.BAG07 AS NUMERIC(18,4))) End
        , a.VAJ34 = c.BAG07,a.VAJ59 = case when isnull(c.BAG23,0) > 0.000001 then c.BAG23 else b.BBY25 end,a.FAB03 = c.BDG02B
    From #tmpVAJ a
    Join BBY1 b on a.BBY01 = b.BBY01
    Join BAG1 c on b.BBY01 = c.BBY01
    Where a.BDN01 >= '1' And a.BDN01 <= '3'  and a.BDA01 <='3'   
  
    Update a
    Set a.FVAJ25 = a.VAJ25,a.FVAJ24 = a.VAJ24,a.VAJ34 = 1,a.VAJ59 = b.BBY25
    From #tmpVAJ a
    Join BBY1 b on a.BBY01 = b.BBY01
    Where a.BDN01 >= '4'
    
    Update a
    Set a.FVAJ25 = a.VAJ25,a.FVAJ24 = a.VAJ24,a.VAJ34 = c.BAG07,a.FAB03=c.BDG02B 
      ,a.VAJ59 = case when isnull(c.BAG23,0) > 0.000001 then c.BAG23 else b.BBY25 end
    From #tmpVAJ a
    Join BBY1 b on a.BBY01 = b.BBY01      
    join BAG1 c on c.BBY01 = b.BBY01
    Where (a.BDA01>='4' and a.BDN01<='3')
    
    select a.BCK01,a.BBY01,min(a.DSK_ID) DSK01
    into #kbmDpt
    from DPT1 a
    join (select BCK01D BCK01,BBY01
          from #tmpVAJ 
          where BDN01 >='1' and BDN01 <= '3') b on b.BCK01 = a.BCK01 and b.BBY01 = a.BBY01
    where a.Quantity > 0.000001 and a.RefQty > 0.000001
    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.DSK01 = d.DSK01
    from #tmpVAJ a
    join bby1 c with(nolock) on c.BBY01=a.bby01
    join BAG1 b with(nolock) on b.BBY01 = a.BBY01        
    left join #kbmDpt 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.DSK01 = b.DSK01,a.VAJ59 = c.PurchasePrice
      ,a.aflag=0
    from #tmpVAJ a
    join #kbmDpt b on b.BCK01 = a.BCK01D and b.BBY01 = a.BBY01
    join DPT1 c with(nolock) on c.BCK01 = b.BCK01 and c.BBY01 = b.BBY01 and c.DSK_ID = b.DSK01
    join BBY1 e with(nolock) on e.BBY01 = a.BBY01
    where a.BDN01 >='1' and a.BDN01 <= '3' and (e.BBY19 = 1 or (e.BBY19 = 0 and a.BCK18 = 1))
    
    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 a.aflag=0 and f1.BBY01 IS NOT NULL  THEN (a.VAJ32*f1.ACV08)/f1.ACV09 
               WHEN a.aflag=0 and f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.VAJ32*g1.ACV08)/g1.ACV09 
             ELSE a.VAJ32 END 
       ,a.VAJ36 = a.FVAJ25 * (case when a.aflag=0 then a.VAJ32 else c.BBY25 end)
       ,a.VAJ37 = CASE WHEN a.aflag=0 and f1.BBY01 IS NOT NULL  THEN (a.FVAJ25 * a.VAJ32 * f1.ACV08)/f1.ACV09 
               WHEN a.aflag=0 and f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.FVAJ25 * a.VAJ32 * g1.ACV08)/g1.ACV09 
             ELSE a.FVAJ25 * a.VAJ32 END
       ,a.VAJ38 = CASE WHEN a.aflag=0 and f1.BBY01 IS NOT NULL  THEN (a.FVAJ25 * a.VAJ32 * f1.ACV08)/f1.ACV09 
               WHEN a.aflag=0 and f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.FVAJ25 * a.VAJ32 * g1.ACV08)/g1.ACV09 
             ELSE a.FVAJ25 * a.VAJ32 END 
       ,a.VAJ61 = CASE WHEN a.aflag=0 and f1.BBY01 IS NOT NULL  THEN (a.FVAJ25 * a.VAJ32 * f1.ACV08)/f1.ACV09 
               WHEN a.aflag=0 and f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.FVAJ25*a.VAJ32*g1.ACV08)/g1.ACV09 
             ELSE a.FVAJ25*a.VAJ32 END ,a.VAJ32=(case when a.aflag=0 then a.VAJ32 else c.BBY25 end)                        
    FROM #tmpVAJ 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.FVAJ25) AND f1.ACV07>=(a.VAJ32*a.FVAJ25)    
    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 
                                  WHERE g2.BCH01 IS NULL AND a.ABC02=g2.ABC02 AND a.BBY01=g2.BBY01)
                   AND g1.ACV06<= (a.VAJ32*a.FVAJ25) AND g1.ACV07>=(a.VAJ32*a.FVAJ25)  
    
    update a
    set a.VAI22 = d.FVAJ36,a.VAI23 = d.FVAJ38                 
    FROM #tmpVAI a
    join (select b.VAI01,Isnull(sum(b.VAJ36),0) FVAJ36,Isnull(sum(b.VAJ38),0) FVAJ38
          From #tmpVAJ b 
          where exists(select * from @TmpVAFsztmp b1 where b1.VAF01 = b.VAF01 and b1.BBX17 <> 3 )               
          Group By b.VAI01 ) d on a.VAI01 = d.VAI01  
          
    update a set a.BCK01F = c.BCK01A,a.BCQ04 = b.BCQ04B
    From #tmpVAJ 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)
    
    select @ReVAIid = case when @ReVAIid = '' then cast(VAI01 as varchar) else @ReVAIid+','+cast(VAI01 as varchar) end 
    from #tmpVAI where isnull(VAI01,'')<>''
  end          
   select @ReVafid_hz = case when @ReVafid_hz = '' then cast(a.VAF01 as varchar) else @ReVafid_hz+','+cast(a.VAF01 as varchar) end 
   from @TmpVAFsztmp 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 @TmpVAFsztmp a
   join bbx1 b with(nolock) on b.BBX01=a.BBX01
   where a.BDA01='Z' and b.BBX13 ='5'
  if Exists(select * FROM @TmpVAFsztmp a Join VAF2 b On a.VAF01=b.VAF01 WHERE b.VAF10 <> 3 And @lSend = 0)
  begin
   -- IF Object_id('tempdb..#TmpVAFsz') IS NOT NULL DROP TABLE #TmpVAFsz
      IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ
      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
    RAISERROR('已有医嘱被作废或停止，请返回重新选择过滤条件，再执行医嘱！', 16, 1) with nowait
    RETURN 7
  end 
 --药品如果禁售，就不允许在发药
  if exists(select * from #tmpVAJ a join BAL1 b on b.BCK01 = a.BCK01D and b.BBY01 = a.BBY01 where b.BAL08 = 1)
  begin
    set @smsg = (select top 1 c.BBY05 from #tmpVAJ a join BAL1 b on b.BCK01 = a.BCK01D and b.BBY01 = a.BBY01 join BBY1 c on a.BBY01 = c.BBY01 where b.BAL08 = 1)
    --IF Object_id('tempdb..#TmpVAFsz') IS NOT NULL DROP TABLE #TmpVAFsz
      IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ
      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
    set @smsg = '【'+@smsg+'】药品药房已经禁售.'
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 4
  end 
  --判断药品最大限制销售额
  declare @gpara69 varchar(10),@vaj47 datetime
  declare @BBY01 int,@BCK01 int,@DSK01 int,@VAJ25 numeric(18,4),@para256 int
  set @para256 = cast(dbo.GetSysParamValue(100,105001,256) as int) 
  Declare @aVAF38 numeric(18,4),@VAA01 int,@VAA07 int
  set @VAA01 = ISNULL((select top 1 VAA01 from #tmpVAI),0)
  set @VAA07 = ISNULL((select top 1 VAA07 from #tmpVAI),0) 
 
  insert into @tmpDPT(BCK01,BBY01,DSK01,VAJ25)
  select a.BCK01D,a.BBY01,a.DSK01,SUM(a.FVAJ25)
  from #TmpVAJ a 
  where a.BDN01 >= '1' and a.BDN01 <= '3'
  group by a.BCK01D,a.BBY01,a.DSK01 
  --可用库存追踪记录
  /*
  select d.BBY01,d.BCK01,d.RefQty,d.SellQty,d.LSQty,c.VAJ25,0 DSK_ID,0 PurchasePrice,0 PresellPrice
  into #tmpDpt1log
  from (select BCK01,BBY01,SUM(Quantity) LSQty,sum(RefQty) RefQty,sum(SellQty) SellQty
        from DPT1
        group by BCK01,BBY01) d
  join (select a.BCK01D BCK01,a.BBY01,sum(a.FVAJ25) VAJ25
        from #tmpVAJ a
        where a.BDN01 >= '1' and a.BDN01 <= '3'    
        group by a.BCK01D,a.BBY01 ) c on c.BCK01 = d.BCK01 and c.BBY01 = d.BBY01
  union all
  select a.BBY01,b.BCK01,b.RefQty,b.SellQty,b.Quantity,a.fvaj25,a.DSK01,b.PurchasePrice,b.PresellPrice
  from #tmpvaj a
  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
  */
  --发送手术医嘱到手术记录表
  Declare @tmpVAT Table(ID int IDENTITY(1,1),VAF01 int,VAT01 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 e.* 
            FROM @TmpVAFsztmp e
            JOIN BAZ1 f ON e.BCK01B = f.BCK01
            WHERE (e.BDA01 = 'S' or e.BDA01 = '0') And e.VAF01A = 0 And f.BAU01 = '03' AND f.ACF01 IN (2,3) )
  BEGIN			    
    insert into @tmpVAT(VAF01) 
    SELECT e.VAF01
    FROM @TmpVAFsztmp e
    WHERE (e.BDA01 = 'S' or e.BDA01 = '0') And e.VAF01A = 0
      and exists(select * from BAZ1 f where f.BCK01 = e.BCK01B 
                  And f.BAU01 = '03' AND f.ACF01 IN (2,3) )    
    Select @aVAIid = 0, @aVAIMax = 0
    Select @aVAIMax = Max(ID) From @tmpVAT
    EXEC Core_NewIDEx 'CBM1','CBM01',@aVAIid out, @aVAIMax
    Set @aVAIid = @aVAIid - @aVAIMax 
    update @tmpVAT 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 @tmpVAT a
    join @TmpVAFsztmp 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 @tmpVAT a
    join @TmpVAFsztmp 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 @tmpVAT a
    join @TmpVAFsztmp b on b.VAF01A = a.VAF01
    join BBX1 c on c.BBX01 = b.BBX01
    left join ACI1 d 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   
  
  update a set a.VEG04 = b.VEH02
  from #TmpVAJ a
  join VEH2 b on b.VAF01 = a.VAF01  
  update a set a.VAJ53=2
  from #TmpVAJ 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   
  IF Exists(Select * From #tmpVAJ)
  Begin   
    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,@aDate
           ,@aDate, '住院临嘱发送' ,0,1,1,@lSign,0,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
         ,VAJ67,BCE01E,BCE03E,VAJ64,BCK01F,BCQ04)
    SELECT a.VAJ01,a.VAA01,a.VAA07,@lSign,1,a.ROWNR,0,a.VAI01,a.VAF01,0,a.ACF01,0,a.BCK01A,a.BCK01B
         ,c.BDN01,a.BBY01,null,0,0,a.VAJ23,a.FVAJ24,a.FVAJ25, a.VAJ26,a.VAJ27
         ,a.VAJ30 ,a.VAJ31 ,a.VAJ32,a.VAJ33,a.VAJ34,c.BBY08 AS VAJ35, a.VAJ36
         ,a.VAJ37,a.VAJ38 ,a.BCE03B,a.BCK01C,a.BCE02B,a.BCE03B,@aDate
         ,@aDate,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 @adate else null end VAJ51
         ,case when a.VAJ53=1 then @adate else null end VAJ52,a.VAJ53
         ,a.VAJ54,@lBCE02,@lBCE03,a.VAF22,a.VAJ59
         ,a.VAJ61,@aDate VAJ62, a.BCK01E,a.VAJ39,a.VAJ01A,DSK01,FAB03,a.VAJ32,a.BCE01E,a.BCE03E,@aDate,a.BCK01F,a.BCQ04
    FROM #tmpVAJ a
    JOIN BBY1 c ON a.BBY01 = c.BBY01
    where exists(select * from @TmpVAFsztmp b where b.VAF01 = a.VAF01 and b.BBX17 <> 3)
    
    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
         ,VAJ67,BCE01E,BCE03E,VAJ64,BCK01F,BCQ04)
    SELECT a.VAJ01,a.VAA01,a.VAA07,@lSign,1,a.ROWNR,0,a.VAI01,a.VAF01,0,a.ACF01,0,a.BCK01A,a.BCK01B
         ,c.BDN01,a.BBY01,null,0,0,a.VAJ23,a.FVAJ24,a.FVAJ25, a.VAJ26,a.VAJ27
         ,a.VAJ30 ,a.VAJ31 ,a.VAJ32,a.VAJ33,a.VAJ34,c.BBY08 AS VAJ35, a.VAJ36
         ,a.VAJ37,a.VAJ38 ,a.BCE03B,a.BCK01C,a.BCE02B,a.BCE03B,@aDate
         ,@aDate,0,a.BCK01D,null,null,null,0,a.VAJ54,@lBCE02,@lBCE03,a.VAF22,a.VAJ59
         ,a.VAJ61,@aDate VAJ62, a.BCK01E,a.VAJ39,a.VAJ01A,DSK01,FAB03,a.VAJ32,a.BCE01E,a.BCE03E,@aDate,a.BCK01F,a.BCQ04
    FROM #tmpVAJ a
    JOIN BBY1 c ON a.BBY01 = c.BBY01
    where exists(select * from @TmpVAFsztmp b where b.VAF01 = a.VAF01 and b.BBX17 = 3) 
    
    insert into VEG2(VAJ01,VEG02,VEG03,VEG04) 
    select VAJ01,0,FVAJ25,VEG04
    from #tmpVAJ
  End

--******
  UPDATE a 
  SET a.VAF10 = 8,a.VAF37 = @aDate,a.BCE03D = a.BCE03A, a.BCE01D = a.BCE01A
     ,a.BCE03E = @lBCE03, a.BCE01E = @lBCE01
     ,a.VAF47 = Case When @para256>0 and (DateDiff(Hour,a.VAF36,@aDate) >= 24 or a.VAF36 > @aDate) Then DateAdd(minute,@para256,a.VAF36) Else @aDate End
     ,a.BCE03B = case when a.VAF45 is null and @IsNurse = 0 then a.BCE03A
                      when a.VAF45 is null and @IsNurse = 1 then @lBCE03 else a.BCE03B end
     ,a.BCE03C = case when a.VAF45 is null and @IsNurse = 0 then a.BCE03A
                      when a.VAF45 is null and @IsNurse = 1 then @lBCE03 else a.BCE03C end
     ,a.BCE01B = case when a.VAF45 is null and @IsNurse = 0 then a.BCE01A
                      when a.VAF45 is null and @IsNurse = 1 then @lBCE01 else a.BCE01B end
     ,a.BCE01C = case when a.VAF45 is null and @IsNurse = 0 then a.BCE01A
                      when a.VAF45 is null and @IsNurse = 1 then @lBCE01 else a.BCE01C end
     ,a.VAF45 = case when a.VAF45 is null then @aDate else a.VAF45 end
  From VAF2 a
    Join @TmpVAFsztmp b on a.VAF01 = b.VAF01
 --插入医嘱发送记录 
  Select @aVAIid = 0, @aVAIMax = 0

  Select @aVAIMax = Max(ID) From @TmpVAFsztmp

  EXEC Core_NewIDEx 'VBI1','VBI01',@aVAIid out, @aVAIMax
  Set @aVAIid = @aVAIid - @aVAIMax 

  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 @aVAIid + ID,VAF01,@aVAIid + ID,@lSign,VAI01,ROWNR,VBI07,@lBCE03,@aDate,@aDate,@aDate
     ,VBI13,BCK01B,2,@lBCE01,0,ACF01,VAA01,VAA07,VAP01,VBI07
     ,case when VBI13=1 then @adate else null end VBI12
     ,case when VBI13=1 then '发送医嘱自动执行,操作人：'+@lbce03 else '' end VBI24
  FROM @TmpVAFsztmp
--保存医嘱计价信息  
  if (@lSend = 1) and (exists(select * from #tmpVBD))
  begin
    SELECT @lVBDMax = MAX(ID) FROM #tmpVBD
    
    EXEC Core_NewIDEx 'VBD1','VBD01',@lVBDid out,@lVBDMax
    SET @lVBDid = @lVBDid - @lVBDMax
 
    INSERT INTO VBD2(VBD01,VAF01,BBY01,VBD04,VBD05,VBD06,BCK01,VBD08,VBD09,BBY01A)
    SELECT @lVBDid+ID,VAF01,BBY01,VBD04,VBD05,VBD06,BCK01,VBD08,VBD09,BBY01A
    FROM #tmpVBD   
  end  
--医嘱状态
  Select @aVAIid = 0, @aVAIMax = 0

  Select @aVAIMax = Max(ID) From @TmpVAFsztmp

  EXEC Core_NewIDEx 'VBG1','VBG01',@aVAIid out, @aVAIMax
  Set @aVAIid = @aVAIid - @aVAIMax 
  
  INSERT INTO VBG1(VBG01,VAF01,VBG03,BCE03,VBG05,VBG06)
  Select @aVAIid + ID,VAF01,8,@lBCE03,@aDate,'住院临嘱发送停止'
  From @TmpVAFsztmp   
--发送手术医嘱到手术记录表
  if exists(SELECT e.* 
            FROM @TmpVAFsztmp e
            JOIN BAZ1 f ON e.BCK01B = f.BCK01
            WHERE (e.BDA01 = 'S' or e.BDA01 = '0') And e.VAF01A = 0 And f.BAU01 = '03' AND f.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 c.VAT01,a.VAA01,a.VAA07,a.VAP01,a.ACF01,3,99,'CM'+substring(@aStr,1,10 -len(c.VAT01))+cast((c.VAT01) AS varchar)	
      ,'手术记录单',a.BCK01F,a.BCK01A,a.BCK01C,a.BCE01A,a.BCE03A,@aDate,a.VAF36
    FROM @TmpVAFsztmp a
    join @tmpVAT c on c.VAF01 = a.VAF01

    INSERT INTO VAT1(VAT01, VAA01, VAA07, VAT04, ACF01, VAF01, VAT08,VAT16, BBX01, ACI02, BCK01,VAT33,AAB01)
    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,@aDate) else a.VAF14 end 
      ,b.VAF22, b.BBX01,b3.ACI02 ,a.BCK01B,a.VAF23,c1.AAB01
    FROM @tmpVAT d
    join @TmpVAFsztmp a on a.VAF01 = d.VAF01
    LEFT JOIN @TmpVAFsztmp b ON b.VAF01A = a.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
    
    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
--如果是护理，则更改护理级别及变动记录
  IF exists(SELECT a.* 
            FROM @TmpVAFsztmp a
            join #tmpVBD b on b.VAF01 = a.VAF01
            join BBY1 c on c.BBY01 = b.BBY01
            join AAG1 d on d.AAG01 = c.BCF01
            WHERE a.BDA01 = 'N' and c.BDN01 = 'N' and a.VAF01A = 0 and d.AAG01 <= 7 and a.VAP01 = 0
               And not exists(select * from VAE1 f where a.VAA07 = f.VAE01 and b.BBY01 = f.AAG01 ) )
  BEGIN
    Declare @tmpVBO Table(ID int IDENTITY(1,1),VAA07 int,BBY01 int)
    
    insert into @tmpVBO(VAA07,BBY01)
    SELECT a.VAA07,b.BBY01
    FROM @TmpVAFsztmp a
    join #tmpVBD b on b.VAF01 = a.VAF01
    join BBY1 c on c.BBY01 = b.BBY01
    join AAG1 d on d.AAG01 = c.BCF01
    WHERE a.BDA01 = 'N' and c.BDN01 = 'N' and a.VAF01A = 0 and d.AAG01 <= 7 and a.VAP01 = 0
       And exists(select * from VAE1 f where a.VAA07 = f.VAE01 and b.BBY01 <> f.AAG01 )
    
    update a
    set a.AAG01 = b.BBY01
    from VAE1 a
    join @tmpVBO b on b.VAA07 = a.VAE01

    UPDATE a 
    SET a.BCE03E = @lBCE03,a.VBO19 = @aDate,a.VBO20 = @aDate,a.VBO21 = 6
    FROM VBO1 a
    JOIN @tmpVBO b on a.VAA07 = b.VAA07
    WHERE isnull(a.VBO21,0)=0 AND a.VBO19 IS NULL
   
    Select @aVAIid = 0, @aVAIMax = 0

    Select @aVAIMax = Max(ID) From @tmpVBO

    EXEC Core_NewIDEx 'VBO1','VBO01',@aVAIid out, @aVAIMax
    Set @aVAIid = @aVAIid - @aVAIMax 

    INSERT INTO VBO1(VBO01,VAA01,VAA07,VBO04,VBO05,VBO06,BCK01A,BCK01B,BBY01A,BBY01B,BCQ04
        ,BCE03A,BCE03B,ABO01,BCE01,BCE03F,VBO21)
    SELECT @aVAIid + ID,a.VAA01,a.VAE01,@aDate,@aDate,6,a.BCK01C,a.BCK01D,a.AAG01,b.BBY01,a.BCQ04B
        ,a.BCE03B,a.BCE03C,a.ABO01,@lBCE01,@lBCE03,0
    FROM VAE1 a
    left JOIN BCQ1 b ON a.BCQ04B = b.BCQ04	and b.VAA01=a.VAA01 and b.BCQ13 >= 1 and b.BCQ13 <= 3 and b.BCQ13 <> 2
    join @tmpVBO c on a.VAE01 = c.VAA07
  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,2,1,f.BBX20,b.VAI04,a.BCK01A,a.BCK01B,a.BCK01E,a.BCK01D
    ,@lBCE01,@lBCE03,@aDate,a.VAA01,a.VAA07,0,a.VAI01,0,'护士站临嘱发送药品医嘱',0,0
  FROM #tmpVAJ a
  JOIN BBY1 c ON a.BBY01 = c.BBY01
  join #tmpVAI b on b.VAI01 = a.VAI01
  join @TmpVAFsztmp d on d.VAF01 = a.VAF01
  join @TmpVAFsztmp e on e.VAF01 = d.VAF01A
  join BBX1 f on f.BBX01 = e.BBX01
  where c.BDN01 <= '3' 
  
  update a set a.VAF10 = 8
  From VBZ1 a
  Join @TmpVAFsztmp b on a.VAF01 = b.VAF01  
  
  update a set a.VCL19=1
  From VCL1 a
  Join @TmpVAFsztmp b on b.VAF01 = a.VCL08
  where a.ACF01=2 and a.VCL02=1 
  
  --修改病人余额    
  if @VAA01 > 0
  begin
    set @aVAF38 = (select SUM(VAI23) FVAI23 from #tmpVAI)           
    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
   --调整住院情况
  update c
  set c.VAE22 = '3'
  from VAE1 c
  where exists(select * from (
    select a.VAA07 from @TmpVAFsztmp a join BBX1 b on b.BBX01 = a.BBX01 where a.BDA01='Z' and b.BBX13='9'
    union all
    select VAA07 from @TmpVAFsztmp where BDA01='0' 
        and (PATINDEX('病危',VAF22) > 0 or PATINDEX('病重',VAF22) > 0 or PATINDEX('危重',VAF22) > 0)
    ) s where s.VAA07 = c.VAE01)
--tiao shi ku cun
/*
  insert into DPT1_log(BBY01,BCK01,RefQty,SellQty,Quantity,SysDate,BCE01,Bce03,ACF01,VAJ25,DSK_ID, PurchasePrice, PresellPrice)
  select BBY01,BCK01,RefQty,SellQty,LSQty,@aDate,@lBCE01,@lbce03,2,VAJ25,DSK_ID, PurchasePrice, PresellPrice
  from #tmpDpt1log    
  */
--IF Object_id('tempdb..#TmpVAFsz') IS NOT NULL DROP TABLE #TmpVAFsz
  IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ
  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