KNxSF - Kinetic SalesRep to Salesforce User matching

How are Kinetic SalesReps matched with Salesforce Users

Functional Summary

SalesReps are associated with Customers and ShipTos in Kinetic.

Users are assigned as owners for accounts in Salesforce

These two fields sync in both directions when changed

How Salesforce Users are found when syncing from Kinetic to Salesforce

The integration finds the SalesRep associated with the Customer/ShipTo.

The UserID is found by linking from the SalesRep's EMailAddress to a list of user codes with pairs Salesforce UserIDs and the email associated with that user.

That UserID is placed as the user ID in the API call made to Salesforce.

This sets the owner on the account on the

How Kinetic SalesReps are found when syncing from Salesforce to Kinetic

The Salesforce API sends the User ID for the account owner.

The Salesforce user ID is used to find the EmailAddress associated with it by referencing a of user codes with pairs Salesforce UserIDs and the email associated with that user.

The first SalesRep with the Salesforce EmailAddress is assigned to the Customer/ShipTo in Kinetic. (Note: If the EmailAddress exists on multiple SalesReps the first alphabetical SalesRepCode is used.)

Useful Queries

Configured Salesforce Users

SELECT ud.CodeDesc AS 'Salesforce User Email', ud.LongDesc AS 'Salesforce User ID', COUNT(*) AS 'Company Count'
FROM Ice.UDCodes ud
WHERE ud.CodeTypeID = 'SF-SalRep'
GROUP BY ud.CodeDesc, ud.LongDesc

Configured Salesforce Users without matching Sales Reps

SELECT ud.CodeDesc AS 'Salesforce User Email', ud.LongDesc AS 'Salesforce User ID'
FROM Ice.UDCodes ud
LEFT JOIN Erp.SalesRep sr
 ON  ud.CodeDesc = sr.EMailAddress
WHERE ud.CodeTypeID = 'SF-SalRep'
	AND ud.Company = 'GB'
  AND sr.Company IS NULL;

Sales Reps without matching Configured Salesforce Users

SELECT sr.InActive, sr.Company, sr.SalesRepCode, sr.Name, sr.EMailAddress 
FROM Erp.SalesRep sr
LEFT JOIN Ice.UDCodes ud 
	ON sr.Company = ud.Company 
	AND sr.EMailAddress = ud.CodeDesc 
	AND ud.CodeTypeID = 'SF-SalRep'
WHERE ud.Company IS NULL

Customers associated with Sales Reps without matching Configured Salesforce Users

SELECT c.Company, c.CustID, c.CustNum, c.Name, c.SalesRepCode 
FROM Erp.Customer c
WHERE c.Address1 <> 'Dummy Customer'
AND c.SalesRepCode IN
(SELECT sr.SalesRepCode
	FROM Erp.SalesRep sr
	LEFT JOIN Ice.UDCodes ud 
		ON sr.Company = ud.Company 
		AND sr.EMailAddress = ud.CodeDesc 
		AND ud.CodeTypeID = 'SF-SalRep'
	WHERE ud.Company IS NULL
	AND sr.Company = c.Company)

ShipTos associated with Sales Reps without matching Configured Salesforce Users

SELECT st.Company, st.CustNum, st.ShipToNum, st.Name,st.SalesRepCode 
FROM Erp.ShipTo st
WHERE st.Address1 <> 'Dummy Customer'
AND st.ShipToNum <> ''
AND st.SalesRepCode IN
(SELECT sr.SalesRepCode
	FROM Erp.SalesRep sr
	LEFT JOIN Ice.UDCodes ud 
		ON sr.Company = ud.Company 
		AND sr.EMailAddress = ud.CodeDesc 
		AND ud.CodeTypeID = 'SF-SalRep'
	WHERE ud.Company IS NULL
	AND sr.Company = st.Company)

Email address appears on multiple sales reps

SELECT sr.EMailAddress, sr.*    
FROM Erp.SalesRep sr    
JOIN (  
    SELECT  
        Company,    
        EMailAddress    
    FROM Erp.SalesRep   
    GROUP BY    
        Company,    
        EMailAddress    
    HAVING COUNT(*) > 1 
) d 
    ON sr.Company = d.Company   
   AND sr.EMailAddress = d.EMailAddress 
ORDER BY    
    sr.Company, 
    sr.EMailAddress;    

Email address appears on multiple active sales reps

SELECT sr.EMailAddress, sr.*    
FROM Erp.SalesRep sr    
JOIN (  
    SELECT  
        Company,    
        EMailAddress    
    FROM Erp.SalesRep   
    WHERE Inactive = 0
    GROUP BY    
        Company,    
        EMailAddress    
    HAVING COUNT(*) > 1 
) d 
    ON sr.Company = d.Company   
   AND sr.EMailAddress = d.EMailAddress 
WHERE Inactive = 0  
ORDER BY    
    sr.Company, 
    sr.EMailAddress;    

Active customers with inactive sales reps

SELECT *    
FROM Erp.Customer c 
INNER JOIN Erp.SalesRep sr ON c.Company = sr.Company AND c.SalesRepCode = sr.SalesRepCode   
INNER JOIN Erp.Customer_UD cud ON c.SysRowID = cud.ForeignSysRowID  
WHERE cud.CheckBox01 = 0    
AND sr.Inactive = 1 

Active ship tos with inactive sales reps

SELECT *    
FROM Erp.ShipTo st  
INNER JOIN Erp.SalesRep sr ON st.Company = sr.Company AND st.SalesRepCode = sr.SalesRepCode 
INNER JOIN Erp.ShipTo_UD stud ON st.SysRowID = stud.ForeignSysRowID 
WHERE stud.CheckBox01 = 0   
AND sr.Inactive = 1