-- TCUA BOM ImPrecice: redrives BOMLines latest.cr Rev L.Up 11.08.17
SELECT * from
(
SELECT
  i.puid         AS BOML_i_puid,
  i.pitem_id     AS BOML_pitem_id,
  ir.puid        AS BOML_ir_puid,
  ir.pitem_revision_id AS BOML_pitem_revision_id,
  bo.pseq_no     AS BOML_pseq_no,
  bo.puid        AS BOML_puid,
  bo.rnotes_refu AS BOML_OcN_ptt_puid,
  cast(bo.pqty_value as NUMBER(*,28)) AS BOML_qty_val,  
  cast(bo.pext_transform_rot00 as NUMBER(*,28))  AS BOML_trf_rot00,
  cast(bo.pext_transform_rot01 as NUMBER(*,28))  AS BOML_trf_rot01,
  cast(bo.pext_transform_rot02 as NUMBER(*,28))  AS BOML_trf_rot02,
  cast(bo.pext_transform_rot10 as NUMBER(*,28))  AS BOML_trf_rot10,  
  cast(bo.pext_transform_rot11 as NUMBER(*,28))  AS BOML_trf_rot11,
  cast(bo.pext_transform_rot12 as NUMBER(*,28))  AS BOML_trf_rot12,
  cast(bo.pext_transform_rot20 as NUMBER(*,28))  AS BOML_trf_rot20,
  cast(bo.pext_transform_rot21 as NUMBER(*,28))  AS BOML_trf_rot21, 
  cast(bo.pext_transform_rot22 as NUMBER(*,28))  AS BOML_trf_rot22,
  cast(bo.pext_transform_per0  as NUMBER(*,28))  AS BOML_trf_per0,
  cast(bo.pext_transform_per1  as NUMBER(*,28))  AS BOML_trf_per1,
  cast(bo.pext_transform_per2  as NUMBER(*,28))  AS BOML_trf_per2,
  cast(bo.pext_transform_tra0  as NUMBER(*,28))  AS BOML_trf_tra0,
  cast(bo.pext_transform_tra1  as NUMBER(*,28))  AS BOML_trf_tra1,
  cast(bo.pext_transform_tra2  as NUMBER(*,28))  AS BOML_trf_tra2,
  cast(bo.pext_transform_invscale  as NUMBER(*,28)) AS BOML_trf_invscale,  
  bo.pref_designator      AS BOML_ref_designator,
  bo.rchild_itemu         AS BOML_child_itemu,
  bo.rchild_itemc         AS BOML_child_itemc,
  bo.poccurrence_name     AS BOML_occurre_name,
  bo.poccurrence_type     AS BOML_occurre_type,
  bo.porder_no            AS BOML_order_no,
  bvr.pstruct_last_mod_date AS BOMV_pstruct_last_mod_date,
  rank() over (partition by i.puid order by paoIr.pcreation_date desc) rnk
FROM
            infodba.PITEM                   i2
 INNER JOIN infodba.PITEMREVISION           ir2 ON (i2.puid = ir2.ritems_tagu)
 INNER JOIN infodba.PSTRUCTURE_REVISIONS    ps ON (ir2.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                   i ON (bo.rchild_itemu = i.puid)
 INNER JOIN infodba.PITEMREVISION           ir 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)
 LEFT JOIN  infodba.PWORKSPACEOBJECT        pwo On (ir.puid = pwo.puid)
 LEFT JOIN  infodba.PPOM_APPLICATION_OBJECT paoIr ON (ir.puid = paoIr.puid)
WHERE
     ir2.pitem_revision_id = :para_ir_pitem_revision_id
 AND i2.pitem_id = :para_i_pitem_id
 AND pwo.pactive_seq = 1
 AND upper(bvt.pname) in ('VIEW', 'CATIA_MULTI_MODEL', 'CATIA')
 ) t
where rnk = 1
