-- Start of DDL script for VW_BQ_ZYHLXMCX
-- Generated 2-二月-15  4:03:26 pm
-- from FW2-SD_HOSPITAL:1

-- View VW_BQ_ZYHLXMCX

CREATE OR REPLACE VIEW vw_bq_zyhlxmcx (
   zyh000,
   zyid00,
   yzmxid,
   zlxmjc,
   qyrq00,
   kzys00,
   sl0000,
   hjje00,
   brxm00,
   xb0000,
   brnl00,
   kzysxm,
   zdmc00,
   bz0000,
   kdbq00 )
AS
select  d.ZYH000,      --住院号
        d.ZYID00,       --住院ID
        a.YZMXID,             --医嘱号
        a.ZLXMJC,             --项目名称
        a.QYRQ00,             --医嘱启用日期
        a.KZYS00,             --开医嘱医生
        (select sum(SL0000) from YJ_YW0000 where ZYGHID=a.ZYID00 and YZCFID=a.YZMXID and sfdjh0<>'0' ) SL0000,      --数量
        (select sum(ZJE000) from YJ_YW0000 where ZYGHID=a.ZYID00 and YZCFID=a.YZMXID and sfdjh0<>'0' ) HJJE00 ,     --金额
        d.XM0000 BRXM00 ,            --病人姓名
        d.XB0000,             --性别
        SF_ZS_CSRQTONL(d.CSRQ00) BRNL00,  --病人年龄
        (select ZWXM00 from BM_YGBM00 where YGBH00=a.KZYS00) kzysxm ,  --开嘱医生姓名
        --substr(SF_ZS_LJ_RYZDMC(a.zyid00,'3',0),1,30) ZDMC00, --诊断名称
        substr(SF_ZS_RYZDMC_DZBL(a.zyid00,'c',0),1,30) ZDMC00, --诊断名称
        a.BZ0000,
        a.BQH000 KDBQ00
  from  bq_yjyz00 a,ZY_BRXXB0 d where  a.ZYID00=d.ZYID00 and a.YZZT00<>'4'
  and exists(select 1 from YJ_YW0000 e where  a.yzmxid=e.yzcfid and a.zyid00=e.zyghid and  e.sfdjh0<>'0'  )
  and exists (select 1 from xt_zlsfgx b,bm_yysfxm c where b.zlxmid=a.zlxmid and c.bh0000 like '04%' and
               b.sfxmid=c.sfxmid and c.flag00='1' and c.bh0000 not like '040%' and c.bh0000 not like '048%' )
/

-- End of DDL script for VW_BQ_ZYHLXMCX