UFN_MEMBERSHIPDUESBATCH_PAYMENTVALIDFORDIRECTDEBIT_UK

        create function dbo.UFN_MEMBERSHIPDUESBATCH_PAYMENTVALIDFORDIRECTDEBIT_UK(@MEMBERSHIPDUESBATCHID uniqueidentifier,@EXISTINGMEMBERSHIPID uniqueidentifier,@MEMBERSHIPPROGRAMID uniqueidentifier)
        returns bit
        as
        begin
                            declare @SOURCEREVENUEID uniqueidentifier = dbo.UFN_MEMBERSHIP_GETPLEDGE(@EXISTINGMEMBERSHIPID)

                            if @SOURCEREVENUEID is null
                            begin
                                select @SOURCEREVENUEID = dbo.UFN_MEMBERSHIP_GETRECURRINGGIFT(@EXISTINGMEMBERSHIPID);
                            end

                            --Return false when there is no existing source revenue, this means there is no installmentplan or recurring gift

                            if @SOURCEREVENUEID is null
                                return 0;

                            declare @R bit;
                        --Note: This select will return a row 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 dbo.BATCHMEMBERSHIPDUES 
                        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = @SOURCEREVENUEID 
                        inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = FINANCIALTRANSACTION.ID
                        inner join dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT as REVENUESCHEDULEDIRECTDEBITPAYMENT on REVENUESCHEDULEDIRECTDEBITPAYMENT.ID = FINANCIALTRANSACTION.ID
                        where BATCHMEMBERSHIPDUES.ID = @MEMBERSHIPDUESBATCHID 
                            and BATCHMEMBERSHIPDUES.PAYMENTMETHODCODE = 3
                            and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 3
                            and FINANCIALTRANSACTION.DELETEDON is null
                            and    --This and clause has all the rejection rules

                                (
                                    REVENUESCHEDULEDIRECTDEBITPAYMENT.CONSTITUENTACCOUNTID <> BATCHMEMBERSHIPDUES.CONSTITUENTACCOUNTID -- The application has a different account from the payment

                                    or
                                    (
                                        dbo.UFN_PAPERLESSMANDATES_ENABLED_FORMEMBERSHIPPROGRAM(@MEMBERSHIPPROGRAMID) = 1 --Site is configured for paperless mandates but...

                                        and
                                        (
                                                --... the DDISOURCECODE is null

                                            REVENUESCHEDULEDIRECTDEBITPAYMENT.DDISOURCECODEID is null 
                                            or    --... the advance notice date is not set at all

                                            REVENUESCHEDULEDIRECTDEBITPAYMENT.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(FINANCIALTRANSACTION.ID), REVENUESCHEDULEDIRECTDEBITPAYMENT.PMADVANCENOTICESENTDATE) > cast(BATCHMEMBERSHIPDUES.DATE as date)
                                        )
                                    )
                                )    

  return @R;
  end