Sample Query to get the Asset Number Details

 SELECT   (SELECT organization_code

            FROM org_organization_definitions

           WHERE organization_id = last_vld_organization_id) "Inventory Org",

         (SELECT DISTINCT segment1

                     FROM mtl_system_items_b

                    WHERE inventory_item_id =

                                           c.inventory_item_id)

                                                               "Asset Groups",

         instance_number "Asset Number",

         instance_description "Asset Number Description",

         c.serial_number "Asset Serial Number",

         (SELECT department_code

            FROM bom_departments

           WHERE department_id =

                       (SELECT owning_department_id

                          FROM eam_org_maint_defaults

                         WHERE object_id = c.instance_id))

                                                          "owning department",

         (SELECT meaning

            FROM fnd_lookup_values

           WHERE lookup_type = 'MTL_EAM_ASSET_CRITICALITY'

             AND enabled_flag = 'Y'

             AND lookup_code = c.asset_criticality_code) "criticality",

         (SELECT accounting_class_code

            FROM eam_org_maint_defaults

           WHERE object_id = c.instance_id) "accounting_class_code",

         (SELECT location_codes

            FROM mtl_eam_locations

           WHERE location_id = (SELECT area_id

                                  FROM eam_org_maint_defaults

                                 WHERE object_id = c.instance_id)) "area"

    FROM csi_item_instances c, mtl_serial_numbers msn

   WHERE c.serial_number = msn.serial_number

     AND c.inventory_item_id = msn.inventory_item_id

ORDER BY instance_number