/* TCUA:IRs of Item L.Up 15.08.2014 15:07 */
SELECT  
  i.puid               AS ItemPuid,
  i.pitem_id           AS ItemId,
  paoI.plast_mod_date  AS ItemLastModDate,
  ir.puid              AS IrPuid,
  ir.pitem_revision_id AS IrId,
  paoIr.plast_mod_date AS IrLastModDate,
  paoIr.pcreation_date As IrCreateDate,
  s.psite_id           AS OwningSite_TC_pSite_ID
FROM infodba.PITEM i
 LEFT JOIN infodba.PITEMREVISION ir 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)
 LEFT JOIN infodba.PPOM_APPLICATION_OBJECT paoI ON (i.puid = paoI.puid)
 LEFT JOIN infodba.PWORKSPACEOBJECT pwo On (ir.puid = pwo.puid)
Where
 pwo.pactive_seq = 1
 and upper(i.pitem_id) like upper(:i_pitem_id)
order by IrCreateDate desc
