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

SF.net SVN: ledger-smb:[4654] trunk



Revision: 4654
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4654&view=rev
Author:   einhverfr
Date:     2012-04-17 08:04:31 +0000 (Tue, 17 Apr 2012)
Log Message:
-----------
COGS reversals added to stored procs

Modified Paths:
--------------
    trunk/LedgerSMB/IS.pm
    trunk/sql/modules/COGS.sql

Modified: trunk/LedgerSMB/IS.pm
===================================================================
--- trunk/LedgerSMB/IS.pm	2012-04-16 10:49:46 UTC (rev 4653)
+++ trunk/LedgerSMB/IS.pm	2012-04-17 08:04:31 UTC (rev 4654)
@@ -1719,7 +1719,7 @@
 }
 
 sub cogs {
-    my ( $dbh2, $form, $id, $totalqty, $project_id, $sellprice) = @_;
+    my ( $dbh2, $form, $id, $totalqty, $project_id, $sellprice, $inv_id) = @_;
     # $id is parts id.
     my $dbh   = $form->{dbh};
 
@@ -1728,20 +1728,20 @@
     $part_sth->execute($id);
     my ($part_ref) = $part_sth->fetchrow_hashref('NAME_lc')
 
-    # Getting cogs
-    my $cogs_sth = $dbh->prepare('SELECT * FROM cogs__add_for_ar(?, ?)');
-    $cogs_sth->execute($id, $totalqty);
-    my ($cogs) = $cogs_sth->fetchrow_array();
 
     # Setting up for the main transaction.
 
     if ($totalqty > 0){
+        # Getting cogs
+        my $cogs_sth = $dbh->prepare('SELECT * FROM cogs__add_for_ar(?, ?)');
+        $cogs_sth->execute($id, $totalqty);
+        my ($cogs) = $cogs_sth->fetchrow_array();
         push @{ $form->{acc_trans}{lineitems} },
               {
                 chart_id   => $parts_ref->{expense_accno_id},
                 amount     => $cogs * -1,
                 project_id => $project_id,
-                invoice_id => $parts_ref->{id}
+                invoice_id => $inv_id
               };
 
         push @{ $form->{acc_trans}{lineitems} },
@@ -1749,123 +1749,36 @@
                 chart_id   => $parts_ref->{inventory_accno_id},
                 amount     => $cogs,
                 project_id => $project_id,
-                invoice_id => $parts_ref->{id}
+                invoice_id => $inv_id
               };
 
     } else {
-    # In this case, the quantity is negative.  So we are looking at a 
-    # reversing  entry for partial COGS.   The two workflows supported here 
-    # are those involved in voiding an invoice or returning some items on it.
-    # If there are unallocated items for the current invoice at the end, we 
-    # will throw an error until we have an understanding of other workflows 
-    # that need to be supported.  -- CT
-    #
-    # Note:  Victor's original patch selected items to reverse based on 
-    # sell price.  This causes issues with restocking fees and the like so
-    # I am removing that restriction.  This should be discussed more fully 
-    # however.  -- CT
-        $query = qq|
-        	      SELECT i.id, i.qty, i.allocated, a.transdate,
-		             -1 * (i.allocated + i.qty) AS available,
-		             p.expense_accno_id, p.inventory_accno_id
-		        FROM invoice i
-		        JOIN parts p ON (p.id = i.parts_id)
-		        JOIN ar a ON (a.id = i.trans_id)
-	               WHERE i.parts_id = ? AND (i.qty +  i.allocated) > 0 
-		    ORDER BY transdate
-				|;
-        $sth = $dbh->prepare($query);
-        $sth->execute($id) || $form->dberror($query);
-        my $qty;
-        while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
-            $form->db_parse_numeric(sth=>$sth, hashref => $ref);
-            if ($totalqty < $ref->{available}){
-                $qty = $ref->{available};
-            } else {
-                $qty = $totalqty;
-            }
-	    # update allocated for sold item
-            $form->update_balance( 
-                            $dbh, "invoice", "allocated", 
-                            qq|id = $ref->{id}|, $qty  
-            );
+        # Getting cogs
+        my $cogs_sth = $dbh->prepare('SELECT * FROM cogs__reverse_ar(?, ?)');
+        $cogs_sth->execute($id, $totalqty);
+        my ($cogs) = $cogs_sth->fetchrow_array();
 
-            # Note:  No COGS calculations on reversed short sale invoices.  
-            # This merely prevents COGS calculations in the future agaisnt
-            # such short invoices.  -- CT
-
-            $totalqty -= $qty;
-            $allocated -= $qty;
-            last if $totalqty == 0;
-        }
-        # If the total quantity is still less than zero, we must assume that
-        # this is just an invoice which has been voided or products returns 
-        # but is not merely representing a voided short sale, and therefore 
-        # we need to unallocate the items from AP.  There has been some debate
-        # as to how to approach this, and I think it is safest to unallocate
-        # the most recently allocated AP items of the same type regardless of
-        # the relevant dates of the invoices.  I can see cases where this 
-        # might require adjustments, however.  -- CT
-
-        if ($totalqty < 0){
-            $query = qq|
-		  SELECT i.allocated, i.sellprice, p.inventory_accno_id, 
-		         p.expense_accno_id, i.id 
-		    FROM invoice i
-		    JOIN parts p ON (i.parts_id = p.id)
-		    JOIN ap a ON (i.trans_id = a.id)
-		   WHERE allocated > 0
-		         AND i.parts_id = ?
-		ORDER BY a.transdate DESC, a.id DESC
-            |;
-
-            my $sth = $dbh->prepare($query);
-            $sth->execute($id);
-
-            while (my $ref = $sth->fetchrow_hashref(NAME_lc)){
-                my $qty = $ref->{allocated} * -1;
-
-                $qty = ($qty < $totalqty) ? $totalqty : $qty;
-
-                my $linetotal = $qty*$ref->{sellprice};
-                push @{ $form->{acc_trans}{lineitems} },
+        push @{ $form->{acc_trans}{lineitems} },
                   {
-                    chart_id   => $ref->{expense_accno_id},
+                    chart_id   => $parts_ref->{expense_accno_id},
                     amount     => $linetotal,
                     project_id => $project_id,
-                    invoice_id => $ref->{id}
+                    invoice_id => $inv_id
                   };
 
-                push @{ $form->{acc_trans}{lineitems} },
+        push @{ $form->{acc_trans}{lineitems} },
                   {
-                    chart_id   => $ref->{inventory_accno_id},
+                    chart_id   => $parts_ref->{inventory_accno_id},
                     amount     => -$linetotal,
                     project_id => $project_id,
-                    invoice_id => $ref->{id}
+                    invoice_id => $inv_id
                   };
                   $form->update_balance( 
                             $dbh, "invoice", "allocated", 
                             qq|id = $ref->{id}|, $qty 
                   );
 
-                $totalqty -= $qty;
-                $allocated -= $qty;
 
-                last if $totalqty == 0;
-            }
-        }
-
-        # If we still have less than 0 total quantity, this is not a return
-        # or a void.  Throw an error.  If there are valid workflows that throw
-        # this error, they will require more work to address and will not work
-        # safely with the current system.  -- CT
-        if ($totalqty < 0){
-            $form->error("Too many reversed items on an invoice");
-        }
-        elsif ($totalqty > 0){
-            $form->error("Unexpected and invalid quantity allocated.".
-                   "  Aborting.");
-        }
     }
     return $allocated;
 }

Modified: trunk/sql/modules/COGS.sql
===================================================================
--- trunk/sql/modules/COGS.sql	2012-04-16 10:49:46 UTC (rev 4653)
+++ trunk/sql/modules/COGS.sql	2012-04-17 08:04:31 UTC (rev 4654)
@@ -1,5 +1,60 @@
+-- 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.
+
+
 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 := 0;
+        t_inv invoice;
+BEGIN
+
+FOR t_inv IN
+    SELECT i.*
+      FROM invoice i
+      JOIN ap a ON a.id = i.trans_id
+     WHERE qty + allocated < 0
+  ORDER BY a.transdate DESC, a.id DESC, i.id DESC
+LOOP
+   IF t_alloc > qty THEN
+       RAISE EXCEPTION 'TOO MANY ALLOCATED';
+   ELSIF t_alloc = in_qty THEN
+       RETURN 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;
+   ELSE
+       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;
+   END IF;
+END LOOP;
+
+RAISE EXCEPTION 'TOO FEW TO REVERSE';
+
+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.$$;
+
 CREATE OR REPLACE FUNCTION cogs__add_for_ar(in_parts_id int, in_qty numeric)
 returns numeric AS 
 $$
@@ -9,9 +64,11 @@
 BEGIN
 
 FOR t_inv IN
-    SELECT * FROM invoice 
-     WHERE trans_id IN (select id from ap)
-           AND qty + allocated < 0;
+    SELECT i.*
+      FROM invoice i
+      JOIN ap a ON a.id = i.trans_id
+     WHERE qty + allocated < 0
+  ORDER BY a.transdate, a.id, i.id
 LOOP
    IF t_alloc > qty THEN
        RAISE EXCEPTION 'TOO MANY ALLOCATED';
@@ -26,12 +83,55 @@
        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);
    END IF;
 END LOOP;
 
 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.$$;
+
+CREATE OR REPLACE FUNCTION cogs__reverse_ap
+(in_parts_id int, in_qty numeric) AS
+$$
+DECLARE t_alloc numeric;
+        t_inv inventory;
+BEGIN
+
+FOR t_inv IN
+    SELECT i.*
+      FROM invoice i
+      JOIN ap a ON a.id = i.trans_id
+     WHERE allocated > 0
+  ORDER BY a.transdate, a.id, i.id
+LOOP
+   IF t_alloc > qty THEN
+       RAISE EXCEPTION 'TOO MANY ALLOCATED';
+   ELSIF t_alloc = in_qty THEN
+       return t_alloc;
+   ELSIF (in_qty - t_alloc) <= -1 * (t_inv.qty + t_inv.allocated) THEN
+       UPDATE invoice SET allocated = allocated + (in_qty - t_alloc)
+        WHERE id = t_inv.id;
+       return t_alloc;
+   ELSE
+       UPDATE invoice SET allocated = qty * -1
+        WHERE id = t_inv.id;
+   END IF;
+END LOOP;
+
+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
@@ -47,12 +147,18 @@
         t_cp account_checkpoint;
 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 LIMIT 1;
 
 FOR t_inv IN
-    SELECT * FROM invoice 
-     WHERE trans_id IN (select id from ar)
-           AND qty + allocated > 0;
+    SELECT i.*
+      FROM invoice i
+      JOIN ar a ON a.id = i.trans_id
+     WHERE qty + allocated > 0
+  ORDER BY a.transdate, a.id, i.id
 LOOP
 
    IF t_alloc > qty THEN

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