USP_GROUP_GETWEALTHCAPACITY

            CREATE procedure dbo.USP_GROUP_GETWEALTHCAPACITY (
                @GROUPID uniqueidentifier,
                @ESTIMATEDWEALTHVALUE money output,
                @ESTIMATEDWEALTHID uniqueidentifier output,
                @MAJORGIVINGCAPACITYVALUE money output,
                @MAJORGIVINGCAPACITYID uniqueidentifier output
            ) as
                set nocount on;

                set @ESTIMATEDWEALTHVALUE = 0;
                set @MAJORGIVINGCAPACITYVALUE = 0;

                declare @ISGROUP bit;
                select @ISGROUP = ISGROUP from dbo.CONSTITUENT where ID = @GROUPID;

                if @GROUPID is not null and @ISGROUP = 1 begin
                    declare @C_REALESTATEASSETS bigint;
                    declare @C_BUSINESSASSETS bigint;
                    declare @C_SECURITIESASSETS bigint;
                    declare @C_AFFLUENCEINDICATORSASSETS bigint;
                    declare @C_INCOMEASSETS bigint;
                    declare @C_OTHERASSETS bigint;

                    exec dbo.USP_GROUP_GETWEALTHSUMMARY
                            @GROUPID = @GROUPID, 
                            @C_REALESTATEASSETS = @C_REALESTATEASSETS output, 
                            @C_SECURITIESASSETS = @C_SECURITIESASSETS output, 
                            @C_BUSINESSASSETS = @C_BUSINESSASSETS output, 
                            @C_AFFLUENCEINDICATORSASSETS = @C_AFFLUENCEINDICATORSASSETS output, 
                            @C_OTHERASSETS = @C_OTHERASSETS output,
                            @C_INCOME = @C_INCOMEASSETS output;

                    -- Calculate the estimated wealth.

                    set @ESTIMATEDWEALTHVALUE = coalesce(@C_REALESTATEASSETS,0) + coalesce(@C_SECURITIESASSETS,0) + coalesce(@C_BUSINESSASSETS,0) + coalesce(@C_AFFLUENCEINDICATORSASSETS,0) + coalesce(@C_INCOMEASSETS,0) + coalesce(@C_OTHERASSETS,0);

                    set @MAJORGIVINGCAPACITYVALUE = 0;
                    set @ESTIMATEDWEALTHID = null;
                    set @MAJORGIVINGCAPACITYID = null;
                    if @ESTIMATEDWEALTHVALUE > 0
                    begin
                        -- Calculate the major giving capacity.

                        set @MAJORGIVINGCAPACITYVALUE = cast((@ESTIMATEDWEALTHVALUE*0.05) as bigint);

                        -- Get estimated wealth and major giving capacity IDs.

                        select top 1 @ESTIMATEDWEALTHID = ID from dbo.ESTIMATEDWEALTH where MINIMUMAMOUNT<=@ESTIMATEDWEALTHVALUE order by MINIMUMAMOUNT desc;
                        if  @ESTIMATEDWEALTHVALUE < (select min(MINIMUMAMOUNT) from dbo.ESTIMATEDWEALTH)
                            select @ESTIMATEDWEALTHID = ID from dbo.ESTIMATEDWEALTH where MINIMUMAMOUNT = (select min(MINIMUMAMOUNT) from dbo.ESTIMATEDWEALTH);
                        else if @ESTIMATEDWEALTHID is null
                            select @ESTIMATEDWEALTHID = ID from dbo.ESTIMATEDWEALTH where MINIMUMAMOUNT = (select max(MINIMUMAMOUNT) from dbo.ESTIMATEDWEALTH);

                        select top 1 @MAJORGIVINGCAPACITYID = ID from dbo.MAJORGIVINGCAPACITY where MINIMUMAMOUNT<=@MAJORGIVINGCAPACITYVALUE order by MINIMUMAMOUNT desc;
                        if @MAJORGIVINGCAPACITYVALUE < (select min(MINIMUMAMOUNT) from dbo.MAJORGIVINGCAPACITY)
                            select @MAJORGIVINGCAPACITYID = ID from dbo.MAJORGIVINGCAPACITY where MINIMUMAMOUNT = (select min(MINIMUMAMOUNT) from dbo.MAJORGIVINGCAPACITY);
                        else if @MAJORGIVINGCAPACITYID is null
                            select @MAJORGIVINGCAPACITYID = ID from dbo.MAJORGIVINGCAPACITY where MINIMUMAMOUNT = (select max(MINIMUMAMOUNT) from dbo.MAJORGIVINGCAPACITY);
                    end
                end

                return 0;