UFN_CONSTITUENTRECOGNITION_CALCULATEFISCALEXPIRATIONDATE

        CREATE function dbo.UFN_CONSTITUENTRECOGNITION_CALCULATEFISCALEXPIRATIONDATE 
        (
            @RECOGNITIONPROGRAMID uniqueidentifier,
            @STARTDATE datetime
        )
        returns datetime
        as begin

            declare @CUTOFFDATEFORYEAR char(4);
            declare @EXPIRATIONDATE datetime;
            declare @MONTH nvarchar(2);
            declare @DAY nvarchar(2);
            declare @BACKDATEMEMBERSHIPS bit;
            declare @YEAROFSTARTDATE nvarchar(4);
            declare @YEAR nvarchar(4);
            declare @FULLCUTOFFDATE date;

            select 
              @CUTOFFDATEFORYEAR = CUTOFFDATEFORYEAR,
              @BACKDATEMEMBERSHIPS = BACKDATEMEMBERSHIPS
            from dbo.RECOGNITIONPROGRAM 
            where ID = @RECOGNITIONPROGRAMID;

            set @EXPIRATIONDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(@STARTDATE,1);

            --If set expiration enabled

            if @BACKDATEMEMBERSHIPS =1 
            begin
              set @MONTH = SUBSTRING(@CUTOFFDATEFORYEAR,1,2);
              set @DAY  = SUBSTRING(@CUTOFFDATEFORYEAR,3,2);

              --Determine the year of the startdate

              set @YEAROFSTARTDATE = YEAR(@STARTDATE);

              --Because we only get the month and day from the @CUTOFFDATEFORYEAR, add the year we get from startdate for now;

              --This may or may not be the right year for the cutoffdate depending on where it falls in the fiscal year.

              set @FULLCUTOFFDATE  = @YEAROFSTARTDATE + @MONTH + @DAY;

              --Now, get the actual year by looking at the fiscal periods of the fiscal year of the @STARTDATE and then

              --use the @FULLCUTOFFDATE from above to narrow it down to one fiscal period in which the cutoffdate will fall in to.

              select 
                @YEAR = YEAR(FP1.STARTDATE) 
              from dbo.GLFISCALPERIOD fp1
              inner join dbo.GLFISCALPERIOD FP2 on FP1.GLFISCALYEARID = FP2.GLFISCALYEARID
              where 
              @STARTDATE between FP2.STARTDATE and FP2.ENDDATE 
                 and (@FULLCUTOFFDATE between FP1.STARTDATE and FP1.ENDDATE 
                      OR dateadd(year, 1, @FULLCUTOFFDATE) between FP1.STARTDATE and FP1.ENDDATE 
                      OR dateadd(year, -1, @FULLCUTOFFDATE) between FP1.STARTDATE and FP1.ENDDATE)

              if @YEAR < @YEAROFSTARTDATE 
                set @EXPIRATIONDATE = dateadd(year, 1, @EXPIRATIONDATE);
              else if @YEAR = @YEAROFSTARTDATE and @MONTH < MONTH(@STARTDATE)
                set @EXPIRATIONDATE = dateadd(year, 1, @EXPIRATIONDATE);
              else if @YEAR = @YEAROFSTARTDATE and @MONTH = MONTH(@STARTDATE) and @DAY <= DAY(@STARTDATE)
                set @EXPIRATIONDATE = dateadd(year, 1, @EXPIRATIONDATE);
            end

            return @EXPIRATIONDATE;
        end