-- Start of DDL Script for View SD_HOSPITAL.VW_YF_ZYYZ00_DHDJ
-- Generated 28-2月-2025 12:02:42 from SD_HOSPITAL@his_ydfe_zsk

CREATE OR REPLACE VIEW vw_yf_zyyz00_dhdj (
   cflsh0,
   yzid00,
   mzzybz,
   tqdjh0,
   zyhghh,
   ch0000,
   brxm00,
   xb0000,
   csrq00,
   brnl00,
   ysxm00,
   ghksmc,
   zyts00,
   srrq00,
   jzrq00,
   jzsj00,
   cfzje0,
   qlzt00,
   djh000,
   fyrq00,
   fysj00,
   zydjs0,
   bz0000,
   cfdgs0,
   zymrjs,
   yfmc00,
   jybz00,
   jyfa00,
   jybzl0,
   lsh000,
   fscgbz,
   id0000,
   yfbmbh,
   zyghid,
   brdh00,
   bqmc00,
   bzl000,
   brid00 )
AS
select  
  --'0' CFLSH0,
  --C.YZID00||'_'||D.TQDJH0 as CFLSH0,
  C.YZID00 as CFLSH0,
  C.YZID00,
  '1' MZZYBZ,
  D.TQDJH0,
  B.ZYH000 ZYHGHH,
  B.RYCWH0 CH0000,
  B.XM0000 BRXM00,
  B.XB0000, 
  B.CSRQ00,  
  substr(sf_sf_csrqtonl_num(B.CSRQ00),1,5) BRNL00,
  (select ZWXM00 from BM_YGBM00 where YGBH00=c.KZYS00) YSXM00,
  (select BMMC00 from BM_BMBM00 where BMBH00=c.KSH000) GHKSMC,  
  C.ZQCYTS ZYTS00, 
  C.LRRQ00 SRRQ00,
  D.CZRQ00 JZRQ00,
  D.CZSJ00 JZSJ00,
  (select sum(YPZSL0*LSDJ00) from YF_YZYPSQ where ZYID00=C.ZYID00 and YZID00=C.YZID00 and TQDJH0=D.TQDJH0) CFZJE0,
  '2' QLZT00,  
  1 DJH000,
  D.CZRQ00 FYRQ00,
  D.CZSJ00 FYSJ00,
  C.JYCS00 ZYDJS0, 
  ' '  BZ0000,
  '5' CFDGS0
  ,nvl(D.MRCS00,2) ZYMRJS
  ,(select YPYFMC from BM_YPYF00 where YFID00=C.YFID00) YFMC00
  ,D.JYBZ00   --煎药备注 
  ,D.JYFA00   --煎药方案
  ,D.JYBZL0   --煎药包装量
  ,D.LSH000
  ,D.FSCGBZ
  ,D.ID0000
  ,C.YFBMBH
  ,B.ZYID00 ZYGHID
  ,(select nvl(BRDH00,BRLXDH) from BM_BRXXB0 where BRID00=B.BRID00) BRDH00
  ,(select BMMC00 from BM_BMBM00 where BMBH00=nvl(c.BQH000,B.DQBQ00)) BQMC00
  ,C.BZL000
  ,B.BRID00
  from ZY_BRXXB0 B,BQ_YPYZ00 C ,YF_ZYDJJK D
  where  c.YZID00=d.YZID00
  and C.ZYID00=B.ZYID00 and D.MZZYBZ='1'
/


-- End of DDL Script for View SD_HOSPITAL.VW_YF_ZYYZ00_DHDJ