[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

G/L Procedure



Good morning!

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

Best regards,
Mark

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