USP_WEALTHCAPACITY_UPDATECOMMON

            CREATE procedure dbo.USP_WEALTHCAPACITY_UPDATECOMMON (
                @WEALTHID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null
            ) as begin
                set nocount on;

                if @WEALTHID is not null begin
                    begin try
                        declare @ESTIMATEDWEALTHVALUE money;
                        declare @MAJORGIVINGCAPACITYVALUE money;
                        declare @ESTIMATEDWEALTHID uniqueidentifier;
                        declare @MAJORGIVINGCAPACITYID uniqueidentifier;
                        declare @MAJORGIVINGCAPACITYBASISVALUE nvarchar(250);

                        set @ESTIMATEDWEALTHVALUE = 0;
                        set @MAJORGIVINGCAPACITYVALUE = 0;
                        set @ESTIMATEDWEALTHID = null;
                        set @MAJORGIVINGCAPACITYID = null;
                        set @MAJORGIVINGCAPACITYBASISVALUE = '';

                        begin try
                            -- Check if the constituent is a group/household or individual

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

                            if @ISGROUP = 0 begin
                                --get wealth capacity for the individual

                                declare @WEALTHCAPACITYFORMULAID uniqueidentifier;

                                select
                                    @WEALTHCAPACITYFORMULAID = WC.WEALTHCAPACITYFORMULAID
                                from
                                    dbo.WEALTHCAPACITY WC
                                where
                                    WC.ID = @WEALTHID;

                                if @WEALTHCAPACITYFORMULAID is null begin
                                    select
                                        @WEALTHCAPACITYFORMULAID = WCF.ID
                                    from
                                        dbo.WEALTHCAPACITYFORMULA WCF
                                    where
                                        WCF.ISDEFAULT = 1;
                                end

                                exec dbo.USP_CONSTITUENT_WEALTHCAPACITY_FROMFORMULA_2
                                    @WEALTHID,
                                    @WEALTHCAPACITYFORMULAID,
                                    @ESTIMATEDWEALTHVALUE output,
                                    @ESTIMATEDWEALTHID output,
                                    @MAJORGIVINGCAPACITYVALUE output,
                                    @MAJORGIVINGCAPACITYID output,
                                    @MAJORGIVINGCAPACITYBASISVALUE output;

                                update dbo.WEALTHCAPACITY
                                set MAJORGIVINGCAPACITYBASISVALUE = @MAJORGIVINGCAPACITYBASISVALUE
                                where ID = @WEALTHID;
                            end
                            else begin
                                --get wealth capacity for the group

                                exec dbo.USP_GROUP_GETWEALTHCAPACITY
                                    @WEALTHID,
                                    @ESTIMATEDWEALTHVALUE output,
                                    @ESTIMATEDWEALTHID output,
                                    @MAJORGIVINGCAPACITYVALUE output,
                                    @MAJORGIVINGCAPACITYID output;
                            end
                        end try
                        begin catch
                            set @ESTIMATEDWEALTHVALUE = 0;
                            set @MAJORGIVINGCAPACITYVALUE = 0;
                            set @ESTIMATEDWEALTHID = null;
                            set @MAJORGIVINGCAPACITYID = null;
                        end catch

                        -- Update the WEALTHCAPACITY table.

                        if @CHANGEAGENTID is null
                            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                        declare @CURRENTDATE datetime;
                        set @CURRENTDATE = getdate();

                        if (select count(ID) from dbo.[WEALTHCAPACITY] where WEALTHCAPACITY.[ID] = @WEALTHID) = 1 begin                        
                            update
                                dbo.[WEALTHCAPACITY]
                            set
                                [WEALTHCAPACITYFORMULAID] = @WEALTHCAPACITYFORMULAID,
                                [ESTIMATEDWEALTHVALUE] = @ESTIMATEDWEALTHVALUE,
                                [ESTIMATEDWEALTHID] = @ESTIMATEDWEALTHID,
                                [MAJORGIVINGCAPACITYID] = @MAJORGIVINGCAPACITYID,
                                [MAJORGIVINGCAPACITYVALUE] = @MAJORGIVINGCAPACITYVALUE,
                                [SYSTEMCALCULATED] = 1,
                                [CHANGEDBYID] = @CHANGEAGENTID,
                                [DATECHANGED] = @CURRENTDATE                   
                            where
                                WEALTHCAPACITY.[ID] = @WEALTHID and
                                WEALTHCAPACITY.[CONFIRMED] = 0;
                        end
                        else begin
                            insert into    dbo.[WEALTHCAPACITY] (
                                [ID],
                                [WEALTHCAPACITYFORMULAID],
                                [ESTIMATEDWEALTHVALUE],
                                [ESTIMATEDWEALTHID],
                                [MAJORGIVINGCAPACITYID],
                                [MAJORGIVINGCAPACITYVALUE],
                                [CONFIRMED],
                                [SYSTEMCALCULATED],
                                [ADDEDBYID],
                                [CHANGEDBYID],
                                [DATEADDED],
                                [DATECHANGED]
                            ) values (
                                @WEALTHID,
                                @WEALTHCAPACITYFORMULAID,
                                @ESTIMATEDWEALTHVALUE,
                                @ESTIMATEDWEALTHID,
                                @MAJORGIVINGCAPACITYID,
                                @MAJORGIVINGCAPACITYVALUE,
                                0,
                                1,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE                            
                            );                                                            
                        end
                    end try
                    begin catch
                    end catch
                end;
                return 0;
            end;