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 i.DocumentType=s.DocumentType 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 t.TermsCode='81'
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?