USP_CONSTITUENTNAMEFORMATS_GETDEFAULTADDRESSEE

            create procedure dbo.USP_CONSTITUENTNAMEFORMATS_GETDEFAULTADDRESSEE
            (
                @CONSTITUENTID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier,
                @ADDRESSEENAMEFORMATID uniqueidentifier = null output,
                @SALUTATIONNAMEFORMATID uniqueidentifier = null output
            )
            as
                set nocount on;

                --A constituent ID must be provided.

                if @CONSTITUENTID is null
                    return null;

                --Set the addressee and salutation fields with defaults when this constituent

                --has either a primary addressee/salutation name format or has only a single name

                --format.

                declare @ISORGANIZATION bit;
                declare @ISGROUP bit;

                select
                    @ISORGANIZATION = CONSTITUENT.ISORGANIZATION,
                    @ISGROUP = CONSTITUENT.ISGROUP
                from
                    dbo.CONSTITUENT
                where
                    CONSTITUENT.ID = @CONSTITUENTID;

                if @ISORGANIZATION = 1 or @ISGROUP = 1
                begin
                    --The constituent name formats simple list only returns one row for organizations and groups/households;

                    --select it by default.

                    set @ADDRESSEENAMEFORMATID = @CONSTITUENTID;
                    set @SALUTATIONNAMEFORMATID = @CONSTITUENTID;
                end
                else
                begin
                    --For individuals

                    declare @AVAILABLENAMEFORMAT table
                    (
                        ID uniqueidentifier,
                        PRIMARYADDRESSEE bit,
                        PRIMARYSALUTATION bit
                    );

                    insert into @AVAILABLENAMEFORMAT (ID, PRIMARYADDRESSEE, PRIMARYSALUTATION)
                    select top 2 --We only need up to two: the two primary values or the sole value when only one exists

                        NAMEFORMAT.ID,
                        NAMEFORMAT.PRIMARYADDRESSEE,
                        NAMEFORMAT.PRIMARYSALUTATION
                    from
                        dbo.NAMEFORMAT
                    where
                        NAMEFORMAT.CONSTITUENTID = @CONSTITUENTID
                        and
                        (
                            NAMEFORMAT.SITEID is null
                            or dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, NAMEFORMAT.SITEID) = 1
                        )
                    order by
                        NAMEFORMAT.PRIMARYADDRESSEE desc,
                        NAMEFORMAT.PRIMARYSALUTATION desc;

                    if @@ROWCOUNT > 1
                    begin
                        --There are multiple available name formats, only set defaults

                        --if a primary addressee/saluation name format is specified.

                        select
                            @ADDRESSEENAMEFORMATID = ID
                        from
                            @AVAILABLENAMEFORMAT
                        where
                            PRIMARYADDRESSEE = 1;

                        select
                            @SALUTATIONNAMEFORMATID = ID
                        from
                            @AVAILABLENAMEFORMAT
                        where
                            PRIMARYSALUTATION = 1;
                    end
                    else
                    begin
                        --There are either zero or one available name formats.

                        --If there is just one, select it by default.

                        select
                            @ADDRESSEENAMEFORMATID = ID,
                            @SALUTATIONNAMEFORMATID = ID
                        from
                            @AVAILABLENAMEFORMAT;
                    end

                end


                return 0;