V_QUERY_REVENUESPLIT_GIFTAID_CALCULATED
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ||
| DECLINESGIFTAID | bit | yes | |
| ISSPONSORSHIP | bit | yes | |
| TAXCLAIMELIGIBILITY | varchar(20) | yes | |
| TAXCLAIMNUMBER | nvarchar(10) | yes | |
| TAXCLAIMAMOUNT | money | yes | |
| QUALIFICATIONSTATUS | varchar(22) | ||
| ADDEDBY_USERNAME | nvarchar(128) | yes | |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | |
| DATEADDED | datetime | yes | |
| DATECHANGED | datetime | yes | |
| TSLONG | bigint | yes | |
| GROSSAMOUNT | money | yes | |
| CLAIMABLEGIFTAIDAMOUNT | money | yes | |
| RECEIVEDGIFTAIDAMOUNT | money | yes | |
| TRANSACTIONTAXCLAIMAMOUNT | money | yes | |
| ORGANIZATIONTAXCLAIMAMOUNT | money | yes | |
| TRANSACTIONGROSSAMOUNT | money | yes | |
| ORGANIZATIONGROSSAMOUNT | money | yes | |
| TRANSACTIONCLAIMABLEGIFTAIDAMOUNT | money | yes | |
| ORGANIZATIONCLAIMABLEGIFTAIDAMOUNT | money | yes | |
| TRANSACTIONRECEIVEDGIFTAIDAMOUNT | money | yes | |
| ORGANIZATIONRECEIVEDGIFTAIDAMOUNT | money | yes | |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | |
| BASEEXCHANGERATEID | uniqueidentifier | yes | |
| BASECURRENCYID | uniqueidentifier | yes | |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | |
| 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_REVENUESPLIT_GIFTAID_CALCULATED AS
select
REVENUESPLIT.ID,
REVENUESPLITGIFTAID.DECLINESGIFTAID,
REVENUESPLITGIFTAID.ISSPONSORSHIP,
case REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY
when 0 then 'No valid declaration'
when 1 then 'Not eligible'
when 2 then 'Valid declaration'
when 3 then 'Covenant gift'
when 4 then 'Gift Aid sponsorship'
end [TAXCLAIMELIGIBILITY],
REVENUESPLITGIFTAID.TAXCLAIMNUMBER,
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 0
when REVENUE.TYPECODE = 0
then coalesce(REVENUESPLITGIFTAID.TAXCLAIMAMOUNT,0)
when REVENUE.TYPECODE = 1
then coalesce(PLEDGETAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
else 0
end as [TAXCLAIMAMOUNT],
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 'Not qualified'
else 'Qualified for Gift Aid'
end as [QUALIFICATIONSTATUS],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
REVENUESPLITGIFTAID.DATEADDED,
REVENUESPLITGIFTAID.DATECHANGED,
REVENUESPLITGIFTAID.TSLONG,
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 RELIEFNOTEXPIRED.ID is not null then BASETAXCLAIMAMOUNT + TRANSITIONALTAXCLAIMAMOUNT
else coalesce(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],
case
when REVENUE.TYPECODE = 0 and
CONSTITUENT.ISGROUP = 0 and
CONSTITUENT.ISORGANIZATION = 0 and
TAXCLAIMNUMBER = '' and -- Ensure the split hasn't already been received
REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (0, 1) and
REVENUESPLITGIFTAID.DECLINESGIFTAID = 0 and
RULES_STATUS = 1 and
ATTRIBUTES_STATUS = 1
then REVENUESPLITGIFTAID.TAXCLAIMAMOUNT
else 0
end as CLAIMABLEGIFTAIDAMOUNT,
case
when TAXCLAIMNUMBER <> '' then REVENUESPLITGIFTAID.TAXCLAIMAMOUNT
else 0
end as RECEIVEDGIFTAIDAMOUNT,
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 0
when REVENUE.TYPECODE = 0
then REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT
when REVENUE.TYPECODE = 1
then coalesce(PLEDGETAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
else 0
end as [TRANSACTIONTAXCLAIMAMOUNT],
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 0
when REVENUE.TYPECODE = 0
then REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT
when REVENUE.TYPECODE = 1
then coalesce(PLEDGETAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
else 0
end as [ORGANIZATIONTAXCLAIMAMOUNT],
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 + REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT
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],
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
then REVENUESPLIT.ORGAMOUNT + REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT
when REVENUE.TYPECODE = 1
then REVENUESPLIT.ORGAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then REVENUESPLIT.ORGAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
else REVENUESPLIT.ORGAMOUNT
end as [ORGANIZATIONGROSSAMOUNT],
case
when REVENUE.TYPECODE = 0 and
CONSTITUENT.ISGROUP = 0 and
CONSTITUENT.ISORGANIZATION = 0 and
TAXCLAIMNUMBER = '' and -- Ensure the split hasn't already been received
REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (0, 1) and
REVENUESPLITGIFTAID.DECLINESGIFTAID = 0 and
RULES_STATUS = 1 and
ATTRIBUTES_STATUS = 1
then REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT
else 0
end as TRANSACTIONCLAIMABLEGIFTAIDAMOUNT,
case
when REVENUE.TYPECODE = 0 and
CONSTITUENT.ISGROUP = 0 and
CONSTITUENT.ISORGANIZATION = 0 and
TAXCLAIMNUMBER = '' and -- Ensure the split hasn't already been received
REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (0, 1) and
REVENUESPLITGIFTAID.DECLINESGIFTAID = 0 and
RULES_STATUS = 1 and
ATTRIBUTES_STATUS = 1
then REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT
else 0
end as ORGANIZATIONCLAIMABLEGIFTAIDAMOUNT,
case
when TAXCLAIMNUMBER <> '' then REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT
else 0
end as TRANSACTIONRECEIVEDGIFTAIDAMOUNT,
case
when TAXCLAIMNUMBER <> '' then REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT
else 0
end as ORGANIZATIONRECEIVEDGIFTAIDAMOUNT,
coalesce(REVENUESPLITGIFTAID.ORGANIZATIONEXCHANGERATEID,REVENUE.ORGEXCHANGERATEID) as ORGANIZATIONEXCHANGERATEID,
coalesce(REVENUESPLITGIFTAID.BASEEXCHANGERATEID,REVENUE.BASEEXCHANGERATEID) as BASEEXCHANGERATEID,
coalesce(REVENUESPLITGIFTAID.BASECURRENCYID,REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) as BASECURRENCYID,
coalesce(REVENUESPLITGIFTAID.TRANSACTIONCURRENCYID,REVENUE.TRANSACTIONCURRENCYID) as TRANSACTIONCURRENCYID,
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
then REVENUESPLIT.BASEAMOUNT + REVENUESPLITGIFTAID.TAXCLAIMAMOUNT
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 [POTENTIALGROSSAMOUNT],
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
then REVENUESPLIT.TRANSACTIONAMOUNT + REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT
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 [TRANSACTIONPOTENTIALGROSSAMOUNT],
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
then REVENUESPLIT.ORGAMOUNT + REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT
when REVENUE.TYPECODE = 1
then REVENUESPLIT.ORGAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then REVENUESPLIT.ORGAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
else REVENUESPLIT.ORGAMOUNT
end as [ORGANIZATIONPOTENTIALGROSSAMOUNT]
/*#EXTENSION*/
from
dbo.FINANCIALTRANSACTIONLINEITEM 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.ID = REVENUE_EXT.ID
inner join dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
left outer join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
left join dbo.UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMELIGIBILITYSTATUS_INLINE() as REVENUESPLITGIFTAIDELIGIBILITY on REVENUESPLIT.ID = REVENUESPLITGIFTAIDELIGIBILITY.REVENUESPLITID
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 RELIEFNOTEXPIRED on RELIEFNOTEXPIRED.ID = REVENUESPLITGIFTAID.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on ADDEDBY.ID = REVENUESPLITGIFTAID.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on CHANGEDBY.ID = REVENUESPLITGIFTAID.CHANGEDBYID;