create or replace procedure SP_BQ_BRZKDJ--病人转科登记
(
  PZYID00 in      number,                 --病人ID
  PZCBQ00 in      number,                 --转出病区号
  PZRBQ00 in      number,                 --转入病区号
  PKSH000 in      number,                 --转入科室
  PZKYS00 in      number,                 --转科医生
  PCZY000 in      number,                 --操作员
  PZKJCBZ in      char    default 'N',    --转科借床标志(是否护理迁出)
  PJSYS00 in      number    default 0,    --接收医生
  PJSHS00 in      number    default 0,    --接收护士
  PJSYY00 in      varchar2  default null  --事件原因
)
-- MODifICATION HISTORY
-- Person   Date         Comments
-- yangy    2012.03.09   病人转科登记
-- xuxm     2017.02.22   病人转区不判断必须出径。for BQHS9.0-20170217-003
-- xuxm     2018.03.30   医生做了取消零嘱操作，护士站转科没有去过滤这种状态医嘱，所以这类医嘱就卡住影响转科操作 for BQHS9.0-20180330-001
-- linyx    2018.04.08   省人民医院转科登记判断是否存在已经停止、撤销、取消的医嘱，tzshr0没有填入，入存在，就填入当前操作人 for BQHS9.0-20180408-003
-- linyx    2018.05.14   厦门莲花医院增加参数BQ_QYZQSFBXZTZYZ控制护理迁出可以不停止医嘱转出 for BQHS9.0-20180510-001
-- fuyc     2018.06.11   【病人转科治疗】由于本院是妇幼保健院，故一般转科治疗病种没有发生改变，比如待产病区转产科一区情况。现要求增加参数控制，转科治疗不需要退出路径操作。 for BQHS9.0-20180610-006
-- linyx    2018.06.22   仙游妇幼 有未记账的医技单可以转区，不做限制，参数BQ_ZKSFCLFY控制 for BQHS9.0-20180614-003
-- linyx    2018.08.20   临床路径引用过来的医嘱，转科没停止的，要修改cjks00为新的病区 for BQHS9.0-20180815-001
--huangzw   2021.06.08   是否允许处方未记账及退药未处理病人做转科登记  for      BQHS9.0-20210604-003
-- dongxb   2023.11.08   转科患者结束无陪护状态 for BQHS9.0-20231106-003
as

  COUNTER              NUMBER(5) ;
  COUNTER2             NUMBER(5) ;
  VCZYXM0   BM_YGBM00.ZWXM00%TYPE; --操作员姓名
  Vcfscbz   XT_XTCS00.VALUE0%TYPE; --病人出院登记时删除处方标志
  Vyjscbz   XT_XTCS00.VALUE0%TYPE; --病人出院登记时删除医技项目标志
  Vcfyxcy   XT_XTCS00.VALUE0%TYPE; --存在未发药品允许出院
  Vyjyxcy   XT_XTCS00.VALUE0%TYPE; --存在未执行医技允许出院
  Vtyyxcy   XT_XTCS00.VALUE0%TYPE; --退药未处理允许允许出院
  Vyjqxsf   XT_XTCS00.VALUE0%TYPE; --医技单未收费的出院时强行收费
  Vparams           VARCHAR2(255);
  Verrmsg           varchar2(255);
  Ecustom               EXCEPTION; --
  VSYSDATE                   DATE;
  Vcbqjzxx  BM_BMBM00.JZXX00%type; --转出病区JZXX00
  Vjbqjzxx  BM_BMBM00.JZXX00%type; --转入病区JZXX00
  vcwyj00   BM_CWK000.CWYJ00%type; --床位押金
  vjcybqh   BM_BMBM00.BMBH00%type; --借床病人原病区号或者转出病区号
  v_BQZCBRSSFYXBTZYZ  XT_XTCS00.VALUE0%TYPE;   --病区转出病人时是否有允许不停止医嘱的转入病区,支持多个病区(主要为ICU病区)
  lv_ZQSFBXZTZYZ      varchar2(1); --转区是否不限制停止医嘱
  lv_BRZKQDQKS00      ZY_BRXXB0.DQKS00%TYPE;   --病人转科前当前科室.
  VZRZCID   LJ_ZRZC00.ZRZCID%type;
  v_qyzqsfbxztzyz     XT_XTCS00.VALUE0%TYPE;
  VYJWJZ0   XT_XTCS00.VALUE0%TYPE; --病人转科登记时有未记账的医技单是否可以转科
  VWJZTY0   XT_XTCS00.VALUE0%TYPE; --是否允许处方未记账及退药未处理病人做转科登记
begin
  VSYSDATE:=SYSDATE;
  if nvl(PZKYS00,0)=0 then
     Verrmsg:='未指定同意病人转科的医生';
     Raise Ecustom;
  end if;
  if PZKJCBZ='Y' then
    Vcbqjzxx:=0;
    Vjbqjzxx:=0;
  else
    begin       --获取借床病人原病区号
      select decode(YBQH00,'',DQBQ00,YBQH00) into vjcybqh from ZY_BRXXB0 where ZYID00=PZYID00;
    exception
      when others then
        vjcybqh:= PZCBQ00;
    end;
    begin
    select nvl(JZXX00,0) into Vcbqjzxx from BM_BMBM00 where BMBH00=vjcybqh;
    exception
      when others then
      Vcbqjzxx:=0;
    end;
    begin
    select nvl(JZXX00,0) into Vjbqjzxx from BM_BMBM00 where BMBH00=PZRBQ00;
    exception
      when others then
      Vjbqjzxx:=0;
    end;
  end if;

  begin
    select nvl(sum(CWYJ00),0) into vcwyj00  from BM_CWK000 where ZYID00=PZYID00;
  exception
    when others then
    vcwyj00:=0;
  end;

  lv_ZQSFBXZTZYZ:='N';
  begin--病区转出病人时是否有允许不停止医嘱的转入病区,支持多个病区(主要为ICU病区)
    select upper(trim(VALUE0)) inTO v_BQZCBRSSFYXBTZYZ from XT_XTCS00 where NAME00='BQ_BQZCBRSSFYXBTZYZ';
  exception
    when others then
    v_BQZCBRSSFYXBTZYZ:='N';
  end;
  if trim(v_BQZCBRSSFYXBTZYZ)<>'N' then
    begin
      select instr(v_BQZCBRSSFYXBTZYZ,'出'||to_char(PZCBQ00)||':') into COUNTER from dual;
      if COUNTER>0 then
        begin
          v_BQZCBRSSFYXBTZYZ:=substr(v_BQZCBRSSFYXBTZYZ,COUNTER+length('出'||to_char(PZCBQ00)||':'),length(v_BQZCBRSSFYXBTZYZ)-COUNTER-length('出'||to_char(PZCBQ00)||':')+1);
          select instr(v_BQZCBRSSFYXBTZYZ,'&') into COUNTER2 from dual;
          if COUNTER2>0 then
            begin
              v_BQZCBRSSFYXBTZYZ:=substr(v_BQZCBRSSFYXBTZYZ,1,COUNTER2-1);
            end;
          end if;
--          v_BQZCBRSSFYXBTZYZ:=','||v_BQZCBRSSFYXBTZYZ||',';
          select instr(','||trim(v_BQZCBRSSFYXBTZYZ)||',',','||to_char(PZRBQ00)||',') into COUNTER2 from dual;
          if COUNTER2>0 then
            begin
              lv_ZQSFBXZTZYZ:='Y';
            end;
          end if;
        end;
      end if;
    end;
  end if;

  begin--病区转出病人时是否允许不停止医嘱(厦门莲花医院)
    select upper(trim(VALUE0)) into v_qyzqsfbxztzyz from XT_XTCS00 where NAME00='BQ_QYZQSFBXZTZYZ';
    if v_qyzqsfbxztzyz='Y' then
      lv_ZQSFBXZTZYZ:='Y';
    end if;
  exception
    when others then
    v_qyzqsfbxztzyz:='N';
  end;

  select ZWXM00 into VCZYXM0 from BM_YGBM00 where YGBH00=PCZY000;
  select count(*) into COUNTER from ZY_BRXXB0
  where DQBQ00=PZCBQ00 and ZYID00=PZYID00 and BRZT00 in ('20');
  if COUNTER=0 then
     Verrmsg:='病人状态已经改变，请刷新后重试';
     Raise Ecustom;
  end if;
  select DQKS00 into lv_BRZKQDQKS00 from ZY_BRXXB0 where ZYID00=PZYID00;

  --（仙游县第二医院）转科治疗病人时是否允许不停止临床路径
  select count(1) into counter from xt_xtcs00 where name00='BQ_SFYXBTZLCLJ' and value0='Y';
    if (COUNTER=0)  then
       --判断病人是否还在路径中
       select count(*) into  COUNTER from LJ_ZRZC00 where ZYID00=PZYID00 and nvl(JSRQ00,' ')=' ';
       if (COUNTER>0) and lv_BRZKQDQKS00<>PKSH000  then
       Verrmsg:='病人还没有出径，请出径后再转区';
       Raise Ecustom;
       end if;
  end if;

  select count(1) into counter from xt_xtcs00 where name00='BQ_SFQYHLLJ' and value0='Y';  --是否启用护理路径
--判断病人是否还在路径护理工作未处理
  if counter>0 then
    begin
      select  ZRZCID into  VZRZCID  from (
      select ZRZCID from LJ_ZRZC00 where ZYID00=PZYID00 and JSFS00='完成' and KSBH00=lv_BRZKQDQKS00 order by KSRQ00 desc) where rownum=1 ;
    exception
      when others then
        VZRZCID:=0;
    end;
    if VZRZCID>0  then  --是否启用护理路径
      select count(*) into  VZRZCID from LJ_HLZXJL where ZRZCID=VZRZCID and SFZX00='0';
      if (COUNTER>0)  then
        Verrmsg:='病人还有未处理的护理工作，请处理后后再出院登记';
        Raise Ecustom;
      end if;
    end if;
  end if;
  --判定病人是否有未停止的项目
  select count(*) into COUNTER from BQ_YPYZ00 where ZYID00=PZYID00 and YZZT00 in ('1','2') and rownum=1;

  if (COUNTER>0) and (lv_ZQSFBXZTZYZ='N') then
     Verrmsg:='病人有未按时执行的药品医嘱，请停止医嘱后再转区';
     Raise Ecustom;
  end if;
  select count(*) into COUNTER from BQ_YZZXJL
  where  ZYID00=PZYID00 and YZID00>0 and DJHLB0='0' and ZT0000 in ('0')and rownum=1 and SFXYZX='Y';

  --if (COUNTER>0) and (lv_ZQSFBXZTZYZ='N') then
  --   Verrmsg:='病人有未执行的药品医嘱，请执行后再转区';
  --   Raise Ecustom;
  --end if;

  select count(*) inTO COUNTER from BQ_YJYZ00 where ZYID00=PZYID00 and YZZT00 in ('1','2') and YZLB00<>9 and ROWNUM=1;
  if (COUNTER>0) and (lv_ZQSFBXZTZYZ='N') then
     Verrmsg:='病人有未按时执行的医技医嘱，请停止医嘱后再转区';
     Raise Ecustom;
  end if;

  select count(*) into COUNTER from BQ_JZXM00 where ZYID00=PZYID00 and TZRQ00 is null and ROWNUM=1;
  if (COUNTER>0) and (lv_ZQSFBXZTZYZ='N') then
     Verrmsg:='病人有未按时收费的项目，请停止收费项目后再转区';
     Raise Ecustom;
  end if;
  begin--出院时未收费处方是否强制作废
    select UPPER(TRIM(VALUE0)) inTO VCFSCBZ from XT_XTCS00 where NAME00='BQ_CYDJCFSCBZ';
  exception
    when others THEN
    VCFSCBZ:='N';
  end;
  begin--出院时未收费医技是否强制作废
    select upper(trim(VALUE0)) into VYJSCBZ from XT_XTCS00 where NAME00='BQ_CYDJYJXMSCBZ';
  exception
    when others then
    VYJSCBZ:='N';
  end;
  begin--处方未记账不能出院判定
    select UPPER(TRIM(VALUE0)) inTO VCFYXCY from XT_XTCS00 where NAME00='ZY_WJZBNCY';
    if VCFYXCY='Y' then
      VCFYXCY:='N';
    else
      VCFYXCY:='Y';
    end if;
  exception
    when others then
    VCFYXCY:='Y';
  end;
  begin--医技未记账出院判定
    select upper(trim(VALUE0)) inTO VYJYXCY from XT_XTCS00 where NAME00='BQ_WZXYJSFYXCY';
  exception
    when others then
    VYJYXCY:='Y';
  end;
  begin--退药未处理出院判定
    select upper(trim(VALUE0)) inTO VTYYXCY from XT_XTCS00 where NAME00='ZY_YTFSFYXCY';
    if VTYYXCY='Y' then
       VTYYXCY:='N';
    else
       VTYYXCY:='Y';
    end if;
  exception
    when others then
    VTYYXCY:='Y';
  end;
  select trim(nvl(max(VALUE0),'N')) into VWJZTY0 from XT_XTCS00 where NAME00='BQ_SFYXWJWCLZKDJ';
  if VWJZTY0='Y' then
        VCFYXCY:='Y';
        VTYYXCY:='Y';
  end if;
  --处理处方
  if VCFYXCY='N' then--未收费处方不允许出院
    select count(*) inTO COUNTER from YF_YZYPSQ where ZYID00=PZYID00 and QLZT00='0'and CXBZ00 in ('Z') and nvl(ZBYBZ0,'0')='0';
    if COUNTER>0 then
       Verrmsg:='病人有医嘱药品处方未发，不能转区';
       Raise Ecustom;
    end if;
    select count(*) inTO COUNTER from YF_ZYCF00 C,YF_ZYCFMX M where C.CFLSH0=M.CFLSH0 and ZYID00=PZYID00 and CFZT00='0'and CXBZ00 in ('Z');-- and SFKSLY='Y' and DQBQ00=PZCBQ00;--CFSRBZ in ('4','6');
    if COUNTER>0 then
       Verrmsg:='病人有零散处方未记账，不能转区';
       Raise Ecustom;
    end if;
  end if;
  --仙游妇幼，有未记账的医技单，允许转区
  select trim(nvl(max(VALUE0),'N')) into VYJWJZ0 from XT_XTCS00 where NAME00='BQ_ZKSFCLFY';
  --处理医技单
  if ((VYJYXCY='N') or (VYJYXCY='M')) and (VYJWJZ0='Y') then--医技单未收费的不允许出院
    select count(*) inTO COUNTER from YJ_YW0000 a where ZYGHID=PZYID00 and MZZYBZ='1'and XMZT00 in ('0','1') and zje000<>0 and rownum=1 and nvl(CXDJH0,0)=0 --and YJLRBZ='N' and KDBQ00=PZCBQ00;
        and (VYJYXCY='N' or (VYJYXCY='M' and exists (select 1 from YJ_YWJJ00 where YJDJH0=a.YJDJH0)));
    if COUNTER>0 then
       Verrmsg:='病人有未记账的医技单，不能转区';
       Raise Ecustom;
    end if;
  end if;

  if VTYYXCY='N' then--退药未处理不允许出院
    select count(*) inTO COUNTER from YF_YZYPSQ where ZYID00=PZYID00 and QLZT00='0'and CXBZ00='-';-- and BQBMBH=PZCBQ00;
    if COUNTER>0 then
       Verrmsg:='病人有医嘱处方退药未处理，不能转区';
       Raise Ecustom;
    end if;
    select count(*) inTO COUNTER from YF_ZYCF00 C,YF_ZYCFMX M where C.CFLSH0=M.CFLSH0 and ZYID00=PZYID00 and CFZT00='0'and CXBZ00 <>'Z';-- and SFKSLY='Y' and DQBQ00=PZCBQ00;--CFSRBZ in ('4','6');
    if COUNTER>0 then
       Verrmsg:='病人有零散处方退药未处理，不能转区';
       Raise Ecustom;
    end if;
  end if;


  update ZY_BRXXB0 set BRZT00=decode(PZKJCBZ,'Y','24','21'),RYCWH0=NULL,HLJBID=NULL,DQBQ00=PZRBQ00,DQKS00=PKSH000,
                       ZKJCBZ=PZKJCBZ,SXYS00=PJSYS00,ZGHS00=PJSHS00,
                       SXYSXM=(SELECT decode(PJSYS00,0,null,ZWXM00)                    FROM BM_YGBM00 WHERE YGBH00=PJSYS00),
                       WPHDJ0=decode(PZKJCBZ,'Y',WPHDJ0,null)
  where ZYID00=PZYID00;
  if SQL%NOTFOUND then
     Verrmsg:='病人信息错误，请刷新后重试';
     Raise Ecustom;
  end if;
  --修改病人的流动信息

  update BQ_BRLDXX set JSCZY0=PCZY000,JSZT00=decode(PZKJCBZ,'Y','迁出','转出'),ZZYS00=PZKYS00, BZ0000 = PJSYY00
                       --JSRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'),JSSJ00=TO_CHAR(VSYSDATE,'HH24:MI:SS')
  where  ZYID00=PZYID00 and JSZT00 is null;
  if SQL%NOTFOUND then--没有发现信息
    Verrmsg:='病人流动信息异常，请联系管理员！';
    Raise Ecustom;
    --insert into BQ_BRLDXX(
    --            ZYID00, CH0000, BQH000, KSH000, QSZT00,
    --            QSCZY0, QSZZYS, QSZRYS,
    --            QSRQ00, QSSJ00,
    --            JSCZY0, JSZT00, ZZYS00,
    --            JSRQ00, JSSJ00)
    --values     (PZYID00, 0     , PZCBQ00, 0     ,'转入',
    --            PCZY000, 0     , 0     ,
    --            TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS'),
    --            PCZY000, '转出',PZKYS00,null,null);
    --            --TO_CHAR(VSYSDATE,'YYYYMMDD'),TO_CHAR(VSYSDATE,'HH24:MI:SS'));
  end if;
  --去掉旧押金,病人有可能有多张床位
  update ZY_BRZHXX set TYJE00=TYJE00-Vcbqjzxx+Vjbqjzxx+vcwyj00
  where  BRID00=(select BRID00 from ZY_BRXXB0 where ZYID00=PZYID00) and SFDJ00='0' and
         JFLBID=(select JFLBID from BM_BRJFLB where SYBZ00='1' and SFYX00='Y' and rownum=1);
  if SQL%NOTFOUND then
     Verrmsg:='病人帐户有问题，转区失败';
     Raise Ecustom;
  end if;
  --修改床位信息,
  update BM_CWK000 set CWZT00='0',ZYID00=NULL,ZCH000=NULL where  ZYID00=PZYID00;
  if SQL%NOTFOUND then
     Verrmsg:='病人的床位信息错误';
     Raise Ecustom;
  end if;

  if lv_ZQSFBXZTZYZ='Y' then   --开始处理转区不停止医嘱的KDBQ00,KDKS00  by BQ-20110727-006.
    begin
      --药品医嘱
      UPDATE BQ_YPYZ00 SET BQH000=PZRBQ00,KSH000=PKSH000 where ZYID00=PZYID00 and YZZT00 in ('1','2');
      --医技医嘱
      UPDATE BQ_YJYZ00 SET BQH000=PZRBQ00,KSH000=PKSH000,ZXKS00=PKSH000 where ZYID00=PZYID00 and YZZT00 in ('1','2')  and ZXKS00=lv_BRZKQDQKS00;
      UPDATE BQ_YJYZ00 SET BQH000=PZRBQ00,KSH000=PKSH000 where ZYID00=PZYID00 and YZZT00 in ('1','2');
    end;
  end if;
  --------------------------------更改医生新开或者是已提交未核对的医嘱医嘱表的BQH000等数据的变化-----------------------
  --药品医嘱
  UPDATE BQ_YPYZ00 SET BQH000=PZRBQ00,KSH000=PKSH000 where ZYID00=PZYID00 and YZZT00 in ('0','7');
  --医技医嘱
  UPDATE BQ_YJYZ00 SET BQH000=PZRBQ00,KSH000=PKSH000,ZXKS00=PKSH000 where ZYID00=PZYID00 and YZZT00 in ('0','7')  and ZXKS00=lv_BRZKQDQKS00;
  UPDATE BQ_YJYZ00 SET BQH000=PZRBQ00,KSH000=PKSH000 where ZYID00=PZYID00 and YZZT00 in ('0','7');
  --------------------------------临床路径引用过来的医嘱，转科没停止的，要修改cjks00为新的病区-----------------------------------------------------------------
  UPDATE BQ_YJYZ00 SET CJKS00=PZRBQ00 where ZYID00=PZYID00 and YZZT00 in ('0','7','1','2') and CJKS00 is not null;
  --------------------------------------------------------------------------------------------------------------
  --写入tzshr0，防止tzshr0为空，导致医嘱打印出来的护士签名为空
  select count(1) into COUNTER from BQ_YPYZ00 where YZZT00 in ('3','4','6') and TZSHR0 is null and ZYID00=PZYID00;
  if COUNTER>0 then
    update BQ_YPYZ00 set TZSHR0=PCZY000 where YZZT00 in ('3','4','6') and TZSHR0 is null and ZYID00=PZYID00;
  end if;
  select count(1) into COUNTER from BQ_YJYZ00 where YZZT00 in ('3','4','6') and TZSHR0 is null and ZYID00=PZYID00;
  if COUNTER>0 then
    update BQ_YJYZ00 set TZSHR0=PCZY000 where YZZT00 in ('3','4','6') and TZSHR0 is null and ZYID00=PZYID00;
  end if;
  ----------------------------------------------------------------------------------------------------------------------
  --##转科患者结束无陪护状态
  update BQ_WPHJL0 set JSRQ00=to_char(VSYSDATE,'YYYYMMDD')
                      ,JSSJ00=to_char(VSYSDATE,'HH24:MI:SS') 
   where JSRQ00 is null and ZYID00=PZYID00 and nvl(PZKJCBZ,'N')='N';
  commit;
exception
  when Ecustom       then
    raise_application_error(-20010,substrb(Verrmsg||'！*'||Vparams,1,220));
  when no_data_found then
    raise_application_error(-20001,substrb('数据没有找到！*'||Verrmsg||Vparams,1,220));
  when others        then
    raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*'||Vparams,1,220));
end SP_BQ_BRZKDJ;