USP_SIMPLEDATALIST_SPONSORSHIPPROGRAMNOAFFILIATE

        create procedure dbo.USP_SIMPLEDATALIST_SPONSORSHIPPROGRAMNOAFFILIATE
        (
            @TYPE int = 0,
            @INCLUDEINACTIVE bit = 0,
            @EXCLUDELOCATION uniqueidentifier = null
        )as
            set nocount on;

            select 
                SPONSORSHIPPROGRAM.ID as VALUE, 
                SPONSORSHIPPROGRAM.NAME as LABEL
            from dbo.SPONSORSHIPPROGRAM
            inner join dbo.SPONSORSHIPOPPORTUNITYGROUP on SPONSORSHIPOPPORTUNITYGROUP.ID = SPONSORSHIPPROGRAM.SPONSORSHIPOPPORTUNITYGROUPID
            where SPONSORSHIPPROGRAM.ID not in (select ID from dbo.SPONSORSHIPAFFILIATEPROGRAM)
          and (@TYPE = 0 or SPONSORSHIPOPPORTUNITYGROUP.SPONSORSHIPOPPORTUNITYTYPECODE = @TYPE)
            and (SPONSORSHIPPROGRAM.ISINACTIVE = 0 or @INCLUDEINACTIVE = 1)
            and (@EXCLUDELOCATION is null or
                  SPONSORSHIPOPPORTUNITYGROUP.SPONSORSHIPLOCATIONID is null or
                  SPONSORSHIPOPPORTUNITYGROUP.SPONSORSHIPLOCATIONID not in(
                  select WITHIN.ID
                  from dbo.SPONSORSHIPLOCATION EXCLUDE
                  inner join dbo.SPONSORSHIPLOCATION WITHIN on WITHIN.HIERARCHYPATH.IsDescendantOf(EXCLUDE.HIERARCHYPATH) = 1
                  where EXCLUDE.ID = @EXCLUDELOCATION))
            order by SPONSORSHIPPROGRAM.SEQUENCE, SPONSORSHIPPROGRAM.NAME