UFN_ADDRESSPROCESS_ADDRESSES

            CREATE function dbo.UFN_ADDRESSPROCESS_ADDRESSES
                (
                @ADDRESSPROCESSINGOPTIONID uniqueidentifier,
                @MAILTYPE tinyint,
                @PARAMETERSETID uniqueidentifier,
                @DATE datetime,
                @INCLUDEHOUSEHOLDPROCESSING bit
                )
            returns table
            as
            return
                (
                with AP_CTE as (
                        select INDUSESEASONALADDRESS,
                            ORGMAILINGPREFERENCE,
                            INDALTADDRESS1TYPECODEID,
                            INDALTADDRESS1ISPRIMARY,
                            INDALTADDRESS2TYPECODEID,
                            INDALTADDRESS2ISPRIMARY,
                            ORGALTADDRESS1TYPECODEID,
                            ORGALTADDRESS1ISPRIMARY,
                            ORGALTADDRESS2TYPECODEID,
                            ORGALTADDRESS2ISPRIMARY,
                            INDINCLUDEWITHNOADDRESS,
                            ORGINCLUDEWITHNOADDRESS,
                            ORGINCLUDEWITHNOCONTACT,
                            INDUSECONSTITUENTPREFS,
                            ORGUSECONSTITUENTPREFS,
                            GROUPALTADDRESS1TYPECODEID,
                            GROUPALTADDRESS1ISPRIMARY,
                            GROUPALTADDRESS2TYPECODEID,
                            GROUPALTADDRESS2ISPRIMARY,
                            GROUPINCLUDEWITHNOADDRESS,
                            GROUPUSECONSTITUENTPREFS
                        from 
                            dbo.ADDRESSPROCESSINGOPTION
                        where 
                            ID = @ADDRESSPROCESSINGOPTIONID
                    )
                    select distinct 
                        C.CONSTITUENTID as CONSTITUENTID,
                        coalesce(MP.ADDRESSID,SA.ADDRESSID,CA.ADDRESSID,A1.ADDRESSID,A2.ADDRESSID,O1.ADDRESSID,O2.ADDRESSID, G1.ADDRESSID, G2.ADDRESSID) as ADDRESSID,
                        coalesce(MP.CONTACTID,CA.CONTACTID) as CONTACTID,
                        coalesce(MP.POSITION,CA.POSITION) as POSITION,
                        C.HOUSEHOLDID,
                        C.RETURNEDASHOUSEHOLDMEMBER,
                        case
                            when (C.RETURNEDASHOUSEHOLDMEMBER = 1) then coalesce(MP.GROUPCONTACTID, C.HOUSEHOLDID)
                            when (C.ISGROUP = 1 and C.HOUSEHOLDID is not null) then coalesce(MP.GROUPCONTACTID, C.HOUSEHOLDID)
                            else null
                        end as GROUPCONTACTID
                    from 
                        dbo.UFN_ADDRESSPROCESS_GETCONSTITUENTS(@DATE, @INCLUDEHOUSEHOLDPROCESSING) C
                        left join dbo.UFN_ADDRESSPROCESS_MAILPREFERENCES(@ADDRESSPROCESSINGOPTIONID,@MAILTYPE,@PARAMETERSETID,@DATE) as MP on C.ID = MP.CONSTITUENTID
                        left join dbo.UFN_ADDRESSPROCESS_SEASONALADDRESSES(@DATE) SA on C.ID = SA.CONSTITUENTID and (select INDUSESEASONALADDRESS from AP_CTE) = 1
                        left join dbo.UFN_ADDRESSPROCESS_CONTACTADDRESSES(@ADDRESSPROCESSINGOPTIONID,@MAILTYPE,@PARAMETERSETID) CA on C.ID = CA.CONSTITUENTID and ((select ORGMAILINGPREFERENCE from AP_CTE) = 0) 
                        left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES((select INDALTADDRESS1TYPECODEID from AP_CTE),(select INDALTADDRESS1ISPRIMARY from AP_CTE)) as A1 on C.ID = A1.CONSTITUENTID AND C.ISORGANIZATION = 0 AND C.ISGROUP = 0
                        left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES((select INDALTADDRESS2TYPECODEID from AP_CTE),(select INDALTADDRESS2ISPRIMARY from AP_CTE)) as a2 on C.ID = a2.constituentid AND c.isorganization = 0 AND C.ISGROUP = 0
                        left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES((select ORGALTADDRESS1TYPECODEID from AP_CTE),(select ORGALTADDRESS1ISPRIMARY from AP_CTE)) as O1 on C.ID = O1.constituentid AND c.isorganization = 1 and ((select ORGMAILINGPREFERENCE from AP_CTE) = 1)
                        left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES((select ORGALTADDRESS2TYPECODEID from AP_CTE),(select ORGALTADDRESS2ISPRIMARY from AP_CTE)) as O2 on C.ID = O2.constituentid AND c.isorganization = 1 and ((select ORGMAILINGPREFERENCE from AP_CTE) = 1)
                        left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES((select GROUPALTADDRESS1TYPECODEID from AP_CTE),(select GROUPALTADDRESS1ISPRIMARY from AP_CTE)) as G1 on C.ID = G1.CONSTITUENTID AND C.ISGROUP = 1
                        left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES((select GROUPALTADDRESS2TYPECODEID from AP_CTE),(select GROUPALTADDRESS2ISPRIMARY from AP_CTE)) as G2 on C.ID = G2.CONSTITUENTID AND C.ISGROUP = 1                                   
                    where 
                        (C.ISORGANIZATION = 1 and
                        ((select ORGMAILINGPREFERENCE from AP_CTE) = 0 and --if a valid mail pref is found, keep the record despite contact option

                        (((select ORGINCLUDEWITHNOCONTACT from AP_CTE) = 0 and (MP.ADDRESSID is not null or CA.CONTACTID is not null)) or
                        (select ORGINCLUDEWITHNOCONTACT from AP_CTE) = 1)) or
                        ((select ORGMAILINGPREFERENCE from AP_CTE) = 1 and
                        (((select ORGINCLUDEWITHNOADDRESS from AP_CTE) = 0 and coalesce(MP.ADDRESSID,CA.ADDRESSID,O1.ADDRESSID,O2.ADDRESSID) is not null) or
                        (select ORGINCLUDEWITHNOADDRESS from AP_CTE) = 1))
                        or
                        (C.ISORGANIZATION = 0 and C.ISGROUP = 0 and
                        (((select INDINCLUDEWITHNOADDRESS from AP_CTE) = 0 and coalesce(MP.ADDRESSID,SA.ADDRESSID,A1.ADDRESSID,A2.ADDRESSID) is not null) or
                        (select INDINCLUDEWITHNOADDRESS from AP_CTE) = 1))
                        or
                        (C.ISGROUP = 1 and
                        (((select GROUPINCLUDEWITHNOADDRESS from AP_CTE) = 0 and coalesce(MP.ADDRESSID,SA.ADDRESSID,G1.ADDRESSID,G2.ADDRESSID) is not null) or
                        (select GROUPINCLUDEWITHNOADDRESS from AP_CTE) = 1)))
                        and 
                        (
                            coalesce((
                                select SUPPRESSMAILING from 
                                (
                                    select top 1
                                        case when  (SENDMAIL = 0 or (SENDMAIL = 1 and DELIVERYMETHODCODE = 1)) then 1 else 0 end SUPPRESSMAILING,
                                        case    when @MAILTYPE = 0 and ACKNOWLEDGEMENTID is not null then 1 -- Revenue acknowledgements

                                                when @MAILTYPE = 1 and (SITEID is not null and BUSINESSUNITCODEID is not null and CATEGORYCODEID is not null) then 3 -- Appeals

                                                when @MAILTYPE = 1 and (SITEID is not null and BUSINESSUNITCODEID is not null) then 2 -- Appeals

                                                when @MAILTYPE = 1 and (SITEID is not null and CATEGORYCODEID is not null) then 2 -- Appeals

                                                when @MAILTYPE = 1 and (BUSINESSUNITCODEID is not null and CATEGORYCODEID is not null) then 2 -- Appeals

                                                when @MAILTYPE = 1 and (SITEID is not null or BUSINESSUNITCODEID is not null or CATEGORYCODEID is not null) then 1 -- Appeals

                                                when @MAILTYPE = 2 and EVENTCATEGORYCODEID is not null and SITEID is not null then 2 -- Events

                                                when @MAILTYPE = 2 and (EVENTCATEGORYCODEID is not null or SITEID is not null) then 1 -- Events

                                                when @MAILTYPE = 3 and CORRESPONDENCECODEID is not null and CORRESPONDENCEID is not null then 2 -- Correspondence

                                                when @MAILTYPE = 3 and (CORRESPONDENCECODEID is not null or CORRESPONDENCEID is not null) then 1 -- Correspondence

                                                when @MAILTYPE = 4 and PLEDGEREMINDERID is not null then 1 -- Reminders

                                                when @MAILTYPE = 8 and PURPOSEID is not null then 1 -- Stewardship

                                                else 0
                                        end as CRITERIAMATCHSCORE
                                    from 
                                        dbo.MAILPREFERENCE
                                    where 
                                        CONSTITUENTID = C.ID and
                                        MAILTYPECODE = @MAILTYPE and
                                        ((C.ISORGANIZATION = 0 and C.ISGROUP = 0 and (select INDUSECONSTITUENTPREFS 
                                                                    from AP_CTE) = 1)
                                        or
                                        (C.ISORGANIZATION = 1 and (select ORGUSECONSTITUENTPREFS 
                                                                    from AP_CTE) = 1)
                                        or
                                        (C.ISGROUP = 1 and (select GROUPUSECONSTITUENTPREFS
                                                                    from AP_CTE) = 1)) and        
                                        ((@PARAMETERSETID = case @MAILTYPE
                                                            when 0 then ACKNOWLEDGEMENTID
                                                            when 3 then CORRESPONDENCEID
                                                            when 4 then PLEDGEREMINDERID
                                                            else null
                                                            end) or 
                                        (case @MAILTYPE    when 0 then ACKNOWLEDGEMENTID
                                                        when 3 then CORRESPONDENCEID
                                                        when 4 then PLEDGEREMINDERID
                                                        else null
                                                        end is null)) and
                                        ((CORRESPONDENCECODEID is null) or (@MAILTYPE = 3 and CORRESPONDENCECODEID = (select CORRESPONDENCECODEID from dbo.CORRESPONDENCEPROCESS where ID = @PARAMETERSETID))) and
                                        ((EVENTCATEGORYCODEID is null) or (@MAILTYPE = 2 and EVENTCATEGORYCODEID = (select E.EVENTCATEGORYCODEID from dbo.EVENT E inner join dbo.INVITATION I  on E.ID = I.EVENTID where I.ID = @PARAMETERSETID))) and
                                        (
                                            (SITEID is null) or 
                                            (@MAILTYPE = 1 and SITEID = (select SITEID from dbo.APPEAL where ID = @PARAMETERSETID)) or
                                            (@MAILTYPE = 2 and SITEID in (select E.SITEID from dbo.EVENTSITE E inner join dbo.INVITATION I  on E.EVENTID = I.EVENTID where I.ID = @PARAMETERSETID))
                                        ) and
                                        ((PURPOSEID is null) or (@MAILTYPE = 8 and PURPOSEID = @PARAMETERSETID)) and
                                        ((BUSINESSUNITCODEID is null) or (@MAILTYPE = 1 and BUSINESSUNITCODEID in (select BUSINESSUNITCODEID from dbo.APPEALBUSINESSUNIT where APPEALID = @PARAMETERSETID))) and
                                        ((CATEGORYCODEID is null) or (@MAILTYPE = 1 and CATEGORYCODEID = (select APPEALCATEGORYCODEID from dbo.APPEAL where ID = @PARAMETERSETID)))
                                    order by 
                                        CRITERIAMATCHSCORE desc, SUPPRESSMAILING desc
                                ) as MATCHEDPREFERENCES
                            ), 0) = 0 -- Verify that the comm prefs allow the user to receive mailings

                        )
                )