/* TCE+TCUA BOM Precise #L.Up: 16.06.2015/J.Fes use psr */
SELECT
 iBOML.pitem_id           AS ItemID,
 irBOML.pitem_revision_id AS IrID,
 irBOML.puid              AS IrPuid,
 count(*)                 AS BOML_n
FROM
 infodba.PSTRUCTURE_REVISIONS psr
 INNER JOIN infodba.PPSBOMVIEWREVISION bvr ON (bvr.puid = psr.pvalu_0)
 INNER JOIN infodba.PPSOCCURRENCE bo on (bo.rparent_bvru = bvr.puid)
 INNER JOIN infodba.PITEMREVISION irBOML ON (irBOML.puid = bo.rchild_itemu)
 INNER JOIN infodba.PITEM iBOML ON (iBOML.puid = irBOML.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' OR
  upper(bvt.pname) = 'CATIA_MULTI_MODEL' OR
  upper(bvt.pname) = 'CATIA')
 AND psr.puid = :para_ir_puid
GROUP BY iBOML.pitem_id, irBOML.pitem_revision_id, irBOML.puid
