/* TC BomView Precice
   #New: 14.06.2013 J.Fes group by
*/
SELECT
 i.pitem_id AS item,
 ir.pitem_revision_id AS rev,
 ir.puid AS ir_puid
-- , count(*) as ir_n
FROM
 infodba.POM_BACKPOINTER bp
 INNER JOIN infodba.PPSBOMVIEWREVISION bvr ON (bvr.puid = bp.to_uid)
 INNER JOIN infodba.PPSOCCURRENCE bo ON (bo.rparent_bvru = bvr.puid)
 INNER JOIN infodba.PITEMREVISION ir ON (ir.puid = bo.rchild_itemu)
 INNER JOIN infodba.PITEM i 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 bp.from_uid = :para_ir_puid
-- GROUP BY i.pitem_id, ir.pitem_revision_id, ir.puid
