[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4654] trunk
- Subject: SF.net SVN: ledger-smb:[4654] trunk
- From: ..hidden..
- Date: Tue, 17 Apr 2012 08:04:31 +0000
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.