UFN_SPONSORSHIP_OFFERSOLESPONSORSHIP

create function dbo.UFN_SPONSORSHIP_OFFERSOLESPONSORSHIP(
    @SPONSORSHIPOPPORTUNITYGROUPID uniqueidentifier,
    @SPONSORSHIPLOCATIONID uniqueidentifier = null
)
returns bit
with execute as caller
as begin
    declare @OFFERSOLESPONSORSHIP bit
    declare @OVERRIDES xml

    select @OFFERSOLESPONSORSHIP = OFFERSOLESPONSORSHIP,
           @OVERRIDES = OVERRIDESPONSORSPEROPPORTUNITY
    from dbo.SPONSORSHIPOPPORTUNITYGROUP
    where ID = @SPONSORSHIPOPPORTUNITYGROUPID;

    if @OVERRIDES is not null
    begin
        if @SPONSORSHIPLOCATIONID is not null
            select @OFFERSOLESPONSORSHIP = OVERRIDES.OFFERSOLESPONSORSHIP
            from dbo.UFN_SPONSORSHIPOPPORTUNITYGROUP_OVERRIDESPONSORSPEROPPORTUNITY(@OVERRIDES) OVERRIDES
            inner join dbo.SPONSORSHIPLOCATION OPPORTUNITYLOCATION on OPPORTUNITYLOCATION.ID = @SPONSORSHIPLOCATIONID
            left outer join dbo.SPONSORSHIPLOCATION OVERRIDELOCATION on OVERRIDELOCATION.ID = OVERRIDES.SPONSORSHIPLOCATIONID
            where OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(OVERRIDELOCATION.HIERARCHYPATH) = 1;
        else
            set @OFFERSOLESPONSORSHIP = 0
    end

    return @OFFERSOLESPONSORSHIP
end