create or replace procedure SP_SF_BRXXB0_BRTH00
(
  Pghh000 in     varchar2             ,             --挂号号  
  Pczy000 in     number               ,             --操作员
  Pczyks0 in     number               ,             --操作员科室
  Pcommit in     varchar2 default 'N' ,             --是否提交
  Pzxcgbz out    number               ,             --执行成功标志 1成功 0失败 
  Ptsxx00 out    varchar2             ,             --执行结果提示信息，成功的情况下也可能有警告信息
  Pxxxx00 out    varchar2                           --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
---lintj       2012.08.03  create
---yangy       2012.08.21  冲销数量错误,退号退费过程错误
---yangy       2012.09.17  存在已退费确认的项目，和已作废的处方，也要允许退号、、
---yangy       2012.12.08  挂号之外的费用算法修改；
-- jlg         2019.05.31  重新整理过程 MZSF9-20190530-003
-- qks         2020.06.04  判断费用是否已结算,条件从JZDH00+0<>0改为JZDH00+0>1; for YJ9-20200603-001
-- linzetao    2021.07.23  修正无法退号问题     MZSF9-20210722-001
-- qks         2021.08.26  bug:当参数YJ_XMCXZJTF=Y,门诊医生以及门诊收费系统退号,系统都会出现“挂号费退号失败！”,实际已退号成功; for MZSF9-20210826-001
-- jlg         2021.11.24  修改非挂号费用判断的错误的bug，原来按BM_ZLZD00.LBBH00 not in (20,21)判断，现在改为YJ_YW0000.FJDJBZ not in ('3','4')判断  MZSF9-20211124-001
-- chenHeyi    2022.11.04  参数MZHS_THSFJXSY=N时，号源表YS_HBHZXH只更改ZY0000，不复用此号源；   MZSF9-20221026-002
-- ruanbh      2022.12.09  退号时限制必需先退二次挂号 MZDZBLS-20221209-003
-- chenHeyi    2023.06.16  扩展MZHS_THSFJXSY，此过程取第1段       MZHS9-20230609-001
-- jiangjh     2024.10.11  YS_CFXXB0.CFZT00 增加判断 not in('0','2') MZSF13-20241008-002  
-- luowf       2024.01.14  当病人处方是已记账，已发药的状态，不允许病人退号成功。   MZSF13-20250107-002  
----------------------------------------------------------------------------------------
as
  Vczyxm0     varchar2(20);
  Vghksbh     SF_YSGHHB.GHKSBH%type;                --旧挂号对应的挂号科室编号
  Vghysbh     SF_YSGHHB.GHYSBH%type;                --旧挂号对应的挂号医生编号
  Vghlb00     SF_YSGHHB.GHLBBH%type;                --旧挂号对应的挂号类别编号
  Vghrq00     SF_YSGHHB.GHMZSJ%type;                --旧挂号对应的挂号日期
  Vghhbid     SF_YSGHHB.GHHBID%type;                --挂号号表ID
  Vid0000     SF_HZBR00.ID0000%type;                --候诊类别ID,SF_HZBR00.ID0000
  Vhzlb00     SF_BRXXB0.HZLB00%type;                
  Vsfdtyy     SF_HZBR00.SFDTYY%type;                --是否当天预约：'1'当天预约
  Vcxdjh0     YJ_YW0000.YJDJH0%type;                --冲销单据号
  Vczrq00     char(8);                             
  Vczsj00     char(8);
  Vcounter    number(14,4);                         --计数器变量
  Ecustom     exception;    
  aYjdjh0List varchar2(200);
  i           number(3);
  j           number(3);
  sYjdjh0     varchar2(20);
  sSl0000     varchar2(10);
  Vhjje00     SF_BRFY00.HJJE00%type;                 --合计金额
  VTHSFJXSY   XT_XTCS00.VALUE0%type;  --患者取某个现场号源后,护士做退预挂号操作后,是否允许该号源继续使用
  VBRID00     SF_BRXXB0.BRID00%type;                 --病人id
  YS_BRECGHLB XT_XTCS00.VALUE0%type;                 --二次挂号类别
  
  cursor CUR_YJYW00 is 
    select YJDJH0,SL0000 from YJ_YW0000 a where ZYHGHH = trim(Pghh000) and MZZYBZ = '0' and nvl(CXDJH0,0)=0 and a.zje000>0 and a.xmzt00<>'6' 
      and not exists(select 1 from YJ_YW0000 where CXDJH0=a.YJDJH0 and zyghid=a.zyghid and mzzybz=a.mzzybz and xmzt00 in ('2','3'));

begin                                        
  Pzxcgbz := 0;
  Vczrq00 := to_char(sysdate,'yyyymmdd');
  Vczsj00 := to_char(sysdate,'hh24:mi:ss');
  Pxxxx00 :=nvl(to_char(trim(Pghh000)),'null')||','||nvl(to_char(Pczy000),'null')||','||nvl(to_char(Pczyks0),'null')||','||nvl(to_char(Pcommit),'null');
  SP_TransLog(sysdate,'SP_SF_BRXXB0_BRTH00',Pczy000,Pczyks0,Pxxxx00);   
  
  select nvl(max(substrb(trim(VALUE0),1,1)),'Y') into VTHSFJXSY from XT_XTCS00 where NAME00='MZHS_THSFJXSY'; 
  if VTHSFJXSY<>'Y' and VTHSFJXSY<>'N' then
    VTHSFJXSY:='Y';
  end if;

  --处方未退药，不能退号
  --SF_WJZCFSFYXTH
  --含有未记帐的医技单，是否作废该医技单
  --含有未记帐的处方单，是否作废该处方单
  --作废未记账的医技单，处方单
  --SP_SF_BRGHXX_YBEDIT 门诊医保病人挂号冲销补充存储过程 合并到这里处理吧 
  
  select count(*) into Vcounter from SF_FYMX00 where JZDH00+0>1 and JFLBID=1 and MZH000 = trim(Pghh000);
  if Vcounter > 0 then
    Ptsxx00 :='预交金病人该挂号有费用结算过，不能退号!';
    raise Ecustom;
  end if;       
  
  select sum(HJJE00) into Vhjje00 from SF_FYMX00 a where  a.MZH000 = trim(Pghh000)  
        and a.CXBZ00='Z' and exists(select 1 from YJ_YW0000 d where d.ZYHGHH=a.MZH000 and d.MZZYBZ='0' and d.SFDJH0=a.DJH000 and d.FJDJBZ not in ('3','4')); --3挂号费4诊查费
  if Vhjje00 > 0 then
      Ptsxx00 :='该挂号还有除挂号费用外的其它费用，不能退号!';
      raise Ecustom;
  end if;  
  
  
 -- select count(*) into Vcounter from YS_CFXXB0 a,SF_BRXXB0 c where a.GHID00=c.GHID00 and c.GHH000 = trim(Pghh000) and c.THBZ00='2' and a.CFZT00 not in('0','2');  
  select count(*) into Vcounter from YF_MZCF00 a,SF_BRXXB0 c where a.GHID00=c.GHID00 and  c.GHH000 = trim(Pghh000) and c.THBZ00='2' and a.CFZT00 not in ('0','3','6')
  and not exists(select 1 from YF_MZCF00 where GHID00=a.GHID00 and BCCFH0=a.CFLSH0 and abs(CFZJE0)=a.CFZJE0);          
  if Vcounter <> 0 then 
    Ptsxx00 :='有处方存在，须先经医生申请才可进行退号!';
    raise Ecustom;
  end if;  
    
  select count(*) into Vcounter from YJ_YW0000 a,bm_zlzd00 b, SF_BRXXB0 c where a.ZYGHID=c.GHID00 and a.ZLXMID=b.ZLXMID and A.MZZYBZ='0' and c.GHH000 = trim(Pghh000) 
           and c.THBZ00='2' and a.XMZT00<>'6' and a.ZJE000>0 and b.lbbh00 not in (20,21) and not exists(select 1 from YJ_YW0000 d where d.ZYGHID=a.ZYGHID and d.MZZYBZ='0' and d.CXDJH0=a.YJDJH0 and d.XMZT00 in ('2','3'));           
  if Vcounter <> 0 then 
    Ptsxx00 :='存在有效的非药品项目，不能进行退号!';
    raise Ecustom;
  end if;  
  
  select ZWXM00 into Vczyxm0 from BM_YGBM00 where YGBH00 = Pczy000;
  select GHKS00,JZYS00,GHLB00,GHRQ00,BRID00 into Vghksbh,Vghysbh,Vghlb00,Vghrq00,VBRID00 from SF_BRXXB0 where GHH000 = trim(Pghh000);  
  
  select nvl(max(VALUE0),'N') into YS_BRECGHLB from XT_XTCS00 where NAME00='YS_BRECGHLB';
  if instr(','||YS_BRECGHLB||',',','||Vghlb00||',')=0 then    
    select count(*) into Vcounter from SF_BRXXB0 A where A.GHRQ00=Vghrq00 and A.JZYS00=Vghysbh and A.BRID00=VBRID00 and instr(','||YS_BRECGHLB||',',','||A.GHLB00||',')>0 AND A.THBZ00<>'0';           
    if Vcounter <> 0 then 
      Ptsxx00 :='存在二次挂号，不能进行退号，请先退二次挂号!';
      raise Ecustom;
    end if;
  end if;  
  
  select nvl(max(ID0000),0),nvl(max(YSPBID),0),nvl(max(HZLB00),0),nvl(max(SFDTYY),'0') into Vid0000,Vghhbid,Vhzlb00,Vsfdtyy  
    from SF_HZBR00 where GHH000 = trim(Pghh000) and HZZT00<>'Z';        
  if VTHSFJXSY='N' then
    --该号源不允许使用时，只更改ZY0000；BRID00不动
    update YS_HBHZXH set ZY0000='挂号:'||Pghh000||',BRID00:'||to_char(BRID00)||'使用过' where GHHBID=Vghhbid and GHH000=Pghh000; 
  else
    if Vhzlb00=3 then--退预约号
      update  SF_YSGHHB  set   GHYGHS = nvl(GHYGHS,0)-1,
                             GHYYHS = nvl(GHYYHS,0)-1,
                             YYXHS0 = nvl(YYXHS0,0)-decode(Vsfdtyy,'1',1,0),
                             TQYYS0 = nvl(TQYYS0,0)-decode(Vsfdtyy,'1',1,0)
                      where  GHKSBH = Vghksbh
                        and  GHYSBH = Vghysbh
                        and  GHLBBH = Vghlb00
                        and  GHMZSJ = Vghrq00
                        and (GHHBID = Vghhbid or Vghhbid = 0)
                        and  GHYGHS >= 1  and  rownum = 1;      
    else
      update  SF_YSGHHB  set   GHYGHS = nvl(GHYGHS,0)-1
                      where  GHKSBH = Vghksbh
                        and  GHYSBH = Vghysbh
                        and  GHLBBH = Vghlb00
                        and  GHMZSJ = Vghrq00
                        and (GHHBID = Vghhbid or Vghhbid = 0)
                        and  GHYGHS >= 1  and  rownum = 1;
    end if;   
    update YS_HBHZXH set ZY0000='挂号:'||Pghh000||',BRID00:'||to_char(BRID00)||'使用过',BRID00=null,BRXM00=null,GHH000=null,YYBZ00='1' where GHHBID=Vghhbid and GHH000=Pghh000; 
  end if;                

  update SF_BRXXB0  set   THRQ00 = Vczrq00,
                          THSJ00 = Vczsj00,
                          THY000 = Pczy000,
                          THYXM0 = Vczyxm0,
                          THBZ00 = '0'    --该挂号信息已退号
                   where  GHH000 = trim(Pghh000);
  --普通病人保留旧记录，其他病人删除候诊记录
  delete  from  SF_HZBR00  where ID0000 = Vid0000 and HZLB00<>'1';  --将病人候诊表中的信息删除
  update SF_HZBR00 set DLXH00=-100,HZZT00='Z' where ID0000=Vid0000 and HZLB00='1';      
  --update YS_HBHZXH set ZY0000='挂号:'||Pghh000||',BRID00:'||to_char(BRID00)||'使用过',BRID00=null,BRXM00=null,GHH000=null,YYBZ00='1' where GHHBID=Vghhbid and GHH000=Pghh000;
  
  i:=0;
  aYjdjh0List := '';
  for reg in CUR_YJYW00 loop
      aYjdjh0List := reg.yjdjh0||','||reg.sl0000||';'||aYjdjh0List;     
  end loop; 

  while (aYjdjh0List is not null or length(aYjdjh0List)=0) loop
    i := instrb(aYjdjh0List,',');
    sYjdjh0 := substr(aYjdjh0List,1,i-1);
    j := instrb(aYjdjh0List,';');
    sSl0000 := substr(aYjdjh0List,i+1,j-i-1);
    aYjdjh0List := substr(aYjdjh0List,j+1,length(aYjdjh0List)-j);
    if (sYjdjh0 is not null) and (to_number(sSl0000)>0) then
      begin
        SP_YJ_YWCX(
          sYjdjh0,            --医技单据号
          Pczy000,            --员工编号
          Pczyks0,            --科室编号
          'N',                --病区申请的项目是否直接退费
          'N',                --是否重新申请
          to_number(sSl0000)  --冲消数量      
          );   
      exception
        when others then
          Ptsxx00 := '挂号费退费失败！';
          Pxxxx00 := '执行医技业务冲销过程SP_YJ_YWCX失败'||substrb(sqlerrm,1,150)||'！*';
          raise Ecustom;
      end;
      select nvl(max(YJDJH0),0) into Vcxdjh0 from YJ_YW0000 where ZYHGHH=Pghh000 and CXDJH0=sYjdjh0 and nvl(sfdjh0,0)=0;
      if Vcxdjh0 > 0 then
        begin
            SP_SF_YJGZ00_YJD000(
              Vcxdjh0,         --医技单据号
              Pczy000,         --操作员
              Vczyxm0,         --操作员姓名
              Pczyks0,         --操作员科室
              Pczy000,         --审核员
              '',              --审核原因
              Ptsxx00,         --存储过程提示的错误信息
              Pxxxx00,         --系统提示的错误信息
              '0',             --是否收费员登记, 0否1是
              'N'              --是否提交
            );
            if trim(Ptsxx00) is not null then
              raise Ecustom;
            end if;
        exception
          when others then
            Ptsxx00 := '挂号费退费确认失败！';
            Pxxxx00 := '执行费用更正过程SP_SF_YJGZ00_YJD000失败'||substrb(sqlerrm,1,150)||'！*';
            raise Ecustom;
        end;
      end if;
    end if;
  end loop;
  
  Pzxcgbz:=1;
  if pcommit='Y' then
     commit;
  end if;
  
exception
  when no_data_found then
    raise_application_error(-20001,substrb('没有找到数据！*'||sqlerrm,1,240));
  when Ecustom       then
    --Pxxxx00 := '执行费用更正过程SP_SF_YJGZ00_YJD000失败！*'||nvl(sqlerrm,'原因不明出错！');
    Pxxxx00 := Pxxxx00;
    --raise_application_error(-20010,substrb(Ptsxx00||'！*',1,240));
  when others        then
    raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240));    
end;
