create or replace procedure SP_ZY_XZDQKS
(pQSRQ00 varchar2,
 PJZRQ00 varchar2,
 pCOMMIT varchar2
)
as
-- Person      Date        Comments
-- zhr         2011.08.24  create
-- qks         2017.09.09  当KDBQ00值为0时,不对BRDQKS进行修正; for SSMZ9-20170908-001
-- qks         2025.03.06  第1个异常数据改成从流动信息取;
begin
  --KDKS属于住院临床科室的,直接等于临床科室
  update ZY_FYMX00 a set BRDQKS=nvl((select KDKS00 from bm_bmbm00 where bmxz00='0' and bmbh00=KDKS00 ),0)
           where nvl(BRDQKS,0)=0 and czrq00 between pQSRQ00 and PJZRQ00 and
                 exists (select 1 from bm_bmbm00 where bmxz00='0' and bmbh00=KDKS00);

  --KDKS不属于住院临床科室的，根据流动信息表修改BRDQKS
       update ZY_FYMX00 a set BRDQKS= (select SF_ZY_BRDQKS(b.ZYID00,a.KDBQ00,b.CZRQ00,b.CZSJ00) from ZY_BRFY00 b where a.DJH000=b.DJH000)
           where nvl(BRDQKS,0)=0 and  CZRQ00 between pQSRQ00 and PJZRQ00 ;

  --取消住院病人
  update ZY_FYMX00 a set BRDQKS=(select DQKS00 from ZY_BRXXB0 b,ZY_BRFY00 c where c.DJH000=a.DJH000 and c.ZYID00=b.ZYID00 and b.BRZT00 in ('9','1') and rownum=1)
           where nvl(BRDQKS,0)=0 and czrq00 between pQSRQ00 and PJZRQ00 and exists
           (select 1 from ZY_BRXXB0 b,ZY_BRFY00 c where c.DJH000=a.DJH000 and c.ZYID00=b.ZYID00 and b.BRZT00 in ('9','1'));

  --异常数据
  --update ZY_FYMX00 a set BRDQKS=(select ksh000 from bm_bqks00 where bqh000=kdbq00 and rownum=1)
  
  update ZY_FYMX00 a set BRDQKS=(select SF_ZY_BRDQKS(b.ZYID00,a.KDBQ00,b.CZRQ00,b.CZSJ00) from ZY_BRFY00 b where a.DJH000=b.DJH000)
           where czrq00 between pQSRQ00 and PJZRQ00 and
                 not exists (select 1 from bm_bqks00 where bqh000=kdbq00 and ksh000=brdqks)
                 and kdbq00<>0;
                 
  --异常数据
  update ZY_FYMX00 a set BRDQKS=(select ksh000 from bm_bqks00 where bqh000=kdbq00 and rownum=1)
           where nvl(BRDQKS,0)=0 and czrq00 between pQSRQ00 and PJZRQ00;
  if pCOMMIT='Y' then
     commit;
  end if;
end;
/