-- Start of DDL Script for View SD_HOSPITAL.VW_YF_YTYKC0
-- Generated 23-9月-2025 17:52:36 from SD_HOSPITAL@his

CREATE OR REPLACE VIEW vw_yf_ytykc0 (
   yfbmbh,
   yfbmmc,
   bqbmbh,
   bqbmmc,
   ypnm00,
   ypmc00,
   ypgg00,
   ykkcsl,
   sjkcsl,
   dkzsl0,
   zsl000,
   ghsl00,
   wghsl0,
   zyfydw,
   ysbz00 )
AS
select 
  Q.YFBMBH YFBMBH,     --药房部门编号
  (select BMMC00 from BM_BMBM00 where BMBH00=Q.YFBMBH) YFBMMC,    --药房部门名称
  Q.BQBMBH,            --病区部门编号
  (select BMMC00 from BM_BMBM00 where BMBH00=Q.BQBMBH) BQBMMC,    --病区部门名称
  Q.YPNM00,            --药品内码
  D.YPMC00,            --药品名称
  D.YPGG00,            --药品规格
  round(Q.YKKCSL/D.Z2J000,3) YKKCSL,     --预扣库存数量
  round(Q.SJKCSL/D.Z2J000,3) SJKCSL,     --实际库存数量
  round(Q.DKZSL0/D.Z2J000,3) DKZSL0,     --抵扣总数量 一直累加
  round(Q.ZSL000/D.Z2J000,3) ZSL000,     --总数量 一直累加
  round(Q.GHSL00/D.Z2J000,3) GHSL00,     --归还数量 一直累加
  round(Q.YKKCSL/D.Z2J000,3) WGHSL0,     --未归还数量
  D.ZYFYDW,                               --住院发药单位 
  --(select count(1) from YF_YZYPSQ where YFBMBH=Q.YFBMBH and BQBMBH=Q.BQBMBH and YPNM00=Q.YPNM00 and SRRQ00>=to_char(sysdate-3,'YYYYMMDD') and CXBZ00<>'-' and YZXZ00 in ('0','3','4'))  YSBZ00   --颜色标志，3天没有新的医嘱就标红
   SF_YF_YTYYSBZ(Q.YFBMBH,Q.BQBMBH,Q.YPNM00)  YSBZ00   --颜色标志，3天没有新的医嘱就标红
from YF_YTYKC0 Q, BM_YD0000 D
where Q.YPNM00=D.YPNM00 and Q.YKKCSL>0
/


-- End of DDL Script for View SD_HOSPITAL.VW_YF_YTYKC0