create or replace view vw_sf_rqdmx6
(mxid00, jzdh00, gjbm00, qdh000, fyrqsj, xm0000, mzid00, mzh000, ghid00, xmbh00, xmmc00, xmgg00, xmdw00, xmdj00, xmsl00, hjje00, zfje00, gfje00, jzje00, bh0000, fpxmmc, sfyp00, zfbl00, kdks00, kdksmc, kfczy0, kfyxm0, kfks00, kfksmc, zxks00, zxksmc, djh000, ywfpmc, pxzdmc, hjje01)
as
select
      a.MXID00,        --明细ID
      a.JZDH00,        --结账单号
      a.GJBM00  ,      --国家编码(排序用)
      a.CZRQ00  ,      --清单号
      to_char(to_date(a.CZRQ00||a.CZSJ00,'YYYYMMDDHH24:MI:SS'),'YYYY-MM-DD HH24:MI') FYRQSJ,
      b.BRXM00  ,      --姓名
      a.BRID00  ,      --门诊ID(实为病人ID)
      b.BRBLH0  ,      --门诊号
      a.MZH000  ,      --挂号ID(门诊ID)
      a.XMBH00  ,      --医疗收费编码
      trim(a.XMMC00)||decode(a.JMBZ00,'4','(减免)','')||'  '||decode(a.SFLB00,'3',(select EYPMC0 from BM_YD0000 where YPNM00=a.XMBH00 and rownum=1), (select YWMC00 from BM_YYSFXM where SFXMID=a.XMBH00 and rownum=1)),  --项目名称
      trim(a.XMGG00)  ,      --项目规格
      trim(a.XMDW00)  ,      --单位
      round((decode(a.JMBZ00,'4',a.HJJE00+a.JMJE00,a.HJJE00))/(decode((a.XMSL00),0,1,(a.XMSL00))),4) ,       --单价
      (a.XMSL00) ,  --数量
      (decode(a.JMBZ00,'4',a.HJJE00+a.JMJE00,a.HJJE00)) ,  --合计金额,如果是事前减免，清单显示总金额要把减免这部分加上去，而后再显示减免的金额
      (decode(a.JMBZ00,'4',a.ZFJE00+a.JMJE00,a.ZFJE00)) ,  --自付金额
      (a.GFJE00) ,  --减免金额
      (a.JZJE00) ,  --计账金额
      d.BH0000  ,      --发票项目编号
      d.XMMC00  ,      --发票项目名称
      decode(a.SFLB00,'3','Y','N'),             --是否药品
      (to_char(SF_SF_HQZFBL(a.MZH000,a.XMBH00,decode(a.SFLB00,'3','Y','N'),a.ZFBL00))*100||'%') ZFBL00,      --自付比率
      a.KDKS00  KDKS00,                --开单科室
      (select BMMC00 from BM_BMBM00 where BMBH00=a.KDKS00 and rownum=1 )   KDKSMC,  --开单科室名称
      a.CZY000  KFCZY0,                --扣费操作员
      a.CZYXM0  KFYXM0,                --扣费操作员姓名
      a.CZYKS0  KFKS00,                --扣费科室
      (select BMMC00 from BM_BMBM00 where BMBH00=a.CZYKS0 and rownum=1 )   KFKSMC,  --扣费科室名称
      a.ZXKS00  ZXKS00,                --执行科室
      (select BMMC00 from BM_BMBM00 where BMBH00=a.ZXKS00 and rownum=1 )   ZXKSMC,   --执行科室名称
      a.DJH000,
      d.YWXMMC,
      a.XMBH00||a.XMMC00,
      a.HJJE00 as HJJE01
from  SF_FYMX00 A,BM_BRXXB0 b,BM_FPXM00 d
where a.BRID00 = b.BRID00
  and a.MZFPID = d.FPXMID;