CREATE OR REPLACE PROCEDURE SP_ZY_JKB000_CANCEL
(
        ad_JKDH00     in   number,            --结账单号
        ad_CZY000     in   number,            --操作员
        as_YHMSG0     out   varchar           --存储过程提示的错误信息
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments 
-- liuj     2013.05.24  增加写入日志表bm_jkqxrz for ZYSF-20130514-001
--zhangyc   2013.07.11  取消交款BM_JKB000.MZBZ00='9'('9'在门诊已经使用)-->'7'  bm_jksj00.jkbz00='9'-->'7'  by ZYSF-20130711-001
  Vcounter  number(5); 
  Vczyxm0    bm_ygbm00.zwxm00%TYPE;
  Ecustom   exception;
BEGIN
  --住院取消缴款
  select count(*) into Vcounter from bm_jkqr00 where jkdh00=ad_JKDH00;
  if Vcounter>0 then
     as_YHMSG0:='缴款已经确认，不能取消缴款!';
     raise Ecustom;
  end if;
  --住院取消缴款
  select count(*) into Vcounter from bm_JKB000 where jkdh00=ad_JKDH00 and QRBZ00='1';
  if Vcounter>0 then
     as_YHMSG0:='缴款已经确认，不能取消缴款!';
     raise Ecustom;
  end if;
  select count(*) into Vcounter from bm_jkb000 where jkdh00=ad_JKDH00 and jkdh00=(
              select max(jkdh00) from bm_jkb000 where sfy000=ad_CZY000 and mzbz00='1' and jkrq00>to_char(sysdate-720,'YYYYMMDD'));
--本次修改，原来语句为注释部分为
/*
select count(*) into Vcounter from bm_jkb000 where jkdh00=ad_JKDH00 and jkdh00=(
              select max(jkdh00) from bm_jkb000 where sfy000=ad_CZY000 and mzbz00='1';
*/  
if Vcounter=0 then
     as_YHMSG0:='只能取消最后一次的缴款记录!';
     raise Ecustom;
  end if;
  update zy_brjfb0 set jkbid0=0 where jkbid0=ad_JKDH00;
  update zy_pjsyqk set jkdid0=0 where jkdid0=ad_JKDH00;
  update zy_pjsyqk set hsjkid=0 where hsjkid=ad_JKDH00; 
  select zwxm00 into Vczyxm0 from bm_ygbm00 where ygbh00 = ad_CZY000;
  insert into BM_JKQXRZ(JKDH00,MZZYBZ,JKQRRY,QRRYXM,JKQRRQ,JKQRSJ,JKQXRY,QXRYXM,JKQXRQ,JKQXSJ) 
       select a.JKDH00,1,QRRY00,b.zwxm00,QRRQ00,QRSJ00,ad_CZY000,vczyxm0,
           to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS') 
         from bm_jkb000 a,bm_ygbm00 b where jkdh00=ad_jkdh00 and a.qrry00=b.ygbh00(+) and rownum=1;
   
  update bm_jkb000 set mzbz00='7',qrry00=ad_CZY000,qrrq00=to_char(sysdate,'yyyymmdd'),
        qrsj00=to_char(sysdate,'hh24:mi:ss') where jkdh00=ad_JKDH00;
  update bm_jksj00 set jkbz00='7'  where jkdh00=ad_JKDH00;
EXCEPTION
  when Ecustom then
       raise_application_error(-20001,as_YHMSG0);
       ROLLBACK;
END;
/