CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YF_ZSMZYCX
 (
 CKDH00, YFBMBH, BRXM00, BQBMBH, BQBMMC, ZYID00, ZYH000, CH0000, YPQLPC, 
YPNM00, YPMC00, YPGG00, YPZSL0, ZHL000, QLDW00, PZBSM0, YSMSL0, YSCSL0, 
QRRQ00, SRRQ00, SRRQSJ, QRRQSJ, FYRQ00, FYRXM0, SFKSLY

 )
 AS 
select
  D.CKDH00,
  B.YFBMBH,
  B.BRXM00,
  B.BQBMBH,
  ( select BMMC00 from BM_BMBM00 where BMBH00 = B.BQBMBH ) BQBMMC,
  B.ZYID00,
  B.ZYH000,
  B.CH0000,
  B.YPQLPC,
  B.YPNM00,
  B.YPMC00,
  B.YPGG00, 
  (B.YPZSL0-B.ZCXSL0) ypzsl0,
  B.ZHL000,
  B.QLDW00,
  substrb((select to_char(wm_concat(PZBSM0)) from  YK_YPZSM0 where YPNM00=B.YPNM00),1,200) PZBSM0,    --药品标识码
  decode(B.CXBZ00,'-',(select nvl(sum(M.TYZSL0),0)/B.ZHL000 from YF_TYZSMX M where M.CFLSH0 = D.CKDH00 and M.YPQLPC = B.YPQLPC and M.YPNM00 = B.YPNM00 and M.MZZYBZ = '1' and M.YZLB00 = '1'),
  ( select nvl(sum(YPZSL0),0)/B.ZHL000 from YF_CFZSMX where CFLSH0 = D.CKDH00 and YPQLPC = B.YPQLPC and YPNM00 = B.YPNM00 and MZZYBZ = '1' and YZLB00 = '1'))  YSMSL0,     --已经扫码数量
  decode(B.CXBZ00,'-',( select nvl(sum(B.YPZSL0),0)/B.ZHL000 from YF_YZZSMX M,YF_YBZSRZ N where M.CFLSH0 = N.CFLSH0 and M.ZSID00 = N.ZSID00 and N.LB0000 in('1','5') and M.MZZYBZ = '1' and YZLB00 = '1' and YPQLPC = B.CXPCID  ),
  ( select nvl(sum(YPZSL0),0)/B.ZHL000 from YF_YZZSMX M,YF_YBZSRZ N where M.ZSID00 = N.ZSID00 and N.LB0000 = '4' and M.MZZYBZ = '1' and YZLB00 = '1' and N.CFLSH0 = B.CKDH00 and YPQLPC = B.YPQLPC )) YSCSL0, --已上传数量
  D.QRRQ00,
  D.SRRQ00,
  D.SRRQ00||' '||D.SRSJ00  SRRQSJ,
  D.QRRQ00||' '||D.QRSJ00  QRRQSJ,
  B.FYRQ00,      --发药人编号
  ( select ZWXM00 from BM_YGBM00 where YGBH00 = B.FYR000 ) FYRXM0, --发药人姓名
  B.SFKSLY
from YF_YZYPSQ B,BM_YD0000 C,YF_YPCKD0 D
where B.CKDH00 = D.CKDH00
  and B.YPNM00 = C.YPNM00 and   nvl(c.XSWZSM,0) <> '1'
  and exists( select 1 from YK_YPZSM0 where YPNM00 = B.YPNM00)
  and ( B.CXBZ00 = 'Z' or ( B.CXBZ00 = '+' and B.YPZSL0 > B.ZCXSL0 ))
/