CREATE VIEW SD_HOSPITAL.VW_SF_YJYWMX ( YJDJH0, YJFZH0, BRID00, GHID00, GHH000, BRXM00, XB0000, CSRQ00, KDKSBH, BMMC00, KDYYID, YJKS00, YJKSMC, ZXR000, ZXRXM0, KDYSBH, KDYSXM, FPXMID, BH0000, FPXMMC, SFDM00, SFXMMC, XMGG00, XMDJ00, XMDW00, XMSL00, AMOUNT, ID0000, ZFJE00, JZJE00, GFJE00, QZFJE0, JMJE00, YSFJE0, ZFBL00, JJSJ00, CXBZ00, ZLDCCF, KDRQ00, KDSJ00, YSZJHM, CXDJH0, ZLXMJC, TSBZBH, TSBZMC, JSXM00, CFDGS0, JZBZ00, JZZTMC, TCID00, TCXMMC, YZCFID, YBXMBH, ZFFSBZ, FPJSFZ, FPFZBH, ZFCFBZ ) AS select a.YJDJH0, --医技单据号 a.YJFZH0, --医技分组号 a.BRID00, --病人ID a.ZYGHID as GHID00, --病人挂号ID a.ZYHGHH as GHH000, --病人挂号号 a.BRXM00, --病人姓名 a.XB0000, --病人性别 a.CSRQ00, --出生日期 a.KDKSBH, --开单科室编号 (select BMMC00 from BM_BMBM00 where BMBH00 = a.KDKSBH) as BMMC00, --开单科室名称 (select YYID00 from BM_BMBM00 where BMBH00 = a.KDKSBH) as KDYYID, --开单科室所属医院ID a.YJKSBH as YJKS00, --医技科室编码 (select BMMC00 from BM_BMBM00 where BMBH00 = a.YJKSBH) as YJKSMC, --医技科室名称 a.ZXR000, --执行人编号 a.ZXRXM0, --执行人姓名 a.KDYS00 as KDYSBH, --开单医生编号 a.KDYSXM, --开单医生姓名 e.FPXMID, --发票项目ID e.BH0000, --发票项目编号 e.XMMC00 as FPXMMC, --发票项目名称 d.SFXMID as SFDM00, --收费代码 d.XMMC00 as SFXMMC, --收费项目名称 '次' as XMGG00, --规格 decode(nvl(c.JMJE00,0),0,decode(nvl(c.JJJE00,0),0,nvl(d.SFJE00,0),c.JJJE00),nvl(c.JJJE00,0)) as XMDJ00, --如果有减免,则还是按JJJE00算单价 nvl(d.DW0000,'次') as XMDW00, --单位 c.SFCS00*c.SL0000 as XMSL00, --数量 --round(decode(nvl(C.JJJE00,0)*C.SFCS00*C.SL0000,0,nvl(D.SFJE00,0)*C.SFCS00*C.SL0000,nvl(C.JJJE00,0)*C.SFCS00*C.SL0000),2), --金额 --如果有减免，按上一种算法，否则按下一种算法 decode(nvl(c.JMJE00,0),0,round(decode(nvl(c.JJJE00,0)*c.SFCS00*c.SL0000,0,nvl(d.SFJE00,0)*c.SFCS00*c.SL0000,nvl(c.JJJE00,0)*c.SFCS00*c.SL0000),2),round(nvl(c.JJJE00,0)*c.SFCS00*c.SL0000,2)) as AMOUNT, c.ID0000, --ID0000 c.ZFJE00, --自费金额 c.JZJE00, --记帐金额 c.GFJE00, --公费金额 c.QZFJE0, --首自付金额 c.JMJE00, --减免金额 c.YSFJE0, --原收费金额 to_char(c.ZFBL00*100) as ZFBL00, --自付比例 substr(SF_SF_GETRQSJ(a.KDRQ00,a.KDSJ00),1,50) as JJSJ00, --计价时间 c.CXBZ00, --冲销标志 'Z'正常,'-'冲销记录,'+'被冲记录 a.ZLDCCF, -- a.KDRQ00, -- a.KDSJ00, -- (select YSZJHM from bm_ygbm00 where ygbh00=A.KDYS00 and rownum=1) as YSZJHM, --开单医生证件号码 a.CXDJH0, --冲销单据号 a.ZLXMJC, --诊疗项目名称 a.BH0000 as TSBZBH, --特殊病种编号 (select MC0000 from BM_TSBZB0 where BH0000=a.BH0000 and rownum=1) as TSBZMC, --特殊病种名称 d.JSXM00, --加收项目 '9' as CFDGS0, --处方单格式 '0' as JZBZ00, --记账标识 '未记账' as JZZTMC, --记账状态 nvl(a.TCID00,0) as TCID00, --套餐ID SF_BRTCXM.TCID00 (select TCXMMC from SF_BRTCXM where TCID00=a.TCID00) as TCXMMC, --套餐项目名称 a.YZCFID as YZCFID, -- substr(SF_XT_GETBRYBXMBH(a.BRID00,c.SFXMID,'N'),1,50) as YBXMBH, --医保项目编号 --decode(a.SFZF00,'1','是','否') as ZFFSBZ, --自费发送标志:0否,1是 decode(a.ZFFSBZ,'Y','是','否') as ZFFSBZ, --自费发送标志:0否,1是 SF_XT_GETFPJSFZ(e.FPXMID,'1','0') as FPJSFZ, --发票结算分组 SF_XT_GETFPJSFZ(e.FPXMID,'2','0') as FPFZBH, --发票分组编号 nvl((select max(ZFCFBZ) from SF_BRXXB0 where BRID00=a.BRID00 and GHID00=a.ZYGHID and rownum=1),'0') as ZFCFBZ --自费处方标志, 1是0否 from YJ_YW0000 a,YJ_YWJJ00 c,BM_YYSFXM d,BM_FPXM00 e where a.YJDJH0 = c.YJDJH0 and c.SFXMID = d.SFXMID and d.MZFPID = e.FPXMID and a.MZZYBZ = '0' and a.XMZT00 = '1' --已计价 /