EAM ASSET HIERARCHY

/*##############################################################################

#        EAM ASSET HIERARCHY

/*############################################################################*/

 

    SELECT LPAD (' '

               , (LEVEL - 1) * 10

               , ' ')

           || serial_number

              serial_num

         , LEVEL

         , serial_number

         , descriptive_text

         , asset_group_description

         , eam_location

         , owning_department

         , parent_serial_number

         , parent_item_id

      FROM apps.mtl_eam_asset_numbers_all_v

START WITH serial_number = '123'        -- enter asset number you want to start your hierarchy with

CONNECT BY PRIOR serial_number = parent_serial_number;