USP_DATAFORMTEMPLATE_VIEW_EMAILADDRESS

The load procedure used by the view dataform template "Email Address View 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.
@EMAILADDRESSTYPECODE	nvarchar(100)	INOUT	Type
@EMAILADDRESS	nvarchar(100)	INOUT	Email address
@PRIMARY	bit	INOUT	Primary
@DONOTEMAIL	bit	INOUT	Do not email
@INFOSOURCECODE	nvarchar(100)	INOUT	Information source
@INFOSOURCECOMMENTS	nvarchar(256)	INOUT	Comments
@ISCONTACT	bit	INOUT	ISCONTACT
@ORGNAME	nvarchar(154)	INOUT	Organization
@JOBPOSITION	nvarchar(100)	INOUT	Position
@JOBDEPARTMENT	nvarchar(100)	INOUT	Department
@JOBDIVISION	nvarchar(100)	INOUT	Division
@ORIGINCODE	tinyint	INOUT	Origin
@STARTDATE	date	INOUT	Start date
@ENDDATE	date	INOUT	End date
@DATEADDED	date	INOUT	Date added
@BOUNCEDEMAIL	bit	INOUT	Bounced
@ISCONFIDENTIAL	bit	INOUT
@DONOTEMAILREASONCODE	nvarchar(100)	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EMAILADDRESS
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @EMAILADDRESSTYPECODE nvarchar(100) = null output,
    @EMAILADDRESS nvarchar(100) = null output,
    @PRIMARY bit = null output,
    @DONOTEMAIL bit = null output,
    @INFOSOURCECODE nvarchar(100) = null output,
    @INFOSOURCECOMMENTS nvarchar(256) = null output,
    @ISCONTACT bit = null output,
    @ORGNAME nvarchar(154) = null output,
    @JOBPOSITION nvarchar(100) = null output,
    @JOBDEPARTMENT nvarchar(100) = null output,
    @JOBDIVISION nvarchar(100) = null output,
    @ORIGINCODE tinyint = null output,
    @STARTDATE date = null output,
    @ENDDATE date = null output,
    @DATEADDED date = null output,
    @BOUNCEDEMAIL bit = null output,
    @ISCONFIDENTIAL bit = null output,
    @DONOTEMAILREASONCODE nvarchar(100) = null output
)
as
    set nocount on;

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

    declare @RELATIONSHIPID uniqueidentifier;

    select 
        @DATALOADED = 1,
        @EMAILADDRESSTYPECODE = (select DESCRIPTION from dbo.EMAILADDRESSTYPECODE where ID = EMAILADDRESSTYPECODEID),
        @EMAILADDRESS = EMAILADDRESS,
        @PRIMARY = ISPRIMARY,
        @DONOTEMAIL = DONOTEMAIL,
        @BOUNCEDEMAIL = case when EXISTS(SELECT ID FROM dbo.EMAILINVALIDRECIPIENT WHERE EMAILINVALIDRECIPIENT.ADDRESS = EMAILADDRESS.EMAILADDRESS AND EMAILINVALIDRECIPIENT.ISBLACKLISTED = 1) then 1 else 0 end,
        @INFOSOURCECODE = (select DESCRIPTION from dbo.INFOSOURCECODE where ID = INFOSOURCECODEID),
        @INFOSOURCECOMMENTS = INFOSOURCECOMMENTS,
        @RELATIONSHIPID = RELATIONSHIPID,
        @ORIGINCODE = ORIGINCODE,
        @STARTDATE = STARTDATE,
        @ENDDATE = ENDDATE,
        @DATEADDED = DATEADDED,
        @ISCONFIDENTIAL = ISCONFIDENTIAL,
        @DONOTEMAILREASONCODE = '(' + (select DESCRIPTION from dbo.DONOTEMAILREASONCODE where ID = DONOTEMAILREASONCODEID) + ')'
    from dbo.EMAILADDRESS
    where ID = @ID;

    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;