USP_DATAFORMTEMPLATE_VIEW_WPSECURITYTRANSASACTIONS_EXPRESSIONS

The load procedure used by the view dataform template "WealthPoint Security Transactions Page Expression View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@NAME	nvarchar(400)	INOUT	NAME
@FLAGGED	bit	INOUT	FLAGGED
@HASWEALTHPROFILE	bit	INOUT	HASWEALTHPROFILE
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@UPDATEPENDING	bit	INOUT	UPDATEPENDING
@WEALTHPOINTDATE	datetime	INOUT	WEALTHPOINTDATE
@SYMBOL	nvarchar(10)	INOUT	SYMBOL
@CONFIRMED	bit	INOUT	CONFIRMED
@DATECONFIRMED	datetime	INOUT	DATECONFIRMED
@CONFIRMEDBY	nvarchar(154)	INOUT	CONFIRMEDBY
@BIOGRAPHICALSUFFIX	nvarchar(8)	INOUT	BIOGRAPHICALSUFFIX
@POLITICALDONATIONSSUFFIX	nvarchar(8)	INOUT	POLITICALDONATIONSSUFFIX
@PRIVATEFOUNDATIONSSUFFIX	nvarchar(8)	INOUT	PRIVATEFOUNDATIONSSUFFIX
@INCOMECOMPENSATIONSUFFIX	nvarchar(8)	INOUT	INCOMECOMPENSATIONSUFFIX
@AFFLUENCEINDICATORSSUFFIX	nvarchar(8)	INOUT	AFFLUENCEINDICATORSSUFFIX
@NONPROFITAFFILIATIONSSUFFIX	nvarchar(8)	INOUT	NONPROFITAFFILIATIONSSUFFIX
@PHILANTHROPICGIFTSSUFFIX	nvarchar(8)	INOUT	PHILANTHROPICGIFTSSUFFIX
@BUSINESSOWNERSHIPSUFFIX	nvarchar(8)	INOUT	BUSINESSOWNERSHIPSUFFIX
@REALESTATESUFFIX	nvarchar(8)	INOUT	REALESTATESUFFIX
@SECURITIESSUFFIX	nvarchar(8)	INOUT	SECURITIESSUFFIX
@OTHERASSETSSUFFIX	nvarchar(8)	INOUT	OTHERASSETSSUFFIX
@NEWBIOGRAPHICAL	bit	INOUT	NEWBIOGRAPHICAL
@NEWPOLITICALDONATIONS	bit	INOUT	NEWPOLITICALDONATIONS
@NEWPRIVATEFOUNDATIONS	bit	INOUT	NEWPRIVATEFOUNDATIONS
@NEWINCOMECOMPENSATION	bit	INOUT	NEWINCOMECOMPENSATION
@NEWAFFLUENCEINDICATORS	bit	INOUT	NEWAFFLUENCEINDICATORS
@NEWNONPROFITAFFILIATIONS	bit	INOUT	NEWNONPROFITAFFILIATIONS
@NEWPHILANTHROPICGIFTS	bit	INOUT	NEWPHILANTHROPICGIFTS
@NEWBUSINESSOWNERSHIP	bit	INOUT	NEWBUSINESSOWNERSHIP
@NEWREALESTATE	bit	INOUT	NEWREALESTATE
@NEWSECURITIES	bit	INOUT	NEWSECURITIES
@NEWOTHERASSETS	bit	INOUT	NEWOTHERASSETS
@ISSUERNAME	nvarchar(60)	INOUT	ISSUERNAME
@ISORG	bit	INOUT	ISORG

Definition

Copy



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

                    @NAME nvarchar(400) = null output,
                    @FLAGGED bit = null output,
                    @HASWEALTHPROFILE bit = null output,
                    @CONSTITUENTID uniqueidentifier = null output,
                    @UPDATEPENDING bit = null output,
                    @WEALTHPOINTDATE datetime = null output,
                    @SYMBOL nvarchar(10) = null output,
                    @CONFIRMED bit = null output,
                    @DATECONFIRMED datetime = null output,
                    @CONFIRMEDBY nvarchar(154) = null output,

                    @BIOGRAPHICALSUFFIX          nvarchar(8) = null output,
                    @POLITICALDONATIONSSUFFIX    nvarchar(8) = null output,
                    @PRIVATEFOUNDATIONSSUFFIX    nvarchar(8) = null output,
                    @INCOMECOMPENSATIONSUFFIX    nvarchar(8) = null output,
                    @AFFLUENCEINDICATORSSUFFIX   nvarchar(8) = null output,
                    @NONPROFITAFFILIATIONSSUFFIX nvarchar(8) = null output,
                    @PHILANTHROPICGIFTSSUFFIX    nvarchar(8) = null output,
                    @BUSINESSOWNERSHIPSUFFIX     nvarchar(8) = null output,
                    @REALESTATESUFFIX            nvarchar(8) = null output,
                    @SECURITIESSUFFIX            nvarchar(8) = null output,
                    @OTHERASSETSSUFFIX           nvarchar(8) = 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, 

                    @ISSUERNAME                     nvarchar(60) = null output,

                    @ISORG                         bit = null output

                )
                as
                    set nocount on;

                    set @DATALOADED = 0;

                    declare @WEALTHID uniqueidentifier;

                    select
                        @WEALTHID = W.ID, 
                        @FLAGGED = case when FP.ID is null then 0 else 1 end,
                        @HASWEALTHPROFILE = case when W.WEALTHPROFILEPDF is null then 0 else 1 end,
                        @WEALTHPOINTDATE = W.WEALTHPOINTDATE,
                        @SYMBOL = S.TICKER,
                        @ISSUERNAME = S.ISSUERNAME,
                        @CONFIRMED = S.CONFIRMED,
                        @DATECONFIRMED = coalesce(S.DATECONFIRMED, '2000-1-1'),
                        @CONFIRMEDBY = coalesce(CONFIRMER.NAME,U.USERNAME),
                        @ISORG = C.ISORGANIZATION
                    from
                        dbo.WPSECURITIES S
                        inner join dbo.CONSTITUENT C on C.ID = S.WEALTHID
                        left outer join dbo.WEALTH W on W.ID = C.ID
                        left outer join dbo.PROSPECT P on P.ID = C.ID
                        left outer join dbo.FLAGGEDPROSPECT FP on FP.APPUSERID = @CURRENTAPPUSERID and FP.PROSPECTID = C.ID
                        left outer join dbo.APPUSER U on U.ID=S.CONFIRMEDBYAPPUSERID
                        left outer join dbo.CONSTITUENT CONFIRMER on CONFIRMER.ID=U.CONSTITUENTID
                    where
                        S.ID = @ID;


                     --Collect common Wealth Details data

                    exec dbo.USP_WEALTHDETAILS_PAGEEXPRESSIONS_COMMON   
                        @WEALTHID,           
                        @DATALOADED                        output,
                        @NAME                            output,                               
                        null,  
                        @CONSTITUENTID                     output,  
                        @UPDATEPENDING                     output,  
                        null,  
                        null,  
                        null,  

                        @BIOGRAPHICALSUFFIX             output,  
                        @POLITICALDONATIONSSUFFIX         output,  
                        @PRIVATEFOUNDATIONSSUFFIX         output,  
                        @INCOMECOMPENSATIONSUFFIX         output,  
                        @AFFLUENCEINDICATORSSUFFIX         output,  
                        @NONPROFITAFFILIATIONSSUFFIX     output,  
                        @PHILANTHROPICGIFTSSUFFIX         output,  
                        @BUSINESSOWNERSHIPSUFFIX         output,  
                        @REALESTATESUFFIX                 output,  
                        @SECURITIESSUFFIX                 output,  
                        @OTHERASSETSSUFFIX                 output,  
                        @NEWBIOGRAPHICAL                 output,  
                        @NEWPOLITICALDONATIONS          output,  
                        @NEWPRIVATEFOUNDATIONS             output,  
                        @NEWINCOMECOMPENSATION             output,  
                        @NEWAFFLUENCEINDICATORS           output,  
                        @NEWNONPROFITAFFILIATIONS         output,  
                        @NEWPHILANTHROPICGIFTS             output,  
                        @NEWBUSINESSOWNERSHIP             output,  
                        @NEWREALESTATE                     output,  
                        @NEWSECURITIES                    output,  
                        @NEWOTHERASSETS                    output,

                        null;

                    return 0;