USP_DATAFORMTEMPLATE_EDITLOAD_PARTIALINFORMATIONSEARCHBUSINESSOWNERSHIP_CONSTITFROMBIO

The load procedure used by the edit dataform template "Prospect Quick Search Business Ownership Constit From Bio 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
@FULLNAME	nvarchar(100)	INOUT	Full name
@LASTNAME	nvarchar(30)	INOUT	Last name
@FIRSTNAME	nvarchar(25)	INOUT	First name
@MIDDLENAME	nvarchar(10)	INOUT	Middle name
@SUFFIXCODEID	uniqueidentifier	INOUT	Suffix
@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
@BUSINESS_PHONE	nvarchar(20)	INOUT	Phone
@ADDRESS_INFOSOURCECODEID	uniqueidentifier	INOUT	Information source

Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_PARTIALINFORMATIONSEARCHBUSINESSOWNERSHIP_CONSTITFROMBIO(
                        @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,
                        @FULLNAME nvarchar(100) = null output,
                        @LASTNAME nvarchar(30) = null output,
                        @FIRSTNAME nvarchar(25) = null output,
                        @MIDDLENAME nvarchar(10) = null output,
                        @SUFFIXCODEID uniqueidentifier = 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,
                        @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 = BODB.[TSLONG],
                            @BUSINESSOPTIONCODE = 0,
                            @BUSINESSID = null,
                            @BUSINESSRELATIONSHIPTYPECODEID = null,
                            @BUSINESSRECIPROCALTYPECODEID = null,
                            @ADDRESSTYPECODE = 1,
                            @FULLNAME = BODB.[NAME],
                            @LASTNAME = BODB.[LAST],
                            @FIRSTNAME = BODB.[FIRST],
                            @MIDDLENAME = BODB.[MIDDLE],
                            @SUFFIXCODEID = (select [ID] from dbo.[SUFFIXCODE] where [DESCRIPTION] = BODB.[SUFFIX]),
                            @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),
                            @BUSINESS_PHONE = BO.[PHONE]
                        from
                            dbo.[PARTIALINFORMATIONSEARCHRESULT_BUSINESSOWNERSHIPDETAIL_BIOS] BODB
                        left join
                            dbo.[PARTIALINFORMATIONSEARCHRESULT_BUSINESSOWNERSHIPDETAIL] BOD on BOD.[ID]  = BODB.[PARTIALINFORMATIONSEARCHRESULT_BUSINESSOWNERSHIPDETAILID]
                        left join
                            dbo.[PARTIALINFORMATIONSEARCHRESULT_BUSINESSOWNERSHIP] BO on BO.[ID] = BOD.[PARTIALINFORMATIONSEARCHRESULT_BUSINESSOWNERSHIPID]
                        where BODB.[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;