V_QUERY_ACKNOWLEDGEMENTPROCESSPLEDGEOUTPUT

/*
Generated by Blackbaud AppFx Platform
Date:  1/21/2016 5:07:46 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.154.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_ACKNOWLEDGEMENTPROCESSPLEDGEOUTPUT AS



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 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) 
    CONTACT.NAME 
    from dbo.RELATIONSHIP
    inner join dbo.CONSTITUENT CONTACT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
    where RELATIONSHIP.ISPRIMARYCONTACT = 1
    and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID) 
  else
    null
  end as PRIMARYCONTACT,
  ADDRESS.ADDRESSBLOCK as ADDRESSBLOCK,
  ADDRESS.CITY as CITY,
  STATE.ABBREVIATION as STATE,
  ADDRESS.POSTCODE as POSTCODE,
  COUNTRY.DESCRIPTION as COUNTRY,
  EMAILADDRESS.EMAILADDRESS,
  REVENUE.TRANSACTIONAMOUNT as TOTALPLEDGEAMOUNT,
  REVENUE.DATE as PLEDGEDATE,
  SCHEDULE.FREQUENCY,
  SCHEDULE.STARTDATE,
  SCHEDULE.NUMBEROFINSTALLMENTS,
  (select INSTALLMENT.DATE from dbo.INSTALLMENT where INSTALLMENT.REVENUEID = REVENUE.ID and INSTALLMENT.SEQUENCE = 1) as FIRSTDUEDATE,
  (select INSTALLMENT.TRANSACTIONAMOUNT from dbo.INSTALLMENT where INSTALLMENT.REVENUEID = REVENUE.ID and INSTALLMENT.SEQUENCE = 1) as FIRSTINSTALLMENTAMOUNT,
  case SCHEDULE.SENDPLEDGEREMINDER when 1 then 'Yes' else 'No' end as SENDPLEDGEREMINDER,
  case REVENUE.GIVENANONYMOUSLY when 1 then 'Yes' else 'No' end as GIVENANONYMOUSLY,
  case when [FIRSTGIFTS].[ID] is not null then 'Yes' else 'No' end as [ISFIRSTGIFT],
  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,
  case when CONSTITUENT.ISORGANIZATION = 1 then
    (select top 1 RELATIONSHIP.POSITION from dbo.RELATIONSHIP 
      inner join dbo.CONSTITUENT CONTACT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
      where RELATIONSHIP.ISPRIMARYCONTACT = 1
      and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID)
  else
    null
  end as POSITION,
  REVENUELETTER.ID as REVENUELETTERID,
  (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = DONOR.ID and NAMEFORMAT.PRIMARYADDRESSEE = 1) as DONORADDRESSEE,
  DONORADDRESS.ADDRESSBLOCK as DONORADDRESSBLOCK,
  DONORADDRESS.CITY as DONORCITY,
  DONORSTATE.ABBREVIATION as DONORSTATE,
  DONORADDRESS.POSTCODE as DONORPOSTCODE,
  DONORCOUNTRY.DESCRIPTION as DONORCOUNTRY,
  DONOREMAILADDRESS.EMAILADDRESS as DONOREMAILADDRESS,
  case when [DDPAYMENT].ID is null then null else [DONOR].NAME end as [DDIACCOUNTHOLDER],
  case when [DDPAYMENT].ID is null then null else [DONORADDRESS].ADDRESSBLOCK end as [DDIACCOUNTHOLDERADDRESSBLOCK],
  case when [DDPAYMENT].ID is null then null else [DONORADDRESS].CITY end as [DDIACCOUNTHOLDERCITY],
  case when [DDPAYMENT].ID is null then null else [DONORSTATE].ABBREVIATION end as [DDIACCOUNTHOLDERSTATE],
  case when [DDPAYMENT].ID is null then null else [DONORADDRESS].POSTCODE end as [DDIACCOUNTHOLDERPOSTCODE],
  case when [DDPAYMENT].ID is null then null else [DONORCOUNTRY].DESCRIPTION end as [DDIACCOUNTHOLDERCOUNTRY],
  case when [DDPAYMENT].ID is null then null else convert(nvarchar(50), DecryptByKey([DDACCOUNT].ACCOUNTNUMBER)) end as [DDIACCOUNTNUMBER],
  case when [DDPAYMENT].ID is null then null else [DDBANK].FINANCIALINSTITUTION end as [DDIACCOUNTBANK],
  case when [DDPAYMENT].ID is null then null else [DDBANK].BRANCHNAME end as [DDIACCOUNTBRANCH],
  case when [DDPAYMENT].ID is null then null else [DDBANK].ADDRESSBLOCK end as [DDIACCOUNTBANKADDRESSBLOCK],
  case when [DDPAYMENT].ID is null then null else [DDBANK].CITY end as [DDIACCOUNTBANKCITY],
  case when [DDPAYMENT].ID is null then null else [DDBANKSTATE].ABBREVIATION end as [DDIACCOUNTBANKSTATE],
  case when [DDPAYMENT].ID is null then null else [DDBANK].POSTCODE end as [DDIACCOUNTBANKPOSTCODE],
  case when [DDPAYMENT].ID is null then null else [DDBANKCOUNTRY].DESCRIPTION end as [DDIACCOUNTBANKCOUNTRY],
  case when [DDPAYMENT].ID is null then null else [DDSCHEDULE].NUMBEROFINSTALLMENTS end as [DDINUMBEROFINSTALLMENTS],
  case when [DDPAYMENT].ID is null then null else [DDSCHEDULE].FREQUENCY end as [DDIFREQUENCY],
  case when [DDPAYMENT].ID is null then null else [DDSCHEDULE].STARTDATE end as [DDISTARTDATE],
  case when [DDPAYMENT].ID is null then null else dbo.UFN_REVENUE_GETLASTTRANSACTIONDATE(REVENUE.ID) end as [DDIENDDATE],
  case when [DDPAYMENT].ID is null then null
    else
    (
      select case when REVENUE.TRANSACTIONTYPECODE = 1 then 
        dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE([DDINSTALLMENT].ID)
      when REVENUE.TRANSACTIONTYPECODE = 2 then 
        REVENUE.TRANSACTIONAMOUNT
      else 
        null
      end
    )
  end  as [DDIINSTALLMENTAMOUNT],
  [DDACCOUNT].ACCOUNTNAME as [DDINAMEONACCOUNT],
  coalesce(GIFTAID.GROSSAMOUNT, REVENUE.TRANSACTIONAMOUNT) as AMOUNTWITHGIFTAID,
  REVENUE.TRANSACTIONCURRENCYID,
  CURRENCY.ISO4217 as TRANSACTIONCURRENCY,
  case
    when len(coalesce(convert(nvarchar(50), DecryptByKey([DDACCOUNT].ACCOUNTNUMBER)),'')) = 0 then ''
    else replicate('*', len(coalesce(convert(nvarchar(50), DecryptByKey([DDACCOUNT].ACCOUNTNUMBER)),'')) -
      len(right(coalesce(convert(nvarchar(50), DecryptByKey([DDACCOUNT].ACCOUNTNUMBER)),''), 4))) +
      right(coalesce(convert(nvarchar(50), DecryptByKey([DDACCOUNT].ACCOUNTNUMBER)),''), 4)
  end as [DDIACCOUNTNUMBER_MASKED]
from dbo.REVENUE
inner join dbo.CURRENCY on CURRENCY.ID = REVENUE.TRANSACTIONCURRENCYID
outer apply dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS_BYROWNUMBER(REVENUE.ID,1) REVENUEDESIGNATION1
outer apply dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS_BYROWNUMBER(REVENUE.ID,2) REVENUEDESIGNATION2
inner join dbo.REVENUELETTER on REVENUE.ID = REVENUELETTER.REVENUEID
inner join dbo.CONSTITUENT on REVENUELETTER.ACKNOWLEDGEEID = CONSTITUENT.ID
left join dbo.CONSTITUENT DONOR on REVENUE.CONSTITUENTID = DONOR.ID
left join dbo.REVENUESCHEDULE SCHEDULE on REVENUE.ID = SCHEDULE.ID
left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1 and ADDRESS.DONOTMAIL = 0
left join dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
left join dbo.STATE on ADDRESS.STATEID = STATE.ID
left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID=CONSTITUENT.ID and EMAILADDRESS.ISPRIMARY = 1
left join dbo.APPEAL on REVENUE.APPEALID = APPEAL.ID
left join dbo.ADDRESS DONORADDRESS on DONORADDRESS.CONSTITUENTID = DONOR.ID and DONORADDRESS.ISPRIMARY = 1 and DONORADDRESS.DONOTMAIL = 0
left join dbo.COUNTRY DONORCOUNTRY on DONORADDRESS.COUNTRYID = DONORCOUNTRY.ID
left join dbo.STATE DONORSTATE on DONORADDRESS.STATEID = DONORSTATE.ID
left join dbo.EMAILADDRESS DONOREMAILADDRESS on DONOREMAILADDRESS.CONSTITUENTID = DONOR.ID and DONOREMAILADDRESS.ISPRIMARY = 1 and DONOREMAILADDRESS.DONOTEMAIL = 0
left join dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT as [DDPAYMENT] on [DDPAYMENT].ID = REVENUE.ID
left join dbo.CONSTITUENTACCOUNT as [DDACCOUNT] on [DDACCOUNT].ID = [DDPAYMENT].CONSTITUENTACCOUNTID
left join dbo.FINANCIALINSTITUTION as [DDBANK] on [DDBANK].ID = [DDACCOUNT].FINANCIALINSTITUTIONID
left join dbo.STATE as [DDBANKSTATE] on [DDBANKSTATE].ID = [DDBANK].STATEID
left join dbo.COUNTRY as [DDBANKCOUNTRY] on [DDBANKCOUNTRY].ID = [DDBANK].COUNTRYID
left join dbo.REVENUESCHEDULE as [DDSCHEDULE] on [DDSCHEDULE].ID = [DDPAYMENT].ID
left join dbo.INSTALLMENT [DDINSTALLMENT] on [DDINSTALLMENT].ID = dbo.UFN_REVENUE_GETNEXTINSTALLMENT([DDPAYMENT].ID) and not [DDPAYMENT].ID is null and REVENUE.TRANSACTIONTYPECODE = 1
left join
  (
    select
      REVENUE.ID as REVENUEID,
      case REVENUE.TRANSACTIONTYPECODE 
        when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(REVENUE.ID, 1, 1)
        when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT_2(REVENUE.ID, 1)
        when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(REVENUE.ID, 1, 1)
      end as GROSSAMOUNT
    from dbo.REVENUE
    where exists(select * from dbo.INSTALLEDPRODUCTLIST where ID = '9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') 
  ) as GIFTAID on REVENUE.ID = GIFTAID.REVENUEID
left join dbo.[UFN_FINANCIALTRANSACTION_GETFIRSTGIFTS]() as [FIRSTGIFTS] on [FIRSTGIFTS].[ID] = [REVENUE].[ID]
where REVENUE.DONOTACKNOWLEDGE = 0
and REVENUE.TRANSACTIONTYPECODE <> 3