create or replace view vw_zy_op_electronic_bill_yj as
select
  c.ZYID00 as  ZYID00,                                                        --住院ID
  C.BRID00 as  BRID00,                                                        --病人ID
  c.ZYH000 as  ZYH000,                                                        --住院号
  a.JZDH00 as  JZDH00,                                                        --结账单号
  a.JZRQ00 as  JZRQ00,                                                        --结账日期
  a.JZSJ00 as  JZSJ00,                                                        --结账时间
  a.JZY000 as  JZY000,                                                        --结账员编码
  d.ZWXM00 as  JZYXM0,                                                        --结账员姓名
  a.CZYKS0 as  CZYKS0,                                                        --操作员科室
  a.YBZHYE as  ybzhye,                                                        --医保账户余额
  0 as ptmzlj,                                                                --普通门诊累计
  0 as tsmzlj,                                                                --特殊门诊累计
  trim(a.dbzbm0) as dbzbm0,                                                   --单病种编码
  trim(a.FFBZBM) as ffbzbm,                                                   --付费病种编码
  nvl(a.YBYL04,0) as  bzdebz,                                                 --单病种限额
  nvl(a.YBYL33,0) as  bzcbcw,                                                 --超标床位费用(付费单病种写入)
  nvl(a.YBYL34,0) as  bzcwhc,                                                 --除外耗材费用(付费单病种写入)
  nvl(a.YBYL69,0) as  bztsyl,                                                 --特需医疗服务费（付费单病种写入）
  nvl(a.YBYL04,0)+nvl(a.YBYL33,0)+nvl(a.YBYL34,0)+nvl(a.YBYL69,0) as BZHJJE,  --单病种合计金额
  a.YBSM09,                                                                   --CDRG组别
  a.YBSM10,                                                                   --CDRG病例类型
  a.YBYL39,                                                                   --CDRG标准费用
  a.YBYL43,                                                                   --可报高值耗材费(C-DRG)
  a.YBYL44,                                                                   --超标床位费(C-DRG)
  a.YBYL45,                                                                   --超标高值耗材费(C-DRG)
  a.CBYPF0,                                                                   --另收高值药品费(C-DRG)
  nvl(a.YBYL39,0)+nvl(a.YBYL43,0)+nvl(a.YBYL44,0)+nvl(a.YBYL45,0)+nvl(a.CBYPF0,0)  as CDRGJE,                --CDRG总金额                  string        是               String  50    是 单位内部唯一               Number  14,2  是                    String  10    是
  '2' as einvoicespecimen,                                                   --einvoicespecimen     电子票据模块 NString  1 (1门诊 2 住院)
  a.JZRQ00 as  issuedate,                                                    --issuedate    开票日期    String  8  是  格式：yyyyMMdd
  a.JZSJ00 as  issuetime,--issuetime   开票时间    String  8  是  格式：hh:mm:ss
  '1' as  payerpartytype,                                                    --payerpartytype       交款人类型  NString  1   是  交款人类型：1 个人 2 单位
  b.BRZJBH as payerpartycode,                                                --payerpartycode       交款人代码    String  [0,30]   否 单位一般为组织机构代码；个人一般为身份证号,选填
  substr(SF_ZY_GETYEXMXS(b.BRXM00,a.YEXM00),1,50) as payerpartyname,         --payerpartyname       交款人名称    String  [1,100]   是
  ''as payeracct,                                                            --payeracct            交款人账号    String  [0,50]   否
  ''as payeropbk,                                                            --payeropbk            交款人开户行    String  [0,100]   否
  nvl(b.BRDH00,b.BRLXDH) as  payerphonenumber,                               --payerphonenumber     交款人手机号  NString  [1,20] 否
  ''as recname,                                                              --recname              收款人    String  [1,100]   否
  ''as recacct,                                                              --recacct              收款人账号    String  [0,50]   否
  ''as recopbk,                                                              --recopbk              收款人开户行    String  [0,100]   否
  a.HJJE00 as totalamount,                                                   --totalamount          项目总金额     Currency  15,2  是
  '01'||to_char(SF_ZY_GETYXPJH(a.PJH000))  as bizcode,                                                      --bizcode              业务流水号     String  [1,32]  是 业务号必填，唯一
  d.ZWXM00 as handlingperson,                                                --handlingperson       开票人（收款人） String  [1,200] 是  票据右下角收款人
  d.ZWXM00 as checker,                                                       --checker              复核人 String  [1,200] 是  票据右下角复核人
  SF_ZY_GETDZFPREMARK(a.ZYID00,a.JZDH00) as remark,                          --remark  其他信息  String  [0,4000]否 备注中插入\r\n标识换行标记
  ''as relatedinvoicecode,                                                   --relatedinvoicecode   相关票据代码   NString  8  预留扩展字段，开具红票时在此填写原票据代码
  ''as relatedinvoicenumber,                                                 --relatedinvoicenumber 相关票据号码  NString  10  预留扩展字段，开具红票时在此填写原票据号码
  a.JZDH00 as businessnumber,                                                --businessnumber       业务单号 是  NString [0,32] 否
  a.JZRQ00 as businessdate,                                                  --businessdate         业务日期   String  8  否  格式：yyyymmdd
  c.XB0000 as gender,                                                        --gender               性别       String  [0,2]   否  男 、女(门诊、住院填写)
  '住院'as medicaltype,                                                      --medicaltype          医疗类别    String  [0,30]  否 门诊、急诊、住院等 (门诊、住院填写)
  trim(c.ZYH000) as patientnumber,                                                 --patientnumber        门诊号     String  [0,30]  否(门诊填写)
  c.RYRQ00 as medicaldate,                                                   --medicaldate          就诊日期    String  8    否 yyyymmdd(门诊、住院填写)
  substrb(nvl(SF_SF_TYZD00('第三方电子发票平台接口','medicalInstitution'),'综合医院'),1,60) as orgtype,--orgtype  医疗机构类型    String  [0,30]  否 (门诊、住院填写)
  (select FBMC00 from BM_BRFBB0 where FBBH00 = a.FBBH00) as medicalinsurancetype,--medicalinsurancetype 医保类型     String  [0,30]   否 (门诊、住院填写)
  --e.YBMC00 as medicalinsurancetype,                                          --medicalinsurancetype 医保类型     String  [0,30]   否 (门诊、住院填写)
  c.YBID00 as medicalinsuranceid,                                            --medicalinsuranceid   医保编号      String [0,30]   否 (门诊、住院填写)
  nvl(a.TCJJZF,0) as fundpayamount,                                          --fundpayamount        医保统筹基金支付  Currency 15,2  否 (门诊、住院填写)
  0 as otherpayamount,                                                       --otherpayamount       其它支付      Currency 15,2  否 (门诊、住院填写)
  nvl(a.GRZHZF,0) as accountpayamount,                                       --accountpayamount     个人账户支付  Currency 15,2  否 (门诊、住院填写)
  nvl(a.ZFJE00,0) as ownpayamount,                                           --ownpayamount         个人现金支付  Currency 15,2  否 (门诊、住院填写)
  nvl(a.ZFJE00,0) as selfpaymentamount,                                      --selfpaymentamount    个人自付      Currency 15,2  否 (门诊、住院填写)
  nvl(a.YBYL63,0) as selfpaymentcost,                                        --selfpaymentcost      个人自费      Currency 15,2  否 (门诊、住院填写)
  trim(B.BRBLH0) as casenumber,                                                           --casenumber           病例号    NString  [0,50]   否 (住院填写)
  trim(c.ZYH000) as hospitalizationnumber,                                                --hospitalizationnumber   住院号    NString  [0,20]   否 (住院填写)
  (select aa.BMMC00 from BM_BMBM00 aa where aa.BMBH00=c.DQKS00) as departmentname,                                                       --departmentname          科别   String  [0,50]   否 (住院填写)
  c.SJRYRQ as inhospitaldate,                                                       --inhospitaldate         住院日期   String  8  否 yyyymmdd (住院填写)
  nvl(decode(C.CYDJRQ,'20991231',to_char(sysdate,'YYYYMMDD'),C.CYDJRQ),to_char(sysdate,'YYYYMMDD')) as outhospitaldate,                                                      --outhospitaldate        出院日期   String  8  否 yyyymmdd (住院填写)
  nvl(a.YSZJE0,0) as prepayamount,                                                         --prepayamount          预缴金额    Currency 15,2  否 (住院填写)
  decode(sign(a.XJYE00),-1,-a.XJYE00,0) as rechargeamount,                                                       --rechargeamount        补缴金额    Currency 15,2  否 (住院填写)
  decode(sign(a.XJYE00),-1,0,a.XJYE00)+nvl(a.YHTKJE,0)+nvl(a.WXTK00,0)+nvl(a.ZFBTK0,0) as refundamount,                                                         --refundamount          退费金额    Currency 15,2  否 (住院填写)
  2 as state,--state     获取返回参数方式   Int 1  否  1：直接返回data，需等待 2：无 data后续发主动获取接口查询（空值，默认为2）
  SF_ZY_YJDZFP_JSON(a.ZYID00,a.JZDH00,a.HJJE00,1) as chargeDetail,           --chargeDetail          收费项目明细列表          String  不限    是 JSON
  '' as listDetail                                                           --listDetail          清单项目明细列表
from ZY_JZB000 a,BM_BRXXB0 b,ZY_BRXXB0 c,BM_YGBM00 d,IC_YBBRLB E
where a.BRID00=b.BRID00
  and a.ZYID00=c.ZYID00
  and a.JZY000=d.YGBH00
  and b.YBLB00=e.YBLB00
  and b.FBBH00=e.FBBH00
;
