CREATE or replace VIEW SD_HOSPITAL.VW_EMR_INAPTIENT_DISEASE
 (
 PID, IPID, ADMINISSIONNO, BEDNO, PATIENTNAME, SEX, ADMINISSIONTIME, WARDCODE, 
DEPTCODE, MEDICINECENTERTYPE, MEDICINEPATIENTTYPE, PATIENTTYPESHORTNAME, 
BALANCE, LIMIT, FEETYPENAME, FEETYPECODE, DISEASEINFO, HOSPITALCODE, RECORDSNUMBER, 
SSF000, MZF000

 )
 AS 
select A.BRID00,    --病人ID
       A.ZYID00,    --住院ID
       A.ZYH000,    --住院号
       A.RYCWH0,    --床号
       A.XM0000,    --姓名
       A.XB0000,    --性别
       A.RYRQ00,    --入院日期
       A.DQBQ00,    --病区
       A.DQKS00,    --科室
       A.YBZXLB,    --医保中心类别
       A.YBBRLB,    --医保病人类别
       (select BRLBJC from BM_YBBRLB where FBBH00=a.fbbh00 and YBBRLB=a.YBBRLB and YBZXLB=a.YBZXLB) BRLBJC,
       --D.BRLBJC,    --病人类别简称
       '<font color=''blue''>'||'账户余额'||SF_BQ_KYJE00(A.ZYID00,A.BRID00)||'</font>' YKZYE0,  --预扣后的可用额
       --E.YKZYE0-E.TYJE00 YKZYE0,  --预扣后的可用额
       E.BJJE00,    --报警限额
       (select FBMC00 from BM_BRFBB0 where FBBH00=A.FBBH00) FBMC00,

       --F.FBMC00,    --费别名称：自费、记帐、医保等
       A.FBBH00,
       nvl((select '<font color=''red''>病种(限额'||SF_ZY_DBZJE00(a.zyid00,a.dbzbm0,'1')/*(select ybhdje from bm_dbzdeb where dbzbm0=a.dbzbm0 and ybzxlb=a.ybzxlb)*//*decode(YBYJS0,0,BZSFJE,YBYJS0)*/||' 费用'||nvl(BZNZFY,0)||
       ' 手术费'||(select sum(H.HJJE00) from ZY_BRFY00 G,ZY_FYMX00 H where G.ZYID00=A.ZYID00 and G.DJH000=H.DJH000 and H.FPXMID=11)||
       ' 麻醉费'||(select sum(H.HJJE00) from ZY_BRFY00 G,ZY_FYMX00 H where G.ZYID00=A.ZYID00 and G.DJH000=H.DJH000 and H.FPXMID=19)||
       ' 差额'||nvl(BZNCE0,0)||' '||trim(BZMC00)||')</font>' from VW_ZY_DBZCZB where ZYID00=A.ZYID00),' ') diseaseInfo,
       (select nvl(trim(max(YYID00)),'0') from BM_BMBM00 where BMBH00=A.DQKS00) YYID00,
       MRN000
       ,(select sum(H.HJJE00) from ZY_BRFY00 G,ZY_FYMX00 H where G.ZYID00=A.ZYID00 and G.DJH000=H.DJH000 and H.FPXMID=11) SSF000 --手术费
       ,(select sum(H.HJJE00) from ZY_BRFY00 G,ZY_FYMX00 H where G.ZYID00=A.ZYID00 and G.DJH000=H.DJH000 and H.FPXMID=19) MZF000 --麻醉费       
from ZY_BRXXB0 A, ZY_BRZHXX E 
where  E.BRID00 = A.BRID00
/