-- Start of DDL Script for Procedure SD_HOSPITAL.SP_BQ_YZMBYY -- Generated 18-二月-2013 10:41:02 from SD_HOSPITAL@PHRM CREATE OR REPLACE PROCEDURE sp_bq_yzmbyy --过程说明 ( pmbid00 in number , --模板ID0000 pkdysbh in number , --开单医生编号 pkdksbh in number default 0 , --开单医生编号 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; --是否使用系统时间作为医嘱的启用时间 Vbysyyzt char(1); --本医生引用医嘱时医嘱状态 Vclbz00 char(1); --长临标志 Vcounter number(10); --计数器变量 Vparams varchar2(255); --参数变量 Verrmsg varchar2(255); --错误提示变量 V_SFICU0 BM_BMBM00.SFICU0%type; V_icuks0 BM_BMBM00.BMBH00%TYPE; V_TZYPBM bm_yd0000.ypbm00%type; --停止药品编码 V_TZYPMC bm_yd0000.ypmc00%type; --停止药品名称 V_TZYPGG bm_yd0000.ypgg00%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 -- qks 2009.04.08 Create,insert into bq_yjyz00语句后的2处Pclbz00改用Vclbz00,sf_zs_xmzxks改用sf_bq_xmzxks -- qks 2009.04.09 函数sf_bq_xmzxks改用sf_zs_xmzxks,原函数sf_bq_xmzxks作废 -- chenqw 2009.09.15 加参数设置病区由开嘱医生本人引用模板的情况下医嘱状态是‘7’，还是‘0’。 -- chenqw 2009.09.27 加参数设置引用医嘱是否都是短嘱 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(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='BQ_BYSYYYZSFZJTJ' and value0='Y'; if Vcounter>0 then Vbysyyzt := '0'; else Vbysyyzt := '7'; end if; --取服务器时间做医嘱启用时间 Vcounter :=0; 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; 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,Vbysyyzt,'0'),'0','正常',zl.bz0000); --update zfbl00,yblb00 --自动生成附加费用 if sql%notfound then Verrmsg:='生成非药医嘱时发生错误！'; Raise ECustom; end if; sp_bq_yjfjfy(Vyzmxid,0); end loop; begin select a.ypbm00,a.ypmc00,a.ypgg00 into V_TZYPBM,V_TZYPMC,V_TZYPGG from bm_yd0000 a,xt_ypmbmx b where a.ypnm00=b.ypnm00 and b.mbid00=pmbid00 and a.tzrq00 is not null and rownum=1; Verrmsg:='，编码'||V_TZYPBM||',规格['||V_TZYPGG||']的'||V_TZYPMC||'被停止！'; -- raise_application_error(-20010,Verrmsg); exception when others then V_TZYPBM:=''; V_TZYPMC:=''; V_TZYPGG:=''; end; 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:='取模板信息时发生错误'||Verrmsg; Raise ECustom; Vqysj00:='10:00:00'; Vclbz00:='1'; end; select count(*) into Vcounter from xt_xtcs00 where name00='BQ_YYYZSFQSDZ' and value0='Y'; if Vcounter>0 then Vclbz00 := '1'; end if; 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, Vclbz00, 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(Vclbz00,'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,Vbysyyzt,'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_BQ_YZMBYY; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_BQ_YZMBYY