USP_GROUPWEALTHDETAILS_PAGEEXPRESSIONS_COMMON

Gets common data for independent group 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
@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
Definition

Copy


            CREATE procedure dbo.USP_GROUPWEALTHDETAILS_PAGEEXPRESSIONS_COMMON
            (
                @ID                             uniqueidentifier,     
                @DATALOADED                  bit =              0 output,
                @NAME                        nvarchar(400) =    null output,
                @ISINACTIVE                  bit =              null output,
                @CONSTITUENTID               uniqueidentifier = 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
            ) as begin
                set nocount on;

                --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 dbo.WPBIOGRAPHICAL            where WEALTHID in (select MEMBERID from @GROUPMEMBERS) and VIEWED = 0);
                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); 

                --Get Constituent Information


                select
                    @DATALOADED = 1,
                    @NAME = CONSTITUENT.NAME,
                    @ISINACTIVE = CONSTITUENT.ISINACTIVE,
                    @CONSTITUENTID = @ID
                from
                    dbo.CONSTITUENT
                left outer join dbo.WEALTH on WEALTH.ID=CONSTITUENT.ID
                where
                    CONSTITUENT.ID = @ID;        

                return 0;
            end;