/* TCUA BomView ImPrecice: listet alle BOMLines der höchsten Revision nach Buchstabe der 1. Ebene einer Baugruppe
   #New: 14.06.2013 J.Fes group by
   Example: :para_i_pitem_id = impraeziese :para_ir_pitem_revision_id = B
   ToDo: 
   1. find BOM via Ir.puid for TCUA
   2. find BOM Lines 
*/

with BOML_Latest 
 AS
 (
  SELECT 
   i.puid AS IPuid,
--   i.pitem_id           AS ItemId,
   ir.puid as IrPuid ,
--   ir.pitem_revision_id AS IrId,
--   paoIr.plast_mod_date AS IrLastModDate,
--   paoIr.pcreation_date AS IrCreationDate,
   RANK() OVER (ORDER BY paoIr.pcreation_date DESC) as Rank
  FROM infodba.PITEM i
   LEFT JOIN infodba.PITEMREVISION ir on (i.puid = ir.ritems_tagu)
   LEFT JOIN infodba.PPOM_OBJECT o on (ir.puid = o.puid)
   LEFT JOIN infodba.PPOM_APPLICATION_OBJECT paoIr ON (ir.puid = paoIr.puid)
   LEFT JOIN infodba.PWORKSPACEOBJECT pwo On (ir.puid = pwo.puid)
  Where
   pwo.pactive_seq = 1
  -- AND i.puid = iBOML.puid
 )

SELECT
 iBOM.pitem_id           AS iBOM_pitem_id,
 irBOM.pitem_revision_id AS irBOM_pitem_revision_id, 
 iBOM.puid               AS iBOM_puid,
 irBOM.puid              AS irBOM_puid,

 iBOML.pitem_id           AS item,
 irBOML.pitem_revision_id AS rev,
 iBOML.puid               AS i_BOML_puid,
 irBOML.puid              AS ir_BOML_puid,
 BOMLL.IrPuid             AS BOMLL_IrPuid

FROM
            infodba.PITEM                iBOM
 INNER JOIN infodba.PITEMREVISION        irBOM    ON (iBOM.puid = irBOM.ritems_tagu)
 LEFT JOIN  infodba.PWORKSPACEOBJECT     irBOMpwo ON (irBOM.puid = irBOMpwo.puid)
 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)
 
 inner join BOML_Latest                  BOMLL    ON (BOMLL.rank = 1 and BOMLL.IPuid = iBOML.puid)

 
WHERE
     upper(bvt.pname) = 'VIEW'
 AND irBOMpwo.pactive_seq = 1    
 AND irBOM.puid = 'wPVNr93mTzDfjB'
