USP_ADDRESS_EDITLOAD

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@ADDRESSTYPECODEID	uniqueidentifier	INOUT
@PRIMARY	bit	INOUT
@DONOTMAIL	bit	INOUT
@STARTDATE	UDT_MONTHDAY	INOUT
@ENDDATE	UDT_MONTHDAY	INOUT
@COUNTRYID	uniqueidentifier	INOUT
@STATEID	uniqueidentifier	INOUT
@ADDRESSBLOCK	nvarchar(150)	INOUT
@CITY	nvarchar(50)	INOUT
@POSTCODE	nvarchar(12)	INOUT
@CART	nvarchar(10)	INOUT
@DPC	nvarchar(8)	INOUT
@LOT	nvarchar(5)	INOUT
@OMITFROMVALIDATION	bit	INOUT
@COUNTYCODEID	uniqueidentifier	INOUT
@CONGRESSIONALDISTRICTCODEID	uniqueidentifier	INOUT
@STATEHOUSEDISTRICTCODEID	uniqueidentifier	INOUT
@STATESENATEDISTRICTCODEID	uniqueidentifier	INOUT
@LOCALPRECINCTCODEID	uniqueidentifier	INOUT
@INFOSOURCECODEID	uniqueidentifier	INOUT
@REGIONCODEID	uniqueidentifier	INOUT
@LASTVALIDATIONATTEMPTDATE	datetime	INOUT
@VALIDATIONMESSAGE	nvarchar(200)	INOUT
@CERTIFICATIONDATA	int	INOUT
@NCOALASTSUBMITDATE	datetime	INOUT
@NCOARETURN	nvarchar(150)	INOUT
@NCOAFOOTNOTE	nvarchar(150)	INOUT
@NCOADPVFOOTNOTE	nvarchar(150)	INOUT
@NCOAMOVEDATE	UDT_FUZZYDATE	INOUT
@NCOADMASUPPRESSION	bit	INOUT
@NCOAMAILGRADE	nvarchar(150)	INOUT
@TSLONG	bigint	INOUT
@VALIDATIONCOUNTRIES	xml	INOUT
@ZIPLOOKUPCOUNTRIES	xml	INOUT
@UPDATECONTACTS	bit	INOUT
@HASCONTACTS	bit	INOUT
@DONOTMAILREASONCODEID	uniqueidentifier	INOUT
@ISHOUSEHOLD	bit	INOUT
@ISHOUSEHOLDMEMBER	bit	INOUT
@UPDATEMATCHINGHOUSEHOLDADDRESSES	bit	INOUT
@MATCHINGHOUSEHOLDMEMBERS	xml	INOUT
@HISTORICALSTARTDATE	date	INOUT
@HISTORICALENDDATE	date	INOUT
@DATEADDED	date	INOUT
@INFOSOURCECOMMENTS	nvarchar(256)	INOUT
@ISCONFIDENTIAL	bit	INOUT
@CONSTITUENTDATAREVIEWROLLBACKREASONID	uniqueidentifier	INOUT
@FORCEDPRIMARY	bit	INOUT
@CANEDITPRIMARY	bit	INOUT
@INVALIDFIELDS	nvarchar(256)	INOUT
@ORIGINCODE	tinyint	INOUT

Definition

Copy



create procedure dbo.USP_ADDRESS_EDITLOAD
  (
        @ID uniqueidentifier,
        @DATALOADED bit = 0 output,
        @ADDRESSTYPECODEID uniqueidentifier = null output,
        @PRIMARY bit = null output,
        @DONOTMAIL bit = null output,
        @STARTDATE dbo.UDT_MONTHDAY = null output,
        @ENDDATE dbo.UDT_MONTHDAY = null output,
        @COUNTRYID uniqueidentifier = null output,
        @STATEID uniqueidentifier = null output,
        @ADDRESSBLOCK nvarchar(150) = null output,
        @CITY nvarchar(50) = null output,
        @POSTCODE nvarchar(12) = null output,
        @CART nvarchar(10) = null output,
        @DPC nvarchar(8) = null output,
        @LOT nvarchar(5) = null output,
        @OMITFROMVALIDATION bit = null output,                    
        @COUNTYCODEID uniqueidentifier = null output,
        @CONGRESSIONALDISTRICTCODEID uniqueidentifier = null output,
        @STATEHOUSEDISTRICTCODEID uniqueidentifier = null output,
        @STATESENATEDISTRICTCODEID uniqueidentifier = null output,
        @LOCALPRECINCTCODEID uniqueidentifier = null output,
        @INFOSOURCECODEID uniqueidentifier = null output,
        @REGIONCODEID uniqueidentifier = null output,                    
        @LASTVALIDATIONATTEMPTDATE datetime = null output,
        @VALIDATIONMESSAGE nvarchar(200) = null output,
        @CERTIFICATIONDATA integer = null output,
        @NCOALASTSUBMITDATE datetime = null output,
        @NCOARETURN nvarchar(150) = null output,
        @NCOAFOOTNOTE nvarchar(150) = null output,
        @NCOADPVFOOTNOTE nvarchar(150) = null output,
        @NCOAMOVEDATE UDT_FUZZYDATE = null output,
        @NCOADMASUPPRESSION bit = null output,
        @NCOAMAILGRADE nvarchar(150) = null output,
        @TSLONG bigint = 0 output,
        @VALIDATIONCOUNTRIES xml = null output,
        @ZIPLOOKUPCOUNTRIES xml = null output,
        @UPDATECONTACTS bit = null output,
        @HASCONTACTS bit = null output,
        @DONOTMAILREASONCODEID uniqueidentifier = null output,
        @ISHOUSEHOLD bit = null output,
        @ISHOUSEHOLDMEMBER bit = null output,
        @UPDATEMATCHINGHOUSEHOLDADDRESSES bit = null output,
        @MATCHINGHOUSEHOLDMEMBERS xml = null output,
        @HISTORICALSTARTDATE date = null output,
        @HISTORICALENDDATE date = null output,
        @DATEADDED date = null output,
        @INFOSOURCECOMMENTS nvarchar(256) = null output,
        @ISCONFIDENTIAL bit = 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
    )
    as
        set nocount on;

        exec dbo.USP_ADDRESS_UPDATE_LOAD @ID, @DATALOADED output, @ADDRESSTYPECODEID output, @PRIMARY output, @DONOTMAIL output,
            @STARTDATE output, @ENDDATE output, @COUNTRYID output, @STATEID output, @ADDRESSBLOCK output, @CITY output,
            @POSTCODE output, @CART output, @DPC output, @LOT output, null, null,
            null, @OMITFROMVALIDATION output, @COUNTYCODEID output, @CONGRESSIONALDISTRICTCODEID output,
            @STATEHOUSEDISTRICTCODEID output, @STATESENATEDISTRICTCODEID output, @LOCALPRECINCTCODEID output, @INFOSOURCECODEID output,
            @REGIONCODEID output, @LASTVALIDATIONATTEMPTDATE output, @VALIDATIONMESSAGE output,    @CERTIFICATIONDATA output,
            @NCOALASTSUBMITDATE output,    @NCOARETURN output,    @NCOAFOOTNOTE output, @NCOADPVFOOTNOTE output, @NCOAMOVEDATE output,
            @NCOADMASUPPRESSION output,    @NCOAMAILGRADE output, @TSLONG output, @VALIDATIONCOUNTRIES output,    @ZIPLOOKUPCOUNTRIES output,
            @UPDATECONTACTS output,    @HASCONTACTS output, @DONOTMAILREASONCODEID output, @HISTORICALSTARTDATE output, @HISTORICALENDDATE output,
            @DATEADDED output, @INFOSOURCECOMMENTS output, @ISCONFIDENTIAL output, @ORIGINCODE output

        if exists(select object_id from sys.objects where type = 'U' and name = 'GROUPMEMBER')
        begin
            declare @CURRENTDATE date;
            set @CURRENTDATE = getdate();

            declare @CONSTITUENTID uniqueidentifier;
            select @CONSTITUENTID = CONSTITUENTID from dbo.ADDRESS 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_ADDRESS_MATCHINGHOUSEHOLDRECORDS(@CONSTITUENTID, @COUNTRYID, @STATEID, @ADDRESSBLOCK, @CITY, @POSTCODE, @ADDRESSTYPECODEID)
                for xml raw('ITEM'), type, elements, root('MATCHINGHOUSEHOLDMEMBERS'), binary base64
            );
        end

        return 0;