USP_HOUSEHOLDRECOGNITIONS_MEMBERSPECIFIEDMEMBERSUPDATE

            create procedure dbo.USP_HOUSEHOLDRECOGNITIONS_MEMBERSPECIFIEDMEMBERSUPDATE
            (
                @GROUPID uniqueidentifier,
                @MEMBERSPECIFIEDMEMBERS xml,
                @CHANGEDATE datetime,
                @CHANGEAGENTID uniqueidentifier
            )
            as
                set nocount on

                -- This stored procedure is used rather than the one that can be generated off a function

                -- since the parent ID (GROUPID) doesn't belong to the source table (REVENUERECOGNITIONDEFAULT).


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

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

                declare @TempTbl table (
                    [ID] uniqueidentifier,
                    [MATCHFACTOR] decimal(5, 2),
                    [RECIPIENTCONSTITUENTID] uniqueidentifier,
                    [SOURCECONSTITUENTID] uniqueidentifier,
                    [REVENUERECOGNITIONTYPECODEID] uniqueidentifier)

                insert into @TempTbl select 
                    [ID],
                    [MATCHFACTOR],
                    [RECIPIENTCONSTITUENTID],
                    [SOURCECONSTITUENTID],
                    [REVENUERECOGNITIONTYPECODEID]
                from dbo.UFN_HOUSEHOLDRECOGNITIONS_GETMEMBERSPECIFIEDMEMBERS_FROMITEMLISTXML(@MEMBERSPECIFIEDMEMBERS)

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

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

                delete from dbo.REVENUERECOGNITIONDEFAULT where REVENUERECOGNITIONDEFAULT.ID in 
                    (select ID from dbo.UFN_HOUSEHOLDRECOGNITIONS_GETMEMBERSPECIFIEDMEMBERS(@GROUPID)
                    EXCEPT select ID from @TempTbl)    

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

                update REVENUERECOGNITIONDEFAULT
                    set
                    REVENUERECOGNITIONDEFAULT.[ID]=temp.[ID],
                    REVENUERECOGNITIONDEFAULT.[MATCHFACTOR]=temp.[MATCHFACTOR],
                    REVENUERECOGNITIONDEFAULT.[RECIPIENTCONSTITUENTID]=temp.[RECIPIENTCONSTITUENTID],
                    REVENUERECOGNITIONDEFAULT.[SOURCECONSTITUENTID]=temp.[SOURCECONSTITUENTID],
                    REVENUERECOGNITIONDEFAULT.CHANGEDBYID = @CHANGEAGENTID,
                    REVENUERECOGNITIONDEFAULT.DATECHANGED = @CHANGEDATE
                    from dbo.REVENUERECOGNITIONDEFAULT inner join @TempTbl as [temp] on REVENUERECOGNITIONDEFAULT.ID = [temp].ID
                    where 
                        (REVENUERECOGNITIONDEFAULT.[ID]<>temp.[ID]) or 
                        (REVENUERECOGNITIONDEFAULT.[ID] is null and temp.[ID] is not null) or 
                        (REVENUERECOGNITIONDEFAULT.[ID] is not null and temp.[ID] is null) or 
                        (REVENUERECOGNITIONDEFAULT.[MATCHFACTOR]<>temp.[MATCHFACTOR]) or 
                        (REVENUERECOGNITIONDEFAULT.[MATCHFACTOR] is null and temp.[MATCHFACTOR] is not null) or 
                        (REVENUERECOGNITIONDEFAULT.[MATCHFACTOR] is not null and temp.[MATCHFACTOR] is null) or 
                        (REVENUERECOGNITIONDEFAULT.[REVENUERECOGNITIONTYPECODEID]<>temp.[REVENUERECOGNITIONTYPECODEID]) or 
                        (REVENUERECOGNITIONDEFAULT.[REVENUERECOGNITIONTYPECODEID] is null and temp.[REVENUERECOGNITIONTYPECODEID] is not null) or 
                        (REVENUERECOGNITIONDEFAULT.[REVENUERECOGNITIONTYPECODEID] is not null and temp.[REVENUERECOGNITIONTYPECODEID] is null) or 
                        (REVENUERECOGNITIONDEFAULT.[RECIPIENTCONSTITUENTID]<>temp.[RECIPIENTCONSTITUENTID]) or 
                        (REVENUERECOGNITIONDEFAULT.[RECIPIENTCONSTITUENTID] is null and temp.[RECIPIENTCONSTITUENTID] is not null) or 
                        (REVENUERECOGNITIONDEFAULT.[RECIPIENTCONSTITUENTID] is not null and temp.[RECIPIENTCONSTITUENTID] is null) or 
                        (REVENUERECOGNITIONDEFAULT.[SOURCECONSTITUENTID]<>temp.[SOURCECONSTITUENTID]) or 
                        (REVENUERECOGNITIONDEFAULT.[SOURCECONSTITUENTID] is null and temp.[SOURCECONSTITUENTID] is not null) or 
                        (REVENUERECOGNITIONDEFAULT.[SOURCECONSTITUENTID] is not null and temp.[SOURCECONSTITUENTID] is null)

                -- insert new items

                insert into REVENUERECOGNITIONDEFAULT 
                (
                    [ID],
                    [MATCHFACTOR],
                    [REVENUERECOGNITIONTYPECODEID],
                    [RECIPIENTCONSTITUENTID],
                    [SOURCECONSTITUENTID],
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED)
                select
                    [ID],
                    [MATCHFACTOR],
                    [REVENUERECOGNITIONTYPECODEID],
                    [RECIPIENTCONSTITUENTID],
                    [SOURCECONSTITUENTID],
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CHANGEDATE, 
                    @CHANGEDATE
                from @TempTbl as [temp]
                where not exists (select ID from dbo.REVENUERECOGNITIONDEFAULT as data where data.ID = [temp].ID)