-- TCUA BOMc ImPrecice: BOMLi Rev.Rule:latest.cr #LUp 20.01.15
-- #LUp:25.07.2024/J.Fes add to return also ItemPuid
SELECT ItemID,ItemPuid,IrID,IrPuid,count(*) AS BOML_n
from
(
SELECT
 iBOML.pitem_id           AS ItemID,
 iBOML.puid               AS ItemPuid,
 irBOML.pitem_revision_id AS IrID,
 irBOML.puid              AS IrPuid,
 rank() over (partition BY iBOML.puid ORDER BY paoIrBOML.pcreation_date DESC) AS rnk
FROM
            infodba.PITEM                  iBOM
 INNER JOIN infodba.PITEMREVISION          irBOM    ON (iBOM.puid = irBOM.ritems_tagu)
 INNER JOIN infodba.PSTRUCTURE_REVISIONS   ps       ON (irBOM.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                  iBOML    ON (bo.rchild_itemu = iBOML.puid)
 INNER JOIN infodba.PITEMREVISION          irBOML   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)

 LEFT JOIN infodba.PWORKSPACEOBJECT        irBOMlpwo  ON (irBOML.puid = irBOMlpwo.puid)
 LEFT JOIN infodba.PPOM_APPLICATION_OBJECT paoIrBOML  ON (irBOML.puid = paoIrBOML.puid)

WHERE
 irBOM.puid = :para_ir_puid
 AND irBOMlpwo.pactive_seq = 1
 AND upper(bvt.pname) in ('VIEW', 'CATIA_MULTI_MODEL', 'CATIA')
) t
 where rnk = 1
GROUP BY ItemID,ItemPuid, IrID, IrPuid

