USP_DATALIST_PURPOSENAMINGOPPORTUNITIES

Returns a list for all naming opportunities for a fundraising purpose.

Parameters

Parameter	Parameter Type	Mode	Description
@PURPOSEID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@FACILITYID	uniqueidentifier	IN	Facility
@UNITCODEID	uniqueidentifier	IN	Unit
@INCLUDEUNAVAILABLE	bit	IN	Include unavailable
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy



                CREATE procedure dbo.USP_DATALIST_PURPOSENAMINGOPPORTUNITIES
                (
                    @PURPOSEID uniqueidentifier = null,
                    @FACILITYID uniqueidentifier = null,
                    @UNITCODEID uniqueidentifier = null,
                    @INCLUDEUNAVAILABLE bit = 0,
                    @CURRENTAPPUSERID uniqueidentifier = null
                )
                as
                    set nocount on;

                    select
                        NAMINGOPPORTUNITY.ID,
                        NAMINGOPPORTUNITY.NAME,
                        (select DESCRIPTION from dbo.NAMINGOPPORTUNITYTYPECODE where NAMINGOPPORTUNITYTYPECODE.ID = NAMINGOPPORTUNITY.NAMINGOPPORTUNITYTYPECODEID),
                        (select DESCRIPTION from dbo.NAMINGOPPORTUNITYAREACODE where NAMINGOPPORTUNITYAREACODE.ID = NAMINGOPPORTUNITYFACILITY.AREACODEID),
                        (select DESCRIPTION from dbo.NAMINGOPPORTUNITYUNITCODE where NAMINGOPPORTUNITYUNITCODE.ID = NAMINGOPPORTUNITYFACILITY.UNITCODEID),
                        NAMINGOPPORTUNITYFACILITY.NAME,
                        NAMINGOPPORTUNITY.QUANTITY,
                        NAMINGOPPORTUNITY.REMAINING,
                        (select count(*) from dbo.UFN_NAMINGOPPORTUNITY_ALLPROSPECTS(NAMINGOPPORTUNITY.ID)) as PROSPECTS
                    from
                        dbo.NAMINGOPPORTUNITY
                        left join NAMINGOPPORTUNITYFACILITY on NAMINGOPPORTUNITYFACILITY.ID = NAMINGOPPORTUNITY.FACILITYID
                    where
                        NAMINGOPPORTUNITY.PURPOSEID = @PURPOSEID and
                        ((NAMINGOPPORTUNITY.REMAINING > 0) or (@INCLUDEUNAVAILABLE = 1)) and
                        (@FACILITYID is null or NAMINGOPPORTUNITY.FACILITYID = @FACILITYID)    and
                        (@UNITCODEID is null or NAMINGOPPORTUNITYFACILITY.UNITCODEID = @UNITCODEID) and
                        dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, dbo.UFN_SITEID_MAPFROM_NAMINGOPPORTUNITYID(NAMINGOPPORTUNITY.ID)) = 1
                    order by
                        NAMINGOPPORTUNITY.NAME