USP_DATAFORMTEMPLATE_VIEW_EXTENDEDRELATIONSHIP_BO

The load procedure used by the view dataform template "Extended Relationship Business Ownership View"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(72)	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.
@SOURCE	nvarchar(100)	INOUT	Source
@SICNAME	nvarchar(100)	INOUT	Trade name
@COMPANYADDRESS	nvarchar(300)	INOUT	Company address
@MAILINGADDRESS	nvarchar(300)	INOUT	Mailing address
@PHONE	nvarchar(20)	INOUT	Phone
@HOMEADDRESS	nvarchar(300)	INOUT	Home address
@YEAROFBIRTH	UDT_YEAR	INOUT	Year of birth
@GENDER	nvarchar(10)	INOUT	Gender
@FULLNAME	nvarchar(300)	INOUT	Relationship's name
@EXECCOUNT	int	INOUT

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EXTENDEDRELATIONSHIP_BO
                (
                    @ID nvarchar(72),
                    @DATALOADED bit = 0 output,
                    @SOURCE nvarchar(100) = null output,
                    @SICNAME nvarchar(100) = null output,
                    @COMPANYADDRESS nvarchar(300) = null output,
                    @MAILINGADDRESS nvarchar(300) = null output,
                    @PHONE nvarchar(20) = null output,
                    @HOMEADDRESS nvarchar(300) = null output,
                    @YEAROFBIRTH dbo.UDT_YEAR = null output,
                    @GENDER nvarchar(10) = null output,
                    @FULLNAME nvarchar(300) = null output,
                    @EXECCOUNT int = null output
                )
                as
                    set nocount on;

                    declare @BOID uniqueidentifier
                    declare @RELATIONSHIPID uniqueidentifier
                    declare @WPRELATIONSHIP_BO_ID uniqueidentifier

                    set @DATALOADED = 0;
                    set @EXECCOUNT = 0;

                    if LEN(@ID) = 36
                        select @BOID = substring(@ID, 1, 36)
                    else if LEN(@ID) = 72
                        select
                            @BOID = substring(@ID, 1, 36),
                            @RELATIONSHIPID = substring(@ID, 37, 36)

                    select 
                        @DATALOADED = 1,
                        @SOURCE = BO.SOURCE + ' (Businesses)',
                        @SICNAME = BO.SICNAME,
                        @COMPANYADDRESS = dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(BO.LINE1, '', BO.CITY, BO.STATE, BO.ZIP),
                        @MAILINGADDRESS = dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(BO.MAILINGLINE1, '', BO.MAILINGCITY, BO.MAILINGSTATE, BO.MAILINGZIP),
                        @PHONE = BO.PHONE,
                        @HOMEADDRESS = case
                            when @RELATIONSHIPID is null then dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(BO.HOMELINE1, '', BO.HOMECITY, BO.HOMESTATE, BO.HOMEZIP)
                            else dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(REL_BO.ADDRESS1, '', REL_BO.CITY1, REL_BO.STATE1, REL_BO.ZIP1)
                            end,
                        @YEAROFBIRTH = case
                            when @RELATIONSHIPID is null then BO.YEAROFBIRTH
                            else REL_BO.YEAROFBIRTH
                            end,
                        @GENDER = case
                            when @RELATIONSHIPID is null then BO.GENDER
                            else REL_BO.GENDER
                            end,
                        @FULLNAME = case
                            when @RELATIONSHIPID is null then BO.FULLNAME
                            else REL_BO.FULLNAME
                            end,
                        @WPRELATIONSHIP_BO_ID = WPEIN.ID
                    from 
                        dbo.WPBUSINESSOWNERSHIP BO
                    left join dbo.WPRELATIONSHIP_BO_IND REL_BO
                        on REL_BO.ID = @RELATIONSHIPID
                    left join dbo.WPRELATIONSHIP_BO WPEIN
                        on BO.WPRELATIONSHIP_BO_ID = WPEIN.ID
                    where BO.ID = @BOID

          select
            @EXECCOUNT = COUNT(ID)
          from 
            dbo.WPRELATIONSHIP_BO_IND
          where
            WPRELATIONSHIP_BO_ID = @WPRELATIONSHIP_BO_ID

                    return 0;