The ar.person_id column was set to
NULL when sales invoices were added to LedgerSMB
programmatically. Turns out stored procedure (function)
person__get_my_entity_id() was selecting against the users table
based on the value of SESSION_USER. But, when connected as the
postgres user, no rows were returned from the stored procedure.
I've come up with a work around, albeit not the most elegant
solution. Please find below, if it is helpful to you.
Brian
-- Function: person__get_my_entity_id()
-- DROP FUNCTION person__get_my_entity_id();
CREATE OR REPLACE FUNCTION person__get_my_entity_id()
RETURNS integer AS
$BODY$
SELECT entity_id
FROM users
WHERE username = SESSION_USER
UNION
SELECT A.entity_id
FROM
(SELECT users.entity_id
FROM users
join entity
on entity.id = users.entity_id
WHERE entity.entity_class = 3
ORDER BY users.id ASC LIMIT 1) A
WHERE NOT EXISTS ( SELECT entity_id FROM users WHERE username = SESSION_USER ) ;
$BODY$
LANGUAGE sql VOLATILE
COST 100;
ALTER FUNCTION person__get_my_entity_id()
OWNER TO postgres;
COMMENT ON FUNCTION person__get_my_entity_id() IS ' Returns the entity_id of the current, logged in user.';
On 06/18/2013 06:19 PM, Erik Huelsmann wrote:
Brian
-- Function: person__get_my_entity_id()
-- DROP FUNCTION person__get_my_entity_id();
CREATE OR REPLACE FUNCTION person__get_my_entity_id()
RETURNS integer AS
$BODY$
SELECT entity_id
FROM users
WHERE username = SESSION_USER
UNION
SELECT A.entity_id
FROM
(SELECT users.entity_id
FROM users
join entity
on entity.id = users.entity_id
WHERE entity.entity_class = 3
ORDER BY users.id ASC LIMIT 1) A
WHERE NOT EXISTS ( SELECT entity_id FROM users WHERE username = SESSION_USER ) ;
$BODY$
LANGUAGE sql VOLATILE
COST 100;
ALTER FUNCTION person__get_my_entity_id()
OWNER TO postgres;
COMMENT ON FUNCTION person__get_my_entity_id() IS ' Returns the entity_id of the current, logged in user.';
Brian Wolf
Phone: 410.367.2958
Email: ..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