USP_DATALIST_RULESTHATDISQUALIFYREVENUEAPPLICATION
This datalist returns a list of the gift aid disqualification rules applicable to the given revenue application.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUESPLITID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_RULESTHATDISQUALIFYREVENUEAPPLICATION
(
@REVENUESPLITID uniqueidentifier
)
as
declare @RULES table(
ID uniqueidentifier,
DisqType nvarchar(25)
);
declare @results table (
CATEGORY nvarchar(255),
DESCRIPTION nvarchar(max)
);
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;
-- Retrieve revenue/revenue application information
select
@REVENUEID = REVENUESPLIT.REVENUEID,
@APPEALID = REVENUE.APPEALID,
@CONSTITUENTID = REVENUE.CONSTITUENTID,
@TRANSACTIONTYPECODE = REVENUE.TRANSACTIONTYPECODE,
@DESIGNATIONID = REVENUESPLIT.DESIGNATIONID,
@TYPECODE = REVENUESPLIT.TYPECODE,
@APPLICATIONCODE = REVENUESPLIT.APPLICATIONCODE,
@PAYMENTMETHODCODE = REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
@CREDITTYPECODEID = CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID
from dbo.REVENUESPLIT
inner join dbo.REVENUE
on REVENUESPLIT.REVENUEID = REVENUE.ID
inner join dbo.REVENUEPAYMENTMETHOD
on REVENUESPLIT.REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID
left outer join dbo.CREDITCARDPAYMENTMETHODDETAIL
on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
where REVENUESPLIT.ID = @REVENUESPLITID;
-- Retrieve current Disqualification rules
insert into @RULES(ID,DisqType)
select ID,BASEDON
from dbo.UFN_GIFTAIDDISQ_GETRECORDS();
--********************************************************************************************************
--Appeals, Credit Card Types, and Payment Methods are all at the REVENUE record level
if @TRANSACTIONTYPECODE != 0
--if exists(select ID from @RULES where ID = @APPEALID and DisqType = 'Appeal')
insert into @results
select
'Appeal',
APPEAL.NAME
from dbo.GIFTAIDDISQUALIFIEDBYAPPEAL
inner join dbo.APPEAL
on GIFTAIDDISQUALIFIEDBYAPPEAL.ID = APPEAL.ID
where GIFTAIDDISQUALIFIEDBYAPPEAL.ID = @APPEALID;
-- no reason to test if exists...if doesn't exist, insert will not do anything...
--if exists(select ID from dbo.GIFTAIDDISQUALIFIEDBYPAYMENTMETHOD where PAYMENTMETHOD = @PAYMENTMETHODCODE)
insert into @results
select
'Payment method',
DESCRIPTION
from dbo.GIFTAIDDISQUALIFIEDBYPAYMENTMETHOD
where PAYMENTMETHOD = @PAYMENTMETHODCODE;
-- credit card a special case
if @PAYMENTMETHODCODE = 2
insert into @results
select
'Credit card type',
CREDITTYPECODE.DESCRIPTION
from dbo.GIFTAIDDISQUALIFIEDBYCREDITCARDTYPE
inner join dbo.CREDITTYPECODE
on GIFTAIDDISQUALIFIEDBYCREDITCARDTYPE.ID = CREDITTYPECODE.ID
where GIFTAIDDISQUALIFIEDBYCREDITCARDTYPE.ID = @CREDITTYPECODEID;
--if exists(select ID from @RULES where DisqType = 'CreditCardType' and ID = @CREDITTYPECODEID)
-- set @STATUS = 0; --Credit Card Type is disqualified
--If this record is a payment, determine where to look for the appeal. Donations, Other, and Membership payments should use
-- the global appeal specified in the mailing section(the one passed in by the payment to this procedure). If it is on a
-- Recurring Gift, Pledge, or Event Registration, then use the appeal associated with that record. Event and membership
-- checks are placed below, with the other checks for them. 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 @TRANSACTIONTYPECODE = 0
begin
if @APPLICATIONCODE = 0
begin
--donation - check global appeal
insert into @results
select
'Appeal',
APPEAL.NAME
from dbo.GIFTAIDDISQUALIFIEDBYAPPEAL
inner join dbo.APPEAL
on GIFTAIDDISQUALIFIEDBYAPPEAL.ID = APPEAL.ID
where GIFTAIDDISQUALIFIEDBYAPPEAL.ID = @APPEALID;
end
else if @APPLICATIONCODE = 2
begin
--pledge - check pledge's appeal
insert into @results
select
'Appeal',
APPEAL.NAME
from dbo.GIFTAIDDISQUALIFIEDBYAPPEAL
inner join dbo.APPEAL
on GIFTAIDDISQUALIFIEDBYAPPEAL.ID = APPEAL.ID
where GIFTAIDDISQUALIFIEDBYAPPEAL.ID = (
select APPEALID
from dbo.REVENUE where ID = (
select PLEDGEID
from dbo.INSTALLMENTSPLITPAYMENT
where PAYMENTID = @REVENUESPLITID
)
);
end
else if @APPLICATIONCODE = 3
begin
--recurring gift - check recurring gift's appeal
insert into @results
select
'Appeal',
APPEAL.NAME
from dbo.GIFTAIDDISQUALIFIEDBYAPPEAL
inner join dbo.APPEAL
on GIFTAIDDISQUALIFIEDBYAPPEAL.ID = APPEAL.ID
where GIFTAIDDISQUALIFIEDBYAPPEAL.ID = (
select APPEALID
from dbo.REVENUE where ID = (
select SOURCEREVENUEID
from dbo.RECURRINGGIFTACTIVITY
where PAYMENTREVENUEID = @REVENUESPLITID
)
)
end
else if @APPLICATIONCODE = 4
begin
--other - check global appeal
insert into @results
select
'Appeal',
APPEAL.NAME
from dbo.GIFTAIDDISQUALIFIEDBYAPPEAL
inner join dbo.APPEAL
on GIFTAIDDISQUALIFIEDBYAPPEAL.ID = APPEAL.ID
where GIFTAIDDISQUALIFIEDBYAPPEAL.ID = @APPEALID;
end
-- Other applications (Planned gift, Matching gift, grant) are automatically disqualified
-- no need to return application type as a disqualification reason
-- also, the link to the page that uses this datalist should not be visible
end
--check Designation and its associated appeals first, since Designation is in the REVENUESPLIT records
insert into @results
select
'Designation',
DESIGNATION.NAME
from dbo.GIFTAIDDISQUALIFIEDBYDESIGNATION
inner join dbo.DESIGNATION
on GIFTAIDDISQUALIFIEDBYDESIGNATION.ID = DESIGNATION.ID
where GIFTAIDDISQUALIFIEDBYDESIGNATION.ID = @DESIGNATIONID;
--if the designation is not disqualified, see if any of its appeals are, which would disqualify this split
insert into @results
select
'Appeal',
APPEAL.NAME
from dbo.GIFTAIDDISQUALIFIEDBYAPPEAL
inner join dbo.APPEAL
on GIFTAIDDISQUALIFIEDBYAPPEAL.ID = APPEAL.ID
where GIFTAIDDISQUALIFIEDBYAPPEAL.ID in (
select APPEALID
from dbo.APPEALDESIGNATION
where DESIGNATIONID = @DESIGNATIONID
)
--only check these for payments
if @TRANSACTIONTYPECODE = 0
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 @results
select
'Event',
EVENT.NAME
from dbo.GIFTAIDDISQUALIFIEDBYEVENT
inner join dbo.EVENT
on GIFTAIDDISQUALIFIEDBYEVENT.ID = EVENT.ID
where GIFTAIDDISQUALIFIEDBYEVENT.ID = @EVENTID;
--check appeals on events to try to disqualify the record
insert into @results
select
'Appeal',
APPEAL.NAME
from dbo.GIFTAIDDISQUALIFIEDBYAPPEAL
inner join dbo.APPEAL
on GIFTAIDDISQUALIFIEDBYAPPEAL.ID = APPEAL.ID
where GIFTAIDDISQUALIFIEDBYAPPEAL.ID = (
select APPEALID
from dbo.EVENT
where ID = @EVENTID
)
end
else if @TYPECODE = 2 and @MEMBERSHIPID is not null --membership
begin
--check the global appeal, as memberships do not have an associated appeal
insert into @results
select
'Appeal',
APPEAL.NAME
from dbo.GIFTAIDDISQUALIFIEDBYAPPEAL
inner join dbo.APPEAL
on GIFTAIDDISQUALIFIEDBYAPPEAL.ID = APPEAL.ID
where GIFTAIDDISQUALIFIEDBYAPPEAL.ID = @APPEALID;
insert into @results
select
'Membership program',
MEMBERSHIPPROGRAM.NAME
from dbo.GIFTAIDDISQUALIFIEDBYMEMBERSHIPPROGRAM
inner join dbo.MEMBERSHIPPROGRAM
on GIFTAIDDISQUALIFIEDBYMEMBERSHIPPROGRAM.ID = MEMBERSHIPPROGRAM.ID
where GIFTAIDDISQUALIFIEDBYMEMBERSHIPPROGRAM.ID = @MEMBERSHIPID;
end
end
--check associated Campaigns
insert into @results
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
);
--check Revenue Category
insert into @results
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;
select
CATEGORY,
DESCRIPTION
from @results
order by CATEGORY;