create or replace trigger TR_SS_SSYJD0_DELETE
 before delete
 on SD_ZY.SS_SSYJD0
 referencing old as old new as new
 for each row
declare
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- chenxz      2022.03.14    for SSMZ9-20220211-001
  E_BNSC00    EXCEPTION;                --信息不能删除
  VS_PROCESS  XT_XTRZ00.JCID00%Type;
  VS_YGBH00   XT_XTRZ00.YGBH00%Type;
  VS_CZNR00   XT_XTCZRZ.CZNR00%Type;
  Vcounter  NUMBER;
  vMZZY00   CHAR(1);
  Vzje000   yj_yw0000.zje000%TYPE;
  Verrmsg   varchar2(100);
  vBMBH00   BM_BMBM00.bmbh00%type;
begin
    select count(1) into Vcounter from XT_YYXX00 where  YYID00 = '220006';
    select userenv('SESSIONID') PROCESS Into VS_PROCESS from dual where rownum=1;
    begin
      Select YGBH00 Into VS_YGBH00 From XT_XTRZ00 Where JCID00=VS_PROCESS And
             LSH000=(select max(LSH000) from XT_XTRZ00 where JCID00=VS_PROCESS);
    exception
      when others then
        VS_YGBH00:=0;
    end;
    --SF_SS_GETKSSSMZLB
    if Vcounter>0 then
    select decode(sum(ZJE000),NULL,0,sum(ZJE000))  into vzje000 from YJ_YW0000 a
      where (a.YJDJH0=:old.YJDJH0)  and a.XMZT00 in ('2','3','4') and a.YJKSBH in (select BMBH00 From VW_BM_MZBM00);
    if Vzje000 <> 0 then
     Verrmsg:='该病人本手术存在麻醉科已收费项目';
     raise e_bnsc00;
    end if;

    if vMZZY00 = '1' then
    select count(1) into Vcounter  from YF_YZYPSQ a,SS_SSCF00 b
      where b.SSDH00=:old.SSDH00 and a.TQDJH0=b.CFLSH0 and a.QLZT00 in('1','2','4')
      and a.DQKS00 in (select BMBH00 From VW_BM_MZBM00);
    elsif vMZZY00 = '0'  then
     select count(1) into Vcounter from YF_MZCF00 a,SS_SSCF00 b
       where b.SSDH00=:old.SSDH00 and a.CFLSH0=b.CFLSH0 and a.CFZT00 in('1','2')
       and a.GHKS00 in (select BMBH00 From vw_bm_mzbm00);
    end if;
    if (Vcounter>0) then
      if (length(Verrmsg)>0) then
        Verrmsg:=Verrmsg||','||'存在麻醉科已收费药品处方';
        raise e_bnsc00;
       end if;
      /*if (Verrmsg is null) then
        Verrmsg := '该病人本手术存在已收费药品处方';
        raise e_bnsc00;
      end if;*/
    end if;
    end if;
exception
    when e_bnsc00 then
         raise_application_error(-20915,Verrmsg||'不能删除,非法操作'||'！*');
         rollback;
end;