V_QUERY_ACKNOWLEDGEMENTPROCESS_SEASONALADDRESSOUTPUT

/*
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_ACKNOWLEDGEMENTPROCESS_SEASONALADDRESSOUTPUT 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,
  (select ADDRESSBLOCK from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as ADDRESSBLOCK,
  (select CITY from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as CITY,
  (select STATE from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as STATE,
  (select POSTCODE from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as POSTCODE,
  (select COUNTRY from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as COUNTRY,
  EMAILADDRESS.EMAILADDRESS,
  REVENUE.TYPE as REVENUETYPE,
  cast(REVENUE.DATE as datetime) as DATE,
  REVENUEPAYMENTMETHOD.PAYMENTMETHOD as PAYMENTMETHOD,
  case REVENUE_EXT.GIVENANONYMOUSLY when 1 then 'Yes' else 'No' end as GIVENANONYMOUSLY,
  case when [FIRSTGIFTS].[ID] is not null then 'Yes' else 'No' end as [ISFIRSTGIFT],
  REVENUE.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,
  case REVENUE_EXT.BENEFITSWAIVED when 1 then 'Yes' else 'No' end as BENEFITSWAIVED,

  REVENUEBENEFIT1.NAME as BENEFIT1NAME,
  REVENUEBENEFIT1.QUANTITY as BENEFIT1QUANTITY,
  REVENUEBENEFIT1.TRANSACTIONUNITVALUE as BENEFIT1UNITVALUE,
  REVENUEBENEFIT1.TRANSACTIONTOTALVALUE as BENEFIT1TOTALVALUE,

  REVENUEBENEFIT2.NAME as BENEFIT2NAME,
  REVENUEBENEFIT2.QUANTITY as BENEFIT2QUANTITY,
  REVENUEBENEFIT2.TRANSACTIONUNITVALUE as BENEFIT2UNITVALUE,
  REVENUEBENEFIT2.TRANSACTIONTOTALVALUE as BENEFIT2TOTALVALUE,

  STOCKDETAIL.ISSUER as ISSUER,
  STOCKDETAIL.SYMBOL as SYMBOL,
  STOCKDETAIL.NUMBEROFUNITS as NUMBEROFUNITS,
  STOCKDETAIL.TRANSACTIONMEDIANPRICE as MEDIANPRICE,
  PROPERTYGIKSUBTYPE = 
    case when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE in (5,8) then (select PTC.DESCRIPTION from dbo.PROPERTYSUBTYPECODE PTC where PTC.ID = PROPERTYDETAIL.PROPERTYSUBTYPECODEID)
    when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 6 then [GIKCODE].DESCRIPTION
    else null end,
  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.TYPECODE = 1 then 
        dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE([DDINSTALLMENT].ID)
      when REVENUE.TYPECODE = 2 then 
        REVENUE.TRANSACTIONAMOUNT
      else 
        null
      end
    )
  end  as [DDIINSTALLMENTAMOUNT],
  [DDACCOUNT].ACCOUNTNAME as [DDINAMEONACCOUNT],
  coalesce(GIFTAID.GROSSAMOUNT, REVENUE.TRANSACTIONAMOUNT) as AMOUNTWITHGIFTAID,
  [GIK].ITEMNAME as [GIFTINKINDITEMNAME],
  [GIK].DISPOSITION as [GIFTINKINDDISPOSITION],
  [GIK].NUMBEROFUNITS as [GIFTINKINDNUMBEROFUNITS],
  [GIK].TRANSACTIONFAIRMARKETVALUE as [GIFTINKINDFAIRMARKETVALUE],
  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.FINANCIALTRANSACTION REVENUE
inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
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 REVENUELETTER.REVENUEID = REVENUE.ID
inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUELETTER.ACKNOWLEDGEEID
left join dbo.CONSTITUENT DONOR on DONOR.ID = REVENUE.CONSTITUENTID
left join dbo.APPEAL on REVENUE_EXT.APPEALID = APPEAL.ID
left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID=CONSTITUENT.ID and EMAILADDRESS.ISPRIMARY = 1
left join dbo.STOCKDETAIL on REVENUEPAYMENTMETHOD.ID = STOCKDETAIL.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.TYPECODE = 1
left join
(
  select
    REVENUE.ID as REVENUEID,
    case REVENUE.TYPECODE 
      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.FINANCIALTRANSACTION REVENUE
  where exists(select * from dbo.INSTALLEDPRODUCTLIST where ID = '9568A6C2-F7AA-45fd-8F54-21FE9654EE2D')
    and REVENUE.DELETEDON is null
    and REVENUE.TYPECODE in (0,1,2,4,5,6,7,8,9,15)
) as GIFTAID on REVENUE.ID = GIFTAID.REVENUEID
left join dbo.GIFTINKINDPAYMENTMETHODDETAIL as [GIK] on [GIK].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.GIFTINKINDSUBTYPECODE as [GIKCODE] on [GIKCODE].ID = [GIK].GIFTINKINDSUBTYPECODEID
outer apply dbo.UFN_REVENUE_GETREVENUEBENEFITS_BYROWNUMBER(REVENUE.ID, 1) as REVENUEBENEFIT1
outer apply dbo.UFN_REVENUE_GETREVENUEBENEFITS_BYROWNUMBER(REVENUE.ID, 2) as REVENUEBENEFIT2
left join dbo.PROPERTYDETAIL on REVENUEPAYMENTMETHOD.ID = PROPERTYDETAIL.ID
left join dbo.[UFN_FINANCIALTRANSACTION_GETFIRSTGIFTS]() as [FIRSTGIFTS] on [FIRSTGIFTS].[ID] = [REVENUE].[ID]
where REVENUE_EXT.DONOTACKNOWLEDGE = 0
and REVENUE.DELETEDON is null
and REVENUE.TYPECODE <> 3