/* TC BomView ImPrecice: listet alle BOMLines der höchsten Revision nach Buchstabe der 1. Ebene einer Baugruppe
   #Change: 12.03.2013 J.Fes  upper(bvt.pname) = 'VIEW'
   Example: :para_i_pitem_id = impraeziese :para_ir_pitem_revision_id = B
   ohne ocn_uggeom_pval = 'NO'
*/
select * from (
SELECT
  i.pitem_id AS item,
  ir.pitem_revision_id AS rev,
  ir.puid AS ir_puid,
  (Select  ptt.pval_0 OCN_Value
   FROM infodba.PNOTE_TEXTS ptt
     INNER JOIN infodba.PNOTE_TYPES pty ON (ptt.pseq = pty.pseq) AND (ptt.puid = pty.puid)
     INNER JOIN infodba.PNOTETYPE pt ON (pty.pvalu_0 = pt.puid)  AND (pt.pname = 'UG GEOMETRY')
   WHERE ptt.puid = bo.rnotes_refu ) AS ocn_uggeom_pval
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)
WHERE
  i2.pitem_id = :para_i_pitem_id
  AND ir2.pitem_revision_id = :para_ir_pitem_revision_id
  AND ir.pitem_revision_id = (SELECT max(ir3.pitem_revision_id) AS item_revision_id
                               FROM infodba.PITEMREVISION ir3, infodba.PITEM i3
                               WHERE i3.puid = i.puid AND ir3.ritems_tagu = i3.puid
                               GROUP BY i3.puid)
  AND upper(bvt.pname) = 'VIEW'
) BomView
WHERE
 ocn_uggeom_pval is null OR upper(ocn_uggeom_pval) <> 'NO'

