USP_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_UPDATEFROMXML

            CREATE procedure dbo.USP_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_UPDATEFROMXML
            (
                @BATCHCONSTITUENTID uniqueidentifier,
                @XML xml,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            as

            set nocount on;

            if @CHANGEAGENTID is null
                exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

            if @CHANGEDATE is null 
                set @CHANGEDATE = getdate()

            -- build a temporary table containing the values from the XML

            declare @TempTbl table (
                [COUNTRYID] uniqueidentifier,
                [ENDTIME] char(4),
                [ID] uniqueidentifier,
                [ISPRIMARY] bit,
                [NUMBER] nvarchar(100),
                [PHONETYPECODEID] uniqueidentifier,
                [SEASONALENDDATE] varchar(4),
                [SEASONALSTARTDATE] varchar(4),
                [SEQUENCE] int,
                [STARTDATE] date,
                [STARTTIME] char(4),
                [INFOSOURCECODEID] uniqueidentifier,
                [INFOSOURCECOMMENTS] nvarchar(256))

            insert into @TempTbl select 
                [COUNTRYID],
                [ENDTIME],
                [ID],
                [ISPRIMARY],
                [NUMBER] = dbo.UFN_PHONE_REMOVECOUNTRYCODE([NUMBER], [COUNTRYID]),
                [PHONETYPECODEID],
                [SEASONALENDDATE],
                [SEASONALSTARTDATE],
                [SEQUENCE],
                [STARTDATE],
                [STARTTIME],
                [INFOSOURCECODEID],
                [INFOSOURCECOMMENTS]
            from dbo.UFN_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_FROMITEMLISTXML(@XML)

            update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');

            if @@Error <> 0
                return 1;

            declare @contextCache varbinary(128);
            declare @e int;

            -- cache current context information 

            set @contextCache = CONTEXT_INFO();

            -- set CONTEXT_INFO to @CHANGEAGENTID 

            if not @CHANGEAGENTID is null
                set CONTEXT_INFO @CHANGEAGENTID;

            -- delete any items that no longer exist in the XML table

            delete from dbo.[BATCHCONSTITUENTPHONE] where [BATCHCONSTITUENTPHONE].ID in 
                (select ID from dbo.UFN_CONSTITUENTBATCH_GETPHONESWITHSEASONAL
                (
                    @BATCHCONSTITUENTID
                )
                EXCEPT select ID from @TempTbl)    

            select @e=@@error;

            -- reset CONTEXT_INFO to previous value 

            if not @contextCache is null
                set CONTEXT_INFO @contextCache;

            if @e <> 0
                return 2;

            -- update the items that exist in the XML table and the db

            update [BATCHCONSTITUENTPHONE]
                    set [BATCHCONSTITUENTPHONE].[COUNTRYID]=temp.[COUNTRYID],
                    [BATCHCONSTITUENTPHONE].[ENDTIME]=temp.[ENDTIME],
                    [BATCHCONSTITUENTPHONE].[ID]=temp.[ID],
                    [BATCHCONSTITUENTPHONE].[ISPRIMARY]=temp.[ISPRIMARY],
                    [BATCHCONSTITUENTPHONE].[NUMBER]=temp.[NUMBER],
                    [BATCHCONSTITUENTPHONE].[PHONETYPECODEID]=temp.[PHONETYPECODEID],
                    [BATCHCONSTITUENTPHONE].[SEASONALENDDATE]=temp.[SEASONALENDDATE],
                    [BATCHCONSTITUENTPHONE].[SEASONALSTARTDATE]=temp.[SEASONALSTARTDATE],
                    [BATCHCONSTITUENTPHONE].[SEQUENCE]=temp.[SEQUENCE],
                    [BATCHCONSTITUENTPHONE].[STARTDATE]=temp.[STARTDATE],
                    [BATCHCONSTITUENTPHONE].[STARTTIME]=temp.[STARTTIME],
                    [BATCHCONSTITUENTPHONE].CHANGEDBYID = @CHANGEAGENTID,
                    [BATCHCONSTITUENTPHONE].DATECHANGED = @CHANGEDATE,
                    [BATCHCONSTITUENTPHONE].[INFOSOURCECODEID]=temp.[INFOSOURCECODEID],
                    [BATCHCONSTITUENTPHONE].[INFOSOURCECOMMENTS]=temp.[INFOSOURCECOMMENTS]

                from dbo.[BATCHCONSTITUENTPHONE] inner join @TempTbl as [temp] on [BATCHCONSTITUENTPHONE].ID = [temp].ID
                where ([BATCHCONSTITUENTPHONE].[COUNTRYID]<>temp.[COUNTRYID]) or 
                    ([BATCHCONSTITUENTPHONE].[COUNTRYID] is null and temp.[COUNTRYID] is not null) or 
                    ([BATCHCONSTITUENTPHONE].[COUNTRYID] is not null and temp.[COUNTRYID] is null) or 
                    ([BATCHCONSTITUENTPHONE].[ENDTIME]<>temp.[ENDTIME]) or 
                    ([BATCHCONSTITUENTPHONE].[ENDTIME] is null and temp.[ENDTIME] is not null) or 
                    ([BATCHCONSTITUENTPHONE].[ENDTIME] is not null and temp.[ENDTIME] is null) or 
                    ([BATCHCONSTITUENTPHONE].[ID]<>temp.[ID]) or 
                    ([BATCHCONSTITUENTPHONE].[ID] is null and temp.[ID] is not null) or 
                    ([BATCHCONSTITUENTPHONE].[ID] is not null and temp.[ID] is null) or 
                    ([BATCHCONSTITUENTPHONE].[ISPRIMARY]<>temp.[ISPRIMARY]) or 
                    ([BATCHCONSTITUENTPHONE].[ISPRIMARY] is null and temp.[ISPRIMARY] is not null) or 
                    ([BATCHCONSTITUENTPHONE].[ISPRIMARY] is not null and temp.[ISPRIMARY] is null) or 
                    ([BATCHCONSTITUENTPHONE].[NUMBER]<>temp.[NUMBER]) or 
                    ([BATCHCONSTITUENTPHONE].[NUMBER] is null and temp.[NUMBER] is not null) or 
                    ([BATCHCONSTITUENTPHONE].[NUMBER] is not null and temp.[NUMBER] is null) or 
                    ([BATCHCONSTITUENTPHONE].[PHONETYPECODEID]<>temp.[PHONETYPECODEID]) or 
                    ([BATCHCONSTITUENTPHONE].[PHONETYPECODEID] is null and temp.[PHONETYPECODEID] is not null) or 
                    ([BATCHCONSTITUENTPHONE].[PHONETYPECODEID] is not null and temp.[PHONETYPECODEID] is null) or 
                    ([BATCHCONSTITUENTPHONE].[SEASONALENDDATE]<>temp.[SEASONALENDDATE]) or 
                    ([BATCHCONSTITUENTPHONE].[SEASONALENDDATE] is null and temp.[SEASONALENDDATE] is not null) or 
                    ([BATCHCONSTITUENTPHONE].[SEASONALENDDATE] is not null and temp.[SEASONALENDDATE] is null) or 
                    ([BATCHCONSTITUENTPHONE].[SEASONALSTARTDATE]<>temp.[SEASONALSTARTDATE]) or 
                    ([BATCHCONSTITUENTPHONE].[SEASONALSTARTDATE] is null and temp.[SEASONALSTARTDATE] is not null) or 
                    ([BATCHCONSTITUENTPHONE].[SEASONALSTARTDATE] is not null and temp.[SEASONALSTARTDATE] is null) or 
                    ([BATCHCONSTITUENTPHONE].[SEQUENCE]<>temp.[SEQUENCE]) or 
                    ([BATCHCONSTITUENTPHONE].[SEQUENCE] is null and temp.[SEQUENCE] is not null) or 
                    ([BATCHCONSTITUENTPHONE].[SEQUENCE] is not null and temp.[SEQUENCE] is null) or 
                    ([BATCHCONSTITUENTPHONE].[STARTDATE]<>temp.[STARTDATE]) or 
                    ([BATCHCONSTITUENTPHONE].[STARTDATE] is null and temp.[STARTDATE] is not null) or 
                    ([BATCHCONSTITUENTPHONE].[STARTDATE] is not null and temp.[STARTDATE] is null) or 
                    ([BATCHCONSTITUENTPHONE].[STARTTIME]<>temp.[STARTTIME]) or 
                    ([BATCHCONSTITUENTPHONE].[STARTTIME] is null and temp.[STARTTIME] is not null) or 
                    ([BATCHCONSTITUENTPHONE].[STARTTIME] is not null and temp.[STARTTIME] is null) or 
                    ([BATCHCONSTITUENTPHONE].[INFOSOURCECODEID]<>temp.[INFOSOURCECODEID]) or 
                    ([BATCHCONSTITUENTPHONE].[INFOSOURCECODEID] is null and temp.[INFOSOURCECODEID] is not null) or 
                    ([BATCHCONSTITUENTPHONE].[INFOSOURCECODEID] is not null and temp.[INFOSOURCECODEID] is null) or 
                    ([BATCHCONSTITUENTPHONE].[INFOSOURCECOMMENTS]<>temp.[INFOSOURCECOMMENTS]) or 
                    ([BATCHCONSTITUENTPHONE].[INFOSOURCECOMMENTS] is null and temp.[INFOSOURCECOMMENTS] is not null) or 
                    ([BATCHCONSTITUENTPHONE].[INFOSOURCECOMMENTS] is not null and temp.[INFOSOURCECOMMENTS] is null)

            if @@Error <> 0
                return 3;    

            -- insert new items

            insert into [BATCHCONSTITUENTPHONE] 
                ([BATCHCONSTITUENTID], 
                [COUNTRYID],
                [ENDTIME],
                [ID],
                [ISPRIMARY],
                [NUMBER],
                [PHONETYPECODEID],
                [SEASONALENDDATE],
                [SEASONALSTARTDATE],
                [SEQUENCE],
                [STARTDATE],
                [STARTTIME],
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED,
                [INFOSOURCECODEID],
                [INFOSOURCECOMMENTS])
            select @BATCHCONSTITUENTID, 
                [COUNTRYID],
                [ENDTIME],
                [ID],
                [ISPRIMARY],
                [NUMBER],
                [PHONETYPECODEID],
                [SEASONALENDDATE],
                [SEASONALSTARTDATE],
                [SEQUENCE],
                [STARTDATE],
                [STARTTIME], 
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CHANGEDATE, 
                @CHANGEDATE,
                [INFOSOURCECODEID],
                [INFOSOURCECOMMENTS]
            from @TempTbl as [temp]
            where not exists (select ID from dbo.[BATCHCONSTITUENTPHONE] as data where data.ID = [temp].ID)

            if @@Error <> 0
                return 4;

            return 0;