UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE

CREATE function dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE
(
  @REVENUESPLITID uniqueidentifier,
  @PAYMENTMETHOD nvarchar(50),
  @APPLICATION nvarchar(50)
)
returns nvarchar(255)
as
begin
  declare @REFERENCE nvarchar(255);
  -- Adding "-DEL" for Reversals created by Deletions will make it 90

  -- First check if the revenue record has a reference and use it. If it does not then use the default string

  select
    @REFERENCE = isnull(REVENUEREFERENCE.REFERENCE, '')
  from
    dbo.FINANCIALTRANSACTIONLINEITEM
  inner join
    dbo.REVENUEREFERENCE on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUEREFERENCE.ID
  where
    FINANCIALTRANSACTIONLINEITEM.ID = @REVENUESPLITID;

  if (len(@REFERENCE) = 0) or (@REFERENCE is null)
  begin
    declare @REVENUEID uniqueidentifier;
    declare @TRANSACTIONTYPECODE tinyint;
    declare @TRANSACTIONTYPE nvarchar(255);
    declare @APPLICATIONCODE tinyint;
    declare @APPLICATION2 nvarchar(255);
    declare @CONSTITUENTKEYNAME nvarchar(255);
    declare @SPLITTYPECODE tinyint;

    select
      @TRANSACTIONTYPECODE = FINANCIALTRANSACTION.TYPECODE,
      @TRANSACTIONTYPE = FINANCIALTRANSACTION.TYPE,
      @REVENUEID = FINANCIALTRANSACTION.ID,
      @CONSTITUENTKEYNAME = CONSTITUENT.KEYNAME,
      @APPLICATIONCODE = REVENUESPLIT_EXT.APPLICATIONCODE,
      @APPLICATION2 = REVENUESPLIT_EXT.APPLICATION,
      @SPLITTYPECODE = REVENUESPLIT_EXT.TYPECODE
    from
      dbo.FINANCIALTRANSACTION
    inner join
      dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
    inner join
      dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID      
    left join
      dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
    where
      FINANCIALTRANSACTIONLINEITEM.ID = @REVENUESPLITID;

    if @TRANSACTIONTYPECODE = 0 and @APPLICATIONCODE = 3 and @SPLITTYPECODE = 2
        set @APPLICATION = 'Recurring membership';

    if @TRANSACTIONTYPECODE = 4 and @APPLICATION <> 'All'
    begin
      set @REFERENCE = left(@CONSTITUENTKEYNAME, 26) + '-' + ' Planned Gift';
      return @REFERENCE;
    end

    if @TRANSACTIONTYPECODE = 5
    begin
      set @REFERENCE = @TRANSACTIONTYPE + '-' + convert(nvarchar(50),(select SALESORDER.SEQUENCEID from dbo.SALESORDER with (nolock) where REVENUEID = @REVENUEID)) + '-' + left(@APPLICATION,40);
      return @REFERENCE;
    end

    if @PAYMENTMETHOD = 'None'
    begin
      set @REFERENCE = left(@CONSTITUENTKEYNAME, 46) + '-' + left(@APPLICATION, 40);
      return @REFERENCE;
    end

    if @APPLICATION = 'All'
    begin
      set @REFERENCE = left(@CONSTITUENTKEYNAME, 66) + '-' + left(@PAYMENTMETHOD, 22);
      return @REFERENCE;
    end

    if @APPLICATION = 'Gift aid'
    begin
      set @REFERENCE = left(@CONSTITUENTKEYNAME, 66) + '-' + left(@APPLICATION, 20);
      return @REFERENCE;
    end

    if @APPLICATION = 'Gift fee'
    begin
      if @APPLICATIONCODE not in(0, 2, 4, 12)
      begin
        set @REFERENCE = left(@CONSTITUENTKEYNAME, 26) + '-' + left(@APPLICATION2, 35) + ' Payment-' + left(@PAYMENTMETHOD, 15) + '-Gift fee';
        return @REFERENCE;
      end

      if @APPLICATIONCODE = 2
      begin
        set @REFERENCE = left(@CONSTITUENTKEYNAME, 26) + '-' + left(coalesce((
          select top 1 DETAIL.[TYPE]
          from dbo.INSTALLMENTPAYMENT 
          inner join dbo.FINANCIALTRANSACTION as PLEDGE on INSTALLMENTPAYMENT.PLEDGEID = PLEDGE.ID
          left join dbo.REVENUEPOSTED as PLEDGEPOSTED on PLEDGE.ID = PLEDGEPOSTED.ID
          cross apply dbo.UFN_POSTTOGLPROCESS_MAPPLEDGEPAYMENTS(PLEDGE.ID, case when PLEDGE.POSTSTATUSCODE = 3 then 2 when PLEDGEPOSTED.ID is not null then 0 else 1 end) as DETAIL
          where INSTALLMENTPAYMENT.PAYMENTID = @REVENUESPLITID),
          @APPLICATION2 + ' Payment'), 35) + '-' + left(@PAYMENTMETHOD, 15) + '-Gift fee';
        return @REFERENCE;
      end

      set @REFERENCE = left(coalesce(@CONSTITUENTKEYNAME, ''), 26) + '-' + left(@APPLICATION2, 35) + '-' + left(@PAYMENTMETHOD, 15) + '-Gift fee'
      return @REFERENCE;
    end

    if @TRANSACTIONTYPECODE = 0
    begin
      if @APPLICATIONCODE = 10
      begin
        set @REFERENCE = @TRANSACTIONTYPE + '-' + left(@APPLICATION,40) + '-' + coalesce(CONVERT(NVARCHAR(50),(select SALESORDER.SEQUENCEID from dbo.SALESORDER with (nolock) where ID = (select SALESORDERID from dbo.SALESORDERPAYMENT where PAYMENTID = @REVENUEID))), '');
        return @REFERENCE;
      end

      if @APPLICATIONCODE = 4
      begin
        set @REFERENCE = left(@CONSTITUENTKEYNAME, 66) + '-' + left(@APPLICATION, 40) + ' Revenue' + '-' + left(@PAYMENTMETHOD, 20);
        return @REFERENCE;
      end

      if @APPLICATIONCODE = 11
      begin
        set @REFERENCE = @TRANSACTIONTYPE + '-' + left(@APPLICATION,40) + '-' + left(@PAYMENTMETHOD, 20);
        return @REFERENCE;
      end

      if @APPLICATIONCODE in (5, 19) --if a membership or membership installment plan payment

      begin
        set @REFERENCE = left(@CONSTITUENTKEYNAME, 26) + '-' + left(@APPLICATION2, 40) + ' Payment' + '-' + left(@PAYMENTMETHOD, 20);
        return @REFERENCE;
      end

      if @APPLICATIONCODE not in (0, 2, 4, 12)
      begin
        set @REFERENCE = left(@CONSTITUENTKEYNAME, 26) + '-' + left(@APPLICATION, 40) + ' Payment' + '-' + left(@PAYMENTMETHOD, 20);
        return @REFERENCE;
      end

      if @APPLICATIONCODE = 12
      begin
        if @APPLICATION = 'Auction purchase loss' or @APPLICATION = 'Auction purchase gain'
        begin
          set @REFERENCE = left(coalesce(@CONSTITUENTKEYNAME, ''), 26) + '-' + left(@APPLICATION,40);
          return @REFERENCE;
        end

        set @REFERENCE = left(coalesce(@CONSTITUENTKEYNAME, ''), 26) + '-' + left(@APPLICATION,40) + ' Payment' + '-' + left(@PAYMENTMETHOD, 20);
        return @REFERENCE;
      end
    end

    set @REFERENCE = left(coalesce(@CONSTITUENTKEYNAME, ''), 26) + '-' + left(@APPLICATION, 40) + '-' + left(@PAYMENTMETHOD, 20);
    return @REFERENCE;
  end

  return @REFERENCE;
end