prompt 申请单发送数据的产生TR_YJ_YW0000_JCPT
create or replace trigger TR_YJ_YW0000_JCPT
 after  insert or delete or update
 on yj_yw0000
referencing new as new old AS old
 for each row
declare  
  V_YJDJH0  YJ_YW0000.YJDJH0%type; 
  V_CZBZ00  YJ_DFSSQD.CZBZ00%type;
  V_LBBH00  BM_ZLZD00.LBBH00%type;
  V_FSCJ00  YJ_DFSSQD.FSCJ00%type;
  V_JSXT00  YJ_DFSSQD.JSXT00%type;
  V_ZXKS00  YJ_YW0000.YJKSBH%type;
  V_XMZT00  varchar2(2);
  V_JCLBID  BM_ZLZD00.JCLBID%type;
  V_COUNT   number;
  V_COUNT1  number;
  V_ZLXMID  YJ_YW0000.ZLXMID%type;
  E_CUSTOM  exception;
  V_ERROR   varchar2(100);
  LS_JCPTCJ varchar2(10);
  V_ZLXMJC  YJ_YW0000.ZLXMJC%type;
  V_XDSQXM  number(1);             --是否属于心电申请项目
  V_DJ0000  BM_TYZD00.DJ0000%type; --心电项目值为-1情况下,改为开单就发送
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- dsm         2016.02.15  create;  
-- daihq       2016.02.19  增加退费的处理,YJ_DFSSQD.XMZT00=8表示退费 
-- dsm         2016.05.06  YJ_YW0000.CZBZ00='2'时才作删除申请 for MZYS-20160506-001
-- dsm         2017.01.22  修正 
-- qks         2017.03.01  移到HIS9.0版本下使用：:old.BWBH00 改用空值,注释掉 if nvl(:old.CZBZ00,'0')='2' then等内容
-- qks         2017.03.23  福清市医院要求已收费的PACS检查申请单要发送集成平台。
-- qks         2017.03.26  修改写入表YJ_DELYW0条件。
-- qks         2017.04.06  福清市医院要求住院未收费的检验项目，也要发送平台。
-- qks         2018.10.16  福清市医院以及福建省人民：新增心电接口。for GGJK-20181016-002 
-- liwm        2019.04.19  心电发送修改 
-- qks         2019.09.25  省人民心电发送接口改为新开单(未收费)就发送：BM_TYZD00.ZDMC00='集成平台心电申请包含内容'，约定条件为DJ0000=-1;
BEGIN
  if (inserting or updating) then
    V_ZXKS00:=:new.YJKSBH;
    if :new.YJLRBZ='T' or substr(:new.ZYHGHH,1,2)='TJ' then return;
    end if;
    V_ZLXMID:=:new.ZLXMID;
    V_ZLXMJC:=:new.ZLXMJC; 
  else
     V_ZXKS00:=:old.YJKSBH;
     if :old.YJLRBZ='T' or substr(:old.ZYHGHH,1,2)='TJ'  then return;
     end if;
     V_ZLXMID:=:old.ZLXMID;
     V_ZLXMJC:=:old.ZLXMJC; 
  end if; 
  select count(*) into V_COUNT from XT_XTCS00 where NAME00='YJ_ESBFWDZKS' and instr('|'||trim(VALUE0)||'|','|'||V_ZXKS00||'|')>0;
  --2018.10.16 判断是否属于心电项目
  --
  select count(1),nvl(min(DJ0000),0) into V_XDSQXM,V_DJ0000 from BM_TYZD00 where ZDMC00='集成平台心电申请包含内容' and V_ZLXMJC like '%'||MC0000||'%';   
  if V_COUNT=0 and V_XDSQXM=0 then 
    return;
  end if; 
  begin
    select trim(VALUE0) into LS_JCPTCJ from XT_XTCS00 where name00='YJ_JCPTCJ';
  exception
    when others then
     LS_JCPTCJ:='1'; 
  end;     

  if deleting  then
    -- :old.BWBH00 --> null
    select count(1) into V_COUNT from YJ_YFSSQD where YJDJH0=:old.YJDJH0;
    if V_COUNT > 0 then
  	  insert into YJ_DELYW0(YJDJH0,MZZYBZ,BRID00,ZYGHID,BRXM00,XB0000,CSRQ00,ZLXMID,ZLXMJC,YJKSBH,KDKSBH,KDYS00,KDBQ00,KDRQ00,KDSJ00,SL0000,BBID00,BWMC00,BWBH00,TXM000,XMZT00,LRXM00,KDYSXM,YSSZKS)
  	  values(:old.YJDJH0,:old.MZZYBZ,:old.BRID00,:old.ZYGHID,:old.BRXM00,:old.XB0000,:old.CSRQ00,:old.ZLXMID,:old.ZLXMJC,:old.YJKSBH,:old.KDKSBH,:old.KDYS00,:old.KDBQ00,:old.KDRQ00,
  	  :old.KDSJ00,:old.SL0000,:old.BBID00,:old.BWMC00,null,:old.TXM000,:old.XMZT00,:old.LRXM00,:old.KDYSXM,:old.YSSZKS); 
  	  delete 	YJ_YFSSQD where YJDJH0=:old.YJDJH0 and FSCJ00 in ('Test_Form_Send','Examine_Form_Send'); 
  	else
  		delete YJ_DFSSQD where YJDJH0=:old.YJDJH0 and FSCJ00 in ('Test_Form_Send','Examine_Form_Send');   
  	end if;
    --删除后面就取不到了
    return;
  end if; 

  if (inserting or updating) and nvl(:new.ZLXMID,0)=0 then
    return ;
  end if; 

  if (inserting or updating) then
    select LBBH00,nvl(JCLBID,0) into V_LBBH00,V_JCLBID from BM_ZLZD00 where ZLXMID=:new.ZLXMID;    
    if (nvl(:new.SL0000,0)<0) and (nvl(:new.SFDJH0,0)>0) then
      V_YJDJH0:=:new.CXDJH0;
      V_XMZT00:='8';
    elsif (nvl(:new.SL0000,0)<0) and (nvl(:new.SFDJH0,0)=0) then
      V_YJDJH0:=:new.CXDJH0;
      V_XMZT00:='10';  --冲销申请    
    else
      V_YJDJH0:=:new.YJDJH0;
      V_XMZT00:=:new.XMZT00;
    end if;
    V_ZXKS00:=:new.YJKSBH;
  else
    select LBBH00,nvl(JCLBID,0) into V_LBBH00,V_JCLBID from BM_ZLZD00 where ZLXMID=:OLD.ZLXMID;
     V_YJDJH0:=:old.YJDJH0;
     V_XMZT00:=:old.XMZT00;
     
     V_ZXKS00:=:old.YJKSBH;
     if nvl(:new.SL0000,0)<0 then
      	V_XMZT00:='11';  --撤销冲销申请
        V_YJDJH0:=:old.CXDJH0;
     end if;	
  end if;  

  if updating then
    select count(*) into V_COUNT  from YJ_DFSSQD where YJDJH0=:new.YJDJH0 and FSBZ00 in('0','2');	 
    select count(*) into V_COUNT1  from YJ_YFSSQD where YJDJH0=:new.YJDJH0;
    if V_COUNT>0  and V_XMZT00 not in ('8','9','10','11') then 
    	return;
    end if;
    if :new.XMZT00='4' then  --LIS,PACS执行更新状态时,不触发到这个表
    	return;
    end if;      
    if nvl(:new.BBZT00,'0')<>'0' then  --LIS,PACS执行更新状态时,不触发到这个表
    	 return ;
    end if;
    if nvl(:new.JKZT00,'0')<>'0' then  --LIS,PACS执行更新状态时,不触发到这个表
    	 return ;
    end if;    
    if nvl(V_XMZT00,'1')='1'   and (V_COUNT>0 or V_COUNT1>0)  then
    	return;
    end if;    
    --检查跟心电是已收费才写入(所以存在就退出)。检验收费时,需要再改状态。
    if (V_LBBH00=3 or V_XDSQXM=1) and :new.SL0000>0 or V_LBBH00=4 and :new.SL0000>0 and :new.XMZT00 in ('0','1') then 
    	 select count(*) into V_COUNT  from YJ_DFSSQD where YJDJH0=:new.YJDJH0;
    	 select count(*) into V_COUNT1  from YJ_YFSSQD where YJDJH0=:new.YJDJH0 ;
    	 if V_COUNT>0 or V_COUNT1>0 then
    	 	  return;
    	 end if;
    end if; 
    /*   --合并到上面
    if V_LBBH00=4 and :new.SL0000>0 and :new.XMZT00 in ('0','1') then 
    	 select count(*) into V_COUNT  from YJ_DFSSQD where YJDJH0=:new.YJDJH0;
    	 select count(*) into V_COUNT1  from YJ_YFSSQD where YJDJH0=:new.YJDJH0 ;
    	 if V_COUNT>0 or V_COUNT1>0 then
    	 	  return;
    	 end if;
    end if;
    */       
  end if;     
  --if (V_LBBH00=3 or V_XDSQXM=1 and V_DJ0000>-1) and nvl(:new.SFDJH0,0)=0 then  --检查未收费的不发
  if (V_LBBH00=3 or V_XDSQXM=1) and V_DJ0000>-1  and nvl(:new.SFDJH0,0)=0 then  --检查未收费的不发
  	return;
  end if;
  
  --2019.04.18心电自助机扣费的不发送
  --2019.08.02 qks 暂时注释掉
--  if (V_XDSQXM=1 and :new.SL0000 > 0 and :new.XMZT00 <> '3') or (:new.ZJE000 = 0) then
--    return;
--  end if;
  
  --2017.04.06 福清市医院要求住院未收费的检验项目，也要发送平台
  --if V_LBBH00=4 and nvl(:new.SFDJH0,0)=0 and :new.MZZYBZ='1' then  --检验住院未收费的不发
  --	return;
  --end if; 	
  if (inserting or ((V_LBBH00=3 or V_XDSQXM=1) and V_DJ0000>-1  and updating and :new.XMZT00 in ('2','3')) 
                or (:new.MZZYBZ='1' and V_LBBH00=4 and updating and :new.XMZT00 in ('2','3'))
                or (V_XDSQXM=1 and V_DJ0000=-1 and updating and :new.XMZT00 in ('2','3'))
      ) and :new.SL0000>0  then
    V_CZBZ00:='0';    
    select  count(*) into V_COUNT  from YJ_DFSSQD where YJDJH0=:new.YJDJH0 and FSCJ00 in('Test_Form_Send','Examine_Form_Send')  ;
    select  count(*) into V_COUNT1  from YJ_YFSSQD where YJDJH0=:new.YJDJH0 and FSCJ00 in('Test_Form_Send','Examine_Form_Send') ;
    if V_COUNT>0 or V_COUNT1>0 then --send已经发过的不发
    	return;
    end if;
    delete YJ_DFSSQD where YJDJH0=:new.YJDJH0 and XMZT00='9';
    delete YJ_DELYW0 where YJDJH0=:new.YJDJH0;
    if V_LBBH00=4 then
      V_FSCJ00:='Test_Form_Send';      
      V_JSXT00:='LIS';
    elsif V_XDSQXM=1 then
      V_FSCJ00:='Examine_Form_Send';      
      V_JSXT00:='MUSE';            
    elsif V_LBBH00=3 then
    	V_FSCJ00:='Examine_Form_Send';
      V_JSXT00:='PACS'; 
      --2017.03.23 福清市医院要求已收费的PACS检查申请单要发送集成平台     
      --if :new.XMZT00='2' and :new.SL0000>0 then
      --	 return;
      --end if;
    end if;        	    
  else  
    V_CZBZ00:='1'; 
    if V_LBBH00=4 then
      V_FSCJ00:='Test_Form_Update';
      V_JSXT00:='LIS';
    elsif V_XDSQXM=1 then
      V_FSCJ00:='Examine_Form_Send';      
      V_JSXT00:='MUSE';      
    elsif V_LBBH00=3 then
      V_FSCJ00:='Examine_Form_Send';
      V_JSXT00:='PACS';
    end if;  
  end if;  
  if LS_JCPTCJ='2' and  V_XMZT00 in ('8','9','10','11') then
    V_FSCJ00:=replace(V_FSCJ00,'Send','Cancel');
  end if; 
  if V_LBBH00<>4 and V_JCLBID=0 and V_XDSQXM=0 then
    return;
  end if;
  
  if V_XMZT00 = '10' or V_XMZT00 = '11' then
    return;
  end if;  
  
  select count(1) into V_COUNT from YJ_DFSSQD where yjdjh0=V_YJDJH0;
  if V_COUNT =0 then
    insert into YJ_DFSSQD(ID0000,YJDJH0,XMZT00,FSBZ00,FSCJ00,JSXT00,JSMK00,CZBZ00)
      values(SQ_YJ_DFSSQD_ID0000.nextval,V_YJDJH0,V_XMZT00,'0',V_FSCJ00,V_JSXT00,'',V_CZBZ00);
  end if;    
   --V_XMZT00=9表示删除,V_XMZT00=8表示退费,CZBZ00 
exception
    when E_CUSTOM then
    	raise_application_error(-20020, NVL(V_ERROR, '原因不明出错！*')); 
    when others then
      raise_application_error(-20020, NVL(SQLERRM, '原因不明出错！*'));
end;
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%