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 40 into lvbbid00 from dual ;
 select '0' into lvxtdm00 from dual ;
 select '医技科室项目安排回传单' into lvbbname from dual;
 select 0 into lvsqllx0 from dual ;
 select '0211' into lvcjczy0 from dual ;
 select '' 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,3,'','Select rownum XH0000,B.* from (select apnum0,aprxm0,mzzybz,brid00,yjdjh0,yjfzh0,zyhghh,brxm00,yexm00,zlxmjc,xb0000,csrq00,apsj00,lrxm00, kdysxm,kdksmc,aprq00,jzbz00,bwmc00,sm0000,zdmc00,apbz00,jcqzy0,brnl00,cwh000,yysj00,zje000,sl0000,ztmc00,zxsl00,KDRQ02,kdbq00,KDBQMC from vw_yj_yw0000_wzx2 b where xmzt00 in (''0'',''1'',''2'')  and mzzybz=''1'' and aprxm0 is not null and (YJKSBH in (select bmbh00 from bm_bmbm00 where bmbh00 in (616,617,204,205,206,212,213,258,266,282,304,305,291,292,441,285,295,623,624,642,644,675,690)) or (Zlxmid in  (Select Zlxmid from vw_XT_xmfwfx where jsxmks=204)  and kdksbh in (select Sqxmks from vw_xt_xmfwfx where jsxmks=204)))  and  (not  exists  ( select ''y'' from yj_yw0000 a where a.cxdjh0=b.yjdjh0 and a.zyhghh=b.zyhghh))   and ((cxdjh0=0) or (cxdjh0 is null)) and kdbq00=:XT_BMBM and aprq00 is not null  and kdrq00 between :开始日期 and :结束日期   order by yjdjh0)B','开始日期','0',2);
 insert into BB_CSMX00 (BBID00, XH0000, CSMC00, CSZLX0, CSBJLX, CSZSQL,NAME00,CSWBMC,SFXTCS,WBXH00) 
 values(lvbbid00,SQ_BB_CSMX00_XH0000.nextval,'结束日期',1,3,'','Select rownum XH0000,B.* from (select apnum0,aprxm0,mzzybz,brid00,yjdjh0,yjfzh0,zyhghh,brxm00,yexm00,zlxmjc,xb0000,csrq00,apsj00,lrxm00, kdysxm,kdksmc,aprq00,jzbz00,bwmc00,sm0000,zdmc00,apbz00,jcqzy0,brnl00,cwh000,yysj00,zje000,sl0000,ztmc00,zxsl00,KDRQ02,kdbq00,KDBQMC from vw_yj_yw0000_wzx2 b where xmzt00 in (''0'',''1'',''2'')  and mzzybz=''1'' and aprxm0 is not null and (YJKSBH in (select bmbh00 from bm_bmbm00 where bmbh00 in (616,617,204,205,206,212,213,258,266,282,304,305,291,292,441,285,295,623,624,642,644,675,690)) or (Zlxmid in  (Select Zlxmid from vw_XT_xmfwfx where jsxmks=204)  and kdksbh in (select Sqxmks from vw_xt_xmfwfx where jsxmks=204)))  and  (not  exists  ( select ''y'' from yj_yw0000 a where a.cxdjh0=b.yjdjh0 and a.zyhghh=b.zyhghh))   and ((cxdjh0=0) or (cxdjh0 is null)) and kdbq00=:XT_BMBM and aprq00 is not null  and kdrq00 between :开始日期 and :结束日期   order by yjdjh0)B','结束日期','0',3);
 insert into BB_CSMX00 (BBID00, XH0000, CSMC00, CSZLX0, CSBJLX, CSZSQL,NAME00,CSWBMC,SFXTCS,WBXH00) 
 values(lvbbid00,SQ_BB_CSMX00_XH0000.nextval,'XT_BMBM',1,1,'userDepid','Select rownum XH0000,B.* from (select apnum0,aprxm0,mzzybz,brid00,yjdjh0,yjfzh0,zyhghh,brxm00,yexm00,zlxmjc,xb0000,csrq00,apsj00,lrxm00, kdysxm,kdksmc,aprq00,jzbz00,bwmc00,sm0000,zdmc00,apbz00,jcqzy0,brnl00,cwh000,yysj00,zje000,sl0000,ztmc00,zxsl00,KDRQ02,kdbq00,KDBQMC from vw_yj_yw0000_wzx2 b where xmzt00 in (''0'',''1'',''2'')  and mzzybz=''1'' and aprxm0 is not null and (YJKSBH in (select bmbh00 from bm_bmbm00 where bmbh00 in (616,617,204,205,206,212,213,258,266,282,304,305,291,292,441,285,295,623,624,642,644,675,690)) or (Zlxmid in  (Select Zlxmid from vw_XT_xmfwfx where jsxmks=204)  and kdksbh in (select Sqxmks from vw_xt_xmfwfx where jsxmks=204)))  and  (not  exists  ( select ''y'' from yj_yw0000 a where a.cxdjh0=b.yjdjh0 and a.zyhghh=b.zyhghh))   and ((cxdjh0=0) or (cxdjh0 is null)) and kdbq00=:XT_BMBM and aprq00 is not null  and kdrq00 between :开始日期 and :结束日期   order by yjdjh0)B','XT_BMBM','1',4);
-------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 rownum XH0000,B.* from (select zxksmc,apnum0,aprxm0,mzzybz,brid00,yjdjh0,yjfzh0,zyhghh,brxm00,yexm00,zlxmjc,xb0000,csrq00,apsj00,lrxm00, kdysxm,kdksmc,aprq00,jzbz00,bwmc00,sm0000,zdmc00,apbz00,jcqzy0,brnl00,cwh000,yysj00,zje000,sl0000,ztmc00,zxsl00,KDRQ02,kdbq00,KDBQMC from vw_yj_yw0000_wzx2 b where xmzt00 in (''0'',''1'',''2'',''3'')  and mzzybz=''1'' and aprxm0 is not null and (YJKSBH in (select bmbh00 from bm_bmbm00 where bmbh00 in (616,617,204,205,206,212,213,258,266,282,304,305,291,292,441,285,295,623,624,642,644,675,690)) or (Zlxmid in  (Select Zlxmid from vw_XT_xmfwfx where jsxmks=204)  and kdksbh in (select Sqxmks from vw_xt_xmfwfx where jsxmks=204)))  and  (not  exists  ( select ''y'' from yj_yw0000 a where a.cxdjh0=b.yjdjh0 and a.zyhghh=b.zyhghh))   and ((cxdjh0=0) or (cxdjh0 is null)) and kdbq00=:XT_BMBM and aprq00 is not null  and kdrq00 between :开始日期 and :结束日期  order by yjdjh0)B


























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