Michael, Mmmm, I think that the government may well be interested in this method of billing! You should only produce an invoice ONCE and that should then be fixed whenever it is printed out or generated. The idea of having a "fluid changing" invoice rings accountancy warning bells and the auditing team would have a field day.
Also, as a check I would suggest you hold the Gross, Net and TAX totals as well as the amount paid within the invoice header record. This allows you to validate that the details records do in fact add up to the correct total held in the header - a good check of data consistency. When the invoice/payment has been generated, you then need to create a SALES LEDGER entry which is a simple one line entry into a file which can also contain purchase ledger entries: Date - Date of Transaction Period - Accounting Period eg 200706 Ledger - eg SAL (Sales), PUR (Purchase), GEN (General Ledger) Transaction type - eg INV Invoice, CRD Credit Note, CSH Cash, ADJ Adjustment etc. Reference - eg Invoice No, Credit note No Amount - Gross amount of the transaction Tax - Total Tax Amount In addition to this, hold a current balance figure against the Customer record which is updated once the invoice/payment has been produced. Once again the ability to cross check the system and endure that all current transactions make up the customer balance held on the customer record is a necessary cross check for data integrity. The sales ledger is what you need to produce statements and aged debt reports. Using this header/detail method with an associated ledger entry means that generating an "amount owed" figure becomes trivial as you only need to scan the Invoice headers in order to obtain Debtors/Creditors details. However, note that allocating payments against invoices (closed item) is not the only way of running a company and you may well find that many mid sized companies still run an "open item" system where payments are allocated against the "remaining unpaid balance" with no reference to an invoice or credit note. Hence individual items are never "cleared" only the overall balance is reduced. As for matching payments against invoices you can approach this in two ways. 1. Allocate against individual item lines 2. Allocate against an invoice Method (2) is certainly preferable as it easily allows under/under payments to be catered for as well as write-offs and early settlement discounts if required. In fact allocating against individual item lines becomes a real PAIN but, again is sometimes necessary. The set of Header/Detail data then caters for the Aged debt/Creditors reports as well as statements etc. Then I suggest creating a Ledger account (General Ledger I think you Americans call it) to deal with the Accountancy side of the business where Invoices, payments, Credit Notes and Adjustments etc. are posted into when required - as opposed to invoices being automatically updated into the accounts ledger as in many modern day systems. This approach really stems from my days in mainframes where batch posting was the norm. The aggravation in reversing "on line accounting" errors when bad postings are made is just not worth the hassle. Apart from that the accountants will really love the "batch total" mentality which gives a really good audit trail. This general ledger then handles all the accountancy features such as Trial Balance, Balance Sheet, Tax on a period by period basis which can be in a different period than the Purchase and Sales ledger - usually the accountants are 1 or even 2 periods behind the Sales/Purchase ledger especially at year end when the final year's accounts aren't closed for a number of weeks/months after the official year end. New "not yet posted" transactions are consequently flagged as "forward postings" Hope I haven't clouded the issue too much here and it is just off the top of my head so I might have missed a couple of bits out but if it gives you food for thought then that's good. There are many ways Dave Crozier -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of MB Software Solutions Sent: 18 June 2007 19:26 To: Profox Subject: Design question I've got the following tables: * transactions * invoice_header * invoice_details * payment_header * payment_details I create invoices based on the transactions that haven't been paid. "Paid" is defined as follows: sum(payment_details.namount) >= transaction.amount , where the sum is done on matching trans_id keys. I've got a field called "iPaid" in the invoice_header table that is set to 1 (true) when all of its detail records have been paid. This allows me to quickly query for all of the UNPAID invoices rather than scanning through and comparing the SUM(payment_details.namount) to transaction.namount every time. Eventually I'll put a trigger into the payment_details table to achieve this, but for now, it'll be in VFP bizobj/dataobj code every time a payment is applied. My question is this: say I create an invoice ("0001") for your unpaid transactions, and 30 days later, when it's time to invoice again, you haven't paid them yet. As the application developer, do you create a new invoice ("0002") for the current AND past charges, or just create an invoice for the current charges (so that you then have 2 invoices in either case), or do you simply open up the existing invoice, tack on the current charges, and thus keep the same ("0001") invoice? My concern about having a 2nd invoice is due to my having the iPaid field in the invoice_header table, as I described above. 2 invoices for the same transactions would prove to be a problem when reconciling the payment data. How would you proceed? tia! -- Michael J. Babcock, MCP MB Software Solutions, LLC http://mbsoftwaresolutions.com http://fabmate.com "Work smarter, not harder, with MBSS custom software solutions!" [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
