V_QUERY_ACKNOWLEDGEMENTPROCESS

/*
Generated by Blackbaud AppFx Platform
Date:  5/3/2024 2:16:43 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3700.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_ACKNOWLEDGEMENTPROCESS AS



select 
  REVENUE.ID as REVENUEID, 
  CONSTITUENT.NAME as CONSTITUENTNAME,
  CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
  FORMATTEDNAMES.PRIMARYADDRESSEE,
  FORMATTEDNAMES.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,
  isnull(PRIMARYCONTACTADDRESS.ADDRESSBLOCK, ADDRESS.ADDRESSBLOCK) as ADDRESSBLOCK,
  isnull(PRIMARYCONTACTADDRESS.CITY, ADDRESS.CITY) as CITY,
  STATE.ABBREVIATION as STATE,
  isnull(PRIMARYCONTACTADDRESS.POSTCODE, ADDRESS.POSTCODE) as POSTCODE,
  COUNTRY.DESCRIPTION as COUNTRY,
  EMAILADDRESS.EMAILADDRESS,
  case REVENUE.TYPECODE 
    when 0 then N'Payment' 
    when 1 then N'Pledge' 
    when 2 then N'Recurring gift' 
    when 3 then N'Matching gift claim' 
    when 4 then N'Planned gift' 
    when 5 then N'Order' 
    when 6 then N'Grant award' 
    when 7 then N'Auction donation' 
    when 8 then N'Donor challenge claim' 
    when 9 then N'Pending gift'
    when 15 then N'Membership installment plan'
    end as REVENUETYPE, 
  cast(REVENUE.DATE as datetime) as DATE,
  REVENUEPAYMENTMETHOD.PAYMENTMETHOD as PAYMENTMETHOD,
  REVENUE.GIVENANONYMOUSLY,
  case when [FIRSTGIFTS].[ID] is not null then 'Yes' else 'No' end as [ISFIRSTGIFT],
  REVENUE.TRANSACTIONAMOUNT as AMOUNT,
  REVENUE.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,
  REVENUE.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 in (1,15) then 
        dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE([NEXTINSTALLMENTS].[INSTALLMENTID])
      when REVENUE.TYPECODE = 2 then 
        REVENUE.TRANSACTIONAMOUNT
      else 
        null
      end
    )
  end  as [DDIINSTALLMENTAMOUNT],
  [DDACCOUNT].ACCOUNTNAME as [DDINAMEONACCOUNT],
  isnull(GIFTAID.GROSSAMOUNT, REVENUE.TRANSACTIONAMOUNT) as AMOUNTWITHGIFTAID,
  [GIK].ITEMNAME as [GIFTINKINDITEMNAME],
  [GIK].DISPOSITION as [GIFTINKINDDISPOSITION],
  [GIK].NUMBEROFUNITS as [GIFTINKINDNUMBEROFUNITS],
  [GIK].TRANSACTIONFAIRMARKETVALUE as [GIFTINKINDFAIRMARKETVALUE],
  REVENUEBENEFIT1.VALUEPERCENT as BENEFIT1PERCENTVALUE,
  REVENUEBENEFIT2.VALUEPERCENT as BENEFIT2PERCENTVALUE,

  REVENUE.TRANSACTIONCURRENCYID,
  C.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 
(
  select
    FT.ID
    ,FT.TYPECODE
    ,FT.DATE
    ,case REX.GIVENANONYMOUSLY when 1 then 'Yes' else 'No' end as GIVENANONYMOUSLY
    ,FT.CONSTITUENTID
    ,FT.TRANSACTIONAMOUNT
    ,REX.RECEIPTAMOUNT
    ,case REX.BENEFITSWAIVED when 1 then 'Yes' else 'No' end as BENEFITSWAIVED
    ,FT.TRANSACTIONCURRENCYID
    ,REX.APPEALID
  from dbo.FINANCIALTRANSACTION as FT
  inner join dbo.REVENUE_EXT as REX ON FT.ID = REX.ID
  where
    FT.ID not in (select ID FROM REVENUE_EXT where donotacknowledge = 1)
    --REVENUE_EXT.DONOTACKNOWLEDGE = 0

    and FT.TYPECODE != 3
    and (FT.TYPECODE < 10 or FT.TYPECODE = 15)
    and FT.DELETEDON is null
) as REVENUE
inner join dbo.CURRENCY as C on C.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 outer join
(
  select 
    case 
      when NFADDRESSEE.NAMEFORMATFUNCTIONID is null
      then NFADDRESSEE.CUSTOMNAME
      else
        replace(dbo.UFN_BUILDNAMEFORMAT(
        NFADDRESSEE.NAMEFORMATFUNCTIONID,
        CONSTITUENT.ID,
        CONSTITUENT.KEYNAME,
        CONSTITUENT.FIRSTNAME,
        CONSTITUENT.MIDDLENAME,
        CONSTITUENTTITLECODE.DESCRIPTION,
        CONSTITUENTSUFFIXCODE.DESCRIPTION,
        case when DECEASEDCONSTITUENT.ID is null  and SPOUSE.ISINACTIVE =0 then SPOUSE.KEYNAME else null end,
        case when DECEASEDCONSTITUENT.ID is null and SPOUSE.ISINACTIVE =0 then SPOUSE.FIRSTNAME else null end,
        case when DECEASEDCONSTITUENT.ID is null and SPOUSE.ISINACTIVE =0 then SPOUSE.MIDDLENAME else null end,
        case when DECEASEDCONSTITUENT.ID is null and SPOUSE.ISINACTIVE =0 then SPOUSETITLECODE.DESCRIPTION else null end,
        case when DECEASEDCONSTITUENT.ID is null and SPOUSE.ISINACTIVE =0 then SPOUSESUFFIXCODE.DESCRIPTION else null end
      ), N'{CONDBREAK}', N' ')
    end as PRIMARYADDRESSEE,
    case
      when NFSALUTATION.NAMEFORMATFUNCTIONID is null
      then NFSALUTATION.CUSTOMNAME
      else
        replace(dbo.UFN_BUILDNAMEFORMAT(
        NFSALUTATION.NAMEFORMATFUNCTIONID,
        CONSTITUENT.ID,
        CONSTITUENT.KEYNAME,
        CONSTITUENT.FIRSTNAME,
        CONSTITUENT.MIDDLENAME,
        CONSTITUENTTITLECODE.DESCRIPTION,
        CONSTITUENTSUFFIXCODE.DESCRIPTION,
        case when DECEASEDCONSTITUENT.ID is null and SPOUSE.ISINACTIVE =0 then SPOUSE.KEYNAME else null end,
        case when DECEASEDCONSTITUENT.ID is null and SPOUSE.ISINACTIVE =0 then SPOUSE.FIRSTNAME else null end,
        case when DECEASEDCONSTITUENT.ID is null and SPOUSE.ISINACTIVE =0 then SPOUSE.MIDDLENAME else null end,
        case when DECEASEDCONSTITUENT.ID is null and SPOUSE.ISINACTIVE =0 then SPOUSETITLECODE.DESCRIPTION else null end,
        case when DECEASEDCONSTITUENT.ID is null and SPOUSE.ISINACTIVE =0 then SPOUSESUFFIXCODE.DESCRIPTION else null end
      ), N'{CONDBREAK}', N' ')
    end as PRIMARYSALUTATION,
    CONSTITUENT.ID
  from dbo.CONSTITUENT
    left outer join dbo.RELATIONSHIP on CONSTITUENT.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID and RELATIONSHIP.ISSPOUSE = 1
    left outer join dbo.CONSTITUENT as SPOUSE on SPOUSE.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
    left outer join dbo.DECEASEDCONSTITUENT on DECEASEDCONSTITUENT.ID = SPOUSE.ID
    left outer join dbo.NAMEFORMAT as NFADDRESSEE on NFADDRESSEE.CONSTITUENTID = CONSTITUENT.ID and NFADDRESSEE.PRIMARYADDRESSEE=1
    left outer join dbo.NAMEFORMAT as NFSALUTATION on NFSALUTATION.CONSTITUENTID = CONSTITUENT.ID and NFSALUTATION.PRIMARYSALUTATION=1
    left outer join dbo.TITLECODE as CONSTITUENTTITLECODE on CONSTITUENTTITLECODE.ID = CONSTITUENT.TITLECODEID
    left outer join dbo.SUFFIXCODE as CONSTITUENTSUFFIXCODE on CONSTITUENTSUFFIXCODE.ID = CONSTITUENT.SUFFIXCODEID
    left outer join dbo.TITLECODE as SPOUSETITLECODE on SPOUSETITLECODE.ID = SPOUSE.TITLECODEID
    left outer join dbo.SUFFIXCODE as SPOUSESUFFIXCODE on SPOUSESUFFIXCODE.ID = SPOUSE.SUFFIXCODEID
) as FORMATTEDNAMES on CONSTITUENT.ID = FORMATTEDNAMES.ID

left join dbo.RELATIONSHIP ORGCONTACTS ON CONSTITUENT.ISORGANIZATION = 1 and ORGCONTACTS.ISPRIMARYCONTACT = 1 and ORGCONTACTS.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID
left join dbo.ADDRESS PRIMARYCONTACTADDRESS ON PRIMARYCONTACTADDRESS.RELATIONSHIPID = ORGCONTACTS.ID and PRIMARYCONTACTADDRESS.DONOTMAIL = 0
left join dbo.ADDRESS on PRIMARYCONTACTADDRESS.ID is null and ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1 and ADDRESS.DONOTMAIL = 0
left join dbo.COUNTRY on isnull(PRIMARYCONTACTADDRESS.COUNTRYID, ADDRESS.COUNTRYID) = COUNTRY.ID
left join dbo.STATE on isnull(PRIMARYCONTACTADDRESS.STATEID, ADDRESS.STATEID) = STATE.ID
left join dbo.APPEAL on REVENUE.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.[UFN_REVENUE_GETNEXTINSTALLMENT_BULK]() as [NEXTINSTALLMENTS] on [NEXTINSTALLMENTS].[REVENUEID] = [DDPAYMENT].[ID]

left join
(
  select
    FINANCIALTRANSACTION.ID as REVENUEID,
    case FINANCIALTRANSACTION.TYPECODE 
      when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(FINANCIALTRANSACTION.ID, 1, 1)
      when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT_2(FINANCIALTRANSACTION.ID, 1)
      when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(FINANCIALTRANSACTION.ID, 1, 1)
    end as GROSSAMOUNT
  from dbo.FINANCIALTRANSACTION
  where (FINANCIALTRANSACTION.TYPECODE < 10 or FINANCIALTRANSACTION.TYPECODE = 15)
  and FINANCIALTRANSACTION.DELETEDON is null
  and exists(select * from dbo.INSTALLEDPRODUCTLIST where ID = '9568A6C2-F7AA-45fd-8F54-21FE9654EE2D')
) 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]