update bm_fpxm00 b set b.mzfpbm='9000107',b.zyfpbm='9000209' where b.fpxmid=1 and b.xmmc00='西药费';

update bm_fpxm00 b set b.mzfpbm='9000109',b.zyfpbm='9000211' where b.fpxmid=2 and b.xmmc00='成药费';

update bm_fpxm00 b set b.mzfpbm='9000109',b.zyfpbm='9000211' where b.fpxmid=3 and b.xmmc00='草药费';

update bm_fpxm00 b set b.mzfpbm='9000111' where b.fpxmid=4 and b.xmmc00='挂号费';

update bm_fpxm00 b set b.zyfpbm='9000201' where b.fpxmid=5 and b.xmmc00='床位费';

update bm_fpxm00 b set b.mzfpbm='9000101',b.zyfpbm='9000202' where b.fpxmid=6 and b.xmmc00='诊察费';

update bm_fpxm00 b set b.zyfpbm='9000207' where b.fpxmid=7 and b.xmmc00='护理费';

update bm_fpxm00 b set b.mzfpbm='9000102',b.zyfpbm='9000203' where b.fpxmid=8 and b.xmmc00='检查费';

update bm_fpxm00 b set b.mzfpbm='9000104',b.zyfpbm='9000204' where b.fpxmid=9 and b.xmmc00='化验费';

update bm_fpxm00 b set b.mzfpbm='9000103',b.zyfpbm='9000205' where b.fpxmid=10 and b.xmmc00='治疗费';

update bm_fpxm00 b set b.mzfpbm='9000105',b.zyfpbm='9000206' where b.fpxmid=11 and b.xmmc00='手术费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=12 and b.xmmc00='救护车费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=13 and b.xmmc00='其他费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=14 and b.xmmc00='放射费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=15 and b.xmmc00='CT费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=16 and b.xmmc00='超声费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=17 and b.xmmc00='输氧费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=18 and b.xmmc00='输血费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=19 and b.xmmc00='麻醉费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=20 and b.xmmc00='麻醉相关';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=21 and b.xmmc00='其它医疗';

update bm_fpxm00 b set b.mzfpbm='9000106',b.zyfpbm='9000208' where b.fpxmid=22 and b.xmmc00='材料费';

update bm_fpxm00 b set b.mzfpbm='9000110',b.zyfpbm='9000212' where b.fpxmid=23 and b.xmmc00='诊疗费';

update bm_fpxm00 b set b.mzfpbm='9000106',b.zyfpbm='9000208' where b.fpxmid=24 and b.xmmc00='特殊材料费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=25 and b.xmmc00='体检费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=26 and b.xmmc00='心脑电图';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=27 and b.xmmc00='肿瘤放射治疗费';


update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=28 and b.xmmc00='婴儿费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=29 and b.xmmc00='伙食费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=30 and b.xmmc00='ICU';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=31 and b.xmmc00='陪伴费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=32 and b.xmmc00='血透费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=33 and b.xmmc00='抢救费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=34 and b.xmmc00='接生费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=35 and b.xmmc00='加床费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=36 and b.xmmc00='注射费';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=37 and b.xmmc00='自费药品材料';

update bm_fpxm00 b set b.mzfpbm='9000112',b.zyfpbm='9000213' where b.fpxmid=38 and b.xmmc00='药事服务';
commit;



create or replace view vw_sf_chargedetail as
select 
  aa.JZDH00 as JZDH00,                                                                                    --HIS结账单号
  aa.JZDH00 as ywlsh,                                                                                     --ywlsh         业务流水号    string  是
  bb.FPXMID as FPXMID,                                                                                    --发票项目ID
  bb.BH0000 as  FPXMBH,                                                                                   --发票项目编码
  --1 as PXXH00,                                                                                          --sortNo      序号    Integer 不限    是  默认从1开始，每个收费项目序号值递增1，本次不允许重复
  max(decode(SF_SF_TYZD00('第三方电子发票平台接口','chargeCode'),'1',bb.MZFPBM,bb.MZFPBM)) as chargeCode, --chargeCode  收费项目代码  String  50    是 填写业务系统内部编码值，由医疗平台配置对照
  aa.XMMC00 as chargeName,                                                                                --chargeName  收费项目名称  String  100   是 填写业务系统内部项目名称
  '无' as unit,                                                                                           --unit        计量单位      String  20    否
  sum(aa.XMJE00) as std,                                                                                  --std         收费标准      Number  14,2  是
  1 as num,                                                                                               --number      数量          Number  14,2  是
  sum(aa.XMJE00) as amt,                                                                                  --amt         金额          Number  14,2  是
  sum(aa.XMJE00) as selfAmt,                                                                              --selfAmt     自费金额      Number  14,2  是 如无金额，填写0
  '' as remark                                                                                            --remark      备注    String  200 否
from SF_JZMX00 aa,BM_FPXM00 bb 
where aa.XMBH00=bb.FPXMID
group by aa.JZDH00,bb.FPXMID,bb.BH0000,aa.XMMC00
;


create or replace view vw_sf_rqdmx8
(jzdh00, gjbm00, gjbm01, qdh000, fyrqsj, xm0000, mzid00, mzh000, ghid00, xmbh00, xmmc00, xmgg00, xmdw00, xmdj00, xmsl00, hjje00, zfje00, gfje00, jzje00, bh0000, fpxmmc, sfyp00, zfbl00, xmzt00, kdks00, kdksmc, kfczy0, kfyxm0, kfks00, kfksmc, zxks00, zxksmc, djh000, ybxj00, fpxmid, fpxmbh, mzfpbm, ybxmbh, gjbzbm, gjbzmc)
as
select
    a.JZDH00,
    (select GJBM00 from BM_YYSFXM where SFXMID=a.XMBH00) GJBM00,
    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),                                                                 --项目名称
    trim(substrb(decode(a.SFLB00,'2',c.GG0000,c.BZ0000),1,50)) XMGG00,              --项目规格
    trim(c.DW0000)  ,                                                               --单位
    round((decode(a.JMBZ00,'4',a.HJJE00+a.JMJE00,a.HJJE00))/(decode((a.XMSL00),0,1,(a.XMSL00))),2)    ,      --单价
    (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   ,                                                                    --发票项目名称
    'N',                                                                            --是否药品
    (to_char(SF_SF_HQZFBL(a.MZH000,a.XMBH00,'N',a.ZFBL00))*100||'%') ZFBL00,        --自付比率
    decode((select e.XMZT00 from YJ_YW0000 e where e.YJDJH0=a.YJDJH0 AND e.SFDJH0=a.DJH000 and e.MZZYBZ='1' and rownum=1),'2','N','3','Y','Y') XMZT00,
    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,                                                                       --
    substrb(SF_SF_HQYBXJ(a.YBLB00,'',a.BRFB00,a.XMBH00,'N',a.BRID00),1,30) YBXJ00,  --医保限价
    d.FPXMID as FPXMID,                                                             --发票ID
    d.BH0000 as FPXMBH,                                                             --发票编码
    d.MZFPBM as MZFPBM,                                                             --电子发票接口-门诊发票编码
    substr(SF_XT_GETYBXMBH(a.BRFB00,a.YBLB00,a.XMBH00,a.SFLB00),1,50) as YBXMBH,    --医保项目编号
    nvl(a.GJBZBM,SF_XT_GETGJBZBMMC(a.XMBH00,'N','1')) GJBZBM,                       --国家医保编码
    nvl(a.GJBZMC,SF_XT_GETGJBZBMMC(a.XMBH00,'N','2')) GJBZMC                        --国家医保名称
from  SF_FYMX00 a,BM_BRXXB0 b,BM_YYSFXM c,BM_FPXM00 d
where a.BRID00 = b.BRID00
  and a.XMBH00 = c.SFXMID
  and a.MZFPID = d.FPXMID
  and a.JMBZ00 <> '4'
  and a.SFLB00 not in ('3')
union all
select
    a.JZDH00  ,
    a.GJBM00  ,
    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)||'(减免)',                                                       --项目名称
    trim(substrb(decode(a.SFLB00,'2',c.GG0000,c.BZ0000),1,50)) XMGG00,              --
    trim(c.DW0000)  ,                                                               --单位
    (a.JMJE00),                                                                     --A.XMDJ00    ,      --单价
    -1,                                                                             --SUM(A.XMSL00) ,  --数量
    (-a.JMJE00),                                                                    --SUM(A.HJJE00) ,  --合计金额
    (-a.JMJE00),                                                                    --SUM(A.ZFJE00) ,  --自付金额
    0,                                                                              --SUM(A.GFJE00) ,  --减免金额
    0,                                                                              --SUM(A.JZJE00) ,  --计账金额
    d.BH0000  ,                                                                     --发票项目编号
    d.XMMC00  ,                                                                     --发票项目名称
    'N',                                                                            --是否药品
    (to_char(SF_SF_HQZFBL(a.MZH000,a.XMBH00,'N',a.ZFBL00))*100||'%') ZFBL00 ,       --自付比率
    'Y'       ,                                                                     --
    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,                                                                       --  
    substrb(SF_SF_HQYBXJ(a.YBLB00,'',a.BRFB00,a.XMBH00,'N',a.BRID00),1,30) YBXJ00,  --医保限价
    d.FPXMID as FPXMID,                                                             --发票ID
    d.BH0000 as FPXMBH,                                                             --发票编码
    d.MZFPBM as MZFPBM,                                                             --电子发票接口-门诊发票编码
    substr(SF_XT_GETYBXMBH(a.BRFB00,a.YBLB00,a.XMBH00,a.SFLB00),1,50) as YBXMBH,    --医保项目编号
    nvl(a.GJBZBM,SF_XT_GETGJBZBMMC(a.XMBH00,'N','1')) GJBZBM,                       --国家医保编码
    nvl(a.GJBZMC,SF_XT_GETGJBZBMMC(a.XMBH00,'N','2')) GJBZMC                        --国家医保名称
from  SF_FYMX00 a,BM_BRXXB0 b,BM_YYSFXM c,BM_FPXM00 d
where a.BRID00 = b.BRID00
  and a.XMBH00 = c.SFXMID
  and a.MZFPID = d.FPXMID
  and a.JMBZ00 = '4'
  and a.SFLB00 not in ('3')
union all
select
    a.JZDH00,
    nvl(a.GJBM00,c.YPBM00)  ,
    c.YPBM00  ,                                                                     --国家编码(排序用)
    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  ,                                                                     --医疗收费编码
    a.XMMC00  ,                                                                     --项目名称
    a.XMGG00  ,                                                                     --项目规格
    a.XMDW00  ,                                                                     --单位
    round( (a.HJJE00)/(decode((a.XMSL00),0,1,(a.XMSL00))),2)  ,                     --单价
    a.XMSL00  ,                                                                     --数量
    a.HJJE00  ,                                                                     --合计金额,如果是事前减免，清单显示总金额要把减免这部分加上去，而后再显示减免的金额
    a.ZFJE00  ,                                                                     --自付金额
    a.GFJE00  ,                                                                     --减免金额
    a.JZJE00  ,                                                                     --计账金额
    d.BH0000  ,                                                                     --发票项目编号
    d.XMMC00  ,                                                                     --发票项目名称
    'Y',                                                                            --是否药品
    (to_char(SF_SF_HQZFBL(a.MZH000,a.XMBH00,'Y',a.ZFBL00))*100||'%') ZFBL00,        --自付比率
    decode((select CFZT00 from YF_MZCF00 where CFLSH0=a.YJDJH0 and DJH000=a.DJH000 and rownum=1),'2','N','3','Y','Y') XMZT00,
    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,                                                                       --
    substrb(SF_SF_HQYBXJ(a.YBLB00,'',a.BRFB00,a.XMBH00,'Y',a.BRID00),1,30) YBXJ00,  --医保限价
    d.FPXMID as FPXMID,                                                             --发票ID
    d.BH0000 as FPXMBH,                                                             --发票编码
    d.MZFPBM as MZFPBM,                                                             --电子发票接口-门诊发票编码
    substr(SF_XT_GETYBXMBH(a.BRFB00,a.YBLB00,a.XMBH00,a.SFLB00),1,50) as YBXMBH,    --医保项目编号
    nvl(a.GJBZBM,SF_XT_GETGJBZBMMC(a.XMBH00,'Y','1')) GJBZBM,                       --国家医保编码
    nvl(a.GJBZMC,SF_XT_GETGJBZBMMC(a.XMBH00,'Y','2')) GJBZMC                        --国家医保名称
from  SF_FYMX00 a,BM_BRXXB0 b,BM_YD0000 c,BM_FPXM00 d
where a.BRID00 = b.BRID00
  and a.XMBH00 = c.YPNM00
  and a.MZFPID = d.FPXMID
  and a.SFLB00 = '3'
;


create or replace view vw_sf_listdetail as
select
  a.MZID00 as BRID00,                                                                                   --brid00
  a.JZDH00 as JZDH00,                                                                                   --HIS结账单号
  a.JZDH00 as ywlsh,                                                                                    --ywlsh             业务流水号  string  是  
  a.XMBH00 as XMBH00,                                                                                   --                  项目编码
  substrb(a.XMMC00,1,50) as name,                                                                       --name              药品名称    String  50  是  如药品名称，器材名称等
  a.XMDJ00 as std,                                                                                      --std                 单价  Number  14,6    是  
  sum(a.XMSL00) as num,                                                                                 --number              数量  Number  14,6    是  
  sum(a.HJJE00) as amt,                                                                                 --amt               金额    Number  14,6    是  
  sum(a.HJJE00) as selfAmt,                                                                             --selfAmt             自费金额  Number  14,6    是  如无金额，填写0
  max(a.QDH000) as listDetailNo,                                                                        --listDetailNo      明细流水号  String  60  否  明细流水号
  max(decode(SF_SF_TYZD00('第三方电子发票平台接口','chargeCode'),'1',a.MZFPBM,a.MZFPBM)) as chargeCode, --chargeCode          收费项目代码  String  50  否  填写业务系统内部编码值，由医疗平台配置对照,如：床位费、检查费
  max(a.FPXMMC) as chargeName,                                                                          --chargeName          收费项目名称  String  100 否  
  max(a.DJH000) as prescribeCode,                                                                       --prescribeCode     处方编码    String  60  否  
  '' as listTypeCode,                                                                                   --listTypeCode      药品类别编码    String  50  否  如药品分类编码01，有则填写
  '' as listTypeName,                                                                                   --listTypeName      药品类别名称    String  50  否  如药品分类名称，抗生素类抗感染药物
  substrb(max(nvl(a.YBXMBH,nvl(trim(a.GJBM00),a.XMBH00))),1,50) as code,                                --code              编码    String  50  否  如药品编码，有则填写
  '' as form,                                                                                           --form              剂型    String  50  否  
  substrb(max(a.XMGG00),1,50) as specification,                                                         --specification     规格    String  50  否  
  substrb(max(a.XMDW00),1,20) as unit,                                                                  --unit              计量单位    String  20  否  
  '' as receivableAmt,                                                                                  --receivableAmt     应收费用    Number  14,6    否  
  '' as medicalCareType,                                                                                --medicalCareType     医保药品分类  String  1   否 1：无自负/甲2：有自负/乙3：全自负/丙 
  '' as medCareItemType,                                                                                --medCareItemType     医保项目类型  String  100 否  
  '' as medReimburseRate,                                                                               --medReimburseRate  医保报销比例    Number  3,2 否  
  '' as remark,                                                                                         --remark              备注  String  200 否  
  1 as PXXH00,                                                                                          --sortNo              序号  Integer 不限    否  序号
  '' as chrgtype                                                                                        --chrgtype          费用类型    String      50  否
from VW_SF_RQDMX8 a
group by a.MZID00,a.JZDH00,a.XMMC00,a.XMDJ00,a.XMBH00
;


