create or replace view vw_yj_sykp00 as
select
  Y.YJDJH0, --
  X.CFLSH0, --
  M.CFID00, --
  Y.ZYHGHH GHH000, --
  Y.ZYGHID GHID00, --
  Y.YJKSBH, --
  (select BMMC00 from BM_BMBM00 where BMBH00 = Y.YJKSBH) YJKSMC, --
  Y.BRXM00 XM0000, --
  Y.CH0000, --
  Y.NL0000, --
  M.CFLSH0||'-'||TO_CHAR(NVL(ZB0000,0)) ZB0000, --
  NVL(ZB0000,M.CFID00) ZB0001, --
  Y.KDRQ00, --
  TO_CHAR(SYSDATE,'YYYY.MM.DD')||Y.KDSJ00 QSSJ00, --起始时间
  M.CSL000, --次用量(剂量单位)
  M.JLDW00, --剂量单位
  M.YPMC00, --
  M.YYZF00, --
  (select YPYFMC from BM_YPYF00 P where M.YFID00=P.YFID00 and rownum=1) YPYF00, --药口用法
  decode(M.PCID00, null, 1, 0, 1, (select ZQCS00 from BM_PC0000 C where M.PCID00=C.PCID00 and rownum=1))
    *decode(M.YYSJ00, null,1,0,1,M.YYSJ00)
    *(select decode(D.DXSYBZ, '0', 1, '1', ceil(M.CSL000/D.Z2J000)) from BM_YD0000 D where M.YPNM00=D.YPNM00 and rownum=1) DXSYSL, --
  Y.XB0000, --
  (select BRBLH0 from SF_BRXXB0 S where Y.ZYGHID=S.GHID00) BRBLH0, --
  Y.SYLSH0, --
  M.YPGG00, --
  SP_YJ_CFNRGZ(M.CFID00) CFTSFH, --
  decode(nvl(trim(ZB0000),'Z'),'Z',M.CFID00,ZB0000) ZB0002, --
  subStr(SF_YJ_GETZD0_SYK(Y.ZYHGHH),1,200) ZDMC00, --
  Y.BRID00, --
  decode(Y.ZXRQ00, null, null,
    substr(Y.ZXRQ00,1,4)||'-'||substr(Y.ZXRQ00,5,2)||'-'||substr(Y.ZXRQ00,7,2)) ZXRQGG, --执行日期规格化
  (select PCMC00 from YF_MZCFMX where CFID00 = M.CFID00 and rownum = 1) PCMC00, --频次名称
  (select substr(KMH000, 1, length(KMH000) - 3) from IC_YHXX00 where BRID00 = Y.BRID00 and ZT0000 = '1') KMH001, --卡面卡前N-3位
  (select substr(KMH000, length(KMH000) - 3, 3) from IC_YHXX00 where BRID00 = Y.BRID00 and ZT0000 = '1') KMH002, --卡面卡最后3位
  decode(M.YYSJ00, null,1,0,1,M.YYSJ00) YYSJ00,  --用药时间字段
  (select PSMC00 from BM_PS0000 where M.PSLBID=PSID00) PSMC00,          --皮试名称
  Y.MZZYBZ,
  null sl0000,    ----自定义字段 : 添加了一个数量字段
  null sjxs00,
  (select nvl(b.brdh00, b.brlxdh)
          from bm_brxxb0 b
         where b.brid00 = y.brid00) brdh00,
  Y.CSRQ00 
from YJ_YW0000 Y, YS_CFXXB0 X, YS_CFMX00 M
where Y.YZCFID=X.CFLSH0 and X.CFLSH0=M.CFLSH0 and Y.MZZYBZ='0' and Y.CXDJH0 is null and Y.DCBZ00 in ('1','2')
  and exists(select 1 from BM_YPYF00 P where M.YFID00=P.YFID00 and P.YFLBID in (1,2,6))
union all
select
  Y.YJDJH0, --
  TO_CHAR(M.TQDJH0) CFLSH0, --
  M.YPQLPC CFID00, --
  Y.ZYHGHH GHH000, --
  Y.ZYGHID GHID00, --
  Y.YJKSBH, --
  (select BMMC00 from BM_BMBM00 where BMBH00 = Y.YJKSBH) YJKSMC, --
  Y.BRXM00 XM0000, --
  Y.CH0000, --
  Y.NL0000, --
  M.TQDJH0||'-'||TO_CHAR(NVL(M.YZID00,0)) ZB0000, --
  to_char(NVL(M.YZID00,M.YPQLPC)) ZB0001, --
  Y.KDRQ00, --
  TO_CHAR(SYSDATE,'YYYY.MM.DD')||Y.KDSJ00 QSSJ00, --起始时间
  M.CSL000, --次用量(剂量单位)
  M.JLDW00, --剂量单位
  M.YPMC00, --
  '' YYZF00, --
  (select YPYFMC from BM_YPYF00 P where M.YFID00=P.YFID00 and rownum=1) YPYF00, --药口用法
  decode(M.PCID00, null, 1, 0, 1, (select ZQCS00 from BM_PC0000 C where M.PCID00=C.PCID00 and rownum=1))
    *decode(M.YYSJ00, null,1,0,1,M.YYSJ00)
    *(select decode(D.DXSYBZ, '0', 1, '1', ceil(M.CSL000/D.Z2J000)) from BM_YD0000 D where M.YPNM00=D.YPNM00 and rownum=1) DXSYSL, --
  Y.XB0000, --
  (select BRBLH0 from SF_BRXXB0 S where Y.ZYGHID=S.GHID00) BRBLH0, --
  Y.SYLSH0, --
  M.YPGG00, --
  '' CFTSFH, --
  decode(nvl(trim(M.YZID00),'Z'),'Z',M.YPQLPC,M.YZID00) ZB0002, --
  subStr(SF_YJ_GETZD0_SYK(Y.ZYHGHH),1,200) ZDMC00, --
  Y.BRID00, --
  decode(Y.ZXRQ00, null, null,
    substr(Y.ZXRQ00,1,4)||'-'||substr(Y.ZXRQ00,5,2)||'-'||substr(Y.ZXRQ00,7,2)) ZXRQGG, --执行日期规格化
  M.PCMC00, --频次名称
  (select substr(KMH000, 1, length(KMH000) - 3) from IC_YHXX00 where BRID00 = Y.BRID00 and ZT0000 = '1') KMH001, --卡面卡前N-3位
  (select substr(KMH000, length(KMH000) - 3, 3) from IC_YHXX00 where BRID00 = Y.BRID00 and ZT0000 = '1') KMH002, --卡面卡最后3位
  decode(M.YYSJ00, null,1,0,1,M.YYSJ00) YYSJ00,  --用药时间字段
  ''  PSMC00,          --皮试名称
  Y.MZZYBZ,
  null sl0000,    ----自定义字段 : 添加了一个数量字段
  null sjxs00,
  (select nvl(b.brdh00, b.brlxdh)
          from bm_brxxb0 b
         where b.brid00 = y.brid00) brdh00,
  Y.CSRQ00 
from YJ_YW0000 Y, YF_YZYPSQ M
where Y.YZCFID=M.TQDJH0  and Y.MZZYBZ='1' and Y.CXDJH0 is null and Y.DCBZ00 in ('1','2')
  and exists(select 1 from BM_YPYF00 P where M.YFID00=P.YFID00 and P.YFLBID in (1,2,6));