USP_DATALIST_MATCHINGGIFTELIGIBLEREVENUE

List all revenue that has been marked as eligible for matching gift claims.

Parameters

Parameter	Parameter Type	Mode	Description
@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.

Definition

Copy



                    CREATE procedure dbo.USP_DATALIST_MATCHINGGIFTELIGIBLEREVENUE
                    (
                        @CURRENTAPPUSERID uniqueidentifier = null,
                        @SITEFILTERMODE tinyint = 0,
                        @SITESSELECTED xml = null,
                        @SECURITYFEATUREID uniqueidentifier = null,
                        @SECURITYFEATURETYPE tinyint = null
                    )
                    as                    
                        select FINANCIALTRANSACTION.ID REVENUEID, 
                            cast(FINANCIALTRANSACTION.DATE as datetime) REVENUEDATE,
                            CONSTITUENT.ID CONSTITUENTID,
                            CONSTITUENT.NAME CONSTITUENTNAME,
                            FINANCIALTRANSACTION.TYPE REVENUETRANSACTIONTYPE,
                            FINANCIALTRANSACTION.TRANSACTIONAMOUNT REVENUEAMOUNT, 
                            REVENUE_EXT.RECEIPTAMOUNT REVENUERECEIPTAMOUNT,
                            ( 
                                select dbo.UDA_BUILDLIST(distinct REVENUESPLIT_EXT.TYPE)
                                from dbo.REVENUESPLIT_EXT
                                inner join FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                                where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID=FINANCIALTRANSACTION.ID
                            ) APPLICATIONTYPES,
                            ( 
                                select dbo.UDA_BUILDLIST(SITE.NAME)
                                from dbo.UFN_SITEID_MAPFROM_REVENUEID(FINANCIALTRANSACTION.ID) REVSITES
                                    inner join dbo.SITE on SITE.ID=REVSITES.SITEID
                            ) SITES,
                        FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
                        from dbo.FINANCIALTRANSACTION
                            inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
                            inner join dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
                        where REVENUE_EXT.ELIGIBLEFORMATCHINGGIFTCLAIM = 1
                            and (
                                select count(*) 
                                from dbo.UFN_SITEID_MAPFROM_REVENUEID(FINANCIALTRANSACTION.ID) 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)))
                            ) > 0
                            and    (
                                @SITEFILTERMODE = 0
                                    or exists(
                                        select 1
                                        from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                                            inner join dbo.UFN_SITEID_MAPFROM_REVENUEID(FINANCIALTRANSACTION.ID) REVSITES on REVSITES.SITEID = SITEFILTER.SITEID
                                    )
                            )
                        order by FINANCIALTRANSACTION.DATE asc, FINANCIALTRANSACTION.TRANSACTIONAMOUNT desc

                        return 0;