SF.net SVN: ledger-smb:[4810] trunk/sql/modules

[..hidden..]

Revision: 4810
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4810&view=rev
Author:   einhverfr
Date:     2012-05-31 07:19:52 +0000 (Thu, 31 May 2012)
Log Message:
-----------
AR Reversal tests passing

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 06:34:56 UTC (rev 4809)
+++ trunk/sql/modules/COGS.sql	2012-05-31 07:19:52 UTC (rev 4810)
@@ -20,6 +20,7 @@
 DECLARE t_alloc numeric := 0;
         t_cogs numeric := 0;
         t_inv invoice;
+        t_avail numeric;
 BEGIN
 
 RAISE NOTICE 'reversing ar';
@@ -29,23 +30,26 @@
       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 a.approved
+     WHERE allocated > 0 and a.approved and parts_id = in_parts_id
   ORDER BY a.transdate DESC, a.id DESC, i.id DESC
 LOOP
-   IF t_alloc > qty THEN
+   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.qty + t_inv.allocated) THEN
-       UPDATE invoice SET allocated = allocated - (in_qty - t_alloc)
+   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, t_cogs];
+       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.qty + t_inv.allocated) * t_inv.sellprice;
+       t_cogs := t_cogs + -1 * (t_inv.allocated) * t_inv.sellprice;
    END IF;
 END LOOP;
 
@@ -267,21 +271,21 @@
    t_transdate date;
 BEGIN
 
-SELECT CASE WHEN qty > 0 THEN cogs__add_for_ar(parts_id, qty)
-            ELSE cogs__reverse_ar(parts_id, qty)
-       END
-  INTO t_cogs 
-  FROM invoice 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;
 
+IF t_inv.qty > 0 THEN 
+   t_cogs := cogs__add_for_ar(t_inv.parts_id, t_inv.qty);
+ELSE
+   t_cogs := cogs__reverse_ar(t_inv.parts_id, t_inv.qty);
+END IF;
+
 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;
-
 SELECT CASE WHEN t_ar.transdate > max(end_date) THEN t_ar.transdate
             ELSE max(end_date) + '1 day'::interval
         END INTO t_transdate

Modified: trunk/sql/modules/test/COGS-FIFO.sql
===================================================================
--- trunk/sql/modules/test/COGS-FIFO.sql	2012-05-31 06:34:56 UTC (rev 4809)
+++ trunk/sql/modules/test/COGS-FIFO.sql	2012-05-31 07:19:52 UTC (rev 4810)
@@ -278,7 +278,7 @@
 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);
+VALUES (-2205, -2205, -2, 50, 0, 3);
 
 SELECT cogs__add_for_ar_line(-2205);
 
@@ -309,24 +309,79 @@
  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
+SELECT 'post-ar-5 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
+SELECT 'post-ar-5 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
+
+INSERT INTO ar (id, invoice, invnumber, transdate, entity_credit_account)
+VALUES (-2206,  true, 'test2006', now() - '4 days'::interval, -2000);
+INSERT INTO invoice (id, trans_id, parts_id, qty, allocated, sellprice)
+VALUES (-2206, -2206, -2, -150, 0, 3);
+
+SELECT cogs__add_for_ar_line(-2206);
+
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-6, allocation invoice 1 series 2 is 50', allocated = 50
+  FROM invoice WHERE id = -2201;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-6, allocation invoice 2 series 2 is 75', allocated = -75
+  FROM invoice WHERE id = -2202;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-6, allocation invoice 3 series 2 is 0', allocated = 0
+  FROM invoice WHERE id = -2203;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-6, allocation invoice 4 series 2 is 75', allocated = -75
+  FROM invoice WHERE id = -2204;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-6, allocation invoice 5 series 2 is 50', allocated = -50
+  FROM invoice WHERE id = -2205;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-6, allocation invoice 6 series 2 is -150', allocated = 150
+  FROM invoice WHERE id = -2206;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-6 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-6 COGS is 62.50, (invoice 4, series 2)', 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-6 COGS is 50, (invoice 5, series 2)', sum(amount) = -50
+from acc_trans
+ where trans_id = -2205 and chart_id = -2102;
+
+INSERT INTO test_result(test_name, success)
+SELECT 'post-ar-6 COGS is -125, (invoice 6, series 2)', sum(amount) = 125
+from acc_trans
+ where trans_id = -2206 and chart_id = -2102;
+
 -- Series 3, Mixed
+-- 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;
+GROUP BY chart_id, trans_id
+order by trans_id, chart_id;
 
 SELECT id, parts_id, qty, allocated, sellprice from invoice
  WHERE trans_id < -1000;

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.