-- TCUA:IRs L.Up 27.05.2014 on Update see IRs_by_Date_TCUA.sql
-- #LUp: 09.04.2018/J.Fes add pwo_IRDateReleased
SELECT  /* #ORA.HINT# */
  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,
  pwo.pdate_released   AS Ir_ReleaseDate,
  s.psite_id           AS OwningSite_TC_pSite_ID
FROM infodba.PITEM i
 LEFT JOIN infodba.PITEMREVISION ir ON (ir.ritems_tagu = i.puid)
 LEFT JOIN infodba.PPOM_APPLICATION_OBJECT paoI ON (paoI.puid = i.puid)
 LEFT JOIN infodba.PPOM_APPLICATION_OBJECT paoIr ON (paoIr.puid = ir.puid)
 LEFT JOIN infodba.PPOM_OBJECT o ON (o.puid = ir.puid)
 LEFT JOIN infodba.PPOM_IMC s ON (s.puid = o.rowning_siteu)
 LEFT JOIN infodba.PWORKSPACEOBJECT pwo ON (pwo.puid = ir.puid)
WHERE
 pwo.pactive_seq = 1
