V_QUERY_REVENUERECOGNITION
Provides the ability to query revenue recognition credits.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| REVENUESPLITID | uniqueidentifier | yes | Revenue split ID |
| CONSTITUENTID | uniqueidentifier | Constituent system ID | |
| EFFECTIVEDATE | datetime | Effective date | |
| AMOUNT | money | Amount | |
| TYPE | nvarchar(100) | yes | Type |
| 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 |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| BASECURRENCYID | uniqueidentifier | yes | Base currency |
| ORGANIZATIONAMOUNT | money | Amount (organization currency) | |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Organization exchange rate |
| TRANSACTIONTYPE | nvarchar(40) | yes | Transaction type |
| DESIGNATIONID | uniqueidentifier | yes | Designation system record ID |
| REVENUERECOGNITIONTYPECODEID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 10/27/2018 10:42:47 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.186.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_REVENUERECOGNITION AS
select
RR.ID,
RR.REVENUESPLITID,
RR.CONSTITUENTID,
RR.EFFECTIVEDATE,
RR.AMOUNT,
RRTC.DESCRIPTION as TYPE,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
RR.DATEADDED,
RR.DATECHANGED,
RR.TSLONG,
RR.BASECURRENCYID,
RR.ORGANIZATIONAMOUNT,
RR.ORGANIZATIONEXCHANGERATEID,
REVENUE.TYPE as TRANSACTIONTYPE,
REVENUESPLIT_EXT.DESIGNATIONID,
[RRTC].[ID] as [REVENUERECOGNITIONTYPECODEID]
/*#EXTENSION*/
from
dbo.REVENUERECOGNITION RR
inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on RR.REVENUESPLITID = REVENUESPLIT.ID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
left join dbo.REVENUERECOGNITIONTYPECODE RRTC on RR.REVENUERECOGNITIONTYPECODEID = RRTC.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on ADDEDBY.ID = RR.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on CHANGEDBY.ID = RR.CHANGEDBYID
where
REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.TYPECODE <> 1
and REVENUE.DELETEDON is null
and REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9)
union all
select
RECOGNITIONCREDIT.ID,
null as REVENUESPLITID,
RECOGNITIONCREDIT.CONSTITUENTID,
RECOGNITIONCREDIT.EFFECTIVEDATE,
RECOGNITIONCREDIT.AMOUNT,
RRTC.DESCRIPTION as TYPE,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
RECOGNITIONCREDIT.DATEADDED,
RECOGNITIONCREDIT.DATECHANGED,
RECOGNITIONCREDIT.TSLONG,
RECOGNITIONCREDIT.BASECURRENCYID,
RECOGNITIONCREDIT.ORGANIZATIONAMOUNT,
RECOGNITIONCREDIT.ORGANIZATIONEXCHANGERATEID,
'Donor challenge claim - Internal sponsor' as TRANSACTIONTYPE,
RECOGNITIONCREDIT.DESIGNATIONID,
[RRTC].[ID] as [REVENUERECOGNITIONTYPECODEID]
from
dbo.RECOGNITIONCREDIT
left join dbo.REVENUERECOGNITIONTYPECODE RRTC on RECOGNITIONCREDIT.USERRECOGNITIONTYPECODEID = RRTC.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on ADDEDBY.ID = RECOGNITIONCREDIT.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on CHANGEDBY.ID = RECOGNITIONCREDIT.CHANGEDBYID
where RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1