-- Start of DDL script for VW_BQ_JCYYSQ
-- Generated 8-二月-14  9:23:13 am
-- from fw2-SD_HOSPITAL:1

-- View VW_BQ_JCYYSQ

CREATE OR REPLACE VIEW vw_bq_jcyysq (
   brid00,
   zyghid,
   yjksbh,
   yjdjh0,
   zyhghh,
   brxm00,
   yexm00,
   brxb00,
   csrq00,
   brnl00,
   zlxmid,
   zlxmjc,
   zlxmjc1,
   kdysxm,
   bmmc00,
   kdksbh,
   kdrq00,
   kdsj00,
   bwmc00,
   jzbz00,
   xmzt00,
   sfdjh0,
   cxdjh0,
   kdbq00,
   sqid00,
   yybz00,
   yyrq00,
   yysd00,
   jcsqdh,
   jkyyh0,
   hbdjh0,
   jczid0,
   jczmc0,
   yyzt00,
   dyzt00,
   zysx00,
   bdbz00,
   yjksmc,
   ch0000,
   mzzybz,
   tx0000 )
AS
select
  Y.BRID00, --病人id
  Y.ZYGHID,
  Y.YJKSBH, --医技科室编号
  Y.YJDJH0, --医技单据号
  Y.ZYHGHH, --住院挂号号
  Y.BRXM00, --病人姓名
  Y.YEXM00, --婴儿姓名
  Y.XB0000, --性别
  Y.CSRQ00, --出生日期
  SF_BQ_CSRQTONL(Y.CSRQ00) BRNL00, --病人年龄
  Y.ZLXMID, --诊疗项目id
  substr(SF_YJ_HBDJHMC(Y.YJDJH0),1,250) ZLXMJC , --诊疗项目简称
  Y.ZLXMJC ZLXMJC1,
  Y.KDYSXM, --开单医生姓名
  B.BMMC00, --开单科室名称
  Y.KDKSBH, --开单科室编号
  Y.KDRQ00, --开单日期
  Y.KDSJ00,
  Y.BWMC00, --部位名称
  DECODE(Y.JZBZ00, NULL, '普通', 'Y', '急诊', 'N', '普通', '普通') JZBZ00,
  DECODE(Y.XMZT00,'0','已申请','1','已计价','2','已收费','3','已执行','4','已报告','5','疑退','6','已作废','7','已退费') XMZT00,
  Y.SFDJH0,
  Y.CXDJH0,
  Y.KDBQ00,
  Y.SQID00,
  decode(nvl(Y.YYID00,0),0,'','已预约') YYBZ00 ,  --预约标志
  C.YYRQ00,
   DECODE((SELECT F.JCZID0 FROM BM_ZLZD00 F WHERE F.ZLXMID=Y.ZLXMID),'20',yysd00||'来等候',
  '21',C.YYSJ00||'提早30分钟到',
  '22',substr(yysd00,1,5)||'来等候','23',C.YYSJ00||'提早30分钟到','24',C.YYSJ00||'来等候',
  '25',C.YYSJ00||'来等候',
 decode((select a.KSPBSJ from YJ_KSPBB0 a,yj_kspbhb b where a.kspbid=b.kspbid and c.pbhbid=b.pbhbid
 and rownum=1),'上午',
 least(to_char(substr(yysd00,1,5)),to_char('10:00')),substr(yysd00,1,5))||'来等候') yysd00,



  C.JCSQDH,
  C.JKYYH0,
  nvl(Y.HBDJH0,Y.YJDJH0) HBDJH0,
  (select JCZID0 from BM_ZLZD00 where ZLXMID=Y.ZLXMID) JCZID0,
  (select F.YYXMMC from BM_ZLZD00 E,YJ_YYZLZD F where E.ZLXMID=Y.ZLXMID and E.JCZID0=F.XMWHID and XMWHID>0) JCZMC0,
  nvl(c.YYZT00,'-1') YYZT00,
  decode(nvl(C.DYCS00,0),0,'未打印','已打印') DYZT00,  --打印状态
  --(SELECT E.JCZYSX FROM YJ_YYZLZD E,BM_ZLZD00 F WHERE E.XMWHID=F.JCZID0 AND F.ZLXMID=Y.ZLXMID) ZYSX00,
  (select f.JCQZYSX from bm_zlzd00 f where f.zlxmid=y.zlxmid) zysx00,
  decode(nvl(C.DYCS00,0),0,'','补打')  BDBZ00,
  (select BMMC00 from BM_BMBM00 where BMBH00=Y.YJKSBH) YJKSMC,
  Y.CH0000,
  DECODE(Y.MZZYBZ,0,'门诊',1,'住院',Y.MZZYBZ) mzzybz,
  decode(y.yjksbh,'531','延时30分钟请重新预约,不便之处,请予谅解!',null)
from YJ_YW0000 Y, BM_BMBM00 B,YJ_YYBRB0 C
  where  Y.YJKSBH<>0 AND Y.MZZYBZ='1'
    and Y.KDKSBH=B.BMBH00(+)  and Y.YJDJH0=C.YJDJH0 and Y.YYID00=C.YYID00 and (nvl(HBDJH0,0)=0 or HBDJH0=Y.YJDJH0)
union all
select
  Y.BRID00, --病人id
  Y.ZYGHID,
  Y.YJKSBH, --医技科室编号
  Y.YJDJH0, --医技单据号
  Y.ZYHGHH, --住院挂号号
  Y.BRXM00, --病人姓名
  Y.YEXM00, --婴儿姓名
  Y.XB0000, --性别
  Y.CSRQ00, --出生日期
  SF_BQ_CSRQTONL(Y.CSRQ00) BRNL00, --病人年龄
  Y.ZLXMID, --诊疗项目id
  substr(SF_YJ_HBDJHMC(Y.YJDJH0),1,250) ZLXMJC , --诊疗项目简称
  Y.ZLXMJC ZLXMJC1,
  Y.KDYSXM, --开单医生姓名
  (select BMMC00 from BM_BMBM00 where BMBH00=Y.KDKSBH) BMMC00, --开单科室名称
  Y.KDKSBH, --开单科室编号
  Y.KDRQ00, --开单日期
  Y.KDSJ00,
  Y.BWMC00, --部位名称
  DECODE(Y.JZBZ00, NULL, '普通', 'Y', '急诊', 'N', '普通', '普通') JZBZ00,
  DECODE(Y.XMZT00,'0','已申请','1','已计价','2','已收费','3','已执行','4','已报告','5','疑退','6','已作废','7','已退费') XMZT00,
  Y.SFDJH0,
  Y.CXDJH0,
  Y.KDBQ00,
  Y.SQID00,
  decode(nvl(Y.YYID00,0),0,'','已预约') YYBZ00 ,  --预约标志
  '' YYRQ00,
  '' YYSD00,
  '' JCSQDH,
  '' JKYYH0,
  nvl(Y.HBDJH0,Y.YJDJH0) HBDJH0,
  (select JCZID0 from BM_ZLZD00 where ZLXMID=Y.ZLXMID) JCZID0,
  (select F.YYXMMC from BM_ZLZD00 E,YJ_YYZLZD F where E.ZLXMID=Y.ZLXMID and E.JCZID0=F.XMWHID and XMWHID>0) JCZMC0,
  '-1' YYZT00,
  '未打印'  DYZT00,  --打印状态
  --(SELECT E.JCZYSX FROM YJ_YYZLZD E,BM_ZLZD00 F WHERE E.XMWHID=F.JCZID0 AND F.ZLXMID=Y.ZLXMID) ZYSX00,
  (select f.JCQZYSX from bm_zlzd00 f where f.zlxmid=y.zlxmid) zysx00,
  ' ' BDBZ00,
  (select BMMC00 from BM_BMBM00 where BMBH00=Y.YJKSBH) YJKSMC,
  Y.CH0000,
  DECODE(Y.MZZYBZ,0,'门诊',1,'住院',Y.MZZYBZ) mzzybz,
  null tx0000
from YJ_YW0000 Y
  where  Y.YJKSBH<>0 AND Y.MZZYBZ='1' and y.cxsl00='0' and y.sl0000>'0' and y.xmzt00<>'6'
      and nvl(Y.YYID00,0)=0 and exists(select 1 from BM_ZLZD00 where ZLXMID=Y.ZLXMID and nvl(JCZID0,0)>0)
/

-- End of DDL script for VW_BQ_JCYYSQ