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.
Subscribe to:
Post Comments (Atom)
3 comments:
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
I really appreciate your post and it was superb .Thanks for sharing information.
Post a Comment