/* TCUA:IRs L.Up 22.11.2013 */
/* TCUA:IRs L.Up 22.11.2013 on Update see IRs_by_Date_TCUA.sql */
select
      ItemRev.ItemPuid     AS ItemPuid,
      ItemRev.ItemId       AS ItemId,
      paoI.plast_mod_date  AS ItemLastModDate,
      ItemRev.IrPuid       AS IrPuid,
      ItemRev.IrId         AS IrId,
      paoIr.plast_mod_date AS IrLastModDate,
      s.psite_id           AS OwningSite_TC_pSite_ID,
      ItemRev.Source
from
   (Select
      i.puid               AS ItemPuid,
      i.pitem_id           AS ItemId,
      ir.puid              AS IrPuid,
      ir.pitem_revision_id AS IrId,
      'SequYes' As Source
    From
      infodba.PITEMREVISION ir
    Inner Join
      (Select ir.ritems_tagu || ir.rsequence_anchoru Id, Max(ir.psequence_id) MaxSeq
        From  infodba.PITEMREVISION ir
        Where ir.rsequence_anchoru Is Not Null
        Group By ir.ritems_tagu || ir.rsequence_anchoru
      ) HSeq
    On ir.ritems_tagu || ir.rsequence_anchoru = HSeq.Id
       and ir.psequence_id = HSeq.MaxSeq
    Left Join infodba.PITEM i On (ir.ritems_tagu = i.puid)
    Union All (
     Select
      i.puid               AS ItemPuid,
      i.pitem_id           AS ItemId,
      ir.puid              AS IrPuid,
      ir.pitem_revision_id AS IrId,
      'SequNo' As Source
    From
      infodba.PITEMREVISION ir
    Left Join infodba.PITEM i On (ir.ritems_tagu = i.puid)
    Where ir.rsequence_anchoru Is Null)
   ) ItemRev
LEFT JOIN infodba.PPOM_OBJECT o on (ItemRev.IrPuid = o.puid)
LEFT JOIN infodba.PPOM_IMC s on (o.rowning_siteu = s.puid)
LEFT JOIN infodba.PPOM_APPLICATION_OBJECT paoIr on (ItemRev.IrPuid = paoIr.puid)
LEFT JOIN infodba.PPOM_APPLICATION_OBJECT paoI  on (ItemRev.ItemPuid = paoI.puid)
WHERE paoIr.plast_mod_date >= TO_DATE(:para_plast_mod_date, 'DD.MM.YYYY HH:MI')
