CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YF_YZZSTY
 (
 CKDH00, YFBMBH, BRXM00, BQBMMC, ZYID00, ZYH000, CH0000, YPQLPC, CXPCID, 
YPNM00, YPMC00, YPGG00, CKSL00, ZHL000, CKDW00, PZBSM0, ZSMSL0, ZSMSL1, 
SMSL00, SMSLMC, SMSLM1

 )
 AS 
select
  B.CKDH00,
  B.YFBMBH,
  B.BRXM00,
  ( select BMMC00 from BM_BMBM00 where BMBH00 = B.BQBMBH ) BQBMMC,
  B.ZYID00,
  B.ZYH000,
  B.CH0000,
  B.YPQLPC,
  B.CXPCID,
  B.YPNM00,
  B.YPMC00,
  B.YPGG00,
  sum(-B.YPZSL0) CKSL00,
  B.ZHL000,
  B.QLDW00 CKDW00,
  substrb((select to_char(wm_concat(PZBSM0)) from  YK_YPZSM0 where YPNM00= B.YPNM00),1,200) PZBSM0,               --药品标识码
  sum(abs(B.YPZSL0))  ZSMSL0,                                                                                    --追溯码数量(向上取整)
  sum(abs(B.YPZSL0))  ZSMSL1,                                                                                    --追溯码数量(向下取整)
  ( select nvl(sum(X.TYZSL0),0)/B.ZHL000 from YF_TYZSMX X where X.YPQLPC = B.YPQLPC and X.YPNM00 = B.YPNM00 and X.MZZYBZ = '1' and X.CFLSH0 = B.CKDH00 ) SMSL00,--已经扫码数量
  (select to_char(nvl(sum(TYZSL0),0)/B.ZHL000)||B.QLDW00 from YF_TYZSMX where YPQLPC = B.YPQLPC and YPNM00 = B.YPNM00 and MZZYBZ = '1' and CFLSH0 = B.CKDH00)||'/'||to_char(sum(-B.YPZSL0))||B.QLDW00 SMSLMC,   --已扫/应扫(向上取整)
  (select to_char(nvl(sum(TYZSL0),0)/B.ZHL000)||B.QLDW00 from YF_TYZSMX where YPQLPC = B.YPQLPC and YPNM00 = B.YPNM00 and MZZYBZ = '1' and CFLSH0 = B.CKDH00)||'/'||to_char(sum(-B.YPZSL0))||B.QLDW00 SMSLM1
from YF_YZYPSQ B,YF_YPCKD0 D
where B.CKDH00 = D.CKDH00
  and D.CKLXBH = '03'
  and B.CXBZ00 = '-'
  and B.CKDH00 is not null
  and exists ( select 1 from BM_YD0000 Y where Y.YPNM00 = B.YPNM00 and nvl(Y.XSWZSM,'0') = '0' )
  group by B.CKDH00,B.YFBMBH,B.BRXM00,B.BQBMBH,B.ZYID00,B.ZYH000,B.CH0000,B.YPQLPC,B.CXPCID,B.YPNM00,B.YPMC00,B.YPGG00,B.ZHL000,B.QLDW00
/