平台数据对照
HIS数据库平台字典表和对照表
local_center_dic：平台字典表
pt_compare 或者 local_compare ：his与平台编码的对照表
建表语句：
-- Create table create table LOCAL_COMPARE ( type VARCHAR2(100) not null, his_code VARCHAR2(100) not null, his_name VARCHAR2(200), center_code VARCHAR2(100), center_name VARCHAR2(200), spell_code VARCHAR2(50), wb_code VARCHAR2(50), oper_code VARCHAR2(6), oper_date DATE default sysdate ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 16K next 8K minextents 1 maxextents unlimited ); -- Add comments to the table comment on table LOCAL_COMPARE is '通用数据对照表'; -- Add comments to the columns comment on column LOCAL_COMPARE.type is '类型:MZDZFP 门诊电子发票 ZYDZFP 住院电子发票'; comment on column LOCAL_COMPARE.his_code is 'HIS编码'; comment on column LOCAL_COMPARE.his_name is 'HIS名称'; comment on column LOCAL_COMPARE.center_code is '中心编码'; comment on column LOCAL_COMPARE.center_name is '中心名称'; comment on column LOCAL_COMPARE.spell_code is '拼音码'; comment on column LOCAL_COMPARE.wb_code is '五笔码'; comment on column LOCAL_COMPARE.oper_code is '操作员'; comment on column LOCAL_COMPARE.oper_date is '操作时间'; -- Create table create table LOCAL_CENTER_DIC ( type VARCHAR2(100) not null, code VARCHAR2(100) not null, name VARCHAR2(200), mark VARCHAR2(200), spell_code VARCHAR2(50), wb_code VARCHAR2(50), input_code VARCHAR2(100), sort_id NUMBER default 0, valid_state VARCHAR2(1) default '1' not null, oper_code VARCHAR2(6), oper_date DATE default sysdate, mark1 VARCHAR2(50) default '99' not null, mark2 VARCHAR2(50), mark3 VARCHAR2(50) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 8K minextents 1 maxextents unlimited ); -- Add comments to the table comment on table LOCAL_CENTER_DIC is '通用对照-数据中心字典表'; -- Add comments to the columns comment on column LOCAL_CENTER_DIC.type is '类型'; comment on column LOCAL_CENTER_DIC.code is '编码'; comment on column LOCAL_CENTER_DIC.name is '名称'; comment on column LOCAL_CENTER_DIC.mark is '备注'; comment on column LOCAL_CENTER_DIC.spell_code is '拼音码'; comment on column LOCAL_CENTER_DIC.wb_code is '五笔码'; comment on column LOCAL_CENTER_DIC.input_code is '输入码'; comment on column LOCAL_CENTER_DIC.sort_id is '顺序号'; comment on column LOCAL_CENTER_DIC.valid_state is '有效性标志 1在用 0停用'; comment on column LOCAL_CENTER_DIC.oper_code is '操作员'; comment on column LOCAL_CENTER_DIC.oper_date is '操作时间'; -- Create/Recreate indexes create index INDEX_LOCAL_CENTER_DIC on LOCAL_CENTER_DIC (TYPE) tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create index INDEX_LOCAL_CENTER_DIC2 on LOCAL_CENTER_DIC (CODE, NAME, SPELL_CODE, WB_CODE) tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );
--HIS对照功能配置的XML :
pt_CommonCompareConfig.xml
--HIS对照函数：
create or replace function fun_get_pf_compare(IN_TYPE varchar2, IN_HIS_CODE varchar2, IN_PF_CODE_DEF varchar2, ai_flag in number) return varchar2 is /*----------------------------------------------------------------------- 过程名称 ：fun_get_pf_code 功能描述 ：根据his编码获取平台编码 IN_TYPE : ；平台编码类型 IN_HIS_CODE ：his编码 IN_PF_CODE_DEF：平台默认编码 ai_flag ： 返回值类型 1编码 2名称 -----------------------------------------------------------------------*/ TEMP_CODE varchar2(200); TEMP_NAME varchar2(200); begin if IN_HIS_CODE is null or IN_HIS_CODE = '' or trim(IN_HIS_CODE) = '' or trim(IN_HIS_CODE) is null then SELECT CC.CENTER_CODE, CC.CENTER_NAME INTO TEMP_CODE, TEMP_NAME FROM LOCAL_COMPARE CC WHERE CC.TYPE = IN_TYPE AND CC.CENTER_CODE = IN_PF_CODE_DEF and rownum=1; else SELECT CC.CENTER_CODE, CC.CENTER_NAME INTO TEMP_CODE, TEMP_NAME FROM LOCAL_COMPARE CC WHERE CC.TYPE = IN_TYPE AND CC.HIS_CODE = IN_HIS_CODE and rownum=1; end if; if TEMP_NAME is null THEN TEMP_CODE := IN_PF_CODE_DEF; TEMP_NAME := ''; END IF; --返回值 if (ai_flag = 1) then return TEMP_CODE; else if (ai_flag = 2) then return TEMP_NAME; else return TEMP_CODE; end if; end if; end fun_get_pf_compare;
--应用对照函数：
fun_get_pf_compare('ICD-10',d.icd_code ,'Z00.001',1) --诊断编码 fun_get_pf_compare('ICD-10',d.icd_code ,'Z00.001',2) --诊断名称
--导入平台字典数据
先建dblink，然后参照以下SQL
insert into local_center_dic SELECT range_code,value_code,value_name,type_,'','','',0,1,'',sysdate,'99','99','99' FROM nhip_mdm.COM_DICT@DRPT where range_code = 'ICD-10'
--诊断模糊匹配
insert into pt_compare select distinct 'ICD-10',a.icd10_code,a.name, (select b.code from local_center_dic b where b.TYPE = 'ICD-10' and (a.icd10_code = b.code or a.name = b.name) and rownum =1), (select b.name from local_center_dic b where b.TYPE = 'ICD-10' and (a.icd10_code = b.code or a.name = b.name) and rownum =1), a.spell_code,a.wb_code,'admin',sysdate from dxt_western_disease a, local_center_dic b where b.TYPE = 'ICD-10' and (a.icd10_code = b.code or a.name = b.name); commit; insert into pt_compare select distinct 'ICD-10',a.icd10_code,a.name, (select b.code from local_center_dic b where b.TYPE = 'ICD-10' and substr(a.icd10_code,1,7) = b.code and rownum =1), (select b.name from local_center_dic b where b.TYPE = 'ICD-10' and substr(a.icd10_code,1,7) = b.code and rownum =1), a.spell_code,a.wb_code,'admin',sysdate from dxt_western_disease a ,local_center_dic b where a.icd10_code not in (select his_code from pt_compare where type = 'ICD-10') and substr(a.icd10_code,1,7) = b.code; commit; insert into pt_compare select distinct 'ICD-10',a.icd10_code,a.name, (select b.code from local_center_dic b where b.TYPE = 'ICD-10' and substr(a.icd10_code,1,7) = substr(b.code,1,7) and rownum =1), (select b.name from local_center_dic b where b.TYPE = 'ICD-10' and substr(a.icd10_code,1,7) = substr(b.code,1,7) and rownum =1), a.spell_code,a.wb_code,'admin',sysdate from dxt_western_disease a ,local_center_dic b where a.icd10_code not in (select his_code from pt_compare where type = 'ICD-10') and substr(a.icd10_code,1,7) = substr(b.code,1,7) ; commit; insert into pt_compare select distinct 'ICD-10',a.icd10_code,a.name, (select b.code from local_center_dic b where b.TYPE = 'ICD-10' and substr(a.icd10_code,1,6) = substr(b.code,1,6) and rownum =1), (select b.name from local_center_dic b where b.TYPE = 'ICD-10' and substr(a.icd10_code,1,6) = substr(b.code,1,6) and rownum =1), a.spell_code,a.wb_code,'admin',sysdate from dxt_western_disease a ,local_center_dic b where a.icd10_code not in (select his_code from pt_compare where type = 'ICD-10') and substr(a.icd10_code,1,6) = substr(b.code,1,6) ; commit; insert into pt_compare select distinct 'ICD-10',a.icd10_code,a.name, (select b.code from local_center_dic b where b.TYPE = 'ICD-10' and substr(a.icd10_code,1,5) = substr(b.code,1,5) and rownum =1), (select b.name from local_center_dic b where b.TYPE = 'ICD-10' and substr(a.icd10_code,1,5) = substr(b.code,1,5) and rownum =1), a.spell_code,a.wb_code,'admin',sysdate from dxt_western_disease a ,local_center_dic b where a.icd10_code not in (select his_code from pt_compare where type = 'ICD-10') and substr(a.icd10_code,1,5) = substr(b.code,1,5) ; commit; insert into pt_compare select distinct 'ICD-10',a.icd10_code,a.name, (select b.code from local_center_dic b where b.TYPE = 'ICD-10' and substr(a.icd10_code,1,4) = substr(b.code,1,4) and rownum =1), (select b.name from local_center_dic b where b.TYPE = 'ICD-10' and substr(a.icd10_code,1,4) = substr(b.code,1,4) and rownum =1), a.spell_code,a.wb_code,'admin',sysdate from dxt_western_disease a ,local_center_dic b where a.icd10_code not in (select his_code from pt_compare where type = 'ICD-10') and substr(a.icd10_code,1,4) = substr(b.code,1,4) ; commit; insert into pt_compare select distinct 'ICD-10',a.icd10_code,a.name, (select b.code from local_center_dic b where b.TYPE = 'ICD-10' and substr(a.icd10_code,1,3) = substr(b.code,1,3) and rownum =1), (select b.name from local_center_dic b where b.TYPE = 'ICD-10' and substr(a.icd10_code,1,3) = substr(b.code,1,3) and rownum =1), a.spell_code,a.wb_code,'admin',sysdate from dxt_western_disease a ,local_center_dic b where a.icd10_code not in (select his_code from pt_compare where type = 'ICD-10') and substr(a.icd10_code,1,3) = substr(b.code,1,3); commit; --未匹配的诊断查询 select t.* from dxt_western_disease t where t.icd10_code not in (select his_code from pt_compare where type = 'ICD-10')
