UFN_QUERY_ACKNOWLEDGEMENTPROCESSEMAILOUTPUT

CREATE function dbo.[UFN_QUERY_ACKNOWLEDGEMENTPROCESSEMAILOUTPUT]
(
  @PARAMETERSETID uniqueidentifier = null
)
returns table
as
return (
  with [NAMEFORMATS_CTE] as
  (
    select
      CONSTITUENTID,
      PRIMARYADDRESSEE,
      PRIMARYSALUTATION,
      FORMATTEDNAME
    from dbo.NAMEFORMAT
    where PRIMARYADDRESSEE = 1
    or PRIMARYSALUTATION = 1
  ),
  [NEXTINSTALLMENT_CTE] as
  (
    select
      [REVENUEID],
      [INSTALLMENTID]
    from dbo.[UFN_REVENUE_GETNEXTINSTALLMENT_BULK]()
  ),
  [GIFTAID_CTE] as
  (
    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 REVENUE.DELETEDON is null
    and REVENUE.TYPECODE in (0,1,2,4,5,6,7,8,9,15)
    and exists(select * from dbo.INSTALLEDPRODUCTLIST where ID = '9568A6C2-F7AA-45fd-8F54-21FE9654EE2D')
  )
  select 
    REVENUE.ID as REVENUEID, 
    CONSTITUENT.NAME as CONSTITUENTNAME,
    CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
    (case when [NAMEFORMATS_CTE].[PRIMARYADDRESSEE] = 1 then [NAMEFORMATS_CTE].[FORMATTEDNAME] else null end) as ADDRESSEE,
    (case when [NAMEFORMATS_CTE].[PRIMARYSALUTATION] = 1 then [NAMEFORMATS_CTE].[FORMATTEDNAME] else null end) as SALUTATION,
    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.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,
    [DESIGNATION1].[DESIGNATIONNAME] as [DESIGNATION1NAME],
    [DESIGNATION1].[DESIGNATIONPUBLICNAME] as [DESIGNATION1PUBLICNAME],
    [DESIGNATION1].[TRANSACTIONAMOUNT] as [DESIGNATION1AMOUNT],
    [DESIGNATION2].[DESIGNATIONNAME] as [DESIGNATION2NAME],
    [DESIGNATION2].[DESIGNATIONPUBLICNAME] as [DESIGNATION2PUBLICNAME],
    [DESIGNATION2].[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,
    REVENUELETTER.ID as REVENUELETTERID,
    (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = DONOR.ID and NAMEFORMAT.PRIMARYADDRESSEE = 1) as DONORADDRESSEE,
    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
      (
        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],
    isnull([GIFTAID_CTE].[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.REVENUELETTER on REVENUE.ID = REVENUELETTER.REVENUEID
  inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
  inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUELETTER.ACKNOWLEDGEEID
  inner join dbo.CONSTITUENT DONOR on DONOR.ID = REVENUE.CONSTITUENTID
  inner join dbo.[UFN_ADDRESSPROCESS_EMAILS](0, @PARAMETERSETID) AS EMAILADDRESS ON CONSTITUENT.ID = EMAILADDRESS.CONSTITUENTID
  left join dbo.CONSTITUENT CONTACT on EMAILADDRESS.CONTACTID = CONTACT.ID
  left join dbo.APPEAL on REVENUE_EXT.APPEALID = APPEAL.ID
  left join dbo.STOCKDETAIL on REVENUEPAYMENTMETHOD.ID = STOCKDETAIL.ID
  left join dbo.EMAILADDRESS DONOREMAILADDRESS on DONOREMAILADDRESS.CONSTITUENTID = DONOR.ID and DONOREMAILADDRESS.ISPRIMARY = 1 and DONOREMAILADDRESS.DONOTEMAIL = 0
  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.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 [NEXTINSTALLMENT_CTE] on [NEXTINSTALLMENT_CTE].[REVENUEID] = [DDPAYMENT].[ID]
  left join dbo.INSTALLMENT [DDINSTALLMENT] on [DDPAYMENT].ID is not null and REVENUE.TYPECODE = 1 and [DDINSTALLMENT].ID = [NEXTINSTALLMENT_CTE].[INSTALLMENTID]
  left join [GIFTAID_CTE] on [REVENUE].[ID] = [GIFTAID_CTE].[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
  outer apply dbo.[UFN_REVENUE_GETORDEREDDESIGNATIONS_BYROWNUMBER]([REVENUE].[ID], 1) as [DESIGNATION1]
  outer apply dbo.[UFN_REVENUE_GETORDEREDDESIGNATIONS_BYROWNUMBER]([REVENUE].[ID], 2) as [DESIGNATION2]
  left join dbo.PROPERTYDETAIL on REVENUEPAYMENTMETHOD.ID = PROPERTYDETAIL.ID
  left join dbo.[UFN_FINANCIALTRANSACTION_GETFIRSTGIFTS]() as [FIRSTGIFTS] on [FIRSTGIFTS].[ID] = [REVENUE].[ID]
  left join dbo.[CURRENCY] on [CURRENCY].[ID] = [REVENUE].[TRANSACTIONCURRENCYID]
  left join [NAMEFORMATS_CTE] on [NAMEFORMATS_CTE].[CONSTITUENTID] = [CONSTITUENT].[ID]
  where REVENUE.ID not in (select ID from dbo.REVENUE_EXT where DONOTACKNOWLEDGE = 1)
  and REVENUE.DELETEDON is null
  and REVENUE.TYPECODE <> 3
)