declare
 lvbbid00 BB_FR3BB0.BBID00%TYPE;
 lvcount0 number(5);
 lvxtdm00 XT_XTMK00.XTDM00%TYPE;
 lvbbname BB_FR3BB0.BBNAME%TYPE;
 lvmblx00 BB_FR3BB0.MBLX00%TYPE;
 lvsqllx0 BB_FR3BB0.SQLLX0%TYPE;
 lvcjczy0 BB_FR3BB0.CJCZY0%TYPE;
 lvbbbh00 BB_FR3BB0.BBBH00%TYPE;
 lvzt0000 BB_FR3BB0.ZT0000%TYPE;
 lvbblxmc BB_MBLX00.MBMC00%TYPE;
 lvyclpro BB_FR3BB0.YCLPRO%TYPE;
Begin
 select 276 into lvbbid00 from dual ;
 select '0' into lvxtdm00 from dual ;
 select '病案室_病室工作日志' into lvbbname from dual;
 select 0 into lvsqllx0 from dual ;
 select '管理员' into lvcjczy0 from dual ;
 select '50002' into lvbbbh00 from dual ;
 select '0' into lvzt0000 from dual ;
 select '住院报表' into lvbblxmc from dual ;
 select '' into lvyclpro from dual ;
 select count(*) into lvcount0 from BB_FR3BB0 WHERE BBNAME=lvbbname;
 if lvcount0>0 then 
   select BBID00 INTO lvbbid00 from BB_FR3BB0 WHERE  BBNAME=lvbbname; 
 else 
   select SQ_BB_FR3BB0_BBID00.nextval into lvbbid00 from dual;
 end if;
 select count(*) into lvcount0 from BB_MBLX00 where MBMC00=lvbblxmc ;
 if lvcount0>0 then 
   select MBLX00 INTO lvmblx00 from BB_MBLX00 WHERE MBMC00=lvbblxmc  ; 
 else 
   select SQ_BB_MBLX00_MBLX00.nextval into lvmblx00 from dual;
   Insert into BB_MBLX00 (MBLX00, MBBH00, MBMC00)
     values(lvmblx00,'000000',lvbblxmc); 
 end if;
 delete TEMP_BBID00;
 insert into TEMP_BBID00(BBID00) values(lvbbid00);
 delete BB_FR3BB0 where BBID00=lvbbid00;
 insert into BB_FR3BB0(BBID00,BBNAME,MBLX00,CJCZY0,CRDATE,SQLLX0,BBBH00,ZT0000,YCLPRO)
 values(lvbbid00,lvbbname,lvmblx00,lvcjczy0,to_char(sysdate,'YYYYMMDD'),lvsqllx0,lvbbbh00,lvzt0000,lvyclpro);
 delete XT_XTMK00 where XTDM00=lvxtdm00 and trim(MKDM00)=trim(lvxtdm00)||'@'||to_char(lvbbid00);  
 insert into XT_XTMK00(MKDM00,MKNBMC,MKWBMC,XTDM00)
 Values(trim(lvxtdm00)||'@'||to_char(lvbbid00),'REPORT',lvbbname,lvxtdm00);
-------BB_CSMX00--------
delete BB_CSMX00 where BBID00=lvbbid00;
 insert into BB_CSMX00 (BBID00, XH0000, CSMC00, CSZLX0, CSBJLX, CSZSQL,NAME00,CSWBMC,SFXTCS,WBXH00) 
 values(lvbbid00,SQ_BB_CSMX00_XH0000.nextval,'病区',1,4,' select bmbh00,bmmc00 from bm_bmbm00 where bmxz00=''9'' and flag00=''1'' and czbz00<>''2'' order by pxxh00','','病区','0',1);
 insert into BB_CSMX00 (BBID00, XH0000, CSMC00, CSZLX0, CSBJLX, CSZSQL,NAME00,CSWBMC,SFXTCS,WBXH00) 
 values(lvbbid00,SQ_BB_CSMX00_XH0000.nextval,'日期',1,3,'','select  distinct '入院及他科转入' flag00,'入院' ty','日期','0',2);
-------BB_YCLPCS---------
 delete BB_YCLPCS where BBID00=lvbbid00; 
-------BB_SQL000--------
delete BB_SQL000 where BBID00=lvbbid00;
 insert into BB_SQL000(BBID00,XH0000,SQL000) values (lvbbid00,SQ_BB_SQL000_XH0000.nextval,'select  distinct 1 order0,''入院及他科转入'' flag00,''入院'' type00,'' '' effect,a.zyid00,b.xm0000||''(''||TRIM(CH0000)||'')'' XM0000

from BQ_BRLDXX a,zy_brxxb0 b 

WHERE a.zyid00=b.zyid00 and  (a.KSH000=:病区 or a.KSH000 in (select KSH000 from bm_bqks00 where BQH000=:病区))  and a.yxbz00=''1'' and a.qszt00=''新入'' 

and a.QSRQ00 =:日期  

union all

select  distinct 2 order0,''入院及他科转入'' flag00,''转入'' type00,'' '' effect,a.zyid00,b.xm0000||''(''||(SELECT TRIM(CH0000)

 FROM BQ_BRLDXX WHERE ZYID00=A.ZYID00 AND JSRQ00=A.QSRQ00 AND JSSJ00=A.QSSJ00 AND ROWNUM=1 )||'')'' XM0000

from BQ_BRLDXX a,zy_brxxb0 b

WHERE a.zyid00=b.zyid00 and  (a.KSH000=:病区 or a.KSH000 in (select KSH000 from bm_bqks00 where BQH000=:病区))  and a.yxbz00=''1'' and a.qszt00=''转入'' 

and a.QSRQ00 =:日期  

union all

select  distinct 3 order0,''出院及转往他科'' flag00,''转出'' type00,'' '' effect,a.zyid00,b.xm0000||''(''||(SELECT TRIM(CH0000) FROM BQ_BRLDXX WHERE ZYID00=A.ZYID00 AND QSRQ00=A.JSRQ00 AND QSSJ00=A.JSSJ00 AND ROWNUM=1 )||'')'' XM0000

from BQ_BRLDXX a,zy_brxxb0 b

WHERE a.zyid00=b.zyid00 and  (a.KSH000=:病区 or a.KSH000 in (select KSH000 from bm_bqks00 where BQH000=:病区))  and a.YXBZ00=''1''  and a.JSZT00=''转出''

and JSRQ00=:日期 

union all

select  distinct 4 order0,''出院及转往他科'' flag00,''出院'' type00,e.mc0000 effect,a.zyid00,b.xm0000||''(''||TRIM(CH0000)||'')'' XM0000

from BQ_BRLDXX a,zy_brxxb0 b,(select d.mc0000,zyid00 from bq_brzdxx c,xt_zlxg00 d where c.zlxg00=d.dm0000 and zdlb00=''3'') e

where a.zyid00=b.zyid00 and  (a.KSH000=:病区 or a.KSH000 in (select KSH000 from bm_bqks00 where BQH000=:病区))  and a.YXBZ00=''1''  and a.JSZT00=''出院''

and a.JSRQ00=:日期 and  a.ZYID00=e.ZYID00(+)

union all

select 5 order0,''其中'' flag00,''总计'' type00,''原在院数'' effect,0,to_char(count(distinct a.ZYID00))

from BQ_BRLDXX a,zy_brxxb0 b WHERE a.zyid00=b.zyid00 and (a.KSH000=:病区 or a.KSH000 in (select KSH000 from bm_bqks00 where BQH000=:病区)) and a.yxbz00=''1'' and a.qszt00 in (''新入'',''转入'') and a.QSRQ00<=to_char(to_date(:日期,''YYYYMMDD'')-1,''YYYYMMDD'') and a.BQJSRQ>to_char(to_date(:日期,''YYYYMMDD'')-1,''YYYYMMDD'')

union all

select 6 order0,''其中'' flag00,''总计'' type00,''入院及他科转入'' effect,0,to_char(count(distinct a.ZYID00))

from BQ_BRLDXX a,zy_brxxb0 b 

WHERE a.zyid00=b.zyid00 and  (a.KSH000=:病区 or a.KSH000 in (select KSH000 from bm_bqks00 where BQH000=:病区))  and a.yxbz00=''1'' and a.qszt00 in(''新入'' ,''转入'')

and a.QSRQ00 =:日期  

union all

select 7 order0,''其中'' flag00,''总计'' type00,''出院及转往他科'' effect,0,to_char(count(distinct a.ZYID00))

from BQ_BRLDXX a,zy_brxxb0 b

WHERE a.zyid00=b.zyid00 and  (a.KSH000=:病区 or a.KSH000 in (select KSH000 from bm_bqks00 where BQH000=:病区))  and a.YXBZ00=''1''  and a.JSZT00 in(''转出'',''出院'')

and JSRQ00=:日期 

union all

select 8 order0,''其中'' flag00,''总计'' type00,''现在院数'' effect,0,to_char(count(distinct a.ZYID00))

from BQ_BRLDXX a,zy_brxxb0 b WHERE a.zyid00=b.zyid00 and (a.KSH000=:病区 or a.KSH000 in (select KSH000 from bm_bqks00 where BQH000=:病区)) and a.yxbz00=''1'' and a.qszt00 in (''新入'',''转入'') and a.QSRQ00<=:日期 and a.BQJSRQ>:日期

order by order0,flag00 desc,type00 desc,effect










');
end ;
----end----
update BB_FR3BB0 SET BBDATA=:bbdata where  BBID00=(select BBID00 from TEMP_BBID00)