CREATE OR REPLACE VIEW SD_HOSPITAL.VW_BQ_APPOINTPROMPT
 (
 XZBZ00, YYID00, YJDJH0, BZ0000, YYRQ00, YYSJ00, PACSZT, PSCZRQ, PSCZSJ, 
PSCZR0, HISZT0, HSCZRQ, HSCZSJ, HISQRR, ZLXMJC, ZYHGHH, RYCWH0, DQKS00, 
KSMC00, DQBQ00, BQMC00, BRXM00, BRXB00, JCQZY0, JCQZY1, BRJTDZ, BRDH00, 
BRNL00, YJKSBH, BWMC00, YJKSMC, LCLJBZ, YYZTMC, KDRQ00, SL0000, HJJE00, 
XXNR00, JCSQDH, JKYYH0, DYZTMC, TZBH00, XMZT00, YYSD00

 )
 AS 
select   
     0 as XZBZ00,
     a.YYID00,             --预约ID
     a.YJDJH0,             --医技单据号
     a.BZ0000,             --反馈注意事项 
     a.YYAPRQ YYRQ00,      --预约安排日期
     a.YYAPSJ YYSJ00,      --预约安排时间
     a.PACSZT,             --PSCS确认状态   
     a.PSCZRQ,            --预约安排操作日期
     a.PSCZSJ,            --预约安排操作时间
     a.PSCZR0,            --预约安排操作人姓名
     --a.JCSQDH,            --PACS的检查申请单号   
     a.HISZT0,            --HIS读取状态
     a.HSCZRQ,            --HIS读取日期
     a.HSCZSJ,            --HIS读取时间
     a.HISQRR,            --HIS确认人 
     b.ZLXMJC,
     c.ZYH000,
     c.RYCWH0,
     c.DQKS00,
     (select BMMC00 from BM_BMBM00 where BMBH00=c.DQBQ00) KSMC00,
     c.DQBQ00,
     (select BMMC00 from BM_BMBM00 where BMBH00=c.DQBQ00) BQMC00,
     c.XM0000 BRXM00,
     c.XB0000 BRXB00,     
     (select JCQZYSX from BM_ZLZD00 where ZLXMID=b.ZLXMID) JCQZY0,     --注意事项 
     substr((select JCQZYSX from BM_ZLZD00 where ZLXMID=b.ZLXMID),1,120) JCQZY1,     --注意事项dbgrid显示
     d.BRJTDZ,
     d.BRDH00,
     substr(SF_BQ_CSRQTONL(c.CSRQ00),1,50) BRNL00,
     b.YJKSBH,  --医技科室
     b.BWMC00,   --部位
     (select BMMC00 from BM_BMBM00 where BMBH00=b.YJKSBH) YJKSMC,
     decode(nvl(DQLJID,0),0,'','CP') LCLJBZ,
     decode(b.XMZT00,'6','取消',decode(PACSZT,'0','申请',decode(HISZT0,'0','已预约','病区确认'))) YYZTMC,
     b.KDRQ00,
     b.SL0000,  --数量
     b.ZJE000,   --总金额
     d.BRXM00||'['||ZLXMJC||'  '||b.BWMC00||'] 预约时间'||to_char(to_date(YYAPRQ,'YYYYMMDD'),'YYYY.MM.DD')||' '||YYAPSJ||' 已预约成功',
     JCSQDH,  --PACS的检查申请单号   
     (select JKYYH0 from YJ_YYBRB0 where YJDJH0=a.YJDJH0 and YYID00=a.YYID00 and rownum=1) JKYYH0,   --PACS预约号
     decode(nvl((select SQDYCS from YJ_YYBRB0 where YJDJH0=a.YJDJH0 and YYID00=a.YYID00 and rownum=1),0),0,'未打印','已打印') DYZTMC,  --DYCS00-->SQDYCS
     nvl(a.TZBH00,nvl(b.HBDJH0,b.YJDJH0)) TZBH00,
     b.XMZT00,    --项目状态
     (select yysd00 from YJ_YYBRB0 where yjdjh0=b.yjdjh0) YYSD00
  from YJ_JCYYFK a,YJ_YW0000 b,ZY_BRXXB0 c,BM_BRXXB0 d
    where a.YJDJH0>0 and a.YJDJH0=b.YJDJH0 and b.ZYGHID=c.ZYID00 and c.BRID00=d.BRID00 and KDKSBH<>YJKSBH and a.YYID00>0
    and not exists(select 1 from yj_YYBRB0 where YYID00=a.YYID00 and YYZT00='2')
/