V_QUERY_REVENUE

/*
Generated by Blackbaud AppFx Platform
Date:  5/3/2024 2:17:54 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3700.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_REVENUE AS



select FT.ID
  ,FT.CONSTITUENTID as [CONSTITUENTID]
  ,cast(ft.[DATE] as date) as [DATE]
  ,FT.BASEAMOUNT as [AMOUNT]
  ,V_QUERY_REVENUE_PLEDGEBALANCE.BALANCE as [TRANSACTIONPLEDGEBALANCE]
  ,case 
    when FT.TYPECODE in (
        1
        ,3
        ,6
        )
      then (
          select top 1 [DATE]
          from dbo.INSTALLMENT
          where REVENUEID = FT.ID
          order by [DATE] asc
          )
    else null
    end as [PLEDGEFIRSTINSTALLMENT]
  ,case 
    when FT.TYPECODE in (
        1
        ,3
        ,6
        )
      then (
          select top 1 [DATE]
          from dbo.INSTALLMENT
          where REVENUEID = FT.ID
          order by [DATE] desc
          )
    else null
    end as [PLEDGELASTINSTALLMENT]
  ,case 
    when FT.TYPECODE in (
        1
        ,3
        ,6
        )
      then (
          select top 1 AMOUNT
          from dbo.INSTALLMENT
          where REVENUEID = FT.ID
          order by [DATE] desc
          )
    else null
    end as [PLEDGELASTINSTALLMENTAMOUNT]
  ,case 
    when FT.TYPECODE in (
        1
        ,3
        ,6
        ,15
        )
      then -- Pledge, Matching gift claim, Membership installment plan

        (
          select top 1 coalesce([PAYMENT].BASEAMOUNT, 0)
          from dbo.INSTALLMENTSPLITPAYMENT
          left join FINANCIALTRANSACTIONLINEITEM LI on INSTALLMENTSPLITPAYMENT.PAYMENTID = LI.ID
          left join FINANCIALTRANSACTION [PAYMENT] on LI.FINANCIALTRANSACTIONID = [PAYMENT].ID
          where INSTALLMENTSPLITPAYMENT.PLEDGEID = FT.ID
          order by [PAYMENT].[DATE] desc
          )
    when FT.TYPECODE = 2
      then -- Recurring Gift

        (
          select top 1 coalesce([PAYMENT].BASEAMOUNT, 0)
          from dbo.RECURRINGGIFTACTIVITY as [RGA]
          inner join dbo.FINANCIALTRANSACTIONLINEITEM as [PAYMENT] on [PAYMENT].ID = [RGA].PAYMENTREVENUEID
          inner join dbo.FINANCIALTRANSACTION as [REVPAYMENT] on [PAYMENT].FINANCIALTRANSACTIONID = [REVPAYMENT].ID
          where SOURCEREVENUEID = FT.ID
          order by [REVPAYMENT].[DATE] desc
          )
    else null
    end as [LATESTPAYMENTAMOUNT]
  ,case 
    when FT.TYPECODE in (
        1
        ,3
        ,6
        ,15
        )
      then (
          select top 1 cast([PAYMENT].[DATE] as date)
          from dbo.INSTALLMENTSPLITPAYMENT
          left join FINANCIALTRANSACTIONLINEITEM LI on INSTALLMENTSPLITPAYMENT.PAYMENTID = LI.ID
          left join FINANCIALTRANSACTION [PAYMENT] on LI.FINANCIALTRANSACTIONID = [PAYMENT].ID
          where INSTALLMENTSPLITPAYMENT.PLEDGEID = FT.ID
          order by [PAYMENT].[DATE] desc
          )
    when FT.TYPECODE = 2
      then (
          select top 1 cast([REVPAYMENT].[DATE] as date)
          from dbo.RECURRINGGIFTACTIVITY as [RGA]
          inner join dbo.FINANCIALTRANSACTIONLINEITEM as [PAYMENT] on [PAYMENT].ID = [RGA].PAYMENTREVENUEID
          inner join dbo.FINANCIALTRANSACTION as [REVPAYMENT] on [PAYMENT].FINANCIALTRANSACTIONID = [REVPAYMENT].ID
          where SOURCEREVENUEID = FT.ID
          order by [REVPAYMENT].[DATE] desc
          )
    else null
    end as [LATESTPAYMENTDATE]
  ,REVENUEPAYMENTMETHOD.PAYMENTMETHOD
  ,case FT.TYPECODE
    when 0
      then N'Payment'
    when 1
      then N'Pledge'
    when 2
      then N'Recurring gift'
    when 3
      then N'Matching gift claim'
    when 4
      then N'Planned gift'
    when 5
      then N'Order'
    when 6
      then N'Grant award'
    when 7
      then N'Auction donation'
    when 8
      then N'Donor challenge claim'
    when 9
      then N'Pending gift'
    when 15
      then N'Membership installment plan'
    end as TRANSACTIONTYPE
  ,[CHECK].CHECKDATE
  ,[CHECK].CHECKNUMBER
  ,[CASH].REFERENCEDATE as [CASHREFERENCEDATE]
  ,[CASH].REFERENCENUMBER as [CASHREFERENCENUMBER]
  ,case 
    when FT.TYPECODE = 1
      or FT.TYPECODE = 2
      or FT.TYPECODE = 15
      then [DIRECTDEBITSCHEDULE].REFERENCEDATE
    else [DIRECTDEBIT].REFERENCEDATE
    end as [DIRECTDEBITREFERENCEDATE]
  ,case 
    when FT.TYPECODE = 1
      or FT.TYPECODE = 2
      or FT.TYPECODE = 15
      then [DIRECTDEBITSCHEDULE].REFERENCENUMBER
    else [DIRECTDEBIT].REFERENCENUMBER
    end as [DIRECTDEBITREFERENCENUMBER]
  ,case 
    when FT.TYPECODE = 1
      or FT.TYPECODE = 2
      or FT.TYPECODE = 15
      then [DIRECTDEBITSCHEDULE].DIRECTDEBITRESULTCODE
    else [DIRECTDEBIT].DIRECTDEBITRESULTCODE
    end as [DIRECTDEBITRESULTCODE]
  ,case 
    when FT.TYPECODE = 1
      or FT.TYPECODE = 2
      or FT.TYPECODE = 15
      then [DIRECTDEBITSCHEDULE].CONSTITUENTACCOUNTID
    else [DIRECTDEBIT].CONSTITUENTACCOUNTID
    end as [DIRECTDEBITACCOUNTID]
  ,case 
    when FT.TYPECODE in (
        1
        ,2
        ,15
        ) and SCHEDULE.CREDITCARDID is not null
      then PLEDGERECURRINGGIFTCREDITCARD.CARDHOLDERNAME
    else [CREDIT].CARDHOLDERNAME
    end CARDHOLDERNAME
  ,case 
    when FT.TYPECODE in (
        1
        ,2
        ,15
        ) and SCHEDULE.CREDITCARDID is not null
      then PLEDGERECURRINGGIFTCREDITTYPECODE.DESCRIPTION
    else CREDITTYPECODE.DESCRIPTION
    end [CREDITTYPE]
  ,case 
    when FT.TYPECODE in (
        1
        ,2
        ,15
        ) and SCHEDULE.CREDITCARDID is not null
      then PLEDGERECURRINGGIFTCREDITCARD.EXPIRESON
    else [CREDIT].EXPIRESON
    end EXPIRESON
  ,[CREDIT].AUTHORIZATIONCODE
  ,REVENUE_EXT.BATCHNUMBER
  ,(
    select top 1 BATCH.DESCRIPTION
    from BATCH
    inner join BATCHTEMPLATE on BATCHTEMPLATE.ID = BATCHTEMPLATEID
    inner join BATCHTYPECATALOG on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHTYPECATALOG.ID
    where BATCH.BATCHNUMBER = REVENUE_EXT.BATCHNUMBER
      and BATCHTYPECATALOG.BASETABLENAME = 'BATCHREVENUE'
    ) as [BATCHDESCRIPTION]
  ,[GIKCODE].DESCRIPTION as [GIFTINKINDSUBTYPECODE]
  ,[STOCK].ISSUER
  ,[STOCK].MEDIANPRICE
  ,[STOCK].NUMBEROFUNITS
  ,[STOCK].SYMBOL
  ,null as [STOCKSALEDATE]
  ,null as [STOCKSALEAMOUNT]
  ,null as [STOCKBROKERFEE]
  ,null as [STOCKPOSTDATE]
  ,null as [STOCKPOSTSTATUS]
  ,[PROPERTYCODE].DESCRIPTION as [PROPERTYSUBTYPECODE]
  ,[PROPERTY].SALEDATE as [PROPERTYSALEDATE]
  ,[PROPERTY].SALEAMOUNT as [PROPERTYSALEAMOUNT]
  ,[PROPERTY].BROKERFEE as [PROPERTYBROKERFEE]
  ,[PROPERTY].SALEPOSTDATE as [PROPERTYPOSTDATE]
  ,[PROPERTY].SALEPOSTSTATUS as [PROPERTYPOSTSTATUS]
  ,FT.POSTDATE as POSTDATE
  ,case FT.POSTSTATUSCODE
    when 3
      then 'Do Not Post'
    when 2
      then 'Posted'
    else 'Not Posted'
    end as [POSTSTATUS]
  ,REVENUE_EXT.DONOTRECEIPT
  ,(select RECEIPTDATE from (select RECEIPTDATE, row_number() over (order by RECEIPTPROCESSDATE desc) as NUM from dbo.REVENUERECEIPT where REVENUEID = FT.ID) as TEMP where TEMP.NUM = 1) as RECEIPTDATE
  ,(select RECEIPTNUMBER from (select RECEIPTNUMBER, row_number() over (order by RECEIPTPROCESSDATE desc) as NUM from dbo.REVENUERECEIPT where REVENUEID = FT.ID) as TEMP where TEMP.NUM = 1) as RECEIPTNUMBER
  ,REVENUE_EXT.RECEIPTAMOUNT
  ,[SCHEDULE].SENDPLEDGEREMINDER
  ,REVENUE_EXT.SOURCECODE
  ,[SCHEDULE].ISPENDING
  ,REVENUE_EXT.APPEALID
  ,REVENUE_EXT.BENEFITSWAIVED
  ,REVENUE_EXT.GIVENANONYMOUSLY
  ,REVENUE_EXT.MAILINGID
  ,REVENUE_EXT.DONOTACKNOWLEDGE
  ,[CHANNELCODE].DESCRIPTION as [CHANNELCODE]
  --dbo.UFN_MKTSEGMENTATION_GETNAME(REVENUE_EXT.MAILINGID) as [MAILING],

  ,isnull(MKTSEGMENTATION.[NAME], '') as [MAILING]
  ,dbo.UFN_REVENUE_DESIGNATIONLIST(FT.ID) as [DESIGNATIONLIST]
  ,null as [SOLICITORLIST]
  ,[SCHEDULE].STARTDATE
  ,[SCHEDULE].ENDDATE
  ,[SCHEDULE].NUMBEROFINSTALLMENTS
  ,[SCHEDULE].FREQUENCY
  ,case when FT.TYPECODE = 2 then case RGS.FIRSTINSTALLMENTCODE when 0 then [SCHEDULE].NEXTTRANSACTIONDATE else (select DATE from dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO(FT.ID,null)) end end NEXTTRANSACTIONDATE
  ,[SCHEDULE].[STATUS]
  ,0 as [MISSEDPAYMENTS]
  ,PLEDGESUBTYPE.[NAME] as [PLEDGESUBTYPE]
  ,PLEDGESUBTYPE.POSTTOGL as [PLEDGESUBTYPEPOST]
  ,FT.DATEADDED
  ,FT.DATECHANGED
  ,FT.TSLONG
  ,[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION]
  ,[ADDEDBY].USERNAME as [ADDEDBY_USERNAME]
  ,[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION]
  ,[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME]
  ,null as PLEDGEREMINDERSENTID
  ,case 
    when FT.TYPECODE = 3
      then case 
          when REVENUEMATCHINGGIFT.ISACTIVE = 1
            and dbo.UFN_PLEDGE_GETBALANCE(FT.ID) <> 0
            then 'Active'
          when REVENUEMATCHINGGIFT.ISACTIVE = 1
            and dbo.UFN_PLEDGE_GETBALANCE(FT.ID) = 0
            then 'Paid in full'
          when REVENUEMATCHINGGIFT.ISACTIVE=0
            and dbo.UFN_PLEDGE_GETBALANCE(FT.ID) <> FT.TRANSACTIONAMOUNT
            then 'Inactive (Partially paid)'
          else 'Inactive'
          end
    else ''
    end as [MATCHINGGIFTCLAIMSTATUS]
  ,[OTHERPAYCODE].DESCRIPTION as [OTHERPAYMETHODDESCRIPTION]
  ,[OTHERPAY].REFERENCEDATE as [OTHERPAYREFERENCEDATE]
  ,[OTHERPAY].REFERENCENUMBER as [OTHERPAYREFERENCENUMBER]
  ,null as [CATEGORY]
  ,REVENUE_EXT.REFERENCE
  ,REVENUE_EXT.NEEDSRERECEIPT
  ,[STOCK].LOWPRICE
  ,[STOCK].HIGHPRICE
  ,coalesce((
      select sum(STOCKSALE.NUMBEROFUNITS)
      from dbo.STOCKSALE
      where STOCKDETAILID = [STOCK].ID
      ), 0) as UNITSSOLD
  ,[STOCK].NUMBEROFUNITS - coalesce((
      select sum(STOCKSALE.NUMBEROFUNITS)
      from dbo.STOCKSALE
      where STOCKDETAILID = [STOCK].ID
      ), 0) as UNITSREMAINING
  ,case 
    when FT.TYPECODE in (
        1
        ,3
        ,6
        )
      then (
          select top 1 INSTALLMENT.[DATE]
          from INSTALLMENT
          where dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID) > 0
            and REVENUEID = FT.ID
          order by INSTALLMENT.[DATE]
          )
    else null
    end as [PLEDGENEXTINSTALLMENTDATE]
  ,[BANKACCOUNTDEPOSITPAYMENT].DEPOSITID
  ,cast(BATX.TRANSACTIONNUMBER as varchar) as [TRANSACTIONNUMBER]
  --dbo.UFN_BANKACCOUNT_GETACCOUNTNAME(BATX.BANKACCOUNTID) as [ACCOUNTNAME],

  ,isnull(BANKACCOUNT.ACCOUNTNAME, '') as [ACCOUNTNAME]
  ,[BANKACCOUNTDEPOSIT].TOTALPAYMENTAMOUNT as [DEPOSITAMOUNT]
  ,cast(BAT.[DATE] as datetime) as [DEPOSITDATE]
  ,[BANKACCOUNTDEPOSIT].[STATUS] as [DEPOSITSTATUS]
  ,BAT.POSTSTATUS as [DEPOSITPOSTSTATUS]
  ,cast(BAT.POSTDATE as datetime) as [DEPOSITPOSTDATE]
  ,isnull(cast(left(BAT.DESCRIPTION, 100) as nvarchar(100)), '') as [DEPOSITREFERENCE]
  ,case 
    when FT.TYPECODE = 1
      or FT.TYPECODE = 2
      or FT.TYPECODE = 15
      then [STANDINGORDERSCHEDULE].REFERENCEDATE
    else [STANDINGORDER].REFERENCEDATE
    end as [STANDINGORDERREFERENCEDATE]
  ,dbo.UFN_STANDINGORDER_REFERENCENUMBER_FORDISPLAY(FT.ID) as [STANDINGORDERREFERENCENUMBER]
  ,case 
    when FT.TYPECODE = 1
      or FT.TYPECODE = 2
      or FT.TYPECODE = 15
      then [STANDINGORDERSCHEDULE].CONSTITUENTACCOUNTID
    else [STANDINGORDER].CONSTITUENTACCOUNTID
    end as [STANDINGORDERACCOUNTID]
  ,case 
    when FT.TYPECODE = 1
      or FT.TYPECODE = 2
      or FT.TYPECODE = 15
      then [STANDINGORDERSCHEDULE].STANDINGORDERSETUP
    else null
    end as [STANDINGORDERSETUP]
  ,case 
    when FT.TYPECODE = 1
      or FT.TYPECODE = 2
      or FT.TYPECODE = 15
      then [STANDINGORDERSCHEDULE].STANDINGORDERSETUPDATE
    else null
    end as [STANDINGORDERSETUPDATE]
  ,[DDISOURCECODE].DESCRIPTION as [DDISOURCECODEDESCRIPTION]
  ,[DIRECTDEBITSCHEDULE].DDISOURCEDATE
  ,[DIRECTDEBITSCHEDULE].SENDPMINSTRUCTION
  ,[DIRECTDEBITSCHEDULE].PMINSTRUCTIONTOSEND
  ,[DIRECTDEBITSCHEDULE].PMINSTRUCTIONDATE_NEW
  ,[DIRECTDEBITSCHEDULE].PMINSTRUCTIONDATE_CANCEL
  ,[DIRECTDEBITSCHEDULE].PMINSTRUCTIONDATE_SETUP
  ,[DIRECTDEBITSCHEDULE].PMADVANCENOTICESENTDATE
  ,[REVENUEVAT].AMOUNTTOTAX as [PORTIONSUBJECTTOVAT]
  ,[VATTAXRATE].DESCRIPTION as [VATTAXRATEDESCRIPTION]
  ,[VATTAXRATE].TAXRATE as [VATTAXRATE]
  ,[REVENUEVAT].VATAMOUNT as [VATAMOUNT]
  ,REVENUE_EXT.ISREIMBURSABLE
  ,FT.CALCULATEDUSERDEFINEDID as LOOKUPID
  ,[GIK].ITEMNAME as [GIFTINKINDITEMNAME]
  ,[GIK].DISPOSITION as [GIFTINKINDDISPOSITION]
  ,[GIK].NUMBEROFUNITS as [GIFTINKINDNUMBEROFUNITS]
  ,[GIK].FAIRMARKETVALUE as [GIFTINKINDFAIRMARKETVALUE]
  ,cast(case 
      when FT.TYPECODE = 1
        or FT.TYPECODE = 2
        or FT.TYPECODE = 15
        then 0
      else [DIRECTDEBIT].ISREJECTED
      end as bit) as [DIRECTDEBITISREJECTED]
  ,case 
    when FT.TYPECODE = 1
      then [PLEDGEORIGINALAMOUNT].ORIGINALAMOUNT
    else null
    end as [PLEDGEORIGINALAMOUNT]
  ,case 
    when FT.TYPECODE = 0
      then [PAYMENTORIGINALAMOUNT].ORIGINALAMOUNT
    else null
    end as [PAYMENTORIGINALAMOUNT]
  ,(
    select top 1 GLBATCH.LOOKUPID
    from REVENUEPOSTEDDETAIL
    inner join dbo.GLBATCH on GLBATCH.ID = REVENUEPOSTEDDETAIL.GLBATCHID
    where REVENUEPOSTEDDETAIL.REVENUEPOSTEDID = FT.ID
    order by REVENUEPOSTEDDETAIL.DATEADDED asc
    ) as [GLBATCH]
  ,isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) as BASECURRENCYID
  ,FT.BASEEXCHANGERATEID
  ,FT.ORGAMOUNT as ORGANIZATIONAMOUNT
  ,FT.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID
  ,FT.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT
  ,FT.TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID
  ,PDACCOUNTSYSTEM.[NAME] as [ACCOUNTSYSTEM]
  ,[STOCK].TRANSACTIONMEDIANPRICE
  ,[STOCK].TRANSACTIONLOWPRICE
  ,[STOCK].TRANSACTIONHIGHPRICE
  ,[STOCK].ORGANIZATIONMEDIANPRICE
  ,[STOCK].ORGANIZATIONLOWPRICE
  ,[STOCK].ORGANIZATIONHIGHPRICE
  ,[GIK].TRANSACTIONFAIRMARKETVALUE as [TRANSACTIONGIFTINKINDFAIRMARKETVALUE]
  ,[GIK].ORGANIZATIONFAIRMARKETVALUE as [ORGANIZATIONGIFTINKINDFAIRMARKETVALUE]
  ,case 
    when FT.TYPECODE in (
        1
        ,3
        ,6
        )
      then case 
          when isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) = FT.TRANSACTIONCURRENCYID
            then V_QUERY_REVENUE_PLEDGEBALANCE.BALANCE
          else dbo.UFN_PLEDGE_GETREVALUEDBASEBALANCEASOF(FT.ID, getdate(), 0)
          end
    else 0
    end as [PLEDGEBALANCE]
  ,case 
    when FT.TYPECODE in (
        1
        ,3
        ,6
        )
      then dbo.UFN_PLEDGE_GETREVALUEDORGANIZATIONBALANCEASOF(FT.ID, getdate(), 0)
    else 0
    end as [REVALUEDORGANIZATIONPLEDGEBALANCE]
  ,case 
    when FT.TYPECODE in (
        1
        ,3
        ,6
        )
      then (
          select top 1 TRANSACTIONAMOUNT
          from dbo.INSTALLMENT
          where REVENUEID = FT.ID
          order by [DATE] desc
          )
    else null
    end as [PLEDGELASTINSTALLMENTTRANSACTIONAMOUNT]
  ,case 
    when FT.TYPECODE in(
        1
        ,3
        ,6
        )
      then (
          select top 1 ORGANIZATIONAMOUNT
          from dbo.INSTALLMENT
          where REVENUEID = FT.ID
          order by [DATE] desc
          )
    else null
    end as [PLEDGELASTINSTALLMENTORGANIZATIONAMOUNT]
  ,case 
    when FT.TYPECODE in (
        1
        ,3
        ,6
        ,15
        )
      then (
          select top 1 coalesce([PAYMENT].TRANSACTIONAMOUNT, 0)
          from dbo.INSTALLMENTSPLITPAYMENT
          left join REVENUESPLIT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
          left join REVENUE [PAYMENT] on REVENUESPLIT.REVENUEID = [PAYMENT].ID
          where INSTALLMENTSPLITPAYMENT.PLEDGEID = FT.ID
          order by [PAYMENT].[DATE] desc
          )
    when FT.TYPECODE = 2
      then (
          select top 1 coalesce([PAYMENT].TRANSACTIONAMOUNT, 0)
          from dbo.RECURRINGGIFTACTIVITY as [RGA]
          inner join dbo.REVENUESPLIT as [PAYMENT] on [PAYMENT].ID = [RGA].PAYMENTREVENUEID
          inner join dbo.REVENUE as [REVPAYMENT] on [PAYMENT].REVENUEID = [REVPAYMENT].ID
          where SOURCEREVENUEID = FT.ID
          order by [REVPAYMENT].[DATE] desc
          )
    else null
    end as [LATESTPAYMENTTRANSACTIONAMOUNT]
  ,case 
    when FT.TYPECODE in(
        1
        ,3
        ,6
        ,15
        )
      then (
          select top 1 coalesce([PAYMENT].ORGANIZATIONAMOUNT, 0)
          from dbo.INSTALLMENTSPLITPAYMENT
          left join REVENUESPLIT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
          left join REVENUE [PAYMENT] on REVENUESPLIT.REVENUEID = [PAYMENT].ID
          where INSTALLMENTSPLITPAYMENT.PLEDGEID = FT.ID
          order by [PAYMENT].[DATE] desc
          )
    when FT.TYPECODE = 2
      then (
          select top 1 coalesce([PAYMENT].ORGANIZATIONAMOUNT, 0)
          from dbo.RECURRINGGIFTACTIVITY as [RGA]
          inner join dbo.REVENUESPLIT as [PAYMENT] on [PAYMENT].ID = [RGA].PAYMENTREVENUEID
          inner join dbo.REVENUE as [REVPAYMENT] on [PAYMENT].REVENUEID = [REVPAYMENT].ID
          where SOURCEREVENUEID = FT.ID
          order by [REVPAYMENT].[DATE] desc
          )
    else null
    end as [LATESTPAYMENTORGANIZATIONAMOUNT]
  ,case 
    when FT.TYPECODE in (
        1
        ,3
        ,6
        ,15
        )
      then (
          select top 1 [PAYMENT].BASECURRENCYID
          from dbo.INSTALLMENTSPLITPAYMENT
          left join REVENUESPLIT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
          left join REVENUE [PAYMENT] on REVENUESPLIT.REVENUEID = [PAYMENT].ID
          where INSTALLMENTSPLITPAYMENT.PLEDGEID = FT.ID
          order by [PAYMENT].[DATE] desc
          )
    when FT.TYPECODE = 2
      then (
          select top 1 [PAYMENT].BASECURRENCYID
          from dbo.RECURRINGGIFTACTIVITY as [RGA]
          inner join dbo.REVENUESPLIT as [PAYMENT] on [PAYMENT].ID = [RGA].PAYMENTREVENUEID
          inner join dbo.REVENUE as [REVPAYMENT] on [PAYMENT].REVENUEID = [REVPAYMENT].ID
          where SOURCEREVENUEID = FT.ID
          order by [REVPAYMENT].[DATE] desc
          )
    else null
    end as [LATESTPAYMENTAMOUNTBASECURRENCYID]
  ,case 
    when FT.TYPECODE in (
        1
        ,3
        ,6
        ,15
        )
      then (
          select top 1 [PAYMENT].TRANSACTIONCURRENCYID
          from dbo.INSTALLMENTSPLITPAYMENT
          left join REVENUESPLIT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
          left join REVENUE [PAYMENT] on REVENUESPLIT.REVENUEID = [PAYMENT].ID
          where INSTALLMENTSPLITPAYMENT.PLEDGEID = FT.ID
          order by [PAYMENT].[DATE] desc
          )
    when FT.TYPECODE = 2
      then (
          select top 1 [PAYMENT].TRANSACTIONCURRENCYID
          from dbo.RECURRINGGIFTACTIVITY as [RGA]
          inner join dbo.REVENUESPLIT as [PAYMENT] on [PAYMENT].ID = [RGA].PAYMENTREVENUEID
          inner join dbo.REVENUE as [REVPAYMENT] on [PAYMENT].REVENUEID = [REVPAYMENT].ID
          where SOURCEREVENUEID = FT.ID
          order by [REVPAYMENT].[DATE] desc
          )
    else null
    end as [LATESTPAYMENTAMOUNTTRANSACTIONCURRENCYID]
  ,case 
    when FT.TYPECODE = 1
      then [PLEDGEORIGINALAMOUNT].TRANSACTIONAMOUNT
    else null
    end as [PLEDGEORIGINALTRANSACTIONAMOUNT]
  ,case 
    when FT.TYPECODE = 1
      then [PLEDGEORIGINALAMOUNT].ORGANIZATIONAMOUNT
    else null
    end as [PLEDGEORIGINALORGANIZATIONAMOUNT]
  ,case 
    when FT.TYPECODE = 0
      then [PAYMENTORIGINALAMOUNT].TRANSACTIONAMOUNT
    else null
    end as [PAYMENTORIGINALTRANSACTIONAMOUNT]
  ,case 
    when FT.TYPECODE = 0
      then [PAYMENTORIGINALAMOUNT].ORGANIZATIONAMOUNT
    else null
    end as [PAYMENTORIGINALORGANIZATIONAMOUNT]
  ,[PROPERTY].[TRANSACTIONSALEAMOUNT]
  ,[PROPERTY].[ORGANIZATIONSALEAMOUNT]
  ,[PROPERTY].[TRANSACTIONBROKERFEE]
  ,[PROPERTY].[ORGANIZATIONBROKERFEE]
  ,[BANKACCOUNTDEPOSIT].TRANSACTIONCURRENCYID as [DEPOSITTRANSACTIONCURRENCYID]
  ,case 
    when FT.TYPECODE in (
        1
        ,2
        ,15
        ) and SCHEDULE.CREDITCARDID is not null
      then case 
          when len(isnull([PLEDGERECURRINGGIFTCREDITCARD].CREDITCARDPARTIALNUMBER, '')) = 0
            then null
          else replicate('*', 16 - len([PLEDGERECURRINGGIFTCREDITCARD].CREDITCARDPARTIALNUMBER)) + [PLEDGERECURRINGGIFTCREDITCARD].CREDITCARDPARTIALNUMBER
          end
    else case 
        when len(isnull([CREDIT].CREDITCARDPARTIALNUMBER, '')) = 0
          then null
        else replicate('*', 16 - len([CREDIT].CREDITCARDPARTIALNUMBER)) + [CREDIT].CREDITCARDPARTIALNUMBER
        end
    end as [CREDITCARDNUMBER]
  ,REVENUEBBNC.ID as [BBISREVENUEID]
  ,V_QUERY_REVENUE_INSTALLMENTPLANBALANCE.BALANCE as [TRANSACTIONINSTALLMENTPLANBALANCE]
  ,case 
    when FT.TYPECODE = 15
      then (
          select top 1 [DATE]
          from dbo.INSTALLMENT
          where REVENUEID = FT.ID
          order by [DATE] asc
          )
    else null
    end as [INSTALLMENTPLANFIRSTINSTALLMENT]
  ,case 
    when FT.TYPECODE = 15
      then (
          select top 1 [DATE]
          from dbo.INSTALLMENT
          where REVENUEID = FT.ID
          order by [DATE] desc
          )
    else null
    end as [INSTALLMENTPLANLASTINSTALLMENT]
  ,case 
    when FT.TYPECODE = 15
      then (
          select top 1 AMOUNT
          from dbo.INSTALLMENT
          where REVENUEID = FT.ID
          order by [DATE] desc
          )
    else null
    end as [INSTALLMENTPLANLASTINSTALLMENTAMOUNT]
  ,case 
    when FT.TYPECODE = 15
      then (
          select top 1 INSTALLMENT.[DATE]
          from INSTALLMENT
          where dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID) > 0
            and REVENUEID = FT.ID
          order by INSTALLMENT.[DATE]
          )
    else null
    end as [INSTALLMENTPLANNEXTINSTALLMENTDATE]
  ,case 
    when FT.TYPECODE = 15
      then case 
          when isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) = FT.TRANSACTIONCURRENCYID
            then V_QUERY_REVENUE_INSTALLMENTPLANBALANCE.BALANCE
          else dbo.UFN_PLEDGE_GETREVALUEDBASEBALANCEASOF(FT.ID, getdate(), 0)
          end
    else 0
    end as [INSTALLMENTPLANBALANCE]
  ,case 
    when FT.TYPECODE = 15
      then dbo.UFN_PLEDGE_GETREVALUEDORGANIZATIONBALANCEASOF(FT.ID, getdate(), 0)
    else 0
    end as [REVALUEDORGANIZATIONINSTALLMENTPLANBALANCE]
  ,case 
    when FT.TYPECODE = 15
      then (
          select top 1 TRANSACTIONAMOUNT
          from dbo.INSTALLMENT
          where REVENUEID = FT.ID
          order by [DATE] desc
          )
    else null
    end as [INSTALLMENTPLANLASTINSTALLMENTTRANSACTIONAMOUNT]
  ,case 
    when FT.TYPECODE = 15
      then (
          select top 1 ORGANIZATIONAMOUNT
          from dbo.INSTALLMENT
          where REVENUEID = FT.ID
          order by [DATE] desc
          )
    else null
    end as [INSTALLMENTPLANLASTINSTALLMENTORGANIZATIONAMOUNT]
  ,case 
    when FT.TYPECODE = 15
      then [MEMBERSHIPINSTALLMENTPLANORIGINALAMOUNT].TRANSACTIONAMOUNT
    else null
    end as [INSTALLMENTPLANORIGINALTRANSACTIONAMOUNT]
  ,case 
    when FT.TYPECODE = 15
      then [MEMBERSHIPINSTALLMENTPLANORIGINALAMOUNT].ORGANIZATIONAMOUNT
    else null
    end as [INSTALLMENTPLANORIGINALORGANIZATIONAMOUNT]
  ,[REVENUEVAT].TRANSACTIONAMOUNTTOTAX as [TRANSACTIONPORTIONSUBJECTTOVAT]
  ,[REVENUEVAT].TRANSACTIONVATAMOUNT as [TRANSACTIONVATAMOUNT]
  ,[REVENUEVAT].ORGANIZATIONAMOUNTTOTAX as [ORGANIZATIONPORTIONSUBJECTTOVAT]
  ,[REVENUEVAT].ORGANIZATIONVATAMOUNT as [ORGANIZATIONVATAMOUNT]
  ,case 
    when FT.TYPECODE in (
        1
        ,2
        ,15
        ) and SCHEDULE.CREDITCARDID is not null
      then PLEDGERECURRINGGIFTCREDITTYPECODE.ID
    else CREDITTYPECODE.ID
    end [CREDITTYPEID]
  ,[GIKCODE].ID as [GIFTINKINDSUBTYPECODEID]
  ,[PROPERTYCODE].ID as [PROPERTYSUBTYPECODEID]
  ,CHANNELCODE.ID as [CHANNELCODEID]
  ,[OTHERPAYCODE].ID as [OTHERPAYMETHODID]
  ,[DDISOURCECODE].ID as [DDISOURCECODEID]
  ,[VATTAXRATE].ID as [VATTAXRATEID]
  ,PDACCOUNTSYSTEM.ID as PDACCOUNTSYSTEMID
  ,case REVENUE_EXT.RECEIPTTYPECODE when 0 then 'Per payment' else 'Consolidated' end [RECEIPTTYPE]
  ,case FT.TYPECODE when 0 then SEPAMANDATEPAYMENT.LOOKUPID else SEPAMANDATECOMMITMENT.LOOKUPID end as [SEPAMANDATELOOKUPID]
  ,case FT.TYPECODE when 0 then SEPAMANDATEPAYMENT.SIGNATUREDATE else SEPAMANDATECOMMITMENT.SIGNATUREDATE end as [SEPAMANDATESIGNATUREDATE]
  ,case FT.TYPECODE when 0 then SEPAMANDATEPAYMENT.STATUS else SEPAMANDATECOMMITMENT.STATUS end as [SEPAMANDATESTATUS]
  ,case FT.TYPECODE when 0 then SEPAMANDATEPAYMENT.TYPE else SEPAMANDATECOMMITMENT.TYPE end as [SEPAMANDATETYPE]
  ,case [SCHEDULE].STATUSCODE when 3 then 'Canceled' else [SCHEDULE].STATUS end as STATUS2
  ,[PAYPAL].REFERENCEDATE as [PAYPALREFERENCEDATE]
  ,[PAYPAL].REFERENCENUMBER as [PAYPALREFERENCENUMBER]
  ,[VENMO].REFERENCEDATE as [VENMOREFERENCEDATE]
  ,[VENMO].REFERENCENUMBER as [VENMOREFERENCENUMBER]
/*#EXTENSION*/
from dbo.FINANCIALTRANSACTION FT
inner join dbo.REVENUE_EXT on FT.ID = REVENUE_EXT.ID and FT.DELETEDON is null
left join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = FT.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = FT.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = FT.CHANGEDBYID
left join dbo.CASHPAYMENTMETHODDETAIL as [CASH] on [CASH].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.CHECKPAYMENTMETHODDETAIL as [CHECK] on [CHECK].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.CREDITCARDPAYMENTMETHODDETAIL as [CREDIT] on [CREDIT].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.DIRECTDEBITPAYMENTMETHODDETAIL as [DIRECTDEBIT] on [DIRECTDEBIT].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.STANDINGORDERPAYMENTMETHODDETAIL as [STANDINGORDER] on [STANDINGORDER].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.OTHERPAYMENTMETHODDETAIL as [OTHERPAY] on [OTHERPAY].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.OTHERPAYMENTMETHODCODE as [OTHERPAYCODE] on [OTHERPAYCODE].ID = [OTHERPAY].OTHERPAYMENTMETHODCODEID
left join dbo.GIFTINKINDPAYMENTMETHODDETAIL as [GIK] on [GIK].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.PAYPALPAYMENTMETHODDETAIL as [PAYPAL] on [PAYPAL].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.VENMOPAYMENTMETHODDETAIL as [VENMO] on [VENMO].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.GIFTINKINDSUBTYPECODE as [GIKCODE] on [GIKCODE].ID = [GIK].GIFTINKINDSUBTYPECODEID
left join dbo.CREDITTYPECODE on CREDITTYPECODE.ID = [CREDIT].CREDITTYPECODEID
left join dbo.STOCKDETAIL as [STOCK] on [STOCK].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.PROPERTYDETAIL as [PROPERTY] on [PROPERTY].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.PROPERTYSUBTYPECODE as [PROPERTYCODE] on [PROPERTYCODE].ID = [PROPERTY].PROPERTYSUBTYPECODEID
left join dbo.REVENUESCHEDULE as [SCHEDULE] on [SCHEDULE].ID = FT.ID
left join dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT as [DIRECTDEBITSCHEDULE] on [DIRECTDEBITSCHEDULE].ID = [SCHEDULE].ID
left join dbo.DDISOURCECODE as [DDISOURCECODE] on [DIRECTDEBITSCHEDULE].DDISOURCECODEID = [DDISOURCECODE].ID
left join dbo.REVENUESCHEDULESTANDINGORDERPAYMENT as [STANDINGORDERSCHEDULE] on [STANDINGORDERSCHEDULE].ID = [SCHEDULE].ID
left join dbo.CHANNELCODE on CHANNELCODE.ID = REVENUE_EXT.CHANNELCODEID
--left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = FT.ID

left join dbo.PLEDGESUBTYPE on PLEDGESUBTYPE.ID = [SCHEDULE].PLEDGESUBTYPEID
left join dbo.REVENUEMATCHINGGIFT on REVENUEMATCHINGGIFT.ID = FT.ID
--left join dbo.REVENUEREFERENCE on FT.ID = REVENUEREFERENCE.ID

left join dbo.REVENUEVAT on FT.ID = REVENUEVAT.ID
left join dbo.VATTAXRATE on REVENUEVAT.VATTAXRATEID = VATTAXRATE.ID
left join dbo.BANKACCOUNTDEPOSITPAYMENT on FT.ID = BANKACCOUNTDEPOSITPAYMENT.ID
left join dbo.FINANCIALTRANSACTION as BAT on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BAT.ID
left join dbo.BANKACCOUNTTRANSACTION_EXT as BATX on BATX.ID = BAT.ID
left join dbo.PLEDGEORIGINALAMOUNT on PLEDGEORIGINALAMOUNT.ID = FT.ID
left join dbo.PAYMENTORIGINALAMOUNT on PAYMENTORIGINALAMOUNT.ID = FT.ID
left join dbo.MEMBERSHIPINSTALLMENTPLANORIGINALAMOUNT on MEMBERSHIPINSTALLMENTPLANORIGINALAMOUNT.ID = FT.ID
left join dbo.CREDITCARD as PLEDGERECURRINGGIFTCREDITCARD on PLEDGERECURRINGGIFTCREDITCARD.ID = SCHEDULE.CREDITCARDID
left join dbo.CREDITTYPECODE PLEDGERECURRINGGIFTCREDITTYPECODE on PLEDGERECURRINGGIFTCREDITTYPECODE.ID = PLEDGERECURRINGGIFTCREDITCARD.CREDITTYPECODEID
left join dbo.V_QUERY_REVENUE_PLEDGEBALANCE on FT.ID = V_QUERY_REVENUE_PLEDGEBALANCE.ID
left join dbo.V_QUERY_REVENUE_INSTALLMENTPLANBALANCE on FT.ID = V_QUERY_REVENUE_INSTALLMENTPLANBALANCE.ID
left join dbo.BANKACCOUNTDEPOSIT on BANKACCOUNTDEPOSIT.ID = BAT.ID
left join dbo.REVENUEBBNC on REVENUEBBNC.ID = FT.ID
left join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = FT.PDACCOUNTSYSTEMID
left join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
left join dbo.BANKACCOUNT on BATX.BANKACCOUNTID = BANKACCOUNT.ID
left join dbo.MKTSEGMENTATION on REVENUE_EXT.MAILINGID = MKTSEGMENTATION.ID
left join dbo.SEPAMANDATE as SEPAMANDATEPAYMENT on SEPAMANDATEPAYMENT.ID = DIRECTDEBIT.SEPAMANDATEID 
left join dbo.SEPAMANDATE as SEPAMANDATECOMMITMENT on SEPAMANDATECOMMITMENT.ID = DIRECTDEBITSCHEDULE.SEPAMANDATEID
outer apply dbo.UFN_RECURRINGGIFTSETTING_GETCURRENT() RGS