V_QUERY_REVENUE_GIFTAID_CALCULATED
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ||
| GROSSAMOUNT | money | yes | |
| CLAIMABLEGIFTAIDAMOUNT | money | yes | |
| RECEIVEDGIFTAIDAMOUNT | money | yes | |
| TRANSACTIONGROSSAMOUNT | money | yes | |
| ORGANIZATIONGROSSAMOUNT | money | yes | |
| TRANSACTIONCLAIMABLEGIFTAIDAMOUNT | money | yes | |
| ORGANIZATIONCLAIMABLEGIFTAIDAMOUNT | money | yes | |
| TRANSACTIONRECEIVEDGIFTAIDAMOUNT | money | yes | |
| ORGANIZATIONRECEIVEDGIFTAIDAMOUNT | money | yes | |
| ADDEDBY_USERNAME | nvarchar(128) | yes | |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | |
| DATEADDED | datetime | ||
| DATECHANGED | datetime | ||
| TSLONG | bigint | yes | |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | |
| BASEEXCHANGERATEID | uniqueidentifier | yes | |
| BASECURRENCYID | uniqueidentifier | yes | |
| TRANSACTIONCURRENCYID | uniqueidentifier | ||
| POTENTIALGROSSAMOUNT | money | yes | |
| TRANSACTIONPOTENTIALGROSSAMOUNT | money | yes | |
| ORGANIZATIONPOTENTIALGROSSAMOUNT | money | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:28:57 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_REVENUE_GIFTAID_CALCULATED AS
select
REVENUE.ID,
sum(
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (2,3,4) or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.BASEAMOUNT
when REVENUE.TYPECODE = 0
then REVENUESPLIT.BASEAMOUNT + case
when REVENUESPLITGIFTAID.TRANSITIONALTAXCLAIMAMOUNT = 0 then REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT
when TRANSITIONALRELIEFEXPIRED.ID is not null then BASETAXCLAIMAMOUNT + TRANSITIONALTAXCLAIMAMOUNT
else coalesce(REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT,0)
end
when REVENUE.TYPECODE = 1
then REVENUESPLIT.BASEAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then REVENUESPLIT.BASEAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
else REVENUESPLIT.BASEAMOUNT
end) as [GROSSAMOUNT],
sum( case
when REVENUE.TYPECODE = 0 and
CONSTITUENT.ISGROUP = 0 and
CONSTITUENT.ISORGANIZATION = 0 and
-- Ensure the split hasn't already been received
TAXCLAIMNUMBER = '' and
-- Make sure the constituent is eligible
REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY in (2,3,4) and
REVENUESPLITGIFTAID.DECLINESGIFTAID = 0 and
RULES_STATUS = 1 and
ATTRIBUTES_STATUS = 1
then case
when REVENUESPLITGIFTAID.TRANSITIONALTAXCLAIMAMOUNT = 0 then REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT
when TRANSITIONALRELIEFEXPIRED.ID is not null then BASETAXCLAIMAMOUNT + TRANSITIONALTAXCLAIMAMOUNT
else REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT
end
else 0
end) as CLAIMABLEGIFTAIDAMOUNT,
sum( case
when TAXCLAIMNUMBER <> '' then case
when REVENUESPLITGIFTAID.TRANSITIONALTAXCLAIMAMOUNT = 0 then REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT
when TRANSITIONALRELIEFEXPIRED.ID is not null then BASETAXCLAIMAMOUNT + TRANSITIONALTAXCLAIMAMOUNT
else REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT
end
else 0
end) as RECEIVEDGIFTAIDAMOUNT,
sum(
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (2,3,4) or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.TRANSACTIONAMOUNT
when REVENUE.TYPECODE = 0
then REVENUESPLIT.TRANSACTIONAMOUNT + case
when REVENUESPLITGIFTAID.TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT = 0 then REVENUESPLITGIFTAID.TRANSACTIONBASETAXCLAIMAMOUNT
when TRANSITIONALRELIEFEXPIRED.ID is not null then TRANSACTIONBASETAXCLAIMAMOUNT + TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT
else REVENUESPLITGIFTAID.TRANSACTIONBASETAXCLAIMAMOUNT
end
when REVENUE.TYPECODE = 1
then REVENUESPLIT.TRANSACTIONAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then REVENUESPLIT.TRANSACTIONAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
else REVENUESPLIT.TRANSACTIONAMOUNT
end) as [TRANSACTIONGROSSAMOUNT],
sum(
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (2,3,4) or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.ORGAMOUNT
when REVENUE.TYPECODE = 0 and CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0
then REVENUESPLIT.ORGAMOUNT + case
when REVENUESPLITGIFTAID.ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT = 0 then REVENUESPLITGIFTAID.ORGANIZATIONBASETAXCLAIMAMOUNT
when TRANSITIONALRELIEFEXPIRED.ID is not null then ORGANIZATIONBASETAXCLAIMAMOUNT + ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT
else REVENUESPLITGIFTAID.ORGANIZATIONBASETAXCLAIMAMOUNT
end
when REVENUE.TYPECODE = 1 and CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0
then REVENUESPLIT.ORGAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2 and CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0
and ((RULES_STATUS & ATTRIBUTES_STATUS = 1) and DECLINESGIFTAID = 0) --Qualified for Gift Aid
then REVENUESPLIT.ORGAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
else REVENUESPLIT.ORGAMOUNT
end) as [ORGANIZATIONGROSSAMOUNT],
sum( case
when REVENUE.TYPECODE = 0 and
CONSTITUENT.ISGROUP = 0 and
CONSTITUENT.ISORGANIZATION = 0 and
-- Ensure the split hasn't already been received
TAXCLAIMNUMBER = '' and
-- Make sure the constituent is eligible
REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY in (2,3,4) and
REVENUESPLITGIFTAID.DECLINESGIFTAID = 0 and
RULES_STATUS = 1 and
ATTRIBUTES_STATUS = 1
then case
when REVENUESPLITGIFTAID.TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT = 0 then REVENUESPLITGIFTAID.TRANSACTIONBASETAXCLAIMAMOUNT
when TRANSITIONALRELIEFEXPIRED.ID is not null then TRANSACTIONBASETAXCLAIMAMOUNT + TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT
else REVENUESPLITGIFTAID.TRANSACTIONBASETAXCLAIMAMOUNT
end
else 0
end) as TRANSACTIONCLAIMABLEGIFTAIDAMOUNT,
sum( case
when REVENUE.TYPECODE = 0 and
CONSTITUENT.ISGROUP = 0 and
CONSTITUENT.ISORGANIZATION = 0 and
-- Ensure the split hasn't already been received
TAXCLAIMNUMBER = '' and
-- Make sure the constituent is eligible
REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY in (2,3,4) and
REVENUESPLITGIFTAID.DECLINESGIFTAID = 0 and
RULES_STATUS = 1 and
ATTRIBUTES_STATUS = 1
then case
when REVENUESPLITGIFTAID.ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT = 0 then REVENUESPLITGIFTAID.ORGANIZATIONBASETAXCLAIMAMOUNT
when TRANSITIONALRELIEFEXPIRED.ID is not null then ORGANIZATIONBASETAXCLAIMAMOUNT + ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT
else REVENUESPLITGIFTAID.ORGANIZATIONBASETAXCLAIMAMOUNT
end
else 0
end) as ORGANIZATIONCLAIMABLEGIFTAIDAMOUNT,
sum( case
when TAXCLAIMNUMBER <> '' then case
when REVENUESPLITGIFTAID.TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT = 0 then REVENUESPLITGIFTAID.TRANSACTIONBASETAXCLAIMAMOUNT
when TRANSITIONALRELIEFEXPIRED.ID is not null then TRANSACTIONBASETAXCLAIMAMOUNT + TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT
else REVENUESPLITGIFTAID.TRANSACTIONBASETAXCLAIMAMOUNT
end
else 0
end) as TRANSACTIONRECEIVEDGIFTAIDAMOUNT,
sum( case
when TAXCLAIMNUMBER <> '' then case
when REVENUESPLITGIFTAID.ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT = 0 then REVENUESPLITGIFTAID.ORGANIZATIONBASETAXCLAIMAMOUNT
when TRANSITIONALRELIEFEXPIRED.ID is not null then ORGANIZATIONBASETAXCLAIMAMOUNT + ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT
else REVENUESPLITGIFTAID.ORGANIZATIONBASETAXCLAIMAMOUNT
end
else 0
end) as ORGANIZATIONRECEIVEDGIFTAIDAMOUNT,
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
REVENUE.DATEADDED,
REVENUE.DATECHANGED,
REVENUE.TSLONG,
REVENUE.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
REVENUE.BASEEXCHANGERATEID,
isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) as BASECURRENCYID,
REVENUE.TRANSACTIONCURRENCYID,
sum( case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.BASEAMOUNT
when REVENUE.TYPECODE = 0 and CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0
then REVENUESPLIT.BASEAMOUNT + case
when REVENUESPLITGIFTAID.TRANSITIONALTAXCLAIMAMOUNT = 0 then REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT
when TRANSITIONALRELIEFEXPIRED.ID is not null then BASETAXCLAIMAMOUNT + TRANSITIONALTAXCLAIMAMOUNT
else REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT
end
when REVENUE.TYPECODE = 1 and CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0
then REVENUESPLIT.BASEAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2 and CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0
then REVENUESPLIT.BASEAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
else REVENUESPLIT.BASEAMOUNT
end) as [POTENTIALGROSSAMOUNT],
sum( case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.TRANSACTIONAMOUNT
when REVENUE.TYPECODE = 0 and CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0
then REVENUESPLIT.TRANSACTIONAMOUNT + case
when REVENUESPLITGIFTAID.TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT = 0 then REVENUESPLITGIFTAID.TRANSACTIONBASETAXCLAIMAMOUNT
when TRANSITIONALRELIEFEXPIRED.ID is not null then TRANSACTIONBASETAXCLAIMAMOUNT + TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT
else REVENUESPLITGIFTAID.TRANSACTIONBASETAXCLAIMAMOUNT
end
when REVENUE.TYPECODE = 1 and CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0
then REVENUESPLIT.TRANSACTIONAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2 and CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0
then REVENUESPLIT.TRANSACTIONAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
else REVENUESPLIT.TRANSACTIONAMOUNT
end) as [TRANSACTIONPOTENTIALGROSSAMOUNT],
sum( case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.ORGAMOUNT
when REVENUE.TYPECODE = 0 and CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0
then REVENUESPLIT.ORGAMOUNT + case
when REVENUESPLITGIFTAID.ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT = 0 then REVENUESPLITGIFTAID.ORGANIZATIONBASETAXCLAIMAMOUNT
when TRANSITIONALRELIEFEXPIRED.ID is not null then ORGANIZATIONBASETAXCLAIMAMOUNT + ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT
else REVENUESPLITGIFTAID.ORGANIZATIONBASETAXCLAIMAMOUNT
end
when REVENUE.TYPECODE = 1 and CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0
then REVENUESPLIT.ORGAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2 and CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0
then REVENUESPLIT.ORGAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
else REVENUESPLIT.ORGAMOUNT
end) as [ORGANIZATIONPOTENTIALGROSSAMOUNT]
/*#EXTENSION*/
from dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT
left join dbo.REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = REVENUE.ID
inner join dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
outer apply dbo.UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMELIGIBILITYSTATUS_INLINE_2(REVENUESPLIT.ID) as REVENUESPLITGIFTAIDELIGIBILITY
left join dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGETAXCLAIMAMOUNT_ALLCURRENCYTYPES() as PLEDGETAXCLAIMAMOUNT on REVENUESPLIT.ID = PLEDGETAXCLAIMAMOUNT.REVENUESPLITID
left join dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATERECURRINGGIFTTAXCLAIMAMOUNT_INLINE() as RECURRINGGIFTTAXCLAIMAMOUNT on REVENUESPLIT.ID = RECURRINGGIFTTAXCLAIMAMOUNT.REVENUESPLITID
left join dbo.UFN_REVENUESPLITGIFTAID_GETTRANSITIONALRELIEFNOTEXPIRED() as TRANSITIONALRELIEFEXPIRED on TRANSITIONALRELIEFEXPIRED.ID = REVENUESPLITGIFTAID.ID
left outer join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
left join dbo.CHANGEAGENT as [ADDEDBY] on ADDEDBY.ID = REVENUE.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on CHANGEDBY.ID = REVENUE.CHANGEDBYID
left join dbo.MEMBERSHIPTRANSACTION on REVENUESPLIT.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
left join dbo.MEMBERSHIPLEVEL on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
left join dbo.EVENTREGISTRANTPAYMENT on REVENUESPLIT.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
where
REVENUE.DELETEDON is null
and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.TYPECODE <> 1
group by REVENUE.ID,
[ADDEDBY].USERNAME,
[CHANGEDBY].USERNAME,
REVENUE.DATECHANGED,
REVENUE.DATEADDED,
REVENUE.DATECHANGED,
REVENUE.TSLONG,
REVENUE.ORGEXCHANGERATEID,
REVENUE.BASEEXCHANGERATEID,
isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID),
REVENUE.TRANSACTIONCURRENCYID