UFN_PROSPECTPLAN_GETFUNDRAISERSANDCREDITS

            CREATE function dbo.UFN_PROSPECTPLAN_GETFUNDRAISERSANDCREDITS
            (
                @PROSPECTPLANID uniqueidentifier
            )
            returns @OutTable table
                (
                    SEQUENCE int,
                    ID uniqueidentifier,
                    NAME nvarchar(200),
                    CREDITPERCENTAGE decimal(5,2),
                    DATEFROM datetime
                )
            as begin

                declare @PRIMARYMANAGERPERCENT decimal(5,2)
                declare @SECONDARYMANAGERPERCENT decimal(5,2)
                declare @SECONDARYSOLICITORPERCENT decimal(5,2)
                declare @SolicitorsTable table
                (
                    SEQUENCE int,
                    PRIORITY int,
                    ID uniqueidentifier, 
                    DATEFROM datetime
                )

                select
                    @PRIMARYMANAGERPERCENT = 0,
                    @SECONDARYMANAGERPERCENT = 0,
                    @SECONDARYSOLICITORPERCENT = 0

                select top 1
                    @PRIMARYMANAGERPERCENT = PRIMARYMANAGERPERCENT,
                    @SECONDARYMANAGERPERCENT = SECONDARYMANAGERPERCENT,
                    @SECONDARYSOLICITORPERCENT = SECONDARYSOLICITORPERCENT
                from
                    dbo.SOLICITORCREDITRULES


                insert into @SOLICITORSTABLE
                (
                    SEQUENCE,
                    PRIORITY,
                    ID,
                    DATEFROM
                )
                (    
                    select 0 SEQUENCE, 0 PRIORITY, PRIMARYMANAGERFUNDRAISERID ID, null DATEFROM  from dbo.PROSPECTPLAN where ID=@PROSPECTPLANID
                    union all
                    select 1 SEQUENCE, 1 PRIORITY, SECONDARYMANAGERFUNDRAISERID ID, null DATEFROM from dbo.PROSPECTPLAN where ID=@PROSPECTPLANID
                    union all
                    select 
                        SF.SEQUENCE+ 2 SEQUENCE,
                        2 PRIORITY,
                        SF.FUNDRAISERID ID,
                        SF.DATEFROM DATEFROM
                    from 
                        dbo.SECONDARYFUNDRAISER SF
                    where
                        SF.PROSPECTPLANID = @PROSPECTPLANID
                )

                -- Don't allow duplicate fundraisers.  Use the highest priority position for the fundraiser.

                delete OUTERSOLICITOR from @SolicitorsTable as OUTERSOLICITOR
                    where OUTERSOLICITOR.PRIORITY <> (
                        select min(PRIORITY) from @SOLICITORSTABLE INNERSOLICITOR where OUTERSOLICITOR.ID = INNERSOLICITOR.ID)

                insert into @OutTable
                    (SEQUENCE, ID, NAME, CREDITPERCENTAGE, DATEFROM)
                    (
                        select
                            FUNDRAISER.SEQUENCE,
                            FUNDRAISER.ID,
                            NF.NAME,
                            (
                                case FUNDRAISER.PRIORITY
                                    when 0 then @PRIMARYMANAGERPERCENT
                                    when 1 then @SECONDARYMANAGERPERCENT
                                    when 2 then @SECONDARYSOLICITORPERCENT
                                    else 0
                                end
                            ) as CREDITPERCENTAGE,
                            DATEFROM
                        from
                            @SolicitorsTable as FUNDRAISER
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FUNDRAISER.ID) NF
                        where
                            FUNDRAISER.ID is not null
                    );

                return;
            end