/* 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 
*/
SELECT
 iBOM.puid               AS iBOM_puid,
 iBOM.pitem_id           AS iBOM_pitem_id,
 irBOM.puid              AS irBOM_puid,
 irBOM.pitem_revision_id AS irBOM_pitem_revision_id,
 
 iBOML.pitem_id           AS item,
 irBOML.pitem_revision_id AS rev,
 iBOML.puid               AS i_BOML_puid,
 irBOML.puid              AS ir_BOML_puid
/*
,
 (SELECT ItemId AS COL1 
from (SELECT 
  i.puid               AS ItemPuid,
  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 = 'wfE5F$mhTzDfjB' -- iBOML.puid
 AND i.puid = iBOML.puid
 -- AND i.pitem_id = 'BG-Einzelteil-01'
) where rank = 1
 ) AS irBOML_puid2
*/ 
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)
WHERE
     upper(bvt.pname) = 'VIEW'
 AND irBOMpwo.pactive_seq = 1    
-- AND irBOM.puid = 'hUO5F$mhTzDfjB'
 AND irBOM.puid = 'wPVNr93mTzDfjB'
-- AND upper(iBOM.pitem_id) = 'BG-COUNT'
-- AND upper(irBOM.pitem_revision_id) = 'B'
-- AND irBOML.puid = 'wfM5F$mhTzDfjB'
-- AND irBOML.puid = 'QYYNslByTzDfjB'
 AND irBOML.pitem_revision_id =
 (SELECT max(ir.pitem_revision_id) AS item_revision_id
  FROM 
             infodba.PITEM i
   LEFT JOIN infodba.PITEMREVISION ir on (ir.ritems_tagu = i.puid)
   LEFT JOIN infodba.PWORKSPACEOBJECT pwo On (ir.puid = pwo.puid)  
  WHERE
   pwo.pactive_seq = 1
   AND i.puid = iBOML.puid 
  GROUP BY i.puid);




/*
 (SELECT max(ir3.pitem_revision_id) AS item_revision_id
  FROM infodba.PITEMREVISION ir3, infodba.PITEM i3
  WHERE 
  i3.puid = iBOML.puid 
  AND ir3.ritems_tagu = i3.puid 
  GROUP BY i3.puid);
 */ 

/*
 AND irBOML.puid = (
 SELECT IrPuid 
from (SELECT 
  i.puid               AS ItemPuid,
  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
 -- AND i.pitem_id = 'BG-Einzelteil-01'
) where rank = 1
) 
;
*/

 -- order by iBOML.pitem_id, irBOML.pitem_revision_id 
 -- AND i2.pitem_id = :para_i_pitem_id
 -- AND ir2.pitem_revision_id = :para_ir_pitem_revision_id
 -- 'hUO5F$mhTzDfjB'
/* 
 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)
*/
-- GROUP BY i.pitem_id, ir.pitem_revision_id, ir.puid
