/* Alternate Channel De-Duplication - Marketing Automation/Preference Centre
Business Owners: Jessica Wood, Alain Gasquet
Developer Notes:
Records were trimmed and cleaned using regular expression replace
If number starts with +612, +6102, +613, +6103, +614, +6104, +617, +6107, +618, +6108 the numbers were considered Australian and +61 was replaced by 0.
For each unique formatted phone number per contact, sorted in descending order, dupliacate records were identified if there was an exact match.
-- Modified to monitor new duplicate records created in Alternate Channel
*/
--create or replace view spSP_advancementADVANCEMENT_dbDB.tdaTDA_schSCH.altchannelALTCHANNEL_phonePHONE_dedupeDEDUPE_vwVW
as
with aq_contact_duplicates as(
with ac_primary as
(
select ac.id "Alt Channel SF ID:Primary",
ac.name "Alt Channel Name:Primary",
ac.aqb__contact__c "ContactID:Primary",
ac.aqb__statusaccount__c "StatusAccountID:Primary",
regexp_replace(split_part(ac.aqb__typelinkedto__c "Type , '"',2), '/','') "Linked To:Primary",
ac.aqb__phonestatus__c "PhoneStatus:Primary",
ac.aqb__type__c "Type:Primary",
case
ac.aqb__phone__c "Phone:Primary",
when
startswith("Formatted case
when startswith("Formatted Phone", '0011') then regexp_replace("Formatted Phone", '^(0011)', '')
when startswith("Formatted Phone", '000') then regexp_replace("Formatted Phone", '^(000)', '0')
when startswith("Formatted Phone", '00') then regexp_replace("Formatted Phone", '^(00)', '0')
when startswith("Formatted Phone", '612') then regexp_replace("Formatted Phone", '^(612)', '02')
when startswith("Formatted Phone", '613') then regexp_replace("Formatted Phone", '^(613)', '03')
when startswith("Formatted Phone", '614') then regexp_replace("Formatted Phone", '^(614)', '04')
when startswith("Formatted Phone", '617') then regexp_replace("Formatted Phone", '^(617)', '07')
when startswith("Formatted Phone", '618') then regexp_replace("Formatted Phone", '^(618)', '08')
when startswith("Formatted Phone", '6102') then regexp_replace("Formatted Phone", '^(6102)', '02')
when startswith("Formatted Phone", '6103') then regexp_replace("Formatted Phone", '^(6103)', '03')
when startswith("Formatted Phone", '6104') then regexp_replace("Formatted Phone", '^(6104)', '04')
when startswith("Formatted Phone", '6107') then regexp_replace("Formatted Phone", '^(6107)', '07')
when startswith("Formatted Phone", '6108') then regexp_replace("Formatted Phone", '^(6108)', '08')
else "Formatted Phone"
end "Formatted Phone:Primary",
ac.createddate "CreatedDate:Primary",
ac.lastmodifieddate "LastModifiedDate:Primary",
u.name "CreatedBy:Primary",
u1.name "LastModifiedBy:Primary",
ac.aqb__source__c "Source:Primary",
ac.aqb__linkedto__c, row_number() over(partition by "ContactIDLinked To:Primary", "Formatted Phone:Primary" order by "CreatedDateLastModifiedDate:Primary" desc ) seq
from(select a.*,
regexp_replace(lower(trim(a.aqb__phone__c)), '[^a-z0-9]', '') "Formatted Phone"
from salesforce_db.salesforce_sch.aqb__aqaddress__c a
)ac
left join salesforce_db.salesforce_sch.recordtype r on r.id = ac.recordtypeid
left join salesforce_db.salesforce_sch.user u on u.id = ac.createdbyid
left join salesforce_db.salesforce_sch.user u1 on u1.id = ac.lastmodifiedbyid
left join salesforce_db.salesforce_sch.contact c on left(c.id,15) = ac.aqb__contact__c "Linked To:Primary"
left join salesforce_db.salesforce_sch.account a on a.id = ac.aqb__account__c
where "ContactID:Primary" isc.aqb__type__c not nullin ('Student', 'Graduand') and
and r.name = 'Phone'
and c.aqb__type__c <> 'Student'
-- and "Status("Linked To:Primary" = 'Good'
--and a.aqb__accounttype__c = 'Household' like '003%')
order by 3,7,14
),
ac_dupes as
(
select ac.id "Alt Channel SF ID:Dupe",
ac.name "Alt Channel Name:Dupe",
ac.aqb__contact__c "ContactID:Dupe",
regexp_replace(split_part(ac.aqb__statuslinkedto__c , '"',2), '/','') "Linked To:Dupe",
ac.aqb__status__c "Status:Dupe",
ac.aqb__type__c "Type:Dupe",
ac.aqb__phone__c "Phone:Dupe",
case
when startswith("Formatted Phone", '0011') then regexp_replace("Formatted Phone", '^(0011)', '')
when startswith("Formatted Phone", '000') then regexp_replace("Formatted Phone", '^(000)', '0')
when startswith("Formatted Phone", '00') then regexp_replace("Formatted Phone", '^(00)', '0')
when startswith("Formatted Phone", '612') then regexp_replace("Formatted Phone", '^(612)', '02')
when startswith("Formatted Phone", '613') then regexp_replace("Formatted Phone", '^(613)', '03')
when startswith("Formatted Phone", '614') then regexp_replace("Formatted Phone", '^(614)', '04')
when startswith("Formatted Phone", '617') then regexp_replace("Formatted Phone", '^(617)', '07')
when startswith("Formatted Phone", '618') then regexp_replace("Formatted Phone", '^(618)', '08')
when startswith("Formatted Phone", '6102') then regexp_replace("Formatted Phone", '^(6102)', '02')
when startswith("Formatted Phone", '6103') then regexp_replace("Formatted Phone", '^(6103)', '03')
when startswith("Formatted Phone", '6104') then regexp_replace("Formatted Phone", '^(6104)', '04')
when startswith("Formatted Phone", '6107') then regexp_replace("Formatted Phone", '^(6107)', '07')
when startswith("Formatted Phone", '6108') then regexp_replace("Formatted Phone", '^(6108)', '08')
else "Formatted Phone"
end "Formatted Phone:Dupe",
ac.createddate "CreatedDate:Dupe",
ac.lastmodifieddate "LastModifiedDate:Dupe",
u.name "CreatedBy:Dupe",
u1.name "LastModifiedBy:Dupe",
ac.aqb__source__c "Source:Dupe",
ac.aqb__linkedto__c,
row_number() over(partition by "ContactIDLinked To:Dupe" order by "CreatedDateLastModifiedDate:Dupe" desc ) seq
from(select a.*,
regexp_replace(lower(trim(a.aqb__phone__c)), '[^a-z0-9]', '') "Formatted Phone"
from salesforce_db.salesforce_sch.aqb__aqaddress__c a
)ac
left join salesforce_db.salesforce_sch.recordtype r on r.id = ac.recordtypeid
left join salesforce_db.salesforce_sch.user u on u.id = ac.createdbyid
left join salesforce_db.salesforce_sch.user u1 on u1.id = ac.lastmodifiedbyid
left join salesforce_db.salesforce_sch.contact c on left(c.id,15) = ac.aqb__contact__c "Linked To:Dupe"
left join salesforce_db.salesforce_sch.account a on a.id = ac.aqb__account__c
where "ContactID:Dupe" is not null
c.aqb__type__c not in ('Student', 'Graduand') and
and r.name = 'Phone' and ("Linked To:Dupe" and c.aqb__type__c <> 'Student'
like '003%')
--and a.aqb__accounttype__c = 'Household'
),
Dupes as
( select *
from (
select * from ac_primary acp
where acp.seq = 1
)dd
join ac_dupes acd on "ContactIDLinked To:Primary" = "ContactIDLinked To:Dupe"
where "Alt Channel Name:Primary" <> "Alt Channel Name:Dupe"
and "Formatted Phone:Primary" like= "Formatted Phone:Dupe"
order by 2,3,1
)
select "Alt Channel SF ID:Primary",
"Alt Channel Name:Primary",
"ContactID:Primary",
"AccountID:Primary",
"Phone:Primary",
"Formatted Phone:Primary",
"Status:Primary",
"Type:Primary",
"LastModifiedDate:Primary",
"CreatedDate:Primary",
"LastModifiedBy:Primary",
"CreatedBy:Primary",
"Source:Primary",
listagg("Alt Channel SF ID:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc)"Alt Channel ID:Dupes",
listagg("Alt Channel Name:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Alt Channel Names:Dupes",
listagg("Phone:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Phones:Dupes",
listagg("Formatted Phone:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Formatted Phones:Dupes",
listagg("Status:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Status:Dupes",
listagg("Type:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Type:Dupes",
listagg("CreatedBy:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "CreatedBy:Dupes",
listagg("CreatedDate:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "CreatedDate:Dupes",
listagg("LastModifiedBy:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "LastModifiedBy:Dupe",
listagg("LastModifiedDate:Dupe",',') within group (order by "LastModifiedDate:Dupe" desc) "LastModifiedDate:Dupe",
listagg("Source:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Source:Dupes",
count("Alt Channel SF ID:Dupe") "No of Dupes:Dupes"
from dupes d
--where "group by all
order by 2,3,1
),
------------------------------------------
/* Account Phone duplicates in Alternate Channel */
aq_account_duplicates as(
with account_primary as
(
select ac.id "Alt Channel SF ID:Primary",
ac.name "Alt Channel Name:Primary",
ac.aqb__contact__c "ContactID:Primary",
ac.aqb__account__c "AccountID:Primary",
regexp_replace(split_part(ac.aqb__linkedto__c , '"',2), '/','') "Linked To:Primary",
ac.aqb__status__c "Status:Primary",
ac.aqb__type__c "Type:Primary",
ac.aqb__phone__c "Phone:Primary",
case
when startswith("Formatted Phone", '0011') then regexp_replace("Formatted Phone", '^(0011)', '')
when startswith("Formatted Phone", '000') then regexp_replace("Formatted Phone", '^(000)', '0')
when startswith("Formatted Phone", '00') then regexp_replace("Formatted Phone", '^(00)', '0')
when startswith("Formatted Phone", '612') then regexp_replace("Formatted Phone", '^(612)', '02')
when startswith("Formatted Phone", '613') then regexp_replace("Formatted Phone", '^(613)', '03')
when startswith("Formatted Phone", '614') then regexp_replace("Formatted Phone", '^(614)', '04')
when startswith("Formatted Phone", '617') then regexp_replace("Formatted Phone", '^(617)', '07')
when startswith("Formatted Phone", '618') then regexp_replace("Formatted Phone", '^(618)', '08')
when startswith("Formatted Phone", '6102') then regexp_replace("Formatted Phone", '^(6102)', '02')
when startswith("Formatted Phone", '6103') then regexp_replace("Formatted Phone", '^(6103)', '03')
when startswith("Formatted Phone", '6104') then regexp_replace("Formatted Phone", '^(6104)', '04')
when startswith("Formatted Phone", '6107') then regexp_replace("Formatted Phone", '^(6107)', '07')
when startswith("Formatted Phone", '6108') then regexp_replace("Formatted Phone", '^(6108)', '08')
else "Formatted Phone"
end "Formatted Phone:Primary",
ac.createddate "CreatedDate:Primary",
ac.lastmodifieddate "LastModifiedDate:Primary",
u.name "CreatedBy:Primary",
u1.name "LastModifiedBy:Primary",
ac.aqb__source__c "Source:Primary",
ac.aqb__linkedto__c,
row_number() over(partition by "Linked To:Primary", "Formatted Phone:Primary" order by "LastModifiedDate:Primary" desc ) seq
from(select a.*,
regexp_replace(lower(trim(a.aqb__phone__c)), '[^a-z0-9]', '') "Formatted Phone"
from salesforce_db.salesforce_sch.aqb__aqaddress__c a
)ac
left join salesforce_db.salesforce_sch.recordtype r on r.id = ac.recordtypeid
left join salesforce_db.salesforce_sch.user u on u.id = ac.createdbyid
left join salesforce_db.salesforce_sch.user u1 on u1.id = ac.lastmodifiedbyid
left join salesforce_db.salesforce_sch.contact c on left(c.id,15) = ac.aqb__linkedto__c
left join salesforce_db.salesforce_sch.account a on left(a.id,15) = "Linked To:Primary"
where c.aqb__type__c not in ('Student', 'Graduand') and r.name = 'Phone' and ("Linked To:Primary" like '001%')
order by 3,7,14
),
account_dupes as
(
select ac.id "Alt Channel SF ID:Dupe",
ac.name "Alt Channel Name:Dupe",
ac.aqb__contact__c "ContactID:Dupe",
ac.aqb__account__c "AccountID:Dupe",
regexp_replace(split_part(ac.aqb__linkedto__c , '"',2), '/','') "Linked To:Dupe",
ac.aqb__status__c "Status:Dupe",
ac.aqb__type__c "Type:Dupe",
ac.aqb__phone__c "Phone:Dupe",
case
when startswith("Formatted Phone", '0011') then regexp_replace("Formatted Phone", '^(0011)', '')
when startswith("Formatted Phone", '000') then regexp_replace("Formatted Phone", '^(000)', '0')
when startswith("Formatted Phone", '00') then regexp_replace("Formatted Phone", '^(00)', '0')
when startswith("Formatted Phone", '612') then regexp_replace("Formatted Phone", '^(612)', '02')
when startswith("Formatted Phone", '613') then regexp_replace("Formatted Phone", '^(613)', '03')
when startswith("Formatted Phone", '614') then regexp_replace("Formatted Phone", '^(614)', '04')
when startswith("Formatted Phone", '617') then regexp_replace("Formatted Phone", '^(617)', '07')
when startswith("Formatted Phone", '618') then regexp_replace("Formatted Phone", '^(618)', '08')
when startswith("Formatted Phone", '6102') then regexp_replace("Formatted Phone", '^(6102)', '02')
when startswith("Formatted Phone", '6103') then regexp_replace("Formatted Phone", '^(6103)', '03')
when startswith("Formatted Phone", '6104') then regexp_replace("Formatted Phone", '^(6104)', '04')
when startswith("Formatted Phone", '6107') then regexp_replace("Formatted Phone", '^(6107)', '07')
when startswith("Formatted Phone", '6108') then regexp_replace("Formatted Phone", '^(6108)', '08')
else "Formatted Phone"
end "Formatted Phone:Dupe",
ac.createddate "CreatedDate:Dupe",
ac.lastmodifieddate "LastModifiedDate:Dupe",
u.name "CreatedBy:Dupe",
u1.name "LastModifiedBy:Dupe",
ac.aqb__source__c "Source:Dupe",
ac.aqb__linkedto__c,
row_number() over(partition by "Linked To:Dupe" order by "LastModifiedDate:Dupe" desc ) seq
from(select a.*,
regexp_replace(lower(trim(a.aqb__phone__c)), '[^a-z0-9]', '') "Formatted Phone"
from salesforce_db.salesforce_sch.aqb__aqaddress__c a
)ac
left join salesforce_db.salesforce_sch.recordtype r on r.id = ac.recordtypeid
left join salesforce_db.salesforce_sch.user u on u.id = ac.createdbyid
left join salesforce_db.salesforce_sch.user u1 on u1.id = ac.lastmodifiedbyid
left join salesforce_db.salesforce_sch.contact c on left(c.id,15) = ac.aqb__linkedto__c
left join salesforce_db.salesforce_sch.account a on left(a.id,15) = "Linked To:Dupe"
where c.aqb__type__c not in ('Student', 'Graduand') and
r.name = 'Phone' and ("Linked To:Dupe" like '001%')
),
Dupes as
( select * from (
select * from account_primary acp
where acp.seq = 1
)dd
join account_dupes acd on "Linked To:Primary" = "Linked To:Dupe"
where "Alt Channel Name:Primary" <> "Alt Channel Name:Dupe"
and "Formatted Phone:Primary" = "Formatted Phone:Dupe"
order by 2,3,1
)
select "Alt Channel SF ID:Primary",
"Alt Channel Name:Primary",
"ContactID:Primary",
"AccountID:Primary",
"Phone:Primary",
"Formatted Phone:Primary",
"Status:Primary",
"Type:Primary",
"LastModifiedDate:Primary",
"CreatedDate:Primary",
"LastModifiedBy:Primary",
"CreatedBy:Primary",
"Source:Primary",
listagg("Alt Channel SF ID:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc)"Alt Channel ID:Dupes",
listagg("Alt Channel Name:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Alt Channel Names:Dupes",
listagg("Phone:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Phones:Dupes",
listagg("Formatted Phone:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Formatted Phones:Dupes",
listagg("Status:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Status:Dupes",
listagg("Type:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Type:Dupes",
listagg("CreatedBy:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "CreatedBy:Dupes",
listagg("CreatedDate:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "CreatedDate:Dupes",
listagg("LastModifiedBy:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "LastModifiedBy:Dupe",
listagg("LastModifiedDate:Dupe",',') within group (order by "LastModifiedDate:Dupe" desc) "LastModifiedDate:Dupe",
listagg("Source:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Source:Dupes",
count("Alt Channel SF ID:Dupe") "No of Dupes"
from dupes d
--where "CreatedBy:Primary" = 'mule_svc'
group by all
order by 2,3,1
),
Segment_01 as
(
select '01' "Segment 01 Order",'Contact Primary Phone Created by User' "Segment Name",
d.*, '' "LastModifiedDate Name",
acd.*, split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1",
split_part("Alt Channel ID:Dupes", ',', 1) "Alt Channel ID:Dupe1"
from aq_contact_duplicates dacd
where -- "CreatedBy:Primary" <>'mule_svc'No of Dupes" = 1 and
"CreatedBy:Primary" not =in ('AQConversionmule_svc', and'Student "No of Dupes:Dupes" = 1
),Integration User')
),
Segment_02 as
(
select '02' "Segment 02 Order",'Contact Primary Phone created by integration' "Segment Name",
dacd.*,
split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1" from duplicates d
where "No of Dupes:Dupes" = 1
and,
split_part("Alt Channel ID:Dupes", ',', -1) "Alt Channel ID:Dupe1"
from aq_contact_duplicates acd
where "CreatedBy:Primary" not in ('mule_svc', 'AQConversionStudent Integration User')
),
Segment_03 as
(
select 'Segment 03
(select '03' "Segment Order",'Account Primary Phone Created by User' "Segment Name",
daad.*, split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1",
split_part("Alt Channel ID:Dupes", ',', 1) "Alt Channel ID:Dupe1"
from aq_account_duplicates daad
where --"No of Dupes:Dupes" >= 1
and
"CreatedBy:Primary" not <>in ('mule_svc', 'Student Integration User')
),
Segment_04 as
Segment_04 as
(
select 'Segment 04(
select '04' "Segment Order",'Account Primary Phone created by mule_svc' "Segment Name",
daad.*,
split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1",
split_part("Alt Channel ID:Dupes", ',', -1) "Alt Channel ID:Dupe1"
from aq_account_duplicates daad
where "CreatedBy:Primary" = in('Student Integration User', 'mule_svc')
)
select * from ( select * from Segment_01
union all
select * from Segment_02
union all
select * from Segment_03
union all
select * from Segment_04
); |