USP_DATAFORMTEMPLATE_VIEW_RESEARCHGROUPMEMBERWEALTHSUMMARY

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN
@NAME	nvarchar(500)	INOUT
@LASTSCREENDATE	datetime	INOUT
@REALESTATEASSETS	money	INOUT
@SECURITIESASSETS	money	INOUT
@BUSINESSASSETS	money	INOUT
@AFFLUENCEINDICATORSASSETS	money	INOUT
@OTHERASSETS	money	INOUT
@TOTALASSETS	money	INOUT
@INCOME	money	INOUT
@C_REALESTATEASSETS	money	INOUT
@C_SECURITIESASSETS	money	INOUT
@C_BUSINESSASSETS	money	INOUT
@C_AFFLUENCEINDICATORSASSETS	money	INOUT
@C_OTHERASSETS	money	INOUT
@C_TOTALASSETS	money	INOUT
@C_INCOME	money	INOUT
@NBIOGRAPHICAL	int	INOUT
@NCAMPAIGN	int	INOUT
@NFOUNDATION	int	INOUT
@NGIFT	int	INOUT
@NINCOME	int	INOUT
@NAFFLUENCEINDICATORS	int	INOUT
@NNONPROFIT	int	INOUT
@NOTHERASSETS	int	INOUT
@NBUSINESS	int	INOUT
@NREALESTATE	int	INOUT
@NSECURITIES	int	INOUT
@C_NBIOGRAPHICAL	int	INOUT
@C_NCAMPAIGN	int	INOUT
@C_NFOUNDATION	int	INOUT
@C_NGIFT	int	INOUT
@C_NINCOME	int	INOUT
@C_NAFFLUENCEINDICATORS	int	INOUT
@C_NNONPROFIT	int	INOUT
@C_NOTHERASSETS	int	INOUT
@C_NBUSINESS	int	INOUT
@C_NREALESTATE	int	INOUT
@C_NSECURITIES	int	INOUT
@CANVIEWBIOGRAPHICAL	bit	INOUT
@CANVIEWCAMPAIGN	bit	INOUT
@CANVIEWFOUNDATION	bit	INOUT
@CANVIEWGIFT	bit	INOUT
@CANVIEWINCOME	bit	INOUT
@CANVIEWAFFLUENCEINDICATORS	bit	INOUT
@CANVIEWNONPROFIT	bit	INOUT
@CANVIEWOTHERASSETS	bit	INOUT
@CANVIEWBUSINESS	bit	INOUT
@CANVIEWREALESTATE	bit	INOUT
@CANVIEWSECURITIES	bit	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT
@HASBEENSEARCHED	bit	INOUT
@ISORG	bit	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_DATAFORMTEMPLATE_VIEW_RESEARCHGROUPMEMBERWEALTHSUMMARY (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @CURRENTAPPUSERID uniqueidentifier,

          @NAME nvarchar(500) = null output,
          @LASTSCREENDATE datetime = null output,

                    @REALESTATEASSETS money = null output,
                    @SECURITIESASSETS money = null output,
                    @BUSINESSASSETS money = null output,
                    @AFFLUENCEINDICATORSASSETS money = null output,
                    @OTHERASSETS money = null output,
                    @TOTALASSETS money = null output,
                    @INCOME money = null output,

                    @C_REALESTATEASSETS money = null output,
                    @C_SECURITIESASSETS money = null output,
                    @C_BUSINESSASSETS money = null output,
                    @C_AFFLUENCEINDICATORSASSETS money = null output,
                    @C_OTHERASSETS money = null output,
                    @C_TOTALASSETS money = null output,
                    @C_INCOME money = null output,

                    @NBIOGRAPHICAL int = null output,
                    @NCAMPAIGN int = null output,
                    @NFOUNDATION int = null output,
                    @NGIFT int = null output,
                    @NINCOME int = null output,
                    @NAFFLUENCEINDICATORS int = null output,
                    @NNONPROFIT int = null output,
                    @NOTHERASSETS int = null output,
                    @NBUSINESS int = null output,
                    @NREALESTATE int = null output,
                    @NSECURITIES int = null output,

                    @C_NBIOGRAPHICAL int = null output,
                    @C_NCAMPAIGN int = null output,
                    @C_NFOUNDATION int = null output,
                    @C_NGIFT int = null output,
                    @C_NINCOME int = null output,
                    @C_NAFFLUENCEINDICATORS int = null output,
                    @C_NNONPROFIT int = null output,
                    @C_NOTHERASSETS int = null output,
                    @C_NBUSINESS int = null output,
                    @C_NREALESTATE int = null output,
                    @C_NSECURITIES int = null output,

                    @CANVIEWBIOGRAPHICAL bit = null output,
                    @CANVIEWCAMPAIGN bit = null output,
                    @CANVIEWFOUNDATION bit = null output,
                    @CANVIEWGIFT bit = null output,
                    @CANVIEWINCOME bit = null output,
                    @CANVIEWAFFLUENCEINDICATORS bit = null output,
                    @CANVIEWNONPROFIT bit = null output,
                    @CANVIEWOTHERASSETS bit = null output,
                    @CANVIEWBUSINESS bit = null output,
                    @CANVIEWREALESTATE bit = null output,
                    @CANVIEWSECURITIES bit = null output,

                    @CONSTITUENTID uniqueidentifier = null output,
                    @HASBEENSEARCHED bit = null output,
                    @ISORG bit = 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
                    set NOCOUNT on;
                    set @DATALOADED = 0;

                    select
                        @DATALOADED = 1,
                        @CONSTITUENTID = @ID,
                        @ISORG = ISORGANIZATION,
            @NAME = NAME
                    from
                        dbo.CONSTITUENT
                    where
                        CONSTITUENT.ID = @ID;

                    select 
                        @NBIOGRAPHICAL = 0,
                        @NCAMPAIGN = 0,
                        @NFOUNDATION = 0,
                        @NGIFT = 0,
                        @NINCOME = 0,
                        @NAFFLUENCEINDICATORS = 0,
                        @NNONPROFIT = 0,
                        @NOTHERASSETS = 0,
                        @NBUSINESS = 0,
                        @NREALESTATE = 0,
                        @NSECURITIES = 0,
                        @C_NBIOGRAPHICAL = 0,
                        @C_NCAMPAIGN = 0,
                        @C_NFOUNDATION = 0,
                        @C_NGIFT = 0,
                        @C_NINCOME = 0,
                        @C_NAFFLUENCEINDICATORS = 0,
                        @C_NNONPROFIT = 0,
                        @C_NOTHERASSETS = 0,
                        @C_NBUSINESS = 0,
                        @C_NREALESTATE = 0,
                        @C_NSECURITIES = 0

                    select
            @LASTSCREENDATE = WEALTHPOINTDATE,
                        @REALESTATEASSETS = REALESTATEASSETS,
                        @C_REALESTATEASSETS = REALESTATEASSETSCONFIRMED,
                        @NREALESTATE = REALESTATENUMBER,
                        @C_NREALESTATE = REALESTATENUMBERCONFIRMED,
                        @BUSINESSASSETS = BUSINESSOWNERSHIPASSETS,
                        @C_BUSINESSASSETS = BUSINESSOWNERSHIPASSETSCONFIRMED,
                        @NBUSINESS = BUSINESSOWNERSHIPNUMBER,
                        @C_NBUSINESS = BUSINESSOWNERSHIPNUMBERCONFIRMED,
                        @SECURITIESASSETS = SECURITIESASSETS,
                        @C_SECURITIESASSETS = SECURITIESASSETSCONFIRMED,
                        @NSECURITIES = SECURITIESNUMBER,
                        @C_NSECURITIES = SECURITIESNUMBERCONFIRMED,
                        @AFFLUENCEINDICATORSASSETS = AFFLUENCEINDICATORASSETS,
                        @C_AFFLUENCEINDICATORSASSETS = AFFLUENCEINDICATORASSETSCONFIRMED,
                        @NAFFLUENCEINDICATORS = AFFLUENCEINDICATORNUMBER,
                        @C_NAFFLUENCEINDICATORS = AFFLUENCEINDICATORNUMBERCONFIRMED,
                        @INCOME = INCOMECOMPENSATIONASSETS,
                        @C_INCOME = INCOMECOMPENSATIONASSETSCONFIRMED,
                        @NINCOME = INCOMECOMPENSATIONNUMBER,
                        @C_NINCOME = INCOMECOMPENSATIONNUMBERCONFIRMED,
                        @OTHERASSETS = OTHERASSETS,
                        @C_OTHERASSETS = OTHERASSETSCONFIRMED,
                        @NOTHERASSETS = OTHERASSETSNUMBER,
                        @C_NOTHERASSETS = OTHERASSETSNUMBERCONFIRMED,
                        @NBIOGRAPHICAL = BIOGRAPHICALNUMBER,
                        @C_NBIOGRAPHICAL = BIOGRAPHICALNUMBERCONFIRMED,
                        @NCAMPAIGN = POLITICALDONATIONNUMBER,
                        @C_NCAMPAIGN = POLITICALDONATIONNUMBERCONFIRMED,
                        @NGIFT = PHILANTHROPICGIFTNUMBER,
                        @C_NGIFT = PHILANTHROPICGIFTNUMBERCONFIRMED,
                        @NNONPROFIT = NONPROFITAFFILIATIONNUMBER,
                        @C_NNONPROFIT = NONPROFITAFFILIATIONNUMBERCONFIRMED,
                        @NFOUNDATION = PRIVATEFOUNDATIONNUMBER,
                        @C_NFOUNDATION = PRIVATEFOUNDATIONNUMBERCONFIRMED,
                        @TOTALASSETS = TOTALASSETS,
                        @C_TOTALASSETS = TOTALASSETSCONFIRMED
                    from
                        dbo.WEALTH
                    where
                        WEALTH.ID = @ID;


                    if @NREALESTATE = 0
                       set @REALESTATEASSETS = null

                    if @C_NREALESTATE = 0
                       set @C_REALESTATEASSETS = null

                    if @NBUSINESS = 0
                       set @BUSINESSASSETS = null

                    if @C_NBUSINESS = 0
                       set @C_BUSINESSASSETS = null

                    if @NSECURITIES = 0
                       set @SECURITIESASSETS = null

                    if @C_NSECURITIES = 0
                       set @C_SECURITIESASSETS = null

                    if @NAFFLUENCEINDICATORS = 0
                       set @AFFLUENCEINDICATORSASSETS = null

                    if @C_NAFFLUENCEINDICATORS = 0
                       set @C_AFFLUENCEINDICATORSASSETS = null

                    if @NINCOME = 0
                       set @INCOME = null

                    if @C_NINCOME = 0
                       set @C_INCOME = null

                    if @NOTHERASSETS = 0
                       set @OTHERASSETS = null

                    if @C_NOTHERASSETS = 0
                       set @C_OTHERASSETS = null

                    if @TOTALASSETS is null
                       set @HASBEENSEARCHED = 0
                    else
                       set @HASBEENSEARCHED = 1

                    if (1=dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID)) begin
                        set @CANVIEWBIOGRAPHICAL = 1;
                        set @CANVIEWCAMPAIGN = 1;
                        set @CANVIEWFOUNDATION = 1;
                        set @CANVIEWGIFT = 1;
                        set @CANVIEWINCOME = 1;
                        set @CANVIEWAFFLUENCEINDICATORS = 1;
                        set @CANVIEWNONPROFIT = 1;
                        set @CANVIEWOTHERASSETS = 1;
                        set @CANVIEWBUSINESS = 1;
                        set @CANVIEWREALESTATE = 1;
                        set @CANVIEWSECURITIES = 1;
                    end
                    else begin
                        set @CANVIEWBIOGRAPHICAL =            dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'45F165B4-025E-49B3-AFCC-FD9B741406DB') &
                                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, 'ca164256-542b-48fe-b165-d53d0dd75ac4');
                        set @CANVIEWCAMPAIGN =                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'7685BF76-60A9-4C8F-8DCE-C143505F3F9D') &
                                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '5a0edb04-1ce6-49fb-b965-5cb91746c37e');
                        set @CANVIEWFOUNDATION =            dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'AA908FCF-089F-4BC2-8B52-3A567F4A5142') &
                                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, 'f9b1f154-844d-4e11-92f5-4c8f5fa0a1cf');
                        set @CANVIEWGIFT =                    dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'4B3922A5-2C7B-4FFB-9F64-54F024C40E13') &
                                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '608322a5-0699-42c6-9cc9-07b3ae685f5e');
                        set @CANVIEWINCOME =                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'4CD067F6-DED7-458C-B592-11AAC1186FD9') &
                                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '0e21b37a-ec41-4883-8b4d-f9738688d441');
                        set @CANVIEWAFFLUENCEINDICATORS =    dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'8BFA650D-4FC4-4454-94F2-2A38332C0684') &
                                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '791f6e7d-8aa8-4078-a4e1-0392cd0b73f2');
                        set @CANVIEWNONPROFIT =                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'6e625a52-7937-464a-afa8-535eedad07e5') &
                                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '40f3df01-4ba4-4228-9a3b-18e4bd98fbe3');
                        set @CANVIEWOTHERASSETS =            dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'DC27FA3B-7EFE-484C-99CA-FC8C84D7EE0A') &
                                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, 'b70edfe9-af03-486a-bc01-352a219cbf01');
                        set @CANVIEWBUSINESS =                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'16b62787-1265-47ca-9898-77bb89afa228') &
                                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, 'd6d56c8f-3f0d-4f5a-b194-e5c5d1399a48');
                        set @CANVIEWREALESTATE =            dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'C5659434-1493-4c84-BCB5-2D2DA2932CA0') &
                                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '0a054f34-9246-49a8-87f0-2cf789597080');
                        set @CANVIEWSECURITIES =            dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'8623389D-F469-4a34-B3D5-00C642257CDC') &
                                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '90758f12-463a-421b-8433-1883eb1c0aa5');
                    end

                    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); 

                    return 0;