SELECT i.puid          AS ItemPuid,
  i.pitem_id           AS ItemName,
  ir.puid              AS irPuid,
  ir.pitem_revision_id AS irRev,
  paoir.plast_mod_date AS irLastModDate,
  s.psite_id           AS irOwningSite_TC_pSite_ID
FROM infodba.PITEM i
LEFT JOIN infodba.PITEMREVISION ir ON (i.PUID = ir.RITEMS_TAGU)
-- LEFT JOIN infodba.PITEMREVISION ir2 on (i.puid = ir2.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 in ( select ir.puid
from infodba.PITEMREVISION ir
inner join
 (select ir.RSEQUENCE_ANCHORU,
         max(ir.PSEQUENCE_ID) as MAX_PSEQUENCE_ID
  from infodba.PITEMREVISION ir
  where regexp_like(ir.PITEM_REVISION_ID,'rev','i')
  group by ir.RSEQUENCE_ANCHORU) ir2
ON ir.RSEQUENCE_ANCHORU = ir2.RSEQUENCE_ANCHORU
and IR.PSEQUENCE_ID     = IR2.MAX_PSEQUENCE_ID
 )