SELECT ir.pitem_revision_id AS IrRev,
  i.pitem_id                AS ItemName,
  paoIr.plast_mod_date      AS IrLastModDate,
  paoIr.pcreation_date      AS IrCreateDate,
  s.psite_id                AS IrOwningSite_TC_pSite_ID
FROM infodba.PITEMREVISION ir
LEFT JOIN infodba.PITEM i on (i.puid = ir.ritems_tagu)
LEFT JOIN infodba.PPOM_OBJECT o on (ir.puid = o.puid)
LEFT JOIN infodba.PPOM_IMC s on (o.rowning_siteu = s.puid)
LEFT JOIN infodba.PPOM_APPLICATION_OBJECT paoIr on (ir.puid = paoIr.puid)
WHERE ir.puid = :ir_puid
AND ir.PSEQUENCE_ID =
  (SELECT DISTINCT MAX(ir2.PSEQUENCE_ID)
  FROM infodba.PITEMREVISION ir
  INNER JOIN infodba.PITEMREVISION ir2
  On ir.Rsequence_Anchoru = ir2.Rsequence_Anchoru
  Where ir.Puid  = :ir_puid
  )