USP_OPPORTUNITYSOLICITOR_UPDATEFROMXML

create procedure dbo.USP_OPPORTUNITYSOLICITOR_UPDATEFROMXML
(
  @OPPORTUNITYID uniqueidentifier,
  @XML xml,
  @CHANGEAGENTID uniqueidentifier = null,
  @CURRENTDATE datetime = null
)
as

  set nocount on;

  if @CHANGEAGENTID is null
    exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

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

  declare @TEMPTBL table
  (
    OPPORTUNITYSOLICITORID uniqueidentifier,
    PLANFUNDRAISERID uniqueidentifier,
    FUNDRAISERROLE tinyint,
    FUNDRAISERSELECTED bit
  )

  insert into @TEMPTBL
  select
    ID,
    PLANFUNDRAISERID,
    FUNDRAISERTYPECODE,
    FUNDRAISERSELECTED
  from
    dbo.UFN_OPPORTUNITY_GETSOLICITORS_FROMITEMLISTXML(@XML)

  declare @CONTEXTCACHE varbinary(128) = context_info();
  set context_info @CHANGEAGENTID;

  --Remove any solicitors that were not included to be selected

  delete from
    dbo.OPPORTUNITYSOLICITOR
  where
    OPPORTUNITYSOLICITOR.OPPORTUNITYID = @OPPORTUNITYID and
    OPPORTUNITYSOLICITOR.ID not in
    (
      select
        OPPORTUNITYSOLICITORID
      from
        @TEMPTBL
    );

  --Remove any solicitors who were explicitly unselected

  delete
    dbo.OPPORTUNITYSOLICITOR
  from
    @TEMPTBL TEMP
  inner join
    dbo.OPPORTUNITYSOLICITOR on OPPORTUNITYSOLICITOR.ID = TEMP.OPPORTUNITYSOLICITORID
  where
    TEMP.FUNDRAISERSELECTED = 0;

  if not @CONTEXTCACHE is null
    set context_info @CONTEXTCACHE;

  --Add selected solicitors who were not previously selected

  insert into dbo.OPPORTUNITYSOLICITOR
  (
    OPPORTUNITYID,
    FUNDRAISERTYPECODE,
    SECONDARYSOLICITORID,
    ADDEDBYID,
    CHANGEDBYID,
    DATEADDED,
    DATECHANGED
  )
  select
    @OPPORTUNITYID,
    TEMP.FUNDRAISERROLE,
    TEMP.PLANFUNDRAISERID,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CURRENTDATE,
    @CURRENTDATE
  from
    @TEMPTBL TEMP
  left join
    dbo.OPPORTUNITYSOLICITOR on OPPORTUNITYSOLICITOR.ID = TEMP.OPPORTUNITYSOLICITORID
  where
    OPPORTUNITYSOLICITOR.ID is null and
    TEMP.FUNDRAISERSELECTED = 1;

  return;