/* TCE:Item IRs by ItemId New:14.5.16 S.Gueth*/
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,
  puowI.puser_name       AS I_OwningUser,
  pulmI.puser_name       AS I_LastModUser,
  -- 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,
  puowIr.puser_name      AS Ir_OwningUser,
  pulmIr.puser_name      AS Ir_LastModUser,
  -- 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_USER               puowI  ON (puowI.puid = paoI.rowning_useru)
 LEFT JOIN infodba.PPOM_USER               pulmI  ON (pulmI.puid = paoI.rlast_mod_useru)
 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.PPOM_USER               puowIr  ON (puowIr.puid = paoIr.rowning_useru)
 LEFT JOIN infodba.PPOM_USER               pulmIr  ON (pulmIr.puid = paoIr.rlast_mod_useru)
 -- 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
 upper(i.pitem_id) = upper(:item_id)
order by I_Id, Ir_CreationDate
