CREATE OR REPLACE TRIGGER tr_bq_yzzxjl_lnforht
 BEFORE
   UPDATE OF zxrxm0
 ON bq_yzzxjl
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
Declare
    LS_ERROR0  varchar2(50);
    E_custom   exception;
    --ls_id      medicalinfo.RecordFlow%type; 
    ls_id      number(30);   
-- Person      Date            Comments
-- dsm         2014.12.12      create for ZYYS-20141125-002
begin
  if :new.ZXRXM0<>:old.ZXRXM0 then
    select nvl(max(to_number(RecordFlow)),0) into ls_id from medicalinfo where ORDER_SN=to_char(:new.YZMXID)  and ImpFlag='0'
    and FIRST_EXECUTE_ID is null  and CZRQ00=to_char(sysdate,'YYYYMMDD');
    if ls_id>0 then
      update medicalinfo a set FIRST_EXECUTE_ID=(select xkh000 from bm_ygbm00 where YGBH00=:new.CZR000),
                               FIRST_EXECUTE_NAME=:new.ZXRXM0 ,
                               FIRST_EXECUTE_TIME=nvl(FIRST_EXECUTE_TIME,:new.ZXRQ00||' '||:new.ZXSJ00)
                               where RecordFlow=ls_id;
         
    end if;  
  end if;
end;
/


CREATE OR REPLACE TRIGGER tr_bq_yjyz00_lnforht
 BEFORE
   UPDATE OF yzzt00, zxrqsc
 ON bq_yjyz00
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
Declare
    LS_ERROR0  varchar2(50);
    E_custom   exception;
   ls_cou     number(5);    
-- Person      Date            Comments
-- dsm         2014.12.12      create for ZYYS-20141125-002
begin
--JY-检验 JC-检查 HL-护理 SW-膳食（食物）SS-手术 CZ-处置 QT-其它
  if updating and :new.yzzt00<>'7' and (:new.yzzt00<>:old.YZZT00 or :new.ZXRQSC<>:old.ZXRQSC) then
     select count(*) into ls_cou from medicalinfo where IPID=to_char(:new.ZYID00) and PARENT_ORDER_SN=to_char(:new.YZMXID);
     insert into medicalinfo(
      RecordFlow,    ORDER_SN,    IPID,    PID,    CREATOR,    CREATOR_TIME,
      PARENT_ORDER_SN,    LONG_FLAG,    ORDER_TYPE,    ORDER_CLASS,    ORDER_CODE,
      ORDER_CONTENT,    DOCTOR_MEMO,    RELATION_KEY,    PRICE,    FREQUENCY_CODE,
      FREQUENCY_NAME,    ROUTE_CY_CODE,    ROUTE_CY_NAME,    DURATION,    DURATION_UNIT,
      ORDER_STATUS,    START_TIME,    STOP_TIME,    SUBMIT_TIME,    SUBMIT_ID,
      SUBMIT_NAME,    STOP_NAME,    STOP_ID,    AFFIRM_TIME,    AFFIRM_ID,    AFFIRM_NAME,
      FIRST_EXECUTE_TIME,    FIRST_EXECUTE_ID,    FIRST_EXECUTE_NAME,    AFFIRM_STOP_TIME,
      AFFIRM_STOP_ID,    AFFIRM_STOP_NAME,    DEPT_CODE,    DEPT_NAME,    WARD_CODE,
      WARD_NAME,    IS_SUB_ORDER,    DRUG_SPEC,    ROUTE_CODE,    ROUTE_NAME,
      DOSE,    DOSE_UNIT_CODE,    DOSE_UNIT,    TOTAL_UNIT,    TOTAL_UNIT_CODE,
      TOTAL,    MANAGE_FLAG,    ImpFlag,    ReturnDesc,CZRQ00,CZSJ00
      )
      select SQ_medicalinfo_RecordFlow.nextval,:new.YZMXID,:new.YZMXID,(select brid00 from zy_brxxb0 where zyid00=:new.zyid00),(select xkh000 from bm_ygbm00 where ygbh00=:new.kzys00),:new.lrrq00||' '||:new.lrsj00,
      :new.YZMXID,decode(:new.clbz00,'0','Y','N'),(select decode(LBBH00,'1','护理','3','检查','4','检验','5','CZ','6','SS','QT')  from BM_ZLZD00 where ZLXMID=:new.ZLXMID),decode(:new.YZCLLB,'出院带药','OUT','IN'),(select ZLXMBH from BM_ZLZD00 where ZLXMID=:new.ZLXMID),
      :new.ZLXMJC,'' YYZF00,:new.YZMXID,(select SFJE00 from VW_BQ_FBFYYZ where ZLXMID=:new.ZLXMID),:new.PCID00,
      :new.PCMC00,'','', :new.TS0000,'天',
      :new.YZZT00,:new.QYRQ00||' '||:new.QYSJ00,trim(:new.TZRQ00||' '||:new.TZSJ00),:new.LRRQ00||' '||:new.LRSJ00,(select xkh000 from bm_ygbm00 where ygbh00=:new.kzys00),
      (select ZWXM00 from BM_YGBM00 where YGBH00=:new.KZYS00), (select ZWXM00 from BM_YGBM00 where YGBH00=:new.TZYS00) STOP_NAME,(select XKH000 from BM_YGBM00 where YGBH00=:new.TZYS00),:new.QYRQ00||' '||:new.QYSJ00, (select xkh000 from bm_ygbm00 where ZWXM00=:new.HDRXM0 and rownum=1) AFFIRM_ID,:new.HDRXM0,
      trim(:new.ZXRQSC||' '||:new.ZXSJSC),(select c.XKH000 from BQ_YZZXJL b,BM_YGBM00 c where  YZMXID=:new.YZMXID and ZXRQ00=:new.ZXRQSC and rownum=1 and b.ZXRXM0=c.ZWXM00),(select ZXRXM0 from BQ_YZZXJL where  YZMXID=:new.YZMXID and ZXRQ00=:new.ZXRQSC and rownum=1), '' AFFIRM_STOP_TIME,
      (select xkh000 from bm_ygbm00 where ZWXM00=:new.TZRXM0 and rownum=1) AFFIRM_STOP_ID,    :new.TZRXM0,    :new.KSH000,(select BMMC00 from BM_BMBM00 where BMBH00=:new.KSH000),:new.BQH000,
      (select BMMC00 from BM_BMBM00 where BMBH00=:new.BQH000 ),'N',    '','',    '',
       :new.MCSL00,:new.DW0000,    :new.DW0000,    :new.DW0000,    :new.DW0000,
       0,decode(ls_cou,0,'0','1')  MANAGE_FLAG,    '0' ImpFlag,   ''  ReturnDesc,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS')
       from dual ;
  end if;

end;
/