V_QUERY_RECOGNITION_TEMPLATE

This is a template for the system generated recognition program queries.

Fields

Field Field Type Null Description
ID uniqueidentifier System record ID
CONSTITUENTID uniqueidentifier Constituent ID
RECOGNITIONPROGRAMID uniqueidentifier Recognition program ID
RECOGNITIONLEVELID uniqueidentifier Recognition level ID
RECOGNITIONLEVEL nvarchar(100) yes Recognition level
JOINDATE datetime yes Join date
DATEACHIEVED datetime Date achieved
EXPIRATIONDATE datetime yes Expiration date
CONSECUTIVEYEARS int yes Years consecutive
TOTALAMOUNT money Total amount
TOTALPLANNEDGIFTAMOUNT money Planned gift total amount
DAYSUNTILEXPIRATION int yes Days until expiration
AMOUNTTONEXTLEVEL money yes Amount to next level
STATUS nvarchar(16) yes Status
COMMENTS nvarchar(1000) Comments
ISANONYMOUS bit Is anonymous
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
ORGANIZATIONTOTALAMOUNT money Total amount (organization currency)
ORGANIZATIONTOTALPLANNEDGIFTAMOUNT money Planned gift total amount (organization currency)
ORGANIZATIONAMOUNTTONEXTLEVEL money yes Amount to next level (organization currency)
ORGANIZATIONEXCHANGERATEID uniqueidentifier yes Currency exchange rate
ADDEDBYID uniqueidentifier yes
CHANGEDBYID uniqueidentifier yes

Definition

Copy 
/*
Generated by Blackbaud AppFx Platform
Date:  9/30/2015 1:01:43 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_RECOGNITION_TEMPLATE AS



        select
          CR.ID,
          CR.CONSTITUENTID,
          CR.RECOGNITIONPROGRAMID,
          CR.RECOGNITIONLEVELID,
          dbo.UFN_RECOGNITIONLEVEL_GETNAME(CR.RECOGNITIONLEVELID) as RECOGNITIONLEVEL,
          (
            select min(CR2.JOINDATE) 
            from dbo.CONSTITUENTRECOGNITION CR2
            where
              CR2.RECOGNITIONPROGRAMID = CR.RECOGNITIONPROGRAMID
              and CR2.CONSTITUENTID = CR.CONSTITUENTID
          ) as [JOINDATE],
          CR.JOINDATE as [DATEACHIEVED],
          CR.EXPIRATIONDATE,
          dbo.UFN_CONSTITUENTRECOGNITION_CONSECUTIVEYEARS(CR.ID) as [CONSECUTIVEYEARS],
          CR.TOTALAMOUNT,
          CR.TOTALPLANNEDGIFTAMOUNT,
          DATEDIFF(dd,getdate(), CR.EXPIRATIONDATE) as DAYSUNTILEXPIRATION,
          case
            when RECOGNITIONLEVEL.AMOUNT <> (select MAX(AMOUNT) from dbo.RECOGNITIONLEVEL where RECOGNITIONPROGRAMID = RECOGNITIONPROGRAM.ID) then
              case
                when RECOGNITIONPROGRAM.REVENUECODE = 1 then  --PLANNED GIFT ONLY

                (
                  select min(PLANNEDGIFTAMOUNT)
                  from dbo.RECOGNITIONLEVEL RLN
                  where
                    RLN.RECOGNITIONPROGRAMID = CR.RECOGNITIONPROGRAMID
                    and RLN.PLANNEDGIFTAMOUNT > CR.TOTALPLANNEDGIFTAMOUNT
                    and RLN.ISACTIVE = 1
                ) - CR.TOTALPLANNEDGIFTAMOUNT
                else
                (
                  select min(AMOUNT)
                  from dbo.RECOGNITIONLEVEL RLN
                  where
                    RLN.RECOGNITIONPROGRAMID = CR.RECOGNITIONPROGRAMID
                    and AMOUNT > CR.TOTALAMOUNT
                    and RLN.ISACTIVE = 1
                ) - CR.TOTALAMOUNT
              end
            else null
          end as AMOUNTTONEXTLEVEL,
          case
            when (CR.STATUSCODE = 0 and CURRENTRECOGNITIONS.EXPIRED = 1) then 'Lapsed'
            else CR.STATUS
          end as STATUS,
          CR.COMMENTS,
          CR.ISANONYMOUS,
          ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
          ADDEDBY.USERNAME as ADDEDBY_USERNAME,
          CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
          CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
          CR.DATEADDED,
          CR.DATECHANGED,
          CR.TSLONG,
          CR.BASECURRENCYID,
          CR.ORGANIZATIONTOTALAMOUNT,
          CR.ORGANIZATIONTOTALPLANNEDGIFTAMOUNT,
          case
            when RECOGNITIONPROGRAM.REVENUECODE = 1 then  --PLANNED GIFT ONLY

            (
              select min(ORGANIZATIONPLANNEDGIFTAMOUNT) 
              from dbo.RECOGNITIONLEVEL RLN
              where
                RLN.RECOGNITIONPROGRAMID = CR.RECOGNITIONPROGRAMID
                and RLN.ORGANIZATIONPLANNEDGIFTAMOUNT > CR.ORGANIZATIONTOTALPLANNEDGIFTAMOUNT
                and RLN.ISACTIVE = 1
            ) - CR.ORGANIZATIONTOTALPLANNEDGIFTAMOUNT
            else
            (
              select min(ORGANIZATIONAMOUNT)
              from dbo.RECOGNITIONLEVEL RLN
              where
                RLN.RECOGNITIONPROGRAMID = CR.RECOGNITIONPROGRAMID
                and ORGANIZATIONAMOUNT > CR.ORGANIZATIONTOTALAMOUNT
                and RLN.ISACTIVE = 1
            ) - CR.ORGANIZATIONTOTALAMOUNT
          end as ORGANIZATIONAMOUNTTONEXTLEVEL,
          CR.ORGANIZATIONEXCHANGERATEID,
          ADDEDBY.ID as ADDEDBYID,
          CHANGEDBY.ID as CHANGEDBYID
          /*#EXTENSION*/
        --when generating the recognition program specific query, pass in the RECOGNITIONPROGRAMID to return the current recognitions for that program

        from dbo.UFN_CONSTITUENT_GETCURRENTRECOGNITIONS(getdate(),null/*RECOGNITIONPROGRAMID*/) as CURRENTRECOGNITIONS
          inner join dbo.CONSTITUENTRECOGNITION CR on CR.ID = CURRENTRECOGNITIONS.ID
          left join dbo.RECOGNITIONPROGRAM on CR.RECOGNITIONPROGRAMID = RECOGNITIONPROGRAM.ID
          left join dbo.RECOGNITIONLEVEL on CR.RECOGNITIONLEVELID = RECOGNITIONLEVEL.ID
          left join dbo.CHANGEAGENT as ADDEDBY on ADDEDBY.ID = CR.ADDEDBYID
          left join dbo.CHANGEAGENT as CHANGEDBY on CHANGEDBY.ID = CR.CHANGEDBYID
        /*WHERECLAUSE*/