• alvin
  • Senior Member
  • Offline
  • From: USA, Guatemala, Honduras
  • Registered: 05/20/2008
  • Posts: 301

Topic: Low Stock Report

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

"The roots of education are bitter, but the fruit is sweet."  - Aristotle.
  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Low Stock Report

Aan't you just run or modify the Inventory Planning Report? It seems easier to implement.

/Joe

  • alvin
  • Senior Member
  • Offline
  • From: USA, Guatemala, Honduras
  • Registered: 05/20/2008
  • Posts: 301

Re: Low Stock Report

Hey Joe,

What do you think it will cost me to have someone modify the  Inventory Planning Report to be a Low Stock Report? 

What do you think?

AM

"The roots of education are bitter, but the fruit is sweet."  - Aristotle.
  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Low Stock Report

Well, I don't know Alvin. You know we are short on resources sad

/Joe

  • alvin
  • Senior Member
  • Offline
  • From: USA, Guatemala, Honduras
  • Registered: 05/20/2008
  • Posts: 301

Re: Low Stock Report

Hey Joe

Can you take just a few minutes and tell me what columns I need to add up to get current Item Qty?

I have looked at the php, but not being a coder there is a lot I just don't get...

Thanks for the help.

"The roots of education are bitter, but the fruit is sweet."  - Aristotle.