-- Start of DDL Script for View SD_HOSPITAL.VW_ZY_FP0000 -- Generated 12-七月-2022 15:12:41 from SD_HOSPITAL@FW2 CREATE OR REPLACE VIEW 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 ) 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 --大写金额 SP_BM_RMB000(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)) DXJFJE --大写金额 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 / -- End of DDL Script for View SD_HOSPITAL.VW_ZY_FP0000