USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTDUPLICATEMATCH_4

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@LOOKUPID	nvarchar(50)	IN
@FIRSTNAME	nvarchar(50)	IN
@MIDDLENAME	nvarchar(50)	IN
@LASTNAME	nvarchar(100)	IN
@SUFFIXCODEID	uniqueidentifier	IN
@TITLECODEID	uniqueidentifier	IN
@MAIDENNAME	nvarchar(100)	IN
@NICKNAME	nvarchar(50)	IN
@GENDERCODE	tinyint	IN
@BIRTHDATE	UDT_FUZZYDATE	IN
@ADDRESSID	uniqueidentifier	IN
@ADDRESSTYPECODEID	uniqueidentifier	IN
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN
@ADDRESS_CITY	nvarchar(50)	IN
@ADDRESS_STATEID	uniqueidentifier	IN
@ADDRESS_POSTCODE	nvarchar(12)	IN
@ADDRESS_COUNTRYID	uniqueidentifier	IN
@PHONEID	uniqueidentifier	IN
@PHONETYPECODEID	uniqueidentifier	IN
@PHONENUMBER	nvarchar(100)	IN
@EMAILID	uniqueidentifier	IN
@EMAILADDRESSTYPECODEID	uniqueidentifier	IN
@EMAILADDRESS	UDT_EMAILADDRESS	IN
@CREATEDON	datetime	IN
@DATECHANGED	datetime	IN
@ADDRESSES	xml	IN
@PHONES	xml	IN
@EMAILS	xml	IN
@SPOUSENAME	nvarchar(154)	IN
@SPOUSELOOKUPID	nvarchar(50)	IN
@SPOUSESTARTDATE	datetime	IN
@SPOUSEENDDATE	datetime	IN
@HOUSEHOLDNAME	nvarchar(154)	IN
@HOUSEHOLDLOOKUPID	nvarchar(50)	IN
@BUSINESSNAME	nvarchar(154)	IN
@BUSINESSLOOKUPID	nvarchar(50)	IN
@CONSTITUENTACTION	tinyint	IN
@ADDRESSACTION	tinyint	IN
@EMAILACTION	tinyint	IN
@PHONEACTION	tinyint	IN
@ISORGANIZATION	bit	IN
@INDUSTRYCODEID	uniqueidentifier	IN
@NUMEMPLOYEES	int	IN
@NUMSUBSIDIARIES	int	IN
@DECEASED	bit	IN
@DECEASEDDATE	UDT_FUZZYDATE	IN
@GIVESANONYMOUSLY	bit	IN
@MARITALSTATUSCODEID	uniqueidentifier	IN
@WEBADDRESS	UDT_WEBADDRESS	IN
@ADDRESSHISTORICALSTARTDATE	date	IN
@ADDRESSHISTORICALENDDATE	date	IN
@ADDRESSDONOTMAIL	bit	IN
@ADDRESSDONOTMAILREASONCODEID	uniqueidentifier	IN
@ADDRESSSTARTDATE	UDT_MONTHDAY	IN
@ADDRESSENDDATE	UDT_MONTHDAY	IN
@ADDRESSDPC	nvarchar(max)	IN
@ADDRESSCART	nvarchar(max)	IN
@ADDRESSLOT	nvarchar(5)	IN
@ADDRESSINFOSOURCECODEID	uniqueidentifier	IN
@ADDRESSINFOSOURCECOMMENTS	nvarchar(256)	IN
@ADDRESSCOUNTYCODEID	uniqueidentifier	IN
@ADDRESSREGIONCODEID	uniqueidentifier	IN
@ADDRESSCONGRESSIONALDISTRICTCODEID	uniqueidentifier	IN
@ADDRESSSTATEHOUSEDISTRICTCODEID	uniqueidentifier	IN
@ADDRESSSTATESENATEDISTRICTCODEID	uniqueidentifier	IN
@ADDRESSLOCALPRECINCTCODEID	uniqueidentifier	IN
@ADDRESSCERTIFICATIONDATA	int	IN
@ADDRESSLASTVALIDATIONATTEMPTDATE	date	IN
@ADDRESSOMITFROMVALIDATION	bit	IN
@ADDRESSVALIDATIONMESSAGE	nvarchar(200)	IN
@PHONEDONOTCALL	bit	IN
@PHONESTARTTIME	UDT_HOURMINUTE	IN
@PHONEENDTIME	UDT_HOURMINUTE	IN
@PHONEINFOSOURCECODEID	uniqueidentifier	IN
@PHONECOUNTRYID	uniqueidentifier	IN
@PHONESTARTDATE	date	IN
@PHONEENDDATE	date	IN
@PHONESEASONALSTARTDATE	UDT_MONTHDAY	IN
@PHONESEASONALENDDATE	UDT_MONTHDAY	IN
@EMAILADDRESSDONOTEMAIL	bit	IN
@EMAILADDRESSINFOSOURCECODEID	uniqueidentifier	IN
@EMAILADDRESSSTARTDATE	date	IN
@EMAILADDRESSENDDATE	date	IN
@CONSTITUENCIES	xml	IN
@GENDERCODEID	uniqueidentifier	IN

Definition

Copy


create procedure dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTDUPLICATEMATCH_4 (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @LOOKUPID nvarchar(50),
    @FIRSTNAME nvarchar(50),
    @MIDDLENAME nvarchar(50),
    @LASTNAME nvarchar(100),
    @SUFFIXCODEID uniqueidentifier,
    @TITLECODEID uniqueidentifier,
    @MAIDENNAME nvarchar(100),
    @NICKNAME nvarchar(50),
    @GENDERCODE tinyint,
    @BIRTHDATE dbo.UDT_FUZZYDATE,
    @ADDRESSID uniqueidentifier,
    @ADDRESSTYPECODEID uniqueidentifier,
    @ADDRESS_ADDRESSBLOCK nvarchar(150),
    @ADDRESS_CITY nvarchar(50),
    @ADDRESS_STATEID uniqueidentifier,
    @ADDRESS_POSTCODE nvarchar(12),
    @ADDRESS_COUNTRYID uniqueidentifier,
    @PHONEID uniqueidentifier,
    @PHONETYPECODEID uniqueidentifier,
    @PHONENUMBER nvarchar(100),
    @EMAILID uniqueidentifier,
    @EMAILADDRESSTYPECODEID uniqueidentifier,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS,
    @CREATEDON datetime,
    @DATECHANGED datetime,
    @ADDRESSES xml,
    @PHONES xml,
    @EMAILS xml,
    @SPOUSENAME nvarchar(154),
    @SPOUSELOOKUPID nvarchar(50),
    @SPOUSESTARTDATE datetime,
    @SPOUSEENDDATE datetime,
    @HOUSEHOLDNAME nvarchar(154),
    @HOUSEHOLDLOOKUPID nvarchar(50),
    @BUSINESSNAME nvarchar(154),
    @BUSINESSLOOKUPID nvarchar(50),
    @CONSTITUENTACTION tinyint,
    @ADDRESSACTION tinyint,
    @EMAILACTION tinyint,
    @PHONEACTION tinyint,
    @ISORGANIZATION bit,
    @INDUSTRYCODEID uniqueidentifier,
    @NUMEMPLOYEES int,
    @NUMSUBSIDIARIES int,
    @DECEASED bit,
    @DECEASEDDATE dbo.UDT_FUZZYDATE,
    @GIVESANONYMOUSLY bit,
    @MARITALSTATUSCODEID uniqueidentifier,
    @WEBADDRESS dbo.UDT_WEBADDRESS,
    @ADDRESSHISTORICALSTARTDATE date,
    @ADDRESSHISTORICALENDDATE date,
    @ADDRESSDONOTMAIL bit,
    @ADDRESSDONOTMAILREASONCODEID uniqueidentifier,
    @ADDRESSSTARTDATE dbo.UDT_MONTHDAY,
    @ADDRESSENDDATE dbo.UDT_MONTHDAY,
    @ADDRESSDPC nvarchar(max),
    @ADDRESSCART nvarchar(max),
    @ADDRESSLOT nvarchar(5),
    @ADDRESSINFOSOURCECODEID uniqueidentifier,
    @ADDRESSINFOSOURCECOMMENTS nvarchar(256),
    @ADDRESSCOUNTYCODEID uniqueidentifier,
    @ADDRESSREGIONCODEID uniqueidentifier,
    @ADDRESSCONGRESSIONALDISTRICTCODEID uniqueidentifier,
    @ADDRESSSTATEHOUSEDISTRICTCODEID uniqueidentifier,
    @ADDRESSSTATESENATEDISTRICTCODEID uniqueidentifier,
    @ADDRESSLOCALPRECINCTCODEID uniqueidentifier,
    @ADDRESSCERTIFICATIONDATA int,
    @ADDRESSLASTVALIDATIONATTEMPTDATE date,
    @ADDRESSOMITFROMVALIDATION bit,
    @ADDRESSVALIDATIONMESSAGE nvarchar(200),
    @PHONEDONOTCALL bit,
    @PHONESTARTTIME dbo.UDT_HOURMINUTE,
    @PHONEENDTIME dbo.UDT_HOURMINUTE,
    @PHONEINFOSOURCECODEID uniqueidentifier,
    @PHONECOUNTRYID uniqueidentifier,
    @PHONESTARTDATE date,
    @PHONEENDDATE date,
    @PHONESEASONALSTARTDATE dbo.UDT_MONTHDAY,
    @PHONESEASONALENDDATE dbo.UDT_MONTHDAY,
    @EMAILADDRESSDONOTEMAIL bit,
    @EMAILADDRESSINFOSOURCECODEID uniqueidentifier,
    @EMAILADDRESSSTARTDATE date,
    @EMAILADDRESSENDDATE date,
    @CONSTITUENCIES xml,
  @GENDERCODEID uniqueidentifier
    )
as
set nocount on;

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

declare @CURRENTDATE datetime

set @CURRENTDATE = getdate()

if @CONSTITUENTACTION is null
    set @CONSTITUENTACTION = 0;

if @ADDRESSACTION is null
    set @ADDRESSACTION = 0;

if @EMAILACTION is null
    set @EMAILACTION = 0;

if @PHONEACTION is null
    set @PHONEACTION = 0;

if @FIRSTNAME is null
    set @FIRSTNAME = '';

if @MIDDLENAME is null
    set @MIDDLENAME = '';

begin try
    ------------GET CONSTITUENT UPDATE RULES --------------
    declare @NEWADDRESSENDDATECODE tinyint
    declare @CREATEHISTORICALNAMECODE tinyint
    declare @NEWPHONEENDDATECODE tinyint
    declare @NEWEMAILENDDATECODE tinyint

    select @NEWADDRESSENDDATECODE = NEWADDRESSENDDATECODE,
        @CREATEHISTORICALNAMECODE = CREATEHISTORICALNAMECODE,
        @NEWPHONEENDDATECODE = NEWPHONEENDDATECODE,
        @NEWEMAILENDDATECODE = NEWEMAILENDDATECODE
    from dbo.CONSTITUENTBUSINESSRULESSETTINGS

    declare @NEWID uniqueidentifier;

    ------------CONSTITUENT HANDLING------------------------
    if @CONSTITUENTACTION = 1 --Add constituent as alias (same SP can be used for both individuals and organizations)
    begin
        declare @IGNOREDUPLICATE bit = 1;--if we are here we want to ignore the duplicate error message and continue on.

        exec dbo.USP_ADD_INDIVIDUAL_ALIAS @CHANGEAGENTID = @CHANGEAGENTID,
            @CONSTITUENTID = @ID,
            @KEYNAME = @LASTNAME,
            @FIRSTNAME = @FIRSTNAME,
            @MIDDLENAME = @MIDDLENAME,
            @TITLECODEID = @TITLECODEID,
            @SUFFIXCODEID = @SUFFIXCODEID,
            @IGNOREDUPLICATE = @IGNOREDUPLICATE
    end;
    else
        if @CONSTITUENTACTION = 2 --Constituent fields update 
        begin
            declare @PICTURE varbinary(max),
                @PICTURETHUMBNAIL varbinary(max),
                @PICTURECHANGED bit,
                @TITLE2CODEID uniqueidentifier,
                @SUFFIX2CODEID uniqueidentifier,
                @ORGISPRIMARY bit,
                @PARENTCORPID uniqueidentifier;

            -- Save old name as a historical name if the rule calls for it.
            if @CREATEHISTORICALNAMECODE = 1
                exec dbo.USP_CREATE_HISTORICALNAME @ID,
                    @LASTNAME,
                    @FIRSTNAME,
                    @MIDDLENAME,
                    @TITLECODEID,
                    @SUFFIXCODEID,
                    @CHANGEAGENTID;

            if @ISORGANIZATION = 1 -- Update organization
            begin
                -- load organization to updated
                exec dbo.USP_ORGANIZATION_EDITLOAD @ID,
                    @PARENTCORPID = @PARENTCORPID output,
                    @PICTURE = @PICTURE output,
                    @PICTURETHUMBNAIL = @PICTURETHUMBNAIL output,
                    @PICTURECHANGED = @PICTURECHANGED output,
                    @ISPRIMARY = @ORGISPRIMARY output;

                -- save organization fields
                exec dbo.USP_ORGANIZATION_EDITSAVE @ID,
                    @CHANGEAGENTID,
                    @LASTNAME,
                    @INDUSTRYCODEID,
                    @NUMEMPLOYEES,
                    @NUMSUBSIDIARIES,
                    @PARENTCORPID,
                    @PICTURE,
                    @PICTURETHUMBNAIL,
                    @PICTURECHANGED,
                    @WEBADDRESS,
                    @ORGISPRIMARY;
            end;
            else -- Update individual
            begin
                -- load individual to update
                exec dbo.USP_INDIVIDUAL_EDITLOAD @ID,
                    @PICTURE = @PICTURE output,
                    @PICTURETHUMBNAIL = @PICTURETHUMBNAIL output,
                    @PICTURECHANGED = @PICTURECHANGED output,
                    @TITLE2CODEID = @TITLE2CODEID output,
                    @SUFFIX2CODEID = @SUFFIX2CODEID output;

                -- save individual fields
                exec dbo.USP_INDIVIDUAL_EDITSAVE_2 @ID,
                    @CHANGEAGENTID,
                    @LASTNAME,
                    @FIRSTNAME,
                    @MIDDLENAME,
                    @MAIDENNAME,
                    @NICKNAME,
                    @TITLECODEID,
                    @SUFFIXCODEID,
                    @GENDERCODE,
                    @BIRTHDATE,
                    @GIVESANONYMOUSLY,
                    @PICTURE,
                    @PICTURETHUMBNAIL,
                    @PICTURECHANGED,
                    @WEBADDRESS,
                    @MARITALSTATUSCODEID,
                    @TITLE2CODEID,
                    @SUFFIX2CODEID,
                    @DECEASED,
                    @DECEASEDDATE,
                    @GENDERCODEID;
            end;
        end;

    --------------ADDRESSES----------------------------------
    if @ADDRESSACTION = 1 -- Add secondary address
    begin
        declare @ADDRESS_ISPRIMARY bit

        select @ADDRESSTYPECODEID = T.c.value('(ADDRESSTYPECODEID)[1]', 'uniqueidentifier'),
            @ADDRESS_COUNTRYID = T.c.value('(COUNTRYID)[1]', 'uniqueidentifier'),
            @ADDRESS_STATEID = T.c.value('(STATEID)[1]', 'uniqueidentifier'),
            @ADDRESS_ADDRESSBLOCK = T.c.value('(ADDRESSBLOCK)[1]', 'nvarchar(150)'),
            @ADDRESS_CITY = T.c.value('(CITY)[1]', 'nvarchar(50)'),
            @ADDRESS_POSTCODE = T.c.value('(POSTCODE)[1]', 'nvarchar(12)'),
            @ADDRESS_ISPRIMARY = T.c.value('(ISPRIMARY)[1]', 'bit')
        from @ADDRESSES.nodes('/ADDRESSES/ITEM') T(c)
        where T.c.value('(ADDRESSID)[1]', 'uniqueidentifier') is null;

        exec dbo.USP_ADDRESS_ADD @ID = @NEWID output,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @CONSTITUENTID = @ID,
            @ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
            @COUNTRYID = @ADDRESS_COUNTRYID,
            @STATEID = @ADDRESS_STATEID,
            @ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK,
            @CITY = @ADDRESS_CITY,
            @POSTCODE = @ADDRESS_POSTCODE,
            @PRIMARY = @ADDRESS_ISPRIMARY;

        -- end date any existing addresses if the rule says to and the addresstype matches some other address type
        if @NEWADDRESSENDDATECODE = 0 --then end date any existing addresses with same type code
        begin
            update dbo.ADDRESS
            set DONOTMAIL = 1,
                HISTORICALENDDATE = isnull(HISTORICALENDDATE, @CURRENTDATE)
            where CONSTITUENTID = @ID and (HISTORICALENDDATE is null or DONOTMAIL = 0) and ADDRESSTYPECODEID = @ADDRESSTYPECODEID and ID <> @NEWID and ISPRIMARY = 0
        end
    end
    else
        if @ADDRESSACTION = 2 -- Update current action              
        begin
            -- invoke address load 
            declare @PRIMARY bit,
                @UPDATECONTACTS bit,
                @UPDATEMATCHINGHOUSEHOLDADDRESSES bit,
                @ISCONFIDENTIAL bit,
                @CONSTITUENTDATAREVIEWROLLBACKREASONID uniqueidentifier;

            exec dbo.USP_ADDRESS_EDITLOAD @ADDRESSID,
                @PRIMARY = @PRIMARY output,
                @UPDATECONTACTS = @UPDATECONTACTS output,
                @UPDATEMATCHINGHOUSEHOLDADDRESSES = @UPDATEMATCHINGHOUSEHOLDADDRESSES output,
                @ISCONFIDENTIAL = @ISCONFIDENTIAL output,
                @CONSTITUENTDATAREVIEWROLLBACKREASONID = @CONSTITUENTDATAREVIEWROLLBACKREASONID output;

            -- update current address
            exec dbo.USP_ADDRESS_EDITSAVE @ADDRESSID,
                @CHANGEAGENTID,
                @ADDRESSTYPECODEID,
                @PRIMARY,
                @ADDRESSDONOTMAIL,
                @ADDRESSSTARTDATE,
                @ADDRESSENDDATE,
                @ADDRESS_COUNTRYID,
                @ADDRESS_STATEID,
                @ADDRESS_ADDRESSBLOCK,
                @ADDRESS_CITY,
                @ADDRESS_POSTCODE,
                @ADDRESSCART,
                @ADDRESSDPC,
                @ADDRESSLOT,
                @ADDRESSOMITFROMVALIDATION,
                @ADDRESSCOUNTYCODEID,
                @ADDRESSCONGRESSIONALDISTRICTCODEID,
                @ADDRESSSTATEHOUSEDISTRICTCODEID,
                @ADDRESSSTATESENATEDISTRICTCODEID,
                @ADDRESSLOCALPRECINCTCODEID,
                @ADDRESSINFOSOURCECODEID,
                @ADDRESSREGIONCODEID,
                @ADDRESSLASTVALIDATIONATTEMPTDATE,
                @ADDRESSVALIDATIONMESSAGE,
                @ADDRESSCERTIFICATIONDATA,
                @UPDATECONTACTS,
                @ADDRESSDONOTMAILREASONCODEID,
                @UPDATEMATCHINGHOUSEHOLDADDRESSES,
                @ADDRESSHISTORICALSTARTDATE,
                @ADDRESSHISTORICALENDDATE,
                @ADDRESSINFOSOURCECOMMENTS,
                @ISCONFIDENTIAL,
                @CONSTITUENTDATAREVIEWROLLBACKREASONID;
        end;

    ----------------EMAILS----------------------------
    if @EMAILACTION = 1 -- add as secondary email
    begin
        declare @EMAILPRIMARY bit,
            @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS bit,
            @EMAILINFOSOURCECOMMENTS nvarchar(256);

        select @EMAILADDRESSTYPECODEID = T.c.value('(EMAILADDRESSTYPECODEID)[1]', 'uniqueidentifier'),
            @EMAILADDRESS = T.c.value('(EMAILADDRESS)[1]', 'dbo.UDT_EMAILADDRESS'),
            @EMAILADDRESSSTARTDATE = T.c.value('(STARTDATE)[1]', 'datetime'),
            @EMAILPRIMARY = T.c.value('(ISPRIMARY)[1]', 'bit')
        from @EMAILS.nodes('/EMAILS/ITEM') T(c)
        where T.c.value('(EMAILADDRESSID)[1]', 'uniqueidentifier') is null;

        -- add email address
        exec dbo.USP_EMAILADDRESS_CREATE @ID = @NEWID output,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @CURRENTDATE = @CURRENTDATE,
            @CONSTITUENTID = @ID,
            @EMAILADDRESSTYPECODEID = @EMAILADDRESSTYPECODEID,
            @EMAILADDRESS = @EMAILADDRESS,
            @PRIMARY = @EMAILPRIMARY,
            @STARTDATE = @EMAILADDRESSSTARTDATE;

        if @NEWEMAILENDDATECODE = 0 --then end date any existing email with same type code
        begin
            update dbo.EMAILADDRESS
            set DONOTEMAIL = 1,
                ENDDATE = isnull(ENDDATE, @CURRENTDATE)
            where CONSTITUENTID = @ID and (ENDDATE is null or DONOTEMAIL = 0) and EMAILADDRESSTYPECODEID = @EMAILADDRESSTYPECODEID and ID <> @NEWID and ISPRIMARY = 0
        end
    end
    else
        if @EMAILACTION = 2 -- update current email
        begin
            -- invoke email load                             
            exec dbo.USP_EMAILADDRESS_EDITLOAD @EMAILID,
                @PRIMARY = @EMAILPRIMARY output,
                @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS = @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS output,
                @INFOSOURCECOMMENTS = @EMAILINFOSOURCECOMMENTS output;

            -- update email address             
            exec dbo.USP_EMAILADDRESS_UPDATE @EMAILID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @EMAILADDRESSTYPECODEID,
                @EMAILADDRESS,
                @EMAILPRIMARY,
                @EMAILADDRESSDONOTEMAIL,
                @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS,
                @EMAILADDRESSINFOSOURCECODEID,
                @EMAILINFOSOURCECOMMENTS,
                @EMAILADDRESSSTARTDATE,
                @EMAILADDRESSENDDATE;
        end;

    ----------------PHONES----------------------------
    if @PHONEACTION = 1 -- add as secondary phone
    begin
        declare @PHONEISPRIMARY bit,
            @UPDATEMATCHINGHOUSEHOLDPHONE bit,
            @PHONEDONOTCALLREASONCODEID uniqueidentifier,
            @PHONEINFOSOURCECOMMENTS nvarchar(256),
            @PHONEISCONFIDENTIAL bit;

        select @PHONETYPECODEID = T.c.value('(PHONETYPECODEID)[1]', 'uniqueidentifier'),
            @PHONENUMBER = T.c.value('(PHONENUMBER)[1]', 'nvarchar(100)'),
            @PHONEISPRIMARY = T.c.value('(ISPRIMARY)[1]', 'bit')
        from @PHONES.nodes('/PHONES/ITEM') T(c)
        where T.c.value('(PHONEID)[1]', 'uniqueidentifier') is null;

        exec dbo.USP_PHONE_CREATE @ID = @NEWID output,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @CURRENTDATE = @CURRENTDATE,
            @CONSTITUENTID = @ID,
            @PHONETYPECODEID = @PHONETYPECODEID,
            @NUMBER = @PHONENUMBER,
            @PRIMARY = @PHONEISPRIMARY,
            @COUNTRYID = @ADDRESS_COUNTRYID;

        if @NEWPHONEENDDATECODE = 0 --then end date any existing PHONE with same type code
        begin
            update dbo.PHONE
            set DONOTCALL = 1,
                ENDDATE = isnull(ENDDATE, @CURRENTDATE)
            where CONSTITUENTID = @ID and (ENDDATE is null or DONOTCALL = 0) and PHONETYPECODEID = @PHONETYPECODEID and ID <> @NEWID and ISPRIMARY = 0
        end
    end
    else
        if @PHONEACTION = 2 -- update current phone   
        begin
            -- phone data load     
            exec dbo.USP_PHONE_EDITLOAD @PHONEID,
                @PRIMARY = @PHONEISPRIMARY output,
                @UPDATEMATCHINGHOUSEHOLDPHONE = @UPDATEMATCHINGHOUSEHOLDPHONE output,
                @INFOSOURCECOMMENTS = @PHONEINFOSOURCECOMMENTS output,
                @DONOTCALLREASONCODEID = @PHONEDONOTCALLREASONCODEID output,
                @ISCONFIDENTIAL = @PHONEISCONFIDENTIAL output;

            -- update phone  
            exec dbo.USP_PHONE_UPDATE @PHONEID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @PHONETYPECODEID,
                @PHONENUMBER,
                @PHONEISPRIMARY,
                @PHONEDONOTCALL,
                @UPDATEMATCHINGHOUSEHOLDPHONE,
                @PHONESTARTTIME,
                @PHONEENDTIME,
                @PHONEINFOSOURCECODEID,
                @PHONEINFOSOURCECOMMENTS,
                @PHONECOUNTRYID,
                @PHONESTARTDATE,
                @PHONEENDDATE,
                @PHONEDONOTCALLREASONCODEID,
                @PHONEISCONFIDENTIAL,
                @PHONESEASONALSTARTDATE,
                @PHONESEASONALENDDATE;
        end;

        ----------------CONSTITUENCIES----------------------------
        -- Constituencies duplicate resolution support is only being added to CUB for now
        -- CUB uses ConstituentUpdateBatchDuplicateMatch.Edit to save the constituencies data
        -- so we don't need to add it here
end try

begin catch
    exec dbo.USP_RAISE_ERROR

    return 1
end catch

return 0;