V_QUERY_PLEDGEREMINDERPROCESS_SEASONALADDRESSOUTPUT

/*
Generated by Blackbaud AppFx Platform
Date:  9/30/2015 1:01:32 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_PLEDGEREMINDERPROCESS_SEASONALADDRESSOUTPUT AS



with [REV] as
(
  select 
    FINANCIALTRANSACTION.ID as REVENUEID,
    FINANCIALTRANSACTION.CONSTITUENTID,
    FINANCIALTRANSACTION.TRANSACTIONAMOUNT as TOTALPLEDGEAMOUNT,
    case REVENUE_EXT.GIVENANONYMOUSLY when 1 then 'Yes' else 'No' end as GIVENANONYMOUSLY,
    cast(FINANCIALTRANSACTION.DATE as datetime) as PLEDGEDATE,

    --Inline the scalar function...

    --dbo.UFN_PLEDGE_GETBALANCE(FINANCIALTRANSACTION.ID) as PLEDGEBALANCE,

    case when FINANCIALTRANSACTION.TYPECODE = 7 then --Auction donations do not have installments

      FINANCIALTRANSACTION.TRANSACTIONAMOUNT - 
      isnull((select 
                sum(FTLI.TRANSACTIONAMOUNT) 
              from dbo.FINANCIALTRANSACTIONLINEITEM as FTLI
              inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
              where FT.PARENTID = FINANCIALTRANSACTION.ID
              and FT.TYPECODE = 20 and FTLI.DELETEDON is null
             ), 0)
    else
      FINANCIALTRANSACTION.TRANSACTIONAMOUNT - 
      (
        isnull((select 
                  sum(INSTALLMENTSPLITPAYMENT.AMOUNT) 
                from dbo.INSTALLMENTSPLITPAYMENT 
                where INSTALLMENTSPLITPAYMENT.PLEDGEID = FINANCIALTRANSACTION.ID), 0) + 
        isnull((select
                  sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) 
                from dbo.INSTALLMENTSPLITWRITEOFF 
                inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID 
                where INSTALLMENTSPLIT.PLEDGEID = FINANCIALTRANSACTION.ID), 0)
      )
    end as PLEDGEBALANCE,

    --Inline the scalar function...

    --dbo.UFN_PLEDGE_GETAMOUNTPAID(FINANCIALTRANSACTION.ID) as AMOUNTPAID,

    isnull((
      select
        sum (INSTALLMENTPAYMENT.AMOUNT)
      from dbo.INSTALLMENTPAYMENT
      inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = INSTALLMENTPAYMENT.PAYMENTID
      inner join dbo.REVENUESPLIT_EXT on FTLI.ID = REVENUESPLIT_EXT.ID
      inner join dbo.FINANCIALTRANSACTION FT on FTLI.FINANCIALTRANSACTIONID = FT.ID
      inner join dbo.REVENUE_EXT on FT.ID = REVENUE_EXT.ID
      where INSTALLMENTPAYMENT.PLEDGEID = FINANCIALTRANSACTION.ID
      and FT.DELETEDON is null
      and FTLI.DELETEDON is null
      and FTLI.TYPECODE <> 1
    ), 0) as AMOUNTPAID,

    FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID as REVENUELOOKUPID,
    FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
    [REVENUE_EXT].[APPEALID]
  from dbo.FINANCIALTRANSACTION
  inner join dbo.REVENUE_EXT ON FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  where FINANCIALTRANSACTION.TYPECODE in (1,15)
  and FINANCIALTRANSACTION.DELETEDON is null

  union all

  --Recurring gifts 

  select 
    FINANCIALTRANSACTION.ID as REVENUEID,
    FINANCIALTRANSACTION.CONSTITUENTID,
    FINANCIALTRANSACTION.TRANSACTIONAMOUNT as TOTALPLEDGEAMOUNT,
    case REVENUE_EXT.GIVENANONYMOUSLY when 1 then 'Yes' else 'No' end as GIVENANONYMOUSLY,
    cast(FINANCIALTRANSACTION.DATE as datetime) as PLEDGEDATE,
    null as PLEDGEBALANCE,
    isnull((select sum(AMOUNT) from dbo.RECURRINGGIFTACTIVITY where SOURCEREVENUEID = FINANCIALTRANSACTION.ID and TYPECODE = 0), 0) as AMOUNTPAID,
    FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID as REVENUELOOKUPID,
    FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
    [REVENUE_EXT].[APPEALID]
  from dbo.FINANCIALTRANSACTION
  inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  where FINANCIALTRANSACTION.TYPECODE = 2
  and FINANCIALTRANSACTION.DELETEDON is null
),
[PRIMARYCONTACT] as
(
  select
    RELATIONSHIP.RELATIONSHIPCONSTITUENTID,
    CONTACT.NAME,
    RELATIONSHIP.POSITION
  from dbo.RELATIONSHIP
  inner join dbo.CONSTITUENT as CONTACT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
  where RELATIONSHIP.ISPRIMARYCONTACT = 1
)
select 
  [REV].REVENUEID,
  CONSTITUENT.NAME as CONSTITUENTNAME,
  CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
  (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYADDRESSEE = 1) as PRIMARYADDRESSEE,
  (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYSALUTATION = 1) as PRIMARYSALUTATION,
  [PRIMARYCONTACT].[NAME] as PRIMARYCONTACT,
  [ADDR].[ADDRESSBLOCK],
  [ADDR].[CITY],
  [ADDR].[STATE],
  [ADDR].[POSTCODE],
  [ADDR].[COUNTRY],
  EMAILADDRESS.EMAILADDRESS,
  [REV].TOTALPLEDGEAMOUNT,
  [REV].GIVENANONYMOUSLY,
  [REV].PLEDGEDATE,
  [REV].PLEDGEBALANCE,
  [REV].AMOUNTPAID,
  APPEAL.NAME as APPEALNAME,
  [DESIGNATION1].[DESIGNATIONNAME] as [DESIGNATION1NAME],
  [DESIGNATION1].[DESIGNATIONPUBLICNAME] as [DESIGNATION1PUBLICNAME],
  [DESIGNATION1].[TRANSACTIONAMOUNT] as [DESIGNATION1AMOUNT],
  [DESIGNATION2].[DESIGNATIONNAME] as [DESIGNATION2NAME],
  [DESIGNATION2].[DESIGNATIONPUBLICNAME] as [DESIGNATION2PUBLICNAME],
  [DESIGNATION2].[TRANSACTIONAMOUNT] as [DESIGNATION2AMOUNT],
  [PRIMARYCONTACT].POSITION,
  [REV].REVENUELOOKUPID,
  [REV].TRANSACTIONCURRENCYID,
  [CURRENCY].[ISO4217] as [TRANSACTIONCURRENCY]
from [REV]
left join dbo.CONSTITUENT on [REV].CONSTITUENTID = CONSTITUENT.ID
left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID = CONSTITUENT.ID and EMAILADDRESS.ISPRIMARY = 1
left join dbo.APPEAL on [REV].APPEALID = APPEAL.ID
left join dbo.[CURRENCY] on [CURRENCY].[ID] = [REV].[TRANSACTIONCURRENCYID]
left join [PRIMARYCONTACT] on [PRIMARYCONTACT].[RELATIONSHIPCONSTITUENTID] = [CONSTITUENT].[ID] and CONSTITUENT.ISORGANIZATION = 1
outer apply dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate()) as [ADDR]
outer apply dbo.[UFN_REVENUE_GETORDEREDDESIGNATIONS_BYROWNUMBER]([REV].[REVENUEID], 1) as [DESIGNATION1]
outer apply dbo.[UFN_REVENUE_GETORDEREDDESIGNATIONS_BYROWNUMBER]([REV].[REVENUEID], 2) as [DESIGNATION2]