create or replace view vw_zy_bslxxtdzfp_order as
select
  a.BRID00 as  BRID00,                                                            --病人ID 
  a.ZYID00 as  ZYID00,                                                            --病人ID 
  a.JZDH00 as  JZDH00,                                                            --结账单号
  a.PJH000 as  PJH000,                                                            --票据号
  a.JZRQ00 as  JZRQ00,                                                            --结账日期
  a.JZSJ00 as  JZSJ00,                                                            --结账时间
  a.JZY000 as  JZY000,                                                            --结账员编码
  d.ZWXM00 as  JZYXM0,                                                            --结账员姓名
  b.BRXM00     as   gmfmc,                                                        --(购买方)名称
  a.HJJE00     as   hjje,                                                         --合计金额
  0            as   hjse,                                                         --合计税额  
  a.HJJE00     as   jshj,                                                         --价税合计
  e.FBBH00     as   ybjgbm,                                                       --医保机构编码
  decode(e.FBBH00,3,decode(a.AAE140,'310','01','390','02','04') ,'05') as yblxbm, --医保类型编码
  b.YBID00 as  hzybbh,                                                            --患者医保编号
  a.INSUPLC_ADMDVS as ybxzqhm,                                                    --参保地医保区划
  (select substrb(BMMC00,1,30) from BM_BMBM00 where BMBH00=c.RZKS00)  as ryksmc,  --入院科室名称 
  (select substrb(BMMC00,1,30) from BM_BMBM00 where BMBH00=c.DQBQ00)  as cyksmc,  --出院科室名称
  a.ZYID00 as hzjzbh,                                                             --患者就诊编号
  a.ZYID00 as zyjzbh,                                                             --住院就诊编号
  a.BRID00 as hzwyid,                                                             --患者唯一 ID
  b.BRXB00 as xb,                                                                 --性别
  substr(SF_XT_CSRQTONL(b.BRCSRQ),1,10) as nl,                                    --年龄
  substrb(c.RYRQ00,1,4)||'-'||substrb(c.RYRQ00,5,2)||'-'||substrb(c.RYRQ00,7,2) as  zyrq,     --住院日期
  --case when C.CYRQ00='20991231' or C.CYRQ00 is null then to_char(sysdate,'YYYY-MM-DD') else substrb(c.CYRQ00,1,4)||'-'||substrb(c.CYRQ00,5,2)||'-'||substrb(c.CYRQ00,7,2) end cyrq, --出院日期  change by czc 20240731 调整视图出院日期针对中途结算病人
  decode(nvl(C.CYRQ00,'20991231'),'20991231',to_char(sysdate,'YYYY-MM-DD'),substrb(c.CYRQ00,1,4)||'-'||substrb(c.CYRQ00,5,2)||'-'||substrb(c.CYRQ00,7,2)) as  cyrq
  a.MDTRTAREA_ADMVS as jydybqh,                                                   --就医地医保区划
  nvl(a.GRZHZF,0) as  grzhzf,                                                     --个人账户支付
  nvl(a.TCJJZF,0) as  ybtcjjzf,                                                   --医保统筹基金支付
  nvl(a.YBYL67,0) as  qtybzf,                                                     --其它医保支付
  nvl(a.YBYL63,0) as  zfje,                                                       --自费金额
  nvl(a.YBYL31,0) as  grzf1,                                                      --个人自负
  nvl(a.ZFJE00,0) as  grzf2,                                                      --个人自付
  nvl(a.ZFJE00,0) as  grxjzf,                                                     --个人现金支付
  nvl(a.XJFKJE,0) as  yjje,                                                       --预缴金额
  nvl((select aa.ZYE000 from ZY_BRZHXX aa where aa.ZYID00=c.ZYID00),0) as  grzhye,--个人账户余额
  nvl(a.TCJJZF,0)+nvl(a.GRZHZF,0) as bxzje,                                       --报销总金额
  a.JZDH00 as jsh,                                                                --结算号
  substrb(a.JZRQ00,1,4)||'-'||substrb(a.JZRQ00,5,2)||'-'||substrb(a.JZRQ00,7,2)||' '||substrb(a.JZSJ00,1,2)||':'||substrb(a.JZSJ00,4,2)||':'||substrb(a.JZSJ00,7,2) as ywrq, --结算时间    
  nvl(b.BRLXDH,b.BRDH00) as sjhm,                                                 --手机号码 
  SF_ZY_GETDZFPREMARK(a.ZYID00,a.JZDH00) as bz                                    --备注
  from ZY_JZB000 a,BM_BRXXB0 b,ZY_BRXXB0 c,BM_YGBM00 d,IC_YBBRLB E
  where a.BRID00 = b.BRID00 
  and a.ZYID00 = c.ZYID00
  and a.JZY000 =d.YGBH00
  and b.YBLB00 =e.YBLB00
  and b.FBBH00 =e.FBBH00
;