UFN_SPONSORSHIP_PROGRAMANDOPPORTUNITYCONSISTENT

create function dbo.UFN_SPONSORSHIP_PROGRAMANDOPPORTUNITYCONSISTENT(
    @SPONSORSHIPPROGRAMID uniqueidentifier,
    @SPONSORSHIPOPPORTUNITYID uniqueidentifier
)
returns bit
with execute as caller
as begin
    declare @VALID bit
    declare @FILTERLOCATIONCODE tinyint
    declare @FILTERLOCATIONS xml
    declare @OPPORTUNITYLOCATIONID uniqueidentifier

    select @VALID = case when SPONSORSHIPPROGRAM.SPONSORSHIPOPPORTUNITYGROUPID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID then 1 else 0 end,
           @FILTERLOCATIONCODE = SPONSORSHIPPROGRAM.FILTERLOCATIONCODE,
           @FILTERLOCATIONS = SPONSORSHIPPROGRAM.FILTERLOCATIONS,
           @OPPORTUNITYLOCATIONID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID
    from dbo.SPONSORSHIPPROGRAM
    inner join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIPOPPORTUNITY.ID = @SPONSORSHIPOPPORTUNITYID
    where SPONSORSHIPPROGRAM.ID = @SPONSORSHIPPROGRAMID;

    if @VALID = 1 and @FILTERLOCATIONCODE <> 0
    begin
        if @FILTERLOCATIONCODE = 1
        begin
            if not exists(select 'x'
                            from dbo.UFN_SPONSORSHIPPROGRAM_FILTERLOCATIONS(@FILTERLOCATIONS) X
                            inner join dbo.SPONSORSHIPLOCATION FILTERLOCATION on FILTERLOCATION.ID = X.SPONSORSHIPLOCATIONID
                          inner join dbo.SPONSORSHIPLOCATION OPPORTUNITYLOCATION on OPPORTUNITYLOCATION.ID = @OPPORTUNITYLOCATIONID
                            where OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(FILTERLOCATION.HIERARCHYPATH) = 1)
                set @VALID = 0;
        end
        if @FILTERLOCATIONCODE = 2
        begin
            if exists(select 'x'
                        from dbo.UFN_SPONSORSHIPPROGRAM_FILTERLOCATIONS(@FILTERLOCATIONS) X
                        inner join dbo.SPONSORSHIPLOCATION FILTERLOCATION on FILTERLOCATION.ID = X.SPONSORSHIPLOCATIONID
                      inner join dbo.SPONSORSHIPLOCATION OPPORTUNITYLOCATION on OPPORTUNITYLOCATION.ID = @OPPORTUNITYLOCATIONID
                        where OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(FILTERLOCATION.HIERARCHYPATH) = 1)
                set @VALID = 0;
        end
    end

    return @VALID
end