• Matt Miller (2/15/2008)


    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...)

    Exactly, they will be able via a gridview control to pick which invoices they want to pay.

    And yes...all those other wonderful things you mentioned, promo's, write-off's etc.

    I *should* be able to do all of that in the PaymentsApplied table with a TransactionType column that would define what kind of transaction it was, payment, charge, discount, writeoff, etc. As long as it's tied back to invoice, that should let me reconcile things later correct?

    Christopher Ford