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 ;