USP_DATALIST_CONSTITUENT_RECOGNITIONHISTORY

This datalist returns all of a constituent's recognition credits.

Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@NUMBERTOSHOWCODE	smallint	IN	Show
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.
@CURRENCYCODE	tinyint	IN	Currency

Definition

Copy




        CREATE procedure dbo.USP_DATALIST_CONSTITUENT_RECOGNITIONHISTORY
        (
          @CONSTITUENTID uniqueidentifier,
          @NUMBERTOSHOWCODE smallint = 2,
          @CURRENTAPPUSERID uniqueidentifier,
          @SITEFILTERMODE tinyint = 0,
          @SITESSELECTED xml = null,
          @SECURITYFEATUREID uniqueidentifier = null,
          @SECURITYFEATURETYPE tinyint = null,
          @CURRENCYCODE tinyint = 0
        )
        as
        set nocount on;

        declare @ISGROUP bit;
        select @ISGROUP = ISGROUP from dbo.CONSTITUENT where ID = @CONSTITUENTID;

        if @ISGROUP = 0
          select 
            [DATA].RECOGNITIONID,
            [DATA].EFFECTIVEDATE,
            [DATA].TYPE,
            [DATA].AMOUNT,
            [DATA].GROSSAMOUNT,
            [DATA].GIFTAMOUNT,
            [DATA].DONOR,
            [DATA].REVENUETYPE,
            case when RECOGNITIONCREDIT.ID is not null 
              then null --Disable go to payment for internal sponsor payment

              else [DATA].RECORDID
            end RECORDID,
            [DATA].DATEADDED,
            [DATA].CAMPAIGNS,
            [DATA].SITES,
            [DATA].CURRENCYID
          from dbo.UFN_DATALIST_CONSTITUENT_RECOGNITIONHISTORY_INDORG_2(@CONSTITUENTID, @NUMBERTOSHOWCODE, @CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED, @SECURITYFEATUREID, @SECURITYFEATURETYPE,@CURRENCYCODE) as [DATA]
          left join dbo.RECOGNITIONCREDIT on [DATA].RECOGNITIONID = RECOGNITIONCREDIT.ID
          cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID([DATA].REVENUESPLITID)as [REVSITES]
          where
            (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null))) and
            (
              @SITEFILTERMODE = 0
              or
              exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) 
              where ((RECOGNITIONCREDIT.ID is null) and UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
              or (UFN_SITE_BUILDDATALISTSITEFILTER.SITEID=dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID))
              )
            )
          order by [DATA].EFFECTIVEDATE desc, [DATA].DATEADDED desc, [DATA].AMOUNT desc
        else
          select 
            [DATA].RECOGNITIONID,
            [DATA].EFFECTIVEDATE,
            [DATA].TYPE,
            [DATA].AMOUNT,
            [DATA].GROSSAMOUNT,
            [DATA].GIFTAMOUNT,
            [DATA].DONOR,
            [DATA].REVENUETYPE,
            case when RECOGNITIONCREDIT.ID is not null 
              then null --Disable go to payment for internal sponsor payment

              else [DATA].RECORDID
            end RECORDID,
            [DATA].DATEADDED,
            [DATA].CAMPAIGNS,
            [DATA].SITES,
            [DATA].CURRENCYID           
          from dbo.UFN_DATALIST_CONSTITUENT_RECOGNITIONHISTORY_GROUP_2(@CONSTITUENTID, @NUMBERTOSHOWCODE, @CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED, @SECURITYFEATUREID, @SECURITYFEATURETYPE,@CURRENCYCODE) as [DATA]
          left join dbo.RECOGNITIONCREDIT on [DATA].RECOGNITIONID = RECOGNITIONCREDIT.ID
          cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID([DATA].REVENUESPLITID)as [REVSITES]
          where
            (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null))) and
            (
              @SITEFILTERMODE = 0
              or
              exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) 
              where ((RECOGNITIONCREDIT.ID is null) and UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
              or (UFN_SITE_BUILDDATALISTSITEFILTER.SITEID=dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID))
              )
            )
          order by [DATA].EFFECTIVEDATE desc, [DATA].DATEADDED desc, [DATA].AMOUNT desc