[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[6267] addons/1.3/wxPOS/setup
- Subject: SF.net SVN: ledger-smb:[6267] addons/1.3/wxPOS/setup
- From: ..hidden..
- Date: Fri, 8 Nov 2013 15:40:38 +0000
Revision: 6267
http://sourceforge.net/p/ledger-smb/code/6267
Author: einhverfr
Date: 2013-11-08 15:40:36 +0000 (Fri, 08 Nov 2013)
Log Message:
-----------
COGS/Invoice routines for AR and COGS routines for AP
Added Paths:
-----------
addons/1.3/wxPOS/setup/COGS.sql
addons/1.3/wxPOS/setup/invoices.sql
Copied: addons/1.3/wxPOS/setup/COGS.sql (from rev 6220, trunk/sql/modules/COGS.sql)
===================================================================
--- addons/1.3/wxPOS/setup/COGS.sql (rev 0)
+++ addons/1.3/wxPOS/setup/COGS.sql 2013-11-08 15:40:36 UTC (rev 6267)
@@ -0,0 +1,377 @@
+-- COGS routines for LedgerSMB 1.4.
+-- This file is licensed under the terms of the GNU General Public License
+-- Version 2 or at your option any later version.
+
+-- This module implements FIFO COGS. One could use it as a template to provide
+-- other forms of inventory valuation as well. With FIFO valuation, the best
+-- way I can see this is to suggest that all reversals only affect the AP rows,
+-- but all COGS amounts get posted to AR rows. This means that AR rows do not
+-- save the data here, but the AP transaction cogs calcuation alone does add to
+-- AR rows.
+
+-- FOR WXPOS USERS, ONLY LOAD THESE ON A 1.3 DATABASE.
+
+
+BEGIN;
+
+
+
+CREATE OR REPLACE FUNCTION cogs__reverse_ar(in_parts_id int, in_qty numeric)
+RETURNS NUMERIC[] AS
+$$
+DECLARE t_alloc numeric := 0;
+ t_cogs numeric := 0;
+ t_inv invoice;
+ t_avail numeric;
+BEGIN
+
+FOR t_inv IN
+ SELECT i.*
+ FROM invoice i
+ JOIN (select id, approved, transdate from ap
+ union
+ select id, approved, transdate from gl) a ON a.id = i.trans_id
+ WHERE allocated > 0 and a.approved and parts_id = in_parts_id
+ ORDER BY a.transdate DESC, a.id DESC, i.id DESC
+LOOP
+ t_avail := t_inv.allocated;
+ IF t_alloc < in_qty THEN
+ RAISE EXCEPTION 'TOO MANY ALLOCATED';
+ ELSIF t_alloc = in_qty THEN
+ RETURN ARRAY[t_alloc, t_cogs];
+ ELSIF (in_qty - t_alloc) * -1 <= t_inv.allocated THEN
+ raise notice 'partial reversal';
+ UPDATE invoice SET allocated = allocated + (in_qty - t_alloc)
+ WHERE id = t_inv.id;
+ t_cogs := t_cogs + (in_qty - t_alloc) * t_inv.sellprice;
+ return ARRAY[t_alloc + (in_qty - t_alloc), t_cogs];
+ ELSE
+ raise notice 'full reversal';
+ UPDATE invoice SET allocated = 0
+ WHERE id = t_inv.id;
+ t_alloc := t_alloc + t_inv.allocated * -1;
+ t_cogs := t_cogs + -1 * (t_inv.allocated) * t_inv.sellprice;
+ END IF;
+END LOOP;
+
+END;
+$$ LANGUAGE PLPGSQL;
+
+COMMENT ON FUNCTION cogs__reverse_ar(in_parts_id int, in_qty numeric) IS
+$$This function accepts a part id and quantity to reverse. It then iterates
+backwards over AP related records, calculating COGS. This does not save COGS
+but rather returns it to the application to save.
+
+Return values are an array of {allocated, cogs}.
+$$;
+
+CREATE OR REPLACE FUNCTION cogs__add_for_ar(in_parts_id int, in_qty numeric)
+returns numeric[] AS
+$$
+DECLARE t_alloc numeric := 0;
+ t_cogs numeric := 0;
+ t_inv invoice;
+ t_avail numeric;
+BEGIN
+
+
+FOR t_inv IN
+ SELECT i.*
+ FROM invoice i
+ JOIN (select id, approved, transdate from ap
+ union
+ select id, approved, transdate from gl) a ON a.id = i.trans_id
+ WHERE qty + allocated < 0 AND i.parts_id = in_parts_id
+ ORDER BY a.transdate asc, a.id asc, i.id asc
+LOOP
+ t_avail := (t_inv.qty + t_inv.allocated) * -1;
+ IF t_alloc > in_qty THEN
+ RAISE EXCEPTION 'TOO MANY ALLOCATED';
+ ELSIF t_alloc = in_qty THEN
+ return ARRAY[t_alloc, t_cogs];
+ ELSIF (in_qty + t_alloc) <= t_avail THEN
+ UPDATE invoice SET allocated = allocated + (in_qty - t_alloc)
+ WHERE id = t_inv.id;
+ t_cogs := t_cogs + (in_qty - t_alloc) * t_inv.sellprice;
+ t_alloc := in_qty;
+ return ARRAY[t_alloc, t_cogs];
+ ELSE
+ UPDATE invoice SET allocated = qty * -1
+ WHERE id = t_inv.id;
+ t_cogs := t_cogs + (t_avail * t_inv.sellprice);
+ t_alloc := t_alloc + t_avail;
+ END IF;
+END LOOP;
+
+RETURN ARRAY[t_alloc, t_cogs];
+
+END;
+$$ LANGUAGE PLPGSQL;
+
+COMMENT ON FUNCTION cogs__add_for_ar(in_parts_id int, in_qty numeric) IS
+$$ This function accepts a parts_id and a quantity, and iterates through AP
+records in order, calculating COGS on a FIFO basis and returning it to the
+application to attach to the current transaction.
+
+Return values are an array of {allocated, cogs}.
+$$;
+
+CREATE OR REPLACE FUNCTION cogs__reverse_ap
+(in_parts_id int, in_qty numeric) RETURNS numeric[] AS
+$$
+DECLARE t_alloc numeric :=0;
+ t_inv invoice;
+ t_cogs numeric :=0;
+ retval numeric[];
+BEGIN
+RAISE NOTICE 'reversing AP: parts_id %, qty %', in_parts_id, in_qty;
+
+FOR t_inv IN
+ SELECT i.*
+ FROM invoice i
+ JOIN ap a ON a.id = i.trans_id
+ WHERE qty + allocated < 0 AND parts_id = in_parts_id
+ ORDER BY a.transdate, a.id, i.id
+LOOP
+ RAISE NOTICE 'id %, avail %, allocated %, requesting %', t_inv.id, t_inv.qty + t_inv.allocated, t_alloc, in_qty - t_alloc;
+ IF t_alloc > in_qty THEN
+ RAISE EXCEPTION 'TOO MANY ALLOCATED';
+ ELSIF t_alloc = in_qty THEN
+ return ARRAY[t_alloc, t_cogs];
+ ELSIF (in_qty - t_alloc) <= -1 * (t_inv.qty + t_inv.allocated) THEN
+ raise notice 'partial reversal';
+ UPDATE invoice SET allocated = allocated + (in_qty - t_alloc)
+ WHERE id = t_inv.id;
+ return ARRAY[in_qty * -1, t_cogs + (in_qty - t_alloc) * t_inv.sellprice];
+ ELSE
+ raise notice 'total reversal';
+ UPDATE invoice SET allocated = qty * -1
+ WHERE id = t_inv.id;
+ t_alloc := t_alloc - (t_inv.qty + t_inv.allocated);
+ t_cogs := t_cogs - (t_inv.qty + t_inv.allocated) * t_inv.sellprice;
+ END IF;
+END LOOP;
+
+RETURN ARRAY[t_alloc, t_cogs];
+
+RAISE EXCEPTION 'TOO FEW TO ALLOCATE';
+END;
+$$ LANGUAGE PLPGSQL;
+
+COMMENT ON FUNCTION cogs__reverse_ap (in_parts_id int, in_qty numeric) IS
+$$ This function iterates through invoice rows attached to ap transactions and
+allocates them on a first-in first-out basis. The sort of pseudo-"COGS" value
+is returned to the application for further handling.$$;
+
+-- Not concerned about performance on the function below. It is possible that
+-- large AP purchases which add COGS to a lot of AR transactions could pose
+-- perforance problems but this is a rare case and so we can worry about tuning
+-- that if someone actually needs it. --CT
+
+CREATE OR REPLACE FUNCTION cogs__add_for_ap
+(in_parts_id int, in_qty numeric, in_lastcost numeric)
+returns numeric AS
+$$
+DECLARE t_alloc numeric := 0;
+ t_cogs numeric := 0;
+ t_inv invoice;
+ t_cp account_checkpoint;
+ t_ar ar;
+ t_avail numeric;
+BEGIN
+
+
+IF in_qty > 0 THEN
+ return cogs__reverse_ap(in_parts_id, in_qty * -1) * in_lastcost;
+END IF;
+
+SELECT * INTO t_cp FROM account_checkpoint ORDER BY end_date DESC LIMIT 1;
+
+FOR t_inv IN
+ SELECT i.*
+ FROM invoice i
+ JOIN ar a ON a.id = i.trans_id
+ WHERE qty + allocated > 0 and parts_id = in_parts_id
+ ORDER BY a.transdate, a.id, i.id
+LOOP
+ t_avail := t_inv.qty + t_inv.allocated;
+ SELECT * INTO t_ar FROM ar WHERE id = t_inv.trans_id;
+ IF t_alloc < in_qty THEN
+ RAISE EXCEPTION 'TOO MANY ALLOCATED';
+ ELSIF t_alloc = in_qty THEN
+ return t_alloc;
+ ELSIF (in_qty + t_alloc) * -1 <= t_avail THEN
+ UPDATE invoice SET allocated = allocated + (in_qty + t_alloc)
+ WHERE id = t_inv.id;
+
+ INSERT INTO acc_trans
+ (chart_id, transdate, amount, invoice_id, approved, trans_id)
+ SELECT expense_accno_id,
+ CASE WHEN t_ar.transdate > t_cp.end_date THEN t_ar.transdate
+ ELSE t_cp.end_date + '1 day'::interval
+ END, (in_qty + t_alloc) * in_lastcost, t_inv.id, true,
+ t_inv.trans_id
+ FROM parts
+ WHERE id = t_inv.parts_id AND inventory_accno_id IS NOT NULL
+ AND expense_accno_id IS NOT NULL
+ UNION
+ SELECT income_accno_id,
+ CASE WHEN t_ar.transdate > t_cp.end_date THEN t_ar.transdate
+ ELSE t_cp.end_date + '1 day'::interval
+ END, -1 * (in_qty + t_alloc) * in_lastcost, t_inv.id, true,
+ t_inv.trans_id
+ FROM parts
+ WHERE id = t_inv.parts_id AND inventory_accno_id IS NOT NULL
+ AND expense_accno_id IS NOT NULL;
+
+ t_cogs := t_cogs + (in_qty + t_alloc) * in_lastcost;
+ return in_qty * -1;
+ ELSE
+ UPDATE invoice SET allocated = qty * -1
+ WHERE id = t_inv.id;
+ t_cogs := t_cogs + t_avail * in_lastcost;
+
+ INSERT INTO acc_trans
+ (chart_id, transdate, amount, invoice_id, approved, trans_id)
+ SELECT expense_accno_id,
+ CASE WHEN t_ar.transdate > t_cp.end_date THEN t_ar.transdate
+ ELSE t_cp.end_date + '1 day'::interval
+ END, -1 * t_avail * in_lastcost,
+ t_inv.id, true, t_inv.trans_id
+ FROM parts
+ WHERE id = t_inv.parts_id AND inventory_accno_id IS NOT NULL
+ AND expense_accno_id IS NOT NULL
+ UNION
+ SELECT income_accno_id,
+ CASE WHEN t_ar.transdate > t_cp.end_date THEN t_ar.transdate
+ ELSE t_cp.end_date + '1 day'::interval
+ END, -t_avail * in_lastcost, t_inv.id, true, t_inv.trans_id
+ FROM parts
+ WHERE id = t_inv.parts_id AND inventory_accno_id IS NOT NULL
+ AND expense_accno_id IS NOT NULL;
+ t_alloc := t_alloc + t_avail;
+ t_cogs := t_cogs + t_avail * in_lastcost;
+ END IF;
+
+
+END LOOP;
+
+RETURN t_alloc;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION cogs__add_for_ar_line(in_invoice_id int)
+RETURNS numeric AS
+$$
+DECLARE
+ t_cogs numeric[];
+ t_inv invoice;
+ t_part parts;
+ t_ar ar;
+ t_transdate date;
+BEGIN
+
+SELECT * INTO t_inv FROM invoice WHERE id = in_invoice_id;
+SELECT * INTO t_part FROM parts WHERE id = t_inv.parts_id;
+SELECT * INTO t_ar FROM ar WHERE id = t_inv.trans_id;
+
+IF t_part.inventory_accno_id IS NULL THEN
+ RETURN 0;
+END IF;
+
+IF t_inv.qty + t_inv.allocated = 0 THEN
+ return 0;
+END IF;
+
+IF t_inv.qty > 0 THEN
+ t_cogs := cogs__add_for_ar(t_inv.parts_id, t_inv.qty + t_inv.allocated);
+ELSE
+ t_cogs := cogs__reverse_ar(t_inv.parts_id, t_inv.qty + t_inv.allocated);
+END IF;
+
+
+UPDATE invoice set allocated = allocated - t_cogs[1]
+ WHERE id = in_invoice_id;
+
+SELECT CASE WHEN t_ar.transdate > max(end_date) THEN t_ar.transdate
+ ELSE max(end_date) + '1 day'::interval
+ END INTO t_transdate
+ from account_checkpoint td;
+INSERT INTO acc_trans
+ (trans_id, chart_id, approved, amount, transdate, invoice_id)
+VALUES (t_inv.trans_id, CASE WHEN t_inv.qty < 0 AND t_ar.is_return
+ THEN t_part.returns_accno_id
+ ELSE t_part.expense_accno_id
+ END, TRUE, t_cogs[2] * -1, t_transdate, t_inv.id),
+ (t_inv.trans_id, t_part.inventory_accno_id, TRUE, t_cogs[2],
+ t_transdate, t_inv.id);
+
+RETURN t_cogs[1];
+
+END;
+
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION cogs__add_for_ap_line(in_invoice_id int)
+RETURNS numeric AS
+$$
+DECLARE retval numeric;
+ r_cogs numeric[];
+ t_inv invoice;
+ t_adj numeric;
+ t_ap ap;
+BEGIN
+
+SELECT * INTO t_inv FROM invoice
+ WHERE id = in_invoice_id;
+
+IF t_inv.qty + t_inv.allocated = 0 THEN
+ return 0;
+END IF;
+
+SELECT * INTO t_ap FROM ap WHERE id = t_inv.trans_id;
+
+IF t_inv.qty < 0 THEN -- normal COGS
+
+ SELECT cogs__add_for_ap(i.parts_id, i.qty + i.allocated, i.sellprice)
+ INTO retval
+ FROM invoice i
+ JOIN parts p ON p.id = i.parts_id
+ WHERE i.id = $1;
+
+ UPDATE invoice
+ SET allocated = allocated + retval
+ WHERE id = $1;
+ELSE -- reversal
+
+ r_cogs := cogs__reverse_ap(t_inv.parts_id, t_inv.qty + t_inv.allocated);
+
+ UPDATE invoice
+ SET allocated = allocated + r_cogs[1]
+ WHERE id = in_invoice_id;
+
+ t_adj := t_inv.sellprice * r_cogs[1] + r_cogs[2];
+
+ INSERT INTO acc_trans
+ (chart_id, trans_id, approved, amount, transdate, invoice_id)
+ SELECT p.inventory_accno_id, t_inv.trans_id, true, t_adj, t_ap.transdate,
+ in_invoice_id
+ FROM parts p
+ WHERE id = t_inv.parts_id
+ UNION
+ SELECT p.expense_accno_id, t_inv.trans_id, true, t_adj * -1, t_ap.transdate,
+ in_invoice_id
+ FROM parts p
+ WHERE id = t_inv.parts_id;
+ retval := r_cogs[1];
+ raise notice 'cogs reversal returned %', r_cogs;
+
+END IF;
+
+RETURN retval;
+
+END;
+
+$$ LANGUAGE PLPGSQL;
+
+COMMIT;
Added: addons/1.3/wxPOS/setup/invoices.sql
===================================================================
--- addons/1.3/wxPOS/setup/invoices.sql (rev 0)
+++ addons/1.3/wxPOS/setup/invoices.sql 2013-11-08 15:40:36 UTC (rev 6267)
@@ -0,0 +1,115 @@
+BEGIN;
+
+CREATE OR REPLACE FUNCTION invoice__start_ar
+(in_invnumber text, in_transdate date, in_taxincluded bool,
+ in_amount numeric, in_netamount numeric, in_paid numeric, in_datepaid numeric,
+ in_duedate date, in_invoice bool, in_curr char(3), person_id int,
+ in_till varchar(20), in_department_id int, in_approved bool,
+ in_entity_credit_account int, in_ar_accno text)
+RETURNS int LANGUAGE SQL AS
+$$
+ INSERT INTO ar
+ (invnumber, transdate, taxincluded,
+ amount, netamount, paid, datepaid
+ duedate, invoice, curr, person_id,
+ till, department_id, approved, entity_credit_account)
+ VALUES ($1, $2, coalesce($3, 'f'),
+ $4,$5, $6, coalesce($7, 'today'),
+ coalesce($8, 'today'), $9, coalesce($10, defaults_get_defaultcurrency()),
+ coalesce($11, person__get_my_entity_id()),
+ $12, $13, coalesce($14, true), $15);
+
+ INSERT INTO acc_trans
+ (trans_id, transdate, chart_id, amount, approved)
+ SELECT currval('id')::int, $2, a.id, $4, true
+ FROM account WHERE accno = $16;
+
+ SELECT currval('id')::int;
+$$;
+
+COMMENT ON invoice__start_ar
+(in_invnumber text, in_transdate date, in_taxincluded bool,
+ in_amount numeric, in_netamount numeric, in_paid numeric, in_datepaid numeric,
+ in_duedate date, in_invoice bool, in_curr char(3), person_id int,
+ in_till varchar(20), in_department_id int, in_approved bool,
+ in_entity_credit_account int, in_ar_accno text)
+IS $$ Saves an ar transaction header. The following fields are optional:
+
+1. in_tax_included, defaults to false
+
+2. in_datepaid, defaults to 'today'
+
+3. in_duedate defaults to 'today',
+
+4. in_person_id defaults to the entity id of the current user.
+
+5. in_curr defaults to the default currency.
+
+All other fields are mandatory.
+
+Returns true on success, raises exception on failure.
+
+$$;
+
+CREATE OR REPLACE FUNCTION invoice__add_item_ar
+(in_id int, in_parts_id int, in_qty int, in_discount numeric,
+ in_unit text)
+RETURNS BOOL LANGUAGE SQL AS
+$$
+INSERT INTO invoice(trans_id, parts_id, qty, discount, unit, allocated)
+SELECT$1, p.id, $3, $4, calesce($5), 0
+ FROM parts WHERE id = $2;
+
+SELECT TRUE;
+$$;
+
+COMMENT ON FUNCTION invoice__add_item_ar
+(in_id int, in_parts_id int, in_qty int, in_discount numeric,
+ in_unit text)
+IS $$This adds an item to the invoice. This is not safe to use alone. If you
+use it, you MUST also use invoice__finalize_ar. In particular this function does
+not add income, inventory, or COGS calculations.
+
+CREATE OR REPLACE FUNCTION invoice__add_payment_ar
+(in_id int, in_ar_accno text, in_cash_accno text, in_transdate date,
+in_source text, in_memo text, in_amount numeric)
+RETURNS BOOL LANGUAGE SQL AS
+$$
+INSERT INTO acc_trans (trans_id, chart_id, transdate, source, memo, amount,
+ approved)
+VALUES ($1, $2, coalesce($4, 'today'), $5, $6, $7, true),
+ ($1, $3, coalesce($4, 'today'), $5, $6, $7 * -1, true);
+$$;
+
+CREATE OR REPLACE FUNCTION invoice__finalize_ar(in_id int)
+returns bool language plpgsql as
+$$
+BEGIN
+ -- income
+ INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, approved,
+ invoice_id)
+ SELECT in_id, p.income_accno_id, a.transdate, i.qty * i.sellprice, true, i.id
+ FROM parts p
+ JOIN invoice i ON i.parts_id = p.id
+ JOIN ar ON i.trans_id = ar.id AND ar.id = in_id;
+
+ -- transaction should now be balanced if this was done with invoice__begin_ar
+ -- add cogs
+ PERFORM cogs__add_for_ar(parts_id, qty)
+ FROM invoice WHERE trans_id = in_id;
+
+ -- check if transaction is balanced, else raise exception
+ PERFORM trans_id FROM acc_trans
+ WHERE trans_id = in_id
+ GROUP BY trans_id
+ HAVING sum(amount) <> 0;
+
+ IF FOUND THEN
+ RAISE EXCEPTION 'Out of balance';
+ END IF;
+
+ RETURN TRUE;
+END;
+$$;
+
+COMMIT;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
------------------------------------------------------------------------------
November Webinars for C, C++, Fortran Developers
Accelerate application performance with scalable programming models. Explore
techniques for threading, error checking, porting, and tuning. Get the most
from the latest Intel processors and coprocessors. See abstracts and register
http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-commits mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-commits