USP_DATAFORMTEMPLATE_VIEW_CONSOLIDATEDPROSPECTSEARCH

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN
@RE7INTEGRATIONCONFIGUREDFORUSER	bit	INOUT
@RE7DBID	int	INOUT
@RE7USERNAME	nvarchar(128)	INOUT
@RE7PASSWORD	nvarchar(128)	INOUT
@RE7DBSERIALNUMBER	nvarchar(100)	INOUT
@SOURCECODE	tinyint	INOUT
@SORTBYCODE	tinyint	INOUT
@NAMEORID	nvarchar(500)	INOUT
@LOCATION	nvarchar(500)	INOUT
@LASTNAME	nvarchar(50)	INOUT
@FIRSTNAME	nvarchar(50)	INOUT
@MIDDLENAME	nvarchar(50)	INOUT
@NICKNAME	nvarchar(50)	INOUT
@ADDRESSBLOCK	nvarchar(100)	INOUT
@CITY	nvarchar(100)	INOUT
@STATE	nvarchar(3)	INOUT
@STATEID	uniqueidentifier	INOUT
@POSTCODE	nvarchar(5)	INOUT
@INCLUDEDECEASED	bit	INOUT
@INCLUDEINACTIVE	bit	INOUT
@SPOUSELASTNAME	nvarchar(50)	INOUT
@SPOUSEFIRSTNAME	nvarchar(50)	INOUT
@AGEMIN	int	INOUT
@AGEMAX	int	INOUT
@ESTIMATEDWEALTHMINID	uniqueidentifier	INOUT
@BUSINESS	nvarchar(50)	INOUT
@CLASSYEAR	UDT_YEAR	INOUT
@RECORDTYPECODE	tinyint	INOUT
@ISEXISTING	bit	INOUT
@EXACTMATCHONLY	bit	INOUT
@OVERALLRATINGCODEID	uniqueidentifier	INOUT
@PROSPECTSEARCHEXPORTFILE	varbinary	INOUT
@PROSPECTSEARCHEXPORTFILENAME	nvarchar(255)	INOUT
@ISEXPORT	bit	INOUT
@ACCOUNTTYPECODE	smallint	INOUT
@MAXROWSCODE	int	INOUT
@ESTIMATEDWEALTHMAXID	uniqueidentifier	INOUT
@HASBUSINESS	bit	INOUT
@GIVINGMIN	nvarchar(20)	INOUT
@LARGESTGIFTMIN	nvarchar(20)	INOUT
@SECURITIESMIN	nvarchar(20)	INOUT
@GIVINGCATEGORIES	xml	INOUT
@GIVINGCATEGORYDISPLAY	nvarchar(100)	INOUT
@MOSAICS	xml	INOUT
@MOSAICDISPLAY	nvarchar(100)	INOUT
@REALESTATEMIN	nvarchar(20)	INOUT
@SPENDMIN	nvarchar(20)	INOUT
@INCOMERANGE	nvarchar(30)	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSOLIDATEDPROSPECTSEARCH
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @CURRENTAPPUSERID uniqueidentifier,
    @RE7INTEGRATIONCONFIGUREDFORUSER bit = null output,
    @RE7DBID int = null output,
    @RE7USERNAME nvarchar(128) = null output,
    @RE7PASSWORD nvarchar(128) = null output,
    @RE7DBSERIALNUMBER nvarchar(100) = null output,

  @SOURCECODE tinyint = null output,
  @SORTBYCODE tinyint = null output,
  @NAMEORID nvarchar(500) = null output,
  @LOCATION nvarchar(500) = null output,
  @LASTNAME nvarchar(50) = null output,
  @FIRSTNAME nvarchar(50) = null output,
  @MIDDLENAME nvarchar(50) = null output,
  @NICKNAME nvarchar(50) = null output,
  @ADDRESSBLOCK nvarchar(100) = null output,
  @CITY nvarchar(100) = null output,
  @STATE nvarchar(3) = null output,
  @STATEID uniqueidentifier = null output,
  @POSTCODE nvarchar(5) = null output,
  @INCLUDEDECEASED bit = null output,
  @INCLUDEINACTIVE bit = null output,
  @SPOUSELASTNAME nvarchar(50) = null output,
  @SPOUSEFIRSTNAME nvarchar(50) = null output,
  @AGEMIN integer = null output,
  @AGEMAX integer = null output,
  @ESTIMATEDWEALTHMINID uniqueidentifier = null output,
  @BUSINESS nvarchar(50) = null output,
  @CLASSYEAR dbo.UDT_YEAR = null output,
  @RECORDTYPECODE tinyint = null output,
    @ISEXISTING bit = null output,
  @EXACTMATCHONLY bit = null output,
  @OVERALLRATINGCODEID uniqueidentifier = null output,
  @PROSPECTSEARCHEXPORTFILE varbinary(max) = null output,
  @PROSPECTSEARCHEXPORTFILENAME nvarchar(255) = null output,
  @ISEXPORT bit = null output,
  @ACCOUNTTYPECODE smallint = null output,
  @MAXROWSCODE int = null output,
  @ESTIMATEDWEALTHMAXID uniqueidentifier = null output,
  @HASBUSINESS bit = null output,
  @GIVINGMIN nvarchar(20) = null output,
  @LARGESTGIFTMIN nvarchar(20) = null output,
  @SECURITIESMIN nvarchar(20) = null output,
  @GIVINGCATEGORIES xml = null output,
  @GIVINGCATEGORYDISPLAY nvarchar(100) = null output,
  @MOSAICS xml = null output,
  @MOSAICDISPLAY nvarchar(100) = null output,
  @REALESTATEMIN nvarchar(20) = null output,
  @SPENDMIN nvarchar(20) = null output,
  @INCOMERANGE nvarchar(30) = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 1;

  --Set defaults
  select 
        @SOURCECODE = 3,
        @SORTBYCODE = 1,
        @RECORDTYPECODE = 1,
        @INCLUDEDECEASED = 1,
        @INCLUDEINACTIVE = 1,
        @EXACTMATCHONLY = 0,
        @ISEXISTING = 0,
        @RE7INTEGRATIONCONFIGUREDFORUSER = 0,
        @MAXROWSCODE = 100;


    select
        @RE7DBID = RE7INTEGRATIONCONFIGURATION.RE7DBID,
        @RE7DBSERIALNUMBER = RE7INTEGRATIONCONFIGURATION.RE7DBSERIALNUMBER
    from
        dbo.RE7INTEGRATIONCONFIGURATION;

    -- Open the symmetric key for decryption
    exec dbo.USP_GET_KEY_ACCESS;

    select
    @RE7INTEGRATIONCONFIGUREDFORUSER = 1,
        @RE7USERNAME = RE7INTEGRATIONCREDENTIALS.RE7USERNAME,
        @RE7PASSWORD = coalesce(convert(nvarchar(128), DecryptByKey(RE7INTEGRATIONCREDENTIALS.RE7PASSWORD)),'')
    from
        dbo.RE7INTEGRATIONCREDENTIALS
    where
        ID = @CURRENTAPPUSERID;

    close symmetric key sym_BBInfinity;

  --Attempt to load saved parameter values
  select @ISEXISTING = 1,
         @SOURCECODE = SOURCECODE,
         @SORTBYCODE = SORTBYCODE,
         @NAMEORID = NAMEORID,
         @LOCATION = LOCATION,
         @LASTNAME = LASTNAME,
         @FIRSTNAME = FIRSTNAME,
         @MIDDLENAME = MIDDLENAME,
         @NICKNAME = NICKNAME,
         @ADDRESSBLOCK = ADDRESSBLOCK,
         @CITY = CITY,
         @STATE = STATE,
         @STATEID = STATEID,
         @POSTCODE = POSTCODE,
         @INCLUDEDECEASED = INCLUDEDECEASED,
         @INCLUDEINACTIVE = INCLUDEINACTIVE,
         @SPOUSELASTNAME = SPOUSELASTNAME,
         @SPOUSEFIRSTNAME = SPOUSEFIRSTNAME,
         @AGEMIN = AGEMIN,
         @AGEMAX = AGEMAX,
         @ESTIMATEDWEALTHMINID = ESTIMATEDWEALTHMINID,
         @ESTIMATEDWEALTHMAXID = ESTIMATEDWEALTHMAXID,
      @BUSINESS = BUSINESS,
         @HASBUSINESS = HASBUSINESS,
         @CLASSYEAR = CLASSYEAR,
         @RECORDTYPECODE = RECORDTYPECODE,
         @EXACTMATCHONLY = EXACTMATCHONLY,
         @OVERALLRATINGCODEID = OVERALLRATINGCODEID,
         @GIVINGMIN = GIVINGMIN,
         @LARGESTGIFTMIN = LARGESTGIFTMIN,
         @SECURITIESMIN = SECURITIESMIN,
         @GIVINGCATEGORIES = dbo.UFN_PROSPECTSEARCH_GET_CATEGORIES_TOITEMLISTXML(@ID),
         @GIVINGCATEGORYDISPLAY = GIVINGCATEGORYDISPLAY,
         @MOSAICS = dbo.UFN_PROSPECTSEARCH_GET_MOSAICS_TOITEMLISTXML(@ID),
         @MOSAICDISPLAY = MOSAICDISPLAY,
         @REALESTATEMIN = REALESTATEMIN,
         @SPENDMIN = SPENDMIN,
         @INCOMERANGE = INCOMERANGE,
         @ISEXPORT = ISEXPORT,
         @MAXROWSCODE = coalesce(MAXROWS, 100)
    from dbo.PROSPECTSEARCH
    where ID = @ID;

    --Set export file information
    set @PROSPECTSEARCHEXPORTFILE = null;    /* DO NOT LOAD THE FILE HERE, IT WILL BE DOWNLOADED ON DEMAND */
    set @PROSPECTSEARCHEXPORTFILENAME = 'ProspectSearch.csv';

  select @ACCOUNTTYPECODE = ACCOUNTTYPECODE from dbo.WEALTHPOINTCONFIGURATION;

    return 0;