CREATE OR REPLACE PROCEDURE sp_cpoe_medical_order_verifiy( P_FLAG in Varchar2 , --Y 标志1 提交核对,2 停止核对,3 撤销核对,4 作废核对 P_PARENT_ORDER_SN in Varchar2,-- IN Y 主医嘱ID P_ORDER_SN in varchar2, P_ORDER_TYPE in Varchar2,-- 医嘱类型附件目录3.1 P_USER_NAME in varchar2,--核对人 P_USER_ID in Varchar2,-- IN Y 核对人ID P_OPER_USER_ID in varchar2,-- 操作人ID P_OPER_USER_NAME in varchar2,-- 操作人姓名 P_OPER_DATE_TIME in varchar2,--操作时间 P_DOCTOR_ID in Varchar2,-- 医生ID P_DOCTOR_NAME in Varchar2,--医生姓名 P_ERRORCODE out Varchar2,--错误代码0 错误1 正确 P_ERRORINFO out Varchar2,--错误信息 P_SYSTEMINFO out Varchar2 --系统错误信息 ) as -- MODIFICATION HISTORY -- Person Date Comments -- wangzy 2018.12.25 核对增加核对人，核对日期时间 -- dongxb 2022.03.13 核对后直接提取或者执行 -- xuxm 2022.03.29 增加项目费用和药品附加费 和库存数量判断 VYZID00 BQ_YPYZ00.YZID00%type; VZYID00 ZY_BRXXB0.ZYID00%type; VYZZT00 BQ_YPYZ00.YZZT00%type; VCLBZ00 BQ_YPYZ00.CLBZ00%type; VBQH000 BQ_YPYZ00.BQH000%type; VQRRQ00 BQ_YPYZ00.QYRQ00%type; VSFHDLZHZJTQZX XT_XTCS00.VALUE0%type; v_Count number(4); Ecustom EXCEPTION ; --错误提示 Vzxcgbz number(1); VBCTQID number(9); VYGBH00 BM_YGBM00.YGBH00%type; VPARAMS varchar(4000); VJZRQ00 BQ_YPYZ00.QYRQ00%type; Vkyje00 number; Vxmje00 number; Vypje00 number; Vfjf000 number; vykhye0 number; begin select YZID00,ZYID00,BQH000,QYRQ00,CLBZ00,YZZT00 into VYZID00,VZYID00,VBQH000,VQRRQ00,VCLBZ00,VYZZT00 from BLJK_YZJKB0 where WBZID0=P_PARENT_ORDER_SN and WBYZID=P_ORDER_SN and YZID00>0; VPARAMS:='SP_CPOE_MEDICAL_ORDER_VERIFIY('||NVL(P_FLAG,'NULL')||','||NVL(P_PARENT_ORDER_SN,'NULL')||','|| NVL(P_ORDER_SN,'NULL')||','||NVL(P_ORDER_TYPE,'NULL')||','||nvl(P_USER_ID,'NULL')||','||NVL(P_OPER_USER_ID,'NULL')|| NVL(P_OPER_USER_NAME,'NULL')||','||NVL(P_OPER_DATE_TIME,'NULL')||','||nvl(P_DOCTOR_ID,'NULL')||','||NVL(P_DOCTOR_NAME,'NULL'); begin select YGBH00 into VYGBH00 from BM_YGBM00 where XKH000=P_USER_ID; exception when others then P_ERRORCODE:='0'; P_ERRORINFO:='胸卡号为：'||P_USER_ID||'员工不存在'; P_SYSTEMINFO:='胸卡号为：'||P_USER_ID||'员工不存在'; raise Ecustom; end; if P_FLAG='1' then --提交核对 --##费用判断 可用余额 Vkyje00:=SF_BQ_KYJE00(VZYID00,0); --药品费用 select sum(JE0000) into Vypje00 from VW_BQ_YPYZJE_HT A,BLJK_YZJKB0 B where A.ZYID00=B.ZYID00 and B.ZYID00=VZYID00 and A.YZID00=B.YZID00 and B.SFPLCL='1'; --项目费用和药品附加费 select sum(JE0000) into Vfjf000 from VW_BQ_FJZXXM_HT A,BLJK_YZJKB0 B where A.YZID00=B.YZID00 and B.ZYID00=VZYID00 and A.ZYID00=B.ZYID00 and B.SFPLCL='1' ; vykhye0:=nvl(Vypje00,0)+nvl(Vfjf000,0) ; if vykhye0>Vkyje00 and vykhye0>0 then ROLLBACK; P_SYSTEMINFO:='患者账户预扣可用余额不足，患者可用余额：'||Vkyje00||'元，所有项目（药品、非药品项目）未扣费金额：'||vykhye0||'元'; raise Ecustom; end if; ---2022.05.23修改后语句 增加条件bljk_yzjkb0.yzcllb的医嘱处理类别不等于自备药 select count(1) into v_Count from VW_BQ_YPYZJE_HT A,BLJK_YZJKB0 B where A.ZYID00=B.ZYID00 and B.ZYID00=VZYID00 and A.YZID00=B.YZID00 and LYL001>YKKCSL and B.SFPLCL='1' and b.yzcllb<>'自备药'; ---2022.05.23未修改前语句 --select count(1) into v_Count from VW_BQ_YPYZJE_HT A,BLJK_YZJKB0 B where A.ZYID00=B.ZYID00 and B.ZYID00=VZYID00 and A.YZID00=B.YZID00 and LYL001>YKKCSL and B.SFPLCL='1'; if v_Count>0 then declare cursor c_yp is select A.YPMC00 FROM VW_BQ_YPYZJE_HT A,BLJK_YZJKB0 B where A.ZYID00=B.ZYID00 and B.ZYID00=VZYID00 and A.YZID00=B.YZID00 and LYL001>YKKCSL and B.SFPLCL='1'; begin for yp in c_yp loop P_SYSTEMINFO:=P_SYSTEMINFO||yp.YPMC00||','; end loop; P_SYSTEMINFO:=P_SYSTEMINFO||'库存不足'; raise Ecustom; end; end if; ---2022.05.23修改后语句 增加条件bljk_yzjkb0.yzcllb的医嘱处理类别不等于自备药 select count(1) into v_Count FROM VW_BQ_YPYZJE_HT A,BLJK_YZJKB0 B where A.ZYID00=B.ZYID00 and B.ZYID00=VZYID00 and A.YZID00=B.YZID00 and B.SFPLCL='1' and b.yzcllb<>'自备药' ---2022.05.23未修改前语句 --select count(1) into v_Count FROM VW_BQ_YPYZJE_HT A,BLJK_YZJKB0 B where A.ZYID00=B.ZYID00 and B.ZYID00=VZYID00 and A.YZID00=B.YZID00 and B.SFPLCL='1' and (a.ztbz00 = '1' or not exists (select 1 from yf_pckcmx c where b.yfbmbh = c.yfbmbh and c.ypnm00 = a.YPNM00 and c.SFYX00 = 'Y')) ; if v_Count>0 then declare cursor c_yp is select A.YPMC00 FROM VW_BQ_YPYZJE_HT A,BLJK_YZJKB0 B where A.ZYID00=B.ZYID00 and B.ZYID00=VZYID00 and A.YZID00=B.YZID00 and (a.ztbz00 = '1' or not exists (select 1 from yf_pckcmx c where b.yfbmbh = c.yfbmbh and c.ypnm00 = a.YPNM00 and c.SFYX00 = 'Y')) and B.SFPLCL='1'; begin for yp in c_yp loop P_SYSTEMINFO:=P_SYSTEMINFO||yp.YPMC00||','; end loop; P_SYSTEMINFO:=P_SYSTEMINFO||'已暂停使用'; raise Ecustom; end; end if; update BLJK_YZJKB0 set SFPLCL='0' where WBZID0=P_PARENT_ORDER_SN and WBYZID=P_ORDER_SN ; --##临瞩直接生成拆分数据，直接生成摆药申请 select nvl(max(value0),'N') into VSFHDLZHZJTQZX from XT_XTCS00 where name00='BQ_SFHDLZHZJTQZX'; if P_ORDER_TYPE in ('XY','ZCY','CY') then select YZZT00,CLBZ00 into VYZZT00,VCLBZ00 from BQ_YPYZ00 where ZYID00=VZYID00 and YZID00=VYZID00; if VYZZT00<>'0' then P_ERRORCODE:='0'; P_ERRORINFO:='此医嘱不是提交状态的医嘱，不能校对'; P_SYSTEMINFO:='此医嘱不是提交状态的医嘱，不能校对'; raise Ecustom; end if; update BQ_YPYZ00 set YZZT00='1',HDRXM0=P_USER_NAME,hdr000=VYGBH00,HDRQ00=to_char(sysdate,'YYYYMMDD'),HDSJ00=to_char(sysdate,'HH24:MI:SS') where ZYID00=VZYID00 and YZID00=VYZID00; if VSFHDLZHZJTQZX='M' then --##生成BQ_YPYZCF数据 SP_BQ_YPYZ00_AUDIT_EMRJK(1,VYZID00); --##生成BQ_YZCFMX数据 select TO_CHAR(SYSDATE+2, 'YYYYMMDD') INTO VJZRQ00 FROM DUAL; SP_BQ_YZCF00_SINGLE(VJZRQ00, VYZID00, '0'); --##药品摆药申请 sp_bq_ypyztq_xz_hg(Pzyid00 => VZYID00, Pbqh000 => VBQH000, Ptqrq00 => VQRRQ00, Pyzid00 => VYZID00, Pyfbmbh => null, Pbctqid => VBCTQID, Pczy000 => VYGBH00, Pczyks0 => VBQH000, Pcommit => 'N', Pzxcgbz => Vzxcgbz, Ptsxx00 => P_ERRORINFO, Pxxxx00 => P_SYSTEMINFO); end if; else select YZZT00,CLBZ00 into VYZZT00,VCLBZ00 from BQ_YJYZ00 where ZYID00=VZYID00 and YZMXID=VYZID00; if VYZZT00<>'0' then P_ERRORCODE:='0'; P_ERRORINFO:='此医嘱不是提交状态的医嘱，不能校对'; P_SYSTEMINFO:='此医嘱不是提交状态的医嘱，不能校对'; raise Ecustom; end if; update BQ_YJYZ00 set YZZT00='1',HDRXM0=P_USER_NAME,hdr000=VYGBH00,HDRQ00=to_char(sysdate,'YYYYMMDD'),HDSJ00=to_char(sysdate,'HH24:MI:SS') where ZYID00=VZYID00 and YZMXID=VYZID00; if VSFHDLZHZJTQZX='M' then --##生成BQ_YJYZCF数据 SP_BQ_YJYZ00_AUDIT_EMRJK(1,VYZID00); --##生成BQ_YZCFMX数据 select TO_CHAR(SYSDATE+2, 'YYYYMMDD') INTO VJZRQ00 FROM DUAL; --SP_BQ_YZCF00_SINGLE(to_date(VQRRQ00,'yyyyMMdd'), VYZID00, '1'); SP_BQ_YZCF00_SINGLE(VJZRQ00, VYZID00, '1'); --commit; --##生成单据 -- begin SP_BQ_YJYZZX_XZ_HG(VYGBH00, VZYID00, Pbqh000 =>VBQH000, Pyzmxid =>VYZID00, Pzxrq00 =>to_date(VQRRQ00,'yyyyMMdd') , Pbctqid =>VBCTQID, Pcommit=>'N'); -- raise Ecustom; --exception -- when others then -- rollback; -- SP_TransLog(sysdate,'SP_CPOE_MEDICAL_ORDER_VERIFIY',VYGBH00,VBQH000,'住院ID：'||VYZID00||'，非药品自动执行异常，异常内容：'||P_SYSTEMINFO); --commit; --end; end if; end if; elsif P_FLAG='2' then if P_ORDER_TYPE in ('XY','ZCY','CY') then select YZZT00 into VYZZT00 from BQ_YPYZ00 where ZYID00=VZYID00 and YZID00=VYZID00; if VYZZT00 <>'4' then if VYZZT00 not in ('3','5','2') then P_ERRORCODE:='0'; P_ERRORINFO:='此医嘱不是停止状态的医嘱，不能停止校对'; P_SYSTEMINFO:='此医嘱不是停止状态的医嘱，不能停止校对'; raise Ecustom; end if; update BQ_YPYZ00 set TZRXM0 = P_OPER_USER_NAME,YZZT00=decode(YZZT00,'5','3','2','3',YZZT00) where YZID00 = VYZID00; end if; else select YZZT00 into VYZZT00 from BQ_YJYZ00 where ZYID00=VZYID00 and YZMXID=VYZID00; if VYZZT00 <>'4' then if VYZZT00 not in ('3','5','2') then P_ERRORCODE:='0'; P_ERRORINFO:='此医嘱不是停止状态的医嘱，不能停止校对'; P_SYSTEMINFO:='此医嘱不是停止状态的医嘱，不能停止校对'; raise Ecustom; end if; update BQ_YJYZ00 set TZRXM0 = P_OPER_USER_NAME,YZZT00=decode(YZZT00,'5','3','2','3',YZZT00) where ZYID00=VZYID00 and YZMXID=VYZID00; end if; end if; elsif P_FLAG in('3','4') then if P_ORDER_TYPE in ('XY','ZCY','CY') then select YZID00,ZYID00,BQH000,QYRQ00,CLBZ00,YZZT00 into VYZID00,VZYID00,VBQH000,VQRRQ00,VCLBZ00,VYZZT00 from BQ_YPYZ00 where YZID00=VYZID00; if vYZZT00<>'4' then if VCLBZ00 ='1' and vYZZT00='3' then --##增加判断已作废的以及已退费退药确认的可做作废处理 by dongxb 2022.03.23 select count(1) into v_Count from yf_yzypsq where yzid00=VYZID00 and zyid00=VZYID00 and (qlzt00='3' or (CXPCID>0 AND QLZT00 in('5','6'))); if v_Count>0 then update BQ_YPYZ00 set YZZT00 = '4',TZYS00 = P_DOCTOR_ID,TZRXM0 = P_OPER_USER_NAME, TZRQ00 = to_char(sysdate,'YYYYMMDD'),TZSJ00 = to_char(sysdate,'HH24:MI:SS') where yzid00=VYZID00 and zyid00=VZYID00 ; end if; else select count(1) into v_Count from YF_YZYPSQ where YZID00=VYZID00 and zyid00=VZYID00 ; if v_Count>0 then P_ERRORINFO := '医嘱已经摆药，不能撤消'; P_SYSTEMINFO:= '医嘱已经摆药，不能撤消'; raise Ecustom; end if; update BQ_YPYZ00 set YZZT00 = '4', TZYS00 =P_DOCTOR_ID, TZRXM0 = P_OPER_USER_NAME, TZRQ00 = to_char(sysdate,'YYYYMMDD'),TZSJ00 = to_char(sysdate,'HH24:MI:SS') where YZID00 = VYZID00; end if; end if; else update BQ_YJYZ00 set YZZT00 = '4',TZYS00 = P_DOCTOR_ID,TZRXM0 = P_OPER_USER_NAME,ZLXMJC=Substrb('(取消)'||ZLXMJC,1,40), TZRQ00 = to_char(sysdate,'YYYYMMDD'),TZSJ00 = to_char(sysdate,'HH24:MI:SS') where YZMXID = VYZID00; update ss_yw0000 set sszt00='7'where yzmxid=VYZID00; end if; end if; P_ERRORCODE:='1'; P_ERRORINFO:='成功'; P_SYSTEMINFO:=''; exception when Ecustom then rollback; P_ERRORCODE:='0'; when others then rollback; P_ERRORCODE:='0'; P_ERRORINFO:='校对医嘱错误'; P_SYSTEMINFO:=sqlerrm; end;