UFN_REVENUE_GETRECOGNITIONS_FORREVENUE

CREATE function dbo.UFN_REVENUE_GETRECOGNITIONS_FORREVENUE(@REVENUEID uniqueidentifier)
returns table
as
  return 
  (
    select 
        REVENUERECOGNITION.[ID],
        REVENUERECOGNITION.[CONSTITUENTID],
        CONSTITUENT.NAME,
        REVENUERECOGNITION.[AMOUNT],
        -- If this is a recurring gift, just use the revenue date as the effective date.  If the actual

        -- recognition effective date were used then the sorting could be off since the effective date isn't displayed

        -- for that revenue type.

        case when FINANCIALTRANSACTION.TYPECODE <> 2 then [EFFECTIVEDATE] else convert(datetime,FINANCIALTRANSACTION.DATE) end 'EFFECTIVEDATE',
        [REVENUERECOGNITIONTYPECODEID],
        REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONTYPE,
      case FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT
      when 0 then 0
      else
        case FINANCIALTRANSACTION.TYPECODE 
                when 1 then 
            case when REVENUERECOGNITION.AMOUNT > FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNT_2(FINANCIALTRANSACTIONLINEITEM.ID, REVENUERECOGNITION.BASECURRENCYID) - FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT + REVENUERECOGNITION.AMOUNT 
            else REVENUERECOGNITION.AMOUNT/FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT * (dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNT_2(FINANCIALTRANSACTIONLINEITEM.ID, REVENUERECOGNITION.BASECURRENCYID) - FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT) + REVENUERECOGNITION.AMOUNT end
          when 2 then 
            case when REVENUERECOGNITION.AMOUNT > FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT then dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMAMOUNT_2(FINANCIALTRANSACTIONLINEITEM.ID, 1, 0) + REVENUERECOGNITION.AMOUNT 
            else REVENUERECOGNITION.AMOUNT/FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT * dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMAMOUNT_2(FINANCIALTRANSACTIONLINEITEM.ID, 1, 0) + REVENUERECOGNITION.AMOUNT end
          else REVENUERECOGNITION.AMOUNT
        end
          end as GROSSAMOUNT,
          REVENUERECOGNITION.BASECURRENCYID,
          REVENUERECOGNITION.ORGANIZATIONAMOUNT,
          REVENUERECOGNITION.ORGANIZATIONEXCHANGERATEID,
      FINANCIALTRANSACTIONLINEITEM.ID REVENUESPLITID
    from 
          dbo.REVENUERECOGNITION
          inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUERECOGNITION.CONSTITUENTID
          inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
          inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
          inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
          left join dbo.REVENUERECOGNITIONTYPECODE on REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
    where 
          FINANCIALTRANSACTION.ID = @REVENUEID
          and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
  )