V_QUERY_RECOGNITIONPROGRAM
Provides the ability to query recognition programs.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| NAME | nvarchar(100) | Name | |
| DESCRIPTION | nvarchar(255) | Description | |
| SITE | nvarchar(250) | yes | Site |
| TYPE | nvarchar(15) | yes | Type |
| ALLOWINDIVIDUAL | bit | Allow individuals | |
| ALLOWHOUSEHOLD | bit | Allow households | |
| ALLOWGROUP | bit | Allow groups | |
| ALLOWORGANIZATION | bit | Allow organizations | |
| ISACTIVE | bit | Active | |
| EXPIRESON | nvarchar(20) | yes | Expiration date |
| BACKDATEMEMBERSHIPS | bit | Use cutoff date | |
| CUTOFFDATEFORYEAR | UDT_MONTHDAY | Cutoff date | |
| GIFTTYPESLIST | nvarchar(1000) | yes | Revenue types |
| STARTDATE | datetime | yes | Recognition effective date |
| IDSETREGISTERNAME | nvarchar(300) | yes | Revenue selection |
| MAXIMUMAMOUNT | money | Maximum recognition amount | |
| REVENUE | nvarchar(26) | yes | Revenue |
| PLANNEDGIFT | nvarchar(43) | yes | Planned gift |
| 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 |
| ORGANIZATIONMAXIMUMAMOUNT | money | Maximum revenue amount (organization currency) | |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Currency exchange rate |
| RECOGNITIONCREDITTYPES | nvarchar(max) | yes | Recognition credit types |
| SITEID | uniqueidentifier | yes | Site ID |
| USEGROSSAMOUNT | bit |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/30/2015 1:01:54 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_RECOGNITIONPROGRAM AS
select
RP.ID,
RP.NAME,
RP.DESCRIPTION,
SITE.NAME as SITE,
RP.TYPE,
RP.ALLOWINDIVIDUAL,
RP.ALLOWHOUSEHOLD,
RP.ALLOWGROUP,
RP.ALLOWORGANIZATION,
RP.ISACTIVE,
case when RP.TYPECODE = 0 then RP.EXPIRESON else '' end as EXPIRESON,
RP.BACKDATEMEMBERSHIPS,
RP.CUTOFFDATEFORYEAR,
dbo.UFN_RECOGNITIONPROGRAM_GETGIFTTYPES_LIST(RP.ID) as [GIFTTYPESLIST],
RP.STARTDATE,
IDSETREGISTER.NAME as IDSETREGISTERNAME,
RP.MAXIMUMAMOUNT,
RP.REVENUE,
RP.PLANNEDGIFT,
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
RP.DATEADDED,
RP.DATECHANGED,
RP.TSLONG,
RP.BASECURRENCYID,
RP.ORGANIZATIONMAXIMUMAMOUNT,
RP.ORGANIZATIONEXCHANGERATEID,
(select dbo.UDA_BUILDLIST(DESCRIPTION) from dbo.UFN_RECOGNITIONPROGRAM_GETCREDITTYPES(RP.ID)) as RECOGNITIONCREDITTYPES,
SITE.ID as SITEID,
RP.USEGROSSAMOUNT
/*#EXTENSION*/
from
dbo.RECOGNITIONPROGRAM as RP
left join dbo.SITE on
SITE.ID = RP.SITEID
left join dbo.IDSETREGISTER on
IDSETREGISTER.ID=RP.SELECTIONID
left join dbo.CHANGEAGENT as ADDEDBY on
ADDEDBY.ID = RP.ADDEDBYID
left join dbo.CHANGEAGENT as CHANGEDBY on
CHANGEDBY.ID = RP.CHANGEDBYID