UFN_CONSTITUENT_GETOPPORTUNITIES

CREATE function dbo.UFN_CONSTITUENT_GETOPPORTUNITIES
(
  @CONSTITUENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
returns table
as
  return
  (
    -- This function serves the ConstituentSummaryOpportunitiesTile.View.xml, which limits the number of records returned.

    -- If you change the number of records returned here, you must also update the tile's UIModel to allow one less than the new limit.

    select top 501
      OPPORTUNITY.ID,
      PROSPECTPLAN.ID as PLANID,
      PROSPECTPLANTYPECODE.DESCRIPTION as PLANTYPE,
      PROSPECTPLAN.NAME as PLANNAME,
      PROSPECTPLAN.STARTDATE,
      case
        when PROSPECTPLAN.PRIMARYMANAGERENDDATE < dbo.UFN_DATE_GETEARLIESTTIME(getdate()) then null
        else PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID
      end as PRIMARYMANAGERID,
      case
        when PROSPECTPLAN.PRIMARYMANAGERENDDATE < dbo.UFN_DATE_GETEARLIESTTIME(getdate()) then null
        else NF.NAME
      end as PRIMARYMANAGERNAME,
      PROSPECTPLAN.BASECURRENCYID,
      OPPORTUNITY.AMOUNT,
      OPPORTUNITY.STATUS
    from dbo.PROSPECTPLAN
      inner join dbo.PROSPECTPLANTYPECODE on PROSPECTPLAN.PROSPECTPLANTYPECODEID = PROSPECTPLANTYPECODE.ID
      left join dbo.OPPORTUNITY on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID) NF
    where
      PROSPECTPLAN.PROSPECTID = @CONSTITUENTID and
      PROSPECTPLAN.ISACTIVE = 1 and
      (
        select count(*)
        from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.ID) as PLANSITE
        where
        (
          dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or
          exists
          (
            select 1
            from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)
            where SITEID=[PLANSITE].[SITEID] or (SITEID is null and [PLANSITE].[SITEID] is null)
          )
        )
      ) > 0
  )