USP_EXCHANGECONTACTBATCH_ADDEDITADDRESS

Commits Exchange contact batch address changes.
Parameters

Parameter	Parameter Type	Mode
@ADDRESSID	uniqueidentifier	IN
@ADDRESSTYPECODEID	uniqueidentifier	IN
@ADDRESSBLOCK	nvarchar(150)	IN
@CITY	nvarchar(50)	IN
@COUNTRYID	uniqueidentifier	IN
@POSTCODE	nvarchar(12)	IN
@STATEID	uniqueidentifier	IN
@CONSTITUENTID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@DONOTMAIL	bit	IN
@INFOSOURCECODEID	uniqueidentifier	IN
Definition

Copy


            CREATE procedure dbo.USP_EXCHANGECONTACTBATCH_ADDEDITADDRESS
            (
                @ADDRESSID uniqueidentifier = null,
                @ADDRESSTYPECODEID uniqueidentifier = null,
                @ADDRESSBLOCK nvarchar(150) = null,
                @CITY nvarchar(50) = null,
                @COUNTRYID uniqueidentifier = null,
                @POSTCODE nvarchar(12) = null,
                @STATEID uniqueidentifier = null,
                @CONSTITUENTID uniqueidentifier = null,
                @CHANGEAGENTID uniqueidentifier = null,
                @DONOTMAIL bit = 1,
                @INFOSOURCECODEID uniqueidentifier = null
            )
            as            
                set nocount on;

                declare @CHANGEDATE datetime;
                set @CHANGEDATE = getdate();

                if @CHANGEAGENTID is null  
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;                            

                if @ADDRESSID is null
                    begin
                        set @ADDRESSID = newid();

                        declare @ISPRIMARY bit;
                        if exists(select ID from dbo.ADDRESS where CONSTITUENTID = @CONSTITUENTID and ISPRIMARY = 1)
                            begin
                                set @ISPRIMARY = 0;
                            end
                        else
                            begin
                                set @ISPRIMARY = 1;
                            end

                        insert into dbo.ADDRESS
                            (ID, CONSTITUENTID, ADDRESSTYPECODEID, ADDRESSBLOCK, CITY, COUNTRYID, POSTCODE, STATEID, DONOTMAIL, ISPRIMARY, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                        values
                            (@ADDRESSID, @CONSTITUENTID, @ADDRESSTYPECODEID, @ADDRESSBLOCK, @CITY, @COUNTRYID, @POSTCODE, @STATEID, @DONOTMAIL, @ISPRIMARY, @CHANGEDATE, @CHANGEDATE, @CHANGEAGENTID, @CHANGEAGENTID);
                    end
                else
                    begin
                        update
                            dbo.ADDRESS
                        set
                            ADDRESSBLOCK = @ADDRESSBLOCK,
                            CITY = @CITY,
                            COUNTRYID = @COUNTRYID,
                            POSTCODE = @POSTCODE,
                            STATEID = @STATEID,
                            DATECHANGED = @CHANGEDATE,
                            CHANGEDBYID = @CHANGEAGENTID
                        where
                            ADDRESS.ID = @ADDRESSID;
                    end

                --Update Source code

                if exists(select ID from dbo.ADDRESSVALIDATIONUPDATE where ID = @ADDRESSID)
                    update dbo.ADDRESSVALIDATIONUPDATE
                    set INFOSOURCECODEID = @INFOSOURCECODEID
                    where ID = @ADDRESSID and 
                        (INFOSOURCECODEID <> @INFOSOURCECODEID
                        or
                        INFOSOURCECODEID is null and @INFOSOURCECODEID is not null
                        or
                        INFOSOURCECODEID is not null and @INFOSOURCECODEID is null);
                else
                    insert into dbo.ADDRESSVALIDATIONUPDATE
                            (ID, INFOSOURCECODEID, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                        values
                            (@ADDRESSID, @INFOSOURCECODEID, @CHANGEDATE, @CHANGEDATE, @CHANGEAGENTID, @CHANGEAGENTID);