Remove Ineligible First Activity values from Opportunities

Removes First Activity (a422_FirstActivity and a422_FirstActivity_GUID) values from Opportunity if the current First Activity has an Activity Type (a422_rel_ActivityValueId) that is set as not eligible to be First Activity (a422_CanBeFirstActivity is No in the a422_dom_ActivityValue entity).

UPDATE o 
SET A422_FirstActivity = Null, A422_FirstActivity_GUID = Null FROM Opportunity AS o  
      LEFT JOIN Task AS t ON o.A422_FirstActivity_GUID = t.ActivityId  
LEFT JOIN FilteredA422_dom_activityvalue AS ta  
ON t.a422_rel_activityvalueid = ta.a422_dom_activityvalueid  
LEFT JOIN PhoneCall AS p ON o.A422_FirstActivity_GUID = p.ActivityId 
LEFT JOIN FilteredA422_dom_activityvalue AS pa  
ON p.a422_rel_activityvalueid = pa.a422_dom_activityvalueid  
LEFT JOIN Fax AS f ON o.A422_FirstActivity_GUID = f.ActivityId  
LEFT JOIN FilteredA422_dom_activityvalue AS fa  
ON f.a422_rel_activityvalueid = fa.a422_dom_activityvalueid  
LEFT JOIN Email AS e ON o.A422_FirstActivity_GUID = e.ActivityId   
LEFT JOIN FilteredA422_dom_activityvalue AS ea  
ON e.a422_rel_activityvalueid = ea.a422_dom_activityvalueid  
LEFT JOIN Letter AS l ON o.A422_FirstActivity_GUID = l.ActivityId   
LEFT JOIN FilteredA422_dom_activityvalue AS la  
ON l.a422_rel_activityvalueid = la.a422_dom_activityvalueid  
LEFT JOIN CampaignResponse AS c ON o.A422_FirstActivity_GUID = c.ActivityId  
LEFT JOIN FilteredA422_dom_activityvalue AS ca  
ON c.a422_rel_activityvalueid = ca.a422_dom_activityvalueid 
WHERE o.A422_FirstActivity_GUID Is Not Null  
AND(ta.a422_canbefirstactivityname = 'No' OR ta.a422_canbefirstactivityname Is Null)  
AND(pa.a422_canbefirstactivityname = 'No' OR pa.a422_canbefirstactivityname Is Null)  
AND(fa.a422_canbefirstactivityname = 'No' OR fa.a422_canbefirstactivityname Is Null)  
AND(ea.a422_canbefirstactivityname = 'No' OR ea.a422_canbefirstactivityname Is Null)  
AND(la.a422_canbefirstactivityname = 'No' OR la.a422_canbefirstactivityname Is Null)  
AND(ca.a422_canbefirstactivityname = 'No' OR ca.a422_canbefirstactivityname Is Null)

     


Comments