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
    AND   mln.inventory_item_id = msib.inventory_item_id
    AND   mln.organization_id = msib.organization_id
    AND   mis.inventory_item_status_code = msib.inventory_item_status_code
    AND   msib.organization_id = mp.organization_id
    AND   mil.inventory_location_id = mpoq.locator_id
    AND   mil.organization_id = mln.organization_id
    AND   mln.inventory_item_id = mpoq.inventory_item_id (+)
    AND   mln.organization_id = mpoq.organization_id (+)
    AND   mln.lot_number = mpoq.lot_number (+)
    AND   mpoq.organization_id = msi.organization_id
    AND   mpoq.subinventory_code = msi.secondary_inventory_name
    AND   mms1.status_id = mln.status_id
    AND   mms2.status_id = msi.status_id
    AND   mms3.status_id = mil.status_id
    AND   mms1.language = userenv('LANG')
    AND   mms2.language = userenv('LANG')
    AND   mms3.language = userenv('LANG')
GROUP BY
    mp.organization_code,
    msib.segment1,
    msib.description,
    msib.inventory_item_id,
    msib.organization_id,
    mln.lot_number,
    mil.inventory_location_id,
    msi.secondary_inventory_name,
    msib.inventory_item_status_code,
    msi.attribute6,
    mms1.status_code,
    mms2.status_code,
    mms3.status_code,
    msi.attribute4,
    mil.segment1,
    mil.segment2,
    mil.segment3,
    mil.segment4,
    msib.primary_uom_code,
    mpoq.subinventory_code,
    mpoq.secondary_uom_code,
    ( mln.expiration_date - SYSDATE ),
    msib.shelf_life_days,
    TO_CHAR(mln.expiration_date,'DD-MON-RRRR'),
    mln.status_id,
    mpoq.locator_id,
    mp.operating_unit,
    mln.attribute14,
    msi.reservable_type
UNION ALL
SELECT
    mp.organization_code,
    a.subinventory_code,
    mil.segment1|| '.'|| mil.segment2|| '.'|| mil.segment3|| '.'|| mil.segment4 locator,
    a.inventory_item_id,
    b.segment1,
    b.description,
    b.inventory_item_status_code item_status,
    b.primary_uom_code,
    SUM(a.primary_transaction_quantity) primary_transaction_quantity,
    a.secondary_uom_code,
    SUM(a.secondary_transaction_quantity) secondary_transaction_quantity,
    a.lot_number,
    NULL expiration_date,
    NULL lot_status,
    a.organization_id
FROM
    mtl_onhand_quantities_detail a,
    mtl_system_items_b b,
    org_organization_definitions mp,
    mtl_item_locations mil
WHERE
    1 = 1
    AND   mp.organization_id = b.organization_id
    AND   a.organization_id = b.organization_id
    AND   a.inventory_item_id = b.inventory_item_id
    AND   mil.inventory_location_id = a.locator_id
    AND   b.lot_control_code = 1
    AND   b.serial_number_control_code = 1
GROUP BY
    mp.organization_code,
    a.subinventory_code,
    mil.segment1,
    mil.segment2,
    mil.segment3,
    mil.segment4,
    a.inventory_item_id,
    b.segment1,
    b.description,
    b.inventory_item_status_code,
    b.primary_uom_code,
    a.secondary_uom_code,
    a.lot_number,
    a.organization_id;