-- TCE+TCUA BOM Precise #L.Up:16.06.15/J.Fes
-- #LUp:25.07.2024/J.Fes add to return also ItemPuid
SELECT
 iBOML.pitem_id           AS ItemID,
 iBOML.puid               AS ItemPuid,
 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
     psr.puid = :para_ir_puid
 AND upper(bvt.pname) in ('VIEW', 'CATIA_MULTI_MODEL', 'CATIA')
GROUP BY iBOML.pitem_id,iBOML.puid,irBOML.pitem_revision_id, irBOML.puid

