UFN_QUERY_TRIBUTEACKNOWLEDGEMENTOUTPUT

CREATE function [dbo].[UFN_QUERY_TRIBUTEACKNOWLEDGEMENTOUTPUT]
  (
    @NAMEFORMATPARAMETERID uniqueidentifier,
    @ADDRESSPROCESSINGOPTIONID uniqueidentifier,
    @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 [RELATIONSHIP_CTE] as (
    select
      [RELATIONSHIPJOBINFO].[JOBTITLE],
      [RELATIONSHIP].[RELATIONSHIPCONSTITUENTID],
      [CONSTITUENT].[NAME],
      [RELATIONSHIPJOBINFO].[STARTDATE]
    from dbo.[RELATIONSHIP] 
    inner join dbo.[RELATIONSHIPJOBINFO] on [RELATIONSHIPJOBINFO].[RELATIONSHIPSETID] = [RELATIONSHIP].[RELATIONSHIPSETID]
    inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [RELATIONSHIP].[RECIPROCALCONSTITUENTID]
    where [RELATIONSHIP].[ISPRIMARYCONTACT] = 1
  )
  select 
    REVENUETRIBUTELETTER.ID as REVENUETRIBUTELETTERID,
    FINANCIALTRANSACTION.ID as REVENUEID, 
    ACKNOWLEDGEE.NAME as ACKNOWLEDGEENAME,
    A.ADDRESSEE as ACKNOWLEDGEEADDRESSEE,
    A.SALUTATION as ACKNOWLEDGEESALUTATION,
    A.CONTACT as ACKNOWLEDGEECONTACT,
    A.POSITION as ACKNOWLEDGEEPOSITION,
    A.ADDRESSBLOCK as ACKNOWLEDGEEADDRESSBLOCK,
    A.CITY as ACKNOWLEDGEECITY,
    A.STATE as ACKNOWLEDGEESTATE,
    A.POSTCODE as ACKNOWLEDGEEPOSTCODE,
    A.COUNTRY as ACKNOWLEDGEECOUNTRY,
    TRIBUTE.TRIBUTETEXT,
    REVENUETRIBUTE.AMOUNT as REVENUETRIBUTEAMOUNT,    
    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 
          [RELATIONSHIP_CTE].[NAME] 
        from [RELATIONSHIP_CTE]
        where [RELATIONSHIP_CTE].[RELATIONSHIPCONSTITUENTID] = [CONSTITUENT].[ID]) 
    else null end as [PRIMARYCONTACT],
    case when [CONSTITUENT].[ISORGANIZATION] = 1 then 
      (select top 1
          [RELATIONSHIP_CTE].[JOBTITLE] 
        from [RELATIONSHIP_CTE]
        where [RELATIONSHIP_CTE].[RELATIONSHIPCONSTITUENTID] = [CONSTITUENT].[ID]
     order by [RELATIONSHIP_CTE].[STARTDATE] desc) 
    else null end as [POSITION],
    ADDRESS.ADDRESSBLOCK as ADDRESSBLOCK,
    ADDRESS.CITY as CITY,
    STATE.ABBREVIATION as STATE,
    ADDRESS.POSTCODE as POSTCODE,
    COUNTRY.DESCRIPTION as COUNTRY,
    EMAILADDRESS.EMAILADDRESS,
    FINANCIALTRANSACTION.TYPE as REVENUETYPE,
    cast(FINANCIALTRANSACTION.DATE as datetime) as DATE,
    case REVENUE_EXT.GIVENANONYMOUSLY when 1 then 'Yes' else 'No' end as GIVENANONYMOUSLY,
    FINANCIALTRANSACTION.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,
    coalesce(REVENUETRIBUTE.AMOUNT + REVENUETRIBUTETAXCLAIMAMOUNT.TAXCLAIMAMOUNT, REVENUETRIBUTE.AMOUNT) as GROSSAMOUNT,
    FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
    isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, ACCOUNTSYSTEMCURRENCY.ID) as BASECURRENCYID,
    isnull(NONPOSTABLEBASECURRENCY.ISO4217, ACCOUNTSYSTEMCURRENCY.ISO4217) as REVENUETRIBUTEAMOUNTCURRENCY,
    TRANSACTIONCURRENCY.ISO4217 as AMOUNTCURRENCY,
    TRANSACTIONCURRENCY.ISO4217 as RECEIPTAMOUNTCURRENCY,
    case 
      when REVENUEDESIGNATION1.DESIGNATIONID is not null then TRANSACTIONCURRENCY.ISO4217
      else null
    end as DESIGNATION1AMOUNTCURRENCY,
    case 
      when REVENUEDESIGNATION2.DESIGNATIONID is not null then TRANSACTIONCURRENCY.ISO4217
      else null
    end as DESIGNATION2AMOUNTCURRENCY,
    isnull(NONPOSTABLEBASECURRENCY.ISO4217, ACCOUNTSYSTEMCURRENCY.ISO4217) as GROSSAMOUNTCURRENCY
  from dbo.FINANCIALTRANSACTION
  inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
  inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
  inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
  inner join dbo.CURRENCY as ACCOUNTSYSTEMCURRENCY on ACCOUNTSYSTEMCURRENCY.ID = CURRENCYSET.BASECURRENCYID
  inner join dbo.CURRENCY as TRANSACTIONCURRENCY on TRANSACTIONCURRENCY.ID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
  left join dbo.CURRENCY as NONPOSTABLEBASECURRENCY on NONPOSTABLEBASECURRENCY.ID = REVENUE_EXT.NONPOSTABLEBASECURRENCYID
  left join dbo.REVENUETRIBUTE on FINANCIALTRANSACTION.ID = REVENUETRIBUTE.REVENUEID
  left join dbo.REVENUETRIBUTETAXCLAIMAMOUNT on REVENUETRIBUTE.ID = REVENUETRIBUTETAXCLAIMAMOUNT.ID
  left join dbo.TRIBUTE on REVENUETRIBUTE.TRIBUTEID = TRIBUTE.ID
  left join dbo.REVENUETRIBUTELETTER on REVENUETRIBUTE.ID = REVENUETRIBUTELETTER.REVENUETRIBUTEID
  outer apply dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS_BYROWNUMBER(FINANCIALTRANSACTION.ID,1) as REVENUEDESIGNATION1
  outer apply dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS_BYROWNUMBER(FINANCIALTRANSACTION.ID,2) as REVENUEDESIGNATION2
  inner join dbo.CONSTITUENT ACKNOWLEDGEE on REVENUETRIBUTELETTER.CONSTITUENTID = ACKNOWLEDGEE.ID
  inner join dbo.UFN_CONTACTPREFERENCES_GETFORMATS_EXT(
    @NAMEFORMATPARAMETERID,
    @ADDRESSPROCESSINGOPTIONID,
    7,
    null,
    @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) as A on ACKNOWLEDGEE.ID = A.CONSTITUENTID
  left join dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
  left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID and ADDRESS.ISPRIMARY = 1 
  left join dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
  left join dbo.STATE on ADDRESS.STATEID = STATE.ID
  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 FINANCIALTRANSACTION.ID = STOCKDETAIL.ID
  where
    REVENUE_EXT.DONOTACKNOWLEDGE = 0
    and FINANCIALTRANSACTION.TYPECODE in (0,1,2,4,5,6,7,8,9) -- not matching gift claims

    and CONSTITUENT.ID not in(select ID from dbo.DECEASEDCONSTITUENT)
    and FINANCIALTRANSACTION.DELETEDON is null

)