Search options (Page 1 of 11)

I need a Low Stock Report...

I can get the sql statement done but I need a little help.

Report would tell me what products at a location has a current quantity of less then or equal to the reorder level.

The statement below gives me a good report, but it doesn't show all the data I need.  Using the statement bellow will not show products that have not been transferred to that location etc.  I am guessing that I need to query some other tables like purchases received etc.

If I can please get some help...  maybe a list of tables that I need to query etc.

Thanks

AM

SELECT 0_item_codes.stock_id
    , 0_item_codes.description
    , item_moves.sum_qty
    , 0_loc_stock.reorder_level
FROM 0_loc_stock
INNER
JOIN 0_item_codes
   ON 0_item_codes.stock_id = 0_loc_stock.stock_id
  AND 0_item_codes.inactive = 0
  AND 0_item_codes.category_id = 18
INNER
JOIN ( SELECT stock_id
             , SUM(qty) AS sum_qty
          FROM 0_stock_moves
         WHERE loc_code = 10
        GROUP
            BY stock_id ) AS item_moves
   ON item_moves.stock_id = 0_item_codes.stock_id
WHERE 0_loc_stock.loc_code = 10
  AND item_moves.sum_qty <= 0_loc_stock.reorder_level

Any chance to get a page where you could view, print inventory transfers - (http://yoursite.com/inventory/view/view_transfer.php?trans_no=35)

And the ability to print to .xls

What do ya think?

AM

Would is be possible to add a column (reorder_qty) to the 0_loc_stock table...for use later in purchasing and inventory control?

I would also like to see this added to reorder_level.php for easy update to the data?

what do ya think?

AM

I made a report using repgen 2.2 but my report is blank?  The report that is already there works fine...

Any ideas where to look?

AM

I need a side by side report that would look a lot like the "Stock Check Sheets" but would show the stock level of multiple locations.  Here is a sample report (script500.com/uploads/sidebyside/sample-sidebyside-report.pdf).

I will use repgen to manage reports.  What I need is the SQL Statement.

   1. It will show stock level for multiple locations, (once the statement is written, it would not be difficult to change or add to the statement to change or add locations)
   2. It would show one, or all categories.
   3. It would not show inactive items.
   4. We may want to add an Item (reorder level) column to this report later.

Objective:  When you are in the retail trade with multiple locations, you may have some items that sell better in one location then others.  You have one location that may operate as a distribution center.  Most of your orders or cargo would come to this location and be transferred from there... When you need to restock some items at a location, you do a stock check to verify the accuracy of the system.  Then you need to check the level of stock at other locations for these items you need to order.  You may want to do a location to location transfer of these items before you generate a PO for your supplier.

If anyone has other ideas or is in need to inventory control... Please let me know.