USP_DATAFORMTEMPLATE_ADD_FAFPROGRAMASSIGNEVENTS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_FAFPROGRAMASSIGNEVENTS
(
    @ID uniqueidentifier = null output,
    @FAFPROGRAMID uniqueidentifier,
    @SELECTEDEVENTS xml = null,
    @CHANGEAGENTID uniqueidentifier = null
)
as

set nocount on;

declare @CURRENTDATE datetime
declare @REMOVEDEVENTLIST table(EVENTID uniqueidentifier)
declare @ADDEDEVENTLIST table(EVENTID uniqueidentifier)

if @ID is null
    set @ID = newid()

set @CURRENTDATE = getdate()

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

    insert into @REMOVEDEVENTLIST(EVENTID)
    select EVENTID 
    from dbo.EVENTEXTENSION 
    where FAFPROGRAMID  = @FAFPROGRAMID
    and EVENTID not in (select EVENTID from dbo.UFN_GLOBALCHANGE_GETEVENTLIST_FROMITEMLISTXML(@SELECTEDEVENTS))

    insert into @ADDEDEVENTLIST(EVENTID)
    select EVENTID 
    from dbo.UFN_GLOBALCHANGE_GETEVENTLIST_FROMITEMLISTXML(@SELECTEDEVENTS)
    where EVENTID not in (select EVENTID from dbo.EVENTEXTENSION where FAFPROGRAMID = @FAFPROGRAMID)

begin try
    update dbo.EVENTEXTENSION 
    set FAFPROGRAMID = NULL, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
    where EVENTID in (select EVENTID from @REMOVEDEVENTLIST)

    update dbo.EVENTEXTENSION 
    set FAFPROGRAMID = @FAFPROGRAMID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
    where EVENTID in (select EVENTID from @ADDEDEVENTLIST)

    --remove local group from this campaign
    update dbo.TEAMEXTENSION 
    set NFGCAMPAIGNLEVELID = null,CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
    where EVENTID in (select EVENTID from @REMOVEDEVENTLIST)
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0