-- TCE:Item IRs L.Up:3.3.15
SELECT /* #ORA.HINT# */
  -- Item
  i.pitem_id             AS I_Id,
  i.puid                 AS I_puid,
  paoI.pcreation_date    AS I_CreationDate,
  paoI.plast_mod_date    AS I_LastModDate,
  -- oI.plsd                AS I_LastSavedDate, not in TCE
  pgI.pname              AS I_Group,
  pgI.puid               AS I_GroupPuid,
  sI.psite_id            AS I_Owning_pSite_ID,

  -- Ir
  ir.pitem_revision_id   AS Ir_Id,
  ir.puid                AS Ir_puid,
  paoIr.pcreation_date   AS Ir_CreationDate,
  paoIr.plast_mod_date   AS Ir_LastModDate,
  -- oIr.plsd               AS Ir_LastSavedDate, not in TCE
  pgIr.pname             AS Ir_Group,
  sIr.psite_id           AS Ir_Owning_pSite_ID

FROM
 -- Item I/i
           infodba.PITEM                   i
 LEFT JOIN infodba.PPOM_APPLICATION_OBJECT paoI  ON (i.puid = paoI.puid)
 LEFT JOIN infodba.PPOM_OBJECT             oI    ON (i.puid = oI.puid)
 LEFT JOIN infodba.PPOM_GROUP              pgI   ON (pgI.puid = paoI.rowning_groupu)
 LEFT JOIN infodba.PPOM_IMC                sI    On (sI.puid = oI.rowning_siteu)
 -- Revision ir/Ir
 LEFT JOIN infodba.PITEMREVISION           ir    ON (i.puid = ir.ritems_tagu)
 LEFT JOIN infodba.PPOM_APPLICATION_OBJECT paoIr ON (ir.puid = paoIr.puid)
 LEFT JOIN infodba.PPOM_OBJECT             oIr   ON (ir.puid = oIr.puid)
 LEFT JOIN infodba.PPOM_GROUP              pgIr  ON (pgIr.puid = paoIr.rowning_groupu)
 -- LEFT JOIN infodba.PWORKSPACEOBJECT        pwo   ON (ir.puid = pwo.puid) not in TCE
 LEFT JOIN infodba.PPOM_IMC                sIr   On (sIr.puid = oIr.rowning_siteu)
WHERE
 i.puid = :i_puid
order by I_Id, Ir_CreationDate
