/* TCE BomView Precice
   !! Parameternamen: para_i_pitem_id nicht aendern !! 
   PITEM PITEM_ID = praeziese1  PUID = 'x0EdDv3nVe1piD' :para_ir_puid = 'x0EdDv3nVe1piD'
  */
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.POM_BACKPOINTER bp
  INNER JOIN infodba.PPSBOMVIEWREVISION bvr ON (bvr.puid = bp.to_uid)
  INNER JOIN infodba.PPSOCCURRENCE bo ON (bo.rparent_bvru = bvr.puid)
  INNER JOIN infodba.PITEMREVISION ir ON (ir.puid = bo.rchild_itemu)
  INNER JOIN infodba.PITEM i ON (i.puid = ir.ritems_tagu)
WHERE
  bp.from_uid = :para_ir_puid
-- ORDER BY i.pitem_id,  ir.pitem_revision_id
) BomView
WHERE ocn_uggeom_pval is null OR upper(ocn_uggeom_pval) <> 'NO'

