USP_DATALIST_CONSTITUENTGROUP_RECOGNITIONHISTORY

This datalist returns all of a constituent group and its members' 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_CONSTITUENTGROUP_RECOGNITIONHISTORY
          (
            @CONSTITUENTID uniqueidentifier,
            @NUMBERTOSHOWCODE smallint = 2,
            @CURRENTAPPUSERID uniqueidentifier = null,
            @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,
              case when RECOGNITIONCREDIT.ID is not null
                then RC_CONSTITUENT.ID 
                else CONSTITUENT.ID
              end ID,
              case when RECOGNITIONCREDIT.ID is not null
                then RCC_NF.NAME 
                else C_NF.NAME
              end NAME,
              [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
            inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = [DATA].REVENUESPLITID
            inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
            left join dbo.REVENUERECOGNITION on REVENUERECOGNITION.ID = [DATA].RECOGNITIONID
            left join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUERECOGNITION.CONSTITUENTID
            left join dbo.CONSTITUENT RC_CONSTITUENT on RC_CONSTITUENT.ID = RECOGNITIONCREDIT.CONSTITUENTID
            cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID([DATA].REVENUESPLITID)as [REVSITES]
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) C_NF
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RC_CONSTITUENT.ID) RCC_NF
            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))
                )
              )
              and REVENUE.DELETEDON is null
              and REVENUESPLIT.DELETEDON is null
              and REVENUESPLIT.TYPECODE <> 1
            order by [DATA].EFFECTIVEDATE desc, [DATA].DATEADDED desc, [DATA].AMOUNT desc
          else
            select 
              [DATA].RECOGNITIONID,
              case when RECOGNITIONCREDIT.ID is not null
                then RC_CONSTITUENT.ID 
                else CONSTITUENT.ID
              end ID,
              case when RECOGNITIONCREDIT.ID is not null
                then RCC_NF.NAME 
                else C_NF.NAME
              end NAME,
              [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
            inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = [DATA].REVENUESPLITID
            inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
            left join dbo.REVENUERECOGNITION on REVENUERECOGNITION.ID = [DATA].RECOGNITIONID
            left join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUERECOGNITION.CONSTITUENTID
            left join dbo.CONSTITUENT RC_CONSTITUENT on RC_CONSTITUENT.ID = RECOGNITIONCREDIT.CONSTITUENTID
            cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID([DATA].REVENUESPLITID)as [REVSITES]
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) C_NF
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RC_CONSTITUENT.ID) RCC_NF
            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))
                )
              )
                and REVENUE.DELETEDON is null
                and REVENUESPLIT.DELETEDON is null
                and REVENUESPLIT.TYPECODE <> 1
            order by [DATA].EFFECTIVEDATE desc, [DATA].DATEADDED desc, [DATA].AMOUNT desc