Untitled

SELECT a.equipment_object_seq                   equipment_object_seq,
       a.contract                               contract,
       a.mch_code                               mch_code,
       a.mch_name                               mch_name,
       a.mch_loc                                mch_loc,
       a.mch_pos                                mch_pos,
       a.mch_doc                                mch_doc,
       nvl(b.acquisition_cost,a.purch_price)    purch_price,
       nvl(b.purchased_date ,a.purch_date)      purch_date,
       nvl(a.warr_exp, b.warranty_expires)      warr_exp,
       decode(a.obj_level, NULL, b.note_text, a.note)
                                                note,
       a.info                                   info,
       a.data                                   data,
       nvl(b.installation_date,a.production_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,
       Equipment_Object_API.Has_Structure__(a.contract,a.mch_code) has_structure_db,                                         
       Translate_Boolean_API.Decode(Equipment_Object_Conn_API.Has_Connection(a.contract,a.mch_code))
                                                has_connection,
       Equipment_Object_Conn_API.Has_Connection(a.contract,a.mch_code) has_connection_db,                                         
       Client_SYS.Get_Key_Reference('EquipmentObject', 'EQUIPMENT_OBJECT_SEQ', a.equipment_object_seq)
                                                mch_code_key_value,
       a.type||chr(31)                            type_key_value,
       a.mch_serial_no                            serial_no,
       nvl(a.manufacturer_no,b.manufacturer_no) manufacturer_no,
       nvl(a.vendor_no, b.supplier_no)          vendor_no,
       nvl(a.part_rev, b.serial_revision)       part_rev,
       substr(Equipment_Main_Position_API.Decode(a.main_pos),1,20)
                                                equipment_main_position,
       a.main_pos                                 equipment_main_position_db,                                     
       a.type                                   type,
       a.mch_type                               mch_type,
       a.obj_level                              obj_level,
       a.group_id                               group_id,
       a.part_no                                part_no,
       a.cost_center                            cost_center,
       a.object_no                              object_no,
       a.category_id                            category_id,
       Equipment_Object_API.Get_Mch_Code(a.functional_object_seq)       sup_mch_code,
       Equipment_Object_API.Get_Contract(a.functional_object_seq)       sup_contract,
       a.rowid                                  objid,
       a.is_category_object                     is_category_object,
       a.is_geographic_object                   is_geographic_object,
       a.location_id                            location_id,
       a.criticality                            criticality,
       nvl(b.manufactured_date,a.manufactured_date)
                                                manufactured_date,
       nvl(Serial_Operational_Status_API.Decode(a.operational_status), substr(Part_Serial_Catalog_API.Get_Operational_Status(a.part_no, a.mch_serial_no), 1, 200))
                                                operational_status,
       nvl(a.operational_status, Serial_Operational_Status_API.Encode(Part_Serial_Catalog_API.Get_Operational_Status(a.part_no, a.mch_serial_no)))
                                                operational_status_db,
       b.owning_customer_no                     owner,
       b.part_ownership_db                      ownership_db,
       Ownership_API.Decode(a.ownership)        ownership,
       null                                     insert_srv,
       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.functional_object_seq         functional_object_seq,
       a.location_object_seq           location_object_seq,
       a.from_object_seq               from_object_seq,
       a.to_object_seq                 to_object_seq,
       a.process_object_seq            process_object_seq,
       a.pipe_object_seq               pipe_object_seq,
       a.circuit_object_seq            circuit_object_seq,
       TO_CHAR(a.rowversion)  objversion,
       a.rowtype                       objtype,
       a.rowkey                          objkey
FROM   equipment_object_tab a LEFT OUTER 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    nvl(b.objstate,'InFacility') IN ('InFacility', 'InRepairWorkshop', 'Contained','InInventory')