-- Start of DDL Script for View SD_HOSPITAL.VW_YJ_WDBRXXDY
-- Generated 2025-05-19 12:20:58 from SD_HOSPITAL@GLGQYY

CREATE OR REPLACE VIEW vw_yj_wdbrxxdy (
   brblh0,
   mrn000,
   brid00,
   brxm00,
   brxb00,
   brnl00,
   brdh00,
   brjtdz,
   zyzt00,
   sfyd00,
   fbbh00,
   fbmc00,
   fbxx00,
   yblb00,
   ybbrlb,
   mzkyje,
   dwbrid,
   kmh000,
   ewm001,
   ghh000,
   ghksmc,
   gms000,
   zyghid,
   mzzybz,
   zdmc00 )
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,                                                           --二维码内容
  f.GHH000,                                                                                   --挂号号
  (select  BMMC00 from BM_BMBM00 where BMBH00=f.GHKS00) as GHKSMC,                            --挂号科室
  nvl((select  GMS000 from YS_MZBLWS where GHID00=f.GHID00),'未发现') as GMS000,                            --过敏史
  f.GHID00 as ZYGHID,                                                                         --住院挂号ID 
  '0' as MZZYBZ,                                                                               --门诊住院标志
  (select ZDMC00 from ys_brzdxx g where f.ghid00=g.ghid00 and g.ZZDBZ0='3') ZDMC00 --西医主要诊断
from BM_BRXXB0 a,IC_YBBRLB d,BM_YBBRLB e, SF_BRXXB0 f
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
union all
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,                                                           --二维码内容
  f.ZYH000 as GHH000,                                                                         --住院号
  '' as GHKSMC,                                                                               --挂号科室
  '' as GMS000,                                                                               --过敏史
  f.ZYID00 as ZYGHID,                                                                         --住院挂号ID 
  '1' as MZZYBZ,                                                                               --门诊住院标志
  (select substrb(SF_ZY_BRRYCYZDXX(f.ZYID00,0,1),1,100) from dual) as ZDMC00       --入院诊断名称
from BM_BRXXB0 a,IC_YBBRLB d,BM_YBBRLB e, ZY_BRXXB0 f
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
/


-- End of DDL Script for View SD_HOSPITAL.VW_YJ_WDBRXXDY