CRM has a feature where we can share bulk records with users
at one time. But if we have achieved the same in some automation process, CRM
service will not respond (or it will take more time, CRM may stop working for some
time).
I had a similar requirement like when sale rep moves from
one city to another provide him/her the access of all records belongs to the
new city and unshared all records were shared with him/her before. To achieve
the same I wrote an SQL query to do the same.
Declare @SystemUser nvarchar(100)
Declare @ObjectId nvarchar(100)
DECLARE @Count INT
set @SystemUser = (select systemuserid from systemuser where Name ='CRM sales Rep')
declare Cur_SHAREWITHUSER cursor for
Select accountId from account where Account.Address1_City = 'Bangalore'
open Cur_SHAREWITHUSER
fetch Cur_SHAREWITHUSER into @ObjectId
while(@@fetch_status=0)
BEGIN
SET @Count = 0
SET @Count = (SELECT COUNT(1) FROM PrincipalObjectAccess WHERE ObjectId = @ObjectId AND PrincipalId = @SystemUser)
IF(@Count >0)
BEGIN
UPDATE PrincipalObjectAccess
SET AccessRightsMask= 23, -- define access level
InheritedAccessRightsMask=134217751,
ChangedOn = GETUTCDATE()
WHERE ObjectId = @ObjectId AND PrincipalId = @SystemUser
END
ELSE
BEGIN
INSERT INTO PrincipalObjectAccess
(Principalid,ObjectId,ObjectTypeCode,[PrincipalTypeCode],[AccessRightsMask],ChangedOn,
[InheritedAccessRightsMask] ,PrincipalObjectAccessId )
values (@SystemUser , @ObjectId ,1,8,23,GETDATE(),134217751,NEWID())
END
fetch Cur_SHAREWITHUSER into @ObjectId
END
close Cur_SHAREWITHUSER
deallocate Cur_SHAREWITHUSER
No comments:
Post a Comment