V_QUERY_HOUSEHOLDREVENUERECOGNITION
Provides the ability to query household revenue recognition credits.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| REVENUESPLITID | uniqueidentifier | yes | Revenue split ID |
| HOUSEHOLDID | uniqueidentifier | Household | |
| 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 ID |
| ORGANIZATIONAMOUNT | money | Amount (organization currency) | |
| TRANSACTIONTYPE | nvarchar(40) | yes | Transaction type |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:31:44 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_HOUSEHOLDREVENUERECOGNITION AS
with CONSTITUENT_CTE as(
select
CONSTITUENT.ID as CONSTITUENTID,
CONSTITUENT.ID as HOUSEHOLDID,
CAST(null as datetime) as DATEFROM,
CAST(null as datetime) as DATETO
from dbo.CONSTITUENT
where CONSTITUENT.ID not in
(select GROUPMEMBER.MEMBERID from dbo.GROUPMEMBER
inner join dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
where GROUPDATA.GROUPTYPECODE = 0
and GROUPMEMBER.MEMBERID = CONSTITUENT.ID and ((GROUPMEMBERDATERANGE.DATEFROM is null and (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO >= getdate()))
or (GROUPMEMBERDATERANGE.DATETO is null and (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= getdate()))
or (GROUPMEMBERDATERANGE.DATEFROM <= getdate() and GROUPMEMBERDATERANGE.DATETO >= getdate())))
union all
select
GROUPMEMBER.MEMBERID as CONSTITUENTID,
GROUPMEMBER.GROUPID as HOUSEHOLDID,
GROUPMEMBERDATERANGE.DATEFROM,
GROUPMEMBERDATERANGE.DATETO
from dbo.GROUPMEMBER
inner join dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
where GROUPDATA.GROUPTYPECODE = 0
)
select
RR.ID,
RR.REVENUESPLITID,
CONSTITUENT_CTE.HOUSEHOLDID,
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,
FINANCIALTRANSACTION.TYPE as TRANSACTIONTYPE
/*#EXTENSION*/
from dbo.REVENUERECOGNITION RR
inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = RR.CONSTITUENTID
and ((CONSTITUENT_CTE.DATEFROM is null and (CONSTITUENT_CTE.DATETO is null or CONSTITUENT_CTE.DATETO >= RR.EFFECTIVEDATE))
or (CONSTITUENT_CTE.DATETO is null and (CONSTITUENT_CTE.DATEFROM is null or CONSTITUENT_CTE.DATEFROM <= RR.EFFECTIVEDATE))
or (CONSTITUENT_CTE.DATEFROM <= RR.EFFECTIVEDATE and CONSTITUENT_CTE.DATETO >= RR.EFFECTIVEDATE))
inner join dbo.FINANCIALTRANSACTIONLINEITEM on RR.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.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
union all
select
RECOGNITIONCREDIT.ID,
null as REVENUESPLITID,
CONSTITUENT_CTE.HOUSEHOLDID,
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,
'Donor challenge claim - Internal sponsor' as TRANSACTIONTYPE
/*#EXTENSION*/
from dbo.RECOGNITIONCREDIT
inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = RECOGNITIONCREDIT.CONSTITUENTID
and ((CONSTITUENT_CTE.DATEFROM is null and (CONSTITUENT_CTE.DATETO is null or CONSTITUENT_CTE.DATETO >= RECOGNITIONCREDIT.EFFECTIVEDATE))
or (CONSTITUENT_CTE.DATETO is null and (CONSTITUENT_CTE.DATEFROM is null or CONSTITUENT_CTE.DATEFROM <= RECOGNITIONCREDIT.EFFECTIVEDATE))
or (CONSTITUENT_CTE.DATEFROM <= RECOGNITIONCREDIT.EFFECTIVEDATE and CONSTITUENT_CTE.DATETO >= RECOGNITIONCREDIT.EFFECTIVEDATE))
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;