USP_WEALTHDETAILS_PAGEEXPRESSIONS_COMMON

Gets common data for independent wealth details page expressions view forms.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@NAME	nvarchar(400)	INOUT
@ISINACTIVE	bit	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT
@WEALTHUPDATEPENDING	bit	INOUT
@DISABLEDWEALTHUPDATES	bit	INOUT
@VALIDSEARCHCRITERIASTRICT	bit	INOUT
@VALIDSEARCHCRITERIARELAXED	bit	INOUT
@BIOGRAPHICALSUFFIX	nvarchar(13)	INOUT
@POLITICALDONATIONSSUFFIX	nvarchar(13)	INOUT
@PRIVATEFOUNDATIONSSUFFIX	nvarchar(13)	INOUT
@INCOMECOMPENSATIONSUFFIX	nvarchar(13)	INOUT
@AFFLUENCEINDICATORSSUFFIX	nvarchar(13)	INOUT
@NONPROFITAFFILIATIONSSUFFIX	nvarchar(13)	INOUT
@PHILANTHROPICGIFTSSUFFIX	nvarchar(13)	INOUT
@BUSINESSOWNERSHIPSUFFIX	nvarchar(13)	INOUT
@REALESTATESUFFIX	nvarchar(13)	INOUT
@SECURITIESSUFFIX	nvarchar(13)	INOUT
@OTHERASSETSSUFFIX	nvarchar(13)	INOUT
@NEWBIOGRAPHICAL	bit	INOUT
@NEWPOLITICALDONATIONS	bit	INOUT
@NEWPRIVATEFOUNDATIONS	bit	INOUT
@NEWINCOMECOMPENSATION	bit	INOUT
@NEWAFFLUENCEINDICATORS	bit	INOUT
@NEWNONPROFITAFFILIATIONS	bit	INOUT
@NEWPHILANTHROPICGIFTS	bit	INOUT
@NEWBUSINESSOWNERSHIP	bit	INOUT
@NEWREALESTATE	bit	INOUT
@NEWSECURITIES	bit	INOUT
@NEWOTHERASSETS	bit	INOUT
@ISORG	bit	INOUT
@ISGROUP	bit	INOUT
@ISHOUSEHOLD	bit	INOUT
@WEALTHPOINTREGISTERED	bit	INOUT
@WEALTHPOINTACCOUNTTYPECODE	int	INOUT
Definition

Copy


            CREATE procedure dbo.USP_WEALTHDETAILS_PAGEEXPRESSIONS_COMMON (
                @ID                             uniqueidentifier,     
                @DATALOADED                  bit =              0 output,
                @NAME                        nvarchar(400) =    null output,
                @ISINACTIVE                  bit =              null output,
                @CONSTITUENTID               uniqueidentifier = null output,
                @WEALTHUPDATEPENDING         bit =              null output,
                @DISABLEDWEALTHUPDATES       bit =              null output,
                @VALIDSEARCHCRITERIASTRICT   bit =              null output,
                @VALIDSEARCHCRITERIARELAXED  bit =              null output,

                @BIOGRAPHICALSUFFIX          nvarchar(13) = null output,
                @POLITICALDONATIONSSUFFIX    nvarchar(13) = null output,
                @PRIVATEFOUNDATIONSSUFFIX    nvarchar(13) = null output,
                @INCOMECOMPENSATIONSUFFIX    nvarchar(13) = null output,
                @AFFLUENCEINDICATORSSUFFIX   nvarchar(13) = null output,
                @NONPROFITAFFILIATIONSSUFFIX nvarchar(13) = null output,
                @PHILANTHROPICGIFTSSUFFIX    nvarchar(13) = null output,
                @BUSINESSOWNERSHIPSUFFIX     nvarchar(13) = null output,
                @REALESTATESUFFIX            nvarchar(13) = null output,
                @SECURITIESSUFFIX            nvarchar(13) = null output,
                @OTHERASSETSSUFFIX           nvarchar(13) = null output,

                @NEWBIOGRAPHICAL             bit = null output,
                @NEWPOLITICALDONATIONS       bit = null output,
                @NEWPRIVATEFOUNDATIONS       bit = null output,
                @NEWINCOMECOMPENSATION       bit = null output,
                @NEWAFFLUENCEINDICATORS      bit = null output,
                @NEWNONPROFITAFFILIATIONS    bit = null output,
                @NEWPHILANTHROPICGIFTS       bit = null output,
                @NEWBUSINESSOWNERSHIP        bit = null output,
                @NEWREALESTATE               bit = null output,
                @NEWSECURITIES               bit = null output,
                @NEWOTHERASSETS              bit = null output,

                @ISORG                         bit = null output,
                @ISGROUP                     bit = null output,
                @ISHOUSEHOLD                 bit = null output,

                @WEALTHPOINTREGISTERED       bit = null output,
                @WEALTHPOINTACCOUNTTYPECODE  integer = null output

            ) as begin
                set nocount on;

                --Get WealthPoint Account Information

                select top (1)
                    @WEALTHPOINTREGISTERED =      REGISTERED,
                    @WEALTHPOINTACCOUNTTYPECODE = ACCOUNTTYPECODE
                from
                    dbo.WEALTHPOINTCONFIGURATION

                --Get Constituent Information


                select
                    @DATALOADED = 1,
                    @NAME = NF.NAME,
                    @ISINACTIVE = CONSTITUENT.ISINACTIVE,
                    @CONSTITUENTID = @ID,
                    @ISORG = CONSTITUENT.ISORGANIZATION,
                    @ISGROUP = CONSTITUENT.ISGROUP,
                    @ISHOUSEHOLD = dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@ID)
                from
                    dbo.CONSTITUENT
                left outer join dbo.WEALTH on WEALTH.ID=CONSTITUENT.ID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
                where
                    CONSTITUENT.ID = @ID;


                --Get WealthPoint Search Information


                if exists(select 1 from dbo.WEALTH where ID=@ID and PENDINGSEARCH=1) begin
                    set @WEALTHUPDATEPENDING = 1;
                end else begin
                    set @WEALTHUPDATEPENDING = 0;
                    exec dbo.USP_CONSTITUENT_VALIDWEALTHPOINTSEARCHCRITERIA @CONSTITUENTID=@ID, @STRICT=@VALIDSEARCHCRITERIASTRICT output, @RELAXED=@VALIDSEARCHCRITERIARELAXED output;
                end

                select 
                    @DISABLEDWEALTHUPDATES = count(*)
                from
                    dbo.DISABLEDWEALTHUPDATES
                where
                    DISABLEDWEALTHUPDATES.ID = @ID

                if @ISGROUP = 0
                begin
                    --Set Wealth Detail Page Record Count Suffixes


                    set @BIOGRAPHICALSUFFIX          = dbo.UFN_TABCAPTIONCOUNTSUFFIX((select count(*) from (select ID from dbo.WPBIOGRAPHICAL where WEALTHID = @ID and REJECTED = 0 union all select ID from dbo.WPBIOGRAPHICALDEMOGRAPHIC where WEALTHID = @ID and REJECTED = 0) BIO));
                    set @POLITICALDONATIONSSUFFIX    = dbo.UFN_TABCAPTIONCOUNTSUFFIX((select count(*) from dbo.WPPOLITICALDONATION      where WEALTHID=@ID and REJECTED = 0));
                    set @PRIVATEFOUNDATIONSSUFFIX    = dbo.UFN_TABCAPTIONCOUNTSUFFIX((select count(*) from dbo.WPPRIVATEFOUNDATION    where WEALTHID=@ID and REJECTED = 0));
                    set @INCOMECOMPENSATIONSUFFIX    = dbo.UFN_TABCAPTIONCOUNTSUFFIX((select count(*) from dbo.WPINCOMECOMPENSATION   where WEALTHID=@ID and REJECTED = 0));
                    set @AFFLUENCEINDICATORSSUFFIX   = dbo.UFN_TABCAPTIONCOUNTSUFFIX((select count(*) from dbo.WPAFFLUENCEINDICATOR   where WEALTHID=@ID and REJECTED = 0));
                    set @NONPROFITAFFILIATIONSSUFFIX = dbo.UFN_TABCAPTIONCOUNTSUFFIX((select count(*) from dbo.WPNONPROFITAFFILIATION where WEALTHID=@ID and REJECTED = 0));
                    set @PHILANTHROPICGIFTSSUFFIX    = dbo.UFN_TABCAPTIONCOUNTSUFFIX((select count(*) from dbo.WPPHILANTHROPICGIFT    where WEALTHID=@ID and REJECTED = 0));
                    set @BUSINESSOWNERSHIPSUFFIX     = dbo.UFN_TABCAPTIONCOUNTSUFFIX((select count(*) from dbo.WPBUSINESSOWNERSHIP    where WEALTHID=@ID and REJECTED = 0));
                    set @REALESTATESUFFIX            = dbo.UFN_TABCAPTIONCOUNTSUFFIX((select count(*) from dbo.WPREALESTATE           where WEALTHID=@ID and REJECTED = 0));
                    set @SECURITIESSUFFIX            = dbo.UFN_TABCAPTIONCOUNTSUFFIX((select count(*) from dbo.WPSECURITIES           where WEALTHID=@ID and REJECTED = 0));
                    set @OTHERASSETSSUFFIX           = dbo.UFN_TABCAPTIONCOUNTSUFFIX((select count(*) from dbo.WPOTHERASSET           where WEALTHID=@ID and REJECTED = 0));

                    --Set Wealth Detail Page New Record Indicators


                    set    @NEWBIOGRAPHICAL             = (select count(ID) from (select ID from dbo.WPBIOGRAPHICAL where WEALTHID=@ID and VIEWED = 0 union all select ID from dbo.WPBIOGRAPHICALDEMOGRAPHIC where WEALTHID=@ID and VIEWED = 0) BIO);
                    set @NEWPOLITICALDONATIONS       = (select count(ID) from dbo.WPPOLITICALDONATION        where WEALTHID=@ID and VIEWED = 0);
                    set @NEWPRIVATEFOUNDATIONS       = (select count(ID) from dbo.WPPRIVATEFOUNDATION       where WEALTHID=@ID and VIEWED = 0); 
                    set @NEWINCOMECOMPENSATION       = (select count(ID) from dbo.WPINCOMECOMPENSATION      where WEALTHID=@ID and VIEWED = 0);
                    set @NEWAFFLUENCEINDICATORS      = (select count(ID) from dbo.WPAFFLUENCEINDICATOR      where WEALTHID=@ID and VIEWED = 0); 
                    set @NEWNONPROFITAFFILIATIONS    = (select count(ID) from dbo.WPNONPROFITAFFILIATION    where WEALTHID=@ID and VIEWED = 0); 
                    set @NEWPHILANTHROPICGIFTS       = (select count(ID) from dbo.WPPHILANTHROPICGIFT        where WEALTHID=@ID and VIEWED = 0); 
                    set @NEWBUSINESSOWNERSHIP        = (select count(ID) from dbo.WPBUSINESSOWNERSHIP       where WEALTHID=@ID and VIEWED = 0); 
                    set @NEWREALESTATE               = (select count(ID) from dbo.WPREALESTATE              where WEALTHID=@ID and VIEWED = 0);  
                    set @NEWSECURITIES               = (select count(ID) from dbo.WPSECURITIES                where WEALTHID=@ID and VIEWED = 0); 
                    set @NEWOTHERASSETS              = (select count(ID) from dbo.WPOTHERASSET              where WEALTHID=@ID and VIEWED = 0); 
                end
                else
                begin
                    --Set Wealth Detail Page Record Count Suffixes

                    declare @BIOGRAPHICALCOUNT int;
                    declare @POLITICALDONATIONSCOUNT int;
                    declare @PRIVATEFOUNDATIONSCOUNT int;
                    declare @INCOMECOMPENSATIONCOUNT int;
                    declare @AFFLUENCEINDICATORSCOUNT int;
                    declare @NONPROFITAFFILIATIONSCOUNT int;
                    declare @PHILANTHROPICGIFTSCOUNT int;
                    declare @BUSINESSOWNERSHIPCOUNT int;
                    declare @REALESTATECOUNT int;
                    declare @SECURITIESCOUNT int;
                    declare @OTHERASSETSCOUNT int;

                    exec dbo.USP_GROUP_GETWEALTHSUMMARY
                        @GROUPID = @ID,
                        @NBIOGRAPHICAL = @BIOGRAPHICALCOUNT output,
                        @NCAMPAIGN = @POLITICALDONATIONSCOUNT output,
                        @NFOUNDATION = @PRIVATEFOUNDATIONSCOUNT output,
                        @NGIFT = @PHILANTHROPICGIFTSCOUNT output,
                        @NINCOME = @INCOMECOMPENSATIONCOUNT output,
                        @NAFFLUENCEINDICATORS = @AFFLUENCEINDICATORSCOUNT output,
                        @NNONPROFIT = @NONPROFITAFFILIATIONSCOUNT output,
                        @NOTHERASSETS = @OTHERASSETSCOUNT output,
                        @NBUSINESS = @BUSINESSOWNERSHIPCOUNT output,
                        @NREALESTATE = @REALESTATECOUNT output,
                        @NSECURITIES = @SECURITIESCOUNT output;

                    set @BIOGRAPHICALSUFFIX = dbo.UFN_TABCAPTIONCOUNTSUFFIX(@BIOGRAPHICALCOUNT);
                    set @POLITICALDONATIONSSUFFIX = dbo.UFN_TABCAPTIONCOUNTSUFFIX(@POLITICALDONATIONSCOUNT);
                    set @PRIVATEFOUNDATIONSSUFFIX = dbo.UFN_TABCAPTIONCOUNTSUFFIX(@PRIVATEFOUNDATIONSCOUNT);
                    set @INCOMECOMPENSATIONSUFFIX = dbo.UFN_TABCAPTIONCOUNTSUFFIX(@INCOMECOMPENSATIONCOUNT);
                    set @AFFLUENCEINDICATORSSUFFIX = dbo.UFN_TABCAPTIONCOUNTSUFFIX(@AFFLUENCEINDICATORSCOUNT);
                    set @NONPROFITAFFILIATIONSSUFFIX = dbo.UFN_TABCAPTIONCOUNTSUFFIX(@NONPROFITAFFILIATIONSCOUNT);
                    set @PHILANTHROPICGIFTSSUFFIX = dbo.UFN_TABCAPTIONCOUNTSUFFIX(@PHILANTHROPICGIFTSCOUNT);
                    set @BUSINESSOWNERSHIPSUFFIX = dbo.UFN_TABCAPTIONCOUNTSUFFIX(@BUSINESSOWNERSHIPCOUNT);
                    set @REALESTATESUFFIX = dbo.UFN_TABCAPTIONCOUNTSUFFIX(@REALESTATECOUNT);
                    set @SECURITIESSUFFIX = dbo.UFN_TABCAPTIONCOUNTSUFFIX(@SECURITIESCOUNT);
                    set @OTHERASSETSSUFFIX = dbo.UFN_TABCAPTIONCOUNTSUFFIX(@OTHERASSETSCOUNT);


                    --Set Wealth Detail Page New Record Indicators

                    --Store group members in a table variable


                    declare @GROUPMEMBERS table
                    (
                        MEMBERID uniqueidentifier
                    )

                    insert into @GROUPMEMBERS (MEMBERID)
                    select
                        ID
                    from dbo.UFN_GROUP_GETCURRENTMEMBERSNOPERMISSIONCHECK(@ID)


                    set    @NEWBIOGRAPHICAL             = (select count(ID) from (select ID from dbo.WPBIOGRAPHICAL where WEALTHID in (select MEMBERID from @GROUPMEMBERS) and VIEWED = 0 union all select ID from dbo.WPBIOGRAPHICALDEMOGRAPHIC where WEALTHID in (select MEMBERID from @GROUPMEMBERS) and VIEWED = 0 ) BIO );
                    set @NEWPOLITICALDONATIONS       = (select count(ID) from dbo.WPPOLITICALDONATION        where WEALTHID in (select MEMBERID from @GROUPMEMBERS) and VIEWED = 0);
                    set @NEWPRIVATEFOUNDATIONS       = (select count(ID) from dbo.WPPRIVATEFOUNDATION       where WEALTHID in (select MEMBERID from @GROUPMEMBERS) and VIEWED = 0); 
                    set @NEWINCOMECOMPENSATION       = (select count(ID) from dbo.WPINCOMECOMPENSATION      where WEALTHID in (select MEMBERID from @GROUPMEMBERS) and VIEWED = 0);
                    set @NEWAFFLUENCEINDICATORS      = (select count(ID) from dbo.WPAFFLUENCEINDICATOR      where WEALTHID in (select MEMBERID from @GROUPMEMBERS) and VIEWED = 0); 
                    set @NEWNONPROFITAFFILIATIONS    = (select count(ID) from dbo.WPNONPROFITAFFILIATION    where WEALTHID in (select MEMBERID from @GROUPMEMBERS) and VIEWED = 0); 
                    set @NEWPHILANTHROPICGIFTS       = (select count(ID) from dbo.WPPHILANTHROPICGIFT        where WEALTHID in (select MEMBERID from @GROUPMEMBERS) and VIEWED = 0); 
                    set @NEWBUSINESSOWNERSHIP        = (select count(ID) from dbo.WPBUSINESSOWNERSHIP       where WEALTHID in (select MEMBERID from @GROUPMEMBERS) and VIEWED = 0); 
                    set @NEWREALESTATE               = (select count(ID) from dbo.WPREALESTATE              where WEALTHID in (select MEMBERID from @GROUPMEMBERS) and VIEWED = 0);  
                    set @NEWSECURITIES               = (select count(ID) from dbo.WPSECURITIES                where WEALTHID in (select MEMBERID from @GROUPMEMBERS) and VIEWED = 0); 
                    set @NEWOTHERASSETS              = (select count(ID) from dbo.WPOTHERASSET              where WEALTHID in (select MEMBERID from @GROUPMEMBERS) and VIEWED = 0); 

                end

                return 0;
            end;