UFN_RECOGNITIONCREDIT_GETRECOGNITIONS

CREATE function dbo.UFN_RECOGNITIONCREDIT_GETRECOGNITIONS
(
  @INCLUDEREVENUERECOGNTIONS bit = 0,
  @REVENUERECOGNITIONTYPECODEID uniqueidentifier = null,
  @CURRENCYID uniqueidentifier = null
)
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 
    ( select dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() as ID) as ORGANIZATIONCURRENCY
    inner join dbo.CURRENCY on CURRENCY.ID = isnull(@CURRENCYID, ORGANIZATIONCURRENCY.ID)
    cross apply dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(CURRENCY.ID,ORGANIZATIONCURRENCY.ID,CURRENCY.DECIMALDIGITS,CURRENCY.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 (@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
    ( select dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() as ID) as ORGANIZATIONCURRENCY
    inner join dbo.CURRENCY on CURRENCY.ID = isnull(@CURRENCYID, ORGANIZATIONCURRENCY.ID)
    cross apply dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(CURRENCY.ID,ORGANIZATIONCURRENCY.ID,CURRENCY.DECIMALDIGITS,CURRENCY.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 (@REVENUERECOGNITIONTYPECODEID is null or RECOGNITIONCREDIT.USERRECOGNITIONTYPECODEID = @REVENUERECOGNITIONTYPECODEID)
    and RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1
)