Re: Display Invoices

Hi Brian,

On Tue, Jun 18, 2013 at 11:32 PM, Brian Wolf <..hidden..> wrote:

Erik,

Thank you for writing. Your insights are a great help, particularly with my project due date approaching.

>>I've now come to understand that my previous assumption is incorrect, right? (In other words:
>>you're completely handling invoices within LSMB, just not manually?)

Right. We pull XML data from another system, and then want to construct sales invoices in LedgerSMB programmatically.

>>1. Do your invoices all have a single line? If not, you will probably have
>>multiple acc_trans lines: one for each invoice line plus one to post on the asset account (AR)

Some may have multiple lines.

Ok. This is how the database structure works: for every part you're entering an invoice for (which may be a good or a service), you create a line in the INVOICE table. The line in the INVOICE table references a line in the PARTS table. The line in the PARTS table has income and expense accounts associated. Since we're talking AR here, you'll have to use the income account ID.

Now, for each line in the INVOICE table, you create a line in the ACC_TRANS table. The ACC_TRANS table references the related INVOICE table line through its invoice_id field. From the PARTS table, you use the income account ID to fill the account_id field.

Then, at the end, you calculate the total amount posted on the income accounts, negate that and add a line to ACC_TRANS which has its account_id set to the AR account.

All the ACC_TRANS and INVOICE lines have a trans_id field, which references TRANSACTIONS(id). You can get such an ID by inserting a single new line into the AR table. So, all lines above actually reference the AR.ID value you get from inserting that line.

>>2. Do you use taxes? If so, you will probably have another line per tax %

Right now there aren't taxes. But, laws may change requiring charging taxes online.

Ok. Well, I'd start with a variant which doesn't do taxes. That's easier. Once that's up and running, I'd add taxes later. Taxes are difficult in the sense that you need to take into account that certain taxes may not be applicable to certain parts/services and that some customers may be tax exempt. And then there's the taxation module which allows one or several taxes to be applied to a single part/customer combination. As I said, I want to explain it, but it's probably shooting a bit far for right now.

>>3. Do you use foreign currencies? If so, you need a number of extra "fx" lines in acc_trans.
>>although I'm not exactly sure how fx is supposed to work (I have hacked it, but I find it rather unintuitive,
>>so I keep having to look it up again)

Not using foreign currencies at this point, but good chance we will use them going forward.
I appreciate your taking a look at it; I didn't follow fx at all.

Ok. I can look those up later this week and document how they work. These are far less complex than taxes, especially if you let payments be handled by the sprocs.

>> However, on the side of payments, you need to update the payments table as well. The best way to do that would be to use the >>Payment.sql:payment_bulk_post or Payment.sql:payment_post stored procedures. They do some complex FX calculations if required >>and keep the tables referentially in tact and update the required fields in multiple tables.

Yes, payment is a missing piece. I'll take a look at the stored procedures.

The payment_post sproc corresponds to the single-payment interface of Cash->Receipt/Payment and the payment_bulk_post sproc corresponds to the interface of Cash->Voucher->Receipts/Payments. That should help decide whichever you want.

Bye,

Erik.

http://efficito.com -- Hosted accounting and ERP.

Robust and Flexible. No vendor lock-in.