create or replace procedure SD_HOSPITAL.SP_BA_ZYZRYB -- MODIFICATION HISTORY -- Person Date Comments -- xuexc 2021.04.15 替换表名， tempYlzlryzbbb替换为BA_YLZLZB BAGL-20210415-001 -- linng 2021.11.04 全院出院总人数与各病区出院人数之和不一致 BAGL-20211104-001 ( J_KSRQYM in char, -- 开始日期 J_JSRQYM in char -- 结束日期 ) as vQSRQ00 varchar2(8); --起始日期 vZZRQ00 varchar2(8); --终止日期 vYYRS00 number(11); --原有人数 vSYRS00 number(11); --实有人数 vSYCW00 number(11); --实有病床数 vKSMC00 varchar2(98); --科室名称 --住院者任意报 vYESTER_1 varchar2(8); vYESTER_2 varchar2(8); cursor CUR_KS is select BMBH00,BMMC00,XH0000 from BA_BMPX00 order by XH0000; Row_KS CUR_KS%rowtype; begin delete from BA_YLZLZB; vYESTER_1:= to_char(ADD_MONTHS(to_date(J_KSRQYM,'yyyy-mm-dd'),-1),'yyyymmdd'); vYESTER_2:= to_char(ADD_MONTHS(to_date(J_JSRQYM,'yyyy-mm-dd'),-1),'yyyymmdd'); SP_BA_ZYZRYB_2(vYESTER_1,vYESTER_2); open CUR_KS; loop fetch CUR_KS into Row_KS; exit when CUR_KS%notfound; if(Row_KS.BMBH00=99999) then vKSMC00 :=' '||Row_KS.BMMC00; insert into BA_YLZLZB(F1,F3,F4,F5,F6, F7,F8,F9,F10,F11, F13,F14,F15,F16,F17, F18,F19,F22,F23,F24,F25,F26,F27,F28,F29,F30,F31,F32,F33,F34) select vKSMC00 BMMC00, sum(RYRS00) RYRS00,sum(TKZR00) TKZR00,sum(CYRS00) CYRS00, sum(ZYRS00) ZYRS00,sum(HZRS00) HZRS00,sum(WYRS00) WYRS00,sum(SWRS00) SWRS00,sum(QTRS00) QTRS00,sum(ZWTK00) ZWTK00, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(ZYRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end ZYL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(HZRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end HZL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(SWRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end SWL000, sum(WZRS00) WZRS00,sum(QJCS00) QJCS00,sum(CGCS00) CGCS00, case when nvl(sum(QJCS00),0)=0 then '0' else to_char(round(sum(CGCS00)*100/sum(QJCS00),2),'fm9999990.00') end CGL000 , sum(SW2400) , nvl(sum(QTRS00),0)+nvl(sum(ZCFMRS),0)+nvl(sum(JHSYRS),0) , sum(ZCFMRS) , sum(JHSYRS) , 0 , sum(PHRRS0) , decode(nvl(sum(SYRS00),0),0,0,to_char(round(sum(PHRRS0)*100/sum(SYRS00),2),'fm9999990.00')) , sum(ZLHJ00) , sum(EXZL00) , sum(YWA000) , sum(LXZL00) , sum(JJEXZL) , sum(WZZL00) from ZS_ZYRSJ0 ZS where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM and BMBH00 in (464,465,93,466,730); select min(RQ0000) into vQSRQ00 FROM ZS_ZYRSJ0 where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM and BMBH00 in (464,465,93,466,730); select max(RQ0000) into vZZRQ00 FROM ZS_ZYRSJ0 where RQ0000>=J_KSRQYM AND RQ0000<=J_JSRQYM and BMBH00 in (464,465,93,466,730); if vQSRQ00 is not null then select sum(YYRS00) into vYYRS00 from ZS_ZYRSJ0 where RQ0000=vQSRQ00 and BMBH00 in (464,465,93,466,730); update BA_YLZLZB set F2=vYYRS00 where F1=vKSMC00; end if; if vZZRQ00 is not null then select sum(SYRS00) into vSYRS00 from ZS_ZYRSJ0 where RQ0000=vZZRQ00 and BMBH00 in (464,465,93,466,730); update BA_YLZLZB set F12=F2+F3+F4-F5-F11 where F1=vKSMC00; --update BA_YLZLZB set F12=vSYRS00 where F1=vKSMC00; select sum(SYCW00) into vSYCW00 from ZS_ZYRSJ0 where RQ0000=vZZRQ00 and BMBH00 in (464,465,93,466,730); update BA_YLZLZB set F20=vSYCW00 where F1=vKSMC00; end if; --------------------------------- elsif(Row_KS.BMBH00=-1) then insert into BA_YLZLZB(F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17, F18,F19,F20,F21,F22,F23,F24,F25,F26,F27,F28,F29,F30,F31,F32,F33,F34) select ' 共享床位' BMMC00, 0, 0, 0 TKZR00, 0 CYRS00, 0 ZYRS00, 0 HZRS00, 0 WYRS00, 0 SWRS00, 0 QTRS00, 0 , 0, 0 ZYL000, 0 HZL000, 0 SWL000, 0 WZRS00, 0 QJCS00, 0, 0, 0, 0 CGL000 , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 from dual; --------------------------------- elsif(Row_KS.BMBH00=0) then insert into BA_YLZLZB(F1,F3,F4,F6,F7,F8,F9,F10,F11,F13,F14,F15,F16,F17, F18,F19,F22,F23,F24,F25,F26,F27,F28,F29,F30,F31,F32,F33,F34,F5) select T.*,(select count(1) from BA_BRZYXX b where b.CYRQ00 >=J_KSRQYM and b.CYRQ00 <=J_JSRQYM) CYRS00 from ( select '全院' BMMC00,sum(RYRS00) RYRS00,sum(TKZR00) TKZR00, --sum(CYRS00) CYRS00, sum(ZYRS00) ZYRS00,sum(HZRS00) HZRS00, sum(WYRS00) WYRS00,sum(SWRS00) SWRS00,sum(QTRS00) QTRS00,sum(ZWTK00) ZWTK00, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(ZYRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end ZYL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(HZRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end HZL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(SWRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end SWL000, sum(WZRS00) WZRS00,sum(QJCS00) QJCS00,sum(CGCS00) CGCS00, case when nvl(sum(QJCS00),0)=0 then '0' else to_char(round(sum(CGCS00)*100/sum(QJCS00),2),'fm9999990.00') end CGL000 , sum(SW2400) , nvl(sum(QTRS00),0)+nvl(sum(ZCFMRS),0)+nvl(sum(JHSYRS),0) , sum(ZCFMRS) , sum(JHSYRS) , 0 , sum(PHRRS0) , decode(nvl(sum(SYRS00),0),0,0,to_char(round(sum(PHRRS0)*100/sum(SYRS00),2),'fm9999990.00')) , sum(ZLHJ00) , sum(EXZL00) , sum(YWA000) , sum(LXZL00) , sum(JJEXZL) , sum(WZZL00) from ZS_ZYRSJ0 ZS where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM) T; select min(RQ0000) into vQSRQ00 FROM ZS_ZYRSJ0 where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM; select max(RQ0000) into vZZRQ00 FROM ZS_ZYRSJ0 where RQ0000>=J_KSRQYM AND RQ0000<=J_JSRQYM; if vQSRQ00 is not null then select sum(YYRS00) into vYYRS00 from ZS_ZYRSJ0 where RQ0000=vQSRQ00; update BA_YLZLZB set F2=vYYRS00 where F1='全院'; end if; if vZZRQ00 is not null then --select sum(SYRS00) into vSYRS00 from ZS_ZYRSJ0 where RQ0000=vZZRQ00; --update BA_YLZLZB set F12=vSYRS00 where F1='全院'; update BA_YLZLZB set F12=F2+F3+F4-F5-F11 where F1='全院'; select sum(SYCW00) into vSYCW00 from ZS_ZYRSJ0 where RQ0000=vZZRQ00; update BA_YLZLZB set F20=vSYCW00 where F1='全院'; end if; -------------------------------------------- else vKSMC00 :=' '||Row_KS.BMMC00; if(Row_KS.BMBH00 ='464' or Row_KS.BMBH00 ='465' or Row_KS.BMBH00 ='93' or Row_KS.BMBH00 ='466' or Row_KS.BMBH00 ='730') then vKSMC00 :=' '||Row_KS.BMMC00; end if; insert into BA_YLZLZB(F1,F3,F4,F6, F7,F8,F9,F10,F11, F13,F14,F15,F16,F17,F18,F19,F22,F23,F24,F25,F26,F27,F28,F29,F30,F31,F32,F33,F34,F5) select T.*,( select count(1) from ba_brzyxx a,zy_brxxb0 b where a.CYRQ00 >=J_KSRQYM and a.CYRQ00 <=J_JSRQYM and a.lsh000=b.zyid00 and b.dqbq00=Row_KS.BMBH00 group by dqbq00 ) CYRS00 from ( select vKSMC00 BMMC00,sum(RYRS00) RYRS00,sum(TKZR00) TKZR00, --sum(CYRS00) CYRS00, sum(ZYRS00) ZYRS00, sum(HZRS00) HZRS00,sum(WYRS00) WYRS00,sum(SWRS00) SWRS00,sum(QTRS00) QTRS00,sum(ZWTK00) ZWTK00, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(ZYRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end ZYL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(HZRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end HZL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(SWRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end SWL000, sum(WZRS00) WZRS00,sum(QJCS00) QJCS00,sum(CGCS00) CGCS00, case when nvl(sum(QJCS00),0)=0 then '0' else to_char(round(sum(CGCS00)*100/sum(QJCS00),2),'fm9999990.00') end CGL000 , sum(SW2400) , nvl(sum(QTRS00),0)+nvl(sum(ZCFMRS),0)+nvl(sum(JHSYRS),0) , sum(ZCFMRS) , sum(JHSYRS) , 0 , sum(PHRRS0) , decode(nvl(sum(SYRS00),0),0,0,to_char(round(sum(PHRRS0)*100/sum(SYRS00),2),'fm9999990.00')) , sum(ZLHJ00) , sum(EXZL00) , sum(YWA000) , sum(LXZL00) , sum(JJEXZL) , sum(WZZL00) from ZS_ZYRSJ0 ZS where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM and BMBH00=Row_KS.BMBH00) T; select min(RQ0000) into vQSRQ00 FROM ZS_ZYRSJ0 where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM and BMBH00=Row_KS.BMBH00; select max(RQ0000) into vZZRQ00 FROM ZS_ZYRSJ0 where RQ0000>=J_KSRQYM AND RQ0000<=J_JSRQYM and BMBH00=Row_KS.BMBH00; if vQSRQ00 is not null then select YYRS00 into vYYRS00 from ZS_ZYRSJ0 where RQ0000=vQSRQ00 and BMBH00=Row_KS.BMBH00; update BA_YLZLZB set F2=vYYRS00 where F1=vKSMC00; end if; if vZZRQ00 is not null then select SYRS00 into vSYRS00 from ZS_ZYRSJ0 where RQ0000=vZZRQ00 and BMBH00=Row_KS.BMBH00; update BA_YLZLZB set F12=F2+F3+F4-F5-F11 where F1=vKSMC00; --update BA_YLZLZB set F12=vSYRS00 where F1=vKSMC00; select SYCW00 into vSYCW00 from ZS_ZYRSJ0 where RQ0000=vZZRQ00 and BMBH00=Row_KS.BMBH00; update BA_YLZLZB set F20=vSYCW00 where F1=vKSMC00; end if; end if; end loop; close CUR_KS; update BA_YLZLZB b1 set F2=(select F12 from BA_YLZLZ1 b2 where b1.F1=b2.F1); update BA_YLZLZB set F12=F2+F3+F4-F5-F11 where F1='全院'; commit; end;