V_QUERY_REVENUEMARKETING
Revenue Appeal query for marketing integration.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | yes | Constituent System ID |
| APPEALID | uniqueidentifier | yes | Appeal System ID |
| APPEALNAME | nvarchar(100) | Appeal Name | |
| APPEALDESCRIPTION | nvarchar(255) | Appeal Description | |
| SOURCECODE | nvarchar(50) | Source code | |
| AMOUNT | money | Amount | |
| DATE | date | yes | Date |
| MAILINGID | uniqueidentifier | yes | Mailing system record ID |
| MAILING | nvarchar(100) | yes | Mailing |
| FINDERNUMBER | bigint | Finder number | |
| DONOTACKNOWLEDGE | bit | Do not acknowledge | |
| DONOTRECEIPT | bit | Do not receipt | |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| TSLONG | bigint | yes | Timestamp value |
| ACCOUNTSYSTEM | nvarchar(50) | yes | Account system |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
| TRANSACTIONCURRENCYID | uniqueidentifier | Transaction currency ID | |
| TRANSACTIONAMOUNT | money | Amount (transaction currency) | |
| ORGANIZATIONAMOUNT | money | Amount (organization currency) | |
| LOOKUPID | nvarchar(100) | yes | Lookup ID |
| TRANSACTIONTYPE | nvarchar(27) | yes | Transaction type |
| PDACCOUNTSYSTEMID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/30/2015 1:01:49 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_REVENUEMARKETING AS
select
[FINANCIALTRANSACTION].[ID],
[FINANCIALTRANSACTION].[CONSTITUENTID] as [CONSTITUENTID],
[REVENUE_EXT].[APPEALID] as [APPEALID],
isnull([APPEAL].[NAME], '') as [APPEALNAME],
isnull([APPEAL].[DESCRIPTION], '') as [APPEALDESCRIPTION],
[REVENUE_EXT].[SOURCECODE] as [SOURCECODE],
/*###BEGIN_BASEAMOUNT###*/[FINANCIALTRANSACTION].[BASEAMOUNT]/*###END_BASEAMOUNT###*/ as [AMOUNT],
cast([FINANCIALTRANSACTION].[DATE] as date) as [DATE],
[REVENUE_EXT].[MAILINGID],
(select [NAME] from dbo.[MKTSEGMENTATION] where [ID] = [REVENUE_EXT].[MAILINGID]) as [MAILING],
[REVENUE_EXT].[FINDERNUMBER],
[REVENUE_EXT].[DONOTACKNOWLEDGE],
[REVENUE_EXT].[DONOTRECEIPT],
[FINANCIALTRANSACTION].[DATEADDED],
[FINANCIALTRANSACTION].[DATECHANGED],
[ADDEDBY].[APPLICATIONNAME] as [ADDEDBY_APPLICATION],
[ADDEDBY].[USERNAME] as [ADDEDBY_USERNAME],
[CHANGEDBY].[APPLICATIONNAME] as [CHANGEDBY_APPLICATION],
[CHANGEDBY].[USERNAME] as [CHANGEDBY_USERNAME],
[FINANCIALTRANSACTION].[TSLONG],
[PDACCOUNTSYSTEM].[NAME] as [ACCOUNTSYSTEM],
[CURRENCYSET].[BASECURRENCYID],
[FINANCIALTRANSACTION].[TRANSACTIONCURRENCYID],
/*###BEGIN_TRANSACTIONAMOUNT###*/[FINANCIALTRANSACTION].[TRANSACTIONAMOUNT]/*###END_TRANSACTIONAMOUNT###*/ as [TRANSACTIONAMOUNT],
/*###BEGIN_ORGAMOUNT###*/[FINANCIALTRANSACTION].[ORGAMOUNT]/*###END_ORGAMOUNT###*/ as [ORGANIZATIONAMOUNT],
[FINANCIALTRANSACTION].[CALCULATEDUSERDEFINEDID] as [LOOKUPID],
[FINANCIALTRANSACTION].[TYPE] as [TRANSACTIONTYPE],
[FINANCIALTRANSACTION].[PDACCOUNTSYSTEMID]
/*#EXTENSION*/
from dbo.[FINANCIALTRANSACTION]
inner join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [FINANCIALTRANSACTION].[ID]
left outer join dbo.[PDACCOUNTSYSTEM] on [PDACCOUNTSYSTEM].[ID] = [FINANCIALTRANSACTION].[PDACCOUNTSYSTEMID]
left outer join dbo.[CURRENCYSET] on [CURRENCYSET].[ID] = [PDACCOUNTSYSTEM].[CURRENCYSETID]
left outer join dbo.[APPEAL] on [REVENUE_EXT].[APPEALID] = [APPEAL].[ID]
left outer join dbo.[CHANGEAGENT] as [ADDEDBY] on [ADDEDBY].[ID] = [FINANCIALTRANSACTION].[ADDEDBYID]
left outer join dbo.[CHANGEAGENT] as [CHANGEDBY] on [CHANGEDBY].[ID] = [FINANCIALTRANSACTION].[CHANGEDBYID]
/*###JOIN_ORDERDONATIONS_CTE###*/
/*###FILTER_TRUNCATION###*/
where [FINANCIALTRANSACTION].[DELETEDON] is null
and
(
(
[FINANCIALTRANSACTION].[TYPECODE] in (0, 5) --Payment or Order
and
exists(
select *
from dbo.[REVENUESPLIT_EXT]
inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [REVENUESPLIT_EXT].[ID]
where [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [FINANCIALTRANSACTION].[ID]
and [REVENUESPLIT_EXT].[APPLICATIONCODE] in (0, 1, 3, 5) --Donation, Event registration, Recurring gift, Membership
)
)
or
(
[FINANCIALTRANSACTION].[TYPECODE] in (1, 3) --Pledge, Matching gift claim
and
exists(
select *
from dbo.[REVENUESPLIT_EXT]
inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [REVENUESPLIT_EXT].[ID]
where [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [FINANCIALTRANSACTION].[ID]
and [REVENUESPLIT_EXT].[APPLICATIONCODE] = 0 --Donation
)
)
or
(
[FINANCIALTRANSACTION].[TYPECODE] = 6 --Grant award
and
exists(
select *
from dbo.[REVENUESPLIT_EXT]
inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [REVENUESPLIT_EXT].[ID]
where [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [FINANCIALTRANSACTION].[ID]
and [REVENUESPLIT_EXT].[APPLICATIONCODE] = 8 --Grant award
)
)
)