I have a situation I have been working on for quite some time. We sometimes give our customers extended terms and allow them to make payments toward a larger invoice. Those payments are expressed with a notation on the invoice as three separate payments with three due dates, however, in the database table it's still a single invoice with a single due date. I'm required to age the payments even though they don't technically exist anywhere.

Here's where I'm at thus far. I have created a view which takes the invoices with extended terms and calculates what the payment amount and due date should be for each extended terms invoice payment.:

alter view V_ArExtTermsInv as
Select i.Customer, i.Invoice as 'Invoice', 
CAST(i.InvoiceDate  as date) as 'InvoiceDate',
i.SalesOrder as 'SalesOrder',
i.InvoiceBal1 as 'Bal',  
i.CurrencyValue as 'Total',
t.TermsCode, t.DueDays, 
tt.ExtendedTermsCode1 as 'Ext1',
tt.ExtendedTermsPct1 as 'ExtPct1',
CAST(DATEADD(day, t.DueDays, i.InvoiceDate) as date) as 'PmtDt1',
round(i.CurrencyValue*(tt.ExtendedTermsPct1/100),2) as 'Pmt1',
tt.ExtendedTermsCode2 as 'Ext2',
tt.ExtendedTermsPct2 as 'ExtPct2',
CAST(DATEADD(day, t.DueDays+30, i.InvoiceDate) as date) as 'PmtDt2',
round(i.CurrencyValue*(tt.ExtendedTermsPct2/100),2) as 'Pmt2',
tt.ExtendedTermsCode3 as 'Ext3',
tt.ExtendedTermsPct3 as 'ExtPct3',
CAST(DATEADD(day, t.DueDays+60, i.InvoiceDate) as date) as 'PmtDt3',
round(i.CurrencyValue*(tt.ExtendedTermsPct3/100),2) as 'Pmt3',
tt.ExtendedTermsCode4 as 'Ext4',
tt.ExtendedTermsPct4 as 'ExtPct4',
CAST(DATEADD(day, t.DueDays+90, i.InvoiceDate) as date) as 'PmtDt4',
i.CurrencyValue*(tt.ExtendedTermsPct4/100) as 'Pmt4',
tt.ExtendedTermsCode5 as 'Ext5',
tt.ExtendedTermsPct5 as 'ExtPct5',
CAST(DATEADD(day, t.DueDays+120, i.InvoiceDate) as date) as 'PmtDt5',
round(i.CurrencyValue*(tt.ExtendedTermsPct5/100),2) as 'Pmt5',
tt.ExtendedTermsCode6 as 'Ext6',
tt.ExtendedTermsPct6 as 'ExtPct6', 
CAST(DATEADD(day, t.DueDays+150, i.InvoiceDate) as date) as 'PmtDt6',
round(i.CurrencyValue*(tt.ExtendedTermsPct6/100),2) as 'Pmt6',
DATEDIFF(day, i.InvoiceDate, CURRENT_TIMESTAMP) as 'DaysFromInv'
from ArInvoice as i
left outer join ArCustomer as c on i.Customer=c.Customer
left outer join ArTrnSummary as s on i.Invoice=s.Invoice and    
left outer join TblArTerms as t on i.TermsCode=t.TermsCode
left outer join [TblArTerms+] as tt on t.TermsCode=tt.TermsCode
where i.InvoiceBal1<>0 and i.MonthInvBalZero=0 and i.YearInvBalZero=0 and 

The Invoice, Date (3/10/2019), BalDue, and InvTotal are the only values here carried in our current database. As it stands, the invoice appears to be past due.

I need to somehow show the balance due here of Pmt1 and Pmt2 as 0 so that I can age this report correctly. As a side note, customers don't always pay the payments in the exact amounts. They might pay all of Pmt1 and half of Pmt2. My logic thus far is that I don't know what they are paying technically. I just want to take the TotalPaid and apply it from Pmt1 to Pmt3 setting Pmt1 to 0, Pmt2 to 0, and Pmt3 to 0 or a negative number if they overpay in total. Is this too complicated to accomplish?

  • So you only have a single table with each payment listed as a column? Are you able to change this structure? To say... add a payments table with a 1:N relationship? Because you'd end up adding columns if you have more than 3 months of payment plans (which also is going to happen if you have partial payments and/or extend the plan terms for the customers). Or are you able to create deferred revenue transactions in your accounting system somehow? What you are trying to do with your current schema is difficult and risky. – Jacob H May 10 '19 at 16:22
  • 1
    Welcome to DBA.SE. Do us a favor and put together a Minimal, Complete, and Verifiable Example. This will allow us a way to look at the structure of your table(s) with some relevant sample data so we can provide you with an actual query to solve your problem. – John Eisbrener May 10 '19 at 16:27
  • The payments are in my view so I can add calculated columns as needed. The max number of payments is 6 but I'm only showing 3 here. I should say that to determine the number of payments and the percentage of total assigned to each payment, we have a terms code table. If an invoice is assigned terms code 81, it gets 3 equal payments (30/60/90). This is how I determine what the due dates are and in what amounts. This terms code table is what I use to display the payment amounts, due dates, etc. for the customer on the invoice, however, it's not calculated in the table AR Transaction Table. – Karen D May 10 '19 at 16:29
  • Is each individual payment being added to the balance sheet? With the full invoice hitting it on the initial transaction? It's been a couple of years since I've been in the accounting business, but if I recall correctly, that's how we did this. – Jacob H May 10 '19 at 16:35
  • No, all that is being reported on financials is the invoice total and the balance due on the total. This is an accounting software customization. Our account software doesn't handle extended terms payments so we are giving the customer extended terms, displaying them on the invoice (via Crystal Reports), but technically in the database it's still the single invoice amount and due date. – Karen D May 10 '19 at 16:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.