select e.*,f.BMMC00,nvl(decode(sign(e.SLSL00),0,0,1,(decode(sign(e.GJSLJE),1,(Round(e.GJSLJE / e.SLSL00 * 10000 + 0.0000499) / 10000),0,0,-1,(-Round(-e.GJSLJE / e.SLSL00 * 10000 + 0.0000499) / 10000)))),0) C_GJDJSL, 
nvl(decode(sign(e.FCSL00),0,0,1,(decode(sign(e.GJFCJE),1,(Round(e.GJFCJE / e.FCSL00 * 10000 + 0.0000499) / 10000),0,0,-1,(-Round(-e.GJFCJE / e.FCSL00 * 10000 + 0.0000499) / 10000)))),0) C_GJDJFC
from ( 
select a.ypbm00 ypbm00,k.sjkcsl sjkcsl,k.kcje00 kcje00,b.CRBMBH,decode(a.YPMC00,' ',0,1) PX0000,a.YPMC00,a.PYSM00,a.WBSM00,a.YPMLBZ,a.YPGG00,
	a.KCDW00 KCDW00,
b.YPNM00,NVL(b.SLSL00,0) SLSL00,NVL(b.SLJE00,0) SLJE00,NVL(b.FCSL00,0) FCSL00,NVL(b.FCJE00,0) FCJE00,NVL(b.GJSLJE,0) GJSLJE,nvl(GJFCJE,0) GJFCJE,nvl(k.GJZJE0,0) JCGJJE,nvl(b.sldj00,0) sldj00,nvl(b.fcdj00,0) fcdj00,a.gjj000,a.lsj000,
 ( select MC0000 from VW_YK_YDKSSJB where BH0000 = a.KSSJB0 ) kss000,decode(a.SF4J70,'1','是','否') SF4J70,
 ( case when  A.MZDJ00<>'0' then '麻醉'  when A.SJDJ00<>'1' and A.SJDJ00<>'0'  then '二类精神药品' when  A.SJDJ00='1' then  '一类精神药品'  when A.DPDJ00<>'0' then '毒品' else '普通' end )  DM0000 
,a.ZBQMC0
     from BM_YD0000 a, vw_yf_pckchz k,
 (select c.CRBMBH,c.YPNM00,sum(SLJE00) SLJE00,
	sum(ROUND(SLSL00*ZHL000/D.K2J000,3)) SLSL00,
	sum(ROUND(FCSL00*ZHL000/D.K2J000,3)) FCSL00,
sum(FCJE00) FCJE00,SUM(nvl(GJSLJE,round(SLSL00*ZHL000/D.K2J000*c.gjdj00,2))) GJSLJE,
SUM(nvl(GJFCJE,round(FCSL00*ZHL000/D.K2J000*c.gjdj00,2))) GJFCJE,round(avg(sldj00),4) sldj00,round(avg(fcdj00),4) fcdj00
     from YF_YPMXZ0 c,BM_YD0000 d
where c.YPNM00=d.YPNM00
          and ((c.JZXZ00='R' and c.RCLXBH in (01,02,03,04,05,06,07,09,16,08,10,11,12,13,31,14,21)) or (c.JZXZ00 = 'C' and c.RCLXBH in (14,01,02,03,04,05,06,07,08,12,16,10,13,11,09,19,15)
         ))
and d.LBBH00 like '0%'
and d.YLBH00 like '%'
and d.JXBH00 like '%'
and c.YFBMBH= 874
and c.JZRQ00>='20240725' and c.JZRQ00<='20240725'
and c.JZRQ00||c.JZSJ00>='2024072500:00:00' and c.JZRQ00||c.JZSJ00<='2024072523:59:59'
     AND c.CRBMBH <> 66666
group by c.CRBMBH,c.YPNM00
order by c.CRBMBH desc,c.YPNM00 ) b
where a.YPNM00(+)=b.YPNM00  and k.ypnm00(+)=b.ypnm00 and k.yfbmbh= 874
     order by PX0000
) e,VW_YF_YPGHDW f where e.CRBMBH=f.BMBH00