-- Start of DDL Script for Procedure SD_HOSPITAL.SP_BQ_YJYZQT_DZBL -- Generated 16-一月-2018 15:54:52 from SD_HOSPITAL@FW2 CREATE OR REPLACE PROCEDURE sp_bq_yjyzqt_dzbl ( P_MODE00 in number,--0删除--1核对,2--停止,3--取消,4--排序,5--预停止(应该是暂停) 7提交 5实际上为暂停的处理,增加8为预停止. P_YZMXID in number,--医嘱ID(分组号) P_HDRXM0 in varchar,--核对人姓名 P_TZYS00 in number,--停止医生 P_TZRXM0 in varchar,--停止人姓名 P_TZRQ00 in varchar,--停止日期或起用日期 P_TZSJ00 in varchar,--停止时间或起用时间 P_PXSJ00 in varchar,--排序时间 P_CZY000 in number ,--操作员 P_CJKS00 in char default '', --采集科室 P_SSSBZ0 in char default 'N' --手术室标识 )as V_COUNT number(10); --计数器 V_TZYS00 BQ_YJYZ00.TZYS00%type;--停止医生 V_HDRXM0 BQ_YJYZ00.TZRXM0%type; V_TZRXM0 BQ_YJYZ00.TZRXM0%type; V_ZYID00 ZY_BRXXB0.ZYID00%type; V_ZLXMJC BQ_YJYZ00.ZLXMJC%type; V_YZZT00 char(1); VERRMSG varchar2(255); --错误提示 VPARAMS varchar2(255); --错误提示 ECUSTOM exception ; --错误提示 VJZRQ00 char(8); --医嘱拆分截至日期 BYTZYZ0 boolean; --是否预停止医嘱 V_TZRQ00 BQ_YJYZ00.TZRQ00%type;--预停止日期日期 V_TZSJ00 BQ_YJYZ00.TZSJ00%type;--预停止日期时间 V_YJYZ00 BQ_YJYZ00%rowtype; --医技医嘱 V_YZSJFG char(8); --医嘱时间分割 VYTZLDZSFKYCX XT_XTCS00.VALUE0%type ; --已经停止类短嘱是否可以撤销 LS_JHMXID BQ_YPYZMX.JHMXID%type; --计划明细 LS_ZRZCID LJ_ZRZC00.ZRZCID%type; --ZRZCID LS_ZYID00 BQ_YPYZ00.ZYID00%type; --ZYID00 PBCTQID number ; --本次提取ID V_CZYXM0 BM_YGBM00.ZWXM00%type; --中文姓名 LS_YZQYSJMRTJSJ varchar2(20); --医嘱起用时间是否默认为提交时间 LS_DQSJWYZQYSJ varchar2(20); --录入医嘱时，医嘱起用时间默认当前时间 LS_DTRYBRSFKYXGQYRQ varchar2(20); --录入医嘱时，当天入院病人是否可以修改起用日期 V_RYRQ00 char(8); --入院日期 V_RYCWH0 ZY_BRXXB0.RYCWH0%type; --入院床位号 V_QYRQ00 BQ_YPYZ00.QYRQ00%type; --起用日期 V_QYSJ00 BQ_YPYZ00.QYSJ00%type; --起用时间 V_SYSDATE date; LS_YZLX00 BQ_YJYZ00.YZLX00%type; LS_SFZCYBZYZPD varchar2(10); --是否做出院标志医嘱判断 V_DYPC00 BQ_YPYZ00.DYPC00%type; --打印批次 -- MODIFICATION HISTORY -- PERSON DATE COMMENTS -- xuxm 2014.04.15 CREATE 新入院病人，入院当天，新增一条医嘱，隔一段时间再点保存、提交，问题：医嘱的起用时间不是当前时间。（参数：ZS_DTRYBRSFKYXGQYRQ 已经开启不允许修改) FOR DZBL-20140411-001 -- xuxm 2014.06.04 医技医嘱提交后，系统有判断如果该项目属于检查预约科室的项目，会直接写入到YJ_YW0000表，待护士执行后再修正状态。要求：去掉该流程，提交时不需要写入YJ_YW0000表，还是由护士执行后再写入YJ_YW0000表。FOR DZBL-20140528-002 -- xuxm 2014.09.24 目前非药医嘱的提交人等同于录入人，需做修改：非药医嘱的提交人需默认为当前做提交操作的医师姓名。 FOR DZBL-20140919-003 -- xuxm 2014.12.03 医嘱启用时间需精确到秒，避免一分钟内先后提交的医嘱顺序颠倒。 FOR DZBL-20140913-001 -- rbh 2014.12.20 引用ZYDZBL DZBL-20141210-007 -- xuxm 2015.02.05 DZBL-20150116-006 医生选择出院医嘱后，当新增医嘱时，如起用时间大于出院时间不允许保存，应给予提示。 -- xuxm 2015.02.15 DZBL-20150122-001 项目为带有“死亡”两个字的不做新增医嘱的限制。 -- xuxm 2015.03.16 DZBL-20150312-002 医生撤销出院医嘱的时候，需清空SJCYRQ,SJCYSJ -- xuxm 2015.04.10 DZBL-20150408-002 撤销完出院医嘱，将ZYBRXXB0.SJCYRQ UPDATE成20991231、SJCYSJ清空成NULL -- xuxm 2015.04.13 DZBL-20150408-003 医生开出院医嘱（今日出院、明日出院、自动出院等）时需判断ZY_BRXXB0.SJCYRQ值是否为20991231，是的话需限制医生不允许再开出院医嘱，并且弹出相关提示信息“如果要新开出院医嘱，必须先撤销已开的出院医嘱，或先开“取消出院” -- xuxm 2015.04.27 DZBL-20150328-001 开具死亡医嘱后，再开新医嘱时，一定要勾选“补录”，才允许开具保存，如未勾选，当选择医嘱后回车到医嘱界面中，应给予提示“在死亡医嘱后继续开医嘱请勾选补录！”。 -- xuxm 2015.05.04 DZBL-20150428-002 现状：医生开出院医嘱时目前系统有判断ZY_BRXXB0.SJCYRQ值是否为2099，SJCYRQ的值是2099，那么允许开出院医嘱，反之不允许开出院医嘱！ -- xuxm 2015.07.14 DZBL-20150708-001 输血知情同意书打印后才允许医生开输血医嘱（输血医嘱的判断按照诊疗字典类别为“输血”的）和填写【病历操作】中的输血申请。（注：需强制这样操作） -- xuxm 2015.08.14 DZBL-20150803-001 按照模板的名称：“输注血液、血液制品知情同意书”，（输血医嘱的判断按照诊疗字典类别为“输血”的）和填写【病历操作】中的输血申请。（注：需强制这样操作） -- xuxm 2016.02.04 DZBL-20151203-003 没有输血申请的情况下，如果医生在医嘱界面开带有“备血/输血”两个字的医嘱，需提醒医生“开备血、输血医嘱前，请先提交输血申请”； -- xuxm 2016.06.21 ZYDZBL-20160614-001 医生开医嘱界面，非药医嘱提交时，也要判断项目的男女性别及是否急诊项目 -- xuxm 2016.07.07 ZYDZBL-20160707-004 医生在开完医嘱，护士站未核对前医生无法取回医嘱进行修改，改为可以取回医嘱！ -- xuxm 2016.08.07 ZYDZBL-20160806-004 （急）医嘱界面，提交医嘱的时候需增加功能：判断检查项目是否已经停止（已停止的项目限制不允许提交） -- wuzy 2016.09.12 ZYDZBL-20160909-001 病人在病区已做出院登记后不能在提交医嘱 -- xuxm 2016.11.02 ZYDZBL-20161101-001 目前提交非药医嘱时，项目是否已经停止、项目的男女性别及是否急诊项目，有做判断，但是提示内容仍然不够准确，需提示具体项目名称（已停止、限男/女性使用、是否急诊项目） -- xuxm 2017.02.14 ZYDZBL-20170209-001 医嘱提交时判断录入人和提交人是同一个医生，增加参数控制提示或者强制限制不允许提交。 -- xuxm 2017.02.14 ZYDZBL-20170207-002 电子病历非药医嘱开单时，增加一个判断，如果执行科室为超声科，那么数量只允许为1，请帮忙修改。 begin VPARAMS := 'SP_BQ_YJYZQT('||P_MODE00||','||P_YZMXID||','||P_HDRXM0||','||P_TZYS00||',' ||P_TZRXM0||','||P_TZRQ00||','||P_TZSJ00||','||P_PXSJ00||')'; V_SYSDATE := sysdate; select ZYID00,HDRXM0,TZYS00,YZZT00,TZRXM0,TZRQ00,TZSJ00 into V_ZYID00,V_HDRXM0,V_TZYS00,V_YZZT00,V_TZRXM0,V_TZRQ00,V_TZSJ00 from BQ_YJYZ00 where YZMXID=P_YZMXID; begin select ZWXM00 into V_CZYXM0 from BM_YGBM00 where YGBH00=P_CZY000; exception when others then V_CZYXM0:=''; end; --删除医嘱 if P_MODE00 = 0 then --判断是否新开医嘱 select count(1) into V_COUNT from BQ_YJYZ00 where YZZT00 in ('0','7') and YZMXID = P_YZMXID; if V_COUNT = 0 then VERRMSG:='不是新开医嘱，不能删除'; raise ECUSTOM; end if; select count(1) into V_COUNT from XK_SXSQ00 where YZMXID = P_YZMXID and SQZT00 in ('2','3'); if V_COUNT > 0 then VERRMSG:='输血申请已经生效，不能删除该医嘱'; raise ECUSTOM; end if; ----已预约数据的处理------ begin select nvl(B.YYID00,0) into V_COUNT from BQ_YJYZ00 A,YJ_YW0000 B,YJ_YYBRB0 C where A.YZMXID=P_YZMXID and A.CLBZ00='1' and A.ZYID00+0=B.ZYGHID and B.XMZT00='6' and trim(B.YZCFID)=to_char(A.YZMXID) and nvl(B.YYID00,0)<>0 and B.YYID00=C.YYID00 and C.YYZT00='0' and ROWNUM=1; exception when others then V_COUNT:=0; end; if V_COUNT>0 then begin delete YJ_YYBRB0 where YYID00=V_COUNT; end; end if; delete BQ_YJYZ00 where YZMXID = P_YZMXID; delete XK_SXSQ00 where YZMXID = P_YZMXID; select count(1) into V_COUNT from SS_YW0000 where YZMXID = P_YZMXID and SSZT00 in ('2','4'); if V_COUNT > 0 then VERRMSG:='手术已被安排或已执行，不能删除该医嘱'; raise ECUSTOM; end if; update SS_YW0000 set SSZT00='7' where YZMXID=P_YZMXID ; --核对医嘱 elsif P_MODE00 = 1 then select count(1) into V_COUNT from BQ_YJYZ00 A where A.ZLXMID in (select ZLXMID from JY_BBCJKS) and A.YZMXID = P_YZMXID and A.YZZT00<>'3' and A.YZZT00<>'4' and (not exists (select 1 from JY_BBCJKS where to_char(KSBH00)=P_CJKS00 and ZLXMID=A.ZLXMID) or P_CJKS00=''); --select NVL(YZLX00,'0') into LS_YZLX00 from BQ_YJYZ00 where YZMXID=P_YZMXID; if V_COUNT = 0 then if (V_YZZT00=0) or (V_YZZT00 in (3,4) and (V_HDRXM0 is null or V_TZRXM0 is null)) then VERRMSG:=VERRMSG; else VERRMSG := '不是待核对的医嘱，不能核对'; raise ECUSTOM; end if; if (V_YZZT00=0) and (V_HDRXM0 is null) then--新开的医嘱 --修改医嘱状态和核对人 update BQ_YJYZ00 set YZZT00 = '1',HDRXM0 = P_HDRXM0 where YZMXID = P_YZMXID; elsif (V_YZZT00=0) and (V_HDRXM0 is not null) then--重整的医嘱 update BQ_YJYZ00 set YZZT00 = '2' where YZMXID = P_YZMXID; else --医生停止/撤销的医嘱 update BQ_YJYZ00 set TZRXM0 = P_HDRXM0 where YZMXID = P_YZMXID; end if; --判定是否仍然有新开的医嘱 select count(*) into V_COUNT from (select YZID00 from BQ_YPYZ00 where ZYID00=V_ZYID00 and YZZT00='0' and ROWNUM=1 union all select YZMXID from BQ_YJYZ00 where ZYID00=V_ZYID00 and YZZT00='0' and ROWNUM=1); if V_COUNT=0 then update XT_DZGG00 set YXZZRQ=to_char(sysdate,'YYYYMMDD'),YXZZSJ=to_char(sysdate,'HH24:MI:SS') where ZYID00=V_ZYID00 and CZLB00='1' and YXZZRQ='99991231'; end if; --判定是否仍然有撤销医嘱 select count(*) into V_COUNT from (select YZID00 from BQ_YPYZ00 where ZYID00=V_ZYID00 and YZZT00='3' and TZRXM0 is null and ROWNUM=1 union all select YZMXID from BQ_YJYZ00 where ZYID00=V_ZYID00 and YZZT00='3' and TZRXM0 is null and ROWNUM=1); if V_COUNT=0 then update XT_DZGG00 set YXZZRQ=to_char(sysdate,'YYYYMMDD'),YXZZSJ=to_char(sysdate,'HH24:MI:SS') where ZYID00=V_ZYID00 and CZLB00='3' and YXZZRQ='99991231'; end if; --判定是否仍然有停止医嘱 select count(*) into V_COUNT from (select YZID00 from BQ_YPYZ00 where ZYID00=V_ZYID00 and YZZT00='4' and TZRXM0 IS NULL and ROWNUM=1 union all select YZMXID from BQ_YJYZ00 where ZYID00=V_ZYID00 and YZZT00='4' and TZRXM0 IS NULL and ROWNUM=1); if V_COUNT=0 then update XT_DZGG00 set YXZZRQ=to_char(sysdate,'YYYYMMDD'),YXZZSJ=to_char(sysdate,'HH24:MI:SS') where ZYID00=V_ZYID00 and CZLB00='2' and YXZZRQ='99991231'; end if; --拆分医嘱20040916 --默认截至日期为当前时间加上30天 select to_char(sysdate+30, 'YYYYMMDD') into VJZRQ00 from DUAL; SP_BQ_YZCF00_SINGLE_ZYDZBL(VJZRQ00, P_YZMXID, '1'); end if; --停止医嘱 elsif P_MODE00 = 2 then BYTZYZ0 :=((V_TZYS00 is not null) and (V_YZZT00='5')); --判断是否提取医嘱 if not BYTZYZ0 then --正常停止 select count(1) into V_COUNT from BQ_YJYZ00 where YZZT00 = '2' and YZMXID = P_YZMXID; if V_COUNT = 0 then VERRMSG:='不是提取医嘱，不能停止'; raise ECUSTOM; end if; --判断停止时间是否大于和起用时间提取时间 select count(1) into V_COUNT from BQ_YJYZ00 where YZMXID = P_YZMXID and QYRQ00 <= P_TZRQ00 and QYRQ00||QYSJ00 <= P_TZRQ00||P_TZSJ00; --and ((TQRQ00 IS NULL and TQSJ00 IS NULL) -- OR (TQRQ00 <= P_TZRQ00 -- and TQRQ00||TQSJ00 <= P_TZRQ00||P_TZSJ00)); if V_COUNT = 0 then VERRMSG:='停止时间小于起用时间，不能停止'; raise ECUSTOM; end if; --调用非药品医嘱停止退费存储过程 SP_BQ_YJYZTZ( P_CZY000,P_YZMXID,P_TZRQ00,P_TZSJ00); -- update BQ_YJYZ00 set YZZT00 = '3',TZYS00 = P_TZYS00,--TZRXM0 = P_TZRXM0, TZRQ00 = P_TZRQ00,TZSJ00 = P_TZSJ00 where YZMXID = P_YZMXID; else --从预停止到停止 if P_HDRXM0 is null then VERRMSG := '核对人不允许为空！'; raise ECUSTOM; end if; if V_TZRXM0=P_HDRXM0 then VERRMSG := '核对和停止操作员不允许一样！'; raise ECUSTOM; end if; --调用非药品医嘱停止退费存储过程 SP_BQ_YJYZTZ( P_CZY000,P_YZMXID,V_TZRQ00,V_TZSJ00); -- update BQ_YJYZ00 set YZZT00 = '3',TZSHR0=(select YGBH00 from BM_YGBM00 where ZWXM00=P_HDRXM0 and ROWNUM=1) --增加停止审核人 where YZMXID = P_YZMXID; end if; if P_TZRXM0='' or P_TZRXM0 is null then select count(*)into V_COUNT from XT_DZGG00 where ZYID00=V_ZYID00 and CZLB00='2'; if V_COUNT=0 then insert into XT_DZGG00(ID0000,ZYID00,FSBQ00,FSBQMC,FSKS00,FSKSMC,FSXX00, YXQSRQ,YXQSSJ,YXZZRQ,YXZZSJ,CZY000,CZLB00,XTDM00) select SQ_XT_DZGG00_ID0000.nextval,ZYID00,0,' ',DQBQ00,' ','"'||trim(RYCWH0)||'床'||trim(ZYH000)||' '||trim(XM0000)||'" --有医嘱停止 '||to_char(sysdate,'HH24:MI'), to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),'99991231','00:00:00',P_CZY000,'2','9' from ZY_BRXXB0 where ZYID00=V_ZYID00; end if; end if; --取消医嘱 elsif P_MODE00 = 3 then begin select VALUE0 into VYTZLDZSFKYCX from XT_XTCS00 where NAME00='ZS_YTZLDZSFKYCX'; exception when others then VYTZLDZSFKYCX:='N'; end; --判断是否新开或核对医嘱 select count(1) into V_COUNT from BQ_YJYZ00 where YZMXID = P_YZMXID and ((YZZT00 in ('0','1')) OR (YZZT00='3' and CLBZ00='1' and trim(VYTZLDZSFKYCX)='Y')); if V_COUNT = 0 then if trim(VYTZLDZSFKYCX)='Y' then VERRMSG:='不是新开或核对、或已经停止的短嘱类 医嘱，不能取消'; else VERRMSG:='不是新开或核对 医嘱，不能取消'; end if; raise ECUSTOM; end if; select count(1) into V_COUNT from SS_YW0000 where YZMXID = P_YZMXID and SSZT00 in ('2','4'); if V_COUNT > 0 then VERRMSG:='手术已被安排或已执行，不能删除该医嘱'; raise ECUSTOM; end if; ----撤销出院医嘱 select count(1) into V_COUNT from XT_XTCS00 where NAME00= 'ZS_SFZCYBZYZPD' and VALUE0<>'N'; if V_COUNT > 0 then select count(1) into V_COUNT from BQ_YJYZ00 A where YZMXID = P_YZMXID and A.YEXM00 is null and replace(replace(A.ZLXMJC,'(描述)',''),'(取消)','') in (select MC0000 from BM_TYZD00 where ZDMC00='出院标志性医嘱') ; if V_COUNT > 0 then SP_ZS_BRCYRQXG_ZYDZBL(V_ZYID00,'20991231',' ','1'); end if; end if; update BQ_YJYZ00 set YZZT00 = '4',TZYS00 = P_TZYS00,TZRXM0 = P_TZRXM0,ZLXMJC=substrb('(取消)'||ZLXMJC,1,40), TZRQ00 = P_TZRQ00,TZSJ00 = P_TZSJ00 where YZMXID = P_YZMXID; -- DELETE XK_SXSQ00 where YZMXID = P_YZMXID; update SS_YW0000 set SSZT00='7'where YZMXID=P_YZMXID; if P_TZRXM0='' or P_TZRXM0 is null then select count(*)into V_COUNT from XT_DZGG00 where ZYID00=V_ZYID00 and CZLB00='3'; if V_COUNT=0 then insert into XT_DZGG00(ID0000,ZYID00,FSBQ00,FSBQMC,FSKS00,FSKSMC,FSXX00, YXQSRQ,YXQSSJ,YXZZRQ,YXZZSJ,CZY000,CZLB00,XTDM00) select SQ_XT_DZGG00_ID0000.nextval,ZYID00,0,' ',DQBQ00,' ','"'||trim(RYCWH0)||'床'||trim(ZYH000)||' '||trim(XM0000)||'" --有医嘱撤消 '||to_char(sysdate,'HH24:MI'), to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),'99991231','00:00:00',P_CZY000,'3','9' from ZY_BRXXB0 where ZYID00=V_ZYID00; end if; end if; ----已预约数据的处理------ begin select nvl(B.YYID00,0) into V_COUNT from BQ_YJYZ00 A,YJ_YW0000 B,YJ_YYBRB0 C where A.YZMXID=P_YZMXID and A.CLBZ00='1' and A.ZYID00+0=B.ZYGHID and B.XMZT00='6' and trim(B.YZCFID)=to_char(A.YZMXID) and nvl(B.YYID00,0)<>0 and B.YYID00=C.YYID00 and C.YYZT00='0' and ROWNUM=1; exception when others then V_COUNT:=0; end; if V_COUNT>0 then begin update YJ_YYBRB0 set QXRQ00=to_char(sysdate,'YYYYMMDD'),QXSJ00=to_char(sysdate,'HH24:MI:SS'),YYZT00='2',BZ0000='操作员: '||V_CZYXM0||' 进行了医嘱撤销！' where YYID00=V_COUNT; end; end if; --排序医嘱 elsif P_MODE00 = 4 then update BQ_YJYZ00 set PXSJ00 = P_PXSJ00 where YZMXID = P_YZMXID; --医嘱预停止(实际应该是暂停) elsif P_MODE00 = 5 then --判断是否新开、核对、提取医嘱 /*select count(1) into V_COUNT from BQ_YJYZ00 where YZMXID = P_YZMXID and YZZT00 in ('0','1','2'); if V_COUNT = 0 then VERRMSG:='不是新开、核对、提取医嘱，不能预停止'; raise ECUSTOM; end if; */ select count(1) into V_COUNT from BQ_YJYZ00 where YZMXID = P_YZMXID and YZZT00 in ('2') and CLBZ00 = '0'; if V_COUNT = 0 then VERRMSG := '非使用长期非药医嘱，不能预停止'; raise ECUSTOM; end if; --判断停止时间是否大于和起用时间提取时间 select count(1) into V_COUNT from BQ_YJYZ00 where YZMXID = P_YZMXID and QYRQ00 <= P_TZRQ00 and QYRQ00||QYSJ00 <= P_TZRQ00||P_TZSJ00; if V_COUNT = 0 then VERRMSG:='停止时间小于起用时间，不能停止'; raise ECUSTOM; end if; --取医嘱信息 select * into V_YJYZ00 from BQ_YJYZ00 where YZMXID = P_YZMXID; --取医嘱时间点 select trim(VALUE0) into V_YZSJFG from XT_XTCS00 where NAME00='BQ_FYYZSJFG'; --插入 insert into BQ_YZZXJL( ZYID00, YZMXID, TQDJH0, DJHLB0, FYDJH0, ZT0000, CZR000, YZID00, QSRQ00, QSSJ00, JSRQ00, JSSJ00, SJJSRQ, SJJSSJ, CZRQ00, CZSJ00, SCJSRQ, CS0000 ) values ( V_YJYZ00.ZYID00, V_YJYZ00.YZMXID, 0, 1, 0, '3', P_CZY000, 0,--V_YJYZ00.YZMXID, decode(V_YJYZ00.YZZT00,'1',V_YJYZ00.QYRQ00,'2',to_char(to_date(V_YJYZ00.TQRQ00,'YYYYMMDD')+1,'YYYYMMDD')), V_YZSJFG, decode(V_YJYZ00.YZZT00,'1',V_YJYZ00.QYRQ00,'2',to_char(to_date(V_YJYZ00.TQRQ00,'YYYYMMDD')+2,'YYYYMMDD')), V_YZSJFG, decode(V_YJYZ00.YZZT00,'1',V_YJYZ00.QYRQ00,'2',to_char(to_date(V_YJYZ00.TQRQ00,'YYYYMMDD')+2,'YYYYMMDD')), V_YZSJFG, to_char(sysdate,'YYYYMMDD'), to_char(sysdate,'HH24:MI:SS'), V_YJYZ00.TQRQ00||V_YJYZ00.TQSJ00, 0 ); update BQ_YJYZ00 set YZZT00='5',TZYS00 = P_TZYS00,TZRXM0 = P_TZRXM0, TZRQ00 = P_TZRQ00,TZSJ00 = P_TZSJ00 where YZMXID = P_YZMXID; elsif P_MODE00 = 7 then select count(1) into V_COUNT from BQ_YJYZ00 where YZMXID = P_YZMXID and YZZT00 in ('7'); if V_COUNT = 0 then VERRMSG := '没有新开未提交的医嘱!*还回信息:'; raise ECUSTOM; end if; select count(1) into V_COUNT from ZY_BRXXB0 where ZYID00=V_ZYID00 and BRZT00 in ('3'); if V_COUNT > 0 then VERRMSG := '病人在病区已做出院登记，不能提交医嘱。'; raise ECUSTOM; end if; begin select a.ZLXMJC into V_ZLXMJC from BQ_YJYZ00 A where (not exists(select 1 from VW_BM_ZLZD00_ZYDZBL T where T.ZLXMID=A.ZLXMID) and ZLXMID<>0 ) and A.YZMXID=P_YZMXID and rownum<=1; exception when others then V_ZLXMJC:=''; end; if V_ZLXMJC is not null then VERRMSG:=V_ZLXMJC||'该项目已停止的项目不允许提交。'; raise ECUSTOM; end if; begin select a.ZLXMJC into V_ZLXMJC from BQ_YJYZ00 A where KZYS00<>P_CZY000 and A.YZMXID=P_YZMXID and exists (select 1 from XT_XTCS00 where NAME00='ZS_YZTJYSXZ' and VALUE0='Y' ) and rownum<=1; exception when others then V_ZLXMJC:=''; end; if V_ZLXMJC is not null then VERRMSG:=V_ZLXMJC||'开单医生和提交医生不是同一个人不允许提交。'; raise ECUSTOM; end if; begin select a.ZLXMJC into V_ZLXMJC from BQ_YJYZ00 A where A.YZMXID=P_YZMXID and MCSL00>1 and exists (select 1 from XT_XTCS00 where NAME00='ZS_YZZXKSXZ' and instr(VALUE0,'|'||ZXKS00||'|')>0) and rownum<=1; exception when others then V_ZLXMJC:=''; end; if V_ZLXMJC is not null then VERRMSG:=V_ZLXMJC||'项目数量只允许为1，请修改后再提交'; raise ECUSTOM; end if; select nvl(max(trim(VALUE0)),'N') into LS_SFZCYBZYZPD from XT_XTCS00 where NAME00='ZS_SFZCYBZYZPD' and VALUE0='Y'; if LS_SFZCYBZYZPD='Y' then select count(*) into V_COUNT from ZS_BLXX01 A where ZYID00=V_ZYID00 and CYRQ00<>'20991231' and exists(select 1 from BM_TYZD00 X,BQ_YJYZ00 Y where Y.YEXM00 is null and X.ZDMC00='出院标志性医嘱' and instr(Y.ZLXMJC,X.MC0000)>0 and Y.YZMXID = P_YZMXID ) ; if V_COUNT>0 then VERRMSG:='如果要新开出院医嘱，必须先撤销已开的出院医嘱，或先开“取消出院”。'; raise ECUSTOM; end if; select count(*) into V_COUNT from ZS_BLXX01 A where ZYID00=V_ZYID00 and CYRQ00<>' ' and CYRQ00||CYSJ00<=to_char(sysdate,'YYYYMMDDHH24:MI:SS') and CYRQ00 is not null and CYRQ00 <>'20991231' and (SWRQ00 is null or SWRQ00=' ') and not exists(select 1 from BQ_YJYZ00 Y where instr(Y.ZLXMJC,'取消出院')>0 and Y.YZMXID = P_YZMXID ) ; if V_COUNT>0 then VERRMSG:='当前提交的医嘱已经超过出院时间，不允许提交。如需新增医嘱，请先撤销出院医嘱或开取消出院。'; raise ECUSTOM; end if; select count(*) into V_COUNT from ZS_BLXX01 A where ZYID00=V_ZYID00 and ( SWRQ00<>'20991231' and SWRQ00 <>' ' ) and exists (select 1 from BQ_YJYZ00 where YZMXID = P_YZMXID and YEXM00 is null ) and not exists(select 1 from BQ_YJYZ00 Y where (instr(Y.ZLXMJC,'取消出院')>0 or Y.BLBZ00='Y') and Y.YZMXID = P_YZMXID ) ; if V_COUNT>0 then VERRMSG:='在死亡医嘱后继续开医嘱请勾选补录。'; raise ECUSTOM; end if; end if; select count(*) into V_COUNT from BQ_YJYZ00 A where ZYID00=V_ZYID00 and YZMXID = P_YZMXID and exists (select 1 from BM_ZLZD00 B where A.ZLXMID=B.ZLXMID and LBBH00 in( select LBBH00 from BM_ZLXMLB where LBMC00='输血' and YXBZ00='Y')) and not exists (select 1 from VW_ZS_SXBLDYJL_ZYDZBL C where C.ZYID00=V_ZYID00 ) and exists (select 1 from XT_XTCS00 where NAME00='ZS_SFBXDYSXTYS' and VALUE0='Y' ) ; if V_COUNT>0 then VERRMSG:='输血知情同意书打印后才能提交输血医嘱。'; raise ECUSTOM; end if; select count(*) into V_COUNT from BQ_YJYZ00 A where ZYID00=V_ZYID00 and YZMXID = P_YZMXID and A.ZLXMJC like '输血%' and not exists (select 1 from XK_SXSQ00 C where ZYID00=V_ZYID00 ) and exists (select 1 from XT_XTCS00 where NAME00='ZS_SXSQGLYZ' and VALUE0='Y' ) ; if V_COUNT>0 then VERRMSG:='开输血医嘱前，请先提交输血申请。'; raise ECUSTOM; end if; select count(*) into V_COUNT from BQ_YJYZ00 A where ZYID00=V_ZYID00 and YZMXID = P_YZMXID and A.ZLXMJC like '备血%' and not exists (select 1 from XK_SXSQ00 C where YZMXID = P_YZMXID ) and exists (select 1 from XT_XTCS00 where NAME00='ZS_SXSQGLYZ' and VALUE0='Y' ) ; if V_COUNT>0 then VERRMSG:='开备血医嘱前，请先提交输血申请,备血医嘱必须从输血申请带出。'; raise ECUSTOM; end if; begin select a.ZLXMJC into V_ZLXMJC from BM_ZLZD00 T ,BQ_YJYZ00 A where T.ZLXMID = A.ZLXMID and A.YZMXID=P_YZMXID and LBBH00='4' and ( A.JZBZ00='Y' or A.PCID00 in (select PCID00 from BM_PC0000 where upper(PCMC00)='ST')) and nvl(T.SFXZJZ,'N')<> 'Y' and rownum<=1 ; exception when others then V_ZLXMJC:=''; end; if V_ZLXMJC is not null then VERRMSG:=V_ZLXMJC||'该项目不是急诊项目，不允许选择应急。'; raise ECUSTOM; end if; begin select a.ZLXMJC into V_ZLXMJC from BM_ZLZD00 T, BQ_YJYZ00 A ,ZY_BRXXB0 B where T.ZLXMID = A.ZLXMID and A.YZMXID = P_YZMXID and A.ZYID00=B.ZYID00 and (T.XBXZ00 = '2' and B.XB0000='男') and rownum<=1 ; exception when others then V_ZLXMJC:=''; end; if V_ZLXMJC is not null then VERRMSG:=V_ZLXMJC||'此项目只限制女性专用.'; raise ECUSTOM; end if; begin select a.ZLXMJC into V_ZLXMJC from BM_ZLZD00 T, BQ_YJYZ00 A ,ZY_BRXXB0 B where T.ZLXMID = A.ZLXMID and A.YZMXID = P_YZMXID and A.ZYID00=B.ZYID00 and (T.XBXZ00 = '1' and B.XB0000='女') and rownum<=1 ; exception when others then V_ZLXMJC:=''; end; if V_ZLXMJC is not null then VERRMSG:=V_ZLXMJC||'此项目只限制男性专用.'; raise ECUSTOM; end if; select nvl(YZLX00,'0') into LS_YZLX00 from BQ_YJYZ00 where YZMXID=P_YZMXID; --是否医嘱启用时间默认提交时间 select nvl(max(trim(VALUE0)),'N') into LS_YZQYSJMRTJSJ from XT_XTCS00 where NAME00='ZS_YZQYSJMRTJSJ'; if (LS_YZQYSJMRTJSJ = 'Y') then select nvl(max(trim(VALUE0)),'N') into LS_DQSJWYZQYSJ from XT_XTCS00 where NAME00='ZS_YZ_DQSJWYZQYSJ'; if LS_DQSJWYZQYSJ = 'Y' then select nvl(A.QSRQ00,B.RYRQ00), B.RYCWH0 into V_RYRQ00, V_RYCWH0 from BQ_BRLDXX A,ZY_BRXXB0 B where A.ZYID00(+)=B.ZYID00 and B.ZYID00 = V_ZYID00 and A.QSZT00='新入' and ROWNUM=1; select nvl(max(trim(VALUE0)),'N') into LS_DTRYBRSFKYXGQYRQ from XT_XTCS00 where NAME00='ZS_DTRYBRSFKYXGQYRQ'; --当天入院病人是否可以修改起用日期 if(V_RYRQ00 = to_char(V_SYSDATE,'YYYYMMDD') and LS_DTRYBRSFKYXGQYRQ <> 'N' ) then V_COUNT := 0; else V_COUNT := 1; end if; if (V_RYCWH0 is null) then VERRMSG:='病人未指定床号，不能提交医嘱！'; raise ECUSTOM; end if; if (V_COUNT = 0) then select count(1) into V_COUNT from BQ_YJYZ00 where YZMXID = P_YZMXID and ((to_date(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS') - to_date(LRRQ00||LRSJ00,'YYYYMMDDHH24:MI:SS'))*24*60*60) between -900 and 60; --非当天入院的病人，或者当天入院且录入时间和启用时间相差10分钟以内的，取医嘱的提交时间作为起用时间 end if; if(V_COUNT>0) then --60秒内的医嘱，取相同的提交时间作为启用时间 /* select NVL(MAX(QYRQ00),TO_char(V_SYSDATE,'YYYYMMDD')),NVL(MAX(QYSJ00),TO_char(V_SYSDATE,'HH24:MI:SS')) into V_QYRQ00, V_QYSJ00 from BQ_YJYZ00 where ZYID00 = V_ZYID00 and ((sysdate - TO_DATE(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS'))*24*60*60) BETWEEN -1 and 60; if(V_QYRQ00||V_QYSJ00 = TO_char(V_SYSDATE,'YYYYMMDDHH24:MI:SS')) then --60秒内的医嘱，取相同的提交时间作为启用时间 select NVL(MAX(QYRQ00),TO_char(V_SYSDATE,'YYYYMMDD')),NVL(MAX(QYSJ00),TO_char(V_SYSDATE,'HH24:MI:SS')) into V_QYRQ00, V_QYSJ00 from BQ_YPYZ00 where ZYID00 = V_ZYID00 and ((sysdate - TO_DATE(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS'))*24*60*60) BETWEEN -1 and 60; end if;*/ update BQ_YJYZ00 set QYRQ00=P_TZRQ00, QYSJ00=P_TZSJ00 where YZMXID = P_YZMXID; end if; end if; end if; if LS_YZLX00 not in('4','5') then update BQ_YJYZ00 set YZZT00 = '0',YZTJRQ=to_char(sysdate,'YYYYMMDD'),YZTJSJ=to_char(sysdate,'HH24:MI:SS'),KZYS00=DECODE(P_SSSBZ0,'Y',KZYS00,decode(nvl(P_CZY000,0),0,KZYS00,P_CZY000)) where YZMXID = P_YZMXID; else update BQ_YJYZ00 set YZZT00 = '0',YZTJRQ=to_char(sysdate,'YYYYMMDD'),YZTJSJ=to_char(sysdate,'HH24:MI:SS') where YZMXID = P_YZMXID; end if; select count(*)into V_COUNT from XT_DZGG00 where ZYID00=V_ZYID00 and CZLB00='1'; if (V_COUNT=0) and (P_SSSBZ0='N') then insert into XT_DZGG00(ID0000,ZYID00,FSBQ00,FSBQMC,FSKS00,FSKSMC,FSXX00, YXQSRQ,YXQSSJ,YXZZRQ,YXZZSJ,CZY000,CZLB00,XTDM00) select SQ_XT_DZGG00_ID0000.nextval,ZYID00,0,' ',DQBQ00,' ','"'||trim(RYCWH0)||'床'||trim(ZYH000)||' '||trim(XM0000)||'" --有新开医嘱 '||to_char(sysdate,'HH24:MI'), to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),'99991231','00:00:00',P_CZY000,'1','9' from ZY_BRXXB0 where ZYID00=V_ZYID00; end if; select nvl(JHMXID,0), nvl(YZLX00,'0') into LS_JHMXID,LS_YZLX00 from BQ_YJYZ00 where YZMXID= P_YZMXID and ROWNUM<=1; if LS_JHMXID>0 then select ZYID00 into LS_ZYID00 from BQ_YJYZ00 where YZMXID= P_YZMXID; begin select ZRZCID into LS_ZRZCID from LJ_ZRZC00 where ZYID00=LS_ZYID00 and NVL(JSRQ00,' ')=' ' ; exception when others then LS_ZRZCID:=0; end; if LS_ZRZCID>0 then SP_ZS_LJ_DELETEBYJL(LS_ZRZCID,LS_JHMXID); end if; end if; if LS_YZLX00 in('4','5') then --BQ-20131024-001 update BQ_YJYZ00 set YZZT00='3' ,TZRQ00=to_char(sysdate,'YYYYMMDD') ,TZSJ00=to_char(sysdate,'HH24:MI:SS') --,TZRXM0 = V_CZYXM0 ,HDRXM0=V_CZYXM0 ,TZYS00=KZYS00 where YZMXID=P_YZMXID; end if; select count(*) into V_COUNT from BQ_YJYZ00 A,YJ_KSPBHB B where A.ZXKS00=B.YJKSBH and B.HBSYBZ='0' and B.YYRQ00>=to_char(sysdate,'YYYYMMDD') and A.YZMXID=P_YZMXID and A.CLBZ00='1' and A.YZLX00<>'4'; /* if V_COUNT>0 then begin COMMIT; --底下执行有可能有回滚 SP_BQ_YJYZZX_XZ 为了保证提交能完成 --为了后面能调用正常的项目执行过程先弄成核对状态 update BQ_YJYZ00 set YZZT00='1' where YZMXID=P_YZMXID; select * into V_YJYZ00 from BQ_YJYZ00 where YZMXID=P_YZMXID; select TO_char(sysdate+30, 'YYYYMMDD') into VJZRQ00 from DUAL; SP_BQ_YZCF00_SinGLE(VJZRQ00, P_YZMXID, '1'); SP_BQ_YJYZZX_XZ(P_CZY000,V_YJYZ00.ZYID00,V_YJYZ00.BQH000,'0,'||TO_char(P_YZMXID),TO_DATE(V_YJYZ00.QYRQ00,'YYYYMMDD'),PBCTQID,NULL,'Y'); update BQ_YJYZ00 set YZZT00='0' where YZMXID=P_YZMXID; --还原状态 end; end if;*/ elsif P_MODE00 = 8 then select count(1) into V_COUNT from BQ_YJYZ00 where YZMXID = P_YZMXID and YZZT00 in ('2') and CLBZ00 = '0'; if V_COUNT = 0 then VERRMSG := '非使用长期非药医嘱，不能预停止'; raise ECUSTOM; end if; --判断停止时间是否大于和起用时间提取时间 select count(1) into V_COUNT from BQ_YJYZ00 where YZMXID = P_YZMXID and QYRQ00 <= P_TZRQ00 and QYRQ00||QYSJ00 <= P_TZRQ00||P_TZSJ00; if V_COUNT = 0 then VERRMSG:='预停止时间小于起用时间，不能预停止'; raise ECUSTOM; end if; update BQ_YJYZ00 set TZYS00 = P_TZYS00,TZRXM0 = P_TZRXM0,YTZRQ0 = P_TZRQ00,YTZSJ0 = P_TZSJ00 where YZMXID = P_YZMXID; elsif P_MODE00 = 9 then--增加取回提交 20130113 阮炳辉 select count(1),nvl(max(DYPC00),0) into V_COUNT,V_DYPC00 from BQ_YJYZ00 where YZMXID=P_YZMXID and YZZT00='0'; if V_COUNT = 0 then VERRMSG := '没有已提交的医嘱!'; raise ECUSTOM; end if; if V_DYPC00 > 0 then VERRMSG:='已经打印过的医嘱不能取回，如果要取回请先撤消打印记录'; raise ECUSTOM; end if; update BQ_YJYZ00 set YZZT00 = '7',TJYS00='' where YZMXID = P_YZMXID; end if; exception when NO_DATA_FOUND then RAISE_APPLICATION_ERROR(-20001,substrb('数据没有找到！*'||sqlerrm||','||VPARAMS,1,220)); when ECUSTOM then RAISE_APPLICATION_ERROR(-20010,substrb(VERRMSG||'！*'||VPARAMS,1,220)); when others then RAISE_APPLICATION_ERROR(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*'||VPARAMS,1,220)); end; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_BQ_YJYZQT_DZBL