USP_DATAFORMTEMPLATE_EDITLOAD_PARTIALINFORMATIONSEARCHCONSTITUENTSELECT

The load procedure used by the edit dataform template "Prospect Quick Search Constituent Real Estate Select 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.
@CONSTITUENTID	uniqueidentifier	INOUT	Constituent
@COUNTRYID	uniqueidentifier	INOUT	Country ID
@PROPERTYADDRESSBLOCK	nvarchar(150)	INOUT	Property address block
@PROPERTYUNITNUMBER	nvarchar(25)	INOUT	Property unit number
@PROPERTYCITY	nvarchar(50)	INOUT	Property city
@PROPERTYSTATE	uniqueidentifier	INOUT	Property state
@PROPERTYPOSTCODE	nvarchar(10)	INOUT	Property post code
@MAILINGADDRESSBLOCK	nvarchar(150)	INOUT	Property address block
@MAILINGUNITNUMBER	nvarchar(25)	INOUT	Property unit number
@MAILINGCITY	nvarchar(50)	INOUT	Property city
@MAILINGSTATE	uniqueidentifier	INOUT	Property state
@MAILINGPOSTCODE	nvarchar(10)	INOUT	Property post code
@ASSESSEEMAILINGADDRESSBLOCK	nvarchar(150)	INOUT	Property address block
@ASSESSEEMAILINGADDRESSUNITNUMBER	nvarchar(25)	INOUT	Property unit number
@ASSESSEEMAILINGCITY	nvarchar(50)	INOUT	Property city
@ASSESSEEMAILINGSTATE	uniqueidentifier	INOUT	Property state
@ASSESSEEMAILINGPOSTCODE	nvarchar(10)	INOUT	Property post code
@BUYERMAILINGADDRESSBLOCK	nvarchar(150)	INOUT	Property address block
@BUYERMAILINGADDRESSUNITNUMBER	nvarchar(25)	INOUT	Property unit number
@BUYERMAILINGCITY	nvarchar(50)	INOUT	Property city
@BUYERMAILINGSTATE	uniqueidentifier	INOUT	Property state
@BUYERMAILINGPOSTCODE	nvarchar(10)	INOUT	Property post code
@INFOSOURCECODEID	uniqueidentifier	INOUT	Info source code ID

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_PARTIALINFORMATIONSEARCHCONSTITUENTSELECT(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @CONSTITUENTID uniqueidentifier = null output,
    @COUNTRYID uniqueidentifier = null output,
    @PROPERTYADDRESSBLOCK nvarchar(150) = null output,
    @PROPERTYUNITNUMBER nvarchar(25) = null output,
    @PROPERTYCITY nvarchar(50) = null output,
    @PROPERTYSTATE uniqueidentifier = null output,
    @PROPERTYPOSTCODE nvarchar(10) = null output,
    @MAILINGADDRESSBLOCK nvarchar(150) = null output,
    @MAILINGUNITNUMBER nvarchar(25) = null output,
    @MAILINGCITY nvarchar(50) = null output,
    @MAILINGSTATE uniqueidentifier = null output,
    @MAILINGPOSTCODE nvarchar(10) = null output,
    @ASSESSEEMAILINGADDRESSBLOCK nvarchar(150) = null output,
    @ASSESSEEMAILINGADDRESSUNITNUMBER nvarchar(25) = null output,
    @ASSESSEEMAILINGCITY nvarchar(50) = null output,
    @ASSESSEEMAILINGSTATE uniqueidentifier = null output,
    @ASSESSEEMAILINGPOSTCODE nvarchar(10) = null output,
    @BUYERMAILINGADDRESSBLOCK nvarchar(150) = null output,
    @BUYERMAILINGADDRESSUNITNUMBER nvarchar(25) = null output,
    @BUYERMAILINGCITY nvarchar(50) = null output,
    @BUYERMAILINGSTATE uniqueidentifier = null output,
    @BUYERMAILINGPOSTCODE nvarchar(10) = null output,
    @INFOSOURCECODEID uniqueidentifier = null output
)
as    

    set nocount on;

    set @DATALOADED = 0

    select @COUNTRYID = ID from dbo.COUNTRY where ABBREVIATION = 'USA'

    select
        @PROPERTYADDRESSBLOCK = R.PROPERTYADDRESSBLOCK,
        @PROPERTYUNITNUMBER = R.PROPERTYUNITNUMBER,
        @PROPERTYCITY = R.PROPERTYCITY,
        @PROPERTYSTATE = PS.ID,
        @PROPERTYPOSTCODE = R.PROPERTYPOSTCODE,
        @MAILINGADDRESSBLOCK = R.MAILINGADDRESSBLOCK,
        @MAILINGUNITNUMBER = R.MAILINGUNITNUMBER,
        @MAILINGCITY = R.MAILINGCITY,
        @MAILINGSTATE = MS.ID,
        @MAILINGPOSTCODE = R.MAILINGPOSTCODE,
        @ASSESSEEMAILINGADDRESSBLOCK = RD.ASSESSEEMAILINGADDRESSBLOCK,
        @ASSESSEEMAILINGADDRESSUNITNUMBER = RD.ASSESSEEMAILINGADDRESSUNITNUMBER,
        @ASSESSEEMAILINGCITY = RD.ASSESSEEMAILINGCITY,
        @ASSESSEEMAILINGSTATE = A.ID,
        @ASSESSEEMAILINGPOSTCODE = RD.ASSESSEEMAILINGPOSTCODE,
        @BUYERMAILINGADDRESSBLOCK = RD.BUYERMAILINGADDRESSBLOCK,
        @BUYERMAILINGADDRESSUNITNUMBER = RD.BUYERMAILINGADDRESSUNITNUMBER,
        @BUYERMAILINGCITY = RD.BUYERMAILINGCITY,
        @BUYERMAILINGSTATE = BS.ID,
        @BUYERMAILINGPOSTCODE = RD.BUYERMAILINGPOSTCODE,
        @DATALOADED = 1
    from
        dbo.PARTIALINFORMATIONSEARCHRESULT_REALESTATE R
    left join
        dbo.PARTIALINFORMATIONSEARCHRESULT_REALESTATEDETAIL RD on RD.PARTIALINFORMATIONSEARCHRESULT_REALESTATEID = R.ID
    left join
        dbo.STATE PS on PS.ABBREVIATION = R.PROPERTYSTATE and PS.COUNTRYID = @COUNTRYID
    left join
        dbo.STATE MS on MS.ABBREVIATION = R.MAILINGSTATE and MS.COUNTRYID = @COUNTRYID
    left join
        dbo.STATE A on A.ABBREVIATION = RD.ASSESSEEMAILINGSTATE and A.COUNTRYID = @COUNTRYID 
    left join
        dbo.STATE BS on BS.ABBREVIATION = RD.BUYERMAILINGSTATE and BS.COUNTRYID = @COUNTRYID 
    where R.ID = @ID

    declare @CHANGEAGENTID uniqueidentifier
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    select @INFOSOURCECODEID = ID from dbo.INFOSOURCECODE where DESCRIPTION = 'CoreLogic'
    if @INFOSOURCECODEID is null begin
        insert into dbo.INFOSOURCECODE
        (
            DESCRIPTION,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values
        (
            'CoreLogic',
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        )
        select @INFOSOURCECODEID = ID from dbo.INFOSOURCECODE where DESCRIPTION = 'CoreLogic'
    end
    return 0;