USP_FAF_REGISTRANT_ROLE
This usp accepts registrant info (constituentID and eventID) and return role in output parameters
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @EVENTID | uniqueidentifier | IN | |
| @IsIndependentHouseholdMember | bit | INOUT | |
| @IsIndependentHouseholdLeader | bit | INOUT | |
| @IsIndependentTeamMember | bit | INOUT | |
| @IsIndependentTeamLeader | bit | INOUT | |
| @IsCompanyHouseholdMember | bit | INOUT | |
| @IsCompanyHouseholdLeader | bit | INOUT | |
| @IsCompanyTeamHouseholdLeader | bit | INOUT | |
| @IsCompanyTeamMember | bit | INOUT | |
| @IsCompanyTeamLeader | bit | INOUT | |
| @IsCompanyLeader | bit | INOUT | |
| @IsCompanyTeamHouseholdMember | bit | INOUT | |
| @IsTeamHouseholdMember | bit | INOUT | |
| @IsTeamHouseholdLeader | bit | INOUT | |
| @IsCompanyIndividual | bit | INOUT | |
| @IsIndependentIndividual | bit | INOUT |
Definition
Copy
CREATE procedure dbo.USP_FAF_REGISTRANT_ROLE
(
@CONSTITUENTID uniqueidentifier
,@EVENTID uniqueidentifier
,@IsIndependentHouseholdMember bit = NULL OUTPUT
,@IsIndependentHouseholdLeader bit = NULL OUTPUT
,@IsIndependentTeamMember bit = NULL OUTPUT
,@IsIndependentTeamLeader bit = NULL OUTPUT
,@IsCompanyHouseholdMember bit = NULL OUTPUT
,@IsCompanyHouseholdLeader bit = NULL OUTPUT
,@IsCompanyTeamHouseholdLeader bit = NULL OUTPUT
,@IsCompanyTeamMember bit = NULL OUTPUT
,@IsCompanyTeamLeader bit = NULL OUTPUT
,@IsCompanyLeader bit = NULL OUTPUT
,@IsCompanyTeamHouseholdMember bit = NULL OUTPUT
,@IsTeamHouseholdMember bit = NULL OUTPUT
,@IsTeamHouseholdLeader bit = NULL OUTPUT
,@IsCompanyIndividual bit = NULL OUTPUT
,@IsIndependentIndividual bit = NULL OUTPUT
--,@IsSponsor bit = NULL OUTPUT
)
as
begin
SELECT
@IsIndependentHouseholdMember = CASE WHEN FNTEAM.TEAMTYPECODE = 3 AND FNTEAM.PARENTTEAMID IS NULL AND TC.CONSTITUENTID is NULL THEN 1 else 0 END
,@IsIndependentHouseholdLeader = CASE WHEN FNTEAM.TEAMTYPECODE = 3 AND FNTEAM.PARENTTEAMID IS NULL AND TC.CONSTITUENTID is NOT NULL THEN 1 else 0 END
,@IsIndependentTeamMember = CASE WHEN FNTEAM.TEAMTYPECODE = 1 AND FNTEAM.PARENTTEAMID IS NULL AND TC.CONSTITUENTID IS NULL THEN 1 else 0 END
,@IsIndependentTeamLeader = CASE WHEN FNTEAM.TEAMTYPECODE = 1 AND FNTEAM.TEAMSTATUSCODE = 0 AND FNTEAM.PARENTTEAMID IS NULL AND TC.CONSTITUENTID IS NOT NULL THEN 1 else 0 END
,@IsCompanyHouseholdMember = CASE WHEN FNTEAM.TEAMTYPECODE = 3 AND TE.TYPECODE = 2 AND TC.CONSTITUENTID is NULL THEN 1 else 0 END
,@IsCompanyHouseholdLeader = CASE WHEN FNTEAM.TEAMTYPECODE = 3 AND TE.TYPECODE = 2 AND TC.CONSTITUENTID is NOT NULL THEN 1 else 0 END
,@IsCompanyTeamHouseholdLeader = CASE WHEN FNTEAM.TEAMTYPECODE = 3 AND TE.TYPECODE = 1 AND TE2.TYPECODE = 2 AND TC.CONSTITUENTID IS NOT NULL then 1 else 0 END
,@IsCompanyTeamHouseholdMember = CASE WHEN FNTEAM.TEAMTYPECODE = 3 AND TE.TYPECODE = 1 AND TE2.TYPECODE = 2 AND TC.CONSTITUENTID IS NULL then 1 else 0 END
,@IsCompanyTeamMember = CASE WHEN FNTEAM.TEAMTYPECODE = 1 AND TE.TYPECODE = 2 AND TC.CONSTITUENTID IS NULL then 1 else 0 END
,@IsCompanyTeamLeader = CASE WHEN FNTEAM.TEAMTYPECODE = 1 AND TE.TYPECODE = 2 AND FNTEAM.TEAMSTATUSCODE = 0 AND TC.CONSTITUENTID IS NOT NULL then 1 else 0 END
,@IsCompanyLeader = CASE WHEN FNTEAM.TEAMTYPECODE = 2 AND FNTEAM.TEAMSTATUSCODE = 0 AND FNTEAM.PARENTTEAMID IS NULL AND TC.CONSTITUENTID IS NOT NULL then 1 else 0 END
,@IsTeamHouseholdLeader = CASE WHEN FNTEAM.TEAMTYPECODE = 3 AND TE.TYPECODE = 1 AND TFT.PARENTTEAMID IS NULL AND TC.CONSTITUENTID IS NOT NULL then 1 else 0 END
,@IsTeamHouseholdMember = CASE WHEN FNTEAM.TEAMTYPECODE = 3 AND TE.TYPECODE = 1 AND TFT.PARENTTEAMID IS NULL AND TC.CONSTITUENTID IS NULL then 1 else 0 END
,@IsCompanyIndividual = CASE WHEN FNTEAM.TEAMTYPECODE = 2 AND FNTEAM.PARENTTEAMID IS NULL AND TC.CONSTITUENTID IS NULL then 1
WHEN FNTEAM.TEAMTYPECODE = 1 AND TE.TYPECODE = 2 AND FNTEAM.TEAMSTATUSCODE IN (1,2) AND TC.CONSTITUENTID IS NOT NULL then 1
ELSE 0 END
,@IsIndependentIndividual = CASE WHEN FNTEAM.RegistrantID IS NOT NULL AND FNTEAM.TEAMID IS NULL then 1
WHEN FNTEAM.TEAMTYPECODE = 1 AND FNTEAM.TEAMSTATUSCODE IN (1,2) AND FNTEAM.PARENTTEAMID IS NULL AND TC.CONSTITUENTID IS NOT NULL THEN 1
WHEN FNTEAM.TEAMTYPECODE = 2 AND FNTEAM.TEAMSTATUSCODE IN (1,2) AND FNTEAM.PARENTTEAMID IS NULL AND TC.CONSTITUENTID IS NOT NULL THEN 1
ELSE 0 END
--,@IsSponsor = CASE WHEN ES.CONSTITUENTID IS NOT NULL then 1 else 0 END
FROM dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID) FNTEAM
LEFT OUTER JOIN dbo.TEAMFUNDRAISINGTEAM TFT
ON FNTEAM.PARENTTEAMID = TFT.ID
LEFT OUTER JOIN dbo.TEAMEXTENSION TE
ON TFT.ID = TE.TEAMFUNDRAISINGTEAMID
LEFT OUTER JOIN dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC
ON FNTEAM.TEAMID = TC.TEAMFUNDRAISINGTEAMID
AND TC.CONSTITUENTID = @CONSTITUENTID
LEFT OUTER JOIN dbo.TEAMFUNDRAISINGTEAM TFT2
ON TFT.PARENTTEAMID = TFT2.ID
LEFT OUTER JOIN dbo.TEAMEXTENSION TE2
ON TFT2.ID = TE2.TEAMFUNDRAISINGTEAMID
--LEFT OUTER JOIN dbo.EVENTSPONSOR ES
--ON ES.CONSTITUENTID = @CONSTITUENTID
--AND ES.EVENTID = @EVENTID
end