USP_CONSTITUENTDATAREVIEW_EMAILADDRESS_LOADAUDITDATA

Loads an email address's data from an audit record.

Parameters

Parameter	Parameter Type	Mode
@AUDITID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@EMAILADDRESSID	uniqueidentifier	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT
@EMAILADDRESSTYPECODEID	uniqueidentifier	INOUT
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT
@PRIMARY	bit	INOUT
@DONOTEMAIL	bit	INOUT
@SPOUSENAME	nvarchar(154)	INOUT
@SPOUSEHASMATCHINGEMAILADDRESS	bit	INOUT
@UPDATEMATCHINGSPOUSEEMAILADDRESS	bit	INOUT
@UPDATEMATCHINGHOUSEHOLDEMAILADDRESS	bit	INOUT
@INFOSOURCECODEID	uniqueidentifier	INOUT
@INFOSOURCECOMMENTS	nvarchar(256)	INOUT
@ORIGINCODE	tinyint	INOUT
@STARTDATE	date	INOUT
@ENDDATE	date	INOUT
@EMAILISCONFIDENTIAL	bit	INOUT

Definition

Copy



CREATE procedure dbo.USP_CONSTITUENTDATAREVIEW_EMAILADDRESS_LOADAUDITDATA (
    @AUDITID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @EMAILADDRESSID uniqueidentifier = null output,
    @CONSTITUENTID uniqueidentifier = null output,
    @EMAILADDRESSTYPECODEID uniqueidentifier = null output,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
  @PRIMARY bit = null output,
    @DONOTEMAIL bit = null output,
    @SPOUSENAME nvarchar(154) = null output,
    @SPOUSEHASMATCHINGEMAILADDRESS bit = null output,
    @UPDATEMATCHINGSPOUSEEMAILADDRESS bit = null output,
    @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS bit = null output,
    @INFOSOURCECODEID uniqueidentifier = null output,
    @INFOSOURCECOMMENTS nvarchar(256) = null output,
    @ORIGINCODE tinyint = null output,
  @STARTDATE date = null output,
  @ENDDATE date = null output,
    @EMAILISCONFIDENTIAL bit = null output
)
as
begin
    set nocount on;

    declare @SPOUSEID uniqueidentifier;

    set @DATALOADED = 0;
    set @SPOUSEHASMATCHINGEMAILADDRESS = 0;
    set @UPDATEMATCHINGSPOUSEEMAILADDRESS = 0;

    if exists(select object_id from sys.objects where type = 'U' and name = 'RELATIONSHIP') begin
        select
            @DATALOADED = 1,
            @EMAILADDRESSID = EMAILADDRESSAUDIT.AUDITRECORDID,
            @CONSTITUENTID = EMAILADDRESSAUDIT.CONSTITUENTID,
            @EMAILADDRESSTYPECODEID = EMAILADDRESSAUDIT.EMAILADDRESSTYPECODEID,
            @EMAILADDRESS = EMAILADDRESSAUDIT.EMAILADDRESS,
            @PRIMARY = EMAILADDRESSAUDIT.ISPRIMARY,
            @DONOTEMAIL = EMAILADDRESSAUDIT.DONOTEMAIL,
            @INFOSOURCECODEID = EMAILADDRESSAUDIT.INFOSOURCECODEID,
            @INFOSOURCECOMMENTS = EMAILADDRESSAUDIT.INFOSOURCECOMMENTS,
            @ORIGINCODE = EMAILADDRESSAUDIT.ORIGINCODE,
            @SPOUSEID = SPOUSE.ID,
            @SPOUSENAME = SPOUSE.NAME,
      @STARTDATE = EMAILADDRESSAUDIT.STARTDATE,
      @ENDDATE = EMAILADDRESSAUDIT.ENDDATE,
            @EMAILISCONFIDENTIAL = EMAILADDRESSAUDIT.ISCONFIDENTIAL
        from 
            dbo.EMAILADDRESSAUDIT
            left join dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = EMAILADDRESSAUDIT.CONSTITUENTID and RELATIONSHIP.ISSPOUSE = 1
            left join dbo.CONSTITUENT as SPOUSE on SPOUSE.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
        where 
            EMAILADDRESSAUDIT.AUDITID = @AUDITID;

        if exists (
            select EMAILADDRESS.ID
            from dbo.EMAILADDRESS
            where
                EMAILADDRESS.CONSTITUENTID = @SPOUSEID and
                EMAILADDRESS.EMAILADDRESS = @EMAILADDRESS
            ) begin
                set @SPOUSEHASMATCHINGEMAILADDRESS = 1;    
                set @UPDATEMATCHINGSPOUSEEMAILADDRESS = 1;
        end
    end
    else begin
        select
            @DATALOADED = 1,
            @EMAILADDRESSID = EMAILADDRESSAUDIT.AUDITRECORDID,
            @CONSTITUENTID = EMAILADDRESSAUDIT.CONSTITUENTID,
            @EMAILADDRESSTYPECODEID = EMAILADDRESSAUDIT.EMAILADDRESSTYPECODEID,
            @EMAILADDRESS = EMAILADDRESSAUDIT.EMAILADDRESS,
            @PRIMARY = EMAILADDRESSAUDIT.ISPRIMARY,
            @DONOTEMAIL = EMAILADDRESSAUDIT.DONOTEMAIL,
            @INFOSOURCECODEID = EMAILADDRESSAUDIT.INFOSOURCECODEID,
            @INFOSOURCECOMMENTS = EMAILADDRESSAUDIT.INFOSOURCECOMMENTS,
            @ORIGINCODE = EMAILADDRESSAUDIT.ORIGINCODE,
      @STARTDATE = EMAILADDRESSAUDIT.STARTDATE,
      @ENDDATE = EMAILADDRESSAUDIT.ENDDATE,
            @EMAILISCONFIDENTIAL = EMAILADDRESSAUDIT.ISCONFIDENTIAL
        from
            dbo.EMAILADDRESSAUDIT
        where
            EMAILADDRESSAUDIT.AUDITID = @AUDITID;
    end

    return 0;
end