prompt 处方队列表插入触发器 TR_SF_SF_YF_CFDL00_INSERT
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);
--异常声明
-- 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
------------------------------------------------------------------------------------------
begin
  -- ZHR         2008.02.24  当FYCKBH=0，退出本触发器
  if :new.fyckbh=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 JZRQ00=to_char(sysdate,'YYYYMMDD') and CFZT00 IN ('1','2'));                
              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;
                 
                 begin      
                   SP_YF_CFDL00_FYDLXH(v_fydlxh,v_dlqsh0,v_value0);
                 exception WHEN OTHERS THEN
                   v_value0:=-1;
                 end;
                 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;
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%




/*
PROMPT 1.创建作废或删除处方表即删除处方队列 TR_YF_MZCF00_CFZT00_DEL 
CREATE OR REPLACE TRIGGER TR_YF_MZCF00_CFZT00_DEL
 AFTER 
 DELETE OR UPDATE
 ON SD_MZ.YF_MZCF00
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW 
Begin
  if (UPDATing and (:NEW.CFZT00=2 or :NEW.CFZT00=3)) OR DELETing then
    update YF_CFDL00 set ZT0000='3' WHERE CFLSH0=:OLD.CFLSH0;
  END IF;  
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20131, '删除处方队列出错!*返回信息:'||SQLERRM);
End;
/
*/


create or  replace trigger TR_BM_CKBM00_LIMIT
 before
  update
 on BM_CKBM00
 referencing old as old new as new 
 for each row 
declare
  VS_PROCESS XT_XTRZ00.JCID00%Type;
  VS_XTDM00 XT_XTRZ00.XTDM00%type;
  YF_CKJRZTXG XT_XTCS00.VALUE0 %type;
  Ecustom   EXCEPTION;
-- MODIFICATION HISTORY
-- Person      Date           Comments
-- csf         2011.02.11     create :YF_CKJRZTXG参数为Y时候,非配药系统登陆或退出时候,配药或发药窗口状态不做变化。
-- huangjy     2018.06.12     窗口暂停或停止后退出本次分配轮次，故DLLS00置1 for  ZYPY-20180611-001 
Begin
    
    select nvl(trim(max(VALUE0)),'N') into YF_CKJRZTXG from XT_XTCS00 where trim(NAME00)='YF_CKJRZTXG';
    if (YF_CKJRZTXG='Y') and ( :new.FLAG00 in ('2','3') ) then
	    select userenv('SESSIONID') PROCESS into VS_PROCESS from dual where rownum=1;
	    select nvl(trim(max(XTDM00)),'0') into VS_XTDM00 from XT_XTRZ00 where JCID00=VS_PROCESS and
	         LSH000=(select max(LSH000) from XT_XTRZ00 where JCID00=VS_PROCESS); 
      
      IF VS_XTDM00<>'R' THEN
        :new.ZT0000:=:OLD.ZT0000;
      END IF;
    end if; 
    if (:new.ZT0000 in ('1','2'))and(:old.DLLS00<>1) then
      :new.DLLS00:=1;
    end if;
exception
  when  Ecustom then
    raise_application_error(-20010,substr('YF_CKJRZTXG参数为Y时候,药房系统进出窗口状态不做变化！*',1,220));
end;
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

prompt 数据保护待配药数量    
create or replace trigger TR_SF_PYRYB0_UPDATE_MZPY
 before  update of DPYSL0
 on SF_PYRYB0
referencing new as new old as old
 for each row
declare
  ls_CZNR00  XT_XTCZRZ.CZNR00%type;
  lv_YGBH00  XT_XTRZ00.YGBH00%type;
  ls_PROCESS XT_XTRZ00.JCID00%type;
  ls_error0 varchar2(100);
  E_CUSTOM  exception;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- dsm       2017.10.10   create for  YFPY-20170718-002   
begin	 
  if updating and :new.DPYSL0<0 then
    :new.DPYSL0:=0;    
  end if;
exception
  when others then
    raise_application_error(-20004,'数据没有找到！*'||sqlerrm);
    rollback;
end;
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
 

prompt 网络诊室插入中间表触发 TR_YF_CFDL01_UPD_FZBMPT  
create or replace trigger TR_YF_CFDL01_UPD_FZBMPT
 before
 update of ZT0000 on SD_YF.YF_CFDL01
 referencing old as old new as new 
 for each row
declare
  Vcounter number(5);
  Ecustom        exception;
  LS_SQL_PTXS    varchar2(100);
  LS_COUNT0      number(5);
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- huangjy     2019.08.13  YF_CFDL01配药后网络诊室的病人放中间表  YFPY-20190812-001
begin
  if updating and :new.ZT0000='2' and :old.ZT0000='1' then
   -- begin
   --    execute immediate 'begin SP_FZBMPT_PYDLXX('||:new.BRID00||','||:new.DLXH00||',''0'','||:new.YFBMBH||','||:new.FYCKBH||'); end;';      
   -- exception
   --    when others then
   --   	Vcounter:=0;
   -- end;      
    begin
      LS_SQL_PTXS:='begin SP_WLZS_JSSJTB(0,1,:v1); end;';
      execute immediate LS_SQL_PTXS using in :new.CFLSH0;  
    exception
      when others then
        Vcounter:=0;		
    end;    			     	  
  end if;
exception
  when Ecustom then
    raise_application_error(-20266,substrb(nvl(SQLERRM, '原因不明出错')||'！*',1,120));
  when others        then
    raise_application_error(-20266,substrb(nvl(SQLERRM, '原因不明出错')||'！*',1,120));
end TR_YF_CFDL01_UPD_FZBMPT;
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%