prompt 197、 莆田涵江医院门诊住院科室工作日报 SP_SF_MZZYKSGZRB create or replace procedure SP_SF_MZZYKSGZRB ( as_TJRQ00 in char, --开始日期 as_JSRQ00 in char, --结束日期 ad_ID0000 out number, --统计报表对应的ID as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar --系统提示的错误信息 ) as --MODIFY HISTORY --date person comments --2011.04.29 zhangwz create; by MZSF-20110429-001. --2011.06.15 zhangyc modify by MZSF-20110614-001 --2011.06.27 zhangyc modify by MZSF-20110627-004.按病区名称排序 --2011.07.05 zhangyc modify by MZSF-20110630-001 按bm_bmbm00.PXXH00 排序 --2011.07.11 zhangyc modify by MZSF-20110707-001 门诊统计BM_GHLBB0.SFTJ00='Y'数据 --2011.07.28 zhangyc modify by MZSF-20110726-001 --2011.08.18 laijg modify by MZSF-20110815-002 增加时间段统计 --2011.09.15 laijg modify by MZSF-20110915-003 病人数、一级病人数需要累加 --2012.01.18 jinfl modify by MZSF-20120116-004 处方人数按药房统计 --2012.01.18 jinfl modify by MZSF-20120118-001 增加特级护理病人 --2012.02.09 liuj modify by MZSF-20120208-001 增加重症监护病人（危重病人） --2014.07.16 zhangyc modify by MZSF-20140710-001 增加NICU统计 lv_count0 number(5) ; --计数器 lv_count1 number(5) ; --计数器 lv_XYF000 number(5) ; lv_ZYF000 number(5) ; lv_PXXH00 BM_GHKSTJ.GHKS88%TYPE ; --排序序号 lv_KSH000 BM_BMBM00.BMBH00%TYPE ; --部门编号 lv_BMMC00 BM_BMBM00.BMMC00%TYPE ; --部门编号 lv_YJHLXMBH BM_ZLZD00.ZLXMID%TYPE ; --一级护理项目编号 lv_TJHLXMBH BM_ZLZD00.ZLXMID%TYPE ; --特级护理项目编号 lv_GHKS11 BM_GHKSTJ.GHKS11%TYPE ; lv_BEIZH2 BM_GHKSTJ.BEIZH2%TYPE ; lv_GHKS08 BM_GHKSTJ.GHKS08%TYPE ; lv_GHKS09 BM_GHKSTJ.GHKS09%TYPE ; lv_GHKS10 BM_GHKSTJ.GHKS09%TYPE ; lv_BEIZH3 BM_GHKSTJ.BEIZH2%TYPE ; -- lv_BQH000 BQ_BRLDXX.bqh000%type ; lv_rq0000 BQ_BRLDXX.QSRQ00%type ; -- lv_TEMP00 date; lv_count2 number(5) ; --计数器 cursor CUR_KSZYBQ is --科室在院病人累加 select a.BQH000,F.BMMC00,F.PXXH00 from BQ_BRLDXX a,bm_bmbm00 F where a.BQH000=F.bmbh00 and a.yxbz00='1' and a.qszt00 in ('新入','转入') group by A.BQH000,f.BMMC00,F.PXXH00 order by F.PXXH00; cursor CUR_KSZYBR is ---科室在院病人 select count(*) as SL0000 from BQ_BRLDXX a,bm_bmbm00 F where a.BQH000=F.bmbh00 and a.yxbz00='1' and a.qszt00 in ('新入','转入') and a.QSRQ00<=lv_rq0000 and A.BQJSRQ>lv_rq0000 and A.BQH000=lv_KSH000 order by F.PXXH00; cursor CUR_YJHLBQ is --一级护理病人累加 select a.BQH000,F.BMMC00,F.PXXH00 from BQ_BRLDXX a,BQ_YJYZ00 b,bm_bmbm00 F where a.BQH000=F.bmbh00 and a.yxbz00='1' and a.qszt00 in ('新入','转入') and a.zyid00=b.zyid00 and b.zlxmid=lv_YJHLXMBH and b.yzzt00>='1' group by a.BQH000,F.BMMC00,F.PXXH00 order by F.PXXH00; cursor CUR_YJHLBR is --一级护理病人 select count(a.zyid00) as SL0000 from BQ_BRLDXX a,BQ_YJYZ00 b,bm_bmbm00 F where a.BQH000=F.bmbh00 and a.yxbz00='1' and a.qszt00 in ('新入','转入') and a.QSRQ00<=lv_rq0000 and a.BQJSRQ>lv_rq0000 and a.zyid00=b.zyid00 and b.zlxmid=lv_YJHLXMBH and b.yzzt00>='1' and b.qyrq00<=lv_rq0000 and (b.TZRQ00>lv_rq0000 or b.TZRQ00 is null) and a.BQH000=lv_KSH000 order by F.PXXH00; cursor CUR_NICULJ is --NICU统计累加 select a.BQH000,F.BMMC00,F.PXXH00 from BQ_BRLDXX a,BQ_YJYZ00 b,bm_bmbm00 F where a.BQH000=F.bmbh00 and a.yxbz00='1' and a.qszt00 in ('新入','转入') and a.zyid00=b.zyid00 and (b.zlxmjc like '%新生儿护理常规%' or b.zlxmjc like '%按早产儿护理常规%') and b.yzzt00>='1' and a.BQH000=18 group by a.BQH000,F.BMMC00,F.PXXH00 order by F.PXXH00; cursor CUR_NICU is -- NICU统计 select count(distinct a.zyid00) SL0000 from BQ_BRLDXX a,BQ_YJYZ00 b,bm_bmbm00 F where a.BQH000=F.bmbh00 and a.yxbz00='1' and a.qszt00 in ('新入','转入') and a.QSRQ00<=lv_rq0000 and a.BQJSRQ>lv_rq0000 and a.zyid00=b.zyid00 and (b.zlxmjc like '%新生儿护理常规%' or b.zlxmjc like '%按早产儿护理常规%') and b.yzzt00>='1' and b.qyrq00<=lv_rq0000 and (b.TZRQ00>lv_rq0000 or b.TZRQ00 is null) and a.BQH000=18 order by F.PXXH00; cursor CUR_ZZJHBQ is --重症监护病人累加 select a.BQH000,F.BMMC00,F.PXXH00 from BQ_BRLDXX a,BQ_YJYZ00 b,bm_bmbm00 F where a.BQH000=F.bmbh00 and a.yxbz00='1' and a.qszt00 in ('新入','转入') and a.zyid00=b.zyid00 and b.zlxmjc like'%重症监护%' and b.yzzt00>='1' group by a.BQH000,F.BMMC00,F.PXXH00 order by F.PXXH00; cursor CUR_ZZJHBR is --重症监护病人 select count(a.zyid00) as SL0000 from BQ_BRLDXX a,BQ_YJYZ00 b,bm_bmbm00 F where a.BQH000=F.bmbh00 and a.yxbz00='1' and a.qszt00 in ('新入','转入') and a.QSRQ00<=lv_rq0000 and a.BQJSRQ>lv_rq0000 and a.zyid00=b.zyid00 and b.zlxmjc like'%重症监护%' and b.yzzt00>='1' and b.qyrq00<=lv_rq0000 and (b.TZRQ00>lv_rq0000 or b.TZRQ00 is null) and a.BQH000=lv_KSH000 order by F.PXXH00; cursor CUR_TJHLBQ is --特级护理病人累加 select a.BQH000,F.BMMC00,F.PXXH00 from BQ_BRLDXX a,BQ_YJYZ00 b,bm_bmbm00 F where a.BQH000=F.bmbh00 and a.yxbz00='1' and a.qszt00 in ('新入','转入') and a.zyid00=b.zyid00 and b.zlxmid=lv_TJHLXMBH and b.yzzt00>='1' group by a.BQH000,F.BMMC00,F.PXXH00 order by F.PXXH00; cursor CUR_TJHLBR is --特级护理病人 select count(a.zyid00) as SL0000 from BQ_BRLDXX a,BQ_YJYZ00 b,bm_bmbm00 F where a.BQH000=F.bmbh00 and a.yxbz00='1' and a.qszt00 in ('新入','转入') and a.QSRQ00<=lv_rq0000 and a.BQJSRQ>lv_rq0000 and a.zyid00=b.zyid00 and b.zlxmid=lv_TJHLXMBH and b.yzzt00>='1' and b.qyrq00<=lv_rq0000 and (b.TZRQ00>lv_rq0000 or b.TZRQ00 is null) and a.BQH000=lv_KSH000 order by F.PXXH00; cursor CUR_MZKSGHL is --门诊科室挂号量 select a.GHKS00,B.BMMC00,b.pxxh00,a.SL0000 from (select GHKS00,count(GHID00) as SL0000 from VW_SF_GHLSXX where czrq00>=as_TJRQ00 and czrq00<=as_JSRQ00 and ghlb00 in (select LBBH00 from BM_GHLBB0 where SFTJ00='Y')--modify by MZSF-20110707-001 group by ghks00) a,BM_BMBM00 b where a.ghks00=b.bmbh00 order by b.pxxh00; cursor CUR_QYJCRCS is --全院检查人次数 select JCLBID,sum(SL0000) as SL000 from (select c.JCLBID,sum(b.SL0000) as SL0000 from SF_BRFY00 a,YJ_YW0000 b,BM_ZLZD00 c where a.CZRQ00>=as_TJRQ00 and a.CZRQ00<=as_JSRQ00 and a.DJH000=b.SFDJH0 and b.ZLXMID=c.ZLXMID and c.JCLBID is not null group by c.JCLBID union all select c.JCLBID,sum(b.SL0000) as SL0000 from ZY_BRFY00 a,YJ_YW0000 b,BM_ZLZD00 c where a.CZRQ00>=as_TJRQ00 and a.CZRQ00<=as_JSRQ00 and a.DJH000=b.SFDJH0 and b.ZLXMID=c.ZLXMID and c.JCLBID is not null group by c.JCLBID ) group by JCLBID order by JCLBID; cursor CUR_GXMZSH is Select a.GHKS11,A.BEIZH2,a.GHKS08 from BM_GHKSTJ a where a.ID0000=ad_ID0000 and GHRQ00='99' order by GHKS88; cursor CUR_FZKB00 is Select a.GHKS10,a.GHKS09 from BM_GHKSTJ a where a.ID0000=ad_ID0000 and GHRQ00='999' order by GHKS10; begin --GHKS01 BMBH00,GHKS02 病人数 GHKS03 NICU GHKS04 一级病人 GHKS05 危重病人 GHKS06 死亡 GHKS07 自动出院 GHKS08 门诊挂号数 --GHKS09 检查人次数 GHKS10 检查类别ID XMHJ00排序序号 select SQ_BM_GHKSTJ_ID0000.nextval into ad_ID0000 from dual; begin --select ZLXMID into lv_YJHLXMBH from VW_BQ_HLYZXM where ZLXMJC='一级护理'; ---modify by MZSF-20110707-001 select ZLXMID into lv_YJHLXMBH from BM_ZLZD00 where ZLXMJC='Ⅰ级护理'; exception when others then lv_YJHLXMBH:=0; end; begin select ZLXMID into lv_TJHLXMBH from BM_ZLZD00 where ZLXMJC='特级护理'; exception when others then lv_TJHLXMBH:=0; end; ----查询住院收费数据---- open CUR_KSZYBQ; loop fetch CUR_KSZYBQ into lv_KSH000,lv_BMMC00,lv_PXXH00; exit when CUR_KSZYBQ%notfound; lv_count0:=0; -- lv_TEMP00:=to_date(as_TJRQ00,'yyyymmdd'); lv_RQ0000:=as_TJRQ00; loop exit when lv_RQ0000>as_JSRQ00; open CUR_KSZYBR; loop fetch CUR_KSZYBR into lv_count2; exit when CUR_KSZYBR%notfound; lv_count0:=lv_count0+lv_count2; end loop; close CUR_KSZYBR; lv_RQ0000:=to_char(to_date(lv_RQ0000,'yyyymmdd')+1,'yyyymmdd'); end loop; update BM_GHKSTJ set BEIZH1=lv_BMMC00,GHKS02=lv_count0 where ID0000=ad_ID0000 and GHKS01=lv_KSH000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,BEIZH1,GHKS02,GHKS88) values(ad_ID0000,'8',lv_KSH000,lv_BMMC00,lv_count0,NVL(lv_PXXH00,0)); end if; end loop; close CUR_KSZYBQ; ---NICU统计 open CUR_NICULJ; loop fetch CUR_NICULJ into lv_KSH000,lv_BMMC00,lv_PXXH00; exit when CUR_NICULJ%notfound; lv_count0:=0; -- lv_TEMP00:=to_date(as_TJRQ00,'yyyymmdd'); lv_RQ0000:=as_TJRQ00; loop exit when lv_RQ0000>as_JSRQ00; open CUR_NICU; loop fetch CUR_NICU into lv_count2; exit when CUR_NICU%notfound; lv_count0:=lv_count0+lv_count2; end loop; close CUR_NICU; lv_RQ0000:=to_char(to_date(lv_RQ0000,'yyyymmdd')+1,'yyyymmdd'); end loop; update BM_GHKSTJ set BEIZH1=lv_BMMC00,GHKS03=lv_count0 where ID0000=ad_ID0000 and GHKS01=lv_KSH000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,BEIZH1,GHKS03,GHKS88) values(ad_ID0000,'8',lv_KSH000,lv_BMMC00,lv_count0,NVL(lv_PXXH00,0)); end if; end loop; close CUR_NICULJ; /* Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,BEIZH1,GHKS03,GHKS88) select ad_ID0000,'8',a.BQH000,F.BMMC00,count(a.zyid00)SL0000,NVL(F.PXXH00,0) from BQ_BRLDXX a,BQ_YJYZ00 b,bm_bmbm00 F where a.BQH000=F.bmbh00 and a.yxbz00='1' and a.qszt00 in ('新入','转入') and a.QSRQ00<=as_JSRQ00 and a.BQJSRQ>as_TJRQ00 and a.zyid00=b.zyid00 and b.zlxmjc like'%新生儿护理常规%' and b.yzzt00>='1' and b.qyrq00<=as_JSRQ00 and (b.TZRQ00>as_TJRQ00 or b.TZRQ00 is null) and a.BQH000=18 group by a.BQH000,F.BMMC00,F.PXXH00 order by F.PXXH00; */ --一级护理病人 open CUR_YJHLBQ; loop fetch CUR_YJHLBQ into lv_KSH000,lv_BMMC00,lv_PXXH00; exit when CUR_YJHLBQ%notfound; lv_count0:=0; -- lv_TEMP00:=to_date(as_TJRQ00,'yyyymmdd'); lv_RQ0000:=as_TJRQ00; loop exit when lv_RQ0000>as_JSRQ00; open CUR_YJHLBR; loop fetch CUR_YJHLBR into lv_count2; exit when CUR_YJHLBR%notfound; lv_count0:=lv_count0+lv_count2; end loop; close CUR_YJHLBR; lv_RQ0000:=to_char(to_date(lv_RQ0000,'yyyymmdd')+1,'yyyymmdd'); end loop; update BM_GHKSTJ set BEIZH1=lv_BMMC00,GHKS04=lv_count0 where ID0000=ad_ID0000 and GHKS01=lv_KSH000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,BEIZH1,GHKS04,GHKS88) values(ad_ID0000,'8',lv_KSH000,lv_BMMC00,lv_count0,NVL(lv_PXXH00,0)); end if; end loop; close CUR_YJHLBQ; --特级护理病人 open CUR_TJHLBQ; loop fetch CUR_TJHLBQ into lv_KSH000,lv_BMMC00,lv_PXXH00; exit when CUR_TJHLBQ%notfound; lv_count0:=0; -- lv_TEMP00:=to_date(as_TJRQ00,'yyyymmdd'); lv_RQ0000:=as_TJRQ00; loop exit when lv_RQ0000>as_JSRQ00; open CUR_TJHLBR; loop fetch CUR_TJHLBR into lv_count2; exit when CUR_TJHLBR%notfound; lv_count0:=lv_count0+lv_count2; end loop; close CUR_TJHLBR; lv_RQ0000:=to_char(to_date(lv_RQ0000,'yyyymmdd')+1,'yyyymmdd'); end loop; update BM_GHKSTJ set BEIZH1=lv_BMMC00,GHKS13=lv_count0 where ID0000=ad_ID0000 and GHKS01=lv_KSH000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,BEIZH1,GHKS13,GHKS88) values(ad_ID0000,'8',lv_KSH000,lv_BMMC00,lv_count0,NVL(lv_PXXH00,0)); end if; end loop; close CUR_TJHLBQ; --重症监护 open CUR_ZZJHBQ; loop fetch CUR_ZZJHBQ into lv_KSH000,lv_BMMC00,lv_PXXH00; exit when CUR_ZZJHBQ%notfound; lv_count0:=0; lv_RQ0000:=as_TJRQ00; loop exit when lv_RQ0000>as_JSRQ00; open CUR_ZZJHBR; loop fetch CUR_ZZJHBR into lv_count2; exit when CUR_ZZJHBR%notfound; lv_count0:=lv_count0+lv_count2; end loop; close CUR_ZZJHBR; lv_RQ0000:=to_char(to_date(lv_RQ0000,'yyyymmdd')+1,'yyyymmdd'); end loop; update BM_GHKSTJ set BEIZH1=lv_BMMC00,GHKS05=lv_count0 where ID0000=ad_ID0000 and GHKS01=lv_KSH000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,BEIZH1,GHKS05,GHKS88) values(ad_ID0000,'8',lv_KSH000,lv_BMMC00,lv_count0,NVL(lv_PXXH00,0)); end if; end loop; close CUR_ZZJHBQ; --select count(b.ZYID00) into lv_count1 from BQ_BRLDXX a,VW_BQ_JSXX00 b where a.yxbz00='1' and a.qszt00 in ('新入','转入') and a.QSRQ00<=as_TJRQ00 -- and a.BQJSRQ>as_TJRQ00 and a.ZYID00=b.ZYID00; -- insert into BM_GHKSTJ(ID0000,GHRQ00,BEIZH1,GHKS02,GHKS88) -- select ad_ID0000,'8','婴 儿',decode(lv_count1,0,'',lv_count1),777 from dual; -- insert into BM_GHKSTJ(ID0000,GHRQ00,BEIZH1,GHKS02,GHKS88) -- select ad_ID0000,'8','急诊科观察床',0,888 from dual; ----1：住院整理数据合计---- Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,BEIZH1,GHKS02,GHKS03,GHKS04,GHKS88,GHKS13,GHKS05) select ID0000,'9',GHKS01,BEIZH1,sum(GHKS02)GHKS02,sum(GHKS03)GHKS03,sum(GHKS04)GHKS04,nvl(GHKS88,0),sum(GHKS13)GHKS13,sum(GHKS05)GHKS05 from BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='8' group by GHKS01,BEIZH1,ID0000,GHKS88 order by nvl(GHKS88,0) ; Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,BEIZH1,GHKS02,GHKS03,GHKS04,GHKS88,GHKS13,GHKS05) select ID0000,'9',9999,'合计',sum(GHKS02)GHKS02,sum(GHKS03)GHKS03,sum(GHKS04)GHKS04,999,sum(GHKS13)GHKS13,sum(GHKS05)GHKS05 from BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='8' group by ID0000; select count(b.ZYID00) into lv_count1 from BQ_BRLDXX a,VW_BQ_JSXX00 b where a.yxbz00='1' and a.qszt00 in ('新入','转入') -- and a.QSRQ00<=as_TJRQ00 and a.BQJSRQ>as_TJRQ00 and a.ZYID00=b.ZYID00; insert into BM_GHKSTJ(ID0000,GHRQ00,BEIZH1,GHKS02,GHKS88) select ad_ID0000,'9','婴 儿',decode(lv_count1,0,'',lv_count1),9999 from dual; insert into BM_GHKSTJ(ID0000,GHRQ00,BEIZH1,GHKS02,GHKS88) select ad_ID0000,'9','急诊科观察床',0,99999 from dual; ---删除临时数据--- delete BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='8'; Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,BEIZH1,GHKS02,GHKS03,GHKS04,GHKS88,GHKS13,GHKS05) select ID0000,'22',GHKS01,BEIZH1,sum(GHKS02)GHKS02,sum(GHKS03)GHKS03,sum(GHKS04)GHKS04,nvl(GHKS88,0),sum(GHKS13)GHKS13,sum(GHKS05)GHKS05 from BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='9' group by GHKS01,BEIZH1,ID0000,GHKS88 order by nvl(GHKS88,0) ; delete BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='9'; ----2：住院整理数据插入列序号---- Insert into BM_GHKSTJ(ID0000,GHRQ00,XMHJ00,GHKS01,BEIZH1,GHKS02,GHKS03,GHKS04,GHKS88,GHKS13,GHKS05) select ID0000,'100',rownum,GHKS01,BEIZH1,GHKS02,GHKS03,GHKS04,GHKS88,GHKS13,GHKS05 from BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='22' ; ---删除临时数据--- delete BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='22'; ----查询门诊收费数据---- open CUR_MZKSGHL; loop fetch CUR_MZKSGHL into lv_KSH000,LV_BMMC00,lv_PXXH00,lv_count0; exit when CUR_MZKSGHL%notfound; update BM_GHKSTJ set GHKS11=lv_KSH000,BEIZH2=LV_BMMC00,GHKS08=lv_count0 where ID0000=ad_ID0000 and GHKS11=lv_KSH000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS11,BEIZH2,GHKS08,GHKS88) values(ad_ID0000,'88',lv_KSH000,LV_BMMC00,lv_count0,NVL(lv_PXXH00,0)); end if; end loop; close CUR_MZKSGHL; ----1.1：门诊整理数据合计---- Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS11,BEIZH2,GHKS08,GHKS88) select ID0000,'99',GHKS11,BEIZH2,sum(GHKS08)GHKS08,nvl(GHKS88,0) from BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='88' group by ID0000,GHKS11,BEIZH2,GHKS88 order by nvl(GHKS88,0); Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS11,BEIZH2,GHKS08,GHKS88) select ID0000,'99',9999,'合计',sum(GHKS08)GHKS08,999 from BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='88' group by ID0000; ---删除临时数据--- delete BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='88'; ----1：门诊整理数据与住院数据---- lv_count1:=1; open CUR_GXMZSH; loop fetch CUR_GXMZSH into lv_GHKS11,lv_BEIZH2,lv_GHKS08; exit when CUR_GXMZSH%notfound; update BM_GHKSTJ set GHKS11=lv_GHKS11,BEIZH2=lv_BEIZH2,GHKS08=lv_GHKS08 where ID0000=ad_ID0000 and XMHJ00=lv_count1; if SQL%NOTFOUND then Insert into BM_GHKSTJ(ID0000,GHRQ00,BEIZH2,GHKS11,GHKS08,XMHJ00) values(ad_ID0000,'100',lv_BEIZH2,lv_GHKS11,lv_GHKS08,lv_count1); end if; lv_count1:=lv_count1+1; end loop; close CUR_GXMZSH; ---删除临时数据--- delete BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='99'; --辅助科统计 --西药方-- select sum(decode(sign(cfzje0),1,1,-1)) INTO lv_XYF000 from YF_MZCF00 where JZRQ00>=as_TJRQ00 and JZRQ00<=as_JSRQ00 and CFZJE0<>0 and YFBMBH<>166; --中药方-- select sum(decode(sign(cfzje0),1,1,-1)) INTO lv_zYF000 from YF_MZCF00 where JZRQ00>=as_TJRQ00 and JZRQ00<=as_JSRQ00 and CFZJE0<>0 and YFBMBH=166; --辅助科统计 open CUR_QYJCRCS; loop fetch CUR_QYJCRCS into lv_KSH000,lv_count0; exit when CUR_QYJCRCS%notfound; update BM_GHKSTJ set GHKS10=lv_KSH000,GHKS09=lv_count0 where ID0000=ad_ID0000 and GHKS10=lv_KSH000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS10,GHKS09) values(ad_ID0000,'888',lv_KSH000,lv_count0); end if; end loop; close CUR_QYJCRCS; Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS10,GHKS09) select ID0000,'999',GHKS10,sum(GHKS09)GHKS09 from BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='888' group by ID0000,GHKS10; select sum(GHKS09)into lv_GHKS09 from BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='888'; Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS10,BEIZH3,GHKS09) values (ad_ID0000,'999',9999,'合计',nvl(lv_GHKS09,0)+nvl(lv_XYF000,0)+nvl(lv_zYF000,0)); delete BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='888'; update BM_GHKSTJ set BEIZH3='西药方',GHKS09=lv_XYF000 where ID0000=ad_ID0000 and XMHJ00=1; update BM_GHKSTJ set BEIZH3='中药方', GHKS09=lv_ZYF000 where ID0000=ad_ID0000 and XMHJ00=2; --辅助科统计 lv_count1:=5; open CUR_FZKB00; loop fetch CUR_FZKB00 into lv_GHKS10,lv_GHKS09; exit when CUR_FZKB00%notfound; if lv_GHKS10=9999 then lv_BEIZH3:='合计'; else select JCLBMC into lv_BEIZH3 from BM_JCLB00 where JCLBID=lv_GHKS10 and rownum=1; end if; update BM_GHKSTJ set GHKS10=lv_GHKS10,GHKS09=lv_GHKS09,BEIZH3=lv_BEIZH3 where ID0000=ad_ID0000 and XMHJ00=lv_count1; if SQL%NOTFOUND then Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS10,GHKS09,BEIZH3,XMHJ00) values(ad_ID0000,'100',lv_GHKS10,lv_GHKS09,lv_BEIZH3,lv_count1); end if; lv_count1:=lv_count1+1; end loop; close CUR_FZKB00; delete BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='999'; --commit; exception when others then as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_MZZYKSGZRB('||as_TJRQ00||','||as_JSRQ00||')',1,150); rollback; end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%