CREATE VIEW SD_HOSPITAL.VW_ZY_FP0000 ( JZDH00, YBDJH0, ZYID00, BRID00, BRZJE0, JZJE00, GFJE00, HJJE00, JMJE00, GRZHZF, TCJJZF, SBGRZH, SBTCJJ, ZFJE00, SJZFJE, PJH000, JZRQ00, JZSJ00, XJFKJE, ZZFKJE, JZY000, JZYXM0, JMYY00, ZYH000, YBZYH0, XM0000, XB0000, RYCWH0, DQBQ00, DQBQMC, DQKS00, DQKSMC, SFJSDW, YSZJE0, ZZZJE0, XJYE00, ZZYE00, YBID00, YBZHYE, GWYBZ0, YLZFJE, JJZFE0, CFDXJE, FYBJE0, BCKBCS, YBXJZF, BRRYCS, RYRQ00, CYDJRQ, FPDYRQ, FPDYSJ, FBBH00, SYBXZF, YBLB00, YBBHSB, XKH000, SBZFE0, BJZFE0, YBYL01, YBYL02, YBYL03, YBYL04, YBYL05, YBYL06, YBYL07, YBYL08, YBYL09, YBYL10, YBYL11, YBYL12, YBYL13, YBMC00, BRZJBH, CYXJ00, BRZTQK, NBPFJE, DBZBM0, DBZMC0, BZBMLB, YBYL14, YBYL15, YBYL16, YBYL17, YBYL18, YBYL19, YBYL20, YBYL21, YBYL22, YBYL23, YBYL24, YBYL25, YBYL26, YBYL27, YBYL28, YBYL29, YBYL30, ZCJZDH, BDBZ00, SFXNH0, BRBLH0, YBKH00, YHTKJE, GRXJZF, YEXM00, CYRQ00, PJXH00, YBJXJ0, YBJZZ0, YBJZE0, YTHXJ0, YTHZZ0, YTHZE0, MXZJE0, YBYL31, YBYL32, YBYL33, YBYL34, YBYL35, YBYL36, YBYL37, YBYL38, YBYL39, YBYL40, YBYL41, YBYL42, YBYL43, YBYL44, YBYL45, YBYL46, YBYL47, YBYL48, YBYL49, BRZHYE, TTZYH0, TTZYID, DWID00, TTLX00, BZFPGS, BZFPJE, WXTK00, ZFBTK0, YBSM01, YBSM02, YBSM03, YBSM04, YBSM05, YBSM06, YBSM07, YBSM08, YBSM09, YBSM10, YBSM11, YBSM12, YBSM13, YBSM14, YBSM15, YBSM16, YBYL50, DZZFJE, YBZXLB, BRDH00, BRLXDH, BRCSRQ, CZYKS0, WBID00, CJLYBM, BRSJXM, BRXBBM, YBZXMC, FBMC00, CXJZDH, DDLSH0, YBDZZT, YBJKID, MXLSH0, YBYL51, AKC227, BKC102, BKC040, BKC041, BKC045, BKC059, BKC052, BKC062, BKC060, AKE173, AKE026, BKC011, AKC087, AKA151, AKA150, BKC612, BKC064, TXYLFWFY, JSQYE0, JSHYE0, BKC001_MC, AAE140, AAE140_MC, BKE174, BKE174_MC, BKC620, BKC619, FKJSLX, FKLSH0, FKJZDH, FKJZJE, YBBXZE, DLTCJJ, DLZHZF, YBQTZE, DLSYBX, DLJZFP, DLQTJJ, DLQYBC, MZBZJE, DLSBJE, GWBZJE, DLDBJJ, CJBTCE, GHHZJE, DXJFJE, FPHJJE, FPHJDX, WBPJLY, WBLYMC, WBPJDM, WBPJXH, CVL_ACCT_PAY, CVL_ACCT_BALC, HL_ACCT_PAY, HL_ACCT_BALC, ACCT_MULAID_PAY, FM_ACCT_BALC, PUR_ACCT_PAY, INSUPLC_ADMDVS, MDTRTAREA_ADMVS, SJCYRQ, SJRYRQ ) AS select JZDH00 , --住院病人结帐单流水号 YBDJH0 , --医保返回的单据号 A.ZYID00, --住院ID A.BRID00, --病人ID BRZJE0 , --病人费用总金额 JZJE00 , --记帐金额 GFJE00 , --公费金额 HJJE00 , --合计应收金额 JMJE00 , --减免金额 GRZHZF , --个人医疗帐户支付 TCJJZF , --统筹基金支付 SBGRZH , --商保个人帐户 SBTCJJ , --商保统筹基金 ZFJE00 , --自付金额 ZFJE00+DECODE(G.SFJSDW,'N',GRZHZF,0) , --实际自付金额 PJH000 , --打印的票据流水号 JZRQ00 , --结帐日期 JZSJ00 , --结帐时间 XJFKJE , --现金付款,为了方便打印发票而设置的 ZZFKJE , --转帐付款 JZY000 , --结帐操作员编码 ZWXM00 , --结帐操作员姓名 JMYY00 , --减免原因 B.ZYH000 , --住院号 B.YBZYH0 , --医保生成的住院流水号 decode(nvl(A.YEXM00,'0'),'0',DECODE((SELECT COUNT(1) FROM XT_XTCS00 WHERE NAME00='ZY_SFXSZTQK' AND VALUE0='Y'),0,B.XM0000,decode(F.BRZTQK,null,B.XM0000,B.XM0000||'('||F.BRZTQK||')')),b.XM0000||'(婴儿:'||A.YEXM00||')') XM0000 , --病人姓名 --DECODE((SELECT COUNT(1) FROM XT_XTCS00 WHERE NAME00='ZY_SFXSZTQK' AND VALUE0='Y'),0,B.XM0000,decode(F.BRZTQK,null,B.XM0000,B.XM0000||'('||F.BRZTQK||')')),--B.XM0000 , --病人姓名 B.XB0000 , --性别 B.RYCWH0 , --病人住院床号 B.DQBQ00 , --当前病人住院病区 C.BMMC00 , --当前病人住院病区名称 B.DQKS00 , --当前病人住院科室 D.BMMC00 , --当前病人住院科室名称 G.SFJSDW , A.YSZJE0 , A.ZZZJE0 , A.XJYE00 , A.ZZYE00 , F.YBID00 , --医保保险号 nvl(A.YBZHYE,0)YBZHYE, A.GWYBZ0 , --公务员补助 A.YLZFJE , --乙类自付 A.JJZFE0 , --保健基金支付 A.CFDXJE , --超封顶线 A.FYBJE0 , --非医保费用 A.BCKBCS , --就诊次数 A.YBXJZF , --医保现金支付 B.BRRYCS , --病人入院次数 B.RYRQ00 , --入院日期 B.CYDJRQ , --出院登记日期 (SELECT DYRQ00 FROM ZY_PJSYQK WHERE PLBH00 = 3 AND PJZT00 = '0' AND SFDY00 = 'Y' AND FZPJH0 = A.PJH000 AND ROWNUM = 1) FPDYRQ , --发票打印日期 (SELECT DYSJ00 FROM ZY_PJSYQK WHERE PLBH00 = 3 AND PJZT00 = '0' AND SFDY00 = 'Y' AND FZPJH0 = A.PJH000 AND ROWNUM = 1) FPDYSJ , --发票打印时间 B.FBBH00 , A.SYBXZF , F.YBLB00 , G.YBBHSB , E.XKH000 , A.SBZFE0 , A.BJZFE0 , A.YBYL01 , --医保预留01 民政救助 A.YBYL02 , --医保预留01 计生救助 A.YBYL03 , --医保预留01 残联救助 A.YBYL04 , A.YBYL05 , A.YBYL06 , A.YBYL07 , A.YBYL08 , A.YBYL09 , A.YBYL10 , A.YBYL11 , A.YBYL12 , A.YBYL13 , G.YBMC00 , F.brzjbh , B.cyxj00 , F.BRZTQK , SF_ZY_GetNBPFJE(B.FBBH00,A.BRID00,A.JZDH00,0), --农保赔付金额 A.DBZBM0, A.DBZMC0, A.BZBMLB, nvl(A.YBYL14,0)YBYL14, --共济账户余额 nvl(A.YBYL15,0)YBYL15, --健康账户支付医保费用 nvl(A.YBYL16,0)YBYL16, --健康账户支付非医保费用 nvl(A.YBYL17,0)YBYL17, --减免金额(医保返回不包含统筹里面) nvl(A.YBYL18,0)YBYL18, --民政(医疗救助基金)补助金额 nvl(A.YBYL19,0)YBYL19, nvl(A.YBYL20,0)YBYL20, nvl(A.YBYL21,0)YBYL21, nvl(A.YBYL22,0)YBYL22, nvl(A.YBYL23,0)YBYL23, nvl(A.YBYL24,0)YBYL24, nvl(A.YBYL25,0)YBYL25, nvl(A.YBYL26,0)YBYL26, nvl(A.YBYL27,0)YBYL27, nvl(A.YBYL28,0)YBYL28, nvl(A.YBYL29,0)YBYL29, nvl(A.YBYL30,0)YBYL30, A.JZDH00, (SELECT BDBZ00 FROM ZY_PJSYQK WHERE PLBH00 = 3 AND PJZT00 = '0' AND SFDY00 = 'Y' AND FZPJH0 = A.PJH000 AND ROWNUM = 1) BDBZ00, G.SFXNH0, F.MRN000, F.YBKH00, nvl(A.YHTKJE,0)YHTKJE, --(a.ZFJE00-nvl(a.ybyl19,0)) as GRXJZF, --(a.ZFJE00-SF_ZY_SFYLXM(A.JZDH00,'N')) as GRXJZF, (a.ZFJE00-SF_ZY_SFYLXM('1',A.ZYID00,A.JZDH00,A.ZFJE00)) as GRXJZF, A.YEXM00, B.CYRQ00, (select AA.PJXH00 from ZY_PJSYQK AA Where AA.FZPJH0=A.PJH000 and PJZT00 = '0' and rownum=1)PJXH00, decode(sign(A.XJYE00),-1,abs(A.XJYE00),0) YBJXJ0, --应补交现金 decode(sign(A.ZZFKJE),-1,abs(A.ZZFKJE),0) YBJZZ0, --应补交转账 decode(sign(nvl(A.XJYE00,0)+nvl(A.ZZFKJE,0)),-1,abs(nvl(A.XJYE00,0)+nvl(A.ZZFKJE,0)),0) YBJZE0, --应补交总额 decode(sign(A.XJYE00),-1,0,abs(A.XJYE00))YTHXJ0, --应退还现金 decode(sign(A.ZZFKJE),-1,0,abs(A.ZZFKJE)) YTHZZ0, --应退还转账 decode(sign(nvl(A.XJYE00,0)+nvl(A.ZZFKJE,0)),-1,0,abs(nvl(A.XJYE00,0)+nvl(A.ZZFKJE,0)))YTHZE0, --应退还总额 nvl(A.mxzje0,0)mxzje0,nvl(A.YBYL31,0)YBYL31,nvl(A.YBYL32,0)YBYL32,nvl(A.YBYL33,0)YBYL33,nvl(A.YBYL34,0)YBYL34,nvl(A.YBYL35,0)YBYL35,nvl(A.YBYL36,0)YBYL36,nvl(A.YBYL37,0)YBYL37,nvl(A.YBYL38,0)YBYL38,nvl(A.YBYL39,0)YBYL39, nvl(A.YBYL40,0)YBYL40,nvl(A.YBYL41,0)YBYL41,nvl(A.YBYL42,0)YBYL42,nvl(A.YBYL43,0)YBYL43,nvl(A.YBYL44,0)YBYL44,nvl(A.YBYL45,0)YBYL45,nvl(A.YBYL46,0)YBYL46,nvl(A.YBYL47,0)YBYL47,nvl(A.YBYL48,0)YBYL48,nvl(A.YBYL49,0)YBYL49, SF_SF_GETBRZHYE(1,A.BRID00,2,0)BRZHYE, B.TTZYH0,B.TTZYID,B.DWID00,B.TTLX00, --BM_TYZD00.ZDMC00='CDRG病例类型' 08:住院费用极端值病例 09:未分组病例 --decode(nvl(A.YBSM10,'00'),'00',substrb(SF_ZY_DBZFPDYFS(A.ZYID00,A.JZDH00,A.DBZBM0,B.DBZMC0),1,10),'07','N','08','N','09','N','Q') as BZFPGS, --单病种发票打印方式 Y:新格式 N:旧格式 Q:CDRG发票 decode(A.BZBMLB,'1','N', decode(nvl(A.YBSM10,'00'),'00', substrb(SF_ZY_DBZFPDYFS(A.ZYID00,A.JZDH00,A.DBZBM0,B.DBZMC0),1,10), decode(nvl(trim(A.YBSM09),'无'),'无','N','07','N','08','N','09','N','Q') ) ) as BZFPGS, --单病种发票打印方式 Y:新格式 N:旧格式 Q:CDRG发票 decode(nvl(A.YBSM10,'00'),'00',SF_ZY_DBZFPJE(0,A.ZYID00,A.JZDH00,A.DBZBM0,B.DBZMC0,A.BRZJE0,A.HJJE00,A.TCJJZF,A.GRZHZF,A.ZFJE00,A.YBYL04,A.YBYL05,A.YBYL33,A.YBYL34,A.YBYL35)+nvl(a.YBYL69,0),A.HJJE00) as BZFPJE, --病种发票金额 nvl(A.WXTK00,0)WXTK00,nvl(A.ZFBTK0,0)ZFBTK0, A.YBSM01,A.YBSM02,A.YBSM03,A.YBSM04,A.YBSM05,A.YBSM06,A.YBSM07,A.YBSM08,A.YBSM09,A.YBSM10, A.YBSM11,A.YBSM12,A.YBSM13,A.YBSM14,A.YBSM15,A.YBSM16,nvl(A.YBYL50,0)YBYL50,A.DZZFJE,G.YBZXLB, F.BRDH00,F.BRLXDH,F.BRCSRQ,A.CZYKS0, (select AA.WBID00 from ZY_PJSYQK AA Where AA.FZPJH0=A.PJH000 and PJZT00 = '0' and rownum=1) as WBID00, --外部票据id (select AA.CJLYBM from ZY_PJSYQK AA Where AA.FZPJH0=A.PJH000 and PJZT00 = '0' and rownum=1) as CJLYBM, --厂家来源编码 B.XM0000 as BRSJXM, --病人实际姓名 decode(trim(B.XB0000),'男','1','女','2','9') as BRXBBH, --性别 1:男 2:女 9:未知 G.YBMC00 as YBZXMC, --医保中心名称 (select aa.FBMC00 from BM_BRFBB0 aa where aa.FBBH00=G.FBBH00 and rownum=1) as FBMC00, --费别名称 A.CXJZDH, A.DDLSH0,--订单流水号 A.YBDZZT,--医保对账状态 1:对账成功 0:对账失败 null未对账 A.YBJKID,--医保接口ID A.MXLSH0,--明细流水号 nvl(a.YBYL51,0) as YBYL51 ,--jjzfe0 统筹基金 nvl(a.YBYL52,0) as akc227 ,--医疗费总金额=个人现金支付金额+个人账户支付金额+基金支付总额 nvl(a.YBYL53,0) as bkc102 ,--基金支付总额=统筹基金支付+商保基金支付+公务员医疗补助+精准扶贫医疗叠加+医疗救助基金+其他基金支付+企业补充 nvl(a.YBYL54,0) as bkc040 ,--个人现金支付金额（实付现金） nvl(a.YBYL55,0) as bkc041 ,--个人帐户支付总额=医保帐户支付+健康帐户支付+家庭共济帐户支付 nvl(a.YBYL56,0) as bkc045 ,--其中:统筹基金支付 nvl(a.YBYL57,0) as bkc059 ,--其中:公务员医疗补助(bkc059=bkc059_1+bkc059_2+bkc059_3) nvl(a.YBYL58,0) as bkc052 ,--其中:大病保险基金支付(商业保险支付) nvl(a.YBYL59,0) as bkc062 ,--其中:精准扶贫医疗叠加 nvl(a.YBYL60,0) as bkc060 ,--其中:医疗救助基金 nvl(a.YBYL61,0) as ake173 ,--其中:其他基金支付 nvl(a.YBYL62,0) as ake026 ,--其中:企业补充 nvl(a.YBYL63,0) as bkc011 ,--个人自费（非医保费用） nvl(a.YBYL64,0) as akc087 ,--个人账户余额 nvl(a.YBYL65,0) as aka151 ,--统筹支付医保费用起付标准 nvl(a.YBYL66,0) as aka150 ,--住院次数或就诊次数 nvl(a.YBYL67,0) as bkc612 ,--补助基金支付bkc612=bkc052 大病保险基金支付+bkc059公务员医疗补助+bkc062精准扶贫医疗叠加+bkc060医疗救助基金+ake173 其他基金支付+ake026企业补充+bkc061保健基金支付 nvl(a.YBYL68,0) as bkc064 ,--产前检查费(医保返回) nvl(a.YBYL69,0) as txylfwfy , --特需医疗服务费用(医保未返回) A.JSQYE0 ,--结算前账户总余额 A.JSHYE0 ,--结算后账户总余额 A.BKC001_MC ,--人员待遇类别名称(城乡居民、普通在职人员等等) A.AAE140 ,--险种类型编码:310 城镇职工基本医疗保险 320:公务员医疗补助 380:新型农村合作医疗 390:城乡居民基本医疗保险 410:工伤保险 510:生育保险 520:机关生育 A.AAE140_MC ,--险种类型名称:城乡居民基本医疗保险、城镇职工基本医疗保险等等 A.BKE174 ,--异地就医类型编码:1 省内 2 省外 3 市内 4 市外 A.BKE174_MC ,--异地就医类型名称:(省内、省外、市内、市外) A.BKC620 ,--(本地)成员共济账户支付[bkc620] A.BKC619 ,--(异地)成员共济账户支付[bkc619] A.FKJSLX ,--分开结算类型 空值正常结算 FYCF_ZF:拆分_自费 FYCF_YB:拆分_医保 FYCF_YE:拆分_婴儿 FYCF_MQ:拆分_母亲 A.FKLSH0 ,--分开结算流水号 A.FKJZDH ,--分开结算单号 A.FKJZJE ,--分开结算金额 A.YBBXZE ,--医保报销总额(DLTCJJ+DLZHZF+YBQTZE+DLSYBX+DLJZFP+DLQTJJ+DLQYBC+MZBZJE+DLSBJE+GWBZJE+DLDBJJ) A.DLTCJJ ,--实际医保统筹基金 A.DLZHZF ,--实际医保账户支付 A.YBQTZE ,--医保其它总额 A.DLSYBX ,--商业保险支付(独立于统筹基金金额) A.DLJZFP ,--精准扶贫医疗(独立于统筹基金金额) A.DLQTJJ ,--其他基金支付(独立于统筹基金金额) A.DLQYBC ,--企业补充(独立于统筹基金金额) A.MZBZJE ,--民政补助金额(独立于统筹基金金额) A.DLSBJE ,--商保支付金额(独立于统筹基金金额) A.GWBZJE ,--公务员补助金额(独立于统筹基金金额) A.DLDBJJ ,--大病基金支付金额(独立于统筹基金金额) A.CJBTCE ,--产检检查费（bkc064）超出个人自付金额后需补退的差额 SF_ZY_GETYBBZXMJE(A.JZDH00,'ghhzje') as GHHZJE, --工会互助补助金额 SP_BM_RMB000(A.HJJE00) DXJFJE ,--大写金额 round(SF_ZY_FPHJJE(A.ZYID00,A.JZDH00,'',A.HJJE00,A.HJJE00,A.BRZJE0,A.YBSM09,A.YBSM10,A.BZBMLB,A.DBZBM0,A.YBJKID,''),2) as FPHJJE,--FPHJJE(发票合计金额) SP_BM_RMB000(round(SF_ZY_FPHJJE(A.ZYID00,A.JZDH00,'',A.HJJE00,A.HJJE00,A.BRZJE0,A.YBSM09,A.YBSM10,A.BZBMLB,A.DBZBM0,A.YBJKID,''),2)) as FPHJDX ,--FPHJDX(发票合计大写) (select AA.WBPJLY from ZY_PJSYQK AA Where AA.FZPJH0=A.PJH000 and PJZT00 = '0' and rownum=1) as WBPJLY, --外部票据来源 decode((select AA.WBPJLY from ZY_PJSYQK AA Where AA.FZPJH0=A.PJH000 and PJZT00 = '0' and rownum=1) ,null,'院内发票','02','电子发票','03','纸质发票','04','电子发票作废红票','05','纸质发票作废红票','未定义') as WBLYMC, --外部票据来源名称 (select AA.WBPJDM from ZY_PJSYQK AA Where AA.FZPJH0=A.PJH000 and PJZT00 = '0' and rownum=1) as WBPJDM, --外部票据代码 (select AA.WBPJXH from ZY_PJSYQK AA Where AA.FZPJH0=A.PJH000 and PJZT00 = '0' and rownum=1) as WBPJXH,--外部票据序号 nvl(a.cvl_acct_pay,0) as cvl_acct_pay ,--公务员账户支付 nvl(a.cvl_acct_balc,0) as cvl_acct_balc ,--公务员余额 nvl(a.YBYL11,0) as hl_acct_pay ,--健康账户支付=YBYL11 nvl(a.YBYL12,0) as hl_acct_balc ,--健康账户余额=YBYL12 nvl(nvl(A.acct_mulaid_pay,a.YBYL13),0) as acct_mulaid_pay ,--共济账户支付=YBYL13 nvl(a.YBYL14,0) as fm_acct_balc ,--共济账户余额=YBYL14 nvl(a.pur_acct_pay,0) as pur_acct_pay ,--医疗账户支付 a.insuplc_admdvs ,--参保地行政区划 a.mdtrtarea_admvs , --就医地医保区划 b.sjcyrq, b.sjryrq from ZY_JZB000 A,ZY_BRXXB0 B,BM_BMBM00 C,BM_BMBM00 D,BM_YGBM00 E,BM_BRXXB0 F,IC_YBBRLB G where A.ZYID00 = B.ZYID00 and A.BRID00 = F.BRID00 and F.FBBH00 = G.FBBH00 and F.YBLB00 = G.YBLB00 and B.DQBQ00 = C.BMBH00 and B.DQKS00 = D.BMBH00 and A.JZY000 = E.YGBH00 /