IHSD.IHS

Parameters

Name	Type	Mode

Definition

procedure pr_IHS_data_refresh_log (program_start_date varchar2 := '2023-04-01')
/*------------------------------------------------------
Project: Intern Health Study 2023 (Care Evolution Data)
Description: Oracle procedure pr_IHS_data_refresh_log
Author: Siqing Hu (shu@med.umich.edu)
Created: 2023-03-01
Updated: 2023-12-05
------------------------------------------------------*/
-- use AUTHID CURRENT_USER to execute with the privileges and 
-- schema context of the calling user
AUTHID CURRENT_USER AS

CNT_AUXILIARYDATA integer;
	--CNT_COMPETITIONS integer;   --no COMPETITIONS data yet
	CNT_FITBITACTIVITYLOGS integer;
	CNT_FITBITDAILYDATA integer;
	CNT_FITBITDEVICES integer;
	CNT_FITBITRESTINGHEARTRATES integer;
	CNT_FITBITSLEEPLOGDETAILS integer;
	CNT_FITBITSLEEPLOGS integer;
	CNT_HEALTHKITACTIVITYSUMMARIES integer;
	CNT_HEALTHKITSAMPLES_APPLEEXERCISETIME integer;
	CNT_HEALTHKITSAMPLES_APPLEEXERCISETIME_DELETED integer;
	CNT_HEALTHKITSAMPLES_HEARTRATE integer;
	CNT_HEALTHKITSAMPLES_HEARTRATEVARIABILITY integer;
	CNT_HEALTHKITSAMPLES_HEARTRATEVARIABILITY_DELETED integer;
	CNT_HEALTHKITSAMPLES_HEARTRATE_DELETED integer;
	CNT_HEALTHKITSAMPLES_RESTINGHEARTRATE integer;
	CNT_HEALTHKITSAMPLES_RESTINGHEARTRATE_DELETED integer;
	CNT_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL integer;
	CNT_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL_DELETED integer;
	CNT_HEALTHKITSAMPLES_STEPS integer;
	CNT_HEALTHKITSAMPLES_STEPS_DELETED integer;
	CNT_HEALTHKITSTATISTICS_DAILYSTEPS integer;
	CNT_MANIFEST integer;
	--CNT_NOTIFICATIONS integer;  --no longer in IHS 2023
	CNT_STUDYPARTICIPANTS integer;
	CNT_SURVEYCONSENTSIGNATURERESULTS integer;
	CNT_SURVEYDEFINITIONS integer;
	CNT_SURVEYQUESTIONRESULTS integer;
	CNT_SURVEYRESULTS integer;
	CNT_SURVEYSTEPRESULTS integer;
	CNT_SURVEYTASKS integer;
	CNT_FITBIT_INTRADAY_HEARTRATE integer;
	CNT_FITBIT_INTRADAY_STEPS integer;
	CNT_FITBIT_HEARTRATE_FILES integer;
	CNT_FITBIT_STEPS_FILES integer;
	CNT_FITBIT_HEARTRATE_FILES_DATA integer;
	CNT_FITBIT_STEPS_FILES_DATA integer;
	CNT_FITBIT_HEARTRATE_FILES_ADD integer;
	CNT_FITBIT_STEPS_FILES_ADD integer;
	--added on 4/5/2022:
	CNT_ANALYTICSEVENTS_EMAILNOTIFICATIONSENT integer;
	CNT_ANALYTICSEVENTS_NOTIFICATIONSENT integer;
	CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONOPENED integer;
	CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONRECEIVED integer;
	CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONSENT integer;
	CNT_ANALYTICSEVENTS_SURVEYSTEPVIEWED integer;
	--aaded on 6/13/2022:
	CNT_ANALYTICSEVENTS_EMAILSEND integer;
	CNT_ANALYTICSEVENTS_EMAILDELIVERY integer;
	CNT_ANALYTICSEVENTS_EMAILOPEN integer;
	CNT_ANALYTICSEVENTS_EMAILCLICK integer;
	CNT_ANALYTICSEVENTS_EMAILBOUNCE integer;
	CNT_ANALYTICSEVENTS_EMAILCOMPLAINT integer;
	--added on 7/1/2022:
	CNT_HEALTHKITSTATISTICS_HOURLYSTEPS integer;

--added on 11/2/2022:
	CNT_FITBIT_INTRADAY_HRV integer;

--added for IHS 2023
    --CNT_SURVEYDICTIONARY integer;

CNT_GARMINACTIVITYSUMMARY integer;
    CNT_GARMINDAILYSUMMARY integer;
    CNT_GARMINDAILYSUMMARY_SAMPLES integer;
    CNT_GARMINEPOCHSUMMARY integer;
    CNT_GARMINHRVSUMMARY integer;
    CNT_GARMINHRVSUMMARY_SAMPLES integer;
    CNT_GARMINMOVEIQACTIVITYSUMMARY integer;
    CNT_GARMINSLEEPSUMMARY integer;
    CNT_GARMINSLEEPSUMMARY_DURATION integer;
    CNT_GARMINSLEEPSUMMARY_SCORE integer;
    CNT_GARMINSTRESSDETAILSUMMARY integer;
    CNT_GARMINSTRESSDETAILSUMMARY_SAMPLES integer;
    CNT_GARMINTHIRDPARTYDAILYSUMMARY integer;
    CNT_GARMINTHIRDPARTYDAILYSUMMARY_SAMPLES integer;
    CNT_GARMINUSERMETRICSSUMMARY integer;

CNT_SK_VISITS integer;
    CNT_SK_LIGHT integer;
    CNT_SK_USAGE integer;
    CNT_SK_KEYBOARD integer;
    --CNT_SK_DEVICE_USAGE integer;
    --CNT_SK_SPEECH  integer;

--added on 12/5/2023:
    CNT_PROJECTDEVICEDATA integer;

record_today number(1);

begin

--1: AUXILIARYDATA
	select count(*) into CNT_AUXILIARYDATA
	from stg_AUXILIARYDATA s
	join AUXILIARYDATA o on fn_guid_to_raw(s.AUXILIARYDATAKEY) = o.AUXILIARYDATAKEY
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	;

--2: CNT_competitions
	--select count(*) into CNT_competitions
	--from stg_competitions
	--;

--3: FITBITACTIVITYLOGS
	select count(*) into CNT_FITBITACTIVITYLOGS
	from stg_FITBITACTIVITYLOGS s
	join FITBITACTIVITYLOGS o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
			and TO_UTC_TIMESTAMP_TZ(s.STARTDATE) = o.STARTDATE
			and TO_UTC_TIMESTAMP_TZ(s.ENDDATE) = o.ENDDATE  --7/26/2021: added to avoid double count
			and s.ACTIVITYTYPEID = o.ACTIVITYTYPEID --11/3/2021: modified
			and o.LASTMODIFIED = TO_UTC_TIMESTAMP_TZ(s.LASTMODIFIED)--modified on 10/19/2021
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.STARTDATE,1,10) >= program_start_date
	;
	--4: FITBITDAILYDATA
	select count(*) into CNT_FITBITDAILYDATA
	from stg_FITBITDAILYDATA s
	join FITBITDAILYDATA o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
			and to_date(substr(s."DATE",1,10), 'yyyy-mm-dd') = o.RECORD_DATE
			and o.MODIFIEDDATE = TO_UTC_TIMESTAMP_TZ(s.MODIFIEDDATE)--modified on 10/19/2021
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s."DATE",1,10) >= program_start_date
	;
	--5: FITBITDEVICES
	select count(*) into CNT_FITBITDEVICES
	from stg_FITBITDEVICES s
	join FITBITDEVICES o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
			and TO_UTC_TIMESTAMP_TZ(s."DATE") = o.RECORD_DATE
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	;
	--6: FITBITRESTINGHEARTRATES
	select count(*) into CNT_FITBITRESTINGHEARTRATES
	from stg_FITBITRESTINGHEARTRATES s
	join FITBITRESTINGHEARTRATES o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
			and to_date(substr(s."DATE",1,10), 'yyyy-mm-dd') = o.RECORD_DATE
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s."DATE",1,10) >= program_start_date
	;
	--7: FITBITSLEEPLOGDETAILS
	select count(*) into CNT_FITBITSLEEPLOGDETAILS
	from stg_FITBITSLEEPLOGDETAILS s
	join FITBITSLEEPLOGDETAILS o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
			and TO_UTC_TIMESTAMP_TZ(s.STARTDATE) = o.STARTDATE
			and TO_UTC_TIMESTAMP_TZ(s.ENDDATE) = o.ENDDATE  --7/26/2021: added to avoid double count
			and s.TYPE = o.TYPE
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.STARTDATE,1,10) >= program_start_date
	;
	--8: FITBITSLEEPLOGS
	select count(*) into CNT_FITBITSLEEPLOGS
	from stg_FITBITSLEEPLOGS s
	join FITBITSLEEPLOGS o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
			and TO_UTC_TIMESTAMP_TZ(s.STARTDATE) = o.STARTDATE
			and TO_UTC_TIMESTAMP_TZ(s.ENDDATE) = o.ENDDATE  --7/26/2021: added to avoid double count
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.STARTDATE,1,10) >= program_start_date
	;

--9: HEALTHKITACTIVITYSUMMARIES
	select count(*) into CNT_HEALTHKITACTIVITYSUMMARIES
	from stg_HEALTHKITACTIVITYSUMMARIES s
	join HEALTHKITACTIVITYSUMMARIES o on fn_guid_to_raw(s.HEALTHKITACTIVITYSUMMARYKEY) = o.HEALTHKITACTIVITYSUMMARYKEY
	and s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.STARTDATE,1,10) >= program_start_date
	;

--modified on 4/18/2022 to handle duplicated HEALTHKITSAMPLES records
	--10: HEALTHKITSAMPLES_APPLEEXERCISETIME
	--select count(*) into CNT_HEALTHKITSAMPLES_APPLEEXERCISETIME
	select count(distinct s.HEALTHKITSAMPLEKEY) into CNT_HEALTHKITSAMPLES_APPLEEXERCISETIME
	from stg_HEALTHKITSAMPLES_APPLEEXERCISETIME s
	join HEALTHKITSAMPLES_APPLEEXERCISETIME o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY
	and s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.STARTDATE,1,10) >= program_start_date
	;
	--11: HEALTHKITSAMPLES_HEARTRATE
	select count(distinct s.HEALTHKITSAMPLEKEY) into CNT_HEALTHKITSAMPLES_HEARTRATE
	from stg_HEALTHKITSAMPLES_HEARTRATE s
	join HEALTHKITSAMPLES_HEARTRATE o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY
	and s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.STARTDATE,1,10) >= program_start_date
	;
	--12: HEALTHKITSAMPLES_HEARTRATEVARIABILITY
	select count(distinct s.HEALTHKITSAMPLEKEY) into CNT_HEALTHKITSAMPLES_HEARTRATEVARIABILITY
	from stg_HEALTHKITSAMPLES_HEARTRATEVARIABILITY s
	join HEALTHKITSAMPLES_HEARTRATEVARIABILITY o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY
	and s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.STARTDATE,1,10) >= program_start_date
	;	
	--13: HEALTHKITSAMPLES_RESTINGHEARTRATE
	select count(distinct s.HEALTHKITSAMPLEKEY) into CNT_HEALTHKITSAMPLES_RESTINGHEARTRATE
	from stg_HEALTHKITSAMPLES_RESTINGHEARTRATE s
	join HEALTHKITSAMPLES_RESTINGHEARTRATE o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY
	and s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.STARTDATE,1,10) >= program_start_date
	;	
	--14: HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL
	select count(distinct s.HEALTHKITSAMPLEKEY) into CNT_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL
	from stg_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL s
	join HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY
	and s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.STARTDATE,1,10) >= program_start_date
	;	
	--15: HEALTHKITSAMPLES_STEPS
	select count(distinct s.HEALTHKITSAMPLEKEY) into CNT_HEALTHKITSAMPLES_STEPS
	from stg_HEALTHKITSAMPLES_STEPS s
	join HEALTHKITSAMPLES_STEPS o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY
	and s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.STARTDATE,1,10) >= program_start_date
	;

--16: HEALTHKITSAMPLES_APPLEEXERCISETIME_DELETED
	select count(*) into CNT_HEALTHKITSAMPLES_APPLEEXERCISETIME_DELETED
	from stg_HEALTHKITSAMPLES_APPLEEXERCISETIME_DELETED s
	join HEALTHKITSAMPLES_DELETED o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY
	and s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.DELETEDDATE,1,10) >= program_start_date
	and data_type = 1
	;
	--17: HEALTHKITSAMPLES_HEARTRATE_DELETED
	select count(*) into CNT_HEALTHKITSAMPLES_HEARTRATE_DELETED
	from stg_HEALTHKITSAMPLES_HEARTRATE_DELETED s
	join HEALTHKITSAMPLES_DELETED o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY
	and s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.DELETEDDATE,1,10) >= program_start_date
	and data_type = 2
	;
	--18: HEALTHKITSAMPLES_HEARTRATEVARIABILITY_DELETED
	select count(*) into CNT_HEALTHKITSAMPLES_HEARTRATEVARIABILITY_DELETED
	from stg_HEALTHKITSAMPLES_HEARTRATEVARIABILITY_DELETED s
	join HEALTHKITSAMPLES_DELETED o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY
	and s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.DELETEDDATE,1,10) >= program_start_date
	and data_type = 3
	;
	--19: HEALTHKITSAMPLES_RESTINGHEARTRATE_DELETED
	select count(*) into CNT_HEALTHKITSAMPLES_RESTINGHEARTRATE_DELETED
	from stg_HEALTHKITSAMPLES_RESTINGHEARTRATE_DELETED s
	join HEALTHKITSAMPLES_DELETED o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY
	and s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.DELETEDDATE,1,10) >= program_start_date
	and data_type = 4
	;
	--20: HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL_DELETED
	select count(*) into CNT_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL_DELETED
	from stg_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL_DELETED s
	join HEALTHKITSAMPLES_DELETED o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY
	and s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.DELETEDDATE,1,10) >= program_start_date
	and data_type = 5
	;
	--21: HEALTHKITSAMPLES_STEPS_DELETED
	select count(*) into CNT_HEALTHKITSAMPLES_STEPS_DELETED
	from stg_HEALTHKITSAMPLES_STEPS_DELETED s
	join HEALTHKITSAMPLES_DELETED o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY
	and s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.DELETEDDATE,1,10) >= program_start_date
	and data_type = 6
	;
	--22: HEALTHKITSTATISTICS_DAILYSTEPS
	select count(*) into CNT_HEALTHKITSTATISTICS_DAILYSTEPS
	from stg_HEALTHKITSTATISTICS_DAILYSTEPS s
	join HEALTHKITSTATISTICS_DAILYSTEPS o on fn_guid_to_raw(s.HEALTHKITSTATISTICKEY) = o.HEALTHKITSTATISTICKEY
	and s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.STARTDATE,1,10) >= program_start_date
	;

--23: MANIFEST
	select count(*) into CNT_MANIFEST
	from stg_MANIFEST s
	join MANIFEST o on TO_UTC_TIMESTAMP_TZ(s.EXPORTSTARTDATE) = o.EXPORTSTARTDATE
	;

--24: NOTIFICATIONS (no longer in IHS 2023)
	--select count(*) into CNT_NOTIFICATIONS
	--from stg_NOTIFICATIONS s
	--join NOTIFICATIONS o on fn_guid_to_raw(s.ID) = o.ID
	--where length(s.PARTICIPANTIDENTIFIER) >= 13
	--;

--25: STUDYPARTICIPANTS
	select count(*) into CNT_STUDYPARTICIPANTS
	from stg_STUDYPARTICIPANTS s
	join STUDYPARTICIPANTS o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	;

--26: SURVEYCONSENTSIGNATURERESULTS
	select count(*) into CNT_SURVEYCONSENTSIGNATURERESULTS
	from stg_SURVEYCONSENTSIGNATURERESULTS s
	join SURVEYCONSENTSIGNATURERESULTS o on fn_guid_to_raw(s.SURVEYCONSENTSIGNATURERESULTKEY) = o.SURVEYCONSENTSIGNATURERESULTKEY
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	;
	--27: SURVEYDEFINITIONS
	select count(*) into CNT_SURVEYDEFINITIONS
	from stg_SURVEYDEFINITIONS s
	join SURVEYDEFINITIONS o on fn_guid_to_raw(s.SURVEYDEFINITIONKEY) = o.SURVEYDEFINITIONKEY
	;
	--28: SURVEYQUESTIONRESULTS
	select count(*) into CNT_SURVEYQUESTIONRESULTS
	from stg_SURVEYQUESTIONRESULTS s
	join SURVEYQUESTIONRESULTS o on fn_guid_to_raw(s.SURVEYQUESTIONRESULTKEY) = o.SURVEYQUESTIONRESULTKEY
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	;
	--29: SURVEYRESULTS 
	select count(*) into CNT_SURVEYRESULTS
	from stg_SURVEYRESULTS s
	join SURVEYRESULTS o on fn_guid_to_raw(s.SURVEYRESULTKEY) = o.SURVEYRESULTKEY
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	;
	--30: SURVEYSTEPRESULTS
	select count(*) into CNT_SURVEYSTEPRESULTS
	from stg_SURVEYSTEPRESULTS s
	join SURVEYSTEPRESULTS o on fn_guid_to_raw(s.SURVEYSTEPRESULTKEY) = o.SURVEYSTEPRESULTKEY
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	;
	--31: SURVEYTASKS
	select count(*) into CNT_SURVEYTASKS
	from stg_SURVEYTASKS s
	join SURVEYTASKS o on fn_guid_to_raw(s.SURVEYTASKKEY) = o.SURVEYTASKKEY
	and o.MODIFIEDDATE = TO_UTC_TIMESTAMP_TZ(s.MODIFIEDDATE)--modified on 10/19/2021
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	;

--32: CNT_FITBIT_intraday_HEARTRATE
	select count(*) into CNT_FITBIT_intraday_HEARTRATE
	from stg_FITBIT_HEARTRATE s
	join fitbit_heartrate o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER 
		and to_date(s.record_date||' '||nvl(s.record_time, '00:00:00'), 'yyyy-mm-dd hh24:mi:ss') = o.record_date
	where length(s.PARTICIPANTIDENTIFIER) >= 13
		and s.RECORD_DATE >= program_start_date
		and s.record_time is not null
	;
	--33: CNT_FITBIT_intraday_STEPS
	select count(*) into CNT_FITBIT_intraday_STEPS
	from stg_FITBIT_STEPS s
	join fitbit_STEPS o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER 
		and to_date(s.record_date||' '||nvl(s.record_time, '00:00:00'), 'yyyy-mm-dd hh24:mi:ss') = o.record_date
	where length(s.PARTICIPANTIDENTIFIER) >= 13
		and s.RECORD_DATE >= program_start_date
		and s.record_time is not null
	;	
	--34: CNT_FITBIT_HEARTRATE_FILES (total incoming files)
	select count(*) into CNT_FITBIT_HEARTRATE_FILES
	from stg_FITBIT_HEARTRATE 
	where record_time is null
	;
	--35: CNT_FITBIT_STEPS_FILES (total incoming files)
	select count(*) into CNT_FITBIT_STEPS_FILES
	from stg_FITBIT_STEPS
	where record_time is null
	;
	--36: CNT_FITBIT_HEARTRATE_FILES_DATA (files with valid data)
	select count(distinct PARTICIPANTIDENTIFIER||RECORD_DATE) into CNT_FITBIT_HEARTRATE_FILES_DATA
	from stg_FITBIT_HEARTRATE s
	where s.record_time is not null
	;
	--37: CNT_FITBIT_STEPS_FILES_DATA (files with valid data)
	select count(distinct PARTICIPANTIDENTIFIER||RECORD_DATE) into CNT_FITBIT_STEPS_FILES_DATA
	from stg_FITBIT_STEPS s
	where s.record_time is not null
	;
	--38: CNT_FITBIT_HEARTRATE_FILES_ADD (files with valid data and PARTICIPANTIDENTIFIER)
	select count(distinct PARTICIPANTIDENTIFIER||RECORD_DATE) into CNT_FITBIT_HEARTRATE_FILES_ADD
	from stg_FITBIT_HEARTRATE s
	where length(s.PARTICIPANTIDENTIFIER) >= 13
		and s.RECORD_DATE >= program_start_date
		and s.record_time is not null
	;
	--39: CNT_FITBIT_STEPS_FILES_ADD (files with valid data and PARTICIPANTIDENTIFIER)
	select count(distinct PARTICIPANTIDENTIFIER||RECORD_DATE) into CNT_FITBIT_STEPS_FILES_ADD
	from stg_FITBIT_STEPS s
	where length(s.PARTICIPANTIDENTIFIER) >= 13
		and s.RECORD_DATE >= program_start_date
		and s.record_time is not null
	;

--added on 4/5/2022:
	--40: CNT_ANALYTICSEVENTS_EMAILNOTIFICATIONSENT
	select count(*) into CNT_ANALYTICSEVENTS_EMAILNOTIFICATIONSENT
	from STG_AnalyticsEvents_EmailNotificationSent s
	join AnalyticsEvents_EmailNotificationSent o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	and TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") = o.EVENT_TIMESTAMP
	and s.NOTIFICATIONIDENTIFIER = o.NOTIFICATIONIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	;

--41: CNT_ANALYTICSEVENTS_NOTIFICATIONSENT
	select count(*) into CNT_ANALYTICSEVENTS_NOTIFICATIONSENT
	from STG_AnalyticsEvents_NotificationSent s
	join AnalyticsEvents_NotificationSent o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	and TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") = o.EVENT_TIMESTAMP
	and s.NOTIFICATIONIDENTIFIER = o.NOTIFICATIONIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	;

--42: CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONOPENED
	select count(*) into CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONOPENED
	from STG_AnalyticsEvents_PushNotificationOpened s
	join AnalyticsEvents_PushNotificationOpened o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	and TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") = o.EVENT_TIMESTAMP
	and s.NOTIFICATIONIDENTIFIER = o.NOTIFICATIONIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	;

--43: CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONRECEIVED
	select count(*) into CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONRECEIVED
	from STG_AnalyticsEvents_PushNotificationReceived s
	join AnalyticsEvents_PushNotificationReceived o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	and TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") = o.EVENT_TIMESTAMP
	and s.NOTIFICATIONIDENTIFIER = o.NOTIFICATIONIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	;

--44: CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONSENT
	select count(*) into CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONSENT
	from STG_AnalyticsEvents_PushNotificationSent s
	join AnalyticsEvents_PushNotificationSent o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	and TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") = o.EVENT_TIMESTAMP
	and s.NOTIFICATIONIDENTIFIER = o.NOTIFICATIONIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	;

--45: CNT_ANALYTICSEVENTS_SURVEYSTEPVIEWED
	select count(*) into CNT_ANALYTICSEVENTS_SURVEYSTEPVIEWED
	from STG_AnalyticsEvents_SurveyStepViewed s
	join AnalyticsEvents_SurveyStepViewed o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	and TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") = o.EVENT_TIMESTAMP
	and s.EVENTID = o.EVENTID
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	;

--added on 6/13/2022 and modified on 6/17/2022 to handle NULL PARTICIPANTIDENTIFIER
	--46: CNT_ANALYTICSEVENTS_EMAILSEND
	select count(*) into CNT_ANALYTICSEVENTS_EMAILSEND
	from STG_AnalyticsEvents_EmailSend s
	join AnalyticsEvents_EmailSend o on s.EMAILEVENTKEY = o.EMAILEVENTKEY
	and TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") = o.EVENT_TIMESTAMP
	and s.NOTIFICATIONIDENTIFIER = o.NOTIFICATIONIDENTIFIER
	--where length(s.PARTICIPANTIDENTIFIER) >= 13
	;

--47: CNT_ANALYTICSEVENTS_EMAILDELIVERY
	select count(*) into CNT_ANALYTICSEVENTS_EMAILDELIVERY
	from STG_AnalyticsEvents_EmailDelivery s
	join AnalyticsEvents_EmailDelivery o on s.EMAILEVENTKEY = o.EMAILEVENTKEY
	and TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") = o.EVENT_TIMESTAMP
	and s.NOTIFICATIONIDENTIFIER = o.NOTIFICATIONIDENTIFIER
	--where length(s.PARTICIPANTIDENTIFIER) >= 13
	;

--48: CNT_ANALYTICSEVENTS_EMAILOPEN
	select count(*) into CNT_ANALYTICSEVENTS_EMAILOPEN
	from STG_AnalyticsEvents_EmailOpen s
	join AnalyticsEvents_EmailOpen o on s.EMAILEVENTKEY = o.EMAILEVENTKEY
	and TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") = o.EVENT_TIMESTAMP
	and s.NOTIFICATIONIDENTIFIER = o.NOTIFICATIONIDENTIFIER
	--where length(s.PARTICIPANTIDENTIFIER) >= 13
	;

--49: CNT_ANALYTICSEVENTS_EMAILCLICK
	select count(*) into CNT_ANALYTICSEVENTS_EMAILCLICK
	from STG_AnalyticsEvents_EmailClick s
	join AnalyticsEvents_EmailClick o on s.EMAILEVENTKEY = o.EMAILEVENTKEY
	and TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") = o.EVENT_TIMESTAMP
	and s.NOTIFICATIONIDENTIFIER = o.NOTIFICATIONIDENTIFIER
	--where length(s.PARTICIPANTIDENTIFIER) >= 13
	;

--50: CNT_ANALYTICSEVENTS_EMAILBOUNCE
	select count(*) into CNT_ANALYTICSEVENTS_EMAILBOUNCE
	from STG_AnalyticsEvents_EmailBounce s
	join AnalyticsEvents_EmailBounce o on s.EMAILEVENTKEY = o.EMAILEVENTKEY
	and TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") = o.EVENT_TIMESTAMP
	and s.NOTIFICATIONIDENTIFIER = o.NOTIFICATIONIDENTIFIER
	--where length(s.PARTICIPANTIDENTIFIER) >= 13
	;

--51: CNT_ANALYTICSEVENTS_EMAILCOMPLAINT
	select count(*) into CNT_ANALYTICSEVENTS_EMAILCOMPLAINT
	from STG_AnalyticsEvents_EmailComplaint s
	join AnalyticsEvents_EmailComplaint o on s.EMAILEVENTKEY = o.EMAILEVENTKEY
	and TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") = o.EVENT_TIMESTAMP
	and s.NOTIFICATIONIDENTIFIER = o.NOTIFICATIONIDENTIFIER
	--where length(s.PARTICIPANTIDENTIFIER) >= 13
	;

--added on 7/1/2022:
	--52: CNT_HEALTHKITV2STATISTICS_HOURLYSTEPS
	select count(*) into CNT_HEALTHKITSTATISTICS_HOURLYSTEPS
	from stg_HEALTHKITSTATISTICS_HOURLYSTEPS s
	join HEALTHKITSTATISTICS_HOURLYSTEPS o on fn_guid_to_raw(s.HEALTHKITSTATISTICKEY) = o.HEALTHKITSTATISTICKEY
	and s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	where length(s.PARTICIPANTIDENTIFIER) >= 13
	and substr(s.STARTDATE,1,10) >= program_start_date
	;

--added on 11/2/2022:
	--53: CNT_FITBIT_INTRADAY_HRV (tracking cumulative count instead)
	select count(*) into CNT_FITBIT_INTRADAY_HRV 
	from FITBIT_hrv
	;

--added for IHS 2023
    --54: CNT_SURVEYDICTIONARY
    --select count(*) into CNT_SURVEYDICTIONARY
    --from SURVEYDICTIONARY
    --;

--tracking cumulative counts
    select count(*) into CNT_GARMINACTIVITYSUMMARY from GARMINACTIVITYSUMMARY;
    select count(*) into CNT_GARMINDAILYSUMMARY from GARMINDAILYSUMMARY;
    select count(*) into CNT_GARMINDAILYSUMMARY_SAMPLES from GARMINDAILYSUMMARY_SAMPLES;
    select count(*) into CNT_GARMINEPOCHSUMMARY from GARMINEPOCHSUMMARY;
    select count(*) into CNT_GARMINHRVSUMMARY from GARMINHRVSUMMARY;
    select count(*) into CNT_GARMINHRVSUMMARY_SAMPLES from GARMINHRVSUMMARY_SAMPLES;
    select count(*) into CNT_GARMINMOVEIQACTIVITYSUMMARY from GARMINMOVEIQACTIVITYSUMMARY;
    select count(*) into CNT_GARMINSLEEPSUMMARY from GARMINSLEEPSUMMARY;
    select count(*) into CNT_GARMINSLEEPSUMMARY_DURATION from GARMINSLEEPSUMMARY_DURATION;
    select count(*) into CNT_GARMINSLEEPSUMMARY_SCORE from GARMINSLEEPSUMMARY_SCORE;
    select count(*) into CNT_GARMINSTRESSDETAILSUMMARY from GARMINSTRESSDETAILSUMMARY;
    select count(*) into CNT_GARMINSTRESSDETAILSUMMARY_SAMPLES from GARMINSTRESSDETAILSUMMARY_SAMPLES;
    select count(*) into CNT_GARMINTHIRDPARTYDAILYSUMMARY from GARMINTHIRDPARTYDAILYSUMMARY;
    select count(*) into CNT_GARMINTHIRDPARTYDAILYSUMMARY_SAMPLES from GARMINTHIRDPARTYDAILYSUMMARY_SAMPLES;
    select count(*) into CNT_GARMINUSERMETRICSSUMMARY from GARMINUSERMETRICSSUMMARY;

select count(*) into CNT_SK_VISITS from SK_VISITS;
	select count(*) into CNT_SK_LIGHT from SK_LIGHT;
	select count(*) into CNT_SK_USAGE from SK_USAGE;
    select count(*) into CNT_SK_KEYBOARD
	from SK_KEYBOARD
    where load_date = trunc(sysdate)
	;
	--select count(*) into CNT_SK_DEVICE_USAGE 
	--from SK_DEVICE_USAGE
    --where load_date = trunc(sysdate)
	--;
	--select count(*) into CNT_SK_SPEECH 
	--from SK_SPEECH
    --where load_date = trunc(sysdate)
	--;

--added on 12/5/2023:
	select count(*) into CNT_PROJECTDEVICEDATA from PROJECTDEVICEDATA;

select count(*) into record_today from IHS_data_refresh_log 
	where trunc(refresh_date) = trunc(sysdate)
	;

--if record_today = 0 then 
		insert into IHS_data_refresh_log (
			refresh_date
			,CNT_AUXILIARYDATA
			--,CNT_COMPETITIONS --no COMPETITIONS data yet
			,CNT_FITBITACTIVITYLOGS
			,CNT_FITBITDAILYDATA
			,CNT_FITBITDEVICES
			,CNT_FITBITRESTINGHEARTRATES
			,CNT_FITBITSLEEPLOGDETAILS
			,CNT_FITBITSLEEPLOGS
			,CNT_HEALTHKITACTIVITYSUMMARIES
			,CNT_HEALTHKITSAMPLES_APPLEEXERCISETIME
			,CNT_HEALTHKITSAMPLES_APPLEEXERCISETIME_DELETED
			,CNT_HEALTHKITSAMPLES_HEARTRATE
			,CNT_HEALTHKITSAMPLES_HEARTRATEVARIABILITY
			,CNT_HEALTHKITSAMPLES_HEARTRATEVARIABILITY_DELETED
			,CNT_HEALTHKITSAMPLES_HEARTRATE_DELETED
			,CNT_HEALTHKITSAMPLES_RESTINGHEARTRATE
			,CNT_HEALTHKITSAMPLES_RESTINGHEARTRATE_DELETED
			,CNT_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL
			,CNT_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL_DELETED
			,CNT_HEALTHKITSAMPLES_STEPS
			,CNT_HEALTHKITSAMPLES_STEPS_DELETED
			,CNT_HEALTHKITSTATISTICS_DAILYSTEPS
			,CNT_MANIFEST
			--,CNT_NOTIFICATIONS    --no longer in IHS 2023
			,CNT_STUDYPARTICIPANTS
			,CNT_SURVEYCONSENTSIGNATURERESULTS
			,CNT_SURVEYDEFINITIONS
			,CNT_SURVEYQUESTIONRESULTS
			,CNT_SURVEYRESULTS
			,CNT_SURVEYSTEPRESULTS
			,CNT_SURVEYTASKS
			,CNT_FITBIT_INTRADAY_HEARTRATE
			,CNT_FITBIT_INTRADAY_STEPS
			,CNT_FITBIT_HEARTRATE_FILES
			,CNT_FITBIT_STEPS_FILES
			,CNT_FITBIT_HEARTRATE_FILES_DATA
			,CNT_FITBIT_STEPS_FILES_DATA
			,CNT_FITBIT_HEARTRATE_FILES_ADD
			,CNT_FITBIT_STEPS_FILES_ADD
			--added on 4/5/2022:
			,CNT_ANALYTICSEVENTS_EMAILNOTIFICATIONSENT
			,CNT_ANALYTICSEVENTS_NOTIFICATIONSENT
			,CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONOPENED
			,CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONRECEIVED
			,CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONSENT
			,CNT_ANALYTICSEVENTS_SURVEYSTEPVIEWED
			--added on 6/13/2022:
			,CNT_ANALYTICSEVENTS_EMAILSEND
			,CNT_ANALYTICSEVENTS_EMAILDELIVERY
			,CNT_ANALYTICSEVENTS_EMAILOPEN
			,CNT_ANALYTICSEVENTS_EMAILCLICK
			,CNT_ANALYTICSEVENTS_EMAILBOUNCE
			,CNT_ANALYTICSEVENTS_EMAILCOMPLAINT
			--added on 7/1/2022:
			,CNT_HEALTHKITSTATISTICS_HOURLYSTEPS
			--added on 11/2/2022:
			,CNT_FITBIT_INTRADAY_HRV

--added for IHS 2023
            --,CNT_SURVEYDICTIONARY
            ,CNT_GARMINACTIVITYSUMMARY
            ,CNT_GARMINDAILYSUMMARY
            ,CNT_GARMINDAILYSUMMARY_SAMPLES
            ,CNT_GARMINEPOCHSUMMARY
            ,CNT_GARMINHRVSUMMARY
            ,CNT_GARMINHRVSUMMARY_SAMPLES
            ,CNT_GARMINMOVEIQACTIVITYSUMMARY
            ,CNT_GARMINSLEEPSUMMARY
            ,CNT_GARMINSLEEPSUMMARY_DURATION
            ,CNT_GARMINSLEEPSUMMARY_SCORE
            ,CNT_GARMINSTRESSDETAILSUMMARY
            ,CNT_GARMINSTRESSDETAILSUMMARY_SAMPLES
            ,CNT_GARMINTHIRDPARTYDAILYSUMMARY
            ,CNT_GARMINTHIRDPARTYDAILYSUMMARY_SAMPLES
            ,CNT_GARMINUSERMETRICSSUMMARY
            ,CNT_SK_VISITS
            ,CNT_SK_LIGHT
            ,CNT_SK_USAGE
            ,CNT_SK_KEYBOARD
            --,CNT_SK_DEVICE_USAGE
            --,CNT_SK_SPEECH

--added on 12/5/2023:
            ,CNT_PROJECTDEVICEDATA
		)
		values (
			sysdate
			--trunc(sysdate)		--refresh_date
			,CNT_AUXILIARYDATA
			--,CNT_COMPETITIONS   --no COMPETITIONS data yet
			,CNT_FITBITACTIVITYLOGS
			,CNT_FITBITDAILYDATA
			,CNT_FITBITDEVICES
			,CNT_FITBITRESTINGHEARTRATES
			,CNT_FITBITSLEEPLOGDETAILS
			,CNT_FITBITSLEEPLOGS
			,CNT_HEALTHKITACTIVITYSUMMARIES
			,CNT_HEALTHKITSAMPLES_APPLEEXERCISETIME
			,CNT_HEALTHKITSAMPLES_APPLEEXERCISETIME_DELETED
			,CNT_HEALTHKITSAMPLES_HEARTRATE
			,CNT_HEALTHKITSAMPLES_HEARTRATEVARIABILITY
			,CNT_HEALTHKITSAMPLES_HEARTRATEVARIABILITY_DELETED
			,CNT_HEALTHKITSAMPLES_HEARTRATE_DELETED
			,CNT_HEALTHKITSAMPLES_RESTINGHEARTRATE
			,CNT_HEALTHKITSAMPLES_RESTINGHEARTRATE_DELETED
			,CNT_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL
			,CNT_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL_DELETED
			,CNT_HEALTHKITSAMPLES_STEPS
			,CNT_HEALTHKITSAMPLES_STEPS_DELETED
			,CNT_HEALTHKITSTATISTICS_DAILYSTEPS
			,CNT_MANIFEST
			--,CNT_NOTIFICATIONS    --no longer in IHS 2023
			,CNT_STUDYPARTICIPANTS
			,CNT_SURVEYCONSENTSIGNATURERESULTS
			,CNT_SURVEYDEFINITIONS
			,CNT_SURVEYQUESTIONRESULTS
			,CNT_SURVEYRESULTS
			,CNT_SURVEYSTEPRESULTS
			,CNT_SURVEYTASKS
			,CNT_FITBIT_INTRADAY_HEARTRATE
			,CNT_FITBIT_INTRADAY_STEPS
			,CNT_FITBIT_HEARTRATE_FILES
			,CNT_FITBIT_STEPS_FILES
			,CNT_FITBIT_HEARTRATE_FILES_DATA
			,CNT_FITBIT_STEPS_FILES_DATA
			,CNT_FITBIT_HEARTRATE_FILES_ADD
			,CNT_FITBIT_STEPS_FILES_ADD
			--added on 4/5/2022:
			,CNT_ANALYTICSEVENTS_EMAILNOTIFICATIONSENT
			,CNT_ANALYTICSEVENTS_NOTIFICATIONSENT
			,CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONOPENED
			,CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONRECEIVED
			,CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONSENT
			,CNT_ANALYTICSEVENTS_SURVEYSTEPVIEWED
			--added on 6/13/2022:
			,CNT_ANALYTICSEVENTS_EMAILSEND
			,CNT_ANALYTICSEVENTS_EMAILDELIVERY
			,CNT_ANALYTICSEVENTS_EMAILOPEN
			,CNT_ANALYTICSEVENTS_EMAILCLICK
			,CNT_ANALYTICSEVENTS_EMAILBOUNCE
			,CNT_ANALYTICSEVENTS_EMAILCOMPLAINT
			--added on 7/1/2022:
			,CNT_HEALTHKITSTATISTICS_HOURLYSTEPS
			--added on 11/2/2022:
			,CNT_FITBIT_INTRADAY_HRV

--added on 12/5/2023:
            ,CNT_PROJECTDEVICEDATA            
		)
		;
		commit;
	--end if;
end;

IHS_2023.PR_IHS_DATA_REFRESH_LOG

Parameters

Definition