USP_DATALIST_FAF_PARTICIPANT_CONSTITUENT

      CREATE procedure dbo.USP_DATALIST_FAF_PARTICIPANT_CONSTITUENT
        ( 
          @CLIENTUSERSID int,
          @EVENTID uniqueidentifier
        )
      as
          set nocount on;

        DECLARE @CONSTITUENTID uniqueidentifier
          SET @CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID)

        DECLARE @ORGANIZATIONCONSTITUENTID uniqueidentifier, @RELATIONSHIPTYPECODEID uniqueidentifier
        SET @RELATIONSHIPTYPECODEID = (SELECT top 1 PRIMARYBUSINESSRECIPROCALTYPECODEID from dbo.NETCOMMUNITYDEFAULTCODEMAP(nolock));

        SELECT @ORGANIZATIONCONSTITUENTID = (SELECT top 1 RECIPROCALCONSTITUENTID from dbo.RELATIONSHIP WHERE RELATIONSHIPCONSTITUENTID=@CONSTITUENTID and RELATIONSHIPTYPECODEID=@RELATIONSHIPTYPECODEID);

          SELECT RG.ID as [REGISTRANTID],
        @CONSTITUENTID as CONSTITUENTID,
        (CASE WHEN TC.ID IS NULL AND TE.TYPECODE =2 THEN NULL ELSE TE.TEAMCONSTITUENTID END) as TEAMCONSTITUENTID,
        TFT.ID as [TEAMID],
        TFT.Name AS [TEAMNAME] ,
        TE.TYPE [TEAMTYPE],
        TFT.PARENTTEAMID as [PARENTTEAMID],
        EA.EMAILADDRESS,
        C.NAME as [NAME],
        @ORGANIZATIONCONSTITUENTID as ORGANIZATIONCONSTITUENTID
        FROM dbo.REGISTRANT RG    
        inner join dbo.EVENT EV ON EV.ID = RG.EVENTID  
        inner join dbo.CONSTITUENT C ON C.ID=RG.CONSTITUENTID 
        left outer join dbo.TEAMFUNDRAISER TF on TF.CONSTITUENTID=C.ID AND TF.APPEALID=EV.APPEALID 
        left outer join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFTM.TEAMFUNDRAISERID =TF.ID 
        left outer join dbo.TEAMFUNDRAISINGTEAM TFT ON TFT.ID=TFTM.TEAMFUNDRAISINGTEAMID 
        left outer join dbo.TEAMEXTENSION TE 
          ON TE.TEAMFUNDRAISINGTEAMID=TFT.ID
        LEFT OUTER JOIN dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC
          ON TFT.ID = TC.TEAMFUNDRAISINGTEAMID
          AND TC.CONSTITUENTID = @CONSTITUENTID
        LEFT OUTER JOIN dbo.EMAILADDRESS EA
          ON EA.CONSTITUENTID = C.ID
          AND EA.ISPRIMARY = 1
        WHERE C.ID = @CONSTITUENTID
        AND EV.ID = @EVENTID