CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YF_YZZSYP
 (
 CKDH00, YFBMBH, BRXM00, BQBMBH, BQBMMC, ZYID00, ZYH000, CH0000, YPQLPC, 
YPNM00, YPMC00, YPGG00, CKSL00, ZHL000, CKDW00, PZBSM0, ZSMSL0, ZSMSL1, 
SMSL00, SMSLMC, SMSLM0, QRRQ00, SFCLYP, SFCYDY

 )
 AS 
select
  B.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,
  sum(B.YPZSL0-B.ZCXSL0) CKSL00,
  B.ZHL000,
  B.QLDW00 CKDW00,
  substrb((select to_char(wm_concat(PZBSM0)) from  YK_YPZSM0 where YPNM00=B.YPNM00),1,200) PZBSM0,                 --药品标识码
  --ceil(sum(A.CKSL00)*A.ZHL000/C.K2J000)  ZSMSL0,                                                                 --追溯码数量(向上取整)
  sum(B.YPZSL0-B.ZCXSL0) ZSMSL0,
  --floor(sum(A.CKSL00)*A.ZHL000/C.K2J000) ZSMSL1,                                                                 --追溯码数量(向下取整)
  sum(B.YPZSL0-B.ZCXSL0) ZSMSL1,
  ( select nvl(sum(YPZSL0),0)/B.ZHL000 from YF_CFZSMX where YPQLPC = B.YPQLPC and YPNM00 = B.YPNM00 and MZZYBZ = '1' )  SMSL00,     --已经扫码数量
  ( select TO_CHAR(nvl(sum(YPZSL0),0)/B.ZHL000)||B.QLDW00 from YF_CFZSMX where YPQLPC = B.YPQLPC and YPNM00 = B.YPNM00 and CFLSH0 = B.CKDH00 and MZZYBZ = '1' )||'/'||to_char(sum(B.YPZSL0-B.ZCXSL0))||B.QLDW00 SMSLMC,  --已扫/应扫(向上取整)
  ( select TO_CHAR(nvl(sum(YPZSL0),0)/B.ZHL000)||B.QLDW00 from YF_CFZSMX where YPQLPC = B.YPQLPC and YPNM00 = B.YPNM00 and CFLSH0 = B.CKDH00 and MZZYBZ = '1' )||'/'||to_char(sum(B.YPZSL0-B.ZCXSL0))||B.QLDW00 SMSLM0,   --已扫/应扫(向下取整)
  D.QRRQ00,
  decode((select nvl(M.ZSCLBZ,'0') from BM_YD0000 M where M.YPNM00 = B.YPNM00),'2','Y','3','Y','N') SFCLYP,
  'N' SFCYDY
from YF_YZYPSQ B,YF_YPCKD0 D
where B.CKDH00 = D.CKDH00
  and D.CKLXBH = '03'
  and ( B.CXBZ00 = 'Z' or ( B.CXBZ00 = '+' and B.YPZSL0 > B.ZCXSL0 ))
  and B.YZXZ00 <> '3'
  and exists ( select 1 from YK_YPZSM0 E where E.YPNM00 = B.YPNM00)
  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.YPNM00,B.YPMC00,B.YPGG00,B.ZHL000,B.QLDW00,D.QRRQ00
union all
select
  B.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,
  sum(B.YPZSL0-B.ZCXSL0) CKSL00,
  B.ZHL000,
  B.QLDW00 CKDW00,
  substrb((select to_char(wm_concat(PZBSM0)) from  YK_YPZSM0 where YPNM00=B.YPNM00),1,200) PZBSM0,                 --药品标识码
  --ceil(sum(A.CKSL00)*A.ZHL000/C.K2J000)  ZSMSL0,                                                                 --追溯码数量(向上取整)
  sum(B.YPZSL0-B.ZCXSL0) ZSMSL0,
  --floor(sum(A.CKSL00)*A.ZHL000/C.K2J000) ZSMSL1,                                                                 --追溯码数量(向下取整)
  sum(B.YPZSL0-B.ZCXSL0) ZSMSL1,
  ( select nvl(sum(YPZSL0),0)/B.ZHL000 from YF_CFZSMX where YPQLPC = B.YPQLPC and YPNM00 = B.YPNM00 and MZZYBZ = '1' )  SMSL00,     --已经扫码数量
  ( select TO_CHAR(nvl(sum(YPZSL0),0)/B.ZHL000)||B.QLDW00 from YF_CFZSMX where YPQLPC = B.YPQLPC and YPNM00 = B.YPNM00 and CFLSH0 = B.CKDH00 and MZZYBZ = '1' )||'/'||to_char(sum(B.YPZSL0-B.ZCXSL0))||B.QLDW00 SMSLMC,  --已扫/应扫(向上取整)
  ( select TO_CHAR(nvl(sum(YPZSL0),0)/B.ZHL000)||B.QLDW00 from YF_CFZSMX where YPQLPC = B.YPQLPC and YPNM00 = B.YPNM00 and CFLSH0 = B.CKDH00 and MZZYBZ = '1' )||'/'||to_char(sum(B.YPZSL0-B.ZCXSL0))||B.QLDW00 SMSLM0,   --已扫/应扫(向下取整)
  D.QRRQ00,
  SF_YF_ZYSFCLYP(B.YPNM00,'3') SFCLYP,
  'Y'  SFCYDY
from YF_YZYPSQ B,YF_YPCKD0 D
where B.CKDH00 = D.CKDH00
  and D.CKLXBH = '03'
  and ( B.CXBZ00 = 'Z' or ( B.CXBZ00 = '+' and B.YPZSL0 > B.ZCXSL0 ))
  and B.YZXZ00 = '3'
  and exists ( select 1 from YK_YPZSM0 E where E.YPNM00 = B.YPNM00)
  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.YPNM00,B.YPMC00,B.YPGG00,B.ZHL000,B.QLDW00,D.QRRQ00
/