-- select * from PNOTETYPE;

/* TC BomView ImPrecice: listet alle BOMLines - via - latest Revision */
SELECT 
 i.pitem_id AS item,
 ir.pitem_revision_id AS rev,
 -- ir.puid AS ir_puid,
 bo.pseq_no AS BOML_PSEQ,
 bo.puid AS BOML_puid,
 bo.rocc_threadu AS BOML_rocc_threadu,
-- bo.* ,
 bvr.pstruct_last_mod_date AS BOMV_Last_mod_date,
 

 (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 ,

 (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 ALTREP')
   WHERE ptt.puid = bo.rnotes_refu ) AS ocn_ug_altrep, 
   
 (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 ENTITY HANDLE')
   WHERE ptt.puid = bo.rnotes_refu ) AS ocn_ug_entity_handle
  /* UG ALTREP - UG ENTITY HANDLE - UG GEOMETRY - UG NAME - UG REF SET */

 
FROM
            infodba.PITEM i
 INNER JOIN infodba.PITEMREVISION ir ON (i.puid = ir.ritems_tagu)
 INNER JOIN infodba.PSTRUCTURE_REVISIONS ps ON (ir.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)
 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) 
WHERE
     upper(bvt.pname) = 'VIEW'
 -- AND i.pitem_id = 'BG-Count' -- :para_i_pitem_id
 -- AND ir.pitem_revision_id = 'A' -- :para_ir_pitem_revision_id
 AND ir.puid = 'RwfNJtiUTzDfjB' -- :para_ir_puid
 AND pwo.pactive_seq = 1