USP_DATAFORMTEMPLATE_VIEW_WEALTHSUMMARY

The load procedure used by the view dataform template "WealthPoint Wealth Summary View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@REALESTATEASSETS	money	INOUT	Real estate
@SECURITIESASSETS	money	INOUT	Securities
@BUSINESSASSETS	money	INOUT	Business ownership
@AFFLUENCEINDICATORSASSETS	money	INOUT	Affluence indicators
@OTHERASSETS	money	INOUT	Other assets
@TOTALASSETS	money	INOUT	Total assets
@INCOME	money	INOUT	Income/compensation
@C_REALESTATEASSETS	money	INOUT	Real estate
@C_SECURITIESASSETS	money	INOUT	Securities
@C_BUSINESSASSETS	money	INOUT	Business ownership
@C_AFFLUENCEINDICATORSASSETS	money	INOUT	Affluence indicators
@C_OTHERASSETS	money	INOUT	Other assets
@C_TOTALASSETS	money	INOUT	Total assets
@C_INCOME	money	INOUT	Income/compensation
@NBIOGRAPHICAL	int	INOUT	Biographical
@NCAMPAIGN	int	INOUT	Political donations
@NFOUNDATION	int	INOUT	Private foundation affiliations
@NGIFT	int	INOUT	Philanthropic gifts
@NINCOME	int	INOUT	NINCOME
@NAFFLUENCEINDICATORS	int	INOUT	Affluence indicators
@NNONPROFIT	int	INOUT	Nonprofit affiliations
@NOTHERASSETS	int	INOUT	NOTHERASSETS
@NBUSINESS	int	INOUT	Number of businesses
@NREALESTATE	int	INOUT	Number of properties
@NSECURITIES	int	INOUT	NSECURITIES
@C_NBIOGRAPHICAL	int	INOUT	Biographical
@C_NCAMPAIGN	int	INOUT	Political donations
@C_NFOUNDATION	int	INOUT	Private foundation affiliations
@C_NGIFT	int	INOUT	Philanthropic gifts
@C_NINCOME	int	INOUT	C_NINCOME
@C_NAFFLUENCEINDICATORS	int	INOUT	Affluence indicators
@C_NNONPROFIT	int	INOUT	Nonprofit affiliations
@C_NOTHERASSETS	int	INOUT	C_NOTHERASSETS
@C_NBUSINESS	int	INOUT	Number of businesses
@C_NREALESTATE	int	INOUT	Number of properties
@C_NSECURITIES	int	INOUT	C_NSECURITIES
@CANVIEWBIOGRAPHICAL	bit	INOUT	CANVIEWBIOGRAPHICAL
@CANVIEWCAMPAIGN	bit	INOUT	CANVIEWCAMPAIGN
@CANVIEWFOUNDATION	bit	INOUT	CANVIEWFOUNDATION
@CANVIEWGIFT	bit	INOUT	CANVIEWGIFT
@CANVIEWINCOME	bit	INOUT	CANVIEWINCOME
@CANVIEWAFFLUENCEINDICATORS	bit	INOUT	CANVIEWAFFLUENCEINDICATORS
@CANVIEWNONPROFIT	bit	INOUT	CANVIEWNONPROFIT
@CANVIEWOTHERASSETS	bit	INOUT	CANVIEWOTHERASSETS
@CANVIEWBUSINESS	bit	INOUT	CANVIEWBUSINESS
@CANVIEWREALESTATE	bit	INOUT	CANVIEWREALESTATE
@CANVIEWSECURITIES	bit	INOUT	CANVIEWSECURITIES
@CONSTITUENTID	uniqueidentifier	INOUT	Constituent ID
@HASBEENSEARCHED	bit	INOUT	HASBEENSEARCHED
@ISORG	bit	INOUT	ISORG

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_WEALTHSUMMARY (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @CURRENTAPPUSERID uniqueidentifier,

                    @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
                ) as
                    set NOCOUNT on;
                    set @DATALOADED = 0;

                    select
                        @DATALOADED = 1,
                        @CONSTITUENTID = @ID,
                        @ISORG = ISORGANIZATION
                    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
                        @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,'F4C7D336-DDEF-474A-89E5-291EE2DB2F52') &
                                                            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,'9CA1A80A-3697-4D42-B7A9-CC0CB5B562A9') &
                                                            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,'2B80B727-9E55-433E-9B37-E061645B1263') &
                                                            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

                    return 0;