Create a basic order forecasting report based on inventory levels

[jmiranda]

Report

• Qty in stock (0)
• Qty inbound (60)
• Qty minimum (50)
• Qty outbound (20)
• Qty to order (10)

[jmiranda]

Basic query

select *, 
greatest((quantity_reorder - (quantity_on_hand + quantity_inbound - quantity_outbound)), 0) as quantity_forecast
from 
(select 
location.location_number,
location.name as location_name,
product.product_code,
product.name as product_name,
ifnull(inventory_level.reorder_quantity, 0) as quantity_reorder,
ifnull(pa.quantity_on_hand, 0)  as quantity_on_hand,
(ifnull(oos.quantity_ordered_not_shipped, 0) + ifnull(oos.quantity_shipped_not_received, 0)) as quantity_inbound,
ifnull(pa.quantity_allocated, 0) as quantity_outbound
from (select 
product_id,
location_id,
sum(product_availability.quantity_on_hand) as quantity_on_hand,
sum(product_availability.quantity_allocated) as quantity_allocated
from product_availability 
group by location_id, product_id) as pa
join product on pa.product_id = product.id
join location on pa.location_id = location.id
left outer join inventory_level on inventory_level.product_id = product.id 
	and inventory_level.inventory_id = location.inventory_id
left outer join on_order_summary oos on oos.product_id = product.id and oos.destination_id = location.id 
) as product_forecast;

[jmiranda]

Order Forecasting.csv