USP_BATCHREVENUE_ADJUSTSOLICITCODEDATERANGES

CREATE procedure USP_BATCHREVENUE_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 BATCHREVENUE.CONSTITUENTID, BATCHREVENUECONSTITUENTSOLICITCODE.SOLICITCODEID, BATCHREVENUECONSTITUENTSOLICITCODE.BATCHREVENUEID order by BATCHREVENUECONSTITUENTSOLICITCODE.STARTDATE desc) as ROWNUMBER,
        BATCHREVENUECONSTITUENTSOLICITCODE.ID as BATCHREVENUECONSTITUENTSOLICITCODE,
        INPUT.NEW_STARTDATE
      from @INPUT INPUT
        inner join dbo.BATCHREVENUECONSTITUENTSOLICITCODE on INPUT.SOLICITCODEID = BATCHREVENUECONSTITUENTSOLICITCODE.SOLICITCODEID
        inner join dbo.BATCHREVENUE on BATCHREVENUE.ID = BATCHREVENUECONSTITUENTSOLICITCODE.BATCHREVENUEID
        inner join dbo.SOLICITCODE on BATCHREVENUECONSTITUENTSOLICITCODE.SOLICITCODEID = SOLICITCODE.ID
      where
        BATCHREVENUE.CONSTITUENTID = @CONSTITUENTID
        and SOLICITCODE.CONSENTCODE in(1,2) -- EU Consent/Advanced Consent

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

  return 0;
end