医保退费主要流程

1.系统初始化Init

 with QryInvoice do
  begin
    Close;
    ParamByName('DanJuID').AsString:=edtDjid.Text;
    Open;
    vJiuZhenID:=FieldByName('JiuZhenID').AsInteger;
    GetClinicInfo(vJiuZhenID);//获得就诊信息
    pnlDjrq.Caption:=FieldByName('SerialNo').AsString;
    pnlSkr.Caption:=FieldByName('ShouKuanRen').AsString;
    pnlSsje.Caption:=FormatFloat('0.00', FieldByName('ShiShouZLJE').AsFloat);
    pnlJzdh.Caption:=FieldByName('JieZhangDH').AsString;
    end;

2.

select a.JiuZhenID,a.ChuFangLH,a.SerialNo,a.YaoPinID,a.YaoMing,a.HeSuanBM,a.GuiGe,
       a.DanWei,a.DanJia,a.PiJia,a.JinJia,a.Jine,a.ZiLiBL,
       convert(Numeric(10,2),a.ShuLiang*a.TieShu*a.TianShu) as ShuLiang,
       a.LingYaoKSBM,a.DanJuID,c.FaYaoZT,a.ZhiXingBM,a.TaoCanBM,
       TuiShu=case when b.ShuLiang is null then 0 else b.ShuLiang end,
       IsAppend=convert(bit,0)
from Out_RecipeDetail a, Out_RetCharge b, Out_DistriMedicStatus c
where a.DanJuID=:DanJuID and a.DanJuID*=b.DanJuID and
      a.YaoPinID*=b.YaoPinID and a.SerialNo*=b.YaoPinXL and a.DanJuID=c.DanJuID
      and a.LingYaoKSBM=c.KeShiBM
order by a.LingYaoKSBM
///sql
  with QryDrug do
  begin
    Close;
    ParamByName('DanJuID').AsString:=aDanJuID;
    Open;
  end;

3.

******************************************/
/*未结帐且不为同一操作员或已结帐的医保退票处理*/
/******************************************/
CREATE PROCEDURE pZ_Out_RetChargeHi(@DanJuID varchar(10),@JiuZhenID int,@KeShiBM varchar(10),
@XingMing varchar(10),@ShouKuanBM varchar(10),@YiShengBM varchar(10),@BingLiHao varchar(20),@YiLiaoDH varchar(12))
AS
declare @TuiFeiSJ datetime,@NewDanJuID varchar(10),@SerialNo varchar(23),@RiQi varchar(10),@OldJiuZhenID int
set @TuiFeiSJ=convert(datetime,getdate(),120)
set @SerialNo=convert(varchar(23),getdate(),121)
set @RiQi=convert(varchar(10),getdate(),120)
begin tran
  --删除发票临时打印表
  delete Out_TempPrintInvoice where ShouKuanYBM=@ShouKuanBM
  if @@Error<>0
  begin
    RollBack Tran
    Return 16 
  end 
  update Out_invoice set TuiFeiSJ=@TuiFeiSJ where DanJuID=@DanJuID
  if @@Error<>0
  begin
    RollBack Tran
    Return 0--更新发票主表
  end
  select @OldJiuZhenID=JiuZhenID from Out_Invoice where DanJuID=@DanJuID
 /* insert into Out_ClinicInfo select @SerialNo,BingLiHao,@JiuZhenID,BingRenLB,LeiBieBM,XingMing,KeShiBM,JiuZhenRQ,@ShouKuanBM from Out_clinicInfo where JiuZhenID=@OldJiuZhenID
  if @@Error<>0
  begin
    RollBack Tran
    Return 20--插入就诊主表失败
  end  */
  update Out_RetCharge set TuiFeiBZ=1 where DanJuID=@DanJuID
  if @@Error<>0
  begin
    RollBack Tran
    Return 1--更新退费记录表
  end 
--Assay:
/*update DR_Store 
	set ZhangMianShu=ZhangMianShu+a.ShuLiang*a.TieShu*a.TianShu
	from Out_RecipeDetail a,DR_Store b
	where 
		a.YaoPinID=b.YaoPinID and 
		b.KeShiBM=a.LingYaoKSBM and 
		a.DanJuID=@DanJuID */

update DR_Store 
	set ZhangMianShu=ZhangMianShu+a.ShuLiang
	from (select YaoPinID,LingYaoKSBM,
              Sum(convert(Numeric(10,2),ShuLiang*TieShu*TianShu)) as ShuLiang 
              from Out_RecipeDetail where
              DanJuID=@DanJuID Group by YaoPinID,LingYaoKSBM) a,DR_Store b
	where 
		a.YaoPinID=b.YaoPinID and 
		b.KeShiBM=a.LingYaoKSBM
/*  declare Out_Ret Cursor
  for
  select YaoPinID,LingYaoKSBM,ShuLiang*TieShu*TianShu as ShuLiang from Out_RecipeDetail where DanJuID=@DanJuID
  open Out_Ret
  declare @YaoPinID varchar(15),@LingYaoKSBM varchar(10),@ShuLiang Numeric(10,2)
  Fetch Next from Out_Ret into @YaoPinID,@LingYaoKSBM,@ShuLiang
  while (@@Fetch_Status=0)
  begin
    update DR_Store set ZhangMianShu=ZhangMianShu+@ShuLiang where YaoPinID=@YaoPinID and KeShiBM=@LingYaoKSBM
    if @@Error<>0
    begin
      Close Out_Ret
      Deallocate Out_Ret
      RollBack Tran
      Return 2--更新药房帐面库存
    end
    Fetch Next from Out_Ret into @YaoPinID,@LingYaoKSBM,@ShuLiang
  end
  Close Out_Ret
  Deallocate Out_Ret
*/
  
  insert into Out_Recipe select @SerialNo,@BingLiHao,@JiuZhenID,2,@RiQi,@YiShengBM,@KeShiBM,@ShouKuanBM,-YingShouJE,-YingShouJE,1,0,0
                         from Out_Invoice where DanJuID=@DanJuID
  if @@Error<>0
  begin
    RollBack Tran
    Return 3--生成负处方主表
  end
  exec @NewDanJuID=pSys_GetBill @ShouKuanBM,1
  if @@Error<>0
  begin
    RollBack Tran
    Return -1--生成单据号错误
  end
  if @NewDanJuID=-1 
  begin      
    RollBack Tran
    Return -2--单据已用完
  end
  --插入单据进入发票临时表
  insert into Out_TempPrintInvoice (ShouKuanYBM,DanJuID) values (@ShouKuanBM,@NewDanJuID)
  if @@Error<>0
  begin
    RollBack Tran
    Return 15
  end
   --插入医保结算主表
   insert into Z_Out_Clinic (YiLiaoDH, DanJuID, YiBaoKaHao, SheBaoHao,RenYuanLB, 
   JiuZhenSJ, ZongFY, YiBaoFY, ZiFeiFY, ZiLiFY, NDXTZiLiFY,
   XianFuFY,DangNianZF, LiNianZF,BuZhuZF,BuChongZF, TongChouZF, JiuZhuZF, 
   GongBuKFZF,LiXiuYFZF, ZiFuZF,XianJinZF, BingZhongMC, JiaoYiSJ,BeiZhu)
   select @YiLiaoDH, @NewDanJuID, YiBaoKaHao, SheBaoHao,RenYuanLB, @SerialNo, -ZongFY,
   -YiBaoFY, -ZiFeiFY, -ZiLiFY,- NDXTZiLiFY, -XianFuFY,-DangNianZF, -LiNianZF,-BuZhuZF,
   -BuChongZF, -TongChouZF, -JiuZhuZF, -GongBuKFZF, -LiXiuYFZF,-ZiFuZF, -XianJinZF,
   BingZhongMC, @SerialNo,BeiZhu
   from Z_Out_Clinic where YiLiaoDH=@YiLiaoDH and DanJuID=@DanJuID
   if @@Error <>0
   begin
     Rollback Tran
     Return 21
   end 


  declare Out_Rec Cursor 
  for
  select SerialNo,YaoPinID,HeSuanBM,ZhiXingBM,YaoMing,GuiGe,DanWei,DanJia,PiJia,JinJia,ShuLiang,TieShu,TianShu,JinE,ZiLiBL,ZiLiJE,LingYaoKSBM,TaoCanBM
  from Out_RecipeDetail
  where DanJuID=@DanJuID
  open Out_Rec
  declare @YaoPinID3 varchar (15),@HeSuanBM varchar(5),@ZhiXingBM varchar(10),@YaoMing varchar(30),@GuiGe varchar(15),
          @DanWei varchar(5),@DanJia Numeric(8,4),@PiJia Numeric(8,4),@JinJia Numeric(8,4),@OldSerialNo varchar(23),
          @ShuLiang3 Numeric(10,2),@TieShu3 int,@TianShu3 int,@JinE Numeric(10,2),@ZiLiBL Numeric(10,2),@ZiLiJE Numeric(10,2),@LingYaoKSBM3 varchar(10),@TaoCanBM int
  Fetch Next from Out_Rec into @OldSerialNo,@YaoPinID3,@HeSuanBM,@ZhiXingBM,@YaoMing,@GuiGe,@DanWei,@DanJia,@PiJia,@JinJia,@ShuLiang3,@TieShu3,@TianShu3,@JinE,@ZiLiBL,@ZiLiJE,@LingYaoKSBM3,@TaoCanBM
  while (@@Fetch_Status=0)
  begin
    insert into Out_RecipeDetail
          (SerialNo,JiuZhenID,ChuFangLH,DanJuID,YaoPinID,HeSuanBM,ZhiXingBM,YaoMing,GuiGe,DanWei,
           DanJia,PiJia,JinJia,ShuLiang,TieShu,TianShu,JinE,ZiLiBL,ZiLiJE,LingYaoKSBM) 
    values(@SerialNo,@JiuZhenID,2,@NewDanJuID,@YaoPinID3,@HeSuanBM,@ZhiXingBM,@YaoMing,@GuiGe,@DanWei,
           @DanJia,@PiJia,@JinJia,-@ShuLiang3,@TieShu3,@TianShu3,-@JinE,@ZiLiBL,-@ZiLiJE,@LingYaoKSBM3)
    if @@Error<>0
    begin
      Close Out_Rec
      Deallocate Out_Rec
      RollBack Tran
      Return 4--生成负处方明细表
    end
    insert into Z_Out_ClinicDetail 
          (SerialNo,YiLiaoDH,DanJuID,YiYuanBM,YiBaoBM,YaoPinMC,ShouFeiLBYY,ShouFeiLBYB,JiXing,
           GuiGe,DanWei,DanJia,ShuLiang,ZiFuBL,ZongFY,ZiFeiFY,ZiLiFY,YiBaoFY)
    select @SerialNo,@YiLiaoDH,@NewDanJuID,YiYuanBM,YiBaoBM,YaoPinMC,ShouFeiLBYY, 
	   ShouFeiLBYB,JiXing,GuiGe,DanWei,DanJia,-ShuLiang,ZiFuBL,-ZongFY,-ZiFeiFY,-ZiLiFY,-YiBaoFY
	   from Z_Out_ClinicDetail where YiLiaoDH=@YiLiaoDH and DanJuID=@DanJuID and SerialNo=@OldSerialNo    
    if @@Error<>0
    begin
      Close Out_Rec
      Deallocate Out_Rec
      RollBack Tran
      Return 4--生成医保负处方明细表
    end
    insert into Z_Out_ClinicDetailExt
           (Serialno,JiuZhenID,ChuFangLH,DanJuID,YaoPinID,PinCi,YongLiang)
    select @SerialNo,JiuZhenID,2,@NewDanJuID,YaoPinID,PinCi,YongLiang
           from Z_Out_ClinicDetailExt where SerialNo=@OldSerialNo and JiuZhenID=@OldJiuZhenID and YaoPinID=@YaoPinID3
           and DanJuId=@DanJuID
    if @@Error<>0
    begin
      Close Out_Rec
      Deallocate Out_Rec
      RollBack Tran
      Return 4--生成医保负处方明细扩展表
    end

    set @SerialNo=convert(varchar(23),dateAdd(ms,10,@SerialNo),121)
    Fetch Next from Out_Rec into @OldSerialNo,@YaoPinID3,@HeSuanBM,@ZhiXingBM,@YaoMing,@GuiGe,@DanWei,@DanJia,@PiJia,@JinJia,@ShuLiang3,@TieShu3,@TianShu3,@JinE,@ZiLiBL,@ZiLiJE,@LingYaoKSBM3,@TaoCanBM
  end
  Close Out_Rec
  Deallocate Out_Rec
  insert into Out_DistriMedicStatus select @SerialNo,@NewDanJuID,KeShiBM,FaYaoCZ,1,FaYaoRQ,FaYaoZT from Out_DistriMedicStatus where DanJuID=@DanJuID 
  if @@Error<>0
  begin
    RollBack Tran
    Return 5--生成负的发药状态表
  end
  insert into Out_Invoice select SerialNo,@JiuZhenID,@NewDanJuID,@RiQi,XingMing,-YingShouJE,-ShiShouJE,-YingShouZLJE,-ShiShouZLJE,@ShouKuanBM,1,@SerialNo,Null
                          from Out_Invoice where DanJuID=@DanJuID
  if @@Error<>0
  begin
    RollBack Tran
    Return 6--生成负的发票主表
  end
  insert into Out_InvoiceDetail select @SerialNo,@JiuZhenID,@NewDanJuID,HeSuanBM,-YingShouJE,-ShiShouJE,-YingShouZLJE,-ShiShouZLJE from Out_InvoiceDetail where DanJuID=@DanJuID
  if @@Error<>0
  begin
    RollBack Tran
    Return 7--生成负的发票明细表
  end 
  
  update Out_Recipe Set JieSuanBZ=1 where JiuZhenID=@JiuZhenID and ChuFangLH=1
  --更新处方主表的结算标志为1
  if @@Error<>0
  begin      
    RollBack Tran
    Return 8--更新正处方主表错误
  end
  
  declare @Num int
  select @Num=count(*) from Out_Recipe where JiuZhenID=@JiuZhenID and ChuFangLH=1
  if @Num=1
  begin

    exec @NewDanJuID=pSys_GetBill @ShouKuanBM,1
      if @@Error<>0
      begin
        RollBack Tran
        Return -1--生成单据号错误
      end
      if @NewDanJuID=-1 
      begin      
        RollBack Tran
        Return -2--单据已用完
      end
    declare @YingShouJE Numeric(10,2),@ShiShouJE Numeric(10,2),@YingShouZLJE Numeric(10,2),@ShiShouZLJE Numeric(10,2)
    set @YingShouJE=0.00
    set @ShiShouJE=0.00
    set @YingShouZLJE=0.00
    set @ShiShouZLJE=0.00   
    declare Out_Invo Cursor    
    for
    select HeSuanBM,convert(Numeric(10,2),sum(JinE)),sum(ZiLiJE)
    from Out_RecipeDetail where JiuZhenID=@JiuZhenID and ChuFangLH=1 Group by HeSuanBM
    open Out_Invo
    declare @YingShouJE2 Numeric(10,2),@ShiShouJE2 Numeric(10,2),@HeSuanBM2 varchar(30),@YingShouZLJE2 Numeric(10,2),@ShiShouZLJE2 Numeric(10,2)
    Fetch Next from Out_Invo into @HeSuanBM2,@YingShouJE2,@YingShouZLJE2
    while (@@Fetch_Status=0)
    begin
      exec pSys_GetRound @YingShouJE2,@ShiShouJE2 out
      if @ShiShouJE2=-1
      begin
        Close Out_Invo
        Deallocate Out_Invo
        RollBack Tran
        Return -3
      end
      exec pSys_GetRound @YingShouZLJE2,@ShiShouZLJE2 out
      if @ShiShouZLJE2=-1
      begin
        Close Out_Invo
        Deallocate Out_Invo
        RollBack Tran
        Return -3
      end  
      set @YingShouJE=@YingShouJE+@YingShouJE2
      set @ShiShouJE=@ShiShouJE+@ShiShouJE2
      set @YingShouZLJE=@YingShouZLJE+@YingShouZLJE2
      set @ShiShouZLJE=@ShiShouZLJE+@ShiShouZLJE2
      
      --生成发票明细表
      insert into Out_InvoiceDetail values(@SerialNo,@JiuZhenID,@NewDanJuID,@HeSuanBM2,@YingShouJE2,@ShiShouJE2,@YingShouZLJE2,@ShiShouZLJE2) 
      if @@Error<>0
      begin
        Close Out_Invo
        Deallocate Out_Invo
        RollBack Tran
        Return 11--生成发票明细表错误
      end
        Fetch Next from Out_Invo into @HeSuanBM2,@YingShouJE2,@YingShouZLJE2
    end
    Close Out_Invo
    Deallocate Out_Invo
    
     update Out_RecipeDetail Set DanJuID=@NewDanJuID where JiuZhenID=@JiuZhenID and ChuFangLH=1
     --更新处方明细表单据号为当前单据号
     if @@Error<>0
     begin            
       RollBack Tran
       Return 9--更新处方明细表错误
     end
      --插入单据进入发票临时表
     insert into Out_TempPrintInvoice (ShouKuanYBM,DanJuID) values (@ShouKuanBM,@NewDanJuID)
     if @@Error<>0
     begin
       RollBack Tran
       Return 15
     end
    --生成发票主表
    insert into Out_Invoice (SerialNo,JiuZhenID,DanJuID,DanJuRQ,XingMing,YingShouJE,ShiShouJE,YingShouZLJE,ShiShouZLJE,ShouKuanYBM,JieSuanZT,
                           TuiFeiSJ,JieZhangDH)
                   values (@SerialNo,@JiuZhenID,@NewDanJuID,@RiQi,@XingMing,@YingShouJE,@ShiShouJE,@YingShouZLJE,@ShiShouZLJE,@ShouKuanBM,1,
                           null,null)
    if @@Error<>0
    begin           
     RollBack Tran
     Return 10--生成发票主表错误
    end    

    insert into Out_DistriMedicStatus 
    select distinct @SerialNo,@NewDanJuID,a.LingYaoKSBM,b.FaYaoCZ,1,b.FaYaoRQ,b.FayaoZT 
    from Out_RecipeDetail a,Out_DistriMedicStatus b 
    where a.DanJuID=@NewDanJuID and b.DanJuID=@DanJuID and a.LingYaoKSBM=b.KeShiBM
    if @@Error<>0
    begin          
      RollBack Tran
      Return 12--生成发药状态表
    end

--Assay:
/*update DR_Store 
	set ZhangMianshu=ZhangMianshu-a.ShuLiang*a.TieShu*a.TianShu
	from Out_RecipeDetail a,DR_Store b
	where a.YaoPinID=b.YaoPinID and
		a.LingYaoKSBM=b.KeShiBM and 
		a.JiuZhenID=@JiuZhenID */
  update DR_Store 
	set ZhangMianShu=ZhangMianShu-a.ShuLiang
	from (select YaoPinID,LingYaoKSBM,
              Sum(convert(Numeric(10,2),ShuLiang*TieShu*TianShu)) as ShuLiang 
              from Out_RecipeDetail where
              JiuZhenID=@JiuZhenID and ChuFangLH=1 Group by YaoPinID,LingYaoKSBM) a,DR_Store b
	where 
		a.YaoPinID=b.YaoPinID and 
		b.KeShiBM=a.LingYaoKSBM
/*    declare Out_Rep Cursor
    for
    select YaoPinID,ShuLiang,TieShu,TianShu,LingYaoKSBM from Out_RecipeDetail where JiuZhenID=@JiuZhenID and ChuFangLH=1
    open Out_Rep
    declare @YaoPinID2 varchar(15),@ShuLiang2 Numeric(10,2),@TieShu int,@TianShu int,@LingYaoKSBM2 varchar(10)
    Fetch Next from Out_Rep into @YaoPinID2,@ShuLiang2,@TieShu,@TianShu,@LingYaoKSBM2
    while (@@Fetch_Status=0) 
    begin
      declare @ZhangMianShu Amount
      set @ZhangMianShu=convert(numeric(10,2),@ShuLiang2*@TieShu*@TianShu)
      update DR_Store set ZhangMianShu=ZhangMianShu-@ZhangMianShu where YaoPinID=@YaoPinID2 and KeShiBM=@LingYaoKSBM2
      if @@Error<>0
      begin
        Close Out_Rep
        Deallocate Out_Rep
        RollBack Tran
        Return 13--更新药房库存错误
      end
      Fetch Next from Out_Rep into @YaoPinID2,@ShuLiang2,@TieShu,@TianShu,@LingYaoKSBM2 
    end
    Close Out_Rep
    Deallocate Out_Rep 
*/
  end
commit tran
Return 14
GO