Recipient field on Phonecall is a business required field. So there are rare chances that it will be left blank on phonecalls.
But sometimes, rare-ities come into picture and become a monster.
That’s what happened to me and I got few corrupted records (duplicates to existing phonecalls) where the recipient on phonecalls were missing. Activities created in the name of user (isworkflow created as 0, which means user created).
My first guess was that probably these are created by a plugin. But alas, no plugin was registered on phonecall.
I suspect that outlook plugin misfired in this scenario (as user who got these gift package of huge number of activities had some outlook issues same day). What makes me think this is : All those gift items were packaged in less than 2 minutes. 
So, it was sure, that user has not done it himself (Sorry for that PJ ).
Now, coming to main point. I wanted to clear my system with this junk. So first step was to query for such records.
That was another issue, as you cant directly query phonecalls based on recipient when recipient is missing (As CRM does not create a respective record for missing party items in Party Table).
Here’s the query to do that :
Approach 1 :
IF OBJECT_ID(‘tempdb..#tmpTemporary’) IS NOT NULL DROP TABLE #tmpTemporary
CREATE TABLE #tmpTemporary(CountParty int, ActivityIds nvarchar(50), Subject nvarchar (200),Owner nvarchar(100), createOnDate datetime, regardingName nvarchar(100))
 
Insert into #tmpTemporary
 select count(b.activityid) as ‘CountParty’, a.activityid, subject, owneridname, createdon,regardingobjectidname from FilteredActivityParty a
left join filteredactivitypointer b
on a.activityid = b.activityid
where b.activitytypecodename like ‘%phone%’
group by a.activityid, subject, owneridname, createdon, regardingobjectidname
 
select * from #tmpTemporary
where CountParty not in ( ‘4’, ‘5’, ‘2’)
and regardingName is not null
union
select * from #tmpTemporary
where CountParty not in ( ‘4’, ‘5’, ‘3’)
and regardingName is null
order by createOnDate desc
 
Approach 2 :
Thanks to some learned people in this world, they helped me get a simpler version of this query.
select     *
from       FilteredPhoneCall pc left outer join FilteredActivityParty ap
                      on (pc.activityid = ap.activityid and ap.participationtypemaskname = ‘To Recipient’)
where      ap.activityid is null
 
As my standard saying goes :
Hope it Helps someone 🙂
 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s