USP_SIMPLEDATALIST_REVENUEBATCHREGISTRANTGUESTSUGGESTION

                CREATE procedure dbo.USP_SIMPLEDATALIST_REVENUEBATCHREGISTRANTGUESTSUGGESTION
                (
                    @CONSTITUENTID uniqueidentifier
                )
                as
                    set nocount on;

                    declare @UNKNOWNGUESTWELLKNOWNGUID uniqueidentifier;
                    set @UNKNOWNGUESTWELLKNOWNGUID = 'C776DAB5-65B2-4258-ADAE-AE396D28E251'; --Well-known GUID for unknown guest


                    select top 100
                        [VALUE],
                        [LABEL]
                    from
                        (
                            select 
                                @UNKNOWNGUESTWELLKNOWNGUID [VALUE],
                                dbo.UFN_REVENUEBATCHCONSTITUENT_GETREGISTRANTGUESTSUGGESTIONNAME(@UNKNOWNGUESTWELLKNOWNGUID) [LABEL],
                                null [KEYNAME]

                            union all

                            select top 100
                                CONSTITUENT.ID [VALUE],
                                CONSTITUENT_NF.NAME [LABEL],
                                CONSTITUENT.KEYNAME
                            from 
                                dbo.RELATIONSHIP 
                                inner join dbo.CONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
                                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) CONSTITUENT_NF
                            where 
                                RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
                                and CONSTITUENT.ISORGANIZATION = 0
                                and ISGROUP = 0            

                            union all

                            select top 100
                                CONSTITUENT.ID [VALUE],
                                CONSTITUENT_NF.NAME [LABEL],
                                CONSTITUENT.KEYNAME
                            from
                                dbo.CONSTITUENT
                                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) CONSTITUENT_NF
                            where
                                CONSTITUENT.ID = @CONSTITUENTID

                            union all

                            select top 100
                                BATCHREVENUECONSTITUENT.ID [VALUE],
                                BATCHREVENUECONSTITUENT.NAME [LABEL],
                                BATCHREVENUECONSTITUENT.KEYNAME
                            from
                                dbo.BATCHREVENUECONSTITUENT
                            where
                                BATCHREVENUECONSTITUENT.ID = @CONSTITUENTID

                            union all

                            select top 100
                                coalesce(CONSTITUENT.ID, BATCHREVENUECONSTITUENT.ID) [VALUE],
                                coalesce(CONSTITUENT_NF.NAME, BATCHREVENUECONSTITUENT.NAME) [LABEL],
                                coalesce(CONSTITUENT.KEYNAME, BATCHREVENUECONSTITUENT.KEYNAME)
                            from 
                                dbo.BATCHREVENUECONSTITUENTRELATION
                            inner join 
                                dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENTRELATION.RELATIONID = BATCHREVENUECONSTITUENT.ID
                            left outer join
                                dbo.CONSTITUENT on CONSTITUENT.ID = BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID
                            outer apply
                                dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) CONSTITUENT_NF
                            where 
                                BATCHREVENUECONSTITUENTRELATION.CONSTITUENTID = @CONSTITUENTID
                                and coalesce(CONSTITUENT.ISORGANIZATION, BATCHREVENUECONSTITUENT.ISORGANIZATION) = 0
                                and coalesce(CONSTITUENT.ISGROUP, BATCHREVENUECONSTITUENT.ISGROUP) = 0        

                        ) [SUGGESTIONUNION]
                    order by
                        [SUGGESTIONUNION].[KEYNAME], 2;