USP_WEALTHPOINT_UPDATEWEALTHSUMMARY

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

                declare @CURRENTDATE datetime;

                set @CURRENTDATE = getdate();

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

                if exists (select WEALTH.ID from dbo.WEALTH where WEALTH.ID = @WEALTHID) begin
                    declare @REALESTATEASSETS money, @REALESTATEASSETSCONFIRMED money, @REALESTATENUMBER int, @REALESTATENUMBERCONFIRMED int;
                    declare @BUSINESSOWNERSHIPASSETS money, @BUSINESSOWNERSHIPASSETSCONFIRMED money, @BUSINESSOWNERSHIPNUMBER int, @BUSINESSOWNERSHIPNUMBERCONFIRMED int;
                    declare @SECURITIESASSETS money, @SECURITIESASSETSCONFIRMED money, @SECURITIESNUMBER int, @SECURITIESNUMBERCONFIRMED int;
                    declare @AFFLUENCEINDICATORASSETS money, @AFFLUENCEINDICATORASSETSCONFIRMED money, @AFFLUENCEINDICATORNUMBER int, @AFFLUENCEINDICATORNUMBERCONFIRMED int;
                    declare @INCOMECOMPENSATIONASSETS money, @INCOMECOMPENSATIONASSETSCONFIRMED money, @INCOMECOMPENSATIONNUMBER int, @INCOMECOMPENSATIONNUMBERCONFIRMED int;
                    declare @OTHERASSETS money, @OTHERASSETSCONFIRMED money, @OTHERASSETSNUMBER int, @OTHERASSETSNUMBERCONFIRMED int;
                    declare @BIOGRAPHICALNUMBER int, @BIOGRAPHICALNUMBERCONFIRMED int;
                    declare @POLITICALDONATIONNUMBER int, @POLITICALDONATIONNUMBERCONFIRMED int;
                    declare @PHILANTHROPICGIFTNUMBER int, @PHILANTHROPICGIFTNUMBERCONFIRMED int;
                    declare @NONPROFITAFFILIATIONNUMBER int, @NONPROFITAFFILIATIONNUMBERCONFIRMED int;
                    declare @PRIVATEFOUNDATIONNUMBER int, @PRIVATEFOUNDATIONNUMBERCONFIRMED int;

                    select @BIOGRAPHICALNUMBER = count(*) from (select ID from dbo.WPBIOGRAPHICAL where WEALTHID = @WEALTHID and REJECTED = 0 union all select ID from dbo.WPBIOGRAPHICALDEMOGRAPHIC where WEALTHID = @WEALTHID and REJECTED = 0) BIO;
                    select @POLITICALDONATIONNUMBER = count(*) from dbo.WPPOLITICALDONATION where WEALTHID = @WEALTHID and REJECTED = 0;
                    select @PHILANTHROPICGIFTNUMBER = count(*) from dbo.WPPHILANTHROPICGIFT where WEALTHID = @WEALTHID and REJECTED = 0;
                    select @NONPROFITAFFILIATIONNUMBER = count(*) from dbo.WPNONPROFITAFFILIATION where WEALTHID = @WEALTHID and REJECTED = 0;
                    select @PRIVATEFOUNDATIONNUMBER = count(*) from dbo.WPPRIVATEFOUNDATION where WEALTHID = @WEALTHID and REJECTED = 0;
                    select @BIOGRAPHICALNUMBERCONFIRMED = count(*) from (select ID from dbo.WPBIOGRAPHICAL where WEALTHID = @WEALTHID and CONFIRMED = 1 union all select ID from dbo.WPBIOGRAPHICALDEMOGRAPHIC where WEALTHID = @WEALTHID and CONFIRMED = 1) BIO;
                    select @POLITICALDONATIONNUMBERCONFIRMED = count(*) from dbo.WPPOLITICALDONATION where WEALTHID = @WEALTHID and CONFIRMED = 1;
                    select @PHILANTHROPICGIFTNUMBERCONFIRMED = count(*) from dbo.WPPHILANTHROPICGIFT where WEALTHID = @WEALTHID and CONFIRMED = 1;
                    select @NONPROFITAFFILIATIONNUMBERCONFIRMED = count(*) from dbo.WPNONPROFITAFFILIATION where WEALTHID = @WEALTHID and CONFIRMED = 1;
                    select @PRIVATEFOUNDATIONNUMBERCONFIRMED = count(*) from dbo.WPPRIVATEFOUNDATION where WEALTHID = @WEALTHID and CONFIRMED = 1;

                    select @BUSINESSOWNERSHIPNUMBER = count(*), @BUSINESSOWNERSHIPASSETS = sum(OWNERSHIPVALUE) from dbo.WPBUSINESSOWNERSHIP where WEALTHID = @WEALTHID and REJECTED = 0;
                    select @AFFLUENCEINDICATORNUMBER = count(*), @AFFLUENCEINDICATORASSETS = sum(RPASSETS) from dbo.WPAFFLUENCEINDICATOR where WEALTHID = @WEALTHID and REJECTED = 0;
                    select @OTHERASSETSNUMBER = count(*), @OTHERASSETS = sum(VALUE) from dbo.WPOTHERASSET where WEALTHID = @WEALTHID and REJECTED = 0;
                    select @INCOMECOMPENSATIONNUMBER = count(*), @INCOMECOMPENSATIONASSETS = sum(TOTALCOMPENSATION) from dbo.WPINCOMECOMPENSATION where WEALTHID = @WEALTHID and REJECTED = 0 and HISTORICCODE <> 1;
                    select @REALESTATENUMBER = count(*), @REALESTATEASSETS = sum(PROPERTYVALUATION) from dbo.WPREALESTATE where WEALTHID = @WEALTHID and REJECTED = 0 and HISTORICCODE <> 1;
                    select @SECURITIESNUMBER = count(*), @SECURITIESASSETS = sum(VALUEDIRECT) from dbo.WPSECURITIES where WEALTHID = @WEALTHID and REJECTED = 0;

                    select @BUSINESSOWNERSHIPNUMBERCONFIRMED = count(*), @BUSINESSOWNERSHIPASSETSCONFIRMED = sum(OWNERSHIPVALUE) from dbo.WPBUSINESSOWNERSHIP where WEALTHID = @WEALTHID and CONFIRMED = 1;
                    select @AFFLUENCEINDICATORNUMBERCONFIRMED = count(*), @AFFLUENCEINDICATORASSETSCONFIRMED = sum(RPASSETS) from dbo.WPAFFLUENCEINDICATOR  where WEALTHID = @WEALTHID and CONFIRMED = 1;
                    select @OTHERASSETSNUMBERCONFIRMED = count(*), @OTHERASSETSCONFIRMED = sum(VALUE) from dbo.WPOTHERASSET where WEALTHID = @WEALTHID and CONFIRMED = 1;
                    select @INCOMECOMPENSATIONNUMBERCONFIRMED = count(*), @INCOMECOMPENSATIONASSETSCONFIRMED = sum(TOTALCOMPENSATION) from dbo.WPINCOMECOMPENSATION where WEALTHID = @WEALTHID and CONFIRMED = 1 and HISTORICCODE <> 1;
                    select @REALESTATENUMBERCONFIRMED = count(*), @REALESTATEASSETSCONFIRMED = sum(PROPERTYVALUATION) from dbo.WPREALESTATE where WEALTHID = @WEALTHID and CONFIRMED = 1 and HISTORICCODE <> 1;
                    select @SECURITIESNUMBERCONFIRMED = count(*), @SECURITIESASSETSCONFIRMED = sum(VALUEDIRECT) from dbo.WPSECURITIES where WEALTHID = @WEALTHID and CONFIRMED = 1;

                    update
                        dbo.WEALTH
                    set
                        REALESTATEASSETS = coalesce(@REALESTATEASSETS,0),
                        REALESTATEASSETSCONFIRMED = coalesce(@REALESTATEASSETSCONFIRMED,0),
                        REALESTATENUMBER = coalesce(@REALESTATENUMBER,0),
                        REALESTATENUMBERCONFIRMED = coalesce(@REALESTATENUMBERCONFIRMED,0),
                        BUSINESSOWNERSHIPASSETS = coalesce(@BUSINESSOWNERSHIPASSETS,0),
                        BUSINESSOWNERSHIPASSETSCONFIRMED = coalesce(@BUSINESSOWNERSHIPASSETSCONFIRMED,0),
                        BUSINESSOWNERSHIPNUMBER = coalesce(@BUSINESSOWNERSHIPNUMBER,0),
                        BUSINESSOWNERSHIPNUMBERCONFIRMED = coalesce(@BUSINESSOWNERSHIPNUMBERCONFIRMED,0),
                        SECURITIESASSETS = coalesce(@SECURITIESASSETS,0),
                        SECURITIESASSETSCONFIRMED = coalesce(@SECURITIESASSETSCONFIRMED,0),
                        SECURITIESNUMBER = coalesce(@SECURITIESNUMBER,0),
                        SECURITIESNUMBERCONFIRMED = coalesce(@SECURITIESNUMBERCONFIRMED,0),
                        AFFLUENCEINDICATORASSETS = coalesce(@AFFLUENCEINDICATORASSETS,0),
                        AFFLUENCEINDICATORASSETSCONFIRMED = coalesce(@AFFLUENCEINDICATORASSETSCONFIRMED,0),
                        AFFLUENCEINDICATORNUMBER = coalesce(@AFFLUENCEINDICATORNUMBER,0),
                        AFFLUENCEINDICATORNUMBERCONFIRMED = coalesce(@AFFLUENCEINDICATORNUMBERCONFIRMED,0),
                        INCOMECOMPENSATIONASSETS = coalesce(@INCOMECOMPENSATIONASSETS,0),
                        INCOMECOMPENSATIONASSETSCONFIRMED = coalesce(@INCOMECOMPENSATIONASSETSCONFIRMED,0),
                        INCOMECOMPENSATIONNUMBER = coalesce(@INCOMECOMPENSATIONNUMBER,0),
                        INCOMECOMPENSATIONNUMBERCONFIRMED = coalesce(@INCOMECOMPENSATIONNUMBERCONFIRMED,0),
                        OTHERASSETS = coalesce(@OTHERASSETS,0),
                        OTHERASSETSCONFIRMED = coalesce(@OTHERASSETSCONFIRMED,0),
                        OTHERASSETSNUMBER = coalesce(@OTHERASSETSNUMBER,0),
                        OTHERASSETSNUMBERCONFIRMED = coalesce(@OTHERASSETSNUMBERCONFIRMED,0),
                        BIOGRAPHICALNUMBER = coalesce(@BIOGRAPHICALNUMBER,0),
                        BIOGRAPHICALNUMBERCONFIRMED = coalesce(@BIOGRAPHICALNUMBERCONFIRMED,0),
                        POLITICALDONATIONNUMBER = coalesce(@POLITICALDONATIONNUMBER,0),
                        POLITICALDONATIONNUMBERCONFIRMED = coalesce(@POLITICALDONATIONNUMBERCONFIRMED,0),
                        PHILANTHROPICGIFTNUMBER = coalesce(@PHILANTHROPICGIFTNUMBER,0),
                        PHILANTHROPICGIFTNUMBERCONFIRMED = coalesce(@PHILANTHROPICGIFTNUMBERCONFIRMED,0),
                        NONPROFITAFFILIATIONNUMBER = coalesce(@NONPROFITAFFILIATIONNUMBER,0),
                        NONPROFITAFFILIATIONNUMBERCONFIRMED = coalesce(@NONPROFITAFFILIATIONNUMBERCONFIRMED,0),
                        PRIVATEFOUNDATIONNUMBER = coalesce(@PRIVATEFOUNDATIONNUMBER,0),
                        PRIVATEFOUNDATIONNUMBERCONFIRMED = coalesce(@PRIVATEFOUNDATIONNUMBERCONFIRMED,0),
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where
                        ID = @WEALTHID;
                end
            end;