create or replace procedure SP_BQ_BRLDTJ --病人流动统计
(  pStartRq in   Varchar2,
   PStartSj in   Varchar2,
   pEndRq00 in   Varchar2,
   PEndSj00 in   Varchar2,
   PFLAG00 OUT   Varchar2,
   PID0000 OUT   number
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- zhr         2008.12.02  修正部门编码表病区被停用后，历史数据无法统计出来的问题
-- zhaoxz     2017.03.10   处理病区病人流动统计，在院病人不准确问题 BQHS9.0-20170307-001
-- zhaoxz     2017.04.12   修改数据整理表BM_ZHTJ00_TEMP00为BQ_ZHTJ00_TEMP00 BQHS9.0-20170412-009
-- zhaoxz     2017.04.13   表BQ_ZHTJ00_TEMP00增加字段ID0000，过程增加出参 BQHS9.0-20170413-008
-- zhaoxz     2017.05.11   优化在院人数统计语句 BQHS9.0-20170511-001
AS
   vStartrqsj  Varchar2(20);
   VEndRqsj00  Varchar2(20);
   vprevtrq    Varchar2(8); --开始日期的前一天
   Vcounter    number(5);
   Vid0000     BQ_ZHTJ00_TEMP00.ID0000%TYPE;
   Cursor C_YYBQBH is select bmbh00,bmmc00,bh0000 from bm_bmbm00 where bmxz00='9' and flag00='1' and czbz00 in ('0','1','2') order by bh0000;
BEGIN
  Vcounter:=sf_bq_brldxx;
  commit;
  vStartRqsj:=pStartRq||pStartsj;
  vEndRqsj00:=pEndRq00||pEndSj00;
  vprevtrq:=to_char(to_date(pStartRq,'YYYYMMDD')-1,'YYYYMMDD');
  select SQ_BQ_ZHTJ00_TEMP00_ID0000.Nextval into Vid0000 from dual;
  --delete BQ_ZHTJ00_TEMP00 where Flag00='BQ_BRLDTJ';
  if pEndRq00 is null then
     for bm in C_YYBQBH loop
       insert into BQ_ZHTJ00_TEMP00 (id0000,flag00,bmmc00,bmbh00,startd,stopda,startt,stopti)
                   VALUES (Vid0000,'BQ_BRLDTJ',bm.bmmc00,bm.bmbh00,pStartRq,pEndRq00,pStartSj,pEndSj00);
       --在院人数 qsrq<=endrq and (jsrq is null or jsrq>=endrq)
     /*  Update BQ_ZHTJ00_TEMP00 set temp01=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  a.BQH000=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 BQ_ZHTJ00_TEMP00 set temp01=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  a.BQH000=bm.bmbh00
                                           and dqbq00=bm.bmbh00 and yxbz00='1' and  (qszt00 in ('新入','转入')
                                           and QSRQ00<=pStartRq and BQJSRQ>pStartRq
                     or qszt00 in ('变更','换入','迁入') and QSRQ00<=pStartRq and nvl(JSRQ00,'99991231')>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 and id0000=Vid0000;
       --病区收治人数,以住院收费处的为准
       Update BQ_ZHTJ00_TEMP00 set temp02=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  a.BQH000=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 and id0000=Vid0000;

       --病区出院登记人数
       Update BQ_ZHTJ00_TEMP00 set temp03=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='出院' and a.BQH000=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 and id0000=Vid0000;
       --转入人数
       Update BQ_ZHTJ00_TEMP00 set temp04=(SELECT COUNT ( a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  QSZT00 in ('转入') and a.BQH000=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 and id0000=Vid0000;
       --转出人数
       Update BQ_ZHTJ00_TEMP00 set temp05=(SELECT COUNT ( a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='转出' and a.BQH000=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 and id0000=Vid0000;
       --实际开放床位数
       Update BQ_ZHTJ00_TEMP00 set temp06=(SELECT SYCW00 from BM_BMBM00 where BMBH00=bm.bmbh00)
       where  flag00='BQ_BRLDTJ' and bmbh00=bm.bmbh00 and id0000=Vid0000;
       --空床数
       Update BQ_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)) and id0000=Vid0000;
       --加床数
       Update BQ_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)) and id0000=Vid0000;

     end loop;

  else
     for bm in C_YYBQBH loop
       insert into BQ_ZHTJ00_TEMP00 (id0000,flag00,bmmc00,bmbh00,startd,stopda,startt,stopti)
                   VALUES (Vid0000,'BQ_BRLDTJ',bm.bmmc00,bm.bmbh00,pStartRq,pEndRq00,pStartSj,pEndSj00);
       --期初人数 qsrq<=endrq and (jsrq is null or jsrq>=endrq)
       Update BQ_ZHTJ00_TEMP00 set temp01=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  a.BQH000=bm.bmbh00
                                          and dqbq00=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 and id0000=Vid0000;
       --病区收治人数,以住院收费处的为准
       Update BQ_ZHTJ00_TEMP00 set temp02=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  a.BQH000=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 and id0000=Vid0000;
       --病区出院登记人数
       Update BQ_ZHTJ00_TEMP00 set temp03=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='出院' and a.BQH000=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 and id0000=Vid0000;
       --转入人数
       Update BQ_ZHTJ00_TEMP00 set temp04=(SELECT COUNT ( a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  QSZT00='转入' and a.BQH000=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 and id0000=Vid0000;
       --转出人数
       Update BQ_ZHTJ00_TEMP00 set temp05=(SELECT COUNT ( a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  JSZT00='转出' and a.BQH000=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 and id0000=Vid0000;
       --期末人数 qsrq<=endrq and (jsrq is null or jsrq>=endrq)
       Update BQ_ZHTJ00_TEMP00 set temp06=(SELECT COUNT (DISTINCT a.ZYID00) FROM BQ_BRLDXX a,zy_brxxb0 b
                                           WHERE  a.BQH000=bm.bmbh00
                                          and dqbq00=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 and id0000=Vid0000;
     end loop;

  end if;
  delete BQ_ZHTJ00_TEMP00 where  flag00='BQ_BRLDTJ' and temp01=0 and temp02=0 and temp03=0 and temp04=0 and temp05=0 and temp06=0
         and exists (select 1 from BM_BMBM00 where CZBZ00='2' and BMBH00=BQ_ZHTJ00_TEMP00.BMBH00) and id0000=Vid0000;
  PFLAG00:='BQ_BRLDTJ';
  PID0000:=Vid0000;
  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;