An update to my recent question:
The issue of payments still remains. Do we need to add a payment at all (since a payment is displayed in the invoice's payment section of the screen due to a row in ACC_TRANS)? If so, can you describe arguments to payment_post() ?
Thanks.
Brian
On 06/25/2013 10:01 AM, Brian Wolf wrote:
The negative amount was easily solved by, well, reversing the polarity of the amount going into 1060-Checking account (eg, from $15.99 to $-15.99).The issue now is payments. Interestingly, when viewing the resulting sales invoice, a payment is displayed in the payments section of the screen, but with a negative amount (eg, $-15.99 instead of $15.99). Does this mean that I don't have to call a stored procedure after all to make a payment? Why is it displayed as a negative amount?
The issue of payments still remains. Do we need to add a payment at all (since a payment is displayed in the invoice's payment section of the screen due to a row in ACC_TRANS)? If so, can you describe arguments to payment_post() ?
Thanks.
Brian
Brian Wolf
Phone: 410.367.2958
Email: ..hidden..
Try out Activus
Secure Payments™, our recurring payments
application.
Demo at http://demo.activustech.com
With lots of help from Erik Huelsmann (thanks!), we've got a working script which pulls sales invoice data (in XML format) from an external system and adds sales invoices to LedgerSMB. It adds rows to ACC_TRANS table, one for each item and one against for the total. Similarly, the script adds a row for each item to the INVOICE table. It also adds a row to AR and a row to TRANSACTIONS.
The issue now is payments. Interestingly, when viewing the resulting sales invoice, a payment is displayed in the payments section of the screen, but with a negative amount (eg, $-15.99 instead of $15.99). Does this mean that I don't have to call a stored procedure after all to make a payment? Why is it displayed as a negative amount?
Erik suggested the payment_post() stored procedure, but I'm having difficulty understanding all of its several arguments. Any help here would be very appreciated.
On 06/18/2013 06:19 PM, Erik Huelsmann wrote:
Thanks.
Brian
Brian WolfPhone: 410.367.2958Email: ..hidden..Try out Activus Secure Payments™, our recurring payments application.Demo at http://demo.activustech.com
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.Right. We pull XML data from another system, and then want to construct sales invoices in LedgerSMB programmatically.
>>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?)
>>1. Do your invoices all have a single line? If not, you will probably haveSome may have multiple lines.
>>multiple acc_trans lines: one for each invoice line plus one to post on the asset account (AR)
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.
------------------------------------------------------------------------------ This SF.net email is sponsored by Windows: Build for Windows Store. http://p.sf.net/sfu/windows-dev2dev
_______________________________________________ Ledger-smb-users mailing list ..hidden.. https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
------------------------------------------------------------------------------ This SF.net email is sponsored by Windows: Build for Windows Store. http://p.sf.net/sfu/windows-dev2dev
_______________________________________________ Ledger-smb-users mailing list ..hidden.. https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
