--长乐测试 参数：YS_CXFSFFS=Y 
CREATE PROCEDURE SP_YS_DELCXF
--过程说明,抽血费根据参数是否一天只收一次
(
   pghid00       in      number     ,         --挂号id
   pmzzybz       in      varchar2             --门诊住院标志
)
AS
   Vsfxm00       bm_yysfxm.sfxmid%type;      --抽血费
   Vsfxm01       bm_yysfxm.sfxmid%type;      --采血针
   VLastrq       yj_yw0000.aprq00%type;      --安排日期
   Vcounter      number(10);                 --计数器变量
   Vparams       varchar2(255);              --参数变量
   Verrmsg       varchar2(255);              --错误提示变量
   Ecustom	 exception;                  --错误变量
   VCXFSFFS      varchar2(10);
   Vsfxm02       varchar2(100);
   Vbrid00       yj_yw0000.brid00%type; 
   Vkdrq00       yj_yw0000.KDRQ00%type;    
   Vsfxmid       yj_ywjj00.sfxmid%type;    
    i number(3);        
   Cursor CYjfzh0 is --每组检验单的执行日期
   select * from (select decode(VCXFSFFS,'Y',a.YJDJH0,a.yjfzh0) yjfzh0,trim(max(aprq00)) aprq00 ,c.SFXMID
           from yj_yw0000 a,bm_zlzd00 b,YJ_YWJJ00 c
   where  a.zlxmid=b.zlxmid and b.lbbh00='4' 
          and ((zyghid=pghid00 and VCXFSFFS='N') or (brid00=vbrid00 and VCXFSFFS='Y' and KDRQ00=Vkdrq00))
          and a.mzzybz=pmzzybz 
          and xmzt00 in ('0','1') and a.sl0000>0 
          and a.yjfzh0=c.yjfzh0 and a.yjdjh0=c.yjdjh0 and  (c.sfxmid in (Vsfxm00,Vsfxm01) or instr(Vsfxm02,','||SFXMID||',')>0)
--          and  exists (select 1 from yj_ywjj00 c where a.yjfzh0=c.yjfzh0 and (c.sfxmid in (Vsfxm00,Vsfxm01) or instr(Vsfxm02,','||SFXMID||',')>0) )
   group  by decode(VCXFSFFS,'Y',a.YJDJH0,a.yjfzh0),c.SFxmid) order by SFXMID;
   --2007.08.22 zhr 参数检验每天只收一次费用名称改为用SFXMID来处理。因为收费项目会有同名，所以用名称字段无法精确匹配。
   --2008.12.19 CSF 省二10g的库检验开单不能保存 max函数处理后再用trim过滤空格
   --2014.12.14 dsm bm_zlzd00增加mzzybz报:未定义明确列  for XMGL-20141217-001  
   --2014.12.19 dsm 增加参数YS_CXFSFFS for MZYS-20141219-002
begin
  VLastrq:='';
    select nvl(to_number(max(trim(value0))),0) into Vsfxm00 from xt_xtcs00 where name00='XT_JYMTZSYCSFXMID';--检验每天只收一次费用名称
    select nvl(to_number(max(trim(value0))),0) into Vsfxm01 from xt_xtcs00 where name00='XT_JYMTZSYCSFXMID1';--检验每天只收一次费用名称
    select ','||nvl(max(trim(value0)),0)||',' into Vsfxm02 from xt_xtcs00 where name00='XT_JYMTZSYCSFXMID2';--检验每天只收一次其他费用名称 格式:SFXMID1,SFXMID2,SFXMID3
    
    select nvl(max(trim(Value0)),'N') into VCXFSFFS from XT_XTCS00 where name00='YS_CXFSFFS' and value0='Y';
    if Vsfxm00=0 and Vsfxm01=0 and  Vsfxm02=',,' then
       return;
    end if;
    Vcounter:=0;
    Vkdrq00:=to_char(sysdate,'YYYYMMDD');
    Vsfxmid:=0;
    i:=0;
    select nvl(max(BRID00),0) into Vbrid00 from YJ_YW0000 where zyghid=pghid00 and mzzybz=pmzzybz and rownum=1;
    for yj in Cyjfzh0 loop   
      if i=0 then 
         Vsfxmid:=yj.sfxmid;
      end if;         
      if Vcounter>0 and VCXFSFFS='Y' /*and pghid00<>15814943*/ then  
         delete from yj_ywjj00 where yjfzh0=yj.yjfzh0 and sfxmid=yj.sfxmid and (sfxmid in (Vsfxm00,Vsfxm01)  or instr(Vsfxm02,','||SFXMID||',')>0);
      elsif  yj.aprq00=Vlastrq and VCXFSFFS<>'Y'  /*and pghid00<>15814943*/ then  
          delete from yj_ywjj00 where yjfzh0=yj.yjfzh0 and sfxmid=yj.sfxmid and (sfxmid in (Vsfxm00,Vsfxm01)  or instr(Vsfxm02,','||SFXMID||',')>0);
      end if;
      VLastrq:=yj.aprq00;
      
      if Vsfxmid=yj.sfxmid then
        Vcounter:=Vcounter+1;
      else 
        Vcounter:=0;
      end if;  
      Vsfxmid:=yj.sfxmid ; 
      i:=i+1; 
      
  end Loop;
exception
  when no_data_found then
    raise_application_error(-20002,substrb('数据没有找到！*'||sqlerrm||','||Vparams,1,220));
  when Ecustom       then
    raise_application_error(-20010,substrb(Verrmsg||'！*'||Vparams,1,220));
  when others        then
    raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'!*'||Vparams,1,220));
end ;
/