UFN_QUERY_ACKNOWLEDGEMENTOUTPUT

CREATE function dbo.[UFN_QUERY_ACKNOWLEDGEMENTOUTPUT]
(
  @NAMEFORMATPARAMETERID uniqueidentifier,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier,
  @PARAMETERSETID uniqueidentifier,
  @SEASONALDATE datetime,
  @INDUSESEASONALADDRESS bit,
  @ORGMAILINGPREFERENCE tinyint,
  @INDALTADDRESS1TYPECODEID uniqueidentifier,
  @INDALTADDRESS1ISPRIMARY bit,
  @INDALTADDRESS2TYPECODEID uniqueidentifier,
  @INDALTADDRESS2ISPRIMARY bit,
  @ORGALTADDRESS1TYPECODEID uniqueidentifier,
  @ORGALTADDRESS1ISPRIMARY bit,
  @ORGALTADDRESS2TYPECODEID uniqueidentifier,
  @ORGALTADDRESS2ISPRIMARY bit,
  @INDINCLUDEWITHNOADDRESS bit,
  @ORGINCLUDEWITHNOADDRESS bit,
  @ORGINCLUDEWITHNOCONTACT bit,
  @ORGSENDTOALLCONTACTS bit,
  @INDUSECONSTITUENTPREFS bit,
  @ORGUSECONSTITUENTPREFS bit,
  @GROUPALTADDRESS1TYPECODEID uniqueidentifier,
  @GROUPALTADDRESS1ISPRIMARY bit,
  @GROUPALTADDRESS2TYPECODEID uniqueidentifier,
  @GROUPALTADDRESS2ISPRIMARY bit,
  @GROUPINCLUDEWITHNOADDRESS bit,
  @GROUPUSECONSTITUENTPREFS bit,
  @INDIVIDUALADDRESSEES xml,
  @ADDRESSEEFUNCTIONID uniqueidentifier,
  @INDIVIDUALSALUTATIONS xml,
  @SALUTATIONFUNCTIONID uniqueidentifier,
  @ORGADDRESSEES xml,
  @CONTACTADDRESSEEFUNCTIONID uniqueidentifier,
  @ORGSALUTATIONCODE tinyint,
  @ORGSALUTATIONS xml,
  @CONTACTSALUTATIONFUNCTIONID uniqueidentifier,
  @CONTACTSALUTATIONOPTIONCODE tinyint,
  @CUSTOMNAME nvarchar(100),
  @GROUPADDRESSEES xml,
  @GROUPADDRESSEEFUNCTIONID uniqueidentifier,
  @GROUPSALUTATIONCODE tinyint,
  @GROUPSALUTATIONS xml,
  @GROUPSALUTATIONFUNCTIONID uniqueidentifier,
  @GROUPNOCONTACTOPTIONCODE bit,
  @GROUPNOCONTACTCUSTOMNAME nvarchar(100),
  @JOINTRULETYPECODE As tinyint,
  @JOINTSELECTIONID As uniqueidentifier,
  @JOINTSELECTIONBOTHRULETYPECODE As tinyint,
  @JOINTSELECTIONNEITHERRULETYPECODE As tinyint
)
returns table
as
return  (
  with [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,
    A.ADDRESSEE,    
    A.SALUTATION,
    A.CONTACT as CONTACTADDRESSEE,
    A.POSITION,
    A.ADDRESSBLOCK,
    A.CITY,
    A.STATE,
    A.POSTCODE,
    A.COUNTRY,
    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,
    DONORADDRESS.ADDRESSBLOCK as DONORADDRESSBLOCK,
    DONORADDRESS.CITY as DONORCITY,
    DONORSTATE.ABBREVIATION as DONORSTATE,
    DONORADDRESS.POSTCODE as DONORPOSTCODE,
    DONORCOUNTRY.DESCRIPTION as DONORCOUNTRY,
    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],
    CONSTITUENT.ID as CONSTITUENTID,
    [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 REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
  inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUELETTER.ACKNOWLEDGEEID
  inner join dbo.CONSTITUENT DONOR on DONOR.ID = REVENUE.CONSTITUENTID
  inner join dbo.UFN_CONTACTPREFERENCES_GETFORMATS_EXT(
    @NAMEFORMATPARAMETERID,
    @ADDRESSPROCESSINGOPTIONID,
    0,
    @PARAMETERSETID,
    @SEASONALDATE,
    0,
    @INDUSESEASONALADDRESS ,
    @ORGMAILINGPREFERENCE ,
    @INDALTADDRESS1TYPECODEID,
    @INDALTADDRESS1ISPRIMARY ,
    @INDALTADDRESS2TYPECODEID,
    @INDALTADDRESS2ISPRIMARY ,
    @ORGALTADDRESS1TYPECODEID,
    @ORGALTADDRESS1ISPRIMARY ,
    @ORGALTADDRESS2TYPECODEID,
    @ORGALTADDRESS2ISPRIMARY ,
    @INDINCLUDEWITHNOADDRESS ,
    @ORGINCLUDEWITHNOADDRESS ,
    @ORGINCLUDEWITHNOCONTACT ,
    @ORGSENDTOALLCONTACTS ,
    @INDUSECONSTITUENTPREFS ,
    @ORGUSECONSTITUENTPREFS ,
    @GROUPALTADDRESS1TYPECODEID,
    @GROUPALTADDRESS1ISPRIMARY ,
    @GROUPALTADDRESS2TYPECODEID,
    @GROUPALTADDRESS2ISPRIMARY ,
    @GROUPINCLUDEWITHNOADDRESS ,
    @GROUPUSECONSTITUENTPREFS ,
    @INDIVIDUALADDRESSEES ,
    @ADDRESSEEFUNCTIONID,
    @INDIVIDUALSALUTATIONS ,
    @SALUTATIONFUNCTIONID,
    @ORGADDRESSEES ,
    @CONTACTADDRESSEEFUNCTIONID,
    @ORGSALUTATIONCODE,
    @ORGSALUTATIONS ,
    @CONTACTSALUTATIONFUNCTIONID,
    @CONTACTSALUTATIONOPTIONCODE ,
    @CUSTOMNAME ,
    @GROUPADDRESSEES ,
    @GROUPADDRESSEEFUNCTIONID,
    @GROUPSALUTATIONCODE,
    @GROUPSALUTATIONS ,
    @GROUPSALUTATIONFUNCTIONID,
    @GROUPNOCONTACTOPTIONCODE ,
    @GROUPNOCONTACTCUSTOMNAME,
    @JOINTRULETYPECODE,
    @JOINTSELECTIONID,
    @JOINTSELECTIONBOTHRULETYPECODE,
    @JOINTSELECTIONNEITHERRULETYPECODE, 
    0) A on CONSTITUENT.ID = A.CONSTITUENTID  
  left join dbo.APPEAL on REVENUE_EXT.APPEALID = APPEAL.ID
  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.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]
  where REVENUE.ID not in (select ID from dbo.REVENUE_EXT where DONOTACKNOWLEDGE = 1)
  and REVENUE.DELETEDON is null
  and REVENUE.TYPECODE <> 3
)