USP_PROSPECTRESEARCHREPORT_MODELINGPROPENSITY

            CREATE procedure dbo.USP_PROSPECTRESEARCHREPORT_MODELINGPROPENSITY(
                @ID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier,
                @ISVISIBLE bit
            )
            as begin
                set nocount on;

                declare @RETVAL TABLE
                    (
                        LABEL nvarchar(100),
                        SEQUENCE integer,
                        VALUETYPE nvarchar(100),
                        STRINGVALUE nvarchar(max),
                        NUMBERVALUE int,
                        DATEVALUE datetime,
                        CURRENCYVALUE money,
                        FUZZYDATEVALUE dbo.UDT_FUZZYDATE default('00000000'),
                        HOURMINUTEVALUE dbo.UDT_HOURMINUTE default('0000'),
                        BOOLEANVALUE bit,
                        MAXIMUMVALUE nvarchar(50),
                        UPDATEDON date,
                        ORIGIN nvarchar(50)
                    )

                if @ISVISIBLE = 1
                begin

                    -- This is the only score type that is not a string score value

                    declare @NCOAMOVEDINDICATORID uniqueidentifier = 'CA48F28A-DBE6-40E5-907A-D29D40F9B588';

                    -- Get all Blackbaud scores

                    insert into @RETVAL 
                    (
                        LABEL,
                        STRINGVALUE,
                        BOOLEANVALUE,
                        SEQUENCE,
                        VALUETYPE,
                        MAXIMUMVALUE,
                        UPDATEDON,
                        ORIGIN
                    )
                    select
                        NAME as LABEL,
                        case when ANALYTICSMODELID = @NCOAMOVEDINDICATORID then null else SCORE end as STRINGVALUE,
                        case
                            when ANALYTICSMODELID = @NCOAMOVEDINDICATORID then
                                case when SCORE = 'Yes' then 1 else 0 end
                            else null
                        end as BOOLEANVALUE,
                        row_number() over (order by NAME) as SEQUENCE,
                        case when ANALYTICSMODELID = @NCOAMOVEDINDICATORID then N'YesNoValue' else N'TextValue' end as VALUETYPE,
                        MAXIMUMSCORE as MAXIMUMVALUE,
                        UPDATEDON,
                        N'Blackbaud' as ORIGIN
                    from
                        dbo.UFN_MODELSCORESFORCONSTITUENT(@ID);

                    declare @SEQUENCE integer = (select coalesce(max(SEQUENCE), 0) from @RETVAL);

                    insert into @RETVAL
                    (
                        LABEL,
                        SEQUENCE,
                        VALUETYPE,
                        STRINGVALUE,
                        NUMBERVALUE,
                        DATEVALUE,
                        CURRENCYVALUE,
                        FUZZYDATEVALUE,
                        HOURMINUTEVALUE,
                        BOOLEANVALUE,
                        MAXIMUMVALUE,
                        UPDATEDON,
                        ORIGIN
                    )
                    select 
                        CATEGORY,
                        @SEQUENCE + ROW_NUMBER() over(order by CATEGORY, coalesce(STARTDATE, DATECHANGED)) as ROWNUMBER,
                        VALUETYPE,
                        STRINGVALUE,
                        NUMBERVALUE,
                        DATEVALUE,
                        CURRENCYVALUE,
                        FUZZYDATEVALUE,
                        HOURMINUTEVALUE,
                        BOOLEANVALUE,
                        null, --custom scores have no max value

                        coalesce(STARTDATE, DATECHANGED),
                        N'Custom'
                    from
                        dbo.UFN_ATTRIBUTE_GETATTRIBUTELIST_WITHAUDIT('Model Scores and Ratings', @ID, @CURRENTAPPUSERID);

                end

                select
                    LABEL,
                    SEQUENCE,
                    VALUETYPE,
                    STRINGVALUE,
                    NUMBERVALUE,
                    DATEVALUE,
                    CURRENCYVALUE,
                    FUZZYDATEVALUE,
                    HOURMINUTEVALUE,
                    BOOLEANVALUE,
                    MAXIMUMVALUE,
                    UPDATEDON,
                    ORIGIN
                from @RETVAL
                order by UPDATEDON desc, LABEL;

            end;