UFN_RECOGNITIONPROGRAM_GETREVENUERECOGNITION_INCURRENCY

CREATE function dbo.UFN_RECOGNITIONPROGRAM_GETREVENUERECOGNITION_INCURRENCY
(
  @PROGRAMID uniqueidentifier,
  @STARTDATE datetime,
  @PROCESSDATE datetime,
  @PLANNEDGIFTCODE tinyint,
  @REVENUESELECTIONID uniqueidentifier,
  @CURRENCYID uniqueidentifier,
  @ORGANIZATIONCURRENCYID uniqueidentifier,
  @DECIMALDIGITS tinyint,
  @ROUNDINGTYPECODE tinyint
)
returns table
as
return
(
  select 
    RREC.CONSTITUENTID,
    case
      when R.TYPECODE = 4 and @PLANNEDGIFTCODE = 2 then 0
      else RREC.AMOUNTINCURRENCY
    end as AMOUNT,
    --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

    dateadd(s, row_number() over(partition by RREC.CONSTITUENTID order by RREC.CONSTITUENTID), cast(RREC.EFFECTIVEDATE as date)) as EFFECTIVEDATE,
    case
      when R.TYPECODE = 4 and @PLANNEDGIFTCODE = 2 then RREC.AMOUNTINCURRENCY -- Separate minimum amount for planned gifts

      else 0
    end as PLANNEDGIFTAMOUNT,
    null as YEARSTART,
    null as YEAREND,
    RREC.ID as REVENUERECOGNITIONID
  from dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RREC
  inner join dbo.FINANCIALTRANSACTIONLINEITEM RS with (nolock) on RREC.REVENUESPLITID = RS.ID
  inner join dbo.REVENUESPLIT_EXT RSE with (nolock) on RSE.ID = RS.ID
  inner join dbo.FINANCIALTRANSACTION R with (nolock) on RS.FINANCIALTRANSACTIONID = R.ID
  where
    RS.TYPECODE <> 1
    and R.DELETEDON is null 
    and RS.DELETEDON is null
    and (RREC.EFFECTIVEDATE >= @STARTDATE or @STARTDATE is null) 
    and (RREC.EFFECTIVEDATE <= @PROCESSDATE or @PROCESSDATE is null)
    and(
      exists(
        select RECOGNITIONPROGRAM.ID
        from dbo.RECOGNITIONPROGRAM with (nolock)
        cross apply GIFTTYPESFILTER.nodes('/GIFTTYPESFILTER/ITEM') T(c)
        where 
          RECOGNITIONPROGRAM.ID = @PROGRAMID 
          and(
            (R.TYPECODE = 0 and (T.c.value('(TYPECODEID)[1]','tinyint') = RSE.APPLICATIONCODE)) or -- Payment

            (T.c.value('(TYPECODEID)[1]','tinyint') = 21 and R.TYPECODE = 1) or --Pledge

            (T.c.value('(TYPECODEID)[1]','tinyint') = 22 and R.TYPECODE = 2) or --Recurring Gift

            (T.c.value('(TYPECODEID)[1]','tinyint') = 23 and R.TYPECODE = 3) or -- Matching Gift Claim

            (T.c.value('(TYPECODEID)[1]','tinyint') = 24 and R.TYPECODE = 7) --Auction donations

          )
      ) 
      or(R.TYPECODE = 4 and @PLANNEDGIFTCODE <> 0) -- Planned Gift, not separate

    )
    and(
      (
        select count(DESIGNATIONID) from dbo.RECOGNITIONPROGRAMDESIGNATION RPD with (nolock)
        where RPD.RECOGNITIONPROGRAMID = @PROGRAMID
      ) = 0 
      or exists(
        select ID from dbo.UFN_RECOGNITIONPROGRAM_GETDESIGNATIONS_ALL(@PROGRAMID) DS
        where RSE.DESIGNATIONID = DS.ID
      )
    )
    and(
      @REVENUESELECTIONID is null 
      or exists(
        select 1 from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@REVENUESELECTIONID) as SEL
        where SEL.ID = R.ID
      )
    )
    and dbo.UFN_RECOGNITIONPROGRAM_VALIDCONSTITUENT(@PROGRAMID, RREC.CONSTITUENTID) = 1
);