[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4809] trunk/sql/modules
- Subject: SF.net SVN: ledger-smb:[4809] trunk/sql/modules
- From: ..hidden..
- Date: Thu, 31 May 2012 06:34:56 +0000
Revision: 4809
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4809&view=rev
Author: einhverfr
Date: 2012-05-31 06:34:56 +0000 (Thu, 31 May 2012)
Log Message:
-----------
AP-before-AR COGS tests now pass as well.
Modified Paths:
--------------
trunk/sql/modules/COGS.sql
trunk/sql/modules/test/COGS-FIFO.sql
Modified: trunk/sql/modules/COGS.sql
===================================================================
--- trunk/sql/modules/COGS.sql 2012-05-31 02:35:50 UTC (rev 4808)
+++ trunk/sql/modules/COGS.sql 2012-05-31 06:34:56 UTC (rev 4809)
@@ -13,8 +13,9 @@
BEGIN;
+
CREATE OR REPLACE FUNCTION cogs__reverse_ar(in_parts_id int, in_qty numeric)
-RETURNS NUMERIC AS
+RETURNS NUMERIC[] AS
$$
DECLARE t_alloc numeric := 0;
t_cogs numeric := 0;
@@ -34,12 +35,12 @@
IF t_alloc > qty THEN
RAISE EXCEPTION 'TOO MANY ALLOCATED';
ELSIF t_alloc = in_qty THEN
- RETURN t_cogs;
+ RETURN ARRAY[t_alloc, t_cogs];
ELSIF (in_qty - t_alloc) <= -1 * (t.qty + t_inv.allocated) 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;
- return t_cogs;
+ return ARRAY[t_alloc, t_cogs];
ELSE
UPDATE invoice SET allocated = 0
WHERE id = t_inv.id;
@@ -56,14 +57,18 @@
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.$$;
+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
+returns numeric[] AS
$$
DECLARE t_alloc numeric := 0;
t_cogs numeric := 0;
t_inv invoice;
+ t_avail numeric;
BEGIN
RAISE NOTICE 'adding for ar';
@@ -75,27 +80,34 @@
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, a.id, i.id
+ ORDER BY a.transdate asc, a.id asc, i.id asc
LOOP
- RAISE NOTICE 'id: %, qty: %, allocated: %', t_inv.id, t_inv.qty, t_inv.allocated;
+ t_avail := (t_inv.qty + t_inv.allocated) * -1;
+ RAISE NOTICE 'id: %, qty: %, allocated: %, requested %, needed %, avail %, cogs so far %',
+ t_inv.id, t_inv.qty, t_inv.allocated, in_qty,
+ in_qty + t_alloc, t_avail, t_cogs;
IF t_alloc > in_qty THEN
RAISE EXCEPTION 'TOO MANY ALLOCATED';
ELSIF t_alloc = in_qty THEN
- return t_cogs;
- ELSIF (in_qty - t_alloc) <= -1 * (t_inv.qty + t_inv.allocated) THEN
+ return ARRAY[t_alloc, t_cogs];
+ ELSIF (in_qty + t_alloc) <= t_avail THEN
+ RAISE NOTICE 'partial allocation: % @ % + %', in_qty - t_alloc, t_inv.sellprice, t_cogs;
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 t_cogs;
+ t_alloc := in_qty;
+ RAISE NOTICE 'cogs %, allocated %, left %', t_cogs, t_alloc, in_qty - t_alloc;
+ return ARRAY[t_alloc, t_cogs];
ELSE
+ RAISE NOTICE 'full allocation';
UPDATE invoice SET allocated = qty * -1
WHERE id = t_inv.id;
- t_cogs := t_cogs + -1 * (t_inv.qty + t_inv.allocated) * t_inv.sellprice;
- t_alloc := t_alloc + -1 + (t_inv.qty + t_inv.allocated);
+ t_cogs := t_cogs + (t_avail * t_inv.sellprice);
+ t_alloc := t_alloc + t_avail;
END IF;
END LOOP;
-RETURN t_cogs;
+RETURN ARRAY[t_alloc, t_cogs];
END;
$$ LANGUAGE PLPGSQL;
@@ -103,8 +115,11 @@
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.$$;
+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
$$
@@ -190,7 +205,7 @@
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 * (in_qty + t_alloc) * in_lastcost, t_inv.id, true,
+ 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
@@ -199,7 +214,7 @@
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, (in_qty + t_alloc) * in_lastcost, t_inv.id, true,
+ 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
@@ -217,7 +232,7 @@
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, t_avail * in_lastcost,
+ 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
@@ -226,7 +241,7 @@
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 * t_avail * in_lastcost, t_inv.id, true, t_inv.trans_id
+ 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;
@@ -245,7 +260,7 @@
RETURNS numeric AS
$$
DECLARE
- t_cogs numeric;
+ t_cogs numeric[];
t_inv invoice;
t_part parts;
t_ar ar;
@@ -258,7 +273,11 @@
INTO t_cogs
FROM invoice WHERE id = in_invoice_id;
+RAISE NOTICE 'cogs function returned %', t_cogs;
+UPDATE invoice set allocated = allocated - t_cogs[1]
+ WHERE id = in_invoice_id;
+
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;
@@ -272,11 +291,11 @@
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 * -1, t_transdate, t_inv.id),
- (t_inv.trans_id, t_part.inventory_accno_id, TRUE, t_cogs,
+ 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;
+RETURN t_cogs[1];
END;
Modified: trunk/sql/modules/test/COGS-FIFO.sql
===================================================================
--- trunk/sql/modules/test/COGS-FIFO.sql 2012-05-31 02:35:50 UTC (rev 4808)
+++ trunk/sql/modules/test/COGS-FIFO.sql 2012-05-31 06:34:56 UTC (rev 4809)
@@ -64,7 +64,7 @@
SELECT cogs__add_for_ap_line(-1202);
INSERT INTO test_result (test_name, success)
-SELECT 'post-ap-run COGS is 37.50, (invoice 1, series 1)', sum(amount) = 37.5
+SELECT 'post-ap-run COGS is 37.50, (invoice 1, series 1)', sum(amount) = -37.5
from acc_trans
where trans_id = -1201 and chart_id = -1102;
@@ -102,7 +102,8 @@
--duplicate to check against reversals
INSERT INTO test_result (test_name, success)
-SELECT 'post-ap-run COGS still 37.50, (invoice 1, series 1)', sum(amount) = 37.5
+SELECT 'post-ap-run COGS still 37.50, (invoice 1, series 1)',
+ sum(amount) = -37.5
from acc_trans
where trans_id = -1201 and chart_id = -1102;
@@ -136,12 +137,12 @@
FROM invoice WHERE id = -1204;
INSERT INTO test_result (test_name, success)
-SELECT 'post-ap-run COGS is 62.50, (invoice 1, series 1)', sum(amount) = 62.5
+SELECT 'post-ap-run COGS is 62.50, (invoice 1, series 1)', sum(amount) = -62.5
from acc_trans
where trans_id = -1201 and chart_id = -1102;
INSERT INTO test_result (test_name, success)
-SELECT 'post-ap-run COGS is 50, (invoice 2, series 1)', sum(amount) = 50
+SELECT 'post-ap-run COGS is 50, (invoice 2, series 1)', sum(amount) = -50
from acc_trans
where trans_id = -1203 and chart_id = -1102;
@@ -151,7 +152,6 @@
INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
VALUES (-1205, -1205, -1, -50, 0, 2);
-SELECT parts_id, qty, allocated, trans_id, id FROM invoice WHERE parts_id < 0;
SELECT cogs__add_for_ap_line(-1205);
INSERT INTO test_result(test_name, success)
@@ -176,7 +176,7 @@
FROM invoice WHERE id = -1205;
INSERT INTO test_result (test_name, success)
-SELECT 'post-ap-run COGS is 62.50, (invoice 1, series 1)', sum(amount) = 62.5
+SELECT 'post-ap-run COGS is 62.50, (invoice 1, series 1)', sum(amount) = -62.5
from acc_trans
where trans_id = -1201 and chart_id = -1102;
@@ -186,18 +186,152 @@
INSERT INTO test_result (test_name, success)
-SELECT 'post-ap-run COGS is 150, (invoice 2, series 1)', sum(amount) = 150
+SELECT 'post-ap-run COGS is 150, (invoice 2, series 1)', sum(amount) = -150
from acc_trans
where trans_id = -1203 and chart_id = -1102;
-- Series 2, AP invoices first, cogs only added to AR
+INSERT INTO ap (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-2201, true, 'test2001', now() - '10 days'::interval, -1000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-2201, -2201, -2, -100, 0, 0.5);
+SELECT cogs__add_for_ap_line(-2201);
+INSERT INTO test_result(test_name, success)
+SELECT 'Allocated is 0 post-AP run (invoice 1 series 2)', allocated = 0
+ FROM invoice WHERE id = -2201;
+
+INSERT INTO ar (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-2202, true, 'test2001', now() - '10 days'::interval, -2000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-2202, -2202, -2, 75, 0, 3);
+
+SELECT cogs__add_for_ar_line(-2202);
+
+INSERT INTO test_result(test_name, success)
+SELECT 'Allocated is 75 post-AR run (invoice 1 series 2)', allocated = 75
+ FROM invoice WHERE id = -2201;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'Allocated is 75 post-AR run (invoice 2 series 2)', allocated = -75
+ FROM invoice WHERE id = -2202;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-run COGS is 37.50, (invoice 2, series 2)', sum(amount) = -37.5
+from acc_trans
+ where trans_id = -2202 and chart_id = -2102;
+
+INSERT INTO ap (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-2203, true, 'test2003', now() - '9 days'::interval, -1000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-2203, -2203, -2, -100, 0, 1);
+
+SELECT cogs__add_for_ap_line(-2203);
+
+INSERT INTO test_result(test_name, success)
+SELECT 'Allocated is 75 post-AP run (invoice 1 series 2)', allocated = 75
+ FROM invoice WHERE id = -2201;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'Allocated is 75 post-AP run (invoice 2 series 2)', allocated = -75
+ FROM invoice WHERE id = -2202;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'Allocated is 0 post-AP run (invoice 3 series 2)', allocated = 0
+ FROM invoice WHERE id = -2203;
+
+INSERT INTO ar (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-2204, true, 'test2004', now() - '7 days'::interval, -2000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-2204, -2204, -2, 75, 0, 3);
+
+SELECT cogs__add_for_ar_line(-2204);
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-4, allocation invoice 1 series 2 is 100', allocated = 100
+ FROM invoice WHERE id = -2201;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-4, allocation invoice 2 series 2 is 75', allocated = -75
+ FROM invoice WHERE id = -2202;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-4, allocation invoice 3 series 2 is 50', allocated = 50
+ FROM invoice WHERE id = -2203;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-4, allocation invoice 4 series 2 is 75', allocated = -75
+ FROM invoice WHERE id = -2204;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-4 COGS is 37.50, (invoice 2, series 2)', sum(amount) = -37.5
+from acc_trans
+ where trans_id = -2202 and chart_id = -2102;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-4 COGS is 62.50, (invoice 2, series 4)', sum(amount) = -62.5
+from acc_trans
+ where trans_id = -2204 and chart_id = -2102;
+
+INSERT INTO ar (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-2205, true, 'test2005', now() - '5 days'::interval, -2000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-2205, -2205, -2, 75, 0, 3);
+
+SELECT cogs__add_for_ar_line(-2205);
+
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-5, allocation invoice 1 series 2 is 100', allocated = 100
+ FROM invoice WHERE id = -2201;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-5, allocation invoice 2 series 2 is 75', allocated = -75
+ FROM invoice WHERE id = -2202;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-5, allocation invoice 3 series 2 is 100', allocated = 100
+ FROM invoice WHERE id = -2203;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-5, allocation invoice 4 series 2 is 75', allocated = -75
+ FROM invoice WHERE id = -2204;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-5, allocation invoice 5 series 2 is 50', allocated = -50
+ FROM invoice WHERE id = -2205;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-5 COGS is 37.50, (invoice 2, series 2)', sum(amount) = -37.5
+from acc_trans
+ where trans_id = -2202 and chart_id = -2102;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-4 COGS is 62.50, (invoice 2, series 4)', sum(amount) = -62.5
+from acc_trans
+ where trans_id = -2204 and chart_id = -2102;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-4 COGS is 50, (invoice 2, series 5)', sum(amount) = -50
+from acc_trans
+ where trans_id = -2205 and chart_id = -2102;
+
+-- Series 2.5, AR reversal
-- Series 3, Mixed
-- Series 4, AP Reversal
+-- finalization
+SELECT sum(amount) as balance, chart_id, trans_id from acc_trans
+ WHERE trans_id < -1000
+GROUP BY chart_id, trans_id;
+
+SELECT id, parts_id, qty, allocated, sellprice from invoice
+ WHERE trans_id < -1000;
+
+
SELECT * FROM test_result;
SELECT (select count(*) from test_result where success is true)
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.