-- Start of DDL Script for View SD_HOSPITAL.VW_YF_YZZSTY
-- Generated 2025-09-22 10:25:01 from SD_HOSPITAL@CHWSY

CREATE OR REPLACE VIEW vw_yf_yzzsty (
   ckdh00,
   yfbmbh,
   brxm00,
   bqbmmc,
   zyid00,
   zyh000,
   ch0000,
   ypqlpc,
   cxpcid,
   ypnm00,
   ypmc00,
   ypgg00,
   ykrkpc,
   scph00,
   cksl00,
   zhl000,
   qldw00,
   pzbsm0,
   zsmsl0,
   zsmsl1,
   smsl00,
   smslm0,
   smslmc )
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,
  0 YKRKPC,
  ' ' SCPH00,
  sum(-B.YPZSL0) CKSL00,
  B.ZHL000,
  B.QLDW00,
  C.PZBSM0,               --药品标识码
  sum(-B.YPZSL0) ZSMSL0, 
  sum(-B.YPZSL0) ZSMSL1,     --追溯码数量(向下取整)                                                                --追溯码数量
  (select nvl(sum(X.TYZSL0),0)/B.ZHL000 from YF_TYZSMX X where X.CXID00 = B.CXPCID and X.YPNM00 = B.YPNM00 and X.MZZYBZ = '1' and X.CFLSH0 = trim(B.CKDH00))  SMSL00,     --已经扫码数量
  (select nvl(sum(X.TYZSL0),0)/B.ZHL000 from YF_TYZSMX X where X.CXID00 = B.CXPCID and X.YPNM00 = B.YPNM00 and X.MZZYBZ = '1' and X.CFLSH0 = trim(B.CKDH00))||'/'||to_char(sum(-B.YPZSL0))||b.QLDW00  SMSLM0,  --已扫/应扫(向下取整)
  (select nvl(sum(X.TYZSL0),0)/B.ZHL000 from YF_TYZSMX X where X.CXID00 = B.CXPCID and X.YPNM00 = B.YPNM00 and X.MZZYBZ = '1' and X.CFLSH0 = trim(B.CKDH00))||'/'||to_char(sum(-B.YPZSL0))||b.QLDW00  SMSLMC   --已扫/应扫(向上取整)
from YF_YZYPSQ B,BM_YD0000 C 
where   
   B.YPNM00 = C.YPNM00
  and B.CXBZ00 = '-'
  and B.CKDH00 is not null
  and exists(select 1 from YF_CFZSMX where ZYID00=b.ZYID00 and YZLB00='1' and YPQLPC=b.CXPCID)
  group by B.CKDH00,B.YFBMBH,B.BRXM00,B.BQBMBH,B.ZYID00,B.ZYH000,B.CH0000,B.YPQLPC,B.CXPCID,B.YPNM00,B.YPMC00,C.PZBSM0,B.YPGG00,B.ZHL000,b.ZHL000,b.QLDW00
/


-- End of DDL Script for View SD_HOSPITAL.VW_YF_YZZSTY