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;
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;
Do you have this query for when there is no locator?
ReplyDeleteTo clarify I am wanting this for when locator could be null in some cases
ReplyDeleteBull eye!!
ReplyDeleteThis is the only query which works for entire Lot, Serial information for an item.