CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YF_TYZSYPMX
 (
 JJSL00, CFID00, CXCFID, CFLSH0, YPMC00, YPGG00, LSDJ00, YPZSL0, CFDW00, 
CSL000, JLDW00, PCMC00, YFSMMC, YPYFMC, ZJYFMC, XMYFMC, YYSJ00, YBLB00, 
ZFBL00, YPNM00, SJSL00, ZB0000, SCCJZW, PZBSM0, YPBM00, ZSMSL0, YSSL00, 
SMSLMC, ZSMSL1, SMSL00, MZZYBZ

 )
 AS 
select
  substr(sf_yf_ypslzh(-YPZSL0*ZHL000,X.YPNM00,3),1,50) JJSL00,
  X.CFID00,
  X.CXCFID,
  X.CFLSH0,
  X.YPMC00,
  X.YPGG00,
  X.LSDJ00,
  -X.YPZSL0  YPZSL0 ,
  X.CFDW00,
  -X.CSL000  CSL000,
  X.JLDW00,
  X.PCMC00,
  X.YFSMMC,
  X.YPYFMC,
  X.ZJYFMC,
  X.XMYFMC,
  X.YYSJ00,
  X.YBLB00,
  X.ZFBL00,
  X.YPNM00,
  X.YPZSL0-X.JYSL00 SJSL00,
  X.ZB0000,
  (select B.SCCJZW from  BM_YPSCCJ B,BM_YD0000 D where D.SCCJBH=B.SCCJBH and D.YPNM00=X.YPNM00) SCCJZW,
  substrb((select TO_CHAR(wm_concat(PZBSM0)) from  YK_YPZSM0 where YPNM00=B.YPNM00),1,200) PZBSM0,
  B.YPBM00,
  --round((-X.YPZSL0*X.ZHL000/B.K2J000),2) ZSMSL0,  --向下取整
  abs(X.YPZSL0)  ZSMSL0,  --向下取整
  --(select to_char(count(1)) from YF_CFZSMX k where K.TYCFH0 = X.CFLSH0 and k.YPNM00 = X.YPNM00 and K.YPQLPC = X.CXCFID and K.MZZYBZ = '0' )  YSSL00,
  (select nvl(sum(K.TYZSL0),0)/X.ZHL000 from YF_TYZSMX k where K.CFLSH0 = X.CFLSH0 and k.YPNM00 = X.YPNM00 and K.YPQLPC = X.CFID00 and K.MZZYBZ = '0' )  YSSL00,
  (select nvl(sum(K.TYZSL0),0)/X.ZHL000 from YF_TYZSMX k where K.CFLSH0 = X.CFLSH0 and k.YPNM00 = X.YPNM00 and K.YPQLPC = X.CFID00 and K.MZZYBZ = '0' )
    ||'/'||to_char( abs(X.YPZSL0)) SMSLMC,  --已扫/应扫
  --round(-X.YPZSL0*X.ZHL000/B.K2J000,2) ZSMSL1,   --向上取整
  abs(X.YPZSL0)  ZSMSL1,  --向下取整
    (select to_char(nvl(sum(K.TYZSL0),0)/X.ZHL000)||X.CFDW00 from YF_TYZSMX k where K.CFLSH0 = X.CFLSH0 and k.YPNM00 = X.YPNM00 and K.YPQLPC = X.CFID00 and K.MZZYBZ = '0' )
    ||'/'||to_char( abs(X.YPZSL0))||X.CFDW00 SMSL00,
  '0' MZZYBZ     
from YF_MZCFMX X,BM_YD0000 B
where  X.YPNM00 = B.YPNM00 and nvl(b.XSWZSM,0)<> '1'
/