OnHand Quantity - With Serial number and Locator

select ohd.item,
ohd.item_desc,
ohd.org_code,
ohd.org_name,
ohd.sub_inventory,
ohd.locator,
msn.serial_number,
ohd.item_status,
ohd.uom,
NVL2(msn.serial_number,1,ohd.total_onhand_qty) onhand_qty,
ohd.total_onhand_qty,
ohd.total_reserve_qty,
ohd.total_transact_qty,
mmt.transaction_date stock_in_date,
mst.transaction_source_type_name stock_in_type
from (SELECT msi.inventory_item_id ,
    (select max(mq.create_transaction_id) from mtl_onhand_quantities mq
        where 1=1
        and mq.organization_id = moq.organization_id
        and mq.inventory_item_id=moq.inventory_item_id
        and mq.locator_id=moq.locator_id
        and mq.transaction_quantity>0)transaction_id,
    moq.organization_id,
    msi.segment1 item,
    msi.description item_desc,
    moq.subinventory_code sub_inventory,
    moq.locator_id,
    ood.organization_code org_code,
    ood.organization_name org_name,
    (mil.segment1||'-'||mil.segment2||'-'||mil.segment3||'-'||mil.segment4||'-'||mil.segment5) locator,
    msi.inventory_item_status_code item_status,
    msi.primary_uom_code,
    msi.primary_unit_of_measure uom,
    SUM(moq.transaction_quantity) total_onhand_qty,
    SUM(moq.transaction_quantity) - ( nvl( (
        SELECT
            SUM(transaction_quantity)
        FROM
            mtl_onhand_quantities
        WHERE
            inventory_item_id = moq.inventory_item_id
            AND organization_id = moq.organization_id
            AND locator_id = moq.locator_id
            AND subinventory_code IN(
                SELECT
                    secondary_inventory_name
                FROM
                    mtl_secondary_inventories
                WHERE
                    organization_id = moq.organization_id
                    AND reservable_type = '2'
            )
    ),0) + nvl( (
        SELECT
            SUM(reservation_quantity)
        FROM
            mtl_reservations
        WHERE
            inventory_item_id = moq.inventory_item_id
            AND organization_id = moq.organization_id
            AND subinventory_code = moq.subinventory_code
            AND locator_id = moq.locator_id
    ),0) ) total_reserve_qty,
    SUM(moq.transaction_quantity) - nvl( (
        SELECT
            SUM(reservation_quantity)
        FROM
            mtl_reservations
        WHERE
            inventory_item_id = moq.inventory_item_id
            AND organization_id = moq.organization_id
            AND subinventory_code = moq.subinventory_code
            AND locator_id = moq.locator_id
    ),0) total_transact_qty
FROM
    mtl_onhand_quantities moq,
    mtl_system_items_b msi,
    org_organization_definitions ood,
    mtl_item_locations mil
WHERE
    msi.inventory_item_id = moq.inventory_item_id --(+)
    AND msi.organization_id = moq.organization_id-- (+)
    AND ood.organization_id = moq.organization_id
    AND mil.inventory_location_id = moq.locator_id
GROUP BY
msi.inventory_item_id,
    moq.organization_id,
    moq.inventory_item_id,
    msi.segment1,
    msi.description,
    moq.subinventory_code,
    moq.locator_id,
    ood.organization_code,
    ood.organization_name,
    ( mil.segment1
      || '-'
      || mil.segment2
      || '-'
      || mil.segment3
      || '-'
      || mil.segment4
      || '-'
      || mil.segment5 ),
    msi.inventory_item_status_code,
    msi.primary_uom_code,
    msi.primary_unit_of_measure
) ohd,
    mtl_serial_numbers msn,
    mtl_material_transactions mmt,
    mtl_txn_source_types mst
where msn.current_organization_id(+)=ohd.organization_id
and msn.inventory_item_id(+)=ohd.inventory_item_id
and msn.current_locator_id(+)=ohd.locator_id
and msn.current_status(+)=3
and mmt.transaction_id=ohd.transaction_id
and mst.transaction_source_type_id=mmt.transaction_source_type_id
order by ohd.item,
ohd.sub_inventory,
ohd.org_code,
ohd.org_name,
ohd.locator;