Steps to Complete in TouchPoint:
- Create Role lookup codes for each Designation/Set to share.
- Apply the role created in the prior step to the matching Fund.
- Apply the role to the relevant users. Be sure the user also has at least FinanceViewOnlyDetail and ViewTransactions.
- Add supplied SQL content to Special Content.
GetFundsByUser
--API
DECLARE @user_role_ids TABLE (RoleId INT)
INSERT INTO @user_role_ids (RoleId)
SELECT ur.RoleId
FROM dbo.UserRole AS ur
JOIN dbo.Users AS u ON u.UserId = ur.UserId
WHERE u.Username = @username
SELECT
c.ContributionId,
c.ContributionAmount,
c.ContributionDate,
c.TranId,
t.TransactionGateway,
cf.FundId,
cf.FundName,
p.PeopleId,
p.GenderId,
p.TitleCode,
p.FirstName,
p.MiddleName,
p.LastName,
p.SuffixCode,
p.CellPhone,
p.WorkPhone,
p.EmailAddress,
p.ModifiedDate,
p.PrimaryCity,
p.PrimaryZip,
p.PrimaryAddress,
p.PrimaryAddress2,
p.PrimaryState,
p.HomePhone,
p.PrimaryCountry,
p.Name
FROM [dbo].[Contribution] AS c
JOIN [dbo].[People] AS p on p.PeopleId = c.PeopleId
JOIN [dbo].[ContributionFund] AS cf on cf.FundId = c.FundId
JOIN [dbo].[Transaction] AS t ON t.Id = c.TranId
WHERE (cf.FundManagerRoleId IN (0, -1) OR cf.FundManagerRoleId IN (SELECT * FROM @user_role_ids)) AND cf.FundStatusId = 1 AND c.TranId IS NOT NULL
ORDER BY c.ContributionId
OFFSET CONVERT(INT, @skip_number) ROWS FETCH NEXT 1000 ROWS ONLY;
GetTotalFundsByUser
--API
DECLARE @user_role_ids TABLE (RoleId INT)
INSERT INTO @user_role_ids (RoleId)
SELECT ur.RoleId
FROM dbo.UserRole AS ur
JOIN dbo.Users AS u ON u.UserId = ur.UserId
WHERE u.Username = @username
SELECT Count(*) As total
FROM [dbo].[Contribution] AS c
JOIN [dbo].[People] AS p on p.PeopleId = c.PeopleId
JOIN [dbo].[ContributionFund] AS cf on cf.FundId = c.FundId
JOIN [dbo].[Transaction] AS t ON t.Id = c.TranId
WHERE (cf.FundManagerRoleId IN (0, -1) OR cf.FundManagerRoleId IN (SELECT * FROM @user_role_ids)) AND cf.FundStatusId = 1 AND c.TranId IS NOT NULL;
- Create the integration user with API Only & Developer role.
Steps to Launch:
- Via MissionPipe, share integration user and password with Solertiae.
- Access to a MissionPipe Zone was provided at the time of subscribing.
Considerations:
- User will need to enter the TouchPoint username when connecting from Partner Essentials.