/* TCUA:Item IRs L.Up:03.3.15 */
SELECT
  -- 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,
  -- 17.04.18/J.Fes #New
  -- paoI.pdate_released    AS I_ReleasedDateWSO,
  oI.plsd                AS I_LastSavedDate,
  pgI.pname              AS I_Group,
  pgI.puid               AS I_GroupPuid,
  sI.psite_id            AS I_Owning_pSite_ID,
  pwoI.pobject_name      AS I_Name,

  -- 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,
  -- 17.04.18/J.Fes #New
  -- paoIr.pdate_released   AS Ir_ReleasedDateWSO,
  oIr.plsd               AS Ir_LastSavedDate,
  pgIr.pname             AS Ir_Group,
  sIr.psite_id           AS Ir_Owning_pSite_ID,
  pwoIr.pobject_name     AS Ir_Name

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)
 LEFT JOIN infodba.PWORKSPACEOBJECT        pwoI  ON (i.puid = pwoI.puid)
 -- 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        pwoIr ON (ir.puid = pwoIr.puid)
 LEFT JOIN infodba.PPOM_IMC                sIr   On (sIr.puid = oIr.rowning_siteu)
WHERE
 pwoIr.pactive_seq = 1
 AND i.puid = :i_puid
order by I_Id, Ir_CreationDate
