CREATE OR REPLACE PROCEDURE SP_SS_YZZXQR ( P_CZLX00 in varchar2 , --输入：执行开始0，执行结束或修改执行1 P_ZYID00 in number , --住院ID P_BQH000 in number , --病区号 P_YZID00 in number , --医嘱ID P_ZXR000 in number , --审核人 P_QRRQ00 in varchar2 , --确认日期 P_QRSJ00 in varchar2 , --确认时间 P_ZXRQ00 in varchar2 , --执行日期 P_ZXSJ00 in varchar2 , --执行时间 P_ID0000 in number default 0 , P_COMMIT in varchar2 default 'N' , --是否提交 P_ZXMXID out number , --执行明细ID P_ZXCGBZ out number , --执行成功标志 1成功 0失败 P_TSXX00 out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 P_XXXX00 out varchar2 , --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 P_INXML0 in varchar2 default '' --XML入参 ) as -- MODIFICATION HISTORY -- Person Date Comments -- chenHeyi 2023.11.14 create for SSMZ9-20231104-001 -- chenHeyi 2023.12.14 增加XML入参 SSMZ9-20231204-001 -- ------------ -------------- ----------------------------------------- V_YZID00 BQ_YZZXMX.YZID00%type; V_ZWXM00 varchar2(20); V_ZXMXID number; V_ZXSJ00 varchar2(20); V_COUNT0 number;--判断短期整装药品 V_COUNT1 number;--判断静滴用法类药品 V_SFDQZZFY varchar2(20); V_ZXBZ00 BQ_YZCFMX.ZXBZ00%type; LS_QSRQ00 BQ_YZZXMX.QSRQ00%type; LS_QSSJ00 BQ_YZZXMX.QSSJ00%type; LS_JSRQ00 BQ_YZZXMX.JSRQ00%type; LS_JSSJ00 BQ_YZZXMX.JSSJ00%type; cursor C_ZZFYYZ is select a.YZID00,c.ZXRQ00,c.ZXSJ00 from BQ_YPYZ00 a,BQ_YPYZMX b,BQ_YZCFMX c where a.YZID00=b.YZID00 and a.YZID00=c.YZID00 and a.YFID00 in(select YFID00 from BM_YPYF00 where YFLBID='0') and a.CLBZ00='1' and c.YZLB00='0' and b.ZYFYDW in('瓶','盒') and c.ZXRQ00=P_ZXRQ00 and a.YZID00=P_YZID00; begin P_ZXCGBZ:=0; select count(1) into V_COUNT0 from BQ_YPYZ00 a,BQ_YPYZMX b --,BQ_YZCFMX c where a.YZID00=b.YZID00 --and a.YZID00=c.YZID00 and a.YFID00 in(select YFID00 from BM_YPYF00 where YFLBID='0') and a.CLBZ00='1' and b.ZYFYDW in('瓶','盒') and a.YZID00=P_YZID00 ; --and c.ZXRQ00=P_ZXRQ00 select nvl(max(VALUE0),'N') into V_SFDQZZFY from XT_XTCS00 where name00='BQ_SFDQZZFY'; select ZWXM00 into V_ZWXM00 from BM_YGBM00 where YGBH00=P_ZXR000 and rownum<=1; /* select count(1) into V_COUNT1 from BQ_YPYZ00 a,BM_YPYF00 b where a.YFID00=b.YFID00 and b.YFLBID=2 and a.YZID00=P_YZID00; if V_COUNT0>0 and V_SFDQZZFY='Y' then for row in C_ZZFYYZ loop V_YZID00:=row.YZID00; V_ZXSJ00:=row.ZXSJ00; if P_CZLX00='0' then select SEQ_BQ_YZZXMX_ZXMXID.NEXTVAL into V_ZXMXID from dual; insert into BQ_YZZXMX (ZXMXID, YZMXID, YZID00, DQZT00, CZR000, SHR000, SHRXM0,SHRQ00, SHSJ00, CZRXM0, ZYID00, YPBSM0, YXBZ00) select V_ZXMXID,a.YZMXID,a.YZID00,'06',P_ZXR000,P_ZXR000,V_ZWXM00,P_QRRQ00,P_QRSJ00,V_ZWXM00,a.ZYID00,a.YPBSM0,'1' from VW_SS_YZCFMX_YZZXQK a where YZID00=V_YZID00 and a.DQBQ00=P_BQH000 and a.ZYID00=P_ZYID00 and a.ZXRQ00=P_ZXRQ00 and a.ZXSJ00=V_ZXSJ00; insert into BQ_ZXGCJL (ZXGCID, ZXMXID, SD0000, ZXR000, ZXRXM0, ZXRQ00, ZXSJ00, ZXZT00, YXBZ00) select SEQ_BQ_ZXGCJL_ZXGCID.NEXTVAL,V_ZXMXID,'',P_ZXR000,V_ZWXM00,P_QRRQ00,P_QRSJ00,'06','1' from VW_SS_YZCFMX_YZZXQK a where yzid00=V_YZID00 and a.DQBQ00=P_BQH000 and a.ZYID00=P_ZYID00 and a.ZXRQ00=P_ZXRQ00 and a.ZXSJ00=V_ZXSJ00; update BQ_YZCFMX a set ZXBZ00='01',ZXMXID=V_ZXMXID,SFBQZX='1',SJZXRQ=P_QRRQ00,SJZXSJ=P_QRSJ00 where a.yzid00=V_YZID00 and a.ZYID00=P_ZYID00 and NVL(A.YZXRQ0,a.ZXRQ00)=P_ZXRQ00 and NVL(A.YZXSJ0,a.ZXSJ00)=V_ZXSJ00; else select SEQ_BQ_YZZXMX_ZXMXID.NEXTVAL into V_ZXMXID from dual; update BQ_ZXGCJL a set a.YXBZ00='0' where 1=1 and exists( select 1 from BQ_YZCFMX b where a.ZXMXID=b.ZXMXID and b.YZID00=V_YZID00 and b.ZYID00=P_ZYID00 and nvl(b.YZXRQ0,b.ZXRQ00)=P_ZXRQ00 and nvl(b.YZXSJ0,b.ZXSJ00)=V_ZXSJ00); update BQ_YZZXMX a set a.YXBZ00='0' where a.YZID00=V_YZID00 and a.ZYID00=P_ZYID00 and exists( select 1 from BQ_YZCFMX b where a.ZXMXID=b.ZXMXID and a.ZYID00=P_ZYID00 and b.YZID00=V_YZID00 and nvl(b.YZXRQ0,b.ZXRQ00)=P_ZXRQ00 and nvl(b.YZXSJ0,b.ZXSJ00)=V_ZXSJ00); insert into BQ_YZZXMX (ZXMXID, YZMXID, YZID00, DQZT00, CZR000, SHR000, SHRXM0,SHRQ00, SHSJ00, CZRXM0, ZYID00, YPBSM0, YXBZ00) select V_ZXMXID,a.YZMXID,a.YZID00,'06',P_ZXR000,P_ZXR000,V_ZWXM00,P_QRRQ00,P_QRSJ00,V_ZWXM00,a.ZYID00,a.YPBSM0,'1' from VW_SS_YZCFMX_YZZXQK a where yzid00=V_YZID00 and a.DQBQ00=P_BQH000 and a.ZYID00=P_ZYID00 and a.ZXRQ00=P_ZXRQ00 and a.ZXSJ00=V_ZXSJ00; insert into BQ_ZXGCJL (ZXGCID, ZXMXID, SD0000, ZXR000, ZXRXM0, ZXRQ00, ZXSJ00, ZXZT00, YXBZ00) select SEQ_BQ_ZXGCJL_ZXGCID.NEXTVAL,V_ZXMXID,'',P_ZXR000,V_ZWXM00,P_QRRQ00,P_QRSJ00,'06','1' from VW_SS_YZCFMX_YZZXQK a where YZID00=V_YZID00 and a.DQBQ00=P_BQH000 and a.ZYID00=P_ZYID00 and a.ZXRQ00=P_ZXRQ00 and a.ZXSJ00=V_ZXSJ00; update BQ_YZCFMX a set ZXBZ00='01',ZXMXID=V_ZXMXID,SFBQZX='1',SJZXRQ=P_QRRQ00,SJZXSJ=P_QRSJ00 where a.YZID00=V_YZID00 and a.ZYID00=P_ZYID00 and NVL(A.YZXRQ0,a.ZXRQ00)=P_ZXRQ00 and NVL(A.YZXSJ0,a.ZXSJ00)=V_ZXSJ00; end if; if V_COUNT1=0 then update BQ_YZZXMX set QSRQ00=P_QRRQ00,QSSJ00=P_QRSJ00,JSRQ00=P_QRRQ00,JSSJ00=P_QRSJ00 where ZXMXID=V_ZXMXID; end if; end loop; else V_YZID00:=P_YZID00; if P_CZLX00='0' then select SEQ_BQ_YZZXMX_ZXMXID.NEXTVAL into V_ZXMXID from dual; insert into BQ_YZZXMX (ZXMXID, YZID00, DQZT00, CZR000, SHR000, SHRXM0,SHRQ00, SHSJ00, CZRXM0, ZYID00, YPBSM0, YXBZ00) select V_ZXMXID,a.YZID00,'06',P_ZXR000,P_ZXR000,V_ZWXM00,P_QRRQ00,P_QRSJ00,V_ZWXM00,a.ZYID00,a.YPBSM0,'1' --from VW_SS_YZCFMX_YZZXQK a where YZID00=V_YZID00 and a.DQBQ00=P_BQH000 and a.ZYID00=P_ZYID00 and a.ZXRQ00=P_ZXRQ00 and a.ZXSJ00=P_ZXSJ00; from VW_SS_YZCFMX_YZZXQK a where a.ID0000=P_ID0000 group by a.YZID00,A.ZYID00,a.ZXRQ00,a.ZXSJ00,a.YPBSM0; insert into BQ_ZXGCJL (ZXGCID, ZXMXID, SD0000, ZXR000, ZXRXM0, ZXRQ00, ZXSJ00, ZXZT00, YXBZ00) select SEQ_BQ_ZXGCJL_ZXGCID.NEXTVAL,V_ZXMXID,'',P_ZXR000,V_ZWXM00,P_QRRQ00,P_QRSJ00,'06','1' --from VW_SS_YZCFMX_YZZXQK a where yzid00=V_YZID00 and a.DQBQ00=P_BQH000 and a.ZYID00=P_ZYID00 and a.ZXRQ00=P_ZXRQ00 and a.ZXSJ00=P_ZXSJ00; from dual; update BQ_YZCFMX a set ZXBZ00='01',ZXMXID=V_ZXMXID,SFBQZX='1',SJZXRQ=P_QRRQ00,SJZXSJ=P_QRSJ00 --where a.yzid00=V_YZID00 and a.ZYID00=P_ZYID00 and NVL(A.YZXRQ0,a.ZXRQ00)=P_ZXRQ00 and NVL(A.YZXSJ0,a.ZXSJ00)=P_ZXSJ00; where a.ID0000=P_ID0000; else select SEQ_BQ_YZZXMX_ZXMXID.NEXTVAL into V_ZXMXID from dual; update BQ_ZXGCJL a set a.YXBZ00='0' where 1=1 and exists( select 1 from BQ_YZCFMX b where a.ZXMXID=b.ZXMXID and b.ID0000=P_ID0000); update BQ_YZZXMX a set a.YXBZ00='0' where a.YZID00=V_YZID00 and a.ZYID00=P_ZYID00 and exists( select 1 from BQ_YZCFMX b where a.ZXMXID=b.ZXMXID and b.ID0000=P_ID0000); insert into BQ_YZZXMX (ZXMXID, YZID00, DQZT00, CZR000, SHR000, SHRXM0,SHRQ00, SHSJ00, CZRXM0, ZYID00, YPBSM0, YXBZ00) select V_ZXMXID,a.YZID00,'06',P_ZXR000,P_ZXR000,V_ZWXM00,P_QRRQ00,P_QRSJ00,V_ZWXM00,a.ZYID00,a.YPBSM0,'1' --from VW_SS_YZCFMX_YZZXQK a where yzid00=V_YZID00 and a.DQBQ00=P_BQH000 and a.ZYID00=P_ZYID00 and a.ZXRQ00=P_ZXRQ00 and a.ZXSJ00=P_ZXSJ00; from VW_SS_YZCFMX_YZZXQK a where a.ID0000=P_ID0000 group by a.YZID00,A.ZYID00,a.ZXRQ00,a.ZXSJ00,a.YPBSM0; insert into BQ_ZXGCJL (ZXGCID, ZXMXID, SD0000, ZXR000, ZXRXM0, ZXRQ00, ZXSJ00, ZXZT00, YXBZ00) select SEQ_BQ_ZXGCJL_ZXGCID.NEXTVAL,V_ZXMXID,'',P_ZXR000,V_ZWXM00,P_QRRQ00,P_QRSJ00,'06','1' --from VW_SS_YZCFMX_YZZXQK a where YZID00=V_YZID00 and a.DQBQ00=P_BQH000 and a.ZYID00=P_ZYID00 and a.ZXRQ00=P_ZXRQ00 and a.ZXSJ00=P_ZXSJ00; from dual; update BQ_YZCFMX a set ZXBZ00='01',ZXMXID=V_ZXMXID,SFBQZX='1',SJZXRQ=P_QRRQ00,SJZXSJ=P_QRSJ00 --where a.YZID00=V_YZID00 and a.ZYID00=P_ZYID00 and NVL(A.YZXRQ0,a.ZXRQ00)=P_ZXRQ00 and NVL(A.YZXSJ0,a.ZXSJ00)=P_ZXSJ00; where a.ID0000=P_ID0000; end if; if V_COUNT1=0 then update BQ_YZZXMX set QSRQ00=P_QRRQ00,QSSJ00=P_QRSJ00,JSRQ00=P_QRRQ00,JSSJ00=P_QRSJ00 where ZXMXID=V_ZXMXID; end if; end if; */ V_YZID00:=P_YZID00; V_COUNT1:=0; select count(1) into V_COUNT1 from BQ_YZZXMX a where a.YZID00=V_YZID00 and a.ZYID00=P_ZYID00 and exists( select 1 from BQ_YZCFMX b where a.ZXMXID=b.ZXMXID and b.ID0000=P_ID0000); if V_COUNT1>0 then update BQ_YZZXMX a set a.YXBZ00='0' where a.YZID00=V_YZID00 and a.ZYID00=P_ZYID00 and exists( select 1 from BQ_YZCFMX b where a.ZXMXID=b.ZXMXID and b.ID0000=P_ID0000); end if; V_COUNT1:=0; --select count(1) into V_COUNT1 from BQ_ZXGCJL a where exists( select 1 from BQ_YZCFMX b where a.ZXMXID=b.ZXMXID and b.ID0000=P_ID0000); select count(1) into V_COUNT1 from BQ_ZXGCJL a where ZXMXID in( select ZXMXID from BQ_YZCFMX b where b.ID0000=P_ID0000); if V_COUNT1>0 then update BQ_ZXGCJL a set a.YXBZ00='0' where 1=1 and ZXMXID in( select ZXMXID from BQ_YZCFMX b where b.ID0000=P_ID0000); end if; select SEQ_BQ_YZZXMX_ZXMXID.NEXTVAL into V_ZXMXID from dual; if P_CZLX00='0' then LS_QSRQ00:=SF_XT_GETXMLNOTE('QSRQ00',P_INXML0); LS_QSSJ00:=SF_XT_GETXMLNOTE('QSSJ00',P_INXML0); insert into BQ_YZZXMX (ZXMXID, YZMXID, YZID00, DQZT00, CZR000, SHR000, SHRXM0,SHRQ00, SHSJ00, CZRXM0, ZYID00, YPBSM0, YXBZ00, QSRQ00,QSSJ00) select V_ZXMXID,a.YZMXID,a.YZID00,'01',P_ZXR000,P_ZXR000,V_ZWXM00,P_QRRQ00,P_QRSJ00,V_ZWXM00,a.ZYID00,a.YPBSM0,'1', LS_QSRQ00,LS_QSSJ00 from VW_SS_YZCFMX_YZZXQK a where a.ID0000=P_ID0000 group by a.YZID00,a.YZMXID,A.ZYID00,a.ZXRQ00,a.ZXSJ00,a.YPBSM0; insert into BQ_ZXGCJL (ZXGCID, ZXMXID, SD0000, ZXR000, ZXRXM0, ZXRQ00, ZXSJ00, ZXZT00, YXBZ00) select SEQ_BQ_ZXGCJL_ZXGCID.NEXTVAL,V_ZXMXID,'',P_ZXR000,V_ZWXM00,LS_QSRQ00,LS_QSSJ00,'01','1' from dual; update BQ_YZCFMX a set ZXBZ00='02',ZXMXID=V_ZXMXID,SFBQZX='1',SJZXRQ=LS_QSRQ00,SJZXSJ=LS_QSSJ00 where a.ID0000=P_ID0000; else LS_QSRQ00:=SF_XT_GETXMLNOTE('QSRQ00',P_INXML0); LS_QSSJ00:=SF_XT_GETXMLNOTE('QSSJ00',P_INXML0); LS_JSRQ00:=SF_XT_GETXMLNOTE('JSRQ00',P_INXML0); LS_JSSJ00:=SF_XT_GETXMLNOTE('JSSJ00',P_INXML0); insert into BQ_YZZXMX (ZXMXID, YZMXID, YZID00, DQZT00, CZR000, SHR000, SHRXM0,SHRQ00, SHSJ00, CZRXM0, ZYID00, YPBSM0, YXBZ00, QSRQ00,QSSJ00,JSRQ00,JSSJ00) select V_ZXMXID,a.YZMXID,a.YZID00,'06',P_ZXR000,P_ZXR000,V_ZWXM00,P_QRRQ00,P_QRSJ00,V_ZWXM00,a.ZYID00,a.YPBSM0,'1', LS_QSRQ00,LS_QSSJ00,LS_JSRQ00,LS_JSSJ00 from VW_SS_YZCFMX_YZZXQK a where a.ID0000=P_ID0000 group by a.YZID00,a.YZMXID,A.ZYID00,a.ZXRQ00,a.ZXSJ00,a.YPBSM0; insert into BQ_ZXGCJL (ZXGCID, ZXMXID, SD0000, ZXR000, ZXRXM0, ZXRQ00, ZXSJ00, ZXZT00, YXBZ00) select SEQ_BQ_ZXGCJL_ZXGCID.NEXTVAL,V_ZXMXID,'',P_ZXR000,V_ZWXM00,LS_JSRQ00,LS_JSSJ00,'06','1' from dual; update BQ_YZCFMX a set ZXBZ00='01',ZXMXID=V_ZXMXID,SFBQZX='1',SJZXRQ=LS_JSRQ00,SJZXSJ=LS_JSSJ00 where a.ID0000=P_ID0000; end if; P_ZXMXID:=V_ZXMXID; P_ZXCGBZ:=1; exception when others then RAISE_APPLICATION_ERROR(-20005,substrb('医嘱执行确认时发生意外错误！*'|| 'SP_SS_YZZXQR('||P_CZLX00||','||P_ZYID00||','||P_BQH000||','||P_YZID00||','||P_YZID00||')'||sqlerrm,1,240)); end;