create or replace procedure SP_SF_YBBRFY ( AS_KSRQ00 in char, --开始日期 AS_JSRQ00 in char, --结束日期 AS_KSBH00 in char, --开单科室 AS_YBLB00 in char, --医保类别 AS_YBMC00 IN char, --医保名称 AD_ID0000 out number, --统计报表对应的ID AS_YHMSG0 out varchar, --存储过程提示的错误信息 AS_SYSMSG out varchar --系统提示的错误信息 ) as -- MODIFICATION HISTORY -- Person Date Comments -- zhangyc 2010.09.14 create; -- zhangyc 2010.10.19 修改挂号人数条件 LS_TJYF00 char(2); --月份 LS_GHRS00 number(5); --挂号人数 ls_BMBH00 number(5); LS_FPBH00 BM_FPXM00.BH0000%TYPE; --发票编号 LS_FPMC00 BM_FPXM00.XMMC00%TYPE; --发票名称 LS_YBMC00 IC_YBBRLB.YBMC00%TYPE; --医保名称 LS_HJJE00 SF_FYMX00.HJJE00%TYPE; --合计金额 LS_SFXM01 BM_YYSFTJ.SFXM01%TYPE; LS_SFXM02 BM_YYSFTJ.SFXM02%TYPE; LS_SFXM03 BM_YYSFTJ.SFXM03%TYPE; LS_SFXM04 BM_YYSFTJ.SFXM04%TYPE; LS_SFXM05 BM_YYSFTJ.SFXM05%TYPE; LS_SFXM06 BM_YYSFTJ.SFXM06%TYPE; LS_SFXM07 BM_YYSFTJ.SFXM07%TYPE; LS_SFXM08 BM_YYSFTJ.SFXM08%TYPE; LS_SFXM09 BM_YYSFTJ.SFXM09%TYPE; LS_SFXM10 BM_YYSFTJ.SFXM10%TYPE; LS_SFXM11 BM_YYSFTJ.SFXM11%TYPE; LS_SFXM12 BM_YYSFTJ.SFXM12%TYPE; LS_GHRS01 number(5); LS_GHRS02 number(5); LS_GHRS03 number(5); LS_GHRS04 number(5); LS_GHRS05 number(5); LS_GHRS06 number(5); LS_GHRS07 number(5); LS_GHRS08 number(5); LS_GHRS09 number(5); LS_GHRS10 number(5); LS_GHRS11 number(5); LS_GHRS12 number(5); ls_Count Number(5); CURSOR CUR_SF_YBBRFY IS select BH0000,XMMC00,YBMC00,TJYF00,HJJE00 from (SELECT H.BH0000,H.XMMC00,D.YBMC00,substr(C.CZRQ00,5,2)TJYF00,sum(A.HJJE00)HJJE00 from SF_FYMX00 A,SF_BRFY00 C,BM_BRXXB0 E,BM_YYSFXM G,BM_FPXM00 H,IC_YBBRLB D where A.DJH000 = C.DJH000 and C.BRID00 = E.BRID00 and A.XMBH00 = G.SFXMID and G.MZFPID = H.FPXMID and D.FBBH00=E.FBBH00 and D.YBLB00=E.YBLB00 and C.CZRQ00 >=AS_KSRQ00 and C.CZRQ00 <=AS_JSRQ00 and (A.KDKS00 = AS_KSBH00 or AS_KSBH00='-1') -- and (D.YBZXLB = AS_YBLB00 or AS_YBLB00 ='-1') and (D.YBMC00 = AS_YBMC00 or AS_YBMC00 ='所有类别') group by H.BH0000,H.XMMC00,D.YBMC00,substr(C.CZRQ00,5,2))where HJJE00<>0; CURSOR CUR_SF_ZFY00 IS select BMBH00,BMMC00,sum(SFXM01),sum(SFXM02),sum(SFXM03),sum(SFXM04),sum(SFXM05), sum(SFXM06),sum(SFXM07),sum(SFXM08),sum(SFXM09),sum(SFXM10),sum(SFXM11),sum(SFXM12) from BM_YYSFTJ where ID0000=ad_ID0000 and SFXM99=9.9 --and BMBH00 in(1,2) group by BMBH00,BMMC00 order by BMMC00,BMBH00; CURSOR CUR_SF_GHRS00 IS select ybmc00,substr(czrq00,5,2)TJYF00,sum(GHCS00)GHRS00 from VW_SF_GHLSXX where czrq00>=AS_KSRQ00 and czrq00<=AS_JSRQ00 and (GHKS00= AS_KSBH00 or AS_KSBH00='-1') --and (YBZXLB = AS_YBLB00 or AS_YBLB00 ='-1') and (YBMC00 = AS_YBMC00 or AS_YBMC00 ='所有类别') and GHCS00>0 group by ybmc00,substr(czrq00,5,2); BEGIN select SQ_BM_YYSFTJ_ID0000.nextval into ad_ID0000 from dual; OPEN CUR_SF_YBBRFY; LOOP FETCH CUR_SF_YBBRFY INTO ls_FPBH00,ls_FPMC00,LS_YBMC00,LS_TJYF00,ls_HJJE00; EXIT WHEN CUR_SF_YBBRFY%NOTFOUND; if LS_TJYF00='01' then --一月份 Update BM_YYSFTJ Set SFXM01 = NVL(SFXM01,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=1 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM01,SFXM99) values(ad_ID0000,1,'总费用',LS_YBMC00,ls_HJJE00,9); end if; IF instr(ls_FPMC00,'药') > 0 THEN Update BM_YYSFTJ Set SFXM01 = NVL(SFXM01,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=2 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM01,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,ls_HJJE00,9); end if; else Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM01,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,0,9); END IF; end if; if LS_TJYF00='02' then --二月份 Update BM_YYSFTJ Set SFXM02 = NVL(SFXM02,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=1 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM02,SFXM99) values(ad_ID0000,1,'总费用',LS_YBMC00,ls_HJJE00,9); end if; IF instr(ls_FPMC00,'药') > 0 THEN Update BM_YYSFTJ Set SFXM02 = NVL(SFXM02,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=2 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM02,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,ls_HJJE00,9); end if; else Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM01,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,0,9); END IF; end if; if LS_TJYF00='03' then --三月份 Update BM_YYSFTJ Set SFXM03 = NVL(SFXM03,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=1 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM03,SFXM99) values(ad_ID0000,1,'总费用',LS_YBMC00,ls_HJJE00,9); end if; IF instr(ls_FPMC00,'药') > 0 THEN Update BM_YYSFTJ Set SFXM03 = NVL(SFXM03,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=2 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM03,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,ls_HJJE00,9); end if; else Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM01,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,0,9); END IF; end if; if LS_TJYF00='04' then --四月份 Update BM_YYSFTJ Set SFXM04 = NVL(SFXM04,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=1 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM04,SFXM99) values(ad_ID0000,1,'总费用',LS_YBMC00,ls_HJJE00,9); end if; IF instr(ls_FPMC00,'药') > 0 THEN Update BM_YYSFTJ Set SFXM04 = NVL(SFXM04,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=2 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM04,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,ls_HJJE00,9); end if; else Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM01,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,0,9); END IF; end if; if LS_TJYF00='05' then --五月份 Update BM_YYSFTJ Set SFXM05 = NVL(SFXM05,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=1 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM05,SFXM99) values(ad_ID0000,1,'总费用',LS_YBMC00,ls_HJJE00,9); end if; IF instr(ls_FPMC00,'药') > 0 THEN Update BM_YYSFTJ Set SFXM05 = NVL(SFXM05,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=2 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM05,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,ls_HJJE00,9); end if; else Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM01,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,0,9); END IF; end if; if LS_TJYF00='06' then --六月份 Update BM_YYSFTJ Set SFXM06 = NVL(SFXM06,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=1 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM06,SFXM99) values(ad_ID0000,1,'总费用',LS_YBMC00,ls_HJJE00,9); end if; IF instr(ls_FPMC00,'药') > 0 THEN Update BM_YYSFTJ Set SFXM06 = NVL(SFXM06,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=2 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM06,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,ls_HJJE00,9); end if; else Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM01,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,0,9); END IF; end if; if LS_TJYF00='07' then --七月份 Update BM_YYSFTJ Set SFXM07 = NVL(SFXM07,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=1 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM07,SFXM99) values(ad_ID0000,1,'总费用',LS_YBMC00,ls_HJJE00,9); end if; IF instr(ls_FPMC00,'药') > 0 THEN Update BM_YYSFTJ Set SFXM07 = NVL(SFXM07,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=2 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM07,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,ls_HJJE00,9); end if; else Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM01,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,0,9); END IF; end if; if LS_TJYF00='08' then --八月份 Update BM_YYSFTJ Set SFXM08 = NVL(SFXM08,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=1 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM08,SFXM99) values(ad_ID0000,1,'总费用',LS_YBMC00,ls_HJJE00,9); end if; IF instr(ls_FPMC00,'药') > 0 THEN Update BM_YYSFTJ Set SFXM08 = NVL(SFXM08,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=2 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM08,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,ls_HJJE00,9); end if; else Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM01,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,0,9); END IF; end if; if LS_TJYF00='09' then --九月份 Update BM_YYSFTJ Set SFXM09 = NVL(SFXM09,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=1 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM09,SFXM99) values(ad_ID0000,1,'总费用',LS_YBMC00,ls_HJJE00,9); end if; IF instr(ls_FPMC00,'药') > 0 THEN Update BM_YYSFTJ Set SFXM09 = NVL(SFXM09,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=2 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM09,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,ls_HJJE00,9); end if; else Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM01,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,0,9); END IF; end if; if LS_TJYF00='10' then --十月份 Update BM_YYSFTJ Set SFXM10 = NVL(SFXM10,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=1 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM10,SFXM99) values(ad_ID0000,1,'总费用',LS_YBMC00,ls_HJJE00,9); end if; IF instr(ls_FPMC00,'药') > 0 THEN Update BM_YYSFTJ Set SFXM10 = NVL(SFXM10,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=2 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM10,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,ls_HJJE00,9); end if; else Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM01,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,0,9); END IF; end if; if LS_TJYF00='11' then --十一月份 Update BM_YYSFTJ Set SFXM11 = NVL(SFXM11,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=1 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM11,SFXM99) values(ad_ID0000,1,'总费用',LS_YBMC00,ls_HJJE00,9); end if; IF instr(ls_FPMC00,'药') > 0 THEN Update BM_YYSFTJ Set SFXM11 = NVL(SFXM11,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=2 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM11,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,ls_HJJE00,9); end if; else Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM01,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,0,9); END IF; end if; if LS_TJYF00='12' then --十二月份 Update BM_YYSFTJ Set SFXM12 = NVL(SFXM12,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=1 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM12,SFXM99) values(ad_ID0000,1,'总费用',LS_YBMC00,ls_HJJE00,9); end if; IF instr(ls_FPMC00,'药') > 0 THEN Update BM_YYSFTJ Set SFXM12 = NVL(SFXM12,0)+ls_HJJE00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=2 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM12,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,ls_HJJE00,9); end if; else Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM01,SFXM99) values(ad_ID0000,2,'其中:药品费',LS_YBMC00,0,9); END IF; end if; end loop; CLOSE CUR_SF_YBBRFY; ---挂号人数------- OPEN CUR_SF_GHRS00; LOOP FETCH CUR_SF_GHRS00 INTO LS_YBMC00,LS_TJYF00,LS_GHRS00; EXIT WHEN CUR_SF_GHRS00%NOTFOUND; if LS_TJYF00='01' then Update BM_YYSFTJ Set SFXM01 =NVL(SFXM01,0)+LS_GHRS00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=3 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM01,SFXM99) values(ad_ID0000,3,'挂号人数',LS_YBMC00,LS_GHRS00,9); end if; end if; if LS_TJYF00='02' then Update BM_YYSFTJ Set SFXM02 =NVL(SFXM02,0)+LS_GHRS00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=3 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM02,SFXM99) values(ad_ID0000,3,'挂号人数',LS_YBMC00,LS_GHRS00,9); end if; end if; if LS_TJYF00='03' then Update BM_YYSFTJ Set SFXM03 =NVL(SFXM03,0)+LS_GHRS00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=3 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM03,SFXM99) values(ad_ID0000,3,'挂号人数',LS_YBMC00,LS_GHRS00,9); end if; end if; if LS_TJYF00='04' then Update BM_YYSFTJ Set SFXM04 =NVL(SFXM04,0)+LS_GHRS00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=3 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM04,SFXM99) values(ad_ID0000,3,'挂号人数',LS_YBMC00,LS_GHRS00,9); end if; end if; if LS_TJYF00='05' then Update BM_YYSFTJ Set SFXM05 =NVL(SFXM05,0)+LS_GHRS00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=3 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM05,SFXM99) values(ad_ID0000,3,'挂号人数',LS_YBMC00,LS_GHRS00,9); end if; end if; if LS_TJYF00='06' then Update BM_YYSFTJ Set SFXM06 =NVL(SFXM06,0)+LS_GHRS00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=3 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM06,SFXM99) values(ad_ID0000,3,'挂号人数',LS_YBMC00,LS_GHRS00,9); end if; end if; if LS_TJYF00='07' then Update BM_YYSFTJ Set SFXM07 =NVL(SFXM07,0)+LS_GHRS00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=3 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM07,SFXM99) values(ad_ID0000,3,'挂号人数',LS_YBMC00,LS_GHRS00,9); end if; end if; if LS_TJYF00='08' then Update BM_YYSFTJ Set SFXM08 =NVL(SFXM08,0)+LS_GHRS00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=3 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM08,SFXM99) values(ad_ID0000,3,'挂号人数',LS_YBMC00,LS_GHRS00,9); end if; end if; if LS_TJYF00='09' then Update BM_YYSFTJ Set SFXM09 =NVL(SFXM09,0)+LS_GHRS00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=3 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM09,SFXM99) values(ad_ID0000,3,'挂号人数',LS_YBMC00,LS_GHRS00,9); end if; end if; if LS_TJYF00='10' then Update BM_YYSFTJ Set SFXM10 =NVL(SFXM10,0)+LS_GHRS00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=3 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM10,SFXM99) values(ad_ID0000,3,'挂号人数',LS_YBMC00,LS_GHRS00,9); end if; end if; if LS_TJYF00='11' then Update BM_YYSFTJ Set SFXM11 =NVL(SFXM11,0)+LS_GHRS00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=3 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM11,SFXM99) values(ad_ID0000,3,'挂号人数',LS_YBMC00,LS_GHRS00,9); end if; end if; if LS_TJYF00='12' then Update BM_YYSFTJ Set SFXM12 =NVL(SFXM12,0)+LS_GHRS00 where ID0000=ad_ID0000 and BMMC00=LS_YBMC00 and BMBH00=3 and SFXM99=9; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM12,SFXM99) values(ad_ID0000,3,'挂号人数',LS_YBMC00,LS_GHRS00,9); end if; end if; end loop; CLOSE CUR_SF_GHRS00; Insert into BM_YYSFTJ(ID0000,SFXM99,BMBH00,SJYSXM,BMMC00, SFXM01,SFXM02,SFXM03,SFXM04,SFXM05,SFXM06, SFXM07,SFXM08,SFXM09,SFXM10,SFXM11,SFXM12) select ID0000,9.9, BMBH00, SJYSXM, BMMC00, sum(SFXM01), sum(SFXM02), sum(SFXM03), sum(SFXM04), sum(SFXM05), sum(SFXM06), sum(SFXM07), sum(SFXM08), sum(SFXM09), sum(SFXM10), sum(SFXM11), sum(SFXM12) from BM_YYSFTJ where ID0000=ad_ID0000 and SFXM99=9 group by ID0000,BMBH00,SJYSXM,BMMC00; delete from BM_YYSFTJ where ID0000=ad_ID0000 and SFXM99=9 ; OPEN CUR_SF_ZFY00; LOOP FETCH CUR_SF_ZFY00 INTO ls_BMBH00,LS_YBMC00,LS_SFXM01,LS_SFXM02,LS_SFXM03,LS_SFXM04 ,LS_SFXM05 ,LS_SFXM06,LS_SFXM07,LS_SFXM08,LS_SFXM09,LS_SFXM10,LS_SFXM11,LS_SFXM12; EXIT WHEN CUR_SF_ZFY00%NOTFOUND; select SFXM01,SFXM02,SFXM03,SFXM04,SFXM05, SFXM06,SFXM07,SFXM08,SFXM09,SFXM10,SFXM11,SFXM12 into LS_GHRS01,LS_GHRS02,LS_GHRS03,LS_GHRS04,LS_GHRS05,LS_GHRS06, LS_GHRS07,LS_GHRS08,LS_GHRS09,LS_GHRS10,LS_GHRS11,LS_GHRS12 from BM_YYSFTJ where ID0000=ad_ID0000 and BMBH00=3 and SFXM99=9.9 and BMMC00=LS_YBMC00; if ls_BMBH00=1 then --次均医药费 Insert into BM_YYSFTJ (ID0000,BMBH00,SJYSXM,BMMC00,SFXM99, SFXM01,SFXM02,SFXM03,SFXM04,SFXM05,SFXM06, SFXM07,SFXM08,SFXM09,SFXM10,SFXM11,SFXM12) values (ad_ID0000,1,'总费用',LS_YBMC00,99, LS_SFXM01,LS_SFXM02,LS_SFXM03,LS_SFXM04,LS_SFXM05 ,LS_SFXM06, LS_SFXM07,LS_SFXM08,LS_SFXM09,LS_SFXM10,LS_SFXM11,LS_SFXM12); update BM_YYSFTJ set SFXM01=decode(nvl(LS_GHRS01,0),0,null,LS_SFXM01/LS_GHRS01) , SFXM02=decode(nvl(LS_GHRS02,0),0,null,LS_SFXM02/LS_GHRS02), SFXM03=decode(nvl(LS_GHRS03,0),0,null,LS_SFXM03/LS_GHRS03), SFXM04=decode(nvl(LS_GHRS04,0),0,null,LS_SFXM04/LS_GHRS04), SFXM05=decode(nvl(LS_GHRS05,0),0,null,LS_SFXM05/LS_GHRS05), SFXM06=decode(nvl(LS_GHRS06,0),0,null,LS_SFXM06/LS_GHRS06), SFXM07=decode(nvl(LS_GHRS07,0),0,null,LS_SFXM07/LS_GHRS07), SFXM08=decode(nvl(LS_GHRS08,0),0,null,LS_SFXM08/LS_GHRS08), SFXM09=decode(nvl(LS_GHRS09,0),0,null,LS_SFXM09/LS_GHRS09), SFXM10=decode(nvl(LS_GHRS10,0),0,null,LS_SFXM10/LS_GHRS10), SFXM11=decode(nvl(LS_GHRS11,0),0,null,LS_SFXM11/LS_GHRS11), SFXM12=decode(nvl(LS_GHRS12,0),0,null,LS_SFXM12/LS_GHRS12) where ID0000=ad_ID0000 and SFXM99=99 and BMBH00=4 and BMMC00=LS_YBMC00; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM99, SFXM01, SFXM02, SFXM03, SFXM04, SFXM05, SFXM06, SFXM07, SFXM08, SFXM09, SFXM10, SFXM11, SFXM12) values(ad_ID0000,4,'次均医药费',LS_YBMC00,99, decode(nvl(LS_GHRS01,0),0,null,LS_SFXM01/LS_GHRS01) , decode(nvl(LS_GHRS02,0),0,null,LS_SFXM02/LS_GHRS02), decode(nvl(LS_GHRS03,0),0,null,LS_SFXM03/LS_GHRS03), decode(nvl(LS_GHRS04,0),0,null,LS_SFXM04/LS_GHRS04), decode(nvl(LS_GHRS05,0),0,null,LS_SFXM05/LS_GHRS05), decode(nvl(LS_GHRS06,0),0,null,LS_SFXM06/LS_GHRS06), decode(nvl(LS_GHRS07,0),0,null,LS_SFXM07/LS_GHRS07), decode(nvl(LS_GHRS08,0),0,null,LS_SFXM08/LS_GHRS08), decode(nvl(LS_GHRS09,0),0,null,LS_SFXM09/LS_GHRS09), decode(nvl(LS_GHRS10,0),0,null,LS_SFXM10/LS_GHRS10), decode(nvl(LS_GHRS11,0),0,null,LS_SFXM11/LS_GHRS11), decode(nvl(LS_GHRS12,0),0,null,LS_SFXM12/LS_GHRS12) ); end if; end if; if ls_BMBH00=2 then -- -- 其中:次均药品费 Insert into BM_YYSFTJ (ID0000,BMBH00,SJYSXM,BMMC00,SFXM99, SFXM01,SFXM02,SFXM03,SFXM04,SFXM05,SFXM06, SFXM07,SFXM08,SFXM09,SFXM10,SFXM11,SFXM12) values (ad_ID0000,2,'其中:药品费',LS_YBMC00,99, LS_SFXM01,LS_SFXM02,LS_SFXM03,LS_SFXM04,LS_SFXM05 ,LS_SFXM06, LS_SFXM07,LS_SFXM08,LS_SFXM09,LS_SFXM10,LS_SFXM11,LS_SFXM12); update BM_YYSFTJ set SFXM01=decode(nvl(LS_GHRS01,0),0,null,LS_SFXM01/LS_GHRS01) , SFXM02=decode(nvl(LS_GHRS02,0),0,null,LS_SFXM02/LS_GHRS02), SFXM03=decode(nvl(LS_GHRS03,0),0,null,LS_SFXM03/LS_GHRS03), SFXM04=decode(nvl(LS_GHRS04,0),0,null,LS_SFXM04/LS_GHRS04), SFXM05=decode(nvl(LS_GHRS05,0),0,null,LS_SFXM05/LS_GHRS05), SFXM06=decode(nvl(LS_GHRS06,0),0,null,LS_SFXM06/LS_GHRS06), SFXM07=decode(nvl(LS_GHRS07,0),0,null,LS_SFXM07/LS_GHRS07), SFXM08=decode(nvl(LS_GHRS08,0),0,null,LS_SFXM08/LS_GHRS08), SFXM09=decode(nvl(LS_GHRS09,0),0,null,LS_SFXM09/LS_GHRS09), SFXM10=decode(nvl(LS_GHRS10,0),0,null,LS_SFXM10/LS_GHRS10), SFXM11=decode(nvl(LS_GHRS11,0),0,null,LS_SFXM11/LS_GHRS11), SFXM12=decode(nvl(LS_GHRS12,0),0,null,LS_SFXM12/LS_GHRS12) where ID0000=ad_ID0000 and SFXM99=99 and BMBH00=5 and BMMC00=LS_YBMC00; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM99, SFXM01, SFXM02, SFXM03, SFXM04, SFXM05, SFXM06, SFXM07, SFXM08, SFXM09, SFXM10, SFXM11, SFXM12) values(ad_ID0000,5,'其中:次均药品费',LS_YBMC00,99, decode(nvl(LS_GHRS01,0),0,null,LS_SFXM01/LS_GHRS01) , decode(nvl(LS_GHRS02,0),0,null,LS_SFXM02/LS_GHRS02), decode(nvl(LS_GHRS03,0),0,null,LS_SFXM03/LS_GHRS03), decode(nvl(LS_GHRS04,0),0,null,LS_SFXM04/LS_GHRS04), decode(nvl(LS_GHRS05,0),0,null,LS_SFXM05/LS_GHRS05), decode(nvl(LS_GHRS06,0),0,null,LS_SFXM06/LS_GHRS06), decode(nvl(LS_GHRS07,0),0,null,LS_SFXM07/LS_GHRS07), decode(nvl(LS_GHRS08,0),0,null,LS_SFXM08/LS_GHRS08), decode(nvl(LS_GHRS09,0),0,null,LS_SFXM09/LS_GHRS09), decode(nvl(LS_GHRS10,0),0,null,LS_SFXM10/LS_GHRS10), decode(nvl(LS_GHRS11,0),0,null,LS_SFXM11/LS_GHRS11), decode(nvl(LS_GHRS12,0),0,null,LS_SFXM12/LS_GHRS12) ); end if; end if ; if ls_BMBH00=3 then Insert into BM_YYSFTJ (ID0000,BMBH00,SJYSXM,BMMC00,SFXM99, SFXM01,SFXM02,SFXM03,SFXM04,SFXM05,SFXM06, SFXM07,SFXM08,SFXM09,SFXM10,SFXM11,SFXM12) values (ad_ID0000,3,'挂号人数',LS_YBMC00,99, LS_SFXM01,LS_SFXM02,LS_SFXM03,LS_SFXM04,LS_SFXM05 ,LS_SFXM06, LS_SFXM07,LS_SFXM08,LS_SFXM09,LS_SFXM10,LS_SFXM11,LS_SFXM12); select count(*) into ls_Count from BM_YYSFTJ where ID0000=ad_ID0000 and BMBH00=1 and SFXM99=9.9 and BMMC00=LS_YBMC00; if ls_Count=0 then Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM99)values(ad_ID0000,1,'总费用',LS_YBMC00,99); Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM99)values(ad_ID0000,2,'其中:药品费',LS_YBMC00,99); Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM99)values(ad_ID0000,4,'其中:药品费',LS_YBMC00,99); Insert into BM_YYSFTJ(ID0000,BMBH00,SJYSXM,BMMC00,SFXM99)values(ad_ID0000,5,'其中:次均药品费',LS_YBMC00,99); end if ; end if; end loop; CLOSE CUR_SF_ZFY00; delete from BM_YYSFTJ where ID0000=ad_ID0000 and SFXM99=9.9 ; EXCEPTION WHEN OTHERS THEN as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:CUR_SF_YBBRFY('||as_KSRQ00||','||as_JSRQ00|| ','||AS_KSBH00||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); ROLLBACK; END; /