SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--=============================================================================================================
--Author     <...8065>
--Alter Date <...2016-09-14>
--Description<...结账作废过程>
--2011.09.13 部分退费后自动生成单据标记VAI17 
--2011.11.21 GY提供未发药药品销帐时更新其记帐数量
--2012.06.18 8065,医嘱部分退费，新生成明细记录原医嘱ID
--2013.07.30 8065,退费时记录原纪录DSK01
--2013.09.17 8065,退费时默认不写入票据号码
--2014.01.14 8065,部分退费时存在于VBY1中不退费的明细更新其VAJ01为新VAJ01
--2014.04.18 8065,虚拟库存的更新放到事务之外
--2014.04.21 8065,体检退费限制必须退费申请
--2015.03,09 8065,体检部分退费新生成单据备注复制原有备注信息
--2015.03.31 8065,退费时校验药品状态是否与前台传入一致
--2016.05.30 8065,退费时写入门诊费用附加表(销账记录及部分退费后待收记录)
--2016.07.29 8065,退费时已执行医技按药品方式更新处理状态
--2016.08.03 8065,退费时医技部分退费数量记录为原记录执行剩余数量-退费数量
--2016.08.08 8065,退费时医技处理状态与其他收费项目区分开来
--2016.08.19 8065,减小数据库锁粒度
--2016.08.19 8065,处理退费时会员优惠抵扣
--=============================================================================================================
ALTER PROC [HORate_VAK1_Cancel] @XML ntext,@VAK01 int out , @GenRemain int = 0 , @IsCancel tinyint = 0
AS 
DECLARE @iDOM int, @iret int
Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @XML
if @iret > 0
BEGIN
   exec sp_xml_removedocument @iDOM	
   RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @iret) with nowait
   return @iret
END 
--获取操作员基本信息
DECLARE @i int,
        @Value int,
        @Count int,
        @VEM01 int ,
        @VAI01 int,
        @VAA01 int,
        @VAA07 int,
	    @EmpId int,
	    @VAK01A int,
	    @BCK01B int,
	    @VAI04 varchar(20),
	    @VAK04 varchar(20),
        @EmpNo varchar(20),
        @EmpName varchar(20),
        @HostIp varchar(256),
        @HostName varchar(256),
        @DeptId int,
        @ErrId int,
        @DeptNo varchar(20),
        @DeptName varchar(20),
        @HostMac varchar(256),
        @FileVer varchar(20),
        @VAI22 numeric(18,4),
        @VAI23 numeric(18,4),
        @VAK13 datetime ,
        @CurDate datetime ,
        @lVAI01S varchar(512)
        
SELECT  @EmpId = EmpId , 
        @EmpNo = EmpNo ,
        @EmpName = EmpName ,
        @DeptId = DeptId ,
        @DeptNo = DeptNo ,
        @DeptName = DeptName ,
        @HostIp = HostIp ,
        @HostName = HostName ,
        @HostMac = HostMac ,
        @FileVer = FileVer 
  FROM OpenXml(@iDOM,'/Root/OperInfo',8) 
  WITH 
  (  
  	    EmpId int , 
        EmpNo varchar(20) ,
        EmpName varchar(20) ,
        DeptId int ,
        DeptNo varchar(20) ,
        DeptName varchar(20) ,
        HostIp varchar(256) ,
        HostName varchar(256) ,
        HostMac varchar(256) ,
        FileVer varchar(20) 
  )
  --获取要销账的明细数据
  SELECT * INTO #tmpVAJ2
  from OpenXml(@iDOM,'/Root/VAJ/Ie',8) 
  with 
  (
         VAJ01 int--收费记录ID
       , VAF01 int--医嘱ID
       , ROWNR int--次序
       , VAI01 int--单据ID
       , VAJ09 int
       , BDN01 varchar(2)--收费类型
       , BBY01 int--收费项目ID
       , VAJ22 smallint--特殊标志
       , VAJ23 smallint--剂数、中药剂数
       , VAJ24 numeric(18,4)--单量
       , VAJ25 numeric(18,4)--总量
       , VAJ26 smallint--急诊标志
       , VAJ27 int--婴儿费
       , BCK01D int--执行科室ID
       , VAJ30 numeric(9,4)--折扣分子
       , VAJ31 numeric(9,4)--折扣分母
       , VAJ59 numeric(18,6)--成本价
       , VAJ32 numeric(18,4)--全价
       , VAJ33 numeric(18,4)--单价
       , VAJ34 numeric(18,4)--包装
       , VAJ35 varchar(20)--单位
       , VAJ36 numeric(18,4)--全额、应收金额
       , VAJ37 numeric(18,4)--应收金额
       , VAJ38 numeric(18,4)--结账金额
       , VAJ39 smallint--自费标志
       , VAJ48 int 
       , VAJ53 tinyint
       , VAJ61 numeric(18,4)
       , IsBackFee smallint--是否退费
       , DSK01 int 
    )	
    
  If not exists(select * from #tmpVAJ2 )
  BEGIN
  	  EXEC sp_xml_removedocument @iDOM
      RAISERROR('提交错误，病人收费记录信息为空.', 16, 1) with nowait 
      RETURN 2
  END 
  --保存前台传入状态
  SELECT VAJ01 ,VAJ53 INTO #tmpVAJStatus 
  from #tmpVAJ2 
  where IsBackFee = -1 
  
  IF @IsCancel = 0
  BEGIN
    IF NOT EXISTS(SELECT * FROM #tmpVAJ2 WHERE IsBackFee = -1)
    BEGIN
  	  EXEC sp_xml_removedocument @iDOM
      RAISERROR('提交错误，没有要退费的数据.', 16, 1) with nowait 
      RETURN 2
    END
  END
  --移除XML文档
  exec sp_xml_removedocument @iDOM 
  --如果当前表中数据不存在，则把备份表中数据转移过来
  IF NOT EXISTS(SELECT * FROM VAK1 with(nolock) WHERE VAK01 = @VAK01)
  BEGIN
        EXEC @ErrId = HORate_Transfer_Data @VAK01 , 0
        IF @ErrId > 0
        BEGIN
           IF @@TRANCOUNT > 0 ROLLBACK TRAN
           RAISERROR('转移数据时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
           Return 1
        END
  END
  --如果不存在要作废的数据，则退出
  IF NOT EXISTS(SELECT * FROM VAK1 with(nolock) WHERE VAK01 = @VAK01)
  BEGIN
     RAISERROR('系统不存在要作废的数据，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
     Return 1
  END
  --体检病人限制退费数据必须经过退费申请
  SELECT @VAA07 = VAA07 from VAK1 with(nolock) where VAK01 = @VAK01 
  /*If EXISTS(select * from VAC1 where VAC01 = @VAA07 and VAC09 = -3) and EXISTS(select * from #tmpVAJ2 where VAF01>0)
  BEGIN
    If not exists(select * from #tmpVAJ2 a , VBY1 b  where a.VAJ01 = b.VAJ01 and b.VBY09 = 1)
    BEGIN
       RAISERROR('体检费用必须先通过体检退费申请.', 16, 1)  WITH NOWAIT
       Return 1
    END
  END*/
  /*=====================================================从数据库加载其他信息=============================================*/
  Update a   
    SET a.ROWNR = b.ROWNR ,
        a.VAF01 = b.VAF01 ,
        a.VAJ09 = b.VAJ09 ,
        a.BDN01 = b.BDN01 ,
        a.BBY01 = b.BBY01 ,
        a.VAJ22 = b.VAJ22 ,
        a.VAJ23 = b.VAJ23 ,
        a.VAJ24 = b.VAJ24 ,
        a.VAJ26 = b.VAJ26 ,
        a.VAJ27 = b.VAJ27 ,
        a.BCK01D = b.BCK01D ,
        a.VAJ30 = b.VAJ30 ,
        a.VAJ31 = b.VAJ31 ,
        a.VAJ59 = b.VAJ59 ,
        a.VAJ32 = b.VAJ32 ,
        a.VAJ33 = b.VAJ33 ,
        a.VAJ34 = b.VAJ34 ,
        a.VAJ35 = b.VAJ35 ,
        a.VAJ36 = b.VAJ36 ,
        a.VAJ37 = b.VAJ37 ,
        a.VAJ38 = b.VAJ38 ,
        a.VAJ39 = b.VAJ39 ,
        a.VAJ48 = b.VAJ48 ,
        a.VAJ53 = b.VAJ53 ,
        a.VAJ61 = b.VAJ61 ,
        a.DSK01 = b.DSK01 
  from #tmpVAJ2 a 
    join VAJ1 b with(nolock) on a.VAJ01 = b.VAJ01 
  --where a.ROWNR is NOT null  
  If exists(select * from #tmpVAJ2 a,#tmpVAJStatus b where  a.VAJ01 = b.VAJ01 and a.VAJ53 <> b.VAJ53)
  BEGIN     
     RAISERROR('提交错误，费用执行状态发生变化,请关闭退费窗口，重新操作退费.', 16, 1) with nowait 
     RETURN 2
  END
  --如果是药品，转换为基本单位及价格
  /*Update a 
    SET a.VAJ32 = b.BBY25 ,
        a.VAJ35 = b.BBY08 ,
        a.VAJ33 = b.BBY25 * c.VAJ30 / c.VAJ31 ,
        a.VAJ25 = a.VAJ25 * c.VAJ34 
  from #tmpVAJ2 a
    join BBY1 b on a.BBY01 = b.BBY01 
    join VAJ1 c on a.VAJ01 = c.VAJ01 
  where c.BDN01 <= '3' 
  
  select * from #tmpVAJ2 
  return 2*/  
  /*=====================================================生成销账临时表===================================================*/
  SELECT IDENTITY(int,1,1) AS ID , * INTO #tmpVAI2 
  from (SELECT * from VAI1 with(nolock) where VAI01 in (SELECT VAI01 from VAJ1 with(nolock) where VAK01 = @VAK01 )) a
  
  SELECT @i = 0, @Count = Count(1) from #tmpVAI2
  Exec @ErrId = Core_NewIdEx 'VAI1' , 'VAI01' , @Value out , @Count 
  
  If @ErrId > 0
  BEGIN 	 
 	 RETURN 1
  END
  --更新冲销单据冲销ID
  Update #tmpVAI2 
    Set VAI01A = VAI01
  --生成冲销单据
  While @i < @Count 
  BEGIN
     SET @VAI04 = '000000000'
     SET @VAI04 = @VAI04 + Cast(@Value - @Count + @i + 1 AS VARCHAR)
     SET @VAI04 = 'I' +  SUBSTRING(@VAI04, Len(@VAI04) - 9, 10)
      
     Update #tmpVAI2
       Set VAI01B = Case ISNULL(VAI01B , 0) when 0 then VAI01 else VAI01B end ,
           VAI01C = VAI01 ,
           VAI01 = @Value - @Count + @i + 1 ,
           VAI04 = @VAI04 
     Where [ID] = @i + 1
     SET @i = @i + 1
  END 
  --生成冲销明细记录
  SELECT IDENTITY(int,1,1) AS ID  ,* INTO #tmpNewVAJ1
  from (SELECT * from VAJ1 with(nolock) where VAK01 = @VAK01 ) a  
    
  SELECT @i = 0, @Count = Count(1) from #tmpNewVAJ1 
  Exec @ErrId = Core_NewIdEx 'VAJ1' , 'VAJ01' , @Value out , @Count 
  If @ErrId > 0
  BEGIN 	 
 	 RETURN 1
  END
  --更新冲销明细冲销ID
  Update #tmpNewVAJ1
    Set VAJ09 = VAJ01 
  --更新冲销明细关联ID
  Update #tmpNewVAJ1
    Set VAJ01B = Case ISNULL(VAJ01B , 0) when 0 then VAJ01 else VAJ01B end  
  where VAJ53 = 1 and VAJ48 > 0 
  
  While @i < @count 
  BEGIN
     Update #tmpNewVAJ1 
       Set VAJ01 = @Value - @Count + @i + 1
     Where [ID] = @i + 1
     Set @i = @i + 1
  END
  --更新冲销明细单据ID
  Update a
    Set a.VAI01 = b.VAI01
  from #tmpNewVAJ1 a
    join #tmpVAI2 b on a.VAI01 = b.VAI01A 
  --如果原明细状态为待执行,冲销明细与原明细更新为不用执行
  Update #tmpNewVAJ1 
    SET VAJ48 = 0,
        VAJ53 = 2
  where VAJ48 = 0 and VAJ53 = 0    
  --如果原明细状态为已执行状态,冲销明细更新为不用执行(不需要执行)
  Update a  
    SET --VAJ48 = 0 ,
        VAJ53 = 1
  from #tmpNewVAJ1 a
    join #tmpVAJ2 b on a.VAJ09 = b.VAJ01    
  where a.VAJ48 > 0 and a.VAJ53 = 1  and b.IsBackFee = 0
  --如果原明细状态为已执行状态,冲销明细更新为待执行(需要执行)
  --已执行医技退费项目处理为拒绝执行
  Update a  
    SET VAJ48 = 0,
        VAJ53 = case when a.BDN01 <='4' then 0 else 2 end
  from #tmpNewVAJ1 a 
    join #tmpVAJ2 b on a.VAJ09 = b.VAJ01    
  where a.VAJ48 > 0 and a.VAJ53 = 1  and b.IsBackFee = -1 
  --找出不退费明细生成新的待收费明细数据 
  SELECT VAJ01 INTO #tmpVAJ3 
  from (select VAJ01 from VAJ1 b with(nolock) where  b.VAK01 = @VAK01 and b.VAJ01 not in (select VAJ01 from #tmpVAJ2) ) a
  
  SELECT IDENTITY(int,1,1) AS ID ,VAJ01 OriginalVAJ01 ,* INTO #tmpRemainVAJ1 
  from
    (
      SELECT b.* from #tmpVAJ2 a 
        JOIN VAJ1 b with(nolock) on a.VAJ01 = b.VAJ01 
      where a.IsBackFee = 0 
      UNION 
      SELECT b.* from VAJ1 b with(nolock)
        JOIN #tmpVAJ3 a on a.VAJ01 = b.VAJ01 
    ) a 
  
  --更新关联ID
  update #tmpRemainVAJ1
    set VAJ01B = Case ISNULL(VAJ01B , 0) when 0 then VAJ01 else VAJ01B end 
  where VAJ48 > 0 and VAJ53 = 1 
    
  SELECT @i = 0 , @Count = Count(1) from  #tmpRemainVAJ1 
  Exec @ErrId = Core_NewIdEx 'VAJ1' , 'VAJ01' , @Value out , @Count 
  If @ErrId > 0
  BEGIN 	 
 	 RETURN 1
  END
  
  While @i < @Count    
  BEGIN
    Update #tmpRemainVAJ1
      Set VAJ01 = @Value - @Count + @i + 1
    Where [ID] = @i + 1
    SET @i = @i + 1 
  END 
  --如果是已执行，更新状态为不用执行
  Update #tmpRemainVAJ1
    SET --VAJ48 = 0 ,
        VAJ53 = 1
  where VAJ48 > 0 and VAJ53 = 1
  --如果是待执行，更新状态为待执行
  Update #tmpRemainVAJ1
    SET VAJ48 = 0,
        VAJ53 = 0
  where VAJ48 = 0 and VAJ53 = 0 
  --查找出部分退费的明细
  SELECT IDENTITY(int,1,1) AS ID , VAJ01 OriginalVAJ01 ,* INTO #tmpPartVAJ1
  from 
    (
      SELECT b.* , b.VAJ25 as VEG03 
      from #tmpVAJ2 a
        join VAJ1 b with(nolock) on a.VAJ01 = b.VAJ01 
      where a.VAJ25 <> b.VAJ25 and a.IsBackFee = -1 
    ) a
  --如果存在部分退费    
  If Exists(select * from #tmpPartVAJ1)
  BEGIN
    --更新部分退费明细剩余部分数量及金额
    Update a
       SET a.VAJ32 = c.VAJ32,
           a.VAJ33 = c.VAJ33,
           a.VAJ25 = b.VAJ25 - c.VAJ25,
           a.VEG03 = d.VEG03 - c.VAJ25 ,
           a.VAJ36 = (b.VAJ25 - c.VAJ25) * c.VAJ32,
           a.VAJ37 = (b.VAJ25 - c.VAJ25) * c.VAJ33,
           a.VAJ38 = (b.VAJ25 - c.VAJ25) * c.VAJ33,
           a.VAJ61 = (b.VAJ25 - c.VAJ25) * c.VAJ33   
    From #tmpPartVAJ1 a
       JOIN VAJ1 b with(nolock) on a.VAJ01 = b.VAJ01 
       JOIN #tmpVAJ2 c on b.VAJ01 = c.VAJ01  
       JOIN VEG1 d with(nolock) on d.VAJ01 = b.VAJ01 
       
    --生成新的明细ID         	
    SELECT @i = 0 , @Count = COUNT(*) from #tmpPartVAJ1
    Exec @ErrId = Core_NewIdEx 'VAJ1' , 'VAJ01' , @Value out, @Count 
    
    If @ErrId > 0
    BEGIN 	 
       RETURN 1
    END
    
    --更新关联ID
    update #tmpPartVAJ1
       set VAJ01B = Case ISNULL(VAJ01B , 0) when 0 then VAJ01 else VAJ01B end 
    where VAJ48 > 0 and VAJ53 = 1 
  
    While @i < @Count 
    BEGIN
       Update #tmpPartVAJ1 
         Set VAJ01 = @Value - @Count + @i + 1
       where [ID] = @i + 1
       SET @i = @i + 1
    END             
    --执行状态更新为待执行
    --医技收费项目剩余执行数量为0时，状态设置为拒绝执行
    Update #tmpPartVAJ1 
      SET VAJ48 = 0 ,
          VAJ53 = case when (ISNULL(VEG03, 0) > 0 or BDN01 <= '3') then 0 else 2 end 
    --where BDN01 <= '3'
    --更新除中草药外的单量
    Update #tmpPartVAJ1 
      SET VAJ24 = VAJ25 
    where BDN01 <> '03'
  END 
  --生成待收费单据
  SELECT IDENTITY(int,1,1) AS ID , a.* INTO #tmpPartVAI1
  from 
    ( 
      SELECT a.VAI01 VAI01O ,b.* from 
        (SELECT VAI01 from 
            (SELECT VAI01 from #tmpRemainVAJ1
             UNION
             SELECT VAI01 from #tmpPartVAJ1) a 
        ) a
      JOIN VAI1 b with(nolock) on a.VAI01 = b.VAI01    
    ) a
    
  If Exists(select * from #tmpPartVAI1)
  BEGIN
    SELECT @i = 0 , @Count = Count(1) from #tmpPartVAI1
    Exec @ErrId = Core_NewIdEx 'VAI1' , 'VAI01' , @Value out , @Count 
    
    If @ErrId > 0
    BEGIN 	 
 	   RETURN 1
    END
    
    SET @lVAI01S = '' ;
    While @i < @Count 
    BEGIN      
        SET @VAI04 = '000000000'
        SET @VAI04 = @VAI04 + Cast(@Value - @Count + @i + 1 AS VARCHAR)
        SET @VAI04 = 'T' +  SUBSTRING(@VAI04, Len(@VAI04) - 9, 10)     	
        
       SELECT @VAI22 = 0 , @VAI23 = 0 ,@VAI01 = VAI01 from #tmpPartVAI1 where [ID] = @i + 1             
       
       If Exists(select * from #tmpRemainVAJ1 where VAI01 = @VAI01 )
       BEGIN
         SELECT @VAI22 = @VAI22 + SUM(VAJ36) , @VAI23 = @VAI23 + SUM(VAJ38) from #tmpRemainVAJ1 where VAI01 = @VAI01  
       END 
       
       If Exists(select * from #tmpPartVAJ1 where VAI01 = @VAI01 )
       BEGIN
       	 SELECT @VAI22 = @VAI22 + SUM(VAJ36) , @VAI23 = @VAI23 + SUM(VAJ38) from #tmpPartVAJ1 where VAI01 = @VAI01
       END
       
       Update #tmpPartVAI1 
         set VAI01B = Case ISNULL(VAI01B , 0) when 0 then VAI01 else VAI01B end ,
             VAI01C = VAI01 ,
             VAI01 = @Value - @Count + @i + 1 ,
             VAI04 = @VAI04 ,
             VAI22 = @VAI22 ,
             VAI23 = @VAI23 
       where [ID] = @i + 1
       
       SET @lVAI01S = @lVAI01S + ',' + CAST(@Value - @Count + @i + 1 as varchar(20))
       SET @i = @i + 1        
    END 
    --更新明细中单据号码
    Update a
     Set a.VAI01 = b.VAI01 
    from #tmpRemainVAJ1 a
     join #tmpPartVAI1 b on a.VAI01 = b.VAI01O 
     
    Update a
     Set a.VAI01 = b.VAI01 
    from #tmpPartVAJ1 a
     join #tmpPartVAI1 b on a.VAI01 = b.VAI01O          
  END
  --生成退费的药品表(没发药)
  SELECT BBY01 , BCK01D , VAJ25 , DSK01 INTO #tmpNoExecDrag
  from (SELECT a.BBY01 , a.BCK01D , SUM(a.VAJ25) VAJ25 , a.DSK01 
        from #tmpVAJ2 a
          join VAJ1 b with(nolock) on b.VAJ01 = a.VAJ01 
        where a.IsBackFee = -1 and b.BDN01 <= '3'  
        group by a.BBY01 , a.BCK01D , a.DSK01
        ) a

   --汇总待发药单据到业务通知表中
   SELECT IDENTITY(int,1,1) AS ID,VAI01 , BCK01D , BCK01E  INTO #TmpForMedicine
   from (SELECT VAI01 , BCK01D , BCK01E , BDN01 , VAJ53 , VAJ01B from #tmpPartVAJ1 
         UNIOn ALL 
         SELECT VAI01 , BCK01D , BCK01E , BDN01 , VAJ53 , VAJ01B from #tmpRemainVAJ1 ) a 
   where BDN01 <= '3' and VAJ53 = 0 and VAJ01B = 0
   group by VAI01 , BCK01D , BCK01E         
  --检索临时表
  /*select * from #tmpVAJ2
  select * from #tmpVAI2 
  select * from #tmpPartVAI1 
  select * from #tmpNewVAJ1
  select * from #tmpRemainVAJ1
  select * from #tmpPartVAJ1  
  select * from #tmpNoExecDrag
  Return 2*/
  --如果数据已经作废，则提示
  IF EXISTS(SELECT * FROM VAK1 WHERE VAK01A = @VAK01)
  BEGIN
    RAISERROR('该结帐记录已经退费，请刷新门诊收费管理界面查看.', 16, 1)  WITH NOWAIT
    Return 1
  END
  
  SET @CurDate = GETDATE() 
  
  BEGIN TRAN --开始事务
  /*===========================================================保存冲销数据==================================================*/  
  --插入结账记录
  Exec @ErrId = Core_NewIDEx 'VAK1','VAK01',@VAK01A out 
  If @ErrId > 0
  BEGIN
     IF @@TRANCOUNT > 0 ROLLBACK TRAN 	 
 	 RETURN 1
  END 
  
  SET @VAK04 = '000000000'
  SET @VAK04 = @VAK04 + Cast(@VAK01A AS VARCHAR)
  SET @VAK04 = 'K' +  SUBSTRING(@VAK04, Len(@VAK04) - 9, 10)                
        
  INSERT INTO VAK1(VAK01, VAA01, VAA07, VAI04, FAB03, VAK06, VAK07, VAK08, VAK09, VAK10, BCE02A, BCE03A, VAK13, VAK01A, VAK15,
  	            VAK16, VAK17, VAK18, VAK19 ,VAK21 , VAK23 , VAK24 , BCE01A , VAK28 )
  	            
  SELECT @VAK01A, VAA01, VAA07, @VAK04, '', 2, -VAK07 , -VAK08 , 0, 0, '', '', @CurDate, @VAK01, @CurDate,
               @CurDate, '门诊退费', 0, 2, -VAK21, -VAK23 , -VAK24 , 0 , @VAK01A from VAK1 where VAK01 = @VAK01   
               	
  IF @@ERROR > 0 --如果发生错误则回滚数据
  BEGIN
     IF @@TRANCOUNT > 0 ROLLBACK TRAN
     RAISERROR('冲病人结帐单据时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
     Return 1
  END  
  --插入冲销单据
  INSERT INTO VAI1 (VAI01, VAA01, VAA07, VAI04, VAI05, BCK01A, BCK01B, BCE02A, BCE03A, BCE02B, BCE03B, VAI12, VAI13, VAI14, 
          VAI01A, VAI16, VAI17, VAI18 ,CBM01 , ACF01 , VAI22 , VAI23 , VAI01B , VAI01C , VAK01)
          
  SELECT  VAI01, VAA01, VAA07, VAI04, '门诊退费', BCK01A, BCK01B, BCE02A, BCE03A, @EmpNo, @EmpName, @CurDate, @CurDate,'门诊退费' , 
          VAI01A , 4 , VAI17, 4 ,CBM01 , 1 , -VAI22 , -VAI23 , VAI01B , VAI01C , @VAK01A from #tmpVAI2 
  
  IF @@ERROR > 0 --如果发生错误则回滚数据     	            
  BEGIN

     IF @@TRANCOUNT > 0 ROLLBACK TRAN
     RAISERROR('冲病人收费单据时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
     Return 1
  END
  --生成剩余单据
  INSERT INTO VAI1 (VAI01, VAA01, VAA07, VAI04, VAI05, BCK01A, BCK01B, BCE02A, BCE03A, BCE02B, BCE03B, VAI12, VAI13, VAI14, 
          VAI01A, VAI16, VAI17, VAI18 ,CBM01 , ACF01 , VAI22 , VAI23 , VAI01B , VAI01C )
          
  SELECT  VAI01, VAA01, VAA07, VAI04, '', BCK01A, BCK01B, BCE02A, BCE03A, @EmpNo, @EmpName, @CurDate, @CurDate,VAI14 , 
          VAI01A , 1 , 2, 2 ,CBM01 , 1 , VAI22 , VAI23 , VAI01B , VAI01C 
  from #tmpPartVAI1 
          
  IF @@ERROR > 0 --如果发生错误则回滚数据     	            
  BEGIN
     IF @@TRANCOUNT > 0 ROLLBACK TRAN
     RAISERROR('冲病人收费单据时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
     Return 1
  END          
  --插入冲销明细
  INSERT INTO VAJ1(VAJ01, VAA01, VAA07, VAJ04, VAJ05, ROWNR, VAJ01A, VAJ01B, VAJ09, VAJ10, VAI01, VAF01, VAK01, ACF01,
      	  VAJ15, BCK01A, BCK01B, BDN01, BBY01, BCJ02, VAJ21, VAJ22, VAJ23, VAJ24, VAJ25, VAJ26, VAJ27, VAJ28, 
      	  VAJ29, VAJ30, VAJ31, VAJ32, VAJ33, VAJ34, VAJ35, VAJ36, VAJ37, VAJ38, VAJ39, VAJ40, VAJ41, BCE03A, 
      	  BCK01C, BCE02B, BCE03B, VAJ46, VAJ47, VAJ48, BCK01D, BCE03C, VAJ51, VAJ52, VAJ53, VAJ54, BCE02D, 
      	  BCE03D, VAJ57, FAB03, VAJ59 ,VAJ61 ,VAJ62 , BCK01E , DSK01) 
      	                    
  SELECT VAJ01, VAA01, VAA07, VAJ04, 4, ROWNR, VAJ01A, VAJ01B, VAJ09, VAJ10, VAI01, VAF01, @VAK01A, ACF01, 
      	  VAJ15, BCK01A, BCK01B, BDN01, BBY01, BCJ02, VAJ21, VAJ22, VAJ23, VAJ24, -VAJ25, VAJ26, VAJ27, VAJ28,
      	  VAJ29, VAJ30, VAJ31, VAJ32, VAJ33, VAJ34, VAJ35, -VAJ36, -VAJ37, -VAJ38, VAJ39, -VAJ40, -VAJ41, BCE03A,
      	  BCK01C, BCE02B, BCE03B, @CurDate , @CurDate, VAJ48, BCK01D, BCE03C, VAJ51, VAJ52, VAJ53, '门诊退费' , '',
      	  '', '' , FAB03, VAJ59, -VAJ61 ,VAJ62 , BCK01E , DSK01 
  from #tmpNewVAJ1 
      	  
  IF @@ERROR > 0  --如果发生错误则回滚数据      	            
  BEGIN
     IF @@TRANCOUNT > 0 ROLLBACK TRAN 
     RAISERROR('冲病人收费单据时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
     Return 1
  END 
  --插入剩余明细
  INSERT INTO VAJ1(VAJ01, VAA01, VAA07, VAJ04, VAJ05, ROWNR, VAJ01A, VAJ01B, VAJ09, VAJ10, VAI01, VAF01, VAK01, ACF01,
      	  VAJ15, BCK01A, BCK01B, BDN01, BBY01, BCJ02, VAJ21, VAJ22, VAJ23, VAJ24, VAJ25, VAJ26, VAJ27, VAJ28, 
      	  VAJ29, VAJ30, VAJ31, VAJ32, VAJ33, VAJ34, VAJ35, VAJ36, VAJ37, VAJ38, VAJ39, VAJ40, VAJ41, BCE03A, 
      	  BCK01C, BCE02B, BCE03B, VAJ46, VAJ47, VAJ48, BCK01D, BCE03C, VAJ51, VAJ52, VAJ53, VAJ54, BCE02D, 
      	  BCE03D, VAJ57, FAB03, VAJ59 ,VAJ61 ,VAJ62 , BCK01E , DSK01) 
      	                    
  SELECT VAJ01, VAA01, VAA07, 2, 1, ROWNR, VAJ01A, VAJ01B, VAJ09, VAJ10, VAI01, VAF01, 0 , ACF01, 
      	  VAJ15, BCK01A, BCK01B, BDN01, BBY01, BCJ02, VAJ21, VAJ22, VAJ23, VAJ24, VAJ25, VAJ26, VAJ27, VAJ28,
      	  VAJ29, VAJ30, VAJ31, VAJ32, VAJ33, VAJ34, VAJ35, VAJ36, VAJ37, VAJ38, VAJ39, VAJ40, VAJ41, BCE03A,
      	  BCK01C, BCE02B, BCE03B, @CurDate, @CurDate, VAJ48, BCK01D, BCE03C, VAJ51, VAJ52, VAJ53, '门诊退费' , @EmpNo ,
      	  @EmpName , '' , FAB03, VAJ59, VAJ61 ,VAJ62 , BCK01E , DSK01 
  from #tmpRemainVAJ1 
      	  
  IF @@ERROR > 0  --如果发生错误则回滚数据      	            
  BEGIN
     IF @@TRANCOUNT > 0 ROLLBACK TRAN
     RAISERROR('冲病人收费单据时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
     Return 1
  END 
  --插入部分退费明细
  INSERT INTO VAJ1(VAJ01, VAA01, VAA07, VAJ04, VAJ05, ROWNR, VAJ01A, VAJ01B, VAJ09, VAJ10, VAI01, VAF01, VAK01, ACF01,
      	  VAJ15, BCK01A, BCK01B, BDN01, BBY01, BCJ02, VAJ21, VAJ22, VAJ23, VAJ24, VAJ25, VAJ26, VAJ27, VAJ28, 
      	  VAJ29, VAJ30, VAJ31, VAJ32, VAJ33, VAJ34, VAJ35, VAJ36, VAJ37, VAJ38, VAJ39, VAJ40, VAJ41, BCE03A, 
      	  BCK01C, BCE02B, BCE03B, VAJ46, VAJ47, VAJ48, BCK01D, BCE03C, VAJ51, VAJ52, VAJ53, VAJ54, BCE02D, 
      	  BCE03D, VAJ57, FAB03, VAJ59 ,VAJ61 ,VAJ62 , BCK01E , DSK01) 
      	                    
  SELECT VAJ01, VAA01, VAA07, 2 , 1 , ROWNR, VAJ01A, VAJ01B, VAJ09, VAJ10, VAI01, VAF01, 0 , ACF01, 
      	  VAJ15, BCK01A, BCK01B, BDN01, BBY01, BCJ02, VAJ21, VAJ22, VAJ23, VAJ24, VAJ25, VAJ26, VAJ27, VAJ28,
      	  VAJ29, VAJ30, VAJ31, VAJ32, VAJ33, VAJ34, VAJ35, VAJ36, VAJ37, VAJ38, VAJ39, VAJ40, VAJ41, BCE03A,
      	  BCK01C, BCE02B, BCE03B, @CurDate, @CurDate, VAJ48, BCK01D, BCE03C, VAJ51, VAJ52, VAJ53, '门诊退费' , @EmpNo ,
      	  @EmpName , '' , FAB03, VAJ59, VAJ61 ,VAJ62 , BCK01E , DSK01 
 from #tmpPartVAJ1 
      	  
  IF @@ERROR > 0  --如果发生错误则回滚数据      	            
  BEGIN
     IF @@TRANCOUNT > 0 ROLLBACK TRAN
     RAISERROR('冲病人收费单据时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
     Return 1
  END 
  --部分退费时存在于VBY1中不退费的明细更新其VAJ01为新VAJ01
  If exists(select * from #tmpRemainVAJ1)
  BEGIN
    Update a with(rowlock)
      SET a.VAJ01 = b.VAJ01 
    from VBY1 a
      join #tmpRemainVAJ1 b on b.VAJ01B = a.VAJ01 
    where a.VBY09 = 0
  END
  --更改原记录状态
  Update VAJ1 with(rowlock)
    set VAJ05 = 3 
  where VAK01 = @VAK01 
  
  --更新原单据状态
  Update VAI1 with(rowlock)
    set VAI16 = 3
  where VAI01 in
  (select a.VAI01 from VAI1 a
     join VAJ1 b on a.VAI01 = b.VAI01 
   where b.VAK01 = @VAK01 
  ) 
  --更新原结账明细未发药状态的未拒绝发药
  Update VAJ1 with(rowlock)
    set VAJ53 = 2 
  where VAK01 = @VAK01 and VAJ48 = 0 and VAJ53 = 0    
  /*=================================================如果存在部分退费的数据，写入业务单据通知表====================================================*/
  If exists(select * from #tmpPartVAI1 )
  BEGIN
    SELECT @i = 0 , @Count = COUNT(1) from #tmpPartVAI1 
    SELECT @VAA01 = VAA01 , @VAA07 = VAA07 , @VAK13 = VAK13 from VAK1 where VAK01 = @VAK01A  
    WHILE @i < @Count 
    BEGIN
      SELECT @VAI01 = VAI01 , @VAI04 = VAI04 from #tmpPartVAI1 
      WHERE [ID] = @i + 1
      SELECT top 1 @BCK01B = BCK01B from VAJ1 where VAI01 = @VAI01
      Exec @ErrId = HOPublic_VBX1_Update @VAA01 , @VAA07 , 1 , 1 , 2 , 0 , 0 , @VAI04 , @VAK13 , @VAK01 , @VAI01 ,
         '门诊部分退费生成单据' , 1 , 1 , @EmpId , @EmpName , @BCK01B , @DeptId , @DeptId  
         
      If @ErrId > 0 
      BEGIN
        IF @@TRANCOUNT > 0 ROLLBACK TRAN
        RAISERROR('写入业务单据通知时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
        Return 1
      END
      SET @i = @i + 1
    END   
  END
/*====================================================汇总待发药的单据列表(暂时只处理门诊)=========================================================*/
If exists(select * from #TmpForMedicine)  
BEGIN
  Declare @BCK01D int ,
          @BCK01E int ,          
          @InDept int 
          
  SELECT top 1 @InDept = BCK01B from #tmpPartVAJ1 
  If ISNULL(@InDept ,0) = 0
    SELECT top 1 @InDept = BCK01B from #tmpRemainVAJ1 
     
  SELECT @i = 0 , @Count = COUNT(1) from #TmpForMedicine 
  while @i < @Count 
  BEGIN
    SELECT @BCK01D = BCK01D , @BCK01E = BCK01E , @VAI01 = VAI01 
    from #TmpForMedicine 
    where [ID] = @i + 1
    SELECT @VAI04 = VAI04 from #tmpPartVAI1 where VAI01 = @VAI01 
    --如果业务通知表中不存在数据，则写入 
    If not exists(select * from VBX1 with(nolock) where VBX18 = @VAI01 and VBX03 = 2 and BCK01D = @BCK01D and BCK01C = @BCK01E)
    BEGIN
       Exec @ErrId = HOPublic_VBX1_Update @VAA01 , @VAA07 , 1 , 2 , 2 , 2 , -1 , @VAI04 , @CurDate , @VAI01 , 0 ,'划价生成待发药单据' , 0 , 0 ,
          @EmpId , @EmpName , @InDept , @BCK01E , @BCK01D , 0
    END
    SET @i = @i + 1 
  END
 END   
 /*=================================================================插入门诊费用附加表==================================================================*/
 --目前只处理门诊划价费用(非医嘱及体检费用)
  If exists(select * from #tmpNewVAJ1 where ACF01 = 1 and VAF01 = 0)
    or exists(select * from #tmpRemainVAJ1 where ACF01 = 1 and VAF01 = 0)
    or exists(select * from #tmpPartVAJ1 where ACF01 = 1 and VAF01 = 0)
  BEGIN
    INSERT INTO VEG1(VAJ01 , VEG03 )
    SELECT VAJ01 , -VAJ25 from #tmpNewVAJ1 where VAF01 = 0 and ACF01 = 1 and VAF01 = 0
    UNION ALL
    SELECT VAJ01 , VAJ25 from #tmpRemainVAJ1 where VAF01 = 0 and ACF01 = 1 and VAF01 = 0
    UNION ALL
    SELECT VAJ01 , VEG03 from #tmpPartVAJ1 where VAF01 = 0 and ACF01 = 1 and VAF01 = 0
    
    IF @@ERROR > 0   --如果发生错误，则回滚数据
    BEGIN
      IF @@TRANCOUNT > 0 ROLLBACK TRAN
      RAISERROR('插入门诊费用附表时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
      Return 1
    END 
  END
  /*==========================================================处理会员会员账号抵扣部分==================================================================*/
  If exists(select * from sys_parameters where ProductID =100 and ProgramID = 103010 and ParamNo = 303 and Value = '1')
  BEGIN 
    SELECT IDENTITY(int , 1 ,1) as ID ,0 as VEM01 ,* INTO #tmpNewVEM1
    FROM (SELECT OriginalVAJ01 VAJ01A , a.VAJ01 ,VAJ36 - VAJ38 VEM04 , b.VBU01 
          FROM #tmpRemainVAJ1 a
            JOIN VEM1 b with(nolock) on b.VAJ01 = a.OriginalVAJ01 
          WHERE b.DFLAG = 0 and b.VEM06 = 1
          UNION ALL 
          SELECT OriginalVAJ01 VAJ01A , a.VAJ01 ,VAJ36 - VAJ38 VEM04 , b.VBU01 
          from #tmpPartVAJ1 a 
            JOIN VEM1 b with(nolock) on b.VAJ01 = a.OriginalVAJ01 
          WHERE b.DFLAG = 0 and b.VEM06 = 1
          ) a
        
    Exec @ErrId = HORate_CardGift_Update '' , 2 , 1 , @EmpId , @EmpName , 0 , 0 , 0 , @VAK01A 
    
    If @ErrId > 0 or @@ERROR > 0
    BEGIN
       IF @@TRANCOUNT > 0 ROLLBACK TRAN
       RAISERROR('退回优惠会员账户抵扣时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
       Return 1
    END
    
    If exists(select * from #tmpNewVEM1)      
    BEGIN       
      SELECT @i = 0 , @Count = COUNT(*) from #tmpNewVEM1 
      Exec Core_NewId_VEM01 @VEM01 out , @Count 
      
      while @i < @Count 
      BEGIN
        Update #tmpNewVEM1 
          SET VEM01 = @VEM01 - @Count + @i + 1
        where ID = @i + 1
        
        SET @i = @i + 1
      END
      
      INSERT INTO VEM1(VEM01 , VAJ01 , VAJ01A , VBU01 , VEM04 , VEM05 , VEM06 , VEM11 , BCE01 , BCE03 , DFLAG)
      SELECT VEM01 , VAJ01 , VAJ01A , VBU01 , VEM04 , 0 , 1 , @CurDate , @EmpId , @EmpName , 0
      from #tmpNewVEM1 
      
      If @ErrId > 0 or @@ERROR > 0
      BEGIN
         IF @@TRANCOUNT > 0 ROLLBACK TRAN
         RAISERROR('处理优惠会员账户抵扣时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
         Return 1
      END    
            
      Exec @ErrId = HORate_CardGift_Update '' , 1 , 1 , @EmpId , @EmpName , 0 , 0 , 0 , @VAK01A , @lVAI01S 
          
      If @ErrId > 0 or @@ERROR > 0
      BEGIN
        IF @@TRANCOUNT > 0 ROLLBACK TRAN
        RAISERROR('优惠会员账户抵扣时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
        Return 1
      END
    END
           
  END
      	
  COMMIT TRAN 
  --================================================如果存在需要退药的明细(未发药)，更新其虚拟库存=====================================================
  If exists(select * from #tmpNoExecDrag)
   and not exists(select * from SYS_Parameters where ProductID = 9999 and ProgramID = 9999 and ParamNo = 46 and value ='1')
  BEGIN
    Update a with(rowlock)
        set a.SellQty = a.SellQty - b.VAJ25 ,
          a.Quantity = a.Quantity + b.VAJ25  
    from DPT1 a
      join #tmpNoExecDrag b on b.BBY01 = a.BBY01 and b.BCK01D = a.BCK01 and a.DSK_ID = b.DSK01 
  END
  --===============================================================删除临时表========================================================================
  IF Object_id('tempdb..#tmpVAJ2') IS NOT NULL DROP TABLE #tmpVAJ2
  IF Object_id('tempdb..#tmpVAI2') IS NOT NULL DROP TABLE #tmpVAI2
  IF Object_id('tempdb..#tmpPartVAI1') IS NOT NULL DROP TABLE #tmpPartVAI1
  IF Object_id('tempdb..#tmpNewVAJ1') IS NOT NULL DROP TABLE #tmpNewVAJ1
  IF Object_id('tempdb..##tmpNewVEM1') IS NOT NULL DROP TABLE #tmpNewVEM1
  IF Object_id('tempdb..#tmpRemainVAJ1') IS NOT NULL DROP TABLE #tmpRemainVAJ1
  IF Object_id('tempdb..#tmpPartVAJ1') IS NOT NULL DROP TABLE #tmpPartVAJ1  
  IF Object_id('tempdb..#tmpNoExecDrag') IS NOT NULL DROP TABLE #tmpNoExecDrag
  IF Object_id('tempdb..#tmpVAJStatus') IS NOT NULL DROP TABLE #tmpVAJStatus