--
Select
  PrefUnion.PITEM_ID,
  PrefUnion.PITEM_REVISION_ID,
  PrefUnion.PUID,
  PrefUnion.RSEQUENCE_ANCHORU,
  PrefUnion.RITEMS_TAGU,
  PrefUnion.PSEQUENCE_LIMIT,
  PrefUnion.PSEQUENCE_ID,
  PrefUnion.Source
From
  (
    Select
      I.PITEM_ID,
      PRev.PITEM_REVISION_ID,
      PRev.PUID,
      PRev.RSEQUENCE_ANCHORU,
      PRev.RITEMS_TAGU,
      PRev.PSEQUENCE_LIMIT,
      PRev.PSEQUENCE_ID,
      'A' As Source
    From
      infodba.PITEMREVISION PRev
    Inner Join
      (
        Select
          PRev.RITEMS_TAGU
          || PRev.RSEQUENCE_ANCHORU Id,
          Max(PRev.PSEQUENCE_ID) MaxSeq
        From
          infodba.PITEMREVISION PRev
        Where
          PRev.RSEQUENCE_ANCHORU Is Not Null
        Group By
          PRev.RITEMS_TAGU
          || PRev.RSEQUENCE_ANCHORU
      )
      HSeq
    On
      PRev.PSEQUENCE_ID = HSeq.MaxSeq
    And PRev.RITEMS_TAGU
      || PRev.RSEQUENCE_ANCHORU = HSeq.Id
    Left Join infodba.PITEM I
    On
      PRev.RITEMS_TAGU = I.PUID
    Union All
    Select
      I.PITEM_ID,
      PRev.PITEM_REVISION_ID,
      PRev.PUID,
      PRev.RSEQUENCE_ANCHORU,
      PRev.RITEMS_TAGU,
      PRev.PSEQUENCE_LIMIT,
      PRev.PSEQUENCE_ID,
      'B' As Source
    From
      infodba.PITEMREVISION PRev
    Left Join infodba.PITEM I
    On
      PRev.RITEMS_TAGU = I.PUID
    Where
      PRev.RSEQUENCE_ANCHORU Is Null
  ) PrefUnion
Where
  PrefUnion.PITEM_ID in ('00000011','Test-PM-GB','P7711130')
order by
 PrefUnion.PITEM_ID , PrefUnion.PITEM_REVISION_ID ASC
