UFN_QUERY_PLEDGEREMINDERPROCESSEMAILOUTPUT

create function dbo.[UFN_QUERY_PLEDGEREMINDERPROCESSEMAILOUTPUT]
(
  @PARAMETERSETID uniqueidentifier = null
)
returns table
as
return
(
  select 
    REVENUE.ID as REVENUEID,
    CONSTITUENT.NAME as CONSTITUENTNAME,
    CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
    (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYADDRESSEE = 1) as ADDRESSEE,
    (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYSALUTATION = 1) as SALUTATION,
    isnull((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONTACT.ID and NAMEFORMAT.PRIMARYADDRESSEE = 1), CONTACT.NAME) as CONTACT,
    (select top 1 RELATIONSHIP.POSITION
     from dbo.RELATIONSHIP                        
     where RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
     and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID) as POSITION,
    EMAILADDRESS.EMAILADDRESS,
    REVENUE.TRANSACTIONAMOUNT as TOTALPLEDGEAMOUNT,
    case REVENUE_EXT.GIVENANONYMOUSLY when 1 then 'Yes' else 'No' end as GIVENANONYMOUSLY,
    cast(REVENUE.DATE as datetime) as PLEDGEDATE,
    dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) as PLEDGEBALANCE,
    dbo.UFN_PLEDGE_GETAMOUNTPAID(REVENUE.ID) as AMOUNTPAID,
    APPEAL.NAME as APPEALNAME,
    (select DESIGNATIONNAME from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(REVENUE.ID) where ROWNUMBER = 1) as DESIGNATION1NAME,
    (select DESIGNATIONPUBLICNAME from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(REVENUE.ID) where ROWNUMBER = 1) as DESIGNATION1PUBLICNAME,
    (select AMOUNT from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(REVENUE.ID) where ROWNUMBER = 1) as DESIGNATION1AMOUNT,
    (select DESIGNATIONNAME from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(REVENUE.ID) where ROWNUMBER = 2) as DESIGNATION2NAME,
    (select DESIGNATIONPUBLICNAME from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(REVENUE.ID) where ROWNUMBER = 2) as DESIGNATION2PUBLICNAME,
    (select AMOUNT from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(REVENUE.ID) where ROWNUMBER = 2) as DESIGNATION2AMOUNT,
    REVENUE.CALCULATEDUSERDEFINEDID as REVENUELOOKUPID,
    REVENUE.TRANSACTIONCURRENCYID,
    dbo.UFN_CURRENCY_GETISO(REVENUE.TRANSACTIONCURRENCYID) as TRANSACTIONCURRENCY
  from dbo.FINANCIALTRANSACTION as REVENUE
  inner join dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
  inner join dbo.[UFN_ADDRESSPROCESS_EMAILS](4, @PARAMETERSETID) EMAILADDRESS on CONSTITUENT.ID = EMAILADDRESS.CONSTITUENTID
  inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = REVENUE.ID 
  left join dbo.CONSTITUENT CONTACT on CONTACT.ID = EMAILADDRESS.CONTACTID
  left join dbo.APPEAL on REVENUE_EXT.APPEALID = APPEAL.ID
  where REVENUE.TYPECODE in (1,15)
  and REVENUE.DELETEDON is null 

  union all -- AS: Recurring Gifts


  select 
    FINANCIALTRANSACTION.ID as REVENUEID,
    CONSTITUENT.NAME as CONSTITUENTNAME,
    CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
    (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYADDRESSEE = 1) as ADDRESSEE,
    (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYSALUTATION = 1) as SALUTATION,
    isnull((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONTACT.ID and NAMEFORMAT.PRIMARYADDRESSEE = 1), CONTACT.NAME) as CONTACT,
    (select top 1 RELATIONSHIP.POSITION
     from dbo.RELATIONSHIP                        
     where RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
     and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID) as POSITION,
    EMAILADDRESS.EMAILADDRESS,
    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,
    APPEAL.NAME as APPEALNAME,
    (select DESIGNATIONNAME from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(FINANCIALTRANSACTION.ID) where ROWNUMBER = 1) as DESIGNATION1NAME,
    (select DESIGNATIONPUBLICNAME from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(FINANCIALTRANSACTION.ID) where ROWNUMBER = 1) as DESIGNATION1PUBLICNAME,
    (select AMOUNT from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(FINANCIALTRANSACTION.ID) where ROWNUMBER = 1) as DESIGNATION1AMOUNT,
    (select DESIGNATIONNAME from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(FINANCIALTRANSACTION.ID) where ROWNUMBER = 2) as DESIGNATION2NAME,
    (select DESIGNATIONPUBLICNAME from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(FINANCIALTRANSACTION.ID) where ROWNUMBER = 2) as DESIGNATION2PUBLICNAME,
    (select AMOUNT from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(FINANCIALTRANSACTION.ID) where ROWNUMBER = 2) as DESIGNATION2AMOUNT,
    FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID as REVENUELOOKUPID,
    FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
    dbo.UFN_CURRENCY_GETISO(FINANCIALTRANSACTION.TRANSACTIONCURRENCYID) as TRANSACTIONCURRENCY
  from dbo.FINANCIALTRANSACTION
  inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  inner join dbo.CONSTITUENT on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
  inner join dbo.[UFN_ADDRESSPROCESS_EMAILS](4, @PARAMETERSETID) EMAILADDRESS on CONSTITUENT.ID = EMAILADDRESS.CONSTITUENTID
  left join dbo.CONSTITUENT CONTACT on CONTACT.ID = EMAILADDRESS.CONTACTID
  left join dbo.APPEAL on REVENUE_EXT.APPEALID = APPEAL.ID
  where FINANCIALTRANSACTION.TYPECODE = 2
  and FINANCIALTRANSACTION.DELETEDON is null
)