IHSD
.IHS_2023
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
IHS_2023.PR_POPULATE_IHS_ANALYTICSEVENTS_EMAIL
Parameters
Name
Type
Mode
Definition
procedure pr_populate_IHS_AnalyticsEvents_Email -- use AUTHID CURRENT_USER to execute with the privileges and -- schema context of the calling user AUTHID CURRENT_USER AS begin -------------------------------------------------------- -- 26-1 - Data insertion for Table AnalyticsEvents_EmailBounce -------------------------------------------------------- insert into AnalyticsEvents_EmailBounce ( PARTICIPANTIDENTIFIER ,EVENT_TIMESTAMP ,PARTICIPANTID ,EMAILEVENTKEY ,MAILDESTINATION ,MAILTIMESTAMP ,MAILSOURCE ,MAILMESSAGEID ,NOTIFICATIONIDENTIFIER ,FROMDOMAIN ,LOCALECODE ,BOUNCETYPE ,BOUNCESUBTYPE ) select s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") as EVENT_TIMESTAMP ,s.PARTICIPANTID ,s.EMAILEVENTKEY ,s.MAILDESTINATION ,TO_UTC_TIMESTAMP_TZ(s.MAILTIMESTAMP) as MAILTIMESTAMP ,s.MAILSOURCE ,s.MAILMESSAGEID ,s.NOTIFICATIONIDENTIFIER ,s.FROMDOMAIN ,s.LOCALECODE ,s.BOUNCETYPE ,s.BOUNCESUBTYPE from STG_AnalyticsEvents_EmailBounce s left 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 o.rowid is null ; -------------------------------------------------------- -- 26-2 - Data insertion for Table AnalyticsEvents_EmailClick -------------------------------------------------------- insert into AnalyticsEvents_EmailClick ( PARTICIPANTIDENTIFIER ,EVENT_TIMESTAMP ,PARTICIPANTID ,EMAILEVENTKEY ,MAILDESTINATION ,MAILTIMESTAMP ,MAILSOURCE ,MAILMESSAGEID ,NOTIFICATIONIDENTIFIER ,FROMDOMAIN ,LOCALECODE ,CLICKTIMESTAMP ,CLICKUSERAGENT ,CLICKIPADDRESS ,CLICKLINK ,CLICKLINKTAGS ) select s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") as EVENT_TIMESTAMP ,s.PARTICIPANTID ,s.EMAILEVENTKEY ,s.MAILDESTINATION ,TO_UTC_TIMESTAMP_TZ(s.MAILTIMESTAMP) as MAILTIMESTAMP ,s.MAILSOURCE ,s.MAILMESSAGEID ,s.NOTIFICATIONIDENTIFIER ,s.FROMDOMAIN ,s.LOCALECODE ,TO_UTC_TIMESTAMP_TZ(s.CLICKTIMESTAMP) as CLICKTIMESTAMP ,s.CLICKUSERAGENT ,s.CLICKIPADDRESS ,s.CLICKLINK ,s.CLICKLINKTAGS from STG_AnalyticsEvents_EmailClick s left 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 o.rowid is null ; -------------------------------------------------------- -- 26-3 - Data insertion for Table AnalyticsEvents_EmailComplaint -------------------------------------------------------- insert into AnalyticsEvents_EmailComplaint ( PARTICIPANTIDENTIFIER ,EVENT_TIMESTAMP ,PARTICIPANTID ,EMAILEVENTKEY ,MAILDESTINATION ,MAILTIMESTAMP ,MAILSOURCE ,MAILMESSAGEID ,NOTIFICATIONIDENTIFIER ,FROMDOMAIN ,LOCALECODE ,COMPLAINTTYPE ,COMPLAINTUSERAGENT ) select s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") as EVENT_TIMESTAMP ,s.PARTICIPANTID ,s.EMAILEVENTKEY ,s.MAILDESTINATION ,TO_UTC_TIMESTAMP_TZ(s.MAILTIMESTAMP) as MAILTIMESTAMP ,s.MAILSOURCE ,s.MAILMESSAGEID ,s.NOTIFICATIONIDENTIFIER ,s.FROMDOMAIN ,s.LOCALECODE ,s.COMPLAINTTYPE ,s.COMPLAINTUSERAGENT from STG_AnalyticsEvents_EmailComplaint s left 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 o.rowid is null ; -------------------------------------------------------- -- 26-4 - Data insertion for Table AnalyticsEvents_EmailDelivery -------------------------------------------------------- insert into AnalyticsEvents_EmailDelivery ( PARTICIPANTIDENTIFIER ,EVENT_TIMESTAMP ,PARTICIPANTID ,EMAILEVENTKEY ,MAILDESTINATION ,MAILTIMESTAMP ,MAILSOURCE ,MAILMESSAGEID ,NOTIFICATIONIDENTIFIER ,FROMDOMAIN ,LOCALECODE ,DELIVERYRECIPIENT ,DELIVERYTIMESTAMP ) select s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") as EVENT_TIMESTAMP ,s.PARTICIPANTID ,s.EMAILEVENTKEY ,s.MAILDESTINATION ,TO_UTC_TIMESTAMP_TZ(s.MAILTIMESTAMP) as MAILTIMESTAMP ,s.MAILSOURCE ,s.MAILMESSAGEID ,s.NOTIFICATIONIDENTIFIER ,s.FROMDOMAIN ,s.LOCALECODE ,s.DELIVERYRECIPIENT ,TO_UTC_TIMESTAMP_TZ(s.DELIVERYTIMESTAMP) as DELIVERYTIMESTAMP from STG_AnalyticsEvents_EmailDelivery s left 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 o.rowid is null ; -------------------------------------------------------- -- 26-5 - Data insertion for Table AnalyticsEvents_EmailOpen -------------------------------------------------------- insert into AnalyticsEvents_EmailOpen ( PARTICIPANTIDENTIFIER ,EVENT_TIMESTAMP ,PARTICIPANTID ,EMAILEVENTKEY ,MAILDESTINATION ,MAILTIMESTAMP ,MAILSOURCE ,MAILMESSAGEID ,NOTIFICATIONIDENTIFIER ,FROMDOMAIN ,LOCALECODE ,OPENTIMESTAMP ,OPENUSERAGENT ,OPENIPADDRESS ) select s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") as EVENT_TIMESTAMP ,s.PARTICIPANTID ,s.EMAILEVENTKEY ,s.MAILDESTINATION ,TO_UTC_TIMESTAMP_TZ(s.MAILTIMESTAMP) as MAILTIMESTAMP ,s.MAILSOURCE ,s.MAILMESSAGEID ,s.NOTIFICATIONIDENTIFIER ,s.FROMDOMAIN ,s.LOCALECODE ,TO_UTC_TIMESTAMP_TZ(s.OPENTIMESTAMP) as OPENTIMESTAMP ,s.OPENUSERAGENT ,s.OPENIPADDRESS from STG_AnalyticsEvents_EmailOpen s left 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 o.rowid is null ; -------------------------------------------------------- -- 26-6 - Data insertion for Table AnalyticsEvents_EmailSend -------------------------------------------------------- insert into AnalyticsEvents_EmailSend ( PARTICIPANTIDENTIFIER ,EVENT_TIMESTAMP ,PARTICIPANTID ,EMAILEVENTKEY ,MAILDESTINATION ,MAILTIMESTAMP ,MAILSOURCE ,MAILMESSAGEID ,NOTIFICATIONIDENTIFIER ,FROMDOMAIN ,LOCALECODE ) select s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s."TIMESTAMP") as EVENT_TIMESTAMP ,s.PARTICIPANTID ,s.EMAILEVENTKEY ,s.MAILDESTINATION ,TO_UTC_TIMESTAMP_TZ(s.MAILTIMESTAMP) as MAILTIMESTAMP ,s.MAILSOURCE ,s.MAILMESSAGEID ,s.NOTIFICATIONIDENTIFIER ,s.FROMDOMAIN ,s.LOCALECODE from STG_AnalyticsEvents_EmailSend s left 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 o.rowid is null ; --- commit for all insertions! --- commit; end;