UFN_BATCHREVENUE_PAYMENTVALIDFORDIRECTDEBIT_UK

CREATE function dbo.UFN_BATCHREVENUE_PAYMENTVALIDFORDIRECTDEBIT_UK
(
  @BATCHREVENUEID uniqueidentifier
)
returns bit
as
begin
  declare @R bit;



  declare @SOURCEREVENUEID uniqueidentifier;
  select @SOURCEREVENUEID = BATCHREVENUEAPPLICATION.REVENUEID 
  from dbo.BATCHREVENUEAPPLICATION
  inner join dbo.BATCHREVENUE on BATCHREVENUE.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEID
  where BATCHREVENUE.ID = @BATCHREVENUEID;

  if @SOURCEREVENUEID is null 
  begin
    declare @APPINFO as nvarchar(60);
    set @APPINFO = (select APPLICATIONINFO from dbo.BATCHREVENUE where ID = @BATCHREVENUEID);
      if len(@APPINFO) > 3
      begin
        set @SOURCEREVENUEID = (select SINGLEAPPLICATIONID from dbo.UFN_REVENUEBATCH_PARSEAPPLICATIONINFO(@APPINFO));
      end
  end

  --Return zero if no commitment exists.

  if @SOURCEREVENUEID is null
    return 0;

  --Note: This select will return applications that are considered invalid. So the case that assigns to @R looks 

  --inverted. However, it's correct: if there are any rows returned then the @BATCHREVENUEID is not valid for UK direct debit

  select
    @R = case when count(1) > 0 then 0 else 1 end 
  from BATCHREVENUEAPPLICATION 
  inner join dbo.BATCHREVENUE on BATCHREVENUE.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEID
  inner join dbo.REVENUE on REVENUE.ID = BATCHREVENUEAPPLICATION.REVENUEID
  inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
  inner join dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT as [DD] on [DD].ID = REVENUE.ID
  where BATCHREVENUEAPPLICATION.BATCHREVENUEID = @BATCHREVENUEID 
    and BATCHREVENUE.PAYMENTMETHODCODE = 3
    and BATCHREVENUEAPPLICATION.APPLIED > 0
    and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 3
    and    --This and clause has all the rejection rules

      (
        [DD].CONSTITUENTACCOUNTID <> BATCHREVENUE.CONSTITUENTACCOUNTID -- The application has a different account from the payment

        or
        (
          dbo.UFN_PAPERLESSMANDATES_ENABLED_FORREVENUE(REVENUE.ID) = 1 --Site is configured for paperless mandates but...

          and
          (
              --... the DDISOURCECODE is null

            [DD].DDISOURCECODEID is null 
            or    --... the advance notice date is not set at all

            [DD].PMADVANCENOTICESENTDATE is null
            or --...the advance notice date is not in the right place

            --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

            dateadd(dd,dbo.UFN_PAPERLESSMANDATES_DAYSADVANCEDNOTICE_FORREVENUE(REVENUE.ID), [DD].PMADVANCENOTICESENTDATE) > cast(BATCHREVENUE.DATE as date)
          )
        )
      )    

  return @R;
end