Parts Requirements bug?

[William Montgomery <..hidden..>]

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