prompt 1. 创建医生员工信息表 V_LIS_YSK create or replace view V_LIS_YSK as select YGBH00 YSID, --医生工号 ZWXM00 YSMC, --姓名 XKH000 XKH000 --胸卡号 from BM_YGBM00 where YGXZ00 like '%0%' or YGXZ00 like '%1%' or YGXZ00 like '%2%'; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% prompt 2. 创建科室、病区字典表 V_LIS_KSK create or replace view V_LIS_KSK as select BMBH00 KSID,--科室或病区代码 BMMC00 KSMC--科室或病区名称 from BM_BMBM00 where BMXZ00 like '%0%' or BMXZ00 like '%1%' or BMXZ00 like '%5%' or BMXZ00 like '%9%'; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% prompt 3.创建视图收费项目/诊疗项目字典表 V_LIS_JCXM create or replace view V_LIS_JCXM as select ZLXMID JCID,--化验项目代码 ZLXMJC JCMC,--化验项目名称 (select sum(NVL(b.TCJE00,c.SFJE00)*b.SFCS00) from XT_ZLSFGX b,BM_YYSFXM c where b.SFXMID=c.SFXMID and b.ZLXMID=a.ZLXMID ) SFJE, ZLXMBH --化验项目编号 （打包项目与明细项目的后三位不一致） from BM_ZLZD00 a where LBBH00='4'; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% prompt 5.创建门诊病人基本信息视图 V_LIS_JBXX_MZ create or replace view V_LIS_JBXX_MZ as select 2 BRLB , --病人类别 1-体检；2-门诊；3-住院；4-婚检；5-其他 (select KMH000 from IC_YHXX00 where BRID00=a.BRID00 and ZT0000='1' and rownum=1) JZKH ,--就诊卡号 a.GHH000 MZH , --门诊号 ' ' ZYH , --住院号 a.BRID00 BRID , --病人ID a.XM0000 BRXM , --病人姓名 decode(a.XB0000,'男','1','女','2','') BRXB, --1-男；2-女；不详时可以为空串或空值 to_date(nvl(a.CSRQ00,'18991231'),'YYYYMMDD') CSRQ, --出生日期 SF_YS_CSRQTONL(a.CSRQ00) BRNL, --病人年龄 '岁' NLLX, --年龄类型 1-岁；2-月；3-周；4-天；5-小时（出生日期有值时，该字段可以不填写）或者是“岁”，“月”，“天” (select BMMC00 from BM_BMBM00 where BMBH00=a.GHKS00 and rownum=1) BRKS, --就诊科室 '' BRBQ, --住院病人病区 '' BRCH, --病人床号 (select ZDMC00 from YS_BRZDXX where GHID00=a.GHID00 and ZZDBZ0='3' and rownum=1) LCZD,---临床诊断 b.KDYSXM KSYS, --开单医师 b.TXM000 BARCODE --条形码 from SF_BRXXB0 a,YJ_YW0000 b,BM_ZLZD00 c where a.GHH000=b.ZYHGHH and b.MZZYBZ='0' and b.ZLXMID=c.ZLXMID and c.LBBH00='4' and ( (b.XMZT00 in ('3','4')) or (XMZT00='2' and b.ZXSL00>0) ) ; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% prompt 6.创建住院病人基本信息视图 V_LIS_JBXX_ZY create or replace view V_LIS_JBXX_ZY as select 3 BRLB , --病人类别 1-体检；2-门诊；3-住院；4-婚检；5-其他 (select KMH000 from IC_YHXX00 where BRID00=a.BRID00 and ZT0000='1' and rownum=1) JZKH ,--就诊卡号 ' ' MZH , --门诊号 a.ZYH000 ZYH , --住院号 a.BRID00 BRID , --病人ID a.XM0000 BRXM , --病人姓名 decode(a.XB0000,'男','1','女','2','') BRXB, --1-男；2-女；不详时可以为空串或空值 to_date(nvl(a.CSRQ00,'18991231'),'YYYYMMDD') CSRQ, --出生日期 SF_YS_CSRQTONL(a.CSRQ00) BRNL, --病人年龄 '岁' NLLX, --年龄类型 1-岁；2-月；3-周；4-天；5-小时（出生日期有值时，该字段可以不填写）或者是“岁”，“月”，“天” (select BMMC00 from BM_BMBM00 where BMBH00=a.DQKS00 and rownum=1) BRKS, --就诊科室 (select BMMC00 from BM_BMBM00 where BMBH00=a.DQBQ00 and rownum=1) BRBQ, --住院病人病区 a.RYCWH0 BRCH, --病人床号 a.RYZDMC LCZD, ---临床诊断 b.KDYSXM KSYS, --开单医师 b.TXM000 BARCODE --条形码 from ZY_BRXXB0 a,YJ_YW0000 b,BM_ZLZD00 c where a.ZYID00=b.ZYGHID and b.MZZYBZ='1' and b.ZLXMID=c.ZLXMID and c.LBBH00='4' ; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% prompt 7.创建门诊病人检查项目申请视图 V_LIS_JCSQD_MZ create or replace view V_LIS_JCSQD_MZ as select b.YJDJH0 JCSQDID,--检查申请单ID '0' JCNO , --序号，没有时设置为“0” 2 BRLB , --病人类别 1-体检；2-门诊；3-住院；4-婚检；5-其他 (select KMH000 from IC_YHXX00 where BRID00=a.BRID00 and ZT0000='1' and rownum=1) JZKH ,--就诊卡号 a.GHH000 MZH , --门诊号 ' ' ZYH , --住院号 a.BRID00 BRID , --病人ID to_date(b.KDRQ00||b.KDSJ00,'YYYYMMDD HH24:MI:SS') SQRQ, --申请时间（长日期类型，包括时间） b.ZLXMID JCID , --检查项目ID（打包项目） b.ZLXMJC JCMC , --检查项目名称（打包项目） b.KDYSXM KSYS, --开单医师 (select ZDMC00 from YS_BRZDXX where GHID00=a.GHID00 and ZZDBZ0='3' and rownum=1) LCZD,---临床诊断 (select BMMC00 from BM_BMBM00 where BMBH00=a.GHKS00 and rownum=1) BRKS, --就诊科室 (select FBMC00 from BM_BRFBB0 where FBBH00=a.FBBH00 and rownum=1) PWAY, --付费方式 ' ' QQXH , --请求序号(患者明细项目的申请序号，没有时为空) null COLTIME , --采样时间(2011-2-23添加，针对HIS打印条码程序) b.TXM000 BARCODE --条形码 from SF_BRXXB0 a,YJ_YW0000 b,BM_ZLZD00 c where a.GHH000=b.ZYHGHH and b.MZZYBZ='0' and b.ZLXMID=c.ZLXMID and c.LBBH00='4' and ( (b.XMZT00 in ('3','4')) or (XMZT00='2' and b.ZXSL00>0) ) ; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% prompt 8.创建住院病人检查项目申请视图 V_LIS_JCSQD_ZY create or replace view V_LIS_JCSQD_ZY as select b.YJDJH0 JCSQDID,--检查申请单ID '0' JCNO , --序号，没有时设置为“0” 2 BRLB , --病人类别 1-体检；2-门诊；3-住院；4-婚检；5-其他 (select KMH000 from IC_YHXX00 where BRID00=a.BRID00 and ZT0000='1' and rownum=1) JZKH ,--就诊卡号 ' ' MZH , --门诊号 a.ZYH000 ZYH , --住院号 a.BRID00 BRID , --病人ID to_date(b.KDRQ00||b.KDSJ00,'YYYYMMDD HH24:MI:SS') SQRQ, --申请时间（长日期类型，包括时间） b.ZLXMID JCID , --检查项目ID（打包项目） b.ZLXMJC JCMC , --检查项目名称（打包项目） b.KDYSXM KSYS, --开单医师 a.RYZDMC LCZD, ---临床诊断 (select BMMC00 from BM_BMBM00 where BMBH00=a.DQKS00 and rownum=1) BRKS, --就诊科室 (select FBMC00 from BM_BRFBB0 where FBBH00=a.FBBH00 and rownum=1) PWAY, --付费方式 ' ' QQXH , --请求序号(患者明细项目的申请序号，没有时为空) null COLTIME , --采样时间(2011-2-23添加，针对HIS打印条码程序) b.TXM000 BARCODE --条形码 from ZY_BRXXB0 a,YJ_YW0000 b,BM_ZLZD00 c where a.ZYID00=b.ZYGHID and b.MZZYBZ='1' and b.ZLXMID=c.ZLXMID and c.LBBH00='4' ; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%