USP_GIFTFEE_GETCONSTITUENCIES_CUSTOMUPDATEFROMXML

            CREATE procedure [dbo].[USP_GIFTFEE_GETCONSTITUENCIES_CUSTOMUPDATEFROMXML] 
            (
                @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 (
                   [ID] uniqueidentifier,
                   [CONSTITUENCYID] uniqueidentifier)

                insert into @TempTbl select 
                    [ID],
                    [CONSTITUENCYID] 
                from dbo.UFN_GIFTFEE_GETCONSTITUENCIES_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.[GIFTFEECONSTITUENCY] where [GIFTFEECONSTITUENCY].ID in 
                    (select ID from dbo.UFN_GIFTFEE_GETCONSTITUENCIES()
                    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 
                    [GIFTFEECONSTITUENCY]
                set 
                    [GIFTFEECONSTITUENCY].[ID]=temp.[ID],
                    [GIFTFEECONSTITUENCY].[USERCONSTITUENCYCODEID]=USERDEFINEDCONSTITUENCYDEFINITION.ID,
                    [GIFTFEECONSTITUENCY].[SYSTEMCONSTITUENCYID]=SYSTEMCONSTITUENCYDEFINITION.ID,
                    [GIFTFEECONSTITUENCY].CHANGEDBYID = @CHANGEAGENTID,
                    [GIFTFEECONSTITUENCY].DATECHANGED = @CHANGEDATE

                from 
                    dbo.[GIFTFEECONSTITUENCY] 
                    inner join @TempTbl as temp on [GIFTFEECONSTITUENCY].ID = temp.ID
                    left join dbo.CONSTITUENCYDEFINITION as USERDEFINEDCONSTITUENCYDEFINITION on USERDEFINEDCONSTITUENCYDEFINITION.ID = temp.[CONSTITUENCYID] and USERDEFINEDCONSTITUENCYDEFINITION.ISSYSTEM = 0
                    left join dbo.CONSTITUENCYDEFINITION as SYSTEMCONSTITUENCYDEFINITION on SYSTEMCONSTITUENCYDEFINITION.ID = temp.[CONSTITUENCYID] and SYSTEMCONSTITUENCYDEFINITION.ISSYSTEM = 1
                where 
                    (
                        SYSTEMCONSTITUENCYDEFINITION.ISACTIVE = 1
                        or USERDEFINEDCONSTITUENCYDEFINITION.ISACTIVE = 1
                    ) and
                    (
                        ([GIFTFEECONSTITUENCY].[ID]<>temp.[ID]) or 
                        ([GIFTFEECONSTITUENCY].[ID] is null and temp.[ID] is not null) or 
                        ([GIFTFEECONSTITUENCY].[ID] is not null and temp.[ID] is null) or 
                        ([GIFTFEECONSTITUENCY].[USERCONSTITUENCYCODEID]<>temp.[CONSTITUENCYID]) or 
                        ([GIFTFEECONSTITUENCY].[USERCONSTITUENCYCODEID] is null and temp.[CONSTITUENCYID] is not null) or 
                        ([GIFTFEECONSTITUENCY].[USERCONSTITUENCYCODEID] is not null and temp.[CONSTITUENCYID] is null)
                    )

                if @@Error <> 0
                    return 3;    

                -- insert new items

                insert into [GIFTFEECONSTITUENCY](
                    [ID],
                    [USERCONSTITUENCYCODEID],    
                    [SYSTEMCONSTITUENCYID],    
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED)
                select 
                    temp.[ID],
                    USERDEFINEDCONSTITUENCYDEFINITION.ID,
                    SYSTEMCONSTITUENCYDEFINITION.ID,
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CHANGEDATE, 
                    @CHANGEDATE
                from 
                    @TempTbl as temp
                    left join dbo.CONSTITUENCYDEFINITION as USERDEFINEDCONSTITUENCYDEFINITION on USERDEFINEDCONSTITUENCYDEFINITION.ID = temp.[CONSTITUENCYID] and USERDEFINEDCONSTITUENCYDEFINITION.ISSYSTEM = 0
                    left join dbo.CONSTITUENCYDEFINITION as SYSTEMCONSTITUENCYDEFINITION on SYSTEMCONSTITUENCYDEFINITION.ID = temp.[CONSTITUENCYID] and SYSTEMCONSTITUENCYDEFINITION.ISSYSTEM = 1
                where 
                    not exists (select ID from dbo.[GIFTFEECONSTITUENCY] as DATA where DATA.ID = TEMP.ID)
                    and (
                        SYSTEMCONSTITUENCYDEFINITION.ISACTIVE = 1
                        or USERDEFINEDCONSTITUENCYDEFINITION.ISACTIVE = 1
                    )

                if @@Error <> 0
                    return 4;

                return 0;