Christopher Ford (2/15/2008)
I'm going to guess that implementing the logic of sum(detail)=Total Payment would best be implemented on the application side in a .Net class library?I can't think of an "easy" way to do that in SQL when you want to apply a payment to several invoices, starting with the oldest first and going forward OR by customer choosing which invoices it gets applied to.
I would set up that logic in both places (DB and UI). In other words - they don't get to submit without the numbers tying in, but also have a secondary check in the DB, so that it will catch you should you have a "momentary lapse" when coding on the UI side.
How you apply the payments or adjustments (keep in mind you could have more than straight payments and invoices, like - discounts, reversed payments, promos, cancellations, writeoffs, late fees, etc...) is a business decision (project requirements).
While you might care to have the "oldest first" method as the default method - you may find it's not what the client had in mind (meaning - if they want to apply the payment to a specific bill - they probably want to make to control WHICH bills this applies to).
The "picking which invoice applies" would be a gridview control or something like that (showing you multiple rows, etc...)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?