create or replace trigger TR_YJ_BBZJB0_UPDATE
before update of STATE0 on YJ_BBZJB0
referencing new as new old as old
for each row
 WHEN (new.BRLB00 in ('1','3')) declare
  Vsysdate    date;                                --日期变量
  Vcounter    number(14,4);                        --计数器变
  Ecustom     exception;
  Vyw0000     YJ_YW0000%rowtype;
  VHSXMnum    number;   --核酸项目
  Vzxcgbz     number;
  Vtsxx00     varchar2(250);
  Vxxxx00     varchar2(250);
  Vbrid00     YJ_YW0000.brid00%type;
  VZXR000     YJ_YW0000.zxr000%type;
  Vyjksbh     YJ_YW0000.yjksbh%type;
-- MODIFICATION HISTORY
-- Person       Date        Comments
-- cyhong       2019.04.25  Create:标本修改状态时操作  BQHS9.0-20190424-002
-- qiulf        2021.08.02  增加病人做完核酸后短信通知功能	by YJ9-20210730-001
-- ---------    ----------  -------------------------------------------
begin
	--	YJ9-20210730-001当病人做完核酸后短信通知功能
	
	if (:new.STATE0 <>:old.STATE0) and (:new.STATE0 = 'reported') then
		begin
		  select count(1) into  VHSXMnum from YJ_YW0000 a where a.YJDJH0=to_number(:new.YJDJH0) and ZLXMID in 
			(select trim(MC0000) from BM_TYZD00 where ZDMC00='短信通知的核酸项目');
				exception
			 when others then
			   VHSXMnum := 0;
	  end;
		if VHSXMnum>0  then
			select ZXR000,yjksbh,brid00 into  VZXR000,Vyjksbh,Vbrid00 from YJ_YW0000 a where a.YJDJH0=to_number(:new.YJDJH0);
			SP_YJ_DXTZ00
			(
			  Vbrid00,                  --Pbrid00 in number ,               --BRID
			  '核酸检测后入院通知',         --Pdxlbmc in varchar2,              --短信类型名称
			  '',                           --Pczlbmc in varchar2,              --操作类别名称
			  VZXR000,       --Pczy000 in number ,               --操作员
			  Vyjksbh,       --Pczyks0 in number ,               --操作员科室
			  'N',                          --Pcommit in varchar2 default 'N',  --是否提交
			  Vzxcgbz,                      -- out number,               --执行成功标志 1成功 0失败
			  Vtsxx00,                      -- out varchar2,             --执行结果提示信息，成功的情况下也可能有警告信息
			  Vxxxx00,                      --out varchar2,             --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标
			  0                             --Pdjh000 in number default 0       --单据号
			);
		end if;
	end if;
	
  if (:new.STATE0='inspecting') and (:old.STATE0<>'received') then  -- or :new.REQUISITION_STATE='reported'
    if :old.STATE0 in ('preinspect','printed') then
      raise_application_error(-20002,'标本未采集确认，请退回病区！*');
    end if;
    if :old.STATE0 in ('canceled','deleted') then
      raise_application_error(-20002,'标本已删除，请退回病区！*');
    end if;
    if :old.STATE0 in ('returned') then
      raise_application_error(-20002,'标本已退回！*');
    end if;
  end if;

  if :old.STATE0 in ('canceled','deleted','returned') and :new.STATE0 = 'received' then
    raise_application_error(-20002,'标本已退回，不能再接收，请将标本退回病区！*');
  end if;

  if (:new.STATE0='received') and (:old.STATE0='samplinged') then
    select * into Vyw0000 from YJ_YW0000 where YJDJH0=to_number(:new.YJDJH0);
    if Vyw0000.XMZT00 = '3' then
      return;
    end if;
    SP_YJ_YWZX00(
      to_number(:new.YJDJH0),    --, --单据号
      0,    --, --子项目ID
      1,    --, --执行数量
      2,    --子项目标志,1:执行子项目,2:大项目
      '9999',    --, --执行人胸卡号
      Vyw0000.YJKSBH,    --执行科室
      'N',    --, --提交标志
      0,    --, --标识是否输血系统执行过程
      null,    --, --执行单据号
      null,    -- , --执行日期
      '0'     --           --错误提示 0报错提示 1不提示直接退出
    );
  end if;

  if (:new.STATE0='returned') and (:old.STATE0='received') then
    select * into Vyw0000 from YJ_YW0000 where YJDJH0=to_number(:new.YJDJH0);
    if Vyw0000.XMZT00 <> '3' then
      return;
    end if;
  end if;
exception
  when others then
    raise_application_error(-20020, substrb(NVL(SQLERRM, '原因不明出错')||'！*',1,220));
end;