/* TC BomView Precice
   #New: 14.06.2013 J.Fes group by
   #Doc: 30.01.2014/J.Fes Vorlage war: Bom_Precise_QTY_BomLineUgGometryNo_DoCountYES.SQL
   */
SELECT 
 count(*) as ir_n,
 i.pitem_id AS item,
 ir.pitem_revision_id AS rev,
 ir.puid AS ir_puid
, pdt.pdatasettype_name
, wso.pobject_name        as DataSetName

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)

 LEFT JOIN infodba.PPSBOMVIEW bv on (bv.puid = bvr.rbom_viewu)
 LEFT JOIN infodba.PPSVIEWTYPE bvt on (bvt.puid = bv.rview_typeu)
 LEFT JOIN infodba.PIMANRELATION pir on (pir.rprimary_objectu = ir.puid)
 LEFT JOIN infodba.PDATASET pd on (pd.puid = pir.rsecondary_objectu)
 LEFT JOIN infodba.PDATASETTYPE pdt on (pdt.puid = pd.rdataset_typeu)
 LEFT JOIN infodba.PWORKSPACEOBJECT wso on (wso.puid = pd.puid)
WHERE
 upper(bvt.pname) = 'VIEW'
 and bp.from_uid = 'hEI9QuVdTzDfjB' -- :para_ir_puid vm.tc83
 and pdt.pdatasettype_name in ('UGMASTER','UGPART','IdeasPart','IdeasAssembly','IdeasDrawing')
-- and ir.
 GROUP BY i.pitem_id, ir.pitem_revision_id, ir.puid, pdt.pdatasettype_name, wso.pobject_name
order by ir_n desc