create or replace procedure SP_ZH_YYSFTJ_DXTZ as ls_COUNT0 number(5); ls_COUNT1 number(5); ls_GHKS00 SF_BRXXB0.GHKS00%type; ls_TJID00 number(3); ad_ID0000 number; --数据发送 ls_SJFS00 varchar2(20); as_KSRQ00 varchar2(8); as_JSRQ00 varchar2(8); as_MZNR00 varchar2(4000); as_ZYNR00 varchar2(4000); as_QYNR00 varchar2(4000); as_DXNR00 varchar2(4000); as_BMMC00 varchar2(20); ls_SSLS00 number(10); cursor CUR_SF_MZGHL0 is --门诊就诊人次 select GHKS00,sum(GHCS00) GHCS00 from VW_SF_GHLSXX where CZRQ00 >= as_KSRQ00 and CZRQ00 <= as_JSRQ00 group by GHKS00; cursor CUR_MZ_FYHZ is --门诊总收入 select a.KDKS00,sum(a.HJJE00) MZZJE0 from SF_FYMX00 a where a.CZRQ00>= as_KSRQ00 and a.CZRQ00 <= as_JSRQ00 and a.KDKS00 is not null group by a.KDKS00; cursor CUR_ZY_ZYRS is --在院人数 select ksh000 dqks00,count( distinct ZYID00) zyrs00 from BQ_BRLDXX where QSZT00 in ('新入','转入') and YXBZ00='1' and QSRQ00<=as_JSRQ00 and BQJSRQ > as_KSRQ00 group by ksh000; -- select a.DQKS00, count(ZYID00) ZYRS00 -- from ZY_BRXXB0 a -- where RYRQ00<=as_JSRQ00 and substr(BRZT00,1,1) in ('1','2','3','4','5') and nvl(CYRQ00,to_char(SYSDATE+1,'YYYYMMDD'))>as_JSRQ00 -- group by a.DQKS00; cursor CUR_ZY_CYRS is --出院人数 select b.DQKS00, COUNT (distinct a.ZYID00) CYRS00 from BQ_BRLDXX a,ZY_BRXXB0 b where JSZT00='出院' and YXBZ00='1' and JSRQ00<=as_JSRQ00 and JSRQ00>=as_KSRQ00 and a.ZYID00=b.ZYID00 and (upper(substr(b.ZYH000,1,1)) not in ('F','M')) group by b.DQKS00; cursor CUR_ZY_FYHZ is --住院总收入 select a.KDKS00,sum(a.HJJE00) ZYZJE0 from ZY_FYMX00 a where a.CZRQ00>= as_KSRQ00 and a.CZRQ00 <= as_JSRQ00 and a.KDKS00 is not null group by a.KDKS00; cursor CUR_KRBZL is --科日报整理 select c.BH0000,f.DH0000,f.ZWXM00 from BM_TYZD00 c, BM_YGBM00 f where c.MC0000=to_char(f.ygbh00) and ZDMC00='医院收费统计短信通知名单' and YXBZ00='1' and f.DH0000 is not null; lv_MZ_FYHZ CUR_MZ_FYHZ%rowtype; lv_ZY_ZYRS CUR_ZY_ZYRS%rowtype; lv_ZY_CYRS CUR_ZY_CYRS%rowtype; lv_ZY_FYHZ CUR_ZY_FYHZ%rowtype; lv_KRBZL CUR_KRBZL%rowtype; v_SSLSSF char(1); -- MODIFICATION HISTORY -- Person Date Comments -- pwt 2019.04.19 create 医院收费短信通知过程 for ZHCX-20190402-001 -- pwt 2019.05.07 3条汇总成1条发送 for ZHCX-20190507-001 -- qiulf 2019.05.23 增加全院收入，手术例数 for ZHCX-20190513-001 begin delete from BM_YYSFTJ_DXTZ; --commit; --手术例数算法参数 select substrb(nvl(trim(upper(max(Value0))),'0'),1,1) into v_SSLSSF from XT_XTCS00 where name00='ZH_SSLSSF'; for ls_TJID00 in 1..3 loop ls_SJFS00 := 'N'; if ls_TJID00=1 then ad_ID0000:=1; as_KSRQ00:=to_char(sysdate-1,'YYYYMMDD'); as_JSRQ00:=to_char(sysdate-1,'YYYYMMDD'); ls_SJFS00 := 'Y'; elsif (ls_TJID00=2) and (to_char(sysdate-1,'dd')<>'01') then ad_ID0000:=2; as_KSRQ00:=to_char(last_day(add_months(sysdate-1,-1))+1,'YYYYMMDD'); as_JSRQ00:=to_char(sysdate-1,'YYYYMMDD'); ls_SJFS00 := 'Y'; elsif ls_TJID00=3 then ad_ID0000:=3; as_KSRQ00:=to_char(last_day(add_months(sysdate-1,-13))+1,'YYYYMMDD'); as_JSRQ00:=to_char(add_months(sysdate-1,-12),'YYYYMMDD'); ls_SJFS00 := 'Y'; end if; if ls_SJFS00='Y' then --门诊就诊人次 open CUR_SF_MZGHL0; loop fetch CUR_SF_MZGHL0 into ls_GHKS00,ls_COUNT0; exit when CUR_SF_MZGHL0%notfound; update BM_YYSFTJ_DXTZ set MZGHL0 = nvl(MZGHL0,0)+ls_COUNT0 where BMBH00 = ls_GHKS00 and ID0000=ad_ID0000; if sql%notfound then insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,MZGHL0) values(ad_ID0000,ls_GHKS00,ls_COUNT0); end if; end loop; close CUR_SF_MZGHL0; --门诊总收入 open CUR_MZ_FYHZ; loop fetch CUR_MZ_FYHZ into lv_MZ_FYHZ; exit when CUR_MZ_FYHZ%notfound; update BM_YYSFTJ_DXTZ set MZZJE0 = nvl(MZZJE0,0)+lv_MZ_FYHZ.MZZJE0 where BMBH00 = lv_MZ_FYHZ.KDKS00 and ID0000=ad_ID0000; if sql%notfound then insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,MZZJE0) values(ad_ID0000,lv_MZ_FYHZ.KDKS00,lv_MZ_FYHZ.MZZJE0); end if; end loop; close CUR_MZ_FYHZ; --在院人数 open CUR_ZY_ZYRS; loop fetch CUR_ZY_ZYRS into lv_ZY_ZYRS; exit when CUR_ZY_ZYRS%notfound; update BM_YYSFTJ_DXTZ set ZYRS00 = nvl(ZYRS00,0)+lv_ZY_ZYRS.ZYRS00 where BMBH00 = lv_ZY_ZYRS.DQKS00 and ID0000=ad_ID0000; if sql%notfound then insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,ZYRS00) values(ad_ID0000,lv_ZY_ZYRS.DQKS00,lv_ZY_ZYRS.ZYRS00); end if; end loop; close CUR_ZY_ZYRS; --出院人数 open CUR_ZY_CYRS; loop fetch CUR_ZY_CYRS into lv_ZY_CYRS; exit when CUR_ZY_CYRS%notfound; update BM_YYSFTJ_DXTZ set CYRS00 = nvl(CYRS00,0)+lv_ZY_CYRS.CYRS00 where BMBH00 = lv_ZY_CYRS.DQKS00 and ID0000=ad_ID0000; if sql%notfound then insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,CYRS00) values(ad_ID0000,lv_ZY_CYRS.DQKS00,lv_ZY_CYRS.CYRS00); end if; end loop; close CUR_ZY_CYRS; --住院总收入 open CUR_ZY_FYHZ; loop fetch CUR_ZY_FYHZ into lv_ZY_FYHZ; exit when CUR_ZY_FYHZ%notfound; update BM_YYSFTJ_DXTZ set ZYZJE0 = NVL(ZYZJE0,0)+lv_ZY_FYHZ.ZYZJE0 where BMBH00 = lv_ZY_FYHZ.KDKS00 and ID0000=ad_ID0000; if sql%notfound then insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,ZYZJE0) values(ad_ID0000,lv_ZY_FYHZ.KDKS00,lv_ZY_FYHZ.ZYZJE0); end if; end loop; close CUR_ZY_FYHZ; --手术例数 if v_SSLSSF='1' then select count(1) into ls_SSLS00 from (select count(BRID00) from YJ_YW0000 A where KDRQ00>= as_KSRQ00 and KDRQ00<=as_JSRQ00 and SFDJH0>0 and exists (select 1 from VW_BM_ZLZD00 B where LBBH00='6' and b.ZLXMID = a.ZLXMID ) group by BRID00 having sum(SL0000)>0); elsif v_SSLSSF='2' then select count(1) into ls_SSLS00 from VW_SS_YWJJ00 b, SS_SSYJD0 c, VW_SS_YW0000 d where b.YJDJH0=c.YJDJH0 and c.SSDH00=d.SSDH00 and d.SSQSRQ >= as_KSRQ00 and d.SSQSRQ <= as_JSRQ00 and (not exists (select 'y' from YJ_YW0000 a where a.CXDJH0=b.YJDJH0)) and ((b.CXDJH0=0) or (b.CXDJH0 is null)) and b.KDKSBH not in (select BMBH00 from VW_BM_SSMZBM) and b.XMZT00 in ('2','4','3'); elsif v_SSLSSF='3' then select count(1) into ls_SSLS00 from VW_SS_YW0000 where SSQSRQ>=as_KSRQ00 and SSQSRQ<= as_JSRQ00 and SSZT00='4'; else --'0' select count(distinct BRID00) into ls_SSLS00 from VW_SS_YW0000 where SSQSRQ>=as_KSRQ00 and SSQSRQ<= as_JSRQ00 and SSZT00 not in ('1','3') and SSFYBZ='2'; end if; insert into BM_YYSFTJ_DXTZ(ID0000,BMBH00,SSLS00) values(ad_ID0000,0,ls_SSLS00); if ls_TJID00=1 then update BM_YYSFTJ_DXTZ set TJKSRQ=to_char(sysdate-1,'YYYYMMDD'),TJJSRQ=to_char(sysdate-1,'YYYYMMDD') where ID0000=ad_ID0000; elsif ls_TJID00=2 then update BM_YYSFTJ_DXTZ set TJKSRQ=to_char(last_day(add_months(sysdate-1,-1))+1,'YYYYMMDD'),TJJSRQ=to_char(sysdate-1,'YYYYMMDD') where ID0000=ad_ID0000; elsif ls_TJID00=3 then update BM_YYSFTJ_DXTZ set TJKSRQ=to_char(last_day(add_months(sysdate-1,-13))+1,'YYYYMMDD'),TJJSRQ=to_char(add_months(sysdate-1,-12),'YYYYMMDD') where ID0000=ad_ID0000; end if; end if; end loop; --commit; open CUR_KRBZL; loop fetch CUR_KRBZL into lv_KRBZL; exit when CUR_KRBZL%notfound; --科室短信整理 if lv_KRBZL.BH0000 <> '0' then --当日 select count(*) into ls_COUNT1 from BM_BMBM00 where BMBH00=substr(lv_KRBZL.BH0000,1,instr(lv_KRBZL.BH0000, ',')-1); if ls_COUNT1 > 0 then select count(*) into ls_COUNT0 from BM_YYSFTJ_DXTZ where BMBH00=substr(lv_KRBZL.BH0000,1,instr(lv_KRBZL.BH0000, ',')-1) and ID0000=1; if ls_COUNT0 > 0 then select '<'||to_char(to_number(substr(a.TJKSRQ,1,4)))||'年'||to_char(to_number(substr(a.TJKSRQ,5,2)))||'月'||to_char(to_number(substr(a.TJKSRQ,7,2)))||'日'|| b.BMMC00||' 日报>'||'门诊:门诊就诊人次:'||nvl(to_char(a.MZGHL0),'0')||',门诊总收入:'||nvl(to_char(a.MZZJE0,'fm9999990.9999'),'0')||'; ' into as_MZNR00 from BM_YYSFTJ_DXTZ a,BM_BMBM00 b where a.BMBH00=b.BMBH00 and a.BMBH00=substr(lv_KRBZL.BH0000,1,instr(lv_KRBZL.BH0000, ',')-1) and a.ID0000=1; else select BMMC00 into as_BMMC00 from BM_BMBM00 where BMBH00=substr(lv_KRBZL.BH0000,1,instr(lv_KRBZL.BH0000, ',')-1); as_MZNR00:= '<'||to_char(to_number(substr(to_char(sysdate-1,'YYYYMMDD'),1,4)))||'年'||to_char(to_number(substr(to_char(sysdate-1,'YYYYMMDD'),5,2)))||'月'||to_char(to_number(substr(to_char(sysdate-1,'YYYYMMDD'),7,2)))||'日'|| as_BMMC00||' 日报>'||'门诊:门诊就诊人次:0,门诊总收入:0; '; end if; end if; select count(*) into ls_COUNT1 from BM_BMBM00 where BMBH00=substr(lv_KRBZL.BH0000,instr(lv_KRBZL.BH0000, ',')+1); if ls_COUNT1 > 0 then select count(*) into ls_COUNT0 from BM_YYSFTJ_DXTZ where BMBH00=substr(lv_KRBZL.BH0000,instr(lv_KRBZL.BH0000, ',')+1) and ID0000=1; if ls_COUNT0 > 0 then select '<'||to_char(to_number(substr(a.TJKSRQ,1,4)))||'年'||to_char(to_number(substr(a.TJKSRQ,5,2)))||'月'||to_char(to_number(substr(a.TJKSRQ,7,2)))||'日'|| b.BMMC00||' 日报>'||'住院：在院人数:'||nvl(to_char(ZYRS00),'0')||',出院人数:'||nvl(to_char(CYRS00),'0')||',住院总收入:'||nvl(to_char(ZYZJE0,'fm9999990.9999'),'0') into as_ZYNR00 from BM_YYSFTJ_DXTZ a,BM_BMBM00 b where a.BMBH00=b.BMBH00 and a.BMBH00=substr(lv_KRBZL.BH0000,instr(lv_KRBZL.BH0000, ',')+1) and ID0000=1; else select BMMC00 into as_BMMC00 from BM_BMBM00 where BMBH00=substr(lv_KRBZL.BH0000,instr(lv_KRBZL.BH0000, ',')+1); as_ZYNR00:= '<'||to_char(to_number(substr(to_char(sysdate-1,'YYYYMMDD'),1,4)))||'年'||to_char(to_number(substr(to_char(sysdate-1,'YYYYMMDD'),5,2)))||'月'||to_char(to_number(substr(to_char(sysdate-1,'YYYYMMDD'),7,2)))||'日'|| as_BMMC00||' 日报>'||'住院：在院人数:0,出院人数:0,住院总收入:0; '; end if; end if; as_DXNR00 := as_MZNR00||as_ZYNR00; --当月 select count(*) into ls_COUNT1 from BM_BMBM00 where BMBH00=substr(lv_KRBZL.BH0000,1,instr(lv_KRBZL.BH0000, ',')-1); if ls_COUNT1 > 0 then select count(*) into ls_COUNT0 from BM_YYSFTJ_DXTZ where BMBH00=substr(lv_KRBZL.BH0000,1,instr(lv_KRBZL.BH0000, ',')-1) and ID0000=2; if ls_COUNT0 > 0 then select '<'||to_char(to_number(substr(a.TJKSRQ,1,4)))||'年'||to_char(to_number(substr(a.TJKSRQ,5,2)))||'月'||to_char(to_number(substr(a.TJKSRQ,7,2)))||'日'|| '至'||to_char(to_number(substr(a.TJJSRQ,5,2)))||'月'||to_char(to_number(substr(a.TJJSRQ,7,2)))||'日 '|| b.BMMC00||' 日报>'||'门诊:门诊就诊人次:'||nvl(to_char(a.MZGHL0),'0')||',门诊总收入:'||nvl(to_char(a.MZZJE0,'fm9999990.9999'),'0')||';' into as_MZNR00 from BM_YYSFTJ_DXTZ a,BM_BMBM00 b where a.BMBH00=b.BMBH00 and a.BMBH00=substr(lv_KRBZL.BH0000,1,instr(lv_KRBZL.BH0000, ',')-1) and ID0000=2; else select BMMC00 into as_BMMC00 from BM_BMBM00 where BMBH00=substr(lv_KRBZL.BH0000,1,instr(lv_KRBZL.BH0000, ',')-1); as_MZNR00:= '<'||to_char(to_number(substr(to_char(last_day(add_months(sysdate-1,-1))+1,'YYYYMMDD'),1,4)))||'年'||to_char(to_number(substr(to_char(last_day(add_months(sysdate-1,-1))+1,'YYYYMMDD'),5,2)))||'月'||to_char(to_number(substr(to_char(last_day(add_months(sysdate-1,-1))+1,'YYYYMMDD'),7,2)))||'日'|| '至'||to_char(to_number(substr(to_char(sysdate-1,'YYYYMMDD'),5,2)))||'月'||to_char(to_number(substr(to_char(sysdate-1,'YYYYMMDD'),7,2)))||'日 '|| as_BMMC00||' 日报>'||'门诊:门诊就诊人次:0,门诊总收入:0; '; end if; end if; select count(*) into ls_COUNT1 from BM_BMBM00 where BMBH00=substr(lv_KRBZL.BH0000,instr(lv_KRBZL.BH0000, ',')+1); if ls_COUNT1 > 0 then select count(*) into ls_COUNT0 from BM_YYSFTJ_DXTZ where BMBH00=substr(lv_KRBZL.BH0000,instr(lv_KRBZL.BH0000, ',')+1) and ID0000=2; if ls_COUNT0 > 0 then select '<'||to_char(to_number(substr(a.TJKSRQ,1,4)))||'年'||to_char(to_number(substr(a.TJKSRQ,5,2)))||'月'||to_char(to_number(substr(a.TJKSRQ,7,2)))||'日'|| '至'||to_char(to_number(substr(a.TJJSRQ,5,2)))||'月'||to_char(to_number(substr(a.TJJSRQ,7,2)))||'日 '|| b.BMMC00||' 日报>'||'住院：在院人数:'||nvl(to_char(ZYRS00),'0')||',出院人数:'||nvl(to_char(CYRS00),'0')||',住院总收入:'||nvl(to_char(ZYZJE0,'fm9999990.9999'),'0') into as_ZYNR00 from BM_YYSFTJ_DXTZ a,BM_BMBM00 b where a.BMBH00=b.BMBH00 and a.BMBH00=substr(lv_KRBZL.BH0000,instr(lv_KRBZL.BH0000, ',')+1) and ID0000=2; else select BMMC00 into as_BMMC00 from BM_BMBM00 where BMBH00=substr(lv_KRBZL.BH0000,instr(lv_KRBZL.BH0000, ',')+1); as_ZYNR00:= '<'||to_char(to_number(substr(to_char(last_day(add_months(sysdate-1,-1))+1,'YYYYMMDD'),1,4)))||'年'||to_char(to_number(substr(to_char(last_day(add_months(sysdate-1,-1))+1,'YYYYMMDD'),5,2)))||'月'||to_char(to_number(substr(to_char(last_day(add_months(sysdate-1,-1))+1,'YYYYMMDD'),7,2)))||'日'|| '至'||to_char(to_number(substr(to_char(sysdate-1,'YYYYMMDD'),5,2)))||'月'||to_char(to_number(substr(to_char(sysdate-1,'YYYYMMDD'),7,2)))||'日 '|| as_BMMC00||' 日报>'||'住院：在院人数:0,出院人数:0,住院总收入:0; '; end if; end if; as_DXNR00 := as_DXNR00||' '||as_MZNR00||as_ZYNR00; --去年同期 select count(*) into ls_COUNT1 from BM_BMBM00 where BMBH00=substr(lv_KRBZL.BH0000,1,instr(lv_KRBZL.BH0000, ',')-1); if ls_COUNT1 > 0 then select count(*) into ls_COUNT0 from BM_YYSFTJ_DXTZ where BMBH00=substr(lv_KRBZL.BH0000,1,instr(lv_KRBZL.BH0000, ',')-1) and ID0000=3; if ls_COUNT0 > 0 then select '<'||to_char(to_number(substr(a.TJKSRQ,1,4)))||'年'||to_char(to_number(substr(a.TJKSRQ,5,2)))||'月'||to_char(to_number(substr(a.TJKSRQ,7,2)))||'日'|| decode(ID0000,1,'','至'||to_char(to_number(substr(a.TJJSRQ,5,2)))||'月'||to_char(to_number(substr(a.TJJSRQ,7,2)))||'日 ')|| b.BMMC00||' 日报>'||'门诊:门诊就诊人次:'||nvl(to_char(a.MZGHL0),'0')||',门诊总收入:'||nvl(to_char(a.MZZJE0,'fm9999990.9999'),'0')||';' into as_MZNR00 from BM_YYSFTJ_DXTZ a,BM_BMBM00 b where a.BMBH00=b.BMBH00 and a.BMBH00=substr(lv_KRBZL.BH0000,1,instr(lv_KRBZL.BH0000, ',')-1) and ID0000=3; else select BMMC00 into as_BMMC00 from BM_BMBM00 where BMBH00=substr(lv_KRBZL.BH0000,1,instr(lv_KRBZL.BH0000, ',')-1); as_MZNR00:= '<'||to_char(to_number(substr(to_char(last_day(add_months(sysdate-1,-13))+1,'YYYYMMDD'),1,4)))||'年'||to_char(to_number(substr(to_char(last_day(add_months(sysdate-1,-13))+1,'YYYYMMDD'),5,2)))||'月'||to_char(to_number(substr(to_char(last_day(add_months(sysdate-1,-13))+1,'YYYYMMDD'),7,2)))||'日'|| '至'||to_char(to_number(substr(to_char(add_months(sysdate-1,-12),'YYYYMMDD'),5,2)))||'月'||to_char(to_number(substr(to_char(add_months(sysdate-1,-12),'YYYYMMDD'),7,2)))||'日 '|| as_BMMC00||' 日报>'||'门诊:门诊就诊人次:0,门诊总收入:0; '; end if; end if; select count(*) into ls_COUNT1 from BM_BMBM00 where BMBH00=substr(lv_KRBZL.BH0000,instr(lv_KRBZL.BH0000, ',')+1); if ls_COUNT1 > 0 then select count(*) into ls_COUNT0 from BM_YYSFTJ_DXTZ where BMBH00=substr(lv_KRBZL.BH0000,instr(lv_KRBZL.BH0000, ',')+1) and ID0000=3; if ls_COUNT0 > 0 then select '<'||to_char(to_number(substr(a.TJKSRQ,1,4)))||'年'||to_char(to_number(substr(a.TJKSRQ,5,2)))||'月'||to_char(to_number(substr(a.TJKSRQ,7,2)))||'日'|| decode(ID0000,1,'','至'||to_char(to_number(substr(a.TJJSRQ,5,2)))||'月'||to_char(to_number(substr(a.TJJSRQ,7,2)))||'日 ')|| b.BMMC00||' 日报>'||'住院：在院人数:'||nvl(to_char(ZYRS00),'0')||',出院人数:'||nvl(to_char(CYRS00),'0')||',住院总收入:'||nvl(to_char(ZYZJE0,'fm9999990.9999'),'0') into as_ZYNR00 from BM_YYSFTJ_DXTZ a,BM_BMBM00 b where a.BMBH00=b.BMBH00 and a.BMBH00=substr(lv_KRBZL.BH0000,instr(lv_KRBZL.BH0000, ',')+1) and ID0000=3; else select BMMC00 into as_BMMC00 from BM_BMBM00 where BMBH00=substr(lv_KRBZL.BH0000,instr(lv_KRBZL.BH0000, ',')+1); as_ZYNR00:= '<'||to_char(to_number(substr(to_char(last_day(add_months(sysdate-1,-13))+1,'YYYYMMDD'),1,4)))||'年'||to_char(to_number(substr(to_char(last_day(add_months(sysdate-1,-13))+1,'YYYYMMDD'),5,2)))||'月'||to_char(to_number(substr(to_char(last_day(add_months(sysdate-1,-13))+1,'YYYYMMDD'),7,2)))||'日'|| '至'||to_char(to_number(substr(to_char(add_months(sysdate-1,-12),'YYYYMMDD'),5,2)))||'月'||to_char(to_number(substr(to_char(add_months(sysdate-1,-12),'YYYYMMDD'),7,2)))||'日 '|| as_BMMC00||' 日报>'||'住院：在院人数:0,出院人数:0,住院总收入:0; '; end if; end if; as_DXNR00 := as_DXNR00||' '||as_MZNR00||as_ZYNR00; select count(*) into ls_COUNT0 from YJ_DXTZ00 where DH0000=lv_KRBZL.DH0000 and DXNRFL='科日报' and YWRQ00=to_char(sysdate-1,'yyyymmdd'); if ls_COUNT0 = 0 then insert into YJ_DXTZ00(DXID00,DXNRFL,DXNR00,YWRQ00,CZRQ00,CZSJ00,DH0000,JSR000,BRID00) values (SQ_YJ_DXTZ00_DXID00.nextval, '科日报', as_DXNR00, to_char(sysdate-1,'yyyymmdd'), to_char(sysdate,'yyyymmdd'), '08:00:00', lv_KRBZL.DH0000, lv_KRBZL.ZWXM00, 0); end if; else --全院短信整理 select '\n1.<'||to_char(to_number(substr(a.TJKSRQ,1,4)))||'年'||to_char(to_number(substr(a.TJKSRQ,5,2)))||'月'||to_char(to_number(substr(a.TJKSRQ,7,2)))||'日'|| decode(ID0000,1,'','至'||to_char(to_number(substr(a.TJJSRQ,5,2)))||'月'||to_char(to_number(substr(a.TJJSRQ,7,2)))||'日 ')|| ' 全院日报>'||'\n(1)全院收入:'||nvl(to_char(MZZJE0+ZYZJE0,'fm99999999990'),'0')||',手术例数:'||nvl(to_char(SSLS00),'0')||'\n(2)门诊:门诊总收入:'||nvl(to_char(a.MZZJE0,'fm99999999990'),'0')||',门诊就诊人次:'||nvl(to_char(a.MZGHL0),'0')||';\n(3)住院：住院总收入:'||nvl(to_char(ZYZJE0,'fm99999999990'),'0')||',在院人数:'||nvl(to_char(ZYRS00),'0')||',出院人数:'|| nvl(to_char(CYRS00),'0') into as_QYNR00 from (select ID0000, TJKSRQ, TJJSRQ, sum(MZGHL0) MZGHL0, sum(MZZJE0) MZZJE0, sum(ZYRS00) ZYRS00, sum(CYRS00) CYRS00, sum(ZYZJE0) ZYZJE0, sum(SSLS00) SSLS00 from BM_YYSFTJ_DXTZ group by ID0000,TJKSRQ,TJJSRQ) a where ID0000=1; as_DXNR00:=as_QYNR00; select '\n2.<'||to_char(to_number(substr(a.TJKSRQ,1,4)))||'年'||to_char(to_number(substr(a.TJKSRQ,5,2)))||'月'||to_char(to_number(substr(a.TJKSRQ,7,2)))||'日'|| decode(ID0000,1,'','至'||to_char(to_number(substr(a.TJJSRQ,5,2)))||'月'||to_char(to_number(substr(a.TJJSRQ,7,2)))||'日 ')|| ' 全院日报>'||'\n(1)全院收入:'||nvl(to_char(MZZJE0+ZYZJE0,'fm99999999990'),'0')||',手术例数:'||nvl(to_char(SSLS00),'0')||'\n(2)门诊:门诊总收入:'||nvl(to_char(a.MZZJE0,'fm99999999990'),'0')||',门诊就诊人次:'||nvl(to_char(a.MZGHL0),'0')||';\n(3)住院：住院总收入:'||nvl(to_char(ZYZJE0,'fm99999999990'),'0')||',出院人数:'|| nvl(to_char(CYRS00),'0') into as_QYNR00 from (select ID0000, TJKSRQ, TJJSRQ, sum(MZGHL0) MZGHL0, sum(MZZJE0) MZZJE0, sum(ZYRS00) ZYRS00, sum(CYRS00) CYRS00, sum(ZYZJE0) ZYZJE0, sum(SSLS00) SSLS00 from BM_YYSFTJ_DXTZ group by ID0000,TJKSRQ,TJJSRQ) a where ID0000=2; as_DXNR00:= as_DXNR00||' '||as_QYNR00; select '\n3.<'||to_char(to_number(substr(a.TJKSRQ,1,4)))||'年'||to_char(to_number(substr(a.TJKSRQ,5,2)))||'月'||to_char(to_number(substr(a.TJKSRQ,7,2)))||'日'|| decode(ID0000,1,'','至'||to_char(to_number(substr(a.TJJSRQ,5,2)))||'月'||to_char(to_number(substr(a.TJJSRQ,7,2)))||'日 ')|| ' 全院日报>'||'\n(1)全院收入:'||nvl(to_char(MZZJE0+ZYZJE0,'fm99999999990'),'0')||',手术例数:'||nvl(to_char(SSLS00),'0')||'\n(2)门诊:门诊总收入:'||nvl(to_char(a.MZZJE0,'fm99999999990'),'0')||',门诊就诊人次:'||nvl(to_char(a.MZGHL0),'0')||';\n(3)住院：住院总收入:'||nvl(to_char(ZYZJE0,'fm99999999990'),'0')||',出院人数:'|| nvl(to_char(CYRS00),'0') into as_QYNR00 from (select ID0000, TJKSRQ, TJJSRQ, sum(MZGHL0) MZGHL0, sum(MZZJE0) MZZJE0, sum(ZYRS00) ZYRS00, sum(CYRS00) CYRS00, sum(ZYZJE0) ZYZJE0, sum(SSLS00) SSLS00 from BM_YYSFTJ_DXTZ group by ID0000,TJKSRQ,TJJSRQ) a where ID0000=3; as_DXNR00:= as_DXNR00||' '||as_QYNR00; select count(*) into ls_COUNT0 from YJ_DXTZ00 where DH0000=lv_KRBZL.DH0000 and DXNRFL='全院日报' and YWRQ00=to_char(sysdate-1,'yyyymmdd'); if ls_COUNT0 = 0 then insert into YJ_DXTZ00(DXID00,DXNRFL,DXNR00,YWRQ00,CZRQ00,CZSJ00,DH0000,JSR000,BRID00) values (SQ_YJ_DXTZ00_DXID00.nextval, '全院日报', as_DXNR00, to_char(sysdate-1,'yyyymmdd'), to_char(sysdate,'yyyymmdd'), '08:00:00', lv_KRBZL.DH0000, lv_KRBZL.ZWXM00, 0); end if; end if; end loop; close CUR_KRBZL; commit; exception when no_data_found then raise_application_error(-20001,substrb('没有找到数据！*'||sqlerrm||',',1,240)); when others then raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240)); end;