UFN_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS

CREATE function [dbo].[UFN_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS]
(
  @REVENUESPLITID uniqueidentifier
)
returns @RESULT table
(
    ID uniqueidentifier,
    CONSTITUENTID uniqueidentifier,
    CONSTITUENT nvarchar(255),
    DONOR nvarchar(255),
    RECOGNITIONCREDITFKID uniqueidentifier,
    AMOUNT money,
    EFFECTIVEDATE datetimeoffset,
    REVENUERECOGNITIONTYPECODEID uniqueidentifier,
    RECOGNITIONTYPE nvarchar(255),
    BASECURRENCYID uniqueidentifier,
    GROSSAMOUNT money,
    DESIGNATIONID uniqueidentifier,
    DESIGNATION nvarchar(255),
    DONORCHALLENGERECOGNITIONTYPECODE tinyint
)
as
begin

    declare @LINEITEMS table
    (
        ID uniqueidentifier,
        FINANCIALTRANSACTIONID uniqueidentifier,
        DONORCONSTITUENTID uniqueidentifier,
        DESIGNATIONID uniqueidentifier,
        DONORCHALLENGERECOGNITIONTYPECODE tinyint,
        DONORCHALLENGEENCUMBERED_AMOUNT money,
        DONORCHALLENGE_NAME nvarchar(255)
    );

    insert into @LINEITEMS
    select
      FTLI.ID,
      FTLI.FINANCIALTRANSACTIONID,
      FT.CONSTITUENTID as DONORCONSTITUENTID,
      RSE.DESIGNATIONID,
      0 as DONORCHALLENGERECOGNITIONTYPECODE,
      null as DONORCHALLENGEENCUMBERED_AMOUNT,
      null DONORCHALLENGE_NAME
    from
      dbo.FINANCIALTRANSACTIONLINEITEM FTLI
    inner join
      dbo.REVENUESPLIT_EXT RSE on RSE.ID = FTLI.ID
    inner join
      dbo.FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
    inner join
      dbo.CONSTITUENT on CONSTITUENT.ID = FT.CONSTITUENTID
    where
      FTLI.ID = @REVENUESPLITID

    union all

    select
      FTLI.ID,
      FTLI.FINANCIALTRANSACTIONID,
      DC.EXTERNALSPONSORID as DONORCONSTITUENTID,
      RSE.DESIGNATIONID,
      1 as DONORCHALLENGERECOGNITIONTYPECODE,
      DCE.AMOUNT as DONORCHALLENGEENCUMBERED_AMOUNT,
      DC.NAME DONORCHALLENGE_NAME
    from
      dbo.FINANCIALTRANSACTIONLINEITEM FTLI
    inner join
      dbo.REVENUESPLIT_EXT RSE on RSE.ID = FTLI.ID
    inner join
      dbo.DONORCHALLENGEENCUMBERED DCE on DCE.MATCHEDREVENUEID = FTLI.FINANCIALTRANSACTIONID and
                                          DCE.DESIGNATIONID = RSE.DESIGNATIONID
    inner join
      dbo.DONORCHALLENGE DC on DC.ID = DCE.DONORCHALLENGEID
    inner join
      dbo.CONSTITUENT on CONSTITUENT.ID = DC.EXTERNALSPONSORID
    inner join
      dbo.DESIGNATION on DESIGNATION.ID = RSE.DESIGNATIONID
    where
      DCE.REVENUESPLITID = @REVENUESPLITID

  insert into @RESULT
  select
    RECOGNITION.ID,
    RECOGNITION.CONSTITUENTID,
    RECOGNITION.NAME as CONSTITUENT,
    case LI.DONORCHALLENGERECOGNITIONTYPECODE
      when 1
        then NF.NAME + ' matched ' + dbo.UFN_CURRENCY_GETSYMBOL(RECOGNITION.BASECURRENCYID) + cast(LI.DONORCHALLENGEENCUMBERED_AMOUNT as varchar(20)) + ' for ' + LI.DONORCHALLENGE_NAME
      else
        NF.NAME
    end as DONOR,
    LI.ID as RECOGNITIONCREDITFKID,
    RECOGNITION.AMOUNT,
    RECOGNITION.EFFECTIVEDATE,
    RECOGNITION.REVENUERECOGNITIONTYPECODEID,
    RECOGNITION.RECOGNITIONTYPE,
    RECOGNITION.BASECURRENCYID,
    RECOGNITION.GROSSAMOUNT,
    DESIGNATION.ID as DESIGNATIONID,
    DESIGNATION.NAME as DESIGNATION,
    LI.DONORCHALLENGERECOGNITIONTYPECODE
  from
    @LINEITEMS LI
  cross apply
    dbo.UFN_REVENUE_GETRECOGNITIONS_2(LI.ID) as RECOGNITION
  cross apply
    dbo.UFN_CONSTITUENT_DISPLAYNAME(LI.DONORCONSTITUENTID) NF
  left join
    dbo.DESIGNATION on LI.DESIGNATIONID = DESIGNATION.ID
  where
    LI.DONORCHALLENGERECOGNITIONTYPECODE = 0 or
    (
      LI.DONORCHALLENGERECOGNITIONTYPECODE = 1 and
      (select CONSTITUENTID from dbo.FINANCIALTRANSACTION where ID = LI.FINANCIALTRANSACTIONID) <> RECOGNITION.CONSTITUENTID
    )

  union all

  --recognition credits from internally sponsored donor challenge

  select
    RECOGNITIONCREDIT.ID,
    RECOGNITIONCREDIT.CONSTITUENTID,
    NF.NAME as CONSTITUENT,
    DONORCHALLENGESPONSORCODE.DESCRIPTION  + ' matched ' + dbo.UFN_CURRENCY_GETSYMBOL(RECOGNITIONCREDIT.BASECURRENCYID) + cast(DONORCHALLENGEENCUMBERED.AMOUNT as varchar(20)) + ' for ' + DONORCHALLENGE.NAME as DONOR,
    DONORCHALLENGEENCUMBERED.ID as RECOGNITIONCREDITFKID,
    RECOGNITIONCREDIT.AMOUNT,
    RECOGNITIONCREDIT.EFFECTIVEDATE,
    RECOGNITIONCREDIT.USERRECOGNITIONTYPECODEID as REVENUERECOGNITIONTYPECODEID,
    REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONTYPE,
    RECOGNITIONCREDIT.BASECURRENCYID, 
    RECOGNITIONCREDIT.AMOUNT as GROSSAMOUNT,
    DESIGNATION.ID as DESIGNATIONID,
    DESIGNATION.NAME as DESIGNATION,
    2 as DONORCHALLENGERECOGNITIONTYPECODE --Internal

  from 
    dbo.RECOGNITIONCREDIT
  inner join
    dbo.CONSTITUENT on RECOGNITIONCREDIT.CONSTITUENTID = CONSTITUENT.ID
  cross apply
    dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
  inner join
    dbo.DESIGNATION on RECOGNITIONCREDIT.DESIGNATIONID = DESIGNATION.ID
  inner join
    dbo.DONORCHALLENGEENCUMBERED on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
  inner join
    dbo.DONORCHALLENGE on DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = DONORCHALLENGE.ID
  left join
    dbo.DONORCHALLENGESPONSORCODE on DONORCHALLENGE.INTERNALSPONSORCODEID = DONORCHALLENGESPONSORCODE.ID
  left join
    dbo.REVENUERECOGNITIONTYPECODE on RECOGNITIONCREDIT.USERRECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
  where
    DONORCHALLENGEENCUMBERED.REVENUESPLITID = @REVENUESPLITID and
    RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1 --donor challenge


    return;
end