prompt 137 住院纸质发票信息视图 VW_ZY_ZZFPXX
create or replace view VW_ZY_ZZFPXX
as
-- MODIFICATION HISTORY
-- Person Date Comments
-- jlg 2020.12.14 created 住院纸质发票信息视图 脱胎于VW_ZY_FPXX00 ZYSF9-20201214-002
-- jlg 2021.02.03 增加SJCYRQ字段；ZYTS00的计算由原来的CYRQ00改为SJCYRQ ZYSF9-20210126-003
-- jlg 2021.05.26 CDRG总金额组成增加CBYPF0字段 ZYSF9-20210524-005
-- jlg 2022.02.17 增加BJTDJH,BJTJE0,BJKJE0,BTKJE0字段 ZYSF9EX-20220117-002
-- chenrenhai 2023.06.07 增加函数SF_MZ_GETICKXX，获取默认的卡面号
select
b.ZYH000, --住院号
b.BRRYCS, --住院次数
substr(SF_ZY_GETYEXMXS(c.XM0000,a.YEXM00),1,50) as XM0000, --姓名
c.XB0000, --性别
a.YEXM00, --婴儿姓名
b.RYCWH0, --住院床位号
decode(b.YBKH00,'0',(select KMH000 from IC_YHXX00 where ICKH00=SF_MZ_GETICKXX(a.BRID00) and ZT0000='1'),b.YBKH00) as JZKH00, --就诊卡号/社保卡卡号
decode(b.YBKH00,'0',decode(a.YBDJH0,'0','0',(select nvl(max(KMH000),'0') from IC_YHXX00 where BRID00=a.BRID00 and ZT0000='1' and KLDH00='YB-SBK')),b.YBKH00) as YBKH00, --医保卡号
(select c.ICKH00 from IC_YHXX00 c where c.BRID00=a.BRID00 and c.ZT0000='1' and rownum = 1) as ICKH00, --病人IC卡号
(select d.YBID00 from BM_BRXXB0 d where d.BRID00=a.BRID00 and rownum = 1) as YBID00, --病人医保ID
b.DQBQ00, --当前病人住院病区
(select BMMC00 from BM_BMBM00 where BMBH00 = b.DQBQ00) as DQBQMC, --当前病人住院病区名称
b.DQKS00, --当前病人住院科室
(select BMMC00 from BM_BMBM00 where BMBH00 = b.DQKS00) as DQKSMC, --当前病人住院科室名称
b.RYRQ00, --入院日期
b.CYDJRQ, --出院登记日期
b.CYRQ00, --出院日期
b.SJCYRQ, --实际出院日期
greatest(1,to_date(b.SJCYRQ,'YYYYMMDD')-to_date(b.RYRQ00,'YYYYMMDD')) as ZYTS00, --住院天数
a.FFBZBM , --付费病种编码
(select BZMC00 from BM_BZSFBZ where FBBH00 = a.FBBH00 and YBZXLB = a.YBZXLB and FFBZBM = a.FFBZBM and rownum=1) as FFBZMC, --付费病种名称
decode(a.FFBZBM,null,'','单') as FFBZBZ, --付费单病种病人标识
a.JZDH00, --住院病人结帐单流水号(SQ_ZY_JZB000_JZDH00)
a.CXJZDH, --冲销结账单号
a.YBDJH0, --医保返回的单据号
a.BRID00, --病人ID
a.ZYID00, --住院ID,F.K=ZY.BRXXB0.ZYID00
a.FBBH00, --费别编号
(select FBMC00 from BM_BRFBB0 where FBBH00 = a.FBBH00) as FBMC00, --费别名称
substr(SF_ZY_GETFBXX00(a.FBBH00,a.YBZXLB,a.YBLB00,a.YBBRLB),1,100) as FBXX00, --费别信息
a.YBZXLB, --医保大中心
a.YBLB00, --医保分中心
a.YBBRLB, --医保病人类
decode(c.SFBCF0,'0',a.HJJE00,c.HJJE00) as HJJE00, --合计应收金额
substr(SP_BM_RMB000(decode(c.SFBCF0,'0',a.HJJE00,c.HJJE00)),1,100) as DXHJJE, --大写合计金额
decode(c.SFBCF0,'0',a.BRZJE0,c.HJJE00) as BRZJE0, --病人费用总金额(拟作废)
decode(c.SFBCF0,'0',a.BCBXF0,c.HJJE00) as BCBXF0, --住院医疗费总额(发给医保)
decode(c.SFBCF0,'0',a.ZFJE00,c.ZFJE00) as ZFJE00, --自付
decode(c.SFBCF0,'0',a.JZJE00,c.JZJE00) as JZJE00, --记帐金额
decode(c.SFBCF0,'0',a.GFJE00,0) as GFJE00, --公费金额，减免时用
decode(c.SFBCF0,'0',a.JMJE00,c.JMJE00) as JMJE00, --优惠金额
decode(c.SFBCF0,'0',a.GRZHZF,c.GRZHZF) as GRZHZF, --个人医疗帐户支付
decode(c.SFBCF0,'0',a.TCJJZF,c.TCJJZF) as TCJJZF, --统筹基金支付
decode(c.SFBCF0,'0',a.YBQTZF,c.YBQTZF) as YBQTZF, --医保其他支付
decode(sign(nvl(decode(c.SFBCF0,'0',a.YBQTZF,c.YBQTZF),0)),0,'','医保其他支付：'||to_char(decode(c.SFBCF0,'0',a.YBQTZF,c.YBQTZF))) as YBQTEX,--医保其他支付（扩展显示）
decode(c.SFBCF0,'0',a.MZBZJE,c.MZBZJE) as MZBZJE, --民政补助金额(不含于统筹基金支付的)
decode(c.SFBCF0,'0',a.DLSBJE,c.DLSBJE) as DLSBJE, --独立商保支付金额(不含于统筹基金支付的)
decode(c.SFBCF0,'0',a.DLDBJJ,c.DLDBJJ) as DLDBJJ, --商保统筹基金支付金额(不包含于统筹基金支付)
a.SBGRZH, --商保个人账户
a.SBTCJJ, --商保统筹基金
decode(c.SFBCF0,'1',0,a.CYYSK0) as CYYSK0, --出院应收款
decode(c.SFBCF0,'1',0,a.CYYTK0) as CYYTK0, --出院应退款
decode(c.SFBCF0,'1',0,a.YSZJE0) as YSZJE0, --预交金总额(现金)
decode(c.SFBCF0,'1',0,a.ZZZJE0) as ZZZJE0, --转账预交金总额
decode(c.SFBCF0,'1',0,a.YSZJE0 + a.ZZZJE0) as YJJZE0, --预交金总额(现金+转账)
decode(c.SFBCF0,'1',0,a.XJYE00) as XJYE00, --现金余额
decode(c.SFBCF0,'1',0,a.ZZYE00) as ZZYE00, --转账余额
decode(c.SFBCF0,'1',0,a.XJYE00 + a.ZZYE00) as XJZZYE, --现金+转账余额
decode(c.SFBCF0,'1',0,decode(sign(a.XJYE00),-1,0,a.XJYE00)) as XJTKJE, --现金退款金额
decode(c.SFBCF0,'1',0,decode(sign(a.ZZYE00),-1,0,a.ZZYE00)) as ZZTKJE, --转账退款金额
decode(c.SFBCF0,'1',0,decode(sign(a.XJYE00),-1,-a.XJYE00,0)) as XJBJJE, --现金补交金额
decode(c.SFBCF0,'1',0,decode(sign(a.ZZYE00),-1,-a.ZZYE00,0)) as ZZBJJE, --转账补交金额
decode(c.SFBCF0,'1','0',decode(sign(a.XJYE00),1,'(退)'||to_char(a.XJYE00),'0')) as XJTKJE_EX, --现金退款金额(中文显示)
decode(c.SFBCF0,'1','0',decode(sign(a.ZZYE00),1,'(退)'||to_char(a.ZZYE00),'0')) as ZZTKJE_EX, --转账退款金额(中文显示)
decode(c.SFBCF0,'1','0',decode(sign(a.XJYE00),-1,'(补)'||to_char(-a.XJYE00),'0')) as XJBJJE_EX , --现金补交金额(中文显示)
decode(c.SFBCF0,'1','0',decode(sign(a.ZZYE00),-1,'(补)'||to_char(-a.ZZYE00),'0')) as ZZBJJE_EX , --转账补交金额(中文显示)
decode(c.SFBCF0,'1',0,greatest(a.XJYE00+a.ZZYE00+nvl(a.YHTKJE,0)+nvl(a.WXTK00,0)+nvl(a.ZFBTK0,0),0)) as JSTKJE, --结算退款金额
decode(c.SFBCF0,'1',0,greatest(-(a.XJYE00+a.ZZYE00+nvl(a.YHTKJE,0)+nvl(a.WXTK00,0)+nvl(a.ZFBTK0,0)),0)) as JSBJJE, --结算补交金额
decode(c.SFBCF0,'1',0,a.XJFKJE) as XJFKJE, --现金付款,为了方便打印发票而设置的应退还病人的现金余额
decode(c.SFBCF0,'1',0,a.ZZFKJE) as ZZFKJE, --转帐付款,为了方便打印发票而设置的应退还病人的转帐余额
decode(c.SFBCF0,'1',0,a.XJFKJE + a.ZZFKJE) as JSFKJE, --结算付款金额（现金+转账）
decode(c.SFBCF0,'1',0,a.YHTKJE) as YHTKJE, --银行退款金额
decode(c.SFBCF0,'1',0,a.WXTK00) as WXTK00, --微信退款金额
decode(c.SFBCF0,'1',0,a.ZFBTK0) as ZFBTK0, --支付宝退款金额
decode(c.SFBCF0,'1','',decode(sign(a.YHTKJE),1,'退POS:'||to_char(a.YHTKJE)||'元','')) as YHTKJE_EX, --银行退款金额(中文显示)
decode(c.SFBCF0,'1','',decode(sign(a.WXTK00),1,'退微信:'||to_char(a.WXTK00)||'元','')) as WXTK00_EX, --微信退款金额(中文显示)
decode(c.SFBCF0,'1','',decode(sign(a.ZFBTK0),1,'退支付宝:'||to_char(a.ZFBTK0)||'元','')) as ZFBTK0_EX, --支付宝退款金额(中文显示)
decode(c.SFBCF0,'1',0,greatest(a.XJYE00+nvl(a.YHTKJE,0)+nvl(a.WXTK00,0)+nvl(a.ZFBTK0,0),0)) as XJTKHJ, --现金退款合计（含微信、支付宝退款）
a.JZZT00, --结算状态0:已正常结算 1：已取消结算
decode(a.CXJZDH,0,decode(a.JZZT00,'0','正常','被冲销'),'冲销') as JZZTMC, --结账状态名称
decode(a.CXJZDH,0,'正常','冲销') as CXZTMC, --冲销状态名称
a.FPHSBZ, --发票回收标志,0:未回收,1:已回收
a.JZRQ00, --结账日期
a.JZSJ00, --结账时间
a.JZRQ00||' '||JZSJ00 as JZRQSJ, --结账日期时间
substr(JZRQ00,1,4) as JZNIAN, --结账日期-年
substr(JZRQ00,5,2) as JZYUE0, --结账日期-月
substr(JZRQ00,7,2) as JZRI00, --结账日期-日
a.JZY000, --结帐操作员编码 F.K=BM_YGBM00.YGBH00
(select ZWXM00 from BM_YGBM00 where YGBH00 = a.JZY000) as JZYXM0, --结账员姓名
a.CZYKS0, --操作员科室
a.JZBQH0, --结账病区号(无用)
a.JMYY00, --减免原因
a.YBZYH0, --医保住院号
a.BCKBCS, --病人住院次数
a.TSBZBH, --特殊病种编号
(select MC0000 from BM_TSBZB0 where YBZXLB = a.YBZXLB and BH0000 = a.TSBZBH) as TSBZMC, --特殊病种名称
decode(a.TSBZBH,'','','特殊病种') TSBZBZ, --特殊病种标识
a.DBZBM0, --单病种ICD10编码
(select DBZMC0 from BM_DBZDEB where YBZXLB = a.YBZXLB and DBZBM0 = a.DBZBM0 and rownum=1) as DBZMC0,--单病种名称
(select YBHDJE from BM_DBZDEB where YBZXLB = a.YBZXLB and DBZBM0 = a.DBZBM0 and rownum=1) as YBHDJE,--单病种医保核定金额
a.JZFS00, --结算方式 实时结算 传送明细
decode(a.JZFS00,'0','传明细','1','实时结算','未定义') as JZFSMC, --结算方式名称
a.JKJKBZ, --健康接口上传标志 1 上传 0 不上传
a.CFXMS0, --住院处方项目总数
a.YBJZRQ, --医保结账日期
a.YBJZSJ, --医保结账时间
a.YBRYRQ, --医保入院日期
a.YBCYRQ, --医保出院日期
a.YBCYSJ, --医保出院时间
a.YBXJZF, --医保现金支付
a.YPWQZF, --医保药品完全自费合计
a.XMWQZF, --医保项目完全自费合计
a.YPBFZF, --医保药品部分自付合计
a.XMBFZF, --医保项目部分自付合计
a.YBQFJE, --医保起伏线
a.YBZHYE, --医保账户余额
decode(a.FBBH00,3,'个人账户余额：'||a.YBZHYE,'') as YBGRYE , --医保个人账户余额
a.FYQSRQ, --结算费用起始日期
a.FYJZRQ, --结算费用截止日期
decode(a.JSLX00,'0','中途结算 '||a.FYQSRQ||'-'||a.FYJZRQ,'1','出院结算 '||a.FYQSRQ||'-'||a.FYJZRQ,'') as JSQZRQ, --结算起止日期
a.GSDJH0, --工伤登记号
a.YLZFJE, --乙类自付金额
a.CFDXJE, --超封顶线金额
a.GWYBZ0, --公务员补助
a.JJZFE0, --保健基金支付
a.FYBJE0, --非医保金额
a.SYBXZF, --商业保险支付
a.SBZFE0, --商保基金支付额
a.BJZFE0, --保健基金支付额
a.YBYL01, --民政救助
a.YBYL02, --计生救助
a.YBYL03, --残联救助
a.YBYL04, --单病种限额
a.YBYL05, --医院支付(医保没返回)
a.YBYL06, --低保基金支付金额
a.YBYL07, --一般诊疗费补偿金额
a.YBYL08, --一般诊疗费费个人支付金额
a.YBYL09, --特殊门诊起付线累计
a.YBYL10, --特殊门诊基金累计
a.YBYL11, --健康账户支付
a.YBYL12, --健康账户余额
a.YBYL13, --共济账户支付
a.YBYL14, --共济账户余额
a.YBYL15, --健康账户支付医保费用
a.YBYL16, --健康账户支付非医保费用
a.YBYL17, --医保减免金额
a.YBYL18, --民政(医疗救助基金)补助金额
a.YBYL19, --超医保上限金额(非医保返回)
a.YBYL20, --个人账户支付起付段(三明地区商业保险接口)
a.YBYL21, --个人现金支付起付段(三明地区商业保险接口)
a.YBYL22, --血费报销金额(血费系统)
a.YBYL23, --医保结算金额误差（总费用-定额）
a.YBYL24, --大额医疗费用报销金额
a.YBYL25, --单病种补偿金额(定额-总费用)
a.YBYL26, --大病补充补偿金额
a.YBYL27, --保健医保统筹支付(已包含在统筹中)
a.YBYL28, --择日住院门诊总费用
a.YBYL29, --择日住院门诊范围内费用
a.YBYL30, --择日住院门诊统筹(医保未返回)
a.YBYL31, --个人支付金额(医保返回)
a.YBYL32, --大病补偿个人支付差额(医保返回)
a.YBYL33, --超标床位费用(付费单病种写入)
a.YBYL34, --除外耗材费用(付费单病种写入)
a.YBYL35, --医保返回的BCBXF0(付费单病种写入)
a.YBYL36, --累计封顶金额
a.YBYL37, --普通门诊累计
a.YBYL38, --特殊门诊累计
a.YBYL39, --CDRG个人支付金额 drgzfe
a.YBYL40, --大病本次补偿金额 dbbcbcje
a.YBYL41, --B账户支付金额 bkzfe0
a.YBYL42, --B账户余额 bkzhye
a.YBYL43, --可报高值耗材费(C-DRG)
a.YBYL44, --超标床位费(C-DRG)
a.YBYL45, --超标高值耗材费(C-DRG)
nvl(a.CBYPF0,0) as CBYPF0, --C-DRG超标药品费
a.YBYL46, --jzfp01 第一道精准扶贫
a.YBYL47, --jzfp02 第二道精准扶贫
a.YBYL48, --jzfp03 综合救助
a.YBYL49, --备用字段 --参数ZY_DBZJSZFJESF=1时,保留医保返回计算出来的医院支付金额
a.YBYL50, --csjz00 慈善救助金额
a.YBYL69, --特需医疗服务费（付费单病种写入）
a.MXZJE0, --单病种明细总额
a.YBSM01, --行政区号+区域
a.YBSM02, --是否择日住院
a.YBSM03, --年度结算截止日期
a.YBSM04, --血费报销ID(血费系统)
a.YBSM05, --单病种登记流水号
a.YBSM06, --结算类型 0 医保 1生育 3保健
a.YBSM07, --新生儿费用发送医保返回单据号 djlsh0
a.YBSM08, --单病种数据拆分费用单据号
a.YBSM09, --病情编码或CDRG组别 bqbm00
a.YBSM10, --CDRG病例类型 jsbllx
(select MC0000 from BM_TYZD00 where ZDMC00='CDRG病历类型' and BH0000 =a.YBSM10) as JSBLMC, --C-DRG结算病历类型名称
a.YBSM11, --CDRG结算ID monitorID
c.PJH000, --打印的票据流水号,F.K=ZY_PJSYQK.PJH000
c.FZPJH0, --分组票据号
SF_ZY_GETYXPJH(c.FZPJH0) as YXPJH0, --有效的票据号
c.PJXH00, --打印的票据序号
c.PJZT00, --票据状态
decode(c.PJZT00,'0','有效',decode(substr(c.PJXH00,1,1),'-','虚拟','作废')) as PJZTMC, --票据状态名称
c.SFDY00, --是否打印,'Y':票据已打印,'N':票据未打印
c.CZY000, --打印操作员
c.CZYXM0, --打印操作员姓名
c.DYRQ00 as FPDYRQ , --发票打印日期
c.DYSJ00 as FPDYSJ , --发票打印时间
decode(c.BDBZ00,'1','是','否') as SFBDBZ, --补打标志
c.SFBBD0, --是否被补打,补打时旧发票和负发票改1;取消发票时,旧发票和负发票改1
c.JKDID0, --结账缴款单ID
c.HTFPDM, --航天金税发票十位代码
c.HTFPXH, --航天金税发票序号
a.JSLX00, --结算类型 0:中途结算,1:出院结算2:呆账结算3:年度结算
decode(a.JSLX00,'0','中途结算','1','出院结算','2','挂账结算','3','年度结算') as JSLXMC, --结算类型名称
(select nvl(ZYE000,0) from ZY_BRZHXX where ZYID00 =a.ZYID00 and JFLBID = 2) as ZYE000, --账户余额
decode(b.YBZYH0,'0',decode(a.ybdjh0,'0',b.ZYH000,a.YBZYH0),b.YBZYH0) as FPZYH0, --发票打印的住院号(医保的取医保住院号)
decode(a.YBDJH0,'0',to_char(a.JZDH00),a.YBDJH0) as FPLSH0, --发票打印的流水号(医保的取医保单据号)
decode(b.YBZYH0,'0',decode(a.YBDJH0,'0',' ','('||trim(b.ZYH000)||')'),'('||trim(b.ZYH000)||')') as YNZYH0, --院内住院号
decode(a.YBYL05,0,0,a.TCJJZF+a.ZFJE00) as DBZXE0, --单病种限额
substr(SP_BM_RMB000(decode(a.YBYL05,0,0,a.TCJJZF+a.ZFJE00)),1,100) as DXBZXE, --大写单病种限额
decode(nvl(a.YBYL49,0)+a.YBYL05,0,decode(c.SFBCF0,'0',a.ZFJE00,c.ZFJE00),decode(c.SFBCF0,'0',a.ZFJE00-greatest(a.YBYL49,0),c.ZFJE00)) as YBZFJE, --兼容单病种自付金额
nvl(a.YBYL49,0)+a.YBYL05 as YYZFJE, --医院支付金额
substr(SP_BM_RMB000(nvl(a.YBYL35,0)),1,100) as DXYB35, --医保返回的BCBXF0(付费单病种写入)的大写金额
nvl(a.YBYL39,0)+nvl(a.YBYL43,0)+nvl(a.YBYL44,0)+nvl(a.YBYL45,0)+nvl(a.CBYPF0,0) as CDRGJE, --CDRG总金额
substr(SP_BM_RMB000(nvl(a.YBYL39,0)+nvl(a.YBYL43,0)+nvl(a.YBYL44,0)+nvl(a.YBYL45,0)+nvl(a.CBYPF0,0)),1,100) as CDRGDX, --CDRG总金额大写
nvl(a.XJYJZE,YSZJE0) as XJYJZE, --发票现金预交总额
nvl(a.ZZYJZE,ZZZJE0) as ZZYJZE, --发票转账预交总额
nvl(a.XJYJZE,YSZJE0) + nvl(a.ZZYJZE,ZZZJE0) as FPYJZE, --发票总预交金额
greatest(nvl(a.XJYJZE,YSZJE0) + nvl(a.ZZYJZE,ZZZJE0) - a.ZFJE00,0) as FPTKZE, --发票应退款金额
greatest(a.ZFJE00 - nvl(a.XJYJZE,YSZJE0) - nvl(a.ZZYJZE,ZZZJE0),0) as FPBSZE, --发票应补收总额
a.BJTDJH, --结算补交退预交金单据号
a.BJTJE0, --结算补交退预交金金额（有正负，正数为补交款，负数为补退款）
greatest(a.BJTJE0,0) as BJKJE0, --补交款金额（正数）
greatest(-a.BJTJE0,0) as BTKJE0, --补退款金额（正数）
nvl(a.YBYL04,0) + nvl(a.YBYL33,0) + nvl(a.YBYL34,0) + nvl(a.YBYL69,0) as DBZJE0, --付费单病种总金额
substr(SP_BM_RMB000(nvl(a.YBYL04,0) + nvl(a.YBYL33,0) + nvl(a.YBYL34,0) + nvl(a.YBYL69,0)),1,100) as DXBZJE, --大写付费单病种总金额
a.ZFJE00 + a.GRZHZF + nvl(a.YBYL41,0) as GRZF01, --CGRG个人支付总额
decode(sign(a.YBYL01),1,'医疗救助:'||a.YBYL01,'') as YLJZ00, --医疗救助
decode(sign(a.YBYL46),1,'第一扶贫:'||a.YBYL46,'') as YDFP00, --第一扶贫
decode(sign(a.YBYL47),1,'第二扶贫:'||a.YBYL47,'') as YEFP00, --第二扶贫
decode(sign(a.YBYL50),1,'慈善救助:'||a.YBYL50,'') as CSJZ00, --慈善救助
decode(sign(a.YBYL24),1,'商保支付:'||a.YBYL24,'') as DBBC00, --商保支付（大病补偿）
nvl((select ZWXM00 from BM_YGBM00 i,BM_BMBM00 j where i.YGBH00 = j.BMJBR0 and j.BMBH00 = b.DQBQ00 and rownum=1),c.CZYXM0) as JBRXM0, --部门经办人姓名
a.YBYL58, --大病补偿（大额医疗），新医保
a.YBYL60, --医疗救助，新医保
decode(nvl(a.YBYL24,0),0,nvl(a.YBYL58,0),a.YBYL24) as DBBC01, --判断新旧医保大病补偿（大额医疗）
c.WBPJLY, --外部票据来源
decode(c.WBPJLY,'02','博思电子发票','03','博思纸质发票','04','电子发票作废红票(博思)','05','纸质发票作废红票(博思)') as WBLYMC, --外部票据来源名称
c.WBPJXH, --外部票据序号
c.WBPJDM, --外部票据代码
c.CJLYBM, --厂家来源编码 BS:博思，YH:易惠，待扩展。。。
nvl(c.WBID00,0) as WBID00, --外部票据ID
g.KJCZY0, --开具电子发票操作员BM_YGBM00.YGBH00
(select ZWXM00 from BM_YGBM00 where YGBH00 = g.KJCZY0) as KJCZXM, --
g.KJPDBM, --开具开票点编码
g.KJCZKS, --开具操作员科室BM_BMBM00.BMBH00
(select BMMC00 from BM_BMBM00 where BMBH00 = g.KJCZKS) as KJKSMC, --
g.KJRQ00, --开具日期
g.KJSJ00, --开具时间
g.KJRQ00||g.KJSJ00 as KJRQSJ, --
g.DZFPXH, --电子发票序号
g.DZFPDM, --电子发票代码
g.DZJYM0, --电子校验码
g.DZEWM0, --电子二维码
g.ZZCZY0, --纸质发票操作员BM_YGBM00.YGBH00
(select ZWXM00 from BM_YGBM00 where YGBH00 = g.ZZCZY0) as ZZCZXM, --
g.ZZPDBM, --纸质开票点编码
g.ZZCZKS, --纸质发票操作员科室BM_BMBM00.BMBH00
(select BMMC00 from BM_BMBM00 where BMBH00 = g.ZZCZKS) as ZZKSMC, --
g.ZZFPRQ, --纸质发票日期
g.ZZFPSJ, --纸质发票时间
g.ZZFPRQ||g.ZZFPSJ as ZZRQSJ, --
g.ZZFPXH, --纸质发票序号
g.ZZFPDM, --纸质发票代码
g.CXCZY0, --冲销操作员BM_YGBM00.YGBH00
(select ZWXM00 from BM_YGBM00 where YGBH00 = g.CXCZY0) as CXCZXM, --
g.CXPDBM, --冲销开票点编码
g.CXCZKS, --冲销操作员科室BM_BMBM00.BMBH00
(select BMMC00 from BM_BMBM00 where BMBH00 = g.CXCZKS) as CXKSMC, --
g.CXRQ00, --冲销日期
g.CXSJ00, --冲销时间
g.CXRQ00||g.CXSJ00 as CXRQSJ, --
g.CHFPXH, --冲红发票序号
g.CHFPDM, --冲红发票代码
g.CHJYM0, --冲红校验码
g.CHEWM0 --冲红二维码
from ZY_JZB000 a,ZY_BRXXB0 b,ZY_PJSYQK c,ZY_WBFPXX g
where a.ZYID00 = b.ZYID00
and a.PJH000 = c.FZPJH0
and nvl(c.WBID00,0) = g.WBID00(+)
and c.PLBH00 = 3
and nvl(c.PJZT00,'0') = '0'
/
---%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%