The Parts Requirements report does not appear to work properly in my
case. I am using SQL-Ledger 2.6.22 and I see the same problem exists in
Ledger-SMB 1.1.8. Parts that are known to be on both open Sales Orders
and open Purchase orders show up as having SO quantities or PO
quantities but not both. The re-order quantity is then incorrect.
I have identified the cause of this problem in IC.pm. The first query
in the requirements sub gets all parts from the invoice table with an
inventory_accoun_id > 0. The subsequent 2 queries gets SO qty and PO
qty all parts on open orders and accumulate qtys. The logic for adding
the qtys assumes all parts were identified in the first query and exist
in the %parts hash. This is an incorrect assumption in my case. I'm
not sure if the assumption is invalid due to an error in our data entry
or invalid in general. I have corrected the problem as shown in the
patch below:
#--- patch
begins--------------------------------------------------------------
--- IC.pm.0 2007-02-16 16:42:09.000000000 -0500
+++ IC.pm 2007-02-16 16:42:09.000000000 -0500
@@ -1503,9 +1503,9 @@
my %ofld = ( customer => so,
vendor => po );
- for (qw(customer vendor)) {
+# for (qw(customer vendor)) {
$query = qq|SELECT p.id, p.partnumber, p.description,
- sum(qty) - sum(ship) AS $ofld{$_}, p.onhand,
+ sum(qty) - sum(ship) AS so, '0' AS po, p.onhand,
0 AS month
FROM orderitems i
JOIN parts p ON (p.id = i.parts_id)
@@ -1514,21 +1514,48 @@
AND p.inventory_accno_id > 0
AND p.assembly = '0'
AND a.closed = '0'
- AND a.${_}_id > 0
+ AND a.customer_id > 0
GROUP BY p.id, p.partnumber, p.description, p.onhand,
month|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- if (exists $parts{$ref->{id}}->{$ofld{$_}}) {
- $parts{$ref->{id}}->{$ofld{$_}} += $ref->{$ofld{$_}};
+ if (exists $parts{$ref->{id}}->{so}) {
+ $parts{$ref->{id}}->{so} += $ref->{so};
} else {
$parts{$ref->{id}} = $ref;
}
}
$sth->finish;
- }
+# }
+
+# for (qw(customer vendor)) {
+ $query = qq|SELECT p.id, p.partnumber, p.description,
+ sum(qty) - sum(ship) AS po, '0' AS so, p.onhand,
+ 0 AS month
+ FROM orderitems i
+ JOIN parts p ON (p.id = i.parts_id)
+ JOIN oe a ON (a.id = i.trans_id)
+ WHERE $where
+ AND p.inventory_accno_id > 0
+ AND p.assembly = '0'
+ AND a.closed = '0'
+ AND a.vendor_id > 0
+ GROUP BY p.id, p.partnumber, p.description, p.onhand,
+ month|;
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ if (exists $parts{$ref->{id}}->{po}) {
+ $parts{$ref->{id}}->{po} += $ref->{po};
+ } else {
+ $parts{$ref->{id}} = $ref;
+ }
+ }
+ $sth->finish;
+# }
# add assemblies from open sales orders
$query = qq|SELECT DISTINCT a.id AS orderid, b.id, i.qty - i.ship AS qty
@@ -1561,7 +1588,7 @@
# assemblies
my $query = qq|SELECT p.id, p.partnumber, p.description,
- a.qty * $qty AS so, p.onhand, p.assembly,
+ a.qty * $qty AS so, '0' AS po, p.onhand, p.assembly,
p.partsgroup_id
FROM assembly a
JOIN parts p ON (p.id = a.parts_id)
@@ -1572,7 +1599,7 @@
UNION
SELECT p.id, p.partnumber, p.description,
- a.qty * $qty AS so, p.onhand, p.assembly,
+ a.qty * $qty AS so, '0' AS po, p.onhand, p.assembly,
p.partsgroup_id
FROM assembly a
JOIN parts p ON (p.id = a.parts_id)
#--- patch ends
---------------------------------------------------------------
Regards,
Wm
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys-and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Ledger-smb-users mailing list
Ledger-smb-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
