IHSD
.IHS_2023
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
IHS_2023.PR_INSERT_SK_DATA
Parameters
Name
Type
Mode
Definition
procedure PR_INSERT_SK_DATA -- use AUTHID CURRENT_USER to execute with the privileges and -- schema context of the calling user AUTHID CURRENT_USER AS cursor c is select s.LUX ,s.CHROMATICITY_X ,s.CHROMATICITY_Y ,s.PLACEMENT ,s.EVENT_TIMESTAMP --already in TIMESTAMP(6) WITH TIME ZONE ,s.DEVICE_TYPE ,s.PARTICIPANT_ID --,s.DEVICE_ID from SK_LIGHT_int s order by s.PARTICIPANT_ID asc, s.DEVICE_TYPE, s.EVENT_TIMESTAMP asc ; tmp_PARTICIPANT_ID varchar2(15) := 'x'; tmp_DEVICE_TYPE varchar2(15) := 'x'; pre_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE := NULL; cnt_PARTICIPANT pls_integer := 0; seq_interval pls_integer := 200; lower_seq pls_integer := 1; upper_seq pls_integer := 200; begin -------------------------------------------------------- -- Data insertion for Table SK_VISITS -------------------------------------------------------- --remove old records delete from SK_VISITS where rowid in ( select o.rowid from stg_SK_VISITS s join STUDYPARTICIPANTS p on s.PARTICIPANT_ID = p.PARTICIPANTIDENTIFIER left join SK_VISITS o on s.PARTICIPANT_ID = o.PARTICIPANT_ID and TO_UTC_TIMESTAMP_TZ(s.EVENT_TIMESTAMP) = o.EVENT_TIMESTAMP and TO_UTC_TIMESTAMP_TZ(s.DEPART_START) = o.DEPART_START and TO_UTC_TIMESTAMP_TZ(s.ARRIVAL_START) = o.ARRIVAL_START where length(s.PARTICIPANT_ID) >= 13 and o.rowid is not null ) ; --insert new records insert into SK_VISITS ( EVENT_TIMESTAMP ,DEPART_START ,DEPART_DURATION ,LOCATIONCATEGORY ,ARRIVAL_START ,ARRIVAL_DURATION ,IDENTIFIER ,DISTANCEFROMHOME ,DEVICE_TYPE ,PARTICIPANT_ID --,DEVICE_ID ) select distinct TO_UTC_TIMESTAMP_TZ(s.EVENT_TIMESTAMP) as EVENT_TIMESTAMP ,TO_UTC_TIMESTAMP_TZ(s.DEPART_START) as DEPART_START ,s.DEPART_DURATION ,l.category_val as LOCATIONCATEGORY ,TO_UTC_TIMESTAMP_TZ(s.ARRIVAL_START) as ARRIVAL_START ,s.ARRIVAL_DURATION ,s.IDENTIFIER ,s.DISTANCEFROMHOME ,d.category_val as DEVICE_TYPE ,s.PARTICIPANT_ID --,s.DEVICE_ID from stg_SK_VISITS s join STUDYPARTICIPANTS p on s.PARTICIPANT_ID = p.PARTICIPANTIDENTIFIER join SK_lookup d on d.data_type = 'DEVICE_TYPE' and lower(d.category_txt) = lower(s.DEVICE_TYPE) join SK_lookup l on l.data_type = 'VISIT_LOCATION_CATEGORY' and lower(l.category_txt) = lower(s.LOCATIONCATEGORY) left join SK_VISITS o on s.PARTICIPANT_ID = o.PARTICIPANT_ID and TO_UTC_TIMESTAMP_TZ(s.EVENT_TIMESTAMP) = o.EVENT_TIMESTAMP and TO_UTC_TIMESTAMP_TZ(s.DEPART_START) = o.DEPART_START and TO_UTC_TIMESTAMP_TZ(s.ARRIVAL_START) = o.ARRIVAL_START where length(s.PARTICIPANT_ID) >= 13 and s.ARRIVAL_START is not null and o.rowid is null ; commit; -------------------------------------------------------- -- Data insertion for Table SK_USAGE -------------------------------------------------------- /* --two different types of data files and they could have same EVENT_TIMESTAMP --require joining with message and phone call related fields to remove old records and/or filter any duplicated records --no de-duplication done at Michigan side (depends on CareEv to deliver new data only) --remove old records delete from SK_USAGE where rowid in ( select o.rowid from stg_SK_USAGE s join STUDYPARTICIPANTS p on s.PARTICIPANT_ID = p.PARTICIPANTIDENTIFIER left join SK_USAGE o on s.PARTICIPANT_ID = o.PARTICIPANT_ID and TO_UTC_TIMESTAMP_TZ(s.EVENT_TIMESTAMP) = o.EVENT_TIMESTAMP and s.DURATION = o.DURATION where length(s.PARTICIPANT_ID) >= 13 and o.rowid is not null ) ; */ --insert new records insert into SK_USAGE ( EVENT_TIMESTAMP ,TOTALINCOMINGCALLS ,TOTALOUTGOINGCALLS ,TOTALPHONECALLDURATION ,TOTALUNIQUECONTACTS ,DURATION ,TOTALINCOMINGMESSAGES ,TOTALOUTGOINGMESSAGES ,DEVICE_TYPE ,PARTICIPANT_ID --,DEVICE_ID ) select distinct TO_UTC_TIMESTAMP_TZ(s.EVENT_TIMESTAMP) as EVENT_TIMESTAMP ,s.TOTALINCOMINGCALLS ,s.TOTALOUTGOINGCALLS ,s.TOTALPHONECALLDURATION ,s.TOTALUNIQUECONTACTS ,s.DURATION ,s.TOTALINCOMINGMESSAGES ,s.TOTALOUTGOINGMESSAGES ,d.category_val as DEVICE_TYPE ,s.PARTICIPANT_ID --,s.DEVICE_ID from stg_SK_USAGE s join STUDYPARTICIPANTS p on s.PARTICIPANT_ID = p.PARTICIPANTIDENTIFIER join SK_lookup d on d.data_type = 'DEVICE_TYPE' and lower(d.category_txt) = lower(s.DEVICE_TYPE) --left join SK_USAGE o on s.PARTICIPANT_ID = o.PARTICIPANT_ID -- and TO_UTC_TIMESTAMP_TZ(s.EVENT_TIMESTAMP) = o.EVENT_TIMESTAMP -- and s.DURATION = o.DURATION where length(s.PARTICIPANT_ID) >= 13 --and o.rowid is null ; commit; -------------------------------------------------------- -- Data insertion for Table SK_KEYBOARD -------------------------------------------------------- insert into SK_KEYBOARD ( CONTENT_JSON ,DEVICE_TYPE ,PARTICIPANT_ID --,DEVICE_ID ,LOAD_DATE ) select s.CONTENT_JSON ,d.category_val as DEVICE_TYPE ,s.PARTICIPANT_ID --,s.DEVICE_ID ,trunc(sysdate) load_date from STG_SK_KEYBOARD s join STUDYPARTICIPANTS p on s.PARTICIPANT_ID = p.PARTICIPANTIDENTIFIER join SK_lookup d on d.data_type = 'DEVICE_TYPE' and lower(d.category_txt) = lower(s.DEVICE_TYPE) where length(s.PARTICIPANT_ID) >= 13 ; -------------------------------------------------------- -- Data insertion for Table SK_LIGHT -------------------------------------------------------- --EXECUTE IMMEDIATE 'truncate table SK_LIGHT_int'; --insert daily new records with EVENT_TIMESTAMP data conversion into an intermediate table first --for handling the different time zone timestamps consistently EXECUTE IMMEDIATE 'truncate table SK_LIGHT_PARTICIPANTs'; EXECUTE IMMEDIATE 'truncate table SK_LIGHT_int'; --EXECUTE IMMEDIATE 'truncate table SK_LIGHT_test'; insert into SK_LIGHT_PARTICIPANTs ( ID_SEQ ,PARTICIPANT_ID) with qq as ( select distinct s.PARTICIPANT_ID from stg_SK_LIGHT s join STUDYPARTICIPANTS p on s.PARTICIPANT_ID = p.PARTICIPANTIDENTIFIER order by s.PARTICIPANT_ID asc ) select rownum, PARTICIPANT_ID from qq order by PARTICIPANT_ID asc ; commit; select count(*) into cnt_PARTICIPANT from SK_LIGHT_PARTICIPANTs; for i in 1 .. CEIL(cnt_PARTICIPANT/seq_interval) loop lower_seq := (i-1) * seq_interval + 1; upper_seq := i * seq_interval; insert into SK_LIGHT_int ( LUX ,CHROMATICITY_X ,CHROMATICITY_Y ,PLACEMENT ,EVENT_TIMESTAMP ,DEVICE_TYPE ,PARTICIPANT_ID --,DEVICE_ID ) select s.LUX ,s.CHROMATICITY_X ,s.CHROMATICITY_Y ,l.category_val as PLACEMENT ,TO_UTC_TIMESTAMP_TZ(s.EVENT_TIMESTAMP) as EVENT_TIMESTAMP ,d.category_val as DEVICE_TYPE ,s.PARTICIPANT_ID --,s.DEVICE_ID from stg_SK_LIGHT s join SK_LIGHT_PARTICIPANTs p on s.PARTICIPANT_ID = p.PARTICIPANT_ID join SK_lookup d on d.data_type = 'DEVICE_TYPE' and lower(d.category_txt) = lower(s.DEVICE_TYPE) join SK_lookup l on l.data_type = 'LIGHT_SENSOR_PLACEMENT' and lower(l.category_txt) = lower(s.PLACEMENT) where p.ID_SEQ between lower_seq and upper_seq ; commit; end loop; for rec in c loop if ( (tmp_PARTICIPANT_ID <> rec.PARTICIPANT_ID) or (tmp_DEVICE_TYPE <> rec.DEVICE_TYPE) ) then --commit for the previous PARTICIPANT_ID and DEVICE_TYPE commit; --insert first record for a new PARTICIPANT_ID and/or DEVICE_TYPE insert into SK_LIGHT ( LUX ,CHROMATICITY_X ,CHROMATICITY_Y ,PLACEMENT ,EVENT_TIMESTAMP ,DEVICE_TYPE ,PARTICIPANT_ID --,DEVICE_ID ) values ( rec.LUX ,rec.CHROMATICITY_X ,rec.CHROMATICITY_Y ,rec.PLACEMENT ,rec.EVENT_TIMESTAMP ,rec.DEVICE_TYPE ,rec.PARTICIPANT_ID --,rec.DEVICE_ID ); --track the PARTICIPANT_ID, DEVICE_TYPE, and TIMESTAMP tmp_PARTICIPANT_ID := rec.PARTICIPANT_ID; tmp_DEVICE_TYPE := rec.DEVICE_TYPE; pre_TIMESTAMP := rec.EVENT_TIMESTAMP; elsif rec.EVENT_TIMESTAMP - pre_TIMESTAMP >= '+00 00:10:00.000000' then --record at every 10 minutes insert into SK_LIGHT ( LUX ,CHROMATICITY_X ,CHROMATICITY_Y ,PLACEMENT ,EVENT_TIMESTAMP ,DEVICE_TYPE ,PARTICIPANT_ID --,DEVICE_ID ) values ( rec.LUX ,rec.CHROMATICITY_X ,rec.CHROMATICITY_Y ,rec.PLACEMENT ,rec.EVENT_TIMESTAMP ,rec.DEVICE_TYPE ,rec.PARTICIPANT_ID --,rec.DEVICE_ID ); --reset pre_TIMESTAMP to current EVENT_TIMESTAMP pre_TIMESTAMP := rec.EVENT_TIMESTAMP; end if; end loop; commit; end;