Description:
In this example we explain that how to Retrieve all
the Access Team members for all Accounts in Dynamic CRM. Or how to get all the
Sales Team Grid Member of the Opportunity of the Dynamics CRM.
SQL Query to get the Sales Team Member List of the
Opportunity of Dynamic CRM.
To solve above all question first you have to
understand the following Table in CRM.
·
PrincipalObjectAccess
·
TempBase
·
TeamMemberShip
·
TeamTemplate
·
SystemUser
Use the below query to retrieve all Team Member for
Particular Accounts
SELECT a.Name AS AccountName,a.AccountId, t.Name AS TeamName, u.FirstName, u.LastName, tt.TeamTemplateName
FROM Account AS
a INNER JOIN
PrincipalObjectAccess
AS poa ON a.AccountId = poa.ObjectId INNER JOIN
TeamBase AS t ON poa.PrincipalId = t.TeamId INNER JOIN
TeamMembership AS tm ON t.TeamId = tm.TeamId INNER JOIN
SystemUser AS u ON tm.SystemUserId = u.SystemUserId INNER JOIN
TeamTemplate AS
tt ON t.TeamTemplateId
= tt.TeamTemplateId
Use the below query to retrieve all Sales Team Member
for Particular Opportunity
select
OpportunityId,
dbo.fn_new_GetSalesTeamForOpportunity(OpportunityId) as 'Sales Team',
from
Opportunity
below is the
function that returns list of Sales Team member for particular Opportunity
CREATE FUNCTION fn_new_GetSalesTeamForOpportunity
(
@OpportunityId UniqueIdentifier
)
RETURNS VARCHAR(MAX)
AS
BEGIN
return (SELECT STUFF((SELECT ',' + u.FirstName+u.LastName
FROM
Opportunity AS a INNER
JOIN
PrincipalObjectAccess
AS poa ON a.OpportunityId = poa.ObjectId INNER JOIN
TeamBase AS t ON poa.PrincipalId = t.TeamId INNER JOIN
TeamMembership AS tm ON t.TeamId = tm.TeamId INNER JOIN
SystemUser AS u ON tm.SystemUserId = u.SystemUserId INNER JOIN
TeamTemplate AS tt ON t.TeamTemplateId = tt.TeamTemplateId where
a.OpportunityId =
@OpportunityId
FOR XML PATH('')), 1, 1, '') as 'Sales Team')
END
GO
