USP_DATALIST_FUNDINGREQUESTSFORFUNDRAISER

Returns a list of funding requests for a given fundraiser.
Parameters

Parameter	Parameter Type	Mode	Description
@FUNDRAISERID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@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_FUNDINGREQUESTSFORFUNDRAISER
                (
                    @FUNDRAISERID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @SITEFILTERMODE tinyint = 0,
                    @SITESSELECTED xml = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
                )
                as
                    set nocount on;

                    with CTE_FUNDRAISERREQUESTS( ID, ROLE)
                    as (
                        select ID, 'Primary manager'
                        from dbo.FUNDINGREQUEST
                        where PRIMARYMANAGERID = @FUNDRAISERID

                        union all

                        select ID, 'Secondary manager'
                        from dbo.FUNDINGREQUEST
                        where SECONDARYMANAGERID = @FUNDRAISERID

                        union all

                        select FUNDINGREQUEST.ID, 'Secondary solicitor'
                        from dbo.FUNDINGREQUEST
                        where @FUNDRAISERID in(
                                select SPONSORID
                                from FUNDINGREQUESTSPONSOR
                                where FUNDINGREQUESTSPONSOR.FUNDINGREQUESTID = FUNDINGREQUEST.ID
                            )
                    )
                    select 
                        FUNDINGREQUEST.ID,
                        GRANTS.ID,
                        GRANTS.GRANTORID,
                        (
                            select top 1 ACTUALDATE
                            from INTERACTION
                            where FUNDINGREQUESTID = FUNDINGREQUEST.ID
                                and STATUSCODE = 2
                            order by ACTUALDATE desc
                        ) LASTSTEPDATE,
                        (
                            select top 1 EXPECTEDDATE
                            from INTERACTION
                            where FUNDINGREQUESTID = FUNDINGREQUEST.ID
                                and STATUSCODE = 1
                            order by EXPECTEDDATE asc
                        ) NEXTSTEPDATE,                        
                        CONSTITUENT.NAME,
                        GRANTS.TITLE,
                        dbo.UFN_FUNDINGREQUESTSTATUSCODE_GETDESCRIPTION(FUNDINGREQUEST.FUNDINGREQUESTSTATUSCODEID),
                        dbo.UFN_FUNDINGREQUESTSTAGECODE_GETDESCRIPTION(FUNDINGREQUESTSTAGECODEID),
                        (
                            select dbo.UDA_BUILDLIST(ROLE)
                            from CTE_FUNDRAISERREQUESTS
                            where ID = FUNDINGREQUEST.ID
                        ),
                        FUNDINGREQUEST.TRANSACTIONAMOUNTREQUESTED,
                        dbo.UFN_FUNDINGREQUEST_GETAMOUNTAWARDEDINCURRENCY(FUNDINGREQUEST.ID, FUNDINGREQUEST.TRANSACTIONCURRENCYID),
                        SITE.NAME,
            FUNDINGREQUEST.TRANSACTIONCURRENCYID
                    from dbo.FUNDINGREQUEST
                        inner join dbo.GRANTS on GRANTS.ID = FUNDINGREQUEST.GRANTSID
                        inner join dbo.CONSTITUENT on CONSTITUENT.ID = GRANTS.GRANTORID
                        inner join dbo.FUNDINGPLAN on FUNDINGPLAN.ID = FUNDINGREQUEST.FUNDINGPLANID
                        left join dbo.SITE on SITE.ID = FUNDINGPLAN.SITEID
                    where
                        (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[FUNDINGPLAN].[SITEID] or (SITEID is null and [FUNDINGPLAN].[SITEID] is null)))
                            and (
                                @SITEFILTERMODE = 0
                                    or FUNDINGPLAN.SITEID in ( 
                                        select SITEID
                                        from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
                                    )
                            )
                            and FUNDINGPLAN.INACTIVE = 0
                            and FUNDINGREQUEST.ID in(
                                select ID
                                from CTE_FUNDRAISERREQUESTS
                            )
                    order by CONSTITUENT.NAME, GRANTS.TITLE