USP_DATAFORMTEMPLATE_EDITLOAD_BATCHPEOPLEFINDERBATCHROW

The load procedure used by the edit dataform template "PeopleFinder Batch Row Edit 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.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@SEQUENCE	int	INOUT	Sequence
@PEOPLEFINDERID	uniqueidentifier	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT	Constituent
@OLDADDRESSID	uniqueidentifier	INOUT
@NEWADDRESSBLOCK	nvarchar(150)	INOUT	New address
@NEWCITY	nvarchar(50)	INOUT	New city
@NEWPOSTCODE	nvarchar(12)	INOUT	New zip
@OTHERLASTNAME	nvarchar(100)	INOUT	Other last name
@NEWPHONENUMBER	nvarchar(100)	INOUT	New phone number
@NEWADDRESSINFOSOURCE	nvarchar(20)	INOUT	New address info source
@DECEASEDYEAR	UDT_FUZZYDATE	INOUT	Year deceased
@DECEASEDYEARDISPLAY	nvarchar(4)	INOUT	Year deceased
@CONFIDENCELEVEL	smallint	INOUT	Confidence level
@SENDMAIL	bit	INOUT	Send mail
@NEWSTATE	nvarchar(50)	INOUT	New state
@ISDECEASED	bit	INOUT	Deceased
@ADDRESSBLOCK	nvarchar(150)	INOUT	Current address
@CITY	nvarchar(50)	INOUT	Current city
@STATE	nvarchar(50)	INOUT	Current state
@POSTCODE	nvarchar(12)	INOUT	Current zip
@NEWSTATEID	uniqueidentifier	INOUT
@CONFIDENCELEVELLETTER	nvarchar(1)	INOUT	Confidence level

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_BATCHPEOPLEFINDERBATCHROW
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @SEQUENCE int = null output,
    @PEOPLEFINDERID uniqueidentifier = null output,
    @CONSTITUENTID uniqueidentifier = null output,
    @OLDADDRESSID uniqueidentifier = null output,
    @NEWADDRESSBLOCK nvarchar(150) = null output,
    @NEWCITY nvarchar(50) = null output,
    @NEWPOSTCODE nvarchar(12) = null output,
    @OTHERLASTNAME nvarchar(100) = null output,
    @NEWPHONENUMBER nvarchar(100) = null output,
    @NEWADDRESSINFOSOURCE nvarchar(20) = null output,
    @DECEASEDYEAR dbo.UDT_FUZZYDATE = null output,
    @DECEASEDYEARDISPLAY nvarchar(4) = null output,
    @CONFIDENCELEVEL smallint = null output,    
    @SENDMAIL bit = null output,
    @NEWSTATE nvarchar(50) = null output,
    @ISDECEASED bit = null output,
    @ADDRESSBLOCK nvarchar(150) = null output,
    @CITY nvarchar(50) = null output,
    @STATE nvarchar(50) = null output,
    @POSTCODE nvarchar(12) = null output,
    @NEWSTATEID uniqueidentifier = null output,
    @CONFIDENCELEVELLETTER nvarchar(1) = null output
)
as
    set nocount on;

    set @DATALOADED = 0;
    set @TSLONG = 0;

    select
    @DATALOADED = 1,
    @TSLONG = BATCHPEOPLEFINDER.TSLONG,
    @SEQUENCE = BATCHPEOPLEFINDER.SEQUENCE,
    @PEOPLEFINDERID = BATCHPEOPLEFINDER.PEOPLEFINDERID,
    @CONSTITUENTID = BATCHPEOPLEFINDER.CONSTITUENTID,
    @OLDADDRESSID = BATCHPEOPLEFINDER.OLDADDRESSID,
    @NEWADDRESSBLOCK = BATCHPEOPLEFINDER.NEWADDRESSBLOCK,
    @NEWCITY = BATCHPEOPLEFINDER.NEWCITY,
    @NEWSTATE = dbo.UFN_STATE_GETABBREVIATION(BATCHPEOPLEFINDER.NEWSTATEID),
    @NEWPOSTCODE = BATCHPEOPLEFINDER.NEWPOSTCODE,
    @OTHERLASTNAME = BATCHPEOPLEFINDER.OTHERLASTNAME,
    @NEWPHONENUMBER = BATCHPEOPLEFINDER.NEWPHONENUMBER,
    @NEWADDRESSINFOSOURCE = BATCHPEOPLEFINDER.NEWADDRESSINFOSOURCE,
    @DECEASEDYEAR = BATCHPEOPLEFINDER.DECEASEDYEAR,
    @DECEASEDYEARDISPLAY = BATCHPEOPLEFINDER.DECEASEDYEARDISPLAY,
    @CONFIDENCELEVEL = BATCHPEOPLEFINDER.CONFIDENCELEVEL,
    @CONFIDENCELEVELLETTER = case when CONFIDENCELEVEL > 530 then 'A'
                              when CONFIDENCELEVEL > 454 then 'B'
                              when CONFIDENCELEVEL > 335 then 'C'
                              else 'D'
                             end,
    @SENDMAIL = BATCHPEOPLEFINDER.SENDMAIL,
    @ISDECEASED = ISDECEASED,
    @ADDRESSBLOCK = ADDRESS.ADDRESSBLOCK,
    @CITY = ADDRESS.CITY,
    @STATE = STATE.ABBREVIATION,
    @POSTCODE = ADDRESS.POSTCODE,
    @NEWSTATEID = BATCHPEOPLEFINDER.NEWSTATEID

    from
        dbo.BATCHPEOPLEFINDER
    left join
        dbo.ADDRESS on ADDRESS.ID = BATCHPEOPLEFINDER.OLDADDRESSID
    left join
        dbo.STATE on STATE.ID = ADDRESS.STATEID
    where
        BATCHPEOPLEFINDER.ID = @ID;

    return 0;