CREATE OR REPLACE PROCEDURE SP_BQ_KSLDXX
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- zhanghr     2011.10.30  create
-- dsm         2016.01.15  写入BQ_KSLDXX.BQH000 for  BQ-20151230-001
as
  Vksh000    number(5);
  Vzyid00    number(10);
  Vyxbz00    number(1);
  cursor c_br is
    select ZYID00 from BQ_BRLDXX where KSJSRQ='20991231' and YXBZ00 in ('1','3')  and qsrq00>=to_char(SYSDATE-365,'yyyymmdd')
     AND ZYID00 IN (SELECT ZYID00 FROM ZY_BRXXB0) order by ZYID00;

  cursor c_ld is
    select ZYID00,BQH000,KSH000,QSRQ00,QSSJ00,JSRQ00,QSZT00,JSZT00,YXBZ00 from BQ_BRLDXX
       where ZYID00=Vzyid00 and YXBZ00 in ('1')  and KSJSRQ='20991231' and qsrq00>=to_char(SYSDATE-365,'yyyymmdd') order by ZYID00,QSRQ00,QSSJ00;
   cursor c_cy is
    select ZYID00,BQH000,KSH000,QSRQ00,QSSJ00,JSRQ00,QSZT00,JSZT00,YXBZ00 from BQ_BRLDXX where  YXBZ00 in ('1','3') and jsrq00>=to_char(SYSDATE-2,'yyyymmdd') and JSZT00='出院'
     AND ZYID00 IN (SELECT ZYID00 FROM ZY_BRXXB0) order by ZYID00;--wwy处理出院又收进来其他日期再出院，病区流动结束日期和科室流动结束日期不不一致问题20231019
     cursor c_wx is
    select ZYID00,BQH000,KSH000,QSRQ00,QSSJ00,JSRQ00,QSZT00,JSZT00,YXBZ00 from BQ_BRLDXX x where  x.YXBZ00 in ('0') and x.jsrq00=x.qsrq00 and x.JSZT00='出院'
    and not EXISTS (select 1 from BQ_BRLDXX w where w.zyid00=x.zyid00 and w.JSZT00='出院' and w.YXBZ00 in ('1')) and x.jsrq00>=to_char(SYSDATE-1,'yyyymmdd')
     AND ZYID00 IN (SELECT ZYID00 FROM ZY_BRXXB0) order by ZYID00;--wwy处理设置无效数据同步到科室流动信息20231019

begin
  for br in c_br loop
      Vzyid00:=br.ZYID00;
      select nvl(max(KSH000),0) into Vksh000 from BQ_KSLDXX where ZYID00=Vzyid00 and LSID00=(select max(LSID00) from BQ_KSLDXX where ZYID00=Vzyid00);
      for ld in c_ld loop
          --没有任何记录，新入院
          if Vksh000=0 then
             insert into BQ_KSLDXX (LSID00,ZYID00,KSH000,QSRQ00,QSZT00,YXBZ00,BQH000)
                    values (SQ_BQ_KSLDXX_LSID00.nextval,Vzyid00,ld.KSH000,ld.QSRQ00,'1',ld.YXBZ00,ld.BQH000);
          --已经有记录，科室不相等，变换科室
          elsif Vksh000>0 and Vksh000<>ld.KSH000 then
             --转出
             update BQ_KSLDXX  set JSRQ00=ld.QSRQ00,JSZT00='4' where ZYID00=Vzyid00 and
                    LSID00=(select max(LSID00) from BQ_KSLDXX where ZYID00=Vzyid00 );
             update BQ_BRLDXX set KSJSRQ=ld.QSRQ00 where ZYID00=Vzyid00 and QSRQ00<=ld.QSRQ00 and QSRQ00||QSSJ00<ld.QSRQ00||ld.QSSJ00 and KSJSRQ='20991231';
             --转入
             insert into BQ_KSLDXX (LSID00,ZYID00,KSH000,QSRQ00,QSZT00,YXBZ00,BQH000)
                         values(SQ_BQ_KSLDXX_LSID00.nextval,Vzyid00,ld.KSH000,ld.QSRQ00,'3',ld.YXBZ00,ld.BQH000);
          end if;
          if ld.JSZT00='出院' then
             update BQ_KSLDXX set JSRQ00=ld.JSRQ00,JSZT00='2' where ZYID00=Vzyid00 and JSZT00 is null;
             update BQ_BRLDXX set KSJSRQ=ld.JSRQ00 where ZYID00=Vzyid00 and QSRQ00<=ld.QSRQ00 and QSRQ00||QSSJ00<=ld.QSRQ00||ld.QSSJ00 and KSJSRQ='20991231';
          end if;
          Vksh000:=ld.KSH000;
      end loop;
      commit;
  end loop;
  for cy in c_cy loop--wwy处理出院又收进来其他日期再出院，病区流动结束日期和科室流动结束日期不不一致问题20231019
      if cy.JSZT00='出院'  then
        update BQ_KSLDXX set JSRQ00=cy.JSRQ00 where ZYID00=cy.ZYID00  and jsrq00<>cy.JSRQ00 and jszt00='2' and LSID00=(select max(LSID00) from BQ_KSLDXX where ZYID00=cy.ZYID00 ) ;
      end if;
  end loop;
    commit;
  for wx in c_wx loop--wwy处理设置无效数据同步到科室流动信息20231019
     if wx.JSZT00='出院'  and wx.YXBZ00='0' then
       update BQ_KSLDXX set YXBZ00='0' where ZYID00=wx.ZYID00  and QSZT00='1' and qsrq00=wx.qsrq00 ;--and jszt00=2
       --LSID00=(select min(LSID00) from BQ_KSLDXX where ZYID00=wx.ZYID00 )
     end if;
  end loop;
     commit;
EXCEPTION
  when others then
  raise_application_error(-20002,substrb(nvl(SQLERRM, '')||'！*'||'执行过程SP_BQ_KSLDXX出错',1,220));
end;