create or replace view vw_dc_mbsj as
select
    A.ZYID00 as ZY_ID,--住院ID
    A.BRID00 as PAT_ID, --病人ID
    A.ZYH000 as ZY_NO, --住院号
    A.BRRYCS as ZY_TIMES, --住院次数
    A.MRN000 as MRN_NO, --病案号
    A.XM0000 as PAT_NAME, --患者姓名
    A.XB0000 as PAT_SEX, --患者性别
    TO_DATE(B.BRCSRQ,'YYYYMMDD') as PAT_BIRTH, --患者出生日期
    GREATEST(TRUNC(MONTHS_BETWEEN(TO_DATE(nvl(A.SJRYRQ,A.RYRQ00),'YYYYMMDD'),TO_DATE(B.BRCSRQ,'YYYYMMDD')) / 12),1) as PAT_AGE_YEAR,--年龄岁  不足1岁算1 用于筛选记录
    SF_XT_CSRQTONL_BZ(B.BRCSRQ, '00:00:00', A.SJRYRQ,'',0) as PAT_AGE, --患者年龄
    A.RYZD00 as ADM_DIAG_CODE, --入院诊断编码
    A.RYZDMC as ADM_DIAG_NAME, --入院诊断
    TO_DATE(A.RYRQ00 || trim(A.RYSJ00), 'YYYY-MM-DD HH24:MI:SS') as ADM_REG_DTIME, --入院登记时间
    A.RYLB00 as ADM_WAY, --入院方式
    '' as ADM_HOSP, --入院院区
    A.RYSQK0 as ADM_ILLNESS, --入院病情
    A.RZKS00 as ADM_DEPT_CODE, --入院科室编码
    (select V.BMMC00 from BM_BMBM00 V where V.BMBH00(+) = A.RZKS00) as ADM_DEPT_NAME, --入院科室名称
    A.RYBQ00 as ADM_WARD_CODE, --入院病区编码
    (select V.BMMC00 from BM_BMBM00 V  where V.BMBH00(+) = A.RYBQ00) as ADM_WARD_NAME, --入院病区名称
    A.SJRYRQ as IN_DTIME_RQ, --入院日期索引
    TO_CHAR(GREATEST(decode(nvl(A.SJCYRQ,'20991231'),'20991231',TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'),'YYYYMMDD'),TO_DATE(A.SJCYRQ,'YYYYMMDD'))-TO_DATE(A.SJRYRQ,'YYYYMMDD'),1))||'天' as IN_DAYS, --住院天数
    '' as CURR_HOSP, --当前所在院区
    A.DQKS00 as CURR_DEPT_CODE, --当前所在科室编码
    (select V.BMMC00  from BM_BMBM00 V where V.BMBH00(+) = A.DQKS00) as CURR_DEPT_NAME, --当前所在科室名称
    A.DQBQ00 as CURR_WARD_CODE, --当前所在病区编码
    (select V.BMMC00 from BM_BMBM00 V  where V.BMBH00(+) = A.DQBQ00) as CURR_WARD_NAME, --当前所在病区名称
    A.RYCWH0 as BED_NO, --床号
    A.RYSQK0 as ILLNESS, --病情
    decode(A.SXYS00,741,2,948,2,A.SXYS00) as DOCT_CODE, --主管医生编码
    decode(A.SXYSXM,'李俊','易联众','郑焕兵','易联众',A.SXYSXM) as DOCT_NAME, --主管医生
    A.ZGHS00 as NURSE_CODE, --主管护士编码
    (select HS.ZWXM00 from BM_YGBM00 HS where A.ZGHS00 = HS.YGBH00(+)) as NURSE_NAME, --主管护士
    to_number('') as NURSE_GROUP_CODE, --护理组编码缺失A.ZRHLZ0
    '' as NURSE_GROUP_NAME, --护理组
    A.ZZYS00 as ZZ_DOCT_CODE, --主治医生编码
    A.ZZYSXM as ZZ_DOCT_NAME, --主治医生
    A.ZRYS00 as ZR_DOCT_CODE, --主任医生编码
    A.ZRYSXM as ZR_DOCT_NAME, --主任医生
    B.PYSM00 as CODE_PY, --拼音码缺失A.PYSM00
    B.WBSM00 as CODE_WB, --五笔码缺失A.WBSM00
    B.BRXX00 as BLOOD_TYPE,--血型
    B.RH0000 as BLOOD_RH, -- RH型
    B.JG0000 as NATIVE_STR, -- 籍贯
    G.yyid00 YYID00,
    G.yyjc00 YYMC00，
    (select ZDMC00 from VW_ZS_WGDBL0 where zyid00 =A.Zyid00) LCZD00, --临床诊断
    to_char(sysdate,'yyyy-MM-dd') KDRQ00,
    I.Yzmxid,
    (select IMG000 from ZS_YSTPQM ZS where ZS.YGBH00=I.KZYS00 and rownum=1) CAIMG0,   --ca图片签
    (select zm.zs0000 from zs_blwsmx zm where zm.zyid00=a.zyid00 and rownum=1) zs0000,
    (select zm.tz0000 from zs_blwsmx zm where zm.zyid00=a.zyid00 and rownum=1) tz0000,
    (select LBMC00 from  BM_ZLXMLB L where I.YZLB00=L.LBBH00 and rownum=1) XMLBMC,  --项目类别
    I.ZLXMJC XMMC00,--项目名称
    (select sum(jjje00*I.mcsl00*b.sfcs00) from bq_yjyzjj b where b.yzmxid = I.yzmxid) XMZJ00, --项目金额
    (select ZWXM00 from  BM_YGBM00 Y where I.KZYS00=Y.YGBH00 and rownum=1) KDYS00,    --开单医生
    (select BMMC00 from  BM_BMBM00 Z where I.ZXKS00=Z.BMBH00 and rownum=1) ZXKSMC，    --执行科室名称
    (select yybmdz from  BM_BMBM00 Z where I.ZXKS00=Z.BMBH00 and rownum=1) KSDZ00     --科室地址
from ZY_BRXXB0 A
inner join BM_BRXXB0 B on A.BRID00 = B.BRID00
left join  IC_YBBRLB C on A.YBLB00=C.YBLB00 and A.FBBH00=C.FBBH00
left join BM_TYZD00 E on B.BRHYZK=E.BH0000 and  E.ZDMC00 = '婚姻状况'
left join BM_BMBM00 F on A.DQBQ00=F.BMBH00
left join XT_YYXX00 G on F.YYID00=G.YYID00
left join BQ_YJYZ00 I on a.zyid00=I.Zyid00