On Account Balance Query

The canned on account reporting did not meet out reporting needs so we wrote the following function used to power our report. We use Create_Dt on the payment record not the PMT_DT because create_dt is the one that lines up with our financial reporting and the offsetting entry to the trasnaction. We are still testing, but so far everything is working as expected.

The function supports:

1. Showing only with open balances
2. Showing only open balances and use history
3. Showing use history
4. Showing what use history would have been up until a specific date
5. Tracking to show how many times the balance was set to 0
6. By customer number lookup

Here is the code for the function:

IF OBJECT_ID (N'dbo.LFT_MBA_FIN_OnAccount_Hist', N'TF') IS NULL
begin
    Declare @SQL varchar(max) =
    'CREATE FUNCTION dbo.LFT_MBA_FIN_OnAccount_Hist()

    returns
     @tbl table (
    ID            int
    primary key (ID))
    as begin
        insert into @tbl
        Select 1
        return
    end'
    execute (@SQl)
end
GO  
alter FUNCTION dbo.LFT_MBA_FIN_OnAccount_Hist(

@CustNoStr            varchar(max) --
,@LastTranDate        datetime --= '6/22/21'
,@NewStartDate        date --= '05/01/21'
,@NewEndDate        datetime --= '05/05/21'
,@DetailLevel        int --= 5
)
returns @tbl table (
CustomerName        varchar(500),
[CustomerNo] [int] NOT NULL index idxCustNo clustered,
    [OrderNo] [int] NULL,
    [RefNo] [int] NULL,
    [PaymentMethodID] [int] NOT NULL,
    [perfno] [int] NULL,
    [SeqNo] [int] NOT NULL,
    [TransactionNo] [int] NULL,
    [TranDate] [datetime] NULL,
    [OnAccount] [money] NULL,
    [OffAccount] [money] NULL,
    [Balance] [money] NULL
    ,CreatedBy                varchar(20)
    ,[To0SeqNo] [int] NULL
    ,PerfTypeID            int
    ,PerfType            varchar(50)
    primary key(CustomerNo, SeqNo)
)
As Begin

/*Docu
    Date Created: 08/06/2021
    Created by:  Ronald Radford
    Purpose: Returns list of on account balances

    @CustNoStr            varchar(max)  - list of customer numbers
    ,@LastTranDate        datetime - Cut off transaction date to see historical values
    ,@DetailLevel        int - level of detail to return
    ,@NewStartDate        date - Start date for new on account customers
    ,@NewEndDate        datetime - end date for new on account customers

    Object Notes:
        Detail levels
        1 = Only customers with a balance
        2 = Only customers with balance with full history
        3 = Full history
        4 = New On Account Balances within date range
        5 = New On Account Balance and History within date range
    

    */            


    Declare @tblHist table(
    CustomerNo                int    index idxCustomer
    ,OrderNo                int
    ,RefNo                    int
    ,PerfNo                    int
    ,PaymentMethodID        int
    ,SeqNo                    int
    ,TransactionNo            int
    ,TranDate                datetime
    ,OnAccount                Money
    ,OffAccount                money
    ,CreatedBy                varchar(20)
    ,primary key(Customerno,PaymentMethodID, SeqNo)
    )

    Declare @tblData table (
        [CustomerNo] [int] NOT NULL index idxCustNo clustered,
        [OrderNo] [int] NULL,
        [RefNo] [int] NULL,
        [PaymentMethodID] [int] NOT NULL,
        [perfno] [int] NULL,
        [SeqNo] [int] NOT NULL,
        [TransactionNo] [int] NULL,
        [TranDate] [datetime] NULL,
        [OnAccount] [money] NULL,
        [OffAccount] [money] NULL,
        [Balance] [money] NULL
        ,CreatedBy                varchar(20)
        ,[To0SeqNo] [int] NULL
        primary key(CustomerNo, SeqNo)
    )

    Declare @tblCust Table(
    CustomerNo        int
    primary key(CustomerNo))

    Set @LastTranDate = DBO.LFN_MBA_EOD (@LastTranDate )
    Set @DetailLevel = isnull(@DetailLevel,1)
    Set @CustNoStr = isnull(@CustNoStr,'')
    Set @NewEndDate = DBO.LFN_MBA_EOD (isnull(@NewEndDate, @LastTranDate )  )
    Set @NewStartDate = isnull(@NewStartDate,'01/01/1900')

    insert into @tblCust
    Select distinct Element from dbo.FT_SPLIT_LIST(@CustnoStr,',')
    where ISNUMERIC (element) = 1

    insert into @tblHist
    Select customer_no , isnull(order_no,0) as OrderNo , ref_no, perf_no  , pmt_method
    ,ROW_NUMBER () over (partition by Customer_No, pmt_method order by p.transaction_No) as SeqNo
    , p.transaction_no  , Min(Trn_Dt) as TranDate
    ,abs( Sum(iif(Pmt_Amt <0, Pmt_Amt,0.00))) as OnAccount
    , Sum(iif(Pmt_Amt >0, Pmt_Amt,0.00)) as OffAccount , p.created_by
    from T_PAYMENT  as P
    inner join T_TRANSACTION as T on p.transaction_no = p.transaction_no and t.sequence_no = p.sequence_no
    inner join TR_PAYMENT_METHOD as Pm on p.pmt_method = PM.id
     where pmt_type = 2 and p.create_dt <= iif(@LastTranDate <@NewEndDate and @DetailLevel > 3
     , @NewEndDate, @LastTranDate)
     and (Len(@CustNoStr) = 0 or (Len(@CustNoStr) > 0
        and customer_no in (Select CustomerNo from @tblCust )))
 
      group by customer_no , order_no , ref_no, perf_no  ,pmt_method, p.transaction_no,p.created_by  ;

     
 
     WITH Totals (CustomerNo, OrderNo, RefNo, PaymentMethodID, perfno
     , SeqNo, TransactionNo, TranDate, OnAccount, OffAccount, Balance, CreatedBy)  
    AS  
    (  
      Select CustomerNo, OrderNo, RefNo, PaymentMethodID, PerfNo
     , SeqNo, TransactionNo, TranDate, OnAccount, OffAccount, Balance,CreatedBy  from @tblHist as H
     cross apply ( Select sum(OnAccount -OffAccount ) as Balance  from @tblHist as R where r.CustomerNo = h.CustomerNo
     and r.PaymentMethodID = h.PaymentMethodID and r.SeqNo <= h.SeqNo ) as RT

    )  
    insert into @tblData
    SELECT *
    FROM Totals as T
    cross apply (Select Count(*) as HistSeqNo
    from Totals as C where t.CustomerNo = c.CustomerNo and t.SeqNo >= c.SeqNo  
    and C.SeqNo > (Select isnull(Max(Seqno),0) from Totals
    where CustomerNo = t.CustomerNo and SeqNo < t.SeqNo and Balance = 0) ) as S
    order by CustomerNo , SeqNo
    OPTION (MAXRECURSION 1000)

    insert into @tbl
    Select isnull(dn.display_name, 'Unknown') as CustomerName,  bd.*, isnull(p.perf_type,0) as PerfTypeID
    ,isnull(pt.description,'') as PerfType
    from
    (Select * from @tblData as D
    Where
    ( @DetailLevel = 1 and exists (Select 1 from
        (Select  CustomerNo, Max(SeqNo) as SeqNo from @tblData
        group by CustomerNo ) as M where m.CustomerNo = d.CustomerNo and d.SeqNo = m.SeqNo  )
        and Balance != 0 )
    or
    ( @DetailLevel = 2 and customerno in (Select d.CustomerNo  from @tblData as D inner join
        (Select  CustomerNo, Max(SeqNo) as SeqNo from @tblData
        group by CustomerNo ) as M on m.CustomerNo = d.CustomerNo and d.SeqNo = m.SeqNo where d.Balance !=0   )
         )
    or (@DetailLevel = 3)

    or (@DetailLevel = 4 and SeqNo = 1 and TranDate between @NewStartDate and @NewEndDate )

    or (@DetailLevel = 5
             and customerNo in (Select CustomerNo from @tblData
                    where SeqNo = 1 and TranDate
                        between @NewStartDate and @NewEndDate)  )) as BD
                left join T_perf as P on bd.perfno = p.perf_no
                left join TR_PERF_TYPE as PT on P.perf_type = pt.id and BD.perfno > 0
                left join FT_CONSTITUENT_DISPLAY_NAME () as DN on bd.CustomerNo = dn.customer_no
    option (recompile)
    return
end

This is the LFN_MBA_EOD function definition

IF OBJECT_ID (N'dbo.LFN_MBA_EOD', N'FN') IS NULL
begin
    Declare @SQL varchar(max) =
    'CREATE FUNCTION dbo.LFN_MBA_EOD()

    Returns datetime
    as begin
        
        return getdate()
    end'
    execute (@SQl)
end

Go
Alter FUNCTION [dbo].[LFN_MBA_EOD](@DateValue datetime)
RETURNS datetime
AS
BEGIN

/*Docu
    Date Created: 05/06/2020
    Created by:  Ronald Radford
    Purpose: Returns the end of day for a given date

    ,@DateValue        datetime - Date value to be set to the end of the day

    Object Notes:

    */            


  RETURN dateadd(ms,-3,cast(dateadd(day,1,cast(isnull(@DateValue,getdate()) as date)) as datetime))
END

Here is a sample of it used in a report to show open balances:

New balances started within a specific date range:

This shows an example of each time the customer's balance was down to zero and then a reset with a new amount