create or replace view vw_sf_ybjsqd_jzb000 as
select
      b.GHID00 as GHID00                                         ,--挂号ID
      A.BRID00 as BRID00                                         ,--病人ID
      trim(B.GHH000) as GHH000                                   ,--挂号号
      A.FBBH00 as FBBH00                                         ,--费别编号
      g.YBZXLB as YBZXLB                                         ,--医保中心类别
      trim(g.YBMC00) as YBZXMC                                   ,--医保中心名称
      F.BRXM00 as BRXM00                                   ,--病人姓名
      trim(F.BRXB00) as BRXB00                                   ,--性别
      trim(F.BRCSRQ) as BRCSRQ                                   ,--出生日期
      trim(F.BRMZ00) as BRMZ00                                   ,--民族
      trim(F.BRZJLX) as BRZJLX                                   ,--证件类型
      trim(F.BRZJBH) as BRZJBH                                   ,--证件编号
      trim(F.AAE140) as AAE140                                   ,--险种类型编码
      trim(F.AAE140_MC) as AAE140_MC                             ,--险种类型名称
      trim(F.PSN_NO) as PSN_NO                                   ,--人员编号
      trim(B.BRBLH0) as BRBLH0                                   ,--门诊病历号
      B.GHKS00 as GHKS00                                         ,--挂号科室编码
      C.BMMC00 as GHKSMC                                         ,--挂号科名称
      B.JZKS00 as JZKS00                                         ,--就诊科室编号
      D.BMMC00 as JZKSMC                                         ,--就诊科室编号室名称
      A.JZDH00 as JZDH00                                         ,--结帐单号
      A.CXJZDH as CXJZDH                                         ,--冲销结账单号
      A.JZRQ00 as JZRQ00                                         ,--结帐日期
      A.JZSJ00 as JZSJ00                                         ,--结帐时间
      decode(trim(A.YBDJH0),'0','',trim(A.YBDJH0)) as YBDJH0     ,--医保单据号
      nvl(decode(trim(A.YBGHH0),'0','',trim(A.YBGHH0)),decode(trim(b.YBGHH0),'0','',trim(b.YBGHH0))) as  YBGHH0,     --医保挂号号
      a.JZY000 as   JZY000                                       ,--结帐操作员编码
      e.ZWXM00 as   JZYXM0                                       ,--结帐操作员姓名
      A.YBJKID as   YBJKID                                       ,--医保接口ID
      A.PJH000 as   PJH000                                       ,--打印的票据流水号
      A.HJJE00 as   HJJE00                                       ,--合计应收金额
      A.TCJJZF as   TCJJZF                                       ,--统筹基金支付
      A.GRZHZF as   GRZHZF                                       ,--个人医疗帐户支付
      A.ZFJE00 as   ZFJE00                                       ,--自付金额
      trim(A.TSBZBH) as   TSBZBH                                 ,--病种编码
      substrb((select  MC0000  from bm_tsbzb0 aa where trim(aa.BH0000)=trim(A.TSBZBH) and rownum=1),1,100) as   TSBZMC                                 ,--病种名称
      B.GHRQ00 as   GHRQ00                                       ,--病人挂号日期
      B.GHSJ00 as   GHSJ00                                       ,--病人挂号时间
      B.JZRQ00 as   BRRQ00                                       ,--病人就诊日期
      B.JZSJ00 as   BRSJ00                                       ,--病人就诊时间
      b.GHY000 as   GHY000                                       ,--挂号员编号
      b.GHYXM0 as   GHYXM0                                       ,--挂号员姓名
      b.JZYS00 as   JZYS00                                       ,--就诊医生代码
      b.JZYSXM as   JZYSXM                                       ,--就诊医生姓名
      substrb(SF_SF_NATION_JSQDZT(a.BRID00,a.JZDH00,a.HJJE00,A.CXJZDH,a.YBJKID,a.JSQDBZ,'0'),1,50) as   JSQDBZ  ,--医保结算单上传标志( 0: 未上传 1:已上传 2:已修改)
      substrb(decode(SF_SF_NATION_JSQDZT(a.BRID00,a.JZDH00,a.HJJE00,A.CXJZDH,a.YBJKID,a.JSQDBZ,'0'),'0','未上传','1','已上传','2','已修改','未知状态'),1,50)   as   JSQDMC ,--清单状态名称
      substrb(SF_SF_NATION_JSQDZT(a.BRID00,a.JZDH00,a.HJJE00,A.CXJZDH,a.YBJKID,a.JSQDBZ,'1'),1,50) as   JSZTBZ  ,--结算状态标志( 0: 已冲销 1:正常)
      substrb(decode(SF_SF_NATION_JSQDZT(a.BRID00,a.JZDH00,a.HJJE00,A.CXJZDH,a.YBJKID,a.JSQDBZ,'1'),'0','冲销','1','正常'),1,50)   as   JSZTMC  ,--结算名称
      decode(trim(F.YBKH00),'0','',trim(F.YBKH00)) as  YBKH00   ,--医保卡号
      decode(trim(F.YBID00),'0','',trim(F.YBID00)) as  YBID00   ,--医保ID
      decode(trim(F.XZQH00),'本地','',trim(F.XZQH00)) as  XZQH00                            ,--行政区号
      nvl(g.YBSDLB,'0')                               as   YBSDLB                           ,--医保属地类别；0本地、1省内、2省外
      decode(nvl(g.YBSDLB,'0'),'2','省外','1','省内','本地') as YBSDMC                       --医保属地名称

 from SF_JZB000 A,SF_BRXXB0 B,BM_BMBM00 C,BM_BMBM00 D,BM_YGBM00 E,BM_BRXXB0 F,IC_YBBRLB G
where A.MZID00 = B.GHID00
  and A.BRID00 = F.BRID00
  and B.FBBH00 = G.FBBH00
  and B.YBLB00 = G.YBLB00
  and B.GHKS00 = C.BMBH00
  and B.JZKS00 = D.BMBH00
  and A.JZY000 = E.YGBH00;