prompt 77、在院病人(财务)费用统计报表过程 SP_ZY_ZYYYF0_CWTJ00 create or replace procedure SP_ZY_ZYYYF0_CWTJ00 ( as_JSRQ00 in char, --在院日期 as_JSSJ00 in char, --在院时间 as_YYID00 in char, --医院ID >0 各分院 =0全部 ad_ID0000 out number, --统计报表对应的ID as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar --系统提示的错误信息 ) -- MODIFICATION HISTORY -- Person Date Comments -- jlg 2019.08.12 created 在院病人费用统计报表过程 ZYSF9-20190805-005 -- jlg 2019.09.06 处理按病人显示没有按分院过滤的记录 ZYSF9-20190904-009 -- qks 2020.03.07 要求增加发票项目编号(BM_FPXM00.BH0000)为24,25相关费用统计; for ZYSF9-20200308-001 -- qks 2021.12.07 优化查询统计速度:1、不写入ZY_ZYBRTJ表,改为写入BM_YYSFTJ;2、未结算使用ZY_FYMX00.JZDH00字段索引;3、原来按游标统计速度太慢,取消掉了; for ZYSF9-20211207-002 -- chenHeyi 2024.04.24 由于中间表的BMBH00按ZY_BRXXB0更新当前病区，所以在修改病人预交总额时去掉病区条件(病人有转病区的情况） as ls_XMBH00 ZY_FYMX00.XMBH00%type; ls_BQH000 ZY_BRXXB0.DQBQ00%type; ls_ZYID00 ZY_BRXXB0.ZYID00%type; ls_BH0000 BM_FPXM00.BH0000%type; ls_HJJE00 ZY_FYMX00.HJJE00%type; ls_ID0000 BM_YYSFTJ.ID0000%type; LS_CXCS00 number(5); begin select SQ_BM_YYSFTJ_ID0000.nextval into ls_ID0000 from dual; select SQ_BM_YYSFTJ_ID0000.nextval into ad_ID0000 from dual; --1:已经结算 insert into BM_YYSFTJ(BMBH00,XMHJ00,ID0000,SFXM99,BZSM01,BZSM02) select A.KDBQ00,sum(A.HJJE00*f.BZ0000),ls_ID0000,a.ZYID00,C.BH0000,'1' from ZY_FYMX00 A,BM_FPXM00 C,ZY_JZB000 E,ZY_JZMXXM F where a.ZYFPID = c.FPXMID and e.JZDH00 = f.JZDH00 and a.MXID00 = f.MXID00 and C.SYBZ00 <> '1' and a.CZRQ00||'0' <= as_JSRQ00||'0' and a.CZRQ00||a.CZSJ00 <= as_JSRQ00||as_JSSJ00 and e.JZRQ00 >= as_JSRQ00 and e.JZRQ00||e.JZSJ00 > as_JSRQ00||as_JSSJ00 and a.KDBQ00 in (select BMBH00 from BM_BMBM00 where (YYID00=as_YYID00 or as_YYID00='0')) group by a.KDBQ00,a.ZYID00,C.BH0000; --2:未结算 a.JZDH00+0=0 -> a.JZDH00=0 insert into BM_YYSFTJ(BMBH00,XMHJ00,ID0000,SFXM99,BZSM01,BZSM02) select A.KDBQ00,sum(a.HJJE00),ls_ID0000,a.ZYID00,C.BH0000,'1' from ZY_FYMX00 A,BM_FPXM00 C where C.FPXMID = a.ZYFPID and C.SYBZ00 <> '1' and a.CZRQ00||'0' <= as_JSRQ00||'0' and a.CZRQ00||a.CZSJ00 <= as_JSRQ00||as_JSSJ00 and a.JZDH00=0 and a.KDBQ00 in (select BMBH00 from BM_BMBM00 where (YYID00=as_YYID00 or as_YYID00='0')) group by a.KDBQ00,a.ZYID00,C.BH0000; --3：更新当前病区 --update ZY_ZYBRTJ a set a.DQBQ00=nvl((select b.DQBQ00 from zy_brxxb0 b where b.zyid00=a.zyid00 ),0) where a.ID0000=ad_ID0000; update BM_YYSFTJ a set BMBH00=nvl((select b.DQBQ00 from zy_brxxb0 b where b.zyid00=a.SFXM99),0) where a.ID0000=ls_ID0000; --4:写入BM_YYSFTJ.SFXM01--SFXM25 insert into BM_YYSFTJ(BMBH00,SFXM01,SFXM02,SFXM03,SFXM04,SFXM05, SFXM06,SFXM07,SFXM08,SFXM09,SFXM10, SFXM11,SFXM12,SFXM13,SFXM14,SFXM15, SFXM16,SFXM17,SFXM18,SFXM19,SFXM20, SFXM21,SFXM22,SFXM23,SFXM24,SFXM25, ID0000,XMHJ00,SFXM99,BZSM02) select BMBH00,decode(BZSM01,'01',sum(XMHJ00),0),decode(BZSM01,'02',sum(XMHJ00),0),decode(BZSM01,'03',sum(XMHJ00),0),decode(BZSM01,'04',sum(XMHJ00),0),decode(BZSM01,'05',sum(XMHJ00),0), decode(BZSM01,'06',sum(XMHJ00),0),decode(BZSM01,'07',sum(XMHJ00),0),decode(BZSM01,'08',sum(XMHJ00),0),decode(BZSM01,'09',sum(XMHJ00),0),decode(BZSM01,'10',sum(XMHJ00),0), decode(BZSM01,'11',sum(XMHJ00),0),decode(BZSM01,'12',sum(XMHJ00),0),decode(BZSM01,'13',sum(XMHJ00),0),decode(BZSM01,'14',sum(XMHJ00),0),decode(BZSM01,'15',sum(XMHJ00),0), decode(BZSM01,'16',sum(XMHJ00),0),decode(BZSM01,'17',sum(XMHJ00),0),decode(BZSM01,'18',sum(XMHJ00),0),decode(BZSM01,'19',sum(XMHJ00),0),decode(BZSM01,'20',sum(XMHJ00),0), decode(BZSM01,'21',sum(XMHJ00),0),decode(BZSM01,'22',sum(XMHJ00),0),decode(BZSM01,'23',sum(XMHJ00),0),decode(BZSM01,'24',sum(XMHJ00),0),decode(BZSM01,'25',sum(XMHJ00),0), ls_ID0000,sum(XMHJ00),SFXM99,'2' from BM_YYSFTJ where ID0000=ls_ID0000 group by BMBH00,SFXM99,BZSM01; --5:按住院病人汇总数据。原来按游标统计速度太慢,取消掉了。 insert into BM_YYSFTJ(BMBH00,SFXM01,SFXM02,SFXM03,SFXM04,SFXM05, SFXM06,SFXM07,SFXM08,SFXM09,SFXM10, SFXM11,SFXM12,SFXM13,SFXM14,SFXM15, SFXM16,SFXM17,SFXM18,SFXM19,SFXM20, SFXM21,SFXM22,SFXM23,SFXM24,SFXM25, ID0000,XMHJ00,SFXM99) select BMBH00,sum(SFXM01),sum(SFXM02),sum(SFXM03),sum(SFXM04),sum(SFXM05), sum(SFXM06),sum(SFXM07),sum(SFXM08),sum(SFXM09),sum(SFXM10), sum(SFXM11),sum(SFXM12),sum(SFXM13),sum(SFXM14),sum(SFXM15), sum(SFXM16),sum(SFXM17),sum(SFXM18),sum(SFXM19),sum(SFXM20), sum(SFXM21),sum(SFXM22),sum(SFXM23),sum(SFXM24),sum(SFXM25), ad_ID0000,sum(XMHJ00),SFXM99 from BM_YYSFTJ where ID0000=ls_ID0000 and nvl(BZSM02,'0')='2' group by BMBH00,SFXM99; --6:删除临时统计 ID0000=ls_ID0000 delete from BM_YYSFTJ where ID0000=ls_ID0000; delete from BM_YYSFTJ where ID0000 = ad_ID0000 and XMHJ00 = 0; --修改病人预交总额 上面3:按zy_brxxb0更新当前病区，这里去病区条件 update BM_YYSFTJ A set SFXM97=(select nvl(sum(JFJE00),0) from ZY_BRJFB0 where ZYID00=A.SFXM99 --and DQBQ00=a.BMBH00 and PLBH00 in (4,8) and JFRQ00||'' <= as_JSRQ00 and JFRQ00||JFSJ00 <= as_JSRQ00||as_JSSJ00 and nvl(JSLX00,'0')<>'3') where A.ID0000=ad_ID0000; /* --增加预交金 insert into BM_YYSFTJ(ID0000,BMBH00,SFXM99,SFXM97,XMHJ00,YPHJ00) select ad_ID0000,A.DQBQ00,A.ZYID00,sum(B.JFJE00),0,-1 from ZY_BRXXB0 A,ZY_BRJFB0 B where A.ZYID00=B.ZYID00 and B.PLBH00 in (4,8) and B.JFRQ00 <= AS_JSRQ00 and B.JFRQ00||B.JFSJ00 <= AS_JSRQ00||AS_JSSJ00 and A.ZYID00 not in (select SFXM99 from BM_YYSFTJ where ID0000=ad_ID0000) group by A.DQBQ00,A.ZYID00; --回收金额 insert into BM_YYSFTJ(ID0000,BMBH00,SFXM99,SFXM97,XMHJ00,YPHJ00) select ad_ID0000,A.DQBQ00,A.ZYID00,-sum(B.JFJE00),0,-1 from ZY_BRXXB0 A,ZY_BRJFB0 B,ZY_PJSYQK C where A.ZYID00=B.ZYID00 and B.PJH000=c.PJH000 and B.PLBH00 in (4,8) and C.SFDY00= 'Y' and b.JZDH00+0>2 and C.HSRQ00 <= AS_JSRQ00 and C.HSRQ00||C.HSSJ00 <= AS_JSRQ00||AS_JSSJ00 and A.ZYID00 in (select SFXM99 from BM_YYSFTJ where ID0000=ad_ID0000 and YPHJ00=-1) group by A.DQBQ00,A.ZYID00; */ insert into BM_YYSFTJ(ID0000,BMBH00,SFXM99,SFXM97,XMHJ00) select ID0000,BMBH00,SFXM99,sum(SFXM97),sum(XMHJ00) from BM_YYSFTJ where ID0000=ad_ID0000 and YPHJ00=-1 group by ID0000,BMBH00,SFXM99; delete BM_YYSFTJ BM_YYSFTJ where ID0000=ad_ID0000 and YPHJ00=-1; insert into BM_YYSFTJ(ID0000,BMBH00,SFXM01,SFXM02,SFXM03,SFXM04,SFXM05,SFXM06,SFXM07,SFXM08,SFXM09,SFXM10,SFXM11,SFXM12,SFXM13, SFXM14,SFXM15,SFXM16,SFXM17,SFXM18,SFXM19,SFXM20,SFXM21,SFXM22,SFXM23,SFXM24,SFXM25,XMHJ00,SFXM97) select ad_ID0000,9999999999,sum(SFXM01),sum(SFXM02),sum(SFXM03),sum(SFXM04),sum(SFXM05),sum(SFXM06),sum(SFXM07),sum(SFXM08),sum(SFXM09),sum(SFXM10),sum(SFXM11),sum(SFXM12),sum(SFXM13), sum(SFXM14),sum(SFXM15),sum(SFXM16),sum(SFXM17),sum(SFXM18),sum(SFXM19),sum(SFXM20),sum(SFXM21),sum(SFXM22),sum(SFXM23),sum(SFXM24),sum(SFXM25),sum(XMHJ00),SUM(SFXM97) from BM_YYSFTJ where ID0000 = ad_ID0000; delete BM_YYSFTJ BM_YYSFTJ where ID0000=ad_ID0000 and nvl(SFXM97,0)=0 and nvl(XMHJ00,0)=0; commit; exception when others then as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_ZYYYF0_CWTJ00('||as_JSRQ00||','||as_JSSJ00||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); rollback; end; / show error; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%