Thanks. Will add it to the patch tracker to review..
Best Wishes,
Chris Travers
On 2/17/07, William Montgomery <[EMAIL PROTECTED]> wrote:
> 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
>
-------------------------------------------------------------------------
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
