V_QUERY_DONORCHALLENGE
Provides the ability to query donor challenges.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| NAME | nvarchar(100) | Name | |
| TYPE | nvarchar(14) | yes | Type |
| DESCRIPTION | nvarchar(255) | Description | |
| STARTDATE | datetime | Start date | |
| ENDDATE | datetime | End date | |
| EXTERNALSPONSORID | uniqueidentifier | yes | External sponsor system ID |
| INTERNALSPONSOR | nvarchar(100) | yes | Internal sponsor |
| TOTALFUNDS | money | Total funds | |
| PAYPLEDGESWITHIN | tinyint | Pledge cut-off value | |
| PAYPLEDGESWITHINUNIT | nvarchar(6) | yes | Pledge cut-off unit |
| MATCHTYPE | nvarchar(27) | yes | Match portion |
| MATCHINGFACTOR | decimal(5, 2) | Matching factor | |
| MATCHTHRESHOLD | money | Match threshold | |
| MINGIFTAMOUNT | money | Min match per gift | |
| MAXMATCHPERGIFT | money | Max match per gift | |
| REVENUERECOGNITIONTYPE | nvarchar(100) | yes | Recognition credit type |
| PROCESSLASTRUN | datetime | yes | Last updated |
| STATUSTYPE | nvarchar(9) | yes | Status |
| LUMPSUMMATCHREVENUEID | uniqueidentifier | yes | Lump sum revenue system ID |
| SITENAME | nvarchar(250) | yes | Site |
| FUNDINGSOURCE | varchar(8) | Funding source | |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| 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 |
| BASECURRENCYID | uniqueidentifier | yes | BASECURRENCYID |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | ORGANIZATIONEXCHANGERATEID |
| ORGANIZATIONTOTALFUNDS | money | Total funds (organization currency) | |
| ORGANIZATIONMATCHTHRESHOLD | money | Match threshold (organization currency) | |
| ORGANIZATIONMINGIFTAMOUNT | money | Min match per gift (organization currency) | |
| ORGANIZATIONMAXMATCHPERGIFT | money | Max match per gift (organization currency) | |
| SITEID | uniqueidentifier | yes | Site ID |
| DESIGNATIONID | uniqueidentifier | yes | Designation ID |
| REMAININGFUNDS | money | yes | Remaining Funds |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:26:21 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_DONORCHALLENGE AS
select
DONORCHALLENGE.ID,
DONORCHALLENGE.NAME,
DONORCHALLENGE.TYPE,
DONORCHALLENGE.DESCRIPTION,
DONORCHALLENGE.STARTDATE,
DONORCHALLENGE.ENDDATE,
DONORCHALLENGE.EXTERNALSPONSORID,
dbo.UFN_DONORCHALLENGESPONSORCODE_GETDESCRIPTION(DONORCHALLENGE.INTERNALSPONSORCODEID) INTERNALSPONSOR,
DONORCHALLENGE.TOTALFUNDS,
DONORCHALLENGE.PAYPLEDGESWITHIN,
DONORCHALLENGE.PAYPLEDGESWITHINUNIT,
DONORCHALLENGE.MATCHTYPE,
DONORCHALLENGE.MATCHINGFACTOR,
DONORCHALLENGE.MATCHTHRESHOLD,
DONORCHALLENGE.MINGIFTAMOUNT,
DONORCHALLENGE.MAXMATCHPERGIFT,
dbo.UFN_REVENUERECOGNITIONTYPECODE_GETDESCRIPTION(DONORCHALLENGE.REVENUERECOGNITIONTYPECODEID) REVENUERECOGNITIONTYPE,
DONORCHALLENGE.PROCESSLASTRUN,
DONORCHALLENGE.STATUSTYPE,
DONORCHALLENGE.LUMPSUMMATCHREVENUEID,
COALESCE((SELECT NAME FROM dbo.SITE WHERE ID=DONORCHALLENGE.SITEID),'') AS SITENAME,
case when DONORCHALLENGE.EXTERNALSPONSORID is null then 'Internal' else 'External' end FUNDINGSOURCE,
DONORCHALLENGE.DATEADDED,
DONORCHALLENGE.DATECHANGED,
DONORCHALLENGE.TSLONG,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
DONORCHALLENGE.BASECURRENCYID,
DONORCHALLENGE.ORGANIZATIONEXCHANGERATEID,
DONORCHALLENGE.ORGANIZATIONTOTALFUNDS,
DONORCHALLENGE.ORGANIZATIONMATCHTHRESHOLD,
DONORCHALLENGE.ORGANIZATIONMINGIFTAMOUNT,
DONORCHALLENGE.ORGANIZATIONMAXMATCHPERGIFT,
DONORCHALLENGE.SITEID,
[DESIGNATION].DESIGNATIONID,
dbo.UFN_DONORCHALLENGE_GETREMAININGFUNDS(DONORCHALLENGE.ID) REMAININGFUNDS
/*#EXTENSION*/
from dbo.DONORCHALLENGE
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = DONORCHALLENGE.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = DONORCHALLENGE.CHANGEDBYID
left join dbo.DONORCHALLENGEDESIGNATIONMAP as [DESIGNATION] on [DESIGNATION].DONORCHALLENGEID = DONORCHALLENGE.ID