CREATE OR REPLACE TRIGGER tr_xt_zlsfgx_all_czrz
 AFTER
  INSERT OR DELETE OR UPDATE
 ON xt_zlsfgx
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
DECLARE
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- qks         2017.09.14  收费项目无法停用问题;
  VS_PROCESS XT_XTRZ00.JCID00%Type;
  VS_YGBH00 XT_XTRZ00.YGBH00%Type;
  VS_CZNR00 XT_XTCZRZ.CZNR00%Type;
  VS_XMMC00 varchar2(300);
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);
  if inserting then  --插入
      VS_CZNR00:=VS_CZNR00||'序列号:'||to_char(:NEW.GXID00)||';';
      VS_CZNR00:=VS_CZNR00||'诊疗项目ID:'||to_char(:NEW.ZLXMID)||';';
      VS_CZNR00:=VS_CZNR00||'收费项目ID:'||to_char(:NEW.SFXMID)||';';
      Insert Into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID)
                     Values(SQ_XT_XTCZRZ_ID0000.NEXTVAL,'XT_ZLSFGX',0,VS_YGBH00,'(增加)'||VS_CZNR00,:NEW.GXID00);
  ELSIF deleting then  --删除
      VS_CZNR00:=VS_CZNR00||'序列号:'||to_char(:OLD.GXID00)||';';
      VS_CZNR00:=VS_CZNR00||'诊疗项目ID:'||to_char(:OLD.ZLXMID)||';';
      VS_CZNR00:=VS_CZNR00||'收费项目ID:'||to_char(:OLD.SFXMID)||';';
      VS_CZNR00:=VS_CZNR00||'收费次数:'||to_char(:OLD.SFCS00)||';';
      Insert Into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID)
                     Values(SQ_XT_XTCZRZ_ID0000.NEXTVAL,'XT_ZLSFGX',2,VS_YGBH00,'(删除)'||VS_CZNR00,:OLD.GXID00);
  ELSIF updating then  --修改
      Select nvl(max(trim(ZLXMJC)),'无') Into VS_XMMC00 From BM_ZLZD00 Where ZLXMID=:old.ZLXMID ;
      VS_CZNR00:='(修改:'||VS_XMMC00;
      VS_XMMC00 := '收费项目ID:'||to_char(:OLD.SFXMID);
      if (nvl(trim(:NEW.TYBZ00),'无') = nvl(trim(:OLD.TYBZ00),'无')) and
        (nvl(trim(:NEW.BZ0000),'无') = nvl(trim(:OLD.BZ0000),'无')) then  --收费项目停用引起的
        Select nvl(max(trim(XMMC00||decode(BZ0000,null,'','['||BZ0000||']'))),'无') Into VS_XMMC00 From BM_YYSFXM Where SFXMID=:old.SFXMID ;
      end if;
      VS_CZNR00:=VS_CZNR00||'-'||VS_XMMC00||')';
      
       if nvl(:old.SFXMID,0)<>nvl(:new.SFXMID,0) then
          Select nvl(max(trim(XMMC00||decode(BZ0000,null,'','['||BZ0000||']'))),'无') Into VS_XMMC00 From BM_YYSFXM Where SFXMID=:old.SFXMID ;
          VS_CZNR00 := VS_CZNR00||'收费项目:'||VS_XMMC00;
          Select nvl(max(trim(XMMC00||decode(BZ0000,null,'','['||BZ0000||']'))),'无') Into VS_XMMC00 From BM_YYSFXM Where SFXMID=:new.SFXMID ;
          VS_CZNR00 := VS_CZNR00||'->'||VS_XMMC00||';';
       end if;          
       If (nvl(:NEW.SFCS00,0) <> nvl(:OLD.SFCS00,0)) Then
          VS_CZNR00 := VS_CZNR00||'收费次数:'||nvl(:OLD.SFCS00,0)||'->'||nvl(:NEW.SFCS00,0)||';';
       End If;
       If (nvl(trim(:NEW.FJFYBZ),'无') <> nvl(trim(:OLD.FJFYBZ),'无')) Then
          VS_CZNR00 := VS_CZNR00||'附加费用标志:'||nvl(trim(:OLD.FJFYBZ),'无')||'->'||nvl(trim(:NEW.FJFYBZ),'无')||';';
       End If;
       If (nvl(trim(:NEW.BQSFBZ),'无') <> nvl(trim(:OLD.BQSFBZ),'无')) Then
          VS_CZNR00 := VS_CZNR00||'病区收入标志:'||nvl(trim(:OLD.BQSFBZ),'无')||'->'||nvl(trim(:NEW.BQSFBZ),'无')||';';
       End If;
       If (nvl(trim(:NEW.GDBZ00),'无') <> nvl(trim(:OLD.GDBZ00),'无')) Then
          VS_CZNR00 := VS_CZNR00||'固定收费项目标志:'||nvl(trim(:OLD.GDBZ00),'无')||'->'||nvl(trim(:NEW.GDBZ00),'无')||';';
       End If;
       If (nvl(trim(:NEW.MZZYBZ),'无') <> nvl(trim(:OLD.MZZYBZ),'无')) Then
          VS_CZNR00 := VS_CZNR00||'门诊住院标志:'||nvl(trim(:OLD.MZZYBZ),'无')||'->'||nvl(trim(:NEW.MZZYBZ),'无')||';';
       End If;
       If (nvl(trim(:NEW.MXXMID),0) <> nvl(trim(:OLD.MXXMID),0)) Then
         VS_CZNR00 := VS_CZNR00||'组合明细项目:'||nvl(trim(:OLD.MXXMID),0)||'->'||nvl(trim(:NEW.MXXMID),0)||';';
       End If;
       If (nvl(trim(:NEW.TYBZ00),'无') <> nvl(trim(:OLD.TYBZ00),'无')) Then
          VS_CZNR00 := VS_CZNR00||'停用标志:'||nvl(trim(:OLD.TYBZ00),'无')||'->'||nvl(trim(:NEW.TYBZ00),'无')||';';
       End If;
       If (nvl(trim(:NEW.SFLB00),'无') <> nvl(trim(:OLD.SFLB00),'无')) Then
          VS_CZNR00 := VS_CZNR00||'收费类别:'||nvl(trim(:OLD.SFLB00),'无')||'->'||nvl(trim(:NEW.SFLB00),'无')||';';
       End If;
       If (nvl(:NEW.YJE000,0)<> nvl(:OLD.YJE000,0)) Then
          VS_CZNR00 := VS_CZNR00||'原金额:'||TO_CHAR(nvl(:OLD.YJE000,0))||'->'||TO_CHAR(nvl(:NEW.YJE000,0))||';';
       End If;
       If (nvl(:NEW.TCJE00,0)<> nvl(:OLD.TCJE00,0)) Then
          VS_CZNR00 := VS_CZNR00||'套餐金额:'||TO_CHAR(nvl(:OLD.TCJE00,0))||'->'||TO_CHAR(nvl(:NEW.TCJE00,0))||';';
       End If;   
           Insert Into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID)
                          Values(SQ_XT_XTCZRZ_ID0000.NEXTVAL,'XT_ZLSFGX',1,VS_YGBH00,VS_CZNR00,:NEW.GXID00);
  end if;
END;
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%