USP_DATALIST_OPPORTUNITYPROGRAMS

Retrieve program records associated with an opportunity.

Parameters

Parameter	Parameter Type	Mode	Description
@CONTEXTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.

Definition

Copy



CREATE procedure dbo.USP_DATALIST_OPPORTUNITYPROGRAMS(@CONTEXTID uniqueidentifier)
as
    set nocount on;

      select SPONSORSHIPPROGRAM.ID, SPONSORSHIPPROGRAM.NAME
      from dbo.SPONSORSHIPPROGRAM inner join 
      SPONSORSHIPOPPORTUNITY on SPONSORSHIPPROGRAM.SPONSORSHIPOPPORTUNITYGROUPID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID
      inner join SPONSORSHIPLOCATION OPPORTUNITYLOCATION on OPPORTUNITYLOCATION.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID
      cross apply dbo.UFN_SPONSORSHIPPROGRAM_FILTERLOCATIONS(SPONSORSHIPPROGRAM.FILTERLOCATIONS) X
      inner join SPONSORSHIPLOCATION PROGRAMLOCATION on X.SPONSORSHIPLOCATIONID = PROGRAMLOCATION.ID
      where SPONSORSHIPOPPORTUNITY.ID = @CONTEXTID and 
      OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(PROGRAMLOCATION.HIERARCHYPATH) = 1 and SPONSORSHIPPROGRAM.FILTERLOCATION = 'In'

      union 

      select SPONSORSHIPPROGRAM.ID, SPONSORSHIPPROGRAM.NAME
      from dbo.SPONSORSHIPPROGRAM inner join 
      SPONSORSHIPOPPORTUNITY on SPONSORSHIPPROGRAM.SPONSORSHIPOPPORTUNITYGROUPID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID
      where SPONSORSHIPOPPORTUNITY.ID = @CONTEXTID and ISNULL(SPONSORSHIPPROGRAM.FILTERLOCATIONCODE,0) = 0