prompt 门诊病人结账信息国家医保结算单
create or replace view VW_SF_BRJZXX_GJYBJSD
(  
  GHID00,
  GHH000,
  BRID00,
  XM0000,
  XB0000,
  CSRQ00,
  YBGHH0,
  GHRQ00,
  GHSJ00,
  JZDH00,
  YBDJH0,
  TSBZBH,
  JZRQ00,
  JZSJ00,
  TSBZMC,
  FBBH00,
  YBLB00,
  YBZXMC,
  QDSCBZ,
  YBZXLB

)
as
-- Person      Date        Comments
-- qiulf      2021.08.24   created  for MZSF-20210824-001
-- zhangyc    2021.09.28   优化语句 by MZSF-20210928-004
-- zhangyc    2021.10.11   修改医保挂号获取值a.YBGHH0-->nvl(a.YBGHHT,a.YBGHH0) by   MZSF-20211009-001
-- qiulf      2021.12.13   增加YBLB00，YBZXMC，QDSCBZ，YBZXLB by 	MZSF-20211213-002
select a.GHID00,  --挂号ID
       a.GHH000,  --挂号号
       a.BRID00,  --病人ID
       a.XM0000,  --姓名
       a.XB0000,  --性别
       a.CSRQ00,  --出生日期
       a.YBGHH0,  --医保挂号号
       a.GHRQ00,  --挂号日期
       a.GHSJ00,  --挂号时间
       b.JZDH00,  --结账单号
       b.YBDJH0,  --医保单据号
       b.TSBZBH,  --特殊病种编号
       b.JZRQ00,  --结算日期
       b.JZSJ00,  --结算时间
       c.MC0000,  --特殊病种名称
       a.FBBH00,   --费别
       a.YBLB00,  --医保类别
       d.YBMC00, --医保中心名称
       (select count(1) from SF_YBJKRZ x where   x.JKCJBM='YBJK_JSQDXXSC_001' and x.GHID00=a.GHID00 and x.flag='1') QDSCBZ, --清单上传标志
       a.YBZXLB  --医保中心类别

from SF_BRXXB0 a,SF_JZB000 b,BM_TSBZB0 c,IC_YBBRLB d
where 
a.GHID00=b.MZID00 
and a.BRID00=B.BRID00
and a.YBLB00=d.YBLB00
and a.YBZXLB=d.YBZXLB
and B.TSBZBH=c.BH0000
and c.TSBZFL='0' and B.YBZXLB=c.YBZXLB

and b.JZDH00 not in (select x.CXJZDH from SF_JZB000 x where x.MZID00=a.GHID00 and X.BRID00=B.BRID00 and x.CXJZDH>0)
/
---%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%