-- Start of DDL script for SP_SF_CWJJSHTJ -- Generated 13-五月-13 11:29:52 am -- from fw2-SD_HOSPITAL:1 -- Procedure SP_SF_CWJJSHTJ CREATE OR REPLACE procedure SP_SF_CWJJSHTJ ( as_KSRQ00 in char, --开始统计日期 as_JSRQ00 in char, --结束统计日期 as_YSLB00 in char, --医生类别 -1：全部 0：院内聘 1：院外聘 as_KDKS00 in char, --科室名称 ‘-1’:全部 as_TJFS00 in char, --统计方式 0：正常工作日 1：节假日 as_YYID00 in char, --医院ID -1：全院 ad_ID0000 out number, --统计报表对应的ID as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar --系统提示的错误信息 ) as --MODIFY HISTORY --date person comments --2011.07.13 zhangyc create; by MZSF-20110704-001 --2011.08.26 linzy 增加过程SF_SF_SDS000 --2011.09.05 linzy SF_SF_SDS000脚本优化 --2011.10.11 lizzy 修正按开单科室汇总，及实得数只统计一次 --2011.10.14 linzy 制剂费用由SSKS00 改为按GHKS00统计 --2012.08.08 jinfl 判断是否院内制剂的条件 B.ZJBZ00 改成B.YKBMBH=166 MZSF-20120806-006 ----------------------------------------------------------------------------- lv_KDYS00 SF_FYMX00.KDYS00%TYPE; lv_FPBH00 BM_FPXM00.bh0000%TYPE; lv_HJJE00 SF_FYMX00.HJJE00%TYPE; lv_HJJE01 SF_FYMX00.HJJE00%TYPE; lv_YSPQLB BM_YGBM00.YSPQLB%TYPE; lv_KDKS00 SF_FYMX00.KDKS00%TYPE; --开单科室 cursor CUR_SF_FYJE00 is select BMBH00,BMMC00,SFXM77,sum(XMHJ00)XMHJ00,SFXM200 from BM_YYSFTJ where ID0000=ad_ID0000 and SFXM99=0 group by BMBH00,BMMC00,SFXM77,SFXM200; --统计医生 cursor CUR_SF_TJYS00 is select distinct BMBH00 KDYS00 from BM_YYSFTJ where ID0000=ad_ID0000; begin select SQ_BM_YYSFTJ_ID0000.nextval into ad_ID0000 from dual; ---BMBH00:医生编号 BMMC00：发票编号 SJYSXM：发票名称 XMHJ00：合计金额 SFXM77:院内聘或院外聘 SFXM88：是否正常工作日 0：正常 1节假日 ---linzy 2011.09.30 SFXM200 按医生开单科室进行汇总 insert into BM_YYSFTJ(ID0000,BMBH00,BMMC00,SJYSXM,XMHJ00,SFXM77,SFXM99,SFXM200) select ad_ID0000,A.KDYS00,B.bh0000,B.XMMC00,sum(A.HJJE00)HJJE00,e.YSPQLB,0,a.KDKS00 from SF_FYMX00 A,SF_BRFY00 D,BM_YYSFXM C,BM_YGBM00 e,BM_FPXM00 B where A.XMBH00 = C.SFXMID and C.HSXMID = B.FPXMID and A.DJH000 = D.DJH000 and A.KDYS00=e.YGBH00(+) and d.CZRQ00>=as_KSRQ00 and d.CZRQ00<=as_JSRQ00 and (a.KDKS00=as_KDKS00 or as_KDKS00 ='-1') --and (a.kdks00 = as_KDKS00 or as_KDKS00 ='-1') and ( (SF_SF_GetZCGZR(D.CZRQ00,D.CZSJ00)=as_TJFS00 and (B.BH0000||'' not in ('15','16','08','09','14'))) or ((B.BH0000||'' in ('15','16','08','09','14')) and as_TJFS00=0) ) and B.bh0000||'' not in ('01','02','03') ---扣除掉药品费 and a.KDKS00 in (select BMBH00 from BM_BMBM00 k where (K.YYID00=as_YYID00 OR as_YYID00='-1')) and (e.YSPQLB=as_YSLB00 or as_YSLB00='-1') group by ad_ID0000,A.KDYS00,B.BH0000,B.XMMC00,e.YSPQLB,a.KDKS00; --01:西药费 02:成药费 03:草药费 04:挂号费 05:床位费 06:诊察费 07:护理费 08:检查费 09:化验费 10:治疗费 --11:手术费 12:救护车费 13:其他费 14:MRI费 15:CT费 16:彩超费 17:输氧费 18:输血费 19:麻醉费 20:麻醉相关 21:其它医疗 --SFXM01:检查费费用 SFXM02:应得检查费奖金 SFXM11:治疗费 SFXM12:应得治疗奖金 --SFXM21:制剂 SFXM22:应得制剂奖金 SFXM31:会诊费 SFXM32:应得会诊费奖金 --SFXM41:诊疗费用 SFXM42:应得诊疗费奖金 --SFXM51 中医辨证论治 SFXM71 预约挂号奖励 --SFXM61 下午出诊奖励 SFXM81 病历协诊奖励 --SFXM111 横向合计 --实得数 if as_TJFS00='0' then SF_SF_SDS000(as_KSRQ00,as_JSRQ00,as_YSLB00,as_KDKS00,lv_kdys00,as_TJFS00,as_YYID00,ad_ID0000); end if; OPEN CUR_SF_FYJE00; loop fetch CUR_SF_FYJE00 into lv_kdys00,lv_FPBH00,lv_YSPQLB,lv_HJJE00,lv_KDKS00; exit when CUR_SF_FYJE00%NOTFOUND; --检查费费用统计(检查费)包含内容：发票项目为 CT、彩超、检查、化验、MRI IF lv_FPBH00 IN ('15','16','08','09','14') then Update BM_YYSFTJ Set SFXM01 = NVL(SFXM01,0)+lv_HJJE00 where ID0000=ad_ID0000 and BMBH00=lv_kdys00 and SFXM200=lv_KDKS00 and SFXM99=1; if SQL%NOTFOUND then insert into BM_YYSFTJ(ID0000,BMBH00,SFXM01,SFXM99,SFXM200) values(ad_ID0000,lv_kdys00,NVL(lv_HJJE00,0),1,lv_KDKS00); end if; end if; --治疗费包含内容：治疗费、手术费 治疗费：按开单治疗费用的10%来计算。（节假日才有，正常没有。外聘的都有） if (lv_FPBH00 IN ('11','10')) then -- if lv_YSPQLB='0'then --内聘,正常工作日 -- if as_TJFS00='0' then -- lv_HJJE01:=0; -- else -- lv_HJJE01:=lv_HJJE00; -- end if; -- else --外聘聘 -- lv_HJJE01:=lv_HJJE00; -- end if; update BM_YYSFTJ Set SFXM11 = NVL(SFXM11,0)+lv_HJJE00 where ID0000=ad_ID0000 and BMBH00=lv_kdys00 and SFXM200=lv_KDKS00 and SFXM99=1; if SQL%NOTFOUND then insert into BM_YYSFTJ(ID0000,BMBH00,SFXM11,SFXM99,SFXM200) values(ad_ID0000,lv_kdys00,NVL(lv_HJJE00,0),1,lv_KDKS00); end if; end IF; end loop; delete BM_YYSFTJ where ID0000=ad_ID0000 and SFXM99=0; ----治疗费包含内容：治疗费 --3、 增加参数或通过其他标识来设置 某些科室为外科系统科室（外科系统科室才有治疗费的补助），且可以某些医生的奖金提取比例可以单独设置。 -- Insert into BM_YYSFTJ(ID0000,BMBH00,SFXM11,SFXM99) -- select ad_ID0000,A.kdys00,sum(A.HJJE00)HJJE00,1 -- from SF_FYMX00 A,SF_BRFY00 D,BM_YYSFXM C,BM_YGBM00 E,BM_FPXM00 B -- where A.XMBH00 = C.SFXMID and C.MZFPID = B.FPXMID -- and A.DJH000 = D.DJH000 and B.bh0000||'' in ('10','11') ---- and a.kdks00 in (select bmbh00 from BM_BMBM00 where SFWKKS='1') -- and d.czrq00>=as_KSRQ00 and d.czrq00<=as_JSRQ00 -- and (a.kdks00 = as_KDKS00 or as_KDKS00 ='-1') -- and SF_SF_GetZCGZR(D.czrq00,D.czsj00)=as_TJFS00 -- and A.kdys00=e.YGBH00(+) -- and (e.YSPQLB=as_YSLB00 or as_YSLB00='-1') --group by ad_ID0000,A.kdys00; ---制剂费用统计 bm_yd0000.zjbz00=1 保留一位小数 linzy insert into BM_YYSFTJ(ID0000,BMBH00,SFXM21,SFXM99,SFXM200) select ad_ID0000,C.YSGZH0,sum(round(A.YPZSL0*A.LSDJ00,1)),1,c.GHKS00 from YF_MZCFMX A,YF_MZCF00 C, BM_YD0000 B,BM_YGBM00 e where A.CFLSH0 = C.CFLSH0 and A.YPNM00 = B.YPNM00 --and B.ZJBZ00 ='1' and B.YKBMBH=166 and C.FYRQ00>=as_KSRQ00 and C.FYRQ00<=as_JSRQ00 and C.GHKS00 in (select BMBH00 from BM_BMBM00 k where (K.YYID00=as_YYID00 OR as_YYID00='-1')) and (c.GHKS00 = as_KDKS00 or as_KDKS00 ='-1') -- and SF_SF_GetZCGZR(C.FYRQ00,c.FYSJ00)=as_TJFS00 and C.YSGZH0=e.YGBH00(+) and (e.YSPQLB=as_YSLB00 or as_YSLB00='-1') group by ad_ID0000,C.YSGZH0,c.GHKS00; --会诊费：按所开会诊费全给。（主任及副主任才有会诊费，且要判断开单科室与该医生与属科室要为不同科室才可以收取） --会诊费判断（select sfxmid from bm_yysfxm where bh0000 like ‘01110%’） insert into BM_YYSFTJ(ID0000,BMBH00,SFXM31,SFXM99,SFXM200) select ad_ID0000,A.ZXYS00,sum(A.HJJE00)HJJE00,1,a.KDKS00 from SF_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,SF_BRFY00 D,BM_YGBM00 E where A.XMBH00 = C.SFXMID and C.MZFPID = B.FPXMID and A.DJH000 = D.DJH000 and A.ZXYS00=e.ygbh00(+) and e.YSLB00 in (select YSLB00 from BM_YSLB00 where (yslbmc like '主任%') or (yslbmc like '副主任%')) and a.KDKS00<>e.BMBH00 and a.KDKS00 in (select BMBH00 from BM_BMBM00 k where (K.YYID00=as_YYID00 OR as_YYID00='-1')) and d.CZRQ00>=as_KSRQ00 and d.CZRQ00<=as_JSRQ00 and (a.kdks00 = as_KDKS00 or as_KDKS00 ='-1') --and (e.BMBH00=as_KDKS00 or as_KDKS00 ='-1') and SF_SF_GetZCGZR(D.CZRQ00,D.CZSJ00)=as_TJFS00 and c.BH0000||'' like '01110%' and (e.YSPQLB=as_YSLB00 or as_YSLB00='-1') group by ad_ID0000,A.ZXYS00,a.kdks00 union all select ad_ID0000,A.zxys00,sum(A.HJJE00)HJJE00,1,a.KDKS00 from ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,ZY_BRFY00 D,BM_YGBM00 E where A.XMBH00 = C.SFXMID and C.ZYFPID = B.FPXMID and A.DJH000 = D.DJH000 and A.ZXYS00=e.YGBH00(+) and e.YSLB00 in (select YSLB00 from BM_YSLB00 where (YSLBMC like '主任%') or (yslbmc like '副主任%')) and a.KDKS00<>e.BMBH00 and d.CZRQ00>=as_KSRQ00 and d.CZRQ00<=as_JSRQ00 and a.KDKS00 in (select BMBH00 from BM_BMBM00 k where (K.YYID00=as_YYID00 OR as_YYID00='-1')) and (a.kdks00 = as_KDKS00 or as_KDKS00 ='-1') and SF_SF_GetZCGZR(D.CZRQ00,D.CZSJ00)=as_TJFS00 and c.BH0000||'' like '01110%' and (e.YSPQLB=as_YSLB00 or as_YSLB00='-1') group by ad_ID0000,A.ZXYS00,A.KDKS00; /* --诊疗费用：发票项目为06:诊察费 IF lv_FPBH00 IN ('06') then Update BM_YYSFTJ Set SFXM41 = NVL(SFXM41,0)+lv_HJJE00 where ID0000=ad_ID0000 and BMBH00=lv_kdys00 and SFXM99=11; if SQL%NOTFOUND then Insert into BM_YYSFTJ(ID0000,BMBH00,SFXM41,SFXM99) values(ad_ID0000,lv_kdys00,NVL(lv_HJJE00,0),11); end if; end IF; */ --诊疗费的补助按该医生就诊时给病人挂号的类别来计算，主任1.8元、副主任1.2元、主治及以下0.6元， --院外聘人员按收取诊疗费用的50%来补助。 insert into BM_YYSFTJ(ID0000,BMBH00,SFXM01,SFXM66,SFXM41,SFXM55,SFXM99,SFXM200) select ad_ID0000,A.KDYS00,sign(A.HJJE00),e.GHLB00,sum(A.HJJE00)HJJE00,count(distinct E.GHID00),11,a.KDKS00 from SF_FYMX00 A,SF_BRFY00 D,SF_BRXXB0 E,BM_YYSFXM C,BM_YGBM00 F,BM_FPXM00 B where A.XMBH00 = C.SFXMID and C.MZFPID = B.FPXMID and A.DJH000 = D.DJH000 and d.MZID00 = e.GHID00(+) and B.BH0000||'' in ('06') and A.HJJE00<>0 and A.HJJE00<>1 and d.CZRQ00>=as_KSRQ00 and d.CZRQ00<=as_JSRQ00 and a.KDKS00 in (select BMBH00 from BM_BMBM00 k where (K.yyid00=as_YYID00 or as_YYID00='-1')) and (a.kdks00 = as_KDKS00 or as_KDKS00 ='-1') and SF_SF_GetZCGZR(D.CZRQ00,D.CZSJ00)=as_TJFS00 and exists (select 1 from SF_FJGHF0 where FJFFBH=A.XMBH00) and A.KDYS00=F.YGBH00(+) and (F.YSPQLB=as_YSLB00 or as_YSLB00='-1') group by ad_ID0000,A.KDYS00,sign(A.HJJE00),e.GHLB00,a.KDKS00; insert into BM_YYSFTJ(ID0000,BMBH00,SFXM66,SFXM41,SFXM55,SFXM99,SFXM200) select ID0000,BMBH00,SFXM66,sum(SFXM41),sum(SFXM01*SFXM55),111,SFXM200 from BM_YYSFTJ where id0000=ad_ID0000 and SFXM99=11 group by ID0000,BMBH00,SFXM66,SFXM200; delete BM_YYSFTJ where id0000=ad_ID0000 and SFXM99=11; --SFXM66:挂号类别 SFXM55：挂号次数 insert into BM_YYSFTJ(ID0000,BMBH00,SFXM41,SFXM42,SFXM99,SFXM200) select ID0000,BMBH00,SFXM41,SF_SF_GETJJTCJE(BMBH00,NVL(SFXM41,0),SFXM66,1,nvl(SFXM55,0),as_TJFS00),1,SFXM200 from BM_YYSFTJ where id0000=ad_ID0000 and SFXM99=111; delete BM_YYSFTJ where id0000=ad_ID0000 and SFXM99=111; insert into BM_YYSFTJ(ID0000,BMBH00, SFXM01,SFXM02, SFXM11,SFXM12, SFXM21,SFXM22, SFXM31,SFXM32, SFXM41,SFXM42, SFXM51,SFXM52, SFXM61,SFXM62, SFXM71,SFXM72, SFXM81,SFXM82, XMHJ00,SFXM99, SFXM200) select ID0000,BMBH00, sum(nvl(SFXM01,0)),sum(nvl(SFXM02,0)), sum(nvl(SFXM11,0)),sum(nvl(SFXM12,0)), sum(nvl(SFXM21,0)),sum(nvl(SFXM22,0)), sum(nvl(SFXM31,0)),sum(nvl(SFXM32,0)), sum(nvl(SFXM41,0)),sum(nvl(SFXM42,0)), sum(nvl(SFXM51,0)),sum(nvl(SFXM52,0)), sum(nvl(SFXM61,0)),sum(nvl(SFXM62,0)), sum(nvl(SFXM71,0)),sum(nvl(SFXM72,0)), sum(nvl(SFXM81,0)),sum(nvl(SFXM82,0)), sum(nvl(SFXM01,0)+nvl(SFXM11,0)+nvl(SFXM21,0)+nvl(SFXM31,0)+nvl(SFXM41,0)),2,SFXM200 from BM_YYSFTJ where ID0000=ad_ID0000 and SFXM99=1 group by ID0000,BMBH00,SFXM200; delete BM_YYSFTJ where ID0000=ad_ID0000 and SFXM99=1; insert into BM_YYSFTJ(ID0000,BMBH00, SFXM01,SFXM02, SFXM11,SFXM12, SFXM21,SFXM22, SFXM31,SFXM32, SFXM41,SFXM42, SFXM51,SFXM52, SFXM61,SFXM62, SFXM71,SFXM72, SFXM81,SFXM82, XMHJ00,SFXM99, SFXM200) select ID0000,BMBH00, SFXM01,round(SFXM01*0.05), --检查费：按医生开单检查费用的5%来计算。 SFXM11,SF_SF_GETJJTCJE(BMBH00,NVL(SFXM11,0),0,0,0,as_TJFS00), --治疗费：按开单治疗费用的10%来计算。（节假日才有，正常没有。外聘的都有）SF_SF_KSGDBL.JJTCBL SFXM21,round(SFXM21*0.05), --按所开制剂药品费用的5%来计算 SFXM31,round(SFXM31*1), --按所开会诊费全给 SFXM41,round(SFXM42),--诊疗费的补助按该医生就诊时给病人挂号的类别来计算，主任1.8元、副主任1.2元、主治及以下0.6元，院外聘人员按收取诊疗费用的50%来补助。 SFXM51,SFXM52, SFXM61,SFXM62, SFXM71,SFXM72, SFXM81,SFXM82, XMHJ00,9,SFXM200 from BM_YYSFTJ where ID0000=ad_ID0000 and SFXM99=2; --费用合计 update BM_YYSFTJ a set SFXM111= (select SFXM01+SFXM11+SFXM21+SFXM31+SFXM41+SFXM51+SFXM61+SFXM71+SFXM81 from BM_YYSFTJ where a.BMBH00=BMBH00 and ID0000=a.ID0000 and SFXM99=9 and SFXM200=a.SFXM200) where ID0000=ad_ID0000 and SFXM99=9; --奖金合计 update BM_YYSFTJ a set SFXM112= (select SFXM02+SFXM12+SFXM22+SFXM32+SFXM42+SFXM52+SFXM62+SFXM72+SFXM82 from BM_YYSFTJ where a.BMBH00=BMBH00 and ID0000=a.ID0000 and SFXM99=9 and SFXM200=a.SFXM200) where ID0000=ad_ID0000 and SFXM99=9; delete BM_YYSFTJ where ID0000=ad_ID0000 and SFXM99=2; update BM_YYSFTJ SET XMHJ00=NVL(SFXM111,0)+NVL(SFXM112,0) where ID0000=ad_ID0000; --修改医生对应的科室 --update BM_YYSFTJ A SET SFXM200=(select B.BMBH00 from BM_YGBM00 B where B.YGBH00=A.BMBH00) --where A.ID0000=ad_ID0000 and A.BMBH00>0; --修改明细数据标识 SFXM201=0 明细 SFXM201=1 科室汇总 SFXM201=2 全部汇总 update BM_YYSFTJ A set SFXM201=0 where ID0000=ad_ID0000; --插入科室合计 insert into BM_YYSFTJ (ID0000,BMBH00,XMHJ00,SFXM200,SFXM201,SFXM01,SFXM02,SFXM11,SFXM12,SFXM21,SFXM22,SFXM31,SFXM32,SFXM41,SFXM42,SFXM51,SFXM52,SFXM61, SFXM62,SFXM71,SFXM72,SFXM81,SFXM82,SFXM111,SFXM112) select ad_ID0000,0,sum(XMHJ00),SFXM200,1,sum(SFXM01),sum(SFXM02),sum(SFXM11),sum(SFXM12),sum(SFXM21),sum(SFXM22),sum(SFXM31),sum(SFXM32), sum(SFXM41),sum(SFXM42),sum(SFXM51),sum(SFXM52),sum(SFXM61),sum(SFXM62),sum(SFXM71),sum(SFXM72),sum(SFXM81),sum(SFXM82),sum(SFXM111), sum(SFXM112) from BM_YYSFTJ where ID0000=ad_ID0000 and BMBH00>0 group by SFXM200; --插入总合计 insert into BM_YYSFTJ (ID0000,BMBH00,XMHJ00,SFXM200,SFXM201,SFXM01,SFXM02,SFXM11,SFXM12,SFXM21,SFXM22,SFXM31,SFXM32,SFXM41,SFXM42,SFXM51,SFXM52,SFXM61, SFXM62,SFXM71,SFXM72,SFXM81,SFXM82,SFXM111,SFXM112) select ad_ID0000,0,sum(XMHJ00),0,2,sum(SFXM01),sum(SFXM02),sum(SFXM11),sum(SFXM12),sum(SFXM21),sum(SFXM22),sum(SFXM31),sum(SFXM32), sum(SFXM41),sum(SFXM42),sum(SFXM51),sum(SFXM52),sum(SFXM61),sum(SFXM62),sum(SFXM71),sum(SFXM72),sum(SFXM81),sum(SFXM82),sum(SFXM111), sum(SFXM112) from BM_YYSFTJ where ID0000=ad_ID0000 and SFXM201=0; --commit; exception when OTHERS then as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_CWJJSHTJ('||as_KSRQ00||','||as_JSRQ00|| ','||as_YSLB00||','||as_KDKS00||','||as_TJFS00|| 'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); rollback; end; / -- End of DDL script for SP_SF_CWJJSHTJ