USP_SIMPLEDATALIST_REVENUERECOGNITIONRECIPIENTSANDDONOR

CREATE procedure dbo.USP_SIMPLEDATALIST_REVENUERECOGNITIONRECIPIENTSANDDONOR
(
  @REVENUEID uniqueidentifier
)
as
  set nocount on

  select
    VALUE,
    LABEL
  from
  (
    select
      RR.CONSTITUENTID as VALUE,
      NF.NAME as LABEL,
      C.KEYNAME,
      C.FIRSTNAME
    from dbo.REVENUERECOGNITION RR
    inner join dbo.REVENUESPLIT RS on RR.REVENUESPLITID = RS.ID
    inner join dbo.CONSTITUENT C on RR.CONSTITUENTID = C.ID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                where RS.REVENUEID = @REVENUEID

    union

    select
      RC.CONSTITUENTID as VALUE,
      NF.NAME as LABEL,
      C.KEYNAME,
      C.FIRSTNAME
    from dbo.RECOGNITIONCREDIT RC
    inner join dbo.DONORCHALLENGEENCUMBERED on RC.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
    inner join dbo.REVENUESPLIT RS on DONORCHALLENGEENCUMBERED.REVENUESPLITID = RS.ID
    inner join dbo.CONSTITUENT C on RC.CONSTITUENTID = C.ID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                where RS.REVENUEID = @REVENUEID

    union

    select
      R.CONSTITUENTID as VALUE,
      NF.NAME as LABEL,
      C.KEYNAME,
      C.FIRSTNAME
    from dbo.REVENUE R
    inner join dbo.CONSTITUENT C on R.CONSTITUENTID = C.ID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
    where R.ID = @REVENUEID
  ) as C

  order by C.KEYNAME, C.FIRSTNAME