create or replace procedure SP_YJ_CXCX00
(
-- MODifICATION HisTORY
-- Person      Date        Comments
-- lintj      2012.08.16   modify
-- zhangwz    2012.10.23   解决撤销冲销的时候医技子单先被独立撤销冲销而没有update相关YJ_YJDJGL.SCX000='0';
-- lintj      2013.07.01   优化处理
-- qks        2013.07.06   游标CUR_YJDJH0增加条件:医技单据关联表增加未更正
-- qks        2016.01.26   退费撤销速度慢问题 by YJ9-20160127-001
-- qks        2018.06.05   退费撤销需要考虑负单“非全部”退费申请; for SSMZ9-20180529-001
-- liwm       2018.06.05   统一添加调用日志 for YJ9-20180605-001
-- gzj        2019.05.28   住院病人诊疗带出处方流程改造 YJ9-20190518-002
-- linzetao   2020.09.04   修正撤消有子医技单的退费申请问题     YJ9-20200903-002
  Pyjdjh0    NUMBER,        --医技单据号(可传冲销或被冲销的单据号)
  Pczy000    NUMBER,        --员工编号
  Pczyks0    NUMBER,        --科室编号
  Pcommit    varchar2 default 'Y' --是否提交
)
as
  Verrmsg  VARCHAR2(100);
  Vyjdjh0  YJ_YW0000.YJDJH0%type;
  Vzldccf  YJ_YW0000.ZLDCCF%type;
  Vyw0000  YJ_YW0000%ROWTYPE;
  Ecustom  EXCEPTION;
  Vcounter NUMBER;
  Vcounter1 NUMBER;
  Vzxcgbz     number;
  Vtsxx00     varchar2(200);
  Vxxxlog     varchar2(2000);

  cursor CUR_YJDJH0 is
    select YJDJH0 from YJ_YW0000 where YJDJH0=Vyjdjh0
     union
    select ZYJDJH YJDJH0 from YJ_YJDJGL a where FYJDJH=Vyjdjh0 and SCX000='1'
       and exists (select 1 from YJ_YW0000 where BRID00=Vyw0000.BRID00 and CXDJH0=a.ZYJDJH and nvl(SFDJH0,0)=0);
begin
  Vcounter1:=0;
  Verrmsg:='';
  Vxxxlog :=substr('PYJDJH0:'||nvl(to_char(PYJDJH0), 'null')||','||'PCZY000:'||nvl(to_char(PCZY000), 'null')||','||'PCZYKS0:'||nvl(to_char(PCZYKS0), 'null')||','||'PCOMMIT:'||nvl(to_char(PCOMMIT), 'null'), 1, 2000);
  SP_TransLog(sysdate,'SP_YJ_CXCX00',Pczy000,Pczyks0,Vxxxlog);

  select count(*) into Vcounter from YJ_YW0000 where YJDJH0=Pyjdjh0;
  if Vcounter<>1 then
    Verrmsg:='单据号为'||TO_CHAR(Pyjdjh0)||'的医技单不存在';
    RAisE Ecustom;
  end if;

  Vyjdjh0 := Pyjdjh0;
  select * into Vyw0000 from YJ_YW0000 where YJDJH0=Pyjdjh0;
  
  if Vyw0000.XMZT00='6' then
     update YJ_YW0000 set XMZT00='1' where YJDJH0=Pyjdjh0;
     if nvl(pcommit,'Y')='Y' then
       commit;
     end if;
     return;
  end if;

  if (Vyw0000.XMZT00='0' ) OR (Vyw0000.XMZT00='1') then
    if (nvl(Vyw0000.CXDJH0,0)>0) and (Vyw0000.SL0000<0) then
      --冲销记录,找被冲纪录
      select count(*) into Vcounter from YJ_YW0000 where YJDJH0=Vyw0000.CXDJH0;
      if Vcounter<>1 then
        Verrmsg:='找不到单据号为'||TO_CHAR(Vyw0000.CXDJH0)||'的被冲原始医技单';
        raise Ecustom;
      else
        select * into Vyw0000 from YJ_YW0000 where YJDJH0=Vyw0000.CXDJH0;
        if (Vyw0000.XMZT00='0' ) OR (Vyw0000.XMZT00='1') then
          Verrmsg:='单据号为'||TO_CHAR(Pyjdjh0)||'的被冲原始医技单未收费，不允许撤消';
          raise Ecustom;
        end if;
        Vyjdjh0 := Vyw0000.YJDJH0;
      end if;
    else
      Verrmsg:='单据号为'||TO_CHAR(Pyjdjh0)||'的医技单未收费，不允许撤消';
      raise Ecustom;
    end if;
  end if;

  for row in CUR_YJDJH0 loop
     select count(*) into Vcounter from YJ_YW0000 where BRID00=Vyw0000.BRID00 and CXDJH0=row.YJDJH0;
     if Vcounter <1 then
       Verrmsg:='该项目没有被冲销,不能撤消'||'！*';
       raise Ecustom;
     end if;

     select count(*) into Vcounter from YJ_YW0000 Y where BRID00=Vyw0000.BRID00 and Y.CXDJH0 in (select W.YJDJH0 from YJ_YW0000 W where W.YJDJH0=row.YJDJH0) and (nvl(SFDJH0,0)<>0);
     if Vcounter >0 then
       --再找 是否存在部分未退费项目
       select count(*) into Vcounter1 from YJ_YW0000 Y where BRID00=Vyw0000.BRID00 and Y.CXDJH0 in (select W.YJDJH0 from YJ_YW0000 W where W.YJDJH0=row.YJDJH0) and (nvl(SFDJH0,0)=0);
       if Vcounter1=0 then
           Verrmsg:='收费处已经更正,不能撤消';
           RAisE Ecustom;
       end if;
     end if;

     if Vyw0000.DCBZ00='2' and Vyw0000.YZCFID>'0' then--判定相关的技术药处方是否退费，如果退费，则不能撤销申请
       if Vyw0000.MZZYBZ='0' then
         select count(*) into Vcounter from YF_MZCF00 where CFLSH0=Vyw0000.YZCFID and SFKSLY='Y' and LYKS00=Vyw0000.YJKSBH;
         if Vcounter>0 then
           select count(*) into Vcounter from YF_MZCFMX where CFLSH0=Vyw0000.YZCFID and CXBZ00='+';
           if Vcounter>0 then
             Verrmsg:='相关的基数药处方已经退费,项目不能撤消申请';
             RAisE Ecustom;
           end if;
         end if;
       else
         select count(*) into Vcounter from YF_ZYCF00 where CFLSH0=Vyw0000.YZCFID and SFKSLY='Y' and LYKS00=Vyw0000.YJKSBH;
         if Vcounter>0 then
           select count(*) into Vcounter from YF_ZYCFMX where CFLSH0=Vyw0000.YZCFID and CXBZ00='+';
           if Vcounter>0 then
             Verrmsg:='相关的基数药处方已经退费,项目不能撤消申请';
             raise Ecustom;
           end if;
         end if;
       end if;
     end if;
     --修改原记录的CXSL00
     if Vcounter1<>0 then
       update YJ_YW0000 a set CXSL00=(select a.CXSL00-abs(SUM(b.SL0000)) from YJ_YW0000 b where YJDJH0 in
           (select YJDJH0 from YJ_YW0000 Y
             where BRID00=Vyw0000.BRID00 and CXDJH0 in (select W.YJDJH0 from YJ_YW0000 W where W.YJDJH0=row.YJDJH0) and nvl(SFDJH0,0)=0))
         where YJDJH0=row.YJDJH0;
     else
       update YJ_YW0000 a set CXSL00=0 where YJDJH0=row.YJDJH0;
     end if;
     --增加and SFDJH0=0
     
     --2018.06.05 需要考虑“非全退”情况 for SSMZ9-20180529-001 
     --update YJ_YWJJ00 SET CXBZ00='Z',CXSL00=0 where YJDJH0=row.YJDJH0;
     update YJ_YWJJ00 a set CXBZ00='Z',CXSL00=(
       select -nvl(sum(SL0000),0) from YJ_YWJJ00 where YJDJH0 in
       (select YJDJH0 from YJ_YW0000 Y where BRID00=Vyw0000.BRID00 and CXDJH0 in (select W.YJDJH0 from YJ_YW0000 W where W.YJDJH0=row.YJDJH0) and nvl(SFDJH0,0)>0)
       and CXID00=a.ID0000
       ) where YJDJH0=row.YJDJH0;
     update YJ_YWJJ00 set CXBZ00='+' where YJDJH0=row.YJDJH0 and CXSL00>0; 
     
     --有诊疗带出处方时也要撤销冲销
     begin
       select ZLDCCF into Vzldccf from YJ_YW0000 where YJDJH0=Pyjdjh0;
     exception
       when others then
         Vzldccf := ' ';
     end;
     if NVL(Vzldccf,' ') <> ' ' then
       if Vyw0000.MZZYBZ=0 then
         SP_YF_MZCFCXTY(Vzldccf);
       else
         SP_YF_YZCFCXTY(Vzldccf);
       end if;
     end if;    
         
     delete from YJ_YWJJ00 where YJDJH0 in
       (select YJDJH0 from YJ_YW0000 Y where BRID00=Vyw0000.BRID00 and CXDJH0 in (select W.YJDJH0 from YJ_YW0000 W where W.YJDJH0=row.YJDJH0) and nvl(SFDJH0,0)=0);
     delete from YJ_YWMX00 where YJDJH0 in
       (select YJDJH0 from YJ_YW0000 Y where BRID00=Vyw0000.BRID00 and CXDJH0 in (select W.YJDJH0 from YJ_YW0000 W where W.YJDJH0=row.YJDJH0) and nvl(SFDJH0,0)=0);
     delete from YJ_YW0000 where YJDJH0 in
       (select YJDJH0 from YJ_YW0000 Y where BRID00=Vyw0000.BRID00 and CXDJH0 in (select W.YJDJH0 from YJ_YW0000 W where W.YJDJH0=row.YJDJH0) and nvl(SFDJH0,0)=0);
     --update YJ_YWJJ00 SET CXBZ00='Z',CXSL00=0 where YJDJH0=row.YJDJH0; --2018.06.05

     --撤销冲消时要求去掉"(取消)"字样
     if Vyw0000.mzzybz='1' and Vyw0000.dcbz00='0' and Vyw0000.yzcfid is not null then--
        update bq_yjyz00 set zlxmjc=substrb(zlxmjc,instr(zlxmjc,'(取消)')+6,lengthb(zlxmjc))
               where yzmxid=to_number(Vyw0000.yzcfid) and clbz00='1' and instrb(zlxmjc,'(取消)')>0;
     end if;

     update YJ_YJDJGL set SCX000='0' where FYJDJH=Vyjdjh0 and ZYJDJH=row.YJDJH0 and SCX000='1';
     update YJ_YJDJGL set SCX000='0' where ZYJDJH=row.YJDJH0 and SCX000='1';  --zhangwz 12.10.23

     SP_XK_XDQXCX(row.YJDJH0);

  end loop;

  if nvl(pcommit,'Y')='Y' then
    commit;
  end if;
exception
    when Ecustom then
      raise_application_error(-20011,Verrmsg);
    when no_data_found then
      raise_application_error(-20010, '数据没有找到！*'||Verrmsg);
    when others then
      raise_application_error(-20020, nvl(sqlerrm, '原因不明出错！*'||Verrmsg));
      rollback;
end SP_YJ_CXCX00;