Share
ReportI am trying to make a sql query that shows open invoices at a point in time with ageing by due date, I have done this by returning records from CustTrans where CreatedDateTime <= @ReportDate and joining to CustSettlement where CreatedDateTime <= @ReportDate. Simple, right??? NO! Problem I am having is with invoices that have a payment schedule attached... I've joined to CustPaymSchedLine to get the breakdown of amounts due according to the schedule assigned, e.g. equal amounts on 30 days, 60 days and 90 days.
In the example I am looking at the customer has not paid the full amount of the first two lines so it's been allocated kind of like this...
1: customer paid $500, leaving $500 due on first line, and $1,000 each on lines 2 and 3
2: customer paid $1,000, $500 allocated to clear line 1 and $500 to line 2, leaving $500 due on line 2 $1k on line 3
My question is... how do I link from CustSettlement to CustPaymSchedLine to make sure the settled amounts are assigned to the correct line?
*This post is locked for comments
I have the same question (0)
All responses (
Answers (
16
