PROMPT 4. 药房处方记帐 SP_SF_YFCFJZ CREATE OR REPLACE PROCEDURE SP_SF_YFCFJZ ( as_CFLSH0 IN CHAR, --处方流水号 ad_DJH000 IN NUMBER, --单据号 ad_SFCKBH IN NUMBER, --收费窗口编号 (Add on 2003-03-31) ad_PYCKBH IN number default 0, ad_FYCKBH IN NUMBER DEFAULT 0, as_YHMSG0 OUT VARCHAR, --存储过程提示的错误信息 as_SYSMSG OUT VARCHAR --系统提示的错误信息 ) as -- MODIFICATION HISTORY -- Person Date Comments -- YANGH 2007.09.17 CREATE:处方记帐 -- zhr 2007.11.07 修改取配药和发药窗口编号算法，同一个病人在一个药房多张处方，有可能是分为多次记账，发药窗口尽量取同一发药窗口． --　　　　　　　　　　　　（前提是窗口未停止工作，窗口有该病人未发处方） -- zhr 2008.02.23 宁德医院配药窗口和发药窗口一对一，同一个病人尽量同一个配药窗口。 -- zhr 2008.02.29 修改取配药和发药窗口编号算法，同一个病人在一个药房多张处方，有可能是分为多次记账，配药窗口尽量取同一配药窗口． --　　　　　　　　　　　　（前提是窗口未停止工作，窗口有该病人未发处方） -- CSF 2008.05.09 中药代煎费，分成每天两次服药，或三次服药，费用不一致，现增加参数实现需求 -- CSF 2008.08.28 增加参数控制，有待煎的中药处方不进入排队系统 -- csf 2008.09.10 宁德市医院改成发药窗口和配药窗口多对一模式，取病人窗口的处理方式同省二（同一个病人，同一个窗口）。 -- csf 2009.03.17 修改取配药和发药窗口编号算法，同一个病人在一个药房多张处方，有可能是分为多次记账，配药窗口尽量取同一配药窗口． --　　　　　　　　　　　　（前提是窗口未停止工作，窗口有该病人未发处方）,取发药窗口编号时候，必须是发药窗口所对应的发药窗口 -- --------- ---------- ------------------------------------------- ls_CFZJE0 YF_MZCF00.CFZJE0%TYPE; --处方总金额 ls_BRID00 YF_MZCF00.BRID00%TYPE; ls_GHID00 YF_MZCF00.GHID00%TYPE; ls_GHH000 YF_MZCF00.GHH000%TYPE; ls_BRXM00 YF_MZCF00.BRXM00%TYPE; ls_XB0000 YF_MZCF00.XB0000%TYPE; ls_CSRQ00 YF_MZCF00.CSRQ00%TYPE; ls_YBID00 BM_BRXXB0.YBID00%TYPE; ls_FBBH00 BM_BRXXB0.FBBH00%TYPE; ls_YBLB00 BM_BRXXB0.YBLB00%TYPE; ls_YBBRLB BM_BRXXB0.YBBRLB%TYPE; ls_SRRQ00 YF_MZCF00.SRRQ00%TYPE; ls_SRSJ00 YF_MZCF00.SRSJ00%TYPE; ls_YSGZH0 YF_MZCF00.YSGZH0%TYPE; ls_SSKS00 YF_MZCF00.SSKS00%TYPE; ls_GHKS00 YF_MZCF00.GHKS00%TYPE; ls_YFBMBH YF_MZCF00.YFBMBH%TYPE; ls_PSZT00 YF_MZCF00.PSZT00%TYPE; ls_ZYTS00 YF_MZCF00.ZYTS00%TYPE; ls_CFZT00 YF_MZCF00.CFZT00%TYPE; ls_BCCFH0 YF_MZCF00.BCCFH0%TYPE; ls_SFDM00 BM_YPLB00.SFDM00%TYPE; ls_SFXMMC BM_YPLB00.SFXMMC%TYPE; ls_DLXH00 YF_CFDL00.DLXH00%TYPE; ls_PYCKBH YF_CFDL00.PYCKBH%TYPE; --配药窗口编码 ls_FYCKBH YF_CFDL00.FYCKBH%TYPE; --发药窗口编码 ls_ZFBL00 SF_FYMX00.ZFBL00%TYPE; ls_GFBL00 SF_FYMX00.ZFBL00%TYPE; ls_ZFJE00 SF_FYMX00.ZFJE00%TYPE; ls_GFJE00 SF_FYMX00.GFJE00%TYPE; ls_JZJE00 SF_FYMX00.JZJE00%TYPE; ls_HJJE00 SF_FYMX00.HJJE00%TYPE; ls_QZFJE0 SF_FYMX00.QZFJE0%TYPE; ls_CFDGS0 VARCHAR2(8); ls_zymrcs YF_MZCF00.ZYMRCS%TYPE;--中药每日服药次数 v_zymrcs YF_MZCF00.ZYMRCS%TYPE;--中药每日服药次数 --P_CFMXJE_CZFS XT_XTCS00.VALUE0%TYPE; --P_CFZJE_CZFS XT_XTCS00.VALUE0%TYPE; --P_CFZJE_SRWS XT_XTCS00.VALUE0%TYPE; --P_CFMXJE_SRWS XT_XTCS00.VALUE0%TYPE; V_DJZYCFBJRCFDL XT_XTCS00.VALUE0%TYPE; ls_CZRQ00 CHAR(8); ls_CZSJ00 CHAR(8); ls_SFKSLY YF_MZCF00.SFKSLY%TYPE; --Modified on 2001-11-02 V_ZYDJS0 YF_MZCF00.ZYDJS0%TYPE; V_YPDLBH YF_MZCF00.YPDLBH%TYPE; V_JYSFDM BM_YYSFXM.SFXMID%TYPE; --中药煎药费收费代码 V_JYSFMC BM_YYSFXM.XMMC00%TYPE; --中药煎药费收费名称 V_JYSFJE BM_YYSFXM.SFJE00%TYPE; --中药煎药费收费金额 V_ZFJE00 YF_MZCF00.CFZJE0%TYPE; V_GFJE00 YF_MZCF00.CFZJE0%TYPE; V_ZFBL00 SF_FYMX00.ZFBL00%TYPE; V_YBBRLB BM_BRXXB0.YBBRLB%TYPE; V_FBBH00 SF_BRXXB0.FBBH00%TYPE; V_YBZXLB IC_YBBRLB.YBZXLB%TYPE; V_MZCF00 YF_MZCF00%ROWTYPE; ls_MZPY_YYGXH CHAR(10); --医院配药个性化参数 v_counter number; E_custom exception; --错误变量 BEGIN ls_CZRQ00 := to_char(sysdate,'YYYYMMDD'); ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); ls_CFZJE0 := 0; BEGIN select NVL(A.BRID00,0),SFKSLY,A.PSZT00,B.SFDM00,B.SFXMMC,A.SRRQ00,A.SRSJ00,A.YSGZH0,A.SSKS00,A.GHKS00,A.YFBMBH, A.ZYTS00,A.ZYDJS0,A.YPDLBH,A.GHID00,A.GHH000,A.BRXM00,A.XB0000,A.CSRQ00,A.CFZT00,A.BCCFH0,A.CFDGS0||','||A.YPDLBH, A.ZYMRCS into ls_BRID00,ls_SFKSLY,ls_PSZT00,ls_SFDM00,ls_SFXMMC,ls_SRRQ00,ls_SRSJ00,ls_YSGZH0,ls_SSKS00,ls_GHKS00,ls_YFBMBH, ls_ZYTS00,V_ZYDJS0,V_YPDLBH,ls_GHID00,ls_GHH000,ls_BRXM00,ls_XB0000,ls_CSRQ00,ls_CFZT00,ls_BCCFH0,ls_CFDGS0, V_ZYMRCS from YF_MZCF00 A,BM_YPLB00 B where A.YPDLBH = B.LBBH00 and A.CFLSH0 = as_CFLSH0 and ((CFZJE0>0 AND CFZT00 ='0')OR (CFZJE0<0 AND CFZT00='5')); SELECT * INTO V_MZCF00 FROM YF_MZCF00 WHERE CFLSH0=as_CFLSH0; EXCEPTION WHEN NO_DATA_FOUND THEN as_YHMSG0:='处方(CFLSH0='''||as_CFLSH0||''')不存在或已记过账,登记非法!'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; END; IF ls_BRID00 <> 0 THEN BEGIN select YBID00,FBBH00,YBLB00,YBBRLB into ls_YBID00,ls_FBBH00,ls_YBLB00,ls_YBBRLB from BM_BRXXB0 where BRID00 = ls_BRID00; EXCEPTION WHEN OTHERS THEN as_YHMSG0:='系统查找不到该处方对应的病人信息!'; as_SYSMSG:=SQLERRM||'select * from bm_brxxb0 where brid00 ='||to_char(ls_BRID00); ROLLBACK; RETURN; END; ELSE BEGIN ls_YBID00:=0; ls_FBBH00:=1; ls_YBLB00:='0'; ls_YBBRLB:='0'; END; END IF; IF ((ls_PSZT00='1') OR (ls_PSZT00='2')) THEN --皮试状态:'0'非皮试,'1'需皮试,'2'皮试未通过不能发药,'3',皮试通过可发药 as_YHMSG0:='皮试未通过,不能发药,处方记账失败!'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; END IF; --修改处方表中的相关信息 UPDATE YF_MZCF00 SET CFZT00 = decode(sign(CFZJE0),-1,'6','1'), DJH000 = ad_DJH000, JZRQ00 = ls_CZRQ00, JZSJ00 = ls_CZSJ00 WHERE CFLSH0 = as_CFLSH0 and ((CFZJE0>0 AND CFZT00 ='0') OR (CFZJE0<0 AND CFZT00='5')); if SQL%NOTFOUND then as_YHMSG0:='对应的处方不存在或已记过账,登记非法!'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; end if; --如果收费处记帐，把医技处方临时表的状态改为2 update yj_cfxx00 set cfzt00='1' where cflsh0=as_cflsh0; select sum(cfzje0) into ls_CFZJE0 from yf_mzcf00 where cflsh0=as_CFLSH0; --添加新的费用明细,注意执行科室应该填写药房的编码 Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,SFYDJ0,YSKS00,BEIZHU,ZFBL00,QZFJE0) select SQ_SF_FYMX00_MXID00.nextval,ad_DJH000,SFDM00,SFXMMC,'','',JE0000,1,JE0000,ZFJE00,GFJE00,JE0000-ZFJE00-GFJE00, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ls_GHKS00,ls_YSGZH0,ls_YFBMBH,ls_YSGZH0,'0',ls_SSKS00,as_CFLSH0,ZFBL00,QZFJE0 from ( select B.SFDM00,B.SFXMMC,SUM(round(YPZSL0*LSDJ00,2)) JE0000,A.ZFBL00,sum(ZFJE00) ZFJE00,sum(GFJE00) GFJE00,sum(QZFJE0) QZFJE0 from YF_MZCFMX A,BM_YPLB00 B where A.YPDLBH = B.LBBH00 and A.CFLSH0 = as_CFLSH0 and SFZBY0='N' group by B.SFDM00,B.SFXMMC,A.ZFBL00); --如果是草药处方,收必须的煎药费 IF ((V_YPDLBH='2') AND (V_ZYDJS0>0)) THEN BEGIN SELECT VALUE0 INTO V_JYSFDM FROM XT_XTCS00 WHERE NAME00='YF_ZYJYF_SFDM'; EXCEPTION WHEN OTHERS THEN V_JYSFDM:=V_JYSFDM; END; --如果 v_mrfycs(每日中药服药次数=3) 那么代煎费按其他项目收取 if V_ZYMRCS=3 then BEGIN SELECT VALUE0 INTO V_JYSFDM FROM XT_XTCS00 WHERE NAME00='YF_ZYJYF_SFDM_SC'; EXCEPTION WHEN OTHERS THEN V_JYSFDM:=V_JYSFDM; END; end if; --如果没有中药煎药费的参数，不收取中药煎药费11.16 IF V_JYSFDM IS NOT NULL THEN SELECT SFJE00,XMMC00 INTO V_JYSFJE,V_JYSFMC FROM BM_YYSFXM WHERE SFXMID=V_JYSFDM; SELECT B.YBZXLB,A.YBBRLB,A.FBBH00 INTO V_YBZXLB,V_YBBRLB,V_FBBH00 FROM BM_BRXXB0 A,IC_YBBRLB B WHERE A.BRID00=V_MZCF00.BRID00 AND B.YBLB00=A.YBLB00 and B.FBBH00=a.FBBH00; --找自付比例 JETHUA 2003.03.18 BEGIN SELECT ZFBL00,JMJE00 INTO V_ZFBL00,V_GFJE00 FROM VW_BM_YBSFDY B WHERE B.SFXMID=V_JYSFDM AND B.SFYP00='N' AND B.YBZXLB=V_YBZXLB AND B.YBBRLB=V_YBBRLB AND B.FBBH00=V_FBBH00 ; EXCEPTION WHEN OTHERS THEN RAISE E_custom; END; --SELECT SQ_SF_BRJFB0_DJH000.NEXTVAL INTO V_FJFDJH FROM DUAL; V_JYSFJE:=V_JYSFJE*V_MZCF00.ZYDJS0; V_ZFJE00:=V_JYSFJE*V_ZFBL00; V_GFJE00:=V_GFJE00*V_MZCF00.ZYDJS0; -- JETHUA 2003.06.01 自费病人和医保病人单独处理:自付金额=处方总金额-公费金额 IF V_FBBH00=1 OR V_FBBH00=3 OR V_FBBH00=90 THEN V_ZFJE00:=V_JYSFJE-V_GFJE00; END IF; --向门诊病人费用明细表 SF_FYMX00 插入一条西药费 INSERT INTO SF_FYMX00( MXID00, --门诊病人费用明细流水号 SQ_SF_FYMX00_MXID00 DJH000, --单据号 F.K=SF_BRFY00.DJH000 XMBH00, --医疗收费编码或药品编码 XMMC00, --项目名称 XMDJ00, --单价 XMSL00, --数量 HJJE00, --合计金额 ZFJE00, --自费金额 GFJE00, --公费金额 JZJE00, --记账金额 ZFBL00, --自付比例 CZRQ00, --开单日期 CZSJ00, --开单时间 ZXRQ00, --执行日期 ZXSJ00, --执行时间 KDKS00, --开单科室编号 KDYS00, --开单医生工号 ZXKS00, --执行科室编号 YSKS00, --医生科室 --医生所在科室 SFLSCF, --零散处方标志,'0':非处方,'1':是零散处方,'2':病区处方 SFYDJ0, --收费员登记 '0'其他人登记 '1'收费员登记 BEIZHU, --备注：处方单流水号。为可打印煎药费 --JETHUA 2002.12.18 ZXYS00) --执行医生工号 VALUES( SQ_SF_FYMX00_MXID00.NEXTVAL, ad_DJH000, V_JYSFDM, V_JYSFMC, ROUND(V_JYSFJE/V_MZCF00.ZYDJS0,4), V_MZCF00.ZYDJS0, V_JYSFJE, NVL(V_ZFJE00,0), NVL(V_GFJE00,0), V_JYSFJE-NVL(V_ZFJE00,0)-NVL(V_GFJE00,0), V_ZFBL00, ls_CZRQ00, ls_CZSJ00, ls_CZRQ00, ls_CZSJ00, V_MZCF00.GHKS00, V_MZCF00.YSGZH0, V_MZCF00.YFBMBH, V_MZCF00.SSKS00, '1', '1', as_CFLSH0, V_MZCF00.YSGZH0); --UPDATE YF_MZCF00 SET FJFDJH = ad_DJH000 --Modified on 2002-05-14,修改成对应的费用明细表的MXID00 -- WHERE CFLSH0 = as_CFLSH0; END IF; END IF; --取参数有待煎的中药处方是否不进入处方配药队列 begin select trim(Value0) into V_DJZYCFBJRCFDL from xt_xtcs00 where name00='MZPY_DJZYCFBJRCFDL'; exception when others then V_DJZYCFBJRCFDL:='N'; end; /*省二中药待煎处方不进入处方配药队列 开始*/ if NOT ((V_DJZYCFBJRCFDL='Y') and (V_YPDLBH='2') AND (V_ZYDJS0>0) ) THEN --医院配药个性化参数 select substrb(nvl(max(trim(value0)),'0'),1,1) into ls_MZPY_YYGXH from XT_XTCS00 where name00='MZPY_YYGXH'; --Modify on 2002-12-01, At 广州,添加处方队列表 if (NVL(ad_PYCKBH,0)<>0)and(NVL(ad_FYCKBH,0)<>0) then ls_PYCKBH:=ad_PYCKBH; ls_FYCKBH:=ad_FYCKBH; elsif ls_MZPY_YYGXH='1' then --Modify on 2007-11-07福建模式，预交金记账，医保病人的因为特殊病种，可能多次记账. --省二模式 --配药窗口重新取 begin --配药窗口尽量取同一配药窗口，避免病人多窗口等待(原则:配药窗口开放，处方未发药的,药品大类一致（成药同西药处理）) select a.PYCKBH into ls_PYCKBH from YF_CFDL00 a where a.brid00=ls_BRID00 and a.YFBMBH=ls_YFBMBH and ZT0000 in ('0','1','2') and exists (select 1 from BM_CKBM00 where a.PYCKBH=CKBH00 and ZT0000 in ('0') and instrb(nvl(CFDGS0,'0,1,2,3,4,5'),nvl(substrb(ls_CFDGS0,1,1),'0'))>0 and instrb(nvl(YPDLBH,'0,1,2,3,4,5'),nvl(substrb(ls_CFDGS0,3,1),'0'))>0) and rownum=1; exception when others then --配药窗口重新取 BEGIN ls_PYCKBH := SF_SF_GETCKBH(ls_YFBMBH,ad_SFCKBH,1,ls_CFDGS0); --从函数中得到配药窗口 EXCEPTION WHEN OTHERS THEN as_YHMSG0:='获取该处方的配药窗口失败,请稍候再试!'; as_SYSMSG:=SQLERRM||'SF_SF_GETCKBH('||to_char(ls_YFBMBH)||','||to_char(ad_SFCKBH)||',1)'; ROLLBACK; RETURN; END; end; --发药窗口尽量取同一发药窗口，避免病人多窗口等待(原则:发药窗口未停止，处方未发药的，药品大类一致，成药同西药,) begin select a.FYCKBH into ls_FYCKBH from YF_CFDL00 a,yf_mzcf00 b where a.cflsh0=b.cflsh0 and a.brid00=ls_BRID00 and a.YFBMBH=ls_YFBMBH and ZT0000 in ('0','1','2') and exists (select 1 from BM_CKBM00 where a.FYCKBH=CKBH00 and ZT0000 in ('0','1') and CKBH00 IN (SELECT DCKBH0 FROM BM_CKDY00 WHERE SCKBH0=ls_PYCKBH) ) and rownum=1; exception when others then BEGIN ls_FYCKBH := SF_SF_GETCKBH(ls_YFBMBH,ls_PYCKBH,2,ls_CFDGS0); --从函数中得到发药窗口 EXCEPTION WHEN OTHERS THEN as_YHMSG0:='获取该处方的发药窗口失败,请稍候再试!'; as_SYSMSG:=SQLERRM||'SF_SF_GETCKBH('||to_char(ls_YFBMBH)||','||to_char(ls_PYCKBH)||',2)'; ROLLBACK; RETURN; END; end; else --广东模式,现金记账且本次挂号处方一次记账 begin select a.PYCKBH,a.FYCKBH into ls_PYCKBH,ls_FYCKBH from YF_CFDL00 a,yf_mzcf00 b where a.cflsh0=b.cflsh0 and b.djh000=ad_DJH000 and a.YFBMBH=ls_YFBMBH and rownum=1; exception when others then BEGIN ls_PYCKBH := SF_SF_GETCKBH(ls_YFBMBH,ad_SFCKBH,1,ls_CFDGS0); --从函数中得到配药窗口 EXCEPTION WHEN OTHERS THEN as_YHMSG0:='获取该处方的配药窗口失败,请稍候再试!'; as_SYSMSG:=SQLERRM||'SF_SF_GETCKBH('||to_char(ls_YFBMBH)||','||to_char(ad_SFCKBH)||',1)'; ROLLBACK; RETURN; END; BEGIN ls_FYCKBH := SF_SF_GETCKBH(ls_YFBMBH,ls_PYCKBH,2,ls_CFDGS0); --从函数中得到发药窗口 EXCEPTION WHEN OTHERS THEN as_YHMSG0:='获取该处方的发药窗口失败,请稍候再试!'; as_SYSMSG:=SQLERRM||'SF_SF_GETCKBH('||to_char(ls_YFBMBH)||','||to_char(ls_PYCKBH)||',2)'; ROLLBACK; RETURN; END; end; end if; IF NVL(TRIM(ls_SFKSLY),'N')='N' THEN -- zjf modif on 20030527 select SQ_YF_CFDL00_DLXH00.nextval into ls_DLXH00 from dual; Insert into YF_CFDL00(DLXH00,CFLSH0,YFBMBH,YPJE00,GHID00,GHH000,BRID00,BRXM00,XB0000,CSRQ00,PYCKBH,FYCKBH,ZT0000) values(ls_DLXH00,as_CFLSH0,ls_YFBMBH,ls_CFZJE0,ls_GHID00,ls_GHH000,ls_BRID00,ls_BRXM00,ls_XB0000,ls_CSRQ00,ls_PYCKBH,ls_FYCKBH,'0'); END IF; END IF; /*省二中药待煎处方不进入处方配药队列 结束*/ --本处不加事务,在Delphi客户端处理 EXCEPTION WHEN NO_DATA_FOUND THEN as_YHMSG0:='对应的处方不存在或已记过账,登记非法!'; as_SYSMSG:=SQLERRM; ROLLBACK; WHEN E_custom THEN as_YHMSG0:='医保对应表中未找到煎药费信息'; as_SYSMSG:=SQLERRM; ROLLBACK; WHEN OTHERS THEN as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_YFCF00('||as_CFLSH0||','||to_char(ad_DJH000)||',as_YHMSG0,as_SYSMSG)',1,150); ROLLBACK; END; /