prompt 草药处方与颗粒剂处方的转化 SP_YS_ZYCFZH_Auto --规则: --1.普通草药方转颗粒剂处方：YF_MZCFMX.数量=次用量/浓缩比例 --2.频次清空 --3.明细用法清空,整剂用法默认:草药:默认的中药整剂用法按ORDER0排序。的第1个;颗粒:取"冲服"取不到,就":默认的中药整剂用法按ORDER0排序。的第1个" --4.找转化对应的药品，用名称匹配-->YPPZBH匹配 --5.普通草药方转颗粒剂处方时，代煎费要去掉 create or replace procedure SP_YS_ZYCFZH_Auto(as_CFLSH0 in char, --处方流水 ad_YFBMBH in number,--药房 as_MSG000 out varchar2, --错误信息 as_NCFLSH out varchar2 --新处方号 ) as ls_YPCZFL BM_YD0000.YPCZFL%type; ls_ZYDJS0 YS_CFXXB0.ZYDJS0%type; ls_GHID00 YS_CFXXB0.GHID00%type; ls_YSGZH0 YS_CFXXB0.YSGZH0%type; ls_LRXM00 BM_YGBM00.ZWXM00%type; ls_count number(5); ls_error varchar2(200); ls_error1 varchar2(200); ls_error2 varchar2(200); ls_YFBMBH YF_YPKCXX.YFBMBH%type; ls_CFLSH0 YS_CFXXB0.CFLSH0%type; ls_JZBZ00 varchar2(1); ls_YPNM00 BM_YD0000.YPNM00%type; ls_YPCZFL_N BM_YD0000.YPCZFL%type; ls_ZYTS00 YS_CFXXB0.ZYTS00%type; ls_SSKS00 YS_CFXXB0.SSKS00%type; ls_ZJYFID YS_CFMX00.ZJYFID%type; -- ls_CFLSH1 YS_CFMX00.CFLSH0%type; ls_ZHL000 BM_YD0000.M2J000%type; ls_YPZSL0 YS_CFMX00.YPZSL0%type; ls_YKKCSL YF_YPKCXX.YKKCSL%type; ls_ZTBZ00 YF_YPKCXX.ZTBZ00%type; ls_YFBMMC BM_BMBM00.BMMC00%type; ls_ZFBL00 YS_CFMX00.ZFBL00%type; ls_YBLB00 YS_CFMX00.YBLB00%type; is_YBBRLB SF_BRXXB0.YBBRLB%type; is_YBZXLB SF_BRXXB0.YBZXLB%type; is_FBBH00 SF_BRXXB0.FBBH00%type; Ecustom exception; -- MODIFICATION HISTORY -- Person Date Comments -- dsm 2015.03.13 create for MZYS-20150310-003 -- dsm 2015.03.25 改成用YPPZBH判断 for MZYS-20150326-001 -- dsm 2015.04.08 YS_CFMX00的单价要除NSBL00 for MZYS-20150403-001 -- dsm 2015.04.13 改成过程中commit,如果前台取消的话,删除新处方,恢复旧处方 MZYS-20150409-002 -- dsm 2017.01.10 增加药房参数ad_YFBMBH for MZYS-20170103-002 -- yanghq 2018.03.05 增加"自费病人医保"参数ad_ZFBRYB for MZYS-20180227-001 begin --ls_CFLSH1:=as_CFLSH0; --ls_YPCZFL=0（普通草药处方）,1或2(颗粒剂) select nvl(YPCZFL,0),ZYDJS0,GHID00,YSGZH0,ZYTS00,GHKS00 into ls_YPCZFL,ls_ZYDJS0,ls_GHID00,ls_YSGZH0,ls_ZYTS00,ls_SSKS00 from YS_CFXXB0 a where a.CFLSH0=as_CFLSH0; select nvl(YPCZFL,0) into ls_YPCZFL from YS_CFMX00 a,BM_YD0000 b where a.CFLSH0=as_CFLSH0 and a.YPNM00=b.YPNM00 and rownum=1; select ZWXM00 into ls_LRXM00 from BM_YGBM00 where YGBH00=ls_YSGZH0; select count(*) into ls_count from YF_MZCF00 where CFLSH0=as_CFLSH0 and CFZT00 in ('0') and CFZJE0>0; if ls_count=0 then ls_error:='处方已经记帐不能转化，请冲销重新开处方！'; raise Ecustom; end if; ls_error:='获取急诊标志失败!'; select decode(b.lbmc00,'急诊','Y','N'),YBBRLB,YBZXLB,FBBH00 into ls_JZBZ00,is_YBBRLB,is_YBZXLB,is_FBBH00 from sf_brxxb0 a,BM_GHLBB0 b where a.GHID00=ls_GHID00 and a.GHLB00=b.LBBH00; ls_error:='获取药房失败(或者选定的药房没有对应药品)!'; select a.YFBMBH,b.YPCZFL into ls_YFBMBH,ls_YPCZFL_N from YF_YPKCXX a,BM_YD0000 b where a.YPNM00=b.YPNM00 and b.yppzbh in (select d.yppzbh from YS_CFMX00 c,BM_YD0000 d where c.CFLSH0=as_CFLSH0 and c.YPNM00=d.YPNM00 ) and YPCZFL<>ls_YPCZFL and a.YKKCSL>0 and ZTBZ00='0' and exists(select 1 from xt_ypfwfx where yplbbh='2' and yfbmbh=a.yfbmbh and slbmbh=ls_ssks00) and ((ls_YPCZFL='0' and a.YFBMBH=ad_YFBMBH) or ls_YPCZFL<>'0') --and rownum=1; --2020.03.17 ljh 修改为以下，多个药房时，草药才会转化成对应药房的颗粒药 and a.YFBMBH=ad_YFBMBH; select SF_YK_GETLSH('MZYF_MZCFH') into LS_CFLSH0 from dual; ls_error:='获取整剂用法失败!'; if ls_YPCZFL='0' then --草药-->颗粒 begin select YFID00 into ls_ZJYFID from VW_BM_YPZJYF where zjyfmc like '%冲服%' and rownum=1; exception when others then select YFID00 into ls_ZJYFID from (select YFID00,ORDER0 from VW_BM_YPZJYF order by ORDER0) where rownum=1; end ; else --颗粒-->草药 select YFID00 into ls_ZJYFID from (select YFID00,ORDER0 from VW_BM_YPZJYF order by ORDER0) where rownum=1; end if; ls_error:=' '; ls_error1:=' '; ls_error2:=' '; ls_count:=0; insert into YS_CFXXB0(CFLSH0, YFBMBH, GHID00, GHH000, BRID00, BRXM00, XB0000, DJH000, BRLB00, YSGZH0, SSKS00, GHKS00, YPDLBH, CFXZ00, ZYTS00, BZID00, CFZT00, FJFDJH, CZY000, SRRQ00, SRSJ00, CFMBMC, CFZJE0, BRDZ00, TSBZBH,ZYDJS0,YPCZFL,ZJZFID) select LS_CFLSH0, ls_YFBMBH, GHID00, GHH000, BRID00, BRXM00, XB0000, 0, BRLB00, YSGZH0, SSKS00, GHKS00, YPDLBH, CFXZ00, ZYTS00, BZID00, '1' , 0 ,ls_YSGZH0,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'), CFMBMC, CFZJE0, BRDZ00, TSBZBH,0,ls_YPCZFL_N,ZJZFID from YS_CFXXB0 where CFLSH0=as_CFLSH0; for c_mx in (select YPNM00,YPMC00,CSL000,YYSJ00,YYSJDW,YFSMID,ZB0000,YPZSL0,ZQCS00,XMYFMC,ZFBL00,YBLB00,SFXS00,ZXCS00,ZJYFID,SFZBY0,PSLBID,YYZF00,YFID00,(select nvl(YPPZBH,'a') from BM_YD0000 where YPNM00=a.YPNM00) YPPZBH from YS_CFMX00 a where CFLSH0=as_CFLSH0) loop begin --select YPNM00 into ls_YPNM00 from BM_YD0000 a where YPPZBH=c_mx.YPPZBH and YPCZFL=ls_YPCZFL_N and exists(select 1 from YF_YPKCXX where YFBMBH=ls_YFBMBH and YPNM00=a.YPNM00 ); select a.YPNM00,b.YKKCSL,b.ZTBZ00,a.M2J000 into ls_YPNM00,ls_YKKCSL,ls_ZTBZ00,ls_ZHL000 from BM_YD0000 a,YF_YPKCXX b where a.YPPZBH=c_mx.YPPZBH and a.YPCZFL=ls_YPCZFL_N and a.YPNM00=b.YPNM00 and b.YFBMBH=ls_YFBMBH and YKKCSL>0 and rownum=1; if ls_ZTBZ00='1' then--被暂停 if ls_error1=' ' then ls_error1:=c_mx.YPMC00 ; else ls_error1:=ls_error1||','||c_mx.YPMC00; end if; ls_YPNM00:=0; end if; if ls_YPNM00>0 then ls_YPZSL0:=SF_YS_LYLJS0(ls_YPNM00,c_mx.CSL000,ls_ZHL000,0,ls_ZYTS00); if ls_YKKCSL0 then ls_count:=ls_count+1; --自费比率，医保类别 select ZFBL00,YBLB00 into ls_ZFBL00,ls_YBLB00 from VW_YS_YBFDMYPSR where YPNM00=ls_YPNM00 and YBBRLB=is_YBBRLB and YBZXLB=is_YBZXLB and FBBH00=is_FBBH00 and rownum=1; insert into YS_CFMX00(CFID00, CFLSH0, YPNM00, YPMC00, YPGG00, LSDJ00, YFID00, PCID00, CSL000, JLDW00, YYSJ00, ZQCS00, YYSJDW, YYZF00, ZB0000, PSLBID, SFZBY0, ZJYFID, XMYFID,XMYFMC, YPZSL0, CFDW00, ZHL000, ZFBL00, YBLB00, SFXS00, ZXCS00, F2J000) select SQ_YS_CFMX00_CFID00.NEXTVAL,ls_CFLSH0, ls_YPNM00, YPMC00, YPGG00, nvl((select LSDJ00/a.NSBL00/a.K2J000*M2J000 from YF_YPKCXX where YFBMBH=ls_YFBMBH and YPNM00=ls_YPNM00),a.LSJ000/a.NSBL00/a.K2J000*a.M2J000), c_mx.YFID00, 0, c_mx.CSL000,JLDW00, c_mx.YYSJ00, c_mx.ZQCS00, c_mx.YYSJDW, c_mx.YYZF00, c_mx.ZB0000, c_mx.PSLBID, c_mx.SFZBY0, ls_ZJYFID, '','', ls_YPZSL0, MZFYDW, M2J000, ls_ZFBL00, ls_YBLB00,--c_mx.ZFBL00, c_mx.YBLB00, c_mx.SFXS00, c_mx.ZXCS00, F2J000 from BM_YD0000 a where YPNM00=ls_YPNM00; end if; end loop; select BMMC00 into ls_YFBMMC from BM_BMBM00 where BMBH00=ls_YFBMBH; if ls_error<>' ' then ls_error:='如下药品找不到可转换的对应药品:'||chr(13)||ls_error||chr(13); end if; if ls_error1<>' ' then ls_error1:='如下药品被暂停:'||chr(13)||ls_error1||chr(13); end if; if ls_error2<>' ' then ls_error2:='如下药品库存不足:'||chr(13)||ls_error2; end if; if ls_error<>' ' or ls_error1<>' ' or ls_error2<>' ' then if ls_count=0 then ls_error:=ls_error||trim(ls_error1)||trim(ls_error2)||chr(13)||' 转换失败!'; else ls_error:=ls_error||trim(ls_error1)||trim(ls_error2)||chr(13)||' 是否继续?'; end if; --ls_error:='如下药品找不到可转换的对应药品:'||chr(13)||ls_error||chr(13)||' 是否继续?'; ls_error:=ls_YFBMMC||','||ls_error; as_MSG000:=ls_count||ls_error; end if; as_NCFLSH:=ls_CFLSH0; ls_error:='插入YF_MZCF00,YF_MZCFMX失败!'; if ls_count>0 then insert into YF_MZCF00 (CFLSH0,YFBMBH,GHID00,GHH000,BRID00, BRXM00,XB0000,CSRQ00,DJH000,YSGZH0,YSXM00,SSKS00,GHKS00, ZYTS00,YPDLBH,CFXZ00,BZID00,BZMC00,CFZT00,SFKSLY,PSZT00, FJFDJH,CZY000,SRRQ00,SRSJ00,CFZJE0,BRDZ00,BZ0000,CFSRBZ, ZYDJS0,ZYMRCS,BCCFH0,TSBZBH,ZYSFJJ,ZYSFNJ,ZYNJJL,ZJZFID,ZYMRJS,TSYS00,DYCJSL,DYCJQL,DECJSL,DECJQL,BRZJBH) select ls_CFLSH0,YFBMBH,GHID00,GHH000,BRID00,BRXM00, XB0000,(select BRCSRQ from BM_BRXXB0 where BRID00=a.BRID00),DJH000,YSGZH0,(select ZWXM00 from BM_YGBM00 where YGBH00=a.YSGZH0),SSKS00,GHKS00, ZYTS00,YPDLBH,CFXZ00,a.BZID00,BZMC00,'0','N','0',FJFDJH,CZY000,SRRQ00,SRSJ00,CFZJE0,BRDZ00, null,'0',ZYDJS0,ZYMRCS,'',TSBZBH,ZYSFJJ,ZYSFNJ,ZYNJJL,ZJZFID,ZYMRJS,TSYS00,DYCJSL,DYCJQL,DECJSL,DECJQL,BRZJBH from YS_CFXXB0 a,BM_DMZD00 b where a.bzid00=b.bzid00(+) and CFLSH0=ls_CFLSH0; insert into YF_MZCFMX(CFID00,CFLSH0,YPNM00,YPMC00, YPGG00,LSDJ00,YPYFMC,PCMC00,CSL000,JLDW00,YYSJ00, YYSJDW,YFSMMC,ZB0000,ZJYFMC,XMYFMC,YPZSL0,CFDW00, ZHL000,CXBZ00,SFXS00,SFZBY0,THBZ00,PSLBID,ZFBL00,YBLB00,BZ0000,ZXCS00, CQLY00,F2J000,TSYS00,ZFFSBZ) select a.CFID00,ls_CFLSH0,a.YPNM00,a.YPMC00, a.YPGG00,(a.LSDJ00*d.NSBL00),b.YPYFMC,c.PCMC00,(DECODE(A.CSL000,null,null,(ROUND((a.CSL000/d.NSBL00),4)))),a.JLDW00,a.YYSJ00, a.YYSJDW,a.YYZF00,a.ZB0000,b.YPYFMC,a.XMYFMC,(ROUND((a.YPZSL0/d.NSBL00),3)),a.CFDW00, a.ZHL000,'Z','Y',SFZBY0,'0',a.PSLBID,a.ZFBL00,a.YBLB00,a.YYZF00,ZXCS00, a.CQLY00, a.F2J000,a.TSYS00,a.ZFFSBZ from YS_CFMX00 a,BM_YPYF00 b,BM_PC0000 c, BM_YD0000 d where a.YPNM00=d.YPNM00 and a.CFLSH0=ls_CFLSH0 and a.YFID00=b.YFID00(+) and a.PCID00=c.PCID00(+) ; update YS_CFXXB0 set CFZJE0=(select sum(LSDJ00*YPZSL0) from YF_MZCFMX where CFLSH0=ls_CFLSH0) where CFLSH0=ls_CFLSH0; update YF_MZCF00 set CFZJE0=(select sum(LSDJ00*YPZSL0) from YF_MZCFMX where CFLSH0=ls_CFLSH0) where CFLSH0=ls_CFLSH0; ls_error:='调用sp_ys_yjxmzddc失败!'; sp_ys_yjxmzddc(ls_CFLSH0,ls_GHID00,ls_LRXM00,ls_JZBZ00,'1'); ls_error:='作废旧处方失败!'; update YS_CFXXB0 set CFZT00='2' where CFLSH0=as_CFLSH0; update YF_MZCF00 set CFZT00='3' where CFLSH0=as_CFLSH0; commit; end if; ls_error:=''; exception when Ecustom then raise_application_error(-20010,substrb(ls_error||'！*',1,240)); RollBack; when others then raise_application_error(-20002,substrb(ls_error||'！*'||nvl(sqlerrm, '原因不明出错'),1,240)); RollBack; end; / show error;