UFN_ADDRESSPROCESS_CONTACTADDRESSES

            CREATE function dbo.UFN_ADDRESSPROCESS_CONTACTADDRESSES
                (
                @ADDRESSPROCESSINGOPTIONID uniqueidentifier,
                @MAILTYPE tinyint,
                @PARAMETERSETID uniqueidentifier = null
                )
            returns table
            as
            return
                (
                with AP_CTE as (
                    select ORGINCLUDEWITHNOCONTACT,
                           ORGUSECONSTITUENTPREFS,
                            ORGSENDTOALLCONTACTS
                    from dbo.ADDRESSPROCESSINGOPTION
                    where ID = @ADDRESSPROCESSINGOPTIONID
                    )

                select C.ID as CONSTITUENTID,
                        coalesce(R.ADDRESSID,OA.ID) as ADDRESSID,
                        R.RECIPROCALCONSTITUENTID as CONTACTID,
                        R.POSITION as POSITION
                from dbo.CONSTITUENT C
                left join dbo.ADDRESS OA on C.ID = OA.CONSTITUENTID and OA.ISPRIMARY = 1 and OA.DONOTMAIL = 0
                outer apply
                    (
                    select top 1 CT.*
                    from dbo.UFN_ADDRESSPROCESS_CONTACTTYPEADDRESS(@ADDRESSPROCESSINGOPTIONID) CT
                    where CT.RELATIONSHIPCONSTITUENTID = C.ID
                    order by CT.PROCESSORDER
                    ) R
                where C.ISORGANIZATION = 1 and
                    ((select ORGSENDTOALLCONTACTS from AP_CTE) = 0 or @MAILTYPE = 5) and --Only send to 1 contact, or this is for receipts

                    ((select ORGINCLUDEWITHNOCONTACT from AP_CTE) = 1 OR
                    ((select ORGINCLUDEWITHNOCONTACT from AP_CTE) = 0 and R.RECIPROCALCONSTITUENTID is not null)) and

                    not exists
                        (select ID from dbo.MAILPREFERENCE MP
                         where CONSTITUENTID = C.ID and
                            (select ORGUSECONSTITUENTPREFS 
                                    from AP_CTE) = 1 and
                            MP.SENDMAIL = 1 and 
                            MP.DELIVERYMETHODCODE = 0 and
                            MP.ADDRESSID is not null and
                            (select count(MPC.ID) from dbo.MAILPREFERENCEORGCONTACTTYPE MPC
                                            where MPC.MAILPREFERENCEID = MP.ID) = 0 and
                            MP.MAILTYPECODE = @MAILTYPE 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)))
                            )
            union all
                select C.ID as CONSTITUENTID,
                        coalesce(R.ADDRESSID,OA.ID) as ADDRESSID,
                        R.RECIPROCALCONSTITUENTID as CONTACTID,
                        R.POSITION as POSITION
                from dbo.CONSTITUENT C
                left join dbo.ADDRESS OA on C.ID = OA.CONSTITUENTID and OA.ISPRIMARY = 1 and OA.DONOTMAIL = 0
                left join dbo.UFN_ADDRESSPROCESS_CONTACTTYPEADDRESS(@ADDRESSPROCESSINGOPTIONID) R on C.ID = R.RELATIONSHIPCONSTITUENTID
                where C.ISORGANIZATION = 1 and
                    @MAILTYPE <> 5 and    -- For receipts, only include 1st contact

                    (select ORGSENDTOALLCONTACTS from AP_CTE) = 1 and
                    ((select ORGINCLUDEWITHNOCONTACT from AP_CTE) = 1 OR
                    ((select ORGINCLUDEWITHNOCONTACT from AP_CTE) = 0 and R.RECIPROCALCONSTITUENTID is not null)) and

                    not exists
                        (select ID from dbo.MAILPREFERENCE MP
                         where CONSTITUENTID = C.ID and
                            (select ORGUSECONSTITUENTPREFS 
                                    from AP_CTE) = 1 and
                            MP.SENDMAIL = 1 and 
                            MP.DELIVERYMETHODCODE = 0 and
                            MP.ADDRESSID is not null and
                            (select count(MPC.ID) from dbo.MAILPREFERENCEORGCONTACTTYPE MPC
                                            where MPC.MAILPREFERENCEID = MP.ID) = 0 and
                            MP.MAILTYPECODE = @MAILTYPE 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)))
                            )

            )