create or replace view vw_sf_rqdmx10 as
select
    a.BRID00,                                                                         --病人ID
    e.JZDH00,
    a.GJBZBM as GJBM00,
    a.GJBM00 as GJBM01,                                                               --国家编码(排序用)
    a.CZRQ00 as QDH000,                                                               --清单号
    to_char(to_date(a.CZRQ00||a.CZSJ00,'YYYYMMDDHH24:MI:SS'),'YYYY-MM-DD HH24:MI') as FYRQSJ,--费用日期时间
    b.BRXM00 as XM0000,                                                               --姓名
    a.BRID00 as MZID00,                                                               --门诊ID(实为病人ID)
    b.BRBLH0 as MZH000,                                                               --门诊号(实为病人病历号)
    a.MZH000 as GHID00,                                                               --挂号ID(实为门诊号)
    a.XMBH00,                                                                         --医疗收费编码
    trim(a.XMMC00)as XMMC00,                                                          --项目名称
    a.XMGG00,                                                                         --项目规格
    a.XMDW00,                                                                         --单位
    round((decode(a.JMBZ00,'4',a.HJJE00+a.JMJE00,a.HJJE00))/(decode((a.XMSL00),0,1,(a.XMSL00))),2) as XMDJ00,  --单价
    a.XMSL00*e.BZ0000 as XMSL00,                                                      --数量
    (decode(a.JMBZ00,'4',a.HJJE00+a.JMJE00,a.HJJE00))*e.BZ0000 as HJJE00,             --合计金额,如果是事前减免，清单显示总金额要把减免这部分加上去，而后再显示减免的金额
    (decode(a.JMBZ00,'4',a.ZFJE00+a.JMJE00,a.ZFJE00))*e.BZ0000 as ZFJE00,             --自付金额
    a.GFJE00*e.BZ0000 as GFJE00,                                                      --减免金额
    a.JZJE00*e.BZ0000 as JZJE00,                                                      --计账金额
    d.BH0000,                                                                         --发票项目编号
    d.XMMC00 as FPXMMC,                                                               --发票项目名称
    decode(a.SFLB00,'3','Y','N') as SFYP00,                                           --是否药品
    (to_char(SF_SF_HQZFBL(a.MZH000,a.XMBH00,'N',a.ZFBL00))*100||'%') as ZFBL00,       --自付比率
    ' ' as XMZT00,                                                                    --项目状态
    a.KDKS00,                                                                         --开单科室
    (select BMMC00 from BM_BMBM00 where BMBH00=a.KDKS00 and rownum=1 ) as KDKSMC,     --开单科室名称
    a.KDYS00,                                                                         --开单医生
    (select ZWXM00 from BM_YGBM00 where YGBH00=a.KDYS00 and rownum=1 ) as KDYSXM,     --开单医生姓名
    a.CZY000 as KFCZY0,                                                               --扣费操作员
    a.CZYXM0 as KFYXM0,                                                               --扣费操作员姓名
    a.CZYKS0 as KFKS00,                                                               --扣费科室
    (select BMMC00 from BM_BMBM00 where BMBH00=a.CZYKS0 and rownum=1 ) as KFKSMC,     --扣费科室名称
    a.ZXKS00,                                                                         --执行科室
    (select BMMC00 from BM_BMBM00 where BMBH00=a.ZXKS00 and rownum=1 ) as ZXKSMC,     --执行科室名称
    a.DJH000,                                                                         --收费单据号
    substrb(SF_SF_HQYBXJ(a.YBLB00,'',a.BRFB00,a.XMBH00,'N',a.BRID00),1,30) as YBXJ00, --医保限价
    a.MZH000 as GHH000,                                                               --挂号号
    a.YJDJH0 as SQDJH0,                                                               --申请单据号
    decode(a.SFLB00,'3','药品',(select ZLXMJC from YJ_YW0000 where YJDJH0=a.YJDJH0 and rownum=1)) as ZLXMJC  --诊疗项目
from  SF_FYMX00 a,BM_BRXXB0 b,BM_FPXM00 d,SF_JZMXXM e
where a.BRID00 = b.BRID00
  and a.MZFPID = d.FPXMID
  and a.MXID00 = e.MXID00
;