CREATE OR REPLACE FUNCTION SF_YJ_ZLDCYP ( pzlxmid in number , --诊疗项目编号 pzyghid in varchar2 , --住院/挂号ID pmzzybz in varchar2 , --门诊住院标志 pyjksbh in varchar2 , --医技科室编号 pkdys00 in number , --开单医生 psl0000 in number , --数量 pcflsh0 in varchar2 , --相关处方号 popflag in number , --操作标志1新增项目2修改项目3删除项目4申请冲销5作废 pcxsl00 in number , --冲消数量 ptsbzbh in varchar2 default '' --特殊病种编码 ) return Varchar2 is Vsysdate date ;--日期变量 Vcounter number(10) ;--临时变量 Vcflsh0 yf_mzcf00.cflsh0%type ;--处方处方流水号 Vghid00 yf_mzcf00.ghid00%type ;--挂号id Vmryf00 yf_mzcf00.yfbmbh%type ;--默认药房 Vcfzt00 yf_mzcf00.cfzt00%type ;--处方状态 Vcfzje0 number(12,2) ;--处方金额 Vcyje00 number(12,2) ;--中药金额 Vyxje00 number(12,2) ;--药械金额 Vbrid00 sf_brxxb0.brid00%type ;--病人ID Vbrxxb0 bm_brxxb0%rowtype ;--病人信息表 Vybzxlb Ic_ybbrlb.ybzxlb%type ;--医保中心类别 Verrmsg varchar2(255) ;--错误提示变量 Ecustom exception ;--错误变量 zysfzddc xt_xtcs00.value0%type ; YJ_ZDDCPDXHWL xt_xtcs00.value0%type ;--盘点序号为零是否可以自动带出：默认为N，不能； 广东省中医院为Y，可以自动带出 YS_ZLDCYPYYID xt_xtcs00.value0%type ; Vyyid00 xt_zldcyp.yyid00%type ; Vcflsh1 yj_yw0000.zldccf%type;--yf_mzcf00.cflsh0%type ;--输出处方流水号 Vcflsh2 yf_mzcf00.cflsh0%type;--yj_yw0000.zldccf%type; tmpbccxsl number(10,4); vtsbzbh yj_yw0000.bh0000%type; LS_YYID00 xt_yyxx00.yyid00%TYPE; -- cursor CSfdzcf_MZ is select cflsh0 from yf_mzcf00 where cflsh0 in (Vcflsh2,Vcflsh3); -- cursor CSfdzcf_ZY is select cflsh0 from yf_zycf00 where cflsh0 in (Vcflsh2,Vcflsh3); Cursor CSfksly is select distinct a.sfksly,decode(b.lbbh00,'1','0','2','2','0') ypdlbh,decode(trim(mzdj00||sjdj00||dpdj00),'000','0','1') cfxz00 from xt_zldcyp a,bm_yd0000 b where zlxmid=Pzlxmid and a.ypnm00=b.ypnm00 and (Vyyid00='0' or a.yyid00=Vyyid00) and (SYFW00='0' or (pmzzybz='0' and SYFW00='1') or (pmzzybz='1' and SYFW00='2')); -- MODIFICATION HISTORY -- Person Date Comments -- YANGY 2007.09.13 诊疗项目带出的药品，如果该药品被任何一个药房停用了（盘点序号为0）, 这个药品就没有办法自动带出，应该改成如果被该药品的发药药房停用了才不让带出。 -- qks 2009.12.22 新增参数YS_ZLDCYPYYID: 诊疗带出药品,是否根据分院来区分 -- liuj 2010.12.07 诊疗项目若带出多个药品按普通毒麻分开带出两张处方。 -- csf 2011.07.01 带出诊疗项目原来不分门诊住院带出同样的药品，现根据XT_ZLDCYP的使用范围来区分带出 门诊和住院的药品 -- csf 2011.07.18 带出药品的医技单冲销后，yj_yw0000.zldccf字段的处方号重复，现纠正。 -- dsm 2013.09.17 带出药品的冲销单应该用bccxsl0 then select value0 into zysfzddc from xt_xtcs00 where name00='BQ_ZLXMSFDCYP'; else zysfzddc:='N'; end if; YJ_ZDDCPDXHWL:='N'; select count(*) into Vcounter from xt_xtcs00 where name00='YJ_ZDDCPDXHWL'; if Vcounter>0 then SELECT UPPER(trim(value0)) into YJ_ZDDCPDXHWL from xt_xtcs00 where name00='YJ_ZDDCPDXHWL'; else YJ_ZDDCPDXHWL:='N'; end if; Vyyid00:='0'; select nvl(max(value0),'N') into YS_ZLDCYPYYID from xt_xtcs00 where name00='YS_ZLDCYPYYID'; if Trim(YS_ZLDCYPYYID)='Y' then select Trim(nvl(max(YYID00),'0')) into Vyyid00 from bm_bmbm00 where bmbh00=pyjksbh; end if; Vsysdate:=sysdate; --处理旧处方 --if popflag in (2,3,4,5) and to_number(trim(pcflsh0))>0 then--操作标志 2修改项目3删除项目4冲销项目申请5作废 for i in 0..1 loop if (instrb(pcflsh0,',')<>0) then if i=0 then Vcflsh2:=substrb(trim(pcflsh0),1,instrb(trim(pcflsh0),',')-1); end if; if i=1 then Vcflsh2:=substrb(trim(pcflsh0),instrb(trim(pcflsh0),',')+1); end if; else if i=0 then Vcflsh2:=trim(pcflsh0); end if; if i=1 then Vcflsh2:='0'; end if; end if; Vcflsh0:=null; if popflag in (2,3,4,5) and (trim(pcflsh0) is not null) and VCFLSH2>'0' then--操作标志 2修改项目3删除项目4冲销项目申请5作废 --如果popflag是'4'那么对应的处方的cfzt只能是'1','2','5','6' if pmzzybz='0' then --如果处方已经收费，退未退部分，如果处方未收费，删除后重新生成 select cfzt00,ghid00 into Vcfzt00,Vghid00 from yf_mzcf00 where cflsh0=Vcflsh2; if Vcfzt00 in ('0','4','3') and popflag in (2,3) then --处方状态:0未记账，1已记账,2已发药,3作废'4'需要审核 begin select Vcflsh0 into Vcflsh0 from yf_mzcf00 where cflsh0=(select bccfh0 from YF_MZCF00 WHERE CFLSH0=Vcflsh2) and ghid00=Vghid00; exception when others then Vcflsh0:=null; end; DELETE YF_MZCFMX WHERE CFLSH0=Vcflsh2; DELETE YF_MZCF00 WHERE CFLSH0=Vcflsh2; if Psl0000<0 then --修改本次冲消数量 update yf_mzcfmx set bccxsl=0 where cflsh0=Vcflsh0; update yf_mzcfmx c set bccxsl=(select sum(b.ypzsl0) from yf_mzcf00 a,yf_mzcfmx b where a.cflsh0=b.cflsh0 and c.ypnm00=b.ypnm00 and a.ghid00=Vghid00 and a.bccfh0=Vcflsh0 and a.cfzt00 in ('0','1','2','5','6') and b.ypzsl0<0 and b.cxbz00='-' ) where cflsh0=Vcflsh0 and ypnm00 in (select ypnm00 from yf_mzcf00 a,yf_mzcfmx b where a.cflsh0=b.cflsh0 and a.ghid00=Vghid00 and a.bccfh0=Vcflsh0 and a.cfzt00 in ('0','1','2','5','6') and b.ypzsl0<0 and b.cxbz00='-' ); end if; if popflag=2 then Vcflsh0:=null; -- goto ADD_NEWCF; elsif popflag=3 then -- return ''; Vcflsh0:=null; end if; elsif Vcfzt00 in ('0','4','3') and popflag in (5) then --处方状态:0未记账，1已记账,2已发药,3作废'4'需要审核 update yf_mzcf00 set cfzt00='3' where cflsh0=Vcflsh2 and cfzt00<>'3'; --return ''; Vcflsh0:=null; elsif Vcfzt00 in ('6','5') then --处方状态:0未记账，1已记账,2已发药,3作废'4'需要审核'6'已退费'5'已退药 --return ''; Vcflsh0:=null; elsif Vcfzt00 in ('1','2') then --冲销处方 Vcflsh0:=sf_yk_getLsh('MZYF_MZCFH'); -- 修改本次冲销数量,可以进行部分冲消 -- update yf_mzcfmx set bccxsl=ypzsl0*Pcxsl00/Psl0000 where cflsh0=Vcflsh2; --update yf_mzcfmx c set bccxsl=(select c.ypzsl0+sum(b.ypzsl0) from yf_mzcf00 a,yf_mzcfmx b -- where a.cflsh0=b.cflsh0 and c.ypnm00=b.ypnm00 and a.ghid00=Vghid00 and a.bccfh0=pcflsh0 and a.cfzt00 in ('0','1','2','5','6') and b.ypzsl0<0 and b.cxbz00='-' ) -- where cflsh0=pcflsh0 and ypnm00 in (select ypnm00 from yf_mzcf00 a,yf_mzcfmx b -- where a.cflsh0=b.cflsh0 and a.ghid00=Vghid00 and a.bccfh0=pcflsh0 and a.cfzt00 in ('0','1','2','5','6') and b.ypzsl0<0 and b.cxbz00='-' ); update yf_mzcfmx set bccxsl=0 where cflsh0=Vcflsh2; update yf_mzcfmx c set bccxsl=ypzsl0+nvl((select sum(b.ypzsl0) from yf_mzcf00 a,yf_mzcfmx b where a.cflsh0=b.cflsh0 and c.ypnm00=b.ypnm00 and a.ghid00=Vghid00 and a.bccfh0=Vcflsh2 and a.cfzt00 in ('0','1','2','5','6') and b.ypzsl0<0 and b.cxbz00='-' ),0) where cflsh0=Vcflsh2 ; insert into yf_mzcf00(cflsh0, yfbmbh, ghid00, ghh000, brid00, brxm00, xb0000, csrq00, ysgzh0, ysxm00, ssks00, ghks00, zyts00, ypdlbh, cfxz00, bzid00, bzmc00, cfzt00, czy000, cfsrbz, pfr000, fhr000, srrq00, srsj00, brdz00, djh000, fjfdjh, bccfh0, cfzje0, cyje00, yxje00, lyks00, bz0000, sfksly,tsbzbh) select Vcflsh0,yfbmbh,ghid00, ghh000, brid00, brxm00, xb0000, csrq00, ysgzh0, ysxm00, ssks00, ghks00, zyts00, ypdlbh, cfxz00, bzid00, bzmc00, '0', pkdys00, '0' , pfr000, fhr000,to_char(sysdate,'yyyymmdd'),to_char(sysdate,'hh24:mi:ss'),brdz00,0,0, Vcflsh2,0, 0,0,lyks00,bz0000,sfksly,vtsbzbh from yf_mzcf00 where cflsh0=Vcflsh2; insert into yf_mzcfmx(cfid00,cflsh0,ypnm00,ypmc00,ypgg00,gjdj00,lsdj00,pfdj00,ypyfmc, sfzby0,pcmc00,csl000,jldw00,yysj00,yysjdw,yfsmmc,zb0000,zjyfmc, xmyfmc,ypzsl0,cfdw00,zhl000,cxbz00,cxcfid,sfxs00,ypsccj,ypscph, kphss0,hskpph,zfbl00,yblb00,ypdlbh,zfje00,qzfje0,gfje00,bz0000) select sq_ys_cfmx00_cfid00.nextval,Vcflsh0,ypnm00,ypmc00,ypgg00,gjdj00,lsdj00,pfdj00,ypyfmc, sfzby0,pcmc00,-csl000,jldw00,yysj00,yysjdw,yfsmmc,zb0000,zjyfmc, xmyfmc,-bccxsl,cfdw00,zhl000,'-',cfid00,sfxs00,ypsccj,ypscph, kphss0,hskpph,zfbl00,yblb00,ypdlbh,-round(zfje00*bccxsl/ypzsl0,2),-round(qzfje0*bccxsl/ypzsl0,2),-round(gfje00*bccxsl/ypzsl0,2),bz0000 from yf_mzcfmx where cflsh0=Vcflsh2 and bccxsl>0 ; --bccxsl>0改成bccxsl'3'; Vcflsh0:=null; elsif Vcfzt00 in ('6') then --处方状态:0未记账，1已记账,2已发药,3作废'4'需要审核'6'已退费 Vcflsh0:=null; elsif Vcfzt00 in ('1','2') then --冲销处方 Vcflsh0:=sf_yk_getLsh('ZYYF_ZYCFH'); -- 修改本次冲销数量,可以进行部分冲消 --update yf_zycfmx set bccxsl=ypzsl0*Pcxsl00/Psl0000 where cflsh0=Vcflsh2; --update yf_zycfmx c set bccxsl=(select c.ypzsl0+sum(b.ypzsl0) from yf_zycf00 a,yf_zycfmx b -- where a.cflsh0=b.cflsh0 and c.ypnm00=b.ypnm00 and a.zyid00=Vghid00 and a.bccfh0=pcflsh0 and a.cfzt00 in ('0','1','2','5','6') and b.ypzsl0<0 and b.cxbz00='-' ) -- where cflsh0=pcflsh0 and ypnm00 in (select ypnm00 from yf_zycf00 a,yf_zycfmx b -- where a.cflsh0=b.cflsh0 and a.zyid00=Vghid00 and a.bccfh0=pcflsh0 and a.cfzt00 in ('0','1','2','5','6') and b.ypzsl0<0 and b.cxbz00='-' ); update yf_zycfmx set bccxsl=0 where cflsh0=Vcflsh2; update yf_zycfmx c set bccxsl=ypzsl0+nvl((select sum(b.ypzsl0) from yf_zycf00 a,yf_zycfmx b where a.cflsh0=b.cflsh0 and c.ypnm00=b.ypnm00 and a.zyid00=Vghid00 and a.bccfh0=Vcflsh2 and a.cfzt00 in ('0','1','2','5','6') and b.ypzsl0<0 and b.cxbz00='-' ),0) where cflsh0=Vcflsh2 ; insert into yf_zycf00(cflsh0, yfbmbh, zyid00, zyh000, brid00, brxm00, xb0000, csrq00, ysgzh0, ysxm00, ssks00, dqks00, zyts00, ypdlbh, cfxz00, bzid00, bzmc00, cfzt00, czy000, cfsrbz, pfr000, fhr000, srrq00, srsj00, brdz00, djh000, fjfdjh, bccfh0, cfzje0, cyje00, yxje00, lyks00, bz0000, sfksly, dqbq00) select Vcflsh0,yfbmbh,zyid00, zyh000, brid00, brxm00, xb0000, csrq00, ysgzh0, ysxm00, ssks00, dqks00, zyts00, ypdlbh, cfxz00, bzid00, bzmc00, '0', pkdys00, '0' , pfr000, fhr000,to_char(sysdate,'yyyymmdd'),to_char(sysdate,'hh24:mi:ss'),brdz00,0,0, Vcflsh2,0, 0,0,lyks00,bz0000,sfksly,dqbq00 from yf_zycf00 where cflsh0=Vcflsh2; insert into yf_zycfmx(cfid00,cflsh0,ypnm00,ypmc00,ypgg00,gjdj00,lsdj00,pfdj00,ypyfmc, sfzby0,pcmc00,csl000,jldw00,yysj00,yysjdw,yfsmmc,zb0000,zjyfmc, xmyfmc,ypzsl0,cfdw00,zhl000,cxbz00,cxcfid,sfxs00,ypsccj,ypscph, kphss0,hskpph,zfbl00,yblb00,ypdlbh,zfje00,qzfje0,gfje00,bz0000) select sq_yf_zycfmx_cfid00.nextval,Vcflsh0,ypnm00,ypmc00,ypgg00,gjdj00,lsdj00,pfdj00,ypyfmc, sfzby0,pcmc00,-csl000,jldw00,yysj00,yysjdw,yfsmmc,zb0000,zjyfmc, xmyfmc,-bccxsl,cfdw00,zhl000,'-',cfid00,sfxs00,ypsccj,ypscph, kphss0,hskpph,zfbl00,yblb00,ypdlbh,-round(zfje00*bccxsl/ypzsl0,2),-round(qzfje0*bccxsl/ypzsl0,2),-round(gfje00*bccxsl/ypzsl0,2),bz0000 from yf_zycfmx where cflsh0=Vcflsh2 and bccxsl>0 ; if not sql%notfound then select nvl(sum(round(ypzsl0*lsdj00,2)),0)into Vcfzje0 from yf_zycfmx where cflsh0=Vcflsh0; select nvl(sum(round(ypzsl0*lsdj00,2)),0)into Vcyje00 from yf_zycfmx where cflsh0=Vcflsh0 and ypdlbh='2'; select nvl(sum(round(ypzsl0*lsdj00,2)),0)into Vyxje00 from yf_zycfmx where cflsh0=Vcflsh0 and ypdlbh='3'; update yf_zycf00 set cfzje0=Vcfzje0,cyje00=Vcyje00,yxje00=Vyxje00 where cflsh0=Vcflsh0; else delete from yf_zycf00 where cflsh0=Vcflsh0; Vcflsh0:=''; end if; --修改原始处方信息 --修改原始处方信息 update yf_zycfmx set cxbz00='+' where cflsh0=Vcflsh2 and cxbz00='Z'; if popflag in (3,4,5) then--操作标志 3删除项目4冲销项目申请5作废 if Vcfzt00 in ('0','4') then update yf_zycf00 set cfzt00='3' where cflsh0 in (Vcflsh2,Vcflsh0); end if; Vcflsh0:=Vcflsh0; elsif popflag=2 then Vcflsh0:=null; -- goto ADD_NEWCF; end if; elsif popflag=4 and vcfzt00='0' then update yf_zycf00 set cfzt00='3' where cflsh0=Vcflsh2; end if; end if; elsif popflag=5 and (trim(pcflsh0) is not null) then--操作标志 5作废 if pmzzybz='0' then update yf_mzcf00 set cfzt00='3' where cflsh0=Vcflsh2; else update yf_zycf00 set cfzt00='3' where cflsh0=Vcflsh2; end if; end if; if Vcflsh1 is null then Vcflsh1:=trim(Vcflsh0); else Vcflsh1:=trim(Vcflsh1)||','||trim(Vcflsh0); end if; end loop; <> --增加新处方 if popflag=1 or popflag=2 then select count(*) into vcounter from xt_zldcyp where zlxmid=pzlxmid and (Vyyid00='0' or yyid00=Vyyid00) and (SYFW00='0' or (pmzzybz='0' and SYFW00='1') or (pmzzybz='1' and SYFW00='2')); if Vcounter=0 then return null; end if; --取病人信息 if pmzzybz='0' then select brid00 into Vbrid00 from sf_brxxb0 where ghid00=pzyghid; else select brid00 into Vbrid00 from zy_brxxb0 where zyid00=pzyghid; end if; select * into Vbrxxb0 from bm_brxxb0 where brid00=Vbrid00; select ybzxlb into Vybzxlb from IC_YBBRLB where YBLB00=Vbrxxb0.yblb00 and fbbh00=Vbrxxb0.fbbh00; for KSLY in Csfksly loop if pmzzybz='0' then Vcflsh0:=sf_yk_getLsh('MZYF_MZCFH'); elsif pmzzybz='1' then Vcflsh0:=sf_yk_getLsh('ZYYF_ZYCFH'); end if; if Vcflsh1 is null then Vcflsh1:=trim(Vcflsh0); else Vcflsh1:=trim(Vcflsh1)||','||trim(Vcflsh0); end if; --取药房部门编号 if KSLY.Ypdlbh='2' then Verrmsg:='找不到执行科室('||Trim(pyjksbh)||')的中药处方领药药房'; select yfbmbh into Vmryf00 from (select yfbmbh from VW_XT_CYFWFX where SLBMBH=pyjksbh order by mrbz00 desc) where rownum=1; elsif KSLY.Ypdlbh='1' then Verrmsg:='找不到执行科室('||Trim(pyjksbh)||')的成药处方领药药房'; select yfbmbh into Vmryf00 from (select yfbmbh from vw_XT_ZCYFWFX where SLBMBH=pyjksbh order by mrbz00 desc) where rownum=1; else Verrmsg:='找不到执行科室('||Trim(pyjksbh)||')的西药处方领药药房'; select yfbmbh into Vmryf00 from (select yfbmbh from VW_XT_XYFWFX where SLBMBH=pyjksbh order by mrbz00 desc) where rownum=1; end if; if pmzzybz='0' then --目前无法插入ys_cfxxb0 because ys_cfxxb0,ys_cfmx00没有是否科室领药字段 insert into YF_MZCF00 (CFLSH0,YFBMBH,GHID00,GHH000,BRID00,BRXM00, XB0000,CSRQ00,YSGZH0,YSXM00,SSKS00,GHKS00,ZYTS00, YPDLBH,CFXZ00,CFZT00,SFKSLY,CZY000,BZ0000,CFSRBZ, SRRQ00,SRSJ00,LYKS00,tsbzbh) select Vcflsh0,Vmryf00,pzyghid,ghh000,b.brid00,b.xm0000, b.xb0000,csrq00,pkdys00,zwxm00,bmbh00,pyjksbh,1, KSLY.Ypdlbh,KSLY.cfxz00,'0',KSLY.SFKSLY,pkdys00,'诊疗项目带出处方','0', to_char(sysdate,'yyyymmdd'),to_char(sysdate,'hh24:mi:ss'),pyjksbh,vtsbzbh from sf_brxxb0 b,bm_ygbm00 c where b.ghid00=pzyghid and c.ygbh00=pkdys00; if Sql%notFound then Verrmsg:='插入处方时发生错误,自动带出药品失败'; Raise Ecustom; end if; /* if TRIM(YJ_ZDDCPDXHWL)='Y' THEN 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,ZFBL00,YBLB00,BZ0000) select SQ_YS_CFMX00_CFID00.NEXTVAL,Vcflsh0,a.ypnm00,k.ypmc00,k.ypgg00,round(lsj000/k2j000*m2j000,2), round(psl0000*ypzsl0/m2j000,2),mzfydw,m2j000,'Z','Y','N','0',ZFBL00,YBLB00,'诊疗带出处方' from xt_zldcyp a,VW_BM_YBSFDY H,YF_YPKCXX F,BM_YD0000 K where a.zlxmid=pzlxmid and a.ypnm00=k.ypnm00 and h.Ybzxlb=Vybzxlb and H.SFYP00='Y' AND K.YPNM00=H.SFXMID AND K.YPNM00=F.YPNM00 AND K.TZRQ00 IS NULL and decode(trim(k.sjdj00||k.dpdj00||k.mzdj00),'000','0','1')=KSLY.cfxz00 and YBBRLB=Vbrxxb0.ybbrlb and FBBH00=Vbrxxb0.fbbh00 and yfbmbh=Vmryf00 and a.sfksly=KSLY.SFKSLY and decode(k.lbbh00,'1','0','2','2','0')=KSLY.ypdlbh and (Vyyid00='0' or a.yyid00=Vyyid00) and (a.SYFW00='0' or (pmzzybz='0' and a.SYFW00='1') or (pmzzybz='1' and a.SYFW00='2')) ; ELSE*/ 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,ZFBL00,YBLB00,BZ0000 ,PCMC00,CSL000,JLDW00,YYSJ00,YYSJDW ) select SQ_YS_CFMX00_CFID00.NEXTVAL,Vcflsh0,a.ypnm00,k.ypmc00,k.ypgg00,round(lsj000/k2j000*m2j000,2), round(psl0000*ypzsl0/m2j000,2),mzfydw,m2j000,'Z','Y','N','0',h.ZFBL00,h.YBLB00,'诊疗带出处方' ,(select PCMC00 from BM_PC0000 where PCID00=a.PCID00),a.MCSL00,K.JLDW00,a.YYSJ00,'天' from xt_zldcyp a,VW_BM_YBSFDY H,YF_YPKCXX F,BM_YD0000 K where a.zlxmid=pzlxmid and a.ypnm00=k.ypnm00 and h.Ybzxlb=Vybzxlb and H.SFYP00='Y' AND K.YPNM00=H.SFXMID AND decode(trim(k.sjdj00||k.dpdj00||k.mzdj00),'000','0','1')=KSLY.cfxz00 and K.YPNM00=F.YPNM00 AND F.YFBMBH=Vmryf00 AND ( TRIM(YJ_ZDDCPDXHWL)='Y' or ( TRIM(YJ_ZDDCPDXHWL)='N' and F.PDXH00<>'0' and NVL(F.ZTBZ00,'0')='0' ) ) AND K.TZRQ00 IS NULL and YBBRLB=Vbrxxb0.ybbrlb and FBBH00=Vbrxxb0.fbbh00 and yfbmbh=Vmryf00 and a.sfksly=KSLY.SFKSLY and decode(k.lbbh00,'1','0','2','2','0')=KSLY.ypdlbh and (Vyyid00='0' or a.yyid00=Vyyid00) and (a.SYFW00='0' or (pmzzybz='0' and a.SYFW00='1') or (pmzzybz='1' and a.SYFW00='2')); -- END IF; if Sql%notFound then Verrmsg:='插入处方明细时发生错误，自动带出药品失败ybzxlb='||Vybzxlb||',ybbrlb='||Vbrxxb0.ybbrlb ||',fbbh00='||Vbrxxb0.fbbh00||',yfbmbh='||Vmryf00; Raise Ecustom; else select nvl(sum(round(ypzsl0*lsdj00,2)),0)into Vcfzje0 from yf_mzcfmx where cflsh0=Vcflsh0; select nvl(sum(round(ypzsl0*lsdj00,2)),0)into Vcyje00 from yf_mzcfmx where cflsh0=Vcflsh0 and ypdlbh='2'; select nvl(sum(round(ypzsl0*lsdj00,2)),0)into Vyxje00 from yf_mzcfmx where cflsh0=Vcflsh0 and ypdlbh='3'; update yf_mzcf00 set cfzje0=Vcfzje0,cyje00=Vcyje00, yxje00=Vyxje00 where cflsh0=Vcflsh0; end if; update yf_mzcf00 set cfzje0=(select sum(round(lsdj00*ypzsl0*100)/100) from yf_mzcfmx where cflsh0=Vcflsh0) where cflsh0=Vcflsh0; if Sql%notFound then Raise no_data_found; end if; else--住院 --目前无法插入ys_cfxxb0 because ys_cfxxb0,ys_cfmx00没有是否科室领药字段 if trim(zysfzddc)='Y' then insert into YF_ZYCF00 (CFLSH0,YFBMBH,ZYID00,ZYH000,BRID00,BRXM00, XB0000,CSRQ00,YSGZH0,YSXM00,SSKS00,DQKS00,ZYTS00, YPDLBH,CFXZ00,CFZT00,SFKSLY,CZY000,BZ0000,CFSRBZ, SRRQ00,SRSJ00,LYKS00,DQBQ00) select Vcflsh0,Vmryf00,pzyghid,zyh000,b.brid00,b.xm0000, b.xb0000,csrq00,pkdys00,zwxm00,bmbh00,pyjksbh,1, KSLY.Ypdlbh,KSLY.cfxz00,'0',KSLY.SFKSLY,pkdys00,'诊疗项目带出处方','0', to_char(sysdate,'yyyymmdd'),to_char(sysdate,'hh24:mi:ss'),pyjksbh,DQBQ00 from zy_brxxb0 b,bm_ygbm00 c where b.zyid00=pzyghid and c.ygbh00=pkdys00; if Sql%notFound then Verrmsg:='插入处方时发生错误,自动带出药品失败'; Raise Ecustom; end if; /* if TRIM(YJ_ZDDCPDXHWL)='Y' THEN insert into YF_ZYCFMX (CFID00,CFLSH0,YPNM00,YPMC00,YPGG00,LSDJ00, --YPYFMC,PCMC00,CSL000,JLDW00,YYSJ00,YYSJDW,YFSMMC,ZB0000,ZJYFMC,XMYFMC, YPZSL0,CFDW00,ZHL000,CXBZ00,SFXS00,SFZBY0,THBZ00,ZFBL00,YBLB00,BZ0000) select SQ_YF_ZYCFMX_CFID00.NEXTVAL,Vcflsh0,a.ypnm00,k.ypmc00,k.ypgg00,round(lsj000/k2j000*m2j000,2), round(psl0000*ypzsl0/m2j000,2),zyfydw,m2j000,'Z','Y','N','0',ZFBL00,YBLB00,'诊疗带出处方' from xt_zldcyp a,VW_BM_YBSFDY H,YF_YPKCXX F,BM_YD0000 K where a.zlxmid=pzlxmid and a.ypnm00=k.ypnm00 and h.Ybzxlb=Vybzxlb and H.SFYP00='Y' AND K.YPNM00=H.SFXMID AND decode(trim(k.sjdj00||k.dpdj00||k.mzdj00),'000','0','1')=KSLY.cfxz00 and K.YPNM00=F.YPNM00 AND K.TZRQ00 IS NULL and YBBRLB=Vbrxxb0.ybbrlb and FBBH00=Vbrxxb0.fbbh00 and yfbmbh=Vmryf00 and a.sfksly=KSLY.SFKSLY and decode(k.lbbh00,'1','0','2','2','0')=KSLY.ypdlbh and (Vyyid00='0' or a.yyid00=Vyyid00) and (a.SYFW00='0' or (pmzzybz='0' and a.SYFW00='1') or (pmzzybz='1' and a.SYFW00='2')); ELSE */ insert into YF_ZYCFMX (CFID00,CFLSH0,YPNM00,YPMC00,YPGG00,LSDJ00, --YPYFMC,PCMC00,CSL000,JLDW00,YYSJ00,YYSJDW,YFSMMC,ZB0000,ZJYFMC,XMYFMC, YPZSL0,CFDW00,ZHL000,CXBZ00,SFXS00,SFZBY0,THBZ00,ZFBL00,YBLB00,BZ0000 ,PCMC00,CSL000,JLDW00,YYSJ00,YYSJDW ) select SQ_YF_ZYCFMX_CFID00.NEXTVAL,Vcflsh0,a.ypnm00,k.ypmc00,k.ypgg00,round(lsj000/k2j000*m2j000,2), round(psl0000*ypzsl0/m2j000,2),zyfydw,m2j000,'Z','Y','N','0',h.ZFBL00,h.YBLB00,'诊疗带出处方' ,(select PCMC00 from BM_PC0000 where PCID00=a.PCID00),a.MCSL00,K.JLDW00,a.YYSJ00,'天' from xt_zldcyp a,VW_BM_YBSFDY H,YF_YPKCXX F,BM_YD0000 K where a.zlxmid=pzlxmid and a.ypnm00=k.ypnm00 and h.Ybzxlb=Vybzxlb and H.SFYP00='Y' AND K.YPNM00=H.SFXMID AND decode(trim(k.sjdj00||k.dpdj00||k.mzdj00),'000','0','1')=KSLY.cfxz00 and K.YPNM00=F.YPNM00 AND F.YFBMBH=Vmryf00 and ( TRIM(YJ_ZDDCPDXHWL)='Y' or ( TRIM(YJ_ZDDCPDXHWL)='N' and F.PDXH00<>'0' and NVL(F.ZTBZ00,'0')='0' ) ) AND K.TZRQ00 IS NULL and YBBRLB=Vbrxxb0.ybbrlb and FBBH00=Vbrxxb0.fbbh00 and yfbmbh=Vmryf00 and a.sfksly=KSLY.SFKSLY and decode(k.lbbh00,'1','0','2','2','0')=KSLY.ypdlbh and (Vyyid00='0' or a.yyid00=Vyyid00) and (a.SYFW00='0' or (pmzzybz='0' and a.SYFW00='1') or (pmzzybz='1' and a.SYFW00='2')); -- END IF; if Sql%notFound then Verrmsg:='插入处方明细时发生错误，自动带出药品失败ybzxlb='||Vybzxlb||',ybbrlb='||Vbrxxb0.ybbrlb ||',fbbh00='||Vbrxxb0.fbbh00||',yfbmbh='||Vmryf00; Raise Ecustom; else select nvl(sum(round(ypzsl0*lsdj00,2)),0)into Vcfzje0 from yf_zycfmx where cflsh0=Vcflsh0; select nvl(sum(round(ypzsl0*lsdj00,2)),0)into Vcyje00 from yf_zycfmx where cflsh0=Vcflsh0 and ypdlbh='2'; select nvl(sum(round(ypzsl0*lsdj00,2)),0)into Vyxje00 from yf_zycfmx where cflsh0=Vcflsh0 and ypdlbh='3'; update yf_zycf00 set cfzje0=Vcfzje0,cyje00=Vcyje00,yxje00=Vyxje00 where cflsh0=Vcflsh0; end if; update yf_zycf00 set cfzje0=(select sum(round(lsdj00*ypzsl0*100)/100) from yf_zycfmx where cflsh0=Vcflsh0) where cflsh0=Vcflsh0; if Sql%notFound then Raise no_data_found; end if; else Vcflsh0:=null; end if; end if; end loop; if substr(Vcflsh1,-1,1)=',' then Vcflsh1:=substr(Vcflsh1,1,length(Vcflsh1)-1); end if; return Vcflsh1; end if; if substr(Vcflsh1,-1,1)=',' then Vcflsh1:=substr(Vcflsh1,1,length(Vcflsh1)-1); end if; return Vcflsh1; exception when no_data_found then raise_application_error(-20001,substrb('数据没有找到！*'||Verrmsg||sqlerrm,1,220)); when Ecustom then raise_application_error(-20010,substrb(Verrmsg||'！*',1,220)); when others then raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*'||Verrmsg,1,220)); end;