USP_MEMBERSHIPDUESBATCH_GETCHILDREN_UPDATEFROMXML

CREATE procedure dbo.USP_MEMBERSHIPDUESBATCH_GETCHILDREN_UPDATEFROMXML
(
    @BATCHMEMBERSHIPDUESID uniqueidentifier,
    @CHILDREN 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.BATCHMEMBERSHIPDUESMEMBER as target
    using (
        select
            ID,
            CONSTITUENTID
        from dbo.UFN_MEMBERSHIPDUESBATCH_GETCHILDREN_FROMITEMLISTXML(@CHILDREN)
    ) as source
        on target.BATCHMEMBERSHIPDUESID = @BATCHMEMBERSHIPDUESID and source.ID = target.ID and target.TYPECODE = 1 
    when not matched by target then
        insert (ID, BATCHMEMBERSHIPDUESID, CONSTITUENTID, TYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (newID(), @BATCHMEMBERSHIPDUESID, source.CONSTITUENTID, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE)
    when matched and source.CONSTITUENTID <> target.CONSTITUENTID then
        update set target.CONSTITUENTID = source.CONSTITUENTID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
    when not matched by source and target.BATCHMEMBERSHIPDUESID = @BATCHMEMBERSHIPDUESID and target.TYPECODE = 1 then
        delete;

end