Query to find serial number material transactions details

1-- For serial controlled and non-lot controlled items
2SELECT mmt.transaction_id,
3       msn.serial_number,
4       mmt.inventory_item_id,
5       (SELECT segment1
6          FROM mtl_system_items_b
7         WHERE inventory_item_id = mmt.inventory_item_id AND ROWNUM = 1)
8          item,
9       (SELECT transaction_type_name
10          FROM apps.mtl_transaction_types mtt
11         WHERE mtt.transaction_type_id = mmt.transaction_type_id)
12          transaction_type,
13       (SELECT segment1
14          FROM mtl_sales_orders
15         WHERE sales_order_id = mmt.transaction_source_id)
16          transaction_source,
17       NULL              lot_number,
18       mmt.creation_date transaction_creation_date,
19       mmt.organization_id,
20       ood.organization_code
21  FROM mtl_unit_transactions        mut,
22       mtl_serial_numbers           msn,
23       mtl_material_transactions    mmt,
24       org_organization_definitions ood
25 WHERE     msn.serial_number = mut.serial_number
26       AND mmt.transaction_id = mut.transaction_id
27       AND ood.organization_id = mmt.organization_id
28       AND mut.serial_number IN ('<Your Serial Number>')
29UNION
30-- For serial controlled and lot controlled items
31SELECT mmt.transaction_id,
32       msn.serial_number,
33       mmt.inventory_item_id,
34       (SELECT segment1
35          FROM mtl_system_items_b
36         WHERE inventory_item_id = mmt.inventory_item_id AND ROWNUM = 1)
37          item,
38       (SELECT transaction_type_name
39          FROM apps.mtl_transaction_types mtt
40         WHERE mtt.transaction_type_id = mmt.transaction_type_id)
41          transaction_type,
42       (SELECT segment1
43          FROM mtl_sales_orders
44         WHERE sales_order_id = mmt.transaction_source_id)
45          transaction_source,
46       mtln.lot_number,
47       mmt.creation_date transaction_creation_date,
48       mmt.organization_id,
49       ood.organization_code
50  FROM mtl_unit_transactions        mut,
51       mtl_serial_numbers           msn,
52       mtl_material_transactions    mmt,
53       mtl_transaction_lot_numbers  mtln,
54       org_organization_definitions ood
55 WHERE     msn.serial_number = mut.serial_number
56       AND mmt.transaction_id = mtln.transaction_id
57       AND mtln.serial_transaction_id = mut.transaction_id
58       AND ood.organization_id = mmt.organization_id
59       AND mut.serial_number IN ('<Your Serial Number>')
60ORDER BY 1 DESC