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 117 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 bmmc00,sum(mzrc00) mzrc00,sum(zycrs0) zycrs','开始时间','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 bmmc00,yszmc0,sum(cyrs00) cyrs00,sum(zycrs0','结束时间','0',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 ssksmc, yszmc0, SUM (cyrs00) cyrs00, SUM (zycrs0) zycrs0,SUM (ssts00) ssts00, DECODE(NVL(SUM(zje000),0),0,100,ROUND (SUM(ypje00) / SUM(zje000)*100, 2)) || ''%'' ypbl00, DECODE(NVL(SUM(cyrs00),0),0,100,ROUND (SUM(ylywsl) / SUM(cyrs00),2)) cypjfy, SUM (yjjcsl) yjjcsl, SUM (ylywsl) ylywsl,sum(RYRS00) RYRS00,sum(ZRRS00) ZRRS00,sum(CYSJTS) CYSJTS ,round(sum(CYSJTS)/decode(SUM(cyrs00),0,1,sum(cyrs00)),2) CYPJTS FROM (select SSKSMC,YSZMC0,sum(decode(ZT0000,''出院'',1,0)) CYRS00,0 zycrs0,0 ssts00,0 ypbl00, 0 cypjfy, 0 yjjcsl, 0 ylywsl, 0 ypje00,0 zje000,sum(decode(ZT0000,''入院'',1,0)) RYRS00,sum(decode(ZT0000,''转入'',1,0)) ZRRS00 ,SUM (decode(a.zt0000,''出院'',sf_zy_calcrs(a.ryrq00,a.RQ0000,a.zt0000,a.ryrq00),0)) CYSJTS from VW_ZY_YSZCRYRSTJ a where a.rq0000 BETWEEN :开始时间 and :结束时间 GROUP BY ssksmc, yszmc0 UNION ALL SELECT b.ssksmc, b.yszmc0, 0 cyrs00 , SUM (sf_zy_calcrs(decode(sign(a.qsrq00-:开始时间),1,a.qsrq00,:开始时间),nvl(a.jsrq00,:结束时间),a.jszt00,c.ryrq00)) zycrs0 ,0 ssts00 , 0 ypbl00, 0 cypjfy, 0 yjjcsl, 0 ylywsl, 0 ypje00,0 zje000,0 RYRS00,0 ZRRS00 ,0 CYSJTS FROM bq_brldxx a, zs_ysz000 b, zy_brxxb0 c WHERE a.yszid0 = b.yszid0 AND a.yszid0 <> 0 AND a.yxbz00 = ''1'' AND b.yszzt0 <> 3 AND a.zyid00 = c.zyid00 AND a.qsrq00<:结束时间 and nvl(a.JSRQ00,''20991231'')>:开始时间 GROUP BY b.ssksmc, b.yszmc0 UNION ALL SELECT b.ssksmc, b.yszmc0, 0 cyrs00, 0 zycrs0,COUNT (d.zyid00) ssts00, 0 ypbl00, 0 cypjfy, 0 yjjcsl,0 ylywsl, 0 ypje00, 0 zje000,0 RYRS00,0 ZRRS00,0 CYSJTS FROM bq_brldxx a, zs_ysz000 b, ss_yw0000 c, zy_brxxb0 d WHERE a.yszid0 = b.yszid0 AND a.yszid0 <> 0 AND a.jszt00 = ''出院'' AND a.yxbz00 = ''1'' AND b.yszzt0 <> 3 AND a.zyid00 = d.zyid00 AND d.brid00 = c.brid00 AND c.ssqsrq BETWEEN :开始时间 and :结束时间 GROUP BY b.ssksmc, b.yszmc0 UNION ALL SELECT b.ssksmc, b.yszmc0, 0 cyrs00, 0 zycrs0, 0 ssts00, 0 ypbl00, 0 cypjfy, (select sum(hjje00) from bm_yysfxm e,zy_fymx00 f where e.sfxmid = f.xmbh00 and f.djh000=c.djh000 and zyfpid in(''8'',''9'')) yjjcsl, SUM (c.hjje00) ylywsl, 0 ypje00,0 zje000,0 RYRS00,0 ZRRS00,0 CYSJTS FROM bq_brldxx a, zs_ysz000 b, zy_brfy00 c WHERE a.yszid0 = b.yszid0 AND a.yszid0 <> 0 AND a.jszt00 = ''出院'' AND a.yxbz00 = ''1'' AND b.yszzt0 <> 3 AND a.zyid00 = c.zyid00 AND c.czrq00 BETWEEN :开始时间 and :结束时间 GROUP BY b.ssksmc, b.yszmc0, c.djh000 UNION ALL SELECT b.ssksmc, b.yszmc0, 0 cyrs00, 0 zycrs0, 0 ssts00, 0 ypbl00, 0 cypjfy, 0 yjjcsl, 0 ylywsl, SUM (ypf000) ypje00, SUM (zfy000) zje000,0 RYFS00,0 ZRRS00,0 CYSJTS FROM bq_brldxx a, zs_ysz000 b, zh_cybrbb c WHERE a.yszid0 = b.yszid0 AND a.yszid0 <> 0 AND a.jszt00 = ''出院'' AND a.yxbz00 = ''1'' AND b.yszzt0 <> 3 AND a.zyid00 = c.zyid00 AND a.jsrq00 BETWEEN :开始时间 and :结束时间 GROUP BY b.ssksmc, b.yszmc0) GROUP BY ssksmc, yszmc0 '); end ; ----end---- update BB_FR3BB0 SET BBDATA=:bbdata where BBID00=(select BBID00 from TEMP_BBID00)