-- Start of DDL Script for View SD_HOSPITAL.VW_YJ_BRXXB0
-- Generated 2024-05-23 17:29:41 from SD_HOSPITAL@GLGQYY

CREATE OR REPLACE VIEW vw_yj_brxxb0 (
   brblh0,
   mrn000,
   brid00,
   brxm00,
   brxb00,
   brnl00,
   brdh00,
   brjtdz,
   zyzt00,
   sfyd00,
   fbbh00,
   fbmc00,
   fbxx00,
   yblb00,
   ybbrlb,
   mzkyje,
   dwbrid,
   kmh000,
   ewm001,
   bmmc00,
   gms000,
   ghh000 )
AS
select
  a.BRBLH0,                                                                                   --病人病历号
  a.MRN000,                                                                                   --病案号
  a.BRID00,                                                                                   --病人id
  a.BRXM00,                                                                                   --病人姓名
  a.BRXB00,                                                                                   --性别
  substr(SF_XT_CSRQTONL(a.BRCSRQ),1,10) as BRNL00,                                            --年龄
  a.BRDH00,                                                                                   --电话
  a.BRJTDZ,                                                                                   --家庭地址
  a.ZYZT00,                                                                                   --病人在院状态,'0':门诊,'1':住院,'2':其它,'3':出院
  a.SFYD00,                                                                                   --是否属于优待病人(挂号用，费用优待) 0否 1是
  decode(a.ZYZT00,'1',nvl(a.FBBH01,a.FBBH00),'2',nvl(a.FBBH01,a.FBBH00),a.FBBH00) as FBBH00,  --费别编号
  (select FBMC00 from BM_BRFBB0 where FBBH00 = decode(a.ZYZT00,'1',nvl(a.FBBH01,a.FBBH00),'2',nvl(a.FBBH01,a.FBBH00),a.FBBH00)) as FBMC00,  --费别名称
  decode(d.YBJGJC,e.BRLBJC,d.YBJGJC,d.YBJGJC||'('||e.BRLBJC||')') as FBXX00,                  --费别信息(需改为通用函数取值 @@jlg)
  decode(a.ZYZT00,'1',nvl(a.YBLB01,a.YBLB00),'2',nvl(a.YBLB01,a.YBLB00),a.YBLB00) as YBLB00,  --医保类别
  decode(a.ZYZT00,'1',nvl(a.YBBRL1,a.YBBRLB),'2',nvl(a.YBBRL1,a.YBBRLB),a.YBBRLB) as YBBRLB,  --医保病人类别
  SF_YJ_GETBRZHYE(a.BRID00,'0',0) as MZKYJE,                                                  --门诊可用余额
  nvl(a.WDWID0,a.BRID00) as DWBRID,                                                           --对外病人ID
  (select KMH000 from IC_YHXX00 where BRID00=a.BRID00 and ZT0000='1' and rownum=1) as KMH000, --卡面号
  nvl(a.WDWID0,a.BRID00) as EWM001,                                                           --二维码内容
  g.bmmc00,                                                                                   --科室
  (select gms000 from ys_mzblws where ghid00=f.ghid00) GMS000,                                --过敏史
  f.ghh000                                                                                    --挂号号
from BM_BRXXB0 a,IC_YBBRLB d,BM_YBBRLB e,sf_brxxb0 f,bm_bmbm00 g
where d.FBBH00 = decode(a.ZYZT00,'1',nvl(a.FBBH01,a.FBBH00),'2',nvl(a.FBBH01,a.FBBH00),a.FBBH00)
  and d.YBLB00 = decode(a.ZYZT00,'1',nvl(a.YBLB01,a.YBLB00),'2',nvl(a.YBLB01,a.YBLB00),a.YBLB00)
  and e.FBBH00 = decode(a.ZYZT00,'1',nvl(a.FBBH01,a.FBBH00),'2',nvl(a.FBBH01,a.FBBH00),a.FBBH00)
  and e.YBBRLB = decode(a.ZYZT00,'1',nvl(a.YBBRL1,a.YBBRLB),'2',nvl(a.YBBRL1,a.YBBRLB),a.YBBRLB)
  and d.YBZXLB = e.YBZXLB
  and a.brid00=f.brid00 and f.ghks00=g.bmbh00
/


-- End of DDL Script for View SD_HOSPITAL.VW_YJ_BRXXB0