12 Month Item Usage / Demand

Post date: Mar 14, 2014 6:3:46 PM

The most effective method I have found so far for getting historical demand for inventory is

SELECT ABS(SUM(mmt.transaction_quantity))

FROM apps.mtl_material_transactions mmt

WHERE transaction_action_id = 1

AND mmt.organization_id = <insert org id>

AND mmt.inventory_item_id = <insert item id>

AND mmt.transaction_date >= ADD_MONTHS(SYSDATE,-12)

This simply adds up the transaction quantity for all transactions that are of the action id of 1 (Issue from stores) for the last 12 months.