/* TCUA BOMc ImPrecice: redrives BOMLines latest.cr Rev L.Up 20.01.15 */
SELECT ItemID ,
       IrID ,
       IrPuid ,
       count(*) AS BOML_n
from
(
SELECT
 iBOML.pitem_id           AS ItemID,
 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.PPOM_APPLICATION_OBJECT paoIrBOML ON (irBOML.puid = paoIrBOML.puid)

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