USP_DATALIST_ADDRESSBOOKFAFALLCONTACTS

    CREATE PROCEDURE dbo.USP_DATALIST_ADDRESSBOOKFAFALLCONTACTS 
    (
        @EVENTID        AS uniqueidentifier,
        @CLIENTUSERID    AS int,
        @ADDRESSBOOKID    AS uniqueidentifier

    )
    as

    set nocount on;

    SELECT    A.ID AS CONTACTID
                , A.FIRSTNAME
                , A.LASTNAME

                , CASE WHEN TM.CONSTITUENTID        IS NOT NULL THEN 1          ELSE 0                  END AS [ISTEAMMEMBER]
        , CASE WHEN TM.STATUS             IS NULL     THEN 'N/A'  ELSE TM.STATUS    END AS [TEAMSTATUS]  

        , CASE WHEN D.CONSTITUENTID            IS NOT NULL THEN 1          ELSE 0                  END AS [ISDONOR]
        , CASE WHEN D.STATUS              IS NULL     THEN 'N/A'  ELSE D.STATUS     END AS [DONORSTATUS]  

        , CASE WHEN (TM.CONSTITUENTID IS NULL AND D.CONSTITUENTID IS NULL AND I.CONSTITUENTID IS NULL AND HH.CONSTITUENTID IS NULL) THEN 1    ELSE 0         END AS [ISPROSPECT]         
        , A.EMAILADDRESS

        , CASE WHEN TM.ISTEAMCAPTAIN      IS NULL THEN 0          ELSE 1            END AS [ISTEAMCAPTAIN]
        , CASE WHEN TM.TCSTATUS           IS NULL     THEN 'N/A'  ELSE TM.TCSTATUS  END AS [TEAMCAPTAINSTATUS]       

        , CASE WHEN HH.CONSTITUENTID        IS NOT NULL THEN 1          ELSE 0                  END AS [ISHOUSEHOLDMEMBER]
        , CASE WHEN HH.STATUS             IS NULL     THEN 'N/A'  ELSE HH.STATUS    END AS [HOUSEHOLDSTATUS]  

        , CASE WHEN HH.ISHOUSEHOLDLEADER  IS NULL THEN 0      ELSE 1                END AS [ISHOUSEHOLDLEADER]
        , CASE WHEN HH.HLSTATUS           IS NULL     THEN 'N/A'  ELSE HH.HLSTATUS  END AS [HOUSEHOLDLEADERSTATUS]  

        , CASE WHEN I.CONSTITUENTID            IS NOT NULL THEN 1          ELSE 0                  END AS [ISINDIVIDUALPARTICIPANT]
        , CASE WHEN I.STATUS              IS NULL     THEN 'N/A'  ELSE I.STATUS     END AS [INDIVIDUALPARTICIPANTSTATUS]  

        , CASE WHEN CM.ISTEAMCAPTAIN      IS NULL THEN 0          ELSE 1            END AS [ISCOMPANYCAPTAIN]
        , CASE WHEN CM.TCSTATUS           IS NULL     THEN 'N/A'  ELSE CM.TCSTATUS  END AS [COMPANYCAPTAINSTATUS]          


        FROM          [dbo].[ADDRESSBOOKFAF] A (NOLOCK)
        LEFT JOIN    [dbo].[UFN_FAFADDRESSBOOK_TEAMMEMBERS_LIST]       (@EVENTID, @CLIENTUSERID) TM    ON A.CONSTITUENTID= TM.CONSTITUENTID                             
        LEFT JOIN    [dbo].[UFN_FAFADDRESSBOOK_DONORS_LIST]            (@EVENTID, @CLIENTUSERID) D   ON A.CONSTITUENTID= D.CONSTITUENTID    
    LEFT JOIN    [dbo].[UFN_FAFADDRESSBOOK_HOUSEHOLDMEMBERS_LIST]  (@EVENTID, @CLIENTUSERID) HH    ON A.CONSTITUENTID= HH.CONSTITUENTID    
    LEFT JOIN    [dbo].[UFN_FAFADDRESSBOOK_PARTICIPANTS_LIST]      (@EVENTID, @CLIENTUSERID) I   ON A.CONSTITUENTID= I.CONSTITUENTID    
        LEFT JOIN    [dbo].[UFN_FAFADDRESSBOOK_COMPANYMEMBERS_LIST]       (@EVENTID, @CLIENTUSERID) CM    ON A.CONSTITUENTID= CM.CONSTITUENTID                              

        WHERE        A.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCURRENTCLIENTUSERID(@CLIENTUSERID)) AND A.ID = @ADDRESSBOOKID  
    ORDER BY A.LASTNAME ASC