USP_DATALIST_PLANNEDGIFTREVENUE

CREATE procedure dbo.USP_DATALIST_PLANNEDGIFTREVENUE
(
  @PLANNEDGIFTID uniqueidentifier
)
as
  set nocount on;

  --Associated revenue

  select
    FT.ID as REVENUEID,
    FT.CALCULATEDDATE as REVENUEDATE,
    FT.TRANSACTIONAMOUNT as REVENUEAMOUNT, 
    FT.[TYPE] as REVENUETRANSACTIONTYPE,
    FT.CONSTITUENTID as REVENUECONSTITUENTID,
    NF.NAME as REVENUECONSTITUENT,
    null as PARENTID,
    dbo.UFN_PLEDGE_GETAMOUNTPAID(FT.ID) as TOTALPAYMENTS,
    FT.TRANSACTIONCURRENCYID as REVENUETRANSACTIONCURRENCYID
  from
    dbo.PLANNEDGIFTREVENUE
  inner join
    dbo.FINANCIALTRANSACTION as FT on FT.ID = PLANNEDGIFTREVENUE.REVENUEID
  outer apply
    dbo.UFN_CONSTITUENT_DISPLAYNAME(FT.CONSTITUENTID) as NF
  where
    PLANNEDGIFTREVENUE.ID = @PLANNEDGIFTID and
    FT.DELETEDON is null

  union all

  --Payments to associated revenue

  select 
    FT.ID as REVENUEID,
    FT.CALCULATEDDATE as REVENUEDATE,
    INSTALLMENTSPLITPAYMENT.AMOUNT as REVENUEAMOUNT, 
    FT.[TYPE] as REVENUETRANSACTIONTYPE,
    FT.CONSTITUENTID as REVENUECONSTITUENTID,
    NF.NAME as REVENUECONSTITUENT,
    PLANNEDGIFTREVENUE.REVENUEID as PARENTID,
    null as TOTALPAYMENTS,
    INSTALLMENTSPLITPAYMENT.APPLICATIONCURRENCYID as REVENUETRANSACTIONCURRENCYID
  from
    dbo.PLANNEDGIFTREVENUE 
  inner join
    dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PLEDGEID = PLANNEDGIFTREVENUE.REVENUEID
  inner join
    dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on FTLI.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
  inner join
    dbo.FINANCIALTRANSACTION as FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
  outer apply
    dbo.UFN_CONSTITUENT_DISPLAYNAME(FT.CONSTITUENTID) as NF
  where
    PLANNEDGIFTREVENUE.ID = @PLANNEDGIFTID and
    FT.DELETEDON is null and
    FTLI.DELETEDON is null

  union all

  --Payments directly to the planned gift

  select
    FT.ID as REVENUEID,
    FT.CALCULATEDDATE as REVENUEDATE,
    sum(FTLI.TRANSACTIONAMOUNT) as REVENUEAMOUNT, 
    FT.[TYPE] as REVENUETRANSACTIONTYPE,
    FT.CONSTITUENTID as REVENUECONSTITUENTID,
    NF.NAME as REVENUECONSTITUENT,
    null as PARENTID,
    null as TOTALPAYMENTS,
    FT.TRANSACTIONCURRENCYID as REVENUETRANSACTIONCURRENCYID
  from
    dbo.PLANNEDGIFTREVENUESPLIT
  inner join
    dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on FTLI.ID = PLANNEDGIFTREVENUESPLIT.REVENUESPLITID
  inner join
    dbo.FINANCIALTRANSACTION as FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
  outer apply
    dbo.UFN_CONSTITUENT_DISPLAYNAME(FT.CONSTITUENTID) as NF
  where
    PLANNEDGIFTREVENUESPLIT.PLANNEDGIFTID = @PLANNEDGIFTID and
    FT.DELETEDON is null and
    FTLI.DELETEDON is null
  group by
    FT.ID, FT.CALCULATEDDATE, FT.[TYPE], FT.CONSTITUENTID, FT.TRANSACTIONCURRENCYID, NF.NAME;