USP_MEMBERSHIPDUESBATCH_VALIDATE_SOLICITCODES

create procedure dbo.USP_MEMBERSHIPDUESBATCH_VALIDATE_SOLICITCODES
(
  @SOLICITCODES xml
)
as
begin
  if @SOLICITCODES is not null
  begin
    declare @SOLICITCODESTABLE table
    (
      [ID] uniqueidentifier,
      [SOLICITCODEID] [uniqueidentifier] NOT NULL,
      [STARTDATE] [datetime] NULL,
      [ENDDATE] [datetime] NULL
    );

    insert into @SOLICITCODESTABLE
    (
      ID,
      SOLICITCODEID,
      STARTDATE,
      ENDDATE
    )
    select
      case when ID is null then newid() else ID end,
      SOLICITCODEID,
      STARTDATE,
      ENDDATE
    from dbo.UFN_MEMBERSHIPDUESBATCH_GETSOLICITCODES_FROMITEMLISTXML(@SOLICITCODES);

    -- Check for solicit codes whose end date is before the start date

    if exists (select 1 from @SOLICITCODESTABLE where STARTDATE is not null and STARTDATE > ENDDATE)
      raiserror('BBERR_SOLICITCODES_STARTENDDATE', 13, 1);

    -- Check for records with the same solicit code and overlapping dates

    if exists (
      select 1
      from @SOLICITCODESTABLE SOLICITCODEONE
      inner join @SOLICITCODESTABLE SOLICITCODETWO on SOLICITCODEONE.ID <> SOLICITCODETWO.ID and SOLICITCODEONE.SOLICITCODEID = SOLICITCODETWO.SOLICITCODEID and
        dbo.UFN_DATES_AREDATESOVERLAPPING(SOLICITCODEONE.STARTDATE, SOLICITCODEONE.ENDDATE, SOLICITCODETWO.STARTDATE, SOLICITCODETWO.ENDDATE) = 1
      )
      raiserror('BBERR_SOLICITCODES_NOOVERLAPPINGDATES', 13, 1);
  end
end