create or replace view vw_yj_cfsyd0 as
select
  A.CFLSH0, --处方流水号
  A.BRID00, --病人ID
  A.BRXM00 XM0000, --姓名
  A.XB0000, --性别
  B.CFID00, --处方ID
  B.CFLSH0||'-'||TO_CHAR(NVL(B.ZB0000,0)) ZB0000, --组别名称
  NVL(ZB0000,CFID00) ZB0001, --组别
  B.CSL000, --次用量(剂量单位)
  B.JLDW00, --剂量单位
  B.YPMC00, --药品名称
  B.YYZF00, --药品用法
  (select YPYFMC from BM_YPYF00 P where YFID00 = B.YFID00 and ROWNUM=1) YPYF00, --药品用法
  decode(B.PCID00, null, 1, 0, 1, (select ZQCS00 from BM_PC0000 C where B.PCID00=C.PCID00 AND ROWNUM=1))
    * decode(B.YYSJ00, NULL,1,0,1,B.YYSJ00)
    -- * (select decode(D.DXSYBZ, '0', 1, '1', ceil(B.CSL000/D.Z2J000)) from BM_YD0000 D where B.YPNM00=D.YPNM00 AND ROWNUM=1) DXSYSL, --打印数量
    * (select decode(D.DXSYBZ, '0', 1, '1', decode(B.JLDW00,B.CFDW00,ceil(B.CSL000/D.Z2J000*B.ZHL000),ceil(B.CSL000/D.Z2J000))) from BM_YD0000 D where B.YPNM00=D.YPNM00 AND ROWNUM=1) DXSYSL, --打印数量
  (select BRBLH0 from SF_BRXXB0 S where GHID00 = A.GHID00) BRBLH0, --病历号
  B.YPGG00, --药品规格
  SP_YJ_CFNRGZ(B.CFID00) CFTSFH, --
  decode(nvl(trim(ZB0000),'Z'),'Z',B.CFID00,ZB0000) ZB0002, --
  subStr(SF_YS_GETZD0_SYK(a.ghh000),1,200) ZDMC00, --诊断名称
  (select PCMC00 from YF_MZCFMX where CFID00 = B.CFID00 and rownum = 1) PCMC00, --频次名称
  (select SM0000 from bm_pc0000 where PCID00 =  B.PCID00 and rownum = 1) SM0000, --频次中文名称
  (select substr(KMH000, 1, length(KMH000) - 3) from IC_YHXX00 where BRID00 = A.BRID00 and ZT0000 = '1') KMH001, --卡面卡前N-3位
  (select substr(KMH000, length(KMH000) - 3, 3) from IC_YHXX00 where BRID00 = A.BRID00 and ZT0000 = '1') KMH002, --卡面卡最后3位
  A.SRRQ00, --录入日期
  A.SRSJ00, --录入时间
  decode(A.SRRQ00, null, null,
    substr(A.SRRQ00,3,2)||'-'||substr(A.SRRQ00,5,2)||'-'||substr(A.SRRQ00,7,2)) SRRQGG,  --输入日期规格
  trim(B.YYSJ00) YYSJ00, --用药时间
  trim(B.YYSJDW) YYSJDW, --用药时间单位
  trim(B.CFDW00) CFDW00, --处方单位
  substr(SF_XT_CSRQTONL((select BRCSRQ from BM_BRXXB0 where BRID00=a.BRID00)),1,100) NL0000,  --年龄
  pm.sjxs00,
  B.YPZSL0 --药品总数量
from YS_CFXXB0 A, YS_CFMX00 B,bm_yzpcmx pm
where A.CFLSH0 = B.CFLSH0 and pm.pcid00=b.pcid00
  and exists (select 1 from BM_YPYF00 where YFID00 = B.YFID00 and YFLBID in(1,2))
  and a.cfzt00 not in ('2')
  and exists (select 1 from YF_MZCF00 where CFLSH0=a.CFLSH0 and CFZT00 not in ('3','5','6'))
  and exists (select 1 from YF_MZCFMX where CFID00=b.CFID00 and CXBZ00='Z')
  order by pm.sjxs00
;