V_QUERY_TRIBUTEACKNOWLEDGEMENTPROCESS_EMAILOUTPUT

/*
Generated by Blackbaud AppFx Platform
Date:  11/11/2014 4:27:18 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_TRIBUTEACKNOWLEDGEMENTPROCESS_EMAILOUTPUT AS



with [RELATIONSHIP_CTE] as (
  select
    [RELATIONSHIPJOBINFO].[JOBTITLE],
    [RELATIONSHIP].[RELATIONSHIPCONSTITUENTID],
    [CONSTITUENT].[NAME],
    [RELATIONSHIPJOBINFO].[STARTDATE]
  from dbo.[RELATIONSHIP] 
  inner join dbo.[RELATIONSHIPJOBINFO] on [RELATIONSHIPJOBINFO].[RELATIONSHIPSETID] = [RELATIONSHIP].[RELATIONSHIPSETID]
  inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [RELATIONSHIP].[RECIPROCALCONSTITUENTID]
  where [RELATIONSHIP].[ISPRIMARYCONTACT] = 1
)
select
  REVENUETRIBUTELETTER.ID as REVENUETRIBUTELETTERID,
  FINANCIALTRANSACTION.ID as REVENUEID,
  ACKNOWLEDGEE.NAME as ACKNOWLEDGEENAME,
  (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = ACKNOWLEDGEE.ID and NAMEFORMAT.PRIMARYADDRESSEE = 1) as ACKNOWLEDGEEPRIMARYADDRESSEE,
  (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = ACKNOWLEDGEE.ID and NAMEFORMAT.PRIMARYSALUTATION = 1) as ACKNOWLEDGEEPRIMARYSALUTATION,
  case when [ACKNOWLEDGEE].[ISORGANIZATION] = 1 then 
    (select top 1 
       [RELATIONSHIP_CTE].[NAME] 
     from [RELATIONSHIP_CTE]
     where [RELATIONSHIP_CTE].[RELATIONSHIPCONSTITUENTID] = [ACKNOWLEDGEE].[ID]) 
  else null end as [ACKNOWLEDGEEPRIMARYCONTACT],
  case when [ACKNOWLEDGEE].[ISORGANIZATION] = 1 then 
    (select top 1
       [RELATIONSHIP_CTE].[JOBTITLE] 
     from [RELATIONSHIP_CTE]
     where [RELATIONSHIP_CTE].[RELATIONSHIPCONSTITUENTID] = [ACKNOWLEDGEE].[ID]
     order by [RELATIONSHIP_CTE].[STARTDATE] desc) 
  else null end as [ACKNOWLEDGEEPOSITION],
  ACKNOWLEDGEEEMAILADDRESS.EMAILADDRESS as ACKNOWLEDGEEEMAILADDRESS,
  TRIBUTE.TRIBUTETEXT,
  REVENUETRIBUTE.AMOUNT as REVENUETRIBUTEAMOUNT,
  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,
  case when [CONSTITUENT].[ISORGANIZATION] = 1 then 
    (select top 1 
       [RELATIONSHIP_CTE].[NAME] 
     from [RELATIONSHIP_CTE]
     where [RELATIONSHIP_CTE].[RELATIONSHIPCONSTITUENTID] = [CONSTITUENT].[ID]) 
  else null end as [PRIMARYCONTACT],
  case when [CONSTITUENT].[ISORGANIZATION] = 1 then 
    (select top 1
       [RELATIONSHIP_CTE].[JOBTITLE] 
     from [RELATIONSHIP_CTE]
     where [RELATIONSHIP_CTE].[RELATIONSHIPCONSTITUENTID] = [CONSTITUENT].[ID]
     order by [RELATIONSHIP_CTE].[STARTDATE] desc) 
  else null end as [POSITION],
  ADDRESS.ADDRESSBLOCK as ADDRESSBLOCK,
  ADDRESS.CITY as CITY,
  STATE.ABBREVIATION as STATE,
  ADDRESS.POSTCODE as POSTCODE,
  COUNTRY.DESCRIPTION as COUNTRY,
  EMAILADDRESS.EMAILADDRESS,
  FINANCIALTRANSACTION.TYPE as REVENUETYPE,
  cast(FINANCIALTRANSACTION.DATE as datetime) as DATE,
  case REVENUE_EXT.GIVENANONYMOUSLY when 1 then 'Yes' else 'No' end as GIVENANONYMOUSLY,
  FINANCIALTRANSACTION.TRANSACTIONAMOUNT as AMOUNT,
  REVENUE_EXT.RECEIPTAMOUNT as RECEIPTAMOUNT,
  APPEAL.NAME as APPEALNAME,
  REVENUEDESIGNATION1.DESIGNATIONNAME as DESIGNATION1NAME,
  REVENUEDESIGNATION1.DESIGNATIONPUBLICNAME as DESIGNATION1PUBLICNAME, 
  REVENUEDESIGNATION1.TRANSACTIONAMOUNT as DESIGNATION1AMOUNT,    
  REVENUEDESIGNATION2.DESIGNATIONNAME as DESIGNATION2NAME, 
  REVENUEDESIGNATION2.DESIGNATIONPUBLICNAME as DESIGNATION2PUBLICNAME, 
  REVENUEDESIGNATION2.TRANSACTIONAMOUNT as DESIGNATION2AMOUNT,
  isnull(REVENUETRIBUTE.AMOUNT + REVENUETRIBUTETAXCLAIMAMOUNT.TAXCLAIMAMOUNT, REVENUETRIBUTE.AMOUNT) as GROSSAMOUNT,
  FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
  isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, ACCOUNTSYSTEMCURRENCY.ID) as BASECURRENCYID,
  isnull(NONPOSTABLEBASECURRENCY.ISO4217, ACCOUNTSYSTEMCURRENCY.ISO4217) as REVENUETRIBUTEAMOUNTCURRENCY,
  TRANSACTIONCURRENCY.ISO4217 as AMOUNTCURRENCY,
  TRANSACTIONCURRENCY.ISO4217 as RECEIPTAMOUNTCURRENCY,
  case 
    when REVENUEDESIGNATION1.DESIGNATIONID is not null then TRANSACTIONCURRENCY.ISO4217
    else null
  end as DESIGNATION1AMOUNTCURRENCY,
  case 
    when REVENUEDESIGNATION2.DESIGNATIONID is not null then TRANSACTIONCURRENCY.ISO4217
    else null
  end as DESIGNATION2AMOUNTCURRENCY,
  isnull(NONPOSTABLEBASECURRENCY.ISO4217, ACCOUNTSYSTEMCURRENCY.ISO4217) as GROSSAMOUNTCURRENCY
from dbo.FINANCIALTRANSACTION
inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID  
inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
inner join dbo.CURRENCY as ACCOUNTSYSTEMCURRENCY on ACCOUNTSYSTEMCURRENCY.ID = CURRENCYSET.BASECURRENCYID
inner join dbo.CURRENCY as TRANSACTIONCURRENCY on TRANSACTIONCURRENCY.ID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
left join dbo.CURRENCY as NONPOSTABLEBASECURRENCY on NONPOSTABLEBASECURRENCY.ID = REVENUE_EXT.NONPOSTABLEBASECURRENCYID
left join dbo.REVENUETRIBUTE on FINANCIALTRANSACTION.ID = REVENUETRIBUTE.REVENUEID
left join dbo.REVENUETRIBUTETAXCLAIMAMOUNT on REVENUETRIBUTE.ID = REVENUETRIBUTETAXCLAIMAMOUNT.ID
left join dbo.TRIBUTE on REVENUETRIBUTE.TRIBUTEID = TRIBUTE.ID
left join dbo.REVENUETRIBUTELETTER on REVENUETRIBUTE.ID = REVENUETRIBUTELETTER.REVENUETRIBUTEID
inner join dbo.CONSTITUENT ACKNOWLEDGEE on REVENUETRIBUTELETTER.CONSTITUENTID = ACKNOWLEDGEE.ID
inner join dbo.[UFN_ADDRESSPROCESS_EMAILS](7, null) AS ACKNOWLEDGEEEMAILADDRESS ON ACKNOWLEDGEE.ID = ACKNOWLEDGEEEMAILADDRESS.CONSTITUENTID
left join dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID and ADDRESS.ISPRIMARY = 1
left join dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
left join dbo.STATE on ADDRESS.STATEID = STATE.ID
left join dbo.APPEAL on REVENUE_EXT.APPEALID = APPEAL.ID
left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID=CONSTITUENT.ID and EMAILADDRESS.ISPRIMARY = 1
outer apply dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS_BYROWNUMBER(FINANCIALTRANSACTION.ID,1) as REVENUEDESIGNATION1
outer apply dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS_BYROWNUMBER(FINANCIALTRANSACTION.ID,2) as REVENUEDESIGNATION2
where 
  REVENUE_EXT.DONOTACKNOWLEDGE = 0
  and FINANCIALTRANSACTION.TYPECODE in (0,1,2,4,5,6,7,8,9) -- not matching gift claims

  and CONSTITUENT.ID not in (select ID from dbo.DECEASEDCONSTITUENT)
  and FINANCIALTRANSACTION.DELETEDON is null;