create or replace view vw_bq_zyhlxmcx_fy
(zyh000, zyid00, djh000, xmmc00, czrq00, kzys00, xmsl00, hjje00, brxm00, xb0000, brnl00, kzysxm, zdmc00, bz0000, kdbq00)
as
select  d.ZYH000,      --住院号
        d.ZYID00,       --住院ID
        a.DJH000,             --单据号
        a.XMMC00,             --项目名称
        a.CZRQ00,             --医嘱启用日期
        a.KDYS00,             --开医嘱医生
        a.XMSL00,      --数量
        a.HJJE00,     --金额
        d.XM0000 BRXM00 ,            --病人姓名
        d.XB0000,             --性别
        substr(SF_ZS_CSRQTONL(d.CSRQ00),1,20) BRNL00,  --病人年龄
        (select ZWXM00 from BM_YGBM00 where YGBH00=a.KDYS00) kzysxm ,  --开嘱医生姓名
        substr(SF_ZS_LJ_RYZDMC(d.zyid00,'c',0),1,100) ZDMC00, --诊断名称
        ' ' BZ0000,
        a.KDBQ00
  from  ZY_FYMX00 a,ZY_BRFY00 b,ZY_BRXXB0 d where  a.DJH000=b.DJH000 and b.ZYID00=d.ZYID00  
        and exists(select 1 from BM_YYSFXM where SFXMID=a.XMBH00 and FLAG00='1' and bh0000 like '04%' and BH0000 not like '040%' and BH0000 not like '048%' );