USP_PHONE_UPDATE

Updates an existing phone.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CURRENTDATE	datetime	IN
@PHONETYPECODEID	uniqueidentifier	IN
@NUMBER	nvarchar(100)	IN
@PRIMARY	bit	IN
@DONOTCALL	bit	IN
@UPDATEMATCHINGHOUSEHOLDPHONE	bit	IN
@STARTTIME	UDT_HOURMINUTE	IN
@ENDTIME	UDT_HOURMINUTE	IN
@INFOSOURCECODEID	uniqueidentifier	IN
@INFOSOURCECOMMENTS	nvarchar(256)	IN
@COUNTRYID	uniqueidentifier	IN
@STARTDATE	date	IN
@ENDDATE	date	IN
@DONOTCALLREASONCODEID	uniqueidentifier	IN
@ISCONFIDENTIAL	bit	IN
@SEASONALSTARTDATE	UDT_MONTHDAY	IN
@SEASONALENDDATE	UDT_MONTHDAY	IN
@DONOTTEXT	bit	IN
Definition

Copy


            CREATE procedure dbo.USP_PHONE_UPDATE
            (
                @ID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTDATE datetime = null,
                @PHONETYPECODEID uniqueidentifier,
                @NUMBER nvarchar(100),
                @PRIMARY bit,
                @DONOTCALL bit,
                @UPDATEMATCHINGHOUSEHOLDPHONE bit,
                @STARTTIME dbo.UDT_HOURMINUTE,
                @ENDTIME dbo.UDT_HOURMINUTE,
                @INFOSOURCECODEID uniqueidentifier,
                @INFOSOURCECOMMENTS nvarchar(256),
                @COUNTRYID uniqueidentifier,
                @STARTDATE date,
                @ENDDATE date,
                @DONOTCALLREASONCODEID uniqueidentifier,
                @ISCONFIDENTIAL bit,
                @SEASONALSTARTDATE dbo.UDT_MONTHDAY = '0000',
                @SEASONALENDDATE dbo.UDT_MONTHDAY = '0000',
                @DONOTTEXT bit=0
            )
            as
                set nocount on;

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

                if @CURRENTDATE is null
                    set @CURRENTDATE = getdate();

                declare @CONSTITUENTID uniqueidentifier;
                declare @OLDNUMBER nvarchar(100);
                declare @OLDPHONETYPECODEID uniqueidentifier;

                if @DONOTCALL = 1
                    begin
                        set @STARTTIME = '';
                        set @ENDTIME = '';
                    end
                else
                    begin
                        set @DONOTCALLREASONCODEID = null;
                    end

                select
                    @CONSTITUENTID = PHONE.[CONSTITUENTID],
                    @OLDNUMBER = PHONE.[NUMBER],
                    @OLDPHONETYPECODEID = PHONE.[PHONETYPECODEID]
                from
                    dbo.[PHONE]
                where
                    PHONE.[ID] = @ID;

                begin try

                    -- JNA <10/21/2009> Check for/remove country code

                    if @COUNTRYID is not null
                        begin
                            declare @COUNTRYCODE nvarchar(10);

                            select
                                @COUNTRYCODE = COUNTRYCODE
                            from dbo.COUNTRY
                            where ID = @COUNTRYID;

                            if left(@NUMBER, len(@COUNTRYCODE) + 1) = '+' + @COUNTRYCODE
                                set @NUMBER = ltrim(right(@NUMBER, len(@NUMBER) - (len(@COUNTRYCODE) + 1)));                            

                        end

                    if @PRIMARY = 1
                        update
                            dbo.[PHONE] 
                        set
                            ISPRIMARY = 0,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where
                            CONSTITUENTID = @CONSTITUENTID and
                            ISPRIMARY = 1 and
                            ID <> @ID;

                    update
                        dbo.PHONE
                    set
                        PHONETYPECODEID = @PHONETYPECODEID,
                        NUMBER = @NUMBER,
                        ISPRIMARY = @PRIMARY,
                        DONOTCALL = @DONOTCALL,
                        STARTTIME = @STARTTIME,
                        ENDTIME = @ENDTIME,
                        INFOSOURCECODEID = @INFOSOURCECODEID,
                        INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS,
                        COUNTRYID = @COUNTRYID,
                        STARTDATE = @STARTDATE,
                        ENDDATE = @ENDDATE,
                        DONOTCALLREASONCODEID = @DONOTCALLREASONCODEID,
                        ISCONFIDENTIAL = @ISCONFIDENTIAL,
                        SEASONALSTARTDATE = @SEASONALSTARTDATE,
                        SEASONALENDDATE = @SEASONALENDDATE,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE,
                        DONOTTEXT = @DONOTTEXT
                    where
                        ID = @ID;

                    if @UPDATEMATCHINGHOUSEHOLDPHONE = 1 begin
                        declare @MATCHINGCONSTITUENTS table(ID uniqueidentifier);
                        insert into @MATCHINGCONSTITUENTS select CONSTITUENTID from dbo.UFN_PHONE_MATCHINGHOUSEHOLDRECORDS(@CONSTITUENTID, @OLDNUMBER, @OLDPHONETYPECODEID);

                        begin try
                            -- update the existing records

                            update dbo.PHONE
                            set
                                PHONETYPECODEID = @PHONETYPECODEID,
                                NUMBER = @NUMBER,
                                DONOTCALL = @DONOTCALL,
                                STARTTIME = @STARTTIME,
                                ENDTIME = @ENDTIME,
                                INFOSOURCECODEID = @INFOSOURCECODEID,
                                INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS,
                                COUNTRYID = @COUNTRYID,
                                STARTDATE = @STARTDATE,
                                ENDDATE = @ENDDATE,
                                DONOTCALLREASONCODEID = @DONOTCALLREASONCODEID,
                                ISCONFIDENTIAL = @ISCONFIDENTIAL,
                                SEASONALSTARTDATE = @SEASONALSTARTDATE,
                                SEASONALENDDATE = @SEASONALENDDATE,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE,
                                DONOTTEXT = @DONOTTEXT
                            where
                                NUMBER = @OLDNUMBER
                                and ( (PHONETYPECODEID = @OLDPHONETYPECODEID) or (@OLDPHONETYPECODEID is null and PHONETYPECODEID is null) )
                                and CONSTITUENTID in (select ID from @MATCHINGCONSTITUENTS);
                        end try
                        begin catch
                            if PATINDEX('%CK_PHONE_ENDDATEINVALIDIFPRIMARY%', ERROR_MESSAGE()) > 0
                                raiserror('ERR_PHONE_ENDDATEINVALIDIFPRIMARYFORANOTHERMEMBER', 13, 1);
                            else
                                exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch
                    end
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;