UFN_REVENUESPLITGIFTAID_GIFTAIDEXPIRED

            CREATE function dbo.UFN_REVENUESPLITGIFTAID_GIFTAIDEXPIRED
            (
                @REVENUESPLITID uniqueidentifier
            )
            returns bit
            as
            begin
                declare @EXPIRED bit
                set @EXPIRED = 0

                declare @TRANSACTIONTYPECODE tinyint, @REVENUEDATE datetime, @TAXCLAIMNUMBER nvarchar(10)
                select 
                    @REVENUEDATE = cast(FINANCIALTRANSACTION.DATE as datetime),
                    @TRANSACTIONTYPECODE = FINANCIALTRANSACTION.TYPECODE,
                    @TAXCLAIMNUMBER = REVENUESPLITGIFTAID.TAXCLAIMNUMBER
                from dbo.FINANCIALTRANSACTIONLINEITEM
                inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                left join dbo.REVENUESPLITGIFTAID on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITGIFTAID.ID
                where FINANCIALTRANSACTIONLINEITEM.ID = @REVENUESPLITID
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTION.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1

                -- Make sure the revenue hasn't expired for Gift Aid support.  If the tax claim number is set

                -- then the split has been submitted already and so it won't have expired.  Also, only Payments

                -- are disqualified by this rule since they are only the transaction types submitted on the R68 report.

                if coalesce(@TAXCLAIMNUMBER, '') = '' and @TRANSACTIONTYPECODE = 0 -- Payment

                begin
                    declare @CLASSIFICATIONCODE tinyint
                    set @CLASSIFICATIONCODE = dbo.UFN_REVENUESPLITGIFTAID_GETCHARITYCLASSIFICATION(@REVENUESPLITID)

                    declare @CURRENTDATE datetime
                    set @CURRENTDATE = getdate()

                    declare @HOWFARBACK int
                    set @HOWFARBACK = 6

                    --New rules effective Apr-01-2010 change the claim limit from 6 to 4 years across the board

                    if @CURRENTDATE >= '2010-04-01'
                        set @HOWFARBACK = 4

                    declare @CUTOFFDATE datetime
                    if @CLASSIFICATIONCODE = 0 -- Charitable trusts

                    begin
                        -- Claims by trusts must be made no later than 5 years after April 5th following the end of 

                        -- the tax year (which is April 5th)                        

                        if datepart(m, @CURRENTDATE) < 4
                            -- Handle current date before April

                            set @CUTOFFDATE = cast((datepart(yyyy, @CURRENTDATE) - (@HOWFARBACK + 1)) as nvarchar(4)) + '-04-06'
            else if datepart(m, @CURRENTDATE) = 4 and datepart(d, @CURRENTDATE) < 6
              --Handle current date in April before the end of the tax year

              set @CUTOFFDATE = cast((datepart(yyyy, @CURRENTDATE) - (@HOWFARBACK + 1)) as nvarchar(4)) + '-04-06'            
                        else
                            -- Handle all other months

                            set @CUTOFFDATE = cast((datepart(yyyy, @CURRENTDATE) - @HOWFARBACK) as nvarchar(4)) + '-04-06'
                    end
                    else -- Other charities

                    begin
                        -- Claims must be made no later than 4 years after then end of the accounting period the revenue

                        -- belongs to

                        set @CUTOFFDATE = dateadd(year, -@HOWFARBACK, dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(getdate(), 0))
                    end

                    if @REVENUEDATE < @CUTOFFDATE
                        set @EXPIRED = 1
                end

                return @EXPIRED
            end