create or replace view vw_sf_rqdmx0 as
select
    a.CZRQ00 as QDH000,                                                            --清单号
    k.BRXM00 as XM0000,                                                            --姓名
    a.BRID00 as MZID00,                                                            --门诊ID(实为病人ID)
    k.BRBLH0 as MZH000,                                                            --门诊号
    a.MZH000 as GHID00,                                                            --挂号ID(门诊ID)
    a.XMBH00,                                                                      --医疗收费编码
    trim(a.XMMC00) as XMMC00,                                                      --项目名称
    a.XMGG00,                                                                      --规格
    a.XMDW00,                                                                      --单位
    decode(sign(a.XMDJ00-1),-1,'0','')||to_char(a.XMDJ00) as XMDJ00,               --项目单价
    a.XMSL00,                                                                      --数量
    a.HJJE00,                                                                      --合计金额
    a.ZFJE00,                                                                      --自付金额
    a.GFJE00,                                                                      --公费减免金额
    a.JZJE00,                                                                      --计账金额      
    d.BH0000,                                                                      --发票项目编号
    d.XMMC00 as FPXMMC,                                                            --发票项目名称
    decode(a.SFLB00,'3','Y','N') as SFYP00,                                        --是否药品
    a.ZFBL00,                                                                      --自付比例
    a.CZRQ00,                                                                      --操作日期
    a.CZSJ00,                                                                      --操作时间
    a.CZY000 as KFCZY0,                                                            --扣费操作员
    a.CZYXM0 as KFYXM0,                                                            --扣费操作员姓名
    a.CZYKS0 as KFKS00,                                                            --扣费科室      
    a.KDYS00,                                                                      --开单医生
    (select ZWXM00 from BM_YGBM00 where YGBH00=a.KDYS00 and rownum=1 ) as KSYSXM,  --开单医生姓名
    (select BMMC00 from BM_BMBM00 where BMBH00=a.KDKS00 and rownum=1 ) as KDKSMC,  --开单科室名称 
    (select BMMC00 from BM_BMBM00 where BMBH00=a.CZYKS0 and rownum=1 ) as KFKSMC,  --扣费科室名称     
    (select BMMC00 from BM_BMBM00 where BMBH00=a.ZXKS00 and rownum=1 ) as ZXKSMC,  --执行科室名称
    a.ZXKS00,                                                                      --执行科室
    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,  --诊疗项目
    k.BRBLH0,                                                                      --病人病历号
    a.GJBZBM as GJBM00,                                                            --国家编码
    a.JZDH00,                                                                      --结账单号
    to_char(to_date(a.CZRQ00||a.CZSJ00,'YYYYMMDDHH24:MI:SS'),'YYYY-MM-DD HH24:MI') as FYRQSJ, --费用日期时间
    substrb(SF_SF_HQYBXJ(a.YBLB00,'',a.BRFB00,a.XMBH00,'N',a.BRID00),1,30) as YBXJ00,  --医保限价
    k.BRXB00,                                                                      --病人性别
    substrb(SF_XT_CSRQTONL_EX(k.BRCSRQ,k.BRCSSJ),1,30) as BRNL00,                  --病人年龄       
    (select FBMC00 from BM_BRFBB0 where FBBH00=a.BRFB00 and rownum=1 ) as BRFB00,  --病人费别
    c.JZRQ00,                                                                      --结算日期
    c.JZSJ00,                                                                      --结算时间
    a.BRYE00                                                                       --病人余额 
 from  SF_FYMX00 a,BM_FPXM00 d,SF_JZB000 c,BM_BRXXB0 k  
where a.BRID00 = k.BRID00
  and a.MZFPID = d.FPXMID
  and a.JZDH00 = c.JZDH00(+)
;