USP_DATALIST_FAFEVENTSPONSOR

List of sponsors in an event.

Parameters

Parameter	Parameter Type	Mode	Description
@EVENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@STATUS	nvarchar(1)	IN	Status
@SPONSORNAME	nvarchar(100)	IN	Sponsor name
@SPONSORID	nvarchar(14)	IN	Sponsorship ID
@MAXROWS	int	IN	Input parameter indicating the maximum number of rows to return.
@SITEFILTERMODE	tinyint	IN
@SITESSELECTED	xml	IN
@SECURITYFEATUREID	uniqueidentifier	IN
@SECURITYFEATURETYPE	tinyint	IN
@CURRENTAPPUSERID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_DATALIST_FAFEVENTSPONSOR
(
  @EVENTID uniqueidentifier,
  @STATUS nvarchar(1) = null,
  @SPONSORNAME nvarchar(100) = '',
  @SPONSORID nvarchar(14) = '',
  @MAXROWS int = 1000,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
    set nocount on;

    select top (@MAXROWS) ES.ID as ID,
           isnull(OC.NAME, IC.NAME) as Name,
           ST.NAME as SponsorshipOption,
           ES.FEESAMOUNT as Amount,           
           ES.DATEADDED as [Date],
           dbo.UFN_SPONSOR_GETBENEFITLIST_BYSPONSORID(ES.ID) as Benefits,
           ES.LOOKUPID as SponsorshipID,
           isnull(OE.EMAILADDRESS, IE.EMAILADDRESS) as EmailAddress,
           isnull(OP.NUMBER, IP.NUMBER) as PhoneNumber,
           case when ES.ISCANCELLED = 0 then 'Active' else 'Canceled' end as [Status],
           ES.EVENTSPONSORSHIPURL as URL
    from dbo.EVENTSPONSOR ES(nolock)
    left outer join dbo.EVENTSPONSORSHIPTYPE ST (nolock)
        on ST.ID = ES.EVENTSPONSORSHIPTYPEID
    left outer join dbo.CONSTITUENT IC(nolock)
        on IC.ID = ES.CONSTITUENTID
    left outer join dbo.EMAILADDRESS IE(nolock)
        on IE.CONSTITUENTID = IC.ID and IE.ISPRIMARY = 1
    left outer join dbo.PHONE IP(nolock)
        on IP.CONSTITUENTID = IC.ID and IP.ISPRIMARY = 1
    left outer join dbo.CONSTITUENT OC(nolock)
        on OC.ID = ES.ORGANIZATIONCONSTITUENTID
    left outer join dbo.EMAILADDRESS OE(nolock)
        on OE.CONSTITUENTID = OC.ID and OE.ISPRIMARY = 1
    left outer join dbo.PHONE OP(nolock)
        on OP.CONSTITUENTID = OC.ID and OP.ISPRIMARY = 1
    where ES.EVENTID = @EVENTID
    and ES.ISCANCELLED = isnull(@STATUS, ES.ISCANCELLED)
    and (OC.NAME like @SPONSORNAME + '%' or IC.NAME like @SPONSORNAME + '%')
    and ES.LOOKUPID like case when @SPONSORID = '' then ES.LOOKUPID else '%' + @SPONSORID + '%' end
  -- site security
  and
  (
    exists
    (
      select 1 from UFN_SITEID_MAPFROM_EVENTID(ES.EVENTID) EVENTSITE
      where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null)))
    )
  )
  -- site filter
  and
  (
    @SITEFILTERMODE = 0
    or
    exists
    (
      select 1 from UFN_SITEID_MAPFROM_EVENTID(ES.EVENTID) EVENTSITE
        where EVENTSITE.SITEID in
        (
          select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
        )
    )
  )

  order by Name;