How To Track A Serial Number Across Organizations

While we transact serial in organization, after transaction it only appears in the current organization.

The On-hand Quantity -> Serial Number query screen displays the current state of the serial number. It queries the serial number table (MTL_SERIAL_NUMBERS), Since the serial number table will only have one record for the serial number and item combination. MTL_SERIAL_NUMBERS.CURRENT_ORGANIZATION_ID hold the current organization of the serial number. This maintains only the last organization to have the serial number.

• If the serial number changes organizations, the serial number will show only in the new organization.
• If a serial number is shipped to a customer, the serial is visible with status 'issued out of stores' in the last organization that owned the serial.

 

To see the history of the serial numbers, we generally look at the transaction history using Transactions –> Material Transaction form. We can query by item or serial number and see all related transactions. Material transaction pull all the transaction that the serial number been through. Unlike serial number form that only looks at the MTL_SERIAL_NUMBERS table, the material transaction screen pull the transaction from MTL_MATERIAL_TRANSACTIONS and MTL_UNIT_TRANSACTIONS.

Example -- The following query could be useful looking for all transactions of a given serial number:

SELECT mut.serial_number, mmt.transaction_id, mmt.transaction_type_id,
       mmt.transaction_quantity, 'No Lot'
  FROM mtl_material_transactions mmt, mtl_unit_transactions mut
 WHERE mmt.transaction_id = mut.transaction_id
   AND mut.serial_number = :pi_serial_number
UNION
SELECT mut.serial_number, mmt.transaction_id, mmt.transaction_type_id,
       mmt.transaction_quantity, b.lot_number
  FROM mtl_material_transactions mmt,
       mtl_transaction_lot_numbers mtln,
       mtl_unit_transactions mut
 WHERE mmt.transaction_id = mtln.transaction_id
   AND mtln.serial_transaction_id = mut.transaction_id
   AND mut.serial_number = :pi_serial_number