V_QUERY_HOUSEHOLDREVENUE

/*
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_HOUSEHOLDREVENUE AS



with CONSTITUENT_CTE as(
select
    CONSTITUENT.ID as CONSTITUENTID,
    CONSTITUENT.ID as HOUSEHOLDID,
    CAST(null as datetime) as DATEFROM,
    CAST(null as datetime) as DATETO
from dbo.CONSTITUENT
where CONSTITUENT.ID not in
    (select GROUPMEMBER.MEMBERID from dbo.GROUPMEMBER
        inner join dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
        left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
        where GROUPDATA.GROUPTYPECODE = 0
        and    GROUPMEMBER.MEMBERID = CONSTITUENT.ID and ((GROUPMEMBERDATERANGE.DATEFROM is null and (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO >= getdate()))
            or (GROUPMEMBERDATERANGE.DATETO is null and (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= getdate())) 
            or (GROUPMEMBERDATERANGE.DATEFROM <= getdate() and GROUPMEMBERDATERANGE.DATETO >= getdate())))

union all

select
    GROUPMEMBER.MEMBERID as CONSTITUENTID,
    GROUPMEMBER.GROUPID as HOUSEHOLDID,
    GROUPMEMBERDATERANGE.DATEFROM,
    GROUPMEMBERDATERANGE.DATETO
from dbo.GROUPMEMBER
inner join dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
where GROUPDATA.GROUPTYPECODE = 0
)

select
    REVENUE.ID,
    CONSTITUENT_CTE.HOUSEHOLDID,
    REVENUE.CONSTITUENTID as [CONSTITUENTID],
    cast(REVENUE.DATE as datetime) as DATE,
    REVENUE.BASEAMOUNT as [AMOUNT],
    case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 3 then
            dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID)
        else
            0 end as [TRANSACTIONPLEDGEBALANCE], 
    case
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 3 then
            (select top 1 DATE from dbo.INSTALLMENT where REVENUEID = REVENUE.ID order by DATE asc)
        else
            null end as [PLEDGEFIRSTINSTALLMENT],
    case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 3 then
            (select top 1 DATE from dbo.INSTALLMENT where REVENUEID = REVENUE.ID order by DATE desc)
        else
            null end as [PLEDGELASTINSTALLMENT],
    case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 3 then
            (select top 1 AMOUNT from dbo.INSTALLMENT where REVENUEID = REVENUE.ID order by DATE desc)
        else
            null end as [PLEDGELASTINSTALLMENTAMOUNT],
    case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 3 then
            (
                select top 1 coalesce([PAYMENT].BASEAMOUNT, 0) from dbo.FINANCIALTRANSACTION as [PAYMENT] 
                inner join dbo.REVENUE_EXT PAYMENT_REX on PAYMENT.ID = PAYMENT_REX.ID
                where [PAYMENT].ID in 
                (
                    select PAYMENTID from dbo.INSTALLMENTPAYMENT where PLEDGEID = REVENUE.ID
                )
                and PAYMENT.DELETEDON is null
                order by cast([PAYMENT].DATE as datetime) desc
            )
        when REVENUE.TYPECODE = 2 then 
            (
                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.REVENUESPLIT_EXT as PAYMENT_RSE on PAYMENT.ID = PAYMENT_RSE.ID
                inner join dbo.FINANCIALTRANSACTION as [REVPAYMENT] on [PAYMENT].FINANCIALTRANSACTIONID = [REVPAYMENT].ID
                inner join dbo.REVENUE_EXT as REVPAYMENT_REX on REVPAYMENT.ID = REVPAYMENT_REX.ID
                where SOURCEREVENUEID = REVENUE.ID
                and PAYMENT.DELETEDON is null
                and PAYMENT.TYPECODE <> 1
                and REVPAYMENT.DELETEDON is null
                order by cast([REVPAYMENT].DATE as datetime) desc
            )
        else 
            null end as [LATESTPAYMENTAMOUNT],
    case
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 3 then
            (
                select top 1 coalesce(cast([PAYMENT].DATE as datetime), 0) from dbo.FINANCIALTRANSACTION as [PAYMENT]
                inner join dbo.REVENUE_EXT as PAYMENT_REX on PAYMENT.ID = PAYMENT_REX.ID
                where [PAYMENT].ID in 
                (
                    select PAYMENTID from dbo.INSTALLMENTPAYMENT where PLEDGEID = REVENUE.ID
                )
                and PAYMENT.DELETEDON is null
                order by cast([PAYMENT].DATE as datetime) desc
            )
        when REVENUE.TYPECODE = 2 then 
            (
                select top 1 coalesce(cast([REVPAYMENT].DATE as datetime), 0) 
                from dbo.RECURRINGGIFTACTIVITY as [RGA]
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as [PAYMENT] on [PAYMENT].ID = [RGA].PAYMENTREVENUEID
                inner join dbo.REVENUESPLIT_EXT as PAYMENT_RSE on PAYMENT.ID = PAYMENT_RSE.ID
                inner join dbo.FINANCIALTRANSACTION as [REVPAYMENT] on [PAYMENT].FINANCIALTRANSACTIONID = [REVPAYMENT].ID
                inner join dbo.REVENUE_EXT as REVPAYMENT_REX on REVPAYMENT.ID = REVPAYMENT_REX.ID
                where SOURCEREVENUEID = REVENUE.ID
                and PAYMENT.DELETEDON is null
                and PAYMENT.TYPECODE <> 1
                and REVPAYMENT.DELETEDON is null
                order by cast([REVPAYMENT].DATE as datetime) desc
            )
        else 
            null end as [LATESTPAYMENTDATE],
    REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
    REVENUE.TYPE as TRANSACTIONTYPE,
    [CHECK].CHECKDATE,
    [CHECK].CHECKNUMBER,
    [CASH].REFERENCEDATE as [CASHREFERENCEDATE],
    [CASH].REFERENCENUMBER as [CASHREFERENCENUMBER],
    case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 2 then [DIRECTDEBITSCHEDULE].REFERENCEDATE
        else [DIRECTDEBIT].REFERENCEDATE end as [DIRECTDEBITREFERENCEDATE],
    case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 2 then [DIRECTDEBITSCHEDULE].REFERENCENUMBER
        else [DIRECTDEBIT].REFERENCENUMBER end as [DIRECTDEBITREFERENCENUMBER],
    case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 2 then [DIRECTDEBITSCHEDULE].DIRECTDEBITRESULTCODE
        else [DIRECTDEBIT].DIRECTDEBITRESULTCODE end as [DIRECTDEBITRESULTCODE],
    case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 2 then [DIRECTDEBITSCHEDULE].CONSTITUENTACCOUNTID
        else [DIRECTDEBIT].CONSTITUENTACCOUNTID end as [DIRECTDEBITACCOUNTID],
    [CREDIT].CARDHOLDERNAME,
    CREDITTYPECODE.DESCRIPTION as [CREDITTYPE],
    [CREDIT].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],
    cast(REVENUE.POSTDATE as datetime) as POSTDATE,
    case when REVENUE.POSTSTATUSCODE = 3 then 'Do Not Post' when REVENUEPOSTED.ID is not null then 'Posted' else 'Not Posted' end as [POSTSTATUS],
    REVENUE_EXT.DONOTRECEIPT,
    (select top 1 RR.RECEIPTDATE from dbo.REVENUERECEIPT RR where RR.REVENUEID = REVENUE.ID order by RECEIPTPROCESSDATE desc) as RECEIPTDATE,
    (select top 1 RR.RECEIPTNUMBER from dbo.REVENUERECEIPT RR where RR.REVENUEID = REVENUE.ID order by RECEIPTPROCESSDATE desc) 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],
    dbo.UFN_REVENUE_DESIGNATIONLIST(REVENUE.ID) as [DESIGNATIONLIST],
    null as [SOLICITORLIST],
    [SCHEDULE].STARTDATE,
    [SCHEDULE].ENDDATE,
    [SCHEDULE].NUMBEROFINSTALLMENTS,
    [SCHEDULE].FREQUENCY,
    [SCHEDULE].NEXTTRANSACTIONDATE,
    [SCHEDULE].STATUS,
    0 as [MISSEDPAYMENTS],
    PLEDGESUBTYPE.NAME as [PLEDGESUBTYPE], 
    PLEDGESUBTYPE.POSTTOGL as [PLEDGESUBTYPEPOST],
    REVENUE.DATEADDED,
    REVENUE.DATECHANGED,
    REVENUE.TSLONG,
    [ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
    [ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
    [CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
    [CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
    --REVENUESPLIT.DESIGNATIONID,

    null as PLEDGEREMINDERSENTID,
    case 
        when REVENUE.TYPECODE = 3 then
            case 
                when REVENUEMATCHINGGIFT.ISACTIVE = 1 AND dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID)<>0 then 'Active' 
                when REVENUEMATCHINGGIFT.ISACTIVE = 1 AND dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID)=0 then 'Paid in full' 
                else 'Inactive' 
            end
    else
        ''
    end
    as [MATCHINGGIFTCLAIMSTATUS],
    [OTHERPAYCODE].DESCRIPTION as [OTHERPAYMETHODDESCRIPTION],    
    [OTHERPAY].REFERENCEDATE as [OTHERPAYREFERENCEDATE],
    [OTHERPAY].REFERENCENUMBER as [OTHERPAYREFERENCENUMBER],
    --GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME --removed in revenue changes from 1.7 

    null as [CATEGORY],
    REVENUEREFERENCE.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 REVENUE.TYPECODE in (1,3) then
            (select top 1 INSTALLMENT.DATE 
            from INSTALLMENT 
            where dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID) > 0
            and REVENUEID = REVENUE.ID
            order by INSTALLMENT.DATE)
    else
        null
    end
    as [PLEDGENEXTINSTALLMENTDATE],
    [BANKACCOUNTDEPOSITPAYMENT].DEPOSITID, 
    [BANKACCOUNTTRANSACTION].TRANSACTIONNUMBER,
    dbo.UFN_BANKACCOUNT_GETACCOUNTNAME(BANKACCOUNTTRANSACTION.BANKACCOUNTID) as [ACCOUNTNAME],
    [BANKACCOUNTDEPOSIT].TOTALPAYMENTAMOUNT as [DEPOSITAMOUNT],
    [BANKACCOUNTTRANSACTION].TRANSACTIONDATE as [DEPOSITDATE],
    [BANKACCOUNTDEPOSIT].STATUS as [DEPOSITSTATUS],
    [BANKACCOUNTTRANSACTION].POSTSTATUS as [DEPOSITPOSTSTATUS],
    [BANKACCOUNTTRANSACTION].POSTDATE as [DEPOSITPOSTDATE],
    [BANKACCOUNTTRANSACTION].REFERENCE  as [DEPOSITREFERENCE],
                            case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 2 then [STANDINGORDERSCHEDULE].REFERENCEDATE
        else [STANDINGORDER].REFERENCEDATE end as [STANDINGORDERREFERENCEDATE],
    dbo.UFN_STANDINGORDER_REFERENCENUMBER_FORDISPLAY(REVENUE.ID) as [STANDINGORDERREFERENCENUMBER],
    case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 2 then [STANDINGORDERSCHEDULE].CONSTITUENTACCOUNTID
        else [STANDINGORDER].CONSTITUENTACCOUNTID end as [STANDINGORDERACCOUNTID],
    case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 2 then [STANDINGORDERSCHEDULE].STANDINGORDERSETUP
        else null end as [STANDINGORDERSETUP],
        case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 2 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,
    [GIK].ITEMNAME as [GIFTINKINDITEMNAME],
    [GIK].DISPOSITION as [GIFTINKINDDISPOSITION],
    [GIK].NUMBEROFUNITS as [GIFTINKINDNUMBEROFUNITS],
    [GIK].FAIRMARKETVALUE as [GIFTINKINDFAIRMARKETVALUE],
  isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) as BASECURRENCYID,
  REVENUE.BASEEXCHANGERATEID,
  REVENUE.ORGAMOUNT as ORGANIZATIONAMOUNT,
  REVENUE.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
  REVENUE.TRANSACTIONAMOUNT,
  REVENUE.TRANSACTIONCURRENCYID,
  [STOCK].MEDIANPRICE as [MEDIANPRICESTRING],
    [STOCK].LOWPRICE as [LOWPRICESTRING],
    [STOCK].HIGHPRICE as [HIGHPRICESTRING],
    [STOCK].TRANSACTIONMEDIANPRICE,
    [STOCK].TRANSACTIONLOWPRICE,
    [STOCK].TRANSACTIONHIGHPRICE,
    [STOCK].ORGANIZATIONMEDIANPRICE,
    [STOCK].ORGANIZATIONLOWPRICE,
    [STOCK].ORGANIZATIONHIGHPRICE,
    [GIK].TRANSACTIONFAIRMARKETVALUE as [TRANSACTIONGIFTINKINDFAIRMARKETVALUE],
    [GIK].ORGANIZATIONFAIRMARKETVALUE as [ORGANIZATIONGIFTINKINDFAIRMARKETVALUE],
    case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 3 
            then dbo.UFN_PLEDGE_GETREVALUEDBASEBALANCEASOF(REVENUE.ID, getdate(), 0)
        else 0 
    end as [PLEDGEBALANCE],
    case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 3 
            then dbo.UFN_PLEDGE_GETREVALUEDORGANIZATIONBALANCEASOF(REVENUE.ID, getdate(), 0)
        else 0
    end as [REVALUEDORGANIZATIONPLEDGEBALANCE],
    case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 3 then
            (select top 1 TRANSACTIONAMOUNT from dbo.INSTALLMENT where REVENUEID = REVENUE.ID order by DATE desc)
        else
            null end as [PLEDGELASTINSTALLMENTTRANSACTIONAMOUNT],
    case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 3 then
            (select top 1 ORGANIZATIONAMOUNT from dbo.INSTALLMENT where REVENUEID = REVENUE.ID order by DATE desc)
        else
            null end as [PLEDGELASTINSTALLMENTORGANIZATIONAMOUNT],
       case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 3 then
            (
                select top 1 coalesce([PAYMENT].TRANSACTIONAMOUNT, 0) from dbo.INSTALLMENTSPLITPAYMENT
                inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
                inner join dbo.FINANCIALTRANSACTION [PAYMENT] on REVENUESPLIT.FINANCIALTRANSACTIONID = [PAYMENT].ID
                inner join dbo.REVENUE_EXT on PAYMENT.ID = REVENUE_EXT.ID
                where INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUE.ID
                and REVENUESPLIT.DELETEDON is null
                and REVENUESPLIT.TYPECODE <> 1
                and PAYMENT.DELETEDON is null
                order by cast([PAYMENT].DATE as datetime) desc
            )
        when REVENUE.TYPECODE = 2 then 
            (
                select top 1 coalesce([PAYMENT].TRANSACTIONAMOUNT, 0) 
                from dbo.RECURRINGGIFTACTIVITY as [RGA]
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as [PAYMENT] on [PAYMENT].ID = [RGA].PAYMENTREVENUEID    
                inner join dbo.REVENUESPLIT_EXT as PAYMENT_RSE on PAYMENT.ID = PAYMENT_RSE.ID
                inner join dbo.FINANCIALTRANSACTION as [REVPAYMENT] on [PAYMENT].FINANCIALTRANSACTIONID = [REVPAYMENT].ID
                inner join dbo.REVENUE_EXT REVPAYMENT_REX on REVPAYMENT.ID = REVPAYMENT_REX.ID
                where SOURCEREVENUEID = REVENUE.ID
                and PAYMENT.DELETEDON is null
                and PAYMENT.TYPECODE <> 1
                and REVPAYMENT.DELETEDON is null
                order by cast([REVPAYMENT].DATE as datetime) desc
            )
        else 
            null end as [LATESTPAYMENTTRANSACTIONAMOUNT],
  case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 3 then
            (
                select top 1 coalesce([PAYMENT].ORGAMOUNT, 0) from dbo.INSTALLMENTSPLITPAYMENT
                inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
                inner join dbo.FINANCIALTRANSACTION [PAYMENT] on REVENUESPLIT.FINANCIALTRANSACTIONID = [PAYMENT].ID
                inner join dbo.REVENUE_EXT on PAYMENT.ID = REVENUE_EXT.ID
                where INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUE.ID
                and REVENUESPLIT.DELETEDON is null
                and REVENUESPLIT.TYPECODE <> 1
                and PAYMENT.DELETEDON is null
                order by cast([PAYMENT].DATE as datetime) desc
            )
        when REVENUE.TYPECODE = 2 then 
            (
                select top 1 coalesce([PAYMENT].ORGAMOUNT, 0) 
                from dbo.RECURRINGGIFTACTIVITY as [RGA]
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as [PAYMENT] on [PAYMENT].ID = [RGA].PAYMENTREVENUEID
                inner join dbo.REVENUESPLIT_EXT as PAYMENT_RSE on PAYMENT.ID = PAYMENT_RSE.ID
                inner join dbo.FINANCIALTRANSACTION as [REVPAYMENT] on [PAYMENT].FINANCIALTRANSACTIONID = [REVPAYMENT].ID
                inner join dbo.REVENUE_EXT as REVPAYMENT_REX on REVPAYMENT.ID = REVPAYMENT_REX.ID
                where SOURCEREVENUEID = REVENUE.ID
                and PAYMENT.DELETEDON is null
                and PAYMENT.TYPECODE <> 1
                and REVPAYMENT.DELETEDON is null
                order by cast([REVPAYMENT].DATE as datetime) desc
            )
        else 
            null end as [LATESTPAYMENTORGANIZATIONAMOUNT],
  case 
        when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 3 then
            (
                select top 1 isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) from dbo.INSTALLMENTSPLITPAYMENT
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
                inner join dbo.FINANCIALTRANSACTION as [PAYMENT] on REVENUESPLIT.FINANCIALTRANSACTIONID = [PAYMENT].ID
                inner join dbo.REVENUE_EXT on PAYMENT.ID = REVENUE_EXT.ID
                inner join dbo.PDACCOUNTSYSTEM on PAYMENT.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                where INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUE.ID
                and REVENUESPLIT.DELETEDON is null
                and REVENUESPLIT.TYPECODE <> 1
                and PAYMENT.DELETEDON is null
                order by cast([PAYMENT].DATE as datetime) desc
            )
when REVENUE.TYPECODE = 2 then 
            (
                select top 1 isnull(REVPAYMENT_REX.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) 
                from dbo.RECURRINGGIFTACTIVITY as [RGA]
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as [PAYMENT] on [PAYMENT].ID = [RGA].PAYMENTREVENUEID
                inner join dbo.REVENUESPLIT_EXT as PAYMENT_RSE on PAYMENT.ID = PAYMENT_RSE.ID
                inner join dbo.FINANCIALTRANSACTION as [REVPAYMENT] on [PAYMENT].FINANCIALTRANSACTIONID = [REVPAYMENT].ID
                inner join dbo.REVENUE_EXT as REVPAYMENT_REX on REVPAYMENT.ID = REVPAYMENT_REX.ID
                inner join dbo.PDACCOUNTSYSTEM on REVPAYMENT.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                where SOURCEREVENUEID = REVENUE.ID
                and PAYMENT.DELETEDON is null
                and PAYMENT.TYPECODE <> 1
                and REVPAYMENT.DELETEDON is null
                order by cast([REVPAYMENT].DATE as datetime) desc
            )
        else 
            null end as [LATESTPAYMENTAMOUNTBASECURRENCYID],
    case 
      when REVENUE.TYPECODE = 1 or REVENUE.TYPECODE = 3 then
            (
                select top 1 [PAYMENT].TRANSACTIONCURRENCYID from dbo.INSTALLMENTSPLITPAYMENT
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
                inner join dbo.FINANCIALTRANSACTION as [PAYMENT] on REVENUESPLIT.FINANCIALTRANSACTIONID = [PAYMENT].ID
                inner join dbo.REVENUE_EXT on PAYMENT.ID = REVENUE_EXT.ID
                where INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUE.ID
                and REVENUESPLIT.DELETEDON is null
                and REVENUESPLIT.TYPECODE <> 1
                and PAYMENT.DELETEDON is null
                order by cast([PAYMENT].DATE as datetime) desc
            )
        when REVENUE.TYPECODE = 2 then 
            (
                select top 1 [REVPAYMENT].TRANSACTIONCURRENCYID 
                from dbo.RECURRINGGIFTACTIVITY as [RGA]
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as [PAYMENT] on [PAYMENT].ID = [RGA].PAYMENTREVENUEID
                inner join dbo.REVENUESPLIT_EXT as PAYMENT_RSE on PAYMENT.ID = PAYMENT_RSE.ID
                inner join dbo.FINANCIALTRANSACTION as [REVPAYMENT] on [PAYMENT].FINANCIALTRANSACTIONID = [REVPAYMENT].ID
                inner join dbo.REVENUE_EXT as REVPAYMENT_REX on REVPAYMENT.ID = REVPAYMENT_REX.ID
                where SOURCEREVENUEID = REVENUE.ID
                and PAYMENT.DELETEDON is null
                and PAYMENT.TYPECODE <> 1
                and REVPAYMENT.DELETEDON is null
                order by cast([REVPAYMENT].DATE as datetime) desc
            )
        else 
            null end as [LATESTPAYMENTAMOUNTTRANSACTIONCURRENCYID],
  [PROPERTY].[TRANSACTIONSALEAMOUNT],
  [PROPERTY].[ORGANIZATIONSALEAMOUNT],
    [PROPERTY].[TRANSACTIONBROKERFEE],
  [PROPERTY].[ORGANIZATIONBROKERFEE],
  [BANKACCOUNTDEPOSIT].TRANSACTIONCURRENCYID as [DEPOSITTRANSACTIONCURRENCYID],
  case
      when len(isnull([CREDIT].CREDITCARDPARTIALNUMBER, '')) = 0 then null
      else replicate('*', 16 - len([CREDIT].CREDITCARDPARTIALNUMBER)) + [CREDIT].CREDITCARDPARTIALNUMBER
  end as [CREDITCARDNUMBER],
  case [SCHEDULE].STATUSCODE when 3 then 'Canceled' else [SCHEDULE].STATUS end as STATUS2,
  [CREDIT].[CREDITTYPECODEID],
  [GIK].[GIFTINKINDSUBTYPECODEID],
  [REVENUE_EXT].[CHANNELCODEID],
  [PROPERTY].[PROPERTYSUBTYPECODEID],
  [OTHERPAY].[OTHERPAYMENTMETHODCODEID],
  [DIRECTDEBITSCHEDULE].[DDISOURCECODEID],
  [REVENUEVAT].[VATTAXRATEID],
  [PAYPAL].REFERENCEDATE as [PAYPALREFERENCEDATE],
  [PAYPAL].REFERENCENUMBER as [PAYPALREFERENCENUMBER],
  [VENMO].REFERENCEDATE as [VENMOREFERENCEDATE],
  [VENMO].REFERENCENUMBER as [VENMOREFERENCENUMBER]

    /*#EXTENSION*/ 
from dbo.FINANCIALTRANSACTION REVENUE 
inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
inner join dbo.PDACCOUNTSYSTEM on REVENUE.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = REVENUE.CONSTITUENTID
    and ((CONSTITUENT_CTE.DATEFROM is null and (CONSTITUENT_CTE.DATETO is null or CONSTITUENT_CTE.DATETO >= cast(REVENUE.DATE as datetime)))
        or (CONSTITUENT_CTE.DATETO is null and (CONSTITUENT_CTE.DATEFROM is null or CONSTITUENT_CTE.DATEFROM <= cast(REVENUE.DATE as datetime))) 
        or (CONSTITUENT_CTE.DATEFROM <= cast(REVENUE.DATE as datetime) and CONSTITUENT_CTE.DATETO >= cast(REVENUE.DATE as datetime)))
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = REVENUE.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = REVENUE.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 outer join dbo.REVENUESCHEDULE as [SCHEDULE] on [SCHEDULE].ID = REVENUE.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 outer join dbo.CHANNELCODE on CHANNELCODE.ID = REVENUE_EXT.CHANNELCODEID
left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
left join dbo.PLEDGESUBTYPE on PLEDGESUBTYPE.ID = [SCHEDULE].PLEDGESUBTYPEID
--left join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID

left join dbo.REVENUEMATCHINGGIFT ON REVENUEMATCHINGGIFT.ID = REVENUE.ID
--removed with revenue changes in 1.7

--left join dbo.REVENUECATEGORY on REVENUE.ID = REVENUECATEGORY.ID

--left join dbo.GLREVENUECATEGORYMAPPING on REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID = GLREVENUECATEGORYMAPPING.ID

left join dbo.REVENUEREFERENCE on REVENUE.ID = REVENUEREFERENCE.ID
left join dbo.REVENUEVAT on REVENUE.ID = REVENUEVAT.ID
left join dbo.VATTAXRATE on REVENUEVAT.VATTAXRATEID = VATTAXRATE.ID
left join dbo.BANKACCOUNTDEPOSITPAYMENT on REVENUE.ID = BANKACCOUNTDEPOSITPAYMENT.ID
left join dbo.BANKACCOUNTTRANSACTION  on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
left join dbo.BANKACCOUNTDEPOSIT on BANKACCOUNTDEPOSIT.ID = BANKACCOUNTTRANSACTION.ID