Saturday, October 11, 2008

Oracle query available quantity

When you like to know the available quantity of a item in Oracle E-Business suite you will, as a user, go to the Item Supply/Demand form in the On-Hand, Availability menu in Inventory. There are other screens however this is one of the most used as I have experienced.

However, in some cases you would like to have a query giving you the available quantyity of a item. Rananthaus, a tech. lead and project manager from California, pointed me to MTL_ONHAND_QUANTITIES. The query below which I wrote will give you the available quantity of an item per organisation, sub-inventory. You can use this query to build upon and enrich your application. I have tested it on a 11.5.10 instance, if you deploy it on an other version please do send me a mail or comment so I can blog that this is also working on other versions.


SELECT
QUA.INVENTORY_ITEM_ID,
QUA.ORGANIZATION_ID,
QUA.SUBINVENTORY_CODE,
SUM(QUA.TRANSACTION_QUANTITY)
FROM
MTL_ONHAND_QUANTITIES QUA
GROUP BY
QUA.INVENTORY_ITEM_ID,
QUA.ORGANIZATION_ID,
QUA.SUBINVENTORY_CODE




Post a Comment