-- Start of DDL Script for Trigger SD_HOSPITAL.TR_YJ_YYBRB0_UPDATE
-- Generated 22-四月-2016 16:52:34 from SD_HOSPITAL@FJLNYY

CREATE OR REPLACE TRIGGER tr_yj_yybrb0_update
 BEFORE
  INSERT OR UPDATE
 ON yj_yybrb0
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
declare
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- dsm      2014.03.11     create for BQ-20140224-002
-- dsm      2014.04.11     add YYID00  for BQ-20140327-001   
-- dsm      2016.04.06     增加写消息记录 for BQ-20160330-004
  VS_PROCESS XT_XTRZ00.JCID00%Type;
  VS_YGBH00  XT_XTRZ00.YGBH00%Type;
  ls_CZYXM0  XT_XTTSXX.CZYXM0%type;
  ls_KDBQ00  XT_XTTSXX.JSKSBH%type;
  ls_DQBQMC  XT_XTTSXX.JSKSMC%type;  
  ls_COU     number;
  ls_IP      XT_XTRZ00.BZ0000%type;
  ls_XZIP00  YJ_YYDYJL.IP0000%type;  
  ls_XXNR00  XT_XTTSXX.XXNR00%type;  
begin
    select userenv('SESSIONID') PROCESS Into VS_PROCESS from dual where rownum=1;
    select nvl(max(YGBH00),0) Into VS_YGBH00 From XT_XTRZ00 Where JCID00=VS_PROCESS And
           LSH000=(select max(LSH000) from XT_XTRZ00 where JCID00=VS_PROCESS);
    select nvl(max(ZWXM00),'') into ls_CZYXM0 from BM_YGBM00 where YGBH00=VS_YGBH00;
    if :new.YJDJH0>0 then
      select decode(MZZYBZ,'1',KDBQ00,'0',KDKSBH)
             ,trim(CH0000)||'床 的'||BRXM00||'的['||ZLXMJC||']项目'||' 已经预约:'||:new.YJKSMC||' '||:new.YYRQ00||'日'||decode(sign(to_number(substr(:new.YYSJ00,1,2))-12),1,'下午','上午')||'的'||:new.JKYYH0||'号' into ls_KDBQ00,ls_XXNR00
              from YJ_YW0000 where YJDJH0=:new.YJDJH0;
      select BMMC00 into ls_DQBQMC from BM_BMBM00 where BMBH00=ls_KDBQ00;
    
      Select nvl(max(BZ0000),0) Into ls_IP From XT_XTRZ00 Where JCID00=VS_PROCESS And
         LSH000=(select max(LSH000) from XT_XTRZ00 where JCID00=VS_PROCESS);        
  
      ls_XZIP00:=substrb(ls_IP,1,instrb(ls_IP,'#')-1); 
      if   instr(ls_XZIP00,'-')>0 then  
        ls_XZIP00:=substrb(ls_XZIP00,1,instrb(ls_XZIP00,'-')-1);   
      end if;
        
      if  nvl(:new.DYCS00,0)<>nvl(:old.DYCS00,0) and nvl(:new.DYCS00,0)>0 then
         insert into YJ_YYDYJL(YJDJH0,YYID00, DYCS00, DYRBH0, DYRQ00, DYSJ00,IP0000)
         values(:new.YJDJH0,:new.YYID00,:new.DYCS00,VS_YGBH00,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),ls_XZIP00)   ;
      end if;  
      select count(*) into ls_COU from XT_XTCS00 where NAME00='BQ_JCYYAPXXTS' and VALUE0='Y';  
      if (inserting or (updating and :new.YYH000<>:old.YYH000)) and ls_COU>0  then            
        insert into XT_XTTSXX(XTDM00,XXBH00,XXNR00,YXQ000,CZYXM0,CZRQ00,XH0000,JSKSBH,JSKSMC,XXXZ00)
        values('9',SQ_XT_XTTSXX_XXBH00.nextval,ls_XXNR00,:new.YYRQ00,ls_CZYXM0,to_char(sysdate,'YYYYMMDD'),SQ_XT_XTTSXX_XH0000.nextval,ls_KDBQ00,ls_DQBQMC,'1')   ;   
      end if ;          
    end if;   
end;
/


-- End of DDL Script for Trigger SD_HOSPITAL.TR_YJ_YYBRB0_UPDATE