REVENUE
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ||
| CONSTITUENTID | uniqueidentifier | yes | |
| DATE | datetime | yes | |
| BATCHNUMBER | nvarchar(100) | ||
| POSTDATE | datetime | yes | |
| DONOTPOST | bit | ||
| DONOTRECEIPT | bit | ||
| RECEIPTAMOUNT | money | ||
| AMOUNT | money | ||
| SOURCECODE | nvarchar(50) | ||
| FINDERNUMBER | bigint | ||
| APPEALID | uniqueidentifier | yes | |
| MAILINGID | uniqueidentifier | yes | |
| CHANNELCODEID | uniqueidentifier | yes | |
| GIVENANONYMOUSLY | bit | ||
| DONOTACKNOWLEDGE | bit | ||
| BENEFITSWAIVED | bit | ||
| ADDEDBYID | uniqueidentifier | ||
| CHANGEDBYID | uniqueidentifier | ||
| DATEADDED | datetime | ||
| DATECHANGED | datetime | ||
| TS | timestamp | ||
| TSLONG | bigint | yes | |
| RECEIPTTYPECODE | tinyint | ||
| NEEDSRERECEIPT | bit | ||
| TRANSACTIONTYPECODE | tinyint | ||
| DESCRIPTION | nvarchar(700) | yes | |
| ELIGIBLEFORMATCHINGGIFTCLAIM | bit | ||
| CUSTOMIDENTIFIER | nvarchar(100) | ||
| SEQUENCEID | int | ||
| LOOKUPID | nvarchar(100) | yes | |
| ISREIMBURSABLE | bit | ||
| RECEIPTTYPE | nvarchar(12) | yes | |
| BASECURRENCYID | uniqueidentifier | yes | |
| ORGANIZATIONAMOUNT | money | ||
| TRANSACTIONAMOUNT | money | ||
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | |
| BASEEXCHANGERATEID | uniqueidentifier | yes | |
| TRANSACTIONTYPE | nvarchar(21) | yes |
Definition
Copy
CREATE view dbo.REVENUE with schemabinding
as
select
FINANCIALTRANSACTION.ID
,FINANCIALTRANSACTION.CONSTITUENTID
,cast(FINANCIALTRANSACTION.DATE as datetime) [DATE]
,REVENUE_EXT.BATCHNUMBER
,cast(FINANCIALTRANSACTION.POSTDATE as datetime) as POSTDATE
,isnull(cast(case FINANCIALTRANSACTION.POSTSTATUSCODE when 3 then 1 else 0 end as bit),'') as DONOTPOST
,REVENUE_EXT.DONOTRECEIPT
,REVENUE_EXT.RECEIPTAMOUNT
,FINANCIALTRANSACTION.BASEAMOUNT as AMOUNT
,REVENUE_EXT.SOURCECODE
,REVENUE_EXT.FINDERNUMBER
,REVENUE_EXT.APPEALID
,REVENUE_EXT.MAILINGID
,REVENUE_EXT.CHANNELCODEID
,REVENUE_EXT.GIVENANONYMOUSLY
,REVENUE_EXT.DONOTACKNOWLEDGE
,REVENUE_EXT.BENEFITSWAIVED
,FINANCIALTRANSACTION.ADDEDBYID
,FINANCIALTRANSACTION.CHANGEDBYID
,FINANCIALTRANSACTION.DATEADDED
,FINANCIALTRANSACTION.DATECHANGED
,FINANCIALTRANSACTION.TS
,FINANCIALTRANSACTION.TSLONG
,REVENUE_EXT.RECEIPTTYPECODE
,REVENUE_EXT.NEEDSRERECEIPT
,FINANCIALTRANSACTION.TYPECODE as TRANSACTIONTYPECODE
,cast('('+case FINANCIALTRANSACTION.TYPECODE when 0 then N'Payment' when 1 then N'Pledge' when 2 then N'Recurring gift' when 3 then N'Matching gift claim' when 4 then N'Planned gift' when 5 then N'Order' when 6 then N'Grant award' when 7 then N'Auction donation' when 8 then N'Donor challenge claim' when 9 then N'Pending gift' end+') '
+convert(nvarchar(20),FINANCIALTRANSACTION.BASEAMOUNT)+' '+isnull(REVENUEPAYMENTMETHOD.PAYMENTMETHOD, 'None')
+ ' - ' + CONSTITUENT.NAME as nvarchar(700)) as DESCRIPTION
,REVENUE_EXT.ELIGIBLEFORMATCHINGGIFTCLAIM
,FINANCIALTRANSACTION.USERDEFINEDID as CUSTOMIDENTIFIER
,FINANCIALTRANSACTION.SEQUENCEGENERATORID SEQUENCEID
,FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID as LOOKUPID
,REVENUE_EXT.ISREIMBURSABLE
,case RECEIPTTYPECODE when 0 then N'Per payment' when 1 then N'Consolidated' end as RECEIPTTYPE
,case when FINANCIALTRANSACTION.DELETEDON is null then isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) else null end as BASECURRENCYID
,FINANCIALTRANSACTION.ORGAMOUNT as ORGANIZATIONAMOUNT
,FINANCIALTRANSACTION.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT,
case when FINANCIALTRANSACTION.DELETEDON is null then FINANCIALTRANSACTION.TRANSACTIONCURRENCYID else null end as TRANSACTIONCURRENCYID ,
FINANCIALTRANSACTION.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID, FINANCIALTRANSACTION.BASEEXCHANGERATEID,
case FINANCIALTRANSACTION.TYPECODE
when 0 then N'Payment'
when 1 then N'Pledge'
when 2 then N'Recurring gift'
when 3 then N'Matching gift claim'
when 4 then N'Planned gift'
when 5 then N'Order'
when 6 then N'Grant award'
when 7 then N'Auction donation'
when 8 then N'Donor challenge claim'
when 9 then N'Pending gift'
end as TRANSACTIONTYPE
from dbo.FINANCIALTRANSACTION inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
left outer join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTION.ID = V.FINANCIALTRANSACTIONID
left join dbo.CONSTITUENT on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
left join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
where FINANCIALTRANSACTION.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and FINANCIALTRANSACTION.DELETEDON is null