UFN_QUERY_ACKNOWLEDGEMENTPLEDGENAMEFORMATOUTPUT

CREATE function dbo.[UFN_QUERY_ACKNOWLEDGEMENTPLEDGENAMEFORMATOUTPUT]
(
  @NAMEFORMATPARAMETERID uniqueidentifier,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier,
  @PARAMETERSETID uniqueidentifier = null,
  @SEASONALDATE datetime = null,
  @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 [FIRSTINSTALLMENT_CTE] as
  (
    select
      [REVENUEID],
      [DATE],
      [TRANSACTIONAMOUNT]
    from dbo.[INSTALLMENT]
    where [SEQUENCE] = 1
  ),
  [NEXTINSTALLMENT_CTE] as
  (
    select
      [REVENUEID],
      [INSTALLMENTID]
    from dbo.[UFN_REVENUE_GETNEXTINSTALLMENT_BULK]()
  ),
  [GIFTAID_CTE] as
  (
    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')
  )
  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.TRANSACTIONAMOUNT as TOTALPLEDGEAMOUNT,
    REVENUE.DATE as PLEDGEDATE,
    SCHEDULE.FREQUENCY,
    SCHEDULE.STARTDATE,
    SCHEDULE.NUMBEROFINSTALLMENTS,
    [FIRSTINSTALLMENT_CTE].[DATE] as [FIRSTDUEDATE],
    [FIRSTINSTALLMENT_CTE].[TRANSACTIONAMOUNT] 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,
    [DESIGNATION1].[DESIGNATIONNAME] as [DESIGNATION1NAME],
    [DESIGNATION1].[DESIGNATIONPUBLICNAME] as [DESIGNATION1PUBLICNAME],
    [DESIGNATION1].[TRANSACTIONAMOUNT] as [DESIGNATION1AMOUNT],
    [DESIGNATION2].[DESIGNATIONNAME] as [DESIGNATION2NAME],
    [DESIGNATION2].[DESIGNATIONPUBLICNAME] as [DESIGNATION2PUBLICNAME],
    [DESIGNATION2].[TRANSACTIONAMOUNT] as [DESIGNATION2AMOUNT],
    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.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],
    isnull([GIFTAID_CTE].[GROSSAMOUNT], [REVENUE].[TRANSACTIONAMOUNT]) as [AMOUNTWITHGIFTAID],
    CONSTITUENT.ID as CONSTITUENTID,
    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.REVENUELETTER on REVENUE.ID = REVENUELETTER.REVENUEID
  inner join dbo.CONSTITUENT DONOR on REVENUE.CONSTITUENTID = DONOR.ID
  inner join dbo.CONSTITUENT on REVENUELETTER.ACKNOWLEDGEEID = CONSTITUENT.ID
  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.REVENUESCHEDULE SCHEDULE on REVENUE.ID = SCHEDULE.ID
  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 [NEXTINSTALLMENT_CTE] on [NEXTINSTALLMENT_CTE].[REVENUEID] = [DDPAYMENT].[ID]
  left join dbo.INSTALLMENT [DDINSTALLMENT] on [DDPAYMENT].ID is not null and REVENUE.TRANSACTIONTYPECODE = 1 and [DDINSTALLMENT].ID = [NEXTINSTALLMENT_CTE].[INSTALLMENTID]
  left join [GIFTAID_CTE] on [REVENUE].[ID] = [GIFTAID_CTE].[REVENUEID]
  left join dbo.[UFN_FINANCIALTRANSACTION_GETFIRSTGIFTS]() as [FIRSTGIFTS] on [FIRSTGIFTS].[ID] = [REVENUE].[ID]
  left join [FIRSTINSTALLMENT_CTE] on [FIRSTINSTALLMENT_CTE].[REVENUEID] = [REVENUE].[ID]
  left join dbo.[CURRENCY] on [CURRENCY].[ID] = [REVENUE].[TRANSACTIONCURRENCYID]
  outer apply dbo.[UFN_REVENUE_GETORDEREDDESIGNATIONS_BYROWNUMBER]([REVENUE].[ID], 1) as [DESIGNATION1]
  outer apply dbo.[UFN_REVENUE_GETORDEREDDESIGNATIONS_BYROWNUMBER]([REVENUE].[ID], 2) as [DESIGNATION2]
  where REVENUE.ID not in (select ID from dbo.REVENUE_EXT where DONOTACKNOWLEDGE = 1)
  and REVENUE.TRANSACTIONTYPECODE <> 3
)