UFN_QUERY_RECEIPTOUTPUTEXTENDED

CREATE function [dbo].[UFN_QUERY_RECEIPTOUTPUTEXTENDED]
(
  @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
(
  select 
    FINANCIALTRANSACTION.ID,
    CONSTITUENT.NAME CONSTITUENTNAME,    
    CONSTITUENT.LOOKUPID CONSTITUENTLOOKUPID,    
    A.ADDRESSEE,    
    A.SALUTATION,
    A.CONTACT as CONTACTADDRESSEE,
    A.POSITION,
    A.ADDRESSBLOCK as ADDRESSBLOCK,
    A.CITY as CITY,
    A.STATE as STATE,
    A.POSTCODE as POSTCODE,
    A.COUNTRY as COUNTRY,
    cast([FINANCIALTRANSACTION].[DATE] as datetime) as [DATE],
    FINANCIALTRANSACTION.TRANSACTIONAMOUNT as PAYMENTAMOUNT,
    REVENUE_EXT.RECEIPTAMOUNT,
    coalesce((select sum(RB.TRANSACTIONTOTALVALUE) from dbo.REVENUEBENEFIT RB where RB.REVENUEID = FINANCIALTRANSACTION.ID), 0) as TOTALBENEFITAMOUNT,
    REVENUEPAYMENTMETHOD.PAYMENTMETHOD as PAYMENTMETHOD,
    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 PTC.DESCRIPTION
      when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 6 then GIKTC.DESCRIPTION
      else null end,
    case REVENUE_EXT.GIVENANONYMOUSLY when 1 then 'Yes' else 'No' end as GIVENANONYMOUSLY,

    [REVENUEVAT].TRANSACTIONAMOUNTTOTAX as [PORTIONSUBJECTTOVAT],
    [VATTAXRATE].DESCRIPTION as [VATTAXRATEDESCRIPTION],
    [VATTAXRATE].TAXRATE as [VATTAXRATE],
    [REVENUEVAT].TRANSACTIONVATAMOUNT as VATAMOUNT,

    OTHERPAYMENTMETHODCODE.DESCRIPTION as OTHERPAYMENTMETHOD,
    REVENUEREFERENCE.REFERENCE,


    -- Tributes fields.  Amounts are intentionally outputted in base currency.

    TRIBUTE1.TYPE as TRIBUTE1TYPE,
    TRIBUTE1.TRIBUTEE as TRIBUTE1TRIBUTEE,
    TRIBUTE1.TRIBUTETEXT as TRIBUTE1TRIBUTETEXT,
    TRIBUTE1.DEFAULTDESIGNATION as TRIBUTE1DEFAULTDESIGNATION,
    TRIBUTE1.AMOUNT as TRIBUTE1AMOUNT,

    TRIBUTE2.TYPE as TRIBUTE2TYPE,
    TRIBUTE2.TRIBUTEE as TRIBUTE2TRIBUTEE,
    TRIBUTE2.TRIBUTETEXT as TRIBUTE2TRIBUTETEXT,
    TRIBUTE2.DEFAULTDESIGNATION as TRIBUTE2DEFAULTDESIGNATION,
    TRIBUTE2.AMOUNT as TRIBUTE2AMOUNT,

    TRIBUTE3.TYPE as TRIBUTE3TYPE,
    TRIBUTE3.TRIBUTEE as TRIBUTE3TRIBUTEE,
    TRIBUTE3.TRIBUTETEXT as TRIBUTE3TRIBUTETEXT,
    TRIBUTE3.DEFAULTDESIGNATION as TRIBUTE3DEFAULTDESIGNATION,
    TRIBUTE3.AMOUNT as TRIBUTE3AMOUNT,

    TRIBUTE4.TYPE as TRIBUTE4TYPE,
    TRIBUTE4.TRIBUTEE as TRIBUTE4TRIBUTEE,
    TRIBUTE4.TRIBUTETEXT as TRIBUTE4TRIBUTETEXT,
    TRIBUTE4.DEFAULTDESIGNATION as TRIBUTE4DEFAULTDESIGNATION,
    TRIBUTE4.AMOUNT as TRIBUTE4AMOUNT,

    TRIBUTE5.TYPE as TRIBUTE5TYPE,
    TRIBUTE5.TRIBUTEE as TRIBUTE5TRIBUTEE,
    TRIBUTE5.TRIBUTETEXT as TRIBUTE5TRIBUTETEXT,
    TRIBUTE5.DEFAULTDESIGNATION as TRIBUTE5DEFAULTDESIGNATION,
    TRIBUTE5.AMOUNT as TRIBUTE5AMOUNT,


    -- Matching Gifts fields

    MATCHINGGIFT1.ORGANIZATION as MATCHINGGIFT1ORGANIZATION,
    MATCHINGGIFT1.CONDITIONTYPE as MATCHINGGIFT1CONDITIONTYPE,
    MATCHINGGIFT1.AMOUNT as MATCHINGGIFT1AMOUNT,

    MATCHINGGIFT2.ORGANIZATION as MATCHINGGIFT2ORGANIZATION,
    MATCHINGGIFT2.CONDITIONTYPE as MATCHINGGIFT2CONDITIONTYPE,
    MATCHINGGIFT2.AMOUNT as MATCHINGGIFT2AMOUNT,

    MATCHINGGIFT3.ORGANIZATION as MATCHINGGIFT3ORGANIZATION,
    MATCHINGGIFT3.CONDITIONTYPE as MATCHINGGIFT3CONDITIONTYPE,
    MATCHINGGIFT3.AMOUNT as MATCHINGGIFT3AMOUNT,


    -- Benefits fields

    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,

    REVENUEBENEFIT3.NAME as BENEFIT3NAME,
    REVENUEBENEFIT3.QUANTITY as BENEFIT3QUANTITY,
    REVENUEBENEFIT3.TRANSACTIONUNITVALUE as BENEFIT3UNITVALUE,
    REVENUEBENEFIT3.TRANSACTIONTOTALVALUE as BENEFIT3TOTALVALUE,

    REVENUEBENEFIT4.NAME as BENEFIT4NAME,
    REVENUEBENEFIT4.QUANTITY as BENEFIT4QUANTITY,
    REVENUEBENEFIT4.TRANSACTIONUNITVALUE as BENEFIT4UNITVALUE,
    REVENUEBENEFIT4.TRANSACTIONTOTALVALUE  as BENEFIT4TOTALVALUE,

    REVENUEBENEFIT5.NAME as BENEFIT5NAME,
    REVENUEBENEFIT5.QUANTITY as BENEFIT5QUANTITY,
    REVENUEBENEFIT5.TRANSACTIONUNITVALUE as BENEFIT5UNITVALUE,
    REVENUEBENEFIT5.TRANSACTIONTOTALVALUE as BENEFIT5TOTALVALUE,


    -- Split 1 fields

    REVENUESPLIT1.APPLICATION as APPLICATION1TYPE,
    REVENUESPLIT1.DESIGNATIONNAME as DESIGNATION1NAME,
    REVENUESPLIT1.DESIGNATIONPUBLICNAME as DESIGNATION1PUBLICNAME,
    REVENUESPLIT1.AMOUNT as DESIGNATION1AMOUNT,
    REVENUESPLIT1.DESIGNATIONADMINISTRATOR as DESIGNATION1ADMINISTRATOR,
    REVENUESPLIT1.DESIGNATIONLOOKUPID as DESIGNATION1LOOKUPID,

    CAMPAIGNS1.CAMPAIGN1 as SPLIT1CAMPAIGN1NAME,
    CAMPAIGNS1.CAMPAIGN2 as SPLIT1CAMPAIGN2NAME,
    CAMPAIGNS1.CAMPAIGN3 as SPLIT1CAMPAIGN3NAME,
    CAMPAIGNS1.CAMPAIGN4 as SPLIT1CAMPAIGN4NAME,
    CAMPAIGNS1.CAMPAIGN5 as SPLIT1CAMPAIGN5NAME,
    CAMPAIGNS1.CAMPAIGN6 as SPLIT1CAMPAIGN6NAME,
    CAMPAIGNS1.CAMPAIGN7 as SPLIT1CAMPAIGN7NAME,
    CAMPAIGNS1.CAMPAIGN8 as SPLIT1CAMPAIGN8NAME,
    CAMPAIGNS1.CAMPAIGN9 as SPLIT1CAMPAIGN9NAME,
    CAMPAIGNS1.CAMPAIGN10 as SPLIT1CAMPAIGN10NAME,

    REVENUESPLIT1.MEMBERSHIPPROGRAM as MEMBERSHIP1PROGRAM,
    REVENUESPLIT1.MEMBERSHIPLEVEL as MEMBERSHIP1LEVEL,
    --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

    cast(cast(REVENUESPLIT1.MEMBERSHIPEXPIRATIONDATE as date) as datetime) as MEMBERSHIP1EXPIRATIONDATE,
    REVENUESPLIT1.PLEDGECONSTITUENT as PLEDGE1CONSTITUENT,
    REVENUESPLIT1.PLEDGEAMOUNT as PLEDGE1AMOUNT,
    REVENUESPLIT1.PLEDGEDATE as PLEDGE1DATE,
    REVENUESPLIT1.PLEDGEBALANCE as PLEDGE1BALANCE,

    -- Recognition credit amounts are intentionally outputted in base currency.

    RECOGNITIONCREDITS1.RECIPIENT1 as SPLIT1RECOGNITIONCREDIT1RECIPIENT,
    RECOGNITIONCREDITS1.AMOUNT1 as SPLIT1RECOGNITIONCREDIT1AMOUNT,
    RECOGNITIONCREDITS1.TYPE1 as SPLIT1RECOGNITIONCREDIT1TYPE,
    RECOGNITIONCREDITS1.EFFECTIVEDATE1 as SPLIT1RECOGNITIONCREDIT1EFFECTIVEDATE,

    RECOGNITIONCREDITS1.RECIPIENT2 as SPLIT1RECOGNITIONCREDIT2RECIPIENT,
    RECOGNITIONCREDITS1.AMOUNT2 as SPLIT1RECOGNITIONCREDIT2AMOUNT,
    RECOGNITIONCREDITS1.TYPE2 as SPLIT1RECOGNITIONCREDIT2TYPE,
    RECOGNITIONCREDITS1.EFFECTIVEDATE2 as SPLIT1RECOGNITIONCREDIT2EFFECTIVEDATE,

    RECOGNITIONCREDITS1.RECIPIENT3 as SPLIT1RECOGNITIONCREDIT3RECIPIENT,
    RECOGNITIONCREDITS1.AMOUNT3 as SPLIT1RECOGNITIONCREDIT3AMOUNT,
    RECOGNITIONCREDITS1.TYPE3 as SPLIT1RECOGNITIONCREDIT3TYPE,
    RECOGNITIONCREDITS1.EFFECTIVEDATE3 as SPLIT1RECOGNITIONCREDIT3EFFECTIVEDATE,

    RECOGNITIONCREDITS1.RECIPIENT4 as SPLIT1RECOGNITIONCREDIT4RECIPIENT,
    RECOGNITIONCREDITS1.AMOUNT4 as SPLIT1RECOGNITIONCREDIT4AMOUNT,
    RECOGNITIONCREDITS1.TYPE4 as SPLIT1RECOGNITIONCREDIT4TYPE,
    RECOGNITIONCREDITS1.EFFECTIVEDATE4 as SPLIT1RECOGNITIONCREDIT4EFFECTIVEDATE,

    RECOGNITIONCREDITS1.RECIPIENT5 as SPLIT1RECOGNITIONCREDIT5RECIPIENT,
    RECOGNITIONCREDITS1.AMOUNT5 as SPLIT1RECOGNITIONCREDIT5AMOUNT,
    RECOGNITIONCREDITS1.TYPE5 as SPLIT1RECOGNITIONCREDIT5TYPE,
    RECOGNITIONCREDITS1.EFFECTIVEDATE5 as SPLIT1RECOGNITIONCREDIT5EFFECTIVEDATE,

    RECOGNITIONCREDITS1.RECIPIENT6 as SPLIT1RECOGNITIONCREDIT6RECIPIENT,
    RECOGNITIONCREDITS1.AMOUNT6 as SPLIT1RECOGNITIONCREDIT6AMOUNT,
    RECOGNITIONCREDITS1.TYPE6 as SPLIT1RECOGNITIONCREDIT6TYPE,
    RECOGNITIONCREDITS1.EFFECTIVEDATE6 as SPLIT1RECOGNITIONCREDIT6EFFECTIVEDATE,

    RECOGNITIONCREDITS1.RECIPIENT7 as SPLIT1RECOGNITIONCREDIT7RECIPIENT,
    RECOGNITIONCREDITS1.AMOUNT7 as SPLIT1RECOGNITIONCREDIT7AMOUNT,
    RECOGNITIONCREDITS1.TYPE7 as SPLIT1RECOGNITIONCREDIT7TYPE,
    RECOGNITIONCREDITS1.EFFECTIVEDATE7 as SPLIT1RECOGNITIONCREDIT7EFFECTIVEDATE,

    RECOGNITIONCREDITS1.RECIPIENT8 as SPLIT1RECOGNITIONCREDIT8RECIPIENT,
    RECOGNITIONCREDITS1.AMOUNT8 as SPLIT1RECOGNITIONCREDIT8AMOUNT,
    RECOGNITIONCREDITS1.TYPE8 as SPLIT1RECOGNITIONCREDIT8TYPE,
    RECOGNITIONCREDITS1.EFFECTIVEDATE8 as SPLIT1RECOGNITIONCREDIT8EFFECTIVEDATE,

    RECOGNITIONCREDITS1.RECIPIENT9 as SPLIT1RECOGNITIONCREDIT9RECIPIENT,
    RECOGNITIONCREDITS1.AMOUNT9 as SPLIT1RECOGNITIONCREDIT9AMOUNT,
    RECOGNITIONCREDITS1.TYPE9 as SPLIT1RECOGNITIONCREDIT9TYPE,
    RECOGNITIONCREDITS1.EFFECTIVEDATE9 as SPLIT1RECOGNITIONCREDIT9EFFECTIVEDATE,

    RECOGNITIONCREDITS1.RECIPIENT10 as SPLIT1RECOGNITIONCREDIT10RECIPIENT,
    RECOGNITIONCREDITS1.AMOUNT10 as SPLIT1RECOGNITIONCREDIT10AMOUNT,
    RECOGNITIONCREDITS1.TYPE10 as SPLIT1RECOGNITIONCREDIT10TYPE,
    RECOGNITIONCREDITS1.EFFECTIVEDATE10 as SPLIT1RECOGNITIONCREDIT10EFFECTIVEDATE,


    -- Split 2 fields

    REVENUESPLIT2.APPLICATION as APPLICATION2TYPE,
    REVENUESPLIT2.DESIGNATIONNAME as DESIGNATION2NAME,
    REVENUESPLIT2.DESIGNATIONPUBLICNAME as DESIGNATION2PUBLICNAME,
    REVENUESPLIT2.AMOUNT as DESIGNATION2AMOUNT,
    REVENUESPLIT2.DESIGNATIONADMINISTRATOR as DESIGNATION2ADMINISTRATOR,
    REVENUESPLIT2.DESIGNATIONLOOKUPID as DESIGNATION2LOOKUPID,

    CAMPAIGNS2.CAMPAIGN1 as SPLIT2CAMPAIGN1NAME,
    CAMPAIGNS2.CAMPAIGN2 as SPLIT2CAMPAIGN2NAME,
    CAMPAIGNS2.CAMPAIGN3 as SPLIT2CAMPAIGN3NAME,
    CAMPAIGNS2.CAMPAIGN4 as SPLIT2CAMPAIGN4NAME,
    CAMPAIGNS2.CAMPAIGN5 as SPLIT2CAMPAIGN5NAME,
    CAMPAIGNS2.CAMPAIGN6 as SPLIT2CAMPAIGN6NAME,
    CAMPAIGNS2.CAMPAIGN7 as SPLIT2CAMPAIGN7NAME,
    CAMPAIGNS2.CAMPAIGN8 as SPLIT2CAMPAIGN8NAME,
   CAMPAIGNS2.CAMPAIGN9 as SPLIT2CAMPAIGN9NAME,
    CAMPAIGNS2.CAMPAIGN10 as SPLIT2CAMPAIGN10NAME,

    REVENUESPLIT2.MEMBERSHIPPROGRAM as MEMBERSHIP2PROGRAM,
    REVENUESPLIT2.MEMBERSHIPLEVEL as MEMBERSHIP2LEVEL,
    --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

    cast(cast(REVENUESPLIT2.MEMBERSHIPEXPIRATIONDATE as date) as datetime) as MEMBERSHIP2EXPIRATIONDATE,
    REVENUESPLIT2.PLEDGECONSTITUENT as PLEDGE2CONSTITUENT,
    REVENUESPLIT2.PLEDGEAMOUNT as PLEDGE2AMOUNT,
    REVENUESPLIT2.PLEDGEDATE as PLEDGE2DATE,
    REVENUESPLIT2.PLEDGEBALANCE as PLEDGE2BALANCE,

    -- Recognition credit amounts are intentionally outputted in base currency.

    RECOGNITIONCREDITS2.RECIPIENT1 as SPLIT2RECOGNITIONCREDIT1RECIPIENT,
    RECOGNITIONCREDITS2.AMOUNT1 as SPLIT2RECOGNITIONCREDIT1AMOUNT,
    RECOGNITIONCREDITS2.TYPE1 as SPLIT2RECOGNITIONCREDIT1TYPE,
    RECOGNITIONCREDITS2.EFFECTIVEDATE1 as SPLIT2RECOGNITIONCREDIT1EFFECTIVEDATE,

    RECOGNITIONCREDITS2.RECIPIENT2 as SPLIT2RECOGNITIONCREDIT2RECIPIENT,
    RECOGNITIONCREDITS2.AMOUNT2 as SPLIT2RECOGNITIONCREDIT2AMOUNT,
    RECOGNITIONCREDITS2.TYPE2 as SPLIT2RECOGNITIONCREDIT2TYPE,
    RECOGNITIONCREDITS2.EFFECTIVEDATE2 as SPLIT2RECOGNITIONCREDIT2EFFECTIVEDATE,

    RECOGNITIONCREDITS2.RECIPIENT3 as SPLIT2RECOGNITIONCREDIT3RECIPIENT,
    RECOGNITIONCREDITS2.AMOUNT3 as SPLIT2RECOGNITIONCREDIT3AMOUNT,
    RECOGNITIONCREDITS2.TYPE3 as SPLIT2RECOGNITIONCREDIT3TYPE,
    RECOGNITIONCREDITS2.EFFECTIVEDATE3 as SPLIT2RECOGNITIONCREDIT3EFFECTIVEDATE,

    RECOGNITIONCREDITS2.RECIPIENT4 as SPLIT2RECOGNITIONCREDIT4RECIPIENT,
    RECOGNITIONCREDITS2.AMOUNT4 as SPLIT2RECOGNITIONCREDIT4AMOUNT,
    RECOGNITIONCREDITS2.TYPE4 as SPLIT2RECOGNITIONCREDIT4TYPE,
    RECOGNITIONCREDITS2.EFFECTIVEDATE4 as SPLIT2RECOGNITIONCREDIT4EFFECTIVEDATE,

    RECOGNITIONCREDITS2.RECIPIENT5 as SPLIT2RECOGNITIONCREDIT5RECIPIENT,
    RECOGNITIONCREDITS2.AMOUNT5 as SPLIT2RECOGNITIONCREDIT5AMOUNT,
    RECOGNITIONCREDITS2.TYPE5 as SPLIT2RECOGNITIONCREDIT5TYPE,
    RECOGNITIONCREDITS2.EFFECTIVEDATE5 as SPLIT2RECOGNITIONCREDIT5EFFECTIVEDATE,

    RECOGNITIONCREDITS2.RECIPIENT6 as SPLIT2RECOGNITIONCREDIT6RECIPIENT,
    RECOGNITIONCREDITS2.AMOUNT6 as SPLIT2RECOGNITIONCREDIT6AMOUNT,
    RECOGNITIONCREDITS2.TYPE6 as SPLIT2RECOGNITIONCREDIT6TYPE,
    RECOGNITIONCREDITS2.EFFECTIVEDATE6 as SPLIT2RECOGNITIONCREDIT6EFFECTIVEDATE,

    RECOGNITIONCREDITS2.RECIPIENT7 as SPLIT2RECOGNITIONCREDIT7RECIPIENT,
    RECOGNITIONCREDITS2.AMOUNT7 as SPLIT2RECOGNITIONCREDIT7AMOUNT,
    RECOGNITIONCREDITS2.TYPE7 as SPLIT2RECOGNITIONCREDIT7TYPE,
    RECOGNITIONCREDITS2.EFFECTIVEDATE7 as SPLIT2RECOGNITIONCREDIT7EFFECTIVEDATE,

    RECOGNITIONCREDITS2.RECIPIENT8 as SPLIT2RECOGNITIONCREDIT8RECIPIENT,
    RECOGNITIONCREDITS2.AMOUNT8 as SPLIT2RECOGNITIONCREDIT8AMOUNT,
    RECOGNITIONCREDITS2.TYPE8 as SPLIT2RECOGNITIONCREDIT8TYPE,
    RECOGNITIONCREDITS2.EFFECTIVEDATE8 as SPLIT2RECOGNITIONCREDIT8EFFECTIVEDATE,

    RECOGNITIONCREDITS2.RECIPIENT9 as SPLIT2RECOGNITIONCREDIT9RECIPIENT,
    RECOGNITIONCREDITS2.AMOUNT9 as SPLIT2RECOGNITIONCREDIT9AMOUNT,
    RECOGNITIONCREDITS2.TYPE9 as SPLIT2RECOGNITIONCREDIT9TYPE,
    RECOGNITIONCREDITS2.EFFECTIVEDATE9 as SPLIT2RECOGNITIONCREDIT9EFFECTIVEDATE,

    RECOGNITIONCREDITS2.RECIPIENT10 as SPLIT2RECOGNITIONCREDIT10RECIPIENT,
    RECOGNITIONCREDITS2.AMOUNT10 as SPLIT2RECOGNITIONCREDIT10AMOUNT,
    RECOGNITIONCREDITS2.TYPE10 as SPLIT2RECOGNITIONCREDIT10TYPE,
    RECOGNITIONCREDITS2.EFFECTIVEDATE10 as SPLIT2RECOGNITIONCREDIT10EFFECTIVEDATE,


    -- Split 3 fields

    REVENUESPLIT3.APPLICATION as APPLICATION3TYPE,
    REVENUESPLIT3.DESIGNATIONNAME as DESIGNATION3NAME,
    REVENUESPLIT3.DESIGNATIONPUBLICNAME as DESIGNATION3PUBLICNAME,
    REVENUESPLIT3.AMOUNT as DESIGNATION3AMOUNT,
    REVENUESPLIT3.DESIGNATIONADMINISTRATOR as DESIGNATION3ADMINISTRATOR,
    REVENUESPLIT3.DESIGNATIONLOOKUPID as DESIGNATION3LOOKUPID,

    CAMPAIGNS3.CAMPAIGN1 as SPLIT3CAMPAIGN1NAME,
    CAMPAIGNS3.CAMPAIGN2 as SPLIT3CAMPAIGN2NAME,
    CAMPAIGNS3.CAMPAIGN3 as SPLIT3CAMPAIGN3NAME,
    CAMPAIGNS3.CAMPAIGN4 as SPLIT3CAMPAIGN4NAME,
    CAMPAIGNS3.CAMPAIGN5 as SPLIT3CAMPAIGN5NAME,
    CAMPAIGNS3.CAMPAIGN6 as SPLIT3CAMPAIGN6NAME,
    CAMPAIGNS3.CAMPAIGN7 as SPLIT3CAMPAIGN7NAME,
    CAMPAIGNS3.CAMPAIGN8 as SPLIT3CAMPAIGN8NAME,
    CAMPAIGNS3.CAMPAIGN9 as SPLIT3CAMPAIGN9NAME,
    CAMPAIGNS3.CAMPAIGN10 as SPLIT3CAMPAIGN10NAME,

    REVENUESPLIT3.MEMBERSHIPPROGRAM as MEMBERSHIP3PROGRAM,
    REVENUESPLIT3.MEMBERSHIPLEVEL as MEMBERSHIP3LEVEL,
    --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

    cast(cast(REVENUESPLIT3.MEMBERSHIPEXPIRATIONDATE as date) as datetime) as MEMBERSHIP3EXPIRATIONDATE,
    REVENUESPLIT3.PLEDGECONSTITUENT as PLEDGE3CONSTITUENT,
    REVENUESPLIT3.PLEDGEAMOUNT as PLEDGE3AMOUNT,
    REVENUESPLIT3.PLEDGEDATE as PLEDGE3DATE,
    REVENUESPLIT3.PLEDGEBALANCE as PLEDGE3BALANCE,

    -- Recognition credit amounts are intentionally outputted in base currency.

    RECOGNITIONCREDITS3.RECIPIENT1 as SPLIT3RECOGNITIONCREDIT1RECIPIENT,
    RECOGNITIONCREDITS3.AMOUNT1 as SPLIT3RECOGNITIONCREDIT1AMOUNT,
    RECOGNITIONCREDITS3.TYPE1 as SPLIT3RECOGNITIONCREDIT1TYPE,
    RECOGNITIONCREDITS3.EFFECTIVEDATE1 as SPLIT3RECOGNITIONCREDIT1EFFECTIVEDATE,

    RECOGNITIONCREDITS3.RECIPIENT2 as SPLIT3RECOGNITIONCREDIT2RECIPIENT,
    RECOGNITIONCREDITS3.AMOUNT2 as SPLIT3RECOGNITIONCREDIT2AMOUNT,
    RECOGNITIONCREDITS3.TYPE2 as SPLIT3RECOGNITIONCREDIT2TYPE,
    RECOGNITIONCREDITS3.EFFECTIVEDATE2 as SPLIT3RECOGNITIONCREDIT2EFFECTIVEDATE,

    RECOGNITIONCREDITS3.RECIPIENT3 as SPLIT3RECOGNITIONCREDIT3RECIPIENT,
    RECOGNITIONCREDITS3.AMOUNT3 as SPLIT3RECOGNITIONCREDIT3AMOUNT,
    RECOGNITIONCREDITS3.TYPE3 as SPLIT3RECOGNITIONCREDIT3TYPE,
    RECOGNITIONCREDITS3.EFFECTIVEDATE3 as SPLIT3RECOGNITIONCREDIT3EFFECTIVEDATE,

    RECOGNITIONCREDITS3.RECIPIENT4 as SPLIT3RECOGNITIONCREDIT4RECIPIENT,
    RECOGNITIONCREDITS3.AMOUNT4 as SPLIT3RECOGNITIONCREDIT4AMOUNT,
    RECOGNITIONCREDITS3.TYPE4 as SPLIT3RECOGNITIONCREDIT4TYPE,
    RECOGNITIONCREDITS3.EFFECTIVEDATE4 as SPLIT3RECOGNITIONCREDIT4EFFECTIVEDATE,

    RECOGNITIONCREDITS3.RECIPIENT5 as SPLIT3RECOGNITIONCREDIT5RECIPIENT,
    RECOGNITIONCREDITS3.AMOUNT5 as SPLIT3RECOGNITIONCREDIT5AMOUNT,
    RECOGNITIONCREDITS3.TYPE5 as SPLIT3RECOGNITIONCREDIT5TYPE,
    RECOGNITIONCREDITS3.EFFECTIVEDATE5 as SPLIT3RECOGNITIONCREDIT5EFFECTIVEDATE,

    RECOGNITIONCREDITS3.RECIPIENT6 as SPLIT3RECOGNITIONCREDIT6RECIPIENT,
    RECOGNITIONCREDITS3.AMOUNT6 as SPLIT3RECOGNITIONCREDIT6AMOUNT,
    RECOGNITIONCREDITS3.TYPE6 as SPLIT3RECOGNITIONCREDIT6TYPE,
    RECOGNITIONCREDITS3.EFFECTIVEDATE6 as SPLIT3RECOGNITIONCREDIT6EFFECTIVEDATE,

    RECOGNITIONCREDITS3.RECIPIENT7 as SPLIT3RECOGNITIONCREDIT7RECIPIENT,
    RECOGNITIONCREDITS3.AMOUNT7 as SPLIT3RECOGNITIONCREDIT7AMOUNT,
    RECOGNITIONCREDITS3.TYPE7 as SPLIT3RECOGNITIONCREDIT7TYPE,
    RECOGNITIONCREDITS3.EFFECTIVEDATE7 as SPLIT3RECOGNITIONCREDIT7EFFECTIVEDATE,

    RECOGNITIONCREDITS3.RECIPIENT8 as SPLIT3RECOGNITIONCREDIT8RECIPIENT,
    RECOGNITIONCREDITS3.AMOUNT8 as SPLIT3RECOGNITIONCREDIT8AMOUNT,
    RECOGNITIONCREDITS3.TYPE8 as SPLIT3RECOGNITIONCREDIT8TYPE,
    RECOGNITIONCREDITS3.EFFECTIVEDATE8 as SPLIT3RECOGNITIONCREDIT8EFFECTIVEDATE,

    RECOGNITIONCREDITS3.RECIPIENT9 as SPLIT3RECOGNITIONCREDIT9RECIPIENT,
    RECOGNITIONCREDITS3.AMOUNT9 as SPLIT3RECOGNITIONCREDIT9AMOUNT,
    RECOGNITIONCREDITS3.TYPE9 as SPLIT3RECOGNITIONCREDIT9TYPE,
    RECOGNITIONCREDITS3.EFFECTIVEDATE9 as SPLIT3RECOGNITIONCREDIT9EFFECTIVEDATE,

    RECOGNITIONCREDITS3.RECIPIENT10 as SPLIT3RECOGNITIONCREDIT10RECIPIENT,
    RECOGNITIONCREDITS3.AMOUNT10 as SPLIT3RECOGNITIONCREDIT10AMOUNT,
    RECOGNITIONCREDITS3.TYPE10 as SPLIT3RECOGNITIONCREDIT10TYPE,
    RECOGNITIONCREDITS3.EFFECTIVEDATE10 as SPLIT3RECOGNITIONCREDIT10EFFECTIVEDATE,


    -- Split 4 fields

    REVENUESPLIT4.APPLICATION as APPLICATION4TYPE,
    REVENUESPLIT4.DESIGNATIONNAME as DESIGNATION4NAME,
    REVENUESPLIT4.DESIGNATIONPUBLICNAME as DESIGNATION4PUBLICNAME,
    REVENUESPLIT4.AMOUNT as DESIGNATION4AMOUNT,
    REVENUESPLIT4.DESIGNATIONADMINISTRATOR as DESIGNATION4ADMINISTRATOR,
    REVENUESPLIT4.DESIGNATIONLOOKUPID as DESIGNATION4LOOKUPID,

    CAMPAIGNS4.CAMPAIGN1 as SPLIT4CAMPAIGN1NAME,
    CAMPAIGNS4.CAMPAIGN2 as SPLIT4CAMPAIGN2NAME,
    CAMPAIGNS4.CAMPAIGN3 as SPLIT4CAMPAIGN3NAME,
    CAMPAIGNS4.CAMPAIGN4 as SPLIT4CAMPAIGN4NAME,
    CAMPAIGNS4.CAMPAIGN5 as SPLIT4CAMPAIGN5NAME,
    CAMPAIGNS4.CAMPAIGN6 as SPLIT4CAMPAIGN6NAME,
    CAMPAIGNS4.CAMPAIGN7 as SPLIT4CAMPAIGN7NAME,
    CAMPAIGNS4.CAMPAIGN8 as SPLIT4CAMPAIGN8NAME,
    CAMPAIGNS4.CAMPAIGN9 as SPLIT4CAMPAIGN9NAME,
    CAMPAIGNS4.CAMPAIGN10 as SPLIT4CAMPAIGN10NAME,

    REVENUESPLIT4.MEMBERSHIPPROGRAM as MEMBERSHIP4PROGRAM,
    REVENUESPLIT4.MEMBERSHIPLEVEL as MEMBERSHIP4LEVEL,
    --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

    cast(cast(REVENUESPLIT4.MEMBERSHIPEXPIRATIONDATE as date) as datetime) as MEMBERSHIP4EXPIRATIONDATE,
    REVENUESPLIT4.PLEDGECONSTITUENT as PLEDGE4CONSTITUENT,
    REVENUESPLIT4.PLEDGEAMOUNT as PLEDGE4AMOUNT,
    REVENUESPLIT4.PLEDGEDATE as PLEDGE4DATE,
    REVENUESPLIT4.PLEDGEBALANCE as PLEDGE4BALANCE,

    -- Recognition credit amounts are intentionally outputted in base currency.

    RECOGNITIONCREDITS4.RECIPIENT1 as SPLIT4RECOGNITIONCREDIT1RECIPIENT,
    RECOGNITIONCREDITS4.AMOUNT1 as SPLIT4RECOGNITIONCREDIT1AMOUNT,
    RECOGNITIONCREDITS4.TYPE1 as SPLIT4RECOGNITIONCREDIT1TYPE,
    RECOGNITIONCREDITS4.EFFECTIVEDATE1 as SPLIT4RECOGNITIONCREDIT1EFFECTIVEDATE,

    RECOGNITIONCREDITS4.RECIPIENT2 as SPLIT4RECOGNITIONCREDIT2RECIPIENT,
    RECOGNITIONCREDITS4.AMOUNT2 as SPLIT4RECOGNITIONCREDIT2AMOUNT,
    RECOGNITIONCREDITS4.TYPE2 as SPLIT4RECOGNITIONCREDIT2TYPE,
    RECOGNITIONCREDITS4.EFFECTIVEDATE2 as SPLIT4RECOGNITIONCREDIT2EFFECTIVEDATE,

    RECOGNITIONCREDITS4.RECIPIENT3 as SPLIT4RECOGNITIONCREDIT3RECIPIENT,
    RECOGNITIONCREDITS4.AMOUNT3 as SPLIT4RECOGNITIONCREDIT3AMOUNT,
    RECOGNITIONCREDITS4.TYPE3 as SPLIT4RECOGNITIONCREDIT3TYPE,
    RECOGNITIONCREDITS4.EFFECTIVEDATE3 as SPLIT4RECOGNITIONCREDIT3EFFECTIVEDATE,

    RECOGNITIONCREDITS4.RECIPIENT4 as SPLIT4RECOGNITIONCREDIT4RECIPIENT,
    RECOGNITIONCREDITS4.AMOUNT4 as SPLIT4RECOGNITIONCREDIT4AMOUNT,
    RECOGNITIONCREDITS4.TYPE4 as SPLIT4RECOGNITIONCREDIT4TYPE,
    RECOGNITIONCREDITS4.EFFECTIVEDATE4 as SPLIT4RECOGNITIONCREDIT4EFFECTIVEDATE,

    RECOGNITIONCREDITS4.RECIPIENT5 as SPLIT4RECOGNITIONCREDIT5RECIPIENT,
    RECOGNITIONCREDITS4.AMOUNT5 as SPLIT4RECOGNITIONCREDIT5AMOUNT,
    RECOGNITIONCREDITS4.TYPE5 as SPLIT4RECOGNITIONCREDIT5TYPE,
    RECOGNITIONCREDITS4.EFFECTIVEDATE5 as SPLIT4RECOGNITIONCREDIT5EFFECTIVEDATE,

    RECOGNITIONCREDITS4.RECIPIENT6 as SPLIT4RECOGNITIONCREDIT6RECIPIENT,
    RECOGNITIONCREDITS4.AMOUNT6 as SPLIT4RECOGNITIONCREDIT6AMOUNT,
    RECOGNITIONCREDITS4.TYPE6 as SPLIT4RECOGNITIONCREDIT6TYPE,
    RECOGNITIONCREDITS4.EFFECTIVEDATE6 as SPLIT4RECOGNITIONCREDIT6EFFECTIVEDATE,

    RECOGNITIONCREDITS4.RECIPIENT7 as SPLIT4RECOGNITIONCREDIT7RECIPIENT,
    RECOGNITIONCREDITS4.AMOUNT7 as SPLIT4RECOGNITIONCREDIT7AMOUNT,
    RECOGNITIONCREDITS4.TYPE7 as SPLIT4RECOGNITIONCREDIT7TYPE,
    RECOGNITIONCREDITS4.EFFECTIVEDATE7 as SPLIT4RECOGNITIONCREDIT7EFFECTIVEDATE,

    RECOGNITIONCREDITS4.RECIPIENT8 as SPLIT4RECOGNITIONCREDIT8RECIPIENT,
    RECOGNITIONCREDITS4.AMOUNT8 as SPLIT4RECOGNITIONCREDIT8AMOUNT,
    RECOGNITIONCREDITS4.TYPE8 as SPLIT4RECOGNITIONCREDIT8TYPE,
    RECOGNITIONCREDITS4.EFFECTIVEDATE8 as SPLIT4RECOGNITIONCREDIT8EFFECTIVEDATE,

    RECOGNITIONCREDITS4.RECIPIENT9 as SPLIT4RECOGNITIONCREDIT9RECIPIENT,
    RECOGNITIONCREDITS4.AMOUNT9 as SPLIT4RECOGNITIONCREDIT9AMOUNT,
    RECOGNITIONCREDITS4.TYPE9 as SPLIT4RECOGNITIONCREDIT9TYPE,
    RECOGNITIONCREDITS4.EFFECTIVEDATE9 as SPLIT4RECOGNITIONCREDIT9EFFECTIVEDATE,

    RECOGNITIONCREDITS4.RECIPIENT10 as SPLIT4RECOGNITIONCREDIT10RECIPIENT,
    RECOGNITIONCREDITS4.AMOUNT10 as SPLIT4RECOGNITIONCREDIT10AMOUNT,
    RECOGNITIONCREDITS4.TYPE10 as SPLIT4RECOGNITIONCREDIT10TYPE,
    RECOGNITIONCREDITS4.EFFECTIVEDATE10 as SPLIT4RECOGNITIONCREDIT10EFFECTIVEDATE,


    -- Split 5 fields

    REVENUESPLIT5.APPLICATION as APPLICATION5TYPE,
    REVENUESPLIT5.DESIGNATIONNAME as DESIGNATION5NAME,
    REVENUESPLIT5.DESIGNATIONPUBLICNAME as DESIGNATION5PUBLICNAME,
    REVENUESPLIT5.AMOUNT as DESIGNATION5AMOUNT,
    REVENUESPLIT5.DESIGNATIONADMINISTRATOR as DESIGNATION5ADMINISTRATOR,
    REVENUESPLIT5.DESIGNATIONLOOKUPID as DESIGNATION5LOOKUPID,

    CAMPAIGNS5.CAMPAIGN1 as SPLIT5CAMPAIGN1NAME,
    CAMPAIGNS5.CAMPAIGN2 as SPLIT5CAMPAIGN2NAME,
    CAMPAIGNS5.CAMPAIGN3 as SPLIT5CAMPAIGN3NAME,
    CAMPAIGNS5.CAMPAIGN4 as SPLIT5CAMPAIGN4NAME,
    CAMPAIGNS5.CAMPAIGN5 as SPLIT5CAMPAIGN5NAME,
    CAMPAIGNS5.CAMPAIGN6 as SPLIT5CAMPAIGN6NAME,
    CAMPAIGNS5.CAMPAIGN7 as SPLIT5CAMPAIGN7NAME,
    CAMPAIGNS5.CAMPAIGN8 as SPLIT5CAMPAIGN8NAME,
    CAMPAIGNS5.CAMPAIGN9 as SPLIT5CAMPAIGN9NAME,
    CAMPAIGNS5.CAMPAIGN10 as SPLIT5CAMPAIGN10NAME,

    REVENUESPLIT5.MEMBERSHIPPROGRAM as MEMBERSHIP5PROGRAM,
    REVENUESPLIT5.MEMBERSHIPLEVEL as MEMBERSHIP5LEVEL,
    --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

    cast(cast(REVENUESPLIT5.MEMBERSHIPEXPIRATIONDATE as date) as datetime) as MEMBERSHIP5EXPIRATIONDATE,
    REVENUESPLIT5.PLEDGECONSTITUENT as PLEDGE5CONSTITUENT,
    REVENUESPLIT5.PLEDGEAMOUNT as PLEDGE5AMOUNT,
    REVENUESPLIT5.PLEDGEDATE as PLEDGE5DATE,
    REVENUESPLIT5.PLEDGEBALANCE as PLEDGE5BALANCE,

    -- Recognition credit amounts are intentionally outputted in base currency.

    RECOGNITIONCREDITS5.RECIPIENT1 as SPLIT5RECOGNITIONCREDIT1RECIPIENT,
    RECOGNITIONCREDITS5.AMOUNT1 as SPLIT5RECOGNITIONCREDIT1AMOUNT,
    RECOGNITIONCREDITS5.TYPE1 as SPLIT5RECOGNITIONCREDIT1TYPE,
    RECOGNITIONCREDITS5.EFFECTIVEDATE1 as SPLIT5RECOGNITIONCREDIT1EFFECTIVEDATE,

    RECOGNITIONCREDITS5.RECIPIENT2 as SPLIT5RECOGNITIONCREDIT2RECIPIENT,
    RECOGNITIONCREDITS5.AMOUNT2 as SPLIT5RECOGNITIONCREDIT2AMOUNT,
    RECOGNITIONCREDITS5.TYPE2 as SPLIT5RECOGNITIONCREDIT2TYPE,
    RECOGNITIONCREDITS5.EFFECTIVEDATE2 as SPLIT5RECOGNITIONCREDIT2EFFECTIVEDATE,

    RECOGNITIONCREDITS5.RECIPIENT3 as SPLIT5RECOGNITIONCREDIT3RECIPIENT,
    RECOGNITIONCREDITS5.AMOUNT3 as SPLIT5RECOGNITIONCREDIT3AMOUNT,
    RECOGNITIONCREDITS5.TYPE3 as SPLIT5RECOGNITIONCREDIT3TYPE,
    RECOGNITIONCREDITS5.EFFECTIVEDATE3 as SPLIT5RECOGNITIONCREDIT3EFFECTIVEDATE,

    RECOGNITIONCREDITS5.RECIPIENT4 as SPLIT5RECOGNITIONCREDIT4RECIPIENT,
    RECOGNITIONCREDITS5.AMOUNT4 as SPLIT5RECOGNITIONCREDIT4AMOUNT,
    RECOGNITIONCREDITS5.TYPE4 as SPLIT5RECOGNITIONCREDIT4TYPE,
    RECOGNITIONCREDITS5.EFFECTIVEDATE4 as SPLIT5RECOGNITIONCREDIT4EFFECTIVEDATE,

    RECOGNITIONCREDITS5.RECIPIENT5 as SPLIT5RECOGNITIONCREDIT5RECIPIENT,
    RECOGNITIONCREDITS5.AMOUNT5 as SPLIT5RECOGNITIONCREDIT5AMOUNT,
    RECOGNITIONCREDITS5.TYPE5 as SPLIT5RECOGNITIONCREDIT5TYPE,
    RECOGNITIONCREDITS5.EFFECTIVEDATE5 as SPLIT5RECOGNITIONCREDIT5EFFECTIVEDATE,

    RECOGNITIONCREDITS5.RECIPIENT6 as SPLIT5RECOGNITIONCREDIT6RECIPIENT,
    RECOGNITIONCREDITS5.AMOUNT6 as SPLIT5RECOGNITIONCREDIT6AMOUNT,
    RECOGNITIONCREDITS5.TYPE6 as SPLIT5RECOGNITIONCREDIT6TYPE,
    RECOGNITIONCREDITS5.EFFECTIVEDATE6 as SPLIT5RECOGNITIONCREDIT6EFFECTIVEDATE,

    RECOGNITIONCREDITS5.RECIPIENT7 as SPLIT5RECOGNITIONCREDIT7RECIPIENT,
    RECOGNITIONCREDITS5.AMOUNT7 as SPLIT5RECOGNITIONCREDIT7AMOUNT,
    RECOGNITIONCREDITS5.TYPE7 as SPLIT5RECOGNITIONCREDIT7TYPE,
    RECOGNITIONCREDITS5.EFFECTIVEDATE7 as SPLIT5RECOGNITIONCREDIT7EFFECTIVEDATE,

    RECOGNITIONCREDITS5.RECIPIENT8 as SPLIT5RECOGNITIONCREDIT8RECIPIENT,
    RECOGNITIONCREDITS5.AMOUNT8 as SPLIT5RECOGNITIONCREDIT8AMOUNT,
    RECOGNITIONCREDITS5.TYPE8 as SPLIT5RECOGNITIONCREDIT8TYPE,
    RECOGNITIONCREDITS5.EFFECTIVEDATE8 as SPLIT5RECOGNITIONCREDIT8EFFECTIVEDATE,

    RECOGNITIONCREDITS5.RECIPIENT9 as SPLIT5RECOGNITIONCREDIT9RECIPIENT,
    RECOGNITIONCREDITS5.AMOUNT9 as SPLIT5RECOGNITIONCREDIT9AMOUNT,
    RECOGNITIONCREDITS5.TYPE9 as SPLIT5RECOGNITIONCREDIT9TYPE,
    RECOGNITIONCREDITS5.EFFECTIVEDATE9 as SPLIT5RECOGNITIONCREDIT9EFFECTIVEDATE,

    RECOGNITIONCREDITS5.RECIPIENT10 as SPLIT5RECOGNITIONCREDIT10RECIPIENT,
    RECOGNITIONCREDITS5.AMOUNT10 as SPLIT5RECOGNITIONCREDIT10AMOUNT,
    RECOGNITIONCREDITS5.TYPE10 as SPLIT5RECOGNITIONCREDIT10TYPE,
    RECOGNITIONCREDITS5.EFFECTIVEDATE10 as SPLIT5RECOGNITIONCREDIT10EFFECTIVEDATE,

    RECOGNITIONPROGRAM.RECOGNITIONPROGRAM,
    RECOGNITIONPROGRAM.RECOGNITIONLEVEL,
    [GIK].ITEMNAME as [GIFTINKINDITEMNAME],
    [GIK].DISPOSITION as [GIFTINKINDDISPOSITION],
    [GIK].NUMBEROFUNITS as [GIFTINKINDNUMBEROFUNITS],
    [GIK].TRANSACTIONFAIRMARKETVALUE as [GIFTINKINDFAIRMARKETVALUE],
    case REVENUE_EXT.BENEFITSWAIVED when 1 then 'Yes' else 'No' end as BENEFITSWAIVED,
    REVENUEBENEFIT1.VALUEPERCENT as BENEFIT1PERCENTVALUE,
    REVENUEBENEFIT2.VALUEPERCENT as BENEFIT2PERCENTVALUE,
    REVENUEBENEFIT3.VALUEPERCENT as BENEFIT3PERCENTVALUE,
    REVENUEBENEFIT4.VALUEPERCENT as BENEFIT4PERCENTVALUE,
    REVENUEBENEFIT5.VALUEPERCENT as BENEFIT5PERCENTVALUE,

    -- General multicurrency fields

    FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
    CURRENCYSET.BASECURRENCYID,

    TRANSACTIONCURRENCY.ISO4217 as PAYMENTAMOUNTCURRENCY,
    TRANSACTIONCURRENCY.ISO4217 as RECEIPTAMOUNTCURRENCY,
    TRANSACTIONCURRENCY.ISO4217 as TOTALBENEFITAMOUNTCURRENCY,
    case when STOCKDETAIL.TRANSACTIONMEDIANPRICE is not null then TRANSACTIONCURRENCY.ISO4217 else null end as MEDIANPRICECURRENCY,
    case when [REVENUEVAT].TRANSACTIONAMOUNTTOTAX is not null then TRANSACTIONCURRENCY.ISO4217 else null end as AMOUNTTOTAXCURRENCY,
    case when [REVENUEVAT].TRANSACTIONVATAMOUNT is not null then TRANSACTIONCURRENCY.ISO4217 else null end as VATAMOUNTCURRENCY,
    case when [GIK].TRANSACTIONFAIRMARKETVALUE is not null then TRANSACTIONCURRENCY.ISO4217 else null end as GIFTINKINDFAIRMARKETVALUECURRENCY,

    -- Tribute multicurrency fields

    case when TRIBUTE1.AMOUNT is not null then BASECURRENCY.ISO4217 else null end as TRIBUTE1AMOUNTCURRENCY,
    case when TRIBUTE2.AMOUNT is not null then BASECURRENCY.ISO4217 else null end as TRIBUTE2AMOUNTCURRENCY,
    case when TRIBUTE3.AMOUNT is not null then BASECURRENCY.ISO4217 else null end as TRIBUTE3AMOUNTCURRENCY,
    case when TRIBUTE4.AMOUNT is not null then BASECURRENCY.ISO4217 else null end as TRIBUTE4AMOUNTCURRENCY,
    case when TRIBUTE5.AMOUNT is not null then BASECURRENCY.ISO4217 else null end as TRIBUTE5AMOUNTCURRENCY,

    -- Matching gift currencies

    MATCHINGGIFT1.TRANSACTIONCURRENCYID as MATCHINGGIFT1CURRENCYID,
    dbo.UFN_CURRENCY_GETISO(MATCHINGGIFT1.TRANSACTIONCURRENCYID) as MATCHINGGIFT1AMOUNTCURRENCY,
    MATCHINGGIFT2.TRANSACTIONCURRENCYID as MATCHINGGIFT2CURRENCYID,
    dbo.UFN_CURRENCY_GETISO(MATCHINGGIFT2.TRANSACTIONCURRENCYID) as MATCHINGGIFT2AMOUNTCURRENCY,
    MATCHINGGIFT3.TRANSACTIONCURRENCYID as MATCHINGGIFT3CURRENCYID,
    dbo.UFN_CURRENCY_GETISO(MATCHINGGIFT3.TRANSACTIONCURRENCYID) as MATCHINGGIFT3AMOUNTCURRENCY,

    -- Benefit currencies

    case when REVENUEBENEFIT1.TRANSACTIONUNITVALUE is not null then TRANSACTIONCURRENCY.ISO4217 else null end as BENEFIT1UNITVALUECURRENCY,
    case when REVENUEBENEFIT1.TRANSACTIONTOTALVALUE is not null then TRANSACTIONCURRENCY.ISO4217 else null end as BENEFIT1TOTALVALUECURRENCY,
    case when REVENUEBENEFIT2.TRANSACTIONUNITVALUE is not null then TRANSACTIONCURRENCY.ISO4217 else null end as BENEFIT2UNITVALUECURRENCY,
    case when REVENUEBENEFIT2.TRANSACTIONTOTALVALUE is not null then TRANSACTIONCURRENCY.ISO4217 else null end as BENEFIT2TOTALVALUECURRENCY,
    case when REVENUEBENEFIT3.TRANSACTIONUNITVALUE is not null then TRANSACTIONCURRENCY.ISO4217 else null end as BENEFIT3UNITVALUECURRENCY,
    case when REVENUEBENEFIT3.TRANSACTIONTOTALVALUE is not null then TRANSACTIONCURRENCY.ISO4217 else null end as BENEFIT3TOTALVALUECURRENCY,
    case when REVENUEBENEFIT4.TRANSACTIONUNITVALUE is not null then TRANSACTIONCURRENCY.ISO4217 else null end as BENEFIT4UNITVALUECURRENCY,
    case when REVENUEBENEFIT4.TRANSACTIONTOTALVALUE is not null then TRANSACTIONCURRENCY.ISO4217 else null end as BENEFIT4TOTALVALUECURRENCY,
    case when REVENUEBENEFIT5.TRANSACTIONUNITVALUE is not null then TRANSACTIONCURRENCY.ISO4217 else null end as BENEFIT5UNITVALUECURRENCY,
    case when REVENUEBENEFIT5.TRANSACTIONTOTALVALUE is not null then TRANSACTIONCURRENCY.ISO4217 else null end as BENEFIT5TOTALVALUECURRENCY,

    -- Designation currencies

    case when REVENUESPLIT1.AMOUNT is not null then TRANSACTIONCURRENCY.ISO4217 else null end as DESIGNATION1AMOUNTCURRENCY,
    case when REVENUESPLIT2.AMOUNT is not null then TRANSACTIONCURRENCY.ISO4217 else null end as DESIGNATION2AMOUNTCURRENCY,
    case when REVENUESPLIT3.AMOUNT is not null then TRANSACTIONCURRENCY.ISO4217 else null end as DESIGNATION3AMOUNTCURRENCY,
    case when REVENUESPLIT4.AMOUNT is not null then TRANSACTIONCURRENCY.ISO4217 else null end as DESIGNATION4AMOUNTCURRENCY,
    case when REVENUESPLIT5.AMOUNT is not null then TRANSACTIONCURRENCY.ISO4217 else null end as DESIGNATION5AMOUNTCURRENCY,

    -- Pledge currencies

    REVENUESPLIT1.PLEDGETRANSACTIONCURRENCYID as PLEDGE1CURRENCYID,
    dbo.UFN_CURRENCY_GETISO(REVENUESPLIT1.PLEDGETRANSACTIONCURRENCYID) as PLEDGE1AMOUNTCURRENCY,
    dbo.UFN_CURRENCY_GETISO(REVENUESPLIT1.PLEDGETRANSACTIONCURRENCYID) as PLEDGE1BALANCECURRENCY,
    REVENUESPLIT2.PLEDGETRANSACTIONCURRENCYID as PLEDGE2CURRENCYID,
    dbo.UFN_CURRENCY_GETISO(REVENUESPLIT2.PLEDGETRANSACTIONCURRENCYID) as PLEDGE2AMOUNTCURRENCY,
    dbo.UFN_CURRENCY_GETISO(REVENUESPLIT2.PLEDGETRANSACTIONCURRENCYID) as PLEDGE2BALANCECURRENCY,
    REVENUESPLIT3.PLEDGETRANSACTIONCURRENCYID as PLEDGE3CURRENCYID,
    dbo.UFN_CURRENCY_GETISO(REVENUESPLIT3.PLEDGETRANSACTIONCURRENCYID) as PLEDGE3AMOUNTCURRENCY,
    dbo.UFN_CURRENCY_GETISO(REVENUESPLIT3.PLEDGETRANSACTIONCURRENCYID) as PLEDGE3BALANCECURRENCY,
    REVENUESPLIT4.PLEDGETRANSACTIONCURRENCYID as PLEDGE4CURRENCYID,
    dbo.UFN_CURRENCY_GETISO(REVENUESPLIT4.PLEDGETRANSACTIONCURRENCYID) as PLEDGE4AMOUNTCURRENCY,
    dbo.UFN_CURRENCY_GETISO(REVENUESPLIT4.PLEDGETRANSACTIONCURRENCYID) as PLEDGE4BALANCECURRENCY,
    REVENUESPLIT5.PLEDGETRANSACTIONCURRENCYID as PLEDGE5CURRENCYID,
    dbo.UFN_CURRENCY_GETISO(REVENUESPLIT5.PLEDGETRANSACTIONCURRENCYID) as PLEDGE5AMOUNTCURRENCY,
    dbo.UFN_CURRENCY_GETISO(REVENUESPLIT5.PLEDGETRANSACTIONCURRENCYID) as PLEDGE5BALANCECURRENCY,

    -- Recognition credit currencies

    case when RECOGNITIONCREDITS1.AMOUNT1 is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT1AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS1.AMOUNT2 is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT2AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS1.AMOUNT3 is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT3AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS1.AMOUNT4 is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT4AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS1.AMOUNT5 is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT5AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS1.AMOUNT6 is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT6AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS1.AMOUNT7 is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT7AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS1.AMOUNT8 is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT8AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS1.AMOUNT9 is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT9AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS1.AMOUNT10 is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT10AMOUNTCURRENCY,

    case when RECOGNITIONCREDITS2.AMOUNT1 is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT1AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS2.AMOUNT2 is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT2AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS2.AMOUNT3 is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT3AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS2.AMOUNT4 is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT4AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS2.AMOUNT5 is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT5AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS2.AMOUNT6 is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT6AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS2.AMOUNT7 is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT7AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS2.AMOUNT8 is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT8AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS2.AMOUNT9 is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT9AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS2.AMOUNT10 is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT10AMOUNTCURRENCY,

    case when RECOGNITIONCREDITS3.AMOUNT1 is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT1AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS3.AMOUNT2 is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT2AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS3.AMOUNT3 is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT3AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS3.AMOUNT4 is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT4AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS3.AMOUNT5 is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT5AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS3.AMOUNT6 is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT6AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS3.AMOUNT7 is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT7AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS3.AMOUNT8 is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT8AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS3.AMOUNT9 is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT9AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS3.AMOUNT10 is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT10AMOUNTCURRENCY,

    case when RECOGNITIONCREDITS4.AMOUNT1 is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT1AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS4.AMOUNT2 is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT2AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS4.AMOUNT3 is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT3AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS4.AMOUNT4 is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT4AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS4.AMOUNT5 is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT5AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS4.AMOUNT6 is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT6AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS4.AMOUNT7 is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT7AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS4.AMOUNT8 is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT8AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS4.AMOUNT9 is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT9AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS4.AMOUNT10 is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT10AMOUNTCURRENCY,

    case when RECOGNITIONCREDITS5.AMOUNT1 is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT1AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS5.AMOUNT2 is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT2AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS5.AMOUNT3 is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT3AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS5.AMOUNT4 is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT4AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS5.AMOUNT5 is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT5AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS5.AMOUNT6 is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT6AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS5.AMOUNT7 is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT7AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS5.AMOUNT8 is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT8AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS5.AMOUNT9 is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT9AMOUNTCURRENCY,
    case when RECOGNITIONCREDITS5.AMOUNT10 is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT10AMOUNTCURRENCY,                    

    case when CONSTITUENT.ISORGANIZATION = 1 then
      (select top 1 RELATIONSHIPJOBINFO.JOBTITLE 
        from dbo.RELATIONSHIP 
        inner join dbo.RELATIONSHIPJOBINFO on RELATIONSHIP.RELATIONSHIPSETID = RELATIONSHIPJOBINFO.RELATIONSHIPSETID
        inner join dbo.CONSTITUENT CONTACT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
        where RELATIONSHIP.ISPRIMARYCONTACT = 1
          and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID)
    else
      null
    end as JOBTITLE

  from dbo.FINANCIALTRANSACTION
  inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
  inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
  inner join dbo.CURRENCY as TRANSACTIONCURRENCY on TRANSACTIONCURRENCY.ID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
  inner join dbo.CURRENCY as BASECURRENCY on BASECURRENCY.ID = CURRENCYSET.BASECURRENCYID
  inner join dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID    
  cross apply dbo.UFN_CONTACTPREFERENCES_GETFORMATS_EXT_2( 
    FINANCIALTRANSACTION.CONSTITUENTID,
    @NAMEFORMATPARAMETERID,
    @ADDRESSPROCESSINGOPTIONID,
    5,
    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) A
  left join CONSTITUENT C on C.ID = A.CONSTITUENTID
  inner join REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = FINANCIALTRANSACTION.ID
  left join dbo.STOCKDETAIL on REVENUEPAYMENTMETHOD.ID = STOCKDETAIL.ID
  left join dbo.PROPERTYDETAIL PD on PD.ID = REVENUEPAYMENTMETHOD.ID
  left join dbo.PROPERTYSUBTYPECODE PTC on PTC.ID = PD.PROPERTYSUBTYPECODEID
  left join dbo.GIFTINKINDPAYMENTMETHODDETAIL GIK on GIK.ID = REVENUEPAYMENTMETHOD.ID
  left join dbo.GIFTINKINDSUBTYPECODE GIKTC on GIKTC.ID = GIK.GIFTINKINDSUBTYPECODEID
  left join dbo.REVENUEVAT on FINANCIALTRANSACTION.ID = REVENUEVAT.ID
  left join dbo.VATTAXRATE on REVENUEVAT.VATTAXRATEID = VATTAXRATE.ID
  left join dbo.OTHERPAYMENTMETHODDETAIL on OTHERPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
  left join dbo.OTHERPAYMENTMETHODCODE on OTHERPAYMENTMETHODCODE.ID = OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID
  left join dbo.REVENUEREFERENCE on REVENUEREFERENCE.ID = FINANCIALTRANSACTION.ID

  outer apply dbo.UFN_REVENUE_GETTRIBUTEBYROWNUMBER(FINANCIALTRANSACTION.ID, 1) as TRIBUTE1
  outer apply dbo.UFN_REVENUE_GETTRIBUTEBYROWNUMBER(FINANCIALTRANSACTION.ID, 2) as TRIBUTE2
  outer apply dbo.UFN_REVENUE_GETTRIBUTEBYROWNUMBER(FINANCIALTRANSACTION.ID, 3) as TRIBUTE3
  outer apply dbo.UFN_REVENUE_GETTRIBUTEBYROWNUMBER(FINANCIALTRANSACTION.ID, 4) as TRIBUTE4
  outer apply dbo.UFN_REVENUE_GETTRIBUTEBYROWNUMBER(FINANCIALTRANSACTION.ID, 5) as TRIBUTE5

  outer apply dbo.UFN_REVENUE_GETMATCHINGGIFTBYROWNUMBER(FINANCIALTRANSACTION.ID, 1) as MATCHINGGIFT1
  outer apply dbo.UFN_REVENUE_GETMATCHINGGIFTBYROWNUMBER(FINANCIALTRANSACTION.ID, 2) as MATCHINGGIFT2
  outer apply dbo.UFN_REVENUE_GETMATCHINGGIFTBYROWNUMBER(FINANCIALTRANSACTION.ID, 3) as MATCHINGGIFT3

  outer apply dbo.UFN_REVENUE_GETSPLITBYROWNUMBER(FINANCIALTRANSACTION.ID, 1) as REVENUESPLIT1
  outer apply dbo.UFN_REVENUE_GETSPLITBYROWNUMBER(FINANCIALTRANSACTION.ID, 2) as REVENUESPLIT2
  outer apply dbo.UFN_REVENUE_GETSPLITBYROWNUMBER(FINANCIALTRANSACTION.ID, 3) as REVENUESPLIT3
  outer apply dbo.UFN_REVENUE_GETSPLITBYROWNUMBER(FINANCIALTRANSACTION.ID, 4) as REVENUESPLIT4
  outer apply dbo.UFN_REVENUE_GETSPLITBYROWNUMBER(FINANCIALTRANSACTION.ID, 5) as REVENUESPLIT5

  outer apply dbo.UFN_REVENUESPLIT_GETTOP10RECOGNITIONCREDITSINONEROW(REVENUESPLIT1.ID) as RECOGNITIONCREDITS1
  outer apply dbo.UFN_REVENUESPLIT_GETTOP10RECOGNITIONCREDITSINONEROW(REVENUESPLIT2.ID) as RECOGNITIONCREDITS2
  outer apply dbo.UFN_REVENUESPLIT_GETTOP10RECOGNITIONCREDITSINONEROW(REVENUESPLIT3.ID) as RECOGNITIONCREDITS3
  outer apply dbo.UFN_REVENUESPLIT_GETTOP10RECOGNITIONCREDITSINONEROW(REVENUESPLIT4.ID) as RECOGNITIONCREDITS4
  outer apply dbo.UFN_REVENUESPLIT_GETTOP10RECOGNITIONCREDITSINONEROW(REVENUESPLIT5.ID) as RECOGNITIONCREDITS5

  outer apply dbo.UFN_REVENUESPLIT_GETTOP10CAMPAIGNSINONEROW(REVENUESPLIT1.ID) as CAMPAIGNS1
  outer apply dbo.UFN_REVENUESPLIT_GETTOP10CAMPAIGNSINONEROW(REVENUESPLIT2.ID) as CAMPAIGNS2
  outer apply dbo.UFN_REVENUESPLIT_GETTOP10CAMPAIGNSINONEROW(REVENUESPLIT3.ID) as CAMPAIGNS3
  outer apply dbo.UFN_REVENUESPLIT_GETTOP10CAMPAIGNSINONEROW(REVENUESPLIT4.ID) as CAMPAIGNS4
  outer apply dbo.UFN_REVENUESPLIT_GETTOP10CAMPAIGNSINONEROW(REVENUESPLIT5.ID) as CAMPAIGNS5

  outer apply dbo.UFN_CONSTITUENT_GETMOSTRECENTLYCHANGEDRECOGNITIONPROGRAM(FINANCIALTRANSACTION.CONSTITUENTID) as RECOGNITIONPROGRAM

  outer apply dbo.UFN_REVENUE_GETREVENUEBENEFITS_BYROWNUMBER(FINANCIALTRANSACTION.ID, 1) as REVENUEBENEFIT1
  outer apply dbo.UFN_REVENUE_GETREVENUEBENEFITS_BYROWNUMBER(FINANCIALTRANSACTION.ID, 2) as REVENUEBENEFIT2
  outer apply dbo.UFN_REVENUE_GETREVENUEBENEFITS_BYROWNUMBER(FINANCIALTRANSACTION.ID, 3) as REVENUEBENEFIT3
  outer apply dbo.UFN_REVENUE_GETREVENUEBENEFITS_BYROWNUMBER(FINANCIALTRANSACTION.ID, 4) as REVENUEBENEFIT4
  outer apply dbo.UFN_REVENUE_GETREVENUEBENEFITS_BYROWNUMBER(FINANCIALTRANSACTION.ID, 5) as REVENUEBENEFIT5

  where FINANCIALTRANSACTION.TYPECODE in (0, 4, 7)
)