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

Parts Requirements bug?



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