USP_WPPROSPECTSEARCHCRITERIA

Gets WealthPoint search criteria values for a prospect.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@FIRSTNAME	nvarchar(50)	INOUT
@MIDDLENAME	nvarchar(100)	INOUT
@LASTNAME	nvarchar(100)	INOUT
@ADDRESS	nvarchar(150)	INOUT
@CITY	nvarchar(50)	INOUT
@STATE	nvarchar(100)	INOUT
@POSTCODE	nvarchar(12)	INOUT
@PHONE	nvarchar(50)	INOUT
@DATEOFBIRTH	datetime	INOUT
@COMPANY	nvarchar(100)	INOUT
@COMPANYADDRESS	nvarchar(150)	INOUT
@COMPANYCITY	nvarchar(50)	INOUT
@COMPANYSTATE	nvarchar(100)	INOUT
@COMPANYPOSTCODE	nvarchar(12)	INOUT
@SPOUSEFIRSTNAME	nvarchar(50)	INOUT
@SPOUSEMIDDLENAME	nvarchar(100)	INOUT
@SPOUSELASTNAME	nvarchar(100)	INOUT
@ISORGANIZATION	bit	INOUT
@DISABLEDWEALTHUPDATES	bit	INOUT
@NICKNAME	nvarchar(50)	INOUT

Definition

Copy



            CREATE procedure dbo.USP_WPPROSPECTSEARCHCRITERIA (
                @ID uniqueidentifier,
                @FIRSTNAME nvarchar(50) = null output,
                @MIDDLENAME nvarchar(100) = null output,
                @LASTNAME nvarchar(100) = null output,
                @ADDRESS nvarchar(150) = null output,
                @CITY nvarchar(50) = null output,
                @STATE nvarchar(100) = null output,
                @POSTCODE nvarchar(12) = null output,
                @PHONE nvarchar(50) = null output,
                @DATEOFBIRTH datetime = null output,
                @COMPANY nvarchar(100) = null output,
                @COMPANYADDRESS nvarchar(150) = null output,
                @COMPANYCITY nvarchar(50) = null output,
                @COMPANYSTATE nvarchar(100) = null output,
                @COMPANYPOSTCODE nvarchar(12) = null output,
                @SPOUSEFIRSTNAME nvarchar(50) = null output,
                @SPOUSEMIDDLENAME nvarchar(100) = null output,
                @SPOUSELASTNAME nvarchar(100) = null output,
                @ISORGANIZATION bit = null output,
                @DISABLEDWEALTHUPDATES bit = null output,
                @NICKNAME nvarchar(50) = null output
            ) as begin
                set nocount on;

                select
                    @FIRSTNAME = C.FIRSTNAME,
                    @MIDDLENAME = C.MIDDLENAME,
                    @LASTNAME = C.KEYNAME,
                    @NICKNAME = C.NICKNAME,
                    @ADDRESS = A.ADDRESSBLOCK,
                    @CITY = A.CITY,
                    @STATE = dbo.UFN_STATE_GETABBREVIATION(A.STATEID),
                    @POSTCODE = A.POSTCODE,
                    @PHONE = P.NUMBER,
                    @DATEOFBIRTH = dbo.UFN_DATE_FROMFUZZYDATE(C.BIRTHDATE),
                    @SPOUSEFIRSTNAME = SPOUSE.FIRSTNAME,
                    @SPOUSEMIDDLENAME = SPOUSE.MIDDLENAME,
                    @SPOUSELASTNAME = SPOUSE.KEYNAME,
                    @COMPANY = CBUSINESS.NAME,
                    @COMPANYADDRESS = ABUSINESS.ADDRESSBLOCK,
                    @COMPANYCITY = ABUSINESS.CITY,
                    @COMPANYSTATE = dbo.UFN_STATE_GETABBREVIATION(ABUSINESS.STATEID),
                    @COMPANYPOSTCODE = ABUSINESS.POSTCODE,
                    @ISORGANIZATION = case when O.ID is null then 0 else 1 end,
                    @DISABLEDWEALTHUPDATES = case when DWU.ID is null then 0 else 1 end
                from
                    dbo.CONSTITUENT C
                    left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
                    left outer join dbo.ADDRESS A on A.CONSTITUENTID=C.ID and A.ISPRIMARY=1
                    left outer join dbo.PHONE P on P.CONSTITUENTID=C.ID and P.ISPRIMARY=1
                    left outer join dbo.RELATIONSHIP RSPOUSE on C.ID = RSPOUSE.RELATIONSHIPCONSTITUENTID and RSPOUSE.ISSPOUSE=1
                    left outer join dbo.CONSTITUENT SPOUSE on SPOUSE.ID = RSPOUSE.RECIPROCALCONSTITUENTID
                    left outer join dbo.ORGANIZATIONDATA O on O.ID = C.ID
                    left outer join dbo.RELATIONSHIP RBUSINESS on C.ID = RBUSINESS.RELATIONSHIPCONSTITUENTID and RSPOUSE.ISPRIMARYBUSINESS=1
                    left outer join dbo.ADDRESS ABUSINESS on ABUSINESS.CONSTITUENTID=RBUSINESS.RECIPROCALCONSTITUENTID and ABUSINESS.ISPRIMARY=1
                    left outer join dbo.CONSTITUENT CBUSINESS on CBUSINESS.ID=RBUSINESS.RECIPROCALCONSTITUENTID
                where
                    C.ID = @ID;

                return 0;

            end;