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 127 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 '' 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 bmbh00,bmmc00,yshsbh,yshsxm,sum(hj0000),sum','开始时间','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 bmbh00,bmmc00,yshsbh,yshsxm,sum(hj0000),sum','结束时间','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 bmbh00,bmmc00,zdysbh yshsbh,yshsxm,count(1) hj0000,

       sum(decode(ssdjmc,''特大'',1,0)) tdss00,

       sum(decode(ssdjmc,''特大'',nvl(sfzd00,0),0)) tdsszd,

       sum(decode(ssdjmc,''大'',1,0)) dss000,

       sum(decode(ssdjmc,''大'',nvl(sfzd00,0),0)) dsszd0,

       sum(decode(ssdjmc,''中'',1,0)) zss000,

       sum(decode(ssdjmc,''中'',nvl(sfzd00,0),0)) zsszd0,

       sum(decode(ssdjmc,''小'',1,0)) xss000 from(

  select c.ssdh00,(select yshsbh from ss_yshsxx e,bm_ygbm00 f where e.yshsbh=f.ygbh00 and f.ygxz00=''1'' and ssdh00=c.ssdh00 and rownum=1) zdysbh,

       (select yshsxm from ss_yshsxx e,bm_ygbm00 f where e.yshsbh=f.ygbh00 and f.ygxz00=''1'' and ssdh00=c.ssdh00 and yshslb = ''主刀医生'' and rownum=1) yshsxm,

       (select bmbh00 from ss_yshsxx e,bm_ygbm00 f where e.yshsbh=f.ygbh00 and f.ygxz00=''1'' and ssdh00=c.ssdh00 and rownum=1) bmbh00,

       (select bmmc00 from ss_yshsxx e,bm_ygbm00 f,bm_bmbm00 g where f.bmbh00=g.bmbh00 and e.yshsbh=f.ygbh00 and f.ygxz00=''1'' and ssdh00=c.ssdh00 and rownum=1) bmmc00,

       i.ssdjmc,

       (select 1 from ss_yshsxx e,bm_ygbm00 f where e.yshsbh=f.ygbh00 and f.ygxz00=''1'' and ssdh00=c.ssdh00 and yshslb = ''主刀医生'' and rownum=1) sfzd00

       from vw_ss_ywjj00 b, ss_ssyjd0 c, vw_ss_yw0000 d,bm_zlzd00 h, bm_ssdj00 i 

         where b.YJDJH0=c.YJDJH0 and c.ssdh00=d.ssdh00 

           and h.ssdjbh=i.ssdjbh(+) and h.zlxmid=b.zlxmid

           and d.ssqsrq BETWEEN :开始时间 and :结束时间 

           and (not exists (select ''y'' from yj_yw0000 a where a.cxdjh0=b.yjdjh0))

           and ((b.cxdjh0=0) or (b.cxdjh0 is null)) 				

           and b.kdksbh not in (select bmbh00 from vw_bm_ssmzbm)

           and b.XMZT00 in (''2'',''4'',''3'')

   )group by bmbh00,bmmc00,zdysbh,yshsxm




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