UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMELIGIBILITYSTATUS

            /* 
                Status codes:
                    0 - No valid declaration
                    1 - Declaration is not eligible for split
                    2 - Constituent has valid declaration for split
                    3 - Split is covenant gift
                    4 - Split is Gift Aid sponsorship
            */
            -- NOTE:  Any changes here should also be made in UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMELIGIBILITYSTATUS_INLINE.

            -- TODO:  At some point, this function should be changed to leverage UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMELIGIBILITYSTATUS_INLINE

            -- so the logic is consolidated but the change would be too disruptive to apply during 

            -- regression.

            CREATE function dbo.UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMELIGIBILITYSTATUS
            (
                @REVENUESPLITID uniqueidentifier
            )
            returns tinyint
            with execute as caller
            as begin
                declare @ELIGIBILITYSTATUSCODE tinyint;
                set @ELIGIBILITYSTATUSCODE = 0;

                declare @TRANSACTIONTYPECODE tinyint = 0;
                declare @ISCOVENANT bit;
                declare @ISSPONSORSHIP bit;
                select 
                    @TRANSACTIONTYPECODE = FINANCIALTRANSACTION.TYPECODE,
                    @ISCOVENANT = RSGA.ISCOVENANT,
                    @ISSPONSORSHIP = RSGA.ISSPONSORSHIP
                from dbo.FINANCIALTRANSACTION
                inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on FINANCIALTRANSACTION.ID = RS.FINANCIALTRANSACTIONID
                inner join dbo.REVENUESPLITGIFTAID RSGA on RSGA.ID = RS.ID
                where RSGA.ID = @REVENUESPLITID;

                if coalesce(@ISCOVENANT, 0) = 0 and coalesce(@ISSPONSORSHIP, 0) = 0
                begin
                    declare @DATE as datetime; 
                    declare    @CONSTITUENTID as uniqueidentifier;
                    declare    @DESIGNATIONID as uniqueidentifier;
                    declare    @REGISTRANTID as uniqueidentifier;
                    declare    @MEMBERSHIPID as uniqueidentifier;
                    select 
                        @DATE = case @TRANSACTIONTYPECODE 
                                    when 2 then 
                                        case REVENUESCHEDULE.STATUSCODE
                                            when 0 then 
                                                case
                                                    when REVENUESCHEDULE.NEXTTRANSACTIONDATE > REVENUESCHEDULE.ENDDATE then REVENUESCHEDULE.ENDDATE 
                                                    else REVENUESCHEDULE.NEXTTRANSACTIONDATE
                                                end
                                            else
                                                    coalesce((select DATE from dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO(FINANCIALTRANSACTION.ID, null)), getdate())
                                        end
                                    else FINANCIALTRANSACTION.DATE 
                                end,
                    @CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID,
                    @DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID,
                    @REGISTRANTID = EVENTREGISTRANTPAYMENT.REGISTRANTID,
                    @MEMBERSHIPID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
                    from dbo.FINANCIALTRANSACTIONLINEITEM 
          inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                    left join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID
                    left join dbo.MEMBERSHIPTRANSACTION on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
                    left join dbo.MEMBERSHIPLEVEL on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                    left join dbo.EVENTREGISTRANTPAYMENT on FINANCIALTRANSACTIONLINEITEM.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
                    where FINANCIALTRANSACTIONLINEITEM.ID = @REVENUESPLITID;

                    declare @ELIGIBLE tinyint;
                    set @ELIGIBLE = dbo.UFN_VALIDDECLARATION(@DATE,@CONSTITUENTID,@DESIGNATIONID,@REGISTRANTID,@MEMBERSHIPID)

                    if @ELIGIBLE = 2 
                        set @ELIGIBILITYSTATUSCODE = 2;
                    else if @ELIGIBLE = 1 
                        set @ELIGIBILITYSTATUSCODE = 1;
                end
                else if coalesce(@ISCOVENANT, 0) = 1
                begin
                    --covenant gifts and Gift Aid sponsorships are always eligible

                    set @ELIGIBILITYSTATUSCODE = 3;
                end
                else if coalesce(@ISSPONSORSHIP, 0) = 1
                begin
                    --covenant gifts and Gift Aid sponsorships are always eligible

                    set @ELIGIBILITYSTATUSCODE = 4;
                end

                return @ELIGIBILITYSTATUSCODE;                
            end