UFN_RECOGNITIONCREDIT_GETRECOGNITIONS_2

create function dbo.UFN_RECOGNITIONCREDIT_GETRECOGNITIONS_2
(
  @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 
        (@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 
        (@REVENUERECOGNITIONTYPECODEID is null or RECOGNITIONCREDIT.USERRECOGNITIONTYPECODEID = @REVENUERECOGNITIONTYPECODEID)
        and RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1
)