UFN_FAFADDRESSBOOK_HOUSEHOLDMEMBERS_LIST

CREATE FUNCTION [dbo].[UFN_FAFADDRESSBOOK_HOUSEHOLDMEMBERS_LIST]
(
     @EVENTID        AS uniqueidentifier
    ,@CLIENTUSERID    AS INT
)
RETURNS TABLE
AS
RETURN
    WITH 
    -- Client user linked constituent id

    [CLIENTUSERCONSITUENTID]
    AS(
        SELECT    C.ID AS CONSTITUENTID, C.DATEADDED
        FROM    dbo.ClientUsers cu  (NOLOCK)
        INNER    JOIN dbo.BackOfficeSystemUsers bosu (NOLOCK)
        ON        cu.ID = bosu.ClientUsersID AND bosu.[current] = 1 AND cu.Deleted = 0  
        INNER    JOIN dbo.BackOfficeSystemPeople bosp (NOLOCK)
        ON        bosp.ID = bosu.BackofficePeopleID AND bosp.BackOfficeSystemID = 0  
        INNER    JOIN dbo.CONSTITUENT C (NOLOCK)
        ON        bosp.BackofficeRecordID = C.SEQUENCEID    
        WHERE  cu.ID = @CLIENTUSERID
    )
    ,[CURRENTEVENTHHMEMBERS]
    AS
    (
        SELECT    
             TF.CONSTITUENTID
            ,TM.TEAMFUNDRAISINGTEAMID AS TEAMID, TM.DATEADDED, TFTC.CONSTITUENTID AS TEAMCAPTAINCONSTITUENTID
      ,T.ID AS TEAMFUNDRAISINGTEAMID

        FROM    [dbo].[TEAMFUNDRAISINGTEAMMEMBER] TM (NOLOCK)
        JOIN    [dbo].[TEAMFUNDRAISER] TF     (NOLOCK)                ON    TM.TEAMFUNDRAISERID = TF.ID
        JOIN    [dbo].[TEAMFUNDRAISINGTEAM] T (NOLOCK)                ON    TM.TEAMFUNDRAISINGTEAMID = T.ID        AND        TF.APPEALID = T.APPEALID
    JOIN     [dbo].[TEAMEXTENSION] TE          (NOLOCK)                  ON  T.ID= TE.TEAMFUNDRAISINGTEAMID    AND TE.TYPECODE= 3
        JOIN    [dbo].[EVENT] E               (NOLOCK)                ON  TF.APPEALID = E.APPEALID
        LEFT JOIN [dbo].[TEAMFUNDRAISINGTEAMCAPTAIN] TFTC (NOLOCK)  ON  TFTC.TEAMFUNDRAISINGTEAMID=T.ID AND TFTC.CONSTITUENTID = TF.CONSTITUENTID
        WHERE    E.ID = @EVENTID
    )
    ,[PREVIOUSEVENTHHMEMBERS]
    AS
    (
        SELECT    
             TF.CONSTITUENTID
            ,TM.TEAMFUNDRAISINGTEAMID AS TEAMID, TM.DATEADDED, TFTC.CONSTITUENTID AS TEAMCAPTAINCONSTITUENTID
        FROM    [dbo].[TEAMFUNDRAISINGTEAMMEMBER] TM (NOLOCK)
        JOIN    [dbo].[TEAMFUNDRAISER] TF  (NOLOCK)            ON    TM.TEAMFUNDRAISERID = TF.ID
        JOIN    [dbo].[TEAMFUNDRAISINGTEAM] T (NOLOCK)        ON    TM.TEAMFUNDRAISINGTEAMID = T.ID AND        TF.APPEALID= T.APPEALID
    JOIN     [dbo].[TEAMEXTENSION] TE          (NOLOCK)      ON  T.ID= TE.TEAMFUNDRAISINGTEAMID    AND TE.TYPECODE= 3
        JOIN    [dbo].[EVENT] E (NOLOCK)                      ON    TF.APPEALID = E.APPEALID
        JOIN    [dbo].[EVENTEXTENSION] EE (NOLOCK)            ON    EE.PRIORYEAREVENTID    = E.ID
        LEFT JOIN [dbo].[TEAMFUNDRAISINGTEAMCAPTAIN] TFTC (NOLOCK)  ON TFTC.TEAMFUNDRAISINGTEAMID=T.ID AND TFTC.CONSTITUENTID = TF.CONSTITUENTID
        WHERE    EE.EVENTID = @EVENTID
    )
    -- Client user team id

    ,[CLIENTUSERTEAM]
    AS
    (
        SELECT    TEAMID
        FROM    [CURRENTEVENTHHMEMBERS] T
        JOIN    [CLIENTUSERCONSITUENTID] C
        ON        T.CONSTITUENTID = C.CONSTITUENTID
    )
    -- FAF Address book contact related list

    ,[FAFADDRESSBOOKCONTACTS]
    AS
    (
        SELECT   A.ID AS CONTACTID
                ,A.FIRSTNAME
                ,A.LASTNAME
                ,A.CONSTITUENTID
                ,CASE 
                    WHEN CE.CONSTITUENTID IS NOT NULL THEN 'A'
                    WHEN PE.CONSTITUENTID IS NOT NULL THEN 'P'
                END AS [STATUS]
                ,CASE
                    WHEN CE.CONSTITUENTID IS NOT NULL THEN CE.DATEADDED
                    WHEN PE.CONSTITUENTID IS NOT NULL THEN PE.DATEADDED
                END AS [DATEADDED]

        ,CASE
                    WHEN CE.CONSTITUENTID=CE.TEAMCAPTAINCONSTITUENTID THEN 'y'
                    WHEN PE.CONSTITUENTID=PE.TEAMCAPTAINCONSTITUENTID THEN 'y'
                END AS [ISHOUSEHOLDLEADER]
        ,CASE 
                    WHEN CE.TEAMCAPTAINCONSTITUENTID  IS NOT NULL THEN 'A'
                    WHEN PE.TEAMCAPTAINCONSTITUENTID  IS NOT NULL THEN 'P'
                END AS [HLSTATUS]
        ,CE.TEAMFUNDRAISINGTEAMID AS [CURRENTHOUSEHOLDID]

        FROM    [dbo].[ADDRESSBOOKFAF] A (NOLOCK)
        LEFT    JOIN [CURRENTEVENTHHMEMBERS] CE        
        ON        A.CONSTITUENTID = CE.CONSTITUENTID
        --JOIN    [CLIENTUSERTEAM] CT

        --ON        CT.TEAMID = CE.TEAMID

        LEFT    JOIN [PREVIOUSEVENTHHMEMBERS] PE        
        ON        A.CONSTITUENTID = PE.CONSTITUENTID
        --AND        CE.TEAMID = PE.TEAMID

        WHERE   A.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCURRENTCLIENTUSERID(@CLIENTUSERID)) 
    AND 
    (
        CE.CONSTITUENTID IS NOT NULL OR PE.CONSTITUENTID IS NOT NULL
    )
    )
    SELECT * FROM  [FAFADDRESSBOOKCONTACTS];