sql.eureka_package_body_oracle.sql Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of aiw-i2b2-etl Show documentation
Show all versions of aiw-i2b2-etl Show documentation
AIW i2b2 ETL is a Protempa query results handler for loading data and
abstractions into i2b2.
---
-- #%L
-- AIW i2b2 ETL
-- %%
-- Copyright (C) 2012 - 2015 Emory University
-- %%
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- #L%
---
CREATE OR REPLACE PACKAGE BODY EUREKA
AS
PROCEDURE EK_INS_PROVIDER_FROMTEMP(
tempProviderTableName IN VARCHAR,
upload_id IN NUMBER)
IS
BEGIN
execute immediate '
MERGE /*+ append nologging parallel */ INTO provider_dimension
USING ' || tempProviderTableName || ' temp
ON (
temp.provider_path = provider_dimension.provider_path
)
WHEN MATCHED THEN
UPDATE SET provider_id=temp.provider_id,
name_char = temp.name_char,
provider_blob = temp.provider_blob,
update_date = temp.update_date,
download_date = temp.download_date,
import_date = sysdate,
sourcesystem_cd = temp.sourcesystem_cd,
upload_id = ' || upload_id || '
WHERE nvl(temp.update_date,to_date(''19000101'',''YYYYMMDD'')) >= nvl(provider_dimension.update_date,to_date(''19000101'',''YYYYMMDD''))
DELETE where temp.delete_date is not null
WHEN NOT MATCHED THEN
INSERT (
provider_id,
provider_path,
name_char,
provider_blob,
update_date,
download_date,
import_date,
sourcesystem_cd,
upload_id)
VALUES (
temp.provider_id,
temp.provider_path,
temp.name_char,
temp.provider_blob,
temp.update_date,
temp.download_date,
sysdate,
temp.sourcesystem_cd, '
|| upload_id ||
') where temp.delete_date is null';
dbms_stats.gather_table_stats(USER, 'provider_dimension');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END EK_INS_PROVIDER_FROMTEMP;
PROCEDURE EK_INS_PATIENT_FROMTEMP(
tempTableName IN VARCHAR,
upload_id IN NUMBER)
IS
maxPatientNum NUMBER;
BEGIN
LOCK TABLE patient_mapping IN EXCLUSIVE MODE NOWAIT;
-- Create new patient(patient_mapping) if temp table patient_ide does not exists
-- in patient_mapping table.
execute immediate '
insert /*+ append nologging parallel */ into patient_mapping (
patient_ide,
patient_ide_source,
patient_num,
patient_ide_status,
upload_id,
project_id)
select temp.patient_id,
temp.patient_id_source,
temp.patient_id,
''A'',
'|| upload_id ||',
''@''
from ' || tempTableName || ' temp
WHERE NOT EXISTS (
select patient_ide
from patient_mapping pm
where pm.patient_ide = temp.patient_id
and pm.patient_ide_source = temp.patient_id_source)
and temp.patient_id_source = ''HIVE''
AND temp.delete_date is null';
COMMIT;
-- update patient_num for temp table
execute immediate '
MERGE /*+ append nologging parallel */ INTO ' || tempTableName || '
USING patient_mapping
ON (
patient_mapping.patient_ide = '|| tempTableName ||'.patient_id
and patient_mapping.patient_ide_source = '|| tempTableName ||'.patient_id_source
)
WHEN MATCHED THEN
UPDATE SET patient_num = patient_mapping.patient_num';
COMMIT;
execute immediate '
MERGE /*+ append nologging parallel */ INTO patient_dimension
USING ' || tempTableName || ' temp
ON (
temp.patient_num = patient_dimension.patient_num
)
WHEN MATCHED THEN
UPDATE SET vital_status_cd = temp.vital_status_cd,
birth_date = temp.birth_date,
death_date = temp.death_date,
sex_cd = temp.sex_cd,
age_in_years_num = temp.age_in_years_num,
language_cd = temp.language_cd,
race_cd = temp.race_cd,
marital_status_cd = temp.marital_status_cd,
religion_cd = temp.religion_cd,
zip_cd = temp.zip_cd,
statecityzip_path = temp.statecityzip_path,
patient_blob = temp.patient_blob,
update_date = temp.update_date,
download_date = temp.download_date,
import_date = sysdate,
sourcesystem_cd = temp.sourcesystem_cd,
upload_id = ' || upload_id || '
WHERE nvl(temp.update_date,to_date(''19000101'',''YYYYMMDD'')) >= nvl(patient_dimension.update_date,to_date(''19000101'',''YYYYMMDD''))
DELETE where temp.delete_date is not null
WHEN NOT MATCHED THEN
INSERT (
PATIENT_NUM,
VITAL_STATUS_CD,
BIRTH_DATE,
DEATH_DATE,
SEX_CD,
AGE_IN_YEARS_NUM,
LANGUAGE_CD,
RACE_CD,
MARITAL_STATUS_CD,
RELIGION_CD,
ZIP_CD,
STATECITYZIP_PATH,
PATIENT_BLOB,
UPDATE_DATE,
DOWNLOAD_DATE,
IMPORT_DATE,
SOURCESYSTEM_CD,
UPLOAD_ID)
VALUES(temp.patient_num,
temp.VITAL_STATUS_CD,
temp.BIRTH_DATE,
temp.DEATH_DATE,
temp.SEX_CD,
temp.AGE_IN_YEARS_NUM,
temp.LANGUAGE_CD,
temp.RACE_CD,
temp.MARITAL_STATUS_CD,
temp.RELIGION_CD,
temp.ZIP_CD,
temp.STATECITYZIP_PATH,
temp.PATIENT_BLOB,
temp.update_date,
temp.download_date,
sysdate,
temp.sourcesystem_cd,
'|| UPLOAD_ID || ')
WHERE temp.patient_num IS NOT NULL and temp.delete_date is null';
dbms_stats.gather_table_stats(USER, 'patient_dimension');
EXCEPTION
WHEN OTHERS THEN
rollback;
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END EK_INS_PATIENT_FROMTEMP;
PROCEDURE EK_INS_CONCEPT_FROMTEMP(
tempConceptTableName IN VARCHAR,
upload_id IN NUMBER)
IS
BEGIN
execute immediate '
MERGE /*+ append nologging parallel */ INTO concept_dimension
USING ' || tempConceptTableName || ' temp
ON (
temp.concept_path = concept_dimension.concept_path
)
WHEN MATCHED THEN
UPDATE SET concept_cd = temp.concept_cd,
name_char = temp.name_char,
concept_blob = temp.concept_blob,
update_date = temp.update_date,
download_date = temp.DOWNLOAD_DATE,
import_date = sysdate,
sourcesystem_cd = temp.SOURCESYSTEM_CD,
upload_id = ' || upload_id || '
WHERE nvl(temp.update_date,to_date(''19000101'',''YYYYMMDD'')) >= nvl(concept_dimension.update_date,to_date(''19000101'',''YYYYMMDD''))
WHEN NOT MATCHED THEN
INSERT (
concept_cd,
concept_path,
name_char,
concept_blob,
update_date,
download_date,
import_date,
sourcesystem_cd,
upload_id)
VALUES (
temp.concept_cd,
temp.concept_path,
temp.name_char,
temp.concept_blob,
temp.update_date,
temp.download_date,
sysdate,
temp.sourcesystem_cd, '
|| UPLOAD_ID ||
')';
dbms_stats.gather_table_stats(USER, 'concept_dimension');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END EK_INS_CONCEPT_FROMTEMP;
PROCEDURE EK_INS_MODIFIER_FROMTEMP(
tempModifierTableName IN VARCHAR,
upload_id IN NUMBER)
IS
BEGIN
execute immediate '
MERGE /*+ append nologging parallel */ INTO modifier_dimension
USING ' || tempModifierTableName || ' temp
ON (
temp.modifier_path = modifier_dimension.modifier_path
)
WHEN MATCHED THEN
UPDATE SET modifier_cd = temp.modifier_cd,
name_char = temp.name_char,
modifier_blob = temp.modifier_blob,
update_date = temp.update_date,
download_date = temp.download_date,
import_date = sysdate,
sourcesystem_cd = temp.sourcesystem_cd,
upload_id = ' || upload_id || '
WHERE nvl(temp.update_date,to_date(''19000101'',''YYYYMMDD'')) >= nvl(modifier_dimension.update_date,to_date(''19000101'',''YYYYMMDD''))
WHEN NOT MATCHED THEN
INSERT (
modifier_cd,
modifier_path,
name_char,
modifier_blob,
update_date,
download_date,
import_date,
sourcesystem_cd,
upload_id)
VALUES (
temp.modifier_cd,
temp.modifier_path,
temp.name_char,
temp.modifier_blob,
temp.update_date,
temp.download_date,
sysdate,
temp.sourcesystem_cd, '
|| upload_id ||
')';
dbms_stats.gather_table_stats(USER, 'modifier_dimension');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END EK_INS_MODIFIER_FROMTEMP;
PROCEDURE EK_INS_ENC_VISIT_FROMTEMP(
tempTableName IN VARCHAR,
upload_id IN NUMBER)
IS
BEGIN
LOCK TABLE encounter_mapping IN EXCLUSIVE MODE NOWAIT ;
--Create new encounter(encounter_mapping) if temp table encounter_ide does not exists
-- in encounter_mapping table. -- jk added project id
EXECUTE immediate '
insert /*+ append nologging parallel */ into encounter_mapping (
encounter_ide,
encounter_ide_source,
encounter_num,
patient_ide,
patient_ide_source,
encounter_ide_status,
project_id,upload_id)
select temp.encounter_id,
temp.encounter_id_source,
temp.encounter_id,
temp.patient_id,
temp.patient_id_source,
''A'',
''@'' project_id,
' || upload_id || '
from ' || tempTableName || ' temp
where not exists (
select em.encounter_ide
from encounter_mapping em
where em.encounter_ide = temp.encounter_id
and em.encounter_ide_source = temp.encounter_id_source
and em.patient_ide = temp.patient_id
and em.patient_ide_source = temp.patient_id_source)
and encounter_id_source = ''HIVE''
and temp.delete_date is null';
COMMIT;
-- update encounter_num for temp table
EXECUTE immediate '
MERGE /*+ append nologging parallel */ INTO ' || tempTableName || ' tempTableName
USING encounter_mapping em
ON (
em.encounter_ide = tempTableName.encounter_id
and em.encounter_ide_source = tempTableName.encounter_id_source
and nvl(em.patient_ide_source,'''') = nvl(tempTableName.patient_id_source,'''')
and nvl(em.patient_ide,'''') = nvl(tempTableName.patient_id,'''')
)
WHEN MATCHED THEN
UPDATE SET encounter_num = em.encounter_num' ;
COMMIT;
EXECUTE immediate '
MERGE /*+ append nologging parallel */ INTO visit_dimension
USING ' || tempTableName || ' temp
ON (
temp.encounter_num = visit_dimension.encounter_num
)
WHEN MATCHED THEN
UPDATE SET start_date = temp.start_date,
end_date = temp.end_date,
inout_cd = temp.inout_cd,
location_cd = temp.location_cd,
visit_blob = temp.visit_blob,
update_date = temp.update_date,
download_date = temp.download_date,
import_date = sysdate,
sourcesystem_cd = temp.sourcesystem_cd,
upload_id = ' || upload_id || ',
length_of_stay = temp.length_of_stay
WHERE nvl(temp.update_date,to_date(''19000101'',''YYYYMMDD'')) >= nvl(visit_dimension.update_date,to_date(''19000101'',''YYYYMMDD''))
DELETE where temp.delete_date is not null';
COMMIT;
-- jk: added project_id='@' to WHERE clause... need to support projects...
EXECUTE immediate '
insert /*+ append nologging parallel */ into visit_dimension (
encounter_num,
patient_num,
START_DATE,
END_DATE,
INOUT_CD,
LOCATION_CD,
VISIT_BLOB,
UPDATE_DATE,
DOWNLOAD_DATE,
IMPORT_DATE,
SOURCESYSTEM_CD,
UPLOAD_ID,
LENGTH_OF_STAY)
select temp.encounter_num,
pm.patient_num,
temp.START_DATE,
temp.END_DATE,
temp.INOUT_CD,
temp.LOCATION_CD,
temp.VISIT_BLOB,
temp.update_date,
temp.download_date,
sysdate,
temp.sourcesystem_cd, '
|| upload_id || ',
temp.LENGTH_OF_STAY
from ' || tempTableName || ' temp, patient_mapping pm
where temp.encounter_num is not null
and temp.encounter_num not in (
select encounter_num
from visit_dimension vd
)
and pm.patient_ide = temp.patient_id
and pm.patient_ide_source = temp.patient_id_source
and temp.delete_date is null';
dbms_stats.gather_table_stats(USER, 'visit_dimension');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
raise_application_error ( -20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM ) ;
END EK_INS_ENC_VISIT_FROMTEMP ;
PROCEDURE EK_UPDATE_OBSERVATION_FACT(
upload_temptable_name IN VARCHAR,
upload_temptable_name_c IN VARCHAR,
upload_id IN NUMBER,
appendFlag IN NUMBER)
IS
BEGIN
EXECUTE IMMEDIATE 'INSERT /*+ append nologging parallel */ INTO ' || upload_temptable_name_c || '
(SELECT em.encounter_num,
temp.encounter_id,
temp.encounter_id_source,
temp.concept_cd,
pm.patient_num,
temp.patient_id,
temp.patient_id_source,
temp.provider_id,
temp.start_date,
temp.modifier_cd,
temp.instance_num,
temp.valtype_cd,
temp.tval_char,
temp.nval_num,
temp.valueflag_cd,
temp.quantity_num,
temp.confidence_num,
temp.observation_blob,
temp.units_cd,
temp.end_date,
temp.location_cd,
temp.update_date,
temp.download_date,
temp.import_date,
temp.sourcesystem_cd,
temp.upload_id,
temp.delete_date
FROM ' || upload_temptable_name || ' temp
LEFT OUTER JOIN encounter_mapping em
ON (em.encounter_ide = temp.encounter_id
and em.encounter_ide_source = temp.encounter_id_source
and em.project_id=''@'')
LEFT OUTER JOIN patient_mapping pm
ON (pm.patient_ide = temp.patient_id
and pm.patient_ide_source = temp.patient_id_source
and pm.project_id=''@''))';
COMMIT;
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || upload_temptable_name;
-- if the append is true, then do the update else do insert all
IF ( appendFlag = 0 ) THEN
--Transfer all rows from temp_obsfact to observation_fact
EXECUTE immediate
'INSERT /*+ append nologging parallel */ ALL INTO observation_fact(encounter_num,concept_cd, patient_num,provider_id, start_date,modifier_cd,instance_num,valtype_cd,tval_char,nval_num,valueflag_cd,
quantity_num,confidence_num,observation_blob,units_cd,end_date,location_cd, update_date,download_date,import_date,sourcesystem_cd,
upload_id)
SELECT encounter_num,concept_cd, patient_num,provider_id, start_date,modifier_cd,instance_num,valtype_cd,tval_char,nval_num,valueflag_cd,
quantity_num,confidence_num,observation_blob,units_cd,end_date,location_cd, update_date,download_date,sysdate import_date,sourcesystem_cd,
temp.upload_id
FROM ' || upload_temptable_name_c || ' temp
where temp.patient_num is not null and temp.encounter_num is not null and temp.delete_date is null'
;
ELSE
EXECUTE immediate
'MERGE /*+ append nologging parallel */ INTO observation_fact USING ' || upload_temptable_name_c || ' temp ON
(temp.encounter_num = observation_fact.encounter_num
and temp.patient_num = observation_fact.patient_num
and temp.concept_cd = observation_fact.concept_cd
and temp.start_date = observation_fact.start_date
and temp.provider_id = observation_fact.provider_id
and temp.modifier_cd = observation_fact.modifier_cd
and temp.instance_num = observation_fact.instance_num)
when matched then
update set valtype_cd = temp.valtype_cd,
tval_char=temp.tval_char,
nval_num = temp.nval_num,
valueflag_cd=temp.valueflag_cd,
quantity_num=temp.quantity_num,
confidence_num=temp.confidence_num,
observation_blob =temp.observation_blob,
units_cd=temp.units_cd,
end_date=temp.end_date,
location_cd =temp.location_cd,
update_date=temp.update_date,
download_date =temp.download_date,
import_date=temp.import_date,
sourcesystem_cd =temp.sourcesystem_cd,
upload_id = temp.upload_id
where nvl(observation_fact.update_date,to_date(''19000101'',''YYYYMMDD'')) <= nvl(temp.update_date,to_date(''19000101'',''YYYYMMDD''))
delete where delete_date is not null
when not matched then
insert (encounter_num,
concept_cd,
patient_num,
provider_id,
start_date,
modifier_cd,
instance_num,
valtype_cd,
tval_char,
nval_num,
valueflag_cd,
quantity_num,
confidence_num,
observation_blob,
units_cd,
end_date,
location_cd,
update_date,
download_date,
import_date,
sourcesystem_cd,
upload_id)
values (temp.encounter_num,
temp.concept_cd,
temp.patient_num,
temp.provider_id,
temp.start_date,
temp.modifier_cd,
temp.instance_num,
temp.valtype_cd,
temp.tval_char,
temp.nval_num,
temp.valueflag_cd,
temp.quantity_num,
temp.confidence_num,
temp.observation_blob,
temp.units_cd,
temp.end_date,
temp.location_cd,
temp.update_date,
temp.download_date,
temp.import_date,
temp.sourcesystem_cd,
temp.upload_id)
where temp.patient_num is not null and temp.encounter_num is not null and
temp.delete_date is null'
;
END IF ;
dbms_stats.gather_table_stats(USER, 'OBSERVATION_FACT');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM ) ;
END EK_UPDATE_OBSERVATION_FACT ;
PROCEDURE EK_INSERT_EID_MAP_FROMTEMP (
tempEidTableName IN VARCHAR,
upload_id IN NUMBER)
IS
existingEncounterNum varchar2(32);
maxEncounterNum number;
TYPE distinctEIdCurTyp IS REF CURSOR;
distinctEidCur distinctEIdCurTyp;
sql_stmt varchar2(400);
disEncounterId varchar2(100);
disEncounterIdSource varchar2(100);
patientMapId varchar2(200);
patientMapIdSource varchar2(50);
BEGIN
LOCK TABLE encounter_mapping IN EXCLUSIVE MODE NOWAIT;
select max(encounter_num) into maxEncounterNum from encounter_mapping;
if maxEncounterNum is null then
maxEncounterNum := 0;
end if;
sql_stmt := 'SELECT distinct encounter_id, encounter_id_source, patient_map_id, patient_map_id_source from ' || tempEidTableName ||
' where delete_Date is null';
OPEN distinctEidCur FOR sql_stmt ;
LOOP
FETCH distinctEidCur INTO disEncounterId, disEncounterIdSource, patientMapId, patientMapIdSource;
EXIT WHEN distinctEidCur%NOTFOUND;
if disEncounterIdSource = 'HIVE' THEN
BEGIN
--check if hive number exist, if so assign that number to reset of map_id's within that pid
select encounter_num into existingEncounterNum
from encounter_mapping
where encounter_num = disEncounterId
and encounter_ide_source = 'HIVE';
EXCEPTION
when NO_DATA_FOUND THEN
existingEncounterNum := null;
END;
if existingEncounterNum is not null then
execute immediate '
update ' || tempEidTableName ||'
set encounter_num = encounter_id,
process_status_flag = ''P''
where encounter_id = :x
and encounter_id_source = ''HIVE''
and not exists (
select 1
from encounter_mapping em
where em.encounter_ide = encounter_map_id
and em.encounter_ide_source = encounter_map_id_source
)'
using disEncounterId;
else
-- generate new patient_num i.e. take max(_num) + 1
if maxEncounterNum < disEncounterId then
maxEncounterNum := disEncounterId;
end if ;
execute immediate '
update ' || tempEidTableName ||'
set encounter_num = encounter_id,
process_status_flag = ''P''
where encounter_id = :x
and encounter_id_source = ''HIVE''
and not exists (
select 1
from encounter_mapping em
where em.encounter_ide = encounter_map_id
and em.encounter_ide_source = encounter_map_id_source
)'
using disEncounterId;
end if;
else
begin
select encounter_num into existingEncounterNum
from encounter_mapping
where encounter_ide = disEncounterId
and encounter_ide_source = disEncounterIdSource;
-- test if record fetched.
EXCEPTION
WHEN NO_DATA_FOUND THEN
existingEncounterNum := null;
end;
if existingEncounterNum is not null then
execute immediate '
update ' || tempEidTableName ||'
set encounter_num = :x,
process_status_flag = ''P''
where encounter_id = :y
and encounter_id_source = :z
and not exists (
select 1
from encounter_mapping em
where em.encounter_ide = encounter_map_id
and em.encounter_ide_source = encounter_map_id_source
)'
using existingEncounterNum,
disEncounterId,
disEncounterIdSource;
else
maxEncounterNum := maxEncounterNum + 1 ;
--TODO : add update colunn
execute immediate '
insert into ' || tempEidTableName ||' (
encounter_map_id,
encounter_map_id_source,
encounter_id,
encounter_id_source,
encounter_num,
patient_map_id,
patient_map_id_source,
process_status_flag,
encounter_map_id_status,
update_date,
download_date,
import_date,
sourcesystem_cd)
values(
:x,
''HIVE'',
:y,
''HIVE'',
:z,
:w,
:v,
''P'',
''A'',
sysdate,
sysdate,
sysdate,
''edu.harvard.i2b2.crc''
)'
using maxEncounterNum,
maxEncounterNum,
maxEncounterNum,
patientMapId,
patientMapIdSource;
execute immediate '
update ' || tempEidTableName ||'
set encounter_num = :x ,
process_status_flag = ''P''
where encounter_id = :y
and encounter_id_source = :z
and not exists (
select 1
from encounter_mapping em
where em.encounter_ide = encounter_map_id
and em.encounter_ide_source = encounter_map_id_source
)'
using maxEncounterNum, disEncounterId, disEncounterIdSource;
end if;
end if;
END LOOP;
CLOSE distinctEidCur ;
COMMIT;
-- do the mapping update if the update date is old
execute immediate '
merge /*+ append nologging parallel */ into encounter_mapping
using ' || tempEidTableName ||' temp
on (temp.encounter_map_id = encounter_mapping.ENCOUNTER_IDE
and temp.encounter_map_id_source = encounter_mapping.ENCOUNTER_IDE_SOURCE
)
when matched then
update
set ENCOUNTER_NUM = temp.encounter_id,
patient_ide = temp.patient_map_id,
patient_ide_source = temp.patient_map_id_source,
encounter_ide_status = temp.encounter_map_id_status,
update_date = temp.update_date,
download_date = temp.download_date,
import_date = sysdate,
sourcesystem_cd = temp.sourcesystem_cd,
upload_id = ' || upload_id ||'
where temp.encounter_id_source = ''HIVE''
and temp.process_status_flag is null
and nvl(encounter_mapping.update_date, to_date(''01-JAN-1900'',''DD-MON-YYYY'')) <= nvl(temp.update_date, to_date(''01-JAN-1900'',''DD-MON-YYYY''))
delete where temp.delete_date is not null';
-- insert new mapping records i.e flagged P -- jk: added project_id
execute immediate '
insert /*+ append nologging parallel */ into encounter_mapping (
encounter_ide,
encounter_ide_source,
encounter_ide_status,
encounter_num,
patient_ide,
patient_ide_source,
update_date,
download_date,
import_date,
sourcesystem_cd,
project_id,
upload_id)
select encounter_map_id,
encounter_map_id_source,
encounter_map_id_status,
encounter_num,
patient_map_id,
patient_map_id_source,
update_date,
download_date,
sysdate,
sourcesystem_cd,
''@'' project_id,'
|| upload_id || '
from ' || tempEidTableName || '
where process_status_flag = ''P''
and delete_date is null';
dbms_stats.gather_table_stats(USER, 'encounter_mapping');
EXCEPTION
WHEN OTHERS THEN
if distinctEidCur%isopen then
close distinctEidCur;
end if;
rollback;
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END EK_INSERT_EID_MAP_FROMTEMP;
PROCEDURE EK_INSERT_PID_MAP_FROMTEMP (
tempPidTableName IN VARCHAR,
upload_id IN NUMBER)
IS
existingPatientNum varchar2(32);
maxPatientNum number;
TYPE distinctPidCurTyp IS REF CURSOR;
distinctPidCur distinctPidCurTyp;
sql_stmt varchar2(400);
disPatientId varchar2(100);
disPatientIdSource varchar2(100);
BEGIN
sql_stmt := ' SELECT distinct patient_id,patient_id_source from ' || tempPidTableName ||' where delete_date is null';
LOCK TABLE patient_mapping IN EXCLUSIVE MODE NOWAIT;
select max(patient_num) into maxPatientNum from patient_mapping ;
-- set max patient num to zero of the value is null
if maxPatientNum is null then maxPatientNum := 0;
end if;
open distinctPidCur for sql_stmt ;
loop
FETCH distinctPidCur INTO disPatientId, disPatientIdSource;
EXIT WHEN distinctPidCur%NOTFOUND;
if disPatientIdSource = 'HIVE' THEN
begin
--check if hive number exist, if so assign that number to reset of map_id's within that pid
select patient_num into existingPatientNum
from patient_mapping
where patient_num = disPatientId
and patient_ide_source = 'HIVE';
EXCEPTION
when NO_DATA_FOUND THEN
existingPatientNum := null;
end;
if existingPatientNum is not null then
execute immediate '
update ' || tempPidTableName ||'
set patient_num = patient_id,
process_status_flag = ''P''
where patient_id = :x
and patient_id_source = ''HIVE''
and not exists (
select 1
from patient_mapping pm
where pm.patient_ide = patient_map_id
and pm.patient_ide_source = patient_map_id_source
)'
using disPatientId;
else
-- generate new patient_num i.e. take max(patient_num) + 1
if maxPatientNum < disPatientId then
maxPatientNum := disPatientId;
end if ;
execute immediate '
update ' || tempPidTableName ||'
set patient_num = patient_id,
process_status_flag = ''P''
where patient_id = :x
and patient_id_source = ''HIVE''
and not exists (
select 1
from patient_mapping pm
where pm.patient_ide = patient_map_id
and pm.patient_ide_source = patient_map_id_source
)'
using disPatientId;
end if;
else
BEGIN
select patient_num into existingPatientNum
from patient_mapping
where patient_ide = disPatientId and
patient_ide_source = disPatientIdSource ;
-- test if record fetched.
EXCEPTION
WHEN NO_DATA_FOUND THEN
existingPatientNum := null;
end;
if existingPatientNum is not null then
execute immediate '
update ' || tempPidTableName ||'
set patient_num = :x,
process_status_flag = ''P''
where patient_id = :y
and patient_id_source = :z
and not exists (
select 1
from patient_mapping pm
where pm.patient_ide = patient_map_id
and pm.patient_ide_source = patient_map_id_source
)'
using existingPatientNum, disPatientId, disPatientIdSource;
else
maxPatientNum := maxPatientNum + 1;
execute immediate '
insert into ' || tempPidTableName ||' (
patient_map_id,
patient_map_id_source,
patient_id,
patient_id_source,
patient_num,
process_status_flag,
patient_map_id_status,
update_date,
download_date,
import_date,
sourcesystem_cd)
values (
:x,
''HIVE'',
:y,
''HIVE'',
:z,
''P'',
''A'',
sysdate,
sysdate,
sysdate,
''edu.harvard.i2b2.crc''
)'
using maxPatientNum,maxPatientNum,maxPatientNum;
execute immediate '
update ' || tempPidTableName ||'
set patient_num = :x,
process_status_flag = ''P''
where patient_id = :y
and patient_id_source = :z
and not exists (
select 1
from patient_mapping pm
where pm.patient_ide = patient_map_id
and pm.patient_ide_source = patient_map_id_source
)'
using maxPatientNum, disPatientId, disPatientIdSource;
end if ;
end if;
END LOOP;
CLOSE distinctPidCur ;
COMMIT;
-- do the mapping update if the update date is old
execute immediate
'merge /*+ append nologging parallel */ into patient_mapping
using ' || tempPidTableName ||' temp
on (
temp.patient_map_id = patient_mapping.patient_IDE
and temp.patient_map_id_source = patient_mapping.patient_IDE_SOURCE
)
when matched then
update set patient_num = temp.patient_id,
patient_ide_status = temp.patient_map_id_status,
update_date = temp.update_date,
download_date = temp.download_date,
import_date = sysdate,
sourcesystem_cd = temp.sourcesystem_cd,
upload_id = ' || upload_id ||'
where temp.patient_id_source = ''HIVE''
and temp.process_status_flag is null
and nvl(patient_mapping.update_date,to_date(''01-JAN-1900'',''DD-MON-YYYY'')) <= nvl(temp.update_date,to_date(''01-JAN-1900'',''DD-MON-YYYY''))
delete where temp.delete_date is not null';
-- insert new mapping records i.e flagged P - jk: added project id
execute immediate '
insert /*+ append nologging parallel */ into patient_mapping (
patient_ide,
patient_ide_source,
patient_ide_status,
patient_num,
update_date,
download_date,
import_date,
sourcesystem_cd,
project_id,
upload_id)
select patient_map_id,
patient_map_id_source,
patient_map_id_status,
patient_num,
update_date,
download_date,
sysdate,
sourcesystem_cd,
''@'' project_id,'
|| upload_id ||'
from '|| tempPidTableName || '
where process_status_flag = ''P'' AND delete_date is null';
dbms_stats.gather_table_stats(USER, 'patient_mapping');
EXCEPTION
WHEN OTHERS THEN
if distinctPidCur%isopen then
close distinctPidCur;
end if;
ROLLBACK;
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END EK_INSERT_PID_MAP_FROMTEMP;
PROCEDURE EK_DISABLE_INDEXES
IS
BEGIN
EXECUTE IMMEDIATE 'ALTER INDEX FACT_NOLOB UNUSABLE';
EXECUTE IMMEDIATE 'ALTER INDEX FACT_PATCON_DATE_PRVD_IDX UNUSABLE';
EXECUTE IMMEDIATE 'ALTER INDEX FACT_CNPT_PAT_ENCT_IDX UNUSABLE';
EXECUTE IMMEDIATE 'ALTER SESSION SET skip_unusable_indexes = true';
EXECUTE IMMEDIATE 'alter session enable parallel dml';
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END EK_DISABLE_INDEXES;
PROCEDURE EK_ENABLE_INDEXES
IS
BEGIN
EXECUTE IMMEDIATE 'ALTER INDEX FACT_NOLOB REBUILD';
EXECUTE IMMEDIATE 'ALTER INDEX FACT_PATCON_DATE_PRVD_IDX REBUILD';
EXECUTE IMMEDIATE 'ALTER INDEX FACT_CNPT_PAT_ENCT_IDX REBUILD';
EXECUTE IMMEDIATE 'ALTER SESSION SET skip_unusable_indexes = false';
EXECUTE IMMEDIATE 'alter session disable parallel dml';
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END EK_ENABLE_INDEXES;
END EUREKA ;
/© 2015 - 2025 Weber Informatics LLC | Privacy Policy