USP_DATAFORMTEMPLATE_VIEW_ADDRESS_2

The load procedure used by the view dataform template "Address View Form 2"

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.
@ADDRESS	nvarchar(300)	INOUT	Address
@PRIMARY	bit	INOUT	Primary
@DONOTMAIL	bit	INOUT	Do not mail
@STARTDATE	UDT_MONTHDAY	INOUT	Start date
@ENDDATE	UDT_MONTHDAY	INOUT	End date
@COUNTRY	nvarchar(100)	INOUT	Country
@ORGNAME	nvarchar(154)	INOUT	Organization
@DONOTMAILREASONCODE	nvarchar(100)	INOUT	Reason
@ISFORMER	bit	INOUT	Is former
@DATEADDED	date	INOUT	Date added
@HISTORICALSTARTDATE	date	INOUT	Historical start date
@HISTORICALENDDATE	date	INOUT	Historical end date
@INFOSOURCECODE	nvarchar(100)	INOUT	Information source
@INFOSOURCECOMMENTS	nvarchar(256)	INOUT	Comments
@ISCONFIDENTIAL	bit	INOUT	Confidential
@ISCONTACT	bit	INOUT	Contact
@JOBPOSITION	nvarchar(100)	INOUT	Position
@JOBDEPARTMENT	nvarchar(100)	INOUT	Department
@JOBDIVISION	nvarchar(100)	INOUT	Division
@ORIGINCODE	tinyint	INOUT	Origin

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ADDRESS_2 
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @ADDRESS nvarchar(300) = null output,
                    @PRIMARY bit = null output,
                    @DONOTMAIL bit = null output,
                    @STARTDATE dbo.UDT_MONTHDAY = null output,
                    @ENDDATE dbo.UDT_MONTHDAY = null output,
                    @COUNTRY nvarchar(100) = null output,
                    @ORGNAME nvarchar(154) = null output,
                    @DONOTMAILREASONCODE nvarchar(100) = null output,
                    @ISFORMER bit = null output,
                    @DATEADDED date = null output,
                    @HISTORICALSTARTDATE date = null output,
                    @HISTORICALENDDATE date = null output,
                    @INFOSOURCECODE nvarchar(100) = null output,
                    @INFOSOURCECOMMENTS nvarchar(256) = null output,
                    @ISCONFIDENTIAL bit = null output,
                    @ISCONTACT bit = null output,
                    @JOBPOSITION nvarchar(100) = null output,
                    @JOBDEPARTMENT nvarchar(100) = null output,
                    @JOBDIVISION nvarchar(100) = null output,
                    @ORIGINCODE tinyint = null output
                ) as
                set nocount on;

                set @DATALOADED = 0;
                set @ISCONTACT = 0;

                declare @RELATIONSHIPID uniqueidentifier;

                select
                    @DATALOADED = 1,
                    @ADDRESS = dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID),
                    @PRIMARY = dbo.ADDRESS.ISPRIMARY,
                    @DONOTMAIL = dbo.ADDRESS.DONOTMAIL,
                    @STARTDATE = dbo.ADDRESS.STARTDATE,
                    @ENDDATE = dbo.ADDRESS.ENDDATE,
                    @COUNTRY = (select case when charindex('[country]', COUNTRYADDRESSFORMAT.FORMATSTRING) > 0 then
                                                                    ''
                                                                else
                                                                    COUNTRY.DESCRIPTION
                                                                end
                                                            from
                                                                dbo.COUNTRY
                                                                inner join dbo.COUNTRYADDRESSFORMAT on COUNTRY.COUNTRYADDRESSFORMATID = COUNTRYADDRESSFORMAT.ID
                                                            where
                                                                COUNTRY.ID = ADDRESS.COUNTRYID),
                    @RELATIONSHIPID = dbo.ADDRESS.RELATIONSHIPID,
                    @DONOTMAILREASONCODE = '(' + (select DESCRIPTION from DONOTMAILREASONCODE where ID=dbo.ADDRESS.DONOTMAILREASONCODEID) + ')',
                    @ISFORMER = case when HISTORICALENDDATE is null then 0 else 1 end,
                    @DATEADDED = ADDRESS.DATEADDED,
                    @HISTORICALSTARTDATE = HISTORICALSTARTDATE,
                    @HISTORICALENDDATE = HISTORICALENDDATE,
                    @INFOSOURCECODE = dbo.UFN_INFOSOURCECODE_GETDESCRIPTION(ADDRESSVALIDATIONUPDATE.INFOSOURCECODEID),
                    @INFOSOURCECOMMENTS = ADDRESSVALIDATIONUPDATE.INFOSOURCECOMMENTS,
                    @ISCONFIDENTIAL = ADDRESS.ISCONFIDENTIAL,
                    @ORIGINCODE = ADDRESSVALIDATIONUPDATE.ORIGINCODE
                    from 
                        dbo.ADDRESS
                    left join
                        dbo.ADDRESSVALIDATIONUPDATE on ADDRESS.ID = ADDRESSVALIDATIONUPDATE.ID
                    where 
                        ADDRESS.ID = @ID;

                if @ORIGINCODE is null
                    set @ORIGINCODE = 0

                if @RELATIONSHIPID is not null
                begin
                    select 
                        @ISCONTACT = r.ISCONTACT,
                        @ORGNAME = org.NAME,
                        @JOBPOSITION = job.JOBTITLE,
                        @JOBDEPARTMENT = job.JOBDEPARTMENT,
                        @JOBDIVISION = job.JOBDIVISION
                    from CONSTITUENT org
                    inner join RELATIONSHIP r 
                        on r.RELATIONSHIPCONSTITUENTID = org.ID
                    left outer join dbo.RELATIONSHIPJOBINFO job
                        on r.RELATIONSHIPSETID = job.RELATIONSHIPSETID
                    where
                        r.ID=@RELATIONSHIPID and
                        r.ISCONTACT=1 and
                        org.ISORGANIZATION=1;
                end

                return 0;