prompt VW_YJ_BRGHXX  病人门诊挂号信息视图
create or replace view VW_YJ_BRGHXX
as       
-- MODIFICATION HISTORY                                     
-- Person   Date           Comments                         
-- jlg      2021.05.12     created 病人门诊挂号信息视图（医技用），脱胎自VW_SF_BRGHXX  YJ9-20210508-003
-- jlg      2022.03.20     增加SFYX00字段值为2的含义（-1过期 0已退号 1有效 2失效 ）	   YJ9EX-20220320-004
-- chenrenhai 2023.06.10   增加通过获取ICKH 优先医保卡号，其次院内就诊卡，最后电子健康卡、虚拟卡 	YJ13H-20230610-001
select  
  0 as JLLBBZ,         --记录类别标志 0所有 1具体挂号记录
  '0' as MZZYBZ,       --门诊住院标志
  0 as GHID00,         --挂号ID
  '' as GHH000,        --挂号号
  '所有' as GHRQ00,    --挂号日期
  ' ' as GHRQGG,       --挂号日期（规格）
  0 as GHYXTS,         --挂号有效天数
  '所有' as JZYSXM,    --就诊医生
  ' ' as GHKSMC,       --挂号科室名称
  1 as SFYX00,         --挂号有效标志
  0 as GHQXBZ,         --挂号权限标志
  0 as GHKS00,         --挂号科室
  0 as JZYS00,         --就诊医生id
  '' as TSBZMC,        --特殊病种名称
  '' as XM0000,        --病人姓名
  '' as ZDXX00,        --诊断信息
  0 as BRID00,         --病人id
  '' as THRQ00         --退号日期
from dual 
union all 
select 
  1 as JLLBBZ,         --记录类别标志 0所有 1具体挂号记录
  '0' as MZZYBZ,
  a.GHID00,
  a.GHH000,
  a.GHRQ00,
  decode(a.GHRQ00, null, '',substr(a.GHRQ00,3,2)||'-'||substr(a.GHRQ00,5,2)||'-'||substr(a.GHRQ00,7,2)) GHRQGG, --挂号日期规格化
  a.GHYXTS,
  a.JZYSXM,
  (select BMMC00 from BM_BMBM00 where BMBH00 = a.GHKS00) as GHKSMC, --挂号科室名称      
  --decode(a.THRQ00,null,sign(to_date(a.GHRQ00,'yyyymmdd')+a.GHYXTS+0.25-sysdate),0) as SFYX00,  --当前挂号是否有效 0已退号 1有效 2过期 
  decode(a.THRQ00,null,decode(a.GHYXTS,-1,2,sign(to_date(a.GHRQ00,'yyyymmdd')+a.GHYXTS+0.25-sysdate)),0) as SFYX00,  --当前挂号是否有效 -1过期 0已退号 1有效 2失效   
  e.GHQXBZ,
  a.GHKS00,
  a.JZYS00,
  (select MC0000 from BM_TSBZB0  where YBZXLB = a.YBZXLB and BH0000=a.TSBZBH and rownum=1) as TSBZMC,  --特殊病种名称
  a.XM0000,
  substrb(SF_YF_GETMZBRZDXX(a.GHID00,'3'),1,200) as ZDXX00,
  a.BRID00,
  a.THRQ00
from SF_BRXXB0 a,BM_GHLBB0 e, IC_YHXX00 f
where a.BRID00 = f.BRID00 
  and f.ICKH00=SF_MZ_GETICKXX(a.BRID00)
  and a.GHLB00 = e.LBBH00
  and a.GHLB00 <> -99999   
  and f.ZT0000 = '1'   --该卡是有效的  
/

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%