UFN_SPONSORSHIPOPPORTUNITY_CALCAVAILABILITY

Returns the correct value for availability of an opportunity based on its opportunity group settings and sponsorships.

Return

Return Type
tinyint

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_SPONSORSHIPOPPORTUNITY_CALCAVAILABILITY(
    @ID uniqueidentifier
)
returns tinyint
with execute as caller
as begin
    -- NOTE:  This program calculates availability based on the program and sponsorships.

    -- It does NOT currently consider location status.


    declare @AVAILABILITYCODE tinyint
    declare @SPONSORCOUNT int
    declare @SOLESPONSOR bit
    declare @SPONSORSPEROPPORTUNITY int

    select    @SPONSORCOUNT=count(SPONSORSHIP.ID),
                        @SOLESPONSOR=max(isnull(cast(ISSOLESPONSORSHIP as tinyint),0)),
                        @SPONSORSPEROPPORTUNITY=min(dbo.UFN_SPONSORSHIPOPPORTUNITY_SPONSORSPEROPPORTUNITY(SPONSORSHIPOPPORTUNITYGROUP.ID,SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID))
    from dbo.SPONSORSHIPOPPORTUNITY
        inner join dbo.SPONSORSHIPOPPORTUNITYGROUP on SPONSORSHIPOPPORTUNITYGROUP.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID
        left outer join dbo.SPONSORSHIP on SPONSORSHIP.SPONSORSHIPOPPORTUNITYID = SPONSORSHIPOPPORTUNITY.ID and STATUSCODE in(0,1)
    where SPONSORSHIPOPPORTUNITY.ID = @ID

    if @SOLESPONSOR = 1 or
       (@SPONSORCOUNT >= @SPONSORSPEROPPORTUNITY and @SPONSORSPEROPPORTUNITY > 0)
        return 2;

    return 0;
end