医保退费主要流程

1.系统初始化Init

 with QryInvoice dobeginClose;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 anda.YaoPinID*=b.YaoPinID and a.SerialNo*=b.YaoPinXL and a.DanJuID=c.DanJuIDand a.LingYaoKSBM=c.KeShiBM
order by a.LingYaoKSBM
///sqlwith QryDrug dobeginClose;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=@ShouKuanBMif @@Error<>0beginRollBack TranReturn 16 end update Out_invoice set TuiFeiSJ=@TuiFeiSJ where DanJuID=@DanJuIDif @@Error<>0beginRollBack TranReturn 0--更新发票主表endselect @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=@OldJiuZhenIDif @@Error<>0beginRollBack TranReturn 20--插入就诊主表失败end  */update Out_RetCharge set TuiFeiBZ=1 where DanJuID=@DanJuIDif @@Error<>0beginRollBack TranReturn 1--更新退费记录表end 
--Assay:
/*update DR_Store set ZhangMianShu=ZhangMianShu+a.ShuLiang*a.TieShu*a.TianShufrom Out_RecipeDetail a,DR_Store bwhere a.YaoPinID=b.YaoPinID and b.KeShiBM=a.LingYaoKSBM and a.DanJuID=@DanJuID */update DR_Store set ZhangMianShu=ZhangMianShu+a.ShuLiangfrom (select YaoPinID,LingYaoKSBM,Sum(convert(Numeric(10,2),ShuLiang*TieShu*TianShu)) as ShuLiang from Out_RecipeDetail whereDanJuID=@DanJuID Group by YaoPinID,LingYaoKSBM) a,DR_Store bwhere a.YaoPinID=b.YaoPinID and b.KeShiBM=a.LingYaoKSBM
/*  declare Out_Ret Cursorforselect YaoPinID,LingYaoKSBM,ShuLiang*TieShu*TianShu as ShuLiang from Out_RecipeDetail where DanJuID=@DanJuIDopen Out_Retdeclare @YaoPinID varchar(15),@LingYaoKSBM varchar(10),@ShuLiang Numeric(10,2)Fetch Next from Out_Ret into @YaoPinID,@LingYaoKSBM,@ShuLiangwhile (@@Fetch_Status=0)beginupdate DR_Store set ZhangMianShu=ZhangMianShu+@ShuLiang where YaoPinID=@YaoPinID and KeShiBM=@LingYaoKSBMif @@Error<>0beginClose Out_RetDeallocate Out_RetRollBack TranReturn 2--更新药房帐面库存endFetch Next from Out_Ret into @YaoPinID,@LingYaoKSBM,@ShuLiangendClose Out_RetDeallocate Out_Ret
*/insert into Out_Recipe select @SerialNo,@BingLiHao,@JiuZhenID,2,@RiQi,@YiShengBM,@KeShiBM,@ShouKuanBM,-YingShouJE,-YingShouJE,1,0,0from Out_Invoice where DanJuID=@DanJuIDif @@Error<>0beginRollBack TranReturn 3--生成负处方主表endexec @NewDanJuID=pSys_GetBill @ShouKuanBM,1if @@Error<>0beginRollBack TranReturn -1--生成单据号错误endif @NewDanJuID=-1 begin      RollBack TranReturn -2--单据已用完end--插入单据进入发票临时表insert into Out_TempPrintInvoice (ShouKuanYBM,DanJuID) values (@ShouKuanBM,@NewDanJuID)if @@Error<>0beginRollBack TranReturn 15end--插入医保结算主表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,BeiZhufrom Z_Out_Clinic where YiLiaoDH=@YiLiaoDH and DanJuID=@DanJuIDif @@Error <>0beginRollback TranReturn 21end declare Out_Rec Cursor forselect SerialNo,YaoPinID,HeSuanBM,ZhiXingBM,YaoMing,GuiGe,DanWei,DanJia,PiJia,JinJia,ShuLiang,TieShu,TianShu,JinE,ZiLiBL,ZiLiJE,LingYaoKSBM,TaoCanBMfrom Out_RecipeDetailwhere DanJuID=@DanJuIDopen Out_Recdeclare @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 intFetch Next from Out_Rec into @OldSerialNo,@YaoPinID3,@HeSuanBM,@ZhiXingBM,@YaoMing,@GuiGe,@DanWei,@DanJia,@PiJia,@JinJia,@ShuLiang3,@TieShu3,@TianShu3,@JinE,@ZiLiBL,@ZiLiJE,@LingYaoKSBM3,@TaoCanBMwhile (@@Fetch_Status=0)begininsert 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<>0beginClose Out_RecDeallocate Out_RecRollBack TranReturn 4--生成负处方明细表endinsert 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,-YiBaoFYfrom Z_Out_ClinicDetail where YiLiaoDH=@YiLiaoDH and DanJuID=@DanJuID and SerialNo=@OldSerialNo    if @@Error<>0beginClose Out_RecDeallocate Out_RecRollBack TranReturn 4--生成医保负处方明细表endinsert into Z_Out_ClinicDetailExt(Serialno,JiuZhenID,ChuFangLH,DanJuID,YaoPinID,PinCi,YongLiang)select @SerialNo,JiuZhenID,2,@NewDanJuID,YaoPinID,PinCi,YongLiangfrom Z_Out_ClinicDetailExt where SerialNo=@OldSerialNo and JiuZhenID=@OldJiuZhenID and YaoPinID=@YaoPinID3and DanJuId=@DanJuIDif @@Error<>0beginClose Out_RecDeallocate Out_RecRollBack TranReturn 4--生成医保负处方明细扩展表endset @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,@TaoCanBMendClose Out_RecDeallocate Out_Recinsert into Out_DistriMedicStatus select @SerialNo,@NewDanJuID,KeShiBM,FaYaoCZ,1,FaYaoRQ,FaYaoZT from Out_DistriMedicStatus where DanJuID=@DanJuID if @@Error<>0beginRollBack TranReturn 5--生成负的发药状态表endinsert into Out_Invoice select SerialNo,@JiuZhenID,@NewDanJuID,@RiQi,XingMing,-YingShouJE,-ShiShouJE,-YingShouZLJE,-ShiShouZLJE,@ShouKuanBM,1,@SerialNo,Nullfrom Out_Invoice where DanJuID=@DanJuIDif @@Error<>0beginRollBack TranReturn 6--生成负的发票主表endinsert into Out_InvoiceDetail select @SerialNo,@JiuZhenID,@NewDanJuID,HeSuanBM,-YingShouJE,-ShiShouJE,-YingShouZLJE,-ShiShouZLJE from Out_InvoiceDetail where DanJuID=@DanJuIDif @@Error<>0beginRollBack TranReturn 7--生成负的发票明细表end update Out_Recipe Set JieSuanBZ=1 where JiuZhenID=@JiuZhenID and ChuFangLH=1--更新处方主表的结算标志为1if @@Error<>0begin      RollBack TranReturn 8--更新正处方主表错误enddeclare @Num intselect @Num=count(*) from Out_Recipe where JiuZhenID=@JiuZhenID and ChuFangLH=1if @Num=1beginexec @NewDanJuID=pSys_GetBill @ShouKuanBM,1if @@Error<>0beginRollBack TranReturn -1--生成单据号错误endif @NewDanJuID=-1 begin      RollBack TranReturn -2--单据已用完enddeclare @YingShouJE Numeric(10,2),@ShiShouJE Numeric(10,2),@YingShouZLJE Numeric(10,2),@ShiShouZLJE Numeric(10,2)set @YingShouJE=0.00set @ShiShouJE=0.00set @YingShouZLJE=0.00set @ShiShouZLJE=0.00   declare Out_Invo Cursor    forselect HeSuanBM,convert(Numeric(10,2),sum(JinE)),sum(ZiLiJE)from Out_RecipeDetail where JiuZhenID=@JiuZhenID and ChuFangLH=1 Group by HeSuanBMopen Out_Invodeclare @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,@YingShouZLJE2while (@@Fetch_Status=0)beginexec pSys_GetRound @YingShouJE2,@ShiShouJE2 outif @ShiShouJE2=-1beginClose Out_InvoDeallocate Out_InvoRollBack TranReturn -3endexec pSys_GetRound @YingShouZLJE2,@ShiShouZLJE2 outif @ShiShouZLJE2=-1beginClose Out_InvoDeallocate Out_InvoRollBack TranReturn -3end  set @YingShouJE=@YingShouJE+@YingShouJE2set @ShiShouJE=@ShiShouJE+@ShiShouJE2set @YingShouZLJE=@YingShouZLJE+@YingShouZLJE2set @ShiShouZLJE=@ShiShouZLJE+@ShiShouZLJE2--生成发票明细表insert into Out_InvoiceDetail values(@SerialNo,@JiuZhenID,@NewDanJuID,@HeSuanBM2,@YingShouJE2,@ShiShouJE2,@YingShouZLJE2,@ShiShouZLJE2) if @@Error<>0beginClose Out_InvoDeallocate Out_InvoRollBack TranReturn 11--生成发票明细表错误endFetch Next from Out_Invo into @HeSuanBM2,@YingShouJE2,@YingShouZLJE2endClose Out_InvoDeallocate Out_Invoupdate Out_RecipeDetail Set DanJuID=@NewDanJuID where JiuZhenID=@JiuZhenID and ChuFangLH=1--更新处方明细表单据号为当前单据号if @@Error<>0begin            RollBack TranReturn 9--更新处方明细表错误end--插入单据进入发票临时表insert into Out_TempPrintInvoice (ShouKuanYBM,DanJuID) values (@ShouKuanBM,@NewDanJuID)if @@Error<>0beginRollBack TranReturn 15end--生成发票主表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<>0begin           RollBack TranReturn 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.KeShiBMif @@Error<>0begin          RollBack TranReturn 12--生成发药状态表end--Assay:
/*update DR_Store set ZhangMianshu=ZhangMianshu-a.ShuLiang*a.TieShu*a.TianShufrom Out_RecipeDetail a,DR_Store bwhere a.YaoPinID=b.YaoPinID anda.LingYaoKSBM=b.KeShiBM and a.JiuZhenID=@JiuZhenID */update DR_Store set ZhangMianShu=ZhangMianShu-a.ShuLiangfrom (select YaoPinID,LingYaoKSBM,Sum(convert(Numeric(10,2),ShuLiang*TieShu*TianShu)) as ShuLiang from Out_RecipeDetail whereJiuZhenID=@JiuZhenID and ChuFangLH=1 Group by YaoPinID,LingYaoKSBM) a,DR_Store bwhere a.YaoPinID=b.YaoPinID and b.KeShiBM=a.LingYaoKSBM
/*    declare Out_Rep Cursorforselect YaoPinID,ShuLiang,TieShu,TianShu,LingYaoKSBM from Out_RecipeDetail where JiuZhenID=@JiuZhenID and ChuFangLH=1open Out_Repdeclare @YaoPinID2 varchar(15),@ShuLiang2 Numeric(10,2),@TieShu int,@TianShu int,@LingYaoKSBM2 varchar(10)Fetch Next from Out_Rep into @YaoPinID2,@ShuLiang2,@TieShu,@TianShu,@LingYaoKSBM2while (@@Fetch_Status=0) begindeclare @ZhangMianShu Amountset @ZhangMianShu=convert(numeric(10,2),@ShuLiang2*@TieShu*@TianShu)update DR_Store set ZhangMianShu=ZhangMianShu-@ZhangMianShu where YaoPinID=@YaoPinID2 and KeShiBM=@LingYaoKSBM2if @@Error<>0beginClose Out_RepDeallocate Out_RepRollBack TranReturn 13--更新药房库存错误endFetch Next from Out_Rep into @YaoPinID2,@ShuLiang2,@TieShu,@TianShu,@LingYaoKSBM2 endClose Out_RepDeallocate Out_Rep 
*/end
commit tran
Return 14
GO