USP_DATALIST_PENDINGGIFTSFORRECOGNITION

CREATE procedure dbo.USP_DATALIST_PENDINGGIFTSFORRECOGNITION
(
  @CONSTITUENTID uniqueidentifier,
  @EVENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as
  set nocount on;

  declare @ISADMIN bit;
  set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

    select 
    REV.ID, 
    REV.TYPE as APPLICATIONTYPE, 
    case when RPM.PAYMENTMETHODCODE is null or RPM.PAYMENTMETHODCODE = 9 THEN
      coalesce(CURRENCY.CURRENCYSYMBOL, '$') + CAST(REV.TRANSACTIONAMOUNT as nvarchar(100)) + ' pending gift ' 
        + case when VBAL.BALANCE <> REV.TRANSACTIONAMOUNT then '(' + coalesce(CURRENCY.CURRENCYSYMBOL, '$') + cast((REV.TRANSACTIONAMOUNT - VBAL.BALANCE) as nvarchar(15)) + ' received) ' else '' end 
        + ' from ' + NF.NAME
    else
      case when len(coalesce(CPM.CHECKNUMBER, REVENUEOFFLINEDONATION.CHECKNUMBER, '')) > 0 then
        coalesce(CURRENCY.CURRENCYSYMBOL, '$') + CAST(REV.TRANSACTIONAMOUNT as nvarchar(100)) + ' pending gift ' 
    + case when VBAL.BALANCE <> REV.TRANSACTIONAMOUNT then '(' + coalesce(CURRENCY.CURRENCYSYMBOL, '$') + cast((REV.TRANSACTIONAMOUNT - VBAL.BALANCE) as nvarchar(15)) + ' received) ' else '' end 
       + ' from ' + NF.NAME + ' (' + RPM.PAYMENTMETHOD + ' #' + coalesce(CPM.CHECKNUMBER, REVENUEOFFLINEDONATION.CHECKNUMBER, '') + ')'
      else
        coalesce(CURRENCY.CURRENCYSYMBOL, '$') + CAST(REV.TRANSACTIONAMOUNT as nvarchar(100)) + ' pending gift ' 
    + case when VBAL.BALANCE <> REV.TRANSACTIONAMOUNT then '(' + coalesce(CURRENCY.CURRENCYSYMBOL, '$') + cast((REV.TRANSACTIONAMOUNT - VBAL.BALANCE) as nvarchar(15)) + ' received) ' else '' end 
       + ' from ' + NF.NAME + ' (' + RPM.PAYMENTMETHOD + ')'
      end
    end as DESCRIPTION, 
    EM.NAME as RECOGNITION, 
    REV.DATE, 
    REV.TRANSACTIONAMOUNT as AMOUNT,
    17 as APPLICATIONTYPECODE,
    VBAL.BALANCE as BALANCE,
    (REV.TRANSACTIONAMOUNT - VBAL.BALANCE) as PAID
  from dbo.FINANCIALTRANSACTION REV (nolock) 
    join dbo.REVENUE_EXT REVX (nolock) on REVX.ID = REV.ID
    join dbo.EVENT E (nolock) on E.APPEALID = REVX.APPEALID and E.ID = @EVENTID
    join dbo.FINANCIALTRANSACTIONLINEITEM RS (nolock) on RS.FINANCIALTRANSACTIONID = REV.ID
    left join dbo.REVENUEPAYMENTMETHOD RPM With (NOLOCK) on RPM.REVENUEID = REV.ID 
    left join dbo.CHECKPAYMENTMETHODDETAIL CPM with (nolock) on CPM.ID = RPM.ID
    left join dbo.REVENUERECOGNITION RR (nolock) on RR.REVENUESPLITID = RS.ID 
    left join dbo.REVENUEOFFLINEDONATION (nolock) on REVENUEOFFLINEDONATION.ID = REV.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RR.CONSTITUENTID) EM
    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REV.CONSTITUENTID) NF
    left join V_QUERY_REVENUE_PLEDGEBALANCE VBAL (nolock) on VBAL.ID = [REV].ID
    left join dbo.CURRENCY (nolock) on CURRENCY.ID = REV.TRANSACTIONCURRENCYID
  where [REV].TYPECODE = 9
    and VBAL.BALANCE > 0
    and RR.CONSTITUENTID = @CONSTITUENTID
    and exists(--Check site security

      select 1
      from dbo.UFN_SITEID_MAPFROM_REVENUEID(REV.ID) as SITE
      where (@ISADMIN = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)))
    )