Re: [Web-erp-developers] Re: Help Required with ReOrder SQL

[Daintree <p.d...@pa...>]

All looks reasonable for Location 'HQ'.

> SUM(`PurchOrderDetails`.`QuantityOrd`) -
> SUM(`PurchOrderDetails`.`QuantityRecd`) as OnOrder

could also be
SUM(`PurchOrderDetails`.`QuantityOrd` - `PurchOrderDetails`.`QuantityRecd`)
as OnOrder

I think.

You could then insert new PO records based on the preferred supplier for
each and create a pdf for all the orders created.

However, in planning what to buy where the volumes of items is managable I
prefer a human reality check about expected future requirements using the
information provided on the planning report which also suggests quantities
to buy based on the historical usage of each item over the last 3 months -
taking the maximum usage and multiplying by 3 or 4 and taking off what is in
stock and on purchase orders but adding back what is on demand - sales
orders not yet invoiced.

Phil

----- Original Message -----
From: "Danie Brink" <br...@na...>
To: "Phil Daintree" <Web...@li...>
Sent: Wednesday, March 24, 2004 3:17 AM
Subject: [Web-erp-developers] Re: Help Required with ReOrder SQL


> Hi Phil
>
> I am trying to solve the reorder question as follows, is this acceptable
> ??
>
> SQL Query 1 (SQL_Q1): Provides Stock Items for which Reorder levels have
> been activated.
>
> SELECT `StockMaster`.`StockID`,
> `StockMaster`.`Description`,
> `StockMaster`.`EOQ` as ReOrderQty,
> `LocStock`.`Quantity` As CurQty,
> `LocStock`.`ReorderLevel` As ROL
> FROM StockMaster, LocStock
> WHERE (`StockMaster`.`StockID` = `LocStock`.`StockID` AND
> `LocStock`.`Quantity` <= `LocStock`.`ReorderLevel` AND
> `LocStock`.`LocCode`="HQ")
>
> SQL Query 2 (SQL_Q2) : Provides A list of Stock Quantities already on
> order.
>
> SELECT `PurchOrders`.`IntoStockLocation`,
>  `PurchOrderDetails`.`ItemCode`,
> SUM(`PurchOrderDetails`.`QuantityOrd`) -
> SUM(`PurchOrderDetails`.`QuantityRecd`) as OnOrder
> FROM `PurchOrders`,`PurchOrderDetails`
> WHERE ( `PurchOrderDetails`.`OrderNo` = `PurchOrders`.`OrderNo` AND
> `PurchOrderDetails`.`Completed` = 0 AND
> `PurchOrders`.`IntoStockLocation` = "HQ" )
> GROUP BY `PurchOrderDetails`.`ItemCode`
> HAVING OnOrder > 0
>
> Solutions :
> the equation for stock that should be re-ordered is as folows for each
> unique item per location.
>
> Actual-Qty-Already-Actioned = SQL_Q1.CurQty + SQL_Q2.OnOrder
> if Actual-Qty-Already-Actioned < SQL_Q1.ROL then
> Do-Re-Order-Qty = SQL_Q1.ReOrderQty
> else
> Do-Re-Order-Qty = 0
>
> Could you please tell me if my assumptions are correct.
>
> Kind Regards
> Danie Brink br...@na...
>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>