CREATE OR REPLACE PROCEDURE SP_BQ_BRLDTJ --病人流动统计
(  pStartRq in   Varchar2,
   PStartSj in   Varchar2,
   pEndRq00 in   Varchar2,
   PEndSj00 in   Varchar2,
   PFLAG00 OUT   Varchar2
)
AS
   vStartrqsj  Varchar2(20);
   VEndRqsj00  Varchar2(20);
   vprevtrq    Varchar2(8); --开始日期的前一天
   Vcounter    number(5);
   Cursor C_YYBQBH is select bmbh00,bmmc00,bh0000 from bm_bmbm00 where bmxz00='0' and flag00='1' and czbz00 in ('0','1') order by bh0000;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- zhr         2008.07.08  改为按科室统计病人流动人数
-- pwt         2019.04.01  增加temp09取消出院人数 for BQ-20190326-002 
-- pwt         2019.04.22  取消入院人数不含日间手术，在院人数需包含取消入院之前人数 for BQ-20190422-001 
-- pwt         2019.05.05  在院人数不统计当天登记入院又取消入院的人数 for BQ-20190505-002
BEGIN
  Vcounter:=sf_bq_brldxx;
  commit;
  vStartRqsj:=pStartRq||pStartsj;
  vEndRqsj00:=pEndRq00||pEndSj00;
  vprevtrq:=to_char(to_date(pStartRq,'YYYYMMDD')-1,'YYYYMMDD');
  delete bm_zhtj00_temp00 where Flag00='BQ_BRLDTJ';
  if pEndRq00 is null then
     for bm in C_YYBQBH loop
       insert into bm_zhtj00_temp00 (flag00,bmmc00,bmbh00,startd,stopda,startt,stopti)
                   VALUES ('BQ_BRLDTJ',bm.bmmc00,bm.bmbh00,pStartRq,pEndRq00,pStartSj,pEndSj00);
       --在院人数 qsrq<=endrq and (jsrq is null or jsrq>=endrq)
       Update bm_zhtj00_temp00 set temp01=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  a.KSH000=bm.bmbh00 and yxbz00='1' and a.qszt00 in ('新入','转入') and
                                                  a.QSRQ00<=pStartRq and BQJSRQ>pStartRq
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))--泉州人民医院，过滤掉住院号带字母开头‘f’和'm'
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --病区收治人数,以住院收费处的为准
       Update bm_zhtj00_temp00 set temp02=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  a.KSH000=bm.bmbh00 and qszt00 in ('新入') and
                                                  yxbz00='1' and qsrq00=pStartRq
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --病区出院登记人数
       Update bm_zhtj00_temp00 set temp03=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='出院' AND a.KSH000=bm.bmbh00 and
                                                  yxbz00='1' and jsrq00=pStartrq
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --转入人数
       Update bm_zhtj00_temp00 set temp04=(SELECT COUNT ( a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  QSZT00 in ('转入') AND a.KSH000=bm.bmbh00 and
                                                  yxbz00='1' and QSRQ00 =pStartrq
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --转出人数
       Update bm_zhtj00_temp00 set temp05=(SELECT COUNT ( a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='转出' AND a.KSH000=bm.bmbh00 and
                                                  yxbz00='1' AND JSRQ00=pStartrq
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --实际开放床位数
       Update bm_zhtj00_temp00 set temp06=(SELECT SYCW00 from BM_BMBM00 where BMBH00=bm.bmbh00)
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --空床数
       Update bm_zhtj00_temp00 set temp07=abs(nvl(temp06,0)-nvl(temp01,0)),temp08=0
              where  flag00='BQ_BRLDTJ' and abs(nvl(temp06,0))>=abs(nvl(temp01,0));
       --加床数
       Update bm_zhtj00_temp00 set temp08=abs(nvl(temp01,0)-nvl(temp06,0)),temp07=0
              where  flag00='BQ_BRLDTJ' and abs(nvl(temp01,0))>abs(nvl(temp06,0));
       --取消入院人数 
       Update bm_zhtj00_temp00 set temp09=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='出院' AND a.KSH000=bm.bmbh00 and
                                                  yxbz00='0' and jsrq00=pStartrq and SFRJSS = '0'
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       
       --在院人数+取消入院之前的人数 
       Update bm_zhtj00_temp00 set temp01=temp01 + (SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='出院' AND a.KSH000=bm.bmbh00 and
                                                  yxbz00='0' and jsrq00>pStartrq and QSRQ00<=pStartrq and SFRJSS = '0'
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
                                                  --and not exists(select 1 from BQ_BRLDXX where ZYID00=a.ZYID00 and QSRQ00=a.QSRQ00 and QSSJ00=a.QSSJ00 and QSZT00='新入' and jsrq00=qsrq00))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       
   
       Update bm_zhtj00_temp00 set temp02=temp02 + (SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='出院' AND a.KSH000=bm.bmbh00 and
                                                  yxbz00='0' and jsrq00=pStartrq and SFRJSS = '0'
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M'))
                                                  and jsrq00=qsrq00)
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;

     end loop;

  else
     for bm in C_YYBQBH loop
       insert into bm_zhtj00_temp00 (flag00,bmmc00,bmbh00,startd,stopda,startt,stopti)
                   VALUES ('BQ_BRLDTJ',bm.bmmc00,bm.bmbh00,pStartRq,pEndRq00,pStartSj,pEndSj00);
       --期初人数 qsrq<=endrq and (jsrq is null or jsrq>=endrq)
       Update bm_zhtj00_temp00 set temp01=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  a.KSH000=bm.bmbh00 and yxbz00='1' and a.qszt00 in ('新入','转入') and
                                                  a.QSRQ00<=vprevtrq  and BQJSRQ>vprevtrq
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --病区收治人数,以住院收费处的为准
       Update bm_zhtj00_temp00 set temp02=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  a.KSH000=bm.bmbh00 and qszt00 in ('新入') and
                                                  yxbz00='1' and qsrq00 between pStartrq and pEndrq00
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --病区出院登记人数
       Update bm_zhtj00_temp00 set temp03=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='出院' AND a.KSH000=bm.bmbh00 and
                                                  yxbz00='1' and jsrq00 between pStartrq and pEndrq00
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --转入人数
       Update bm_zhtj00_temp00 set temp04=(SELECT COUNT ( a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  QSZT00='转入' AND a.KSH000=bm.bmbh00 and
                                                  yxbz00='1'AND QSRQ00 between pStartrq and pEndrq00
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --转出人数
       Update bm_zhtj00_temp00 set temp05=(SELECT COUNT ( a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='转出' AND a.KSH000=bm.bmbh00 and
                                                  yxbz00='1' AND JSRQ00 between pStartrq and pEndrq00
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --期末人数 qsrq<=endrq and (jsrq is null or jsrq>=endrq)
       Update bm_zhtj00_temp00 set temp06=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  a.KSH000=bm.bmbh00 and yxbz00='1' and a.qszt00 in ('新入','转入') and
                                                  a.QSRQ00<=pEndrq00 and BQJSRQ>pEndrq00
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --取消入院人数
       Update bm_zhtj00_temp00 set temp09=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='出院' AND a.KSH000=bm.bmbh00 and
                                                  yxbz00='0' and jsrq00 between pStartrq and pEndrq00 and SFRJSS = '0'
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       --在院人数+取消入院之前的人数 
       Update bm_zhtj00_temp00 set temp01=temp01 + (SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='出院' AND a.KSH000=bm.bmbh00 and
                                                  yxbz00='0' and jsrq00 > pEndrq00 and QSRQ00<=pEndrq00 and SFRJSS = '0'
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M')))
                                                  --and not exists(select 1 from BQ_BRLDXX where ZYID00=a.ZYID00 and QSRQ00=a.QSRQ00 and QSSJ00=a.QSSJ00 and QSZT00='新入' and jsrq00=qsrq00))
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
       
       Update bm_zhtj00_temp00 set temp02=temp02 + (SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='出院' AND a.KSH000=bm.bmbh00 and
                                                  yxbz00='0' and jsrq00 between pStartrq and pEndrq00 and SFRJSS = '0'
                                                  and a.zyid00=b.zyid00  and (upper(substr(b.zyh000,1,1)) not in ('F','M'))
                                                  and jsrq00=qsrq00)
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00;
     end loop;

  end if;
  PFLAG00:='BQ_BRLDTJ';
  COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20010, '数据没有找到！*');
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20020, substrb(NVL(SQLERRM, '原因不明出错')||'！*',1,220));
END SP_BQ_BRLDTJ;