create or replace trigger TR_BQ_YPYZ00_BFUPD before update of YZZT00 on SD_BQ.BQ_YPYZ00 referencing old as old new as new for each ROW when (old.YZZT00 = '0' and new.YZZT00 = '1') declare v_yznr01 varchar(200);--医嘱内容1 v_dqbq00 ZY_BRXXB0.DQBQ00%type;--病人病区 v_yfbmbh XT_YPFWFX.YFBMBH%type;--药房部门编号 v_cflsh0 YF_ZYCF00.CFLSH0%type;--处方流水号 v_cfzje0 YF_ZYCF00.CFZJE0%type;--处方总金额 v_sfksly YF_ZYCF00.SFKSLY%type;--是否科室领药 v_yzcllb BQ_YPYZMX.YZCLLB%type;--医嘱处理类别 v_cfsrbz YF_ZYCF00.CFSRBZ%type;--处方输入标志 v_Count number;--记数器 v_Count2 number;--记数器2 e_SQLERRM varchar(500); V_YPNM00 BM_YD0000.YPNM00%TYPE; V_LBBH00 BM_YD0000.LBBH00%TYPE; E_BQCF_YPKCBZ EXCEPTION;--病区处方药品不足 v_CYDYBZ varchar2(10); V_SFZDJZ varchar2(10); --自动记帐出院带药的处方 v_djh000 ZY_BRFY00.DJH000%type; BQ_CYDYYZSCZYLSCFFS varchar2(10);--出院带药医嘱生成住院零散处方方式 0在医嘱核对的触发器中生成 1医嘱提取过程生成 默认 为0 LS_YZLRSFXSYF number(5); begin -- MODIFICATION HISTORY -- Person Date Comments -- YANGY 2008.04.13 出院带药直接记帐 -- YANGY 2008.04.22 出院带药自动改药房 -- zhr 2009.10.21 生成医嘱内容此触发器不再处理，由过程SP_BQ_YPFJFY负责 -- yangh 2009.12.08 中药和成药的出院带药，核对的时候不合并到同个处方 -- dsm 2011.12.31 相关业务表增加YEXM00数据的插入. for YF-20111226-002 -- dsm 2013.01.17 出院带药处方自动记帐. for BQ-20130109-001 -- csf 2013.03.21 由于南平市一的医嘱是医生提交医嘱时候直接核对的，导致病人到药房可能余额不足而导致又要到收费处， ----------------------------如果核对时候在本触发器直接记账的话会导致每条医嘱一个处方单，所以本触发器不处理 出院带药、应急领药 改成在病区提取环节来生成处方单并记账 ----------------------------新增参数BQ_CYDYYZSCZYLSCFFS来控制 BQ-20130321-001。 -- csf 2013.11.01 带出的药品有浓缩比例、浓缩编号，次数量及总数量应除以浓缩比例 BQ-20130415-001 -- dsm 2014.05.05 YF_ZYCF00判断是否用已经存在的处方时加YSZID0的条件 for BQ-20140505-002 --生成医嘱内容 --select decode(:new.YEXM00,null,'','('||:new.YEXM00||')')||decode(:new.yplbbh,'2',to_char(:new.ZQCYTS)||'贴','')||Trim(:new.PCMC00)||' '||Trim(:new.YPYFMC)||' ' -- ||Decode(:new.CLBZ00,'0','',Decode(:new.ZQS000,1,'',To_Char(:new.ZQS000)||Trim(:new.ZQDW00))) -- into v_yznr01 from dual; --调用医嘱内容生成储存过程 --SP_BQ_YZNRSC(:new.YZID00,v_yznr01); begin select trim(value0) into v_CYDYBZ from xt_xtcs00 where name00='BQ_CYDYYPMCBZ'; exception when others then v_CYDYBZ := '◆'; end; begin --出院带药处方自动记帐 select trim(value0) into V_SFZDJZ from XT_XTCS00 where name00='BQ_CYDYCFJZ'; exception when others then V_SFZDJZ:='N'; end; select nvl(max(trim(value0)),'0') into BQ_CYDYYZSCZYLSCFFS from XT_XTCS00 where NAME00='BQ_CYDYYZSCZYLSCFFS'; --判断是否出院带药或应急领药 select Count(1) into v_Count from BQ_YPYZMX where YZID00 = :new.YZID00 and ( (BQ_CYDYYZSCZYLSCFFS='0' and YZCLLB in ('出院带药','应急领药')) or (BQ_CYDYYZSCZYLSCFFS='1' and YZCLLB in ('应急领药')) ); if (v_Count = 0) then return; end if; --取出病人病区 select DQBQ00 into v_dqbq00 from ZY_BRXXB0 where ZYID00 = :new.ZYID00; --取出病区默认药房部门编号 /*begin select YFBMBH into v_yfbmbh from XT_YPFWFX where SLBMBH = v_dqbq00 and YPLBBH = :new.YPLBBH and MRBZ00 = 'Y' and RowNum = 1; exception when no_data_found then v_dqbq00 := 0; end;*/ select count(*) into LS_YZLRSFXSYF from XT_XTCS00 where NAME00='ZS_YZLRSFXSYF' and VALUE0='Y'; --取出病区默认药房部门编号 if LS_YZLRSFXSYF=0 or nvl(:new.YFBMBH,0)=0 then begin if v_Count=0 then begin select YFBMBH into v_yfbmbh from XT_YPFWFX where SLBMBH = v_dqbq00 and YPLBBH = :new.YPLBBH and MRBZ00 = 'Y' and RowNum = 1; exception when no_data_found then v_dqbq00 := 0; end; else --如果有全成分的药品的话不能取默认药房 begin select YFBMBH into v_yfbmbh from XT_YPFWFX a where SLBMBH = v_dqbq00 and YPLBBH = :new.YPLBBH --and MRBZ00 = 'Y' and exists(select 1 from YF_YPKCXX b,BQ_YPYZMX c where c.YZID00=:NEW.YZID00 and b.YPNM00=c.YPNM00 and b.YFBMBH=a.YFBMBH and YKKCSL>0 ) and RowNum = 1; :new.YFBMBH:=v_yfbmbh; exception when no_data_found then v_dqbq00 := 0; end; end if; end; else v_yfbmbh:=:new.YFBMBH; end if; SELECT A.YPNM00,B.LBBH00 INTO V_YPNM00,V_LBBH00 FROM BQ_YPYZMX A, BM_YD0000 B WHERE A.YPNM00=B.YPNM00 AND A.YZID00=:NEW.YZID00 AND ROWNUM=1; select nvl(max(Z.YFBMBH),v_yfbmbh) into v_yfbmbh from XT_YPFWFX Z,YF_YPKCXX X WHERE Z.SLBMBH=v_dqbq00 AND Z.YPLBBH=V_LBBH00 AND Z.MRBZ00='N' AND Z.YFBMBH=X.YFBMBH AND X.YPNM00=V_YPNM00 AND X.BQKDYF='Y' AND ROWNUM=1; /* UPDATE BQ_ZBTQYZ T SET YFBMBH=(SELECT Z.YFBMBH FROM XT_YPFWFX Z,YF_YPKCXX X WHERE Z.SLBMBH=PBQH000 AND Z.YPLBBH=T.YPLBBH AND Z.MRBZ00='N' AND Z.YFBMBH=X.YFBMBH AND T.YPNM00=X.YPNM00 AND X.BQKDYF='Y'AND ROWNUM=1) WHERE ID0000=PBCTQID AND SJBZ00='1' --AND YPLBBH='2'-- AND EXISTS (SELECT 1 from BM_YD0000 where T.YPNM00=YPNM00 AND LBBH00='2' AND jkbh00 is not null and nsbl00<>1) AND EXISTS (SELECT 1 FROM YF_YPKCXX B,XT_YPFWFX C WHERE T.YPNM00=B.YPNM00 AND C.SLBMBH=PBQH000 AND B.YFBMBH=C.YFBMBH AND B.BQKDYF='Y' AND C.MRBZ00='N' AND C.YPLBBH=T.YPLBBH); */ --判断药房药品库存是否小于领药量 select Count(1) into v_Count from BQ_YPYZMX a,BM_YD0000 b,YF_YPKCXX c where a.YZID00 = :new.YZID00 --and b.LBBH00 = :new.YPLBBH and b.YPNM00 = a.YPNM00 and c.YPNM00 = a.YPNM00 and c.YPNM00 = b.YPNM00 and c.YFBMBH = v_yfbmbh and ( (c.YKKCSL/b.Z2J000 >= a.LYL000 and b.NSBL00=1) or (c.YKKCSL/b.Z2J000 >= round(a.LYL000/b.NSBL00,2) and b.NSBL00<>1)); -- select Count(1) into v_Count2 from BQ_YPYZMX where YZID00 = :new.YZID00; -- if v_Count <> v_Count2 then --提示药房药品库存是否小于领药量的药品 --定义游标 declare v_ypmc00 BQ_YPYZMX.YPMC00%type := null;--药品名称 v_yfbmmc BM_BMBM00.BMMC00%type := null;--药房部门名称 --游标定义 Cursor c_yfkc_ypmc00 is select YPMC00 from BQ_YPYZMX a where a.YZID00 = :new.YZID00 and not Exists ( select 1 from BQ_YPYZMX a,BM_YD0000 b,YF_YPKCXX c where a.YZID00 = :new.YZID00 --and b.LBBH00 = :new.YPLBBH and b.YPNM00 = a.YPNM00 and c.YPNM00 = a.YPNM00 and c.YPNM00 = b.YPNM00 and c.YFBMBH = v_yfbmbh -- and c.YKKCSL/b.Z2J000 >= a.LYL000); and ( (c.YKKCSL/b.Z2J000 >= a.LYL000 and b.NSBL00=1) or (c.YKKCSL/b.Z2J000 >= round(a.LYL000/b.NSBL00,2) and b.NSBL00<>1))); --用游标处理 begin OPEN c_yfkc_ypmc00; LOOP FETCH c_yfkc_ypmc00 INTO v_ypmc00; EXIT WHEN c_yfkc_ypmc00%NOTFOUND; e_SQLERRM := e_SQLERRM||trim(v_ypmc00); END LOOP; --取药房部门名称 select BMMC00 into v_yfbmmc from BM_BMBM00 where BMBH00 = v_yfbmbh; e_SQLERRM := v_yfbmmc||':'||e_SQLERRM||'库存不足'; end; Raise E_BQCF_YPKCBZ; end if; --取出院带药或应急领药 select YZCLLB into v_yzcllb from BQ_YPYZMX where YZID00 = :new.YZID00 and YZCLLB in ('出院带药','应急领药') and Rownum = 1; --处方输入标志 if v_yzcllb = '出院带药' then v_cfsrbz := '5'; elsif v_yzcllb = '应急领药' then v_cfsrbz := '7'; end if; --判断是否有毒麻药品 select Count(1) into v_Count from BQ_YPYZMX where YZID00 = :new.YZID00 and DMBZ00 = '1'; -- if v_Count > 0 then --调用生成处方流水号函数SF_YK_GETLSH v_cflsh0 := SF_YK_GETLSH('ZYYF_ZYCFH'); begin if v_yzcllb = '出院带药' then select Trim(VALUE0) into v_sfksly from XT_XTCS00 where NAME00 = 'BQ_CYDYSFKSLY' and RowNum = 1; elsif v_yzcllb = '应急领药' then select Trim(VALUE0) into v_sfksly from XT_XTCS00 where NAME00 = 'BQ_YJYZSFKSLY' and RowNum = 1; end if; exception when no_data_found then v_sfksly := 'N'; end; --调用住院处方主表存储过程SP_BQ_ZYCF00 --操作员取核对人 SP_BQ_ZYCF00(0,v_cflsh0,v_yfbmbh,:new.ZYID00,:new.KZYS00,:new.YPLBBH,'1',:new.ZQCYTS, :new.JYCS00,:new.BZID00,:new.KZYS00,v_cfzje0,:new.BZ0000,'0',v_sfksly,v_cfsrbz); if nvl(:new.YEXM00,' ')<>' ' then update YF_ZYCF00 set YEXM00=:new.YEXM00 where CFLSH0=v_cflsh0; end if; --住院处方明细 insert into YF_ZYCFMX( CFID00,--处方ID号 SQ_YF_ZYCFMX_CFID00 CFLSH0,--处方流水号 YPNM00,--药品内码 对应BM_YD0000表 YPMC00,--药品名称 YPGG00,--规格 LSDJ00,--零售单价(处方单位) YPYFMC,--药品用法名称 BM_YPYF00 PCID00,--频次ID PCMC00,--频次名称 CSL000,--次数量(剂量单位) JLDW00,--剂量单位(J) YYSJ00,--用药时间数 YYSJDW,--时间单位(天或小时) ZQCD00,--周期长度 YFSMMC,--用法说明名称 对应BM_YPYFSM表 XMYFMC,--中药细目用法名称 BM_YPYF00_YPYFMC YPZSL0,--药品总数量(处方单位) CFDW00,--处方单位 ZHL000,--转换率(处方单位/剂量单位) PSLBID,--皮试类别ID 对应MZ_PS0000表 ZFBL00,--医保自付比率 YBLB00,--医保类别 '4'甲类'5'乙类'6'民族用药'7'非医保 BZ0000,--备注 YZID00,--医嘱ID YZMXID --医嘱明细ID ) select SQ_YF_ZYCFMX_CFID00.NextVal,-- v_cflsh0, --处方流水号 a.YPNM00, --药品内码 v_CYDYBZ||a.YPMC00, --药品名称 a.YPGG00, --药品规格 Round((b.LSJ000/b.K2J000)*b.Z2J000,4), --零售单价 :new.YPYFMC, --药品用法名称 :new.PCID00, --频次ID :new.PCMC00, --频次名称 decode(b.NSBL00,1,a.MCSL00,round(a.MCSL00/b.NSBL00,4)), --次数量 a.JLDW00, --剂量单位 :new.ZQS000, --用药时间数 :new.ZQDW00, --单位 :new.ZQCD00, --周期长度 :new.YYZF00, --嘱咐 a.XMYFMC, --细目用法名称 decode(b.NSBL00,1,a.LYL000,ROUND(a.LYL000/b.NSBL00,2)), --药品总数量 a.ZYFYDW, --处方单位 b.Z2J000, --转换率 a.PSID00, --皮试ID a.ZFBL00, --自付比例 a.YBLB00, --医保类别 a.BZ0000, --备注 :new.YZID00, --医嘱ID a.YZMXID --医嘱明细ID from BQ_YPYZMX a,VW_BQ_YD0000 b where YZID00 = :new.YZID00 and b.YPNM00 = a.YPNM00 and b.yfbmbh=v_yfbmbh and a.DMBZ00 = '1'; --and YZCLLB = v_yzcllb; --计算处方总金额 select Round(Sum(LSDJ00*YPZSL0),2) into v_cfzje0 from YF_ZYCFMX where CFLSH0 = v_cflsh0; --修改住院处方总金额 update YF_ZYCF00 set CFZJE0 = v_cfzje0,BZ0000=v_yzcllb where CFLSH0 = v_cflsh0; if v_yzcllb='出院带药' and V_SFZDJZ='Y' then select SQ_ZY_BRJFB0_DJH000.NEXTVAL into v_djh000 FROM DUAL WHERE ROWNUM=1; SP_YF_ZYCFJZ(v_cflsh0,to_char(sysdate,'YYYYMMDD'),To_Char(SYSDATE,'HH24:MI:SS'),:NEW.KZYS00,v_djh000); UPDATE YF_ZYCF00 SET CFZT00='1',DJH000=v_djh000, JZRQ00=to_char(sysdate,'YYYYMMDD'),JZSJ00=To_Char(SYSDATE,'HH24:MI:SS') WHERE CFLSH0=v_cflsh0 AND CFZT00='0'; --insert into dsmtest(BZ,ID) values('a',v_djh000); end if; end if; --判断是否有非毒麻药品 select Count(1) into v_Count from BQ_YPYZMX where YZID00 = :new.YZID00 and DMBZ00 = '0'; if v_Count > 0 then --根据ZYID00=:new.ZYID00且药品类别YPDLBH = :new.YPLBBH and YPDLBH in (0,1) --且处方输入标志CFSRBZ = v_cfsrbz且未记账处方CFZT00 = 0且非毒处方CFXZ00 = 0 --且药房YFBMBH = :new.YFBMBH且不是冲销处方条件 --在YF_ZYCF00表判断存在记录 select Count(1) into v_Count from YF_ZYCF00 where ZYID00 = :new.ZYID00 --and YPDLBH = :new.YPLBBH and ((YPDLBH in ('0','1') and :new.YPLBBH in ('0','1')) or (YPDLBH = '2' and :new.YPLBBH = '2')) and CFSRBZ = v_cfsrbz --出院带药或应急领药 and CFZT00 = '0' --未记账 and CFXZ00 = '0' --非毒麻处方 and YFBMBH = v_yfbmbh --相同药房 and BCCFH0 is null--不是冲销处方 and nvl(YEXM00,' ')=nvl(:new.YEXM00,' ') and YSGZH0=:new.KZYS00 and nvl(YSZID0,0)=nvl(:new.YSZID0,0) ; if v_Count = 0 then --调用生成处方流水号函数SF_YK_GETLSH v_cflsh0 := SF_YK_GETLSH('ZYYF_ZYCFH'); begin if v_yzcllb = '出院带药' then select Trim(VALUE0) into v_sfksly from XT_XTCS00 where NAME00 = 'BQ_CYDYSFKSLY' and RowNum = 1; elsif v_yzcllb = '应急领药' then select Trim(VALUE0) into v_sfksly from XT_XTCS00 where NAME00 = 'BQ_YJYZSFKSLY' and RowNum = 1; end if; exception when no_data_found then v_sfksly := 'N'; end; --调用住院处方主表存储过程SP_BQ_ZYCF00 --操作员取核对人 SP_BQ_ZYCF00(0,v_cflsh0,v_yfbmbh,:new.ZYID00,:new.KZYS00,:new.YPLBBH,'0',:new.ZQCYTS, :new.JYCS00,:new.BZID00,:new.KZYS00,v_cfzje0,:new.BZ0000,'0',v_sfksly,v_cfsrbz); if nvl(:new.YEXM00,' ')<>' ' then update YF_ZYCF00 set YEXM00=:new.YEXM00 where CFLSH0=v_cflsh0; end if; else if :new.YPLBBH = '2' then select CFLSH0 into v_cflsh0 from YF_ZYCF00 where ZYID00 = :new.ZYID00 --and YPDLBH = :new.YPLBBH and YPDLBH = '2' and CFSRBZ = v_cfsrbz --出院带药 and CFZT00 = '0' --未记账 and CFXZ00 = '0' --非毒麻处方 and YFBMBH = v_yfbmbh --相同药房 and BCCFH0 is null--不是冲销处方 and nvl(YEXM00,' ')=nvl(:new.YEXM00,' ') and YSGZH0=:new.KZYS00 and nvl(YSZID0,0)=nvl(:new.YSZID0,0) and RowNum = 1; else select CFLSH0 into v_cflsh0 from YF_ZYCF00 where ZYID00 = :new.ZYID00 --and YPDLBH = :new.YPLBBH and YPDLBH in ('0','1') and CFSRBZ = v_cfsrbz --出院带药 and CFZT00 = '0' --未记账 and CFXZ00 = '0' --非毒麻处方 and YFBMBH = v_yfbmbh --相同药房 and BCCFH0 is null--不是冲销处方 and nvl(YEXM00,' ')=nvl(:new.YEXM00,' ') and YSGZH0=:new.KZYS00 and nvl(YSZID0,0)=nvl(:new.YSZID0,0) and RowNum = 1; end if; end if; --住院处方明细 insert into YF_ZYCFMX( CFID00,--处方ID号 SQ_YF_ZYCFMX_CFID00 CFLSH0,--处方流水号 YPNM00,--药品内码 对应BM_YD0000表 YPMC00,--药品名称 YPGG00,--规格 LSDJ00,--零售单价(处方单位) YPYFMC,--药品用法名称 BM_YPYF00 PCID00,--频次ID PCMC00,--频次名称 CSL000,--次数量(剂量单位) JLDW00,--剂量单位(J) YYSJ00,--用药时间数 YYSJDW,--时间单位(天或小时) ZQCD00,--周期长度 YFSMMC,--用法说明名称 对应BM_YPYFSM表 XMYFMC,--中药细目用法名称 BM_YPYF00_YPYFMC YPZSL0,--药品总数量(处方单位) CFDW00,--处方单位 ZHL000,--转换率(处方单位/剂量单位) PSLBID,--皮试类别ID 对应MZ_PS0000表 ZFBL00,--医保自付比率 YBLB00,--医保类别 '4'甲类'5'乙类'6'民族用药'7'非医保 BZ0000,--备注 YZID00,--医嘱ID YZMXID --医嘱明细ID ) select SQ_YF_ZYCFMX_CFID00.NextVal, -- v_cflsh0, --处方流水号 a.YPNM00, --药品内码 v_CYDYBZ||a.YPMC00, --药品名称 a.YPGG00, --药品规格 Round((b.LSJ000*b.Z2J000/b.K2J000),4), --零售单价 :new.YPYFMC, --药品用法名称 :new.PCID00, --频次ID :new.PCMC00, --频次名称 decode(b.NSBL00,1,a.MCSL00,round(a.MCSL00/b.NSBL00,4)), --次数量 a.JLDW00, --剂量单位 :new.ZQS000, --用药时间数 :new.ZQDW00, --单位 :new.ZQCD00, --周期长度 :new.YYZF00, --嘱咐 a.XMYFMC, --细目用法名称 decode(b.NSBL00,1,a.LYL000,round(a.LYL000/b.NSBL00,2)), --药品总数量 a.ZYFYDW, --处方单位 b.Z2J000, --转换率 a.PSID00, --皮试ID a.ZFBL00, --自付比例 a.YBLB00, --医保类别 a.BZ0000, --备注 :new.YZID00, --医嘱ID a.YZMXID --医嘱明细ID from BQ_YPYZMX a,VW_BQ_YD0000 b where YZID00 = :new.YZID00 and b.YPNM00 = a.YPNM00 and b.yfbmbh=v_yfbmbh and a.DMBZ00 = '0'; --and YZCLLB = v_yzcllb; --计算处方总金额 select Round(Sum(LSDJ00*YPZSL0),2) into v_cfzje0 from YF_ZYCFMX where CFLSH0 = v_cflsh0; --修改住院处方总金额 update YF_ZYCF00 set CFZJE0 = v_cfzje0,BZ0000=v_yzcllb where CFLSH0 = v_cflsh0; if v_yzcllb='出院带药' and V_SFZDJZ='Y' then --SP_YF_ZYCFCL(v_cflsh0,'1',0,:NEW.KZYS00,v_djh000); select SQ_ZY_BRJFB0_DJH000.NEXTVAL into v_djh000 FROM DUAL WHERE ROWNUM=1; SP_YF_ZYCFJZ(v_cflsh0,to_char(sysdate,'YYYYMMDD'),To_Char(SYSDATE,'HH24:MI:SS'),:NEW.KZYS00,v_djh000); UPDATE YF_ZYCF00 SET CFZT00='1',DJH000=v_djh000, JZRQ00=to_char(sysdate,'YYYYMMDD'),JZSJ00=To_Char(SYSDATE,'HH24:MI:SS') WHERE CFLSH0=v_cflsh0 AND CFZT00='0'; -- insert into dsmtest(BZ,ID) values('a',v_djh000); end if; end if; --该医嘱状态改为停止状态 -- SP_BQ_ZYCFSF(v_cflsh0,:new.kzys00,:new.ksh000,:new.kzys00,v_yzcllb); :new.YZZT00 := '3'; :new.TQRQ00 := To_Char(SYSDATE,'YYYYMMDD'); :new.TQSJ00 := To_Char(SYSDATE,'HH24:MI:SS'); :new.TZRQ00 := To_Char(SYSDATE,'YYYYMMDD'); :new.TZSJ00 := To_Char(SYSDATE,'HH24:MI:SS'); --停止医生，停止人姓名 :new.TZYS00 := :new.HDR000; :new.TZRXM0 := :new.HDRXM0; exception --预定义型异常情态 --病区处方药品不足 when E_BQCF_YPKCBZ then Raise_Application_Error(-20203,e_SQLERRM||'!*还回信息:'||SqlErrm); --没有找到数据 when no_data_found then Raise_Application_Error(-20202,'没有找到数据错误!*还回信息:'||SqlErrm); --OTHER异常处理器 when others then Raise_Application_Error(-20201,NVL(SQLERRM,'意外出错，原因不明')||'！*'); end TR_BQ_YPYZ00_BFUPD;