Wednesday 19 September 2018

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;

3 comments:

  1. Do you have this query for when there is no locator?

    ReplyDelete
  2. To clarify I am wanting this for when locator could be null in some cases

    ReplyDelete
  3. Bull eye!!
    This is the only query which works for entire Lot, Serial information for an item.

    ReplyDelete