UFN_QUERY_PLEDGEREMINDEROUTPUTEXTENDEDDETAILS2

CREATE function dbo.[UFN_QUERY_PLEDGEREMINDEROUTPUTEXTENDEDDETAILS2]
( 
  @REVENUEID uniqueidentifier,
  @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 (
  select
    CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
    FINANCIALTRANSACTION.ID as REVENUEID,
    FINANCIALTRANSACTION.TRANSACTIONAMOUNT as TOTALPLEDGEAMOUNT,
    case REVENUE_EXT.GIVENANONYMOUSLY when 1 then 'Yes' else 'No' end as GIVENANONYMOUSLY,
    cast(FINANCIALTRANSACTION.DATE as datetime) as PLEDGEDATE,
    case when FINANCIALTRANSACTION.TYPECODE= 2 then null else dbo.UFN_PLEDGE_GETBALANCE(FINANCIALTRANSACTION.ID) end as PLEDGEBALANCE,
    case when FINANCIALTRANSACTION.TYPECODE= 2 then coalesce((select sum(AMOUNT) from dbo.RECURRINGGIFTACTIVITY where SOURCEREVENUEID = FINANCIALTRANSACTION.ID and TYPECODE = 0), 0) else dbo.UFN_PLEDGE_GETAMOUNTPAID(FINANCIALTRANSACTION.ID) end as AMOUNTPAID,
    APPEAL.NAME as APPEALNAME,
    LASTPAYMENT.DATE as LASTPAYMENTDATE,
    LASTPAYMENT.AMOUNT as LASTPAYMENTAMOUNT,
    V4.*,
    FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID as REVENUELOOKUPID,

    FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
    CURRENCYSET.BASECURRENCYID,
    CURRENCY.ISO4217 as TOTALPLEDGEAMOUNTCURRENCY,
    CURRENCY.ISO4217 as PLEDGEBALANCECURRENCY,
    CURRENCY.ISO4217 as AMOUNTPAIDCURRENCY,
    case when LASTPAYMENT.AMOUNT is not null then CURRENCY.ISO4217 else null end as LASTPAYMENTAMOUNTCURRENCY,
    case when DESIGNATION1AMOUNT is not null then CURRENCY.ISO4217 else null end as DESIGNATION1AMOUNTCURRENCY,
    case when DESIGNATION2AMOUNT is not null then CURRENCY.ISO4217 else null end as DESIGNATION2AMOUNTCURRENCY,
    case when DESIGNATION3AMOUNT is not null then CURRENCY.ISO4217 else null end as DESIGNATION3AMOUNTCURRENCY,
    case when DESIGNATION4AMOUNT is not null then CURRENCY.ISO4217 else null end as DESIGNATION4AMOUNTCURRENCY,
    case when DESIGNATION5AMOUNT is not null then CURRENCY.ISO4217 else null end as DESIGNATION5AMOUNTCURRENCY,

    case when SPLIT1RECOGNITIONCREDIT1AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT1AMOUNTCURRENCY,
    case when SPLIT1RECOGNITIONCREDIT2AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT2AMOUNTCURRENCY,
    case when SPLIT1RECOGNITIONCREDIT3AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT3AMOUNTCURRENCY,
    case when SPLIT1RECOGNITIONCREDIT4AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT4AMOUNTCURRENCY,
    case when SPLIT1RECOGNITIONCREDIT5AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT5AMOUNTCURRENCY,
    case when SPLIT1RECOGNITIONCREDIT6AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT6AMOUNTCURRENCY,
    case when SPLIT1RECOGNITIONCREDIT7AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT7AMOUNTCURRENCY,
    case when SPLIT1RECOGNITIONCREDIT8AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT8AMOUNTCURRENCY,
    case when SPLIT1RECOGNITIONCREDIT9AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT9AMOUNTCURRENCY,
    case when SPLIT1RECOGNITIONCREDIT10AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT1RECOGNITIONCREDIT10AMOUNTCURRENCY,

    case when SPLIT2RECOGNITIONCREDIT1AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT1AMOUNTCURRENCY,
    case when SPLIT2RECOGNITIONCREDIT2AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT2AMOUNTCURRENCY,
    case when SPLIT2RECOGNITIONCREDIT3AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT3AMOUNTCURRENCY,
    case when SPLIT2RECOGNITIONCREDIT4AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT4AMOUNTCURRENCY,
    case when SPLIT2RECOGNITIONCREDIT5AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT5AMOUNTCURRENCY,
    case when SPLIT2RECOGNITIONCREDIT6AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT6AMOUNTCURRENCY,
    case when SPLIT2RECOGNITIONCREDIT7AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT7AMOUNTCURRENCY,
    case when SPLIT2RECOGNITIONCREDIT8AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT8AMOUNTCURRENCY,
    case when SPLIT2RECOGNITIONCREDIT9AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT9AMOUNTCURRENCY,
    case when SPLIT2RECOGNITIONCREDIT10AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT2RECOGNITIONCREDIT10AMOUNTCURRENCY,

    case when SPLIT3RECOGNITIONCREDIT1AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT1AMOUNTCURRENCY,
    case when SPLIT3RECOGNITIONCREDIT2AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT2AMOUNTCURRENCY,
    case when SPLIT3RECOGNITIONCREDIT3AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT3AMOUNTCURRENCY,
    case when SPLIT3RECOGNITIONCREDIT4AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT4AMOUNTCURRENCY,
    case when SPLIT3RECOGNITIONCREDIT5AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT5AMOUNTCURRENCY,
    case when SPLIT3RECOGNITIONCREDIT6AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT6AMOUNTCURRENCY,
    case when SPLIT3RECOGNITIONCREDIT7AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT7AMOUNTCURRENCY,
    case when SPLIT3RECOGNITIONCREDIT8AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT8AMOUNTCURRENCY,
    case when SPLIT3RECOGNITIONCREDIT9AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT9AMOUNTCURRENCY,
    case when SPLIT3RECOGNITIONCREDIT10AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT3RECOGNITIONCREDIT10AMOUNTCURRENCY,

    case when SPLIT4RECOGNITIONCREDIT1AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT1AMOUNTCURRENCY,
    case when SPLIT4RECOGNITIONCREDIT2AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT2AMOUNTCURRENCY,
    case when SPLIT4RECOGNITIONCREDIT3AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT3AMOUNTCURRENCY,
    case when SPLIT4RECOGNITIONCREDIT4AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT4AMOUNTCURRENCY,
    case when SPLIT4RECOGNITIONCREDIT5AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT5AMOUNTCURRENCY,
    case when SPLIT4RECOGNITIONCREDIT6AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT6AMOUNTCURRENCY,
    case when SPLIT4RECOGNITIONCREDIT7AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT7AMOUNTCURRENCY,
    case when SPLIT4RECOGNITIONCREDIT8AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT8AMOUNTCURRENCY,
    case when SPLIT4RECOGNITIONCREDIT9AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT9AMOUNTCURRENCY,
    case when SPLIT4RECOGNITIONCREDIT10AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT4RECOGNITIONCREDIT10AMOUNTCURRENCY,

    case when SPLIT5RECOGNITIONCREDIT1AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT1AMOUNTCURRENCY,
    case when SPLIT5RECOGNITIONCREDIT2AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT2AMOUNTCURRENCY,
    case when SPLIT5RECOGNITIONCREDIT3AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT3AMOUNTCURRENCY,
    case when SPLIT5RECOGNITIONCREDIT4AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT4AMOUNTCURRENCY,
    case when SPLIT5RECOGNITIONCREDIT5AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT5AMOUNTCURRENCY,
    case when SPLIT5RECOGNITIONCREDIT6AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT6AMOUNTCURRENCY,
    case when SPLIT5RECOGNITIONCREDIT7AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT7AMOUNTCURRENCY,
    case when SPLIT5RECOGNITIONCREDIT8AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT8AMOUNTCURRENCY,
    case when SPLIT5RECOGNITIONCREDIT9AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT9AMOUNTCURRENCY,
    case when SPLIT5RECOGNITIONCREDIT10AMOUNT is not null then BASECURRENCY.ISO4217 else null end as SPLIT5RECOGNITIONCREDIT10AMOUNTCURRENCY
  from (
    select
      max(case ROWNUM when 1 then NAME else null end) as DESIGNATION1NAME,
      max(case ROWNUM when 1 then PUBLICNAME else null end) as DESIGNATION1PUBLICNAME,
      max(case ROWNUM when 1 then AMOUNT else 0 end) as DESIGNATION1AMOUNT,
      max(case ROWNUM when 1 then DESIGNATIONLOOKUPID else null end) as DESIGNATION1LOOKUPID,
      max(case ROWNUM when 1 then case ROWNUMBER when 1 then RECIPIENT else null end else null end) as SPLIT1RECOGNITIONCREDIT1RECIPIENT,
      max(case ROWNUM when 1 then case ROWNUMBER when 1 then RECAMOUNT else 0 end else 0 end) as SPLIT1RECOGNITIONCREDIT1AMOUNT,
      max(case ROWNUM when 1 then case ROWNUMBER when 1 then TYPE else null end else null end) as SPLIT1RECOGNITIONCREDIT1TYPE,
      max(case ROWNUM when 1 then case ROWNUMBER when 1 then EFFECTIVEDATE else null end else null end) as SPLIT1RECOGNITIONCREDIT1EFFECTIVEDATE,
      max(case ROWNUM when 1 then case ROWNUMBER when 2 then RECIPIENT else null end else null end) as SPLIT1RECOGNITIONCREDIT2RECIPIENT,
      max(case ROWNUM when 1 then case ROWNUMBER when 2 then RECAMOUNT else 0 end else 0 end) as SPLIT1RECOGNITIONCREDIT2AMOUNT,
      max(case ROWNUM when 1 then case ROWNUMBER when 2 then TYPE else null end else null end) as SPLIT1RECOGNITIONCREDIT2TYPE,
      max(case ROWNUM when 1 then case ROWNUMBER when 2 then EFFECTIVEDATE else null end else null end) as SPLIT1RECOGNITIONCREDIT2EFFECTIVEDATE,
      max(case ROWNUM when 1 then case ROWNUMBER when 3 then RECIPIENT else null end else null end) as SPLIT1RECOGNITIONCREDIT3RECIPIENT,
      max(case ROWNUM when 1 then case ROWNUMBER when 3 then RECAMOUNT else 0 end else 0 end) as SPLIT1RECOGNITIONCREDIT3AMOUNT,
      max(case ROWNUM when 1 then case ROWNUMBER when 3 then TYPE else null end else null end) as SPLIT1RECOGNITIONCREDIT3TYPE,
      max(case ROWNUM when 1 then case ROWNUMBER when 3 then EFFECTIVEDATE else null end else null end) as SPLIT1RECOGNITIONCREDIT3EFFECTIVEDATE,
      max(case ROWNUM when 1 then case ROWNUMBER when 4 then RECIPIENT else null end else null end) as SPLIT1RECOGNITIONCREDIT4RECIPIENT,
      max(case ROWNUM when 1 then case ROWNUMBER when 4 then RECAMOUNT else 0 end else 0 end) as SPLIT1RECOGNITIONCREDIT4AMOUNT,
      max(case ROWNUM when 1 then case ROWNUMBER when 4 then TYPE else null end else null end) as SPLIT1RECOGNITIONCREDIT4TYPE,
      max(case ROWNUM when 1 then case ROWNUMBER when 4 then EFFECTIVEDATE else null end else null end) as SPLIT1RECOGNITIONCREDIT4EFFECTIVEDATE,
      max(case ROWNUM when 1 then case ROWNUMBER when 5 then RECIPIENT else null end else null end) as SPLIT1RECOGNITIONCREDIT5RECIPIENT,
      max(case ROWNUM when 1 then case ROWNUMBER when 5 then RECAMOUNT else 0 end else 0 end) as SPLIT1RECOGNITIONCREDIT5AMOUNT,
      max(case ROWNUM when 1 then case ROWNUMBER when 5 then TYPE else null end else null end) as SPLIT1RECOGNITIONCREDIT5TYPE,
      max(case ROWNUM when 1 then case ROWNUMBER when 5 then EFFECTIVEDATE else null end else null end) as SPLIT1RECOGNITIONCREDIT5EFFECTIVEDATE,
      max(case ROWNUM when 1 then case ROWNUMBER when 6 then RECIPIENT else null end else null end) as SPLIT1RECOGNITIONCREDIT6RECIPIENT,
      max(case ROWNUM when 1 then case ROWNUMBER when 6 then RECAMOUNT else 0 end else 0 end) as SPLIT1RECOGNITIONCREDIT6AMOUNT,
      max(case ROWNUM when 1 then case ROWNUMBER when 6 then TYPE else null end else null end) as SPLIT1RECOGNITIONCREDIT6TYPE,
      max(case ROWNUM when 1 then case ROWNUMBER when 6 then EFFECTIVEDATE else null end else null end) as SPLIT1RECOGNITIONCREDIT6EFFECTIVEDATE,
      max(case ROWNUM when 1 then case ROWNUMBER when 7 then RECIPIENT else null end else null end) as SPLIT1RECOGNITIONCREDIT7RECIPIENT,
      max(case ROWNUM when 1 then case ROWNUMBER when 7 then RECAMOUNT else 0 end else 0 end) as SPLIT1RECOGNITIONCREDIT7AMOUNT,
      max(case ROWNUM when 1 then case ROWNUMBER when 7 then TYPE else null end else null end) as SPLIT1RECOGNITIONCREDIT7TYPE,
      max(case ROWNUM when 1 then case ROWNUMBER when 7 then EFFECTIVEDATE else null end else null end) as SPLIT1RECOGNITIONCREDIT7EFFECTIVEDATE,
      max(case ROWNUM when 1 then case ROWNUMBER when 8 then RECIPIENT else null end else null end) as SPLIT1RECOGNITIONCREDIT8RECIPIENT,
      max(case ROWNUM when 1 then case ROWNUMBER when 8 then RECAMOUNT else 0 end else 0 end) as SPLIT1RECOGNITIONCREDIT8AMOUNT,
      max(case ROWNUM when 1 then case ROWNUMBER when 8 then TYPE else null end else null end) as SPLIT1RECOGNITIONCREDIT8TYPE,
      max(case ROWNUM when 1 then case ROWNUMBER when 8 then EFFECTIVEDATE else null end else null end) as SPLIT1RECOGNITIONCREDIT8EFFECTIVEDATE,
      max(case ROWNUM when 1 then case ROWNUMBER when 9 then RECIPIENT else null end else null end) as SPLIT1RECOGNITIONCREDIT9RECIPIENT,
      max(case ROWNUM when 1 then case ROWNUMBER when 9 then RECAMOUNT else 0 end else 0 end) as SPLIT1RECOGNITIONCREDIT9AMOUNT,
   max(case ROWNUM when 1 then case ROWNUMBER when 9 then TYPE else null end else null end) as SPLIT1RECOGNITIONCREDIT9TYPE,
      max(case ROWNUM when 1 then case ROWNUMBER when 9 then EFFECTIVEDATE else null end else null end) as SPLIT1RECOGNITIONCREDIT9EFFECTIVEDATE,
      max(case ROWNUM when 1 then case ROWNUMBER when 10 then RECIPIENT else null end else null end) as SPLIT1RECOGNITIONCREDIT10RECIPIENT,
      max(case ROWNUM when 1 then case ROWNUMBER when 10 then RECAMOUNT else 0 end else 0 end) as SPLIT1RECOGNITIONCREDIT10AMOUNT,
      max(case ROWNUM when 1 then case ROWNUMBER when 10 then TYPE else null end else null end) as SPLIT1RECOGNITIONCREDIT10TYPE,
      max(case ROWNUM when 1 then case ROWNUMBER when 10 then EFFECTIVEDATE else null end else null end) as SPLIT1RECOGNITIONCREDIT10EFFECTIVEDATE,
      max(case ROWNUM when 2 then NAME else null end) as DESIGNATION2NAME,
      max(case ROWNUM when 2 then PUBLICNAME else null end) as DESIGNATION2PUBLICNAME,
      max(case ROWNUM when 2 then AMOUNT else 0 end) as DESIGNATION2AMOUNT,
      max(case ROWNUM when 2 then DESIGNATIONLOOKUPID else null end) as DESIGNATION2LOOKUPID,
      max(case ROWNUM when 2 then case ROWNUMBER when 1 then RECIPIENT else null end else null end) as SPLIT2RECOGNITIONCREDIT1RECIPIENT,
      max(case ROWNUM when 2 then case ROWNUMBER when 1 then RECAMOUNT else 0 end else 0 end) as SPLIT2RECOGNITIONCREDIT1AMOUNT,
      max(case ROWNUM when 2 then case ROWNUMBER when 1 then TYPE else null end else null end) as SPLIT2RECOGNITIONCREDIT1TYPE,
      max(case ROWNUM when 2 then case ROWNUMBER when 1 then EFFECTIVEDATE else null end else null end) as SPLIT2RECOGNITIONCREDIT1EFFECTIVEDATE,
      max(case ROWNUM when 2 then case ROWNUMBER when 2 then RECIPIENT else null end else null end) as SPLIT2RECOGNITIONCREDIT2RECIPIENT,
      max(case ROWNUM when 2 then case ROWNUMBER when 2 then RECAMOUNT else 0 end else 0 end) as SPLIT2RECOGNITIONCREDIT2AMOUNT,
      max(case ROWNUM when 2 then case ROWNUMBER when 2 then TYPE else null end else null end) as SPLIT2RECOGNITIONCREDIT2TYPE,
      max(case ROWNUM when 2 then case ROWNUMBER when 2 then EFFECTIVEDATE else null end else null end) as SPLIT2RECOGNITIONCREDIT2EFFECTIVEDATE,
      max(case ROWNUM when 2 then case ROWNUMBER when 3 then RECIPIENT else null end else null end) as SPLIT2RECOGNITIONCREDIT3RECIPIENT,
      max(case ROWNUM when 2 then case ROWNUMBER when 3 then RECAMOUNT else 0 end else 0 end) as SPLIT2RECOGNITIONCREDIT3AMOUNT,
      max(case ROWNUM when 2 then case ROWNUMBER when 3 then TYPE else null end else null end) as SPLIT2RECOGNITIONCREDIT3TYPE,
      max(case ROWNUM when 2 then case ROWNUMBER when 3 then EFFECTIVEDATE else null end else null end) as SPLIT2RECOGNITIONCREDIT3EFFECTIVEDATE,
      max(case ROWNUM when 2 then case ROWNUMBER when 4 then RECIPIENT else null end else null end) as SPLIT2RECOGNITIONCREDIT4RECIPIENT,
      max(case ROWNUM when 2 then case ROWNUMBER when 4 then RECAMOUNT else 0 end else 0 end) as SPLIT2RECOGNITIONCREDIT4AMOUNT,
      max(case ROWNUM when 2 then case ROWNUMBER when 4 then TYPE else null end else null end) as SPLIT2RECOGNITIONCREDIT4TYPE,
      max(case ROWNUM when 2 then case ROWNUMBER when 4 then EFFECTIVEDATE else null end else null end) as SPLIT2RECOGNITIONCREDIT4EFFECTIVEDATE,
      max(case ROWNUM when 2 then case ROWNUMBER when 5 then RECIPIENT else null end else null end) as SPLIT2RECOGNITIONCREDIT5RECIPIENT,
      max(case ROWNUM when 2 then case ROWNUMBER when 5 then RECAMOUNT else 0 end else 0 end) as SPLIT2RECOGNITIONCREDIT5AMOUNT,
      max(case ROWNUM when 2 then case ROWNUMBER when 5 then TYPE else null end else null end) as SPLIT2RECOGNITIONCREDIT5TYPE,
      max(case ROWNUM when 2 then case ROWNUMBER when 5 then EFFECTIVEDATE else null end else null end) as SPLIT2RECOGNITIONCREDIT5EFFECTIVEDATE,
      max(case ROWNUM when 2 then case ROWNUMBER when 6 then RECIPIENT else null end else null end) as SPLIT2RECOGNITIONCREDIT6RECIPIENT,
      max(case ROWNUM when 2 then case ROWNUMBER when 6 then RECAMOUNT else 0 end else 0 end) as SPLIT2RECOGNITIONCREDIT6AMOUNT,
      max(case ROWNUM when 2 then case ROWNUMBER when 6 then TYPE else null end else null end) as SPLIT2RECOGNITIONCREDIT6TYPE,
      max(case ROWNUM when 2 then case ROWNUMBER when 6 then EFFECTIVEDATE else null end else null end) as SPLIT2RECOGNITIONCREDIT6EFFECTIVEDATE,
      max(case ROWNUM when 2 then case ROWNUMBER when 7 then RECIPIENT else null end else null end) as SPLIT2RECOGNITIONCREDIT7RECIPIENT,
      max(case ROWNUM when 2 then case ROWNUMBER when 7 then RECAMOUNT else 0 end else 0 end) as SPLIT2RECOGNITIONCREDIT7AMOUNT,
      max(case ROWNUM when 2 then case ROWNUMBER when 7 then TYPE else null end else null end) as SPLIT2RECOGNITIONCREDIT7TYPE,
      max(case ROWNUM when 2 then case ROWNUMBER when 7 then EFFECTIVEDATE else null end else null end) as SPLIT2RECOGNITIONCREDIT7EFFECTIVEDATE,
      max(case ROWNUM when 2 then case ROWNUMBER when 8 then RECIPIENT else null end else null end) as SPLIT2RECOGNITIONCREDIT8RECIPIENT,
      max(case ROWNUM when 2 then case ROWNUMBER when 8 then RECAMOUNT else 0 end else 0 end) as SPLIT2RECOGNITIONCREDIT8AMOUNT,
      max(case ROWNUM when 2 then case ROWNUMBER when 8 then TYPE else null end else null end) as SPLIT2RECOGNITIONCREDIT8TYPE,
      max(case ROWNUM when 2 then case ROWNUMBER when 8 then EFFECTIVEDATE else null end else null end) as SPLIT2RECOGNITIONCREDIT8EFFECTIVEDATE,
      max(case ROWNUM when 2 then case ROWNUMBER when 9 then RECIPIENT else null end else null end) as SPLIT2RECOGNITIONCREDIT9RECIPIENT,
      max(case ROWNUM when 2 then case ROWNUMBER when 9 then RECAMOUNT else 0 end else 0 end) as SPLIT2RECOGNITIONCREDIT9AMOUNT,
      max(case ROWNUM when 2 then case ROWNUMBER when 9 then TYPE else null end else null end) as SPLIT2RECOGNITIONCREDIT9TYPE,
      max(case ROWNUM when 2 then case ROWNUMBER when 9 then EFFECTIVEDATE else null end else null end) as SPLIT2RECOGNITIONCREDIT9EFFECTIVEDATE,
      max(case ROWNUM when 2 then case ROWNUMBER when 10 then RECIPIENT else null end else null end) as SPLIT2RECOGNITIONCREDIT10RECIPIENT,
      max(case ROWNUM when 2 then case ROWNUMBER when 10 then RECAMOUNT else 0 end else 0 end) as SPLIT2RECOGNITIONCREDIT10AMOUNT,
      max(case ROWNUM when 2 then case ROWNUMBER when 10 then TYPE else null end else null end) as SPLIT2RECOGNITIONCREDIT10TYPE,
      max(case ROWNUM when 2 then case ROWNUMBER when 10 then EFFECTIVEDATE else null end else null end) as SPLIT2RECOGNITIONCREDIT10EFFECTIVEDATE,
      max(case ROWNUM when 3 then NAME else null end) as DESIGNATION3NAME,
      max(case ROWNUM when 3 then PUBLICNAME else null end) as DESIGNATION3PUBLICNAME,
      max(case ROWNUM when 3 then AMOUNT else 0 end) as DESIGNATION3AMOUNT,
      max(case ROWNUM when 3 then DESIGNATIONLOOKUPID else null end) as DESIGNATION3LOOKUPID,
      max(case ROWNUM when 3 then case ROWNUMBER when 1 then RECIPIENT else null end else null end) as SPLIT3RECOGNITIONCREDIT1RECIPIENT,
      max(case ROWNUM when 3 then case ROWNUMBER when 1 then RECAMOUNT else 0 end else 0 end) as SPLIT3RECOGNITIONCREDIT1AMOUNT,
      max(case ROWNUM when 3 then case ROWNUMBER when 1 then TYPE else null end else null end) as SPLIT3RECOGNITIONCREDIT1TYPE,
      max(case ROWNUM when 3 then case ROWNUMBER when 1 then EFFECTIVEDATE else null end else null end) as SPLIT3RECOGNITIONCREDIT1EFFECTIVEDATE,
      max(case ROWNUM when 3 then case ROWNUMBER when 2 then RECIPIENT else null end else null end) as SPLIT3RECOGNITIONCREDIT2RECIPIENT,
      max(case ROWNUM when 3 then case ROWNUMBER when 2 then RECAMOUNT else 0 end else 0 end) as SPLIT3RECOGNITIONCREDIT2AMOUNT,
      max(case ROWNUM when 3 then case ROWNUMBER when 2 then TYPE else null end else null end) as SPLIT3RECOGNITIONCREDIT2TYPE,
      max(case ROWNUM when 3 then case ROWNUMBER when 2 then EFFECTIVEDATE else null end else null end) as SPLIT3RECOGNITIONCREDIT2EFFECTIVEDATE,
      max(case ROWNUM when 3 then case ROWNUMBER when 3 then RECIPIENT else null end else null end) as SPLIT3RECOGNITIONCREDIT3RECIPIENT,
      max(case ROWNUM when 3 then case ROWNUMBER when 3 then RECAMOUNT else 0 end else 0 end) as SPLIT3RECOGNITIONCREDIT3AMOUNT,
      max(case ROWNUM when 3 then case ROWNUMBER when 3 then TYPE else null end else null end) as SPLIT3RECOGNITIONCREDIT3TYPE,
      max(case ROWNUM when 3 then case ROWNUMBER when 3 then EFFECTIVEDATE else null end else null end) as SPLIT3RECOGNITIONCREDIT3EFFECTIVEDATE,
      max(case ROWNUM when 3 then case ROWNUMBER when 4 then RECIPIENT else null end else null end) as SPLIT3RECOGNITIONCREDIT4RECIPIENT,
      max(case ROWNUM when 3 then case ROWNUMBER when 4 then RECAMOUNT else 0 end else 0 end) as SPLIT3RECOGNITIONCREDIT4AMOUNT,
      max(case ROWNUM when 3 then case ROWNUMBER when 4 then TYPE else null end else null end) as SPLIT3RECOGNITIONCREDIT4TYPE,
      max(case ROWNUM when 3 then case ROWNUMBER when 4 then EFFECTIVEDATE else null end else null end) as SPLIT3RECOGNITIONCREDIT4EFFECTIVEDATE,
      max(case ROWNUM when 3 then case ROWNUMBER when 5 then RECIPIENT else null end else null end) as SPLIT3RECOGNITIONCREDIT5RECIPIENT,
      max(case ROWNUM when 3 then case ROWNUMBER when 5 then RECAMOUNT else 0 end else 0 end) as SPLIT3RECOGNITIONCREDIT5AMOUNT,
      max(case ROWNUM when 3 then case ROWNUMBER when 5 then TYPE else null end else null end) as SPLIT3RECOGNITIONCREDIT5TYPE,
      max(case ROWNUM when 3 then case ROWNUMBER when 5 then EFFECTIVEDATE else null end else null end) as SPLIT3RECOGNITIONCREDIT5EFFECTIVEDATE,
      max(case ROWNUM when 3 then case ROWNUMBER when 6 then RECIPIENT else null end else null end) as SPLIT3RECOGNITIONCREDIT6RECIPIENT,
      max(case ROWNUM when 3 then case ROWNUMBER when 6 then RECAMOUNT else 0 end else 0 end) as SPLIT3RECOGNITIONCREDIT6AMOUNT,
      max(case ROWNUM when 3 then case ROWNUMBER when 6 then TYPE else null end else null end) as SPLIT3RECOGNITIONCREDIT6TYPE,
      max(case ROWNUM when 3 then case ROWNUMBER when 6 then EFFECTIVEDATE else null end else null end) as SPLIT3RECOGNITIONCREDIT6EFFECTIVEDATE,
      max(case ROWNUM when 3 then case ROWNUMBER when 7 then RECIPIENT else null end else null end) as SPLIT3RECOGNITIONCREDIT7RECIPIENT,
      max(case ROWNUM when 3 then case ROWNUMBER when 7 then RECAMOUNT else 0 end else 0 end) as SPLIT3RECOGNITIONCREDIT7AMOUNT,
      max(case ROWNUM when 3 then case ROWNUMBER when 7 then TYPE else null end else null end) as SPLIT3RECOGNITIONCREDIT7TYPE,
      max(case ROWNUM when 3 then case ROWNUMBER when 7 then EFFECTIVEDATE else null end else null end) as SPLIT3RECOGNITIONCREDIT7EFFECTIVEDATE,
      max(case ROWNUM when 3 then case ROWNUMBER when 8 then RECIPIENT else null end else null end) as SPLIT3RECOGNITIONCREDIT8RECIPIENT,
      max(case ROWNUM when 3 then case ROWNUMBER when 8 then RECAMOUNT else 0 end else 0 end) as SPLIT3RECOGNITIONCREDIT8AMOUNT,
      max(case ROWNUM when 3 then case ROWNUMBER when 8 then TYPE else null end else null end) as SPLIT3RECOGNITIONCREDIT8TYPE,
      max(case ROWNUM when 3 then case ROWNUMBER when 8 then EFFECTIVEDATE else null end else null end) as SPLIT3RECOGNITIONCREDIT8EFFECTIVEDATE,
      max(case ROWNUM when 3 then case ROWNUMBER when 9 then RECIPIENT else null end else null end) as SPLIT3RECOGNITIONCREDIT9RECIPIENT,
      max(case ROWNUM when 3 then case ROWNUMBER when 9 then RECAMOUNT else 0 end else 0 end) as SPLIT3RECOGNITIONCREDIT9AMOUNT,
      max(case ROWNUM when 3 then case ROWNUMBER when 9 then TYPE else null end else null end) as SPLIT3RECOGNITIONCREDIT9TYPE,
      max(case ROWNUM when 3 then case ROWNUMBER when 9 then EFFECTIVEDATE else null end else null end) as SPLIT3RECOGNITIONCREDIT9EFFECTIVEDATE,
      max(case ROWNUM when 3 then case ROWNUMBER when 10 then RECIPIENT else null end else null end) as SPLIT3RECOGNITIONCREDIT10RECIPIENT,
      max(case ROWNUM when 3 then case ROWNUMBER when 10 then RECAMOUNT else 0 end else 0 end) as SPLIT3RECOGNITIONCREDIT10AMOUNT,
      max(case ROWNUM when 3 then case ROWNUMBER when 10 then TYPE else null end else null end) as SPLIT3RECOGNITIONCREDIT10TYPE,
      max(case ROWNUM when 3 then case ROWNUMBER when 10 then EFFECTIVEDATE else null end else null end) as SPLIT3RECOGNITIONCREDIT10EFFECTIVEDATE,
      max(case ROWNUM when 4 then NAME else null end) as DESIGNATION4NAME,
      max(case ROWNUM when 4 then PUBLICNAME else null end) as DESIGNATION4PUBLICNAME,
      max(case ROWNUM when 4 then AMOUNT else 0 end) as DESIGNATION4AMOUNT,
      max(case ROWNUM when 4 then DESIGNATIONLOOKUPID else null end) as DESIGNATION4LOOKUPID,
      max(case ROWNUM when 4 then case ROWNUMBER when 1 then RECIPIENT else null end else null end) as SPLIT4RECOGNITIONCREDIT1RECIPIENT,
      max(case ROWNUM when 4 then case ROWNUMBER when 1 then RECAMOUNT else 0 end else 0 end) as SPLIT4RECOGNITIONCREDIT1AMOUNT,
      max(case ROWNUM when 4 then case ROWNUMBER when 1 then TYPE else null end else null end) as SPLIT4RECOGNITIONCREDIT1TYPE,
      max(case ROWNUM when 4 then case ROWNUMBER when 1 then EFFECTIVEDATE else null end else null end) as SPLIT4RECOGNITIONCREDIT1EFFECTIVEDATE,
      max(case ROWNUM when 4 then case ROWNUMBER when 2 then RECIPIENT else null end else null end) as SPLIT4RECOGNITIONCREDIT2RECIPIENT,
      max(case ROWNUM when 4 then case ROWNUMBER when 2 then RECAMOUNT else 0 end else 0 end) as SPLIT4RECOGNITIONCREDIT2AMOUNT,
      max(case ROWNUM when 4 then case ROWNUMBER when 2 then TYPE else null end else null end) as SPLIT4RECOGNITIONCREDIT2TYPE,
      max(case ROWNUM when 4 then case ROWNUMBER when 2 then EFFECTIVEDATE else null end else null end) as SPLIT4RECOGNITIONCREDIT2EFFECTIVEDATE,
      max(case ROWNUM when 4 then case ROWNUMBER when 3 then RECIPIENT else null end else null end) as SPLIT4RECOGNITIONCREDIT3RECIPIENT,
      max(case ROWNUM when 4 then case ROWNUMBER when 3 then RECAMOUNT else 0 end else 0 end) as SPLIT4RECOGNITIONCREDIT3AMOUNT,
      max(case ROWNUM when 4 then case ROWNUMBER when 3 then TYPE else null end else null end) as SPLIT4RECOGNITIONCREDIT3TYPE,
      max(case ROWNUM when 4 then case ROWNUMBER when 3 then EFFECTIVEDATE else null end else null end) as SPLIT4RECOGNITIONCREDIT3EFFECTIVEDATE,
      max(case ROWNUM when 4 then case ROWNUMBER when 4 then RECIPIENT else null end else null end) as SPLIT4RECOGNITIONCREDIT4RECIPIENT,
      max(case ROWNUM when 4 then case ROWNUMBER when 4 then RECAMOUNT else 0 end else 0 end) as SPLIT4RECOGNITIONCREDIT4AMOUNT,
      max(case ROWNUM when 4 then case ROWNUMBER when 4 then TYPE else null end else null end) as SPLIT4RECOGNITIONCREDIT4TYPE,
      max(case ROWNUM when 4 then case ROWNUMBER when 4 then EFFECTIVEDATE else null end else null end) as SPLIT4RECOGNITIONCREDIT4EFFECTIVEDATE,
      max(case ROWNUM when 4 then case ROWNUMBER when 5 then RECIPIENT else null end else null end) as SPLIT4RECOGNITIONCREDIT5RECIPIENT,
      max(case ROWNUM when 4 then case ROWNUMBER when 5 then RECAMOUNT else 0 end else 0 end) as SPLIT4RECOGNITIONCREDIT5AMOUNT,
      max(case ROWNUM when 4 then case ROWNUMBER when 5 then TYPE else null end else null end) as SPLIT4RECOGNITIONCREDIT5TYPE,
      max(case ROWNUM when 4 then case ROWNUMBER when 5 then EFFECTIVEDATE else null end else null end) as SPLIT4RECOGNITIONCREDIT5EFFECTIVEDATE,
      max(case ROWNUM when 4 then case ROWNUMBER when 6 then RECIPIENT else null end else null end) as SPLIT4RECOGNITIONCREDIT6RECIPIENT,
      max(case ROWNUM when 4 then case ROWNUMBER when 6 then RECAMOUNT else 0 end else 0 end) as SPLIT4RECOGNITIONCREDIT6AMOUNT,
      max(case ROWNUM when 4 then case ROWNUMBER when 6 then TYPE else null end else null end) as SPLIT4RECOGNITIONCREDIT6TYPE,
      max(case ROWNUM when 4 then case ROWNUMBER when 6 then EFFECTIVEDATE else null end else null end) as SPLIT4RECOGNITIONCREDIT6EFFECTIVEDATE,
      max(case ROWNUM when 4 then case ROWNUMBER when 7 then RECIPIENT else null end else null end) as SPLIT4RECOGNITIONCREDIT7RECIPIENT,
      max(case ROWNUM when 4 then case ROWNUMBER when 7 then RECAMOUNT else 0 end else 0 end) as SPLIT4RECOGNITIONCREDIT7AMOUNT,
      max(case ROWNUM when 4 then case ROWNUMBER when 7 then TYPE else null end else null end) as SPLIT4RECOGNITIONCREDIT7TYPE,
      max(case ROWNUM when 4 then case ROWNUMBER when 7 then EFFECTIVEDATE else null end else null end) as SPLIT4RECOGNITIONCREDIT7EFFECTIVEDATE,
      max(case ROWNUM when 4 then case ROWNUMBER when 8 then RECIPIENT else null end else null end) as SPLIT4RECOGNITIONCREDIT8RECIPIENT,
      max(case ROWNUM when 4 then case ROWNUMBER when 8 then RECAMOUNT else 0 end else 0 end) as SPLIT4RECOGNITIONCREDIT8AMOUNT,
      max(case ROWNUM when 4 then case ROWNUMBER when 8 then TYPE else null end else null end) as SPLIT4RECOGNITIONCREDIT8TYPE,
      max(case ROWNUM when 4 then case ROWNUMBER when 8 then EFFECTIVEDATE else null end else null end) as SPLIT4RECOGNITIONCREDIT8EFFECTIVEDATE,
      max(case ROWNUM when 4 then case ROWNUMBER when 9 then RECIPIENT else null end else null end) as SPLIT4RECOGNITIONCREDIT9RECIPIENT,
      max(case ROWNUM when 4 then case ROWNUMBER when 9 then RECAMOUNT else 0 end else 0 end) as SPLIT4RECOGNITIONCREDIT9AMOUNT,
      max(case ROWNUM when 4 then case ROWNUMBER when 9 then TYPE else null end else null end) as SPLIT4RECOGNITIONCREDIT9TYPE,
      max(case ROWNUM when 4 then case ROWNUMBER when 9 then EFFECTIVEDATE else null end else null end) as SPLIT4RECOGNITIONCREDIT9EFFECTIVEDATE,
      max(case ROWNUM when 4 then case ROWNUMBER when 10 then RECIPIENT else null end else null end) as SPLIT4RECOGNITIONCREDIT10RECIPIENT,
      max(case ROWNUM when 4 then case ROWNUMBER when 10 then RECAMOUNT else 0 end else 0 end) as SPLIT4RECOGNITIONCREDIT10AMOUNT,
      max(case ROWNUM when 4 then case ROWNUMBER when 10 then TYPE else null end else null end) as SPLIT4RECOGNITIONCREDIT10TYPE,
      max(case ROWNUM when 4 then case ROWNUMBER when 10 then EFFECTIVEDATE else null end else null end) as SPLIT4RECOGNITIONCREDIT10EFFECTIVEDATE,
      max(case ROWNUM when 5 then NAME else null end) as DESIGNATION5NAME,
      max(case ROWNUM when 5 then PUBLICNAME else null end) as DESIGNATION5PUBLICNAME,
      max(case ROWNUM when 5 then AMOUNT else 0 end) as DESIGNATION5AMOUNT,
      max(case ROWNUM when 5 then DESIGNATIONLOOKUPID else null end) as DESIGNATION5LOOKUPID,
      max(case ROWNUM when 5 then case ROWNUMBER when 1 then RECIPIENT else null end else null end) as SPLIT5RECOGNITIONCREDIT1RECIPIENT,
      max(case ROWNUM when 5 then case ROWNUMBER when 1 then RECAMOUNT else 0 end else 0 end) as SPLIT5RECOGNITIONCREDIT1AMOUNT,
      max(case ROWNUM when 5 then case ROWNUMBER when 1 then TYPE else null end else null end) as SPLIT5RECOGNITIONCREDIT1TYPE,
      max(case ROWNUM when 5 then case ROWNUMBER when 1 then EFFECTIVEDATE else null end else null end) as SPLIT5RECOGNITIONCREDIT1EFFECTIVEDATE,
      max(case ROWNUM when 5 then case ROWNUMBER when 2 then RECIPIENT else null end else null end) as SPLIT5RECOGNITIONCREDIT2RECIPIENT,
      max(case ROWNUM when 5 then case ROWNUMBER when 2 then RECAMOUNT else 0 end else 0 end) as SPLIT5RECOGNITIONCREDIT2AMOUNT,
      max(case ROWNUM when 5 then case ROWNUMBER when 2 then TYPE else null end else null end) as SPLIT5RECOGNITIONCREDIT2TYPE,
      max(case ROWNUM when 5 then case ROWNUMBER when 2 then EFFECTIVEDATE else null end else null end) as SPLIT5RECOGNITIONCREDIT2EFFECTIVEDATE,
      max(case ROWNUM when 5 then case ROWNUMBER when 3 then RECIPIENT else null end else null end) as SPLIT5RECOGNITIONCREDIT3RECIPIENT,
      max(case ROWNUM when 5 then case ROWNUMBER when 3 then RECAMOUNT else 0 end else 0 end) as SPLIT5RECOGNITIONCREDIT3AMOUNT,
      max(case ROWNUM when 5 then case ROWNUMBER when 3 then TYPE else null end else null end) as SPLIT5RECOGNITIONCREDIT3TYPE,
      max(case ROWNUM when 5 then case ROWNUMBER when 3 then EFFECTIVEDATE else null end else null end) as SPLIT5RECOGNITIONCREDIT3EFFECTIVEDATE,
      max(case ROWNUM when 5 then case ROWNUMBER when 4 then RECIPIENT else null end else null end) as SPLIT5RECOGNITIONCREDIT4RECIPIENT,
      max(case ROWNUM when 5 then case ROWNUMBER when 4 then RECAMOUNT else 0 end else 0 end) as SPLIT5RECOGNITIONCREDIT4AMOUNT,
      max(case ROWNUM when 5 then case ROWNUMBER when 4 then TYPE else null end else null end) as SPLIT5RECOGNITIONCREDIT4TYPE,
      max(case ROWNUM when 5 then case ROWNUMBER when 4 then EFFECTIVEDATE else null end else null end) as SPLIT5RECOGNITIONCREDIT4EFFECTIVEDATE,
      max(case ROWNUM when 5 then case ROWNUMBER when 5 then RECIPIENT else null end else null end) as SPLIT5RECOGNITIONCREDIT5RECIPIENT,
      max(case ROWNUM when 5 then case ROWNUMBER when 5 then RECAMOUNT else 0 end else 0 end) as SPLIT5RECOGNITIONCREDIT5AMOUNT,
      max(case ROWNUM when 5 then case ROWNUMBER when 5 then TYPE else null end else null end) as SPLIT5RECOGNITIONCREDIT5TYPE,
      max(case ROWNUM when 5 then case ROWNUMBER when 5 then EFFECTIVEDATE else null end else null end) as SPLIT5RECOGNITIONCREDIT5EFFECTIVEDATE,
      max(case ROWNUM when 5 then case ROWNUMBER when 6 then RECIPIENT else null end else null end) as SPLIT5RECOGNITIONCREDIT6RECIPIENT,
      max(case ROWNUM when 5 then case ROWNUMBER when 6 then RECAMOUNT else 0 end else 0 end) as SPLIT5RECOGNITIONCREDIT6AMOUNT,
      max(case ROWNUM when 5 then case ROWNUMBER when 6 then TYPE else null end else null end) as SPLIT5RECOGNITIONCREDIT6TYPE,
      max(case ROWNUM when 5 then case ROWNUMBER when 6 then EFFECTIVEDATE else null end else null end) as SPLIT5RECOGNITIONCREDIT6EFFECTIVEDATE,
      max(case ROWNUM when 5 then case ROWNUMBER when 7 then RECIPIENT else null end else null end) as SPLIT5RECOGNITIONCREDIT7RECIPIENT,
      max(case ROWNUM when 5 then case ROWNUMBER when 7 then RECAMOUNT else 0 end else 0 end) as SPLIT5RECOGNITIONCREDIT7AMOUNT,
      max(case ROWNUM when 5 then case ROWNUMBER when 7 then TYPE else null end else null end) as SPLIT5RECOGNITIONCREDIT7TYPE,
      max(case ROWNUM when 5 then case ROWNUMBER when 7 then EFFECTIVEDATE else null end else null end) as SPLIT5RECOGNITIONCREDIT7EFFECTIVEDATE,
      max(case ROWNUM when 5 then case ROWNUMBER when 8 then RECIPIENT else null end else null end) as SPLIT5RECOGNITIONCREDIT8RECIPIENT,
      max(case ROWNUM when 5 then case ROWNUMBER when 8 then RECAMOUNT else 0 end else 0 end) as SPLIT5RECOGNITIONCREDIT8AMOUNT,
      max(case ROWNUM when 5 then case ROWNUMBER when 8 then TYPE else null end else null end) as SPLIT5RECOGNITIONCREDIT8TYPE,
      max(case ROWNUM when 5 then case ROWNUMBER when 8 then EFFECTIVEDATE else null end else null end) as SPLIT5RECOGNITIONCREDIT8EFFECTIVEDATE,
      max(case ROWNUM when 5 then case ROWNUMBER when 9 then RECIPIENT else null end else null end) as SPLIT5RECOGNITIONCREDIT9RECIPIENT,
      max(case ROWNUM when 5 then case ROWNUMBER when 9 then RECAMOUNT else 0 end else 0 end) as SPLIT5RECOGNITIONCREDIT9AMOUNT,
      max(case ROWNUM when 5 then case ROWNUMBER when 9 then TYPE else null end else null end) as SPLIT5RECOGNITIONCREDIT9TYPE,
      max(case ROWNUM when 5 then case ROWNUMBER when 9 then EFFECTIVEDATE else null end else null end) as SPLIT5RECOGNITIONCREDIT9EFFECTIVEDATE,
      max(case ROWNUM when 5 then case ROWNUMBER when 10 then RECIPIENT else null end else null end) as SPLIT5RECOGNITIONCREDIT10RECIPIENT,
      max(case ROWNUM when 5 then case ROWNUMBER when 10 then RECAMOUNT else 0 end else 0 end) as SPLIT5RECOGNITIONCREDIT10AMOUNT,
      max(case ROWNUM when 5 then case ROWNUMBER when 10 then TYPE else null end else null end) as SPLIT5RECOGNITIONCREDIT10TYPE,
      max(case ROWNUM when 5 then case ROWNUMBER when 10 then EFFECTIVEDATE else null end else null end) as SPLIT5RECOGNITIONCREDIT10EFFECTIVEDATE
    from (
      select
        row_number() over (partition by ROWNUM order by REVENUERECOGNITION.EFFECTIVEDATE, CONSTITUENT.NAME) as ROWNUMBER,
        CONSTITUENT.NAME as RECIPIENT, REVENUERECOGNITION.AMOUNT as RECAMOUNT, REVENUERECOGNITIONTYPECODE.DESCRIPTION as TYPE, REVENUERECOGNITION.EFFECTIVEDATE,
        V2.*
      from (
        select *
        from (
          select
            DESIGNATIONLEVEL.NAME,
            isnull(nullif(DESIGNATION.VANITYNAME, ''), DESIGNATIONLEVEL.NAME) as PUBLICNAME,
            FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT as AMOUNT, DESIGNATION.USERID as DESIGNATIONLOOKUPID,
            convert(varchar(36), FINANCIALTRANSACTIONLINEITEM.ID) as FINANCIALTRANSACTIONLINEITEMID,
            row_number() over (order by BASEAMOUNT desc) as ROWNUM
          from REVENUESPLIT_EXT
          inner join FINANCIALTRANSACTIONLINEITEM on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
          left join dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
          left join dbo.DESIGNATIONLEVEL on DESIGNATIONLEVEL.ID = coalesce(DESIGNATION.DESIGNATIONLEVEL5ID, DESIGNATION.DESIGNATIONLEVEL4ID, DESIGNATION.DESIGNATIONLEVEL3ID, DESIGNATION.DESIGNATIONLEVEL2ID, DESIGNATION.DESIGNATIONLEVEL1ID)
          where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID) as V1
        where rownum <= 5) as V2
      left join dbo.REVENUERECOGNITION on V2.FINANCIALTRANSACTIONLINEITEMID = REVENUERECOGNITION.REVENUESPLITID
      left join dbo.CONSTITUENT on REVENUERECOGNITION.CONSTITUENTID = CONSTITUENT.ID
      left join dbo.REVENUERECOGNITIONTYPECODE on REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID) V3
      where ROWNUMBER <= 10) as V4
    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = @REVENUEID
    inner join dbo.CONSTITUENT on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
    left join dbo.APPEAL on REVENUE_EXT.APPEALID = APPEAL.ID
    left join (
      select top 1
        cast(FINANCIALTRANSACTION.DATE as datetime) as DATE,
        sum(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT) as AMOUNT,
        @REVENUEID as FINANCIALTRANSACTIONID
      from dbo.FINANCIALTRANSACTIONLINEITEM
      inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
      where FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
      and FINANCIALTRANSACTIONLINEITEM.ID in (
        select INSTALLMENTSPLITPAYMENT.PAYMENTID from dbo.INSTALLMENTSPLITPAYMENT where INSTALLMENTSPLITPAYMENT.PLEDGEID = @REVENUEID
      )
      group by FINANCIALTRANSACTION.ID, FINANCIALTRANSACTION.DATE, FINANCIALTRANSACTION.DATEADDED
      order by FINANCIALTRANSACTION.DATE desc, FINANCIALTRANSACTION.DATEADDED desc
    ) as LASTPAYMENT on FINANCIALTRANSACTION.ID = LASTPAYMENT.FINANCIALTRANSACTIONID
    inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
    inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
    left join dbo.CURRENCY on FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = CURRENCY.ID
    left join dbo.CURRENCY as BASECURRENCY on CURRENCYSET.BASECURRENCYID = BASECURRENCY.ID
)