USP_DATAFORMTEMPLATE_ADD_FAFNFGASSIGNGROUPS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_FAFNFGASSIGNGROUPS
(
  @ID uniqueidentifier = null output,
  @CONTEXTID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @GROUPS xml = null
)
as

set nocount on;

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

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

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

declare @ASSIGNMENTLIST table
(
  ID uniqueidentifier,
  CHECKED bit,
  NEWASSIGNMENT uniqueidentifier
)

insert into @ASSIGNMENTLIST
  select
    T.c.value('(ID)[1]', 'uniqueidentifier') as ID,
    T.c.value('(CHECKED)[1]', 'bit') as CHECKED,
    T.c.value('(NEWASSIGNMENT)[1]', 'uniqueidentifier') as NEWASSIGNMENT
  from @GROUPS.nodes('/GROUPS/ITEM') T(c)

begin tran
begin try

update dbo.TEAMEXTENSION set NFGCAMPAIGNLEVELID = AL.NEWASSIGNMENT
  from TEAMEXTENSION TX
  inner join @ASSIGNMENTLIST AL on AL.ID = TX.TEAMFUNDRAISINGTEAMID
  where AL.CHECKED = 1 and AL.NEWASSIGNMENT is not null

update dbo.TEAMFUNDRAISINGTEAM set PARENTTEAMID = null
  from dbo.TEAMFUNDRAISINGTEAM TFT
  inner join dbo.TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID = TFT.ID
  inner join @ASSIGNMENTLIST AL on AL.ID = TFT.ID
  where AL.CHECKED = 1 and al.NEWASSIGNMENT is not null and TX.TYPECODE in (1, 2, 3) and TFT.PARENTTEAMID is not null

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    ROLLBACK TRAN
    return 1
end catch

commit tran
return 0