create or replace view vw_zy_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.ZYFPBM,bb.ZYFPBM)) 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 ZY_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_zy_rqdhz0_ybxm as
select 
  a.BRID00,                          --1.病人ID
  a.ZYID00,                          --2.住院ID
  a.ZYH000,                          --3.住院号
  a.XM0000,                          --4.姓名
  a.XMBH00,                          --5.编码  医疗收费编码或药品编码
  a.GJBM00,                          --6.国家编码
  a.YBXMBH,                          --7.医保项目编号
  a.YPFB00,                          --8.医保药品费别       
  a.XMMC00,                          --9.项目名称
  a.ZLXMJC,                          --10.诊疗项目简称
  a.XMGG00,                          --11.药品才有规格
  a.XMDW00,                          --12.单位
  a.CZRQ00,                          --13.操作日期
  a.QDRQ00,                          --14.清单日期
  a.ZYFPID,                          --15.住院发票ID
  a.FPXMBH,                          --16.发票项目编号
  a.FPXMMC,                          --17.发票项目名称
  a.ZFBL00,                          --18.医保自付比例，如果自费发送标志为Y时，自费比例直接显示为1
  a.XMSL00,                          --19.数量
  a.HJJE00,                          --20.合计金额
  a.XMDJ00,                          --21.单价
  a.ZFJE00,                          --22.自费金额
  a.GFJE00,                          --23.公费金额
  a.JZJE00,                          --24.记帐金额
  a.JMJE00,                          --25.减免金额
  a.JZDH00,                          --26.结账单号
  a.YEXM00,                          --27.婴儿姓名
  a.SFLB00,                          --28.收费类别
  a.ZXKS00,                          --29.执行科室
  a.ZXKSMC,                          --30.执行科室名称
  (select YBJSDJ from BM_YBSFXM where XMBH00=a.YBXMBH
         and (FBBH00,YBZXLB)=(select FBBH00,YBZXLB from ZY_BRXXB0 where ZYID00=a.ZYID00)) YBJSDJ,   --31.医保结算单价
  decode(a.SFLB00,3,(select YPBM00 from BM_YD0000 where YPNM00 = a.XMBH00),
                 (select BH0000 from BM_YYSFXM where SFXMID = a.XMBH00)) as SFXMBH,--32.收费项目编号(收费项目或药品编号)
  a.GJBZBM,                          --33.国家医保编码
  a.GJBZMC,                           --34.国家医保名称
  a.ZYFPBM                            --35.电子发票接口-门诊发票编码
from (
  select
       a.BRID00,                                                         --病人ID
       a.ZYID00,                                                         --住院ID
       a.ZYH000,                                                         --住院号
       a.XM0000,                                                         --姓名
       a.XMBH00,                                                         --编码  医疗收费编码或药品编码
       a.GJBM00,                                                         --国家编码
       (select max(c.YBXMBH) from ZY_BRXXB0 b,VW_BM_YBSFDY c where b.FBBH00 = c.FBBH00 and b.YBZXLB = c.YBZXLB and b.YBBRLB = c.YBBRLB 
         and b.ZYID00 = a.ZYID00 and decode(a.SFLB00,3,'Y','N')= c.SFYP00 and a.XMBH00 = c.SFXMID and rownum=1) as YBXMBH,         --医保项目编号 
       (select decode(c.YBLB00,'0','医保甲类','1','医保乙类','2','医保丙类', '4','甲类药品','5','乙类药品','7','丙类药品')          
         from ZY_BRXXB0 b,VW_BM_YBSFDY c where b.FBBH00 = c.FBBH00 and b.YBZXLB = c.YBZXLB and b.YBBRLB = c.YBBRLB
         and b.ZYID00 = a.ZYID00 and decode(a.SFLB00,3,'Y','N')= c.SFYP00 and a.XMBH00 = c.SFXMID and rownum=1) as YPFB00,         --医保药品费别     
       a.XMMC00,                                                         --项目名称
       decode(a.SFLB00,'3','',max(g.ZLXMJC)) as ZLXMJC,                  --诊疗项目简称
       a.XMGG00,                                                         --药品才有规格
       a.XMDW00,                                                         --单位
       a.CZRQ00,                                                         --操作日期
       nvl(a.QDRQ00,a.CZRQ00) as QDRQ00,                                 --清单日期
       a.ZYFPID,                                                         --住院发票ID
       f.BH0000 as FPXMBH,                                               --发票项目编号
       f.XMMC00 as FPXMMC,                                               --发票项目名称
       f.ZYFPBM as ZYFPBM,                                               --电子发票接口-门诊发票编码
       decode(nvl(a.ZFFSBZ,'N'),'Y',1,a.ZFBL00)*100 as ZFBL00,           --医保自付比例，如果自费发送标志为Y时，自费比例直接显示为1
       sum(a.XMSL00) as XMSL00,                                          --数量
       sum(a.HJJE00) as HJJE00,                                          --合计金额
       a.XMDJ00,                                                         --单价
       sum(a.ZFJE00) as ZFJE00,                                          --自费金额
       sum(a.GFJE00) as GFJE00,                                          --公费金额
       sum(a.JZJE00) as JZJE00,                                          --记帐金额
       sum(a.JMJE00) as JMJE00,                                          --减免金额
       a.JZDH00,                                                         --结账单号
       a.YEXM00,                                                         --婴儿姓名
       a.SFLB00,                                                         --收费类别
       a.ZXKS00,                                                         --执行科室
       (select BMMC00 from BM_BMBM00 where BMBH00 = a.ZXKS00) as ZXKSMC, --执行科室名称
       nvl(a.GJBZBM,SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'1')) GJBZBM,    --国家医保编码
       nvl(a.GJBZMC,SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'2')) GJBZMC     --国家医保名称
  from ZY_FYMX00 a,BM_FPXM00 f,YJ_YW0000 g
    where a.ZYFPID = f.FPXMID
      and a.ZYID00 = g.ZYGHID(+)
      and a.DJID00 = g.YJDJH0(+)
      and a.XMSL00 > 0 
    group by a.BRID00,a.ZYID00,a.ZYH000,a.XM0000,a.JZDH00,a.XMBH00,a.GJBM00,a.XMMC00,ZXKS00,
             a.XMGG00,a.XMDW00,a.CZRQ00,nvl(a.QDRQ00,a.CZRQ00),a.ZYFPID,f.BH0000,f.XMMC00,f.ZYFPBM,
               a.ZFBL00,a.YEXM00,a.SFLB00,a.XMDJ00,a.ZFFSBZ,
                 nvl(a.GJBZBM,SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'1')),
                 nvl(a.GJBZMC,SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'2'))
  union all
  select
       a.BRID00,                                                         --病人ID
       a.ZYID00,                                                         --住院ID
       a.ZYH000,                                                         --住院号
       a.XM0000,                                                         --姓名
       a.XMBH00,                                                         --编码  医疗收费编码或药品编码
       a.GJBM00,                                                         --国家编码     
       (select max(c.YBXMBH) from ZY_BRXXB0 b,VW_BM_YBSFDY c where b.FBBH00 = c.FBBH00 and b.YBZXLB = c.YBZXLB and b.YBBRLB = c.YBBRLB 
         and b.ZYID00 = a.ZYID00 and decode(a.SFLB00,3,'Y','N')= c.SFYP00 and a.XMBH00 = c.SFXMID and rownum=1) as YBXMBH,         --医保项目编号  
       (select decode(c.YBLB00,'0','医保甲类','1','医保乙类','2','医保丙类', '4','甲类药品','5','乙类药品','7','丙类药品')          
         from ZY_BRXXB0 b,VW_BM_YBSFDY c where b.FBBH00 = c.FBBH00 and b.YBZXLB = c.YBZXLB and b.YBBRLB = c.YBBRLB
         and b.ZYID00 = a.ZYID00 and decode(a.SFLB00,3,'Y','N')= c.SFYP00 and a.XMBH00 = c.SFXMID and rownum=1) as YPFB00,         --医保药品费别                
       a.XMMC00,                                                         --项目名称
       decode(a.SFLB00,'3','',max(g.ZLXMJC)) as ZLXMJC,                  --诊疗项目简称
       a.XMGG00,                                                         --药品才有规格
       a.XMDW00,                                                         --单位
       a.CZRQ00,                                                         --操作日期
       nvl(a.QDRQ00,a.CZRQ00) as QDRQ00,                                 --清单日期
       a.ZYFPID,                                                         --住院发票ID
       f.BH0000 as FPXMBH,                                               --发票项目编号
       f.XMMC00 as FPXMMC,                                               --发票项目名称
       f.ZYFPBM as ZYFPBM,                                               --电子发票接口-门诊发票编码
       decode(nvl(a.ZFFSBZ,'N'),'Y',1,a.ZFBL00)*100 as ZFBL00,           --医保自付比例，如果自费发送标志为Y时，自费比例直接显示为1
       sum(a.XMSL00) as XMSL00,                                          --数量
       sum(a.HJJE00) as HJJE00,                                          --合计金额
       a.XMDJ00,                                                         --单价
       sum(a.ZFJE00) as ZFJE00,                                          --自费金额
       sum(a.GFJE00) as GFJE00,                                          --公费金额
       sum(a.JZJE00) as JZJE00,                                          --记帐金额
       sum(a.JMJE00) as JMJE00,                                          --减免金额
       a.JZDH00,                                                         --结账单号
       a.YEXM00,                                                         --婴儿姓名
       a.SFLB00,                                                         --收费类别
       a.ZXKS00,                                                         --执行科室
       (select BMMC00 from BM_BMBM00 where BMBH00 = a.ZXKS00) as ZXKSMC, --执行科室名称
       nvl(a.GJBZBM,SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'1')) GJBZBM,    --国家医保编码
       nvl(a.GJBZMC,SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'2')) GJBZMC     --国家医保名称
  from ZY_FYMX00 a,BM_FPXM00 f,YJ_YW0000 g
    where a.ZYFPID = f.FPXMID
      and a.ZYID00 = g.ZYGHID(+)
      and a.DJID00 = g.YJDJH0(+)
      and a.XMSL00 < 0 
    group by a.BRID00,a.ZYID00,a.ZYH000,a.XM0000,a.JZDH00,a.XMBH00,a.GJBM00,a.XMMC00,ZXKS00,
             a.XMGG00,a.XMDW00,a.CZRQ00,nvl(a.QDRQ00,a.CZRQ00),a.ZYFPID,f.BH0000,f.XMMC00,f.ZYFPBM,
                 a.ZFBL00,a.YEXM00,a.SFLB00,a.XMDJ00,a.ZFFSBZ,
                 nvl(a.GJBZBM,SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'1')),
                 nvl(a.GJBZMC,SF_XT_GETGJBZBMMC(a.XMBH00,decode(a.SFLB00,'3','Y','N'),'2'))
) a
;



create or replace view vw_zy_listdetail as
select
  a.BRID00 as BRID00,                                                                                    --病人ID 
  a.ZYID00 as ZYID00,                                                                                    --住院ID
  a.ZYH000 as ZYH000,                                                                                    --住院号 
  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.QDRQ00) as listDetailNo,                                                                         --listDetailNo     明细流水号  String  60  否  明细流水号
  max(decode(SF_SF_TYZD00('第三方电子发票平台接口','chargeCode'),'1',a.ZYFPBM,a.ZYFPBM))  as chargeCode, --chargeCode         收费项目代码  String  50  否  填写业务系统内部编码值，由医疗平台配置对照,如：床位费、检查费
  max(a.FPXMMC) as chargeName,                                                                           --chargeName         收费项目名称  String  100 否  
  max(a.QDRQ00) as prescribeCode,                                                                        --prescribeCode      处方编码  String  60  否  
  '' as listTypeCode,                                                                                    --listTypeCode     药品类别编码    String  50  否  如药品分类编码01，有则填写
  '' as listTypeName,                                                                                    --listTypeName     药品类别名称    String  50  否  如药品分类名称，抗生素类抗感染药物
  substrb(max(nvl(a.YBXMBH,nvl(trim(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_ZY_RQDHZ0_YBXM a 
where a.HJJE00<>0
group by a.BRID00,a.ZYID00,a.ZYH000,a.JZDH00,a.XMMC00,a.XMDJ00,a.XMBH00
;



