SELECT
 Objects_tbl.*,
 Ir.ObjectPuid AS IrPuid,
 Ir.ObjectName AS IrName
FROM
  dbo.Objects_tbl WITH (nolock)
 LEFT OUTER JOIN dbo.Objects_Ir_Rel_tbl WITH (nolock) ON (dbo.Objects_tbl.Id = dbo.Objects_Ir_Rel_tbl.ObjectId)
 LEFT OUTER JOIN dbo.Objects_tbl Ir WITH (nolock) ON (dbo.Objects_Ir_Rel_tbl.IrId = Ir.Id)
WHERE Objects_tbl.Id in
 (SELECT Objects_Ir_Rel_tbl.ObjectId
  FROM Objects_Ir_Rel_tbl WITH (nolock)
  WHERE Objects_Ir_Rel_tbl.IrId in
  (SELECT TOP 200
    Objects_Ir_Rel_tbl.IrId
   FROM
    Objects_tbl WITH (nolock)
    INNER JOIN Objects_Ir_Rel_tbl WITH (nolock) ON (Objects_tbl.Id = Objects_Ir_Rel_tbl.ObjectId)
   WHERE
     Objects_tbl.ObjectClass='DS'
-- AND Objects_tbl.ProcStateColRf = 'RUN.F'
AND Objects_tbl.ProcStateColRf like 'X%'
-- AND Objects_tbl.ProcStateColRf = 'N'

	AND Objects_tbl.ObjectOwningSiteID='TCP010'
	AND Objects_tbl.ObjectUgMaster_Type = 'AP'

 AND Objects_tbl.ObjectType IN ('UGMASTER','UGPART')
 AND Objects_tbl.ObjectUgMaster_APLevels<=3
 AND Objects_tbl.ObjectUgMaster_CompCount<=20000
 AND Objects_tbl.ObjectUgMaster_CompCount>=0
   ORDER BY
 Objects_tbl.ObjectUgMaster_APLevels ASC --, 
, Objects_tbl.ObjectUgMaster_CompCount ASC
   )
  )
ORDER BY
 Ir.ObjectUgMaster_APLevels ASC, 
 Ir.ObjectUgMaster_CompCount ASC,
 Ir.ObjectLastModDate DESC,
 Ir.ObjectName,
(CASE Objects_tbl.ObjectType
  WHEN 'UGMASTER' THEN 11
  WHEN 'UGALTREP' THEN 12
  WHEN 'UGMANIFESTATION' THEN 13
  WHEN 'UGPART' THEN 14
  ELSE 19 END) ASC
