USP_MEMBERSHIPDUESBATCH_GETMEMBERSHIPPROGRAMADDONS_UPDATEFROMXML

CREATE procedure dbo.USP_MEMBERSHIPDUESBATCH_GETMEMBERSHIPPROGRAMADDONS_UPDATEFROMXML
(
    @BATCHMEMBERSHIPDUESID uniqueidentifier,
    @MEMBERSHIPPROGRAMADDONS xml,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null
)
as
begin    
    set nocount on;

    if @CHANGEAGENTID is null
        exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

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

    merge into dbo.BATCHMEMBERSHIPDUESMEMBERSHIPROGRAMADDON as target
    using (
        select
            ADDONID,
            NUMBEROFADDONS
        from dbo.UFN_MEMBERSHIPDUESBATCH_GETMEMBERSHIPPROGRAMADDONS_FROMITEMLISTXML(@MEMBERSHIPPROGRAMADDONS)
        where APPLY = 1 and NUMBEROFADDONS > 0
    ) as source
        on target.BATCHMEMBERSHIPDUESID = @BATCHMEMBERSHIPDUESID and source.ADDONID = target.ADDONID
    when not matched by target then
        insert (ID, BATCHMEMBERSHIPDUESID, ADDONID, NUMBEROFADDONS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (newID(), @BATCHMEMBERSHIPDUESID, source.ADDONID, source.NUMBEROFADDONS, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE)
    when matched and source.NUMBEROFADDONS <> target.NUMBEROFADDONS then
        update set target.NUMBEROFADDONS = source.NUMBEROFADDONS, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
    when not matched by source and target.BATCHMEMBERSHIPDUESID = @BATCHMEMBERSHIPDUESID then
        delete;
end