create or replace trigger TR_SF_SF_YF_CFDL00_INSERT before insert on YF_CFDL00 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW declare v_fydlxh Varchar2(50); v_value0 Number(5); v_dlqsh0 Number(5); v_tssx00 bm_brxxb0.TSSXMC%type; V_ZYDJS0 YF_MZCF00.ZYDJS0%type; V_DJZYCFBJRCFDL xt_xtcs00.value0%type; V_SFQYDJGLGN XT_XTCS00.value0%type; V_PDQHSFZJTXDYZT XT_XTCS00.value0%type; --参数值 MZPY_ABRSCFYDLXH XT_XTCS00.value0%type; --按病人生成处方序号 'Y'按病人生成fydlxh 'N'按处方单 默认为N MZPY_SFBFCKSCDLXH XT_XTCS00.value0%type; --配药队列序号是否不分窗口，一个药房一个队列序号 Y是 N 否 默认为N ls_fydlxh YF_CFDL00.FYDLXH%type; V_BRLBNLWH XT_XTCS00.value0%type; V_BRNL00 NUMBER(5); cnt number(5); --异常声明 -- MODIFICATION HISTORY -- Person Date Comments -- zhr 2007.11.3 新增药品大类编号字段YPDLBH，通过此触发器自动取值。 -- yangh 2007.11.16 对于高级干部 不取药参保人 写处方队列表时候不形成队列序号，且处方队列的状态为3 -- CSF 2008.01.07 不同窗口队列启始号不一致 -- ZHR 2008.02.24 当FYCKBH=0，退出本触发器 -- CSF 2009.03.07 省二中药待煎处方进入队列，但不占用队列序号 -- CSF 2010.06.08 参数MZPY_DJZYCFBJRCFDL为M，中药代煎处方的序号为0，现取消该种做法，按正常的发药队列序号处理 -- chenyw 2010.07.28 当启用中药带煎系统时，高级干部或不取药参保人的病人的代煎处方也要进入配药系统; -- zhangwz 2010.12.14 调用参数控制 排队取号时是否直接填写打印相关信息 by YFPY-20101213-001. -- CSF 2011.05.18 病人同一时间，同一窗口有若干张处方（没有发药的处方），fydlxh只用一个，加参数控制 -- CSF 2011.05.19 配药队列序号是否不分窗口，一个药房一个队列序号 按参数MZPY_SFBFCKSCDLXH控制 -- CSF 2011.06.23 病人的特殊属性，存在操作员乱输入的状况，所以对此进行有效性过滤。 -- laijg 2012.06.15 增加病人的特殊属性(VIP、本院职工) 进入排号队列 by YFPY-20120613-002 -- laijg 2013.05.02 V_BRNL00取法可能为空，分开取 by YFPY-20130502-001 -- huangjy 2019.01.05 有发药序号的增加处方排号日期MZSF-20190107-001 -- huangjy 2020.12.29 防止新序列FYDLXH重复 for YFPY-20201223-001 -- huangjy 2021.06.08 增加BD接口FYDLXH已经有值时，不自动生成 by YFPY-20210607-001 ------------------------------------------------------------------------------------------ begin -- ZHR 2008.02.24 当FYCKBH=0，退出本触发器 if :new.fyckbh=0 then return; end if; if nvl(:new.FYDLXH,0)>0 then return; end if; select YPDLBH,CFDGS0,NVL(ZYDJS0,0) into :new.YPDLBH,:new.CFDGS0,V_ZYDJS0 from yf_mzcf00 where cflsh0=:new.cflsh0; --取参数有待煎的中药处方是否不进入处方配药队列 begin select trim(Value0) into V_DJZYCFBJRCFDL from xt_xtcs00 where name00='MZPY_DJZYCFBJRCFDL'; exception when others then V_DJZYCFBJRCFDL:='N'; end; begin select trim(value0) into V_SFQYDJGLGN from XT_XTCS00 where NAME00='YF_SFQYDJGLGN'; exception when others then V_SFQYDJGLGN:='N'; end; begin -- by YFPY-20101213-001. select trim(value0) into V_PDQHSFZJTXDYZT from XT_XTCS00 where NAME00='MZPY_PDQHSFZJTXDYZT'; exception when others then V_PDQHSFZJTXDYZT:='N'; end; begin -- by YFPY-20120613-002 select trim(value0) into V_BRLBNLWH from XT_XTCS00 where NAME00='MZPY_BRLBNLWH'; exception when others then V_BRLBNLWH:='0'; end; select nvl(max(trim(VALUE0)),'N') into MZPY_ABRSCFYDLXH from XT_XTCS00 where NAME00='MZPY_ABRSCFYDLXH'; select nvl(max(trim(VALUE0)),'N') into MZPY_SFBFCKSCDLXH from XT_XTCS00 where NAME00='MZPY_SFBFCKSCDLXH'; SELECT SF_YS_CSRQTONL(nvl(BRCSRQ,to_char(sysdate,'yyyymmdd'))) INTO V_BRNL00 FROM BM_BRXXB0 a WHERE BRID00=:NEW.BRID00; SELECT TRIM(nvl(max(TSSXMC),'普通')) INTO v_tssx00 FROM BM_BRXXB0 a WHERE BRID00=:NEW.BRID00 and exists (select 1 FROM VW_BM_BRQTLB where QTLBMC=TSSXMC); IF ((v_tssx00 = '普通') or (v_tssx00 = 'VIP') or (v_tssx00 = '本院职工') or (:new.YPDLBH='2' and V_ZYDJS0>0 and v_tssx00 <> '普通' and V_SFQYDJGLGN='Y')) THEN /* Y省二中药待煎处方不进入处方配药队列 M进入但是序号为0 开始*/ -- IF ((V_DJZYCFBJRCFDL='M') and (:new.YPDLBH='2') AND (V_ZYDJS0>0) ) THEN -- :new.fydlxh:=0; -- ELSE /*取队列起始号*/ select a.dlqsh0 into v_dlqsh0 from bm_ckbm00 a where bmbh00=:new.yfbmbh and ckbh00=:new.fyckbh; if :NEW.ypje00 <0 then begin select nvl(fydlxh,0) INTO :new.fydlxh from YF_CFDL00 where cflsh0 in (select BCCFH0 from yf_mzcf00 where cflsh0=:new.cflsh0) ; exception when others then :new.fydlxh:=0; end; else ls_fydlxh:=0; if MZPY_ABRSCFYDLXH='Y' then select nvl(min(FYDLXH),0) into ls_fydlxh FROM YF_CFDL00 a where BRID00=:new.BRID00 and PYCKBH=:NEW.PYCKBH and FYCKBH=:NEW.FYCKBH and PHBZ00='0' and exists (select 1 from YF_MZCF00 where BRID00=a.BRID00 and JZRQ00=to_char(sysdate,'YYYYMMDD') and CFZT00 IN ('1','2')) AND PYRQ00 IS NULL; --临时修改 end if; if ls_fydlxh>0 then :new.fydlxh:=ls_fydlxh; else if MZPY_SFBFCKSCDLXH='Y' then v_fydlxh:='SEQ_YF_FYDLXH_'||to_char(:new.YFBMBH)||'_'||to_char(SYSDATE,'DD'); else v_fydlxh:='SEQ_YF_FYDLXH_'||to_char(:new.FYCKBH)||'_'||to_char(SYSDATE,'DD'); end if; cnt:=1; while cnt > 0 loop begin SP_YF_CFDL00_FYDLXH(v_fydlxh,v_dlqsh0,v_value0); exception WHEN OTHERS THEN v_value0:=-1; end; if v_value0>0 then if MZPY_SFBFCKSCDLXH='Y' then select count(1) into cnt from YF_CFDL00 where YFBMBH=:new.YFBMBH and FYDLXH=v_value0 ; else select count(1) into cnt from YF_CFDL00 where YFBMBH=:new.YFBMBH and FYCKBH=:new.FYCKBH and FYDLXH=v_value0 ; end if; end if; end loop; begin select v_value0 into :new.fydlxh from dual where rownum=1; exception WHEN OTHERS THEN :new.fydlxh:=0; end; end if; if :new.fydlxh>0 then --MZSF-20190107-001 :new.PHRQ00:=to_char(sysdate,'YYYYMMDD'); end if; end if; --end if; if v_tssx00 = 'VIP' and V_BRNL00>=to_number(V_BRLBNLWH) then :new.brxm00:=:new.brxm00||'(VL)'; elsif v_tssx00 = '本院职工' and V_BRNL00>=to_number(V_BRLBNLWH) then :new.brxm00:=:new.brxm00||'(BL)'; elsif v_tssx00 = 'VIP' then :new.brxm00:=:new.brxm00||'(V)'; elsif v_tssx00 = '本院职工' then :new.brxm00:=:new.brxm00||'(B)'; elsif V_BRNL00>=to_number(V_BRLBNLWH) then :new.brxm00:=:new.brxm00||'(L)'; end if; else :new.cflsh0 := substr('/'||TRIM(:new.cflsh0),1,16); :new.zt0000 := '3'; end if; if V_PDQHSFZJTXDYZT='Y' then :new.ZT0000:='1'; :new.DYRQ00:=to_char(sysdate,'YYYYMMDD'); :new.DYSJ00:=to_char(sysdate,'HH24:MI:SS'); end if; exception WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20906,substr(nvl(sqlerrm,'出错原因不详'),1,40)||'，请记录此信息并和系统管理员联系！'||'!*'); end;