/* TC BomView ImPrecice: listet alle BOMLines - via - latest Revision */
SELECT
 i.pitem_id AS item,
 ir.pitem_revision_id AS rev,
 ir.puid AS ir_puid
FROM
            infodba.PITEM i2
 INNER JOIN infodba.PITEMREVISION ir2 ON (i2.puid = ir2.ritems_tagu)
 INNER JOIN infodba.PSTRUCTURE_REVISIONS ps ON (ir2.puid = ps.puid)
 INNER JOIN infodba.PPSBOMVIEWREVISION bvr ON (ps.pvalu_0 = bvr.puid)
 INNER JOIN infodba.PPSOCCURRENCE bo ON (bvr.puid = bo.rparent_bvru)
 INNER JOIN infodba.PITEM i ON (bo.rchild_itemu = i.puid)
 INNER JOIN infodba.PITEMREVISION ir ON (i.puid = ir.ritems_tagu)

 LEFT JOIN infodba.PPSBOMVIEW bv on (bvr.rbom_viewu = bv.puid)
 LEFT JOIN infodba.PPSVIEWTYPE bvt on (bvt.puid = bv.rview_typeu)
WHERE
     upper(bvt.pname) = 'VIEW'
 AND i2.pitem_id = :para_i_pitem_id
 AND ir2.pitem_revision_id = :para_ir_pitem_revision_id
 AND ir.pitem_revision_id =
 (SELECT max(ir3.pitem_revision_id) AS item_revision_id
  FROM infodba.PITEMREVISION ir3, infodba.PITEM i3
  WHERE i3.puid = i.puid AND ir3.ritems_tagu = i3.puid GROUP BY i3.puid)

/*
SELECT
  paoIr.pcreation_date  AS IrCreateDate
  ,i.pitem_id                 AS ItemID
  ,ir.pitem_revision_id       AS IrId
  ,ir.puid As Irpuid
--  i.pitem_id           AS ItemId,
--  ir.pitem_revision_id AS IrId
FROM infodba.PITEM i
 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.PWORKSPACEOBJECT pwo On (ir.puid = pwo.puid)
Where
-- pwo.PACTIVE_SEQ = 1
 i.puid in ('gJeBDAa3TzDfjB')
 -- and ROWNUM=1
GROUP BY i.pitem_id , paoIr.pcreation_date,  ir.pitem_revision_id,   ir.puid
order by paoIr.pcreation_date desc

*/
