These scripts will identify duplicate lots when the uniqueness is set to "Across Items" at organization level.
Note: Lot Uniqueness - Across Items - it means that there could be only 1 lot number across items and organizations. So if you set this value for 1 organization, it will affect all others regardless the others can have the setup to "None".
SQL scripts created for Identifying the Problem Transactions :
Select mtln1.*
from mtl_lot_numbers mtln1, mtl_lot_numbers mtln2
where mtln1.lot_number = mtln2.lot_number
and mtln1.inventory_item_id <> mtln2.inventory_item_id
and mtln1.organization_id in
(select organization_id from mtl_parameters
where lot_number_uniqueness = 1)
ORDER BY mtln1.lot_number;
Select *
from mtl_onhand_quantities_detail
where (organization_id,inventory_item_id,lot_number) in
(Select mtln1.organization_id,mtln1.inventory_item_id,mtln1.lot_number
from mtl_lot_numbers mtln1, mtl_lot_numbers mtln2
where mtln1.lot_number = mtln2.lot_number
and mtln1.inventory_item_id <> mtln2.inventory_item_id
and mtln1.organization_id in
(select organization_id from mtl_parameters
where lot_number_uniqueness = 1))
Select mtln1.*
from mtl_lot_numbers mtln1, mtl_lot_numbers mtln2
where mtln1.lot_number = mtln2.lot_number
and mtln1.inventory_item_id <> mtln2.inventory_item_id
and mtln1.organization_id in
(select organization_id from mtl_parameters
where lot_number_uniqueness = 1)
ORDER BY mtln1.lot_number;
Select *
from mtl_onhand_quantities_detail
where (organization_id,inventory_item_id,lot_number) in
(Select mtln1.organization_id,mtln1.inventory_item_id,mtln1.lot_number
from mtl_lot_numbers mtln1, mtl_lot_numbers mtln2
where mtln1.lot_number = mtln2.lot_number
and mtln1.inventory_item_id <> mtln2.inventory_item_id
and mtln1.organization_id in
(select organization_id from mtl_parameters
where lot_number_uniqueness = 1))
