IHSD
.IHS_2023
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
IHS_2023.PR_POPULATE_IHS_ANALYTICSEVENTS_DATA
Parameters
Name
Type
Mode
Definition
procedure pr_populate_IHS_AnalyticsEvents_data -- use AUTHID CURRENT_USER to execute with the privileges and -- schema context of the calling user AUTHID CURRENT_USER AS begin -------------------------------------------------------- -- 25-1 - Data insertion for Table AnalyticsEvents_EmailNotificationSent -------------------------------------------------------- insert into AnalyticsEvents_EmailNotificationSent ( PARTICIPANTIDENTIFIER ,EVENT_TIMESTAMP ,NOTIFICATIONIDENTIFIER ,SUBJECT ,SENT_TO ,PARTICIPANTID ) select s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") as EVENT_TIMESTAMP ,s.NOTIFICATIONIDENTIFIER ,s.SUBJECT ,s."TO" ,s.PARTICIPANTID from STG_AnalyticsEvents_EmailNotificationSent s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left 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 and o.rowid is null ; -------------------------------------------------------- -- 25-2 - Data insertion for Table AnalyticsEvents_NotificationSent -------------------------------------------------------- insert into AnalyticsEvents_NotificationSent ( PARTICIPANTIDENTIFIER ,EVENT_TIMESTAMP ,CONTENTVERSION ,NOTIFICATIONIDENTIFIER ,NOTIFICATIONTYPE ,RECIPIENTS ,STATUSCODE ,TARGETCUSTOMFIELD ,PARTICIPANTID ) select s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") as EVENT_TIMESTAMP ,s.CONTENTVERSION ,s.NOTIFICATIONIDENTIFIER ,s.NOTIFICATIONTYPE ,s.RECIPIENTS ,s.STATUSCODE ,s.TARGETCUSTOMFIELD ,s.PARTICIPANTID from STG_AnalyticsEvents_NotificationSent s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left 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 and o.rowid is null ; -------------------------------------------------------- -- 25-3 - Data insertion for Table AnalyticsEvents_PushNotificationOpened -------------------------------------------------------- insert into AnalyticsEvents_PushNotificationOpened ( PARTICIPANTIDENTIFIER ,EVENT_TIMESTAMP ,DEVICEIDENTIFIER ,NOTIFICATIONIDENTIFIER ,PARTICIPANTID --new field since 10/14/2022 ,PushEventKey ) select s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") as EVENT_TIMESTAMP ,s.DEVICEIDENTIFIER ,s.NOTIFICATIONIDENTIFIER ,s.PARTICIPANTID ,s.PushEventKey from STG_AnalyticsEvents_PushNotificationOpened s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left 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 and o.rowid is null ; -------------------------------------------------------- -- 25-4 - Data insertion for Table AnalyticsEvents_PushNotificationReceived -------------------------------------------------------- insert into AnalyticsEvents_PushNotificationReceived ( PARTICIPANTIDENTIFIER ,EVENT_TIMESTAMP ,DEVICEIDENTIFIER ,NOTIFICATIONIDENTIFIER ,PARTICIPANTID --new field since 10/14/2022 ,PushEventKey ) select s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") as EVENT_TIMESTAMP ,s.DEVICEIDENTIFIER ,s.NOTIFICATIONIDENTIFIER ,s.PARTICIPANTID ,s.PushEventKey from STG_AnalyticsEvents_PushNotificationReceived s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left 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 and o.rowid is null ; -------------------------------------------------------- -- 25-5 - Data insertion for Table AnalyticsEvents_PushNotificationSent -------------------------------------------------------- insert into AnalyticsEvents_PushNotificationSent ( PARTICIPANTIDENTIFIER ,EVENT_TIMESTAMP ,BODY ,NOTIFICATIONIDENTIFIER ,TITLE ,PARTICIPANTID --new fields since 10/14/2022 ,PushEventKey ,DeviceIdentifier ) select s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") as EVENT_TIMESTAMP ,s.BODY ,s.NOTIFICATIONIDENTIFIER ,s.TITLE ,s.PARTICIPANTID ,s.PushEventKey ,s.DeviceIdentifier from STG_AnalyticsEvents_PushNotificationSent s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left 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 and o.rowid is null ; -------------------------------------------------------- -- 25-6 - Data insertion for Table AnalyticsEvents_SurveyStepViewed -------------------------------------------------------- insert into AnalyticsEvents_SurveyStepViewed ( PARTICIPANTIDENTIFIER ,EVENT_TIMESTAMP ,CLIENTLOCALTIMESTAMP ,EVENTID ,STEPIDENTIFIER ,SURVEYID ,SURVEYSESSIONID ,SURVEYVERSION ,TASKID ,TASKRUNUUID ,PARTICIPANTID ) select s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") as EVENT_TIMESTAMP ,to_date(substr(s.CLIENTLOCALTIMESTAMP, 1, LENGTH(s.CLIENTLOCALTIMESTAMP) - 3), 'fmmm/dd/yyyy hh24:mi:ss') as CLIENTLOCALTIMESTAMP ,s.EVENTID ,s.STEPIDENTIFIER ,s.SURVEYID ,s.SURVEYSESSIONID ,s.SURVEYVERSION ,s.TASKID ,s.TASKRUNUUID ,s.PARTICIPANTID from STG_AnalyticsEvents_SurveyStepViewed s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left 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 and o.rowid is null ; --- commit for all insertions! --- commit; end;