UFN_REVENUESPLITGIFTAID_GETTRANSITIONALRELIEFNOTEXPIRED

            -- Note: Changes made here may also need to be made to UFN_REVENUESPLITGIFTAID_GETCHARITYCLASSIFICATION and UFN_CHARITYCLAIMREFERENCENUMBER_GETBYSITE

            CREATE function dbo.UFN_REVENUESPLITGIFTAID_GETTRANSITIONALRELIEFNOTEXPIRED()
            returns table
            as
            return
            (
                select
                    REVENUESPLITGIFTAID.ID
                from dbo.REVENUESPLITGIFTAID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUESPLITGIFTAID.ID = FINANCIALTRANSACTIONLINEITEM.ID
                inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                cross apply
                (
                    select
                        case 
                            when CLASSIFICATIONCODE = 0 then case 
                                                                -- cast(cast(datepart(yyyy, getdate()) as nvarchar(4)) + '-04-05' as datetime) is the ENDOFTAXYEAR date

                                                                when cast(cast(getdate() as date) as datetime) <= cast(cast(datepart(yyyy, getdate()) as nvarchar(4)) + '-04-05' as datetime) then cast((datepart(yyyy, getdate()) - 3) as nvarchar(4)) + '-04-06'
                                                                else cast(cast((datepart(yyyy, getdate()) - 2) as nvarchar(4)) + '-04-06' as datetime)
                                                            end
                            else dateadd(year, -2, case when object_id(N'dbo.GLFISCALPERIOD', N'U') is not null 
                                                                and (select 1 
                                                                        from dbo.INSTALLEDPRODUCTLIST with (nolock)
                                                                        where ID = '0e85c527-e6e9-4c5f-a8e8-105fd0e18fe7'
                                                                            and LEN(EXPIREDATE) = 8
                                                                            and getdate() <= convert(datetime, substring(EXPIREDATE,1,4) + '-' + substring(EXPIREDATE,5,2) + '-' + substring(EXPIREDATE,7,2) + 'T00:00:00')) = 1
                                                            then (select MIN(STARTDATE) 
                                                                    from dbo.GLFISCALPERIOD 
                                                                    where CAST(getdate() as date) between STARTDATE and ENDDATE)
                                                            else (select dateadd(month, (FISCALYEARFINALMONTH + 12 - month(getdate())) % 12 - 11, cast(cast(GETDATE() + 1 - day(getdate()) as date) as datetime))
                                                                    from dbo.INSTALLATIONINFO where ID = 1)
                                                            end)
                        end as CUTOFFDATE
                    from
                    (
                        select top 1 CLASSIFICATIONCODE
                        from 
                        (
                            select 
                                CLASSIFICATIONCODE,
                                1 as [ORDER]
                            from dbo.CHARITYCLAIMREFERENCENUMBERSITE
                            inner join dbo.CHARITYCLAIMREFERENCENUMBER on CHARITYCLAIMREFERENCENUMBERSITE.CHARITYCLAIMREFERENCENUMBERID = CHARITYCLAIMREFERENCENUMBER.ID
                            inner join dbo.UFN_SITEID_MAPFROM_DESIGNATIONID_BULK() SITEFROMDESIGNATION on CHARITYCLAIMREFERENCENUMBERSITE.SITEID = SITEFROMDESIGNATION.SITEID
                            where
                                REVENUESPLIT_EXT.DESIGNATIONID = SITEFROMDESIGNATION.ID and
                                FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1 and
                                FINANCIALTRANSACTIONLINEITEM.DELETEDON is null

                            union all

                            select top 1
                                CLASSIFICATIONCODE,
                                2 as [ORDER]
                            from dbo.CHARITYCLAIMREFERENCENUMBER
                            where
                                not exists    (    select 1
                                                from dbo.CHARITYCLAIMREFERENCENUMBERSITE
                                                where
                                                    CHARITYCLAIMREFERENCENUMBERSITE.CHARITYCLAIMREFERENCENUMBERID = CHARITYCLAIMREFERENCENUMBER.ID
                                            )
                        ) as CLASSIFICATIONCODE
                        order by [ORDER]
                    ) as CHARITYCLASSIFICATION
                ) as CUTOFFDATETABLE
                where
                    (
                        (
                            REVENUESPLITGIFTAID.INCLUDETRANSITIONALAMOUNTCODE = 0 and -- INCLUDETRANSITIONALAMOUNTCODE of 0 indicates calculate

                            cast(FINANCIALTRANSACTION.DATE as datetime) >= CUTOFFDATETABLE.CUTOFFDATE) or
                        REVENUESPLITGIFTAID.INCLUDETRANSITIONALAMOUNTCODE = 1 -- INCLUDETRANSITIONALAMOUNTCODE of 1 indicates include

                    ) and
                    REVENUESPLITGIFTAID.INCLUDETRANSITIONALAMOUNTCODE <> 2 -- INCLUDETRANSITIONALAMOUNTCODE of 2 indicates exclude

            )