UFN_RECOGNITIONCREDIT_GETRECOGNITIONS_BYCONSTITUENT

create function dbo.UFN_RECOGNITIONCREDIT_GETRECOGNITIONS_BYCONSTITUENT
(
  @CONSTITUENTID uniqueidentifier,
  @INCLUDEREVENUERECOGNTIONS bit = 0,
  @REVENUERECOGNITIONTYPECODEID uniqueidentifier = null,
  @CURRENCYID uniqueidentifier,
  @DECIMALDIGITS tinyint,
  @ROUNDINGTYPECODE tinyint,
  @ORGANIZATIONCURRENCYID uniqueidentifier
)
returns table
as return
(
  select
    RR.ID,
    RR.REVENUESPLITID,
    RRC_NF.NAME,
    RRC.KEYNAME,
    RRC.FIRSTNAME,
    RRTC.DESCRIPTION,
    RR.EFFECTIVEDATE,
    RR.AMOUNT,
    RR.ORGANIZATIONAMOUNT,
    RR.BASECURRENCYID,
    RR.REVENUERECOGNITIONTYPECODEID,
    RR.CONSTITUENTID,
    RR.ORGANIZATIONEXCHANGERATEID,
    RR_INCURRENCY.DESIGNATIONID,
    -1 as RECOGNITIONCREDITTYPECODE,
    RR_INCURRENCY.AMOUNTINCURRENCY,
    RR.DATEADDED,
    RR.DATECHANGED,
    RR_INCURRENCY.TRANSACTIONTYPE,
    RR_INCURRENCY.TRANSACTIONTYPECODE,
    DONOR_NF.NAME as DONOR,
    RR_INCURRENCY.ORGANIZATIONEXCHANGERATEID as ORGANIZATIONEXCHANGERATEIDINCURRENCY,
    RR_INCURRENCY.REVENUEID,
    RR_INCURRENCY.APPLICATIONCODE,
    RR_INCURRENCY.REVENUESPLITTYPECODE
  from dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as RR_INCURRENCY
    inner join dbo.REVENUERECOGNITION as RR on RR.ID = RR_INCURRENCY.ID
    inner join dbo.CONSTITUENT RRC with (nolock) on RR.CONSTITUENTID = RRC.ID
    inner join dbo.CONSTITUENT DONOR with (nolock) on RR_INCURRENCY.REVENUECONSTITUENTID = DONOR.ID
    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(DONOR.ID) DONOR_NF
    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RRC.ID) RRC_NF
    left join dbo.REVENUERECOGNITIONTYPECODE RRTC on RR.REVENUERECOGNITIONTYPECODEID = RRTC.ID
  where 
    RRC.ID = @CONSTITUENTID and
    (
      @REVENUERECOGNITIONTYPECODEID is null or
      RR.REVENUERECOGNITIONTYPECODEID = @REVENUERECOGNITIONTYPECODEID
    ) and
    @INCLUDEREVENUERECOGNTIONS = 1

  union all

  select
    RECOGNITIONCREDIT.ID,
    DONORCHALLENGEENCUMBERED.REVENUESPLITID as REVENUESPLITID, --This should be null but we still store some info on the revenue, REVENUECAMPAIGN for example

    RRC_NF.NAME,
    RRC.KEYNAME,
    RRC.FIRSTNAME,
    RRTC.DESCRIPTION,
    RECOGNITIONCREDIT.EFFECTIVEDATE,
    RECOGNITIONCREDIT.AMOUNT,
    RECOGNITIONCREDIT.ORGANIZATIONAMOUNT,
    RECOGNITIONCREDIT.BASECURRENCYID,
    RECOGNITIONCREDIT.USERRECOGNITIONTYPECODEID as REVENUERECOGNITIONTYPECODEID,
    RECOGNITIONCREDIT.CONSTITUENTID,
    RECOGNITIONCREDIT.ORGANIZATIONEXCHANGERATEID,
    RECOGNITIONCREDIT.DESIGNATIONID,
    RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE,
    RECOGNITIONCREDIT_INCURRENCY.AMOUNTINCURRENCY,
    RECOGNITIONCREDIT.DATEADDED,
    RECOGNITIONCREDIT.DATECHANGED,
    'Donor challenge claim - Internal sponsor' as TYPE,
    8 as TYPECODE,
    DONORCHALLENGESPONSORCODE.DESCRIPTION as DONOR,
    RECOGNITIONCREDIT_INCURRENCY.ORGANIZATIONEXCHANGERATEID as ORGANIZATIONEXCHANGERATEIDINCURRENCY,
    RECOGNITIONCREDIT_INCURRENCY.REVENUEID,
    RECOGNITIONCREDIT_INCURRENCY.APPLICATIONCODE,
    RECOGNITIONCREDIT_INCURRENCY.REVENUESPLITTYPECODE
  from dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as RECOGNITIONCREDIT_INCURRENCY
    inner join dbo.RECOGNITIONCREDIT on RECOGNITIONCREDIT.ID = RECOGNITIONCREDIT_INCURRENCY.ID
    inner join dbo.DONORCHALLENGEENCUMBERED on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
    inner join dbo.CONSTITUENT RRC with (nolock) on RECOGNITIONCREDIT.CONSTITUENTID = RRC.ID
    inner join dbo.DONORCHALLENGE on DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = DONORCHALLENGE.ID
    inner join dbo.DONORCHALLENGESPONSORCODE on DONORCHALLENGE.INTERNALSPONSORCODEID = DONORCHALLENGESPONSORCODE.ID
    left join dbo.REVENUERECOGNITIONTYPECODE RRTC on RECOGNITIONCREDIT.USERRECOGNITIONTYPECODEID = RRTC.ID
 cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RRC.ID) RRC_NF
  where
    RRC.ID = @CONSTITUENTID and
    (
      @REVENUERECOGNITIONTYPECODEID is null or
      RECOGNITIONCREDIT.USERRECOGNITIONTYPECODEID = @REVENUERECOGNITIONTYPECODEID
    ) and
    RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1
)