In order to integrate LSMB into another business workflow application I've developed, I've written a stored procedure that can be called to insert GL transactions into the LSMB database instead of trying to use HTTP calls to the web based system. I know that Chris is starting to move to bindings for other languages; so, I don't know if this would be useful for everyone, or not. There's probably more work that could be done here. I only did some rudimentary error checking and I'm not terribly familiar with the schema. I attempted to do something similar for AR and AP as well, but I ran out of development time before I was able to get them working.
Anyway, here it is...
-- Function: gl__transaction_create(date, text, text, text, boolean, integer, text[], numeric[], numeric[], text[], text[])
-- DROP FUNCTION gl__transaction_create(date, text, text, text, boolean, integer, text[], numeric[], numeric[], text[], text[]);
CREATE OR REPLACE FUNCTION gl__transaction_create(trans_date date, ref text, description text, notes text, approved boolean, department_id integer, chart_id text[], debit numeric[], credit numeric[], source text[], memo text[])
RETURNS boolean AS
$BODY$
declare
array_size INTEGER;
array_size2 INTEGER;
trans_id INTEGER;
p_id INTEGER;
total NUMERIC;
marray RECORD;
chart RECORD;
begin
-- Check the sizes of the passed arrays to verify they are the same size.
array_size := 0;
for marray in select * from unnest(chart_id) loop
array_size := array_size + 1;
end loop;
array_size2 := 0;
for marray in select * from unnest(debit) loop
array_size2 := array_size2 + 1;
end loop;
if array_size <> array_size2 then
raise 'Arrays are not balanced.';
end if;
array_size2 := 0;
for marray in select * from unnest(credit) loop
array_size2 := array_size2 + 1;
end loop;
if array_size <> array_size2 then
raise 'Arrays are not balanced.';
end if;
array_size2 := 0;
for marray in select * from unnest(source) loop
array_size2 := array_size2 + 1;
end loop;
if array_size <> array_size2 then
raise 'Arrays are not balanced.';
end if;
array_size2 := 0;
for marray in select * from unnest(memo) loop
array_size2 := array_size2 + 1;
end loop;
if array_size <> array_size2 then
raise 'Arrays are not balanced.';
end if;
-- Be sure the transaction is balanced.
total := 0;
for marray in select unnest(debit) as d, unnest(credit) as c loop
total := total + marray.d - marray.c;
end loop;
if total <> 0 then
raise 'The transaction is not balanced. %', total;
end if;
-- Get a transaction ID.
select * into trans_id from nextval('id'::regclass);
-- Make an entry into the gl table.
insert into gl (id, reference, description, transdate, person_id, notes, approved, department_id)
values (trans_id::integer, ref, description, trans_date, (select id from users where username=SESSION_USER), notes,
approved, department_id);
-- Loop through arrays and make entries to acc_trans.
for marray in select unnest(chart_id) as cid, unnest(debit) as d, unnest(credit) as c, unnest(source) as s, unnest(memo) as m loop
begin
select * into strict chart from account__get_from_accno(marray.cid);
exception
when NO_DATA_FOUND then
raise 'Account % not found', marray.cid;
when TOO_MANY_ROWS then
raise 'Account % not unique.', marray.cid;
end;
total := marray.c - marray.d;
insert into acc_trans(trans_id, chart_id, amount, transdate, source, memo)
values(trans_id,
chart.id, total, trans_date, marray.s, marray.m);
end loop;
-- If transaction is approved, update the transactions table.
if approved then
if not draft_approve(trans_id) then
raise 'Unable to approve this transaction.';
end if;
end if;
return TRUE;
end;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION gl__transaction_create(date, text, text, text, boolean, integer, text[], numeric[], numeric[], text[], text[]) OWNER TO mpl;
GRANT EXECUTE ON FUNCTION gl__transaction_create(date, text, text, text, boolean, integer, text[], numeric[], numeric[], text[], text[]) TO public;
GRANT EXECUTE ON FUNCTION gl__transaction_create(date, text, text, text, boolean, integer, text[], numeric[], numeric[], text[], text[]) TO mpl;
GRANT EXECUTE ON FUNCTION gl__transaction_create(date, text, text, text, boolean, integer, text[], numeric[], numeric[], text[], text[]) TO lsmb_josephine_ledger__gl_voucher_create;
GRANT EXECUTE ON FUNCTION gl__transaction_create(date, text, text, text, boolean, integer, text[], numeric[], numeric[], text[], text[]) TO lsmb_josephine_ledger__gl_transaction_create;