Fwd: Fwd: Re: Display Invoices

Thanks.
Brian

Brian Wolf

Phone: 410.367.2958

Try out Activus Secure Payments™, our recurring payments application.

-------- Original Message --------

Subject:	Fwd: Re: [Ledger-smb-users] Display Invoices
Date:	Wed, 19 Jun 2013 17:40:34 -0400
From:	Brian Wolf <..hidden..>
Reply-To:	..hidden..
Organization:	Activus Technologies
To:	Ledger SMB Mailing List <..hidden..>

I've nearly completed a script to load sales invoices into LedgerSMB based on XML data from an external source. Many thanks to Erik.

One last piece of the puzzle: adding a payment.
Using store procedure (function) payment_post seems a good way to procedure, but a few of its many arguments are not clear to me. I can finish up this load script if someone can give me some guidance on the arguments. I've tried calling the function, but it errors saying that either the function does not exist (of course it does) or parameters are incorrect.

I'm assuming that an argument containing "op_" means overpayment. Since I'm not an accountant, I'm understanding what to supply in each argument. Below is the function signature:

function payment_post(
in_datepaid date,
in_account_class integer,
in_entity_credit_id integer,
in_curr character,
in_notes text,
in_department_id integer,
in_gl_description text,
in_cash_account_id integer[],
in_amount numeric[],
in_cash_approved boolean[],
in_source text[],
in_memo text[],
in_transaction_id integer[],
in_op_amount numeric[],
in_op_cash_account_id integer[],
in_op_source text[],
in_op_memo text[],
in_op_account_id integer[],
in_ovp_payment_id integer[],
in_approved boolean
)

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

-------- Original Message --------

Subject:	Re: [Ledger-smb-users] Display Invoices
Date:	Tue, 18 Jun 2013 20:08:04 -0400
From:	Brian Wolf <..hidden..>
Reply-To:	..hidden..
Organization:	Activus Technologies
To:	..hidden..

Just to confirm............

Using the LedgerSMB UI, I created a sales invoice containing two parts, and posted the invoice along with a payment. I can see five rows in the ACC_TRANS table. For each part, ACC_TRANS contains one row with account ID = 15 ('4010' Sales). There are two rows with account ID = 3 ('1200' AR), with offsetting amounts (i.e., one positive and one negative). The additional row has account ID = 1 ('1060' Checking), with a negative amount equal to the sum of the parts amounts.

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

On 06/18/2013 06:19 PM, Erik Huelsmann wrote:

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