fnGetEmailJobRecipientsInList

            CREATE FUNCTION [dbo].[fnGetEmailJobRecipientsInList]
            (
            @EmailJobID int,
            @ListID int
            )
            RETURNS @Recipient TABLE (
            [EmailJob_RecipientID] int
            )
            AS
            BEGIN

            Declare @ListOrigin INT
            Declare @UserImportID INT
            SELECT @ListOrigin = Origin, @UserImportID = UserImportID  From dbo.EmailList WHERE ID = @ListID

            IF @ListOrigin = 0 BEGIN
                IF @UserImportID > 0 Begin
                    INSERT INTO @Recipient
                    SELECT ejr.ID
                    FROM (SELECT ID, UserID, EmailAddress FROM EmailJob_Recipient WHERE EmailJobID = @EmailJobID) ejr
                    JOIN (SELECT cu.ID, cu.Email FROM
                            (SELECT ClientUsersID
                            FROM [dbo].[UserImportMasterList] uiml WHERE UserImportID = @UserImportID) uiml                
                            JOIN [dbo].[clientusers] cu
                            ON cu.ID = uiml.ClientUsersID
                            WHERE cu.[Deleted] = 0 AND cu.[active]=1) [user]
                    ON ejr.UserID = [user].ID OR (ejr.UserID = 0 AND ejr.EmailAddress = [user].Email)
                END
                ELSE BEGIN
                    -- This is a NetCom list that isnt tied to an import, so it represents all Client Users
                    INSERT INTO @Recipient
                    SELECT ejr.ID
                    FROM (SELECT ID, UserID, EmailAddress FROM EmailJob_Recipient WHERE EmailJobID = @EmailJobID) ejr
                    JOIN (SELECT Email, ID FROM [dbo].[clientusers]    WHERE [Deleted] = 0 AND [active]=1) [user]
                    ON ejr.UserID = [user].ID OR (ejr.UserID = 0 AND ejr.EmailAddress = [user].Email)
                END
            END
            Else IF @ListOrigin = 1 BEGIN
                INSERT INTO @Recipient
                SELECT ejr.ID
                FROM (SELECT ID, EmailAddress FROM EmailJob_Recipient WHERE EmailJobID = @EmailJobID) ejr
                JOIN (SELECT EmailAddress FROM EmailListUploadMasterList WHERE EmailListID = @ListID)eluml
                On ejr.EmailAddress = eluml.EmailAddress
            End
            Else IF @ListOrigin = 2 BEGIN
                --If a recipients BOS People ID is in the list, then its a match.
                --Also, if the email addresses match, and the BOS ID is different, it is a match
                --This is used to match recipients from a UDF/Imported target list to a Query basd list
                INSERT INTO @Recipient
                SELECT ejr.ID
                FROM (SELECT ID, BackOfficeSystemPeopleID FROM EmailJob_Recipient WHERE EmailJobID = @EmailJobID) ejr
                JOIN (SELECT PeopleID FROM EmailList_People WHERE EmailListID = @ListID)elp
                On ejr.BackOfficeSystemPeopleID = elp.PeopleID
                UNION
                Select recipients.RecipientID
                FROM    (    SELECT ejr.ID as RecipientID, ejr.EmailAddress, bosp.BackOfficeSystemID AS BOSID
                            FROM (SELECT ID, BackOfficeSystemPeopleID, EmailAddress FROM EmailJob_Recipient WHERE EmailJobID = @EmailJobID) ejr
                            JOIN BackOfficeSystemPeople bosp
                            ON   ejr.BackOfficeSystemPeopleID = bosp.ID) recipients
                JOIN     (    SELECT elp.EmailAddress, bosp.BackOfficeSystemID AS BOSID
                            FROM (SELECT PeopleID, EmailAddress FROM EmailList_People WHERE EmailListID = @ListID)elp
                            JOIN BackOfficeSystemPeople bosp
                            ON   elp.PeopleID = bosp.ID)  list
                ON recipients.EmailAddress = list.EmailAddress AND recipients.BOSID <> list.BOSID
            End
            Else IF @ListOrigin = 3 BEGIN
                INSERT INTO @Recipient
                SELECT ejr.ID
                FROM (SELECT ID, EmailAddress FROM EmailJob_Recipient WHERE EmailJobID = @EmailJobID) ejr
                JOIN (SELECT EmailAddress FROM EmailList_UserDefined WHERE EmailListID = @ListID)elud
                On ejr.EmailAddress = elud.EmailAddress
            End

            RETURN
            END