if (exists (select * from sys.objects where name = 'Clinic_Advice_Stop_InRest'))
    drop proc Clinic_Advice_Stop_InRest
go 
create   PROC [dbo].[Clinic_Advice_Stop_InRest]
  @lXml  nText
  ,@lBCE01 int 
  ,@lBCE03 varchar(20)  --操作员
  ,@lType tinyint  --1:住院医师工作站 2:护士、手术工作站
	,@lucode varchar(20)
	,@luid VARCHAR(40)
	,@lmac VARCHAR(20)
AS
  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
select @lBCE03 = BCE03 from BCE1 where BCE01 = @lBCE01
  Create Table #TmpVAFsp (ID int IDENTITY(1,1),VAF01 int, BDA01 varchar(2), VAF01A int,Rownr int,VAF47 datetime)
  --取得需要发送的医嘱id
  INSERT INTO #TmpVAFsp(VAF01,VAF47)
  SELECT VAF01,VAF47
  from OpenXml(@iDOM,'/Root/VAF1/Ie',8)
    WITH (VAF01 int
          ,VAF47 datetime)	
    
  If not exists(select * from #TmpVAFsp)
  BEGIN
    exec sp_xml_removedocument @iDOM
    IF Object_id('tempdb..#TmpVAFsp') IS NOT NULL DROP TABLE #TmpVAFsp
    RAISERROR('提交文档错误，医嘱信息为空', 16, 1) with nowait
    RETURN 2
  END
  exec sp_xml_removedocument @iDOM
--判断是否有临嘱还没退费
  Declare @aSmsg varchar(1024)  
  if Exists(select * FROM #TmpVAFsp a
            WHERE Exists(Select b.* from VAF2 b where a.VAF01=b.VAF01 AND b.VAF10 <> 3))
  begin
    Select @aSmsg = b.VAF22
    From #TmpVAFsp a
      Join VAF2 b on a.VAF01 = b.VAF01 and b.VAF10 <> 3
    
    IF Object_id('tempdb..#TmpVAFsp') IS NOT NULL DROP TABLE #TmpVAFsp
    Set @aSmsg = '"'+@aSmsg + '"医嘱已经停止，请返回刷新条件重新选择！'
    RAISERROR(@aSmsg, 16, 1) with nowait
    RETURN 3
  end  
  
  UPDATE a
  SET a.BDA01 = b.BDA01,a.VAF01A = b.VAF01A,a.Rownr = b.Rownr
  FROM #TmpVAFsp a
    JOIN VAF2 b ON a.VAF01 = b.VAF01
  WHERE b.VAF10 = 3 

  InSert Into #TmpVAFsp(VAF01,VAF01A,BDA01,Rownr,VAF47)  
  Select a.VAF01,a.VAF01A,a.BDA01,a.Rownr,b.VAF47 
  From VAF2 a
    Join #TmpVAFsp b on a.VAF01 = b.VAF01A
  Where b.BDA01 >= '1' And b.BDA01 <= '2' And b.Rownr = 1
  UNION ALL
  Select a.VAF01,a.VAF01A,a.BDA01,a.Rownr,b.VAF47 
  From VAF2 a
    Join #TmpVAFsp b on a.VAF01A = b.VAF01
  Where b.BDA01 in ('8','S','E')

  Declare @lMaxId int, @lMinId int, @lDate Datetime, @lID int
  Set @lDate = Getdate()
  declare  @lTxt varchar(50)
  set @lTxt = '医嘱停止' 

  Select @lMaxId = Max(ID), @lMinId = Min(ID) From #TmpVAFsp
BEGIN TRAN   
  IF @lType = 1
    UPDATE a 
    SET a.VAF10 = 8, a.BCE03D = @lBCE03, a.BCE01D = @lBCE01, a.VAF47 = b.VAF47
    From VAF2 a
      Join #TmpVAFsp b on a.VAF01 = b.VAF01
  ELSE
   	UPDATE a 
    SET a.VAF10 = 8, a.BCE03D = a.BCE03A, a.BCE01D = a.BCE01A, a.BCE03E = @lBCE03,a.BCE01E = @lBCE01, a.VAF47 = b.VAF47 
    From VAF2 a
      Join #TmpVAFsp b on a.VAF01 = b.VAF01

  EXEC Core_NewIDEx 'VBG1','VBG01',@lID out,@lMaxId
  SET @lID = @lID - @lMaxId
    
  INSERT INTO VBG1(VBG01,VAF01,VBG03,BCE03,VBG05,VBG06)
  SELECT @lID+(ID-@lMinId+1),VAF01,8,@lBCE03,@lDate,@lTxt
  FROM #TmpVAFsp

  Update a  Set a.VAF10 = 8
  From VBZ1 a
  Join #TmpVAFsp 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