if (exists (select * from sys.objects where name = 'ClinicAdvice_Del_InRest'))
    drop proc ClinicAdvice_Del_InRest
go 
create    PROC [dbo].[ClinicAdvice_Del_InRest]
  @lXml  varchar(8000)  
  ,@lacf01 tinyint
  ,@BCE03 varchar(64) = ''
	,@lucode varchar(20)
	,@luid VARCHAR(40)
	,@lmac VARCHAR(20)
AS
  
 --取得需要发送的医嘱id
  CREATE TABLE #TmpVafdf(VAF01 int primary key nonclustered, VAF01A int, BDA01 varchar(2),Rownr int, CBM01 int,VAF51 int,VAF10 tinyint)  
  
  INSERT INTO #TmpVafdf(VAF01)
  EXEC (@lXml)
 if @lacf01 <> 2 
 begin
  UPDATE a
  SET a.BDA01 = b.BDA01,a.VAF01A = b.VAF01A,a.Rownr = b.Rownr, a.CBM01 = b.CBM01,a.VAF51 = b.VAF51
  FROM #TmpVafdf a
  JOIN VAF1 b ON a.VAF01 = b.VAF01
  
  InSert Into #TmpVafdf(VAF01,CBM01,VAF51)
  Select a.VAF01,a.CBM01,a.VAF51
  From VAF1 a
    Join #TmpVafdf b on a.VAF01 = b.VAF01A
  Where b.BDA01 >= '1' And b.BDA01 <= '2' And a.VAF32 = 1 And b.Rownr = 1
  UNION ALL
  Select a.VAF01,a.CBM01,a.VAF51
  From VAF1 a
    Join #TmpVafdf b on a.VAF01A = b.VAF01
  Where b.BDA01 in ('8','S','E')
  
  update a
  set a.VAF10 = b.VAF10
  from #TmpVafdf a
  join VAF1 b on b.VAF01 = a.VAF01
 end 
 else
 begin
  UPDATE a
  SET a.BDA01 = b.BDA01,a.VAF01A = b.VAF01A,a.Rownr = b.Rownr, a.CBM01 = b.CBM01,a.VAF51 = b.VAF51
  FROM #TmpVafdf a
  JOIN VAF2 b ON a.VAF01 = b.VAF01 
  
  InSert Into #TmpVafdf(VAF01,CBM01,VAF51)
  Select a.VAF01,a.CBM01,a.VAF51
  From VAF2 a
    Join #TmpVafdf b on a.VAF01 = b.VAF01A
  Where b.BDA01 >= '1' And b.BDA01 <= '2' And a.VAF32 = 1 And b.Rownr = 1
  UNION ALL
  Select a.VAF01,a.CBM01,a.VAF51
  From VAF2 a
    Join #TmpVafdf b on a.VAF01A = b.VAF01
  Where b.BDA01 in ('8','S','E')
  
  update a
  set a.VAF10 = b.VAF10
  from #TmpVafdf a
  join VAF2 b on b.VAF01 = a.VAF01
 end   
  
  Declare @TmpVAFdno Table(ID int IDENTITY(1,1),VAF01 int,CBM01 int,VAF51 int)
  Declare @TmpCBMall Table(ID int IDENTITY(1,1),CBM01 int)
  
  Declare @tmpCBMd Table(ID int IDENTITY(1,1),CBM01 int) 
  Declare @tmpVBQd Table(VBQ01 int) 
  Declare @TmpVBG Table(ID int IDENTITY(1,1),VAF01 int)
  
  delete from #TmpVafdf where VAF10 >= 3
  
  insert into @TmpCBMall(CBM01)
  select distinct CBM01
  from #TmpVafdf
  
  insert into @TmpVAFdno(VAF01,CBM01,VAF51)
  select a.VAF01,a.CBM01,a.VAF51
  from VAF1 a
  join @TmpCBMall b on b.CBM01 = a.CBM01
  where @lacf01=1 and not exists(select * from #TmpVafdf c where c.VAF01 = a.VAF01)
  union all
  select a.VAF01,a.CBM01,a.VAF51
  from VAF2 a
  join @TmpCBMall b on b.CBM01 = a.CBM01
  where @lacf01=2 and not exists(select * from #TmpVafdf c where c.VAF01 = a.VAF01)
      
  Insert Into @tmpCBMd(CBM01)
  select CBM01
  from @TmpCBMall a
  where not exists(select * from @TmpVAFdno b where b.CBM01 = a.CBM01)  
    
  if not Exists(Select * from #TmpVafdf)
  begin
    --删除临时表
    IF Object_id('tempdb..#TmpVafdf') IS NOT NULL DROP TABLE #TmpVafdf  
    RAISERROR('医嘱都已执行，没有可以删除的医嘱！',16,1) with nowait
    return 1
  end

  Insert Into @tmpVBQd(VBQ01) 
  Select Distinct VAF51
  From #TmpVafdf a
  where not exists(select * from @TmpVAFdno b where b.VAF51 = a.VAF51)
  
  insert into @TmpVBG(VAF01)
  select VAF01
  from #TmpVafdf
  
  Declare @VBG01 int, @mID int, @aDate datetime,@VBG01a int 
  set @mID = isnull((select MAX(ID) from @TmpVBG),0)
  set @aDate = GETDATE()
  
  EXEC Core_NewIDEx 'VBG1','VBG01',@VBG01 out, @mID
  Set @VBG01 = @VBG01 - @mID 
  
  set @mID = isnull((select MAX(ID) from @tmpCBMd),0)
  EXEC Core_NewIDEx 'VBG1','VBG01',@VBG01a out, @mID
  Set @VBG01a = @VBG01a - @mID 
BEGIN TRAN  
  INSERT INTO VBG1(VBG01,VAF01,VBG03,BCE03,VBG05,VBG06)
  Select @VBG01 + a.ID,a.VAF01,b.VAF10,@BCE03,@aDate,'医嘱删除操作'
  From @TmpVBG a
  join #TmpVafdf b on b.VAF01 = a.VAF01
 if @lacf01 <> 2
 begin 
  Delete a From CBM1 a Join @tmpCBMd b on a.CBM01 = b.CBM01
  DELETE a FROM VBQ1 a Join @tmpVBQd b on a.VBQ01 = b.VBQ01
  DELETE a FROM VAF1 a JOIN #TmpVafdf b on a.VAF01 = b.VAF01 
  Delete a From VAO1 a Join @tmpCBMd b on a.VAF01 = B.CBM01 AND VAO22 = 1
 end 
 else
 begin
  Delete a From CBM2 a Join @tmpCBMd b on a.CBM01 = b.CBM01
  DELETE a FROM VBQ2 a Join @tmpVBQd b on a.VBQ01 = b.VBQ01
  DELETE a FROM VAF2 a JOIN #TmpVafdf b on a.VAF01 = b.VAF01 
  Delete a From VAO2 a Join @tmpCBMd b on a.VAF01 = B.CBM01 AND VAO22 = 1
 end
  INSERT INTO VBG1(VBG01,VAF01,VBG03,BCE03,VBG05,VBG06)
  select @VBG01a + ID,CBM01,0,@BCE03,@aDate,'医嘱删除医嘱单删除'
  from @tmpCBMd
  delete a FROM VCR1 a JOIN #TmpVafdf b on a.VAF01 = b.VAF01  
  update a set a.VAF10 = 5 from VBZ1 a join #TmpVafdf b on a.VAF01 = b.VAF01  

DECLARE @llbce02mac VARCHAR(30)
set @llbce02mac = @lucode+@lmac

	INSERT INTO RESTIN1(URIID,USRCODE,TABID) 
	VALUES (@luid,@llbce02mac,0)

COMMIT TRAN

--删除临时表
  IF Object_id('tempdb..#TmpVafdf') IS NOT NULL DROP TABLE #TmpVafdf