USP_CONSTITUENTUPDATEBATCH_ADJUSTSOLICITCODEDATERANGES

CREATE procedure USP_CONSTITUENTUPDATEBATCH_ADJUSTSOLICITCODEDATERANGES
(
  @CONSTITUENTID uniqueidentifier,
  @SOLICITCODES xml,
  @CHANGEAGENTID uniqueidentifier = null
)
as
begin
  if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  declare @CURRENTDATE datetime = getdate();

  declare @INPUT table
  (
    SOLICITCODEID uniqueidentifier,
    NEW_STARTDATE date
  );

  -- Populate temp table with selected data

  insert into @INPUT
  select
    T.c.value('(SOLICITCODEID)[1]','uniqueidentifier') AS 'SOLICITCODEID',
    T.c.value('(STARTDATE)[1]','date') AS 'STARTDATE'
  from @SOLICITCODES.nodes('/SOLICITCODES/ITEM') T(c);

  begin try
    with UPDATEROWS as
    (
      select
        ROW_NUMBER() over (partition by BATCHCONSTITUENTUPDATE.PRIMARYRECORDID, BATCHCONSTITUENTUPDATESOLICITCODE.SOLICITCODEID order by BATCHCONSTITUENTUPDATESOLICITCODE.STARTDATE desc) as ROWNUMBER,
        BATCHCONSTITUENTUPDATESOLICITCODE.ID as BATCHCONSTITUENTUPDATESOLICITCODEID,
        INPUT.NEW_STARTDATE
      from @INPUT INPUT
        inner join dbo.BATCHCONSTITUENTUPDATESOLICITCODE on INPUT.SOLICITCODEID = BATCHCONSTITUENTUPDATESOLICITCODE.SOLICITCODEID
        inner join dbo.BATCHCONSTITUENTUPDATE on BATCHCONSTITUENTUPDATE.ID = BATCHCONSTITUENTUPDATESOLICITCODE.BATCHCONSTITUENTUPDATEID
        inner join dbo.SOLICITCODE on BATCHCONSTITUENTUPDATESOLICITCODE.SOLICITCODEID = SOLICITCODE.ID
      where
        BATCHCONSTITUENTUPDATE.PRIMARYRECORDID = @CONSTITUENTID
        and (SOLICITCODE.CONSENTCODE = 1 
             or
             SOLICITCODE.CONSENTCODE = 2) -- EU / Advanced Consent

        and BATCHCONSTITUENTUPDATESOLICITCODE.STARTDATE < INPUT.NEW_STARTDATE
        and BATCHCONSTITUENTUPDATESOLICITCODE.ENDDATE is null
    )
    update dbo.BATCHCONSTITUENTUPDATESOLICITCODE
    set
      ENDDATE = dateadd(day, -1, UPDATEROWS.NEW_STARTDATE),
      DATECHANGED = @CURRENTDATE,
      CHANGEDBYID = @CHANGEAGENTID
    from UPDATEROWS
    where
      BATCHCONSTITUENTUPDATESOLICITCODE.ID = UPDATEROWS.BATCHCONSTITUENTUPDATESOLICITCODEID
      and UPDATEROWS.ROWNUMBER = 1;
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;
end