USP_MEMBERSHIPDUESBATCH_ADJUSTSOLICITCODEDATERANGES

CREATE procedure dbo.USP_MEMBERSHIPDUESBATCH_ADJUSTSOLICITCODEDATERANGES
(
  @BILLTOCONSTITUENTID 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 BATCHMEMBERSHIPDUES.BILLTOCONSTITUENTID, BATCHMEMBERSHIPDUESSOLICITCODE.SOLICITCODEID, BATCHMEMBERSHIPDUESSOLICITCODE.BATCHMEMBERSHIPDUESID order by BATCHMEMBERSHIPDUESSOLICITCODE.STARTDATE desc) as ROWNUMBER,
        BATCHMEMBERSHIPDUESSOLICITCODE.ID as BATCHMEMBERSHIPDUESSOLICITCODEID,
        INPUT.NEW_STARTDATE
      from @INPUT INPUT
        inner join dbo.BATCHMEMBERSHIPDUESSOLICITCODE on INPUT.SOLICITCODEID = BATCHMEMBERSHIPDUESSOLICITCODE.SOLICITCODEID
        inner join dbo.BATCHMEMBERSHIPDUES on BATCHMEMBERSHIPDUES.ID = BATCHMEMBERSHIPDUESSOLICITCODE.BATCHMEMBERSHIPDUESID
        inner join dbo.SOLICITCODE on BATCHMEMBERSHIPDUESSOLICITCODE.SOLICITCODEID = SOLICITCODE.ID
      where
        BATCHMEMBERSHIPDUES.BILLTOCONSTITUENTID = @BILLTOCONSTITUENTID
        and SOLICITCODE.CONSENTCODE in (1, 2) -- EU / Advanced Consent

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

  return 0;
end