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

[Danie B. <br...@na...>]

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...