USP_EMAILADDRESS_EDITLOAD

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@EMAILADDRESSTYPECODEID	uniqueidentifier	INOUT
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT
@PRIMARY	bit	INOUT
@DONOTEMAIL	bit	INOUT
@SPOUSENAME	nvarchar(154)	INOUT
@SPOUSEHASMATCHINGEMAILADDRESS	bit	INOUT
@UPDATEMATCHINGSPOUSEEMAILADDRESS	bit	INOUT
@ISHOUSEHOLD	bit	INOUT
@ISHOUSEHOLDMEMBER	bit	INOUT
@UPDATEMATCHINGHOUSEHOLDEMAILADDRESS	bit	INOUT
@MATCHINGHOUSEHOLDMEMBERS	xml	INOUT
@TSLONG	bigint	INOUT
@INFOSOURCECODEID	uniqueidentifier	INOUT
@INFOSOURCECOMMENTS	nvarchar(256)	INOUT
@CONSTITUENTDATAREVIEWROLLBACKREASONID	uniqueidentifier	INOUT
@FORCEDPRIMARY	bit	INOUT
@CANEDITPRIMARY	bit	INOUT
@INVALIDFIELDS	nvarchar(256)	INOUT
@ORIGINCODE	tinyint	INOUT
@STARTDATE	date	INOUT
@ENDDATE	date	INOUT
@INVALIDEMAIL	bit	INOUT
@EMAILBOUNCEDDATE	date	INOUT
@EMAILISCONFIDENTIAL	bit	INOUT
@DONOTEMAILREASONCODEID	uniqueidentifier	INOUT

Definition

Copy



CREATE procedure dbo.USP_EMAILADDRESS_EDITLOAD
   (
        @ID uniqueidentifier,
        @DATALOADED bit = 0 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,
        @ISHOUSEHOLD bit = null output,
        @ISHOUSEHOLDMEMBER bit = null output,
        @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS bit = null output,
        @MATCHINGHOUSEHOLDMEMBERS xml = null output,
        @TSLONG bigint = 0 output,
        @INFOSOURCECODEID uniqueidentifier = null output,
        @INFOSOURCECOMMENTS nvarchar(256) = null output,
        @CONSTITUENTDATAREVIEWROLLBACKREASONID uniqueidentifier = null output,    -- used by constituent data review

        @FORCEDPRIMARY bit = null output,            -- used by constituent data review

        @CANEDITPRIMARY bit = null output,            -- used by constituent data review

        @INVALIDFIELDS nvarchar(256) = null output,    -- used by constituent data review

        @ORIGINCODE tinyint = null output,
        @STARTDATE date = null output,
        @ENDDATE date = null output,
        @INVALIDEMAIL bit = null output,
        @EMAILBOUNCEDDATE date = null output,
        @EMAILISCONFIDENTIAL bit = null output,
        @DONOTEMAILREASONCODEID uniqueidentifier = null output
    )
    as
        set nocount on;

        exec dbo.USP_EMAILADDRESS_UPDATE_LOAD 
            @ID, 
            @DATALOADED output,
            @TSLONG output, 
            @EMAILADDRESSTYPECODEID output,
            @EMAILADDRESS output, 
            @PRIMARY output, 
            @DONOTEMAIL output, 
            @SPOUSENAME output, 
            @SPOUSEHASMATCHINGEMAILADDRESS output,
            --@UPDATEMATCHINGSPOUSEEMAILADDRESS output, --Deprecated

            @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS = @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS output, 
            @INFOSOURCECODEID = @INFOSOURCECODEID output, 
            @INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS output,
            @ORIGINCODE = @ORIGINCODE output,
            @STARTDATE  = @STARTDATE output,
            @ENDDATE  = @ENDDATE output,
            @INVALIDEMAIL = @INVALIDEMAIL output,
            @EMAILBOUNCEDDATE = @EMAILBOUNCEDDATE output, 
            @EMAILISCONFIDENTIAL = @EMAILISCONFIDENTIAL output,
      @DONOTEMAILREASONCODEID = @DONOTEMAILREASONCODEID output;

        declare @CURRENTDATE date;
        set @CURRENTDATE = getdate();

        declare @CONSTITUENTID uniqueidentifier;
        select @CONSTITUENTID = CONSTITUENTID from dbo.EMAILADDRESS where ID = @ID;

        set @ISHOUSEHOLD = dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID);
        set @ISHOUSEHOLDMEMBER = case when exists (
            select 1 
            from dbo.GROUPMEMBER GM 
            left outer join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
            left outer join dbo.GROUPDATA GD on GD.ID = GM.GROUPID
            where GM.MEMBERID = @CONSTITUENTID 
                and GD.GROUPTYPECODE = 0
                and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
                    or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                    or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))
        ) then 1 else 0 end;                                

        set @MATCHINGHOUSEHOLDMEMBERS = (
            select
                CONSTITUENTID,
                NAME,
                RELATIONSHIPTOPRIMARY
            from
                dbo.UFN_EMAILADDRESS_MATCHINGHOUSEHOLDRECORDS(@CONSTITUENTID, @EMAILADDRESS, @EMAILADDRESSTYPECODEID)
            for xml raw('ITEM'), type, elements, root('MATCHINGHOUSEHOLDMEMBERS'), binary base64
        );

        return 0;