Query to Get On-hand Stock at Organization, Subinventory, Locator for the lot and non Lot Items
SELECT mp.organization_code, mpoq.subinventory_code, mil.segment1|| '.'|| mil.segment2|| '.'|| mil.segment3|| '.'|| mil.segment4 locator, msib.inventory_item_id, msib.segment1 item_number, msib.description, msib.inventory_item_status_code item_status, msib.primary_uom_code, SUM(mpoq.primary_transaction_quantity) onhand_qty, mpoq.secondary_uom_code, SUM(mpoq.secondary_transaction_quantity) sec_onhand_qty, mln.lot_number, TO_CHAR(mln.expiration_date,'DD-MON-RRRR') expiration_date, mms1.status_code lot_status, msib.organization_id FROM mtl_system_items_b msib, mtl_item_status mis, mtl_item_locations mil, org_organization_definitions mp, mtl_lot_numbers mln, mtl_onhand_quantities_detail mpoq, mtl_material_statuses_tl mms1, mtl_material_statuses_tl mms2, mtl_material_statuses_tl mms3, mtl_secondary_inventories msi WHERE 1 = 1