USP_DATAFORMTEMPLATE_EDITLOAD_PARTIALINFORMATIONSEARCHBUSINESSOWNERSHIP_CONSTITFROMEXEC

The load procedure used by the edit dataform template "Prospect Quick Search Business Ownership Constit From Exec Edit Data 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.
@BUSINESSOPTIONCODE	tinyint	INOUT	For organization:
@BUSINESSID	uniqueidentifier	INOUT	Organization
@BUSINESSRELATIONSHIPTYPECODEID	uniqueidentifier	INOUT	Individual is the
@BUSINESSRECIPROCALTYPECODEID	uniqueidentifier	INOUT	Organization is the
@ADDRESSTYPECODE	tinyint	INOUT	Address to use
@LASTNAME	nvarchar(50)	INOUT	Last name
@FIRSTNAME	nvarchar(50)	INOUT	First name
@MIDDLENAME	nvarchar(50)	INOUT	Middle name
@PREFIXCODEID	uniqueidentifier	INOUT	Prefix
@SUFFIXCODEID	uniqueidentifier	INOUT	Suffix
@TITLE	nvarchar(30)	INOUT	Title
@YEAROFBIRTH	UDT_YEAR	INOUT	Year of birth
@GENDERCODE	tinyint	INOUT	Gender
@BUSINESSNAME	nvarchar(30)	INOUT	Name
@BUSINESS_COUNTRYID	uniqueidentifier	INOUT	Business country
@BUSINESS_ADDRESSBLOCK	nvarchar(100)	INOUT	Business address
@BUSINESS_CITY	nvarchar(50)	INOUT	Business city
@BUSINESS_STATEID	uniqueidentifier	INOUT	Business state
@BUSINESS_POSTCODE	nvarchar(10)	INOUT	Business zip
@MAILING_COUNTRYID	uniqueidentifier	INOUT	Mailing country
@MAILING_ADDRESSBLOCK	nvarchar(100)	INOUT	Mailing address
@MAILING_CITY	nvarchar(50)	INOUT	Mailing city
@MAILING_STATEID	uniqueidentifier	INOUT	Mailing state
@MAILING_POSTCODE	nvarchar(10)	INOUT	Mailing zip
@EXECUTIVE_COUNTRYID	uniqueidentifier	INOUT	Executive country
@EXECUTIVE_ADDRESSBLOCK	nvarchar(100)	INOUT	Executive address
@EXECUTIVE_CITY	nvarchar(50)	INOUT	Executive city
@EXECUTIVE_STATEID	uniqueidentifier	INOUT	Executive state
@EXECUTIVE_POSTCODE	nvarchar(10)	INOUT	Executive zip
@BUSINESS_PHONE	nvarchar(20)	INOUT	Phone
@ADDRESS_INFOSOURCECODEID	uniqueidentifier	INOUT	Information source

Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_PARTIALINFORMATIONSEARCHBUSINESSOWNERSHIP_CONSTITFROMEXEC(
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @TSLONG bigint = 0 output,
                        @BUSINESSOPTIONCODE tinyint = null output,
                        @BUSINESSID uniqueidentifier = null output,
                        @BUSINESSRELATIONSHIPTYPECODEID uniqueidentifier = null output,
                        @BUSINESSRECIPROCALTYPECODEID uniqueidentifier = null output,
                        @ADDRESSTYPECODE tinyint = null output,
                        @LASTNAME nvarchar(50) = null output,
                        @FIRSTNAME nvarchar(50) = null output,
                        @MIDDLENAME nvarchar(50) = null output,
                        @PREFIXCODEID uniqueidentifier = null output,
                        @SUFFIXCODEID uniqueidentifier = null output,
                        @TITLE nvarchar(30) = null output,
                        @YEAROFBIRTH dbo.UDT_YEAR = null output,
                        @GENDERCODE tinyint = null output,
                        @BUSINESSNAME nvarchar(30) = null output,
                        @BUSINESS_COUNTRYID uniqueidentifier = null output,
                        @BUSINESS_ADDRESSBLOCK nvarchar(100) = null output,
                        @BUSINESS_CITY nvarchar(50) = null output,
                        @BUSINESS_STATEID uniqueidentifier = null output,
                        @BUSINESS_POSTCODE nvarchar(10) = null output,
                        @MAILING_COUNTRYID uniqueidentifier = null output,
                        @MAILING_ADDRESSBLOCK nvarchar(100) = null output,
                        @MAILING_CITY nvarchar(50) = null output,
                        @MAILING_STATEID uniqueidentifier = null output,
                        @MAILING_POSTCODE nvarchar(10) = null output,
                        @EXECUTIVE_COUNTRYID uniqueidentifier = null output,
                        @EXECUTIVE_ADDRESSBLOCK nvarchar(100) = null output,
                        @EXECUTIVE_CITY nvarchar(50) = null output,
                        @EXECUTIVE_STATEID uniqueidentifier = null output,
                        @EXECUTIVE_POSTCODE nvarchar(10) = null output,
                        @BUSINESS_PHONE nvarchar(20) = null output,
                        @ADDRESS_INFOSOURCECODEID uniqueidentifier = null output
                    ) as
                        set nocount on;

                        set @DATALOADED = 0
                        set @TSLONG = 0

                        declare @DEFAULT_COUNTRYID uniqueidentifier;
                        select @DEFAULT_COUNTRYID = ID from dbo.COUNTRY where ABBREVIATION = 'USA'

                        select
                            @DATALOADED = 1,
                            @TSLONG = BODE.[TSLONG],
                            @BUSINESSOPTIONCODE = 0,
                            @BUSINESSID = null,
                            @BUSINESSRELATIONSHIPTYPECODEID = null,
                            @BUSINESSRECIPROCALTYPECODEID = null,
                            @ADDRESSTYPECODE = 0,
                            @LASTNAME = BODE.[LAST],
                            @FIRSTNAME = BODE.[FIRST],
                            @MIDDLENAME = BODE.[MIDDLE],
                            @PREFIXCODEID = (select [ID] from dbo.[TITLECODE] where [DESCRIPTION] = BODE.[PREFIX]),
                            @SUFFIXCODEID = (select [ID] from dbo.[SUFFIXCODE] where [DESCRIPTION] = BODE.[SUFFIX]),
                            @TITLE = BODE.[TITLE],
                            @YEAROFBIRTH = BODE.[YEAROFBIRTH],
                            @GENDERCODE = BODE.[GENDERCODE],
                            @BUSINESSNAME = BO.[COMPANY],
                            @BUSINESS_COUNTRYID = @DEFAULT_COUNTRYID,
                            @BUSINESS_ADDRESSBLOCK = BO.[ADDRESS],
                            @BUSINESS_CITY = BO.[CITY],
                            @BUSINESS_STATEID = dbo.UFN_STATE_GETID(@DEFAULT_COUNTRYID, BO.[STATE], 1),
                            @BUSINESS_POSTCODE = BO.[ZIP],
                            @MAILING_COUNTRYID = @DEFAULT_COUNTRYID,
                            @MAILING_ADDRESSBLOCK = BOD.[MAILINGADDRESS],
                            @MAILING_CITY = BOD.[MAILINGCITY],
                            @MAILING_STATEID = dbo.UFN_STATE_GETID(@DEFAULT_COUNTRYID, BOD.[MAILINGSTATE], 1),
                            @MAILING_POSTCODE = BOD.[MAILINGZIP] + (case when LEN(BOD.[MAILINGZIP4]) > 0 then '-' + BOD.[MAILINGZIP4] else '' end),
                            @EXECUTIVE_COUNTRYID = @DEFAULT_COUNTRYID,
                            @EXECUTIVE_ADDRESSBLOCK = BODE.[ADDRESS],
                            @EXECUTIVE_CITY = BODE.[CITY],
                            @EXECUTIVE_STATEID = dbo.UFN_STATE_GETID(@DEFAULT_COUNTRYID, BODE.[STATE], 1),
                            @EXECUTIVE_POSTCODE = BODE.[ZIP],
                            @BUSINESS_PHONE = BO.[PHONE]
                        from
                            dbo.[PARTIALINFORMATIONSEARCHRESULT_BUSINESSOWNERSHIPDETAIL_EXECS] BODE
                        left join
                            dbo.[PARTIALINFORMATIONSEARCHRESULT_BUSINESSOWNERSHIPDETAIL] BOD on BOD.[ID]  = BODE.[PARTIALINFORMATIONSEARCHRESULT_BUSINESSOWNERSHIPDETAILID]
                        left join
                            dbo.[PARTIALINFORMATIONSEARCHRESULT_BUSINESSOWNERSHIP] BO on BO.[ID] = BOD.[PARTIALINFORMATIONSEARCHRESULT_BUSINESSOWNERSHIPID]
                        where BODE.[ID] = @ID

                        declare @CHANGEAGENTID uniqueidentifier
                        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

                        declare @CURRENTDATE datetime
                        set @CURRENTDATE = getdate()

                        select @ADDRESS_INFOSOURCECODEID = ID from dbo.INFOSOURCECODE where DESCRIPTION = 'D&B'
                        if @ADDRESS_INFOSOURCECODEID is null begin
                            insert into dbo.INFOSOURCECODE
                            (
                                DESCRIPTION,
                                ADDEDBYID,
                                CHANGEDBYID,
                                DATEADDED,
                                DATECHANGED
                            )
                            values
                            (
                                'D&B',
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            )
                            select @ADDRESS_INFOSOURCECODEID = ID from dbo.INFOSOURCECODE where DESCRIPTION = 'D&B'
                        end

                        return 0;