UFN_SPONSORSHIP_REACTIVATEALLOWED

CREATE function dbo.UFN_SPONSORSHIP_REACTIVATEALLOWED(
    @SPONSORSHIPID uniqueidentifier
)
returns bit
with execute as caller
as begin
    declare @STATUSCODE tinyint
    declare @ACTIONCODE tinyint
    declare @ELIGIBILITYCODE tinyint
    declare @AVAILABILITYCODE tinyint
    declare @ISSOLESPONSORSHIP bit
    declare @SPONSORSHIPOPPORTUNITYID uniqueidentifier
  declare @CONSTITUENTID uniqueidentifier

    select @STATUSCODE = SPONSORSHIP.STATUSCODE,
           @ACTIONCODE = LASTTRANSACTION.ACTIONCODE,
           @ELIGIBILITYCODE = SPONSORSHIPOPPORTUNITY.ELIGIBILITYCODE,
           @AVAILABILITYCODE = SPONSORSHIPOPPORTUNITY.AVAILABILITYCODE,
           @ISSOLESPONSORSHIP = SPONSORSHIP.ISSOLESPONSORSHIP,
           @SPONSORSHIPOPPORTUNITYID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID,
       @CONSTITUENTID = SPONSORSHIP.CONSTITUENTID
    from dbo.SPONSORSHIP
    inner join dbo.SPONSORSHIPTRANSACTION LASTTRANSACTION on LASTTRANSACTION.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and LASTTRANSACTION.CONTEXTSPONSORSHIPID = SPONSORSHIP.ID and LASTTRANSACTION.TRANSACTIONSEQUENCE = (select max(TRANSACTIONSEQUENCE) from dbo.SPONSORSHIPTRANSACTION MAXSEQUENCE where MAXSEQUENCE.SPONSORSHIPCOMMITMENTID = LASTTRANSACTION.SPONSORSHIPCOMMITMENTID and MAXSEQUENCE.CONTEXTSPONSORSHIPID = LASTTRANSACTION.CONTEXTSPONSORSHIPID)
    inner join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIPOPPORTUNITY.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID
    where SPONSORSHIP.ID = @SPONSORSHIPID;

    if @ISSOLESPONSORSHIP = 1
    begin
        declare @SPONSORCOUNT smallint

        select @SPONSORCOUNT = count(*)
        from dbo.SPONSORSHIP
        where SPONSORSHIPOPPORTUNITYID = @SPONSORSHIPOPPORTUNITYID
        and STATUSCODE in(0,1)

        if @SPONSORCOUNT > 0
            return 0;
    end

  --if there's already a sponsorship for the same sponsor with the same opportunity then don't allow re-activation

  if exists (select 'x' from dbo.SPONSORSHIP where CONSTITUENTID = @CONSTITUENTID and SPONSORSHIPOPPORTUNITYID = @SPONSORSHIPOPPORTUNITYID and STATUSCODE = 1)
    return 0;

    if @STATUSCODE = 2 and            -- sponsorship is inactive

       @ACTIONCODE in(2,3) and        -- last action was cancel or terminate

       @ELIGIBILITYCODE = 1 and        -- opportunity is eligible

       @AVAILABILITYCODE = 0        -- opportunity is available

        return 1;

    return 0;
end