-- Start of DDL Script for View SD_HOSPITAL.VW_YJ_BRJBDJ2
-- Generated 26-二月-2021 16:44:48 from SD_HOSPITAL@FW2

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,
   ghrysj,
   zdsj00,
   sfcrb0,
   yyid00,
   brzjbh )
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,
    decode(SF_SF_GETYYID00(),'223280',nvl((select substrb(b.dzsf00||b.dzdjs0||b.DZXQ00||b.DZXZJD||b.DZC000||b.DZMPH0,1,100) from ys_crbdj0 b where a.GHID00=b.MZID00 and rownum=1)
     ,(select substrb(B.BRSZSF||B.BRSZS0||B.BRSZZ0||B.BRJTDZ,1,100)from bm_brxxb0 b where a.brid00=b.brid00)) ,
    (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(H.ZDMC00,'?') 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,
    decode(SF_SF_GETYYID00(),'223280',nvl((select brzy00 from ys_crbdj0 c where a.GHID00=c.MZID00 and rownum=1),(select BRZY00 from BM_BRXXB0 B where A.BRID00=B.BRID00) ) ,
    (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,
    A.GHSJ00,H.ZDSJ00
    ,nvl((select SFCRB0 from XT_ICD900 where ICD900=H.ICD900 and rownum=1),'0') SFCRB0,
    (select YYID00 from BM_BMBM00 where BMBH00=a.JZKS00) YYID00,
    (select BRZJBH from BM_BRXXB0 b where A.BRID00=B.BRID00) BRZJBH
from sf_brxxb0 a, YS_BRZDXX h
  where a.GHID00=h.GHID00(+) and a.thrq00 is null
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,
    h.zdmc00 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.RYSJ00,H.ZDSJ00,
    nvl((select SFCRB0 from XT_ICD900 where ICD900=H.ZDM000 and rownum=1),'0') SFCRB0,
   (select YYID00 from BM_BMBM00 where BMBH00=a.DQKS00) YYID00,
   (select BRZJBH from BM_BRXXB0 b where A.BRID00=B.BRID00) BRZJBH
from ZY_BRXXB0 A, BQ_BRZDXX H, XT_ZDLB00 Z
  where A.ZYID00=H.ZYID00(+) and H.ZDLB00=Z.DM0000
/


-- End of DDL Script for View SD_HOSPITAL.VW_YJ_BRJBDJ2