You are html tracking Visitor

Friday, November 20, 2009

On-hand inventory information

On-hand inventory information:-
--------------------------------------

The following select statement would extracts all on-hand inventory information from Oracle
Applications base tables.

Note:- You may need to modify the query to match with your instance conditions and your requirements.

SELECT
NVL(substr(org.organization_code, 1,3), ' ') orgcode
,NVL(substr(msi.segment1, 1, 8), ' ') seg11
,NVL(substr(msi.segment1, 9, 8), ' ') seg12
,NVL(substr(msi.segment1, 17, 4), ' ') seg13
,NVL(moq.subinventory_code, ' ') sub_inv_code
,NVL(to_char(round(sum(moq.transaction_quantity))), ' ') trans_qnty
FROM mtL_system_items msi
,org_organization_definitions org
,mtl_onhand_quantities moq
,hr_organization_units hou
WHERE moq.inventory_iteM_id = msi.inventory_item_id
AND moq.organizatioN_id = msi.organizatioN_id
AND moq.organizatioN_id = org.organizatioN_id
AND moq.organization_id = hou.organization_id
-- AND hou.type = 'DC'
GROUP BY org.organization_code
, moq.subinventory_code
, msi.segment1;

I hope the above information would be helpful to you.

3 comments:

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...

Phani,

This query is useful to know the on hand quantity of a item. Please give us a query to find available quantity of item in such a way..
Available Quantity = On hand Quantity - Reserved Quantity

Thanks
Siva
Hexaware Technologies

Inventory Management Software said...

I really appreciate your post and it was superb .Thanks for sharing information.