Untitled

SELECT a.equipment_object_seq         equipment_object_seq,
       a.contract                     contract,
       a.mch_code                     mch_code,
       a.mch_name                     mch_name,
       a.part_no                      part_no,
       a.mch_loc                      mch_loc,
       a.area_id                      area_id,
       a.deck_id                      deck_id, 
       a.mch_pos                      mch_pos,
       a.mch_doc                      mch_doc,
       a.maintenance_strategy_id      maintenance_strategy_id,
       b.acquisition_cost             purch_price,
       nvl(b.purchased_date ,a.purch_date)
                                      purch_date,
       nvl(a.warr_exp, b.warranty_expires) warr_exp,
       b.note_text                    note,
       a.info                         info,
       a.data                         data,
       b.installation_date            production_date,
       Site_API.Get_Company(a.contract) company,
       Translate_Boolean_API.Decode(Equipment_Object_API.Has_Structure__(a.contract, a.mch_code)) has_structure,
       Translate_Boolean_API.Decode(Equipment_Object_Conn_API.Has_Connection(a.contract, a.mch_code)) has_connection,
       Client_SYS.Get_Key_Reference('EquipmentObject', 'EQUIPMENT_OBJECT_SEQ', a.equipment_object_seq) mch_code_key_value,
       Client_SYS.Get_Key_Reference('TypeDesignation', 'TYPE', a.type) type_key_value,
       a.mch_serial_no                  serial_no,
       b.manufacturer_no              manufacturer_no,
       b.supplier_no                  vendor_no,
       b.serial_revision              part_rev,
       Equipment_Main_Position_API.Decode(a.main_pos) equipment_main_position,
       a.type                         type,
       a.mch_type                     mch_type,
       a.obj_level                    obj_level,
       a.group_id                     group_id,
       a.cost_center                  cost_center,
       a.object_no                    object_no,
       a.category_id                  category_id,
       c.mch_code    sup_mch_code,
       'EquipmentObject'              lu_name,
       Client_SYS.Get_Key_Reference('EquipmentObject', 'EQUIPMENT_OBJECT_SEQ', a.equipment_object_seq) key_ref,
       c.contract    sup_contract,
       a.criticality                  criticality,
       b.latest_transaction           latest_transaction,
       b.state                        serial_state,
       b.operational_condition        operational_condition,
       b.operational_status           operational_status,
       b.operational_status_db        operational_status_db,
       b.manufactured_date            manufactured_date,
       nvl(b.owning_customer_no,b.owning_vendor_no)           owner,
       b.part_ownership_db            ownership_db,
       Ownership_API.Decode(b.part_ownership_db) ownership,
       Equipment_Object_API.Get_Mch_Code(a.location_object_seq)            location_mch_code,
       Equipment_Object_API.Get_Contract(a.location_object_seq)            location_contract,
       Equipment_Object_API.Get_Mch_Code(a.from_object_seq)                from_mch_code,
       Equipment_Object_API.Get_Contract(a.from_object_seq)                from_contract,
       Equipment_Object_API.Get_Mch_Code(a.to_object_seq)                  to_mch_code,
       Equipment_Object_API.Get_Contract(a.to_object_seq)                  to_contract,
       Equipment_Object_API.Get_Mch_Code(a.process_object_seq)             process_mch_code,
       Equipment_Object_API.Get_Contract(a.process_object_seq)             process_contract,
       Equipment_Object_API.Get_Mch_Code(a.pipe_object_seq)                pipe_mch_code,
       Equipment_Object_API.Get_Contract(a.pipe_object_seq)                pipe_contract,
       Equipment_Object_API.Get_Mch_Code(a.circuit_object_seq)             circuit_mch_code,
       Equipment_Object_API.Get_Contract(a.circuit_object_seq)             circuit_contract,    
       a.location_id                   location_id,
       a.item_class_id                 item_class_id,
       Safe_Access_Code_API.Decode(a.safe_access_code) 
                                       safe_access_code,
       a.safe_access_code              safe_access_code_db,
       a.process_class_id              process_class_id,
       a.not_applicable_set_date       not_applicable_set_date,
       a.not_applicable_set_user       not_applicable_set_user,
       a.not_applicable_reason         not_applicable_reason ,
       a.pm_prog_application_status    pm_prog_application_status,
       a.applied_date                  applied_date,
       a.applied_pm_program_rev        applied_pm_program_rev,
       a.applied_pm_program_id         applied_pm_program_id,
       a.is_geographic_object          is_geographic_object,
       a.is_category_object            is_category_object,
       a.main_pos                      equipment_main_position_db,
       a.cluster_id                    cluster_id,
       a.rowid                         objid,
       TO_CHAR(a.rowversion) objversion,
       a.rowkey                        objkey
FROM   equipment_object_tab a JOIN part_serial_catalog b
ON  a.part_no = b.part_no
AND    a.mch_serial_no = b.serial_no
left outer join equipment_object_tab c
ON c.equipment_object_seq = a.functional_object_seq
WHERE    a.obj_level IS NULL