UFN_OPPORTUNITY_GETSOLICITORS

CREATE function dbo.UFN_OPPORTUNITY_GETSOLICITORS
(
  @OPPORTUNITYID uniqueidentifier,
  @ONLYSELECTED bit = 0
)
returns table
as
return
  select
    OS.ID,
    OS.PLANFUNDRAISERID,
    NF.NAME as FUNDRAISERNAME,
    OS.FUNDRAISERTYPECODE,
    convert(bit, case when OS.ID is null then 0 else 1 end) as FUNDRAISERSELECTED
  from
  (
    select
      OPPORTUNITYSOLICITOR.ID,
      null as PLANFUNDRAISERID,
      0 as FUNDRAISERTYPECODE,
      PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID as CONSTITUENTID,
      OPPORTUNITY.ID as OPPORTUNITYID,
      PROSPECTPLAN.PRIMARYMANAGERENDDATE as PLANFUNDRAISERENDDATE
    from
      dbo.PROSPECTPLAN
      inner join dbo.OPPORTUNITY on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
      left join dbo.OPPORTUNITYSOLICITOR on OPPORTUNITYSOLICITOR.OPPORTUNITYID = OPPORTUNITY.ID and OPPORTUNITYSOLICITOR.FUNDRAISERTYPECODE = 0
    where
      PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID is not null

    union all

    select
      OPPORTUNITYSOLICITOR.ID,
      null as PLANFUNDRAISERID,
      1 as FUNDRAISERTYPECODE,
      PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID as CONSTITUENTID,
      OPPORTUNITY.ID as OPPORTUNITYID,
      PROSPECTPLAN.SECONDARYMANAGERENDDATE as PLANFUNDRAISERENDDATE
    from
      dbo.PROSPECTPLAN
      inner join dbo.OPPORTUNITY on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
      left join dbo.OPPORTUNITYSOLICITOR on OPPORTUNITYSOLICITOR.OPPORTUNITYID = OPPORTUNITY.ID and OPPORTUNITYSOLICITOR.FUNDRAISERTYPECODE = 1
    where
      PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID is not null

    union all

    select
      OPPORTUNITYSOLICITOR.ID,
      SECONDARYFUNDRAISER.ID as PLANFUNDRAISERID,
      2 as FUNDRAISERTYPECODE,
      SECONDARYFUNDRAISER.FUNDRAISERID as CONSTITUENTID,
      OPPORTUNITY.ID as OPPORTUNITYID,
      SECONDARYFUNDRAISER.DATETO as PLANFUNDRAISERENDDATE
    from
      dbo.SECONDARYFUNDRAISER
      inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID = SECONDARYFUNDRAISER.PROSPECTPLANID
      inner join dbo.OPPORTUNITY on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
      left join dbo.OPPORTUNITYSOLICITOR on OPPORTUNITYSOLICITOR.OPPORTUNITYID = OPPORTUNITY.ID and OPPORTUNITYSOLICITOR.FUNDRAISERTYPECODE = 2 and OPPORTUNITYSOLICITOR.SECONDARYSOLICITORID = SECONDARYFUNDRAISER.ID
  ) as OS
  outer apply
    dbo.UFN_CONSTITUENT_DISPLAYNAME(OS.CONSTITUENTID) NF
  where
    OS.OPPORTUNITYID = @OPPORTUNITYID and
    (OS.PLANFUNDRAISERENDDATE is null or OS.PLANFUNDRAISERENDDATE > getdate()) and
    (coalesce(@ONLYSELECTED, 0) = 0 or OS.ID is not null);