create or replace PROCEDURE SP_ZS_YZMBYY --过程说明 ( pmbid00 in number , --模板ID0000 pkdysbh in number , --开单医生编号 pkdksbh in number , --开单科室编号 pzyid00 in number , --病人住院id pqyrqsj in char , --医嘱起用日期时间'2003050710:00:00' pyexm00 in char , --婴儿姓名 Pczy000 in number , --操作员id号 Pzxjg00 out varchar2 , --执行结果提示 Pzxbz00 out number , --传出参数说明 Pcommit in varchar2 default 'N', --是否提交 Pclbz00 in char default '0', --药品长临标志 默认为长 '0'：长嘱； '1'：短嘱; '2': 非药医嘱强制为短嘱 PZBBH00 IN VARCHAR2 default '-1',--组别编号 PZLMXID IN VARCHAR2 default '-1', --诊疗 明细ID PYYMBNR IN CHAR default '-1' --引用模板类别 0:西成药 1：中草药 2：诊疗 ) AS Vsysdate date; --日期变量 Vczyxm0 bm_ygbm00.zwxm00%type; --操作员姓名 Vyzmxid bq_yjyz00.yzmxid%type; --医嘱号 Vyfbmbh xt_ypfwfx.yfbmbh%type; --领药药房 Vypdlbh bm_yd0000.lbbh00%type; --药品大类编号 Vkdbq00 zy_brxxb0.dqbq00%type; --当前病区 Vdqks00 zy_brxxb0.dqks00%type; --当前科室 Vzqdw00 bm_pc0000.zqdw00%type; --频次单位 Vypyfmc bm_ypyf00.ypyfmc%type; --药品用法名称 Vyflbid bm_ypyf00.yflbid%type; --药品用法类别 Vmrqssj bm_ypyf00.mrqssj%type; --药品用法默认起始 Vqysj00 char(8); --起用时间 Vsyxtsj boolean; --是否使用系统时间作为医嘱的启用时间 Vclbz00 char(1); --长临标志 Vcounter number(10); --计数器变量 Vparams varchar2(255); --参数变量 Verrmsg varchar2(255); --错误提示变量 V_SFICU0 BM_BMBM00.SFICU0%type; V_icuks0 BM_BMBM00.BMBH00%TYPE; Ecustom exception; --错误变量 E_ICUCW0 EXCEPTION; cursor C_zlmbmx is select a.zlxmid,a.ts0000,a.tsdw00,a.mcsl00,b.zqcs00,a.bz0000,a.dw0000, a.mxid00,a.clbz00,a.pcid00,b.pcmc00,a.lbbh00,a.zlxmjc from vw_xt_zlmbmx a,vw_bm_pc0000 b where a.pcid00=b.pcid00(+) and MBID00=pmbid00 and (PYYMBNR='2' or PYYMBNR='-1') AND (PZLMXID='-1' OR instr(PZLMXID,','||to_char(a.mxid00)||',')>0) order by a.xsxh00; cursor C_ypyzzb is select distinct zbbh00,yplb00 from (select zbbh00,yplb00 from xt_ypmbmx where MBID00=pmbid00 AND (PZBBH00='-1' OR instr(PZBBH00,','||to_char(zbbh00)||',')>0) and (yplb00 = PYYMBNR or PYYMBNR='-1') order by zbbh00,znxh00); -- MODIFICATION HISTORY -- Person Date Comments -- YANGY 2007.07.25 往BQ_YJYZ00表中插入TS0000字段时，如果为空，默认0 -- YANGY 2007.09.25 往BQ_YJYZ00表中插入TS0000字段时，如果为空，长嘱默认0，短嘱默认为1 -- YANGY 2007.09.26 引用时，可以允许修改启用时间； -- YANGY 2008.03.04 可以部分引用医嘱； -- YANGY 2008.03.05 引用长期的非药医嘱模板，如果医生没有长嘱权限，只能引用为短嘱； -- YANGY 2008.04.12 医嘱模板引用时，ICU病区的病人引用时，执行科室和开单科室要改成ICU病房 -- YANGY 2008.04.16 引用模板，顺序不变 -- YANGY 2008.04.24 引用药品模板，启用时间修改没有效果 -- YANGY 2008.06.05 引用模板时，医嘱使用天数有误 -- YANGY 2008.06.27 从医嘱模板中引用的毒麻药品，在毒麻处方发药中发不出药品。 -- cqw 2009.07.15 ZS_YZ_DQSJWYZQYSJ='Y'时病区会报错，已修改; begin Vsysdate:=sysdate; Vparams :='Sp_Zs_Yzmbyy('||nvl(to_char(Pmbid00),'null')||','||nvl(to_char(Pkdysbh),'null')||','|| nvl(to_char(Pzyid00),'null')||','||nvl(to_char(Pkdksbh),'null')||','|| nvl(to_char(Pczy000),'null')||',pzxjg00,pzxbz00,'''||Pcommit||''')'; select zwxm00 into Vczyxm0 from bm_ygbm00 where ygbh00=pczy000; select dqbq00,dqks00 into Vkdbq00,Vdqks00 from zy_brxxb0 where zyid00=pzyid00; V_icuks0 := Vdqks00; select NVL(MAX(SFICU0),'0') INTO V_SFICU0 FROM BM_BMBM00 WHERE BMBH00=Vkdbq00; IF V_SFICU0='1' THEN begin select a.ksh000 into V_icuks0 from bm_bqks00 a, bm_bmbm00 b where a.ksh000=b.bmbh00 and b.sficu0='1' and a.bqh000=Vkdbq00; exception when OTHERS then raise E_ICUCW0; return; end; END IF; --取服务器时间做医嘱启用时间 select count(*) into Vcounter from xt_xtcs00 where name00='ZS_YZ_DQSJWYZQYSJ' and value0='Y'; if Vcounter>0 then Vsyxtsj:=True; else Vsyxtsj:=False; end if; if trim(substr(pqyrqsj,12,5))= ''or (trim(substr(pqyrqsj,12,5)) is null )then Vqysj00 :='08:00:00'; else Vqysj00 := substr(pqyrqsj,12,5)||':00'; end if; -- Vqysj00 := substr(pqyrqsj,12,5)||':00'; for zl in C_zlmbmx loop select sq_bq_yzmx00_yzmxid.nextval into Vyzmxid from dual where rownum=1; -- if Vsyxtsj=False then--取非药医嘱的启用时间 -- begin -- select substr(VALUE0,1,8) into Vqysj00 from xt_xtcs00 where NAME00 = 'BQ_FYYZMRQYSJ'; -- exception -- when others then -- Vqysj00:='10:00:00'; -- end; -- else -- end if; insert into bq_yjyz00(yzmxid, zyid00, yexm00, yzlb00, clbz00, pxsj00, zlxmid, zlxmjc, zxks00, kzys00, lrrq00, lrsj00, lrrxm0, qyrq00, qysj00, ts0000, tsdw00, bqh000, ksh000, pcid00, mcsl00, dw0000, pcmc00, yzzt00, tqfs00, yzcllb, bz0000) Values (Vyzmxid, pzyid00,pyexm00,zl.lbbh00, decode(Pclbz00, '2', '1', zl.clbz00), to_char(sysdate,'hh24:mi:ss'),zl.zlxmid,zl.zlxmjc,sf_zs_xmzxks(zl.zlxmid,Vkdbq00,V_icuks0),pkdysbh,to_char(sysdate,'yyyymmdd'), to_char(sysdate,'hh24:mi:ss'),Vczyxm0,to_char(to_date(substr(pqyrqsj,1,10),'yyyy.mm.dd'),'yyyymmdd'),Vqysj00, decode(decode(Pclbz00, '2', '1', zl.clbz00),'0',0,'1',zl.ts0000), zl.tsdw00, vkdbq00,V_icuks0,NVL(zl.pcid00,0),nvl(zl.mcsl00,1),zl.dw0000,nvl(zl.pcmc00,' '), decode(pkdysbh,pczy000,'7','0'),'0','正常',zl.bz0000); --update zfbl00,yblb00 --自动生成附加费用 if sql%notfound then Verrmsg:='生成非药医嘱时发生错误！'; Raise ECustom; end if; sp_bq_yjfjfy(Vyzmxid,0); end loop; for zb in C_ypyzzb loop select sq_bq_yzmx00_yzmxid.nextval into Vyzmxid from dual where rownum=1; begin select decode(c.lbbh00,'1','1','2','2','0')into Vypdlbh from xt_ypmbmx z,bm_yd0000 c where mbid00=pmbid00 and zbbh00=zb.zbbh00 and rownum=1 and z.ypnm00=c.ypnm00; select yfbmbh into vyfbmbh from xt_ypfwfx where slbmbh = vkdbq00 and yplbbh =Vypdlbh and mrbz00 = 'Y' and rownum = 1; exception when others then Vyfbmbh := 0; end; begin select zqdw00,yfmc00,yflbid,mrqssj into Vzqdw00,Vypyfmc,Vyflbid,Vmrqssj from vw_xt_ypmbmx z where mbid00=pmbid00 and zbbh00=zb.zbbh00 and yplb00=zb.yplb00 and rownum=1; if Vsyxtsj=True then Vqysj00:= Vqysj00; --to_char(sysdate,'hh24:mi:ss'); elsif (Vmrqssj is not null)or (Trim(Vmrqssj)='') then Vqysj00:=Vmrqssj||':00'; elsif Vyflbid in (1,2) then begin select Substr(VALUE0,1,8) into Vqysj00 from xt_xtcs00 where NAME00 = 'BQ_ZSYZMRQYSJ'; exception when others then Vqysj00:='08:00:00'; end; else begin select Substr(VALUE0,1,8) into Vqysj00 from xt_xtcs00 where NAME00 = 'BQ_PTYZMRQYSJ'; exception when others then Vqysj00:='09:00:00'; end; end if; if Trim(Vzqdw00)='天'or Trim(Vzqdw00)='周' then Vclbz00:='0'; else Vclbz00:='1'; end if; exception when others then Verrmsg:='取模板信息时发生错误'; Raise ECustom; Vqysj00:='10:00:00'; Vclbz00:='1'; end; insert into bq_ypyz00( yzid00, zyid00, yexm00, yplbbh,clbz00, pxsj00, kzys00, lrrq00, lrsj00, lrrxm0, qyrq00, qysj00, zqs000, zqcd00, zqdw00, zqcyts, jycs00, bqh000, ksh000, yfbmbh, pcid00, pcmc00, yfid00, ypyfmc, yyzf00, yzzt00) select Vyzmxid,pzyid00,pyexm00,lbbh00, Pclbz00, to_char(sysdate,'hh24:mi:ss'),pkdysbh,to_char(sysdate,'yyyymmdd'),to_char(sysdate,'hh24:mi:ss'),Vczyxm0, to_char(to_date(substr(pqyrqsj,1,10),'yyyy.mm.dd'),'yyyymmdd'),Vqysj00, decode(Pclbz00,'0',1,nvl(yysj00,1)), nvl(z.zqcd00,1),z.zqdw00, ts0000,decode(lbbh00,'2',ts0000,0), Vkdbq00, Vdqks00,Vyfbmbh,nvl(z.pcid00,0),nvl(z.pcmc00,' '), z.yfid00,z.yfmc00,z.yyzf00,decode(pkdysbh,pczy000,'7','0') from vw_xt_ypmbmx z where mbid00=pmbid00 and zbbh00=zb.zbbh00 and yplb00=zb.yplb00 and rownum=1; if Not sql%Notfound then --插入药品医嘱明细表 insert into bq_ypyzmx(yzid00,yzmxid,ypnm00,ypmc00,ypgg00, mcsl00,jldw00,lyl000,zyfydw,z2j000,xmyfmc,tqfs00,yzcllb,dmbz00) select Vyzmxid,sq_bq_yzmx00_yzmxid.nextval,ypnm00,ypmc00,ypgg00, nvl(jl0000,0),jldw00,nvl(ypzsl0/z2j000,decode(yplb00,'1',(decode(qzfs00,'2',CEIL(ts0000*jl0000),'0',CEIL(jl0000)*ts0000,ts0000*jl0000)) ,'0',0)),zyfydw,z2j000,xmyfmc,'0','正常',DMBZ00 from vw_xt_ypmbmx where mbid00=pmbid00 and zbbh00=zb.zbbh00 and yplb00=zb.yplb00; --Update zfbl00,yblb00,qzfsid --自动生成附加费用 sp_bq_ypfjfy(Vyzmxid,0); end if; end loop; if pcommit='Y' then commit; end if; exception when no_data_found then raise_application_error(-20001,substr('数据没有找到！*'||sqlerrm||','||Vparams,1,220)); when E_ICUCW0 then Raise_Application_Error(-20004,substrb('取ICU科室错误!*返回信息:'||Vparams,1,220)); when Ecustom then raise_application_error(-20010,substr(Verrmsg||'！*'||Vparams,1,220)); when others then raise_application_error(-20002,substr(nvl(sqlerrm, '原因不明出错')||'！*'||Vparams,1,220)); end Sp_Zs_Yzmbyy; /