create or replace procedure SP_ZH_YPXSCX ( V_FLAG00 in char, --报表标志 --SUMYPSH V_GHORJZ in number, --报表分类 V_YFBMBH in number, V_KDKS00 in number, V_KDYS00 in number, V_YPNM00 in number, V_YPZSL0 in number, V_YSPX00 in char, V_YPZJE0 in number, V_SFZBYP in char, V_FBBH00 in number, V_LBBH00 in char, V_YLBH00 in char, V_EJYL00 in char, V_JXBH00 in char, V_MZZY00 in char, V_YPLSJ0 in number default 0, V_YYID00 in char, V_SFNHZZ in number default 0 ) AS C_STARTD char(8); --开始日期 C_STARTT char(8); --开始时间 C_STOPDA char(8); --结束日期 C_STOPTI char(8); --结束时间 C_NOW000 char(8); --今天 C_ID0000 number(10); --序列号 begin --date revisor comments --2009.03.30 yangh 新增零售单价的条件。 --2010.04.23 zhr V_YSPX00='2'时，去掉TEMP06的'%'，因为前台进行to_number操作 --2010.10.20 zhangyc 增加进价金额TEMP09 ZHCX-20100712-001 --2011.10.25 jinfl 增加不同分院的统计，保留全院 --2012.07.19 caowenbin 修改药理 --2016.08.26 chenhn 增加参数V_SFNHZZ by ZHCX-20160120-001 --先删除所有记录 DELETE FROM BM_ZHTJ00_TEMP00 where trim(FLAG00) like '%'||trim(V_FLAG00)||'%'; select STARTD,STARTT,STOPDA,STOPTI into C_STARTD,C_STARTT,C_STOPDA,C_STOPTI from BM_ZHTJ00_TEMP00 where FLAG00=PACKAGE_ZHCX.GET_SUMSR; delete from BM_ZHTJ00_TEMP00 where FLAG00=PACKAGE_ZHCX.GET_SUMSR; select TO_CHAR(SYSDATE,'YYYYMMDD') into C_NOW000 from dual; if C_STOPDA>C_NOW000 then C_STOPDA:=C_NOW000; C_STOPTI:='23:59:59'; end if; select SQ_BM_YYSFTJ_ID0000.NEXTVAL into C_ID0000 from dual; delete from BM_YYSFTJ where ID0000=C_ID0000; delete BM_YYSFTJ where ID0000<=C_ID0000-100; COMMIT; if V_GHORJZ = 0 then insert into BM_YYSFTJ(BMBH00,ID0000,SFXM01,SFXM02,SFXM03,SFXM04) select 0,C_ID0000,a.YPNM00,sum(a.YPZSL0*a.ZHL000),sum(round(a.LSDJ00*a.YPZSL0,2)),b.GHID00 from YF_MZCFMX a,YF_MZCF00 b,BM_BMBM00 c where a.CFLSH0=b.CFLSH0 and b.YFBMBH=c.BMBH00 and b.CFZT00<>'3' and b.JZRQ00 is not null and b.YFBMBH=decode(V_YFBMBH,0,b.YFBMBH,V_YFBMBH) and c.YYID00=decode(V_YYID00,'0 ',c.YYID00,V_YYID00) and b.YSGZH0=decode(V_KDYS00,0,YSGZH0,V_KDYS00) and b.GHKS00=decode(V_KDKS00,0,b.GHKS00,V_KDKS00) and a.YPNM00=decode(V_YPNM00,0,a.YPNM00,V_YPNM00) and b.GHID00 in (select E.GHID00 from SF_BRXXB0 E,IC_YBBRLB F where E.GHRQ00 between C_STARTD and C_STOPDA and (V_FBBH00=0 or E.FBBH00=V_FBBH00) and (V_SFNHZZ=0 or (V_SFNHZZ=1 and E.YBLB00=F.YBLB00 and E.FBBH00=F.FBBH00 and (F.SFXNH0='1' OR F.SFXNH0='4')) or (V_SFNHZZ=2 and E.YBZXLB='2' and E.YBBRLB<>'C'))) and a.SFZBY0='N' and (V_SFZBYP='2' or (V_SFZBYP='1' and exists(select 1 from BM_YD0000 where YPNM00=a.YPNM00 and SFZBYP='Y')) or (V_SFZBYP='0' and exists(select 1 from BM_YD0000 where YPNM00=a.YPNM00 and SFZBYP='N'))) group by b.GHID00,a.YPNM00; insert into BM_ZHTJ00_TEMP00(FLAG00,TEMP01,TEMP02,TEMP03,TEMP04,TEMP05,TEMP06,TEMP07,TEMP08,TEMP09) select distinct V_FLAG00,to_char(a.SFXM01,'9999999999'),trim(b.YPMC00),trim(b.YPGG00),trim(b.KCDW00),to_char(b.LSJ000,'99999990.00'), to_char(round(a.SFXM02/b.K2J000,2),'99999990.00'),to_char(SFXM03,'999999990.00'),to_char(SFXM04,'9999999999'),to_char(b.GJJ000,'99999990.00') from BM_YYSFTJ a,BM_YD0000 b where a.ID0000=C_ID0000 and a.SFXM01=b.YPNM00 and a.SFXM02/b.K2J000>=V_YPZSL0 and a.SFXM03>=V_YPZJE0 and round(a.SFXM02/b.K2J000,2)>=V_YPLSJ0 and (V_LBBH00='Z' or b.LBBH00=V_LBBH00) and (V_YLBH00='Z' or b.YLBH00=V_YLBH00) and (V_EJYL00='0' or b.CYLLSH=V_EJYL00) and (V_JXBH00='Z' or b.JXBH00=V_JXBH00); end if; if V_GHORJZ = 1 then if V_YSPX00='0' then insert into BM_YYSFTJ(BMBH00,ID0000,SFXM01,SFXM02,SFXM03,SFXM04) select BMBH00,ID0000,YPNM00,sum(YPZSL0),sum(YPJE00),0 from ( select 0 BMBH00,C_ID0000 ID0000,YPNM00,sum(YPZSL0*ZHL000) YPZSL0,sum(round(LSDJ00*YPZSL0,2)) YPJE00 from YF_MZCFMX where CFLSH0 in (select CFLSH0 from YF_MZCF00 a,BM_BMBM00 c where a.JZRQ00 between C_STARTD and C_STOPDA and a.YFBMBH=c.BMBH00 and a.YFBMBH=decode(V_YFBMBH,0,a.YFBMBH,V_YFBMBH) and a.YSGZH0=decode(V_KDYS00,0,a.YSGZH0,V_KDYS00) and c.YYID00=decode(V_YYID00,'0 ',c.YYID00,V_YYID00) and V_MZZY00 in ('Z','0') and (V_FBBH00=0 or exists(select 1 from SF_BRXXB0 E,IC_YBBRLB F where E.GHID00=a.GHID00 and E.FBBH00=V_FBBH00 and (V_SFNHZZ=0 or (V_SFNHZZ=1 and E.YBLB00=F.YBLB00 and E.FBBH00=F.FBBH00 and (F.SFXNH0='1' OR F.SFXNH0='4')) or (V_SFNHZZ=2 and E.YBZXLB='2' and E.YBBRLB<>'C')))) and a.GHKS00=decode(V_KDKS00,0,a.GHKS00,V_KDKS00)) and YPNM00=decode(V_YPNM00,0,YPNM00,V_YPNM00) and SFZBY0='N' and (V_SFZBYP='2' or (V_SFZBYP='1' and exists(select 1 from BM_YD0000 where YPNM00=YF_MZCFMX.YPNM00 and SFZBYP='Y')) or (V_SFZBYP='0' and exists(select 1 from BM_YD0000 where YPNM00=YF_MZCFMX.YPNM00 and SFZBYP='N'))) group by YPNM00 union all select 0,C_ID0000,YPNM00,sum(YPZSL0*ZHL000),sum(round(LSDJ00*YPZSL0,2)) from YF_ZYCFMX where CFLSH0 in (select CFLSH0 from YF_ZYCF00 a,BM_BMBM00 c where a.JZRQ00 between C_STARTD and C_STOPDA and a.YFBMBH=c.BMBH00 and a.YFBMBH=decode(V_YFBMBH,0,a.YFBMBH,V_YFBMBH) and a.YSGZH0=decode(V_KDYS00,0,a.YSGZH0,V_KDYS00) and c.YYID00=decode(V_YYID00,'0 ',c.YYID00,V_YYID00) and V_MZZY00 in ('Z','1') and (V_FBBH00=0 or exists(select 1 from ZY_BRXXB0 E,IC_YBBRLB F where E.ZYID00=a.ZYID00 and E.FBBH00=V_FBBH00 and (V_SFNHZZ=0 or (V_SFNHZZ=1 and E.YBLB00=F.YBLB00 and E.FBBH00=F.FBBH00 and (F.SFXNH0='1' OR F.SFXNH0='4')) or (V_SFNHZZ=2 and E.YBZXLB='2' and E.YBBRLB<>'C')))) and a.DQKS00=decode(V_KDKS00,0,a.DQKS00,V_KDKS00)) and YPNM00=decode(V_YPNM00,0,YPNM00,V_YPNM00) and SFZBY0='N' and (V_SFZBYP='2' or (V_SFZBYP='1' and exists(select 1 from BM_YD0000 where YPNM00=YF_ZYCFMX.YPNM00 and SFZBYP='Y')) or (V_SFZBYP='0' and exists(select 1 from BM_YD0000 where YPNM00=YF_ZYCFMX.YPNM00 and SFZBYP='N'))) group by YPNM00 union all select 0,C_ID0000,YPNM00,sum(YPZSL0*ZHL000),sum(round(LSDJ00*YPZSL0,2)) from YF_YZYPSQ a,BM_BMBM00 c where a.JZRQ00 between C_STARTD and C_STOPDA and a.YFBMBH=c.BMBH00 and a.YFBMBH=decode(V_YFBMBH,0,a.YFBMBH,V_YFBMBH) and a.YSGZH0=decode(V_KDYS00,0,a.YSGZH0,V_KDYS00) and c.YYID00=decode(V_YYID00,'0 ',c.YYID00,V_YYID00) and a.DQKS00=decode(V_KDKS00,0,a.DQKS00,V_KDKS00) and a.YPNM00=decode(V_YPNM00,0,a.YPNM00,V_YPNM00) and (V_SFZBYP='2' or (V_SFZBYP='1' and exists(select 1 from BM_YD0000 where YPNM00=a.YPNM00 and SFZBYP='Y')) or (V_SFZBYP='0' and exists(select 1 from BM_YD0000 where YPNM00=a.YPNM00 and SFZBYP='N'))) and V_MZZY00 in ('Z','1') and (V_FBBH00=0 or exists(select 1 from ZY_BRXXB0 E,IC_YBBRLB F where E.ZYID00=a.ZYID00 and E.FBBH00=V_FBBH00 and (V_SFNHZZ=0 or (V_SFNHZZ=1 and E.YBLB00=F.YBLB00 and E.FBBH00=F.FBBH00 and (F.SFXNH0='1' OR F.SFXNH0='4')) or (V_SFNHZZ=2 and E.YBZXLB='2' and E.YBBRLB<>'C')))) group by YPNM00) group by BMBH00,ID0000,YPNM00; else insert into BM_YYSFTJ(BMBH00,ID0000,SFXM01,SFXM02,SFXM03,SFXM04,SFXM05) select BMBH00,ID0000,YPNM00,sum(YPZSL0),sum(YPJE00),YSGZH0,0 from ( select 0 BMBH00,C_ID0000 ID0000,a.YPNM00,sum(a.YPZSL0*a.ZHL000) YPZSL0,sum(round(a.LSDJ00*a.YPZSL0,2)) YPJE00,b.YSGZH0 from YF_MZCFMX a,YF_MZCF00 b,BM_BMBM00 c where a.CFLSH0=b.CFLSH0 and b.JZRQ00 between C_STARTD and C_STOPDA and b.YFBMBH=c.BMBH00 and c.YYID00=decode(V_YYID00,'0 ',c.YYID00,V_YYID00) and b.YFBMBH=decode(V_YFBMBH,0,b.YFBMBH,V_YFBMBH) and b.YSGZH0=decode(V_KDYS00,0,b.YSGZH0,V_KDYS00) and b.GHKS00=decode(V_KDKS00,0,b.GHKS00,V_KDKS00) and a.YPNM00=decode(V_YPNM00,0,a.YPNM00,V_YPNM00) and a.SFZBY0='N' and (V_SFZBYP='2' or (V_SFZBYP='1' and exists(select 1 from BM_YD0000 where YPNM00=a.YPNM00 and SFZBYP='Y')) or (V_SFZBYP='0' and exists(select 1 from BM_YD0000 where YPNM00=a.YPNM00 and SFZBYP='N'))) and V_MZZY00 in ('Z','0') and (V_FBBH00=0 or exists(select 1 from SF_BRXXB0 E,IC_YBBRLB F where E.GHID00=b.GHID00 and E.FBBH00=V_FBBH00 and (V_SFNHZZ=0 or (V_SFNHZZ=1 and E.YBLB00=F.YBLB00 and E.FBBH00=F.FBBH00 and (F.SFXNH0='1' OR F.SFXNH0='4')) or (V_SFNHZZ=2 and E.YBZXLB='2' and E.YBBRLB<>'C')))) group by a.YPNM00,b.YSGZH0 union all select 0,C_ID0000,a.YPNM00,sum(a.YPZSL0*a.ZHL000),sum(round(a.LSDJ00*a.YPZSL0,2)),b.YSGZH0 from YF_ZYCFMX a,YF_ZYCF00 b,BM_BMBM00 c where a.CFLSH0=b.CFLSH0 and b.JZRQ00 between C_STARTD and C_STOPDA and b.YFBMBH=c.BMBH00 and c.YYID00=decode(V_YYID00,'0 ',c.YYID00,V_YYID00) and b.YFBMBH=decode(V_YFBMBH,0,b.YFBMBH,V_YFBMBH) and b.YSGZH0=decode(V_KDYS00,0,b.YSGZH0,V_KDYS00) and b.DQKS00=decode(V_KDKS00,0,b.DQKS00,V_KDKS00) and a.YPNM00=decode(V_YPNM00,0,a.YPNM00,V_YPNM00) and a.SFZBY0='N' and (V_SFZBYP='2' or (V_SFZBYP='1' and exists(select 1 from BM_YD0000 where YPNM00=a.YPNM00 and SFZBYP='Y')) or (V_SFZBYP='0' and exists(select 1 from BM_YD0000 where YPNM00=a.YPNM00 and SFZBYP='N'))) and V_MZZY00 in ('Z','1') and (V_FBBH00=0 or exists(select 1 from ZY_BRXXB0 E,IC_YBBRLB F where E.ZYID00=b.ZYID00 and E.FBBH00=V_FBBH00 and (V_SFNHZZ=0 or (V_SFNHZZ=1 and E.YBLB00=F.YBLB00 and E.FBBH00=F.FBBH00 and (F.SFXNH0='1' OR F.SFXNH0='4')) or (V_SFNHZZ=2 and E.YBZXLB='2' and E.YBBRLB<>'C')))) group by a.YPNM00,b.YSGZH0 union all select 0,C_ID0000,YPNM00,sum(YPZSL0*ZHL000),sum(round(LSDJ00*YPZSL0,2)),YSGZH0 from YF_YZYPSQ a,BM_BMBM00 c where a.JZRQ00 between C_STARTD and C_STOPDA and a.YFBMBH=decode(V_YFBMBH,0,a.YFBMBH,V_YFBMBH) and a.YSGZH0=decode(V_KDYS00,0,a.YSGZH0,V_KDYS00) and a.DQKS00=decode(V_KDKS00,0,a.DQKS00,V_KDKS00) and a.YPNM00=decode(V_YPNM00,0,a.YPNM00,V_YPNM00) and a.YFBMBH=c.BMBH00 and c.YYID00=decode(V_YYID00,'0 ',c.YYID00,V_YYID00) and (V_SFZBYP='2' or (V_SFZBYP='1' and exists(select 1 from BM_YD0000 where YPNM00=a.YPNM00 and SFZBYP='Y')) or (V_SFZBYP='0' and exists(select 1 from BM_YD0000 where YPNM00=a.YPNM00 and SFZBYP='N'))) and V_MZZY00 in ('Z','1') and (V_FBBH00=0 or exists(select 1 from ZY_BRXXB0 E,IC_YBBRLB F where E.ZYID00=a.ZYID00 and E.FBBH00=V_FBBH00 and (V_SFNHZZ=0 or (V_SFNHZZ=1 and E.YBLB00=F.YBLB00 and E.FBBH00=F.FBBH00 and (F.SFXNH0='1' OR F.SFXNH0='4')) or (V_SFNHZZ=2 and E.YBZXLB='2' and E.YBBRLB<>'C')))) group by YPNM00,YSGZH0) group by BMBH00,ID0000,YPNM00,YSGZH0; if V_YSPX00='2' then insert into BM_YYSFTJ(BMBH00,ID0000,SFXM01,SFXM02,SFXM03,SFXM04,SFXM05) select 0 BMBH00,C_ID0000 ID0000,-100 SFXM01,0 SFXM02,0 SFXM03,KDYS00,sum(HJJE00) HJJE00 from ZY_FYMX00 a,BM_BMBM00 c where a.CZRQ00 between C_STARTD and C_STOPDA and a.KDYS00=decode(V_KDYS00,0,a.KDYS00,V_KDYS00) and a.KDKS00=c.BMBH00 and c.YYID00=decode(V_YYID00,'0 ',c.YYID00,V_YYID00) and a.KDKS00=decode(V_KDKS00,0,a.KDKS00,V_KDKS00) and V_MZZY00 in ('Z','1') and a.XMBH00<=9900000000 group by KDYS00 union all select 0 BMBH00,C_ID0000 ID0000,-100 SFXM01,0 SFXM02,0 SFXM03,KDYS00,sum(HJJE00) HJJE00 from SF_FYMX00 a,BM_BMBM00 c where a.CZRQ00 between C_STARTD and C_STOPDA and a.KDYS00=decode(V_KDYS00,0,a.KDYS00,V_KDYS00) and a.KDKS00=c.BMBH00 and c.YYID00=decode(V_YYID00,'0 ',c.YYID00,V_YYID00) and a.KDKS00=decode(V_KDKS00,0,a.KDKS00,V_KDKS00) and V_MZZY00 in ('Z','0') and a.XMBH00<=9900000000 group by KDYS00; end if; end if; if V_YSPX00='2' then insert into BM_ZHTJ00_TEMP00(FLAG00,TEMP01,TEMP02,TEMP03,TEMP04,TEMP05,TEMP06,TEMP07,TEMP08) select V_FLAG00,'-100','',to_char(SFXM05+SFXM03,'999999990.00'),'',to_char(SFXM05,'999999990.00'),to_char(round(SFXM03*100/(SFXM03+SFXM05),2),'999999990.00'),to_char(SFXM03,'999999990.00'),to_char(SFXM04,'999999') from (select sum(SFXM03) SFXM03,SFXM04,sum(YLSR00) SFXM05 from (select sum(SFXM03) SFXM03,SFXM04,0 YLSR00 from BM_YYSFTJ a,BM_YD0000 b where a.ID0000=C_ID0000 and a.SFXM01=b.YPNM00 and (V_LBBH00='Z' or (b.LBBH00=V_LBBH00 and (V_YLBH00='Z' or b.YLBH00=V_YLBH00) and (V_EJYL00='0' or b.CYLLSH=V_EJYL00) and (V_JXBH00='Z' or b.JXBH00=V_JXBH00))) group by SFXM04 union all select 0,SFXM04,sum(SFXM05) YLSR00 from BM_YYSFTJ where ID0000=C_ID0000 and SFXM01=-100 group by SFXM04) group by SFXM04) where SFXM03>=V_YPZJE0; else insert into BM_ZHTJ00_TEMP00(FLAG00,TEMP01,TEMP02,TEMP03,TEMP04,TEMP05,TEMP06,TEMP07,TEMP08,TEMP09) select distinct V_FLAG00,to_char(a.SFXM01,'9999999999'),trim(substrb(b.YPMC00,1,40)),trim(b.YPGG00),trim(b.KCDW00),to_char(b.LSJ000,'99999990.00'), to_char(round(a.SFXM02/b.K2J000,2),'99999990.00'),to_char(SFXM03,'999999990.00'),to_char(SFXM04,'999999'),to_char(b.GJJ000,'99999990.00') from BM_YYSFTJ a,BM_YD0000 b where a.ID0000=C_ID0000 and a.SFXM01=b.YPNM00 and a.SFXM02/b.K2J000>=V_YPZSL0 and SFXM03>=V_YPZJE0 and b.LSJ000>=V_YPLSJ0 and (V_LBBH00='Z' or b.LBBH00=V_LBBH00) and (V_YLBH00='Z' or b.YLBH00=V_YLBH00) and (V_EJYL00='0' or b.CYLLSH=V_EJYL00) and (V_JXBH00='Z' or b.JXBH00=V_JXBH00); end if; end if; delete BM_YYSFTJ where ID0000=C_ID0000; commit; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001,'插入临时表错误！SP_ZH_YPXSCX('||V_FLAG00||','||V_GHORJZ||')*'||SQLCODE||SQLERRM); ROLLBACK; end;