PROMPT 129.病人疾病登记表(VW_YJ_BRJBDJ2)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- liuj       2011.09.26   增加诊断ID
-- gzj        2019.04.26   对JBMC00字段截取长度 for YJ9-20190425-001
-- luowf      2022.12.18   增加就诊日期时间、挂号类型JZRQ00、JZSJ00、GHLBMC
create or replace view VW_YJ_BRJBDJ2 (
   ZDXXID,   ZYID00,   GHH000,   XM0000,   XB0000,   ZDKS00,   ZDYS00,   ZDYSXM,   BRNL00,
   BRJTDZ,   BRSZQY,   ZDKSMC,   YQBGBZ,   ZDM000,   JBMC00,   ZDRQ00,   LBMC00,   
   ZDLB00,   ZXYZD0,   ZDFF00,   ZLFA00,   ZDFF01,   ZLFA01,   ZDRQ02,   GHRYRQ,
   BRZY00,   TYDWMC,   SFCZ00,   FBRQ00,   GHJZYS,   GHJZKS,   JZRQ00,   JZSJ00,   GHLBMC 
)
as
select
    h.ZDXXID,
    a.GHID00, 
    A.GHH000, 
    A.XM0000, 
    A.XB0000, 
    H.JZKS00, 
    H.JZYS00, 
    nvl(A.JZYSXM,H.JZYSXM),
    substr(SF_BQ_CSRQTONL(A.CSRQ00),1,8) as BRNL00,
    (select substrb(B.BRSZSF||B.BRSZS0||B.BRSZZ0||B.BRJTDZ,1,100)from BM_BRXXB0 b where a.BRID00=b.BRID00) BRJTDZ,
    --2008.6.25 sechen是否区域
    (select decode(BRSZQY,'0','区域内','区域外')  from BM_BRXXB0 b where a.BRID00=b.BRID00) BRSZQY,  
    (select BMMC00 from BM_BMBM00 E where a.JZKS00=e.BMBH00) JZKSMC, 
    decode(h.YQBGBZ, '1', '是', '否'),trim(h.icd900) ZDM000,
    nvl(substr(H.ZDMC00,1,100),'?') JBMC00, H.ZDRQ00, '门诊' LBMC00,
    decode(ZZDBZ0,'3','主要诊断','4','其他诊','5','丛属诊','1','中医疾病','2','中医症候') ZDLB00,
    decode(ZZDBZ0,'3','西医诊断','4','西医诊断','5','西医诊断','1','中医诊断','2','中医诊断') ZXYZD0,
    decode(ZDFF00,'0','症状','1','检查'),
    decode(ZLFA00,'0','饮片','1' ,'成药','2', '院内制剂','3', '其他','4', '未按方案执行'),
    ZDFF00 ZDFF01,
    ZLFA00 ZLFA01,
    nvl(H.ZDRQ00, A.GHRQ00),
    A.GHRQ00,
    (select BRZY00 from BM_BRXXB0 b where A.BRID00=B.BRID00) BRZY00,
    (select TYDWMC from BM_BRXXB0 b where A.BRID00=B.BRID00) TYDWMC,
    (select decode(SFCZ00,'0','初诊','1','复诊') from SF_BRXXB0 F where A.GHID00=F.GHID00 and rownum = 1) SFCZ00,
    (select FBRQ00 from YS_BRBQB0 F where A.GHID00=F.GHID00 and rownum = 1) FBRQ00,
    A.JZYS00 GHJZYS, 
    A.JZKS00 GHJZKS,
    nvl(a.JZRQ00,A.GHRQ00) JZRQ00,
    nvl(a.JZSJ00,A.GHSJ00) JZSJ00,
    (select LBMC00 from BM_GHLBB0 where LBBH00=A.GHLB00 ) GHLBMC
from SF_BRXXB0 a, YS_BRZDXX h
  where a.GHID00=h.GHID00(+)
union all
select
    h.ID0000,
    a.ZYID00, 
    A.ZYH000, 
    A.XM0000, 
    A.XB0000, 
    A.DQKS00, 
    decode(nvl(A.SXYS00,0), 0, decode(nvl(A.ZZYS00,0), 0, A.ZRYS00, A.ZZYS00), SXYS00),
    decode(nvl(A.SXYS00,0), 0, decode(nvl(A.ZZYS00, 0), 0, A.ZRYSXM, A.ZZYSXM), SXYSXM),
    substr(SF_BQ_CSRQTONL(A.CSRQ00),1,8) BRNL00,
    (select substrb(B.BRSZSF||B.BRSZS0||B.BRSZZ0||B.BRJTDZ,1,100) from BM_BRXXB0 B where A.BRID00=B.BRID00) BRJTDZ,
    (select decode(BRSZQY,'0','区域内','区域外')  from BM_BRXXB0 b where a.BRID00=b.BRID00) BRSZQY,
    (select BMMC00 from BM_BMBM00 E where A.DQKS00=E.BMBH00) JZKSMC, 
    decode(h.YQBGBZ, '1', '是', '否'),
    trim(h.ZDM000) ZDM000,
    substr(h.ZDMC00,1,100) JBMC00,
    H.ZDRQ00, 
    '住院' LBMC00,
    z.MC0000 ZDLB00, '西医诊断' ZXYZD0,
    '' ZDFF00,
    '' ZLFA00,
    '' ZDFF01,
    '' ZLFA01,
    nvl(H.ZDRQ00, A.RYRQ00), 
    A.RYRQ00,
    (select BRZY00 from BM_BRXXB0 B where A.BRID00=B.BRID00) BRZY00,
    (select TYDWMC from BM_BRXXB0 B where A.BRID00=B.BRID00) TYDWMC,
    '' SFCZ00, 
    '' FBRQ00, 
    A.SXYS00, 
    A.DQKS00,
    A.RYRQ00 JZRQ00,
    A.RYSJ00 JZSJ00,
    '' GHLBMC
from ZY_BRXXB0 A, BQ_BRZDXX H, XT_ZDLB00 Z
  where A.ZYID00=H.ZYID00(+) AND H.ZDLB00=Z.DM0000
;

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%