UFN_REVENUESPLIT_GETDISQUALIFICATIONS

            CREATE function dbo.UFN_REVENUESPLIT_GETDISQUALIFICATIONS
            (
                @REVENUESPLITID uniqueidentifier,
                @RETURNALLDISQUALIFICATIONS bit
            )
            returns @DISQUALIFICATIONS table
            (
                CATEGORY nvarchar(255),
                DESCRIPTION nvarchar(max),
                SUBDESCRIPTION nvarchar(max)
            )
            as
            begin
                declare @RULES table(
                    ID uniqueidentifier, 
                    DisqType nvarchar(25)
                );

                declare @NUMDISQUALIFICATIONS integer = 0;

                declare @APPEALID uniqueidentifier;
                declare @PAYMENTMETHODCODE tinyint;
                declare @CREDITTYPECODEID uniqueidentifier;
                declare @TRANSACTIONTYPECODE tinyint;

                declare @REVENUEID uniqueidentifier;
                declare @DESIGNATIONID uniqueidentifier;
                declare @AMOUNT money;
                declare @TYPECODE tinyint;
                declare @APPLICATIONCODE tinyint;    

                declare @CONSTITUENTID uniqueidentifier;
                declare @EVENTID uniqueidentifier;
                declare @MEMBERSHIPID uniqueidentifier;
                declare @REGISTRANTID uniqueidentifier;
                declare @OTHERPAYMENTMETHODCODEID uniqueidentifier;

                -- Retrieve revenue/revenue application information-

                select
                    @REVENUEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
                    @APPEALID = REVENUE_EXT.APPEALID,
                    @CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID,
                    @TRANSACTIONTYPECODE = FINANCIALTRANSACTION.TYPECODE,
                    @DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID,
                    @TYPECODE = REVENUESPLIT_EXT.TYPECODE,
                    @APPLICATIONCODE = REVENUESPLIT_EXT.APPLICATIONCODE,        
                    @PAYMENTMETHODCODE = REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                    @CREDITTYPECODEID = CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID,
                    @OTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID
                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.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUEPAYMENTMETHOD.REVENUEID
                left outer join dbo.CREDITCARDPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
                left outer join dbo.OTHERPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = OTHERPAYMENTMETHODDETAIL.ID
                where FINANCIALTRANSACTIONLINEITEM.ID = @REVENUESPLITID
                    and FINANCIALTRANSACTION.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;

                -- Planned gift, matching gift, and grant can never be qualified, so if this split is 

                -- applied to any of them, automatically disqualify the split

                if @APPLICATIONCODE in (4, 6, 7, 8) -- Other, Planned gift, matching gift and grant

                    begin
                        insert into @DISQUALIFICATIONS
                        select
                            'Application',
                            REVENUESPLIT_EXT.APPLICATION,
                            ''
                        from dbo.FINANCIALTRANSACTIONLINEITEM
                        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                        where FINANCIALTRANSACTIONLINEITEM.ID = @REVENUESPLITID
                            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                            and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;

                        set @NUMDISQUALIFICATIONS = @NUMDISQUALIFICATIONS + @@ROWCOUNT;
                    end

                -- Retrieve current Disqualification rules

                insert into @RULES (ID, DisqType)
                select ID, BASEDON
                from dbo.UFN_GIFTAIDDISQ_GETRECORDS();

                -- short circuit if there are no rules

                if @@ROWCOUNT = 0
                    return;

                --********************************************************************************************************

                --Appeals, Credit Card Types, and Payment Methods are all at the REVENUE record level

                if @TRANSACTIONTYPECODE != 0 and (@NUMDISQUALIFICATIONS = 0 or @RETURNALLDISQUALIFICATIONS = 1)
                begin
                    insert into @DISQUALIFICATIONS
                    select
                        'Appeal',
                        APPEAL.NAME,
                        ''
                    from dbo.GIFTAIDDISQUALIFIEDBYAPPEAL 
                    inner join dbo.APPEAL
                    on GIFTAIDDISQUALIFIEDBYAPPEAL.ID = APPEAL.ID
                    where GIFTAIDDISQUALIFIEDBYAPPEAL.ID = @APPEALID;

                    set @NUMDISQUALIFICATIONS = @@ROWCOUNT;
                end

                -- Change payment method to account for other specific payment methods

                if (@NUMDISQUALIFICATIONS = 0 or @RETURNALLDISQUALIFICATIONS = 1) and @PAYMENTMETHODCODE != 10
                begin
                    insert into @DISQUALIFICATIONS
                    select
                        'Payment method',
                        DESCRIPTION,
                        ''
                    from dbo.GIFTAIDDISQUALIFIEDBYPAYMENTMETHOD
                    where PAYMENTMETHOD = @PAYMENTMETHODCODE;

                    set @NUMDISQUALIFICATIONS = @NUMDISQUALIFICATIONS + @@ROWCOUNT;
                end

                -- credit card a special case

                if @PAYMENTMETHODCODE = 2 and (@NUMDISQUALIFICATIONS = 0 or @RETURNALLDISQUALIFICATIONS = 1)
                begin
                    insert into @DISQUALIFICATIONS
                    select
                        'Payment method',
                        'Credit card',
                        CREDITTYPECODE.DESCRIPTION
                    from dbo.GIFTAIDDISQUALIFIEDBYCREDITCARDTYPE
                    inner join dbo.CREDITTYPECODE
                    on GIFTAIDDISQUALIFIEDBYCREDITCARDTYPE.ID = CREDITTYPECODE.ID
                    where GIFTAIDDISQUALIFIEDBYCREDITCARDTYPE.ID = @CREDITTYPECODEID;

                    set @NUMDISQUALIFICATIONS = @NUMDISQUALIFICATIONS + @@ROWCOUNT;                    
                end

                -- other payment method a special case

                if @PAYMENTMETHODCODE = 10 and (@NUMDISQUALIFICATIONS = 0 or @RETURNALLDISQUALIFICATIONS = 1)
                begin
                    insert into @DISQUALIFICATIONS
                    select
                        'Payment method',
                        'Other payment method',
                        OTHERPAYMENTMETHODCODE.DESCRIPTION
                    from dbo.GIFTAIDDISQUALIFIEDBYOTHERPAYMENTTYPE
                    inner join dbo.OTHERPAYMENTMETHODCODE
                    on GIFTAIDDISQUALIFIEDBYOTHERPAYMENTTYPE.ID = OTHERPAYMENTMETHODCODE.ID
                    where GIFTAIDDISQUALIFIEDBYOTHERPAYMENTTYPE.ID = @OTHERPAYMENTMETHODCODEID
                end

                -- Check if the appeal is disqualified for donations, pledges, recurring gifts and other.                

                if @TRANSACTIONTYPECODE = 0 and (@NUMDISQUALIFICATIONS = 0 or @RETURNALLDISQUALIFICATIONS = 1)
                begin
                    if @APPLICATIONCODE in (0, 1, 2, 3, 5, 18, 19) -- Donation, event registration, pledge, recurring gift, membership, membership add-on, membership installment plan

                    begin
                        -- Check global appeal

                        insert into @DISQUALIFICATIONS
                        select
                            'Appeal',
                            APPEAL.NAME,
                            ''
                        from dbo.GIFTAIDDISQUALIFIEDBYAPPEAL 
                        inner join dbo.APPEAL
                        on GIFTAIDDISQUALIFIEDBYAPPEAL.ID = APPEAL.ID
                        where GIFTAIDDISQUALIFIEDBYAPPEAL.ID = @APPEALID;

                        set @NUMDISQUALIFICATIONS = @NUMDISQUALIFICATIONS + @@ROWCOUNT;
                    end 
                end

                --check the designation since designation is in the REVENUESPLIT records

                if (@NUMDISQUALIFICATIONS = 0 or @RETURNALLDISQUALIFICATIONS = 1)
                begin
                    insert into @DISQUALIFICATIONS
                    select
                        'Designation',
                        DESIGNATION.NAME,
                        ''
                    from dbo.GIFTAIDDISQUALIFIEDBYDESIGNATION
                    inner join dbo.DESIGNATION
                    on GIFTAIDDISQUALIFIEDBYDESIGNATION.ID = DESIGNATION.ID
                    where GIFTAIDDISQUALIFIEDBYDESIGNATION.ID = @DESIGNATIONID;

                    set @NUMDISQUALIFICATIONS = @NUMDISQUALIFICATIONS + @@ROWCOUNT;
                end

                --only check these for payments

                if @TRANSACTIONTYPECODE = 0 and (@NUMDISQUALIFICATIONS = 0 or @RETURNALLDISQUALIFICATIONS = 1)
                begin
                    --check Event and Membership based upon the TYPECODE value

                    --get the Event ID

                    select @REGISTRANTID = REGISTRANTID from dbo.EVENTREGISTRANTPAYMENT where PAYMENTID = @REVENUESPLITID;
                    select @EVENTID = EVENTID from dbo.REGISTRANT where ID = @REGISTRANTID;

                    --get the Membership ID                    

                    select @MEMBERSHIPID = MEMBERSHIPPROGRAMID from dbo.MEMBERSHIP where ID = 
                    (select MEMBERSHIPID from dbo.MEMBERSHIPTRANSACTION where REVENUESPLITID = @REVENUESPLITID);

                    if @TYPECODE = 1 and @EVENTID is not null --event registration

                    begin  
                        insert into @DISQUALIFICATIONS
                        select
                            'Event',
                            EVENT.NAME,
                            ''
                        from dbo.GIFTAIDDISQUALIFIEDBYEVENT
                        inner join dbo.EVENT
                        on GIFTAIDDISQUALIFIEDBYEVENT.ID = EVENT.ID
                        where GIFTAIDDISQUALIFIEDBYEVENT.ID = @EVENTID;

                        set @NUMDISQUALIFICATIONS = @NUMDISQUALIFICATIONS + @@ROWCOUNT;
                    end
                    else if @TYPECODE = 2 and @MEMBERSHIPID is not null --membership

                    begin
                        insert into @DISQUALIFICATIONS
                        select
                            'Membership program',
                            MEMBERSHIPPROGRAM.NAME,
                            ''
                        from dbo.GIFTAIDDISQUALIFIEDBYMEMBERSHIPPROGRAM 
                        inner join dbo.MEMBERSHIPPROGRAM
                        on GIFTAIDDISQUALIFIEDBYMEMBERSHIPPROGRAM.ID = MEMBERSHIPPROGRAM.ID
                        where GIFTAIDDISQUALIFIEDBYMEMBERSHIPPROGRAM.ID = @MEMBERSHIPID;

                        set @NUMDISQUALIFICATIONS = @NUMDISQUALIFICATIONS + @@ROWCOUNT;
                    end
                end

                --check associated Campaigns

                if (@NUMDISQUALIFICATIONS = 0 or @RETURNALLDISQUALIFICATIONS = 1)
                begin
                    insert into @DISQUALIFICATIONS
                    select
                        'Campaign',
                        CAMPAIGN.NAME,
                        ''
                    from dbo.GIFTAIDDISQUALIFIEDBYCAMPAIGN
                    inner join dbo.CAMPAIGN
                    on GIFTAIDDISQUALIFIEDBYCAMPAIGN.ID = CAMPAIGN.ID
                    where GIFTAIDDISQUALIFIEDBYCAMPAIGN.ID in (
                        select CAMPAIGNID 
                        from dbo.REVENUESPLITCAMPAIGN 
                        where REVENUESPLITID = @REVENUESPLITID
                        );

                    set @NUMDISQUALIFICATIONS = @NUMDISQUALIFICATIONS + @@ROWCOUNT;
                end

                --check Revenue Category

                if (@NUMDISQUALIFICATIONS = 0 or @RETURNALLDISQUALIFICATIONS = 1)
                    insert into @DISQUALIFICATIONS
                    select
                        'Revenue category',
                        GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME,
                        ''
                    from dbo.GIFTAIDDISQUALIFIEDBYREVENUECATEGORY
                    inner join dbo.REVENUECATEGORY
                    on GIFTAIDDISQUALIFIEDBYREVENUECATEGORY.ID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID
                    inner join dbo.GLREVENUECATEGORYMAPPING
                    on GIFTAIDDISQUALIFIEDBYREVENUECATEGORY.ID = GLREVENUECATEGORYMAPPING.ID        
                    where REVENUECATEGORY.ID = @REVENUESPLITID;

                return;
            end